sxh / engineering-map

切换主题

第38章 总复习与面试宝典 ​

本章目标 ​

  1. 以知识地图的形式梳理全书 37 章的核心知识点
  2. 提供 50 道面试高频题目的详细解答
  3. 提供 30 道 SQL 练习题及参考答案
  4. 整理常见错误排查速查表
  5. 提供从初学到专家的学习路线图

前置知识 ​

  • 已完成前面 37 章的学习
  • 具备一定的实际项目经验

38.1 知识地图 ​

MySQL 知识体系
│
├── 1. SQL 基础 (第1-17章)
│   ├── DDL: CREATE/ALTER/DROP (数据库、表、索引、视图)
│   ├── DML: INSERT/UPDATE/DELETE/REPLACE
│   ├── DQL: SELECT/FROM/WHERE/JOIN/GROUP BY/HAVING/ORDER BY/LIMIT
│   ├── 函数: 聚合函数/字符串函数/日期函数/数学函数/流程控制函数
│   ├── 子查询: 标量子查询/行子查询/表子查询/关联子查询
│   ├── JOIN: INNER/LEFT/RIGHT/CROSS/FULL JOIN
│   └── UNION: UNION vs UNION ALL
│
├── 2. 数据库设计 (第33章)
│   ├── 范式: 1NF(原子性)/2NF(完全函数依赖)/3NF(消除传递依赖)
│   ├── 反范式: 何时/如何/代价
│   ├── 数据类型: INT/BIGINT/CHAR/VARCHAR/DATETIME/TIMESTAMP/DECIMAL/TEXT/JSON
│   ├── NULL 处理: NULL vs NOT NULL vs DEFAULT
│   ├── 垂直分割: 宽表拆分/扩展表模式
│   └── ER 图: 实体/属性/关系/1:1/1:N/M:N
│
├── 3. 索引 (第18-21章)
│   ├── B+Tree 结构: 页/节点/叶子节点/双向链表
│   ├── 聚簇索引: 数据即索引/PK/InnoDB
│   ├── 二级索引: 回表/覆盖索引
│   ├── 索引类型: 普通/唯一/主键/全文/空间/前缀
│   ├── 左前缀原则: 复合索引的列顺序
│   ├── EXPLAIN: type/key/rows/Extra/possible_keys
│   ├── 索引失效: 函数/隐式转换/前导通配符/OR/负向操作
│   └── 索引设计: 选列/选顺序/避免冗余
│
├── 4. 视图/存储过程/触发器/事件 (第22-25章)
│   ├── View: 虚拟表/安全/简化/可更新性
│   ├── Procedure: IN/OUT/INOUT/DECLARE/游标/事务
│   ├── Function: DETERMINISTIC/聚合函数
│   ├── Trigger: BEFORE/AFTER/INSERT/UPDATE/DELETE/NEW/OLD
│   └── Event: 定时任务/ON SCHEDULE
│
├── 5. 事务与锁 (第26-28章)
│   ├── ACID: 原子性/一致性/隔离性/持久性
│   ├── 隔离级别: RU/RC/RR/SERIALIZABLE
│   ├── MVCC: ReadView/Undo Log/版本链/快照读/当前读
│   ├── 锁类型: 共享锁/排他锁/意向锁/记录锁/间隙锁/临键锁
│   ├── 死锁: 成因/检测/预防
│   └── 分布式事务: XA
│
├── 6. 管理与维护 (第29-31章)
│   ├── 用户权限: CREATE USER/GRANT/REVOKE/角色/认证插件
│   ├── 备份恢复: mysqldump/XtraBackup/binlog PITR
│   └── 日志系统: Error/General/Slow/Binlog/Redo/Undo
│
├── 7. 性能优化 (第32章)
│   ├── 查询优化: 三步法（发现→分析→优化）
│   ├── JOIN 优化: 驱动表/索引/STRAIGHT_JOIN
│   ├── 子查询优化: →JOIN/派生表/CTE
│   ├── 深分页: 覆盖索引+延迟关联/游标分页
│   ├── COUNT 优化: InnoDB 索引扫描
│   └── ORDER BY/GROUP BY 优化: 索引排序/松散扫描
│
├── 8. 架构 (第34-35章)
│   ├── 分区: RANGE/LIST/HASH/KEY/裁剪/管理
│   ├── 复制: Master-Slave/异步/半同步/GTID
│   ├── 读写分离: ProxySQL/应用层
│   └── 高可用: MHA/Orchestrator/InnoDB Cluster
│
└── 9. 进阶 (第36-37章)
    ├── 全文搜索: Natural/Boolean/QueryExpansion/ngram
    ├── JSON: 路径/函数/生成列+索引/JSON_TABLE
    └── 实战项目: 电商/员工管理系统

38.2 50 道面试题详解 ​

基础 SQL（1-15题） ​

题 1：WHERE 和 HAVING 的区别是什么？

WHERE 在分组前过滤行，HAVING 在分组后过滤组。WHERE 不能使用聚合函数，HAVING 可以。WHERE 作用于原始数据行，HAVING 作用于 GROUP BY 后的组结果。执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。

sql
-- WHERE：过滤原始行
SELECT dept_id, COUNT(*) FROM employees WHERE salary > 10000 GROUP BY dept_id;

-- HAVING：过滤分组后的组
SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id HAVING COUNT(*) > 5;

题 2：DELETE、TRUNCATE、DROP 的区别？

特性DELETETRUNCATEDROP
类型DMLDDLDDL
删除内容行（可 WHERE）所有行整表（结构+数据）
回滚可回滚不可回滚（隐含COMMIT）不可回滚
触发器触发不触发不触发
自增计数保留重置N/A
速度慢（逐行日志）快（回收空间）快（元数据）
空间回收不释放释放释放

题 3：CHAR 和 VARCHAR 的区别？

CHAR(N) 是定长字符串，始终占用 N 个字符的存储空间。VARCHAR(N) 是变长字符串，实际占用 = 数据长度 + 1-2 字节长度前缀。CHAR 适合固定长度的编码（如 ISO 国家代码、MD5 哈希），VARCHAR 适合可变长度文本（如姓名、地址）。CHAR 最大 255 字符，VARCHAR 最大 16383 字节（实际受行大小限制）。CHAR 会自动截断尾随空格（PAD_CHAR_TO_FULL_LENGTH 模式下），VARCHAR 保留尾随空格。

题 4：UNION 和 UNION ALL 的区别？

UNION 会去重（内部使用临时表 + DISTINCT 排序），UNION ALL 保留所有行。当确定不存在重复行或重复可接受时，应该使用 UNION ALL（性能更好）。

sql
-- UNION ALL：更快，不排序不去重
SELECT name FROM current_employees
UNION ALL
SELECT name FROM former_employees;

-- UNION：更慢，排序 + 去重
SELECT name FROM current_employees
UNION
SELECT name FROM former_employees;

题 5：LEFT JOIN、RIGHT JOIN、INNER JOIN 的区别？

  • INNER JOIN：只返回两个表中匹配的行（交集）
  • LEFT JOIN：返回左表所有行，右表无匹配的填 NULL
  • RIGHT JOIN：返回右表所有行，左表无匹配的填 NULL
  • CROSS JOIN：笛卡尔积（无 ON 条件）

题 6：COUNT(*)、COUNT(1)、COUNT(col) 的区别？

  • COUNT() 和 COUNT(1)：完全等价，统计所有行数（包括 NULL 行）。优化器将 COUNT(1) 内部转换为 COUNT()。
  • COUNT(col)：统计 col 列非 NULL 的行数。
  • 性能：COUNT() 和 COUNT(1) 性能相同。MyISAM 对 COUNT() 有优化（存储了总行数），InnoDB 需要扫描索引。
  • InnoDB 下 COUNT(*) 会选择最小的二级索引进行扫描。

题 7：ON 和 WHERE 在 JOIN 中的区别？

ON 是 JOIN 条件，决定如何匹配两表的行。WHERE 是整体过滤条件，在 JOIN 完成后过滤。对于 INNER JOIN，ON 和 WHERE 效果相同（优化器等价处理）。对于 LEFT/RIGHT JOIN，ON 和 WHERE 效果不同：ON 中的条件不影响左表（保留左表所有行），WHERE 中的条件会过滤所有结果行（可能排除左表行）。

sql
-- LEFT JOIN：ON 不减少左表行
SELECT * FROM users u LEFT JOIN orders o
  ON u.id = o.user_id AND o.status = 'paid';

-- LEFT JOIN：WHERE 减少左表行（效果类似 INNER JOIN）
SELECT * FROM users u LEFT JOIN orders o
  ON u.id = o.user_id
WHERE o.status = 'paid';

题 8：子查询和 JOIN 如何选择？

现代 MySQL（5.6+）对子查询进行了大量优化（自动转换为 semi-join、materialization），许多子查询已经与 JOIN 等效。但一般建议：当查询只需要一个表的列时用子查询，需要多个表的列时用 JOIN。关联子查询在数据量大时性能差，应考虑改写为派生表或 JOIN。

题 9：EXISTS 和 IN 的区别？

多数情况下优化器会做等价转换。EXISTS 对子查询结果为 large 的场景更优（找到第一个匹配就停止），IN 对子查询结果为 small 的场景更友好。MySQL 8.0 对 IN 子查询优化（semi-join）后差异大大缩小。

题 10：GROUP BY 和 DISTINCT 的区别？

DISTINCT 用于去重，GROUP BY 用于分组聚合。在某些情况下它们可以达到相同结果。DISTINCT 本质上是 GROUP BY 所有 SELECT 列的特例。MySQL 内部实现上，DISTINCT 和 GROUP BY 使用相同的优化路径，性能差异很小。

题 11：LIMIT 深分页为什么慢？如何优化？

LIMIT 1000000, 10 需要扫描并丢弃前 100 万行。优化方法：（1）覆盖索引 + 延迟关联；（2）游标分页（基于 ID 的 "WHERE id > ?"）；（3）限制最大页数；（4）搜索引擎方案（Elasticsearch）。

题 12：隐式类型转换如何导致索引失效？

MySQL 将字符串和数字比较时，会把字符串转为数字。例如 WHERE phone = 13800138000（phone 是 VARCHAR），会触发隐式转换导致索引失效。应写为 WHERE phone = '13800138000'。

题 13：ORDER BY 如何利用索引？

当 ORDER BY 的列顺序与索引左前缀匹配时，MySQL 可以利用索引排序避免 filesort。例如 INDEX(a,b,c)，ORDER BY a,b,c 可以利用索引排序。如果 WHERE 中有 a=1 AND b=2，然后 ORDER BY c，也可以利用索引。

题 14：SQL 语句的执行顺序是什么？

FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT。理解执行顺序对查询优化至关重要。

题 15：MySQL 有哪些类型的 JOIN？

INNER JOIN（内连接）、LEFT JOIN（左外连接）、RIGHT JOIN（右外连接）、CROSS JOIN（交叉连接/笛卡尔积）、NATURAL JOIN（自然连接）、STRAIGHT_JOIN（强制左表为驱动表）。MySQL 不直接支持 FULL OUTER JOIN，但可以用 LEFT JOIN UNION RIGHT JOIN 实现。

索引（16-25题） ​

题 16：B+Tree 索引的结构特点是什么？为什么 MySQL 使用 B+Tree 而不是 B-Tree？

B+Tree 特点：（1）所有数据存储在叶子节点；（2）非叶子节点只存储键值；（3）叶子节点通过双向链表连接；（4）所有叶子节点在同一层。

B+Tree vs B-Tree：（1）B+Tree 非叶子节点不存储数据，单个节点可存更多键，树更矮；（2）B+Tree 叶子节点有链表，范围查询效率高（一次寻路，顺序遍历）；（3）B+Tree 的所有查询最终都到叶子节点，查询时间稳定 O(logN)。

题 17：聚簇索引和二级索引的区别？

聚簇索引（Clustered Index）：数据存储顺序与索引顺序一致。InnoDB 将数据和主键索引存在一起（数据即索引）。一个表只能有一个聚簇索引。

二级索引（Secondary Index）：叶子节点存储的是索引列+主键值。通过二级索引查询时，先找到主键值，再通过主键索引找到完整行数据（回表）。

text
二级索引查找过程：二级索引 → 获取主键值 → 聚簇索引 → 获取完整行
覆盖索引：二级索引包含了所有需要的列 → 不需要回表

题 18：什么是覆盖索引？它如何提升性能？

覆盖索引是查询所需的所有列都包含在索引中，不需要回表。EXPLAIN 的 Extra = "Using index" 表示使用了覆盖索引。优势：（1）减少磁盘 I/O（不需要回表）；（2）减少 Buffer Pool 占用；（3）对 COUNT(*) 查询尤其有效。

题 19：复合索引的左前缀原则是什么？举例说明。

复合索引 (a, b, c) 的索引顺序是：先按 a 排序，a 相同按 b 排序，b 相同按 c 排序。要利用索引，查询条件必须从 a 开始。WHERE a=1 AND b=2 能用索引；WHERE b=2 不能用索引；WHERE a=1 AND c=3 能使用 a 列（但 c 不能）；WHERE a=1 AND b>2 AND c=3 能使用 a,b 列（范围后的 c 失效）。

题 20：哪些操作会导致索引失效？（列举 5+ 种）

  1. 对索引列使用函数：WHERE YEAR(created_at) = 2024
  2. 隐式类型转换：WHERE varchar_col = 123
  3. LIKE 前导通配符：WHERE name LIKE '%keyword'
  4. OR 连接无索引列：WHERE indexed_col=1 OR unindexed_col=2
  5. 负向操作符：!=, <>, NOT IN, NOT EXISTS
  6. 复合索引中范围查询中断：WHERE a=1 AND b>10 AND c=5
  7. 在索引列上做运算：WHERE id + 1 = 100

题 21：EXPLAIN 的 type 字段有哪些值？从小到大（性能从好到差）排列。

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

  • const：主键等值查询（最多一行）
  • eq_ref：JOIN 中被驱动表的主键/唯一键等值查找
  • ref：非唯一索引等值查找
  • range：索引范围扫描
  • index：全索引扫描（full index scan）
  • ALL：全表扫描（full table scan）

题 22：EXPLAIN 的 Extra 字段有哪些常见值？各代表什么？

  • Using index：覆盖索引（无需回表）-- 好
  • Using where：Server 层对存储引擎返回的行进行额外过滤
  • Using index condition：索引下推（ICP）-- 好
  • Using filesort：需要额外排序 -- 可能需要优化
  • Using temporary：需要临时表 -- 通常需要优化
  • Using join buffer：JOIN 缓冲区不够用
  • Impossible WHERE：WHERE 条件总是 false
  • Select tables optimized away：MIN/MAX 直接通过索引获取

题 23：什么是索引下推（Index Condition Pushdown / ICP）？

MySQL 5.6+ 的特性。将 WHERE 条件中可以由索引判断的部分，下推到存储引擎层处理，减少回表次数。Extra = "Using index condition" 表示使用了 ICP。

sql
-- INDEX(name, age)
SELECT * FROM users WHERE name LIKE '张%' AND age > 30;
-- 无ICP：通过索引找到所有 name LIKE '张%' 的行 → 全部回表 → Server过滤age
-- 有ICP：通过索引找到 name LIKE '张%' → 在索引中检查age>30 → 仅匹配的行回表

题 24：如何选择索引的列和列顺序？

选择列：WHERE 条件中经常用到的列、JOIN 的关联列、ORDER BY 和 GROUP BY 的列。

选择顺序：（1）等值条件列排前面，范围条件列排后面；（2）选择性高的列排前面（选择性 = DISTINCT值数/总行数）；（3）考虑查询频率，频率最高的查询应被索引覆盖。

题 25：什么是前缀索引？为什么和如何使用？

前缀索引只索引字符串列的前 N 个字符，节省索引空间。CREATE INDEX idx_email_prefix ON users(email(10))。需要选择合适的前缀长度：既足够区分数据，又不过长。使用 SELECT COUNT(DISTINCT LEFT(col, N))/COUNT(*) FROM table 来评估选择性。

事务与锁（26-35题） ​

题 26：事务的 ACID 特性分别是什么？

  • Atomicity（原子性）：事务中所有操作要么全成功要么全失败。通过 Undo Log 回滚实现。
  • Consistency（一致性）：事务前后数据保持一致状态。由其他三个特性共同保证。
  • Isolation（隔离性）：并发事务间互不干扰。通过锁 + MVCC 实现。
  • Durability（持久性）：已提交的事务数据永久保存。通过 Redo Log 实现。

题 27：MySQL 的四种事务隔离级别是什么？各解决了什么问题？

隔离级别脏读不可重复读幻读默认
READ UNCOMMITTED可能可能可能
READ COMMITTED解决可能可能
REPEATABLE READ解决解决可能(大部分解决)InnoDB默认
SERIALIZABLE解决解决解决
  • 脏读：读到其他事务未提交的数据
  • 不可重复读：同一事务两次读取同一行，返回的数据不同
  • 幻读：同一事务两次范围查询，返回的行数不同

InnoDB 的 REPEATABLE READ 通过 MVCC + Next-Key Lock 大大减少了幻读（但仍存在极端场景）。

题 28：MVCC 的原理是什么？InnoDB 如何实现 MVCC？

MVCC（Multi-Version Concurrency Control）允许多个事务看到数据的不同版本。

InnoDB 实现：（1）每行数据有两个隐藏列：DB_TRX_ID（最近修改的事务ID）和 DB_ROLL_PTR（指向 Undo Log 的回滚指针）；（2）Undo Log 形成版本链；（3）ReadView（快照）在事务开始时创建（RR级别）或每次读取时创建（RC级别）；（4）ReadView 通过判断事务ID的可见性，确定看到哪个版本。

题 29：什么是快照读和当前读？

  • 快照读（Snapshot Read）：读取数据的快照版本，不加锁。普通 SELECT 是快照读。
  • 当前读（Current Read）：读取最新版本并加锁。SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、UPDATE、DELETE 都是当前读。
sql
-- 快照读（无锁）
SELECT * FROM accounts WHERE id = 1;

-- 当前读（加锁）
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- 排他锁
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;  -- 共享锁

题 30：InnoDB 有哪些锁类型？

  • 行级锁：Record Lock（记录锁）、Gap Lock（间隙锁）、Next-Key Lock（临键锁 = 记录锁 + 间隙锁）
  • 表级锁：意向共享锁（IS）、意向排他锁（IX）
  • 行锁模式：共享锁（S）、排他锁（X）
  • 其他：AUTO-INC 锁（自增锁）

Next-Key Lock 是 InnoDB 的默认行锁算法，锁定索引记录和它之前的间隙，用于在 REPEATABLE READ 级别下防止幻读。

题 31：什么是死锁？如何预防和排查？

死锁是两个或多个事务相互等待对方持有的锁，形成循环等待。

排查：（1）SHOW ENGINE INNODB STATUS 查看 LATEST DETECTED DEADLOCK 部分；（2）设置 innodb_print_all_deadlocks=ON 将死锁信息写入错误日志。

预防：（1）按相同顺序访问资源；（2）缩短事务（锁持有时间越短越好）；（3）使用低隔离级别（RC 可减少间隙锁）；（4）添加合理的索引（减少锁范围）。

题 32：乐观锁和悲观锁的区别？各适用于什么场景？

  • 乐观锁：假设冲突很少，操作时不加锁，只在更新时检查版本号（CAS）。适合读多写少场景。
  • 悲观锁：假设冲突频繁，操作前先加锁。适合写多冲突高的场景。
sql
-- 乐观锁（版本号）
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = ? AND stock >= ? AND version = ?;

-- 悲观锁
SELECT stock FROM products WHERE id = ? FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = ?;

题 33：InnoDB 的 Redo Log 和 Undo Log 分别是什么作用？

Redo Log：（1）记录数据页的物理修改；（2）保证持久性（崩溃恢复）；（3）WAL 机制：先写 Redo Log，后刷数据页；（4）循环写（固定大小）。

Undo Log：（1）记录数据修改前的版本；（2）支持事务回滚；（3）支持 MVCC（提供历史版本）；（4）Purge 线程异步清理。

题 34：Binary Log（Binlog）和 Redo Log 的区别？

特性BinlogRedo Log
所属层级Server 层InnoDB 引擎层
记录内容逻辑 SQL / 行变更物理页修改
主要用途复制、PITR崩溃恢复
写入方式追加写循环写
文件大小自动切换（max_binlog_size）固定大小

题 35：什么是两阶段提交（Two-Phase Commit）？

MySQL 使用两阶段提交确保 Binlog 和 Redo Log 的一致性：（1）Prepare 阶段：写入 Redo Log，标记为 prepare 状态；（2）写入 Binlog；（3）Commit 阶段：Redo Log 标记为 commit 状态。这样确保崩溃恢复时，可以通过检查 Binlog 来确定事务是否应该提交。

性能优化（36-45题） ​

题 36：InnoDB 的 COUNT(*) 为什么慢？如何优化？

InnoDB 不支持行数缓存（MyISAM 可以）。因为 MVCC 的存在，不同事务看到的不同行数。COUNT(*) 必须扫描索引。优化：（1）统计近似值用 information_schema.TABLES 的 TABLE_ROWS；（2）用计数器表维护计数；（3）业务上只显示近似值。

题 37：如何优化大表分页？

方法一（覆盖索引+延迟关联）：SELECT t1.* FROM t t1 JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000, 20) t2 ON t1.id = t2.id。

方法二（游标分页）：SELECT * FROM t WHERE id > last_id ORDER BY id LIMIT 20。

方法三：限制最大页数 + 结合搜索引擎方案。

题 38：ORDER BY + LIMIT 如何优化？

如果 ORDER BY 列有索引且匹配左前缀，可以利用索引排序（无 filesort）。当 WHERE + ORDER BY + LIMIT 组合时：如果 WHERE 等值 + ORDER BY 匹配索引左前缀，Extra 不出现 filesort。如果 WHERE 范围 + ORDER BY 匹配索引，也可以利用索引排序。

题 39：为什么 SELECT * 不好？

（1）无法使用覆盖索引（必须回表）；（2）传输无用数据（网络开销）；（3）排序和临时表的开销更大；（4）破坏表的封装（新加列影响现有查询）；（5）无法利用索引优化器可能选择错误。

题 40：innodb_buffer_pool_size 应该设多大？为什么重要？

专用数据库服务器：物理内存的 50%-70%。Buffer Pool 缓存了数据页和索引页，是 InnoDB 最重要的性能参数。太小则频繁磁盘 I/O，太大则 OS SWAP。通过 SHOW STATUS LIKE 'Innodb_buffer_pool_read%' 计算命中率，应保持 >99%。

题 41：EXPLAIN 中 Using temporary 和 Using filesort 是什么意思？需要优化吗？

Using filesort：MySQL 无法利用索引排序，需要额外的排序操作。Using temporary：MySQL 需要创建临时表来处理查询（通常是 GROUP BY 或 DISTINCT）。两者同时出现通常意味着查询需要优化（加索引）。单独出现且数据量小可以接受，大表必须优化。

题 42：慢查询优化的标准流程是什么？

（1）从慢查询日志/监控发现慢 SQL；（2）使用 EXPLAIN 分析执行计划；（3）定位瓶颈（全表扫描？filesort？temporary？）；（4）优化（加索引/改查询/改表结构）；（5）EXPLAIN 验证优化效果；（6）实际执行时间对比；（7）生产灰度验证。

题 43：如何做 JOIN 优化？

（1）确保被驱动表的 JOIN 列有索引；（2）驱动表应该小（优化器通常自动选择）；（3）尽量减少 JOIN 前的数据量；（4）使用合适的数据类型（JOIN 列类型相同）；（5）考虑在 JOIN 前用子查询减少数据量；（6）避免笛卡尔积。

题 44：innodb_flush_log_at_trx_commit 参数的含义和选择？

0：每秒刷 Redo Log，最快但崩溃丢 1 秒数据。 1：每次 commit 都刷，最安全（ACID 持久性）但最慢。 2：每次 commit 写入 OS，每秒刷磁盘。MySQL 崩溃不丢，OS 崩溃丢 1 秒。

选择：金融/订单场景选 1；大多数互联网业务可选 2（需保证 OS 不 crash）。

题 45：MySQL 8.0 相比 5.7 有哪些重要新特性？

（1）窗口函数（ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD）；（2）CTE 和递归 CTE；（3）降序索引；（4）原子 DDL；（5）不可见索引；（6）角色管理；（7）caching_sha2_password 默认认证；（8）JSON 增强（JSON_TABLE, JSON_ARRAYAGG 等）；（9）CHECK 约束真正生效；（10）默认字符集 utf8mb4。

架构与设计（46-50题） ​

题 46：数据库读写分离的架构是什么？如何应对复制延迟？

架构：Master 处理写操作，Slave(s) 处理读操作。通过中间件（ProxySQL）或应用层路由实现。

应对复制延迟：（1）刚写入后的读强制走 Master；（2）版本号/token 跟踪（Redis 缓存 "刚写入" 的 key）；（3）监控 Seconds_Behind_Master，延迟过高时切回 Master 读；（4）业务上容忍短暂不一致。

题 47：数据库分库分表有哪些策略？各有什么优缺点？

垂直拆分（按业务模块）：user_db, order_db, product_db。优点：清晰、易管理。缺点：跨库 JOIN 困难。

水平拆分（按数据行）：user_0-user_7，按 user_id % 8。优点：扩展性好。缺点：跨片查询复杂、全局唯一 ID 生成困难、事务跨分片不可用。

分表：单表数据量过大（>5000 万行）时将一张表拆为多张相同结构的物理表。

题 48：分区的类型有哪些？各适用于什么场景？

RANGE（按范围）：时间序列数据（订单按年/月） LIST（按列表）：离散分类数据（按国家/地区） HASH（按哈希）：均匀分布（按 user_id 哈希） KEY（按 MySQL 内部哈希）：类似 HASH，适用于非整型列

题 49：GTID 复制相比传统复制有什么优势？

GTID 每个事务有全局唯一 ID。优势：（1）故障切换时自动找位置（不需要手动指定 binlog 文件+位置）；（2）简化复制拓扑变更；（3）复制错误时可以精确定位到事务；（4）更可靠的数据一致性保证。

题 50：如何设计一个高可用的 MySQL 架构？

（1）主从复制（1 主 + N 从）保证数据冗余；（2）读写分离提升读性能；（3）自动故障切换（Orchestrator / MHA / InnoDB Cluster）；（4）定期备份 + binlog PITR；（5）监控告警（PMM / Prometheus + Grafana）；（6）连接池 + 限流保护；（7）缓存层（Redis）减少数据库压力；（8）最终：MySQL Group Replication + InnoDB Cluster 实现原生高可用。

38.3 30 道 SQL 练习题 ​

基础练习（1-10题） ​

练习 1： 创建 students 表（id, name, age, gender, class_id），class 表（id, class_name, teacher）。插入测试数据。

练习 2： 查询每个班级的学生人数，按人数降序排列。

练习 3： 查询年龄大于班级平均年龄的学生。

练习 4： 查询没有学生的班级（使用 LEFT JOIN NULL）。

练习 5： 将学生按年龄段分组（<18, 18-25, >25），统计每组人数。

练习 6： 使用子查询找出选修了所有课程的学生。

练习 7： 使用 EXISTS 查询至少有一门成绩 >90 分的学生。

练习 8： 使用 CASE WHEN 将学生成绩分为 A(>=90)、B(>=80)、C(>=70)、D(<70)。

练习 9： 使用窗口函数 ROW_NUMBER 为每个班级的学生按成绩排名。

练习 10： 使用 UNION 合并两个表的数据并去重。

进阶练习（11-20题） ​

练习 11： 创建 orders 表（id, user_id, amount, status, created_at），写出按月统计销售额的 SQL。

练习 12： 使用 JOIN 查询每个用户的订单总数和总消费金额，包括没有订单的用户。

练习 13： 查询连续 3 天都下单的用户（使用窗口函数 LAG/LEAD）。

练习 14： 使用递归 CTE 查询组织架构树（departments 表自引用）。

练习 15： 实现行转列：将学生多科目成绩表转为每个科目一列。

练习 16： 使用自连接找出同一部门薪资比同事高的员工。

练习 17： 查询用户的首次购买距离当前有多少天，并按 30 天间隔分组。

练习 18： 使用变量实现行号生成（不带窗口函数时的替代方案）。

练习 19： 编写存储过程，实现批量插入测试数据（带循环和事务）。

练习 20： 使用 JSON_TABLE 将 JSON 数组展开为关系表并做聚合。

高级练习（21-30题） ​

练习 21： 设计并实现深分页优化的表结构（含索引），限制 LIMIT 100000, 20 查询耗时 < 100ms。

练习 22： 实现乐观锁库存扣减：UPDATE 时检查版本号。

练习 23： 编写触发器，在订单状态变更时自动更新关联的产品销量。

练习 24： 实现全文搜索的商品搜索功能（含 ngram 中文分词）。

练习 25： 使用 EXPLAIN 分析并优化一个有多个 JOIN 的复杂查询。

练习 26： 实现 PV/UV 统计表设计，支持按日/周/月聚合查询。

练习 27： 使用窗口函数实现"连续登录天数"的计算。

练习 28： 设计点餐系统的表结构（至少 6 个表），写出核心业务 SQL。

练习 29： 模拟 binlog PITR 恢复：给定 binlog 文件 range，恢复到指定时间点。

练习 30： 编写一个完整的数据库健康检查脚本（SQL 版），检查：无主键表、大表、多余索引、长事务、锁等待。

参考答案示例（练习 1-3） ​

sql
-- 练习 1
CREATE TABLE class (
  id INT PRIMARY KEY AUTO_INCREMENT,
  class_name VARCHAR(50) NOT NULL,
  teacher VARCHAR(50) NOT NULL
);

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  age TINYINT UNSIGNED NOT NULL,
  gender ENUM('M','F') NOT NULL,
  class_id INT NOT NULL,
  FOREIGN KEY (class_id) REFERENCES class(id)
);

INSERT INTO class VALUES (1, '高一一班', '张老师'), (2, '高一二班', '李老师'), (3, '高一三班', '王老师');
INSERT INTO students (name, age, gender, class_id) VALUES
('张三', 16, 'M', 1), ('李四', 17, 'M', 1), ('王五', 16, 'F', 1),
('赵六', 15, 'M', 2), ('钱七', 16, 'F', 2), ('孙八', 17, 'M', 3);

-- 练习 2
SELECT c.class_name, COUNT(s.id) AS student_count
FROM class c
LEFT JOIN students s ON c.id = s.class_id
GROUP BY c.id, c.class_name
ORDER BY student_count DESC;

-- 练习 3
SELECT s.name, s.age, c.class_name
FROM students s
JOIN class c ON s.class_id = c.id
WHERE s.age > (
  SELECT AVG(s2.age)
  FROM students s2
  WHERE s2.class_id = s.class_id
);

38.4 常见错误排查速查表 ​

错误码错误信息常见原因解决方案
2003Can't connect to MySQL server服务未启动/端口错误/防火墙检查服务状态、检查端口和防火墙
1045Access denied for user密码错误/Host不匹配检查用户名和Host、重置密码
1146Table doesn't exist表不存在/库不对/大小写USE正确数据库、检查表名大小写
1062Duplicate entry主键/唯一键重复检查数据、使用ON DUPLICATE KEY
1205Lock wait timeout exceeded锁等待超时检查SHOW ENGINE INNODB STATUS
1213Deadlock found死锁检查死锁日志、优化事务顺序
1040Too many connections连接数满了增大max_connections、检查连接泄漏
2006MySQL server has gone away超时断开/packet太长增大wait_timeout、增大max_allowed_packet
1406Data too long for column插入数据超出列长度检查数据长度、修改VARCHAR长度
1054Unknown column列名拼写错误检查列名DESC table_name
1175Safe update modeWHERE无索引的UPDATE/DELETE添加索引条件或SET SQL_SAFE_UPDATES=0
1091Can't DROP; check that col/key exists删除不存在的列/索引使用IF EXISTS
1064You have an error in SQL syntaxSQL 语法错误检查语法、引号、关键字
1364Field doesn't have a default valueNOT NULL且无默认值的列添加默认值或提供值
1451Cannot delete/update parent row外键约束冲突先删除子表数据或SET FOREIGN_KEY_CHECKS=0

38.5 学习路线图 ​

=== 初级阶段（1-2个月）===
目标：能够编写基本的CRUD SQL，理解数据库基本概念
├── 安装MySQL + 基本客户端操作
├── DDL: CREATE/ALTER/DROP TABLE
├── DML: INSERT/UPDATE/DELETE
├── DQL: SELECT/FROM/WHERE/ORDER BY/LIMIT
├── 聚合函数: COUNT/SUM/AVG/MAX/MIN + GROUP BY + HAVING
├── 多表关联: INNER JOIN/LEFT JOIN
├── 基本子查询
├── 常用字符串/日期/数学函数
├── 索引基础: 为什么快/怎么看索引/什么时候加
└── 练习: LeetCode SQL 简单题 50 道

=== 中级阶段（2-4个月）===
目标：能独立设计中等复杂度的数据库，能进行基本的性能优化
├── 范式设计(1NF/2NF/3NF) + 反范式
├── 复杂JOIN和子查询
├── 窗口函数(ROW_NUMBER/RANK/LAG/LEAD)
├── CTE和递归CTE
├── EXPLAIN详解和索引优化
├── 事务和隔离级别
├── MVCC原理
├── 锁类型和死锁
├── 视图、存储过程、触发器
├── 慢查询日志 + mysqldumpslow
├── 数据类型选择和优化
├── 分区表基础
└── 练习: 实战项目（电商/博客/企业系统）

=== 高级阶段（4-6个月）===
目标：胜任DBA工作，能够处理高并发系统的架构设计
├── InnoDB深度: Buffer Pool/Redo/Undo/Binlog
├── 主从复制搭建和运维
├── GTID复制
├── 备份恢复: XtraBackup + PITR
├── 分库分表方案: 水平拆分/垂直拆分
├── 高可用架构: MHA/InnoDB Cluster
├── 读写分离: ProxySQL
├── 性能调优: sysbench压力测试
├── 监控: PMM/Prometheus
├── 全文搜索 + JSON高级用法
├── MySQL 8.0新特性全面掌握
└── 练习: 大规模系统设计 + 故障演练

=== 专家阶段（持续）===
├── MySQL源码阅读（InnoDB部分）
├── 贡献 MySQL Bug 报告或Patch
├── 大规模集群运维（百台+）
├── 新技术跟进（HeatWave/MySQL NDB Cluster等）
├── 考取 Oracle MySQL认证
└── 分享: 博客/演讲/开源

推荐资源：

  • 书籍：《高性能MySQL（第4版）》《MySQL技术内幕：InnoDB存储引擎》
  • 官方文档：https://dev.mysql.com/doc/refman/8.0/en/
  • 博客：Percona Blog, MySQL Server Team Blog
  • 工具：MySQL Workbench, Percona Toolkit, sysbench
  • 练习平台：LeetCode SQL, HackerRank SQL, SQLZoo
  • 社区：StackOverflow (mysql tag), DBA StackExchange

本章练习 ​

练习 1：知识自测 ​

对照 38.1 的知识地图，标记每个模块的掌握程度（1-5分），找出薄弱环节制定复习计划。

练习 2：面试模拟 ​

找一位同学或朋友，随机抽取 38.2 中的 10 个题目进行模拟面试。练习口头表达的清晰度和深度。

练习 3：SQL 习题 ​

独立完成 38.3 中的 30 道 SQL 练习题，设置时间限制（基础10题30分钟，进阶10题60分钟，高级10题90分钟）。

练习 4：复习脑图 ​

根据自身理解，重新绘制一份 MySQL 知识体系脑图（不参考本书），然后与 38.1 对比查漏补缺。

练习 5：故障模拟 ​

根据 38.4 的错误码表，在测试环境模拟 5 种以上常见错误，练习排查和解决流程。

练习 6：学习计划 ​

根据 38.5 的学习路线图，结合自身当前水平制定未来 3 个月的学习计划。

把零散知识，整理成可以反复走的路。

© sxh313 · Engineering Map