MySQL SQL Tuning:深入理解Order By_MySQL

bitsCN.com

mysql sql tuning:深入理解order by

在MySQL中ORDER BY按先后顺序有2种实现方式，先走索引无排序，如果不行，则用FILESORT

走索引无排序需要满足2个条件：

①排序字段和执行计划中所利用INDEX的索引键(或前面几个索引键)完全一致

②表访问方式为index、ref或range [注释：explain输出中的Type可看出]

最快的排序就是不去排序，这也证明，索引的另一个非常重要的优势：

索引能够降低排序成本，而排序涉及到的资源有CPU和内存，也就是

索引不仅能降低IO开销，加速查询，也能极大降低CPU开销！

如果无法利用索引完成排序操作，则将走filesort，即使完全在内存中排序不需要磁盘文件亦如是

对于filesort有2种算法：

① two-pass

   原理：

   先读取行指针和排序字段，进行排序，而后依据排序结果再去读取所需要的数据

   优点：

   排序的数据量较小，完全可以在内存中完成

   缺点：

   第二次读取时，会发生大量的随机IO，太昂贵

② single-pass 

   原理：

   一次性把sql中涉及到的字段全部读出，然后依据排序字段排序，最后直接返回排序结果

   优点：

   只需一次顺序IO，无须任何随机IO，大大降低IO开销

   缺点：

   内存容不下那么多的数据，可能会先放在磁盘上，对大数据却分，单个小块排序

   排完放回磁盘，待所有单块排完，最后进行结果集merge，再返回排序结果

当查询所需列和排序列的总和大于max_length_for_sort_data时或者所需列是BLOB/TEXT，则选择two-pass

single-pass总体而言表现较佳，如果希望使用，则不妨把max_length_for_sort_data加大 

这里需要注意，计算所需列和排序列长度时，都是按最大长度给定，比如varchar，utf-8

所以，有时候甚至我们会看到排序消耗的临时存储空间比磁盘上原表要大多倍啊

另外，在Join时，order by所有列都来自关联的第一个表时，Extra有using filesort

除此之外的一切Join情况，Extra会有using temporary，using filesort 

因为MySQL会先把所有被关联的数据读到临时表，再filesort

如果别无选择只能filesort，那么如何加快order by？

①加大sort_buffer_size

②加大read_rnd_buffer_size【注释：如果是single-pass可以不必考虑】

③只选择必要的列，只为列选择适合的数据类型

④加大tmpdir