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 実行

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