锁

lock

全局锁

锁整个数据库的，加锁后数据库只读。后续的DML、DDL包括已经更新操作的事务提交都被阻塞
应用场景:数据库逻辑备份，获得一致性视图，保证完整性
set global readonly=true也可以实现全库只读
- 但是readonly的值可能用来处理其他逻辑，比如主从库判断；
- 并且FTWRL可以再数据库异常退出的时候自动释放，但是readonly不行

加全局锁: flush tables with read lock;(FTWRL)
数据库备份: musqldump -u用户名 -p密码数据库名 > 路径/数据库名.sql
解锁: unlock tables;
- 数据库备份的mysqldump命令只是一个工具，不是mysql内部的命令，因此不能在mysql界面中使用。在控制台使用即可，如果备份远程数据库，则需要使用-h 设置ip
缺点:
1. 主库上备份，则备份期间业务停摆，无法更新
2. 从库上备份，则备份期间主库可以更新，但是从库无法执行主库同步的二进制日志(binlog)，导致主从延迟
- InnoDB引擎中为了解决这个问题，加上--single-transaction参数完成不加锁的一致性备份。
- mysqldump --single-transaction -u用户 -p密码数据库名 > 路径/数据库名.sql
- 实际上通过快照读实现，这种方式不需要添加全局锁
- 但是MyISAM中不支持这个隔离级别，所以就要FTWRL，因为–single-transaction需要数据库中所有的表的引擎都支持事务才行。

表级锁

每次锁住整张表，粒度大，锁冲突的概率最高，并发度最低。
表级锁分三类:表锁、元数据锁(MDL)、意向锁

表锁

表锁分两类: 表共享读锁(read lock)、表独占写锁(write lock)
1. 表共享读锁，所有人(包括自己)都只能看，不能写入
2. 表独占写锁，其他人不能看也不能写，加锁的人可读可写

加锁: lock tables 表名... read / write
解锁: unlock tables;或者关闭客户端

元数据锁

系统自动控制，无需显式使用，访问表的时候自动加上, 是server层的锁
维护表元数据的一致性，在表有活动事务的时候，不能对元数据进行写入
为了避免DML和DDL的冲突，保证读写的正确性
在对一张表进行增删改查(DML)，自动加MDL共享读锁
在对表结构变更的时候(DDL)，自动加MDL独占写锁
查看元数据锁: select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks;
- 如果session A申请MDL读锁，session B在之后申请MDL读锁，不会有问题。session C在之后申请MDL写锁，会被阻塞；如果session D再session C后面申请MDL读锁，也会被阻塞，因为session C的申请没有被处理。
- 为了解决这个问题，就要解决长事务；或者在alert table(DDL)中加入等待时间，如果等待时间内没有拿到MDL写锁就放弃

对应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都互斥

意向锁

A更新某一行，添加了行锁，B同时想加表锁。这个时候，需要B一行一行检查是否存在行锁，性能极低。
为了减少DML在执行时，加的行锁和表锁的冲突，在InnoDB中引入了意向锁。
意向锁不用检查每行是否加锁，可以减少表锁的检查。
A更新某一行，添加了行锁，然后会对表添加意向锁。B同时想加表锁，就先去检查是否存在意向锁。
意向锁分为两类: 意向共享锁(IS)、意向排他锁(IX)
查看意向锁以及行锁: select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

意向共享锁(IS)
- 由语句select ... lock in share mode添加
- 与表锁中的共享锁(read)兼容，与表锁排他锁(write)互斥
- select * form score where id=1 lock in share mode;会加上行共享锁，同时会添加意向共享锁
意向排他锁(IX)
- 由语句insert、update、delete、select ... for update添加
- 与表锁共享锁(read)以及排他锁(write)都互斥，意向锁之间不互斥
- update score set math=66 where id = 1会加上行锁，同时会添加意向排他锁。

行级锁

每次锁住对应的行，粒度小，锁冲突的概率最低，并发度最高。
InnoDB数据基于索引组织，行锁通过对索引上的索引项加锁实现，不是对记录加锁。
行级分三类: 行锁、间隙锁、临键锁

行锁

锁定单个行记录，防止其他事务进行update或delete，在RC,RR下都支持
行锁分两类: 共享锁(S)、排他锁(X)
行锁需要的时候才会加，但是在提交的时候才会释放，这就是两阶段协议
- 所以如果会锁住多行，要把最可能冲突的行影响并发度的行放后面

共享锁(S)
- 允许一个事务读一行，阻止其他事物获得相同数据集的排他锁
排他锁(X)
- 允许获取排他锁的事务更新数据，阻止其他事物获得相同数据集的排他锁和共享锁

	共享锁	排他锁
共享锁	兼容	冲突
排他锁	冲突	冲突

SQL	行锁类型	说明
INSERT	排他锁	自动加锁
UPDATE	排他锁	自动加锁
DELETE	排他锁	自动加锁
SELECT	不加锁
SELECT … LOCK IN SHARE MODE	共享锁
SELECT … FOR UPDATE	排他锁

InnoDB在RR下会使用临键锁进行搜索和索引扫描，防止幻读。
针对唯一索引进行检索时，对已经存在的记录进行等值匹配时，会自动优化为行锁
InnoDB行锁针对索引加锁，不通过索引条件检索数据，那么InnoDB将对表中所有记录加锁，此时升级为表锁。

间隙锁

锁定索引记录间隙(不包含该记录)，确保索引记录间隙不变，防止其他事物在这个间隙insert，导致幻读，在RR隔离级别下支持

InnoDB在RR下会使用临键锁进行搜索和索引扫描，防止幻读。
索引上的等值查询(唯一索引)，给不存在的记录加锁时，会优化为间隙锁
索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询要求时，临键锁退化为间隙锁

临键锁

行锁和间隙锁的组合，同时锁住数据以及数据前的间隙，在RR隔离级别下支持

InnoDB在RR下会使用临键锁进行搜索和索引扫描，防止幻读。
索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止，生成临键锁

死锁

45_deadlock

事务A等待事务B释放id=2的行锁，事务B等待事务A释放id=1的行锁，循环等待，进入死锁。

解决死锁的两种策略

直接进入等待，直到超时，超时时间为innodb_lock_wait_timeout，默认50s，特别长，但是又不能缩短为1s，这样如果正常的请求时间长点就会被误杀。
死锁检测，发现死锁以后回滚某一个事务，让其他事务可以正常执行，将innodb_deadlock_detect设置为on，表示开启死锁检测逻辑

一般会使用死锁检测的方法，就是一个事务被锁的时候，判断依赖的线程有没有被别人锁住，最后判断是否出现循环等待，也就是死锁。
但是如果多个事务更新同一行，每个后面来的线程都会判断是不是由于自己导致死锁，时间复杂度 $O(n)$ $O (n)$ ，每个线程都要遍历一次其他线程，所以最终时间复杂度 $O(n^2)$ $O (n^{2})$
- 比如1000个线程同时修改一行数据，那么每个线程都要遍历其他999个线程判断是否存在循环依赖，就需要判断100万次。这样虽然不会出现死锁，但是CPU利用率很高，每秒却无法处理几个事务。
  - 对于上述的热点行，可以临时关闭死锁检测，就需要等待50s超时，风险较高
  - server层限流，限制同一时间进入更新的线程数；可以使用中间件, 或者修改MySQL源码，对于同一行的修改，进入引擎之前排队。
  - 拆行，将一行拆分为多行减少冲突概率，原理上就是分段汇总，比如java的原子类LongAdder

`InnoDB`引擎

逻辑存储结构: 表空间 - 段 - 区 - 页 - 行

表空间(ibd文件)，一个MySQL实例可以对应多个表空间，用于存储记录，索引
段分为数据段(Leaf node segment)，索引段(Non-leaf node segment)，回滚段(Rollback segment)。数据段就是B+树叶子节点，索引段就是非叶子节点，段用来管理多个区
区默认大小为1M，默认存储64个页
页默认大小为16K，为了保证页的连续性，InnoDB每次从磁盘申请4~5个区。
行，数据按行存放。Trx_id就是最后一次事务操作的id;Roll_pointer是某条记录改动时，把旧版本写入undo log中，相当于一个指针，可以找到记录修改前的信息。

架构

左侧是内存结构，右侧是磁盘结构:

内存结构

Buffer Pool
- 缓冲池是主内存中的一个区域，可以缓存磁盘上经常操作的真实数据。增删改查操作缓冲池中的数据，没有数据则从磁盘中加载到缓冲池中，再以一定的频率刷新到磁盘，减少磁盘I/O，加快处理速度。缓冲池以页为单位，底层采用链表管理页，页分三类:
  (1) free page: 空闲页，没有使用
  (2) clean page: 被使用的页，数据没有修改过
  (3) dirty page: 脏页，被使用过被修改过，与磁盘不一致的数据(缓冲池中还没有刷新到磁盘的数据)
Change Buffer
- 更改缓冲区，(针对非唯一二级索引页，主键索引这种唯一索引是不会存储在更改缓冲区中)，执行DML语句时，如果这些数据页没有在缓冲池中，不会直接操作磁盘，而是将数据变更存在Change Buffer中，在未来数据被读取时，合并恢复到缓冲池中，再将合并后的数据刷新到磁盘
- 因为二级索引通常是非唯一的，以随机顺序插入二级索引。删除和更新可能会影响索引树中不相邻的二级索引页。每一次都操作磁盘则会产生大量的磁盘I/O，更改缓冲区可以在缓冲池中合并处理，减少磁盘I/O
Adaptive Hash Index
- 自适应哈希索引，InnoDB本身不支持哈希索引，支持B+树索引。但是B+树索引是等值索引，因此引入自适应哈希索引优化对缓冲池数据的查询。InnoDB会监控表上对各索引页的查询，如果观察到哈希索引可以提高速度，则会自动建立哈希索引，即为自适应哈希索引。
- 参数: adaptive_hash_index是自适应哈希索引的开关，默认是开启的
Log Buffer
- 日志缓冲区，用来保存将要写入到磁盘中的Log日志数据(redo log, undo log)，大小默认为16MB。日志会定期刷新到磁盘中，如果需要更新、插入或删除多行事务，增加日志缓冲区的大小可以节省磁盘I/O
- 参数: innode_log_buffer_size 缓冲区大小
- innodb_flush_log_at_trx_commit 日志刷新到磁盘的时机
  - 0: 日志每秒写入并刷新
  - 1(默认): 日志在每次事务提交时写入并刷新到磁盘
  - 2: 日志在每次事务提交后写入，并且每秒刷新到磁盘

磁盘结构

System Tablespace
- 系统表空间，是更改缓冲区的存储区域。如果表在系统表空间而不是每个表文件或者通用表空间中创建的，也可能包含表和索引数据
- 参数: innodb_data_file_path
File-Per-Table Tablespaces
- 每个表的文件表空间包含单个表的数据和索引，存储在文件系统的单个数据文件中。
- 参数: innodb_file_per_table默认为开启的，如果不开启，则存储在系统表空间中
General Tablespaces
- 通用表空间，需要通过create tablespace语法创建，建表时可以指定该表空间
- 创建一个表空间: create tablespace 表空间名 add datafile 表名.ibd engine=引擎名;
- 创建一张表并指定表空间: create table xxx ... tablespae 表空间名;
Undo Tablespaces
- 撤销表空间，MySQL初始化时自动创建两个默认的undo表空间，初始大小16M，用于存储undo log
Temporary Tablespaces
- 临时表空间，使用会话临时表空间和全局临时表空间，存储用户创建的临时表等数据
Doublewrite Buffer Files
- 双写缓冲区，InnoDB将数据页从缓冲池刷新到磁盘前，将数据页写入双写缓冲区文件中，便于系统异常时恢复数据
- 后缀: .dblwr
Redo Log
- 重做日志，实现事务的持久性。日志文件由两部分组成，重做日志缓冲区redo log buffer和重做日志文件redo log。前者在内存中，后者在磁盘中。事务提交以后会把所有的修改信息都存到该日志中用于在刷新脏页到磁盘时，发生错误时进行数据恢复使用。
- redo log 不会循环写，每隔一段时间清理之前没用的内容。
- ib_logfile

后台线程

Master Thread 核心后台线程，负责调度其他线程，同时将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性。包括脏页的刷新，合并插入缓存，undo页的回收
IO Thread 使用AIO来处理IO请求，IO Thread主要负责这些I/O请求的回调

线程类型	默认个数	职责
Read Thread	4	负责读
Write Thread	4	负责写
Log Thread	1	负责将日志缓冲区刷新到磁盘
Insert Buffer Thread	1	负责将写缓冲区的内容刷新到磁盘

show engine innodb status;查看InnoDB引擎的状态信息

Purge Thread回收事务已经提交了的undo log，事务提交之后，undo log可能不用了，就用该线程来回收
Page Cleaner Thread是协助 Master Thread刷新脏页到磁盘的线程，减轻Master Thread的工作压力，减少阻塞

事务原理

一致性是通过redo log, undo log来保证的
隔离性通过锁机制，MVCC(多版本并发控制)实现
持久性由redo log保证
原子性由undo log保证
undo log记录数据被修改前的信息，包含回滚和MVCC
undo log记录逻辑日志，redo log记录物理日志
当delete时，undo log会记录一条insert记录；update时，undo log会有一条相反的update。执行rollback时，可以从逻辑记录中读取到相应的内容并回滚
undo log销毁: undo log在事务执行时产生，事务提交时，不会立即删除，因为可能还要用于MVCC
undo log存储: 采用段的方式进行管理和记录，存放在rollback segment中，内部包含1024个undo log segment

MVCC(多版本并发控制)

MVCC: 维护一个数据库的多个版本，使得读写操作没有冲突。
快照读为MVCC提供了一个非阻塞读的功能。
MVCC依赖:数据库记录中的三个隐式字段， undo log，readView实现

当前读: 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
- select ... lock in share mode(共享锁)
- select ... for update、update、insert、delete(排他锁)
- 上述二者都是一种当前读
快照读: 简单的select(不加锁)就是快照读，读取的是记录数据的可见版本，有可能是历史数据，非阻塞读
- Read Committed: 每次select都生成一个快照读
- Repeatable Read: 开启事务后第一个select语句才是快照读的地方，后面实际上查询的都是第一个读生成的快照
- Serializable: 快照读会退化为当前读

记录中的隐式字段

隐藏字段	含义
DB_TRX_ID	最近修改事务的ID，记录插入这条记录或最后一次修改该记录的事务ID
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合undo log
DB_ROW_ID	隐藏主键，如果表没有主键才会生成

查看表空间ibd文件: ibd2sdi 文件名.idb

undo log
- undo log日志可以被删除，但是不会立即删除
  - 当insert的时候，产生的undo log只在回滚时需要，事务提交以后，可以立即被删除
  - 当update, delete的时候，产生的undo log日志不仅在回滚时需要，在快照读的时候也需要，不会立即被删除
undo log 版本链
- 不同事物或相同事务对同一条记录进行修改，导致该记录的undo log生成一条记录版本链表，链表头部是最新的旧记录，链表尾部是最早的旧记录

readView

读视图是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id，包含四个核心字段

字段	含义
m_ids	当前活跃事务ID
min_trx_id	最小活跃事务ID
max_trx_id	预分配事务，当前最大事务ID + 1(因为事务ID自增)
creator_trx_id	`readView`创建者的事务ID

版本链数据访问规则:

trx_id表示当前事务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中是可以访问该版本的。因为如果成立，表示数据已经提交了

Read Committed在事务中每一次执行快照读的时候生成readView
Repeatable Read在事务第一次执行快照读的时候生成readView，后续复用该readView

MySQL四个系统数据库

数据库	含义
mysql	存储MySQL服务器正常运行所需要的各种信息(时区，主从，用户，权限等)
information_schema	提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型已经访问权限等
performance_schema	为MySQL服务器运行时状态提供了一个底层监控功能，用于手机数据库服务器性能参数
sys	一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图

MySQL笔记_4

锁

全局锁

表级锁

表锁

元数据锁

意向锁

行级锁

行锁

间隙锁

临键锁

死锁

InnoDB引擎

逻辑存储结构: 表空间 - 段 - 区 - 页 - 行

架构

内存结构

磁盘结构

后台线程

事务原理

MVCC(多版本并发控制)

版本链数据访问规则:

MySQL四个系统数据库

`InnoDB`引擎