MySQL系列之存储引擎（二）

InnoDB

mysql默认的存储引擎（5.5之后）；应用于事务性，安全性操作多的情况。其表数据文件本身是按照B+树组织的一个索引结构文件

路径：mysql-5.6.33-win64/data/test

.frm文件：存储表的结构文件

.ibd文件：数据和索引是放在一起的

行格式

记录的额外信息

变长字段长度	NULL标志位	记录头信息	列1数据	列2数据	...

这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类，分别是：

• 变长字段长度；
• NULL值；
• 记录头信息。

变长字段长度列表

mysql支持一些变长的数据类型，比如varchar(M)，varbinary(M)，text，blob，这些数据类型修饰列称为变长字段，变长字段中存储多少字节数据不是固定的，所以在存储真实数据的时候要把这些数据占用的字节数也存起来，在compact行格式中，把所有变长字段实际占用的字节长度存放在记录的开头部位，从而变成一个变长字段长度列表。

char是一种固定长度的类型，varchar是一种可变长度的类型

varchar(M)中M代表能存多少个字符（mysql5.0.3以前是字节，之后是字符）

NULL值列表

Compact行格式会把可以为null的列统一管理起来，存一个标记位在null值的列表中，如果表中没有允许存储null的列，则null值列表也不存在了。

二进制位的值为1，表示该列的值可以为null

二进制位的值为0，表示该列的值不可以为null

记录头信息

它由固定的5个字节组成，也就是40个二进制位，不同的位代表不同的意思：

名称	大小(单位:bit)	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	当前记录拥有的记录数
heap_no	3	当前记录在记录堆的位置信息
record_type	3	当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
next_record	6	下一条记录的相对位置

记录的真实数据

记录的真实数据除了自定义的列数据以外，还有三个隐藏列：

列名	表中真实名称	是否必须	占用空间	描述
row_id	DB_ROW_ID	否	6字节	行ID，唯一标识一条记录
transaction_id	DB_TRX_ID	是	6字节	事务ID
roll_pointer	DB_ROLL_RTR	是	7字节	回滚指针

一个表没有手动定义主键，则会选取一个unique键作为主键，如果连unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键，所以row_id是在没有自定义主键以及unique键的情况下才存在的。

常见问题：

1.为什么推荐innodb必须建主键，并且推荐使用整型的自增主键？

推荐innodb必须建主键：innodb的表如果不建主键的话，他会在表中找一列所有数据都不重复的列来去构建整个b+树，如果表中找没有一列数据时可以作为索引的（列数据不重复），那么mysql会自己维护一个隐藏列来维护B+树的结构，来组织整张表的所有数据

推荐使用整形的自增主键：因为底层是B+树，如果使用自增的整型作为主键的话，在折半查找比大小阶段会快很多；如果不是自增的话，因为底层叶子节点是递增的，如果插入的时候不是自增的，那会插入到之前已经存在的叶子节点的中间，这样也有可能会造成之前的叶子节点的分裂，分裂之后还会进行树的平衡，这样的效率不如自增高。

2.为什么非主键索引结构叶子节点存储的是主键值？

因为一致性和节省存储空间

数据页结构

页是InnoDB对磁盘进行io操作时最小的数据存储单位（磁盘和内存交互的基本单位）。索引B+Tree的每一个节点其实都是一页，一个页的大小默认为 16KB。

页目录

页目录存放的是一个指针和一个主键，一个指针占6b，一个int的主键4b，通常一个页目录占10个字节；

页目录中的值从小到大排列，如果要找其中一个值的话，从上到下会耗费时间，底层用的是二分折半查找，这样可以更快的找到对象的区间

有页目录的好处：

页目录存放的是主键，如果要找3，则先从页目录开始找，因为4>3，所以要找到页目录为1的数，遍历页目录为1的数，如果页目录为1的数中找不到，则就返回没有。

BufferPool(innodb缓冲池)

Innodb 基于磁盘存储，同时按页的方式来管理记录。如果每次查询或修改都要按页和磁盘进行 IO 交互会严重影响数据库的性能，因此引入了内存缓存；

有了内存缓存，在对数据进行查询时，先查内存缓存，如果数据存在直接返回，如果不存在则去磁盘读取并将读取到的页放到缓存池中，然后再返回数据；对数据的修改也是先修改缓存池的页，而后异步的将页刷新回磁盘。

bufferpool类似于一个数组，大小是128M，如果进行select语句查询时，会将磁盘中的16KB的页数据复制到bufferpool中，这样的话128M可以存放页的多少是固定的，就是128M/16kb=8192个页数据

show  GLOBAL VARIABLES like'%innodb_buffer_pool_size%'；——134217728b——128M

如果bufferpool满了应该进行淘汰，如果淘汰的是bufferpool中用的最少的一页，可能会造成bufferpool区域的数据是不连续的，这样新的bufferpool进来应该放在什么地方？

free链表

会用到free链表，free链表是管理空闲区域的，每个空着的页都会有一个控制块，其节点会记录除了自己有多少个控制块，就相当于有多少块空闲区域，也会记录当前链表的头节点，当前链表的尾节点

select查询时从磁盘中取出页数据，会去控制块找到在bufferpool中相应的位置，将页数据进去，之后再将这个控制块从free链表中删除，如果bufferpool再有别的页空闲出来，也会将这个页的位置添加到free链表中

flush链表

update操作：当要update的数据在bufferpool中时，会直接修改bufferpool中的数据，而不会修改磁盘中的数据，再将磁盘中的数据放到bufferpool，这样会比直接修改慢；但是被修改的这页bufferpool数据就会变成脏页，如何识别这些脏页的数据才能将其在磁盘中修改呢？

这就用到了flush链表，flush链表的控制块中存放的就是脏页的位置，程序定期去flush链表中，一个一个的将脏页持久化到磁盘中去

lru链表

上面提到的，bufferpool如果被放满之后，会按照最近最少原则去淘汰页数据，用到的就是lru链表

如果刚开始bufferpool是空的，从磁盘中取一个页数据放到bufferpool中时，lru链表也会新增一个控制块，这时又有一个页数据进来，放到bufferpool中，就会放到之前那个控制块的前面，随着bufferpool不断的使用，最后页数据的控制块就会是lru链表的头节点，如果在往bufferpool加的过程中，又有select语句查到了之前已经加到bufferpool的页数据，说明这个页数据又被使用了一次，这时这个页数据的位置会被放到lru链表的头结点

所以lru链表保证了，越是前面的控制块越是最近最多使用的，链表靠后的就是用的比较少的，如果bufferpool满了，那就去淘汰lru链表最尾部的页数据

lru链表升级版

上面的简单的lru链表有一个问题，那就是，如果现在新来一个select语句，要查询的数据量非常大，将bufferpool的空着的都填满都不够，这时只能删除使用比较频繁的热点数据

在mysql中，lru链表有个规定，就是八分之五，这八分之五用来放热数据，后面的八分之三是冷数据区域

这样的话逻辑就会发生改变，新进来的数据会到冷数据区域，如果冷数据区域已经满的话，就从冷数据区域最后一个开始淘汰，如果冷数据区域的数据被重复使用了，那就将其移动到，冷数据区域的第一个

冷数据区域放到热数据区域要满足的条件：

冷数据区域的数据第一次访问和第二次访问的差值大于1s，就会将冷数据区域的数据，转移到热数据区域的头部

为什么是大于1s呢？

因为如果是进行全表扫描的话，一页里面的数据就都会被访问到，那么第一行到第二行的访问时间一定会比1s小，如果设置成大于1s的话，就能避免掉全表扫描这种情况，也是真正的热数据。

bufferpool脏页，不同事务如何隔离

MVCC机制（下一节）

redolog

区别于 binlog 日志，redolog属于innodb下面的，redolog 是 Innodb 独有的日志模块，它只记录有关 Innodb 引擎的事务日志，记录内容为对数据页的物理操作（比如偏移量 500，写 'abcd'）。redolog对象存在磁盘区域，记录事务对数据库做了哪些修改。

应用场景：奔溃恢复：确定恢复的起点；确定恢复的终点；咋样恢复。

之前提到的对数据的修改是先修改缓存池的页，而后异步的将页刷新回磁盘，但是异步刷新磁盘也带来一个新问题：在刷新磁盘前如果意外宕机，重启后内存数据已经没有了，就会导致数据丢失。

为了避免数据丢失，Innodb 采用了 Write Ahead Log(WAL) 策略，即当事务提交时，先写日志，再修改页，当发生意外宕机时，可以通过日志来恢复数据，这个日志就叫做 redo log，它保证了事务的持久性。

show global VARIABLES like "%datadir%";——D:\mysql-8.0.17-winx64\Data\
--根据查出来的数据去操作系统中找对应的位置

redolog下面有两个ib_logfile0和ib_logfile1，这两个空间是连续的，专门用来存储redolog对象的，大小在48M左右，这是mysql生成的固定大小，已经在磁盘中开辟好的大小，可以修改；

如果将logfile调大，那么如果mysql挂掉的话，重启后，要将logfile中的数据恢复还原，这个过程耗费的时间会更多。

两个空间如果0满了就去1，如果0和1都满了，就会重新在0中写，覆盖掉之前写入的数据。如果0和1都满了的话，就会触发xxxpoint，一旦触发，就会对mysql性能有所影响，因为要将0和1中没有持久化到磁盘的数据都持久化进去

show global variables like"%innodb_log_file_size%";——48M左右

为什么页里面的数据要生成一个redolog对象再去持久化，而不是直接持久化这个页？

涉及到了顺序IO和随机IO，因为一页的数据有很多条，如果只改了一条，却要update整个页是不合适的；还有如果一页中改了多条，但是这些在磁盘中的位置不一定是连续的（随机IO），如果要找的话会耗费比较长的时间；

都不如生成一个redolog对象，将这个对象去持久化，而且如果在执行到一半的时候，mysql挂掉的话，也可以用redolog对象去还原数据

undolog

一个数a，存的是1，将其update成2，undolog中记录的是1，它记录的是修改之前的数据

insert undo：事务提交即释放

update undo：需要支持MVCC，不能立即删除

delete mark：记录打删除标记（逻辑删除）

应用场景：事务回滚；子主题

logbuffer

logbuffer在内存区域，logbuffer中存放的是：因为begin开始到commit结束中间可以有很多步的操作，每执行一次操作都会将这个放到logbuffer中，只有当commit之后，这些logbuffer中的对象才会被存放到logfile中

doublewritebuffer

innodb的脏页要写到磁盘中去，要经历4次，因为一页是16KB，但是操作系统往磁盘写一次是4KB，说明要写一页需要4次才可以，如果写了两次之后，操作系统挂了之后，数据就丢失了，这样怎么处理？

为了解决这一问题，又新加了一个doublewritebuffer，将这16kb的数据先写到doublewritebuffer，但是这个过程也可能出现部分成功部分不成功的情况：

假设16KB的8KB写入成功，mysql挂掉了，这时可以取老数据结合redolog对象中的记录，生成新数据，如果16KB都写成功了，就可以将redolog中的数据删掉，这时就算doublewritebuffer写入表空间的时候，挂掉了，也不会出现数据丢失的问题，因为已经持久化到doublewritebuffer了

如果磁盘上有原子性，这16KB要么全部成功，要么全部失败，这样的话，可以关闭doublewritebuffer，再节约一些时间

changebuffer

进行update时，不仅要修改相应的数据，还要修改相应的索引

因此，在update的时候，如果不用changebuffer的话，就会将对应数据的页数据和索引页都放到bufferpool，每放一次都是一次磁盘IO，操作一些update会涉及到很多的磁盘IO，优化的方法就是changebuffer

如果要update数据和索引的话，changebuffer中就会存放这条update修改语句，此时就只往bufferpool中放要修改的页数据，当再进来select语句，刚好要用到这个索引的时候，就将这个索引页和changebuffer中的update语句相结合，进行修改，生成一个新的索引页，放入到bufferpool中，之后再写到磁盘中去

如果写操作比较多，读操作比较少的话，可以适量的将changebuffer调大

innodb基础优化参数

default-storage-engine
innodb_buffer_pool_size # 没有固定大小，50%测试值，看情况微调。但是尽量设置不超过物理内存70%
innodb_file_per_table=(1,0)
innodb_flush_log_at_trx_commit=(0,1,2) # 1是最安全的，0是性能最高，2折中
binlog_sync
Innodb_flush_method=(O_DIRECT, fdatasync)
innodb_log_buffer_size # 100M以下
innodb_log_file_size   # 100M以下
innodb_log_files_in_group    # 5个成员以下,一般2-3个够用（iblogfile0-N）
innodb_max_dirty_pages_pct   # 达到百分之75的时候刷写 内存脏页到磁盘。
log_bin
max_binlog_cache_size # 可以不设置
max_binlog_size       # 可以不设置
innodb_additional_mem_pool_size  #小于2G内存的机器，推荐值是20M。32G内存以上100M

Memory

数据存储在内存，表结构存储在磁盘，访问效率高；服务关闭，表中的数据丢失；

应用场景：mysql内存表做数据缓存。

MyISAM

不支持对事务的支持；不支持行级锁，不支持外键。

是用来修饰数据库表的，索引文件和数据文件是分离的

路径：mysql-5.6.33-win64/data/test

.frm文件：存储表的结构文件

.MYD文件：MyISAM存储引擎的数据文件

.MYI文件：MyISAM存储引擎的索引文件

查找过程：select查询时，判断其是否有走索引，如果走了索引，会先查找MYI，内部折半查找，找到叶子节点，定位到其所在的位置，叶子结点找到的是数据所在行的磁盘文件的地址，再根据这个地址，找到MYD文件对应的地址的数据

MyISAM和InnoDB的区别

MyISAM：

• 不支持事务，但是每次查询都是原子的；
• 支持表级锁，即每次操作是对整个表加锁；
• 存储表的总行数；
• MYISAM表有三个文件: 索引文件、表结构文件、数据文件；采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

InnoDB：

• 支持ACID的事务，支持事务的四种隔离级别；
• 支持行级锁及外键约束，因此可以支持写并发不存储总行数；
• innoDb引擎存储在一个文件空间(共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里)，也有可能为多个(设置为独立表空，表大小受操作系统文件大小限制，一般为2G)，受操作系统文件大小的限制；
• 主键索引采用聚集索引(索引的数据域存储数据文件本身) ，辅助索引的数据域存储主键的值；因此辅助索引查找数据，要先通过辅助索引找到主键值，再访问主键索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。