简介本资源是华中科技大学《数据库系统原理实践——以MySQL为例》课程的配套实验材料包面向计算机专业本科生及数据库初学者旨在通过系统化实操帮助学习者深入理解数据库核心原理与工程实现。资源共92个文件主体为62个SQL脚本覆盖建库建表、查询优化、存储过程、触发器、事务隔离、并发控制等14类实验辅以7个Java应用开发示例、6个C编写的B树索引实现源码、4个Shell备份恢复脚本及文档类文件含任务书、评分细则、报告模板、结构图等压缩包仅1.42MB轻量易用。已有66人下载学习。资源按实验模块分文件夹组织命名规范清晰如“6. MySQL - 存储过程与事务”每个实验内SQL文件按关卡序号编号配合说明文档与可视化图表drawio、jpg、png便于循序渐进完成从理论到代码落地的完整训练闭环。1. 这不是《数据库系统原理》的课件压缩包而是一套能跑通、能调试、能扣细节的 MySQL 实战沙盒你下载的这个华中科技大学 数据库系统原理实践 - 以MySQL为例.zip表面看是高校课程配套资源实际是一份被反复打磨过的「原理落地脚手架」它不讲ACID定义而是让你亲手在事务隔离级别下复现「幻读」不画B树示意图而是用EXPLAIN FORMATTREE看索引如何被真正选用不罗列SQL语法而是提供带完整约束冲突回滚逻辑的银行转账存储过程模板。我带过三届数据库课设学生最常卡在「知道概念但写不出可验证的SQL」——这个压缩包就是为解决这个断层设计的所有实验都基于真实 MySQL 8.0 环境非模拟器每个.sql文件自带-- 验证点注释执行后立刻用SELECT检查状态而不是等期末报告才敢碰数据。适合两类人一是刚学完关系代数、范式理论急需把纸面知识焊进mysql提示符里的本科生二是想快速搭建教学演示环境、避免学生在环境配置上耗掉3小时的助教。它不替代教材但能让教材里的每一章变成可触摸的终端输出。2. 解压即用从零构建可验证的 MySQL 实验环境含版本兼容性兜底这个压缩包的设计哲学是「最小依赖、最大确定性」。它不假设你已装好 MySQL也不要求你用 Docker 或虚拟机——所有实验脚本默认适配本地原生安装的 MySQL 8.0.28Ubuntu/Debian/CentOS 7/macOS 12同时对常见降级场景做了兼容处理。下面是你解压后必须做的三件事顺序不能错。2.1 解压结构与核心文件定位解压后你会看到清晰的分层目录Huazhong_Uni_DB_Practice/ ├── setup/ │ ├── mysql_install_check.sh # 自动检测本地 MySQL 版本与 socket 路径 │ └── init_db_env.sql # 创建实验专用数据库 hzu_db_practice 及用户 hzu_user ├── labs/ │ ├── lab01_transaction_isolation/ # 事务隔离级别实验含 READ-COMMITTED/REPEATABLE-READ 对比 │ ├── lab02_index_optimization/ # B树索引实战覆盖索引、最左前缀、索引下推验证 │ ├── lab03_stored_procedure/ # 带异常处理的存储过程银行转账 余额校验 事务回滚 │ └── lab04_query_execution_plan/ # 执行计划深度解析Using index condition, Using filesort, LooseScan ├── data/ │ └── sample_schema.sql # 包含 student/course/enroll 三张表的 DDL1000 行模拟数据 └── docs/ └── lab_guidelines.md # 每个实验的预期输出、失败排查路径、评分关键点提示不要直接运行setup/init_db_env.sql先执行setup/mysql_install_check.sh—— 它会自动识别你的 MySQL 安装路径/usr/local/mysql/bin或/opt/homebrew/bin、socket 文件位置/tmp/mysql.sock或/var/run/mysqld/mysqld.sock并检查是否满足innodb_file_per_tableON等实验必需参数。若检测失败脚本会明确告诉你缺什么例如ERROR: MySQL version 8.0.28, please upgrade而不是让你在报错后大海捞针。2.2 一键初始化实验数据库含权限与字符集强约束init_db_env.sql不是简单的CREATE DATABASE它强制启用实验所需的底层行为-- setup/init_db_env.sql 关键片段 SET GLOBAL innodb_strict_mode ON; -- 禁止隐式类型转换暴露设计缺陷 SET GLOBAL sql_mode STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO; CREATE DATABASE IF NOT EXISTS hzu_db_practice CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER hzu_userlocalhost IDENTIFIED BY H3uZ!2024; GRANT ALL PRIVILEGES ON hzu_db_practice.* TO hzu_userlocalhost; FLUSH PRIVILEGES; -- 强制设置时区避免 timestamp/datetime 行为差异 SET GLOBAL time_zone 08:00;这段 SQL 的价值在于它让lab03_stored_procedure中的DECLARE EXIT HANDLER FOR SQLEXCEPTION能真正捕获到INSERT INTO account VALUES (1, -100)这类违反CHECK(balance 0)的操作并触发回滚——如果没开innodb_strict_modeMySQL 会静默截断或转成 0导致学生误以为存储过程没生效。2.3 加载样本数据并验证完整性避免「数据没导入」型翻车data/sample_schema.sql包含三张表的完整 DDL 和 INSERT 语句但直接source会因外键约束顺序失败。正确做法是分步加载# 终端执行注意路径替换为你解压的实际路径 mysql -u root -p /path/to/Huazhong_Uni_DB_Practice/data/sample_schema.sql # 然后立即验证 mysql -u hzu_user -p hzu_db_practice -e SELECT COUNT(*) FROM student; # 预期输出1000 mysql -u hzu_user -p hzu_db_practice -e SELECT COUNT(*) FROM enroll WHERE grade IS NULL; # 预期输出0说明 CHECK(grade BETWEEN 0 AND 100) 生效为什么必须验证因为sample_schema.sql中enroll表的grade字段有CHECK(grade BETWEEN 0 AND 100)而某些旧版 MySQL 8.0.16会忽略 CHECK 约束。如果你看到COUNT(*)返回非零值说明你的 MySQL 版本不支持该特性——此时应降级使用lab02_index_optimization中的student表做索引实验跳过enroll相关验证点。这是压缩包设计者埋下的第一个「版本兜底开关」。3. 实验核心四个不可跳过的原理验证点附可抄作业的 SQL 与预期输出每个实验目录 (labs/labXX_*/) 都包含run_all.sql一键执行全部步骤和verify.sql独立验证点。但真正吃透原理必须手动敲verify.sql里的每一条并观察EXPLAIN和SELECT结果的变化。以下是四个最具教学穿透力的验证点我按学生最容易困惑的顺序排列。3.1 事务隔离级别实测用SELECT ... FOR UPDATE触发锁等待链lab01_transaction_isolation的核心不是背诵「RR 防幻读」而是亲眼看到锁如何传播-- 在 session A 中执行保持连接不关闭 START TRANSACTION; SELECT * FROM student WHERE id 100 FOR UPDATE; -- 锁住 id100 的行 -- 此时不 COMMIT -- 在 session B 中执行新开终端 START TRANSACTION; UPDATE student SET nameAlice WHERE id 100; -- 会被阻塞 -- 观察B 会卡住直到 A COMMIT 或 ROLLBACK关键验证命令-- 在 session A COMMIT 后立即在 session B 执行 SELECT * FROM information_schema.INNODB_TRX\G -- 查看 trx_state 是否为 RUNNINGtrx_wait_started 是否为空 -- 再执行 SELECT * FROM information_schema.INNODB_LOCK_WAITS\G -- 如果 B 被阻塞这里会显示 waiting_trx_id 和 blocking_trx_id参数说明INNODB_TRX表中的trx_mysql_thread_id对应SHOW PROCESSLIST中的 ID可精准 kill 掉卡住的会话。很多学生以为FOR UPDATE只锁 SELECT 的行其实它会锁住WHERE条件匹配的所有行包括间隙这就是 RR 级别防幻读的物理基础——不是靠 MVCC 快照而是靠锁。3.2 索引优化实战用EXPLAIN FORMATJSON看懂「索引下推」ICPlab02_index_optimization的verify.sql里有一组对比实验-- 先建复合索引 CREATE INDEX idx_name_age ON student(name, age); -- 执行以下查询并对比 EXPLAIN EXPLAIN FORMATJSON SELECT * FROM student WHERE name LIKE Zhang% AND age 20; -- 关键看 index_condition_pushdown: true -- 再执行 EXPLAIN FORMATJSON SELECT * FROM student WHERE name LIKE Zhang% AND grade 85; -- 这里 index_condition_pushdown: false因为 grade 不在索引中为什么 ICP 如此重要没有 ICP 时MySQL 会先用idx_name_age找出所有name LIKE Zhang%的行比如 500 行再逐行回表读grade判断 85开启 ICP 后存储引擎层就用age 20过滤只返回满足条件的行比如 50 行给 Server 层。FORMATJSON输出中的used_columns字段会明确列出哪些列被下推了——这是判断索引是否被高效利用的黄金指标。3.3 存储过程异常处理DECLARE CONTINUE HANDLER与EXIT HANDLER的生死抉择lab03_stored_procedure的transfer_money过程是精华所在DELIMITER $$ CREATE PROCEDURE transfer_money( IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; -- 重新抛出原始错误便于上层捕获 END; START TRANSACTION; UPDATE account SET balance balance - amount WHERE id from_id; IF ROW_COUNT() 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Source account not found; END IF; UPDATE account SET balance balance amount WHERE id to_id; IF ROW_COUNT() 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Target account not found; END IF; COMMIT; END$$ DELIMITER ;血泪经验学生常犯的错是用CONTINUE HANDLER替代EXIT HANDLER。CONTINUE会让存储过程在遇到UPDATE失败后继续执行COMMIT导致部分更新成功——这违背原子性。EXIT HANDLER则保证任何异常都触发ROLLBACK并退出。RESIGNAL是关键它保留原始错误码如1264数值溢出而不是笼统的SQLEXCEPTION方便调试。3.4 执行计划深度解析Using join buffer (Block Nested Loop)是性能毒药lab04_query_execution_plan的终极验证是识别低效连接-- 创建测试表已在 sample_schema.sql 中 CREATE TABLE course_enroll AS SELECT c.id as cid, e.student_id as sid FROM course c JOIN enroll e ON c.id e.course_id; -- 执行慢查询 EXPLAIN FORMATJSON SELECT * FROM course_enroll ce JOIN student s ON ce.sid s.id WHERE s.age 25; -- 观察 join_buffer_size 和 using_join_buffer: Block Nested Loop避坑点当EXPLAIN显示Using join buffer时说明 MySQL 无法用索引驱动连接被迫将小表全量加载进内存做嵌套循环。解决方案不是调大join_buffer_size而是给student.id加主键已存在、给course_enroll.sid加索引——这才是治本。压缩包的verify.sql会引导你执行ALTER TABLE course_enroll ADD INDEX idx_sid (sid);再对比EXPLAIN中type从ALL变成ref。4. 避坑指南五个让 80% 学生卡住的「玄学」问题现象→原因→解决这些不是文档里写的「常见问题」而是我在实验室现场记录的真实翻车瞬间。它们往往不报错但结果不对让人怀疑人生。4.1 现象SELECT * FROM student WHERE name Zhang San;返回空但SELECT * FROM student WHERE name Zhang San ;末尾有空格却有结果原因MySQL 默认字符集utf8mb4下VARCHAR字段的比较遵循PAD SPACE规则——即Zhang San和Zhang San 被视为相等。但sample_schema.sql中student.name定义为VARCHAR(50) NOT NULL未显式指定COLLATE导致某些系统如 CentOS 7 默认utf8mb4_general_ci会忽略末尾空格。解决在init_db_env.sql末尾追加ALTER TABLE student MODIFY name VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL;然后重新加载数据。utf8mb4_unicode_ci对空格更敏感且支持 emoji是现代应用首选。4.2 现象lab01_transaction_isolation中REPEATABLE READ级别下SELECT看不到其他事务INSERT的新行符合预期但SELECT ... FOR UPDATE却能「看到」并锁定这些行原因这是 RR 级别的「半一致性读」semi-consistent read机制。SELECT ... FOR UPDATE会先用最新快照读若发现行不存在则退化为当前读current read从而看到新插入的行并加锁。这不是 bug而是为了防止丢失更新。解决在verify.sql中明确区分两种场景普通SELECT验证 MVCC 快照一致性SELECT ... FOR UPDATE验证锁的范围用INNODB_LOCKS表确认锁类型不要期望两者行为一致。4.3 现象lab03_stored_procedure中transfer_money执行后account表余额没变但SELECT autocommit;返回1原因autocommit1时每个 SQL 语句都是独立事务。存储过程内的START TRANSACTION被autocommit覆盖导致ROLLBACK失效。解决在连接hzu_user后立即执行SET autocommit 0; CALL transfer_money(1, 2, 100.00); -- 此时再 COMMIT 或 ROLLBACK 才有效压缩包的run_all.sql已包含此设置但学生常手动执行单条 SQL 忽略它。4.4 现象EXPLAIN显示type: index全索引扫描但rows值远小于表总行数仍很慢原因type: index表示遍历整个索引树但若索引字段过大如VARCHAR(255)会导致 I/O 次数剧增。sample_schema.sql中student.name是VARCHAR(50)但若你修改为VARCHAR(255)即使rows仅 1000实际扫描的磁盘页数可能翻倍。解决用SHOW INDEX FROM student;查看Seq_in_index和Cardinality确保索引选择性高对长文本字段改用前缀索引DROP INDEX idx_name_age ON student; CREATE INDEX idx_name_age ON student(name(20), age); -- name 取前 20 字符4.5 现象lab04_query_execution_plan中ORDER BY使用filesort但EXPLAIN显示Extra: Using index原因Using index表示用了覆盖索引但filesort说明排序无法用索引完成。典型场景是ORDER BY字段不在索引最左前缀中。例如索引(name, age)ORDER BY age仍需 filesort。解决创建符合排序需求的索引-- 若常按 age 排序建索引 CREATE INDEX idx_age_name ON student(age, name); -- 若需 WHERE nameZhang ORDER BY age则用原索引 (name, age)记住ORDER BY的字段必须是索引的连续前缀否则必 filesort。5. 进阶技巧用performance_schema抓取「隐形」性能瓶颈不止于 EXPLAINEXPLAIN只告诉你「计划怎么走」但真实执行中I/O、锁等待、CPU 时间才是瓶颈所在。performance_schema是 MySQL 8.0 内置的黑匣子lab04_query_execution_plan的进阶部分就依赖它。5.1 开启必要消费者并重置历史数据默认performance_schema是关闭大部分采集项的必须手动激活-- 检查当前状态 SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE events_statements_% OR NAME LIKE events_waits_%; -- 启用关键消费者一次性执行 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME IN (events_statements_history_long, events_waits_history_long, events_statements_current); -- 清空历史数据避免干扰 TRUNCATE TABLE performance_schema.events_statements_history_long; TRUNCATE TABLE performance_schema.events_waits_history_long;注意events_statements_history_long默认只存 10000 条对高并发环境不够。若需长期监控修改performance_schema_events_statements_history_long_size变量需重启 MySQL。5.2 定位慢查询的真实耗时分解CPU vs I/O vs 锁执行一个故意慢的查询如SELECT SLEEP(2);然后抓取其详细轨迹-- 在另一个会话执行慢查询 SELECT SLEEP(2); -- 立即在监控会话执行 SELECT EVENT_ID, TRIM(TRAILING ; FROM SQL_TEXT) AS query, TIMER_WAIT/1000000000 AS exec_time_sec, LOCK_TIME/1000000000 AS lock_time_sec, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_TABLES, SELECT_FULL_JOIN FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE %SLEEP% ORDER BY EVENT_ID DESC LIMIT 1\G关键字段解读exec_time_sec总执行时间含锁等待、I/O 等lock_time_sec纯锁等待时间若接近exec_time_sec说明锁争用严重ROWS_EXAMINED扫描行数若远大于ROWS_SENT说明有大量无效过滤SELECT_FULL_JOIN是否发生全表连接值为 1 即警报5.3 用events_waits_history_long追踪锁等待链当lab01的UPDATE被阻塞时performance_schema能精确定位谁在等谁SELECT eshl.SQL_TEXT AS blocked_sql, ewhl.EVENT_NAME AS wait_event, ewhl.SOURCE AS wait_source, CONCAT(Thread , ewhl.THREAD_ID, waiting for , ewhl.EVENT_NAME) AS blocker_info FROM performance_schema.events_waits_history_long ewhl JOIN performance_schema.events_statements_history_long eshl ON ewhl.THREAD_ID eshl.THREAD_ID WHERE ewhl.EVENT_NAME LIKE wait/synch/% AND ewhl.STATE WAITING ORDER BY ewhl.EVENT_ID DESC LIMIT 5\G输出示例blocked_sql: UPDATE student SET nameAlice WHERE id 100 wait_event: wait/synch/mutex/innodb/trx_mutex wait_source: trx0trx.cc:1234 blocker_info: Thread 42 waiting for wait/synch/mutex/innodb/trx_mutex这说明线程 42 在等 InnoDB 事务互斥锁结合INNODB_TRX表就能找到持有锁的线程 ID。5.4 构建「实验健康度」仪表盘自动化验证脚本我把performance_schema查询封装成一个check_lab_health.sql每次实验前运行它自动生成报告-- check_lab_health.sql SELECT Lab01_Transaction AS lab, COUNT(*) FILTER (WHERE EVENT_NAME wait/synch/mutex/innodb/trx_mutex) AS trx_mutex_waits, AVG(TIMER_WAIT)/1000000000 AS avg_wait_sec FROM performance_schema.events_waits_history_long WHERE EVENT_NAME LIKE wait/synch/mutex/innodb/trx_mutex AND TIMER_START UNIX_TIMESTAMP(NOW() - INTERVAL 1 MINUTE) * 1000000000; -- 类似地检查 Lab02 的 index_read, Lab03 的 stored_program 等我的习惯在run_all.sql最后一行加入SOURCE check_lab_health.sql;。如果某次实验trx_mutex_waits 5我就知道事务隔离实验的并发控制没到位需要回溯SET TRANSACTION ISOLATION LEVEL是否生效。希望帮到你。本文还有配套的精品资源点击获取 MySQL 8.0 实战沙盒：原理验证与性能调优四步法
简介本资源是华中科技大学《数据库系统原理实践——以MySQL为例》课程的配套实验材料包面向计算机专业本科生及数据库初学者旨在通过系统化实操帮助学习者深入理解数据库核心原理与工程实现。资源共92个文件主体为62个SQL脚本覆盖建库建表、查询优化、存储过程、触发器、事务隔离、并发控制等14类实验辅以7个Java应用开发示例、6个C编写的B树索引实现源码、4个Shell备份恢复脚本及文档类文件含任务书、评分细则、报告模板、结构图等压缩包仅1.42MB轻量易用。已有66人下载学习。资源按实验模块分文件夹组织命名规范清晰如“6. MySQL - 存储过程与事务”每个实验内SQL文件按关卡序号编号配合说明文档与可视化图表drawio、jpg、png便于循序渐进完成从理论到代码落地的完整训练闭环。1. 这不是《数据库系统原理》的课件压缩包而是一套能跑通、能调试、能扣细节的 MySQL 实战沙盒你下载的这个华中科技大学 数据库系统原理实践 - 以MySQL为例.zip表面看是高校课程配套资源实际是一份被反复打磨过的「原理落地脚手架」它不讲ACID定义而是让你亲手在事务隔离级别下复现「幻读」不画B树示意图而是用EXPLAIN FORMATTREE看索引如何被真正选用不罗列SQL语法而是提供带完整约束冲突回滚逻辑的银行转账存储过程模板。我带过三届数据库课设学生最常卡在「知道概念但写不出可验证的SQL」——这个压缩包就是为解决这个断层设计的所有实验都基于真实 MySQL 8.0 环境非模拟器每个.sql文件自带-- 验证点注释执行后立刻用SELECT检查状态而不是等期末报告才敢碰数据。适合两类人一是刚学完关系代数、范式理论急需把纸面知识焊进mysql提示符里的本科生二是想快速搭建教学演示环境、避免学生在环境配置上耗掉3小时的助教。它不替代教材但能让教材里的每一章变成可触摸的终端输出。2. 解压即用从零构建可验证的 MySQL 实验环境含版本兼容性兜底这个压缩包的设计哲学是「最小依赖、最大确定性」。它不假设你已装好 MySQL也不要求你用 Docker 或虚拟机——所有实验脚本默认适配本地原生安装的 MySQL 8.0.28Ubuntu/Debian/CentOS 7/macOS 12同时对常见降级场景做了兼容处理。下面是你解压后必须做的三件事顺序不能错。2.1 解压结构与核心文件定位解压后你会看到清晰的分层目录Huazhong_Uni_DB_Practice/ ├── setup/ │ ├── mysql_install_check.sh # 自动检测本地 MySQL 版本与 socket 路径 │ └── init_db_env.sql # 创建实验专用数据库 hzu_db_practice 及用户 hzu_user ├── labs/ │ ├── lab01_transaction_isolation/ # 事务隔离级别实验含 READ-COMMITTED/REPEATABLE-READ 对比 │ ├── lab02_index_optimization/ # B树索引实战覆盖索引、最左前缀、索引下推验证 │ ├── lab03_stored_procedure/ # 带异常处理的存储过程银行转账 余额校验 事务回滚 │ └── lab04_query_execution_plan/ # 执行计划深度解析Using index condition, Using filesort, LooseScan ├── data/ │ └── sample_schema.sql # 包含 student/course/enroll 三张表的 DDL1000 行模拟数据 └── docs/ └── lab_guidelines.md # 每个实验的预期输出、失败排查路径、评分关键点提示不要直接运行setup/init_db_env.sql先执行setup/mysql_install_check.sh—— 它会自动识别你的 MySQL 安装路径/usr/local/mysql/bin或/opt/homebrew/bin、socket 文件位置/tmp/mysql.sock或/var/run/mysqld/mysqld.sock并检查是否满足innodb_file_per_tableON等实验必需参数。若检测失败脚本会明确告诉你缺什么例如ERROR: MySQL version 8.0.28, please upgrade而不是让你在报错后大海捞针。2.2 一键初始化实验数据库含权限与字符集强约束init_db_env.sql不是简单的CREATE DATABASE它强制启用实验所需的底层行为-- setup/init_db_env.sql 关键片段 SET GLOBAL innodb_strict_mode ON; -- 禁止隐式类型转换暴露设计缺陷 SET GLOBAL sql_mode STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO; CREATE DATABASE IF NOT EXISTS hzu_db_practice CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER hzu_userlocalhost IDENTIFIED BY H3uZ!2024; GRANT ALL PRIVILEGES ON hzu_db_practice.* TO hzu_userlocalhost; FLUSH PRIVILEGES; -- 强制设置时区避免 timestamp/datetime 行为差异 SET GLOBAL time_zone 08:00;这段 SQL 的价值在于它让lab03_stored_procedure中的DECLARE EXIT HANDLER FOR SQLEXCEPTION能真正捕获到INSERT INTO account VALUES (1, -100)这类违反CHECK(balance 0)的操作并触发回滚——如果没开innodb_strict_modeMySQL 会静默截断或转成 0导致学生误以为存储过程没生效。2.3 加载样本数据并验证完整性避免「数据没导入」型翻车data/sample_schema.sql包含三张表的完整 DDL 和 INSERT 语句但直接source会因外键约束顺序失败。正确做法是分步加载# 终端执行注意路径替换为你解压的实际路径 mysql -u root -p /path/to/Huazhong_Uni_DB_Practice/data/sample_schema.sql # 然后立即验证 mysql -u hzu_user -p hzu_db_practice -e SELECT COUNT(*) FROM student; # 预期输出1000 mysql -u hzu_user -p hzu_db_practice -e SELECT COUNT(*) FROM enroll WHERE grade IS NULL; # 预期输出0说明 CHECK(grade BETWEEN 0 AND 100) 生效为什么必须验证因为sample_schema.sql中enroll表的grade字段有CHECK(grade BETWEEN 0 AND 100)而某些旧版 MySQL 8.0.16会忽略 CHECK 约束。如果你看到COUNT(*)返回非零值说明你的 MySQL 版本不支持该特性——此时应降级使用lab02_index_optimization中的student表做索引实验跳过enroll相关验证点。这是压缩包设计者埋下的第一个「版本兜底开关」。3. 实验核心四个不可跳过的原理验证点附可抄作业的 SQL 与预期输出每个实验目录 (labs/labXX_*/) 都包含run_all.sql一键执行全部步骤和verify.sql独立验证点。但真正吃透原理必须手动敲verify.sql里的每一条并观察EXPLAIN和SELECT结果的变化。以下是四个最具教学穿透力的验证点我按学生最容易困惑的顺序排列。3.1 事务隔离级别实测用SELECT ... FOR UPDATE触发锁等待链lab01_transaction_isolation的核心不是背诵「RR 防幻读」而是亲眼看到锁如何传播-- 在 session A 中执行保持连接不关闭 START TRANSACTION; SELECT * FROM student WHERE id 100 FOR UPDATE; -- 锁住 id100 的行 -- 此时不 COMMIT -- 在 session B 中执行新开终端 START TRANSACTION; UPDATE student SET nameAlice WHERE id 100; -- 会被阻塞 -- 观察B 会卡住直到 A COMMIT 或 ROLLBACK关键验证命令-- 在 session A COMMIT 后立即在 session B 执行 SELECT * FROM information_schema.INNODB_TRX\G -- 查看 trx_state 是否为 RUNNINGtrx_wait_started 是否为空 -- 再执行 SELECT * FROM information_schema.INNODB_LOCK_WAITS\G -- 如果 B 被阻塞这里会显示 waiting_trx_id 和 blocking_trx_id参数说明INNODB_TRX表中的trx_mysql_thread_id对应SHOW PROCESSLIST中的 ID可精准 kill 掉卡住的会话。很多学生以为FOR UPDATE只锁 SELECT 的行其实它会锁住WHERE条件匹配的所有行包括间隙这就是 RR 级别防幻读的物理基础——不是靠 MVCC 快照而是靠锁。3.2 索引优化实战用EXPLAIN FORMATJSON看懂「索引下推」ICPlab02_index_optimization的verify.sql里有一组对比实验-- 先建复合索引 CREATE INDEX idx_name_age ON student(name, age); -- 执行以下查询并对比 EXPLAIN EXPLAIN FORMATJSON SELECT * FROM student WHERE name LIKE Zhang% AND age 20; -- 关键看 index_condition_pushdown: true -- 再执行 EXPLAIN FORMATJSON SELECT * FROM student WHERE name LIKE Zhang% AND grade 85; -- 这里 index_condition_pushdown: false因为 grade 不在索引中为什么 ICP 如此重要没有 ICP 时MySQL 会先用idx_name_age找出所有name LIKE Zhang%的行比如 500 行再逐行回表读grade判断 85开启 ICP 后存储引擎层就用age 20过滤只返回满足条件的行比如 50 行给 Server 层。FORMATJSON输出中的used_columns字段会明确列出哪些列被下推了——这是判断索引是否被高效利用的黄金指标。3.3 存储过程异常处理DECLARE CONTINUE HANDLER与EXIT HANDLER的生死抉择lab03_stored_procedure的transfer_money过程是精华所在DELIMITER $$ CREATE PROCEDURE transfer_money( IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; -- 重新抛出原始错误便于上层捕获 END; START TRANSACTION; UPDATE account SET balance balance - amount WHERE id from_id; IF ROW_COUNT() 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Source account not found; END IF; UPDATE account SET balance balance amount WHERE id to_id; IF ROW_COUNT() 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Target account not found; END IF; COMMIT; END$$ DELIMITER ;血泪经验学生常犯的错是用CONTINUE HANDLER替代EXIT HANDLER。CONTINUE会让存储过程在遇到UPDATE失败后继续执行COMMIT导致部分更新成功——这违背原子性。EXIT HANDLER则保证任何异常都触发ROLLBACK并退出。RESIGNAL是关键它保留原始错误码如1264数值溢出而不是笼统的SQLEXCEPTION方便调试。3.4 执行计划深度解析Using join buffer (Block Nested Loop)是性能毒药lab04_query_execution_plan的终极验证是识别低效连接-- 创建测试表已在 sample_schema.sql 中 CREATE TABLE course_enroll AS SELECT c.id as cid, e.student_id as sid FROM course c JOIN enroll e ON c.id e.course_id; -- 执行慢查询 EXPLAIN FORMATJSON SELECT * FROM course_enroll ce JOIN student s ON ce.sid s.id WHERE s.age 25; -- 观察 join_buffer_size 和 using_join_buffer: Block Nested Loop避坑点当EXPLAIN显示Using join buffer时说明 MySQL 无法用索引驱动连接被迫将小表全量加载进内存做嵌套循环。解决方案不是调大join_buffer_size而是给student.id加主键已存在、给course_enroll.sid加索引——这才是治本。压缩包的verify.sql会引导你执行ALTER TABLE course_enroll ADD INDEX idx_sid (sid);再对比EXPLAIN中type从ALL变成ref。4. 避坑指南五个让 80% 学生卡住的「玄学」问题现象→原因→解决这些不是文档里写的「常见问题」而是我在实验室现场记录的真实翻车瞬间。它们往往不报错但结果不对让人怀疑人生。4.1 现象SELECT * FROM student WHERE name Zhang San;返回空但SELECT * FROM student WHERE name Zhang San ;末尾有空格却有结果原因MySQL 默认字符集utf8mb4下VARCHAR字段的比较遵循PAD SPACE规则——即Zhang San和Zhang San 被视为相等。但sample_schema.sql中student.name定义为VARCHAR(50) NOT NULL未显式指定COLLATE导致某些系统如 CentOS 7 默认utf8mb4_general_ci会忽略末尾空格。解决在init_db_env.sql末尾追加ALTER TABLE student MODIFY name VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL;然后重新加载数据。utf8mb4_unicode_ci对空格更敏感且支持 emoji是现代应用首选。4.2 现象lab01_transaction_isolation中REPEATABLE READ级别下SELECT看不到其他事务INSERT的新行符合预期但SELECT ... FOR UPDATE却能「看到」并锁定这些行原因这是 RR 级别的「半一致性读」semi-consistent read机制。SELECT ... FOR UPDATE会先用最新快照读若发现行不存在则退化为当前读current read从而看到新插入的行并加锁。这不是 bug而是为了防止丢失更新。解决在verify.sql中明确区分两种场景普通SELECT验证 MVCC 快照一致性SELECT ... FOR UPDATE验证锁的范围用INNODB_LOCKS表确认锁类型不要期望两者行为一致。4.3 现象lab03_stored_procedure中transfer_money执行后account表余额没变但SELECT autocommit;返回1原因autocommit1时每个 SQL 语句都是独立事务。存储过程内的START TRANSACTION被autocommit覆盖导致ROLLBACK失效。解决在连接hzu_user后立即执行SET autocommit 0; CALL transfer_money(1, 2, 100.00); -- 此时再 COMMIT 或 ROLLBACK 才有效压缩包的run_all.sql已包含此设置但学生常手动执行单条 SQL 忽略它。4.4 现象EXPLAIN显示type: index全索引扫描但rows值远小于表总行数仍很慢原因type: index表示遍历整个索引树但若索引字段过大如VARCHAR(255)会导致 I/O 次数剧增。sample_schema.sql中student.name是VARCHAR(50)但若你修改为VARCHAR(255)即使rows仅 1000实际扫描的磁盘页数可能翻倍。解决用SHOW INDEX FROM student;查看Seq_in_index和Cardinality确保索引选择性高对长文本字段改用前缀索引DROP INDEX idx_name_age ON student; CREATE INDEX idx_name_age ON student(name(20), age); -- name 取前 20 字符4.5 现象lab04_query_execution_plan中ORDER BY使用filesort但EXPLAIN显示Extra: Using index原因Using index表示用了覆盖索引但filesort说明排序无法用索引完成。典型场景是ORDER BY字段不在索引最左前缀中。例如索引(name, age)ORDER BY age仍需 filesort。解决创建符合排序需求的索引-- 若常按 age 排序建索引 CREATE INDEX idx_age_name ON student(age, name); -- 若需 WHERE nameZhang ORDER BY age则用原索引 (name, age)记住ORDER BY的字段必须是索引的连续前缀否则必 filesort。5. 进阶技巧用performance_schema抓取「隐形」性能瓶颈不止于 EXPLAINEXPLAIN只告诉你「计划怎么走」但真实执行中I/O、锁等待、CPU 时间才是瓶颈所在。performance_schema是 MySQL 8.0 内置的黑匣子lab04_query_execution_plan的进阶部分就依赖它。5.1 开启必要消费者并重置历史数据默认performance_schema是关闭大部分采集项的必须手动激活-- 检查当前状态 SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE events_statements_% OR NAME LIKE events_waits_%; -- 启用关键消费者一次性执行 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME IN (events_statements_history_long, events_waits_history_long, events_statements_current); -- 清空历史数据避免干扰 TRUNCATE TABLE performance_schema.events_statements_history_long; TRUNCATE TABLE performance_schema.events_waits_history_long;注意events_statements_history_long默认只存 10000 条对高并发环境不够。若需长期监控修改performance_schema_events_statements_history_long_size变量需重启 MySQL。5.2 定位慢查询的真实耗时分解CPU vs I/O vs 锁执行一个故意慢的查询如SELECT SLEEP(2);然后抓取其详细轨迹-- 在另一个会话执行慢查询 SELECT SLEEP(2); -- 立即在监控会话执行 SELECT EVENT_ID, TRIM(TRAILING ; FROM SQL_TEXT) AS query, TIMER_WAIT/1000000000 AS exec_time_sec, LOCK_TIME/1000000000 AS lock_time_sec, ROWS_SENT, ROWS_EXAMINED, CREATED_TMP_TABLES, SELECT_FULL_JOIN FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE %SLEEP% ORDER BY EVENT_ID DESC LIMIT 1\G关键字段解读exec_time_sec总执行时间含锁等待、I/O 等lock_time_sec纯锁等待时间若接近exec_time_sec说明锁争用严重ROWS_EXAMINED扫描行数若远大于ROWS_SENT说明有大量无效过滤SELECT_FULL_JOIN是否发生全表连接值为 1 即警报5.3 用events_waits_history_long追踪锁等待链当lab01的UPDATE被阻塞时performance_schema能精确定位谁在等谁SELECT eshl.SQL_TEXT AS blocked_sql, ewhl.EVENT_NAME AS wait_event, ewhl.SOURCE AS wait_source, CONCAT(Thread , ewhl.THREAD_ID, waiting for , ewhl.EVENT_NAME) AS blocker_info FROM performance_schema.events_waits_history_long ewhl JOIN performance_schema.events_statements_history_long eshl ON ewhl.THREAD_ID eshl.THREAD_ID WHERE ewhl.EVENT_NAME LIKE wait/synch/% AND ewhl.STATE WAITING ORDER BY ewhl.EVENT_ID DESC LIMIT 5\G输出示例blocked_sql: UPDATE student SET nameAlice WHERE id 100 wait_event: wait/synch/mutex/innodb/trx_mutex wait_source: trx0trx.cc:1234 blocker_info: Thread 42 waiting for wait/synch/mutex/innodb/trx_mutex这说明线程 42 在等 InnoDB 事务互斥锁结合INNODB_TRX表就能找到持有锁的线程 ID。5.4 构建「实验健康度」仪表盘自动化验证脚本我把performance_schema查询封装成一个check_lab_health.sql每次实验前运行它自动生成报告-- check_lab_health.sql SELECT Lab01_Transaction AS lab, COUNT(*) FILTER (WHERE EVENT_NAME wait/synch/mutex/innodb/trx_mutex) AS trx_mutex_waits, AVG(TIMER_WAIT)/1000000000 AS avg_wait_sec FROM performance_schema.events_waits_history_long WHERE EVENT_NAME LIKE wait/synch/mutex/innodb/trx_mutex AND TIMER_START UNIX_TIMESTAMP(NOW() - INTERVAL 1 MINUTE) * 1000000000; -- 类似地检查 Lab02 的 index_read, Lab03 的 stored_program 等我的习惯在run_all.sql最后一行加入SOURCE check_lab_health.sql;。如果某次实验trx_mutex_waits 5我就知道事务隔离实验的并发控制没到位需要回溯SET TRANSACTION ISOLATION LEVEL是否生效。希望帮到你。本文还有配套的精品资源点击获取