MySQL编程规范

基础规范

1. 使用InnoDB存储引擎
2. 表字符集默认使用UTF8，如果涉及到用户输入应当校验字符范围，emoji需要使用UTF8MB4
3. 所有表都需要添加注释
4. 单表数据量建议控制在5000W以内
5. 不在数据库中存储图⽚、文件等大数据
6. 禁止在线上做数据库压力测试
7. 禁⽌从测试、开发环境直连线上数据库

命名规范

1. 库名、表名、字段名禁⽌止超过32个字符。并且能够准确表达含义。
2. 库名、表名、字段名禁⽌止使⽤用MySQL保留字
3. 临时库、表名必须以tmp为前缀，并以日期为后缀，日期格式为YYYY[MM][DD][HH][mm][ss] 比如tmp_aaa_2016、tmp_aaa_201605、tmp_aaa_20161201、tmp_aaa_2016020123等都是可以。
4. 备份库、表必须以bak为前缀，并以日期为后缀,日期格式为YYYY[MM][DD][HH][mm][ss] 比如bak_aaa_2016、bak_aaa_201605、bak_aaa_20161201、bak_aaa_2016020123等都是可以。
5. 库名、表名、字段名必须使用小写字母，单词使用下划线‘_’作分割。

开发设计规范

1. 禁⽌使用分区表。
2. 拆分大字段和访问频率低的字段，分离冷热数据。
3. 用Hash进⾏散表，表名后缀使⽤⼗进制数，下标从0开始。
4. 按日期时间分表需符合YYYY[MM][DD][HH][mm][ss] 比如aaa_2016、aaa_201605、aaa_20161201、aaa_2016020123等都是可以。
5. 采用合适的分库分表策略。例如千库十表、十库百表等
6. 尽可能不使用‘text’、‘blob’类型
7. 用‘decimal’代替‘float’和‘double’存储精确浮点数
8. 避免使用‘null’字段。‘null’字段很难查询优化，‘null’字段的索引需要额外空间，‘null’字段的复合索引无效。
9. 所有字段均定义为‘not null’
10. 使用‘unsigned’存储非负整数
11. 使用‘datetime’存储时间
12. 使用‘int unsigned’存储IPv4，而非‘char(15)’
13. 使用‘varbinary’存储大小写敏感的变长字符串
14. 禁止在数据库中存储明文密码，把密码加密后存储
15. 少用‘text/blob’，‘varchar’的性能会比‘text’高很多，实在避免不了‘blob’，请拆表
16. 数据库中不允许存储大文件，或者照片，可以将大对象放到磁盘上，数据库中存储它的路径。
17. 每行记录物理长度不超过8KB

索引规范

1. 索引的数量要控制：
   • 单张表中索引数量不超过5个
   • 单个索引中的字段数不超过5个
   • 对字符串使⽤用前缀索引，前缀索引长度不超过8个字符
   • 建议优先考虑前缀索引，超过20个长度的字符串列，最好创建前缀索引而非整列索引，必要时可添加伪列并建立索引。
2. 主键准则
   • 表必须有主键
   • 不使用更新频繁的列作为主键
   • 尽量不选择字符串列作为主键，最好使用‘int/bigint’，视数据情况而定。
   • 不使用UUID MD5 HASH这些作为主键(数值太离散了)
   • 默认使⽤非空的唯一键作为主键
   • 建议选择自增(‘auto_increment’)或发号器。
3. 重要的SQL必须被索引，比如：
   • UPDATE、DELETE语句的WHERE条件列
   • ORDER BY、GROUP BY、DISTINCT的字段
4. 多表JOIN的字段注意以下：
   • 区分度最大的字段放在前面
   • 核⼼SQL优先考虑覆盖索引
   • 避免冗余和重复索引
   • 索引要综合评估数据密度和分布以及考虑查询和更新比例
5. 索引禁忌
   • 不在低基数列上建立索引，例如“性别”
   • 不在索引列进行数学运算和函数运算
6. 尽量不使用外键，数据库是稀缺资源，在应用中保证该逻辑的完整性。
   • 外键用来保护参照完整性，可在业务端实现
   • 对父表和子表的操作会相互影响，降低可用性
7. 新建的唯一索引必须不能和主键重复
8. 索引字段的默认值不能为‘null’，要改为其他的‘default’或者空。‘null’非常影响索引的查询效率
9. 反复查看与表相关的SQL，符合最左前缀的特点建立索引。多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，减少索引数量
10. 能使用唯一索引就要使用唯一索引，提高查询效率
11. 研发要经常使用explain，如果发现索引选择性差，必须让他们学会使用hint
12. 非唯一索引必须按照‘idx_字段名称_字段名称[_字段名]‘进行命名 比如user_id、user_name、user_email建立联合索引。 idx_userId_userName_userEmail
13. 唯一索引必须按照‘uni_字段名称_字段名称[_字段名]‘进行命名 比如user_id、user_name、user_email建立联合索引。 uni_userId_userName_userEmail

SQL规范

1. SQL语句尽可能简单，大的SQL想办法拆成小的SQL语句(充分利用QUERY CACHE和充分利用多核CPU)
2. 事务要简单，整个事务的时间长度不要太长
3. 应用中不准使用触发器、函数、存储过程，除非二级主管和DBA特殊批准。
4. 降低业务耦合度，为scale out、sharding留有余地，尽量不要多业务表关联查询。
5. 避免在数据库中进⾏数学运算(MySQL不擅长数学运算和逻辑判断)
6. 不要用select *，查询哪几个字段就select 这几个字段
7. SQL中使用到‘or’的改写为用‘in’，‘or’的效率没有‘in’的效率高。
8. 禁⽌单条SQL语句同时更新多个表。
9. ‘limit’分页注意效率。‘limit’越大，效率越低。可以改写‘limit’，比如例子改写： select id from tlimit 10000, 10; => select id from t where id > 10000 limit10;
10. 避免使⽤大表的JOIN
11. 对数据的更新要打散后批量更新，不要一次更新太多数据
12. 减少与数据库的交互次数
13. 注意使用性能分析工具explain/showprofile/ mysqlsla
14. SQL语句要求所有研发，SQL关键字全部是大写，每个词只允许有一个空格
15. SQL语句不可以出现隐式转换，比如 select id from 表 where id=’1’，其中id为数值型。
16. ‘in’条件里面的数据数量要少，500个以内，要学会使用exist代替in，exist在一些场景查询会比in快
17. 最好不使用‘not in’和‘not like’，会把空和null给查出来。
18. 在SQL语句中，禁止使用前缀是%的like
19. 使⽤预编译语句，只传参数，比传递SQL语句更高效；一次解析，多次使用；降低SQL注入概率
20. 禁止使⽤‘order by rand()’
21. SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。
22. 拆分大的‘delete’和‘insert’语句。

流程规范

1. 所有新建表要估算最大数据量、最高并发访问量，并告知DBA。
2. 所有的建表操作需要提前告知DBA该表涉及的查询SQL，以及预估高并发的SQL。
3. 所有的建表需要确定建立哪些索引后才可以建表上线；
4. 所有的改表结构、加索引操作都需要将涉及到所改表的查询SQL发出来告知DBA等相关人员；
5. 在建新表加字段之前，要求研发至少要提前3天邮件出来，给DBA们评估、优化和审核的时间
6. 批量导入、导出数据必须提交DBA操作。
7. 禁⽌在线上从库执行后台管理和统计类查询。
8. 禁⽌有super权限的应用程序账号存在。
9. 推广活动或上线新功能必须提前通知DBA进⾏行流量评估。
10. 不在业务高峰期批量更新、查询数据库。

数据字段信息

1 数值类型字段

如果数值字段没有那么大，就不要用 bigint

参考文档

1. 运维帮