limit 100w,10为什么查询慢？如何优化？

前言

在 mysql数据库 中，limit x,y 的查询中，x 值越大，那么查询速度也就越慢，例如以下示例：

• limit 0,10：我们知道，这个查询时间很快，在20毫秒左右。
• limit 1000000,10：查询时间可能是 25 秒左右（1秒=1000 毫秒），甚至更长时间。

通过这里可以看出，limit 中 x值越大，那么查询速度就越慢。

查询慢原因

这个问题其实就是 MySQL 中典型的深度分页问题。这主要是因为数据库需要扫描和跳过 x 条记录才能返回 y条结果。随着 x的增加，需要扫描和跳过的记录数也增加，从而导致性能下降。

这是因为LIMIT语句仅仅限制了结果集的返回数量，但数据库仍然需要执行全量查询，然后丢弃超出限制数量的记录。 

优化方法有以下两种：

01、起始ID定位

起始 ID 定位指的是 limit 查询时，指定起始 ID。而这个起始 ID 是上一次查询的最后一条 ID。例如上一次查询的最后一条数据的 ID 为 1000000，那我们就从 1000001 开始扫描表，直接跳过前面的 1000000 条数据，这样查询的效率就高了，具体实现 SQL 如下：

select name, age, gender from person where id > 1000000 order by id limit 10;

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

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

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

这种查询方式，只适合一页一页的数据查询，但如果用户是跳着分页的，例如查询完第 1 页之后，直接查询第 120 页，那么这种实现方式就不行了。

02、索引覆盖+子查询

未优化前的sql如下：

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

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

对于上面sql，我们可以做以下优化：

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 操作，从而提高查询性能。

当使用LIMIT子句获取大量数据时，我们可以考虑以下优化方案：

使用缓存

考虑将结果缓存到缓存系统中，以便在后续请求时候直接从缓存中获取数据，而不需要重新查询数据库。

优化查询语句

分析查询语句，确保它使用了合适的索引和优化技术。避免不必要的计算和连接操作，以减小查询的复杂度。

异步处理

如果业务允许，可以考虑将查询任务异步化，将查询结果存储到临时表中，然后异步加载到应用层。

数据库优化

考虑对数据库服务器进行性能优化，包括优化配置、增加硬件资源、调整缓冲池大小等。考虑使用数据库分片、分表等技术以提高查询性能。

分页查询

将大的结果集分割成多个小的结果集，每次只加载一页数据。使用LIMIT和OFFSET子句进行分页，例如，每次查询1000条记录。
select * from table_name limit 1000 offset 0; --第一页
select * from table_name limit 1000 offset 1000; --第二页
注意，随着OFFSET值的增加，查询性能可能逐渐降低，因为数据库需要跳过越来越多的记录。