本文是 MySQL索引系列的第四篇。在前三篇文章中，我们系统介绍了索引的数据结构、覆盖索引、最左前缀原则、索引下推等核心优化技术，以及字符串索引的优化方法。本文将深入分析索引失效的多种场景及其背后的原理，帮助你全面理解索引为何有时会“失效”，以及如何有效避免和优化这类问题。

一、核心原理：B+树索引的有序性特性

要理解索引失效的原因，我们首先需要回顾B+树索引的核心特性——有序性。InnoDB存储引擎使用的B+树索引结构保持同一层兄弟节点的有序性，这是索引能够快速定位数据的根本原因。

实际上，B+树提供的快速定位能力，正是来源于同一层兄弟节点的有序性。当我们执行等值查询或范围查询时，优化器可以借助这种有序性快速跳过不符合条件的数据块，极大减少需要扫描的数据量。

然而，当我们对索引字段进行函数操作时（下文都默认字段上有索引），问题就出现了：

-- 示例：按月份查询订单数据
SELECT * FROM orders WHERE MONTH(create_time) = 7;

这条SQL语句的问题在于：B+树索引是按照create_time的原始值排序的，而不是按照MONTH(create_time)的计算结果排序的。如果计算month()函数，你会看到传入7的时候，在树的第一层就不知道该怎么办了，因为所有月份的日期值都被转换为1-12的数字，完全破坏了原有的有序性。

也就是说，对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能，转而使用全索引扫描或全表扫描。

二、函数操作导致索引失效的详细分析

2.1 显式函数操作

最常见的索引失效场景就是在索引列上直接使用函数：

WHERE SUBSTRING(name, 1, 4) = 'Johnoh'，在 MySQL 中表示： 筛选出 name 列中「从第 1 个字符开始，连续截取 4 个字符，结果等于 'John'」的所有用户记录。

2.2 隐式类型转换

MySQL的隐式类型转换也会在底层转换为函数操作，导致索引失效：

-- order_no是VARCHAR类型，但用数字查询
SELECT * FROM orders WHERE order_no = 1001;

-- MySQL实际执行的是：
SELECT * FROM orders WHERE CAST(order_no AS SIGNED) = 1001;

MySQL字符转换默认规则：在MySQL中，字符串和数字做比较的话，是将字符串转换成数字。这个规则可以通过简单查询验证：

SELECT '10' > 9;  -- 返回1（true），说明字符串'10'被转换为数字10

如果MySQL将数字转换为字符串，按字符串比较'10'和'9'，应该返回0（false），因为'10'的第一个字符'1'比'9'小。但实际返回1，证实了MySQL的字符串到数字的转换规则。

2.3 关键区别：索引列 vs 查询值

重要区别：只有在索引列上做函数操作才会导致索引失效，在查询值上做函数操作不会影响索引使用：

-- 不会导致索引失效（在查询值上做操作）
SELECT * FROM users WHERE id = 1000 + 1;
SELECT * FROM users WHERE age = '30';  -- 字符串转数字
SELECT * FROM orders WHERE create_time = DATE_ADD('2023-01-01', INTERVAL 7 DAY);

-- 会导致索引失效（在索引列上做操作）
SELECT * FROM users WHERE id + 1 = 1001;
SELECT * FROM users WHERE CAST(age AS CHAR) = '30';
SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m') = '2023-06';

三、隐式字符编码转换的多表关联问题

在多表关联查询中，如果关联字段的字符集不同，也会导致隐式转换和索引失效：

-- 订单表使用utf8mb4字符集
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_no VARCHAR(20) CHARACTER SET utf8mb4,
    KEY idx_order_no (order_no)
);

-- 订单详情表使用utf8字符集
CREATE TABLE order_details (
    id INT PRIMARY KEY,
    order_no VARCHAR(20) CHARACTER SET utf8,
    product_name VARCHAR(100),
    KEY idx_order_no (order_no)
);

-- 关联查询
SELECT o.*, od.* 
FROM orders o 
JOIN order_details od ON o.order_no = od.order_no;

MySQL实际执行的是：

SELECT o.*, od.* 
FROM orders o 
JOIN order_details od ON CONVERT(od.order_no USING utf8mb4) = o.order_no;

由于在order_details表的索引字段order_no上进行了CONVERT函数操作，导致该表的索引无法使用。

到这里，你终于明确了，字符集不同只是条件之一，连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

四、MySQL优化器的"保守"行为

MySQL的优化器确实有"偷懒"的嫌疑，即使简单地把where id+1=1000改写成where id=1000-1就能够用上索引快速查找，也不会主动做这个语句重写。

这意味着开发者需要主动优化查询语句，而不是依赖优化器自动优化：

-- 优化器不会重写这个查询（导致全表扫描）
SELECT * FROM users WHERE id + 1 = 1001;

-- 需要手动重写为（可以使用索引）
SELECT * FROM users WHERE id = 1001 - 1;

这种"保守"行为提醒我们，作为开发者需要具备主动优化意识，不能完全依赖数据库优化器。

前面4种情况其实说的都是同一个事情：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能，导致索引失效。

五、其他常见索引失效场景

除了函数操作，还有多种情况会导致索引无法有效使用：

5.1 违反最左前缀原则

对于复合索引 (col1, col2, col3)，以下查询无法充分利用索引：

最左前缀原则要求查询必须从复合索引的最左边列开始，并且不能跳过中间的列。这是因为B+树索引是按照索引定义的列顺序构建的，如果跳过前面的列，就无法利用索引的有序性。

5.2 LIKE查询以通配符开头

-- 无法使用索引
SELECT * FROM products WHERE name LIKE '%apple%';
SELECT * FROM products WHERE name LIKE '%apple';

-- 可以使用索引
SELECT * FROM products WHERE name LIKE 'apple%';

当LIKE模式以通配符开头时，优化器无法利用索引的有序性进行快速定位，因为无法确定匹配值的前缀。这种情况下，优化器只能进行全表扫描，逐行比较是否匹配模式。

对于%%全模糊匹配，可以考虑使用搜索引擎如Elasticsearch。如果必须使用前导通配符%apple，可以考虑使用反转字符串并建立反转索引的技巧。

5.3 OR条件使用不当

当OR条件中包含未索引列时，整个查询可能无法使用索引：

-- 假设age字段没有索引
SELECT * FROM users WHERE name = 'john' OR age > 30;

-- 优化方案：使用UNION或确保所有OR条件都有索引（但是需要注意union可能会使用临时表）
SELECT * FROM users WHERE name = 'john'
UNION
SELECT * FROM users WHERE age > 30;

MySQL处理OR条件时，如果OR的各个条件都使用独立的索引，可以使用index_merge优化。但如果其中一个条件没有索引，优化器就无法使用任何索引，只能选择全表扫描。

5.4 IN和NOT IN滥用

当IN列表中的值过多时，优化器可能选择全表扫描：

-- 当value_list包含大量值时，可能导致全表扫描
SELECT * FROM products WHERE category_id IN (1, 2, 3, ..., 1000);

-- 最简单的方案就是，分批次查询（拆成5批）
SELECT * FROM products WHERE category_id IN (1, 2, ..., 200);

当IN列表包含大量值时，优化器需要评估回表查询的代价。如果IN列表过大，优化器可能判断全表扫描更高效。

一般来说，当IN列表包含的值超过表中总行数的30%时，优化器倾向于选择全表扫描。

5.5 SELECT * 的性能影响

虽然SELECT *不会直接导致索引失效，但会带来其他性能问题：

1. 无法使用覆盖索引：除非索引字段全覆盖（正常都不会）
2. 网络传输浪费：返回不必要的数据增加了网络传输开销
3. 内存占用增加：需要缓存更大的结果集，可能挤占其他查询的内存资源，影响内存命中率
4. 增加了排序和临时表的使用：当需要排序或分组时，更大的行尺寸会增加临时表的使用

-- 不推荐
SELECT * FROM users WHERE age > 30;

-- 推荐：只选择需要的字段
SELECT id, name, email FROM users WHERE age > 30;

-- 使用覆盖索引优化
CREATE INDEX idx_users_age_covering ON users(age) INCLUDE (id, name, email);
SELECT id, name, email FROM users WHERE age > 30;

六、诊断与优化：使用EXPLAIN深入分析查询

要深入诊断索引是否被正确使用，EXPLAIN命令是最重要的工具。EXPLAIN执行计划包含6个关键字段，每个字段都承载着优化器决策的关键信息：

-- 分析查询执行计划
EXPLAIN SELECT * FROM orders WHERE MONTH(create_time) = 6;

对于这条查询，EXPLAIN结果可能显示：

• type: ALL：表示全表扫描
• key: NULL：表示没有使用索引
• rows: 1000000：表示需要扫描100万行
• Extra: Using where：表示需要逐行判断条件

这表明索引没有被使用，需要进行优化。

如果想深入学习EXPLAIN的详细用法和所有字段含义，推荐阅读我的另一篇文章：《MySQL EXPLAIN执行计划：SQL性能翻倍的秘密武器》

七、总结与最佳实践

通过本文的分析，我们可以看到，大多数索引失效场景都源于同一个根本原因：对索引字段进行了某种形式的操作，破坏了索引值的有序性，导致优化器无法使用索引的快速定位能力。以下是详细的总结和优化建议：

7.1 索引失效场景及解决方案总结表

数据分布倾斜、统计信息过期出现概率较小，因此全文未具体介绍。

核心原因在于：MySQL使用采样统计的方法导致索引统计信息不准确及优化器存在误判的情况。

7.2 核心优化原则

1. 保持索引原始性：避免在索引列上进行任何函数计算、类型转换或表达式运算
2. 注意隐式转换：MySQL的隐式类型转换和字符集转换可能导致意外的函数操作
3. 统一设计规范：保持表结构设计的一致性，避免字符集和排序规则的不匹配
4. 主动优化意识：MySQL优化器不会自动重写所有低效查询，需要开发者主动优化
5. 使用EXPLAIN验证：对关键查询使用EXPLAIN分析执行计划，确保索引被正确使用

7.3 结语

索引优化是数据库性能调优的核心技能，也是一个需要持续学习和实践的过程。通过本文的系统分析，希望你已经理解了各种索引失效场景背后的原理，并掌握了相应的优化方法。

在实际工作中，建议养成以下良好习惯：

• 在编写SQL时就要考虑索引使用情况
• 定期使用EXPLAIN分析关键查询的执行计划
• 监控慢查询日志，及时发现性能问题
• 建立数据库设计规范，避免常见的设计陷阱

数据库优化之路永无止境，但每一步的探索都会带来实实在在的性能提升和更好的用户体验。希望本文能成为你索引优化路上的有力助手，帮助你在工作中解决更多的性能挑战。