MySQL 統計の詳細な概要

php中世界最好的语言オリジナル: 2018-03-05 16:12:542505ブラウズ

この記事では、統計情報の概念と MYSQL 統計情報の利点の紹介を通じて、MySQL 統計情報に関連する知識ポイントの包括的な概要を説明します。これが、困っている友人に役立つことを願っています。

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

MySQL 統計ストレージは、非永続統計と永続統計の 2 つのタイプに分類されます。

1. 非永続的な統計情報

非永続的な統計情報は、データベースを再起動すると、統計情報が失われます。非永続統計を設定するには 2 つの方法があります:

1 グローバル変数、

INNODB_STATS_PERSISTENT=OFF

2 CREATE/ALTER テーブルパラメータ、

STATS_PERSISTENT=0

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

1 ANALYZE TABLE を実行

2 innodb_stats_on_metadata=ON の場合、SHOW TABLE STATUS、SHOW INDEX、クエリ INFORMATION _SCHEMAUnder TABLES、

3 --auto-rehash 関数を有効にして、mysql クライアントを使用してログインします

4 テーブルが初めて開かれます

5 統計の最後の更新以降情報、テーブル 1/16 のデータが変更されました

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

2. 永続統計

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

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

1 INNODB_STATS_AUTO_RECALC=ON

テーブル内のデータの 10% が変更された場合

2 新しいインデックスを追加する

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

データベースname table_namelast_updaten_row sclustered_index_sizesum_of_other_index_sizesinnodb_index_statsdatabase_name


テーブル名
統計の最終更新時刻
テーブルの行数
クラスター化インデックスのページ数
他のインデックスのページ数

データベース名table_nameindex_namelast_updatestat_namestat_valuesample_sizestat_description

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

CREATE TABLE t1 (
 a INT, b INT, c INT, d INT, e INT, f INT,
 PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY 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は、 Index フィールドの詳細な説明は次のとおりです。

1、n_diff_pfx01 はインデックスの最初の列の後の番号を表します。たとえば、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) 単一のテーブルでテーブルのサンプリングを指定できます:

ALTER TABLE_NAME STATS_SAMPLE_PAGES=40;

テスト後の最大値はここでの STATS_SAMPLE_PAGES の値は 65535 です。それを超えると、エラーが報告されます。

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


テーブル名
インデックス名
統計の最終更新日
統計名
統計値
サンプリングサイズ
タイプの説明

MySQL 統計の詳細な概要

1. 非永続的な統計情報

2. 永続統計

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

関連記事