mysql千万级分页查询SQL优化

场景

某后台的功能列表，页面底部为通用分页：
总条数: 16209321 页码：1 2 3 4 5 .... 9819
页面默认展示 10 条数据，默认展示条数可选。
页面上部分搜索区域部分有多达 20-30 的筛选条件，筛选条件分别来自于不下 10 张数据表。 拿订单列表查询举例，可以使用用户表里的某个特殊字段进行筛选，如性别等，这些字段肯定不会在订单表存储，所以必然会进行联表。
使用者常常有疑问： 为何页面只有 10 条数据，查询却如此之慢？
老板会质疑你，做的是什么玩意？查询 10 条数据都要 1 分钟以上的时间？（优化前页面需要转 1 分钟才可显示出数据，页面转圈圈～）

这个功能对应的是后台的一个千万级别的大表，未分库分表，目前的数据量为13755695，分页查询使用到了limit，优化之前的查询耗时30 s，按照下文的方式调整SQL后，耗时800 ms； 

关于分页的优化

使用limit分页时的MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。【阿里巴巴JAVA开发手册】

select * from big_table where data_type in (1,2) limit 1000000,10; ## 耗时：39.23s

分页查询优化方式

1.基于主键ID实现查询优化分页查询优化

select * from big_table where id > (select id from big_table where data_type in (1,2) limit 1000000,1) limit 10; ## 耗时：0.716s

优化缺陷：如果主键不是自增长的（如uuid方式），则此方法失效；如果查询要求中需要针对一些字段进行过滤查询，如big_table中有一个data_type的字段，取值范围1,2,3。现在在查询中我们固定使用1,2进行查询，那么此方法也会失效，因为你根据条件筛选出最大的id后，后面10个数据，不一定都是你想要的，可能包含data_type=3的数据。

2.基于主键ID实现查询优化分页查询优化

select * from big_table a inner join (select id from big_table where data_type in (1,2) limit 1000000,10) b on a.id = b.id; ##耗时：0.69s

先快速定位需要获取的id段，然后再关联；

关于count的优化

在第一次优化后艰难使用一段时间后，依然遭到投诉，想了想，遭到投诉也是必然的，50 秒开一个页面。决定再次优化，本次的考虑点如下：
1、 由于时间慢是因为 count 语句造成，count 语句大约需要 40 多秒的时间，实际数据查询经过了第一次优化只需要不到十秒。而 count 函数又是必不可少的，因为需要查询总数，以供分页显示总条数及最后一页，所以必须从 count 优化入手
2、 如果技术上优化遇到了瓶颈，或者说 mysql 已经优化到极致，那么能否从业务上解决？

优化分析主要从两个角度进行。

1、 从技术角度来看，查询必有筛选条件，由于几十个筛选条件的取值不确定性，通过缓存 count 的总条数是无法满足的。继续观察 mysql 索引情况，由于现有索引的 key_len 过大，可以通过建立较小的索引 (使用小字段) 来为排序使用，由于我们的业务查询必有时间段条件，固为时间段字段单独建立索引，由此带来了几秒的性能提升 (并不明显)。

2、 从业务角度看，我们观察了百度、google、微博等网站，分页都不会显示结果集的总条数以及也不会有最后一页的链接，都是通过点击下一页的方式不断检索后面的数据，也就没有使用 count 函数的必要了，经过与需求方沟通，发现我们的想法并不复合需求，由于是后台管理系统，是需要通过筛选条件实时查询总条数，固此优化想法 pass。
3、 通过沟通，得到另一个优化方向，可以将前端分页组件异步加载。首先将数据列表展示出来，方便其它操作。前端调整逻辑，分别调用 2 次接口，获取数据接口、获取分页结果接口。分页结果接口返回数据前，分页组件显示 “正在努力加载…” 字样。
此种优化最终实现：列表数据加载 <5 秒，分页组件加载 >40 秒

其他优化思路

通过学习研究发现，mysql innodb 引擎在有索引、有 where 条件的情况下，count 速度并不慢，所以问题一样还出在 sql 上。

1, 通过分析 sql 发现，由于查询条件众多，只是对 where 语句后面的 sql 进行了动态 sql 处理，而 left join 语句没有进行动态 sql 处理，导致不管有几个查询条件，都需要链接 10 张左右其它数据表。是链接查询限制了 sql 的性能！而不是 count 限制的 sql 的性能！ 固做以下优化，将 left join 一并使用动态 sql 链接：

2.与需求方沟通后，查询列表可去掉排序规则，使用默认排序即可。此点优化也十分重要，order by 会导致 file sort，而极大的影响查询性能，所以我们去掉了 order by 语句，而使用默认的 id 排序。
3.最终优化： 列表数据查询 130ms，分页插件查询 (count 查询)150ms，目前数据单表数据量级在 2000 万左右，以次时间效率推断，多查询条件的复杂分页查询，可以支持单表几亿没有问题。

 

参考文章

https://zhuanlan.zhihu.com/p/457709985

https://blog.51cto.com/u_7117633/2850318

https://learnku.com/articles/53559

 

本篇文章如有帮助到您，请给「翎野君」点个赞，感谢您的支持。

首发链接：https://www.cnblogs.com/lingyejun/p/16181378.html