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

---

第7章 高パフォーマンスのためのインデックス設計

概要

インデックスはMySQLではキーとも呼ばれる、ストレージエンジンが行を素早く見つけるためのデータ構造で、データ量が増えるほど重要になる。本章は、MySQLでは主にInnoDBを前提に、インデックスの効き方を整理し、実務で効く設計パターンと注意点をまとめる。

• インデックスは性能改善の中でも特に効果が大きく、設計次第で性能が桁違いに変わる。
• 小規模では雑でも動くことがあるが、データ増加とともに差が急激に出る。
• 最適化にはクエリの書き換えが必要になることも多い（次章のクエリ最適化とセットで考える）。
• 例はサンプルDBのSakilaを多用する。

インデックスの基礎

• インデックスは「探したい値 → 該当行へ到達」を速くする仕組み。
• 典型例は主キーやユニークキーでの一点検索。

  SELECT first_name FROM sakila.actor WHERE actor_id = 5;

• 複合インデックスでは列順が重要で、効率よく使えるのは基本的に「左端（先頭）からの一致」だけ。

ORMを使っているときの注意点

• ORM（オブジェクト・リレーショナル・マッピング）は、アプリケーション側のオブジェクト（クラス/構造体）と、RDBのテーブル/行/列を対応付けて扱えるようにする仕組み（またはそのライブラリ）のこと。
• ORMは多くの場合、文法的・論理的に正しいSQLを生成できるが、「インデックスに優しいSQL」を常に生成してくれるわけではない。
• 主キーでの単純な取得のような基本形はともかく、少し複雑な検索になるほど、インデックス設計の細かい前提（複合キーの列順、左端一致、範囲条件の影響など）を外しやすい。
• そのため、ORMを使っていても「インデックスの仕組み」を理解して、生成されたSQLがインデックスを活かせる形になっているかを自分で確認する必要がある。

インデックスの種類

B-tree系（通常のインデックス）

• 多くの場面で使う基本形。値が順序付けて格納される。
• 一点検索だけでなく、前方一致・範囲検索にも強い。
• インデックスの並びとクエリの要求が一致すれば、ORDER BY の高速化にも使える。
• InnoDBには頻出アクセスを見て自動でメモリ上のハッシュを作る仕組み（アダプティブ・ハッシュ・インデックス）がある。

補足：B-treeはどうやってデータを持っているか

• B-treeは「キー（インデックス対象の列の値）」をソートされた状態で保持し、目的の値を速く探せるようにした木構造。
• 大まかには、上の階層ほど「境界値と次に辿る先」を持ち、下の階層ほど「実際のキーと行に辿り着くための情報」を持つ。
  • 上の階層：この値ならこっち、という分岐のための情報
  • 末端の階層（リーフ）：キーが並んでいて、そこから行を取りにいける
• InnoDBでは実装がB+treeで、リーフ側が中心になる。特に次の2点が重要。
  • 主キー（クラスタ化）インデックスのリーフには、キーの並びの中に行データそのものが入る
  • セカンダリインデックスのリーフには、キーに加えて「主キー値」が入る。そこから主キー側を辿って行を取得するため、主キー検索より段階が増えやすい
• キーが順序付けて並ぶので、範囲条件やインデックス順での取り出しに強い、という性質につながる。

よく使える条件の形（複合インデックスの先頭から使えることが前提）

• 先頭列を含む完全一致
• 先頭列の前方一致
• 先頭列の範囲条件
• 先頭列は一致＋次の列は範囲
• 必要な列がインデックス内に揃う（カバリング）場合の「インデックスだけで返す」クエリ

B-treeの典型制約

• 左端列から始まらない条件には効きにくい（2列目だけ、後方一致など）。
• インデックス列を飛ばして使えない（先頭列を指定しないと基本的に先頭列しか使えない）。
• 先頭側で範囲条件が出ると、その右側の列をインデックスアクセス最適化に使いにくい（前方一致の LIKE 'J%' の後ろの列など）。

FULLTEXT（全文検索）

• テキスト内のキーワードを発見する特殊なタイプのインデックス。
• MATCH ... AGAINST のための特殊なインデックスで、通常の WHERE 比較とは別物。
• FULLTEXTがあっても、同じ列にB-treeが不要になるわけではない。

インデックスがもたらす3つの利点

• サーバーが調べるデータ量を減らせる。絞り込みを速くする。
• ソートや一時テーブルを避けられる。並び順を活用できる。
• ランダムなディスク読み取りを、連続したディスク読み取りに寄せられる。範囲で順に読めると強い。

高パフォーマンスのためのインデックス戦略

プレフィックスインデックス（文字列の先頭だけ索引）

• 文字列の先頭N文字だけをインデックス化し、インデックスを小さくする方法。
• 軸は選択性（ユニーク値の多さ）。短くしすぎると同じ値が増えて効きが落ちる。
• 平均だけでなく最悪ケース（偏り）も見る。San や New のように同じ先頭が多いと短いプレフィックスは効きにくい。
• デメリットとして、ORDER BY / GROUP BY に使えない・カバリングに使えない、がある。

例（作成）

ALTER TABLE sakila.city_demo ADD KEY (city(7));

複合インデックスと index_merge

• よくある失敗は「WHEREに出る列に単独インデックスを大量に貼る」。それだけでは強いインデックスになりにくい。
• MySQLには複数インデックスを合成する index_merge があるが、しばしば「本当は複合インデックスが必要」というサインにもなる。
• index_merge はCPU/メモリなどの合成コストやソート/マージを伴い、テーブルスキャンより遅くなることもある。
• EXPLAIN を見て、より良い複合キーを作れるか、クエリを UNION などで書き換えられないかを検討する。

補足：index_merge

• 何をしているか
  • 1つのテーブルに対して「複数のインデックス」をそれぞれ走査し、その結果を後段で合成して必要な行を作る。
  • 合成方法には大きく3パターンある。
    • OR条件：インデックス走査結果の和集合を作る
    • AND条件：インデックス走査結果の共通部分を作る
    • ORとANDが混ざる：和集合と共通部分を組み合わせる
• どうやって気づくか
  • EXPLAIN を見ると、実行計画のどこかに index_merge が出る。
  • Extra に「どのインデックスをどう合成するか」が出ることがある（例えば「union」「intersect」など）。
• 発生したとき
  • うまくいく場面もあるが、多くの場合「本当は複合インデックス1本で処理したい」状況の代替になっている。
  • 合成には、バッファ・ソート・マージが必要になりやすく、CPUとメモリを食う。
  • さらに、各インデックス走査で拾った行をあとで行参照するなら、その行参照がランダムなディスク読み取りになりやすい。
  • この合成コストは、単体でクエリを流していると見えにくく、並行実行時に他クエリを巻き込んで遅くしやすい。
• 対応の考え方
  • ANDで index_merge が出るなら、「必要な列をまとめた複合インデックス」を作れないかが第一候補。
  • ORで index_merge が重いなら、クエリを UNION に書き換える方が安定することがある（それぞれが別インデックスに素直に乗る形に分解する）。
  • どうしても邪魔なら、設定で無効化したり、クエリ側で IGNORE INDEX などを使ってオプティマイザの選択肢を狭める手もある（ただし副作用が出やすいので慎重に）。

列順の決め方（複合キーの並び）

• 結論：列順は「そのインデックスで何をしたいか（絞り込み／並び替え／必要列を揃える）」で決まる。

決め方の手順

• まず「このインデックスで速くしたいクエリ」を1つ決める（頻度が高い／遅い／負荷が高いもの）。
• 次に、そのクエリが何を要求しているかを分解する。
  • 絞り込み：WHERE でどの列を使うか（等価か、範囲か）
  • 並び替え：ORDER BY / GROUP BY / DISTINCT があるか、列順は何か
  • 取得列：SELECT で何を返すか（カバリングにしたいか）
• そのうえで、複合キーの列順を決める（下のルール参照）。

B-tree前提のよく使うルール

• ルール1：等価条件で使う列を先頭側に置くと、効率よく絞り込みやすい。
• ルール2：「範囲条件」が出た列より右側は、インデックスでの絞り込みに使いにくくなる。
  • 例：A = 10 AND B LIKE 'J%' AND C = 3 のとき、(A, B, C) を作っても C はインデックスでの絞り込みに使われにくい。
• ルール3：ORDER BY / GROUP BY / DISTINCT を速くしたいなら、その列順に合わせることが重要（順序が合うとソートを避けられる）。
• ルール4：「選択性が高い列を先頭に」は絞り込みだけが目的のときに役に立つことがあるが、常に最優先ではない。
  • ソートを避けられるか、ランダムなディスク読み取りを減らせるかの方が効くことも多い。
• ルール5：外れ値（ゲスト扱いのユーザーや特殊な管理アカウント等）があると、平均的な見立てが外れる。
  • その場合、インデックスでは救えずアプリ側の特別扱いが必要になることもある。

補足：複合インデックスの仕組み

• KEY(a, b, c) は「3列を別々に持つ」のではなく、「a→b→c の順で並べた1本の索引」を作るイメージ。
• B-treeは並び順を持つため、複合インデックスは次のように整列される。
  • まず a で並ぶ
  • a が同じものの中で b で並ぶ
  • a と b が同じものの中で c で並ぶ
• この並び方の結果として「左端一致」が重要になる。
  • KEY(last_name, first_name) があるとき、WHERE last_name='Smith' は使えるが、WHERE first_name='John' のように先頭列を飛ばす形は使いにくい。
• 複合インデックスは、うまく合わせると「絞り込み」と「並び替え」を同じインデックスで同時に満たせる。
  • ORDER BY の列と方向がインデックスの並びと合い、かつ左端から使える形であることが条件。
• 「単独インデックスを複数本」では複合インデックスの代わりにならないことが多い。
  • それぞれのインデックスを別々に読んで結果を合成する必要が出ると、index_merge のような追加コストが発生しやすい。

クラスタ化（主キーとデータ配置）

• クラスタ化とは、テーブルの行データが「あるインデックスのB-tree（特にリーフページ）」に一緒に格納され、キーの近い行が物理的に近くに置かれるようにするデータ配置のこと。
• InnoDBでは主キーでクラスタ化され、行は主キーB-treeのリーフに格納される（別のインデックス種類ではなく、データの置き方の話）。
• 1テーブルに対してクラスタ化できるのは1つだけ（行を2箇所に同時には置けないため）。
• 主キーがない場合は代替（ユニークかつNULL不可）を探し、無ければ隠し主キーを作る。隠し主キーは競合が増える欠点がある。

補足：InnoDBのデータレイアウト

• InnoDBでは「主キーインデックスのリーフに行が入る」ため、テーブルは実質的に主キーB-treeとして保存される。
• 主キー（クラスタ化）インデックスのリーフには、主キー値に加えてトランザクション管理用の情報や、残りの列が一緒に格納される。
• セカンダリインデックス（主キー以外のインデックス）のリーフには「セカンダリのキー値」と「その行の主キー値」が入るため、想像以上に大きくなり得る。
  • そのため、セカンダリで行を見つけても、行本体を読むには主キー側を辿る必要が出やすい（セカンダリ→主キー→行の2段階）。
  • 逆に、セカンダリだけで必要な列が揃う（カバリング）なら、主キー側を読まずに済む。

メリット（なぜ速くなるか）

• 行が主キー順に並んでいるので、主キーが近い行をまとめて取りやすい。うまく設計できると少ないディスク読み取りでたくさんの行が取れる。
• 主キーでの検索は、主キーのB-treeだけで行まで到達できるので速い。
• 主キーの範囲検索も、連続した領域を読む形になりやすく有利。

デメリット・注意点（どこで遅くなるか）

• 効果が大きいのはディスク読み取りがボトルネックのとき。データが全部メモリに乗るなら、並び順の差は小さくなりやすい。
• 挿入順序が重要。主キーが単調増加なら末尾に追記する形になりやすく速いが、主キーがランダムだと途中に割り込む必要が出やすい。
  • 途中割り込みが増えると、ページ分割が起きて断片化し、結果としてディスク使用量やランダムなディスク読み取りが増えやすい。

InnoDBで主キー順に行を挿入する

• 主キーが単調増加だと、クラスタ化インデックスの末尾に追記していく形になりやすく、挿入が速い。
  • そのため、特別な理由がなければアプリの意味とは切り離した代理キー（AUTO_INCREMENT など）を主キーにする選択が有効になることがある。
• 主キーがランダムだと途中に割り込む必要が出やすく、目的のページがキャッシュに無いとディスク読み取りが増え、ページ分割や断片化が起きやすい。
• ランダムな主キーで大量に入れた後は、再構築（OPTIMIZE TABLE など）で詰め直した方が良い場合がある。
• 例：ベンチマーク
  • UUID主キーのテーブルは、連番主キーのテーブルより挿入が遅く、インデックスサイズも大きくなった。
    • 100万行：userinfo 137秒 / 342MB、userinfo_uuid 180秒 / 544MB
    • 300万行：userinfo 1233秒 / 1036MB、userinfo_uuid 4525秒 / 1707MB

高並行時の注意

• 連番主キー（AUTO_INCREMENT など）は新しい行が「常に主キーインデックスの末尾（いちばん右側）」に入るため、同時にINSERTが多いと全員が同じ末尾ページを更新しにいき、そこで待ちや競合が起きやすい（ホットスポットになり得る）。
  • 対策の検討には、AUTO_INCREMENT 周りのロック設定や、書き込みのさせ方（バッチ化など）も含まれる。

カバリングインデックス

• 「WHEREに使う列」だけでなく、「取得する列」まで含めてインデックス設計する発想。
• 行を引かずにインデックスだけで返せると、読むデータ量が減って大きく速くなる。
• EXPLAIN の Extra に Using index が出るのが目印。
• InnoDBではセカンダリに主キーが含まれるため、主キー列は実質的に追加でカバーできることがある。

インデックスを使ってソートする

• MySQLは「ソートする」か「インデックスを順に読む」かで並び替えを実現する。
• インデックス順に並べるには条件が厳しい。ORDER BY の列と方向がインデックスの並びと合う、左端から使える、JOIN時は先頭テーブルの列、など。
• 先頭側の列が WHERE などで定数に固定されると、後続列での並び替えに使えることがある。
• ORDER BY ... LIMIT ... のようなクエリでは特に重要な最適化ポイントになる。

重複インデックス

• まったく同じ列・同じ順序・同じ種類のインデックスを複数作ること。MySQLは警告だけで作れてしまう。
• 読みが速くなることは基本なく、書き込み時の更新回数が増えるだけなので、容量も更新コストも無駄になりやすい。

冗長インデックス

• すでにある複合インデックスの「左端プレフィックス」として機能が被るインデックス。
  • 例：KEY(A,B) があるなら KEY(A) は多くの場合は冗長（A で探す用途は KEY(A,B) で代替できるため）。
• ただし冗長＝必ず削除、ではない。クエリの要求が違うと、あえて2本残した方が良いことがある。
  • 例：COUNT(*) のように軽いクエリは単列が速い一方、複数列を返すクエリは複合＋カバリングが効く、などのトレードオフが出る。

未使用インデックス

• 実際のワークロードで参照されていないインデックス。読み性能には寄与せず、書き込み更新と容量だけ増やすので「死荷重」になりやすい。

削除・統合の注意点

• インデックスを増やすほど INSERT/UPDATE/DELETE は遅くなる（更新する索引が増えるため）。
• InnoDBではセカンダリのリーフに主キーが付くので、インデックスの拡張・削除で ORDER BY の効き方が変わり、filesort が発生するなどの副作用が出ることがある。
  • filesort はMySQLの用語で、ORDER BY をインデックス順に処理できず、別途「並び替え処理」が走ることを指す（名前に反して必ずディスクに書くわけではないが、CPU/メモリや一時領域の負荷が増えやすい）。
• MySQL 8.0 の不可視インデックスを使うと、「削除せずに無効化」して影響確認できる。
  • 不可視にすると、インデックス自体は維持されるが、通常のクエリではオプティマイザが実行計画の候補にしない。
  • 問題が出たら可視に戻せるので、「本当に消してよいか」の安全な検証ステップとして使える。

未使用インデックスの特定例

SELECT * FROM sys.schema_unused_indexes;

インデックスとテーブルのメンテナンス

破損への対応

• 怪しい挙動があれば CHECK TABLE で確認する。
• エンジンによっては REPAIR TABLE が使えるが、使えない場合は no-op の ALTER TABLE（同じエンジンに変更）で再構築できることがある。
• InnoDBの破損は重大で、ハード故障や「DBファイルを外部から触る」運用ミスを強く疑う。

例（再構築）

ALTER TABLE <table> ENGINE=INNODB;

インデックスの統計を更新

• MySQLのオプティマイザは「どのインデックスを使うか」「どの順番でJOINするか」などを決めるときに、インデックス統計（どれくらい絞れるかの見積もり）を使う。
• 統計が古い／偏っていると、見積もりが外れて実行計画が悪化し、急に遅くなる原因になる。

更新の基本

• ANALYZE TABLE は統計を再生成するコマンドで、統計が原因だと疑われるときの基本的な対処になる。
• インデックスの状態は SHOW INDEX で確認でき、特に Cardinality（ユニーク値数の推定）は「そのインデックスがどれくらい絞れそうか」を見る材料になる。

InnoDB統計の特徴（なぜズレることがあるか）

• InnoDBの統計は「インデックスの一部ページをサンプリングして推定」する方式なので、データ分布が偏っていると推定が外れやすい。
• サンプルページ数は設定で調整余地があり、増やすと推定が安定する可能性がある一方、計算コストが増える。

運用上の注意

• SHOW INDEX などのメタデータ参照や、クライアントの補完機能が統計更新（サンプリング）を誘発し得る。
• 大規模環境では、こうした統計更新がロックやディスク読み取りの負荷要因になることがあるため、必要なら設定で抑制する。

断片化

• 断片化は「本来なら連続して詰まっているはずのデータ/インデックスが、スカスカになったり、ディスク上で飛び飛びに配置されたりして、読む量や移動が増える状態」のこと。

B-treeインデックスの断片化

• インデックスのページがスカスカになったり、リーフページがディスク上で非連続になると、範囲スキャンやフルインデックススキャンが遅くなり得る。
• 特に、インデックスだけで返す（カバリング）クエリは「インデックスをたくさん読む」ので、インデックス断片化の影響を受けやすい。

データ（行）の断片化（3種類）

• 行の断片化：1行が複数の場所に分割して格納される状態。単一行を読むだけでも余計な読みが増えることがある。
• 物理配置の非連続：論理的に連続しているはずのページ/行がディスク上で連続していない状態。フルスキャンや主キー範囲スキャンが遅くなり得る。
• 空き領域の断片化：ページ内の空きが多く、同じ行数でも読むページ数が増えてしまう状態。

なぜ起きるか（代表例）

• 更新で行が大きくなったり、途中に割り込む挿入が多いと、ページ分割が起きてスカスカや飛び飛びになりやすい。

対策

• 対策は基本的に「作り直して詰め直す」。
  • OPTIMIZE TABLE
  • ダンプ＆リロード
  • OPTIMIZE TABLE 非対応なら no-op ALTER TABLE で再構築

例（再構築）

ALTER TABLE <table> ENGINE=<engine>;

まとめ

• インデックスは「行を見つけるための索引」であると同時に、「読む量」「並び替え」「行参照の回数」を減らすための道具。
• B-tree系インデックスはキーが順序付けて保持されるため、一点検索だけでなく、前方一致・範囲検索・インデックス順の取り出しに強い。
• 複合インデックスは列順がすべてを決める。基本は左端から使える形に揃え、途中で範囲条件が入ると右側列が効きにくくなる点に注意する。
• 「選択性が高い列を先頭に」は有効な場面もあるが、ソート回避やランダムなディスク読み取り削減の方が効くことも多い。外れ値があると経験則は崩れる。
• プレフィックスインデックスは索引を小さくできるが、選択性が落ちたり、ORDER BY/GROUP BY/カバリングに使えないなどの制約がある。
• カバリングは「行を読まずにインデックスだけで返す」最強の形で、EXPLAIN の Using index はその目印になり得る（ただし読む件数が多ければ重い）。
• index_merge は複数インデックスの合成で救えることもあるが、合成コストが出やすく、「本当は複合インデックスかUNIONなどのクエリ分解が必要」というサインになることも多い。
• InnoDBは主キーでクラスタ化される。主キーが単調増加だと挿入が速い一方、高並行では末尾がホットスポットになり得る。ランダム主キーはページ分割・断片化・索引肥大を招きやすい。
• 重複・冗長・未使用インデックスは、読み性能に寄与しないのに書き込みコストと容量を増やしやすいので整理する（削除前は不可視インデックスで安全に検証できる）。
• 統計のズレや断片化は実行計画やスキャン性能に効く。必要に応じて統計更新や再構築（詰め直し）を行う。

業務での活かし方

• 遅いクエリを特定し、EXPLAIN で「どれだけ読んでいるか」「ソートが発生していないか」「カバリングできているか」を確認する。
• インデックスは「絞り込み」か「ソート回避」か「カバリング」か、狙いを決めて設計する。
• index_merge が出るなら、複合インデックス化や UNION 分解を検討する。
• 主キー設計はクラスタ化に直結するので、挿入パターン（単調増加かランダムか）を意識する。
• インデックスを増やすほど INSERT/UPDATE/DELETE は遅くなることを意識する。重複・冗長・未使用を整理し、削除前は不可視インデックスで影響確認する。
• ANALYZE TABLE は統計のズレで実行計画が不自然なときの見直しに使う。
• OPTIMIZE TABLE は断片化でスキャンが重いときに再構築して詰め直す選択肢として使う。