数据库

一、基础篇

　　执行一条select语句，期间发生了什么

　　首先，我们需要了解关于Mysql的内部结构，MySQL内部分为两个部分，第一部分是Server层，第二部分是存储引擎，在Server层中，一般负责链接、分析和执行SQL语句，存储引擎层负责数据的存储和提取，在Mysql中默认使用的是innoDB存储引擎，不同的i存储引擎支持的索引类型也不相同，nnodb支持的索引类型是B+树

　　在连接过程中，，需要先经过TCP的三次握手，因为Mysql是基于TCP协议进行传输的，验证完用户名和密码后，连接成功，连接有长连接和短连接之分，长连接是你可以输入多个sql指令才会断开，短连接每输入一个指令就会断开，那么这里存在一个问题，就是说长连接不用了之后可以一直保持连接么？

　　答案肯定是否，在MySQL中定义了一个最大空闲时长，如果超过这个时间，就会自动断开

　　但是，使用长连接后可能会占用内存增多，因为 MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象。那么如何解决这种问题呢？

　　可以采用两种方法：1、定期断开长连接 2、客户端通过mysql_reset_connection()函数来主动重置连接

 

　　关于缓存：

　　　　在之前的版本中，MySQL存在一种查询缓存，也就是会保存key和value对在缓存当中，当再次查询的时候会自动调用，但是这种缓存很鸡肋，每当表被更新的时候就会被删除，所以在8.0版本之后就被删除了

　　解析器对SQL语句进行解析之后，进入执行器执行SQL查询语句阶段

　　在执行过程中，就不得不提索引下推操作，在使用索引下推之前，在查询联合索引时，当查询到第一个索引满足条件后，会进行回表操作，然后交给B+ 树来判断第二个索引是否满足条件，在使用索引下推之后，当查询到第一个索引满足条件后，会交给存储引擎来判断第二个索引是否满足条件，如果满足，再进行回表操作

 

 

二、索引篇

　　什么是索引？

　　　　索引就类似于书的目录，会占用一定的内存，但是会提高查询效率

　　索引的分类：

　　　　按照物理存储分类：聚簇索引（主键索引）、二级索引（辅助索引）

　　　　按照字段特性分类：主键索引（按照主键进行索引）、唯一索引、普通索引（不是主键的字段）、前缀索引（不要求全部的字段）。

　　　　按字段个数分类：单列索引、联合索引

　　　　按照数据结构分类：B+tree索引、Hash索引、Full-text索引

 

　　那么为什么innodb采用B+树作为索引呢，首先相较于B树，B+树只在叶子节点处存储数据，这也就说明了B+树在相同的I/O操作次数下，能查询到更多的节点。另外，B+tree在叶子节点中采用的是双链表连接，适合MySQL中常见的基于范围的顺序查找，B树无法做到这点。相较于Hash表来说，B+树更适合范围查找，应用的场景更多，而hash更适合等值查找

　　在建立联合索引的时候，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的SQL使用到，区分度就是某个字段column不同值得个数除以表得总行数

　　

　　什么时候需要索引？

　　　　1、字段具有唯一性限制得，比如商品编码 　　2、经常用于where查询条件得字段　　3、经常用于group by和 order by得字段　

　　什么时候不需要要创建索引？

　　　　1、WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。　　2、字段中存在大量重复数据，不需要创建索引　　3、表内数据太少　　4、经常更新得字段

　　

　　关于索引失效

　　　　　　

　　总结：

　　　　　　　　

 

三、事务篇

　　为了防止在修改数据库中途出现意外，比如说有转账操作，你这边已经扣了100万，但是突然服务器停电了，他那边还没增加100万，这就会导致你非常伤心

　　所以，为了防止出现这种情况，数据库提出事务，我们在转账之前先开启事务，等所有数据库操作执行完成后，才提交事务，对于已经提交得事务来说，该事务如果中途发生中断或者错误，那么该事务期间对数据库所做的修改会被回滚到执行该事务之前的状态

　　事务有哪些特性？

　　事务是由MySQL的引擎来实现的，我们常见的innoDB引擎支持事务操作，但是并不是所有的存储引擎都支持事务操作

　　事务的特性主要有四个：1、原子性：要么全部完成，要么全部不完成  （通过undo logo来实现回滚功能）

　　　　　　　　　　　　　2、一致性：事务前和事务后数据库保持一致性状态    （通过持久性、隔离性和原子性来保证）

　　　　　　　　　　　　　3、隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致　　（通过MVCC（多版本并发控制）或锁机制来保证）

　　　　　　　　　　　　　4、持久性：事务处理结束后，对数据的修改就是永久的，即使系统故障也不会丢失　　（通过redo log（重做日志）来保证）

　　

 　　并行事务会发生什么问题？

　　　　Mysql服务端运行多个客户端连接的，这意味着MySQL会同时处理多个事务的情况，那么在同时处理多个事务的时候，就可能会出现脏读、不可重复读、幻读的问题

　　　　　　脏读：其实就是在读的时候，上一个修改操作还没保存，所以出现读错误

　　　　　　不可重复读：就是事务A在读取余额之后，进行代码逻辑处理，这时候，事务B也读取了余额，并进行了更新，导致A再次读取该数据时，发现前后两次读到的数据不一致

　　　　　　幻读：在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。也是由于多个事务并行操作导致的数据读取不一致

　　　　　　

 　　　　严重程度排序：脏读>不可重复读>幻读

 

　　事务的隔离等级有哪些

　　　　刚才我们提到多个事务并发执行的时候，会出现脏读、不可重复读、幻读的现象，这些现象会对事务的一致性产生不同程度的影响，为此，SQL标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，这四个隔离级别如下：

　　　　读未提交：指一个事务还没提交时，它做的变更就能被其他事务看到

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

　　　　可重复读：指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的 Innodb引擎的默认隔离级别，事务只能看到启动事务时的数据

　　　　串行化：会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后一个事务必须等前一个事务执行完成，才能继续执行

　　　　

　　　　

 

　　　　　　MySQL 在「可重复读」隔离级别下，可以很大程度上避免幻读现象的发生（注意是很大程度避免，并不是彻底避免），所以 MySQL 并不会使用「串行化」隔离级别来避免幻读现象的发生，因为使用「串行化」隔离级别会影响性能。

 　　　　

• • • 　　　　针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
    • 　　　　针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

　　　　　　对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。

　　　　　　「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

 　　　　注意：执行开始事务命令，并不意味着启动了事务，在MySQL有两种开启事务的命令，分别是1、begin/start transaction 命令；　　2、start transaction with consistent snapshot 命令；

　　　　　　第一种是开启之后，等执行第一条select语句才代表事务真正启动　　第二个执行命令之后就算启动事务了

　　　　

　　　　

　　Read View 在 MVCC 里如何工作的？

　　　　首先我们要知道Read View的结构是什么样子的

　　　　　　Read View有四个重要的字段

　　　　　

• m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
• min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。
• max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；
• creator_trx_id ：指的是创建该 Read View 的事务的事务 id。

　　知道了Read View的字段，我们还需要知道两个隐藏列

• trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
• roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

  

   在创建Read View后，我们可以将记录中的ti_id划分这三种情况

  

   一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

• 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
• 如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
• 如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：
  • 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
  • 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

　　　　

　　如果事务在读取数据的时候，发现有个事务正在处理这个数据，但是还没有提交，这时候该事务就会通过roll_pointer进行读取旧版本的数据

　　

　　总结：　

　　事务是在 MySQL 引擎层实现的，我们常见的 InnoDB 引擎是支持事务的，事务的四大特性是原子性、一致性、隔离性、持久性，我们这次主要讲的是隔离性。

当多个事务并发执行的时候，会引发脏读、不可重复读、幻读这些问题，那为了避免这些问题，SQL 提出了四种隔离级别，分别是读未提交、读已提交、可重复读、串行化，从左往右隔离级别顺序递增，隔离级别越高，意味着性能越差，InnoDB 引擎的默认隔离级别是可重复读。

要解决脏读现象，就要将隔离级别升级到读已提交以上的隔离级别，要解决不可重复读现象，就要将隔离级别升级到可重复读以上的隔离级别。

而对于幻读现象，不建议将隔离级别升级为串行化，因为这会导致数据库并发时性能很差。MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了，详见这篇文章 (opens new window)），解决的方案有两种：

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同：

• 「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
• 「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列」的比对，来控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。

在可重复读隔离级别中，普通的 select 语句就是基于 MVCC 实现的快照读，也就是不会加锁的。而 select .. for update 语句就不是快照读了，而是当前读了，也就是每次读都是拿到最新版本的数据，但是它会对读到的记录加上 next-key lock 锁。

我举例了两个在使用了可重复读后发生幻读场景的例子。

第一个例子：对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。

第二个例子：对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。

所以，MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select ... for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

 

 锁篇：

　　全局锁应该怎么用？

　　　　要使用全局锁，则要执行这条命令：flush tables with read lock

　　　　使用这条命令之后，整个数据库就处于只读状态了，任何的写命令都会被阻塞（增删改查）

　　　　要始放全局锁，则要执行这条命令 unlock tables，当然要是防护全局锁，则要执行这条命令

　　　　　　当然，当会话断开了，全局锁会被自动锁定

　　全局锁的应用场景是什么？

　　　　全局锁主要应用于做全库逻辑备份没这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样，比如说，如果先备份了用户表的数据，但是在这时候，用户购买了一件商品，但是这样的话，在备份表内用户的余额并不会改变，这时候，再去备份商品表的话，就相当于用户白嫖了一件商品

　　

　　那么全局锁优惠带来什么缺点呢

　　　　加上全局锁，就意味着整个数据库都是只读状态。那么如果数据库中有很多数据，备份就会花费很多的时间，关键是在备份的时候，业务只能读数据，而不能更新数据，这样就会导致业务停滞。所以，我们要避免这种情况的话，我们可以使用数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建read view，然后整个事务执行期间都在用这个read view，而且由于MVCC的支持，备份期间业务依然可以对数据进行更新操作。

　　但是这样的话就要求引擎能够支持这种事务，比如说MyISAM，像这种在备份数据库时就要使用全局锁的方法

　　

　　表级锁：表锁、元数据锁、意向锁、AUTO-INC锁

　　表锁：

　　　　

　　如果实行了表锁，那么如果对表进行写操作，就会导致阻塞，要释放表锁，可以使用下面的这条命令，会释放当前会话的所有表锁：unlock tables

另外，当会话退出后，也会释放所有表锁。

不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

　　

　　元数据锁（MDL）：

 　　　　我们不需要显示的使用MDL，因为当我们对数据库表进行操作时，会自动给这个表加上MDL：

　　　　　　对一张表进行CRUD（create、read\update\delete)操作时，加的是MDL读锁

　　　　　　对一张表做结构变更操作的时候，加的是MDL写锁

　　　　当有线程在执行select语句（加MDL读锁）的期间，如果有其他线程要更改该表的结构，那么就会被阻塞，知道执行完select语句（释放MDL读锁），反之亦然

　　　　MDL不需要显示调用，那它什么时候释放

　　　　　　MDL是事务提交之后才会释放，这意味着事务执行期间，MDL是一直持有的

　　　　那么这时候就有一个问题：就是当线程A启用了事务，然后执行了一条select语句，MDL读锁被启用，然后这时候线程B修改了该表的字段，理所当然会被阻塞，若是这时候B再执行select命令，也会被阻塞，这就容易导致数据库的线程爆满

　　　　为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？　　

　　　　　　这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

　　　　　　所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

　　

　　意向锁：

　　　　在使用innodb引擎的表里对某些记录加上共享锁之前，先在表级别加上一个意向共享锁

　　　　同理，在使用innodb引擎的表里对某些记录加上独占锁之前，需要先在表级别加上一个意向独占锁

　　　　也就是说，在执行插入、更新、删除操作，需要先对表加上意向独占锁，然后对该记录加上独占锁

　　　　而普通的查询是不会加上行级锁的，普通的select语句是利用MVCC实现不一致性读，是无锁的

　　　　　　MVCC：通过read view读取备份记录，然后根据train_id\min_id\max_id进行比较，判断修改在　　　　　　　　之前还是之后以及是否需要查询

　　　　　　

　　　　意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不　　　　　　　　　　　　会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。

 　　　　

　　　　如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

　　　　那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

　　　　所以，意向锁的目的是为了快速判断表里是否有记录被加锁

　　

　　AUTO-INC 锁

　　　　表里的主键通常都是设置成自增的，这是通过主键字段声明AUTO_INCREMENT 属性实现的。之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。　　

　　　　AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

　　　　在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

 

　　　　所以，在AUTO-INC运行期间，若其他事务要向该表插入语句都会被阻塞，从而保证插入数据时，被AUTO_INCREMENT 修饰的字段的值是连续递增的。

但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

　　　　　

　　行级锁：　　　

　　　　InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

　　　　前面也提到，普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读。

 　　

　　　Record Lock

　　　　　　又称为记录锁，有S锁和X锁之分

　　　　　　

　　　　　　

　　　　　　

 

 　　　　插入意向锁：

　　　　　　一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加上了间隙锁，如果有的话，插入操作就会被阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新纪录，但是现在处于等待状态

　　　　插入意向锁虽然有意向锁，但是他并不是意向锁，他是一种特殊的间隙锁，属于行级别锁

　　　　如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

　　　　插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。

 

　Mysql是怎么加锁的

　　什么SQL语句会加行级锁？

　　　　innoDB引擎支持行级锁，但是MyISAM不支持行级锁

　　　　普通的select语句是不会对记录加锁的，因为它属于快照读，是通过MVCC（多版本并发控制）实现的，如果要在查询时对记录加行级锁，可以使用下面这两个方式，这两种查询会加锁的语句称为锁定读

　　　　　　

 　　首先我们要保证上锁的语句是在同一个事务当中，否则，上锁就上了个寂寞，因为当事务提交的时候，锁也会被释放，我们应该用begin或者start transaction开启事务

　　除了上面两条语句进行加锁外，update和delete操作都会加行级锁，且锁的类型都是独占锁

　　　　　　

　　共享锁满足读读共享、读写互斥，独占锁满足读写互斥，写写互斥

 

行级锁有哪些种类？

　　不同隔离级别下，行级锁的种类是不同的。在读已提交隔离级别下，行级锁的种类只有记录锁（Record lock）

　　在可重复度隔离级别下，行级锁的种类除了有记录锁，还有间隙锁（避免幻读），所以行级锁的种类有三种：

　　　　1、Record Lock（记录锁）：有S锁和X锁之分，S锁和S锁可以同时对同一个记录使用，但是不能和X锁同时使用，X锁不可以和任何锁同时使用

　　　　2、Gap Lock（间隙锁）：锁住一个间隙（比如说（3，5）就相当于锁住了id=4的记录），防止幻读

　　　　3、Next-Key Lock Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改和删除 id = 5 这条记录。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。

 

　　Mysql是怎么加行级锁的？

　　　　行级锁加锁规则比较复杂，不同的场景下，加锁的形式是不同的

　　　　加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。

 　　　　那么在莫i写特殊情况下next-key lock会不会退化成记录锁或者是间隙锁呢

　　　　　　会的，如果说对唯一索引进行查询，且记录存在的情况下，next-key lock会退化成记录锁，因为在唯一索引情况下，仅靠记录锁就能避免幻读，幻读的定义就是，当一个事务前后两次查询的结果集，不相同时，就认为发生幻读。所以，要避免幻读就是避免结果集某一条记录被其他事务删除，或者有其他事务插入了一条新记录，这样前后两次查询的结果集就不会出现不相同的情况。

 　　　　　　若是记录不存在，next-key lock会退化成间隙锁，因为我们只需要将未查询到的左右两边的id作为边界，就可以将这一块的查询结果锁住，不会出现幻读情况

 

　　在唯一锁的范围查询中，当大于或者小于号的范围内，假如说是查询>15的id值，若是不存在，则上一个（15，+∞】的next-key lock，若存在一个20，则加一个（15，20】的next-key lock和一个（20，+∞】的next-key lock

　　大于等于或者是小于等于的情况的时候，就是对等于的边界值上一个记录锁

　　

　　非唯一索引等值查询

　　　　当我们用非唯一索引等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，同时会对这两个索引都加锁，但是对主键加锁的时候，只有满足查询条件的记录才能对他们的主键索引加锁

　　　　所以，当查询的记录存在时，由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。

　　　　当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

我们也可以通过 select * from performance_schema.data_locks\G; 这条语句来看看事务 A 加了什么锁。

我们要知道，在某些情况下（22，39）的间隙锁，可能会出现id=22和id=39成功插入 的情况出现，所以我们首先要清楚什么情况下会发生阻塞，

　　插入语句在插入一条记录之前，需要先定位到B+树的位置，如果插入的位置的下一条记录的索引有间隙锁，才会发生阻塞，在分析二级索引的间隙锁是否可以成功插入记录时，我们首先要知道二级索引树是如何存放记录的

　　二级索引树是按照二级索引值按顺序存放的，在相同的二级索引值情况下，再按照主键id的顺序存放，知道了这个前提，我们才能知道执行插入语句的时候，插入的位置的下一条记录时谁

　　所以，出现成功的情况的时候，那么可能就是因为age虽然相同，但是它的下一个可能也是相同age的情况，所以插入成功，反之，不成功

插入 age = 22 记录的成功和失败的情况分别如下：

• 当其他事务插入一条 age = 22，id = 3 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条是 id = 10、age = 22 的记录，该记录的二级索引上没有间隙锁，所以这条插入语句可以执行成功。
• 当其他事务插入一条 age = 22，id = 12 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条是 id = 20、age = 39 的记录，正好该记录的二级索引上有间隙锁，所以这条插入语句会被阻塞，无法插入成功。

插入 age = 39 记录的成功和失败的情况分别如下：

• 当其他事务插入一条 age = 39，id = 3 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条是 id = 20、age = 39 的记录，正好该记录的二级索引上有间隙锁，所以这条插入语句会被阻塞，无法插入成功。

• 当其他事务插入一条 age = 39，id = 21 的记录的时候，在二级索引树上定位到插入的位置，而该位置的下一条记录不存在，也就没有间隙锁了，所以这条插入语句可以插入成功。

所以，当有一个事务持有二级索引的间隙锁 (22, 39) 时，插入 age = 22 或者 age = 39 记录的语句是否可以执行成功，关键还要考虑插入记录的主键值，因为「二级索引值（age列）+主键值（id列）」才可以确定插入的位置，确定了插入位置后，就要看插入的位置的下一条记录是否有间隙锁，如果有间隙锁，就会发生阻塞，如果没有间隙锁，则可以插入成功。

 

所以，之前说的「事务 A 在 age = 39 记录的二级索引上（INDEX_NAME: index_age ），加了范围为 (22, 39) 的 X 型间隙锁」这个说法是不准确的，因为只考虑了LOCK_DATA 第一个数值（39），没有考虑 LOCK_DATA 第二个数值（20）。，第一个数值是age值，第二个数值是id值

 　　　　当查询的记录存在的情况：

事务 A 加锁变化过程如下：

• 由于不是唯一索引，所以肯定存在值相同的记录，于是非唯一索引等值查询的过程是一个扫描的过程，最开始要找的第一行是 age = 22，于是对该二级索引记录加上范围为 (21, 22] 的 next-key 锁。同时，因为 age = 22 符合查询条件，于是对 age = 22 的记录的主键索引加上记录锁，即对 id = 10 这一行加记录锁。
• 接着继续扫描，扫描到的第二行是 age = 39，该记录是第一个不符合条件的二级索引记录，所以该二级索引的 next-key 锁会退化成间隙锁，范围是 (22, 39)。
• 停止查询。

　　其实总结来说，就是查询到的主键进行记录锁，当查询到的第一个不符合条件的二级索引记录，这时候，该二级索引的next-key lock会退化成间隙锁

非唯一索引范围查询

　　非唯一索引范围查询的时候，假如说要查询age>=22的记录，查询到的每一个范围内的记录都成为一个next-key lock，同时对主键进行记录锁，直到最后一个记录，设置一个标记

事务 A 的加锁变化：

• 最开始要找的第一行是 age = 22，虽然范围查询语句包含等值查询，但是这里不是唯一索引范围查询，所以是不会发生退化锁的现象，因此对该二级索引记录加 next-key 锁，范围是 (21, 22]。同时，对 age = 22 这条记录的主键索引加记录锁，即对 id = 10 这一行记录的主键索引加记录锁。
• 由于是范围查询，接着继续扫描已经存在的二级索引记录。扫面的第二行是 age = 39 的二级索引记录，于是对该二级索引记录加 next-key 锁，范围是 (22, 39]，同时，对 age = 39 这条记录的主键索引加记录锁，即对 id = 20 这一行记录的主键索引加记录锁。
• 虽然我们看见表中最后一条二级索引记录是 age = 39 的记录，但是实际在 Innodb 存储引擎中，会用一个特殊的记录来标识最后一条记录，该特殊的记录的名字叫 supremum pseudo-record ，所以扫描第二行的时候，也就扫描到了这个特殊记录的时候，会对该二级索引记录加的是范围为 (39, +∞] 的 next-key 锁。
• 停止查询

 

 

如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

　　　　

update 没加索引会锁全表

　　为什么会发生这种的事故？

 　　　　因为如果where后面不加索引的话，其实就相当于全表扫描，就相当于对全表上了一个next-key lock ，直到事务结束，这个锁才会释放

　　但是我们如果where后面加上索引，有时候也不能避免这种情况出现，我们可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。

大致的意思是，当 sql_safe_updates 设置为 1 时。

update 语句必须满足如下条件之一才能执行成功：

• 使用 where，并且 where 条件中必须有索引列；
• 使用 limit；
• 同时使用 where 和 limit，此时 where 条件中可以没有索引列；

delete 语句必须满足以下条件能执行成功：

• 同时使用 where 和 limit，此时 where 条件中可以没有索引列；

如果 where 条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引的话，我们可以使用 force index([index_name]) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。

　　　　

 

MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读吗？

　　　　

在 MySQL 的可重复读隔离级别下，针对当前读的语句会对索引加记录锁+间隙锁，这样可以避免其他事务执行增、删、改时导致幻读的问题。

有一点要注意的是，在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

MySQL 死锁了，怎么办？

　　为什么会发生死锁，我们可以举一个例子，假如说，事务A要向表中插入id为1007的订单，事务B要向表中插入id为1008的订单，两者同时开始，在查询对应的订单是否存在时，都申请了锁，那么两者相互等待对方释放锁，也就是出现了死锁的现象

那么这时候如果不用select……for update，而是用select，那么可能会出现两个相同id的订单，出现幻读

 

Insert 语句是怎么加行级锁的？

Insert 语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。

隐式锁就是在 Insert 过程中不加锁，只有在特殊情况下，才会将隐式锁转换为显示锁，这里我们列举两个场景。

• 如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的；
• 如果 Insert 的记录和已有记录存在唯一键冲突，此时也不能插入记录；

1、记录之间加有间隙锁

插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），现象就是 Insert 语句会被阻塞。（所以并不是加上了锁，只是产生了一个要加锁的意向）

2、遇到唯一键冲突

如果在插入新记录时，插入了一个与「已有的记录的主键或者唯一二级索引列值相同」的记录（不过可以有多条记录的唯一二级索引列的值同时为NULL，这里不考虑这种情况），此时插入就会失败，然后对于这条记录加上了 S 型的锁。

• 如果主键索引重复，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁。

• 如果唯一二级索引重复，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加 S 型 next-key 锁。

 

如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

　　在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

　　　　1、设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

　　　　2、开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

 

 

间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，共享（S型）和排他（X型）的间隙锁是没有区别的，他们相互不冲突，且功能相同。

两个事务即使生成的间隙锁的范围是一样的，也不会发生冲突，因为间隙锁目的是为了防止其他事务插入数据，因此间隙锁与间隙锁之间是相互兼容的。

在执行插入语句时，如果插入的记录在其他事务持有间隙锁范围内，插入语句就会被阻塞，因为插入语句在碰到间隙锁时，会生成一个插入意向锁，然后插入意向锁和间隙锁之间是互斥的关系。

如果两个事务分别向对方持有的间隙锁范围内插入一条记录，而插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁。