統計入門
TiDB は、統計情報をオプティマイザへの入力として使用し、SQL ステートメントの各プラン ステップで処理される行数を推定します。オプティマイザは、 インデックスアクセスとテーブル結合のシーケンスを含む、使用可能な各プラン選択のコストを推定し、使用可能な各プランのコストを生成します。次に、オプティマイザは、全体的なコストが最も低い実行プランを選択します。
統計を収集する
このセクションでは、統計を収集する 2 つの方法 (自動更新と手動収集) について説明します。
自動更新
INSERT 、 DELETE 、またはUPDATEステートメントの場合、TiDB は統計内の行数と変更された行数を自動的に更新します。
TiDB は 60 秒ごとに更新情報を保持します。
テーブルへの変更の数に基づいて、TiDB はそれらのテーブルの統計情報を収集するANALYZEを自動的に設定します。これは、 tidb_enable_auto_analyzeシステム変数と次のtidb_auto_analyze%変数によって制御されます。
| システム変数 | デフォルト値 | 説明 |
|---|---|---|
tidb_enable_auto_analyze | ON | TiDB がANALYZE自動的に実行するかどうかを制御します。 |
tidb_auto_analyze_ratio | 0.5 | 自動更新のしきい値。 |
tidb_auto_analyze_start_time | 00:00 +0000 | TiDB が自動更新を実行できる 1 日の開始時刻。 |
tidb_auto_analyze_end_time | 23:59 +0000 | TiDB が自動更新を実行できる 1 日の終了時刻。 |
tidb_auto_analyze_partition_batch_size | 128 | パーティションテーブルを分析するときに (つまり、パーティションテーブルの統計を自動的に更新するときに) TiDB が自動的に分析するパーティションの数。 |
tidb_enable_auto_analyze_priority_queue | ON | 優先キューを有効にして、統計を自動的に収集するタスクをスケジュールするかどうかを制御します。この変数を有効にすると、TiDB は、新しく作成されたインデックスやパーティションが変更されたパーティション テーブルなど、収集する価値の高いテーブルの統計の収集を優先します。さらに、TiDB はヘルス スコアが低いテーブルを優先し、キューの先頭に配置します。 |
テーブル内の変更された行数とtblの行の合計数の比率がtidb_auto_analyze_ratioより大きく、現在の時刻がtidb_auto_analyze_start_timeからtidb_auto_analyze_end_timeの間である場合、TiDB はバックグラウンドでANALYZE TABLE tblステートメントを実行し、このテーブルの統計を自動的に更新します。
小さなテーブルのデータを変更すると自動更新が頻繁にトリガーされる状況を回避するために、テーブルの行数が 1000 未満の場合、変更によって TiDB の自動更新はトリガーされません。 SHOW STATS_METAステートメントを使用して、テーブル内の行数を表示できます。
注記：
現在、自動更新では、手動
ANALYZEで入力した設定項目は記録されません。そのため、WITH構文を使用してANALYZEの収集動作を制御する場合は、統計情報を収集するためのスケジュールされたタスクを手動で設定する必要があります。
手動収集
現在、TiDB は完全なコレクションとして統計を収集します。統計を収集するには、 ANALYZE TABLEステートメントを実行できます。
次の構文を使用して完全なコレクションを実行できます。
TableNameList内のすべてのテーブルの統計を収集するには:ANALYZE TABLE TableNameList [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH]|[WITH NUM SAMPLES|WITH FLOATNUM SAMPLERATE];WITH NUM BUCKETS生成されたヒストグラム内のバケットの最大数を指定します。WITH NUM TOPN生成されるTOPNの最大数を指定します。WITH NUM CMSKETCH DEPTHCM スケッチの深さを指定します。WITH NUM CMSKETCH WIDTHCM スケッチの幅を指定します。WITH NUM SAMPLESサンプル数を指定します。WITH FLOAT_NUM SAMPLERATEサンプリング レートを指定します。
WITH NUM SAMPLESとWITH FLOAT_NUM SAMPLERATEサンプルを収集する 2 つの異なるアルゴリズムに対応します。
詳しい説明についてはヒストグラム 、 トップN 、 CMSketch (Count-Min Sketch) を参照してください。 SAMPLES / SAMPLERATEについては収集パフォーマンスの向上を参照してください。
オプションを永続化して再利用しやすくする方法については、 ANALYZE構成を永続化する参照してください。
統計の種類
このセクションでは、ヒストグラム、Count-Min Sketch、Top-N の 3 種類の統計について説明します。
ヒストグラム
ヒストグラム統計は、間隔述語または範囲述語の選択性を推定するためにオプティマイザによって使用され、統計のバージョン 2 で等号/IN 述語を推定するために列内の異なる値の数を決定するために使用されることもあります ( 統計のバージョンを参照)。
ヒストグラムは、データの分布を大まかに表したものです。値の範囲全体を一連のバケットに分割し、バケットに含まれる値の数など、単純なデータを使用して各バケットを説明します。TiDB では、各テーブルの特定の列に対して等深度ヒストグラムが作成されます。等深度ヒストグラムを使用して、間隔クエリを推定できます。
ここで「等深度」とは、各バケットに入る値の数が可能な限り等しくなることを意味します。たとえば、特定のセット {1.6、1.9、1.9、2.0、2.4、2.6、2.7、2.7、2.8、2.9、3.4、3.5} に対して、4 つのバケットを生成するとします。等深度ヒストグラムは次のようになります。[1.6、1.9]、[2.0、2.6]、[2.7、2.8]、[2.9、3.5] の 4 つのバケットが含まれます。バケットの深さは 3 です。
ヒストグラムのバケット数の上限を決定するパラメータの詳細については、 手動収集を参照してください。バケット数が多いほどヒストグラムの精度は高くなりますが、精度が高くなるとメモリリソースの使用量が増大します。実際のシナリオに応じて、この数値を適切に調整してください。
カウントミニマムスケッチ
注記：
Count-Min スケッチは、統計バージョン 1 では、equal/IN 述語の選択性推定にのみ使用されます。バージョン 2 では、以下で説明するように、衝突を回避するために Count-Min スケッチを管理することが困難なため、代わりにヒストグラム統計が使用されます。
Count-Min Sketch はハッシュ構造です。1 a = 1の同値クエリやINクエリ (たとえばa IN (1, 2, 3) ) を処理する場合、TiDB はこのデータ構造を使用して推定を行います。
Count-Min Sketch はハッシュ構造であるため、ハッシュ衝突が発生する可能性があります。 EXPLAIN文で、等価クエリの推定値が実際の値と大きく異なる場合、大きい値と小さい値が一緒にハッシュされていると考えられます。 この場合、ハッシュ衝突を回避するには、次のいずれかの方法を実行できます。
WITH NUM TOPNパラメータを変更します。TiDB は高頻度 (上位 x) データを別々に保存し、その他のデータは Count-Min Sketch に保存します。したがって、大きい値と小さい値が一緒にハッシュされるのを防ぐには、WITH NUM TOPNの値を増やすことができます。TiDB では、デフォルト値は 20 です。最大値は 1024 です。このパラメータの詳細については、 手動収集参照してください。- 2 つのパラメータ
WITH NUM CMSKETCH DEPTHとWITH NUM CMSKETCH WIDTH変更します。どちらもハッシュ バケットの数と衝突確率に影響します。実際のシナリオに応じて 2 つのパラメータの値を適切に増やすと、ハッシュ衝突の確率を下げることができますが、統計のメモリ使用量が高くなります。TiDB では、デフォルト値WITH NUM CMSKETCH DEPTHは 5、デフォルト値WITH NUM CMSKETCH WIDTHは 2048 です。2 つのパラメータの詳細については、 手動収集参照してください。
トップN
上位 N 値は、列またはインデックス内で上位 N 個出現する値です。上位 N 統計は、頻度統計またはデータ スキューと呼ばれることもあります。
TiDB は、Top-N 値の値と発生回数を記録します。ここで、 N WITH NUM TOPNパラメータによって制御されます。デフォルト値は 20 で、最も頻繁に発生する上位 20 個の値が収集されることを意味します。最大値は 1024 です。パラメータの詳細については、 手動収集参照してください。
選択的統計収集
このセクションでは、統計を選択的に収集する方法について説明します。
インデックスの統計を収集する
IndexNameList in TableNameのすべてのインデックスの統計を収集するには、次の構文を使用します。
ANALYZE TABLE TableName INDEX [IndexNameList] [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH]|[WITH NUM SAMPLES|WITH FLOATNUM SAMPLERATE];
IndexNameListが空の場合、この構文はTableName内のすべてのインデックスの統計を収集します。
注記：
収集前後の統計情報の一貫性を確保するために、
tidb_analyze_versionが2場合、この構文はインデックスだけでなく、テーブル全体 (すべての列とインデックスを含む) の統計を収集します。
いくつかの列の統計を収集する
ほとんどの場合、オプティマイザはWHERE 、 JOIN 、 ORDER BY 、およびGROUP BYステートメントの列の統計のみを使用します。これらの列はPREDICATE COLUMNSとして参照できます。
テーブルに多数の列がある場合、すべての列の統計を収集すると、大きなオーバーヘッドが発生する可能性があります。オーバーヘッドを削減するには、特定の列 (選択した列) のみの統計を収集するか、オプティマイザーが使用するPREDICATE COLUMNSのみの統計を収集します。将来再利用できるように列のサブセットの列リストを保持するには、 列構成の保持参照してください。
注記：
- 述語列の統計の収集は
tidb_analyze_version = 2にのみ適用されます。- TiDB v7.2.0 以降、TiDB では
tidb_analyze_skip_column_typesシステム変数も導入され、統計を収集するANALYZEコマンドを実行するときに、統計収集でスキップされる列の種類を示します。システム変数はtidb_analyze_version = 2にのみ適用されます。
特定の列の統計を収集するには、次の構文を使用します。
ANALYZE TABLE TableName COLUMNS ColumnNameList [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH]|[WITH NUM SAMPLES|WITH FLOATNUM SAMPLERATE];構文では、
ColumnNameListターゲット列の名前リストを指定します。複数の列を指定する必要がある場合は、カンマ,使用して列名を区切ります。たとえば、ANALYZE table t columns a, bです。この構文では、特定のテーブルの特定の列の統計を収集するだけでなく、インデックス付き列とそのテーブルのすべてのインデックスの統計も同時に収集します。PREDICATE COLUMNSの統計を収集するには、次の手順を実行します。TiDB が
PREDICATE COLUMNS収集できるようにするには、tidb_enable_column_trackingシステム変数の値をONに設定します。設定後、TiDB は 300 秒ごとに
PREDICATE COLUMNS情報をmysql.column_stats_usageシステム テーブルに書き込みます。ビジネスのクエリ パターンが比較的安定したら、次の構文を使用して
PREDICATE COLUMNSの統計を収集します。ANALYZE TABLE TableName PREDICATE COLUMNS [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH]|[WITH NUM SAMPLES|WITH FLOATNUM SAMPLERATE];この構文は、特定のテーブル内の
PREDICATE COLUMNSの統計を収集するだけでなく、そのテーブル内のインデックス付き列とすべてのインデックスの統計を同時に収集します。注記：
mysql.column_stats_usageシステム テーブルにそのテーブルに対して記録されたPREDICATE COLUMNS含まれていない場合、上記の構文は、そのテーブル内のすべての列とすべてのインデックスの統計を収集します。- コレクションから除外された列 (手動で列をリストするか、
PREDICATE COLUMNS使用することによって) の統計は上書きされません。新しいタイプの SQL クエリを実行すると、オプティマイザは、そのような列に古い統計が存在する場合はそれを使用し、列に統計が収集されていない場合は疑似列統計を使用します。PREDICATE COLUMNS使用する次の ANALYZE では、それらの列の統計が収集されます。
すべての列とインデックスの統計を収集するには、次の構文を使用します。
ANALYZE TABLE TableName ALL COLUMNS [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH]|[WITH NUM SAMPLES|WITH FLOATNUM SAMPLERATE];
パーティションの統計を収集する
PartitionNameListinTableNameのすべてのパーティションの統計を収集するには、次の構文を使用します。ANALYZE TABLE TableName PARTITION PartitionNameList [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH]|[WITH NUM SAMPLES|WITH FLOATNUM SAMPLERATE];PartitionNameListinTableNameのすべてのパーティションのインデックス統計を収集するには、次の構文を使用します。ANALYZE TABLE TableName PARTITION PartitionNameList INDEX [IndexNameList] [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH]|[WITH NUM SAMPLES|WITH FLOATNUM SAMPLERATE];テーブル内の一部のパーティションのいくつかの列の統計を収集するだけが必要な場合は、次の構文を使用します。
ANALYZE TABLE TableName PARTITION PartitionNameList [COLUMNS ColumnNameList|PREDICATE COLUMNS|ALL COLUMNS] [WITH NUM BUCKETS|TOPN|CMSKETCH DEPTH|CMSKETCH WIDTH]|[WITH NUM SAMPLES|WITH FLOATNUM SAMPLERATE];
動的プルーニングモードでパーティションテーブルの統計を収集する
動的剪定モードでパーティション化されたテーブルにアクセスすると (v6.3.0 以降のデフォルト)、TiDB はテーブル レベルの統計 (GlobalStats と呼ばれる) を収集します。現在、GlobalStats はすべてのパーティションの統計から集計されます。動的プルーニング モードでは、テーブルの任意のパーティションの統計の更新によって、そのテーブルの GlobalStats の更新がトリガーされることがあります。
一部のパーティションの統計が空の場合、または一部のパーティションの一部の列の統計が欠落している場合、コレクションの動作はtidb_skip_missing_partition_stats変数によって制御されます。
GlobalStats 更新がトリガーされ、
tidb_skip_missing_partition_statsがOFF場合:一部のパーティションに統計がない場合 (分析されたことのない新しいパーティションなど)、GlobalStats の生成が中断され、パーティションに統計がないことを示す警告メッセージが表示されます。
特定のパーティションに一部の列の統計情報がない場合 (これらのパーティションで分析用に異なる列が指定されている場合)、これらの列の統計情報が集計されるときに GlobalStats の生成が中断され、特定のパーティションに一部の列の統計情報が存在しないことを示す警告メッセージが表示されます。
GlobalStats 更新がトリガーされ、
tidb_skip_missing_partition_statsがON場合:- 一部のパーティションのすべての列または一部の列の統計が欠落している場合、TiDB は GlobalStats を生成するときにこれらの欠落しているパーティションの統計をスキップするため、GlobalStats の生成には影響しません。
動的プルーニング モードでは、パーティションとテーブルのANALYZE構成は同じである必要があります。したがって、 ANALYZE TABLE TableName PARTITION PartitionNameListステートメントの後にCOLUMNS構成を指定したり、 WITHの後にOPTIONS構成を指定したりしても、TiDB はそれらを無視し、警告を返します。
収集パフォーマンスの向上
注記：
- TiDB での
ANALYZE TABLEの実行時間は、MySQL や InnoDB よりも長くなる可能性があります。InnoDB では少数のページのみがサンプリングされますが、TiDB ではデフォルトで包括的な統計セットが完全に再構築されます。
TiDB は、統計収集のパフォーマンスを向上させる 2 つのオプションを提供します。
- 列のサブセットに関する統計を収集します。 いくつかの列の統計情報を収集するを参照してください。
- サンプリング。
統計サンプリング
サンプリングは、 ANALYZEステートメントの 2 つの別個のオプションを介して実行できます。それぞれが異なる収集アルゴリズムに対応しています。
WITH NUM SAMPLES、TiDB のリザーバ サンプリング メソッドで実装されるサンプリング セットのサイズを指定します。テーブルが大きい場合、このメソッドを使用して統計を収集することはお勧めしません。リザーバ サンプリングの中間結果セットには冗長な結果が含まれるため、メモリなどのリソースに余分な負荷がかかります。WITH FLOAT_NUM SAMPLERATE、v5.3.0 で導入されたサンプリング方法です。値の範囲が(0, 1]の場合、このパラメータはサンプリング レートを指定します。これは、TiDB のベルヌーイ サンプリングの方法で実装されており、より大きなテーブルのサンプリングに適しており、収集効率とリソース使用率が向上します。
v5.3.0 より前の TiDB では、統計を収集するためにリザーバ サンプリング メソッドが使用されていました。v5.3.0 以降、TiDB バージョン 2 の統計では、デフォルトでベルヌーイ サンプリング メソッドが使用されて統計が収集されます。リザーバ サンプリング メソッドを再利用するには、 WITH NUM SAMPLESステートメントを使用できます。
現在のサンプリング レートは、適応アルゴリズムに基づいて計算されます。 SHOW STATS_META使用して表の行数を観察できる場合は、この行数を使用して 100,000 行に対応するサンプリング レートを計算できます。 この数を観察できない場合は、表のSHOW TABLE REGIONSの結果のAPPROXIMATE_KEYS列のすべての値の合計を別の基準として使用して、サンプリング レートを計算できます。
注記：
通常、
STATS_METAAPPROXIMATE_KEYSよりも信頼性が高いです。ただし、STATS_METAの結果がAPPROXIMATE_KEYSの結果よりもはるかに小さい場合は、APPROXIMATE_KEYS使用してサンプリング レートを計算することをお勧めします。
統計情報を収集するためのメモリ割り当て
TiDB v6.1.0 以降では、システム変数tidb_mem_quota_analyzeを使用して、TiDB で統計を収集するためのメモリクォータを制御できます。
適切な値tidb_mem_quota_analyzeを設定するには、クラスターのデータ サイズを考慮してください。デフォルトのサンプリング レートを使用する場合、主な考慮事項は、列の数、列値のサイズ、および TiDB のメモリ構成です。最大値と最小値を構成するときは、次の提案を考慮してください。
注記：
以下の提案は参考用です。実際のシナリオに基づいて値を設定する必要があります。
- 最小値: TiDB が最も多くの列を持つテーブルから統計を収集する場合の最大メモリ使用量よりも大きくする必要があります。おおよその目安: TiDB がデフォルト設定を使用して 20 列のテーブルから統計を収集する場合、最大メモリ使用量は約 800 MiB です。TiDB がデフォルト設定を使用して 160 列のテーブルから統計を収集する場合、最大メモリ使用量は約 5 GiB です。
- 最大値: TiDB が統計を収集していない場合は、使用可能なメモリよりも小さくする必要があります。
ANALYZE構成を永続化する
v5.4.0 以降、TiDB はいくつかのANALYZEの構成の永続化をサポートしています。この機能により、将来の統計収集に既存の構成を簡単に再利用できます。
永続性をサポートするANALYZEの構成は次のとおりです。
| 構成 | 対応するANALYZE構文 |
|---|---|
| ヒストグラムバケットの数 | WITH NUM BUCKETS |
| トップNの数 | WITH NUM TOPN |
| サンプル数 | WITH NUM SAMPLES |
| サンプリングレート | WITH FLOATNUM SAMPLERATE |
ANALYZE列タイプ | AnalyzeColumnOption ::= ( 'すべての列' |
ANALYZE列目 | ColumnNameList ::= 識別子 ( ',' 識別子 )* |
ANALYZE構成の永続性を有効にする
ANALYZE構成の永続性機能は、デフォルトで無効になっています。この機能を有効にするには、システム変数tidb_persist_analyze_optionsがONであることを確認し、システム変数tidb_analyze_versionを2に設定します。
この機能を使用すると、ステートメントを手動で実行するときに、 ANALYZEステートメントで指定された永続性設定を記録できます。記録されると、次に TiDB が統計を自動的に更新するか、これらの設定を指定せずに手動で統計を収集するときに、TiDB は記録された設定に従って統計を収集します。
auto analyze操作に使用される特定のテーブルに保存されている構成を照会するには、次の SQL ステートメントを使用できます。
SELECT sample_num, sample_rate, buckets, topn, column_choice, column_ids FROM mysql.analyze_options opt JOIN information_schema.tables tbl ON opt.table_id = tbl.tidb_table_id WHERE tbl.table_schema = '{db_name}' AND tbl.table_name = '{table_name}';
TiDB は、最新のANALYZEステートメントで指定された新しい構成を使用して、以前に記録された永続構成を上書きします。たとえば、 ANALYZE TABLE t WITH 200 TOPN;実行すると、 ANALYZEステートメントの上位 200 個の値が設定されます。続いてANALYZE TABLE t WITH 0.1 SAMPLERATE;実行すると、 ANALYZE TABLE t WITH 200 TOPN, 0.1 SAMPLERATE;と同様に、上位 200 個の値と auto ANALYZEステートメントのサンプリング レート 0.1 の両方が設定されます。
ANALYZE構成の永続性を無効にする
ANALYZE構成の永続性機能を無効にするには、 tidb_persist_analyze_optionsシステム変数をOFFに設定します。 ANALYZE構成の永続性機能はtidb_analyze_version = 1には適用されないため、 tidb_analyze_version = 1設定することでもこの機能を無効にすることができます。
ANALYZE構成の永続性機能を無効にした後、TiDB は永続化された構成レコードをクリアしません。したがって、この機能を再度有効にすると、TiDB は以前に記録された永続的な構成を使用して統計を収集し続けます。
注記：
ANALYZE構成の永続性機能を再度有効にしたときに、以前に記録した永続性構成が最新のデータに適用できなくなった場合は、ANALYZEステートメントを手動で実行し、新しい永続性構成を指定する必要があります。
列構成の保持
ANALYZEステートメントの列構成 ( COLUMNS ColumnNameList 、 PREDICATE COLUMNS 、 ALL COLUMNSを含む) を永続化する場合は、 tidb_persist_analyze_optionsシステム変数の値をONに設定して構成の永続性を分析する機能を有効にします。ANALYZE 構成永続化機能を有効にした後、次の操作を実行します。
- TiDB が自動的に統計を収集する場合、または列構成を指定せずに
ANALYZEステートメントを実行して手動で統計を収集する場合、TiDB は統計収集に以前に保持された構成を引き続き使用します。 - 列構成を指定して
ANALYZEステートメントを手動で複数回実行すると、TiDB は最新のANALYZEステートメントで指定された新しい構成を使用して、以前に記録された永続的な構成を上書きします。
統計が収集されたPREDICATE COLUMNSと列を検索するには、 SHOW COLUMN_STATS_USAGEステートメントを使用します。
次の例では、 ANALYZE TABLE t PREDICATE COLUMNS;を実行した後、 TiDB は列b 、 c 、およびdの統計を収集します。ここで、列bはPREDICATE COLUMNであり、列cとdはインデックス列です。
SET GLOBAL tidb_enable_column_tracking = ON;
Query OK, 0 rows affected (0.00 sec)
CREATE TABLE t (a INT, b INT, c INT, d INT, INDEX idx_c_d(c, d));
Query OK, 0 rows affected (0.00 sec)
-- The optimizer uses the statistics on column b in this query.
SELECT * FROM t WHERE b > 1;
Empty set (0.00 sec)
-- After waiting for a period of time (100 * stats-lease), TiDB writes the collected `PREDICATE COLUMNS` to mysql.column_stats_usage.
-- Specify `last_used_at IS NOT NULL` to show the `PREDICATE COLUMNS` collected by TiDB.
SHOW COLUMN_STATS_USAGE
WHERE db_name = 'test' AND table_name = 't' AND last_used_at IS NOT NULL;
+---------+------------+----------------+-------------+---------------------+------------------+
| Db_name | Table_name | Partition_name | Column_name | Last_used_at | Last_analyzed_at |
+---------+------------+----------------+-------------+---------------------+------------------+
| test | t | | b | 2022-01-05 17:21:33 | NULL |
+---------+------------+----------------+-------------+---------------------+------------------+
1 row in set (0.00 sec)
ANALYZE TABLE t PREDICATE COLUMNS;
Query OK, 0 rows affected, 1 warning (0.03 sec)
-- Specify `last_analyzed_at IS NOT NULL` to show the columns for which statistics have been collected.
SHOW COLUMN_STATS_USAGE
WHERE db_name = 'test' AND table_name = 't' AND last_analyzed_at IS NOT NULL;
+---------+------------+----------------+-------------+---------------------+---------------------+
| Db_name | Table_name | Partition_name | Column_name | Last_used_at | Last_analyzed_at |
+---------+------------+----------------+-------------+---------------------+---------------------+
| test | t | | b | 2022-01-05 17:21:33 | 2022-01-05 17:23:06 |
| test | t | | c | NULL | 2022-01-05 17:23:06 |
| test | t | | d | NULL | 2022-01-05 17:23:06 |
+---------+------------+----------------+-------------+---------------------+---------------------+
3 rows in set (0.00 sec)
統計のバージョン
tidb_analyze_version変数は、TiDB によって収集される統計を制御します。現在、 tidb_analyze_version = 1とtidb_analyze_version = 2 2 つのバージョンの統計がサポートされています。
- TiDB Self-Managed の場合、この変数のデフォルト値は、v5.3.0 以降で
1から2に変更されます。 - TiDB Cloudの場合、この変数のデフォルト値は、v6.5.0 以降で
1から2に変更されます。 - クラスターが以前のバージョンからアップグレードされた場合、アップグレード後もデフォルト値
tidb_analyze_versionは変更されません。
バージョン 2 が推奨されており、最終的にはバージョン 1 を完全に置き換えるために引き続き機能強化される予定です。バージョン 1 と比較すると、バージョン 2 では、より大規模なデータ ボリュームに対して収集される多くの統計の精度が向上しています。また、バージョン 2 では、述語選択性の推定のために Count-Min スケッチ統計を収集する必要がなくなり、選択した列のみの自動収集もサポートされるようになったため、収集パフォーマンスが向上しています ( いくつかの列の統計情報を収集する参照)。
次の表は、オプティマイザーの推定で使用するために各バージョンで収集される情報を示しています。
| 情報 | バージョン 1 | バージョン2 |
|---|---|---|
| 表内の行の総数 | ⎷ | ⎷ |
| 等号/IN述語推定 | ⎷ (カラム/ インデックス トップ N と カウント 最小 スケッチ) | ⎷ (カラム/ インデックス トップ N と ヒストグラム) |
| 範囲述語推定 | ⎷ (カラム/ インデックス トップ N と ヒストグラム) | ⎷ (カラム/ インデックス トップ N と ヒストグラム) |
NULL述語推定 | ⎷ | ⎷ |
| 列の平均長さ | ⎷ | ⎷ |
| インデックスの平均長 | ⎷ | ⎷ |
統計バージョンを切り替える
すべてのテーブル/インデックス (およびパーティション) が同じバージョンの統計収集を利用するようにすることをお勧めします。バージョン 2 が推奨されますが、使用中のバージョンで問題が発生したなどの正当な理由がない限り、あるバージョンから別のバージョンに切り替えることはお勧めしません。バージョン間の切り替えでは、すべてのテーブルが新しいバージョンで分析されるまで統計が利用できない期間が発生する可能性があり、統計が利用できない場合はオプティマイザー プランの選択に悪影響を与える可能性があります。
切り替えの正当な理由の例としては、バージョン 1 では、Count-Min スケッチ統計を収集するときにハッシュ衝突が原因で、equal/IN 述語の推定が不正確になる可能性があることが挙げられます。解決策はカウントミニマムスケッチセクションに記載されています。または、 tidb_analyze_version = 2設定してすべてのオブジェクトでANALYZEを再実行することも解決策の 1 つです。バージョン 2 の初期リリースでは、 ANALYZEの後にメモリオーバーフローが発生するリスクがありました。この問題は解決されていますが、当初は、 tidb_analyze_version = 1設定してすべてのオブジェクトでANALYZE再実行するという解決策がありました。
バージョン間のANALYZEを準備するには:
ANALYZEステートメントを手動で実行する場合は、分析対象のすべてのテーブルを手動で分析します。SELECT DISTINCT(CONCAT('ANALYZE TABLE ', table_schema, '.', table_name, ';')) FROM information_schema.tables JOIN mysql.stats_histograms ON table_id = tidb_table_id WHERE stats_ver = 2;自動分析が有効になっているため、TiDB が
ANALYZEステートメントを自動的に実行する場合は、DROP STATSのステートメントを生成する次のステートメントを実行します。SELECT DISTINCT(CONCAT('DROP STATS ', table_schema, '.', table_name, ';')) FROM information_schema.tables ON mysql.stats_histograms ON table_id = tidb_table_id WHERE stats_ver = 2;前のステートメントの結果が長すぎてコピーして貼り付けることができない場合は、結果を一時テキスト ファイルにエクスポートし、次のようにファイルから実行することができます。
SELECT DISTINCT ... INTO OUTFILE '/tmp/sql.txt'; mysql -h ${TiDB_IP} -u user -P ${TIDB_PORT} ... < '/tmp/sql.txt'
統計情報をビュー
次のステートメントを使用して、 ANALYZEステータスと統計情報を表示できます。
ANALYZE状態
ANALYZEステートメントを実行すると、 SHOW ANALYZE STATUS使用してANALYZEの現在の状態を表示できます。
TiDB v6.1.0 以降では、 SHOW ANALYZE STATUSステートメントはクラスター レベルのタスクの表示をサポートします。TiDB を再起動した後でも、このステートメントを使用して再起動前のタスク レコードを表示できます。TiDB v6.1.0 より前では、 SHOW ANALYZE STATUSステートメントはインスタンス レベルのタスクのみを表示でき、タスク レコードは TiDB の再起動後にクリアされます。
SHOW ANALYZE STATUS最新のタスク レコードのみを表示します。TiDB v6.1.0 以降では、システム テーブルmysql.analyze_jobsを通じて過去 7 日間の履歴タスクを表示できます。
tidb_mem_quota_analyzeが設定され、TiDB のバックグラウンドで実行されている自動ANALYZEタスクがこのしきい値を超えるメモリを使用すると、タスクは再試行されます。 SHOW ANALYZE STATUSステートメントの出力で、失敗したタスクと再試行されたタスクを確認できます。
tidb_max_auto_analyze_timeが 0 より大きく、TiDB のバックグラウンドで実行されている自動ANALYZEタスクにこのしきい値を超える時間がかかる場合、タスクは終了します。
mysql> SHOW ANALYZE STATUS [ShowLikeOrWhere];
+--------------+------------+----------------+-------------------------------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------------------------------------------------------------------------|
| Table_schema | Table_name | Partition_name | Job_info | Processed_rows | Start_time | End_time | State | Fail_reason |
+--------------+------------+----------------+-------------------------------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------------------------------------------------------------------------|
| test | sbtest1 | | retry auto analyze table all columns with 100 topn, 0.055 samplerate | 2000000 | 2022-05-07 16:41:09 | 2022-05-07 16:41:20 | finished | NULL |
| test | sbtest1 | | auto analyze table all columns with 100 topn, 0.5 samplerate | 0 | 2022-05-07 16:40:50 | 2022-05-07 16:41:09 | failed | analyze panic due to memory quota exceeds, please try with smaller samplerate |
テーブルのメタデータ
SHOW STATS_METAステートメントを使用すると、行の合計数と更新された行の数を表示できます。
テーブルの健全性状態
SHOW STATS_HEALTHYステートメントを使用すると、テーブルの正常性状態を確認し、統計の精度を大まかに見積もることができます。 modify_count >= row_countの場合、正常性状態は 0 です。 modify_count < row_countの場合、正常性状態は (1 - modify_count / row_count ) * 100 です。
列のメタデータ
SHOW STATS_HISTOGRAMSステートメントを使用すると、すべての列の異なる値の数とNULLの数を表示できます。
ヒストグラムのバケット
SHOW STATS_BUCKETSステートメントを使用して、ヒストグラムの各バケットを表示できます。
トップN情報
SHOW STATS_TOPNステートメントを使用すると、TiDB によって現在収集されている Top-N 情報を表示できます。
統計情報を削除
統計を削除するには、 DROP STATSステートメントを実行します。
負荷統計
注記：
TiDB Cloudサーバーレスクラスターでは読み込み統計は利用できません。
デフォルトでは、列統計のサイズに応じて、TiDB は次のように異なる方法で統計をロードします。
- メモリを少量しか消費しない統計 (count、distinctCount、nullCount など) の場合、列データが更新されている限り、TiDB は対応する統計をメモリに自動的にロードし、SQL 最適化ステージで使用します。
- 大量のメモリを消費する統計 (ヒストグラム、TopN、Count-Min Sketch など) の場合、SQL 実行のパフォーマンスを確保するために、TiDB はオンデマンドで統計を非同期的にロードします。ヒストグラムを例に挙げると、TiDB は、オプティマイザがその列のヒストグラム統計を使用する場合にのみ、その列のヒストグラム統計をメモリにロードします。オンデマンドの非同期統計のロードは、SQL 実行のパフォーマンスには影響しませんが、SQL 最適化に不完全な統計を提供する可能性があります。
v5.4.0 以降、TiDB では統計の同期ロード機能が導入されています。この機能により、SQL ステートメントを実行するときに、TiDB は大規模な統計 (ヒストグラム、TopN、Count-Min Sketch 統計など) をメモリにロードできるようになり、SQL 最適化の統計の完全性が向上します。
この機能を有効にするには、 tidb_stats_load_sync_waitシステム変数の値を、SQL 最適化が完全な列統計を同期的にロードするまで待機できる最大タイムアウト (ミリ秒単位) に設定します。この変数のデフォルト値は100で、この機能が有効であることを示します。
統計の同期ロード機能を有効にした後、 tidb_stats_load_pseudo_timeoutシステム変数の値を変更することで、SQL 最適化の待機時間がタイムアウトに達したときの TiDB の動作を制御できます。この変数のデフォルト値はONで、タイムアウト後、SQL 最適化プロセスはどの列でもヒストグラム、TopN、または CMSketch 統計を使用しないことを示します。この変数がOFFに設定されている場合、タイムアウト後、SQL 実行は失敗します。
輸出入統計
このセクションでは、統計をエクスポートおよびインポートする方法について説明します。
注記：
このセクションはTiDB Cloudには適用されません。
輸出統計
統計をエクスポートするためのインターフェースは次のとおりです。
${db_name}データベース内の${table_name}のテーブルの JSON 形式の統計を取得するには:http://${tidb-server-ip}:${tidb-server-status-port}/stats/dump/${db_name}/${table_name}例えば：
curl -s http://127.0.0.1:10080/stats/dump/test/t1 -o /tmp/t1.json特定の時間における
${db_name}のデータベース内の${table_name}番目のテーブルの JSON 形式の統計を取得するには:http://${tidb-server-ip}:${tidb-server-status-port}/stats/dump/${db_name}/${table_name}/${yyyyMMddHHmmss}
輸入統計
注記：
MySQL クライアントを起動するときは、
--local-infile=1オプションを使用します。
通常、インポートされた統計は、エクスポート インターフェイスを使用して取得された JSON ファイルを参照します。
統計の読み込みはLOAD STATSステートメントで実行できます。
例えば：
LOAD STATS 'file_name';
file_nameインポートする統計のファイル名です。
ロック統計
v6.5.0 以降、TiDB は統計のロックをサポートしています。テーブルまたはパーティションの統計がロックされると、テーブルの統計は変更できなくなり、テーブルに対してANALYZEステートメントを実行することもできなくなります。例:
テーブルtを作成し、そこにデータを挿入します。テーブルtの統計がロックされていない場合、 ANALYZEステートメントは正常に実行できます。
mysql> CREATE TABLE t(a INT, b INT);
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO t VALUES (1,2), (3,4), (5,6), (7,8);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE t;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t, reason to use this rate is "Row count in stats_meta is much smaller compared with the row count got by PD, use min(1, 15000/4) as the sample-rate=1" |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
テーブルtの統計をロックし、 ANALYZE実行します。警告メッセージは、 ANALYZEステートメントがテーブルtをスキップしたことを示します。
mysql> LOCK STATS t;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW STATS_LOCKED;
+---------+------------+----------------+--------+
| Db_name | Table_name | Partition_name | Status |
+---------+------------+----------------+--------+
| test | t | | locked |
+---------+------------+----------------+--------+
1 row in set (0.01 sec)
mysql> ANALYZE TABLE t;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t, reason to use this rate is "use min(1, 110000/8) as the sample-rate=1" |
| Warning | 1105 | skip analyze locked table: test.t |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
表tおよびANALYZEの統計のロックを解除すると、再度正常に実行できます。
mysql> UNLOCK STATS t;
Query OK, 0 rows affected (0.01 sec)
mysql> ANALYZE TABLE t;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t, reason to use this rate is "use min(1, 110000/8) as the sample-rate=1" |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
さらに、 LOCK STATS使用してパーティションの統計をロックすることもできます。例:
パーティション テーブルtを作成し、そこにデータを挿入します。パーティションp1の統計がロックされていない場合、 ANALYZEステートメントは正常に実行できます。
mysql> CREATE TABLE t(a INT, b INT) PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (30));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO t VALUES (1,2), (3,4), (5,6), (7,8);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE t;
Query OK, 0 rows affected, 6 warning (0.02 sec)
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | disable dynamic pruning due to t has no global stats |
| Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t's partition p0, reason to use this rate is "Row count in stats_meta is much smaller compared with the row count got by PD, use min(1, 15000/4) as the sample-rate=1" |
| Warning | 1105 | disable dynamic pruning due to t has no global stats |
| Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t's partition p1, reason to use this rate is "TiDB assumes that the table is empty, use sample-rate=1" |
| Warning | 1105 | disable dynamic pruning due to t has no global stats |
| Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t's partition p2, reason to use this rate is "TiDB assumes that the table is empty, use sample-rate=1" |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)
パーティションp1の統計をロックし、 ANALYZE実行します。警告メッセージは、 ANALYZEステートメントがパーティションp1をスキップしたことを示します。
mysql> LOCK STATS t PARTITION p1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW STATS_LOCKED;
+---------+------------+----------------+--------+
| Db_name | Table_name | Partition_name | Status |
+---------+------------+----------------+--------+
| test | t | p1 | locked |
+---------+------------+----------------+--------+
1 row in set (0.00 sec)
mysql> ANALYZE TABLE t PARTITION p1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t's partition p1, reason to use this rate is "TiDB assumes that the table is empty, use sample-rate=1" |
| Warning | 1105 | skip analyze locked table: test.t partition (p1) |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
パーティションp1とANALYZEの統計情報のロック解除を再度正常に実行できます。
mysql> UNLOCK STATS t PARTITION p1;
Query OK, 0 rows affected (0.00 sec)
mysql> ANALYZE TABLE t PARTITION p1;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1105 | Analyze use auto adjusted sample rate 1.000000 for table test.t's partition p1, reason to use this rate is "TiDB assumes that the table is empty, use sample-rate=1" |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
統計情報のロックの動作
- パーティションテーブルの統計をロックすると、パーティションテーブルのすべてのパーティションの統計がロックされます。
- テーブルまたはパーティションを切り捨てると、テーブルまたはパーティションの統計ロックが解除されます。
次の表は、統計のロックの動作を示しています。
| テーブル全体を削除する | テーブル全体を切り捨てる | パーティションを切り捨てる | 新しいパーティションを作成する | パーティションを削除する | パーティションを再編成する | パーティションを交換する | |
|---|---|---|---|---|---|---|---|
| パーティションテーブルがロックされている | ロックが無効です | TiDBは古いテーブルを削除するためロックは無効になり、ロック情報も削除されます。 | / | / | / | / | / |
| パーティションテーブルとテーブル全体がロックされている | ロックが無効です | TiDBは古いテーブルを削除するためロックは無効になり、ロック情報も削除されます。 | 古いパーティションのロック情報は無効であり、新しいパーティションは自動的にロックされます | 新しいパーティションは自動的にロックされます | 削除されたパーティションのロック情報はクリアされ、テーブル全体のロックは引き続き有効になります。 | 削除されたパーティションのロック情報はクリアされ、新しいパーティションは自動的にロックされます | ロック情報は交換されたテーブルに転送され、新しいパーティションは自動的にロックされます。 |
| パーティションテーブルで、一部のパーティションのみがロックされている | ロックが無効です | TiDBは古いテーブルを削除するためロックは無効になり、ロック情報も削除されます。 | TiDBは古いテーブルを削除するためロックは無効になり、ロック情報も削除されます。 | / | 削除されたパーティションのロック情報はクリアされます | 削除されたパーティションのロック情報はクリアされます | ロック情報は交換テーブルに転送される |
ANALYZEタスクと同時実行を管理する
このセクションでは、バックグラウンドANALYZEタスクを終了し、 ANALYZE同時実行を制御する方法について説明します。
バックグラウンドのANALYZEタスクを終了する
TiDB v6.0 以降、TiDB はKILLステートメントを使用してバックグラウンドで実行されているANALYZEタスクを終了することをサポートしています。バックグラウンドで実行されているANALYZEタスクが大量のリソースを消費し、アプリケーションに影響を与えていることがわかった場合は、次の手順を実行してANALYZEタスクを終了できます。
次の SQL ステートメントを実行します。
SHOW ANALYZE STATUS結果の
instance列目とprocess_id列目を確認すると、TiDB インスタンスのアドレスとバックグラウンドANALYZEタスクのタスクID取得できます。バックグラウンドで実行されている
ANALYZEタスクを終了します。ANALYZEタスクを終了するには、KILL TIDB ${id};ステートメントを実行します。ここで、${id}前の手順で取得したバックグラウンドANALYZEタスクのIDです。
KILLステートメントの詳細については、 KILL参照してください。
ANALYZE同時実行を制御する
ANALYZEステートメントを実行すると、システム変数を使用して同時実行性を調整し、システムへの影響を制御できます。
関連するシステム変数の関係を以下に示します。
tidb_build_stats_concurrency 、 tidb_build_sampling_stats_concurrency 、 tidb_analyze_partition_concurrency 、上の図に示すように、上流と下流の関係にあります。実際の合計同時実行数は、 tidb_build_stats_concurrency * ( tidb_build_sampling_stats_concurrency + tidb_analyze_partition_concurrency ) です。これらの変数を変更するときは、それぞれの値を同時に考慮する必要があります。 tidb_analyze_partition_concurrency 、 tidb_build_sampling_stats_concurrency 、 tidb_build_stats_concurrencyの順に 1 つずつ調整し、システムへの影響を観察することをお勧めします。これら 3 つの変数の値が大きいほど、システムのリソース オーバーヘッドが大きくなります。
tidb_build_stats_concurrency
ANALYZEステートメントを実行すると、タスクは複数の小さなタスクに分割されます。各タスクは、1 つの列またはインデックスの統計情報のみを処理します。3 tidb_build_stats_concurrencyを使用して、同時実行される小さなタスクの数を制御できます。デフォルト値は2です。v7.4.0 以前のバージョンでは、デフォルト値は4です。
tidb_build_sampling_stats_concurrency
通常の列を分析する場合、 tidb_build_sampling_stats_concurrency使用してサンプリング タスクの実行の同時実行を制御できます。デフォルト値は2です。
tidb_analyze_partition_concurrency
ANALYZEステートメントを実行する場合、 tidb_analyze_partition_concurrency使用して、パーティションテーブルの統計の読み取りと書き込みの同時実行を制御できます。デフォルト値は2です。v7.4.0 以前のバージョンの場合、デフォルト値は1です。
tidb_distsql_scan_concurrency
通常の列を分析する場合、 tidb_distsql_scan_concurrency変数を使用して、一度に読み取るリージョンの数を制御できます。デフォルト値は15です。値を変更するとクエリのパフォーマンスに影響することに注意してください。値を慎重に調整してください。
tidb_index_serial_scan_concurrency
インデックス列を分析する場合、 tidb_index_serial_scan_concurrency変数を使用して、一度に読み取るリージョンの数を制御できます。デフォルト値は1です。値を変更するとクエリのパフォーマンスに影響することに注意してください。値を慎重に調整してください。