慢查询优化思路

本文介绍慢查询的优化思路，但不以索引优化作为重点，索引相关的优化可以参考《索引使用原则、索引失效》、《MySQL单表查询时索引使用情况》。

1.应用层面

对于报表类页面，一般涉及到的数据库表较多且数据量大，容易造成接口查询较慢。

如果页面还有分页的要求，一般需要先进行 count，再进行列表查询。在做 count 时可以去查询语句中无关的返回字段，去掉 order by。另外，对于 A left join B 这种情况，实际上 A 表已经包含了完整的数据行数，因此可以简化成仅对表 A 做 count。

2.SQL 层面

2.1in 与 exists 的选择

选择使用 in 还是 exists 的标准是：小表驱动大表。

比如下面这样：

select * from A where exists (select cc from B where B.cc=A.cc);
select * from A where cc in (select cc from B);

当 A 小于 B 时，用 exists。因为 exists 的实现，相当于遍历表 A，然后用每一行去表 B 中进行比较判断，实现的逻辑类似于：

# 此时扫描行数为 A + A*B，A 的大小对扫描行数影响较大。
for i in Afor j in Bif j.cc = i.cc then ...

当 B 小于 A 时，用 in。因为实现的逻辑类似于：

# 此时扫描行数为 B + B*A，B 的大小对扫描行数影响较大。
for i in Bfor j in Aif j.cc = i.cc then ...

因此，哪个表小就用哪个表作为驱动表，A 小就用 exists，B 小就用 in。

2.2避免子查询

执行子查询时，MySQL 需要为内层查询语句（即子查询）的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。且 MySQL 对子查询的优化并不好，有可能导致全表扫描。

2.3group by 优化

group by 使用索引的原则几乎跟 order by 一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。
group by 先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
where 效率高于 having，能写在 where 限定的条件就不要写在having 中
减少使用 order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct 这些语句较为耗费CPU，数据库的 CPU 资源是极其宝贵的。
包含了order by、group by、distinct 这些查询的语句都需要排序，如果用不到索引就需要进行 filesort，一般情况下 filesort 较为耗时。因此 where 条件过滤出来的结果集请保持在1000 行以内，否则SQL会比较慢。

2.4分页查询优化

对于下面的查询，仅需要返回 10 条记录，却需要对大量数据进行排序。且由于查询返回的是所有字段，因此优化器很可能会不走索引，导致用 filesort 排序，因此排序的代价非常大。

SELECT * FROM student ORDER BY id LIMIT 200000,10;

优化思路一：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

SELECT * FROM student t JOIN (SELECT id FROM student ORDER BY id LIMIT 2000000,10)a
ON t.id = a.id;

优化思路二：该方案适用于主键自增的表，可以把 Limit 查询转换成某个位置的查询。

SELECT * FROM student WHERE id > 2000000 LIMIT 10;

2.5sql 编写尽量精准

sql 编写尽量精准，像 is 代替 is not、= 代替 <>、人工条件下推、列剪枝、添加 limit 等方式，都能引导优化器生成更高效的执行计划。

下面先介绍 is 代替 is not，其他的待日后补充（我一定会回来的！）。

对于有索引的情况，用 is null 代替 is not null。假设我们有一个学生表 student 和班级表 class，学生表记录了学生的学号 stu_no，而班级表记录了班级信息以及每个班级的班长的学号 monitor_no。现在要找出所有不为班长的学生信息。实现方法如下：

方法一：

select sql_no_cache a.* from student a where a.stu_no not in (select monitor_no from class where monitor_no is not null
)

方法二：

select sql_no_cache a.* from student a left join class b on a.stu_no = b.monitor_no where b.monitor_no is null;

方法二用 join 方式替换了子查询，且通过 b.monitor_no is null 进行过滤，如果 monitor_no 上存在索引，则查询效率会高很多。

用 = 替换 <> 也是类似的道理，此处不再赘述。

3.数据库表层面

除了索引以外，可以考虑对表进行分区、分桶；行存表转列存表等。

4.服务器参数层面

4.1 FileSort 方式排序/分组优化

在 SQL 中尽量使用 Index 完成排序或分组。对于排序，如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；

如果不同就使用联合索引。但也存在无法使用 Index 的情况，此时就需要对 FileSort 方式进行调优。

调优策略：

尝试提高 sort_buffer_size，避免需要排序的字段太多导致一次操作完成不了需要多次 IO。
尝试提高 max_length_for_sort_data：由于需要返回的字段长度超出该阈值之后排序算法会从「单路排序」变为「双路排序」，而双路排序需要更多的 IO 次数。
Order by 时 select * 是一个大忌。最好只 Query 需要的字段。这和上一点也是相辅相成的。

4.2优化联表查询性能

MySQL 在进行表连接时可采用的嵌套循环连接（Nested-Loop Join）算法有：

索引嵌套循环（Index Nested-Loop Join）
简单嵌套循环（Simple Nested-Loop Join）
块嵌套循环（Block Nested-Loop Join）

其中块嵌套循环需要用到 join buffer 来存储驱动表数据（经过单表条件过滤后的数据，且只包含需要返回的字段），join buffer 越大意味着需要加载驱动表数据的次数越小。因此，如果自己的机器的内存比较大可以尝试调大 join_buffer_size 的值来对连接查询进行优化。