SQL优化 硬控面试官半小时
SQL优化可以串联 MySQL 90%的知识点，讲的好的话，可以让面试官眼前一亮，是很加分的，特别是对于那些本身就很懂MySQL的面试官，基本这次面试就稳了。
这里我总结了一些SQL优化的思路，供大家参考，基本覆盖99%的场景了。
PS: 如果对你有用的话，请不要吝啬你的花花，这是我分享的动力
如何定位慢SQL（场景）
- 业务场景执行时间非常久，触发网关超时
- 使用以下命令分析慢SQL日志：`mysqldumpslow /path/to/your/slow-query.log`
优化思路
增删查改
- 对于插入可以把多个语句合并成一个语句批量处理。
- 对于删除，因为删除是假删除(减少B+树合并访问磁盘的开销)，如果有太多地方没有使用，B+树层数虚高，增加了访问磁盘的速度，并且全表扫描也会扫描到很多无用数据，可以在数据库空闲的时候通过alter table来重建表使数据排列更紧凑
- 对于频繁修改的数据
- 利用最左匹配原则减少索引数量
- 如果有唯一索引可以考虑改成普通索引，避免修改时为了维护唯一性导致change buffer失效。
- 对于查询:
- 使用缓存, 早期mysql开启查询缓存，mysql8.0没有查询缓存，业务层可以使用缓存例如redis；或者可以把一些特殊的语句定期执行然后保存，后面查保存的数据，不用执行sql，比如我之前做的一个预测成本的功能，每月只需要预测一次，那就可以写一个定时任务每个月定时预测一次保存到另一张表，后续查询直接查这张表就可以了，就不用执行复杂SQL了。
- 对于大数据量的场景，可以读写分离，分库分表
分析执行计划
EXPLAIN分析SQL语句的执行计划，主要关注以下几个字段
- 如果
type为ALL，说明进行了全表扫描，考虑是否可以通过增加索引来优化。 - 分析
possible_keys （可能使用到的索引）和key（实际使用的索引），确保相关的列上建立适当的索引并且正确选取索引。 - 使用覆盖索引来避免回表
- 使用复合索引来提高多条件查询的性能（索引下推）。
- 利用最左匹配原则尽可能的建立更少的索引
- 分析有没有没有正确选取索引：
- 可能会错误的使用全表扫描的场景：
- 对字段使用了函数：将函数写在判断条件上面，避免对字段使用函数。
- 字段隐式类型转换（ str->int, 字段使用了utf8但是字符是utf8mb4），需要保证查询目标与字段类型一致
- 如果没有出现上述问题，还有一种解决思路：使用force index强制使用索引 | order by .. limit 1。
- 因为选取索引是优化器的工作，优化器会分析选取索引的扫描行数加上回表的代价是否比主键全表扫描少，这里采用采样分析，因为全表分析代价太大，在多个事务的时候，因为是假删除而且多个事务的时候MVCC多版本数据在undo-log里面，这个时候采样分析会把已经删除的数据也考虑到总量里面去，比如实际上总量是1000行，考虑成了2000行导致考虑的扫描行数翻倍。所以采样分析针对高并发和大数量的场景是非常不准的
- MySQL临时表, CTE会破坏索引结构（例如group后使用了临时表）：想办法优化掉临时表，或者减少临时表的查询、JOIN操作
rows字段，表示查询的结果集行数。我们要尽可能的减少rows的数量，以下是一些思路- 确保查询条件尽可能具体, 例如在WHERE子句中使用更严格的条件。
- 对于确定的数量（例如只需要查询一个结果）使用limit
- in 替换成 exists，in 是 双重匹配，exists匹配到了一个后就会提前返回
- count字段看可不可以替换成count( * ), count(1)
- Extra字段，记录一些额外信息
- 如果有Using filesort，表示使用了文件排序。
- 可以考虑给需要排序的字段加上索引，因为索引使用的B+树本来就是排序好的，可以减少排序时间。 或者可以把单次排序的内存
sort_buffer_size设置大一点，因为排序是取磁盘里的部分数据到内存进行排序最后合并， 把单次排序内存设置大一点这样减少IO次数 - 如果有Using Join Buffer, 说明Join没有使用索引。没有索引join会用到Block Nested-Loop Join算法，时间复杂度很高，可以看作两层遍历，实际上更复杂一点，考虑到数据很多不能全读到内存里，mysql使用了join_buffer来存一部分数据，可能会因为 join_buffer 不够大，需要对被驱动表做多次全表扫描。
- 在需要Join的字段加上索引
其它思路
- 对于某些语句正常执行的时候很快，偶尔执行时间会很长，很难复现。
- 这是因为触发了flush操作，我们应该尽可能的去减少flush操作触发的次数。如果频繁触发flush操作，可能是脏页比例过高，可以查看脏页比例，脏页比率控制策略主要与redo-log大小和写盘速度(innodb_io_capacity)有关，我们需要设置合理的redo_log大小以及符合实际的预期写盘速度(可以通过 fio 这个工具来测试)。我们也可以想办法减小单次flush操作的时间，InnoDB有个策略是刷某一页时发现这一页旁边的页也是脏页也会去刷旁边的页，这样连带着一片都刷进去，导致一次flush操作可能会耗费很长的时间，我们可以通过
innodb_flush_neighbors来控制这个行为，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。 - 设计表的时候, 使索引的长度尽可能小，页的大小是固定的，根据索引长度计算B+树阶数，阶数越小，每页的节点数越多，B+数深度越小，访问磁盘次数越少；
- 对于事务，使修改的顺序尽可能靠后，因为修改会持有行锁，会等事务提交后再释放锁，这样可能会阻塞其他事务，修改靠后就可以更晚的持有这些锁，这样就可以减少阻塞，也可以减少死锁发生的概率。
- 大数据量分页查询的优化，改成使用id：分页的原理是查询到第一个满足条件的数据行后往后一页一页遍历，如果改成id的话，就可以使用索引进行优化，直接定位到需要查询的那一页
- CTE物化问题，部分数据库（如MySQL）将CTE处理为临时表时不会保留原表索引， 解决方法：修改逻辑避免使用CTE/使用临时表替换CTE, 并在临时表上加索引（空间换时间）
CTE（Common Table Expression 公共表表达式） 是一种在 SQL 查询中定义临时结果集的方法。它通过 WITH 子句创建，可以：
- 简化复杂查询的可读性
- 支持递归查询
- 多次引用同一结果集 基础语法
WITH cte_name (column1, column2, ...) AS ( -- 子查询 SELECT ... ) SELECT * FROM cte_name;
我的面试思路
一般结合一两个点来讲
比如 Join 没有使用索引，耗时很长，但是后续分析发现Join字段上建立了索引，为什么Join没有选取索引呢？最终分析发现了以下原因：
- 临时表破坏了原表的索引结构
- CTE物化问题破坏了原表的索引结构
再针对上述问题讲解一些优化思路。
PS: 此文章适合有一定SQL优化基础的人进阶使用，对于小白可能不是很友好，如果你有什么疑问的话，可以在评论区我们相互交流一下。（因为每个知识点都要展开讲的话基本覆盖了90%MySQL知识点了，篇幅太长）
最后，如果对你有用的话，一定不要忘记送个花花呀
查看27道真题和解析