索引——数据库性能的命脉
在合肥做后端开发的程序员几乎每天都在和 MySQL 打交道。但你真的理解 MySQL 的索引吗？为什么加了索引还是慢？什么时候该加索引什么时候不该加？复合索引的字段顺序怎么定？本文将从底层原理出发结合实际案例帮你彻底搞懂 MySQL 索引。
B+ 树索引原理
MySQL InnoDB 引擎使用的索引数据结构是 B+ 树(不是 B 树也不是红黑树)。为什么选择 B+ 树？因为它在磁盘 I/O 方面表现最优——B+ 树的非叶子节点只存储键值不存储数据意味着同样大小的磁盘页面能容纳更多的键值从而降低树的高度减少磁盘 I/O 次数。一棵 3 层的 B+ 树可以存储千万级别的数据记录查找任意一条数据最多只需要 3 次 I/O。
B+ 树的特点：叶子节点之间通过双向链表连接这使得范围查询(如 BETWEEN/>/</ORDER BY)非常高效——找到起点后顺着链表遍历即可无需回溯到上层节点。所有数据都存储在叶子节点非叶子节点只起到"导航"作用。每个叶子节点中的数据按索引列排序并且包含了对应行的全部数据(对于聚簇索引)或主键值(对于二级索引)。
聚簇索引 vs 二级索引
这是理解 MySQL 索引最关键的概念：
聚簇索引(Clustered Index)：也叫主键索引。InnoDB 表必须有且只有一个聚簇索引(因为数据实际就是按聚簇索引的顺序物理存储的)。如果你定义了 PRIMARY KEY 那么它就是聚簇索引；如果没有定义 PRIMARY KEY 但有 UNIQUE NOT NULL 的列那么第一个这样的列就是聚簇索引；如果都没有 InnoDB 会自动生成一个隐藏的 ROW_ID 作为聚簇索引。聚簇索引的叶子节点存储的是完整的行数据。二级索引(Secondary Index)：也叫非聚簇索引/辅助索引。你在非主键列上创建的索引都是二级索引。二级索引的叶子节点存储的是索引列的值 + 主键值(注意不是完整行数据！)。这意味着通过二级索引查找数据需要两步：先在二级索引 B+ 树中找到主键值然后再回到聚簇索引 B+ 树中查找完整行数据这个过程叫"回表"。
回表的代价：如果你的查询只需要索引列本身的值(即 SELECT 列都在索引中)那么就不需要回表这叫"覆盖索引(Covering Index)"是性能最好的情况之一。所以在设计复合索引时要尽量考虑常用的 SELECT 列将其包含进去。
Explain 执行计划分析
Explain 是分析 SQL 执行计划的利器。重点关注以下几个字段：
type：访问类型从好到差的顺序是 system > const > eq_ref > ref > range > index > ALL。出现 ALL(全表扫描)通常意味着需要优化。key：实际使用的索引。如果是 NULL 说明没有用到索引。rows：预估需要扫描的行数越少越好。Extra：额外信息——Using index(使用了覆盖索引很好)/Using where(需要在 server 层过滤)/Using filesort(需要额外排序不好)/Using temporary(使用了临时表不好)。
例如：
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 1 ORDER BY created_at DESC LIMIT 20; -- 结果： -- type: ref (用到了索引) -- key: idx_user_status (使用了复合索引) -- rows: 50 (预估扫描50行) -- Extra: Backward index scan (倒序扫描索引 很高效)