MySQL索引列使用内置函数为何全表扫描

在MySQL里，对索引列使用内置函数（比如DATE()、UPPER()、YEAR()）会导致全表扫描，根本原因在于B+树索引存储的是字段的原始值，而不是函数计算后的结果。当WHERE写成DATE(create_time)='2025-04-18'时，数据库不得不逐行计算DATE()再比对，自然无法利用索引的有序结构。解决方案也很直接——把函数从索引列上“搬走”，比如将YEAR(create_time)=2025改写成create_time >= '2025-01-01' AND create_time < '2026-01-01'。

对索引列使用内置函数（如 DATE()、UPPER()、YEAR()、LEFT()）会让优化器放弃B+树搜索路径，最终选择全表扫描——这可不是索引“坏了”，而是优化器在成本评估后主动做出的决定。

为什么函数会让索引失效

MySQL的B+树索引存储的是字段原始值（比如 '2025-04-18 14:30:00'），不是函数计算后的结果。一旦你在 WHERE 中写 DATE(create_time) = '2025-04-18'，MySQL 就必须对每一行的 create_time 执行一次 DATE() 计算，再比对结果。这等于逐行计算 + 判断，B+ 树的快速定位能力完全派不上用场。

优化器会评估成本：遍历主键（type: ALL）或遍历二级索引（type: index）可能比走树搜索更“划算”，于是直接跳过索引查找逻辑。

• EXPLAIN 中间出现 key: NULL 且 type: ALL 是最直接的信号
• 即使 key 非空（比如显示 t_modified），但 rows 接近表总行数、Extra 没有 Using index condition，也说明只是在全索引扫描，而非高效查找
• 函数调用发生在索引列上，是硬性限制；哪怕函数本身很轻量（如 UPPER()），照样失效

哪些函数操作会触发这个问题

几乎所有对索引列直接调用的标量函数都会破坏索引匹配能力，常见的有：

• 日期类：DATE()、YEAR()、MONTH()、DAY()、DATE_FORMAT()
• 字符串类：UPPER()、LOWER()、TRIM()、LEFT()、SUBSTR()、CONCAT()
• 数值类：ABS()、ROUND()、id + 1 这类表达式（本质也是函数运算）

另外注意，LIKE 不是函数，但它以 % 开头（如 name LIKE '%明'）同样因无法确定字典序起点而跳过索引，原理不同但结果一致。

怎么改写才能重用索引

核心思路是把函数从索引列“移开”，转为对查询值做预处理，或拆成范围条件。

• 日期场景：把 WHERE YEAR(create_time) = 2025 改成 WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'
• 字符串大小写：如果业务允许，建索引时用 COLLATE utf8mb4_0900_as_cs 或确保写入时统一大小写，避免查时用 UPPER()
• 前缀提取：把 WHERE LEFT(phone, 3) = '138' 改成 WHERE phone LIKE '138%'（前提是满足最左前缀）
• 生成列（MySQL 5.7+）：可添加存储列 ADD COLUMN create_date DATE AS (DATE(create_time)) STORED，再给它建索引，绕过原列函数问题

容易被忽略的隐式函数调用

有些“看起来没写函数”的情况，其实 MySQL 在背后悄悄加了转换，一样导致索引失效：

• 字段是 VARCHAR，却传数字：如 WHERE phone = 13800138000 → 实际执行 CAST(phone AS SIGNED)
• 连接字符集与字段字符集不一致：如字段是 utf8mb4，但客户端连接用 latin1 → 等价于 CONVERT(phone USING latin1)
• JOIN 字段字符集不同：两表关联字段字符集不一致，也会触发隐式 CONVERT，不仅影响性能，还可能引发笛卡尔积

这类隐式调用不会出现在 SQL 文本里，只能靠 EXPLAIN 的 Extra 字段（如出现 Using where; Using index 是好的，只有 Using where 且 key 为空，就要怀疑类型或字符集是否对齐）和 SHOW WARNINGS 查看优化器重写后的语句来发现。