01 | 基础架构：一条 SQL 查询语句是如何执行的？
请求自上而下经过 Server 层 → Engine 层。Server 层承担连接、解析、优化、执行，所有引擎共享；Engine 层负责数据存取，可插拔。
| 组件 | 职责 | 备注 |
|---|---|---|
| 连接器 | 建立 / 鉴权 / 维持连接 | 长短连接由客户端控制，服务端 wait_timeout 兜底 |
| 查询缓存 | 命中即直接返回 | 8.0 已移除；表一更新整张缓存失效，不建议用 |
| 分析器 | 词法 + 语法 + 预处理；列 / 表名校验在此发生 | 报 Unknown column 'k' in 'field list' 就在此 |
| 优化器 | 选索引、决定 join 顺序 | |
| 执行器 | 鉴权后调引擎接口；返回结果集 | 引擎扫描行数 ≠ rows_examined（见 §05） |
常用查询
SHOW PROCESSLIST; -- 当前连接
SELECT @@wait_timeout; -- 28800s = 8h
SHOW VARIABLES LIKE 'wait_timeout';
SELECT @@query_cache_type; -- 8.0 之前可观察是否开启
清理连接占用的资源但保留连接：调接口函数 mysql_reset_connection()，不是 SQL 语句。
为什么
SELECT k FROM ...在表没有k列时报错在分析器而非执行器？因为字段定义是元数据，分析器读元数据就能判断列是否存在，无需打开表。
02 | 日志系统：一条 SQL 更新语句是如何执行的？
WAL（Write-Ahead Logging）：先写日志，再写磁盘。日志顺序写，数据随机写。
redo log vs binlog
| 维度 | redo log | binlog |
|---|---|---|
| 归属 | InnoDB 引擎专属 | Server 层，所有引擎共享 |
| 内容 | 物理：哪个数据页做了什么修改 | 逻辑：行变更的语义（语句 / 行格式） |
| 写入方式 | 循环写，固定大小 | 追加写，写满切下一个 |
| 用途 | 事务、crash-safe | 归档、主从同步、恢复 |
| 共享性 | 只有 InnoDB 自己用（私有物理格式） | 其他数据库 / 引擎可消费（语义通用） |
两阶段提交
binlog 写入夹在 redo log 的 prepare → commit 之间，使两个日志的提交状态保持一致。崩溃恢复决策：
| 崩溃时机 | redo log 状态 | binlog 状态 | 恢复决策 |
|---|---|---|---|
| binlog 写入前崩 | prepare | 未写 | 回滚 |
| binlog 已写、commit 前崩 | prepare | 已写 | 提交 |
| commit 之后崩 | commit | 已写 | 直接提交 |
关键参数
SELECT @@innodb_flush_log_at_trx_commit; -- 1 推荐：每事务持久化 redo
SELECT @@sync_binlog; -- 1 推荐：每事务持久化 binlog
SELECT @@max_binlog_size / 1024 / 1024; -- MB
SELECT @@innodb_page_size / 1024; -- 16（默认页大小 KB）
SELECT @@binlog_format; -- STATEMENT / ROW / MIXED
binlog 格式：STATEMENT 记 SQL（时间函数等不确定语义有风险）；ROW 记前后两行内容，体积大但确定。
记录以 页（page） 为单位存取，默认 16KB —— 读一行实际读 16KB。
参考：
03 | 事务隔离：为什么你改了我还看不见？
三种读异常
| 异常 | 含义 | 出现于隔离级别 |
|---|---|---|
| 脏读 | 读到其他事务未提交的数据 | READ UNCOMMITTED |
| 不可重复读 | 同一记录前后读取内容不一致 | READ UNCOMMITTED、READ COMMITTED |
| 幻读 | 同一范围前后读取行数不一致 | READ UNCOMMITTED、READ COMMITTED；InnoDB RR 因 MVCC 已避免 |
脏读示例：
-- session1
BEGIN;
UPDATE T1 SET k=2 WHERE id=1; -- 未提交
-- session2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT k FROM T1 WHERE id=1; -- 2 ← 脏读
不可重复读示例：
-- session1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT k FROM T1 WHERE id=1; -- 1
-- session2
UPDATE T1 SET k=2 WHERE id=1; -- 已提交
-- session1
SELECT k FROM T1 WHERE id=1; -- 2 ← 不可重复读
幻读示例（RC 下）：
-- session1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT count(*) FROM T1 WHERE k=1; -- 1
-- session2
INSERT INTO T1 (k) VALUES (1);
-- session1
SELECT count(*) FROM T1 WHERE k=1; -- 2 ← 幻读
四种隔离级别
| 级别 | 视图创建时机 | 阻挡的异常 |
|---|---|---|
| READ UNCOMMITTED | 无视图，直接读最新值 | — |
| READ COMMITTED | 每条 SQL 开始时 | 脏读 |
| REPEATABLE READ | 事务首条语句执行时 | 脏读 + 不可重复读（+ 幻读，仅 InnoDB） |
| SERIALIZABLE | 加读 / 写锁强制串行 | 脏读 + 不可重复读 + 幻读 |
RR 在标准 SQL 里无法避免幻读，但 InnoDB 在 RR 下叠加 MVCC + Next-Key Lock 后能避免（仅 snapshot read；
FOR UPDATE等当前读另议，见 §08）。
常用配置
SELECT @@tx_isolation; -- 当前隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET autocommit = 0; -- 关闭自动提交
COMMIT WORK AND CHAIN; -- 等价 commit; begin;
长事务的影响与避免
-- 持续超过 60s 的事务
SELECT * FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(timediff(now(), trx_started)) > 60;
避免清单：
- 确认
autocommit = 1；框架别习惯性把所有语句BEGIN/COMMIT包起来。 SET MAX_EXECUTION_TIME给单语句兜底。- 监控
information_schema.innodb_trx，设长事务阈值告警。 - 开发期开
general_log，提前发现问题。
参考：
04 | 深入浅出索引（上）
索引数据结构对比
| 结构 | 等值 | 范围 | 适用场景 |
|---|---|---|---|
| 哈希表 | 优 | 不支持 | 等值查询为主 |
| 有序数组 | 优 | 优 | 静态数据 |
| 平衡 BST | O(log N) | O(log N) | 内存索引 |
| N 叉树（B+） | 高度低 | 高度低 | InnoDB 磁盘索引 |
估算：InnoDB 一个 16KB 页放约 1170 个
BIGINT索引项（16K / (8B 数据 + 6B 子树指针)）。N 叉 B+ 树深度极浅，3 层即覆盖十亿行级别。
主键索引 vs 二级索引
| 维度 | 主键索引（聚簇 / clustered） | 二级索引（secondary） |
|---|---|---|
| 叶子节点 | 整行数据 | 主键值 |
| 查询路径 | 一次定位 | 命中后 回表 到主键索引 |
覆盖索引可避免回表，见 §05。
自增主键的两条好处
- 写性能：始终追加，不挪记录，不触发页分裂。
- 存储：主键短 → 二级索引叶子节点小，整体空间小。
何时业务字段适合做主键
KV 场景：只有一个唯一索引且就是它，没有二级索引带来的回表与膨胀代价。
叶子节点存的是什么
InnoDB 叶子节点 = 一个 页（默认 16KB），页里存多行 行数据。“叶子存页"是结构描述；“叶子存行"是内容描述，二者并不矛盾。
为什么二级索引不直接存行地址
MyISAM 走的就是这条路（“堆组织表”）。InnoDB 选主键而非地址，是为了 避免页分裂时所有索引都得改主键记录地址。
05 | 深入浅出索引（下）
覆盖索引
查询所需列全部包含在索引中 → 无需回表。
SELECT * FROM T WHERE k BETWEEN 3 AND 5;
若 k 列值为 1, 2, 3, 5, 6, 7：引擎实际读 3 条（3 / 5 / 6 终止），Server 拿到 2 条 —— 这就是 explain 的 rows 与引擎扫描数不等的来源。
最左前缀原则
B+ 树索引可用最左前缀定位记录。
索引顺序的第一判据：如果调整顺序能少维护一个索引，就采纳。
常查
(name, age)与(age)时，建(name, age) + (age)，不是(age, name) + (age)—— 前者总空间小。
索引下推（Index Condition Pushdown, 5.6+）
联合索引 (name, age)：
SELECT * FROM tuser WHERE name LIKE '张%' AND age = 10 AND ismale = 1;
| 版本 | 行为 |
|---|---|
| < 5.6 | 仅用 name 定位前缀，符合 张% 的全部回表后再筛 age |
| ≥ 5.6 ICP | 索引内部就判 age = 10，不满足直接跳过，减少回表次数 |
联合索引按列顺序排序：(name, age) 先按 name，再按 age。
第一性原则：在满足查询需求前提下，尽量少访问资源。
06 | 全局锁和表锁：给表加个字段怎么这么难？
全局锁
FLUSH TABLES WITH READ LOCK; -- FTWRL：整库只读
-- ... 备份 ...
UNLOCK TABLES;
代价：
- 主库执行 → 业务停摆。
- 从库执行 → 不消化主库 binlog，主从延迟。
更优解：mysqldump --single-transaction 在 RR 下开事务，靠 MVCC 拿一致性视图，过程中可正常写。仍需 FTWRL 的场景：引擎不支持 RR（如 MyISAM）。
FTWRL vs set global readonly=true
| 维度 | FTWRL | readonly |
|---|---|---|
| 副作用 | 仅设全局读锁 | readonly 常被业务用作主备判断，影响面大 |
| 异常恢复 | 客户端断开 → 自动释放 | 客户端断开 → 长期 readonly，库不可写 |
表级锁两类
LOCK TABLES
LOCK TABLES T1 READ, T2 WRITE;
-- 当前线程：只能读 T1、读写 T2，其他表禁访
-- 其他线程：写 T1、读写 T2 都被阻塞
SHOW OPEN TABLES WHERE In_use > 0;
MDL（Metadata Lock）—— 隐式
| 操作 | 加锁 | 互斥关系 |
|---|---|---|
| 增删改查 | MDL 读锁 | 读读不互斥 |
| 结构变更 DDL | MDL 写锁 | 读写、写写互斥（排他） |
事务中的 MDL 锁在 语句开始时申请、事务提交时才释放。
典型雪崩：给小表加字段卡死全库
-- session1 长事务持有 MDL 读锁未释放
BEGIN;
SELECT * FROM T; -- MDL 读锁
-- session3 发起 DDL
ALTER TABLE T ADD COLUMN c2 VARCHAR(255);
-- ← 拿不到 MDL 写锁，阻塞，并排到 MDL 等待队列首位
-- session4 后续任何对 T 的读写
SELECT * FROM T; -- ← 排在 session3 之后，全部阻塞
写锁请求会阻塞 之后所有 对该表的读写请求 —— 一个长事务足以拖垮全库。
安全加字段
- 先解长事务：
information_schema.innodb_trx找超时事务。 - DDL 加超时退避：
ALTER TABLE tbl_name NOWAIT ADD COLUMN ...; -- 拿不到锁立即放弃
ALTER TABLE tbl_name WAIT 30 ADD COLUMN ...; -- 最多等 30s
实测：MySQL 5.7.30 不支持
NOWAIT / WAIT；腾讯云 MySQL 5.7.18-txsql 支持。参见 腾讯云 NOWAIT 语法。
Online DDL 的 LOCK 选项
ALTER TABLE T ADD COLUMN c2 VARCHAR(255) NULL, LOCK=EXCLUSIVE;
| LOCK 选项 | 并发查询 | 并发 DML |
|---|---|---|
| NONE | ✓ | ✓ |
| SHARED | ✓ | ✗ |
| DEFAULT | 尽量都允许 | 尽量都允许 |
| EXCLUSIVE | ✗ | ✗ |
07 | 行锁功过：怎么减少行锁对性能的影响？
两阶段锁协议
InnoDB 事务的行锁在需要时才加，直到事务结束才释放。
由此导出一条工程规则：把最易冲突的锁放最后。
例：转账 + 记日志（按 ③①② 顺序执行）
| 顺序 | 步骤 | 冲突度 |
|---|---|---|
| ③ 先 | 记交易日志 INSERT | 低 |
| ① 中 | 扣顾客 A 余额 | 中 |
| ② 后 | 加影院 B 余额 | 高（很多人同时买） |
把"加影院余额"放最后，热点行锁持有时间从整段事务缩到末尾一小段。
死锁与检测
SELECT @@innodb_lock_wait_timeout; -- 默认 50s（图中环境设为 7200 = 2h）
SELECT @@innodb_deadlock_detect; -- 1 开启主动检测；触发回滚 1213
主动检测的代价：每个新被阻塞的线程要做 O(n) 环路检查。1000 并发更新同行 → 百万次量级的检测，CPU 烧穿。
热点行的解决：拆分多行，把一个余额账户拆成 N 个子账户分摊并发，应用层聚合。
08 | 事务到底是隔离的还是不隔离的？
BEGIN 不等于事务起点
BEGIN; -- 仅声明，未启动
START TRANSACTION WITH CONSISTENT SNAPSHOT; -- 立即拍快照，事务真正启动
BEGIN 之后第一条对 InnoDB 表的操作才真正开启事务。
两个"视图"别混淆
| 名称 | 含义 |
|---|---|
| view | CREATE VIEW 定义的虚拟表，调用时执行查询语句 |
| consistent read view | InnoDB MVCC 用的一致性读视图，支撑 RC / RR 隔离级别 |
RR 隔离级别下，事务启动时拍 整库 快照。
transaction id
InnoDB 每个事务有唯一 transaction id，事务开启时向事务系统申请，严格递增。MVCC 用它判断数据行版本对当前事务是否可见。
– EOF –