MySQL使用变量查询不走索引触发全表扫描锁表问题求助

• 执行计划生成时无法获取变量值：MySQL的查询优化器在生成执行计划的阶段，用户自定义变量@v_ym的实际值还未完成计算，优化器无法基于ym列的分布统计信息判断走索引的收益更高，因此默认选择全表扫描。
• 数据类型隐式转换导致索引失效：extract(year_month from ...)返回的是数值类型结果，而表中ym字段定义为varchar(6)字符串类型，二者进行等值比对时，MySQL会自动将ym列的所有字符串值转为数值后再比对，这个转换过程直接导致ym字段的索引无法被命中。
• 代码笔误：你事件代码中SELECT CAST(@ym AS CHAR);引用的@ym变量并未定义，实际使用的变量是@v_ym，这行冗余代码建议删除。

方案1：统一数据类型+强制指定索引 #

首先将变量转为和ym字段一致的字符串类型，同时查询时强制指定走ym索引，修改后的事件代码如下：

Begin
 SET @v_ym := CAST(extract(year_month from DATE_SUB(SYSDATE(),INTERVAL 1 DAY)) AS CHAR);
 select ssaname,
        @v_ym ym,
        omcr.btscount_ssa(ssaname) btscount,
        sum(case when duration>30 then duration else 0 end) dur_30 
 from btsoutage.bts_faults FORCE INDEX(indx_btsfaults_ym)
 where ym=@v_ym  and ssaname is not null
 group by ssaname;
END;

方案2：使用预处理语句执行查询 #

预处理语句会在变量值确定后再生成执行计划，可以自动匹配索引，不需要强制指定：

Begin
 SET @v_ym := CAST(extract(year_month from DATE_SUB(SYSDATE(),INTERVAL 1 DAY)) AS CHAR);
 SET @sql = 'select ssaname,? ym,omcr.btscount_ssa(ssaname) btscount,sum(case when duration>30 then duration else 0 end) dur_30 from btsoutage.bts_faults where ym=? and ssaname is not null group by ssaname';
 PREPARE stmt FROM @sql;
 EXECUTE stmt USING @v_ym, @v_ym;
 DEALLOCATE PREPARE stmt;
END;

方案3：直接在条件中写常量计算逻辑 #

可以不用变量，直接将年月计算逻辑写在WHERE条件中，MySQL的常量折叠机制会提前计算出固定值，生成执行计划时可以正常命中索引：

Begin
 select ssaname,
        extract(year_month from date_sub(sysdate(),interval 1 day)) ym,
        omcr.btscount_ssa(ssaname) btscount,
        sum(case when duration>30 then duration else 0 end) dur_30 
 from btsoutage.bts_faults 
 where ym = DATE_FORMAT(DATE_SUB(SYSDATE(),INTERVAL 1 DAY), '%Y%m') and ssaname is not null
 group by ssaname;
END;

内容的提问来源于stack exchange，提问作者sriman narayana