テストデータベースとテーブル t1 を作成します。

mysql> CREATE DATABASE test;

mysql> USE test;

mysql> CREATE TABLE t1 (
       col1 INT,
       col2 CHAR(10),
       col3 VARCHAR(10))
       ENGINE = InnoDB;

mysql> CREATE INDEX i1 ON t1(col1);

テーブル t1 を作成した後、INNODB_TABLES をクエリーして test/t1 のメタデータを検索します:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 71
         NAME: test/t1
         FLAG: 1
       N_COLS: 6
        SPACE: 57
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
 INSTANT_COLS: 0

テーブル t1 の TABLE_ID は 71 です。 FLAG フィールドは、テーブルの形式とストレージの特性に関するビットレベルの情報を提供します。 6 つのカラムがあり、そのうちの 3 つが InnoDB によって作成された非表示のカラム (DB_ROW_ID、DB_TRX_ID、および DB_ROLL_PTR) です。 このテーブルの SPACE の ID は 57 です (0 の値は、テーブルがシステムテーブルスペース内に存在することを示します)。 ROW_FORMAT はコンパクトです。 ZIP_PAGE_SIZE は、Compressed 行フォーマットのテーブルにのみ適用されます。 INSTANT_COLS では、ALGORITHM=INSTANT で ALTER TABLE ... ADD COLUMN を使用して最初のインスタントカラムを追加する前に、テーブルのカラム数が表示されます。

INNODB_TABLES の TABLE_ID 情報を使用して、INNODB_COLUMNS テーブルにテーブルのカラムに関する情報をクエリーします。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS where TABLE_ID = 71\G
*************************** 1. row ***************************
     TABLE_ID: 71
         NAME: col1
          POS: 0
        MTYPE: 6
       PRTYPE: 1027
          LEN: 4
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
*************************** 2. row ***************************
     TABLE_ID: 71
         NAME: col2
          POS: 1
        MTYPE: 2
       PRTYPE: 524542
          LEN: 10
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL
*************************** 3. row ***************************
     TABLE_ID: 71
         NAME: col3
          POS: 2
        MTYPE: 1
       PRTYPE: 524303
          LEN: 10
  HAS_DEFAULT: 0
DEFAULT_VALUE: NULL

TABLE_ID および NAME カラムに加えて、INNODB_COLUMNS は、(0 から始まり、順次増分する) 各カラムの順序位置 (POS)、MTYPE または「「メインタイプ」」 (6 = INT, 2 = CHAR, 1 = VARCHAR)、PRTYPE または「「正確な型」」 (MySQL データセット、文字セットコード、およびヌル可能性を示すビットを持つバイナリ値) およびコード長を表すリテラル (LEN) を提供します。 HAS_DEFAULT および DEFAULT_VALUE のカラムは、ALGORITHM=INSTANT とともに ALTER TABLE ... ADD COLUMN を使用して即時に追加されたカラムにのみ適用されます。

INNODB_TABLES の TABLE_ID 情報を再度使用して、テーブル t1 に関連付けられたインデックスに関する情報を INNODB_INDEXES にクエリーします。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE TABLE_ID = 71 \G
*************************** 1. row ***************************
       INDEX_ID: 111
           NAME: GEN_CLUST_INDEX
       TABLE_ID: 71
           TYPE: 1
       N_FIELDS: 0
        PAGE_NO: 3
          SPACE: 57
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
       INDEX_ID: 112
           NAME: i1
       TABLE_ID: 71
           TYPE: 0
       N_FIELDS: 1
        PAGE_NO: 4
          SPACE: 57
MERGE_THRESHOLD: 50

INNODB_INDEXES は、2 つのインデックスのデータを返します。 最初のインデックスは GEN_CLUST_INDEX です。これは、テーブルにユーザー定義のクラスタ化されたインデックスが存在しない場合に InnoDB によって作成されたクラスタ化されたインデックスです。 2 番目のインデックス (i1) は、ユーザー定義のセカンダリインデックスです。

INDEX_ID は、インスタンス内のすべてのデータベースにわたって一意であるインデックスの識別子です。 TABLE_ID は、そのインデックスが関連付けられているテーブルを識別します。 インデックスの TYPE 値は、インデックスのタイプ (1 = クラスタ化されたインデックス、0 = セカンダリインデックス) を示します。 N_FILEDS 値は、このインデックスを構成するフィールドの数です。 PAGE_NO はインデックスの B ツリーのルートページ番号であり、SPACE はインデックスが存在するテーブルスペースの ID です。 ゼロ以外の値は、インデックスがシステムテーブルスペースに存在しないことを示します。 MERGE_THRESHOLD では、インデックスページのデータ量のパーセンテージしきい値を定義します。 行が削除されたとき、または更新操作によって行が短縮されたときに、インデックスページのデータ量がこの値 (デフォルトは 50%) を下回った場合、InnoDB はインデックスページを隣接するインデックスページとマージしようとします。

INNODB_INDEXES の INDEX_ID 情報を使用して、INNODB_FIELDS にインデックス i1 のフィールドに関する情報をクエリーします。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FIELDS where INDEX_ID = 112 \G
*************************** 1. row ***************************
INDEX_ID: 112
    NAME: col1
     POS: 0

INNODB_FIELDS には、インデックス付きフィールドの NAME と、インデックス内での順序位置が用意されています。 インデックス (i1) が複数のフィールドに定義されている場合、INNODB_FIELDS はインデックス付けされた各フィールドのメタデータを提供します。

INNODB_TABLES の SPACE 情報を使用して、INNODB_TABLESPACES テーブルにテーブルのテーブルスペースに関する情報をクエリーします。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE = 57 \G
*************************** 1. row ***************************
          SPACE: 57
          NAME: test/t1
          FLAG: 16417
    ROW_FORMAT: Dynamic
     PAGE_SIZE: 16384
 ZIP_PAGE_SIZE: 0
    SPACE_TYPE: Single
 FS_BLOCK_SIZE: 4096
     FILE_SIZE: 114688
ALLOCATED_SIZE: 98304
AUTOEXTEND_SIZE: 0
SERVER_VERSION: 8.0.23
 SPACE_VERSION: 1
    ENCRYPTION: N
         STATE: normal

INNODB_TABLESPACES では、テーブルスペースの SPACE ID および関連付けられたテーブルの NAME に加えて、テーブルスペースのフォーマットおよび記憶特性に関するビットレベルの情報であるテーブルスペース FLAG データが提供されます。 テーブルスペース ROW_FORMAT、PAGE_SIZE およびその他のいくつかのテーブルスペースメタデータ項目も用意されています。

INNODB_TABLES の SPACE 情報を再度使用して、INNODB_DATAFILES にテーブルスペースデータファイルの場所をクエリーします。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_DATAFILES WHERE SPACE = 57 \G
*************************** 1. row ***************************
SPACE: 57
 PATH: ./test/t1.ibd

データファイルは、MySQL の data ディレクトリの下の test ディレクトリにあります。 file-per-table テーブルスペースが CREATE TABLE ステートメントの DATA DIRECTORY 句を使用して MySQL データディレクトリ以外の場所に作成された場合、テーブルスペースの PATH は完全修飾のディレクトリパスになります。

最後のステップとして、テーブル t1 (TABLE_ID = 71) に行を挿入し、INNODB_TABLESTATS テーブルのデータを表示します。 このテーブル内のデータは、InnoDB テーブルのクエリー時に使用するインデックスを決定するために MySQL オプティマイザによって使用されます。 この情報は、インメモリーデータ構造から取得されます。

mysql> INSERT INTO t1 VALUES(5, 'abc', 'def');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESTATS where TABLE_ID = 71 \G
*************************** 1. row ***************************
         TABLE_ID: 71
             NAME: test/t1
STATS_INITIALIZED: Initialized
         NUM_ROWS: 1
 CLUST_INDEX_SIZE: 1
 OTHER_INDEX_SIZE: 0
 MODIFIED_COUNTER: 1
          AUTOINC: 0
        REF_COUNT: 1

STATS_INITIALIZED フィールドは、このテーブルの統計が収集されているかどうかを示します。 NUM_ROWS は、現在の推定されるテーブル内の行数です。 CLUST_INDEX_SIZE および OTHER_INDEX_SIZE フィールドはそれぞれ、テーブルのクラスタ化されたインデックスとセカンダリインデックスを格納するディスク上のページの数をレポートします。 MODIFIED_COUNTER 値は、外部キーからの DML 操作およびカスケード操作によって変更された行数を示します。 AUTOINC 値は、自動インクリメントベースの操作に対して発行される次の番号です。 テーブル t1 では自動インクリメントカラムが定義されていないため、この値は 0 です。 REF_COUNT 値はカウンタです。 このカウンタが 0 に達すると、テーブルキャッシュからテーブルメタデータを削除できることを示します。

テストデータベースおよび親テーブルと子テーブルを作成します。

mysql> CREATE DATABASE test;

mysql> USE test;

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

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

親テーブルと子テーブルが作成されたら、INNODB_FOREIGN をクエリーして、test/child と test/parent の外部キー関係の外部キーデータを見つけます:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN \G
*************************** 1. row ***************************
      ID: test/fk1
FOR_NAME: test/child
REF_NAME: test/parent
  N_COLS: 1
    TYPE: 1

メタデータには、子テーブルで定義された CONSTRAINT として指定されている外部キー ID (fk1) が含まれています。 FOR_NAME は、外部キーが定義されている子テーブルの名前です。 REF_NAME は、親テーブル (「参照される」テーブル) の名前です。 N_COLS は、外部キーのインデックス内のカラム数です。 TYPE は、外部キーカラムに関する追加情報を提供するビットフラグを表す数値です。 この場合、TYPE 値は 1 です。これは、外部キーに対して ON DELETE CASCADE オプションが指定されたことを示します。 TYPE 値の詳細は、INNODB_FOREIGN テーブルの定義を参照してください。

外部キー ID を使用して、INNODB_FOREIGN_COLS をクエリーして、外部キーのカラムに関するデータを表示します。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS WHERE ID = 'test/fk1' \G
*************************** 1. row ***************************
          ID: test/fk1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
         POS: 0

FOR_COL_NAME は子テーブル内の外部キーカラムの名前であり、REF_COL_NAME は親テーブル内の参照されるカラムの名前です。 POS 値は、外部キーのインデックス内のキーフィールドの序数位置です (0 から始まります)。