8.3.8 InnoDB および MyISAM インデックス統計コレクション

ストレージエンジンはオプティマイザによって使用されるテーブルに関する統計を収集します。テーブル統計は値グループに基づきますが、ここで値グループは同じキープリフィクス値を持つ行のセットです。オプティマイザの目的で、重要な統計は平均値グループサイズです。

MySQL は平均値グループサイズを次のように使用します。

ref アクセスごとに読み取る必要がある行数を見積もるには
部分結合によって生成される行数、つまりフォームの操作によって生成される行数を見積もるため
```
(...) JOIN tbl_name ON tbl_name.key = expr
```

インデックスの平均値グループサイズが増えるほど、ルックアップあたりの平均行数が増えるため、それらの 2 つの目的でインデックスが役立たなくなります。インデックスが最適化の目的に役立つようにするには、各インデックス値でターゲットとするテーブル内の行を少なくすることがもっとも適切です。指定したインデックス値が多数の行を生成する場合、そのインデックスはあまり役に立たず、MySQL がそれを使用する可能性は少なくなります。

平均値グループサイズは、値グループの数であるテーブルカーディナリティーと関連しています。 SHOW INDEX ステートメントは、N/S に基づいて、カーディナリティー値を表示します。ここで N はテーブル内の行数で、S は平均値グループサイズです。その比率から、テーブル内の値グループの概数がわかります。

<=> 比較演算子に基づいた結合では、NULL の扱いはほかの値と異なりません。ほかのどの N に対しても N <=> N とまったく同じように、NULL <=> NULL です。

ただし、= 演算子に基づく結合では、NULL は NULL 以外の値と異なります。expr1 または expr2 (または両方) が NULL である場合、expr1 = expr2 は true になりません。これは、tbl_name.key = expr 形式の比較のための ref アクセスに影響: 比較は true にできないため、expr の現在の値が NULL の場合、MySQL はテーブルにアクセスしません。

= 比較では、テーブルにある NULL 値の数は問題になりません。最適化の目的で、関連のある値は NULL 以外の値グループの平均サイズです。ただし、MySQL では現在その平均サイズを収集したり、使用したりできません。

InnoDB および MyISAM テーブルでは、innodb_stats_method および myisam_stats_method システム変数をそれぞれ使用して、テーブル統計のコレクションに対していくらかの制御ができます。これらの変数には、3 つの可能性のある値を使用でき、次のように異なります。

変数が nulls_equal に設定されている場合、すべての NULL 値が同一として扱われます (つまり、それらすべてが単一の値グループを形成します)。
NULL 値グループサイズが、NULL 以外の値グループサイズよりはるかに大きい場合、このメソッドは平均値グループサイズを上方に歪めます。これにより、オプティマイザには、NULL 以外の値を検索する結合に対して、インデックスが実際以上に役に立たないかのように見えます。結果として、nulls_equal メソッドにより、オプティマイザに ref アクセスに対してインデックスを使用すべきときでも使用させないようにすることがあります。
変数が nulls_unequal に設定されている場合、NULL 値は同じとみなされません。代わりに、各 NULL 値はサイズ 1 の個別の値グループを形成します。
多くの NULL 値がある場合、このメソッドは平均値グループサイズを下方に歪めます。 NULL 以外の平均値グループサイズが大きい場合、NULL 値をサイズ 1 のグループとしてカウントすると、オプティマイザは NULL 以外の値を検索する結合に対して、インデックスの値を多く見積もりすぎます。結果として、nulls_unequal メソッドによって、ほかのメソッドの方が適している可能性がある場合に、オプティマイザに ref ルックアップに対してこのインデックスを使用させることがあります。
変数が nulls_ignored に設定されている場合、NULL 値は無視されます。

= より <=> を使用する多くの結合を使用する傾向がある場合、比較で NULL 値は特別ではなく、NULL は互いに等しくなります。この場合、nulls_equal は適切な統計メソッドです。

innodb_stats_method システム変数にはグローバル値があります。myisam_stats_method システム変数にはグローバル値とセッション値の両方があります。グローバル値を設定すると、対応するストレージエンジンからのテーブルの統計収集に影響します。セッション値を設定すると、現在のクライアント接続のみに対する統計収集に影響します。つまり、myisam_stats_method のセッション値を設定することで、他のクライアントに影響を与えることなく、特定の方法でテーブル統計を強制的に再生成できます。

MyISAM テーブルの統計を再生成するには、次のいずれかの方法を使用できます:

myisamchk --stats_method=method_name --analyze を実行します
テーブルを変更して、統計を古くさせ (たとえば、行を挿入してから削除します)、次に myisam_stats_method を設定して、ANALYZE TABLE ステートメントを発行します。

innodb_stats_method と myisam_stats_method の使用に関するいくつかの警告は次のとおりです。

先述したように、テーブル統計を明示的に収集させることができます。ただし、MySQL は統計を自動的に収集することもあります。たとえば、テーブルへのステートメントの実行の途中で、そうしたステートメントの中にはテーブルを変更するものもあり、MySQL は統計を収集する場合があります。 (たとえば、これは一括挿入や削除、または一部の ALTER TABLE ステートメントで行われることがあります。) これが行われた場合、その時点での innodb_stats_method または myisam_stats_method の値を使用して、統計が収集されます。したがって、一方の方法を使用して統計を収集し、後でテーブル統計が自動的に収集されるときにシステム変数がもう一方の方法に設定されている場合は、もう一方の方法が使用されます。
特定のテーブルの統計の生成に使用されたメソッドを伝える方法はありません。
これらの変数は InnoDB および MyISAM テーブルにのみ適用されます。ほかのストレージエンジンはテーブル統計を収集するメソッドが 1 つしかありません。通常、それは nulls_equal メソッドに近いものになります。