Java

# MySQL 实战45讲学习笔记

# 1. MySQL 架构

MySQL 分为两部分：Server 层与存储引擎层。 Server 中含有：连接器、分析器、优化器与执行器，存储引擎层即使用于存储的引擎，其中包含有用于操作数据的接口。

  • 连接器：用于连接 MySQL，会校验账号与密码。

  • 分析器：用于做词法分析和语法分析，判断输入的 MySQL 语句是否正确。

  • 优化器：用于优化输入的 SQL 语句，选择优化器认为的最优的执行顺序，查询索引等。

  • 执行器：即真正的去调用存储引擎来查询所需要的数据。

查询缓存：在 MySQL 8.0以前带有查询缓存，即以 key、value 的形式存储我们的查询语句与查询结果。但是不被推荐使用，因为只需要更新表内的任意一条数据，都会导致整张表的缓存删除，导致缓存的命中率低下，只适用于极少更新的表中，故不被推荐使用，并且在 MySQL 8.0以后删除了查询缓存的功能。

# 2. redolog 与 binlog

# binlog

  • 介绍

binlog 是 MySQL 数据库 Server 层实现的修改日志，记录数据库中原始逻辑操作。

  • 特点
  1. MySQL 中所有的引擎都有 binlog，是 MySQL 中的Server层实现的
  2. binlog 没有定长空间，是追加写入的方式，当写满了的时候可以找下一个空间接着写
  3. binlog 是记录逻辑原始逻辑操作，比如 ID = 2 的行修改 c = c + 1

# redolog

  • 介绍 在执行器调用存储引擎之后，存储引擎出于效率考虑，并不会立即将数据存入磁盘中，而是先将操作写入 redolog 中，在 redolog 中记录下自己的操作，然后在适当的时间（比如系统空闲时间）将数据写入磁盘。

  • 特点

  1. redolog 只有 InnoDB 才有，是 InnoDB 自己实现的
  2. redolog 是有固定的存储空间的，它有两个指针，分别是 write pos 与 check point， write pos 指针表示当前日志写入到的位置，当指针到末尾以后会回到头部重新开始写入。 而 check point 则表示清除了 redolog 的位置，将 redolog 的内容写入到磁盘中，check point 与 write pos 之间的位置则表示空闲待写入的空间。当 write pos 追上 check point 的时候会停止写入 redolog， 先将记录的 redolog 存入到磁盘中，待有空闲空间以后再继续写入 redolog
  3. redolog 记录的是物理操作，比如在某个数据页上，修改了什么数据，更为具体。
  4. 因为在写入数据进入物理磁盘之前，会先写入 redolog，所以在我们数据库崩溃异常重启的时候，只需要依照我们的 redolog 上的记录来恢复尚未存入到磁盘中的数据，就可以恢复原库数据，这个行为称为 crash-safe。

# 关于 crash-safe 能力

看完讲解后我提出一个问题，为什么 binlog 没有 crash-safe 能力？它也记录了事务的操作，我们按照 binlog 的记录直接恢复未存储的数据不就好了吗？

查阅一些资料无果之后又仔细多次阅读 MySQL 实战45讲，发现也并没有描述为什么 binlog 不带有 crash-safe 能力，所以在往后继续看两阶段提交的时候，需要在脑中有一个思想，就是 binlog 不带有 carsh-safe 能力，不然你在看两阶段提交的时候会很纳闷，不能理解为什么。

# 两阶段提交

假设我们要执行一个修改语句：UPDATE T SET c = c + 1 WHERE ID = 2

该语句执行的流程图如下： 执行流程图

图中可以看到，redolog 的写入是分为两阶段的，分别是开始的 prepare 阶段与事务提交后的 commit 阶段。

为什么会需要有这两个阶段呢？

我们可以使用反证法假设没有两阶段提交，单纯的只是分别写入这两个日志：

  1. 先写入 redolog 后写入 binlog

假设在写入了 redolog 的时候，数据库崩溃了，我们需要使用 redolog 恢复原库，这个时候 redolog 记录 c 的值已经被修改成了 1，我们使用 redolog 的 crash-safe 能力恢复以后库的值即为 1，这个时候 1 即为原库值。但是这个时候 binlog 中还没有写入 c = 1 的操作，如果我们之后使用 binlog 来恢复的话，值就不会等于 1，就与原库值不一致。

  1. 先写入 binlog 后写入 redolog

假设在写入 binlog 的时候数据库崩溃了，这个时候 binlog 已经记录了 c = 1，但是我们的 redolog 中没有记录下来，使用 crash-safe 恢复库以后，原库值 c = 0，两边又不一致了。

而使用两阶段提交的话，我们就能知道当前日志是否同时写入了 redolog 与 binlog 中，能确保两边恢复的数据都一致了。

# 3. 事务隔离

事务隔离是数据库的一个重要特性，能够避免在并行操作的时候读取到我们意料之外的值情况。同时也能够确保在我们进行一系列操作的时候，当某一个操作失败的时候能够将之前的操作也回滚掉，不在数据库中生效。

数据库操作主要分为读操作和写操作。写操作的控制主要是依靠数据库的锁来控制并发下的意外情况，而读操作则依据设置的事务隔离级别不同，会有不同的处理。

事务隔离从读的方面来讲，主要目的是为了解决如下几个问题：

假设有如下数据： insert into T(c) values(1);

假设有事务 A、B，事务 A 优先于事务 B 开启。

  • 脏读：读取到其他事务尚未提交的内容。比如在事务 A 中，我修改了 c 的值为2，事务 A 尚未提交，但是事务 B 已经读取到了这个尚未提交的新值。
  • 不可重复读：在另一个事务开启期间，重复读取某一行的值，但是读取到的值前后不一致。比如事务 A 第一次读取 c 的值为1，然后事务 B 修改了 c 的值为2并提交，这个时候事务 A 再次读取 c 的值为2，前后读取到的 c 值不一致，此为不可重复读。
  • 幻读：在事务开启期间重复读取一张表中的数据总行数，前后读取到的行数不一致。比如事务 A 第一次读取到的表 T 的行数为1，然后事务 B 在表 T 中新增了一行并提交，这个时候事务 A 再次读取表行数为2，前后不一致。

幻读和不可重复读的主要区别点在与，不可重复读是指某一行数据，前后重复读取的值不相同，而幻读是读取一张表中的行数，前后不一致。不可重复读针对于行的修改，幻读针对的是对表中行数据的新增与删除操作。

介绍完事务并发读取的几个问题后，我们再谈下不同的事务隔离级别：

  • 读未提交：一个事务可以读取到另外一个事务尚未提交的修改。无法解决以上任一问题。
  • 读已提交：一个事务只能读取到其他事务已经提交的数据，尚未提交的数据无法读取到。能解决脏读问题。
  • 可重复读：一个事务在事务期间读取某一行的值，前后都保持一致，不会出现前后读取到的数据不一致的问题。能解决脏读，不可重复读。
  • 串行化：所有的事务都是串行执行，没有并发操作，效率低下，但是能解决以上所有问题。

ORACLE 默认为读已提交，MySQL 默认为可重复读。

# 事务隔离实现原理

事务隔离实现的依靠的是回滚日志与 Read-View。

回滚日志记录下了我们的数据值可以从当前值修改回原值的内容，而 Read-View 则是我们数据库中表的快照。

比如当我们的事务隔离级别为可重复读的时候，我们每开启一个事务都会生成一个 Read-View 的快照，然后在事务执行期间，我们读取的都是这个快照而不是实际的数据库， 这样就保证了我们的事务期间读取到的值都是一致的，不会出现不可重复读的问题。

而读已提交则是在每次执行 SQL 语句的时候都会生成一个 Read-View，读未提交则不会生成 Read-View，所有的操作都是直接作用在表上的。

当我们想要回滚数据的时候，我们依靠回滚日志与 Read-View就可以回滚我们的事务。假设我们的事务隔离级别为读已提交，将表 T 中的 c 值依次修改为 2、3、4，那么这个时候事务则会记录如下的回滚日志与 Read-View。

回滚日志与 Read-View

当我们想要回滚事务的时候，需要依次从 Read-View C - B - A 按照回滚日志来回滚。

那么我们的回滚日志总不可能一直保存着吧？什么时候删除呢？

是否删除回滚日志取决于我们是否有比回滚日志更早的 Read-View，当没有更早的 Read-View 的时候就会清理掉回滚日志了。

至于何时删除 Read-View，MySQL 实战中没讲，我没也查到，猜测事务结束的时候就会清理掉 Read-View 了，但是为什么不在事务结束的时候直接清除掉回滚日志呢？事务结束了不就不需要回滚日志了吗？

# 解决长事务

长事务会一直累积回滚日志，增加库的大小，需要尽量避免长事务的出现。

如何避免长事务呢？ 个人认为需要做到以下几点：

  1. 统一项目后端框架，确保我们的框架不会自动运行 set autocommit=0
  2. 规范开发行为，如无特殊需求禁止使用长事务
  3. 必要的话可以要求开发全部手动控制整个事务的生命周期，从开启事务到提交、回滚事务都手动编写，不使用框架的自动事务提交
  4. CodeReview，检查代码，避免漏提交事务，写错等
  5. 定期运行 select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now()，trx_started))>60 检查数据库中是否存在长事务

大佬答案

首先，从应用开发端来看：

  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（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

# 4. 索引

# 常见索引数据结构

  1. 哈希结构

采用数据与链表相结合的数据结构，每个数组中存放的是一个链表。将 key 值哈希后得到数组下标值，然后放入数组中的链表中，当发生哈希碰撞时则直接接在链表后面即可。因为数组的特性，所以等值查询访问速度很快，但是如果进行范围查询的话，会需要扫描所有数据，非常慢，所以适合等值查询场景。

  1. 有序数组

有序数组则是直接使用有序数组来存储数据，不管是等值查询还是范围查询都非常的快，缺点则是如果在数组中间插入新数据的话，需要移动后续的所有数据，插入的代价非常大，不适合频繁插入的场景，只适合存储一些静态的数据场景。

  1. 二叉搜索树

二叉搜索树是一个二叉平衡树的数据结构。针对于等值查询与范围查询的情况查询效率都是 O(log(N))，性能很高，并且插入也是 O(log(N))，是一种非常优秀的存储结构。但是因为树是分层的，查询的时候是需要一层层从上往下查的，如果使用二叉搜索树的话，当数据量大的时候，树的高度会非常高。

比如一个树高为20的二叉搜索树，存储的行数为 2^19 = 524288，我们如果要搜索最后一行的数据的话，需要加载20个数据块，机械硬盘每次随机读取一个数据块的寻址时间是10 ms 左右，那就是20 * 10，消耗的时间非常多。如果我们替换二叉树为 N 叉树的话，比如 InnoDB 使用的 n 为1200，当树高为4的时候，可以存储的行数为 1200 * 1200 * 1200 = 17亿之多，所以现在很多数据库存储都采用了 N 叉树来存储。

# InnoDB 的索引

InnoDB 中常见的索引为主键索引（聚簇索引）与非主键索引（二级索引）。主键索引的叶子节点存储的是整行数据，而非主键索引的叶子结点存储的主键索引的 key 值，当我们使用主键索引查询数据的时候，在找到主键索引后，可以直接从叶子节点获取数据，但是如果使用非主键索引查询的时候，则是先查询到主键索引的 key，然后再通过主键索引查询到主键上的行数据。（B+ 树详细介绍）

# 当我表中有其他唯一字段的时候，我是否需要自增主键

从以上介绍可以看到，我们的主键索引存储的是行数据，非主键存储的是主键索引值。所以当你的表数据中存在其他索引的时候，如果使用很长的非自增字段作为索引，在非主键索引的叶子中会需要存储较多的数据，浪费存储空间。而我们日常项目中，大多数情况都会存在非主键索引，所以我们最好还是使用自增主键作为主键，这样其他叶子结点的存储会比较节省空间。

# 关于重建索引

重建索引的作用：索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

留下问题：以下2个重建索引的语句是否正确？

重建二级索引

alter table T drop index k;
alter table T add index(k);

重建主键索引

alter table T drop primary key;
alter table T add primary key(id);

答案： 重建索引k的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。在专栏的第12篇文章《为什么表数据删掉一半，表文件大小不变？》中，我会和你分析这条语句的执行流程。

# 索引查询流程介绍

表结构

create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

插入数据语句

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

索引结构示意图 索引结构示意图

查询语句

select * from T where k between 3 and 5

查询流程

  1. 利用二级索引 k，查询索引树，找到 k = 3 的索引，获取到叶子节点上的主键值 300。（查询索引树 k 一次）
  2. 利用主键值 300 回表查询主键索引树，查询到 id = 300 的行数据。（回表一次）
  3. k 索引树继续查询下一个数据，找到 k = 5，满足条件，获取到叶子结点上的主键值 500。（查询索引树 k 一次）
  4. 利用主键值 500 回表查询主键索引树，查询到 id = 500 的行数据。（回表一次）
  5. k 索引树继续查询下一个数据，找到 k = 6，不满足条件，查询结束。（查询索引树 k 一次）

以上总共查询 k 索引树 3 次（MySQL 只拿到了 2 行数据，索引它认为扫描行数只是 2），回表 2 次。

# 覆盖索引

索引覆盖指的是查询语句中查询的字段在索引树上都存在，所以不需要回表都能够获取到所有的字段值，因为不需要回表，所以查询效率更高。覆盖索引详情见

# 最左前缀原则

MySQL 在查询的时候遵循最左前缀原则，最左前缀有两个概念：

  1. 即当我们建立多字段联合索引的时候，当我们最左侧的索引字段存在于查询语句中时，我们的索引即可生效，比如 (a, b) 索引，查询语句 Selet * FROM T WHERE a > 0。但是当查询语句为 Selet * FROM T WHERE b > 0 时，索引不生效。
  2. 当我们使用 LIKE 进行模糊查询的时候，若查询内容左侧字符未使用匹配字符而是使用具体的查询内容的话，即可使用索引。比如我们的查询语句为 SELECT * FROM T WHERE k LIKE '张%'，此时可以使用索引，但是若改为 SELECT * FROM T WHERE k LIKE '%张%' 时不可使用索引。

依照最左前缀原则，我们创建索引的第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。若必须要维护两个索引的话，尽量将占用存储空间较小的字段单独建立索引，节省空间，索引占用的空间越小，一个数据页存储的索引越多，所需 IO 操作越少，查询效率越快。

最左前缀详情见

# 索引下推

假设表结构如下：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

当我们查询：

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

依照最左前缀原则，可以匹配上 name_age 索引。

  • 在 MySQL 5.6 之前，索引会忽略 age 索引，只是查询索引上的 name 是否匹配，然后再一条一条数据回表。 回表查询

  • 在 MySQL 5.6 之后，索引会同时使用 name 与 age 索引，在二级索引上，当判断 name 索引符合条件但是 age 索引不满足条件的时候，并不会去进行回表查询。 不回表查询

# 5. 数据库的锁

数据库的锁分为：全局锁、表级锁与行锁吗，下面分别介绍。

# 全局锁

全局锁是对整个数据库实例加锁，一般用于逻辑备份数据库。MySQL 提供的加全局锁的命令是 Flush tables with read lock (FTWRL)。

全局锁处理过程风险较大，因为当全局锁加上以后，如下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

导致的主要问题如下：

  • 当在主库加全局锁时，会导致加锁期间所有的写操作都不能执行，业务无法继续。
  • 当在从库加全局锁时，会导致主库同步过来的 binlog 不能被执行，主从数据延迟。

所以在有事务的存储引擎上，我们一般不会使用全局锁的方式来逻辑备份我们的数据，而是使用可重复读事务的方式来创建一个数据库的视图，在视图上备份我们的数据。这样我们库的读写操作不会停止，不影响业务。

为何不使用 set global readonly=true 的方式使库变为只读，从而备份？

  • 有些系统中使用数据库是否只读来判断该库为主库还是从库，如果全局修改的话，可能会影响判断。
  • 当我们的客户端备份连接突然异常中断的时候，如果使用 FTWRL 命令，中断以后会自动解锁，让业务恢复正常，但是使用设置只读的方式库依旧会保持只读，继续影响业务。

# 表级锁

表级锁分为两种：表锁与元数据锁（MDL）。表锁可以使用 lock tables … read/write 语法来开启，元数据锁不需要主动开启，当我们在操作 MySQL 数据库时会自动开启。

表锁

表锁使用的限制也比较大，比如当我们加表读锁的时候，其他线程不能对该表进行写操作，加锁线程本身也只能对表做读操作而不能进行写操作，同时也不能访问其他表。当我们给表加读写锁的时候，当前线程可以对表进行读写操作，但是其他线程读写操作都不能做。

在细粒度更低的行锁出现之前，表锁是主要的处理并发的方式。

元数据锁（MDL） 元数据锁是在 MySQL 5.5 版本中引入的，在我们访问表的时候会自动加上锁，用于避免出现当我们在读写表的时候，其他线程对表进行了结构变更，导致数据与表结构不一致的问题（比如删除列）。

当我们对一张表进行增删改查的时候，MDL 会自动添加上 MDL 读锁，当我们修改表结构的时候会添加上 MDL 写锁。

MDL 读锁：不互斥，加上读锁以后其他线程也可执行增删改查操作，但不可进行表结构修改操作。

MDL 写锁：互斥，加上写锁以后其他线程不能操作表（所以在生产环境添加字段时需要谨慎操作）。

生产加字段、修改字段和添加索引操作需要全表扫描，若生产上的大表修改会阻塞很长时间，需慎重。小表也需要慎重，推荐小表更新时在 alter 语句中添加定时，当指定时间内没有获取到 MDL 写锁的话则自动超时放弃，不阻塞后续业务，等待下次开发人员重新尝试修改。

# 提问

当备库用–single-transaction做逻辑备份的时候，如果从主库的binlog传来一个DDL语句会怎么样？

个人开始认为没有影响，因为我觉得 single-transaction 做备份是单独拉出一个视图，修改原库并不影响已经拉出来的视图，很明显，不完全。

答案如下： 假设 DDL 语句针对的是表 t1，执行 single-transaction 备份的时候关键语句如下：

/* 手动设置一次事务级别，确保事务级别为可重复读 */
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
/* 启动事务，使用 WITH CONSISTENT SNAPSHOT 确保可以得到一个一致性视图 */ 
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* 设置保存点 */
/* other tables */
Q3:SAVEPOINT sp;
/* 获取表结构 */
/* 时刻 1 */
Q4:show create table `t1`;
/* 正式导出数据 */
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 回滚到保存点，为了释放 MDL 读锁 */
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */

具体情况分为以下 4 种：

  1. 在 Q4 之前到达，那么不会有任何现象，在 Q4 获取到的是 DDL 执行后的表结构
  2. 在时刻 2 的时候到达，在执行 Q5 的时候会提示 Table definition has changed, please retry transaction（表结构已经变更），终止 mysqldump
  3. 在时刻 2 与时刻 3 之间到达（个人认为指的即使在执行 Q4 以后，已经获取了 MDL 读锁），MDL 读锁被获取，DDL 无法进行写操作，延迟主从同步
  4. 在时刻 4 之后到达，读锁已经释放，DDL 语句正常执行，无现象

# 疑问

在 Q2 的时候不是已经获取到了一个一致性视图了吗？为什么不是直接查询视图？而是后面还是需要获取表结构以及查表？（个人视图概念还是不是很清晰，需后续继续学习视图后再来看这个问题）

# 行锁

顾名思义，行锁即是针对于数据行的锁。行锁的实现完全有存储引擎自己实现，所以不同的存储引擎也不同。比如 MyISAM 引擎就不支持行锁，只能靠表锁来控制并发。

下面我们主要来聊聊 InnoDB 行锁的几个点。

# 两阶段锁协议

两阶段锁协议指的是当一个事务中执行创建、更新或删除语句的时候，才会添加上行锁，并且行锁在使用完以后（即执行完语句）并不会立即释放，而是需要等到事务提交以后才会释放所有的行锁。

如下图： 两阶段锁协议

当事务 A 更新 id = 1 的行时，给该行添加行锁，更新 id = 2 的行时给该行添加行锁。在事务 B 更新 id = 1 的行时，已经被事务 A 加上了行锁，所以被阻塞，知道事务 A 提交以后释放了行锁，事务 B 才能获取到行锁，执行更新语句。

因为两阶段锁协议的存在，当并发很高的时候，同时有很多个线程对相同的一些行做增删改操作的时候，又会带来一个很严重的问题，死锁。

# 死锁

死锁即是指两个不同的线程中各自持有不同的锁，且各自不释放自己手中的锁，同时对方手中持有的锁又是自己依赖的下一个锁，所以互相等待对方释放锁来继续下一步操作，因为两个线程互相不释放锁，所以一直在等待导致死锁的出现。

如下图： 死锁

事务 A 持有行 id = 1 的行锁，事务 B 持有行 id = 2 的行锁，互相等待对方释放锁，导致死锁。

# 解决死锁

解决死锁的方式有两种：

  • 等待超时自动释放锁 在 MySQL 中你可以通过 innodb_lock_wait_timeout 参数来设置超时时间，默认值为 50s，但是在业务中，如果等待锁释放 50s的话，会让业务停顿很长时间，这一般来说往往是无法接受的，而如果你讲该值设置的很小的话，假设有某个执行时间较长的事务，那可能总是会导致事务失败，所以并不是很适合处理高并发的死锁，只能作为保障操作。

  • 死锁检测 死锁检测是主动去检测是否有线程持有你需要的锁，导致死锁的出现，当检测到死锁的时候，会自动回滚导致死锁的某一事务，让其他事务继续执行，确保不会停顿。在 MySQL 中可以通过设置 innodb_deadlock_detect 为 on 来开启，默认即为 on。死锁检测虽好，能够及时检测出死锁，避免一直阻塞在死锁处，但是同样会带来问题。

    当并发很高的时候死锁检测消耗的时间也是非常的长。每个线程在检查死锁的时候，需要遍历所有的线程，判断是否有死锁的存在，这个时间复杂度是 O(n)，但是每个线程都需要检查一次，所以是 O(n^2)，当有 1000 个线程的时候，那死锁检测就是 100 万次，耗时非常大。

# 解决死锁检测耗时长

主要思路有 2 个：

  • 确保代码不会出现死锁，关闭死锁检测。该方法风险太大，不太可取。

  • 控制代码并发度 可以从 4 个方面控制：

    1. 如果连接数据库客户端不多，或者只有特定的客户端会操作特定的表，可以直接从客户端处控制并发
    2. 若有连接数据库的中间件，在中间件中控制并发
    3. 如果有能修改 MySQL 源码的大神，可以直接修改源码，当有线程在修改行的时候，若有其他线程想要修改，则在进入引擎之前先进入队列排队
    4. 从业务上缓解问题，针对于特定的业务场景，适当的增加数据行数，尽量避免并发修改同一行的情况出现

# 提问

如果你要删除一个表里面的前10000行数据，有以下三种方法可以做到：

  • 第一种，直接执行delete from T limit 10000;
  • 第二种，在一个连接中循环执行20次 delete from T limit 500;
  • 第三种，在20个连接中同时执行delete from T limit 500。

你会选择哪一种方法呢？为什么呢？

我的答案：第二种。

第一种直接删除 10000 行，我认为耗费时间很长，且一次锁定的行数过多，会阻塞库较长时间。

第三种同时用 20 个连接并发执行，很容易会造成锁的冲突，而且我认为可能删除的结果也不是 10000 行，待验证。

正确答案：第二种。

第一种方式（即：直接执行delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。

第三种方式（即：在20个连接中同时执行delete from T limit 500），会人为造成锁冲突。

# 6. 唯一索引与普通索引的选取

# 索引的查询区别

例：SELECT id FROM T WHERE k = 5

  1. 普通索引在查询到满足 k = 5 的索引的时候，会继续去查询下一个，直到查询到不满足条件的值位置。
  2. 唯一索引在查询到满足 k = 5 的索引的时候因为唯一性的约束则会立即停止继续查询。

所以相比唯一索引，普通索引多出了一次查询。因为数据加载都是按照数据页一页一页的加载进入内存，如果在同一个数据页的时候，这一次查询的损耗微乎其微，而查询的数据为最后一条，导致不在同一个数据页的概率很低。经测算，平均的性能损耗是非常低的，所以可以认为普通索引与唯一索引在查询性能上基本没什么差异。

# 索引的更新区别

更新的时候需要提出一个新的概念，叫做 change buffer。

当我们需要更新一个数据页的时候，如果当前数据页已经被读取到数据页中，那么可以直接更新这个数据页。

但是当数据页不在内存中的时候，我们会将当前的更新先存储在 change buffer 中，当下次查询到这个需要被更新的数据页的时候，我们再将 change buffer 中的改动直接在内存中修改数据页，这个将 change buffer 中的内容修改到数据页中的操作叫做 merge。

当然，除了数据页被加载到内存中的时候会更新数据页以外，系统后台也有线程会定时 merge，在系统正常关闭的过程中，也会进行 merge 操作。

这就是 change buffer，利用好它可以帮我们减少读取数据页的次数，change buffer 中累计的改动越大，性能优化则越大。

需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

# 什么时候使用 change buffer 呢？

当我们有大量的写入操作，而且写入之后不需要立即读取出数据的时候就适合使用 change buffer。因为写入多，且不需要立即读取，则可以在 change buffer 中积攒很多改动，然后一次写入数据页，对性能的优化是很明显的。

但是像唯一索引就不需要使用 change buffer，因为唯一索引在写入数据之前需要将数据读取出来，判断当前索引是否已经存在，必然是需要读取数据页的，既然数据页都已经读取了，那么直接更新数据页肯定是更快的，没必要再使用 change buffer 了。

所以基于写入数据上的关于 change buffer 的差异，导致当数据页不在内存中的情况，普通索引写入性能会比唯一索引更好，尤其体现在大量写入且不需要立即读取的场景下。

# 总结

当我们业务上已经能够必然确定字段的强唯一性的时候，我们优先选取普通索引，可以提高我们的写入数据的性能。

但是当业务上没办法必然确定业务的强唯一性，还需要数据库来保证数据不会出现重复值的时候，我们就必须选择唯一索引，其实日常业务普通开发的情况，可能还是选择唯一索引的情况比较多。

像丁奇大神举例的类似归档库的情况可能会比较适合直接使用普通索引。

# change buffer 与 redolog 的 WAL 机制的区别

WAL 即 Write-Ahead Logging，先写日志再写磁盘，参考 MySQL 实战 45 讲 第二篇。

change buffer 是在我们内存中没有当前待修改的数据页的时候，节省一次读取数据页的开销，将改动记录在缓存中，之后再写入数据页的行为，节省的是随机读取磁盘的 IO 消耗。

redolog 的 WAL 是在写入磁盘前，先记录在日志中，等稍后再统一写入磁盘上，是将一次次的随机写转换成了统一的顺序写，节省的是随机写的 IO 消耗。

← 概览 →