mysql、innodb和加锁分析

原文：https://liuzhengyang.github.io/2016/09/25/mysqlinnodb/

介绍

本文主要介绍MySQL和InnoDB存储引擎的一些知识。分为MySQL基础知识、InnoDB介绍和加锁分析。

MySQL基础知识

MySQL架构

MySQL的分层可以分为三层，最上面一层可以叫做接入层，负责连接处理、授权、安全等，第二层的功能包括查询解析、分析、优化、缓存一些内置函数等功能，第三层包含了存储引擎。存储引擎负责数据的存储和读取，实现存储引擎的API，服务器层通过存储引擎API进行调用。

并发控制

和其他的并发问题一样，MySQL也是通过加锁进行并发控制的。锁就会涉及读写锁、锁粒度等问题。
为了防止一个线程访问数据时，其他数据修改了同一部分数据，一种办法是加一把大锁，这样固然能解决数据安全问题，但是带来的影响是不支持并发。
所以一种经典解决办法是定义两种类型的锁，共享锁(shared lock)和排它锁(exclusive lock)，也叫读锁(read lock)和写锁(write lock)。读锁是共享的，写锁是排他的。多个读锁可以同时存在，但是写锁不能和读锁和写锁共存。在读多写少的场景比较适合读写锁。
在锁的大小范围上，可以定义锁的粒度,我们可以锁全部数据，也可以只锁我们访问的数据，锁的粒度越小，所需资源越多，增加的系统开销也越大。在数据库概念中分为了表级锁和行级锁。

事务

事务是一组原子性的SQL查询，或者说一个独立的工作单元。事务内的语句，要么全部执行成功，要么全部执行失败。常见的例子是银行转帐，A转帐给B，包括三个操作，检查A的余额大于100元，从A的账户减100元，给B的账户加100元。事务的四个特性是ACID。

原子性(Atomicity): 一个事务必须被视为一个不可分割的最小工作单元，事务中的操作要么全部执行成功，要么全部失败会滚，不可能只执行其中的一部分。
一致性(consistency) 数据库总是要从一个一致的状态转换到另一个一致的状态。需要确保当从A减去100时若系统崩溃，账户不会损失100元。
隔离性(isolation)通常来说，一个事务在最终提交前对其他事务是不可见的。为满足不同的并发场景，定义了不同的隔离级别，从低到高是读未提交，读已提交，可重复读和序列化。序列化能够保证最强的一致性，其他隔离级别的一致性依次将此，并发性更好。读未提交会看到其他事务没有提交的操作，称为脏读。读已提交只能看到其他事务已经提交的修改，但是不能重复读，因为一个事务内两次读看到的结果不同。可重复读解决了不可重复读的问题，但是有幻读的问题，就是在读取某个范围的记录时，其他事务在该范围内插入了新的数据，两次读的结果不一致。InnoDB通过多版本并发控制(MVCC)解决了幻读的问题，可重复读是MySQL的默认隔离级别。序列化是最高的隔离级别，会让事务串行执行，在读取的每一行数据加锁，会造成大量的锁征用。
持久性(durability) 持久性要保证提交了事务后，即使系统崩溃，修改的数据也不会丢失。持久性也分为不同的级别，通过备份等手段可以提高持久性。
死锁

死锁是指多个事务循环等待已持有的资源。如A获取了资源1要获取资源2，同时B获取了资源2要去获取资源1.这样就形成了死锁。死锁都发生在多个资源的加载顺序不一致导致，MySQL中的死锁多发生在事务中的加锁引起。MySQL中提供了死锁检测和回滚事务中的一个事务打破死锁的功能。

MySQL中的事务

MySQL默认使用自动提交，可以通过AUTOCOMMIT变量来启用或禁止自动提交，设置AUTOCOMMIT为0后，可以通过COMMIT提交事务或通过ROLLBACK回滚事务。

Innodb

Innodb 概览

Innodb是一个通用的存储引擎，也是现在MySQL默认的存储引擎。InnoDB的关键优点如下:

DML操作遵循ACID模型，通过commit, rollback和奔溃恢复的事务特性保护用户数据。
提供行级锁和一致性读，提高了并发性和性能。
每个表都有一个主键索引，使用聚簇索引的方式组织数据，减少主键查询的IO，提高基于主键的查询语句。
InnoDB采用MVCC支持高并发，默认隔离级别是REPEATABLE READ(可重复读), 并且通过next-key locking策略防止幻读的出现。间隙锁使得Innodb不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能。但是二级索引(Secondary index, 非主键索引)中必须包含主键列。
innodb的高性能的原因:
Insert Buffer
异步IO
Double write
Adaptive Hash Index

InnoDB与MVCC

InnoDB保存修改行的旧版本的信息，来支持并发一致性读和回滚等特性。这些信息保存在一个称为回滚段(rollback sement)的数据结构中。InnoDB使用回滚段中的信息来进行事务回滚中的undo操作。并且用这些信息来作为一致性读的信息。内部实现上个，InnoDB怎讲了三个字段在每个保存的行中。一个DB_TRX_ID字段表明最后一个插入或修改行的事务的id，删除操作也被当做是特殊的修改操作，通过设置一个特殊的标志位。每个行还会有一个DB_ROLL_PTR字段叫做回滚指针，指向回滚段的undo log。如果行被更新，undo log 记录包含了必要的回退到被更新前所需的内容。Undo log可以分为Insert 的Undo Log和Update的。Insert的在事务提交后就可以被忽略了，而Update的还需要用于一致性读。InnoDB会有一个purge清理操作来在合适的时候清除undo log。
Innodb的MVCC对于secondary index的处理和primary index的不一样，primary index的更新是在原地更新的，而secondary index则是将就的记录加上删除标志，再插入更新的记录。
总结: Innodb的MVCC通过Undo segment中的undo log实现，当插入数据或修改数据时，会记录相反作用的逻辑语句到undo log中，如插入对应的是删除。

binlog与redo的log的区别

binlog用来进行POINT-IN-TIME的恢复和主从复制。
redolog用来保证事务的持久性，防止缓存区内数据没有刷新到磁盘导致数据修改丢失
binlog由数据库上层生成，是SQL执行的逻辑日志。redo log是存储引擎层面的物理格式的日志，记录的是对于每个页的修改。
binlog在事务提交完成后进行一次写入，而InnoDB的redolog在事务进行中不断地被写入

Innodb锁和Innodb事务模型

共享锁和排他锁

Innodb实现了标准的行级锁，行级锁分为了两种类型，共享锁(shared lock, 简称s 锁)和排他锁(exclusive lock, 简称x锁)。

持有一个共享锁的事务能够读取这一行
持有排他锁的事务能够更新或者删除这一行

如果事务T1在行r上持有s锁，然后另一个事务T2在行r上的锁请求如下处理

T2的s锁加锁请求能够被立刻允许。结果是，T1和T2都在r上持有s锁
T2的x锁不能够立即被授权。

如果事务T1在r上持有x锁，那么另一个事务T2在r上的任何锁请求都不能被立即允许，T2必须在等待T1释放在r上的锁。

意向锁(Intention Lock)

Innodb支持不同粒度的锁，允许行级锁和表级锁共存。为了实现不同粒度的锁，使用了新增的锁类型，意向锁。意向锁是一种表级锁，表示一个事务之后要获取的锁类型。有两种意向锁，

意向共享锁 (Intention shared, IS) 事务T有要在表t的一行上加s锁的意向
意向排他锁 (Intention exclusive, IX) 事务T有要在表t的一行上加x锁的意向

例如, SELECT … LOCK IN SHARE MODE设置IS锁, SELECT … FOR UPDATE 设置IX锁

意向锁的协议如下:

在一个事务在表t上获取s锁之前，它必须首先在t上获取IS或更强的锁。
在一个事务在表t上获取x锁之前，它必须首先在t上获取IX或更强的锁。
这些规则可以用锁兼容矩阵来表示

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

当请求的事务和已有的锁兼容时能够被允许。事务必须等到已有的冲突的锁释放后。

记录锁(Record Lock)

记录锁是在索引记录上的锁。例如， SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE.
防止其他事务插入、更新或删除 t.c1=10的行记录。
记录锁总是锁在索引记录上，及时这个表没有定义任何索引。这种情况下Innodb锁创建一个隐藏的聚簇索引并使用这个索引来加记录锁。

记录锁的事务数据类似如下，

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`

trx id 10078 lock_mode X locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 8000000a; asc ;;

1: len 6; hex 00000000274f; asc 'O;;

2: len 7; hex b60000019d0110; asc ;;

间隙锁(Gap Lock)

间隙锁在索引记录间加锁，或在第一个索引记录前、最后一个索引记录后加锁。例如， SELECT c1 FROM t
WHERE c1 BETWEEN 10 AND 20 FRO UPDATE 防止其他事务插入一个t.c1为15的事务，而不管是否已经有这样的记录，因为已有值范围内都被锁住了。
间隙可以跨越一个索引记录值、多个索引记录值、甚至空的。
间隙锁是在性能和并发性之间的权衡，在一些事务隔离级别中会使用。

在使用一个唯一索引查询一个唯一的行时不需要使用间隙锁。计入，id列有唯一索引,下面的语句只会在id=100的行加索引记录锁而不管是否有其他会话在间隙前插入数据

1	SELECT * FROM child WHERE id = 100;

如果id非唯一，则需要加入

如果id没有索引并且没有非唯一的索引，那么这个语句就会锁住之前的间隙。
值得注意的是冲突的锁可以被不同的事务在事务上持有。例如，事务A可以持有共享间隙锁(gap S-lock)在一个间隙上，事务B可以在同一个间隙上持有排他间隙锁。允许冲突的锁的原因是如果一个记录在一个索引上被清除了，其他事务在这个记录上持有的间隙锁必须被合并。

Innodb的间隙是单纯禁止的(purely inhibitive)，意思是它们只防止其他事务在间隙中插入。它们不防止其他事务在同一个间隙中加间隙锁。所以，一个间隙x锁和间隙s锁的效果一样。

Gap锁可以被明确关掉，可以通过调整事务隔离级别到READ COMMITTED级别来达到。

Next-Key锁

next-key锁是在一个索引记录上的记录锁和索引记录前的间隙锁的结合。

InnoDB用这种方式来进行行级加锁，当它搜索或扫描一个表索引，它在遇到的索引记录上加共享或排他锁。这样，行级锁实际上就是索引记录所。索引记录上的next-key锁还影响索引记录前的间隙。next-key锁是索引记录所加上索引记录前面的间隙锁。如果一个会话在记录R上的一个索引上持有共享或排他锁，另一个会话不能在R的index索引顺序前面插入新纪录。

假设一个索引包含10,11,13和20这几个值, 可能的next-key锁如下

(negative infinity, 10]

(10, 11]

(11, 13]

(13, 20]

(20, positive infinity)

在最后一个区间next-key锁锁住索引的最大值和上确届。
默认，Innodb使用REPEATABLE READ事务隔离级别。这种情况下，Innodb用next-key锁来进行搜索和索引扫描，来防止幻影数据行(幻读)。

next-key锁的事务数据类似如下

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`

trx id 10080 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 8000000a; asc ;;

1: len 6; hex 00000000274f; asc 'O;;

2: len 7; hex b60000019d0110; asc ;;

插入意向锁

插入意向锁是在INSERT操作前执行的一种间隙锁。这个锁表示插入的意向，这样多个要插入同一个索引间隙的事务之间就不需要互相等待，如果它们不是插入间隙中的同一个位置。假设有索引记录值为4和7.两个单独的事务尝试插入在4和7之间插入记录5,和6，首先要在要插入的行上加排他锁，但是它们并不互相block，因为要插入的行不冲突。

下面的例子展示了一个事务要在插入的数据加排他锁前加插入意向锁。例子设计两个客户端，A和B。
A创建了一个表，包括两个索引记录(90和102) 然后开始了一个事务，在id大于100的索引记录上放置排他锁，这个排他锁包括了102之前的间隙锁。

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;

mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;

+-----e

| id |

+-----+

| 102 |

+-----+

B开始一个事务要在间隙中插入一个记录，这个事务执行了插入意向锁但是它等待获取排他锁。

1 2	mysql> START TRANSACTION; mysql> INSERT INTO child (id) VALUES (101);

事务日志如下

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`

trx id 8731 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 80000066; asc f;;

1: len 6; hex 000000002215; asc " ;;

2: len 7; hex 9000000172011c; asc r ;;...

查看锁状态

除了通过show engine innodb status查看锁状态外，还可以通过innodbtop工具查看更多的信息。
(转)mysql、innodb和加锁分析
地址在innodbtop

自增锁(AUTO-INC Lock)

AUTO-INC锁是一个特殊的表级锁，用于插入有AUTO_INCREMENT字段的表的事务。最简单的情况下，如果一个事务插入数据，其他的事务必须等待执行。

InnoDB事务模型

autocommit, Commit, Rollback

一致非阻塞读(Consistent Nonblocking Read)

常用数据类型

多版本并发控制

MVCC是一种解决方案，不同的数据库有不同的实现方式。基本是通过保存数据在某个时间点的快照来实现。不管执行多长时间，每个事务看到的数据都是一致的，大多都实现了非阻塞的读，写操作也只锁定必要的行。
Innodb的MVCC，是通过在每行记录的后面保存两个隐藏的列来实现的，一个是创建的系统版本号，一个是删除的系统版本号，每次创建事务，系统版本号都会递增。在Repeatable Read隔离级别下，Innodb的MVCC的实现是

SELECT

只查找版本早于当前事务版本的数据行。行的删除版本要么未定义，要么大于当前事务版本号

INSERT

插入新的每一行保存当前系统版本号作为行版本号

DELETE

为删除的每一行保存当前系统版本号作为行删除标识

UPDATE

插入一条新数据，将当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行为行删除标识
这样就可以保证大多数的读操作都不需要加锁。

索引

索引作用

用于快速查找。可以用书的书签进行理解。
索引优点, 以BTree索引为例，按照顺序存储数据，并且索引中存储了实际的列值，
所以有序有利于Order By 、Group By查询，并且某些查询只需要使用索引。

索引大大减少了服务器需要扫描的数据流
索引可以帮助服务器避免排序和临时表
素银可以将随机I/O变为顺序I/O
索引多种类型适用于不用的场景。
B-Tree索引

mysql中默认的存储引擎innodb使用的是B+Tree，但是创建索引时使用的是using BTree。

B+Tree同一层是按照顺序排序的，并且有向右的指针，适合排序、范围查找。
MySQL索引类型可以分为聚簇索引和非聚簇索引。
聚簇索引是一种数据存放方式，指主键和数据都存储在索引的叶子节点中。
聚簇索引只能有一个，如果有主键则使用主键索引为聚簇索引，如果没有，则选择唯一的非空的索引作为聚簇索引，如果再没有，则生成一个。
除了聚簇索引都是非聚簇索引，
非聚簇索引的叶节点存储索引列的值和主键值。所以查找记录需要先查到主键，再通过主键找到对应的记录。
当索引的列包含了要查询的列时，就形成了覆盖索引，覆盖索引是针对某一条查询而言的，或者叫做索引覆盖查询更好理解一些。覆盖索引不需要再按主键进行查询, MySQL中只能使用B-Tree索引做覆盖索引。

如何判断索引是否创建的合适

索引的目的是增加查找速度，如果衡量一个索引是否是一个好的索引，需要看索引的列、索引列的顺序、和使用的查询语句有哪些。看一个索引好坏主要有三个方面

查询语句是否使用到了索引列
由于BTree索引结构，限制只能高效的使用最左前缀，不能跳过索引某一列，当索引一列使用范围匹配后
索引是否可以一次将数据获取，最好是聚簇索引或覆盖索引
使用了索引列，能达到一星索引，

索引优缺点

索引优点

索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机I/O变为顺序I/O

索引缺点

索引会增加额外的存储空间
插入数据和修改数据时

行加锁分析

锁类型

共享锁
排他锁

下面进行一些常见的行加锁分析

背景: MySQL InnoDB存储引擎 Repeatable Read 隔离级别
现在创建一个test表，有id,name,age, 下面分析各种情况SQL语句的加锁情况
现在数据有

id	name	age
1	a	23
2	b	23
3	c	14
4	e	15
5	f	16

1 2	select * from test where id = 3; update test set name = 'lzy' where id = 10;

id列是主键

update 语句会

可能的死锁场景和分析

未完待续

mysql 复制

半同步symi sync

mysql默认的复制是异步的，master向binlog日志中写入事件但是并不是道slave是否收到并处理了。当master崩溃的事件，提交的事务可能没有传送到任何一个slave上。结果就是故障切换(failover)时有可能导致事务丢失。
半同步是异步同步的另一种方案, slave 连接到master时会知道它是不是semisync的。
如果在master端启用了半同步并且至少有一个slave启用了半同步，线程在执行事务提交是会block直到事务提交并且至少一个半同步salve响应回到它已经收到所有的事务时间或者发生超时。
slave只有在事务事件都被写入日志并且刷入到磁盘才会响应已经收到事务。如果超时发生，没有任何slave响应事务，master就会变成异步同步，当至少一个slave追上来的时候，master会变回半同步。
和异步复制比起来，半同步提高了数据完整性，当事务成功提交后，可以知道数据至少已经存在两个地方了。在master提交但是在等待slave响应的时候崩溃，事务仍有可能没有到达任何一个slave。
半同步会带来性能影响，因为提交必须要等待slave的响应。这是为了提高数据完整性的权衡。减少的时间至少是提交给slave和等待响应的TCP/IP的往返时间。所以半同步更适合告诉网络主机间。

术语

row (行)

用一个列的集合来定义的一个逻辑数据结构。一个Row的集合组成了Table。

page(页)

一个表示InnoDB每次从磁盘(Data File) 到内存(buffer pool)间传送该数据的大小。一个page可以包含一个或多个row。

rollback seƒgment

undo log

保存着被活动的事务(transaction)修改过的数据的副本的一个存储区域。如果另一个事务需要看到原来的数据(作为一致性读(consistent read)操作的一部分)，这个没有修改过的数据就会被返回。

参考资料

感觉有收获的话，请我吃个早饭吧!

(转)mysql、innodb和加锁分析