测试数据 :

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;

order by 查询 :

select city, name, age 
from t where city = '杭州'
order by name limit 1000;

全字段排序

为避免全表扫描，要在 city上加索引

• Using filesort : 要排序 , 会用到 sort_buffer 排序内存

explain select city, name, age 
from t where city = '杭州'
order by name limit 1000;

city 索引示意图 :

• 杭州记录 : 从 ID_X 到 ID_(X+N)

全字段排序的执行流程 :

1. 初始化 sort_buffer，能放入 name、city、age 字段
2. 从索引 city 中找到满足条件的主键 id
3. 到主键索引中，取 name、city、age 值，存入 sort_buffer 中
4. 在重复 2，3步骤 ，直到条件不满足
5. 在 sort_buffer 中按 name 做快速排序
6. 再取排序前 1000 行返回给客户端

• 排序过程中，可能在内存中完成或 结合磁盘临时文件完成

查看排序是否用临时文件 ：

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a 
from  performance_schema.session_status 
where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name, age from t 
where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`;

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b 
from performance_schema.session_status 
where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

OPTIMIZER_TRACE 结果 ：

• number_of_tmp_files : 排序时 , 用临时文件数 (0 : 排序直接在内存中完成)
• examined_rows=4000 : 参与排序的行数是 4000 行
• packed_additional_fields : 排序时 , 对字符串做紧凑处理
• select @b-@a : 整个执行过程扫描了 4000 行

rowid 排序

用于排序的行数据的长度阈值，当长度太大，就换算法

SET max_length_for_sort_data = 16;

rowid 排序执行流程 :

1. 初始化 sort_buffer，确定放 name , id 字段
2. 从索引 city 中找到满足条件的主键 id
3. 到主键索引中，取 name、id值，存入 sort_buffer 中
4. 在重复 2，3步骤 ，直到条件不满足
5. 对 sort_buffer 按 name 做排序
6. 排序取前 1000 行的 ID
7. 根据 ID 回原表中取出 city、name、age 返回给客户端

• rowid 排序多一次访问表 t 的主键索引

OPTIMIZER_TRACE 变化 :

• examined_rows = 4000 : 排序的数据是 4000 行
• select @b-@a 的值 : 5000 (多了 1000 行 根据 id 去原表取值)
• sort_mode = <sort_key, rowid> : 参与排序的只有 name , id
• number_of_tmp_files = 10 : 每行变小了，排序的总数据量就变小，要临时文件也变少了

结论

MySQL 的设计思想：当内存够，就多用内存，尽量减少磁盘访问

• 排序内存太小，会影响排序效率，就用 rowid 排序 , 但要回表查
• 内存足够大，就优先用全字段排序

优化 order by :

• 联合索引 : (city, name)

alter table t add index city_user(city, name);

city/ name 联合索引 :

查询过程 ：

1. 从索引 (city, name) 找满足条件的主键 id
2. 到主键索引中取 name、city、age 值，并返回
3. 重复步骤 1 , 2，直到取到 1000 条记录，或不满足条件时 , 就结束

• name 是递增排序 , 所以不用排序

explain 结果 :

• Extra 中没有 Using filesort ， 不用排序

利用覆盖索引来优化该 SQL：

• city、name、age 的联合索引

alter table t add index city_user_age(city, name, age);

执行流程 :

1. 从索引 (city ,name, age) 找到满足条件的记录
2. 取出 city、name、age值 , 并返回
3. 重复步骤 1 , 2，直到取到 1000 条记录，或不满足条件时 , 就结束

• 覆盖索引优化：不用回表查询

执行计划 :

• Using index : 用了覆盖索引，性能会很快