MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.2.3 INFORMATION

version 8.0

9.1 English
9.0 English
8.4 English
8.0 English
5.7 English

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

このページは機械翻訳したものです。

8.2.3 INFORMATION_SCHEMA クエリーの最適化

データベースを監視するアプリケーションでは、INFORMATION_SCHEMA テーブルを頻繁に使用できます。これらのテーブルに対するクエリーを最も効率的に記述するには、次の一般的なガイドラインを使用します:

データディクショナリテーブルのビューである INFORMATION_SCHEMA テーブルのみのクエリーを試行します。
静的メタデータのみのクエリーを試行してください。動的メタデータのカラムを選択するか、取得条件を静的メタデータとともに使用すると、動的メタデータを処理するためのオーバーヘッドが増加します。

注記

INFORMATION_SCHEMA クエリーでのデータベース名とテーブル名の比較動作は、予想とは異なる場合があります。詳細は、セクション10.8.7「INFORMATION_SCHEMA 検索での照合の使用」を参照してください。

これらの INFORMATION_SCHEMA テーブルはデータディクショナリテーブルのビューとして実装されるため、これらのテーブルに対するクエリーではデータディクショナリから情報が取得されます:

CHARACTER_SETS
CHECK_CONSTRAINTS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
EVENTS
FILES
INNODB_COLUMNS
INNODB_DATAFILES
INNODB_FIELDS
INNODB_FOREIGN
INNODB_FOREIGN_COLS
INNODB_INDEXES
INNODB_TABLES
INNODB_TABLESPACES
INNODB_TABLESPACES_BRIEF
INNODB_TABLESTATS
KEY_COLUMN_USAGE
PARAMETERS
PARTITIONS
REFERENTIAL_CONSTRAINTS
RESOURCE_GROUPS
ROUTINES
SCHEMATA
STATISTICS
TABLES
TABLE_CONSTRAINTS
TRIGGERS
VIEWS
VIEW_ROUTINE_USAGE
VIEW_TABLE_USAGE

一部のタイプの値は、非ビューの INFORMATION_SCHEMA テーブルの場合でも、データディクショナリからの参照によって取得されます。これには、データベース名、テーブル名、テーブルタイプ、ストレージエンジンなどの値が含まれます。

一部の INFORMATION_SCHEMA テーブルには、テーブル統計を提供するカラムが含まれています:

STATISTICS.CARDINALITY
TABLES.AUTO_INCREMENT
TABLES.AVG_ROW_LENGTH
TABLES.CHECKSUM
TABLES.CHECK_TIME
TABLES.CREATE_TIME
TABLES.DATA_FREE
TABLES.DATA_LENGTH
TABLES.INDEX_LENGTH
TABLES.MAX_DATA_LENGTH
TABLES.TABLE_ROWS
TABLES.UPDATE_TIME

これらのカラムは、動的テーブルメタデータ、つまりテーブルの内容の変更に応じて変更される情報を表します。

デフォルトでは、MySQL は、カラムのクエリー時に mysql.index_stats および mysql.table_stats ディクショナリテーブルからこれらのカラムのキャッシュされた値を取得します。これは、ストレージエンジンから統計を直接取得するよりも効率的です。キャッシュされた統計が使用できないか、期限切れになっている場合、MySQL はストレージエンジンから最新の統計を取得し、mysql.index_stats および mysql.table_stats ディクショナリテーブルにキャッシュします。後続のクエリーでは、キャッシュされた統計が期限切れになるまで、キャッシュされた統計が取得されます。

information_schema_stats_expiry セッション変数は、キャッシュされた統計が期限切れになるまでの期間を定義します。デフォルトは 86400 秒 (24 時間) ですが、期間は 1 年まで延長できます。

特定のテーブルのキャッシュされた値をいつでも更新するには、ANALYZE TABLE を使用します。

次の場合、統計カラムのクエリーでは、mysql.index_stats および mysql.table_stats ディクショナリテーブルの統計は格納または更新されません:

キャッシュされた統計が失効していない場合。
information_schema_stats_expiry が 0 に設定されている場合。
サーバーが read_only, super_read_only, transaction_read_only または innodb_read_only モードで起動されたとき。
クエリーでパフォーマンススキーマデータもフェッチされる場合。

information_schema_stats_expiry はセッション変数であり、各クライアントセッションで独自の有効期限値を定義できます。ストレージエンジンから取得され、あるセッションによってキャッシュされた統計は、ほかのセッションで使用できます。

注記

innodb_read_only システム変数が有効になっている場合、InnoDB を使用するデータディクショナリの統計テーブルを更新できないため、ANALYZE TABLE が失敗することがあります。キー分散を更新する ANALYZE TABLE 操作では、操作によってテーブル自体が更新された場合でも (MyISAM テーブルの場合など)、障害が発生する可能性があります。更新された分散統計を取得するには、information_schema_stats_expiry=0 を設定します。

データディクショナリテーブルのビューとして実装された INFORMATION_SCHEMA テーブルの場合、基礎となるデータディクショナリテーブルのインデックスを使用すると、オプティマイザで効率的なクエリー実行計画を作成できます。オプティマイザによる選択を確認するには、EXPLAIN を使用します。サーバーが INFORMATION_SCHEMA クエリーを実行するために使用するクエリーも表示するには、EXPLAIN の直後に SHOW WARNINGS を使用します。

utf8mb4 文字セットの照合順序を識別する次のステートメントについて考えてみます:

mysql> SELECT COLLATION_NAME
       FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
       WHERE CHARACTER_SET_NAME = 'utf8mb4';
+----------------------------+
| COLLATION_NAME             |
+----------------------------+
| utf8mb4_general_ci         |
| utf8mb4_bin                |
| utf8mb4_unicode_ci         |
| utf8mb4_icelandic_ci       |
| utf8mb4_latvian_ci         |
| utf8mb4_romanian_ci        |
| utf8mb4_slovenian_ci       |
...

サーバーはこのステートメントをどのように処理しますか。確認するには、EXPLAIN を使用します:

mysql> EXPLAIN SELECT COLLATION_NAME
       FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
       WHERE CHARACTER_SET_NAME = 'utf8mb4'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cs
   partitions: NULL
         type: const
possible_keys: PRIMARY,name
          key: name
      key_len: 194
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: col
   partitions: NULL
         type: ref
possible_keys: character_set_id
          key: character_set_id
      key_len: 8
          ref: const
         rows: 68
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.01 sec)

そのステートメントの静的化に使用されたクエリーを表示するには、SHOW WARNINGS を使用します:

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `mysql`.`col`.`name` AS `COLLATION_NAME`
         from `mysql`.`character_sets` `cs`
         join `mysql`.`collations` `col`
         where ((`mysql`.`col`.`character_set_id` = '45')
         and ('utf8mb4' = 'utf8mb4'))

SHOW WARNINGS で示されているように、サーバーは、mysql システムデータベースの character_sets および collations データディクショナリテーブルに対するクエリーとして COLLATION_CHARACTER_SET_APPLICABILITY に対するクエリーを処理します。

PREV HOME UP NEXT