子查询和排序优化

子查询优化

        子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作； 但是，子查询的执行效率不高 。原因如下：

• 执行子查询时，MySQL需要为内层查询语句的执行结果建立一个临时表 ，外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询；
• 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引 ，所以查询性能会受到一定的影响；

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

 排序优化

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

        优化建议：

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

        结论：

• type 是 ALL，最坏的情况。Extra 里出现了 Using filesort，也是最坏的情况；
• 两个索引同时存在，mysql自动选择最优的方案；
• 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过
  滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然；

 filesort算法：双路排序和单路排序（了解）

        双路排序 （慢）

• MySQL 4.1之前是使用双路排序 ，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和order by列 ，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
• 从磁盘取排序字段，在buffer进行排序，再从 磁盘取其他字段 。

        取一批数据，要对磁盘进行两次扫描，IO很耗时，所以在mysql4.1之后，出现了单路排序。

        单路排序 （快）

        从磁盘读取查询需要的 所有列 ，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但它会使用更多的空间，因为它把每一行都保存在内存中了。