MySQL 核心原理速记，覆盖架构、日志、事务隔离 / MVCC、索引与锁等高频主题。

MySQL 架构

img

连接器

负责和客户端建立连接、获取权限、维持和管理连接。连接命令如下：

1
mysql -h $ip -P $port -u $user -p

查询缓存

  1. 但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

  1. MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

分析器

词法、语法分析，分析你SQL语句中的各个字段，确保没有出错。比如识别SELECT 关键字，识别出表单名t.

select * from t;

优化器

  1. 在表里面有多个索引的时候，决定使用哪个索引
  2. 在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的 join：
1
mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;
  • 既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。
  • 也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

执行器

  1. 判断执行权限
  2. 根据表的引擎定义，调用引擎接口去执行

日志

内容详见 - MySQL-日志

redo log - 重做日志

采用WAL（Write - Ahead Logging）, 先写日志，后写磁盘。

详细介绍：

img

bin log

存在于Server层。

Undo log

存储事务执行过程中数据修改前的旧值。目的在事务回滚时，能够利用这些旧值。

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

img

区别

  • redo log是存储引擎特有的，bin log是在Server层实现的，也就是说所有引擎都可以使用。
  • redo log是物理日志，举个例子，比如你将某个值由1改成2，又将2改成3，那么最终物理日志呈现的只有3（而没有记录3的由来），只不过不是直接将3写入，可能是采用偏移量之类的操作方式（无奈我看不懂），bin log是逻辑日志，逻辑日志表示记录从1-2-3这样子的一个过程。
  • redo log是循环写的，大小是固定的，bin log是追加的方式写的，不会覆盖以前的日志。
特性 Redo Log (重做日志) Bin Log (归档日志) Undo Log (回滚日志)
核心关键词 “恢复” “复制” “撤销”
比喻 记账本的草稿 (防止断电忘事) 完整的账单明细 (给别人看) 橡皮擦 / Ctrl+Z
侧重点 物理偏向 (页修改) 逻辑偏向 (SQL语义) 逻辑反向 (逆操作)
主要作用 崩溃恢复 (Crash Safe)



保证 D (持久性)		 主从复制、数据恢复



保证数据一致性		 事务回滚、MVCC



保证 A (原子性) 和 I (隔离性)
写入时机 事务进行中不断写 事务提交时一次性写 事务开始前/修改前写
释放时机 落盘后覆盖 (循环写) 不删除 (追加写) 事务提交后，若无 MVCC 需求则标记删除 (Purge)

事务隔离

事务的特性

ACID，即对应原子性、一致性、隔离性、持久性。通过undo log来保证原子性，能够撤销事务内的所以操作来保证原子性，要么是全部都成功，通过redo log来保证持久性，会根据策略进行刷脏，通过锁+MVCC的方式来保证隔离性，而一致性指的是从一个正确的状态迁移到另外一个正确的状态下，其实就是通过事务中的AID来保证C。

事务隔离级别

隔离级别越高，性能越低。如下的隔离级别逐次升高。

读未提交 - read uncommited

当前事务可以读取到未提交的事务。这种级别会造成脏读、不可重复读、幻读

读提交 - read commited

当前事务能够读取已提交事务的修改，这种隔离级别会造成不可重复读、幻读

可重复读 - repeatable read

当前事务一开始读的数据是什么样子，那在它提交之前一直都是这样子，即使多次读取。会有幻读问题，但是个人认为它**不会造成幻读**（通过MVCC + 锁的方式）；

串行化 - serializable

对于同一行记录，写操作会加写锁，读操作会加读锁，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

各类读问题

脏读（Dirty Read）

  • 定义：脏读是指在一个事务处理过程中读取了另一个未提交事务修改的数据。也就是说，事务A读取了事务B正在修改但尚未提交的数据，之后如果事务B回滚，那么事务A读取的数据就是无效的、“脏”的数据。
  • 示例：假设有两个事务，事务A是一个查询操作，事务B是一个更新操作。事务B更新了数据库中某条记录的一个字段值，但还没提交。此时事务A查询这条记录，获取到了事务B修改后的字段值。随后，事务B由于某种原因回滚了，那么事务A之前读取到的字段值就不再有效，这种情况就是脏读。
  • 产生原因：主要是因为事务的隔离级别设置较低，允许一个事务读取另一个未提交事务的数据。在数据库并发操作中，如果没有适当的并发控制机制，就容易出现脏读现象。

不可重复读（Non - repeatable Read）

  • 定义：不可重复读是指在一个事务内，多次读取同一数据时，由于其他事务对该数据进行了修改或删除操作，导致每次读取的数据不一致。
  • 示例：事务A在开始时读取了数据库中某条记录的某个字段值为10。随后，事务B更新了这条记录的该字段值为20并提交。当事务A再次读取这条记录的该字段时，得到的值为20，与第一次读取的值不同，这种情况就是不可重复读。
  • 产生原因：这是由于事务的隔离级别没有完全限制其他事务对数据的修改操作。在多事务并发环境下，当一个事务允许其他事务在其执行过程中修改它正在读取的数据时，就可能出现不可重复读的情况。

幻读（Phantom Read）

  • 定义：幻读是指在一个事务中，按照某个条件进行数据查询时，第一次查询和第二次查询得到的结果集数量不同。这通常是因为在两次查询之间，另一个事务插入或删除了满足查询条件的记录。
  • 示例：事务A查询数据库中年龄大于30岁的员工数量，得到结果为10人。在事务A执行期间，事务B插入了几条年龄大于30岁的新员工记录并提交。当事务A再次查询年龄大于30岁的员工数量时，得到的结果比第一次多，好像出现了“幻觉”一样，这种情况就是幻读。
  • 产生原因：主要是因为事务的隔离级别不能阻止其他事务插入或删除符合查询条件的记录。在并发事务环境下，当一个事务在执行过程中，其他事务对数据的插入或删除操作影响了该事务的查询结果集，就会产生幻读。

事务隔离级别与这三种情况的关系

  • 读未提交（Read Uncommitted）：这是最低的事务隔离级别，允许脏读、不可重复读和幻读。在这种隔离级别下，一个事务可以读取另一个未提交事务的数据，所以很容易出现上述三种情况。
  • 读已提交（Read Committed）：这种隔离级别可以避免脏读。在一个事务读取数据时，只能读取其他已提交事务的数据，但是仍然可能出现不可重复读和幻读。
  • 可重复读（Repeatable Read）：可以避免脏读和不可重复读。在一个事务执行期间，它所读取的数据不会被其他事务修改，但是可能会出现幻读。在一些数据库系统（如MySQL的InnoDB存储引擎）中，通过使用间隙锁等技术可以在一定程度上减少幻读的发生。
  • 串行化（Serializable）：这是最高的事务隔离级别，可以避免脏读、不可重复读和幻读。在这种隔离级别下，事务是串行执行的，相当于对并发事务进行了严格的限制，虽然保证了数据的一致性，但会严重影响系统的性能和并发处理能力。

MVCC

Multi Version Concurrency Control - 多版本并发控制，一种数据库并发控制的技术，用来实现各种事务隔离级别。

快照读

读的是数据的历史版本。常用以实现不同事务之间的隔离。

MVCC 为每个事务提供了一个数据快照，事务在这个快照范围内读取数据。这个快照包含了在事务开始时已经提交的数据版本。通过快照读，事务可以避免受到其他事务在其执行过程中对数据进行修改的影响，从而实现了不同事务之间的隔离

SELECT 是快照读，如果加锁的话，便是当前读。

当前读

读的是数据的最新版本。

常用于对数据实时性要求高的场景，比如增删改等用来更新数据的场景，都需要读取到数据的最新版本。

  • 更新数据 - UPDATE 、DELETE、INSERT

原理

  • MVCC 的核心思想是为每个事务提供一个数据快照（snapshot）。当一个事务开始时，它会看到数据库在某个特定时刻的状态，并且在整个事务期间，这个状态对于该事务是相对固定的。这意味着，即使其他事务对数据进行了修改，当前事务看到的仍然是它开始时的数据版本。
  • 数据库会为每个数据行保存多个版本，这些版本可以通过时间戳或者版本号来区分。每当数据被修改时，不是直接覆盖原来的数据，而是创建一个新的版本。例如，在一个简单的银行账户余额数据表中，当用户 A 进行取款操作修改余额数据时，数据库不会立即删除原来的余额记录，而是创建一个新的余额版本记录，并标记上相应的事务时间戳或者版本号。

优势

  • 提高并发性能：不会因为锁的竞争而导致系统性能下降。每个事务看到的是自己的版本数据，读写操作不会阻塞
  • 保证数据一致性和隔离性：MVCC 能够提供不同的事务隔离级别，通过合理设置版本号或时间戳的检查规则，可以实现如读已提交（Read Committed）、可重复读（Repeatable Read）等隔离级别。

索引

InnoDB使用B+树作为索引模型。

  • 非叶子节点不保存数据
  • 叶子节点间使用 指针相连，并按照大小顺序排列

主键索引

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

如何选择索引

  • 基于索引的“区分度”来选择，一个索引上不同的值越多，区分度越高。索引上不同的值的个数，我们称之为“基数”（cardinality），也就是说，这个基数越大，索引的区分度越好。

如何加索引

针对超长字符串，可以考虑选择如下其一方式建立索引：

  • 倒序存储+前缀索引
  • 添加hash字段，为其建立索引，crc32

回表

普通索引，也就是通过二级索引找到对应的主键，在通过主键找到所需要的数据，这个过程就叫做回表，如果直接通过二级索引就能找到数据的话是不需要回表的。

覆盖索引

如果查找的列本身就在二级索引上，那么就不要回表了，这样子的索引叫做覆盖索引，通常用来减少回表的次数以提高性能。

联合索引

使用多个字段组合的索引，假设有联合索引（a，b），那么在索引树中它会先根据a的大小进行排序，如果有相同值的a在按照b的大小进行排序。

最左前缀

可以是联合索引的最左N个字段，比如说有联合索引（a，b，c），那么等值查询条件是a = 1的时候会用到联合索引中的部分索引a，条件是a = 1 and b = 2的时候会用到联合索引中的部分索引（a，b），同理条件是a = 1 and b = 2 and c = 3，而对于条件是a = 1 and c = 3的情况下会使用也只能用到联合索引中的部分索引a。

索引下推

  1. 索引下推（Index Condition Pushdown，ICP）概述
  • 定义：索引下推是一种数据库查询优化技术，主要应用于数据库引擎（如MySQL的InnoDB存储引擎）在处理联合索引查询时。它的基本思想是将原本需要在服务器层（MySQL Server）进行的部分条件判断下推到存储引擎层（InnoDB），在索引遍历过程中就进行条件过滤，从而减少不必要的数据回表操作，提高查询效率。
  • 背景：在没有索引下推之前，当使用联合索引进行查询时，存储引擎通过索引获取到满足部分索引列条件的数据行的主键（以InnoDB为例），然后将这些主键对应的完整数据行回表（回到数据表）读取，最后在服务器层对这些完整的数据行进行其他剩余条件的判断。这种方式可能会导致大量不必要的数据回表操作，浪费系统资源。
  1. 索引下推的工作原理
  • 假设在一个employees表中有一个联合索引idx_name_age（包含name和age两个列），现在要执行一个查询SELECT * FROM employees WHERE name LIKE 'J%' AND age > 30;。
  • 传统方式：
  • 存储引擎首先使用联合索引idx_name_age找到name以J开头的所有记录的主键值。
  • 然后对这些主键值对应的记录进行回表操作，将完整的数据行读取到服务器层。
  • 最后在服务器层判断这些记录的age是否大于30，筛选出满足条件的记录。
  • 索引下推方式：
  • 存储引擎在遍历联合索引idx_name_age时，不仅会检查name列是否以J开头，还会同时检查age列是否大于30。只有当name和age条件都满足的索引记录对应的主键才会被用来进行回表操作，读取完整的数据行。这样就避免了许多不满足age > 30条件的记录的回表操作，减少了数据读取量和系统开销。
  1. 索引下推的优势
  • 减少回表操作次数：通过在存储引擎层提前过滤掉不符合条件的数据，大大减少了回表操作的频率。回表操作通常涉及磁盘I/O或者缓存读取，减少回表操作可以显著提高查询性能，尤其是在处理大量数据和复杂查询时。
  • 提高查询效率：由于减少了不必要的数据读取和处理，查询的整体效率得到提升。在高并发的数据库环境中，这种性能提升可以使系统能够更快速地响应大量的查询请求。
  1. 索引下推的限制和注意事项
  • 对索引的依赖：索引下推是基于索引进行操作的，只有在使用联合索引并且有可以下推的条件时才能发挥作用。如果查询条件没有涉及联合索引或者没有合适的下推条件，就无法利用索引下推。
  • 不同数据库引擎的支持情况：不是所有的数据库引擎都支持索引下推。目前MySQL的InnoDB存储引擎支持索引下推，但其他数据库引擎可能需要具体查看其文档来确定是否支持以及支持的程度。
  • 与其他优化技术的结合：在实际的数据库优化中，索引下推通常需要与其他优化技术（如合理的索引设计、查询语句优化等）结合使用，才能达到最佳的性能提升效果。例如，即使有索引下推，如果索引本身设计不合理（如索引列的顺序不符合查询习惯等），查询效率可能仍然不高。

页分裂

B+树的叶子节点会按照从小到大的顺序排列，如果插入的主键要处于中间位置，那么需要移动后面的数据来腾出位置，而如果在插入之前数据页就已经满了的话，那么就需要将后面的部分数据移动至另外一个数据页上，这个过程就叫做页分裂。

页合并

当相邻两个数据页由于删除了数据之后，整个的空间变小了，那么就会将两个数据页进行合并，以提高数据页空间的利用率。

重建二级索引和重建主键索引

首先重建索引是为了让数据页的空间得到充分利用，也就省去了很多空间的浪费，对于重建二级索引来说是可以先删除掉索引在增加，对于重建主键索引的话如果先删除的话，它会使二级索引失效（由于重建主键索引，重新计算后的指针会发生变化），正确地重建主键索引是：ALTER TABLE T ENGINE = INNODB，或者是迁移数据库，不过这种方式只适合离线的业务。

锁

全局锁

给整个数据库实例加 读锁 - **Flush tables with read lock**, 其他线程的增删读写会被阻塞，使用 **unlock tables**进行解除。

为什么使用全局锁而不要使用全库只读（set global readonly = true）：一方面是readonly通常会被用来做其他的逻辑，比如说用来判断主备库，随意修改全局变量可能会影响；另外一方面使用全局锁的客户端如果崩溃了的话，MySQL会自动释放锁，整个库可以回到正常更新的状态，而将整个库设置为readonly之后，如果客户端发生异常，则数据库会一直保持readonly状态，导致整个库长时间处于不可写状态。

表锁

加表锁 - lock tables t1 read/write

行锁

  • 二阶段锁协议：在事务中，只有提交(commit)或者回滚(rollback)时才是解锁阶段， 其余时间为加锁阶段。
  • 发起死锁检测：innodb_deadlock_detect设置为on，默认情况下是on，缺点就是会耗费大量的CPU资源（发现死锁后主动回滚某一个事务，让其他事务得以继续执行）。

元数据锁 - metadata lock - MDL

属于Server层的锁，表级锁，主要用于隔离DML和DDL操作之间的干扰，比如一个线程在查询数据的时候，另外一个线程肯定不能修改表结构，不然就乱套了，所以DML操作需要申请MDL读锁，而DDL操作需要申请MDL写锁，读读之间共享，读写之间互斥，写写之间互斥。

  • DML：数据库操作语言
  • DDL：数据库定义语言
  • MDL 读锁 - 共享读锁：当一个事务只是读取数据库对象的元数据（如查询表的结构信息）时，会获取共享读锁。这种锁允许多个事务同时对同一对象的元数据进行读取操作，但不允许其他事务对该对象的元数据进行修改操作。
  • MDL 写锁- 排他锁：当一个事务需要修改数据库对象的元数据（如添加列、修改表结构等）时，会获取排他锁。这种锁会阻止其他事务对该对象的元数据进行读取或修改操作，直到该事务完成对元数据的修改并释放锁。

术语

  • 脏页：指内存（缓存池）中的数据页与磁盘上的数据页不一致的情况。数据库表单的增删改等写操作，都会产生脏页。

reference