《MySQL实战45讲》笔记

《MySQL实战45讲》笔记

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

MySQL的基本架构示意图

《MySQL实战45讲》(1-7)笔记

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

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

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、 Memory等多个存储引擎。

现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认存储引擎。

连接器

连接命令写法如下，输入完命令后在交互对话里面输入密码。也可以直接在-p后面写在命令行中
```
mysql -h$ip -P$port -u$user -p
```
使用show processlist命令查询当前连接状态

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数wait_timeout控制的，默认值是8小时。

建立连接的过程通常是比较复杂的，尽量减少建立连接的动作，使用长连接。MySQL在执行过程中使用的内存是管理在连接对象里的，只有在连接断开的时候才会释放。所以长连接容易导致内存问题。

如何解决长连接导致的内存问题：
- 定期断开长连接
- 对于MySQL 5.7或更新的版本，在执行较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
查询缓存

MySQL拿到一个查询请求后，先会在缓存中查找，找不到就会继续后面的执行阶段。执行完成后，结果会存入到查询缓存中。

由于对表的更新操作会导致缓存清空，对于更新压力大的数据库，查询缓存的命令率比较低。对于静态表，不更新或者查询很多更新很少的表，才适合使用缓存查询。MySQL也提供了这种“按需使用”的方式。你可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显式指定select SQL_CACHE * from T where ID=10；

MySQL 8.0版本已经将查询缓存的整块功能删掉了，之后的版本都没有查询缓存了
分析器

先做"词法分析"，再做"语法分析"，判断输入的SQL是否满足MySQL语法
优化器

在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
执行器

先判断一下你对当前操作的表有没有执行查询的权限。有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
select * from T where ID=10;

比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：
1. 调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

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

重要的日志模块：redo log

redo log是 InnoDB引擎特有的日志。

MySQL里经常说到的WAL技术，WAL的全称是WriteAhead Logging，它的关键点就是先写日志，再写磁盘。

当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。

checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos和checkpoint之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

重要的日志模块：binlog

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

创建表： create table T(ID int primary key, c int);

更新： update T set c=c+1 where ID=2;

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

《MySQL实战45讲》(1-7)笔记

redo log的写入拆成了两个步骤：prepare和 commit,这就是"两阶段提交"。

两阶段提交
- 为什么日志需要“两阶段提交”
  
  由于redo log和binlog是两个独立的逻辑，如果不用两阶段提交，要么就是先写完redo log再写binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。
  
  仍然用前面的update语句来做例子。假设当前ID=2的行，字段c的值是0，再假设执行update语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了crash，会出现什么情况呢？
  1. 先写redo log后写binlog。假设在redo log写完，binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。
    
    但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。
  2. 先写binlog后写redo log。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。
    
    可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。
  简单说，redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。
总结

物理日志redo log和逻辑日志binlog

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

sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数也建议你设置成1，这样可以保证MySQL异常重启之后binlog不丢失。

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

隔离性与隔离级别

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

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

隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。SQL标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。

读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。

可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
- 举例说明这几种隔离级别
  
  假设数据表T中只有一列，其中一行的值为1，下面是按照时间顺序执行两个事务的行为。
```
create table T(c int) engine=InnoDB; 

insert into T(c) values(1)·
```
若隔离级别是“读未提交”，则V1的值就是2。这时候事务B虽然还没有提交，但是结果已经被 A看到了。因此，V2、V3也都是2。

若隔离级别是“读提交”，则V1是1，V2的值是2。事务B的更新在提交后才能被A看到。所以， V3的值也是2。

若隔离级别是“可重复读”，则V1、V2是1，V3是2。之所以V2还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。

若隔离级别是“串行化”，则在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后，事务B才可以继续执行。所以从A的角度看， V1、V2值是1，V3 的值是2。

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

查询当前MySQL隔离级别
```
show variables like 'transaction_isolation';
```
事务隔离的实现

在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

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

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

同时你会发现，即使现在有另外一个事务正在将4改成5，这个事务跟read-viewA、B、C对应的事务是不会冲突的。

回滚日志什么时候删除？

系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的read-view的时候。
为什么建议尽量不要使用长事务？

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

对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库，

事务的启动方式

MySQL的事务启动方式有以下几种：
- 显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是 rollback。
- set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
有些客户端连接框架会默认连接成功后先执行一个set autocommit=0的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。

建议使用set autocommit=1, 通过显式语句的方式来启动事务。

在autocommit为1的情况下，用begin显式启动的事务，如果执行commit则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行begin语句的开销。

在information_schema库的innodb_trx这个表中查询长事务
```
#查找持续时间超过60s的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
```
如何避免长事务
- 从应用开发端来看：
  - 确认是否使用了set autocommit=0,观察general_log
    
    general log 将所有到达MySQL Server的SQL语句记录下来。
    
    一般不会开启开功能，因为log的量会非常庞大。但个别情况下可能会临时的开一会儿general log以供排障使用。
    
    相关参数一共有3：general_log、log_output、general_log_file
    
    show variables like 'general_log'; -- 查看日志是否开启
    
    set global general_log=on; -- 开启日志功能
    
    show variables like 'general_log_file'; -- 看看日志文件保存位置
    
    set global general_log_file='tmp/general.lg'; -- 设置日志文件保存位置
    
    show variables like 'log_output'; -- 看看日志输出类型 table或file
    
    set global log_output='table'; -- 设置输出类型为 table
    
    set global log_output='file'; -- 设置输出类型为file
  - 确认是否有不必要的只读事务，只读事务可以去掉
  - 通过SETMAX_EXECUTION_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。
- 从数据库端来看：
  - 监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill；
  - Percona的pt-kill这个工具
  - 测试阶段要求输出所有的general_log，分析日志行为提前发现问题；
  - MySQL 5.6或者更新版本，把innodb_undo_tablespaces(用于设定创建的undo表空间的个数,在mysql_install_db时初始化后，就再也不能被改动了，修改该值会导致MySQL无法启动)设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

第四节：深入浅出索引（上）

索引常见的三种数据结构：哈希表、有序数组和搜索树。
三种模型的区别：
- 哈希表这种结构适用于只有等值查询的场景。比如Memcached及其他一些NoSQL引擎。
- 有序数组，更新数据的时候往中间插入一个记录就必须得挪动后面所有的记录，成本太高。等值查询和范围查询场景中的性能就都非常优秀。只适用于静态存储引擎。
- 搜索树比较均衡。
InnoDB 的索引模型

在MySQL中，索引是在存储引擎层实现的，所以并没有统一的索引标准，

InnoDB使用了B+树索引模型，每一个索引在InnoDB里面对应一棵B+树。
举例说明：
```
#主键列为ID的表，表中有字段k，并且在k上有索引。

create table T(

id int primary key,

k int not null,

name varchar(16),

index (k))engine=InnoDB;
```
表中R1~R5(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)
从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

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

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

从上面的示例可以看出：
- 如果语句是select *fromTwhere ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
- 如果语句是select *fromTwhere k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID 的值为500，再到ID索引树搜索一次。这个过程称为回表。
基于非主键索引的查询需要多扫描一棵索引树
索引维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。如果新插入的ID值为400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

而更糟的情况是，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。

当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

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

插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

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

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

第五节：深入浅出索引（下）

案例

执行 select *fromTwhere k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？
```
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');
```
这条SQL查询语句的执行流程：
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 fromTwhere k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。

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

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

B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。
```
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
```
索引项是按照索引定义里面出现的字段顺序排序的。

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

在建立联合索引的时候，如何安排索引内的字段顺序?

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

，name字段是比age字段大的，创建一个（name,age)的联合索引和一个(age)的单字段索引。
索引下推
```
#检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”

select * from tuser where name like '张%' and age=10 and ismale=1
```
执行步骤：
- 这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录ID3。
- 当然是判断其他条件是否满足。
  - 在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。
  - MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
  执行流程图(每一个虚线箭头表示回表一次)
  
  在(name,age)索引里面我特意去掉了age的值，这个过程InnoDB并不会去看age的值，只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此，需要回表4次。
  
  InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。在我们的这个例子中，只需要对ID4、ID5这两条记录回表取数据判断，就只需要回表2次。

第六节：全局锁和表锁：给表加个字段怎么有这么多阻碍

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

全局锁

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

全局锁的典型使用场景是，做全库逻辑备份

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

single-transaction方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过FTWRL方法。这往往是DBA要求业务开发人员使用 InnoDB替代MyISAM的原因之一。
全库只读，为什么不使用set global readonly=true的方式呢？
- 一是，在有些系统中，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都不允许，自然也不能访问其他表。
- 元数据锁MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。
在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
- 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
- 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
示例

session A先启动，这时候会对表t加一个MDL读锁。由于session B需要的也是 MDL读锁，因此可以正常执行。

之后session C会被blocked，是因为session A的MDL读锁还没有释放，而session C需要MDL写锁，因此只能被阻塞。

如果只有session C自己被阻塞还没什么关系，但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。前面我们说了，所有对表的增删改查操作都需要先申请MDL读锁，就都被锁住，等于这个表现在完全不可读写了。

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session 再请求的话，这个库的线程很快就会爆满。

事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。
课后问题：

备份一般都会在备库上执行，你在用–single-transaction方法做逻辑备份的过程中，如果主库上的一个小表做了一个DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？
```
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）；

showcreate 是为了拿到表结构(Q4)，然后正式导数据（Q5），回滚到SAVEPOINT sp，在这里的作用是释放 t1的MDL锁（Q6。当然这部分属于“超纲”，上文正文里面都没提到。

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

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

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

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁

从两阶段锁说起

在下面的操作序列中，事务B的update语句执行时会是什么现象呢？假设字段id是表t的主键

这个问题的结论取决于事务A在执行完两条update语句后，持有哪些锁，以及在什么时候释放。

实际上事务B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行。事务A持有的两个记录的行锁，都是在commit的时候才释放的。

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

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
死锁和死锁检测
这时候，事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：
- 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout来设置。
- 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。
在InnoDB中，innodb_lock_wait_timeout的默认值是50s

秒客网

《MySQL实战45讲》(1-7)笔记

《MySQL实战45讲》笔记

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

连接器

查询缓存

分析器

优化器

执行器

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

重要的日志模块：redo log

重要的日志模块：binlog

两阶段提交

总结

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

隔离性与隔离级别

事务隔离的实现

事务的启动方式

如何避免长事务

第四节：深入浅出索引（上）

第五节：深入浅出索引（下）

第六节：全局锁和表锁：给表加个字段怎么有这么多阻碍

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

相关文章

《MySQL实战45讲》(1-7)笔记

《MySQL实战45讲》笔记

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

连接器

查询缓存

分析器

优化器

执行器

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

重要的日志模块：redo log

重要的日志模块：binlog

两阶段提交

总结

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

隔离性与隔离级别

事务隔离的实现

事务的启动方式

如何避免长事务

第四节： 深入浅出索引（上）

第五节： 深入浅出索引（下）

第六节： 全局锁和表锁 ：给表加个字段怎么有这么多阻碍

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

相关文章

第四节：深入浅出索引（上）

第五节：深入浅出索引（下）

第六节：全局锁和表锁：给表加个字段怎么有这么多阻碍

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