性能优化之分页查询 | StartDT Tech Lab 12

这是奇点云全新技术专栏「StartDT Tech Lab」的第 12 期。

在这里，我们聚焦数据技术，分享方法论与实战。一线的项目经历，丰富的实践经验，真实的总结体会…滑到文末，可以看到我们的往期内容。

本篇由奇点云高级 Java 开发工程师「张三」带来：

作者：张三

阅读时间：约 5 分钟

一、背景

商品列表展示、新闻列表展示……无论什么类型的系统，只要有前端页面供用户操作，往往都有业务数据列表查询展示的功能。而这些列表展示的功能，通常都采用了分页查询的形式来实现。

之所以选择分页查询的方式，我认为至少有三点原因：

1. 分页查询更符合用户使用的习惯；

2. 这些业务数据放在一个页面显示的话，如果数据量太大，就会无法正常显示；

3. 即便假设页面足够大，受限于系统服务及所使用的数据库配置，也是无法一次获取和处理太多数据的。

通过上面的介绍，大家了解到，如果需要商品列表查询，可以使用分页查询来做。那么问题来了：

如果遇到上千万或者上亿的数据需要同步，比如我们的 DataNuza 系统的圈人结果数据，原始数据是存放在大数据的库中，而应用系统直接拿这个库来使用的话，十分不方便，且存在性能问题，所以我们需要将数据同步到应用数据库中。

本次主要分享我们在大数据同步方面优化分页查询的经验。

二、分页查询优化

方案 1

一般分页查询

以 MySQL 为例，一般的分页查询使用简单的 limit 子句就可以实现。limit 子句声明如下：

# 第一个参数n指定第一个返回记录行的偏移量，第二个参数m指定返回记录行的最大数目。SELECT c1,c2,… FROM table LIMIT n,m;

←左滑查看

MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行。那当 offset 特别大的时候，效率就会非常低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

方案 2

基于主键 id 实现分页查询优化

假设数据表的 id 是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的 id 的范围，尽量给出查询的大致范围。

SELECT c1,c2,cn... FROM table WHERE id>=50000 LIMIT 10;

←左滑查看

这种查询方式能够极大地优化查询速度，基本能在几十毫秒之内完成。

限制则是只能用于明确知道 id 的情况。不过一般建立表的时候，都会添加基本的 id 字段，这为分页查询带来很多便利。

方案 3

利用表的覆盖索引来加速分页查询

我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。

另外 MySQL 中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

在我们的例子中，我们知道 id 字段是主键，自然就包含了默认的主键索引。

select * from test t inner join (select id from test limit 5000000,10) tt on t.id = tt.id;

←左滑查看

方案 4

分表分库

尽管上面的方法可以优化查询的速度，但数据表的数据量超过 500W 的时候，还是建议使用分表分库，将数据拆分到不同的表中，减少数据行和索引的量也会大幅度提高查询效率。但这种方法要求源表做出改变，不一定适用所有的场景。

以上就是本次分享的分页查询优化经验。

在实际项目中，我们采用了基于主键 id 实现分页查询优化，同步的过程中，记录下每次同步的主键 id，作为下一轮同步的限定 id，也做了程序异常退出恢复机制。

希望能给大家提供一些帮助和思路，欢迎大家一起交流，共同进步！