agent_backend/public/downloads/database-lessons.md

数据库开发

术核 AI · 后端工程课件

01 准备 MySQL 8.4 与命令行练习环境

创建独立的 notes_lab，核对客户端、账号、字符集和会话参数。

本节目标

• 启动MySQL 8.4 LTS并用业务账号连接
• 区分容器端口3306与宿主端口3307
• 核对InnoDB、utf8mb4和当前数据库

必要概念

服务进程、数据库与客户端

mysqld持续保存和处理数据；mysql命令行客户端发送SQL。一个实例中可以创建多个数据库。MySQL中的SCHEMA与DATABASE在这里是同义词，账号则是用户名与来源host的组合。退出客户端不会停止服务。

本机独立实验边界

所有建表、写入、锁等待与恢复命令仅在notes_lab练习环境运行，不针对线上实例。需要Docker与Python 3.11+；Windows建议在WSL Bash中执行本模块Shell命令。本节只借Docker获得一致环境，后续容器模块再系统讲解。

默认值也要核对

本课程固定MySQL 8.4、InnoDB、utf8mb4；默认隔离级别通常是REPEATABLE-READ，后续实验显式设置。统一把DATETIME(6)中的业务时间解释为UTC，各客户端建立连接时设置+00:00。

动手实现

1. 生成本机凭据并启动数据库

在准备存放工程的父目录中执行以下 Bash，命令会创建并进入 notes-service。仅首次初始化时生成凭据；继续学习时进入既有目录并复用 .mysql.env，不要重复生成密码。镜像首次初始化会创建notes_lab和app_user，app_user只在该练习数据库获得权限，不是root。密码生成在忽略文件中，终端不打印；重新使用旧数据卷时环境变量不会重置已存在账号。

文件：终端 / 当前课时

mkdir -p notes-service
cd notes-service
umask 077
python3 - <<'PY'
import secrets
from pathlib import Path
root_password=secrets.token_urlsafe(32)
app_password=secrets.token_urlsafe(32)
Path('.mysql.env').write_text(f'MYSQL_ROOT_PASSWORD={root_password}\nMYSQL_DATABASE=notes_lab\nMYSQL_USER=app_user\nMYSQL_PASSWORD={app_password}\n')
PY
printf '.mysql.env\n.mysql-client.cnf\n.env\nbackups/\n.venv/\n' >> .gitignore
docker run --name notes-mysql --env-file .mysql.env \
  -p 127.0.0.1:3307:3306 -v notes_mysql_data:/var/lib/mysql \
  -d mysql:8.4 --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci --default-time-zone=+00:00
docker logs --tail 30 notes-mysql

2. 用交互密码连接业务库

等待日志出现ready for connections。用编辑器读取本机.mysql.env中的MYSQL_PASSWORD用于交互登录，不粘贴到共享日志。第一条命令在容器内连接3306；本机安装了mysql客户端则用第二条，两条选择一条。

文件：终端 / 当前课时

docker exec -it notes-mysql mysql --protocol=TCP -h 127.0.0.1 -P 3306 -u app_user -p notes_lab
# 或本机客户端：
mysql --protocol=TCP -h 127.0.0.1 -P 3307 -u app_user -p notes_lab

3. 确认环境与身份

下面是mysql客户端中的SQL。DATABASE()必须是notes_lab，CURRENT_USER()显示匹配到的授权账号；不要只凭命令行提示符判断目标。默认排序规则ai_ci不区分重音和大小写，涉及唯一名称时要把此行为纳入设计。

文件：终端 / 当前课时

SELECT VERSION(),DATABASE(),CURRENT_USER(),USER();
SELECT @@default_storage_engine,@@character_set_database,@@collation_database;
SELECT @@transaction_isolation,@@autocommit,@@sql_mode;
SET SESSION time_zone = '+00:00';
SELECT @@session.time_zone,UTC_TIMESTAMP(6);
SHOW GRANTS FOR CURRENT_USER();

4. 验证客户端和事务开关

mysql交互客户端通常autocommit=1，单条DML独立提交；Python稍后显式使用autocommit=False。不要将两个客户端的默认行为混为一谈。退出重连后再次确认数据库。

文件：终端 / 当前课时

SELECT 6*7 AS answer;
-- answer=42
SELECT DATABASE()='notes_lab' AS in_lab;
-- in_lab=1
quit

验收结果

• 服务版本为8.4，数据库为notes_lab，存储引擎为InnoDB。
• 客户端使用3307或容器内部3306的正确位置，业务身份不是root。
• 字符集utf8mb4、会话UTC和隔离级别已实际核对。

生产约束与故障演练

新服务需要能重复建立且不会误连线上库的开发环境。

实现决策

• 显式核对数据库与授权账号。
• 本机密码文件不进入仓库。
• 生产记录8.4补丁版和镜像摘要，练习使用mysql:8.4入口。

隔离环境演练

在独立实例用错库名与端口连接，分别记录错误层次。

恢复验收

• 目标可辨认。
• 环境与数据卷生命周期可区分。
• 无共享明文密码。

常见错误

Can't connect to MySQL server

原因：初始化尚未完成，或混淆宿主与容器端口。

处理：查看专用容器日志；宿主3307，docker exec内部3306。

Access denied for user

原因：用户名/host匹配或密码不对；旧卷不会因env变化重设密码。

处理：使用当前卷的真实凭据，管理账号确认账号host；需要改密时执行明确ALTER USER，不删卷解决。

扩展练习

故意用notes_missing连接，再与连接成功后SELECT 1/0比较错误层次。

提示：数据库名属于连接目标，SQL表达式在建立会话之后才执行。

参考答案：

不存在数据库通常为1049 Unknown database；认证失败常为1045。MySQL中SELECT 1/0通常返回NULL并产生警告，可用SHOW WARNINGS查看，不能把它当作稳定抛异常的事务测试。要测试约束失败请使用后续非法INSERT。

技术文档

• MySQL 8.4：mysql客户端
• MySQL 8.4：账号名称
• Docker：MySQL镜像

---

02 定义InnoDB笔记表、UTC时间与数据约束

创建统一notes模型，验证布尔值、标题和时间的真实MySQL行为。

本节目标

• 建立AUTO_INCREMENT主键和CHECK约束
• 区分DATETIME存值约定与时区转换
• 说明JSON、TEXT和BOOLEAN的存储边界

必要概念

类型与约束各自负责边界

id使用BIGINT AUTO_INCREMENT，标题VARCHAR(120)，正文TEXT。MySQL BOOLEAN是TINYINT(1)同义词，单写BOOLEAN不会限制只能0或1，所以增加CHECK。MySQL 8.4会执行CHECK，不应沿用旧版本忽略CHECK的印象。

UTC是明确的数据约定

DATETIME(6)保存带微秒的日期时间字段，本身不保存时区，也不会因SET time_zone转换已有值。连接统一+00:00，写入使用UTC_TIMESTAMP(6)或UTC会话下的CURRENT_TIMESTAMP(6)，Python输出时再明确标注UTC。

结构化扩展字段需限定用途

JSON列验证文档语法，但不能替代需要连接、唯一性和外键的关系表。TEXT默认值使用表达式DEFAULT ('')；正文上限16000字符，utf8mb4最坏64000字节仍在TEXT容量内；JSON和日志也应限制大小，避免无限载荷。updated_at不自动更新，本课程每次写入显式维护。

动手实现

1. 首次创建统一notes表

在app_user/notes_lab下执行一次，保存为sql/001_notes.sql。version从第一版就存在，后续并发修改统一递增。所有业务表明确ENGINE=InnoDB，不依赖不同环境的隐含配置。

文件：sql/001_notes.sql

CREATE TABLE notes (
 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(120) NOT NULL,
 content TEXT NOT NULL DEFAULT (''),
 pinned BOOLEAN NOT NULL DEFAULT FALSE,
 created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 version INT NOT NULL DEFAULT 1,
 CONSTRAINT notes_title_nonempty CHECK (CHAR_LENGTH(TRIM(title))>0),
 CONSTRAINT notes_pinned_boolean CHECK (pinned IN (0,1)),
 CONSTRAINT notes_content_limit CHECK (CHAR_LENGTH(content)<=16000),
 CONSTRAINT notes_version_positive CHECK (version>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
SHOW CREATE TABLE notes;

2. 插入并从同一会话取得编号

MySQL 8.4不提供本模块DML的RETURNING语法。插入后使用同一会话LAST_INSERT_ID()，Python使用cursor.lastrowid；绝不能查询MAX(id)猜自己的编号。

文件：终端 / 当前课时

INSERT INTO notes(title) VALUES('db02-first');
SET @note_id=LAST_INSERT_ID();
SELECT id,title,content,pinned,version,created_at,updated_at FROM notes WHERE id=@note_id;
-- content空串，pinned=0，version=1，时间非空

3. 逐条提交非法值观察错误

在交互客户端autocommit=1时逐条运行，预期各自失败。先确认STRICT_TRANS_TABLES包含在sql_mode，超长数据不能静默截断。随后核对只有合法样本。

文件：终端 / 当前课时

INSERT INTO notes(title) VALUES(NULL);
-- 1048：列不可为NULL
INSERT INTO notes(title) VALUES('   ');
-- 3819：CHECK失败
INSERT INTO notes(title,pinned) VALUES('db02-invalid-bool',2);
-- 3819：BOOLEAN底层是整数，CHECK拦住2
INSERT INTO notes(title) VALUES(REPEAT('a',121));
-- 严格模式下1406：数据过长
SELECT id,title FROM notes WHERE id=@note_id;

4. 验证DATETIME不会随显示时区转换

同一created_at在两个会话时区设置下保持原字段值，而NOW(6)随会话时区变化、UTC_TIMESTAMP(6)保持UTC。不要把没有tzinfo的数据库datetime直接当作用户本地时间。最后恢复+00:00。

文件：终端 / 当前课时

SET SESSION time_zone='+00:00';
SELECT created_at,NOW(6),UTC_TIMESTAMP(6) FROM notes WHERE id=@note_id;
SET SESSION time_zone='+08:00';
SELECT created_at,NOW(6),UTC_TIMESTAMP(6) FROM notes WHERE id=@note_id;
SET SESSION time_zone='+00:00';
SELECT JSON_EXTRACT('{"source":"api","revision":1}','$.source') AS source;
-- "api"：JSON字符串与SQL普通字符串的输出表示不同

验收结果

• SHOW CREATE TABLE确认InnoDB、CHECK、version和utf8mb4。
• 非法NULL、空标题和pinned=2被拒绝，默认值与主键正确。
• 切换时区时DATETIME原值不变，能解释UTC存值约定。

生产约束与故障演练

API、脚本和worker共同写入同一张表。

实现决策

• 完整性规则放到数据库。
• UTC作为所有写入入口的共同约定。
• 编号不保证连续，updated_at与version由统一写入代码维护。

隔离环境演练

向notes_lab提交非法标题和布尔整数，记录错误码与未落库结果。

恢复验收

• 约束真实生效。
• 时间序列化规则一致。
• 没有依赖自增编号连续性。

常见错误

pinned保存成2也成功

原因：只声明BOOLEAN却没有CHECK。

处理：定义CHECK(pinned IN(0,1))，应用入口也按布尔校验。

序列化时间出现8小时偏差

原因：把UTC约定的无时区DATETIME当本地时间解释。

处理：统一连接+00:00，读出后按已知UTC约定加timezone.utc，输出带时区ISO8601。

扩展练习

比较JSON扩展属性与tags关系表，说明标签唯一性应该放在哪里。

提示：JSON适合弱结构扩展，不自动提供跨行唯一和外键规则。

参考答案：

标签名在tags表使用UNIQUE，笔记标签在关联表维护外键。少量无需关系查询的附加属性可用JSON；热点JSON路径需显式生成列或函数索引并验证计划。

技术文档

• MySQL 8.4：CREATE TABLE与CHECK
• MySQL 8.4：时间类型
• MySQL 8.4：JSON

---

03 用PyMySQL参数化完成增删改查

建立显式提交、回滚和关闭的Python访问层，用lastrowid与rowcount确认结果。

本节目标

• 使用DictCursor和独立参数元组
• 正确读取lastrowid和受影响行数
• 在成功与异常路径释放事务和连接

必要概念

动态值通过驱动参数传递

PyMySQL execute(sql,params)的%s表示值，不加引号、不用f-string替换用户输入。标识符不是值参数，动态列名和排序方向用固定白名单。连接默认禁用多语句，不启用MULTI_STATEMENTS。

MySQL写入结果的读取方法

INSERT后从当前cursor.lastrowid取得生成编号，再SELECT同一行。UPDATE要根据rowcount及业务语义判断：默认统计实际变化行，相同值可能返回0；本课每次更新递增version，因此成功匹配一定发生变化。

显式管理生命周期

PyMySQL使用autocommit=False。本课完整脚本try中commit、except中rollback、finally中close，cursor上下文只关闭cursor，不代替数据库提交。连接读写超时是客户端等待预算，不等同于服务端事务整体超时。

动手实现

1. 安装同步驱动并设置环境

Bash沿用第01课生成文件。set -a与source只用于自己生成的本机文件，不source不可信文件。MySQL 8.4默认认证使用caching_sha2_password，安装PyMySQL的rsa附加依赖用于无TLS练习连接；生产应配置可信TLS。

文件：终端 / 当前课时

python -m pip install 'PyMySQL[rsa]>=1.1,<2'
set -a
source .mysql.env
set +a
unset MYSQL_ROOT_PASSWORD
export MYSQL_HOST=127.0.0.1 MYSQL_PORT=3307
python -m pip freeze > requirements-lock.txt

2. 保存统一连接函数

保存mysql_client.py；后续Redis模块直接复用。max_execution_time主要限制SELECT，innodb_lock_wait_timeout处理行锁，lock_waittimeout处理元数据锁；它们不是统一的所有SQL执行超时。生产另配置请求deadline和慢查询观测。此直接驱动入口使用MYSQL*，同步SQLAlchemy连接串才是mysql+pymysql://。

文件：mysql_client.py

import os
import pymysql
from pymysql.cursors import DictCursor

def connect():
    conn = pymysql.connect(
        host=os.environ.get("MYSQL_HOST", "127.0.0.1"),
        port=int(os.environ.get("MYSQL_PORT", "3307")),
        user=os.environ.get("MYSQL_USER", "app_user"),
        password=os.environ["MYSQL_PASSWORD"],
        database=os.environ.get("MYSQL_DATABASE", "notes_lab"),
        charset="utf8mb4", cursorclass=DictCursor, autocommit=False,
        connect_timeout=3, read_timeout=5, write_timeout=5,
    )
    try:
        with conn.cursor() as cur:
            cur.execute("SET SESSION time_zone = '+00:00'")
            cur.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED")
            cur.execute("SET SESSION innodb_lock_wait_timeout = 2")
            cur.execute("SET SESSION lock_wait_timeout = 3")
            cur.execute("SET SESSION max_execution_time = 3000")
        conn.commit()
        return conn
    except BaseException:
        conn.close()
        raise

3. 执行完整CRUD并显式结束事务

保存db_crud.py并运行。自建样本最后删除，可以重复执行。读取和修改都只使用自己的lastrowid；同一SQL更新version与updated_at。打印的pinned通常是0/1，业务JSON应按模型转成bool。

文件：db_crud.py

from mysql_client import connect

conn=connect()
try:
    with conn.cursor() as cur:
        cur.execute("INSERT INTO notes(title,content) VALUES(%s,%s)",("db03-O'Reilly","parameterized"))
        note_id=cur.lastrowid
        cur.execute("SELECT id,title,pinned,version FROM notes WHERE id=%s",(note_id,))
        print("created",cur.fetchone())
        cur.execute("UPDATE notes SET pinned=%s,version=version+1,updated_at=UTC_TIMESTAMP(6) WHERE id=%s",(True,note_id))
        assert cur.rowcount==1
        cur.execute("SELECT id,pinned,version FROM notes WHERE id=%s",(note_id,))
        print("updated",cur.fetchone())
        cur.execute("DELETE FROM notes WHERE id=%s",(note_id,))
        assert cur.rowcount==1
        cur.execute("SELECT id FROM notes WHERE id=%s",(note_id,))
        assert cur.fetchone() is None
    conn.commit()
except BaseException:
    conn.rollback()
    raise
finally:
    conn.close()

4. 验证SQL片段只成为普通文本

保存db_input.py。输入中包含单引号和DROP片段，驱动将其作为值处理；读取原值后回滚专用插入，不执行任何表删除命令。注意finally没有代替rollback。

文件：db_input.py

from mysql_client import connect

payload="db03-x'); DROP TABLE notes; --"
conn=connect()
try:
    with conn.cursor() as cur:
        cur.execute("INSERT INTO notes(title) VALUES(%s)",(payload,))
        note_id=cur.lastrowid
        cur.execute("SELECT title FROM notes WHERE id=%s",(note_id,))
        assert cur.fetchone()["title"]==payload
    conn.rollback()  # 仅验收，不保留样本
except BaseException:
    conn.rollback()
    raise
finally:
    conn.close()

5. 明确列表边界与不存在结果

回到mysql客户端执行。列表使用确定排序和LIMIT，更新不存在编号得到0 affected rows；PyMySQL不能用execute返回值当作整条数据库记录。生产API再将未找到与版本冲突区分成约定响应。

文件：终端 / 当前课时

SELECT id,title,pinned FROM notes WHERE pinned=0 ORDER BY id DESC LIMIT 10;
UPDATE notes SET pinned=1,version=version+1 WHERE id=-1;
SELECT ROW_COUNT() AS changed_rows;
-- 0

验收结果

• CRUD输出使用同一生成编号，更新后version增加。
• 输入SQL片段保持文本，数据库表仍可访问。
• 每条完整脚本都有commit或显式回滚，以及finally关闭连接。

生产约束与故障演练

浏览器输入将进入数据库，写入成功必须有可确认的结果。

实现决策

• 固定SQL与独立参数。
• 默认不开启多语句。
• 不在发生不确定写入结果后无条件重试。

隔离环境演练

对专用样本提交包含SQL语法的标题，再回滚并查询表完整性。

恢复验收

• 无SQL插值。
• 空结果和行数语义正确。
• 异常路径不泄漏连接。

常见错误

读取到的是1而不是记录字典

原因：把cursor.execute返回的受影响行数当作查询数据。

处理：SELECT后使用fetchone/fetchall，INSERT后使用lastrowid。

UPDATE相同内容返回0，被误判为不存在

原因：默认受影响行数反映实际修改。

处理：使用版本递增的条件更新，或明确启用FOUND_ROWS并理解行为；需要区分不存在与未变更时单独查询。

扩展练习

写一个rename_note(conn,id,title,expected_version)，返回是否修改成功，函数不自行提交。

提示：把WHERE版本比较、版本递增和时间更新放到一条UPDATE。

参考答案：

with conn.cursor() as cur:

cur.execute("UPDATE notes SET title=%s,version=version+1,updated_at=UTC_TIMESTAMP(6) WHERE id=%s AND version=%s",(title,id,expected_version))
return cur.rowcount==1

外层负责commit/rollback/close。更新0行可能不存在或冲突，需要时再读取区分。

技术文档

• PyMySQL：使用示例
• PyMySQL：连接参数
• MySQL 8.4：LAST_INSERT_ID

---

04 使用外键、JOIN与聚合建模标签

建立InnoDB多对多关系，验证唯一关联、外键索引和删除约束。

本节目标

• 设计tags和note_tags关系表
• 用LEFT JOIN保留零标签笔记
• 理解MySQL外键即时检查与索引要求

必要概念

联合主键表达一组关系

note_tags一行表示一条笔记与一个标签的关联，PRIMARY KEY(note_id,tag_id)防重复。标签名UNIQUE遵循表排序规则，默认ai_ci下Python和python可能冲突，需要时选择明确的区分大小写排序规则。

InnoDB即时检查外键

父记录必须先存在；MySQL不支持将此类外键检查推迟到COMMIT。InnoDB要求外键列有合适前缀索引，缺少时可能自动创建；本课显式声明tag_id索引便于审计。不要通过foreign_key_checks=0绕过正常业务写入。

LEFT JOIN后的计数要选列

未匹配标签的笔记仍有一条右侧NULL行。COUNT(*)会计为1，COUNT(nt.tag_id)得到0。连接更多一对多关系时注意行数放大，不直接对放大后的记录计数。

动手实现

1. 创建父表与关联表

保存sql/002_tags.sql，在notes_lab首次执行一次。删除笔记级联清理关联；被引用标签使用RESTRICT。显式存储引擎与字符集，避免外键因表属性不一致失败。

文件：sql/002_tags.sql

CREATE TABLE tags (
 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(40) NOT NULL UNIQUE,
 CONSTRAINT tags_name_nonempty CHECK(CHAR_LENGTH(TRIM(name))>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE note_tags (
 note_id BIGINT NOT NULL, tag_id BIGINT NOT NULL,
 PRIMARY KEY(note_id,tag_id),
 INDEX note_tags_tag_id_idx(tag_id),
 CONSTRAINT nt_note_fk FOREIGN KEY(note_id) REFERENCES notes(id) ON DELETE CASCADE,
 CONSTRAINT nt_tag_fk FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

2. 在当前会话准备独立样本

@tagged_id与@empty_id是当前mysql会话变量，换窗口需要重新设置。标签插入只首次运行；重复练习可直接复用已有两个标签，不使用INSERT IGNORE吞掉其他约束错误。

文件：终端 / 当前课时

INSERT INTO tags(name) VALUES('db04-python'),('db04-api');
INSERT INTO notes(title) VALUES('db04-with-tags');
SET @tagged_id=LAST_INSERT_ID();
INSERT INTO notes(title) VALUES('db04-no-tags');
SET @empty_id=LAST_INSERT_ID();
INSERT INTO note_tags(note_id,tag_id)
SELECT @tagged_id,id FROM tags WHERE name IN ('db04-python','db04-api');

3. 读取明细与零标签统计

第一条2行明细，第二条两个笔记的标签数分别2与0。group by显式包含所选笔记列，与默认ONLY_FULL_GROUP_BY一致。

文件：终端 / 当前课时

SELECT n.id,n.title,t.name FROM notes n
JOIN note_tags nt ON nt.note_id=n.id JOIN tags t ON t.id=nt.tag_id
WHERE n.id=@tagged_id ORDER BY t.name;
SELECT n.id,n.title,COUNT(nt.tag_id) AS tag_count FROM notes n
LEFT JOIN note_tags nt ON nt.note_id=n.id
WHERE n.id IN (@tagged_id,@empty_id)
GROUP BY n.id,n.title ORDER BY n.id;

4. 验证即时约束和级联删除

逐条执行预期失败语句。重复关联1062，删除被引用标签1451，不存在父标签的关联1452。最后删除本课带标签笔记，关联消失而标签本身仍保留。

文件：终端 / 当前课时

INSERT INTO note_tags(note_id,tag_id)
SELECT @tagged_id,id FROM tags WHERE name='db04-python';
-- 1062 duplicate entry
DELETE FROM tags WHERE name='db04-python';
-- 1451 parent row referenced
INSERT INTO note_tags(note_id,tag_id) VALUES(@empty_id,-999);
-- 1452 parent missing，立即失败
DELETE FROM notes WHERE id=@tagged_id;
SELECT COUNT(*) AS remaining FROM note_tags WHERE note_id=@tagged_id;
SHOW INDEX FROM note_tags;

验收结果

• 明细2行、计数2与0。
• 重复、被引用删除和无父关联分别被立即拒绝。
• 联合主键与tag_id辅助索引可在SHOW INDEX中确认。

生产约束与故障演练

笔记列表要展示标签，删除策略应与业务约定一致。

实现决策

• 外键即时检查。
• 关系使用约束与明确索引。
• 零关系结果单独测试。

隔离环境演练

用本课专用样本重复建立关系、删除被引用标签，并核对约束错误。

恢复验收

• 无孤儿关联。
• 零标签不丢行。
• 删除边界清晰。

常见错误

到COMMIT才希望补齐父行，但子行INSERT立即失败

原因：把其他系统的延迟外键习惯套到MySQL。

处理：先插父记录，再插子记录；需要复杂调度时重新规划写入顺序。

无标签笔记统计为1

原因：COUNT(*)把LEFT JOIN空关联行计入。

处理：改COUNT(nt.tag_id)，检查其他连接是否进一步放大。

扩展练习

查询带db04-python标签的笔记且每条最多一次。

提示：EXISTS直接表达是否存在，避免把标签明细带回结果。

参考答案：

SELECT n.id,n.title FROM notes n WHERE EXISTS(SELECT 1 FROM note_tags nt JOIN tags t ON t.id=nt.tag_id WHERE nt.note_id=n.id AND t.name='db04-python') ORDER BY n.id; 清理关联后可为空，重新准备样本后再验收。

技术文档

• MySQL 8.4：JOIN
• MySQL 8.4：外键
• MySQL 8.4：聚合

---

05 明确InnoDB事务、保存点与DDL提交边界

让必需写入一起提交，并验证错误语句、整个事务和结构变更的不同回滚范围。

本节目标

• 使用START TRANSACTION与显式ROLLBACK
• 区分语句失败和事务整体失败
• 验证DDL隐式提交不能用ROLLBACK撤销

必要概念

事务覆盖需要同时成功的动作

创建笔记和关联必需标签放在同一InnoDB事务。客户端收到异常后应按业务边界回滚，而不是看数据库是否仍允许SELECT就继续提交。外部HTTP、邮件或文件不随数据库事务自动回滚。

普通约束错误通常只回滚本条语句

MySQL中重复键或外键失败后，事务里的其他成功写入仍可能保留，事务也通常能继续执行。死锁1213会回滚整个事务；锁等待超时1205默认通常只回滚当前语句，受配置影响。应用统一结束失败业务事务可避免半成品。

DDL不是可随意回滚的DML

CREATE TABLE、ALTER TABLE等通常在执行前后隐式提交。MySQL原子DDL保护单条DDL的崩溃一致性，不表示BEGIN包起来的多条结构变更可以ROLLBACK。迁移文件要独立于业务事务。

动手实现

1. 执行笔记与标签一起成功的路径

在mysql客户端执行，先确认db04-api标签存在。没有DML RETURNING，紧接INSERT用同连接LAST_INSERT_ID。COMMIT后从另一个会话查询能看到笔记和关联。

文件：终端 / 当前课时

START TRANSACTION;
INSERT INTO notes(title) VALUES('db05-atomic-ok');
SET @ok_id=LAST_INSERT_ID();
INSERT INTO note_tags(note_id,tag_id)
SELECT @ok_id,id FROM tags WHERE name='db04-api';
COMMIT;
SELECT n.id,n.title,t.name FROM notes n
JOIN note_tags nt ON nt.note_id=n.id JOIN tags t ON t.id=nt.tag_id
WHERE n.id=@ok_id;

2. 让第二步失败后明确回滚整个业务

逐条执行。外键失败后SELECT仍能看到本事务第一步插入的笔记，这正是不能忽略异常后COMMIT的原因。ROLLBACK后在原会话和新会话查询该编号均为空。

文件：终端 / 当前课时

START TRANSACTION;
INSERT INTO notes(title) VALUES('db05-must-rollback');
SET @failed_id=LAST_INSERT_ID();
INSERT INTO note_tags(note_id,tag_id) VALUES(@failed_id,-999);
-- 1452：本条语句失败
SELECT id,title FROM notes WHERE id=@failed_id;
-- 本事务仍可看到第一步成功写入
ROLLBACK;
SELECT COUNT(*) AS remaining FROM notes WHERE id=@failed_id;
-- 0

3. 只对明确可选步骤使用保存点

本例业务允许笔记创建而标签失败。保存点之前写入保留，恢复到保存点后提交。必需标签不得借此绕过验证；AUTO_INCREMENT已消耗编号可能留下空洞。

文件：终端 / 当前课时

START TRANSACTION;
INSERT INTO notes(title) VALUES('db05-optional-tag');
SET @optional_id=LAST_INSERT_ID();
SAVEPOINT optional_tag;
INSERT INTO note_tags(note_id,tag_id) VALUES(@optional_id,-999);
ROLLBACK TO SAVEPOINT optional_tag;
RELEASE SAVEPOINT optional_tag;
COMMIT;
SELECT id,title FROM notes WHERE id=@optional_id;

4. 用专用表证明DDL隐式提交

仅notes_lab执行，一次实验会保留一条笔记和一张空表，再显式清理。CREATE TABLE之前INSERT因隐式提交已落库，随后ROLLBACK不能撤销它们。不要将此模式用于真实业务迁移。

文件：终端 / 当前课时

START TRANSACTION;
INSERT INTO notes(title) VALUES('db05-ddl-committed');
SET @ddl_id=LAST_INSERT_ID();
CREATE TABLE ddl_commit_probe(id INT PRIMARY KEY) ENGINE=InnoDB;
ROLLBACK;
SELECT id,title FROM notes WHERE id=@ddl_id;
SHOW TABLES LIKE 'ddl_commit_probe';
-- 都存在，证明ROLLBACK没有回滚DDL前的提交
DELETE FROM notes WHERE id=@ddl_id;
DROP TABLE ddl_commit_probe;

验收结果

• 失败后主动回滚，第一步笔记没有遗留。
• 保存点例子只保留业务明确允许的部分。
• 能观察DDL隐式提交，并清理仅本课创建的标记。

生产约束与故障演练

一次用户保存涉及多表，异常处理必须符合MySQL实际回滚语义。

实现决策

• DML事务显式提交回滚。
• DDL独立运行并设计恢复路径。
• 事务中不等待外部服务。

隔离环境演练

在notes_lab触发外键错误和DDL隐式提交，分别记录保留与回滚的记录。

恢复验收

• 无必需写入半成品。
• 错误码与回滚范围可解释。
• 不宣称BEGIN能包住所有DDL。

常见错误

标签失败但笔记仍保存

原因：约束错误通常只撤销本条SQL，应用继续提交了事务。

处理：except中显式rollback整个业务事务，再向调用方报告失败。

ROLLBACK没有撤销ALTER或CREATE

原因：DDL发生隐式提交，原子DDL不等于用户可回滚DDL事务。

处理：迁移拆分为可检查步骤、先备份和演练，避免与业务DML混用。

扩展练习

PyMySQL第二次execute触发1452时，最小异常处理是什么？

提示：不要根据SELECT还能运行就判断可以继续提交。

参考答案：

try内完成全部必需SQL后conn.commit；except BaseException中conn.rollback并raise；finally conn.close。若需要只保留部分成功，必须先定义业务允许的保存点范围。

技术文档

• MySQL 8.4：事务语句
• MySQL 8.4：隐式提交
• MySQL 8.4：InnoDB错误处理
• MySQL 8.4：保存点

---

06 用双会话观察RR、RC与当前读

按固定顺序比较MySQL默认RR快照、显式RC和带锁当前读。

本节目标

• 复现默认RR下的一致性读
• 显式切换RC观察新提交
• 区分快照SELECT与锁定读/UPDATE

必要概念

默认RR固定一致性读快照

InnoDB默认REPEATABLE READ。普通SELECT通常在事务第一次一致性读建立快照，后续普通读取使用相同快照；START TRANSACTION本身不一定已建立快照。长快照会增加undo保留与purge压力。

RC每次一致性读建立新快照

READ COMMITTED下不同SELECT可以读到期间新提交的值。SET TRANSACTION ISOLATION LEVEL只设置接下来事务，必须在START TRANSACTION之前且当前没有活动事务。普通读均不读取另一会话尚未提交的值。

UPDATE与锁定读不能按旧快照推断

SELECT FOR UPDATE与UPDATE使用锁定/当前读规则，可访问较新的已提交行。RR事务普通读仍见v1时，带锁读取可能见v2；后续写入可能覆盖他人编辑，因此版本条件仍必要。RR不自动把并发编辑变成版本冲突异常。

动手实现

1. 准备同一行和两个窗口

在一个mysql会话插入样本，记录编号；A/B都SET @iso_id=实际编号。不要假定样本id=1。每次切换隔离级别前双方执行ROLLBACK结束旧事务。

文件：终端 / 当前课时

INSERT INTO notes(title,content) VALUES('db06-isolation','v1');
SELECT LAST_INSERT_ID() AS experiment_id;
-- A、B均替换为刚返回的编号
SET @iso_id=123;
SELECT @@transaction_isolation;
-- 默认REPEATABLE-READ

2. 按窗口顺序观察默认RR

A建立快照后，B更新但不提交时A仍v1；B提交后A普通读也仍v1；A结束事务再读才v2。代码按注释切窗口执行，不要整段粘到单一窗口。

文件：终端 / 当前课时

-- A
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT content FROM notes WHERE id=@iso_id;
-- B
START TRANSACTION;
UPDATE notes SET content='v2',version=version+1,updated_at=UTC_TIMESTAMP(6) WHERE id=@iso_id;
-- A：v1
SELECT content FROM notes WHERE id=@iso_id;
-- B
COMMIT;
-- A：仍v1
SELECT content FROM notes WHERE id=@iso_id;
COMMIT;
SELECT content FROM notes WHERE id=@iso_id;
-- v2

3. 切为RC观察两次查询变化

延续v2。A显式RC读取，B自动提交更新v3后，A在相同事务的下一条SELECT读到v3。最后结束A事务，后续下一事务仍按会话默认RR，除非再显式设置。

文件：终端 / 当前课时

-- A
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT content FROM notes WHERE id=@iso_id;
-- B，确认autocommit=1
UPDATE notes SET content='v3',version=version+1,updated_at=UTC_TIMESTAMP(6) WHERE id=@iso_id;
-- A：现在为v3
SELECT content FROM notes WHERE id=@iso_id;
COMMIT;

4. 证明RR普通读与当前读可以不同

A普通读v3建立快照；B提交v4；A普通读仍v3，但FOR UPDATE读到v4并取得锁。此处只观察，不覆盖写入。A立即ROLLBACK释放锁；不能把这一实验推导为无条件覆盖更新是安全的。

文件：终端 / 当前课时

-- A
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT content FROM notes WHERE id=@iso_id;
-- B
UPDATE notes SET content='v4',version=version+1,updated_at=UTC_TIMESTAMP(6) WHERE id=@iso_id;
-- A：v3
SELECT content FROM notes WHERE id=@iso_id;
-- A：v4，带锁当前读
SELECT content FROM notes WHERE id=@iso_id FOR UPDATE;
ROLLBACK;

验收结果

• RR事务中保持旧值，结束后读取新值。
• RC同事务下一次SELECT能看到B新提交。
• RR普通读与FOR UPDATE结果不同，所有实验事务已结束。

生产约束与故障演练

报表导出与多人编辑同时运行，需要明确每种读操作所见的数据。

实现决策

• 默认RR与显式RC分别验收。
• 短事务降低undo保留压力。
• 当前读与普通快照读不混淆。

隔离环境演练

在同一笔记上执行固定双会话时序，记录每次提交和读取结果。

恢复验收

• 结果能对应具体时点。
• 无遗留长事务。
• 不把RR描述成自动乐观锁。

常见错误

切隔离级别报事务中不能修改

原因：已有事务尚未结束，或在START TRANSACTION之后才SET TRANSACTION。

处理：先ROLLBACK，再设置下一事务的级别，然后START TRANSACTION。

以为RR能防止旧表单覆盖新内容

原因：普通读取快照与UPDATE的锁定读规则不同。

处理：在UPDATE中加入expected version条件，或对需要串行的业务使用短锁定事务。

扩展练习

为什么多条报表SQL可用短RR只读事务，但编辑接口仍需版本检查？

提示：报表关心一致快照，编辑关心提交时是否有人已修改。

参考答案：

RR普通读取可以共享一致快照；编辑提交必须用WHERE id=? AND version=?做原子条件更新。不要让用户填写表单的整个过程占用数据库事务。

技术文档

• MySQL 8.4：隔离级别
• MySQL 8.4：一致性读
• MySQL 8.4：锁定读

---

07 观察行锁、间隙锁、死锁与版本冲突

使用独立实验行定位InnoDB等待，再用一致加锁顺序和条件版本更新约束并发。

本节目标

• 通过performance_schema定位等待链
• 复现RR范围锁与死锁
• 用版本条件阻止旧编辑覆盖

必要概念

锁依赖访问路径与隔离级别

InnoDB锁定索引记录。唯一主键等值查找通常只需目标记录锁；RR范围锁定读可能使用next-key/gap锁阻止范围插入。RC通常减少搜索与扫描的间隙锁，但外键和重复键检查等仍有例外，不能说RC完全没有间隙锁。

死锁与超时有不同回滚范围

循环等待会触发死锁检测，一方收到1213，整个事务被回滚；1205锁等待超时默认通常只回滚当前语句。应用遇到两类错误都显式ROLLBACK，并在事务外有限重试完整业务动作，不重试任意一条末尾SQL。

数据库锁与编辑版本分别使用

数据库锁保护短事务中的并发资源，用户编辑过程不应持有连接。UPDATE同时比较version并递增，rowcount=0表示未成功匹配，必须返回冲突或不存在而不是假装保存成功。

动手实现

1. 准备两个实验编号与三个窗口

业务窗口A/B连接app_user，分别记录CONNECTION_ID()。另开只读管理窗口用root连接本机notes-mysql，查看performance_schema需要额外权限，不把诊断权限授予API。记录两个新编号并在A/B各设置变量。

文件：终端 / 当前课时

INSERT INTO notes(title) VALUES('db07-first');
SELECT LAST_INSERT_ID();
INSERT INTO notes(title) VALUES('db07-second');
SELECT LAST_INSERT_ID();
-- A/B替换为实际编号
SET @first_id=124,@second_id=125;
SELECT CONNECTION_ID();
SET SESSION innodb_lock_wait_timeout=5;

2. 制造有上限的行锁等待

A先START TRANSACTION并锁第一行，B开始事务更新同一行会等待。等待期间管理窗口执行查询，记录blocking与waiting线程。B约5秒收到1205后ROLLBACK，A观察后也ROLLBACK；不要直接KILL任何未核对会话。

文件：终端 / 当前课时

-- A
START TRANSACTION;
SELECT id FROM notes WHERE id=@first_id FOR UPDATE;
-- B
START TRANSACTION;
UPDATE notes SET content='waiting',version=version+1 WHERE id=@first_id;
-- 管理窗口：在等待期间执行
SELECT REQUESTING_THREAD_ID,BLOCKING_THREAD_ID,REQUESTING_ENGINE_TRANSACTION_ID,
       BLOCKING_ENGINE_TRANSACTION_ID FROM performance_schema.data_lock_waits;
-- B超时后，以及A观察完后，各自执行
ROLLBACK;

3. 固定相反顺序复现死锁

先A锁第一行、B锁第二行，再A请求第二行进入等待，最后B请求第一行。其中一方收到1213，不能指定一定哪方被牺牲。另一方拿到锁后两边都结束事务。管理窗口SHOW ENGINE INNODB STATUS可查看最近死锁，输出可能含业务SQL，留存时脱敏。

文件：终端 / 当前课时

-- A
START TRANSACTION;
SELECT id FROM notes WHERE id=@first_id FOR UPDATE;
-- B
START TRANSACTION;
SELECT id FROM notes WHERE id=@second_id FOR UPDATE;
-- A等待
SELECT id FROM notes WHERE id=@second_id FOR UPDATE;
-- B触发死锁，1213出现在一个窗口
SELECT id FROM notes WHERE id=@first_id FOR UPDATE;
-- A/B均ROLLBACK
-- 管理窗口
SHOW ENGINE INNODB STATUS;

4. 以固定顺序加锁并保持短事务

A、B都按相同主键顺序锁两行。先确认全部旧事务结束，再A执行下列START与SELECT，B执行同样两条等待；A COMMIT后B继续，B再COMMIT。本课数量只有两行，不把大范围全表加锁作为默认方案。

文件：终端 / 当前课时

START TRANSACTION;
SELECT id FROM notes WHERE id IN (@first_id,@second_id) ORDER BY id FOR UPDATE;
-- A先COMMIT，B返回后再COMMIT
COMMIT;

5. 用独立范围表观察RR间隙保护

仅在notes_lab建立专用lock_range_probe，DDL在事务外。A RR锁[10,20]范围后B插入15会等待；B超时回滚、A回滚。再将A换READ COMMITTED重复，B通常可插入15，因为不再需要同样的范围间隙保护；两轮结束后清理专用表。

文件：终端 / 当前课时

CREATE TABLE lock_range_probe(id INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO lock_range_probe VALUES(10),(20),(30);
-- A
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT id FROM lock_range_probe WHERE id BETWEEN 10 AND 20 FOR UPDATE;
-- B，单独设置较短等待后执行
SET SESSION innodb_lock_wait_timeout=2;
INSERT INTO lock_range_probe VALUES(15);
-- B 1205后ROLLBACK；A也ROLLBACK。
-- A改READ COMMITTED，重复同一SELECT；B再INSERT(15)，应成功。
-- 所有事务结束后，只清理本课专用表
DROP TABLE lock_range_probe;

6. 原子比较旧版本并拒绝第二次覆盖

专用笔记先重置到version=1仅为实验，生产版本不得倒退。第一次更新成功1行，第二次仍带version=1匹配0行；SELECT确认内容仍为editor A。

文件：终端 / 当前课时

UPDATE notes SET version=1,content='base' WHERE id=@first_id;
UPDATE notes SET content='editor A',version=version+1,updated_at=UTC_TIMESTAMP(6)
WHERE id=@first_id AND version=1;
SELECT ROW_COUNT() AS changed_rows;
-- 1
UPDATE notes SET content='editor B',version=version+1,updated_at=UTC_TIMESTAMP(6)
WHERE id=@first_id AND version=1;
SELECT ROW_COUNT() AS changed_rows;
-- 0
SELECT content,version FROM notes WHERE id=@first_id;

验收结果

• 行锁等待能在管理窗口找到，1205后主动回滚。
• 死锁出现1213，固定顺序实验仅等待而不产生本例闭环。
• 范围实验与版本实验分别解释间隙保护和编辑冲突。

生产约束与故障演练

多人编辑和批量任务竞争同一批数据。

实现决策

• 管理诊断权限与业务权限分开。
• 加锁顺序一致，等待有上限。
• 版本比较与更新在同一SQL完成。

隔离环境演练

仅在专用行和lock_range_probe上制造等待与死锁，保存脱敏证据并结束所有事务。

恢复验收

• 无无限等待。
• 旧编辑无法覆盖已提交版本。
• 能区分1213与1205的回滚边界。

常见错误

设置较高隔离后插入也卡住

原因：RR范围锁可能保护间隙，更新不只锁住已存在行。

处理：核对索引访问范围和data_locks，缩小事务范围，按业务需要选择RC。

发生1205后继续COMMIT留下部分写入

原因：误以为所有数据库错误都会回滚整个事务。

处理：统一显式rollback失败业务，再有限重试；记录错误码与完整操作关联。

扩展练习

收到1213重试时，应复用旧计算结果还是重新读取整个事务？

提示：数据库已回滚被牺牲事务，外部事实可能变化。

参考答案：

回滚并重新建立事务、重新读取和计算，使用次数上限与抖动退避。任何外部副作用需要幂等键，不能因数据库回滚就断言邮件或文件也消失。

技术文档

• MySQL 8.4：InnoDB锁
• MySQL 8.4：死锁示例
• MySQL 8.4：data_lock_waits

---

08 设计组合索引、覆盖索引与JSON路径索引

按查询条件建立InnoDB索引，用不可见索引辅助对照，核算写入成本。

本节目标

• 从WHERE和ORDER BY推导索引顺序
• 理解聚簇主键与二级索引
• 为JSON路径创建受控的生成列索引

必要概念

InnoDB主键决定聚簇组织

主键索引叶子包含行数据，二级索引包含其索引键和主键值。选择过宽的主键会增加所有二级索引成本。查询返回列都在索引中时可能形成覆盖读取，仍需用实际计划确认。

组合索引依据固定查询设计

置顶列表WHERE pinned=1 ORDER BY created_at DESC,id DESC可使用(pinned,created_at DESC,id DESC)。低基数单列pinned索引未必划算。MySQL没有WHERE谓词形式的部分索引，本课用组合索引与实际选择率解决查询。

不可见索引与JSON索引各有边界

不可见索引默认不参与优化器选择，但仍维护并占用空间，适合可控验证，不是删除替代。JSON字段本身不能按任意路径自动获得B-tree索引；抽出稳定字段到生成列后建索引，需同时设计类型、长度和NULL语义。

动手实现

1. 有限批次生成50000条性能样本

保存db_seed_performance.py，只对notes_lab执行一次。每批500条提交，出错只回滚当前批，之前批次仍保留；再次运行会新增同量数据，不能假定脚本幂等。所有正文不超过16000字符。

文件：db_seed_performance.py

from datetime import datetime,timedelta
from mysql_client import connect

conn=connect()
try:
    for start in range(1,50001,500):
        rows=[]
        for number in range(start,min(start+500,50001)):
            utc_value=datetime(2026,1,1)+timedelta(seconds=number)
            rows.append((f"db08-bench-{number}","x"*200,number%100==0,utc_value,utc_value))
        with conn.cursor() as cur:
            cur.executemany("INSERT INTO notes(title,content,pinned,created_at,updated_at) VALUES(%s,%s,%s,%s,%s)",rows)
        conn.commit()
    print("inserted",50000)
except BaseException:
    conn.rollback()
    raise
finally:
    conn.close()

2. 保存原计划并建立组合索引

MySQL DDL发生隐式提交，因此在独立维护会话运行。ALGORITHM=INPLACE,LOCK=NONE请求在线二级索引创建，若当前操作或表条件不支持就报错而非假装完全无锁。仍可能需要短暂元数据锁，设置lock_wait_timeout。

文件：终端 / 当前课时

ANALYZE TABLE notes;
EXPLAIN SELECT id,title,created_at FROM notes WHERE pinned=1
ORDER BY created_at DESC,id DESC LIMIT 20;
SET SESSION lock_wait_timeout=3;
CREATE INDEX notes_pinned_created_id_idx ON notes(pinned,created_at DESC,id DESC)
ALGORITHM=INPLACE LOCK=NONE;
EXPLAIN SELECT id,title,created_at FROM notes WHERE pinned=1
ORDER BY created_at DESC,id DESC LIMIT 20;
SHOW INDEX FROM notes;

3. 用不可见索引做读计划对照

仅对刚创建的实验索引切换，不修改主键。观察key、rows和Extra变化，恢复VISIBLE后重新验收。不可见期间写入仍需维护该索引，不能用它证明删除索引后的写入收益。

文件：终端 / 当前课时

ALTER TABLE notes ALTER INDEX notes_pinned_created_id_idx INVISIBLE;
EXPLAIN SELECT id,title,created_at FROM notes WHERE pinned=1
ORDER BY created_at DESC,id DESC LIMIT 20;
ALTER TABLE notes ALTER INDEX notes_pinned_created_id_idx VISIBLE;
SELECT DATA_LENGTH,INDEX_LENGTH FROM information_schema.tables
WHERE table_schema='notes_lab' AND table_name='notes';

4. 在专用表给JSON路径建生成列索引

为避免改变贯穿课程notes模型，用独立json_index_probe。source取固定文本，JSON无该字段时生成NULL。查询直接使用生成列，避免依赖优化器是否把另一个表达式识别成相同形式。小表可能仍顺序扫描，目的先验证语义。

文件：终端 / 当前课时

CREATE TABLE json_index_probe (
 id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 payload JSON NOT NULL,
 source VARCHAR(32) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload,'$.source'))) STORED,
 INDEX source_idx(source)
) ENGINE=InnoDB;
INSERT INTO json_index_probe(payload) VALUES('{"source":"api","revision":1}'),('{"revision":2}');
SELECT id,source FROM json_index_probe ORDER BY id;
EXPLAIN SELECT id FROM json_index_probe WHERE source='api';
-- 本课专用表验收结束后清理
DROP TABLE json_index_probe;

验收结果

• 保存建索引前后真实计划，不要求固定成本数值。
• 不可见索引能恢复可见，明确仍有写维护成本。
• JSON路径生成列得到api与NULL，查询含义明确。

生产约束与故障演练

首页置顶查询变慢，需要同时控制读延迟和写入成本。

实现决策

• 索引对应明确查询。
• 维护DDL与业务事务分开。
• JSON路径类型与长度受约束。

隔离环境演练

在notes_lab比较可见/不可见计划和JSON路径结果，恢复索引可见状态。

恢复验收

• 有数据规模和计划证据。
• 不声称MySQL支持部分索引。
• 没有为每个字段盲目建索引。

常见错误

照搬WHERE pinned=1的CREATE INDEX报语法错误

原因：MySQL不支持谓词部分索引。

处理：使用与查询匹配的组合索引，或按业务设计受控生成列并验证访问路径。

在线建索引仍在等待

原因：LOCK=NONE不等于从不申请元数据锁，长事务可能阻塞DDL。

处理：设短lock_wait_timeout并检查元数据锁，安排维护窗口；不要关闭所有约束强行建表。

扩展练习

为什么SELECT id只读置顶列表比同时读取大content更容易覆盖索引？

提示：二级索引有索引列与主键，但不自动包含整个TEXT。

参考答案：

id、pinned、created_at可由组合二级索引提供；content通常需要回聚簇索引取行，扩大读取量。不要为覆盖随意把大正文加入索引，先限制输出字段并比较计划。

技术文档

• MySQL 8.4：多列索引
• MySQL 8.4：不可见索引
• MySQL 8.4：生成列索引
• MySQL 8.4：在线DDL

---

09 读懂EXPLAIN ANALYZE并优化深分页

对比估计JSON计划与实际TREE执行结果，用范围条件替代大OFFSET。

本节目标

• 区分EXPLAIN估计与ANALYZE真实执行
• 用实际rows和loops分析深分页
• 实现与排序一致的二字段游标

必要概念

MySQL的计划格式要对应版本

EXPLAIN FORMAT=JSON提供估计计划结构；MySQL 8.4的EXPLAIN ANALYZE使用TREE展示实际时间、行数和循环。ANALYZE会执行查询，本课只用于SELECT，不把写语句当作无副作用诊断。

深OFFSET需要处理跳过的数据

LIMIT 20 OFFSET 40000通常仍处理之前大量记录。连续滚动适合以最后一行的排序键定位下一页；任意跳页则需要另做产品与性能取舍，不能把取深游标的成本忽略掉。

复合游标展开成明确范围

按created_at DESC,id DESC排序，就筛选created_at<游标时间 OR(created_at=游标时间 AND id<游标id)。展开条件方便核对MySQL访问计划，不能仅假定行构造器比较一定利用完整复合索引。created_at应保持稳定。

动手实现

1. 建立列表索引并比较两种计划

继续使用第08课样本，CREATE INDEX首次执行一次。ANALYZE TABLE是更新统计，EXPLAIN ANALYZE是执行并测量查询，两者用途不同。读取节点actual time、rows、loops；不要把cost当毫秒。

文件：终端 / 当前课时

CREATE INDEX notes_created_id_idx ON notes(created_at DESC,id DESC);
ANALYZE TABLE notes;
EXPLAIN FORMAT=JSON SELECT id,title,created_at FROM notes
ORDER BY created_at DESC,id DESC LIMIT 20;
EXPLAIN ANALYZE SELECT id,title,created_at FROM notes
ORDER BY created_at DESC,id DESC LIMIT 20;

2. 记录深OFFSET额外扫描量

相同查询仅改变OFFSET，比较底层扫描节点的actual rows。设备、缓冲池和其他事务会影响毫秒数，保留数据规模及是否已预热，不预设固定数值。

文件：终端 / 当前课时

EXPLAIN ANALYZE SELECT id,title,created_at FROM notes
ORDER BY created_at DESC,id DESC LIMIT 20 OFFSET 40000;

3. 从第一页末尾读取游标

mysql会话变量通过SELECT INTO设置。实际API直接从已返回第20行取得时间和编号，不额外做OFFSET查询。下一页查询必须同时保留同样的过滤条件和排序方向。

文件：终端 / 当前课时

SELECT created_at,id INTO @cursor_time,@cursor_id FROM notes
ORDER BY created_at DESC,id DESC LIMIT 1 OFFSET 19;
SELECT id,title,created_at FROM notes
WHERE created_at<@cursor_time OR (created_at=@cursor_time AND id<@cursor_id)
ORDER BY created_at DESC,id DESC LIMIT 20;
EXPLAIN ANALYZE SELECT id,title,created_at FROM notes
WHERE created_at<@cursor_time OR (created_at=@cursor_time AND id<@cursor_id)
ORDER BY created_at DESC,id DESC LIMIT 20;

4. 在相近深度公平对照

教学为了获得第40000行游标，先花一次OFFSET成本，随后只比较游标查询本身。生产连续翻页用上页已有游标。若计划仍扫描大量行，观察OR范围与索引使用情况，而不是强行宣称游标一定更快。

文件：终端 / 当前课时

SELECT created_at,id INTO @deep_time,@deep_id FROM notes
ORDER BY created_at DESC,id DESC LIMIT 1 OFFSET 39999;
EXPLAIN ANALYZE SELECT id,title,created_at FROM notes
WHERE created_at<@deep_time OR(created_at=@deep_time AND id<@deep_id)
ORDER BY created_at DESC,id DESC LIMIT 20;
-- 第二次运行用于观察热缓冲条件，记录两次实际结果。

验收结果

• 区分JSON估计计划与TREE实际执行数据。
• 深OFFSET和深游标有可对照的扫描量记录。
• 时间相同的记录由id稳定排序，跨页无重复编号。

生产约束与故障演练

数据增长后后页延迟明显高于第一页。

实现决策

• 先量工作量再比较耗时。
• 游标字段稳定且唯一排序。
• 跨页请求不默认共享数据库快照。

隔离环境演练

在notes_lab对照浅页、深OFFSET和深游标，保存真实计划。

恢复验收

• 不忽略取游标成本。
• 无错误格式选项。
• 性能结论注明样本与缓存状态。

常见错误

EXPLAIN ANALYZE FORMAT=JSON不支持

原因：把不同版本或其他数据库的格式组合套到MySQL8.4。

处理：本课使用EXPLAIN FORMAT=JSON与EXPLAIN ANALYZE两条独立命令。

同一时间的笔记跨页重复

原因：只按时间排序或只使用一个时间游标。

处理：排序与筛选同时包含id，并完整保留其他列表条件。

扩展练习

给置顶列表添加游标条件，说明逻辑运算括号位置。

提示：pinned条件必须作用于OR的两个分支。

参考答案：

WHERE pinned=1 AND (created_at<%s OR(created_at=%s AND id<%s)) ORDER BY created_at DESC,id DESC LIMIT 20。参数传(time,time,id)。缺少外层括号会让后一OR分支绕过pinned过滤。

技术文档

• MySQL 8.4：EXPLAIN
• MySQL 8.4：LIMIT优化
• MySQL 8.4：行构造器优化

---

10 用有界连接池管理PyMySQL连接与事务

明确驱动不自带业务连接池，通过QueuePool复用连接并验证借还和耗尽恢复。

本节目标

• 建立进程级有界DBAPI连接池
• 明确commit/rollback与close归还语义
• 在连接损坏时失效而非暗中重连

必要概念

PyMySQL连接池需额外组件

PyMySQL是同步DBAPI驱动，本课使用SQLAlchemy QueuePool作为独立池组件，不引入ORM。pool_size加max_overflow是本进程连接上限，池按需创建连接；应用启动、关闭统一管理。

连接与事务都不能跨并发请求共享

线程借到各自连接后完成短事务，再close归还。池代理的close不是销毁底层连接，dispose关闭空闲连接；关闭时先停止工作并归还所有借出连接。多进程在子进程内各建池，不跨fork复用连接。

活跃事务禁止透明重连

网络断开后不要ping(reconnect=True)继续使用旧事务，数据库事务已经丢失。池借出前用ping(reconnect=False)发现坏连接并invalidate，交上层按业务语义重试。socket超时不能证明最后一个写入未提交。

动手实现

1. 建立独立池组件

安装SQLAlchemy2.0系列并更新锁文件。在db_pool.py保存下面代码，复用第03课mysql_client.connect及其UTC、RC和超时。没有把同步调用放进async路由；异步接入在API+数据库模块使用对应驱动与池。

文件：db_pool.py

# Shell先执行：python -m pip install 'SQLAlchemy>=2.0,<2.1'
from sqlalchemy.pool import QueuePool
from mysql_client import connect

pool=QueuePool(creator=connect,pool_size=2,max_overflow=0,timeout=0.2,
               recycle=300,reset_on_return="rollback")

def checkout():
    conn=pool.connect()
    try:
        conn.ping(reconnect=False)
        return conn
    except BaseException:
        conn.invalidate()
        conn.close()
        raise

2. 4个线程共享最多2条连接

保存db_pool_read.py。异常回滚失败时也必须在finally归还或失效；这里只读查询出错后把连接失效处理，避免把损坏连接交给下一请求。不要在持有连接时睡眠或执行外部HTTP。

文件：db_pool_read.py

from concurrent.futures import ThreadPoolExecutor
from db_pool import pool,checkout

def count_notes(_):
    conn=checkout()
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT COUNT(*) AS n FROM notes")
            value=cur.fetchone()["n"]
        conn.commit()
        return value
    except BaseException:
        try:
            conn.rollback()
        finally:
            conn.invalidate()
        raise
    finally:
        conn.close()

try:
    with ThreadPoolExecutor(max_workers=4) as workers:
        print(list(workers.map(count_notes,range(8))))
    print(pool.status())
finally:
    pool.dispose()

3. 验证异常插入明确回滚

保存db_pool_rollback.py。主动异常后rollback，再借到连接查询同一编号应0。代码不依赖连接上下文自动提交。SQLAlchemy池归还回滚是最后防线，不能替代显式业务事务控制。

文件：db_pool_rollback.py

from db_pool import pool,checkout

note_id=None
conn=checkout()
try:
    with conn.cursor() as cur:
        cur.execute("INSERT INTO notes(title) VALUES(%s)",("db10-rollback",))
        note_id=cur.lastrowid
    raise RuntimeError("simulated after insert")
except RuntimeError:
    conn.rollback()
finally:
    conn.close()
conn=checkout()
try:
    with conn.cursor() as cur:
        cur.execute("SELECT COUNT(*) AS n FROM notes WHERE id=%s",(note_id,))
        assert cur.fetchone()["n"]==0
    conn.commit()
finally:
    conn.rollback()
    conn.close()
    pool.dispose()

4. 占用全部连接后触发有界等待

保存db_pool_timeout.py。两条连接都借出时第三次借用约0.2秒后抛SQLAlchemy TimeoutError；释放后重新借用成功。这个超时与数据库行锁1205不是同一故障层。仅在独立练习进程执行。

文件：db_pool_timeout.py

from sqlalchemy.exc import TimeoutError
from db_pool import pool,checkout

first=checkout()
second=checkout()
try:
    try:
        unexpected=checkout()
    except TimeoutError:
        print("expected pool timeout")
    else:
        unexpected.close()
        raise AssertionError("pool exceeded configured limit")
finally:
    first.rollback();first.close()
    second.rollback();second.close()
conn=checkout()
try:
    with conn.cursor() as cur:
        cur.execute("SELECT 1 AS ok")
        assert cur.fetchone()["ok"]==1
    conn.commit()
finally:
    conn.rollback();conn.close();pool.dispose()

验收结果

• 并发读取完成且最大连接数为2。
• 异常插入回滚，连接归还后可以继续使用。
• 耗尽产生池等待超时，释放后恢复，不盲目扩池。

生产约束与故障演练

API与worker同时访问数据库，突发流量不能无限增加连接。

实现决策

• 进程内池、进程间总预算。
• 事务显式结束，断线连接失效。
• 池等待、网络等待、数据库锁等待分开监控。

隔离环境演练

在独立进程耗尽2条连接并触发插入回滚，观察归还恢复。

恢复验收

• 无泄漏。
• 等待有界。
• 坏连接不携带假事务继续执行。

常见错误

关闭池后仍有数据库连接

原因：还有借出连接或工作线程未结束。

处理：先停止接收任务并等待有界工作结束，归还连接后dispose。

断线后自动重连并继续提交

原因：新连接不包含旧事务状态。

处理：禁用事务中透明重连，失效旧连接并按幂等性重试完整业务动作。

扩展练习

4个API进程每池8条、2个任务进程每池5条，再留10条运维，最低连接预算是多少？

提示：每个进程有独立池，不能只算一个进程。

参考答案：

4×8+2×5+10=52条，还需加迁移、监控和其他服务余量。max_connections不能只机械等于52，数据库内存与请求排队策略也应验收。

技术文档

• SQLAlchemy：独立池用法
• PyMySQL：连接参数
• MySQL 8.4：连接过多

---

11 按账号host范围拆分权限与连接配置

为API创建最小读写账号，区分DDL、业务查询和运维诊断权限。

本节目标

• 识别user@host授权匹配
• 授予现有与未来业务表的数据库级权限
• 配置TLS、凭据和每次连接的会话参数

必要概念

MySQL账号包含来源host

app_user@%与app_user@localhost是两个账号。CURRENT_USER()显示最终匹配的授权身份。Docker练习通过网络连接，用%仅方便独立网络测试；生产使用受控网络范围、TLS与独立密码，不能把用户名相同当成同一身份。

数据库级授权覆盖该库未来表

GRANT SELECT,INSERT,UPDATE,DELETE ON notes_lab.*可覆盖现有和未来对象，不需要逐表自动权限模板。它范围比单表授权宽，生产根据服务边界选择。运行账号不授予CREATE、ALTER、DROP或全局管理权限。

配置责任不能靠账号默认猜测

MySQL普通账号没有通用的每账号SET默认会话变量机制供本例照搬；应用connect统一设置UTC、RC、锁等待和SELECT时限。TLS要校验证书与主机名，密码轮换后新建连接验证，再逐步回收旧连接。

动手实现

1. 管理连接创建运行身份

docker exec -it notes-mysql mysql -u root -p后执行；密码在专用本机终端填写，不使用示例密码部署。mysql客户端将凭据命令写历史的风险可用该交互进程MYSQL_HISTFILE=/dev/null禁用，或使用受控管理员工具。下面账号仅一次创建。

文件：终端 / 当前课时

CREATE USER 'notes_runtime'@'%' IDENTIFIED BY 'REPLACE_WITH_LOCAL_RANDOM_PASSWORD';
GRANT SELECT,INSERT,UPDATE,DELETE ON notes_lab.* TO 'notes_runtime'@'%';
SHOW GRANTS FOR 'notes_runtime'@'%';
SHOW GRANTS FOR 'app_user'@'%';

2. 以运行身份验证允许与拒绝

新终端使用mysql --protocol=TCP -h127.0.0.1 -P3307 -u notes_runtime -p notes_lab连接。CRUD成功但ALTER被拒绝，专用样本插入后立即删除；不得给运行账号补DDL权限来消除这个预期错误。

文件：终端 / 当前课时

SELECT CURRENT_USER(),DATABASE();
SET SESSION time_zone='+00:00';
INSERT INTO notes(title) VALUES('db11-runtime');
SET @runtime_id=LAST_INSERT_ID();
DELETE FROM notes WHERE id=@runtime_id;
ALTER TABLE notes ADD COLUMN should_not_exist INT;
-- 1142：ALTER command denied

3. 验证未来表与会话超时

app_user创建permission_probe后，notes_runtime可SELECT，说明库级授权覆盖未来表；验收后由app_user删除。超时是当前连接设置：MAX_EXECUTION_TIME主要用于SELECT，本例SLEEP可用于观察；客户端不同也应重做连接配置。

文件：终端 / 当前课时

-- app_user
CREATE TABLE permission_probe(id INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO permission_probe VALUES(1);
-- notes_runtime
SELECT id FROM permission_probe;
SET SESSION max_execution_time=1000;
SELECT SLEEP(2);
SHOW WARNINGS;
-- SLEEP可能返回1表示被中断；以实际状态与耗时验收，勿依赖固定异常
-- app_user验收后
DROP TABLE permission_probe;

4. 为生产连接开启校验而不破坏练习配置

在mysql_client.connect的pymysql.connect参数中按实际证书增加这些参数，不将占位路径直接用于本机无证书环境。MYSQL_HOST应匹配证书主机名，密钥不写进仓库。应用MYSQL_USER改为notes_runtime；迁移仍使用单独app_user凭据。

文件：终端 / 当前课时

# pymysql.connect(...) 生产TLS参数片段：
# ssl_ca=os.environ["MYSQL_SSL_CA"],
# ssl_verify_cert=True,
# ssl_verify_identity=True,
# host=os.environ["MYSQL_HOST"],

# 业务连接成功后可查询（不要记录秘密）：
# SHOW SESSION STATUS LIKE 'Ssl_cipher';
# SELECT CURRENT_USER(), @@session.time_zone, @@transaction_isolation;

验收结果

• 运行账号CRUD可用但ALTER被拒绝。
• 未来表权限符合数据库级授权范围。
• 实际连接身份、TLS和UTC/RC设置均可核对。

生产约束与故障演练

运行配置泄漏或应用代码出错时，需要限制影响范围。

实现决策

• 迁移、运行和诊断账号分开。
• host匹配与网络访问共同限制。
• 每次连接主动设置会话参数。

隔离环境演练

用notes_runtime尝试ALTER并验证拒绝，然后核对正常读写不受影响。

恢复验收

• 非root运行。
• 拒绝错误有明确原因。
• 生产TLS验证不被省略。

常见错误

本机能登录、容器连接却被拒绝

原因：账号host条目和连接来源不同。

处理：核对CURRENT_USER、USER及已创建账号来源范围，不通过授予root远程权限绕过。

配置ssl=True就认为已验证服务器身份

原因：加密与证书链、主机名验证是不同要求。

处理：设置可信CA并启用ssl_verify_cert和ssl_verify_identity，使用证书匹配主机名。

扩展练习

给只读报表建立权限，并说明PROCESS应不应该顺手授予。

提示：业务查询所需权限与全实例诊断不同。

参考答案：

独立报表账号授予指定库/表SELECT，必要时只授予特定视图；PROCESS和performance_schema广泛诊断权限单独给运维身份。报表连接也设置时限，不能仅靠只读权限防止昂贵查询。

技术文档

• MySQL 8.4：GRANT
• MySQL 8.4：账号名称
• MySQL 8.4：加密连接
• PyMySQL：TLS参数

---

12 用mysqldump恢复并验收MySQL结构变更

恢复到另一个数据库验证数据与自增，再为新增列设计独立的兼容迁移。

本节目标

• 用single-transaction备份InnoDB并恢复到隔离库
• 理解mysqldump与binlog时间点恢复的范围
• 验证DDL隐式提交和INSTANT操作限制

必要概念

逻辑备份需要匹配写入与DDL条件

mysqldump --single-transaction为InnoDB建立一致性读取，不保证非事务表一致，也不适合备份同时执行ALTER/DROP等DDL。dump包含SQL，恢复前核对来源；单业务库备份不会自动包含完整账号和授权配置。

时间点恢复需要备份加连续binlog

每日dump不满足15分钟RPO。需要记录与备份一致的binlog坐标或GTID以及保留完整日志，再在隔离环境恢复到明确停止点。binlog复制和本机留存不等于异地备份；不可在原线上库盲目重放。

兼容迁移不依赖DDL回滚

新增可空列可先兼容旧代码，再逐步上线使用。MySQL 8.4对符合条件的加列支持ALGORITHM=INSTANT，明确要求可在不支持时失败；即使INSTANT也需要元数据锁，且DDL隐式提交，ROLLBACK不能撤销。

动手实现

1. 准备客户端凭据文件

用编辑器在notes-service创建.mysql-client.cnf，按下面INI结构填写本机练习密码。此处示例占位值必须替换；权限设置为600，目录只供当前用户访问。不要把凭据放进mysqldump命令参数或共享日志。

文件：.mysql-client.cnf

[client]
host=127.0.0.1
port=3307
protocol=TCP
user=app_user
password=REPLACE_WITH_LOCAL_APP_PASSWORD
default-character-set=utf8mb4

2. 用凭据文件生成逻辑备份

以下Linux Bash需要MySQL8.4客户端。先在受保护.mysql-client.cnf保存client的host=127.0.0.1、port=3307、user=app_user和password；chmod600，不提交。暂停本次练习写入与DDL以便后续行数对照。--set-gtid-purged=OFF使本课单库恢复不改目标GTID集合；生产时间点恢复另行规划坐标。

文件：终端 / 当前课时

mkdir -p backups
chmod 700 backups
chmod 600 .mysql-client.cnf
mysqldump --version
mysqldump --defaults-extra-file=.mysql-client.cnf \
  --single-transaction --quick --no-tablespaces --set-gtid-purged=OFF \
  notes_lab --result-file=backups/notes_lab.sql
# 前一命令成功退出后再继续；shell脚本应开启set -e。
sha256sum backups/notes_lab.sql > backups/notes_lab.sql.sha256
mysql --defaults-extra-file=.mysql-client.cnf -N -B notes_lab \
  -e 'SELECT COUNT(*),MIN(id),MAX(id),COALESCE(SUM(pinned=1),0) FROM notes' > backups/source-notes.txt
sha256sum -c backups/notes_lab.sql.sha256

3. 创建独立恢复库并授予恢复身份

仅本机root管理连接执行。不要使用--databases生成固定USE notes_lab后再误以为命令参数能改目标；本课dump只带表SQL，显式导入notes_restore。notes_restore只能作为验收库，不能覆盖notes_lab。

文件：终端 / 当前课时

CREATE DATABASE notes_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
GRANT ALL PRIVILEGES ON notes_restore.* TO 'app_user'@'%';
-- 只针对本机隔离恢复库，恢复完成后按运行账号权限方案重新授权。

4. 恢复后比较行数、关系与自增

Bash导入前使用SELECT DATABASE确认目标；mysql --force会跳过错误继续执行，本课不使用。下面数量一致只适用于已暂停写入的练习源库，不能拿备份后持续变化的数据简单判错。

文件：终端 / 当前课时

mysql --defaults-extra-file=.mysql-client.cnf notes_restore -e 'SELECT DATABASE();'
mysql --defaults-extra-file=.mysql-client.cnf notes_restore < backups/notes_lab.sql
mysql --defaults-extra-file=.mysql-client.cnf -N -B notes_restore \
  -e 'SELECT COUNT(*),MIN(id),MAX(id),COALESCE(SUM(pinned=1),0) FROM notes' > backups/restored-notes.txt
diff backups/source-notes.txt backups/restored-notes.txt
mysql --defaults-extra-file=.mysql-client.cnf notes_restore \
  -e 'SELECT COUNT(*) FROM note_tags nt LEFT JOIN notes n ON n.id=nt.note_id WHERE n.id IS NULL; SHOW CREATE TABLE notes;'

5. 验证恢复库新写入与源库兼容迁移

先在notes_restore执行前一组DML，LAST_INSERT_ID生成合法编号后ROLLBACK，不保留标记。再明确USE notes_lab执行独立DDL；不要包BEGIN期待可回滚。ALGORITHM=INSTANT失败时先评估支持限制，不能静默退回可能重建整表的操作。

文件：终端 / 当前课时

USE notes_restore;
SET SESSION time_zone='+00:00';
START TRANSACTION;
INSERT INTO notes(title) VALUES('db12-restore-probe');
SELECT LAST_INSERT_ID();
ROLLBACK;

USE notes_lab;
SELECT DATABASE();
SET SESSION lock_wait_timeout=3;
ALTER TABLE notes ADD COLUMN archived_at DATETIME(6) NULL, ALGORITHM=INSTANT;
SELECT id,title,pinned FROM notes ORDER BY id DESC LIMIT 3;
SELECT id,title FROM notes WHERE archived_at IS NULL ORDER BY id DESC LIMIT 3;
-- 旧查询和新查询都可工作；DDL不能用ROLLBACK撤销。

6. 记录binlog恢复演练所需证据

本课逻辑恢复已可执行，时间点恢复部分按真实环境补录，不能冒填文件名与GTID。管理窗口只读查看变量；不要将mysqlbinlog输出直接管道写入现有业务库。生产选择持久日志归档方案并在隔离实例恢复到误操作之前，验收角色、时区、索引和业务请求。

文件：终端 / 当前课时

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
SHOW BINARY LOG STATUS;
-- 保存：备份时间/一致快照对应日志坐标或GTID/日志保留与异地副本
-- 演练：隔离恢复实例、停止点、实际恢复耗时、允许丢失窗口、业务验收结果

验收结果

• dump退出成功、校验和正确，独立恢复库数量与静止源库一致。
• 外键无孤儿，恢复库新插入自增正常且能回滚DML。
• 新增列前后旧查询兼容，明确DDL不能由ROLLBACK撤销。

生产约束与故障演练

结构发布前需要证明可恢复，且新旧应用能在过渡期并存。

实现决策

• 恢复库与源库隔离。
• 备份验证覆盖数据、外键、自增与权限。
• DDL单独执行，明确兼容与恢复方案。

隔离环境演练

先逻辑恢复到notes_restore，再在notes_lab新增可空列；时间点恢复需真实日志与明确停止点后单独执行。

恢复验收

• 有实际恢复证据后才标通过。
• 未把本机耗时推定为线上SLA。
• 不以删库或删卷作为常规回退。

常见错误

恢复导入竟修改原库

原因：dump包含固定CREATE DATABASE/USE，或连接目标未核对。

处理：本课不加--databases，导入前核对SQL内容与SELECT DATABASE；生产先隔离恢复。

每日dump被认为能恢复到任意一分钟

原因：没有完整binlog和一致起点。

处理：规划备份坐标/GTID、日志保留与归档，并实际演练恢复停止点。

INSTANT加列仍等待或不支持

原因：元数据锁冲突或表/操作条件不满足。

处理：短lock_wait_timeout失败后查阻塞，确认版本和限制，不盲目降级成大表复制。

扩展练习

业务最多丢15分钟数据，只有每日mysqldump是否足够？还需验收什么？

提示：把备份间隔、binlog归档延迟、恢复速度分别对齐RPO/RTO。

参考答案：

不够。需要与一致性备份关联的连续binlog或受管时间点恢复，监控归档延迟与保留期，保护异地副本；实际恢复到指定停止点并测量RTO，同时重建权限和验收业务读写。

技术文档

• MySQL 8.4：mysqldump
• MySQL 8.4：时间点恢复
• MySQL 8.4：在线DDL操作
• MySQL 8.4：隐式提交