MySQL統計の概要

MySQL は、SQL 解析とクエリ最適化のプロセスを通じて SQL を実行します。パーサーは SQL をデータ構造に分解し、後続のステップに渡します。クエリ オプティマイザーは、SQL クエリを実行するための最適なソリューションを見つけ、実行プランを生成します。クエリ オプティマイザーは、データベース統計に応じて SQL の実行方法を決定します。以下では、MySQL 5.7 の innodb 統計の関連コンテンツを紹介します。

MySQL 統計のストレージには、非永続統計と永続統計の 2 種類があります。

1. 非永続的な統計

非永続的な統計はメモリに保存され、データベースを再起動すると失われます。非永続的な統計を設定するには、次の 2 つの方法があります。

非永続的な統計は、次の状況で自動的に更新されます。

非永続的な統計情報のデメリットは明らかです。データベースの再起動後に大量のテーブルが統計情報を更新し始めると、インスタンスに大きな影響を与えるため、現在は永続的な統計情報が使用されています。

2. 永続的な統計

5.6.6 以降、MySQL はデフォルトで永続統計 (INNODB_STATS_PERSISTENT=ON) を使用し、永続統計はテーブル mysql.innodb_table_stats および mysql.innodb_index_stats に保存されます。

永続的な統計は、次の状況で自動的に更新されます。

innodb_table_stats はテーブルの統計情報、innodb_index_stats はインデックスの統計情報です。各フィールドの意味は次のとおりです。

innodb_index_stats をよりよく理解するために、説明用のテスト テーブルを作成します。

テーブルt1を作成します（
 a INT、b INT、c INT、d INT、e INT、f INT、
 主キー (a, b)、キー i1 (c, d)、一意キー i2uniq (e, f)
)ENGINE=INNODB;

書き込まれたデータは次のとおりです。

t1 テーブルの統計を表示するには、stat_name フィールドと stat_value フィールドに注目します。

tat_name=sizeの場合: stat_valueはインデックスされたページの数を示します

stat_name=n_leaf_pagesの場合: stat_valueはリーフノードの数を示します

stat_name=n_diff_pfxNNの場合: stat_valueはインデックスフィールド内の一意の値の数を示します。詳細な説明は次のとおりです。

1. n_diff_pfx01 は、インデックスの最初の列の distinct の後の番号を示します。たとえば、PRIMARY の列 a には値 1 が 1 つだけあるため、index_name='PRIMARY' で stat_name='n_diff_pfx01' の場合、stat_value=1 になります。

2. n_diff_pfx02 は、インデックスの最初の 2 つの列に含まれる異なる値の数を示します。たとえば、i2uniq の e 列と f 列には 4 つの値があるため、index_name='i2uniq'、stat_name='n_diff_pfx02' の場合、stat_value=4 になります。

3. 非一意インデックスの場合、主キー インデックスは元の列の後に追加されます。たとえば、index_name='i1' および stat_name='n_diff_pfx03' の場合、主キー列 a は元のインデックス列 c および d の後に追加されます。(c、d、a) の個別の結果は 2 です。

stat_name と stat_value の具体的な意味を理解することで、SQL 実行中に適切なインデックスが使用されない理由をトラブルシューティングするのに役立ちます。たとえば、インデックス n_diff_pfxNN の stat_value が実際の値よりもはるかに小さい場合、クエリ オプティマイザーはインデックスの選択性が低いと判断し、間違ったインデックスが使用される可能性があります。

3. 不正確な統計情報への対処

実行プランを確認したところ、正しいインデックスが使用されていないことがわかりました。innodb_index_stats の統計情報に大きな差があることが原因の場合は、次の方法で対処できます。

1. 統計を手動で更新します。実行中に読み取りロックが追加されることに注意してください。

分析可能なテーブル名;

2. 更新後も統計が不正確な場合は、テーブルからサンプリングされるデータ ページを増やすことを検討してください。変更するには 2 つの方法があります。

a) グローバル変数 INNODB_STATS_PERSISTENT_SAMPLE_PAGES、デフォルト値は 20 です。

b) 単一のテーブルでそのテーブルのサンプリングを指定できます。

テーブル TABLE_NAME STATS_SAMPLE_PAGES=40 を変更します。

テストの結果、STATS_SAMPLE_PAGES の最大値は 65535 になりました。この値を超えるとエラーが報告されます。

現在、MySQL ではヒストグラム機能が提供されていません。場合によっては (データの分布が不均一な場合など)、統計情報を更新するだけでは正確な実行プランが得られないことがあります。唯一の方法は、インデックスヒントを通じてインデックスを指定することです。新しいバージョン 8.0 ではヒストグラム機能が追加されます。MySQL がますます強力になることを楽しみにしましょう!