MySQL之排序分组的索引优化

更多知识，请移步我的小破站：http://hellofriend.top

排序优化

ORDER BY子句，尽量使用Index方式（索引）排序,避免使用FileSort方式（手工）排序。

技巧：无过滤，不索引；顺序错，必手工排序；方向反，必手工排序；

• 要想Order BY使用到索引，必须要添加过滤条件（where子句对索引中的字段进行过滤，而且必须按照顺序），Limit分页也行。
• 在SQL语句中的顺序一定要和定义索引中的字段顺序完全一致。
• 要么全升序、要么全降序。有升有降无法使用索引。

案例

SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;

可以看到，上面where条件中有范围查询，那么后面的索引会失效。

那么我们可以创建两个索引，一个是idx_age_empno（避免不了Using filesort），另一个是idx_age_name（不能让where条件充分用到索引），当这两个索引同时存在的时候，MySQL会选择谁作为最优索引呢？会选择让where子句舒服的索引，即idx_age_empno。

Using filesort有两种排序算法

一种是单路排序，一种的双路排序。

双路排序

MySQL4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 orderby 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

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

简单来说，取一批数据，要对磁盘进行了两次扫描，众所周知，I/O 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序。

单路排序

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

单路排序更快，因为使用到了内存。

分组优化

Group By 使用索引的原则几乎跟Order By一致 ，唯一区别是Group By即使没有过滤条件用到索引，也可以直接使用索引。