MySQL查询优化求助：带索引却全表扫描的关联查询优化

一、先排查索引失效的核心原因 #

• 检查关联字段类型一致性：如果关联的另一张表的对应字段和st_aepsrequest_log.snd_transno的类型、字符集、排序规则不匹配（比如一个是varchar(32)，一个是char(32)，或者存在隐式类型转换），MySQL会直接放弃使用索引。执行DESC 关联表名对比字段属性，确保完全一致。
• 评估索引区分度：如果snd_transno字段重复值过多（区分度低于10%），MySQL优化器会判定走索引的效率不如全表扫描。用SELECT COUNT(DISTINCT snd_transno)/COUNT(*) FROM st_aepsrequest_log计算区分度，若区分度过低，考虑更换索引策略。
• 排查索引阻断逻辑：查询中如果对snd_transno使用了函数（如SUBSTRING(snd_transno,1,5)）、隐式转换（比如用数字值关联字符串类型的snd_transno），都会直接导致索引失效，需要改写语句消除此类操作。

二、针对性优化措施 #

1. 强制指定索引（应急方案） #

如果确认索引有效但优化器选择错误，可在查询中强制使用目标索引：

SELECT 所需字段列表
FROM st_aepsrequest_log FORCE INDEX(idx_snd_transno)
JOIN 关联表 ON st_aepsrequest_log.snd_transno = 关联表.对应字段
WHERE 查询条件;

注意：仅在确认索引能提升性能时使用，避免滥用导致后续优化器决策异常。

2. 优化索引结构 #

• 创建联合索引：如果查询包含其他过滤条件（比如时间范围、状态字段），创建包含关联字段+过滤字段的联合索引，例如CREATE INDEX idx_snd_transno_create_time ON st_aepsrequest_log(snd_transno, create_time)，让索引同时满足关联和过滤需求，提升利用率。
• 重建索引：若索引存在碎片，会导致优化器放弃使用。执行ALTER TABLE st_aepsrequest_log REBUILD INDEX idx_snd_transno重建索引，消除碎片。

3. 改写查询语句 #

• 调整关联顺序：MySQL嵌套循环连接会优先处理小表，将数据量更小的表作为驱动表，让st_aepsrequest_log作为被驱动表，优化器更倾向于使用被驱动表的索引。
• 使用覆盖索引：避免SELECT *，只查询需要的字段，同时创建包含这些字段的索引（比如CREATE INDEX idx_snd_transno_cover ON st_aepsrequest_log(snd_transno, 字段1, 字段2)），让MySQL直接从索引获取数据，无需回表，大幅提升性能。

三、验证优化效果 #

优化后执行EXPLAIN查看执行计划：确认st_aepsrequest_log的type字段变为ref或range，key字段显示目标索引，rows字段数值大幅降低，说明索引已正常生效。

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