この記事では、MySQLでインデックスを追加・削除する手順を、CREATE INDEX・ALTER TABLE ADD INDEX・DROP INDEX・SHOW INDEX・EXPLAINまで実機（Rocky Linux 9 / MySQL 8.0）で動作確認しながら解説します。「どのカラムにインデックスを張るべきか」「逆に張ってはいけないケース」まで含めてカバーします。
この記事のポイント
・CREATE INDEX文でカラムへのインデックスを追加できる
・SHOW INDEX FROMで既存インデックスの構造を確認する
・EXPLAINのtypeがALLからrefに変わればインデックスが使われている
・更新頻度が高いカラムや選択性が低いカラムへの追加は逆効果になる
でも安心してください。プロのエンジニアはコマンドを暗記していません。
「現場で使える型」を効率よく使いこなしているだけです。
なぜインデックスが必要なのか
インデックスを持たないテーブルに対してSELECT文を実行すると、MySQLはすべての行を先頭から順番に読み込む「フルテーブルスキャン（type=ALL）」を行います。行数が1,000行程度なら体感できないほど高速ですが、100万行を超えると数秒単位の待機が発生し始めます。インデックスはB-Tree（バランスツリー）という構造でデータを整理します。辞書の「あいうえお索引」と同じ発想で、特定の値を持つ行への参照がツリーを辿るだけで高速に求められます。行数が増えても検索コストが対数的にしか増えないため、大規模テーブルでの効果が特に顕著です。
MySQLはテーブル作成時にPRIMARY KEYを自動でインデックス化します。それ以外のカラムはデフォルトではインデックス未設定なので、JOINやWHERE句で頻繁に使うカラムを手動で追加する必要があります。
インデックスの種類と特徴
MySQLで使えるインデックスは主に以下の4種類です。・INDEX（通常インデックス）：最もよく使う汎用型。重複値を許容する
・UNIQUE INDEX：重複値を禁止する一意制約つきのインデックス
・PRIMARY KEY：テーブルに1つだけ設定できる一意かつNOT NULLのインデックス
・FULLTEXT INDEX：文字列カラム全文検索用。LIKE '%文字%'の代わりにMATCH()で使う
通常の運用でよく使うのはINDEXとUNIQUE INDEXの2つです。フルテキスト検索はアプリ層でElasticsearchなどを使うことも多く、MySQLのFULLTEXTは補助的な位置づけになります。
CREATE INDEXでインデックスを追加する
1. 単一カラムへのインデックス追加
基本構文は以下のとおりです。CREATE INDEX インデックス名 ON テーブル名 (カラム名);
mysql> CREATE INDEX idx_customer_id ON orders (customer_id); Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0
2. 複合インデックスの追加
複数カラムを組み合わせた「複合インデックス（複合キー）」も作成できます。CREATE INDEX idx_order_date_status ON orders (order_date, status);
3. ALTER TABLE ADD INDEXとの使い分け
インデックスを追加するもう一つの方法がALTER TABLE構文です。ALTER TABLE orders ADD INDEX idx_status (status);
UNIQUE INDEXを追加したい場合は、INDEXの前にUNIQUEを付けます。
CREATE UNIQUE INDEX idx_email ON users (email); # または ALTER TABLE users ADD UNIQUE INDEX idx_email (email);
SHOW INDEX FROMで既存インデックスを確認する
テーブルに設定されているインデックスの一覧はSHOW INDEX FROMで確認できます。mysql> SHOW INDEX FROM orders\G *************************** 1. row *************************** Table: orders Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 58432 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: orders Non_unique: 1 Key_name: idx_customer_id Seq_in_index: 1 Column_name: customer_id Collation: A Cardinality: 3201 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 2 rows in set (0.00 sec)
・Key_name：インデックス名（PRIMARYはプライマリキー）
・Column_name：インデックスが設定されているカラム名
・Non_unique：0=ユニーク、1=重複を許容
・Cardinality：インデックスのカラムのユニーク値の推定数。値が大きいほどインデックスの選択性が高い
・Index_type：BTREEが通常のインデックス
Cardinalityはあくまで推定値です。ANALYZE TABLEを実行すると統計情報が更新され、より正確な値になります。
EXPLAINでインデックスが実際に使われているか確認する
インデックスを追加しただけでは「本当にクエリがインデックスを使っているか」は分かりません。EXPLAINコマンドで実行計画を確認します。mysql> EXPLAIN SELECT * FROM orders WHERE customer_id = 1001\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: orders partitions: NULL type: ref possible_keys: idx_customer_id key: idx_customer_id key_len: 5 ref: const rows: 14 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
・type=ALL：フルテーブルスキャン（改善が必要）
・type=ref：非ユニークインデックスを使ったルックアップ（効率的）
・type=eq_ref：ユニークインデックスまたはPRIMARY KEYを使った1行特定（最も効率的）
rowsが14まで絞り込まれており、インデックスが正しく機能していることが確認できます。インデックスを追加してもtypeがALLのままであれば、MySQLがインデックスより全件スキャンの方が速いと判断しているか、カラムの型不一致・関数適用によりインデックスが使えない状態になっています。
DROP INDEXでインデックスを削除する
不要になったインデックスは削除できます。DROP INDEX インデックス名 ON テーブル名; # または ALTER TABLE テーブル名 DROP INDEX インデックス名;
mysql> DROP INDEX idx_customer_id ON orders; Query OK, 0 rows affected (0.28 sec) Records: 0 Duplicates: 0 Warnings: 0
インデックスを追加してはいけないケース
インデックスは増やせば増やすほど良いわけではありません。以下のケースでは追加が逆効果になります。・更新頻度が高いカラム：INSERT/UPDATE/DELETE時にインデックスの再構築コストがかかる。書き込みが多いシステムではインデックス数を絞る
・選択性が低いカラム：「性別（男・女・その他の3値）」のようにユニーク値が少ないカラムへのインデックスはほぼ効かない。MySQLがフルスキャンの方が速いと判断して使わないことが多い
・WHERE句で使われないカラム：JOINやORDER BYにも影響するが、実際にクエリで参照されないカラムへの追加は無駄なオーバーヘッド
・小規模テーブル（数百行以下）：フルスキャンで十分に速いため、インデックスの追加効果がほぼない
「とりあえずWHEREで使うカラム全部にインデックスを貼ってしまう」という設計は書き込みパフォーマンスを著しく悪化させます。EXPLAIN出力のrowsと実行時間を計測しながら、本当に効果があるカラムにだけ追加する姿勢が重要です。
本記事のまとめ
| やりたいこと | コマンド |
|---|---|
| 単一カラムにインデックスを追加する | CREATE INDEX インデックス名 ON テーブル名 (カラム名) |
| 複合インデックスを追加する | CREATE INDEX インデックス名 ON テーブル名 (カラム1, カラム2) |
| 一意制約つきインデックスを追加する | CREATE UNIQUE INDEX インデックス名 ON テーブル名 (カラム名) |
| ALTER TABLEでインデックスを追加する | ALTER TABLE テーブル名 ADD INDEX インデックス名 (カラム名) |
| テーブルのインデックス一覧を確認する | SHOW INDEX FROM テーブル名 |
| クエリの実行計画を確認する | EXPLAIN SELECT ... FROM テーブル名 WHERE ... |
| インデックスを削除する | DROP INDEX インデックス名 ON テーブル名 |
インデックス設計は「Linuxサーバー上でDBをどう動かすか」という構造理解が土台になります
MySQLのインデックスを正しく設計するには、クエリの実行計画・ストレージエンジンの仕組み・サーバーリソースとの兼ね合いを体系的に理解することが重要です。断片的な知識の積み上げより、現場で使われる設計パターンを体系的に身につけることで、チューニングの判断が格段に速くなります。
現場で通用する安全なLinuxサーバー構築の「型」を体系的に身につけたい方へ、『Linuxサーバー構築入門マニュアル（図解60P）』を完全無料でプレゼントしています。
「独学の時間がもったいない」「プロから直接、現場の技術を最短で学びたい」という本気の方には、2日で実務レベルのスキルが身につく【初心者向けハンズオンセミナー】も開催しています。
3,100名以上が実践した「型」を無料で公開中
プロのエンジニアはコマンドを暗記していません。
「現場で使える型」を効率よく使いこなしているだけです。
その「型」を図解60Pにまとめた入門マニュアルを、完全無料でプレゼントしています。
姓・名・メールの3つだけ／30秒／解除は3秒 ／ 詳細はこちら
この記事を書いた人
宮崎 智広（みやざき ともひろ）
株式会社イーネットマーキュリー代表。現役のLinuxサーバー管理者として20年以上の実務経験を持ち、これまでに累計3,100名以上のエンジニアを指導してきたLinux教育のプロフェッショナル。「現場で本当に使える技術」を体系的に伝えることをモットーに、実践型のLinuxセミナーの開催や無料マニュアルの配布を通じてLinux人材の育成に取り組んでいる。
趣味は、キャンプにカメラ、トラウト釣り。好きな食べ物は、ラーメンにお酒。休肝日が作れない、酒量を減らせないのが悩み。最近、ドラマ「フライトエンジェル」を観て涙腺が崩壊しました。
- 前のページへ：MySQLのEXPLAIN出力を読む方法｜type・rows・Extraフィールドでインデックスが効いているか診断する手順
- この記事の属するカテゴリ：データーベース管理へ戻る
無料メルマガで学習を続ける
Linuxの実践スキルをメールで毎週お届け。
登録は30秒、解除もいつでも可。
登録無料・いつでも解除できます