MySQL SQL ステートメント分析とクエリ最適化の詳細な説明

MySQL SQL ステートメント分析とクエリ最適化の詳細な説明

パフォーマンスの問題のあるSQL文を取得する方法

1. ユーザーからのフィードバックを通じてパフォーマンスに問題があるSQL文を取得する
2. スロークエリログを通じてパフォーマンスに問題があるSQL文を取得する
3. パフォーマンスに問題があるSQLをリアルタイムで取得する

スロークエリログを使用してパフォーマンスの問題のあるSQLステートメントを取得する

まず、遅いクエリに関連するパラメータを紹介しましょう。

1. slow_query_log はカスタムスロークエリログ設定を開始します。これは、MySQL コマンドラインから設定できます: set global slow_query_log=on
または、/etc/my.cnfファイルを変更してslow_query_log=onを追加します。

2. slow_query_log_file は、スロークエリログの保存パスとファイルを指定します。ログストレージとデータストレージは別々に保存することをお勧めします。

3. long_query_timeはスロークエリログのSQL実行時間を記録する閾値を指定する。①条件に合うSQLを全て記録する
②データ変更文 ③クエリ文を含む ④ロールバックされたSQL

知らせ:
時間はマイクロ秒単位の精度で、保存単位は秒です。デフォルト値は 10 秒です。たとえば、1 マイクロ秒の値を照会する場合は、0.001 秒に設定する必要があります。

4. log_queries_not_using_indexes インデックスを使用しないSQLをログに記録するかどうか

5. log_output はスローログクエリの保存形式を設定します(ファイルとして保存する必要がある場合は、FILE に変更してください)

低速クエリ使用ログに記録される情報

1. 最初の行はsbtestを使用してテスト情報を記録します
2. 2行目はスロークエリログの時間を記録する
3. 3行目に記録される情報は、ロックが使用された時間です。
4. 4行目に記録される情報は、返されたデータの行数です。
5. 5行目に記録される情報は、スキャンされたデータの行数です。
6. 6行目に記録される情報はタイムスタンプである
7. 7行目に記録されている情報はクエリSQL文です

パフォーマンスの問題のあるSQLを取得するために低速クエリを使用する

よく使われるスロークエリログ分析ツール (mysqldumpslow)
概要: クエリ条件以外は同一のSQL文を集計し、パラメータで指定した順序で分析結果を出力します。


遅いクエリログの例

低速クエリ関連の構成設定

分析結果を表示するためのコマンドライン実行パラメータ

]# cd /var/lib/mysql/log
]# mysqldumpslow -sr -t 10 遅いmysql

よく使われるスロークエリログ分析ツール (pt-query-digest)

ツールを使用する前に、まずツールをインストールする必要があります。すでにツールをお持ちの場合は、次のインストール手順をスキップできます。
1. Perlモジュール
]# yum install -y perl-CPAN perl-Time-HiRes perl-IO-Socket-SSL perl-DBD-mysql perl-Digest-MD5
2. srcディレクトリに切り替えてrpmパッケージをダウンロードします。
]# cd /usr/local/src
]# wget https://www.percona.com/downloads/percona-toolkit/3.0.7/binary/redhat/7/x86_64/percona-toolkit-3.0.7-1.el7.x86_64.rpm

3. ツールキットをインストールする
]# rpm -ivh percona-toolkit-3.0.7-1.el7.x86_64.rpm

スロークエリログを分析するためのコマンドを実行する

]# pt-query-digest --user=root --password=redhat --host=127.0.0.1 slow-mysql > slow.rep
分析結果は以下のとおりです。

MySQLサーバがクエリ要求を処理するプロセス全体

1. クライアントはSQLリクエストをサーバーに送信する
2. サーバーはキャッシュサーバーでSQLにヒットがあるかどうかを確認します。
3. サーバーはSQLを解析して前処理し、その後オプティマイザが対応する実行プランを実行します。
4. 実行計画に従って、ストレージエンジンAPIを呼び出してデータを照会する
5. 結果をクライアントに返す

クエリキャッシュがSQLパフォーマンスに与える影響

1. クエリ全体がクエリキャッシュ内のデータにヒットするかどうかを優先的にチェックする
2. 大文字と小文字を区別するハッシュ検索で実装

クエリキャッシュのパラメータの最適化

query_cache_typeはクエリキャッシュが使用可能かどうかを設定します
オン、オフ、デマンド

注: DEMAND は、クエリ ステートメントで SQL-CACHE と SQL_NO_CACHE のみが使用され、キャッシュが必要かどうかが制御されることを意味します。

query_cache_sizeはクエリキャッシュのメモリサイズを設定します

query_cache_limitはクエリキャッシュの利用可能なストレージの最大値を設定します。

query_cache_wlock_invalidate は、テーブルがロックされた後にキャッシュされたデータを返すかどうかを設定します (このオプションはデフォルトでは無効になっており、推奨されています)

query_cache_min_res_unitはクエリキャッシュによって割り当てられるメモリブロックの最小値を設定します。

MySQLが誤った実行計画を生成する原因

1. 不正確な統計情報
2. 実行計画のコスト見積りが実行計画の実際のコストと一致しない
3. MySQLオプティマイザは、あなたが考えるベストとは異なるベストを考えているかもしれない
4. MySQLは他の同時クエリを考慮しないので、現在のクエリデータに影響を与える可能性があります。
5. MySQLは固定ルールに基づいて実行計画を生成することがある
6. MySQLは制御できないコストを考慮しない

MySQLオプティマイザが最適化できるSQLタイプ

1. テーブルの関連付け順序を再定義します。オプティマイザは、統計情報に基づいてテーブルの関連付け順序を決定します。

2. 外部リンクを内部リンクに変換する
where 条件やデータベース テーブル構造など。

3. 同等の変換規則を使用する
(5=5かつa > 5)はa > 5と書き換えられる。

4. count()、min()、max()を最適化する
最適化されたテーブルを選択する
オプティマイザーは実行プランからテーブルを削除し、定数に置き換えました。

5. 式を定数式に変換する

6. 同等の変換規則を使用する

7. サブクエリの最適化

8. in()条件を最適化する

クエリ処理のさまざまな段階で費やされた時間を判断する方法

プロファイルの使用

プロファイリングを 1 に設定します。
クエリを実行します。
プロフィールを表示します。

クエリ N のプロファイルを表示します。

クエリの各段階にかかる時間

プロファイルを使用して、ステートメントに費やされた時間を表示します

特定のSQLクエリの最適化

1. マスタースレーブ切り替えの原理を利用して、大きなテーブルのテーブル構造を変更します。たとえば、スレーブサーバーで変更し、変更が完了したらマスタースレーブ切り替えを実行し、元のマスターで大きなテーブルを変更します。一定のリスクがあります。
2. プライマリ サーバーに新しいテーブルを作成します。テーブル構造は、大きなテーブルが変更された後のテーブル構造と同じです。次に、古いテーブルのデータを新しいテーブルに再度インポートし、古いテーブルに一連のトリガーを作成して、古いテーブルのデータを新しいテーブルに同期します。古いテーブルのすべてのデータが新しいテーブルに同期されたら、次の図に示すように、古いテーブルに排他ロックを追加し、新しいテーブルの名前を古いテーブルに変更し、名前を変更した古いテーブルを削除します。


大きなテーブルを変更するには、pt-online-schema-changeコマンドを使用します。具体的な操作は次の図に示されています。

上図のパラメータの説明

--alter SQL ステートメントが使用される
--user データベースログインユーザー
--password ログインしたユーザーのパスワード
Dは変更されたすべてのテーブルのデータベース名を指定します
t テーブルの名前
--charsetはデータベース文字列を指定します
--execute 実行

オリジナル作品、転載する場合は出典を明記してください

以下もご興味があるかもしれません:
  • MySQL で SQL クエリを最適化するための 30 の一般的な方法について簡単に説明します。
  • MySQL は SQL ステートメントの最新のレコードをクエリします (最適化)
  • MYSQL クエリの効率を向上させる 10 の SQL ステートメント最適化テクニック
  • MySQL SQL 文を最適化するための 10 のヒント
  • MySQL テーブルの読み取り、書き込み、インデックス作成、その他の操作の SQL ステートメントの効率最適化の問題を分析します。
  • MySQL SQL文を最適化するためのヒント
  • MySQL の最適化: 高品質の SQL 文を書く方法
  • MySQL を最適化するための 19 の一般的かつ効果的な方法 (推奨!)

<<:  LinuxサーバにおけるNginxとApacheの共存の実装方法の分析

>>:  JS と Nodejs におけるイベント駆動型開発についての簡単な説明

推薦する

JS ES6コーディング標準の詳細な説明

目次1. ブロックスコープ1.1. let は var を置き換える1.2. グローバル定数とスレッ...

AIX マウント NFS の書き込み効率が低い場合の解決策

NFSが提供するサービスマウント: サーバー上で /usr/sbin/rpc.mountd サーボ ...

CSS でベジェ曲線の実装を反転する方法

まずは、以前書いた CSS カルーセルアニメーション効果を見てみましょう。アニメーションの遷移をスム...

さまざまなネットワーク画像形式におけるPNGの利点の詳細な説明

BMP は、ハードウェア デバイスに依存せず、広く使用されている画像ファイル形式です。ビットマップ保...

MySQL のテーブルリターンとインデックスカバレッジの例の詳細な説明

目次インデックスタイプインデックス構造非クラスター化インデックスクエリインデックスカバー要約するイン...

Nginx レベルで基本的なユーザー認証を構成する手順を完了します。

序文アプリケーション シナリオ: おそらく、内部 Web サイトは外部ユーザーにアクセス可能である必...

MySQL データベースの集計クエリと結合クエリ操作

目次1. クエリ結果を挿入する2. 集計クエリ2.1 はじめに2.2 集計関数2.3 group b...

webpack イメージを base64 に変換する例

url-loader をダウンロード 糸を追加 -D URLローダー モジュール: { ルール: {...

JD.com フラッシュセール効果を実現する JavaScript

この記事では、JD.comのフラッシュセール効果を実現するためのJavaScriptの具体的なコード...

Vueベースのビデオプレーヤーの実装例

既存のビデオ プレーヤーがニーズを満たせない場合は、ビデオを自分でカプセル化する必要があります。ビデ...

オブジェクト内のフィールドを削除する js メソッド

この記事では主に、オブジェクト内のフィールドを削除するための js の実装を紹介し、次のように共有し...

mysql は sql ファイルを実行し、エラーを報告します エラー: 不明なストレージ エンジン 'InnoDB' ソリューション

問題を見つける最近、仕事で問題が発生しました。InnoDB タイプの SQL ファイルを実行すると、...

エコー後に要素編集フォームel-radioが選択できない問題を解決します

目次序文質問オンラインソリューション序文この記事の内容は私がこの業界に入ったときのメモを元にしている...

docker を使用して influxdb と mongo をデプロイするための一般的なコマンド

Docker ベースのデータベースをデプロイするsudo docker pull influxdb ...

Chromeブラウザの自動パスワード保存プロンプト機能を無効にする方法

注: Web 開発では、フォームに autocomplete="off" を追加...