← 返回

MySQL 进阶复习手册（理解强化版）

首发 2026/07/30 阅读 0 评论 0 更新 2026/07/30

MySQL 进阶复习手册（理解强化版）

适用范围：MySQL 8.0 / 8.4，默认存储引擎为 InnoDB。
定位：这不是 PDF 的缩写版，而是按理解顺序重新编写的复习与实验手册。
约定：SQL 关键字大写，表名与列名使用 snake_case；终端结果只保留能说明问题的行。
最重要的学习顺序：InnoDB → 事务与 MVCC → 索引 → 执行计划 → SQL 优化 → 锁。

统一练习数据库
MySQL 架构与存储引擎
InnoDB、事务日志与 MVCC
索引：从 B+Tree 到联合索引
性能分析与 EXPLAIN
SQL 优化
视图、存储过程、函数与触发器
MySQL 管理与常用工具
锁：从“为什么会等”到死锁排查
高频面试题与最终复习清单
官方校对入口

0. 统一练习数据库

后文始终使用同一套电商数据，不再每节临时换表。

这套数据故意包含以下情况：

商品主键为 201、203、205、210，中间缺少 202、204，便于演示间隙锁。
同一客户有多张订单，便于演示联合索引、覆盖索引和分页。
product_name 故意不建索引，便于观察无合适索引时的扫描与加锁范围。
两个账户可直接演示转账、锁等待和死锁。
审计表初始为空，后文通过触发器写入。

0.1 `customers`：客户表

text

+-------------+---------------+--------+---------------------+
| customer_id | customer_name | level  | created_at          |
+-------------+---------------+--------+---------------------+
|         101 | 张伟          | VIP    | 2025-01-10 09:00:00 |
|         102 | 李娜          | NORMAL | 2025-02-18 10:30:00 |
|         103 | 王强          | VIP    | 2025-03-05 14:20:00 |
|         104 | 赵敏          | NORMAL | 2025-04-12 16:00:00 |
+-------------+---------------+--------+---------------------+
4 rows in set

0.2 `products`：商品表

text

+------------+------------+---------------+---------+-------+--------+
| product_id | sku        | product_name  | price   | stock | status |
+------------+------------+---------------+---------+-------+--------+
|        201 | KB-001     | 机械键盘      |  399.00 |   100 | ACTIVE |
|        203 | MS-001     | 无线鼠标      |  129.00 |    80 | ACTIVE |
|        205 | DP-001     | 27寸显示器    | 1299.00 |    20 | ACTIVE |
|        210 | HUB-001    | USB-C扩展坞   |  199.00 |    50 | ACTIVE |
+------------+------------+---------------+---------+-------+--------+
4 rows in set

0.3 `orders`：订单表

text

+----------+--------------+-------------+-----------+--------------+---------------------+
| order_id | order_no     | customer_id | status    | total_amount | created_at          |
+----------+--------------+-------------+-----------+--------------+---------------------+
|     1001 | O20260720001 |         101 | PAID      |       528.00 | 2026-07-20 10:00:00 |
|     1002 | O20260721001 |         101 | SHIPPED   |      1299.00 | 2026-07-21 11:20:00 |
|     1003 | O20260722001 |         102 | PAID      |       398.00 | 2026-07-22 09:15:00 |
|     1004 | O20260723001 |         103 | CANCELLED |       199.00 | 2026-07-23 18:30:00 |
|     1005 | O20260724001 |         104 | PENDING   |       399.00 | 2026-07-24 13:40:00 |
|     1006 | O20260725001 |         101 | PAID      |       199.00 | 2026-07-25 08:50:00 |
+----------+--------------+-------------+-----------+--------------+---------------------+
6 rows in set

0.4 `order_items`：订单明细表

text

+----------+------------+----------+------------+
| order_id | product_id | quantity | unit_price |
+----------+------------+----------+------------+
|     1001 |        201 |        1 |     399.00 |
|     1001 |        203 |        1 |     129.00 |
|     1002 |        205 |        1 |    1299.00 |
|     1003 |        210 |        2 |     199.00 |
|     1004 |        210 |        1 |     199.00 |
|     1005 |        201 |        1 |     399.00 |
|     1006 |        210 |        1 |     199.00 |
+----------+------------+----------+------------+
7 rows in set

0.5 `accounts`：账户表

text

+------------+------------+---------+---------+
| account_id | owner_name | balance | version |
+------------+------------+---------+---------+
|          1 | 张伟       | 5000.00 |       0 |
|          2 | 李娜       | 3000.00 |       0 |
+------------+------------+---------+---------+
2 rows in set

0.6 表关系与关键索引

关系或索引	用途
`customers 1 → N orders`	一名客户可有多张订单
`orders 1 → N order_items`	一张订单可有多条明细
`products 1 → N order_items`	一个商品可出现在多张订单中
`orders(customer_id, status, created_at, order_id)`	联合索引、排序、覆盖索引
`orders(created_at, order_id)`	时间线分页
`products.sku`	唯一二级索引
`products.product_name`	故意无索引，供扫描与锁实验使用

0.7 完整初始化脚本

警告：脚本会删除同名练习库，只能在个人练习环境执行。
后文带有增删改和 DDL 的示例是独立实验，不要从头到尾无脑连续执行。

sql

DROP DATABASE IF EXISTS mysql_advanced_review;

CREATE DATABASE mysql_advanced_review
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE mysql_advanced_review;

CREATE TABLE customers (
    customer_id   INT UNSIGNED PRIMARY KEY,
    customer_name VARCHAR(30) NOT NULL,
    email         VARCHAR(100) NOT NULL,
    level         VARCHAR(10) NOT NULL DEFAULT 'NORMAL',
    created_at    DATETIME NOT NULL,
    CONSTRAINT uk_customers_email UNIQUE (email),
    CONSTRAINT chk_customers_level
        CHECK (level IN ('NORMAL', 'VIP'))
) ENGINE = InnoDB;

CREATE TABLE products (
    product_id   INT UNSIGNED PRIMARY KEY,
    sku          VARCHAR(30) NOT NULL,
    product_name VARCHAR(80) NOT NULL,
    category     VARCHAR(30) NOT NULL,
    price        DECIMAL(10, 2) NOT NULL,
    stock        INT UNSIGNED NOT NULL,
    status       VARCHAR(10) NOT NULL DEFAULT 'ACTIVE',
    created_at   DATETIME NOT NULL,
    CONSTRAINT uk_products_sku UNIQUE (sku),
    CONSTRAINT chk_products_price CHECK (price >= 0),
    CONSTRAINT chk_products_status
        CHECK (status IN ('ACTIVE', 'INACTIVE')),
    INDEX idx_products_category_price (category, price)
) ENGINE = InnoDB;

CREATE TABLE orders (
    order_id     BIGINT UNSIGNED PRIMARY KEY,
    order_no     VARCHAR(24) NOT NULL,
    customer_id  INT UNSIGNED NOT NULL,
    status       VARCHAR(12) NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    created_at   DATETIME NOT NULL,
    remark       VARCHAR(200) NULL,
    CONSTRAINT uk_orders_no UNIQUE (order_no),
    CONSTRAINT chk_orders_status
        CHECK (status IN ('PENDING', 'PAID', 'SHIPPED', 'CANCELLED')),
    CONSTRAINT chk_orders_amount CHECK (total_amount >= 0),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    INDEX idx_orders_customer_status_created
        (customer_id, status, created_at, order_id),
    INDEX idx_orders_created (created_at, order_id)
) ENGINE = InnoDB;

CREATE TABLE order_items (
    order_id   BIGINT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity   INT UNSIGNED NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT chk_order_items_quantity CHECK (quantity > 0),
    CONSTRAINT chk_order_items_price CHECK (unit_price >= 0),
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id) REFERENCES orders (order_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_items_product
        FOREIGN KEY (product_id) REFERENCES products (product_id),
    INDEX idx_order_items_product (product_id)
) ENGINE = InnoDB;

CREATE TABLE accounts (
    account_id INT UNSIGNED PRIMARY KEY,
    owner_name VARCHAR(30) NOT NULL,
    balance    DECIMAL(12, 2) NOT NULL,
    version    INT UNSIGNED NOT NULL DEFAULT 0,
    CONSTRAINT chk_accounts_balance CHECK (balance >= 0)
) ENGINE = InnoDB;

CREATE TABLE order_audit (
    audit_id   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id   BIGINT UNSIGNED NOT NULL,
    old_status VARCHAR(12) NULL,
    new_status VARCHAR(12) NULL,
    changed_at DATETIME NOT NULL,
    INDEX idx_order_audit_order_time (order_id, changed_at)
) ENGINE = InnoDB;

INSERT INTO customers
    (customer_id, customer_name, email, level, created_at)
VALUES
    (101, '张伟', 'zhangwei@example.com', 'VIP',    '2025-01-10 09:00:00'),
    (102, '李娜', 'lina@example.com',      'NORMAL', '2025-02-18 10:30:00'),
    (103, '王强', 'wangqiang@example.com', 'VIP',    '2025-03-05 14:20:00'),
    (104, '赵敏', 'zhaomin@example.com',   'NORMAL', '2025-04-12 16:00:00');

INSERT INTO products
    (product_id, sku, product_name, category, price, stock, status, created_at)
VALUES
    (201, 'KB-001',  '机械键盘',    '外设',  399.00, 100, 'ACTIVE', '2025-05-01 09:00:00'),
    (203, 'MS-001',  '无线鼠标',    '外设',  129.00,  80, 'ACTIVE', '2025-05-02 09:00:00'),
    (205, 'DP-001',  '27寸显示器',  '显示', 1299.00,  20, 'ACTIVE', '2025-05-03 09:00:00'),
    (210, 'HUB-001', 'USB-C扩展坞', '配件',  199.00,  50, 'ACTIVE', '2025-05-04 09:00:00');

INSERT INTO orders
    (order_id, order_no, customer_id, status, total_amount, created_at, remark)
VALUES
    (1001, 'O20260720001', 101, 'PAID',      528.00, '2026-07-20 10:00:00', NULL),
    (1002, 'O20260721001', 101, 'SHIPPED',  1299.00, '2026-07-21 11:20:00', NULL),
    (1003, 'O20260722001', 102, 'PAID',      398.00, '2026-07-22 09:15:00', NULL),
    (1004, 'O20260723001', 103, 'CANCELLED', 199.00, '2026-07-23 18:30:00', '客户取消'),
    (1005, 'O20260724001', 104, 'PENDING',   399.00, '2026-07-24 13:40:00', NULL),
    (1006, 'O20260725001', 101, 'PAID',      199.00, '2026-07-25 08:50:00', NULL);

INSERT INTO order_items
    (order_id, product_id, quantity, unit_price)
VALUES
    (1001, 201, 1,  399.00),
    (1001, 203, 1,  129.00),
    (1002, 205, 1, 1299.00),
    (1003, 210, 2,  199.00),
    (1004, 210, 1,  199.00),
    (1005, 201, 1,  399.00),
    (1006, 210, 1,  199.00);

INSERT INTO accounts (account_id, owner_name, balance, version) VALUES
    (1, '张伟', 5000.00, 0),
    (2, '李娜', 3000.00, 0);

初始化后快速确认：

sql

SELECT VERSION(), @@default_storage_engine,
       @@transaction_isolation, @@autocommit;

text

+-----------+--------------------------+-------------------------+--------------+
| VERSION() | @@default_storage_engine | @@transaction_isolation | @@autocommit |
+-----------+--------------------------+-------------------------+--------------+
| 8.x.x     | InnoDB                   | REPEATABLE-READ         |            1 |
+-----------+--------------------------+-------------------------+--------------+
1 row in set

版本号和服务器配置以你的实际环境为准。锁实验默认使用 REPEATABLE READ。

0.8 可选：扩充订单数据做性能实验

六条数据适合看语义，不适合比较性能。以下脚本额外生成 20,000 张订单；只在性能练习时执行。

sql

SET SESSION cte_max_recursion_depth = 20000;

INSERT INTO orders
    (order_id, order_no, customer_id, status,
     total_amount, created_at, remark)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM seq
    WHERE n < 20000
)
SELECT
    10000 + n,
    CONCAT('B', LPAD(n, 17, '0')),
    101 + MOD(n, 4),
    ELT(1 + MOD(n, 4), 'PENDING', 'PAID', 'SHIPPED', 'CANCELLED'),
    50 + MOD(n * 37, 2000),
    TIMESTAMP('2025-01-01 00:00:00') + INTERVAL n MINUTE,
    NULL
FROM seq;

ANALYZE TABLE orders;

删除扩充数据：

sql

DELETE FROM orders WHERE order_id >= 10001;
ANALYZE TABLE orders;

1. MySQL 架构与存储引擎

1.1 一条 SQL 经历了什么

可以把 MySQL 理解为四层：

层次	负责什么	常见关键词
客户端与连接	建立连接、认证、权限、会话	TCP、Socket、TLS、连接线程
Server 层	解析、语义检查、优化、执行	Parser、Optimizer、Executor
存储引擎层	读写数据、维护索引、事务与行锁	InnoDB、MyISAM、Memory
持久化文件	表空间、日志、配置和系统文件	`.ibd`、redo、undo、binlog

以查询为例：

text

mysql> SELECT order_id, status
    -> FROM orders
    -> WHERE customer_id = 101 AND status = 'PAID';

[连接与鉴权]
      ↓
[解析：SQL 是否合法、列是否存在]
      ↓
[优化：选哪个索引、先访问哪张表]
      ↓
[执行器调用 InnoDB]
      ↓
[InnoDB 读索引页 / 数据页]
      ↓
[结果返回客户端]

关键结论：

优化器做的是成本估算，不是固定套规则。
索引和行锁主要由存储引擎实现。
视图、存储过程、权限判断等跨引擎能力主要在 Server 层。
MySQL 8.0 已移除旧查询缓存，不要再把 Query Cache 当成当前架构组件。

1.2 存储引擎是“表级选择”

同一个数据库中的不同表可以使用不同引擎：

sql

SHOW ENGINES;

SHOW VARIABLES LIKE 'default_storage_engine';

SHOW CREATE TABLE orders\G

建表时显式指定：

sql

CREATE TABLE demo_engine (
    id INT PRIMARY KEY,
    note VARCHAR(50)
) ENGINE = InnoDB;

1.3 InnoDB、MyISAM、Memory 怎么选

能力	InnoDB	MyISAM	Memory
事务	支持	不支持	不支持
锁粒度	行级为主，也有表级锁	表锁	表锁
崩溃恢复	强	弱于 InnoDB	数据重启即失
外键	支持	不支持	不支持
常见索引	B+Tree、全文、空间	B+Tree、全文、空间	Hash、B+Tree
典型定位	绝大多数业务表	兼容旧系统、少数特殊场景	小型临时或易重建数据

实践结论：

新业务默认选 InnoDB。
不要仅凭“只读多，所以 MyISAM 更快”就换引擎；事务、并发和恢复能力通常更重要。
Memory 表的定义可以保留，但数据在服务重启后消失，不能保存核心业务数据。

1.4 InnoDB 表空间和页

常见逻辑层次：

text

表空间 Tablespace
└─ 段 Segment
   └─ 区 Extent
      └─ 页 Page
         └─ 行 Record

页是 InnoDB 管理磁盘数据的基本单位，默认页大小通常为 16 KiB。
一个区默认包含连续的多个页。
启用 innodb_file_per_table 时，每张 InnoDB 表通常有自己的 .ibd 表空间文件。
数据和 B+Tree 索引都按页组织，而不是“一行对应一次磁盘读取”。

sql

SHOW VARIABLES LIKE 'innodb_page_size';
SHOW VARIABLES LIKE 'innodb_file_per_table';

1.5 存储引擎面试速答

问：InnoDB 和 MyISAM 的主要区别？
InnoDB 支持事务、行级并发、崩溃恢复和外键，是现代 MySQL 的默认业务引擎；MyISAM 不支持事务和行锁，主要见于旧系统或特殊只读场景。回答时不要只说“谁更快”，性能取决于负载。

2. InnoDB、事务日志与 MVCC

这一章是理解索引和锁的地基。先记住一句话：

数据页负责保存结果，redo 保证崩溃后能重做，undo 保存旧版本并支持回滚，MVCC 决定普通查询能看见哪个版本。

2.1 Buffer Pool：为什么查询不总是读磁盘

InnoDB 的数据最终在磁盘，但正常读写会大量经过 Buffer Pool：

text

SELECT / UPDATE
      ↓
Buffer Pool 中有页？ ──是──> 直接访问内存页
      │
      否
      ↓
从磁盘把页载入 Buffer Pool

页的常见状态：

状态	含义
Free page	未使用的空闲页
Clean page	内存内容与磁盘一致
Dirty page	内存已修改，尚未刷新到数据文件

sql

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW ENGINE INNODB STATUS\G

不要机械套用“Buffer Pool 必须等于物理内存 80%”。专用数据库服务器可给较高比例；容器、共享主机和开发机必须给操作系统及其他进程留空间。

2.2 其他内存、磁盘与后台组件

只背 Buffer Pool 不够。把其余组件放进下面这张总表，先理解“它在解决什么问题”：

组件	在哪里	作用	复习抓手
Change Buffer	内存中占用 Buffer Pool，磁盘部分位于系统表空间	暂存符合条件的非唯一二级索引页变更，等目标页被读取时再合并	减少随机读；不是主键缓存
Adaptive Hash Index	内存	根据热点等值访问自动建立哈希路径	引擎自动决定，不是手工 `CREATE HASH INDEX`
Log Buffer	内存	redo 写入磁盘前的缓冲区	它是 redo 缓冲区，不是通用的 undo 缓冲区
数据页与索引页	表空间文件	保存表和 B+Tree 的持久结果	常先读入 Buffer Pool 再访问
redo log files	磁盘	保存崩溃恢复所需的重做记录	配合 WAL；不保存业务快照
undo tablespaces	磁盘	保存回滚信息与历史版本	活跃 Read View 仍需要时不能清理
doublewrite files/area	磁盘	数据页写入最终位置前，先留一份完整副本	防止部分页写入；不能替代 redo
temporary tablespaces	磁盘	承载 InnoDB 临时表等临时数据	不等于业务表空间

Change Buffer 的直观过程：

text

修改二级索引页
      ↓
目标页已在 Buffer Pool？ ──是──> 直接修改内存页
      │
      否，且满足缓冲条件
      ↓
先记入 Change Buffer
      ↓
以后读入目标页时合并

注意版本差异：MySQL 8.4 中 innodb_change_buffering 默认值为 none，因此“Change Buffer 永远在工作”已经不是可靠结论；旧版本或已有实例配置可能不同，必须查看实际值。

sql

SHOW VARIABLES LIKE 'innodb_change_buffering';
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';

Adaptive Hash Index 可能缩短热点等值访问，也可能在特定高并发负载中形成争用。是否启用应以实际压测和监控为准，不要把它当成普通索引设计手段。

doublewrite 解决的是“一个数据页只写了一部分，页面已损坏”的问题：

text

脏页 → 先写 doublewrite → 再写最终数据文件
             │
             └─ 最终页损坏时，可用完整副本辅助恢复

后台工作可以按职责记：

后台职责	做什么
Page Cleaner	把脏页逐步刷新到数据文件
Purge	清理已无任何活跃 Read View 需要的旧版本和删除标记
I/O 线程	处理异步读写等 I/O 工作
主协调逻辑	调度检查点、刷脏、清理等维护工作

线程数量和内部命名会随版本与配置变化，复习时掌握职责，不要死背固定线程数。

2.3 一次更新为什么不立即把整页刷盘

假设修改商品库存：

sql

UPDATE products
SET stock = stock - 1
WHERE product_id = 201;

简化过程：

text

1. 在 Buffer Pool 中修改数据页，页变脏
2. 生成 undo，保存修改前的信息
3. 生成 redo，记录恢复该修改所需的信息
4. COMMIT 时按配置确保 redo 达到持久化要求
5. 后台线程稍后把脏页刷入数据文件

先顺序写日志、后异步刷随机数据页的思想叫 WAL（Write-Ahead Logging）。

2.4 redo、undo、binlog 不要混

对象	所属层	主要目的	核心问题
redo log	InnoDB	崩溃恢复、持久性	“提交后宕机，修改还能恢复吗？”
undo log	InnoDB	回滚、MVCC 旧版本	“能撤销吗？快照读看哪个旧版本？”
binary log	Server	复制、审计链路、时间点恢复	“发生过哪些逻辑变更？”

三个常见误区：

redo 不是业务 SQL 的完整文本。
undo 不会在提交瞬间全部删除；旧版本仍被活跃 Read View 需要时不能清理。
binlog 与 redo 目的不同，不能互相简单替代。

2.5 ACID 到底由谁保证

特性	含义	主要实现基础
原子性 Atomicity	全做或全不做	undo、事务控制
一致性 Consistency	约束与业务不变量保持成立	A、I、D、约束及正确业务逻辑共同保证
隔离性 Isolation	并发事务按规则互相隔离	MVCC、锁、隔离级别
持久性 Durability	已提交结果可恢复	redo、刷盘策略

原文常见说法“某两份日志直接保证一致性”过于简单。一致性是最终目标，数据库机制和应用逻辑都要参与。例如数据库能保证转账语句原子提交，但金额是否允许为负仍要靠约束和业务判断。

2.6 `innodb_flush_log_at_trx_commit`

该变量影响 redo 的提交刷盘策略：

值	简化含义	取舍
`1`	每次提交写入并刷到持久存储	默认，持久性最强
`2`	每次提交写到操作系统缓存，通常每秒刷盘	宕机风险较低，操作系统崩溃可能丢失
`0`	通常每秒写入并刷盘	性能优先，可能丢失最近一段事务

sql

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

不要为了跑分快而直接修改生产配置；还要结合硬件缓存、文件系统、复制与业务可接受的数据损失窗口。

2.7 快照读与当前读

这是理解“为什么别人锁住了，我普通 SELECT 仍能查”的关键。

读取方式	典型语句	读什么	是否加行锁
快照读	普通 `SELECT`	对当前事务可见的版本	通常不加
当前读	`SELECT ... FOR SHARE`	满足可见性规则的最新数据	S 锁
当前读	`SELECT ... FOR UPDATE`	满足可见性规则的最新数据	X 锁
当前读/写	`UPDATE`、`DELETE`	当前版本	X 锁

sql

-- 快照读
SELECT stock
FROM products
WHERE product_id = 201;

-- 当前读：准备修改，因此锁定
SELECT stock
FROM products
WHERE product_id = 201
FOR UPDATE;

FOR UPDATE 必须放在显式事务中才有持续意义。autocommit = 1 且没有显式事务时，语句结束就提交，锁也随即释放。

2.8 MVCC 依靠什么

MVCC（Multi-Version Concurrency Control）依赖三部分：

聚簇索引记录中的事务相关隐藏信息。
undo 形成的历史版本链。
Read View 判断哪个版本可见。

常见隐藏字段：

隐藏字段	含义
`DB_TRX_ID`	最近一次插入或修改该记录的事务 ID
`DB_ROLL_PTR`	指向 undo 中上一个版本
`DB_ROW_ID`	表没有合适聚簇键时，InnoDB 生成的隐藏行 ID

版本链可以这样理解：

text

当前版本：stock = 98，DB_TRX_ID = 30
    ↓ DB_ROLL_PTR
旧版本：  stock = 99，DB_TRX_ID = 25
    ↓
更旧版本：stock = 100，DB_TRX_ID = 18

普通查询从新到旧检查：这个版本对我的 Read View 可见吗？ 可见就返回，不可见就继续沿 undo 找。

2.9 Read View 用人话理解

Read View 记录快照创建时：

哪些事务仍处于活跃、未提交状态；
活跃事务 ID 的边界；
创建 Read View 的事务是谁。

判断思路不是背四个字段，而是回答三个问题：

这是我自己在事务里改的版本吗？是，则自己可见。
产生这个版本的事务在快照建立前已经提交了吗？是，则可见。
它在快照建立后才开始，或当时仍未提交吗？是，则不可见，继续找旧版本。

需要识别的字段名：

字段	复习理解
`creator_trx_id`	创建者事务 ID
`m_ids`	创建快照时仍活跃的事务 ID 集合
`min_trx_id`	活跃事务的最小 ID
`max_trx_id`	下一批事务 ID 的上界标记

2.10 `READ COMMITTED` 与 `REPEATABLE READ`

隔离级别	普通 `SELECT` 的 Read View	直观结果
`READ COMMITTED`（RC）	每次快照读建立新视图	同一事务后一次查询可看见别人新提交的数据
`REPEATABLE READ`（RR）	通常首次一致性读建立，后续复用	同一事务的普通查询保持可重复

双终端实验：RC

终端 A：

text

mysql(A)> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK

mysql(A)> START TRANSACTION;
Query OK

mysql(A)> SELECT stock FROM products WHERE product_id = 201;
+-------+
| stock |
+-------+
|   100 |
+-------+

终端 B：

text

mysql(B)> UPDATE products SET stock = 99 WHERE product_id = 201;
Query OK, 1 row affected

终端 A 再查：

text

mysql(A)> SELECT stock FROM products WHERE product_id = 201;
+-------+
| stock |
+-------+
|    99 |
+-------+

mysql(A)> ROLLBACK;

RC 每次普通查询建立新快照，所以第二次看见了 B 已提交的 99。

双终端实验：RR

先恢复数据：

sql

UPDATE products SET stock = 100 WHERE product_id = 201;

终端 A：

text

mysql(A)> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK

mysql(A)> START TRANSACTION;
Query OK

mysql(A)> SELECT stock FROM products WHERE product_id = 201;
+-------+
| stock |
+-------+
|   100 |
+-------+

终端 B：

text

mysql(B)> UPDATE products SET stock = 99 WHERE product_id = 201;
Query OK, 1 row affected

终端 A：

text

mysql(A)> SELECT stock FROM products WHERE product_id = 201;
+-------+
| stock |
+-------+
|   100 |
+-------+

mysql(A)> SELECT stock FROM products
    -> WHERE product_id = 201 FOR UPDATE;
+-------+
| stock |
+-------+
|    99 |
+-------+

mysql(A)> ROLLBACK;

为什么普通查询是 100，FOR UPDATE 却是 99？

普通查询是快照读，复用 A 先前的 Read View。
FOR UPDATE 是当前读，要基于较新的可用版本加锁。

2.11 MVCC 不等于“没有并发问题”

MVCC 主要让快照读减少与写操作的冲突，它不自动解决所有业务竞态。

错误的库存扣减思路：

sql

-- 应用先查到 stock = 1
SELECT stock FROM products WHERE product_id = 201;

-- 之后再更新，期间可能已有别人修改
UPDATE products SET stock = 0 WHERE product_id = 201;

更可靠的原子条件更新：

sql

UPDATE products
SET stock = stock - 1
WHERE product_id = 201
  AND stock >= 1;

应用必须检查受影响行数：

text

1 row affected  -> 扣减成功
0 rows affected -> 库存不足或商品不存在

需要“先读后做复杂决策”时，在短事务中使用 SELECT ... FOR UPDATE，并尽快提交。

3. 索引：从 B+Tree 到联合索引

3.1 索引解决什么问题

索引是存储引擎维护的有序数据结构，用额外空间和写入成本换取更少的扫描。

没有合适索引：

text

WHERE order_no = 'O20260725001'

orders: [1001] → [1002] → [1003] → ... → [1006]
         逐行检查

有唯一索引：

text

B+Tree 定位 order_no
        ↓
找到二级索引记录和主键 order_id
        ↓
需要其他列时，再按主键取整行

索引的代价：

占用磁盘和 Buffer Pool。
INSERT、UPDATE、DELETE 要同步维护。
索引越多，优化器选择和统计信息维护也越复杂。

所以正确目标不是“让所有列都有索引”，而是：

让重要查询扫描更少的数据，同时控制写入与存储成本。

3.2 为什么常用 B+Tree

B+Tree 适合数据库页式存储：

一个节点能容纳许多键和子指针，树高较低。
非叶子节点主要承担导航，可容纳更多分支。
数据按键值顺序存在叶子层，适合等值、范围、排序与分组。
InnoDB 索引叶子页按顺序组织，相邻页之间有链接，便于范围扫描。

对比：

结构	等值	范围	排序	典型使用
B+Tree	好	好	好	InnoDB 普通索引
Hash	好	不适合	不支持有序扫描	Memory 默认索引、InnoDB 自适应机制
Full-text	关键词检索	非普通范围	非普通排序	大段文本搜索
Spatial	空间关系	空间范围	非普通排序	GIS 数据

不要把 InnoDB 的自适应哈希索引理解成可手工创建的普通 Hash 索引；它由引擎按访问模式自动管理。

3.3 聚簇索引与二级索引

InnoDB 表本质上是按聚簇索引组织的索引组织表。

聚簇索引

叶子记录保存完整行：

text

PRIMARY(order_id)

[1001 | 整行数据] [1002 | 整行数据] ... [1006 | 整行数据]

聚簇索引选择顺序：

显式主键。
没有主键时，选择合适的非空唯一索引。
都没有时，InnoDB 生成隐藏行 ID。

二级索引

叶子记录通常保存“二级索引键 + 主键值”：

text

uk_orders_no(order_no)

['O20260720001' | 1001]
['O20260721001' | 1002]
...

查询其他列时：

text

二级索引找到 order_id = 1006
             ↓
聚簇索引按 1006 找到完整行

这个二次定位过程叫回表。

但不要背成“二级索引一定慢”：

查询列全在二级索引里时可以直接覆盖。
页是否已在 Buffer Pool、返回多少行、数据分布等都会影响实际耗时。

3.4 主键为什么宜短、稳定、尽量有序

InnoDB 二级索引叶子会携带主键，因此主键过大会放大所有二级索引。

好的主键通常具备：

唯一且非空；
尽量短；
业务生命周期内不修改；
插入值大体递增，减少随机页访问和页分裂。

sql

CREATE TABLE good_pk (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_no VARCHAR(32) NOT NULL,
    UNIQUE KEY uk_good_pk_business_no (business_no)
) ENGINE = InnoDB;

不要绝对化：

UUID v4 随机且 36 字符文本较大，直接做聚簇主键通常不理想。
有序 UUID、UUID v7、ULID 等能改善插入局部性，但仍比 BIGINT 大。
分布式系统是否能使用自增主键取决于 ID 生成方案，不能只看数据库单机性能。

3.5 页分裂与页合并

顺序插入通常追加到 B+Tree 右侧；随机插入可能命中中间已满页：

text

目标页已满
   ↓
申请新页
   ↓
移动部分记录
   ↓
调整父节点与叶子页链接

这叫页分裂，会增加写放大和碎片。

删除记录时，InnoDB 通常先做删除标记；之后由 purge 清理。页利用率过低且相邻页可合并时，可能发生页合并。它们是理解主键有序性的机制，不是要求应用自行控制每一页。

3.6 索引分类与基本语法

分类	约束能力	数量
主键索引	唯一、非空、标识行	每表一个
唯一索引	保证索引键唯一，通常允许 `NULL`	可多个
普通索引	加速访问，不保证唯一	可多个
全文索引	文本关键词搜索	可多个

下面是语法对照。初始化脚本已经创建了这两个索引，不要在同一练习库里重复执行；要动手练习，请先改索引名或在副本表上操作。

sql

-- 普通联合索引
CREATE INDEX idx_products_category_price
ON products (category, price);

-- 唯一索引
CREATE UNIQUE INDEX uk_products_sku
ON products (sku);

-- 查看
SHOW INDEX FROM products;

-- 删除
DROP INDEX idx_products_category_price ON products;

练习库已经创建索引，可用下面的查询得到更紧凑的结果：

sql

SELECT index_name, seq_in_index, column_name, non_unique
FROM information_schema.statistics
WHERE table_schema = 'mysql_advanced_review'
  AND table_name = 'orders'
ORDER BY index_name, seq_in_index;

text

+------------------------------------+--------------+-------------+------------+
| index_name                         | seq_in_index | column_name | non_unique |
+------------------------------------+--------------+-------------+------------+
| idx_orders_created                 |            1 | created_at  |          1 |
| idx_orders_created                 |            2 | order_id    |          1 |
| idx_orders_customer_status_created |            1 | customer_id |          1 |
| idx_orders_customer_status_created |            2 | status      |          1 |
| idx_orders_customer_status_created |            3 | created_at  |          1 |
| idx_orders_customer_status_created |            4 | order_id    |          1 |
| PRIMARY                            |            1 | order_id    |          0 |
| uk_orders_no                       |            1 | order_no    |          0 |
+------------------------------------+--------------+-------------+------------+
8 rows in set

3.7 联合索引不是多个单列索引的拼接

索引：

text

(customer_id, status, created_at, order_id)

排序关系近似：

text

先按 customer_id
  相同客户内按 status
    相同状态内按 created_at
      时间相同再按 order_id

因此能高效定位的前缀是：

text

(customer_id)
(customer_id, status)
(customer_id, status, created_at)
(customer_id, status, created_at, order_id)

3.8 最左前缀规则

完整利用前两列

sql

SELECT order_id, created_at
FROM orders
WHERE customer_id = 101
  AND status = 'PAID';

跳过首列

sql

SELECT order_id
FROM orders
WHERE status = 'PAID';

status 不是联合索引首列，通常不能依靠该索引进行高效的普通前缀定位。优化器在特定数据分布下可能采用其他策略，例如跳跃扫描，但不能把它当成通用保证。

中间断层

sql

SELECT order_id
FROM orders
WHERE customer_id = 101
  AND created_at >= '2026-07-01';

能先按 customer_id 定位；由于缺少 status，无法继续把 created_at 当作连续索引前缀来缩小搜索区间。

`WHERE` 书写顺序不重要

下面两条对索引含义相同，优化器会重排条件：

sql

WHERE customer_id = 101 AND status = 'PAID'

sql

WHERE status = 'PAID' AND customer_id = 101

“最左”指索引定义顺序，不是 SQL 文本里谁写在左边。

3.9 联合索引遇到范围条件

sql

SELECT order_id, created_at
FROM orders
WHERE customer_id = 101
  AND status = 'PAID'
  AND created_at >= '2026-07-01'
  AND order_id > 1000;

理解成两层：

customer_id、status 的等值条件和 created_at 范围可用于确定扫描区间。
范围列右侧的 order_id 通常不能继续缩小 B+Tree 的起止区间，但仍可能参与索引条件下推、覆盖或结果过滤。

不要背“> 一定失效，而 >= 一定不失效”。实际可用键部分与边界构造由数据类型、条件组合和优化器版本决定，必须看执行计划。

3.10 常见低效场景

1. 在索引列上做函数或计算

sql

-- 较难直接使用 created_at 的普通索引定位
SELECT order_id
FROM orders
WHERE DATE(created_at) = '2026-07-20';

优先改成范围：

sql

SELECT order_id
FROM orders
WHERE created_at >= '2026-07-20 00:00:00'
  AND created_at <  '2026-07-21 00:00:00';

确实长期按表达式查询时，可评估函数索引：

sql

CREATE INDEX idx_orders_created_date
ON orders ((DATE(created_at)));

索引同样有写入和空间成本，不要为一次临时查询创建。

2. 隐式类型转换

sql

-- sku 是 VARCHAR，不应传数值
SELECT *
FROM products
WHERE sku = 201;

正确：

sql

SELECT *
FROM products
WHERE sku = '201';

隐式转换是否导致索引不可用取决于转换方向和表达式，但最佳实践始终是：参数类型与列类型一致。

3. 前导通配符

sql

-- B+Tree 通常可利用固定前缀
WHERE product_name LIKE '机械%';

-- 无法从字符串开头定位普通 B+Tree
WHERE product_name LIKE '%键盘';
WHERE product_name LIKE '%键%';

大量任意位置文本检索应评估全文索引或搜索引擎，不要靠 %关键词% 扫大表。

4. `OR`

sql

SELECT *
FROM orders
WHERE order_no = 'O20260725001'
   OR status = 'PAID';

不能简单断言“只要一边无索引，所有索引都失效”。优化器可能：

使用 Index Merge；
拆成多个索引访问；
判断全表扫描更便宜。

高频复杂 OR 可以评估改写为 UNION ALL，但要处理重复行，并用执行计划验证。

5. 低选择性或返回大部分行

即使有索引，查询大量数据时优化器也可能选择全表扫描：

sql

SELECT *
FROM orders
WHERE status <> 'CANCELLED';

这不是“索引突然坏了”，而是优化器认为大量回表比顺序扫描更贵。

6. `IS NULL`

IS NULL 和 IS NOT NULL 并非天然不走索引。索引可保存 NULL，最终方案取决于选择性、数据分布和成本。

3.11 覆盖索引

查询需要的列全部能从某个索引取得，就不必为结果列回表。

sql

SELECT order_id, status, created_at
FROM orders
WHERE customer_id = 101
  AND status = 'PAID'
ORDER BY created_at;

这些列都在：

text

(customer_id, status, created_at, order_id)

典型执行计划会显示覆盖索引访问，传统格式的 Extra 常出现 Using index。

若查询增加 remark：

sql

SELECT order_id, status, created_at, remark
FROM orders
WHERE customer_id = 101
  AND status = 'PAID';

remark 不在索引中，符合条件的行通常需要回表。

不要为了覆盖所有 SELECT * 把整张表塞进索引；宽索引会显著增加空间和写放大。

3.12 `Using index` 与 `Using index condition`

这两个最容易被混为一谈：

`Extra`	正确理解
`Using index`	查询可从索引本身取得所需列，通常代表覆盖访问
`Using index condition`	使用 ICP，在存储引擎读取完整行前先用索引列过滤

ICP（Index Condition Pushdown）的价值：

text

先扫描二级索引
    ↓
在索引层判断可下推的 WHERE 条件
    ↓
只有通过的候选项才读取聚簇索引记录

Using index condition 的含义不是简单的“已经回表”，而是“部分条件被推到存储引擎索引层执行”；符合条件的候选项是否还需读取完整行，要结合查询列与完整计划判断。

3.13 前缀索引

长字符串可以只索引开头若干字符：

sql

CREATE INDEX idx_customers_email_prefix
ON customers (email(10));

选择前缀长度时查看区分度：

sql

SELECT
    COUNT(DISTINCT email) / COUNT(*) AS full_selectivity,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_selectivity
FROM customers;

代价：

前缀重复越多，需要扫描的候选项越多。
前缀索引不能覆盖完整原列值。
某些排序和分组无法仅靠前缀索引完成。

邮箱通常可直接建立完整唯一索引；前缀索引更适合确实很长且不要求完整唯一性的字符串。

3.14 单列索引还是联合索引

如果核心查询总是：

sql

WHERE customer_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 20

联合索引：

sql

(customer_id, status, created_at)

通常比三个孤立单列索引更贴合访问路径。

但也不要背“联合索引永远优于单列索引”：

只按 status 查询时，上述联合索引并不理想。
不同查询模式可能需要不同索引。
MySQL 可使用 Index Merge，但它不等于专门设计的联合索引一定多余。

3.15 索引提示与不可见索引

sql

SELECT *
FROM orders USE INDEX (idx_orders_created)
WHERE created_at >= '2026-07-20';

SELECT *
FROM orders FORCE INDEX (idx_orders_created)
WHERE created_at >= '2026-07-20';

SELECT *
FROM orders IGNORE INDEX (idx_orders_created)
WHERE created_at >= '2026-07-20';

索引提示是最后手段：

先确认统计信息是否过旧。
检查 SQL 和索引设计。
用真实数据量验证。
最后才考虑提示，因为数据分布变化后提示可能反而变慢。

测试“删掉索引会怎样”时，可先把索引设为不可见：

sql

ALTER TABLE orders
ALTER INDEX idx_orders_created INVISIBLE;

-- 验证后恢复
ALTER TABLE orders
ALTER INDEX idx_orders_created VISIBLE;

3.16 索引设计清单

创建索引前依次问：

这是高频且重要的查询吗？
WHERE、JOIN、ORDER BY、GROUP BY 的访问顺序是什么？
联合索引首列是否能明显缩小范围？
是否能兼顾过滤和排序？
是否需要少量附加列形成合理覆盖？
是否与已有索引重复？
写入成本和磁盘成本可接受吗？
在接近生产的数据量与分布下，EXPLAIN ANALYZE 真的更好吗？

4. 性能分析与 `EXPLAIN`

4.1 正确优化流程

不要先猜索引。标准流程是：

text

找到真实慢 SQL
   ↓
确认调用次数、总耗时与业务影响
   ↓
查看 EXPLAIN / EXPLAIN ANALYZE
   ↓
判断慢在扫描、连接、排序、锁等待还是返回过多
   ↓
改 SQL / 索引 / 数据模型
   ↓
用真实参数和数据量复测

“单次慢但一天执行一次”和“每次 20 ms 但每秒执行几千次”都可能值得优化，不能只看单次耗时。

4.2 全局语句频次

sql

SHOW GLOBAL STATUS
WHERE variable_name IN
    ('Com_select', 'Com_insert', 'Com_update', 'Com_delete');

它能粗略观察实例读写比例，但不能告诉你具体哪条 SQL 最耗资源。

4.3 慢查询日志

查看配置：

sql

SHOW VARIABLES
WHERE variable_name IN (
    'slow_query_log',
    'slow_query_log_file',
    'long_query_time',
    'log_output'
);

临时调整示例，需要管理权限：

sql

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

注意：

SET GLOBAL 通常只影响后续连接或运行期，持久化方式取决于部署。
生产开启日志前要评估磁盘、轮转和隐私脱敏。
log_queries_not_using_indexes 在某些负载中会产生大量噪声，不能替代耗时分析。

4.4 Performance Schema 找“总成本最高”的 SQL

sql

SELECT
    digest_text,
    count_star,
    ROUND(sum_timer_wait / 1000000000000, 3) AS total_seconds,
    ROUND(avg_timer_wait / 1000000000, 3) AS avg_ms,
    sum_rows_examined,
    sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'mysql_advanced_review'
ORDER BY sum_timer_wait DESC
LIMIT 10;

重点观察：

count_star：调用频率；
total_seconds：累计成本；
avg_ms：平均耗时；
sum_rows_examined / sum_rows_sent：扫描很多却返回很少，常提示访问路径有问题。

旧的 SHOW PROFILE(S) 不适合作为 MySQL 8 的主要调优方法；优先使用 Performance Schema 与 EXPLAIN ANALYZE。

4.5 `EXPLAIN` 看估算，`EXPLAIN ANALYZE` 看实测

sql

EXPLAIN
SELECT order_id, created_at
FROM orders
WHERE customer_id = 101
  AND status = 'PAID'
ORDER BY created_at DESC;

树形格式更容易读：

sql

EXPLAIN FORMAT = TREE
SELECT order_id, created_at
FROM orders
WHERE customer_id = 101
  AND status = 'PAID'
ORDER BY created_at DESC;

实际执行并记录迭代器时间：

sql

EXPLAIN ANALYZE
SELECT order_id, created_at
FROM orders
WHERE customer_id = 101
  AND status = 'PAID'
ORDER BY created_at DESC;

典型输出形态：

text

-> Covering index lookup on orders
   using idx_orders_customer_status_created
   (customer_id=101, status='PAID')
   (actual time=... rows=... loops=1)

具体文字、成本和时间会随版本、数据量与统计信息变化。

EXPLAIN ANALYZE 会真正执行语句。对大查询要评估负载；对可修改数据的语句更要谨慎，不要把它当成无副作用的静态分析。

4.6 传统 `EXPLAIN` 重点列

列	看什么	不要误解
`id` / `select_type`	查询块和子查询类型	不能只按 `id` 背固定执行顺序
`type`	表访问方式	`ALL` 在极小表上可能合理
`possible_keys`	候选索引	有候选不代表会选
`key`	实际选用索引	`NULL` 也可能是常量查询或无需索引
`key_len`	计划使用的索引键长度	不是实际读取字节，也不是越短越好
`ref`	与索引比较的值或列	要结合 `key` 看
`rows`	预计扫描行数	是估算，不等于真实行数
`filtered`	读取后预计保留百分比	不是简单“越大越好”
`Extra`	覆盖、排序、临时表、ICP 等	单个关键词不能直接判定好坏

4.7 `type` 访问方式怎么记

常见访问方式从更精确到更宽泛，大致为：

text

const → eq_ref → ref → range → index → ALL

`type`	常见含义
`const`	主键或唯一键等值，最多一行
`eq_ref`	连接时按主键/唯一键精确匹配一行
`ref`	普通索引等值，可能多行
`range`	索引范围扫描
`index`	扫完整棵索引
`ALL`	全表扫描

它只是线索，不是成绩单。扫描 4 行小表的 ALL 可能比绕索引更合理；真正要看扫描量、循环次数、回表、排序和总耗时。

4.8 `Extra` 高频词

`Extra`	含义
`Using index`	覆盖索引访问
`Using index condition`	使用索引条件下推 ICP
`Using where`	Server 层仍需判断条件
`Using filesort`	需要额外排序，名称不代表一定写磁盘
`Using temporary`	使用内部临时表完成某些分组、去重等操作
`Backward index scan`	反向扫描索引

Using filesort 或 Using temporary 不一定是故障。返回几十行的小查询完全可能足够快；优化应由实际成本驱动。

4.9 估算与实际差距很大怎么办

sql

ANALYZE TABLE orders;

然后重新比较：

sql

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'PAID';

还可考虑：

数据是否高度倾斜；
参数值是否差异很大；
统计信息是否代表当前数据；
是否需要直方图；
查询是否一次返回过多列或行。

直方图示例：

sql

ANALYZE TABLE orders
UPDATE HISTOGRAM ON status WITH 16 BUCKETS;

只在确认列分布估算确有问题时使用；不要给每个列无差别创建直方图。

4.10 一次执行计划复盘

查询：

sql

SELECT order_id, created_at
FROM orders
WHERE customer_id = 101
  AND status = 'PAID'
  AND created_at >= '2026-07-01'
ORDER BY created_at DESC
LIMIT 20;

复盘顺序：

key 是否为 idx_orders_customer_status_created？
是否按 customer_id + status + created_at 做范围定位？
预计和实际读取多少行？
是否覆盖，无需回表？
索引顺序能否直接满足排序？
LIMIT 20 是否让执行器提前停止？

这比只盯着 type = range 更接近真实调优。

5. SQL 优化

5.1 总原则

优化 SQL 的优先级通常是：

少读不需要的行。
少返回不需要的列。
让过滤、连接、排序尽量使用合适索引。
缩短事务，减少锁持有时间。
批量处理，减少网络往返和提交次数。
最后才是微调内存参数或强制索引。

5.2 批量插入

低效：客户端逐条发送，若开启自动提交还会产生多次提交。下面三条执行后用 DELETE 清理。

sql

INSERT INTO products
    (product_id, sku, product_name, category,
     price, stock, status, created_at)
VALUES
    (301, 'KB-301', '键盘 A', '外设',
     199.00, 10, 'ACTIVE', NOW());

INSERT INTO products
    (product_id, sku, product_name, category,
     price, stock, status, created_at)
VALUES
    (302, 'KB-302', '键盘 B', '外设',
     299.00, 20, 'ACTIVE', NOW());

INSERT INTO products
    (product_id, sku, product_name, category,
     price, stock, status, created_at)
VALUES
    (303, 'KB-303', '键盘 C', '外设',
     399.00, 30, 'ACTIVE', NOW());

DELETE FROM products
WHERE product_id IN (301, 302, 303);

更好：一次发送多行。该组与上组是二选一的对照实验。

sql

INSERT INTO products
    (product_id, sku, product_name, category,
     price, stock, status, created_at)
VALUES
    (301, 'KB-301', '键盘 A', '外设', 199.00, 10, 'ACTIVE', NOW()),
    (302, 'KB-302', '键盘 B', '外设', 299.00, 20, 'ACTIVE', NOW()),
    (303, 'KB-303', '键盘 C', '外设', 399.00, 30, 'ACTIVE', NOW());

DELETE FROM products
WHERE product_id IN (301, 302, 303);

大量数据可分成可控批次，并减少提交次数。这里用 ROLLBACK 结束，只演示写法：

sql

START TRANSACTION;

INSERT INTO products
    (product_id, sku, product_name, category,
     price, stock, status, created_at)
VALUES
    (311, 'MS-311', '鼠标 A', '外设', 99.00, 50, 'ACTIVE', NOW()),
    (312, 'MS-312', '鼠标 B', '外设', 159.00, 40, 'ACTIVE', NOW());

INSERT INTO products
    (product_id, sku, product_name, category,
     price, stock, status, created_at)
VALUES
    (313, 'MS-313', '鼠标 C', '外设', 239.00, 30, 'ACTIVE', NOW()),
    (314, 'MS-314', '鼠标 D', '外设', 329.00, 20, 'ACTIVE', NOW());

ROLLBACK;

不要把几百万行塞进一条巨大 SQL；受 max_allowed_packet、undo、redo、锁持有时间和失败重试成本影响，应按可控批次处理。

5.3 `LOAD DATA` 导入大文件

sql

LOAD DATA LOCAL INFILE '/path/to/products.csv'
INTO TABLE products
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(product_id, sku, product_name, category,
 price, stock, status, created_at);

客户端需允许本地文件：

bash

mysql --local-infile=1 -u review_user -p mysql_advanced_review

安全提醒：

LOCAL 表示客户端读取文件并上传，客户端和服务器都可能有开关限制。
不要为了导入临时文件就在生产环境永久放开不必要权限。
先验证编码、换行、转义、列顺序和错误行处理。

5.4 主键插入顺序

大体递增的聚簇键通常具有更好的页局部性；随机大键可能导致更多中间页访问和页分裂。

但优化目标不是“业务必须暴露连续自增 ID”。可使用：

数据库自增主键 + 独立业务编号；
分布式趋势递增 ID；
有序 UUID 类方案。

核心是让聚簇键短、稳定，并评估写入分布。

5.5 `ORDER BY` 优化

查询：

sql

SELECT order_id, created_at
FROM orders
WHERE customer_id = 101
  AND status = 'PAID'
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

索引：

text

(customer_id, status, created_at, order_id)

前两列已固定，后两列按相同方向倒序，MySQL 可反向扫描索引，通常不需额外排序。

若排序方向混合且非常重要：

sql

ORDER BY created_at DESC, order_id ASC

可评估与之匹配的降序索引：

sql

CREATE INDEX idx_orders_timeline_mixed
ON orders (customer_id, status, created_at DESC, order_id ASC);

不要见到 Using filesort 就立即加索引：

filesort 是额外排序算法名称，不等于必然写磁盘。
小结果集排序很便宜。
新增索引可能比排序本身更贵。

5.6 `GROUP BY` 优化

sql

SELECT customer_id, status, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, status;

分组顺序与联合索引前缀一致时，可能减少额外排序或临时结构。

只按 status 分组：

sql

SELECT status, COUNT(*)
FROM orders
GROUP BY status;

现有 (customer_id, status, ...) 不能直接提供按 status 聚集的顺序。是否单独创建 status 索引，要看频率、表规模、选择性和写入成本。

5.7 深分页

偏移分页：

sql

SELECT order_id, created_at
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 200000, 20;

问题：即使最终只返回 20 行，也要定位并跳过前面大量记录。

方案一：游标/Seek 分页

上一页最后一条为：

text

created_at = '2026-07-21 11:20:00'
order_id   = 1002

sql

SELECT order_id, created_at
FROM orders
WHERE created_at < '2026-07-21 11:20:00'
   OR (
       created_at = '2026-07-21 11:20:00'
       AND order_id < 1002
   )
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

优点：复杂度不随页码线性上升。
限制：不适合直接跳到任意第 N 页；排序必须稳定，最后加唯一键作为兜底。

方案二：延迟关联

sql

SELECT o.*
FROM orders AS o
JOIN (
    SELECT order_id
    FROM orders
    ORDER BY created_at DESC, order_id DESC
    LIMIT 200000, 20
) AS page USING (order_id)
ORDER BY o.created_at DESC, o.order_id DESC;

内层尽量只扫描窄的覆盖索引，最后对 20 个主键回表。它仍需跳过偏移，只是降低每条记录的代价；能用 Seek 分页时通常优先 Seek。

5.8 `COUNT` 的正确理解

写法	统计什么
`COUNT(*)`	结果集行数
`COUNT(1)`	常量非空，因此也是结果集行数
`COUNT(column)`	该列非 `NULL` 的行数
`COUNT(DISTINCT column)`	非 `NULL` 的不同值数

sql

SELECT COUNT(*)
FROM orders
WHERE customer_id = 101;

实践：

统计行数优先写语义清楚的 COUNT(*)。
InnoDB 不为一般查询维护可直接返回的精确总行数。
COUNT(*) 与 COUNT(1) 的微小差别不要靠口诀判断，应以版本和执行计划为准。
超大表高频精确计数可维护汇总表，但必须设计一致性、补偿和重算机制。

5.9 `UPDATE`：先缩小扫描，再缩短事务

推荐按主键或高选择性索引更新：

sql

UPDATE products
SET price = 379.00
WHERE product_id = 201;

无索引条件：

sql

UPDATE products
SET status = 'INACTIVE'
WHERE product_name = '机械键盘';

InnoDB 的行锁落实在索引记录上。没有合适索引时，为找目标可能扫描并锁住大量索引记录，效果看起来像“整表都被锁”，但这不应简单称为“行锁升级成表锁”。第 8 章会用双终端验证。

5.10 库存扣减：把检查和修改合成一条

sql

UPDATE products
SET stock = stock - 3
WHERE product_id = 201
  AND stock >= 3
  AND status = 'ACTIVE';

text

Query OK, 1 row affected -> 成功
Query OK, 0 rows affected -> 不足、下架或不存在

这比“先普通查询库存、应用判断、再更新”更能抵抗并发竞态。

5.11 乐观锁

账户表有 version：

sql

SELECT balance, version
FROM accounts
WHERE account_id = 1;

假设应用读到 version = 0：

sql

UPDATE accounts
SET balance = 4900.00,
    version = version + 1
WHERE account_id = 1
  AND version = 0;

若受影响行数为 0，说明期间被其他事务修改，应重新读取并按业务策略重试。乐观锁不是 InnoDB 的一种物理锁，而是应用利用条件更新检测冲突。

5.12 批量更新与删除

大事务会带来：

大量 undo/redo；
长时间持锁；
复制延迟；
回滚时间长；
旧版本不能及时 purge。

按主键范围分批：

sql

DELETE FROM order_audit
WHERE audit_id < 100000
ORDER BY audit_id
LIMIT 5000;

循环批次时记录进度、限速并监控复制与锁等待。删除前先确认索引，否则每一批仍可能反复扫描大表。

5.13 JOIN 优化

sql

SELECT
    o.order_id,
    c.customer_name,
    o.total_amount
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE o.created_at >= '2026-07-01';

检查：

连接列数据类型一致。
被查找一侧的连接列有索引；这里 customers.customer_id 是主键。
驱动表过滤是否足够早。
不要返回无用大字段。
用 EXPLAIN ANALYZE 看每层 loops × rows，嵌套循环中小误差会被放大。

5.14 一张 SQL 优化检查表

text

[ ] 是否只查需要的列，而不是无脑 SELECT *？
[ ] WHERE / JOIN 是否让扫描范围足够小？
[ ] 联合索引顺序是否匹配核心访问路径？
[ ] 是否发生大量回表？
[ ] 排序与分组真的需要新增索引吗？
[ ] 深分页能否改 Seek？
[ ] 是否把多次网络往返合并成批量操作？
[ ] 事务是否过大、持锁是否过久？
[ ] 优化前后是否用同一批真实参数复测？
[ ] 是否观察了总耗时，而不只看单次耗时？

6. 视图、存储过程、函数与触发器

6.1 先判断逻辑该放哪里

能力	更适合的场景	主要风险
视图	封装稳定查询、限制暴露列	层层嵌套后难优化
存储过程	数据库内批处理、减少往返、集中事务逻辑	版本管理、测试、跨库迁移较难
存储函数	小型、确定性的值转换	被逐行调用时可能放大成本
触发器	强制审计、非常靠近数据的规则	隐式副作用，不易排查
应用代码	复杂业务流程、外部服务协作	需正确处理事务、重试和并发

优先写集合式 SQL。不要因为学了循环和游标，就把数据库当通用编程语言使用。

6.2 视图是什么

普通视图保存查询定义，不保存查询结果：

sql

CREATE OR REPLACE
SQL SECURITY INVOKER
VIEW v_order_summary AS
SELECT
    o.order_id,
    o.order_no,
    c.customer_name,
    o.status,
    o.total_amount,
    o.created_at
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id;

查询视图：

sql

SELECT order_id, customer_name, total_amount
FROM v_order_summary
WHERE status = 'PAID'
ORDER BY created_at DESC;

text

+----------+---------------+--------------+
| order_id | customer_name | total_amount |
+----------+---------------+--------------+
|     1006 | 张伟          |       199.00 |
|     1003 | 李娜          |       398.00 |
|     1001 | 张伟          |       528.00 |
+----------+---------------+--------------+
3 rows in set

查看与删除：

sql

SHOW CREATE VIEW v_order_summary\G

DROP VIEW IF EXISTS v_order_summary;

6.3 视图的三个实际作用

简化查询：把稳定的连接和派生列集中定义。
限制暴露：只授权用户访问视图中的部分列。
隔离变化：在一定范围内屏蔽底层表结构调整。

视图不是绝对安全边界。还要考虑：

DEFINER 是否存在；
SQL SECURITY DEFINER 还是 INVOKER；
用户是否同时拥有基表权限；
视图定义是否泄露敏感数据。

复习环境优先显式写 SQL SECURITY INVOKER，让调用者按自身权限执行。

6.4 可更新视图与 `CHECK OPTION`

简单单表视图可能可更新：

sql

CREATE OR REPLACE
SQL SECURITY INVOKER
VIEW v_active_products AS
SELECT
    product_id,
    sku,
    product_name,
    price,
    stock,
    status
FROM products
WHERE status = 'ACTIVE'
WITH CASCADED CHECK OPTION;

允许符合条件的更新：

sql

UPDATE v_active_products
SET price = 389.00
WHERE product_id = 201;

不允许更新后脱离视图条件：

sql

UPDATE v_active_products
SET status = 'INACTIVE'
WHERE product_id = 201;

text

ERROR 1369 (HY000):
CHECK OPTION failed 'mysql_advanced_review.v_active_products'

常见不可更新视图包含：

聚合函数或窗口函数；
DISTINCT；
GROUP BY / HAVING；
UNION；
无法与基表行形成明确一对一关系的结构。

CASCADED 会检查当前视图及所依赖视图的相关条件；LOCAL 只要求当前视图及依赖视图中显式要求检查的条件。没有特殊理由时，默认的 CASCADED 更不容易绕过约束。

6.5 `DELIMITER` 到底是什么

存储程序内部有许多分号，命令行客户端需要暂时更换“整段定义的结束符”：

sql

DELIMITER //

CREATE PROCEDURE sp_demo()
BEGIN
    SELECT 'hello';
    SELECT 'mysql';
END//

DELIMITER ;

DELIMITER 是 mysql 客户端命令，不是服务器 SQL 语法。某些 GUI 或驱动会自动处理，不应把它发送给只接受单条 SQL 的 API。

6.6 存储过程：输入、输出与局部变量

统计某客户已支付订单：

sql

DELIMITER //

CREATE PROCEDURE sp_order_summary(
    IN  p_customer_id INT UNSIGNED,
    OUT p_paid_count  INT,
    OUT p_paid_amount DECIMAL(12, 2)
)
READS SQL DATA
BEGIN
    SELECT
        COUNT(*),
        COALESCE(SUM(total_amount), 0)
    INTO
        p_paid_count,
        p_paid_amount
    FROM orders
    WHERE customer_id = p_customer_id
      AND status = 'PAID';
END//

DELIMITER ;

调用：

sql

SET @paid_count = 0;
SET @paid_amount = 0;

CALL sp_order_summary(101, @paid_count, @paid_amount);

SELECT @paid_count, @paid_amount;

text

+-------------+--------------+
| @paid_count | @paid_amount |
+-------------+--------------+
|           2 |       727.00 |
+-------------+--------------+
1 row in set

参数类型：

参数	含义
`IN`	调用者传入，默认类型
`OUT`	过程写入，调用者接收
`INOUT`	既传入又带回

6.7 三种变量

变量	示例	作用域
系统变量	`@@session.transaction_isolation`	会话或全局
用户变量	`@paid_count`	当前连接
局部变量	`DECLARE v_total INT`	当前 `BEGIN ... END` 块

sql

SELECT @@session.autocommit;
SELECT @@global.max_connections;

SET @customer_id = 101;
SELECT @customer_id;

局部变量必须在块开头按规则声明：

sql

DELIMITER //

CREATE PROCEDURE sp_local_variable()
BEGIN
    DECLARE v_order_count INT DEFAULT 0;

    SELECT COUNT(*)
    INTO v_order_count
    FROM orders;

    SELECT v_order_count;
END//

DELIMITER ;

存储程序中的声明顺序要记住：

text

局部变量 / 条件
    ↓
游标
    ↓
处理程序 Handler

6.8 `IF`、`CASE` 与 `SIGNAL`

sql

DELIMITER //

CREATE PROCEDURE sp_customer_level(
    IN  p_amount DECIMAL(12, 2),
    OUT p_level  VARCHAR(10)
)
NO SQL
BEGIN
    IF p_amount < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '消费金额不能为负数';
    ELSEIF p_amount >= 10000 THEN
        SET p_level = 'VIP';
    ELSE
        SET p_level = 'NORMAL';
    END IF;
END//

DELIMITER ;

SQLSTATE '45000' 常用于抛出用户定义业务异常。不要静默返回一个含糊值，让调用方误以为成功。

查询表达式中的 CASE：

sql

SELECT
    order_id,
    CASE status
        WHEN 'PENDING'   THEN '待支付'
        WHEN 'PAID'      THEN '已支付'
        WHEN 'SHIPPED'   THEN '已发货'
        WHEN 'CANCELLED' THEN '已取消'
        ELSE '未知'
    END AS status_name
FROM orders;

6.9 循环

结构	判断时机	适合
`WHILE`	先判断再执行	可能一次都不执行
`REPEAT`	先执行再判断退出	至少执行一次
`LOOP`	无内置条件	配合 `LEAVE` / `ITERATE`

清晰的 WHILE 示例：

sql

DELIMITER //

CREATE PROCEDURE sp_sum_to_n(
    IN  p_n     INT,
    OUT p_total BIGINT
)
NO SQL
BEGIN
    DECLARE v_i INT DEFAULT 1;

    IF p_n < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'n 不能为负数';
    END IF;

    SET p_total = 0;

    WHILE v_i <= p_n DO
        SET p_total = p_total + v_i;
        SET v_i = v_i + 1;
    END WHILE;
END//

DELIMITER ;

这里是语法演示。真实计算 1 + ... + n 应直接用数学公式，数据库循环并不是更优方案。

6.10 游标与 Handler

游标把查询结果逐行取出。标准顺序：

text

DECLARE CURSOR → DECLARE HANDLER → OPEN
→ FETCH → 判断结束 → CLOSE

先建快照表：

sql

CREATE TABLE IF NOT EXISTS vip_customer_snapshot (
    customer_id   INT UNSIGNED PRIMARY KEY,
    customer_name VARCHAR(30) NOT NULL,
    refreshed_at  DATETIME NOT NULL
) ENGINE = InnoDB;

完整示例：

sql

DELIMITER //

CREATE PROCEDURE sp_refresh_vip_snapshot()
MODIFIES SQL DATA
BEGIN
    DECLARE v_done          BOOLEAN DEFAULT FALSE;
    DECLARE v_customer_id   INT UNSIGNED;
    DECLARE v_customer_name VARCHAR(30);

    DECLARE cur_vip CURSOR FOR
        SELECT customer_id, customer_name
        FROM customers
        WHERE level = 'VIP'
        ORDER BY customer_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_done = TRUE;

    DELETE FROM vip_customer_snapshot;

    OPEN cur_vip;

    read_loop: LOOP
        FETCH cur_vip INTO v_customer_id, v_customer_name;

        IF v_done THEN
            LEAVE read_loop;
        END IF;

        INSERT INTO vip_customer_snapshot
            (customer_id, customer_name, refreshed_at)
        VALUES
            (v_customer_id, v_customer_name, NOW());
    END LOOP;

    CLOSE cur_vip;
END//

DELIMITER ;

调用：

sql

CALL sp_refresh_vip_snapshot();
SELECT * FROM vip_customer_snapshot;

text

+-------------+---------------+---------------------+
| customer_id | customer_name | refreshed_at        |
+-------------+---------------+---------------------+
|         101 | 张伟          | 2026-07-29 10:00:00 |
|         103 | 王强          | 2026-07-29 10:00:00 |
+-------------+---------------+---------------------+
2 rows in set

这个需求其实可用集合式 SQL 更简洁高效：

sql

DELETE FROM vip_customer_snapshot;

INSERT INTO vip_customer_snapshot
    (customer_id, customer_name, refreshed_at)
SELECT customer_id, customer_name, NOW()
FROM customers
WHERE level = 'VIP';

先想集合操作，只有确实需要逐行状态时才用游标。

6.11 事务型存储过程：转账

这个版本做了四件重要的事：

校验输入。
始终按较小账户 ID 到较大账户 ID 的顺序加锁。
异常时回滚并重新抛出。
检查余额后再更新。

sql

DELIMITER //

CREATE PROCEDURE sp_transfer(
    IN p_from_account INT UNSIGNED,
    IN p_to_account   INT UNSIGNED,
    IN p_amount       DECIMAL(12, 2)
)
MODIFIES SQL DATA
transfer: BEGIN
    DECLARE v_first        INT UNSIGNED;
    DECLARE v_second       INT UNSIGNED;
    DECLARE v_locked_id    INT UNSIGNED;
    DECLARE v_from_balance DECIMAL(12, 2);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION, NOT FOUND
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    IF p_amount <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '转账金额必须大于 0';
    END IF;

    IF p_from_account = p_to_account THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '转出和转入账户不能相同';
    END IF;

    SET v_first  = LEAST(p_from_account, p_to_account);
    SET v_second = GREATEST(p_from_account, p_to_account);

    START TRANSACTION;

    SELECT account_id
    INTO v_locked_id
    FROM accounts
    WHERE account_id = v_first
    FOR UPDATE;

    SELECT account_id
    INTO v_locked_id
    FROM accounts
    WHERE account_id = v_second
    FOR UPDATE;

    SELECT balance
    INTO v_from_balance
    FROM accounts
    WHERE account_id = p_from_account;

    IF v_from_balance < p_amount THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '余额不足';
    END IF;

    UPDATE accounts
    SET balance = balance - p_amount,
        version = version + 1
    WHERE account_id = p_from_account;

    UPDATE accounts
    SET balance = balance + p_amount,
        version = version + 1
    WHERE account_id = p_to_account;

    COMMIT;
END//

DELIMITER ;

测试：

sql

CALL sp_transfer(1, 2, 200.00);

SELECT account_id, balance, version
FROM accounts
ORDER BY account_id;

text

+------------+---------+---------+
| account_id | balance | version |
+------------+---------+---------+
|          1 | 4800.00 |       1 |
|          2 | 3200.00 |       1 |
+------------+---------+---------+
2 rows in set

恢复：

sql

UPDATE accounts
SET balance = CASE account_id
    WHEN 1 THEN 5000.00
    WHEN 2 THEN 3000.00
END,
version = 0
WHERE account_id IN (1, 2);

该过程自行开始并提交事务，调用它时不要再让外层应用假定自己能统一控制同一事务。真实项目要在“过程控制事务”和“应用控制事务”之间明确选一种边界。

6.12 存储函数

存储函数必须返回一个值，参数均为输入：

sql

DELIMITER //

CREATE FUNCTION fn_order_status_name(p_status VARCHAR(12))
RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL
RETURN CASE p_status
    WHEN 'PENDING'   THEN '待支付'
    WHEN 'PAID'      THEN '已支付'
    WHEN 'SHIPPED'   THEN '已发货'
    WHEN 'CANCELLED' THEN '已取消'
    ELSE '未知'
END//

DELIMITER ;

sql

SELECT
    order_id,
    fn_order_status_name(status) AS status_name
FROM orders;

特性声明：

声明	含义
`DETERMINISTIC`	相同输入产生相同输出
`NO SQL`	不访问 SQL 数据
`READS SQL DATA`	只读取数据
`MODIFIES SQL DATA`	修改数据，通常用于过程而非函数设计

函数可能对结果集每行调用一次。大量行上的复杂函数会放大成本，简单状态翻译也可放在查询 CASE 或应用展示层。

6.13 触发器与 `OLD` / `NEW`

操作	可用值
`INSERT`	`NEW.column`
`UPDATE`	`OLD.column`、`NEW.column`
`DELETE`	`OLD.column`

订单状态审计：

sql

DELIMITER //

CREATE TRIGGER trg_orders_status_audit
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF NOT (OLD.status <=> NEW.status) THEN
        INSERT INTO order_audit
            (order_id, old_status, new_status, changed_at)
        VALUES
            (NEW.order_id, OLD.status, NEW.status, NOW());
    END IF;
END//

DELIMITER ;

用事务验证触发器与原语句同生共死：

sql

START TRANSACTION;

UPDATE orders
SET status = 'PAID'
WHERE order_id = 1005;

SELECT order_id, old_status, new_status
FROM order_audit
WHERE order_id = 1005;

ROLLBACK;

事务内部能看到：

text

+----------+------------+------------+
| order_id | old_status | new_status |
+----------+------------+------------+
|     1005 | PENDING    | PAID       |
+----------+------------+------------+
1 row in set

回滚后：

sql

SELECT COUNT(*)
FROM order_audit
WHERE order_id = 1005;

text

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set

触发器要克制使用：

它会增加原 DML 的耗时。
失败会使原语句失败。
隐式写入容易被开发者忽略。
MySQL 是行级触发器，更新 10,000 行会触发 10,000 次。
外键级联动作不会像显式 DML 那样触发对应触发器，设计审计时必须注意。

查看与删除对象：

sql

SHOW TRIGGERS;
SHOW CREATE PROCEDURE sp_transfer\G
SHOW CREATE FUNCTION fn_order_status_name\G

DROP TRIGGER IF EXISTS trg_orders_status_audit;
DROP PROCEDURE IF EXISTS sp_transfer;
DROP FUNCTION IF EXISTS fn_order_status_name;

7. MySQL 管理与常用工具

7.1 四个系统库

数据库	作用
`mysql`	账户、权限、时区及服务器内部数据字典相关信息
`information_schema`	数据库、表、列、约束等元数据视图
`performance_schema`	运行期事件、等待、语句、锁和资源统计
`sys`	对 Performance Schema 的易读封装视图

示例：

sql

-- 查订单表索引
SELECT index_name, column_name, seq_in_index
FROM information_schema.statistics
WHERE table_schema = 'mysql_advanced_review'
  AND table_name = 'orders';

-- 查当前数据锁
SELECT object_schema, object_name, index_name,
       lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks;

7.2 `mysql` 客户端

连接：

bash

mysql -h 127.0.0.1 -P 3306 \
  -u review_user -p mysql_advanced_review

直接执行并退出：

bash

mysql -u review_user -p \
  -D mysql_advanced_review \
  -e "SELECT COUNT(*) FROM orders;"

不要写成：

bash

mysql -u root -pMyPlainTextPassword

命令行参数可能被进程列表或终端历史暴露。交互输入密码，或使用权限严格控制的客户端配置文件和密钥管理方案。

7.3 `mysqladmin`

bash

mysqladmin -u admin_user -p ping
mysqladmin -u admin_user -p status
mysqladmin -u admin_user -p version

它适合轻量管理和健康检查。删除数据库等破坏性命令必须在确认环境、实例和目标名称后执行。

7.4 `mysqldump` 一致性备份

InnoDB 逻辑备份：

bash

mysqldump -u backup_user -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --databases mysql_advanced_review \
  > mysql_advanced_review.sql

重点：

--single-transaction 通过一致性快照备份事务表，通常不阻塞普通业务写入。
它主要适用于 InnoDB 等事务表；混有非事务表时不能保证整体一致。
备份期间避免 DDL，否则表定义与数据的一致性仍可能受影响。
“备份命令执行成功”不等于“备份可恢复”，必须定期做恢复演练。

只备结构：

bash

mysqldump -u backup_user -p \
  --no-data mysql_advanced_review \
  > schema_only.sql

只备数据：

bash

mysqldump -u backup_user -p \
  --no-create-info mysql_advanced_review \
  > data_only.sql

7.5 恢复 SQL 文件

从操作系统终端：

bash

mysql -u restore_user -p < mysql_advanced_review.sql

已进入 mysql 客户端：

sql

SOURCE /absolute/path/mysql_advanced_review.sql;

恢复前确认：

目标实例和数据库；
字符集；
是否含 DROP；
账号权限；
磁盘空间；
外键与对象依赖；
是否会覆盖现有数据。

7.6 `mysqlbinlog`

查看二进制日志：

bash

mysqlbinlog \
  --start-datetime="2026-07-29 09:00:00" \
  --stop-datetime="2026-07-29 10:00:00" \
  binlog.000123

按位置：

bash

mysqlbinlog \
  --start-position=12345 \
  --stop-position=67890 \
  binlog.000123

binlog 常用于复制与时间点恢复。真实恢复前必须确认：

日志格式与 GTID 设置；
起止点；
是否包含不希望重放的语句；
基础全量备份和 binlog 是否连续。

不要把 mysqlbinlog ... | mysql ... 直接对生产实例盲目执行。

7.7 `mysqlshow` 与 `mysqlimport`

快速查看对象：

bash

mysqlshow -u review_user -p
mysqlshow -u review_user -p mysql_advanced_review
mysqlshow -u review_user -p mysql_advanced_review orders

导入文本文件：

bash

mysqlimport -u review_user -p \
  --local \
  --fields-terminated-by=',' \
  mysql_advanced_review /path/products.txt

mysqlimport 本质上是 LOAD DATA 的命令行封装，文件名通常要与目标表名匹配。

7.8 配置变量：Session、Global、Persist

sql

-- 当前连接
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- 后续连接的全局默认，需要权限
SET GLOBAL max_connections = 300;

-- MySQL 8 持久化到服务器管理的配置
SET PERSIST max_connections = 300;

区别：

方式	影响	重启后
`SET SESSION`	当前连接	失效
`SET GLOBAL`	通常影响后续连接或全局运行状态	通常失效
`SET PERSIST`	全局并写入持久化配置	保留

生产改参数前要记录原值、评估影响和回滚方式。不是所有变量都可动态修改或持久化。

8. 锁：从“为什么会等”到死锁排查

这一章不要先背锁名。先建立一个判断框架：

谁（哪个事务）通过什么访问路径，锁住了哪个索引记录或区间；另一个事务要申请什么锁；两者是否冲突；何时释放。

只要这五个问题答清楚，大多数锁现象都能解释。

8.1 先记住六条

锁通常属于事务或会话，不只属于某条 SQL。
InnoDB 行锁锁的是索引记录和索引区间，不是抽象的“这一行”。
普通 SELECT 通常走 MVCC 快照读，不会被未提交的行级 X 锁挡住。
UPDATE、DELETE、SELECT ... FOR UPDATE/FOR SHARE 是当前读，会申请锁。
没有合适索引时会扫描并可能锁住大量记录，但这不等于真的发生“行锁升级为表锁”。
事务结束才是主要释放点：COMMIT、ROLLBACK，或连接断开触发回滚。

8.2 锁的层次

层次	代表	主要解决什么
全局锁	Global Read Lock	整个实例的一致性操作
表级显式锁	`LOCK TABLES`	显式限制整表并发
元数据锁	MDL	防止 DML 与表结构变化冲突
意向锁	IS / IX	快速表示“表内将有行级 S/X 锁”
行级锁	Record / Gap / Next-Key	保护索引记录与区间

这些锁可以同时存在。例如一条 UPDATE 通常既涉及：

表上的 MDL 共享类锁；
表上的 IX 意向锁；
命中索引记录上的 X 锁；
RR 范围扫描时可能还有 gap / next-key 锁。

8.3 锁等待和死锁不是一回事

单向等待

text

事务 A 持有记录 201 的 X 锁
事务 B 也申请记录 201 的 X 锁

B ──等待──> A

A 提交后，B 可以继续。这只是锁等待。

环形等待

text

A 持有账户 1，等待账户 2
↑                     ↓
B 等待账户 1，持有账户 2

没有事务能自行前进，这是死锁。InnoDB 通常检测到后选择一个事务作为牺牲者回滚。

8.4 全局锁

显式获取全局读锁：

sql

FLUSH TABLES WITH READ LOCK;

释放：

sql

UNLOCK TABLES;

全局读锁存在时，普通读取可以继续，但会阻塞许多写入、DDL 和提交活动，是很重的操作。

典型历史用途是全库逻辑备份，防止按顺序读取多张表时获得互相不一致的时刻：

text

先备份库存表
   ↓
业务创建订单并扣库存
   ↓
再备份订单表

结果：备份中的订单是新的，库存却可能是旧的

对全为 InnoDB 的常规逻辑备份，优先：

bash

mysqldump -u backup_user -p \
  --single-transaction \
  mysql_advanced_review \
  > backup.sql

一致性快照可减少对业务写入的阻塞，但备份期间仍应避免 DDL，且非事务表不受同样保证。

8.5 显式表锁

sql

LOCK TABLES products READ;
-- 当前会话可读；其他会话也可读，写入等待

UNLOCK TABLES;

sql

LOCK TABLES products WRITE;
-- 当前会话可读写；其他会话的相关读写等待

UNLOCK TABLES;

业务应用很少需要手工 LOCK TABLES。它并发度低，而且与 InnoDB 事务组合时有额外规则。大多数场景应让 InnoDB 根据 SQL 自动使用行级锁。

8.6 MDL：为什么一个普通查询能挡住 `ALTER TABLE`

MDL（Metadata Lock）保护表结构：

查询和 DML 需要共享类 MDL，表示“我正在按当前结构使用表”。
DDL 需要排他 MDL，表示“我要改变结构”。
显式事务中的 MDL 通常持有到事务结束。

如果允许事务一边按旧结构执行，另一边同时删除列，结果无法正确解释。因此：

text

事务 A：使用 products 当前结构，未提交
事务 B：ALTER TABLE products ...

B 必须等 A 结束

即使是 ALGORITHM=INSTANT 的 DDL，仍需要在执行阶段取得排他 MDL，因此也可能被长事务挡住。

8.7 意向锁：它不是“打算以后再锁”

假设事务已经锁住表中一行，另一个会话想锁整张表。没有意向锁时，数据库需要逐行检查有没有冲突。

意向锁是在表级做标记：

意向锁	含义
IS	本事务在表内持有或准备取得行级 S 锁
IX	本事务在表内持有或准备取得行级 X 锁

它的作用是让表锁快速判断冲突，不需要逐行遍历。

兼容矩阵：

已持有 \ 新申请	IS	IX	S 表锁	X 表锁
IS	✅	✅	✅	❌
IX	✅	✅	❌	❌
S 表锁	✅	❌	✅	❌
X 表锁	❌	❌	❌	❌

重点：

IS 与 IX 之间通常兼容，因为它们只是声明表内可能有行锁。
是否真正冲突，还要检查具体行级 S/X 锁。
意向锁由 InnoDB 自动管理，事务结束自动释放。

8.8 行级 S 锁与 X 锁

锁	含义	其他事务可再取得
S（共享）	当前事务要稳定读取该记录	同记录 S 可以，X 不可以
X（排他）	当前事务要修改或独占读取该记录	同记录 S、X 都不可以

兼容矩阵：

已持有 \ 新申请	S	X
S	✅	❌
X	❌	❌

常见语句：

SQL	行级行为
普通 `SELECT`	通常快照读，不加行锁
`SELECT ... FOR SHARE`	对访问范围申请 S 锁
`SELECT ... FOR UPDATE`	对访问范围申请 X 锁
`INSERT`	对插入记录申请 X 类记录锁，并涉及插入意向
`UPDATE` / `DELETE`	对扫描和修改涉及的索引记录申请 X 锁

8.9 Record、Gap、Next-Key 到底锁什么

现有主键：

text

201        203        205        210

Record Lock：记录锁

锁住索引中的某条记录，例如 [203]。

sql

SELECT *
FROM products
WHERE product_id = 203
FOR UPDATE;

在 RR 中，使用唯一索引完整等值命中已存在记录时，通常只需记录锁，不锁前方间隙。

Gap Lock：间隙锁

锁住两个索引值之间的空档，不包含端点：

text

(203, 205)

它主要阻止其他事务向间隙插入新索引值，例如 204。它本身不锁住已有的 203 或 205 记录。

Gap Lock 是“抑制插入”的锁。同一间隙上的 gap 锁可以共存，不要套用普通 S/X 记录锁的兼容规则。

Next-Key Lock：临键锁

组合：

text

前方间隙 + 右端记录

例如：

text

(203, 205]

它既保护记录 205，也阻止在 203 与 205 之间插入。

InnoDB 在 RR 的范围当前读中常用 next-key 锁，目的是让锁定范围内不能凭空插入“幻影”记录。

Insert Intention Lock：插入意向锁

事务准备向某个间隙插入时使用的特殊 gap 锁。多个事务插入同一大间隙内的不同位置时，若不竞争同一位置，可能并发进行。

8.10 为什么索引决定加锁范围

InnoDB 并不记住抽象的 WHERE 逻辑，它更直接地知道“执行时扫描了哪些索引记录和范围”。

sql

UPDATE products
SET status = 'INACTIVE'
WHERE product_name = '机械键盘';

product_name 无索引，执行器可能扫描整个聚簇索引。RR 下，扫描经过的大量记录和间隙都可能被锁住。

如果有索引：

sql

CREATE INDEX idx_products_name
ON products (product_name);

就能先定位更窄的二级索引范围，再锁对应二级索引记录和聚簇索引记录。

正确表述：

没有合适索引时，行级锁的覆盖范围可能扩大到几乎整张表。

不够准确的表述：

行锁自动升级成了表锁。

InnoDB 通常仍持有许多索引记录锁，而不是转换成单个表锁；两者只是阻塞效果可能相似。

8.11 隔离级别如何改变锁

隔离级别	普通读	当前读与写的典型范围
RC	每次新快照	通常只锁索引记录；非匹配记录锁较早释放
RR	首次一致性读建立并复用快照	范围扫描常用 gap / next-key 防止插入
SERIALIZABLE	普通读也更强地参与锁定	并发最低，适合少数特殊场景

RC 下 gap lock 大多关闭，但外键检查与重复键检查等仍会使用相关间隙锁。

8.12 锁实验准备

打开两个终端：

bash

mysql -u review_user -p \
  -D mysql_advanced_review \
  --prompt='mysql(A)> '

bash

mysql -u review_user -p \
  -D mysql_advanced_review \
  --prompt='mysql(B)> '

两个终端都检查：

sql

SELECT CONNECTION_ID(), @@transaction_isolation, @@autocommit;
SET SESSION innodb_lock_wait_timeout = 10;

每次实验前恢复：

sql

ROLLBACK;

UPDATE products
SET stock = CASE product_id
    WHEN 201 THEN 100
    WHEN 203 THEN 80
    WHEN 205 THEN 20
    WHEN 210 THEN 50
END,
status = 'ACTIVE'
WHERE product_id IN (201, 203, 205, 210);

DELETE FROM products WHERE product_id = 204;

UPDATE accounts
SET balance = CASE account_id
    WHEN 1 THEN 5000.00
    WHEN 2 THEN 3000.00
END,
version = 0
WHERE account_id IN (1, 2);

若某条 B 端语句显示“等待中”，不要在 B 端继续输入；切回 A 端执行指定的 COMMIT 或 ROLLBACK。

8.13 实验一：X 锁不挡普通快照读，却挡更新

先恢复：

sql

UPDATE products SET stock = 100 WHERE product_id = 201;

终端 A：

text

mysql(A)> START TRANSACTION;
Query OK

mysql(A)> UPDATE products
    -> SET stock = 99
    -> WHERE product_id = 201;
Query OK, 1 row affected

mysql(A)> -- 先不要提交

此时 A 持有 product_id = 201 的 X 锁，99 尚未提交。

终端 B 普通查询：

text

mysql(B)> SELECT stock
    -> FROM products
    -> WHERE product_id = 201;
+-------+
| stock |
+-------+
|   100 |
+-------+
1 row in set

立即返回。 B 通过 MVCC 读到最后已提交版本 100，没有去申请与 A 冲突的记录锁。

终端 B 更新：

text

mysql(B)> UPDATE products
    -> SET stock = stock - 1
    -> WHERE product_id = 201;
-- 等待中，没有立即返回

B 的 UPDATE 需要同一记录的 X 锁，与 A 冲突。

切回终端 A：

text

mysql(A)> COMMIT;
Query OK

终端 B 随即继续：

text

Query OK, 1 row affected

最终值：

sql

SELECT stock FROM products WHERE product_id = 201;

text

+-------+
| stock |
+-------+
|    98 |
+-------+

过程是：A 提交 99，B 获得锁后基于当前值再减 1，得到 98。

实验结论：

text

普通 SELECT：快照读，通常不等行级 X 锁
UPDATE：当前读，需要 X 锁，冲突就等待

8.14 实验二：S/S 兼容，S/X 冲突

恢复：

sql

UPDATE products SET stock = 100 WHERE product_id = 201;

终端 A：

text

mysql(A)> START TRANSACTION;
Query OK

mysql(A)> SELECT stock FROM products
    -> WHERE product_id = 201 FOR SHARE;
+-------+
| stock |
+-------+
|   100 |
+-------+

终端 B：

text

mysql(B)> START TRANSACTION;
Query OK

mysql(B)> SELECT stock FROM products
    -> WHERE product_id = 201 FOR SHARE;
+-------+
| stock |
+-------+
|   100 |
+-------+

B 的 S 锁立即取得，因为 S/S 兼容。

终端 B 尝试升级为写：

text

mysql(B)> UPDATE products
    -> SET stock = stock - 1
    -> WHERE product_id = 201;
-- 等待 A 释放 S 锁

终端 A：

text

mysql(A)> COMMIT;
Query OK

终端 B：

text

Query OK, 1 row affected

mysql(B)> ROLLBACK;
Query OK

实验结论：

text

FOR SHARE + FOR SHARE  -> 可共存
FOR SHARE + UPDATE     -> 冲突

8.15 实验三：查不到记录，为什么还能挡住插入

保证 204 不存在：

sql

DELETE FROM products WHERE product_id = 204;

终端 A：

text

mysql(A)> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK

mysql(A)> START TRANSACTION;
Query OK

mysql(A)> SELECT product_id
    -> FROM products
    -> WHERE product_id = 204
    -> FOR UPDATE;
Empty set

mysql(A)> -- 查询为空，但先不要结束事务

主键中相邻值是 203 和 205。RR 下对不存在唯一键 204 的锁定等值查询，通常锁住间隙：

text

(203, 205)

终端 B：

text

mysql(B)> INSERT INTO products
    -> (product_id, sku, product_name, category,
    ->  price, stock, status, created_at)
    -> VALUES
    -> (204, 'PAD-204', '桌垫', '配件',
    ->  59.00, 10, 'ACTIVE', NOW());
-- 等待中

终端 A：

text

mysql(A)> ROLLBACK;
Query OK

终端 B：

text

Query OK, 1 row affected

清理：

sql

DELETE FROM products WHERE product_id = 204;

为什么空结果也要锁？

A 要稳定地锁定“主键 204 仍不存在”这个判断。若 B 可以插入 204，A 在同一事务的当前读范围中就出现了幻影。

把 A 改为 RC 后重做，普通唯一键缺失查询通常不会用相同 gap lock 阻塞 B；但重复键与外键检查仍可能涉及间隙锁。

8.16 实验四：无索引不是表锁升级，却可能像锁表

确认没有 product_name 索引：

sql

SHOW INDEX FROM products;

终端 A：

text

mysql(A)> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK

mysql(A)> START TRANSACTION;
Query OK

mysql(A)> UPDATE products
    -> SET status = 'INACTIVE'
    -> WHERE product_name = '机械键盘';
Query OK, 1 row affected

mysql(A)> -- 不提交

为找“机械键盘”，执行计划要扫描聚簇索引中的多条记录。

终端 B 更新完全不同的主键：

text

mysql(B)> UPDATE products
    -> SET stock = stock + 1
    -> WHERE product_id = 210;
-- 在该练习数据与全表扫描计划下应进入等待

若没有等待，先确认 A 使用的是 RR，再用 EXPLAIN 检查 A 的语句是否确实全表扫描；不同统计信息、索引或隔离级别会改变访问和加锁范围。

终端 A：

text

mysql(A)> ROLLBACK;
Query OK

终端 B 继续：

text

Query OK, 1 row affected

恢复 210 库存：

sql

UPDATE products SET stock = 50 WHERE product_id = 210;

建立索引后对照：

sql

CREATE INDEX idx_products_name
ON products (product_name);

终端 A 再做同一更新：

text

mysql(A)> START TRANSACTION;
Query OK

mysql(A)> UPDATE products
    -> SET status = 'INACTIVE'
    -> WHERE product_name = '机械键盘';
Query OK, 1 row affected

mysql(A)> -- 仍不提交

终端 B：

text

mysql(B)> UPDATE products
    -> SET stock = stock + 1
    -> WHERE product_id = 210;
Query OK, 1 row affected
-- 立即完成

A 可通过姓名索引只访问更窄的记录范围，因此没有持有 product_id = 210 上的冲突锁。

实验结束，在 A 中回滚并统一清理：

sql

ROLLBACK;

UPDATE products SET stock = 50 WHERE product_id = 210;

DROP INDEX idx_products_name ON products;

结论：

加锁范围跟实际扫描的索引范围有关。
无索引会让范围极宽。
它仍可能是一大批行级索引锁，而不是一个真正的表级 X 锁。
RC 下非匹配记录锁可能较早释放，现象会与 RR 不同。

8.17 实验五：长事务如何挡住 DDL（MDL）

终端 A：

text

mysql(A)> START TRANSACTION;
Query OK

mysql(A)> SELECT product_id, stock
    -> FROM products
    -> WHERE product_id = 201;
+------------+-------+
| product_id | stock |
+------------+-------+
|        201 |   100 |
+------------+-------+

mysql(A)> -- 普通查询已结束，但事务没有结束

终端 B：

text

mysql(B)> ALTER TABLE products
    -> ADD COLUMN demo_tag VARCHAR(20) NULL;
-- 等待排他 MDL

终端 A：

text

mysql(A)> COMMIT;
Query OK

终端 B：

text

Query OK, 0 rows affected

清理：

sql

ALTER TABLE products DROP COLUMN demo_tag;

最危险的线上链路：

text

A：长事务持有共享 MDL
        ↓
B：DDL 排队等待排他 MDL
        ↓
C、D、E：后续访问可能继续排在 DDL 后
        ↓
连接大量堆积

所以 DDL 发布前必须排查长事务，而不是只看“这个 ALTER 是不是 INSTANT”。

8.18 实验六：亲手制造死锁

恢复账户：

sql

UPDATE accounts
SET balance = CASE account_id
    WHEN 1 THEN 5000.00
    WHEN 2 THEN 3000.00
END
WHERE account_id IN (1, 2);

第一步：A 锁账户 1

text

mysql(A)> START TRANSACTION;
Query OK

mysql(A)> UPDATE accounts
    -> SET balance = balance - 100
    -> WHERE account_id = 1;
Query OK, 1 row affected

第二步：B 锁账户 2

text

mysql(B)> START TRANSACTION;
Query OK

mysql(B)> UPDATE accounts
    -> SET balance = balance - 100
    -> WHERE account_id = 2;
Query OK, 1 row affected

第三步：A 等账户 2

text

mysql(A)> UPDATE accounts
    -> SET balance = balance + 100
    -> WHERE account_id = 2;
-- 等待 B

第四步：B 再等账户 1，形成环

text

mysql(B)> UPDATE accounts
    -> SET balance = balance + 100
    -> WHERE account_id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

InnoDB 选择一个牺牲事务，示例中显示 B，但真实环境不保证总是 B。

另一个事务解除等待后仍要显式结束：

text

mysql(A)> COMMIT;
Query OK

恢复实验数据：

sql

UPDATE accounts
SET balance = CASE account_id
    WHEN 1 THEN 5000.00
    WHEN 2 THEN 3000.00
END
WHERE account_id IN (1, 2);

死锁形成图：

text

A 持有 account 1 ──等待──> account 2（B 持有）
↑                                      │
└──────── account 1（B 等待） <────────┘

8.19 死锁怎么预防

1. 固定加锁顺序

无论 1 → 2 还是 2 → 1 转账，都先锁较小 ID：

sql

START TRANSACTION;

SELECT account_id, balance
FROM accounts
WHERE account_id = 1
FOR UPDATE;

SELECT account_id, balance
FROM accounts
WHERE account_id = 2
FOR UPDATE;

-- 再按实际转账方向修改
COMMIT;

第 6 章 sp_transfer 就采用了这个思路。

2. 事务短小

事务中不要：

等用户点击；
调用慢外部接口；
做大文件处理；
无限制批量更新；
打开事务后长时间闲置。

3. 使用合适索引

扫描更少索引记录，通常就申请更少锁，冲突窗口也更小。

4. 应用必须支持重试

死锁是并发系统的正常可恢复事件。收到 1213 后：

丢弃本次事务结果。
进行短暂随机退避。
从事务开头整体重试。
设置最大重试次数。

不能只重发最后一条 SQL，因为前面的事务状态可能已回滚。

8.20 死锁与锁等待超时的区别

情况	常见错误	处理
死锁	`1213 / SQLSTATE 40001`	一个事务被选为牺牲者，整体重试
等待超时	`1205 / Lock wait timeout`	默认通常只回滚当前语句，应用应主动回滚整个业务事务

sql

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
SHOW VARIABLES LIKE 'innodb_rollback_on_timeout';

不要把调大超时时间当成解决方案。它只会让等待更久，根因仍是长事务、错误加锁顺序、无索引扫描或热点行。

8.21 `NOWAIT` 与 `SKIP LOCKED`

不愿等待：

sql

START TRANSACTION;

SELECT *
FROM products
WHERE product_id = 201
FOR UPDATE NOWAIT;

锁冲突时立即报错，由应用决定重试或返回。

任务队列跳过已被其他工作者领取的行：

sql

START TRANSACTION;

SELECT order_id
FROM orders
WHERE status = 'PENDING'
ORDER BY order_id
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- 更新领取状态
COMMIT;

队列高频使用时应建立匹配索引，例如：

sql

CREATE INDEX idx_orders_status_id
ON orders (status, order_id);

SKIP LOCKED 返回的是不完整视图，适合多消费者队列，不适合要求严格读取全部匹配数据的一般业务查询。

8.22 怎么查“谁在等谁”

第一步：进程列表

sql

SHOW FULL PROCESSLIST;

重点看：

Id：连接 ID；
Time：当前状态持续时间；
State：是否等待锁、MDL 等；
Info：当前 SQL。

第二步：最方便的 sys 视图

sql

SELECT
    wait_age,
    locked_table_schema,
    locked_table_name,
    locked_index,
    waiting_pid,
    blocking_pid,
    waiting_query,
    blocking_query
FROM sys.innodb_lock_waits;

若阻塞者当前空闲，blocking_query 可能是 NULL；它仍可能开着未提交事务并持锁。

第三步：Performance Schema 原始锁

当前数据锁：

sql

SELECT
    engine_transaction_id,
    thread_id,
    object_schema,
    object_name,
    index_name,
    lock_type,
    lock_mode,
    lock_status,
    lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'mysql_advanced_review';

等待关系：

sql

SELECT
    w.requesting_engine_transaction_id AS waiting_trx,
    w.requesting_thread_id             AS waiting_thread,
    w.blocking_engine_transaction_id   AS blocking_trx,
    w.blocking_thread_id               AS blocking_thread,
    r.object_name,
    r.index_name,
    r.lock_mode,
    r.lock_data
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
  ON r.engine = w.engine
 AND r.engine_lock_id = w.requesting_engine_lock_id;

第四步：查活跃事务

sql

SELECT
    trx_id,
    trx_mysql_thread_id,
    trx_state,
    trx_started,
    trx_rows_locked,
    trx_rows_modified,
    trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

第五步：查 MDL

sql

SELECT
    object_schema,
    object_name,
    lock_type,
    lock_duration,
    lock_status,
    owner_thread_id
FROM performance_schema.metadata_locks
WHERE object_schema = 'mysql_advanced_review'
ORDER BY object_name, lock_status;

第六步：看最近一次死锁

sql

SHOW ENGINE INNODB STATUS\G

搜索：

text

LATEST DETECTED DEADLOCK

关注：

两个事务各执行什么 SQL；
各自持有什么锁；
又在等待什么锁；
使用哪个索引；
哪个事务被回滚。

8.23 `KILL` 之前必须知道什么

sql

KILL QUERY 123;

只终止当前语句。若连接仍处于事务中，它先前持有的锁可能继续存在。

sql

KILL CONNECTION 123;

终止连接，活动事务会回滚并最终释放锁。

线上操作前必须确认：

连接 ID 是否仍对应目标会话；
它是否在执行关键事务；
回滚量多大、需要多久；
是否会触发应用自动重试；
是否有更安全的方式让应用主动回滚。

8.24 锁何时释放

场景	释放时机
InnoDB 记录/间隙/意向锁	通常事务 `COMMIT` / `ROLLBACK`
`autocommit = 1` 的单条 DML	语句提交时
`SELECT ... FOR UPDATE` 未显式开事务	语句自动提交后，很快释放
MDL	通常语句或事务结束；显式事务内常到事务结束
`LOCK TABLES`	`UNLOCK TABLES` 或连接结束
全局读锁	`UNLOCK TABLES` 或连接结束
连接异常断开	服务器回滚活动事务后释放

没有通用的 UNLOCK ROW 命令。想释放事务锁，就结束事务。

8.25 三个可靠并发写法

防超卖：条件更新

sql

UPDATE products
SET stock = stock - 1
WHERE product_id = 201
  AND stock >= 1;

检查受影响行数即可。

复杂库存决策：悲观锁

sql

START TRANSACTION;

SELECT stock, status
FROM products
WHERE product_id = 201
FOR UPDATE;

-- 做短小、纯数据库内判断
UPDATE products
SET stock = stock - 1
WHERE product_id = 201;

COMMIT;

低冲突写入：乐观锁

sql

UPDATE accounts
SET balance = 4900.00,
    version = version + 1
WHERE account_id = 1
  AND version = 0;

如何选：

场景	建议
单条条件即可表达	原子条件更新
冲突高、必须先读后决定	`FOR UPDATE`，短事务
冲突低、可安全重试	`version` 乐观锁

8.26 锁章节一页速记

text

普通 SELECT
└─ 通常 MVCC 快照读，不加行锁

SELECT ... FOR SHARE
└─ 当前读，S 锁

SELECT ... FOR UPDATE / UPDATE / DELETE
└─ 当前读，X 锁

唯一索引 + 完整等值 + 已存在
└─ 通常 Record Lock

RR + 范围/非唯一索引当前读
└─ 常见 Next-Key Lock

RR + 唯一键等值但不存在
└─ 常见 Gap Lock

无合适索引
└─ 扫描多 → 锁记录多，不是简单“升级表锁”

普通查询挡 DDL
└─ 查 MDL 和未提交长事务

A 等 B，B 等 A
└─ 死锁：固定顺序 + 短事务 + 整体重试

8.27 锁的最终自测

尝试不看答案回答：

A 更新一行未提交，为什么 B 的普通 SELECT 还能返回？
为什么 B 的 UPDATE 同一行会等待？
查询不存在的主键并 FOR UPDATE，为什么可能挡住插入？
无索引更新为什么会阻塞另一主键，却不能简单叫“表锁升级”？
一个普通 SELECT 已经执行完，为什么未提交事务仍能挡 DDL？
意向锁锁住了具体哪一行吗？
死锁报错后为什么要重试整个事务？
KILL QUERY 后为什么锁可能还在？

答案：

普通 SELECT 通常通过 MVCC 读取已提交快照。
更新需要同一索引记录的 X 锁，与 A 的 X 锁冲突。
RR 下可能取得保护缺失键所在区间的 gap lock。
实际是扫描并持有大量索引记录/区间锁，未必转换成表级锁。
MDL 往往持有到显式事务结束。
不锁具体行，它是表级的行锁意向标记。
死锁牺牲事务的整个事务状态已被回滚，不能只续最后一步。
它可能只停止当前语句，连接中的事务仍未结束。

9. 高频面试题与最终复习清单

9.1 高频问题速答

1. 为什么 InnoDB 常用 B+Tree？

B+Tree 分支多、树高低，适合页式存储；数据集中在有序叶子层，既支持等值，又支持范围、排序和分组。相比 Hash，它能处理有序访问；相比二叉树，大数据量下通常需要更少层级。

2. 聚簇索引和二级索引有什么区别？

聚簇索引叶子保存完整行，每表一个；InnoDB 二级索引叶子保存二级键和主键值，可有多个。二级索引查询缺少的列时，再按主键访问聚簇索引，这叫回表。

3. 为什么主键宜短且稳定？

主键决定聚簇组织，且被所有二级索引叶子携带。主键过大会放大索引；修改主键等于改变行的组织位置，成本高。趋势递增还可减少随机页访问和页分裂。

4. 什么是最左前缀？

联合索引按定义顺序逐层排序，查询通常从首列开始形成连续可定位前缀。它与 WHERE 条件书写顺序无关；中间缺列后，右侧列一般不能继续缩小普通索引搜索区间。

5. 范围条件后面的索引列一定失效吗？

不能绝对说“失效”。范围列通常终止继续构造更窄的 B+Tree 起止区间，但右侧列仍可能参与 ICP、覆盖、过滤或排序。以 EXPLAIN 和 EXPLAIN ANALYZE 为准。

6. 什么是覆盖索引？

查询需要的所有列都能从某个索引得到，无需再访问聚簇索引完整行。传统执行计划常出现 Using index。

7. `Using index condition` 是回表吗？

它表示使用 ICP，把可由索引列判断的条件下推到存储引擎，以减少读取完整行。它不等同于一句“已经回表”；是否需要读取完整行还要看查询列和完整计划。

8. 为什么优化器有索引却不用？

使用索引不一定成本更低。返回比例很高、大量随机回表、小表、统计信息或数据分布都可能让全表扫描更便宜。

9. `EXPLAIN` 与 `EXPLAIN ANALYZE` 的区别？

EXPLAIN 展示优化器估算的计划；EXPLAIN ANALYZE 真正执行并给出每个迭代器的实际时间、行数和循环次数，可用来比较估算与现实。

10. `COUNT(*)`、`COUNT(1)`、`COUNT(column)`？

前两者统计结果集行数；COUNT(column) 忽略该列为 NULL 的行。统计行数优先用语义直接的 COUNT(*)，不要背固定性能排名。

11. redo、undo、binlog 分别做什么？

redo 是 InnoDB 崩溃恢复与持久性的基础；undo 用于回滚并提供 MVCC 旧版本；binlog 属于 Server 层，用于复制和时间点恢复等。

12. 什么是 MVCC？

InnoDB 维护记录版本链，由隐藏事务信息、undo 和 Read View 共同判断普通快照读可见哪个版本，从而减少读写冲突。

13. RC 和 RR 的核心差别？

RC 每次一致性读通常创建新 Read View；RR 通常复用首次一致性读建立的视图。当前读和写还会因隔离级别采用不同的锁定策略。

14. 快照读和当前读？

普通 SELECT 通常是快照读，读取可见版本且不加行锁；FOR SHARE、FOR UPDATE、UPDATE、DELETE 是当前读，要读取较新状态并申请锁。

15. Record、Gap、Next-Key？

Record 锁索引记录；Gap 锁记录之间的间隙，主要阻止插入；Next-Key 是前方间隙加右端记录。RR 范围当前读常用 Next-Key 防止幻影插入。

16. 没索引会“行锁升级成表锁”吗？

通常不是。InnoDB 为扫描到的大量索引记录和区间加锁，阻塞效果可能接近锁表，但内部未必转换成一个表锁。

17. 为什么普通 `SELECT` 经常不被 X 锁阻塞？

因为普通查询通常通过 MVCC 读取已提交的可见版本，不申请冲突的记录锁。SERIALIZABLE、显式表锁、MDL 等情况要另行分析。

18. 什么是 MDL？

MDL 保护表结构。查询和 DML 使用共享类 MDL，DDL 需要排他 MDL。显式事务长期不提交，即使查询早已返回，也可能继续挡住 DDL。

19. 如何降低死锁？

固定访问表和行的顺序、保持事务短小、使用合适索引、减少不必要锁定，并让应用能对 1213 从事务开头整体重试。

20. `--single-transaction` 为什么适合 InnoDB 备份？

它利用一致性快照导出事务表，通常无需全局读锁即可获得同一事务视图。它不能让非事务表自动一致，备份期间 DDL 也可能破坏一致性。

9.2 易错说法校正表

容易背错的说法	更准确的版本
MySQL Server 层有查询缓存	MySQL 8.0 已移除旧 Query Cache
读多就应该选 MyISAM	新业务通常仍优先 InnoDB，需综合事务、并发和恢复
InnoDB B+Tree 叶子是单向链表	InnoDB 叶子页按顺序并通过双向链接组织
`>` 会让右列失效，`>=` 不会	范围访问是优化器构造区间的问题，不可用此口诀绝对判断
字符串不加引号，索引一定失效	隐式转换方向决定访问方式；正确做法是参数类型与列一致
`OR` 一边无索引，所有索引必失效	可能 Index Merge、拆分访问或全扫，成本驱动
`Using index condition` 就是回表	它表示 ICP 条件下推，不是“回表”的同义词
`filtered` 越大计划一定越好	它只是估算的保留比例，要结合扫描、循环与总成本
`ALL` 一定很差	小表全扫可能最合理
无索引时行锁升级为表锁	常是锁住大量扫描到的索引记录和间隙
`COUNT(字段) < COUNT(id) < COUNT(1) < COUNT(*)` 永远成立	这是过度口诀；先按语义写，再用当前版本实测
提交后 undo 立即删除	活跃快照仍需要旧版本时，undo 不能立即清理
Log Buffer 同时缓存 redo 和 undo	Log Buffer 缓冲的是 redo；undo 位于 undo 页/表空间，相关页也可能被 Buffer Pool 缓存
Change Buffer 默认一直处理二级索引变更	取决于版本和 `innodb_change_buffering`；MySQL 8.4 默认值为 `none`
redo 只有固定两个文件循环写	当前版本 redo 文件组织和容量管理已变化，掌握 WAL 与恢复职责更重要
一致性只由 redo 与 undo 保证	一致性是目标，还依赖隔离、约束和正确业务逻辑

9.3 综合练习

练习 1：设计索引

高频查询：

sql

SELECT order_id, total_amount, created_at
FROM orders
WHERE customer_id = ?
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

参考设计：

sql

CREATE INDEX idx_orders_customer_status_created_amount
ON orders (
    customer_id,
    status,
    created_at DESC,
    order_id,
    total_amount
);

解释：

前两列等值过滤；
created_at 支持排序和范围；
order_id 保证稳定顺序；
total_amount 让该查询具备覆盖可能。

但现有练习库已经有相近索引。真实项目应先评估是否值得用更宽索引替换，而不是重复新增。

练习 2：改写日期函数查询

原 SQL：

sql

SELECT *
FROM orders
WHERE DATE(created_at) = '2026-07-20';

改写：

sql

SELECT order_id, customer_id, status, total_amount, created_at
FROM orders
WHERE created_at >= '2026-07-20 00:00:00'
  AND created_at <  '2026-07-21 00:00:00';

练习 3：防止库存变成负数

sql

UPDATE products
SET stock = stock - 5
WHERE product_id = 201
  AND stock >= 5;

应用以受影响行数判断成功或失败。

练习 4：说明锁类型

RR 下：

sql

START TRANSACTION;

SELECT *
FROM products
WHERE product_id = 204
FOR UPDATE;

已知 204 不存在，前后为 203、205。

参考答案：通常对唯一主键缺失位置取得 (203, 205) 的 gap lock，阻止其他事务插入 204，直到事务结束。

练习 5：找出死锁风险

事务 A：

sql

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

事务 B：

sql

UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;

参考答案：两个事务按相反顺序锁账户。统一为先锁较小 account_id，再锁较大 account_id，并保留死锁整体重试。

9.4 最终复习清单

架构与 InnoDB

text

[ ] 能说清 Server 层与存储引擎层的分工
[ ] 知道 MySQL 8 已移除旧 Query Cache
[ ] 能解释页、Buffer Pool、脏页与 WAL
[ ] 能说清 Change Buffer、Log Buffer 与 Adaptive Hash 的职责
[ ] 能解释 doublewrite 防页损坏、redo 做崩溃恢复
[ ] 能区分 redo、undo、binlog
[ ] 能说清快照读、当前读、Read View
[ ] 能解释 RC 与 RR 的 Read View 时机

索引与优化

text

[ ] 能画出聚簇索引和二级索引的查询路线
[ ] 能解释回表、覆盖索引与 ICP
[ ] 不再把最左前缀理解成 WHERE 书写顺序
[ ] 不用“索引失效口诀”替代 EXPLAIN
[ ] 会读 type、key、rows、filtered、Extra
[ ] 会用 EXPLAIN ANALYZE 对比估算与实际
[ ] 会优化排序、深分页、批量写入与条件更新

数据库编程与管理

text

[ ] 会创建和查询视图，理解 CHECK OPTION
[ ] 会写 IN / OUT 参数与异常 Handler
[ ] 会正确写游标的 done 标记
[ ] 知道集合式 SQL 通常优于游标循环
[ ] 知道触发器与原 DML 在同一事务
[ ] 会做 InnoDB 一致性逻辑备份并进行恢复演练

锁

text

[ ] 能从事务、索引路径、锁对象、兼容性、释放点分析
[ ] 知道普通 SELECT 为什么通常不等行级 X 锁
[ ] 能区分 S / X、IS / IX
[ ] 能区分 Record / Gap / Next-Key
[ ] 知道无索引是锁范围扩大，不是简单表锁升级
[ ] 会复现和解释 MDL 等待
[ ] 会用 sys.innodb_lock_waits 与 data_locks 排查
[ ] 能解释锁等待与死锁的差异
[ ] 知道死锁后要整体重试事务

10. 官方校对入口

本手册以 MySQL 8.x 为目标，并按 MySQL 8.4 官方文档校正关键机制。继续深挖时优先阅读：

最后一句：索引问题看访问路径，MVCC 问可见版本，锁问题问“谁锁了哪个索引范围、谁又申请了什么”。不要只背名词。

MySQL 进阶复习手册（理解强化版）

目录

0. 统一练习数据库

0.1 customers：客户表

0.2 products：商品表

0.3 orders：订单表

0.4 order_items：订单明细表

0.5 accounts：账户表

0.6 表关系与关键索引

0.7 完整初始化脚本

0.8 可选：扩充订单数据做性能实验

1. MySQL 架构与存储引擎

1.1 一条 SQL 经历了什么

1.2 存储引擎是“表级选择”

1.3 InnoDB、MyISAM、Memory 怎么选

1.4 InnoDB 表空间和页

1.5 存储引擎面试速答

2. InnoDB、事务日志与 MVCC

2.1 Buffer Pool：为什么查询不总是读磁盘

2.2 其他内存、磁盘与后台组件

2.3 一次更新为什么不立即把整页刷盘

2.4 redo、undo、binlog 不要混

2.5 ACID 到底由谁保证

2.6 innodb_flush_log_at_trx_commit

2.7 快照读与当前读

2.8 MVCC 依靠什么

2.9 Read View 用人话理解

2.10 READ COMMITTED 与 REPEATABLE READ

双终端实验：RC

双终端实验：RR

2.11 MVCC 不等于“没有并发问题”

3. 索引：从 B+Tree 到联合索引

3.1 索引解决什么问题

3.2 为什么常用 B+Tree

3.3 聚簇索引与二级索引

聚簇索引

二级索引

3.4 主键为什么宜短、稳定、尽量有序

3.5 页分裂与页合并

3.6 索引分类与基本语法

3.7 联合索引不是多个单列索引的拼接

3.8 最左前缀规则

完整利用前两列

跳过首列

中间断层

WHERE 书写顺序不重要

3.9 联合索引遇到范围条件

3.10 常见低效场景

1. 在索引列上做函数或计算

2. 隐式类型转换

3. 前导通配符

4. OR

5. 低选择性或返回大部分行

6. IS NULL

3.11 覆盖索引

3.12 Using index 与 Using index condition

3.13 前缀索引

3.14 单列索引还是联合索引

3.15 索引提示与不可见索引

3.16 索引设计清单

4. 性能分析与 EXPLAIN

4.1 正确优化流程

4.2 全局语句频次

4.3 慢查询日志

4.4 Performance Schema 找“总成本最高”的 SQL

4.5 EXPLAIN 看估算，EXPLAIN ANALYZE 看实测

4.6 传统 EXPLAIN 重点列

4.7 type 访问方式怎么记

4.8 Extra 高频词

4.9 估算与实际差距很大怎么办

4.10 一次执行计划复盘

5. SQL 优化

5.1 总原则

5.2 批量插入

5.3 LOAD DATA 导入大文件

5.4 主键插入顺序

5.5 ORDER BY 优化

5.6 GROUP BY 优化

5.7 深分页

方案一：游标/Seek 分页

0.1 `customers`：客户表

0.2 `products`：商品表

0.3 `orders`：订单表

0.4 `order_items`：订单明细表

0.5 `accounts`：账户表

2.6 `innodb_flush_log_at_trx_commit`

2.10 `READ COMMITTED` 与 `REPEATABLE READ`

`WHERE` 书写顺序不重要

4. `OR`

6. `IS NULL`

3.12 `Using index` 与 `Using index condition`

4. 性能分析与 `EXPLAIN`

4.5 `EXPLAIN` 看估算，`EXPLAIN ANALYZE` 看实测

4.6 传统 `EXPLAIN` 重点列

4.7 `type` 访问方式怎么记

4.8 `Extra` 高频词

5.3 `LOAD DATA` 导入大文件

5.5 `ORDER BY` 优化

5.6 `GROUP BY` 优化

5.8 `COUNT` 的正确理解

5.9 `UPDATE`：先缩小扫描，再缩短事务

6.4 可更新视图与 `CHECK OPTION`

6.5 `DELIMITER` 到底是什么

6.8 `IF`、`CASE` 与 `SIGNAL`

6.13 触发器与 `OLD` / `NEW`

7.2 `mysql` 客户端

7.3 `mysqladmin`

7.4 `mysqldump` 一致性备份

7.6 `mysqlbinlog`

7.7 `mysqlshow` 与 `mysqlimport`

8.6 MDL：为什么一个普通查询能挡住 `ALTER TABLE`

8.21 `NOWAIT` 与 `SKIP LOCKED`