MySQL数据库SQL优化之order by 语句

优化ORDER BY 语句之前，首先来了解一下MySQL中排序方式。先看customer 表上的索引情况

mysql> show index from customer;
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| customer |          0 | PRIMARY           |            1 | customer_id | A         |         599 |     NULL |   NULL |      | BTREE      |         |               | YES     |
| customer |          1 | idx_fk_store_id   |            1 | store_id    | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     |
| customer |          1 | idx_fk_address_id |            1 | address_id  | A         |         599 |     NULL |   NULL |      | BTREE      |         |               | YES     |
| customer |          1 | idx_last_name     |            1 | last_name   | A         |         599 |     NULL |   NULL |      | BTREE      |         |               | YES     |
| customer |          1 | idx_email         |            1 | email       | A         |         599 |     NULL |   NULL | YES  | BTREE      |         |               | YES     |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
5 rows in set (0.43 sec)

1.MySQL中两种排序方式

  第一种通过有序索引顺序扫描直接返回有序数据，这种方式在使用explain 分析查询的时候显示为 using index ，不需要额外的排序，操作效率高，例如：

mysql> explain select customer_id from customer order by store_id;


+----+-------------+----------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customer | NULL       | index | NULL          | idx_fk_store_id | 1       | NULL |  599 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.20 sec)

 第二种方式是通过对返回数据进行排序，也就是通常说的Filesort排序，所有不是通过索引直接返回排序结果的排序都叫Filesort排序。

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

例如：按照商店store_id 排序返回所有客户记录时，出现了对全表扫描的结果的排序：

mysql> explain select * from customer order by store_id;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | customer | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  599 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

 又如，只需要获取商店store_id 和顾客email信息时， 对表customer 的扫描就被对覆盖索引 idx_storeid_email扫描替代，此时虽然只访问了索引就足够，但是在索引idx_storeid_email 上发生了一次排序操作，所以执行计划中仍然有 Using Filesort。

mysql> alter table customer add index idx_storeid_email(store_id,email);
Query OK, 0 rows affected (1.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select store_id,email,customer_id from customer order by email;
+----+-------------+----------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key               | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+----------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | customer | NULL       | index | NULL          | idx_storeid_email | 154     | NULL |  599 |   100.00 | Using index; Using filesort |
+----+-------------+----------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

 

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

了解的MySQL的排序方式，优化目标就清晰了：

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

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

例如：查询商店编号store_id 为1，按照email 逆序排序的记录主键customer_id 时，优化器使用扫描索引idx_storeid_email 直接返回排序完毕的记录：

mysql> explain select store_id,email,customer_id from customer where store_id=1 order by email;
+----+-------------+----------+------------+------+-----------------------------------+-------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table    | partitions | type | possible_keys                     | key               | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+----------+------------+------+-----------------------------------+-------------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | customer | NULL       | ref  | idx_fk_store_id,idx_storeid_email | idx_storeid_email | 1       | const |  326 |   100.00 | Using where; Using index |
+----+-------------+----------+------------+------+-----------------------------------+-------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.04 sec)

 而查询商店编号store_id 大于等于1小于等于3，按照email 排序的记录主键customer_id的时候，由于优化器评估使用索引idx_storeid_email 进行范围扫描代价cost最低，所以最终是对索引扫描的结果，进行了额外的按照ename 逆序进行排序操作：

mysql> explain select store_id,email,customer_id from customer where store_id>=1 and store_id<=3 order by email desc;
+----+-------------+----------+------------+-------+-----------------------------------+-------------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys                     | key               | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+----------+------------+-------+-----------------------------------+-------------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | customer | NULL       | index | idx_fk_store_id,idx_storeid_email | idx_storeid_email | 154     | NULL |  599 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+----------+------------+-------+-----------------------------------+-------------------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

 

总结，下列SQL可以使用索引：

SELECT * FROM tabname ORDER BY key_part1,key_part2,...;
SELECT * FROM tabname WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC;
SELECT * FROM tabname ORDER BY key_part1 DESC,key_part2 DESC;

但是再以下几种情况下则不使用索引

SELECT * FROM tabname ORDER BY key_part1 DESC,key_part2 ASC;
---order by 的字段混合ASC 和DESC
SELECT * FROM tabname WHERE key2=constant ORDER BY key1;
---用于查询行的关键字与ORDER BY 中所使用的不相同。
SELECT * FROM tabname ORDER BY key1，key2；
---对不同的关键字使用ORDER BY;

2.Filesort 的优化

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

• 两次扫描算法（Two Passes): 首先根据条件取出排序字段和行指针信息，之后再排序区sort buffer 中排序。如果排序区sort buffer 不够，则在临时表Temporary Table 中存储排序结果。完成排序后根据行指针回表读取记录。该算法是MySQL4.1 之前采用的算法，需要两次访问数据，第一次获取排序字段和行指针信息，第二次根据行指针获取记录，尤其是第二次读取操作可能导致大量随机I/O操作；优点是排序的时候内存开销较少。
• 一次扫描算法（Single Passes）:一次性取出满足条件的行的所有字段，然后再排序区sort buffer 中排序后直接输出结果集，排序的时候内存开销比较大，但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query 语句取出的字段总大小来判断使用哪种排序算法。

如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种算法。

适当加大系统变量max_length_for_sort_data 的值，能够让MySQL 选择更优化Filesort排序算法。当然，假如max_length_for_sort_data设置过大，会造成CPU利用率过低和磁盘I/O过高，CPU和I/O利用平衡就足够了。

适当加大sort_buffer_size 排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行；当然也不能无限制加大sort_buffer_size排序区，因为sort_buffer_size 参数是每个线程独占的，设置过大，会导致服务器SWAP严重，要考虑数据库活动连接数和服务器内存的大小来适当设置排序区。

   尽量只使用必要的字段，SELECT 具体的字段名称，而不是SELECT * 选择所有字段，这样可以减少排序区的使用，提高SQL性能。