跳至主要內容

《MySQL 实战 45 讲》笔记

钝悟2022/7/20大约 13 分钟笔记数据库数据库MySQL

《MySQL 实战 45 讲》笔记

极客时间教程 - MySQL 实战 45 讲 学习笔记

01 基础架构：一条 SQL 查询语句是如何执行的？

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、解析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

MySQL 整个查询执行过程，总的来说分为 6 个步骤：

  1. 连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。
  2. 查询缓存：命中缓存，则直接返回结果。弊大于利，因为失效非常频繁——任何更新都会清空查询缓存。
  3. 分析器
  • 词法分析：解析 SQL 关键字
  • 语法分析：生成一颗对应的语法解析树
  1. 优化器
  • 根据语法树生成多种执行计划
  • 索引选择：根据策略选择最优方式
  1. 执行器
  • 校验读写权限
  • 根据执行计划，调用存储引擎的 API 来执行查询
  1. 存储引擎：存储数据，提供读写接口

02 日志系统：一条 SQL 更新语句是如何执行的？

更新流程和查询的流程大致相同，不同之处在于：更新流程还涉及两个重要的日志模块：

  • redo log（重做日志）
  • binlog（归档日志）

redo log

采用 WAL（Write-Ahead Logging） 技术：先写日志，再写磁盘。更新时先写 redo log 并更新内存，后台再刷到磁盘。

redo log 是固定大小的循环写（如 4 个文件，每个 1GB，循环使用）。

write pos 是当前记录位置，checkpoint 是当前要擦除的位置。两者之间是可用空间。write pos 追上 checkpoint 表示日志满了，需要先刷脏页。

redo log 保证了 crash-safe 能力（异常重启不丢已提交数据）。

binlog

redo log 是 InnoDB 引擎特有的，Server 层有自己的 binlog（归档日志）。

对比项redo logbinlog
所属层InnoDB 引擎Server 层（所有引擎可用）
日志类型物理日志（数据页修改）逻辑日志（原始 SQL）
写入方式循环写，空间有限追加写，切换到新文件

update 语句的执行流程：

  1. 执行器找引擎取 ID=2 这一行（主键索引搜索，可能从磁盘读入内存）
  2. 执行器将新值传给引擎写入内存
  3. 引擎更新内存，并将操作记录到 redo log（prepare 状态）
  4. 执行器生成 binlog 并写入磁盘
  5. 执行器调用引擎提交事务，redo log 改为 commit 状态

两阶段提交

由于 redo log 和 binlog 是两个独立的日志，必须用两阶段提交保证一致性：

  • 先写 redo log 后写 binlog：redo log 写完但 binlog 未完成时崩溃 → 数据恢复但 binlog 缺失 → 主备不一致
  • 先写 binlog 后写 redo log：binlog 写完但 redo log 未写时崩溃 → 数据未恢复但 binlog 有多余记录 → 主备不一致

结论：不用两阶段提交，数据库状态和日志恢复出的状态可能不一致。

03 事务隔离：为什么你改了我还看不见？

隔离级别

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。

在 MySQL 中，事务支持是在引擎层实现的。并不是所有的引擎都支持事务。比如 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

事务特性 ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）。

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。隔离级别越高，效率越低。

  • Oracle 的默认隔离级别是“读提交”
  • MySQL 的默认隔离级别是“可重复读”

事务隔离的实现

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

04 深入浅出索引（上）

索引的出现是为了提高数据查询的效率。对于数据库的表而言，索引就像是书的目录。

索引的常见模型

哈希索引

适用于只有等值查询的场景。

限制：

  • 无法用于排序（数据不按索引值顺序存储）
  • 不支持部分索引匹配
  • 不能避免读取行（不存储字段值）
  • 只支持等值比较（=、IN()、<=>），不支持范围查询
  • 哈希冲突多时维护代价高

MySQL 中只有 Memory 引擎显式支持哈希索引。

有序数组索引

等值查询和范围查询性能都非常优秀，可用二分查找（O(logN)）。

只适用于静态存储引擎（中间插入需移动后面所有记录）。

N 叉搜索树

二叉搜索树检索时间复杂度 O(logN)，但索引不止在内存中，还要写到磁盘上。树的高度意味着磁盘扫描次数，因此使用 N 叉树来减少树高度。

InnoDB 整数字段索引的 N 约 1200，树高 4 时可存 17 亿个值。

InnoDB 的索引模型

InnoDB 中表按主键顺序以索引形式存放，称为索引组织表。每个索引对应一棵 B+ 树。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

  • 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
  • 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

基于非主键索引的查询需要多扫描一次主键索引树，这个过程称为回表。

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。

  • 为了保证有序，插入新值时，可能需要按序挪动已有数据。
  • 此外，如果所在的数据页满了，需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。
  • 当相邻两个页由于删除了数据，利用率很低之后，会将数据页合并。合并的过程，可以认为是分裂过程的逆过程。

由于非主键索引的叶子节点内容是主键的值，因此**主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。**所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

适合用业务字段直接做主键的场景：

  • 只有一个索引；
  • 该索引必须是唯一索引。

05 深入浅出索引（下）

覆盖索引

能覆盖查询字段的索引，可以直接提供查询结果，无需回表，称为覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这里的最左，可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

如果是联合索引，那么 key 也由多个列组成，同时，索引只能用于查找 key 是否存在（相等），遇到范围查询 (>、<、BETWEEN、LIKE) 就不能进一步匹配了，后续退化为线性查找。因此，列的排列顺序决定了可命中索引的列数。

应该将选择性高的列或基数大的列优先排在多列索引最前列。但有时，也需要考虑 WHERE 子句中的排序、分组和范围条件等因素，这些因素也会对查询性能造成较大影响。“索引的选择性”是指不重复的索引值和记录总数的比值，最大值为 1，此时每个记录都有唯一的索引与其对应。索引的选择性越高，查询效率越高。如果存在多条命中前缀索引的情况，就需要依次扫描，直到最终找到正确记录。

索引下推

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

06 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁（FTWRL）

  • 作用：对整个数据库加锁，使数据库进入只读状态（阻塞所有数据更新、DDL 操作和事务提交）。
  • 使用场景：
    • 全库逻辑备份（确保备份数据的一致性）。
    • 问题：
      • 主库备份会导致业务停摆（无法更新）。
      • 从库备份会阻塞主从同步（binlog 延迟）。
  • 替代方案：
    • mysqldump --single-transaction（InnoDB 适用）：
      • 通过事务的可重复读隔离级别和 MVCC 实现一致性视图，备份期间允许数据更新。
    • readonly=true的缺陷：
      • 影响主备库判断逻辑；异常时不会自动释放锁，风险更高。
  • 适用引擎：
    • InnoDB：优先用--single-transaction。
    • MyISAM：必须用 FTWRL（不支持事务）。

表级锁

表锁（LOCK TABLES ... READ/WRITE）

  • 行为：显式加锁，限制其他线程的读写，同时限制本线程的操作范围（如LOCK TABLES t1 READ后，本线程只能读t1）。
  • 应用场景：
    • MyISAM 等不支持行锁的引擎。
    • InnoDB 一般不用（行锁更细粒度）。

元数据锁（MDL）

  • 自动加锁：

    • 读锁：增删改查时自动加（多个读锁不互斥）。
    • 写锁：修改表结构时加（与读锁/其他写锁互斥）。

  • 常见问题：

    • 长事务阻塞 DDL：未提交的事务会持有 MDL 读锁，导致后续 DDL（如加字段）被阻塞，进而阻塞所有后续查询（线程爆满）。

  • 解决方案：

    • 监控长事务（information_schema.innodb_trx），必要时 kill。

    • 使用 WAIT/NOWAIT 语法（MariaDB/AliSQL 支持）：

      ALTER TABLE tbl_name WAIT 10 ADD COLUMN ...;  -- 等待 10 秒超时
ALTER TABLE tbl_name NOWAIT ADD COLUMN ...;  -- 立即放弃

关键实践建议

  • 备份策略：
    • InnoDB 库：用mysqldump --single-transaction（非阻塞）。
    • 含 MyISAM 的库：用 FTWRL（需业务低峰期）。
  • DDL 操作：
    • 避免在高峰期执行，优先检查长事务。
    • 使用支持超时的 DDL 语法（如 MariaDB 的WAIT N）。
  • 锁升级：将 MyISAM 表迁移到 InnoDB，避免使用表锁。

小结

锁类型命令/机制适用场景风险与解决方案
全局锁FTWRLMyISAM 备份业务阻塞 → 改用 InnoDB+事务
表锁LOCK TABLESMyISAM 并发控制影响粒度大 → 升级 InnoDB
MDL 锁自动加锁（读/写）防止表结构不一致长事务阻塞 DDL → 监控/Kill

通过合理选择锁机制和引擎，可以平衡数据一致性与并发性能。

07 行锁功过：怎么减少行锁对性能的影响？

行锁在引擎层由各个引擎自己实现。MyISAM 不支持行锁，并发只能用表锁（同时刻只能有一个更新）。InnoDB 支持行锁。

如果事务中需要锁多个行，把最可能造成锁冲突的锁的申请时机尽量往后放。

两阶段锁协议：行锁在需要时才加上，但要等到事务结束时才释放。

死锁和死锁检测

不同线程循环依赖资源导致无限等待，称为死锁。

两种策略：

  • 等待超时：通过 innodb_lock_wait_timeout 设置（默认 50s）。
    • 问题：等待时间过长影响业务；设置太短又会误伤普通锁等待
  • 死锁检测（innodb_deadlock_detect=on）：发现死锁后主动回滚某个事务。
    • 问题：极端情况下（所有事务更新同一行），每次加锁都要检测循环依赖，时间复杂度 O(n²)，导致 CPU 高但吞吐量低

减少死锁的主要方向：控制访问相同资源的并发事务量。

08 事务到底是隔离的还是不隔离的

事务的启动时机：

  • begin/start transaction 命令并不是事务的起点，事务的真正启动是在执行第一个操作 InnoDB 表的语句时。
  • 使用 start transaction with consistent snapshot 可以立即启动事务并创建一致性视图。

一致性视图（Consistent Read View）：

  • 在可重复读隔离级别下，事务启动时会创建一个一致性视图，事务执行期间看到的数据与该视图一致。
  • 一致性视图是基于事务 ID（transaction id）和数据版本（row trx_id）来实现的。

“快照”在 MVCC 里是怎么工作的？

InnoDB 中每个事务有唯一的 transaction id（事务开始时申请，按顺序递增）。每行数据有多个版本（每次更新生成新版本，带 row trx_id），旧版本通过 undo log 保留。

图中虚线框是同一行数据的 4 个版本，当前最新 V4（k=22，row trx_id=25）。虚线箭头是 undo log，V1/V2/V3 是按需从当前版本通过 undo log 计算出来的。

一致性视图（read-view）实现：

事务启动时构造一个数组，保存当前所有“活跃”（已启动未提交）的事务 ID。

  • 低水位：数组中最小事务 ID
  • 高水位：当前系统已创建的最大事务 ID + 1

数据版本可见性判断：

row trx_id 位置可见性
绿色部分（< 低水位）可见（已提交或自己生成）
红色部分（≥ 高水位）不可见（将来事务生成）
黄色部分（低~高之间，在数组中）不可见（未提交事务）
黄色部分（低~高之间，不在数组中）可见（已提交事务）

InnoDB 利用“所有数据都有多个版本”的特性，实现了“秒级创建快照”。

更新逻辑

更新数据都是先读后写，称为“当前读”（current read）。

可重复读 vs 读提交：

  • 可重复读：事务开始时创建一致性视图，整个事务共用
  • 读提交：每个语句执行前重新计算新视图
Copyright © 2026 钝悟