MySQL 8.0.39：索引、事务与 SQL 手写实验

教材入口：索引与执行、MVCC与锁、诊断与恢复、SQL手写。所有表均为专用教学数据，不连接业务库。

实验文件可逐份下载，保存时保留文件名，并将下列文件放入同一个 labs/mysql 目录，再从仓库根目录执行本文命令。

用途	文件
本机运行、教学模型与关系结果校验	run_local.py、models.py、verify_practice.py
索引造数与执行计划	init.sql、queries.sql
事务夹具、A/B交接与只读观察	setup.sql、sessions.sql、observe.sql
SQL手写固定数据与查询	practice.sql
8.0.39容器配置与入口	docker-compose.yml、run.sh

已运行与未运行

2026-10-23最初Docker daemon不可用、常见PATH无mysql/mysqld；随后用户提供本机TCP服务，临时客户端确认 MySQL Community 8.4.7，全局RR、16KB页、deadlock_detect=1、rollback_on_timeout=0、innodb_flush_log_at_trx_commit=1、sync_binlog=1。客户端显式autocommit=True，不把驱动默认事务状态误认全局配置。

已真实运行8.4.7索引计划与双会话；源码与Docker教学基线仍是 8.0.39，该镜像未运行。原始输出分别在忽略目录 output/t16-local-plans.json 与 output/t16-local-tx.json；单次耗时不当基准，复制、崩溃和在线DDL未运行。同份SQL手写夹具在8.4.7真实执行15项全部通过，ROUND四位精度与SQLite固定预期一致，证据为 output/t16-local-practice.json；唯一新库已清理。

本机服务的隔离实验

实验代码

python3 -m venv /tmp/mysql-study-client
/tmp/mysql-study-client/bin/pip install PyMySQL sqlparse
/tmp/mysql-study-client/bin/python labs/mysql/run_local.py --mode all

run_local.py 默认127.0.0.1:3306/root/空密码；其他密码从MYSQL_PWD读取，不写日志。每次生成 study_t16_日期_随机后缀_*，以不带IF NOT EXISTS的CREATE拒绝碰撞，只跟踪本次新建库，并在finally关闭连接后删除这些库。它不会DROP/重置已有study/study_tx或其他用户库，不改服务与全局配置。--mode plans|tx|practice|all可只复现一组。

plans读取同一份init/queries，跳过其原专用库CREATE/USE，改在全新隔离库执行；tx读取同一份setup并按明确A/B交接制造等待，第三连接只观察本次schema。practice读取同份SQL夹具与固定预期。8.4.7通过不是8.0.39语法或计划通过；换服务版本须记录差别。

离线入口可独立运行：

实验代码

python3 labs/mysql/models.py all
python3 labs/mysql/verify_practice.py

前者是 Python 教学推演，验证确定性相关输入/offset/视图选择/写偏差/等待环/回填版本条件；不实现优化器、物理页或锁调度。后者实际读取同份 practice.sql 的固定数据和查询，在 SQLite 执行并比较预先写定结果，用于检查关系语义；不能证明 MySQL 8.0.39 的语法、类型、排序规则、窗口实现或计划。本轮SQLite 3.54.0的15项固定结果全部通过；MySQL 8.4.7的独立实跑范围见本页“已运行与未运行”，执行任一校验入口可逐项复查预期与实际值。

真实 MySQL 的四个入口

前提是 Docker daemon 和 Compose 已由使用者准备好。本目录镜像固定 mysql:8.0.39，InnoDB 16KB 页、256MB buffer pool，宿主3307→容器3306，专用容器 study-mysql-8039。脚本确认 SELECT VERSION() 恰为8.0.39；换版本直接失败，避免无意漂移。

实验代码

bash labs/mysql/run.sh plans       # 默认入口；重建study.orders并造100000行
bash labs/mysql/run.sh practice    # 重建study_sql中的pr_*手写夹具
bash labs/mysql/run.sh setup       # 重建study_tx中的双会话夹具
bash labs/mysql/run.sh observe     # 只读第三连接快照，不重置会话夹具

run.sh 启动本实验容器、最多等待60次ping，每次间隔2秒；输出在本目录 out/。plans输出 explain.txt，practice输出 practice.txt，setup输出 setup.txt，observe输出 observe.txt。真正运行后仍须核对预期，不以脚本退出0代替结果分析。

需要交互会话时分别打开两个终端，复制执行：

实验代码

cd labs/mysql
docker compose exec -e MYSQL_PWD=labpass mysql mysql -uroot study_tx

A/B 严格按 sessions.sql 的交接标签执行，观察连接运行 observe。这是并发协议，不是顺序批处理文件。每组结束两连接均显式ROLLBACK，确认没有活动事务后再用 setup恢复数据；不能在A仍持锁时重置表。账户/间隙/条件扣减/死锁/医生写偏差五组输入均保留。

仅重置本实验：在本目录执行 docker compose down -v。它删除本实验容器与其卷；没有授权连接其他服务或清理用户浏览器记录。

造数与 Q1–Q14

orders(id,user_id,status,amount,created_at,note) 主键id，二级索引 (user_id,status,created_at)、(status,created_at)、(created_at)。init.sql确定性生成100000行，user_id=n%10000、status=ELT(1+n%4,...)、时间分布于2025年。

这两个取模产生相关性：1234只有SHIPPED，1233只有PAID，各10行。原始Q1–Q9保留；Q10显式查基数，避免把空范围当成低回表证据。

查询	输入与观察点
Q1/Q2	原1234+PAID空范围；全行与覆盖投影，不用于推断命中成本
Q3	user前缀+LIKE '%A%'，观察ICP与服务层过滤
Q4/Q9	时间范围/状态时间范围，检查key、排序与实际扫描
Q5/Q6	LIMIT90000,20 vs id>90000 LIMIT20，同数据同页
Q7/Q8	覆盖计数、缺失联合索引中间前缀
Q10	实际两用户状态基数、空范围0/命中10对照
Q11/Q12	1233命中范围的传统EXPLAIN与EXPLAIN ANALYZE，比较取列路径
Q13/Q14	同一个1233输入，对照ICP on/off，最后恢复会话optimizer_switch

真实执行后记录 type/key/key_len/rows/filtered/Extra 与实际rows/loops/time，不要求固定计划文本。Q2通常可覆盖，但二级索引的MVCC可见性分支仍可能查聚簇记录。EXPLAIN估算与实际迭代、逻辑访问与物理I/O必须分开，EXPLAIN ANALYZE真的执行，不可用于危险生产写。

保存证据与反例

字段	运行后填写
日期、机器、镜像/版本、页大小	保存原始SELECT/SHOW，不凭文档默认值
表行数/用户状态基数/统计信息	包括0/10对照、SHOW INDEX与ANALYZE TABLE
Q1–Q14原始计划	保存传统估算与实际迭代，说明冷热与参数
A/B隔离、autocommit、SQL顺序	保存交接点，锁模式与等待边；不固定页号/lock id
死锁日志、1205配置、事务最终值	不硬编码哪一方为受害者，明确回滚清理
SQL手写结果	对照固定fixture；差异先调查类型/NULL/排序规则
假设未满足与未知	原样保留，不改输出迎合预期

100000行、单机、合成分布不能代表生产；这里没有做吞吐压测、长快照purge增长、崩溃恢复、复制/选主、在线DDL、机房故障或MySQL源码编译。教学模型及SQLite夹具的断言通过也不补足这些限制。