order by 查询

Question

1：课程中，老师说 order by 在查询的时候，首先是根据我们的查询条件等，查询出中间数据。然后再对中间数据排序。
对于中间数据排序，如果中间数据量小，就在内存中排序，如果数据量很大，就会起临时文件进行排序。
这也是我们对于order by 查询的一个优化点。就是 调整 sort_buffer_size 的大小。

我调整了 sort_buffer_size 的大小为2M 。

然后进行查询

这个查询中只有2条结果。也就是我们使用order by 的一个中间结果。

我在该查询语句后面加上 order by 排序

发现，居然起了临时文件。

问题：select title,film_id from film where film_id>998; 这个sql 只有2条数据结果。
也就是中间结果集只有2条数据。
EXPLAIN select title,film_id from film where film_id>998 ORDER BY title; 加上 order by 排序之后，也就是相当于对中间结果集2条数据再进行一次排序。

这么2条数据，我的sort_buffer_size 都已经设置为2M 了，肯定不用起临时文件啊，但是为什么 执行计划中显示还是 using filesort ?

望老师解答？

Answer 1

Moody 2023-06-27 11:05:54

explain的结果不一定准确，可以用下面的方法看一下实际用没用临时文件：

* 打开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

输出的格式类似：

这里的12，就是使用的临时文件数量，如果是0，就是没有使用临时文件