在开发时,肯定会遇到过需要字段排序显示结果的需求,以一张市民表为例:
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。
它的执行流程如下:
- 初始化sort_buffer,确定放入name、city、age三个字段。
- 从索引city中找到第一个满足city='杭州'条件的主键id
- 到主键id索引中取出整行,取所需字段存入sort_buffer中
- 从索引city继续往下取,直到不满足条件为止
- 对sort_buffer中数据按照字段给name做排序
- 取排序结果前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;
可以查看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同时也是有序的
覆盖索引是指,索引上的信息足够满足查询请求,不需要再回到主键索引上去取数据。