Mysql の遅いクエリ操作の概要

MySQL の遅いクエリの説明

MySQL スロークエリログは、MySQL が提供するログレコードの一種です。これは、応答時間がしきい値を超えた MySQL のステートメントを記録するために使用されます。具体的には、実行時間が long_query_time 値を超えた SQL ステートメントがスロークエリログに記録されます。 long_query_time のデフォルト値は 10 です。これは、ステートメントが 10 秒以上実行されることを意味します。デフォルトでは、MySQL データベースはスロークエリログを開始しないため、このパラメータを手動で設定する必要があります。もちろん、チューニングに必要でない場合は、スロークエリログをオンにすると、多かれ少なかれパフォーマンスに影響が出るため、このパラメータを開始することは一般的に推奨されません。スロークエリログは、ファイルおよびデータベーステーブルへのログレコードの書き込みをサポートします。スロークエリログは、管理者が問題を分析する際に役立つスロークエリステートメントを記録するために使用されます。ログはデフォルトでは有効になっていないため、構成ファイルに一連のパラメータを追加して手動で有効にする必要があります。

MySQLスロークエリを有効にする理由

データベースはボトルネックが発生しやすい場所です。Nosql は現在非常に熱く議論されており、誰もがデータベースに不満を抱いていると思います。 MySQL の速度に最も大きな影響を与えるステートメントは、クエリが非常に遅いステートメントです。これらの遅いステートメントは、不適切に記述されているか、大規模なデータの下で複数のテーブルを結合したクエリである可能性があります。したがって、これらのステートメントを見つけて、理由を分析し、最適化する必要があります。これがこのブログ記事を投稿する理由でもあります

MySQL スロークエリを有効にする方法

1) 方法1: MySQLデータベースターミナルにログインして開く

mysql> show variables like "%long%"; <SPAN style="COLOR: #ff00ff"> // デフォルトのスロークエリ時間 10 秒をチェックします</SPAN> 
+-----------------+-----------+  
| 変数名 | 値 |  
+-----------------+-----------+  
| 長いクエリ時間 | 10.000000 |  
+-----------------+-----------+  
セット内の 1 行 (0.00 秒)  
  
mysql> set global long_query_time=1; <SPAN style="COLOR: #ff00ff">// 1 秒に設定し、グローバルを追加すると、次回 mysql に入ったときに有効になります</SPAN>  
クエリは正常、影響を受けた行は 0 行 (0.00 秒)  
 
mysql> show variables like "%slow%"; <SPAN style="COLOR: #ff00ff"> // スロークエリが有効になっているかどうかを確認します</SPAN> 
+---------------------+---------------------------------+  
| 変数名 | 値 |  
+---------------------+---------------------------------+  
| log_slow_queries | オフ |  
| 遅い起動時間 | 2 |  
| slow_query_log | オフ |  
| slow_query_log_file | /usr/local/mysql/mysql-slow.log |  
+---------------------+---------------------------------+  
セット内の 4 行 (0.00 秒)  
  
mysql> set slow_query_log='ON'; <SPAN style="COLOR: #ff00ff"> //グローバルを追加します。そうしないとエラーが報告されます</SPAN> 
エラー 1229 (HY000): 変数 'slow_query_log' はグローバル変数なので、SET GLOBAL で設定する必要があります。 
mysql> set global slow_query_log='ON'; <SPAN style="COLOR: #ff00ff">//このパラメータを ON に設定すると、実行時間が特定の値を超える SQL ステートメントをキャプチャできるスロークエリが有効になります。 </SPAN> 
クエリは正常、影響を受けた行は 0 行 (0.28 秒)  
  
mysql> show variables like "%slow%"; <SPAN style="COLOR: #ff00ff">//有効になっているかどうかを確認します</SPAN> 
+---------------------+---------------------------------+  
| 変数名 | 値 |  
+---------------------+---------------------------------+  
| log_slow_queries | オン |  
| 遅い起動時間 | 2 |  
| slow_query_log | オン |  
| slow_query_log_file | /usr/local/mysql/mysql-slow.log |  
+---------------------+---------------------------------+  
セット内の 4 行 (0.00 秒)

方法2: MySQL設定ファイルmy.cnfを変更する

[root@www ~]# vim /etc/mysql.cnf
[mysqld]エリアの設定に以下の内容を追加します。
......

slow_query_log = 1 //スロークエリログを開きます。1をONに変更することもできます
long_query_time = 1 //mysql のスロークエリ時間。スロークエリと見なされるまでの秒数を指定します。1 秒を超える実行ステートメントをスロークエリログに記録するように設定することをお勧めします。
slow_query_log_file= /var/lib/mysql/mysql-slow.log // スロークエリログのパス。ここで、slow_query_log_file を log-slow-queries に変更できます。
// 主に上記の3行の内容ですが、詳細な設定として次の行を追加することもできます
long-queries- not-using-indexes //インデックスを使用しないクエリステートメントを記録する
min_examined_row_limit = 1000 // レコードを最大 1000 回検索することでクエリが遅くなります
log-slow-admin-statements //ALTER TABLE やその他のステートメントによって発生した遅いクエリを記録する
log-slow-slave-statements //サーバーから生成された遅いクエリを記録する

分析ツール

分析ツールは、実際に mysql-slow.log に記録されたデータを分析して表示します (実際には、必要な情報を抽出するためのシェルスクリプトを作成することもできます)。

[root@www ~]# cat mysql-slow.log //コマンドを表示

/usr/local/mysql/libexec/mysqld、バージョン: 5.1.26-rc-log (ソース配布)。次で開始:  
TCP ポート: 3306 Unix ソケット: /tmp/mysql.sock  
時間IDコマンド引数  
# 時刻: 100814 13:28:30  
# ユーザー@ホスト: root[root] @ localhost []  
# クエリ時間: 10.096500 ロック時間: 0.045791 送信行数: 1 検査行数: 2374192  
タイムスタンプを1281763710に設定します。  
ad_visit_history から count(distinct ad_code) を x として選択します。ad_code が (media_id=15 の ad_list から ad_code を選択します)。  
# 時間: 100814 13:37:02  
# ユーザー@ホスト: root[root] @ localhost []  
# クエリ時間: 10.394134 ロック時間: 0.000091 送信行数: 1 検査行数: 2374192  
タイムスタンプを1281764222に設定します。  
ad_visit_history から count(distinct ad_code) を x として選択します。ad_code が (media_id=15 の ad_list から ad_code を選択します)。  
# 時間: 100814 13:37:16  
# ユーザー@ホスト: root[root] @ localhost []  
# クエリ時間: 4.608920 ロック時間: 0.000078 送信行数: 1 検査行数: 1260544  
タイムスタンプを1281764236に設定します。  
ad_visit_history から cou として count(*) を選択し、ad_code in (ad_list から ad_code を選択し、id=41 の場合)、id desc で順序付けします。

ご覧の通り、実行時間やロック時間など、SQL 文の実行状況を記録するだけなので、分析ツールを使用するかどうかは個人の状況によります。分析ツールは数多くありますが、ここでは MySQL に付属しているスロークエリ分析ツールである mysqldumpslow の使い方についてのみ説明します。

[root@www ~]# mysqldumpslow -h

オプションhには引数が必要です  
エラー: 不正なオプション 
  
使用方法: mysqldumpslow [ OPTS... ] [ LOGS... ]  
  
MySQLのスロークエリログを解析して要約します。オプションは次のとおりです。  
  
 --verbose 詳細  
 --debug デバッグ  
 --help このテキストを標準出力に書き込む  
  
 -v 詳細  
 -d debug //エラー チェック -s ORDER 何を基準に並べ替えるか (t、at、l、al、r、ar など)、'at' がデフォルト //並べ替え方法: クエリ回数、時間、ロック時間、返されるレコード数 -r 並べ替え順序を逆にする (最初ではなく最後が最大) //並べ替えを逆にする -t NUM 上位 n 件のクエリのみを表示する //上位 N 件のクエリを表示する -a すべての数値を N に、文字列を 'S' に抽象化しない 
 -n NUM 名前に少なくとも n 桁の数字が含まれる抽象的な数字 //名前に少なくとも n 桁の数字が含まれる抽象的な数字 -g PATTERN grep: この文字列を含む文のみ考慮 //構成モード -h HOSTNAME *-slow.log ファイル名の db サーバーのホスト名 (ワイルドカードも可)、 //MySQL マシン名または IP 
 デフォルトは「*」で、すべてに一致します 
 -i NAME サーバーインスタンス名（mysql.server 起動スクリプトを使用している場合） 
 -l 合計時間からロック時間を減算しない //合計時間からロック時間を減算しない

例:

[root@BlackGhost bin]# ./mysqldumpslow -sr -t 20 /var/lib/mysql/mysql-slow.log

[root@BlackGhost bin]# ./mysqldumpslow -sr -t 20 -g 'count' /var/lib/mysql/mysql-slow.log

MySQL の遅いクエリ操作に関する上記の要約は、私が皆さんと共有したいことのすべてです。これが皆さんの参考になれば幸いです。また、123WORDPRESS.COM をサポートしていただければ幸いです。

以下もご興味があるかもしれません: