WL#5373: information schema System Tables for InnoDB

Status: Complete   —   Priority: Medium

This is to support following 6 system table information schema views for InnoDB:

information_schema.INNODB_SYS_TABLES
information_schema.INNODB_SYS_INDEXES
information_schema.INNODB_SYS_COLUMNS
information_schema.INNODB_SYS_FIELDS
information_schema.INNODB_SYS_FOREIGN
information_schema.INNODB_SYS_FOREIGN_COLS

And one information schema view for in memory statistics we have for the table:
information_schema.INNODB_SYS_TABLESTATS

For each table (except for INNODB_SYS_TABLESTATS), we will scan the
corresponding system table, fetch each record/row in the table, and parse them
to populate the system table view. This gives user/developer/DBA a clear view of
InnoDB system table content. Please note, rows are directly read from tables and
not parsed from in memory cached metadata. so we can discover any inconsistency
between metadata in cache and on disk in case such situation happens.

As mentioned, INNODB_SYS_TABLESTATS, actually, there is no corresponding system
table. However, this is a view (or fake table) of statistics we have in memory
for the table.

With this system table view, the original innodb_table_monitor output could
become obsolete.

Some sample output is listed here:

mysql> select * from INNODB_SYS_TABLES;
+----------+--------------------+------+--------+-------+
| TABLE_ID | NAME               | FLAG | N_COLS | SPACE |
+----------+--------------------+------+--------+-------+
|       11 | SYS_FOREIGN        |    0 |      7 |     0 |
|       12 | SYS_FOREIGN_COLS   |    0 |      7 |     0 |
|       17 | test/aa            |    1 |      4 |     0 |
|       19 | test/child         |    1 |      5 |     0 |
|       21 | test/customer      |    1 |      4 |     0 |
|       18 | test/parent        |    1 |      4 |     0 |
|       20 | test/product       |    1 |      6 |     0 |
|       22 | test/product_order |    1 |      7 |     0 |
+----------+--------------------+------+--------+-------+
8 rows in set (0.00 sec)


mysql> select * from INNODB_SYS_INDEXES;
+----------+------------------+----------+------+----------+---------+-------+
| INDEX_ID | NAME             | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE |
+----------+------------------+----------+------+----------+---------+-------+
|       11 | ID_IND           |       11 |    3 |        1 |     302 |     0 |
|       12 | FOR_IND          |       11 |    0 |        1 |     303 |     0 |
|       13 | REF_IND          |       11 |    0 |        1 |     304 |     0 |
|       14 | ID_IND           |       12 |    3 |        2 |     305 |     0 |
|       19 | GEN_CLUST_INDEX  |       17 |    1 |        0 |     315 |     0 |
|       20 | PRIMARY          |       18 |    3 |        1 |     317 |     0 |
|       21 | GEN_CLUST_INDEX  |       19 |    1 |        0 |     318 |     0 |
|       22 | par_ind          |       19 |    0 |        1 |     319 |     0 |
|       23 | PRIMARY          |       20 |    3 |        2 |     322 |     0 |
|       24 | PRIMARY          |       21 |    3 |        1 |     324 |     0 |
|       25 | PRIMARY          |       22 |    3 |        1 |     326 |     0 |
|       26 | product_category |       22 |    0 |        2 |     327 |     0 |
|       27 | customer_id      |       22 |    0 |        1 |     328 |     0 |
+----------+------------------+----------+------+----------+---------+-------+
13 rows in set (0.00 sec)


mysql> select * from INNODB_SYS_COLUMNS;
+----------+------------------+-----+-------+--------+-----+
| TABLE_ID | NAME             | POS | MTYPE | PRTYPE | LEN |
+----------+------------------+-----+-------+--------+-----+
|       11 | ID               |   0 |     1 | 524292 |   0 |
|       11 | FOR_NAME         |   1 |     1 | 524292 |   0 |
|       11 | REF_NAME         |   2 |     1 | 524292 |   0 |
|       11 | N_COLS           |   3 |     6 |      0 |   4 |
|       12 | ID               |   0 |     1 | 524292 |   0 |
|       12 | POS              |   1 |     6 |      0 |   4 |
|       12 | FOR_COL_NAME     |   2 |     1 | 524292 |   0 |
|       12 | REF_COL_NAME     |   3 |     1 | 524292 |   0 |
|       17 | a                |   0 |     6 |   1027 |   4 |
|       18 | id               |   0 |     6 |   1283 |   4 |
|       19 | id               |   0 |     6 |   1027 |   4 |
|       19 | parent_id        |   1 |     6 |   1027 |   4 |
|       20 | category         |   0 |     6 |   1283 |   4 |
|       20 | id               |   1 |     6 |   1283 |   4 |
|       20 | price            |   2 |     3 | 525558 |   5 |
|       21 | id               |   0 |     6 |   1283 |   4 |
|       22 | no               |   0 |     6 |   1283 |   4 |
|       22 | product_category |   1 |     6 |   1283 |   4 |
|       22 | product_id       |   2 |     6 |   1283 |   4 |
|       22 | customer_id      |   3 |     6 |   1283 |   4 |
+----------+------------------+-----+-------+--------+-----+
20 rows in set (0.00 sec)


mysql> select * from INNODB_SYS_FIELDS;
+----------+------------------+-----+
| INDEX_ID | NAME             | POS |
+----------+------------------+-----+
|       11 | ID               |   0 |
|       12 | FOR_NAME         |   0 |
|       13 | REF_NAME         |   0 |
|       14 | ID               |   0 |
|       14 | POS              |   1 |
|       20 | id               |   0 |
|       22 | parent_id        |   0 |
|       23 | category         |   0 |
|       23 | id               |   1 |
|       24 | id               |   0 |
|       25 | no               |   0 |
|       26 | product_category |   0 |
|       26 | product_id       |   1 |
|       27 | customer_id      |   0 |
+----------+------------------+-----+
14 rows in set (0.00 sec)


mysql> select * from INNODB_SYS_FOREIGN;
+---------------------------+--------------------+---------------+--------+------+
| ID                        | FOR_NAME           | REF_NAME      | N_COLS | TYPE |
+---------------------------+--------------------+---------------+--------+------+
| test/child_ibfk_1         | test/child         | test/parent   |      1 |    1 |
| test/product_order_ibfk_1 | test/product_order | test/product  |      2 |    4 |
| test/product_order_ibfk_2 | test/product_order | test/customer |      1 |    0 |
+---------------------------+--------------------+---------------+--------+------+
3 rows in set (0.00 sec)


mysql> select * from INNODB_SYS_FOREIGN_COLS;
+---------------------------+------------------+--------------+-----+
| ID                        | FOR_COL_NAME     | REF_COL_NAME | POS |
+---------------------------+------------------+--------------+-----+
| test/child_ibfk_1         | parent_id        | id           |   0 |
| test/product_order_ibfk_1 | product_category | category     |   0 |
| test/product_order_ibfk_1 | product_id       | id           |   1 |
| test/product_order_ibfk_2 | customer_id      | id           |   0 |
+---------------------------+------------------+--------------+-----+
4 rows in set (0.00 sec)


To get detail of a table, we can join across different system tables with
table_id, for example to know more about table "customer", query with its
table_id = 21: 


mysql> select * from INNODB_SYS_INDEXES where table_id = 21;
+----------+---------+----------+------+----------+---------+-------+
| INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE |
+----------+---------+----------+------+----------+---------+-------+
|       24 | PRIMARY |       21 |    3 |        1 |     324 |     0 |
+----------+---------+----------+------+----------+---------+-------+
1 row in set (0.00 sec)


mysql> select * from INNODB_SYS_FIELDS where index_id = 24;
+----------+------+-----+
| INDEX_ID | NAME | POS |
+----------+------+-----+
|       24 | id   |   0 |
+----------+------+-----+
1 row in set (0.00 sec)


mysql> select * from INNODB_SYS_COLUMNS where table_id = 21;
+----------+------+-----+-------+--------+-----+
| TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN |
+----------+------+-----+-------+--------+-----+
|       21 | id   |   0 |     6 |   1283 |   4 |
+----------+------+-----+-------+--------+-----+
1 row in set (0.00 sec)

mysql> select * from INNODB_SYS_FOREIGN where  ref_name = "test/customer";
+---------------------------+--------------------+---------------+--------+------+
| ID                        | FOR_NAME           | REF_NAME      | N_COLS | TYPE |
+---------------------------+--------------------+---------------+--------+------+
| test/product_order_ibfk_2 | test/product_order | test/customer |      1 |    0 |
+---------------------------+--------------------+---------------+--------+------+
1 row in set (0.00 sec)


The output of INNODB_SYS_TABLESTATS looks like the following:

mysql> select * from INNODB_SYS_TABLESTATS;
+----------+--------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
| TABLE_ID | NAME               | STATS_INITIALIZED | NUM_ROWS |
CLUST_INDEX_SIZE | OTHER_INDEX_SIZE | MODIFIED_COUNTER | AUTOINC | REF_COUNT |
+----------+--------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
|       11 | SYS_FOREIGN        | Initialized       |        0 |               
1 |                2 |                0 |       0 |         0 |
|       12 | SYS_FOREIGN_COLS   | Initialized       |        0 |               
1 |                0 |                0 |       0 |         0 |
|       17 | test/aa            | Initialized       |        0 |               
1 |                0 |                0 |       0 |         0 |
|       19 | test/child         | Initialized       |        0 |               
1 |                1 |                0 |       0 |         0 |
|       21 | test/customer      | Initialized       |        0 |               
1 |                0 |                0 |       0 |         0 |
|       18 | test/parent        | Initialized       |        0 |               
1 |                0 |                0 |       0 |         0 |
|       20 | test/product       | Initialized       |        0 |               
1 |                0 |                0 |       0 |         0 |
|       22 | test/product_order | Initialized       |        0 |               
1 |                2 |                0 |       0 |         0 |
+----------+--------------------+-------------------+----------+------------------+------------------+------------------+---------+-----------+
8 rows in set (0.00 sec)
REF: rb://330