MySQL 全系列：架构、索引、事务与优化

学习目标

学完本章你应该能够：

1. 画出 MySQL 三层架构图，并完整描述一条 SQL 查询从客户端到返回结果的执行流程。
2. 画出 B+ 树结构图，讲清楚聚簇索引与非聚簇索引的区别、回表原理以及覆盖索引的优化思路。
3. 说出 ACID 四个特性及其底层实现机制，并能解释四种隔离级别分别解决了什么问题。
4. 用 EXPLAIN 分析慢查询，识别索引失效场景，并给出 LIMIT 深度分页的优化方案。
5. 描述垂直分表与水平分表的区别，说出分表后根据非分片键查询的常见解决方案。

前置知识：基本 SQL 语法（SELECT/INSERT/UPDATE/DELETE）、数据库基本概念（表、行、列、主键）、对 B 树或二叉搜索树有初步了解、了解内存与磁盘的基本区别。

本章你会动手做的事：

1. 在本地安装 MySQL 8.0，创建测试表并插入 10 万条数据，练习 EXPLAIN 分析执行计划。
2. 用 SHOW ENGINE INNODB STATUS 观察 InnoDB 引擎状态，找到锁信息段落。
3. 开启慢查询日志，故意写一条慢查询并捕获分析。

一、MySQL 软件架构

1.1 用生活类比先建立直觉

把 MySQL 想象成一家大型餐厅：

• 连接层 = 餐厅迎宾台。客人（客户端连接）到了先在门口排队，迎宾员（连接池/线程管理）安排座位，验证会员身份（鉴权）。餐厅不会为每个客人都专门建一个大门，而是复用有限的入口。
• 服务层 = 后厨管理系统。点菜单（SQL）先被翻译成标准格式（解析器），然后厨师长决定怎么做最快最省（优化器），最后由具体厨师执行（执行器）。这里还有一块缓存区，上次做过的菜谱直接复用（查询缓存）。
• 引擎层 = 实际的厨房。不同厨房擅长不同菜系——InnoDB 厨房支持事务（精致料理），MyISAM 厨房快但不支持事务（快餐）。厨房里真正操作食材（数据）的地方。

这三层各司其职，上层不需要知道下层的实现细节，只需要通过标准接口通信。

graph TB
    subgraph 连接层
        A1[客户端连接1] --> B1[连接池]
        A2[客户端连接2] --> B1
        B1 --> C1[线程管理]
        C1 --> D1[鉴权模块]
    end

    subgraph 服务层
        D1 --> E1[SQL接口]
        E1 --> F1[解析器
词法分析+语法分析]
        F1 --> G1[查询优化器
生成执行计划]
        G1 --> H1[执行器
调用存储引擎接口]
    end

    subgraph 引擎层
        H1 --> I1[InnoDB引擎]
        H1 --> I2[MyISAM引擎]
        H1 --> I3[其他引擎]
        I1 --> J1[磁盘数据文件]
        I2 --> J2[磁盘数据文件]
    end

桥接：理解三层架构的核心在于"分层解耦"——连接层管"谁连进来了"，服务层管"要做什么事"，引擎层管"数据怎么存怎么取"。这也解释了为什么 MySQL 可以支持多种存储引擎：因为引擎层是可插拔的。

1.2 工程要点

MySQL 三层架构详解

MySQL vs Redis 对比

⚠️ 新手必踩的坑： 很多人以为"MySQL 慢所以都用 Redis"。实际上 MySQL 有 Buffer Pool 缓存热数据，命中时也是纯内存操作。两者不是替代关系，而是互补：MySQL 是"数据真相的来源"，Redis 是"加速读取的缓存层"。

-- 步骤1：查看当前 MySQL 支持的存储引擎
SHOW ENGINES;

-- 步骤2：查看当前默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';

-- 步骤3：查看连接数相关信息
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

二、一条 SQL 查询的执行过程

2.1 用生活类比先建立直觉

把一条 SQL 查询的执行想象成机场过海关的流程：

1. 连接器 = 检票口：出示护照（账号密码），验证身份，分配登机牌（建立连接）。如果你 8 小时没动作（wait_timeout），检票口会自动让你重新检票。
2. 查询缓存 = 快速通道（已废弃）：以前如果你飞过同样的航线，可以直接放行。但航班信息经常变（表数据更新缓存就失效），命中率太低，8.0 版本直接取消了这个通道。
3. 分析器 = 海关申报审核：先看你写的是什么语言（词法分析识别关键字），再检查语法对不对（语法分析）。“SELECT FROM * users” 会被拦下来——语法错误。
4. 优化器 = 航线规划：同样从北京到上海，走高速还是走国道？优化器会根据代价估算，选择走索引 A 还是索引 B，先 join 哪张表。
5. 执行器 = 实际登机飞行：按照优化器给的执行计划，调用存储引擎接口逐行获取数据，最终返回结果集。

graph LR
    A[客户端] --> B[连接器
鉴权+管理连接]
    B --> C[查询缓存
8.0已废弃]
    C --> D[分析器
词法分析+语法分析]
    D --> E[优化器
生成执行计划]
    E --> F[执行器
调用引擎接口]
    F --> G[存储引擎
InnoDB读取数据]
    G --> H[返回结果集]
    H --> A

桥接：理解 SQL 执行流程的关键在于——MySQL 不是拿到 SQL 就直接查数据，而是先"听懂"（分析器）、再"想清楚怎么做"（优化器）、最后"动手做"（执行器+引擎）。这也是为什么同一条 SQL 可能有截然不同的执行效率：优化器的选择决定了执行路径。

2.2 工程要点

各阶段详解

⚠️ 新手必踩的坑： 权限检查发生在两个地方——连接建立时（连接器检查全局权限）和执行查询时（执行器检查具体表的权限）。所以改了用户权限后，已建立的连接不会立即生效，需要重新连接。

-- 步骤1：查看当前连接的线程ID和用户
SELECT CONNECTION_ID(), CURRENT_USER(), USER();

-- 步骤2：查看wait_timeout（空闲连接超时时间，默认8小时）
SHOW VARIABLES LIKE 'wait_timeout';

-- 步骤3：用EXPLAIN查看优化器生成的执行计划
-- 这里能看到优化器选择了哪个索引
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 步骤4：强制使用某个索引（当优化器选错时）
SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '张三';

-- 步骤5：查看SQL实际执行时的详细警告信息
-- 用于分析优化器为什么没走索引
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = '张三';

三、数据落盘流程

3.1 用生活类比先建立直觉

把数据落盘想象成仓库管理员的日常：

• Buffer Pool = 办公桌：管理员不会每次记账都跑去仓库翻账本，而是先在办公桌上操作。桌上的东西是"热数据"，访问最快。但桌子有限（内存有限），放不下所有东西。
• redo log = 便利贴：每改一笔账，先在便利贴上快速记一笔（顺序写，很快），然后再慢慢把账本翻到对应页正式修改（随机写，慢）。万一突然停电（crash），便利贴还在，重做一遍就行。这就是 WAL（Write-Ahead Logging）：先写日志，再改数据。
• binlog = 总账本：记录所有数据变更操作，用于主从复制和数据备份恢复。归档到异地，所有分仓库都能照着抄。
• undo log = 撤销便签：记下"改之前是什么样"，万一要回滚（ROLLBACK）或者 MVCC 需要读旧版本，就照着撤销便签恢复。

两阶段提交就像签合同：先在草稿上签字（redo log prepare），再在正式合同上签字（binlog 写入），最后把草稿盖章生效（redo log commit）。两步都完成才算数，避免"草稿签了但正式合同没签"的不一致。

graph TB
    A[执行UPDATE语句] --> B[在Buffer Pool中修改数据页]
    B --> C[生成redo log
写入redo log buffer]
    C --> D[生成undo log
用于回滚和MVCC]
    D --> E[阶段1: redo log写入磁盘
状态=prepare]
    E --> F[阶段2: binlog写入磁盘]
    F --> G[阶段3: redo log状态改为commit]
    G --> H[后台线程异步刷脏页到磁盘]

    I[崩溃恢复] --> J{检查redo log状态}
    J -->|prepare且binlog完整| K[提交事务]
    J -->|prepare且binlog不完整| L[回滚事务]
    J -->|commit| M[无需处理]

桥接：数据落盘流程的核心矛盾是"性能 vs 安全"。直接每次写都刷盘太慢（随机I/O），完全不刷盘又怕丢数据。WAL 机制用"先写顺序日志（快）+ 后台异步刷数据页（慢）“巧妙化解了这个矛盾。两阶段提交则保证了 redo log 和 binlog 的一致性，这是主从复制正确的基础。

3.2 工程要点

三种日志对比

⚠️ 新手必踩的坑： redo log 是 InnoDB 引擎层的日志，binlog 是 Server 层的日志。如果不用两阶段提交，可能出现"redo log 写了但 binlog 没写"的情况——主库恢复后数据在，但从库复制不到这条变更，导致主从不一致。两阶段提交就是来解决这个问题的。

-- 步骤1：查看redo log相关配置
SHOW VARIABLES LIKE 'innodb_log_file%';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';

-- 步骤2：查看binlog相关配置
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'sync_binlog';

-- 步骤3：查看Buffer Pool大小（通常设为可用内存的60%-80%）
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 步骤4：查看Buffer Pool状态信息
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 步骤5：查看binlog内容（用于理解逻辑日志格式）
-- SHOW BINLOG EVENTS IN 'mysql-bin.000001';

两阶段提交的必要性

假设不用两阶段提交，可能出现以下两种情况：

1. 先写 redo log，后写 binlog：redo log 写完崩溃，binlog 没写。恢复后主库有这条数据，但从库复制不到，主从不一致。
2. 先写 binlog，后写 redo log：binlog 写完崩溃，redo log 没写。恢复后主库没这条数据，但从库复制了这条，主从不一致。

两阶段提交的流程：

1. 写 redo log，标记为 prepare 状态
2. 写 binlog
3. 将 redo log 标记为 commit 状态

崩溃恢复时的判断逻辑：

• 如果 redo log 是 commit 状态：事务已提交，正常恢复
• 如果 redo log 是 prepare 状态：检查 binlog 是否完整
  • binlog 完整：提交事务（因为 binlog 可能已经被从库读取）
  • binlog 不完整：回滚事务

四、索引机制

4.1 用生活类比先建立直觉

把索引想象成图书馆的目录检索系统：

• 没有索引 = 在书架上一本一本翻：要找一本叫"MySQL实战"的书，得从第一个书架翻到最后一个，时间复杂度 O(N)。数据量大时，这简直就是灾难。
• 有索引 = 先查目录卡片：目录卡片按书名排好序，用二分查找快速定位到"MySQL实战"在第3排第5层。B+ 树就是这种"目录卡片"的数据结构。

为什么是 B+ 树而不是其他结构？

• 二叉搜索树：数据量大时树太高，磁盘I/O次数多（每次访问一个节点就是一次磁盘读取）
• B 树：每个节点都存数据，一个节点能放的索引键少，扇出（每个节点的子节点数）小，树也比较高
• B+ 树：非叶子节点只存索引键不存数据，一个节点能放很多键，扇出极大（通常几百），3-4层就能存千万级数据。叶子节点通过链表相连，范围查询极快

B+ 树的结构就像一栋大楼：大厅（根节点）只有指示牌指向各楼层，每层走廊（非叶子节点）只有房间号指引，真正的货物（数据）都在底层仓库（叶子节点），仓库之间有传送带相连（叶子节点链表）。

graph TB
    Root[根节点
仅存索引键 10 20 30]
    Root --> N1[非叶子节点
5 10 15]
    Root --> N2[非叶子节点
20 25 30]
    Root --> N3[非叶子节点
35 40 45]

    N1 --> L1[叶子节点
数据: 3 5 7]
    N1 --> L2[叶子节点
数据: 10 12 15]
    N2 --> L3[叶子节点
数据: 20 22 25]
    N2 --> L4[叶子节点
数据: 30 32 35]
    N3 --> L5[叶子节点
数据: 40 42 45]

    L1 -.->|双向链表| L2
    L2 -.->|双向链表| L3
    L3 -.->|双向链表| L4
    L4 -.->|双向链表| L5

桥接：理解 B+ 树的关键在于"扇出大则树矮则I/O少”。InnoDB 默认页大小 16KB，假设主键是 bigint（8字节），一个页能放约 1170 个索引键（还要算指针），3层 B+ 树能存约 1170 乘以 1170 乘以 16 约等于 2190 万行数据。这就是为什么千万级表查询也能很快。

4.2 工程要点

聚簇索引 vs 非聚簇索引

回表与覆盖索引

graph LR
    A[查询: SELECT name FROM users WHERE name = 张三] --> B{name列有索引吗}
    B -->|有| C[在name索引树上查找]
    C --> D[找到主键id=5]
    D --> E{SELECT只查name吗}
    E -->|是| F[覆盖索引
直接返回name
无需回表]
    E -->|否 还要查age| G[回表: 用id=5
去聚簇索引查完整行]
    G --> H[拿到age等所有列]

-- 步骤1：创建测试表
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    city VARCHAR(50) NOT NULL,
    INDEX idx_name(name),
    INDEX idx_name_age(name, age)
) ENGINE=InnoDB;

-- 步骤2：插入测试数据
INSERT INTO users (name, age, city) VALUES
('张三', 25, '北京'),
('李四', 30, '上海'),
('王五', 28, '广州'),
('张三', 22, '深圳'),
('赵六', 35, '杭州');

-- 步骤3：验证回表（Extra列显示NULL表示需要回表取所有列）
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- type=ref, key=idx_name, Extra=NULL（需要回表取所有列）

-- 步骤4：验证覆盖索引（Extra列显示Using index表示无需回表）
EXPLAIN SELECT name FROM users WHERE name = '张三';
-- type=ref, key=idx_name, Extra=Using index（索引覆盖，无需回表）

-- 步骤5：联合索引的覆盖索引
EXPLAIN SELECT name, age FROM users WHERE name = '张三';
-- type=ref, key=idx_name_age, Extra=Using index（联合索引覆盖）

⚠️ 新手必踩的坑： SELECT * 几乎永远无法利用覆盖索引，因为它要返回所有列，而索引不可能包含所有列。养成"只查需要的列"的习惯，不仅能利用覆盖索引加速，还能减少网络传输量。

最左前缀原则

联合索引 (a, b, c) 的 B+ 树是先按 a 排序，a 相同再按 b 排序，b 相同再按 c 排序。所以：

-- 步骤1：创建联合索引
CREATE INDEX idx_abc ON users(name, age, city);

-- 步骤2：能完整走索引的查询
EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京';
-- key=idx_abc, ref=const,const,const

-- 步骤3：只能走部分索引的查询（a走索引，c不走）
EXPLAIN SELECT * FROM users WHERE name = '张三' AND city = '北京';
-- key=idx_abc, ref=const（只有name用了索引）

-- 步骤4：完全不走索引的查询
EXPLAIN SELECT * FROM users WHERE age = 25 AND city = '北京';
-- key=NULL, type=ALL（全表扫描）

索引下推 ICP（Index Condition Pushdown，5.6+）

在没有 ICP 之前，对于 WHERE name = '张三' AND age LIKE '%2'：

1. 存储引擎根据 name 索引找到所有 name=‘张三’ 的记录的主键
2. 执行器拿到主键后，逐个回表取完整行
3. 执行器在 Server 层过滤 age LIKE ‘%2’

有了 ICP 之后：

1. 存储引擎根据 name 索引找到 name=‘张三’ 的记录
2. 存储引擎直接在索引层用 age LIKE ‘%2’ 过滤（因为联合索引包含 age）
3. 只有满足条件的记录才回表

-- 步骤1：创建联合索引（name, age）
CREATE INDEX idx_name_age_icp ON users(name, age);

-- 步骤2：观察ICP效果
EXPLAIN SELECT * FROM users WHERE name = '张三' AND age LIKE '%2';
-- Extra列显示 Using index condition 表示使用了ICP
-- 如果没有ICP，Extra会显示 Using where

为什么对性别创建索引快不了

-- 步骤1：给gender列加索引
ALTER TABLE users ADD COLUMN gender CHAR(1) DEFAULT 'M';
CREATE INDEX idx_gender ON users(gender);

-- 步骤2：查询男性记录
EXPLAIN SELECT * FROM users WHERE gender = 'M';
-- type=ref, 但rows可能接近全表行数的一半

索引的选择性 = 不同值的数量 / 总行数。gender 只有 M/F 两个值，选择性约 0.5（50%）。MySQL 优化器认为"走索引要回表两次（索引+数据），还不如直接全表扫描一次"。一般来说，选择性低于 30% 的列不适合单独建索引。

SQL 求每班级大于 18 岁人数

-- 步骤1：创建学生表
CREATE TABLE students (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    class VARCHAR(20) NOT NULL,
    INDEX idx_class_age(class, age)
) ENGINE=InnoDB;

-- 步骤2：插入测试数据
INSERT INTO students (name, age, class) VALUES
('张三', 20, '一班'), ('李四', 17, '一班'),
('王五', 19, '二班'), ('赵六', 18, '二班'),
('钱七', 21, '三班'), ('孙八', 16, '三班');

-- 步骤3：查询每班级大于18岁的人数（注意：age大于18不包含18）
SELECT class, COUNT(*) AS cnt
FROM students
WHERE age > 18
GROUP BY class
ORDER BY cnt DESC;

-- 步骤4：验证执行计划（联合索引 idx_class_age 覆盖了 class 和 age）
EXPLAIN SELECT class, COUNT(*) FROM students WHERE age > 18 GROUP BY class;
-- Extra列应显示 Using index（覆盖索引）+ Using temporary（GROUP BY需要临时表）

五、事务与隔离级别

5.1 用生活类比先建立直觉

把事务想象成银行转账：

你给朋友转 100 块钱，涉及两个操作：你的账户减 100，朋友的账户加 100。这两个操作必须"要么都成功，要么都失败"——这就是事务的原子性。

转账过程中可能出现的问题：

• 脏读：转账还没提交，你就看到朋友的账户多了 100。万一回滚了呢？你看到的是"脏"数据。
• 不可重复读：你查了一次余额是 900，朋友同时转给你 100 并提交了，你再查变成 1000。同一次事务里两次查询结果不一样——这就是不可重复读。
• 幻读：你统计班级人数是 30 人，这时有人新注册了一条学生记录并提交，你再统计变成 31 人。像幻觉一样多出了"行"——这就是幻读。

MVCC（多版本并发控制） 就像图书馆的"多版本存档"：每个人看书时，拿到的都是他开始看那一刻的版本快照（ReadView）。别人后来改的内容，他看不到。这样读操作不加锁也能保证一致性，大幅提升并发性能。

graph TB
    subgraph 版本链
        R1[行数据v3
trx_id=300
name=王五]
        R1 -->|roll_pointer| R2[undo日志v2
trx_id=200
name=李四]
        R2 -->|roll_pointer| R3[undo日志v1
trx_id=100
name=张三]
    end

    subgraph ReadView
        RV[事务400开启时生成ReadView
活跃事务列表 200和300
min_trx_id=200
max_trx_id=401
creator_trx_id=400]
    end

    RV -->|判断版本可见性| J1{trx_id 小于 min_trx_id}
    J1 -->|是| OK1[可见
事务已提交]
    J1 -->|否| J2{trx_id 大于等于 max_trx_id}
    J2 -->|是| OK2[不可见
事务在ReadView之后开启]
    J2 -->|否| J3{trx_id在活跃列表中}
    J3 -->|是| OK3[不可见
事务未提交]
    J3 -->|否| OK4[可见
事务已提交]

桥接：MVCC 的精妙之处在于"读不加锁，读写不互斥"。它通过 undo log 版本链保存历史数据，通过 ReadView 判断哪个版本对当前事务可见。InnoDB 在 RC 级别每次 SELECT 都生成新 ReadView（所以能看到别人已提交的更新），在 RR 级别只在第一次 SELECT 时生成 ReadView（所以整个事务看到的数据是固定的）。

5.2 工程要点

ACID 四个特性

四种隔离级别

⚠️ 新手必踩的坑： InnoDB 在 RR 级别下通过 Next-Key Lock（临键锁）基本解决了幻读问题，但这不是 SQL 标准的要求。SQL 标准中 RR 仍然允许幻读。另外，如果你在 RR 级别下先做了一次普通查询（生成 ReadView），然后执行 UPDATE 语句（会触发当前读），再查询可能看到新行——这种特殊场景下仍可能出现"幻读"。

-- 步骤1：查看当前隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

-- 步骤2：设置隔离级别（全局或会话级）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 步骤3：开启事务
START TRANSACTION;

-- 步骤4：在事务中查询（快照读，不加锁）
SELECT * FROM users WHERE id = 1;

-- 步骤5：当前读（加锁读，读取最新已提交数据）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 步骤6：提交或回滚
COMMIT;
-- ROLLBACK;

读未提交适用场景

读未提交（READ UNCOMMITTED）几乎不用于生产环境，但以下场景可以考虑：

• 实时性要求极高、准确性要求极低的监控统计（如"大约有多少用户在线"）
• 数据频繁写入但很少读取的中间表
• 对一致性完全不敏感的粗略计数场景

MVCC 原理详解

InnoDB 每行数据都有两个隐藏列：

• trx_id：最后一次修改该行的事务ID
• roll_pointer：指向 undo log 中该行的上一个版本

ReadView 包含四个关键字段：

• m_ids：生成 ReadView 时当前活跃（未提交）的事务ID列表
• min_trx_id：m_ids 中的最小值
• max_trx_id：生成 ReadView 时系统应分配给下一个事务的ID
• creator_trx_id：生成 ReadView 的事务ID

可见性判断规则：

1. trx_id == creator_trx_id：自己修改的，可见
2. trx_id < min_trx_id：修改该行的事务在 ReadView 之前已提交，可见
3. trx_id >= max_trx_id：修改该行的事务在 ReadView 之后才开启，不可见
4. min_trx_id <= trx_id < max_trx_id：看 trx_id 是否在 m_ids 中
   • 在 m_ids 中：事务未提交，不可见，顺 roll_pointer 找上一个版本
   • 不在 m_ids 中：事务已提交，可见

六、锁机制

6.1 用生活类比先建立直觉

把数据库锁想象成停车场的车位管理：

• 共享锁（S锁/读锁）= 多人同时看车位信息屏：多个人可以同时看大屏幕上哪些车位是空的，互不影响。但看的时候别人不能改屏幕信息。
• 排他锁（X锁/写锁）= 一个人在车位上停车：有人在停车（修改），其他人既不能停车（写）也不能看这个车位的状态（读，在当前读模式下）。
• 意向锁（IS/IX）= 停车场入口的指示灯：入口指示灯亮红（IX）表示"里面有人正在操作某个车位"，这样如果有人想"封锁整个停车场"（表锁），不用进去逐个检查车位，看一眼指示灯就知道里面有没有人在操作。
• 行锁 = 锁一个车位：精确到单个数据行，并发度高
• 间隙锁 = 锁一排空车位：锁住一个范围但不锁具体记录，防止别人在这个范围内插入新数据
• 临键锁（Next-Key Lock）= 锁一个车位加它前面的空位：行锁+间隙锁的组合，锁住一个左开右闭区间

graph TB
    A[InnoDB锁类型] --> B[表级锁]
    A --> C[行级锁]

    B --> B1[意向共享锁 IS]
    B --> B2[意向排他锁 IX]
    B --> B3[AUTO-INC锁]

    C --> C1[记录锁 Record Lock
锁住单行]
    C --> C2[间隙锁 Gap Lock
锁住范围不含记录]
    C --> C3[临键锁 Next-Key Lock
Record加Gap 左开右闭]

桥接：锁机制的核心目标是"在保证数据一致性的前提下，最大化并发度"。InnoDB 默认使用行锁（而非 MyISAM 的表锁），粒度更细，并发性能更好。间隙锁和临键锁是 InnoDB 在 RR 级别下解决幻读的关键手段。

6.2 工程要点

行锁三种类型

共享锁与排他锁

-- 步骤1：加共享锁（读锁），其他事务可以读但不能写
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- 步骤2：加排他锁（写锁），其他事务不能读（当前读）也不能写
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 步骤3：普通SELECT不加锁（快照读，MVCC）
SELECT * FROM users WHERE id = 1;

-- 步骤4：UPDATE/DELETE/INSERT 自动加排他锁
UPDATE users SET age = 26 WHERE id = 1;

⚠️ 新手必踩的坑： LOCK IN SHARE MODE 在 MySQL 8.0 中可以用 FOR SHARE 替代。注意：加了共享锁后，如果其他事务也尝试加排他锁（FOR UPDATE），会阻塞等待。两个事务都加了排他锁但锁定不同行时不会死锁，但如果互相等待对方释放锁就会死锁。

意向锁

意向锁是表级锁，由存储引擎自动加，不需要手动操作：

• 当事务要给某行加 S 锁前，先给表加 IS 锁
• 当事务要给某行加 X 锁前，先给表加 IX 锁

意向锁的目的：当有人想加表级锁时（如 LOCK TABLES users WRITE），不用逐行检查有没有行锁，只需检查表上有没有意向锁即可。

死锁检测与避免

死锁的经典场景：事务A锁了行1等待行2，事务B锁了行2等待行1，双方都不放手。

-- 步骤1：查看死锁检测是否开启（默认开启）
SHOW VARIABLES LIKE 'innodb_deadlock_detect';

-- 步骤2：查看锁等待超时时间（默认50秒）
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- 步骤3：查看最近一次死锁信息
SHOW ENGINE INNODB STATUS\G
-- 在输出中找 LATEST DETECTED DEADLOCK 段落

-- 步骤4：开启死锁日志（用于排查，记录到error log）
SET GLOBAL innodb_print_all_deadlocks = ON;

避免死锁的常见策略：

1. 统一加锁顺序：所有事务按相同顺序访问表和行
2. 缩短事务：事务越短，持有锁的时间越短，死锁概率越低
3. 降低隔离级别：RC 比 RR 的锁范围小（无间隙锁），死锁概率低
4. 批量操作拆分：大事务拆成小事务

七、慢查询优化

7.1 用生活类比先建立直觉

把慢查询优化想象成城市交通治堵：

• 发现拥堵 = 开启慢查询日志：先得知道哪条路堵了。在关键路口装监控（slow_query_log），记录所有通行时间超过阈值的车辆。
• 分析原因 = EXPLAIN 分析：查监控看是哪段路堵——是全表扫描（所有车都挤一条路）、还是没有走索引（没走高速走了小路）、还是用了临时表（临时改道绕远路）。
• 优化方案 = 治堵措施：加索引（修高速公路）、改SQL（优化路线）、分页优化（限流分流）。

graph LR
    A[慢查询日志
记录慢SQL] --> B[EXPLAIN分析
查看执行计划]
    B --> C{type是ALL吗}
    C -->|是 全表扫描| D[加合适索引]
    C -->|否| E{Extra有filesort吗}
    E -->|是| F[优化ORDER BY
利用索引有序性]
    E -->|否| G{Extra有temporary吗}
    G -->|是| H[优化GROUP BY
减少临时表]
    G -->|否| I{rows过大吗}
    I -->|是| J[优化查询条件
缩小扫描范围]
    I -->|否| K[SQL已优化
考虑其他层面]

桥接：慢查询优化的核心方法论是"定位到分析到优化到验证"。先通过慢查询日志定位问题SQL，再用 EXPLAIN 分析执行计划找到瓶颈，然后针对性优化（加索引/改SQL/调参数），最后用 EXPLAIN 验证优化效果。

7.2 工程要点

排查慢查询流程

-- 步骤1：开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 步骤2：设置慢查询阈值（单位秒，这里设为1秒）
SET GLOBAL long_query_time = 1;

-- 步骤3：查看慢查询日志文件位置
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 步骤4：查看慢查询数量
SHOW STATUS LIKE 'Slow_queries';

-- 步骤5：用EXPLAIN分析慢SQL
EXPLAIN SELECT * FROM users WHERE name = '张三';

EXPLAIN 关键字段

type 字段详解（从好到差）：

索引失效场景

-- 步骤1：函数操作列导致索引失效
EXPLAIN SELECT * FROM users WHERE LEFT(name, 1) = '张';
-- type=ALL（索引失效），因为对列用了函数

-- 步骤2：隐式类型转换导致索引失效
-- 假设phone是VARCHAR类型
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;
-- type=ALL（索引失效），因为MySQL把字符串转成了数字再比较

-- 步骤3：OR连接可能导致索引失效
EXPLAIN SELECT * FROM users WHERE name = '张三' OR age = 25;
-- 如果age没有索引，整个查询可能全表扫描

-- 步骤4：不等于和NOT IN可能导致索引失效
EXPLAIN SELECT * FROM users WHERE name != '张三';
-- type=ALL（通常不走索引）

-- 步骤5：LIKE以通配符开头导致索引失效
EXPLAIN SELECT * FROM users WHERE name LIKE '%三';
-- type=ALL（索引失效），因为B+树无法定位

EXPLAIN SELECT * FROM users WHERE name LIKE '张%';
-- type=range（可以走索引），前缀匹配

-- 步骤6：最左前缀不满足
EXPLAIN SELECT * FROM users WHERE age = 25;
-- 假设只有联合索引idx_name_age(name,age)，跳过了name，索引失效

⚠️ 新手必踩的坑： OR 连接的两侧如果不是都有索引，优化器可能选择全表扫描。解决办法：拆成两条 SQL 用 UNION ALL，或者给两侧列都加索引。另外，IS NULL 和 IS NOT NULL 在 MySQL 8.0 中是可以走索引的（取决于数据分布），但老版本通常不走。

10 万条数据分页查询优化

-- 步骤1：创建测试表并插入10万条数据
CREATE TABLE articles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_created_at(created_at)
) ENGINE=InnoDB;

-- 步骤2：插入测试数据（使用存储过程批量插入）
DELIMITER $$
CREATE PROCEDURE insert_articles()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 100000 DO
        INSERT INTO articles (title, content, author_id, created_at)
        VALUES (CONCAT('文章', i), CONCAT('内容', i), i % 100, NOW() - INTERVAL i SECOND);
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;
CALL insert_articles();

-- 步骤3：原始慢查询（LIMIT 100000, 10）
-- 需要扫描100010行，丢弃前100000行，极其浪费
EXPLAIN SELECT * FROM articles ORDER BY created_at LIMIT 100000, 10;
-- type=index, rows=100010, Extra=NULL

-- 步骤4：优化方案一：延迟关联（子查询先查主键，再关联）
EXPLAIN SELECT a.* FROM articles a
INNER JOIN (
    SELECT id FROM articles ORDER BY created_at LIMIT 100000, 10
) b ON a.id = b.id;
-- 子查询走覆盖索引（Using index），大幅减少回表次数

-- 步骤5：优化方案二：游标分页（记住上一页最后一条记录的值）
-- 前提：按created_at有序，且created_at有索引
SELECT * FROM articles
WHERE created_at < '2026-08-10 12:00:00'
ORDER BY created_at DESC
LIMIT 10;
-- 每次只扫描10行，性能稳定

-- 步骤6：优化方案三：覆盖索引加延迟关联
EXPLAIN SELECT a.* FROM articles a
INNER JOIN (
    SELECT id FROM articles
    WHERE created_at < '2026-08-10 12:00:00'
    ORDER BY created_at DESC
    LIMIT 10
) b ON a.id = b.id;
-- 子查询完全走索引，再通过主键精确回表10次

优化前后对比：

八、分库分表

8.1 用生活类比先建立直觉

把分库分表想象成公司规模扩张后的组织架构调整：

• 垂直分表 = 按职能拆部门：原来一个部门什么都干（一张表几十上百个字段），现在拆成"人事部"管基本信息、“财务部"管薪资信息、“技术部"管技能信息。每个部门（表）字段少了，职责清晰。对应到数据库就是把一张宽表按列拆成多张窄表。
• 水平分表 = 按区域开分公司：北京分公司管北方客户、上海分公司管南方客户。每个分公司（表）结构一样，但数据不同。对应到数据库就是把一张表按行拆到多张结构相同的表中。
• 分表策略 = 分公司的选址规则：
  • 范围分片：0-10000号客户去北京、10001-20000号去上海。简单但有热点问题（新数据总在最后一个分片）。
  • 哈希分片：客户编号对N取模，均匀分布。但加减节点需要重新hash迁移大量数据。
  • 一致性哈希：环形空间，加减节点只影响相邻区间。适合节点动态变化的场景。

分表后的查询问题：如果按 user_id 分表，但你想按手机号查用户怎么办？你不知道这个用户在哪张分表上。

graph TB
    A[用户查询请求] --> B{查询条件是什么}

    B -->|有user_id| C[直接路由
hash(user_id)取模N等于分表号]
    C --> D[在对应分表查询]

    B -->|只有phone 无user_id| E{如何找到分表}

    E --> F[方案1:路由表
维护phone到user_id映射]
    F --> F1[先查路由表得到user_id]
    F1 --> F2[再按user_id路由]

    E --> G[方案2:基因法
把user_id的部分bit嵌入phone]
    G --> G1[从phone提取基因bit]
    G1 --> G2[用基因bit定位分表]

    E --> H[方案3:双写
按user_id和phone各建一套表]
    H --> H1[在phone维度的表直接查]

桥接：分库分表是"用复杂度换性能"的终极手段。它解决了单表数据量过大导致的性能问题，但引入了分布式事务、跨表查询、全局唯一ID等新问题。在分表之前，应先尝试优化索引、SQL、读写分离等手段。

8.2 工程要点

垂直分表 vs 水平分表

分表策略

-- 步骤1：范围分片示例（按user_id范围分表）
-- user_id 1-1000000 -> users_0
-- user_id 1000001-2000000 -> users_1
-- 路由逻辑（应用层实现）:
-- table_index = (user_id - 1) / 1000000

-- 步骤2：哈希分片示例（按user_id取模分表）
-- 假设分4张表
-- table_index = user_id % 4
-- user_id=1 -> users_1, user_id=2 -> users_2, user_id=4 -> users_0

-- 步骤3：创建分表
CREATE TABLE users_0 (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    phone VARCHAR(20),
    age INT
) ENGINE=InnoDB;

CREATE TABLE users_1 (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    phone VARCHAR(20),
    age INT
) ENGINE=InnoDB;

-- 步骤4：一致性哈希（概念说明，实际由中间件实现）
-- 将节点映射到0到2的32次方的环形空间
-- 数据也hash到环形空间，顺时针找到的第一个节点就是存储节点
-- 加减节点只影响相邻区间的数据

分表后按非分片键查询

-- 步骤1：路由表方案
-- 额外维护一张映射表，记录phone到user_id的映射
CREATE TABLE phone_user_map (
    phone VARCHAR(20) PRIMARY KEY,
    user_id BIGINT NOT NULL,
    INDEX idx_user_id(user_id)
) ENGINE=InnoDB;

-- 查询流程：
-- 先查映射表: SELECT user_id FROM phone_user_map WHERE phone = '13800138000'
-- 再按user_id路由: SELECT * FROM users_{user_id取模4} WHERE id = user_id

-- 步骤2：基因法方案
-- 假设user_id的后4位作为基因，嵌入到phone的最后4位
-- user_id = 123456, 基因 = 3456
-- phone = 1380013456（最后4位是基因）
-- 路由时: table_index = phone最后4位取模4 = 3456取模4 = 0
-- 这样不查路由表也能直接定位分表

-- 步骤3：双写方案
-- 维护两套分表：一套按user_id分，一套按phone分
-- 写入时同时写两套（保证最终一致）
-- 按user_id查走第一套，按phone查走第二套
-- 缺点：存储翻倍，写入性能下降，需要保证一致性

⚠️ 新手必踩的坑： 分表后 JOIN 变得极其困难——两张分表无法直接 JOIN。常见解决方案：1）在应用层做内存JOIN；2）使用 ShardingSphere 等中间件的绑定表/广播表；3）适当冗余字段避免JOIN。另外，分表后全局唯一ID不能用自增，需要用雪花算法或号段模式。

九、数据迁移与文件服务器

9.1 用生活类比先建立直觉

把数据迁移想象成搬家：

• mysqldump = 自己打包搬家：把所有东西装箱（导出SQL文件），搬到新家再拆箱（导入）。简单但慢，适合小房子（小数据量）。
• LOAD DATA INFILE = 货运公司批量运输：直接把整箱货物整车运过去，比一件一件搬快得多。适合有结构化的批量数据导入。
• DataX/Canal = 专业搬家公司全流程服务：DataX 是"一次性整搬”（全量同步），Canal 是"增量搬运”（监听binlog实时同步变更）。适合大规模、持续性的数据迁移。

文件服务器选型就像选择仓库类型：

• 本地磁盘 = 家里储物间：拿取方便但空间有限，换房子东西不好搬。
• NFS = 小区共享储物间：多个住户能共享，但高峰期要排队，有性能瓶颈。
• 对象存储（MinIO/OSS）= 专业云仓：容量无限、高可用、按需付费，通过API存取，是现代应用的首选。
• CDN = 快递前置仓：把常用货物提前放到离用户最近的仓库，用户下单秒到。适合静态资源加速。

桥接：数据迁移和文件存储的选择核心是"数据量加实时性加成本"的权衡。小数据量用 mysqldump 足矣；大数据量加需要实时同步用 Canal；文件存储优先选对象存储，静态资源加速叠加CDN。

9.2 工程要点

MySQL 数据迁移工具对比

# 步骤1：使用mysqldump导出数据
mysqldump -h 127.0.0.1 -u root -p mydb users > users_backup.sql

# 步骤2：导出时包含建表语句和数据
mysqldump -u root -p --databases mydb > mydb_full.sql

# 步骤3：只导出表结构不导出数据
mysqldump -u root -p --no-data mydb > schema.sql

# 步骤4：导入数据到目标库
mysql -h 127.0.0.1 -u root -p mydb < users_backup.sql

-- 步骤5：使用LOAD DATA INFILE批量导入（比INSERT快10-20倍）
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(name, age, city);

-- 步骤6：开启批量插入优化（大批量INSERT时临时关闭检查以提速）
SET SESSION unique_checks = 0;
SET SESSION foreign_key_checks = 0;
-- 执行批量INSERT...
SET SESSION unique_checks = 1;
SET SESSION foreign_key_checks = 1;

⚠️ 新手必踩的坑： LOAD DATA INFILE 默认只读取服务端文件（MySQL服务器上的文件）。如果要读取客户端本地文件，需要用 LOAD DATA LOCAL INFILE，并且服务端需要开启 local_infile 参数：SET GLOBAL local_infile = ON。

文件服务器选型

// Go语言上传文件到MinIO的示例代码
package main

import (
    "context"
    "log"

    "github.com/minio/minio-go/v7"
    "github.com/minio/minio-go/v7/pkg/credentials"
)

func uploadToMinIO() {
    // 步骤1：初始化MinIO客户端
    endpoint := "minio.example.com:9000"
    accessKey := "your-access-key"
    secretKey := "your-secret-key"
    useSSL := true

    minioClient, err := minio.New(endpoint, &minio.Options{
        Creds:  credentials.NewStaticV4(accessKey, secretKey, ""),
        Secure: useSSL,
    })
    if err != nil {
        log.Fatalf("步骤1失败：初始化MinIO客户端出错: %v", err)
    }

    // 步骤2：创建bucket（如果不存在）
    bucketName := "my-files"
    location := "us-east-1"
    err = minioClient.MakeBucket(context.Background(), bucketName,
        minio.MakeBucketOptions{Region: location})
    if err != nil {
        // 检查bucket是否已存在
        exists, errExists := minioClient.BucketExists(context.Background(), bucketName)
        if errExists == nil && exists {
            log.Printf("步骤2：bucket %s 已存在", bucketName)
        } else {
            log.Fatalf("步骤2失败：创建bucket出错: %v", err)
        }
    } else {
        log.Printf("步骤2：成功创建bucket %s", bucketName)
    }

    // 步骤3：上传文件
    objectName := "uploads/avatar/user_001.jpg"
    filePath := "/tmp/avatar.jpg"
    contentType := "image/jpeg"

    _, err = minioClient.FPutObject(context.Background(), bucketName,
        objectName, filePath, minio.PutObjectOptions{
            ContentType: contentType,
        })
    if err != nil {
        log.Fatalf("步骤3失败：上传文件出错: %v", err)
    }

    log.Printf("步骤3：成功上传文件 %s 到 %s/%s", filePath, bucketName, objectName)
}

十、数据库范式与键

10.1 三大范式：用生活类比建立直觉

把数据库表设计想象成整理一间杂乱的仓库：

• 第一范式（1NF）= 每个格子只放一样东西：仓库里不能一个箱子里塞"苹果、香蕉、橘子"混在一起，必须拆成一个格子放一种。对应到数据库：每个字段必须是原子的、不可再分的，不能有"逗号分隔的多值"或"数组"。
• 第二范式（2NF）= 每个货架只放一类货，且按主键归位：如果一个表既存"学生信息"又存"课程信息"还存"成绩"，就会乱。2NF 要求：先满足 1NF，且非主键列必须完全依赖于整个主键，不能只依赖主键的一部分（针对联合主键）。否则就把部分依赖的列拆到自己的表里。
• 第三范式（3NF）= 不要绕弯记信息：表里记"订单→客户→客户电话"，如果订单表直接存"客户电话"，而客户电话其实只依赖客户ID，那就出现了传递依赖（订单依赖客户，客户依赖电话）。3NF 要求：非主键列不能依赖于其他非主键列，只能直接依赖于主键。把"客户电话"放到客户表里，订单表只存客户ID。

graph TB
    A[第一范式 1NF
字段原子不可再分] --> B[第二范式 2NF
非主键列完全依赖整个主键]
    B --> C[第三范式 3NF
非主键列不传递依赖
只直接依赖主键]
    C --> D[规范化的表
减少冗余 避免更新异常]

桥接：范式的本质是"用空间换一致性"——通过拆表消除冗余，从而避免插入异常、更新异常、删除异常。但范式不是越高越好，实际开发中常故意"反范式"（冗余字段）来减少 JOIN、提升查询性能。

10.2 主键与候选键的区别

• 候选键（Candidate Key）：能够唯一标识一行、且不含多余列的属性或属性组合。一个表可以有多个候选键。
• 主键（Primary Key）：从候选键中选一个作为表的"官方身份标识"。一个表只能有一个主键。

类比：一个班级里，学号和身份证号都能唯一确定某个学生（都是候选键），但老师规定"用学号当主键"，身份证号就是没被选中的候选键。

-- 步骤1：建表时声明多个候选键（UNIQUE 约束）
CREATE TABLE student (
    id INT PRIMARY KEY,            -- 主键：被选定的候选键
    student_no VARCHAR(20) UNIQUE, -- 候选键1：学号唯一
    id_card VARCHAR(18) UNIQUE     -- 候选键2：身份证号唯一
) ENGINE=InnoDB;

-- 步骤2：主键不能为 NULL，候选键（UNIQUE）允许一个 NULL
INSERT INTO student (id, student_no, id_card) VALUES (1, 'S001', NULL);
-- 主键 id 若写 NULL 会直接报错

-- 步骤3：查看表的主键信息
SHOW KEYS FROM student WHERE Key_name = 'PRIMARY';

⚠️ 新手必踩的坑： 主键自动隐含 NOT NULL + UNIQUE，且 InnoDB 的聚簇索引就是按主键构建的。如果表没有显式主键，InnoDB 会选第一个非空唯一索引当聚簇索引；都没有则会隐式生成一个 6 字节的 row_id 当聚簇索引（不可见、不可控，严重影响性能）。

考点总结

三大范式按"原子性→完全依赖→不传递依赖"逐级收紧，目的是消除冗余和更新异常；主键是从多个候选键中挑选出的唯一身份标识，一个表只能有一个主键。面试常让你"设计一个订单表并说明如何满足 3NF"。

十一、存储引擎：MyISAM 与 InnoDB

11.1 用生活类比建立直觉

把两种存储引擎想象成两种仓库管理模式：

• MyISAM = 老式档案室：账本（数据）和检索卡（索引）是分开的两摞文件。查东西先翻检索卡，卡上写着"第几柜第几格"，再去柜子里取。仓库管理员不支持"事务"（不能保证一组操作要么全做要么全不做），但读起来很快、占用空间小。
• InnoDB = 现代智能仓库：货物按"货位编号"（主键）直接码放在带索引的货架上，检索卡和货物是一体的（聚簇索引）。支持事务、崩溃后能自动恢复、多人在不同货位同时操作互不干扰（行锁）。

graph LR
    subgraph MyISAM
        M1[.MYD 数据文件]
        M2[.MYI 索引文件]
        M1 -.->|索引叶子存地址| M2
    end
    subgraph InnoDB
        I1[.ibd 数据+索引文件]
        I2[主键聚簇
叶子即数据]
        I1 --> I2
    end

11.2 工程要点：两者核心区别

11.3 MyISAM 存储位置与存储格式

MyISAM 表物理上拆分为三个文件，默认在数据库对应目录下：

• .frm：表结构定义（MySQL 8.0 已合并进数据字典，不再单独文件）
• .MYD（MYData）：数据文件
• .MYI（MYIndex）：索引文件

存储格式有三种：

-- 步骤1：静态（定长）格式——字段都是定长类型（CHAR/INT 等），查询最快，但空间略浪费
CREATE TABLE t_static (
    id INT,
    code CHAR(10)
) ENGINE=MyISAM ROW_FORMAT=FIXED;

-- 步骤2：动态格式——含 VARCHAR/TEXT/BLOB 等变长字段，省空间但易产生碎片
CREATE TABLE t_dynamic (
    id INT,
    name VARCHAR(100)
) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;

-- 步骤3：压缩格式——只读场景，用 myisampack 工具压缩，极大节省空间
-- 压缩后表只读，适合历史归档日志
-- myisampack t_archive.MYI

11.4 MyISAM 与 InnoDB 各自特点总结

• MyISAM 特点：表锁、不支持事务、索引与数据分离（非聚簇）、COUNT(*) 无需扫描、全文索引早、占用空间相对小。适合"写少读多、可丢、无需事务"的场景（如统计日志、报表）。
• InnoDB 特点：行锁、支持事务与崩溃恢复、聚簇索引（按主键组织数据）、支持外键、MVCC 多版本读。MySQL 5.5 起默认引擎，适合绝大多数需要一致性和并发写的业务。

考点总结

InnoDB 与 MyISAM 的分水岭是"事务+行锁+聚簇索引+崩溃恢复"。MyISAM 的 .MYD/.MYI 分离存储、定长/动态/压缩三种格式是高频考点；现在新项目几乎都用 InnoDB。

十二、索引深入：类型、优劣与创建注意

12.1 用生活类比建立直觉

把索引类型想象成图书馆的不同检索手段：

• 聚簇/非聚簇 = 书是按编号直接上架（聚簇），还是另有一张索引导航卡（非聚簇）。
• 普通索引 = 普通书名卡，允许重复（多个同名书）。
• 唯一索引 = VIP 卡，一个名字只能对应一本书。
• 组合索引 = 多字段联合检索卡（如"作者+年份"），按字段顺序排。
• 全文索引 = 内容关键词检索，不是按书名而是按书里讲了什么词。
• 前缀索引 = 只取书名前几个字做卡，省空间但可能不精确（适合长字符串）。

graph TB
    A[索引类型] --> B[按数据结构
聚簇/非聚簇]
    A --> C[按功能
普通/唯一/组合/全文/前缀]
    B --> B1[InnoDB聚簇
叶子=整行]
    B --> B2[MyISAM非聚簇
叶子=地址]
    C --> C1[普通索引
允许重复]
    C --> C2[唯一索引
UNIQUE]
    C --> C3[组合索引
多列联合]
    C --> C4[全文索引
FULLTEXT]
    C --> C5[前缀索引
列前N字节]

12.2 索引的优缺点

优点：

1. 大幅减少扫描行数（B+ 树从 O(N) 降到 O(logN)）。
2. 避免排序（ORDER BY 走索引有序性）和临时表（GROUP BY）。
3. 把随机 I/O 变成顺序 I/O。

缺点：

1. 占用额外磁盘和内存（索引也是数据）。
2. 拖慢写操作（INSERT/UPDATE/DELETE 要同步维护索引）。
3. 索引过多时，优化器选错索引的概率反而上升。

12.3 创建索引的注意事项（高频考点）

-- 步骤1：高选择性列建索引（如 user_id），低选择性（如 gender）不宜单独建
-- 选择性 = COUNT(DISTINCT col) / COUNT(*)，越接近 1 越好

-- 步骤2：组合索引遵循最左前缀，常用查询列放前面
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);

-- 步骤3：长字符串用前缀索引节省空间
CREATE INDEX idx_name_prefix ON users(name(10));

-- 步骤4：尽量用覆盖索引，避免 SELECT *
-- 步骤5：避免对索引列做函数/运算，否则索引失效
-- 步骤6：主键用自增整型，避免 UUID 导致页分裂

12.4 使用索引一定能提高性能吗？

不一定。三种典型"索引反而不快"的场景：

1. 回表代价大于全表扫描：如 WHERE gender='M'，命中索引后要回表取大量行，优化器判断不如直接全表扫描。
2. 索引失效：函数操作、隐式类型转换、LIKE 以 % 开头、OR 一侧无索引等，导致走 ALL。
3. 小表：数据量极小（几百行），全表扫描本身就在内存里，建索引反而增加开销。

-- 步骤1：小表，索引无意义（优化器往往直接全表扫描）
CREATE TABLE tiny (id INT PRIMARY KEY, v INT);
-- 只有10行数据，加索引反而多维护成本

-- 步骤2：低选择性 + 大量回表，索引可能不如全表扫描
EXPLAIN SELECT * FROM users WHERE gender = 'M';
-- 若男性占 50%，type 可能是 ALL（优化器放弃索引）

⚠️ 新手必踩的坑： “建了索引查询就一定快"是错觉。索引是否生效、是否划算，最终由优化器基于 rows 估算决定。用 EXPLAIN 看 type 和 rows 才是硬道理。

考点总结

索引类型按"结构（聚簇/非聚簇）“和"功能（普通/唯一/组合/全文/前缀）“两个维度划分；索引有维护成本，低选择性、索引失效、小表三种情况用了索引也可能更慢。创建索引记住"高选择性、最左前缀、前缀省空间、避免函数运算”。

十三、字段类型选型与货币存储

13.1 CHAR 与 VARCHAR 的区别

类比：CHAR 像固定长度的快递盒（不管装没装满都占那么大地方），VARCHAR 像可伸缩的真空压缩袋（用多少占多少，但要多留点空间记长度）。

-- 步骤1：CHAR 定长，存入 'ab' 实际占 10 字节（补 8 个空格）
CREATE TABLE t_char (c CHAR(10));
INSERT INTO t_char VALUES ('ab');

-- 步骤2：VARCHAR 变长，存入 'ab' 实际约 3 字节（2 数据 + 1 长度）
CREATE TABLE t_varchar (v VARCHAR(10));
INSERT INTO t_varchar VALUES ('ab');

-- 步骤3：注意 CHAR 检索会去掉尾部空格，VARCHAR 保留
SELECT c='ab', v='ab' FROM t_char, t_varchar;

13.2 货币用什么字段类型（DECIMAL）

绝对不要用 FLOAT / DOUBLE 存钱——浮点数有精度误差（二进制无法精确表示十进制小数），会导致"0.1+0.2 != 0.3”。货币应使用 DECIMAL(M, D)（定点数），以字符串形式精确存储。

-- 步骤1：正确的货币字段定义（共16位，小数2位，可存到 99999999999999.99）
CREATE TABLE account (
    id BIGINT PRIMARY KEY,
    balance DECIMAL(16, 2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB;

-- 步骤2：精确计算，无浮点误差
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)) AS exact_sum;
-- 结果 = 0.30，而 0.1+0.2 用 DOUBLE 会得到 0.30000000000000004

-- 步骤3：错误示范（不要这样做）
CREATE TABLE account_wrong (
    balance DOUBLE  -- 浮点误差会让对账永远对不上
);

考点总结

CHAR 定长补空格、VARCHAR 变长省空间，定长短码用 CHAR、波动文本用 VARCHAR；货币必须用 DECIMAL 定点数，FLOAT/DOUBLE 的精度误差在金额场景是不可接受的。

十四、权限表、Binlog 格式与时间戳转换

14.1 MySQL 权限相关表

MySQL 的权限系统信息存放在 mysql 系统库的几张表里，权限从"全局→库→表→列→存储过程"逐级细化：

-- 步骤1：创建用户并授予全局只读权限（写入 mysql.user）
CREATE USER 'reader'@'%' IDENTIFIED BY 'pwd123';
GRANT SELECT ON *.* TO 'reader'@'%';

-- 步骤2：授予某库所有表的权限（写入 mysql.db）
GRANT ALL PRIVILEGES ON shop.* TO 'app'@'%';

-- 步骤3：查看某用户被赋予了哪些权限（从权限表汇总）
SHOW GRANTS FOR 'app'@'%';

-- 步骤4：刷新权限，使修改立即生效
FLUSH PRIVILEGES;

14.2 Binlog 的三种录入格式

Binlog 记录所有数据变更，有三种格式，核心权衡是"日志量 vs 主从一致性 vs 可读性”：

-- 步骤1：查看当前 binlog 格式
SHOW VARIABLES LIKE 'binlog_format';

-- 步骤2：动态修改为 ROW 格式（需 SUPER 权限）
SET GLOBAL binlog_format = 'ROW';

-- 步骤3：STATEMENT 格式下，带 NOW() 的语句在主从可能时间不一致
-- 而 ROW 格式直接记录具体变更值，规避了该问题

14.3 UNIX 与 MySQL 时间戳互转

UNIX 时间戳是自 1970-01-01 00:00:00 UTC 起的秒数。MySQL 提供两个互转函数：

-- 步骤1：UNIX 时间戳 → MySQL 日期时间
SELECT FROM_UNIXTIME(1690000000) AS dt;
-- 结果形如 '2023-07-22 15:06:40'

-- 步骤2：MySQL 日期时间 → UNIX 时间戳
SELECT UNIX_TIMESTAMP('2023-07-22 15:06:40') AS ts;
-- 结果 = 1690000000

-- 步骤3：获取当前时间的 UNIX 时间戳
SELECT UNIX_TIMESTAMP(NOW()) AS now_ts;

-- 步骤4：注意时区影响——UNIX_TIMESTAMP 按会话时区解释 DATETIME
-- 跨时区系统建议统一使用 UTC，避免 8 小时偏差

考点总结

权限表按粒度分 user（全局）、db（库）、tables_priv（表）、columns_priv（列）、procs_priv（过程）五级；binlog 三种格式 STATEMENT/ROW/MIXED 权衡"日志量 vs 一致性"，8.0 默认 ROW；时间戳互转用 UNIX_TIMESTAMP() 和 FROM_UNIXTIME()。

十五、数据操作进阶：大批量删除、临时表与连接

15.1 百万级数据如何删除（分批 + LIMIT）

一次性 DELETE FROM huge_table WHERE ... 是灾难：会锁大量行、写满 undo log、主从延迟飙升。正确做法是分批小事务删除，每批用 LIMIT 控制行数。

类比：搬家不能一次把所有家具扔下楼（会堵死楼道还压坏电梯），得一趟一趟搬，每趟限量。

-- 步骤1：分批删除，每批 1000 行，循环执行直到影响行数为 0
-- 假设按自增主键范围删除
DELETE FROM huge_table
WHERE create_time < '2020-01-01'
ORDER BY id
LIMIT 1000;
-- 在应用层循环调用，直到返回 0 行；每批之间可短暂 SLEEP 释放锁

-- 步骤2：更稳妥的做法——按主键游标推进
-- 先取最小待删 id，再每批删 < 当前游标 + 1000
DELETE FROM huge_table
WHERE id >= 1000 AND id < 2000;
-- 游标从 1000 推进到 2000、3000……每次独立小事务

-- 步骤3：如果是整表清空且无需回滚，用 TRUNCATE 最快（DDL，不可回滚）
TRUNCATE TABLE huge_table;

⚠️ 新手必踩的坑： 大表 DELETE 不加 LIMIT 会长时间持锁并撑大 binlog/undo；分批删除时务必带 ORDER BY 主键 LIMIT n，且每批提交后释放锁，避免主从延迟和锁等待。

15.2 临时表：是什么、何时删除

临时表（TEMPORARY TABLE）只对当前会话可见，其他会话看不到；会话结束（连接断开）时自动删除，无需手动 DROP。常用于复杂查询的中间结果缓存。

-- 步骤1：创建会话级临时表
CREATE TEMPORARY TABLE tmp_user_stat (
    user_id BIGINT,
    cnt INT
) ENGINE=Memory;  -- 也可用 InnoDB

-- 步骤2：写入中间计算结果
INSERT INTO tmp_user_stat
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

-- 步骤3：在当前会话内可像普通表一样查询
SELECT * FROM tmp_user_stat WHERE cnt > 10;

-- 步骤4：断开连接后临时表自动删除；也可手动提前删除
DROP TEMPORARY TABLE IF EXISTS tmp_user_stat;

-- 注意：同名临时表会"遮蔽"同名普通表，仅当前会话可见

15.3 内连接与外连接（INNER / OUTER JOIN）

• 内连接 INNER JOIN：只返回两表都匹配的行。类比：两个朋友圈的交集，只有两边都认识的人才出现在合影里。
• 外连接 OUTER JOIN：
  • LEFT JOIN：返回左表全部 + 右表匹配；右表无匹配则补 NULL。
  • RIGHT JOIN：返回右表全部 + 左表匹配。
  • FULL OUTER JOIN：左右都返回（MySQL 不直接支持，可用 UNION 模拟）。

-- 步骤1：内连接——只保留两表都匹配的用户及其订单
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 步骤2：左连接——列出所有用户，没下单的订单字段为 NULL
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 步骤3：找"从未下单"的用户（利用 LEFT JOIN + NULL 过滤）
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- 步骤4：MySQL 不支持 FULL OUTER JOIN，用 UNION 模拟
SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id=o.user_id
UNION
SELECT u.name, o.amount FROM users u RIGHT JOIN orders o ON u.id=o.user_id;

15.4 UNION 与 UNION ALL 的注意事项

• UNION：合并结果并去重（内部多一次排序/去重，有性能开销）。
• UNION ALL：直接拼接，不去重（更快，数据量大时首选）。

-- 步骤1：UNION 去重（性能较低，两结果集若有重复只保留一份）
SELECT id FROM users WHERE age > 30
UNION
SELECT id FROM orders WHERE amount > 1000;

-- 步骤2：UNION ALL 不去重（更快，确认无重复或允许重复时用）
SELECT id FROM users WHERE age > 30
UNION ALL
SELECT id FROM orders WHERE amount > 1000;

-- 步骤3：注意两查询的列数和数据类型必须一致，且列名以第一个查询为准
-- 错误示例：SELECT id FROM users UNION SELECT name, age FROM orders; -- 列数不匹配会报错

⚠️ 新手必踩的坑： 90% 的场景用 UNION ALL 就够了，却惯性写成 UNION 白白付出去重开销。只有确需去重时才用 UNION；另外两个子查询的列数、顺序、类型必须对齐。

考点总结

百万级删除要分批 + LIMIT 小事务，避免长锁和主从延迟；临时表会话级可见、连接断开自动删除；INNER 取交集、LEFT/RIGHT 保留一侧全量；UNION 去重有开销、UNION ALL 直接拼接更快，二者列结构必须一致。

十六、B 树 vs B+ 树：结构与选型

16.1 用生活类比先建立直觉

把两种索引结构想象成两种不同的图书馆找书法：

• B 树图书馆 = 每层的指示牌旁边也堆着书：你走到 3 楼，指示牌写着"小说区在 5 楼"，但 3 楼角落也摆了几本小说可以直接拿走。也就是说，非叶子节点既存索引键，也存数据。好处是可能"中途就拿到书"，坏处是每一层能挂的指示牌（索引键）变少，楼就得盖得更高（树更高），找书要多爬几层楼（多几次磁盘 I/O）。
• B+ 树图书馆 = 只有地下仓库放书，楼上全是纯指示牌：1~N 楼只挂"XX 区在楼下"的牌子（只存索引键），真正的书全部码放在地下一层（叶子节点），而且地下一层的书架之间用传送带串成一条线（叶子节点双向链表）。你要找书一定下到地下一层；要找"从 A 到 Z 所有小说"，顺着传送带一路拿就行。

一句话区分：B 树是"沿途都能拿书"，B+ 树是"书只在底层、且底层排好队连成串"。

graph TB
    subgraph B树["B 树：内部节点也存数据"]
        BR[根节点
键10·数据A
键20·数据B]
        BR --> BN1[节点
键5·数据C
键8·数据D]
        BR --> BN2[节点
键15·数据E
键18·数据F]
        BN1 --> BL1[叶子
键1·数据G
键3·数据H]
        BN1 --> BL2[叶子
键6·数据I]
        BN2 --> BL3[叶子
键12·数据J]
        BN2 --> BL4[叶子
键22·数据K]
    end

    subgraph BPlus["B+ 树：只有叶子存数据，且叶子双向链表串联"]
        PR[根节点
仅键10·20]
        PR --> PN1[非叶子
仅键5·8]
        PR --> PN2[非叶子
仅键15·18]
        PN1 --> PL1[叶子
键1·3·5·6·8
+整行数据]
        PN2 --> PL2[叶子
键10·12·15·18·20·22
+整行数据]
        PL1 -.->|双向链表| PL2
    end

桥接：B+ 树之所以成为 MySQL/InnoDB 的默认索引结构，核心就三个理由——减少 I/O、范围查询友好、叶子天然有序。下面逐一拆解。

16.2 工程要点

结构差异对比

为什么 MySQL 选 B+ 树

1. 减少磁盘 I/O（最核心理由）：InnoDB 页大小 16KB，主键 bigint 8 字节 + 指针 6 字节 ≈ 14 字节，一个非叶子节点约能放 16KB / 14B ≈ 1170 个键。因为 B+ 树非叶子不存数据，这些空间全用来放键和指针，扇出极大。3 层 B+ 树可存 1170 × 1170 × 每页行数(约16) ≈ 2190 万 行。而 B 树非叶子也塞数据，扇出骤降、树变高，每次查询要多读几层页 = 多几次随机 I/O。
2. 范围查询友好：B+ 树叶子是双向链表，WHERE id BETWEEN 100 AND 200 只需定位到 100 的叶子，顺着链表向右扫到 200 即可，无需反复回根。B 树没有这层链表，范围查询要反复中序回溯，效率差。
3. 叶子有序，全表/排序高效：ORDER BY 主键、COUNT(*) 等只需顺序遍历叶子链表，B+ 树天然支持"顺序 I/O"，把随机读变成顺序读。

-- 步骤1：查看 InnoDB 页大小（默认 16384 字节 = 16KB）
SHOW VARIABLES LIKE 'innodb_page_size';

-- 步骤2：理解"扇出大→树矮"：以下查询可看 B+ 树高度（≈3 表示千万级表只需 3 次 I/O）
-- 通过表数据量估算：页大小 / (主键长度 + 指针长度) ≈ 单节点扇出
SELECT 16384 / (8 + 6) AS approx_fanout_per_node;
-- 结果约 1170，说明单节点能指向约 1170 个子节点

-- 步骤3：对比——如果非叶子也存整行数据（假设行 200 字节）
-- 单节点只能放 16384 / 200 ≈ 81 个键，扇出骤降、树高翻倍，I/O 次数倍增

⚠️ 新手必踩的坑： “B 树更快，因为可能在中途就拿到了数据”——这是错觉。数据库索引在磁盘上，瓶颈是 I/O 次数而非单次比较。B+ 树用"更矮的树 + 更大的扇出"换来更少的 I/O，远比"偶尔少走一层"划算。所以包括 MySQL、PostgreSQL、Oracle 在内的关系型数据库，索引几乎清一色 B+ 树。

考点总结

B 树内部节点也存数据、叶子不串联；B+ 树只有叶子存数据且叶子用双向链表串联。MySQL 用 B+ 树的三点原因：非叶子纯键→扇出大→树矮→I/O 少；叶子链表→范围查询快；叶子有序→全表扫描/排序高效。

十七、实战：学生成绩数据库与语文 Top3（字节原题）

17.1 用生活类比先建立直觉

把"查某门课成绩前三名"想象成学校发奖状：

1. 先有一本学生花名册（谁叫什么），一本课程目录（哪些课），还有一本成绩登记册（谁、哪门课、多少分）。这三本册子分开记，才是规范的"第三范式"设计——成绩册只记"学号+课号+分数"，不直接把学生姓名、课程名抄进去（否则改个名字要改一堆成绩记录）。
2. 发奖状时，老师先翻成绩册挑出"语文"那几行，按分数从高到低排，取最上面三张——这就是 ORDER BY score DESC LIMIT 3。
3. 如果老师想要"每门课各取前三"（而不仅是语文），就得先按课分组、组内排名，再取每组前三——这就是窗口函数 ROW_NUMBER() 的用武之地。

17.2 工程要点：建表 + 可运行 SQL

步骤 1~3：建三张表（student / course / score）

-- 步骤1：学生表（花名册）
CREATE TABLE student (
    id   BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    INDEX idx_name(name)            -- 按名字查学生时用
) ENGINE=InnoDB;

-- 步骤2：课程表（课程目录）
CREATE TABLE course (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL,
    INDEX idx_course_name(course_name)   -- 按课程名定位课程时用
) ENGINE=InnoDB;

-- 步骤3：成绩表（登记册，外键关联两张维度表）
CREATE TABLE score (
    id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    student_id BIGINT NOT NULL,
    course_id  BIGINT NOT NULL,
    score      DECIMAL(5,2) NOT NULL,    -- 百分制，定点数避免浮点误差
    INDEX idx_course_score(course_id, score),  -- 关键：按课程+分数排序/过滤
    INDEX idx_student(student_id),
    UNIQUE KEY uk_stu_course(student_id, course_id),  -- 一个学生一门课只有一条成绩
    CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(id),
    CONSTRAINT fk_score_course  FOREIGN KEY (course_id)  REFERENCES course(id)
) ENGINE=InnoDB;

-- 步骤4：插入测试数据（语文 course_id=1，数学 course_id=2）
INSERT INTO student (name) VALUES ('张三'),('李四'),('王五'),('赵六'),('钱七');
INSERT INTO course (course_name) VALUES ('语文'),('数学'),('英语');
INSERT INTO score (student_id, course_id, score) VALUES
(1,1,88.50),(2,1,95.00),(3,1,92.50),(4,1,78.00),(5,1,99.00),
(1,2,80.00),(2,2,85.00),(3,2,90.00);

方法一：ORDER BY + LIMIT（最简单，面试首选）

-- 步骤5：查"语文"成绩 Top3（分数降序，取前三）
SELECT s.name, sc.score
FROM score sc
JOIN student s ON s.id = sc.student_id
JOIN course  c ON c.id = sc.course_id
WHERE c.course_name = '语文'
ORDER BY sc.score DESC        -- 分数从高到低
LIMIT 3;                      -- 只取前 3 行
-- 结果：钱七 99.00、李四 95.00、王五 92.50

方法二：窗口函数 ROW_NUMBER()（可扩展到"每门课各取前三"）

-- 步骤6：用 ROW_NUMBER 在结果集内给语文成绩排名，再取前 3
SELECT name, score
FROM (
    SELECT s.name,
           sc.score,
           ROW_NUMBER() OVER (ORDER BY sc.score DESC) AS rn  -- 全局按分数排名
    FROM score sc
    JOIN student s ON s.id = sc.student_id
    JOIN course  c ON c.id = sc.course_id
    WHERE c.course_name = '语文'
) t
WHERE rn <= 3;
-- 结果同方法一。若为"每门课各取前三"，把窗口改成
-- ROW_NUMBER() OVER (PARTITION BY c.course_name ORDER BY sc.score DESC)

执行计划讲解

-- 步骤7：看方法一的执行计划
EXPLAIN
SELECT s.name, sc.score
FROM score sc
JOIN student s ON s.id = sc.student_id
JOIN course  c ON c.id = sc.course_id
WHERE c.course_name = '语文'
ORDER BY sc.score DESC
LIMIT 3;

执行顺序与关键点：

1. course 表：WHERE course_name='语文' 走 idx_course_name，type=ref，迅速定位到 course_id=1。
2. score 表：用 course_id=1 去 idx_course_score(course_id, score) 查找。因为该联合索引的第二列就是 score，且查询需要"按 score 降序"，优化器可以顺着索引的有序性直接拿到排好序的分数，无需额外 filesort（Extra 不出现 Using filesort）。
3. student 表：sc.student_id 走主键（或 idx_student）回表/eq_ref 拿到学生姓名。
4. LIMIT 3：排序/扫描到前 3 行即停止，不需要把所有语文成绩都排完，所以即使成绩有 100 万条，取前三也很快。

⚠️ 新手必踩的坑： 如果 score 表上只有 INDEX idx_course(course_id) 而没有 (course_id, score) 联合索引，那么 ORDER BY score 就无法利用索引有序性，会出现 Using filesort（内存/磁盘排序），数据量大时明显变慢。这就是"联合索引把排序列也带上"的实际收益。

考点总结

三表设计遵循第三范式：student（谁）、course（什么课）、score（学号+课号+分数，外键关联）。Top3 用 ORDER BY score DESC LIMIT 3 最直观；窗口函数 ROW_NUMBER() OVER(ORDER BY ...) 可扩展到"每门课各取前三"。性能关键在 idx_course_score(course_id, score) 让过滤+排序同时走索引、避免 filesort。

十八、为什么索引不全用 Hash 结构

18.1 用生活类比先建立直觉

把 Hash 索引想象成一排带编号的抽屉柜：

• 你给一个 key（比如 user_id=10086），柜子用一个哈希函数瞬间算出"第 37 号抽屉"，拉开就拿到数据——等值查询快到飞起，时间复杂度 O(1)。
• 但如果你想找"编号在 100 到 200 之间的所有抽屉"，或者"按编号从小到大排列"，抽屉柜就傻了：它没有"顺序"概念，只能把每个抽屉都拉开挨个看。这就是 Hash 的致命弱点——只认精确匹配，不认范围、不认顺序。

而 B+ 树像一本按拼音排序的字典：你既能用二分快速翻到"张三"那一页（等值），也能轻松摘出"从张到李"的所有条目（范围），还能直接按字母顺序读（排序）。代价是单次查找是 O(logN) 而非 O(1)，但综合能力远强于 Hash。

18.2 工程要点：Hash 索引的局限

MySQL 不全用 Hash 的根本原因：

1. 不支持范围与排序：业务里 WHERE age > 18、ORDER BY create_time 无处不在，Hash 完全无能为力。
2. 不支持最左前缀/组合索引：Hash 是对"整个索引键"算哈希，无法像 B+ 树那样按 (a,b,c) 逐列利用。
3. 哈希冲突：不同 key 可能算出同一个抽屉，需要额外链表解决，最坏退化成 O(N)。
4. 内存限制：纯 Hash 索引（如 Memory 引擎）数据必须全在内存，表一大就放不下；而 B+ 树配合 16KB 页能优雅地放在磁盘上。
5. 无法做覆盖扫描/部分匹配：Hash 是"一次性定位"，不能像 B+ 树那样只扫索引的一部分。

-- 步骤1：Memory 引擎表可用纯 Hash 索引（仅内存，重启丢失）
CREATE TABLE hash_demo (
    id BIGINT,
    val VARCHAR(50),
    INDEX USING HASH (id)     -- 显式声明 Hash 索引
) ENGINE=Memory;

-- 步骤2：Hash 等值查询极快
SELECT * FROM hash_demo WHERE id = 10086;   -- ✅ O(1)

-- 步骤3：但范围查询 Hash 帮不上忙，只能全表扫
SELECT * FROM hash_demo WHERE id > 100;      -- ❌ 无法利用 Hash 索引

-- 步骤4：InnoDB 的"自适应哈希索引"（AHI）是补偿手段
-- 它只对频繁等值访问的 B+ 树索引页自动建内存 Hash，加速点查
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';  -- 默认 ON

⚠️ 新手必踩的坑： InnoDB 没有用户可创建的 Hash 索引——它的索引本质都是 B+ 树。“自适应哈希索引（AHI）“是 InnoDB 内部的自动优化：当某个 B+ 树索引被频繁做等值查询时，它在内存里自动为该索引页建一个 Hash 结构来加速点查，你无法直接控制它建在哪。所以面试被问"为什么 InnoDB 不用 Hash 索引”，答的是"业务需要范围/排序/模糊，B+ 树综合能力更强”。

考点总结

Hash 索引等值 O(1) 极快，但不支持范围、排序、模糊、最左前缀、组合索引，还有哈希冲突和内存限制。B+ 树虽是 O(logN)，却能一站式满足等值+范围+排序+模糊，且磁盘友好。InnoDB 用户层只有 B+ 树索引，AHI 只是内部对热点等值查询的内存加速。

十九、LIKE 模糊查询与索引

19.1 用生活类比先建立直觉

把 B+ 树索引想象成按拼音排序的通讯录：

• 你要找"姓张的人"，直接翻到 Z 开头那一摞，顺着拿就行——这对应 LIKE '张%'（前缀已知），索引能定位起点后顺序扫描。
• 你要找"名字里带’伟’的人"，通讯录是按拼音排的，你根本不知道’伟’会出现在哪一页，只能从第 1 页翻到最后 1 页逐条看——这对应 LIKE '%伟'（后缀模糊）或 LIKE '%伟%'（前后都模糊），索引彻底失效，退化成全表扫描。

核心原理就四个字：最左前缀——B+ 树的有序性是从"最左边第一个字符"开始的，只有前缀确定，才能利用这棵有序树；前缀不确定，有序性就无从用起。

19.2 工程要点

三种 LIKE 写法与索引关系

-- 步骤1：前缀模糊 'abc%' —— 能走索引（定位 a 开头，顺序扫）
EXPLAIN SELECT * FROM users WHERE name LIKE '张%';
-- type=range，key=idx_name，能利用 B+ 树有序性

-- 步骤2：后缀模糊 '%abc' —— 不能走索引（前缀未知，无法定位起点）
EXPLAIN SELECT * FROM users WHERE name LIKE '%三';
-- type=ALL（全表扫描），索引失效

-- 步骤3：前后模糊 '%abc%' —— 不能走索引
EXPLAIN SELECT * FROM users WHERE name LIKE '%小明%';
-- type=ALL（全表扫描），索引失效

替代方案：覆盖索引

即使 %abc% 不能走普通索引，如果查询只返回索引列本身（不需要回表取其他列），仍可能用到"索引全扫描 + 覆盖"，避免回表，比全表扫快：

-- 步骤4：覆盖索引缓解 '%abc%'（假设只查 name，且 name 有索引）
-- Extra 可能显示 Using where; Using index（扫描索引树但不回表）
EXPLAIN SELECT name FROM users WHERE name LIKE '%三%';
-- 虽仍是扫描，但扫的是更小的索引树而非整行数据

替代方案：全文索引 FULLTEXT

对于"文章内容包含某词"这类模糊搜索，正确武器是全文索引，而不是 LIKE '%...%'：

-- 步骤5：建全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_title(title);

-- 步骤6：用 MATCH ... AGAINST 做关键词检索（能走全文索引）
SELECT * FROM articles
WHERE MATCH(title) AGAINST('数据库' IN NATURAL LANGUAGE MODE);
-- 比 LIKE '%数据库%' 快几个数量级，且支持相关性排序

⚠️ 新手必踩的坑： 很多人以为"加个索引，LIKE '%关键词%' 就能快"。错——只要前缀带 %，B+ 树索引就帮不上忙。正确做法：能确定前缀就用 '关键词%'；要搜中间内容就用全文索引或倒排索引（如 ES）；只取索引列可用覆盖索引缓解。另外中文全文索引在 MySQL 5.7+ 需指定 ngram 解析器（WITH PARSER ngram）才能按词切分。

考点总结

LIKE 'abc%' 前缀已知能走索引（range），LIKE '%abc' 和 LIKE '%abc%' 前缀未知、索引失效（全表扫），根因是 B+ 树的最左前缀有序性。替代方案：前缀模糊用 'abc%'；只查索引列用覆盖索引缓解；内容搜索用全文索引 FULLTEXT（中文配 ngram）或 ES。

二十、读写分离：主从架构与延迟治理

20.1 用生活类比先建立直觉

把数据库的读写分离想象成银行的柜台与自助查询机：

• 主库（Master）= 柜台：所有"存钱、取钱、转账"（写操作）只能在柜台办，保证账本只有一处被修改，不会出现两本账对不上的情况。
• 从库（Slave）= 自助查询机：只办理"查余额、打流水"（读操作）。多摆几台查询机，就能同时服务更多查账的客户，分担柜台压力。
• 主从同步 = 柜台把每笔业务抄送给各查询机：主库每发生一笔变更，通过 binlog 复制给从库，从库重放（replay）后数据保持一致。

这种"写走主、读走从"的架构，让读流量被分摊到多个从库，系统整体吞吐量大幅提升。但有个绕不开的麻烦——主从延迟。

主从延迟问题：你刚在柜台存了 100 块（主库已写），立刻跑到查询机查余额，查询机还没收到这笔同步（从库落后了几百毫秒），显示"余额没变"。这就是经典的"写完立刻读，读到了旧数据"。

20.2 工程要点

主从架构图

graph TB
    App[应用层] -->|写请求 INSERT/UPDATE/DELETE| Master[(主库 Master
处理写操作)]
    App -->|读请求 SELECT| Proxy[读写分离中间件
ShardingSphere/MyCat]

    Master -->|binlog 复制| Slave1[(从库 Slave1
处理读请求)]
    Master -->|binlog 复制| Slave2[(从库 Slave2
处理读请求)]
    Master -->|binlog 复制| Slave3[(从库 Slave3
处理读请求)]

    Proxy --> Slave1
    Proxy --> Slave2
    Proxy --> Slave3

    subgraph 问题区
        D[主从延迟
主库写完 从库尚未同步] -.->|写完立刻读从库可能读到旧值| App
    end

主从延迟的解决方案

-- 步骤1：查看主从复制状态（在从库执行）
SHOW SLAVE STATUS\G
-- 关注：
--   Slave_IO_Running / Slave_SQL_Running 都应为 Yes
--   Seconds_Behind_Master 即从库落后主库的秒数（主从延迟指标）

-- 步骤2：查看主库 binlog 位置（用于判断复制进度）
SHOW MASTER STATUS\G

-- 步骤3：应用层"强制走主库"的示意（伪代码）
-- if (刚写入且需要立即读) {
--     使用主库连接查询();
-- } else {
--     使用从库连接查询();   // 普通读走从库
-- }

// Go 语言：用读写分离中间件 ShardingSphere-Proxy 时的连接示意
// 实际路由由中间件完成，应用只需区分"写数据源"和"读数据源"
package main

import (
    "database/sql"
    "log"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // 步骤1：写数据源指向主库（处理 INSERT/UPDATE/DELETE）
    writeDB, err := sql.Open("mysql", "app:pwd@tcp(master-host:3306)/shop")
    if err != nil {
        log.Fatalf("步骤1失败：打开主库连接出错: %v", err)
    }

    // 步骤2：读数据源指向从库（或通过中间件地址自动负载均衡）
    readDB, err := sql.Open("mysql", "app:pwd@tcp(slave-host:3306)/shop")
    if err != nil {
        log.Fatalf("步骤2失败：打开从库连接出错: %v", err)
    }

    // 步骤3：写操作走主库
    _, _ = writeDB.Exec("UPDATE account SET balance = balance - 100 WHERE id = ?", 1)

    // 步骤4：普通读走从库（分摊读压力）
    var balance int
    _ = readDB.QueryRow("SELECT balance FROM account WHERE id = ?", 1).Scan(&balance)

    // 步骤5：关键读（刚写入需立即读）强制走主库，避免主从延迟读到旧值
    _ = writeDB.QueryRow("SELECT balance FROM account WHERE id = ?", 1).Scan(&balance)
}

⚠️ 新手必踩的坑： 读写分离不是银弹。它解决的是"读多写少"场景的扩展性问题，但引入了一致性复杂度。常见翻车点：1）写完立刻读从库读到旧值（用"强制走主库"解决）；2）从库延迟过高拖垮读（用并行复制/半同步缓解）；3）事务内混用读写导致路由混乱（同一事务的读写应绑定同一数据源）。

考点总结

读写分离：主库处理写、从库分担读，通过 binlog 复制保持同步，提升读吞吐量。核心痛点是主从延迟——写完立刻读从库可能读到旧值。解决方案：关键读强制走主库、用 ShardingSphere/MyCat 等中间件统一路由、半同步复制缩短延迟窗口、并行复制加速追平。

二十一、全表扫描的识别与优化

21.1 用生活类比先建立直觉

把"全表扫描（type=ALL）“想象成在图书馆找某本书，但目录卡片丢了——管理员只能从第一个书架的第一本书开始，一本一本地翻，一直翻到最后一个书架，确认"这本书到底在不在这里”。数据量小的时候还凑合，一旦表里有上百万本书（行），这种"逐本翻"就要命了。

在 EXPLAIN 的执行计划里，type=ALL 就是"逐本翻"的官方盖章：MySQL 没有可用的索引帮它快速定位，只能把整张表的每一行都读一遍。它和 type=index（扫整个索引树）看着都"全扫"，但 ALL 扫的是数据行，index 至少扫的是更小的索引树——两者都该警惕，但 ALL 通常更糟。

graph TB
    A[收到一条 SELECT] --> B{EXPLAIN 里 type 是什么}
    B -->|const/eq_ref/ref/range| C[走了索引
只扫少量行 ✓]
    B -->|index| D[扫了整棵索引树
未回表尚可接受]
    B -->|ALL| E[全表扫描
逐行读数据 ✗]

    E --> F{为什么没走索引?}
    F -->|对列用了函数| G1[如 WHERE YEAR(created_at)=2024]
    F -->|隐式类型转换| G2[如 phone VARCHAR 却传数字]
    F -->|LIKE '%x' 前缀模糊| G3[前缀未知无法定位]
    F -->|OR 一侧无索引| G4[优化器放弃索引]
    F -->|列选择性太低| G5[如 gender='M' 占一半]
    F -->|根本没建索引| G6[WHERE 条件列无索引]

桥接：识别全表扫描的唯一标准就是 EXPLAIN 的 type 列出现 ALL（或该走索引却走了 ALL）。优化它不等于"无脑加索引"——低选择性列加索引反而更慢，此时要靠覆盖索引、分区、归档等手段缩小扫描范围。

21.2 工程要点

type=ALL 的含义与定位

-- 步骤1：用 EXPLAIN 看 type 列，如果出现 ALL 就是全表扫描
EXPLAIN SELECT * FROM users WHERE age + 1 = 25;
-- type=ALL, rows≈全表行数，Extra 通常为 NULL（未用索引）

-- 步骤2：对比——同样的查询，去掉列上的函数就能走索引
EXPLAIN SELECT * FROM users WHERE age = 24;
-- type=ref（走了 idx_age），rows 大幅减少

典型触发场景速查表

⚠️ 新手必踩的坑： 不要看到 type=ALL 就恐慌着去加索引。像 gender='M' 这种低选择性查询，加索引后优化器仍可能选 ALL，因为走索引要回表、并不比直接全表扫便宜。这类场景要么接受全表扫（小表无所谓），要么用覆盖索引 / 分区 / 归档缩小扫描面。

优化手段一：覆盖索引，避免回表放大

-- 步骤3：原始全表扫描（要回表取所有列）
EXPLAIN SELECT * FROM users WHERE city = '北京';
-- type=ALL

-- 步骤4：如果只查索引列，用覆盖索引缓解（扫索引树而非整行）
-- 假设 city 有索引
EXPLAIN SELECT city FROM users WHERE city = '北京';
-- Extra 显示 Using where; Using index，扫描的是更小的索引树

优化手段二：分区表，把"全表扫"降为"分区剪枝"

-- 步骤5：按时间范围分区，查询只扫命中分区（分区裁剪）
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount DECIMAL(12,2),
    created_at DATETIME NOT NULL,
    INDEX idx_user(user_id)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p2024Q1 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p2024Q2 VALUES LESS THAN (TO_DAYS('2024-07-01')),
    PARTITION p2024Q3 VALUES LESS THAN (TO_DAYS('2024-10-01')),
    PARTITION p2024Q4 VALUES LESS THAN (TO_DAYS('2025-01-01'))
);

-- 步骤6：只查 Q1 数据，优化器只扫 p2024Q1 一个分区
EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-04-01';
-- partitions 列只出现 p2024Q1，扫描量从全表降到单分区

注意：分区表的主键/唯一索引必须包含分区键，否则建表报错——这是分区最常见的坑。

优化手段三：历史数据归档，缩小"活表"体积

-- 步骤7：把 3 年前的数据迁到归档表，活表只留热数据
-- 归档表结构与活表一致，可放冷存储
CREATE TABLE orders_archive LIKE orders;

-- 步骤8：分批把冷数据挪走（避免长事务锁表）
INSERT INTO orders_archive
SELECT * FROM orders
WHERE created_at < '2021-01-01'
ORDER BY id LIMIT 5000;

-- 步骤9：确认已迁走后再从活表删除（分批 DELETE）
DELETE FROM orders
WHERE created_at < '2021-01-01'
ORDER BY id LIMIT 5000;
-- 活表变小后，即便偶发 ALL，扫描行数也少得多

LIMIT / ORDER BY 与全表扫描的关系

LIMIT 本身不能避免全表扫描——SELECT * FROM t LIMIT 10 仍可能先全表扫再取前 10 行（当无 ORDER BY 或无法用索引排序时）。真正能减少扫描的是"让过滤/排序走索引"。深度分页 LIMIT 100000, 10 的优化（延迟关联、游标分页）已在第七章详述，此处不再重复。

考点总结

EXPLAIN 的 type=ALL 表示全表扫描：逐行读取整张表，是慢查询的头号信号。典型触发场景包括函数操作列、隐式类型转换、LIKE '%x'、OR 一侧无索引、低选择性列、条件列无索引。优化不只靠加索引（低选择性列加索引没用），还要善用覆盖索引、分区裁剪（PARTITION BY RANGE）、历史数据归档来缩小扫描范围；LIMIT 不能单独避免全表扫描，必须配合索引过滤/排序。

二十二、商品信息大数据量的库表设计

22.1 用生活类比先建立直觉

把电商商品想象成商场里的"商品模板"和"具体货品"两层：

• SPU（Standard Product Unit，标准产品单元）= 商品模板/款式：比如"iPhone 15"就是一个 SPU——它描述了这个款式是一台手机、什么品牌、什么系列。但商场里实际卖的不是"iPhone 15 这个款式"，而是"iPhone 15 128G 蓝色"这样的一台台具体货。
• SKU（Stock Keeping Unit，库存量单位）= 具体货品/最小发货单元：在 SPU"iPhone 15"下面，根据"容量+颜色"能组合出很多 SKU，如"128G 蓝"“256G 黑”。每个 SKU 有独立的价格、库存，是真正被下单、被发货的对象。

类比：SPU 是"这道菜的做法"（宫保鸡丁），SKU 是"这份具体的宫保鸡丁（中辣、加饭）"——顾客买单的是后者，但菜单上展示的是前者。

graph TB
    SPU[SPU 商品模板
iPhone 15
标题/品牌/系列/详情] -->|1 对 多| SKU1[SKU 蓝色 128G
price/stock/spec]
    SPU -->|1 对 多| SKU2[SKU 黑色 256G
price/stock/spec]
    SPU -->|1 对 多| SKU3[SKU 白色 512G
price/stock/spec]

    subgraph 查询路径
        Q1[用户浏览商品页] --> Q2[按 SPU 展示款式信息]
        Q2 --> Q3[选择规格后定位到具体 SKU]
        Q3 --> Q4[下单锁该 SKU 库存]
    end

桥接：SPU/SKU 分离的核心是"把变化慢的展示信息（标题、详情）与变化快的交易信息（价格、库存）拆开"。这样上架新颜色只需加 SKU，不用复制整段详情；库存扣减也只需锁单行 SKU，避免热行竞争。

22.2 工程要点

SPU / SKU 基础表结构

-- 步骤1：SPU 表——存变化慢的款式信息
CREATE TABLE product_spu (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    title       VARCHAR(200) NOT NULL,
    brand_id    BIGINT NOT NULL,
    category_id BIGINT NOT NULL,
    description TEXT,
    INDEX idx_cat_brand(category_id, brand_id)
) ENGINE=InnoDB;

-- 步骤2：SKU 表——存变化快的库存/价格，外键关联 SPU
CREATE TABLE product_sku (
    id         BIGINT PRIMARY KEY AUTO_INCREMENT,
    spu_id     BIGINT NOT NULL,
    spec_json  JSON NOT NULL,        -- 如 {"color":"蓝","capacity":"128G"}
    price      DECIMAL(12,2) NOT NULL,
    stock      INT NOT NULL DEFAULT 0,
    INDEX idx_spu(spu_id),            -- 按 SPU 查其下所有 SKU
    INDEX idx_stock(spu_id, stock)    -- 热销/有货筛选时走索引
) ENGINE=InnoDB;

大数据量：分库分表 / 分桶

当 SKU 达到亿级，单表扛不住，按 spu_id 或 sku_id 分片：

-- 步骤3：按 spu_id 取模分 16 张表（应用层或 ShardingSphere 路由）
-- table_index = spu_id % 16
-- product_sku_0, product_sku_1, ... product_sku_15 结构相同
-- 按 spu_id 查询能精确定位分片；按其他维度查需路由表/基因法（见第八章）

-- 步骤4：分桶（bucket）思路——同一品类放同一桶，方便按品类聚合分析
-- 可用 category_id 做二级路由，减少跨片 JOIN

反范式设计：用冗余换查询速度

纯范式下"查商品时带品牌名"要 JOIN brand 表，亿级数据 JOIN 成本高。常见反范式是冗余品牌名到 SPU 表：

-- 步骤5：冗余 brand_name 到 spu，避免每次 JOIN brand 表
ALTER TABLE product_spu ADD COLUMN brand_name VARCHAR(50) NOT NULL DEFAULT '';
-- 写入时同步维护；以少量冗余换取列表页免 JOIN 的提速
-- 代价：品牌改名时要批量更新冗余列（可用异步任务保证最终一致）

索引策略

• SPU：按 (category_id, brand_id) 建联合索引，支撑"分类+品牌"筛选。
• SKU：必建 idx_spu(spu_id)；价格/库存筛选建 (spu_id, stock) 或 (spu_id, price)。
• 避免在大文本 description 上建普通索引，搜索用全文索引或外部 ES。

读写分离 + 缓存预热

商品读多写少，天然适合读写分离；且大促前要做缓存预热——把热点商品提前加载进 Redis，避免开门瞬间海量请求穿透到数据库。

// 步骤6：Go 语言实现缓存预热——大促前把热点 SKU 批量写入 Redis
package main

import (
    "context"
    "fmt"
    "log"
    "time"

    "github.com/redis/go-redis/v9"
)

func preheatHotSKU() {
    ctx := context.Background()

    // 步骤1：连接 Redis（读多写少场景的加速层）
    rdb := redis.NewClient(&redis.Options{Addr: "redis:6379"})
    defer rdb.Close()

    // 步骤2：从数据库查出 Top N 热点 SKU（实际可用销量/访问量排序）
    hotSKUs := []struct {
        SKUID int64
        Price int64
        Stock int
    }{
        {SKUID: 1001, Price: 599900, Stock: 50},
        {SKUID: 1002, Price: 699900, Stock: 30},
    }

    // 步骤3：批量写入 Redis，设置较短过期时间防止脏数据常驻
    pipe := rdb.Pipeline()
    for _, s := range hotSKUs {
        key := fmt.Sprintf("sku:%d", s.SKUID)
        pipe.HSet(ctx, key, "price", s.Price, "stock", s.Stock)
        pipe.Expire(ctx, key, 3600*time.Second)
    }
    if _, err := pipe.Exec(ctx); err != nil {
        log.Fatalf("步骤3失败：缓存预热出错: %v", err)
    }
    log.Println("步骤3：热点 SKU 已预热到 Redis，开门可挡住大部分查库请求")
}

⚠️ 新手必踩的坑： 缓存预热不是"预热完就完事"。库存会实时变化，预热数据必须设 TTL 并配合"写库后删缓存/更新缓存"的失效策略，否则用户看到的是过期的旧库存。另外，对 SKU 库存扣减要用 UPDATE ... SET stock = stock - 1 WHERE id = ? AND stock > 0 的行锁 + 条件乐观扣减，防止超卖。

考点总结

商品大数据量设计核心是 SPU（款式，变化慢）/ SKU（具体货品，变化快）分离；海量 SKU 按 spu_id/sku_id 分库分表/分桶；用冗余品牌名等反范式手段避免亿级 JOIN；索引聚焦分类/品牌/价格/库存维度；配合读写分离与大促前缓存预热（热点 SKU 预载 Redis）抗住读峰，库存扣减须用行锁 + 条件更新防超卖。

二十三、MySQL 主键设计

23.1 用生活类比先建立直觉

把主键想象成图书馆每本书的"永久书架编号"：

• 书的排放顺序就是按这个编号来的（InnoDB 聚簇索引按主键排）。如果编号是"顺着 1、2、3、4 发的"（自增），新书就直接码在最后一排的末尾，整整齐齐，不用挪动任何旧书。
• 如果编号是"随机生成的 UUID"（像 7f3a-9c2b-...），新书可能被分配到中间某个位置，而那个位置已经塞满了——图书馆只能把后半排的书整体往后挪一格腾出空位，这就是页分裂（Page Split），非常费劲。
• 而且编号越长，书脊上写的编号占的地方越大——一个书架（16KB 页）能摆的书就越少，书架层数（树高）就得加高，查书要多走几层。

所以主键设计的铁律：短、有序、稳定。

graph TB
    subgraph 自增["自增主键：顺序追加，无分裂"]
        A1[页1: 1 2 3 ... 100] --> A2[页2: 101 102 ... 200]
        A2 --> A3[新行 201 直接追加到页尾 ✓]
    end

    subgraph UUID["UUID/雪花 乱序：插入导致页分裂"]
        U1[页1: 5 17 23 ... 99] --> U2[插入 id=50 需腾位]
        U2 --> U3[页1 拆成 页1+页1' 半满
空间碎片 写放大 ✗]
    end

桥接：主键之所以"不能太大"，是因为它是聚簇索引的键，会出现在每一棵二级索引的叶子节点里（二级索引存主键值用于回表）。主键每多 1 字节，所有二级索引都跟着膨胀。所以能用 BIGINT（8 字节）就别用 36 字节的 UUID 字符串。

23.2 工程要点

为什么主键不能太大

-- 步骤1：BIGINT 主键 8 字节，紧凑
CREATE TABLE t_bigint (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    INDEX idx_name(name)   -- 二级索引叶子存主键值，8 字节
) ENGINE=InnoDB;

-- 步骤2：UUID 字符串主键 36 字节，所有二级索引跟着膨胀
CREATE TABLE t_uuid (
    id CHAR(36) PRIMARY KEY,   -- 如 '7f3a9c2b-...'
    name VARCHAR(50),
    INDEX idx_name(name)       -- 每个索引项多存 36 字节，索引体积暴涨
) ENGINE=InnoDB;
-- 表越大、二级索引越多，UUID 主键的存储与内存代价越明显

主键越大：① 单个 16KB 页能放的索引键越少 → B+ 树更高 → 查询多几次 I/O；② 所有二级索引都冗余存主键值 → 索引总体积膨胀、Buffer Pool 命中率下降。

自增整型 vs UUID vs 雪花算法

-- 步骤3：雪花算法生成 64 位整数当主键（应用层生成后传入）
-- 结构：1位符号 + 41位时间戳 + 10位机器号 + 12位序列号
CREATE TABLE t_snowflake (
    id BIGINT PRIMARY KEY,       -- 由应用层用雪花算法生成，时间递增
    name VARCHAR(50),
    INDEX idx_name(name)
) ENGINE=InnoDB;
-- 既保持 8 字节紧凑，又时间有序 → 写入几乎追加、极少页分裂，且天然分布式唯一

主键连续性：优劣权衡

• 连续（自增）优点：写入追加、无页分裂、索引最紧凑、范围扫描快。
• 连续（自增）缺点：
  1. 可预测：竞争对手能靠 id+1 爬取全部订单/用户（安全风险）。
  2. 分库分表冲突：多实例各自自增会产生重复 ID，需引入发号器（雪花/号段）。
  3. 热点集中：所有新写入都落在最后一个页，写入热点明显。
• 不连续（UUID/雪花）优点：不可预测、分布式友好。
• 不连续缺点：乱序导致页分裂（UUID）、或仍需保证时间有序（雪花已缓解）。

实务建议：绝大多数业务用雪花算法 64 位整数主键——兼顾紧凑（8 字节）、时间有序（少分裂）、分布式唯一（带机器号）。仅在单库单表且不在意外泄时，用自增整型即可。

页分裂原理与代价

-- 步骤4：观察页分裂（InnoDB 内部，通过 performance_schema 的 innodb_metrics）
-- 关注 index_page_splits / index_page_reorg_attempts 两个指标
SELECT NAME, COUNT FROM information_schema.INNODB_METRICS
WHERE NAME IN ('index_page_splits','index_page_reorg_attempts');

-- 步骤5：把表按主键顺序重建，消除历史分裂造成的碎片（OPTIMIZE）
ALTER TABLE t_uuid ENGINE=InnoDB;
-- 重建后数据按主键重新紧凑排列，碎片减少、页更满

页分裂的代价：① 一次分裂要分配新页、拷贝半数行，写放大；② 分裂后两页都半满，空间碎片；③ 树结构变化可能抬升树高。所以"有序主键"对写入性能影响巨大。

考点总结

InnoDB 聚簇索引按主键组织数据，主键会冗余进所有二级索引。设计铁律是"短、有序、稳定"：主键越大（如 36 字节 UUID）索引越膨胀、树越高、I/O 越多；自增整型写入追加无页分裂但分库分表冲突且可预测；UUID 无序引发频繁页分裂；雪花算法 64 位整数兼顾紧凑、时间有序与分布式唯一，是主流选择。连续主键性能优但需防 ID 泄露与分片冲突，乱序主键写入代价高。

二十四、Vitess on Kubernetes 设计分片键避免热点订单表

24.1 用生活类比先建立直觉

类比：把订单表想象成"全国快递分拣中心"。分片键（sharding key）就是"按什么维度分拣包裹"：如果按"下单时间"分拣（范围分片），那么每天新下的单全堆进"今天"那个分拣口，它爆满、其他口闲着——这就是热点。如果按"客户 ID 哈希"分拣，包裹会被均匀撒到所有口子，谁都不闲着但也都不爆——这就是均衡。

Vitess 是跑在 Kubernetes 上的 MySQL 分片中间件。订单表（orders）怎么选分片键，直接决定了会不会出现"某台 MySQL 分片被订单写爆"的热点问题。

flowchart TB
    W[订单写入] --> Q{按什么分片键?}
    Q -->|created_at 范围| R1[最新订单全落同一分片
热点!]
    Q -->|customer_id 哈希| R2[均匀散到所有分片
均衡 ✓]
    Q -->|order_id 哈希| R3[均匀但跨客户查询全扇出]

这张图点出核心矛盾：范围分片易热点，哈希分片均衡但跨键查询要扇出。订单表最怕"按时间范围分片导致新单集中"，所以优先用 customer_id 哈希。

24.2 工程要点

错误示范：用 created_at 范围分片 = 天然热点

-- 步骤1：如果按 created_at 做范围分片（Vitess 用 range vindex）
-- 新订单的 created_at 永远是最大的，于是永远落在"最后一个分片"
-- 大促时所有写请求集中打在同一个 MySQL 分片 → 热点、连接耗尽
-- 这是订单表分片最常见的翻车点

正确示范：VSchema 用 hash vindex 分散到 customer_id

Vitess 用 VSchema（逻辑 schema）声明分片方式。下面是一份 orders 表用 customer_id 哈希分片的 VSchema：

# vschema_orders.yaml —— Vitess 逻辑分片定义
apiVersion: v1
kind: VitessVschema
metadata:
  name: orders-vschema
spec:
  shardGateway: {}
  keyspaces:
    orders_keyspace:
      sharded: true                 # 步骤1：声明为分片表空间
      vindexes:
        customer_hash:              # 步骤2：定义一个哈希分片索引
          type: hash                # hash 类型 → 数据均匀散布到各分片
          params:
            "column": "customer_id"
      tables:
        orders:
          columnVindexes:
            - column: customer_id   # 步骤3：用 customer_id 作为分片键
              name: customer_hash
        order_items:
          columnVindexes:
            - column: customer_id   # 步骤4：子表与父表同分片键，避免跨分片 JOIN
              name: customer_hash

⚠️ 新手必踩的坑： 选分片键要看"查询模式"，不能只看"分布均匀"。customer_id 哈希虽然均匀，但如果你经常要"按 order_id 查订单"，而 order_id 不是分片键，Vitess 就只能扇出查询所有分片再合并——很慢。解决：要么把 order_id 里编码进 customer_id（基因法），要么冗余一张 order_id → customer_id 的路由表（见第八章双写/路由表思路）。

分片键选择的权衡表

Reshard 在线扩分片（热点预警后的扩容）

当某个 customer_id 范围（如大商家）仍然偏热，可以在 K8s 上用 Vitess 的 Reshard 工作流把分片数翻倍，数据自动重新平衡：

# 步骤1：定义目标分片数（从 2 个扩到 4 个）
vtctlclient Reshard -source_shards 0 -target_shards '0,1' Create orders_keyspace.reshard
# 步骤2：切流，确认无误后完成
vtctlclient Reshard -tablet_type rdonly SwitchTraffic orders_keyspace.reshard
vtctlclient Reshard Complete orders_keyspace.reshard

考点总结

Vitess 在 K8s 上把 MySQL 分片化，订单表分片键选错就会热点。最忌用 created_at 范围分片（新单永远落最后一个分片，大促直接打爆）；推荐用 customer_id 哈希分片（hash vindex）把订单均匀撒到所有分片。但哈希分片下"按 order_id 查"会扇出所有分片，可用基因法（order_id 编码 customer_id）或路由表缓解。数据真偏热时再用 Vitess Reshard 工作流在线翻倍分片、自动再平衡。核心：分片键 = 均匀度 + 查询模式 的折中。

二十五、基于 XtraBackup + S3 实现 Point-in-Time Recovery 的 CronJob

25.1 用生活类比先建立直觉

类比：把数据库备份想象成"每天拍全家福 + 随时记日记"。XtraBackup 每天拍的全量快照相当于"全家福"（能恢复到拍照那一刻的样子）；而 MySQL 的 binlog 相当于"从拍照后到现在写的每一笔日记"。Point-in-Time Recovery（PITR，按时间点恢复）就是把"全家福"恢复到昨天晚上 10 点，再按日记一笔笔重放到今天上午 9 点 30 分——于是数据库精确回到"今天 9:30"的状态，而不是只能回到"昨天 10 点"。

把"全家福"和"日记"都存到 S3 对象存储（无限容量、跨可用区），即使本地机房没了，备份还在。Kubernetes 里用 CronJob 定时自动拍全家福并上传 S3。

flowchart LR
    MySQL[(MySQL)] -->|xtrabackup 物理备份| Full[全量快照]
    MySQL -->|binlog 持续归档| Bin[增量日志]
    Full -->|xbcloud put| S3[(S3 桶)]
    Bin -->|mysqlbinlog + aws s3 cp| S3
    S3 -->|恢复时拉取| Restore[xtrabackup --prepare + mysqlbinlog --stop-datetime]

这张图是 PITR 的全景：全量（XtraBackup）→ S3，增量（binlog）→ S3；恢复时先还原全量、再回放 binlog 到目标时间点。

25.2 工程要点

步骤1：CronJob 定时做全量备份并直传 S3

用 Percona XtraBackup 的 xbstream + xbcloud 把备份流式直接上传到 S3，避免落本地磁盘占空间：

# xtrabackup-full-cronjob.yaml
apiVersion: batch/v1
kind: CronJob
metadata:
  name: mysql-xtrabackup-full
  namespace: mysql
spec:
  schedule: "0 2 * * *"          # 步骤1：每天凌晨 2 点做一次全量备份
  concurrencyPolicy: Forbid      # 步骤2：上一次没跑完就不并发，避免互相覆盖
  jobTemplate:
    spec:
      template:
        spec:
          containers:
            - name: xtrabackup
              image: percona/percona-xtrabackup:8.0
              env:
                - name: MYSQL_HOST
                  value: "mysql-primary.mysql.svc.cluster.local"
                - name: MYSQL_USER
                  value: "backup"
                - name: MYSQL_PASSWORD
                  valueFrom:
                    secretKeyRef:
                      name: mysql-backup-secret
                      key: password
                - name: S3_BUCKET
                  value: "my-mysql-backup"
                - name: S3_PREFIX
                  value: "xtrabackup/full"
              command:
                - /bin/bash
                - -c
                - |
                  # 步骤3：流式备份并直接上传到 S3（xbcloud 内部处理分块/重试）
                  xtrabackup --backup \
                    --stream=xbstream \
                    --user=$MYSQL_USER --password=$MYSQL_PASSWORD \
                    --host=$MYSQL_HOST \
                    --target-dir=/tmp/backup | \
                    xbcloud put \
                      --storage=s3 \
                      --s3-bucket=$S3_BUCKET \
                      --s3-endpoint=s3.amazonaws.com \
                      --parallel=4 \
                      $S3_PREFIX/$(date +%F_%H-%M)-
                  # 步骤4：成功后打印标记，失败则 CronJob 以非 0 退出、触发告警
                  echo "full backup uploaded to s3://$S3_BUCKET/$S3_PREFIX/$(date +%F_%H-%M)"                  
              resources:
                requests:
                  cpu: 500m
                  memory: 1Gi
          restartPolicy: OnFailure

⚠️ 新手必踩的坑： XtraBackup 备份账户需要 RELOAD、LOCK TABLES、REPLICATION CLIENT、PROCESS 等权限，且必须能连到主库或可用的副本。不要拿超级权限账号明文写进镜像——用 secretKeyRef 从 Secret 注入密码（如本例），必要时用 IAM Role 代替长期密钥。

步骤2：binlog 增量持续归档到 S3（PITR 的关键）

全量只能恢复到"拍照点"，要精确到任意时间点，必须持续把 binlog 传 S3：

#!/usr/bin/env bash
# binlog-archive.sh —— 把新生成的 binlog 增量传到 S3
# 步骤1：从 MySQL 拉取当前 binlog 列表
mysql -h $MYSQL_HOST -u $MYSQL_USER -p"$MYSQL_PASSWORD" \
  -e "SHOW BINARY LOGS;" | tail -n +2 | awk '{print $1}' > /tmp/binlogs.txt

# 步骤2：逐个上传尚未归档的 binlog
while read -r logfile; do
  if ! aws s3 ls "s3://$S3_BUCKET/binlog/$logfile"; then
    # 步骤3：用 mysqlbinlog 流式导出并直传 S3（不落本地）
    mysqlbinlog -h $MYSQL_HOST -u $MYSQL_USER -p"$MYSQL_PASSWORD" \
      --read-from-remote-server --raw "$logfile" \
      | aws s3 cp - "s3://$S3_BUCKET/binlog/$logfile"
  fi
done < /tmp/binlogs.txt

也可用一个独立 CronJob 每 5 分钟跑一次 binlog-archive.sh，确保 binlog 几乎实时进 S3，PITR 的精度就取决于它。

步骤3：按时间点恢复（PITR 实操）

# 步骤1：从 S3 拉取最近的全量备份并准备（apply log）
xbcloud get --storage=s3 --s3-bucket=my-mysql-backup \
  --s3-endpoint=s3.amazonaws.com xtrabackup/full/2026-08-10_02-00- | \
  xbstream -x -C /var/lib/mysql
xtrabackup --prepare --target-dir=/var/lib/mysql   # 步骤2：回滚未提交事务，使备份一致

# 步骤3：拉取目标时间点之后的 binlog，重放到"今天 09:30"
aws s3 cp s3://my-mysql-backup/binlog/ /tmp/binlog/ --recursive
mysqlbinlog --stop-datetime="2026-08-10 09:30:00" \
  /tmp/binlog/mysql-bin.000123 /tmp/binlog/mysql-bin.000124 \
  | mysql -h restored-host -u root -p

echo "已恢复到 2026-08-10 09:30:00"

考点总结

PITR = 全量备份（XtraBackup）+ binlog 增量归档，二者都进 S3 才完整。K8s 里用 CronJob 每天 xtrabackup --backup --stream=xbstream | xbcloud put 流式直传 S3（不落本地盘），再用另一个高频 CronJob 把 binlog 持续归档 S3。恢复时先 --prepare 全量、再 mysqlbinlog --stop-datetime 回放 binlog 到目标时刻。注意：备份账号用最小权限 + Secret 注入，绝不明文写镜像；CronJob 设 concurrencyPolicy: Forbid 防止并发覆盖。binlog 归档越频繁，PITR 精度越高。

二十六、主从延迟超过 5 秒时自动提升只读节点为写节点并更新 ConfigMap

26.1 用生活类比先建立直觉

类比：把主库想象成"总指挥"，从库是"副指挥"。正常情况下总指挥发令、副指挥抄送。某天总指挥失联了（主库卡死 / 网络分区 / 严重复制阻塞），但副指挥其实还清醒、数据也只差几秒。如果死等总指挥，整个系统就停摆。这时需要一个"自动接班机制"：监控发现"总指挥失联且副指挥数据只差 5 秒以内"，就提拔副指挥为总指挥，并立刻通知所有部门"以后听新总指挥的"——这个"通知"在 K8s 里就是更新 ConfigMap（业务读写的入口地址）。

⚠️ 注意：主从延迟 >5s 通常不该直接提升（延迟大说明从库数据落后多，提升会丢数据）。这里把"延迟 >5s"作为"主库疑似不可用"的预警信号之一，真正的提升前提是：主库已确认失联（fenced）+ 从库延迟已收敛到可接受范围，且先对老主做隔离（防脑裂）。下面给出带安全围栏的实现。

flowchart TB
    Mon[监控脚本 每 5s] -->|SHOW REPLICA STATUS| R[从库]
    R -->|Seconds_Behind_Source| C{主库探活?}
    C -->|主库 PING 失败| D[隔离老主 fence]
    D -->|延迟已收敛<阈值| E[STOP REPLICA
提升为可写]
    E --> F[更新 ConfigMap
writer=新主]
    F --> G[应用热加载新入口]
    C -->|主库仍活| H[只告警，不提升
等待延迟回落]

这张图是"带围栏的自动故障转移"：先确认主库真失联 → 隔离老主防脑裂 → 从库延迟收敛后才提升 → 更新 ConfigMap 把写流量切过去。

26.2 工程要点

步骤1：ConfigMap 声明读写入口（应用从这里读主库地址）

# mysql-endpoints-configmap.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-endpoints
  namespace: mysql
data:
  # 步骤1：应用读取 writer 字段作为写入口；reader 为只读副本
  writer: "mysql-primary.mysql.svc.cluster.local:3306"
  reader: "mysql-replica.mysql.svc.cluster.local:3306"

步骤2：监控 + 自动提升的健康检查脚本

脚本每 5 秒检查一次：主库是否可达？从库 Seconds_Behind_Source（MySQL 8 改名为 Seconds_Behind_Source，旧版 Seconds_Behind_Master）是否超过阈值。满足"主库失联 + 从库延迟可控"才执行提升，并调用 kubectl 更新 ConfigMap。

#!/usr/bin/env bash
# auto-failover.sh —— 带围栏的自动提升（仅当主库真失联时）
PRIMARY="mysql-primary.mysql.svc.cluster.local"
REPLICA="mysql-replica.mysql.svc.cluster.local"
LAG_LIMIT=5                       # 步骤1：延迟阈值（秒），仅作"主库疑似不可用"信号
NS="mysql"

while true; do
  # 步骤2：探主库活活性
  if mysql -h $PRIMARY -u monitor -p"$MON_PWD" -e "SELECT 1" >/dev/null 2>&1; then
    # 主库活着：即使从库延迟 >5s 也只告警，不提升（避免丢数据）
    LAG=$(mysql -h $REPLICA -u monitor -p"$MON_PWD" -e "SHOW REPLICA STATUS\G" \
            | grep Seconds_Behind_Source | awk '{print $2}')
    if [ "${LAG:-0}" -gt "$LAG_LIMIT" ]; then
      echo "WARN: 从库延迟 ${LAG}s > ${LAG_LIMIT}s，主库仍存活，仅告警不切换"
    fi
    sleep 5; continue
  fi

  # 步骤3：主库失联 → 先等几秒确认不是网络抖动
  sleep 10
  mysql -h $PRIMARY -u monitor -p"$MON_PWD" -e "SELECT 1" >/dev/null 2>&1 && { sleep 5; continue; }

  # 步骤4：隔离老主（fence），防止它恢复后双写造成脑裂
  # 实际可用 iptables / 云厂商 API 隔离其网络，此处用脚本占位
  echo "FENCE: 隔离老主 $PRIMARY 的网络写入"

  # 步骤5：提升从库为可写
  mysql -h $REPLICA -u root -p"$REPLICA_PWD" -e "STOP REPLICA; RESET REPLICA ALL;"
  echo "已提升 $REPLICA 为可写节点"

  # 步骤6：更新 ConfigMap 的 writer 指向新主
  kubectl -n $NS patch configmap mysql-endpoints \
    --patch "{\"data\":{\"writer\":\"$REPLICA:3306\"}}"
  echo "ConfigMap 已更新：writer -> $REPLICA"

  # 步骤7：跳出循环，防止反复切换（后续由人工介入确认）
  break
done

步骤3：应用热加载 ConfigMap 变更

应用不能硬编码主库地址，要从 ConfigMap（或挂载文件）动态读取，并在 ConfigMap 更新后重新初始化连接池：

// 步骤1：从 ConfigMap 数据读取 writer 地址（实际可由 k8s informer watch 变更）
func loadWriterFromConfigMap() string {
    // 伪代码：通过 client-go 读取 mysql-endpoints 的 writer 字段
    cm := k8sClient.CoreV1().ConfigMaps("mysql").Get(context.Background(), "mysql-endpoints", metav1.GetOptions{})
    return cm.Data["writer"]
}

// 步骤2：ConfigMap 变更时回调，重建写连接池（连接池要支持热替换）
func onConfigMapChanged(newWriter string) {
    old := writeDB
    writeDB = sql.Open("mysql", "app:pwd@tcp("+newWriter+")/shop")
    if old != nil {
        old.Close() // 步骤3：优雅关闭旧连接，避免泄露
    }
    log.Println("写连接池已切换到", newWriter)
}

⚠️ 新手必踩的坑： 自动提升最怕脑裂——老主其实没真死，只是监控和它之间短暂不通，结果两边都在写。所以"隔离老主（fence）“这一步绝不能省：必须先把老主的网络写入掐断（或让它进入只读），再提升新主、再更 ConfigMap。另外提升后要人工确认老主数据已追平，否则 STOP REPLICA 后的新主可能比老主少几秒数据。

考点总结

主从延迟 >5s 本身是"数据落后"的信号，不该直接提升（会丢落后那几秒的数据）；正确做法是把它当作"主库疑似不可用"的预警，配合"主库探活 + 隔离老主（fence 防脑裂）“才执行提升。流程：监控 Seconds_Behind_Source + 主库 SELECT 1 → 主库失联且确认非抖动 → fence 老主 → 从库 STOP REPLICA; RESET REPLICA ALL 变可写 → kubectl patch configmap 把 writer 指向新主 → 应用 watch ConfigMap 热替换写连接池。核心铁律：先隔离老主再提升，宁可少切不要脑裂。

二十七、自测题与动手练习

自测题（10道）

1. MySQL 的三层架构分别是什么？查询缓存在哪个版本被废弃，为什么？

2. 请解释 redo log、binlog、undo log 各自的作用，以及两阶段提交为什么要引入两个阶段而不是直接一次写入。

3. 给定联合索引 (a, b, c)，以下哪些查询能走索引？为什么？

• WHERE a = 1 AND b = 2 AND c = 3
• WHERE b = 2 AND c = 3
• WHERE a = 1 AND c = 3
• WHERE a = 1 AND b > 2 AND c = 3

4. InnoDB 在 RR 隔离级别下是如何基本解决幻读问题的？MVCC 的 ReadView 可见性判断规则是什么？

5. LIMIT 100000, 10 为什么慢？请说出至少两种优化方案及其原理。

6. 请画出 B 树和 B+ 树的结构差异，并解释为什么 MySQL/InnoDB 最终选择 B+ 树而非 B 树作为索引结构（至少说出三个理由）。

7. 给定学生表、课程表、成绩表三张表，请写出 SQL 查询"语文成绩前三名"的学生姓名与分数，至少给出两种方式（ORDER BY+LIMIT 与窗口函数），并说明为什么联合索引 (course_id, score) 能避免 filesort。

8. 读写分离架构下，为什么"写完主库立刻读从库"可能读到旧数据？请给出至少两种解决主从延迟导致不一致的方案。

9. 如何通过 EXPLAIN 判断一条 SQL 是否发生了全表扫描？type=ALL 与 type=index 有什么区别？请列举至少三种会导致索引失效、退化成全表扫描的常见写法，并分别给出对应的优化思路（可涉及覆盖索引、分区裁剪或历史数据归档）。

10. 为什么说 InnoDB 的主键"不能太大”？请对比自增整型、UUID、雪花算法三种主键方案在"长度、有序性、页分裂、分布式友好度"上的差异，并说明为什么乱序主键（如 UUID）会引发页分裂、影响写入性能。

动手练习（3个）

练习1：索引实战

创建一张包含 10 万条数据的 orders 表（字段：id, user_id, amount, status, created_at），完成以下任务：

• 给 (user_id, status, created_at) 建联合索引
• 用 EXPLAIN 验证以下查询的索引使用情况：按 user_id 查、按 status 查、按 user_id加status 查、按 created_at 查
• 构造一个覆盖索引查询和一个需要回表的查询，对比 Extra 列的差异

练习2：事务与隔离级别实验

开启两个 MySQL 会话（两个终端），设置隔离级别为 RR，完成以下实验：

• 会话A开启事务并查询某行数据
• 会话B修改该行数据并提交
• 会话A再次查询，验证是否读到新值（应该读不到，因为是 RR）
• 会话A执行 UPDATE 语句更新该行（触发当前读），观察是否报错或行为变化
• 用 SHOW ENGINE INNODB STATUS 查看锁信息

练习3：慢查询优化全流程

• 开启慢查询日志，阈值设为 0.5 秒
• 构造一条慢查询（如对 10 万条数据的无索引列做 WHERE 加 ORDER BY）
• 用 EXPLAIN 分析执行计划
• 针对性优化（加索引/改SQL）
• 用 EXPLAIN 验证优化效果，对比优化前后的 type、key、rows、Extra 字段变化

二十八、本章小结

本章从 MySQL 的软件架构出发，系统讲解了 MySQL 面试中的核心知识点：

1. 架构层面：MySQL 采用连接层、服务层、引擎层的三层架构，存储引擎可插拔。一条 SQL 查询经过连接器、分析器、优化器、执行器、存储引擎的完整流程。数据落盘采用 WAL 机制，通过 redo log（崩溃恢复）加 binlog（复制备份）加 undo log（回滚MVCC）三种日志协作，两阶段提交保证 redo log 与 binlog 的一致性。

2. 索引机制：B+ 树的非叶子节点只存索引键，扇出大、树矮，适合磁盘存储。聚簇索引将数据和主键索引存在一起，非聚簇索引需要回表。覆盖索引避免回表，最左前缀原则决定联合索引的匹配规则，索引下推在引擎层过滤减少回表。索引选择性低的列（如性别）不适合单独建索引。

3. 事务与锁：ACID 的原子性靠 undo log、持久性靠 redo log、隔离性靠 MVCC加锁。四种隔离级别分别解决脏读、不可重复读、幻读问题。MVCC 通过隐藏列加 undo log 版本链加 ReadView 实现非锁定读。InnoDB 的行锁包括记录锁、间隙锁和临键锁，意向锁用于快速判断表级是否有行锁。

4. 性能优化：慢查询排查遵循"日志定位、EXPLAIN分析、优化索引或SQL、验证效果"的流程。索引失效的常见场景包括函数操作列、隐式类型转换、OR连接、LIKE 百分号开头等。深度分页用延迟关联或游标分页优化。

5. 分库分表与迁移：垂直分表按列拆、水平分表按行拆。分表策略有范围分片、哈希分片、一致性哈希。分表后按非分片键查询可用路由表、基因法或双写。数据迁移工具有 mysqldump、LOAD DATA INFILE、DataX、Canal 等，文件存储优先选对象存储（MinIO/OSS），静态资源叠加CDN加速。

掌握以上知识，面试时能够画出 B+ 树结构图、MVCC 版本链图、两阶段提交流程图，并清晰讲解索引回表、事务隔离、慢查询优化的原理，足以应对大部分 MySQL 面试场景。