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

どちらの 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 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 ステートメントの影響を受けます:

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 に設定します。 統計は、次回テーブルにアクセスしたときに再度収集されます。