一次分页慢查询导致的事故处理过程
事故背景 这次事故也是我们组里遇到的一次关于分页慢查询的典型例子，通过这篇文章，你可以很清晰的跟随我们还原事故现场，以及每一步遇到问题做出的调整和改动。
事故问题现场 16:00 收到同事反馈，融合系统分⻚查询可⽤率降低 16:05 查询接⼝ump监控，发现接⼝tp99异常彪⾼ 打开机器监控，发现⼏乎所有机器的tp999都异常的⾼，观察机器cpu监控，发现cpu使⽤率并不⾼
16:10 查看数据库监控，发现数据库cpu异常彪⾼，定位到是数据库问题，同时收到了⼤量的慢sql邮件。 定位到这里，我们基本确定这个不是几分钟能解决的问题，于是我们分成两步去处理。第一步：打开限流，防止更多的慢sql请求进行 第二步：分析慢sql，进行改造上线 查看慢sql，⼤部分都是融合系统分⻚查询接⼝涉及到的sql，同时由于上游系统在15:35左右对于该接⼝调⽤流量激增，和数据库cpu暴涨，接⼝tp999暴涨的时间吻合，推测是由于库存对于该接⼝的调⽤对于数据库造成了压⼒，导致接⼝耗时增加。但是该接⼝的调⽤量并不⾼，再次查看慢sql，发现有⼤量已经遍历到⼏百⻚的慢sql。推测是深分⻚的问题。
16:15 排查⽇志发现，⼤部分sql都指向商家xxxx，查询发现其下有10w条数据（占⽤总数量的⼗分之⼀），mq发现有⼤量重试，分⻚查询接⼝超时时间发现配置的是2s。推测是慢查询导致的⾼频次重试将数据库的性能拖垮。 16:25 观察代码后，确定了是深分⻚问题，确定下来了优化⽅案。为了避免库存修改接⼝，⾸先我们优化sql将其优化为⼦查询的形式。即先通过pageno和pagesize查询出id，然后取出当中的最⼩值和最⼤值，然后使⽤范围查询去查询出来全表数据。由于线上持续对数据库造成压⼒，先让上游把mq的消费暂停消费。 17:40 优化代码上线，上游重新打开mq消费，但是由于消费积累的消息⽐较多，直接打开后，还是对融合数据库造成了压⼒。接⼝的tp99再次飙升，数据库cpu再次飙到100%。 18:00 复盘了下，决定不再优化旧接⼝，⽽是开发新接⼝，基于滚动id进⾏分⻚查询。需要推动上游⼀起参与开发和联调。 22:20 新接⼝上线，重新放开mq消费，上游积压了⼤量消息的情况下，新接⼝表现平稳，“问题解决” 问题原因和解决⽅法 深分⻚出现原因 问题sql：
select * from table where org_code = xxxx limit 1000,100 以上⾯的sql为例，mysql的limit⼯作原理就是先读取前⾯1000条记录，然后抛弃前1000条，读后⾯100条想要的，所以⻚码越⼤，偏移量越⼤，性能就越差。
深分⻚的⼏种解决⽅法 查询id+基于id查询 即先使⽤查询条件查询出来id,再通过id进⾏范围查询，也就是说我第⼀次优化的时候使⽤的⽅法 ⾸先查询出来id，以上⾯的sql为例
select id from table where org_code = xxxx limit 1000,5 然后查询出来id后，使⽤id进⾏in查询，由于是直接基于主键的in查询，所以效率较⾼
select * from table where id in (1,2,3,4,5); 基于id查询优化 由于在第⼀次查询已经查询出来了所有符合条件的id了，可以使⽤范围查询来替代in查询，效率更⾼(in 查询需要和集合⾥⾯的元素进⾏⽐对，但是范围查询只需要⽐较最⼤和最⼩即可)
select * from table where org_code = xxxx and id >= 1 and id 0 limit 10; 这种⽅式服务端实现起来⽐较简单且性能很好。缺点是需要客户端修改，且需要保证id是⾃增有序且结果需要是按照id排序的。最终定下的是使⽤滚动查询的⽅法。最终优化sql上线后，表现平稳。第⼆周和库存⼀起重新优化了⾮多规格sku的sql。如下:
select id,dj_org_code,dj_sku_id,jd_sku_id,yn from table whereorg_code = xxxx and id > 0 order by id asc limit 500 测试了没问题后上线。观察线上监控稳定。本以为⾼枕⽆忧的时候，⼀周之后，数据库再次出现了⼤量的慢查询，数据库cpu报警，观察接⼝监控:
可以看到在调⽤量并不⼤的前提下，接⼝的耗时达到了60s。联系运维同学帮忙排查，发现了⼤量的慢 sql：
select id,dj_org_code,dj_sku_id,jd_sku_id,yn from table whereorg_code = xxxx and id > 0 order by id asc limit 500 可以看出来，这就是我们优化后的sql。运维同学explain这条sql后发现，这条sql⾛了主键索引，没有⾛我们以为应该要⾛的org_code的索引。
和运维初步沟通后得出结论，在某些情况下，主键索引的优先级是会⾼于普通索引的。
最终解决方案 引用join 因为我们使⽤了主键索引进⾏排序，且查询了不在索引树只在叶⼦节点中的字段。因此mysql认为主键索引更优，因为既可以排序，⼜不⽤回表，所以就使⽤主键索引最终导致了全表扫描。
最终使⽤了先查询id（不查询叶⼦节点字段保证使⽤索引），在通过join，使⽤查询出来的id来查询对应的数据的sql：
select a.id as id,a.dj_org_code as djorgcode,a.dj_sku_id asdjskuid,a.jd_sku_id as jdskuid,a.yn as yn fromtable a join(select id from table where org_code = xxxx and id > 0 orderby id asc limit 500) t on a.id=t.id; 再次explain了下，可以发现⾛了我们既定的索引:
于是上线，解决问题。上线稳定后，分析之前的问题sql，执⾏下⾯两条语句，同样的sql，不同的商家，mysql的执⾏结果也是不⼀样的
查询资料找原因 查阅资料得知
mysql会将limit的数量和where条件⾥查询出的数量进⾏⽐对，如果limit数量占⽐较⼩ （例如某些商家的sku数⽬⽐较多），则会优化为主键索引，因为mysql此时认为⾛主键索引会减少 ⼀次索引树的查询，且可以在较短时间⾥⾯得到结果。（没有limit不会⾛主键索引） 因此在where 索引a order by 主键索引 limit n的这种sql，需要考虑mysql优化主键索引的情况。 除了上⾯最终上线后的优化sql，也可以通过force index强制使⽤索引: select id,dj_org_code,dj_sku_id,jd_sku_id,yn from table forceindex(idx_upc) where org_code = xxxx and id > 0 order by id asc limit500 但是这种写死了索引名称的⽅式，如果以后修改了索引名，容易导致安全隐患。
问题总结 b端系统也需要考虑对⾃⼰系统的保护，接⼊限流等，防⽌异常流量或者异常调⽤把⾃⼰的系统调死。这次幸亏上游系统是通过mq调⽤融合api的，可以暂停消费，如果是⽤api调⽤，且流量较⼤，持续让数据库处于⾼压状态，会影响到融合系统的整体稳定性。 针对可能出现的⻛险点绝不姑息。这次这个分⻚查询sku的接⼝，之前就看到过，但是当时觉得这个接⼝在数据量较少的情况下性能也还好，⽽且也有了商家维度的索引，就放过了，考虑后续优化。结果现在就爆出了问题。 针对sql的优化，上线前要谨慎，⽽且需要同⼀条sql，需要针对不同的边界情况（例如这次的多sku的商家）进⾏反复测试，调整。
把握三月，抢占先“机”，联想打印送你早春福利
微软已确认Office 2021不会绝版
人工智能或是中国经济下一个爆发点
铜端子如何安装，它的安装要求有哪些
人工智能新时代，信息源可靠成关键
一次分页慢查询导致的事故处理过程
中国电信首发5G云手机，一套硬件变两台手机！5G云手机会成为潮流吗？
智能制造装备商耐科装备IPO上市首发获通过 业绩增长稳定
深度解读镓、锗市场
基于双12位DAC的高精度直流电压/电流源设计
霸气了！定制版iPhoneX曝光 堪称土豪机
如何使用Ethers.js在以太坊上构建出完整的DApps
思必驰荣获创新成长企业 100 强
示波器软件免费下载安装教程
MANGO:基于FPGA的重可编程高性能计算架构探索
TDS3034开机无法进入测试界面维修——泰克示波器维修案例
【微控制器基础】——从历史切入，了解微控制器的五个要素（上）
移动芯片市场，英特尔在2013卷土重来？
水泵软启动原理 水泵软启动电路图
三星将投资70亿美元扩大中国NAND闪存芯片产能
事故问题现场 16:00 收到同事反馈，融合系统分⻚查询可⽤率降低 16:05 查询接⼝ump监控，发现接⼝tp99异常彪⾼ 打开机器监控，发现⼏乎所有机器的tp999都异常的⾼，观察机器cpu监控，发现cpu使⽤率并不⾼
16:10 查看数据库监控，发现数据库cpu异常彪⾼，定位到是数据库问题，同时收到了⼤量的慢sql邮件。 定位到这里，我们基本确定这个不是几分钟能解决的问题，于是我们分成两步去处理。第一步：打开限流，防止更多的慢sql请求进行 第二步：分析慢sql，进行改造上线 查看慢sql，⼤部分都是融合系统分⻚查询接⼝涉及到的sql，同时由于上游系统在15:35左右对于该接⼝调⽤流量激增，和数据库cpu暴涨，接⼝tp999暴涨的时间吻合，推测是由于库存对于该接⼝的调⽤对于数据库造成了压⼒，导致接⼝耗时增加。但是该接⼝的调⽤量并不⾼，再次查看慢sql，发现有⼤量已经遍历到⼏百⻚的慢sql。推测是深分⻚的问题。
16:15 排查⽇志发现，⼤部分sql都指向商家xxxx，查询发现其下有10w条数据（占⽤总数量的⼗分之⼀），mq发现有⼤量重试，分⻚查询接⼝超时时间发现配置的是2s。推测是慢查询导致的⾼频次重试将数据库的性能拖垮。 16:25 观察代码后，确定了是深分⻚问题，确定下来了优化⽅案。为了避免库存修改接⼝，⾸先我们优化sql将其优化为⼦查询的形式。即先通过pageno和pagesize查询出id，然后取出当中的最⼩值和最⼤值，然后使⽤范围查询去查询出来全表数据。由于线上持续对数据库造成压⼒，先让上游把mq的消费暂停消费。 17:40 优化代码上线，上游重新打开mq消费，但是由于消费积累的消息⽐较多，直接打开后，还是对融合数据库造成了压⼒。接⼝的tp99再次飙升，数据库cpu再次飙到100%。 18:00 复盘了下，决定不再优化旧接⼝，⽽是开发新接⼝，基于滚动id进⾏分⻚查询。需要推动上游⼀起参与开发和联调。 22:20 新接⼝上线，重新放开mq消费，上游积压了⼤量消息的情况下，新接⼝表现平稳，“问题解决” 问题原因和解决⽅法 深分⻚出现原因 问题sql：
select * from table where org_code = xxxx limit 1000,100 以上⾯的sql为例，mysql的limit⼯作原理就是先读取前⾯1000条记录，然后抛弃前1000条，读后⾯100条想要的，所以⻚码越⼤，偏移量越⼤，性能就越差。
深分⻚的⼏种解决⽅法 查询id+基于id查询 即先使⽤查询条件查询出来id,再通过id进⾏范围查询，也就是说我第⼀次优化的时候使⽤的⽅法 ⾸先查询出来id，以上⾯的sql为例
select id from table where org_code = xxxx limit 1000,5 然后查询出来id后，使⽤id进⾏in查询，由于是直接基于主键的in查询，所以效率较⾼
select * from table where id in (1,2,3,4,5); 基于id查询优化 由于在第⼀次查询已经查询出来了所有符合条件的id了，可以使⽤范围查询来替代in查询，效率更⾼(in 查询需要和集合⾥⾯的元素进⾏⽐对，但是范围查询只需要⽐较最⼤和最⼩即可)
select * from table where org_code = xxxx and id >= 1 and id 0 limit 10; 这种⽅式服务端实现起来⽐较简单且性能很好。缺点是需要客户端修改，且需要保证id是⾃增有序且结果需要是按照id排序的。最终定下的是使⽤滚动查询的⽅法。最终优化sql上线后，表现平稳。第⼆周和库存⼀起重新优化了⾮多规格sku的sql。如下:
select id,dj_org_code,dj_sku_id,jd_sku_id,yn from table whereorg_code = xxxx and id > 0 order by id asc limit 500 测试了没问题后上线。观察线上监控稳定。本以为⾼枕⽆忧的时候，⼀周之后，数据库再次出现了⼤量的慢查询，数据库cpu报警，观察接⼝监控:
可以看到在调⽤量并不⼤的前提下，接⼝的耗时达到了60s。联系运维同学帮忙排查，发现了⼤量的慢 sql：
select id,dj_org_code,dj_sku_id,jd_sku_id,yn from table whereorg_code = xxxx and id > 0 order by id asc limit 500 可以看出来，这就是我们优化后的sql。运维同学explain这条sql后发现，这条sql⾛了主键索引，没有⾛我们以为应该要⾛的org_code的索引。
和运维初步沟通后得出结论，在某些情况下，主键索引的优先级是会⾼于普通索引的。
最终解决方案 引用join 因为我们使⽤了主键索引进⾏排序，且查询了不在索引树只在叶⼦节点中的字段。因此mysql认为主键索引更优，因为既可以排序，⼜不⽤回表，所以就使⽤主键索引最终导致了全表扫描。
最终使⽤了先查询id（不查询叶⼦节点字段保证使⽤索引），在通过join，使⽤查询出来的id来查询对应的数据的sql：
select a.id as id,a.dj_org_code as djorgcode,a.dj_sku_id asdjskuid,a.jd_sku_id as jdskuid,a.yn as yn fromtable a join(select id from table where org_code = xxxx and id > 0 orderby id asc limit 500) t on a.id=t.id; 再次explain了下，可以发现⾛了我们既定的索引:
于是上线，解决问题。上线稳定后，分析之前的问题sql，执⾏下⾯两条语句，同样的sql，不同的商家，mysql的执⾏结果也是不⼀样的
查询资料找原因 查阅资料得知
mysql会将limit的数量和where条件⾥查询出的数量进⾏⽐对，如果limit数量占⽐较⼩ （例如某些商家的sku数⽬⽐较多），则会优化为主键索引，因为mysql此时认为⾛主键索引会减少 ⼀次索引树的查询，且可以在较短时间⾥⾯得到结果。（没有limit不会⾛主键索引） 因此在where 索引a order by 主键索引 limit n的这种sql，需要考虑mysql优化主键索引的情况。 除了上⾯最终上线后的优化sql，也可以通过force index强制使⽤索引: select id,dj_org_code,dj_sku_id,jd_sku_id,yn from table forceindex(idx_upc) where org_code = xxxx and id > 0 order by id asc limit500 但是这种写死了索引名称的⽅式，如果以后修改了索引名，容易导致安全隐患。
问题总结 b端系统也需要考虑对⾃⼰系统的保护，接⼊限流等，防⽌异常流量或者异常调⽤把⾃⼰的系统调死。这次幸亏上游系统是通过mq调⽤融合api的，可以暂停消费，如果是⽤api调⽤，且流量较⼤，持续让数据库处于⾼压状态，会影响到融合系统的整体稳定性。 针对可能出现的⻛险点绝不姑息。这次这个分⻚查询sku的接⼝，之前就看到过，但是当时觉得这个接⼝在数据量较少的情况下性能也还好，⽽且也有了商家维度的索引，就放过了，考虑后续优化。结果现在就爆出了问题。 针对sql的优化，上线前要谨慎，⽽且需要同⼀条sql，需要针对不同的边界情况（例如这次的多sku的商家）进⾏反复测试，调整。
把握三月，抢占先“机”，联想打印送你早春福利
微软已确认Office 2021不会绝版
人工智能或是中国经济下一个爆发点
铜端子如何安装，它的安装要求有哪些
人工智能新时代，信息源可靠成关键
一次分页慢查询导致的事故处理过程
中国电信首发5G云手机，一套硬件变两台手机！5G云手机会成为潮流吗？
智能制造装备商耐科装备IPO上市首发获通过 业绩增长稳定
深度解读镓、锗市场
基于双12位DAC的高精度直流电压/电流源设计
霸气了！定制版iPhoneX曝光 堪称土豪机
如何使用Ethers.js在以太坊上构建出完整的DApps
思必驰荣获创新成长企业 100 强
示波器软件免费下载安装教程
MANGO:基于FPGA的重可编程高性能计算架构探索
TDS3034开机无法进入测试界面维修——泰克示波器维修案例
【微控制器基础】——从历史切入，了解微控制器的五个要素（上）
移动芯片市场，英特尔在2013卷土重来？
水泵软启动原理 水泵软启动电路图
三星将投资70亿美元扩大中国NAND闪存芯片产能