Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 26.8Mb
PDF (A4) - 26.8Mb
MySQL 5.6 リファレンスマニュアル  /  ...  /  InnoDB INFORMATION_SCHEMA システムテーブル

14.14.3 InnoDB INFORMATION_SCHEMA システムテーブル

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='directory' 句に対するサポートの導入とともに MySQL 5.6.6 で追加されました。 

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 システムテーブル内に見つかったメタデータのタイプを示します。

  1. テストデータベースとテーブル 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);

  2. テーブル 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 行フォーマットのテーブルにのみ適用されます。

  3. 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) を提供します。

  4. ふたたび 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 以外の値は、そのインデックスがシステムテーブルスペース内に存在しないことを示します。

  5. 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 は、各インデックス付きフィールドのメタデータを提供します。

  6. 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 行フォーマットのテーブルスペースに適用されます)。

  7. ふたたび 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 は完全修飾のディレクトリパスになります。

  8. 最後の手順として、テーブル 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 テーブル内に見つかったデータを示します。

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

    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;

  2. 親テーブルと子テーブルが作成されたら、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 テーブルの定義を参照してください。

  3. 外部キー 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: a

  • INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES: b

  • INFORMATION_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)


PREV   HOME   UP   NEXT
Download this Manual
PDF (US Ltr) - 26.8Mb
PDF (A4) - 26.8Mb