Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.2Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


MySQL 5.6 リファレンスマニュアル  /  ...  /  INFORMATION_SCHEMA クエリーの最適化

8.2.4 INFORMATION_SCHEMA クエリーの最適化

データベースをモニターするアプリケーションでは、INFORMATION_SCHEMA テーブルを頻繁に使用することがあります。INFORMATION_SCHEMA テーブルに対する特定の種類のクエリーは、高速に実行するように最適化できます。この目標は、ファイル操作 (ディレクトリのスキャンやテーブルファイルを開くなど) を最小限にし、これらの動的テーブルを構成する情報を収集することです。これらの最適化は、INFORMATION_SCHEMA テーブルの検索にどのような照合順序が使われるかに影響します。詳細は、セクション10.1.7.9「照合順序と INFORMATION_SCHEMA 検索」を参照してください。

1) WHERE 句のデータベース名とテーブル名には定数のルックアップ値を使用してみます

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

  • データベースやテーブルをルックアップするには、リテラル値、定数を返す関数、スカラーサブクエリーなど、定数に評価される式を使用します。

  • 一致するデータベースディレクトリ名を見つけるためにデータディレクトリのスキャンが必要になるため、非定数のデータベース名ルックアップ値を使用する (またはルックアップ値を使用しない) クエリーを避けます。

  • データベース内では、一致するテーブルファイルを見つけるためにデータベースディレクトリのスキャンが必要になるため、非定数のテーブル名ルックアップ値を使用する (またはルックアップ値を使用しない) クエリーを避けます。

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

テーブル データディレクトリスキャンを避けるために指定するカラム データベースディレクトリスキャンを避けるために指定するカラム
COLUMNS TABLE_SCHEMA TABLE_NAME
KEY_COLUMN_USAGE TABLE_SCHEMA TABLE_NAME
PARTITIONS TABLE_SCHEMA TABLE_NAME
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA TABLE_NAME
STATISTICS TABLE_SCHEMA TABLE_NAME
TABLES TABLE_SCHEMA TABLE_NAME
TABLE_CONSTRAINTS TABLE_SCHEMA TABLE_NAME
TRIGGERS EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE
VIEWS TABLE_SCHEMA TABLE_NAME

特定の定数のデータベース名に制限されたクエリーの利点は、指定したデータベースディレクトリのみをチェックするだけで済むことです。例:

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 ファイルも開く必要があります。

ファイルオープンの最適化の種類は、次のように表されます。

  • SKIP_OPEN_TABLE: テーブルファイルを開く必要はありません。データベースディレクトリをスキャンすることによって、クエリー内ですでに情報を使用できるようになっています。

  • OPEN_FRM_ONLY: テーブルの .frm ファイルのみを開く必要があります。

  • OPEN_TRIGGER_ONLY: テーブルの .TRG ファイルのみを開く必要があります。

  • OPEN_FULL_TABLE: 最適化されていない情報のルックアップ。.frm.MYD、および .MYI ファイルを開く必要があります。

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

  • COLUMNS: OPEN_FRM_ONLY がすべてのカラムに適用されます

  • KEY_COLUMN_USAGE: OPEN_FULL_TABLE がすべてのカラムに適用されます

  • PARTITIONS: OPEN_FULL_TABLE がすべてのカラムに適用されます

  • REFERENTIAL_CONSTRAINTS: OPEN_FULL_TABLE がすべてのカラムに適用されます

  • STATISTICS:

    カラム 最適化の種類
    TABLE_CATALOG OPEN_FRM_ONLY
    TABLE_SCHEMA OPEN_FRM_ONLY
    TABLE_NAME OPEN_FRM_ONLY
    NON_UNIQUE OPEN_FRM_ONLY
    INDEX_SCHEMA OPEN_FRM_ONLY
    INDEX_NAME OPEN_FRM_ONLY
    SEQ_IN_INDEX OPEN_FRM_ONLY
    COLUMN_NAME OPEN_FRM_ONLY
    COLLATION OPEN_FRM_ONLY
    CARDINALITY OPEN_FULL_TABLE
    SUB_PART OPEN_FRM_ONLY
    PACKED OPEN_FRM_ONLY
    NULLABLE OPEN_FRM_ONLY
    INDEX_TYPE OPEN_FULL_TABLE
    COMMENT OPEN_FRM_ONLY
  • TABLES:

    カラム 最適化の種類
    TABLE_CATALOG SKIP_OPEN_TABLE
    TABLE_SCHEMA SKIP_OPEN_TABLE
    TABLE_NAME SKIP_OPEN_TABLE
    TABLE_TYPE OPEN_FRM_ONLY
    ENGINE OPEN_FRM_ONLY
    VERSION OPEN_FRM_ONLY
    ROW_FORMAT OPEN_FULL_TABLE
    TABLE_ROWS OPEN_FULL_TABLE
    AVG_ROW_LENGTH OPEN_FULL_TABLE
    DATA_LENGTH OPEN_FULL_TABLE
    MAX_DATA_LENGTH OPEN_FULL_TABLE
    INDEX_LENGTH OPEN_FULL_TABLE
    DATA_FREE OPEN_FULL_TABLE
    AUTO_INCREMENT OPEN_FULL_TABLE
    CREATE_TIME OPEN_FULL_TABLE
    UPDATE_TIME OPEN_FULL_TABLE
    CHECK_TIME OPEN_FULL_TABLE
    TABLE_COLLATION OPEN_FRM_ONLY
    CHECKSUM OPEN_FULL_TABLE
    CREATE_OPTIONS OPEN_FRM_ONLY
    TABLE_COMMENT OPEN_FRM_ONLY
  • TABLE_CONSTRAINTS: OPEN_FULL_TABLE がすべてのカラムに適用されます

  • TRIGGERS: OPEN_TRIGGER_ONLY がすべてのカラムに適用されます

  • VIEWS:

    カラム 最適化の種類
    TABLE_CATALOG OPEN_FRM_ONLY
    TABLE_SCHEMA OPEN_FRM_ONLY
    TABLE_NAME OPEN_FRM_ONLY
    VIEW_DEFINITION OPEN_FRM_ONLY
    CHECK_OPTION OPEN_FRM_ONLY
    IS_UPDATABLE OPEN_FULL_TABLE
    DEFINER OPEN_FRM_ONLY
    SECURITY_TYPE OPEN_FRM_ONLY
    CHARACTER_SET_CLIENT OPEN_FRM_ONLY
    COLLATION_CONNECTION OPEN_FRM_ONLY

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

これは特に、複数のデータベースの情報を検索し、長時間かかり、パフォーマンスに影響を与える可能性のある 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 が適用されます)。EXPLAINOPEN_FULL_TABLE (SKIP_OPEN_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 最適化が適用されます。EXPLAINOPEN_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 出力行の場合: 定数のデータベースルックアップ値およびテーブルルックアップ値により、サーバーは TABLES の値のディレクトリスキャンを回避できます。TABLES.TABLE_NAME の参照には、さらにテーブルファイルは必要ありません。

2 つめの EXPLAIN 出力行の場合 : 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 テーブルのいずれでもないため、最適化は適用されません。


User Comments
  Posted by Shlomi Noach on October 3, 2011
The example for joining TABLES with COLUMNS shows good EXPLAIN plan for TABLES, but poor EXPLAIN plan for columns, since the table name and schema for COLUMNS cannot be deduced ahead.
But this is just due to poor optimizer's work.
Here's how to get the same results, but with far better execution plan; we push the constants down to COLUMNS:

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 = 't1'
-> AND B.TABLE_SCHEMA = 'test'
->
-> \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: TABLE_SCHEMA,TABLE_NAME
key_len: NULL
ref: NULL
rows: NULL
Extra: Using where; Open_frm_only; Scanned 0 databases; Using join buffer
2 rows in set (0.00 sec)

Sign Up Login You must be logged in to post a comment.