MySQL統計の概要

MySQL統計の概要

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

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

1. 非永続的な統計

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

1 グローバル変数、

INNODB_STATS_PERSISTENT=オフ

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

STATS_PERSISTENT=0

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

1 ANALYZE TABLEを実行する

2 innodb_stats_on_metadata=ONの場合、SHOW TABLE STATUS、SHOW INDEXを実行し、INFORMATION_SCHEMAの下のTABLES、STATISTICSをクエリします。

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=オン

この場合、テーブル内のデータの 10% が変更されます。

2 新しいインデックスの追加

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

innodb_table_stats

データベース名

データベース名

テーブル名

テーブル名

最終更新

統計が最後に更新された日時

n_行

表の行数

クラスター化インデックスのサイズ

クラスター化インデックス内のページ数

他のインデックスサイズの合計

他のインデックスのページ数

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 がますます強力になることを楽しみにしましょう!

以下もご興味があるかもしれません:
  • Gearman + MySQL による永続化操作例
  • Docker を使用した MySQL のデプロイの詳細説明 (データ永続化)
  • MySQL での Java 絵文字の永続化の詳細な説明
  • MySQL 8 の新機能: 永続的なグローバル変数を変更する方法
  • MySQL 8 の新機能: 自動増分主キーの永続性に関する詳細な説明
  • MySQL 8.0 の統計が不正確である理由
  • MySQL 永続統計の詳細な説明

<<:  nginx で SSL 証明書を設定して https サービスを実装する方法

>>:  JS のあらゆる場所で絶対等価演算子の使用をやめる

推薦する

Vue3のサンドボックスの仕組みの詳しい説明

目次序文ブラウザコンパイル版ローカルプリコンパイルバージョン要約する序文vue3サンドボックスには主...

Web アプリ開発時間を短縮する 10 の時間を節約するヒント (グラフィカル チュートリアル)

今日の開発環境では、速いほど良いです。 「迅速なアプリケーション開発」、「アジャイル ソフトウェア開...

Mac VMware Fusion CentOS7 静的 IP 構成チュートリアル図

目次CentOS7をインストールする静的IPの設定viを使用してファイルを編集するCentOS7をイ...

React スキャフォールディングの構築方法を学ぶ

1. フロントエンドエンジニアリングの複雑さいくつかの小さなデモ プログラムを開発するだけであれば、...

Docker Swarmを使用してWordPressを構築する方法

原因かつて私は Vultr に WordPress を設定しましたが、よく知られている理由により、こ...

Alibaba Cloud ECS クラウド サーバー (Linux システム) は、MySQL をインストールした後にリモートで接続できません (落とし穴)

昨日、1年間使用していた Alibaba Cloud サーバーを購入しました。システムは Linux...

MySQL は対応するクライアント プロセスにどのように接続しますか?

質問特定の MySQL 接続について、それがどのクライアント プロセスからのものであるかをどのように...

Git サーバーを使用してデバッグ ブランチを表示し、修正する方法を 1 日 1 分で学習します。

デバッグブランチプロジェクトの通常の開発中に、以前にリリースされたバージョンにバグがある場合がありま...

Linux で Jenkins プロジェクトを構築するプロセス (CentOS 7 を例に)

https://gitee.com/tengge1/ShadowEditor のデプロイメントを例...

HTML 5 ワーキングドラフトの謎を解く

World Wide Web Consortium (W3C) は、HTML 5 仕様のドラフトをリ...

Vueルーティングナビゲーションガードの簡単な理解

目次1. グローバルガード1. グローバル前線警備2. グローバル解像度ガード3. グローバルポスト...

JavaScript におけるシリアル操作と並列操作

目次1. はじめに2. es5メソッド3. 非同期関数のシリアル実行4. 非同期関数の並列実行5. ...

Linux環境でIPV6接続をサポートするようにmysql5.6を設定する方法

導入:この記事では主に、Linux システムで IPV6 接続をサポートするように MySQL を構...

テーブルパーティションとパーティション分割とは何ですか?MySqlデータベースパーティションとテーブルパーティション分割方法

1. テーブルとパーティションを分割する必要があるのはなぜですか?日常の開発では、大きなテーブルに遭...

MySQL 5.7.18 MSI インストール グラフィック チュートリアル

この記事では、参考までにMySQL 5.7.18 MSIインストールチュートリアルを紹介します。具体...