InnoDB data Dictionary details-system Table

1. Introduction

In InnoDB, you don't actually see system tables. There are four basic system tables to store table metadata: tables, columns, indexes, index columns, and so on. These four tables are SYS_TABLES, SYS_COLUMNS, SYS_INDEXES, SYS_FIELDS. The following are introduced separately

2、SYS_TABLES

Store all tables with InnoDB as the storage engine, one table for each record. The columns of the table are:

NAME: table name ID: table ID number N_COLS: table column number TYPE: table storage type, including record format, compression and other information MIX_ID, MIX_LEN, CLUSTER_NAME: temporarily unused SPACE: the table space ID where this table is located.

This table has a clustered index on NAME and a unique secondary index on ID.

3、SYS_COLUMNS

Store column information, each column corresponding to a record. Tabulated:

TABLE_ID: IDPOS of the table to which the column belongs: Column NAME: MTYPE: Primary data type of the column PRTYPE: Exact data type of the column LEN: Length of the column data, excluding varchar type, because this type already stores PREC: Precision of the column data in the record.

The primary key column of this table is (TABLE_ID,POS)

4、SYS_INDEXES

Store index information, one index for each record.

TABLE_ID: IDID of the table to which the column belongs: index number NAME: index name N_FIELDS: number of columns contained in the index TYPE: index type, including clustered index, unique index, DICT_UNIVERSAL, DICT_IBUFSPACE: table space of the table to which the index belongs IDPAGE_NO: root page number of the B+ tree corresponding to the index.

The table primary key (TABLE_ID,ID)

5、SYS_FIELDS

Store defined index columns, one for each record:

INDEX_ID: index where the column is located IDPOS: column number of the column in the index COL_NAME: column name

The table primary key is (INDEX_ID,POS)

6. The root page position of the data dictionary table is stored on page 7 of file 0 in innodb. This page stores the five root page numbers of the top four tables, as well as the next table ID value, the next index ID value, the next tablespace ID value, and rowid. Page 7 is structured as follows:

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 segment into which the dictionary header is created */

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