“order by”是怎么工作的?
explain命令看语句的执行情况:
Extra 这个字段中的“Using filesort”表示的就是需要排序,MySQL 会给每个线程分配一块内存用于排序,称为 sort_buffer。
select city,name,age from t where city='杭州' order by name limit 1000 ;
通常情况下,这个语句执行流程如下所示 :
1.初始化 sort_buffer,确定放入 name、city、age 这三个字段;
2.从索引 city 找到第一个满足 city='杭州’条件的主键 id,也就是图中的 ID_X;
3.到主键 id 索引取出整行,取 name、city、age 三个字段的值,存入 sort_buffer 中;
4.从索引 city 取下一个记录的主键 id;
5.重复步骤 3、4 直到 city 的值不满足查询条件为止,对应的主键 id 也就是图中的 ID_Y;
6.对 sort_buffer 中的数据按照字段 name 做快速排序;
7.按照排序结果取前 1000 行返回给客户端。
“按name排序”这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数 sort_buffer_size。
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;
创建一个 city 和 name 的联合索引:
整个查询过程的流程就变成了:
1.从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id;
2.到主键 id 索引取出整行,取 name、city、age 三个字段的值,作为结果集的一部分直接返回;
3.从索引 city 取下一个记录主键 id;
4.重复步骤 2、3,直到查到第 1000 条记录,或者是不满足 city='杭州’条件时循环结束。
这个语句的执行流程有没有可能进一步简化呢?
创建一个 city、name 和 age 的联合索引:
1.从索引 (city,name,age) 找到第一个满足 city='杭州’条件的记录,取出其中的 city、name 和 age 这三个字段的值,作为结果集的一部分直接返回;
2.从索引(city,name,age)取下一个记录,同样取出这三个字段的值,作为结果集的一部分直接返回;
3.重复执行步骤 2,直到查到第 1000 条记录,或者是不满足 city='杭州’条件时循环结束。
Extra 字段里面多了“Using index”,表示的就是使用了覆盖索引,性能上会快很多。