2020-03-24

MySQL锁篇

MySQL锁介绍

乐观锁

用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？

即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据

举例

数据库表三个字段，分别是id、value、version

1	select id,value,version from TABLE where id = #{id}

每次更新表中的value字段时，为了防止发生冲突，需要这样操作

1	update TABLE set value=2,version=version+1 where id=#{id} and version=#{version}

锁的分类

按照锁的粒度

MySQL主要包含三种类型（级别）的锁定机制

全局锁：锁的是整个database。由MySQL的SQL layer层实现的

表级锁：锁的是某个table。由MySQL的SQL layer层实现的

行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB

表级锁和行级锁的区别

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

按照锁的功能

共享读锁和排他写锁

按照锁的实现方式

悲观锁和乐观锁（使用某一版本列或者唯一列进行逻辑控制）

MySQL表级锁

表级锁介绍

由MySQL SQL layer层实现

MySQL的表级锁有两种

一种是表锁，一种是元数据锁（meta data lock，MDL)

MySQL 实现的表级锁定的争用状态变量

1	show status like 'table%';

table_locks_immediate：产生表级锁定的次数

table_locks_waited：出现表级锁定争用而发生等待的次数

表锁介绍

MySQL允许客户端会话明确获取表锁，以防止其他会话在特定时间段内访问表。客户端会话只能为自己获取或释放表锁。它不能获取或释放其他会话的表锁

表锁有两种表现形式

表共享读锁（Table Read Lock），表独占写锁（Table Write Lock）

手动增加表锁

1	LOCK TABLES table_name [READ \| WRITE];

可将表的名称放在LOCK TABLES关键字后面，后跟一个锁类型。 MySQL提供两种锁类型：READ和WRITE

查看表锁情况

1	show open tables;

删除表锁

1	unlock tables;

表锁演示

环境准备

新建表

CREATE DATABASE IF NOT EXISTS testdb;
 
USE testdb;
CREATE TABLE tbl (
  id int(11) NOT NULL AUTO_INCREMENT,
  col int(11) NOT NULL,
  PRIMARY KEY (id)
);

读锁演示

表锁定为READ

表的READ锁具有以下功能：

同时可以通过多个会话获取表的READ锁。此外，其他会话可以从表中读取数据，而无需获取锁定
持有READ锁的会话只能从表中读取数据，但不能写入。此外，其他会话在释放READ锁之前无法将数据写入表中。来自另一个会话的写操作将被放入等待状态，直到释放READ锁
如果会话正常或异常终止，MySQL将会隐式释放所有锁。这也与WRITE锁相关

首先，连接到testdb数据库。要查找当前的连接ID，请使用CONNECTION_ID()函数，如下所示

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)

注：

CONNECTION_ID()：MySQL的这个函数返回的是这个连接的连接ID或者thread ID。对于已经建立的连接的客户端，都有一个唯一的连接ID

然后，在向tbl表中插入一个新行

1	INSERT INTO tbl(col) VALUES(10);

接下来，从上表tbl中检索所有行

mysql> SELECT * FROM tbl;
+----+-----+
| id | col |
+----+-----+
|  1 |  10 |
+----+-----+
1 row in set (0.00 sec)

之后，要获取锁，可以使用LOCK TABLE语句

1 2	mysql> LOCK TABLES tbl READ; Query OK, 0 rows affected (0.00 sec)

最后，在同一个会话中，如果您尝试在tbl表中插入一个新行，将收到一条错误消息

1 2	mysql> INSERT INTO tbl(col) VALUES(11); ERROR 1099 (HY000): Table 'tbl' was locked with a READ lock and can't be updated

所以一旦获得了READ锁定，就不能在同一个会话中的表中写入数据。让我们从不同的会话中来查看READ锁

首先，打开另一个终端并连接到数据库testdb，然后检查连接ID：

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              11 |
+-----------------+
1 row in set (0.00 sec)

然后，从tbl检索数据，如下所示

mysql> SELECT * FROM tbl;
+----+-----+
| id | col |
+----+-----+
|  1 |  10 |
+----+-----+
1 row in set (0.00 sec)

接下来，从第二个会话(会话ID为7)插入一个新行到tbl表中

第二个会话的插入操作处于等待状态，因为第一个会话已经在tbl表上获取了一个READ锁，并且尚未释放

可以使用SHOW PROCESSLIST;语句查看详细信息，如下所示

之后，返回第一个会话并使用UNLOCK TABLES;语句来释放锁。从第一个会话释放READ锁之后，在第二个会话中执行INSERT操作

最后，查看tbl表中的数据，以查看第二个会话中的INSERT操作是否真的执行

mysql> SELECT * FROM tbl;
+----+-----+
| id | col |
+----+-----+
|  1 |  10 |
|  5 |  12 |
+----+-----+
2 rows in set (0.00 sec)

写锁演示

表锁定WRITE

表锁为WRITE具有以下功能：

只有拥有表锁定的会话才能从表读取和写入数据
在释放WRITE锁之前，其他会话不能从表中读写

首先，从第一个会话获取一个WRITE锁

1	LOCK TABLE tbl WRITE;

然后，在tbl表中插入一个新行

1	INSERT INTO tbl(col) VALUES(14);

没有问题，上面语句可能正常执行。接下来，从tbl表读取数据

mysql> SELECT * FROM tbl;
+----+-----+
| id | col |
+----+-----+
|  1 |  10 |
|  5 |  12 |
|  6 |  14 |
+----+-----+
3 rows in set (0.00 sec)

之后，打开第二个连接到MySQL的会话，尝试写和读数据

MySQL将这些操作置于等待状态。可以在第一个会话中，使用SHOW PROCESSLIST;语句来查看它

最后，从第一个会话释放锁。执行以下语句

1	UNLOCK TABLES;

执行上面语句后，将看到第二个会话中的所有待处理已经执行操作

mysql> SELECT * FROM tbl;
+----+-----+
| id | col |
+----+-----+
|  1 |  10 |
|  5 |  12 |
|  6 |  14 |
|  7 |  20 |
+----+-----+
4 rows in set (0.00 sec)

元数据锁介绍

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的

因此，在MySQL 5.5 版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL 读锁，当要对表做结构变更操作的时候，加MDL 写锁

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行

元数据锁演示

session1（Mysql1）、session2（Mysql2）

1、session1

开启事务

begin;

加MDL读锁

mysql> select * from tbl;
+----+-----+
| id | col |
+----+-----+
|  1 |  10 |
|  5 |  12 |
|  6 |  14 |
|  7 |  20 |
+----+-----+
4 rows in set (0.00 sec)

2、session2

修改阻塞

1	alter table tbl add size int;

3、session1

提交事务或者rollback 释放读锁

commit;

4、session2

修改完成

1 2	Query OK, 0 rows affected (38.67 sec) Records: 0 Duplicates: 0 Warnings: 0

补充

表读锁

session1（Navicat）、session2（mysql）

1、session1: lock table mylock read; -- 给mylock表加读锁
2、session1: select * from mylock; -- 可以查询
3、session1：select * from tdep; --不能访问非锁定表
4、session2：select * from mylock; -- 可以查询没有锁
5、session2：update mylock set name='x' where id=2; -- 修改阻塞,自动加行写锁
6、session1：unlock tables; -- 释放表锁
7、session2：Rows matched: 1 Changed: 1 Warnings: 0 -- 修改执行完成
8、session1：select * from tdep; --可以访问

表写锁

session1（Navicat）、session2（mysql）

1、session1: lock table mylock write; -- 给mylock表加写锁
2、session1: select * from mylock; -- 可以查询
3、session1：select * from tdep; --不能访问非锁定表
4、session1：update mylock set name='y' where id=2; --可以执行
5、session2：select * from mylock; -- 查询阻塞
6、session1：unlock tables; -- 释放表锁
7、session2：4 rows in set (22.57 sec) -- 查询执行完成
8、session1：select * from tdep; --可以访问

MySQL行锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

行锁介绍

行锁的劣势：开销大；加锁慢；会出现死锁

行锁的优势：锁的粒度小，发生锁冲突的概率低，处理并发的能力强

加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（行写锁），对于普通SELECT语句，InnoDB不会加任何锁

当然我们也可以显式的加锁

共享锁：select * from tableName where … + lock in share more

排他锁：select * from tableName where … + for update

InnoDB和MyISAM的最大不同点有两个

一，InnoDB支持事务(transaction)

二，默认采用行级锁。加锁可以保证事务的一致性，可谓是有人(锁)的地方，就有江湖(事务)

查看行锁状态

show STATUS like 'innodb_row_lock%';

Innodb_row_lock_current_waits：当前正在等待锁定的数量

Innodb_row_lock_time：从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg：每次等待所花平均时间

Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间

Innodb_row_lock_waits：系统启动后到现在总共等待的次数

行读锁

session1（Mysql1）、session2（Mysql2）

1、session1: begin;--开启事务未提交
         select * from mylock  where ID=1 lock in share mode; --手动加id=1
的行读锁,使用索引
2、session2：update mylock set name='y' where id=2; -- 未锁定该行可以修改
3、session2：update mylock set name='y' where id=1; -- 锁定该行修改阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction  -- 锁定超时
4、session1: commit; --提交事务或者rollback 释放读锁
5、session2：update mylock set name='y' where id=1; --修改成功
          Query OK, 1 row affected (0.00 sec)
          Rows matched: 1 Changed: 1 Warnings: 0

注：使用索引加行锁，未锁定的行可以访问

行读锁升级为表锁

session1（Mysql1）、session2（Mysql2）

背景：name列没有加索引

1、session1: begin;--开启事务未提交
            --手动加name='c'的行读锁,未使用索引
            select * from mylock  where name='c' lock in share mode;
2、session2：update mylock set name='y' where id=2; -- 修改阻塞未用索引行锁升级
为表锁
3、session1: commit; --提交事务或者rollback 释放读锁
4、session2：update mylock set name='y' where id=2; --修改成功
          Query OK, 1 row affected (0.00 sec)
          Rows matched: 1 Changed: 1 Warnings: 0

注：未使用索引行锁升级为表锁

行写锁

session1（Mysql1）、session2（Mysql2）

1、session1: begin;--开启事务未提交
            --手动加id=1的行写锁,
            select * from mylock  where id=1 for update;
           
2、session2：select * from mylock  where id=2 ; -- 可以访问
3、session2: select * from mylock  where id=1 ; -- 可以读不加锁 
4、session2: select * from mylock  where id=1 lock in share mode ; --加读锁被阻塞
5、session1：commit; -- 提交事务或者rollback 释放写锁
6、session2：执行成功

注：主键索引产生记录锁

结论：读锁和读锁不冲突，读锁和写锁冲突，带有读锁&写锁这两个任何之一的语句和未加锁的语句是不冲突的

间隙锁

一般来说，事务的幻读问题，都是通过Seriablizable隔离级别来解决的。但是MySQL使用的间隙锁来解决了，只有在RR（可重复读）隔离级别才会产生间隙锁

间隙锁防止两种情况
1、防止插入间隙内的数据

2、防止已有数据更新为间隙内的数据

间隙的范围

update news set number=3 where number=4;

number : 2 3 4

id:1 2 3 4 5

间隙情况：

id、number均在间隙内

id、number均在间隙外

id在间隙内、number在间隙外

id在间隙外，number在间隙内

id、number为边缘数据

案例演示：
mysql> create table news (id int, number int,primary key (id));
mysql> insert into news values(1,2);
......
--加非唯一索引
mysql> alter table news add index idx_num(number);

非唯一索引等值

-- 非唯一索引的等值
session 1:
start transaction ;
update news set number=3 where number=4;
session 2:
start transaction ;
insert into news value(2,3);#（均在间隙内，阻塞）
insert into news value(7,8);#（均在间隙外，成功）
insert into news value(2,8);#（id在间隙内，number在间隙外，成功）
insert into news value(4,8);#（id在间隙内，number在间隙外，成功）
insert into news value(7,3);#（id在间隙外，number在间隙内，阻塞）
insert into news value(7,2);# (id在间隙外，number为上边缘数据，阻塞)
insert into news value(2,2);#（id在间隙内，number为上边缘数据，阻塞）
insert into news value(7,5);#（id在间隙外，number为下边缘数据，成功）
insert into news value(4,5);#（id在间隙内，number为下边缘数据，阻塞）

结论：只要number（where后面的）在间隙里（2 3 4），不包含最后一个数（5）则不管id是多少都会阻塞

主键索引范围

--主键索引范围
session 1:
start transaction ;
update news set number=3 where id>1 and id <6;
session 2:
start transaction ;
insert into news value(2,3);#（均在间隙内，阻塞）
insert into news value(7,8);#（均在间隙外，成功）
insert into news value(2,8);#（id在间隙内，number在间隙外，阻塞）
insert into news value(4,8);#（id在间隙内，number在间隙外，阻塞）
insert into news value(7,3);#（id在间隙外，number在间隙内，成功）
--id无边缘数据，因为主键不能重复

结论：只要id（在where后面的）在间隙里(2 4 5)，则不管number是多少都会阻塞。非唯一索引无穷大

session1（Navicat）、session2（mysql）

--无穷大
session 1:
start transaction ;
update news set number=3 where number=13 ;
session 2:
start transaction ;
insert into news value(11,5);#(执行成功)
insert into news value(12,11);#(执行成功)
insert into news value(14,11);#(阻塞)
insert into news value(15,12);#(阻塞)
检索条件number=13,向左取得最靠近的值11作为左区间，向右由于没有记录因此取得无穷大作为右区
间，因此，session 1的间隙锁的范围（11，无穷大）
结论：id和number同时满足
注：非主键索引产生间隙锁，主键范围产生间隙锁

死锁

两个session 互相等等待对方的资源释放之后，才能释放自己的资源,造成了死锁

session1（Navicat）、session2（mysql）

1、session1: begin;--开启事务未提交
            --手动加行写锁id=1 ，使用索引
           update mylock set name='m' where id=1;
2、session2：begin;--开启事务未提交
--手动加行写锁id=2 ，使用索引
           update mylock set name='m' where id=2; 
3、session1: update mylock set name='nn' where id=2; -- 加写锁被阻塞
4、session2：update mylock set name='nn' where id=1; -- 加写锁会死锁，不允许操作
ERROR 1213 (40001): Deadlock found when trying to get lock; try
restarting transaction

InnoDB架构分析

InnoDB架构图

上图详细显示了InnoDB存储引擎的体系架构，从图中可见，InnoDB存储引擎由内存池，后台线程和磁盘文件三大部分组成。接下来我们就来简单了解一下内存相关的概念和原理

InnoDB内存结构

Buﬀer Pool缓冲池

概述

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。但是由于CPU速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池记录来提高数据库的的整体性能

所以，缓冲池的大小直接影响着数据库的整体性能，可以通过配置参数innodb_buffer_pool_size来设置

具体来看，缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲(insertbuﬀer)、自适应哈希索引(adaptive hash index)、InnoDB存储的锁信息(lock info)和数据字典信息(data dictionary)

物理上是存在一个文件里的，但是逻辑上不是存储在一起

在架构图上可以看到，InnoDB存储引擎的内存区域除了有缓冲池之外，还有重做日志缓冲和额外内存池。InnoDB存储引擎首先将重做日志信息先放到这个缓冲区中，然后按照一定频率将其刷新到重做日志文件中。重做日志缓冲一般不需要设置的很大，该值可由配置参数innodb_log_buffer_size控制

数据页和索引页

InnoDB存储引擎工作时，需要以Page页为最小单位去将磁盘中的数据加载到内存中，与数据库相关的所有内容都存储在Page结构里。

Page分为几种类型，数据页和索引页就是其中最为重要的两种类型

插入缓冲（Insert Buﬀer）

主要针对次要索引的数据插入存在的问题而设计

我们都知道，在InnoDB引擎上进行插入操作时，一般需要按照主键顺序进行插入，这样才能获得较高的插入性能。当一张表中存在次要索引时，在插入时，数据页的存放还是按照主键进行顺序存放，但是对于次要索引叶节点的插入不再是顺序的了，这时就需要离散的访问次要索引页，由于随机读取的存在导致插入操作性能下降

InnoDB为此设计了Insert Buﬀer来进行插入优化。对于次要索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非主键索引是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buﬀer中。看似数据库这个非主键的索引已经插到叶节点，而实际没有，这时存放在另外一个位置。然后再以一定的频率和情况进行Insert Buﬀer和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中，这样就大大提高了对于非聚簇索引的插入性能

自适应哈希索引（Adaptive Hash Index）

InnoDB会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。InnoDB存储引擎会监控对表上各个索引页的查询，如果观察到建立哈希索引可以带来速度上的提升，则建立哈希索引，所以叫做自适应哈希索引

自适应哈希索引是通过缓冲池B+树页构建而来，因此建立速度很快，而且不需要对整张数据表建立哈希索引。其有一个要求，即对这个页的连续访问模式必须是一样的，也就是说其查询的条件（WHERE）必须完全一样，而且必须是连续的

锁信息（Lock Info）

InnoDB存储引擎会在行级别上对表数据进行上锁，不过InnoDB也会在数据库内部其他很多地方使用锁，从而允许对多种不同资源提供并发访问，数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性

数据字典信息（Data Dictionary）

InnoDB有自己的表缓存，可以称为表定义缓存或数据字典，当InnoDB打开一张表，就会增加一个对应的对象到数据字典

数据字典是对数据库中的数据、库对象、表对象等元素的集合。在MySQL中，数据字典信息内容就包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容。

内存数据落盘分析

整体思路分析

InnoDB内存缓冲池中的数据page要完成持久化的话，是通过两个流程来完成的，一个是脏页落盘，一个是预写redo log日志，这样才能保证数据的可靠性

当缓冲池中的页的版本比磁盘要新时，数据库需要将新版本的页从缓冲池刷新到磁盘。但是如果每次一个页发送变化，就进行刷新，那么性能开发是非常大的，于是InnoDB对于数据文件和日志文件的刷盘遵守了Write AheadLog（WAL）策略和Force Log at Commit两种规则，二者保证了事务的持久性

WAL要求数据的变更写入到磁盘前，首先必须将内存中的日志写入到磁盘，即当事务提交时，先写重做日志，然后再择时将脏读写入磁盘，如果发生宕机导致数据丢失，就通过重做日志进行数据恢复

InnoDB存储引擎会首先将重做日志信息先放入重做日志缓冲中，然后再按照一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置得很大，因为一般情况每一秒都会将重做日志缓冲刷新到日志文件中，可通过配置参数innodb_log_buffer_size控制，默认为8MB

除每秒刷新机制之外，每次事务提交时重做日志缓冲也会刷新到日志中。InnoDB是事务的存储引擎，其通过Force-log-at-commit机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，然后事务的提交操作才算完成

Force-log-at-commit要求当一个事务提交时，所有产生的日志都必须刷新到磁盘上，如果日志刷新成功后，缓冲池中的数据刷新到磁盘前数据库发生了宕机，那么重启时，数据库可以从日志中恢复数据

如上图所示，InnoDB在缓冲池中变更数据时，会首先将相关变更写入重做日志缓冲中，然后再按时或者当事务提交时写入磁盘，这符合Force-log-at-commit原则；当重做日志写入磁盘后，缓冲池中的变更数据才会依据checkpoint机制择时写入到磁盘中，这符合WAL原则。在checkpoint择时机制中，就有重做日志文件写满的判断。所以，如前文所述，如果重做日志文件太小，经常被写满，就会频繁导致checkpoint将更改的数据写入磁盘，导致性能抖动

为了确保每次日志都写入到重做日志文件，在每次将重做日志缓冲写入重做日志后，必须调用一次fsync操作（操作系统的函数），将缓冲文件从文件系统缓存中真正写入磁盘

可以通过innodb_flush_log_at_trx_commit来控制重做日志刷新到磁盘的策略

该参数的默认值为1，表示事务提交必须进行一次fsync操作（操作系统的函数），还可以设置为0和2

0表示事务提交时不进行写入重做日志操作，该操作只在主线程中完成

2表示提交时写入重做日志，但是之邪入文件系统缓存，不进行fsync操作

由此可见，设置为0时，性能最高，但是丧失了事务的一致性

捋一捋

将数据加载到内存中，是在内存中发生的修改，以page为单位加载到内存中进行存储

磁盘中页中的数据和内存中页的数据不一样的时候，就是脏页

此时就要开始两个流程，一个就是脏页落盘，一个就是预写rudo log日志

为什么要写redo log日志，因为写redo log写入速度很快，它是一种顺序写入，它的作用是先写redo log日志，之后进行脏页落盘，如果在进行脏页落盘的时候发生了宕机数据也不会丢，所以在rudo log日志中存入相应的日志数据

在进行rudo log file的时候先缓存到redo log buffer中，之后一并将redo log buffer中的数据存储到rudo log file中，这样就变相提高写rudo log日志的性能

rudo log日志只有在数据丢失的时候才会有用，如果落盘成功的话，redo log日志中相应的内容就可以被清掉

只要事务提交，就强制写入redo log file中

脏页落盘

在数据库中进行读取操作，将从磁盘中读到的页放在缓冲池中，下次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页

对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为CheckPoint的机制刷新回磁盘

重做日志落盘

InnoDB存储引擎会首先将重做日志信息先放入重做日志缓冲中，然后再按照一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置得很大，因为一般情况每一秒钟都会讲重做日志缓冲刷新到日志文件中。可通过配置参数innodb_log_buffer_size控制，默认为8MB

CheckPoint检查点机制

简介

思考一下这个场景：如果重做日志可以无限地增大，同时缓冲池也足够大，那么是不需要将缓冲池中页的新版本刷新回磁盘。因为当发生宕机时，完全可以通过重做日志来恢复整个数据库系统中的数据到宕机发生的时刻

但是这需要两个前提条件：1. 缓冲池可以缓存数据库中所有的数据，2. 重做日志可以无限增大

因此Checkpoint（检查点）技术就诞生了，目的是解决以下几个问题

1、缩短数据库的恢复时间

2、缓冲池不够用时，将脏页刷新到磁盘

3、重做日志不可用时，刷新脏页

当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。数据库只需对Checkpoint后的重做日志进行恢复，这样就大大缩短了恢复的时间

当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷回磁盘

当重做日志出现不可用时，因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的。重做日志可以被重用的部分是指这些重做日志已经不再需要，当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。如果重做日志还需要使用，那么必须强制Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置

对于InnoDB存储引擎而言，是通过LSN（Log Sequence Number）来标记版本的

Checkpoint发生的时间、条件及脏页的选择等都非常复杂。而Checkpoint所做的事情无外乎是将缓冲池中的脏页刷回到磁盘，不同之处在于每次刷新多少页到磁盘，每次从哪里取脏页，以及什么时间触发Checkpoint

Checkpoint分类

在InnoDB存储引擎内部，有两种Checkpoint，分别为：Sharp Checkpoint、Fuzzy Checkpoint

sharp checkpoint：在关闭数据库的时候，将buﬀer pool中的脏页全部刷新到磁盘中

fuzzy checkpoint：数据库正常运行时，在不同的时机，将部分脏页写入磁盘。仅刷新部分脏页到磁盘，也是为了避免一次刷新全部的脏页造成的性能问题

Fuzzy Checkpoint：

1、Master Thread Checkpoint

在Master Thread中，会以每秒或者每10秒一次的频率，将部分脏页从内存中刷新到磁盘，这个过程是异步的。正常的用户线程对数据的操作不会被阻塞

2、FLUSH_LRU_LIST Checkpoint

FLUSH_LRU_LIST checkpoint是在单独的page cleaner线程中执行的

MySQL对缓存的管理是通过buﬀer pool中的LRU列表实现的，LRU 空闲列表中要保留一定数量的空闲页面，来保证buﬀer pool中有足够的空闲页面来相应外界对数据库的请求

当这个空间页面数量不足的时候，发生FLUSH_LRU_LIST checkpoint

空闲页的数量由innodb_lru_scan_depth参数表来控制的，因此在空闲列表页面数量少于配置的值的时候，会发生checkpoint，剔除部分LRU列表尾端的页面

3、Async/Sync Flush Checkpoint

Async/Sync Flush checkpoint是在单独的page cleaner线程中执行的

Async/Sync Flush checkpoint 发生在重做日志不可用的时候，将buﬀer pool中的一部分脏页刷新到磁盘中，在脏页写入磁盘之后，事物对应的重做日志也就可以释放了

关于redo_log文件的的大小，可以通过innodb_log_file_size 来配置

对于是执行Async Flush checkpoint还是Sync Flush checkpoint，由checkpoint_age以及async_water_mark和sync_water_mark来决定

##即checkpoint_age等于最新的lsn减去已经刷新到磁盘的lsn的值
checkpoint_age = redo_lsn-checkpoint_lsn
async_water_mark = 75%*innodb_log_file_size
sync_water_mark = 90%*innodb_log_file_size

当checkpoint_age<sync_water_mark的时候，无需执行Flush checkpoint。也就说，redo
log剩余空间超过25%的时候，无需执行Async/Sync Flush checkpoint
当async_water_mark<checkpoint_age<sync_water_mark的时候，执行Async Flush
checkpoint，也就说，redo log剩余空间不足25%，但是大于10%的时候，执行Async Flush
checkpoint，刷新到满足条件1
当checkpoint_age>sync_water_mark的时候，执行sync Flush checkpoint。也就说，redo
log剩余空间不足10%的时候，执行Sync Flush checkpoint，刷新到满足条件1。
在mysql 5.6之后，不管是Async Flush checkpoint还是Sync Flush checkpoint，都不会阻
塞用户的查询进程

总结

由于磁盘是一种相对较慢的存储设备，内存与磁盘的交互是一个相对较慢的过程由于innodb_log_ﬁle_size定义的是一个相对较大的值，正常情况下，由前面两种checkpoint刷新脏页到磁盘，在前面两种checkpoint刷新脏页到磁盘之后，脏页对应的redo log空间随即释放，一般不会发生Async/Sync Flush checkpoint。同时也要意识到，为了避免频繁低发生Async/SyncFlush checkpoint，也应该将innodb_log_ﬁle_size配置的相对较大一些

4、Dirty Page too much Checkpoint

Dirty Page too much Checkpoint是在Master Thread 线程中每秒一次的频率实现的

Dirty Page too much 意味着buﬀer pool中的脏页过多，执行checkpoint脏页刷入磁盘，保证buﬀer pool中有足够的可用页面

Dirty Page 由innodb_max_dirty_pages_pct配置，innodb_max_dirty_pages_pct的默认值在innodb 1.0之前是90%，之后是75%

Double Write双写

如果说Insert Buﬀer给InnoDB存储引擎带来了性能上的提升，那么Double Write带给InnoDB存储引擎的是数据页的可靠性

如上图所示，Double Write由两部分组成，一部分是内存中的double write buﬀer，大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为2MB

在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制到内存中的double write buﬀer区域，之后通过double write buﬀer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免操作系统缓冲写带来的问题。在完成doublewrite页的写入后，再讲double wirite buﬀer中的页写入各个表空间文件中

如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间中的double write中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志

Redo log Buﬀer重做日志缓冲

如上图所示，InnoDB在缓冲池中变更数据时，会首先将相关变更写入重做日志缓冲中，然后再按时或者当事务提交时写入磁盘，这符合Force-log-at-commit原则

当重做日志写入磁盘后，缓冲池中的变更数据才会依据checkpoint机制择时写入到磁盘中，这符合WAL原则

在checkpoint择时机制中，就有重做日志文件写满的判断，所以，如前文所述，如果重做日志文件太小，经常被写满，就会频繁导致checkpoint将更改的数据写入磁盘，导致性能抖动

操作系统的文件系统是带有缓存的，当InnoDB向磁盘写入数据时，有可能只是写入到了文件系统的缓存中，没有真正的“落袋为安”

InnoDB的innodb_ﬂush_log_at_trx_commit属性可以控制每次事务提交时InnoDB的行为

当属性值为0时，事务提交时，不会对重做日志进行写入操作，而是等待主线程按时写入

当属性值为1时，事务提交时，会将重做日志写入文件系统缓存，并且调用文件系统的fsync，将文件系统缓冲中的数据真正写入磁盘存储，确保不会出现数据丢失

当属性值为2时，事务提交时，也会将日志文件写入文件系统缓存，但是不会调用fsync，而是让文件系统自己去判断何时将缓存写入磁盘

innodb_ﬂush_log_at_commit是InnoDB性能调优的一个基础参数，涉及InnoDB的写入效率和数据安全。

当参数值为0时，写入效率最高，但是数据安全最低

参数值为1时，写入效率最低，但是数据安全最高

参数值为2时，二者都是中等水平。

一般建议将该属性值设置为1，以获得较高的数据安全性，而且也只有设置为1，才能保证事务的持久性

日志的刷盘机制如下图所示

该参数默认值为1
可以通过innodb_flush_log_at_trx_commit来控制重做日志刷新到磁盘的策略。该参数默认值为1，表示事务提交必须进行一次fsync操作，还可以设置为0和2。

0表示事务提交时不进行写入重做日志操作，该操作只在主线程中完成

2表示提交时写入重做日志，但是只写入文件系统缓存，不进行fsync操作

由此可见，设置为0时，性能最高，但是丧失了事务的一致性。

InnoDB磁盘文件

InnoDB的主要的磁盘文件主要分为三大块：一是系统表空间，二是用户表空间，三是redo日志文件和归档文件。二进制文件(binlog)等文件是MySQL Server层维护的文件，所以未列入InnoDB的磁盘文件中

系统表空间和用户表空间

上图显示InnoDB存储引擎对于文件的存储方式，其中frm文件是表结构定义文件，记录每个表的表结构定义

系统表空间存储哪些数据

系统表空间是一个共享的表空间，因为它是被多个表共享的

InnoDB系统表空间包含InnoDB数据字典(元数据以及相关对象)、double write buﬀer、change buﬀer、undo logs的存储区域

系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是一个共享的表空间因为它是被多个表共享的

1、数据字典(data dictionary)：记录数据库相关信息

2、doublewrite write buﬀer：解决部分写失败（页断裂）

3、insert buﬀer：内存insert buﬀer数据，周期写入共享表空间，防止意外宕机

4、回滚段(rollback segments)

5、undo空间：undo页

系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据

系统表空间是以每个数据库为单位的

系统表空间配置解析

系统表空间是由一个或者多个数据文件组成

默认情况下，一个初始大小为10MB，名为ibdata1的系统数据文件在MySQL的data目录下被创建。用户可以使用innodb_data_file_path对数据文件的大小和数量进行配置

mysql> show variables like 'innodb_data%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir  |                        |
+-----------------------+------------------------+
2 rows in set (0.01 sec)

innodb_data_file_path 的格式如下

1	innodb_data_file_path=datafile1[,datafile2]...

用户可以通过多个文件组成一个表空间，同时制定文件的属性

1	innodb_data_file_path = /db/ibdata1:1000M;/dr2/db/ibdata2:1000M:autoextend

这里将/db/ibdata1和/dr2/db/ibdata2两个文件组成系统表空间

如果这两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能

两个文件的文件名之后都跟了属性，表示文件ibdata1的大小为1000MB，文件ibdata2的大小为1000MB，而且用完空间之后可以自动增长(autoextend)

设置innodb_data_file_path参数之后，所有基于InnoDB存储引擎的表的数据都会记录到该系统表空间中，如果设置了参数innodb_file_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立的用户表空间。用户表空间的命名规则为：表名.ibd。

通过这种方式，用户不用将所有数据都存放于默认的系统表空间中，但是用户表空间只存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的表空间中

注

innodb_data_file_path用来指定innodb tablespace文件，如果我们不在My.cnf文件中指定innodb_data_home_dir和innodb_data_file_path那么默认会在datadir目录下创建ibdata1 作为innodb tablespace

如何使用用户表空间

如果设置了参数innodb_ﬁle_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立的用户表空间。用户表空间的命名规则为：表名.ibd

通过这种方式，用户不用将所有数据都存放于默认的系统表空间中

用户表空间存储哪些数据

用户表空间只存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的系统表空间中

1、每个表的数据和索引都会存在自已的表空间中

2、每个表的结构

3、undo空间：undo页（需要设置）

重做日志文件和归档文件

哪些文件是重做日志文件

默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logﬁle0和ib_logﬁle1的文件，这就是InnoDB的重做日志文件(redo log ﬁle)，它记录了对于InnoDB存储引擎的事务日志

重做日志文件的作用是什么

当InnoDB的数据存储文件发生错误时，重做日志文件就能派上用场。InnoDB存储引擎可以使用重做日志文件将数据恢复为正确状态，以此来保证数据的正确性和完整性

为了得到更高的可靠性，用户可以设置多个镜像日志组，将不同的文件组放在不同的磁盘上，以此来提高重做日志的高可用性

重做日志文件组是如何写入数据的

每个InnoDB存储引擎至少有1个重做日志文件组(group)，每个文件组下至少有2个重做日志文件，如默认的ib_logﬁle0和ib_logﬁle1

在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行

InnoDB存储引擎先写入重做日志文件1，当文件被写满时，会切换到重做日志文件2，再当重做日志文件2也被写满时，再切换到重做日志文件1

如何设置重做日志文件大小

用户可以使用innodb_log_ﬁle_size来设置重做日志文件的大小，这对InnoDB存储引擎的性能有着非常大的影响

如果重做日志文件设置的太大，数据丢失时，恢复时可能需要很长的时间

另一方面，如果设置的太小，重做日志文件太小会导致依据checkpoint的检查需要频繁刷新脏页到磁盘中，导致性能的抖动

InnoDB的事务分析

数据库事务具有ACID四大特性。ACID是以下4个词的缩写：

原子性(atomicity) ：事务最小工作单元，要么全成功，要么全失败
一致性(consistency)：事务开始和结束后，数据库的完整性不会被破坏
隔离性(isolation) ：不同事务之间互不影响，四种隔离级别为RU（读未提交）、RC（读已提交）、RR（可重复读）、SERIALIZABLE （串行化）
持久性(durability) ：事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失

下面我们就来详细讲解一下上述示例涉及的事务的ACID特性的具体实现原理。总结来说，事务的隔离性由多版本控制机制和锁实现，而原子性、一致性和持久性通过InnoDB的redo log、undo log和Force Log at Commit机制来实现

原子性，持久性和一致性

原子性，持久性和一致性主要是通过redo log、undo log和Force Log at Commit机制机制来完成的。redo log用于在崩溃时恢复数据，undo log用于对事务的影响进行撤销，也可以用于多版本控制。而Force Log at Commit机制保证事务提交后redo log日志都已经持久化

RedoLog

数据库日志和数据落盘机制，如下图所示

redo log写入磁盘时，必须进行一次操作系统的fsync操作，防止redo log只是写入了操作系统的磁盘缓存中。参数innodb_ﬂush_log_at_trx_commit可以控制redo log日志刷新到磁盘的策略

UndoLog

UndoLog没有专门存储成一个文件，它存储到系统表空间里，UndoLog有两个作用，一个是用来做事务回滚，一个是用来做MVCC的版本记录

UndoLog中分为两类进行存储

insert undolog：做insert插入操作时，产生的回滚日志

update undolog：做delete和update操作时，产生的回滚日志

undolog没有单独的文件，而是存储到系统表空间中的（ibdata1）

数据库崩溃重启后需要从redo log中把未落盘的脏页数据恢复出来，重新写入磁盘，保证用户的数据不丢失。当然，在崩溃恢复中还需要回滚没有提交的事务。由于回滚操作需要undo日志的支持，undo日志的完整性和可靠性需要redo日志来保证，所以崩溃恢复先做redo恢复数据，然后做undo回滚

在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作

事前准备

第一种其实没有加锁，串行化就是读写都加锁，中间两种是mvcc，即读不加锁写加锁

聚集索引和非聚集索引

主键索引是聚集索引，辅助索引是非聚集索引

本文标题:

文章作者:爱吃鱼的呆先生

发布时间:2020-03-24, 10:32:20

最后更新:2020-03-26, 10:01:37

原始链接:http://yoursite.com/2020/03/24/8MySQL/3MySQL/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。