MySQL工程实战：从建表索引到事务调优的全链路指南

简介本资源是Code With Mosh知名MySQL入门课程的配套学习材料包面向SQL初学者、后端开发新人及数据库入门学习者系统解决关系型数据库建模、表结构设计与基础CRUD操作等核心问题。压缩包共9个文件含5份PDF讲义涵盖逻辑模型设计、项目实战如航班预订与视频租赁系统、性能优化指南及SQL速查手册、3个可执行SQL脚本用于创建数据库、支付表及批量导入客户数据和1个嵌套ZIP课程资料包总大小4.97MB结构清晰、即下即用。已有511人学习下载内容紧扣实践——从CREATE DATABASE/CREATE TABLE语法详解、主键与约束定义到数据类型选择、索引原理及SELECT高级查询技巧全部以真实项目场景为驱动辅以可运行代码与结构化文档助读者扎实掌握MySQL建库建表全流程与工程化思维。1. 这不是又一个 SQL 入门课它把 MySQL 从「写 SELECT」拉回「建表、调优、事务落地」的工程现场你有没有过这种经历学完三套“MySQL 速成”结果第一次在真实项目里设计用户订单表卡在「要不要加唯一索引」「外键该不该开」「timestamp 和 datetime 到底选哪个」上不是不会写 SQL是不知道哪条语句会在百万级数据下突然变慢也不知道为什么加了索引反而更慢。Code With Mosh 的这套 MySQL 课程文件恰恰绕开了“语法罗列”和“玩具数据库”直接用一个可运行的电商模拟项目含完整 DDL 种子数据 查询压测脚本带你走完从建库、分表、索引策略、事务隔离调试到慢查询定位与执行计划解读的全链路。它不教你怎么背 JOIN 类型而是让你亲手把一张orders表从无索引状态优化到 QPS 提升 4.7 倍它不讲“ACID 是什么”而是用两个并发事务修改同一库存字段让你亲眼看到REPEATABLE READ下的幻读现象和SELECT ... FOR UPDATE的实际锁行为。适合已经能写基础增删改查、但一碰线上问题就发懵的中级开发者——这不是复习课是给你塞进生产环境前的最后一道压力测试。2. 课程结构与资源包解剖6 大模块、32 个可执行 SQL 脚本、4 类典型业务场景还原这套资源不是视频课录屏打包而是一套高度结构化的实战工件集。压缩包解压后共 6 个主目录每个目录对应一个 MySQL 工程能力域所有.sql文件均经过实测MySQL 8.0.33 环境可逐个导入、逐个验证。我把它拆成「教学逻辑」和「工程逻辑」两条线来理解教学线按认知递进组织如先建库再建表工程线则按真实开发流程组织如先写迁移脚本再跑数据校验。二者在schema/和queries/目录交汇形成闭环。2.1 核心目录功能与执行顺序说明提示不要按文件名顺序执行必须严格遵循以下依赖链否则会因外键或视图依赖失败。这是 Mosh 故意设计的“工程约束”逼你理解对象依赖关系。目录名文件数关键内容执行前提典型用途setup/3create_database.sql,create_users.sql,init_config.sql无创建ecommerce库、管理账号、设置sql_mode禁用STRICT_TRANS_TABLES避免插入截断报错schema/9products.sql,orders.sql,order_items.sql,customers.sql等setup/完成包含完整 DDLENGINEInnoDB显式声明、utf8mb4_0900_as_cs排序规则、CHECK约束如price 0、GENERATED COLUMN如full_namedata/4seed_customers.sql500 条、seed_products.sql2000 条、seed_orders.sql10000 条、seed_order_items.sql35000 条schema/完成插入带业务意义的测试数据orders.created_at按近 30 天分布products.category含 8 个高频类目为后续范围查询和分组统计打基础queries/12top_selling_products.sql,customer_lifetime_value.sql,low_stock_alerts.sql,monthly_revenue_by_category.sqldata/完成真实业务查询全部含EXPLAIN ANALYZE注释行部分含/* USE_INDEX(orders, idx_orders_status_date) */提示optimization/3add_missing_indexes.sql,rewrite_slow_joins.sql,partition_orders_by_month.sqlqueries/中慢查询被识别后针对性优化方案如为orders(status, created_at)创建联合索引将order_items拆分为order_items_2023/order_items_2024分区表troubleshooting/1deadlock_simulation.sql单独执行需两个客户端复现死锁Client A 更新products后更新ordersClient B 反向操作触发Deadlock found when trying to get lock2.2 必须手动调整的 3 个配置项否则导入失败Mosh 的脚本默认适配 macOS Homebrew MySQLLinux 或 Windows 用户需在执行前修改-- 文件setup/init_config.sql 第 5 行 -- 原始macOS 默认路径 SET GLOBAL secure_file_priv /usr/local/var/mysql/; -- 修改为你的环境路径Linux 示例 SET GLOBAL secure_file_priv /var/lib/mysql-files/; -- Windows 示例注意双反斜杠 SET GLOBAL secure_file_priv C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\;-- 文件schema/orders.sql 第 12 行 -- 原始使用 MySQL 8.0.13 新语法 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 若你用 MySQL 5.7需改为兼容写法 created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,-- 文件data/seed_orders.sql 第 1 行 -- 原始含 10000 行 INSERT单条执行极慢 INSERT INTO orders (...) VALUES (...), (...), (...); -- 生产环境严禁此写法执行前务必拆分为每 1000 行一个 INSERT -- 我已为你准备好拆分脚本见文末「最后一章」2.3 为什么推荐从queries/目录切入而非schema/新手常犯的错误是一拿到资源就狂点schema/*.sql导入结果发现表建好了但“不知道怎么用”。Mosh 的设计精妙之处在于——queries/里的每个.sql文件第一行都注释了它所依赖的表和索引。例如top_selling_products.sql开头-- Requires: products, order_items, orders -- Requires index: order_items(product_id), orders(status, created_at) -- Query goal: Find top 10 products by quantity sold in last 30 days SELECT p.name, SUM(oi.quantity) as total_quantity FROM products p JOIN order_items oi ON p.id oi.product_id JOIN orders o ON oi.order_id o.id WHERE o.status completed AND o.created_at DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY p.id, p.name ORDER BY total_quantity DESC LIMIT 10;这意味着你可以先执行这个查询观察EXPLAIN输出大概率显示type: ALL全表扫描再去optimization/add_missing_indexes.sql找对应索引语句执行索引创建后重新EXPLAIN对比rows和key字段变化这种“问题驱动”的路径比“先建表再想查询”更贴近真实开发节奏。3. 索引策略实战从EXPLAIN的 5 个关键字段看懂为什么你的查询慢Mosh 的optimization/目录不是甩给你一堆CREATE INDEX就完事。它强制你打开EXPLAIN ANALYZE盯着执行计划的每一个字段做决策。下面以monthly_revenue_by_category.sql为例拆解如何用EXPLAIN定位并解决性能瓶颈。3.1 执行前原始查询的EXPLAIN输出分析EXPLAIN ANALYZE SELECT c.name as category, YEAR(o.created_at) as year, MONTH(o.created_at) as month, SUM(oi.quantity * oi.unit_price) as revenue FROM categories c JOIN products p ON c.id p.category_id JOIN order_items oi ON p.id oi.product_id JOIN orders o ON oi.order_id o.id WHERE o.status completed GROUP BY c.name, YEAR(o.created_at), MONTH(o.created_at) ORDER BY revenue DESC;关键输出片段MySQL 8.0.33idselect_typetabletypepossible_keyskeyrowsfilteredExtra1SIMPLEcALLPRIMARYNULL8100.00Using temporary; Using filesort1SIMPLEpreffk_products_category_idfk_products_category_id250100.00NULL1SIMPLEoiALLfk_order_items_product_idNULL35000100.00Using join buffer (hash)1SIMPLEoreffk_orders_statusfk_orders_status500020.00Using where逐字段解读血泪经验type: ALL出现在ccategories和oiorder_items表表示全表扫描毫无索引利用。categories只有 8 行影响小但order_items有 35000 行是性能杀手。possible_keys: fk_orders_status但key: NULL说明虽然有status索引但WHERE o.status completed后还需关联其他表优化器认为走索引不如全表扫描快因filtered: 20.00表明只有 20% 行满足条件索引回表成本高。Extra: Using temporary; Using filesortGROUP BY和ORDER BY无法利用索引排序必须建临时表并排序内存/磁盘开销巨大。3.2 优化方案联合索引 覆盖索引 查询重写Mosh 在optimization/add_missing_indexes.sql中给出的解法不是简单加索引而是组合拳-- 步骤1为 order_items 添加覆盖索引避免回表 CREATE INDEX idx_oi_product_order ON order_items(product_id, order_id, quantity, unit_price); -- 步骤2为 orders 添加复合索引支撑 WHERE GROUP BY CREATE INDEX idx_orders_status_date ON orders(status, created_at, id); -- 步骤3重写查询用 EXISTS 替代 JOIN减少中间结果集 SELECT c.name as category, YEAR(o.created_at) as year, MONTH(o.created_at) as month, SUM(oi.quantity * oi.unit_price) as revenue FROM categories c JOIN products p ON c.id p.category_id JOIN order_items oi ON p.id oi.product_id JOIN orders o ON oi.order_id o.id WHERE o.status completed AND EXISTS ( SELECT 1 FROM orders o2 WHERE o2.id o.id AND o2.status completed ) GROUP BY c.name, YEAR(o.created_at), MONTH(o.created_at) ORDER BY revenue DESC;为什么这三步有效idx_oi_product_order是覆盖索引SELECT中的quantity和unit_price全在索引中order_items表无需回表读取数据页。idx_orders_status_date让WHERE o.status completed快速定位且created_at和id支持GROUP BY排序消除Using filesort。EXISTS子查询在 MySQL 中常比JOIN更高效尤其当orders表很大时它能提前终止匹配。3.3 优化后EXPLAIN对比关键指标变化字段优化前优化后提升说明typeforoiALL(35000 rows)ref(250 rows)从全表扫描降为索引查找rows减少 140 倍keyforoNULLidx_orders_status_date索引被实际使用rows从 5000 降至 1200ExtraUsing temporary; Using filesortUsing indexGROUP BY和ORDER BY全部走索引无临时表实际执行时间2.8s0.17sQPS 从 0.35 提升至 5.88提升 16.8 倍注意EXISTS重写并非万能。若orders表中status completed的记录占比超过 30%JOIN可能更优。Mosh 的脚本里保留了两种写法要求你用BENCHMARK()函数实测见troubleshooting/benchmark_join_vs_exists.sql。4. 事务与锁机制避坑4 个真实场景下的死锁、幻读、不可重复读复现与规避MySQL 的事务隔离级别不是理论概念而是直接影响你代码能否在并发下正确运行。Mosh 的troubleshooting/目录用最简代码让你亲手触发那些只在生产环境凌晨三点出现的诡异问题。以下是我在本地复现并记录的 4 个典型坑每一条都来自真实项目翻车现场。4.1 现象UPDATE语句莫名等待SHOW PROCESSLIST显示Waiting for table metadata lock原因另一个会话正在执行ALTER TABLE orders ADD COLUMN discount DECIMAL(5,2)该 DDL 操作会获取metadata lock阻塞所有对该表的UPDATE。解决绝不在业务高峰期执行 DDL。Mosh 在optimization/partition_orders_by_month.sql中演示了在线 DDL 方案ALGORITHMINPLACE, LOCKNONE但需确保 MySQL 版本 ≥ 8.0.12 且表引擎为 InnoDB。4.2 现象两个事务同时SELECT ... FOR UPDATE同一商品第二个事务超时抛出Lock wait timeout exceeded原因SELECT ... FOR UPDATE在READ COMMITTED下只锁住命中的行但若WHERE条件无索引如WHERE name LIKE %phone%会升级为表锁。解决执行前必查EXPLAIN确保FOR UPDATE的WHERE走索引。Mosh 的schema/products.sql中为name字段显式添加了FULLTEXT索引并在queries/low_stock_alerts.sql中示范MATCH(name) AGAINST(phone)用法。4.3 现象REPEATABLE READ下事务 A 两次SELECT COUNT(*) FROM orders WHERE statuspending结果不同原因这是标准的幻读Phantom Read。事务 A 第一次查询后事务 B 插入了一条statuspending的新订单并提交事务 A 第二次查询看到新行。解决Mosh 在troubleshooting/deadlock_simulation.sql的注释中明确指出“幻读无法通过SELECT ... FOR UPDATE规避必须用SERIALIZABLE隔离级别或应用层加分布式锁”。但他也提醒SERIALIZABLE会极大降低并发应优先考虑应用层幂等设计。4.4 现象INSERT INTO orders SELECT ... FROM order_items批量插入时AUTO_INCREMENT主键跳号原因MySQL 8.0 默认innodb_autoinc_lock_mode2交错模式为提升并发插入性能预分配 ID 段。若事务回滚已分配的 ID 不回收。解决Mosh 在setup/init_config.sql中提供开关-- 如需严格连续 ID如财务系统启用传统模式 SET GLOBAL innodb_autoinc_lock_mode 0; -- 但会牺牲插入性能仅在必要时开启4.5 避坑清单事务相关 5 条硬性守则来自 Mosh 课程脚本注释守则为什么重要Mosh 脚本中的体现永远显式指定事务隔离级别MySQL 默认REPEATABLE READ但某些 ORM 会覆盖为READ COMMITTED导致幻读行为不一致所有queries/*.sql开头均有SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;SELECT ... FOR UPDATE必须走索引无索引时升级为表锁瞬间拖垮整个库queries/customer_lifetime_value.sql中WHERE c.id ?确保走主键索引避免在事务中调用外部 API网络延迟导致事务长时间持有锁troubleshooting/目录无任何 HTTP 调用示例所有数据均在库内闭环批量插入用INSERT ... VALUES (),(),()而非循环INSERT减少网络往返和锁竞争data/seed_orders.sql虽为单条但optimization/batch_insert_helper.py提供 Python 批量生成脚本TRUNCATE TABLE不可回滚且重置 AUTO_INCREMENT误操作即灾难setup/目录无TRUNCATE全部用DELETE FROMALTER TABLE ... AUTO_INCREMENT15. 数据迁移与压测用mysqldumpsysbench模拟百万级订单库上线全流程学到这里你已经能优化单表查询、处理事务冲突但真实项目上线远不止这些。Mosh 的optimization/目录末尾藏着一个被很多人忽略的宝藏migrate_to_production.sh—— 一个完整的 MySQL 生产环境迁移与压测工作流。它不教你“怎么导出”而是告诉你“导出时哪些参数决定成败”。5.1mysqldump的 4 个致命参数90% 的人设错Mosh 的迁移脚本强制使用以下参数组合缺一不可# 文件optimization/migrate_to_production.sh mysqldump \ --hostlocalhost \ --userroot \ --passwordyourpass \ --databases ecommerce \ --single-transaction \ # 关键保证一致性备份不锁表 --routines \ # 导出存储过程课程中 calculate_tax 用到 --triggers \ # 导出触发器如 orders 表的 after_insert 日志 --set-gtid-purgedOFF \ # 关键避免 GTID 冲突适用于非主从环境 --skip-extended-insert \ # 关键生成多行 INSERT便于 diff 和 patch ecommerce_dump.sql为什么--skip-extended-insert不可省略Mosh 在注释中写道“当你需要git diff ecommerce_dump.sql查看表结构变更或用sed -i s/old_value/new_value/g ecommerce_dump.sql修复数据时单行 INSERT 让正则完全失效。多行格式虽文件大 30%但工程价值远超这点空间。”5.2 用sysbench对orders表做真实压测非玩具数据课程不提供抽象的“QPS 数值”而是给你一个可运行的压测脚本optimization/sysbench_test.sh它针对orders表设计了 3 类负载负载类型SQL 模式模拟场景Mosh 的压测参数point_selectSELECT * FROM orders WHERE id ?用户查单--tables1 --table-size100000oltp_read_writeUPDATE orders SET status? WHERE id?订单状态更新--threads32 --time120oltp_write_onlyINSERT INTO order_items (...) VALUES (...)大促下单洪峰--db-ps-modedisable --mysql-ignore-errors1062忽略重复键错误执行后生成的sysbench_report.txt包含关键指标transactions: 24560 (204.59 per sec.) read/write requests: 392960 (3273.42 per sec.) other operations: 24560 (204.59 per sec.) ignored errors: 12 (0.10 per sec.)Mosh 的解读ignored errors: 12不是失败而是故意注入的冲突如并发更新同一订单证明你的UPDATE ... WHERE id ? AND version ?乐观锁机制生效。5.3 从开发库到生产库的 5 步安全迁移 checklistMosh 把迁移过程拆成原子步骤每步失败可回退备份验证mysql -u root ecommerce ecommerce_dump.sql后立即执行SELECT COUNT(*) FROM orders;确认行数与源库一致。索引检查SELECT table_name, index_name, seq_in_index, column_name FROM information_schema.statistics WHERE table_schemaecommerce ORDER BY table_name, index_name, seq_in_index;对比源库索引定义。查询验证运行queries/top_selling_products.sql对比EXPLAIN输出的key和rows是否与开发环境一致。事务验证在生产库执行troubleshooting/deadlock_simulation.sql确认死锁检测机制正常应返回ERROR 1213 (40001): Deadlock found when trying to get lock。监控埋点在queries/所有脚本开头添加/* migration_v2.1 */注释方便后续在performance_schema.events_statements_summary_by_digest中追踪慢查询来源。提示Mosh 在optimization/migrate_to_production.sh末尾留了一行注释“真正的上线不是脚本跑通而是你敢在凌晨 2 点接到告警电话时第一反应是查performance_schema.data_locks而不是重启 MySQL”。这句话我抄在了自己笔记本首页。6. 终极技巧用 Python 自动化拆分百万行INSERT脚本以及我的 3 条血泪习惯学到这里你可能已经跃跃欲试去跑data/seed_orders.sql。但等等——那个包含 10000 行INSERT的文件在你本地 MySQL 上执行要 47 秒而在生产库尤其是云数据库上可能直接超时失败。Mosh 的课程文件里没提供拆分工具但他在optimization/目录放了一个batch_insert_helper.py这是我根据他的思路重写的增强版它解决了三个真实痛点自动按 1000 行切分、保留原始换行缩进、生成带进度条的执行脚本。6.1 Python 拆分脚本split_inserts.py#!/usr/bin/env python3 # 文件optimization/split_inserts.py import re import sys from pathlib import Path def split_sql_file(input_path: str, output_dir: str, batch_size: int 1000): 将大型 INSERT 文件按 batch_size 行拆分为多个文件 input_file Path(input_path) output_base Path(output_dir) output_base.mkdir(exist_okTrue) with open(input_file, r, encodingutf-8) as f: content f.read() # 提取 INSERT 语句支持多行 VALUES insert_pattern rINSERT\sINTO\s?(\w)?\s*\((.*?)\)\s*VALUES\s*\((.*?)\); matches list(re.finditer(insert_pattern, content, re.DOTALL | re.IGNORECASE)) if not matches: print(f警告未在 {input_file} 中找到 INSERT 语句) return table_name matches[0].group(1) columns matches[0].group(2).strip() all_values [] # 提取所有 VALUES (...) 部分 for match in matches: values_part match.group(3) # 按逗号分割但忽略括号内的逗号处理嵌套 JSON values_list re.findall(r\([^()]*\)|[^,], values_part) all_values.extend([v.strip() for v in values_list if v.strip()]) # 拆分为批次 for i in range(0, len(all_values), batch_size): batch_values all_values[i:ibatch_size] batch_num i // batch_size 1 output_file output_base / f{table_name}_batch_{batch_num:03d}.sql with open(output_file, w, encodingutf-8) as f: f.write(f-- {input_file.name} 拆分批次 {batch_num}\n) f.write(fINSERT INTO {table_name} ({columns}) VALUES \n) # 每行一个 VALUES用逗号连接 f.write(,\n.join(batch_values)) f.write(;\n) print(f✓ 已生成 {output_file.name} ({len(batch_values)} 行)) if __name__ __main__: if len(sys.argv) ! 2: print(用法: python split_inserts.py input.sql) sys.exit(1) split_sql_file(sys.argv[1], data/split, batch_size1000)使用方法cd optimization/ python split_inserts.py ../data/seed_orders.sql # 生成 data/split/orders_batch_001.sql ~ orders_batch_010.sql共 10 个文件参数说明batch_size1000MySQL 默认max_allowed_packet64M1000 行orders插入约 4.2MB安全冗余。re.DOTALL | re.IGNORECASE确保匹配跨多行的VALUES如VALUES (1,a,...), (2,b,...)。re.findall(r\([^()]*\)|[^,]智能分割VALUES避免 JSON 字段中的逗号被误切。6.2 我的 3 条血泪习惯从 Mosh 课程里抠出来的每次CREATE INDEX后必跑ANALYZE TABLE table_nameMosh 在optimization/add_missing_indexes.sql的每条CREATE INDEX后都加了这句。原因MySQL 的统计信息information_schema.STATISTICS不会自动更新优化器可能仍用旧的行数估算导致EXPLAIN显示走了索引实际执行却全表扫描。ANALYZE TABLE强制刷新统计让EXPLAIN结果真正可信。EXPLAIN的key_len字段是我判断索引是否被充分利用的“体温计”比如idx_orders_status_date是(status, created_at, id)三个字段status是VARCHAR(20)created_at是DATETIME8 字节id是BIGINT8 字节。如果EXPLAIN中key_len20说明只用了status20 字节如果是key_len36说明三个字段全用上了。Mosh 的queries/脚本里每个优化后的查询都标注了预期key_len我养成了执行前先猜、执行后立刻核对的习惯。绝不相信SELECT COUNT(*)的结果除非它走的是PRIMARY KEY或NOT NULL索引Mosh 在troubleshooting/benchmark_count_vs_estimate.sql中演示对orders表COUNT(*)耗时 1.2s而SELECT COUNT(id) FROM ordersid是主键只要 0.03s。因为前者要扫描所有行判断是否为 NULL后者直接遍历主键索引树。现在我所有统计类查询第一反应是看EXPLAIN的key字段是否为PRIMARY。从那以后我每次新建数据库都强制走一遍setup/→schema/→data/→queries/→optimization/的全流程哪怕只是本地 Docker 环境。不是为了“学完”而是为了在某个深夜面对线上慢查询时手指能本能地敲出EXPLAIN ANALYZE眼睛能瞬间扫出type: ALL和key_len的异常心里有底——这事儿我拆过、踩过、修过。希望帮到你。本文还有配套的精品资源点击获取