DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

MySQLクエリ最適化：実践的なハウツーガイド

EXPLAIN、インデックス、安全な書き換え、スロークエリの証拠を用いた実践的なMySQLクエリチューニングガイド。

MySQLクエリ最適化：実践的なハウツーガイド

MySQLのスロークエリは、実行計画を確認すれば謎ではなくなります。難しいのはインデックスが重要だと知ることではありません。難しいのは、どのクエリが遅いのかを証明し、MySQLがなぜその計画を選んだのかを理解し、書き込み、ストレージ、他のクエリに悪影響を与えずにクエリやインデックスを変更することです。

証拠から始めましょう。スロークエリログ、パフォーマンススキーマ、アプリケーショントレース、またはPMMなどの監視ツールを使用して、実際にユーザーに影響を与えているクエリを見つけます。次に、EXPLAINを使用し、安全な場合はEXPLAIN ANALYZEを使用して、MySQLが何をしているかを確認します。

クエリパフォーマンスの理解

一般的な原因は以下の通りです：

  • インデックスの欠如または非効率性： 適切なインデックスがないと、MySQLはフルテーブルスキャンを実行する必要があり、大規模なテーブルでは非常に非効率です。
  • 不適切に記述されたSQL： 非サーガブルフィルター、不要なSELECT *、偶発的なクロス結合、非効率な結合条件はすべてパフォーマンスを低下させる可能性があります。
  • 大規模なデータセット： データが多いほど、読み取り、ソート、グループ化、キャッシュするページが増えます。
  • ハードウェアと設定： 最適でないサーバー設定や不十分なハードウェアリソースも影響を与える可能性がありますが、このガイドではクエリレベルの最適化に焦点を当てます。

EXPLAINの力

EXPLAINは、MySQLがクエリをどのように計画するかを理解したいときに最初に使用するツールです。単純なEXPLAIN SELECTの場合、MySQLは結果セットを返さずにオプティマイザが選択した計画を示します。EXPLAIN ANALYZEはクエリを実行し、実際のタイミングを報告するため、本番システムでは注意して使用してください。

EXPLAINの使用方法

読み取りクエリの場合は、先頭にEXPLAINを付けます：

EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

EXPLAIN出力の解釈

EXPLAINの出力は、いくつかの重要な列を持つテーブルです：

  • id：クエリ内のSELECTのシーケンス番号。番号が大きいほど通常は先に実行されます。
  • select_type：SELECTのタイプ（例：SIMPLE、PRIMARY、SUBQUERY、DERIVED）。
  • table：アクセスされているテーブル。
  • partitions：使用されているパーティション（パーティショニングが有効な場合）。
  • type：結合タイプ。これは最も有用な列の1つです。クエリの形状が許せば、const、eq_ref、ref、またはrangeを目指しましょう。大規模なテーブルでのindex、特にALLには注意が必要です。
  • possible_keys：MySQLが使用できるインデックスを示します。
  • key：MySQLが実際に使用することを選択したインデックス。
  • key_len：MySQLが使用すると予想されるインデックス部分の長さ。短ければ自動的に良いわけではありません。選択性とクエリに依存します。
  • ref：インデックス（key）と比較される列または定数。
  • rows：MySQLが調査すると予想される行数の見積もり。
  • filtered：テーブル条件によってフィルタリングされる行の割合。
  • Extra：MySQLがクエリをどのように解決するかに関する追加情報。注目すべき主な値：
    • Using where：MySQLが行を処理する際に条件を適用することを示します。一般的であり、常に悪いわけではありません。
    • Using index：クエリがインデックスでカバーされている（必要なすべての列がインデックスに含まれている）ことを意味し、これは良いことです。
    • Using temporary：MySQLが一時テーブルを作成する必要があることを示し、多くの場合GROUP BYやORDER BY操作で発生します。遅くなる可能性があります。
    • Using filesort：MySQLが外部ソートを実行する必要があることを示します（順序付けにインデックスを使用していない）。これは多くの場合、非効率なORDER BY句の兆候です。

EXPLAINを使用したボトルネックの特定

一般的なシナリオと、EXPLAINが問題の特定にどのように役立つかを見てみましょう：

シナリオ1：フルテーブルスキャン

次のようなクエリを考えます：

SELECT * FROM orders WHERE order_date = '2023-10-26';

order_date列にインデックスがない場合、EXPLAINは次のように表示する可能性があります：

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 1000000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

問題： type: ALLはフルテーブルスキャンを示します。rows: 1000000は、MySQLがordersテーブルのすべての行を調査する必要があることを示しています。key: NULLはインデックスが使用されなかったことを意味します。

解決策： order_date列にインデックスを追加します：

CREATE INDEX idx_order_date ON orders (order_date);

インデックスを追加した後、EXPLAINを再実行します。refやrangeなど、より選択的なアクセスタイプが表示され、日付フィルターが選択的であれば、推定行数が減少するはずです。

シナリオ2：非効率なORDER BYまたはGROUP BY

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id ORDER BY customer_id;

customer_idにインデックスがない場合、EXPLAINは次のように表示する可能性があります：

+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra                            |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
|  1 | SIMPLE      | orders | index | NULL          | NULL | NULL    | NULL | 100000 | Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+

問題： Using temporaryとUsing filesortは、MySQLがデータのソートとグループ化にコストのかかる操作を実行していることを示します。これは多くの場合、グループ化と順序付けの両方の要件を効率的に満たすインデックスがないためです。

解決策： この特定のクエリの場合、(customer_id)のインデックスにより、MySQLはグループ化の順序で行をスキャンできる可能性があります。実際のクエリが最初に日付、ステータス、またはテナントでフィルタリングする場合、(tenant_id, status, customer_id)のような複合インデックスの方が適している可能性があります。

CREATE INDEX idx_customer_id ON orders (customer_id);

シナリオ3：不要なSELECT *の使用

すべての列（*）を選択しながら、必要な列が少ない場合、より多くのデータを転送し、カバリングインデックスが有用になるのを妨げる可能性があります。これは、JSON列、テキストブロブ、または多くのNULL許容フィールドを持つワイドテーブルで特に顕著です。

-- 'status'にインデックスがあると仮定
SELECT * FROM tasks WHERE status = 'pending';

EXPLAINはUsing whereを表示するかもしれませんが、フィルタリングに使用されるインデックスにない列をクエリが必要とする場合、それでもテーブルデータにアクセスする必要があります。

解決策： 必要な列のみを指定します：

SELECT task_id, description FROM tasks WHERE status = 'pending';

この正確な形状のクエリを頻繁に実行する場合は、フィルター列と返される列を含むカバリングインデックスを検討します：

CREATE INDEX idx_tasks_status_id_description
  ON tasks (status, task_id, description);

すべてのクエリに対してカバリングインデックスを作成しないでください。読み取りを高速化しますが、ストレージと書き込みのオーバーヘッドが発生します。

スロークエリの書き換え

インデックス作成以外にも、SQLの構造化方法によってMySQLが実行する作業量が変わります。

相関サブクエリを避ける

相関サブクエリは、外部クエリで処理される行ごとに1回実行される可能性があります。MySQLはそれらの一部を最適化できますが、EXPLAINが繰り返し依存ルックアップを示す場合、結合または導出テーブルの方が明確で高速であることがよくあります。

非効率な場合が多い：

SELECT o.order_id, o.order_date
FROM orders o
WHERE o.customer_id IN (
    SELECT c.customer_id
    FROM customers c
    WHERE c.country = 'USA'
);

結合としてより良い場合が多い：

SELECT o.order_id, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA';

両方のバージョンでEXPLAINを使用してください。結合はすべてのスキーマで自動的に高速になるわけではありませんが、多くのチームにとって推論とインデックス作成が容易です。

LIKE句の最適化

LIKE句の先頭のワイルドカード（%）は、通常、通常のBツリーインデックスが範囲シークに使用されるのを防ぎます。

非効率：

SELECT * FROM products WHERE product_name LIKE '%widget';

より良い（可能な場合）：

SELECT * FROM products WHERE product_name LIKE 'widget%';

含むスタイルのマッチングが必要な場合は、適切なテキスト検索にはMySQL全文インデックス、特定の言語にはn-gramアプローチ、または関連性と柔軟なマッチングが重要な場合は検索エンジンを検討してください。

可能な場合はUNIONの代わりにUNION ALLを使用する

UNIONは重複行を削除するため、追加のソートと重複排除の手順が必要です。重複がないことがわかっている場合、または重複を削除する必要がない場合は、UNION ALLの方が高速です。

遅い：

SELECT name FROM table1
UNION
SELECT name FROM table2;

速い：

SELECT name FROM table1
UNION ALL
SELECT name FROM table2;

その他の最適化のヒント

  • 統計情報を最新に保つ： テーブル統計情報が最新であることを確認して、クエリオプティマイザが情報に基づいた決定を行えるようにします。これは通常自動的に処理されますが、ANALYZE TABLEで手動で更新できます。
  • サーバー設定： クエリチューニングは、小さなInnoDBバッファプールや過負荷のディスクを補償しません。MySQL 8.0では、古いクエリキャッシュは削除されているため、query_cache_sizeを中心に新しいチューニングを計画しないでください。
  • 定期的な監視： MySQL Enterprise Monitor、Percona Monitoring and Management（PMM）、または組み込みのパフォーマンススキーマビューなどのツールを使用して、スロークエリを追跡し、傾向を特定します。

実践的なチューニングワークフロー

本番システムの場合、スロークエリから外側に向かってチューニングします：

  1. 正確なSQL、バインド値、行数、タイミングをキャプチャします。
  2. MySQLのバージョンがサポートしている場合は、EXPLAIN FORMAT=TREEまたはEXPLAIN FORMAT=JSONを実行します。
  3. 選択されたインデックスがフィルターと結合パターンに一致するかどうかを確認します。
  4. 現実的なデータでクエリの書き換えまたはインデックス変更をテストします。
  5. 調査された行数、一時テーブル、ソート動作、およびウォールクロックレイテンシを比較します。

これにより、クエリが「遅そうに見える」という理由だけでインデックスを追加することを防ぎます。インデックスにはコストがかかります。すべての挿入、更新、削除はそれらを維持する必要があります。10個の重複するインデックスを持つテーブルは、1つの読み取りクエリが改善されたとしても、全体的に遅くなる可能性があります。

一般的なマルチテナントアプリケーションクエリの場合、インデックスの順序は、インデックス付き列の数よりも重要であることがよくあります：

SELECT id, created_at, total
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;

有用なインデックスは次のようになります：

CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

このインデックスは等価フィルターで始まり、日付範囲と順序付けをサポートします。created_atを最初に置くと、MySQLは適切なテナントを見つける前に多くのテナントをスキャンする可能性があります。statusを省略すると、クエリは機能するかもしれませんが、多くの余分な行を調査することになります。

非サーガブルフィルターに注意

条件がサーガブルであるとは、MySQLがインデックスを使用して一致する行を検索できる場合です。インデックス付き列を関数でラップすると、それが壊れることがよくあります：

-- created_atのインデックスを使用するのが難しい
SELECT * FROM orders
WHERE DATE(created_at) = '2025-01-15';

範囲として書き換えます：

SELECT *
FROM orders
WHERE created_at >= '2025-01-15'
  AND created_at <  '2025-01-16';

2番目のバージョンでは、MySQLがcreated_atのインデックスをシークできます。同じ考え方がLOWER(email)、数値列の計算、暗黙の型変換にも当てはまります。列にインデックスがある場合は、可能な限り比較の列側をクリーンに保ちます。

ページネーションに注意

オフセットページネーションは、深いページでコストが高くなります：

SELECT id, title
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20 OFFSET 200000;

MySQLは、要求されたページを返す前に、以前の行を通過する必要があります。フィード、監査ログ、管理テーブルの場合、キーセットページネーションの方が通常は優れています：

SELECT id, title, published_at
FROM posts
WHERE status = 'published'
  AND (published_at, id) < ('2025-05-01 12:00:00', 987654)
ORDER BY published_at DESC, id DESC
LIMIT 20;

(status, published_at, id)などのインデックスと組み合わせます。これにより、ユーザーがページ10,000にジャンプするのではなくカーソルを移動するため、製品の動作が少し変わりますが、厄介なクエリを予測可能なものに変えることができます。

実際のデータで検証する

小さなステージングデータベースは嘘をつきます。20,000行で一瞬のクエリも、特にデータ分布に偏りがある場合、2億行ではひどいものになる可能性があります。可能であれば、本番と同様のボリュームとカーディナリティに対してテストしてください。本番データをコピーできない場合は、少なくとも同様のテナントサイズ、ステータス分布、日付範囲でデータを生成してください。

もう1つの習慣が役立ちます：古い計画と新しい計画をチケットに残しておくことです。将来の自分は、なぜインデックスが存在するのかを知りたがるでしょう。

最高のMySQLチューニング習慣は、すべての変更にその価値を証明させることです。スロークエリをキャプチャし、計画を検査し、1つのクエリまたはインデックスを変更し、レイテンシと調査された行数を比較します。クリーンなEXPLAIN計画は有用ですが、本当の勝利は、新しい書き込み負荷やストレージの肥大化を生み出さずに、本番レイテンシを低減することです。