表——MySQL技术内幕 InnoDB存储引擎

索引组织表

InnoDB使用主键聚合索引，意思就是MySQL中的表是按照主键顺序在磁盘文件中存储的。

如果表没有主键：

判断表中有没有唯一非空索引，有则选中唯一非空索引中的第一个被定义的那个列来做聚合索引
如果条件1没有选出一个主键，那么自动创建一个6字节大小的指针来做聚合索引

关于隐藏列_rowid

如果表有一个数字类型的单列主键，那么隐藏列_rowid指向这个主键。
其他情况下，_rowid指向第一个唯一单列非空索引，前提是这个索引列的类型得是整数
否则_rowid无法使用

create table rowid_test(
  a int not null,
  b int null,
  c int not null
  unique key(b),
  unique key(c),unique key(a)
);
insert into rowid_test values (1,11,111) , (2,22,222) , (3,33,333);

可以看出_rowid指向了第一个定义的单列整数唯一非空索引c。

InnoDB逻辑存储结构

InnoDB的所有数据都被存放在表空间(tablespace)中，表空间由段(segment)组成，段又由区(extend)组成，区又由页(page，某些文档中称为块)组成。

表空间

默认情况下所有数据放在共享表空间ibdata1中，如果启用了innodb_file_per_table，则每张表都有一个独立的表空间。这个独立的表空间只存储数据和索引，还有插入缓冲Bitmap页，其余的数据还是在共享表空间中。

共享表空间中存储的是回滚(undo)信息，插入缓冲索引页，系统事务信息，二次缓冲写等。

共享表空间不会自动缩减，比如undo信息在没用的时候会被标记，这些空间可以在之后被使用。这就造成了表空间文件越来越大的问题。

在高版本的MySQL中，undo log已经从ibdata1中提取出来，有了单独的undo_001和undo_002表空间，并且也可以自动缩减了

段

表空间由段组成，段分为数据段、索引段和回滚段等。

数据段即B+树的叶子节点(Leaf node segment)，索引段是B+树的非叶子节点(Non-leaf node segment)。

InnoDB会自动完成段的管理，而不是由DBA来完成。

区

区由连续的页组成，大小为1MB。默认InnoDB存储引擎中的页大小为16KB，也就是一个区有64个页。

压缩页可能是2K,4K,6K，对应的区中就会有512,256,128个页。

也就是说新创建的表文件最起码有1MB，但事实不是这样的

CREATE TABLE t1(
  col1 INT NOT NULL AUTO_INCREMENT,
  col2 VARCHAR(7000),
  PRIMARY KEY(col1)
);

这个新建的表文件才112KB。这是因为InnoDB在一开始先使用32个页大小的碎片页来存放数据，这样可以节省一些小表或undo类段的存储空间，而来也让碎片利用率提升。

插入两条记录

INSERT INTO t1 SELECT NULL, REPEAT('a', 7000);
INSERT INTO t1 SELECT NULL, REPEAT('a', 7000);

使用工具innodb_space查看这个表空间的详情

注意，这里只有一个INDEX页，这是B+树的索引页。只有一个索引页是因为页大小为16KB = 16384Byte，这两条数据我们忽略主键，只算大的字符串列，一共才是14000Byte，完全可以容纳进一个页。但再插入一个就无法容纳了。

INSERT INTO t1 SELECT NULL, REPEAT('a', 7000);

可以看到B+树分裂出了两个叶子(5,6)，一个非叶子节点(4)，如何判断呢，我想应该是非叶子节点没有prev和next吧，而叶子节点顶多没有其中一个。

我们想看到区的存在，就需要用完这32个碎片页，目前从上面的结果分析来看，现在的数据已经占用7个页了。我们最起码需要16384 * (32 - 7) = 409600Byte大小的数据，也就是58条多一点，由于之前的两个B+树叶子节点每一个还能存储一条数据，大概再插入60条数据的话，32个碎片页刚刚快到达满的状态。这个数是书上给的，我就是大致分析一下。

定义存储过程

DELIMITER //
CREATE PROCEDURE load_t1(cnt INT UNSIGNED) 
BEGIN
  DECLARE s INT UNSIGNED DEFAULT 1;
  DECLARE c VARCHAR(7000) DEFAULT REPEAT('a', 7000);
  WHILE s <= cnt DO
    INSERT INTO t1 SELECT NULL, c;
    SET s = s+1;
  END WHILE;
END;
//
DELIMITER ;

调用

CALL load_t1(60);

再次查看这个表文件的大小，由于还是没到1MB，所以我们可以推断32个页的碎片空间还是没用完。

最后插入一条数据

INSERT INTO t1 SELECT NULL, REPEAT('a', 7000);

空间变成了2.0MB，应该是转而使用了两个段进行存储。

当再查看表空间详情时，多了很多待分配的空间

页

页是大部分数据库系统都有的概念，有时也称作块(block)，它是InnoDB管理磁盘空间的最小单位。默认大小16KB，可以通过innodb_page_size设置。一旦设置便不可再修改，除非通过mysqldump导出并产生新的数据库。

InnoDB中的页类型

行

MySQL是面向行的数据库，数据按照行进行存放，一个页中最多存放16KB/2-200行记录。

另一种就是面向列的数据库，也叫列族数据库。比如MySQL的infobright引擎，Google的Big Table。

InnoDB行记录格式

行记录格式决定了表中的行使用什么格式存储，可以选择COMPACT、COMPRESSED、DEFAULT、DYNAMIC、FIXED和REDUNDANT。

Compact行记录格式

从名字来看就相当紧凑，相当节省空间，当表中的行越多，Compact格式的性能就发挥的越强。

开始是一个非空变长字段长度列表，按照列顺序逆序放置，长度为

列长度小于255字节，用1字节表示
列长度大于255字节，用两字节表示

两字节足够表示了，因为VARCHAR最大长度就是65535。

然后是NULL标志位，也称空位图，表示该行是否有某一列是NULL。有在对应位用1表示，也是倒序的。

比如一个表有4个字段t1,t2,t3,t4，那么：

t1为空 空位图：0000 0001
t2t3为空 空位图： 0000 0110

该列的大小不确定，取决于列有多少个可空字段，假设这个可空字段数为N，则该列占用\(\left \lceil N/8 \right \rceil\)字节。

然后是记录头信息，固定占用5字节。下面是每个位代表的信息。

然后就是每个列中的数据，NULL列不占用存储空间，注意两个隐藏列，事务ID和回滚指针分别为6字节和7字节，如果没有定义主键，还会加上一个6字节的_rowid列。

创建一个表，使用LATIN1字符集，就是ISO-8859-1单字节字符集，然后使用Compact行记录格式。

CREATE TABLE compact_test (
  t1 VARCHAR(10),
  t2 VARCHAR(10),
  t3 CHAR(10),
  t4 VARCHAR(10)
) CHARSET=LATIN1 ROW_FORMAT=COMPACT;

INSERT INTO compact_test VALUES ('a','bb','bb','ccc');
INSERT INTO compact_test VALUES ('d','ee','ee','fff');
INSERT INTO compact_test VALUES ('d',NULL,NULL,'fff');

使用hexdump工具读取这个表的二进制ibd文件。

hexdump -C -v compact_test.ibd > compact_test.txt

我这里从00010078开始记录了表中的三行数据。你的可能不一样，可以使用编辑器的搜索功能搜索03 02 01

分析这些数据

// 第一行
03 02 01 变长字段长度 逆序
00 空值标志字节 第一行没有空值
00 00 10 00 2c 记录头
00 00 00 00 05 00 自动生成的ROWID
00 00 00 00 71 17 事务ID
82 00 00 01 0a 01 10 回滚指针
61 列1数据a
62 62 列2数据bb
62 62 20 20 20 20 20 20 20 20 列3数据bb，因为是CHAR 后面补空格
63 63 63 列4数据ccc

// 第二行
03 02 01
00
00 00 18 00 2b
00 00 00 00 05 01
00 00 00 00 71 18
81 00 00 01 08 01 10
64 列1数据d
65 65 列2数据ee
65 65 20 20 20 20 20 20 20 20 列3数据ee
66 66 66 列4数据fff

// 第三行
03 01 因为t2为空，所以它不在这里面
06 有空列，空列是t2,t3，6的二进制是0000 0110，这说明这个字段存储的也是倒序的哪个列为空的信息
00 00 20 ff 98 
00 00 00 00 05 02
00 00 00 00 71 1d
82 00 00 01 1e 01 10
64 列1值d
66 66 66 列4值fff

Redundant格式

MySQL5.0之前的InnoDB行记录方式，后来为了兼容性页保留了该格式。

字段长度偏移列表用于计算出每个字段的位置，偏移列表中的表项在字段长度小于255字节时使用1字节表示，大于255字节时用2字节表示。

同时记录头信息中多了两个字段，n_fields和1byte_offs_flag，并使用6个字节存储。

CREATE TABLE redundant_test
ROW_FORMAT=Redundant
AS SELECT * FROM compact_test;

hexdump -C -v redundant_test.ibd > redundant_test.txt

如下是使用Redundant时表的二进制文件，从那个23开始是表数据

整理一下

23 20 16 14 13 0c 06 长度偏移列表，逆序
00 00 10 0f 00 ba 字段Header
00 00 00 00 05 03 RowID
00 00 00 00 71 1e 事务Id
81 00 00 01 0c 02 88 回滚指针
61 列1数据
62 62 列2数据
62 62 20 20 20 20 20 20 20 20 列3数据
63 63 63 列4数据

主要不一样的就是长度偏移列表，长度偏移列表是倒序存储的，所以我们从右面开始读。06代表第一列长度为6(_rowid)，0c代表第二列长度为6，因为0c转换成十进制是12，6+6=12。这个长度偏移列表记录列相对于起始位置0的偏移量，减去前一个列的偏移量，就能计算出字段的长度了。

下面我们看看第三行数据，看看Redundant如何处理NULL值

21 9e 94 14 13 0c 06
00 00 20 0f 00 74
00 00 00 00 05 05
00 00 00 00 71 1e
81 00 00 01 0c 02 a6
64 第1列数据
00 00 00 00 00 00 00 00 00 00 第3列数据
66 66 66 第4列数据

Redundant完全忽略了第二列数据，也就是说它完全不保存变长空字段，但是对于定长空字段则占用字段最大字节数。

看长度偏移列表中，第一列的偏移量是14，第二列的偏移量直接到了94，转换成10进制这中间差了128个数字，但目前我还不知道Redundant是如何通过这一点检测出第二列是NULL的。等有时间看看官方文档吧。

行溢出数据

InnoDB会把一个记录中的某些数据存储在数据页外，这种不存储在数据页中的数据项称为行溢出数据，这也是我们常说BLOB类型的列会存到单独的地方的原因，但事实情况稍微复杂一点，BLOB类型不一定会单独存储在数据页外，VARCHAR类型也不一定单独存储在数据页内。

数据库在存储记录的实现中大都会遵循一个原则，即记录必须小于页大小，不能有记录横跨多个页。所以，在MySQL中，一个记录行的大小被限定在小于16KB。也就是说，一个字段是否作为行溢出数据单独存储与它们的类型无关，主要原因在于，它是否过大而可能导致行不能存储在一个页中。

创建这样的记录

CREATE TABLE test_overflow (
  t1 varchar(65532)
) CHARSET=LATIN1 ROW_FORMAT=Compact;

INSERT INTO test_overflow SELECT repeat('a',65532);

5到9这几个不在INDEX索引页中的数据应该就是t1这个列的行溢出数据了。它们保存在Uncompressed BLOB Page中。

实际上，这一个列的前768字节的数据仍然保存在索引页中，然后就是指向行溢出页的偏移量，可以使用hexdump工具去验证。以下是行溢出数据的存储方式。

那么一个字段存储在BLOB Page的长度阈值是多少？因为InnoDB使用B+树索引组织表，那么一个索引页中至少得有两个数据，否则就是链表了。所以InnoDB会判断如果插入的记录在页中只能存放一条，那么就将记录中的数据存储到BLOB Page。

CREATE TABLE test_overflow2 (
  t1 varchar(9000)
) CHARSET=LATIN1 ROW_FORMAT=Compact;


INSERT INTO test_overflow2 SELECT repeat('a',9000);

可以看到，长度为9000的VARCHAR确实存放在了BLOB PAGE中，而非数据页，因为InnoDB认为一个页中没办法存放两条这种数据。

对于VARCHAR数据，书上给出的阈值是8098。插入长度为8098的数据不会放到BLOB PAGE中，而是数据页中。

DROP TABLE test_overflow2;
CREATE TABLE test_overflow2 (
  t1 varchar(9000)
) CHARSET=LATIN1 ROW_FORMAT=Compact;


INSERT INTO test_overflow2 SELECT repeat('a',8098);
INSERT INTO test_overflow2 SELECT repeat('a',8098);
INSERT INTO test_overflow2 SELECT repeat('a',8098);
INSERT INTO test_overflow2 SELECT repeat('a',8098);
INSERT INTO test_overflow2 SELECT repeat('a',8098);
INSERT INTO test_overflow2 SELECT repeat('a',8098);
INSERT INTO test_overflow2 SELECT repeat('a',8098);
INSERT INTO test_overflow2 SELECT repeat('a',8098);

总结一下：

对于任何类型的过大的数据（一般是BLOB，VARCHAR），InnoDB会存储在行缓冲数据页中
InnoDB判断一个行中的列是否存储在行缓冲数据页中的依据是一个页中是否只能存储一个该行数据
对于Latin1字符集下的VARCHAR类型数据，这个阈值是VARCHAR(8098)，但也仅仅是Latin1字符集。
一般情况下，数据存放在B+树的Index索引页中，发生行溢出时，存放在Uncompress BLOB Page中，

Compressed和Dynamic行记录格式

InnoDB 1.0.x版本引入了新的文件格式(file format)，也就是全新的页格式。之前使用Compact和Redundant行格式的文件格式称为Antelope，新的文件格式称为Barracuda，这种格式拥有两种新的行记录格式，Dynamic和Compressed。

新的行格式对于BLOB完全行溢出，并且在数据页中只存储20个字节的指针，没有768个前缀字节。

Compressed还可以使用zlib算法对行进行压缩。

CHAR行的存储结构

MySQL4.1之后，CHAR(N)中的N代表的不再是字节数，而是字符数，这样，CHAR字段所占用的字节数依赖于所选字符集，在某些字符集下，CHAR也有着变长字段的行为。

比如GBK类型的CHAR(2)，最少占用2个字节，最大占用4个字节。

CREATE TABLE test_vari_char(
  a CHAR(2)
) CHARSET=GBK;

INSERT INTO test_vari_char SELECT 'ab';
INSERT INTO test_vari_char SELECT '我们';
INSERT INTO test_vari_char SELECT 'a';

使用hexdump查看表结构

// 第一行
02 变长字段列表 字段长度 2字节
00
00 00 10 00 1c 
00 00 00 00 07 23
00 00 00 00 75 85
81 00 00 00 b6 01 10 
61 62 字段数据 ab

// 第二行
04 变长字段列表，字段长度，4字节
00
...
ce d2 c3 c7 字段数据 我们

// 第三行
02 变长字段列表，字段长度，2字节
00
...
61 20 字段数据a

可以看到，某些字符集下的CHAR字段被保存为变长字段，它最小长度就是指定的字符长度，如果没达到这个长度后面补空格(0x20)。除了最小长度补空格之外，在多字节可变长字符集中，CHAR和VARCHAR的行为几乎没有区别。

InnoDB数据页结构

File Header、Page Header和File Trailer是固定大小的，分别占用38、56和8字节。其它的是动态变化的。

开始之前先捋一下，首先，表空间文件中有若干页，如果一个页是数据页的话，那么它必然也是B+树的一个节点，因为InnoDB是使用B+树聚簇索引来组织表的嘛。下面的内容主要针对数据页。

File Header

文件头，记录该页和文件、文件中其他页的一些关系。占用38字节

页头，记录页本身的一些信息。占用56字节

Infimum和Supremum Records

Infimum记录比页中任何主键值都小的值。Supremum记录比页中任何主键值都大的值。

User Records和Free Space

User Records用于存储用户的行记录。

Free Space，空闲空间，很明显，User Records少了它就多，User Records多了它就少。

Page Directory

存放页中记录相对位置。

因为一个数据页就是一个B+树节点，这个节点中有很多行记录，这些行记录存到User Records中。数据库在进行磁盘读取时，需要使用B+树索引到需要的数据所在的页，现在只是知道需要的数据一定在这个页中，数据库还需要把这个页读到内存中，然后去它的User Records中查找对应的数据。怎么查找？16KB的数据顺序遍历一遍？那效率应该不太高。

Page Directory使用稀疏结构，按顺序记录页中部分行主键以及它们所在页中的相对位置，这些记录指针称作槽(slot)。这样，数据库系统在内存中可以利用Page Directory进行二分查找，能够加快处理效率。

比如块中有如下数据：

(i, d, c, b, e, g, l, h, f, j, k, a)

那么槽可能是

(a, e, i)

利用稀疏结构进行二分查找同样也只能够找到记录所在页中的大致位置，还需进一步查找。除非要查找的记录就在槽中。

File Trailer

文件尾，为了保证页被正确写入磁盘。

其中只有一个8字节的FIL_PAGE_END_LSN字段，前4字节代表页的checksum值，后4字节代表LSN。

InnoDB读取一个页时会使用checksum算法来检测文件尾，判断页是否完整。

InnoDB数据页结构分析

CREATE TABLE test_page (
  a INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  b CHAR(10)
) CHARSET=UTF8;

DELIMITER $$
CREATE PROCEDURE load_test_page (cnt INT UNSIGNED)
BEGIN
  SET @c = 0;
  WHILE @c < cnt DO
    INSERT INTO test_page
      SELECT NULL, REPEAT(CHAR(97+RAND()*26),10);
    SET @c = @c + 1;
  END WHILE;
END;
$$

DELIMITER ;

CALL load_test_page(100);

SELECT * FROM test_page LIMIT 10;

使用innodb_space，发现第4个页是数据页并且只有一个数据页，那么从16 * 1024 * 4 = 65536 = 0x00010000。

所以使用hexdump从0x00010000开始读取。也就是下图中的位置就是数据页。

先是文件头的38字节

c1 34 dd d3 checksum值
00 00 00 04 页偏移量，这正是第4个页
ff ff ff ff 因为只有一个B+树节点，所以没有
ff ff ff ff 后一个页
00 00 00 00 8b 03 be 3e LSN
45 bf 页类型 数据页
00 00 00 00 00 00 00 00 00
00 00 00 6e  SPACE ID

直接跳过56字节的Page Header，看infimum和supremum部分

# Infimum伪行记录
01 00 02 00 1c  recorder header
69 6e 66 69 6d 75 6d 00  Inifimum标志

# Supremum伪行记录
05 00 0b 00 00  recorder header
73 75 70 72 65 6d 75 6d  Supremum标志

Infimum记录头的最后两个字节代表下一个记录的偏移量。就是相对当前位置往后00 1c。

顺着向下找，找到了第一条记录

00 00 00 01    主键
00 00 00 00 75 9b   事务id
82 00 00 00 bc 01 10   回滚指针
78 78 78 78 78 78 78 78 78 78   列b中的数据

emm，我还写了个小程序来获取块中的内容，支持MySQL8.x——ibd_reader

分区表

这里的分区不是前面所说的ibd文件中的extent的概念，而是将一张表分到不同的若干个物理文件中。

MySQL的分区为水平分区，即按行进行分区，并且MySQL使用局部分区索引，即一个分区中既包含数据也包含索引。

MySQL支持四种分区

RANEG分区：行数据的某个列值属于连续区间范围内的放入一个分区中
LIST分区：RANEG的离散版本，看不懂
HASH分区：根据用户自定义的表达式来分区，看不懂
KEY分区：根据MySQL提供的哈希函数来分区

分区操作是MySQL层面的，不过也有部分存储引擎不支持分区。

如果表中存在主键或唯一索引，分区列必须是唯一索引的一部分

RANGE分区

最常用的分区类型。

下面创建了一个表，并且对这个表创建分区，如果id小于10，那么存入分区1，如果不满足第一条且id小于20，那么存入分区2。如果都不满足，那么无法插入。

CREATE TABLE test_partition(
  id INT
) PARTITION BY RANGE (id) (
  PARTITION p0 VALUES LESS THAN (10),
  PARTITION p1 VALUES LESS THAN (20)
);

可以看到分区后，InnoDB使用了两个表空间文件来存储该表中的数据。

插入几条数据，可以看到大于20的那一条插入没有成功。

INSERT INTO test_partition SELECT 5;
INSERT INTO test_partition SELECT 15;
INSERT INTO test_partition SELECT 25;

查看information_schema.PARTITIONS表可以获取每个分区的具体信息。

SELECT * FROM information_schema.PARTITIONS
WHERE table_schema='test_db' AND table_name='test_partition'\G;

maxvalue是正无穷的含义，可以这样创建一个分区

ALTER TABLE test_partition
  ADD PARTITION(
    PARTITION p2 VALUES LESS THAN MAXVALUE
  );

现在25可以插入了

RANEG分区的好处就是我们可以将一些数据分别管理，尤其是可以按照日期管理。比如可以将一个记录销售额的表sales按年份分区。这样无论是想删除销售额还是快速查询销售额，都可以使用到分区。

比如不再需要2008年的销售额，可以直接对分区进行删除

如果只想查询2008年的销售额，那么SQL优化器会直接找到那个分区，这样就节省了很多时间。这种操作叫分区修剪(Partition Pruning)。

如果编写这样的语句，则MySQL优化器会去搜索两个分区，即使条件中也指明了2009的分区中不可能有数据存在。

MySQL优化器只能对YEAR、TO_DAYS、TO_SECONDS、UNIX_TIMESTAMP这类函数进行优化选择，尽量在创建分区时使用这些函数作为条件。

LIST分区

离散版本的RANGE

分区和性能

一般对于OLAP应用来说，它们需要查询分析一张表中的大部分数据，所以分区很容易带来性能优化。而对于OLTP应用来说，经常返回大表中的一两条数据，这时B+树索引就可以工作的很好了，所以通常对于OLTP应用来说，分区没办法带来什么性能优化，甚至是反优化。

posted @ 2021-11-24 16:40 yudoge 阅读(260) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Loading

yudoge