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

MySQL のクエリが遅い、実行計画が期待通りにならない――そんな経験はありませんか?
実は MySQL には「オプティマイザヒント」という強力な機能があり、特定のクエリだけ実行計画を細かくコントロールできます。
本記事では、実務でよく使われる NO_MERGE
、INDEX
、HASH_JOIN
などの代表的なヒントを実例とともに解説し、すぐに現場で活用できる知識をお届けします。
オプティマイザヒント早見表
よく使われるオプティマイザヒントを、用途別にまとめました。 詳しい使い方は後述の「具体例」セクションをご覧ください。
インデックス制御系
ヒント | 構文例 | 用途 | 対象バージョン |
---|---|---|---|
INDEX | /*+ INDEX(t1 idx_name) */ | 特定のインデックスを強制使用 | MySQL 5.7+ |
NO_INDEX | /*+ NO_INDEX(t1 idx_name) */ | 特定のインデックスを除外 | MySQL 5.7+ |
FORCE_INDEX | FORCE 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;
主要なオプティマイザヒント一覧
以下は、実務でよく使われるヒントの早見表です。
# | ヒント名 | 用途 | 効果 |
---|---|---|---|
1 | INDEX | 特定のインデックスを強制使用 | 指定したインデックスを優先的に使う |
2 | NO_INDEX | 特定のインデックスを使わない | 指定したインデックスを除外する |
3 | NO_MERGE | サブクエリをマージしない | 一時テーブルとして独立実行 |
4 | MERGE | サブクエリを外側にマージ | サブクエリを展開して最適化 |
5 | HASH_JOIN | ハッシュ結合を使用 | 大量データの結合を高速化(MySQL 8.0+) |
6 | NO_HASH_JOIN | ハッシュ結合を使わない | Nested Loop Join を選択 |
7 | JOIN_ORDER | 結合順序を固定 | 指定した順でテーブルを結合 |
8 | BKA / NO_BKA | Batched 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;
問題点
オプティマイザが products
→ orders
→ users
の順で結合してしまい、不要な中間データが大量に生成されることがあります。
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;
効果
結合順序が users
→ orders
→ products
に固定され、効率的な実行計画になります。
ポイント
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 バージョン |
---|---|---|---|---|
1 | INDEX | 特定インデックスを強制使用 | スキャン行数削減 | 5.7+ |
2 | NO_MERGE | サブクエリを独立実行 | 集計後に結合、効率化 | 5.7+ |
3 | HASH_JOIN | ハッシュ結合を使用 | 大量データ結合高速化 | 8.0.18+ |
4 | JOIN_ORDER | 結合順序を固定 | 効率的な結合順に制御 | 8.0+ |
5 | BKA | Batched 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 オプティマイザヒントは、実行計画を細かくコントロールできる強力な機能です。
本記事では、代表的なヒントである INDEX
、NO_MERGE
、HASH_JOIN
、JOIN_ORDER
、BKA
について、実例とともに解説しました。
活用のポイント
- まず EXPLAIN で診断:ヒントを適用する前に、実行計画を確認して問題箇所を特定しましょう
- ピンポイントで適用:すべてのクエリにヒントを入れるのではなく、問題のあるクエリにのみ適用します
- 統計情報の更新を忘れずに:
ANALYZE TABLE
で統計を最新化すれば、ヒントなしで改善することもあります - 効果を検証:ヒント適用前後で実行時間を計測し、本当に改善しているか確認しましょう
注意点
- オプティマイザヒントは強力ですが、MySQL のバージョンアップやデータ量の変化で効果が変わる可能性があります
- ヒントを多用すると、保守性が下がり、将来的に最適化の妨げになることもあります
- 可能な限り、インデックス設計やクエリ構造の見直しで対応し、ヒントは最後の手段として使うのが理想的です
オプティマイザヒントを正しく理解し、適切に活用することで、MySQL のパフォーマンスを最大限引き出せるようになります。 ぜひ、実際のプロジェクトで試してみてください。
関連リンク
- article
MySQL オプティマイザヒント早見表:/\*+ NO_MERGE, INDEX, HASH_JOIN \*/ 実例集
- article
MySQL Shell(mysqlsh)入門:AdminAPI で InnoDB Cluster を最短構築
- article
MySQL Optimizer Hints 実測比較:INDEX_MERGE/NO_RANGE_OPTIMIZATION ほか
- article
MySQL ロック待ち・タイムアウトの解決:SHOW ENGINE INNODB STATUS の読み解き方
- article
MySQL オプティマイザ概説:実行計画が決まるまでの舞台裏
- article
MySQL 基本操作徹底解説:SELECT/INSERT/UPDATE/DELETE の正しい書き方
- article
Vitest `vi` API 技術チートシート:`mock` / `fn` / `spyOn` / `advanceTimersByTime` 一覧
- article
Pinia ストア分割テンプレ集:domain/ui/session の三層パターン
- article
Obsidian Markdown 拡張チートシート:Callout/埋め込み/内部リンク完全網羅
- article
Micro Frontends 設計:`vite-plugin-federation` で分割可能な UI を構築
- article
TypeScript 公開 API の型設計術:`export type`/`interface`/`class`の責務分担と境界設計
- article
Nuxt nuxi コマンド速見表:プロジェクト作成からモジュール公開まで
- blog
iPhone 17シリーズの発表!全モデルiPhone 16から進化したポイントを見やすく整理
- blog
Googleストアから訂正案内!Pixel 10ポイント有効期限「1年」表示は誤りだった
- blog
【2025年8月】Googleストア「ストアポイント」は1年表記はミス?2年ルールとの整合性を検証
- blog
Googleストアの注文キャンセルはなぜ起きる?Pixel 10購入前に知るべき注意点
- blog
Pixcel 10シリーズの発表!全モデル Pixcel 9 から進化したポイントを見やすく整理
- blog
フロントエンドエンジニアの成長戦略:コーチングで最速スキルアップする方法
- review
今の自分に満足していますか?『持たざる者の逆襲 まだ何者でもない君へ』溝口勇児
- review
ついに語られた業界の裏側!『フジテレビの正体』堀江貴文が描くテレビ局の本当の姿
- review
愛する勇気を持てば人生が変わる!『幸せになる勇気』岸見一郎・古賀史健のアドラー実践編で真の幸福を手に入れる
- review
週末を変えれば年収も変わる!『世界の一流は「休日」に何をしているのか』越川慎司の一流週末メソッド
- review
新しい自分に会いに行こう!『自分の変え方』村岡大樹の認知科学コーチングで人生リセット
- review
科学革命から AI 時代へ!『サピエンス全史 下巻』ユヴァル・ノア・ハラリが予見する人類の未来