在MySQL中对大量数据进行limit offset分页查询的优化
在软件开发中采用LIMIT OFFSET
对数据库进行分页是常见操作,但在数据量很大时直接使用LIMIT OFFSET
查询尾部的数据会导致性能很慢,本文简要介绍2种改进方案。
以system_user
表为例,基于MySQL中快速创建大量测试数据一文中的介绍给其添加1000万的测试数据
|
|
基于查询SQLSELECT * FROM system_user LIMIT 9999990,10;
进行优化分析。
上述SQL在数据库中的查询耗时如下:
利用explain
分析其执行计划结果如下:
上图中的rows
这一例的数据为9722930,由于rows
这一列的值是预估值,实际上MySQL
会将数据offset+count
的数据都获取到内存中,然后再进行过滤。在本例中即会将10000000条数据都获取到然后再进行过滤筛选,而获取这么多数据显然会导致查询速度变慢!
问题根源为
MySQL
在执行LIMIT OFFSET
时会将数据全部加载到内存中然后再进行过滤,实际上执行的是一种假分页!
找到问题的根源后,要提高查询速度只能让MySQL
查询时返回的数据尽可能小,接下来根据主键是否连续自增来分别叙述。
自增主键过滤
若主键连续自增,则可从业务逻辑的角度先对数据用WHERE
过滤,然后用LIMIT
进行分页,类似SQL如下:
|
|
执行结果如下,可以看出时间明显缩短很多
进一步分析其执行计划,发现rows
这一列的值为11,只是获取了我们想要的数据,没有获取大批量数据。
采用此种方式性能提升的原因如下:
MySQL
中的主键默认有索引,基于索引查询速度很快WHERE
优先于LIMIT
执行,数据量相对之前,变得很小
其中最关键的是第2点,只要查询的数据量变小,查询速度自然会提升。
覆盖索引过滤
基于自增主键过滤要求主键必须主键连续自增,若主键不连续(如主键采用UUID
生成)则上述方案不可行,此时可基于覆盖索引来减少获取和传输的数据量大小。
将查询sql修改为类似如下
|
|
执行结果如下,可以看出耗时只比最初的少1秒
查看其执行计划,发现获取的数据量仍然很大
由于MySQL
在数据量为千万级时查询速度会变慢,将数据库表中的数据量缩小到500万,执行如下:
问题的根源在SELECT id FROM system_user LIMIT 9999990,10
,虽然此时只查询id,但是id的数量仍然很庞大,由此造成查询速度变慢。
此时可通过在数据库表中添加一列num
并对其创建唯一索引,之后基于num
进行过滤查询
|
|
改进后的sql如下
|
|
执行结果耗时如下:
可以看出其耗时和采用自增连续主键时类似。对应的执行计划如下,从图中也能看出要获取的数据量明显变小。
总结
上述两种方案归根到底均为要通过WHERE
提前过滤不需要的数据,减少返回的数据量,总结如下:
- 若主键连续且自增,则通过主键进行过滤
- 若主键不连续自增,可额外创建一个自增列或者采用
覆盖索引
的方式改写