このセクションでは、非永続オプティマイザ統計を構成する方法について説明します。 オプティマイザ統計は、innodb_stats_persistent=OFF の場合、または個々のテーブルが STATS_PERSISTENT=0 で作成または変更された場合、ディスクに永続化されません。 かわりに、統計はメモリーに格納され、サーバーの停止時に失われます。 統計は、特定の操作および特定の条件下で定期的に更新されます。
オプティマイザ統計はデフォルトでディスクに永続化され、innodb_stats_persistent 構成オプションによって有効化されます。 永続的オプティマイザ統計については、セクション15.8.10.1「永続的オプティマイザ統計のパラメータの構成」を参照してください。
オプティマイザ統計の更新
非永続オプティマイザ統計は、次の場合に更新されます:
ANALYZE TABLEの実行。-
SHOW TABLE STATUS、SHOW INDEXを実行するか、innodb_stats_on_metadataオプションを有効にしてINFORMATION_SCHEMA.TABLESまたはINFORMATION_SCHEMA.STATISTICSテーブルをクエリーします。innodb_stats_on_metadataのデフォルト設定はOFFです。innodb_stats_on_metadataを有効にすると、多数のテーブルまたはインデックスを持つスキーマのアクセス速度が低下し、InnoDBテーブルを含むクエリーの実行計画の安定性が低下する可能性があります。innodb_stats_on_metadataは、SETステートメントを使用してグローバルに構成されます。SET GLOBAL innodb_stats_on_metadata=ON注記innodb_stats_on_metadataは、オプティマイザ statistics が非永続として構成されている場合 (innodb_stats_persistentが無効な場合) にのみ適用されます。 -
デフォルトの
--auto-rehashオプションを有効にして mysql クライアントを起動します。auto-rehashオプションを使用すると、すべてのInnoDBテーブルがオープンされ、オープンしているテーブルの操作によって統計が再計算されます。mysql クライアントの起動時間を改善し、統計を更新するには、
--disable-auto-rehashオプションを使用してauto-rehashをオフにします。auto-rehash機能は、対話ユーザーのためのデータベース、テーブル、およびカラム名の自動名前補完を有効にします。 最初にテーブルが開かれます。
InnoDBは、統計が最後に更新されてから 1 / 16 のテーブルが変更されたことを検出します。
サンプルページ数の構成
MySQL クエリーオプティマイザは、インデックスの相対的な選択性に基づいて、キー分布に関する推定された統計を使用して実行計画のためのインデックスを選択します。 InnoDB でオプティマイザ統計が更新されると、テーブルの各インデックスからランダムページがサンプリングされ、インデックスの cardinality が見積もられます。 (この手法は、ランダムダイブと呼ばれます。)
統計の推定値の品質を制御する (それにより、クエリーオプティマイザへの情報を改善する) ために、パラメータ innodb_stats_transient_sample_pages を使用して、サンプリングされるページの数を変更できます。 サンプリングされるページのデフォルト数は 8 です。これは、正確な推定値を生成するには十分ではなく、クエリーオプティマイザによる不適切なインデックス選択につながる可能性があります。 この手法は、大きなテーブルや、結合で使用されるテーブルの場合に特に重要です。 このようなテーブルに対する不必要なフルテーブルスキャンが、パフォーマンスの重大な問題になる場合があります。 このようなクエリーのチューニングのヒントは、セクション8.2.1.23「全テーブルスキャンの回避」 を参照してください。innodb_stats_transient_sample_pages は、実行時に設定できるグローバルパラメータです。
innodb_stats_persistent=0 である場合は、innodb_stats_transient_sample_pages の値がすべての InnoDB テーブルおよびインデックスのインデックスサンプリングに影響を与えます。 インデックスサンプルサイズを変更する場合、次のような大きな影響がある可能性があることに注意してください:
1 や 2 などの小さな値では、カーディナリティーの不正確な推定値が生成される可能性があります。
innodb_stats_transient_sample_pages値を大きくすると、必要なディスク読み取りが増える可能性があります。 8 よりもはるかに大きい値 (100 など) を指定すると、テーブルのオープンまたはSHOW TABLE STATUSの実行にかかる時間が大幅に遅くなる可能性があります。オプティマイザが、インデックスの選択性の異なる推定値に基づいて、非常に異なるクエリー計画を選択する可能性があります。
あるシステムで innodb_stats_transient_sample_pages のどのような値が最適に機能したとしても、このオプションを設定し、その値のままにします。 過剰な I/O を必要とせずに、データベース内のすべてのテーブルに対して適度に正確な推定値を生成する値を選択してください。 統計は ANALYZE TABLE の実行時以外のさまざまな時間に自動的に再計算されるため、インデックスのサンプルサイズを増やし、ANALYZE TABLE を実行してから、サンプルサイズをふたたび減らしても意味がありません。
通常、小さいテーブルでは、大きいテーブルよりも必要なインデックスサンプルが少なくなります。 データベースに多数の大きなテーブルが含まれている場合は、ほとんどが小さなテーブルである場合より大きな innodb_stats_transient_sample_pages 値を使用することを考慮してください。