索引原理

索引是数据库性能的第一抓手。理解底层数据结构，就能预判一条查询是否走索引、为什么慢。

为什么是 B+Tree

关系型数据库（MySQL、PostgreSQL）默认使用 B+Tree 作为索引结构：

矮胖的多叉树：三层即可存放数千万行，查找只需 3~4 次磁盘 I/O。
所有数据在叶子节点：内部节点只存键，能容纳更多分支，树更矮。
叶子节点形成有序链表：天然支持范围查询与排序。

对比其他结构：

结构	等值查询	范围查询	说明
哈希表	O(1)	不支持	只适合等值，如 Redis/内存索引
二叉搜索树	O(log n)	支持	可能退化为链表
B 树	O(log n)	一般	数据分散在内部节点
B+Tree	O(log n)	优秀	数据集中在叶子、链表串联

聚簇索引与非聚簇索引

以 InnoDB 为例：

聚簇索引（主键索引）：叶子节点存放整行数据。一张表只能有一个。
二级索引（辅助索引）：叶子节点存放主键值，需要时再回表取整行。

CREATE TABLE users (
    id    BIGINT PRIMARY KEY,        -- 聚簇索引
    name  VARCHAR(64),
    email VARCHAR(255),
    city  VARCHAR(64),
    INDEX idx_city (city),           -- 二级索引
    INDEX idx_city_name (city, name) -- 联合索引
);

查询 WHERE city = '北京' 时：先查 idx_city 得到主键，再按主键回聚簇索引取整行 —— 这一步叫回表。

联合索引与最左前缀

联合索引 (a, b, c) 相当于按 a、再按 b、再按 c 排序的电话簿。查询必须从最左列开始连续匹配：

查询条件	能否用上索引
`WHERE a = 1`	✅ 用到 a
`WHERE a = 1 AND b = 2`	✅ 用到 a, b
`WHERE a = 1 AND b = 2 AND c = 3`	✅ 全部
`WHERE b = 2`	❌ 跳过最左列
`WHERE a = 1 AND c = 3`	⚠️ 只用到 a（c 断裂）
`WHERE a = 1 AND b > 2 AND c = 3`	⚠️ c 用不上（范围后失效）

Tip

设计联合索引的口诀：等值在前，范围在后；区分度高在前。把最常用的等值过滤列放左边，范围列放最后。

覆盖索引

如果查询需要的列全都在索引里，就无需回表，称为覆盖索引，性能极佳：

-- 只需 city 与 name，二者都在 idx_city_name 中，无需回表
SELECT city, name FROM users WHERE city = '北京';

-- 用 EXPLAIN 查看 Extra 是否出现 Using index
EXPLAIN SELECT city, name FROM users WHERE city = '北京';

索引失效的常见场景

对索引列做运算或函数：WHERE YEAR(created_at) = 2026。
隐式类型转换：字符串列用数字比较 WHERE phone = 13800000000。
前导模糊匹配：WHERE name LIKE '%abc'（abc% 可以）。
OR 连接非索引列：导致全表扫描。
不满足最左前缀（见上表）。
负向条件：!=、NOT IN 常用于区分度过低时选择全表扫描。

-- 反例：函数导致索引失效
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- 正例：改写为范围查询，可用索引
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

索引的代价

索引不是越多越好：

占用额外磁盘空间。
写入变慢：每次 INSERT/UPDATE/DELETE 都要维护所有相关索引。
优化器可能在索引过多时选错执行计划。

Warning

常见的索引滥用：给每个列都建单列索引（应合并为联合索引）、给低区分度列（如性别）单独建索引、重复建索引（(a) 与 (a,b) 中前者冗余）。

小结

B+Tree 天然适合范围查询与排序，是关系型数据库索引的默认结构。
InnoDB 二级索引存主键，非覆盖查询需回表。
联合索引遵循最左前缀；把等值、高区分度列放左侧。
索引有写入代价，按真实查询模式设计，避免滥用。

#索引原理

#为什么是 B+Tree

#聚簇索引与非聚簇索引

#联合索引与最左前缀

#覆盖索引

#索引失效的常见场景

#索引的代价

#小结