データベースを監視するアプリケーションでは、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 に対するクエリーを処理します。