mysql优化—order by

MySql中的两种排序方式

1. 通过有序索引顺序扫描直接返回有序数据

因为索引的结构是B+树，索引中的数据是按照一定顺序排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作。Explain分析查询时，type显示为index。

2. Filesort排序，对返回的数据进行排序

所有不是通过索引直接返回排序结果的操作都是Filesort排序，也就是说进行了额外的排序操作。Explain分析查询时，Extra显示为Using filesort，Filesort有两种排序算法，如下：

2.1 两次扫描算法

首先根据条件取出排序字段和行指针信息，之后在排序区sort buffer中排序。这种排序算法需要访问两次数据，第一次获取排序字段和行指针信息，第二次根据行指针获取记录，此时可能会导致大量随机I/O操作。有点是排序的时候内存开销较小。

2.2 一次扫描算法

一次性取出满足条件的行的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序的时候开销比较大，但排序效率比两次扫描算法要高。

根据两种排序算法的特性，适当加大系统变量max_length_for_sort_data的 ，能够让Mysql选择更优的Filesort排序算法。并且在书写SQL语句时，只使用需要的字段，而不是select * 所有字段，这样可以减少排序区的使用，提高SQL性能。

Order By优化的核心原则

尽量减少额外的排序，通过索引直接返回有序数据。

注意：索引的选择，由以下几个因素指定：

a. 简单查询，无其它条件(where/order by/group by/limit),看查询字段，选包含所有查询字段且信息量最大的索引，如果走的是复合索引，查询字段的顺序不会影响索引的选择，顺序按照索引顺序逐级排序(不一定按照你的查询字段排序)。

  例1：province_id字段被索引，选用该索引，走覆盖索引，数据按province_id升序排列,

           (即使只查询id字段，也会选择province_id索引)

       explain select id, province_id from city;

         例2：由于没有任何一个索引中包含province_id和city_name两个字段，索引不会命中任何一个索引

             explain select province_id, city_name from city;

      

     b. 带where条件查询，看where条件字段是否命中索引(覆盖索引一定命中索引/复合索引要符合最左前缀原则/非覆盖索引要看结果集大小)

       例1：city_name没有被索引，所以会全表查询

          explain select * from city where city_name = 2;