每天进步一点点——优化order by 语句

优化ORDER BY语句前，首先了解下MySQL中的排序方式。

mysql>show index from customer\G;

*************************** 1. row***************************

       Table: customer

  Non_unique: 0

    Key_name: PRIMARY

 Seq_in_index: 1

 Column_name: customer_id

   Collation: A

 Cardinality: 599

    Sub_part: NULL

      Packed: NULL

        Null:

  Index_type: BTREE

     Comment:

Index_comment:

*************************** 2. row***************************

       Table: customer

  Non_unique: 0

    Key_name: uk_email

 Seq_in_index: 1

 Column_name: email

   Collation: A

 Cardinality: 599

    Sub_part: NULL

      Packed: NULL

        Null: YES

  Index_type: BTREE

     Comment:

Index_comment:

*************************** 3. row***************************

       Table: customer

  Non_unique: 1

    Key_name: idx_fk_store_id

 Seq_in_index: 1

 Column_name: store_id

   Collation: A

 Cardinality: 4

    Sub_part: NULL

       Packed: NULL

        Null:

  Index_type: BTREE

     Comment:

Index_comment:

*************************** 4. row***************************

       Table: customer

  Non_unique: 1

    Key_name: idx_fk_address_id

 Seq_in_index: 1

 Column_name: address_id

   Collation: A

 Cardinality: 599

    Sub_part: NULL

      Packed: NULL

        Null:

  Index_type: BTREE

     Comment:

Index_comment:

*************************** 5. row***************************

       Table: customer

  Non_unique: 1

    Key_name: idx_last_name

 Seq_in_index: 1

 Column_name: last_name

   Collation: A

 Cardinality: 599

    Sub_part: NULL

      Packed: NULL

        Null:

  Index_type: BTREE

     Comment:

Index_comment:

*************************** 6. row ***************************

       Table: customer

  Non_unique: 1

    Key_name: idx_email

 Seq_in_index: 1

 Column_name: email

   Collation: A

 Cardinality: 599

    Sub_part: NULL

      Packed: NULL

        Null: YES

  Index_type: BTREE

     Comment:

Index_comment:

6 rows in set (0.00 sec)

 

ERROR:

No query specified

 

第一种通过有序索引顺序扫描直接返回有序数据

mysql>explain select customer_id from customer order by store_id\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: customer

         type: index

possible_keys: NULL

         key: idx_fk_store_id

     key_len: 1

         ref: NULL

        rows: 599

       Extra: Using index

1 row in set (0.00 sec)

 

ERROR:

No query specified

第二种通过对返回数据进行排序，也就是通常说的FILESORT排序

所有不通过索引直接返回的排序结果的排序都叫走filesort排序

filesort并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，是否使用磁盘文件或者临时表，则取决于MySQL服务器对排序参数的设置和需要排序的数据大小。

如下面查询语句使用了全表扫描的排序结果。

mysql>explain select * from customer order by store_id\G;

*************************** 1. row ***************************

          id: 1

 select_type: SIMPLE

       table: customer

        type: ALL

possible_keys: NULL

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 599

       Extra: Using filesort

1 row in set (0.00 sec)

又如虽然至访问了索引就足够，但是在索引上发生了一次排序，所以执行计划中让然有Using filesort

mysql>alter table customer add index idx_storeid_email(store_id,email);

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0  Warnings: 0

mysql>explain select store_id,email,customer_id from customer order by email\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: customer

        type: index

possible_keys: NULL

         key: idx_storeid_email

     key_len: 154

          ref: NULL

        rows: 599

       Extra: Using index; Using filesort

1 row in set (0.00 sec)

 

ERROR:

No query specified

filesort是通过相应的排序算法，取得数据在sort_buffer_size系统变量设置的内存排序去中进行排序，如果内存实在装不下，它就会将磁盘上的数据进行分块，再对各个数据进行排序，然后将各个块合并成有序的结果集。sort_buffer_size设置的排序区是每个线程独占的，所以同一时刻MySQL中存在多个sort buffer排序区。

 

所以MySQL排序方式优化目标为：尽量减少额外的排序，通过索引直接返回有序数据。

 

where条件和order by使用的相同的索引，并且orderby的顺序和索引顺序相同，并且order by的字段都是升序或者降序。否则肯定需要额外的排序操作，这样就会出现filesort。

 

 

mysql>explain select store_id,email,customer_id from customer where store_id=1 orderby email desc\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: customer

        type: ref

possible_keys:idx_fk_store_id,idx_storeid_email

         key: idx_storeid_email

     key_len: 1

         ref: const

        rows: 325

        Extra: Using where; Using index

1 row in set (0.00 sec)

 

ERROR:

No query specified

上面例子优化器使用扫描索引idx_storeid_email直接返回排序完毕的记录

 

 

总结：以下ＳＱＬ可以使用索引

select* from 表名　order bykey_part1,key_part2,.......;

select* from 表名         where key_part1=1 order by key_part2DESC key_part2 DESC;

select* from 表名　ORDER BYkey_part1 DESC,key_part2 DESC;

但是以下集中情况则不使用索引

select* from 表名　ORDER BYkey_part1 DESC,key_part2 ASC;

————order by的混合字段ASC和DESC

select* from 表名 where key2=constantORDER BY key1;

————用于查询行的关键字与ORDER BY中所使用的不同】

select* from 表名 ORDER BYkey1,key2;

————对不同关键字使用ORDER BY；

Filesort的优化

通过创建合适的索引，能够减少FILESORT出现，但是在某些情况下，条件限制不能让filesort消失，那就需要想办法加快filesort操作，对于filesort，MySQL有两种排序算法

两次扫描算法

首先根据条件取出排序字段和行指针信息，之后在排序区sort buffer中排序。如果排序去不够则在临时表中存储排序结果。完成排序后根据行指针回表读取记录。第一次排序获取排序字段和行指针信息，第二次根据指针获取记录，尤其是第二次读取操作可能导致大量的I/O操作，优点是排序的时候内存开销较少。

一次扫描算法

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

 

MySQL通过比较系统变量max_length_for_sort_data的大小和query语句取出的字段总大小来判断使用那种排序算法，如果max_length_for_sort_data更大，那么使用第二宗优化之后的算法，否则使用第一种算法。适当的加大系统变量max_length_for_sort_data的值，能够让MySQL选择更优化的filesort排序，但是设置过大会导致CPU利用率过低和磁盘I/O过高。适当加大sort_buffer_size排序区，尽量让排序的内存中完成，而不是通过创建临时表放在文件中进行；然而也不能无限制加大sort_buffer_size排序去，因为sort_buffer_size参数是每个线程独占的，过大会导致服务器SWAP严重。尽量只使用必要的字段，select具体的字段名称，而不是select *选择所有字段，这样也可以减少排序去的使用，提高SQL性能。