前言
最近,确实感觉遇到转型的痛苦了,慢慢积累沉淀吧,还是理解的不够好。
- 下文的测试用例均为官网提供的
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
的数据传输流程,以及排序相关参数。
近期评论