Posted inMySQL

MySQLスロークエリログを使用して遅いSQLクエリを検出し最適化するためのガイド

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

アプリケーションが遅い、原因はデータベースだと疑っていますか？

これは、ほとんどの開発者が一度は直面する状況です。ユーザーから特定の機能が「亀のように遅い」と苦情が寄せられます。アプリケーションのコードを確認しても異常は見当たらず、すべての疑いの目はデータベースに向けられます。しかし、どのSQLクエリがボトルネックになっているのかを正確に知るにはどうすればよいのでしょうか？当て推量ではなく、MySQLは非常に強力なツールを提供しています。それがスロークエリログです。

このツールは、設定した実行時間のしきい値を超えたすべてのSQLコマンド、つまり「邪魔者」をすべて記録します。これらを見つけ出すことが、データベースのパフォーマンスを最適化するための最初で最も重要なステップです。

中核となる概念を理解する

MySQLスロークエリログとは？

簡単に言えば、スロークエリログはテキストファイルです。MySQLサーバーは、実行時間がlong_query_time（秒単位）の値より長いSQLクエリに関する情報を記録するためにこれを使用します。クエリ文だけでなく、ログファイルには実行時間やスキャンした行数など、問題をより正確に診断するのに役立つ貴重な情報も含まれています。

スロークエリログはいつ有効にすべきか？

特にトラフィックの多いシステムでログを常時記録すると、I/Oリソースを消費し、パフォーマンスにわずかな影響を与える可能性があります。そのため、最も一般的な使用シナリオは2つあります。

  1. 開発・テスト環境：常に有効にする。これにより、開発段階で問題のあるクエリをすぐに発見できます。
  2. 本番環境：パフォーマンス低下の報告があった場合にのみ有効にするか、定期的に（例：週に数時間）システムの「健康診断」のために有効にします。問題を発見して修正した後は、リソースを節約するために無効にすることをお勧めします。

AからZまでの詳細な実践ガイド

それでは本題に入りましょう。設定方法、ログの読み方、そして最適化の方法です。

1. スロークエリログの設定

まず、MySQLサーバーに接続し、現在の設定を確認します。

状態の確認

MySQLクライアントで次のコマンドを実行します。

SHOW VARIABLES LIKE '%slow_query_log%';

次のような結果が表示されます。

+---------------------+--------------------------------------------------+
| Variable_name       | Value                                            |
+---------------------+--------------------------------------------------+
| slow_query_log      | OFF                                              |
| slow_query_log_file | /var/lib/mysql/your-hostname-slow.log            |
+---------------------+--------------------------------------------------+

次に、デフォルトの時間しきい値を確認します。

SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

上記の例では、ログは無効（OFF）で、時間しきい値は10秒です。これは非常に高いしきい値です。商品ページの読み込みに9秒かかるクエリは許容できませんが、この設定では記録されません。この値を下げる必要があります。

スロークエリログを有効にする（一時的）

この方法は、サーバーを再起動せずに素早く診断するのに非常に便利です。ただし、この設定はMySQLが再起動すると失われます。

SET GLOBAL slow_query_log = 'ON';
-- しきい値を1秒に設定、手始めに妥当な値です
SET GLOBAL long_query_time = 1;

my.cnfファイルでの永続的な設定

再起動後も設定を有効にするには、MySQLの設定ファイルを編集する必要があります。このファイルの場所はオペレーティングシステムによって異なる場合があります。

  • Ubuntu/Debian: /etc/mysql/my.cnf または /etc/mysql/mysql.conf.d/mysqld.cnf
  • CentOS/RHEL: /etc/my.cnf

設定ファイルを開き、[mysqld]セクションの下に次の行を追加（または修正）します。

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
# (オプションだが非常に便利) インデックスを使用しないクエリを記録する
log_queries_not_using_indexes = 1

重要な注意： /var/log/mysql/ディレクトリが存在し、mysqlユーザーがそこに書き込み権限を持っていることを確認してください。ファイルを保存した後、変更を適用するためにMySQLサービスを再起動する必要があります。

sudo systemctl restart mysql

2. ログファイルの分析

システムがしばらく稼働すると、mysql-slow.logファイルに遅いクエリが記録され始めます。直接閲覧することも、ツールを使って分析することもできます。

ログファイルを直接読む

ログファイルのエントリは次のような構造になっています。

# Time: 2023-10-27T10:15:30.123456Z
# User@Host: root[root] @ localhost []  Id: 123
# Query_time: 2.123  Lock_time: 0.000  Rows_sent: 5  Rows_examined: 50000
SET timestamp=1698398130;
SELECT p.post_title, u.display_name 
FROM wp_posts p 
JOIN wp_users u ON p.post_author = u.ID 
WHERE p.post_status = 'publish' AND u.user_email LIKE '%@gmail.com';

注意すべき重要な情報：

  • Query_time: クエリの実行時間（2.123秒）。
  • Rows_examined: データベースがスキャンしなければならなかった行数（50,000行）。これは非常に重要な指標です。Rows_examinedとRows_sent（返された行数）の比率が高いほど、クエリの効率は悪くなります。条件を満たす5行を見つけるためだけに、Excelファイルで50,000行を読まなければならないと想像してみてください。
  • 遅延を引き起こした具体的なSQL文。

`mysqldumpslow`ツールの使用

ログファイルが大きくなると、手動で読むのは不可能です。`mysqldumpslow`はMySQLに付属のユーティリティで、遅いクエリを集計して並べ替えるのに役立ちます。

便利なコマンドをいくつか紹介します。

# 実行時間が最も長い上位10クエリをリストアップ
sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# スキャンした行数が最も多い上位10クエリをリストアップ
sudo mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log

# 実行回数が最も多い上位10クエリをリストアップ（繰り返し遅延を引き起こしている）
sudo mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

`mysqldumpslow`の出力は、類似のクエリをグループ化し、具体的な値を’S’（文字列）と’N’（数値）に置き換えます。これにより、個々のクエリではなく、どの*種類*のクエリが主な問題であるかを特定できます。

3. 遅いクエリの最適化

スローログから「犯人」を特定したら、次のステップはEXPLAINコマンドでそれを「解剖」することです。

EXPLAINによる分析

ログで次のクエリを見つけたとします。

SELECT * FROM products WHERE category_name = 'スマートフォン';

その前にEXPLAINキーワードを付けて、MySQLクライアントで実行します。

EXPLAIN SELECT * FROM products WHERE category_name = 'スマートフォン';

MySQLは実行計画を返します。特にtype列に注意してください。値がALLの場合、それは非常に悪い兆候です。これはMySQLがテーブル全体をスキャン（フルテーブルスキャン）していることを意味します。rows列には推定値が表示され、通常は非常に大きな数値になります。

インデックスを追加して高速化

WHERE句でフルテーブルスキャンが見られる場合、最も効果的な解決策は、通常、フィルタリングに使用される列にインデックスを追加することです。

CREATE INDEX idx_products_category_name ON products(category_name);

これは実際の例です。約50GBの本番データベースで、数百万のレコードをスキャンする必要があったため、製品検索クエリに4.5秒もかかっていました。スローログを分析し、適切な複合インデックスを追加したところ、実行時間はわずか200ミリ秒未満に短縮されました。

EXPLAINで再確認

インデックスを追加した後、もう一度EXPLAINコマンドを実行してみてください。おそらく、type列がrefに変わり、key列に作成したインデックス名が表示され、そして最も重要なことに、rows列の値が大幅に減少するなど、明らかな改善が見られるでしょう。これは、クエリがテーブル全体を「探索」するのではなく、インデックスを使用して目的のデータに直接アクセスしたことを証明しています。

結論

データベースの最適化は魔法ではなく、体系的なプロセスです。スロークエリログは、「アプリが遅い」という曖昧な問題を具体的なタスクのリストに変えるのに役立ちます。標準的な手順は通常、ログを有効にしてデータを収集 -> mysqldumpslowを使用して分析し、最も「重い」クエリを見つける -> EXPLAINを使用して原因を理解する -> インデックスの追加などの最適化手法を適用する、となります。これは、データベースを扱う開発者なら誰でも習得すべき、基礎的かつ不可欠なスキルです。

Share:
Tags: