发布于 

MySQL /maɪ ˈsiːkwəl/ 原理与实践 💽

梳理 MySQL 知识体系

SQL 执行

graph LR
    客户端-->连接器
    连接器-->查询缓存
    连接器-->分析器
    查询缓存--命中则\n直接返回结果-->客户端
    分析器-->优化器
    优化器-->执行器
    执行器-->存储引擎
    存储引擎
  • Server 层：连接器、查询缓存、分析器、优化器、执行器等（包括内置函数、存储过程、触发器、视图等）
    • 连接器：负责跟客户端建立连接、获取权限、维持和管理连接，客户端如果太长时间没动静，连接器就会自动将它断开
    • 查询缓存：key 是查询的语句，value 是查询的结果（查询缓存命中率通常很低），8.0 版本将查询缓存的整块功能删除
    • 分析器：根据词法分析的结果，语法分析器会根据语法规则，判断输入的这个 SQL 语句是否满足 MySQL 语法
    • 优化器：在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序
    • 执行器：开始执行的时候，要先判断一下你对这个表 T 有没有执行的权限，然后使用引擎提供的接口
  • 存储引擎层：负责数据的存储和提取，插件式支持 InnoDB（5.5.5 后默认）、MyISAM、Memory 等存储引擎

日志

与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）。

redo log

MySQL 中的 WAL（Write-Ahead Logging）关键点就是先写日志，再写磁盘。当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

InnoDB 的 redo log 是固定大小的文件，从头开始写，写到末尾就又回到开头循环写。有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

binlog

redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志），这两种日志有以下三点不同：

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  2. redo log 是 物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是 逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID = 2 这一行的 c 字段加 1 ”。
  3. redo log 是 循环写 的，空间固定会用完；binlog 是可以 追加写 入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程：

graph 
A[取 ID=2 这一行] --> B(数据页在内存中?)
B --> |是| C[返回行数据]
B --> |否| D[磁盘中读入内存]
D --> C
C --> E[将这行的 C 值加 1]
E --> F[写入新行]
F --> G[新行更新到内存]
G --> H[写入 redo log 处于 prepare 阶段]
H --> I[写 binlog]
I --> J[提交事务 redo log 处于 commit 状态]

两阶段提交

为了 让两份日志之间的逻辑一致，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是”两阶段提交”。

如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。

  • redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不丢失。
  • sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

事务

简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL 中，事务支持是在引擎层实现的。事务的 ACID（Atomicity、Consistency、Isolation、Durability） 即原子性、一致性、隔离性、持久性。

隔离级别

当数据库上有 多个事务同时执行 的时候，就可能出现 脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read） 的问题，为了解决这些问题，就有了“隔离级别”的概念。

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

  • 读未提交：一个事务还没提交时，它做的变更就能被别的事务看到。
  • 读提交：一个事务提交之后，它做的变更才会被其他事务看到。
  • 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然，在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
  • 串行化：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准：

  • 读未提交：直接返回记录上的最新值，没有视图概念
  • 读提交：视图是 在每个 SQL 语句开始执行的时候 创建的
  • 可重复读：视图是 在事务启动时 创建的，整个事务存在期间都用这个视图
  • 串行化：直接用加锁的方式 来避免并行访问

什么时候需要“可重复读”的场景呢？

假设管理一个个人银行账户表，一个表存了每个月月底的余额，一个表存了账单明细。这时候要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与 本月的账单明细 一致。一定希望 在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。

这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他事务更新的影响。

启动方式

MySQL 的事务启动方式有以下几种：

  1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
  2. set autocommit=0，这个命令会将这个线程的 自动提交 关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你 主动 执行 commit 或 rollback 语句，或者断开连接。

隔离的实现

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值，当没有事务再需要用到这些回滚日志时（就是当系统里没有比这个回滚日志更早的 read-view 的时候），回滚日志会被删除。

假设一个值从 1 被按顺序改成了 2、3、4，不同时刻启动的事务查询这条记录会有不同的 read-view，即同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

为什么尽量不要使用长事务？

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致 大量占用存储空间。除了对回滚段的影响，长事务还 占用锁资源，也可能拖垮整个库。

如何避免长事务对业务的影响？

从应用开发端来看：

  1. 确认是否使用了 set autocommit = 0。这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，目标就是把它改成 1。
  2. 确认是否有不必要的 只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。有些是业务并没有这个需要，但是也 把好几个 select 语句放到了事务中。这种只读事务可以去掉。
  3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来 控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

从数据库端来看：

  1. 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
  2. Percona 的 pt-kill 工具；
  3. 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
  4. 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

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

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。

第一种启动方式，一致性视图是在第执行第一个快照读语句时创建的； 第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot时创建的。

在 MySQL 里，有两个“视图”的概念：

  • 一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
  • 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

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

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是 基于整库 的（但并不需要拷贝整库数据）。

  • InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。
  • 而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。
  • 数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。
graph LR
    V1["V1 (k=1)
row trx-id=10"]
    V2["V2 (k=10)
row trx-id=15"]
    V3["V3 (k=11)
row trx-id=17"]
    V4["V4 (k=22)
row trx-id=25"]

    V1 -->|U1: set k=10
transaction id=15| V2
    V2 -->|U2: set k=k+1
transaction id=17| V3
    V3 -->|U3: set k=k*2
transaction id=25| V4

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。即一个事务以启动的时刻为准，如果一个数据版本是在启动之前生成的就认；如果是启动以后才生成的就不认，如果是这个事务自己更新的数据，还是要认的。

在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。

graph LR
    A["已提交事务"] 
    B["未提交事务集合"]
    C["未开始事务"]
    D["当前事务"]

    A <-->|低水位| B
    B <-->|高水位| C
    D --> B

对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

  1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
  2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
  3. 如果落在黄色部分，那就包括两种情况
    1. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
    2. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

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

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

  1. 版本未提交，不可见；
  2. 版本已提交，但是是在视图创建后提交的，不可见；
  3. 版本已提交，而且是在视图创建前提交的，可见。

更新逻辑

当要去更新数据的时候，就不能再在历史版本上更新了，否则其他事务的更新就丢失了，这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。 除了 update 语句外，select 语句如果加锁，也是当前读。

select * from t where id=1 改为修改一下，加上 lock in share mode 或 for update，下面这两个 select 语句，就是分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）。

1
2
mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

如果事务 C 没有提交，在同一行的写锁还没释放，事务 B 是当前读必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务 C 释放这个锁，才能继续它的当前读。

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

  • 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
  • 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

为什么表结构不支持“可重复读”？这是因为表结构没有对应的行数据，也没有 row trx_id，因此只能遵循当前读的逻辑，MySQL 8.0 已经可以把表结构放在 InnoDB 字典里了，也许以后会支持表结构的可重复读。

索引

常见索引模型

索引的常见模型有哈希表、有序数组、搜索树：

  • 哈希表 是一种以键 - 值（key-value）存储数据的结构，拉链法解决哈希冲突的问题，区间需要全部扫描，哈希表这种结构适用于只有等值查询的场景。
  • 有序数组 在等值查询和范围查询场景中的性能就都非常优秀，但是往中间插入一个记录就必须得挪动后面所有的记录，有序数组索引只适用于静态数据存储引擎。
  • 二叉搜索树 为了维持 O(log(N)) 的查询复杂度，就需要保持这棵树是平衡二叉树，为了做这个保证，更新的时间复杂度也是 O(log(N))。
  • N 叉树
    • 树可以有二叉，也可以有多叉，二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树，因为索引不止存在内存中，还要写到磁盘上
    • 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块，在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间
    • 为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块则使用“N 叉”树，“N”取决于数据块的大小
    • 以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了
    • 树根的数据块总是在内存中的（第二层也有很大概率在），一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 2-3 次磁盘
    • N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了

      在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样，而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

InnoDB 索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

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

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

基于主键索引和普通索引的查询有什么区别？

  • 如果语句是 select * from T where ID = 500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
  • 如果语句是 select * from T where k = 5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

即基于非主键索引的查询需要多扫描一棵索引树

索引维护

页分裂：如果插入数据所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去

页合并：当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并（分裂过程的逆过程）

哪些场景下应该使用自增主键，而哪些场景下不应该？

自增主键： NOT NULL PRIMARY KEY AUTO_INCREMENT，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂，而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。此外，主键长度越小，二级索引的叶子节点就越小，普通索引占用的空间也就越小。

如果业务的场景需求是只有一个索引且该索引必须是唯一索引（典型的 KV 场景），这时候就要优先考虑“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树（避免回表）。

覆盖索引

select * from T where k between 3 and 5 的查找过程（k 上有索引）：

  1. 在 k 索引树上找到 k = 3 的记录，取得 ID = 300；
  2. 再到 ID 索引树查到 ID = 300 对应的 R3；
  3. 在 k 索引树取下一个值 k = 5，取得 ID = 500；
  4. 再回到 ID 索引树查到 ID = 500 对应的 R4；
  5. 在 k 索引树取下一个值 k = 6，不满足条件，循环结束。

这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4），由于查询结果所需要的数据只在主键索引上有，所以 不得不回表。

如果执行的语句是 select ID from T where k between 3 and 5 ，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。

需要注意的是，在引擎内部使用覆盖索引在索引 k 上其实读了三个记录，但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

是否有必要将身份证号和名字建立联合索引？

如果有根据身份证号查询市民信息的需求，只要在身份证号字段上建立索引就够了。而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？如果有一个高频请求，要 根据市民的身份证号查询他的姓名，用到了覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

索引字段的维护总是有代价的，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

最左前缀原则

建立（name，age）联合索引，where name like ‘张%’，能够用上这个索引，查找到第一个符合条件的记录然后向后遍历，直到不满足条件为止。

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

建立联合索引的时候，索引内的字段顺序安排的评估标准是 索引的复用能力，因为可以支持最左前缀，所以当已经有了 (a, b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

但是如果查询条件里面只有 b 的语句，是无法使用 (a, b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a, b)、(b) 这两个索引，这时候要考虑的原则就是空间了，比如 name 字段是比 age 字段大的，则创建一个（name, age) 的联合索引和一个 (age) 的单字段索引。

索引下推

建立（name，age）联合索引

1
mysql> select * from user where name like '张 %' and age=10 and ismale=1;

这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录，在 MySQL 5.6 之前需要回表到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。InnoDB 在 (name, age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过

唯一索引 OR 普通索引

查询过程

  • 对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。
  • 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

因为引擎是按页读写的，当找到满足条件的记录的时候，它所在的数据页就都在内存里了，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。如果满足条件的记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这种情况的概率会很低，计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

更新过程

当需要更新一个数据页时：

  • 如果数据页在内存中就直接更新

  • 如果数据页还没有在内存中，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作（保证数据逻辑的正确性）

  • change buffer 是可以持久化的数据，其在内存中有拷贝，也会被写入到磁盘上，将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge

  • 除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

什么条件下可以使用 change buffer 呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束，而这必须要将数据页读入内存才能查找判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

如果要在一张表中插入一个新记录，InnoDB 的处理流程：

  • 第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

    • 对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；

    • 对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

    • 普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

  • 第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

    • 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

    • 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

    • 将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。

    • change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer 的使用场景

change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

索引选择和实践

普通索引和唯一索引在查询能力上是没差别的，主要考虑的是对更新性能的影响，所以建议尽量选择普通索引。如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。当有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

change buffer 和 redo log

change buffer 是占用 InnoDB buffer pool 的空间，同时也会持久化到硬盘

1
mysql> insert into t(id,k) values(id1,k1),(id2,k2);

分析这条更新语句，它涉及了四个部分：InnoDB buffer pool、redo log（ib_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）

这条更新语句做了如下的操作：

  1. Page 1（k1 所在的页） 在内存中，直接更新内存；
  2. Page 2（k2 所在的页）没有在内存中，就在内存的 change buffer 区域，记录下“往 Page 2 插入一行”这个信息；
  3. 将上述两个动作记入 redo log 中（写入磁盘）。

做完上面这些，事务就可以完成了，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

1
mysql> select * from t where k in (k1, k2)

如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作（k1，k2）就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关了

  1. 读 Page 1 的时候，直接从内存返回。WAL 之后如果读数据，是不是一定要读盘，是不是一定要从 redo log 里面把数据更新以后才可以返回？其实是不用，虽然磁盘上还是之前的数据，但直接从内存返回结果，结果是正确的。
  2. 要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果，所以直到需要读 Page 2 的时候，这个数据页才会被读入内存。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

索引的选择

选择索引是优化器的工作，而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。

扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

锁 🔒

数据库锁设计的初衷是处理 并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则，而锁就是用来实现这些访问规则的重要数据结构。根据加锁的范围，MySQL 里面的锁大致可以分成 全局锁、表级锁和行锁 三类。

全局锁

全局锁就是对整个数据库实例加锁，MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock(FTWRL)，使用这个命令之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做 全库逻辑备份。也就是把整库每个表都 select 出来存成文本，但是业务就得停摆。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的，single-transaction 方法只适用于所有的表使用事务引擎的库。

既然要全库只读，为什么不使用 set global readonly = true 的方式呢？

  1. 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大。
  2. 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)

表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。

对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

  • 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。
  • 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

如何安全地给小表加字段？

要小心不要导致锁住线上查询和更新，如：读（未提交）写（被阻塞）读（被阻塞）…

  1. 解决长事务，事务不提交，就会一直占着 MDL 锁，在 MySQL 的 information_schema 库的 innodb_trx 表中，可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
  2. 在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。

MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

1
2
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

行锁

行锁是在引擎层由各个引擎自己实现的，并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

行锁就是针对数据表中行记录的锁，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但 并不是不需要了就立刻释放，而是要 等到事务结束时才释放。这个就是两阶段锁协议。

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，比如电影票在线交易业务，这个业务需要涉及到以下操作：

  1. 从顾客 A 账户余额中扣除电影票价；
  2. 给影院 B 的账户余额增加这张电影票价；
  3. 记录一条交易日志。

要完成这个交易，需要 update 两条记录，并 insert 一条记录。为了保证交易的原子性，要把这三个操作放在一个事务中。那么，要怎样安排这三个语句在事务中的顺序呢？

如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院 账户余额这一行的锁时间就最少。这就最大程度地 减少了事务之间的锁等待，提升了并发度。

死锁检测

当出现死锁以后，有两种策略：

  • 一种策略是，直接进入等待，直到超时。这个 超时时间 可以通过参数 innodb_lock_wait_timeout 来设置。
  • 另一种策略是，发起 死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。

  • 超时退出容易误伤： 对于在线服务来说，这个等待时间往往是无法接受的。但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

  • 死锁检测要耗费大量的 CPU 资源： 每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

解决办法：

  • 头痛医头：就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是 业务有损 的。
  • 控制并发度：在客户端做并发控制汇总到数据库服务端以后峰值并发数也很高，如果有中间件，可以考虑在中间件实现，基本思路就是，对于相同行的更新，在进入引擎之前排队，这样在 InnoDB 内部就不会有大量的死锁检测工作了。
  • 业务设计上：通过将一行改成逻辑上的多行来减少锁冲突，可以考虑放在多条记录上，比如 10 个记录，影院的 账户总额等于这 10 个记录的值的总和（和记录表不一样的），这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。

自增 id

表定义自增值 id

InnoDB 系统自增 row_id

Xid

Innodb trx_id

thread_id

参考资料

快来参与讨论吧