索引的有序数据结构

索引是一种有序的数据结构，帮助数据库系统高效定位和检索数据。MySQL 默认存储引擎 InnoDB 中的索引采用 B+ 树结构（叶子节点按键值有序排列，并通过链表相连。如图所示，B+ 树是一棵多路平衡树，所有键值都存储在叶子节点中，并按顺序链接，内部节点只存储索引键用于快速定位。此外，一些存储引擎（如 MEMORY 引擎）还可以使用哈希索引，但在 InnoDB 中主要还是以 B+ 树为底层结构。B+ 树的这些特性保证了索引列的键值是顺序排列的，从而可以进行高效的范围查询和精确查找。

索引失效的原因

索引并非在所有情况下都被使用。当查询条件无法充分利用索引结构或返回的数据量过大时，存储引擎可能放弃索引而进行全表扫描​。如图所示，红线表示全表扫描成本（随检索行数几乎不变），绿色曲线表示使用索引时的成本会随着结果行数增加而上升​。因此，如果查询结果行数很多，使用索引的成本可能超过全表扫描，优化器会判定全表扫描更优。常见导致索引失效的情形包括：在查询条件中对索引列进行函数计算、使用不等式或不连续列查询、或者返回大范围数据等。例如：

-- 示例：WHERE 子句中对列进行了函数计算

SELECT * FROM Orders WHERE YEAR(order_date) = 2022;

上述查询因为对 order_date 列进行了函数运算，无法直接利用 B+ 树索引顺序，导致索引失效。类似地，如果查询条件返回绝大部分行（如 price > 0），优化器也可能选择不使用索引而全表扫描。

组合索引失效情况

复合索引在查询时要满足“左前缀原则”，否则会失效。常见失效情况包括：

前导列使用 IN 或 <>：若组合索引如 (a, b, c) 的查询条件对首列 a 使用了 IN 或不等号，后续列的排序关系会被破坏。例如：

CREATE INDEX idx_abc ON T(a, b, c); SELECT * FROM T WHERE a IN (1,2,3) AND b = 10;

在该查询中，a 列使用了 IN，存储引擎只能对每个 a 的取值分别扫描索引区间，此时无法在索引中同时按 b 的连续范围查找，导致索引对 c 列的排序特性也失效。

中间列使用范围查询：若复合索引 (a, b, c) 对中间列 b 使用了范围条件（>、<、BETWEEN 等），则后续列 c 也无法利用索引顺序。示例：

CREATE INDEX idx_abc ON T(a, b, c);

SELECT * FROM T WHERE a = 1 AND b BETWEEN 100 AND 200 AND c = 'X';

此时索引可以定位到满足 a=1 的叶子节点区间，但因为 b 列使用了范围查询，存储引擎只会扫描符合 b 范围的节点，无法再按顺序使用 c 列进行查找。

非组合索引失效情况

单列索引（非组合索引）也有常见的失效情形：

LIKE 前缀通配符：若在 LIKE 模糊查询中将通配符放在前缀，如 '%abc'，索引失效。例

CREATE INDEX idx_name ON Users(name);

SELECT * FROM Users WHERE name LIKE '%Smith';

由于 % 放在最前面，存储引擎无法利用索引定位字符串起始位置，从而无法使用索引。

LIKE 中间/后缀通配符：若通配符位于中间或尾部（如 'a%bc'），则只要前缀字符固定，索引可以用于快速定位前缀位置，再进行后续匹配，不会导致失效。例：

SELECT * FROM Users WHERE name LIKE 'J%son';

这里前缀 'J' 是确定的，索引可以定位到以 'J' 开头的记录，然后顺序匹配后续字符。

IN、<> 或大范围查询：即使技术上索引可用，在数据范围很大时优化器可能选择全表扫描。比如：

SELECT * FROM Orders WHERE status <> 'Completed';

对多数记录有效的不等于查询，可能匹配几乎全表数据，优化器倾向于全表扫描​。类似地，如果使用 IN 列出大量取值（如数十个类别），也可能因匹配行数过多而不使用索引。

列上做函数计算导致失效

如果在查询条件中对索引列进行了函数或表达式计算，索引也会失效。比如：

SELECT * FROM Employees WHERE YEAR(hire_date) = 2023;

上述查询在 hire_date 列上使用了 YEAR() 函数，使得索引键的原始顺序无法被直接利用，必须先计算每行然后再过滤，因此无法走索引。类似地，对列进行加减运算、字符串函数等操作，都会破坏索引结构，导致退化为全表扫描。