Mysql数据存储结构

InnoDB采取的方式是：每个表的数据存在表名.ibd文件（表空间）中，将这些数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。当记录中的数据太多，当前页放不下的时候，会把多余的数据存储到其他页中，这种现象称为行溢出。

InnoDB为了不同的目的而设计了不同类型的页，我们把用于存放记录的页叫做数据页，下面是数据页结构

删除一条记录的过程

数据写入流程

Buffer Pool

在MySQL服务器启动的时候就向操作系统申请了一片连续的内存，他们给这片内存起了个名，叫做Buffer Pool 。

我们可以把所有空闲的缓存页对应的控制块作为一个节点放到一个链表中，这个链表也可以被称作free链表（或者说空闲链表）

怎么定位缓存在哪里呢？
我们可以用表空间号 + 页号作为key，缓存页作为value创建一个哈希表，在需要访问某个页的数据时，先从哈希表中根据表空间号 + 页号看看有没有对应的缓存页，如果有，直接使用该缓存页就好，如果没有，那就从free链表中选一个空闲的缓存页，然后把磁盘中对应的页加载到该缓存页的位置。

如果我们修改了Buffer Pool中某个缓存页的数据，那它就和磁盘上的页不一致了，这样的缓存页也被称为脏页 。我们不得不再创建一个存储脏页的链表，凡是修改过的缓存页对应的控制块都会作为一个节点加入到一个链表中，因为这个链表节点对应的缓存页都是需要被刷新到磁盘上的，所以也叫flush链表 。所以每次修改缓存页后，我们并不着急立即把修改同步到磁盘上，而是在未来的某个时间点进行同步。

Buffer Pool对应的内存大小毕竟是有限的，如果需要缓存的页占用的内存大小超过了Buffer Pool大小，也就是free链表中已经没有多余的空闲缓存页的时候岂不是很尴尬，发生了这样的事儿该咋办？

我们可以再创建一个链表，由于这个链表是为了按照最近最少使用的原则去淘汰缓存页的，所以这个链表可以被称为LRU链表（LRU的英文全称：Least Recently Used）。当然是把某些旧的缓存页从Buffer Pool中移除，然后再把新的页放进来喽～

什么时候刷盘呢？

后台有专门的线程每隔一段时间负责把脏页刷新到磁盘，这样可以不影响用户线程处理正常的请求。主要有两种刷新路径：

从LRU链表的冷数据中刷新一部分页面到磁盘。

后台线程会定时从LRU链表尾部开始扫描一些页面，扫描的页面数量可以通过系统变量innodb_lru_scan_depth来指定，如果从里边儿发现脏页，会把它们刷新到磁盘。这种刷新页面的方式被称之为BUF_FLUSH_LRU。
从flush链表中刷新一部分页面到磁盘。

后台线程也会定时从flush链表中刷新一部分页面到磁盘，刷新的速率取决于当时系统是不是很繁忙。这种刷新页面的方式被称之为BUF_FLUSH_LIST。
有时候后台线程刷新脏页的进度比较慢，导致用户线程在准备加载一个磁盘页到Buffer Pool时没有可用的缓存页，这时就会尝试看看LRU链表尾部有没有可以直接释放掉的未修改页面，如果没有的话会不得不将LRU链表尾部的一个脏页同步刷新到磁盘（和磁盘交互是很慢的，这会降低处理用户请求的速度）。这种刷新单个页面到磁盘中的刷新方式被称之为BUF_FLUSH_SINGLE_PAGE。

怎么确定一个页完整的刷到磁盘中了？万一中间断电了呢？

为保证从内存中同步到磁盘的页的完整性，在页的首部和尾部都会存储页中数据的校验和和页面最后修改时对应的LSN值，如果首部和尾部的校验和和LSN值校验不成功的话，就说明同步过程出现了问题。

删除的数据是直接移除吗？

这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉

索引

每个索引都对应一棵B+树，B+树分为好多层，最下边一层是叶子节点，其余的是内节点。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。
InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。
我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表（每个数据页的File Header部分都有上一个和下一个页的编号）。而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表（每个记录的头信息中都有一个next_record属性）。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。
通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快。 页内查找记录的流程：

InnoDB会把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个槽，存放在Page Directory中，如图：

所以在一个页中根据主键查找记录是非常快的，分为两步：

通过二分法确定该记录所在的槽。

通过记录的next_record属性遍历该槽所在的组中的各个记录。

聚簇索引

索引和数据记录存放在一起的结构叫聚簇索引，比如主键，索引和数据分开存放的索引叫非聚簇索引，比如二级索引，索引值是主键。
B+树的叶子节点存储的是完整的用户记录。

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

联合索引

按照c2和c3列建立联合索引：

先把各个记录和页按照c2列进行排序。
在记录的c2列相同的情况下，采用c3列进行排序

索引下推
对索引中包含的字段先做判断，过滤掉不符合条件的记录，从而减少回表次数，这种优化手段就叫索引下推。

mysq> select * from tuser where name like '张 %' and age=10 and ismale=1;

InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。

根页面万年不动窝

我们前边介绍B+树索引的时候，为了大家理解上的方便，先把存储用户记录的叶子节点都画出来，然后接着画存储目录项记录的内节点，实际上B+树的形成过程是这样的：

每当为某个表创建一个B+树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

这个过程需要大家特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

索引的使用注意事项

B+树索引在空间和时间上都有代价，所以没事儿别瞎建索引。
B+树索引适用于下边这些情况：
- 全值匹配
- 匹配左边的列
- 匹配范围值
- 精确匹配某一列并范围匹配另外一列
- 用于排序
- 用于分组
在使用索引时需要注意下边这些事项：
- 只为用于搜索、排序或分组的列创建索引
- 为列的基数大的列创建索引
- 索引列的类型尽量小
- 可以只对字符串值的前缀建立索引
- 只有索引列在比较表达式中单独出现才可以适用索引
- 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
- 定位并删除表中的重复和冗余索引
- 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。
索引失效的场景：
- 模糊搜索左侧带%
- ASC、DESC混用
- 排序列包含非同一个索引的列
- 排序列使用了复杂的表达式
- 分组的列顺序和联合索引的列顺序不一致

优化器

explain之访问方法

MySQL执行查询语句的方式称之为访问方法或者访问类型

const
通过主键或者唯一二级索引列与常数的等值比较来定位一条记录
ref
搜索条件为普通二级索引列与常数等值比较，采用二级索引来执行查询的访问方法
不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含NULL值的数量并不限制，所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法。
ref_or_null
不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来
range
利用索引进行范围匹配的访问方法称之为：range
index
采用遍历二级索引记录的执行方式称之为：index
看下边这个查询：

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

由于key_part2并不是联合索引idx_key_part最左索引列，所以我们无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下边这两个条件：它的查询列表只有3个列：key_part1, key_part2, key_part3，而索引idx_key_part又包含这三个列。且搜索条件中只有key_part2列。这个列也包含在索引idx_key_part中。

all
使用全表扫描执行查询的方式称之为：all

explain 字段解析

我们把EXPLAIN语句输出的各个列的作用先大致罗列一下：

列名	描述
`id`	在一个大的查询语句中每个`SELECT`关键字都对应一个唯一的`id`
`select_type`	`SELECT`关键字对应的那个查询的类型
`table`	表名
`partitions`	匹配的分区信息
`type`	针对单表的访问方法
`possible_keys`	可能用到的索引
`key`	实际上使用的索引
`key_len`	实际使用到的索引长度
`ref`	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
`rows`	预估的需要读取的记录条数
`filtered`	某个表经过搜索条件过滤后剩余记录条数的百分比
`Extra`	一些额外的信息,Using index索引覆盖

需要注意的是，大

连接查询的原理

嵌套循环连接（Nested-Loop Join）

步骤1：选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
步骤2：对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

基于块的嵌套循环连接（Block Nested-Loop Join）

所以设计MySQL的大叔提出了一个join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。使用join buffer的过程如下图所示：

基于成本的优化

I/O成本

我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。

CPU成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询，这个过程总结一下就是这样：

根据搜索条件，找出所有可能使用的索引
计算全表扫描的代价
计算使用不同索引执行查询的代价
对比各种执行方案的代价，找出成本最低的那一个

计算全表扫描的代价需要两个信息：

聚簇索引占用的页面数
该表中的记录数

这两个信息从哪来呢？设计MySQL的大叔为每个表维护了一系列的统计信息，关于这些统计信息是如何收集起来的我们放在本章后边详细唠叨，现在看看怎么查看这些统计信息哈。设计MySQL的大叔给我们提供了SHOW TABLE STATUS语句来查看表的统计信息

SHOW TABLE STATUS LIKE 'TABLE_83'

优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算idx_key2在(10, 1000)这个范围区间中包含多少二级索引记录，计算过程是这样的：

步骤1：先根据key2 > 10这个条件访问一下idx_key2对应的B+树索引，找到满足key2 > 10这个条件的第一条记录，我们把这条记录称之为区间最左记录。我们前头说过在B+数树中定位一条记录的过程是贼快的，是常数级别的，所以这个过程的性能消耗是可以忽略不计的。
步骤2：然后再根据key2 < 1000这个条件继续从idx_key2对应的B+树索引中找出最后一条满足这个条件的记录，我们把这条记录称之为区间最右记录，这个过程的性能消耗也可以忽略不计的。
步骤3：如果区间最左记录和区间最右记录相隔不太远（在MySQL 5.7.21这个版本里，只要相隔不大于10个页面即可），那就可以精确统计出满足key2 > 10 AND key2 < 1000条件的二级索引记录条数。否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。
回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立

n_rows统计项的收集

为啥老强调n_rows这个统计项的值是估计值呢？现在就来揭晓答案。InnoDB统计一个表中有多少行记录的套路是这样的：

按照一定算法（并不是纯粹随机的）选取几个叶子节点页面，计算每个页面中主键值记录数量，然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的n_rows值。

事务

事务的概念

我们把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称之为一个事务（英文名是：transaction 直译交易更加易懂）。
归根结底是为了解决事务的并发带来的一系列问题：脏写、脏读、不可重复读、幻读。主要解决的并发问题：读-写，写-读，写-写 每种引擎对事务的实现手段不一样，innodb采用MVCC或者锁实现

事务的状态

事务的持久性实现-redo log

如果我们只在内存的Buffer Pool中修改了页面，假设在事务提交后突然发生了某个故障，导致内存中的数据都失效了，那么这个已经提交了的事务对数据库中所做的更改也就跟着丢失了，这是我们所不能忍受的。

那事务提交时将所有修改过的内存中的页面全部刷盘？

性能太差。与在事务提交时将所有修改过的内存中的页面刷新到磁盘中相比，只将该事务执行过程中产生的redo日志刷新到磁盘的好处如下：

redo日志占用的空间非常小

存储表空间ID、页号、偏移量以及需要更新的值所需的存储空间是很小的，关于redo日志的格式我们稍后会详细唠叨，现在只要知道一条redo日志占用的空间不是很大就好了。
redo日志是顺序写入磁盘的

在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序IO。

mtr

设计InnoDB的大叔们认为向某个索引对应的B+树中插入一条记录的这个过程必须是原子的，不能说插了一半之后就停止了。所以他们规定在执行这些需要保证原子性的操作时必须以组的形式来记录的redo日志，在进行系统崩溃重启恢复时，针对某个组中的redo日志，要么把全部的日志都恢复掉，要么一条也不恢复。

log buffer

写入redo日志时也不能直接直接写到磁盘上，实际上在服务器启动时就向操作系统申请了一大片称之为redo log buffer的连续内存空间，翻译成中文就是redo日志缓冲区，我们也可以简称为log buffer

可是这些日志总在内存里呆着也不是个办法，在一些情况下它们会被刷新到磁盘里，比如：

log buffer空间不足时

log buffer的大小是有限的（通过系统变量innodb_log_buffer_size指定），如果不停的往这个有限大小的log buffer里塞入日志，很快它就会被填满。设计InnoDB的大叔认为如果当前写入log buffer的redo日志量已经占满了log buffer总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
事务提交时

我们前边说过之所以使用redo日志主要是因为它占用的空间少，还是顺序写，在事务提交时可以不把修改过的Buffer Pool页面刷新到磁盘，但是为了保证持久性，必须要把修改这些页面对应的redo日志刷新到磁盘。
后台线程不停的刷刷刷

后台有一个线程，大约每秒都会刷新一次log buffer中的redo日志到磁盘。
正常关闭服务器时
做所谓的checkpoint时（我们现在没介绍过checkpoint的概念，稍后会仔细唠叨，稍安勿躁）
其他的一些情况...

redo日志是首先写到log buffer中，之后才会被刷新到磁盘上的redo日志文件。所以设计InnoDB的大叔提出了一个称之为buf_next_to_write的全局变量，标记当前log buffer中已经有哪些日志被刷新到磁盘中了

check point

redo日志只是为了系统崩溃后恢复脏页用的，如果对应的脏页已经刷新到了磁盘，也就是说即使现在系统崩溃，那么在重启后也用不着使用redo日志恢复该页面了，所以该redo日志也就没有存在的必要了，那么它占用的磁盘空间就可以被后续的redo日志所重用。也就是说：判断某些redo日志占用的磁盘空间是否可以覆盖的依据就是它对应的脏页是否已经刷新到磁盘里
设计InnoDB的大叔提出了一个全局变量checkpoint_lsn来代表当前系统中可以被覆盖的redo日志总量是多少
比方说现在页a被刷新到了磁盘，mtr_1生成的redo日志就可以被覆盖了，所以我们可以进行一个增加checkpoint_lsn的操作，我们把这个过程称之为做一次checkpoint。做一次checkpoint其实可以分为两个步骤：
步骤一：计算一下当前系统中可以被覆盖的redo日志对应的lsn值最大是多少。

步骤二：将checkpoint_lsn和对应的redo日志文件组偏移量以及此次checkpint的编号写到日志文件的管理信息（就是checkpoint1或者checkpoint2）中。

怎么崩溃恢复的？

确定恢复的起点

我们前边说过，checkpoint_lsn之前的redo日志都可以被覆盖，也就是说这些redo日志对应的脏页都已经被刷新到磁盘中了，既然它们已经被刷盘，我们就没必要恢复它们了。对于checkpoint_lsn之后的redo日志，它们对应的脏页可能没被刷盘，也可能被刷盘了，我们不能确定，所以需要从checkpoint_lsn开始读取redo日志来恢复页面。

确定恢复的终点

redo日志恢复的起点确定了，那终点是哪个呢？这个还得从block的结构说起。我们说在写redo日志的时候都是顺序写的，写满了一个block之后会再往下一个block中写：普通block的log block header部分有一个称之为LOG_BLOCK_HDR_DATA_LEN的属性，该属性值记录了当前block里使用了多少字节的空间。对于被填满的block来说，该值永远为512。如果该属性的值不为512，那么就是它了，它就是此次崩溃恢复中需要扫描的最后一个block。

怎么恢复

使用哈希表

根据redo日志的space ID和page number属性计算出散列值，把space ID和page number相同的redo日志放到哈希表的同一个槽里，如果有多个space ID和page number都相同的redo日志，那么它们之间使用链表连接起来，按照生成的先后顺序链接起来的，如图所示：

之后就可以遍历哈希表，因为对同一个页面进行修改的redo日志都放在了一个槽里，所以可以一次性将一个页面修复好（避免了很多读取页面的随机IO），这样可以加快恢复速度。

跳过已经刷新到磁盘的页面
那在恢复时怎么知道某个redo日志对应的脏页是否在崩溃发生时已经刷新到磁盘了呢？
如果在做了某次checkpoint之后有脏页被刷新到磁盘中，那么该页对应的FIL_PAGE_LSN代表的lsn值肯定大于checkpoint_lsn的值，凡是符合这种情况的页面就不需要重复执行lsn值小于FIL_PAGE_LSN的redo日志了，所以更进一步提升了崩溃恢复的速度。

undo log

事务执行过程中可能已经修改了很多东西，为了保证事务的原子性，我们需要把东西改回原先的样子，这个过程就称之为回滚（英文名：rollback），这样就可以造成一个假象：这个事务看起来什么都没做，所以符合原子性要求
事务id：

对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增、删、改操作时才会为这个事务分配一个事务id，否则的话是不分配事务id的。
对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增、删、改操作时才会为这个事务分配一个事务id，否则的话也是不分配事务id的。

隐藏列：
聚簇索引的记录除了会保存完整的用户数据以外，而且还会自动添加名为trx_id、roll_pointer的隐藏列，如果用户没有在表中定义主键以及UNIQUE键，还会自动添加一个名为row_id的隐藏列

MVCC原理

版本链

roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id，这个信息很重要

ReadView

对于使用READ UNCOMMITTED隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了；对于使用SERIALIZABLE隔离级别的事务来说，设计InnoDB的大叔规定使用加锁的方式来访问记录（加锁是啥我们后续文章中说哈）；对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录。
核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。为此，设计InnoDB的大叔提出了一个ReadView的概念，这个ReadView中主要包含4个比较重要的内容：

m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。

小贴士：注意max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。
creator_trx_id：表示生成该ReadView的事务的事务id。

小贴士：我们前边说过，只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0。

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。

READ COMMITTED —— 每次读取数据前都生成一个ReadView

REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

从上边的描述中我们可以看出来，所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SELECT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。
与SQL标准不同的一点就是，MySQL在REPEATABLE READ隔离级别实际上就已经解决了幻读问题。

锁

怎么解决脏读、不可重复读、幻读这些问题呢？其实有两种可选的解决方案：

方案一：读操作利用多版本并发控制（MVCC），写操作进行加锁。

所谓的MVCC我们在前一章有过详细的描述，就是通过生成一个ReadView，然后通过ReadView找到符合条件的记录版本（历史版本是由undo日志构建的），其实就像是在生成ReadView的那个时刻做了一次时间静止（就像用相机拍了一个快照），查询语句只能读到在生成ReadView之前已提交事务所做的更改，在生成ReadView之前未提交的事务或者之后才开启的事务所做的更改是看不到的。而写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读-写操作并不冲突。
MVCC并没有完全的解决幻读问题，具体可见这里

MySQL（innodb）的选择是允许在快照读之后执行当前读，并且更新 snapshot 镜像的版本。严格来说，这个结果违反了 repeatable read 隔离级别，，但是 who cares 呢，毕竟官方都说了：“This is not a bug but an intended and documented behavior. ”

具体案例：

假设有sessionA和sessionB都开启了事务，sessionB插入并提交一条数据，主键值为 n，在sessionA中select是看不到这条提交的数据的，但是执行update xxx where id=n却是可以执行成功的，这算幻读吧。

方案二：读、写操作都采用加锁的方式。

如果我们的一些业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本（已提交的最新版本，而不是第一次select生成的快照版本），比方在银行存款的事务中，你需要先把账户的余额读出来，然后将其加上本次存款的数额，最后再写到数据库中。在将账户余额读取出来后，就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才可以访问账户的余额。这样在读取记录的时候也就需要对其进行加锁操作，这样也就意味着读操作和写操作也像写-写操作那样排队执行。
很明显，采用MVCC方式的话，读-写操作彼此并不冲突，性能更高，采用加锁方式的话，读-写操作彼此需要排队执行，影响性能。一般情况下我们当然愿意采用MVCC来解决读-写操作并发执行的问题，但是业务在某些特殊情况下，要求必须采用加锁的方式执行，那也是没有办法的事。

我们说幻读问题的产生是因为当前事务读取了一个范围的记录，然后另外的事务向该范围内插入了新记录，当前事务再次读取该范围的记录时发现了新插入的新记录，我们把新插入的那些记录称之为幻影记录。采用加锁的方式解决幻读问题就有那么一丢丢麻烦了，因为当前事务在第一次读取记录时那些幻影记录并不存在，所以读取的时候加锁就有点尴尬 —— 因为你并不知道给谁加锁，没关系，这难不倒设计InnoDB的大叔的，我们稍后揭晓答案，稍安勿躁。

结论：

在快照读情况下，MySQL通过mvcc来避免幻读。
在当前读情况下，MySQL通过next-key来避免幻读

锁的分类

共享锁和独占锁

设计MySQL的大叔给锁分了个类：

共享锁，英文名：Shared Locks，简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁。
独占锁，也常称排他锁，英文名：Exclusive Locks，简称X锁。在事务要改动一条记录时，需要先获取该记录的X锁。
对读取的记录加S锁：

SELECT ... LOCK IN SHARE MODE;

对读取的记录加X锁：

SELECT ... FOR UPDATE;

我们前边提到的锁都是针对记录的，也可以被称之为行级锁或者行锁，对一条记录加锁影响的也只是这条记录而已，给表加的锁也可以分为共享锁（S锁）和独占锁（X锁）：
我们在对教学楼整体上锁（表锁）时，怎么知道教学楼中有没有教室已经被上锁（行锁）了呢？依次检查每一间教室门口有没有上锁？那这效率也太慢了吧！遍历是不可能遍历的，这辈子也不可能遍历的，于是乎设计InnoDB的大叔们提出了一种称之为意向锁（英文名：Intention Locks）的东东：

意向共享锁，英文名：Intention Shared Lock，简称IS锁。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
意向独占锁，英文名：Intention Exclusive Lock，简称IX锁。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

InnoDB中的表级锁

表级别的S锁、X锁

在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的。

另外，在对某个表执行一些诸如ALTER TABLE、DROP TABLE这类的DDL语句时，其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞，同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行DDL语句也会发生阻塞。这个过程其实是通过在server层使用一种称之为元数据锁（英文名：Metadata Locks，简称MDL）东东来实现的，一般情况下也不会使用InnoDB存储引擎自己提供的表级别的S锁和X锁。
系统实现这种自动给AUTO_INCREMENT修饰的列递增赋值的原理主要是两个：
采用AUTO-INC锁，也就是在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。这样一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。

小贴士：需要注意一下的是，这个AUTO-INC锁的作用范围只是单个插入语句，插入语句执行完成后，这个锁就被释放了，跟我们之前介绍的锁在事务结束时释放是不一样的。

如果我们的插入语句在执行前就可以确定具体要插入多少条记录，比方说我们上边举的关于表t的例子中，在语句执行前就可以确定要插入2条记录，那么一般采用轻量级锁的方式对AUTO_INCREMENT修饰的列进行赋值。这种方式可以避免锁定表，可以提升插入性能。

InnoDB中的行级锁

下边我们来看看都有哪些常用的行锁类型。

Record Locks：

我们前边提到的记录锁就是这种类型，也就是仅仅把一条记录锁上，是有S锁和X锁之分的，
Gap Locks：

如图中为number值为8的记录加了gap锁，意味着不允许别的事务在number值为8的记录前边的间隙插入新记录，其实就是number列的值(3, 8)这个区间的新记录是不允许立即插入的。比方说有另外一个事务再想插入一条number值为4的新记录，它定位到该条新记录的下一条记录的number值为8，而这条记录上又有一个gap锁，所以就会阻塞插入操作，直到拥有这个gap锁的事务提交了之后，number列的值在区间(3, 8)中的新记录才可以被插入。

这个gap锁的提出仅仅是为了防止插入幻影记录而提出的

RC和RUC隔离级别下是不会有gap锁和next-key锁的

Next-Key Locks：有时候我们既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录。next-key锁的本质就是一个正经记录锁和一个gap锁的合体，它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的间隙。

那什么时候用record锁？什么时候用next key锁呢？

一条语句需要加的锁受到很多条件制约，比方说：

事务的隔离级别
语句执行时使用的索引（比如聚簇索引、唯一二级索引、普通二级索引）
查询条件（比方说=、=<、>=等等）
具体执行的语句类型

比方说gap锁和next key锁设计出来是为了解决幻读问题，所以RR/SERIALIZABLE才会用到，RC不会用到

对于使用主键进行范围查询的情况

SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;

为number值大于8的所有聚簇索引记录都加一个S型next-key锁

对于使用主键或者唯一索引进行等值查询的情况

SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;

record锁，因为不会出现幻读，每次查询都是这一条记录

对于使用普通二级索引进行等值查询的情况

SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;

由于普通的二级索引没有唯一性，所以一个事务在执行上述语句之后，要阻止别的事务插入name值为'c曹操'的新记录，设计InnoDB的大叔采用下边的方式对上述语句进行加锁：
- 对所有name值为'c曹操'的二级索引记录加S型next-key锁，它们对应的聚簇索引记录加S型正经就锁。
- 对最后一个name值为'c曹操'的二级索引记录的下一条二级索引记录加gap锁。

全表扫描的情况

比方说：

SELECT * FROM hero WHERE country  = '魏' LOCK IN SHARE MODE;

由于country列上未建索引，所以只能采用全表扫描的方式来执行这条查询语句，存储引擎每读取一条聚簇索引记录，就会为这条记录加锁一个S型next-key锁，然后返回给server层，如果server层判断country = '魏'这个条件是否成立，如果成立则将其发送给客户端，否则会向InnoDB存储引擎发送释放掉该记录上的锁的消息（不过在REPEATABLE READ隔离级别下，InnoDB存储引擎并不会真正的释放掉锁，所以聚簇索引的全部记录都会被加锁，并且在事务提交前不释放。RC和RUC则会直接释放锁）
使用SELECT ... FOR UPDATE进行加锁的情况与上边类似，只不过加的是X型正经记录锁，就不赘述了。

对于UPDATE ...语句来说，加锁情况与SELECT ... FOR UPDATE类似，不过如果被更新的列中还有别的二级索引列的话，这些对应的二级索引记录也会被加X型正经记录锁。

和DELETE ...的语句来说，加锁情况与SELECT ... FOR UPDATE类似，不过如果表中还有别的二级索引列的话，这些对应的二级索引记录也会被加X型正经记录锁。

INSERT语句

前边唠叨锁的细节时说过，INSERT语句一般情况下不加锁，不过当前事务在插入一条记录前需要先定位到该记录在B+树中的位置，如果该位置的下一条记录已经被加了gap锁（next-key锁也包含gap锁，之后就不强调了），那么当前事务会在该记录上加上一种类型为插入意向锁的锁，并且事务进入等待状态。

详情点这里

insert加锁分析

日志

biglog 和redolog写入顺序

点这里查看详细

一条sql语句在mysql是如何执行的

点这里

binlog的三种模式对比

点这里

为什么mysql索引是B+树不是B树？

核心区别：

B树的非叶子节点也存数据，B+树只有叶子节点存

这会带来很多问题，一个节点就是一个数据页（16KB），只存目录项可能可以存1000条，但是存数据可能只能存500条，那根节点最多就只有500个子节点，这样整个树的深度都会增加，从而增加IO次数
假设B+树和B树深度一样，但是内存是没办法装进所有索引节点的，只能装进去一部分，如果是B+树，由于目录项占空间少就能全部装进内存，还有一部分叶子节点装进去，这样大部分请求都是在内存里就能解决，没有磁盘IO，而B+树可能非叶子节点都装不完，这样大部分查询都要进行磁盘IO

B+树同层页和页之间有双向链表 这样范围查询会方便很多，直接遍历就好了，B树要进行树的中序遍历

一个B+树的深度最主要由每个页可以放多少目录项记录决定，比如说每页只能存放1000条目录项，最多可以存放500条数据，那三层b+树最多可存放：1000*1000*500 = 5千万条记录

所以当数据越来越多时，第一可能会增加树的深度从而增加磁盘IO次数第二最重要的是会导致很多数据加载不进内存，从而产生磁盘IO而不是内存里就能完成搜索，性能会急剧下降，一般到500w条数据就要考虑分表，这个也要看机器的硬件条件主要是内存。当一个库的数据量过大，自然请求并发量也会变大很多，cpu和内存都会有压力，就要考虑分库了

mysql笔记