1.事故背景这次事故也是我们组遇到的典型的分页慢查询。通过这篇文章，您可以轻松清晰地跟随我们还原事故现场，以及每一步遇到问题时所做的调整和改变。2、事故现场16:00收到同事反馈，融合系统逐页查询可用性下降。16:05查询接口UMP监控，发现接口TP99异常偏高。打开机器监控，发现几乎所有的机器TP999都异常高。观察机器的CPU监控，发现CPU占用率不高。16:10查看数据库监控，发现数据库CPU异常高。定位为数据库问题，同时收到大量慢SQL邮件。定位到这里之后，我们基本确定这个问题不是几分钟就能解决的，所以我们分两步来处理。第一步：开启限流，防止出现更慢的SQL请求。第二步：分析慢SQL，进行改造，上线查看慢SQL。大部分是融合系统分页查询接口相关的SQL。系统对该接口的调用流量在15:35左右激增，恰逢数据库CPU和接口TP999暴涨的时间。推测是库存调用接口对数据库造成压力，导致接口消耗。时间增加。但是这个接口的调用次数并不多。再看慢SQL，发现有大量遍历了几百页的慢SQL。推测是deeppaging的问题。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消费。在上游消息大量积压的情况下，新接口执行流畅，“问题解决”3.问题原因及解决方案：select*fromtablewhereorg_code=xxxxlimit1000,100以上SQL为例，MySQLlimit的工作原理是先读前1000条记录，然后舍弃前1000条记录，想读后面的100条记录，所以pagenumber越大，offset越大，性能越差。2、深度分页的几种解决方案1）查询ID+基于ID的查询，即先使用查询条件查询id，再使用id进行范围查询，也就是我在时使用的方法我先优化先查询ID，以上面的SQL为例selectidfromtablewhereorg_code=xxxxlimit1000,5然后查询id，用id查询in，因为是in查询直接根据主键，所以效率更高select*fromtablewhereidin(1,2,3,4,5);2）基于ID的查询优化由于第一次查询已经查询完所有符合条件的ID，所以可以使用范围查询代替inQuery，效率更高（inquery需要和集合中的元素进行比较，而rangequery只需要比较最大和最小)select*fromtablewhereorg_code=xxxxandid>=1andid<=5;Usesubqueryselecta.id,a.dj_sku_id,a.jd_sku_idfromtableajoin(selectidfromjd_spu_skuwhereorg_code=xxxx限制1000,5)bona.id=b.id;使用子查询可以减少和数据库的IO交互，也是解决深度分页的常用方法。3）使用滚动查询，接口每次都会返回查询数据的最大id（cursor）。下一次查询会传入这个游标，服务器只需要根据这个游标取出n个id大于这个游标的item即可。数据。n是每页显示的项目数。select*fromtablewhereorg_code=xxxxandid>0limit10;这种方法在服务器端实现起来比较简单，性能也不错。缺点是需要客户端修改，需要保证ID是自增顺序，结果需要按照ID排序。最终决定采用滚动查询的方式。最终优化后的SQL上线后，性能稳定。第二周，针对非多规格SKU的SQL与库存一起重新优化。如下：SELECTid,dj_org_code,dj_sku_id,jd_sku_id,ynFROMtablewhereorg_code=xxxxandid>0orderbyidasclimit500经过测试，没问题后会上线。观察在线监控的稳定性。正当我以为可以高枕无忧的时候，一周后，数据库再次出现大量慢查询，数据库CPU报警。观察接口监控：可以看到在调用次数不大的前提下，接口的耗时达到了60S。联系运维同学帮忙排查，发现很多慢SQL:SELECTid,dj_org_code,dj_sku_id,jd_sku_id,ynFROMtablewhereorg_code=xxxxandid>0orderbyidasclimit500可以看到，这是我们优化的SQL。运维同学解释了这条sql，发现这条sql丢了主键索引，却没有丢掉我们认为应该需要的org_code索引。与运维初步沟通后得出结论，在某些情况下，主键索引的优先级会高于普通索引。四、最终解决方案1、参考join，因为我们使用主键索引进行排序，查询不在索引树中，只在叶子节点中的字段。所以mysql认为主键索引比较好，因为不用回表就可以排序，所以使用主键索引最终导致全表扫描。最后先查询ID（不查询叶子节点字段，保证索引的使用），然后使用查询到的ID通过joinSQL查询对应的数据：selecta.idASid,a。dj_org_codeASdjOrgCode,a。dj_sku_idASdjSkuId,a.jd_sku_idASjdSkuId,a.ynASynfromtableajoin(SELECTidFROMtablewhereorg_code=xxxxandid>0orderbyidasclimit500)tona.id=t.id;再解释一下，可以发现我们建立的指标出现了偏差：于是上网解决问题。在线稳定后，分析之前的问题SQL，执行下面两条语句。同样的SQL，不同的商家，MYSQL的执行结果也是不一样的。MYSQL根据信息，将限制数量与where条件查询的数量进行比较。如果限制数量比较少（比如有些商家的SKU数量比较多），就会“优化”为主键Index，因为MYSQL认为主键索引会减少索引树的查询在这一次，很快就能得到结果。（没有LIMIT，主键索引不会进入。）因此，在SQLwhereindexAorderbyprimarykeyindexlimitN中，需要考虑MYSQL优化主键索引的情况。除了上面最后启动后的优化SQL，还可以通过forceindex强制使用索引：SELECTid,dj_org_code,dj_sku_id,jd_sku_id,ynFROMtableforceindex(idx_upc)whereorg_code=xxxxandid>0orderbyidasclimit500但这是一种硬编码索引名称的方法。如果以后修改索引名称，很容易导致安全隐患。5、问题总结1）B端系统还需要考虑对自身系统的保护、访问限流等，防止异常流量或异常调用关闭自身系统。好在上游系统通过MQ调用fusionAPI，可以暂停消费。如果API被调用，流量较大，数据库会持续承受高压，影响融合系统的整体稳定性。2)对可能存在的风险不予容忍。这次分页查询sku的接口以前见过，不过当时觉得这个接口在数据量小的时候性能还是不错的，而且还有商户维度的索引，所以我放手了。考虑后续优化。结果现在出问题了。3）对于SQL的优化，上线前要谨慎，同一条SQL需要针对不同的边界条件（比如这个多SKU商户）反复测试和调整。
