mysql是怎样运行的-从根儿上理解mysql学习笔记(二)

说明

本文是《MySQL是怎样运行的-从根儿上理解MySQL》的学习笔记，文中的图全部来自于这本书，强烈建议买一本看看，对MySQL理解会特别深入，非常感谢作者"小孩子4919"。

第七章 B+树索引的使用

索引的代价及更好地使用索引

在介绍这部分只是前还是先创建一个表

CREATE TABLE single_table(
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1(key1),
UNIQUE KEY uk_key2(key2),
KEY idx_key3(key3),
KEY idx_key_part(key_part1, key_part2, key_part3)
)Engine=InnoDB CHARSET=utf8;

为single_table表建立了1个聚簇索引和4个二级索引，分别是

1. 为主键id建立了聚簇索引
2. 为key1列建立了二级索引
3. 为key2列建立了二级索引，而且该索引还是唯一二级索引
4. 为key3列建立了二级索引
5. 为key_part1, key_part2, key_part3建立了二级索引，而且这是一个联合索引

如果想查找key1='abc’的完整记录，就可以通过idx_key1对应的B+树，很容易定位到第一条key1列的值等于’abc’的记录，然后顺序往后扫描直到不满足条件即可，如图7-5。

图7-5定位key1值为'abc'的第一条记录示意图

虽然索引比较方便进行查找，但是不能肆意创建，这里有必要介绍使用索引的代价

1. 空间的代价
   每建立一个索引，都需要建立一个B+树，每棵B+树的每个节点都是一个数据页，一个数据页默认会占用16KB空间，一棵很大的B+树由许多数据页组成，这将占用一大片存储空间。
2. 时间的代价
   在对表中数据进行增删改操作时，都需要修改维护各个B+树索引。这些操作会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行页面分裂、页面回收的操作，以维护节点和记录的排序。

知道了索引的代价，下面介绍如何更好地使用索引。

• 只为用于搜索、排序或分组的列创建索引
  只为出现在WHERE子句中的列、连接子句中的连接列，或者出现在ORDER BY或GROUP BY子句中的列创建索引，在查询列表中的列没必要建索引，例如

  SELECT common_field, key_part3 FROM single_table WHERE key1 = 'a';
  

  这里出现了3个列字段，SELECT查询列表中两个，WHERE子句中一个，但是只需要为WHERE子句中出现的列即key1建立索引即可。
• 考虑索引列不重复值的个数
  前文叙述中提到，二级索引查询出结果后需要回表，因此二级索引查询出来的记录越多，回表代价越大。在为某个列创建索引时需要考虑该列中不重复值的个数占全部记录的比例。如果比例太低，说明重复值比较多，不适合建索引。
• 索引列类型尽量小
  这个比较好理解，索引是需要存储空间的，索引类型小，占用空间就小。
• 为列前缀建立索引
  如果某一个字段特别长，可以为某个列的前几个字符建立索引。

  ALTER TABLE single_table DROP INDEX idx_key1;
  ALTER TABLE single_table ADD INDEX idx_key1(key1(10));
  

  这里给idx_key1前10个字符建立了索引，只有前10个字符是有序的。
• 覆盖索引
  为了彻底告别回表带来的性能损耗，建议在查询列表中只包含索引列，比如下面的语句

  SELECT key1, id FROM single_table WHERE key1 > 'a' AND key1 < 'c';
  

  覆盖索引包含key1、id这两个字段，因此不需要执行回表的操作。
• 让索引列以列名形式在搜索条件中单独出现

  SELECT * FROM s1 single_table WHERE key2 * 2 < 4; # (不妥)
  SELECT * FROM s1 single_table WHERE key2 < 2; # (OK)
  

应用B+树索引

扫描区间和扫描条件

对于某个查询来说，最简单粗暴的执行方案就是扫描表中的所有记录，判断每一条记录是否符合搜索条件，这种方案称为全表扫描。显然全表扫描耗时很高，但却是一种万能的解决方案。
前文提到B+树索引可以提高搜索效率，比如下面的查询语句：

SELECT * FROM single_table WHERE id >=2 AND id <= 100;

由于在single_table表中建立了主键的聚簇索引，查找id的区间就是[2, 100]，全表扫描的区间可以认为是$(-\infty ,+\infty )$。
对于下面的查询语句

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

分析之后知道对于key2扫描区间是[1438, 1438]$\bigcup$[6328, 6328]$\bigcup$[38, 79]，方便起见将[1438, 1438]、[6328, 6328]这样只包含一个值的扫描区间称为单点扫描区间，将[38, 79]这样包含多个值的扫描区间称为范围扫描区间。
并不是所有的搜索条件都可以称为边界条件，比如

SELECT * FROM single_table WHERE key1 < 'a' and key3 > 'z' and common_field = 'abc';

• 如果使用idx_key1查询，那么扫描区间就是$(-\infty ,a)$，形成该扫描区间的条件是key1 < 'a'。而key3 > 'z' and common_field = 'abc'就是普通的搜索条件，它们需要在获取idx_key1的二级索引记录后，再执行回表操作获得完整用户记录后才能判断条件是否满足。
• 如果使用idx_key3查询，那么扫描区间是$(z,+\infty )$。

从上文的描述中可以看到，在使用某个索引执行查询时，关键的问题是通过搜索条件找到合适的扫描区间，然后再到对应B+树定位到该扫描区间中第一条记录，然后就可以沿着记录所在的单向链表往后扫描，知道某条记录不符合形成该扫描区间的边界条件为止。注意LIKE操作符比较特殊，如果搜索条件是key1 LIKE 'a%'，搜索的区间就是$(-\infty ,a)$，但是如果搜索条件是key1 LIKE '%bc'，那么搜索区间就是$(-\infty ,+\infty )$。

1. 所有搜索条件都可以生成合适的扫描区间情况

   SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;# sql1
   SELECT * FROM single_table WHERE key2 > 100 OR key2 > 200;# sql2
   

   sql1和sql2在uk_key2上的扫描范围分别是$(200,+\infty )$和$(100,+\infty )$。

2. 有的搜索条件不能生成合适的扫描区间情况

   SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';
   

   这里使用uk_key2的扫描区间是$(100,+\infty )$，因为idx_key2索引里面根本就没有common_field字段，因此common_field = 'abc'这个搜索条件用不到，可以把common_field = 'abc'搜索条件替换为TRUE(TRUE对应的扫描区间是$(-\infty ,+\infty )$)。

   SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc'; # 原始sql
   SELECT * FROM single_table WHERE key2 > 100 AND TRUE; # 替换后sql
   SELECT * FROM single_table WHERE key2 > 100; # 简化后sql
   

   再看另外一个例子

   SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc'; # 原始sql
   SELECT * FROM single_table WHERE key2 > 100 OR TRUE; # 替换后sql
   SELECT * FROM single_table WHERE TRUE; # 简化后sql
   

   可见，这条sql如果使用uk_key2执行查询扫描范围是$(-\infty ,+\infty )$，还不如直接使用全表扫描。

3. 从复杂的搜索条件中找出扫描区间
   看下面的例子

   SELECT * FROM single_table WHERE
   (key1 > 'xyz' AND key2 = 748) OR
   (key1 < 'abc' AND key1 > 'lmn') OR
   (key1 LIKE '%suf' and key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc'));
   

   这条查询语句相当复杂，不要被过长的命令吓到，仔细分析下可能用到的索引。WHERE子句中共出现了3个字段key1、key2、common_field，其中key1、key22个字段建有索引，下面分别进行分析。

   1. 假设使用idx_key1执行查询
      将不能形成合适扫描区间的搜索条件暂时移除，移除的方法也简单，就是直接将它们替换成TRUE即可。于是，替换之后就变成

      (key1 > 'xyz' AND TRUE) OR
      (key1 < 'abc' AND key1 > 'lmn') OR
      (TRUE AND key1 > 'zzz' AND (TRUE OR TRUE));
      

      继续化简有

      key1 > 'xyz'  OR (key1 < 'abc' AND key1 > 'lmn') OR key1 > 'zzz' ;
      

      由于(key1 < 'abc' AND key1 > 'lmn')永远为FALSE，因此上面的条件化简为

      key1 > 'xyz'  OR key1 > 'zzz' ;
      

      进而化简为

      key1 > 'xyz' ;
      

      至此，终于分析处理使用idx_key1索引得到的扫描区间是${(}^{\prime }xy{z}^{\prime },\infty )$。
   2. 假设使用uk_key2执行查询
      首先同样按照上述的方法将将不能形成合适扫描区间的搜索条件暂时移除，得到

      (TRUE AND key2 = 748) OR
      (TRUE AND TRUE) OR
      (TRUE AND (key2 < 8000 OR TRUE));
      

   最后化简只得到一个TRUE，因此扫描区间是$(-\infty ,+\infty )$。也就是说如果使用uk_key2索引则需要全表扫描然后再回表，得不偿失，因此这种情况下是不会使用uk_key2这个索引的。

4. 使用联合索引执行查询时对应扫描区间
   联合索引包含多列，以single_table的idx_key_part联合索引为例，它的排序规则是：先按照key_part1列的值进行排序；如果key_part1列的值相同，则按key_part2列的值进行排序；如果key_part2还相同，则按key_part3进行排序，如图7-10所示。
   
   

   图7-10 idx_key_part索引的示意图
   对于查询Q1来说

   SELECT * FROM single_table WHERE key_part1 = 'a'; # Q1
   

   由于联合索引是按照key_part1进行排序的，因此符合key_part1='a'条件的所有记录肯定相邻，对应的扫描区间是[‘a’,‘a’]。
   对于查询Q2来说

   SELECT * FROM single_table WHERE key_part1='a' AND key_part2='b'; # Q2
   

   联合索引是先按照key_part1进行排序，key_part1相同时再按照key_part2进行排序，因此扫描的区间是[(‘a’, ‘b’), (‘a’, ‘b’)]。
   对于查询Q3来说

   SELECT * FROM single_table WHERE key_part1='a' AND key_part2='b' AND key_part3='c'; # Q3
   

   联合索引排序顺序正好是key_part1、key_part2、key_part3，因此扫描区间是[(‘a’, ‘b’, ‘c’), (‘a’, ‘b’, ‘c’)]。
   对于查询Q5来说

   SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 < 'd'; # Q5
   

   首先根据联合索引定位到key_part1 = 'a'位置，再根据联合索引定位到key_part2 < 'd'位置。
   对于查询Q6来说

   SELECT * FROM single_table WHERE key_part2 = 'a'; # Q6
   

   这里就有不同了，联合索引是先按照key_part1进行排序的，无法根据联合索引定位到key_part2 = 'a'位置，因此这种情况下联合索引失效。
   对于查询Q7来说

   SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part3 = 'c'; # Q7
   

   基于以上的分析知道，key_part1 = 'a'这个搜索条件是可以使用联合索引的，但是当key_part1相同时是按照key_part2排序的，因此key_part3 = 'c'并不能加速搜索。
   对于查询Q8来说

   SELECT * FROM single_table WHERE key_part1 < 'b' AND key_part2 = 'a'; # Q8
   

   这里注意联合索引只有当key_part1值相同时才会按照key_part2继续排序，因此联合索引只能用到key_part1 < 'b'，key_part2 = 'a'这个条件并不能加速搜索过程。
   对于查询Q9来说

   SELECT * FROM single_table WHERE key_part1 <= 'b' AND key_part2 = 'a'; # Q9
   

   显然Q8和Q9非常像，但Q8中条件是key_part1 < 'b'，而Q9中是key_part1 <= 'b'。这里当记录中有key_part1='b'就可以使用key_part2 = 'a'加速扫描。

索引用于排序

在编写查询语句时，经常需要使用ORDER BY子句对查询出来的记录按照某种规则排序。一般情况下只能将记录加载到内存中，然后再用一些排序算法进行排序。有些时候查询结果集太大以至于无法在内存中进行排序，此时需要借助磁盘的空间存放中间结果，在排序操作完成后再把排好序的结果集返回客户端。在MySQL中，在内存或者磁盘中进行排序的方式统称为文件排序(filesort)。但是，如果ORDER BY子句中使用了索引列，就可以避免文件排序，例如

SELECT * FROM single_table ORDER BY key_part1, key_part2, key_part3 LIMIT 10;

ORDER BY子句的顺序正好是联合索引的顺序，联合索引已经帮忙排好序，因此直接取出10条记录即可。

索引用于分组

用于分组和用于排序类似，分组要求GROUP BY子句的顺序和联合索引的顺序要一致。

总结

本章系统分析了B+树索引在查询语句中的使用，需要注意的是，即使索引能够加速搜索过程，但是InnoDB也不一定就会采用索引，这是因为使用索引得到的结果可能需要进行回表的操作，如果回表代价太高，可能还不如直接使用聚簇索引全表扫描性能好。具体使用哪种方式，则是查询优化器的工作，第12章会定量进行分析。

第八章 MySQL的数据目录

数据目录结构

每当新建一个数据库时，MySQL会在数据目录下新建一个同名子目录以及db.opt文件。SHOW VARIABLES LIKE 'datadir';命令可以查看MySQL的数据目录。举例来说，如果执行CREATE DATABASES test_db，在数据目录下会创建test_db目录并在该目录下创建db.opt文件。db.opt文件描述了数据库的一些属性，比如数据库的字符集和比较规则。
在该数据库下新建一个表test_table时，会在数据库目录test_db下新建分别名为test_table.frm和test_table.idb文件，test_table.frm用于描述表结构信息。test_table.idb则存储表的数据信息。

MySQL系统数据库简介

• mysql
  这个数据库相当重要，它存储了MySQL用户账户和权限信息、一些存储过程和事件的定义信息、一些运行过程中产生的日志信息、一些帮助信息以及时区信息等。
• information_schema
  这个数据库保存着MySQL服务器维护的所有其他数据库的信息。比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引等。这些信息并不是真实的用户数据，而是一些描述信息，有时也成为元信息。
• performance_schema
  这个数据库主要保存MySQL服务器运行过程中一些状态信息，算是对MySQL服务器的一个性能监控。它包含的信息有统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等。
• sys
  这个数据库主要是通过视图的形式吧information_schema和performance_schema结合起来，让开发人员更方便了解MySQL服务器的性能信息。

第九章 独立表空间结构

说明

之前的几章都是按照《MySQL是怎样运行的》一书的结构与内容进行笔记记录，但是这一章并没有按照原文进行介绍，原因是个人觉得还是需要对独立表空间有宏观认识，再进行微观介绍效果可能好一些。这里笔记中有一些我自己画的图，为了跟原文区分开，我自己画的图标号从101开始。

区和段的概念

表空间中的页太多了，InnoDB为了更好的管理这些页面，提出了区(Extent)的概念，对于16KB的页来说，连续的64个页就是一个区，于是一个区默认是1MB(16KB*64=1MB)，无论是系统表空间还是独立表空间，都可以看成是由若干个连续的区组成的，每256个区划分为1组，如图9-3所示。

图9-3 表空间物理结构

由图9-3可以看到得到如下信息：
第一组最开始3个页面的类型是固定的，分别如下

• FSP_HDR：用于描述整个表空间的整体属性以及本组中所有的区的属性，整个表空间只有一个FSP_HDR类型的页面。
• IBUF_BITMAP：这个类型的页面用于存储Change Buffer的信息。
• INODE：这个类型的页面存储了许多INODE Entry的数据结构，后文详述。

其余各组最开始两个页面类型是规定的，介绍如下：

• XDFS: 全称是extent descriptor，用来登记本组256个区的属性。跟FSP_HDR作用差不多，只是FSP_HDR还会额外存储表空间的属性。
• IBUF_BITMAP：上文已经介绍，不再描述。
  

图9-101 独立表空间逻辑结构

上文提到InnoDB的表空间是由若干个区组成的，为了便于管理这些区，InnoDB又提出段的概念，InnoDB将存储所有用户记录的区的集合称为Leaf node segment，将存储所有页目录的区的集合称为Non-leaf node segment，此外，还有Rollback segment，这里暂时不做介绍，如图9-101所示。
为什么要提出区的概念呢？其实仅仅有页的概念就可以完全表示索引，引入区和段主要是因为数据量过大时不好管理。当数据量比较小时，只需要申请几个页就可以表示一个索引，但是当数据量比较大时，申请页的数量也会很多，而页之间是通过双向链表联系起来的，如果双向链表相邻位置的两个页物理地址不连续，对于传统机械硬盘来说，需要重新定义磁头位置，也就是会产生随机I/O，这样会影响磁盘的性能。所以，当数据量较大时，尽量保证申请的页在物理位置上也是连续的，区(Extent)由此而生，一个区是由连续的64个页组成的。当表中数据量非常大时，为某个索引分配空间就不再以页为单位，而是以区为单位分配。这样可能造成一点点空间的浪费(数据不足以填满整个区)，但是采用顺序I/O，可以消除很多随机I/O带来的性能损耗。
从之前的介绍可以知道，扫描用户记录可能会用到页的next指针，如果将存储用户记录的页(B+树的叶子节点)和存储目录的页(B+树的非叶子节点)放到物理上相邻的位置中，也有可能产生随机I/O，例如，连续的1，2，3号页中，2号页是存储目录的，1，3号页是存储用户记录的，虽然1的next指针指向3，但由于1号页和3号页物理上不连续，还是会产生随机I/O，因此，尽量将数据页和目录也分开是一个比较好的选择。InnoDB将存放用户记录的区的集合称为Leaf node segment，将存放目录页的区的集合称为Non-leaf node segment。
前文中提到，默认情况下一个区占1MB空间，而一个索引需要分配两个段(分别用于存储数据和目录)，即使每个段只有一个区，那么一个索引至少也需要2MB空间，这对于数据量特别少的表来说空间浪费太严重了，InnoDB为了解决这个问题，提出了碎片区的概念。
6. 在刚开始向表中插入数据时，段是从某个碎片区以单个页面为单位来分配存储空间的；
7. 当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间(原来在碎片区的页面不会被复制到新申请的完整的区中)；

所以，精确地说，段是某些零散页面以及一些完整的区的集合。

区的分类

InnoDB独立表空间的区大概分为4类。

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

前3种类型的区是直属于表空间的，而FSEG是附属于某个段的。
为了便于管理这些区，InnoDB设计了一种XDES Entry(Extent Descriptor Entry)的结构，每一个区都维护这样的一个结构，结构的信息如图9-4。

图9-4 XDES Entry结构示意图

从图9-4中可以看到XDES Entry结构有40字节，主要包括4部分。

• Segment ID(8字节)
  每个段都有唯一编号ID，这个字段就是标识当前区所属的段，如果当前区直属于表结构，该字段无意义。
• List Node(12字节)
  这个部分可以将若干XDES Entry连接成一个双向链表
• State(4字节)
  该字段表明区的状态，取值有FREE、FREE_FRAG、FULL_FRAG、FSEG，含义前文已述。
• Page State Bitmap(16字节)
  这部分共16字节，也就是128位。一个区默认64个页，每两位对应一个页。这2个位中第一个位表示对应的页是否是空闲的，第2位暂时没用。

到现在提出了很多概念——区、段、碎片区、附属于段的区、XDFS Entry结构，目的仅仅是为了减少随机I/O，而又不至于上数据量少的表浪费空间，InnoDB为了方便管理不同状态的区，通过List Node将不同状态的区连接成双向链表，上文介绍了区的状态有FREE、FREE_FRAG、FULL_FRAG、FSEG这四种，InnoDB只将FREE、FREE_FRAG、FULL_FRAG三种状态的区构成各自的双向链表，分别名为FREE链表、FREE_FRAG链表和FULL_FRAG链表。FSEG状态的区因为附属于某个段，不再直接附属于独立表空间，段也为状态为FSEG的区构建了3个分别名为FREE链表、NOT_FULL链表和FULL链表。现在，将介绍向表中插入数据申请新页面的过程。
当段中数据较少时，会首先查询FREE_FRAG链表的头结点，如果FREE_FRAG头结点不为空，说明表空间尚有FREE_FRAG状态的区，从这个头节点对应的区中取一些零散的页来插入数据。当这个节点对应的区没有空闲页面时，将该节点的State字段值由FREE_FRAG改为FULL_FRAG，并将该节点从FREE_FRAG链表移除迁移到FULL_FRAG中。当段中的数据已经占满32个零散的页后，就直接申请完整的区来插入数据。如果在一开始FREE_FRAG头结点为空，说明当前表空间中没有状态为FREE_FRAG的区，则从FREE链表中取出头结点插入数据，并将该头结点对应的区State属性由FREE改为FREE_FRAG，同时改节点从FREE链表中移除并插入到FREE_FRAG中。
对于下面的表

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

一个索引对应两个段，一个数据段，一个目录段。表t共有2个索引，分别是c1主键索引和c2二级索引，因此该表共需要4个段，而每个段都要维护FREE链表、NOT_FULL链表和FULL链表三个链表，因此表t一共有12个链表。再加上前文说过的直属于表空间的FREE链表、FREE_FRAG链表和FULL_FRAG链表3个链表，整个独立表空间共需要维护15个链表。
如何找到这些链表呢？InnoDB设计了List Base Node(链表基节点)的结构，如图9-6所示。

图9-6 List Base Node的结构示意图

其中，List Length表示该链表一共有多少个节点，另外还有指向链表头尾节点的指针。

段的结构

上文详细说了区和段的概念及联系，对段有了一个较为简单的认识，段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，它是由若干零散的页面以及一些完整的区组成。像每个区都有XDES Entry来记录区中的属性一样，每个段都定义了一个INODE Entry结构，如图9-7所示。

图9-7 INODE Entry结构示意图

图9-7展示了INODE Entry的结构，各部分含义如下

• Segment ID：这个INODE Entry结构对应段的编号
• NOT_FULL_N_USED：在NOT_FULL链表中已经使用了多少个页面
• 3个List Base Node：分别为段的FREE链表、NOT_FULL链表、FULL链表定义了List Base Node。
• Magic Number：用来标记这个INODE Entry是否已经被初始化(即把各个字段的值都填进去了)。Magic Number=97937874(这个数字没有什么特殊含义，只是个规定)表示已经被初始化。
• Fragment Array Entry：前面说过很多次，段是由一些零散页面和一些完整的区的集合，每个Fragment Array Entry结构对应着一个零散的页面，这个结构供4字节，表示一些零散页面的页号。

各类型页面详细情况

图9-3给出了表空间结构，每256个区组成一个组，每个组第一个区的前几页存储了一些元数据信息，现在对其一一详细介绍。

FSP_HDR类型

FSP_HDR这个页是第一个组的第一个页面，也是表空间的第一个页面，页号为0。它存储了表空间的整体属性以及第一个组256个区对应的XDES Entry结构，如图9-8所示。

图9-8 FSP_HDR 类型的页结构示意图

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

这里着重介绍File Space Header(图9-9)XDES Entry这两部分。

图9-9 File Space Header结构示意图

图9-9给出了File Space Header的结构，这里对其中的若干重要字段进行介绍。

• Space ID(4字节)：表空间ID
• Not Used(4字节)：未被实用，忽略
• Size(4字节)：当前表空间拥有的页面数
• FREE Limit：尚未被初始化的最小页号，大于或等于这个页号的区对应的XDES Entry结构未被加入到FREE链表
• Space Flags：表空间一些占用存储空间比较小的属性
• FRAG_N_USED： FREE_FRAG链表已使用的页面数量
• Next Unused Segment ID：当前表空间中下一个未使用的Segment ID，每当分配好一个Segment ID段后，该属性自增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链表基节点。
• List Base Node for SEG_INODES_FULL List ：SEG_INODES_FULL链表基节点
• List Base Node for SEG_INODES_FREE List：SEG_INODES_FREE链表基节点

XDES类型

表空间每256个区分为一组，每组需要有一个页记录整个组的元数据信息，这个页的类型就是XDES类型，它的结构和FSP_HDR类型非常相似，如图9-10所示。

图9-10 XDES类型的页的结构示意图

从图9-10中可以看到，相比于FSP_HDR来说，XDES就是缺少了File Space Header内容(记录整个表的元数据)。

INODE类型

InnoDB为每个索引定义了两个段，而且为某些特殊功能定义了特殊的段，为了方便管理这些段，每个段维护了一个INODE Entry结构，INODE类型的页就是为了存储INODE Entry结构而存在的，如图9-11。

图9-11 INODE类型的页结构示意图

第十章 单表访问方法

MySQL将MySQL执行查询语句的方式成为访问方法或者访问类型。同一个查询语句可以使用不同的访问方法来执行，虽然最后的查询结果都是一样的，但是不同的执行方式花费的时间成本可能差距甚大。
为了叙述方便，这里还是给出一个具体的表

CREATE TABLE single_table(
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1(key1),
UNIQUE KEY uk_key2(key2),
KEY idx_key3(key3),
KEY idx_key_part(key_part1, key_part2, key_part3)
)Engine=InnoDB CHARSET=utf8;

为single_table表建立了1个聚簇索引和4个二级索引，分别是

• 为主键id建立了聚簇索引
• 为key1列建立了二级索引
• 为key2列建立了二级索引，而且该索引还是唯一二级索引
• 为key3列建立了二级索引
• 为key_part1, key_part2, key_part3建立了二级索引，而且这是一个联合索引

访问方法

• const
  MySQL将通过主键或者唯一二级索引列来定位一条记录的访问方法称为const

  SELECT * FROM single_table WHERE id = 1438; # Q1
  SELECT * FROM single_table WHERE key2 = 3841; # Q2
  SELECT * FROM single_table WHERE key2 IS NULL; # Q3 访问方法不是const
  

  Q1和Q2这两个查询都是const，因为它们是通过主键或者唯一二级索引定位一条记录，而Q3不是const，因为key2是唯一二级索引，但是唯一二级索引并不限制NULL的数量，所以Q3可能访问到多条记录，Q3查询最多使用ref访问方法。

• ref
  搜索条件为二级索引列与常数进行等值比较，形成的扫描区间为单点扫描区间，采用二级索引来执行查询的访问方法称为ref，如Q4

  SELECT * FROM single_table WHERE key1 = 'abc'; # Q4
  SELECT * FROM single_table WHERE key_part1 = 'god like'; # Q5
  SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary'; # Q6
  SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill'; # Q7
  

  普通二级索引并不限制索引列值的唯一性，所以这种单点扫描区间最终得到的记录可能有多条，此时使用二级索引执行查询的代价就取决于该扫描区间的记录条数。同时注意，采用二级索引来执行查询时，其实每获取一条二级索引记录，就会立即对齐执行回表的操作(这里MySQL其实进行了一定的优化，先获取一批符合条件的记录，然后通过主键排序再进行回表，减少了随机I/O的时间)。
  对于联合索引来说，只要最左边连续的列是与常数进行等值比较，就可以采用ref访问方法。如Q5-Q7

• ref_or_null
  有时，我们不仅想找出某个二级索引列的值等于某个常数的记录，而且还想把该列中值为NULL的记录也找出来，比如

  SELECT * FROM single_table WHERE key1 = 'abc' OR key1 is NULL; # Q8
  

  对应的扫描区间是[NULL, NULL]$\bigcup$[‘abc’, ‘abc’]，执行过程如图10-4所示。
  

  图10-4 ref_or_null访问方法执行过程 值为NULL的记录会被放到索引的最左边，ref_or_null访问方法只是比ref访问方法多扫描了一些值为NULL的二级索引记录。

• range
  使用索引执行查询时，对应扫描区间为若干个单点扫描区间或者范围扫描区间的访问方法称为range。

  SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >=38 AND key2 <= 79); # Q9
  

  如果使用idx_key2执行该查询，扫描区间是[38, 79]$\bigcup$[1438, 1438]$\bigcup$[6328, 6328]，不过扫描区间是$(-\infty ,+\infty )$的访问方法不能称为range。

• index
  扫描全部二级索引记录的访问方法称为index访问方法，如

  SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';# Q10
  SELECT * FROM single_table ORDER BY id; # Q11
  

  由于key_part2并不是联合索引idx_key_part的索引列中最左边的列，所以无法形成合适范围区间来减少需要扫描的记录数量，但是这个查询条件符合下面两个条件：

  • 它的查询列只有key_part1, key_part2, key_part3三列，而索引idx_key_part又包含这三列；
  • 搜索条件中只有key_part2，而索引idx_key_part包含这一列。

  也就是说可以直接遍历idx_key_part索引记录，然后逐一判断是否符合key_part2 = 'abc'条件。二级索引记录比聚簇索引小得多(聚簇索引需要存储用户定义的所有列以及隐藏列，而联合二级索引只需要存放索引列和主键)，这个过程也不需要回表的操作，所以直接扫描联合索引比直接扫描全部聚簇索引成本要小得多。
  另外，当通过全表扫描对使用InnoDB引擎的表执行查询时，如果添加了"ORDER BY 主键语句"，那么该语句在执行时也会被认为是index访问方法，如Q11。

• all
  使用全表扫描执行查询的访问方法称为all访问方法。

• 索引合并
  使用多个索引来完成一次查询的执行方法称为索引合并，具体的索引合并方法有以下3种。

  • Intersection索引合并

    SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b'; # Q12
    

    使用全表扫描当然可以完成任务，不过由于搜索条件涉及key1和key3列，因此可以使用以下两种方案。

    • 方案1：使用idx_key1索引执行查询
    • 方案2：使用idx_key3索引执行查询

    这两种方案差别不大，这里提出一种新的方案，同时使用idx_key1索引和idx_key3索引。使用idx_key1索引查找key1 = 'a'的主键集合s1；使用idx_key3索引查找key3 = 'c'的主键集合s2；然后对s1和s2求交集后再执行回表操作。

  • Union索引合并
    有了Intersection索引合并的基础，Union索引合并就比较容易理解了，这里直接给出查询语句

    SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'; # Q13
    
    SELECT * FROM single_table WHERE key1 > 'a' AND key3 = 'b'; # Q14,idx_key1所得记录不是按照主键排序的，因此不能使用Intersection索引合并
    SELECT * FROM single_table WHERE key1 > 'a' OR key3 = 'b';  # Q15,idx_key1所得记录不是按照主键排序的，因此不能使用Union索引合并
    SELECT * FROM single_table WHERE key1 = 'a' OR id > 9000;  # Q16 可以使用Union索引合并
    

    需要注意的是，如果使用某个二级索引执行查询时，对应扫描区间中二级索引记录不是按照主键值进行排序的，则不可以使用Intersection索引合并和Union索引合并，如Q14，Q15；但是对于Q16， 使用idx_key1索引查询key1 = 'a'得到的结果是按主键排序的，id > 9000也是按主键排序的，因此Q16是可以使用Union索引合并。

  • Sort-Union索引合并
    先将从各个索引中扫描到的记录主键值进行排序，再按照执行Union索引合并的方式执行查询的查询方式成为Sort-Union索引合并。
    Union索引合并使用条件很苛刻，它必须保证从各个索引扫描到的记录主键值是有序的，比如Q15，对于Q15，可以使用idx_key1查询key1 > 'a'得到的记录按照主键集合手动排序；然后再进行合并。
    注意，InnoDB有Sort-Union索引合并，但是没有Sort-Intersection索引合并。