如何优化大表分页查询的Limit性能问题?

关注“Java艺术”一起来充电吧!

完成需求，不只在于速度，更要考虑质量。在规定的时间内完成需求的情况下，如果还有时间，应当想一下还能怎么优化，从性能、代码可读性、可扩展性层面考虑。进步就是这样一点点积累的。

昨天写的那篇有点小失误，在此修正一下。其实mybatis-plus提供的优化器做得非常好，它不仅仅只是在优化后的sql之上加一层select count(1) from (原sql ) as total，而是直接优化为select count(1) from... where...，优化后的查询总数的sql性能更优。

但是limit的问题，mybatis-plus优化器是解决不的。如果表的数据量非常大，我们除了优化查询总数的sql之外，还是需要优化limit的。

我看到过很多文章介绍说通过id优化提升limit性能，他们所举的例子都是单表查询。之所以能只查单表，是因为表的设计上已经通过冗余字段去掉多表连接查询了，这也是值得推荐且主流的优化大表查询性能的做法。

通过在设计表的时候加上冗余字段，去掉多表链接查询，使用id优化提升limit性能的例子如下：

SELECT a.字段
FROM table a
RIGHT JOIN
(
SELECT id -- 只查id列
FROM table
WHERE .....(非聚簇索引的条件查询)
LIMIT 1000000,20
) as b ON b.id = a.id


原SQL是:

SELECT 需要获取的字段
FROM table
WHERE .....(非聚簇索引的条件查询)
LIMIT 1000000,20


由于非聚簇索引的叶子节点上面存储的是主键的id，因此，如果select只查主键，那么就不需要根据主键id再到聚簇索引上面获取记录信息，而如果select需要查询除主键外的其它字段信息，就必须要到聚簇索引上面取记录信息。

limit是在查询结果基础上跳过多少条记录，也就是说，跳过多少条记录也是需要查询这些记录的信息的。如limit 10000,20，那么就需要10020次根据主键id到聚簇索引上面取记录信息，而select id就能减少这10020次查询，因为非聚簇索引上面存的就是主键id。前提是where后面的条件必须确保都是走索引，在全表扫描下，任何优化都是徒劳。

根据这个思路，我优化了下最近做的一个需求的分页查询。虽然是需要多表连接查询，但where部分条件是在主表上面筛选的，或者是通过优化手段，转为只在主表上面进行条件筛选的，因此也适合使用这种通过id优化limit性能的方案。

改造就是将原来的sql拆分为两部分。第一部分是去掉select选项，只保留ID，然后去掉多表连接。第一部分主要就是保留where后面的条件查询。根据条件查询，获取当前分页的记录的主键id。在这一部完成分页。

先根据筛选条件完成分页查询获取当前页的id：

select `ID` from 主表 where 查询条件 LIMIT 90,10


分页还是通过mybatis-plus的分页插件完成。在查询ID的mapper方法上完成分页，获取总数信息。下面是myatis-plus分页插件优化后的查询总数的sql。

-- myatis-plus分页插件打印的sql
SELECT COUNT(1) FROM 主表 WHERE 查询条件


第二部分再是根据获取到的当前页的记录id去查询需要的字段。包括子查询、连接查询等。是原sql去掉where条件查询之后，替换为ID in ()的查询语句。

select 字段、子查询 from 主表、连接表 where 主表.ID in (上一次查询获取到的id)


这样优化之后，分页的性能就能提升了。

还有一些文章是介绍说通过id多优化的，如：

..... where id>=(page * pageSize) limit pageSize;


我认为这种方案是不实用的，即使表的记录的id是连续的，且中间没有记录被删除，但是根据条件筛选出来的记录的id也是连续的吗？这种方案实现的分页查询结果一定是不准确的。

如果是遇到多表连接查询，且查询条件也需要根据Join的表的某个字段做过滤的情况下，如何优化分页查询limit带来的性能开销？

select a.*,b.* from a left join b on a.b_id=b.id where a.xx=? and b.xx=?


这真的不是一个好的表结构设计，实在想不出好的优化方案。

我想到的一种是，因为分页查询点击下一页时，要求查询条件是不能变的，且正常情况下也是不会变的，如果变了页码就应该重新从1开始，可以使用内存缓存上一次查询的最大的id，根据用户+接口的维度去缓存，在获取下一页时，跳过上一次查询的最大id，然后取多少条记录就行了。但是缺点就是不能跳页查询，前端只能是下一页、下一页，而且还需要浪费内存去缓存查询状态，在查询条件发生变化的情况下，还需要清掉缓存。

关于分页limit的问题，你们项目中是如何解决的呢？欢迎留言讨论。

在此毫无违和感的向大家推荐一本书，建议买电子版的，我买的一些纸质的书都很少看，电子书的好处是随时随地都可以看，包括在地铁上、公交车上、躺在被窝里睡不着时。

公众号：Java艺术

扫码关注最新动态