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 におけるイベント駆動型開発についての簡単な説明

推薦する

MySQL 30軍事ルールの詳細な説明

1. 基本仕様(1)InnoDBストレージエンジンを使用する必要があります。解釈:トランザクション、...

メニューのホバー効果を実現するCSS3

結果: html <nav id="nav-1"> <a cl...

MySQL をベースにしたシンプルな検索エンジンを実装する

目次MySQL ベースの検索エンジンの実装1. ngram全文パーサー2. 全文インデックスを作成す...

Linux でファイルのユーザーとグループを変更する方法

Linux では、ファイルが作成されると、そのファイルの所有者はファイルを作成したユーザーになります...

MySQLのスローログの開き方と保存形式の詳細な分析

開発プロジェクトでは、MySQL のスロークエリログを通じて効率の問題のある SQL を監視できます...

JS クロスドメイン ソリューション React 構成 リバース プロキシ

クロスドメインソリューションjsonp (get をシミュレート) CORS (クロスオリジンリソー...

Linuxの読み取りコマンドの使用

1. コマンドの紹介read コマンドは、シェルの組み込みコマンドで、標準入力または -u オプショ...

Vueでブラウザ共有機能を呼び出す方法

序文Vue(発音は /vjuː/、view に似ています)は、ユーザーインターフェイスを構築するため...

Tomcatのデフォルトパスの設定によって発生するプロジェクトURLの競合を解決する方法の詳細な説明

序文Tomcat は優れた Java コンテナですが、避けられない小さな落とし穴もいくつかあります。...

MySQLインデックスの基本構文

インデックスはソートされたデータ構造です。 where 条件での検索や order by 条件での並...

js配列のfind、some、filter、reduceの違いの詳細な説明

Array の filter、find、some、reduce メソッドの違いを区別し、使用シナリオ...

HTML シンボルからエンティティへのアルゴリズムのチャレンジ

チャレンジ:文字列内の文字 &、<、>、" (二重引用符)、および &...

node.jsのインストールとHbuilderXの設定の詳細な説明

npm インストールチュートリアル: 1. Node.jsインストールパッケージをダウンロードする公...

Linux オペレーティング システムに Apache サービスをインストールする方法

ダウンロードリンク:動作環境VMware 仮想マシンの CentOS 7.6セキュアCRT Xftp...

MySQL における単一テーブルと複数テーブル、およびビューと一時テーブルに対する Update と Select の違い

1. テーブルAのデータを使用してMySQLのテーブルBの内容を更新するたとえば、データ テーブル内...