MySQL的深分页问题

MySQL的深分页问题是指在使用LIMIT offset, count 查询时，MySQL 会扫描 offset + count 条记录，但只返回 count 条。也就是说，MySQL 需要从磁盘或缓存中读取大量数据并丢弃前 offset 条记录。如果 offset 很大，MySQL 会进行大量无效扫描，导致性能下降。

有一个题目表topic：

select * from topic where create_time >= #{time} and type = #{type} limit 10000, 10;

这条查询需要 MySQL 扫描前 100010 条记录，丢弃前 10000 条，仅返回最后 10 条。

如果表中每条记录大小为 1KB，那么 MySQL 至少要处理 10MB 的数据才能返回 10KB 的结果。

解决办法

1.子查询

select * from topic
where id in (
    select id from (
        select id from topic
        where create_time >= #{time}
        and type = #{type}
        limit 10000, 10
    )
);

select * from topic
join (
    select id from topic
    where create_time >= #{time}
    and type = #{type}
) as t
on topic.id = t.id;

create_time, type有索引的情况下，虽然依旧有10000的id被读取出来被抛弃，但是之前10010条数据都要回表，现在只有10条数据要回表。

2.游标分页

select * from topic
where id > #{id}
and create_time >= #{time}
and type = #{type}
limit 10;

关键就在于将上一次的最后一个数据的id作为条件。

优点：性能好，适合前后翻页。缺点：无法直接跳到任意页。

3.限制分页深度

限制 offset 的最大值，避免用户访问非常深的页面。例如只允许翻到前 100 页。（这也是百度、淘宝、京东的策略）

4.使用全文搜索或 NoSQL

对于极深分页需求，可以考虑 Elasticsearch 等全文搜索引擎，或将部分非实时数据存储到 Redis/MongoDB 等 NoSQL 数据库，按需加载。