order by 运行过程
应用场景：
很多时候会碰到需要根据指定字段排序来显示结果的需求。下面以市民表为例，简要说明 order by 在我们数据库中到底是怎么运行的。
假设一张市民表，表的定义如下：
CREATE TABLE `t` ( `id` int(11) NOT NULL, `city` varchar(16) NOT NULL, `name` varchar(16) NOT NULL, `age` int(11) NOT NULL, `addr` varchar(128) DEFAULT NULL, PRIMARY KEY (`id`), KEY `city` (`city`)) ENGINE=InnoDB; |
而你存在这样的一个查询：
select * from t where city = 'Gary Berry' order by name limit 1000; |
我们的市民表 t 是存在city 的索引的，下面查看下这个语句的执行计划：
root@172.16: [test]> explain select * from t where city = 'Gary Berry' order by name limit 1000;+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------------+| 1 | SIMPLE | t | ref | city | city | 66 | const | 13 | Using index condition; Using where; Using filesort |+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------------+ |
Extra 这个字段中的 “ using filesort” 表示的就是需要排序，MySQL会给每一个线程分配一块内存进行排序，称为 sort_buffer。
通常这个执行流程如下：
1、初始化sort_buffer,确定放入 name ，city ，age这是三个字段；
2、从索引 city 找到第一个满足 city = ‘Gary Berry’ 条件的主键id；
3、到主键 id索引取出整行，取出 name，city，age三个字段的值，存入sort_buffer 中；
4、从索引 city 取下一个记录的主键 id；
5、重复3、4直到 city 的值不满足查询条件为止；
6、对 sort_buffer 中的数据按照字段 name 做快速排序；
7、按照排序结果取前 1000行返回给客户。
sort_buffer_size，就是MySQl为排序开辟的内存（sort_buffer）的大小，如果排序的数据量小于 sort_buffer_size,排序就在内存中完成，但如果排序数量太大，内存放不下的情况，就将用到磁盘临时文件辅助排序。
所以就有了一个情况，当需要返回的字段很多的情况，sort_buffer中存放的字段太多，这时内存里能够存放的行数就会变少，要利用到很多个磁盘临时文件才能完成排序，这样就会严重影响性能~！
MySQL 认为排序的单行长度太大会怎么做呢？
介绍一个 MySQL 的系统参数：
max_length_for_sort_data : 专门控制用于排序的行数据的长度的一个参数。如果单行的长度过大，MySQL就会选择新的算法（rowid 排序算法），不放入太多的字段到内存。
下面我修改max_length_for_sort_data ，让MySQL 采用新的算法：
SET max_length_for_sort_data = 16; |
city、name、age 这三个字段的定义总长度是 36 ，max_length_for_sort_data 设置为 16；
整个的执行过程将会变成如下这个样子：
1、初始化 sort_buffer，确定放入两个字段，即 name 和 id；
2、从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
3、到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
4、从索引 city 取下一个记录的主键 id；
5、重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
6、对 sort_buffer 中的数据按照字段 name 进行排序；
7、遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
可以看出两种算法，第二种多访问了一次 t 表主键索引，会额外的造成磁盘读。
而MySQl 做排序是一个成本很高的操作，那有什么好的方法数据库可以不需要创建临时表，也不需要排序？
可以设想如果能保证从city这个索引取出的行，天然的就是按照name递增排序的话，是不是就可以不用再排序了呢？
root@172.16: [test]> alter table t add index idx_city_name(city,name);root@172.16: [test]> explain select * from t where city = 'Gary Berry' order by name limit 1000;+----+-------------+-------+------+--------------------+---------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+--------------------+---------------+---------+-------+------+-------------+| 1 | SIMPLE | t | ref | city,idx_city_name | idx_city_name | 66 | const | 13 | Using where |+----+-------------+-------+------+--------------------+---------------+---------+-------+------+-------------+1 row in set (0.00 sec) |
确实是这样的，并且这个查询的整个过程将变成如下方式：
1、从索引（idx_city_name）中找到 第一个满足 city = 'Gary Berry' 条件的主键id；
2、到主键 id 索引取出整行，取name，city，age 三个字段的值，作为结果集的一部分直接返回；
3、从索引（idx_city_name）取出下一个记录主键id；
4、重复步骤2、3，直到查到第 1000条记录，或者是不满足city = ‘Gary Berry’条件时循环结束。
上面给大家总结的是MySQL里面order by语句的几种算法流程。
开发系统过程中，总是不可避免的使用到 order by 语句，心里要清楚每个语句排序逻辑是怎么实现的，才能够分析出在最坏情况下，每个语句的执行对系统资源的消耗。
如果提高order by的速度：
1、order by 时 select * 是一个大忌（避免使用），order by的字段存在合适索引；
2、尝试提高 sort_buffer_size 但是需要根据系统能力提高，这个参数是针对每个thread的，max_length_for_sort_data 的提高要根据 sort_buffer_size 的大小合理设定。
下面列举几个order by 能用到索引以及order by 不能使用索引排序的情景：
key a_b_c(a,b,c)
order by 能使用索引最左前缀
-- order by a
-- order by a，b
-- order by a，b，c
-- order by a desc,b desc,c desc
如果 where 使用索引的最左前缀定义为常量，则order by能使用索引
-- where a = const order by b，c
-- where a = const and b =const order by c
-- where a = const order by b，c
-- where a = const and b > const order by b,c
不能使用索引进行排序
-- order by asc，b，desc，c，desc /*排序不一致*/
-- where g = const order by b，c /*丢失 a 索引*/
-- where a = const order by c /*丢失b 索引*/
-- where a = const order by a，d /* d 不是索引的一部分 */
-- where a in (...) order by b,c /* 对于排序来说，多个相等条件也是范围查询*/
