MySql存储引擎与底层结构

第 3 章：索引原理与优化
这是数据库精通最重要的专题之一。
3.1 为什么使用 B+ 树？
假设一张表有 1000 万条记录。

如果没有适合的索引，数据库可能需要检查大量记录才能找到目标数据。

B+ 树通过多层索引缩小搜索范围：
根节点

确定搜索方向

中间节点 A

中间节点 B

叶子节点

保存索引记录，叶子层按键值有序链接

B+ 树的优势：

1. 多叉结构降低树高，减少页面访问次数。
2. 叶子层有序，适合范围查询。
3. 索引节点主要保存键值和指针，便于在一个页面中容纳更多索引项。
4. 结构适合数据库以页面为单位读写的特性。
   3.2 聚簇索引和二级索引有什么区别？
   假设订单表的主键是 id。

• 聚簇索引： InnoDB 的主键索引叶子节点保存完整行数据。
• 二级索引： 叶子节点保存索引列以及对应的主键值。
  例如：
  CREATE INDEX idx_user_id ON orders(user_id);

SELECT *
FROM orders
WHERE user_id = 1001;

执行流程可能是：
idx_user_id 二级索引
↓
找到 user_id = 1001 对应的主键 id
↓
根据主键查询聚簇索引
↓
读取完整订单记录

这就是回表。

如果查询需要的字段都存在于二级索引中，就可能直接通过二级索引返回结果，不必再查询聚簇索引，这就是覆盖索引。
3.3 联合索引和最左前缀原则
CREATE INDEX idx_user_status_time
ON orders(user_id, status, created_at);

索引的排序逻辑可以理解为：
先按 user_id 排序
再按 status 排序
最后按 created_at 排序

典型查询：
-- 可以利用联合索引
SELECT *
FROM orders
WHERE user_id = 1001;

-- 可以利用联合索引的前缀
SELECT *
FROM orders
WHERE user_id = 1001
AND status = 1;

-- 也可以使用更完整的索引条件
SELECT *
FROM orders
WHERE user_id = 1001
AND status = 1
AND created_at >= '2026-01-01';

下面的查询没有 user_id 条件，通常不能直接利用该索引进行完整的高效定位：
SELECT *
FROM orders
WHERE status = 1;

具体是否使用索引仍取决于执行计划、统计信息、数据分布和查询成本。
3.4 如何判断 SQL 是否走索引？
EXPLAIN
SELECT *
FROM orders
WHERE user_id = 1001
AND status = 1;

重点关注：

• type：访问类型。
• key：实际选择的索引。
• rows：预计检查的行数。
• filtered：预计过滤比例。
• Extra：是否出现覆盖索引、额外过滤或排序等信息。
  不能只看有没有索引，还要看扫描行数、回表成本、排序成本和实际执行耗时。
  3.5 常见索引失效或效率下降的情况
  -- 对索引列做函数运算，通常不利于普通索引范围查找
  SELECT * FROM orders
  WHERE DATE(created_at) = '2026-10-19';

-- 改为时间范围
SELECT * FROM orders
WHERE created_at >= '2026-10-19 00:00:00'
AND created_at < '2026-10-20 00:00:00';

其他需要掌握的场景：

• 对索引列进行计算或类型转换。
• 使用前置通配符，例如 LIKE '%abc'。
• 联合索引不符合有效的前缀条件。
• 低选择性条件导致优化器认为全表扫描更便宜。
• 查询返回大量字段，产生较多回表。
• 索引顺序无法满足排序需求。
  进阶要求： 能够通过执行计划、数据分布和 SQL 改写来证明优化效果，而不是盲目加索引。