mysql调优之索引——ORDER BY（GROUP BY）

order by的排序优化

1、ORDER BY 子句尽量使用index方式排序，避免使用filesort方式排序。

2、ORDER BY 满足两种方式会使用index方式排序：

• order by使用索引最左前列
• 使用where 子句与order by 子句条件列组合满足索引最左前列

3、如果不在索引列上，filesort有两种算法，mysql就要启动双路和单路排序。

• 双路排序
  （1）mysql4.1之前是使用双路排序，两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从数据库列表中读取对应的数据输出。
  （2）从磁盘读取字段，在buffer中进行排序，再从磁盘取其他字段。
  （3）取一批数据，要扫描两次磁盘，进行两次I/O操作，由于I/O操作很耗时，索引在4.1之后采用另一种算法，单路排序。
• 单路排序
  （1） 从磁盘中读取查询所需要的列，按照order by列在buffer进行排序，然后扫描排序后的列表进行输出，它的效率更高一点，避免了第二次读取数据。并且随机I/O变成了顺序I/O，但是它会使用更大的内存空间，因为它把数据都保存在内存当中。
• 注意
  在sort buffer中，单路排序比双路排序使用了更多的内存空间，因为单路排序把所有字段都取出，所有有可能导致取出的数据总大小超出sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据进行排序（创建tmp，多路合并），排完再取sort_buffer容量大小，再排序......，导致多次I/O.

4、提高ORDER BY效率

（1）ORDER BY时只查找需要的字段，禁忌select * ，这很重要，这里的影响是：

• 当查找的字段总和小于max_length_for_sort_data，且排序字段不是TEXT或者BLOB，会用改进的算法单路排序，否则使用多路排序。
• 两种算法都有可能超出sort_buffer的容量，超出后，会创建tmp文件进行合并排序，导致多次I/O，但是单路算法的风要高一些，所以此时要适当调大sort_buffer_size.
  （2） 尝试提高sort_buffer_size的大小
  不管哪种算法，适当提高sort_buffer_size，都会提高效率，但是要根据系统的能力去提高，因为这个参数是针对每个进程的。
  （3）尝试提高max_length_for_sort_data
  提高这个参数，会增加使用单路排序算法的概率，如果设的太高，数据总容量超出sort_buffer_size的概率就会增大，明显症状是高的I/O的活动和低的处理器使用率。

GROUP BY 与之类似

总结：

order by.png 微信公众号欢迎关注.jpg