数据库

标签：事务 记录 Read 数据库 索引 id View

一、基础篇

　　执行一条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锁之分

　　　　　　

　　　　　　

　　　　　　

 

 　　　　插入意向锁：

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

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

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

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

 

　　

 

　　　　

　　　　

标签：事务,记录,Read,数据库,索引,id,View
From： https://www.cnblogs.com/guanyifan/p/18116283