MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.3.1 ANALYZE TABLE ステートメント

9.1 English
9.0 English
8.4 English
8.0 English
5.7 English

MySQL 8.0 リファレンスマニュアル / ... / ANALYZE TABLE ステートメント

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

13.7.3.1 ANALYZE TABLE ステートメント

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    UPDATE HISTOGRAM ON col_name [, col_name] ...
        [WITH N BUCKETS]

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    DROP HISTOGRAM ON col_name [, col_name] ...

ANALYZE TABLE では、テーブル統計が生成されます:

どちらの HISTOGRAM 句も指定しない ANALYZE TABLE では、キー分散分析が実行され、指定したテーブルの分散が格納されます。 MyISAM テーブルの場合、キー配布分析用の ANALYZE TABLE は myisamchk --analyze の使用と同等です。
ANALYZE TABLE で UPDATE HISTOGRAM 句を使用すると、指定したテーブルのカラムのヒストグラム統計が生成され、データディクショナリに格納されます。この構文に使用できるテーブル名は 1 つだけです。
ANALYZE TABLE で DROP HISTOGRAM 句を使用すると、指定したテーブルのカラムのヒストグラム統計がデータディクショナリから削除されます。この構文に使用できるテーブル名は 1 つだけです。

このステートメントには、このテーブルに対する SELECT および INSERT 権限が必要です。

ANALYZE TABLE は、InnoDB、NDB および MyISAM テーブルで動作します。ビューでは機能しません。

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

ANALYZE TABLE はパーティションテーブルでサポートされており、ALTER TABLE ... ANALYZE PARTITION を使用して 1 つ以上のパーティションを分析できます。詳細は、セクション13.1.9「ALTER TABLE ステートメント」およびセクション24.3.4「パーティションの保守」を参照してください。

分析中、そのテーブルは InnoDB および MyISAM に対する読み取りロックでロックされます。

ANALYZE TABLE は、フラッシュロックを必要とするテーブル定義キャッシュからテーブルを削除します。長時間実行されているステートメントまたはトランザクションがまだテーブルを使用している場合、後続のステートメントおよびトランザクションは、フラッシュロックが解放される前にこれらの操作が終了するまで待機する必要があります。通常、ANALYZE TABLE 自体は迅速に終了するため、同じテーブルを含む遅延トランザクションまたは遅延ステートメントが残りのフラッシュロックによるものであることは明らかではない場合があります。

デフォルトでは、ANALYZE TABLE ステートメントはレプリカにレプリケートされるようにバイナリログに書き込まれます。ロギングを抑制するには、オプションの NO_WRITE_TO_BINLOG キーワード、またはそのエイリアス LOCAL を指定します。

ANALYZE TABLE の出力

ANALYZE TABLE は、次のテーブルに示すカラムを含む結果セットを返します。

カラム	値
`Table`	テーブル名
`Op`	`analyze` または `histogram`
`Msg_type`	`status`、`error`、`info`、`note`、または `warning`
`Msg_text`	情報メッセージ

キー分布分析

どちらの HISTOGRAM 句も指定しない ANALYZE TABLE では、キー分散分析が実行され、テーブルの分散が格納されます。既存のヒストグラム統計は影響を受けません。

前回のキー分散分析以降にテーブルが変更されていない場合、テーブルは再度分析されません。

MySQL では、格納されたキーの分散を使用して、定数以外の結合でテーブルを結合する順序を決定します。さらに、クエリー内の特定のテーブルにどのインデックスを使用するかを決定する場合は、キー分布を使用できます。

格納されているキー分散カーディナリティを確認するには、SHOW INDEX ステートメントまたは INFORMATION_SCHEMA STATISTICS テーブルを使用します。セクション13.7.7.22「SHOW INDEX ステートメント」およびセクション26.34「INFORMATION_SCHEMA STATISTICS テーブル」を参照してください。

InnoDB テーブルの場合、ANALYZE TABLE は、各インデックスツリーでランダムな除算を実行し、それに応じてインデックスカーディナリティの見積りを更新することで、インデックスカーディナリティを決定します。これらは単なる見積もりであるため、ANALYZE TABLE を繰り返し実行すると、別の数値が生成される可能性があります。これによって ANALYZE TABLE の InnoDB テーブル上での速度は速くなりますが、すべての行が考慮されているわけではないため、100% 正確とは言えません。

セクション15.8.10.1「永続的オプティマイザ統計のパラメータの構成」で説明されているように、innodb_stats_persistent を有効にすることで、ANALYZE TABLE によって収集された statistics をより正確かつ安定させることができます。 innodb_stats_persistent が有効になっている場合、統計は定期的に再計算されないため (サーバーの再起動後など)、インデックスカラムデータに対する大きな変更後に ANALYZE TABLE を実行することが重要です。

innodb_stats_persistent が有効になっている場合は、innodb_stats_persistent_sample_pages システム変数を変更することで、ランダムな除算の数を変更できます。 innodb_stats_persistent が無効になっている場合は、かわりに innodb_stats_transient_sample_pages を変更します。

InnoDB でのキー配布分析の詳細は、セクション15.8.10.1「永続的オプティマイザ統計のパラメータの構成」およびセクション15.8.10.3「InnoDB テーブルに対する ANALYZE TABLE の複雑さの推定」を参照してください。

MySQL では、結合の最適化にインデックスカーディナリティの見積りが使用されます。結合が適切な方法で最適化されていない場合は、ANALYZE TABLE を実行してみてください。 ANALYZE TABLE では特定のテーブルに十分な値が生成されない場合は、特定のインデックスの使用を強制するクエリーとともに FORCE INDEX を使用するか、または MySQL でテーブルスキャンよりもインデックス検索が優先されるように max_seeks_for_key システム変数を設定してください。セクションB.3.5「オプティマイザ関連の問題」を参照してください。

ヒストグラム統計分析

ANALYZE TABLE で HISTOGRAM 句を使用すると、テーブルのカラム値のヒストグラム統計を管理できます。ヒストグラム統計の詳細は、セクション8.9.6「オプティマイザ統計」を参照してください。

次のヒストグラム操作を使用できます:

ANALYZE TABLE で UPDATE HISTOGRAM 句を使用すると、指定したテーブルのカラムのヒストグラム統計が生成され、データディクショナリに格納されます。この構文に使用できるテーブル名は 1 つだけです。

オプションの WITH N BUCKETS 句では、ヒストグラムのバケット数を指定します。 N の値は、1 から 1024 の範囲の整数である必要があります。この句を省略すると、バケットの数は 100 になります。
ANALYZE TABLE で DROP HISTOGRAM 句を使用すると、指定したテーブルのカラムのヒストグラム統計がデータディクショナリから削除されます。この構文に使用できるテーブル名は 1 つだけです。

ストアドヒストグラム管理ステートメントは、名前付きのカラムにのみ影響します。これらのステートメントを考慮してください。

ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c2, c3 WITH 10 BUCKETS;
ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c3 WITH 10 BUCKETS;
ANALYZE TABLE t DROP HISTOGRAM ON c2;

最初のステートメントは、c1、c2 および c3 カラムのヒストグラムを更新し、それらのカラムの既存のヒストグラムを置き換えます。 2 番目のステートメントは、c1 および c3 のヒストグラムを更新し、c2 ヒストグラムは影響を受けません。 3 番目のステートメントは、c2 のヒストグラムを削除し、c1 および c3 のヒストグラムは影響を受けません。

ヒストグラム生成は、暗号化されたテーブル (統計内のデータの公開を回避するため) または TEMPORARY テーブルではサポートされていません。

ヒストグラム生成は、ジオメトリタイプ (空間データ) および JSON を除くすべてのデータ型のカラムに適用されます。

ヒストグラムは、格納されたカラムおよび仮想生成されたカラムに対して生成できます。

ヒストグラムは、単一カラムの一意インデックスでカバーされるカラムに対しては生成できません。

ヒストグラム管理ステートメントは、リクエストされた操作をできるだけ多く実行しようとし、残りの診断メッセージをレポートします。たとえば、UPDATE HISTOGRAM ステートメントで複数のカラムを指定したが、その一部が存在しないか、サポートされていないデータ型を持つ場合、他のカラムに対してヒストグラムが生成され、無効なカラムに対してメッセージが生成されます。

ヒストグラムは、次の DDL ステートメントの影響を受けます:

DROP TABLE では、削除されたテーブルのカラムのヒストグラムが削除されます。
DROP DATABASE では、データベース内のすべてのテーブルが削除されるため、ステートメントは削除されたデータベース内のすべてのテーブルのヒストグラムを削除します。
RENAME TABLE ではヒストグラムは削除されません。かわりに、名前を変更したテーブルが新しいテーブル名に関連付けられるようにヒストグラムの名前を変更します。
カラムを削除または変更する ALTER TABLE ステートメントは、そのカラムのヒストグラムを削除します。
ALTER TABLE ... CONVERT TO CHARACTER SET では、文字セットの変更の影響を受けるため、文字カラムのヒストグラムは削除されます。非文字カラムのヒストグラムは影響を受けません。

histogram_generation_max_mem_size システム変数は、ヒストグラム生成に使用できるメモリーの最大量を制御します。グローバル値とセッション値は、実行時に設定できます。

グローバル histogram_generation_max_mem_size 値を変更するには、グローバルシステム変数を設定するのに十分な権限が必要です。セッションの histogram_generation_max_mem_size 値を変更するには、制限付きセッションシステム変数を設定するのに十分な権限が必要です。セクション5.1.9.1「システム変数権限」を参照してください。

ヒストグラム生成のためにメモリーに読み込まれるデータの推定量が histogram_generation_max_mem_size で定義されている制限を超える場合、MySQL はデータをすべてメモリーに読み取るのではなくサンプリングします。サンプリングは、テーブル全体に均等に分散されます。 MySQL では、ページレベルのサンプリング方法である SYSTEM サンプリングを使用します。

INFORMATION_SCHEMA.COLUMN_STATISTICS テーブルの HISTOGRAM カラムの sampling-rate 値をクエリーして、ヒストグラムを作成するためにサンプリングされたデータの割合を判断できます。 sampling-rate は、0.0 と 1.0 の間の数値です。値 1 は、すべてのデータが読み取られたことを意味します (サンプリングなし)。

次の例では、サンプリングを示します。この例では、データ量が histogram_generation_max_mem_size の制限を超えていることを確認するために、employees テーブルの birth_date カラムのヒストグラム統計を生成する前に、制限は低い値 (2000000 バイト) に設定されます。

mysql> SET histogram_generation_max_mem_size = 2000000;

mysql> USE employees;

mysql> ANALYZE TABLE employees UPDATE HISTOGRAM ON birth_date WITH 16 BUCKETS\G
*************************** 1. row ***************************
   Table: employees.employees
      Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'birth_date'.

mysql> SELECT HISTOGRAM->>'$."sampling-rate"'
       FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
       WHERE TABLE_NAME = "employees"
       AND COLUMN_NAME = "birth_date";
+---------------------------------+
| HISTOGRAM->>'$."sampling-rate"' |
+---------------------------------+
| 0.0491431208869665              |
+---------------------------------+

sampling-rate 値が 0.0491431208869665 の場合、birth_date カラムのデータの約 4.9% がヒストグラム統計を生成するためにメモリーに読み取られたことを意味します。

MySQL 8.0.19 の時点で、InnoDB ストレージエンジンは、InnoDB テーブルに格納されているデータに対して独自のサンプリング実装を提供します。ストレージエンジンが独自のサンプリング実装を提供しない場合に MySQL で使用されるデフォルトのサンプリング実装では、大規模なテーブルに対してコストがかかる全テーブルスキャンが必要です。 InnoDB サンプリング実装では、全テーブルスキャンを回避することでサンプリングパフォーマンスが向上します。

sampled_pages_read および sampled_pages_skipped INNODB_METRICS カウンタを使用して、InnoDB データページのサンプリングを監視できます。 (INNODB_METRICS カウンタの一般的な使用方法については、セクション26.51.22「INFORMATION_SCHEMA INNODB_METRICS テーブル」を参照してください。)

次の例は、ヒストグラム統計を生成する前にカウンタを有効にする必要があるサンプリングカウンタの使用方法を示しています。

mysql> SET GLOBAL innodb_monitor_enable = 'sampled%';

mysql> USE employees;

mysql> ANALYZE TABLE employees UPDATE HISTOGRAM ON birth_date WITH 16 BUCKETS\G
*************************** 1. row ***************************
   Table: employees.employees
      Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'birth_date'.

mysql> USE INFORMATION_SCHEMA;

mysql> SELECT NAME, COUNT FROM INNODB_METRICS WHERE NAME LIKE 'sampled%'\G
*************************** 1. row ***************************
 NAME: sampled_pages_read
COUNT: 43
*************************** 2. row ***************************
 NAME: sampled_pages_skipped
COUNT: 843

この式は、サンプリングカウンタデータに基づいてサンプリングレートを概算します:

sampling rate = sampled_page_read/(sampled_pages_read + sampled_pages_skipped)

サンプリングカウンタデータに基づくサンプリングレートは、INFORMATION_SCHEMA.COLUMN_STATISTICS テーブルの HISTOGRAM カラムの sampling-rate 値とほぼ同じです。

ヒストグラム生成のために実行されるメモリー割り当てについては、パフォーマンススキーマ memory/sql/histograms インストゥルメントをモニターしてください。セクション27.12.18.10「メモリーサマリーテーブル」を参照してください。

その他の考慮事項

ANALYZE TABLE は、INFORMATION_SCHEMA.INNODB_TABLESTATS テーブルからテーブル統計をクリアし、STATS_INITIALIZED カラムを Uninitialized に設定します。統計は、次回テーブルにアクセスしたときに再度収集されます。

PREV HOME UP NEXT