后端MySQL 与 Redis 前端速成

第二章:MySQL 索引——给数据库装上“导航系统”

为什么 B+ 树快?什么是回表?联合索引为什么要遵守最左前缀?

MySQL 与 Redis 前端速成2 / 11

后端
第二章:MySQL 索引——给数据库装上“导航系统”文章封面

当前1. 为什么不用普通数组?

这一章很关键。

很多人学 MySQL,只会背:

索引能提高查询速度,底层是 B+ 树。

面试官接着问一句:

为什么 B+ 树快?什么是回表?联合索引为什么要遵守最左前缀?

当场就容易进入“CPU 过热,语言系统停止响应”的状态。

今天我们不死记硬背。你先把自己想象成 MySQL 查询优化器,任务是从一座存放了 1000 万份订单的仓库里,找到刘凤伟的某一笔订单。

你的简历里有万福鉴酒订单状态机、生猪智慧医药系统和 TripStar AI,这些项目都非常适合被追问数据库索引设计。


第一关:没有索引会发生什么?

假设订单表有 1000 万条数据:

SELECT *
FROM orders
WHERE order_no = 'ORDER20260730001';

如果 order_no 没有索引,MySQL 可能只能这样干:

第 1 条:不是
第 2 条:不是
第 3 条:不是
……
第 8,739,521 条:找到了!

这叫:

全表扫描,Full Table Scan。

就像你去图书馆找《JavaScript 高级程序设计》,管理员告诉你:

没有检索系统,你自己从第一排开始翻吧。

数据只有几十条时无所谓,但数据达到几十万、几百万条,全表扫描就会明显变慢。

于是我们给 order_no 建索引:

CREATE INDEX idx_order_no
ON orders(order_no);

以后 MySQL 不再一行一行找,而是根据索引快速定位。


第二关:索引到底是什么?

索引本质上是一种:

以额外存储空间换取查询速度的数据结构。

和书籍目录很像。

一本 1000 页的书,如果没有目录,你想找“闭包”,只能从第一页开始翻。

有目录以后:

闭包…………第 236 页
原型链………第 312 页
事件循环……第 489 页

索引本身不是原始数据,而是一个帮助你定位原始数据的结构。

所以索引有两个特点:

  1. 查询通常会变快。
  2. 索引也需要占用空间,增删改数据时还要同步维护索引。

因此并不是索引越多越好。


第三关:为什么 MySQL 索引使用 B+ 树?

先别被“B+ 树”三个字吓到。

你不需要一天之内手写 B+ 树,只需要理解它为什么适合数据库。

1. 为什么不用普通数组?

假设订单号已经排好序,可以通过二分查找快速搜索。

但数组插入数据可能需要移动大量元素:

[1, 2, 3, 5, 6]

插入 4:

[1, 2, 3, 4, 5, 6]

数据库每天都在新增、修改数据,普通连续数组不够灵活。


2. 为什么不用普通二叉树?

普通二叉树理想情况下是:

        8
      /   \
     4     12
    / \   /  \
   2   6 10  14

但如果数据按顺序插入:

1 → 2 → 3 → 4 → 5

它可能退化成链表:

1
 \
  2
   \
    3
     \
      4

查询效率会明显下降。

即使使用平衡二叉树,每个节点通常只有两个分支,数据量一大,树会比较高。

数据库从磁盘读取数据时,树越高,需要的磁盘访问次数通常越多。


3. B+ 树厉害在哪里?

B+ 树可以理解成一棵“特别矮、特别胖”的树。

普通二叉树一个节点最多两个孩子:

左孩子 + 右孩子

B+ 树的一个节点可以存放大量索引项,对应很多个孩子:

一个节点
├── 区间1
├── 区间2
├── 区间3
├── 区间4
├── ……
└── 区间100

它的特点是:

树很矮

因为每层能容纳大量索引项,所以即使有几百万条数据,树的高度通常也不会特别夸张。

查询时需要访问的节点更少。

非叶子节点主要保存索引

中间节点主要负责导航,不保存完整业务数据,因此同一页可以存放更多索引项。

数据集中在叶子节点

真正的数据或者数据定位信息主要位于叶子节点。

叶子节点之间有序连接

可以想象成:

叶子1 ⇄ 叶子2 ⇄ 叶子3 ⇄ 叶子4

因此 B+ 树不仅适合单值查询,还特别适合:

WHERE amount BETWEEN 100 AND 500

以及:

ORDER BY created_at

这类范围查询和有序扫描。


面试标准答案:为什么使用 B+ 树?

InnoDB 索引主要使用 B+ 树。B+ 树的分支数量多、树高较低,查询时需要的磁盘 IO 次数较少;非叶子节点主要保存索引信息,因此单个节点能够容纳更多键值;数据集中在叶子节点,并且叶子节点按照顺序连接,所以既适合等值查询,也适合范围查询和排序。

这个答案已经能应对大部分前端实习面试。


第四关:聚簇索引是什么?

这是 MySQL 索引里最值得理解的知识点。

以 InnoDB 为例,主键索引通常是:

聚簇索引。

假设表结构为:

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

主键索引大致可以想象成:

B+ 树

          [10 | 20]
          /   |   \
        ...  ...  ...

叶子节点:
[id=1, name=张三, age=20]
[id=2, name=李四, age=21]
[id=3, name=刘凤伟, age=22]

主键索引的叶子节点中保存的是:

完整的一行数据。

所以根据主键查询:

SELECT *
FROM users
WHERE id = 3;

找到主键索引的叶子节点后,数据就直接拿到了。

这就是聚簇索引。


为什么一张表通常只有一个聚簇索引?

因为表中数据只能按照一种主要方式组织。

就像一个班级名单:

  • 可以按照学号作为主要顺序;
  • 也可以按照姓名作为主要顺序;

但同一份原始名单不可能同时物理地按照两种顺序排列。


没有主键怎么办?

InnoDB 通常会按照类似下面的逻辑选择聚簇索引:

  1. 优先使用主键。
  2. 没有主键时,选择合适的非空唯一索引。
  3. 再没有,就生成隐藏的行标识。

面试中不需要把隐藏字段名字背得太深,知道 MySQL 最终需要一种方式组织行数据即可。


第五关:二级索引和回表

假设用户表:

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  city VARCHAR(50),
  INDEX idx_name(name)
);

id 是主键索引。

name 是普通索引,也叫:

  • 二级索引
  • 辅助索引
  • 非聚簇索引

二级索引的叶子节点通常不会保存完整行数据,而是保存:

索引字段值 + 主键值

例如:

name = 刘凤伟
id = 1001

现在执行:

SELECT *
FROM users
WHERE name = '刘凤伟';

MySQL 可能经历两步:

第一步:查询二级索引

在 idx_name 中找到:

name = 刘凤伟
主键 id = 1001

第二步:查询主键索引

再拿着 id = 1001 到主键索引中获取完整数据:

id、name、age、city……

这个过程叫:

回表。

可以把它想象成快递柜。

你先通过姓名目录找到:

刘凤伟 → 38号柜

然后再去 38 号柜取完整包裹。


面试标准答案:什么是回表?

InnoDB 二级索引的叶子节点通常保存索引字段和对应的主键值。如果查询的字段无法直接从二级索引获得,就需要先通过二级索引找到主键,再根据主键查询聚簇索引获取完整行数据,这个过程叫回表。


第六关:覆盖索引——不回表,直接拿走

还是这张表:

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  city VARCHAR(50),
  INDEX idx_name(name)
);

执行:

SELECT id, name
FROM users
WHERE name = '刘凤伟';

二级索引 idx_name 中已经有:

name + id

查询所需要的所有字段,都能从索引中直接得到,不需要再回主键索引查询。

这叫:

覆盖索引。

“覆盖”的意思就是:

索引已经覆盖了这条 SQL 所需要的全部字段。


为什么不建议无脑 SELECT *?

SELECT *
FROM users
WHERE name = '刘凤伟';

因为 * 还需要 age、city 等字段,二级索引里没有,就可能需要回表。

而:

SELECT id, name
FROM users
WHERE name = '刘凤伟';

有机会直接使用覆盖索引。

所以企业开发中建议:

用什么字段就查询什么字段,不要习惯性 SELECT *。

不过要注意:

SELECT * 不等于索引一定失效,而是更容易发生回表,数据量大时优化器也可能认为走索引不划算。


第七关:联合索引——索引界的组合技

你在万福鉴酒项目中,有一个非常典型的查询:

查询某个用户某种状态下的订单,并按创建时间倒序展示。

SQL 可能是:

SELECT id, order_no, status, created_at
FROM orders
WHERE user_id = 1001
  AND status = 1
ORDER BY created_at DESC;

可以建立联合索引:

CREATE INDEX idx_user_status_created
ON orders(user_id, status, created_at);

联合索引不是创建了三个完全独立的索引,而是创建了一个按照以下顺序排列的索引:

先按 user_id 排序
user_id 相同时,再按 status 排序
前两个都相同时,再按 created_at 排序

像通讯录:

先按省份
再按城市
再按区县

例如:

广东 → 广州 → 天河
广东 → 广州 → 越秀
广东 → 深圳 → 南山
四川 → 广安 → 邻水

第八关:最左前缀原则

联合索引:

(user_id, status, created_at)

可以理解为从最左边开始排列:

user_id → status → created_at

以下查询通常能够较好地使用它。

使用第一列

WHERE user_id = 1001

可以。

使用前两列

WHERE user_id = 1001
  AND status = 1

可以。

使用三列

WHERE user_id = 1001
  AND status = 1
  AND created_at > '2026-07-01'

可以。

跳过第一列

WHERE status = 1

通常无法按照这个联合索引的常规最左匹配方式高效定位。

为什么?

因为索引首先按照 user_id 排序。

它整体可能是:

user_id=1, status=0
user_id=1, status=1
user_id=2, status=0
user_id=2, status=1
user_id=3, status=0
user_id=3, status=1

只看 status 时,状态为 1 的数据分散在不同用户区域,无法直接找到一个连续范围。


一个有趣的问题

下面这条 SQL 能使用联合索引吗?

WHERE status = 1
  AND user_id = 1001

虽然 SQL 中 status 写在前面,但它仍然可能使用:

(user_id, status)

因为:

最左前缀强调的是索引字段的逻辑匹配,不是 SQL 条件的书写顺序。

MySQL 优化器可以重新分析条件。

所以这两条一般没有本质区别:

WHERE user_id = 1001 AND status = 1
WHERE status = 1 AND user_id = 1001

第九关:范围查询为什么可能影响后面的列?

联合索引:

(user_id, created_at, status)

查询:

WHERE user_id = 1001
  AND created_at > '2026-07-01'
  AND status = 1

MySQL 可以先根据:

user_id = 1001

缩小范围,再对 created_at 做范围查询。

但是进入 created_at > ... 的范围以后,后面的 status 往往无法继续作为精确定位区间的关键列。

你可以先记住这个面试规则:

联合索引从左向右匹配,遇到范围条件后,后续字段在索引定位能力上可能受到限制。

注意,“不能继续用于精确定位”不代表后面的字段完全没用。MySQL 还可能通过索引条件下推等能力减少回表,但一天速成阶段不需要钻得这么深。


第十关:常见索引失效场景

“索引失效”更准确的理解是:

MySQL 没有按照我们预期的方式使用索引,或者优化器认为全表扫描更便宜。

不要机械地认为某种写法一出现,索引就百分之百死亡。


1. 对索引字段使用函数

有索引:

INDEX idx_created_at(created_at)

不推荐:

WHERE DATE(created_at) = '2026-07-30'

因为数据库需要对大量 created_at 执行 DATE(),普通索引中的原始排序不容易直接被利用。

推荐改成范围:

WHERE created_at >= '2026-07-30 00:00:00'
  AND created_at <  '2026-07-31 00:00:00'

2. 模糊查询以 % 开头

有索引:

INDEX idx_name(name)

可以较好利用索引:

WHERE name LIKE '刘%'

难以利用普通 B+ 树进行快速定位:

WHERE name LIKE '%凤伟%'

因为第一个字符都不知道,数据库无法从索引的起点定位。

类比字典:

  • 查“刘开头的名字”很好找;
  • 查“名字中间包含凤伟”就得翻很多页。

对于复杂全文搜索,可能需要全文索引或 Elasticsearch 一类搜索方案。


3. 隐式类型转换

假设手机号字段是字符串:

phone VARCHAR(20)

推荐:

WHERE phone = '15118978572'

不推荐:

WHERE phone = 15118978572

字符串和数字比较时可能发生隐式类型转换,进而影响索引使用。

前后端联调时也要注意字段类型一致。


4. 联合索引跳过最左列

索引:

(user_id, status)

查询:

WHERE status = 1

通常无法充分使用这个联合索引。

如果状态查询特别高频,可能需要单独建立:

INDEX idx_status(status)

但还有一个问题:状态通常只有几个值,区分度较低,单独建索引未必划算。


5. 字段区分度太低

例如:

gender:男/女
status:0/1
deleted:0/1

一张表 100 万条记录,status=1 命中 80 万条。

即使有索引,数据库也可能觉得:

走索引还要回表 80 万次,不如直接扫描整张表。

因此:

建了索引不代表 MySQL 一定使用,优化器会估算成本。


6. OR 条件使用不当

例如:

WHERE user_id = 1001
   OR remark = '特殊订单'

user_id 有索引,但 remark 没有索引,可能导致查询计划不理想。

不过不能死记成“用了 OR 就索引失效”。如果两边都有合适索引,MySQL 也可能使用索引合并等策略。

面试时这样说更稳:

OR 条件可能影响索引选择,需要结合两侧字段的索引情况和 EXPLAIN 判断,不能简单说 OR 一定导致索引失效。

这句话比大量培训班答案靠谱。


第十一关:索引是不是越多越好?

不是。

每多一个索引,都要付出代价。

占用空间

索引本身也要保存数据。

降低写入速度

执行:

INSERT
UPDATE
DELETE

时,不仅要修改表数据,还要维护相关索引。

一张表如果有 15 个索引,插入一条数据,可能要同步更新多个索引结构。

可能干扰优化器

重复索引、冗余索引会增加维护成本,也可能让查询优化变得复杂。

标准回答:

索引能够提高查询速度,但会占用额外存储空间,并增加插入、更新和删除时的维护成本。因此应该围绕高频查询条件、关联字段和排序字段建立索引,而不是给所有字段都建索引。


第十二关:如何判断 SQL 有没有使用索引?

使用:

EXPLAIN
SELECT id, order_no, status
FROM orders
WHERE user_id = 1001
  AND status = 1;

你先重点看五个字段。

possible_keys

可能使用的索引。

key

实际选择的索引。

如果是:

NULL

通常表示没有使用索引。

type

访问方式。

常见类型大致从好到差:

const
eq_ref
ref
range
index
ALL

你现在重点认识:

  • const:通过主键或唯一索引查一条。
  • ref:通过普通索引进行等值查询。
  • range:索引范围查询。
  • index:扫描整个索引。
  • ALL:全表扫描。

看到 ALL 不代表一定出事故。

如果表只有几十条数据,全表扫描可能反而合理。

rows

MySQL 估计需要扫描的行数。

通常越少越好,但它只是估算值。

Extra

常见信息:

Using index

往往表示使用了覆盖索引。

Using where

表示还要根据条件进行过滤。

Using filesort

表示排序没有完全直接利用索引,需要额外排序。

Using temporary

可能使用临时表,复杂分组或排序场景需要关注。


第十三关:给你的项目设计索引

场景一:万福鉴酒订单列表

业务需求:

  • 查询当前用户订单;
  • 按订单状态筛选;
  • 按创建时间倒序;
  • 支持分页。

SQL:

SELECT
  id,
  order_no,
  status,
  amount,
  created_at
FROM orders
WHERE user_id = 1001
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

可以考虑:

CREATE INDEX idx_user_status_created
ON orders(user_id, status, created_at);

索引顺序为什么这样设计?

user_id:先定位当前用户
status:再筛选订单状态
created_at:最后辅助排序

面试回答:

我会根据真实查询条件设计 (user_id, status, created_at) 联合索引,因为订单列表通常先按用户和状态进行等值筛选,再按创建时间排序。这样可以减少扫描范围,并尽可能利用索引完成过滤和排序。


场景二:生猪智慧医药系统查询药品

业务需求:

SELECT id, name, category_id, price
FROM medicine
WHERE category_id = 10
ORDER BY created_at DESC;

可以考虑:

CREATE INDEX idx_category_created
ON medicine(category_id, created_at);

如果用户搜索:

WHERE name LIKE '阿莫%'

可以考虑给 name 建索引。

但如果产品要求:

WHERE name LIKE '%阿莫%'

普通 B+ 树索引帮助可能有限。

这时你可以说:

数据规模较小时可以接受模糊查询;数据规模扩大后,可以评估全文索引或接入专门搜索服务。

不要一上来就喊 Elasticsearch。十万条药品数据都没有,就架一套 ES,多少有点“为了切水果买了台挖掘机”。


场景三:TripStar AI 生成任务

任务表:

CREATE TABLE trip_tasks (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL
);

查询某用户最近任务:

SELECT id, status, created_at
FROM trip_tasks
WHERE user_id = 1001
ORDER BY created_at DESC
LIMIT 20;

可以建立:

CREATE INDEX idx_user_created
ON trip_tasks(user_id, created_at);

任务的高频进度可以放 Redis,但最终任务记录和旅行计划仍然落 MySQL。


第十四关:主键为什么经常使用自增 ID?

例如:

id BIGINT PRIMARY KEY AUTO_INCREMENT

一个重要原因是:

自增主键通常按顺序插入,比较符合 B+ 树叶子节点的排列方式,可以减少随机插入带来的页分裂和数据移动。

假设已有:

1, 2, 3, 4, 5

插入 6,直接放在后面。

如果主键是完全随机的字符串:

a91x...
2kx8...
zz3p...

每次插入位置都可能不同,维护索引的成本会更高。

但这并不代表所有系统都必须使用自增 ID。分布式系统可能会使用雪花 ID、UUID 等方案,只是要综合考虑:

  • 全局唯一性
  • 有序性
  • 存储空间
  • 安全暴露
  • 分布式生成能力

面试阶段不要绝对化。


第十五关:一套完整的索引优化思路

当面试官问:

一条 SQL 很慢,你怎么优化?

不要只回答“加索引”。

可以按照这套流程:

第一,确认 SQL 的查询条件、返回字段和数据量;第二,使用 EXPLAIN 查看是否发生全表扫描、实际使用了哪个索引以及估算扫描行数;第三,检查 WHERE、JOIN、ORDER BY 字段是否存在合适索引;第四,检查是否存在函数计算、前置模糊匹配、隐式类型转换和不合理联合索引;第五,减少 SELECT * 和不必要返回字段,尝试利用覆盖索引;第六,对于深分页、大范围查询和复杂关联,考虑改写 SQL 或调整分页方案;最后结合慢查询日志和真实执行效果验证,不能只看是否建立了索引。

这段非常值得背。


第十六关:索引知识地图

你现在把这一条链记住:

普通查询很慢
    ↓
建立 B+ 树索引
    ↓
主键索引叶子节点保存完整数据
    ↓
二级索引叶子节点保存索引值和主键
    ↓
查完整数据时可能回表
    ↓
查询字段都在索引里,就是覆盖索引
    ↓
多个字段组合,就是联合索引
    ↓
联合索引遵守最左前缀
    ↓
使用 EXPLAIN 检查实际执行计划

这条链通了,MySQL 索引就不再是一堆散乱名词。


本章面试背诵区

1. 什么是索引?

索引是一种以额外存储空间和写入维护成本换取查询效率的数据结构。InnoDB 中索引主要使用 B+ 树,适合等值查询、范围查询和排序。

2. 什么是聚簇索引?

聚簇索引的叶子节点保存完整行数据。InnoDB 通常使用主键索引作为聚簇索引,因此一张表通常只有一个聚簇索引。

3. 什么是回表?

通过二级索引查询时,如果所需字段不全部存在于索引中,需要先从二级索引获取主键值,再通过主键索引查询完整行数据,这个过程叫回表。

4. 什么是覆盖索引?

查询所需字段都能够直接从某个索引中获得,不需要再访问聚簇索引获取完整行数据,这种情况叫覆盖索引。

5. 什么是最左前缀原则?

联合索引按照字段从左到右组织,查询条件需要从联合索引最左侧字段开始连续匹配,才能充分利用索引。SQL 条件的书写顺序不重要,重要的是有没有匹配索引的最左列。


本章闯关题

先别往上翻答案,看看能不能直接说出来。

第 1 题

索引为:

INDEX idx_abc(a, b, c)

下面哪些查询可以匹配最左前缀?

① WHERE a = 1
② WHERE a = 1 AND b = 2
③ WHERE b = 2 AND c = 3
④ WHERE c = 3 AND a = 1

答案:

① 可以
② 可以
③ 通常不可以
④ 可以先使用 a,但无法直接跳过 b 完整利用 c

第 2 题

SELECT *
FROM users
WHERE name = '刘凤伟';

name 有普通索引,是否一定不需要回表?

答案:

不一定。普通索引一般只有 name 和主键,如果 SELECT * 还需要其他字段,就可能回表。


第 3 题

为什么:

LIKE '刘%'

可能使用索引,而:

LIKE '%刘%'

通常难以高效使用普通 B+ 树索引?

答案:

B+ 树按照字段前缀顺序排列。已知开头时可以定位连续范围,开头未知时无法确定查找起点。


第 4 题

建了索引以后,MySQL 为什么还可能全表扫描?

答案:

如果查询命中数据比例过高、表数据本身很少、回表成本太大,或者索引区分度太低,优化器可能认为全表扫描成本更低。


第 5 题:项目题

万福鉴酒要查询某用户“进行中”的订单,并按创建时间倒序。你会设计什么索引?

参考回答:

CREATE INDEX idx_user_status_created
ON orders(user_id, status, created_at);

理由:

用户和状态是等值筛选条件,创建时间用于排序,按照查询模式组成联合索引,可以减少扫描和额外排序成本。

下一章进入 MySQL 事务、ACID、隔离级别、MVCC、行锁、乐观锁,以及如何避免订单超卖和重复提交。这一章会更像破案:两个人同时抢最后一瓶酒,到底谁能成功下单。