MySQL 进阶篇
MySQL 进阶篇
一、进阶篇-存储引擎
1.1 MySQL体系结构
- 连接层
最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
- 服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
- 引擎层
存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。
- 存储层
数据存储层， 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。
1.2 MySQL存储引擎介绍
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。MySQL5.5之前的默认存储引擎是MyISAM，5.5之后改为了InnoDB。
查询当前数据库支持的存储引擎
show engines;
创建表时指定存储引擎
CREATE TABLE name(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;
1.3 存储引擎的特点
常用存储引擎的对比
| 特点 | MyISAM | InnoDB | MEMORY | MERGE | NDB |
|---|---|---|---|---|---|
| 存储限制 | 有 | 64TB | 有 | 没有 | 有 |
| 事务安全 | 支持 | ||||
| 锁机制 | 表锁 | 行锁 | 表锁 | 表锁 | 行锁 |
| B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | 支持 | 支持 | |||
| 全文索引 | 支持 | ||||
| 集群索引 | 支持 | ||||
| 数据缓存 | 支持 | 支持 | 支持 | ||
| 索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 数据可压缩 | 支持 | ||||
| 空间使用 | 低 | 高 | N/A | 低 | 低 |
| 内存使用 | 低 | 高 | 中登 | 低 | 高 |
| 批量插入的速度 | 高 | 低 | 高 | 高 | 高 |
| 支持外键 | 支持 |
1.3.1 InnoDB
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的MySQL 存储引擎。
1.3.1.1 特点
DML操作遵循ACID模型，支持事务；
行级锁，提高并发访问性能；
支持外键FOREIGN KEY约束，保证数据的完整性和正确性；
auto_increment 自增列
1.3.1.2 AUTO_INCREMENT
自增的值默认从1开始，可以手动指定该值，但指定的值是空或者0，则执行自增的逻辑。
可以修改自增的初始值ALERT TABLE *** AUTO_INCREMENT = n，但是这个值是保存在内存中的。重启后会失效。
在InnoDB类型的表中，自增列必须是索引列，如果是组合索引，也必须是组合索引的第一列。
1.3.1.3 存储方式
xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。
参数：innodb_file_per_table
show variables like 'innodb_file_per_table';
如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。 我们直接打开MySQL的数据存放目录： C:\ProgramData\MySQL\MySQL Server 8.0\Data， 这个目录下有很多文件夹，不同的文件夹代表不同的数据库，我们直接打开itcast文件夹。
可以看到里面有很多的ibd文件，每一个ibd文件就对应一张表，比如：我们有一张表 account，就有这样的一个account.ibd文件，而在这个ibd文件中不仅存放表结构、数据，还会存放该表对应的索引信息。 而该文件是基于二进制存储的，不能直接基于记事本打开，我们可以使用mysql提供的一个指令 ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表的表结构。
数据存储逻辑图
表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。
段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。
页 : 页是组成区的最小单元，页也是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段
1.3.1.4 外键约束
1.3.2 MyISAM
MyISAM是MySQL早期的默认存储引擎。
1.3.2.1 特点
不支持事务，不支持外键。支持表锁，不支持行锁
访问速度快，对事物没有完整性没有要求的可以使用这个引擎来创建表。
1.3.2.2 存储文件结构
每个MyISAM引擎类型的表在磁盘上存储成3个文件，全称如下：
xxx.sdi：存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引
1.3.3 Memory
Memory引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。
特点
数据在内存中存放，默认使用hash索引，访问非常快。服务关闭后数据丢失。
文件
xxx.sdi：存储表结构信息
1.3.4 区别及特点
1.4 存储引擎的选择
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。
InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。
二、进阶篇-索引
2.1 索引概述
2.1.1 介绍
索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
2.1.2 演示
表结构及其数据如下：
假如我们要执行的SQL语句为 ： select * from user where age = 45;
1). 无索引情况
在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。
2). 有索引情况
如果我们针对于这张表建立了索引，假设索引结构就是二叉树，那么也就意味着，会对age这个字段建立一个二叉树的索引结构。
此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。
2.1.3 特点
| 优势 | 劣势 |
|---|---|
| 提高数据检索的效率，降低数据库的IO成本 | 索引列也是要占用空间的。 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。 | 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。 |
2.2 索引结构
2.2.1 概述
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：
| 索引结构 | 描述 |
|---|---|
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持 B+ 树索引 |
| Hash索引 | 底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询 |
| R-tree(空间索引） | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES |
上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持情况。
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-tree索引 | 不支持 | 支持 | 不支持 |
| Full-text | 5.6版本之后支持 | 支持 | 不支持 |
2.2.2 二叉树
假如说MySQL的索引结构采用二叉树的数据结构，比较理想的结构如下：
如果主键是顺序插入的，则会形成一个单向链表，结构如下：
所以，如果选择二叉树作为索引结构，会存在以下缺点：
顺序插入时，会形成一个链表，查询性能大大降低。
大数据量情况下，层级较深，检索速度慢。
此时大家可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数据，最终形成的数据结构也是一颗平衡的二叉树,结构如下:
但是，即使如此，由于红黑树也是一颗二叉树，所以也会存在一个缺点：
- 大数据量情况下，层级较深，检索速度慢。
所以，在MySQL的索引结构中，并没有选择二叉树或者红黑树，而选择的是B+Tree，那么什么是B+Tree呢？在详解B+Tree之前，先来介绍一个B-Tree。
2.2.3 B-Tree
B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针：
特点：
5阶的B树，每一个节点最多存储4个key，对应5个指针。
一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
在B树中，非叶子节点和叶子节点都会存放数据。
我们可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~galles/visualization/BTree.html
2.2.4 B+Tree
B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一下其结构示意图：
我们可以看到，两部分：
绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。
最终我们看到，B+Tree 与 B-Tree相比，主要有以下三点区别：
所有的数据都会出现在叶子节点。
叶子节点形成一个单向链表。
非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。
上述我们所看到的结构是标准的B+Tree的数据结构，接下来，我们再来看看MySQL中优化之后的B+Tree。
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。
2.2.5 Hash
MySQL中除了支持B+Tree索引，还支持一种索引类型---Hash索引。
【1】结构
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。
【2】特点
Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）
无法利用索引完成排序操作
查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引
【3】存储引擎支持
在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。
思考题： 为什么InnoDB存储引擎选择使用B+tree索引结构?
相对于二叉树，层级更少，搜索效率高；
对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
相对Hash索引，B+tree支持范围匹配及排序操作；
2.3 索引分类
2.3.1 索引的分类
在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引（不允许为null） | 默认自动创建, 只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复（可为null） | 可以有多个索引 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
2.3.2 聚集索引&二级索引
而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
| 分类 | 含有 | 特点 |
|---|---|---|
| 聚集索引(Clustered Index) | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
| 二级索引(Secondary Index) | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
- 如果存在主键，则优先将主键索引作为聚集索引。
- 如果不存在主键，将使用第一个不允许为null的唯一索引(UNIQUE)作为聚集索引。
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
聚集索引和二级索引的具体结构如下：
聚集索引的叶子节点下挂的是这一行的数据 。
二级索引的叶子节点下挂的是该字段值对应的主键值
接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。
具体过程如下:
①. 由于是根据name字段进行查询，所以先根据name='Arm'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
③. 最终拿到这一行的数据，直接返回即可。
回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。
思考题：
以下两条SQL语句，那个执行效率高? 为什么?
A. select * from user where id = 10 ;
B. select * from user where name = 'Arm' ;
备注: id为主键，name字段创建的有索引；
解答：
A 语句的执行性能要高于B 语句。因为A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。
思考题：
InnoDB主键索引的B+tree高度为多高呢?
假设:
一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。
高度为2：
n * 8 + (n + 1) * 6 = 16*1024 , 算出n约为 1170
1171* 16 = 18736
也就是说，如果树的高度为2，则可以存储 18000 多条记录。
高度为3：
1171 * 1171 * 16 = 21939856
也就是说，如果树的高度为3，则可以存储 2200w 左右的记录。
2.4 索引语法
创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;
查看索引
SHOW INDEX FROM table_name ;
删除索引
DROP INDEX index_name ON table_name ;
2.5 SQL性能分析
2.5.1 SQL执行频率
MySQL 客户端连接成功后，通过 show [session | global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：
-- session 是查看当前会话,默认是此级别;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_%';
Com_delete: 删除次数
Com_insert: 插入次数
Com_select: 查询次数
Com_update: 更新次数
通过下面两个参数可以了解事物的提交和回滚情况：
- Com_commit: 事物提交次数
- Com_rollback: 事物回滚次数
下面还有几个针对InnoDB存储引擎的参数可以参考：
- Innodb_row_read: Select查询返回的行数
- Innodb_rows_inserted: 执行INSERT操作插入的行数。
- Innodb_rows_updated: 执行UPDATE操作更新的行数
- Innodb_rows_deleted: 执行DELETE操作删除的行数
通过这几个操作可以很容易了解当前数据库是以插入更新为主，还是以查询操作为主。从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以查询为主，那么就要考虑对数据库的索引进行优化了。
- Connections： 试图连接MySQL服务器的次数
- Uptime： 服务器的工作时间
- Slow_queries: 慢查询的次数。
那么通过查询SQL的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。 那假如说是以查询为主，我们又该如何定位针对于那些查询语句进行优化呢？ 次数我们可以借助于慢查询日志。
接下来，我们就来介绍一下MySQL中的慢查询日志。
2.5.2 慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。
如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。
systemctl restart mysqld
然后，再次查看开关情况，慢查询日志就已经打开了。
测试
执行如下SQL语句 ：
select * from tb_user; -- 这条SQL执行效率比较高, 执行耗时 0.00sec
select count(*) from tb_sku; -- 由于tb_sku表中, 预先存入了1000w的记录, count一次,耗时 13.35sec
检查慢查询日志
最终我们发现，在慢查询日志中，只会记录执行时间超多我们预设时间（2s）的SQL，执行较快的SQL是不会记录的。那这样，通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化。
2.5.3 profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
SELECT @@have_profiling ;
可以看到，当前MySQL是支持 profile操作的，但是开关是关闭的。可以通过set语句在session/global级别开启profiling：
SET profiling = 1;
开关已经打开了，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。 我们直接执行如下的SQL语句：
select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
select count(*) from tb_sku;
执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
查看每一条SQL的耗时情况:
查看指定SQL各个阶段的耗时情况 :
MySQL支持进一步选择all、cpu、block io、context、switch、page faults等明细类型来查看MySQL在使用什么资源上耗费了过高的时间。
2.5.4 explain 执行计划
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法:
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
Explain 执行计划中各个字段的含义:
| 字段 | 含义 |
|---|---|
| id | select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。 |
| select_type | 表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、 PRIMARY（主查询，即外层的查询）、 UNION（UNION 中的第二个或者后面的查询语句）、 SUBQUERY（SELECT/WHERE之后包含了子查询）等 |
| table | 输出结果集的表 |
| type | 表示MySQL在表中找到所需行的方式，或者叫访问类型，常见类型如下： type=ALL： 全表扫描，Mysql遍历全表来找到匹配行 type=index：索引全扫描，Mysql遍历整个索引来查询匹配的行， type=range：索引范围扫描，常见于<，<=，>，>=，between等操作。 type=ref：使用非唯一索引(不是UNIQUE类型的索引)扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行。 type=eq_ref：使用唯一索引扫描（例如：锁表连接中使用primary key或者unique key作为关联条件） type=const/system：单表中只有一行匹配，查询非常迅速，例如通过primary key 或者 unique key 进行查询。 type=NULL：MySQL不用访问索引，就能获取结果。 |
| possible_key | 表示查询是可能使用的索引 |
| key | 实际使用的索引，如果为NULL，则没有使用索引。 |
| key_len | 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。 |
| rows | MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。 |
| filtered | 表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。 |
2.6 索引使用
2.6.1 最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。
以 tb_user 表为例，我们先来查看一下之前 tb_user 表所创建的索引。
在 tb_user 表中，有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession，age，status。
对于最左前缀法则指的是，查询时，最左边的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。 接下来，我们来演示几组案例，看一下
具体的执行计划：
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '软件工程' and age = 31;
explain select * from tb_user where profession = '软件工程';
以上的这三组测试中，我们发现只要联合索引最左边的字段 profession存在，索引就会生效，只不过索引的长度不同。 而且由以上三组测试，我们也可以推测出profession字段索引长度为47、age字段索引长度为2、status字段索引长度为5。
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';
而通过上面的这两组测试，我们也可以看到索引并未生效，原因是因为不满足最左前缀法则，联合索引最左边的列profession不存在。
explain select * from tb_user where profession = '软件工程' and status = '0';
上述的SQL查询时，存在profession字段，最左边的列是存在的，索引满足最左前缀法则的基本条件。但是查询时，跳过了age这个列，所以后面的列索引是不会使用的，也就是索引部分生效，所以索引的长度就是47。
思考题：
当执行SQL语句: explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程'； 时，是否满足最左前缀法则，走不走上述的联合索引，索引长度？
可以看到，是完全满足最左前缀法则的，索引长度54，联合索引是生效的。
注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。
2.6.2 范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';
当范围查询使用> 或 < 时，走联合索引了，但是索引的长度为49，就说明范围查询右边的status字段是没有走索引的。
explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';
所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 < 。
2.6.3 索引失效情况
2.6.3.1 索引列运算
不要在索引列上进行运算操作， 索引将失效。
在tb_user表中，除了前面介绍的联合索引之外，还有一个索引，是phone字段的单列索引。
当根据phone字段进行等值匹配查询时, 索引生效。
explain select * from tb_user where phone = '17799990015';
当根据phone字段进行函数运算操作之后，索引失效。
explain select * from tb_user where substring(phone,10,2) = '15';
2.6.3.2 字符串不加引号
字符串类型字段使用时，不加引号，索引将失效。
接下来，我们通过两组示例，来看看对于字符串类型的字段，加单引号与不加单引号的区别：
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '软件工程' and age = 31 and status = 0;
explain select * from tb_user where phone = '17799990015';
explain select * from tb_user where phone = 17799990015;
经过上面两组示例，我们会明显的发现，如果字符串不加单引号，对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效。
2.6.3.3 模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
接下来，我们来看一下这三条SQL语句的执行效果，查看一下其执行计划：
由于下面查询语句中，都是根据profession字段查询，符合最左前缀法则，联合索引是可以生效的，我们主要看一下，模糊查询时，%加在关键字之前，和加在关键字之后的影响。
explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';
经过上述的测试，我们发现，在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字前面加了%，索引将会失效。
2.6.3.4 or连接条件
用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;
由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。
然后，我们可以对age字段建立索引。
create index idx_user_age on tb_user(age);
建立了索引之后，我们再次执行上述的SQL语句，看看前后执行计划的变化。
最终，我们发现，当or连接的条件，左右两侧字段都有索引时，索引才会生效。
2.6.3.5 数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。
select * from tb_user where phone >= '17799990005';
select * from tb_user where phone >= '17799990015';
经过测试我们发现，相同的SQL语句，只是传入的字段值不同，最终的执行计划也完全不一样，这是为什么呢？
就是因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。
接下来，我们再来看看 is null 与 is not null 操作是否走索引。
执行如下两条语句 ：
explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;
接下来，我们做一个操作将profession字段值全部更新为null。然后，再次执行上述的两条SQL，查看SQL语句的执行计划。
最终我们看到，一模一样的SQL语句，先后执行了两次，结果查询计划是不一样的，为什么会出现这种现象，这是和数据库的数据分布有关系。查询时MySQL会评估，走索引快，还是全表扫描快，如果全表扫描更快，则放弃索引走全表扫描。 因此，is null 、is not null是否走索引，得具体情况具体分析，并不是固定的。
2.6.4 SQL提示
目前tb_user表的数据情况如下:
索引情况如下:
把上述的 idx_user_age, idx_email 这两个之前测试使用过的索引直接删除。
drop index idx_user_age on tb_user;
drop index idx_email on tb_user;
执行SQL : explain select * from tb_user where profession = '软件工程';
查询走了联合索引。
执行SQL，创建profession的单列索引：create index idx_user_pro on tb_user(profession);
创建单列索引后，再次执行A中的SQL语句，查看执行计划，看看到底走哪个索引。
测试结果，我们可以看到，possible_keys中 idx_user_pro_age_sta,idx_user_pro 这两个索引都可能用到，最终MySQL选择了idx_user_pro_age_sta索引。这是MySQL自动选择的结果。
那么，我们能不能在查询的时候，自己来指定使用哪个索引呢？ 答案是肯定的，此时就可以借助于MySQL的SQL提示来完成。 接下来，介绍一下SQL提示。
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
1). use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
2). ignore index ： 忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
3). force index ： 强制使用索引。
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
2.6.5 覆盖索引
尽量使用覆盖索引，减少select *。那么什么是覆盖索引呢？覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。
接下来，我们来看一组SQL的执行计划，看看执行计划的差别，然后再来具体做一个解析。
explain select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '软 件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
上述这几条SQL的执行结果为:
从上述的执行计划我们可以看到，这四条SQL语句的执行计划前面所有的指标都是一样的，看不出来差异。但是此时，我们主要关注的是后面的Extra，前面两天SQL的结果为 Using where; UsingIndex ; 而后面两条SQL的结果为: Using index condition 。
| Extra | 含义 |
|---|---|
| Using where; Using Index | 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据 |
| Using index condition | 查找使用了索引，但是需要回表查询数据 |
因为，在tb_user表中有一个联合索引 idx_user_pro_age_sta，该索引关联了三个字段profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主键id。 所以当我们查询返回的数据在 id、profession、age、status 之中，则直接走二级索引直接返回数据了。 如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。 而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表查询（除非是根据主键查询，此时只会扫描聚集索引）。
为了大家更清楚的理解，什么是覆盖索引，什么是回表查询，我们一起再来看下面的这组SQL的执行过程。
A. 表结构及索引示意图:
id是主键，是一个聚集索引。 name字段建立了普通索引，是一个二级索引（辅助索引）。
B. 执行SQL : select * from tb_user where id = 2;
根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
C. 执行SQL：selet id,name from tb_user where name = 'Arm';
虽然是根据name字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在name的二级索引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高。
D. 执行SQL：selet id,name,gender from tb_user where name = 'Arm';
由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。
思考题：
一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案:
select id,username,password from tb_user where username ='itcast';
答案: 针对于 username, password建立联合索引, sql为: create index
idx_user_name_pass on tb_user(username,password);
这样可以避免上述的SQL语句，在查询的过程中，出现回表查询。
2.6.6 前缀索引
当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
创建前缀索引的语法：
create index idx_xxxx on table_name(column(n)) ;
示例：
为tb_user表的email字段，建立长度为5的前缀索引。
create index idx_email_5 on tb_user(email(5));
前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
前缀索引的查询流程
2.6.7 单列索引与联合索引
单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。
我们先来看看 tb_user 表中目前的索引情况:
在查询出来的索引中，既有单列索引，又有联合索引。
接下来，我们来执行一条SQL语句，看看其执行计划：
通过上述执行计划我们可以看出来，在and连接的两个字段 phone、name上都是有单列索引的，但是最终mysql只会选择一个索引，也就是说，只能走一个字段的索引，此时是会回表查询的。
紧接着，我们再来创建一个phone和name字段的联合索引来查询一下执行计划。
create unique index idx_user_phone_name on tb_user(phone,name);
此时，查询时，就走了联合索引，而在联合索引中包含 phone、name的信息，在叶子节点下挂的是对应的主键id，所以查询是无需回表查询的。
如果查询使用的是联合索引，具体的结构示意图如下：
2.7 索引设计原则
1). 针对于数据量较大，且查询比较频繁的表建立索引。
2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，可以更好地确定哪个索引最有效地用于查询。
三、进阶篇-SQL优化
3.1 插入数据
一次性插入多条记录，可以从以下三个方面进行优化
- 批量插入数据
Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
- 手动控制事务
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;
- 主键顺序插入，性能要高于乱序插入。
主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
3.2 大批量插入数据
如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：
可以执行如下指令，将数据脚本文件中的数据加载到表结构中：
-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;
主键顺序插入性能高于乱序插入
3.3 主键优化
在上一小节，我们提到，主键顺序插入的性能是要高于乱序插入的。 这一小节，就来介绍一下具体的原因，然后再分析一下主键又该如何设计。
3.3.1 数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。
行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：
在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不下，将会存储到下一个页中，页与页之间会通过指针连接。
3.3.2 页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行溢出)，根据主键排列。
3.3.2.1 主键顺序插入效果
①. 从磁盘中申请页， 主键顺序插入
②. 第一个页没有满，继续往第一页插入
③. 当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接
④. 当第二页写满了，再往第三页写入
3.3.2.2 主键乱序插入效果
①. 加入1#,2#页都已经写满了，存放了如图所示的数据
②. 此时再插入id为50的记录，我们来看看会发生什么现象，会再次开启一个页，写入新的页中吗？
不会。因为，索引结构的叶子节点是有顺序的。按照顺序，应该存储在47之后。
但是47所在的1#页，已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。
但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50。
移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个页，应该是3#， 3#的下一个页是2#。 所以，此时，需要重新设置链表指针。
上述的这种现象，称之为 "页分裂"，是比较耗费性能的操作。
3.3.3 页合并
目前表中已有数据的索引结构(叶子节点)如下：
当我们对已有数据进行删除时，具体的效果如下:
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
当我们继续删除2#的数据记录
当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
删除数据，并将页合并之后，再次插入新的数据21，则直接插入3#页
这个里面所发生的合并页的这个现象，就称之为 "页合并"。
知识小贴士：
MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。
3.3.4 索引设计原则
满足业务需求的情况下，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
业务操作时，避免对主键的修改。
3.4 order by 优化
MySQL的排序，有两种方式：
Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。
接下来，我们来做一个测试：
3.4.1 查看索引
3.4.2 执行排序SQL
explain select id,age,phone from tb_user order by age ;
explain select id,age,phone from tb_user order by age, phone ;
由于 age, phone 都没有索引，所以此时再排序时，出现Using filesort， 排序性能较低。
3.4.3 创建索引
-- 创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);
3.4.4 执行升序排序
创建索引后，根据age, phone进行升序排序
explain select id,age,phone from tb_user order by age;
explain select id,age,phone from tb_user order by age , phone;
建立索引之后，再次进行排序查询，就由原来的Using filesort， 变为了 Using index，性能就是比较高的了。
3.4.5 进行降序排序
创建索引后，根据age, phone进行降序排序
explain select id,age,phone from tb_user order by age desc , phone desc ;
也出现 Using index， 但是此时Extra中出现了 Backward index scan，这个代表反向扫描索引，因为在MySQL中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan。 在MySQL8版本中，支持降序索引，我们也可以创建降序索引。
3.4.6 失效情况
根据phone，age进行升序排序，phone在前，age在后。
explain select id,age,phone from tb_user order by phone , age;
排序时,也需要满足最左前缀法则,否则也会出现 filesort。因为在创建索引的时候， age是第一个字段，phone是第二个字段，所以排序时，也就该按照这个顺序来，否则就会出现 Using filesort。
根据age, phone进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc , phone desc ;
因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时就会出现Using filesort。
为了解决上述的问题，我们可以创建一个索引，这个联合索引中 age 升序排序，phone 倒序排序。
create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);
然后再次执行如下SQL
explain select id,age,phone from tb_user order by age asc , phone desc ;
升序/降序联合索引结构图示:
3.4.7 总结
由上述的测试,我们得出order by优化原则:
A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
B. 尽量使用覆盖索引。
C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。
3.5 group by 优化
分组操作，我们主要来看看索引对于分组操作的影响。
首先我们先将 tb_user 表的索引全部删除掉 。
接下来，在没有索引的情况下，执行如下SQL，查询执行计划：
explain select profession , count(*) from tb_user group by profession ;
然后，我们在针对于 profession ， age， status 创建一个联合索引。
create index idx_user_pro_age_sta on tb_user(profession , age , status);
紧接着，再执行前面相同的SQL查看执行计划。
explain select profession , count(*) from tb_user group by profession ;
再执行如下的分组查询SQL，查看执行计划：
我们发现，如果仅仅根据age分组，就会出现 Using temporary ；而如果是根据profession,age两个字段同时分组，则不会出现 Using temporary。原因是因为对于分组操作，在联合索引中，也是符合最左前缀法则的。
3.5.1 总结：
所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：
在分组操作时，可以通过索引来提高效率。
分组操作时，索引的使用也是满足最左前缀法则的。
3.6 limit 优化
在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。
我们一起来看看执行limit分页查询耗时对比：
通过测试我们会看到，越往后，分页查询效率越低，这就是分页查询的问题所在。
因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。
优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
3.7 count 优化
3.7.1 概述
select count(*) from tb_user ;
在测试中，我们发现，如果数据量很大，在执行count操作时，是非常耗时的。
MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；但是如果是带条件的count，MyISAM也慢。
InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。
3.7.2 count用法
count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。
用法：count（*）、count（主键）、count（字段）、count（数字）
| count用法 | 含义 |
|---|---|
| count(主键) | InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null) |
| count(字段) | 没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。 |
| count(数字) | InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。 |
| count(*) | InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。 |
按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，
所以尽量使用 count(*)。
3.8 update 优化
我们主要需要注意一下update语句执行时的注意事项。
update course set name = 'javaEE' where id = 1 ;
当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。
但是当我们在执行如下SQL时。
update course set name = 'SpringBoot' where name = 'PHP' ;
当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能大大降低。
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。
四、进阶篇-视图/存储/触发器
TODO
五、进阶篇-锁
5.1 概述
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
MySQL中的锁，按照锁的粒度分，分为以下三类：
全局锁：锁定数据库中的所有表。
表级锁：每次操作锁住整张表。
行级锁：每次操作锁住对应的行数据。
5.2 全局锁
5.2.1 介绍
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
为什么全库逻辑备份，就需要加全就锁呢？
【我们一起先来分析一下不加全局锁，可能存在的问题】
假设在数据库中存在这样三张表: tb_stock 库存表，tb_order 订单表，tb_orderlog 订单日志表。
在进行数据备份时，先备份了tb_stock库存表。
然后接下来，在业务系统中，执行了下单操作，扣减库存，生成订单（更新tb_stock表，插入tb_order表）。
然后再执行备份 tb_order表的逻辑。
业务中执行插入订单日志操作。
最后，又备份了tb_orderlog表。
此时备份出来的数据，是存在问题的。因为备份出来的数据，tb_stock表与tb_order表的数据不一致(有最新操作的订单信息,但是库存数没减)。
那如何来规避这种问题呢? 此时就可以借助于MySQL的全局锁来解决。
【再来分析一下加了全局锁后的情况】
对数据库进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的DDL、DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就是查询操作。那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保证了数据的一致性和完整性。
5.2.2 语法
1). 加全局锁
flush tables with read lock ;
2). 数据备份
mysqldump -uroot –p1234 itcast > itcast.sql
数据备份的相关指令, 在后面MySQL管理章节, 还会详细讲解.
3). 释放锁
unlock tables ;
5.2.3 特点
数据库中加全局锁，是一个比较重的操作，存在以下问题：
如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。
在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。
mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql
5.3 表级锁
表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
对于表级锁，主要分为以下三类：
表锁
元数据锁（meta data lock，MDL）
意向锁
5.3.1 表锁
对于表锁，分为两类：
表共享读锁（read lock）
表独占写锁（write lock）
语法：
加锁：lock tables 表名... read/write。
释放锁：unlock tables / 客户端断开连接 。
特点:
A. 读锁
左侧为客户端一，对指定表加了读锁，不会影响右侧客户端二的读，但是会阻塞右侧客户端的写。
B. 写锁
左侧为客户端一，对指定表加了写锁，会阻塞右侧客户端的读和写。
结论: 读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。
5.3.2 元数据锁
meta data lock , 元数据锁，简写MDL。
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。
在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。
常见的SQL操作时，所添加的元数据锁：
| 对应SQL | 锁类型 | 说明 |
|---|---|---|
| lock tables xxx read /write | SHARED_READ_ONLY /SHARED_NO_READ_WRITE | |
| select 、select ... lock in share mode | SHARED_READ | 与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥 |
| insert 、update、delete、select ... for update | SHARED_WRITE | 与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥 |
| alter table ... | EXCLUSIVE | 与其他的MDL都互斥 |
5.3.2.1 什么是元数据锁
在MySQL5.5.3之前，有一个著名的bug#989(bug链接: MySQL Bugs: #989: If DROP TABLE while there's an active transaction, wrong binlog order)，大致如下:
# session1:
BEGIN;
INSERT INTO t ... ;
COMMIT;
# session2:
DROP TABLE t;
# 如果user1在一个表上有一个活动的事务，然后user2删除这个表，那么user1会提交，那么在binlog中我们有如下内容(binlog记录的操作顺序顺序)：
1.
DROP TABLE t;
2.
BEGIN;
INSERT INTO t ... ;
COMMIT;
很显然mysql执行时会先删除表t，然后执行insert 会报1032 error。
再举一个简单的例子，如果你在查询一个表的过程中，另外一个session对该表删除了一个列，那前面的查询到底该显示什么呢？如果在RR隔离级别下，事物中再次执行相同的语句还会和之前结果一致吗？
答案是否定的，RR隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求。
为了防止这种情况，所以在5.5.3版本引入了MDL锁(表查询开始MySQL会在表上加一个锁，来防止被别的session修改了表定义，)。其实5.5也有类似保护元数据的机制，只是没有明确提出MDL概念而已。但是5.5之前版本(比如5.1)与5.5之后版本在保护元数据这块有一个显著的不同点是，5.1对于元数据的保护是语句级别的，5.5对于Metadata的保护是事务级别的。所谓语句级别，即语句执行完成后，无论事务是否提交或回滚，其表结构可以被其他会话更新；而事务级别则是在事务结束后才释放MDL。引入MDL锁主要是为了解决两个问题：
事务隔离问题：比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求。
数据复制问题：比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。也就是上面提到的bug#989。
DDL操作与MetaData Lock
Metadata lock 机制是为了保证数据一致性存在的，在有session操作时候某张表时，需要首先获得Metadata lock ,然后操作，如果这个时候，又来了一个session要DDL操作同一个表，就会出现
waiting for table metadata lock自动提交模式下，单语句就是一个事务，执行完了，事务也就结束了。
preparestatement 会获得 metalock，一旦 prepare 完毕， metalock 就释放了。
online DDL应该是指在
alter table进行的时候， 插入/修改/删除数据的sql语句(DML)不会Waiting for table metadata lock。一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态，后续对TableA的任何操作（包括读）都无法进行，也会在Opening tables的阶段进入Waiting for table metadata lock的队列。
5.3.2.2 metadata lock和行锁
元数据锁是server层的锁，表级锁，每执行一条DML、DDL语句时都会申请metadata锁，DML操作需要metadata读锁，DDL操作需要metadata写锁，metadata加锁过程是系统自动控制，无法直接干预，读锁和写锁的阻塞关系如下：
- 读锁和写锁之间相互阻塞，即同一个表上的DML和DDL之间互相阻塞。
- 写锁和写锁之间互相阻塞，即两个session不能对表同时做表定义变更，需要串行操作。
- 读锁和读锁之间不会产生阻塞。也就是增删改查不会因为metadata lock产生阻塞，可以并发执行，日常工作中大家看到的DML之间的锁等待是innodb行锁引起的，和metadata lock无关。
申请metadata锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到metadata锁锁后，直到事务执行完才会将锁释放。（这里有种特殊情况如果事务中包含DDL操作，mysql会在DDL操作语句执行前，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放）。
熟悉innodb行锁的同学这里可能有点疑困惑，因为行锁分类和metadata lock很类似，也主要分为读锁和写锁，或者叫共享锁和排他锁，读写锁之间阻塞关系也一致。二者最重要的区别一个是表锁，一个是行锁，且行锁中的读写操作对应在metadata lock中都属于读锁。
注意： 支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。
metadata lock锁通常发生在DDL操作挂起的时候，原因是有未提交的事务对该表进行DML操作。而MySQL的会话那么多，不知道哪个会话的操作没有及时提交影响了DDL。通常我们排查这类问题，往往需要从information_schema.innodb_trx表中查询当前在执行的事务，但当SQL已经执行过了，没有commit，这个时候这个表中是看不到SQL的。
大家也许会奇怪，以前听说普通查询不加锁的，怎么这里又说要加表锁，我们做一个简单测试：
【模拟与查找MDL锁】
在MySQL5.7中，performance_schema库中新增了metadata_locks表，专门记录MDL的相关信息。首先要开启metadata_locks锁记录，执行如下SQL开启：
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';
session1：查询前，先看一下metadata_locks表，这个表位于performance_schema下，记录了metadata lock的加锁信息。
mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | performance_schema | metadata_locks | 2459204563344 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 36 | 3 |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
1 row in set (0.00 sec)
**session2：**执行简单查询。
mysql> begin;
mysql> SELECT * from students; # 注意事务未提交
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 1 | |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
| 4 | 刘德华 | 59 | 185.00 | 男 | 1 | |
| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |
| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |
| 9 | 程坤 | 27 | 181.00 | 男 | 2 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |
| 12 | 静香 | 12 | 180.00 | 女 | 4 | |
| 13 | 郭靖 | 12 | 170.00 | 男 | 4 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
+----+-----------+------+--------+--------+--------+-----------+
14 rows in set (0.00 sec)
session1： 在会话1中查看metadata_locks表
mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | report-vision | students | 2459204563152 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 37 | 17 |
| TABLE | performance_schema | metadata_locks | 2459204563440 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 36 | 4 |
+-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.00 sec)
此时再次查看metadata_lock表，发现多了一条students的加锁记录，加锁类型为SHARED_READ,且状态是已授予（GRANTED)。大家通常理解的查询不加锁，是指不在表上加innodb行锁。
如果在session2执行select期间且事务未提交，另外一个session执行了一个DDL操作，此时就会产生互斥的metadata lock：
session3：执行删除该表操作（DDL），发现该语句执行状态挂起
mysql> drop table students;
阻塞中......
session1：查询所有会话 发现发生MDL锁
mysql> show processlist;
+----+------+-----------------+---------------+---------+------+---------------------------------+---------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+---------------+---------+------+---------------------------------+---------------------+
| 2 | root | localhost:64908 | NULL | Sleep | 4991 | | NULL |
| 3 | root | localhost:64940 | report-vision | Sleep | 4991 | | NULL |
| 4 | root | localhost:64970 | report-vision | Sleep | 4981 | | NULL |
| 5 | root | localhost:65012 | report-vision | Sleep | 433 | | NULL |
| 6 | root | localhost:65449 | report-vision | Sleep | 4799 | | NULL |
| 7 | root | localhost:65454 | report-vision | Sleep | 4797 | | NULL |
| 8 | root | localhost:50270 | report-vision | Sleep | 438 | | NULL |
| 9 | root | localhost:51163 | report-vision | Sleep | 3869 | | NULL |
| 11 | root | localhost:59351 | NULL | Query | 0 | starting | show processlist |
| 12 | root | localhost:59625 | report-vision | Sleep | 121 | | NULL |
| 13 | root | localhost:59689 | report-vision | Query | 14 | Waiting for table metadata lock | drop table students |
+----+------+-----------------+---------------+---------+------+---------------------------------+---------------------+
11 rows in set (0.00 sec)
显然，id为13的线程还未执行drop操作，状态为Waiting for table metadata lock，也就是在等待session2的事务提交操作完成。
session1：查看metadata_locks表记录 发现students表有MDL锁冲突
mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | report-vision | students | 2459204563152 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 37 | 17 |
| GLOBAL | NULL | NULL | 2459204563440 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5497 | 38 | 6 |
| SCHEMA | report-vision | NULL | 2459204563728 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5482 | 38 | 6 |
| TABLE | report-vision | students | 2459204564400 | EXCLUSIVE | TRANSACTION | PENDING | sql_parse.cc:6020 | 38 | 6 |
| TABLE | performance_schema | metadata_locks | 2459204563920 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 36 | 6 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
5 rows in set (0.00 sec)
当 session2 的事务提交后：
session2：
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
session3：
mysql> drop table students;
Query OK, 0 rows affected (4 min 11.77 sec)
5.3.2.3 元数据锁可能造成系统崩溃
举一个简单例子，session1启动一个事务，对表students执行一个简单的查询；session2对students加一个字段；session3来对students做一个查询；session4来对students做一个update，各个session串行操作。
session1：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * from students where id=1;
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
+----+--------+------+--------+--------+--------+-----------+
1 row in set (0.00 sec)
session2：
mysql> alter table students add column col1 int;
阻塞中...
session3:
mysql> select sleep(10) from students; # 开始执行后还会阻塞一段时间
阻塞中...
session4:
mysql> update students set name='aaaa' where id=1;
阻塞中...
也就是由于session1的一个事务没有提交，导致session2的DDL操作被阻塞，session3和session4本身不会被session1阻塞，但由于在锁队列中，session2排队更早，它准备加的是metadata lock写锁，阻塞了session3和session4的读锁。如果students是一个执行频繁的表，show processlist会发现大量waiting for table metadata lock的线程，数据库连接很快就会消耗完，导致业务系统无法正常响应。
此时如果session1提交，是session2的alter语句先执行还是session3和session4先执行呢？
之前一直以为先到的先执行，当然是session2先执行，但经过测试，在5.7中，session3和session4先执行，session2最后执行（如果为drop语句依然是session2最先执行），也就会出现alter(或其他DDL语句)长时间无法执行的情况；而在8.0中，session2先执行，session3和session4后执行，由于5.6以后DDL是online的（drop 相关的DDL除外），session2并不会阻塞session3和session4，感觉这样才是合理的，alter不会被‘饿死’。
5.3.2.4 如何快速找到阻塞源头？
监控元数据锁
快速解决问题永远是第一位的，一旦出现长时间的metadata lock，尤其是在访问频繁的业务表上产生，通常会导致表无法访问，读写全被阻塞，此时找到阻塞源头是第一位的。这里最重要的表就是前面提到过的performance_schema.metadata_locks表。
metadata_locks是5.7中被引入，记录了metadata lock的相关信息，包括持有对象、类型、状态等信息。但5.7默认设置是关闭的（8.0默认打开），需要通过下面命令打开设置：
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'WHERE NAME = 'wait/lock/metadata/sql/mdl';
如果要永久生效，需要在配置文件中加入如下内容：
[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
单纯查询这个表无法得出具体的阻塞关系，也无法得知什么语句造成的阻塞，这里要关联另外两个表performance_schema.thread和performance_schema.events_statements_history,thread表可以将线程id和show processlist中id关联，events_statements_history表可以得到事务的历史sql，关联得到一条监控元数据锁的sql语句。
关联后的完整sql如下：
SELECT locked_schema,
locked_table,
locked_type,
waiting_processlist_id,
waiting_age,
waiting_query,
waiting_state,
blocking_processlist_id,
blocking_age,
substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
sql_kill_blocking_connection
FROM
(
SELECT
b.OWNER_THREAD_ID AS granted_thread_id,
a.OBJECT_SCHEMA AS locked_schema,
a.OBJECT_NAME AS locked_table,
"Metadata Lock" AS locked_type,
c.PROCESSLIST_ID AS waiting_processlist_id,
c.PROCESSLIST_TIME AS waiting_age,
c.PROCESSLIST_INFO AS waiting_query,
c.PROCESSLIST_STATE AS waiting_state,
d.PROCESSLIST_ID AS blocking_processlist_id,
d.PROCESSLIST_TIME AS blocking_age,
d.PROCESSLIST_INFO AS blocking_query,
concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM performance_schema.metadata_locks a JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
) t1,
(
SELECT thread_id, group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
FROM
performance_schema.events_statements_history
GROUP BY thread_id
) t2
WHERE t1.granted_thread_id = t2.thread_id \G
*************************** 1. row ***************************
locked_schema: report-vision
locked_table: students
locked_type: Metadata Lock
waiting_processlist_id: 13
waiting_age: 938
waiting_query: alter table students add column col1 int
waiting_state: Waiting for table metadata lock
blocking_processlist_id: 12
blocking_age: 947
blocking_query: SELECT * from students where id=1
sql_kill_blocking_connection: KILL 12
1 row in set, 2 warnings (0.01 sec)
根据显示结果，processlist_id为12的线程阻塞了13的线程，我们需要kill 12即可解锁。
实际上，MySQL也提供了一个类似的视图来解决metadata lock问题，视图名称为sys.schema_table_lock_waits，但此视图查询结果有bug，不是很准确，建议大家还是参考上面sql。
5.3.2.5 如何避免元数据锁阻塞
MDL锁一旦发生会对业务造成极大影响，因为后续所有对该表的访问都会被阻塞，造成连接积压。我们日常要尽量避免 MDL 锁阻塞的发生，下面给出几点优化建议可供参考：
- 开启 metadata_locks 表记录 MDL 锁。
- 设置参数 lock_wait_timeout 为较小值，使被阻塞端主动停止。
- 规范使用事务，及时提交事务，避免使用大事务。
- 增强监控告警，及时发现 MDL 锁。
- DDL 操作及备份操作放在业务低峰期执行。
- 对于表结构经常变化且不关心原有表名只查询实时最新数据的特殊业务场景，可以在每次变更表结构时，用不同的表名（表名为添加后缀，如时间戳或uuid的表名）保存最新数据，随机定时异步删除原有的表名。来避免DDL操作与DML操作阻塞。
5.3.3 意向锁
5.3.3.1 意向锁概述
需要强调一下，意向锁是一种不与行级锁冲突表级锁，这一点非常重要。意向锁分为两种：
【意向共享锁】（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）
-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;
【意向排他锁】（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）
-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;
即：意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。
5.3.3.2 加锁示例
假如没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢，来通过示意图简单分析一下：
首先客户端一，开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加行锁。
当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较低。
有了意向锁之后 :
客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。
而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。
5.3.3.3 意向锁要解决的问题
我们先来看一下百度百科上对意向锁存在意义的描述：
如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。
设想这样一张 users 表： MySql，InnoDB，Repeatable-Read：users（id PK，name）
| id | name |
|---|---|
| 1 | ROADHOG |
| 2 | Reinhardt |
| 3 | Tracer |
| 4 | Genji |
| 5 | Hanzo |
| 6 | Mccree |
事务 A 获取了某一行的排他锁，并未提交：
SELECT * FROM users WHERE id = 6 FOR UPDATE;
事务 B 想要获取 users 表的表锁：
LOCK TABLES users READ;
因为共享锁与排他锁互斥，所以事务 B 在视图对 users 表加共享锁的时候，必须保证：
- 当前没有其他事务持有 users 表的排他锁。
- 当前没有其他事务持有 users 表中任意一行的排他锁 。
为了检测是否满足第二个条件，事务 B 必须在确保 users表不存在任何排他锁的前提下，去检测表中的每一行是否存在排他锁。很明显这是一个效率很差的做法，但是有了意向锁之后，情况就不一样了
5.3.3.4 意向锁的兼容性
意向锁是怎么解决这个问题的呢？首先，我们需要知道意向锁之间的兼容互斥性：
| 意向共享锁（IS） | 意向排他锁（IX） | |
|---|---|---|
| 意向共享锁（IS） | 兼容 | 兼容 |
| 意向排他锁（IX） | 兼容 | 兼容 |
即意向锁之间是互相兼容的，emmm......那你存在的意义是啥？
虽然意向锁和自家兄弟互相兼容，但是它会与普通的排他 / 共享锁互斥：
| 意向共享锁（IS） | 意向排他锁（IX） | |
|---|---|---|
| 共享锁（S） | 兼容 | 互斥 |
| 排他锁（X） | 互斥 | 互斥 |
注意：这里的排他/共享锁指的都是表锁！！！，意向锁不会与行级的共享/排他锁互斥！！！
现在我们回到刚才 users 表的例子：
事务 A 获取了某一行的排他锁，并未提交：
SELECT * FROM users WHERE id = 6 FOR UPDATE;
此时 users 表存在两把锁：users 表上的意向排他锁与 id 为 6 的数据行上的排他锁。
事务 B 想要获取 users 表的共享锁：
LOCK TABLES users READ;
此时事务 B 检测事务 A 持有 users 表的意向排他锁，就可以得知事务 A 必然持有该表中某些数据行的排他锁，那么事务 B 对 users 表的加锁请求就会被排斥（阻塞），而无需去检测表中的每一行数据是否存在排他锁。
5.3.3.5 意向锁的并发性
这就牵扯到我前面多次强调的一件事情：
意向锁不会与行级的共享/排他锁互斥！！！ 意向锁不会与行级的共享/排他锁互斥！！！ 意向锁不会与行级的共享/排他锁互斥！！！
重要的话要加粗说三遍，正因为如此，意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性（不然我们直接用普通的表锁就行了）。
最后我们扩展一下上面 users 表的例子来概括一下意向锁的作用（一条数据从被锁定到被释放的过程中，可能存在多种不同锁，但是这里我们只着重表现意向锁）：
| id | name |
|---|---|
| 1 | ROADHOG |
| 2 | Reinhardt |
| 3 | Tracer |
| 4 | Genji |
| 5 | Hanzo |
| 6 | Mccree |
事务 A 先获取了某一行的排他锁，并未提交：
SELECT * FROM users WHERE id = 6 FOR UPDATE;
- 事务 A 获取了 users 表上的意向排他锁。
- 事务 A 获取了 id 为 6 的数据行上的排他锁。
之后事务 B 想要获取 users 表的共享锁：
LOCK TABLES users READ;
- 事务 B 检测到事务 A 持有 users 表的意向排他锁。
- 事务 B 对 users 表的加锁请求被阻塞（排斥）。
最后事务 C 也想获取 users 表中某一行的排他锁：
SELECT * FROM users WHERE id = 5 FOR UPDATE;
- 事务 C 申请 users 表的意向排他锁。
- 事务 C 检测到事务 A 持有 users 表的意向排他锁。
- 因为意向锁之间并不互斥，所以事务 C 获取到了 users 表的意向排他锁。
- 因为id 为 5 的数据行上不存在任何排他锁，最终事务 C 成功获取到了该数据行上的排他锁。
5.3.3.6 总结
- InnoDB 支持多粒度锁，特定场景下，行级锁可以与表级锁共存。
- 意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与 共享锁 / 排他锁 互斥。
- IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
- 意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。
- 意向共享锁(IS): 由语句select ... lock in share mode添加 。 与 表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
- 意向排他锁(IX): 由insert、update、delete、select...for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。
一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。
5.4 行级锁
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。行级锁主要分为三类：行锁、间隙锁、临健锁。在不同的事务隔离级别下，对行级锁的支持也是不同的。在读以提交（Read Commit）级别，行级锁的种类只有行锁（Record Lock），在可重复读（Repeatable Read）隔离级别下，行级锁的种类除了行锁(Record Lock), 还有间隙锁(Gap Lock)、临健锁(Next-Key Lock)
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：
行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录本身），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下支持。
临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。 在RR隔离级别下支持。
5.4.1 行锁
InnoDB实现了以下两种类型的行锁：
共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁
两种行锁的兼容情况如下:
| S(共享锁) | X(排他锁) | |
|---|---|---|
| S(共享锁) | 兼容 | 冲突 |
| X(排他锁) | 冲突 | 冲突 |
- 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
- 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。
举个例子，当一个事务执行了下面这条语句：
mysql > begin;
mysql > select * from t_test where id = 1 for update;
事务会对表中主键 id = 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行修改和删除了。
当事务执行 commit 后，事务过程中生成的锁都会被释放
常见的SQL语句，在执行时，所加的行锁如下：
| SQL | 行锁类型 | 说明 |
|---|---|---|
| INSERT ... | 排他锁 | 自动加锁 |
| UPDATE ... | 排他锁 | 自动加锁 |
| DELETE ... | 排他锁 | 自动加锁 |
| SELECT（正常） | 不加任何锁 | |
| SELECT ... LOCK IN SHARE MODE | 共享锁 | 需要手动在SELECT之后加LOCK IN SHARE MODE |
| SELECT ... FOR UPDATE | 排他锁 | 需要手动在SELECT之后加FOR UPDATE |
演示
默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key(临健锁) 锁进行搜索和索引扫描，以防止幻读。
针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。
可以通过以下SQL，查看意向锁及行锁的加锁情况：
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
数据准备
CREATE TABLE `stu` (
`id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
INSERT INTO `stu` VALUES (1, 'tom', 1);
INSERT INTO `stu` VALUES (3, 'cat', 3);
INSERT INTO `stu` VALUES (8, 'rose', 8);
INSERT INTO `stu` VALUES (11, 'jetty', 11);
INSERT INTO `stu` VALUES (19, 'lily', 19);
INSERT INTO `stu` VALUES (25, 'luci', 25);
演示行锁的时候，我们就通过上面这张表来演示一下。
- 普通的select语句，执行时，不会加锁。
- select...lock in share mode，加共享锁，共享锁与共享锁之间兼容。
- 共享锁与排他锁之间互斥。
客户端一获取的是id为1这行的共享锁，客户端二是可以获取id为3这行的排它锁的，因为不是同一行数据。 而如果客户端二想获取id为1这行的排他锁，会处于阻塞状态，以为共享锁与排他锁之间互斥。
- 排它锁与排他锁之间互斥
当客户端一，执行update语句，会为id为1的记录加排他锁； 客户端二，如果也执行update语句更新id为1的数据，也要为id为1的数据加排他锁，但是客户端二会处于阻塞状态，因为排他锁之间是互斥的。 直到客户端一，把事务提交了，才会把这一行的行锁释放，此时客户端二，解除阻塞。
- 无索引行锁升级为表锁
stu表中数据如下:
我们在两个客户端中执行如下操作:
在客户端一中，开启事务，并执行update语句，更新name为Lily的数据，也就是id为19的记录 。然后在客户端二中更新id为3的记录，却不能直接执行，会处于阻塞状态，为什么呢？
原因就是因为此时，客户端一，根据name字段进行更新时，name字段是没有索引的，如果没有索引，此时行锁会升级为表锁(因为行锁是对索引项加的锁，而name没有索引)。
接下来，我们再针对name字段建立索引，索引建立之后，再次做一个测试：
此时我们可以看到，客户端一，开启事务，然后依然是根据name进行更新。而客户端二，在更新id为3的数据时，更新成功，并未进入阻塞状态。 这样就说明，我们根据索引字段进行更新操作，就可以避免行锁升级为表锁的情况。
5.4.2 间隙锁&临健锁
间隙锁(Gap Locks)是封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。
产生间隙锁的条件（RR事务隔离级别下；）：
- 使用普通索引锁定；
- 使用多列唯一索引；
- 使用唯一索引锁定多行记录。
以上情况，都会产生间隙锁，下面是小编看了官方文档理解的：
对于使用唯一索引来搜索并给某一行记录加锁的语句，不会产生间隙锁。（这不包括搜索条件仅包括多列唯一索引的一些列的情况；在这种情况下，会产生间隙锁。）例如，如果id列具有唯一索引，则下面的语句仅对具有id值100的行使用记录锁，并不会产生间隙锁：
SELECT * FROM child WHERE id = 100 FOR UPDATE;
这条语句，就只会产生记录锁，不会产生间隙锁。
打开间隙锁设置
首先查看 innodb_locks_unsafe_for_binlog 是否禁用：
show variables like 'innodb_locks_unsafe_for_binlog';
查看结果：
TODO mysql执行结果
innodb_locks_unsafe_for_binlog：默认值为OFF，即启用间隙锁。因为此参数是只读模式，如果想要禁用间隙锁，需要修改 my.cnf（windows是my.ini） 重新启动才行。
# 在 my.cnf 里面的[mysqld]添加
[mysqld]
innodb_locks_unsafe_for_binlog = 1
5.4.2.1 唯一索引的间隙锁
测试环境： MySQL，InnoDB，默认的隔离级别（RR）
数据准备：
CREATE TABLE `test` (
`id` int(1) NOT NULL AUTO_INCREMENT,
`name` varchar(8) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');
在进行测试之前，我们先来看看test表中存在的隐藏间隙：
- (-infinity, 1]
- (1, 5]
- (5, 7]
- (7, 11]
- (11, +infinity]
【只使用记录锁，不会产生间隙锁】
我们现在进行以下几个事务的测试：
/* 开启事务1 */
BEGIN;
/* 查询 id = 5 的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` = 5 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);
# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句
/* 事务2插入一条 name = '小张' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小张'); # 正常执行
/* 事务3插入一条 name = '小张' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '小东'); # 正常执行
/* 提交事务1，释放事务1的锁 */
COMMIT;
上诉的案例，由于主键是唯一索引，而且是只使用一个索引查询，并且只锁定一条记录，所以以上的例子，只会对 id = 5 的数据加上记录锁，而不会产生间隙锁。
【产生间隙锁】
我们继续在 id 唯一索引列上做以下的测试：
/* 开启事务1 */
BEGIN;
/* 查询 id 在 7 - 11 范围的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);
# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句
/* 事务2插入一条 id = 3，name = '小张1' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (3, '小张1'); # 正常执行
/* 事务3插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 正常执行
/* 事务4插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 阻塞
/* 事务5插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 阻塞
/* 事务6插入一条 id = 9， name = '大东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (9, '大东'); # 阻塞
/* 事务7插入一条 id = 11， name = '李西' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (11, '李西'); # 阻塞
/* 事务8插入一条 id = 12， name = '张三' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (12, '张三'); # 正常执行
/* 提交事务1，释放事务1的锁 */
COMMIT;
从上面我们可以看到，(5, 7]、(7, 11] 这两个区间，都不可插入数据，其它区间，都可以正常插入数据。所以我们可以得出结论：当我们给 (5, 7] 这个区间加锁的时候，会锁住 (5, 7]、(7, 11] 这两个区间。
我们再来测试如果我们锁住不存在的数据时，会怎样：
/* 开启事务1 */
BEGIN;
/* 查询 id = 3 这一条不存在的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);
# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句
/* 事务2插入一条 id = 3，name = '小张1' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (2, '小张1'); # 阻塞
/* 事务3插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 阻塞
/* 事务4插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 正常执行
/* 事务5插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 正常执行
/* 提交事务1，释放事务1的锁 */
COMMIT;
我们可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁。
结论
- 对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：WHERE
id= 5 FOR UPDATE; - 对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE
idBETWEEN 5 AND 7 FOR UPDATE;
5.4.2.2 普通索引的间隙锁
数据准备
创建 test1 表：
# 注意：number 不是唯一值
CREATE TABLE `test1` (
`id` int(1) NOT NULL AUTO_INCREMENT,
`number` int(1) NOT NULL COMMENT '数字',
PRIMARY KEY (`id`),
KEY `number` (`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
在这张表上，我们有 id number 这两个字段，id 是我们的主键，我们在 number 上，建立了一个普通索引，为了方便我们后面的测试。现在我们要先加一些数据：
INSERT INTO `test1` VALUES (1, 1);
INSERT INTO `test1` VALUES (5, 3);
INSERT INTO `test1` VALUES (7, 8);
INSERT INTO `test1` VALUES (11, 12);
在进行测试之前，我们先来看看test1表中 number 索引存在的隐藏间隙：
- (-infinity, 1]
- (1, 3]
- (3, 8]
- (8, 12]
- (12, +infinity]
【案例分析】
我们执行以下的事务（事务1最后提交），分别执行下面的语句：
/* 开启事务1 */
BEGIN;
/* 查询 number = 5 的数据并加记录锁 */
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);
# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句
/* 事务2插入一条 number = 0 的数据 */
INSERT INTO `test1` (`number`) VALUES (0); # 正常执行
/* 事务3插入一条 number = 1 的数据 */
INSERT INTO `test1` (`number`) VALUES (1); # 被阻塞
/* 事务4插入一条 number = 2 的数据 */
INSERT INTO `test1` (`number`) VALUES (2); # 被阻塞
/* 事务5插入一条 number = 4 的数据 */
INSERT INTO `test1` (`number`) VALUES (4); # 被阻塞
/* 事务6插入一条 number = 8 的数据 */
INSERT INTO `test1` (`number`) VALUES (8); # 正常执行
/* 事务7插入一条 number = 9 的数据 */
INSERT INTO `test1` (`number`) VALUES (9); # 正常执行
/* 事务8插入一条 number = 10 的数据 */
INSERT INTO `test1` (`number`) VALUES (10); # 正常执行
/* 提交事务1 */
COMMIT;
我们会发现有些语句可以正常执行，有些语句被阻塞了。我们再来看看我们表中的数据：
这里可以看到，number (1 - 8) 的间隙中，插入语句都被阻塞了，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。我们再进行以下的测试，方便我们更好的理解间隙锁的区域（我们要将数据还原成原来的那样）：
/* 开启事务1 */
BEGIN;
/* 查询 number = 5 的数据并加记录锁 */
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);
/* 事务1插入一条 id = 2， number = 1 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (2, 1); # 阻塞
/* 事务2插入一条 id = 3， number = 2 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (3, 2); # 阻塞
/* 事务3插入一条 id = 6， number = 8 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (6, 8); # 阻塞
/* 事务4插入一条 id = 8， number = 8 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (8, 8); # 正常执行
/* 事务5插入一条 id = 9， number = 9 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (9, 9); # 正常执行
/* 事务6插入一条 id = 10， number = 12 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (10, 12); # 正常执行
/* 事务7修改 id = 11， number = 12 的数据 */
UPDATE `test1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞
/* 提交事务1 */
COMMIT;
我们来看看结果：
这里有一个奇怪的现象：
- 事务3添加 id = 6，number = 8 的数据，给阻塞了；
- 事务4添加 id = 8，number = 8 的数据，正常执行了。
- 事务7将 id = 11，number = 12 的数据修改为 id = 11， number = 5的操作，给阻塞了；
这是为什么呢？我们来看看下边的图，大家就明白了。
隐藏的间隙锁图
从图中可以看出，当 number 相同时，会根据主键 id 来排序，所以：
- 事务3添加的 id = 6，number = 8，这条数据是在 （3, 8） 的区间里边，所以会被阻塞；
- 事务4添加的 id = 8，number = 8，这条数据则是在（8, 12）区间里边，所以不会被阻塞；
- 事务7的修改语句相当于在 （3, 8） 的区间里边插入一条数据，所以也被阻塞了。
结论
- 在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
- 在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序。
5.4.2.3 总结
- 记录锁、间隙锁、临键锁，都属于排它锁；
- 记录锁就是锁住一行记录；
- 间隙锁只有在事务隔离级别 RR 中才会产生；
- 唯一索引只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；
- 普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；
- 间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据，这是为了防止出现 幻读 现象；
- 普通索引的间隙，优先以普通索引排序，然后再根据主键索引排序（多普通索引情况还未研究）；
- 事务级别是RC（读已提交）级别的话，间隙锁将会失效。
5.4.3 案例分析
next-key lock的加锁规则
总结的加锁规则里面，包含了两个 “ “ 原则 ” ” 、两个 “ “ 优化 ” ” 和一个 “bug” 。
- 原则 1 ：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
- 原则 2 ：查找过程中访问到的对象才会加锁。任何辅助索引上的锁，或者非索引列上的锁，最终都要回溯到主键上，在主键上也要加一把锁。
- 优化 1 ：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。也就是说如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁
- 优化 2 ：索引上（不一定是唯一索引）的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
- 一个bug ：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
【案例分析前的数据准备】
我们以表test作为例子，建表语句和初始化语句如下：其中id为主键索引
CREATE TABLE test(
id int(11)NOT NULL,
col1 int(11)DEFAULT NULL,
col2 int(11)DEFAULT NULL,
PRIMARY KEY (id),
KEYc (col1)
)ENGINE=InnoDB;
insert into test values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
5.4.3.1 唯一索引等值查询间隙锁
| session A | session B | session C |
|---|---|---|
| begin; update test set col2 =col2+1where id=7; | ||
| . | insert into testvalues(8,8,8)(blocked) | |
| update test set col2 =col2+1 where id=10; |
由于表 test 中没有 id=7 的记录
根据原则 1 ，加锁单位是 next-key lock ， session A 加锁范围就是 (5,10] ；
同时根据优化 2 ，这是一个等值查询 (id=7) ，而 id=10 不满足查询条件， next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)
5.4.3.2 非唯一索引等值查询锁
| session A | session B | session C |
|---|---|---|
| begin; select id from test where col1 = 5 lock in share mode; | ||
| update test col2 =col2+1where id=5; (Query OK) | ||
| insert into test values(7,7,7) (blocked) |
这里 session A 要给索引 col1 上 col1=5 的这一行加上读锁.
- 根据原则 1，加锁单位是 next-key lock，左开右闭，5是闭上的，因此会给 (0,5]加上 next-key lock。
- 要注意col1是普通索引，因此仅访问 col1=5 这一条记录是不能马上停下来的(可能有col1=5的其他记录)，需要向右遍历，查到c=10 才放弃。根据第二个原则，需要对所有访问进行加锁，因此需要给区间(5,10]加上next-key lock。
- 但是同时这个符合优化 2 ：等值判断，向右遍历，最后一个值不满足 col1=5 这个等值条件，因此退化成间隙锁 (5,10)。
- 根据原则 2，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，因此，在主键索引上没有添加任何锁，这解释了为什么会话B的更新语句可以执行成功。
但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住 这个例子说明，锁是加在索引上的。
执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
如果你要用 lock in share mode来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，因为覆盖索引不会访问主键索引，不会给主键索引上加锁
5.4.3.3 主键索引范围查询锁
上面两个例子是等值查询的，这个例子是关于范围查询的，也就是说下面的语句
select * from test where id=10 for update
select * from tets where id>=10 and id<11 for update;
这两条查语句肯定是等价的，但是它们的加锁规则不太一样
| Session A | Session B | Session C |
|---|---|---|
| begin; select * from test where id>= 10 andid<11 for update; | ||
| insert into testvalues(8,8,8)(Query OK) insert into testvalues(13,13,13);(blocked) | ||
| update test set clo2=col2+1where id=15;(blocked) |
开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10] 。 根据优化 1 ，主键id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。 它是范围查询，范围查找就往后继续找，找到 id=15 这一行停下来，不满足条件，因此需要加 next-key lock (10,15] 。 session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15] 。首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。
5.4.3.4 非唯一索引范围查询锁
与案例三不同的是，案例四中查询语句的 where 部分用的是字段 c ，它是普通索引这两条查语句肯定是等价的，但是它们的加锁规则不太一样
| Session A | Session B | Session C |
|---|---|---|
| begin; select * from test where col1>= 10 andcol1<11 for update; | ||
| insert into test values(8,8,8)(blocked) | ||
| update test set clo2=col2+1where id=15;(blocked) |
在第一次用 col1=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由于索引 col1 是非唯一索引，没有优化规则，也就是 说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和(10,15] 这两个 next-key lock 。 这里需要扫描到 col1=15 才停止扫描，是合理的，因为 InnoDB 要扫到 col1=15，才知道不需要继续往后找了。
5.4.3.5 唯一索引范围查询锁 bug
| Session A | Session B | Session C |
|---|---|---|
| begin; select * from test where id> 10 and id<=15 for update; | ||
| update test set clo2=col2+1 where id=20; (blocked) | ||
| insert into test values(16,16,16); (blocked) |
session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15] 这个 next-key lock ，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。
但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20 。而且由于这是个范围扫描，因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上。照理说，这里锁住 id=20 这一行的行为，其实是没有必要的。因为扫描到 id=15 ，就可以确定不用往后再找了。
5.4.3.6 非唯一索引上存在“等值”的例子
这里，我给表 t 插入一条新记录：insert into t values(30,10,30);也就是说，现在表里面有两个c=10的行但是它们的主键值 id 是不同的（分别是 10 和 30 ），因此这两个c=10 的记录之间，也是有间隙的。
| Session A | Session B | Session C |
|---|---|---|
| begin; delete from test wherecol1=10; | ||
| . | insert into test values(12,12,12);(blocked) | |
| update test set col2=col2+1 where col2=15;(blocked) |
这次我们用 delete 语句来验证。注意， delete 语句加锁的逻辑，其实跟 select ... for update 是类似的，也就是我在文章开始总结的两个 “ 原则 ” 、两个 “ 优化 ” 和一个 “bug” 。
这时， session A 在遍历的时候，先访问第一个 col1=10 的记录。同样地，根据原则 1 ，这里加的是(col1=5,id=5) 到 (col1=10,id=10) 这个 next-key lock 。
由于c是普通索引，所以继续向右查找，直到碰到 (col1=15,id=15) 这一行循环才结束。根据优化 2 ，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (col1=10,id=10) 到 (col1=15,id=15) 的间隙锁。
这个 delete 语句在索引 c 上的加锁范围，就是上面图中蓝色区域覆盖的部分。这个蓝色区域左右两边都是虚线，表示开区间，即 (col1=5,id=5) 和 (col1=15,id=15) 这两行上都没有锁
5.3.4.7 limit 语句加锁
例子 6 也有一个对照案例，场景如下所示：
| Session A | Session B |
|---|---|
| begin; delete from test where col1=10 limit 2; | |
| insert into test values(12,12,12); (Query OK) |
session A 的 delete 语句加了 limit 2 。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2 ，删除的效果都是一样的。但是加锁效果却不一样。
这是因为，案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (col1=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引 col1 上的加锁范围就变成了从（ col1=5,id=5)到（ col1=10,id=30) 这个前开后闭区间，如下图所示：
这个例子对我们实践的指导意义就是， 在删除数据的时候尽量加 limit 。 这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。
5.4.3.8 一个死锁的例子
| Session A | Session B |
|---|---|
| begin; select id from test where col1=10 lockin share mode; | |
| update test set col2=col2+1 where c=10;(blocked) | |
| insert into test values(8,8,8); | |
| ERROR 1213(40001):Deadlock found when trying togetlock;try restarting transaction |
session A 启动事务后执行查询语句加 lock in share mode ，在索引 col1 上加了 next-keylock(5,10] 和间隙锁 (10,15) （索引向右遍历退化为间隙锁）；
session B 的 update 语句也要在索引 c 上加 next-key lock(5,10]，进入锁等待；实际上分成了两步，先是加(5,10) 的间隙锁，加锁成功；然后加 col1=10 的行锁，因为sessionA上已经给这行加上了读锁，此时申请死锁时会被阻塞
然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚
5.4.3.9 order by索引排序的间隙锁1
如下面一条语句
begin;
select * from test where id>9 and id<12 order by id desc for update;
下图为这个表的索引id的示意图。
- 首先这个查询语句的语义是 order by id desc，要拿到满足条件的所有行，优化器必须先找到 “第一个 id<12 的值”。
- 这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到 id=12 的这个值，只是最终没找到，但找到了 (10,15) 这个间隙。（ id=15 不满足条件，所以 next-key lock 退化为了间隙锁 (10,15) 。
- 然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到 id=5 这一行，又因为区间是左开右闭的，所以会加一个next-key lock (0,5] 。 也就是说，在执行过程中，通过树搜索的方式定位记录的时候，用的是 “ 等值查询 ” 的方法
5.4.3.10 order by索引排序的间隙锁2
| Session A | Session B |
|---|---|
| begin; select * from test where col1>=15 and c<=20 order by col1 desc lock in share mode; | |
| insert into testvalues(6,6,6); (blocked) |
1.由于是 order by col1 desc ，第一个要定位的是索引 col1 上 “ 最右边的 ”col1=20 的行。这是一个非唯一索引的等值查询：左开右闭区间，首先加上 next-key lock (15,20] 。 向右遍历，col1=25不满足条件，退化为间隙锁 所以会加上间隙锁(20,25) 和 next-key lock (15,20]
2.在索引 col1 上向左遍历，要扫描到 col1=10 才停下来。同时又因为左开右闭区间，所以 next-keylock 会加到(5,10] ，这正是阻塞session B 的 insert 语句的原因。
3.在扫描过程中， col1=20 、 col1=15 、 col1=10 这三行都存在值，由于是 select * ，所以会在主键id 上加三个行锁。 因此， session A 的 select 语句锁的范围就是：
- 索引 col1 上 (5, 25) ；
- 主键索引上 id=15 、 20 两个行锁。
5.4.3.11 update修改数据的例子-先插入后删除
| Session A | Session B |
|---|---|
| begin; select col1 from test where col1>5 lock in share mode; | |
| update test set col1=1 where col1=5 (Query oK) update test set col1=5 where col1=1; (blocked) |
注意：根据 col1>5 查到的第一个记录是 col1=10 ，因此不会加 (0,5] 这个 next-key lock 。 session A 的加锁范围是索引 col1 上的 (5,10] 、 (10,15] 、 (15,20] 、 (20,25] 和(25,supremum] 。 之后 session B 的第一个 update 语句，要把 col1=5 改成 col1=1 ，你可以理解为两步：
- 插入 (col1=1, id=5) 这个记录；
- 删除 (col1=5, id=5) 这个记录。
通过这个操作， session A 的加锁范围变成了图 7 所示的样子： 
好，接下来 session B 要执行 update t set col1 = 5 where col1 = 1 这个语句了，一样地可以拆成两步： 3. 插入 (col1=5, id=5) 这个记录； 4. 删除 (col1=1, id=5) 这个记录。 第一步试图在已经加了间隙锁的 (1,10) 中插入数据，所以就被堵住了
六、进阶篇-InnoDB引擎
6.1 逻辑存储结构
InnoDB的逻辑存储结构如下图所示:
**表空间 ：**表空间是InnoDB存储引擎逻辑结构的最高层， 如果用户启用了参数 innodb_file_per_table(在8.0版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。
**段：**分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点， 索引段即为B+树的非叶子节点。段用来管理多个Extent（区）。
**区：**表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页。
**页：**是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
**行：**InnoDB 存储引擎数据是按行进行存放的。在行中，默认有两个隐藏字段：
Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
6.2 架构
MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。
6.2.1 内存结构
在左侧的内存结构中，主要分为这么四大块儿： Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。 接下来介绍一下这四个部分。
6.2.1.1 Buffer Pool
InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O。
在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。
缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。
缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：
free page：空闲page，未被使用。
clean page：被使用page，数据没有被修改过。
dirty page：脏页，被使用page，数据被修改过，内存中的数据与磁盘的数据产生了不一致。
在专用服务器上，通常将多达80％的物理内存分配给缓冲池 。参数设置：
show variables like 'innodb_buffer_pool_size';
6.2.1.2 Change Buffer
Change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。
Change Buffer的意义是什么呢?
先来看一幅图，这个是二级索引的结构图：
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。
6.2.1.3 Adaptive Hash Index
自适应hash索引，用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持hash索引，但是给我们提供了一个功能就是这个自适应hash索引。因为前面我们讲到过，hash索引在进行等值匹配时，一般性能是要高于B+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。
InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。
自适应哈希索引，无需人工干预，是系统根据情况自动完成。
参数： adaptive_hash_index
6.2.1.4 Log Buffer
Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。
参数:
innodb_log_buffer_size：缓冲区大小
innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，取值主要包含以下三个：
1: 日志在每次事务提交时写入并刷新到磁盘，默认值。
0: 每秒将日志写入并刷新到磁盘一次。
2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。
6.2.2 磁盘结构
接下来，再来看看InnoDB体系结构的右边部分，也就是磁盘结构：
6.2.2.1 System Tablespace
系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)
参数：innodb_data_file_path
系统表空间，默认的文件名叫 ibdata1。
6.2.2.2 File-Per-Table Tablespaces
如果开启了innodb_file_per_table开关 ，则每个表的文件表空间包含单个InnoDB表的数据和索引 ，并存储在文件系统上的单个数据文件中。
开关参数：innodb_file_per_table ，该参数默认开启。
那也就是说，我们每创建一个表，都会产生一个表空间文件，如图：
6.2.2.3 General Tablespaces
通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。
A. 创建表空间
CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = engine_name;
B. 创建表时指定表空间
CREATE TABLE xxx ... TABLESPACE ts_name;
6.2.2.4 Undo Tablespaces
撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志。
这里要需要说明一下，在MySQL5.6.3之前的版本中，这个undo tablespace是和system tablespace系统表空间存放在一起的，也就是没有单独的undo log文件，直接存放在ibdata1文件里边，在MySQL5.6.3之后的版本中，MySQL支持将undo log tablespace单独剥离出来，但这个特性依然很鸡肋：
- 要在安装数据库的时候，就指定好独立undo tablespace，在安装完成后不可更改；
- undo tablespace的space id必须从1开始，无法增加或者删除undo tablespace；
到了MySQL5.7版本，终于引入期待已久的功能：即在线truncate undo tablespace（解决了第一个鸡肋点，可以在安装数据库之后更改undo tablespace）
在MySQL8.0中，InnoDB再进一步，对undo log做了进一步的改进：
- 从8.0.3版本开始，默认undo tablespace的个数从0调整为2，也就是在8.0版本中，独立undo tablespace被默认打开。修改该参数为0会报warning并在未来不再支持；
- 无需从space_id 1开始创建undo tablespace，这样解决了In-place upgrade或者物理恢复到一个打开了Undo tablespace的实例所产生的space id冲突。不过依然要求undo tablespace的space id是连续分配的；
6.2.2.5 Temporary Tablespaces
InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。
6.2.2.6 Doublewrite Buffer Files
双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。
6.2.2.7 Redo Log
重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时,发生错误时, 进行数据恢复使用。
以循环方式写入重做日志文件，涉及两个文件：
6.2.3 后台线程
前面我们介绍了InnoDB的内存结构，以及磁盘结构，那么内存中我们所更新的数据，又是如何到磁盘中的呢？ 此时，就涉及到一组后台线程，接下来，就来介绍一些InnoDB中涉及到的后台线程。
在InnoDB的后台线程中，分为4类，分别是：Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread。
1). Master Thread
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收 。
2). IO Thread
在InnoDB存储引擎中大量使用了AIO来处理IO请求, 这样可以极大地提高数据库的性能，而IOThread主要负责这些IO请求的回调。
TODO
6.3 事务原理
6.3.1 事务基础
1). 事务
事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
2). 特性
原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
那实际上，我们研究事务的原理，就是研究MySQL的InnoDB引擎是如何保证事务的这四大特性的。
而对于这四大特性，实际上分为两个部分。 其中的原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。 而隔离性是通过数据库的锁，加上MVCC来保证的。
我们在讲解事务原理的时候，主要就是来研究一下redolog，undolog以及MVCC。
6.3.2 redo log
6.3.2.1 redo log 概述
重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。
如果没有redo log，可能会存在什么问题的？ 我们一起来分析一下。
我们知道，在InnoDB引擎中的内存结构中，主要的内存区域就是缓冲池，在缓冲池中缓存了很多的数据页。 当我们在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中，然后将缓冲池中的数据修改，修改后的数据页我们称为脏页。 而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。 而缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性。
那么，如何解决上述的问题呢？ 在InnoDB中提供了一份日志 redo log，接下来我们再来分析一下，通过redo log如何解决这个问题。
有了redo log之后，当对缓冲区的数据进行增删改之后，会首先将操作的数据页的变化，记录在redo log buffer中。在事务提交时，会将redo log buffer中的数据刷新到redo log磁盘文件中。过一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，此时就可以借助于redo log进行数据恢复，这样就保证了事务的持久性。而如果脏页成功刷写到磁盘或者涉及到的数据已经盘，此时redo log日志文件就没有作用了，就可以删除了，所以存在的两个redo log文件是循环写的。
那为什么每一次提交事务，要刷新redo log到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢 ?
因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。 而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。 这种先写日志的方式，称之为 WAL（Write-Ahead Logging）。
6.3.2.2 redo log日志刷盘策略
redo log的写入并不是直接写入磁盘的，InnoDB引擎会在写redo log的时候先写redo log buffer，之后以 一 定的频率刷到真正的redo log file 中。这里的一定频率怎么看待呢？这就是我们要说的刷盘策略。
注意，redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，只是刷入到 文件系统缓存（page cache）中去（这是现代操作系统为了提高文件写入效率做的一个优化），真正的写入会交给系统自己来决定（比如page cache足够大了）。那么对于InnoDB来说就存在一个问题，如果交给系统来同步，同样如果系统宕机，那么数据也丢失了（虽然整个系统宕机的概率还是比较小的）。 针对这种情况，InnoDB给出innodb_flush_log_at_trx_commit 参数，该参数控制 commit提交事务时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略：
- 设置为0 ：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日 志的同步）
- 设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）
- 设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件。
show variables like 'innodb_flush_log_at_trx_commit';
/*
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
*/
另外，InnoDB存储引擎有一个后台线程，每隔1秒，就会把 redo log buffer 中的内容写到文件系统缓存( page cache ) ，然后调用刷盘操作。
也就是说，一个没有提交事务的redo log 记录，也可能会刷盘。因为在事务执行过程redo log记录是会写入redo log buffer中，这些redo log记录会被后台线程刷盘。
除了后台线程每秒1次的轮询操作，还有一种情况，当redoa log buffer占用的空间即将达到innodb_log_buffer_size(这个参数默认是16M）的一半的时候，后台线程会主动刷盘。
6.3.2.3 redo log不同刷盘策略演示
innodb_flush_log_at_trx_commit=1
小结: innodb_flush_log_at_trx_commit=1 为1时，只要事务提交成功,redo log记录就一定在硬盘里，不会有任何数据丢失。 如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失。可以保证ACID的D，数据绝对不会丢失，但是效率最差的。 建议使用默认值，虽然操作系统宕机的概率理论小于数据库宕机的概率，但是一般既然使用了事务，那么数据的安全相对来说更重要些
innodb_flush_log_at_trx_commit=2
innodb_flush_log_at_trx_commit=0
小结: innodb_flush_log_at_trx_commit=o 为0时,master thread中每1秒进行一次重做日志的fsync操作，因此实例crash最多丢失1秒钟内的事务。( master thread是负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性) 数值0话，是一种折中的做法，它的IO效率理论是高于1的，低于2的，这种策略也有丢失数据的风险，也无法保证D。
总结：虽然用户可以通过设置参数innodb_flush_log_at_trx_commit为0或2来提高事务提交的性能，但需清楚，这种设置方法丧失了事务的ACID特性。
0 最快，2其次，性能相对1提高了接近50%。
6.3.2.4 redo log buffer写流程
MySQL把对底层页面中的一次原子访问的过程称之为一个Mini-Transaction，简称mtr，比如，向某个索引对应的B+树中插入一条记录的过程就是一个Nini-Transaction。一个所谓的mtr可以包含一组redo日志，在进行崩溃恢复时这一组redo日志作为一个不可分割的整体。
一个事务可以包含若干条语句，每一条语句其实是由若干个mtr组成，每一个mtr又可以包含若干条redo日志，画个图表示它们的关系就是这样:
向log buffer中写入redo日志的过程是顺序的，也就是先往前边的block中写，当该block的空闲空间用完之后再往下一个block中写。当想往log buffer中写入redo日志时，第一个遇到的问题就是应该写在哪个block的哪个偏移量处，所以InnoDB的设计者特意提供了一个称之为buf_free的全局变量，该变量指明后续写入的redo日志应该写入到 log buffer中的哪个位置，如图所示:
一个mtr执行过程中可能产生若干条redo日志，这些redo日志是一个不可分割的组，所以其实并不是每生成一条redo日志，就将其插入到log buffer中，而是每个mtr运行过程中产生的日志先暂时存到一个地方，当该mtr结束的时候，将过程中产生的一组redo日志再全部复制到log bulffer中。假设有两个名为T1、T2的事务，每个事务都包含2个mtr，我们给这几个mtr命名一下; 事务T1的两个mtr分别称为mtr_T1_1和mtr_T1_2 事务T2的两个mtr分别称为mtr_T2_1和mtr_T2_2
每个mtr都会产生一组redo日志，用示意图来描述一下这些mtr产生的日志情况:
不同的事务可能是并发执行的，所以T1、T2之间的mtr可能是交替执行的。每当一个mtr执行完成时，伴随该mtr生成的一组redo日志就需要被复制到log buffer中，也就是说不同事务的mtr可能是交替写入log buffer的，我们画个示意图(为了美观，把一个mtr中产生的所有的redo日志当作一个整体来画):
有的mtr产生的redo日志量非常大，比如1mtr_t1_2产生的redo日志占用空间比较大，占用了3个block来存储。
6.3.2.5 redo log block的结构图
一个redo log block是由日志头、日志体、日志尾组成。日志头占用12字节，日志尾占用8字节，所以一个block真正能存储的数据就是512-12-8=492字节。
为什么一个block设计成512字节? 这个和磁盘的扇区有关，机械磁盘默认的扇区就是512字节，如果要写入的数据大于512字节，那么要写入的扇区肯定不止一个，这时就要涉及到盘片的转动，找到下一个扇区，假设现在需要写入两个扇区A和B，如果扇区A写入成功，而扇区B写入失败，那么就会出现非原子性的写入，而如果每次只写入和扇区的大小一样的512字节,那么每次的写入都是原子性的
真正的redo日志都是存储到占用496字节大小的log block body中，图中的log block header和log block trailer存储的是一些管理信息。我们来看看这些所谓的管理信息都有什么
log block header的属性分别如下:
- LOG_BLOCK_HDR_NO : log buffer是由log block组成，在内部log buffer就好似一个数组，因此LOG_BLOCK_HDR_NO用来标记这个数组中的位置。其是递增并且循环使用的，占用4个字节，但是由于第—位用来判新是否是flush bit，所以最大的值为2G。
- LOG_BLOCK_HDR_DATA_LEN∶表示block中已经使用了多少字节，初始值为12(因为log block body从第12个字节处开始)。随着往block中写入的redo日志越来也多，本属性值也跟着增长。如果log block body已经被全部写满,那么本属性的值被设置为512
- LOG_BLOCK_FIRST_REC_GROUP :一条redo日志也可以称之为一条redo日志记录（redo log record)，一个mtr会生产多条redo日志记录，这些redo日志记录被称之为一个redo日志记录组(redo log record group)。LOG_BLOCK_FIRST_REC_GROUP就代表该block中第一个mtr生成的redo日志记录组的偏移量(其实也就是这个block里第一个mtr生成的第一条redo日志的偏移量)。如果该值的大小和LOG_BLOCK_HDR_DATA_LEN相同，则表示当前log block不包含新的日志。
LOG_BLOCK_CHECKPOINT_NO:占用4字节，表示该log block最后被写入时的checkpoint。 log block trailer中属性的意思如下:
- LOG_BLOCK_CHECKSUN:表示block的校验值，用于正确性校验（其值和LOG_BLOCK_HDR_NO相同)，暂时不关心它。
6.3.3 undo log
redo log是事务持久性的保证，undo log是事务原子性的保证。在事务中更新数据的前置操作其实是要先写入一个undo log
6.3.3.1 如何理解Undo日志
事务需要保证原子性，也就是事务中的操作要么全部完成，要么什么也不做。但有时候事务执行到一半会出现一些情况，比如：
- 情况一：事务执行过程中可能遇到各种错误，比如 服务器本身的错误 ， 操作系统错误 ，甚至是突然 断电 导致的错误。
- 情况二：程序员可以在事务执行过程中手动输入 ROLLBACK 语句结束当前事务的执行。
以上情况出现，需要把数据改回原先的样子，这个过程称之为回滚 ，这样就可以造成一个假象：这个事务看起来什么都没做，所以符合原子性要求
每当我们要对一条记录做改动时(这里的改动可以指INSERT、DELETE、UPDATE ），都需要"留一手"—>把回滚时所需的东西记下来。比如:
- 你插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删除就好了（对于每个INSERT，InnoDB存储引擎会完成一个DELETE)
- 你删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。(对于每个DELETE，InnoDB存储引擎会执行一个INSERT)
- 你修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。(对于每个UPDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去)
MySQL把这些为了回滚而记录的这些内容称之为撒销日志或者回滚日志(即undo log)。注意，由于查询操作( SELECT）并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的undo日志
此外，undo log 会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护
6.3.3.2 undo log的作用
作用1：回滚数据
用户对undo日志可能有误解:undo用于将数据库物理地恢复到执行语句或事务之前的样子。但事实并非如此。undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。
这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。
作用2：MVCC
undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取
6.3.3.2 undo log的存储结构
【回滚段与undo页】
InnoDB对undo log的管理采用段的方式，也就是回滚段（rollback segment） 。每个回滚段记录了1024个 undo log segment，而在每个undo log segment段中进行undo页的申请。
- 在 InnoDB1.1版本之前(不包括1.1版本)，只有一个rollback segment，因此支持同时在线的事务限制为1024。虽然对绝大多数的应用来说都已经够用。
- 从1.1版本开始InnoDB支持最大 128个rollback segment ，故其支持同时在线的事务限制提高到 了 128*1024
show variables like 'innodb_undo_logs';
/*
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_undo_logs | 128 |
+------------------+-------+
*/
虽然InnoDB1.1版本支持了128个rollback segment，但是这些rollback segment都存储于共享表空间ibdata中。从InnoDB1.2版本开始，可通过参数对rollback segment做进一步的设置。这些参数包括:
- innodb_undo_directory:设置rollback segment文件所在的路径。这意味若rollback segment可以存放在共享表空间以外的位置，即可以设置为独立表空间。该参数的默认值为“.”，表示当前InnoDB存储引擎的目录
- innodb_undo_logs:设置rollback segment的个数，默认值为128。在InnoDB1.2版本中，该参数用来替换之前版本的参数innodb_rollback_segments。
- innodb_undo_tablespaces:设置构成rollback segment文件的数目，这样rollback segment可以较为平均地分布在多个文件中。设置该参数后，会在路径innodb_undo_directory看到undo为前缀的文件，该文件就代表rollback segment文件
undo log相关参数一般很少改动。
【undo页的重用】
当开启一个事务需要写undo log的时候，就得先去undo log segment中去找到一个空闲的位置，当有空位的时候，就去申请undo页，在这个申请到的undo页中进行undo log的写入。我们知道mysql默认一页的大小是16k。为每一个事务分配一个页，是非常浪费的（除非你的事务非常长)，假设你的应用的TPS(每秒处理的事务数目)为1000，那么1s就需要1000个页大概需要16M的存储，1分钟大概需要1G的存储。如果照这样下去除非MySQL清理的非常勤快，否则随着时间的推移，磁盘空间会增长的非常快，而且很多空间都是浪费的。
于是undo页就被设计的可以重用了，当事务提交时，并不会立刻删除undo页。因为重用，所以这个undo页可能混杂着其他事务的undo log。undo log在commit后，会被放到一个链表中，然后判断undo页的使用空间是否小于3 /4，如果小于3/4的话，则表示当前的undo页可以被重用，那么它就不会被回收，其他事务的undo log可以记录在当前undo页的后面。由于undo log是离散的，所以清理对应的磁盘空间时，效率不高。
【回滚段与事务】
- 每个事务只会使用一个回滚段，一个回滚段在同一时刻可能会服务于多个事务。
- 当一个事务开始的时候，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数据会被复制到回滚段。
- 在回滚段中，事务会不断填充盘区，直到事务结束或所有的空间被用完。如果当前的盘区不够用，事务会在段中请求扩展下一个盘区，如果所有已分配的盘区都被用完，事务会覆盖最初的盘区或者在回滚段允许的情况下扩展新的盘区来使用
- 回滚段存在于undo表空间中，在数据库中可以存在多个undo表空间，但同一时刻只能使用一个undo表空间
- 当事务提交时， lnnoDB存储引擎会做以下两件事情：
- 将undo log放入列表中，以供之后的purge操作
- 判断undo log所在的页是否可以重用，若可以分配给下个事务使用
【回滚段中的数据分类】
未提交的回滚数据(uncommitted undo information) 该数据所关联的事务并未提交，用于实现读一致性，所以该数据不能被其他事务的数据覆盖
已经提交但未过期的回滚数据(committed undo information) 该数据关联的事务已经提交，但是仍受到undo retention参数的保持时间的影响
事务已经提交并过期的数据(expired undo information) 事务已经提交，而且数据保存时间已经超过undo retention参数指定的时间，属于已经过期的数据。当回滚段满了之后，会优先覆盖"事务已经提交并过期的数据"
事务提交后并不能马上删除undo log及undo log所在的页。这是因为可能还有其他事务需要通过undo log来得到行记录之前的版本。故事务提交时将undo log放入一个链表中，是否可以最终删除undo log及undo log所在页由purge线程来判断
6.3.3.3 undo log的类型
在InnoDB存储引擎中，undo log分为：
insert undo log insert undo log是指在insert操作中产生的undo log。因为insert操作的记录，只对事务本身可见，对其他事务不可见(这是事务隔离性的要求)，故该undo log可以在事务提交后直接删除。不需要进行purge操作
update undo log update undo log记录的是对delete和update操作产生的undo log，该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除
6.3.3.4 undo log的生命周期
【简要生成过程】
以下是undo+redo事务的简化i过程
假设有2个数值，分别为A=1和B=2，然后将A修改为3,B修改为4
- start transaction ;
2．记录A=1到undo log;
- update A = 3;
4.记录A=3 到redo log;
5．记录B=2到undo log;
- update B = 4;
7.记录B =4到redo log;
8．将redo log刷新到磁盘
- commit
- 在1-8步骤的任意一步系统宕机，事务未提交，该事务就不会对磁盘上的数据做任何影响
- 如果在8-9之间宕机，恢复之后可以选择回滚，也可以选择继续完成事务提交，因为此时redo log已经持久化
- 若在9之后系统宕机，内存映射中变更的数据还来不及刷回磁盘，那么系统恢复之后，可以根据redo log把数据刷回磁盘
只有Buffer Pool的流程：
有了Redo Log和Undo Log之后：
在更新Buffer Pool中的数据之前，需要先将该数据事务开始之前的状态写入Undo Log中。假设更新到一半出错了，就可以通过Undo Log来回滚到事务开始前。
【详细生成过程】
对于InnoDB引擎来说，每个行记录除了记录本身的数据之外，还有几个隐藏的列:
- DB_ROW_ID:如果没有为表显式的定义主键，并且表中也没有定义唯一索引，那么InnoDB会自动为表添加一个row_id的隐藏列作为主键
- DB_TRX_ID:每个事务都会分配一个事务ID，当对某条记录发生变更时，就会将这个事务的事务ID写入trx_id中
- DB_ROLL_PTR;回滚指针，本质上就是指句undo log的指针
当我们执行INSERT时：
begin;
INSERT INTO user (name) VALUES ("tom");
插入的数据都会生成一条insert undo log，并且数据的回滚指针会指向它。undo log会记录undo log的序号、插入主键的列和值…。那么在进行rollback的时候，通过主键直接把对应的数据删除即可
当我们执行UPDATE时：
对于更新的操作会产生update undo log，并且会分更新主键的和不更新主键的，假设现在执行:
UPDATE user SET name= "Sun" WHERE id=1;
这时会把老的记录写入新的undo log，让回滚指针指向新的undo log，它的undo no是1，并且新的undo log会指向老的undo log (undo no=0) .假设现在执行:
UPDATE user SET id=2 WHERE id=1;
对于更新主键的操作，会先把原来的数据deletemark标识打开，这时并没有真正的删除数据，真正的删除会交给清理线程去判断，然后在后面插入一条新的数据，新的数据也会产生undo log，并且undo log的序号会递增
可以发现每次对数据的变更都会产生一个undo log，当一条记录被变更多次时，那么就会产生多条undo log,undo log记录的是变更前的日志，并且每个undo log的序号是递增的，那么当要回滚的时候，按照序号依次向前推，就可以找到原始数据
【undo log是如何回滚的】
以上面的例子来说，假设执行rollback，那么对应的流程应该是这样：
- 通过undo no=3的日志把id=2的数据删除
- 通过undo no=2的日志把id=1的数据的deletemark还原成0
- 通过undo no=1的日志把id=1的数据的name还原成Tom
- 通过undo no=0的日志把id=1的数据删除
【undo log的删除】
针对于insert undo log 因为insert操作的记录，只对事务本身可见，对其他事务不可见。故该undo log可以在事务提交后直接删 除，不需要进行purge操作。
针对于update undo log 该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。
补充:purge线程两个主要作用是:清理undo页和清除page里面带有Delete_Bit标识的数据行。在InnoDB中，事务中的Delete操作实际上并不是真正的删除掉数据行，而是一种Delete Mark操作，在记录上标识Delete_Bit，而不删除记录。是一种"假删除";只是做了个标记，真正的删除工作需要后台purge线程去完成
6.3.3.5 小结
- undo log是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子
- redo log是物理日志，记录的是数据页的物理变化，undo log不是redo log的逆过程
6.4 MVCC
6.4.1 基本概念
6.4.1.1 当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode(共享锁)，select ... for update、update、insert、delete(排他锁)都是一种当前读。
测试：
在测试中我们可以看到，即使是在默认的RR隔离级别下，事务A中依然可以读取到事务B最新提交的内容，因为在查询语句后面加上了 lock in share mode 共享锁，此时是当前读操作。当然，当我们加排他锁的时候，也是当前读操作。
6.4.1.2 快照读
简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
Read Committed：每次select，都生成一个快照读。
Repeatable Read：开启事务后第一个select语句才是快照读的地方。
Serializable：快照读会退化为当前读。
测试:
在测试中,我们看到即使事务B提交了数据,事务A中也查询不到。 原因就是因为普通的select是快照读，而在当前默认的RR隔离级别下，开启事务后第一个select语句才是快照读的地方，后面执行相同的select语句都是从快照中获取数据，可能不是当前的最新数据，这样也就保证了可重复读。
6.4.1.3 MVCC
全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。
接下来，我们再来介绍一下InnoDB引擎的表中涉及到的隐藏字段 、undolog 以及 readview，从而来介绍一下MVCC的原理。
6.4.2 隐藏字段
6.4.2.1 介绍
当我们创建了上面的这张表，我们在查看表结构的时候，就可以显式的看到这三个字段。 实际上除了这三个字段以外，InnoDB还会自动的给我们添加三个隐藏字段及其含义分别是：
| 隐藏字段 | 含义 |
|---|---|
| DB_TRX_ID | 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。 |
| DB_ROLL_PTR | 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。 |
| DB_ROW_ID | 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。 |
而上述的前两个字段是肯定会添加的， 是否添加最后一个字段DB_ROW_ID，得看当前表有没有主键，如果有主键，则不会添加该隐藏字段。
6.4.2.2 测试
查看有主键的表 stu
进入服务器中的 /var/lib/mysql/itcast/ , 查看stu的表结构信息, 通过如下指令:
ibd2sdi stu.ibd
查看到的表结构信息中，有一栏 columns，在其中我们会看到处理我们建表时指定的字段以外，还有额外的两个字段 分别是：DB_TRX_ID 、 DB_ROLL_PTR ，因为该表有主键，所以没有DB_ROW_ID隐藏字段。
查看没有主键的表 employee
建表语句：
create table employee (id int , name varchar(10));
此时，我们再通过以下指令来查看表结构及其其中的字段信息：
ibd2sdi employee.ibd
查看到的表结构信息中，有一栏 columns，在其中我们会看到处理我们建表时指定的字段以外，还有额外的三个字段 分别是：DB_TRX_ID 、 DB_ROLL_PTR 、DB_ROW_ID，因为employee表是没有指定主键的。
6.4.3 undo log
回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。 而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。
6.4.3.1 版本链
有一张表原始数据为：
DB_TRX_ID : 代表最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID，是自增的。
DB_ROLL_PTR ： 由于这条数据是才插入的，没有被更新过，所以该字段值为null。
然后，有四个并发事务同时在访问这张表。
A. 第一步
当事务2执行第一条修改语句时，会记录undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本。
B.第二步
当事务3执行第一条修改语句时，也会记录undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本。
C. 第三步
当事务4执行第一条修改语句时，也会记录undo log日志，记录数据变更之前的样子; 然后更新记录，并且记录本次操作的事务ID，回滚指针，回滚指针用来指定如果发生回滚，回滚到哪一个版本。
最终我们发现，不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条
记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。
6.4.4 readview
ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。
ReadView中包含了四个核心字段：
| 字段 | 含义 |
|---|---|
| m_ids | 当前活跃的事务ID集合 |
| min_trx_id | 最小活跃事务ID |
| max_trx_id | 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的） |
| creator_trx_id | ReadView创建者的事务ID |
而在readview中就规定了版本链数据的访问规则：
trx_id 代表当前undo log版本链对应事务ID。
| 条件 | 是否可以访问 | 说明 |
|---|---|---|
| trx_id ==creator_trx_id | 可以访问该版本 | 成立，说明数据是当前这个事务更改的。 |
| trx_id < min_trx_id | 可以访问该版本 | 成立，说明数据已经提交了。 |
| trx_id > max_trx_id | 不可以访问该版本 | 成立，说明该事务是在ReadView生成后才开启。 |
| min_trx_id <= trx_id <= max_trx_id | 如果trx_id不在m_ids中，是可以访问该版本的 | 成立，说明数据已经提交。 |
不同的隔离级别，生成ReadView的时机不同：
READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。
【ReadView的规则】
有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见。
- 如果被访问版本的trx_id属性值与ReadView中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
- 如果被访问版本的trx_id属性值小于ReadView中的 up_limit_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
- 如果被访问版本的trx_id属性值大于或等于ReadView中的 low_limit_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问
- 如果被访问版本的trx_id属性值在ReadView的 up_limit_id 和 low_limit_id之间，那就需要判断一下trx_id属性值是不是在 trx_ids 列表中。
- 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。
- 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。
6.4.5 原理分析
6.4.5.1 RC隔离级别
RC隔离级别下，在事务中每一次执行快照读时都会生成ReadView。
我们就来分析事务5中，两次快照读读取数据，是如何获取数据的?
在事务5中，查询了两次id为30的记录，由于隔离级别为Read Committed，所以每一次进行快照读都会生成一个ReadView，那么两次生成的ReadView如下。
那么这两次快照读在获取数据时，就需要根据所生成的ReadView以及ReadView的版本链访问规则，到undolog版本链中匹配数据，最终决定此次快照读返回的数据。
先来看第一次快照读具体的读取过程：
在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配，在遇到第一条复合的记录后，就直接返回了
这里返回的是0x00002对应的记录
这么多判断就是为了判断一件事：
当前记录对应的事物id是否是活跃的事物id，如果活跃，就不可以访问，反之，可以访问
B. 再来看第二次快照读具体的读取过程:
在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配，在遇到第一条复合的记录后，就直接返回了
这里返回的是0x00003对应的记录
6.4.5.2 RR隔离级别
RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。 而RR是可重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的。
那MySQL是如何做到可重复读的呢? 我们简单分析一下就知道了
我们看到，在RR隔离级别下，只是在事务中第一次快照读时生成ReadView，后续都是复用该ReadView，那么既然ReadView都一样， ReadView的版本链匹配规则也一样， 那么最终快照读返回的结果也是一样的。
所以呢，MVCC的实现原理就是通过 InnoDB表的隐藏字段、UndoLog 版本链、ReadView来实现的。而MVCC + 锁，则实现了事务的隔离性。 而一致性则是由redolog 与 undolog保证。
七、进阶篇-MySQL管理
系统数据库
Mysql数据库安装完成后，自带了一下四个数据库，具体作用如下：
| 数据库 | 含义 |
|---|---|
| mysql | 存储MySQL服务器正常运行所需要的各种信息 （时区、主从、用户、权限等） |
| information_schema | 提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等 |
| performance_schema | 为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数 |
| sys | 包含了一系列方便 DBA 和开发人员利用 performance_schema性能数据库进行性能调优和诊断的视图 |
常用工具
nysql
该mysql不是指mysql服务，而是指mysql的客户端工具。
语法 ：
mysql [options] [database]
选项 ：
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口
-e, --execute=name #执行SQL语句并退出
-e选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便。
mysqladmin
mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。
通过帮助文档查看选项：
mysqladmin --help
语法:
mysqladmin [options] command ...
选项:
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口
mysqlbinlog
由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具。
语法 ：
mysqlbinlog [options] log-files1 log-files2 ...
选项 ：
-d, --database=name 指定数据库名称，只列出指定的数据库相关操作。
-o, --offset=# 忽略掉日志中的前n行命令。
-r,--result-file=name 将输出的文本格式日志输出到指定文件。
-s, --short-form 显示简单格式， 省略掉一些信息。
--start-datatime=date1 --stop-datetime=date2 指定日期间隔内的所有日志。
--start-position=pos1 --stop-position=pos2 指定位置间隔内的所有日志。
mysqlshow
mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。
语法 ：
mysqlshow [options] [db_name [table_name [col_name]]]
选项 ：
--count 显示数据库及表的统计信息（数据库，表 均可以不指定）
-i 显示指定数据库或者指定表的状态信息
示例：
#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p2143 test --count
#查询test库中book表的详细情况
mysqlshow -uroot -p2143 test book --count
mysqldump
mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。
语法 ：
mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A
连接选项 ：
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器ip或域名
-P, --port=# 指定连接端口
输出选项：
--add-drop-database 在每个数据库创建语句前加上 drop database 语句
--add-drop-table 在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不 开启 (--skip-add-drop-table)
-n, --no-create-db 不包含数据库的创建语句
-t, --no-create-info 不包含数据表的创建语句
-d --no-data 不包含数据
-T, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；一 个.txt文件，数据文件
mysqlimport/source
mysqlimport： 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件。
语法 ：
mysqlimport [options] db_name textfile1 [textfile2...]
示例 ：
mysqlimport -uroot -p2143 test /tmp/city.txt
source：如果需要导入sql文件,可以使用mysql中的source 指令 :
语法 ：
source /root/xxxxx.sql