WL#5373: information schema System Tables for InnoDB
Status: Complete
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
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.