MySQLでスロークエリを特定してパフォーマンスを改善する手順

MySQLは、広く使用されているデータベース管理システムですが、スロークエリが発生すると、アプリケーションのパフォーマンスに大きな影響を与えることがあります。スロークエリは、実行に時間がかかるクエリであり、これを特定し、最適化することが重要です。

この記事では、スロークエリを特定し、パフォーマンスを改善するための手順を詳しく解説します。

1. スロークエリの定義と重要性

スロークエリとは、設定された閾値（通常はlong_query_timeで指定）を超えて実行されるクエリのことです。

MySQLでは、スロークエリを特定するためにスロークエリログを使用します。スロークエリを特定することは、データベースのパフォーマンスを向上させるための第一歩です。

2. スロークエリログの有効化

スロークエリを特定するためには、まずスロークエリログを有効にする必要があります。以下の手順で設定を行います。

手順

MySQLにログインします。
- mysql -u root -p
スロークエリログを有効にします。
- SET GLOBAL slow_query_log = 'ON';
スロークエリログの出力先を指定します（デフォルトはデータディレクトリ内のhostname-slow.log）
- SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
スロークエリの閾値を設定します（例：1秒以上のクエリをログに記録）
- SET GLOBAL long_query_time = 1;

これで、1秒以上かかるクエリがスロークエリログに記録されるようになります。

3. スロークエリの確認

スロークエリログが有効になったら、実際にスロークエリを確認します。以下のコマンドでログファイルを表示できます。

cat /var/log/mysql/mysql-slow.log

または、mysqldumpslowツールを使用して、スロークエリログを解析することもできます。

mysqldumpslow /var/log/mysql/mysql-slow.log

このコマンドは、スロークエリの統計情報を提供し、どのクエリが最も時間がかかっているかを特定するのに役立ちます。

4. スロークエリの分析

スロークエリを特定したら、次にそのクエリを分析します。クエリの実行計画を確認するために、EXPLAINステートメントを使用します。

以下は、スロークエリの例です。

SELECT * FROM orders WHERE customer_id = 12345;

このクエリの実行計画を確認するには、次のようにします。

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

EXPLAINの結果には、クエリがどのように実行されるか、どのインデックスが使用されるか、スキャンされる行数などの情報が含まれます。特に注目すべきは、rows列で、スキャンされる行数が多い場合、パフォーマンスが低下する可能性があります。

5. インデックスの最適化

スロークエリの原因の一つは、適切なインデックスが存在しないことです。インデックスを追加することで、クエリのパフォーマンスを大幅に改善できます。

例えば、customer_idにインデックスを追加するには、次のようにします。

ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

インデックスを追加した後、再度EXPLAINを実行して、クエリの実行計画が改善されたか確認します。

6. クエリのリファクタリング

インデックスの追加だけでは不十分な場合、クエリ自体をリファクタリングすることも考慮します。以下のポイントに注意してクエリを最適化します。

*SELECT を避ける: 必要なカラムだけを選択します。
- SELECT order_id, order_date FROM orders WHERE customer_id = 12345;
JOINの最適化
- 不要なJOINを避け、INNER JOINを使用することでパフォーマンスを向上させます。
WHERE句の最適化
- 条件を明確にし、インデックスが効くようにします。

7. EXPLAIN ANALYZEの活用

MySQL 8.0以降では、EXPLAIN ANALYZEを使用して、クエリの実行計画を実際に実行し、詳細な統計情報を得ることができます。これにより、クエリのパフォーマンスをさらに深く分析できます。

EXPLAIN ANALYZE SELECT order_id, order_date FROM orders WHERE customer_id = 12345;

このコマンドは、実行時間やスキャンされた行数などの詳細な情報を提供します。

8. MySQLの統計情報を確認・更新する

ここまでで触れた「統計情報」とは、MySQLのオプティマイザがクエリの実行計画（インデックスを使うか、どの順序でテーブルを結合するかなど）を決定するために参照する、テーブルやインデックスのカーディナリティ（値の種類数）や行数の推定値のことです。統計情報が古いまま放置されていると、実際のデータと乖離が生じ、EXPLAINで確認したはずの実行計画が最適でなくなることがあります。

現在の統計情報を確認する

SHOW TABLE STATUSを使うと、テーブルのおおよその行数や平均行長などの統計情報を確認できます。

SHOW TABLE STATUS LIKE 'orders';

インデックスごとのカーディナリティ（値の種類の多さ）を確認するにはSHOW INDEXを使います。

SHOW INDEX FROM orders;

このCardinality列の値が実際のデータ量に対して極端に少ない、または0に近い場合、統計情報が古くなっている可能性があります。information_schema.STATISTICSテーブルを使えば、複数テーブルの統計情報をまとめてSQLで確認することもできます。

SELECT table_name, index_name, cardinality
FROM information_schema.statistics
WHERE table_schema = 'your_database';

統計情報が古くなるタイミング

以下のようなケースでは、統計情報とテーブルの実データに乖離が生じやすいため、明示的な更新が必要です。

一括INSERT・大量削除など、データ量が急激に変化した直後
InnoDBの永続統計（innodb_stats_persistent）が有効な環境で、自動更新の閾値（innodb_stats_auto_recalc）に達していない場合
サーバー再起動直後（永続統計を使っていない設定の場合、統計情報がリセットされる）

統計情報を明示的に最新化するには、後述のANALYZE TABLEを実行します。

9. 定期的なメンテナンス

スロークエリの特定と最適化は一度きりの作業ではありません。定期的にスロークエリログを確認し、クエリのパフォーマンスを監視することが重要です。また、インデックスの再構築や統計情報の更新も定期的に行うべきです。

ANALYZE TABLE orders;

このコマンドは、ordersテーブルの統計情報を更新し、クエリオプティマイザがより良い実行計画を選択できるようにします。

10. まとめ

MySQLでスロークエリを特定し、パフォーマンスを改善するための手順を解説しました。スロークエリログの有効化、クエリの分析、インデックスの最適化、クエリのリファクタリング、定期的なメンテナンスを行うことで、データベースのパフォーマンスを向上させることができます。

これらの手順を実践することで、アプリケーションの応答性を改善し、ユーザー体験を向上させることができるでしょう。