InnoDB data Dictionary detailed-system table

1. Introduction

In InnoDB, the system tables are not actually visible. There are 4 most basic system tables that store the table's metadata: tables, columns, indexes, indexed columns, and so on. The 4 tables were sys_tables, Sys_columns, Sys_indexes, Sys_fields. The following are respectively

2, Sys_tables

Stores all tables with InnoDB as the storage engine and one table for each record. The columns for this table are:

NAME：表名ID：表的ID号N_COLS：表的列数TYPE：表的存储类型，包括记录的格式、压缩等信息MIX_ID、MIX_LEN、CLUSTER_NAME：暂时未用SPACE：这个表所在的表空间ID。

This table has a clustered index on name with a level one or two index on the ID.

3, Sys_columns

Stores column information, one record for each column. Table columns:

TABLE_ID：该列所属表的IDPOS：该列在表中第几列NAME：列名MTYPE：列的主数据类型PRTYPE：列的精确数据类型LEN：列数据长度，不包括varchar类型，因为该类型在记录里面已经存储了PREC：列数据的精度。

The primary key column of the table is (Table_id,pos)

4, Sys_indexes

Stores index information, one index for each record.

TABLE_ID：该列所属表的IDID：索引的索引号NAME：索引名N_FIELDS：索引包含的列数TYPE：索引类型，包括聚集索引、唯一索引、DICT_UNIVERSAL、DICT_IBUFSPACE：索引所在表的表空间IDPAGE_NO：该索引对应的B+树的根页面号。

The table primary key (Table_id,id)

5, Sys_fields

Stores the defined index columns, one index column for each record:

INDEX_ID：该列所在的索引IDPOS：该列在索引中第几列COL_NAME：列名

The table primary key is (Index_id,pos)

6, the Data Dictionary table root page location, in the InnoDB using the system tablespace No. 0 File number 7th page storage. This page stores the 5 root page numbers for the 4 tables above, as well as the next table ID value, the next index ID value, the next Tablespace ID value, and the ROWID. The 7th page structure looks like this:

Dict0boot.h::

/* Dictionary header offsets */  #define DICT_HDR_ROW_ID     0   /* The latest assigned row id */  #define DICT_HDR_TABLE_ID   8   /* The latest assigned table id */  #define DICT_HDR_INDEX_ID   16  /* The latest assigned index id */  #define DICT_HDR_MAX_SPACE_ID   24  /* The latest assigned space id,or 0*/  #define DICT_HDR_MIX_ID_LOW 28  /* Obsolete,always DICT_HDR_FIRST_ID*/  #define DICT_HDR_TABLES     32  /* Root of SYS_TABLES clust index */  #define DICT_HDR_TABLE_IDS  36  /* Root of SYS_TABLE_IDS sec index */  #define DICT_HDR_COLUMNS    40  /* Root of SYS_COLUMNS clust index */  #define DICT_HDR_INDEXES    44  /* Root of SYS_INDEXES clust index */  #define DICT_HDR_FIELDS     48  /* Root of SYS_FIELDS clust index */  #define DICT_HDR_FSEG_HEADER    56  /* Segment header for the tablespace  

The next section explains the loading of system tables and the loading principle of common table data dictionaries.

InnoDB data Dictionary detailed-system table