【MySQL】子查询优化、排序优化和覆盖索引

一、子查询优化

  子查询可以通过一个SQL语句实现比较复杂的查询。但是子查询的效率不高。原因在于

• 执行子查询时，MySQL为内层查询语句的结果建立一个临时表，在查询结束后，会撤销这些临时表。这就导致消耗过多的CPU和IO资源，产生大量的慢查询。
• 子查询产生的临时表不会存在索引，索引查询性能较低。

二、排序优化

  在MySQL中支持两种排序方式，分别是FileSort和IndexSort。

• Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
• FileSort排序一般在内存中进行，占用CPU较多。且如果待排结果过大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

  在SQL中使用索引到WHERE以及GROUP BY中的目的是，避免在WHERE子句中进行全表扫描，且避免OEDER BY排序进行FileSort排序。
  但并不是所有的FileSort排序的执行效率都低于Index，原因在于所有的排序都是在条件过滤之后执行的，如果过滤了大部分数据后只剩下几百几千条数据，那么Index也无法进行显著的提升。

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

  当要排序的字段不在索引列上时，filesort有两种算法：双路排序和单路排序
双路排序
  双路排序会进行两次磁盘扫描，第一次从磁盘去除排序字段并进行排序，第二次根据buffer中排序完的字段从磁盘中读取其他字段的值。双路排序对一批数据进行排序要扫描两次磁盘，非常耗时。

单路排序
  单路排序从磁盘中读取查询需要的所有列，在buffer中对它们进行排序后输出，其效率更快，但是需要更多的内存空间。由于其需要更多得内存空间，所以也存在一定的问题，如果内存空间无法满足，则需要更多的磁盘IO来支持该算法。

优化策略

• 提高sort_buffer_size
    不管是单路排序还是双路排序，提高这个参数都能提高效率

• 提高max_length_for_sort_data
    提高这个参数会增加使用改进算法的概率，但是如果设置的过高，那么数据总容量超出sort_buffer_size的概率也就越大，会带来频繁的IO活动影响效率。

• 最好只query需要的字段，而不是SELECT *
    查询过多的字段很容易导致数据总量超出sort_buffer_size，从而导致过多的磁盘IO。

ORDER BY、GROUP BY、distinct这些语句较为耗费CPU，尽量不用

三、覆盖索引

  覆盖索引指的是我们使用SELECT查询的列是构建索引时使用的列。

CREATE INDEX idx_age_name ON student(age, NAME);

EXPLAIN SELECT age, NAME FROM student WHERE age <> 20;

  在上面这个例子中，使用索引后可以节省回表操作，花费的时间小于全表扫描的时间，因此即使有age <> 20存在，优化器也使用了索引。

3.1、覆盖索引的利弊

好处

• 避免了Innodb表进行索引的二次查询（回表），有效较少了IO操作。
• 可以把随机IO变成顺序IO加快查询效率。
• 由于覆盖索引可以显著减少树的搜索次数，所以使用覆盖索引是一个常用的性能优化手段。

弊端

• 索引字段的维护有代价，建立冗余索引来支持覆盖索引时会占用一部分内存空间。