本文共 8684 字，大约阅读时间需要 28 分钟。
优化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: 0mysql> 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 和DESCSELECT * 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有两种排序算法。
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性能。
转载地址：http://zcfoi.baihongyu.com/