2. 索引原理¶

索引是数据库性能优化的基石。面试中，关于索引的问题往往从“怎么用”深入到“底层结构”再到“优化策略”。

2.1 为什么是 B+ 树？¶

这是面试中最经典的问题。为了回答好这个问题，我们需要对比其他数据结构。

哈希表 (Hash)：
- 优点：查询速度极快，时间复杂度 O(1)。
- 缺点：不支持范围查询（如 > 5），不支持排序，不支持模糊查询。只适合等值查询。
二叉查找树 (BST) / 平衡二叉树 (AVL)：
- 缺点：树的高度较高。对于几百万的数据，树高可能达到十几层。每一层都需要一次磁盘 I/O，性能极差。
B 树 (B-Tree)：
- 特点：每个节点都存储 key 和 data。
- 缺点：由于非叶子节点也存储数据，导致每个节点能存储的索引项变少，树的高度依然较高。且范围查询需要中序遍历，效率一般。

B+ 树的绝对优势： 1. 更矮胖：非叶子节点只存储 key，不存储 data。这使得一个节点（通常一页 16KB）可以存储更多的索引项。通常 3-4 层的 B+ 树就可以支撑千万级的数据量。这意味着查询任何数据最多只需要 3-4 次磁盘 I/O。 2. 扫库扫表能力强：B+ 树的所有叶子节点通过**双向链表**连接。这使得范围查询（如 BETWEEN）和全表扫描变得非常高效，只需要遍历叶子节点链表即可。 3. 查询性能稳定：所有数据都存储在叶子节点，查询任何数据的路径长度相同。

2.2 聚簇索引 vs 非聚簇索引¶

这是 InnoDB 和 MyISAM 最大的区别之一。

聚簇索引 (Clustered Index)：
- 定义：索引结构和数据一起存放的索引。InnoDB 的主键索引就是聚簇索引。
- 结构：叶子节点存储了完整的行数据。
- 优势：查询主键时，直接获取数据，无需回表。
非聚簇索引 (Secondary Index)：
- 定义：索引结构和数据分开存放的索引。InnoDB 的普通索引（辅助索引）就是非聚簇索引。
- 结构：叶子节点存储的是**索引列的值**和**主键值**。
- 回表：如果通过辅助索引查询非索引列的数据，需要先在辅助索引中找到主键，然后再去聚簇索引中根据主键找到完整的行数据。这个过程称为**回表**。

面试题：为什么建议 InnoDB 表必须建主键，且推荐使用整型的自增主键？

解析： 1. 如果未定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有，它会隐式创建一个 6 字节的 row-id，这会增加维护成本。 2. 整型：比较速度快，占用空间小。 3. 自增：保证每次插入都是追加操作，不会引起 B+ 树的页分裂和大规模的数据移动，性能最高。

2.3 索引的高级特性¶

2.3.1 覆盖索引 (Covering Index)¶

如果一个索引包含（或覆盖）了所有需要查询的字段的值，我们就称之为“覆盖索引”。 * 优势：不再需要回表操作，极大地减少了 I/O 次数。 * 案例：表 user (id, name, age)，索引 idx_name_age (name, age)。 * SELECT age FROM user WHERE name = 'Alice' -> 命中覆盖索引，无需回表。

2.3.2 索引下推 (Index Condition Pushdown, ICP)¶

MySQL 5.6 引入的优化。 * 原理：在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。 * 案例：索引 (name, age)。 * SELECT * FROM user WHERE name LIKE '张%' AND age = 10。 * 无 ICP：先找到所有姓张的主键，回表查出数据，再判断 age 是否等于 10。 * 有 ICP：在索引内部就判断 age 是否等于 10，不符合的直接跳过，大大减少回表次数。

2.4 索引失效的十大场景¶

这是面试中的“送分题”，也是“送命题”。

不满足最左前缀原则：联合索引 (a, b, c)，查询 WHERE b=1 或 WHERE c=1 失效。
索引列上做计算：WHERE id + 1 = 10 失效。
索引列上使用函数：WHERE SUBSTR(name, 1, 3) = 'abc' 失效。
类型转换：字符串不加单引号。WHERE name = 123（name 是 varchar），发生隐式转换，失效。
LIKE 以通配符开头：WHERE name LIKE '%abc' 失效。LIKE 'abc%' 走索引。
OR 连接条件：WHERE a=1 OR b=2，如果 b 没有索引，则整个索引失效。
不等于比较：!= 或 <> 通常会导致索引失效（取决于数据量）。
IS NULL / IS NOT NULL：根据数据分布情况，可能失效。
数据分布影响：如果 MySQL 估计全表扫描比走索引更快（例如表中绝大多数数据都符合条件），则不走索引。
字符集不统一：连接查询时，如果两个表的字符集不一致，索引可能失效。