はじめに
「MySQLのクエリが遅いけれど、何が原因なのか分からない…」
「インデックスを追加したいけれど、どこに設定すればいいの？」
そんなふうに悩んでいませんか。
MySQLは、データ量が少ないうちは問題なく動いていても、件数が増えた途端に急に遅くなることがあります。
ただ、インデックスはやみくもに追加すれば速くなるわけではなく、付け方によっては逆に処理が重くなることもあります。
この記事では、MySQLのインデックス最適化について、まず何を確認すればいいのか、どのカラムに設定すればよいのか、遅いクエリをどう改善していけばよいのかを、順を追ってやさしく整理していきます。
読み終わるころには、迷わず見直せる状態を目指せます。
MySQLのインデックス最適化とは？
MySQLにおけるインデックス最適化とは、データベースの検索処理を効率化し、クエリの実行速度を向上させるための重要な手法です。
しかし、インデックスは単に作ればよいわけではなく、設計や使い方を誤るとかえってパフォーマンスを低下させる原因にもなります。
ここでは、なぜインデックスによって処理速度に差が生まれるのかという基本的な仕組みと、その最適化において押さえておくべき考え方について解説します。
インデックス最適化の目的
インデックス最適化は、「必要な行だけを、できるだけ少ない回数で読み込める状態」にするために行います。どこを改善したいのかを整理すると、見るべきポイントが分かりやすくなります。
目的①：読む行数を減らす
たとえば、100万行あるテーブルから100件だけ取得したい場合、インデックスがないと100万行すべてを順番に確認します。
一方で、適切なインデックスがあれば、必要な100件付近だけを読み取ればよくなるため、処理時間を大きく減らせます。
目的②：無駄な比較処理を減らす
インデックスがない場合、MySQLは条件に一致するかどうかを全行で判定します。100万行なら、条件判定も100万回必要です。インデックスがあると、最初から条件に合いそうな範囲だけを確認できるため、比較回数を大幅に減らせます。
目的③：不要なディスク読み込みを防ぐ
値の種類が少ない列にインデックスを付けたり、複合インデックスの順番を間違えたりすると、結局たくさんの行を読むことになります。また、インデックスに含まれていない列をSELECTすると、位置を見つけたあとにテーブル本体を追加で読み直す必要があり、その分だけ遅くなります。
目的④：データが増えても遅くなりにくくする
データ件数が数千件のうちは問題なくても、数十万件、数百万件になると、インデックスの有無で処理時間に大きな差が出ます。最初から適切なインデックスを設定しておくことで、データが増えても表示速度や検索速度を保ちやすくなります。
最適化の基本は「検索条件に一致させること」
インデックスは、検索条件に合った形で作られていないと、うまく使われません。まずは「どの列で検索しているか」を整理し、それに合わせてインデックスを設定することが大切です。
基本①：WHERE句で使う列にインデックスを付ける
たとえば、WHERE user_id = 100 で検索するなら、user_id にインデックスを付けます。
こうすることで、MySQLは必要な行まで直接たどり着けます。インデックスがない場合は、すべての行を1件ずつ確認するため、データ量が増えるほど遅くなります。
基本②：複数条件なら、検索に使う順番で並べる
WHERE user_id = 100 AND status = 1 のように2つの条件を使う場合は、複合インデックスを (user_id, status) の順で作ります。こうすると、user_id で絞り込んだあとに、status までまとめて判定できます。逆に (status, user_id) にすると、user_id の条件が十分に活かされず、余分な行を読むことがあります。
基本③：範囲検索に使う列もインデックスに含める
WHERE created_at >= ‘2024-01-01’ のような日付の範囲検索では、created_at にインデックスを付けることで、必要な期間だけを読み取れるようになります。インデックスがない場合は、過去のデータも含めてすべて確認するため、件数が増えるほど時間がかかります。
基本④：検索条件とインデックスの形をそろえる
インデックスは、「どの列で検索するか」「どの順番で条件を書くか」に合わせて作ることが基本です。検索条件とインデックスの構成が一致していれば、MySQLは必要な行だけを効率よく読み取れるようになります。
MySQLのインデックス最適化で最初に押さえるべき設計ルール
インデックス最適化を効果的に行うためには、やみくもにインデックスを追加するのではなく、クエリの使われ方に基づいた明確なルールを押さえることが重要です。
特に、検索条件や並び替え、テーブル結合など実際の処理内容に応じて適切に設計しなければ、期待した効果が得られないどころかパフォーマンスを悪化させることもあります。
ここでは、実務で意識すべき基本的なインデックス設計のポイントを順に見ていきます。
WHERE句に使うカラムを優先する
インデックスは、まずWHERE句で使っているカラムに付けるのが基本です。
たとえば、WHERE email = 'a@example.com' で1件を探すなら、email にインデックスを付けます。インデックスがあれば、100万行あるテーブルでも、必要な1行まで直接たどり着けます。
逆に、email にインデックスがない場合は、100万行すべてを1件ずつ確認することになり、データが増えるほど遅くなります。
複数条件がある場合でも、WHERE句で使っているカラムにインデックスがなければ、その条件では絞り込めません。まずは、普段よく使うWHERE句を確認し、検索に使っているカラムから優先してインデックスを付けていきましょう。
JOIN・ORDER BYで使うカラムも考慮する
インデックスは、WHERE句だけでなく、JOINやORDER BYで使うカラムにも付けておくと効果があります。
たとえば、ON user_id = user_id でテーブルを結合するなら、両方のテーブルの user_id にインデックスを付けます。インデックスがあれば、一致する行だけを探して結合できますが、ない場合は大量の行を何度も比較することになり、処理が重くなります。
また、ORDER BY created_at のように並び替えをする場合は、created_at にインデックスを付けることで、最初から並んだ順でデータを取り出せます。インデックスがないと、取得したあとに並び替え処理が必要になり、件数が多いほど遅くなります。
検索条件だけでなく、「結合」と「並び替え」でよく使うカラムも、あわせて確認してみましょう。
複合インデックスは順序がすべて
複合インデックスは、カラムの順番どおりに検索条件が使われていないと、うまく効きません。
たとえば、(user_id, created_at) の順でインデックスを作った場合、WHERE user_id = 100 AND created_at = '2024-01-01' のような検索では、両方の条件を使って効率よく絞り込めます。
一方で、WHERE created_at = '2024-01-01' だけでは、先頭の user_id を使っていないため、このインデックスはほとんど使われません。
また、WHERE user_id = 100 AND created_at >= '2024-01-01' のように、先頭が一致条件、その後に範囲条件が続く形も効率よく使えます。
そのため、複合インデックスは「よく使う条件を先頭に置く」「等価条件 → 範囲条件」の順で並べるのが基本です。順番が逆になると、余分な行を読み込む原因になります。
不要なインデックスは逆効果になる
インデックスは多ければ多いほど良いわけではありません。使われていないインデックスが増えると、逆に処理が遅くなることがあります。
たとえば、データを追加するたびに、MySQLは設定されているすべてのインデックスを更新します。インデックスが1本なら1回で済みますが、5本あると5回更新が必要になります。
そのため、INSERTやUPDATEが多いテーブルでは、インデックスを増やしすぎると書き込みが重くなります。
また、インデックスはそれぞれディスク容量も使います。使っていないインデックスを残したままにすると、容量だけが増え、管理もしづらくなります。
そのため、インデックスは「よく使う検索に必要なものだけ」を残すことが大切です。追加するときは、本当にそのクエリで使われるかを確認してから設定しましょう。
MySQLのインデックス設計でやってはいけない失敗例
インデックスはパフォーマンス改善に有効な一方で、設計を誤ると逆に処理速度を低下させる原因にもなります。
特に、実行計画やデータ特性を考慮せずに作成されたインデックスは、期待した効果を発揮しないどころか、更新処理の負荷を増やす要因にもなります。
ここでは、ありがちな失敗例を通して、避けるべきインデックス設計のポイントを整理していきます。
複合インデックスの順序ミス
複合インデックスでよくある失敗が、カラムの順番を逆にしてしまうことです。
たとえば、WHERE user_id = 100 AND status = 1 で検索するなら、インデックスは (user_id, status) の順で作るのが基本です。これなら、まず user_id で対象を絞り込み、その中だけで status を確認できます。
逆に (status, user_id) にすると、先に status = 1 の行を広く集めてから user_id を探すことになり、余分な行まで読み込んでしまいます。
また、先頭のカラムに >= や < などの範囲条件を置くと、その後ろのカラムは効きにくくなります。
複合インデックスは、「実際の検索条件でよく使う順番」に合わせて並べるようにしましょう。
インデックスの貼りすぎによる性能低下
インデックスは検索を速くできますが、増やしすぎると逆に処理が重くなります。
理由は、INSERTやUPDATEのたびに、設定されているすべてのインデックスを更新する必要があるためです。たとえば、インデックスが1本なら1回の更新で済みますが、6本あると6回更新しなければなりません。
そのため、データを大量に追加したり更新したりするテーブルでは、インデックスが多いほど書き込みに時間がかかります。
また、インデックスはディスク容量も使います。使われていないインデックスを何本も残していると、容量だけが増え、管理もしづらくなります。
「念のため」で増やすのではなく、実際によく使う検索に必要なものだけを残すようにしましょう。
カーディナリティを無視した設計
値の種類が少ないカラムにインデックスを付けても、あまり効果が出ないことがあります。
たとえば、status が「0」と「1」の2種類しかない場合、status = 1 で検索すると、100万行のうち半分近い50万行が対象になることがあります。これでは、インデックスを使っても大量の行を読む必要があり、あまり速くなりません。
一方で、メールアドレスや会員IDのように、ほとんど重複しないカラムなら、1つの値で1行だけを探せるため、インデックスの効果が大きくなります。
そのため、インデックスは「値の種類が多く、しっかり絞り込めるカラム」に優先して付けるようにしましょう。
MySQLのEXPLAINで実行計画を確認する方法
インデックスの最適化を実践するうえで欠かせないのが、クエリがどのように実行されているかを可視化することです。
そのために活用するのが「EXPLAIN」による実行計画の確認であり、これによってインデックスが適切に使われているか、どこに無駄があるのかを把握できます。
ここでは、EXPLAINの基本的な見方から、改善のヒントを得るためのチェックポイントまでを順に解説します。
EXPLAINの基本的な見方
EXPLAINでは、まず「本当にインデックスが使われているか」を確認します。特に見るのは、type、rows、key、Extra の4つです。
確認①：typeを見る
type は、どの方法でデータを探しているかを表します。
ALL なら全件走査で、テーブル全体を読んでいます。ref や range なら、インデックスを使って検索できています。
確認②：rowsを見る
rows は、MySQLが何行読むと予想しているかです。
たとえば、100万行のテーブルで rows = 1000000 なら、ほぼ全件を読んでいます。rows = 100 なら、100行程度まで絞り込めている状態です。
確認③：keyを見る
key には、実際に使われたインデックス名が表示されます。
ここが NULL なら、インデックスは使われていません。作成したインデックス名が表示されていれば、その検索で使われています。
確認④：Extraを見る
Extra では、追加の処理が分かります。
Using where は、取得後にさらに条件判定をしている状態です。Using index が出ていれば、インデックスだけで必要なデータを取得できており、効率よく処理できています。
まずはこの4つを順番に見るだけでも、「どこが遅いのか」をかなり見つけやすくなります。
type・key・rowsで見るべきポイント
EXPLAINでは、まず type、key、rows の3つを見ると、クエリがどれくらい効率よく動いているかが分かります。
type
どの方法で検索しているかです。ALL なら全件走査で、テーブル全体を読んでいます。ref や range なら、インデックスを使って必要な範囲だけを探せています。
key
実際に使われたインデックス名です。ここが NULL なら、インデックスは使われていません。インデックスを作ったのに NULL のままなら、検索条件と合っていない可能性があります。
rows
MySQLが何行読むと予想しているかです。rows = 1000000 なら100万行を読む見込みで、rows = 100 なら100行程度まで絞り込めています。
ただし、type が ref や range でも、rows が数十万になっている場合は、インデックスは使われていても、まだ多くの行を読んでいる状態です。3つをセットで確認するようにしましょう。
改善が必要なパターンの見抜き方
EXPLAINで改善が必要かどうかを見るときは、type、key、rows の組み合わせを確認します。
まず、type = ALL で key = NULL の場合は、インデックスが使われておらず、テーブル全体を読んでいます。100万行あるテーブルなら、100万行すべてを確認するため、まず見直したい状態です。
また、type が ref や range でも安心はできません。rows が50万や100万に近い場合は、インデックスは使われていても、多くの行を読んでいる状態です。
たとえば、key にインデックス名が表示されていても、rows が大きければ、条件で十分に絞り込めていません。インデックスの順番や、どのカラムに付けるかを見直したほうがよい場合があります。
「ALLになっていないか」「keyがNULLではないか」「rowsが多すぎないか」を確認すると、改善が必要なクエリを見つけやすくなります。
MySQLの遅いクエリをインデックスで改善する具体例
ここまでの知識を踏まえ、実際にどのようにクエリのパフォーマンスが改善されるのかを具体例で確認していきます。
理論だけでなく、インデックスの有無や設計の違いによって処理速度がどのように変化するのかを比較することで、最適化の効果をより実感できるはずです。
ここでは、段階的な改善パターンを通してそのポイントを見ていきます。
インデックスなしで遅いケース
検索条件に対応するインデックスがないと、MySQLはテーブル全体を順番に確認します。
たとえば、100万行あるテーブルで WHERE email = 'a@example.com' のように1件だけ探す場合でも、email にインデックスがなければ、100万行すべてを1件ずつ比較します。
この状態では、EXPLAINは type = ALL、key = NULL、rows = 1000000 のようになり、全件走査になっていることが分かります。
本来は1行だけ取得したい検索でも、全行を読むため、データが増えるほど処理時間は長くなります。まずは、WHERE句で使っているカラムにインデックスがあるかを確認してみましょう。
インデックス追加で改善するケース
たとえば、100万行あるテーブルで WHERE email = 'a@example.com' を実行する場合、email にインデックスがなければ100万行すべてを確認します。
一方で、email にインデックスを追加すると、必要な1行だけをすぐに見つけられるようになります。
EXPLAINでも、type は ALL から ref に変わり、key にインデックス名、rows は 1 に近い値が表示されます。
もし rows が数十万や数百万になっているなら、WHERE句で使っているカラムにインデックスを追加するだけで、大きく改善できる可能性があります。
複合インデックスでさらに改善するケース
1つのカラムだけでは十分に絞り込めない場合は、複合インデックスにするとさらに速くなることがあります。
たとえば、WHERE user_id = 100 AND status = 1 で検索する場合、user_id だけにインデックスがあると、まず user_id = 100 の行をまとめて取得し、そのあとで status = 1 を1件ずつ確認します。
もし user_id = 100 の行が1万件あるなら、1万件を読んでから絞り込むことになります。
そこで、(user_id, status) の複合インデックスを作ると、最初から user_id と status の両方に一致する行だけを探せるようになります。その結果、読む行数は1万件から100件程度まで減ります。
EXPLAINでも、rows が大きく減っていれば、複合インデックスによって絞り込みがうまくできていると分かります。
まとめ
インデックス最適化で大切なのは、「どのカラムで検索しているか」に合わせて、必要なインデックスだけを作ることです。
まずは、WHERE句・JOIN・ORDER BYでよく使うカラムを確認し、その条件に合ったインデックスを設定しましょう。複数条件で検索する場合は、複合インデックスの順番も重要です。
逆に、値の種類が少ないカラムや、実際には使われていないインデックスを増やしすぎると、書き込みや更新が遅くなる原因になります。
迷ったときは、EXPLAINで type、key、rows を確認してみてください。ALL や rows が大きいクエリから見直していくと、改善しやすくなります。
まずは、今よく使っている検索条件を1つ選び、そのカラムにインデックスがあるかを確認するところから始めてみましょう。
