MySQLは遅いSQLを開始し、原因を分析します

ステップ1. MySQLスロークエリを有効にする

方法1: 設定ファイルを変更する

Windows: Windows 構成ファイルは my.ini で、通常は MySQL インストール ディレクトリまたは c:\Windows にあります。

Linux: Linuxの設定ファイルはmy.cnfで、通常は/etcにあります。

my.ini に数行追加します:

[mysqlld] 
長いクエリ時間=2 
#5.0、5.1、およびその他のバージョンには、次の構成オプションがあります。log-slow-queries="mysql_slow_query.log" 
#5.5以降のバージョンでは、次のオプションslow-query-log=Onを設定します 
slow_query_log_file="mysql_slow_query.log" 
インデックスを使用しないログクエリ

最初の文は、クエリが遅いと判断されるまでの秒数を定義するために使用されます。ここでは 2 秒と定義します。

2 番目の文は、スロー クエリ ログのパスを定義するために使用されます (Windows なので、権限の問題はありません)

3番目の文は、インデックスを使用しないクエリを記録することです

ステップ2: 遅いクエリのステータスを確認する

方法2: MySQLデータベースを介して低速クエリを有効にする

上記の設定を有効にするには、mysql サーバー プロセス mysqld を再起動する必要があります。しかし、多くの場合、特に製品の運用環境では、変更を加えるたびに MySQL サーバーを再起動することは望ましくなく、特定の時間にそれを記録することも望まれます。 MySQL 5.1 では、より柔軟なランタイム制御が提供されるため、MySQL サーバーを再起動する必要がなく、特定の低速クエリを選択的に記録したり、記録しなかったりすることができます。

MySQL 5.1 では、スロークエリの有効化/無効化を柔軟に制御するためのグローバル変数slow_query_logとslow_query_log_fileが提供されています。 long_query_timeで時間を設定することも可能

#// スロークエリのログ記録を有効にする#注: slow_query_log グローバル変数が設定されている場合、log_slow_queries も暗黙的に変更されますmysql>set global slow_query_log=ON

残念ながら、MySQL 5.0 では柔軟な制御のための同様のグローバル変数は提供されていませんが、long_query_time を十分に大きな値に設定することで、特定のクエリ ステートメントの記録を回避できます。例えば

mysql> グローバル long_query_time を 3600 に設定します。

mysql> グローバル log_querise_not_using_indexes を ON に設定します。

MySQL 5.0 では、サービスがシャットダウンされていないときにログを記録したくない場合は、ログ ファイルを /dev/null へのシンボリック リンクにすることができます。注意: 変更後にFLUSH LOGSを実行するだけで、MySQLが現在のログファイル記述子を解放し、/dev/nullに再ログ記録するようになります。

MySQL 5.0 とは異なり、MySQL 5.1 では実行時にジャーナリング動作を変更し、データベース テーブルにログを記録できます。 mysql グローバル変数 log_output を TABLE に設定するだけです。 MySQL は、mysql.gengera_log と mysql.slow_log の 2 つのテーブルにログを記録します。ただし、ジャーナル ファイルに記録することをお勧めします。

mysql> 'log_output' のような変数を表示する\G
変数名: log_output
値: ファイル
mysql> グローバル log_output='table' を設定します。

欠陥と監査

遅いクエリを記録すると、製品の最適化に役立ちます。ただし、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 などのクエリも記録されます。

microslow-patch パッチを使用すると、より細かい時間の粒度を使用して、実行されたすべての SQL ステートメントを記録できます。ただし、このパッチを使用するには、MySQL を自分でコンパイルする必要があります。安定性の理由から、このパッチを開発およびテスト環境に適用して、その利便性を享受することをお勧めします。運用環境ではこれを行わないようにしてください...

ステップ2. スロークエリが有効になっていることを確認する

MySQLのスロークエリのステータスを表示するには、次のSQL文を実行します。

実行結果には、スロー クエリが有効かどうか、スロー クエリの秒数、スロー クエリ ログなどの情報が画面に表示されます。

/* 遅いクエリ時間をチェック */ 
「long_query_time」のような変数を表示します。デフォルトは 10 秒です。 
/* スロークエリの設定を確認する */ 
「%slow_queries%」のようなステータスを表示します。 
/*スロークエリログパスを表示*/ 
 「%slow%」のような変数を表示します。

ステップ3: 低速クエリ操作を実行する

実際、意味のある低速クエリを実行するのは困難です。自分でテストしたところ、200,000 件のデータ レコードを含む大規模なテーブルをクエリした場合でも、数秒しかかかりませんでした。これを次の文に置き換えることができます。

スリープ(10)を選択します。

ステップ4: 遅いクエリの数を確認する

実行された低速クエリの数を表示するには、次の SQL ステートメントを使用します。

'%slow%' のようなグローバル ステータスを表示します。

mysql ログの設定:

注: これらの日次ファイルは、mysql が再起動されたときにのみ生成されます # すべての SQL ステートメントを記録します log=E:/mysqllog/mysql.log 
#データベースの起動とシャットダウンの情報、および操作中に生成されたエラー情報を記録します。log-error=E:/mysqllog/myerror.log 
# SELECT文を除くすべてのSQL文をログに記録します。このログはデータファイルの復元に使用できます。log-bin=E:/mysqllog/bin 
#遅いクエリのSQL文を記録する log-slow-queries=E:/mysqllog/slow.log  
#クエリ時間が遅い long_query_time=2

ステップ4: スロークエリログを分析する

方法1: ツールを使って分析する

MySQL には、遅いクエリ ログを分析するための mysqldumpslow ツールが付属しています。さらに、便利なオープン ソース ツールもいくつかあります。たとえば、MyProfi（ダウンロードアドレス：http://sourceforge.net/projects/myprofi/）、mysql-log-filter、そしてもちろんmysqlsla

以下は、mysqldumpslow の共通パラメータの説明です。詳細な情報については、mysqldumpslow -help を使用して照会できます。

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

次のステップは、mysql に付属する低速クエリ ツールである mysqldumpslow を使用して分析することです (mysql の bin ディレクトリ内)。ログ ファイルの名前は host-slow.log です。

最も多くのレコードを持つ上位10のSQL文を一覧表示します

mysqldumpslow -sc -t 10 ホストの遅い.log

最も多くのレコードを返す上位10個のSQL文をリストします

mysqldumpslow -sr -t 10 ホスト-slow.log

時間別に左結合を含む最初の10個のSQL文を返す

mysqldumpslow -st -t 10 -g "左結合" ホストスロー.log

mysqldumpslow コマンドを使用すると、必要なさまざまなクエリ ステートメントを明確に取得できます。これは、MySQL クエリ ステートメントの監視、分析、最適化に非常に役立ちます。

方法2: MySQLのスロークエリログを直接分析する

ログの一部は次のとおりです。

# 時間: 121017 17:38:54 
# ユーザー@ホスト: root[root] @ localhost [127.0.0.1] 
# クエリ時間: 3.794217 ロック時間: 0.000000 送信行数: 1 検査行数: 4194304 
タイムスタンプを1350466734に設定します。 
text='orange' の wei から * を選択します。 
# 時間: 121017 17:46:22 
# ユーザー@ホスト: root[root] @ localhost [127.0.0.1] 
# クエリ時間: 3.819219 ロック時間: 0.000000 送信行数: 0 検査行数: 4194304 
タイムスタンプを1350467182に設定します。 
wei から * を選択し、text='xishizhaohua' を指定します。

実際、遅いクエリ ステートメントを見つけるだけで十分です。次に示すように、explain または desc コマンドを実行して遅いクエリ ステートメントを表示します。

問題は明らかであり、解決策も明らかです。インデックスを作成します。

mysql> wei(text) にインデックス text_index を作成します。  
クエリは正常、4194304 行が影響を受けました (1 分 58.07 秒)  
レコード: 4194304 重複: 0 警告: 0

すると、クエリ操作を実行するときにかかる時間が大幅に短縮されます。

mysql> select * from wei where text='orange'; 
+---------+--------+ 
| ID | テキスト | 
+---------+--------+ 
| 4103519 | オレンジ | 
+---------+--------+ 
セット内1列（0.33秒）

スロー クエリ ログは、実行された SQL ステートメントを記録するのに役立ちます。しかし、それは万能薬ではなく、あなたが思っているほど意味がないかもしれません。どのステートメントが遅いかはわかるだけですが、なぜ遅いのでしょうか? 具体的な理由を自分で分析し、継続的にデバッグする必要があります。より効率的な SQL ステートメントを変更するだけで済む場合もあれば、単にインデックスを追加するだけで済む場合もありますが、アプリケーションの設計を調整する必要が生じる可能性もあります。たとえば、上記のステートメントは非常に明白で、600 万行を超えるデータをチェックします。残念ながら、すべての記述がそれほど明白なわけではありません。他にも次のような理由が考えられます:

• *テーブルがロックされているため、クエリはアイソクロナス状態になります。 lock_timeは、クエリがロックの変換を待機した時間を示します。
• *データまたはインデックスはキャッシュされません。これは、サーバーを初めて起動した場合やサーバーが調整されていない場合によく発生します。
• *データベースのバックアップ、I/Oが遅くなる
• * 他のクエリが同時に実行され、現在のクエリが削減される可能性があります

したがって、ログ ファイル内の特定のレコードについて過度に神経質になるのではなく、合理的に検討して実際の理由を見つけてください。遅いクエリが頻繁に発生する場合は、特別な注意を払う必要があります。個別に発生した場合は、定期的なチェックを行ってください。盲目的に試すよりも有用な、比較と排除のための統計とベンチマーク レポートをまとめることをお勧めします。この部分にあまり時間とエネルギーを無駄にしないでいただければ幸いです。

要約する

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