MySQL 5.1.23 以降、INFORMATION_SCHEMA テーブルに対する特定の種類のクエリーを最適化してより高速に実行できるように、INFORMATION_SCHEMA の実装が変更されました。この節では、これらの最適化を活用するクエリーの作成方法についてガイドラインを示します。一般に、ここで説明する方法では、サーバーで INFORMATION_SCHEMA テーブルの内容を取得するために必要となるファイルシステムへのアクセスが最小限に抑えられます。サーバーでディレクトリをスキャンしたりテーブルファイルを開いたりする必要がなくなるようにクエリーを作成すると、パフォーマンスが向上します。

1) WHERE 節のデータベース名とテーブル名には定数の検索値を使用するようにします

この原則は次のように活用できます。

この原則は、次の表に示す INFORMATION_SCHEMA テーブルに適用されます。この表に示されたカラムに定数の検索値を使用すると、サーバーでのディレクトリスキャンを回避できます。たとえば、TABLES から選択する場合は、WHERE 節で TABLE_SCHEMA に定数の検索値を使用すると、データディレクトリのスキャンを回避できます。

特定の定数のデータベース名に制限されたクエリーには、指定のデータベースディレクトリだけを確認すれば済むという利点があります。例 :

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test';

リテラルのデータベース名 test を使用すると、データベースがいくつある場合でも、サーバーは test データベースディレクトリだけを確認すれば済みます。これに対し、次のクエリーでは、パターン 'test%' に一致するデータベース名を調べるためにデータディレクトリをスキャンする必要があるため、効率が低くなります。

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'test%';

特定の定数のテーブル名に制限されたクエリーの場合、対応するデータベースディレクトリ内で指定のテーブルだけを確認すれば済みます。例 :

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';

リテラルのテーブル名 t1 を使用すると、test データベースにテーブルがいくつある場合でも、サーバーは t1 テーブルのファイルだけを確認すれば済みます。これに対し、次のクエリーでは、パターン 't%' に一致するテーブル名を調べるために test データベースディレクトリをスキャンする必要があります。

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't%';

次のクエリーでは、パターン 'test%' に一致するデータベース名を調べるためにデータディレクトリをスキャンする必要があります。また、一致するデータベースごとに、パターン 't%' に一致するテーブル名を調べるためにデータベースディレクトリをスキャンする必要があります。

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test%' AND TABLE_NAME LIKE 't%';

2) 開く必要のあるテーブルファイルの数が最小になるようにクエリーを作成します

特定の INFORMATION_SCHEMA テーブルカラムを参照するクエリーでは、開く必要のあるテーブルファイルの数を最小にするいくつかの最適化を使用できます。例 :

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test';

この場合、サーバーがデータベースディレクトリをスキャンしてデータベース内のテーブルの名前を調べたあとは、それらの名前が使用可能になるため、ファイルシステムの検索を追加で実行する必要はありません。したがって、TABLE_NAME ではどのファイルも開く必要がありません。ENGINE (ストレージエンジン) の値は、テーブルの .frm ファイルを開くことで判定できます。.MYD や .MYI といったほかのテーブルファイルを開く必要はありません。

MyISAM テーブルの INDEX_LENGTH など、一部の値では .MYD または .MYI ファイルも開く必要があります。

ファイルを開く処理の最適化のタイプは、次のように表されます。

次のリストに、上記の最適化タイプがどのように INFORMATION_SCHEMA テーブルカラムに適用されるかを示します。ここに示されていないテーブルとカラムには、どの最適化も適用されません。

3) EXPLAIN を使用して、サーバーがクエリーに INFORMATION_SCHEMA 最適化を使用できるかどうかを調べます

上記の最適化のうち、サーバーで INFORMATION_SCHEMA クエリーの評価に使用できるものがあれば、EXPLAIN の出力の Extra 値に表示されます。次の例は、Extra 値に表示される可能性のある情報の種類を示しています。

mysql> EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE
    -> TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: VIEWS
         type: ALL
possible_keys: NULL
          key: TABLE_SCHEMA,TABLE_NAME
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Open_frm_only; Scanned 0 databases

定数のデータベース検索値およびテーブル検索値を使用すると、サーバーはディレクトリスキャンを回避できます。VIEWS.TABLE_NAME の参照には、.frm ファイルだけを開く必要があります。

mysql> EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLES
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Open_full_table; Scanned all databases

検索値が指定されていない (WHERE 節がない) ため、サーバーはデータディレクトリと各データベースディレクトリをスキャンする必要があります。このようにして特定された各テーブルについて、テーブル名と行形式が選択されます。TABLE_NAME では、さらにテーブルファイルを開く必要はありません (SKIP_OPEN_TABLE 最適化が適用される)。ROW_FORMAT では、すべてのテーブルファイルを開く必要があります (OPEN_FULL_TABLE が適用される)。OPEN_FULL_TABLE の方が SKIP_OPEN_TABLE より負荷が大きいため、EXPLAIN では OPEN_FULL_TABLE が報告されます。

mysql> EXPLAIN SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'test'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLES
         type: ALL
possible_keys: NULL
          key: TABLE_SCHEMA
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Open_frm_only; Scanned 1 database

テーブル名の検索値が指定されていないため、サーバーは test データベースディレクトリをスキャンする必要があります。TABLE_NAME カラムと TABLE_TYPE カラムには、それぞれ SKIP_OPEN_TABLE 最適化と OPEN_FRM_ONLY 最適化が適用されます。OPEN_FRM_ONLY の方が負荷が大きいため、EXPLAIN では OPEN_FRM_ONLY が報告されます。

mysql> EXPLAIN SELECT B.TABLE_NAME
    -> FROM INFORMATION_SCHEMA.TABLES AS A, INFORMATION_SCHEMA.COLUMNS AS B
    -> WHERE A.TABLE_SCHEMA = 'test'
    -> AND A.TABLE_NAME = 't1'
    -> AND B.TABLE_NAME = A.TABLE_NAME\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: A
         type: ALL
possible_keys: NULL
          key: TABLE_SCHEMA,TABLE_NAME
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Skip_open_table; Scanned 0 databases
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: B
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Open_frm_only; Scanned all databases;
               Using join buffer

EXPLAIN 出力の 1 行目: 定数のデータベース検索値およびテーブル検索値により、サーバーは TABLES の値のディレクトリスキャンを回避できます。TABLES.TABLE_NAME の参照には、さらにテーブルファイルを開く必要はありません。

EXPLAIN 出力の 2 行目: COLUMNS テーブルのすべての値が OPEN_FRM_ONLY 検索なので、COLUMNS.TABLE_NAME では、.frm ファイルを開く必要があります。

mysql> EXPLAIN SELECT * FROM INFORMATION_SCHEMA.COLLATIONS\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: COLLATIONS
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:

この場合、COLLATIONS は最適化を使用できる INFORMATION_SCHEMA テーブルではないため、最適化は適用されません。