『High Performance MySQL 第4版』を読んだ内容を章ごとにまとめています。

第8章クエリパフォーマンス最適化

概要
クエリが遅いのはなぜ？
遅いクエリの基本：データアクセスを最適化する
1. DBに「不要なデータ」を要求していないか？
  1. 典型的なミス
2. MySQL が「必要以上に行を調べていないか？」
クエリを書き換える方法
クエリ実行の基礎
MySQLクエリオプティマイザの限界
特定クエリタイプの最適化
まとめ
業務での活かし方

概要

この章は、スキーマ設計・インデックス設計に加えて「クエリそのものの設計/書き方」を最適化の方法として挙げている。
主眼は「応答時間を短くすること」で、そのために MySQL がクエリを パース → 最適化 → 実行 → 結果返却 する内部プロセスを理解し、必要に応じて 実行計画（実行プラン）を変える/変わるようにクエリを書く ことを狙う。

クエリが遅いのはなぜ？

クエリは1つの作業だが、実際には複数のサブタスクの集合で、それぞれが時間を消費する。

最適化の基本方針は「サブタスクをなくす」「回数を減らす」「1回あたりを速くする」。
クエリの時間は、ネットワーク、CPU、統計/計画、ロック待ち、そして（特に）ストレージエンジンへの行取得呼び出しで消費される。
行取得は、メモリ操作/CPU操作に加えて、データがメモリに無い場合は I/O が支配的になりやすい。

遅いクエリの基本：データアクセスを最適化する

この章では、遅いクエリを次の2段階で見直すのが有効だとしている。

1) アプリが「不要なデータ」を取りに行っていないか（行が多すぎる/列が多すぎる）
2) MySQL が「不要な行」を調べていないか（調べた行数が大きすぎる）

DBに「不要なデータ」を要求していないか？

余計なデータ取得は、MySQL サーバの余計な仕事・ネットワーク転送・アプリ側メモリ/CPUの消費につながる。

典型的なミス

必要以上の行を取る
MySQL は「必要な分だけオンデマンドで返して止まる」わけではない（クライアントが先頭N行だけ欲しくても、サーバは結果セットを生成し続ける）。
LIMIT が重要。
複数テーブルJOINで全部の列を取る
例：SELECT * で3テーブル分の全列を返すより、必要なテーブル/列だけ返す（例：sakila.actor.* だけなど）。
SELECT * を安易に使う
返す列が増えると I/O/メモリ/CPU が増えるだけでなく、カバリングインデックスのような最適化の余地も狭まる。
ただし、開発の単純化やアプリ側キャッシュ（オブジェクト単位のキャッシュ）などの意図があるなら「コストを理解した上で」許容される場合もある。
同じデータを何度も取りに行く
同じクエリを実行してデータベース・サーバーから同じデータを繰り返し取得するのではなく、最初に取得した時にキャッシュし、それ以降は再利用する。

MySQL が「必要以上に行を調べていないか？」

「返した行」の少なさに対して「調べた行」が多いクエリを見つけるのが出発点。

まず見る3つのコスト指標

応答時間
調べた行数
返した行数

これらは完璧な指標ではないが、スロークエリログに載るため「調べすぎ問題」を見つけるのに有効。

応答時間

応答時間 = サービス時間 + 待ち時間
サービス時間: サーバが実際にクエリを処理するのにかかる時間
待ち時間: I/O 待ちやロック待ちなどの「実行していない時間」。
負荷状況・同時実行・ロック・ハードウェアに影響され、応答時間は一定ではない。

調べた行数と返した行数

理想は rows examined ≒ rows returned だが、JOIN では1行の結果生成に複数行アクセスが必要なので、通常は一致しない。
比率が 1:1〜10:1 程度ならまだしも、桁違いに大きいと「無駄に調べている」可能性が高い。
ただし行アクセスは均一ではない（行が短いほど速い/メモリかディスクかで差が大きい）ため、万能ではない。

調べた行数とアクセス方式

EXPLAIN の type（アクセス方式）は「そのテーブルから行を見つけるために、どんなアクセス方法を使うか」を表す。
本文では「フルスキャン→インデックススキャン→レンジ→ユニーク参照→定数」といった“概念”で説明しており、type の値は列挙していないため、ここから先は補足として代表的な値を挙げる。
概ね速い→遅い順（状況次第で例外はある）
- system（システムテーブル：常に1行、ほぼ定数扱い）
- const（定数参照：主キー/ユニークインデックスで1行に決まる）
- eq_ref（一意参照：JOINの各行に対して主キー/ユニークで1行を引く）
- ref（インデックス参照：非一意インデックスで該当行をまとめて引く。本文中の例でも登場）
- fulltext（FULLTEXTインデックスを使う）
- ref_or_null（ref＋NULLも特別扱いで探索する）
- index_merge（複数インデックスを使って結果をマージする）
- unique_subquery（IN (subquery) を一意参照で最適化できる形）
- index_subquery（IN (subquery) をインデックス参照で最適化できる形）
- range（レンジスキャン：インデックスの範囲を読む）
- index（インデックスフルスキャン：インデックスを先頭から末尾まで読む）
- ALL（フルテーブルスキャン）
例外（速度の並びの外側）
- NULL（アクセス対象テーブルが無い/最適化でテーブルが消えた、など）
アクセス方式が悪いときは、たいてい 適切なインデックス追加が第一候補になる。

また、WHERE を適用する場所（=どこで行を捨てられるか）が重要で、良い順に次の3つが挙げられている。
1) インデックス参照時に条件で絞る（ストレージエンジン層）
2) カバリングインデックスで行アクセスを避けつつサーバ層で条件適用（Extra: Using index）
3) 行を読んでからサーバ層で捨てる（Extra: Using where）

クエリを書き換える方法

「同じ結果を別の形で取る」だけでなく、場合によっては アプリ側の処理も変えつつ、返す結果そのものを変える ことで効率化する発想を持つ。

複雑なクエリ / 複数のシンプルなクエリ

伝統的には「できるだけ少ないクエリで」が推奨されてきた（ネットワークやパース/最適化のコストが相対的に重かったため）。
ただし MySQL は接続や小さなクエリに強く、ネットワークも速くなったため、複数クエリに分解するのが有効な場面がある。
一方で「出し過ぎ」は典型的な失敗（10回の単一行クエリで済ませるより、1回で10行取る方が良いなど）。

クエリを分割して実行する

大量削除を例に、巨大な DELETE を一度に流すと、長時間ロック・トランザクションログ肥大・リソース独占・レプリケーション遅延などを招きやすい。

DELETE ... LIMIT 10000 のように 中サイズのチャンクで繰り返し実行する。
必要なら各チャンクの間に sleep を挟んで負荷やロック保持時間を分散する。

JOIN分解

JOINをDBでやり切る代わりに、単一テーブルクエリを複数回発行してアプリで結合する手法。

利点として、次が挙げられている。

テーブル（オブジェクト）単位のキャッシュが効きやすい（キャッシュ命中ならクエリ自体を省略できる/IN()リストを減らせる）。
ロック競合が減る場合がある。
テーブルをサーバ分割しやすくなる。
IN() の活用などで、MySQL がより効率的に行を取りに行ける場合がある。
JOINは実質的な非正規化で同じ行に何度も触れがちだが、アプリ結合なら行アクセス重複を減らせることがある。

クエリ実行の基礎

高性能を引き出す投資として「MySQLがどう最適化し、どう実行するか」を理解する価値が高い、と明言されている。

クエリの実行経路

1) クライアントがSQLを送る
2) サーバがパース/前処理/最適化して実行計画を作る
3) 実行エンジンが計画に従い、ストレージエンジンAPIを呼ぶ
4) サーバが結果をクライアントへ返す

クライアント/サーバプロトコル

プロトコルは 半二重（half-duplex）：送信と受信を同時にできない通信方式のため、メッセージを途中で打ち切れない。
クエリは1つのパケットで送られるので、巨大クエリでは max_allowed_packet が重要になる。
結果は複数パケットで返る。クライアントは 結果セット全体を受け取るまで 終われない（先頭だけ取って「残り要らない」は基本できない）。
クライアントは「pullしているつもり」でも実際はサーバが行を push しており、止められない（drinking from the fire hose）。

バッファリングとロック/リソース

多くのクライアントライブラリは結果をバッファリングする（デフォルト）。
「バッファリング（buffering）」は、すぐに処理（送受信）しきれないデータを、いったんメモリなどのバッファにためておくこと。
全行をフェッチし終わるまで、サーバはロック等のリソースを解放できない（状態としては Sending data）。
バッファリングしない（非バッファ）と、メモリを節約しつつ早く処理を始められるが、その間サーバ側のロックやリソースが開きっぱなしになる。
例として PHP の mysql_unbuffered_query、Perl DBI の mysql_use_result が挙げられている。

クエリ状態

SHOW FULL PROCESSLIST などでスレッド（接続）の状態を見られる。
代表例：

Sleep: 新しいクエリ待ち
Query: 実行または結果送信中
Locked: サーバレベルのテーブルロック待ち。InnoDB の行ロック待ちはここに出ない。
Analyzing and statistics: 統計情報の確認や最適化
Copying to tmp table [on disk]: 一時テーブルへコピー（GROUP BY、filesort、UNION など）。on disk はメモリ→ディスク変換
Sorting result: ソート中

クエリ最適化プロセス

「SQL文」から「実行エンジンが辿る 実行計画（execution plan）」を作る。
章の説明は理解のために パース → 前処理 → 最適化 に分けているが、内部的には効率のためにまとめて行われることもある。また、構文エラーなどはこの過程のどこでも起きうる。

全体像（入力→出力）

1) SQL（文字列） を受け取る
2) パーサが パースツリー（構造） を作る
3) 前処理で「意味が通っているか」を確定する（存在/曖昧性/権限）
4) オプティマイザが複数の実行方法を比較して 実行計画（指示の木） を選ぶ

ここで作られた実行計画を、後段の実行エンジンが「手続き」として辿って結果を返す。

パーサとプリプロセッサ（まず正しいSQLにする）

パーサ：SQLをトークン化してパースツリーを作り、文法として正しいか検証する。
前処理（preprocessor）：パースツリーに意味付けをしていく。
テーブル/列の存在確認
名前解決（別名、列参照の曖昧性解消）
権限チェック（通常は速いが、権限が膨大だと遅くなりうる）

クエリオプティマイザ（どう実行するのが安いかを決める）

オプティマイザの仕事は「同じ結果が得られる複数の実行方法」を並べて、最も安い（costが低い） と予測した実行計画を選ぶこと。
MySQLは コストベース最適化（cost-based optimizer） を使う。
コストの単位は元々「ランダムな4KBデータページ読み」だったが、現在は WHERE 比較の推定コストなども含めて洗練されてきている。
見積もりは統計（ページ数、カーディナリティ、行/キー長、分布など）に基づく。
キャッシュの効果はコスト計算に織り込まない（すべてディスクI/Oになる前提で計算する）。
観察ポイント：実行してから SHOW STATUS LIKE 'Last_query_cost' を見ると、そのときオプティマイザが見積もったコストが分かる。

「最適でない」計画が選ばれる典型理由（本文の例）

統計が不正確（例：InnoDBはMVCCのため、常に正確な行数統計を維持しない）。
コストモデルが現実と一致しない（順次I/Oが速い、メモリに載っている等を十分に反映できない）。
MySQLの最適（コスト最小）が、人間の最速（実時間最短）と一致しないことがある。
同時実行中の他クエリの影響を考慮しない。
常にコストだけで決めるわけではなく、ルール優先のケースもある（例：全文検索ならFULLTEXTインデックスがあれば使う、など）。
ストアド関数やUDF（User-Defined Function: MySQL本体に標準で用意されていない処理を、ユーザーが追加してSQLから呼べるようにした関数）など、オプティマイザが把握できないコストがある。
すべての候補計画を列挙できず、最適計画を見逃すことがある。

静的最適化と動的最適化（いつ確定できるかが違う）

静的（static）：パースツリーだけを見て確定できる最適化。値に依存せず、再実行しても常に正しい（コンパイル時最適化のイメージ）。
具体例
- 代数変形で WHERE を同値な形に書き換えて簡約する：(5=5 AND a>5) → a>5
- 定数の畳み込み/簡約：(a<b AND b=c) AND a=5 → b>5 AND b=c AND a=5（条件の伝播＋正規化）
- 不可能条件や定数条件の除去：実行前に「結果が必ず空」と判断できると、EXPLAIN の Extra に Impossible WHERE が出る
- MIN() / MAX() の最適化：B-treeインデックスの先頭/末尾行だけ読める場合、最適化段階で値を定数扱いにでき、EXPLAIN に Select tables optimized away が出ることがある
動的（dynamic）：定数値や統計など コンテキストに依存し、実行のたびに評価が必要（実行時最適化のイメージ）。
具体例
- WHERE の「定数値」が変わる：同じSQLでも WHERE col = 1 と WHERE col = 999999 で、ヒット件数が変わり「どのインデックスを使う/フルスキャンする」などの判断が変わりうる
- インデックスの行数（統計）が変わる：データ量の増減や統計更新によって、range で読むか、別のアクセス方法にするか、JOIN順序をどうするか、などの判断が変わりうる
prepared statement や stored procedure では、この差が重要になり、状況によっては実行中に再最適化されることもある。

MySQLが行う最適化の例

JOIN順序の入れ替え（多テーブルでは最重要級：どの表を外側ループにするかで、後続の参照回数が激変する）。
OUTER JOIN → INNER JOIN に変換できる場合の書き換え。
代数変形と簡約（例：(5=5 AND a>5) → a>5）。
COUNT() / MIN() / MAX() などの最適化（インデックスとNULL可否によっては表を読まずに済む形まで落ちることがある）。
カバリングインデックスの活用。
サブクエリをより効率的な形へ変換できる場合がある。
早期終了（LIMIT、Impossible WHERE、NOT EXISTS/DISTINCT系の「見つかったら打ち切り」など）。
等価伝播（等しい列に WHERE 条件を伝播して、別テーブル側でも絞り込めるようにする）。
例：USING(film_id) などで列が等しいなら、WHERE film.film_id > 500 は film_actor.film_id > 500 にも効くので、両方に重複して書く必要はない。
IN() リストをソートして二分探索（OR の羅列より計算量的に有利）。

先回りしてオプティマイザを出し抜こうとしない（可読性を下げるだけで、最適化を邪魔しがち）。必要ならヒントやクエリ書き換え、スキーマ/インデックス見直しで「助ける」のが選択肢。

テーブル/インデックス統計（オプティマイザの材料）

サーバ層は統計を自前で保持せず、ストレージエンジンから統計（ページ数、カーディナリティ、分布など）を受け取って最適化に使う。

MySQLのJOIN実行戦略（計画どおりにどう回すか）

まず前提として、MySQLは join を広義に扱い、2テーブル以上の結合だけでなく「すべてのクエリをJOIN」とみなす（サブクエリ、単一テーブルの SELECT、UNION も含む）。
例：UNION は「各 SELECT を実行 → 結果を一時テーブルへ書き出し（spool） → 一時テーブルから読み出し」という流れで処理され、MySQLの用語ではこの一連の各ステップも join として扱われる。
伝統的な実行の考え方は ネステッドループJOIN：
1) まず「先頭のテーブル」から1行取る（外側ループ）
2) 次のテーブルで、その行にマッチする行を探す（内側ループ）
3) これをテーブル数ぶん繰り返し、全テーブルでマッチが揃ったら SELECT 句に指定した列で「結果行」を作って返す
4) 次の結果行を作るために「最後のテーブルで次のマッチ行」を探し、無ければ1つ前のテーブルへ戻って（バックトラックして）次の行を探す…という形で進む
つまり MySQL は「まず1つのテーブルから始めて、次のテーブルでマッチを探す」を繰り返すため、実行の形（=実行計画の形）も left-deep tree（左に深い形） になりやすい。
MySQL 8.0.20以降：block nested-loop join は使われなくなり、代わりに ハッシュJOIN が使われる（片側のデータセットがメモリに載る場合などで特に有利）。

実行計画

実行計画は「SQLをどういう順番で、どのインデックスを使って、どんな手順で処理するか」をまとめた 設計図。MySQLは多くのDBのように“バイトコード”を生成するというより、実行エンジンが参照できる 指示の構造（ツリー） を作って、それを辿って結果を作る。
木（ツリー）と言っても、イメージは「処理の手順書」。
例：A JOIN B JOIN C なら、「まずAから行を取り、Aの1行ごとにBのマッチを探し、そこまで揃った行ごとにCを探す」…のように、1つずつ順にたどる流れになる。
その結果、MySQLの多テーブルJOINの計画は left-deep tree（左に深い形） になりやすい（どこかの中間結果をまとめて作ってから、別枝と合流するようなバランス木ではなく、「先頭テーブル→次→次…」の鎖に近い形）。
観察ポイント：EXPLAIN で計画の概要（どの表から始めるか、どのインデックスで読むか、ソート/一時テーブルが要るか等）を確認できる。
EXPLAIN EXTENDED のあと SHOW WARNINGS で、オプティマイザが再構成したクエリ（意味は同じだがテキストは変わりうる）を見られる。
EXPLAIN FORMAT=TREE を使うと、計画を“木”として眺めやすい（本文の脚注で触れられている）。

JOINオプティマイザ（どの順に結合するかを選ぶ）

多テーブルでは「どの表から始めるか」「次にどの表を読むか」を決め、コストが低い計画を選ぶ。
本文の EXPLAIN 例では、actor を先頭にすることで「後続テーブルへのインデックス参照回数（プローブ回数）」を減らせることが示される。
STRAIGHT_JOIN で順序固定はできるが、通常はオプティマイザに任せたほうが良い（例外は稀）。
JOIN順序の探索空間は n! と急増し、テーブル数が多いと総当たりが不可能になる。
optimizer_search_depth を超えると貪欲探索などの近道（ヒューリスティクス）に頼る。
LEFT JOIN や相関サブクエリなど、順序変更できない要素は探索空間を減らす。

ソート最適化（ORDER BYをどう捌くか）

まず押さえること：MySQLが ORDER BY の並びを インデックスだけで作れないとき、行を自前で並べ替える必要があり、その処理をまとめて filesort と呼ぶ（実ファイルを使わなくてもこの名前）。
filesort は「メモリで完結するか」で挙動が分かれる。
sort buffer に収まる：メモリ上で quicksort
収まらない：値を チャンクに分けてソート→マージ（ディスクを使うことがある）
filesort のアルゴリズムは2種類（どちらも速さ vs 使うメモリ/一時領域のトレードオフ）。
2パス（two-pass）：ORDER BY 用の値＋行ポインタをソート→並び順に行を読み直して出力（表を2回読む。2回目がランダムI/Oになりやすく高コスト）。
1パス（one-pass）：必要な列をまとめて読み、ORDER BY でソートしてそのまま出力（読み直しを避けやすいが、ソート対象の1行ぶんデータが大きくなりやすい）。
「filesort の一時領域が異常に膨らむ」理由（本文の注意点）
ソート用レコードが 固定サイズで確保され、VARCHAR は「最大長」を入れられるサイズになる。
utf8mb4 は 1文字=4バイトで見積もるため、スキーマ次第で一時領域がテーブルサイズを大きく上回ることがある。
JOIN＋ORDER BY のときは「どのタイミングで filesort するか」が2パターンある（EXPLAIN の Extra がヒント）。
ORDER BY が JOIN順序の最初のテーブルの列だけ：最初のテーブルを先に filesort してから JOIN でき、Extra は Using filesort
それ以外（最初じゃないテーブルの列、複数テーブルの列）：JOIN結果を 一時テーブルにためてから filesort が必要になり、Extra は Using temporary; Using filesort
- LIMIT があっても filesort のあとに適用されるため、一時テーブル/ソート対象が非常に大きくなりうる

実行エンジン

実行段階は、実行計画に従って粛々と進む。
多くの処理はストレージエンジンの handler API を呼ぶ（テーブル出現ごとに handler インスタンスを作る）。
代表的には「インデックスの先頭行を読む」「次行を読む」など少数の基本操作の組み合わせで多くのクエリを実行する。

結果の返却

MySQL は結果行を 増分に生成して即送る（行をメモリに溜めずに済み、クライアントも早く受け取れる）。
行はプロトコル上は1行1パケットで送られる。TCP層でまとめられることはある。

MySQLクエリオプティマイザの限界

UNION の制約

UNION の外側にある条件を、内側へ十分に押し下げできないことがある。
外側で LIMIT するだけだと、各 SELECT の全結果を一時テーブルに入れてから先頭だけ取る、になりやすい。
対策として、各 SELECT にも冗長に LIMIT/ORDER BY を書き、先に絞ってから UNION する。
さらに、一時テーブルから取り出す順序は未定義なので、最終 LIMIT の直前に全体 ORDER BY を置くべき、という指摘がある。

等価伝播の副作用

巨大な IN() リストが、等価条件（WHERE/ON/USING）で関連する複数テーブルへコピーされ、最適化/実行が遅くなることがある。
この問題に対する組み込みの回避策は無く、必要ならアプリ側（クエリ生成側）を変える。

具体例（イメージ）

SELECT o.order_id, o.customer_id, c.status
FROM orders AS o
JOIN customers AS c USING (customer_id)
WHERE o.customer_id IN (1, 2, 3, ... , 50000);

USING(customer_id) によって o.customer_id = c.customer_id が保証されるため、MySQLは IN() リストを c.customer_id 側にも共有できる（=コピーして適用できる）と判断する。
これは通常は「どちらのテーブル側で IN() 判定するか」の選択肢が増えて有利だが、リストが巨大だとコピーや評価コストが増え、最適化/実行が遅くなることがある。

並列実行

MySQL は 単一クエリを複数CPUで並列実行できない（他DBにはある機能だがMySQLにはない）。

同一テーブルを SELECT しながら UPDATE できない

標準SQL的には自然な形でも、MySQL はターゲットテーブルを同時参照する UPDATE を拒否する（エラー 1093）。

回避策：派生テーブルを使って一時テーブルへ実体化させ、UPDATE ... JOIN (SELECT ...) にする。

特定クエリタイプの最適化

このセクションのアドバイスはバージョン依存で、将来はMySQL側で自動最適化される可能性がある、と前置きされている。

COUNT() クエリ

COUNT() がしていること

COUNT(expr) は NULLでない値を数える（値の個数）。
COUNT(*) は行数を数える（* を列に展開せず、列自体を無視して行を数える特殊形）。
行数が欲しいなら 常に COUNT(*) が意図も明確で、性能劣化も避けやすい。

単純な最適化

1回のクエリで複数条件の件数を取る例：

SELECT
  SUM(IF(color = 'blue', 1, 0)) AS blue,
  SUM(IF(color = 'red', 1, 0)) AS red
FROM items;

同等の発想で COUNT(color = 'blue' OR NULL) の形も示されている。

近似を使う

正確さが不要なら EXPLAIN の推定行数（rows）を近似として使うのが有効な場合がある。
例として、30分キャッシュ表示のアクティブユーザ数を「多少ズレてもOK」と割り切り、不要な WHERE 条件や DISTINCT を外して filesort を避けたら高速化した、という話がある。

もう少し複雑な最適化

COUNT() は本質的に「大量の行を数える」ので最適化が難しい。
MySQL内での打ち手は カバリングインデックス程度で、足りなければアプリ構成側（外部キャッシュなど）を見直す必要がある。

JOINクエリ

要点（ハイライト）：

ON/USING 列にインデックスがあることを確認する。
ただし JOIN順序によっては片側だけでよい。使われないインデックスはオーバーヘッドになるため、必要性を考える。
GROUP BY / ORDER BY を1テーブル由来の列に寄せると、インデックスを使える余地が増える。
MySQLのアップグレードでは JOIN 構文や優先順位などの変化に注意（意図せずクロス積になったり、結果が変わったり、構文エラーになりうる）。

GROUP BY ... WITH ROLLUP

WITH ROLLUP は GROUP BY の拡張で、通常のグループ集計に加えて 小計/合計（super aggregation）行も結果に含める（=ロールアップ行を追加する）。
イメージ：GROUP BY dept WITH ROLLUP なら「deptごとの集計行＋全体合計行」が返る。
WITH ROLLUP は十分に最適化されない場合がある。
EXPLAIN で filesort/一時テーブルになっていないか確認し、WITH ROLLUP を外した場合と比較する。
必要ならヒントでグルーピング方法を誘導できる可能性がある。
場合によっては、アプリ側で super aggregation を実装したり、サブクエリ/一時テーブル/UNION で段階的に処理する方が良い。

LIMIT と OFFSET

ページングは ORDER BY と併用されがちで、並び替えを支えるインデックスが無いと filesort が増える。
高い OFFSET（例：LIMIT 10000, 20）は、不要な行を大量生成して捨てるため高コスト。

代表的な改善策：

deferred join：まずカバリングインデックス上で OFFSET を処理して必要な主キーだけ集め、あとから本体表へJOINして必要列を取る。
位置列を事前計算してインデックスし、BETWEEN のレンジスキャンに変換する。
カーソル的に「最後に見たキー」を覚え、OFFSET を使わず WHERE pk < last_pk ORDER BY pk DESC LIMIT N の形で次ページを取る。
事前集計や、主キー＋ORDER BY 用列だけを持つ冗長テーブルへのJOINなども選択肢。

SQL_CALC_FOUND_ROWS

LIMIT 付きのクエリに SQL_CALC_FOUND_ROWS ヒントを付けると、「LIMIT が無ければ何行返ったはずか」を知る目的で使われる（ページングの総件数表示など）。
ただしMySQLは予測で件数を出せないため、実際には LIMIT に達しても止まらず、残りの結果セットを最後まで生成して捨てる 動きになり高コスト。
見積もるのではなく、結局は 全結果を生成して捨てるので高コスト。
代案：
「次へ」リンク方式（LIMIT 21 を取って20件だけ表示し、21件目があれば次ページあり）
多めに取ってキャッシュ（例：1000件）し、ページングをキャッシュから行う
EXPLAIN の rows で概算（「Googleも正確な件数を出さない」的な例え）
別の COUNT(*) を走らせる（カバリングインデックスが効けば SQL_CALC_FOUND_ROWS より速いことがある）

UNION

MySQL は UNION を 一時テーブルに格納してから読み直すことで実行する。
そのため、必要に応じて WHERE/LIMIT/ORDER BY を各 SELECT に手動で押し下げる必要がある。
重複排除が不要なら 必ず UNION ALL。
ALL を省くと一時テーブルが distinct（行全体での一意判定）になり高コスト。
ただし UNION ALL にしても一時テーブル自体は無くならない。

まとめ

クエリ最適化は、スキーマ・インデックス・クエリ設計が噛み合って初めて成立する最後のピース。
最終的には「応答時間」で判断し、どこで時間を消費しているかを 実行の流れ（パース/最適化/実行/返却） から推論できるようになるのがゴール。
最適化の普遍的な方針は、やらない／回数を減らす／速くやる

※MySQL は厳密には「読み取った行数」を最小化しているというより「読み取るページ数」を減らす最適化をしがちで、EXPLAIN の行数はコストの荒い物差しとして使うのが現実的

業務での活かし方

大量JOIN・GROUP BYを扱う集計は、サマリーテーブル化で高速化を検討する
スロークエリを検知したら EXPLAIN で type / rows / Extra を確認し、ボトルネックが「読みすぎ」「一時テーブル」「filesort」なのか当たりを付ける
カバリングインデックスの仕組みを理解する
目的：テーブル行（本体）を読まずに、インデックスだけで WHERE + SELECT を満たして「読む量」を減らす
目印：EXPLAIN の Extra に Using index（=カバリング）
例：一覧画面で SELECT * をやめて必要列だけに絞り、WHERE (絞り込み列) + ORDER BY (並び替え列) + SELECT (表示列) をまとめてカバーする複合インデックスを検討する
例：高い OFFSET があるページングは、まずカバリングで主キーだけ取って（例：SELECT id ... ORDER BY ... LIMIT ...）、その結果にJOINして必要列を取る（deferred join）
index_merge が出たら「本当は複合インデックスが欲しい」サインになり得るので、クエリと合わせて複合インデックス化を検討する
「必要な行/列だけ取る」を徹底する（LIMIT を付ける、SELECT * を安易に使わない）
ページングは OFFSET が大きいほど重くなりやすいので、可能なら「最後に見たキー」を使うカーソル方式（keyset pagination）や deferred join を検討する
SQL_CALC_FOUND_ROWS は「残りも全部生成して捨てる」動きで高コストになりやすいので、次ページ判定（LIMIT 21）や別 COUNT(*)（カバリングできるなら）等に置き換える
ORDER BY は「インデックスで並べられないと filesort」になるため、並び替えが必要な画面はインデックス設計（並び順と一致）から逆算して作る
JOIN＋ORDER BY で EXPLAIN Extra: Using temporary; Using filesort が出ると重くなりやすいので、ORDER BY をJOIN順序の先頭テーブル由来の列に寄せられないか検討する
巨大な IN() リストは、JOIN条件で等価な列があると他テーブル側にもコピーされて最適化/実行が遅くなることがあるため、クエリ生成側で分割・別案を検討する
大量削除/更新は1回でやり切らず、... LIMIT n のチャンクに分ける（ロック保持・ログ・レプリケーション遅延などの悪化を避けやすい）
巨大な結果セットはクライアント側のバッファリングでメモリを食いやすい一方、非バッファにするとサーバ側のロック/リソース解放が遅れやすいので、画面仕様（本当に大量に返す必要があるか）から見直す