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では、InnoDBfile-per-table および一般テーブルスペースのデータファイルパス情報が提供されます。INNODB_TABLESPACESは、InnoDBfile-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: NULLTABLE_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: 50INNODB_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: 0INNODB_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: normalINNODB_TABLESPACESでは、テーブルスペースのSPACEID および関連付けられたテーブルの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: 1STATS_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: 0FOR_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: aINFORMATION_SCHEMA.INNODB_TABLESPACES: bINFORMATION_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 |
+------------------------+------------+-----------+-------+-----------+