Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand InnoDB data Dictionary". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In InnoDB, the system table is virtually invisible, unlike Oracle, which can easily get its contents through a query statement. Because MySQL is a plug-in database management system. Its structure is divided into two layers, namely the Server layer and the storage engine layer. The earliest storage engine is MyISAM, which does not have a data dictionary, and has only .frm files for table structure, so it is necessary for InnoDB to have this file for the Server layer to recognize and manage it. For the Server layer, what storage engine a table is, which is the attribute of the table. Within each storage engine, the data dictionary is not managed by the Server layer, which makes the data dictionary imperceptible to the user.

System table structure

InnoDB has four basic system tables, which are used to store user-defined tables, columns, indexes and index columns. These tables are SYS_TABLES,SYS_COLUMNS,SYS_INDEXES,SYS_FIELDS.

SYS_TABLES

Used to store all tables where InnoDB is the storage engine

NAME: represents the table name of a table

ID: indicates the ID number of this table

N_COLS: indicates the number of columns in this table, and the number of columns specified in the table.

TYPE: indicates the storage type of this table, including record format, compression, and so on.

SPACE: indicates the tablespace ID number in which the table resides. The primary key for this table is listed as NAME, and there is also a unique index on the ID number.

SYS_COLUMNS

Used to store information about all columns in all tables defined in InnoDB, each column corresponds to a record for that table.

TABLE_ID: indicates the ID number of the table to which this column belongs

POS: indicates which column this column is in the table.

NAME: indicates the column name.

MTYPE: represents the primary data type of this column.

PRTYPE: represents some of the exact data types of this column, which is a combined value, including the NULL flag, whether there is a symbol number flag, whether it is a binary string flag and indicates that the column is true varchar

LEN: indicates the precision of this column data, which is not currently used.

SYS_INDEXES

Used to store index information for all tables in InnoDB. Each record corresponds to an index.

TABLE_ID: indicates the ID number of the table to which this index belongs.

ID: represents the index ID number of this index

NAME: represents the index name of this index

N_FIELDS: indicates the number of columns included in this index.

TYPE: indicates the type of index, including clustered index, unique index, etc.

SPACE: indicates the ID number of the tablespace in which the index data resides

PAGE_NO: represents the B+ tree root page corresponding to this index.

SYS_FIEDS

Used to store index columns defined in all indexes, each record corresponds to an index column.

INDEX_ID: the index in which this column is located

POS: how many index columns is this column in an index?

COL_NAME: the column name of this index column.

Loading of dictionary tables

When InnoDB starts, if you are creating a new database, you need to initialize the library, and the index needs to create dictionary-managed Btree information. Because the structure and number of system tables in InnoDB are fixed, you only need to create the storage B + tree of these tables when initializing the library. At the same time, the root page numbers of these B+ trees are stored in a fixed location, so there is no need to store the information of these tables themselves in the system table. For a B-tree, as long as you find its root page, you can find and retrieve its data.

For the storage of the data dictionary table root page location, InnoDB uses a special page (page 7 of the No. 0 tablespace 0 file) to manage the data dictionary information. This page is used to store five root page numbers (with five indexes) of four system tables.

During the loading process of an ordinary user table, when a user accesses a table, the system will first look for the table SHARE object from the table object buffer pool. If found, it will take an idle instantiated table object directly from its instantiated table object space linked list. If no instantiated object is available, it needs to be reopened (instantiated the table), and when the table is instantiated You need to find dictionary information for the table, including the table itself, column information, index information, and so on, much of which is obtained from SHARE objects. If there is no SHARE object, you need to construct the SHARE object from the system table.

Rowid management

In InnoDB, records in a user table do not necessarily have a Rowid column, and Rowid is allocated only if a table does not have a primary key defined. On the other hand, the administrative allocation of Rowid is not an exclusive ID space for a table, but a global one. All tables are used to share this ID number.

The allocation of Rowid does not directly modify the page, as long as the value is a multiple of 256 will be written once. So if you insert 200 times, these values have not been written, this is a system restart, the ID number is not reused, because when the database is started, the function will be called to do a job, that is, 256 will be added after the last written Rowid value is aligned up 256, so there will be no problem.

This is the end of "how to understand InnoDB data Dictionary". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.