【MySQL进阶之路 | 高级篇】子查询优化和排序优化

1. 子查询优化

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

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

1. 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的cpu和IO资源，产生大量的慢查询。
2. 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定影响。
3. 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

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

2. 排序优化

在where条件列上添加索引，但为什么还要在ORDER BY字段上添加索引呢。

在MySQL中 ，支持两种排序方式，分别是文件排序（FileSort）和索引排序(IndexSort).

• index排序中，索引可以保证数据的有序性，不需要再进行排序。
• 文件排序则一般在内存中进行排序，占用cpu太多。如果待排结果较大，会产生临时文件IO到磁盘进行排序的情况，效率较低。

1). 优化建议

• SQL中，可以在where子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，我们还是避免，以提高查询速度。
• 尽量使用Index完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单列索引；如果不同就使用联合索引。
• 在无法使用Index时，需要对FileSort方式进行调优。

2). 小结

1.

2.

所有排序都是在过滤条件之后才执行的。如果条件过滤到大部分的数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。相较于在where语句中没有用到索引的话，要对几万条甚至更多的数据进行扫描，这才是非常耗费性能的。所以索引放在where语句需要的字段上性价比最高。

如果两个索引同时存在（where字段上的索引，order by字段上的索引），MySQL自动选择最优的方案。但随着数据量的变化，选择的索引也会随之变化。

但当范围条件和order by和group by的字段出现二选一的时候，优先观察条件字段上的过滤数量，如果过滤的数据足够多，而需要排序的数据不多的话（而此时使用文件排序的速度也很快），优先把索引放在范围字段的上面。反之亦然。