WHERE 条件里明明有索引却走了全表扫描，最常见的隐形杀手就是隐式类型转换：列和常量的类型不一致时，MySQL 悄悄把转换函数套在了索引列上。

问题的本质：转换发生在哪一侧

MySQL 的比较规则里有一条兜底条款：两个操作数类型不兼容时，统一转成浮点数再比较。关键不在”转不转”，而在”转换发生在谁身上”：

  • 数字列 vs 字符串常量：num = '123' 等价于 num = CAST('123' AS DOUBLE)，转换在常量侧，执行前算一次就好，索引不受影响。
  • 字符串列 vs 数字常量：phone = 123 等价于 CAST(phone AS DOUBLE) = 123，转换落在列上，每一行都要现算。

B+ 树索引存的是列的原始值及其排序。对列套一层转换函数后，要比较的值在索引树中既不存在也无序，优化器无法二分定位，只能全表扫描逐行转换再比较。这和”对索引列使用函数”是同一种失效机制，只不过函数是 MySQL 替你加上的，因此更加隐蔽。

实验对比

准备一张表，num1 为 INT，num2 为 VARCHAR，两列都建了索引：

SELECT * FROM t WHERE num1 = 10000;    -- 走索引
SELECT * FROM t WHERE num1 = '10000';  -- 走索引，转换发生在常量
SELECT * FROM t WHERE num2 = 10000;    -- 全表扫描，转换发生在列
SELECT * FROM t WHERE num2 = '10000';  -- 走索引，类型一致

第三条为什么必须全扫？因为字符串转数字不是一对一映射：'10000'、'010000'、'10000abc'（转换时截取前缀数字部分）转出来都是 10000。索引里按字符串字典序排列的值，回答不了”转成数字后等于 10000 的有哪些”，只能每行都试一遍。这也意味着 num2 = 10000 可能查出你没预期的行——既是性能问题，也是正确性隐患。

字符集不一致：另一种隐式转换

类型之外，字符集与排序规则（collation）不一致同样会触发隐式转换。典型场景是 utf8 与 utf8mb4 混用：两表 JOIN 时关联列字符集不同，MySQL 会执行类似 CONVERT(col USING utf8mb4) 的转换，转换落在哪张表的列上，哪张表的索引就失效。utf8（MySQL 里是三字节别名）与 utf8mb4（真四字节）并存的历史库最容易踩这个坑，EXPLAIN 的 Extra 或 SHOW WARNINGS 输出中能看到 convert 痕迹。

一劳永逸的做法是建表规范中统一字符集与 collation，存量库尽早完成字符集升级，别给 JOIN 留混用空间。

排查与实践建议

  • EXPLAIN 显示 type=ALL 且 key 为 NULL、而列上明明有索引时，先怀疑隐式转换；紧接着执行 SHOW WARNINGS，优化器改写后的 SQL 里常能看到 cast 字样。
  • 核对应用侧传参类型：ORM 参数绑定、HTTP 参数反序列化都可能把字符串变成数字传进 SQL。
  • 团队规约：字符串列的查询条件必须加引号；时间列禁止函数包裹，一律改写为范围条件；全库统一 utf8mb4 及一致的 collation。

小结

判定隐式转换是否让索引失效，标准只有一条：转换函数是否落在索引列上。字符串列配数字条件、跨字符集关联是两大高发场景。养成”条件类型与列类型严格一致”的习惯，再配合 SHOW WARNINGS 验证，这类问题在 code review 阶段就能消灭。

面试追问：

  1. int_col = '123' 为什么不影响索引？（转换发生在常量侧，只算一次，列上的有序性未被破坏）
  2. '123abc' 与数字比较时转成什么？（按前缀截取数字部分得到 123；非数字开头的字符串转为 0）