如何有效提升MySQL大表分页查询效率（本文以一张900万条数据体量的表为例进行详细解读）

1、提出问题

在某车辆监控项目的软件系统开发过程中，记录每台车的行驶轨迹表数据会随着时间的推移，单表的数据量会越来越大（每台车辆10秒记录一条）。

每台车的数据体量：

可见，车辆轨迹数据的查询不会像最初那样简单快速，如果查询关键字段没有走索引，会直接影响到用户体验，甚至会影响到服务是否能正常运行！

下面就某月车辆行驶轨迹表为例，数据库是 Mysql，数据体量在 900 万以上，详细介绍分页查询下，不同阶段的查询效率情况。

1.1 问题测试

下面我们一起来测试一下，每次查询车辆行驶轨迹表时最多返回 100 条数据，不同的起始下，数据库查询性能的差异。

可以看出，随着起点位置越大，分页查询效率下降明显，一般查询耗时超过 1 秒的 SQL 都被称为慢 SQL，事实上，这还只是数据库层面的耗时，还没有算后端服务的处理链路时间，以及返回给前端的数据渲染时间，以百万级的单表查询为例，如果数据库查询耗时 1 秒，再经过后端的数据封装处理，前端的数据渲染处理，以及网络传输时间，没有异常的情况下，差不多在 3～4 秒之间，必须在限定的时间内尽快优化，不然可能会影响服务的正常运行和用户体验。

对于千万级的单表数据查询，我也测试了一下，查询耗时结果：43 秒！

据互联网软件用户体验报告:

• b当平均请求耗时在1秒之内，用户体验是最佳的，此时的软件也是用户留存度最高的；
• 2 秒之内，还勉强过的去，用户能接受；
• 当超过 3 秒，体验会稍差；超过 5 秒，基本上会卸载当前软件。

2、解决问题（三种方案）

2.1、方案一：查询的时候，只返回主键 ID

我们继续回到上文给大家介绍的客户表查询，将select *改成select id，简化返回的字段，我们再来观察一下查询耗时。

通过对比发现，通过简化返回的字段，可以提升查询效率。

实际的操作思路就是先通过分页查询满足条件的主键 ID，然后通过主键 ID 查询部分数据，可以显著提升查询效果。

-- 先分页查询满足条件的主键ID
select id from  t_location_log order by id limit 100000,10;

-- 再通过分页查询返回的ID，批量查询数据
select * from  t_location_log where id in (1,2,3,4,.....);

2.2、方案二：查询的时候，通过主键 ID 过滤

这种方案有一个要求就是主键ID，必须是数字类型，实践的思路就是取上一次查询结果的 ID 最大值，作为过滤条件，而且排序字段必须是主键 ID，不然分页排序顺序会错乱。

带上主键 ID 作为过滤条件，查询性能非常的稳定，基本上在0.69 s内可以返回。

这种方案还是非常可行的，如果当前业务对排序要求不多，可以采用这种方案，性能也非常杠！但是如果当前业务对排序有要求，比如通过客户最后修改时间、客户最后下单时间、客户最后下单金额等字段来排序，那么上面介绍的【方案一】，比【方案二】查询效率更高！

2.3、方案三：采用 elasticSearch 作为搜索引擎

当数据量越来越大的时候，尤其是出现分库分表的数据库，以上通过主键 ID 进行过滤查询，效果可能会不尽人意，还有另一种比较好的解决办法就是将数据存储到 elasticSearch 中，通过 elasticSearch 实现快速分页和搜索，效果提升也是非常明显。

3、总结

上文中介绍的表主键 ID 都是数值类型的，之所以采用数字类型作为主键，是因为数字类型的字段能很好的进行排序。但如果当前表的主键 ID 是字符串类型，比如 uuid 这种，就没办法实现这种排序特性，而且搜索性能也非常差，因此不建议大家采用 uuid 作为主键ID，具体的数值类型主键 ID 的生成方案有很多种，比如自增、雪花算法等等，都能很好的满足我们的需求。

希望本文中的一些sql查询技巧给你实际工作中帮到你。

人生从来没有真正的绝境。只要一个人的心中还怀着一粒信念的种子，那么总有一天，他就能走出困境，让生命重新开花结果。