【WebWiki】08 · MySQL

03 数据库（MySQL）

WebWiki · 数据库篇

1. 逻辑架构与存储结构

连接层(连接池/鉴权) → 服务层(解析器/优化器/执行器) → 引擎层(InnoDB)
InnoDB：表空间 → 段 → 区(1MB) → 页(16KB，最小 IO 单位) → 行

总体架构

1.1 四层逻辑架构

连接层：连接池、鉴权、线程管理。每个连接对应一个线程，连接数过多会耗尽内存。
服务层（SQL Layer）：
- 解析器（Parser）：词法/语法分析，生成解析树。
- 预处理器（Preprocessor）：检查表/列是否存在、权限。
- 优化器（Optimizer）：选执行计划（走哪个索引、join 顺序），基于成本（cost）。
- 执行器（Executor）：调存储引擎 API 真正读写数据。
引擎层：插件式存储引擎，InnoDB（默认，事务型）/ MyISAM（只读/历史表）/ Memory。
物理文件层：数据文件、redo/undo、binlog、索引文件。

1.2 InnoDB 物理存储结构（深度）

表空间（Tablespace）：ibdata1（系统表空间）+ 各表独立 .ibd（开启 innodb_file_per_table）。
段（Segment）：数据段（B+树叶子）、索引段（B+树非叶子）、回滚段（undo）。
区（Extent）：固定 1MB，由 64 个连续的 16KB 页组成。连续分配减少随机 IO。
页（Page）：最小 IO 单位，16KB。类型有数据页、undo 页、系统页等。一次读写至少一页。
行（Row）：行格式 Compact/Dynamic（默认）。Dynamic 下超长字段（如 TEXT）只在页里存 20 字节指针，真正数据放溢出页，避免单页被大字段撑爆。

为什么页是 16KB？ 太小则 B+树层级变高、IO 次数多；太大则一次读浪费带宽。16KB 是经验平衡点。一行约 1KB 时，一个数据页能放约 16 行；3 层 B+树（根→枝→叶）叶子页约 16^2 × 16 × 1000 ≈ 千万级行（估算），所以「三层 B+树撑两千万行」是有依据的。

2. 索引

2.1 B+ 树与聚簇索引

InnoDB 用 B+ 树：非叶子节点只存键，叶子节点存数据并用双向链表串联，等值与范围查询都高效，树高 3 层可撑约 2000 万行；
聚簇索引：主键索引的叶子存整行数据；二级索引叶子存主键值，查非索引列需回表。

B+ 树为什么比 B 树 / 红黑树 / Hash 好？

结构	问题
二叉搜索树 / 红黑树	树太高（千万行要 ~24 层），每层一次磁盘 IO，太慢
Hash 索引	等值快，但不支持范围、不支持排序、不支持前缀
B 树（非 B+）	非叶子也存数据，单页存的键少、树更高；范围查询要中序遍历，不连续
B+ 树	非叶只存键 → 单页键多、树矮；叶子双向链表 → 范围/排序连续扫；所有查询走叶子同层，稳定

聚簇 vs 二级索引（深度）

聚簇索引（主键）：数据即索引，叶子是完整行。一张表只有一个聚簇索引。没显式主键时，InnoDB 选第一个唯一非空索引；都没有则隐式生成 6 字节 row_id。
二级索引：叶子存「索引列值 + 主键值」。通过二级索引查到主键后，再拿主键回聚簇索引取整行——这叫回表。

-- 主键 id 聚簇索引；name 二级索引
SELECT * FROM user WHERE name = 'tom';
-- 过程：name 索引找到 name='tom' 对应的 id=10 → 拿 id=10 回聚簇索引取整行
-- 若只查 id,name，则二级索引叶子已含，无需回表（覆盖索引）

2.2 关键概念

概念	说明
覆盖索引	查询列全在索引里，免回表：`index(name)` 支持 `SELECT id,name ... WHERE name=?`
最左前缀	联合索引 (a,b,c) 支持 a / a,b / a,b,c；b 或 c 单独不行；范围列之后的列失效
索引下推(ICP)	WHERE 中索引列条件在引擎层先过滤，减少回表
前缀索引	长字符串取前 N 位建索引，区分度用 `COUNT(DISTINCT LEFT(col,n))/COUNT(*)` 评估

索引下推 ICP（深度）

没有 ICP 时：WHERE name LIKE '张%' AND age=20，存储引擎用 name 找到候选行后全部回表交给 Server 层再判断 age；有 ICP 后，存储引擎在遍历索引时就顺手用 age 过滤，少回表。

-- 联合索引 (name, age)，ICP 让 age 在引擎层先用上
EXPLAIN SELECT * FROM user WHERE name LIKE '张%' AND age=20;
-- Extra 出现 "Using index condition" 即 ICP 生效

前缀索引与区分度

-- 评估前 10 位区分度，越接近 1 越好
SELECT COUNT(DISTINCT LEFT(address,10))/COUNT(*) FROM user;
-- 区分度足够则建前缀索引，省空间
ALTER TABLE user ADD INDEX idx_addr (address(10));

2.3 建索引原则

区分度高的列优先；WHERE / ORDER BY / JOIN ON 高频列建索引；
单表索引建议 ≤ 5 个，避免过度索引拖慢写入；
不在索引列上做函数/运算/隐式转换（WHERE phone=13800001111 会把 varchar 转数字导致索引失效）；
LIKE '%xx' 左模糊失效，LIKE 'xx%' 可以走索引；
尽量用自增主键（避免随机主键导致的页分裂）。

失效场景速查（深度）

写法	是否走索引	原因
`WHERE name = 'tom'`	✅	等值
`WHERE name LIKE 'tom%'`	✅	右模糊可前缀匹配
`WHERE name LIKE '%tom'`	❌	左模糊无法定位起点
`WHERE SUBSTR(name,1,3)='tom'`	❌	对列用函数
`WHERE phone = 13800001111`（phone 是 varchar）	❌	隐式转换（字符串转数字）
`WHERE age+1 = 20`	❌	对列运算
`WHERE a=1 OR b=2`（b 无索引）	❌（可能）	OR 一侧无索引导致全表
`WHERE a!=1` / `a IS NULL`	⚠️	优化器可能选全表
联合索引 `WHERE b=1 AND c=2`（跳过 a）	❌	违反最左前缀

3. 事务与 MVCC

ACID：原子性(undo log)、持久性(redo log)、隔离性(锁+MVCC)、一致性(结果)。

3.1 隔离级别与现象

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	有	有	有
READ COMMITTED (RC)	无	有	有
REPEATABLE READ (默认)	无	无	基本避免(MVCC+间隙锁)
SERIALIZABLE	无	无	无

脏读：读到别的事务未提交的数据（可能被回滚，读到幽灵）。
不可重复读：同一事务内两次读同一行，值被别的事务改了（强调「改值」）。
幻读：同一事务内两次 SELECT ... WHERE，行数变了（别的事务插入/删除，强调「增删行」）。

注意：SQL 标准里 RR 仍允许幻读，但 InnoDB 在 RR 下用 MVCC + 间隙锁基本消除了幻读，这是 InnoDB 对标准的增强。

3.2 MVCC 实现细节（深度）

MVCC（多版本并发控制）让「读不加锁、读写不阻塞」：每行除了业务列，还有隐藏列：

DB_TRX_ID（6 字节）：最近一次修改该行的事务 ID。
DB_ROLL_PTR（7 字节）：回滚指针，指向 undo log 里该行的上一个版本。
DB_ROW_ID：隐式行 ID（无主键时）。

undo log 版本链：每次更新，旧版本写入 undo log，新行 roll_ptr 指向它，形成链表。事务读时沿链找「对自己可见」的版本。

ReadView（读视图）：决定哪些版本可见。包含：当前活跃（未提交）事务 ID 列表 m_ids、最小活跃 min_trx_id、预分配下一个 max_trx_id、创建者 creator_trx_id。

可见性判断（简化）：

若 trx_id == creator_trx_id → 自己改的，可见；
若 trx_id < min_trx_id → 已提交，可见；
若 trx_id >= max_trx_id → 未来事务，不可见；
若 min_trx_id ≤ trx_id < max_trx_id 但不在 m_ids → 已提交，可见；在 m_ids → 未提交，不可见，沿 undo 链找更早版本。

RC vs RR 的关键差异：

RC：每次 SELECT 都新建一个 ReadView → 别的事务一提交就能看到（所以不可重复读）。
RR：第一次 SELECT 建 ReadView 后整个事务复用 → 后续读都按同一个快照，自然可重复读。

-- 验证 RR 可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM account WHERE id=1; -- 读到 100
-- 另一会话 UPDATE account SET bal=200 WHERE id=1; COMMIT; （此时已提交）
SELECT * FROM account WHERE id=1; -- RR 下仍读到 100（快照不变）
COMMIT;

4. 锁

4.1 锁的分类

全局锁：FLUSH TABLES WITH READ LOCK，用于逻辑备份；
表锁 / 元数据锁(MDL)：DDL 会加 MDL 写锁，长事务期间 DDL 会阻塞后续所有查询；
行锁：
- Record Lock：锁单条索引记录；
- Gap Lock：锁索引区间，防插入（RR 下防幻读）；
- Next-Key Lock：Record + Gap；
死锁：SHOW ENGINE INNODB STATUS 查看 LATEST DETECTED DEADLOCK；避免方案：按相同顺序访问资源、小事务、合理索引（避免锁升级为表级扫描）。

4.2 行锁是在索引上加的（深度）

关键认知：InnoDB 行锁是加在索引记录上的，不是加在数据行上。如果 WHERE 没走索引（或索引失效），会锁住全表所有行（等价于表锁），这是生产大事故来源。

-- id 是主键（有索引）→ 只锁 id=1 这一行
UPDATE account SET bal=bal-10 WHERE id=1;

-- phone 没建索引 → 全表扫描，锁住所有行！高并发直接卡死
UPDATE account SET bal=bal-10 WHERE phone='13800001111'; -- 危险！

4.3 间隙锁与临键锁（深度）

在 RR 隔离级别下，为防止幻读，InnoDB 用 Next-Key Lock = 记录锁 + 间隙锁：

Record Lock：锁住已存在的某条记录。
Gap Lock：锁住记录之间的「间隙」，禁止往里插新记录。
Next-Key Lock：锁住「本记录 + 前面间隙」，左开右闭 (prev, cur]。

-- 假设 id 有 10, 20, 30，查询范围是 10~30
SELECT * FROM t WHERE id BETWEEN 10 AND 30 FOR UPDATE;
-- 加锁区间：(负无穷,10] (10,20] (20,30] (30, 正无穷) 附近间隙
-- 别的会话 INSERT id=25 会被阻塞 → 防止幻读

说明：RC 级别下没有间隙锁，所以 RC 会有幻读；RR 才有。这也是为什么默认 RR 更安全。

4.4 死锁与排查

-- 会话A
BEGIN; UPDATE account SET bal=bal-10 WHERE id=1; -- 锁 id=1
-- 会话B
BEGIN; UPDATE account SET bal=bal-10 WHERE id=2; -- 锁 id=2
-- 会话A 又想锁 id=2（被B占）→ 互等
UPDATE account SET bal=bal-10 WHERE id=2;
-- 会话B 又想锁 id=1（被A占）→ 死锁，MySQL 检测后回滚一方

-- 排查
SHOW ENGINE INNODB STATUS\G  -- 看 LATEST DETECTED DEADLOCK

避免方案：事务里按固定顺序访问多行资源；保持事务小、快；必要的索引让锁精准；用 SELECT ... FOR UPDATE 时范围尽量小。

5. 日志体系

日志	层	作用
redo log (物理)	InnoDB	崩溃恢复，WAL：先写日志再刷数据页
undo log (逻辑)	InnoDB	回滚 + MVCC 版本链
binlog (逻辑)	Server 层	主从复制、数据恢复，row 模式为主
slow log	Server 层	慢查询定位

两阶段提交：redo log prepare → 写 binlog → redo log commit，保证两份日志一致。

5.1 redo log（深度）

为什么需要：每次更新都刷磁盘数据页太慢（随机 IO）。InnoDB 用 WAL（Write-Ahead Logging）：先写 redo log（顺序 IO、快），再异步把脏页刷盘。
结构：固定大小环形缓冲区（ib_logfile0/1），写满循环覆盖（已刷盘的才可覆盖）。
crash-safe：宕机重启后，用 redo log 把「已提交但未刷盘」的修改重放，保证持久性。

5.2 undo log（深度）

记录「修改前的值」的逻辑日志。用于：① ROLLBACK 回滚未提交事务；② MVCC 提供历史版本（见 3.2）。
INSERT 的 undo 在事务提交后即可清；UPDATE/DELETE 的 undo 要留着供 MVCC 读，由 purge 线程在无人引用后清理。

5.3 binlog（深度）

Server 层日志，与引擎无关，记录「对数据做了什么改动」，用于主从复制和时间点恢复。
三种格式：
- STATEMENT：记 SQL 文本，省空间但有函数/不确定性风险（UUID()/NOW() 主从不一致）。
- ROW（默认推荐）：记每行前后镜像，安全、利于恢复，日志稍大。
- MIXED：混合。
sync_binlog=1：每次事务提交都刷盘，保证不丢（与 redo 配合两阶段提交）。

5.4 两阶段提交（深度，保证 redo 与 binlog 一致）

事务提交：
  1) 写 redo log，标记为 prepare
  2) 写 binlog
  3) 写 redo log，标记为 commit

为什么必须两阶段？假设只先写 redo 再写 binlog、中间崩溃：

若 binlog 没写 → 重启用 redo 恢复出数据，但 binlog 缺这条 → 主从不一致。
两阶段提交保证：崩溃后重启，若 redo 是 prepare 且 binlog 完整 → 提交；若 binlog 不完整 → 回滚。从而 redo 与 binlog 永远一致。

6. SQL 优化与 EXPLAIN

EXPLAIN SELECT o.* FROM orders o
JOIN user u ON o.user_id = u.id
WHERE o.status = 1 AND o.created_at > '2026-01-01'
ORDER BY o.created_at DESC LIMIT 20;

type 好坏	const > eq_ref > ref > range > index > ALL
key / key_len	实际用到的索引及长度（判断联合索引用了几列）
rows	预估扫描行数，越小越好
Extra	Using index(覆盖，好) / Using filesort(排序未走索引) / Using temporary(临时表，要优化)

6.1 EXPLAIN 字段详解（深度）

字段	含义	关注点
`id`	执行顺序，大先执行，相同则从上到下	看 join 谁驱动谁
`select_type`	查询类型	SIMPLE / PRIMARY / SUBQUERY
`type`（访问类型）	怎么找数据	核心！ const > eq_ref > ref > range > index > ALL
`possible_keys`	可能用的索引	有但没用上要查原因
`key`	实际用的索引	NULL = 全表
`key_len`	索引使用字节数	判断联合索引用了几列
`ref`	索引比较的列/常量	—
`rows`	预估扫描行	越小越好
`Extra`	额外信息	Using filesort/temporary 是红灯

type 等级含义：
- const/system：主键/唯一索引等值，最多一行（最快）。
- eq_ref：join 时主键/唯一索引关联，一行对一行。
- ref：普通索引等值。
- range：索引范围（BETWEEN/IN/>）。
- index：全索引扫描（比 ALL 好，数据在索引里）。
- ALL：全表扫描（最差，必须优化）。

6.2 常见优化手段（深度）

只查需要的列：SELECT * 无法走覆盖索引，且多传字段浪费 IO/带宽。

深分页优化：LIMIT 1000000,20 要扫 100 万行再丢。改用游标/延迟关联：

-- 反例：越翻越慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 正解：游标分页，WHERE id > 上一页最大id
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
-- 或延迟关联：先查索引拿 id，再回表
SELECT * FROM orders a
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000,20) b ON a.id=b.id;

大事务拆小：长事务占 undo、锁资源、拖主从延迟。
join 小表驱动大表：MySQL 优化器一般会自动选，但 WHERE 过滤后数据集小的作驱动表更优。
or 改 union：WHERE a=1 OR b=2 常导致索引失效，拆成 UNION（两侧各有索引时）。
避免 SELECT COUNT(*) 全表：估算用 EXPLAIN 的 rows，或维护计数表。

7. 主从与高可用

主库写 binlog → 从库 IO 线程拉取写 relay log → SQL 线程重放

复制方式：异步（默认，可能丢）/ 半同步 / 组复制 MGR；
读写分离：应用侧路由（ShardingSphere-JDBC / 动态数据源），注意主从延迟对"写后立读"的影响；
高可用：MHA / Orchestrator / 云上 RDS 自带主备切换。

7.1 复制原理（深度）

主库开启 binlog，事务提交时写 binlog；
从库 IO Thread 连主库 dump 线程，拉取 binlog 写入本地 relay log（中继日志）；
从库 SQL Thread 读 relay log 重放，更新从库数据；
新版 MySQL 用 多线程复制（MTS，按库/按事务并行） 缓解延迟。

7.2 三种复制模式

模式	一致性	性能	说明
异步（默认）	可能丢（主提交即从库未知）	好	主挂可能丢最后事务
半同步	至少一个从接收即返回	中	折中，防丢数据
组复制 MGR	多数派一致（Paxos）	中	原生高可用，多主

7.3 主从延迟与读写分离坑

延迟来源：从库重放慢于主库写入，大事务、从库压力大都会放大。
“写后立读”陷阱：主库刚插入，应用立即从从库读 → 读到旧数据（延迟未同步）。解决：写后强制走主库、或读己写（session 级路由主库）。
读写分离路由：ShardingSphere-JDBC / 动态数据源按注解/方法把写走主、读走从；但报表类长查询可全走从。

8. 分库分表（ShardingSphere）

垂直拆分：按业务域把大字段/低频表拆出去；水平拆分：按分片键把行打散。

# ShardingSphere-JDBC 示例：订单表按 user_id 取模分 4 库
rules:
  sharding:
    tables:
      t_order:
        actual-data-nodes: ds_${0..3}.t_order_${0..7}
        database-strategy:
          standard:
            sharding-column: user_id
            sharding-algorithm-name: order-mod
    sharding-algorithms:
      order-mod:
        type: MOD
        props: { sharding-count: 32 }

分片键选择：最高频查询条件（订单表选 user_id，交易保证同用户路由同库）；
全局唯一 ID：雪花算法（Snowflake）；
引入的问题：跨库 join、分布式事务、跨片分页——能不分就不分，先上读写分离 + 归档撑到单表 500 万~2000 万再评估。

8.1 分片算法与坑（深度）

取模（MOD）：简单均匀，但扩容（库数变了）要迁移大量数据，停机成本高。生产更常用一致性哈希或范围分片（按时间）以便扩容。
跨片查询：WHERE user_id=? 能路由单库；WHERE status=?（无分片键）会广播到所有库再聚合，慢。
跨片分页：各库取前 N 条汇总再全局排序，深度分页极慢。解决：业务上限制深度、用 ES 做检索、或按分片键收敛。
分布式事务：跨库写入用 Seata/消息最终一致（见微服务篇）。
全局 ID：自增主键在分片下会冲突，用 Snowflake（64 位：时间戳+机器+序列）或号段模式。

8.2 何时才分（决策树）

单表 < 500 万行、QPS 不高 → 别分，加索引、优化 SQL。
读写都高 → 先读写分离（主写从读）。
单表过大/历史数据多 → 冷热归档（把老数据迁走）。
仍瓶颈 → 才考虑分库分表，且优先水平分表（同库多表）再分库。

9. 实战军规

varchar 长度按需、not null + 默认值、金额用 DECIMAL 或分为单位的 BIGINT；
每表必备：id BIGINT auto_increment、created_at、updated_at；
禁止 SELECT *；禁止不带 WHERE 的 UPDATE/DELETE（开启 sql_safe_updates）；
大表 DDL 用 gh-ost / pt-osc 在线变更；
字段超长文本拆出扩展表，图片/文件存 OSS 只存 URL。

9.1 字段类型选型（深度）

场景	推荐	不推荐	原因
金额	`DECIMAL(18,2)` 或 `BIGINT` 分	`FLOAT/DOUBLE`	浮点有精度误差，钱会算错
主键	`BIGINT` 自增 / Snowflake	`UUID`(varchar)	UUID 随机导致页分裂、索引大
状态/性别	`TINYINT`	`VARCHAR`	省空间、快
时间	`DATETIME` / `TIMESTAMP`	字符串	可比较、可索引
长文本	拆扩展表 / `TEXT`	主表大 `VARCHAR`	撑爆数据页

9.2 安全与运维军规

-- 开启安全更新，防误删全表
SET sql_safe_updates = 1;

-- 大表改结构用在线工具（避免锁表）
-- gh-ost / pt-online-schema-change：建影子表、增量同步、原子改名

-- 慢查询开关
SET long_query_time = 1;          -- 超过1秒记慢日志
-- 或 my.cnf: slow_query_log=1, slow_query_log_file=..., long_query_time=1

-- 数据恢复：binlog 闪回（row 模式）
mysqlbinlog --start-datetime="2026-09-19 10:00:00" binlog.000123 | mysql -uroot -p

9.3 排查问题 checklist

慢查询 → EXPLAIN 看 type/key/rows/Extra；
锁等待 → SHOW PROCESSLIST 看 State=‘Sending data’/‘Locked’；
死锁 → SHOW ENGINE INNODB STATUS；
主从延迟 → SHOW SLAVE STATUS 看 Seconds_Behind_Master；
磁盘满/IO 高 → 看 redo/undo/binlog 体积与 iostat。

小结

MySQL 性能的根在存储结构（B+树/页/聚簇索引）与索引设计（最左前缀/覆盖/ICP）；正确性的根在事务隔离 + MVCC + 锁（行/间隙/临键）；可靠性的根在日志体系（redo/undo/binlog + 两阶段提交）。优化时先 EXPLAIN，遵循「最左前缀、避免索引失效、小事务、深分页用游标」；扩容时记住「能不分就不分，先读写分离再归档」。把这一篇的表、锁、日志三条主线串起来，遇到慢 SQL 和线上数据问题就能系统性定位，而不是盲目加索引。

posted @ 2026-09-19 05:40 Stellan7 阅读(16) 评论(0) 收藏举报

刷新页面返回顶部

Loading

Stellan