Information Schema for InnoDB System Tables

Note: this article was originally published on http://blogs.innodb.com on April 11, 2011 by Jimmy Yang.

One of the most important things a database user or DBA wants to know is what columns, indexes etc. a table has. There are a few ways to find these things out, such as show tables. However, to really reveal all of the detailed metadata information in an InnoDB database, many have tried the “innodb_table_monitor” to peek into internal data dictionary. By creating an “innodb_table_monitor” table, InnoDB will print out the contents of metadata periodically. Unfortunately, it is printed out in an unstructured text for each table, and to find out what you need you would have to either carefully scan the output or have your own parser to do some additional analysis on the result if you want to display them systematically.

Well, in the MySQL 5.6 release, “innodb_table_monitor” can become history, and you will no longer need to search the text output to find out system metadata information. Instead, you can query the InnoDB internal system tables through Information Schema System Tables we implemented in this release.

To begin with, let’s take a look at six of the system tables in InnoDB:

SYS_TABLES
SYS_INDEXES
SYS_FIELDS
SYS_COLUMNS
SYS_FOREIGN_COLS
SYS_FOREIGN

The name of these tables explain what each system table contains, SYS_TABLES contains basic information about the table, each table’s index information is stored in SYS_INDEXES, and its column information is stored in SYS_COLUMNS. Each index’s field information is stored in SYS_FIELDS. And if a table contains foreign keys, that information is contained in SYS_FOREIGN_COLS and SYS_FOREIGN. You can get a good idea what a table or index look like after digging into all the detailed information stored in these system tables.

When a table is being used, the content of the each system table is loaded into an in-memory structure (its in-memory representation). For example, SYS_TABLES info is loaded into dict_table_t, SYS_INDEXES info is loaded into dict_index_t structure etc. There could be some additional information stored in each system table’s in memory representation in addition to its on disk content. For example, some run time statistics (such as rows inserted/updated) are stored in dict_table_t. To accommodate that we also created the 7th Information Schema table called SYS_TABLESTATS to display such information.

So in summary we have added the following seven System Tables:

mysql> show tables like “INNODB_SYS%”;
+———————————————————————-+
| Tables_in_information_schema (INNODB_SYS%) |
+———————————————————————-+
| INNODB_SYS_FIELDS                                     |
| INNODB_SYS_INDEXES                                   |
| INNODB_SYS_TABLESTATS                            |
| INNODB_SYS_COLUMNS                                |
| INNODB_SYS_FOREIGN_COLS                        |
| INNODB_SYS_FOREIGN                                  |
| INNODB_SYS_TABLES                                    |
+——————————————————————–+
7 rows in set (0.00 sec)

Now let’s go through these table using an example. Suppose we have a table named “test_table”, which has one index “idx”:

mysql> create table test_table(col1 int, col2 char(10), col3 varchar(10)) engine = innodb;

mysql> create index idx on test_table(col1);

Let see what’s in each system table:

———————————————————————-

mysql> select * from INNODB_SYS_TABLES \G
*************************** 1. row ***************************
TABLE_ID: 11
NAME: SYS_FOREIGN
FLAG: 0
N_COLS: 7
SPACE: 0
*************************** 2. row ***************************
TABLE_ID: 12
NAME: SYS_FOREIGN_COLS
FLAG: 0
N_COLS: 7
SPACE: 0
*************************** 3. row ***************************
TABLE_ID: 13
NAME: test/test_table
FLAG: 1
N_COLS: 6
SPACE: 1
3 rows in set (0.00 sec)

———————————————————————-

We can see that table “test_table” is listed there with TABLE_ID 13.  It has 6 columns because  InnoDB adds three hidden columns (DB_ROW_ID, DB_TRX_ID,  DB_ROLL_PTR) to the table. The table flag is 1 shows it is “DICT_TF_COMPACT” type, and its space ID is 1.

Then we could select into INNODB_SYS_COLUMNS to find more information about its columns:

———————————————————————-

mysql> select * from INNODB_SYS_COLUMNS where TABLE_ID = 13 \G
*************************** 1. row ***************************
TABLE_ID: 13
NAME: col1
POS: 0
MTYPE: 6
PRTYPE: 1027
LEN: 4
*************************** 2. row ***************************
TABLE_ID: 13
NAME: col2
POS: 1
MTYPE: 2
PRTYPE: 524542
LEN: 10
*************************** 3. row ***************************
TABLE_ID: 13
NAME: col3
POS: 2
MTYPE: 1
PRTYPE: 524303
LEN: 10
3 rows in set (0.00 sec)

———————————————————————-

So it shows all of its three columns, each column’s position, type and length.

Let’s select into SYS_INDEXES to find out its index:

———————————————————————-

mysql> select * from INNODB_SYS_INDEXES where TABLE_ID = 13 \G
*************************** 1. row ***************************
INDEX_ID: 15
NAME: GEN_CLUST_INDEX
TABLE_ID: 13
TYPE: 1
N_FIELDS: 0
PAGE_NO: 3
SPACE: 1
*************************** 2. row ***************************
INDEX_ID: 16
NAME: idx
TABLE_ID: 13
TYPE: 0
N_FIELDS: 1
PAGE_NO: 4
SPACE: 1
2 rows in set (0.00 sec)

———————————————————————-

It has two indexes instead of one! Well, InnoDB actually creates an internal clustered index called “GEN_CLUST_INDEX” if there is no user defined clustered index for the table. SYS_INDEX also reveals the root page of the index in PAGE_NO field.

Note the user index “idx” has its INDEX_ID as 16. We can use it to select into SYS_FIELDS to find out the indexed columns:

———————————————————————-

mysql> select * from INNODB_SYS_FIELDS where INDEX_ID = 16 \G
*************************** 1. row ***************************
INDEX_ID: 16
NAME: col1
POS: 0
1 row in set (0.00 sec)

———————————————————————-

Last let’s look into INNODB_SYS_TABLESTATS by inserting a row into “test_table”:

———————————————————————-

mysql> insert into test_table values(9, “me”, “here”);
Query OK, 1 row affected (0.06 sec)

mysql> select * from INNODB_SYS_TABLESTATS where TABLE_ID = 13 \G
*************************** 1. row ***************************
TABLE_ID: 13
NAME: test/test_table
STATS_INITIALIZED: Initialized
NUM_ROWS: 1
CLUST_INDEX_SIZE: 1
OTHER_INDEX_SIZE: 0
MODIFIED_COUNTER: 1
AUTOINC: 0
MYSQL_HANDLES_OPENED: 1
1 row in set (0.00 sec)

———————————————————————-

So it shows the num_rows inserted through “NUM_ROWS” field, and modified field through “MODIFIED_COUNTER”.

If tables have a foreign key relationship, that information can be displayed via the SYS_FOREIGNS and SYS_FOREIGNCOLS table:

———————————————————————-
CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)) ENGINE=INNODB;

CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
CONSTRAINT constraint_test
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE) ENGINE=INNODB;

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN \G
*************************** 1. row ***************************
ID: test/constraint_test
FOR_NAME: test/child
REF_NAME: test/parent
N_COLS: 1
TYPE: 1
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS\G
*************************** 1. row ***************************
ID: test/constraint_test
FOR_COL_NAME: parent_id
REF_COL_NAME: id
POS: 0
1 row in set (0.00 sec)

———————————————————————-

So INNODB_SYS_FOREIGN and INNODB_SYS_FOREIGN_COLS clearly described the foreign key relationship between the “child” and “parent” tables and its related foreign key column.

All of these seven tables can be joined through either TABLE_ID or INDEX_ID, so you can fetch all information for exactly the table you want to study. And by displaying these system tables as as relational tables themselves, user can query through them and get exactly the information you are interested in. This becomes a great interface for any external tools to display the database content.

There is another benefit of Information Schema SYSTEM TABLES in that, by design, the data is read from system table directly, rather than fetching data from their in memory representations (like innodb_table_monitor does). So it is a true representation of what we have on disk. If there is an mismatch (unlikely) between the in memory information and on disk data, these system tables will display the real image on disk, and help DBA and even developer to debug or better understand the system metadata behavior.

Comments:

Post a Comment:
Comments are closed for this entry.
About

This is the InnoDB team blog.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today