【MySQL】インデックスはどこに付ける？付けるかの判断ポイントと単独・複合インデックスの選び方｜ブログ

テーブルを設計するとき、「どの列にインデックスを付ければよいのか」「単独インデックスと複合インデックスのどちらにすべきか」と迷うことは多いのではないでしょうか。とりあえず検索に使う列すべてに付けておくと、効かないインデックスが増え、書き込みが遅くなる原因になります。 この記事では MySQL（InnoDB）を前提に、インデックスを付けるかどうかの判断と、単独・複合インデックスの選び方を整理します。 インデックスを付けるべきとき インデックスは本の索引のように「この値の行はどこにあるか」を素早く引くための仕組みです。MySQL では、主に次の場面でインデックスが使われます。 WHERE の条件に合う行を探すとき JOIN で、ほかのテーブルから行を取り出すとき 特定の列の MIN()・MAX() を求めるとき ORDER BY・GROUP BY で並べ替えや集約をするとき つまり、インデックスを付ける候補になるのは、頻繁に実行されるクエリの WHERE・JOIN・ORDER BY に使われる列です。考える順番は「この列に付けるか」ではなく「速くしたいクエリはどれで、そのクエリがどの列を条件にしているか」です。 なお、主キーと UNIQUE 制約の列には、インデックスが付きます。また MySQL では、外部キー制約を張ると、参照する側のテーブルに外部キーの列のインデックスが無い場合は自動で作られます。新しく付ける前に、SHOW INDEX FROM テーブル名 で既にあるインデックスを確認しましょう。 インデックスを付けても効果が薄いとき インデックスにはコストもあります。不要なインデックスは記憶領域を無駄にし、どのインデックスを使うかを決める時間も無駄にします。さらに、INSERT・UPDATE・DELETE のたびに各インデックスを更新する必要があるため、書き込みのコストが増えます。次のような場合は、そのコストに見合う効果が出にくくなります。 クエリがテーブルの大半の行を読む 小さなテーブルや、大半の行を処理する集計のクエリでは、インデックスの効果は小さくなります。大半の行を読むなら、インデックスを経由するより先頭から順に読むほうが速いためです。 たとえば「削除済みかどうか」のフラグのように、値が2種類しかなく、ほとんどの行が同じ値の列があります。その値で検索するとほぼ全件が該当するため、この列に単独でインデックスを付けても効果は限られます。 条件の書き方がインデックスに合っていない インデックスがあっても、条件の書き方によっては使われません。 LIKE '%検索語' のように先頭がワイルドカードの検索は、範囲の検索に使えません 複合インデックスの2列目以降だけを条件にした検索は、そのインデックスを使えません（次の章で説明します） 複合インデックスを使うとき 同じクエリの中で、複数の列を同時に条件にするときは、複合インデックスを検討します。 sql SELECT FROM orders WHERE userid = 123 AND status = 'shipped'; userid と status にそれぞれ単独インデックスを付けた場合、MySQL はより多くの行を絞り込めるほうを1つ選んで使うか、複数のインデックスを組み合わせる最適化（Index Merge）を試みます。一方、(userid, status) の複合インデックスがあれば、条件に合う行を直接取り出せます。 また、SELECT で取得する列がすべて1つのインデックスに含まれていれば、テーブル本体を読まずにインデックスだけで結果を返せます。これをカバリングインデックスと呼びます。ただし、列を足すほどインデックスは大きくなり、書き込みのコストも増えます。まずは絞り込みに必要な列で設計しましょう。 複合インデックスの列の順番 先頭の列から順に使われる (col1, col2, col3) の複合インデックスは次の条件の検索に使えます。 | 条件にする列 | インデックスを使えるか | |---|---| | col1 | 使える | | col1 と col2 | 使える | | col1 と col2 と col3 | 使える | | col2 だけ、col2 と col3 | 使えない | 電話帳が「姓 → 名」の順で並んでいる場合、名前だけでは探せないのと同じです。このため、(userid, status) の複合インデックスがあれば、userid だけを条件にする検索にもそのまま使えます。userid だけの単独インデックスを別に付ける必要は、多くの場合ありません。逆に、status だけで検索するクエリも頻繁にあるなら、status を先頭にしたインデックスが別に必要です。 等価条件の列を前、範囲条件の列を後ろに置く = で比較する列と、>=・BETWEEN のような範囲で比較する列が混在する場合は、等価条件の列を前に置きます。 sql SELECT FROM orders WHERE status = 'shipped' AND createdat >= '2026-01-01'; このクエリには (status, createdat) の順が適しています。範囲条件に使われた列より後ろの列は、インデックスでの絞り込みに使われないためです。(createdat, status) の順にすると、createdat の範囲で読んだ行を、status で1行ずつふるい落とすことになります。 検索項目が多い画面での選び方 管理画面の一覧のように検索項目がたくさんある場合、すべての組み合わせにインデックスを用意するのは現実的ではありません。 すべての条件をインデックスでカバーしなくてよい MySQL は、使えるインデックスが複数あるとき、通常は最も行を絞り込めるもの1つを選びます。また、インデックスで読んだ行は、そのあと元の WHERE 条件で改めてふるい落とされます。 つまり、インデックスの役割は条件を全部カバーすることではなく読む行数を十分に減らすことです。たとえば「注文番号・ステータス・注文日（期間）・顧客名」で検索できる画面なら、次のように考えます。 それだけで数件まで絞れる項目（ほぼ一意な注文番号など）: 単独インデックスで十分です。ほかの条件が同時に指定されても、数件をふるい落とすだけで済みます それだけでは絞り切れない項目（ステータス・期間など）: 一緒に使われることが多いなら、複合インデックスの候補になります 部分一致で検索する項目（LIKE '%山田%' など）: 先頭がワイルドカードの検索にはインデックスが使えないため、貼っても効きません 「よく使う」は実際のクエリで判断する どの検索を優先するかは、どれだけ遅いかと、どれだけ実行されるかで判断します。 稼働中のシステムなら、スロークエリログで遅いクエリを洗い出せます。スロークエリログは実行に longquerytime 秒（既定値は10秒）以上かかったクエリを記録します。logqueriesnotusingindexes を有効にすると、インデックスを使わなかったクエリも記録されます。記録したログは mysqldumpslow で集計できます。 これから作るシステムなら、画面の仕様から考えます。一覧画面を開いたときの既定の表示（絞り込みなし・既定の並び順）は、毎回必ず実行されます。公開状態や削除済みかどうかのように毎回必ず付く条件も、必ず使われる列です。任意の検索項目は遅いと分かったものから足していく進め方でも十分です。 複合インデックスはどこまで複合するか InnoDB では、複合インデックスに含められる列は16列までです。ただし、上限まで足しても意味はありません。前の章で見たとおり、絞り込みに効くのは次の列までです。 先頭に、= で比較する列（毎回必ず付く条件を前に置く） その後ろに、範囲条件の列か ORDER BY の列を1つ 範囲条件より後ろの列は絞り込みに使われず、WHERE にも ORDER BY にも出てこない列を足しても、インデックスが大きくなって書き込みが重くなるだけです。例外は取得する列まで含めてカバリングインデックスにする場合です。 貼るのをためらうときは試して確かめる 「下手に貼って遅くなるくらいなら貼らない」という判断もありえますが、貼らない場合も、データが増えるにつれて毎回テーブル全体を読むようになります。行数が少ないうちは問題が見えにくい点に注意が必要です。 インデックスは貼ったあとに EXPLAIN で使われているかを確かめ、使われていなければ削除できます。また MySQL 8 には、インデックスを削除せずにオプティマイザから見えなくする不可視インデックスがあります。 sql ALTER TABLE orders ALTER INDEX idxstatuscreatedat INVISIBLE; ALTER TABLE orders ALTER INDEX idxstatuscreatedat VISIBLE; 大きなテーブルではインデックスの削除と再作成にコストがかかるため、消した場合の影響を先に確かめるのに役立ちます。ただし、不可視にしても書き込み時のインデックスの更新は続くため、確かめられるのは読み込みへの影響です。なお、MariaDB では同様の機能が「IGNORED」として 10.6 から提供されています。 EXPLAIN で効いているかを確かめる インデックスが実際に使われているかは、クエリの前に EXPLAIN を付けて確認します。主に見る項目は次のとおりです。 | 項目 | 見るポイント | |---|---| | type | ALL はテーブル全体を読んでいる。ref・range・const などなら、インデックスで行を絞り込めている | | key | 実際に使われたインデックス。NULL なら使われていない | | rows | 読むと見込まれる行数。InnoDB では推定値 | | Extra | Using filesort は並べ替えの処理が別に発生している。Using index はインデックスだけで結果を返せている | テスト用の少ないデータでは、インデックスがあってもテーブル全体を読むほうが選ばれることがあります。本番に近い件数のデータで確認しましょう。 まとめ インデックスは「速くしたいクエリ」から考え、頻繁に実行されるクエリの WHERE・JOIN・ORDER BY に使われる列に付ける 大半の行が該当する条件や、インデックスに合わない条件の書き方では、効果が薄い。書き込みのコストも考えて判断する 同じクエリで一緒に条件にする列は、複合インデックスにまとめる 複合インデックスは先頭の列から順に使われる。等価条件の列を前、範囲条件の列を後ろに置く 検索項目が多い画面では、すべての組み合わせをカバーしようとせず、行を大きく減らせる項目と、実際に遅く頻繁に実行されるクエリから選ぶ 付けたら EXPLAIN で、使われているか、読む行が減っているかを確かめる まずは普段書いているクエリに EXPLAIN を付けて、インデックスが使われているかを確かめるところから始めてみてください。 なお、リヴァイトではシステム開発のご相談を承っています。お気軽にお問い合わせください。