MySQLのインデックス統計更新時の概要についてまとめました。
※目次をクリックすると目次の下部にコンテンツが表示されます。
MySQLのbオプティマイザーとインデックス統計の概要
・オプティマイザはマッチする行を見積もるのにインデックス統計を使用する。
・MySQLのオプティマイザはコストベースで、メインのコストメトリックは、そのクエリーがアクセスするデータの数。
・インデックス統計が生成されていなかったり古くなっているとオプティマイザが最適なプランを検索できなくなる。
・ANALYZE TABLE文を手動で実行する事によってインデックス統計を生成できる。
・show index文の”Cardinality”で現状の見積り値を確認できる。
※INFORMATION_SCHEMA.STATISTICSテーブルでも確認できる。
・MySQLのオプティマイザはコストベースで、メインのコストメトリックは、そのクエリーがアクセスするデータの数。
・インデックス統計が生成されていなかったり古くなっているとオプティマイザが最適なプランを検索できなくなる。
・ANALYZE TABLE文を手動で実行する事によってインデックス統計を生成できる。
・show index文の”Cardinality”で現状の見積り値を確認できる。
※INFORMATION_SCHEMA.STATISTICSテーブルでも確認できる。
各ストレージエンジンのインデックス統計の概要
１）MyISAM
・インデックス統計をディスク上に保存。
・ANALYZE TABLE文でフルインデックススキャンを実行。実行中はテーブル全体がロックされる。
２）InnnoDB
・ランダムにある一定数のページをサンプリングして見積り、メモリー内に保存。
・サンプルするページ数はデフォルトは8で、innodb_stats_sample_pagesシステム変数で変更できる。
●インデックス統計が計算されるタイミング
・最初にテーブルをオープンしたとき
・ANALYZE TABLE文を実行したとき
・テーブルサイズが大幅に変更された場合
・INFORMATION_SCHEMAテーブルに対するクエリ、SHOW TABLE STATUS、SHOW INDEX実行時
・MySQLコマンドラインクライアントでauto-rehashが有効になっている場合（デフォルトは有効）
●インデックス統計計算時の注意点
・データが大量にある場合やI/Oが遅い場合はパフォーマンスに影響を与える。
ロックが頻繁に発生したりサーバーの負荷が増大したりする。スタートアップ時間が遅くなることもある。
innodb_stats_on_metadataをオフにして無効にする事が出来る。
・MySQL5.6では、innodb_analyze_is_persistentオプションを設定して、MySQLリスタート後もインデックス統計を維持できるようになった。
MySQLをリスタートしてもインデックス統計を再計算せずにそのまま維持され、起動時間を短くできる。
・自動でインデックス統計を再計算させない場合は、定期的に手動でANALYZE TABLE文を実行する。
・インデックス統計をディスク上に保存。
・ANALYZE TABLE文でフルインデックススキャンを実行。実行中はテーブル全体がロックされる。
２）InnnoDB
・ランダムにある一定数のページをサンプリングして見積り、メモリー内に保存。
・サンプルするページ数はデフォルトは8で、innodb_stats_sample_pagesシステム変数で変更できる。
●インデックス統計が計算されるタイミング
・最初にテーブルをオープンしたとき
・ANALYZE TABLE文を実行したとき
・テーブルサイズが大幅に変更された場合
・INFORMATION_SCHEMAテーブルに対するクエリ、SHOW TABLE STATUS、SHOW INDEX実行時
・MySQLコマンドラインクライアントでauto-rehashが有効になっている場合（デフォルトは有効）
●インデックス統計計算時の注意点
・データが大量にある場合やI/Oが遅い場合はパフォーマンスに影響を与える。
ロックが頻繁に発生したりサーバーの負荷が増大したりする。スタートアップ時間が遅くなることもある。
innodb_stats_on_metadataをオフにして無効にする事が出来る。
・MySQL5.6では、innodb_analyze_is_persistentオプションを設定して、MySQLリスタート後もインデックス統計を維持できるようになった。
MySQLをリスタートしてもインデックス統計を再計算せずにそのまま維持され、起動時間を短くできる。
・自動でインデックス統計を再計算させない場合は、定期的に手動でANALYZE TABLE文を実行する。