<返回更多

为什么别人能用好 MySQL？万字详解其复杂原理

2020-04-17    
加入收藏

来源：简书-七把刀

https://www.jianshu.com/p/d4cc0ea9d097

MySQL InnoDB 引擎现在广为使用，它提供了事务，行锁，日志等一系列特性，本文分析下 InnoDB 的内部实现机制，MySQL 版本为 5.7.24，操作系统为 Debian 9。

MySQL InnoDB 的实现非常复杂，本文只是总结了一些皮毛，希望以后能够研究的更加深入些。

1、InnoDB 架构

为什么别人能用好 MySQL？万字详解其复杂原理

 

Innodb 架构图

InnoDB 的架构分为两块：内存中的结构和磁盘上的结构。InnoDB 使用日志先行策略，将数据修改先在内存中完成，并且将事务记录成重做日志(Redo Log)，转换为顺序IO高效的提交事务。

这里日志先行，说的是日志记录到数据库以后，对应的事务就可以返回给用户，表示事务完成。但是实际上，这个数据可能还只在内存中修改完，并没有刷到磁盘上去。内存是易失的，如果在数据落地前，机器挂了，那么这部分数据就丢失了。

InnoDB 通过 redo 日志来保证数据的一致性。如果保存所有的重做日志，显然可以在系统崩溃时根据日志重建数据。

当然记录所有的重做日志不太现实，所以 InnoDB 引入了检查点机制。即定期检查，保证检查点之前的日志都已经写到磁盘，则下次恢复只需要从检查点开始。

2、InnoDB 内存中的结构

内存中的结构主要包括 Buffer Pool，Change Buffer、Adaptive Hash Index以及 Log Buffer 四部分。

如果从内存上来看，Change Buffer 和 Adaptive Hash Index 占用的内存都属于 Buffer Pool，Log Buffer占用的内存与 Buffer Pool独立。

Buffer Pool

缓冲池缓存的数据包括Page Cache、Change Buffer、Data Dictionary Cache等，通常 MySQL 服务器的 80% 的物理内存会分配给 Buffer Pool。

基于效率考虑，InnoDB中数据管理的最小单位为页，默认每页大小为16KB，每页包含若干行数据。

为了提高缓存管理效率，InnoDB的缓存池通过一个页链表实现，很少访问的页会通过缓存池的 LRU 算法淘汰出去。

InnoDB 的缓冲池页链表分为两部分：New sublist(默认占5/8缓存池) 和 Old sublist(默认占3/8缓存池，可以通过 innodb_old_blocks_pct修改，默认值为 37)，其中新读取的页会加入到 Old sublist的头部，而 Old sublist中的页如果被访问，则会移到 New sublist的头部。

缓冲池的使用情况可以通过 show engine innodb status 命令查看。其中一些主要信息如下：

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992 # 分配给InnoDB缓存池的内存(字节)
Dictionary memory allocated 102398  # 分配给InnoDB数据字典的内存(字节)
Buffer pool size   8191 # 缓存池的页数目
Free buffers       7893 # 缓存池空闲链表的页数目
Database pages     298  # 缓存池LRU链表的页数目
Modified db pages  0    # 修改过的页数目
......

Change Buffer

通常来说，InnoDB辅助索引不同于聚集索引的顺序插入，如果每次修改二级索引都直接写入磁盘，则会有大量频繁的随机IO。Change buffer 的主要目的是将对 非唯一 辅助索引页的操作缓存下来，以此减少辅助索引的随机IO，并达到操作合并的效果。它会占用部分Buffer Pool 的内存空间。

在 MySQL5.5 之前 Change Buffer其实叫 Insert Buffer，最初只支持 insert 操作的缓存，随着支持操作类型的增加，改名为 Change Buffer。

如果辅助索引页已经在缓冲区了，则直接修改即可；如果不在，则先将修改保存到 Change Buffer。Change Buffer的数据在对应辅助索引页读取到缓冲区时合并到真正的辅助索引页中。Change Buffer 内部实现也是使用的 B+ 树。

可以通过 innodb_change_buffering 配置是否缓存辅助索引页的修改，默认为 all，即缓存 insert/delete-mark/purge 操作(注：MySQL 删除数据通常分为两步，第一步是delete-mark，即只标记，而purge才是真正的删除数据)。

为什么别人能用好 MySQL？万字详解其复杂原理

 

查看Change Buffer 信息也可以通过 show engine innodb status 命令。更多信息见

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)

Adaptive Hash Index

自适应哈希索引(AHI)查询非常快，一般时间复杂度为 O(1)，相比 B+ 树通常要查询 3~4次，效率会有很大提升。innodb 通过观察索引页上的查询次数，如果发现建立哈希索引可以提升查询效率，则会自动建立哈希索引，称之为自适应哈希索引，不需要人工干预，可以通过 innodb_adaptive_hash_index 开启，MySQL5.7 默认开启。

考虑到不同系统的差异，有些系统开启自适应哈希索引可能会导致性能提升不明显，而且为监控索引页查询次数增加了多余的性能损耗， MySQL5.7 更改了 AHI 实现机制，每个 AHI 都分配了专门分区，通过 innodb_adaptive_hash_index_parts配置分区数目，默认是8个，如前一节命令列出所示。

Log Buffer

Log Buffer是 重做日志在内存中的缓冲区，大小由 innodb_log_buffer_size 定义，默认是 16M。一个大的 Log Buffer可以让大事务在提交前不必将日志中途刷到磁盘，可以提高效率。如果你的系统有很多修改很多行记录的大事务，可以增大该值。

配置项 innodb_flush_log_at_trx_commit 用于控制 Log Buffer 如何写入和刷到磁盘。注意，除了 MySQL 的缓冲区，操作系统本身也有内核缓冲区。

配置不同的值效果如下图所示：

为什么别人能用好 MySQL？万字详解其复杂原理

 

innodb_flush_log_at_timeout 可以配置刷新日志缓存到磁盘的频率，默认是1秒。注意刷磁盘的频率并不保证就正好是这个时间，可能因为MySQL的一些操作导致推迟或提前。

而这个 “flush” 方法并不是C标准库的 fflush 方法(fflush是将C标准库的缓冲写到内核缓冲区，并不保证刷到磁盘)，它通过 innodb_flush_method 配置的，默认是 fsync，即日志和数据都通过 fsync 系统调用刷到磁盘。

可以发现，InnoDB 基本每秒都会将 Log buffer落盘。而InnoDB中使用的 redo log 和 undo log，它们是分开存储的。

redo log在内存中有log buffer，在磁盘对应ib_logfile文件。而undo log是记录在表空间ibd文件中的，InnoDB为undo log会生成undo页，对undo log本身的操作（比如向undo log插入一条记录），也会记录redo log，因此undo log并不需要马上落盘。而 redo log 则通常会分配一块连续的磁盘空间，然后先写到log buffer，并每秒刷一次磁盘。

redo log 必须在数据落盘前先落盘(Write Ahead Log)，从而保证数据持久性和一致性。而数据本身的修改可以先驻留在内存缓冲池中，再根据特定的策略定期刷到磁盘。

3、InnoDB 磁盘上的结构

为了后面测试方便，我们先建立一个测试数据库 test，然后建立一个测试表 t。

mysql> create database test;
mysql> use test;
mysql> create table t (id int auto_increment primary key, ch varchar(5000));
mysql> insert into t (ch) values('abc');
mysql> insert into t (ch) values('defgh');

建立完成后，可以在 MySQL 目录中看到 test 数据库目录，然后里面有 db.opt， t.frm 和 t.ibd 3个文件。其中 db.opt 保存了数据库test的默认字符集 utf8mb4 和校验方法 utf8mb4_general_ci，t.frm 是表的数据字典信息(InnoDB数据字典信息主要是存储在系统表空间ibdata1文件中，由于历史原因才在 t.frm 多保留了一份)，t.ibd是表的数据和索引。

3.1 InnoDB 表结构

InnoDB 与 MyISAM 不同，它在系统表空间存储数据字典信息，因此它的表不能像 MyISAM 那样直接拷贝数据表文件移动。MySQL5.7 采用的文件格式是 Barracuda，它支持 COMPACT 和 DYNAMIC 这两种新的行记录格式。创建表时可以通过 ROW_FORMAT 指定行记录格式，默认是 DYNAMIC。可以通过命令 SHOW TABLE STATUS 查看表信息，此外，也可使用 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t' 查看。

mysql> SHOW TABLE STATUS FROM test LIKE 't' G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 3
    Create_time: 2019-01-13 02:24:52
    Update_time: 2019-01-13 02:28:16
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

InnoDB表使用上有一些限制，如一个表最多只能有64个辅助索引，一行大小不能超过65535等，组合索引不能超过16个字段等，一般应该不会突破限制，详细见 innodb-restrictions。

3.2 InnoDB 表空间概述

表空间根据类型可以分为系统表空间，File-Per-Table 表空间，常规表空间，Undo表空间，临时表空间等。本节分析 File-Per-Table 表空间。

注意：必须删除常规表空间中的表后才能删除常规表空间。

CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
CREATE TABLE t2 (c2 INT PRIMARY KEY) TABLESPACE ts1;
ALTER TABLE t2 TABLESPACE=innodb_file_per_table;

DROP TABLE t1;
DROP TABLESPACE ts1;

表空间文件结构上分为段、区、页。

为什么别人能用好 MySQL？万字详解其复杂原理

 

3.3 InnoDB File-Per-Table表空间

采用 File-Per-Table 的优缺点如下：

表空间文件结构

InnoDB 表空间文件 .ibd 初始大小为 96K，而InnoDB默认页大小为 16K，页大小也可以通过 innodb_page_size 配置为 4K, 8K…64K 等。在ibd文件中，0-16KB偏移量即为0号数据页，16KB-32KB的为1号数据页，以此类推。页的头尾除了一些元信息外，还有Checksum校验值，这些校验值在写入磁盘前计算得到，当从磁盘中读取时，重新计算校验值并与数据页中存储的对比，如果发现不同，则会导致 MySQL 崩溃。

ibd文件存储结构如下所示：

为什么别人能用好 MySQL？万字详解其复杂原理

 

ibd文件存储结构

InnoDB页分为INDEX页、Undo页、系统页，IBUF_BITMAP页, INODE页等多种。

可以在 innodb_sys_tables 表中查到表t的表空间ID为28，然后可以在 innodb_buffer_page查到所有页信息，一共4个页。分别是 FSP_HDR, IBUF_BITMAP, INODE, INDEX。

select * from information_schema.innodb_sys_tables where name='test/t';
select * from information_schema.innodb_buffer_page where SPACE=28;

索引页分析

InnoDB引擎索引页的结构如下图，可以用 hexdump查看 t.ibd 文件，然后对照InnoDB页的结构分析下各个页的字段。

为什么别人能用好 MySQL？万字详解其复杂原理

 

索引页结构

# hexdump -C t.ibd
0000c000  95 45 82 8a 00 00 00 03  ff ff ff ff ff ff ff ff  |.E..............|
0000c010  00 00 00 00 00 28 85 7c  45 bf 00 00 00 00 00 00  |.....(.|E.......|
0000c020  00 00 00 00 00 1c 00 02  00 b0 80 04 00 00 00 00  |................|
0000c030  00 9a 00 02 00 01 00 02  00 00 00 00 00 00 00 00  |................|
0000c040  00 00 00 00 00 00 00 00  00 2f 00 00 00 1c 00 00  |........./......|
0000c050  00 02 00 f2 00 00 00 1c  00 00 00 02 00 32 01 00  |.............2..|
0000c060  02 00 1c 69 6e 66 69 6d  75 6d 00 03 00 0b 00 00  |...infimum......|
0000c070  73 75 70 72 65 6d 75 6d  03 00 00 00 10 00 1b 80  |supremum........|
0000c080  00 00 01 00 00 00 00 05  68 d1 00 00 01 54 01 10  |........h....T..|
0000c090  61 62 63 05 00 00 00 18  ff d6 80 00 00 02 00 00  |abc.............|
0000c0a0  00 00 05 69 d2 00 00 01  55 01 10 64 65 66 67 68  |...i....U..defgh|
0000c0b0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
0000fff0  00 00 00 00 00 70 00 63  95 45 82 8a 00 28 85 7c  |.....p.c.E...(.||
00010000  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
为什么别人能用好 MySQL？万字详解其复杂原理

 

FSEG结构关系图

 03 00 00 00 10 00 1b # 记录头
 80 00 00 01          # 主键值1
 00 00 00 00 05 68    # 事务ID
 d1 00 00 01 54 01 10 # 回滚指针
 61 62 63             # ch的值 abc

 05 00 00 00 18 ff d6 # 第2条记录头
 80 00 00 02          # 主键值2
 00 00 00 00 05 69    # 事务ID
 d2 00 00 01 55 01 10 # 回滚指针
 64 65 66 67 68       # ch的值 defgh

B+树页详细结构

当然，我们也可以通过 innodb_ruby 工具来分析表空间文件。

root@stretch:/home/vagrant# innodb_space -s /var/lib/mysql/ibdata1 -T test/t space-page-type-regions
start       end         count       type                
0           0           1           FSP_HDR             
1           1           1           IBUF_BITMAP         
2           2           1           INODE               
3           3           1           INDEX               
4           5           2           FREE (ALLOCATED)    
root@stretch:/home/vagrant# innodb_space -s /var/lib/mysql/ibdata1 -T test/t -p 3 page-records
Record 127: (id=1) → (ch="abc")

Record 154: (id=2) → (ch="defgh")

索引结构

InnoDB数据文件本身就是索引文件，其索引分聚集索引和辅助索引，聚集索引的叶节点包含了完整的数据记录，辅助索引叶节点数据部分是主键的值，除了空间索引外，InnoDB的索引实现基本都是 B+ 树，如图所示。

其中非叶子结点存储的是子页的最小的键值和子页的页号，叶子结点存储的是数据，数据按照索引键排序。同一层的页之间用双向链表连接(前面提到的FIL Header中PREV PAGE 和 NEXT PAGE)，同一页内的记录用单向链表连接(Record Header中记录了下一条记录的偏移)。每一页设置了两个虚拟记录Infimum和Supremum用于标识页的开始和结束。

为什么别人能用好 MySQL？万字详解其复杂原理

 

索引结构

在InnoDB中根据辅助索引查询，如果除了主键外还有其他字段，则需要查询两遍，先根据辅助索引查询主键的值，然后再到主索引中查询得到记录。此外，因为辅助索引的数据部分是主键值，主键不能过大，否则会导致辅助索引占用空间变大，用自增ID做主键是个不错的选择。

mysql> create table t2(id int auto_increment primary key, ch varchar(10), key(ch));
mysql> insert into t2(ch) values('ab');

创建一个新的测试表 t2，有主索引 id 和 辅助索引 ch，分析 t2.ibd 文件可验证：

页目录

前面提到INDEX页内的记录是通过单向链表连接在一起的，遍历列表性能会比较差，而INDEX页的页目录就是为了加速记录搜索。表 t2 中的页目录只有两项，分别是 0x63 和 0x70，即 99 和 112。

下面的ownedkey为这个页目录槽拥有的小于等于它的记录数目，显然 infimum 的ownedkey为 1，即只有它自己，没有key会比infimum小。而 supremum 的owned是3，分别是我们插入的两条记录和它自己。

slot    offset  type          owned  key
0       99      infimum       1       
1       112     supremum      3 

每个页目录槽最少要包含4个记录，最多包含8个记录(包括它自己)。如果我们在表 t2 中另外插入 7 条记录，则会增加一个新的slot，即 id 为 4 的记录，如下：

slot    offset  type          owned   key
0       99      infimum       1       
1       207     conventional  4       (i=4)
2       112     supremum      5  

下图是页目录结构图，可以通过页目录的二分查找提高页内数据的查询性能。

为什么别人能用好 MySQL？万字详解其复杂原理

 

页目录结构

3.4 InnoDB 系统表空间

系统表空间包含内容有：数据字典，双写缓冲，修改缓冲，undo日志，以及在系统表空间创建的表的数据和索引。可以看到，除了分配未使用的页外， UNDO_LOG，SYS, INDEX 页占据了不少的空间。UNDO_LOG 页存储的是Undo log，SYS 页存储的是数据字典、回滚段、修改缓存等信息，INDEX 是索引页，TRX_SYS 页用于InnoDB的事务系统。数据字典就是数据表的元信息，修改缓冲前面提到是为了提高IO性能也不再赘述，这里主要分析下 Undo 日志和双写缓冲。

root@stretch:/home/vagrant# innodb_space -s /var/lib/mysql/ibdata1 space-page-type-summary
type                count       percent     description         
ALLOCATED           427         55.60       Freshly allocated   
UNDO_LOG            125         16.28       Undo log            
SYS                 110         14.32       System internal     
INDEX               71          9.24        B+Tree index        
INODE               11          1.43        File segment inode  
FSP_HDR             9           1.17        File space header   
IBUF_BITMAP         8           1.04        Insert buffer bitmap
BLOB                5           0.65        Uncompressed BLOB   
TRX_SYS             2           0.26        Transaction system header

Undo 日志

MySQL的MVCC(多版本并发控制)依赖Undo Log实现。MySQL的表空间文件 t.ibd 存储的是记录最新值，每个记录都有一个回滚指针(见前面图中的Roll Ptr)，指向该记录的最近一条Undo记录，而每条Undo记录都会指向它的前一条Undo记录，如下图所示。默认情况下 undo log存储在系统表空间 ibdata1 中。

为什么别人能用好 MySQL？万字详解其复杂原理

 

Undo Log示意图

CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `a` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t3 values(1, 'A');

update t3 set a='B' where id=1;
update t3 set a='C' where id=1;

插入一条数据后，可以发现当前 t3.ibd 文件中的记录是 (1, ‘A’)，而 Undo Log此时有一条 insert 的记录。如下：

root@stretch:/var/lib/mysql# innodb_space -s ibdata1 -T test/t3 -p 3 -R 127 record-history
Transaction   Type                Undo record
(n/a)         insert              (id=1) → ()

执行后面的update语句，可以看到 undo log如下：

root@stretch:/var/lib/mysql# innodb_space -s ibdata1 -T test/t3 -p 3 -R 127 record-history
Transaction   Type                Undo record
2333          update_existing     (id=1) → (a="B")
2330          update_existing     (id=1) → (a="A")
(n/a)         insert              (id=1) → ()

需要注意的是，Undo Log 在事务执行过程中就会产生，事务提交后才会持久化，如果事务回滚了则Undo Log也会删除。

另外，删除记录并不会立即在表空间中删除该记录，而只是做个标记(delete-mark)，真正的删除则是等由后台运行的 purge 进程处理。除了每条记录有Undo Log的列表外，整个数据库也会有一个历史列表，purge 进程会根据该历史列表真正删除已经没有再被其他事务使用的 delete-mark 的记录。purge 进程会删除该记录以及该记录的 Undo Log。

双写缓冲

先回顾下InnoDB的记录更新流程：先在Buffer Pool中更新，并将更新记录到 Redo Log 文件中，Buffer Pool中的记录会标记为脏数据并定期刷到磁盘。由于InnoDB默认Page大小是16KB，而磁盘通常以扇区为单位写入，每次默认只能写入512个字节，无法保证16K数据可以原子的写入。

如果写入过程发生故障(比如机器掉电或者操作系统崩溃)，会出现页的部分写入(partial page writes)，导致难以恢复。因为 MySQL 的重做日志采用的是物理逻辑日志，即页间是物理信息，而页内是逻辑信息，在发生页部分写入时，无法确认数据页的具体修改而导致难以恢复。

MySQL 的数据页在真正写入到表空间文件前，会先写到系统表空间文件的一段连续区域双写缓冲(Double-Write Buffer，默认大小为 2MB，128个页)并 fsync 落盘，等双写缓冲写入成功后才会将数据页写到实际表空间的位置。

因为双写缓冲和数据页的写入时机不一致，如果在写入双写缓冲出错，可以直接丢弃该缓冲页，而如果是写入数据页时出错，则可以根据双写缓冲区数据恢复表空间文件。

4、InnoDB 事务隔离级别

InnoDB的多版本并发控制是基于事务隔离级别实现的，而事务隔离级别则是依托前面提到的 Undo Log 实现的。当读取一个数据记录时，每个事务会使用一个读视图(Read View)，读视图用于控制事务能读取到的记录的版本。

InnoDB的事务隔离级别分为：Read UnCommitted，Read Committed，Repeatable Read以及Serializable。其中Serializable是基于锁实现的串行化方式，严格来说不是事务可见性范畴。

 

5、InnoDB 和 ACID 模型

事务有 ACID 四个属性， InnoDB 是支持事务的，它实现 ACID 的机制如下：

Atomicity

innodb的原子性主要是通过提供的事务机制实现，与原子性相关的特性有：

Autocommit 设置。
COMMIT 和 ROLLBACK 语句(通过 Undo Log实现)。

Consistency

innodb的一致性主要是指保护数据不受系统崩溃影响，相关特性包括：

InnoDB 的双写缓冲区(doublewrite buffer)。
InnoDB 的故障恢复机制(crash recovery)。
Isolation
innodb的隔离性也是主要通过事务机制实现，特别是为事务提供的多种隔离级别，相关特性包括：

Durability

innodb的持久性相关特性：

声明：本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系，我们将立即删除或处理。
▍相关推荐
更多资讯 >>>
Copyright @ 简易百科 V 2.0 京ICP备14042104号