MySQL スロークエリを通じて MySQL のパフォーマンスを最適化する方法

アクセス数が増えると、MySQL データベースへの負荷が増大します。MySQL アーキテクチャを使用するほぼすべての Web アプリケーションでは、データベースのパフォーマンスに問題が生じます。MySQL スロー クエリ ログを使用して問題のあるクエリを追跡すると非常に便利です。これを使用して、現在のプログラムで大量のリソースを消費する SQL ステートメントを分析できます。

my.cnf ファイルを通じてスロー クエリ ログを有効にすることができます。関連するパラメータの意味を見てみましょう。

log-slow-queries <slow_query_log_file>

遅いクエリのログを保存するファイル。 mysql サーバー プロセス mysqld_safe プロセス ユーザーがファイルに対して w 権限を持っていることを確認する必要があります。

long_query_time

クエリ時間がこの値を超えると、遅いクエリとみなされ、記録されます。単位は秒で、最小値は 1、デフォルト値は 10 秒です。ほとんどのアプリケーションにとって、10 秒は長すぎます。最初は 3 秒から始めて、徐々に短縮し、そのたびに最も「コストのかかる」 10 個の SQL ステートメントを見つけて最適化することをお勧めします。日々、一歩一歩最適化を進めます。一度に多数の SQL ステートメントを検索しても、最適化にはあまり意味がありません。

log-queries-not-using-indexes

MySQL は、インデックスを使用しないクエリをスロー クエリ ログに記録します。実行速度に関係なく、インデックスを使用しないクエリはログに記録されます。場合によっては、インデックスを使用しないクエリは非常に高速になります (小さなテーブルのスキャンなど)。ただし、サーバーの速度が低下し、大量のディスク領域が使用されることもあります。

log-slow-admin-statements

一部の管理指示も記録されます。たとえば、 OPTIMEZE TABLE 、 ALTER TABLEなどです。

低速クエリを有効にする

方法1: サーバー上のMySQL設定ファイルmy.cnfを探し、mysqldモジュールに次の内容を追加します。

log_slow_queries = いいえ
ログスロークエリ = /var/run/mysqld/slow_querys.log 
長いクエリ時間 = 3 
インデックスを使用しないログクエリ 
遅い管理ステートメントのログ

次に、MySQL サーバーを再起動します。これは、次のコマンドを使用してスロー クエリ ログを確認するためです。

tail -f /var/run/mysqld/slow_querys.log

方法 2: mysql のグローバル変数を変更します。この方法の利点は、mysql サーバーを再起動する必要がないことです。mysql にログインして、sql スクリプトを実行するだけです。

グローバル slow_query_log を ON に設定します。

グローバル long_query_time=3 を設定します。

次に、次のコマンドを実行して成功したかどうかを確認します。

mysql> 'long%' のような変数を表示します。
+-----------------+-----------+
| 変数名 | 値 |
+-----------------+-----------+
| 長いクエリ時間 | 10.000000 |
+-----------------+-----------+
セット内の 1 行 (0.00 秒)

mysql> 'slow%' のような変数を表示します。
+---------------------+---------------+
| 変数名 | 値 |
+---------------------+---------------+
| 遅い起動時間 | 2 |
| slow_query_log | オン |
| slow_query_log_file | /tmp/slow.log |
+---------------------+---------------+
セット内の 3 行 (0.00 秒)

スロークエリログの分析

方法1: たとえば、MySQLのスロークエリログ分析を表示することで、tail -f slow_query.logを実行してフィールドの内容と意味を表示できます。

# 時刻: 110107 16:22:11 
# ユーザー@ホスト: root[root] @ localhost [] 
# クエリ時間: 9.869362 ロック時間: 0.000035 送信行数: 1 検査行数: 6261774 
タイムスタンプを1294388531に設定します。 
ep_friendsからcount(*)を選択します。

• 最初の行はSQLクエリが実行された時刻です
• 2行目はSQLクエリを実行するための接続情報を示しています。
• 3行目には役に立つ情報が記録されています
• Query_time SQL 実行時間。長いほど遅くなります。
• Lock_time MySQL サーバー ステージでのテーブル ロックの待機時間 (ストレージ エンジン ステージではありません)
• Rows_sent クエリによって返された行数
• Rows_examined クエリによって検査された行の数

方法2: mysqldumpslowコマンドを使用して分析する。例:

mysqldumpslow -sc -t 10 /tmp/スローログ

これにより、最も多くのレコードを含む上位 10 個の SQL ステートメントが出力されます。

-s はソート方法を示します。c、t、l、r は、それぞれレコード数、時間、クエリ時間、返されたレコード数でソートされます。ac、at、al、ar は、対応する逆ソ​​ートを示します。-t は上位 n を意味し、返されたレコード数を意味します。-g の後には、大文字と小文字を区別しない正規表現マッチング パターンを続けることができます。

例えば

/path/mysqldumpslow -sr -t 10 /tmp/slow-log

最も多くのレコードを返す上位 10 件のクエリを取得します。

/path/mysqldumpslow -st -t 10 -g "左結合" /tmp/slow-log

時間順に並べられた左結合を含む最初の 10 個のクエリ ステートメントを取得します。

スロークエリログの欠点

遅いクエリを記録すると、製品の最適化に役立ちます。ただし、MySQL の現在のバージョンにはまだいくつかの欠点があります。

1. MySQL 5.0 では、 long_query_timeの時間粒度が十分に細かくなく、最小値は 1 秒です。同時実行パフォーマンスの高い Web スクリプトの場合、1 秒で表示されることはあまり重要ではありません。つまり、1 秒かかるクエリは比較的少ないということです。よりきめ細かい long_query_time 設定が提供されたのは、MySQL 5.1.21 になってからでした。

2. サーバーによって実行されたすべてのクエリがスローログに記録されるわけではありません。 MySQL の通常のログにはすべてのクエリが記録されますが、クエリが解析される前に記録されます。つまり、通常のログには、実行時間、テーブルロック時間、チェックされた行数などの情報を含めることはできません。

3. log_queries_not_using_indexesオプションがオンになっている場合、低速クエリ ログにジャンク ログ レコードが大量に書き込まれます。これらの高速で効率的なフル テーブル スキャン クエリ (小さなテーブル) により、本当に役立つ低速クエリ レコードが排除されます。 select * from categoryなどのクエリも記録されます。 log_queries_not_using_indexesオプションをオンにすると、低速クエリ ログにジャンク ログ レコードが大量に書き込まれます。これらの高速で効率的なフル テーブル スキャン クエリ (小さなテーブル) により、本当に役立つ低速クエリ レコードが排除されます。 select * from categoryなどのクエリも記録されます。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。