半夜被慢查询告警吵醒，limit深度分页的坑
分享是最有效的学习方式。博客：https://blog.ktdaddy.com/
故事
梅雨季，闷热的夜，令人窒息，窗外一道道闪电划破漆黑的夜幕，小猫塞着耳机听着恐怖小说，辗转反侧，终于睡意来了，然而挨千刀的手机早不振晚不振，偏偏这个时间振动了一下，一个激灵，没有按捺住对内容的好奇，点开了短信，卧槽？告警信息，原来是负责的服务出现慢查询了。小猫想起来，本日在下班之前上线了一个版本，由于新增了一个业务字段，所以小猫写了干系的刷数据的接口，在下班之前调用开始刷历史数据。
考虑到表的数据量比力大，一次性把数据全部读取出来然后在内存内里去刷新数据肯定是不现实的，所以小猫采用了分页查询的方式依次根据条件查询出结果，然后进行表数据的重置。没想到的是，数据量太大，分页的深度越来越深，渐渐地，慢查询也就暴暴露来了。
https://img2024.cnblogs.com/blog/2200669/202406/2200669-20240627065503499-1677439577.jpg
强迫症小猫瞬间睡意全无，翻起来打开电脑开始解决题目。
那么为什么用利用limit之后会出现慢查询呢？接下来老猫和大家一起来剖析一下吧。
https://img2024.cnblogs.com/blog/2200669/202406/2200669-20240627065524521-2445651.png
limit分页为什么会变慢？
在表明为什么慢之前，咱们来重现一下小猫的慢查询场景。咱们从现实的例子推进。
做个小实行
假设我们有一张这样的业务表，商品Product表。具体的建表语句如下：
CREATE TABLE `Product` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`type` tinyint(3) unsigned NOT NULL DEFAULT '1' ,
`spuCode` varchar(50) NOT NULL DEFAULT '' ,
`spuName` varchar(100) NOT NULL DEFAULT '' ,
`spuTitle` varchar(300) NOT NULL DEFAULT '' ,
`channelId` bigint(20) unsigned NOT NULL DEFAULT '0',
`sellerId` bigint(20) unsigned NOT NULL DEFAULT '0'
`mallSpuCode` varchar(32) NOT NULL DEFAULT '',
`originCategoryId` bigint(20) unsigned NOT NULL DEFAULT '0' ,
`originCategoryName` varchar(50) NOT NULL DEFAULT '' ,
`marketPrice` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
`status` tinyint(3) unsigned NOT NULL DEFAULT '1' ,
`isDeleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
`timeCreated` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`timeModified` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) ,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uk_spuCode` (`spuCode`,`channelId`,`sellerId`),
KEY `idx_timeCreated` (`timeCreated`),
KEY `idx_spuName` (`spuName`),
KEY `idx_channelId_originCategory` (`channelId`,`originCategoryId`,`originCategoryName`) USING BTREE,
KEY `idx_sellerId` (`sellerId`)
) ENGINE=InnoDB AUTO_INCREMENT=12553120 DEFAULT CHARSET=utf8mb4 COMMENT='商品表'从上述建表语句中我们发现timeCreated走普通索引。
接下来我们根据创建时间来实行一下分页查询：
当为浅分页的时间，如下：
select * from Product where timeCreated > "2020-09-12 13:34:20" limit 0,10此时实行的时间为：
"executeTimeMillis":1
当调解分页查询为深度分页之后，如下：
select * from Product where timeCreated > "2020-09-12 13:34:20" limit 10000000,10此时深度分页的查询时间为：
"executeTimeMillis":27499
此时看到这里，小猫的场景已经重现了，此时深度分页的查询已经非常耗时。
剖析一下原因
简朴回首一下普通索引和聚簇索引
我们来回首一下普通索引和聚簇索引（也有人叫做聚集索引）的关系。
大家大概都知道Mysql底层用的数据结构是B+tree（假如有不知道的伙伴可以自己相识一下为什么mysql底层是B+tree）,B+tree索引其实可以分为两大类，一类是聚簇索引，别的一类是非聚集索引（即普通索引）。
（1）聚簇索引：InnoDB存储表是索引构造表，聚簇索引就是一种索引构造形式，聚簇索引叶子节点存放表中所有行数据记录的信息，所以经常会说索引即数据，数据即索引。固然这个是针对聚簇索引。
https://img2024.cnblogs.com/blog/2200669/202406/2200669-20240627065548318-264472394.png
由图可知在实行查询的时间，从根节点开始共经历了3次查询即可找到真实数据。倘若没有聚簇索引的话，就需要在磁盘上进行逐个扫描，直至找到数据为止。显然，索引会加速查询速度，但是在写入数据的时间，由于需要维护这颗B+树，因此在写入过程中性能也会降落。
（2）普通索引：普通索引在叶子节点并不包含所有行的数据记录，只是会在叶子节点存自己的键值和主键的值，在检索数据的时间，通过普通索引子节点上的主键来获取想要找到的行数据记录。
https://img2024.cnblogs.com/blog/2200669/202406/2200669-20240627065608037-1296355625.png
由图可知流程，首先从非聚簇索引开始寻找聚簇索引，找到非聚簇索引上的聚簇索引后，就会到聚簇索引的B+树上进行查询，通过聚簇索引B+树找到完整的数据。该过程比力专业的叫法也被称为“回表”。
看一下现实深度分页实行过程
有了以上的知识底子我们再来回过头看一下上述深度分页SQL的实行过程。
上述的查询语句中idx_timeCreated显然是普通索引，咱们结合上述的知识储备点，其深度分页的实行就可以拆分为如下步骤：
1、通过普通索引idx_timeCreated，过滤timeCreated，找到满足条件的记录ID；
2、通过ID，回到主键索引树，找到满足记录的行，然后取出展示的列（回表）；
3、扫描满足条件的10000010行，然后扔掉前10000000行，返回。
结合看一下实行操持：
https://img2024.cnblogs.com/blog/2200669/202406/2200669-20240627065624990-1239678060.png
原因其实很清楚了：
显然，导致这句SQL速度慢的题目出如今第2步。此中发生了10000010次回表，这前面的10000000条数据完全对本次查询没有意义，但是却占据了绝大部分的查询时间。
再深入一点从底层存储来看，数据库表中行数据、索引都是以文件的形式存储到磁盘(硬盘)上的，而硬盘的速度相对来说要慢很多，存储引擎运行sql语句时，需要访问硬盘查询文件，然后返回数据给服务层。当返回的数据越多时，访问磁盘的次数就越多，就会越耗时。
替换limit分页的一些方案。
上述我们其实已经搞清楚深度分页慢的原因了，总结为“无用回表次数过多”。
那怎么优化呢？相信大家应该都已经知道了，其焦点固然是减少无用回表次数了。
有哪些方式可以帮助我们减少无用回表次数呢？
子查询法
思绪：假如把查询条件，转移回到主键索引树，那就不就可以减少回表次数了。
所以，咱们将现实的SQL改成下面这种形式：
select * FROM Product where id >= (select p.id from Product p where p.timeCreated > "2020-09-12 13:34:20" limit 10000000, 1) LIMIT 10;测试一下实行时间：
"executeTimeMillis":2534
我们可以明显地看到相比之前的27499，时间整整缩短了十倍，在结合实行操持观察一下。
https://img2024.cnblogs.com/blog/2200669/202406/2200669-20240627065644679-607096390.png
我们综合上述的实行操持可以看出，子查询 table p查询是用到了idx_timeCreated索引。首先在索引上拿到了聚集索引的主键ID,省去了回表操纵，然后第二查询直接根据第一个查询的 ID往后再去查10个就可以了!
显然这种优化方式是有效的。
利用inner join方式进行优化
这种优化的方式其实和子查询优化方法如出一辙，其本质优化思绪和子查询法一样。
我们直接来看一下优化之后的SQL：
select * from Product p1 inner join (select p.id from Product p where p.timeCreated > "2020-09-12 13:34:20" limit 10000000,10) as p2 on p1.id = p2.id测试一下实行的时间：
"executeTimeMillis":2495
https://img2024.cnblogs.com/blog/2200669/202406/2200669-20240627065704188-352509756.png
咱们发现和子查询的耗时其实差不多，该思绪是先通过idx_timeCreated二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样背面直接走了主键索引了，同时也减少了回表。
上面两种方式其焦点优化思想都是减少回表次数进行优化处理。
标签记录法（锚点记录法）
我们再来看下一种优化思绪，上述深度分页慢原因我们也清楚了，一次性查询的数据太多也是题目，所以我们从这个点出发去优化，每次查询少量的数据。那么我们可以采用下面那种锚点记录的方式。雷同船开到一个地方短停息泊之后继续行驶，那么那个停泊的地方就是抛锚的地方，老猫喜欢用锚点标记来做比方，固然看到网上有其他的小伙伴称这种方式为标签记录法。其实意思也都差不多。
这种方式就是标记一下上次查询到哪一条了，下次再来查的时间，从该条开始往下扫描。我们直接看一下SQL：
select * from Product p where p.timeCreated > "2020-09-12 13:34:20" and id>10000000 limit 10显然，这种方式非常快，耗时如下：
"executeTimeMillis":1
但是这种方式显然是有缺陷的，大家想想假如我们的id不是一连的，或者说不是自增形式的，那么我们得到的数据就一定是禁绝确的。与此同时咱们也不能跳页查看，只能前后翻页。
固然存在相同的缺陷，我们还可以换一种写法。
select * from Product p where p.timeCreated > "2020-09-12 13:34:20" and id between 10000000 and 10000010这种方式也是一样存在上述缺陷，别的的话更要留意的是between ...and语法是两头都是闭地区间。上述语句假如ID一连不断地环境下，咱们终极得到的其实是11条数据，并不是10条数据，所以这个地方还是需要留意的。
存入到es中
上述摆列的几种分页优化的方法其实已经够用了，那么假如数据量再大点的话咋整，那么我们大概就要选择其他中心件进行查询了，固然我们可以选择es。那么es真的就是万能药吗？显然不是。ES中同样存在深度分页的题目，那么针对es的深度分页，那么又是别的一个故事了，这里咱们就不展开了。
写到最后
那么半夜三更爬起来优化慢查询的小猫毕竟有没有解决题目呢？电脑前，小猫长吁了一口吻，解决了！
我们看下小猫的优化方式：
select * from InventorySku isk inner join (select id from InventorySku where inventoryId = 6058 limit 109500,500 ) as d on isk.id = d.id显然小猫采用了inner join的优化方法解决了当前的题目。
相信小伙伴们背面遇到这类题目也能搞定了。
我是老猫，资深研发老鸟，让我们一起聊聊技术，聊聊职场，聊聊人生。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]