mysql InnoDB——插入缓存、二次写、支持事务、异步IO等特性介绍

最新推荐文章于 2024-04-04 12:55:28 发布

编程小王子啊

最新推荐文章于 2024-04-04 12:55:28 发布

阅读量2.2k

点赞数 3

分类专栏：数据库专栏文章标签： mysql 数据库 java sql

本文链接：https://blog.csdn.net/u012361112/article/details/109253174

版权

数据库专栏专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、Mysql逻辑架构

mysql逻辑架构分为3层：

1). 连接层：主要完成一些类似连接处理，授权认证及相关的安全方案。

2). 服务层：在 MySQL据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断，SQL接口，SQL解析，SQL分析优化，缓存查询的处理以及部分内置函数执行(如日期,时间,数学运算,加密)等等。各个存储引擎提供的功能都集中在这一层，如存储过程，触发器，视图等。

3). 引擎层：是底层数据存取操作实现部分，由多种存储引擎共同组成。真正负责MySQL中数据的存储和提取。针对特殊数据引擎还要做事务处理。

二、MySQL 存储引擎

存储引擎是数据库的核心，对于mysql来说，存储引擎是以插件的形式运行的。需要特别注意的是，存储引擎是基于表的，而不是数据库。

1. MySQL 存储引擎概述

1.1 InnoDB 存储引擎

InnoDB存储引擎支持事务，其设计目标主要面向在线事务处理(OLTP)的应用，其特点是行锁设计、支持外键，并支持类似于Oracle的非锁定读，即默认读操作不会产生锁。InnoDB 通过多版本并发控制系统(MVCC)来获得高并发性，并且实现了SQL隔离的4种级别。同时，使用一种被称为next-key locking的策略来避免幻读(phantom)现象的产生。除此之外，InnoDB存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead)等高性能和高可用的功能。

1.2 MyISAM 存储引擎

MyISAM 存储引擎不支持事务、表锁设计，支持全文索引，主要面向一些OLAP数据库应用。在MySQL 5.5.8版本之前MyISAM存储引擎是默认的存储引擎。MyISAM存储引擎的另一个与众不同的地方是它的缓冲池只缓存索引文件，而不缓冲数据文件，数据文件的缓存交由操作系统本身来完成。MyISAM 存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。可以通过使用myisampack 工具来进一步压缩数据文件，因为myisampack 工具使用(Huffman)编码静态算法来压缩数据，因为使用myisampack工具压缩的表是只读的。

1.3 NDB 存储引擎

NDB 存储引擎是一个集群存储引擎，类似于Oracle的 RAC集群，不过与Oracle RAC share nothing 架构不同的是，其结构是share nothing的集群架构，因此能够提供更高的可用性。NDB的特点是数据全部放在内存中（从MySQL 5.1版本开始，可以将非索引数据放在磁盘上），因此主键查找的速度极快，并且通过添加NDB数据存储节点可以线性地提高数据库性能。

1.4 Memory 存储引擎

Memory 存储引擎将表中的数据全部放在内存中，如果数据库重启或者发生崩溃，表中的数据都将消失。它非常适合用于存入存储临时数据的临时表。它只支持表锁，并发性能较差，并且不支持TEXT和BLOB列类型。

2. 查看MySQL的存储引擎

查看mysql现在已提供的存储引擎:

mysql> show engines;

查看mysql当前默认的存储引擎:

mysql> show variables like '%storage_engine%';

查看某张表用了什么引擎:

mysql> show create table 表名;

三、InnoDB 存储引擎

1. InnoDB 体系架构

1.1 后台线程

InnoDB 存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务

1) Master Thread

Master Thread 是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲、UNDO页的回收。

2) IO Thread

在InnoDB 存储引擎中大量使用了AIO(Async IO) 来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread的主要工作室负责这些IO请求的回调处理。InnoDB 1.0版本之前共有4个IO Thread，分别是write、read、insert buffer和log IO Thread。从InnoDB 1.0.x版本开始，read thread 和write thread 分别增大到了4个，并且不再使用innodb_filre_io_threads参数，分别使用innodb_read_io_threads和innodb_write_io_threads参数进行设置。

3) Purge Thread

事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页。用户可以在MySQL数据库的配置文件中添加如下命令来启动独立的Purge Thread:

[mysqld]

innodb_purge_threads=1

4) Page Cleaner Thread

Page Cleaner Thread 是在InnoDB 1.2.x版本中引入的。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。

1.2 内存

InnoDB 内存包含缓冲池、重做日志缓冲、额外内存池3个部分：

1) 缓冲池

InnoDB 存储引擎是基于磁盘存储的，并将其中的记录安装页的方式进行管理。缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库进行读取页的操作，首先将从磁盘读取到的页放在缓冲池中，这个过程称为将页“FIX”在缓冲池中。下一次读取相同的页时，首先判断该页是否在缓冲池中。对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。这里需要注意的是，页从缓冲池中刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为CheckPoint的机制刷新回磁盘。同样，这也是为了提高数据库的整体性能。

综上所述，缓冲池的大小直接影响数据库的整体性能。对于InnoDB 存储引擎而言，其缓冲池大小由参数innodb_buffer_pool_size决定：

mysql> show VARIABLES like 'innodb_buffer_pool_size'\G;
*************************** 1. row ***************************
Variable_name: innodb_buffer_pool_size
        Value: 134217728
1 row in set (0.01 sec)

从InnoDB 1.0.x版本开始，允许有多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。这样做的好处是减少数据库内部的资源竞争，增加数据库的并发处理能力。可以通过参数innodb_buffer_pool_instances来进行配置，该值默认为1。

mysql> show VARIABLES like 'innodb_buffer_pool_instances'\G;
*************************** 1. row ***************************
Variable_name: innodb_buffer_pool_instances
        Value: 1
1 row in set (0.00 sec)

2) LRU List

通常来说，数据库中的缓冲池是通过LRU算法来进行管理的。即最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。

在InnoDB存储引擎中，缓冲池中页的默认大小为16KB，同样使用LRU算法对缓冲池进行管理。稍有不同的是InnoDB引擎对传统的LRU算法做了一些优化。新读取到的页，虽然是最新访问的页，但并不是直接放入到LRU列表的首部，而是放入到LRU列表的midpoint位置。在默认配置下，该位置在LRU列表长度的5/8处。midpoint位置可由参数innodb_old_blocks_pct控制：

mysql> show VARIABLES like 'innodb_old_blocks_pct'\G;
*************************** 1. row ***************************
Variable_name: innodb_old_blocks_pct
        Value: 37
1 row in set (0.00 sec)

2. InnoDB 关键特性

InnoDB 存储引擎的关键特性包括：

插入缓冲
两次写
自适应哈希索引
异步IO
刷新邻接页
支持事务

2.1 插入缓冲

插入缓冲并不是缓冲池的一个组成部分，它和数据页一样，也是物理页的一个组成部分。

在进行插入操作时，数据页的存放还是按照主键进行顺序存放的，但是对于非聚集索引叶子节点的插入不再是顺序的了，这时就需要离散地访问非聚集索引页，由于随机读取的存在而导致了插入操作性能下降。

InnoDB 存储引擎开创性地设计了Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接地插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer对象中，好似欺骗，然后再以一定的频率和情况进行Insert Buffer和辅助索引子节点的merge(合并)操作。然而Insert Buffer的使用需要同时满足以下两个条件：

索引是辅助索引
索引不是唯一的

Insert Buffer 的数据结构是一颗B+树。

2.2 两次写

如果说Insert Buffer带给InnoDB 存储引擎的是性能上的提升，那么两次写(double write)带给InnoDB 存储引擎的是数据页的可靠性。

当发生数据库宕机时，可能InnoDB 存入引擎正在写入某个耶到列表中，而这个页只写了一部分，比如16kb的页，只写了前4kb，之后就发生了宕机，这种情况被称为部分写失效(partial page write)。重做日志中记录的是对页的物理操作，如偏移量800，写'aaaa'记录。在应用重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，在进行重做，这就是doublewrite。体系架构如下图所示：

脏页刷新时的doublewrite步骤：

1、先通过memcpy方法将该脏页复制到 doublewrite buffer中。

2、将doublewrite buffer中的数据分两次（一次1M）顺序的写入共享表空间的doublewrite中.

3、上面操作只是写入oscache，为了可靠性，马上调用fsync将数据flush到磁盘（doublewrite的文件写入是顺序写，所以效率高）。

4、完成doublewrite文件写入后，接下来将doublewrite buffer中的数据对应到各个表空间文件进行写入操作（离散写）。

2.3 自适应哈希索引

哈希（hash）是一种非常快的查找方法，一般情况下查找的时间复杂度为O(1)。常用于连接（join）操作，如SQL Server和Oracle中的哈希连接（hash join）。但是SQL Server和Oracle等常见的数据库并不支持哈希索引（hash index）。MySQL的Heap存储引擎默认的索引类型为哈希，而InnoDB存储引擎提出了另一种实现方法，自适应哈希索引（adaptive hash index）。

InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应（adaptive）的。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

2.4 异步io

为了提高磁盘操作性能，当前的数据库系统都采用异步IO的方式来处理磁盘操作。

异步IO：用户可以在发出一个IO请求后立即再发出另外一个IO请求，当全部IO请求发送完毕后，等待所有IO操作完成，这就是AIO。
与AIO对应的是Sync IO，即每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。

异步IO的好处　　

不用等待直接响应上一个用户的请求；
多次的请求在一起排序，请求的数据页是在一起的，一次读出来，减少多次读。（数据库的读写请求队列放在文件系统中单独分配的一块小内存结构里，非文件系统的缓存）

四、MySQL 物理文件

1. 日志

1.1 慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句会被记录到日志文件中。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动设置slow_query_log这个参数。

mysql> show variables like 'long_query_time'\G;
*************************** 1. row ***************************
Variable_name: long_query_time
        Value: 10.000000
1 row in set (0.01 sec)
mysql> show variables like 'slow_query%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /var/lib/mysql/node2-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)

要启用慢查询日志功能，需要修改my.cnf文件，增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器：

long_query_time=5.0

slow_query_log=1

slow_query_log_file=/tmp/mysql_slow.log

日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow，一些常用例子如下：

得到返回记录集最多的10个SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

1.2 二进制日志

二进制日志(binary log)记录了对MySQL数据库执行更新的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。它主要有以下几种作用：

恢复：某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复
复制：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库(一般称为Slave或Standby)与一台MySQL数据库(一般称为master或primary)进行实时同步。
审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

1) 启用binlog: 通过修改配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件的 log-bin 参数，如果没有给定值，写成 log-bin=，则默认名称为主机名：

[mysqld]
log-bin=my-binlog-name

2) 写 Binlog 的时机
对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。

如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。
设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。

如果 sync_binlog=0 或 sync_binlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。在MySQL 5.7.7之前，默认值 sync_binlog 是0，MySQL 5.7.7和更高版本使用默认值1，这是最安全的选择。

3) Binlog 的日志格式
记录在二进制日志中的事件的格式取决于二进制记录格式。支持三种格式类型：

STATEMENT：基于SQL语句的复制（statement-based replication, SBR)，每一条被修改数据的sql都会记录在binlog中
ROW：基于行的复制（row-based replication, RBR)，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。
MIXED：混合模式复制（mixed-based replication, MBR)，在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默认值是 ROW。日志格式通过 binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

2. 表结构定义文件

因为MySQL插件时存储引擎的体系结构的关系，MySQL数据的存储是根据表进行的，每个表都会有与之对应的文件。但不论表采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义。frm还用来存放视图的定义。

3. InnoDB 存储引擎文件

3.1 表空间文件

InnoDB采用将存储的数据按表空间（tablespace）进行存放的设计。在默认配置下会有一个初始大小为10MB，名为ibdata1的文件。该文件就是默认的表空间文件（tablespace file），用户可以通过参数innodb_data_file_path对其进行设置.

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)
设置innodb_data_file_path参数后，所有基于InnoDB存储引擎的表的数据都会记录到该共享表空间中。若设置了参数innodb_file_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立表空间。独立表空间的命名规则为:表名.ibd。通过这样的方式，用户不用将所有数据都存放于默认的表空间中。这些单独的表空间仅仅存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的表空间中。默认该参数是关闭的。

这里写图片描述

3.2 重做日志文件

在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。在MySql官方手册中将其称为InnoDB存储引擎的日志文件，不过更准确的定义应该是重做日志文件(redo log file)。重做日志文件对于InnoDB存储引擎很重要，它们记录了对于InnoDB存储引擎的事务日志。

在InnoDB存储引擎中，对于各种不同的操作有着不同的重做日志格式。到InnoDB1.2.x版本为止，总共定义了51种重做日志类型。虽然各种重做日志的类型不同，但是他们有着基本的格式，下表显示了重做日志条目的结构：

这里写图片描述

重做日志条目由4个部分组成：

redo_log_type占用1个字节，表示重做日志的类型。
space表示表空间的ID，但采用压缩的方式，因此占用的空间可能小于4字节。
page_no表示页的偏移量，同样采用压缩的方式
redo_log_body表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析。

写入重做日志文件的操作不是直接写，而是先写入一个重做日志缓冲(redo log buffer)中，然后按照一定的条件顺序地写入日志文件。下图显示了重做日志的写入过程。

这里写图片描述

五、表

1. 索引组织表

在Innodb存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。如果在创建表时没有指定主键，则按以下方式选择或创建主键：

首先判断表中是否有非空的唯一索引，如果有，则该列即为主键。
如果没有，则Innoodb存储引擎自动创建一个6字节大小的指针。

2. InnoDB 逻辑存储结构

从InnoDB存储引擎的逻辑结构看，所有数据都被逻辑地存放在一个空间内，称为表空间，而表空间由段（sengment）、区（extent）、页（page）组成。ps：页在一些文档中又称块（block）。InnoDB存储引擎的逻辑存储结构大致如下：

1) 表空间(table space)

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。在物理上，对应一个xxx.ibd文件。如果参数innodb_file_per_table为true，则该数据库下的所有表共享一个表空间(一个物理文件)，否则每张表一个单独表空间，mysql 5.7版本该参数默认为true。

2) 段(segment)

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。因为InnoDB存储引擎是由索引组织的（index organized），因此数据即索引，索引即数据。那么数据段即为B+树段叶子节点（Leaf node segment），索引段即为B+树段非索引节点。

3) 区(extent)

区是由连续的页（Page）组成的空间，在任何情况下每个区大小都为1MB，为了保证页的连续性，InnoDB存储引擎每次从磁盘一次申请4-5个区。默认情况下，InnoDB存储引擎的页大小为16KB，即一个区中有64个连续的页。（1MB／16KB=64）

InnoDB1.0.x版本开始引入压缩页，每个页的大小可以通过参数KEY_BLOCK_SIZE设置为2K、4K、8K，因此每个区对应的页尾512、256、128。InnpDB1.2.x版本新增了参数innodb_page_size，通过该参数可以将默认页的大小设置为4K、8K，但是页中的数据不是压缩的。创建的表的默认大小是96kb。在每个段开始时，先用32个页大小的碎片页来存放数据(即每个段有。在使用完这些页之后才是64个连续页的申请。

可以使用下面的命令查看页大小：

SHOW GLOBAL STATUS like 'Innodb_page_size';

4) 页(page)

页是InnoDB存储引擎磁盘管理的最小单位，每个页默认16KB；InnoDB存储引擎从1.2.x版本碍事，可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。若设置完成，则所有表中页的大小都为innodb_page_size，不可以再次对其进行修改，除非通过mysqldump导入和导出操作来产生新的库。

innoDB存储引擎中，常见的页类型有：

1. 数据页（B-tree Node)

2. undo页（undo Log Page）

3. 系统页（System Page）

4. 事物数据页（Transaction System Page）

5. 插入缓冲位图页（Insert Buffer Bitmap）

6. 插入缓冲空闲列表页（Insert Buffer Free List）

7. 未压缩的二进制大对象页（Uncompressed BLOB Page）

8. 压缩的二进制大对象页（compressed BLOB Page

5) 行(row)

InnoDB存储引擎是面向列的（row-oriented)，也就是说数据是按行进行存放的，每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200，即7992行记录。

3. InnoDB 行记录格式

在早期的InnoDB版本中，由于文件格式只有一种，因此不需要为此文件格式命名。随着InnoDB引擎的发展，开发出了不兼容早期版本的新文件格式，用于支持新的功能。为了在升级和降级情况下帮助管理系统的兼容性，以及运行不同的MySQL版本，InnoDB开始使用命名的文件格式。

Antelope: 先前未命名的，原始的InnoDB文件格式。它支持两种行格式：COMPACT 和 REDUNDANT。MySQL5.6的默认文件格式。可以与早期的版本保持最大的兼容性。不支持 Barracuda 文件格式。
Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED 和 DYNAMIC。与这两个新的行格式相关的功能包括：InnoDB表的压缩，长列数据的页外存储和索引建前缀最大长度为3072字节。

3.1 Compact

Compact行记录是在MySQL5.0中引入的，为了高效的存储数据，简单的说，就是为了让一个页（Page）存放的行数据越多，这样性能就越高。行记录格式如下：　　

1）变长字段长度列表：记录一行记录中varchar类型列的值的长度的列表。

2）NULL标识位：该位指示了该行数据中是否有NULL值，有则用1，用位图来存储。比如：第一列，第三列，第4列值为NULL，则二进制表示为：0000 1101。

3）记录头信息：固定占用5字节（40位）

4）列N数据：实际存储每列的数据，NULL不占该部分任何空间，即NULL占有NULL标志位，实际存储不占任何空间。不管是CHAR类型还是VARCHAR类型，在compact格式下NULL值都不占用任何存储空间。

5）每一行数据除了用户定义的例外，还有两个隐藏列，事物ID列和回滚指针列，分别位6字节和7字节的大小，MySQL实现MVCC需要依赖这两个字段。若InnoDB表没有定义主键，每行还会增加一个6字节的rowid列。

3.2 Redundant

Redundant是MySQL 5.0版本之前InnoDB的行记录存储方式，MySQL 5.0支持Redundatn是为了兼容之前版本的页格式。MySQL 5.0之前的行记录格式：

1）字段偏移列表：不同于Compact行记录格式，它的首部是一个字段长度偏移列表，同样是按照列的顺序逆序放置的，若列的长度小于255字节，用1字节表示，若大于255字节，用2字节表示。

2）记录头信息：占用6字节（48位）

比起Compact行格式，Redundant格式多了n_fileds属性。

3）Redundant格式对于VARCHAR类型的NULL值，不会占用任何存储空间，而CHAR类型的NULL值需要占用空间。并且CHAR类型会占用可能存放的最大字节数，比如表test的字符集编码为utf-8时，定义一个char(10)类型的列时，即使该列为NULL，仍然会占用30个字节的空间（单个字符在utf-8编码中最多占用3个字节）。

3.3 行溢出数据

InnoDB 存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。

1）VARCHAR(N) 中N指的是字符的长度，VARCHAR类型最多可以存放65535字节，并且65535是指一行中n个varchar列类型的长度总和，可以存放的最大字符长度和使用的字符集编码有关。

2）当行记录的长度没有超过行记录最大长度时，所有数据都会存储在当前页。

3）当行记录的长度超过行记录最大长度时，变长列（variable-length column）会选择外部溢出页（overflow page，一般是Uncompressed BLOB Page）进行存储。

4）Compact + Redundant：保留前768Byte在当前页（B+Tree叶子节点），其余数据存放在溢出页。768Byte后面跟着20Byte的数据，用来存储指向溢出页的指针。

3.4 概述

对于 Compact 和 Redundant 行格式，InnoDB将变长字段(VARCHAR, VARBINARY, BLOB 和 TEXT)的前786字节存储在B+树节点中，其余的数据存放在溢出页(off-page)，如下图：

上面所讲的讲的blob或变长大字段类型包括blob,text,varchar，其中varchar列值长度大于某数N时也会存溢出页，在latin1字符集下N值可以这样计算：innodb的块大小默认为16kb，由于innodb存储引擎表为索引组织表，树底层的叶子节点为一双向链表，因此每个页中至少应该有两行记录，这就决定了innodb在存储一行数据的时候不能够超过8k，减去其它列值所占字节数，约等于N。

使用Antelope文件格式，若字段的值小于等于786字节，不需要溢出页，因为字段的值都在B+树节点中，所以会降低I/O操作。这对于相对较短的BLOB字段有效，但可能由于B+树节点存储过多的数据而导致效率低下。

4. InnoDB 数据页结构

mysql数据页的物理存储结构如下图：

、

4.1 File Header

File Header用来记录页的一些头信息，固定38字节，一些关键字段如下：

FILE_PAGE_OFFSET，表空间中页的偏移值。
FILE_PAGE_PREV, FILE_PAGE_NEXT分别表示当前页的上一个页和下一个页。
FILE_PAGE_TYPE表示页的类型，有B+树页节点，索引节点，BLOB页等类型。

4.2 Page Header

Page Header用来记录数据页的状态信息，由14个部分组成，共占用56字节。

4.3 Infimum 和 Supremum Record

这部分存储的是固定的两条记录，分别为数据页中的「最小记录」和「最大记录」，如图所示：

4.4 User Record 和 Free Space

User Record就是之前讨论过的部分，即实际存储行记录的内容。Free Space很明显指的就是空闲空间，同样也是个链表数据结构。在一条记录被删除后，该空间会被加入到空闲链表中。

在数据页中，当记录为空时，User Records 是不存在的。随着记录的一条条插入，会不断从 Free Space 开辟空间分配给记录，如图所示：

4.5 Page Directory

Mysql 查询一条数据时，是先定位到该条数据所在的数据页，然后将页载入内存中，再在内存中差中。一个数据页的记录可能很多，在上千条的情况下，如果通过顺序遍历，会非常耗时。Page Directory的作用便是对这些记录再建立索引，增加查询速度。

由于记录之间是按主键排序的，可以把它们从小到大分成一个个的「组」，每组包含很少的几条记录，如图所示：

分组规则大致如下：

「最小记录」自成一组
包含「最大记录」的组一般为 1~8 条记录
其它记录一般是 4~8 条分为一组

分组之后，把每组中最大的那条记录的地址偏移量提出来，按顺序存储起来，这些地址偏移量称为槽（Slot），而这些槽就组成了页目录（Page Directory）。就像是给一本书做了目录。

目录有了，怎么使用呢？此时如果要查找一条记录，步骤大致如下：

由于页目录中的槽是有序的，因此可以用「二分法」快速定位到一个槽；
找到该槽所在分组中主键值最小的记录；
通过 next_record 遍历组中的记录。

5. 分区表

Mysql 数据库支持的分区类型为水平分区，并不支持垂直分区。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了存放了数据又存放了索引。而全部分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL还不支持全局分区。

当前MySQL数据库支持以下几种类型的分区：

RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。
LIST分区：和RANGE分区类似，只是LIST分区面向的是离散的值。
HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。
KEY分区：根据MySQL数据库提供的哈希函数来进行分区。

编程小王子啊

关注

3
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
mysql InnoDB——插入缓存、二次写、支持事务、异步IO等特性介绍

觉得自己对Mysql事务很熟悉？那可能是你没碰到阿里面试官Java程序猿呀关注12020.06.29 18:10:29字数 4,585阅读 2,682前言迎面走来了一个风尘仆仆的身穿格子衫的男子，手里拿着一个MacBook Pro，看着那稀少的发量，和那从容淡定的眼神。我心里一颤，我去，这是架构师，架构师来面我技术面，我心里顿时不淡定了，表面很稳实则心里慌得一批。果然，他手里拿着我的简历，快速的扫了一下，然后用眼角余光看了一下我，上来就开问。Mysql事务简介面试官：看.
复制链接

扫一扫