高效MySQL开发规范

*本篇转载自公司内部文章(已脱敏处理)

基础规范

  • 数据库字符集默认使用utf8,如果存储emoji等四字节使用utf8mb4字符集
  • 禁止将业务日志实时保存到数据库,建议保存到日志文件,对于统计后的结果再存放到mysql中
  • 禁止线上核心业务使用mysql存储过程、视图、触发器、Event、InnoDB外键约束等,这些容易将业务逻辑和db耦合在一起,而且在MySQL的这些特性中存在严重BUG

库表设计

  • 库名、表名、字段名必须使小写字母,并采用下划线分割;对相关功能的表应当使用相同前缀,如job_xxx,前缀通常为库名或依赖主实体对象:
  • 建议表及关键字段写备注,详细说明表及字段的含义
  • 涉及货币金额或其他精度敏感的数据必须使用定点数DECIMAL替代FLOATDOUBLE
  • 库名、表名、字段名禁止使用MySQL保留字,如datelikedescreturn
  • 控制表字段数,单表不超过50个纯INT/20个VARCHAR(10)字段等同存储体积的字段数,上限控制在20~50
  • 字段长度只分配真正需要的空间
    • 问题:使用VARCHAR(5)VARCHAR(200) 存储’hello’的磁盘空间开销是一样的,使用更短的列有什么优势吗? 更大的定义列会消耗更多的内存,因为MySQL通常会分配固定大小的内存块来保存内部值,尤其是使用内存临时表进行排序或操作时会特别糟糕

索引设计

  • 基本规则:索引不是越多越好,能不添加的索引尽量不要添加,过多的索引会严重降低数据插入和更新的效率,并带来更多的读写冲突和死锁!
    • 索引名称必须使用小写,普通索引按照“idx_字段名字段名【字段名】”进行命名,唯一索引按照“uniq_字段名字段名【字段名】”进行命名”
    • 表必须有主键,推荐使用独立于业务的AUTO_INCREMENT列或全局ID生成器做主键,禁止使用多字段做联合主键
    • 不使用UUID/MD5/HASH等函数生成的无规则值做主键,效率极差
  • 索引数量控制
    • 单张表中索引数量不超过5个
    • 单个索引中的字段数不超过5个
    • 对字符串使用前缀索引,前缀索引长度不超过10个字符
  • 索引字段的顺序需要考虑每个字段去重之后的数量,区分度最大的【个数最多的】放在前面。
  • 合理创建联合索引(避免冗余),符合最左前缀原则:(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)
  • 可能需要添加索引的字段:
    • ORDER BYGROUP BYDISTINCT的字段需要添加在索引的后面
    • UPDATEDELETE语句需要根据WHERE条件添加索引
    • 对于JOIN操作,需要在JOIN字段上建立索引
  • 线上慎用FORCE INDEX,使用前需要和DBA沟通,并得到DBA的测试允许
  • 线上OLTP系统中禁止使用外键,高并发时极易引起死锁等问题
  • 索引使用禁忌
    • 不使用%前导的查询,如like '%ab'
    • 不使用负向查询,如not in/not like/<>
    • 不在低区分度的列上建立索引,例如“性别”
    • 不在索引列进行数学运算和函数运算
      • 示例:假设在表tab中id建立了索引
        • Select col_A,col_B from tab where id + 1 > 10001 不会使用索引
        • Select col_A,col_B from tab where id > 10001 – 1 会使用索引

SQL优化

  • 线上尽量少使用大SQL,可能一条大SQL就把整个数据库堵死,将复杂SQL拆分为多条简单SQL,化繁为简
    • 一条SQL只能在一个CPU上运算,如果SQL比较复杂执行效率会非常低
    • 简单SQL缓存命中率更高
    • 减少锁表时间
    • 充分利用多核CPU,提高并发效率
  • 减少MySQL端的数学运算和逻辑判断,避免SQL语句出现md5()order by rand()
  • 尽量少用SELECT * ,只取需要的数据列, 避免无谓的IO、CPU和网络开销
  • WHERE条件中,同一字段改写ORIN()IN包含的值不应过多,默认不超过200个,IN里禁止使用子查询
  • 过滤表记录合并且不去重的情况,改写UNIONUNION ALL
  • 减少使用拼接SQL,使用预编译语句,降低SQL注入概率
  • WHERE条件中的非等值条件(IN、BETWEEN、<、<=、>、>=)会导致使用不了联合索引的后续字段,注意避免
  • WHERE条件比较,字段类型和传入值必须保证类型一致,避免隐式转换
    • 示例:字段: code varchar(50) NOT NULL COMENT '编码' #code上建立了索引
      • SELECT id,name,addr from tab_name where code=10001; 不会使用索引
      • SELECT id,name,addr from tab_name where code='10001'; 会使用索引
  • Limit分页优化
    • 传统分页:
      • Select * from table limit 10000,10;
    • LIMIT原理:Limit 10000,10 偏移量越大则越慢
    • 推荐分页:
      • 分页方式一:
        • Select * from table WHERE id>=23423 limit 11; #10+1 (每页10条)
        • Select * from table WHERE id>=23434 limit 11;
      • 分页方式二:
        • Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10;
      • 分页方式三:
        • Select * from table INNER JOIN (SELECT id from table limit 10000,10) USING(id)
      • 分页方式四:
        • 程序取ID: Select id from table limit 10000,10; 然后 Select * from table WHERE ID in(123,456…);