新征程软件技术平台

# MySQL 高性能优化规范

# 设计规范

# 1. 设计表的时候，所有表和字段都添加相应的注释

这个好习惯一定要养成，设计数据库表的时候，所有表和字段都添加相应的注释，后面更容易维护。

# 2. 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。

这个点，是阿里开发手册中Mysql的规约。你的字段，尤其是表示枚举状态时，如果含义被修改了，或者状态追加时，为了后面更好维护，需要即时更新字段的注释。

# 字段规范

# 1. 尽量把所有列定义为 NOT NULL

  • NOT NULL列更节省空间：NULL列需要一个额外字节作为判断是否为NULL的标志位。
  • NULL列需要注意空指针问题：NULL列在计算和比较的时候，需要注意空指针问题。

# 2. 同财务相关的金额类数据必须使用 decimal 类型

  • 非精准浮点：float,double
  • 精准浮点：decimal
  • Decimal 类型为精准浮点数，在计算时不会丢失精度

占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且小数点要占用一个字节

可用于存储比 bigint 更大的整型数据

# 3. 尽量使用 varchar 代替 char

  • 因为首先变长字段存储空间小，可以节省存储空间。
  • 其次对于查询来说，在一个相对较小的字段内搜索，效率更高。

# 索引规范

# 1. 每个 Innodb 表必须有个主键

Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

Innodb是按照主键索引的顺序来组织表的

  • 不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）
  • 不要使用 UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长）
  • 主键建议使用自增ID值

# 2. 对于频繁的查询优先考虑使用覆盖索引

覆盖索引的好处：

  • 避免Innodb表进行索引的二次查询（回表）: Innodb 是以聚集索引的顺序来存储的，对于 Innodb 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话， 在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据， 避免了对主键的二次查询 ，减少了 IO操作，提升了查询效率。

  • 可以把随机IO变成顺序IO加快查询效率: 由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说， 对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

# 开发规范

# 1. 避免数据类型的隐式转换

隐式转换会导致索引失效，如:

select name,phone from customer where id = '111';
1

# 2. WHERE 从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致索引失效，如:

where date(create_time)='20190101'
1

推荐：

where create_time >= '20190101' and create_time < '20190102'
1

# 3. 禁止使用 SELECT * 查询

  • 消耗更多的CPU和IO以网络带宽资源
  • 无法使用覆盖索引
  • 可减少表结构变更带来的影响

# 4. 写完 SQL 先 explain 查看执行计划

日常开发写SQL的时候，尽量养成这个好习惯呀：写完SQL后，用explain分析一下，尤其注意走不走索引。

# 5. 避免使用 JOIN 关联太多的表

对于 MySQL 来说，是存在关联缓存的，缓存的大小可以由 join_buffer_size 参数进行设置。

在 MySQL 中，对于同一个 SQL 多关联（join）一个表，就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大。

如果程序中大量的使用了多表关联的操作，同时 join_buffer_size 设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。

同时对于关联操作来说，会产生临时表操作，影响查询效率，MySQL 最多允许关联 61 个表，建议不超过 5 个。

# 6. 拆分复杂的大 SQL 为多个小 SQL

  • 大 SQL 逻辑上比较复杂，需要占用大量 CPU 进行计算
  • MySQL 中，一个 SQL 只能使用一个 CPU 进行计算
  • SQL 拆分后可以通过并行执行来提高处理效率

# 操作规范

# 1. 对于大表使用 pt-online-schema-change 修改表结构

  • 避免大表修改产生的主从延迟
  • 避免在对表字段进行修改时进行锁表

对大表数据结构的修改一定要谨慎，会造成严重的锁表操作，尤其是生产环境，是不能容忍的。

pt-online-schema-change它会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中， 并在原表中增加一些触发器。把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉。 把原来一个 DDL 操作，分解成多个小的批次进行。

# limit 深分页问题 👍

举例：

select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;
1

limit深分页，导致SQL变慢原因有两个：

  1. limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。
  2. limit 100000,10 扫描更多的行数，也意味着回表更多的次数。

优化深分页问题我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法：

  • 标签记录法：select id,name,balance FROM account where id > 100000 limit 10; 需要一种类似连续自增的字段来支持
  • 延迟关联法：select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) AS acct2 on acct1.id= acct2.id; 优化思路：先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表
Last Updated: 2 years ago

← 数据库设计范式 →