MySQL索引失效的五大场景与实战验证

现象或背景

在处理大数据量查询时，我们经常会遇到性能瓶颈。例如，一个核心转化率指标在DQC全绿的情况下，由于上游埋点当天有3个版本在灰度，事件覆盖率实际只有78%，导致业务方拿这个数做投放决策，事后发现偏差近10%。这表明，DQC全绿 ≠ 指标可信，告警只回答了"有没有坏"，没回答"到底有多坏、坏到什么程度还可用"。

原理或根因分析

索引失效的常见原因包括：

1. 隐式类型转换：当VARCHAR字段传入int参数时，MySQL会将整列转换为数字进行比较，导致索引失效。
2. 函数包裹：在WHERE子句中对列使用函数，如UPPER(email)，会使索引失效。
3. collation不一致：JOIN两侧的collation不一致可能导致索引静默失效。
4. 覆盖索引缺失：查询需要的数据不在索引列中，导致回表查询。
5. 索引选择性差：索引列的选择性不足，导致优化器选择全表扫描。

具体做法

1. 避免隐式类型转换

确保查询参数类型与列类型一致，避免隐式转换。例如，将VARCHAR字段与int参数比较时，显式转换参数类型：

SELECT * FROM users WHERE email = '123456';

2. 避免函数包裹

在WHERE子句中避免对列使用函数，改用其他方式实现相同逻辑。例如，将UPPER(email) = ? 改为 email IN (SELECT ... FROM ... WHERE ...):

SELECT * FROM users WHERE email IN (SELECT ... FROM ... WHERE ...);

3. 保持collation一致

确保JOIN两侧的collation一致，避免因collation不一致导致索引失效。

4. 使用覆盖索引

为查询所需的列创建覆盖索引，确保查询所需数据全部包含在索引中，避免回表查询。

5. 提高索引选择性

选择高选择性的列作为索引列，避免索引选择性差导致优化器选择全表扫描。

踩过的坑或反直觉之处

1. 隐式类型转换：在VARCHAR字段传入int参数时，MySQL会将整列转换为数字进行比较，导致索引失效。
2. 函数包裹：在WHERE子句中对列使用函数，如UPPER(email)，会使索引失效。
3. collation不一致：JOIN两侧的collation不一致可能导致索引静默失效。
4. 覆盖索引缺失：查询需要的数据不在索引列中，导致回表查询。
5. 索引选择性差：索引列的选择性不足，导致优化器选择全表扫描。

结论与可复用清单

• 避免隐式类型转换：确保查询参数类型与列类型一致。
• 避免函数包裹：在WHERE子句中避免对列使用函数。
• 保持collation一致：确保JOIN两侧的collation一致。
• 使用覆盖索引：为查询所需的列创建覆盖索引。
• 提高索引选择性：选择高选择性的列作为索引列。

通过以上方法，可以有效避免索引失效，提高查询性能。