MySQL 5.6 の時点では、InnoDB
INFORMATION_SCHEMA
システムテーブルを使用して、InnoDB
によって管理されているスキーマオブジェクトに関するメタデータを抽出できます。この情報は、通常の InnoDB
テーブルとは異なり直接クエリーできない InnoDB
内部システムテーブル (InnoDB
データディクショナリとも呼ばれます) から取得されます。従来より、このタイプの情報は、セクション14.15「InnoDB モニター」の手法を使用して、InnoDB
モニターを設定し、SHOW ENGINE INNODB STATUS
コマンドからの出力を解析することによって取得します。InnoDB
INFORMATION_SCHEMA
テーブルのインタフェースを使用すると、SQL を使用してこのデータをクエリーできます。
対応する内部システムテーブルが存在しない INNODB_SYS_TABLESTATS
を除き、InnoDB
INFORMATION_SCHEMA
システムテーブルは、メモリー内にキャッシュされているメタデータからではなく、内部の InnoDB
システムテーブルから直接読み取られたデータで移入されます。
InnoDB
INFORMATION_SCHEMA
システムテーブルには、下に一覧表示されているテーブルが含まれます。INNODB_SYS_DATAFILES
と INNODB_SYS_TABLESPACES
は、InnoDB
file-per-table テーブルスペース (.ibd
ファイル) を MySQL データディレクトリ以外の場所に作成できるようにする、CREATE TABLE
ステートメントの DATA DIRECTORY='
句に対するサポートの導入とともに MySQL 5.6.6 で追加されました。
directory
'
mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_SYS%';
+--------------------------------------------+
| Tables_in_information_schema (INNODB_SYS%) |
+--------------------------------------------+
| INNODB_SYS_DATAFILES |
| INNODB_SYS_TABLESTATS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_INDEXES |
| INNODB_SYS_FIELDS |
| INNODB_SYS_TABLESPACES |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_SYS_TABLES |
+--------------------------------------------+
9 rows in set (0.00 sec)
これらのテーブル名は、提供されるデータのタイプを示しています。
INNODB_SYS_TABLES
は、InnoDB
データディクショナリのSYS_TABLES
テーブル内の情報と同等の、InnoDB
テーブルに関するメタデータを提供します。INNODB_SYS_COLUMNS
は、InnoDB
データディクショナリのSYS_COLUMNS
テーブル内の情報と同等の、InnoDB
テーブルカラムに関するメタデータを提供します。INNODB_SYS_INDEXES
は、InnoDB
データディクショナリのSYS_INDEXES
テーブル内の情報と同等の、InnoDB
インデックスに関するメタデータを提供します。INNODB_SYS_FIELDS
は、InnoDB
データディクショナリのSYS_FIELDS
テーブル内の情報と同等の、InnoDB
インデックスのキーカラム (フィールド) に関するメタデータを提供します。INNODB_SYS_TABLESTATS
は、インメモリーデータ構造から取得されたInnoDB
テーブルに関する低レベルのステータス情報のビューを提供します。対応する内部InnoDB
システムテーブルはありません。INNODB_SYS_DATAFILES
は、InnoDB
データディクショナリのSYS_DATAFILES
テーブル内の情報と同等の、InnoDB
テーブルスペースのデータファイルパス情報を提供します。INNODB_SYS_TABLESPACES
は、InnoDB
データディクショナリのSYS_TABLESPACES
テーブル内の情報と同等の、InnoDB
テーブルスペースに関するメタデータを提供します。INNODB_SYS_FOREIGN
は、InnoDB
データディクショナリのSYS_FOREIGN
テーブル内の情報と同等の、InnoDB
テーブルで定義された外部キーに関するメタデータを提供します。INNODB_SYS_FOREIGN_COLS
は、InnoDB
データディクショナリのSYS_FOREIGN_COLS
テーブル内の情報と同等の、InnoDB
テーブルで定義された外部キーのカラムに関するメタデータを提供します。
InnoDB
INFORMATION_SCHEMA
システムテーブルを TABLE_ID
、INDEX_ID
、SPACE
などのフィールドを通して結合することにより、調査またはモニターしたいオブジェクトの使用可能なすべてのデータを容易に取得できます。
各テーブルのカラムについては、InnoDB
INFORMATION_SCHEMA のドキュメントを参照してください。
例 14.13 InnoDB INFORMATION_SCHEMA システムテーブル
この例では、単純なテーブル (t1
) を 1 つのインデックス (i1
) で使用して、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_SYS_TABLES
をクエリーしてtest/t1
のメタデータを見つけます。mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1' \G *************************** 1. row *************************** TABLE_ID: 71 NAME: test/t1 FLAG: 1 N_COLS: 6 SPACE: 57 FILE_FORMAT: Antelope ROW_FORMAT: Compact ZIP_PAGE_SIZE: 0 10 rows in set (0.00 sec)
テーブル
t1
のTABLE_ID
は 71 です。FLAG
フィールドは、テーブルの形式とストレージの特性に関するビットレベルの情報を提供します。6 つのカラムがあり、そのうちの 3 つがInnoDB
によって作成された非表示のカラム (DB_ROW_ID
、DB_TRX_ID
、およびDB_ROLL_PTR
) です。このテーブルのSPACE
の ID は 57 です (0 の値は、テーブルがシステムテーブルスペース内に存在することを示します)。FILE_FORMAT
は Antelope であり、ROW_FORMAT
は Compact です。ZIP_PAGE_SIZE
は、Compressed
行フォーマットのテーブルにのみ適用されます。 -
INNODB_SYS_TABLES
からのTABLE_ID
情報を使用して、このテーブルのカラムに関する情報を取得するためにINNODB_SYS_COLUMNS
テーブルをクエリーします。mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where TABLE_ID = 71 \G *************************** 1. row *************************** TABLE_ID: 71 NAME: col1 POS: 0 MTYPE: 6 PRTYPE: 1027 LEN: 4 *************************** 2. row *************************** TABLE_ID: 71 NAME: col2 POS: 1 MTYPE: 2 PRTYPE: 524542 LEN: 10 *************************** 3. row *************************** TABLE_ID: 71 NAME: col3 POS: 2 MTYPE: 1 PRTYPE: 524303 LEN: 10 3 rows in set (0.00 sec)
INNODB_SYS_COLUMNS
は、TABLE_ID
とカラムNAME
に加えて、各カラムの序数位置 (POS
) (0 から始まり順次に増分します)、カラムMTYPE
または「メインの型」 (6 = INT、2 = CHAR、1 = VARCHAR)、PRTYPE
または「正確な型」 (MySQL のデータ型、文字セットコード、および NULL 可能性を表すビットを含むバイナリ値)、およびカラムの長さ (LEN
) を提供します。 -
ふたたび
INNODB_SYS_TABLES
からのTABLE_ID
情報を使用して、テーブルt1
に関連付けられたインデックスに関する情報を取得するためにINNODB_SYS_INDEXES
をクエリーします。mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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 *************************** 2. row *************************** INDEX_ID: 112 NAME: i1 TABLE_ID: 71 TYPE: 0 N_FIELDS: 1 PAGE_NO: 4 SPACE: 57 2 rows in set (0.00 sec)
INNODB_SYS_INDEXES
は、2 つのインデックスのデータを返します。最初のインデックスはGEN_CLUST_INDEX
です。これは、テーブルにユーザー定義のクラスタ化されたインデックスが存在しない場合にInnoDB
によって作成されたクラスタ化されたインデックスです。2 番目のインデックス (i1
) は、ユーザー定義のセカンダリインデックスです。INDEX_ID
は、インスタンス内のすべてのデータベースにわたって一意であるインデックスの識別子です。TABLE_ID
は、そのインデックスが関連付けられているテーブルを識別します。インデックスのTYPE
値は、インデックスのタイプ (1 = クラスタ化されたインデックス、0 = セカンダリインデックス) を示します。N_FILEDS
値は、このインデックスを構成するフィールドの数です。PAGE_NO
はインデックスの B ツリーのルートページ番号であり、SPACE
はインデックスが存在するテーブルスペースの ID です。0 以外の値は、そのインデックスがシステムテーブルスペース内に存在しないことを示します。 -
INNODB_SYS_INDEXES
からのINDEX_ID
情報を使用して、インデックスi1
のフィールドに関する情報を取得するためにINNODB_SYS_FIELDS
をクエリーします。mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS where INDEX_ID = 112 \G *************************** 1. row *************************** INDEX_ID: 112 NAME: col1 POS: 0 1 row in set (0.00 sec)
INNODB_SYS_FIELDS
は、インデックス付きフィールドのNAME
と、インデックス内のその序数位置を提供します。インデックス (i1) が複数のフィールドで定義されている場合、INNODB_SYS_FIELDS
は、各インデックス付きフィールドのメタデータを提供します。 -
INNODB_SYS_TABLES
からのSPACE
情報を使用して、このテーブルのテーブルスペースに関する情報を取得するためにINNODB_SYS_TABLESPACES
テーブルをクエリーします。mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 57 \G *************************** 1. row *************************** SPACE: 57 NAME: test/t1 FLAG: 0 FILE_FORMAT: Antelope ROW_FORMAT: Compact or Redundant PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 1 row in set (0.00 sec)
INNODB_SYS_TABLESPACES
は、テーブルスペースのSPACE
ID および関連付けられたテーブルのNAME
に加えて、テーブルスペースの形式とストレージの特性に関するビットレベルの情報であるテーブルスペースのFLAG
データを提供します。また、テーブルスペースのFILE_FORMAT
、ROW_FORMAT
、PAGE_SIZE
、およびZIP_PAGE_SIZE
データも提供されます (ZIP_PAGE_SIZE
は、Compressed
行フォーマットのテーブルスペースに適用されます)。 -
ふたたび
INNODB_SYS_TABLES
からのSPACE
情報を使用して、このテーブルスペースのデータファイルの場所を取得するためにINNODB_SYS_DATAFILES
をクエリーします。mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE SPACE = 57 \G *************************** 1. row *************************** SPACE: 57 PATH: ./test/t1.ibd 1 row in set (0.01 sec)
データファイルは、MySQL の
data
ディレクトリの下のtest
ディレクトリにあります。file-per-table テーブルスペースがCREATE TABLE
ステートメントのDATA DIRECTORY
句を使用して MySQL データディレクトリ以外の場所に作成された場合、テーブルスペースのPATH
は完全修飾のディレクトリパスになります。 -
最後の手順として、テーブル
t1
(TABLE_ID = 71
) に行を挿入し、INNODB_SYS_TABLESTATS
テーブル内のデータを表示します。このテーブル内のデータは、InnoDB
テーブルのクエリー時に使用するインデックスを決定するために MySQL オプティマイザによって使用されます。この情報は、インメモリーデータ構造から取得されます。対応する内部InnoDB
システムテーブルはありません。mysql> INSERT INTO t1 VALUES(5, 'abc', 'def'); Query OK, 1 row affected (0.06 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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 1 row in set (0.00 sec)
STATS_INITIALIZED
フィールドは、このテーブルの統計が収集されているかどうかを示します。NUM_ROWS
は、現在の推定されるテーブル内の行数です。CLUST_INDEX_SIZE
およびOTHER_INDEX_SIZE
フィールドはそれぞれ、テーブルのクラスタ化されたインデックスとセカンダリインデックスを格納するディスク上のページの数をレポートします。MODIFIED_COUNTER
値は、外部キーからの DML 操作およびカスケード操作によって変更された行数を示します。AUTOINC
値は、自動インクリメントベースの操作に対して発行される次の番号です。テーブルt1
では自動インクリメントカラムが定義されていないため、この値は 0 です。REF_COUNT
値はカウンタです。このカウンタが 0 に達すると、テーブルキャッシュからテーブルメタデータを削除できることを示します。
例 14.14 外部キーの INFORMATION_SCHEMA システムテーブル
INNODB_SYS_FOREIGN
および INNODB_SYS_FOREIGN_COLS
テーブルは、外部キー関係に関するデータを提供します。この例では、外部キー関係を持つ親テーブルと子テーブルを使用して、INNODB_SYS_FOREIGN
および INNODB_SYS_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_SYS_FOREIGN
をクエリーして、test/child
とtest/parent
の外部キー関係の外部キーデータを見つけます。mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN \G *************************** 1. row *************************** ID: test/fk1 FOR_NAME: test/child REF_NAME: test/parent N_COLS: 1 TYPE: 1 1 row in set (0.00 sec)
メタデータには、子テーブルで定義された
CONSTRAINT
として指定されている外部キーID
(fk1
) が含まれています。FOR_NAME
は、外部キーが定義されている子テーブルの名前です。REF_NAME
は、親テーブル (「参照される」テーブル) の名前です。N_COLS
は、外部キーのインデックス内のカラム数です。TYPE
は、外部キーカラムに関する追加情報を提供するビットフラグを表す数値です。この場合、TYPE
値は 1 です。これは、外部キーに対してON DELETE CASCADE
オプションが指定されたことを示します。TYPE
値の詳細は、INNODB_SYS_FOREIGN
テーブルの定義を参照してください。 -
外部キー
ID
を使用して、この外部キーのカラムに関するデータを表示するためにINNODB_SYS_FOREIGN_COLS
をクエリーします。mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS WHERE ID = 'test/fk1' \G *************************** 1. row *************************** ID: test/fk1 FOR_COL_NAME: parent_id REF_COL_NAME: id POS: 0 1 row in set (0.00 sec)
FOR_COL_NAME
は子テーブル内の外部キーカラムの名前であり、REF_COL_NAME
は親テーブル内の参照されるカラムの名前です。POS
値は、外部キーのインデックス内のキーフィールドの序数位置です (0 から始まります)。
例 14.15 InnoDB INFORMATION_SCHEMA システムテーブルの結合
この例では、employees サンプルデータベース内のテーブルに関するファイル形式、行フォーマット、ページサイズ、およびインデックスサイズ情報を収集するために 3 つの InnoDB
INFORMATION_SCHEMA
システムテーブル (INNODB_SYS_TABLES
、INNODB_SYS_TABLESPACES
、および INNODB_SYS_TABLESTATS
) を結合する方法を示します。
クエリー文字列を短くするために、次のテーブル名のエイリアスが使用されます。
INFORMATION_SCHEMA.INNODB_SYS_TABLES
: aINFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
: bINFORMATION_SCHEMA.INNODB_SYS_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.FILE_FORMAT, 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_SYS_TABLES a
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES b on a.NAME = b.NAME
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS c on b.NAME = c.NAME
WHERE a.NAME LIKE 'employees/%'
ORDER BY a.NAME DESC;
+------------------------+-------------+------------+-----------+-------+-----------+
| NAME | FILE_FORMAT | ROW_FORMAT | page_size | pk_mb | secidx_mb |
+------------------------+-------------+------------+-----------+-------+-----------+
| employees/titles | Antelope | Compact | 16384 | 20 | 11 |
| employees/salaries | Antelope | Compact | 16384 | 91 | 33 |
| employees/employees | Antelope | Compact | 16384 | 15 | 0 |
| employees/dept_manager | Antelope | Compact | 16384 | 0 | 0 |
| employees/dept_emp | Antelope | Compact | 16384 | 12 | 10 |
| employees/departments | Antelope | Compact | 16384 | 0 | 0 |
+------------------------+-------------+------------+-----------+-------+-----------+
6 rows in set (0.01 sec)