MySQL索引失效的常见场景有哪些？

Question

在MySQL中，索引失效的常见场景之一是**在索引列上使用函数或表达式**。例如，执行查询 `SELECT * FROM users WHERE YEAR(created_at) = 2023;` 时，即使 `created_at` 字段上有索引，MySQL也无法直接使用该索引，因为对字段应用了 `YEAR()` 函数，导致索引失效。优化方式是改写为范围查询：`WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'`，从而有效利用索引提升查询性能。这类问题在实际开发中频繁出现，需特别注意。

Answer 1

一、索引失效的常见场景：在索引列上使用函数或表达式

在MySQL查询优化中，索引是提升数据检索效率的核心机制。然而，即使字段上建立了索引，若在查询条件中对索引列应用了函数或表达式，索引将无法被有效利用，从而导致全表扫描，严重影响性能。

1. 问题现象：为何函数会导致索引失效？

MySQL的B+树索引结构依赖于字段的原始值进行有序存储和快速查找。当查询中对索引列使用函数（如YEAR()、UPPER()、DATE()等）时，数据库必须先对每一行数据计算函数结果，再与条件比较。这意味着无法直接通过索引定位数据，破坏了索引的有序性优势。

-- 示例：索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2023;
-- 即使 created_at 有索引，也无法使用

2. 深层原理：索引如何工作与函数的干扰

B+树索引基于键值排序，支持范围查找和等值匹配。但函数改变了原始键值，使得索引条目不再对应查询中的表达式结果。例如，created_at 存储的是完整时间戳，而 YEAR(created_at) 是派生值，索引中并不存在该派生列。

• 索引列必须以“原样”出现在WHERE子句中才能被使用
• 任何对列的运算（包括类型转换）都会阻止索引命中
• 执行计划中通常表现为type=ALL，即全表扫描

3. 常见的函数导致索引失效场景

错误写法	正确改写方式	说明
WHERE UPPER(name) = 'JOHN'	WHERE name = 'john' 或 使用函数索引	大小写不敏感可考虑归一化存储
WHERE DATE(created_at) = '2023-01-01'	WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02'	避免使用DATE()函数
WHERE id + 1 = 10	WHERE id = 9	避免对索引列做算术运算
WHERE CAST(age AS CHAR) = '25'	WHERE age = 25	类型转换也会导致失效

4. 解决方案：重构查询以保留索引可用性

核心思想是将函数从索引列转移到常量一侧，保持索引列“裸露”状态。以下为日期年份查询的优化示例：

-- 原始低效查询
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 优化后高效查询
SELECT * FROM users 
WHERE created_at >= '2023-01-01' 
  AND created_at < '2024-01-01';

此改写利用了索引的范围扫描能力（range类型），显著减少I/O开销。

5. 高级应对策略：函数索引（MySQL 8.0+）

对于确实需要函数查询的场景，MySQL 8.0引入了函数索引（Functional Index）功能，允许在表达式上创建索引。

-- 创建函数索引
CREATE INDEX idx_year_created ON users ((YEAR(created_at)));

-- 此时以下查询可使用索引
SELECT * FROM users WHERE YEAR(created_at) = 2023;

注意：函数索引会增加存储和维护成本，应权衡使用。

6. 分析过程：如何识别此类问题？

通过EXPLAIN命令分析执行计划是诊断索引失效的关键手段。

EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2023;

重点关注以下字段：

• type：若为ALL，表示全表扫描
• key：显示实际使用的索引，NULL表示未使用
• Extra：出现Using where但无Using index可能存在问题

7. 可视化流程：索引使用决策路径

graph TD
            A[SQL查询包含索引列] --> B{索引列是否被函数或表达式包裹?}
            B -- 是 --> C[索引无法直接使用]
            B -- 否 --> D[检查是否满足最左前缀原则]
            C --> E[考虑重写查询或创建函数索引]
            D --> F[索引可被使用]
            E --> G[评估性能影响]
            G --> H[实施优化方案]

8. 实际开发中的规避建议

1. 代码审查时重点关注WHERE子句中的字段操作
2. 建立SQL规范，禁止对索引列使用函数
3. 使用ORM时注意生成的SQL语句，避免隐式函数调用
4. 定期通过慢查询日志分析潜在的索引失效问题
5. 对高频查询进行执行计划验证
6. 考虑在应用层处理格式转换，而非数据库层
7. 使用Generated Columns配合普通索引替代函数索引（兼容性更好）
8. 监控Created_tmp_disk_tables和Select_scan等状态变量
9. 采用Percona Toolkit等工具进行自动化SQL审计
10. 培训团队成员理解索引底层机制