mysql基础知识汇总梳理

mysql 的基础知识

mysql官网：https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

mysql的架构上来分主要分为server和引擎层。

业务层

连接器:连接器负责跟客户端建立连接、获取权限、维持和管理连接；
查询缓存:服务的查询缓存，如果能找到对应的查询，则不必进行查询解析，优化，执行等过程，直接返回缓存中的结果集；
解析器:解析器会根据查询语句，构造出一个解析树，主要用于根据语法规则来验证语句是否正确，比如SQL的关键字是否正确，关键字的顺序是否正确;
优化器:解析树转化为查询计划，一般情况下，一条查询可以有很多种执行方式，最终返回相同的结果，优化器就是根据成本找到这其中最优的执行计划;
执行器:执行计划调用查询执行引擎，而查询引擎通过一系列API接口查询到数据;

引擎层

我们常说的InnoDB还是Myisam引擎，还有memory，这里只说InnoDb和myisam引擎。

innodb与myisam区别

1、事务层面

innodb支持事务，并且有4种隔离级别；myisam不支持事务。

innodb对hash索引的支持：虽然支持，但是不由dba干预，只能有innodb自动优化创建。

2、锁级别

innodb支持行级别锁和表级别锁；myisam只支持表级别锁。innodb中使用行级别锁并不是一定的，取决于查询条件。

3、索引层面

innodb支持B-Tree，Ful-text索引，innodb中是可以支持hash索引，但是必须手动启用，且使用hash索引，hash索引是由innodb进行自动创建、优化，不能进行干预，innodb中是支持聚簇索引和非聚簇索引；myisam支持 B-tree、Full-text 等索引，不支持 Hash 索引，不支持聚簇索引。

4、外键层面

innodb支持外键，而myisam不支持外键。

5、数据存储层面

innodb 的.ibd文件存储的是表的数据文件和索引文件，是放在一起的， .frm文件存储表定义；myisam中则是 .frm文件存储表定义，数据文件的扩展名为.MYD， 索引文件的扩展名是.MYI。

innodb官网介绍：https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html

innodb和myisam下的索引

索引其实是为了帮助mysql高效获取数据的一种数据结构，myisam和innodb索引底层都使用了B+树的数据结构，在innodb中索引可以分为聚簇索引和非聚簇索引，在一个表中只会存在一个聚簇索引，其他的都是非聚簇索引，要注意的是无论聚簇索引或者是非聚簇索引，它们只是在数据存储的格式上有区别。

聚簇索引

在innodb中一个表中聚簇索引的使用优先级：主键——》第一个UNIQUE索引且中属性为NOT NULL——》InnoDB会在包含行ID值的合成列内部生成名为GEN_CLUST_INDEX的隐藏聚集索引（这些行按InnoDB分配给此类表中的行的ID排序。行ID是一个6字节的字段，在插入新行时会单调增加。因此，由行ID排序的行在物理上处于插入顺序。）要注意的是虽然主键可以是聚簇索引，但也只是在innodb下，默认的聚簇索引首选是主键。

非聚簇索引（二级索引、辅助索引）

在一个表中，除了聚簇索引（只能有一个）其他的都是非聚簇索引，也就是我们常见的自己创建的索引。

回表

在innodb中，在聚簇索引（主键）B+树中，行数据是存放在叶子节点上的，其他节点存放的其实是主键，在非聚簇索引中，也就是我们自己创建的索引B+树中，叶子节点上存放的其实是我们的主键，其他节点上存放的就是我们的索引列，使用索引查询时的步骤其实是先从我们的非聚簇索引上查询到主键，再拿着主键去我们的聚簇索引B+树中查询到叶子结点中的数据，这个过程也就是我们的回表。

在myisam中，虽然myisam不支持聚簇索引，但是在myisam中依然是有主键索引B+树的（主键索引树只在innodb中被默认当成是聚簇索引），所以如果一个表中存在多个索引树，则每个索引树的叶子结点上存放的其实是数据真正存放的地址。

PS：为什么聚簇索引和非聚簇索引要这样存储数据？

首先innodb和myisam

我们可以想象一下，如果非聚簇索引上叶子节点不存储主键，存储行数据，这样虽然减少了一次聚簇索引的查询，但是我们的数据就要同时存放两份，存放两份带来的直接问题就是怎样去保证数据的一致性和存储空间的增大，所以非聚簇索引上只存储主键，其实是为了保证mysql中的数据一致性和节约存储空间，同时这样存储，后面我们需要更新数据，只需要更新一份即可。这也是innodb中数据文件和索引文件都存放在一个文件的一个原因。

而在myisam引擎下，因为索引树存放的是数据的物理地址，所以在文件存储上，索引文件要和表数据分开存放。

聚簇索引和非聚簇索引的区别及优劣势

聚簇索引和非聚簇索引的根本区别在于表记录的排列顺序和与索引的排列顺序是否一致。

首先我们要明确一点：索引的顺序和数据实际存放的顺序可能并不是一致的。

innodb中聚簇索引（以主键索引为例）中叶子节点中存储的就是行数据，**行数据在物理储器中的真实地址就是按照主键索引树形成的顺序进行排列的（也就是索引树是什么顺序，实际物理顺序就是什么样）。**所以在innodb下进行查询速度比较快（因为数据是存放在叶子节点，只需要一次主键查询就可以查询所有数据，同时因为局部性原理，每次mysql读取都会读取到16k大小的数据（可以通过innodb_page_size设置，默认16k））。但是这种结构存在的问题就是插入修改慢，因为在插入的时候，同时要对聚簇索引树进行调整，叶子节点上的数据会发生变动，因为在实际物理存储中，顺序只会存在一种，这也是为什么只有一个聚簇索引的原因。

非聚簇索引中因为叶子节点存放的是数据的物理地址，所以索引树的顺序其实只是一个逻辑顺序，和真实的物理地址没有关系。所以在修改的时候速度要比聚簇索引要快，因为只需要挪动叶子节点上的物理地址即可，不需要调整整个索引树的顺序。但是缺点就是在查询的时候，需要多出来一步（需要根据实际物理地址去数据文件中进行查找）。

总结：聚簇索引查询快（局部性原理、数据存放叶子节点），修改慢（涉及到数据修改，page分裂合并），非聚簇索引修改快（挪动叶子节点指针），查询慢（多了一步IO操作）。

索引的其他概念：

覆盖索引：查询的字段全部都是索引字段，即不需要回表查询，这样的索引查询称为覆盖索引。

索引下推（ICP—Index Condition Pushdown）：mysql5.6之后的优化。简单来说就是减少回表次数，进而提升查询效率。使用索引查询数据其实在引擎层进行查询的时候只会用到我们的第一个索引列，然后将查询结果返回给server层，由server层做其余条件的筛选，索引下推就是讲我们的其余索引条件一起传入引擎层，在引擎层做好索引匹配和判断，再返回给server层做判断，一定程度上减少了server层的判断和回表次数。

联合索引（复合索引）：简单的说就是在2列或2列以上上创建的索引。联合索引遵循最左匹配原则

日志文件与mysql的ACID

我们都知道mysql中事务的四大特性：ACID（Atomicity：原子性，Consistency：一致性，Isolation：隔离性，Durability：持久性），那么和日志有什么关系呢?Mysql中运用日志文件怎么保证ACID的特性？在mysql中只有redo log和undo log被称为事务日志。

数据保存/修改过程

在了解redo log和undo log之前我们先看下数据在插入/修改操作时，redo log和undo log、bin log是怎么进行工作的。

首先我们创建一张表T，主键为Id，辅助索引为a
create table T(id int primary key, a int not null, name varchar(16),index (a))engine=InnoDB;
接下来插入一条数据，
insert into t(id,a,name) values(id1,a1,‘哈哈’),(id2,a2,‘哈哈哈’);

1、经过server层，对sql进行解析、优化。

2、插入数据时候可能有两种场景:
第一种场景:假设Id1这条数据在内存池中,

1. 直接更新Buffer Pool中的Index Page和Data Page;
2. 写入redo log中，处于预提交状态；
3. 写入binlog中，
4. 提交事务，处于commit状态，两阶段提交；
5. 后台线程写入到数据文件的索引段和数据段中;

第二种场景假设id2这条数据不再内存池中,

1. 数据写入到内存池中，非聚集索引写入到Insert Buffer，其他数据写入Data Page中;
2. 后续的动作保持和上面剩下的步骤一样。

binlog

binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

binlog 中日志格式

1. ROW：基于行的复制，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。
   优点是不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题；缺点是
   因为每行都要记录日志，会照成日志量暴涨；
2. STATMENT
   基于SQL语句的复制，每一条会修改数据的sql语句会记录到binlog中。
   优点是不需要记录每一行的变化，减少了binlog日志量，节约了IO, 从而提高了性能；
   缺点是某些情况下会导致主从数据不一致，比如执行sysdate()等函数的时候。
3. MIXED
   基于STATMENT和ROW两种模式的混合复制，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog。

binlog的刷盘时机

对于innodb引擎来说，每次事务提交（commit）的时候才会记录binlog，但此时commit记录还是在内存中，并没有实际写到binlog文件中，mysql通过sync_binlog参数控制binlog的刷盘时机，取值范围为0~N。0表示由系统控制。大于0的时候代表每几次事务提交写入文件。

相关参数及查看bin log

log_bin：设置此参数表示启用binlog功能，并指定路径名称。开启为ON，如果设置此参数为其他值，如master-bin，则生成的bin log文件名前缀为master-bin.x

log_bin_index 设置此参数是指定二进制索引文件的路径与名称

binlog_do_db 此参数表示只记录指定数据库的二进制日志

binlog_ignore_db 此参数表示不记录指定的数据库的二进制日志

max_binlog_cache_size 此参数表示binlog使用的内存最大的尺寸

binlog_cache_size 此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。

binlog_cache_use：使用二进制日志缓存的事务数量

binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量

max_binlog_size Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束

sync_binlog 该参数直接影响mysql的性能和完整性

sync_binlog=0： 当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘        同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。

sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。
Mysql中默认的设置是sync_binlog=0，即不作任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统绷Crash，在文件系统缓存中的所有Binlog信息都会丢失

redo-log

redo log 主要用于保证事务的一致性和持久性（数据恢复）。redo log是属于mysql中innodb引擎层记录的日志，是物理日志，主要记录的是某个数据页上做了什么操作（即只记录对数据做了哪些变更，而不是将整个数据页进行记录）。要注意的是redo-log中redo -log的日志大小是固定的。

redo log的写入过程

以update语句为例： update tab set name= ‘aa’ where id= 1

1、首先获取到id=1这一行的数据（先去内存中查，没有去磁盘中查，然后放入缓存中）(server层)

2、将name值进行修改（server层），将修改结果存入内存中（引擎层）

3、写入redo log（写入详见刷盘策略，记录的是XX数据页做了XX的修改，同时记录事务状态处于prepare阶段）（引擎层）

4、server层写bin log，并通知引擎层提交事务

5、知引擎层提交事务（commit），同时将redo log的记录状态置为 commit。

mysql中的WAL（Write-Ahead Logging）：先写日志，再写磁盘。

redo log的刷盘策略

涉及参数

• innodb_flush_log_at_trx_commit={0、1、2}

  0：每秒将日志写入并刷新到磁盘一次。未刷新日志的事务可能会在崩溃中丢失。

  1：完全符合 ACID 需要默认设置 1。日志在每次事务提交时写入并刷新到磁盘。

  2：日志在每次事务提交后写入，并每秒刷新到磁盘一次。未刷新日志的事务可能会在崩溃中丢失。

• innodb_log_buffer_size：log buffer的大小，默认8M

• innodb_log_file_size：#事务日志的大小，默认5M

• innodb_log_files_group =2：# 事务日志组中的事务日志文件个数，默认2个

• innodb_log_group_home_dir =./：# 事务日志组路径，当前目录表示数据目录

• innodb_mirrored_log_groups =1：# 指定事务日志组的镜像组个数，但镜像功能好像是强制关闭的，所以只有一个log group。在MySQL5.7中该变量已经移除

redo log其实包括两部分：redo log buffer和redo log file。

其实在写入redo log日志后，并不是立即写入redo log file，而是先写入 redo log buffer（根据策略）中，但最终都是先到 OS BUFFER中，再写到redo log file中。

redo log的日志格式

redo log记录的是数据页的更改，大小是固定的，采用了循环写入的方式。

参数：

innodb_log_files_in_group ：控制redo log的文件个数（默认：2，ib_logfile_N：存储redo log日志)。2=<innodb_log_files_in_group <=100。

innodb_log_file_size：默认值：50331648（48MB）。最大值512GB。最小值：4194304（4M）。

在每个字节大小日志文件在日志组。日志文件 ( innodb_log_file_size* innodb_log_files_in_group)的组合大小不能超过略小于 512GB 的最大值。例如，一对 255 GB 的日志文件接近限制但未超过限制。默认值为 48MB。

redo log同时还用于mysql 的奔溃恢复。因此innodb_log_files_in_group 和innodb_log_file_size 的组合设置不适宜过大，导致崩溃恢复速度太慢，设置过小则会导致一次事务切换多个文件，影响性能。

checkPoint与 write Pos:

write pos到check point之间的部分是redo log空着的部分，用于记录新的记录；check point到write pos之间是redo log待落盘的数据页更改记录。当write pos追上check point时，会先推动check point向前移动，空出位置再记录新的日志。

LSN(log sequence number)

LSN(log sequence number)日志序列号，5.6.3之后占用8字节，LSN主要用于发生崩溃（crash）时对数据进行恢复（recovery），LSN是一个一直递增的整型数字，表示事务写入到日志的字节总量。LSN不仅只存在于redo log中，在每个数据页头部也会有对应的LSN号，该LSN记录当前页最后一次修改的LSN号，用于在recovery时对比重做日志LSN号决定是否对该页进行恢复数据。前面说的checkpoint也是有LSN号记录的，LSN号串联起一个事务开始到恢复的过程。

InnoDB在启动的时候，不管上次数据库是否正常关闭，都会尝试进行恢复操作，分为两种情况:

1. checkpoint表示已经完整刷到磁盘上data page上的LSN，因此恢复时仅需要恢复从checkpoint开始的日志部分，LSN表示写入日志的字节的总量，例如，当数据库在上一次checkpoint的LSN为10000时宕机，且事务是已经提交过的状态。启动数据库时会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从检查点开始恢复。
2. 在宕机前正处于checkpoint的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度。这时候一宕机，数据页中记录的LSN就会大于日志页中的LSN，在重启的恢复过程中会检查到这一情况，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

LSN类型：

show engine innodb status
LOG
---
Log sequence number          78849712
Log buffer assigned up to    78849712
Log buffer completed up to   78849712
Log written up to            78849712
Log flushed up to            78849712
Added dirty pages up to      78849712
Pages flushed up to          78849712
Last checkpoint at           78849712

1. Log sequence number: 当前系统最大的LSN号
2. log flushed up to:当前已经写入redo日志文件的LSN
3. pages flushed up to：已经将更改写入脏页的lsn号
4. Last checkpoint at就是系统最后一次刷新buffer pool脏中页数据到磁盘的checkpoint

undo log

undo log也是属于引擎层记录的日志，undo log的主要作用用于回滚，是用来保证事务的原子性。undo log主要存储的是数据的逻辑变化日志，比如说我们要insert一条数据，那么undo log就会生成一条对应的delete日志。简单点说，undo log记录的是数据修改之前的数据，因为需要支持回滚。数据库事务开始之前，会将要修改的记录存放到undo log里，当事务回滚时或者数 据库崩溃时，可以利用undo log，撤销未提交事务对数据库产生的影响。

undo log另一个作用是实现多版本控制(MVCC)，undo记录中包含了记录更改前的镜像，如果更改数据的事务未提交，对于隔离级别大于等于read commit的事务而言，不应该返回更改后数据，而应该返回老版本的数据。

undo log中也存在undo buffer和undo log，undo buffer与redo buffer一样，也是环形缓冲，但当缓冲满的时候，undo buffer中的内容会也会被刷新到磁盘；与redo log不同的是，磁盘上不存在单独的undo log文件，所有的undo log均存放在主ibd数据文件中（表空间），即使客户端设置了每表一个数据文件也是如此。

undo log 存放在共享表空间中，以段(rollback segment)的形式（回滚段）存在。

undoLog产生和销毁

undoLog 在事务开始前产生；事务在提交时，并不会立刻删除 undo log， innodb 会将该事务对应的 undo log 放入到删除列表中，后面会通过后台线程 purge thread 进行回收处理。undoLog 属于逻辑日志，记录一个变化过程。例如执行一个 delete ， undolog 会记录一个insert ；执行一个 update ， undolog 会记录一个相反的 update 。

delete/update 操作的内部机制

当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。

但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。

通过undo log记录delete和update操作的结果发现：(insert操作无需分析，就是插入行而已)

delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。

update分为两种情况：update的列是否是主键列。
1、果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。

2、如果是主键列，update分两部执行：先删除该行，再插入一行目标行

undo log的相关参数配置：

• innodb_undo_directory : undo log的独立表空间存放目录
• innodb_undo_logs ：回滚段大小
• innodb_undo_tablespaces : undo log 文件个数

事务中的隔离级别

再了解事务的隔离级别之前先了解一下事务并发下可能会出现的几种问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3、幻读：事务A将数据库中数据进行修改，但是事务B就在这个时候插入了一条新数据，当事务A修改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

事务隔离级别：

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交/不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

查看数据库事务级别：

show VARIABLES like ‘%transaction_isolation%’

transaction_isolation REPEATABLE-READ

MVCC（Multi-Version Concurrency Control）

多版本控制协议，只有在InnoDB引擎下存在。MVCC是为了实现事务的隔离性，通过版本号，避免同一数据在不同事务间的竞争，你可以把它当成基于多版本号的一种乐观锁。当然，**这种乐观锁只在事务级别读已提交和可重复读有效。**也就是说MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别和MVCC不兼容，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

MVCC的实现依赖于：三个隐藏字段、undo log和Read View（判断行数据的可见性），其核心思想就是：只能查找事务 id 小于等于当前事务 ID 的行；只能查找删除时间大于等于当前事务 ID 的行，或未删除的行。

三个隐藏字段

innodb实际为每一行数据都增加了三个隐藏字段：

DB_ROW_ID：一个唯一行号（DB_ROW_ID，6字节，如果聚簇索引中其他条件都不满足，将会使用该列作为聚簇索引），

DB_TRX_ID：一个记录创建的版本号DB_TRX_ID（每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列），

DB_ROLL_PTR：一个记录回滚的版本号（DB_ROLL_PTR，7字节）（每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息）。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vZ3wMcP0-1648392940468)(C:\Users\Administrator\Desktop\mysql梳理\new\mysql梳理\img\innodb隐藏咧.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2i5ZxNH2-1648392940469)(C:\Users\Administrator\Desktop\mysql梳理\new\mysql梳理\img\MVCC版本链.png)]

Read View

对于 RU(READ UNCOMMITTED) 隔离级别下，所有事务直接读取数据库的最新值即可，和 SERIALIZABLE 隔离级别，所有请求都会加锁，同步执行。所以这对这两种情况下是不需要使用到 Read View 的版本控制。对于 RC(READ COMMITTED) 和 RR(REPEATABLE READ) 隔离级别的实现就是通过上面的版本控制来完成。两种隔离界别下的核心处理逻辑就是判断所有版本中哪个版本是当前事务可见的处理。

在读已提交（READ COMMITTED）和可重复读（REPEATABLE READ）隔离级别下开启事务，执行查询sql时会生成当前事务的一致性视图ReadView。

读已提交和可重复读他们的区别就是readview 的生成时机不同：

• 读已提交（READ COMMITTED）每次读取数据前（以select语句为准）都生成一个ReadView；语句级多版本
• 可重复读（REPEATABLE READ）只在第一次读取数据（以select语句为准）时生成一个ReadView。事务级多版本。

ReadView中主要包含：

• m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
• min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
• max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。
• creator_trx_id：表示生成该ReadView事务的事务id。开启事务begin时不会分配事务id，只有执行第一个写操作事务才会分配事务id，只读操作事务id都默认为0。

比如有id为1，2，3这三个事务，id为3的事务提交了。那么此时ReadView：m_ids[1,2]，min_trx_id=1，max_trx_id=4。

• 如果被访问版本的 trx_id 属性值小于 m_ids 列表中最小的事务id，表明生成该版本的事务在生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
• 如果被访问版本的 trx_id 属性值大于 m_ids 列表中最大的事务id，表明生成该版本的事务在生成 ReadView 后才生成，所以该版本不可以被当前事务访问。
• 如果被访问版本的 trx_id 属性值在 m_ids 列表中最大的事务id和最小事务id之间，那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

对于使用已提交读（READ COMMITTED）和可重复读（REPEATABLE READ）隔离级别的事务来说，需要判断版本链中的哪个版本是当前事务可见的。根据版本链对比规则，从版本链里的最新数据开始逐条跟ReadView对比从而得到结果。

1、如果被访问版本的trx_id=creator_trx_id，表明当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

2、如果被访问版本的trx_id<min_trx_id，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。

3、如果被访问版本的trx_id>=max_trx_id，表明被访问版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。

4、如果min_trx_id<=被访问版本的trx_id<max_trx_id，需要判断trx_id是不是在m_ids列表中:

• 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被当前事务访问；
• 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被当前事务访问。

mysql中的锁

• 从性能上分为乐观锁(用版本对比来实现)和悲观锁

• 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)
  读锁（共享锁）：Shared Locks（S锁），针对同一份数据，多个读操作可以同时进行而不会互相影响

  ​ 写锁（排它锁）：Exclusive Locks（X锁），当前写操作没有完成前，它会阻断其他写锁和读锁

• 从对数据操作的粒度分，分为表锁，行锁，间隙锁

表锁

myisam中只支持表锁，innodb中支持表锁和行锁。

表锁的特点：开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

表锁下的IS锁和IX锁

• IS锁：意向共享锁、Intention Shared Lock。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
• IX锁，意向排他锁、Intention Exclusive Lock。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

也就是说无论IS锁还是IX锁，都是表级别锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。直接查看表有没有意向锁就可以知道表中有没有行锁。

行锁

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

行锁的种类：

• LOCK_REC_NOT_GAP：单个行记录上的锁。
• LOCK_GAP：间隙锁，锁定一个范围，但不包括记录本身。比如锁定a=5以及其前后2个范围内的数据，也就是将a=3,4,6,7这些行都锁了起来，不包括a=5本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
• LOCK_ORDINARY：锁定一个范围，并且锁定记录本身。比如锁定a=5以及其前后2个范围内的数据，也就是将a=3,4,5,6,7这些行都锁了起来。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

行锁分析：当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

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 :系统启动后到现在总共等待的次数

死锁

死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB.

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

解决方法：

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别事务隔离

间隙锁

当我们用范围条件条件检索数据（非聚簇索引、非唯一索引），并请求共享或排他锁时，InnoDB会给符合条件的数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，称为间隙，InnoDB也会为这些间隙加锁，即间隙锁。

在InnoDB下，间隙锁的产生需要满足三个条件：

• 隔离级别为RR
• 当前读
• 查询条件能够走到索引

间隙锁在某些情况下可以解决可重复读（REPEATABLE READ）的幻读问题。

事务在第一次执行读取操作时，幻影记录还不存在，所以无法给幻影记录加上记录锁。间隙锁，锁的就是两个值之间的空隙，为了防止插入幻影记录。

比如表中有id尾1、2、3、10、20的五条数据，间隙就有 id 为 (3,10]，(10,20]，(20,+∞) 这三个区间。当事务查找8<id<15之间的数据时，不仅查出的记录会加记录锁， (3,10]，(10,20]两个区间也会加间隙锁，所以此时想插入id为9的数据是会被阻塞的，直到拥有这个间隙锁的事务提交，才能插入id为9的数据到 (3,10]的区间。