《从根儿上理解MySQL》读书笔记(二)

第六章 快速查询的秘籍-B+树索引

InnoDB数据页的7个组成部分，各个数据页可以组成一个双向链表，而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。页和记录的关系示意图如下：

6.1 没有索引的查找

假设现在有一个精准匹配的sql语句：

SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

6.1.1 在一个页中的查找

假设目前表中的记录比较少，所有的记录都可以被存放到一个页中，在查找记录的时候可以根据搜索条件的不同分为两种情况：

  1. 以主键为搜索条件
    1. 可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
  2. 以其他列作为搜索条件
    1. 对非主键列的查找的过程来说，因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

6.1.2 在很多页中查找

大部分情况下我们表中存放的记录都是非常多的，需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤：

  1. 定位到记录所在的页。
  2. 从所在的页内中查找相应的记录。

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们刚刚介绍过的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的。

6.2 索引

首先创建一个表：

mysql> CREATE TABLE index_demo(
    ->     c1 INT,
    ->     c2 INT,
    ->     c3 CHAR(1),
    ->     PRIMARY KEY(c1)
    -> ) ROW_FORMAT = Compact;
Query OK, 0 rows affected (0.03 sec)

这个新建的index_demo表中有2个INT类型的列，1个CHAR(1)类型的列，而且我们规定了c1列为主键，这个表使用Compact行格式来实际存储记录的。简化后的行格式示意图如下：

  1. record_type：记录头信息的一项属性，表示记录的类型，0表示普通记录、2表示最小记录、3表示最大记录、1表示目录项记录。
  2. next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量。
  3. 各个列的值：这里只记录在index_demo表中的三个列，分别是c1、c2和c3。
  4. 其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

把一些记录放到页里边的示意图就是

6.2.1 一个简单的索引方案

我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该咋办？还记得我们为根据主键值快速定位一条记录在页中的位置而设立的页目录么？我们也可以想办法为快速定位记录所在的数据页而建立一个别的目录，建这个目录必须完成下面这些事儿：

  1. 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
    根据上面的示意图，现在假设页10最多只能放3条记录，如果已经存放3条纪录，此时我们再插入一条，我们不得不再分配一个新页：

新分配的数据页编号可能并不是连续的，也就是说我们使用的这些页在存储空间里可能并不挨着。它们只是通过维护着上一个页和下一个页的编号而建立了链表关系。另外，页10中用户记录最大的主键值是5，而页28中有一条记录的主键值是4，因为5 > 4，所以这就不符合 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，所以在插入主键值为4的记录的时候需要伴随着一次记录移动，也就是把主键值为5的记录移动到页28中，然后再把主键值为4的记录插入到页10中，这个过程的步骤如下：

1. 将主键值为5的纪录移动到页28
2. 将主键值为4的纪录插入到页10

这个过程表明了在对页中的记录进行增删改操作的过程中，我们必须通过一些诸如记录移动的操作来始终保证这个状态一直成立：下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。这个过程我们也可以称为页分裂。

  1. 给所有的页建立一个目录项。

因为这些16KB的页在物理存储上可能并不挨着，所以如果想从这么多页中根据主键值快速定位某些记录所在的页，我们需要给它们做个目录，每个页对应一个目录项，每个目录项包括下面两个部分：

  1. 页的用户记录中最小的主键值，我们用key来表示。
  2. 页号，我们用page_no表示。

以页28为例，它对应目录项2，这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值5。我们只需要把几个目录项在物理存储器上连续存储，比如把他们放到一个数组里，就可以实现根据主键值快速查找某条记录的功能了。比方说我们想找主键值为20的记录，具体查找过程分两步：

1. 先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中（因为 12 < 20 < 209），它对应的页是页9。
2. 再根据前面说的在页中查找记录的方式去页9中定位具体的记录。

针对数据页做的简易目录就搞定了。不过忘了说了，这个目录有一个别名，称为索引。

6.3 InnoDB中的索引方案

上面之所以称为一个简易的索引方案，是因为我们为了在根据主键值进行查找时使用二分法快速定位具体的目录项而假设所有目录项都可以在物理存储器上连续存储，但是这样做有几个问题：

  1. InnoDB是使用页来作为管理存储空间的基本单位，也就是最多能保证16KB的连续存储空间，而随着表中记录数量的增多，需要非常大的连续的存储空间才能把所有的目录项都放下，这对记录数量非常多的表是不现实的。
  2. 我们时常会对记录进行增删，假设我们把页28中的记录都删除了，页28也就没有存在的必要了，那意味着目录项2也就没有存在的必要了，这就需要把目录项2后的目录项都向前移动一下，这种牵一发而动全身的设计不是什么好主意～

所以，设计InnoDB的大佬们需要一种可以灵活管理所有目录项的方式。他们灵光乍现，忽然发现这些目录项其实长得跟我们的用户记录差不多，只不过目录项中的两个列是主键和页号而已，所以他们复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为目录项记录。那InnoDB怎么区分一条记录是普通的用户记录还是目录项记录呢？别忘了记录头信息里的record_type属性，它的各个取值代表的意思如下：

0：普通的用户记录
1：目录项记录
2：最小记录
3：最大记录

我们把前面使用到的目录项放到数据页中的样子就是这样：

从图中可以看出来，我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调一遍目录项记录和普通的用户记录的不同点：

1. 目录项记录的record_type值是1，而普通用户记录的record_type值是0。
2. 目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列。
3. 还记得我们之前在介绍记录头信息的时候说过一个叫min_rec_mask的属性么，只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1，其他别的记录的min_rec_mask值都是0。

现在以查找主键为20的记录为例，根据某个主键值去查找记录的步骤就可以大致拆分成下面两步：

1. 先到存储目录项记录的页，也就是页30中通过二分法快速定位到对应目录项，因为12 < 20 < 209，所以定位到对应的记录所在的页就是页9。
2. 再到存储用户记录的页9中根据二分法快速定位到主键值为20的用户记录。

虽然说目录项记录中只存储主键值和对应的页号，比用户记录需要的存储空间小多了，但是不论怎么说一个页只有16KB大小，能存放的目录项记录也是有限的，那如果表中的数据太多，以至于一个数据页不足以存放所有的目录项记录，该咋办呢？

当然是再多整一个存储目录项记录的页。

在这个查询步骤我们需要定位存储目录项记录的页，但是这些页在存储空间中也可能不挨着，如果我们表中的数据非常多则会产生很多存储目录项记录的页，那我们怎么根据主键值快速定位一个存储目录项记录的页呢？其实也简单，为这些存储目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据，所以现在各个页的示意图就是这样子：

如图，我们生成了一个存储更高级目录项的页33，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在[1, 320)之间，则到页30中查找更详细的目录项记录，如果主键值不小于320的话，就到页32中查找更详细的目录项记录。

当结构再深一点，看起来就会像一棵树，或者说是一种数据结构，它的名称是B+树。

不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到B+树这个数据结构中了，所以我们也称这些数据页为节点。从图中可以看出来，我们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上面的那个节点也称为根节点。

一个B+树的节点其实可以分成好多层，InnoDB规定最下面的那层，也就是存放我们用户记录的那层为第0层，之后依次往上加。

6.3.1 聚簇索引

我们上面介绍的B+树本身就是一个目录，或者说本身就是一个索引。它有两个特点：

  1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

    • 页内的记录是按照主键的大小顺序排成一个单向链表。
    • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
    • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

  2. B+树的叶子节点存储的是完整的用户记录
    所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建，InnoDB存储引擎会自动的为我们创建聚簇索引。在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

6.3.2 二级索引

聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该咋办呢？难道只能从头到尾沿着链表依次遍历记录么？

我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则，再建一棵B+树，效果如下图所示：

这个B+树与上面介绍的聚簇索引有几处不同：

  1. 使用记录c2列的大小进行记录和页的排序，这包括三个方面的含义：
    1. 页内的记录是按照c2列的大小顺序排成一个单向链表。
    2. 各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表。
    3. 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表。
  2. B+树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。
  3. 目录项记录中不再是主键+页号的搭配，而变成了c2列+页号的搭配。

所以如果我们现在想通过c2列的值查找某些记录的话就可以使用我们刚刚建好的这个B+树了。以查找c2列的值为4的记录为例，查找过程如下：

  1. 确定目录项记录页
    根据根页面，也就是页44，可以快速定位到目录项记录所在的页为页42（因为2 < 4 < 9）。
  2. 通过目录项记录页确定用户记录真实所在的页。
    在页42中可以快速定位到实际存储用户记录的页，但是由于c2列并没有唯一性约束，所以c2列值为4的记录可能分布在多个数据页中，又因为2 < 4 ≤ 4，所以确定实际存储用户记录的页在页34和页35中。
  3. 在真实存储用户记录的页中定位到具体的记录。
    到页34和页35中定位到具体的记录。
  4. 但是这个B+树的叶子节点中的记录只存储了c2和c1（也就是主键）两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。

我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程也被称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2棵B+树。

这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引（英文名secondary index），或者辅助索引。由于我们使用的是c2列的大小作为B+树的排序规则，所以我们也称这个B+树为为c2列建立的索引。

6.3.3 联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层含义：

  1. 先把各个记录和页按照c2列进行排序。
  2. 在记录的c2列相同的情况下，采用c3列进行排序

为c2和c3列建立的索引的示意图如下：

  1. 每条目录项记录都由c2、c3、页号这三个部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序。
  2. B+树叶子节点处的用户记录由c2、c3和主键c1列组成。

    千万要注意一点，以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的

6.4 InnoDB的B+树索引的注意事项

6.4.1 B+树形成规则

  1. 每当为某个表创建一个B+树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
  2. 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
  3. 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

6.4.2 内节点中目录项记录的唯一性

为了让新插入记录能找到自己在那个页里，我们需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

  1. 索引列的值
  2. 主键值
  3. 页号

也就是我们把主键值也添加到二级索引内节点中的目录项记录了，这样就能保证B+树每一层节点中各条目录项记录除页号这个字段外是唯一的。

6.4.3 一个页面最少存储2条记录

一个B+树只需要很少的层级就可以轻松存储数亿条记录，查询速度杠杠的！这是因为B+树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉许多无效的子目录，直到最后访问到存储真实数据的目录。那如果一个大的目录中只存放一个子目录是什么效果呢？那就是目录层级非常非常非常多，而且最后的那个存放真实数据的目录中只能存放一条记录。费了半天劲只能存放一条真实的用户记录？

InnoDB的一个数据页至少可以存放两条记录

6.5 MyISAM中的索引方案简单介绍

我们知道InnoDB中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储：

  1. 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号而快速访问到一条记录。MyISAM记录也需要记录头信息来存储一些额外数据。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。

  2. 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录。

     这一点和InnoDB是完全不相同的，在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引！

  3. 如果有需要的话，我们也可以对其它的列分别建立索引或者建立联合索引，原理和InnoDB中的索引差不多，不过在叶子节点处存储的是相应的列 + 行号。这些索引也全部都是二级索引。

6.6 MySQL中创建和删除索引的语句

InnoDB和MyISAM会自动为主键或者声明为UNIQUE的列去自动建立B+树索引，但是如果我们想为其他的列建立索引就需要我们显式的去指明。
我们可以在创建表的时候指定需要建立索引的单个列或者建立联合索引的多个列：

CREATE TALBE 表名 (
    各种列的信息 ··· , 
    [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)

建议以idx_为前缀，后边跟着需要建立索引的列名，多个列名之间用下划线_分隔开。
其中的KEY和INDEX是同义词，任意选用一个就可以。我们也可以在修改表结构的时候添加索引：

ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);

也可以在修改表结构的时候删除索引：

ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

第七章 学会怎么用-B+树索引的使用

  1. 每个索引都对应一棵B+树，B+树分为好多层，最下面一层是叶子节点，其余的是内节点。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。

  2. InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。

  3. 我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。

  4. B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前面的列排序，如果该列值相同，再按照联合索引后边的列排序。

  5. 通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快。

7.1 索引的代价

虽然索引是个好东西，可不能乱建，它在空间和时间上都会拖后腿：

  • 空间上的代价
    • 每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那可是很大的一片存储空间呢。
  • 时间上的代价
    • 每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收什么的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作

所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。

7.2 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. 表中的主键是id列，它存储一个自动递增的整数。所以InnoDB存储引擎会自动为id列建立聚簇索引。
  2. 我们额外定义了一个二级索引idx_name_birthday_phone_number，它是由3个列组成的联合索引。所以在这个索引对应的B+树的叶子节点处存储的用户记录只保留name、birthday、phone_number这三个列的值以及主键id的值，并不会保存country列的值。

idx_name_birthday_phone_number的示意图如下：

从图中可以看出，这个idx_name_birthday_phone_number索引对应的B+树中页面和记录的排序方式就是这样的：

  1. 先按照name列的值进行排序。
  2. 如果name列的值相同，则按照birthday列的值进行排序。
  3. 如果birthday列的值也相同，则按照phone_number的值进行排序。
    这个排序方式十分重要，因为只要页面和记录是排好序的，我们就可以通过二分法来快速定位查找。

7.2.1 全值匹配

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

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

WHERE子句中的几个搜索条件的顺序对查询结果没有影响。MySQL有一个叫查询优化器的东东，会分析这些搜索条件并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件，后使用哪个搜索条件。

7.2.2 匹配左边的列

其实在我们的搜索语句中也可以不用包含全部联合索引中的列，只包含左边的就行或者包含多个左边的列也行，比方说下面的查询语句：

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

那为什么搜索条件中必须出现左边的列才可以使用到这个B+树索引呢？比如下面的语句就用不到这个B+树索引么？

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

是的，的确用不到，因为B+树的数据页和记录先是按照name列的值排序的，在name列的值相同的情况下才使用birthday列进行排序，也就是说name列的值不同的记录中birthday的值可能是无序的。而现在你跳过name列直接根据birthday的值去查找，臣妾做不到呀～ 那如果我就想在只使用birthday的值去通过B+树索引进行查找咋办呢？这好办，你再对birthday列建一个B+树索引就行了。

但是需要特别注意的一点是，如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列。比方说联合索引idx_name_birthday_phone_number中列的定义顺序是name、birthday、phone_number，如果我们的搜索条件中只有name和phone_number，而没有中间的birthday。

这样只能用到name列的索引，birthday和phone_number的索引就用不上了，因为name值相同的记录先按照birthday的值进行排序，birthday值相同的记录才按照phone_number值进行排序。

7.2.3 匹配列前缀

某个列建立索引的意思其实就是在对应的B+树的记录中使用该列的值进行排序，比方说person_info表上建立的联合索引idx_name_birthday_phone_number会先用name列的值进行排序，所以这个联合索引对应的B+树中的记录的name列的排列就是这样的：

Aaron
Aaron
...
Aaron
Asa
Ashburn
...
Ashburn
Baird
Barlow
...
Barlow

字符串排序的本质就是比较哪个字符串大一点儿，哪个字符串小一点，比较字符串大小就用到了该列的字符集和比较规则,一般的比较规则都是逐个比较字符的大小，也就是说我们比较两个字符串的大小的过程其实是这样的：

  1. 先比较字符串的第一个字符，第一个字符小的那个字符串就比较小。
  2. 如果两个字符串的第一个字符相同，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小。
  3. 如果两个字符串的第二个字符也相同，那就接着比较第三个字符，依此类推。

所以一个排好序的字符串列其实有这样的特点：

  1. 先按照字符串的第一个字符进行排序。
  2. 如果第一个字符相同再按照第二个字符进行排序。
  3. 如果第二个字符相同再按照第三个字符进行排序，依此类推。

也就是说这些字符串的前n个字符，也就是前缀都是排好序的，所以对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的，比方说我们想查询名字以'As'开头的记录，那就可以这么写查询语句：

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

但是需要注意的是，如果只给出后缀或者中间的某个字符串，比如这样：

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

MySQL就无法快速定位记录位置了，因为字符串中间有'As'的字符串并没有排好序，所以只能全表扫描了。有时候我们有一些匹配某些字符串后缀的需求，比方说某个表有一个url列，该列中存储了许多url：

+----------------+
| url            |
+----------------+
| www.baidu.com  |
| www.google.com |
| www.gov.cn     |
| ...            |
| www.wto.org    |
+----------------+

假设已经对该url列创建了索引，如果我们想查询以com为后缀的网址的话可以这样写查询条件：WHERE url LIKE '%com'，但是这样的话无法使用该url列的索引。为了在查询时用到这个索引而不至于全表扫描，我们可以把后缀查询改写成前缀查询，不过我们就得把表中的数据全部逆序存储一下，也就是说我们可以这样保存url列中的数据：

+----------------+
| url            |
+----------------+
| moc.udiab.www  |
| moc.elgoog.www |
| nc.vog.www     |
| ...            |
| gro.otw.www    |
+----------------+

这样再查找以com为后缀的网址时搜索条件便可以这么写：WHERE url LIKE 'moc%'，这样就可以用到索引了。

7.2.4 匹配范围值

回头看我们idx_name_birthday_phone_number索引的B+树示意图，所有记录都是按照索引列的值从小到大的顺序排好序的，所以这极大的方便我们查找索引列的值在某个范围内的记录。比方说下面这个查询语句：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

由于B+树中的数据页和记录是先按name列排序的，所以我们上面的查询过程其实是这样的：

  1. 找到name值为Asa的记录。
  2. 找到name值为Barlow的记录。
  3. 取出中间值。
  4. 找到这些记录的主键值，再到聚簇索引中回表查找完整的记录。

不过在使用联合进行范围查找的时候需要注意，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引，比方说这样：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';
  1. 通过条件name > 'Asa' AND name < 'Barlow' 来对name进行范围，查找的结果可能有多条name值不同的记录，
  2. 对这些name值不同的记录继续通过birthday > '1980-01-01'条件继续过滤。

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

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

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

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';
  1. name = 'Ashburn'，对name列进行精确查找，当然可以使用B+树索引了。
  2. birthday > '1980-01-01' AND birthday < '2000-12-31'，由于name列是精确查找，所以通过name = 'Ashburn'条件查找后得到的结果的name值都是相同的，它们会再按照birthday的值进行排序。所以此时对birthday列进行范围查找是可以用到B+树索引的。
  3. phone_number > '15100000000'，通过birthday的范围查找的记录的birthday的值可能不同，所以这个条件无法再利用B+树索引了，只能遍历上一步查询得到的记录。

7.2.6 用于排序

我们在写查询语句的时候经常需要对查询出来的记录通过ORDER BY子句按照某种规则进行排序。一般情况下，我们只能把记录都加载到内存中，再用一些排序算法，比如快速排序、归并排序、等等排序等等在内存中对这些记录进行排序，有的时候可能查询的结果集太大以至于不能在内存中进行排序的话，还可能暂时借助磁盘的空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端。

在MySQL中，把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort），跟文件这个词儿一沾边儿，就显得这些排序操作非常慢了。但是如果ORDER BY子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤，比如下面这个简单的查询语句：

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

这个查询的结果集需要先按照name值排序，如果记录的name值相同，则需要按照birthday来排序，如果birthday的值相同，则需要按照phone_number排序。大家可以回过头去看我们建立的idx_name_birthday_phone_number索引的示意图，因为这个B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。

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

对于联合索引有个问题需要注意，ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出ORDER BY phone_number, birthday, name的顺序，那也是用不了B+树索引，这种颠倒顺序就不能使用索引的原因我们上面详细说过了，这就不赘述了。

同理，ORDER BY name、ORDER BY name, birthday这种匹配索引左边的列的形式可以使用部分的B+树索引。当联合索引左边列的值为常量，也可以使用后边的列进行排序，比如这样：

SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;

这个查询能使用联合索引进行排序是因为name列的值相同的记录是按照birthday, phone_number排序的。

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

ASC、DESC混用
对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。

ORDER BY子句后的列如果不加ASC或者DESC默认是按照ASC排序规则排序的，也就是升序排序的。

为什么会有这种奇葩规定呢？这个还得回头想想这个idx_name_birthday_phone_number联合索引中记录的结构：

  1. 先按照记录的name列的值进行升序排列。
  2. 如果记录的name列的值相同，再按照birthday列的值进行升序排列。
  3. 如果记录的birthday列的值相同，再按照phone_number列的值进行升序排列。
    如果查询中的各个排序列的排序顺序是一致的，比方说下面这两种情况：
    • ORDER BY name, birthday LIMIT 10，这种情况直接从索引的最左边开始往右读10行记录就可以了。
    • ORDER BY name DESC, birthday DESC LIMIT 10，这种情况直接从索引的最右边开始往左读10行记录就可以了。
      但是如果我们查询的需求是先按照name列进行升序排列，再按照birthday列进行降序排列的话，比如说这样的查询语句：
    SELECT * FROM person_info ORDER BY name, birthday DESC LIMIT 10;
    这样如果使用索引排序的话过程就是这样的：
    1. 先从索引的最左边确定name列最小的值，然后找到name列等于该值的所有记录，然后从name列等于该值的最右边的那条记录开始往左找10条记录。
    2. 先从索引的最左边确定name列最小的值，然后找到name列等于该值的所有记录，然后从name列等于该值的最右边的那条记录开始往左找10条记录。

累不累？累！重点是这样不能高效使用索引，而要采取更复杂的算法去从索引中取数据，设计MySQL的大佬觉得这样还不如直接文件排序来的快，所以就规定使用联合索引的各个排序列的排序顺序必须是一致的。

WHERE子句中出现非排序使用到的索引列
如果WHERE子句中出现了非排序使用到的索引列，那么排序依然是使用不到索引的，比方说这样：

SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10;

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

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

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

name和country并不属于一个联合索引中的列，所以无法使用索引进行排序。

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

SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;

使用了UPPER函数修饰过的列就不是单独的列啦，这样就无法使用索引进行排序啦。

7.2.7 用于分组

有时候我们为了方便统计表中的一些信息，会把表中的记录按照某些列进行分组。比如下面这个分组查询：

SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

这个查询语句相当于做了3次分组操作：

  1. 先把记录按照name值进行分组，所有name值相同的记录划分为一组。
  2. 将每个name值相同的分组里的记录再按照birthday的值进行分组，将birthday值相同的记录放到一个小分组里，所以看起来就像在一个大分组里又化分了好多小分组。
  3. 再将上一步中产生的小分组按照phone_number的值分成更小的分组，所以整体上看起来就像是先把记录分成一个大分组，然后把大分组分成若干个小分组，然后把若干个小分组再细分成更多的小小分组。

然后针对那些小小分组进行统计，比如在我们这个查询语句中就是统计每个小小分组包含的记录条数。如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有了索引的话，恰巧这个分组顺序又和我们的B+树中的索引列的顺序是一致的，而我们的B+树索引又是按照索引列排好序的，这不正好么，所以可以直接使用B+树索引进行分组。

和使用B+树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边的列进行分组。

7.3 回表的代价

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

在使用idx_name_birthday_phone_number索引进行查询时大致可以分为这两个步骤：

  1. 从索引idx_name_birthday_phone_number对应的B+树中取出name值在Asa～Barlow之间的用户记录。
  2. 由于索引idx_name_birthday_phone_number对应的B+树用户记录中只包含name、birthday、phone_number、id这4个字段，而查询列表是*，意味着要查询表中所有字段，也就是还要包括country字段。这时需要把从上一步中获取到的每一条记录的id字段都到聚簇索引对应的B+树中找到完整的用户记录，也就是我们通常所说的回表，然后把完整的用户记录返回给查询用户。

由于索引idx_name_birthday_phone_number对应的B+树中的记录首先会按照name列的值进行排序，所以值在Asa～Barlow之间的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，我们可以很快的把这些连着的记录从磁盘中读出来，这种读取方式我们也可以称为顺序I/O。根据第1步中获取到的记录的id字段的值可能并不相连，而在聚簇索引中记录是根据id（也就是主键）的顺序排列的，所以根据这些并不连续的id值到聚簇索引中访问完整的用户记录可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数据页，这种读取方式我们也可以称为随机I/O。一般情况下，顺序I/O比随机I/O的性能高很多，所以步骤1的执行可能很快，而步骤2就慢一些。所以这个使用索引idx_name_birthday_phone_number的查询有这么两个特点：

  1. 会使用到两个B+树索引，一个二级索引，一个聚簇索引。
  2. 访问二级索引使用顺序I/O，访问聚簇索引使用随机I/O。

需要回表的记录越多，使用二级索引的性能就越低。

那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方式去执行查询呢？这个就是传说中的查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。

7.3.2 覆盖索引

为了彻底告别回表操作带来的性能损耗，我们建议：最好在查询列表里只包含索引列，比如这样：

SELECT name, birthday, phone_number FROM person_info  
    WHERE name > 'Asa' AND name < 'Barlow';

因为我们只查询name, birthday, phone_number这三个索引列的值，所以在通过idx_name_birthday_phone_number索引得到结果后就不必到聚簇索引中再查找记录的剩余列，也就是country列的值了，这样就省去了回表操作带来的性能损耗。我们把这种只需要用到索引的查询方式称为索引覆盖。

7.4 如何挑选索引

7.4.1 只为用于搜索、排序或分组的列创建索引

只为出现在WHERE子句中的列、连接子句中的连接列，或者出现在ORDER BY或GROUP BY子句中的列创建索引。而出现在查询列表中的列就没必要建立索引了：

SELECT birthday, country FROM person_name WHERE name = 'Ashburn';

像查询列表中的birthday、country这两个列就不需要建立索引，我们只需要为出现在WHERE子句中的name列创建索引就可以了。

7.4.2 考虑列的基数

列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2, 5, 8, 2, 5, 8, 2, 5, 8，虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。

假设某个列的基数为1，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，因为所有值都一样就无法排序，无法进行快速查找了～ 而且如果某个建立了二级索引的列的重复值特别多，那么使用这个二级索引查出的记录还可能要做回表操作，这样性能损耗就更大了。所以结论就是：最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

7.4.3 索引列的类型尽量小

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT这么几种，它们占用的存储空间依次递增，我们这里所说的类型大小指的就是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT

  • 数据类型越小，在查询时进行的比较操作越快
  • 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O

7.4.4 索引字符串值的前缀

字符串越长，那存储一个字符串就需要占用越大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题：

  1. B+树索引中的记录需要把该列的完整字符串存储起来，而且字符串越长，在索引中占用的存储空间越大。
  2. 如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

索引列的字符串前缀其实也是排好序的，所以索引的设计者提出了个方案 --- 只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值，再对比就好了。这样只在B+树中存储字符串的前几个字符的编码，既节约空间，又减少了字符串的比较时间，还大概能解决排序的问题，比方说我们在建表语句中只对name列的前10个字符进行索引可以这么写：

CREATE TABLE person_info(
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

name(10)就表示在建立的B+树索引中只保留记录的前10个字符的编码，这种只索引字符串值的前缀的策略是我们非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候。

索引列前缀对排序的影响
因为二级索引中不包含完整的name列信息，所以无法对前十个字符相同，后边的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序。

7.4.5 让索引列在比较表达式中单独出现

假设表中有一个整数列my_col，我们为这个列建立了索引。下面的两个WHERE子句虽然语义是一致的，但是在效率上却有差别：

  1. WHERE my_col * 2 < 4
  2. WHERE my_col < 4/2

第1个WHERE子句中my_col列并不是以单独列的形式出现的，而是以my_col * 2这样的表达式的形式出现的，存储引擎会依次遍历所有的记录，计算这个表达式的值是不是小于4，所以这种情况下是使用不到为my_col列建立的B+树索引的。而第2个WHERE子句中my_col列并是以单独列的形式出现的，这样的情况可以直接使用B+树索引。

所以结论就是：如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。

7.4.6 主键插入顺序

我们知道，对于一个使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话,意味着：性能损耗！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入

7.4.7 冗余和重复索引

通过idx_name_birthday_phone_number索引就可以对name列进行快速搜索，再创建一个专门针对name列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

另一种情况，我们可能会对某个列重复建立索引,既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免

7.5 总结

  1. B+树索引在空间和时间上都有代价
  2. B+树索引适用于下面这些情况：
    • 全值匹配
    • 匹配左边的列
    • 匹配范围值
    • 精确匹配某一列并范围匹配另外一列
    • 用于排序
    • 用于分组
  3. 在使用索引时需要注意下面这些事项：
    • 只为用于搜索、排序或分组的列创建索引
    • 为列的基数大的列创建索引
    • 索引列的类型尽量小
    • 可以只对字符串值的前缀建立索引
    • 只有索引列在比较表达式中单独出现才可以适用索引
    • 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
    • 定位并删除表中的重复和冗余索引
    • 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。

第八章 数据的家-MySQL的数据目录

8.1 数据库和文件系统的关系

我们知道像InnoDB、MyISAM这样的存储引擎都是把表存储在磁盘上的，而操作系统用来管理磁盘的系统被称为文件系统，所以用一句话来表述就是：像 InnoDB 、 MyISAM 这样的存储引擎都是把表存储在文件系统上的。当我们想读取数据的时候，这些存储引擎会从文件系统中把数据读出来返回给我们，当我们想写入数据的时候，这些存储引擎会把这些数据又写回文件系统。

8.2 MySQL数据目录

MySQL服务器程序在启动时会到文件系统的某个目录下加载一些文件，之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中，这个目录就称为数据目录。

8.2.1 数据目录和安装目录的区别

安装目录下非常重要的bin目录，它里边存储了许多关于控制客户端程序和服务器程序的命令（许多可执行文件，比如mysql，mysqld，mysqld_safe等等等等好几十个）。而数据目录是用来存储MySQL在运行过程中产生的数据

8.2.2 如何确定MySQL中的数据目录

数据目录对应着一个系统变量datadir，我们在使用客户端与服务器建立连接之后查看这个系统变量的值就可以了：

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /usr/local/var/mysql/ |
+---------------+-----------------------+
1 row in set (0.00 sec)

8.3 数据目录的结构

8.3.1 数据库在文件系统中的表示

每当我们使用CREATE DATABASE 数据库名语句创建一个数据库的时候，在文件系统上实际发生了什么呢？其实很简单，每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹，我们每当我们新建一个数据库时，MySQL会帮我们做这两件事：

  1. 在数据目录下创建一个和数据库名同名的子目录（或者说是文件夹）。
  2. 在该与数据库名同名的子目录下创建一个名为db.opt的文件，这个文件中包含了该数据库的各种属性，比方说该数据库的字符集和比较规则是什么。

8.3.2 表在文件系统中的表示

我们的数据其实都是以记录的形式插入到表中的，每个表的信息其实可以分为两种：

  1. 表结构的定义
  2. 表中的数据

InnoDB和MyISAM这两种存储引擎都在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件，文件名是这样：表名.frm，这个后缀名为.frm是以二进制格式存储的。

InnoDB是如何存储表数据的

innoDB其实是使用页为基本单位来管理存储空间的，为了更好的管理这些页，设计InnoDB的大佬们提出了一个表空间或者文件空间（英文名：table space或者file space）的概念，这个表空间是一个抽象的概念，它可以对应文件系统上一个或多个真实文件（不同表空间对应的文件数量可能不同）。每一个表空间可以被划分为很多很多很多个页，我们的表数据就存放在某个表空间下的某些页里。

系统表空间（system tablespace）
这个所谓的系统表空间可以对应文件系统上一个或多个实际的文件，默认情况下，InnoDB会在数据目录下创建一个名为ibdata1、大小为12M的文件，这个文件就是对应的系统表空间在文件系统上的表示。这个文件是所谓的自扩展文件，也就是当不够用的时候它会自己增加文件大小。

当然，如果你想让系统表空间对应文件系统上多个实际文件，或者仅仅觉得原来的ibdata1这个文件名难听，那可以在MySQL启动时配置对应的文件路径以及它们的大小，比如我们这样修改一下配置文件：

[server]
innodb_data_file_path=data1:512M;data2:512M:autoextend

在一个MySQL服务器中，系统表空间只有一份。

独立表空间(file-per-table tablespace)
在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个.ibd的扩展名而已。

当然我们也可以自己指定使用系统表空间还是独立表空间来存储数据，这个功能由启动参数innodb_file_per_table控制，比如说我们想刻意将表数据都存储到系统表空间时，可以在启动MySQL服务器的时候这样配置：

[server]
innodb_file_per_table=0

当innodb_file_per_table的值为0时，代表使用系统表空间；当innodb_file_per_table的值为1时，代表使用独立表空间。不过innodb_file_per_table参数只对新建的表起作用，对于已经分配了表空间的表并不起作用。如果我们想把已经存在系统表空间中的表转移到独立表空间，可以使用下面的语法：

ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_table;

或者把已经存在独立表空间的表转移到系统表空间，可以使用下面的语法：

ALTER TABLE 表名 TABLESPACE [=] innodb_system;

其他类型的表空间
随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用表空间（general tablespace）、undo表空间（undo tablespace）、临时表空间（temporary tablespace）等等。

MyISAM是如何存储表数据的

在MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件。而且和InnoDB不同的是，MyISAM并没有什么所谓的表空间一说，表数据都存放到对应的数据库子目录下。假如test表使用MyISAM存储引擎的话，那么在它所在数据库对应的xiaohaizi目录下会为test表创建这三个文件：

test.frm
test.MYD
test.MYI

其中test.MYD代表表的数据文件，也就是我们插入的用户记录；test.MYI代表表的索引文件，我们为该表创建的索引都会放到这个文件中

8.3.3 视图在文件系统中的表示

MySQL中的视图其实是虚拟的表，也就是某个查询语句的一个别名而已，所以在存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了。和表一样，描述视图结构的文件也会被存储到所属数据库对应的子目录下面，只会存储一个视图名.frm的文件。

8.3.4 其他的文件

除了我们上面说的这些用户自己存储的数据以外，数据目录下还包括为了更好运行程序的一些额外文件，主要包括这几种类型的文件：

  1. 服务器进程文件。
      我们知道每运行一个MySQL服务器程序，都意味着启动一个进程。MySQL服务器会把自己的进程ID写入到一个文件中。
  2. 服务器日志文件。
      在服务器运行过程中，会产生各种各样的日志，比如常规的查询日志、错误日志、二进制日志、redo日志等等各种日志，这些日志各有各的用途。
  3. 默认/自动生成的SSL和RSA证书和密钥文件。
      主要是为了客户端和服务器安全通信而创建的一些文件

8.4 文件系统对数据库的影响

因为MySQL的数据都是存在文件系统中的，就不得不受到文件系统的一些制约，这在数据库和表的命名、表的大小和性能方面体现的比较明显，比如下面这些方面：

  1. 数据库名称和表名称不得超过文件系统所允许的最大长度。
  2. 特殊字符的问题
    1. 为了避免因为数据库名和表名出现某些特殊字符而造成文件系统不支持的情况，MySQL会把数据库名和表名中所有除数字和拉丁字母以外的所有字符在文件名里都映射成 @+编码值的形式作为文件名。比方说我们创建的表的名称为'test?'，由于?不属于数字或者拉丁字母，所以会被映射成编码值，所以这个表对应的.frm文件的名称就变成了test@003f.frm。
  3. 文件长度受文件系统最大长度限制

8.5 MySQL系统数据库简介

我们前面提到了MySQL的几个系统数据库，这几个数据库包含了MySQL服务器运行过程中所需的一些信息以及一些运行状态信息，我们现在稍微了解一下。

  1. mysql
      这个数据库它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

  2. information_schema
      这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引等等。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。

  3. performance_schema
      这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，算是对MySQL服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。

  4. sys
      这个数据库主要是通过视图的形式把information_schema 和performance_schema结合起来，让程序员可以更方便的了解MySQL服务器的一些性能信息。

第九章 存放页的大池子-InnoDB的表空间

表空间是一个抽象的概念，对于系统表空间来说，对应着文件系统中一个或多个实际文件；对于每个独立表空间来说，对应着文件系统中一个名为表名.ibd的实际文件。大家可以把表空间想象成被切分为许许多多个页的池子，当我们想为某个表插入一条记录的时候，就从池子中捞出一个对应的页来把数据写进去。

9.1 独立表空间结构

9.1.1 区（extent）的概念

表空间中的页实在是太多了，为了更好的管理这些页，InnoDB提出了区（英文名：extent）的概念。对于16KB的页来说，连续的64个页就是一个区，也就是说一个区默认占用1MB空间大小。不论是系统表空间还是独立表空间，都可以看成是由若干个区组成的，每256个区被划分成一组。画个图表示就是这样：

其中extent 0 ~ extent 255这256个区算是第一个组，extent 256 ~ extent 511这256个区算是第二个组，extent 512 ~ extent 767这256个区算是第三个组，依此类推可以划分更多的组。这些组的头几个页的类型都是类似的，就像这样：

  1. 第一个组最开始的3个页的类型是固定的，也就是说extent 0这个区最开始的3个页的类型是固定的，分别是：

    • FSP_HDR类型：这个类型的页是用来登记整个表空间的一些整体属性以及本组所有的区，也就是extent 0 ~ extent 255这256个区的属性，整个表空间只有一个FSP_HDR类型的页。
    • IBUF_BITMAP类型：这个类型的页是存储本组所有的区的所有页关于INSERT BUFFER的信息。
    • INODE类型：这个类型的页存储了许多称为INODE的数据结构。

  2. 其余各组最开始的2个页的类型是固定的，也就是说extent 256、extent 512这些区最开始的2个页的类型是固定的，分别是：

    • XDES类型：全称是extent descriptor，用来登记本组256个区的属性，也就是说对于在extent 256区中的该类型页存储的就是extent 256 ~ extent 511这些区的属性，对于在extent 512区中的该类型页存储的就是extent 512 ~ extent 767这些区的属性。上面介绍的FSP_HDR类型的页其实和XDES类型的页的作用类似，只不过FSP_HDR类型的页还会额外存储一些表空间的属性。
    • IBUF_BITMAP类型：上面介绍过了。

总结就是表空间被划分为许多连续的区，每个区默认由64个页组成，每256个区划分为一组，每个组的最开始的几个页类型是固定的就好了。

9.1.2 段（segment）的概念

不引入区的概念只使用页的概念对存储引擎的运行并没什么影响，但是我们来考虑一下下面这个场景：

我们每向表中插入一条记录，本质上就是向该表的聚簇索引以及所有二级索引代表的B+树的节点中插入数据。而B+树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。我们介绍B+树索引的适用场景的时候特别提到范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远，就是所谓的随机I/O。随机I/O是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序I/O。

所以才引入了区（extent）的概念，一个区就是在物理位置上连续的64个页。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配，甚至在表中的数据十分非常特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费（数据不足填充满整个区），但是从性能角度看，可以消除很多的随机I/O。

我们提到的范围查询，其实是对B+树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页放到申请到的区中的话，进行范围扫描的效果就大打折扣了。InnoDB对B+树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个段（segment），存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。

默认情况下一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存储空间的，一个区默认占用1M存储空间，所以默认情况下一个只存了几条记录的小表也需要2M的存储空间么？以后每次添加一个索引都要多申请2M的存储空间么？这对于存储记录比较少的表简直是天大的浪费。，设计InnoDB的大佬们提出了一个碎片（fragment）区的概念，也就是在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。所以此后为某个段分配存储空间的策略是这样的：

  1. 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页为单位来分配存储空间的。
  2. 当某个段已经占用了32个碎片区页之后，就会以完整的区为单位来分配存储空间。

所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页以及一些完整的区的集合。除了索引的叶子节点段和非叶子节点段之外，InnoDB中还有为存储一些特殊的数据而定义的段，比如回滚段。

9.1.3 区的分类

表空间的是由若干个区组成的，这些区大体上可以分为4种类型：

  1. 空闲的区：现在还没有用到这个区中的任何页。
  2. 有剩余空间的碎片区：表示碎片区中还有可用的页。
  3. 没有剩余空间的碎片区：表示碎片区中的所有页都被使用，没有空闲页。
  4. 附属于某个段的区。每一个索引都可以分为叶子节点段和非叶子节点段，除此之外InnoDB还会另外定义一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位。

这4种类型的区也可以被称为区的4种状态（State），设计InnoDB的大佬们为这4种状态的区定义了特定的名词儿：

状态名 含义
FREE 空闲的区
FREE_FRAG 有剩余空间的碎片区
FULL_FRAG 没有剩余空间的碎片区
FSEG 附属于某个段的区

处于FREE、FREE_FRAG以及FULL_FRAG这三种状态的区都是独立的，算是直属于表空间；而处于FSEG状态的区是附属于某个段的。

为了方便管理这些区，设计InnoDB的大佬设计了一个称为XDES Entry的结构（全称就是Extent Descriptor Entry），每一个区都对应着一个XDES Entry结构，这个结构记录了对应的区的一些属性。我们先看图来对这个结构有个大致的了解：

从图中我们可以看出，XDES Entry是一个40个字节的结构，大致分为4个部分，各个部分的释义如下：

  1. Segment ID（8字节）
      每一个段都有一个唯一的编号，用ID表示，此处的Segment ID字段表示就是该区所在的段。当然前提是该区已经被分配给某个段了，不然的话该字段的值没什么意义。
  2. List Node（12字节）
      这个部分可以将若干个XDES Entry结构串联成一个链表，大家看一下这个List Node的结构：

      如果我们想定位表空间内的某一个位置的话，只需指定页号以及该位置在指定页号中的页内偏移量即可。所以：
    • Pre Node Page Number和Pre Node Offset的组合就是指向前一个XDES Entry的指针
    • Next Node Page Number和Next Node Offset的组合就是指向后一个XDES Entry的指针。
  3. State（4字节）
      这个字段表明区的状态。可选的值就是我们前面说过的那4个，分别是：FREE、FREE_FRAG、FULL_FRAG和FSEG。
  4. Page State Bitmap（16字节）
      这个部分共占用16个字节，也就是128个比特位。我们说一个区默认有64个页，这128个比特位被划分为64个部分，每个部分2个比特位，对应区中的一个页。比如Page State Bitmap部分的第1和第2个比特位对应着区中的第1个页，第3和第4个比特位对应着区中的第2个页，依此类推，Page State Bitmap部分的第127和128个比特位对应着区中的第64个页。这两个比特位的第一个位表示对应的页是否是空闲的，第二个比特位还没有用。
XDES Entry链表

向表中插入数据本质上就是向表中各个索引的叶子节点段、非叶子节点段插入数据，捋一捋向某个段中插入数据的过程：

  1. 当段中数据较少的时候，首先会查看表空间中是否有状态为FREE_FRAG的区，也就是找还有空闲空间的碎片区，如果找到了，那么从该区中取一些零碎的页把数据插进去；否则到表空间下申请一个状态为FREE的区，也就是空闲的区，把该区的状态变为FREE_FRAG，然后从该新申请的区中取一些零碎的页把数据插进去。之后不同的段使用零碎页的时候都会从该区中取，直到该区中没有空闲空间，然后该区的状态就变成了FULL_FRAG。
    现在的问题是你怎么知道表空间里的哪些区是FREE的，哪些区的状态是FREE_FRAG的，哪些区是FULL_FRAG的？我们可以通过List Node中的指针，做这么三件事：

    • 把状态为FREE的区对应的XDES Entry结构通过List Node来连接成一个链表，这个链表我们就称之为FREE链表。
    • 把状态为FREE_FRAG的区对应的XDES Entry结构通过List Node来连接成一个链表，这个链表我们就称之为FREE_FRAG链表。
    • 把状态为FULL_FRAG的区对应的XDES Entry结构通过List Node来连接成一个链表，这个链表我们就称之为FULL_FRAG链表。
      这样每当我们想找一个FREE_FRAG状态的区时，就直接把FREE_FRAG链表的头节点拿出来，从这个节点中取一些零碎的页来插入数据，当这个节点对应的区用完时，就修改一下这个节点的State字段的值，然后从FREE_FRAG链表中移到FULL_FRAG链表中。同理，如果FREE_FRAG链表中一个节点都没有，那么就直接从FREE链表中取一个节点移动到FREE_FRAG链表的状态，并修改该节点的STATE字段值为FREE_FRAG，然后从这个节点对应的区中获取零碎的页就好了。

  2. 当段中数据已经占满了32个零散的页后，就直接申请完整的区来插入数据了。
    我们怎么知道哪些区属于哪个段的呢？我们想要每个段都有它独立的链表，所以可以根据段号（也就是Segment ID）来建立链表，因为一个段中可以有好多个区，有的区是完全空闲的，有的区还有一些页可以用，有的区已经没有空闲页可以用了，所以我们有必要继续细分，设计InnoDB的大佬们为每个段中的区对应的XDES Entry结构建立了三个链表：

    • FREE链表：同一个段中，所有页都是空闲的区对应的XDES Entry结构会被加入到这个链表。注意和直属于表空间的FREE链表区别开了，此处的FREE链表是附属于某个段的。
    • NOT_FULL链表：同一个段中，仍有空闲空间的区对应的XDES Entry结构会被加入到这个链表。
    • FULL链表：同一个段中，已经没有空闲空间的区对应的XDES Entry结构会被加入到这个链表。

再次强调一遍，每一个索引都对应两个段，每个段都会维护上述的3个链表，比如下面这个表：

CREATE TABLE t (
  c1 INT NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(100),
  c3 VARCHAR(100),
  PRIMARY KEY (c1),
  KEY idx_c2 (c2)
)ENGINE=InnoDB;

这个表t共有两个索引，一个聚簇索引，一个二级索引idx_c2，所以这个表共有4个段，每个段都会维护上述3个链表，总共是12个链表，加上我们上面说过的直属于表空间的3个链表，整个独立表空间共需要维护15个链表。所以段在数据量比较大时插入数据的话，会先获取NOT_FULL链表的头节点，直接把数据插入这个头节点对应的区中即可，如果该区的空间已经被用完，就把该节点移到FULL链表中。

链表基节点

我们怎么找到这些链表呢，或者说怎么找到某个链表的头节点或者尾节点在表空间中的位置呢？设计InnoDB的大佬当然考虑了这个问题，他们设计了一个叫List Base Node的结构，翻译成中文就是链表的基节点。这个结构中包含了链表的头节点和尾节点的指针以及这个链表中包含了多少节点的信息，我们画图看一下这个结构的示意图：

  1. List Length表明该链表一共有多少节点，
  2. First Node Page Number和First Node Offset表明该链表的头节点在表空间中的位置。
  3. Last Node Page Number和Last Node Offset表明该链表的尾节点在表空间中的位置。
链表小结

综上所述，表空间是由若干个区组成的，每个区都对应一个XDES Entry的结构，直属于表空间的区对应的XDES Entry结构可以分成FREE、FREE_FRAG和FULL_FRAG这3个链表；每个段可以附属若干个区，每个段中的区对应的XDES Entry结构可以分成FREE、NOT_FULL和FULL这3个链表。每个链表都对应一个List Base Node的结构，这个结构里记录了链表的头、尾节点的位置以及该链表中包含的节点数。

9.1.4 段的结构

段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页以及一些完整的区组成。像每个区都有对应的XDES Entry来记录这个区中的属性一样，设计InnoDB的大佬为每个段都定义了一个INODE Entry结构来记录一下段中的属性。大家看一下示意图：

  1. Segment ID
      就是指这个INODE Entry结构对应的段的编号（ID）。
  2. NOT_FULL_N_USED
      这个字段指的是在NOT_FULL链表中已经使用了多少个页。下次从NOT_FULL链表分配空闲页时可以直接根据这个字段的值定位到。而不用从链表中的第一个页开始遍历着寻找空闲页。
  3. 3个List Base Node
      分别为段的FREE链表、NOT_FULL链表、FULL链表定义了List Base Node，这样我们想查找某个段的某个链表的头节点和尾节点的时候，就可以直接到这个部分找到对应链表的List Base Node。
  4. Magic Number：
      这个值是用来标记这个INODE Entry是否已经被初始化了（初始化的意思就是把各个字段的值都填进去了）。如果这个数字是值的97937874，表明该INODE Entry已经初始化，否则没有被初始化。
  5. Fragment Array Entry
      我们前面强调过无数次：段是一些零散页和一些完整的区的集合，每个Fragment Array Entry结构都对应着一个零散的页，这个结构一共4个字节，表示一个零散页的页号。

9.2 各类型页详细情况

9.2.1 FSP_HDR类型

首先看第一个组的第一个页，当然也是表空间的第一个页，页号为0。这个页的类型是FSP_HDR，它存储了表空间的一些整体属性以及第一个组内256个区的对应的XDES Entry结构，直接看这个类型的页的示意图：

一个完整的FSP_HDR类型的页大致由5个部分组成，各个部分的具体释义如下表：

名称 中文名 占用空间大小 简单描述
File Header 文件头部 38字节 页的一些通用信息
File Space Header 表空间头部 112字节 表空间的一些整体属性信息
XDES Entry 区描述信息 10240字节 存储本组256个区对应的属性信息
Empty Space 尚未使用空间 5986字节 用于页结构的填充，没什么实际意义
File Trailer 文件尾部 8字节 校验页是否完整

File Space Header部分

名称 占用空间大小 描述
Space ID 4字节 表空间的ID
Not Used 4字节 这4个字节未被使用，可以忽略
Size 4字节 当前表空间占有的页数
FREE Limit 4字节 尚未被初始化的最小页号，大于或等于这个页号的区对应的XDES Entry结构都没有被加入FREE链表
Space Flags 4字节 表空间的一些占用存储空间比较小的属性
FRAG_N_USED 4字节 FREE_FRAG链表中已使用的页数量
List Base Node for FREE List 16字节 FREE链表的基节点
List Base Node for FREE_FRAG List 16字节 FREE_FREG链表的基节点
List Base Node for FULL_FRAG List 16字节 FULL_FREG链表的基节点
Next Unused Segment ID 8字节 当前表空间中下一个未使用的 Segment ID
List Base Node for SEG_INODES_FULL List 16字节 SEG_INODES_FULL链表的基节点
List Base Node for SEG_INODES_FREE List 16字节 SEG_INODES_FREE链表的基节点
  1. List Base Node for FREE List、List Base Node for FREE_FRAG List、List Base Node for FULL_FRAG List
    分别是直属于表空间的FREE链表的基节点、FREE_FRAG链表的基节点、FULL_FRAG链表的基节点，这三个链表的基节点在表空间的位置是固定的，就是在表空间的第一个页（也就是FSP_HDR类型的页）的File Space Header部分。
  2. FRAG_N_USED
    这个字段表明在FREE_FRAG链表中已经使用的页数量，方便之后在链表中查找空闲的页。
  3. FREE Limit
    在该字段表示的页号之前的区都被初始化了，之后的区尚未被初始化。
  4. Next Unused Segment ID
    该字段表明当前表空间中最大的段ID的下一个ID。
  5. Space Flags
    表空间对于一些布尔类型的属性，或者只需要寥寥几个比特位搞定的属性都放在了这个Space Flags中存储，虽然它只有4个字节，32个比特位大小，却存储了好多表空间的属性，详细情况如下表：
标志名称 占用的空间（单位：bit） 描述
POST_ANTELOPE 1 表示文件格式是否大于ANTELOPE
ZIP_SSIZE 4 表示压缩页的大小
ATOMIC_BLOBS 1 表示是否自动把值非常长的字段放到BLOB页里
PAGE_SSIZE 4 页大小
DATA_DIR 1 表示表空间是否是从默认的数据目录中获取的
SHARED 1 是否为共享表空间
TEMPORARY 1 是否为临时表空间
ENCRYPTION 1 表空间是否加密
UNUSED 18 没有使用到的比特位

  1. List Base Node for SEG_INODES_FULL List和List Base Node for SEG_INODES_FREE List
    每个段对应的INODE Entry结构会集中存放到一个类型位INODE的页中，如果表空间中的段特别多，则会有多个INODE Entry结构，可能一个页放不下，这些INODE类型的页会组成两种列表：

    • SEG_INODES_FULL链表，该链表中的INODE类型的页都已经被INODE Entry结构填充满了，没空闲空间存放额外的INODE Entry了。
    • SEG_INODES_FREE链表，该链表中的INODE类型的页都已经仍有空闲空间来存放INODE Entry结构。

XDES Entry部分
XDES Entry就是在表空间的第一个页中保存的。我们知道一个XDES Entry结构的大小是40字节，但是一个页的大小有限，只能存放有限个XDES Entry结构，所以我们才把256个区划分成一组，在每组的第一个页中存放256个XDES Entry结构。

9.2.2 XDES类型

每一个XDES Entry结构对应表空间的一个区，虽然一个XDES Entry结构只占用40字节，但你抵不住表空间的区的数量也多啊。在区的数量非常多时，一个单独的页可能就不够存放足够多的XDES Entry结构，所以我们把表空间的区分为了若干个组，每组开头的一个页记录着本组内所有的区对应的XDES Entry结构。由于第一个组的第一个页有些特殊，因为它也是整个表空间的第一个页，所以除了记录本组中的所有区对应的XDES Entry结构以外，还记录着表空间的一些整体属性，这个页的类型就是我们刚刚说完的FSP_HDR类型，整个表空间里只有一个这个类型的页。除去第一个分组以外，之后的每个分组的第一个页只需要记录本组内所有的区对应的XDES Entry结构即可，不需要再记录表空间的属性了。

与FSP_HDR类型的页对比，除了少了File Space Header部分之外，也就是除了少了记录表空间整体属性的部分之外，其余的部分是一样一样的。

9.2.3 IBUF_BITMAP类型

对比前面介绍表空间的图，每个分组的第二个页的类型都是IBUF_BITMAP，这种类型的页里边记录了一些有关Change Buffer的东西。

9.2.4 INODE类型

再次对比前面介绍表空间的图，第一个分组的第三个页的类型是INODE。我们前面说过InnoDB为每个索引定义了两个段，而且为某些特殊功能定义了些特殊的段。为了方便管理，他们又为每个段设计了一个INODE Entry结构，这个结构中记录了关于这个段的相关属性。这个INODE类型的页就是为了存储INODE Entry结构而存在的。

名称 中文名 占用空间大小 简单描述
File Header 文件头部 38字节 页的一些通用信息
List Node for INODE Page List 通用链表节点 12字节 存储上一个INODE页和下一个INODE页的指针
INODE Entry 段描述信息 16128字节
Empty Space 尚未使用空间 6字节 用于页结构的填充，没什么实际意义
File Trailer 文件尾部 8字节 校验页是否完整

除了File Header、Empty Space、File Trailer这几个老朋友外，我们重点关注List Node for INODE Page List和INODE Entry这两个部分。

首先看INODE Entry部分，我们前面已经详细介绍过这个结构的组成了，主要包括对应的段内零散页的地址以及附属于该段的FREE、NOT_FULL和FULL链表的基节点。每个INODE Entry结构占用192字节，一个页里可以存储85个这样的结构。

重点看一下List Node for INODE Page List，因为一个表空间中可能存在超过85个段，所以可能一个INODE类型的页不足以存储所有的段对应的INODE Entry结构，所以就需要额外的INODE类型的页来存储这些结构。还是为了方便管理这些INODE类型的页，设计InnoDB的大佬们将这些INODE类型的页串联成两个不同的链表：

  1. SEG_INODES_FULL链表：该链表中的INODE类型的页中已经没有空闲空间来存储额外的INODE Entry结构了。
  2. SEG_INODES_FREE链表：该链表中的INODE类型的页中还有空闲空间来存储额外的INODE Entry结构了。

想必大家已经认出这两个链表了，我们前面提到过这两个链表的基节点就存储在File Space Header里边，也就是说这两个链表的基节点的位置是固定的，所以我们可以很轻松的访问到这两个链表。以后每当我们新创建一个段（创建索引时就会创建段）时，都会创建一个INODE Entry结构与之对应，存储INODE Entry的大致过程就是这样的：

  1. 先看看SEG_INODES_FREE链表是否为空，如果不为空，直接从该链表中获取一个节点，也就相当于获取到一个仍有空闲空间的INODE类型的页，然后把该INODE Entry结构放到该页中。当该页中无剩余空间时，就把该页放到SEG_INODES_FULL链表中。
  2. 如果SEG_INODES_FREE链表为空，则需要从表空间的FREE_FRAG链表中申请一个页，修改该页的类型为INODE，把该页放到SEG_INODES_FREE链表中，与此同时把该INODE Entry结构放入该页。

9.3 Segment Header 结构的运用

我们知道一个索引会产生两个段，分别是叶子节点段和非叶子节点段，而每个段都会对应一个INODE Entry结构，那我们怎么知道某个段对应哪个INODE Entry结构呢？所以得找个地方记下来这个对应关系。

在数据页，也就是INDEX类型的页时有一个Page Header部分，其中的PAGE_BTR_SEG_LEAF和PAGE_BTR_SEG_TOP都占用10个字节，它们其实对应一个叫Segment Header的结构，该结构图示如下：

各个部分的具体释义如下：

名称 占用字节数 描述
Space ID of the INODE Entry 4 INODE Entry结构所在的表空间ID
Page Number of the INODE Entry 4 INODE Entry结构所在的页页号
Byte Offset of the INODE Ent 2 INODE Entry结构在该页中的偏移量

这样子就很清晰了，PAGE_BTR_SEG_LEAF记录着叶子节点段对应的INODE Entry结构的地址是哪个表空间的哪个页的哪个偏移量，PAGE_BTR_SEG_TOP记录着非叶子节点段对应的INODE Entry结构的地址是哪个表空间的哪个页的哪个偏移量。这样子索引和其对应的段的关系就建立起来了。不过需要注意的一点是，因为一个索引只对应两个段，所以只需要在索引的根页中记录这两个结构即可。

9.4 系统表空间

系统表空间的结构和独立表空间基本类似，只不过由于整个MySQL进程只有一个系统表空间，在系统表空间中会额外记录一些有关整个系统信息的页，所以会比独立表空间多出一些记录这些信息的页。因为这个系统表空间最牛逼，相当于是表空间之首，所以它的表空间 ID（Space ID）是0。

9.4.1 系统表空间的整体结构

系统表空间与独立表空间的一个非常明显的不同之处就是在表空间开头有许多记录整个系统属性的页，如图：

可以看到，系统表空间和独立表空间的前三个页（页号分别为0、1、2，类型分别是FSP_HDR、IBUF_BITMAP、INODE）的类型是一致的，只是页号为3～7的页是系统表空间特有的，我们来看一下这些多出来的页都是干什么使的：

页号 页类型 英文描述 描述
3 SYS Insert Buffer Header 存储Insert Buffer的头部信息
4 INDEX Insert Buffer Root 存储Insert Buffer的根页
5 TRX_SYS Transction System 事务系统的相关信息
6 SYS First Rollback Segment 第一个回滚段的页
7 SYS Data Dictionary Header 数据字典头部信息

除了这几个记录系统属性的页之外，系统表空间的extent 1和extent 2这两个区，也就是页号从64~191这128个页被称为Doublewrite buffer，也就是双写缓冲区。

InnoDB数据字典

我们平时使用INSERT语句向表中插入的那些记录称之为用户数据，MySQL只是作为一个软件来为我们来保管这些数据，提供方便的增删改查接口而已。但是每当我们向一个表中插入一条记录的时候，MySQL先要校验一下插入语句对应的表存不存在，插入的列和表中的列是否符合，如果语法没有问题的话，还需要知道该表的聚簇索引和所有二级索引对应的根页是哪个表空间的哪个页，然后把记录插入对应索引的B+树中。所以说，MySQL除了保存着我们插入的用户数据之外，还需要保存许多额外的信息，这些数据也称为元数据。

InnoDB存储引擎特意定义了一些列的内部系统表（internal system table）来记录这些这些元数据：

表名 描述
SYS_TABLES 整个InnoDB存储引擎中所有的表的信息
SYS_COLUMNS 整个InnoDB存储引擎中所有的列的信息
SYS_INDEXES 整个InnoDB存储引擎中所有的索引的信息
SYS_FIELDS 整个InnoDB存储引擎中所有的索引对应的列的信息
SYS_FOREIGN 整个InnoDB存储引擎中所有的外键的信息
SYS_FOREIGN_COLS 整个InnoDB存储引擎中所有的外键对应列的信息
SYS_TABLESPACES 整个InnoDB存储引擎中所有的表空间信息
SYS_DATAFILES 整个InnoDB存储引擎中所有的表空间对应文件系统的文件路径信息
SYS_VIRTUAL 整个InnoDB存储引擎中所有的虚拟生成列的信息

这些系统表也被称为数据字典，它们都是以B+树的形式保存在系统表空间的某些页中，其中SYS_TABLES、SYS_COLUMNS、SYS_INDEXES、SYS_FIELDS这四个表尤其重要，称之为基本系统表（basic system tables），我们先看看这4个表的结构：

SYS_TABLES表

列名 描述
NAME 表的名称
ID InnoDB存储引擎中每个表都有一个唯一的ID
N_COLS 该表拥有列的个数
TYPE 表的类型，记录了一些文件格式、行格式、压缩等信息
MIX_ID 已过时，忽略
MIX_LEN 表的一些额外的属性
CLUSTER_ID 未使用，忽略
SPACE 该表所属表空间的ID

这个SYS_TABLES表有两个索引：
- 以NAME列为主键的聚簇索引
- 以ID列建立的二级索引

SYS_COLUMNS表

列名 描述
TABLE_ID 该列所属表对应的ID
POS 该列在表中是第几列
NAME 该列的名称
MTYPE main data type，主数据类型，就是那堆INT、CHAR、VARCHAR、FLOAT、DOUBLE之类的东东
PRTYPE precise type，精确数据类型，就是修饰主数据类型的那堆东东，比如是否允许NULL值，是否允许负数什么的
LEN 该列最多占用存储空间的字节数
PREC 该列的精度，不过这列貌似都没有使用，默认值都是0

这个SYS_COLUMNS表只有一个聚集索引：
- 以(TABLE_ID, POS)列为主键的聚簇索引

SYS_INDEXES表

列名 描述
TABLE_ID 该索引所属表对应的ID
ID Inn

NAME 该索引的名称
N_FIELDS| 该索引包含列的个数
TYPE |该索引的类型，比如聚簇索引、唯一索引、更改缓冲区的索引、全文索引、普通的二级索引等等各种类型
SPACE| 该索引根页所在的表空间ID
PAGE_NO| 该索引根页所在的页号
MERGE_THRESHOLD| 如果页中的记录被删除到某个比例，就把该页和相邻页合并，这个值就是这个比例

这个SYS_INEXES表只有一个聚集索引：
- 以(TABLE_ID, ID)列为主键的聚簇索引

SYS_FIELDS表

列名 描述
INDEX_ID 该索引列所属的索引的ID
POS 该索引列在某个索引中是第几列
COL_NAME 该索引列的名称

这个SYS_INEXES表只有一个聚集索引：
- 以(INDEX_ID, POS)列为主键的聚簇索引

Data Dictionary Header页
只要有了上述4个基本系统表，也就意味着可以获取其他系统表以及用户定义的表的所有元数据。比方说我们想看看SYS_TABLESPACES这个系统表里存储了哪些表空间以及表空间对应的属性，那就可以：

  1. 到SYS_TABLES表中根据表名定位到具体的记录，就可以获取到SYS_TABLESPACES表的TABLE_ID
  2. 使用这个TABLE_ID到SYS_COLUMNS表中就可以获取到属于该表的所有列的信息。
  3. 使用这个TABLE_ID还可以到SYS_INDEXES表中获取所有的索引的信息，索引的信息中包括对应的INDEX_ID，还记录着该索引对应的B+数根页是哪个表空间的哪个页。
  4. 使用INDEX_ID就可以到SYS_FIELDS表中获取所有索引列的信息。

InnoDB拿出一个固定的页来记录这4个表的聚簇索引和二级索引对应的B+树位置，这个页就是页号为7的页，类型为SYS，记录了Data Dictionary Header，也就是数据字典的头部信息。除了这4个表的5个索引的根页信息外，这个页号为7的页还记录了整个InnoDB存储引擎的一些全局属性，说话太啰嗦，直接看这个页的示意图：

可以看到这个页由下面几个部分组成：

名称 中文名 占用空间大小 简单描述
File Header 文件头部 38字节 页的一些通用信息
Data Dictionary Header 数据字典头部信息 56字节 记录一些基本系统表的根页位置以及InnoDB存储引擎的一些全局信息
Segment Header 段头部信息 10字节 记录本页所在段对应的INODE Entry位置信息
Empty Space 尚未使用空间 16272字节 用于页结构的填充，没什么实际意义
File Trailer 文件尾部 8字节 校验页是否完整

可以看到这个页里竟然有Segment Header部分，意味着InnoDB把这些有关数据字典的信息当成一个段来分配存储空间，我们就姑且称之为数据字典段吧。由于目前我们需要记录的数据字典信息非常少（可以看到Data Dictionary Header部分仅占用了56字节），所以该段只有一个碎片页，也就是页号为7的这个页。

接下来我们需要细细介绍一下Data Dictionary Header部分的各个字段：

  1. Max Row ID：我们说过如果我们不显式的为表定义主键，而且表中也没有UNIQUE索引，那么InnoDB存储引擎会默认为我们生成一个名为row_id的列作为主键。因为它是主键，所以每条记录的row_id列的值不能重复。原则上只要一个表中的row_id列不重复就可以了，也就是说表a和表b拥有一样的row_id列也没什么关系，不过InnoDB只提供了这个Max Row ID字段，不论哪个拥有row_id列的表插入一条记录时，该记录的row_id列的值就是Max Row ID对应的值，然后再把Max Row ID对应的值加1，也就是说这个Max Row ID是全局共享的。
  2. Max Table ID：InnoDB存储引擎中的所有的表都对应一个唯一的ID，每次新建一个表时，就会把本字段的值作为该表的ID，然后自增本字段的值。
  3. Max Index ID：InnoDB存储引擎中的所有的索引都对应一个唯一的ID，每次新建一个索引时，就会把本字段的值作为该索引的ID，然后自增本字段的值。
  4. Max Space ID：InnoDB存储引擎中的所有的表空间都对应一个唯一的ID，每次新建一个表空间时，就会把本字段的值作为该表空间的ID，然后自增本字段的值。
  5. Mix ID Low(Unused)：这个字段没什么用，跳过。
  6. Root of SYS_TABLES clust index：本字段代表SYS_TABLES表聚簇索引的根页的页号。
  7. Root of SYS_TABLE_IDS sec index：本字段代表SYS_TABLES表为ID列建立的二级索引的根页的页号。
  8. Root of SYS_COLUMNS clust index：本字段代表SYS_COLUMNS表聚簇索引的根页的页号。
  9. Root of SYS_INDEXES clust index本字段代表SYS_INDEXES表聚簇索引的根页的页号。
  10. Root of SYS_FIELDS clust index：本字段代表SYS_FIELDS表聚簇索引的根页的页号。
  11. Unused：这4个字节没用，跳过。

==information_schema系统数据库
需要注意一点的是，用户是不能直接访问InnoDB的这些内部系统表的，除非你直接去解析系统表空间对应文件系统上的文件。不过设计InnoDB的大佬考虑到查看这些表的内容可能有助于大家分析问题，所以在系统数据库information_schema中提供了一些以innodb_sys开头的表：

mysql> USE information_schema;
Database changed

mysql> SHOW TABLES LIKE 'innodb_sys%';
+--------------------------------------------+
| Tables_in_information_schema (innodb_sys%) |
+--------------------------------------------+
| INNODB_SYS_DATAFILES                       |
| INNODB_SYS_VIRTUAL                         |
| INNODB_SYS_INDEXES                         |
| INNODB_SYS_TABLES                          |
| INNODB_SYS_FIELDS                          |
| INNODB_SYS_TABLESPACES                     |
| INNODB_SYS_FOREIGN_COLS                    |
| INNODB_SYS_COLUMNS                         |
| INNODB_SYS_FOREIGN                         |
| INNODB_SYS_TABLESTATS                      |
+--------------------------------------------+
10 rows in set (0.00 sec)

在information_schema数据库中的这些以INNODB_SYS开头的表并不是真正的内部系统表（内部系统表就是我们上面介绍的以SYS开头的那些表），而是在存储引擎启动时读取这些以SYS开头的系统表，然后填充到这些以INNODB_SYS开头的表中。以INNODB_SYS开头的表和以SYS开头的表中的字段并不完全一样。

9.4 总结图

链接:《从根儿上理解MySQL》读书笔记(三)

一条小咸鱼