MySQL优化器未使用varchar字段prodcode索引问题咨询

索引未被使用的原因 #

你给出的EXPLAIN结果中possible_keys字段为NULL，说明优化器直接判定没有可用的索引，而非判断索引成本过高放弃使用，这类情况优先排查以下问题：

• 隐式字符集转换：这是varchar类型索引失效的最高发原因。你的表和prodcode字段使用utf8编码，若当前数据库连接的默认字符集为utf8mb4，查询时MySQL会自动将prodcode字段转为utf8mb4编码后再和输入的字符串对比，函数作用在索引字段上会直接导致索引失效。你可以执行SHOW VARIABLES LIKE '%character%';检查当前连接的字符集配置。
• 索引统计信息严重过时：4000万行的大表如果长时间没有更新统计信息，MySQL的优化器会无法正确识别索引的可用状态，直接忽略该索引。
• 索引物理损坏：极少数情况下，索引文件的物理损坏会导致优化器无法识别该索引。

如果后续调整后EXPLAIN的possible_keys出现了article_prodcode_idx但还是没有走索引，则可能是以下原因：

• 匹配行占比过高：如果prodcode='a-4536-x-bef45-green'对应的行数占总表的20%以上，优化器会认为走索引后还要回表查询所有字段（你使用了SELECT *），整体成本比全表扫描更高，因此选择全表扫描。

优化方案 #

• 优先排查字符集匹配问题：如果确认是连接字符集和字段字符集不匹配，你可以先执行SET NAMES utf8;后再运行查询，验证是否能命中索引。如果业务侧连接默认用utf8mb4，可以在线修改prodcode字段的字符集适配：ALTER TABLE article MODIFY COLUMN prodcode varchar(64) CHARACTER SET utf8mb4 NOT NULL;，大表修改字段建议使用pt-online-schema-change这类在线DDL工具，避免长时间锁表影响业务。
• 更新表统计信息：执行ANALYZE TABLE article;刷新表的索引统计数据，完成后再执行EXPLAIN验证索引命中情况。
• 验证匹配行数：先执行SELECT COUNT(*) FROM article FORCE INDEX(article_prodcode_idx) WHERE prodcode='a-4536-x-bef45-green';查看该条件匹配的行数，如果匹配行数确实很少，强制走索引的性能远高于全表扫描，业务侧可以临时用强制索引的写法保证查询性能，同时排查优化器判断异常的原因。
• 建联合索引适配业务查询：如果你常用的查询会同时带prodcode和status/priority条件，可以直接建联合索引，比如idx_prodcode_status(prodcode, status)，既可以避免回表，也可以大幅提升查询效率。
• 重建索引：如果确认是索引损坏，可以在线重建该索引：ALTER TABLE article DROP INDEX article_prodcode_idx, ADD INDEX article_prodcode_idx(prodcode);，大表操作同样建议使用在线DDL工具。

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