MySQL开发规范
2017-07-05 11:08 Kevin.hhl 阅读(175) 评论(0) 收藏 举报数据库线上准入规范(通用)
准入规范级别
- 【必须】：为强制要求，必须遵守，如有特殊需求需要在部署测试环境前经评审通过
- 【强烈建议】：尽量遵守，如无法遵守请给出备注说明，否则不予执行、部署
- 【建议】：为最佳实践，开发人员可以根据实际情况有所调整
＊思维导图如下：
1.MySQL设计类规范：
1.1 核心规则
表字符集选择utf8mb4【必须】
表必须有物理主键，且不使用字符字段、复合主键，统一使用自增整形(int/bigint)主键 【必须】
生产库存储引擎使用INNODB 【必须】
不在数据库中存储图片、文件，不使用大文本类型【必须】
不使用外键，由程序保证数据一致性【必须】
禁止使用存储过程、触发器 【必须】
每张表数据量控制在2000W以下 ，如预计会超出，需提前做好拆分或者归档迁移计划【强烈建议】
表、字段必须加上comment注释 【必须】
1.2 命名规则
库名、表名、字段名、索引名使用小写字母,以下划线_分割 【必须】
库名、表名、字段名、索引名不使用保留字 （关键字）【必须】
库名、表名、字段名不超过15个字符，需见名知意 【建议】
索引命名：【强烈建议】
聚集索引：“cx_字段名前3字母[_字段名前3字母]”
唯一索引：“uniq_字段名前3字母[_字段名前3字母]”
非唯一索引：“idx_字段名前3字母[_字段名前3字母]”
如果索引具有多种属性，按照“聚集 >唯一 >非唯一”的顺序命名
1.3 字段类规范
能用整数的坚决不用字符类型 【必须】
所有字段均定义为NOT NULL，即便为null的字段设置default值 【必须】
字符串统一单引号引用，而不是双引号【必须】
使用UNSIGNED存储非负整数 【必须】
使用DECIMAL存储精确浮点数 【必须】
使用TINYINT来代替ENUM类型 【必须】
拆分TEXT、BLOB类型字段，或者独立建表 【必须】
使用UNSIGNED INT存储ipv4 地址，而不是CHAR(15) 【强烈建议】
非中文字段使用varchar存储变长字符串 【强烈建议】
尽可能将字符转化为数字 存储【建议】
使用TIMESTAMP存储操作时间 *【建议】 timestamp类型精确到秒，用于INSERT或UPDATE操作时记录日期和时间，范围是从1970-01-01到2037年
varchar(N) 其中的N从MySQL 5.0开始指最多能存的字符数，提前对N预估好，不要过大，过大此表产生的临时表申请内存是N申请，过大容易转成text。 除text、blog字段外，InnoDB每行最多65535字节，这个是硬性规定。 与选择的字符集有关，字母和数字在gbk、utf8、utf8mb4都是一个字节，中文在gbk下2个字节，中文在utf8下是3个字节。utf8：变长字节，单个字符最多占用3个字节。utf8mb4：变长字节，单个字符最多占用4个字节，可以存放表情字符。
为索引的字段必须为not null 【必须】
1.4 索引类规范
单张表索引数量不超过5，单个索引字段数不超过5 【强烈建议】
避免包含及冗余索引 【强烈建议】
重要的SQL必须被索引 【强烈建议】
UPDATE、DELETE语句的WHERE条件列
ORDER BY、GROUP BY、DISTINCT的字段
多表JOIN的字段
主键的选择要慎重 【强烈建议】
首选使用非空的唯一键， 其次选择自增列或发号器
不使用更新频繁的列，尽量不选择字符串列，不使用UUID MD5 HASH * 能不建立索引尽量不用索引 【强烈建议】
不在null列上加索引
不在低基数列上建立索引，例如“性别”
复合索引字段排序，区分度最大的字段放在前面【建议】
对特殊字段，增加crc32或md5的伪列并建立索引【建议】
核心SQL优先考虑覆盖索引【建议】
对字符串使用前缀索引【建议】 前缀长度不超过8个字符，必须是最左前缀。
针对索引的禁忌 【建议】
不使用负向查询，例如 not in，!= ，not like
不在索引列进行数学运算和函数运算
不使用%前导的模糊查询，例如like “%abc”
有order by的select 不用select * from xxx，指定需要查询的字段 【必须】
前缀索引原则:找到前缀最小长度>=95%的区分度：
select min(cc.l) as ninty_five_percent from(select length(url) as l, count(distinct url) as c, @ccount := @ccount + count(distinct url) as t from upload.image,(select @ccount := 0) as foo group by length(url)) as cc where cc.t >= (@ccount*0.95) order by cc.t; |
CREATE TABLE IF NOT EXISTS innodb_table_stats ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT comment '自增主键', `database_name` VARCHAR(64) NOT NULL comment '数据库名', `table_name` VARCHAR(64) NOT NULL comment '表名字', `last_update` TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '最后更新的时间', `n_rows BIGINT` UNSIGNED NOT NULL comment '自增主键', `clustered_index_size` BIGINT UNSIGNED NOT NULL comment '聚簇索引的大小', `sum_of_other_index_sizes` BIGINT UNSIGNED NOT NULL comment '除聚簇索引外其他索引的大小', PRIMARY KEY (id), UNIQUE KEY uniq__dat_nam_tab_nam(database_name,table_name), KEY `idx__las_upd`(last_update)) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 comment 'InnoDB表统计信息'; |
1.5 SQL类规范
UPDATE、DELETE语句不使用LIMIT 【必须】
避免大数据类型间的隐式转换 【必须】 原因：会导致索引失效，例如数字转字符串。
拒绝大SQL，拆分成小SQL【强烈建议】 好处：充分利用多核CPU。
使用预编译语句【建议】 好处：只传参数，比传递SQL语句更高效；一次解析，多次使用 ；可以避免SQL注入。
不使用select * ，必须指定索取的字段 【必须】
避免使用大表的JOIN 【强烈建议】 原因：MySQL最擅长的是单表的主键/索引查询，JOIN消耗较多内存，产生临时表，查询变慢。
避免在数据库中进行数学运算 【强烈建议】 原因：MySQL不擅长数学运算，无法使用索引
减少与数据库的交互次数 【建议】
INSERT ... ON DUPLICATE KEY UPDATE
REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),()
UPDATE … WHERE ID IN(A,B,C,…)
禁止in的个数超过50个【必须】
禁止并发使用count(*) 【必须】
不要一次批量更新数据条数>3000,拆成批量,并适当sleep x 【必须】
不使用子查询，一律改成join方式【必须】
SQL中的or 一律使用in替换，尤其在字段没有索引的情况下in的效率比or高【必须】
分页sql: limit M,N; 尤其M很大的时候使用先select c1,c2 from tx as n inner join(select pk_col from tx where cx='x' limit m,n) as m on n.pk_col = m.pk_col; 【必须】
不使用多表(>=3)join，放到业务逻辑层分解实现，或者通过存储冗余来解决 【强烈建议】
select * from txx order by key_col1 desc ,pk_col desc limit n; 一定记得加主键列 【必须】
1.6 MySQL使用规范
申请MySQL说明用途、存储容量预估、峰值流量(QPS、TPS)预估、连续增长的表或大表在设计的时候考虑拆分【必须】
所有的DDL(create、alter、drop)提交到DBA执行【必须】
批量的DML修正操作需要提交到DBA评审后再执行【必须】
所有的DCL(授权、回收权限)提交到DBA执行【必须】
业务上的调整涉及到数据库请及时通知DBA 【必须】
程序端必须设置connect_timeout、idle_timeout 【必须】
程序端使用事务最后必须及时结束事务(commit/rollback) 【必须】
后台统计类的业务、临时任务脚本等放到从库上 【必须】
统计或大数据业务使用只读权限用户在从库上执行 【必须】
不同服务使用同一db实例请申请自己的用户，避免使用同一用户 【必须】
2.MySQL操作类规范
对所有的业务数据库，都需要在“新建数据库申请”中明确RTO和RPO，DBA制订对应的维护和备份策略（SLA）【必须】
对线上数据库结构的任何变更，均走上线部署流程 【必须】
不在业务高峰期执行大批量数据操作（增删改）【必须】
不允许直接drop table,要先rename,一周后再drop【必须】
对于线上关键服务db不允许直接delete data,要先复制到数据删除归档库【必须】
任何数据库相关的BUG，及时告知DBA 【建议】
大表数据变更（增删改）多行数据时，未做分批处理，导致业务故障。要求大表每次变更不超过3000行，中间waiting 1秒 【必须】
3.MySQL部署类规范
3.1单机上部署多少个实例将是维护与管理的上限？ 4个实例
3.2是否允许不同项目的数据库实例混合部署到同一个MySQL群集上？同是一级服务的项目，不允许部署到同一个群集(机器)上，即单机上不能出现两个不同项目的一级服务。
此级别决定该数据库的备份策略、响应时限：
浙公网安备 33010602011771号