在开发时,肯定会遇到过需要字段排序显示结果的需求,以一张市民表为例:

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;

我需要查询城市为“杭州”的所有人名字,并且按照姓名排序返回前1000个人的姓名、年龄。

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

它是怎么执行的呢?

全字段排序

为了避免全表扫描,我们需要在city字段加上索引。

然后使用explain分析sql语句,可以发现在Extra中有:Using index condition; Using filesort

字段Using filesort表示需要排序,MySQL会给每一个线程分配一块内存用于排序,称为sort_buffer。

它的执行流程如下:

  1. 初始化sort_buffer,确定放入name、city、age三个字段。
  2. 从索引city中找到第一个满足city='杭州'条件的主键id
  3. 到主键id索引中取出整行,取所需字段存入sort_buffer中
  4. 从索引city继续往下取,直到不满足条件为止
  5. 对sort_buffer中数据按照字段给name做排序
  6. 取排序结果前1000行返回给客户端

这个排序过程称为全字段排序。

看回高亮处,它是怎么排序的呢?

可能在内存中完成,也可能需要外部排序,这取决于排序所需内存和参数sort_buffer_size

下面记录一下判断方法。

/* 打开 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`\G
 
/* @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;

img

可以查看OPTIMIZER_TRACE的结果确认,可以看到字段number_of_tmp_files 使用临时文件的数量。当内存放不下时,需要使用外部排序,而外部排序一般使用归并算法。可以理解为MySQL把需要排序的数据分成x分,每一份单独排序后存在这些临时文件中,最后再把这x份有序文件合并成一个大文件。

如果sort_buffer_size超过需要排序的数据量大小,那就不用临时文件,直接在内存中排序。

还有一些参数可以解释一下:

examined_rows=4000,表示参与排序的行数是4000

sort_mode的packed_additional_fields 表示排序过程对字符串做了紧凑处理,即使name字段的定义是varchar(16),排序过程还是会按照实际长度分配空间。

最后一个查询语句 select @b-@a 的返回结果是 4000,表示整个执行过程只扫描了 4000 行

查询 OPTIMIZER_TRACE 这个表时,需要用到临时表,而 internal_tmp_disk_storage_engine 的默认值是 InnoDB。如果使用的是 InnoDB 引擎的话,把数据从临时表取出来的时候,会让 Innodb_rows_read 的值加 1。所以可以把它改成MyISAM。

rowid排序

上面的算法有个问题,如果查询要返回的字段很多,那sort_buffer里面要放的字段太多,会分成多个临时表,排序性能很差。

如果单行很大,这个方法效率不高。

可以修改一个参数,让MySQL执行另一种算法:

SET max_length_for_sort_data = 16;

新算法放入sort_buffer的字段少了,只有要排序的列(即name)和主键id

那剩余需要的值,就不能直接返回,而是在最后排序出结果后,回表取出所需字段返回。

实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id,然后到原表查到 city、name 和 age 这三个字段的结果,不需要在服务端再耗费内存存储结果,是直接返回给客户端的。

这样可以使排序需要的临时文件变小了,排序更快了。

哪个更好?

如果内存够,就要多利用内存,尽量减少磁盘访问。

如果MySQL内存足够大,会优先选择全字段排序,把所有的字段放到sort_buffer中,这样就能返回查询结果了;不然就会采用rowid,可以排序更多行,但是需要回表取数据。

但想想,为什么它还要再排序一次呢,可不可以使用索引的有序性?

可以创建(city, name)的联合索引,当查到满足条件的city时,name同时也是有序的

覆盖索引是指,索引上的信息足够满足查询请求,不需要再回到主键索引上去取数据。