MySQL8.0 LIMIT到Using filesort（不涉及具体算法）

前言

最近，确实感觉遇到转型的痛苦了，慢慢积累沉淀吧，还是理解的不够好。

• 下文的测试用例均为官网提供的sakila数据库，附上下载链接。

LIMIT的作用

• 众所周知，limit有以下几种用途（第二个语句和第三个语句作用相同），分别如下所示：

 -- 如图1
 select * from film limit 5
 -- 如图2
 select * from film limit 6，5
 select * from film limit 5 OFFSET 6

• 以下2个图分别说明了limit的作用，红色部分为结果。

  

优化一个LIMIT语句

• 针对sakila.film表，分别对相应字段进行limit操作，使用EXPLAIN分析其执行计划。

注：1、title字段对应索引idx_title，类型为varchar(255)。

• 笔者实际工作中，遇到了一个关于LIMIT的慢查询需要优化，这里使用film表的语句对应一下，为了显示出查询时间差异，略微夸张了下参数，如下所示。

-- 根据电影题目排序，分页时使用LIMIT
select film_id, description from film order by title limit 50000, 10

• 可以先根据EXPLAIN来看其执行计划，如下

explain select film_id, description from film order by title limit 50000, 10

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film	NULL	ALL	NULL	NULL	NULL	NULL	64818	100.00	Using filesort

• 执行时间：0.15s左右

• 可以看到，type为ALL、Extra为Using filesort，使用行指针对数据进行扫描，title字段进行排序，根据OFFET的值抛弃前50000条数据，并返回LIMIT个数的数据。

• 这里就会产生一个疑问，MySQL为何没有使用idex_title的排序结果，毕竟作为一个B+树索引，已经有了排序结果，使用索引扫描岂不是更快？

• 我们可以强制MySQL使用idx_title索引，发现确实快了。。。

explain select film_id, description  from film force index(`idx_title`) order by `title` limit 50000, 10

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film	NULL	index	NULL	idx_title	767	NULL	50010	100.00	NULL

• 执行时间：0.08s左右

• 可见，order by语句是否使用索引已有排序，取决于查询优化器的决策，需要具体查看explain中的执行计划。原因在官网可以略知一二：

the query uses SELECT , which may select more columns than key_part1 and key_part2. In that case, scanning an entire index and looking up table rows to find columns not in the index may be more expensive than scanning the table and sorting the results. If so, the optimizer probably will not use the index. select包含非索引列时，使用索引扫描表并且回表查询行数据开销比 使用行指针扫描表数据并排序大时，查询优化器就不会使用索引，具体开销比较取决于查询优化器的实现。

• 一个常见的优化方式就是延迟关联，先使用覆盖索引的方式查出需要的数据的主键，再使用主键进行关联，查到需要的数据，具体语句如下：

select film_id, description  from film 
		inner join(
			select film_id from film ORDER BY title limit 50000, 10
		) a USING(film_id);

• 执行时间：0.012s左右

• 注：当然，由于OFFSET后面的数还是比较大，导致扫描的数据条数还是比较大。可以结合自己的业务尽量避免OFFSET语句。

Using filesort排序（具体算法没有涉及）

• 那么，在Using filesort时，MySQL会如何进行排序呢？

两次数据传输排序(two-pass)

• 两次数据传输排序：读取行指针和需要排序的字段，然后需要根据排序结果读取相应的行数据，如下图所示（与具体存储格式无关，只是释义）。

  

• 优点：排序存储尽可能少的数据，“排序缓冲区”（内存）中容纳更多的行。
• 缺点：两次数据传输，第二次读取产生大量随机I/O，成本较高。

一次数据传输排序(single-pass)

• 一次数据传输排序：先读取查询所需要的所有列，在根据给定列进行排序，最后直接返回排序结果。
• 优点：只需一次顺序I/O读取数据，无需附加随机I/O
• 缺点：排序冗余列，占用空间。

到底使用哪个？

• 查询中所有需要的列和ORDER BY的列总大小超过max_length_for_sort_data（8.0.20之前版本有效）字节，则采用two-pass算法；BLOB或者TEXT，即时没被ORDER BY用到，也会使用two-pass。

简单理解：冗余列大小比较大，空间成本相对较大。

show variables Like '%max_length_for%'

Variable_name	Value
max_length_for_sort_data	4096

内存还是磁盘？

取决于排序缓冲区大小设置soft_buffer_size，如果排序的数据大于排序缓冲区大小，就会使用磁盘的临时文件完成排序操作。 理想情况下，内存中完成排序的效率更高，但5.7版本的soft_buffer_size是预先分配的，一味地增加会导致内存过度使用问题，这点在MySQL 8.0.12得到了改善，优化器会根据需要情况进行内存分配（大量排序查询并发状况时例外。。。收益不大）。 还需要注意，max_sort_length决定了排序缓冲区的列大小。

小结

• SELECT包含非索引列时，Order by语句是否使用索引扫描，取决于查询优化器在具体情况下的决策，需要EXPLAIN。
• Using filesort分为single_pass和two_pass的数据传输流程，以及排序相关参数。

参考

• 高性能MySQL
• dev.mysql.com/doc/refman/…
• stackoverflow.com/questions/1…
• dev.mysql.com/doc/refman/…