从一条 SQL 的生命周期开始，把 MySQL 一次性想明白

这篇笔记沿着「一条 SQL 语句在 MySQL 里的完整旅程」这条主线，把架构、存储引擎、索引、事务、MVCC、锁、日志与性能优化全部串起来——每章都配可跑的实战 SQL（电商库 mall），结尾附 20 问速答。忘了没关系，跟着主线走一遍，知识点会自己「长」回脑子里。 适用版本：MySQL 8.0 · InnoDB · 10 章 · 40+ 段实战 SQL · 20 问速查

目录

一、架构全景：一条 SQL 的生命周期

回忆 MySQL 最高效的路径，不是把知识点一个个背回来，而是顺着**「一条 SQL 语句从客户端发出去，到拿到结果」的完整旅程**走一遍——每个组件在旅程中轮番登场，忘了哪个就补哪个。这一章先建立全景地图，后面九章都是对地图某一块的放大。

1.1 先看全景：四层结构

把 MySQL 想象成一栋四层楼：客户端在楼外，SQL 从楼顶进、数据在楼底存。

#mermaid-svg-7YIbNxS3lh4m5yJp{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-7YIbNxS3lh4m5yJp .error-icon{fill:#552222;}#mermaid-svg-7YIbNxS3lh4m5yJp .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-7YIbNxS3lh4m5yJp .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-7YIbNxS3lh4m5yJp .marker{fill:#333333;stroke:#333333;}#mermaid-svg-7YIbNxS3lh4m5yJp .marker.cross{stroke:#333333;}#mermaid-svg-7YIbNxS3lh4m5yJp svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-7YIbNxS3lh4m5yJp p{margin:0;}#mermaid-svg-7YIbNxS3lh4m5yJp .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp .cluster-label text{fill:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp .cluster-label span{color:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp .cluster-label span p{background-color:transparent;}#mermaid-svg-7YIbNxS3lh4m5yJp .label text,#mermaid-svg-7YIbNxS3lh4m5yJp span{fill:#333;color:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp .node rect,#mermaid-svg-7YIbNxS3lh4m5yJp .node circle,#mermaid-svg-7YIbNxS3lh4m5yJp .node ellipse,#mermaid-svg-7YIbNxS3lh4m5yJp .node polygon,#mermaid-svg-7YIbNxS3lh4m5yJp .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-7YIbNxS3lh4m5yJp .rough-node .label text,#mermaid-svg-7YIbNxS3lh4m5yJp .node .label text,#mermaid-svg-7YIbNxS3lh4m5yJp .image-shape .label,#mermaid-svg-7YIbNxS3lh4m5yJp .icon-shape .label{text-anchor:middle;}#mermaid-svg-7YIbNxS3lh4m5yJp .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-7YIbNxS3lh4m5yJp .rough-node .label,#mermaid-svg-7YIbNxS3lh4m5yJp .node .label,#mermaid-svg-7YIbNxS3lh4m5yJp .image-shape .label,#mermaid-svg-7YIbNxS3lh4m5yJp .icon-shape .label{text-align:center;}#mermaid-svg-7YIbNxS3lh4m5yJp .node.clickable{cursor:pointer;}#mermaid-svg-7YIbNxS3lh4m5yJp .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-7YIbNxS3lh4m5yJp .arrowheadPath{fill:#333333;}#mermaid-svg-7YIbNxS3lh4m5yJp .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-7YIbNxS3lh4m5yJp .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-7YIbNxS3lh4m5yJp .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-7YIbNxS3lh4m5yJp .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-7YIbNxS3lh4m5yJp .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-7YIbNxS3lh4m5yJp .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-7YIbNxS3lh4m5yJp .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-7YIbNxS3lh4m5yJp .cluster text{fill:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp .cluster span{color:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-7YIbNxS3lh4m5yJp .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-7YIbNxS3lh4m5yJp rect.text{fill:none;stroke-width:0;}#mermaid-svg-7YIbNxS3lh4m5yJp .icon-shape,#mermaid-svg-7YIbNxS3lh4m5yJp .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-7YIbNxS3lh4m5yJp .icon-shape p,#mermaid-svg-7YIbNxS3lh4m5yJp .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-7YIbNxS3lh4m5yJp .icon-shape .label rect,#mermaid-svg-7YIbNxS3lh4m5yJp .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-7YIbNxS3lh4m5yJp .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-7YIbNxS3lh4m5yJp .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-7YIbNxS3lh4m5yJp :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

文件系统层

存储引擎层（InnoDB）

MySQL 服务层（Server Layer）

SQL 语句

仅更新语句，提交时写

客户端Navicat · JDBC · 程序

连接器连接管理 · 权限校验

分析器词法 · 语法解析

优化器选索引 · 定执行计划

执行器调用引擎接口

Buffer Pool内存数据页

事务 · 锁 · MVCC

数据文件.ibd 表空间

redo log

undo log

binlog

• 连接层：管理 TCP 连接、账号密码认证、线程复用。客户端真正连的是它。
• 服务层：包含连接器、分析器、优化器、执行器，以及内置函数、存储过程、触发器等跨引擎能力。核心 SQL 逻辑都在这层。
• 存储引擎层：插件式设计，真正负责数据的存与取。InnoDB 还独占管理事务、锁、MVCC 和 redo/undo 日志。
• 文件系统层：数据文件（.ibd）和三类日志的最终归宿，落盘的地方。

记忆锚点：服务层管「怎么执行 SQL」，引擎层管「数据怎么存取」。binlog 属于服务层，redo/undo 属于 InnoDB——这个归属关系后面讲两阶段提交时会反复用到。

1.2 一条 SELECT 的五步旅程

以最常见的一句话为例：SELECT * FROM users WHERE id = 1;

记忆锚点：分析器管「这句话对不对」，优化器管「怎么执行最快」，执行器管「真去干活」，存储引擎管「数据怎么存取」。四连问，面试和排障都靠它定位问题层。

1.3 一条 UPDATE 多了什么（伏笔）

换成 UPDATE users SET status = 0 WHERE id = 1;，前四步完全一样，但进入 InnoDB 后多出一整套动作：

这套「先写日志、后刷数据页」的设计叫 WAL（Write-Ahead Logging），是 MySQL 崩溃后能自动恢复的全部底气。

1.4 连接管理实战

连接建立有真实开销：TCP 握手、身份认证、读权限表。高并发短连接场景下这些开销会被放大，所以应用侧永远用连接池（FastAPI 侧如 SQLAlchemy 的 pool_size、pool_recycle）。两个经典问题先记下来：

• 空闲连接超时：由 wait_timeout（默认 8 小时）控制，超时后服务端主动断开，客户端再发 SQL 就会收到 MySQL server has gone away。连接池的 pool_recycle 要设得比它小。
• 长连接内存上涨：5.7 及以前版本中，连接内执行过的大的临时结果不会立即释放，长连接越久内存越高。对策：连接池定期回收重建连接，或执行完大任务后调用 mysql_reset_connection()。

实战演练：三条命令看清当前连接

SHOW PROCESSLIST; — 谁连着、正在干嘛
SHOW STATUS LIKE 'Threads%'; — 连接数概况
SHOW VARIABLES LIKE 'wait_timeout'; — 空闲多久会被断开

mysql> SHOW PROCESSLIST;
+—-+——+———————+——+———+——+————+————————–+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———————+——+———+——+————+————————–+
| 8 | root | 192.168.1.10:52331 | mall | Query | 0 | starting | SHOW PROCESSLIST |
| 12 | app | 192.168.1.20:60012 | mall | Sleep | 120 | | NULL |
| 15 | app | 192.168.1.20:60018 | mall | Query | 12 | sending da | SELECT * FROM orders … |
+—-+——+———————+——+———+——+————+————————–+
— Command=Query 且 Time 很大：大概率是慢 SQL 或被锁阻塞；确认后 KILL 15; 掐掉它

二、存储引擎与数据类型

这一章回答两个问题：表用什么引擎建？每个字段选什么类型？——这两件事在建表那一刻就决定了这张表未来五年的性能上限。

2.1 InnoDB 与 MyISAM：一场已经结束的比赛

MySQL 5.5 之前默认引擎是 MyISAM，5.5 起换成 InnoDB，8.0 连系统表也全部改用 InnoDB。面试还爱问这个对比，因为对比的每一行都是考点：

实战演练：查看引擎

SHOW ENGINES; — 查看支持的引擎清单
SHOW TABLE STATUS FROM mall WHERE Name = 'orders'; — 看某张表用的引擎
— 8.0 里查到的 Engine 列应为 InnoDB；想迁移老表：
ALTER TABLE old_table ENGINE = InnoDB;

2.2 数值类型：整数与实数

• INT(11) 里的 11 只是「显示宽度」，不限制取值范围，8.0.17 起官方已废弃这个写法，直接写 INT 即可。
• 状态字段用 TINYINT UNSIGNED 并在 COMMENT 里写清枚举含义，别用 ENUM（见 2.3）。
• 主键统一 BIGINT UNSIGNED：INT 的 21 亿在数据量大的业务里真会用完，且未来接雪花 ID、合并表都方便。

实数只有一条军规：金额必须用 DECIMAL。FLOAT/DOUBLE 是二进制浮点数，天生无法精确表示 0.1 这样的十进制小数：

SELECT 0.1 + 0.2 = 0.3; — 0（不相等！浮点精度丢失）
SELECT 0.1 + 0.2; — 0.30000000000000004

— 正确姿势：金额 DECIMAL(10,2)，总共 10 位、小数 2 位
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)); — 0.3

DECIMAL 是精确存储，代价是比 DOUBLE 占空间、计算慢。超大并发的支付核心表里，常见的替代方案是「以分为单位存 BIGINT」，在应用层换算。

2.3 字符串：CHAR / VARCHAR / TEXT / ENUM

• CHAR(N)：定长，不足 N 位自动补空格（读出时去掉）。适合长度恒定的值：国家码 CHAR(2)、MD5 值 CHAR(32)。
• VARCHAR(N)：变长，额外花 1~2 字节记录实际长度。N 是「字符数」，utf8mb4 下一个字符最多占 4 字节，所以单列理论上限约 16383 字符（受行大小 65535 字节约束）。
• TEXT / BLOB：超长内容溢出到独立数据页存储，查询代价高。确有需求时垂直拆到附表，别和主业务字段混在一行。
• ENUM：改一个枚举值就要 DDL、排序按定义顺序而非字典序，团队规范通常禁用——用 TINYINT + 字典表（或 COMMENT 枚举说明）替代。

实战演练：IP 地址存什么类型？

— 方案 A：VARCHAR(15)，可读性最好，通用业务直接用
— 方案 B：INT UNSIGNED，省 7 字节，且能按网段范围比较（内网/日志统计场景）
SELECT INET_ATON('192.168.1.100'); — 3232235876（点分十进制 → 整数）
SELECT INET_NTOA(3232235876); — '192.168.1.100'（整数 → 点分十进制）

— 方案 C：VARBINARY(16)，需要支持 IPv6 时用 INET6_ATON/INET6_NTOA

2.4 时间类型：DATETIME vs TIMESTAMP

推荐组合：DATETIME + DEFAULT CURRENT_TIMESTAMP（5.6.5 起 DATETIME 也支持该默认值与 ON UPDATE CURRENT_TIMESTAMP），避开 TIMESTAMP 的 2038 上限；跨国业务统一存 UTC，展示层再转时区。

2.5 JSON 与生成列（8.0）

8.0 的 JSON 不是简单存个字符串：它是二进制格式，能按路径查询。但 JSON 列不能直接建索引，套路是「生成列 + 索引」：

ALTER TABLE products ADD COLUMN attrs JSON COMMENT '扩展属性';

— 把常用的 JSON 字段物化成生成列，再建索引
ALTER TABLE products
ADD COLUMN attrs_brand VARCHAR(50)
GENERATED ALWAYS AS (JSON_UNQUOTE(attrs –>> '$.brand')) STORED,
ADD INDEX idx_attrs_brand (attrs_brand);

SELECT * FROM products WHERE attrs_brand = 'Apple'; — 能走索引了

2.6 建表规范清单（团队通用版）

• 字段尽量 NOT NULL + DEFAULT：NULL 参与比较、统计、索引都有额外成本，COUNT(字段) 还会跳过 NULL 行；
• 主键 BIGINT UNSIGNED AUTO_INCREMENT，保持业务无意义；
• 金额 DECIMAL(10,2)（或分单位 BIGINT），禁 FLOAT/DOUBLE；
• 字符集 utf8mb4——MySQL 的 utf8 是历史阉割版（utf8mb3），存不了 emoji；
• 每表每字段写 COMMENT，半年后的你会感谢现在的你；
• created_at / updated_at 交给默认值自动维护，应用层少一次赋值；
• 单表字段控制在 50 个以内，TEXT 大字段垂直拆分；
• 不建物理外键：外键带来额外的锁与级联开销、DDL 耦合，分库分表后完全没法用——用「逻辑外键 + 应用层保证」。

记忆锚点：类型选型一句话——能小不用大，能定长不用变长，数值优于字符串，NULL 越少越好。索引列尤其如此：类型越小，一页装得越多，树越矮，查得越快。

三、SQL 核心语法与电商建库实战

从这一章开始动手：我们建一个贯穿全文的电商库 mall，后面所有的索引、事务、锁、优化案例都在这五张表上发生。语法只讲「用了会踩坑」的部分，手册上查得到的略过。

3.1 建库建表：五张表的完整 DDL

CREATE DATABASE mall DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mall;

CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '用户ID',
username VARCHAR(50) NOT NULL COMMENT '用户名',
email VARCHAR(100) NOT NULL COMMENT '邮箱',
status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1正常 0禁用',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_username (username),
UNIQUE KEY uk_email (email)
) ENGINE=InnoDB COMMENT='用户表';

CREATE TABLE categories ( — 树形分类
id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '分类ID',
name VARCHAR(50) NOT NULL COMMENT '分类名',
parent_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父分类ID，0为顶级',
sort INT NOT NULL DEFAULT 0 COMMENT '排序权重',
PRIMARY KEY (id),
KEY idx_parent (parent_id)
) ENGINE=InnoDB COMMENT='商品分类表';

CREATE TABLE products (
id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '商品ID',
category_id BIGINT UNSIGNED NOT NULL COMMENT '分类ID（逻辑外键）',
name VARCHAR(100) NOT NULL COMMENT '商品名',
price DECIMAL(10,2) NOT NULL COMMENT '售价',
stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存',
sales INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '累计销量',
status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1上架 0下架',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_category (category_id),
KEY idx_status_created (status, created_at)
) ENGINE=InnoDB COMMENT='商品表';

CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '订单ID',
order_no VARCHAR(32) NOT NULL COMMENT '业务订单号',
user_id BIGINT UNSIGNED NOT NULL COMMENT '下单用户（逻辑外键）',
total_amount DECIMAL(12,2) NOT NULL COMMENT '订单总额',
status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0待支付 1已支付 2已发货 3已完成 4已取消',
pay_time DATETIME NULL COMMENT '支付时间',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_order_no (order_no),
KEY idx_user_created (user_id, created_at),
KEY idx_status_created (status, created_at)
) ENGINE=InnoDB COMMENT='订单表';

CREATE TABLE order_items (
id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '明细ID',
order_id BIGINT UNSIGNED NOT NULL COMMENT '订单ID（逻辑外键）',
product_id BIGINT UNSIGNED NOT NULL COMMENT '商品ID（逻辑外键）',
quantity INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '购买数量',
price DECIMAL(10,2) NOT NULL COMMENT '成交单价（下单时快照）',
PRIMARY KEY (id),
KEY idx_order (order_id),
KEY idx_product (product_id)
) ENGINE=InnoDB COMMENT='订单明细表';

三个设计决策值得停下来想一想，它们是所有业务表设计的通用套路：

• order_items.price 存「快照」：商品价格会变，历史订单必须记当时的成交价，不能 JOIN products 现价来算——账单会错；
• orders.total_amount 存「冗余」：明明能 SUM(明细) 算出来，但订单页是高频读，冗余一列换取免聚合，读多写少场景的标准取舍；
• 只有逻辑外键：字段上加了索引（如 idx_user_created），但不建 FOREIGN KEY 约束，理由见 2.6。

3.2 DDL：ALTER 的坑与 8.0 的甜

ALTER TABLE products ADD COLUMN subtitle VARCHAR(100) NOT NULL DEFAULT '' COMMENT '副标题';
— 8.0 加列是 INSTANT 算法：只改元数据，千万行大表也是秒级

ALTER TABLE products ADD INDEX idx_name (name); — INPLACE，可在线加
ALTER TABLE products DROP INDEX idx_name;
ALTER TABLE products
MODIFY COLUMN subtitle VARCHAR(200) NOT NULL DEFAULT '' COMMENT '副标题';
— 注意：改列类型/长度无法 INSTANT，大表会锁或长时间 INPLACE，务必低峰操作

易错点：线上 ALTER 前先看算法。 执行前用 ALTER TABLE … , ALGORITHM=INSTANT 试探：支持就秒回；不支持会直接报错而不是默默锁表。大表变更推荐 gh-ost / pt-online-schema-change 工具。

3.3 DML：增删改的四个细节

— ① 批量插入：一条 SQL 插多行，比循环单插快一个数量级
INSERT INTO users (username, email) VALUES
('alice', 'alice@test.com'),
('bob', 'bob@test.com');

— ② 存在则更新（8.0.20+ 行别名写法，VALUES() 写法已废弃）
INSERT INTO products (id, name, price, stock) VALUES
(1, 'iPhone 17', 6999.00, 100) AS new
ON DUPLICATE KEY UPDATE stock = stock + new.stock;

— ③ UPDATE 条件写在 WHERE 里，顺手防超卖（第 7 章展开）
UPDATE products SET stock = stock – 1 WHERE id = 1 AND stock >= 1;

— ④ 安全带：开启后，没写 WHERE 或 WHERE 没走索引列的 UPDATE/DELETE 直接报错
SET SQL_SAFE_UPDATES = 1;

3.4 DCL：给应用账号最小权限

CREATE USER 'mall_app'@'192.168.1.%' IDENTIFIED BY '请用强密码';
GRANT SELECT, INSERT, UPDATE, DELETE ON mall.* TO 'mall_app'@'192.168.1.%';
SHOW GRANTS FOR 'mall_app'@'192.168.1.%'; — 检查授予结果
REVOKE DELETE ON mall.* FROM 'mall_app'@'192.168.1.%'; — 收回误授权限

• 应用账号只给表的增删改查，永远不给 DROP / ALTER / GRANT；
• 报表/看板账号给只读 GRANT SELECT；
• GRANT/REVOKE 立即生效，不需要 FLUSH PRIVILEGES（那是直接改 mysql.user 表时的老规矩）；
• 账号按「用途 + 来源网段」收紧：mall_app 只允许从应用网段连。

3.5 常用函数速查

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i'); — '2026-09-25 14:30'
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); — 一周后
SELECT DATEDIFF('2026-09-25', '2026-09-01'); — 24（只按日期算天数）
SELECT IFNULL(NULL, '默认值'); — '默认值'
SELECT CASE WHEN status = 1 THEN '正常' ELSE '禁用' END FROM users;

四、查询进阶：JOIN、窗口函数与经典业务题

业务 SQL 的复杂度 80% 来自「多表 + 分组 + 排名」。这一章把 JOIN、子查询、分组、窗口函数一口气过完，最后用四道经典业务题收尾——每一道都是面试和实战的高频客。

4.1 JOIN：INNER 和 LEFT 就够用了

— INNER JOIN：取交集。已支付订单 + 下单用户名
SELECT o.order_no, u.username, o.total_amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status >= 1;

— LEFT JOIN：保留左表全部。所有商品各自的销量（包括一件没卖出去的）
SELECT p.id, p.name, IFNULL(SUM(oi.quantity), 0) AS sold
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name;

RIGHT JOIN 在实际代码里几乎不出现——把两张表换个位置写 LEFT 就行。真正容易出错的是下面这个细节：

易错点：LEFT JOIN 的过滤条件写 ON 还是 WHERE？

对右表的过滤条件写在 ON 里：左表行全保留，不匹配的补 NULL（「每个商品及其高价订单，没卖过高价的显示 NULL」）。写在 WHERE 里：NULL 行会被过滤掉，LEFT JOIN 悄悄退化成 INNER JOIN。这是统计 SQL 出「数据变少」bug 的头号原因。

— ON 过滤右表：保留所有商品（正确姿势）
FROM products p LEFT JOIN order_items oi
ON oi.product_id = p.id AND oi.price > 100

— WHERE 过滤：卖不出高价的商品直接消失（LEFT 白写了）
FROM products p LEFT JOIN order_items oi
ON oi.product_id = p.id
WHERE oi.price > 100

4.2 子查询：IN 还是 EXISTS

— 下过单的用户：IN 写法
SELECT * FROM users u
WHERE u.id IN (SELECT user_id FROM orders);

— EXISTS 写法
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

经典结论是**「小表驱动大表」**：子查询结果集小用 IN（外层大表被小集合过滤）；外表小、内层大且内层连接列有索引时 EXISTS 更快（外表每行去内层索引探测一次）。8.0 的优化器在多数场景会自动改写两者，优先保证语义清晰，别为想象中的性能牺牲可读性。

4.3 GROUP BY 与 HAVING：销售日报

SELECT DATE(o.pay_time) AS day,
COUNT(*) AS order_cnt,
SUM(o.total_amount) AS gmv,
ROUND(AVG(o.total_amount), 2) AS avg_amount
FROM orders o
WHERE o.status >= 1 — 只统计有效订单
GROUP BY DATE(o.pay_time) — 按支付日分组
HAVING SUM(o.total_amount) > 10000 — 只要 GMV 过万的日子
ORDER BY day DESC;

记忆锚点：一条 SELECT 的执行顺序——FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。 WHERE 过滤「行」（不能用聚合函数），HAVING 过滤「组」（可以用聚合函数）。这也是为什么 SELECT 里起的别名在 WHERE 里用不了、在 ORDER BY 里能用。

易错点：ONLY_FULL_GROUP_BY。 8.0 默认开启该 sql_mode：SELECT 里出现的非聚合列必须出现在 GROUP BY 里，否则报 1055 错误。别急着关它——它防的是「一行结果混入别的行的值」这种逻辑炸弹。

4.4 窗口函数（8.0）：排名神器

窗口函数 = 「不合并行的 GROUP BY」：分组聚合会把 N 行压成 1 行，窗口函数在每行旁边附上一个计算结果。先认三兄弟：

SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rn, — 1 2 3 4（并列也硬排）
RANK() OVER (ORDER BY score DESC) AS rk, — 1 2 2 4（并列同名，跳号）
DENSE_RANK() OVER (ORDER BY score DESC) AS dr — 1 2 2 3（并列同名，不跳号）
FROM students;

配 PARTITION BY 就是「分组内排名」——直接解掉最经典的业务题：

— 经典题 1：每个分类销量 Top3 的商品
SELECT * FROM (
SELECT p.id, p.name, p.category_id, p.sales,
ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY p.sales DESC) AS rn
FROM products p
) t
WHERE t.rn <= 3;

— 经典题 2：每日 GMV 与累计 GMV（先按天聚合，再对聚合结果开窗）
SELECT DATE(pay_time) AS day,
SUM(total_amount) AS daily,
SUM(SUM(total_amount)) OVER (ORDER BY DATE(pay_time)) AS cumulative
FROM orders
WHERE status >= 1
GROUP BY DATE(pay_time);

第二题里 SUM(SUM(…)) OVER 看着吓人：内层 SUM 先完成「按天分组」，外层窗口对分组结果做「按天累加」。窗口函数在 GROUP BY 之后执行，所以聚合函数可以整体作为窗口的输入。

4.5 递归 CTE：查整棵分类树

categories 是树形结构（parent_id 指向父节点），查出「某分类的所有子孙」或整棵树，8.0 一个递归 CTE 搞定：

WITH RECURSIVE tree AS (
— 锚点：顶级分类
SELECT id, name, parent_id, 1 AS lvl, CAST(id AS CHAR(200)) AS path
FROM categories WHERE parent_id = 0
UNION ALL
— 递归：不断找上一层节点的孩子
SELECT c.id, c.name, c.parent_id, tree.lvl + 1, CONCAT(tree.path, '-', c.id)
FROM categories c
INNER JOIN tree ON c.parent_id = tree.id
)
SELECT * FROM tree ORDER BY path; — path 前缀天然按树形排序

4.6 两道压轴实战题

压轴 1：深分页为什么慢、怎么救

— 慢：LIMIT 1000000, 20 要取 1000020 行、丢掉前 100 万行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

— 优化 A：游标/书签翻页——记住上一页最后的 id（要求排序键连续递增）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

— 优化 B：延迟关联——先用覆盖索引把 20 个 id 找出来，再回表取整行
SELECT o.*
FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;

压轴 2：找出连续登录 ≥ 3 天的用户

思路一眼难、代码一行透：连续的日期减去连续的序号，差值恒定——差值相同的日子必然连成一段。

— 表：users_login(user_id, login_date)
SELECT user_id, MIN(login_date) AS streak_start, COUNT(*) AS days
FROM (
SELECT user_id, login_date,
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date) DAY) AS grp
FROM (SELECT DISTINCT user_id, login_date FROM users_login) d — 先去重
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;
— grp 相同的行即一段连续登录；要 ≥N 天就改 HAVING 条件

五、索引：从 B+ 树到 EXPLAIN 实战

索引是 MySQL 性能问题的第一现场：九成慢 SQL 的病因和药方都在索引上。这一章从「为什么偏偏是 B+ 树」讲起，一路到 EXPLAIN 实战排障，是全文最值得反复看的一章。

5.1 为什么是 B+ 树：一道排除题

回忆索引结构，最快的办法是把候选方案挨个排除：

• 哈希表：等值查询 O(1) 很香，但完全不支持范围查询和排序——WHERE id > 100 直接歇菜；
• 二叉搜索树：数据递增插入会退化成链表，O(n)；
• 红黑树/AVL：解决了退化，但二叉意味着「百万数据 20+ 层」，每层都是一次磁盘 IO；
• B 树：多叉矮胖，方向对了；但每个节点都存整行数据，一页 16KB 装不了几个键，树还是偏高；
• B+ 树：非叶子节点只存「键 + 指针」，叶子节点存数据并且按序组成双向链表——矮、且范围查询顺藤摸瓜。

#mermaid-svg-iAP2EhnRiZiIZRBv{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-iAP2EhnRiZiIZRBv .error-icon{fill:#552222;}#mermaid-svg-iAP2EhnRiZiIZRBv .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-iAP2EhnRiZiIZRBv .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-iAP2EhnRiZiIZRBv .marker{fill:#333333;stroke:#333333;}#mermaid-svg-iAP2EhnRiZiIZRBv .marker.cross{stroke:#333333;}#mermaid-svg-iAP2EhnRiZiIZRBv svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-iAP2EhnRiZiIZRBv p{margin:0;}#mermaid-svg-iAP2EhnRiZiIZRBv .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv .cluster-label text{fill:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv .cluster-label span{color:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv .cluster-label span p{background-color:transparent;}#mermaid-svg-iAP2EhnRiZiIZRBv .label text,#mermaid-svg-iAP2EhnRiZiIZRBv span{fill:#333;color:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv .node rect,#mermaid-svg-iAP2EhnRiZiIZRBv .node circle,#mermaid-svg-iAP2EhnRiZiIZRBv .node ellipse,#mermaid-svg-iAP2EhnRiZiIZRBv .node polygon,#mermaid-svg-iAP2EhnRiZiIZRBv .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-iAP2EhnRiZiIZRBv .rough-node .label text,#mermaid-svg-iAP2EhnRiZiIZRBv .node .label text,#mermaid-svg-iAP2EhnRiZiIZRBv .image-shape .label,#mermaid-svg-iAP2EhnRiZiIZRBv .icon-shape .label{text-anchor:middle;}#mermaid-svg-iAP2EhnRiZiIZRBv .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-iAP2EhnRiZiIZRBv .rough-node .label,#mermaid-svg-iAP2EhnRiZiIZRBv .node .label,#mermaid-svg-iAP2EhnRiZiIZRBv .image-shape .label,#mermaid-svg-iAP2EhnRiZiIZRBv .icon-shape .label{text-align:center;}#mermaid-svg-iAP2EhnRiZiIZRBv .node.clickable{cursor:pointer;}#mermaid-svg-iAP2EhnRiZiIZRBv .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-iAP2EhnRiZiIZRBv .arrowheadPath{fill:#333333;}#mermaid-svg-iAP2EhnRiZiIZRBv .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-iAP2EhnRiZiIZRBv .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-iAP2EhnRiZiIZRBv .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-iAP2EhnRiZiIZRBv .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-iAP2EhnRiZiIZRBv .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-iAP2EhnRiZiIZRBv .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-iAP2EhnRiZiIZRBv .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-iAP2EhnRiZiIZRBv .cluster text{fill:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv .cluster span{color:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-iAP2EhnRiZiIZRBv .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-iAP2EhnRiZiIZRBv rect.text{fill:none;stroke-width:0;}#mermaid-svg-iAP2EhnRiZiIZRBv .icon-shape,#mermaid-svg-iAP2EhnRiZiIZRBv .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-iAP2EhnRiZiIZRBv .icon-shape p,#mermaid-svg-iAP2EhnRiZiIZRBv .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-iAP2EhnRiZiIZRBv .icon-shape .label rect,#mermaid-svg-iAP2EhnRiZiIZRBv .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-iAP2EhnRiZiIZRBv .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-iAP2EhnRiZiIZRBv .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-iAP2EhnRiZiIZRBv :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

双向链表

双向链表

根节点（内部页）只存键和指针：15 · 35

叶子页①5 · 10 · 15

叶子页②20 · 25 · 30

叶子页③40 · 45 · 50

著名的「2000 万行只需 3 层」推导：主键 BIGINT 8 字节 + 页指针 6 字节 = 14 字节，一页 16KB 约 1170 个路牌；假设每行数据 1KB，一页叶子存 16 行。于是 3 层树容量 = 1170 × 1170 × 16 ≈ 2190 万行。根和中间层常年驻留内存，一次主键查询通常只有 1 次磁盘 IO。

5.2 聚簇索引与二级索引：回表的由来

• 聚簇索引（主键索引）：叶子节点存整行数据。InnoDB 表本身就是「按主键组织」的 B+ 树，所以也叫索引组织表；表没有主键时，InnoDB 会挑一个唯一非空索引顶替，再没有就生成隐藏的 row_id。
• 二级索引（普通索引）：叶子节点存「索引列的值 + 主键值」，不存行数据——不是不想存，是行数据会随页分裂移动，存地址会失效，存稳定的主键值才可靠。

于是一次二级索引查询的完整路径是：先在二级索引树找到主键值，再拿主键值去聚簇索引树查一遍整行——第二次查找就叫回表。如果命中行数很大（比如 10 万行），就是 10 万次回表，这就是很多「明明有索引还是慢」的真相。

记忆锚点：主键为什么推荐自增。 自增主键永远追加到 B+ 树最右侧，页写满才开新页，顺序友好；UUID 这类随机主键会让新数据随机插入，频繁页分裂、内存命中率下降、树越来越高。非要用分布式 ID，用趋势递增的雪花 ID。

5.3 覆盖索引与索引下推：省掉回表的两板斧

— 覆盖索引：SELECT 的列全部包含在索引里 → 不用回表，EXPLAIN 的 Extra 显示 Using index
SELECT id, category_id FROM products WHERE category_id = 3; — idx_category(category_id)

— 索引下推（ICP，5.6+）：把 WHERE 里能用索引判断的条件"下推"给存储引擎先过滤
— 假设索引 idx_name_age(name, age)：
SELECT * FROM users WHERE name LIKE '张%' AND age = 10;
— 没有 ICP：引擎按 '张%' 找到 1000 行全部回表，Server 层再按 age=10 过滤剩 10 行
— 有 ICP： 引擎在索引里就把 age != 10 的 990 行扔掉，只回表 10 行

5.4 最左前缀原则：联合索引的灵魂

联合索引 (a, b, c) 的排序规则是「先按 a 排，a 相同按 b 排，再相同按 c 排」——电话簿先姓后名。所以查询条件必须从最左列开始连续命中：

• 设计口诀：等值条件的列放前面，范围条件的列放最后；区分度（不重复比例）高的列放前面；多租户系统把 tenant_id 放最前；
• 已建 (a,b) 就不必单建 (a)：最左前缀让它兼职了 a 的索引——「减少冗余索引」是 DDL 评审的固定动作。

5.5 索引失效场景清单（逐条对照）

— ① 索引列参与运算或套函数
WHERE YEAR(created_at) = 2026 — ✗ 给列套了函数，索引报废
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01' — ✓ 改写成范围条件

— ② 隐式类型转换（字符串列用数字查）
WHERE order_no = 20260925001 — ✗ 等价于给 order_no 套 CAST 函数
WHERE order_no = '20260925001' — ✓ 类型对齐

— ③ 前导模糊匹配
WHERE name LIKE '%手机%' — ✗ 开头不确定，B+ 树没法定位
WHERE name LIKE '苹果%' — ✓ 相当于范围查询 [苹果, 苹果+1)

— ④ OR 连接了没有索引的列
WHERE category_id = 3 OR subtitle = 'x' — ✗ subtitle 无索引 → 整条 SQL 全表扫描

— ⑤ 联合索引不满足最左前缀（见 5.4）

— ⑥ 优化器「主动放弃」：预估命中行数太多，回表成本 > 全表扫描
— （严格说这不叫失效，是成本估算；FORCE INDEX 可强制，但先想清楚要不要）

纠偏：老文章的两大误传。「!= 和 NOT IN 一定失效」——不对，MySQL 会按成本估算决定走 range 还是全表，很多时候照样走索引；「IS NULL 用不了索引」——也不对，NULL 值同样存在 B+ 树里。判断标准永远是 EXPLAIN，不是口诀。

5.6 EXPLAIN：慢 SQL 的听诊器

EXPLAIN SELECT * FROM orders WHERE user_id = 88 AND status = 1
ORDER BY created_at DESC LIMIT 20;

type 梯度（从好到差）：const → eq_ref → ref → range → index → ALL。日常标准：至少 range，等值查询应到 ref，JOIN 被驱动表应到 eq_ref；出现 ALL（全表扫描）就要警惕。

• const：主键/唯一键等值，最多一行；
• eq_ref：JOIN 时被驱动表走主键或唯一索引；
• ref：普通二级索引等值；
• range：索引范围（BETWEEN、>、IN 等）；
• index：扫整棵索引树（常见于覆盖索引或 MIN/MAX）；
• ALL：全表扫描。

Extra 高频值：Using index 覆盖索引（好）；Using index condition 索引下推（好）；Using filesort 需要额外排序（优化信号）；Using temporary 建了临时表（GROUP BY / DISTINCT 常见，重点优化）。

5.7 实战：订单列表页慢查询优化全过程

场景：用户中心「我的订单」页，线上平均 1.8s。SQL 与优化前 EXPLAIN：

SELECT * FROM orders
WHERE user_id = 88 AND status = 1
ORDER BY created_at DESC
LIMIT 20;

— 优化前（表上只有 idx_user_created(user_id, created_at)）：
— type: ref | key: idx_user_created | rows: 8972 | Extra: Using index condition
— 诊断：status 不在索引里 → 8972 行全部回表再过滤，行数越多越慢

分析：查询条件是 user_id 等值 + status 等值 + created_at 排序。按「等值列在前、范围/排序列在后」重建联合索引：

ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);
ALTER TABLE orders DROP INDEX idx_user_created;

— 优化后：
— type: ref | key: idx_user_status_created | rows: 41 | Extra: Backward index scan
— 三列全部命中：先按 user_id+status 精确定位，created_at 在索引里天然有序 → 免排序
— 线上实测 1.8s → 12ms

记忆锚点：索引优化三步法。 ① EXPLAIN 看 type/key/rows/Extra 四件套 → ② 判断「条件列有没有都进索引、排序能不能免掉」→ ③ 按「等值在前、范围在后」调整联合索引列顺序。九成慢 SQL 走不完这三步就解决了。

六、事务与 MVCC

转账是最经典的开场：A 扣 100、B 加 100，两步必须同生共死。事务解决的正是「多个操作打包成一个不可分割的单元」，而 MVCC 是 InnoDB 实现「读写不互相阻塞」的独门功夫。

6.1 ACID：四个特性各靠什么实现

（这一列就是第 7、8 章的目录。）

6.2 并发三问题与四个隔离级别

两个事务并发跑，会出现三种「脏」现象：脏读（读到了别人没提交的数据）、不可重复读（同一事务里两次读同一行，值变了）、幻读（同一事务里两次按同样条件查，行数变了）。隔离级别就是「允许哪种现象」的取舍：

实战演练：转账事务 + 隔离级别

SELECT @@transaction_isolation; — 查看当前隔离级别，默认 REPEATABLE-READ
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; — 只改当前会话

— 转账：两个终端（会话 A 扣款、会话 B 观察）演示
BEGIN;
UPDATE accounts SET balance = balance – 100 WHERE id = 1; — A：只改不提交
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; — 或 ROLLBACK; 撤销一切
— 会话 B 在 A 提交前查询：RU 级别能读到未提交数据（脏读）；RC/RR 读到的仍是旧值

— SAVEPOINT：事务里的"存档点"
BEGIN;
UPDATE products SET stock = stock – 1 WHERE id = 1;
SAVEPOINT sp1;
UPDATE products SET stock = stock – 1 WHERE id = 2; — 这步做错了
ROLLBACK TO sp1; — 只撤销第二步，第一步保留
COMMIT;

6.3 MVCC 三件套：隐藏列、版本链、ReadView

RR 级别下「别人改了但没提交，我读到的还是旧值」——不是锁等待，是 **MVCC（多版本并发控制）**在悄悄工作。它由三件套组成：

• 隐藏列：每行数据都有两个隐形字段 DB_TRX_ID（最后修改它的事务 ID）和 DB_ROLL_PTR（回滚指针，指向 undo log 里的上一版本）；
• undo log 版本链：每次修改都把旧版本存进 undo log，一行数据的历史版本串成链表；
• ReadView（读视图）：事务发起快照读时生成的「名单」，记录此刻哪些事务活跃、哪些已提交。

#mermaid-svg-6dnQ4a4ygnKdUD0D{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-6dnQ4a4ygnKdUD0D .error-icon{fill:#552222;}#mermaid-svg-6dnQ4a4ygnKdUD0D .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-6dnQ4a4ygnKdUD0D .marker{fill:#333333;stroke:#333333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .marker.cross{stroke:#333333;}#mermaid-svg-6dnQ4a4ygnKdUD0D svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-6dnQ4a4ygnKdUD0D p{margin:0;}#mermaid-svg-6dnQ4a4ygnKdUD0D .label{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;color:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .cluster-label text{fill:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .cluster-label span{color:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .cluster-label span p{background-color:transparent;}#mermaid-svg-6dnQ4a4ygnKdUD0D .label text,#mermaid-svg-6dnQ4a4ygnKdUD0D span{fill:#333;color:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .node rect,#mermaid-svg-6dnQ4a4ygnKdUD0D .node circle,#mermaid-svg-6dnQ4a4ygnKdUD0D .node ellipse,#mermaid-svg-6dnQ4a4ygnKdUD0D .node polygon,#mermaid-svg-6dnQ4a4ygnKdUD0D .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .rough-node .label text,#mermaid-svg-6dnQ4a4ygnKdUD0D .node .label text,#mermaid-svg-6dnQ4a4ygnKdUD0D .image-shape .label,#mermaid-svg-6dnQ4a4ygnKdUD0D .icon-shape .label{text-anchor:middle;}#mermaid-svg-6dnQ4a4ygnKdUD0D .node .katex path{fill:#000;stroke:#000;stroke-width:1px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .rough-node .label,#mermaid-svg-6dnQ4a4ygnKdUD0D .node .label,#mermaid-svg-6dnQ4a4ygnKdUD0D .image-shape .label,#mermaid-svg-6dnQ4a4ygnKdUD0D .icon-shape .label{text-align:center;}#mermaid-svg-6dnQ4a4ygnKdUD0D .node.clickable{cursor:pointer;}#mermaid-svg-6dnQ4a4ygnKdUD0D .root .anchor path{fill:#333333!important;stroke-width:0;stroke:#333333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .arrowheadPath{fill:#333333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edgeLabel{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-6dnQ4a4ygnKdUD0D .edgeLabel p{background-color:rgba(232,232,232, 0.8);}#mermaid-svg-6dnQ4a4ygnKdUD0D .edgeLabel rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-6dnQ4a4ygnKdUD0D .labelBkg{background-color:rgba(232, 232, 232, 0.5);}#mermaid-svg-6dnQ4a4ygnKdUD0D .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .cluster text{fill:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D .cluster span{color:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-6dnQ4a4ygnKdUD0D .flowchartTitleText{text-anchor:middle;font-size:18px;fill:#333;}#mermaid-svg-6dnQ4a4ygnKdUD0D rect.text{fill:none;stroke-width:0;}#mermaid-svg-6dnQ4a4ygnKdUD0D .icon-shape,#mermaid-svg-6dnQ4a4ygnKdUD0D .image-shape{background-color:rgba(232,232,232, 0.8);text-align:center;}#mermaid-svg-6dnQ4a4ygnKdUD0D .icon-shape p,#mermaid-svg-6dnQ4a4ygnKdUD0D .image-shape p{background-color:rgba(232,232,232, 0.8);padding:2px;}#mermaid-svg-6dnQ4a4ygnKdUD0D .icon-shape .label rect,#mermaid-svg-6dnQ4a4ygnKdUD0D .image-shape .label rect{opacity:0.5;background-color:rgba(232,232,232, 0.8);fill:rgba(232,232,232, 0.8);}#mermaid-svg-6dnQ4a4ygnKdUD0D .label-icon{display:inline-block;height:1em;overflow:visible;vertical-align:-0.125em;}#mermaid-svg-6dnQ4a4ygnKdUD0D .node .label-icon path{fill:currentColor;stroke:revert;stroke-width:revert;}#mermaid-svg-6dnQ4a4ygnKdUD0D :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

roll_pointer

roll_pointer

当前版本balance = 900trx_id = 300

undo 版本 2balance = 1000trx_id = 200

undo 版本 1（初始）balance = 1000trx_id = 100

可见性判断规则（简化记忆版）——拿版本的 trx_id 和 ReadView 比大小：

不可见就顺着 roll_pointer 走到上一版本，重复判断，直到找到可见版本或链尾。一句口诀：「小于下限可见，超过上限不可见，中间查名单」。

记忆锚点：RC 和 RR 的本质区别只有一句话。 RC：每次快照读都生成新 ReadView → 能看到别的事务新提交的数据 → 不可重复读。 RR：只在事务第一次查询时生成 ReadView，之后一直复用 → 整个事务看到的世界定格在开头 → 可重复读。 所有隔离级别差异，都是这一句话的推论。

6.4 快照读与当前读

• 快照读：普通 SELECT，走 MVCC，不加锁，读的是快照；
• 当前读：SELECT … FOR UPDATE、SELECT … FOR SHARE（8.0 写法，旧版 LOCK IN SHARE MODE）以及所有 UPDATE / DELETE——读的是最新版本，并加锁。

为什么 UPDATE 必须「当前读」？如果不读最新值，两个事务基于同一个旧快照各自 +1，结果会丢一次更新。改数据永远以最新版本为准。

6.5 RR 到底有没有解决幻读

标准答案：InnoDB 的 RR 在很大程度上解决了幻读，但没堵死：

• 快照读：靠 MVCC，事务内多次查询结果一致，天然无幻读；
• 当前读：靠**临键锁（Next-Key Lock）**锁住「已有记录 + 记录间的间隙」，别的事务插不进来（第 7 章展开）；
• 漏洞场景：事务先快照读（未加锁），别的事务插入一行并提交，本事务再当前读——这批「幻影行」就出现了。要彻底避免，第一次查询就用 FOR UPDATE。

七、锁：从 MDL 到间隙锁

锁是隔离性的另一半。MVCC 让「读」不阻塞，锁负责「写与写」排队。这一章按粒度从大到小过一遍，最后用死锁复现和「超卖」三段式收尾——都是真实线上会疼的案例。

7.1 锁的全景：一张表看全

7.2 全局锁：备份的正确姿势

FLUSH TABLES WITH READ LOCK; — 全库只读：MyISAM 时代的备份方法
UNLOCK TABLES;

— InnoDB 时代：靠 MVCC 快照拿到一致性视图，完全不阻塞业务
mysqldump –single-transaction -uroot -p mall > mall_$(date +%F).sql

7.3 MDL 锁：最隐蔽的线上事故

对表做增删改查时自动加 MDL读锁，做 ALTER 时需要 MDL写锁，读写互斥。经典翻车现场：

会话 A：BEGIN; SELECT * FROM users WHERE id = 1; — 拿到 MDL 读锁，事务挂着没提交
会话 B：ALTER TABLE users ADD COLUMN vip INT; — 需要 MDL 写锁 → 排队等 A
会话 C：SELECT * FROM users WHERE id = 2; — 只要 MDL 读锁，却被 B 挡在后面
— 结果：这张表的所有后续查询全部堆积，业务"假死"

• 预防一：DDL 前先查长事务，有挂着的事务就别动表：SELECT * FROM information_schema.innodb_trx ORDER BY trx_started;
• 预防二：把 MDL 等待上限调小，别用默认值（默认约一年）：SET GLOBAL lock_wait_timeout = 10;——拿不到写锁就失败撤退，而不是把表堵死；
• 预防三：低峰期做 DDL，并先在预发环境演练。

7.4 行锁三兄弟：加在索引上的锁

假设表里有 id 为 1、5、10、15 四行（id 有索引），RR 级别下的加锁规则：

易错点：行锁加在索引上，索引失效等于锁全表。 UPDATE orders SET status = 4 WHERE order_no = 'X1';——order_no 没索引时，InnoDB 全表扫描，把每一行都锁住，其他事务的任何更新全部排队。「删了一个没走索引的 WHERE」和「锁全表」是同一件事，线上大批量 UPDATE 前先 EXPLAIN 一遍。

7.5 死锁复现与排查

— 会话 A — 会话 B
BEGIN; BEGIN;
UPDATE products SET stock = stock-1 WHERE id = 1;
UPDATE products SET stock = stock-1 WHERE id = 2;
UPDATE products SET stock = stock-1 WHERE id = 2; — A 等 B 放 id=2
UPDATE products SET stock = stock-1 WHERE id = 1; — B 等 A 放 id=1
— ERROR 1213 (40001): Deadlock found …（B 被选为牺牲者回滚）

SHOW ENGINE INNODB STATUS\\G
— 找 "LATEST DETECTED DEADLOCK" 段：两个事务各持有什么锁、在等什么锁、最后执行的 SQL

• InnoDB 默认开启死锁检测（innodb_deadlock_detect=ON），发现回环立即回滚代价小的一方，通常毫秒级解开；
• 拿不到锁的等待由 innodb_lock_wait_timeout（默认 50s）控制，超时报 ERROR 1205；
• 防死锁四招：多行更新固定顺序（如按主键升序）、事务尽量短、用条件更新代替「先查后改」、热点行用队列/Redis 削峰而不是硬怼数据库。

7.6 实战：超卖问题的三种解法

秒杀扣库存，同一个商品 1000 个请求同时来，库存只有 100——三段演进：

— 版本 1：先查后改（必超卖）
— stock = SELECT stock FROM products WHERE id = 1; → 应用判断 stock >= 1
— UPDATE products SET stock = stock – 1 WHERE id = 1;
— 并发下两个请求同时读到 stock=1、同时通过判断 → 卖出 2 件，库存只有 1

— 版本 2：条件更新（推荐，本质是乐观锁思想）
UPDATE products SET stock = stock – 1 WHERE id = 1 AND stock >= 1;
— affected_rows = 0 → 没抢到，返回"已售罄"；判断进入 WHERE，靠行锁保证原子性

— 版本 3：悲观锁 SELECT … FOR UPDATE（适合还要联动多表的复杂扣减）
BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE; — 锁住，别人进不来
— … 应用层业务校验、写订单、写明细 …
UPDATE products SET stock = stock – 1 WHERE id = 1;
COMMIT;

— 乐观锁通用形态：version 号
UPDATE docs SET content = '新内容', version = version + 1
WHERE id = 1 AND version = 5; — 命中 0 行 = 别人先改了，重读重试

记忆锚点：什么时候用哪种锁。 冲突少 → 乐观锁（version / 条件更新），无锁高性能，失败重试；冲突激烈 → 悲观锁（FOR UPDATE），一次锁到位避免反复重试打爆 CPU；秒杀级热点 → 数据库锁都不合适，上 Redis 预扣 + MQ 异步落库。

八、日志系统：redo / undo / binlog

第 6 章表说过：原子性靠 undo、持久性靠 redo。这一章把三本日志彻底讲透，再回答那个经典问题——为什么更新一条数据要写两次日志（两阶段提交）。

8.1 三本日志各管什么

8.2 redo log：WAL 的精髓

为什么改数据不直接刷盘？数据页在磁盘上随机分散，刷页是随机 IO；而日志是顺序追加，快几个数量级。所以 InnoDB 的选择是：先在内存（Buffer Pool）改页、同时把「这一页改了什么」顺序写进 redo log，脏页由后台线程慢慢刷。这套顺序写换随机写的思路就是 WAL（Write-Ahead Logging）。

• 循环写：redo log 是固定大小的环（如 4 个文件各 1GB），write pos 追着 checkpoint 跑；写满就必须停下来推进 checkpoint（强制刷脏页），此时业务更新会被阻塞——线上偶发的「MySQL 卡一下」，查查 redo log 大小；
• 落盘时机由 innodb_flush_log_at_trx_commit 控制：

8.3 undo log：回滚的底气

每次修改前先记「反向操作」：INSERT 记一条 DELETE，UPDATE 记旧值，DELETE 记整行。两个用途：一是 ROLLBACK 时逐条执行反向操作；二是作为 MVCC 的版本链（第 6 章图）。长事务的隐藏成本就在这：版本链不能清理，undo 越积越长，回滚段膨胀——又一个「别开长事务」的理由。

8.4 binlog：复制与归档的生命线

SHOW BINLOG EVENTS IN 'binlog.000123' LIMIT 10; — 人肉看一眼 binlog 内容
SHOW MASTER STATUS; — 当前写入到哪个 binlog 文件、位置

8.5 两阶段提交：为什么更新要写两次日志

redo log（InnoDB）和 binlog（Server）是两个独立的日志系统。假设不用两阶段、简单地「先 redo 后 binlog」，中间崩溃：恢复后主库有这条数据（redo 已落盘），binlog 里却没有 → 从库永远少这条 → 主从不一致。反过来先 binlog 后 redo，则从库多一条。两阶段提交就是让两本日志「要么都算数，要么都不算」：

InnoDB

执行器

InnoDB

执行器

#mermaid-svg-1aTCO8TcWnj9kMl7{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;fill:#333;}@keyframes edge-animation-frame{from{stroke-dashoffset:0;}}@keyframes dash{to{stroke-dashoffset:0;}}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-animation-slow{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 50s linear infinite;stroke-linecap:round;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-animation-fast{stroke-dasharray:9,5!important;stroke-dashoffset:900;animation:dash 20s linear infinite;stroke-linecap:round;}#mermaid-svg-1aTCO8TcWnj9kMl7 .error-icon{fill:#552222;}#mermaid-svg-1aTCO8TcWnj9kMl7 .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-thickness-normal{stroke-width:1px;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-thickness-invisible{stroke-width:0;fill:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-1aTCO8TcWnj9kMl7 .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-1aTCO8TcWnj9kMl7 .marker{fill:#333333;stroke:#333333;}#mermaid-svg-1aTCO8TcWnj9kMl7 .marker.cross{stroke:#333333;}#mermaid-svg-1aTCO8TcWnj9kMl7 svg{font-family:\”trebuchet ms\”,verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-1aTCO8TcWnj9kMl7 p{margin:0;}#mermaid-svg-1aTCO8TcWnj9kMl7 .actor{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-1aTCO8TcWnj9kMl7 text.actor>tspan{fill:black;stroke:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .actor-line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-1aTCO8TcWnj9kMl7 .innerArc{stroke-width:1.5;stroke-dasharray:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .messageLine0{stroke-width:1.5;stroke-dasharray:none;stroke:#333;}#mermaid-svg-1aTCO8TcWnj9kMl7 .messageLine1{stroke-width:1.5;stroke-dasharray:2,2;stroke:#333;}#mermaid-svg-1aTCO8TcWnj9kMl7 #arrowhead path{fill:#333;stroke:#333;}#mermaid-svg-1aTCO8TcWnj9kMl7 .sequenceNumber{fill:white;}#mermaid-svg-1aTCO8TcWnj9kMl7 #sequencenumber{fill:#333;}#mermaid-svg-1aTCO8TcWnj9kMl7 #crosshead path{fill:#333;stroke:#333;}#mermaid-svg-1aTCO8TcWnj9kMl7 .messageText{fill:#333;stroke:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .labelBox{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-1aTCO8TcWnj9kMl7 .labelText,#mermaid-svg-1aTCO8TcWnj9kMl7 .labelText>tspan{fill:black;stroke:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .loopText,#mermaid-svg-1aTCO8TcWnj9kMl7 .loopText>tspan{fill:black;stroke:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .loopLine{stroke-width:2px;stroke-dasharray:2,2;stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);}#mermaid-svg-1aTCO8TcWnj9kMl7 .note{stroke:#aaaa33;fill:#fff5ad;}#mermaid-svg-1aTCO8TcWnj9kMl7 .noteText,#mermaid-svg-1aTCO8TcWnj9kMl7 .noteText>tspan{fill:black;stroke:none;}#mermaid-svg-1aTCO8TcWnj9kMl7 .activation0{fill:#f4f4f4;stroke:#666;}#mermaid-svg-1aTCO8TcWnj9kMl7 .activation1{fill:#f4f4f4;stroke:#666;}#mermaid-svg-1aTCO8TcWnj9kMl7 .activation2{fill:#f4f4f4;stroke:#666;}#mermaid-svg-1aTCO8TcWnj9kMl7 .actorPopupMenu{position:absolute;}#mermaid-svg-1aTCO8TcWnj9kMl7 .actorPopupMenuPanel{position:absolute;fill:#ECECFF;box-shadow:0px 8px 16px 0px rgba(0,0,0,0.2);filter:drop-shadow(3px 5px 2px rgb(0 0 0 / 0.4));}#mermaid-svg-1aTCO8TcWnj9kMl7 .actor-man line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;}#mermaid-svg-1aTCO8TcWnj9kMl7 .actor-man circle,#mermaid-svg-1aTCO8TcWnj9kMl7 line{stroke:hsl(259.6261682243, 59.7765363128%, 87.9019607843%);fill:#ECECFF;stroke-width:2px;}#mermaid-svg-1aTCO8TcWnj9kMl7 :root{–mermaid-font-family:\”trebuchet ms\”,verdana,arial,sans-serif;}

UPDATE orders SET status = 1 WHERE id = 10

写 undo log（记录旧值）

Buffer Pool 中修改数据页（脏页）

返回影响行数

COMMIT

① redo log 写入，状态 = prepare

可以提交

② binlog 写入并落盘

③ 事务提交

redo log 状态 = commit，完成

记忆锚点：崩溃恢复的裁决规则。 重启后扫描 redo log：状态是 commit → 直接提交；状态是 prepare → 去查 binlog：这条事务的 binlog 完整（有对应 XID）→ 提交；不完整 → 回滚。一句话：「redo 说改了，还得 binlog 拍板」。

与两阶段提交配套的还有 binlog 的落盘参数 sync_binlog：0（交给 OS，可能丢）、1（每次提交 fsync，最安全）、N（攒 N 个事务再 fsync）。生产标准配置是「双 1」：innodb_flush_log_at_trx_commit = 1 + sync_binlog = 1——钱相关的系统别省这个 IO。

九、性能优化实战

前面八章的知识，最终都汇到这一章使用。优化的正确顺序永远是：先找到慢的 SQL（慢查询日志），再看它为什么慢（EXPLAIN），最后对症下药（索引/SQL 改写/参数/架构）。反过来「凭感觉加索引」是新人最常见的弯路。

9.1 第一步：让慢 SQL 自己现身

— 开启慢查询日志：记录所有超过 1 秒的 SQL（以及没走索引的全表扫描）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; — 线上一般从 1s 起步，逐步收紧到 0.5 / 0.2

— 找到日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

— 8.0 直接查 performance_schema，聚合出 Top 慢 SQL
SELECT DIGEST_TEXT AS sql_pattern,
COUNT_STAR AS exec_count,
ROUND(AVG_TIMER_WAIT/1e12, 3) AS avg_sec,
ROUND(SUM_TIMER_WAIT/1e12, 3) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;
— 按 total_sec 排序：总耗时最多的先治理；按 avg_sec 排序：单次最疼的先治理

9.2 看整体健康度

SHOW GLOBAL STATUS LIKE 'Threads_running'; — 当前活跃线程，飙高 = 并发压力或锁等待
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%'; — 行锁统计
— Innodb_row_lock_waits / Innodb_row_lock_time_avg 高 → 第 7 章的锁问题

— Buffer Pool 命中率：低于 99% 说明内存不够或存在全表扫描
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
— 命中率 = 1 – Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests

9.3 实战案例一：深分页拖垮后台导出

症状：运营后台导出订单，翻到几百页后每页要 5s+，期间整库 RT 上升。

— 原始 SQL：LIMIT 500000, 20 —— 索引能定位起点，但优化器仍要数过50万行并回表
SELECT * FROM orders ORDER BY id LIMIT 500000, 20;

— 处方（游标翻页，4.6 节讲过）：WHERE id > 上页最后id，一步跳到位
SELECT * FROM orders WHERE id > 500000 ORDER BY id LIMIT 20; — 5s → 8ms
— 导出场景没有"上一页"按钮，天然适合游标；页面跳转场景用延迟关联兜底

9.4 实战案例二：一条统计 SQL 拖垮整个服务

症状：晚高峰订单创建全部超时，但订单表 QPS 并不高。

— 罪魁：运营写了条统计 SQL，WHERE 里的列套了函数
SELECT COUNT(*) FROM orders WHERE DATE(created_at) = '2026-09-24';
— EXPLAIN: type=ALL, rows=全表 → 全表扫描

— 更糟的是同一条连接随后执行了：
UPDATE orders SET status = 4 WHERE DATE(created_at) < '2026-09-01';
— 没走索引的 UPDATE = 全表逐行加锁（7.4 节）→ 所有订单更新排队 → 业务雪崩

— 处方：改成范围条件，索引复活
SELECT COUNT(*) FROM orders
WHERE created_at >= '2026-09-24' AND created_at < '2026-09-25'; — 走 idx_status_created
UPDATE orders SET status = 4
WHERE created_at < '2026-09-01'; — 走索引，只锁旧行
— 教训：函数包列不仅慢查询，批量 UPDATE 时还会升级成锁全表

9.5 实战案例三：COUNT(*) 太慢怎么救

症状：商品列表页要显示总数，SELECT COUNT(*) FROM products 要 2s（200 万行）。

— 认清事实：InnoDB 的 COUNT(*) 必须真数（MVCC 下每个事务可见行数不同），没有捷径
— 但可以给 COUNT 加过滤条件，让它走覆盖索引免回表：
SELECT COUNT(*) FROM products WHERE category_id = 3; — 走 idx_category，只扫索引树

— 页面不要求精确时的三层方案：
— ① 显示"约 N 万"：explain 的 rows 估算值直接拿来用
EXPLAIN SELECT * FROM products; — rows 列 ≈ 行数，毫秒级
— ② 计数表：计数频繁的场景，单独维护 counter 表或 Redis 计数
— ③ 汇总表：离线/定时任务每 5 分钟聚合一次写进 stats 表，前端读它

9.6 参数调优速查（8.0 默认配置基础上）

9.7 优化心法：一张地图

记忆锚点：性能优化的优先级。 SQL 与索引（成本最低收益最大）→ 表结构（冗余/拆分/冷热分离）→ 参数（Buffer Pool、日志）→ 架构（读写分离、缓存、分库分表）。 前两层解决 90% 的问题，永远从它们开始；分库分表是最后的大招，不是第一反应。

十、速查手册与复习路线

最后一章是「考前冲刺」：20 个高频问题的一句话答案，适合面试前或排障时快速唤醒记忆。答案里括号内的章节号，就是需要展开回忆的入口。

10.1 20 问速答

10.2 常用运维命令贴

— 看状态
SHOW PROCESSLIST; — 当前连接与正在执行的 SQL
SHOW ENGINE INNODB STATUS\\G — 死锁/锁等待/缓冲池全景
SELECT * FROM information_schema.innodb_trx; — 活跃事务（DDL 前必查）

— 看表
SHOW TABLE STATUS FROM mall; — 引擎、行数、平均行长
SHOW INDEX FROM orders; — 索引清单
SHOW CREATE TABLE orders\\G — 完整建表语句

— 看执行
EXPLAIN ANALYZE SELECT ...; — 8.0 的 ANALYZE 会真的执行并给真实耗时

— 备份与恢复
mysqldump –single-transaction -uroot -p mall > mall_backup.sql
mysql –uroot –p mall < mall_backup.sql

10.3 三轮复习路线

• 第一轮 · 串主线（今天）：只读每章的「记忆锚点」和四张图，把「SQL 生命周期 → 索引 → MVCC → 锁 → 日志」这条线连起来，目标是合上页面能复述这条线；
• 第二轮 · 跑实战（本周）：把 mall 五张表建出来，亲手跑第 3、4 章的每一段 SQL，做一遍 5.7 节的索引优化实验和 7.5 节的死锁复现——手过一遍顶看十遍；
• 第三轮 · 刷速查（面试前夜）：只看 10.1 的 20 问，答不上来的按括号里的章节号回去补那一段。

最后一块拼图：所有 MySQL 知识最终收敛成一句话——一条 SQL 进来，服务层解析优化，引擎层用 B+ 树找数据，靠 MVCC 让读写不打架，靠锁让写写排队，靠 redo/undo/binlog 保证不丢不错。能默写这句话，你就没忘。

MySQL 知识全景回顾 · 基于 MySQL 8.0 / InnoDB · 2026-09 整理 · 欢迎收藏复习，转载请注明出处