MyBatis 百万级 Limit 分页优化，不加索引性能最低提升 6 倍 - 今日头条

本文由简悦 SimpRead 转码，原文地址 www.toutiao.com

SELECTid,title,content FROM items WHERE id IN ; 如果 limit 语句的 offset 较大，你可以通过传

在实际开发中分页查询是最常用的场景之一，但也通常也是最容易出问题的地方。DBA 的同事给出的解决方法就是加 “索引 / 组合索引”，例如在 name,create_time,status 等字段上加组合索引，这样根据这些条件查询的时候能够有效的利用索引，性能迅速的就提高了。

以上做法对 select * from table limit 0,10 这个没有问题，但是当 limit 1000000,10 的时候数据读取就很慢了。我只想要 10 条数据但是每次查询都扫描了 100 多万行，在高频访问下堵的死死的。那么有什么办法解决这个问题呢？

前提：数据库主键 ID 为自增

正常分页 SQL 语句为：

1

select id,name,content,create_time from users order by id asc limit 1000000,10

优化后 SQL 语句为：

1

select id,name,content,create_time from users where id > 1000000 order by id asc limit 10

看到这里大家应该明白了吧？进行查询的时候可以将上一页的最大值 ID 当成参数作为查询条件的，那么当次查询只需要扫描最大 ID 开始的 20 行数据即可。

那么有的同学会问如果设计的表主键不是自增又该怎么办？这里还有一种方法根据数据的入库时间，具体方法和上面雷同。

1

select id,name,content,create_time from users where id > '2020-01-01 12:29:23' order by id asc limit 10

这样查询的时间基本固定，并且也不会随着数据量的增长而发生变化。

如下图所示，小编准备了 300w + 的数据，这张表共有 28 个字段。

正常分页所需时长：

从 200w 开始获取 10 条数据共花费 12s 多。

根据入库时间进行分页：

这里的查询时间是上面 SQL 查询出来结果的 create_time，可以看到花费了 2s 多。性能提升了约 6 倍！

备注：create_time 字段没有增加索引。

当一个数据库表过于庞大，LIMIT offset, length 中的 offset 值过大，则 SQL 查询语句会非常缓慢，你需增加 order by，并且 order by 字段需要建立索引。如果使用子查询去优化 LIMIT 的话，则子查询必须是连续的，某种意义来讲，子查询不应该有 where 条件，where 会过滤数据，使数据失去连续性。如果你查询的记录比较大，并且数据传输量比较大，比如包含了 text 类型的 field，则可以通过建立子查询。

1

SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);

如果 limit 语句的 offset 较大，你可以通过传递 pk 键值来减小 offset = 0，这个主键最好是 int 类型并且 auto_increment

1

SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;

这条语句，大意如下:

SELECT * FROM users WHERE uid >= (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10; 如果 limit 的 offset 值过大，用户也会翻页疲劳，你可以设置一个 offset 最大的，超过了可以另行处理，一般连续翻页过大，用户体验很差，则应该提供更优的用户体验给用户。