主题 11MySQL 索引

11.1为什么第四站跳到了索引

路线图里 04 到 10 还没写，先写 11，原因是排序标准变了：前三章按「和算法的重合度」排， 这一章按「面试出现的频率 × 你能不能当场演示」排。索引这两项都是满的——只要岗位涉及数据库， 「索引怎么优化」几乎必问，而且可以现场跑给你看。

先看这一章要解决的问题长什么样：

11.2为什么不直接用二叉搜索树：磁盘 IO 才是瓶颈

你在二叉搜索树专题里已经知道：BST 查找是 O(log n)，100 万个节点约 20 层。 那数据库为什么不用它？因为内存里的 log n 和磁盘上的 log n 是两件事。

关键差别在单位：算法题里一次「访问节点」是读内存，纳秒级；数据库里一次「访问节点」如果落在磁盘上， 是读一个页（16KB），机械盘约 10ms，SSD 约 0.1ms。差了五个数量级。 所以数据库优化的目标不是「比较次数最少」，而是「访问页的次数最少」。

「每个节点能带 1600 个孩子」不是拍脑袋的数字，可以算出来，我们直接实测验证。

实测：10 万行到底占了几个页

-- 建表：6 个字段，主键 int，其余 varchar / tinyint / datetime
create table t_user (
  id int unsigned auto_increment primary key,
  name varchar(32) not null,
  age tinyint unsigned not null,
  city varchar(16) not null,
  email varchar(64) not null,
  created_at datetime not null
) engine=InnoDB default charset=utf8mb4;

-- 灌入 10 万行后查真实占用
select table_rows, data_length, index_length
from information_schema.tables
where table_schema='idx_lab' and table_name='t_user';

实测结果：

table_rows = 99833
data_length  = 7880704   -- 7.52 MB
index_length = 0         -- 只有主键，还没建二级索引

7880704 / 16384 ≈ 481 个页
100000 / 481   ≈ 208 行/页

两个数字直接推出来了：

1. 一个 16KB 的叶子页能放 208 行。（每行约 78 字节，6 个字段 + 行头 + 事务 ID 之类）
2. 一个非叶子页能放约 1638 个指针。（InnoDB 主键索引的指针 = 主键 4 字节 + 页号 6 字节 = 10 字节， 16384 / 10 ≈ 1638）

于是层数和容量的关系就出来了：

B+ 树相比 B 树，多做了两件事

11.3聚簇索引与回表：一条查询到底读了几次

InnoDB 的索引分两种，记它们的区别只要记「叶子节点存什么」：

「回表」就是：在二级索引上找到主键，再拿主键去聚簇索引导一次，才能拿到整行。 两次 B+ 树查找，第二次还可能多几次 IO。我们把它测出来。

无索引 vs 有索引：相差 450 倍

-- 建索引前
select * from t_user where name = 'abc123';
-- type: ALL | key: None | rows: 99722 | Extra: Using where

type=ALL 是全表扫描，rows=99722 表示优化器估计要扫近 10 万行。 建上索引再看：

alter table t_user add index idx_name(name);

select * from t_user where name = 'abc123';
-- type: ref | key: idx_name | key_len: 130 | rows: 1 | Extra: None

同一张表、同一条 SQL，rows 从 99722 变成 1。 实际耗时（连跑 5 次取最快）：

注意 key_len = 130 这个数：name 是 varchar(32)、utf8mb4， 一个字符最多占 4 字节 → 32 × 4 = 128，再加 2 字节存变长长度 = 130。 key_len 是执行计划里最被低估的一列，它等于「索引实际用了多长」，后面 11.4 会靠它反推用到了几列。

为什么建议用自增主键

因为聚簇索引的叶子要按主键有序排列。用自增 ID，新行永远追加在最右边，只影响最后一个页； 用 UUID 这类随机值，插入位置散布在全树各处，会频繁触发页分裂——一个满页被拆成两半， 空间利用率和写入性能一起下降。这不是理论：同样的数据量，随机主键的表比自增主键的表可能要大一倍。

11.4联合索引与最左前缀：让 key_len 自己说话

「最左前缀」这四个字几乎人人会背，但大多数人背的是结论，说不出原因。 我们用 key_len 把它测出来——这一节的方法比结论重要。

alter table t_user add index idx_city_age(city, age);

第一组：用一列 vs 用两列

-- 只查 city
select * from t_user where city = '北京';
-- type: ref | key: idx_city_age | key_len: 66 | rows: 5083

-- 两列都查（写在前后来都一样）
select * from t_user where age = 30 and city = '北京';
-- type: ref | key: idx_city_age | key_len: 67 | rows: 128

把 key_len 拆开看：

第二组：跳过最左列会发生什么

-- 跳过 city，只查 age
select * from t_user where age = 30;
-- type: ALL | key: None | rows: 99678 | Extra: Using where

type=ALL，索引完全没用上。原因不是「MySQL 笨」，而是索引里行的顺序是「先按 city、再按 age」。 想象一本先按省份、再按城市排序的电话簿，你只给「城市」不给出省，这本书对你毫无帮助—— 北京的「朝阳区」和吉林的「朝阳区」散落在两个相隔几百页的位置。

第三组：范围条件为什么会让后面的列失效

这一组是全场最有价值的两条对比，很多人从来没注意过：

-- ① city 用范围，age 用等值
select * from t_user where city > '南' and age = 30;
-- type: ALL | key: None | rows: 99678 | Extra: Using where     ← 索引直接没用

-- ② city 用等值，age 用范围
select * from t_user where city = '北京' and age > 30;
-- type: range | key: idx_city_age | key_len: 67 | rows: 3521 | Extra: Using index condition

同样是「一列范围 + 一列等值」，结果完全不同：

1. ① 为什么干脆不走索引？city > '南' 命中的城市很多，每个城市下面又散着各个 age， 顺着索引走一遍并不比全表扫便宜。优化器基于成本估算主动放弃了索引—— 这不是「失效」，是「不划算」。
2. ② 为什么走了 range 但也只用了一列？city='北京' 先精确定位到一段， 但这段内部的 age 虽然有序，age > 30 之后还要逐行取出来判断。 key_len 显示 67（两列宽度），但 Extra 里是 Using index condition （索引下推），不是 Using index（覆盖）。
3. 结论口诀：等值列放前面，范围列放最后。反了就会浪费后面所有列。

11.5覆盖索引：不用回表的查询能快多少

如果查询要的列全在索引里，就不必回表。执行计划里这句 Extra: Using index 就是标志。

-- 要整行 → 回表
select * from t_user where name = 'abc123';
-- type: ref | key: idx_name | rows: 1 | Extra: None

-- 只要 id 和 name → 索引里都有，不回表
select id, name from t_user where name = 'abc123';
-- type: ref | key: idx_name | rows: 1 | Extra: Using index

-- 只要 name → 完全覆盖
select name from t_user where name = 'abc123';
-- type: ref | key: idx_name | rows: 1 | Extra: Using index

三条 SQL 的 type 和 rows 一模一样，差别只在 Extra。 行数少的时候差别看不出来（都是 0.1ms 级），但结果集一大，差异就是线性的： 回表 1 万次 = 1 万次随机 IO，不回表 = 顺着索引页顺序读完。

实战里怎么用

不是让你到处堆联合索引，而是让高频查询的列刚好被某个索引覆盖。 顺序上有个技巧：把范围条件放在最后，等值列放前面，这样 select 里的列也更容易全在索引里。

-- 高频查询：按城市 + 年龄段查用户 id 和 name
-- 索引 (city, age) 已经存在，再加 name 就能覆盖
alter table t_user add index idx_city_age_name(city, age, name);

select id, name from t_user where city = '北京' and age = 30;
-- Extra: Using index   ← 一次都不用回表

11.6索引失效：六种写法，全部实测

这一节建议直接背表格。每一条我都跑过，type 栏是真实值，不是「据说」。

几个必须补充的细节：

1. 隐式转换是双向的。varchar 列传数字会失效； 反过来 int 列传字符串不会失效（MySQL 会把字符串转成数字）。 所以「where id = '123' 走索引，where name = 123 不走」—— 记一条就够：别让列的类型和参数类型不一致。
2. or 不一定失效。如果 or 两边都有索引， 优化器会用 index_merge 合并两个索引的结果。只有一边没有时才会退化成全扫。
3. != / not in / is not null 通常不走索引， 但不是因为它们「失效」，而是因为命中行数太多，走索引反而更慢—— 优化器算出「全表扫更便宜」就放弃了。区分「写错了」和「不划算」是这一节的重点。
4. 字符集不一致也会失效。两张表 join 时一个 utf8 一个 utf8mb4， 连接字段会做隐式转换，索引直接失效。这类问题在线上很常见，但看 EXPLAIN 才发现。

11.7深分页：为什么 limit 越翻越慢

业务里最经典的慢查询：后台列表翻到第 1 万页。测一下。

select * from t_user order by id limit 99990, 10;
-- type: index | key: PRIMARY | key_len: 4 | rows: 99678 | Extra: None

type=index 说明走的是全索引扫描，rows=99678 说明扫了将近 10 万行—— 虽然只要最后 10 条，但前面 99990 条也得一条条读出来再丢掉。

优化一：延迟关联

select u.* from t_user u
join (select id from t_user order by id limit 99990, 10) t
  on u.id = t.id;

完整执行计划（三行，注意每行在干什么）：

table=<derived2>  type=ALL     key=None     rows=99789  Extra=None
table=u           type=eq_ref  key=PRIMARY  rows=1      Extra=None
table=t_user      type=index   key=PRIMARY  rows=99789  Extra=Using index

关键在于第三行的 Extra: Using index：子查询在索引上扫 10 万行时，不需要回表 （它只要 id，而聚簇索引的键就是 id）； 第二行 eq_ref 说明外层只回表了 10 次（一次一行）。

对比一下：原本是「扫 10 万行 + 回表 10 万次」，现在是「扫 10 万行 + 回表 10 次」。 差别不在扫描量，在回表次数。

优化二：游标（keyset pagination）——更好，但要接口支持

-- 记住上一页最后一条的 id，下次直接从这里开始
select * from t_user where id > 99990 order by id limit 10;
-- type: range | key: PRIMARY | rows=10     ← 只扫 10 行

这才是真正把 rows 从 10 万降到 10 的办法。代价是不能跳页—— 你没法直接跳到第 5000 页，只能一页页往下翻。所以它适合「无限滚动」、「导出」、「消息流」， 不适合「页码导航」。

11.8本章速记卡与自测

这一章的答案里，凡是带数字的都来自实测。面试时能报出「10 万行 481 个页、208 行一页」， 可信度远高于「B+ 树很快」。