mysql数据库优化（三）看这里之子查询优化和排序优化

前面我们讲过了关于mysql数据库优化的两篇文章，有兴趣的小伙伴可以去看看哦。在之前的基础上我们今天来讲一讲子查询优化以及排序优化。

目录

子查询优化

排序优化

filesort算法：双路排序和单路排序

---

子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结 果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。

子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子 查询的执行效率不高。原因：

① 执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表 ，然后外层查询语句从临时表 中查询记录。查询完毕后，再 撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会 受到一定的影响。

③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询 不需要建立临时表 ，其 速度比子查询 要快 ，如果查询中使用索引的话，性能就会更好。

之前我们在数据库中建立了两张表学生表和班级表，它们表结构如下

 

举例1：查询学生表中班长的学生信息

-- 创建班级表中的班长索引
-- 语句一
ALTER TABLE `class` ADD INDEX idx_monitor(monitor)
-- 查看班级表的索引
SHOW INDEX FROM class
-- 查询学生表中的班长信息
EXPLAIN SELECT * FROM student s
WHERE s.stuno IN(
		SELECT monitor
		FROM class c
		WHERE monitor IS NOT NULL
		);

-- 推荐使用多表查询
-- 语句二
EXPLAIN SELECT 	s.* FROM student s JOIN class c
ON s.stuno=c.monitor;	

语句一分析结果如下，其多一个临时表

语句二没有临时表

 

 

结论：尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

排序优化

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？

回答：在mysql中，支持两种排序方式，分别是Filesort和Index排序

①Index排序中，索引可以保证数据有序性，不需要在进行排序，效率较高

②Filesort排序则一般在内存中进行排序，占用CPU较多，如果待排结果较大，会产生临时文件IO到磁盘进行排序的情况，效率较低。

优化建议

1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫 描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排 序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。

2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列； 如果不同就使用联合索引。

3. 无法使用 Index 时，需要对 FileSort 方式进行调优。

案例：

删除掉student以及class上的所有索引

过程一：

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classId;

 

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classId LIMIT 10;

 过程二：order by时，不limit，索引失效

-- 创建索引
CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

-- 不限制，索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classId;

 

#增加limit条件，使用上了索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classId LIMIT 10;

 过程三：order by时，顺序 错误 以及规则不一致 索引失效

-- 创建索引age classId stuno
CREATE INDEX idx_age_classid_stuno ON student(age,classId,stuno);

-- 不会使用到索引，age没有加入，与我们之前讲到的最左前缀匹配矛盾
EXPLAIN SELECT * FROM student ORDER BY classId LIMIT 10;

-- 不会使用到索引，age没有加入，与我们之前讲到的最左前缀匹配矛盾
EXPLAIN SELECT * FROM student ORDER BY classId,NAME LIMIT 10;

-- 会使用
EXPLAIN SELECT * FROM student ORDER BY age, classId,stuno LIMIT 10;

-- 不会使用，因为我们的索引都是默认的asc ，而语句中出现了desc
EXPLAIN SELECT * FROM student ORDER BY age, classId DESC,stuno LIMIT 10;

-- 会使用，虽然索引都是asc，但是我们order by中都是desc而不是只有某一个是desc
EXPLAIN SELECT * FROM student ORDER BY age DESC, classId DESC,stuno DESC LIMIT 10;

-- 会使用
EXPLAIN SELECT * FROM student ORDER BY age,classId LIMIT 10;

-- 会使用
EXPLAIN SELECT * FROM student ORDER BY age LIMIT 10;

过滤四： 无过滤，不索引

-- yes
EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classId;

-- yes
EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classId, NAME;

-- no
EXPLAIN SELECT * FROM student WHERE classId=45 ORDER BY age;

-- yes

EXPLAIN SELECT * FROM student WHERE classId=45 ORDER BY age LIMIT 10;

小结

 实战

order by子句，尽量使用index方式排序，避免使用Filesort方式排序

执行案例前先清楚student表上的索引。

场景：查询年龄为30岁的且学生编号小于101000的学生 按用户名排序

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND stuno<101000 ORDER BY NAME;

运行后的结果为

 

方案一：为了去掉Filesort我们可以建立索引

CREATE INDEX idx_age_name ON student(age,NAME);

然后

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND stuno<101000 ORDER BY NAME;

看结果，我们去除了Filesort且使用了索引

且查询效率提升

 

 方案二 

CREATE INDEX idx_age_stuno_name ON student(age,stuno,NAME);


EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND stuno<101000 ORDER BY NAME;

 

 结果竟然有Filesort的sql运行速度超过了已经优化掉filesort的sql，而且快了很多，几乎一瞬间就出现了结果。

原因：所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤掉大部分数据的话，剩下几百几千条数据进行排序其实不是很消耗性能，即使索引优化了排序，但实际提升性能有限。相对的stuno<101000这个条件，如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个紫断殇性价比最高，是最优选择。

结论：

1. 两个索引同时存在，mysql自动选择最优的方案。（对于这个例子，mysql选择 idx_age_stuno_name）。但是， 随着数据量的变化，选择的索引也会随之变化的 。

2. 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过

滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段

上。反之，亦然。

filesort算法：双路排序和单路排序

排序的字段如果不再索引列上，则Filesort会有两种算法 双路排序和单路排序

双路排序 （慢）

①MySQL 4.1之前是使用双路排序 ，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和

order by列 ，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取

对应的数据输出

②从磁盘取排序字段，在buffer进行排序，再从 磁盘取其他字段 。

单路排序 （快）

从磁盘读取查询需要的 所有列 ，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输 出， 它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空 间， 因为它把每一行都保存在内存中了。取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种 改进的算法，就是单路排序。

结论及引申出的问题

由于单路是后出的，总体而言好过双路

但是用单路有问题①在sort_buffer中，单路比多路要占用很多空间，因为单路是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer大小的数据进行排序，排序完再取那么容量大小，再排...从而多次IO②单路本来想升一次IO，反而导致了大量的IO操作。

优化策略

1. 尝试提高 sort_buffer_size

不管用哪种方法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程的调整。从mysql5.7，innodb默认值为1M

2. 尝试提高 max_length_for_sort_data

提高这个参数，会增加用改进算法的概率。

但是如果设的太高数据总量超出sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。如果需要返回的列的总长度大于max_length_for_sort_data，使用双路算法，否则使用单路

3. Order by 时select * 是一个大忌。最好只Query需要的字段。

①当query的字段大小总和小于max_length_for_sort_data而且排序字段不是text和blob类型时，会用改进后的算法--单路算法，否则使用老算法--多路算法

②两种算法的数据都是有可能超出sort_buffer_size容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是用单路排序算法的风险更大一下，所以要提高sort_buffer_size