一次讲透数据库与 Redis 实战：B+ 树索引、EXPLAIN、深度分页、分布式锁与秒杀五层

本文是《一次讲透 XSS 与 CSRF》《一次讲透 JWT 与双 Token 机制》《一次讲透 Redis》《一次讲透 React 渲染与性能优化》《一次讲透 NestJS 与 PostgreSQL》的续篇。上一篇把 JOIN 家族和事务 ACID 铺完了，这一篇继续往深处打：索引在 B+ 树里长什么样、回表和覆盖索引是什么、EXPLAIN 四个字段怎么读、深度分页怎么治、窗口函数怎么写；后半篇切到 Redis 专题：持久化三件套、分布式锁的四步进化主线、秒杀五层方案。全文按"能口述出来"的标准整理，每节末尾附我自己的背诵版总结。

Part 1 数据库硬仗（D1）

0. 三块地基：连接方式、索引、事务 ACID

连接方式：

连接	语义
内连接 INNER JOIN	只返回两表都匹配的行（交集）
外连接 OUTER JOIN	保留会匹配的行，没有匹配的补 NULL
左连接 LEFT JOIN	左表全部保留
右连接 RIGHT JOIN	右表全部保留
全连接 FULL JOIN	两表全部保留

连接方式不影响性能。

索引：用空间换时间，但是占用额外存储并且会拖慢写入。

事务 ACID：

原子性：要么全做要么全不做
一致性：合法状态到合法状态
隔离性：并发事务互不干扰
持久性：提交即永久

面试四连

Q1：内连接和外连接的区别一句话？"外连接"和"左连接"是什么关系？

内连接是只返回两表都匹配的行，左连接是外连接的一种。外连接以某张表为主表，没匹配上的补 NULL，左连接是外连接的三种之一。

Q2：场景选型：查"所有用户及他们的文件数，没传过文件的显示 0"——用哪种 JOIN？为什么 INNER 不行？

没匹配的行 COUNT(f.id) 数出来的自然是 0，COUNT 不数 NULL，所以用 LEFT JOIN + COUNT 自带这个效果。

Q3：为什么索引不是建得越多越好？（说出代价的两条 + 一个失效场景）

索引可以增加查询的速度，但是占用额外的存储并且拖慢写入。失效场景：对索引列做函数运算、LIKE 前通配 LIKE '%文件'、联合索引不满足最左前缀。

Q4：用"下单：扣库存 + 创建订单"场景，说出 ACID 四个特性分别保证什么。

原子：扣库存 + 建订单绑定成一个整体，全部成功或者全部回滚

一致：库存 + 已售 = 总量这条不变式事务前后都成立

隔离：两单同时抢最后一件库存，不会都成功超卖

持久：提交后宕机重启，数据还在

1. JOIN 五种连接

JOIN 就是把两张分开的表，按照一个共同的字段粘回一张宽表。内连接取交集，两边都匹配时才留；左连接以左边为主表全部保留，右表匹配不上补 NULL；右连接反之，全连接取并集。性能上内连接最快因为结果集最小，但真正决定性能的是索引和驱动表。另外 ON 是连接前过滤，放 WHERE 会把 NULL 行筛掉，退化成内连接。

2. 索引和最左前缀（B+ 树）

主键索引：叶子节点存整行数据，表本身就是按主键组织的一颗 B+ 树
二级索引：叶子节点只存索引列的值 + 主键
回表：走二级索引只拿到主键，还要再回到主键索引的树查一次才能拿到整行，多一次树查找
覆盖索引：要查的列全部在索引里，免回表（EXPLAIN 的 Extra 显示 Using index）

2.1 最左前缀原则

联合索引 (a, b, c) 的本质是：先把全表按 a 排序，a 相同再按 b 排，b 相同再按 c 排。所以它天然相当于建了 (a)、(a,b)、(a,b,c)，但是哪一套都不能跳过 a。

3. 索引失效的五大场景

索引列上用函数/运算：WHERE YEAR(create_time) = 2023，索引存的是原始值，套函数之后没办法按序定位。改写成 WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'
隐式类型转换：phone 是 varchar 却写 number
LIKE 前置通配：LIKE '%三'，不知道开头，索引白排
OR 两侧有一个列没有索引：整个走不了索引
不满足最左前缀

4. 口述：为什么 MySQL 用 B+ 树不用哈希表做索引

索引是 B+ 树实现的目录，矮胖，3-4 层就能放千万数据，叶子节点存数据并用双向链表串起来，所以等值、范围、排序都能走索引，这是它比哈希表强的一个优势。

哈希表是把键打散存储、无序，等值查询 O(1) 很快，但是范围查询和 GROUP BY 完全做不了。

5. 思考题：联合索引 idx(name, age)

SELECT * FROM users WHERE age=20 能用上索引吗？——不能用
WHERE name LIKE '张%' AND age=20 能用上几列？——两列都可以用上
顺便说一句：WHERE age=20 AND name='张三' 呢？——能用上索引

总结

索引是 B+ 树实现的目录，矮胖，3-4 层就能放千万数据，叶子节点存数据并用双向链表串起来，所以等值、范围、排序都能走索引，这是它比哈希表快的一个优势。

InnoDB 里主键索引叶子存整行，普通索引叶子只存主键，所以走普通索引拿整行要"回表"；如果 SELECT 的列都在索引里就是覆盖索引，免回表。联合索引遵循最左前缀，因为整表是按照第一列排序的，范围查询后面的列会失效。失效的场景主要是：列上套函数、隐式类型转换、前置通配 LIKE、OR 混入无索引列。

6. EXPLAIN 四字段

EXPLAIN 是让 MySQL 自己交代这条 SQL 打算怎么执行的工具。

字段	含义
type	访问方式
key	实际用了哪个索引
rows	预估扫描行数
Extra	两个好词两个坏词

6.1 三道真题

口述：EXPLAIN 主要看哪四个字段？type=ALL 和 Using filesort 分别说明什么问题？

EXPLAIN 主要要看 type 访问方式、key 实际用了哪个索引、rows 预估扫描行数、Extra 两个好词两个坏词。type=ALL 说明没索引或者索引失效，Using filesort 说明 ORDER BY 没走上索引。拿到结果就对症下药：该加索引就加索引，索引失效就改写 SQL。

判断题：一条 SQL 的 Extra 显示 Using index，同时 type 是 index——这个 SQL 一定慢吗？为什么？（提示：想想覆盖索引扫整棵索引树是什么场景）

不一定慢，要看它替代的方案是什么。type=index 确实是扫一整棵索引树（没有精确定位起点，逐条走），单看不是好事；但是配合 Using index 覆盖索引，扫的是只含索引列 + 主键的瘦树，不用回表。对比 type=ALL 扫整张"胖表"还要回表，这是明显的好姿势，典型场景是聚合统计。

场景题：SELECT * FROM orders WHERE user_id=5 ORDER BY create_time DESC LIMIT 20，EXPLAIN 显示 type=ALL, Using filesort——你会怎么治？（提示：建什么索引？顺序有讲究吗？）

用 user_id 和 create_time 建联合索引 idx_user_create_time。type=ALL 说明没走上索引，Using filesort 说明 ORDER BY 不在索引里面，所以用 user_id 和 create_time 建联合索引。如果反过来就违反了最左前缀，走不了索引——等值列放在最左边，排序列放第二。

6.2 总结

EXPLAIN 是看执行计划的工具，重点看四个字段：type 访问方式，从 const、eq_ref、ref、range、index 到 ALL，ALL 是全表扫描必须消灭，至少要 range；key 是实际用到的索引，key_len 还能看出联合索引用上了几列；rows 是预估扫描行数；Extra 里 Using index 是覆盖索引免回表，是好事，出现 Using filesort 或者 Using temporary 要警惕，说明排序或者分组没走上索引。拿到结果就对症下药，该加索引加索引，索引失效就改写 SQL。

7. 深度分页

LIMIT 1000000, 20 慢的本质：数据库不会"跳过"前 100 万行，他是逐行读完，数够一百万行，然后扔掉，再给你 20 行。

优化方式三选：

游标分页 keyset
延迟关联（支持跳页的最优解）
业务限制

7.1 三道真题

口述：LIMIT 1000000, 20 到底慢在哪？说出"读了多少行、（走二级索引时）回表多少次"。

慢就慢在读了 1000020 行，扔掉了一百万行，越往后越慢，第一页和第五万页成本天差地别。如果走的是二级索引，回表 1000020 次，每次一个随机 IO。

用树的语言解释：为什么游标分页翻到第 5 万页和第 1 页一样快？

id > 1000020 是等值/范围定位，主键树从根二分下钻 3-4 层直接落在 1000020 之后，读 20 行就停。永远是"定位 + 读 20 行"，和翻到第几页无关。

延迟关联为什么能把回表从 100 万次降到 20 次？（提示：子查询 SELECT 的是什么列、走的是什么索引）

因为子查询 SELECT id，二级索引叶子必带主键，id 天然就躺在索引里面，是覆盖索引。瘦树上只拿 id，0 次回表地扫过 100 万行；外层只对最终的 20 个 id 回表，回表次数从一百万次降到 20 次。

7.2 总结

深度分页慢是因为 LIMIT 的 offset 不是跳过而是逐行读完再丢弃，走二级索引还要回表上百万。三个优化：一是游标分页，记住上一页的最后 id 用 WHERE id > 定位，主键树直接下钻，每一页都是读 20 行，翻到哪都一样，适合下拉加载，我实习的医嘱查询就是这种场景；二是延迟关联，子查询在覆盖索引上只拿 id 不回表，最后只对 20 行回表，回表次数从百万降低到 20；三是业务上限制跳页，只提供上一页和下一页。

8. 窗口函数（PG 特色）

窗口函数 = 不合并行的 GROUP BY。分组做统计，但是每一行的明细都保留，统计结果像多出来的一列贴在每行旁边。

语法：

函数(...) OVER(PARTITION BY 分组列 ORDER BY 排序列)

OVER(...)：开窗开关——函数从"普通聚合"变成"对窗口算，结果贴回每一行"
PARTITION BY dept：按照什么分区（GROUP BY 的分组）
ORDER BY salary DESC：区内按什么排序

8.1 三道真题

口述：窗口函数和 GROUP BY 的本质区别是什么？

窗口函数是不合并行的 GROUP BY。分组做统计，但是每一行的明细都保留，统计结果像多出来的一列贴在每行旁边。

成绩 90、90、85，ROW_NUMBER() / RANK() / DENSE_RANK() 各排出什么名次？

函数	名次	一句话
ROW_NUMBER()	1 2 3	并列也硬排
RANK()	1 1 3	并列占位，跳号
DENSE_RANK()	1 1 2	并列不跳号

实战题："查询每个用户最新的一笔订单"，用窗口函数怎么说思路？（不用写全 SQL，说出关键两步）

先按照用户进行分组，然后按照订单时间进行排序，取第一行：

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY create_time DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

8.2 总结

窗口函数是不合并行的分组统计：GROUP BY 会把多行压成一行，窗口函数就是每一行都保留，统计结果作为新列贴回每行。语法是函数加 OVER，PARTITION BY 分区，ORDER BY 区内排序。排名类常用 ROW_NUMBER() / RANK() / DENSE_RANK()，区别是并列的时候硬排、跳号、不跳号。典型场景是每组 Top N：先 ROW_NUMBER() 打排名，外层 WHERE rn <= N 筛选。PG 这边还有 MVCC 读写不阻塞、JSONB 配 GIN 索引存半结构化数据、CTE 递归查树形结构这些特色。

Part 2 Redis 专题（D2）

9. 持久化：RDB / AOF / 混合

因为 Redis 一般用于存储临时数据，那 Redis 持久化就是给"活在内存里的数据"上保险：内存断电就清零，持久化把数据抄到硬盘上，重启后照抄本恢复
持久化数据必须写进磁盘，Redis 的卖点是快，所以 RDB、AOF 就是在数据安全和性能之间找到平衡点

不持久化的 Redis 重启后分布式锁全丢，所有的锁会瞬间失效，并发请求全涌进临界区。缓存丢了是变慢，锁丢了是出错。

9.1 追问五连

RDB vs AOF 怎么选？ 缓存场景 RDB 够用甚至可关；存重要数据 AOF everysec + 混合持久化
快照时 Redis 卡不卡？ fork + COW，不阻塞写；但数据集特别大时 fork 本身有瞬时停顿（这也是追问深水区，点到即可）
AOF 文件太大怎么办？ 自动重写（auto-aof-rewrite-percentage），子进程按当前内存生成最简命令集
最多丢多少数据？ everysec 丢 1 秒；RDB 丢两次快照之间的全部
重启用哪个恢复？ AOF 优先，它更完整

9.2 总结

持久化三种方式：RDB、AOF 和混合持久化。RDB 是定期给整本书进行拍照——照片小，翻看快，但两次拍照之间写的内容会丢；AOF 是每写一句就记一笔流水账，账很全最多丢一秒，但是账本厚，恢复很慢，所以要定期重写。之后的混合持久化就是先拍一张当前照片，之后的新内容记流水：照片负责恢复快，流水负责丢得少。

10. 分布式锁：setnx

分布式锁就是多台服务器抢同一个坑位的牌子。用 Redis 这个所有机器都能看见的第三方牌子，同一时刻只允许一个实例进门干活。

代码实例（四步进化的完整形态）：

/** 引入 ioredis 客户端 连接 redis **/
const Redis = require('ioredis');
const redis = new Redis();
const { randomUUID } = require('crypto'); // 防止误删别人的锁

/** 一条原子命令加锁 **/
const token = randomUUID();
// 加锁必须一条命令同时完成【设置值 + 设置过期时间 + 不存在才创建】
const ok = await redis.set('lock:order:1001', token, 'EX', 10, 'NX');
if (!ok) return;

try {
  // 业务逻辑
} finally {
  // 释放锁：Lua 原子解锁（GET 验证身份 + DEL 合成一步）
  const unlock = `
    if redis.call('GET', KEYS[1]) == ARGV[1] then
      return redis.call('DEL', KEYS[1])
    else
      return 0
    end`;
  await redis.eval(unlock, 1, 'lock:order:1001', token);
}

短板三条：

业务自动过期，业务还没跑完——锁续期，使用看门狗思路
单机 Redis 单点故障——Redlock 解决，独立 Redis
主从锁丢失——主节点加锁成功还没同步就挂了

看门狗续期：

const dog = setInterval(async () => {
  await redis.eval(`
    if redis.call('GET', KEYS[1]) == ARGV[1] then
      return redis.call('EXPIRE', KEYS[1], 10)
    end`, 1, 'lock:order:1001', token);
}, 3000);

10.1 追问四连

为什么不能用 SETNX + EXPIRE 两条命令？ 非原子，崩在中间永久死锁
为什么 value 要用 UUID？Lua 为什么必须？ 防误删他人锁；GET 和 DEL 两步本身不原子
主从切换时锁会丢吗？ 会（锁还没同步到从库，主库挂了）→ 引出 Redlock 多数派方案，但性能差、争议大，实践更多是"容忍小概率 + 业务幂等兜底"
和秒杀什么关系？ 妙答：秒杀主力不是锁，是 DECR 原子扣减（无锁化）；锁用在低频强一致场景。这个衔接正好通向下一节

10.2 总结：四步进化主线

原子加锁 → 带身份 → 原子解锁 → 自动续期

原子加锁：一条 SET key uuid EX 10 NX 命令，占坑 + 过期一条命令防死锁。value 用 UUID 标识身份，防止误删他人的锁。业务执行期间看门狗定时续期，防锁过期活没干完。业务完毕之后先停掉看门狗，再使用 Lua 脚本原子解锁：GET 验证身份 + DEL 合成一步。

11. 秒杀五层方案

秒杀的本质就是把不能成交的请求先筛出来，然后只执行能成交的请求。

五层设计拆解：

前端让按钮置灰、设计答题验证码——源头削减
网关上设置令牌桶/漏桶限流、设计黑名单——准入控制
使用 Redis 原子预扣除 + Lua——无锁化
MQ 预扣成功才发送消息，异步来建订单——生产者-消费者削峰
乐观锁 + 唯一索引——兜底

总纲：Redis 挡量，MQ 削峰，DB 乐观锁兜底。

demo：

// 1. redis 预扣：lua 把"查 + 扣"合成原子操作
const lua = `
  local stock = tonumber(redis.call('GET', KEYS[1]) or '0')
  if stock <= 0 then return -1 end
  return redis.call('DECR', KEYS[1])`;
const left = await redis.eval(lua, 1, 'stock:1001');
if (left < 0) return { msg: '已抢完' };

// 2. DB 兜底：乐观锁，改完看影响行数
const [res] = await db.query(sql, [1001]);
if (res.affectedRows === 0) throw new Error('库存不足/重复下单');

11.1 追问四连

为什么不用悲观锁？ 不用排队，并且能够快速失败
一人重复下单怎么防？ 增加一个唯一索引，数据库层硬保证
Redis 扣了、MQ 消息丢了怎么办？ 使用重试机制 + 定时对账（比对 Redis 扣减量和 DB 的订单量）加上补偿，本质是最终一致性
Redis 挂了怎么办？ 降级，收紧网关限流 + 排队页面

11.2 晚间口述（就用这个顺序）

前端挡无效 → 网关限流 → Redis 原子预扣 → MQ 削峰 → DB 乐观锁兜底，五层每层一句话 + 总纲那句背出来。

前端使用按钮置灰、答题验证码，源头上削减无效点击。网关用令牌桶限流加黑名单进行准入控制。Redis 层用 DECR 进行原子抵扣，配合 Lua 把查和扣合成原子，扣成负数当场拒绝。预扣成功之后才发消息给 MQ，异步创建订单。DB 层使用乐观锁和唯一索引进行兜底，防止一人重复下单。总的就是：Redis 挡量，MQ 削峰，DB 乐观兜底。

12. 总结记忆卡

JOIN：内连接取交集，左/右/全连接没匹配补 NULL；连接方式不影响性能，真正决定性能的是索引和驱动表；ON 是连接前过滤，放 WHERE 退化成内连接
数人头场景：LEFT JOIN + COUNT(f.id)，COUNT 不数 NULL，没匹配的天然是 0
索引 = B+ 树目录：矮胖，3-4 层放千万数据，叶子节点双向链表串起来，等值/范围/排序都能走，这是它比哈希表强的地方
回表：二级索引叶子只存主键，拿整行要回主键树再查一次；覆盖索引：SELECT 的列全在索引里，免回表（Extra 显示 Using index）
最左前缀：联合索引 (a,b,c) 天然相当于 (a)、(a,b)、(a,b,c)，不能跳过 a；范围查询后面的列失效
索引失效五场景：列上套函数/运算、隐式类型转换、前置通配 LIKE、OR 混入无索引列、不满足最左前缀
EXPLAIN 四字段：type / key / rows / Extra；type=ALL 全表扫描必须消灭，至少 range；Using index 是好事，Using filesort / Using temporary 要警惕
深度分页：offset 不是跳过是逐行读完再丢弃，走二级索引回表上百万；治法三选——游标分页（定位 + 读 20 行）、延迟关联（覆盖索引拿 id，回表降到 20 次）、业务限制跳页
窗口函数：不合并行的 GROUP BY；ROW_NUMBER 硬排 / RANK 跳号 / DENSE_RANK 不跳号；每组 Top N = 打排名 + 外层 WHERE rn <= N
持久化：RDB 拍照小而快、两次之间全丢；AOF 流水账全、厚而慢要重写；混合 = 先拍照再记流水，照片负责恢复快、流水负责丢得少
缓存 vs 锁丢数据的区别：缓存丢了是变慢，锁丢了是出错
分布式锁四步进化：原子加锁（SET NX EX 一条命令）→ 带身份（UUID）→ 原子解锁（Lua：GET 验证 + DEL）→ 自动续期（看门狗）
秒杀五层：前端挡无效 → 网关限流 → Redis 原子预扣 → MQ 削峰 → DB 乐观锁兜底；总纲：Redis 挡量、MQ 削峰、DB 乐观兜底

本文基于个人面试复盘与背诵笔记整理（D1 数据库硬仗 + D2 Redis 专题），如有疏漏欢迎指出。系列前篇：《一次讲透 XSS 与 CSRF》《一次讲透 JWT 与双 Token 机制》《一次讲透 Redis：为什么快、缓存三兄弟、从单机到集群》《一次讲透 React 渲染与性能优化》《一次讲透 NestJS 与 PostgreSQL：IoC、DTO 两道防线、JOIN 家族、索引与事务》。

一次讲透数据库与 Redis 实战

评论 (0)