Posted inMySQL

MySQL Optimizer Hintsを使いこなす：オプティマイザに代わって「ハンドル」を握るべき時とは？

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

実例：MySQLの「頭脳」が計算を誤る時

created_atカラムに最適なインデックスを貼ったはずなのに、EXPLAINを確認するとMySQLが頑なにフルテーブルスキャン（Full Table Scan）を選択している。そんな皮肉な状況に陥ったことはありませんか？さらに、3〜4つのテーブルをJOINする際、MySQLが誤って最大のテーブルをドライビングテーブル（driving table）として選んでしまい、期待していた数ミリ秒ではなく30秒もクエリにかかってしまうこともあります。

以前、私がEコマースシステムの運用を担当していた際、order_itemsテーブルが5000万行に達したことがありました。MySQLのオプティマイザは、時として「最もコストが低い」と判断したインデックスを自信満々に選択します（コストベース）。しかし実際には、データの分布が偏っていたために、その選択によってサーバーのCPU使用率が100%に達してしまいました。その時、ANALYZE TABLEで統計情報を更新するのを待つ余裕はありませんでした。そこで窮地を救ってくれたのが、Optimizer Hintsでした。

なぜMySQLオプティマイザはインデックスの選択を誤るのか？

MySQLは、行数やデータの分散度（cardinality）に基づいてコストを計算するコストベースオプティマイザ（CBO）を採用しています。しかし、CBOが常に賢いとは限りません。主な理由は以下の3点です：

  • 統計情報の劣化: 頻繁にINSERTやDELETEが行われると、information_schema内の統計指標にズレが生じます。この状態のMySQLは、古い地図を見て道案内をしているようなものです。
  • データの偏り (Data Skew): 例えばstatusカラムの99%が’COMPLETED’だとします。残り1%の’PENDING’をフィルタリングする場合、インデックスを使えば非常に高速です。しかし、MySQLはカラム全体の分散度が低いと判断し、テーブルスキャンを選択してしまうことがあります。
  • クエリが複雑すぎる: 10テーブル以上をJOINする場合、実行計画の組み合わせは膨大になります。オプティマイザは計画の計算時間を短縮するために、最適な案をスキップしてしまうことがあります。

解決策：従来の手法からモダンな手法まで

1. 従来の手法：Index Hints (USE, FORCE, IGNORE INDEX)

これはMySQL 5.x時代から馴染みのある方法です。テーブル名の直後に構文を記述します。

SELECT * FROM orders FORCE INDEX (idx_created_at) 
WHERE created_at > '2023-01-01' AND status = 'SHIPPED';

ただし、この方法はやや原始的です。インデックスの選択には介入できますが、JOINの順序やその他のシステムパラメータに影響を与えることはできません。

2. モダンな手法：MySQL 8.0 Optimizer Hints

バージョン8.0以降、MySQLはより柔軟なヒント機能を提供しています。これらは、SELECTキーワードの直後に/*+ ... */という形式のコメントブロックとして記述します。ヒント名を書き間違えても、MySQLは構文エラーを出す代わりに、それを無視して通常通り実行してくれます。

インデックスの使用を強制、または拒否する

FORCE INDEXの代わりに、INDEX()やNO_INDEX()を使用します。これにより、クエリロジックと最適化の指示を明確に分離できます。

-- o (orders) テーブルに対して特定のインデックスを強制する
SELECT /*+ INDEX(o idx_status) */ o.id, o.total 
FROM orders o 
WHERE o.status = 'PENDING';

-- テーブルスキャンの方が速いことがわかっているため、インデックスの使用を禁止する
SELECT /*+ NO_INDEX(users primary) */ * FROM users WHERE id > 100;

JOINの順序を制御する (JOIN_ORDER)

これは複雑なレポートクエリにおける「強力な武器」になります。MySQLがテーブルAからBへJOINしようとしても、フィルタリング後のテーブルBの方が遥かに小さいことが分かっている場合があります。その際、JOIN_ORDERで順序を固定します。

SELECT /*+ JOIN_ORDER(customers, orders, payments) */ 
       c.name, o.order_date, p.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN payments p ON o.id = p.order_id;

この例では、MySQLは必ずcustomersを最初にスキャンし、次にorders、最後にpaymentsの順で処理します。

システム変数を一時的に変更する (SET_VAR)

非常に重いクエリがあり、そのクエリ限定でmax_execution_timeを増やしたい場合はどうすればよいでしょうか？SET_VARを使えば、サーバー全体に影響を与えずに設定を変更できます。

SELECT /*+ SET_VAR(max_execution_time=5000) SET_VAR(tmp_table_size=1G) */ 
       count(*), region 
FROM big_data_logs 
GROUP BY region;

上記のクエリは、最大実行時間を5秒に制限し、テンポラリテーブルに1GBのメモリを割り当てます。これは本番環境において非常に安全な手法です。

現場の知恵：乱用は禁物

Optimizer Hintsは強力ですが、私は常に以下の優先順位で対応するようにしています：

  1. インデックスの確認: インデックスの不足や重複がないかを確認します。
  2. 統計情報の更新: ANALYZE TABLE table_name;を実行します。これにより、コードを修正することなくMySQLが正しいインデックスを再選択してくれることが多いです。
  3. クエリのリファクタリング: クエリを分割したり、CTEを使用してオプティマイザが計算しやすいように書き換えます。
  4. ヒント句の使用: 緊急の修正が必要な場合や、上記の手順で効果がない場合の最終手段として使用します。

注意： ヒントは「技術的負債」になる可能性があります。将来、テーブルが100万行から10億行に増えた場合、今日設定したヒントが原因でクエリが大幅に遅くなるかもしれません。ヒントを使用する際は、必ずコード内にその理由をコメントとして残しておきましょう。

ちょっとしたコツ：ヒントを追加した後は、EXPLAIN ANALYZEを組み合わせて各ステップの実際の実行時間を計測してください。パフォーマンスが少なくとも30〜50%向上しない場合は、ヒントの削除を検討すべきです。

Optimizer Hintsは、DBエンジニアにとって鋭いナイフのようなものです。正しく使えば、最も困難なケースも解決できます。しかし乱用すれば、将来のシステムメンテナンスにおいて自分自身を苦しめることになるでしょう。

Share:
Tags: