MySQL InnoDB 数据字典介绍

为什么InnoDB 需要字典

 

 

InnoDB字典是InnoDB用于维护用户表的各种信息的一组内部表。它作为人类和数据库之间的API。人类根据他们的名字参考表，而InnoDB通过整数标识符。字典存储表名和index_id之间的对应关系。

字典表是普通的InnoDB表，但对用户不可见。然而一些版本的MySQL在information_schema数据库中提供的字典只读访问。

字典被存储在ibdata1中。例如，SYS_TABLES的root页有id 8，所以它是ibdata1开头的第八页。

即使是MySQL 5.6，字典页也是冗余格式。我可能会在以后写更多关于记录格式的文章。现在只要知道冗余度是最早的记录格式就行了。它自4.0版本起可用，是当时唯一的格式。

 

 

SYS_TABLES

 

 

CREATE TABLE `SYS_TABLES` (
`NAME` varchar(255) NOT NULL DEFAULT '',
`ID` bigint(20) unsigned NOT NULL DEFAULT '0',
`N_COLS` int(10) DEFAULT NULL,
`TYPE` int(10) unsigned DEFAULT NULL,
`MIX_ID` bigint(20) unsigned DEFAULT NULL,
`MIX_LEN` int(10) unsigned DEFAULT NULL,
`CLUSTER_NAME` varchar(255) DEFAULT NULL,
`SPACE` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

 

所有字典表具有预定义的硬编码index_ids。这并不奇怪，只要InnoDB没有字典的字典:)。 SYS_TABLES有index_id 0-1。 SYS_TABLES的root页是页id 8。

 

NAME 是人类可读的表名。数据库名和表名。例如，对于表 sakila.actor ，NAME 就是 “sakila/actor”。

ID 为表的标识符。它是一个整数。在其他字典表中，表通过table_id 被引用。

N_COLS 是表中的列数。

 

我假设 TYPE, MIX_ID, MIX_LEN and CLUSTER_NAME 不重要。其实，我不知道这些字段是什么。不管怎样，它们从不用于数据恢复，所以我们把它们放到之后再研究。

SPACE 是表空间的标识符。因此ibdata1是SPACE 0，ibdata2是SPACE 1，每​​一个ibd文件都有自己的SPACE标识符。 SPACE表示表的索引存储在哪个表空间。

 

表sakila.actor的样本记录：

 

 

# ./bin/constraints_parser.SYS_TABLES -4f pages-1402815586/FIL_PAGE_INDEX/0-1 | grep sakila/actor
"sakila/actor" 61 4 1 0 80 "" 47
“sakila/actor” – 显然是表名. 61 – 表 id。4 是表中的字段数：

mysql> show create table sakila.actor\G
*************************** 1. row ***************************
Table: actor
Create Table: CREATE TABLE `actor` (
`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`),
KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8

47 是SPACE 标识符。如果是0，如果innodb_file_per_table 被禁用。这不是在我的环境中：

If would be 0 if innodb_file_per_table were disabled. It’s not in my environment:

mysql> show variables like 'innodb_file_per_table';

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

 

 

SYS_INDEXES

 

 

另一个重要的字典表SYS_INDEXES。它存储有关InnoDB索引的信息。

它的 index_id 是 0-3 ，且root 页储存在页11。

 

 

CREATE TABLE `SYS_INDEXES` (
`TABLE_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
`ID` bigint(20) unsigned NOT NULL DEFAULT '0',
`NAME` varchar(120) DEFAULT NULL,
`N_FIELDS` int(10) unsigned DEFAULT NULL,
`TYPE` int(10) unsigned DEFAULT NULL,
`SPACE` int(10) unsigned DEFAULT NULL,
`PAGE_NO` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`TABLE_ID`,`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

表中的字段：

 

TABLE_ID 是表标识符。它是一个来自SYS_TABLES。

ID 是索引标识符。这是非常重要的字段。这是每个InnoDB页包含在其所属索引的头index_id中包含的。因此，如果我们想恢复一些表，我们需要找到属于表的PRIMARY索引，这意味着我们需要有一些具体的index_id的所有页。NAME 是索引名称。表sakila.actor 有两个索引：PRIMARY 和idx_actor_last_name。所以这些就是SYS_INDEXES 中的名称。

N_FIELDS 是索引中字段数。idx_actor_last_name 有一个字段，在SYS_INDEXES中也是。

TYPE 不是重要的字段。

SPACE 是InnoDB 表空间标识符，索引储存在其中。也许InnoDB 开发者遇见到在不同文件储存索引的可能性，当对目前MySQL版本，它与在SYS_TABLES中相同。

PAGE_NO 是所以的root页的标识符。我们知道InnoDB 以 B+tree 数据结构储存索引。所以B+tree 的root节点是由这个字段标识的root 页。

 

 

SYS_COLUMNS

 

SYS_COLUMNS 描述在表中的独立字段。它作为index_id 0-2储存。

 

CREATE TABLE `SYS_COLUMNS` (
`TABLE_ID` bigint(20) unsigned NOT NULL,
`POS` int(10) unsigned NOT NULL,
`NAME` varchar(255) DEFAULT NULL,
`MTYPE` int(10) unsigned DEFAULT NULL,
`PRTYPE` int(10) unsigned DEFAULT NULL,
`LEN` int(10) unsigned DEFAULT NULL,
`PREC` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`TABLE_ID`,`POS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

 

TABLE_ID 是表标识符，它来自SYS_TABLES

 

POS 是在表中的字段的相对位置。

 

NAME 是字段名称。

 

MTYPE 和 PRTYPE 编码MySQL字段类型。InnoDB作为单独的产品开始了它的历史，与MySQL不相关。这两个字段是那个时候的产物。MySQL和InnoDB类型之间没有严格的对应关系。 InnoDB使用最适合特定MySQL类型的类型。这里的编码和归类也被存储。

LEN 是字段长度。

 

PREC 是一些MySQL类型的精度。

 

看到表 sakila.actor的示例是不是很有趣？就在这里。记得table_id 是61？ 我们从SYS_TABLES得知。

 

 

61      0       "actor\_id"     6       1794    2       0
61      1       "first\_name"   12      2162959 135     0
61      2       "last\_name"    12      2162959 135     0
61      3       "last\_update"  3       525575  4       0

 

 

我们可以看到，smallint是两个字节，时间戳是四个字节。varchar（45）是135个字节。显然，这是最大的尺寸，不是字段的实际大小，它在每个记录都应不同。但是，为什么是135，而不是45。因为字段是UTF8，每个字符最多可以使用3个字节。 3*45等于 135。

 

SYS_FIELDS

今天最后一个表（也很重要）是 SYS_FIELDS。它储存在index_id 0-4。
它列出在所有索引的单个字段。

 

 

CREATE TABLE `SYS_FIELDS` (
`INDEX_ID` bigint(20) unsigned NOT NULL,
`POS` int(10) unsigned NOT NULL,
`COL_NAME` varchar(255) DEFAULT NULL,
PRIMARY KEY (`INDEX_ID`,`POS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

 

该表非常简单。

INDEX_ID 是索引标识符，可以在SYS_INDEXES中找到。

POS 是在索引中的字段的位置。

COL_NAME 是字段名。

为什么关注？

我们究竟为什么需要InnoDB 字典？

有两个原因。首先，如果我们需要恢复表A，我们需要知道它的PRIMARY索引的index_id。学习它最简单的方法是InnoDB字典。

其次，你可能会注意到InnoDB字典储存有关字段名称和类型的信息。所以，如果我们不知道的表结构，我们可以从字典恢复。