在实际查询应用中,往往需要对一个或多个字段进行排序,来保证结果集数据的有序性。当我们在执行计划的Extra出现了Using filesort的时候就表示语句需要排序,MySQL会给每个线程分配一块排序内存,由参数sort_buffer_size控制大小,如果sort buffer放不下,则需要通过临时文件来进行排序,通过OPTIMIZER_TRACE可以查看SQL是否使用临时文件进行排序

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

/* 执行语句 */
select city, name,age from t where country='china' order by age;

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

number_of_tmp_files为0则表示未使用临时文件排序,examined_rows表示查询扫描的行数,sort_mode为排序模式

现有如下数据:

mysql> show create table users;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`country` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

计划查询country为china的name,age,country,并且按照age排序

mysql> select name,age,country from users where country='china' order by age;

全字段排序

在该排序模式下该语句的执行过程如下:

  • 初始化sort buffer,确定放入要查询的name,age,country
  • 从country上的索引查找第一个country=china的主键
  • 到主键索引上,取出name,age,country三个字段的值,存到sort_buffer中
  • 从country索引查询下一个符合条件的主键值
  • 重复执行上面两步,直到不满足查询条件
  • 对sort_buffer中的数据按照age进行排序
  • 返回结果集给客户端

ROWID排序

如果排序中单行的数据长度超过了max_length_for_sort_data参数,排序会采用ROWID的排序算法,ROWID排序的过程如下:

  • 初始化sort_buffer,确定要放入age和id
  • 从country索引查找第一个country=china的主键
  • 到主键索引上取出整行,将age和id放入sort_buffer
  • 从country索引获取下一行满足条件的数据
  • 重复执行上面两步,直到不满足查询条件
  • 对sort_buffer中的age进行排序
  • 遍历排序结果,回到数据表中取出对应的name,age,country返回给客户端

ROWID排序相对全字段排序减少了sort_buffer中包含的字段,能够包含更多的行数据,但ROWID多了一次主键索引的访问,增加了磁盘读。

排序优化

通常都是利用索引有序的特性来优化排序,避免额外的排序。我们可以创建country和age的联合索引,查询过程就变成下面的过程

  • 从联合索引找到第一个满足country=china的主键ID
  • 到主键索引上取出整行,把name,age,country作为结果集的一部分直接返回
  • 从联合索引取下一个主键ID
  • 重复上述两个操作,直到不满足查询条件退出

这时执行计划的Extra中就不存在Using filesort了,意味着查询不再需要排序。