假设表的定义是这样子的
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;
这时,你的SQL语句可以这么写:
select city,name,age from t where city='杭州' order by name limit 1000 ;
1.sort_buffer_size
就是MySQL为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于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中看到是否使用了临时文件。
2.rowid排序
在上面这个算法过程里面,只对原表的数据读了一遍,剩下的操作都是在sort_buffer和临时文件中执行的。但这个算法有一个问题,就是如果查询要返回的字段很多的话,那么sort_buffer里面要放的字段数太多,这样内存里能够同时放下的行数很少,要分成很多个临时文件,排序的性能会很差。
所以如果单行很大,这个方法效率不够好。
那么,如果MySQL认为排序的单行长度太大会怎么做呢?
接下来,我来修改一个参数,让MySQL采用另外一种算法
SET max_length_for_sort_data = 16;
max_length_for_sort_data,是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意思是,如果单行的长度超过这个值,MySQL就认为单行太大,要换一个算法。
city、name、age 这三个字段的定义总长度是36,我把max_length_for_sort_data设置为16,我们再来看看计算过程有什么改变。
新的算法放入sort_buffer的字段,只有要排序的列(即name字段)和主键id。
但这时,排序的结果就因为少了city和age字段的值,不能直接返回了,整个执行流程就变成如下所示的样子:
-
初始化sort_buffer,确定放入两个字段,即name和id;
-
从索引city找到第一个满足city='杭州’条件的主键id,也就是图中的ID_X;
-
到主键id索引取出整行,取name、id这两个字段,存入sort_buffer中;
-
从索引city取下一个记录的主键id;
-
重复步骤3、4直到不满足city='杭州’条件为止,也就是图中的ID_Y;
-
对sort_buffer中的数据按照字段name进行排序;
-
遍历排序结果,取前1000行,并按照id的值回到原表中取出city、name和age三个字段返回给客户端。
这个执行流程的示意图如下,我把它称为rowid排序。