mysql 学习-AI整理版
基于 mysql 学习 重新整理。
目标不是"把原文再说一遍"，而是把内容改成更适合学习、复习、回看的笔记结构。
这份笔记怎么读
原文是一篇面试问答式的 MySQL 核心知识点合集，覆盖存储引擎、事务、日志、优化与主从复制。建议按下面顺序建立体系，再逐个问答自测：
- 存储引擎（MyISAM vs InnoDB）
- 事务 ACID 与隔离级别
- ACID 的底层保证（undo log / redo log / MVCC）
- 慢查询优化思路
- 主从同步原理
- 索引类型与利弊
学习路线图
| 阶段 | 重点 | 说明 |
|---|---|---|
| 基础 | MyISAM vs InnoDB | 从事务、锁、索引三个维度对比记忆 |
| 核心 | ACID 与隔离级别 | 四个特性 + 四个级别 + 三种读异常 |
| 深入 | 日志机制 | undo log、redo log、binlog 各自保证什么 |
| 实战 | 慢查询优化 | "找原因 → 改语句 → 再分表"三步法 |
| 架构 | 主从同步 | 三个线程的协作流程 |
一、MyISAM 和 InnoDB 的区别
| 维度 | MyISAM | InnoDB |
|---|---|---|
| 事务 | 不支持，但每次查询是原子的 | 支持 ACID 事务与四种隔离级别 |
| 锁粒度 | 表级锁，每次操作锁整张表 | 行级锁 + 外键约束，支持写并发 |
| 总行数 | 存储，COUNT(*) 快 | 不存储 |
| 文件组成 | 三个文件：索引文件、表结构文件、数据文件 | 共享表空间（一个文件空间）或独立表空间（多个文件，受操作系统文件大小限制） |
| 索引结构 | 非聚集索引：索引数据域存指向数据文件的指针；辅索引与主索引基本一致，但不保证唯一 | 聚集索引：主键索引的数据域存数据本身；辅索引数据域存主键值 |
InnoDB 查数据的回表过程：辅索引的数据域存的是主键值，所以从辅索引查数据要先找到主键值，再回主键（聚集）索引取数据。
为什么最好用自增主键：防止插入数据时为维护 B+ 树结构而做大范围的文件调整。
二、事务：ACID 与隔离级别
1. 四个基本特性
| 特性 | 含义 |
|---|---|
| 原子性（Atomicity） | 一个事务中的操作要么全部成功，要么全部失败 |
| 一致性（Consistency） | 数据库总是从一个一致状态转换到另一个一致状态。例如 A 转账给 B 100 元但 A 只有 90 元，事务若执行成功就会破坏约束，因此不能成功 |
| 隔离性（Isolation） | 一个事务的修改在最终提交前，对其他事务是不可见的 |
| 持久性（Durability） | 一旦事务提交，修改就永久保存在数据库中 |
2. 四个隔离级别
| 级别 | 名称 | 说明 | 存在问题 |
|---|---|---|---|
| read uncommitted | 读未提交 | 可能读到其他事务未提交的数据 | 脏读 |
| read committed | 读已提交 | 只读取已提交的事务 | 不可重复读 |
| repeatable read | 可重复读（MySQL 默认） | 每次读取结果都一样 | 可能产生幻读 |
| serializable | 串行 | 给每一行读取的数据加锁 | 大量超时和锁竞争，一般不使用 |
3. 三种读异常
- 脏读（Dirty Read）：事务 A 更新了一份数据还未提交，事务 B 此时读取了同一份数据，A 随后回滚，B 读到的就是不正确的数据。
- 不可重复读（Non-repeatable Read）：一个事务内两次查询同一行数据结果不一致，期间被其他事务更新了该数据。
- 幻读（Phantom Read）：一个事务内两次查询的数据行数不一致，期间被其他事务插入了新行。
三、ACID 靠什么保证
| 特性 | 保证机制 |
|---|---|
| A 原子性 | undo log：记录回滚所需日志，事务回滚时撤销已执行成功的 SQL |
| C 一致性 | 由其他三大特性 + 程序代码共同保证业务上的一致性 |
| I 隔离性 | MVCC（多版本并发控制） |
| D 持久性 | 内存 + redo log：修改数据时同时写内存和 redo log，宕机后从 redo log 恢复 |
redo log 与 binlog 的两阶段提交：
- InnoDB redo log 写盘，事务进入 prepare 状态；
- prepare 成功后 binlog 写盘并持久化；
- 持久化成功后，InnoDB 事务进入 commit 状态（在 redo log 里写一条 commit 记录）。
redo log 的刷盘会在系统空闲时进行。
四、慢查询怎么优化
先定位慢的原因，再对症下药。三个常见方向：查询条件没命中索引？加载了不需要的数据列（如 SELECT *）？还是数据量本身太大？
- 分析语句：看是否加载了额外的数据（查了多余的行或结果中用不到的列），重写语句；
- 分析执行计划：查看索引使用情况，修改语句或索引使其尽可能命中索引；
- 考虑分表：语句优化空间用尽且数据量太大时，做横向或纵向分表。
五、主从同步原理
主从复制共三个线程：Master 一条（binlog dump thread），Slave 两条（I/O thread、SQL thread）。
流程：
- 主库把所有修改数据库结构或内容的操作记录到 binlog（主从复制的基础）；
- 主库 log dump 线程在 binlog 变动时读取内容并发送给从节点；
- 从库 I/O 线程接收 binlog 内容，写入本地 relay log；
- 从库 SQL 线程读取 relay log 重放更新，最终保证主从一致。
注：主从使用 binlog 文件 + position 偏移量定位同步位置；从库保存已接收的偏移量，宕机重启后自动从 position 处继续同步。
同步模式：默认异步复制——主库发完日志不关心从库是否处理完，主库挂掉时从库可能丢日志。由此衍生两种模式：
| 模式 | 机制 | 代价 |
|---|---|---|
| 全同步复制 | 主库强制同步日志到所有从库，全部执行完才返回客户端 | 性能受严重影响 |
| 半同步复制 | 至少一个从库写入日志并返回 ACK 确认，主库即认为写完成 | 折中方案 |
六、索引类型及其对性能的影响
| 类型 | 特点 |
|---|---|
| 普通索引 | 允许索引列包含重复值 |
| 唯一索引 | 保证数据记录唯一性 |
| 主键索引 | 特殊的唯一索引，一表只能有一个，用 PRIMARY KEY 创建 |
| 联合索引 | 覆盖多个列，如 INDEX(columnA, columnB) |
| 全文索引 | 建立倒排索引提升检索效率，解决"字段是否包含"类问题；ALTER TABLE table_name ADD FULLTEXT(column) |
收益：极大提高查询速度；查询过程中可利用优化器提升系统性能。
代价：
- 降低插入、删除、更新表的速度——写操作还要同时维护索引文件；
- 索引占用物理空间，聚集索引需要的空间更大；
- 非聚集索引很多时，一旦聚集索引改变，所有非聚集索引都会跟着变。
一页总结
- 存储引擎：InnoDB 支持事务/行锁/聚集索引，MyISAM 反之；
- 隔离级别：读未提交 → 读已提交 → 可重复读（默认）→ 串行，分别对应脏读、不可重复读、幻读、锁竞争；
- ACID 保证：undo log 保原子，MVCC 保隔离，redo log 保持久，一致性靠三者 + 业务代码；
- 慢查询：看语句 → 看执行计划 → 分表；
- 主从：binlog → dump 线程 → I/O 线程 → relay log → SQL 线程；
- 索引：加速读、拖慢写、占空间。
接下来可以补充：原文预留了"执行计划查看（EXPLAIN）"小节未展开，复习时建议补上。