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 のあらゆる場所で絶対等価演算子の使用をやめる

推薦する

ポップアップ効果を実現するにはjsを使用します

この記事の例では、ポップアップ効果を実現するためのjsの具体的なコードを参考までに共有しています。具...

MySQL 8.0 における非同期レプリケーションの 3 つの方法について簡単に説明します。

この実験では、空のデータベース、オフライン、オンラインの 3 つのモードで、1 つのマスターと 2 ...

ins タグと del タグの属性と使用法

insとdel は、HTML 4.0 で導入され、文書の作成時に作成者が共同作業できるようにし、また...

JavaScript でグレイウルフのポットビーティングゲームを実装

1. プロジェクト文書 2. ページレイアウトにHTMLとCSSを使用するHTML部分 <di...

インターフェーステストプラットフォームを構築するためのDjango+Vue+Dockerの詳細な説明

1. 冒頭の2つの単語みなさんこんにちは。私の名前はLin Zonglinです。私はテストエンジニア...

vue.config.js パッケージ最適化構成

Baiduの情報は多様すぎて目が回ります。心配しないでください。私はあなたのためにそれを体験しました...

ウェブページ内のFlash SWFファイルを変更する方法

これは多くの人が遭遇した問題だと思います。実際、Web ページから FLASH をダウンロードして修...

Web 上の要素を非表示にする方法とその利点と欠点

ソースコードの例: https://codepen.io/shadeed/pen/03caf6b36...

LinuxはRsync+Inotifyを使用してローカルとリモートのデータのリアルタイム同期を実現します。

0x0 テスト環境本社本番サーバーと支社バックアップサーバーはリモートデータバックアップが必要です...

VMware 仮想マシンに固定 IP アドレスを設定する方法 (グラフィック チュートリアル)

1. メニューバーで「編集」→「仮想ネットワーク エディター」を選択して仮想ネットワーク エディタ...

Mysql 文字列の傍受と指定された文字列内のデータの取得

序文: 正規表現のインターセプションに似た、MySql フィールドの文字列から特定の文字を抽出すると...

HTML マルチメディア アプリケーション: Web ページにフラッシュ アニメーションと音楽を挿入する

1. HTML_falshアニメーションでのマルチメディアの応用(WebページへのFlashアニメー...

MySQL 8.0.15 のインストールと設定方法のグラフィック チュートリアル (Windows 10 X64)

最近私が学んでいるのは MySQL の知識なので、MySQL をインストールすることが非常に重要です...

XHTML 入門チュートリアル: XHTML Web ページ画像アプリケーション

<br />適度に画像を追加すると、Web ページがより美しくなります。 画像タグ &l...

JavaScriptはスライダーを介してWebページの色を変更することを実装します

みなさんこんにちは、今日はウェブフロントエンドのHTMLを見ていたら、inputタグのtype属性が...