mysql实战45讲学习笔记一

这是去年读过的一个专栏，当时有些概念自己还不是很了解，现在回过头来再看确实是干货满满，非常不错的一个专栏。由于专栏内容较多，纯手打记录笔记效率太低，因此本笔记内容多数为原文重点内容摘抄，留作后面再次回看的基础，因此有条件有时间的同学可以看原博客，比较忙的看我这些笔记也可以有收获。本篇中涉及到的概念详见目录，有问题及时在本博客或个人博客留言

mysql的基本架构

mysql总体来说分为server层和服务器层，其基本架构如下：

server层涵盖mysql大部分核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取，多种存储引擎插件式的集成在该架构中。

一条sql语句的执行过程一般是经过连接器，分析器，优化器，执行器等功能模块，最后到达存储引擎。

redolog与binlog

假设现在有一个表t，主键为id，有一列整型列名为c，有下面一条update语句：

update t set c=c+1 where id=2

上面说到查询的过程经过的mysql架构，在更新语句中也会经过，除此之外更新语句还会涉及到两个重要的日志模块，redolog和binlog

redolog

在mysql中，假如每一次更新都是直接写进磁盘，磁盘找到对应的记录，然后更新，这个过程中io成本，查找成本都很高，为了避免这个问题，在innodb存储引擎中提供了redolog模块，一条更新语句会先更新内存中，同时写入redolog，然后在适当的时机刷入磁盘。这其实就是mysql常提到的一个技术，WAL(Write-Ahead Logging)，先写日志，再写磁盘。

innodb中redolog的大小是固定的，假设配置为一组4个文件，每个文件大小为1GB，那么总共就可以记录4GB的操作，redolog中记录的是物理操作，即在某个数据页上做了什么修改。假设下面是mysql中redolog模块的结构：

其中write pos是当前记录的位置，一边写一边后移，写到3号文件的末尾就回到0号文件开头，checkpoint是当前要擦除的位置，也是往后推移并循环的，擦除记录之前要把记录更新到数据文件。两者之间的是可以用来记录新操作的空间，假如write pos追上checkpoint，表示这个空间满了，不能再记录新的更新，需要停下来擦除一些记录，把checkpoingt推进下。

有了redolog，innodb就可以保证在数据库发生异常重启的时候，之前提交的记录都不会丢失，这个能力称为crash-safe。

binlog

redolog在存储引擎层，并且是innodb引擎特有的，而在Server 层也有自己的日志，称为 binlog（归档日志）。

这两种日志有以下三点不同。

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

下面是执行更新语句的整个过程：

• 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁 盘读入内存，然后再返回。
• 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到 新的一行数据，再调用引擎接口写入这行新数据。
• 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
• 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状 态，更新完成

整个过程的执行流程如下图,图中浅色框表示是在 InnoDB 内部执行的， 深色框表示是在执行器中执行的。：

可以看到redolog的写入拆成了两个步骤： prepare 和 commit，这就是"两阶段提交"，这是为了让两份日志之间的逻辑一致。

为什么日志需要“两阶段提交”？

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。
仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执 行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash， 会出现什么情况呢？

• 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候， MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍 然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。 但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。 然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢 失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

• 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写， 崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录 了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

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

数据库隔离级别

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

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复 读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。 在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要 注意的是，“读未提交”隔离级别下直接返回记录上的新值，没有视图概念；而“串行 化”隔离级别下直接用加锁的方式来避免并行访问。

事务隔离的实现

以“可重复读”为例说明：

在 MySQL 中，每条记录在更新的时候都会同时记录一条回滚操作（undolog）。记录上的新值，通过回滚操作，都可以得到前一个状态的值。
假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。 如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

你一定会问，回滚日志总不能一直保留吧，什么时候删除呢？答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除,什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。

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

可以在 information_schema 库的 innodb_trx 这个表中查询长事务

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

这个问题，我们可以从应用开发端和数据库端来看。
首先，从应用开发端来看：

• 确认是否使用了setautocommit=0。这个确认工作可以在测试环境中开展，把MySQL的general_log开起来，然后随便跑一个业务逻辑，通过general_log的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成1。

• 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用begin/commit框起来。我见过有些是业务并没有这个需要，但是也把好几个select语句放到了事务中。这种只读事务可以去掉。

• 业务连接数据库的时候，根据业务本身的预估，通过SETMAX_EXECUTION_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

其次，从数据库端来看：

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

索引

在 MySQL 中，索引是在存储引擎层实现的，常用索引存储结构有哈希索引，B+树索引。

哈希表这种结构适用于只有等值查询的场景，不适合范围查询。

为什么不用二叉树存储索引

原因是，索引不止存在内存中，还要写到磁盘上。想象一下一棵100万节点的平衡二叉树，树高20。一次查询可能需要访问20个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要10ms左右的寻址时间。也就是说，对于一个100万行的表，如果使用二叉树来存储，单独访问一个行可能需要20个10ms的时间，这个查询可真够慢的。
为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N叉”树。这里，“N叉”树中的“N”取决于数据块的大小。

以InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了。考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了

N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了

innodb索引模型

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

假设有一张表T，有主键索引id和普通索引k，表中五条数据的id和k分别为(100,1)、(200,2)、(300,3)、(500,5) 和 (600,6），那么两棵树的示意如下：

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

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

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

基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。如果新插入的ID值为400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。
而更糟的情况是，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。
当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

为什么优先使用自增主键

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT

插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。
也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插 入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

从存储空间的角度来看：假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级 索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整 型（bigint）则是 8 个字节。

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

如何避免回表

由上面知道，对于非主键索引/二级索引 （secondary index）其叶子节点存的是主键，当我们通过非主键索引查询数据的时候，需要先通过非主键索引查询定位到其叶子节点的主键值，然后回表去查询这条主键对应数据的其他信息，假如我们只是通过非主键索引去查询主键select id from T where k=5，因为非主键索引的叶子节点存储内容本身就有主键，因此可以直接提供查询结果，不需要回表。这个时候我们称索引k为覆盖索引。

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

基于覆盖索引的启示，假如我们有一个高频接口，本身要查的字段比较少，比如通过会员手机号查询会员昵称，我们可以在手机号与昵称上建立联合索引，这样查询就不用回表，直接可以通过手机号查到昵称。

但是，索引字段的维护总是有代价的，如果字段太多就不合适了。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

最左前缀索引

相信大家都知道最左前缀原则，就是说假如有一个联合索引（a，b，c）那么查询语句中where条件中wherea=？或者where a=? and b=?或者where a=? and c=？都可以利用到这个索引，但是where b=？and c=？是用不到这个索引的。因此我们在建立联合索引的时候，索引字段顺序要把最常作为条件的放在前面，同时如果有了联合索引能够兼容单字段索引，比如上面还有个索引a，那么就可以只维护联合索引，删除a字段的单独索引以节省维护成本。

索引下推

假设有一张会员表，里面有id，name，age，sex，tel等字段，其中id是主键，在（name,age）上有联合索引，假如现在的需求是检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男性”。那么，SQL 语句是这么写的：

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

通过前面的最左前缀索引知道，第一个条件name like '张 %'可以用到（name,age）上的联合索引，通过name定位到id。

在 MySQL 5.6 之前，只能从查到的第一条name like '张 %' 数据开始一个个回表。到主键索引上找出数据行，再对比age和sex字段值。

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

重建索引

对于上面例子中的 InnoDB 索引模型中提到的表 T，如果你要重建索引 k， 你的两个 SQL 语句可以这么写：

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。

思考题：下面的索引是必须的吗

实际上主键索引也是可以使用多个字段的。DBA 小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的：

CREATE TABLE `geek` (
	`a` INT ( 11 ) NOT NULL,
	`b` INT ( 11 ) NOT NULL,
	`c` INT ( 11 ) NOT NULL,
	`d` INT ( 11 ) NOT NULL,
	PRIMARY KEY ( `a`, `b` ),
	KEY `c` ( `c` ),
	KEY `ca` ( `c`, `a` ),
KEY `cb` ( `c`, `b` ) 
) ENGINE = INNODB;

公司的同事告诉他说，由于历史原因，这个表需要 a、b 做联合主键，这个小吕理解了。
但是，学过本章内容的小吕又纳闷了，既然主键包含了 a、b 这两个字段，那意味着单独在字段 c 上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？
同事告诉他，是因为他们的业务里面有这样的两种语句：

select * from geek where c=N order by a limit 1; 
select * from geek where c=N order by b limit 1;

这位同事的解释对吗，为了这两个查询模式，这两个索引是否都是必须 的？为什么呢？

解答

主键 a，b 的聚簇索引组织顺序相当于 order by a,b ，也就是先按 a 排序，再按 b 排序， c 无序

索引 ca 的组织是先按 c 排序，再按 a 排序，同时记录主键 （非主键索引上会记录主键值，这里记录的主键只有b，不是ab），这个跟索引 c 的数据是一模一样的

索引 cb 的组织是先按 c 排序，在按 b 排序，同时记录主键 （这里记录的只有a，不包括b）

所以，结论是 ca 可以去掉，cb 需要保留。

全局锁和表锁

全局锁

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

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

但是让整库都只读，听上去就很危险：
如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从 延迟。

而不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

有一个方法能够拿到一致性视图，就是在可重复读隔离级别下开启一个事务。

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

single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员 使用 InnoDB 替代 MyISAM 的原因之一。

全库只读，为什么不使用 set global readonly=true 的方式

readonly 方式也可以让全库进入只读状态，但还是建议用 FTWRL 方式，主要有两个原因：

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

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（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 的操作。连写 t1 都不允许，自然也不能访问其他表。

在还没有出现更细粒度的锁的时候，表锁是常用的处理并发的方式。而对于 InnoDB 这 种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面 还是太大。

元数据锁（MDL）

另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上，其作用是，保证读写的正确性,防止DDL和DML并发冲突。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

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

MDL锁的注意点

虽然 MDL 锁是系统默认会加的，但却是你不能忽略的一个机制。我们知道给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响。而实际上，即使是小表，操作不慎也会出问题。下面是一个例子：

sessionA	sessionB	sessionC	sessionD
begin
select * from t limit 1;
	select * from t limit 1;
		alter table t add f int;(blocked)
			select * from t limit 1;(blocked)

• 我们可以看到sessionA先启动，这时候会对表t加一个MDL读锁。由于sessionB需要的也是MDL读锁，因此可以正常执行。
• 之后sessionC会被blocked，是因为sessionA的MDL读锁还没有释放，而sessionC需要MDL写锁，因此只能被阻塞。
• 如果只有sessionC自己被阻塞还没什么关系，但是之后所有要在表t上新申请MDL读锁的请求也会被sessionC阻塞。前面我们说了，所有对表的增删改查操作都需要先申请MDL读锁，就都被锁住，等于这个表现在完全不可读写了。
• 如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session再请求的话，这个库的线程很快就会爆满。
• 你现在应该知道了，事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

如何安全的给小表加字段

首先我们要解决长事务，事务不提交，就会一直占着MDL锁。在MySQL的information_schema库的innodb_trx表中，你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务。

如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？
这时候kill可能未必管用，因为新的请求马上就来了。比较理想的机制是，在altertable语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。

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

ALTER TABLE tbl_name NOWAIT add column …

ALTER TABLE tbl_name WAIT N add column …

行锁

行锁就是针对数据表中行记录的锁。

两阶段锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。如果你的事务中需要锁多个行，要把可能造成锁冲突、可能影响并发度的锁尽量往后放。

提升并发度的小例子

实现一个电影票在线交易业务，顾客A要在影院B购买电影票。我们简化一点，这个业务需要涉及到以下操作：
1.从顾客A账户余额中扣除电影票价；2.给影院B的账户余额增加这张电影票价；3.记录一条交易日志。
也就是说，要完成这个交易，我们需要update两条记录，并insert一条记录。为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务中的顺序呢？
试想如果同时有另外一个顾客C要在影院B买票，那么这两个事务冲突的部分就是语句2了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。
**根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。**所以，如果你把语句2安排在后，比如按照3、1、2这样的顺序，那么影院账户余额这一行的锁时间就少。这就大程度地减少了事务之间的锁等待，提升了并发度。

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。比如下面的例子：

事务A	事务B
begin；update t set k=k+1 where id=1	begin
	update t set k=k+1 where id=2
update t set k=k+1 where id=2
	update t set k=k+1 where id=1

事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

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

在InnoDB中，innodb_lock_wait_timeout的默认值是50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。
但是，我们又不可能直接把这个时间设置成一个很小的值，比如1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

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

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

怎么解决由这种热点行更新导致的性能问题

一种方法是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉.但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一 个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

另一个思路是控制并发度.如果并发能够控制住，比如同一行同时多只有10个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。一个直接的想法就是，在客户端做并发控制。但是，你会很快发现这个方法不太可行，因为客户端很多。见过一个应用，有600个客户端，这样即使每个客户端控制到只有5个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到3000。
因此，这个并发控制要做在数据库服务端。如果你有中间件，可以考虑在中间件实现；如果团队有能修改MySQL源码的人，也可以做在MySQL里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。

以上方案均有实现方面的难度，另外有一种方案就是将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如10个记录，影院的账户总额等于这10个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的1/10，可以减少锁等待个数，也就减少了死锁检测的CPU消耗。
这个方案看上去是无损的，但其实这类方案需要根据业务逻辑做详细设计。如果账户余额可能会减少，比如退票逻辑，那么这时候就需要考虑当一部分行记录变成0的时候，代码要有特殊处理。

思考题

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

解答：

假设这个 DDL 是针对表 t1 的， 这里我把备份过程中几个关键的语句列出来：

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT； 
/* other tables */ 
Q3:SAVEPOINT sp; 
/* 时刻 1 */ 
Q4:show create table `t1`; 
/* 时刻 2 */ 
Q5:SELECT * FROM `t1`; 
/* 时刻 3 */ 
Q6:ROLLBACK TO SAVEPOINT sp; 
/* 时刻 4 */ 
/* other tables */

在备份开始的时候，为了确保RR（可重复读）隔离级别，再设置一次RR隔离级别(Q1);
启动事务，这里用WITH CONSISTENT SNAPSHOT确保这个语句执行完就可以得到一个一致性视图（Q2)；
设置一个保存点，这个很重要（Q3）；
show create是为了拿到表结构(Q4)，然后正式导数据（Q5），回滚到SAVEPOINT sp，在这里的作用是释放t1的MDL锁（Q6）

DDL 从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成。

• 如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
• 如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
• 如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
• 从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。

mvcc

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

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

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

快照在mvcc里是怎么工作的

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。假如数据有100G，这个快照是怎么实现的？

InnoDB里面每个事务有一个唯一的事务ID，叫作transactionid。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版 本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的 数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。
也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。下面就是一个记录被多个事务连续更新后的状态：

图中虚线框里是同一行数据的 4 个版本，当前最新版本是 V4，k 的值是 22，它是被 transaction id 为 25 的事务更新的，因此它的 row trx_id 也是 25。

前面的文章说过，语句更新会生成 undo log（回滚日志），那么， undo log 在哪呢？

实际上，图2中的三个虚线箭头，就是undolog；而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undolog计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来。

明白了多版本和rowtrx_id的概念后，我们再来想一下，InnoDB是怎么定义那个“100G”的快照的。
按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。
因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。
当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。
在实现上，InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就是，启动了但还没提交。

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。
这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。
而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。
这个视图数组把所有的 row trx_id 分成了几种不同的情况。

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

• 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；

• 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；

• 如果落在黄色部分，那就包括两种情况

  a.若rowtrx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；

  b.若rowtrx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。

比如，对于本节图1中的数据来说，如果有一个事务，它的低水位是18，那么当它访问这一行数据时，就会从V4通过U3计算出V3，所以在它看来，这一行的值是11。

你看，有了这个声明后，系统里面随后发生的更新，是不是就跟这个事务看到的内容无关 了呢？因为之后的更新，生成的版本一定属于上面的 2 或者 3(a) 的情况，而对它来说，这些新的数据版本是不存在的，所以这个事务的快照，就是“静态”的了。

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

案例分析

下面以一个案例来进行分析：

如下是一个只有两个字段的表：

 mysql> CREATE TABLE `t` (
      `id` int(11) NOT NULL,  
     `k` int(11) DEFAULT NULL,  
     PRIMARY KEY (`id`) ) ENGINE=InnoDB; 
 insert into t(id, k) values(1,1);

事务A	事务B	事务C
start transaction with consistent snapshot
	start transaction with consistent snapshot
		update t set k=k+1 where id=1
	update t set k=k+1 where id=1;select k from t where id=1
select k from t where id=1;commit
	commit

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

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

事务 C 没有显式地使用 begin/commit，表示这个 update 语句本身就是 一个事务，语句完成的时候会自动提交。事务 B 在更新了行之后查询 ; 事务 A 在一个只读事务中查询，并且时间顺序上是在事务 B 的查询之后。

这时，如果告诉你事务 B 查到的 k 的值是 3，而事务 A 查到的 k 的值是 1，你是不是感觉有点晕呢,下面是分析过程:

快照读

假设：

• 事务 A 开始前，系统里面只有一个活跃事务 ID 是 99；
• 事务 A、B、C 的版本号分别是 100、101、102，且当前系统里只有这四个事务；
• 三个事务开始前，(1,1）这一行数据的 row trx_id 是 90。

这样，事务 A 的视图数组就是 [99,100], 事务 B 的视图数组是 [99,100,101], 事务 C 的视图数组是 [99,100,101,102]。

为简化分析，下面只画出跟事务 A 查询逻辑有关的操作：

从图中可以看到，第一个有效更新是事务C，把数据从(1,1)改成了(1,2)。这时候，这个数据的最新版本的rowtrx_id是102，而90这个版本已经成为了历史版本。
第二个有效更新是事务B，把数据从(1,2)改成了(1,3)。这时候，这个数据的最新版本（即rowtrx_id）是101，而102又成为了历史版本。
你可能注意到了，在事务A查询的时候，其实事务B还没有提交，但是它生成的(1,3)这个版本已经变成当前版本了。但这个版本对事务A必须是不可见的，否则就变成脏读了。
好，现在事务A要来读数据了，它的视图数组是[99,100]。当然了，读数据都是从当前版本读起的。所以，事务A查询语句的读数据流程是这样的：

• 找到 (1,3) 的时候，判断出 row trx_id=101，比高水位大，处于红色区域，不可见；
• 接着，找到上一个历史版本，一看 row trx_id=102，比高水位大，处于红色区域，不可见；
• 再往前找，终于找到了（1,1)，它的 row trx_id=90，比低水位小，处于绿色区域，可见。

这样执行下来，虽然期间这一行数据被修改过，但是事务A不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读（快照读）。

上面的分析相对麻烦，下面我们形成一个共识，一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：
1.版本未提交，不可见；2.版本已提交，但是是在视图创建后提交的，不可见；3.版本已提交，而且是在视图创建前提交的，可见。

我们用这个规则来判断图 4 中的查询结果，事务 A 的查询语句的视图数组是在事务 A 启动的时候生成的，这时候：

(1,3)还没提交，属于情况1，不可见；
(1,2)虽然提交了，但是是在视图数组创建之后提交的，属于情况2，不可见；
(1,1)是在视图数组创建之前提交的，可见。

当前读

这时你可能有疑问了：事务B的update语句，如果按照一致性读，好像结果不对？如下图：

是的，如果事务 B 在更新之前查询一次数据，这个查询返回的 k 的值确实是 1。

但是，当它要去更新数据的时候，就不能再在历史版本上更新了，否则事务C的更新就丢失了。因此，事务B此时的setk=k+1是在（1,2）的基础上进行的操作。
所以，这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（currentread）。

因此，在更新的时候，当前读拿到的数据是(1,2)，更新后生成了新版本的数据(1,3)，这个新版本的rowtrx_id是101。
所以，在执行事务B查询语句的时候，一看自己的版本号是101，最新数据的版本号也是101，是自己的更新，可以直接使用，所以查询得到的k的值是3。
这里我们提到了一个概念，叫作当前读。其实，除了update语句外，select语句如果加锁，也是当前读。

所以，如果把事务A的查询语句select * from t where id=1修改一下，加上lock in share mode或for update，也都可以读到版本号是101的数据，返回的k的值是3。下面这两个select语句，就是分别加了读锁（S锁，共享锁）和写锁（X锁，排他锁）。

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

假设事务 C 不是马上提交的，而是变成了下面的事务 C’，会怎么样呢?

事务A	事务B	事务C
start transaction with consistent snapshot
	start transaction with consistent snapshot
		start transaction with consistent snapshot;update t set k=k+1 where id=1
	update t set k=k+1 where id=1;select k from t where id=1
select k from t where id=1;commit		commit
	commit

事务C’的不同是，更新后并没有马上提交，在它提交前，事务B的更新语句先发起了。前面说过了，虽然事务C’还没提交，但是(1,2)这个版本也已经生成了，并且是当前的最新版本。那么，事务B的更新语句会怎么处理呢？

这时候，我们在上一篇文章中提到的“两阶段锁协议”就要上场了。事务 C’没提交，也就是说 (1,2) 这个版本上的写锁还没释放。而事务 B 是当前读，必须要读最新版本，而且 必须加锁，因此就被锁住了，必须等到事务 C’释放这个锁，才能继续它的当前读。

事务的可重复读的能力是怎么实现的？

可重复读的核心就是一致性读（consistentread）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。
而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

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

那么，我们再看一下，在读提交隔离级别下，事务 A 和事务 B 的查询语句查到的 k，分别 应该是多少呢？

这里需要说明一下，“start transaction with consistent snapshot; ”的意思是从这个语 句开始，创建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于普通的 start transaction。

下面是读提交时的状态图，可以看到这两个查询语句的创建视图数组的时机发生了变化， 就是图中的 read view 框。（注意：这里，我们用的还是事务 C 的逻辑直接提交，而不是 事务 C’）

这时，事务A的查询语句的视图数组是在执行这个语句的时候创建的，时序上(1,2)、(1,3)的生成时间都在创建这个视图数组的时刻之前。但是，在这个时刻：

(1,3) 还没提交，属于情况 1，不可见；
(1,2) 提交了，属于情况 3，可见。

所以，这时候事务 A 查询语句返回的是 k=2。显然地，事务 B 查询结果 k=3。

思考题

如果某次写入使用了 change buffer 机制，之后主机异常重启，是否会丢失 change buffer 和数据

答案是不会丢失。虽然是只更新内存，但是在事务提交的时候，我们把change buffer的操作也记录到redo log里了，所以崩溃恢复的时候，change buffer也能找回来。

merge的过程是否会把数据直接写回磁盘？

merge的执行流程是这样的：

• 从磁盘读入数据页到内存（老版本的数据页）；
• 从change buffer里找出这个数据页的change buffer记录(可能有多个），依次应用，得到新版数据页；
• 写redo log。这个redo log包含了数据的变更和change buffer的变更。