InnoDB文件物理结构解析2 - FIL_PAGE_INDEX

1. 关于索引组织表

InnoDB使用的是索引组织表(IOT)的方式存储表记录，索引组织表以主键构建一个B-tree的数据结构来存储行记录，行记录存储在树的叶节点内。这与Oracle数据库是不同的，Oracle数据库默认创建的表是堆组织表(HOT)，HOT记录按堆数据结构进行存储。在InnoDB中，我们将存储行记录的B-tree索引称为Clustered Index, 而表的相关普通B-tree索引(非主键索引)称为Secondary Index。

The data in each table is divided into pages. The pages that make up each table are arranged in a tree data structure called a B-tree index. Table data and secondary indexes both use this type of structure. The B-tree index that represents an entire table is known as the clustered index, which is organized according to the primary key columns. The nodes of a clustered index data structure contain the values of all columns in the row. The nodes of a secondary index structure contain the values of index columns and primary key columns.

存储Clustered Index的节点(页)与Secondary Index的的节点的内容是不一样的，树结构，包含页节点(Leaf)和非叶节点(Non-Leaf)，前文提到，InnoDB中表的记录和相关索引都使用FIL_PAGE_INDEX Page存储，要解析一个FIL_PAGE_INDEX页的第一步就是要确定这个页存储的是什么数据。

1. FIL_PAGE_INDEX Page的结构

前文提到，表和其相关索引的数据是存储在FIL_PAGE_INDEX页的，那么我们首先看看FIL_PAGE_INDEX的页结构：

    0+--------------------------+| FIL Header (38)          |38+--------------------------+| INDEX Header (36)        |74+--------------------------+| FSEG Header (20)         |94+--------------------------+| System Records (26)      |120+--------------------------+| User Records             | // Records are un-ordered physically, 物理无序(不按主键顺序)存放, 类似于堆表|                          | // 但会由一个单向链表(singly-linked)将用户记录按主键有序串起来。+--------Heap Top----------+ // Heap Top是Page空间使用的高水位(HWM)| Free Space               |+--------------------------+| Page Directory           | // The page directory grows downwards from the FIL trailer in ascending order by key. //空间占用从后往前增长|                          | // The number of entries is stored in the INDEX header. 数量记录在INDEX Header->PAGE_N_DIR_SLOTS中。
16376+--------------------------+| FIL Trailer (8)          |
16384+--------------------------+
* 前面的数字是在Page内的偏移(bytes)

解析的第一步需要解析FIL_PAGE_INDEX的INDEX_Header结构:

38 +----------------------+ | PAGE_N_DIR_SLOTS (2) |
40 +----------------------+| PAGE_HEAP_TOP (2)    |
42 +----------------------+| PAGE_N_HEAP (2)      |
46 +----------------------+| PAGE_FREE (2)        |
48 +----------------------+ | PAGE_GARBAGE (2)     |
50 +----------------------+| PAGE_LAST_INSERT (2) |
52 +----------------------+| PAGE_DIRECTION (2)   |
52 +----------------------+| PAGE_N_DIRECTION (2) |
54 +----------------------+| PAGE_N_RECS (2)      |
56 +----------------------+| PAGE_MAX_TRX_ID (2)  |
64 +----------------------+| PAGE_LEVEL (2)       |
66 +----------------------+ | PAGE_INDEX_ID (2)    |
73 +----------------------+ * 前面的数字是在Page内的偏移(bytes)

通过解析INDEX_Header，我们就可以知道FIL_PAGE_INDEX存储的是哪个索引，记录类型，page level, …：

public class IdxPage1 {public static void main(String[] args) throws IOException, Exception {String fileName = "D:\\Data\\mysql\\8.0.18\\data\\sakila\\film.ibd";try (IbdFileParser parser = new IbdFileParser(fileName)) {List<Long> pageNums = parser.getPageTypeMap().get(FilHeader.FIL_PAGE_INDEX);StringBuilder buff = new StringBuilder();buff.append(" PAGE       PAGE_TYPE LEVEL INDEX_ID   PAGE_PREV   PAGE_NEXT\n").append("----- --------------- ----- -------- ----------- -----------\n");for (long pageNum : pageNums) {IndexPage indexPage = (IndexPage) parser.getPage(pageNum);FilHeader filHeader = indexPage.getFilHeader();IndexHeader indexHeader = indexPage.getIndexHeader();buff.append(String.format("%5d ", pageNum)).append(String.format("%15s ", filHeader.getPageTypeName())).append(String.format("%5d ", indexHeader.getPageLevel())).append(String.format("%8d ", indexHeader.getIndexId())).append(String.format("%11d ", filHeader.getPreviousPage())).append(String.format("%11d ", filHeader.getNextPage())).append("\n");}System.out.println(buff);}}
}
 程序输入:PAGE       PAGE_TYPE LEVEL INDEX_ID   PAGE_PREV   PAGE_NEXT
----- --------------- ----- -------- ----------- -----------4  FIL_PAGE_INDEX     1      596  4294967295  4294967295 5  FIL_PAGE_INDEX     1      597  4294967295  4294967295 6  FIL_PAGE_INDEX     0      598  4294967295  4294967295 7  FIL_PAGE_INDEX     0      599  4294967295  4294967295 8  FIL_PAGE_INDEX     0      596  4294967295           9 9  FIL_PAGE_INDEX     0      596           8          10 10  FIL_PAGE_INDEX     0      596           9          11 11  FIL_PAGE_INDEX     0      596          10          12 12  FIL_PAGE_INDEX     0      596          11          13 13  FIL_PAGE_INDEX     0      596          12          14 14  FIL_PAGE_INDEX     0      596          13          15 15  FIL_PAGE_INDEX     0      596          14          18 16  FIL_PAGE_INDEX     0      597  4294967295          17 17  FIL_PAGE_INDEX     0      597          16  4294967295 18  FIL_PAGE_INDEX     0      596          15          19 19  FIL_PAGE_INDEX     0      596          18          20 20  FIL_PAGE_INDEX     0      596          19  4294967295 

首先我们观察INDEX_ID，这是索引在InnoDB中的唯一编号，通过输出我们可以看到sakila.film表有4个索引，编号分别为: 596, 597, 598和599，INDEX_ID对应的页存储的就是该索引的数据。我们可以在MySQL中查到对应信息:

selectidx.space space_id,idx.page_no,index_id,idx.name index_name,tab.name table_name
from innodb_indexes idx, innodb_tables tab
whereidx.table_id = tab.table_idand index_id in(596, 597, 598, 599);--语句输出: 
space_id|page_no|index_id|index_name                 |table_name |
--------+-------+--------+---------------------------+-----------+387|      4|     596|PRIMARY                    |sakila/film|387|      5|     597|idx_title                  |sakila/film|387|      6|     598|idx_fk_language_id         |sakila/film|387|      7|     599|idx_fk_original_language_id|sakila/film|

这与我们解析的结果是一致的。结合PAGE_LEVEL和PAGE_PREV/PAGE_NEXT信息可以得到整棵B-Tree的基本层次结构, 以主键(PRIMARY, 596)为例，我们可以看到596号索引最大的page_level为1，位于page(4)， 最大的level代表着树的根节点(root)， page4的page_next和page_prev都为0xffffffff(4294967295)，可以理解为指向自己或者终结。page(8)的page_prev为0xffffffff，level=0，代表叶节点，是叶节点的最左边节点。page(20)的page_nex为0xffffff, level=0, 说明是叶子节点最右边的节点。所以根据解析输出，我们可以描绘树的基本结构：

Level 1:                         page(4)|+---------+----------+---------+--------+/         /           |          \        \
Level 0:  page(8) <-> page(9) <-> page(10) <-> ... <-> page(20) 

后续文章将开始讨FIL_PAGE_INDEX页内的记录内容。