记一次慢SQL原因分析-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_39158331/article/details/146882921

背景

我方业务有个定时任务，定期从表中获取一条未初始化的活动，然后初始化redis分片。

某日上游业务调用我方应用，错误的创建了4k的活动，被我逻辑删除掉了，其中一半的活动已被初始化（state2=1）。结果sql执行性能蹭蹭往上涨，tp99从30ms到2000ms以上，慢日志显示扫表110w行

活动表如下，省略了业务字段。

CREATE TABLE `activity` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '活动编号',
  `state1` int(11) NOT NULL COMMENT '审批状态',
  `state2` int(11) NOT NULL COMMENT '初始化状态',
  `yn` tinyint(4) NOT NULL COMMENT '是否有效',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_state1_state2` (`state1`,`state2`) USING BTREE,
)

慢sql如下

SELECT
	* 
FROM
	activity 
WHERE
	state1 IN (2, 3, 4, 5, 6, 7, 8, 9, 100, 102) // 审批状态
	AND state2 = 0 //库存初始化进度 0未初始化 100初始化中 1初始化完毕
    AND yn = 1
ORDER BY
	id ASC 
	LIMIT 2;

原因分析

通过explain解析，该sql错误的使用id主键索引扫表，而非idx_state1_state2索引范围查询。

结合慢日志rowsExamined字段显示扫了110w行，初步结论是扫表了。

该sql where条件和order条件均有索引，优化器错误的选择主键索引导致性能变差：由于该表用作任务表，当活动初始化后state2设置为1，所以上述sql 99%的时间下都没有数据。如果走主键扫描，由于没有数据所以会一直扫到最后一行，等同于全表扫描。且select * 会导致大量的回表查询，性能急剧下降。

由于我逻辑删除上游的测试数据后，实际有2k条数据state1=2,state2=0,yn=0，所以idx_state1_state2索引过滤后会拿到2k多条数据，而我对这部分数据排序后limit2，优化器认为性价比不高所以选择了主键索引。而历史逻辑中没有这2k条测试数据经过索引过滤后不超过十条，limit2占比较高，用的idx_state1_state2索引所以一直没有慢sql问题