MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.15.3 InnoDB INFORMATION

9.1 English
9.0 English
8.4 English
8.0 English
5.7 English

MySQL 8.0 リファレンスマニュアル / ... / InnoDB INFORMATION_SCHEMA スキーマオブジェクトテーブル

このページは機械翻訳したものです。

15.15.3 InnoDB INFORMATION_SCHEMA スキーマオブジェクトテーブル

InnoDB INFORMATION_SCHEMA テーブルを使用して、InnoDB で管理されるスキーマオブジェクトに関するメタデータを抽出できます。この情報はデータディクショナリから取得されます。従来、このタイプの情報は、セクション15.17「InnoDB モニター」の手法を使用して取得し、InnoDB モニターを設定して、SHOW ENGINE INNODB STATUS ステートメントからの出力を解析します。 InnoDB INFORMATION_SCHEMA テーブルのインタフェースを使用すると、SQL を使用してこのデータをクエリーできます。

InnoDB INFORMATION_SCHEMA スキーマオブジェクトテーブルには、次のテーブルが含まれます。

INNODB_DATAFILES
INNODB_TABLESTATS
INNODB_FOREIGN
INNODB_COLUMNS
INNODB_INDEXES
INNODB_FIELDS
INNODB_TABLESPACES
INNODB_TABLESPACES_BRIEF
INNODB_FOREIGN_COLS
INNODB_TABLES

これらのテーブル名は、提供されるデータのタイプを示しています。

INNODB_TABLES は、InnoDB テーブルに関するメタデータを提供します。
INNODB_COLUMNS は、InnoDB テーブルのカラムに関するメタデータを提供します。
INNODB_INDEXES は、InnoDB インデックスに関するメタデータを提供します。
INNODB_FIELDS では、InnoDB インデックスのキーカラム (フィールド) に関するメタデータが提供されます。
INNODB_TABLESTATS では、メモリー内データ構造から導出された InnoDB テーブルに関する低レベルのステータス情報のビューが提供されます。
INNODB_DATAFILES では、InnoDB file-per-table および一般テーブルスペースのデータファイルパス情報が提供されます。
INNODB_TABLESPACES は、InnoDB file-per-table、general および undo テーブルスペースに関するメタデータを提供します。
INNODB_TABLESPACES_BRIEF では、InnoDB テーブルスペースに関するメタデータのサブセットが提供されます。
INNODB_FOREIGN は、InnoDB テーブルに定義されている外部キーに関するメタデータを提供します。
INNODB_FOREIGN_COLS では、InnoDB テーブルに定義されている外部キーのカラムに関するメタデータが提供されます。

InnoDB INFORMATION_SCHEMA スキーマオブジェクトテーブルは、TABLE_ID、INDEX_ID、SPACE などのフィールドを使用して結合できるため、調査または監視するオブジェクトに使用可能なすべてのデータを簡単に取得できます。

各テーブルのカラムについては、InnoDB INFORMATION_SCHEMA のドキュメントを参照してください。

例 15.2 InnoDB INFORMATION_SCHEMA スキーマオブジェクトテーブル

この例では、単一のインデックス (i1) を持つ単純なテーブル (t1) を使用して、InnoDB INFORMATION_SCHEMA スキーマオブジェクトテーブルにあるメタデータのタイプを示します。

テストデータベースとテーブル 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 に達すると、テーブルキャッシュからテーブルメタデータを削除できることを示します。

例 15.3 外部キー INFORMATION_SCHEMA スキーマオブジェクトテーブル

INNODB_FOREIGN テーブルおよび INNODB_FOREIGN_COLS テーブルは、外部キー関係に関するデータを提供します。この例では、外部キー関係を持つ親テーブルと子テーブルを使用して、INNODB_FOREIGN テーブルと INNODB_FOREIGN_COLS テーブルで検出されたデータを示します。

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

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 から始まります)。

例 15.4 InnoDB INFORMATION_SCHEMA スキーマオブジェクトテーブルの結合

この例では、employees サンプルデータベースのテーブルに関するファイル形式、行形式、ページサイズおよびインデックスサイズ情報を収集するために、3 つの InnoDB INFORMATION_SCHEMA スキーマオブジェクトテーブル (INNODB_TABLES、INNODB_TABLESPACES および INNODB_TABLESTATS) を結合する方法を示します。

クエリー文字列を短くするために、次のテーブル名のエイリアスが使用されます。

INFORMATION_SCHEMA.INNODB_TABLES: a
INFORMATION_SCHEMA.INNODB_TABLESPACES: b
INFORMATION_SCHEMA.INNODB_TABLESTATS: c

圧縮テーブルに対応するために、IF() 制御フロー関数が使用されています。テーブルが圧縮されている場合、インデックスサイズは PAGE_SIZE ではなく、ZIP_PAGE_SIZE を使用して計算されます。バイト単位でレポートされる CLUST_INDEX_SIZE および OTHER_INDEX_SIZE を 1024*1024 で割ると、M バイト (MB) 単位のインデックスサイズが得られます。 MB 値は、ROUND() 関数を使用して小数点以下 0 桁に丸められます。

mysql> SELECT a.NAME, a.ROW_FORMAT,
        @page_size :=
         IF(a.ROW_FORMAT='Compressed',
          b.ZIP_PAGE_SIZE, b.PAGE_SIZE)
          AS page_size,
         ROUND((@page_size * c.CLUST_INDEX_SIZE)
          /(1024*1024)) AS pk_mb,
         ROUND((@page_size * c.OTHER_INDEX_SIZE)
          /(1024*1024)) AS secidx_mb
       FROM INFORMATION_SCHEMA.INNODB_TABLES a
       INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES b on a.NAME = b.NAME
       INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESTATS c on b.NAME = c.NAME
       WHERE a.NAME LIKE 'employees/%'
       ORDER BY a.NAME DESC;
+------------------------+------------+-----------+-------+-----------+
| NAME                   | ROW_FORMAT | page_size | pk_mb | secidx_mb |
+------------------------+------------+-----------+-------+-----------+
| employees/titles       | Dynamic    |     16384 |    20 |        11 |
| employees/salaries     | Dynamic    |     16384 |    93 |        34 |
| employees/employees    | Dynamic    |     16384 |    15 |         0 |
| employees/dept_manager | Dynamic    |     16384 |     0 |         0 |
| employees/dept_emp     | Dynamic    |     16384 |    12 |        10 |
| employees/departments  | Dynamic    |     16384 |     0 |         0 |
+------------------------+------------+-----------+-------+-----------+

PREV HOME UP NEXT