《MySQL 性能优化：从表设计到数据操作的实用技巧》

MySQL 性能优化：从表设计到数据操作的实用技巧

描述

过去排查现场问题时，常常会碰到因慢 sql 不断堆积而产生的一系列麻烦，像是 cpu 占用率突然飙高、服务出现卡顿现象，严重时甚至会导致宕机。这些慢 sql 在实际分析中，往往存在很大的优化空间。

慢 sql 可能表现为执行时间过长，在业务高峰期执行频率过高时问题尤其明显。

这里总结了一些小技巧分享给大家，这些技巧涉及到表设计、插数据和查数据阶段。

表设计

• 不要被数据库范式束缚
  • 理论上关系数据库设计需满足三大范式（1NF 原子性、2NF 唯一性、3NF 独立性）。
  • 但实际业务为提高查询效率可降低范式要求，保存一定冗余信息（反范式），如人员表直接加国籍字段而非关联国籍表。
  • 冗余表还有一种常见的应用场景就是涉及跨库分表时所做的冗余，否则跨库数据库连接、开销操作都会造成网络开销。
  • 比如：通过在订单表中冗余一些用户表的关键信息（如用户昵称、用户等级等），可以减少这种跨库查询的需求。在查询订单信息时，无需再跨库查询用户表获取相关信息，直接从订单表的冗余字段中就可以获取部分有用信息，提高了查询效率。
  • 实战来说其实我们公司采用的是伪RPC(HTTP)调用方式，涉及大数据量时确实会造成一定的查询影响。当然据说RPC比HTTP更轻量。有懂的可以在评论区补充！这里就不展开说了。
• 优先选择符合存储需要的最小的数据类型
  • 某些字符串可转数字类型存储，如 IP 地址转整型。
  • 权限类列可转数字，如增删改查用四位二进制数表示并存储该二进制数值。
  • 小数值类型（年龄、状态表示）优先用 TINYINT 类型。
• 建议把 BLOB 或 TEXT 列分离到单独的扩展表中
  • MySQL 内存临时表不支持 TEXT、BLOB 类型，含此类数据的查询在排序等操作时只能用磁盘临时表且可能二次查询，sql 性能差。
  • 若使用此类数据类型，分离到单独扩展表，查询时避免 select *，不需要 TEXT 列数据时不查询该列。
• 尽可能把所有列定义为 NOT NULL
  • 索引 NULL 列需额外空间保存。表中字段允许为 NULL 时每行有 NULL 标志位，全非 NULL 则无此标志位。
  • 对 NULL 值做比较和计算时需特别处理。
  • 数值类型（以 int 列为例）在 min/max/sum/avg 中 NULL 值直接忽略，avg 结果可能不符合预期。
  • 对 NULL 做加减操作（如 1 + NULL）结果为 NULL。
• 限制每张表上的索引数量，不要滥用索引
  • 索引并非越多越好，虽可提高查询效率，但会降低插入和更新效率，有时也会降低查询效率。
  • MySQL 优化器会评估索引生成执行计划，索引多会增加优化器生成计划的时间从而降低查询性能。
  • 当然滥用索引并不是说不用索引，当你认为索引对你是有效且有帮助的时候索引是必要的。
  • 结合我司来看，其实对于常见查询字段为防止后续查询速度造成影响会在一开始建表的时候强制对字段建立索引。
• 索引列顺序的选择
  • 建立索引为减少随机 IO、增加查询性能。
  • 区分度最高的列放联合索引最左侧（区分度 = 列中不同值的数量 / 列的总行数）。
  • 字段长度小的列放联合索引最左侧（提高 IO 性能）。
  • 使用最频繁的列放联合索引左侧。
• 避免建立冗余索引和重复索引
  • 会增加存储开销和降低插入性能。
  • 重复索引示例：primary key (id)、index (id)、unique index (id)。
  • 冗余索引示例：index (a,b,c)、index (a,b)、index (a)。
• 对于频繁的查询优先考虑使用覆盖索引
  • 覆盖索引包含所有查询字段（where、select、order by、group by 包含的字段）。
  • 好处是避免 InnoDB 表二次查询（回表操作），提升查询效率。
• 尽量避免滥用外键约束
  • 外键影响父表和子表写操作降低性能，业务数据完整性可在业务代码里实现。
  • 避免滥用而不是说不允许使用，当使用外键进行写操作时和删除操作时会检查父表从而导致数据库层面的额外检查以及锁机制的影响(删除/修改父表时锁定子表等)。
  • 如果只是简单场景下完全可以使用外键约束。因为数据库的优化器是专门针对数据库操作进行优化的，外键约束的检查在数据库内部是高度优化的操作，直接由数据库引擎执行，不需要额外的网络传输和应用层的处理逻辑。
  • 而对于复杂的场景，涉及高并发和大数据量时，外键约束会导致大量的锁等待和事务回滚。例如电商领域的双11大促。如果使用外键约束，数据库可能会因为频繁的外键检查而产生大量的锁竞争，导致性能下降。而在业务代码中，可以通过更灵活的方式处理这种情况，比如采用异步查询验证用户 ID 的方式，或者对验证逻辑进行缓存优化，减少对数据库的频繁查询。

插入优化

• 使用批量插入
  • 批量插入可提高插入效率和性能，减少每次插入一条记录造成的开销（执行 SQL 语句等）。
  • 可减少数据库 I/O 操作次数、减少锁定表的时间。
  • 注意设置合适插入大小和频率，防止 MySQL 因插入过多数据出现异常。
  • 使用 mybatis 的 saveBatch 方法时，需在 jdbcUrl 上加 rewriteBatchedStatements = true 参数将单条 insert 改写成一条 insert 多个参数形式。
  • 权衡事务大小和性能，需要根据具体业务场景和数据库的负载能力，找到一个合适的事务大小，既能保证数据一致性，又能兼顾系统的并发性能。
• 主键顺序插入
  • InnoDB 引擎是聚集索引，按主键顺序插入可避免页面分裂，提高聚集索引性能，避免索引树维护代价过高。
  • 推荐使用Snowflake 而非UUID的原因，雪花算法存储的一定程度上有序从而减少上述情况的发生，另一方面雪花算法为long类型UUID为字符类型 前者占用8字节而后者占用16字节。
  • 我们知道索引数据会占用一次IO，所以索引数据大小类型的选择越小越好。

查询优化

• 避免使用select *
  • 实际业务可能只需一两列数据，使用 select * 会浪费数据库和应用服务器内存资源。
  • 不会走覆盖索引，会产生大量回表操作导致性能低。
• 用 union all 代替 union
  • union 获取去重结果，union all 不去重，排重过程更耗时耗 CPU 资源。
  • 除特殊场景（如不允许结果集有重复数据）外尽量用 union all。
• 对应同一列进行 or 判断时，使用 in 代替 or
  • in 的值不超过 500 个，in 操作更能有效利用索引，or 很少能利用索引。
• 小表驱动大表
  • 用小表数据集驱动大表数据集效率更高。
  • 如根据小表数据查询大表数据时，使用 in 或 exists 关键字，in 优先执行子查询效率更高（in 里数据量少作为条件查询更快）。
  • 详情可以参考面试鸭1482. MySQL 中 EXISTS 和 IN 的区别是什么？
• 善用 limit
  • 仅关心是否存在符合条件数据时，用 select 1 from table where xx = xx limit 1 替代 select count (*) from table where xx = xx。
  • count (*) 会全表扫描，limit 1扫到一条符合条件数据就返回。
• 避免使用子查询
  • 子查询结果集无法使用索引，会被存到临时表（无索引）影响查询性能。
  • 尤其返回结果集大的子查询对性能影响更大，消耗过多 CPU 和 IO 资源产生慢查询。
  • 采用内外连接或者临时表替换子查询。
• 避免数据类型的隐式转换
  • 隐式转换会导致索引失效。
• 避免使用 JOIN 关联太多的表
  • MySQL 关联查询存在关联缓存，关联表越多占用内存越大。
  • 多表关联操作多且 join_buffer_size 设置不合理时易造成内存溢出。
  • 可拆分多表关联长 sql 为若干短 sql 再在程序中拼接数据。
• 尽量使用覆盖索引查询
  • 索引树存索引列 + 主键，查询字段在索引列上（覆盖索引）可直接从索引树返回数据，否则需回表查询。频繁执行的 sql 建议创建覆盖索引。

最后

文章中指出的部分关于SQL优化，在面试鸭网站中均有提及。

如：EXISTS IN 区别？、为什么不推荐多表JOIN、MySQL如何解决深度分页？、排序如何实现的？、内外连接区别？、如何优化SQL、如何监控优化慢SQL等……

感兴趣可移步到面试鸭。