T-CREATOR

MySQL オプティマイザヒント早見表:/\*+ NO_MERGE, INDEX, HASH_JOIN \*/ 実例集

MySQL オプティマイザヒント早見表:/\*+ NO_MERGE, INDEX, HASH_JOIN \*/ 実例集

MySQL のクエリが遅い、実行計画が期待通りにならない――そんな経験はありませんか? 実は MySQL には「オプティマイザヒント」という強力な機能があり、特定のクエリだけ実行計画を細かくコントロールできます。 本記事では、実務でよく使われる NO_MERGEINDEXHASH_JOIN などの代表的なヒントを実例とともに解説し、すぐに現場で活用できる知識をお届けします。

オプティマイザヒント早見表

よく使われるオプティマイザヒントを、用途別にまとめました。 詳しい使い方は後述の「具体例」セクションをご覧ください。

インデックス制御系

ヒント構文例用途対象バージョン
INDEX​/​*+ INDEX(t1 idx_name) *​/​特定のインデックスを強制使用MySQL 5.7+
NO_INDEX​/​*+ NO_INDEX(t1 idx_name) *​/​特定のインデックスを除外MySQL 5.7+
FORCE_INDEXFORCE INDEX (idx_name)インデックス使用を強制(従来構文)MySQL 5.0+

サブクエリ・ビュー制御系

ヒント構文例用途対象バージョン
NO_MERGE​/​*+ NO_MERGE(subq) *​/​サブクエリを一時テーブルとして独立実行MySQL 5.7+
MERGE​/​*+ MERGE(subq) *​/​サブクエリを外側のクエリにマージMySQL 5.7+
SUBQUERY​/​*+ SUBQUERY(subq) *​/​サブクエリ実行方式を指定MySQL 5.7+

結合制御系

ヒント構文例用途対象バージョン
HASH_JOIN​/​*+ HASH_JOIN(t1, t2) *​/​ハッシュ結合を使用MySQL 8.0.18+
NO_HASH_JOIN​/​*+ NO_HASH_JOIN(t1, t2) *​/​ハッシュ結合を使わないMySQL 8.0.18+
JOIN_ORDER​/​*+ JOIN_ORDER(t1, t2, t3) *​/​結合順序を固定MySQL 8.0+
BKA​/​*+ BKA(t1) *​/​Batched Key Access を有効化MySQL 5.6+
NO_BKA​/​*+ NO_BKA(t1) *​/​Batched Key Access を無効化MySQL 5.6+

その他の最適化制御

ヒント構文例用途対象バージョン
MAX_EXECUTION_TIME​/​*+ MAX_EXECUTION_TIME(1000) *​/​クエリ実行時間を制限(ミリ秒)MySQL 5.7+
SET_VAR​/​*+ SET_VAR(sort_buffer_size=16M) *​/​セッション変数を一時的に変更MySQL 8.0+
SKIP_SCAN​/​*+ SKIP_SCAN(t1 idx_name) *​/​インデックススキップスキャンを使用MySQL 8.0+
NO_SKIP_SCAN​/​*+ NO_SKIP_SCAN(t1 idx_name) *​/​インデックススキップスキャンを無効化MySQL 8.0+

複数ヒントの組み合わせ例

sql-- インデックス指定 + サブクエリ制御
SELECT /*+ INDEX(orders idx_user_id) NO_MERGE(subq) */
  o.*, subq.total
FROM orders o
JOIN (SELECT user_id, SUM(amount) AS total FROM payments GROUP BY user_id) AS subq
  ON o.user_id = subq.user_id;

-- 結合順序 + ハッシュ結合
SELECT /*+ JOIN_ORDER(u, o, p) HASH_JOIN(o, p) */
  u.name, o.order_date, p.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

背景

MySQL オプティマイザの役割とは

MySQL のオプティマイザは、SQL クエリを受け取ると自動的に「どのインデックスを使うか」「どのテーブルから結合するか」といった実行計画を決定します。 多くの場合、オプティマイザは適切な判断を下してくれますが、統計情報が古い場合や複雑な結合クエリでは、必ずしも最適な実行計画が選ばれるとは限りません。

オプティマイザヒントが必要になる場面

以下のようなケースでは、オプティマイザヒントによる介入が有効です。

  • テーブル統計が実際のデータ分布と乖離している
  • 複雑な結合で意図しないテーブルスキャンが発生する
  • サブクエリやビューが展開されず、一時テーブルが作られてしまう
  • 特定のインデックスを強制的に使いたい

こうした場面で、コメント形式のヒント ​/​*+ ... *​/​ を SQL に埋め込むことで、オプティマイザの動作を調整できます。

MySQL のオプティマイザヒントの仕組み

MySQL 5.7 以降では、SQL 標準に準拠した「オプティマイザヒント構文」が正式にサポートされています。 ヒントは SELECT 文の直後に ​/​*+ ヒント名 *​/​ の形式で記述し、クエリごとに細かく制御できるのが特徴です。

以下の図は、オプティマイザヒントがクエリ実行にどう影響するかを示しています。

mermaidflowchart LR
  query["SQL クエリ"] -->|パース| optimizer["オプティマイザ"]
  optimizer -->|ヒント適用| plan["実行計画生成"]
  plan -->|実行| result["結果"]

  hint["/*+ ヒント */"] -.->|制御| optimizer

図で理解できる要点

  • オプティマイザは SQL を解析して実行計画を生成する
  • ヒントはオプティマイザに対する「指示」として機能する
  • ヒントがあることで、自動選択を上書きできる

課題

オプティマイザが最適な実行計画を選べないケース

実務では、以下のような問題に直面することがあります。

課題 1: 不適切なインデックス選択

MySQL オプティマイザは、カーディナリティや統計情報をもとにインデックスを選びますが、実際のデータ分布と合わない場合、効率の悪いインデックスが選ばれてしまいます。

typescript// 例: orders テーブルに複数のインデックスがある場合
SELECT * FROM orders
WHERE user_id = 100 AND status = 'completed';

この場合、user_id のインデックスと status のインデックスがあると、オプティマイザがどちらを選ぶかは統計次第になります。

課題 2: サブクエリやビューがマージされない

ビューやサブクエリを使うと、場合によっては一時テーブルが作成され、パフォーマンスが悪化します。

typescript// サブクエリが独立して実行される例
SELECT * FROM (
  SELECT * FROM users WHERE age > 30
) AS subq
WHERE subq.country = 'Japan';

MySQL 5.7 以前や、複雑な条件では、サブクエリが外側のクエリにマージされず、中間テーブルが作られることがあります。

課題 3: 結合アルゴリズムの選択ミス

MySQL 8.0 からは HASH JOIN がサポートされましたが、デフォルトでは Nested Loop Join が選ばれることが多く、大量データの結合では非効率になる場合があります。

以下の図は、オプティマイザが誤った実行計画を選んでしまう流れを示しています。

mermaidflowchart TD
  start["クエリ実行"] --> check["統計情報確認"]
  check -->|統計が古い| wrong["不適切なインデックス選択"]
  check -->|複雑な結合| temp["一時テーブル作成"]
  check -->|結合方法| loop["Nested Loop<br/>選択"]

  wrong --> slow["スロークエリ"]
  temp --> slow
  loop --> slow
  slow --> problem["パフォーマンス低下"]

図で理解できる要点

  • 統計情報の精度がインデックス選択に直結する
  • サブクエリや結合方法の選択ミスが一時テーブルや非効率な処理を生む
  • これらが積み重なるとスロークエリの原因になる

課題まとめ

#課題影響
1不適切なインデックス選択フルスキャンや遅いインデックスが使われる
2サブクエリがマージされない一時テーブルが作られ、メモリ・ディスク I/O が増加
3結合アルゴリズムの選択ミス大量データの結合で Nested Loop が選ばれ、処理が遅延

これらの課題を解決するために、オプティマイザヒントを活用します。

解決策

オプティマイザヒントの基本構文

MySQL のオプティマイザヒントは、SELECT 文の直後に ​/​*+ ヒント名(引数) *​/​ の形式で記述します。

sqlSELECT /*+ ヒント名(テーブル名 引数) */ カラム
FROM テーブル名
WHERE 条件;

複数のヒントを組み合わせる場合は、スペース区切りで並べます。

sqlSELECT /*+ INDEX(t1 idx_user) NO_MERGE(subq) */ *
FROM table1 t1
JOIN (SELECT * FROM table2) AS subq ON t1.id = subq.id;

主要なオプティマイザヒント一覧

以下は、実務でよく使われるヒントの早見表です。

#ヒント名用途効果
1INDEX特定のインデックスを強制使用指定したインデックスを優先的に使う
2NO_INDEX特定のインデックスを使わない指定したインデックスを除外する
3NO_MERGEサブクエリをマージしない一時テーブルとして独立実行
4MERGEサブクエリを外側にマージサブクエリを展開して最適化
5HASH_JOINハッシュ結合を使用大量データの結合を高速化(MySQL 8.0+)
6NO_HASH_JOINハッシュ結合を使わないNested Loop Join を選択
7JOIN_ORDER結合順序を固定指定した順でテーブルを結合
8BKA / NO_BKABatched Key Access の有効/無効インデックスアクセスを最適化

以下の図は、ヒントがどのように実行計画に影響するかを示しています。

mermaidflowchart LR
  sql["SELECT /*+ INDEX(...) */"] --> parser["パーサー"]
  parser --> hint["ヒント解析"]
  hint -->|INDEX 指定| idx["インデックス強制"]
  hint -->|NO_MERGE 指定| temp["一時テーブル作成"]
  hint -->|HASH_JOIN 指定| hash["ハッシュ結合"]

  idx --> exec["実行計画"]
  temp --> exec
  hash --> exec
  exec --> run["クエリ実行"]

図で理解できる要点

  • ヒントはパーサーで解析され、実行計画生成に反映される
  • 各ヒントは特定の最適化動作を制御する
  • 複数ヒントを組み合わせることで、細かい調整が可能

ヒント適用の基本方針

オプティマイザヒントは強力ですが、多用すると保守性が下がります。 以下のような方針で適用するのがおすすめです。

  • まず EXPLAIN で実行計画を確認する:ヒントなしの状態で問題箇所を特定
  • ピンポイントで適用する:問題のあるクエリにのみヒントを追加
  • 統計情報の更新を優先するANALYZE TABLE で統計を最新化してから判断
  • ヒントの効果を検証する:適用前後で実行時間を計測

具体例

例 1: INDEX ヒントで特定インデックスを強制する

状況

orders テーブルに複数のインデックスがあり、オプティマイザが status のインデックスを選んでしまうが、実際には user_id のインデックスの方が効率的な場合です。

ヒントなしのクエリ

sqlSELECT * FROM orders
WHERE user_id = 100 AND status = 'completed';

EXPLAIN の結果(ヒントなし)

plaintext+----+-------+--------+------------+------+
| id | type  | table  | key        | rows |
+----+-------+--------+------------+------+
|  1 | ref   | orders | idx_status | 5000 |
+----+-------+--------+------------+------+

この場合、idx_status が選ばれていますが、実際には user_id でフィルタした方が効率的です。

INDEX ヒント適用

sqlSELECT /*+ INDEX(orders idx_user_id) */ *
FROM orders
WHERE user_id = 100 AND status = 'completed';

EXPLAIN の結果(ヒント適用後)

plaintext+----+-------+--------+-------------+------+
| id | type  | table  | key         | rows |
+----+-------+--------+-------------+------+
|  1 | ref   | orders | idx_user_id |  200 |
+----+-------+--------+-------------+------+

idx_user_id が使われ、スキャン行数が 5000 → 200 に削減されました。

ポイント

  • INDEX(テーブル名 インデックス名) の形式で指定します
  • テーブルにエイリアスがある場合は、エイリアス名を使います
  • 複数のインデックスを候補として指定する場合は INDEX(t1 idx1, idx2) のように並べます

例 2: NO_MERGE ヒントでサブクエリを独立実行

状況

サブクエリが外側のクエリにマージされてしまい、意図しない実行計画になる場合です。 特に、サブクエリ側で集計や LIMIT を使っているときに有効です。

ヒントなしのクエリ

sqlSELECT *
FROM users u
JOIN (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
  HAVING order_count > 10
) AS subq ON u.id = subq.user_id;

問題点

MySQL がサブクエリを展開してしまい、結合後に集計が行われるため、パフォーマンスが低下することがあります。

NO_MERGE ヒント適用

sqlSELECT *
FROM users u
JOIN (
  SELECT /*+ NO_MERGE() */ user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
  HAVING order_count > 10
) AS subq ON u.id = subq.user_id;

効果

サブクエリが一時テーブルとして独立して実行され、集計結果が確定してから外側の users と結合されます。 これにより、不要な行の結合を避けられます。

ポイント

  • NO_MERGE() はサブクエリの SELECT 直後に記述します
  • ビューに対しても適用可能です:NO_MERGE(view_name)
  • 逆に、マージを強制したい場合は MERGE() を使います

例 3: HASH_JOIN ヒントで大量データの結合を高速化

状況

MySQL 8.0.18 以降では、ハッシュ結合がサポートされています。 大量のデータを結合する際、Nested Loop Join よりも高速になることがあります。

ヒントなしのクエリ

sqlSELECT *
FROM orders o
JOIN order_details od ON o.id = od.order_id;

EXPLAIN の結果(ヒントなし)

plaintext+----+--------+-------+--------+------+
| id | table  | type  | key    | rows |
+----+--------+-------+--------+------+
|  1 | o      | ALL   | NULL   | 10000|
|  1 | od     | ref   | fk_ord | 5    |
+----+--------+-------+--------+------+

Nested Loop Join が選ばれ、orders の各行に対して order_details を検索しています。

HASH_JOIN ヒント適用

sqlSELECT /*+ HASH_JOIN(o, od) */ *
FROM orders o
JOIN order_details od ON o.id = od.order_id;

EXPLAIN の結果(ヒント適用後)

plaintext+----+--------+-------+-----------+------+
| id | table  | type  | Extra     | rows |
+----+--------+-------+-----------+------+
|  1 | o      | ALL   | Hash Join | 10000|
|  1 | od     | ALL   | Hash Join | 50000|
+----+--------+-------+-----------+------+

ハッシュ結合が適用され、大量データの結合が高速化されます。

ポイント

  • HASH_JOIN(テーブル1, テーブル2) の形式で指定します
  • MySQL 8.0.18 以降で有効です
  • インデックスがない結合や、大量データの等値結合で効果的です
  • 逆にハッシュ結合を無効化したい場合は NO_HASH_JOIN() を使います

例 4: JOIN_ORDER ヒントで結合順序を固定

状況

複数テーブルの結合で、オプティマイザが非効率な結合順序を選んでしまう場合です。

ヒントなしのクエリ

sqlSELECT *
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

問題点

オプティマイザが productsordersusers の順で結合してしまい、不要な中間データが大量に生成されることがあります。

JOIN_ORDER ヒント適用

sqlSELECT /*+ JOIN_ORDER(u, o, p) */ *
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

効果

結合順序が usersordersproducts に固定され、効率的な実行計画になります。

ポイント

  • JOIN_ORDER(テーブル1, テーブル2, ...) の形式で、結合順を指定します
  • LEFT JOIN など外部結合では順序変更ができない場合があります
  • 結合順序の最適化は難しいため、EXPLAIN で効果を必ず確認してください

例 5: BKA ヒントで Batched Key Access を有効化

状況

インデックスを使った結合で、Batched Key Access(BKA)を有効にすることで、ランダム I/O を削減できます。

ヒント適用

sqlSELECT /*+ BKA(o) */ *
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'Japan';

効果

orders テーブルへのアクセスがバッチ化され、I/O 効率が向上します。

ポイント

  • BKA(テーブル名) で有効化、NO_BKA(テーブル名) で無効化できます
  • MySQL 5.6 以降で利用可能です
  • インデックスが使える結合で効果を発揮します

具体例まとめ表

#ヒント用途効果対象 MySQL バージョン
1INDEX特定インデックスを強制使用スキャン行数削減5.7+
2NO_MERGEサブクエリを独立実行集計後に結合、効率化5.7+
3HASH_JOINハッシュ結合を使用大量データ結合高速化8.0.18+
4JOIN_ORDER結合順序を固定効率的な結合順に制御8.0+
5BKABatched Key Access 有効化ランダム I/O 削減5.6+

以下の図は、各ヒントがどの段階で実行計画に影響するかを示しています。

mermaidflowchart TD
  query["クエリ"] --> parse["パース"]
  parse --> opt["オプティマイザ"]

  opt -->|INDEX| index_sel["インデックス選択"]
  opt -->|NO_MERGE| subq["サブクエリ処理"]
  opt -->|HASH_JOIN| join_alg["結合アルゴリズム"]
  opt -->|JOIN_ORDER| join_seq["結合順序"]
  opt -->|BKA| access["アクセス方式"]

  index_sel --> plan["実行計画"]
  subq --> plan
  join_alg --> plan
  join_seq --> plan
  access --> plan

  plan --> exec["実行"]

図で理解できる要点

  • 各ヒントはオプティマイザの異なる判断ポイントに作用する
  • INDEX はインデックス選択、HASH_JOIN は結合アルゴリズム、といった具合に役割が分かれている
  • これらを組み合わせることで、複雑なクエリも細かく制御できる

まとめ

MySQL オプティマイザヒントは、実行計画を細かくコントロールできる強力な機能です。 本記事では、代表的なヒントである INDEXNO_MERGEHASH_JOINJOIN_ORDERBKA について、実例とともに解説しました。

活用のポイント

  • まず EXPLAIN で診断:ヒントを適用する前に、実行計画を確認して問題箇所を特定しましょう
  • ピンポイントで適用:すべてのクエリにヒントを入れるのではなく、問題のあるクエリにのみ適用します
  • 統計情報の更新を忘れずにANALYZE TABLE で統計を最新化すれば、ヒントなしで改善することもあります
  • 効果を検証:ヒント適用前後で実行時間を計測し、本当に改善しているか確認しましょう

注意点

  • オプティマイザヒントは強力ですが、MySQL のバージョンアップやデータ量の変化で効果が変わる可能性があります
  • ヒントを多用すると、保守性が下がり、将来的に最適化の妨げになることもあります
  • 可能な限り、インデックス設計やクエリ構造の見直しで対応し、ヒントは最後の手段として使うのが理想的です

オプティマイザヒントを正しく理解し、適切に活用することで、MySQL のパフォーマンスを最大限引き出せるようになります。 ぜひ、実際のプロジェクトで試してみてください。

関連リンク