1

参考

《MySQL技术内幕》

表

表是关于特定实体的数据集合，也是关系型数据库模型的核心

索引组织表

在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式
的表称为索引组织表。每张表都有一个主键

• 首先判断表中是否有非空的唯一索引，如果有则该列为主键。当有多个
  非空唯一索引时，将选择建表时第一个定义的非空唯一索引为主键
• 如果不符上述条件则InnoDB自动创建一个6字节大小的指针

接下来用一个表来说明

mysql> select *,_rowid from z;
+---+------+----+----+--------+
| a | b    | c  | d  | _rowid |
+---+------+----+----+--------+
| 1 |    2 |  3 |  4 |      4 |
| 5 |    6 |  7 |  8 |      8 |
| 9 |   10 | 11 | 12 |     12 |
+---+------+----+----+--------+

_rowid可以显示表的主键，但只能显示单个列为主键的情况

InnoDB逻辑存储结构

之前在MySQL2中已经讲了表空间文件存放所有数据。表空间又由段、区、页
组成，页在某些文档也称为块

表空间

在默认情况下InnoDB存储引擎有一个共享表空间ibdata1，即所有的数据都存放
在这个表空间中，如果用户启动参数innodb_file_per_table，则每张表内的
数据可以单独存放到一个表空间中，注意这里存放的只是数据、索引和插入缓冲
Bitmap页，其他类的数据还是在共享表空间中，比如回滚信息undo log，插入
缓冲索引页，系统事务信息，二次写缓冲

段

上图已经说明表空间是由各个段组成，常见的段有数据段、索引段、回滚段等。
数据段即为B+树的叶子节点，索引段即为B+树的非叶子节点

区

区是由连续的页组成，在任何情况下每个区的大小都为1MB，为了保证区中页的
连续性，InnoDB存储引擎一次磁盘申请4~5个区，一个区有64个连续的页

页

页是InnoDB磁盘管理的最小单位，在InnoDB存储引擎中默认每个页的大小是
16KB。现在版本可以通过innodb_page_size将页的大小设置为4K、8K、16K。
常见的页类型如下

• 数据页，数据即索引，索引即数据
• undo页
• 系统页
• 事务数据页
• 插入缓冲位图页
• 插入缓冲空闲列表页
• 未压缩的二进制大对象页
• 压缩的二进制大对象页

行

InnoDB存储引擎是面向列的，也就是说数据是按照行进行存放的。每个页最多
存放 16KB/2-200=7992 行记录

InnoDB行记录格式

页中存放的是表中一行行数据，InnoDB存储引擎提供两种格式来存放行记录数
据，Compact和Redundant

Compact

一个页中存放的数据越多性能越高
Compact行记录格式的首部是一个非NULL变长字段长度列表，并且按照列的顺序
逆序放置

• 若列的长度小于255字节，用1字节表示
• 若大于255字节，用2字节表示，因为VARCHAR类型最大长度是65535

第二个部分是NULL标志位，用于指示该行数据是否有NULL值，有则用1表示，
第三个部分是记录头信息，固定占5个字节
根据头信息最后两个字节可以知道下条记录的位置，InnoDB存储引擎在页内部
是通过一种链表的结构来串连各个行记录的。
最后的部分就是实际存储每个列的数据，注意NULL不占该部分空间，还有一点
是除了用户定义的列外，还有两个隐藏列：事务ID和回滚指针列，分别为6字
节和7字节大小，若InnoDB表没有主键则还会增加一个rowid列

Redundant

Redundant是老版本的行记录存储方式

行溢出数据

InnonDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外，
比如BLOB LOB这类大对象列类型会存储在数据页面之外

Dynamic

支持Compact和Redundant格式的称为Antelope文件格式，新的文件格式
Barracuda支持Compressed和Dynamic

InnoDB数据页结构

InnoDB数据页由7个部分组成

约束

关系型数据库系统和文件系统的一个不同点是关系数据库本身能保证存储
数据的完整性，约束机制可以保证数据的完整性。数据完整性有以下三种
形式

1. 实体完整性保证表中有一个主键。在InnoDB 中可以通过定义primary
   key或Unique Key来保证实体完整性。还可以通过编写一个触发器来保证
   实体完整性
2. 域完整性保证数据每列的值满足特定条件。可以通过以下方式来保证

• 选择合适数据类型
• 外键约束
• 编写触发器
• 用DEFAULT约束作为强制域完整性的一个方面

3. 参考完整性保证两张表之间的关系，可以通过定义外键或编写触发器

InnoDB存储引擎提供的约束如下

• Primary Key
• Unique Key
• Foreign Key
• Default
• NOT NULL

约束的创建和查找

约束的创建有两种方式

1. 表建立时就进行约束定义
2. 使用alter table创建约束，Unique Key还可以通过create unique
   index 来创建

   create unique index bb on z(b);

主键约束默认约束名为PRIMARY，Unique Key约束默认约束名为列名

select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS
where table_schema='mytest' and table_name='z';

约束和索引的区别

约束是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，
既有逻辑的概念，在数据库中还代表物理存储的方式

对错误数据的约束

在某些默认设置下MySQL数据库允许非法或不正确的数据的插入或更新，可以在
内部将其转化为一个合法的值，如向NOT NULL的字段插入一个NULL值，MySQL
会将其更改为0再插入，可以设置sql_mode保证数据库提示报错而不是警告

set sql_mode='STRICT_TRANS_TABLES'

ENUM和SET

MySQL数据库不支持传统的CHECK约束，但是可以通过ENUM和SET类型解决这个
需求，例如有一个性别类型，范围只能是male或female

create table a(
		id int,
		sex enum('male','female')
	);
insert into a select 22,female';

触发器与约束

完整性约束通常也可以使用触发器来实现，触发器的作用就是在执行INSERT
DELETE和UPDATE命令之前或之后自动调用SQL命令或存储过程。创建触发器
的命令是create trigger

CREATE
[DEFINER={user|CURRENT_USER}]
TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE
ON tb1_name FOR EACH ROW trigger_stmt

最多可以为一个表建立6个触发器，分别为 INSERT UPDATE DELETE的BEFORE
和AFTER各定义一个。BEFORE和AFTER 代表触发器发生的时间，表示在每行操
做之前还是之后发生。通过触发器可以实现MySQL 数据库本身并不支持的一些
特性。假设有张用户表，用户每次购买物品后金额都是减的，但是如果有一个
减去负值的操作，那么用户的钱反而会增多，语句本身没有问题但是逻辑错误

create table usercash(
	userid int not null,
	cash int not null);
insert into usercash values(1,1000);
update usercash set cash=cash-(-20) where userid=1;

这时需要一个触发器来约束这个逻辑行为

CREATE TABLE tgr_usercash_update BEFORE UPDATE ON usercash(
	FOR EACH ROW
	BEGIN 
	IF new.cash-oil.cash > 0 THEN
	INSERT INTO usercash_err_log
	SELECT old.userid, old.cash, new.cash,USER(),NOW();
	SET new.cash = old.cash;
	END IF;
	END;
	$$
);
DELIMITER $$;

外键约束

MyISAM不支持外键，InnoDB支持外键

CREATE TABLE parent(
id INT NOT NULL,
PRIMARY KEY(id)
)ENGINE = INNODB;

CREATE TABLE child(
id INT, parent_id INT,
FOREIGN KEY(parent_id) REFERENCES parent(id)
)ENGINE=INNODB;

分区表

InnoDB MyISAM NDB支持分区。
分区的过程是将一个表或索引分解为更小、更可管理的部分。就访问数据库而言
就一个表或一个索引，但是在物理上这个表或索引可以有数十个物理分区组成
，每个分区都是独立的对象，MySQL支持的分区类型是水平分区，不支持垂直
分区，此外MySQL的分区是局部分区索引，一个分区中既存放了数据又存放了
索引。全局分区指的是数据存放在各个分区，但是所有数据的索引存放在一个
对象中

• 水平分区 同一个表中不同行的记录分配到不同物理文件中
• 垂直分区 同一个表中不同列的记录分配到不同物理文件中

不管创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是
唯一索引的一个组成部分。如果建表时没有指定主键或唯一索引，可以指定
任何一个列为分区列

RANGE分区

行数据基于一个给定连续区间的列值被放入分区，以下语句创建一个id列的
区间分区表，id小于10时数据插入p0分区，id在10到20之间数据插入p1分
区，启用分区后表就不止一个ibd文件，t#P#p0.ibd t#P#p1.ibd

create table t(
id int)engine=innodb
partition by range(id)(
partition p0 values less than (10),
partition p1 values less than (20));

如果插入的数据不在分区中可以对分区添加一个MAXVALUE值的分区

alter table t add partition(partition p2 values less than maxvalue);
# 删除一个分区
alter table t drop partition p2;

RANGE分区主要用于日期列的分区，例如对于销售类的表可以根据年来分区
存放销售记录。创建分区的好处是可以加快某些查询操作，通过 explain
SQL优化器只需搜索p1这个分区即可，这就是Partitions Pruning分区
修剪，如果改为id<20那么会搜索p1 p2两个分区，所以注意写法

explain partitions
select * from t where id>=10 and id<=19;

需要注意实际上SQL优化器并不会对以上的写法进行分区，为了好讲解我才
这样写，书中写对于RANGE分区的查询，优化器只能对YEAR() TO_DAYS()
TO_SECONDS() UNIX_TIMESTAMP()这类函数进行优化选择

# 比如date表示日期
partition by range(YEAR(date))(...);

LIST分区

和RANGE分区类似，只是LIST面向的是离散的值，而非连续的

create table t(
a int,b int)engine=innodb
partition by list(b)(
partition p0 values in (1,3,5,7,9),
partition p1 values in (0,2,4,6,8));

在插入多个行数据的过程遇到分区未定义的值时，MyISAM和InnoDB存储引
擎的处理完全不同，MyISAM会将之前的行数据都插入但是之后的不会插入
，而InnoDB存储引擎会将其视为一个事务，也就是说如果有未定义则所有
的插入都无效

HASH分区

根据用户自定义的表达式的返回值来进行分区，目的是将数据均匀分布到
预先定义的各个分区中，保证各分区的数据量大致都是一样的。在RANGE
和LIST分区中必须明确指定一个给定的列值或列值集合应该保存在哪个
分区中，在HASH分区中需要指定一个列值或表达式以及被指定分区的表
将要被分割成的分区数量

create table t(
a int,b datetime)engine=innodb
partition by hash(year(b)) partitions 4;
# 2010 mod 4=2 记录会放到p2分区中

KEY分区

根据MySQL提供的哈希函数进行分区

create table t(
a int,b datetime)engine=innodb
partition by key(year(b)) partitions 4;

COLUMNS分区

前四种分区的条件是数据必须是整型或者转化为整型，COLUMNS可以直接对
非整型的数据进行分区，也可以对多个列的值进行分区

create table t(
a int,b datetime)engine=InnoDB
partition by range columns(B)(
partition p0 values less than ('2009-01-01'));
create table t(
a int,b datetime)engine=InnoDB
partition by range columns(a,B)(
partition p0 values less than (5,'2009-01-01'));

子分区

是在分区的基础上再进行分区，有时也称这种分区为复合分区。MySQL允许在
RANGE和LIST的分区上再进行HASH或KEY的子分区

分库分表

mycat shardingsphere drds

索引与算法

索引并不是越多越好，在一开始就应该在需要处添加索引，MySQL 支持如下
索引

• B+树索引 B+树索引并不能找到一个给定键值的具体行，而是找到行所在
  的页，然后数据库把页读入到内存中
• 全文索引 基于相似度的查询
• 哈希索引 会根据表的使用情况自动为表生成哈希索引

数据结构与算法

索引是帮助Mysql 高效获取数据的数据结构。数据库的一个重要功能就是查询，
所以数据库系统设计者就会从查询算法的角度进行优化。最容易想到的就是顺序
查询，就好像遍历一个数组一样，时间复杂度为O(n)，当数据量非常大的时候
效果不好。学习数据结构后就知道还有其他的查询算法，比如二分查找二叉树
查找等，但是这些算法与特定的数据结构有关，比如二分查找必须保证数据
是有序的，二叉树查找必须建立二叉查找树结构，但是数据本身可能无法满
足各种数据结构，数据库系统维护满足特定查找算法的数据结构，这种数据
结构以某种方式指向数据，这种数据结构就是索引

二分查找法

每页Page Directory中的槽是按照主键的顺序存放，对于某一条具体记录的
查询时通过对Page Direcotry进行二分查找得到

B+树

B+树的插入分为三种情况
当插入28时满足第一种情况，所以直接插入到叶子节点
继续插入70满足第二种情况，这时插入Leaf Page后的顺序为 50 55 60 65
70，中间值是60所以放到分页节点
最后插入95满足第三种情况，首先拆分叶子节点 75 80 85 90 95，中间值是
85将小于85的放在左边大于85的放在右边，然后拆分分页节点，25 50 60 75
85，中间值是60，小于60的放在左边大于60的放在右边
B+树为了保持平衡可能会做大量拆分页的操作，因为B+树结构主要用于磁盘，
所以提供了旋转来减少拆分。旋转发生在Leaf Page已经满当左右兄弟节点没有
满的情况，这时不会拆分而是将记录移到兄弟节点上，通常情况左新兄弟先进
行旋转操作，现在插入70
B+树使用填充因子来控制树的删除变化，50%是填充因子可设的最小值，B+树
的删除也分为三种情况

B+树索引

B+树索引的本质就是B+树在数据库中的实现，B+索引在数据库中有一个特点就
是高扇出性，因此在数据库中B+树的高度一般在2~4层，也就是说查找某一键
值的行记录最多只需2到4次，B+索引可以分为聚集索引和辅助索引

聚集索引

聚集索引是按照每一张表的主键构造一棵B+树，叶子节点存放的就是整张表的
行记录数据，也将叶子节点称为数据页，聚集索引这个特性决定了索引组织表
中数据也是索引的一部分。每张表只能有一个聚集索引，多数情况下查询优化
器倾向采用聚集索引。聚集索引并不是顺序物理地存储数据，而是在逻辑上连
续的

辅助索引

也称为非聚集索引，叶子节点并不包含行记录的所有数据，叶子节点除了包含
键值以外，每个叶子节点的索引行还包含一个书签(bookmark)，该书签用来
告诉InnoDB存储引擎在哪里可以找到与索引相对应的行数据，这个书签实际
就是相应的聚集索引键。每张表可以有多个辅助索引，先通过辅助索引找到
相应的主键索引，再通过主键索引找到一个完整的行记录

索引的使用

根据OLTP和OLAP两种应用

联合索引

对表的多个列进行索引，联合索引也是一棵B+树，不同的是联合索引的键值
的数量不是1而是大于等于2
根据这棵B+树可以知道以下查询语句可以使用联合索引

select * from table where a=xxx and b=xxx
select * from table where a=xxx

但是对于b列的查询不能使用联合索引，因为b值在叶子节点中并不是顺序的

select * from table where b=xxx

组合索引的结构

当表中的字段全部都是索引列的时候，无论进行怎样的查询都会使用
索引，索引并不是越多越好，也不需要在全部列上加索引。关于为什
么字段全是索引列的时候都会使用索引，举个栗子

id是主键 a,b,c是组合索引
当根据a b c查询整行数据时一定会用到索引，因为组合索引abc对应
的就是主键id，此时不需要回表就可以查询到所有数据
如果增加一个字段d，此时根据组合索引abc查询到对应id，然后根据
id查询到整行数据，如果直接顺序查询的效率高于使用索引查询，那
么就不会根据索引查询

覆盖索引

即从辅助索引中就可以得到查询的记录，不需要查询聚集索引的记录。覆盖
索引的另一个好处是对于某些统计问题而言，索引包含所有需要查询的字
段的值

select count(*) from buy_log;

InnoDB并不会选择通过查询聚集索引来进行统计，由于buy_log表上还有
辅助索引，而辅助索引远小于聚集索引，所以会选择辅助索引。优点如下

1. 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量
2. 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作
   系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）
3. 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引

哈希算法

InnoDB存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式
，哈希函数采用的是除法散列方式

全文检索

可以通过索引字段的前缀进行查找。通常使用倒排索引来实现，倒排索引同
B+树索引一样是一种索引结构，它在辅助表中存储了单词与单词自身在一
个或多个文档中所在位置之间的映射，这通常使用关联数组实现，有两种
形式

1. inverted file index 表现形式为{单词，单词所在文档的ID}
2. full inverted index 表现形式为{单词，(单词所在文档的ID，在具
   体文档中的位置)}

select * from blog where content like 'xxx%';

但是更多的是如下情况

select * from blog where content like '%xxx%';

全文索引有以下限制

1. 每张表只能有一个全文检索的索引
2. 由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则
3. 不支持没有单词界定符的语言，比如中文 日文 韩语

锁

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的
并发访问。MySQL 中提供了两种封锁粒度：行级锁以及表级锁。
应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，
发生锁争用的可能就越小，系统的并发程度就越高。加锁需要消耗资源，锁的各
种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。
当有多个请求读取表中数据时可以不采用任何操作，但是请求中有写请求时一
定要进行相应的并发控制。使用锁就是为了支持对共享资源的并发访问，提供
数据完整性和一致性

lock与latch

lock与latch都被称为锁。latch 一般称为闩锁（轻量级的锁），其要求锁定的
时间非常短，若持续的时间长则应用的性能非常差，在InnoDB存储引擎中latch
又可以分为mutex（互斥量）和rwlock（读写锁）。lock的对象是事务，用来锁
定的是数据库中的对象，如表、页、行。并且仅在事务commit或rollback或unlock
后释放，有死锁机制

锁的类型

InnoDB存储引擎实现了两种标准的行级锁

• 共享锁(S Lock) 又叫读锁 读锁是可以共享的，或者说多个请求可以共享一
  把锁读数据不会造成阻塞，允许事务读一行数据
• 排他锁(X Lock) 又叫写锁 写锁会排斥其他所有锁的请求，一直阻塞，直到
  写入完成释放锁，允许事务删除或更新一行数据

一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其
它事务不能对 A 加任何锁。
一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更
新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁。
如果一个事务T1已经获取了行r的共享锁，那么另外的事务T2可以立即获取行r
的共享锁，因为读取并没有改变行r的数据，这种情况为锁兼容。若有其他事务
T3想获得行r的排他锁，必须等事务T1、T2释放行r上的共享锁–这种情况称为
锁不兼容

意向锁

InnoDB存储引擎支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的
锁同时存在。InnoDB存储引擎支持意向锁，意向锁是将锁定的对象分为多个层
次

• 意向共享锁(IS Lock) 事务想要获得一张表中某几行的共享锁
• 意向排他锁(IX Lock) 事务想要获得一张表中某几行的排他锁

为什么使用意向锁

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测
是否有其它事务对表 A或者表A中的任意一行加了锁，那么就需要对表A的每一
行都检测一次，这是非常耗时的。
意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事
务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定

1. 一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更
   强的锁
2. 一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对
表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中
某一行的锁，因此事务 T 加 X 锁失败。
注意下图的X和S都是表级锁，书上的原句是“在对记录r加X锁之前，已经有事
务对表1进行了S表锁，那么表1上已存在S锁，之后事务需要对记录r在表1上
加IX锁，由于不兼容，所以事务需要等待表锁操作的完成”，这里的X锁也是
表锁，行级锁只会和行级锁发生冲突，表级锁只会和表级锁发生冲突

锁的使用

这里对意向锁的解释很清晰 https://www.zhihu.com/question/51513268
意向锁是 InnoDB 自动加的，不需用户干预。对于UPDATE、 DELETE 和 INSERT
语句，InnoDB会自动给涉及数据集加排他锁(X)，对于普通SELECT语句，InnoDB
不会加任何锁，事务可以通过以下语句显式给记录集加共享锁或排他锁：

# 共享锁（S）
# 其他session仍然可以查询记录，并也可以对该记录加share mode 的共享锁。
# 但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 

# 排他锁（X)
# 其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁
SELECT * FROM table_name WHERE ... FOR UPDATE

# 表锁的语法如下
# 需要注意lock tables语法除了会限制别的线程的读写之外，也限定了本线程接下来的操作对象
lock tables .. read/write 
unlock tables

对于 insert、update、delete，InnoDB 会自动给涉及的数据加排他锁（X），只
有查询 select 需要我们手动设置排他锁对于一般的 select 语句，InnoDB 不会
加任何锁，也就是可以多个并发去进行 select 的操作，不会有任何的锁冲突，因
为根本没有锁。注意 select * 这样的全表扫描会给表加意向共享锁

行锁锁的到底是什么

参考 https://blog.csdn.net/weixin_43935927/article/details/109438506
行锁锁的究竟是什么，事实上行锁锁的并不是某一行的数据，而是这一行数据
的索引，比如先操作一个没有索引的表
给id=1的数据加排他锁，但是id=3和id=5的数据都被阻塞了，说明锁住的不
是Record，而且整个表都被锁住了。接下来操作一个有主键索引的表
使用相同的id加锁会冲突，使用不同的id加锁成功，但是不能确定是否是锁
定了id这个字段，接下来操作一个有唯一索引的表
第二个事务想获取name=’4’的排它锁肯定会失败，但是为什么id=4的数据
也被阻塞，说明并没有锁定name这个字段，否则锁定id应该不会被阻塞，
结论就是InnoDB的行锁是通过锁住索引来实现的。第一个表中因为没有索引
所以会自动创建一个主键，查询没有使用索引所以会全表扫描把每一个隐藏
的聚集索引都锁住了，这里加的应该是一个表级排他锁。第二个表中给唯一
索引加锁时主键索引也会被锁住，辅助索引中，索引存储的是二级索引和
主键的值，也就是通过二级索引最终要找到主键索引，比如name=4存储
的是索引和主键id的值4，主键索引中除了索引还有完整的数据，所以
会将主键索引也锁定
总结一句话就是如果表中有索引就索索引，否则就锁整个表。所以InnoDB的
行锁就是靠锁住索引来实现的

行锁锁的是索引

参考 https://www.cnblogs.com/frankltf/p/9127440.html
接下来自己实现一下以上操作，开启两个端口，创建一个test表

------+------+
| id   | name |
+------+------+
|    1 | fd   |
|    2 | fds  |
|    3 | vv   |
|    4 | fda  |
+------+------+

1. test表没有索引，会对整个表进行加锁。用两种情况演示，一种是加S锁，
   一种是加X锁。可以看到第一张图加了表级S锁，第二种图加了表级X锁
2. test表有主键索引id，但是不通过索引来对数据进行加锁，那么锁的依
   然是整个表。第一张表使用非索引name来锁数据那么会锁整个表，第二张表
   通过索引id来锁数据那么对该行加锁
3. test表有主键索引id和非主键索引name，如果操作非主键索引name，那
   么先锁定非主键索引，然后锁定相关的主键索引。 在UPDATE、DELETE操作时
   ，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值
   ，即所谓的next-key locking
4. 如果明确指定主键但是表中没有该值那么不会锁定任何数据，如果主键不
   明确的话会锁表，不明确就是没有使用等于而是使用像like等

索引类型对锁类型的影响

• 主键：众所周知，自带最高效的索引属性
• 唯一索引：属性值重复率为0，可以作为业务主键
• 普通索引：属性值重复率大于0，不能作为唯一指定条件，对于普通索引，
  当“重复率”低时，甚至接近主键或者唯一索引的效果时，依然是行锁；但是
  如果“重复率”高时，Mysql不会把这个普通索引当做索引，即会造成一个没
  有索引的SQL，从而形成表锁。普通索引就是非唯一索引

快照

快照数据是指该行的之前版本的数据，也即是 undo中的数据，读取快照数据
不需要加锁，因为没有事务需要对历史的数据进行修改。
不同事物隔离级别下，读取的方式不同，并不是在每个事物隔离级别下都采用
非锁定的一致性读。
一个行记录可能不止一个快照数据，一般称这种技术为行多版本技术，由此带
来的并发控制称之为多版本并发控制。
事实上新的版本数据会覆盖旧的版本数据，那事务还能够读取到旧版本的数据
是因为undo log，也就是说undo log能够实现MVCC。我在上一篇博客中解释
undo log是保存在内存中的没有必要存入磁盘是针对回滚这个问题而言，事
实上undo log也是要存入磁盘的
参考 https://www.cnblogs.com/qcloud1001/p/9322321.html
快照的定义：关于指定数据集合的一个完全可用拷贝，该拷贝包括相应数据
在某个时间点（拷贝开始的时间点）的映像。快照可以是其所表示的数据的
一个副本，也可以是数据的一个复制品。
存储快照，是一种数据保护措施，可以对源数据进行一定程度的保护
快照是一份完全可用的副本，那么它完全可以被上层业务当做源数据

一致性非锁定读

参考 https://blog.csdn.net/u010900754/article/details/106772974
一致性非锁定读指InnoDB存储引擎通过行多版本控制的方式来读取当前执行时
间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读取
操作不会因此等待行上锁的释放，因为一般的读取操作是没有锁的，所以可以
直接读取到加锁的数据，会去读取行的一个快照
一致性非锁定读包含两层含义

1. 非锁定 这种读操作不需要加S锁，即便其他事务已经对该数据加了X锁依然
   可以读
2. 一致性 这个一致性取决于隔离级别，不同的隔离级别下读取的值不同，非
   锁定读可以保证即便其他事务修改了数据，但是当前读操作是不会被阻塞的，
   并且保证返回相应一致性要求下的快照数据

MVVC用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取
最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取
的行都加锁，单纯使用 MVCC 无法实现。
在事务隔离级别为READ COMMITTED和REPETABLE READ下，InnoDB存储引擎
使用非锁定的一致性读，但是对于快照数据的定义不同

• READ COMMITTED 对于快照数据，总是读取被锁定行的最新一份快照数据
• REPEATABLE READ 读取事务开始时的行版本数据

一致性锁定读

某些情况下需要对数据库读取操作进行加锁以保证数据逻辑的一致性，即使是
对SELECT的只读操作也有两种一致性的锁定读操作，必须在事务中操作，注
意对于一致性非锁定读这两个操作不会加锁

1. SELECT … FOR UPDATE 对读取的行记录加X锁，其余事务不能加任何锁
2. SELECT … LOCK IN SHARE MODE 加S锁，其余事务可以加S锁
3. 在serializable隔离级别下，读操作加S锁

非锁定读和锁定读的本质区别就在于在读取数据时是否加锁。使用场景如下

• 一致性锁定读 对数据一致性，实时性比较高的情况下（会破坏事务隔离性，
  ，因为获取的都是最新的数据，比较慢，性能低）
• 一致性非锁定读 对数据一致性，实时性不高的情况下（数据可能不是最新
  的，不会破坏事务隔离性）

自增长与锁

每个含有自增长值的表都有一个自增长计数器，当对含有自增长计数器的表进
行插入操作时这个计数器会被初始化，插入操作会依据这个自增长的计数器值
加1赋予自增长列，这种锁采用一种特殊的表锁机制，并不是在一个事务完成
后才会释放，而是在完成自增长值插入的SQL语句后立即释放。但是对于有自
增长值的列的并发插入性能较差，事务必须等待前一个插入的完成（虽然不
用等待事务的完成），对于INSERT .. SELECT的大数据量插入会影响插入
的性能，目前的版本采用一种轻量级互斥量的自增长实现机制。注意自增长
的列必须是索引，同时必须是索引的第一个列

外键和锁

对于一个外键列，如果没有显示对这个列加锁，则自动加一个索引避免表锁
，当外键值插入或更新的时候需要查询父表中的记录，即SELECT 父表，但
对于父表的SELECT并不是采用一致性非锁定读的方式，而是使用S锁

锁的算法

InnoDB有3种行锁的算法，区别在于加锁范围的不同

1. Record Lock 单个行记录上的锁，之前已经提过总是锁定索引记录
2. Gap Lock 间隙锁，锁定一个范围，但不包含记录本身
3. Next-Key Lock Gap+Record 锁定一个范围并且锁定记录本身，例如
   一个索引有10 11 13 20四个值，那么Next-Key Locking的区间为

   (-∞,10]
   (10,11]
   (11,13]
   (13,20]
   (20,+∞)

   采用Next-Key Lock是为了解决幻读
   当查询的索引含有唯一属性进行等值查询时（唯一索引的主键索引），InnoDB
   存储引擎会将Next-Key Lock进行优化，将其降级为Record Lock，即仅锁定
   索引本身而不是范围

   # a是主键
   +---+
   | a |
   +---+
   | 1 |
   | 2 |
   | 5 |
   +---+

   会话A首先对a=5进行X锁定，因为a是主键所以仅仅锁定这一个值。这样在会
   话B中插入4不会被阻塞，锁的降级仅在查询的列是唯一索引的情况下

   create table z(a int ,b int,primary key(a),key(b));
   +----+------+
   | a  | b    |
   +----+------+
   |  1 |    1 |
   |  3 |    1 |
   |  5 |    3 |
   |  7 |    6 |
   | 10 |    8 |
   +----+------+

   b是辅助索引，所以要使用Next-Key Locking技术加锁，并且由于有两个锁
   引，需要分别进行锁定。对于聚集索引，对a=5的索引加上Record Lock，对
   于辅助索引需要加上Next-Key Lock,锁定的范围是(1,3)，注意InnoDB存
   储引擎还会对辅助索引下一个键值进行加gap Lock，即范围为(3,6)的锁
   ，以下语句都会阻塞，也就是(1,6]的范围都被锁定

   select * from z where a=5 lock in share mode;
   insert into z values(4,2);
   insert into z values(6,5);

间隙锁

当我们查询的记录不存在，没有命中任何一个record，无论是用等值查询还
是范围查询的时候，它使用的都是间隙锁。注意间隙锁主要是阻塞insert，
而没有阻塞select，相同的间隙锁之间不冲突。
Gap Lock只在隔离级别RR中存在，如果要关闭间隙锁，事务隔离级别设置成
RC，innodb_locks_unsafe_for_binlog设置为ON。这种情况下除了外键约
束和唯一性检查会加间隙锁，其他情况都不会用间隙锁

临键锁

当我们使用了范围查询，不仅仅命中了Record记录，还包含了Gap间隙，在这
种情况下我们使用的就是临键锁，它是MySQL里面默认的行锁算法，相当于记
录锁加上间隙锁，临键锁有两种退化的情况

• 记录锁：唯一性索引，等值查询匹配到一条记录的时候，退化成记录锁
• 间隙锁：没有匹配到任何记录的时候，退化成间隙锁

比如要查询(5,9)之间的范围，锁住的是 (4,7]和(7,10]
临键锁与间隙锁不同的是，它除了锁住原本的临键区间，还会锁住最后一个
key的下一个左开右闭的区间

select * from t where id>5 and id<=7 for update; -- 锁住(4,7]和(7,10] 
select * from t where id>8 and id<=10 for update; -- 锁住 (7,10]和(10,+∞)

为什么要锁住下一个左开右闭的区间？——为了解决幻读的问题。所以，我们看
下MySQL InnoDB里面事务隔离级别的实现。为什么InnoDB 的RR 级别能够解
决幻读的问题，就是用临键锁实现的
Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。
MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在
的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key
Locks 可以解决幻读问题

解决幻读

Phantom Problem是指在同一事务下，连续执行两次同样的SQL语句可能导致
不同的结果，第二次SQL语句可能返回之前不存在的行。例如之前的表t

select * from t where a>2 for update;

如果事务A没有提交应该返回5，现在另一个事务B插入4，那么在此查询发现
多了4这个数据，这就是幻读。上述的情况是在隔离级别为 READ COMMITED
的条件下，如果是在REPEATABLE READ条件下会对(2,+∞)这个范围加X锁。
这里的select都是用for update，如果只是普通的select那么读取的就
是快照数据，一样不会产生幻读，如果加了for updata就是给数据加排
斥锁，这时会通过行锁算法锁定一个范围，同样解决了幻读。
幻读和不可重复读的区别是，前者是一个范围，后者是本身

快照读与当前读

1. 快照读，也叫做普通读
   MVCC 的 SELECT 操作是读取快照中的数据，不需要进行加锁操作。故不会有
   next-key locks的使用

   SELECT * FROM table ...;

2. 当前读
   所谓当前读，指的是加锁的select(S或者X), update, delete等语句。
   在RR的事务隔离级别下，数据库会使用next-key locks来锁住本条记录
   以及索引区间，从而读取最新的数据。可以看到 MVCC 并不是完全不用
   加锁，而只是避免了 SELECT 的加锁操作。
   在进行 SELECT 操作时，可以强制指定进行加锁操作。以下第一个语句需要
   加 S 锁，第二个需要加 X 锁

   SELECT * FROM table WHERE ? lock in share mode;
   SELECT * FROM table WHERE ? for update;

总结

在mysql中，提供了两种事务隔离技术，第一个是mvcc，第二个是next-key技
术。在RR的隔离级别下，Innodb使用MVVC和next-key locks解决幻读，MVVC
解决的是普通读(快照读)的幻读，next-key locks 解决的是当前读情况下的
幻读。
这个在使用不同的语句的时候可以动态选择。不加lock in share mode之类的
就使用mvcc。否则使用next-key。mvcc的优势是不加锁，并发性高。缺点是不
是实时数据。next-key的优势是获取实时数据，但是需要加锁。同时需要注意
几点

1. 事务的快照时间点是以第一个select来确认的。所以即便事务先开始。但是
   select在后面的事务的update之类的语句后进行，那么它是可以获取后面的
   事务的对应的数据
2. mysql中数据的存放还是会通过版本记录一系列的历史数据，这样可以根
   据版本查找数据

锁问题

因为事务隔离性的要求，锁会带来三种问题

丢失更新

一个事务的更新操作会被另一个事务的更新操作覆盖，从而导致数据的不一致
，但是当前数据库的任何隔离级别下都不会导致数据库理论意义上的丢失更新
，因为update操作会加锁，所以事务1更新记录时，事务2没有办法更新，只
有等事务1提交释放锁后事务2才会获取事务1已经修改的数据

脏读

注意区分两个概念：脏页和脏数据

• 脏页 在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，当然在刷新
  之前日志都已经被写入到重做日志中
• 脏数据 事务对缓冲池中的行记录的修改，并且还没有被提交

参考 https://www.imooc.com/article/17291
脏读是指在不同的事务下当前事务可以读到另外事务未提交的数据，也就是脏
数据。脏读发生的条件是事务的隔离级别为READ UNCOMMITTED，有一个问题
就是在该隔离级别下的加锁情况究竟是什么样的，核心就是读操作不加锁，
写加锁，这与一开始的锁讲解一致，该隔离级别下并没有使用MVVC，而是读
取最新的行数据，所以可以读到其他事务还未提交的修改

不可重复读

在一个事务内多次读取同一数据集合，这个事务还未结束时，另外一个事务也
访问该同一数据集合，并做了一些DML操作并提交，那么第一个事务再次读取
的数据可能就与之前读取的数据不一样，不可重复读发生的条件是事务的隔离
级别是READ COMMITTED。事实上之前已经讲解过会读取一个快照，在RC隔离
级别下读取的是最新的快照，不可重复读和脏读的区别就是脏读是读到未提
交的数据，不可重复读读到的是已经提交的数据，违反一致性要求，可以使
用Next-Key Lock算法来避免不可重复读问题

阻塞

在一个事务结束时（ commit rollback ）或者客户端断开连接时就会释放锁。
因为不同锁之间的兼容性关系，在有些时候一个事务的锁需要等待另一个事务中
的锁释放它锁占用的资源，这就是阻塞。可以使用innodb_lock_wait_timeout
来控制等待时间（默认是50s），innodb_rollback_on_timeout用来设定是否
在等待超时时对进行的事务进行回滚操作

死锁

死锁是两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等
待现象，解决死锁最简单的方式就是设置超时时间，当两个事务相互等待时当一
个事务的等待时间超时其中一个事务就会回滚，另一个事务继续执行，除此还可
以采用wait-for-garph方式进行死锁检测
死锁的发生有如下条件

• 同一时刻只能有一个事务持有这把锁
• 其他的事务需要在这个事务释放锁之后才能获取锁，而不可以强行剥夺
• 当多个事务形成等待环路的时候，即发生死锁

wait-for graph要求数据库保存以下两种信息

• 锁的信息链表
• 事务等待链表

通过上述链表可以构造出一张图，若在这个图中存在回路就代表存在死锁，因此
资源间相互发生等待，因此wait-for graph是一种较为主动的死锁检测机制，
每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，一般
会回滚undo量最小的事务，采用的算法是深度优先算法，并且使用非递归优化

死锁概率

死锁发生的概率与以下几个因素有关

• 系统中事务的数量越多发生死锁的概率越大
• 每个事务操作的数量越多发生死锁的概率越大
• 操作数据的集合越小发生死锁的概率越大

锁升级

锁升级是指将当前锁的粒度降低，比如数据库可以把一个表中1000个行锁升级
为一个页锁，或者将页锁升级为一个表锁。如果在数据库的设计中认为锁是一
种稀有资源，而且想避免锁的开销，那么就会频繁出现锁升级的现象。InnoDB
存储引擎不存在锁升级的问题，不是根据每个记录来产生行锁的，而是根据每
个事务访问的页对锁进行管理，采用的是位图的方式，因此不管一个事务锁住
页中的是一个记录还是多个记录开销通常都是一样的

页级锁

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。采取了折衷
的页级锁，一次锁定相邻的一组记录。BDB支持页级锁

MVVC实现原理

参考 https://www.imooc.com/article/17290
参考 https://www.jianshu.com/p/8845ddca3b23
MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式
去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

• 当前读 像select lock in share mode(共享锁), select for update
  ;update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当
  前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能
  修改当前记录，会对读取的记录进行加锁。
• 快照读 像不加锁的select操作就是快照读，即不加锁的非阻塞读，快照读
  的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所
  以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版
  本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，
  避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不
  一定是数据的最新版本，而有可能是之前的历史版本

在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中
的3个隐式字段、undo日志和Read View 来实现的
InnoDB为每行记录都实现了三个隐藏字段

• DB_TRX_ID，6字节 插入或更新行的最后一个事务的事务ID，事务编号是自
  动递增的（我们把它理解为创建版本号，在数据新增或者修改为新数据的时候
  ，记录当前事务ID）
• DB_ROLL_PTR，7字节 回滚指针（我们把它理解为删除版本号，数据被删除
  或记录为旧数据的时候，记录当前事务ID）。我们把这两个事务ID理解为版本号
• DB_ROW_ID 6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，
  InnoDB会自动以DB_ROW_ID产生一个聚簇索引
• undo log undo log中记录的是数据表记录行的多个版本，也就是事务执行
  过程中的回滚段，其实就是MVCC中的一行原始数据的多个版本镜像数据
• read view 主要用来判断当前版本数据的可见性

undo log

undo log是为回滚而用，具体内容就是copy事务前的数据库内容（行）到undo
buffer，在适合的时间把undo buffer中的内容刷新到磁盘。undo buffer与
redo buffer一样，也是环形缓冲，但当缓冲满的时候，undo buffer中的内
容会也会被刷新到磁盘；与redo log不同的是，磁盘上不存在单独的undo
log文件，所有的undo log均存放在主ibd数据文件中（表空间），即使客
户端设置了每表一个数据文件也是如此

行更新过程

接下来看一下事务1对某行记录的更新过程

1. 初始数据行 如果这行数据是刚INSERT的，可以认为ID为1，其余两列为
   NULL
2. 更改该行的各字段的值 更改时会先用排他锁锁定该行，记录redo log，
   把该行修改前的值Copy到undo log，修改当前行的值，填写事务编号，使
   回滚指针指向undo log中修改前的行
3. 事务2修改该行的值 此时undo log中有两行记录，并且通过回滚指针
   连在一起

Read View

判断当前版本数据项是否可见，实际上也就是内部快照。在innodb 中，创建
一个新事务的时候，会将当前系统中的活跃事务列表（trx_sys->trx_list
）创建一个副本（read view），副本中保存的是系统当前不应该被本事务
看到的其他事务id列表。当用户在这个事务中要读取该行记录的时候，
innodb会将该行当前的版本号与该read view进行比较。具体的算法
如下

1. 设该行的当前事务id为trx_id_0，read view中最早的事务id为
   trx_id_1，最迟的事务id为trx_id_2
2. 如果trx_id_0< trx_id_1的话，那么表明该行记录所在的事务已经
   在本次新事务创建之前就提交了，所以该行记录的当前值是可见的。跳
   到步骤6
3. 如果trx_id_0>trx_id_2的话，那么表明该行记录所在的事务在本
   次新事务创建之后才开启，所以该行记录的当前值不可见.跳到步骤5
4. 如果trx_id_1<=trx_id_0<=trx_id_2, 那么表明该行记录所在事
   务在本次新事务创建的时候处于活动状态，从trx_id_1到trx_id_2进
   行遍历，如果trx_id_0等于他们之中的某个事务id的话，那么不可见
   。跳到步骤5
5. 从该行记录的 DB_ROLL_PTR 指针所指向的回滚段中取出最新的
   undo-log的版本号，将它赋值该trx_id_0，然后跳到步骤2
6. 将该可见行的值返回

注意新建事务(当前事务)与正在内存中commit的事务不在活跃事务链表
中

函数：read_view_sees_trx_id。
read_view中保存了当前全局的事务的范围：
【low_limit_id， up_limit_id】
1. 当行记录的事务ID小于当前系统的最小活动id，就是可见的。
　　if (trx_id < view->up_limit_id) {
　　　　return(TRUE);
　　}
2. 当行记录的事务ID大于当前系统的最大活动id，就是不可见的。
　　if (trx_id >= view->low_limit_id) {
　　　　return(FALSE);
　　}
3. 当行记录的事务ID在活动范围之中时，判断是否在活动链表中，如果
在就不可见，如果不在就是可见的。
　　for (i = 0; i < n_ids; i++) {
　　　　trx_id_t view_trx_id
　　　　　　= read_view_get_nth_trx_id(view, n_ids - i - 1);
　　　　if (trx_id <= view_trx_id) {
　　　　return(trx_id != view_trx_id);
　　　　}
　　}

在RR隔离级别下，数据快照版本是在第一个读请求发起时创建的，之后
不会在发生变化，在RC隔离级别下则是在每次读请求都会创建一个快照
，也就是每次SELECT都能读取到已经COMMIT的数据，快照是在第一条
快照读的语句中生成，而不是在事务开始时创建快照
https://zhuanlan.zhihu.com/p/55819387

事务分类

事务可以分为以下类型

• 扁平事务
• 带有保存点的扁平事务
• 链事务
• 嵌套事务
• 分布式事务

扁平事务

在扁平事务中，所有的操作都处于同一层次，由BEGIN WORK开始，由COMMIT
WORK结束，要么都执行要么都回滚，扁平事务是应用程序成为原子操作的基本
组成模块。扁平事务的主要限制是不能提交或者回滚事务的某一部分，或分几
个步骤提交

带有保存点的事务

允许事务在执行过程中回滚到同一事务中较早的一个状态，保存点用来通知系
统应该记住事务当前的状态，以便当之后发生错误时事务能够回到保存点当时
的位置，保存点用SAVE WORK函数建立

链事务

保存点模式的一种变种，带有保存点的扁平事务，当系统发生崩溃时所有的保
存点都会消失，因此其保存点是易失的而非持久的，当恢复时事务需要从开始
出重新执行，而不能从最近的一个保存点继续执行。链事务的思想是：在提交
一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一
个要开始的事务，注意提交事务操作和开始下一个事务操作将合并为一个原
子操作。一个事务由多个子事务链式组成

嵌套事务

由一个顶层事务控制各个层次的事务，顶层事务之下嵌套的事务被称为子事务
，其控制的是一个局部的变换，可以看成一棵树，注意子事务的提交并不会马
上生效，除非其父事务已经提交，所以只有顶层事务提交后所有子事务才会提
交。树中任意一个事务的回滚都会引起其所有子事务一起回滚，所以子事务不
具有持久性。InnoDB不支持

分布式事务

在一个分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中
的不同节点，至少需要访问网络中两个节点的数据库，每个节点的数据库执行
的事务操作都是扁平的，如果任何一个操作失败都会导致整个分布式事务回滚

事务控制语句

MySQL默认设置下事务都是自动提交的，即执行SQL语句后马上就会执行COMMIT
操作，可以通过SET AUTOCOMMIT=0 禁用自动提交

1. START TRANSACTION|BEGIN 显示开启一个事务
2. COMMIT 提交事务，对数据库永久性修改
3. ROLLBACK 回滚结束事务，撤销未提交的修改
4. SAVEPOINT identifier 在事务中创建一个保存点，一个事务可以有多个保存点
5. RELEASE SAVEPOINT identifier 删除一个保存点
6. ROLLBACK TO identifier 回滚到标记点
7. SET TRANSACTION 设置事务的隔离级别

对于事务操作的统计

每秒处理事务的能力TPS。计算TPS的方法如下，前提是所有事务都必须是显示
提交，隐式提交或回滚不会计算(autocommit=1)

(com_commit+com_rollback)/time

事务的隔离级别

在REPEATABLE READ事务隔离级别下，使用Next-Key Lock锁的算法，因此
避免了幻读。事实上READ COMMITTED隔离级别和SERIALIZABLE隔离级别的
性能差别并不大

set [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL {
	READ UNCOMMITTED
	| READ COMMITTED
	| REPEATABLE READ
	| SERIALIZABLE
}

在SERIALIZABLE隔离级别下InnoDB存储引擎对每个SELECT语句自动加上
LOCK IN SHARE MODE，即每个读取操作加上一个共享锁，主要用于分布
式事务。
在READ COMMITTED的事务隔离级别下，除了唯一性的约束检查及外键约束
的检查需要gap lock

分布式事务

InnoDB存储引擎提供对XA事务的支持，并通过XA事务来支持分布式事务的
实现。分布式事务是指允许多个独立的事务资源参与到一个全局的事务中。
事务资源通常是关系型数据库系统，也可以是其他类型的资源。全局事务
要求在其中的所有参与的事务要么都提交，要么都回滚。使用分布式事务
时隔离级别必须是SERIALIZABLE

备份与恢复

根据备份的方法可以分为如下备份方式

• Hot Backup 热备份 指数据库运行中直接备份，对正在运行的数据库操
  作没有任何影响，也称为在线备份
• Cold Backup 冷备份 在数据库停止的情况下备份，这种备份最简单，一
  般只需要赋值相关的数据库物理文件即可，也称为离线备份
• Warm Backup 温备份 同样在数据库运行时备份，但是会对数据库的操作
  有所影响，比如一个全局读锁以保证备份数据的一致性

按照备份后文件的内容可以分为

• 逻辑备份 备份出的文件内容是可读的，内容一般是由一条条SQL语句或者
  表内实际数据组成。好处是可以观察导出文件的内容，一般适用于数据库的
  升级、迁移等工作。缺点是恢复所需的时间较长
• 裸文件备份 复制数据库的物理文件，既可以是在数据库运行中的复制，
  也可以是停止运行时直接的数据文件复制，恢复时间较短

按照备份数据库的内容来分，可以分为

• 完全备份 对数据库进行一个完整的备份
• 增量备份 在完全备份的基础上对更改的数据进行备份
• 日志备份 对MySQL数据库二进制日志的备份，通过对一个完全备份进行二
  进制日志的重做来完成数据库的 point-in-time的恢复工作。MySQL数据库
  复制的原理就是异步实时将二进制日志重传送并引用到从数据库

冷备份

只备份MySQL数据库的frm文件，共享表空间文件，独立表空间文件(*.ibd)
重做日志文件，优点如下

• 备份简单，只需要复制相关文件
• 备份文件易于在不同操作系统，不同MySQL版本上恢复
• 恢复简单，只需把文件恢复到指定位置
• 恢复速度快，不需要执行任何SQL语句，也不需要重建索引

缺点如下

• 冷备的文件比逻辑文件大很多，因为表空间还存放了undo段，插入缓冲等
• 冷备也不总是可以轻易跨平台

复制

一般用于建立大型的应用，主要涉及三个线程

1. binlog 线程 负责将主服务器上的数据更改写入二进制日志（Binary
   log）中
2. I/O 线程 负责从主服务器上读取二进制日志，并写入从服务器的中继
   日志（Relay log）
3. SQL 线程 负责读取中继日志，解析出主服务器已经执行的数据更改并
   在从服务器中重放（Replay）

复制的工作原理实际就是一个完全备份加上二进制日志备份的还原，不同
的是这个二进制日志的还原操作基本上实时进行。注意复制是异步实时

查询性能优化

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain
结果来优化查询语句，并输出 SELECT 执行的详细信息，在 SELECT 语
句前加上 Explain 就可以了

explain select * from user_info where id = 2;
  id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

各列的含义如下

• id: SELECT查询的标识符，每个SELECT都会自动分配一个唯一的标识符
• select_type: SELECT查询的类型
• table: 查询的是哪个表
• partitions: 匹配的分区
• type: join类型
• possible_keys: 此次查询中可能选用的索引
• key: 此次查询中确切使用到的索引
• ref: 哪个字段或常数与 key 一起被使用
• rows: 显示此查询一共扫描了多少行. 这个是一个估计值
• filtered: 表示此查询条件所过滤的数据的百分比
• extra: 额外的信息

select_type

表示查询的类型

• SIMPLE 表示此查询不包含 UNION 查询或子查询
• PRIMARY 表示此查询是最外层的查询
• UNION 表示此查询是 UNION 的第二或随后的查询
• DEPENDENT UNION UNION 中的第二个或后面的查询语句，取决于外面的
  查询
• UNION RESULT UNION 的结果
• SUBQUERY 子查询中的第一个 SELECT
• DEPENDENT SUBQUERY 子查询中的第一个 SELECT，取决于外面的查询，
  即子查询依赖于外层查询的结果

优化数据访问

1. 减少请求的数据量

• 只返回必要的列：最好不要使用 SELECT * 语句。
• 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
• 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要
  查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的

2. 减少服务器端扫描的行数
   最有效的方式是使用索引来覆盖查询

慢查询日志

某些sql语句执行完毕所花费的时间特别长，我们将这种响应比较慢的语句
记录在慢查询日志中，通过慢查询日志，可以查找出哪些查询语句的执行
效率低，以便进行优化