MySQL 5.7复合索引仅使用首列问题排查及优化方案咨询

你遇到的问题核心是隐式类型转换导致索引失效。仔细看你的查询条件：col2 = 123，但col2的定义是varchar(255)类型。MySQL在处理不同类型的比较时，会触发隐式转换——这里它会把col2的每一行值都转换成数字类型，再和123比较，而不是把123转换成字符串去匹配索引。这种转换操作会让col2对应的索引部分完全失效，所以复合索引只能用到最前面的col1，剩下的col2、col3条件只能通过回表扫描来筛选，自然性能拉胯。

再结合你的基数数据：col1的基数只有40，意味着用col1过滤后还剩下大量数据（170万/40 ≈ 4.25万条），这么多数据回表扫描当然慢。

你用拼接col1+col2的生成列建索引，本质是把两个列的字符串内容合并成一个索引键，查询时MySQL可以直接匹配这个合并后的字符串，避开了类型转换的问题。但这种方案确实不支持IN查询，因为IN的多值组合会让合并后的索引键无法被高效利用，比如col1 IN ('x','y') AND col2 IN ('123','456')，对应的生成列值有'x123'、'x456'、'y123'、'y456'，索引没法快速定位这些离散的组合。

优化建议 #

• 最直接的修复：修改查询条件的类型匹配
  把查询里的col2 = 123改成col2 = '123'（给数字加引号，变成字符串）。这样MySQL不需要做任何隐式转换，就能完整利用你的复合索引(col1, col2, col3)——先通过col1过滤，再用col2缩小范围，最后用col3精准定位，全程走索引，回表的数据量会非常小（如果你的查询是SELECT *，可能还是需要回表，但索引已经过滤了绝大多数数据）。这是成本最低、效果最好的方案，优先考虑。

• 如果无法修改查询语句：用生成列适配类型转换
  要是应用层没法改查询（比如代码已经固化），可以创建一个基于col2转数字的生成列，再建对应的复合索引：

  -- 添加存储生成列，把col2转成整数（确保col2都是数字内容，否则转换会报错）
  ALTER TABLE `table` ADD COLUMN col2_int INT GENERATED ALWAYS AS (CAST(col2 AS UNSIGNED)) STORED;
  -- 创建新的复合索引
  CREATE INDEX idx_col1_col2int_col3 ON `table`(col1, col2_int, col3);
  

  这样当你执行col2 = 123时，MySQL会自动使用这个新索引，因为生成列col2_int已经提前完成了转换，不需要在查询时做逐行转换。如果col2里有非数字内容，可以用更安全的转换逻辑：

  ALTER TABLE `table` ADD COLUMN col2_int INT GENERATED ALWAYS AS (IF(col2 REGEXP '^[0-9]+$', CAST(col2 AS UNSIGNED), NULL)) STORED;
  

  这个方案能支持IN查询，比如col1 IN ('x','y') AND col2 IN ('123','456')（或者数字形式的IN），索引都能正常工作。

• 额外检查：确认索引统计信息是否准确
  有时候MySQL的索引基数统计不准，也会导致优化器选择错误的执行计划。可以执行ANALYZE TABLE table``来更新统计信息，让优化器能更准确地判断索引的价值。

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