一张表在 InnoDB 里到底有几棵树，取决于你建了几个索引。主键那棵树的叶子直接装整行，其余每棵树的叶子只装"索引列 + 主键值"。理解这句话，EXPLAIN 里的 Using index、Using index condition、rows 估算才都有落脚点。以下内容按 MySQL 8.0 InnoDB 核对。

---

1. 聚簇索引：主键即是数据

InnoDB 是索引组织表（index-organized table）。行数据按主键顺序存在 B+ 树叶子页里，这棵树就是表本身，叫聚簇索引。没有主键时选第一个非空唯一索引，都没有就生成 6 字节隐藏列 DB_ROW_ID。

CREATE TABLE t_order (
  id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  status  TINYINT NOT NULL DEFAULT 0,
  amount  DECIMAL(12,2) NOT NULL,
  KEY idx_user (user_id)
) ENGINE=InnoDB;

结构	叶子节点存什么	对应几棵树
聚簇索引	整行所有列 + 事务信息	表本身，1 棵
二级索引	索引列 + 主键值	每个二级索引 1 棵
唯一二级索引	同上（无主键列时为 row id）	每个唯一索引 1 棵

主键顺序即物理顺序：递增主键让插入集中在最右页，UUID 主键则散落随机页、触发页分裂与更多脏页刷盘。

---

2. 二级索引与回表

二级索引叶子的"值"是主键，不是行。查非索引列必须先拿到主键，再去聚簇索引取整行，这个动作叫回表（bookmark lookup）。

-- 走 idx_user，需要回表取 status / amount
SELECT status, amount FROM t_order WHERE user_id = 10086;

-- 典型执行计划（MySQL 8.0，非实测输出）
-- id=1 type=ref key=idx_user rows≈N Extra=NULL

查询形态	是否回表	代价模型
二级索引列 + 主键列	否	只扫二级索引
二级索引列 + 其他列	是	每命中一行一次聚簇查找
主键等值	否	直接定位聚簇索引

回表次数由命中行数决定。rows 估算偏大时优化器会放弃 idx_user 转而全表扫——顺序扫比大量随机回表便宜，这是很多人误判"索引失效"的真实原因。

---

3. 覆盖索引与 Using index

查询需要的列全部在二级索引里，就不回表，Extra 显示 Using index，称为覆盖索引。

-- 覆盖：user_id 与 id 都在 idx_user 的叶子里
SELECT id FROM t_order WHERE user_id = 10086;

-- 覆盖（把需要的列放进索引）
ALTER TABLE t_order ADD KEY idx_user_status (user_id, status);
SELECT status FROM t_order WHERE user_id = 10086;

Extra 值	含义	读法
Using index	覆盖索引，无回表	最理想，注意别让它变成"索引膨胀"
Using index condition	用了索引下推（ICP）	条件下推到引擎层过滤，减少回表
Using where	服务层还要再过滤	索引没吃住全部条件

覆盖索引不是越多越好：把 status、amount 塞进二级索引等于造第二份表，写入放大与 buffer pool 占用都会上升。

---

4. 联合索引：最左前缀与索引下推

联合索引 (a, b, c) 先按 a、再按 b、再按 c 排序，所以可用条件必须从最左列起连续。

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

-- 能用：a / a,b / a,b,c
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;
-- 只能用 a：中间断了
SELECT * FROM t WHERE a = 1 AND c = 3;
-- 完全用不上：首列缺失
SELECT * FROM t WHERE b = 2;

范围条件会截断后续列：b > 2 之后的 c 只能靠 ICP 过滤，不参与定位。

-- 典型执行计划：Extra=Using index condition
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3;

ICP（Index Condition Pushdown）把 c 的判断下推到 InnoDB 层，在回表前先筛掉不匹配的行，减少回表次数。EXPLAIN 出现 Using index condition 说明它在工作；出现 Using where 则表示过滤发生在 Server 层，回表已经做完了。

---

5. 前缀索引与选择性

长字符串列整列建索引会让索引页数暴涨；前缀索引只取前 n 个字符，用选择性决定 n。

SELECT COUNT(DISTINCT LEFT(email, 8))  / COUNT(*) AS sel_8,
       COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS sel_12,
       COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS sel_20,
       COUNT(DISTINCT email)           / COUNT(*) AS sel_full
FROM t_user;

指标	计算方式	判断
选择性	count(distinct left(col,n)) / count(*)	越接近 1 越好
经验阈值	通常 ≥ 0.9	低于则前缀区分度不足
索引长度	n × 字符集字节数	utf8mb4 一个字符最多 4 字节

代价是无法覆盖索引：必须回表验证完整值，Using index 不出现。

ALTER TABLE t_user ADD KEY idx_email_prefix (email(12));

---

6. 自适应哈希索引（AHI）

InnoDB 会监控索引页的等值查找模式，对反复命中的热页在内存建哈希表，把多层 B+ 树查找压成一次定位。开关只能整体做。

SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
-- ON / OFF，默认 ON

SHOW ENGINE INNODB STATUS\G
-- 关注 INSERT BUFFER AND ADAPTIVE HASH INDEX 段的
-- hash searches/s、non-hash searches/s

特性	说明
触发条件	同一索引前缀被等值查询反复命中
可干预程度	只能 innodb_adaptive_hash_index=OFF 整体关
冲突风险	高并发下 AHI 分区锁（btr_search_latch）可能成为争用点，常见于大量范围扫描 + 多 buffer pool 实例的场景

关 AHI 不是常规操作：只在 SHOW ENGINE INNODB STATUS 里能看到 btr_search_latch 等待时才值得做 A/B 对比。

---

7. 全文索引与倒排

FULLTEXT 走倒排索引：词项 → 文档 id 列表，与 B+ 树是两套结构。

CREATE TABLE t_article (
  id BIGINT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT KEY ft_body (body) WITH PARSER ngram
) ENGINE=InnoDB;

SELECT id, MATCH(body) AGAINST ('缓存 失效' IN BOOLEAN MODE) AS score
FROM t_article
WHERE MATCH(body) AGAINST ('缓存 失效' IN BOOLEAN MODE)
ORDER BY score DESC LIMIT 20;

维度	B+ 树索引	全文索引
适用	前缀、范围、排序	关键词检索、相关度排序
中文分词	不涉及	需 ngram 解析器（默认 2 元组）
更新代价	局部页修改	倒排表维护，写入更重

LIKE '%关键词%' 前导通配走不了 B+ 树；真要模糊搜索就上全文索引或交给 ES。

---

8. 索引合并（index merge）

多列各有独立索引且选择性都不错时，优化器可能分别扫两个索引再取交并。

SELECT * FROM t_order WHERE user_id = 10086 OR status = 3;

-- 典型执行计划
-- type=index_merge
-- key=idx_user,idx_status
-- Extra=Using union(idx_user,idx_status); Using where

形态	Extra 提示	何时出现
交集	Using intersect(...)	多个等值条件、各自索引都够选择性
并集	Using union(...)	OR 连接不同索引列
排序并集	Using sort_union(...)	范围条件组合，需要先排序主键

索引合并常是缺联合索引的兜底；OR 高频就直接建 (user_id, status) 或改 UNION ALL。

---

9. EXPLAIN 各列怎么读

EXPLAIN FORMAT=TRADITIONAL
SELECT id, amount FROM t_order
WHERE user_id = 10086 AND status = 1
ORDER BY created_at DESC LIMIT 10;

列	读法	该警惕什么
type	访问类型等级	出现 ALL、index 要复查
key	实际用的索引	NULL 说明没走索引
key_len	索引使用字节数	小于索引定义长度 → 只用了一部分列
rows	预估扫描行数	与实际命中量级差太多 → 统计信息过期
filtered	扫描后剩余百分比	rows × filtered 才是回表估算量
Extra	附加动作	见 Using filesort、Using temporary

type 由好到差：system → const → eq_ref → ref → range → index → ALL。index 是全索引扫描，比 ALL 好不了多少；ref 指非唯一等值。

key_len 的算法是逐列累加：

列类型	字节	备注
INT	4	可空 +1
BIGINT	8	可空 +1
DATETIME	5（8.0 默认）	旧格式 8
VARCHAR(n) utf8mb4	4n + 2	变长长度前缀 2 字节

rows 是采样估算：information_schema.tables 的 TABLE_ROWS 对 InnoDB 同为采样值，别拿它当真实行数分页。

---

10. 索引失效的四种典型

写法	为什么失效	改法
WHERE DATE(created_at) = '2026-01-01'	列上套函数	改范围 >= '...' AND < '...'
WHERE phone = 13800000000（列是 varchar）	隐式转换，列被转成数字	参数加引号
WHERE name LIKE '%张%'	前导通配	全文索引 / 反转列 / ES
WHERE a = 1 OR b = 2（不同列）	单索引无法覆盖两支	联合索引或 UNION ALL

隐式转换最隐蔽：varchar 列与数字比较时 MySQL 把列转成 double，索引失效；反过来数字列与字符串比较则参数被转换，索引仍可用。

---

11. 排错：五种"建了索引还是慢"

现象	常见原因	处理
key 有值但 rows 极大	选择性差，优化器仍要扫很多行	建更精确的联合索引，或加过滤条件
key=NULL 且 type=ALL	隐式转换 / 函数 / 前导通配	按第 10 节逐条对照改写
有索引但选错	统计信息过期、回表代价估算偏差	ANALYZE TABLE，或 FORCE INDEX 临时验证
Using filesort 消不掉	排序方向与索引顺序不一致	建与 ORDER BY 同序的联合索引
写入变慢、页分裂多	索引过多、随机主键	砍掉低频索引，改递增主键

---

12. 边界：什么时候不该再加索引

场景	更合适的做法	理由
报表类任意维度聚合	列存 / OLAP（ClickHouse、Doris）	B+ 树撑不住多维度即席查询
模糊搜索	全文索引或 ES	前导通配无法索引化
超高写入且只需点查	精简索引 + 合理主键	每个二级索引都是一次写放大
单表过亿且历史数据冷	分区 / 归档	索引再优也挡不住 B+ 树变高

聚簇决定行怎么存，二级索引决定要不要回表，覆盖与 ICP 决定回表几次，AHI 决定热等值查询快不快。

---

13. 自测

1. 一张有 3 个二级索引的 InnoDB 表，物理上一共有几棵 B+ 树？
2. Using index 与 Using index condition 的本质区别是什么？
3. 联合索引 (a, b, c) 上执行 WHERE a = 1 AND c = 3，key_len 会是多少？
4. 前缀索引为什么无法做覆盖索引？
5. type=index 和 type=ALL 差在哪里，为什么前者也常常不能接受？
6. varchar 列与数字比较导致索引失效，根因是什么？