Why Does InnoDB Need Dictionary
InnoDB dictionary is a set of internal tables InnoDB uses to maintain various information about user tables. It serves as API between a human and the database. While the humans refer tables by their names, InnoDB works with integer identifiers. The dictionary stores correspondence between table name and index_id.
The dictionary tables are normal InnoDB tables, but they’re not visible for a user. However some versions of MySQL provide read-only access to the dictionary in information_schema database.
The dictionary is stored in ibdata1. Root page of SYS_TABLES, for example, has id 8, so it’s eighth page from the beginning of ibdata1.
The dictionary pages are in REDUNDANT format even if you use MySQL 5.6. More about record formats I will write in future posts, I hope. For now it’s enough to mention REDUNDANT is the oldest record format. It was available since 4.0 and was the only format by that time.
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;
All dictionary tables have predefined hard-coded index_ids. No surprise, as long as InnoDB doesn’t have a dictionary for the dictionary :). SYS_TABLES has index_id 0-1. Root page of SYS_TABLES is page id 8.
NAME is human readable table name. Well, database name and table name. For example, for table `sakila`.`actor` NAME will be “sakila/actor”.
ID is table identifier. Just an integer number. The table is referenced by this table_id in other dictionary tables.
N_COLS is number of columns in the table.
I will pretend TYPE, MIX_ID, MIX_LEN and CLUSTER_NAME are not important. Actually, I have no idea what these fields are. Anyway, they were never used for data recovery, so let’s leave them for future investigations.
SPACE is an identifier of a tablespace. Thus ibdata1 is SPACE 0, ibdata2 is SPACE 1, Every ibd file has its own SPACE identifier. SPACE tells in what tablespace the table’s indexes are stored.
Sample record for table 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” – table name obviously. 61 – table id. 4 is number of fields in the table:
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 is SPACE identifier. 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)
Another important dictionary table is SYS_INDEXES. It stores information about InnoDB indexes.
Its index_id is 0-3 and the root page is stored in page 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;
Fields of the table:
TABLE_ID is table identifier. It’s one from SYS_TABLES.
ID is index identifier. This is very important field. The matter is every InnoDB page contains in the header index_id of the index it belongs to. So if we want to recover some table, we need to find all pages that belong to the PRIMARY index of the table which means we need all pages with some specific index_id.
NAME is the name of an index. Table sakila.actor has two indexes: PRIMARY and idx_actor_last_name. So these are names in SYS_INDEXES.
N_FIELDS is the number of fields in an index. idx_actor_last_name has one field, so is it in SYS_INDEXES.
TYPE is not important field
SPACE is InnoDB tablespace identifier where the index is stored. Probably InnoDB developers foresaw possibility to store indexes in different files, but for current MySQL versions it’s the same as in SYS_TABLES.
PAGE_NO is an identifier of root page of the index. As you know InnoDB stores indexes in B+tree data structure. So root node of the B+tree is the root page identified by this field.
SYS_COLUMNS describes individual fields in a table. It is stored as 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 is table identifier, it comes from SYS_TABLES
POS is relative position of the field in the table.
NAME is the name of the field.
MTYPE and PRTYPE encode MySQL type of the field. InnoDB started its history as a separate product, not related to MySQL. These two fields is heritage from that time. There is no strict correspondence between MySQL and InnoDB type. InnoDB uses its own type that suits the best for the particular MySQL type. Here encoding and collation are stored as well.
LEN is length of the field.
PREC is precision for some MySQL types.
Isn’t it interesting to see example of the table for sakila.actor? Here it goes. Remember, table_id is 61? We know that from 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
As we can see, smallint is two bytes, timestamp is four bytes. And varchar(45) is 135 bytes. Apparently this is the maximum size, not actual size of a field which is different for every record. But why 135, not 45. Because the field is utf8, where each character may use up to three bytes. 3*45 gives 135.
The last table for today(but not least) is SYS_FIELDS. It’s stored in index_id 0-4.
It lists individual fields on all indexes.
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;
The table is quite simple.
INDEX_ID is index identifier that we can find in SYS_INDEXES.
POS is position of the field in the index.
And COL_NAME is field name.
Why do we care?
Why do we need InnoDB dictionary anyway?
There are two reasons. First, if we need to recover table A we need to know index_id of its PRIMARY index. The easiest way to learn it is InnoDB dictionary.
Second, as might notice the InnoDB dictionary stores some information about field names and types. So in case we don’t know the table structure we can recover it from the dictionary.by