程序员Wiki

InnoDB页：将数据划分为若干个页page，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

# InnoDB行格式

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。

InnoDB

记录的额外信息： 分别是变长字段长度列表、NULL值列表和记录头信息

【1】变长字段长度列表： MySQL中比如VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型这些数据类型的列称为变长字段，变长字段中存储多少字节的数据是不固定的。所以这些变长字段占用的存储空间分为两部分：真正的数据内容、占用的字节数。

在行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放 。变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的 。并不是所有记录都有这个变长字段长度列表部分，比方说表中所有的列都不是变长的数据类型的话，这一部分就不需要有。

【2】NULL值列表： 表中的某些列可能存储NULL值，如果把这些 NULL值都放到记录的真实数据中存储会很占地方，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中，它的处理过程是这样的：
 ① 首先统计表中允许存储 NULL的列有哪些；
 ② 如果表中没有允许存储 NULL 的列，则NULL值列表也不存在了；

【3】记录头信息

InnoDB 
#先创建一个表：
mysql> CREATE TABLE page_demo(
    ->     c1 INT,
    ->     c2 INT,
    ->     c3 VARCHAR(10000),
    ->     PRIMARY KEY (c1)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.03 sec)
1
2
3
4
5
6
7
8

# 主键的生成策略

优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中也没有定义Unique键的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。

这个新创建的page_demo表有3个列，其中 c1和 c2列是用来存储整数的，c3列是用来存储字符串的。需要注意的是，我们把 c1 列指定为主键，所以在具体的行格式中InnoDB就没必要为我们去创建那个所谓的row_id隐藏列了。简化后的行格式示意图就是这样：

InnoDB

# InnoDB数据页结构

数据页代表的这块16KB大小的存储空间可以被划分为多个部分，不同部分有不同的功能，各个部分如图所示：

InnoDB
InnoDB

每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：

InnoDB

这里我们继续上面的 page_demo例子：

#先创建一个表：
mysql> CREATE TABLE page_demo(
    ->     c1 INT,
    ->     c2 INT,
    ->     c3 VARCHAR(10000),
    ->     PRIMARY KEY (c1)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.03 sec)

#向page_demo表中插入几条记录：
mysql> INSERT INTO page_demo VALUES(1, 100, 'aaaa'),
                                      (2, 200, 'bbbb'),
                                  (3, 300, 'cccc'),
                                      (4, 400, 'dddd');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

那么，这些记录的示意图就是：

InnoDB

【1】delete_mask：这个属性标记着当前记录是否被删除。这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已。所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。
【2】min_rec_mask：B+树的每层非叶子节点中的最小记录都会添加该标记，min_rec_mask值都是0，意味着它们都不是B+树的非叶子节点中的最小记录。
【3】n_owned：在页目录分组时使用，每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。
【4】heap_no：这个属性表示当前记录在本页中的位置，从图中可以看出来，我们插入的4条记录在本页中的位置分别是：2、3、4、5。heap_no值为0和1的记录，称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录，一个代表最大记录。

InnoDB

【5】record_type：这个属性表示当前记录的类型，一共有4种类型的记录，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录。
【6】next_record：它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。比方说第一条记录的 next_record值为32，意味着从第一条记录的真实数据的地址处向后找 32个字节便是下一条记录的真实数据。下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定 Infimum记录（也就是最小记录） 的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）

InnoDB

从图中可以看出来，我们的记录按照主键从小到大的顺序形成了一个单链表。如果从中删除掉一条记录，这个链表也是会跟着变化的，比如我们把第2条记录删掉：

mysql> DELETE FROM page_demo WHERE c1 = 2;
Query OK, 1 row affected (0.02 sec)
1
2
InnoDB

第2条记录并没有从存储空间中移除，而是把该条记录的delete_mask值设置为1。
第2条记录的 next_record值变为了0，意味着该记录没有下一条记录了。
第1条记录的 next_record指向了第3条记录。
还有一点你可能忽略了，就是最大记录的 n_owned值从5变成了4。
因为主键值为2的记录被我们删掉了，但是存储空间却没有回收，如果我们再次把这条记录插入到表中：

mysql> INSERT INTO page_demo VALUES(2, 200, 'bbbb');
Query OK, 1 row affected (0.00 sec)
1
2
InnoDB

# Page Directory（页目录）

现在我们了解了记录在页中按照主键值由小到大顺序串联成一个单链表，那如果我们想根据主键值查找页中的某条记录该咋办呢？设计 InnoDB的大叔们为我们的记录也制作了一个类似的目录，他们的制作过程是这样的：
【1】将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。
【2】每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的 n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。
【3】将每个组的最后一条记录的地址偏移量单独提取出来，用作查找。

注意：这个页目录是为主键服务的。

InnoDB

对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1-8 条之间，剩下的分组中记录的条数范围只能在是 4-8 条之间。分组是按照下边的步骤进行：
【1】初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
【2】之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。
【3】在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。

我们再添加 12条记录看看效果：

mysql> INSERT INTO page_demo VALUES(5, 500, 'eeee'), (6, 600, 'ffff'), (7, 700, 'gggg'), (8, 800, 'hhhh'), (9, 900, 'iiii'), (10, 1000, 'jjjj'), (11, 1100, 'kkkk'), (12, 1200, 'llll'), (13, 1300, 'mmmm'), (14, 1400, 'nnnn'), (15, 1500, 'oooo'), (16, 1600, 'pppp');
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0
1
2
3
InnoDB

因为把16条记录的全部信息都画在一张图里太占地方，让人眼花缭乱的，所以只保留了用户记录头信息中的 n_owned和next_record属性。因为各个槽代表的记录的主键值都是从小到大排序的，所以我们可以使用所谓的二分法来进行快速查找。所以在一个数据页中查找指定主键值的记录的过程分为两步：
【1】通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最大的那条记录。
【2】通过记录的 next_record属性遍历该槽所在的组中的各个记录。
比方说我们想找主键值为6的记录，过程是这样的：计算中间槽的位置：(0+4)/2=2，所以查看槽2对应记录的主键值为8，又因为8 > 6，所以设置high=2，low保持不变。重新计算中间槽的位置：(0+2)/2=1，所以查看槽1对应的主键值为4，又因为4 < 6，所以设置low=1，high保持不变。因为high – low的值为1，所以确定主键值为 6的记录在槽2对应的组中。我们可以很轻易的拿到槽1对应的记录（主键值为4），该条记录的下一条记录就是槽2中主键值最小的记录，该记录的主键值为5。所以我们可以从这条主键值为5的记录出发，遍历槽2中的各条记录。注意：若查到数据在槽2的分组中，由于槽2是指向最后一个记录，所以需要向上找一个槽位，定位到上一个槽位最后一行，然后再向下找。

# File Header（文件头部）

File Header针对各种类型的页都通用，也就是说不同类型的页都会以 File Header作为第一个组成部分，它描述了一些针对各种页都通用的一些信息，比方说这个页的编号是多少，它的上一个页、下一个页是谁等。

FIL_PAGE_OFFSET：每一个页都有一个单独的页号，就跟你的身份证号码一样，InnoDB通过页号来可以唯一定位一个页。

FIL_PAGE_PREV和FIL_PAGE_NEXT：FIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号。这样通过建立一个双向链表把许许多多的页就都串联起来了

InnoDB

# 索引的代价

【空间上的代价】： 每建立一个索引都要为它建立一棵 B+树，每一棵 B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间。
【时间上的代价】： 每次对表中的数据进行增、删、改操作时，都需要去修改各个 B+树索引。

B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收啥的操作来维护好节点和记录的排序。

# B+树索引适用的条件

首先，B+树索引并不是万能的，并不是所有的查询语句都能用到我们建立的索引。下边介绍几个我们可能使用 B+树索引来进行查询的情况。

CREATE TABLE person_info(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);
1
2
3
4
5
6
7
8
9

person_info表会为聚簇索引和idx_name_birthday_phone_number索引建立 2棵B+树。在记录结构中只保留name、birthday、phone_number、id这四个列的真实数据值，所以示意图就长这样：

InnoDB

内节点中存储的是目录项记录，叶子节点中存储的是用户记录（由于不是聚簇索引，所以用户记录是不完整的，缺少 country列的值）。
【1】先按照name列的值进行排序。
【2】如果name列的值相同，则按照birthday列的值进行排序。
【3】如果birthday列的值也相同，则按照phone_number的值进行排序。

# 全值匹配

如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，比方说下边这个查找语句：

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';
1

【1】因为B+树的数据页和记录先是按照name列的值进行排序的，所以先可以很快定位name列的值是Ashburn的记录位置。
【2】在name列相同的记录里又是按照birthday列的值进行排序的，所以在name列的值是Ashburn的记录里又可以快速定位birthday列的值是 ’1990-09-27’的记录。
【3】如果很不幸，name和birthday列的值都是相同的，那记录是按照phone_number列的值排序的，所以联合索引中的三个列都可能被用到。

调换name、birthday、phone_number这几个搜索列的顺序对查询的执行过程是没有影响的。

# 匹配左边的列

SELECT * FROM person_info WHERE name = 'Ashburn';
1

或者包含多个左边的列也行：

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';
1

只有左边的列才能匹配，下边的语句就用不到这个B+树索引：

SELECT * FROM person_info WHERE birthday = '1990-09-27';
1

因为B+树的数据页和记录先是按照name列的值排序的，在name列的值相同的情况下才使用birthday列进行排序，也就是说name列的值不同的记录中birthday的值可能是无序的。如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列。

# 匹配列前缀

person_info表上建立的联合索引idx_name_birthday_phone_number会先用name列的值进行排序。也就是说这些字符串的前n个字符，也就是前缀都是排好序的，所以对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的，比方说我们想查询名字以’As’开头的记录，那就可以这么写查询语句：

SELECT * FROM person_info WHERE name LIKE 'As%';
1

由于所有记录都是由链表连起来的（记录之间用单链表，数据页之间用双链表），所以他们之间的记录都可以很容易的取出来。找到这些记录的主键值，再到聚簇索引中回表查找完整的记录。如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到 B+树索引，比方说这样：

SELECT * FROM person_info WHERE name > ‘Asa’ AND name < ‘Barlow’ AND birthday > '1980-01-01';
1

上边这个查询可以分成两个部分：
【1】通过条件name > ‘Asa’ AND name < ‘Barlow’来对name进行范围。
【2】对这些name值不同的记录继续通过birthday > ‘1980-01-01’条件继续过滤。

对于联合索引idx_name_birthday_phone_number来说，只能用到name列的部分，而用不到birthday列的部分，因为只有name值相同的情况下才能用birthday列的值进行排序，而这个查询中通过name进行范围查找的记录中可能并不是按照birthday列进行排序的，所以在搜索条件中继续以birthday列进行查找时是用不到这个B+树索引的。

# 精确匹配某一列并范围匹配另外一列

虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找，比方说这样：

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';
1

由于name列是精确查找，所以通过name = ‘Ashburn’条件查找后得到的结果的name值都是相同的，它们会再按照birthday的值进行排序。所以此时对birthday列进行范围查找是可以用到B+树索引的。phone_number > ‘15100000000’，通过birthday的范围查找的记录的birthday的值可能不同，所以这个条件无法再利用B+树索引了，只能遍历上一步查询得到的记录。

# 用于排序

有的时候可能查询的结果集太大以至于不能在内存中进行排序的话，还可能暂时借助磁盘的空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端。在MySQL中，把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort）。

但是如果ORDER BY子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤，比如下边这个简单的查询语句：

SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;
1

因为这个B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。

# 使用联合索引进行排序注意事项

ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出 ORDER BY phone_number, birthday, name的顺序，那也是用不了B+树索引。

# 不可以使用索引进行排序的几种情况

【1】ASC、DESC混用： 对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。
【2】WHERE 子句中出现非排序使用到的索引列： 如果WHERE子句中出现了非排序使用到的索引列，那么排序依然是使用不到索引的，比方说这样：

SELECT * FROM person_info WHERE country = ‘China’ ORDER BY name LIMIT 10;
1

这个查询只能先把符合搜索条件country = ‘China’的记录提取出来后再进行排序，是使用不到索引。

【3】排序列包含非同一个索引的列： 有时候用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序，比方说：

SELECT * FROM person_info ORDER BY name, country LIMIT 10;
1

【4】排序列使用了复杂的表达式： 要想使用索引进行排序操作，必须保证索引列是以单独列的形式出现，而不是修饰过的形式，比方说这样：

SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;
1
(adsbygoogle = window.adsbygoogle || []).push({});

← MySQL 事务 →