MySQL深度分页问题：为什么LIMIT越来越慢？如何优化？

创作时间:

作者:

@小白创作中心

MySQL深度分页问题：为什么LIMIT越来越慢？如何优化？

引用

来源

https://www.cnblogs.com/vipstone/p/18399160

在MySQL中，当使用LIMIT进行分页查询时，随着偏移量的增加，查询速度会显著变慢。例如，limit 0,10的查询时间可能在20毫秒左右，而limit 1000000,10的查询时间可能达到15秒甚至更长。这种现象被称为深度分页问题，其本质是数据库需要扫描和跳过大量记录才能返回所需结果。本文将深入探讨这一问题的原因，并介绍两种常见的优化方法。

为什么LIMIT越来越慢？

在数据库查询中，当使用LIMIT x, y进行分页查询时，如果x值越大，查询速度可能会变慢。这主要是因为数据库需要扫描和跳过x条记录才能返回y条结果。随着x的增加，需要扫描和跳过的记录数也增加，从而导致性能下降。

例如，limit 1000000,10需要扫描1000010行数据，然后丢掉前面的1000000行记录，所以查询速度就会很慢。

优化手段

对于MySQL深度分页问题，常见的优化手段有两种：

1. 起始ID定位法

起始ID定位法指的是在使用LIMIT查询时，指定起始ID。这个起始ID是上一次查询的最后一条ID。例如，如果上一次查询的最后一条数据的ID为6800000，那么就可以从6800001开始扫描表，直接跳过前面的6800000条数据，这样查询的效率就高了。具体实现SQL如下：

select name, age, gender
from person
where id > 6800000 -- 核心实现SQL
order by id limit 10;

其中id字段为表的主键字段。

为什么起始ID查询效率高呢？

这种查询方式以上一次查询的最后ID作为起始ID进行查询，由于上次的ID已经定位到具体的位置，所以只需要遍历B+树叶子节点的双向链表（主键索引的底层数据结构）就可以查询到后面的数据，因此查询效率较高。如下图所示：

如果上次查询结果为9，之后再查询时，只需要从9之后再遍历N条数据就能查询出结果，所以效率就很高。

优缺点分析

这种查询方式只适合一页一页的数据查询，例如手机APP中刷新闻时那种瀑布流方式。但如果用户是跳着分页的，例如查询完第1页之后，直接查询第250页，那么这种实现方式就不行了。

2. 索引覆盖+子查询

为了提高查询效率，可以使用索引覆盖加子查询的方式。假设未优化前的SQL如下：

select name, age, gender
from person
order by createtime desc 
limit 1000000,10;

在以上SQL中，createtime字段创建了索引，但查询效率依然很慢，因为它要取出100万条完整数据，并需要读取大量的索引页，和进行频繁的回表查询，所以执行效率会很低。

此时，可以做以下优化：

SELECT p1.name, p1.age, p1.gender
FROM person p1
JOIN (
    SELECT id FROM person ORDER BY createtime desc LIMIT 1000000, 10
) AS p2 ON p1.id = p2.id;

相比于优化前的SQL，优化后的SQL将不需要频繁回表查询，因为子查询中只查询主键ID，这时可以使用索引覆盖来实现。子查询可以先查询出一小部分主键ID，再进行查询，这样就可以大大提升查询的效率。

索引覆盖（Index Coverage）是一种数据库查询优化技术，它指的是在执行查询时，数据库引擎可以直接从索引中获取所有需要的数据，而不需要再回表（访问主键索引或者表中的实际数据行）来获取额外的信息。这种方式可以减少磁盘I/O操作，从而提高查询性能。