MySQL / MariaDBで索引を追加する前の確認

索引は検索の候補経路を追加しますが、作成成功は高速化の保証ではありません。このページはMySQL / MariaDBのテーブル関連に属する作成記事です。テーブル関連の親ページから子ページを選択でき、索引の削除や外部キーの条件も別に確認できます。

2026-10-26にMySQL8.4.13 / MariaDB11.8.9-ubu2404の新規InnoDB検体で確認しました。実サイトへの索引追加や負荷試験ではありません。既存の他製品向け説明も後の各章に保持しますが、構文・ロック・制約を全製品共通とは扱いません。

1. 遅いSQLと守る結果を先に決める

同じSELECTでも抽出列・条件値・結合・ORDER BY・LIMITが変われば必要な索引は変わります。取得結果の集合だけでなく順序も比較します。日時が同じ行を含むLIMITでは、idなど一意な列を並びの末尾に入れます。EXPLAINのrowsは推定で、1回の計画や利用回数だけを全利用者の速度と読み替えません。

SELECT VERSION(), DATABASE(), @@SESSION.sql_mode, @@SESSION.foreign_key_checks;
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='cwiki_w26' AND TABLE_NAME='orders';

接続先・製品版・engineと既存の索引/制約を読み取ります。名前が違っても列が重複する場合があり、逆に先頭列が同じでも同じ定義とは限りません。列順・長さ指定・昇降順・一意性・式・照合順序と利用側のhintも照合します。下の一覧は共通項目に絞り、不可視/ignored等の製品固有項目は別途SHOW INDEXや対象版の定義で確認します。

SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, NON_UNIQUE,
       SEQ_IN_INDEX, COLUMN_NAME, SUB_PART, COLLATION, INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA='cwiki_w26' AND TABLE_NAME='orders'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA='cwiki_w26' AND TABLE_NAME='orders'
ORDER BY CONSTRAINT_NAME;

2. 新規の使い捨て検体で前後を比較する

次は検体専用です。新しく作成した空のcwiki_w26データベースでだけ準備します。既存の同名表へ流さず、検体用に実データを転載しません。接続先DBの作成権限や本番DDLをこの教材のために追加する必要はありません。

-- New disposable database cwiki_w26 only. Never run against an existing table.
CREATE TABLE cwiki_w26.orders (
    id INT NOT NULL PRIMARY KEY,
    user_id INT NOT NULL,
    created_at DATETIME NOT NULL,
    note VARCHAR(40) NULL
) ENGINE=InnoDB;
INSERT INTO cwiki_w26.orders (id,user_id,created_at,note) VALUES
    (1,10,'2026-01-01 09:00:00','first'),
    (2,10,'2026-01-02 09:00:00','second'),
    (3,20,'2026-01-01 09:00:00','other'),
    (4,10,'2026-01-02 09:00:00','same timestamp'),
    (5,30,'2026-01-03 09:00:00',NULL),
    (6,40,'2026-01-04 09:00:00','last');

追加前の結果はidの順に1,2,4です（created_atで並べ、同時刻をidで固定）。このSELECTとEXPLAINを追加前後に同じ条件で実行します。

SELECT id, user_id, created_at, note
FROM cwiki_w26.orders
WHERE user_id=10 AND created_at >= '2026-01-01 00:00:00'
ORDER BY created_at, id;

EXPLAIN SELECT id, user_id, created_at, note
FROM cwiki_w26.orders
WHERE user_id=10 AND created_at >= '2026-01-01 00:00:00'
ORDER BY created_at, id;

通常索引の構文を学ぶ検体として次を追加し、一覧で(user_id,created_at,id)の列順を確認します。既存本番では承認済みの評価計画に従い、対応する索引ならINVISIBLE等を使った影響評価から始めます。下の通常作成を本番へそのまま転用しません。

-- Disposable fixture only. Check existing indexes and dependencies first.
CREATE INDEX idx_orders_user_created_id
ON cwiki_w26.orders (user_id, created_at, id);

検体では索引追加後も6行と元の値、SELECTの1,2,4の順序を保持しました。実行計画のkey/possible_keys/type/Extra等を読み、候補になったことと採用されたことを分けます。小規模データの計画・時間から、本番の速度倍率や全件数への効果は算出していません。通常のEXPLAINと異なりANALYZE系の実行を伴う方式は負荷もあるので、診断用に本番へ自動追加しません。

3. UNIQUEは高速化だけのオプションではない

UNIQUE作成時には既存値も検査されます。重複のある検体は1062で拒否され、元の行は残りました。重複を削除/上書きして通すのではなく、何を同一とするか、NULL・大文字小文字/照合順序・複合列の規則を設計します。今回のnullable一列の検体はNULLを2行許容し、同じ非NULL値のINSERTを1062で拒否しました。全製品やNULLを含む全複合キーの保証ではありません。

InnoDBの主キーは行の格納構造にも関わるため、「索引は元データと完全に別」という説明だけでは不十分です。既存のPRIMARY/UNIQUEと同じ列へ通常索引を重ねる必要性、外部キーが必要とする索引も先に調べます。

4. 公開中の変更は別の運用計画で扱う

追加にはCPU/I/O・一時領域・書込み側の保守コストとmetadata lockがあります。検体では読取transactionが表を使用中、別接続のCREATE INDEXが1秒の検体用lock_wait_timeoutで1205となりました。LOCK=NONEやオンライン機能を「待たない/必ず無停止」と呼ばず、engine/対象操作の対応と待ち・切替・容量を確認します。KILL、全保護解除、外部ツールへの切替を自動対処にしません。

不可視化の製品別の例と、一意性が残ることは削除記事の「不可視化は削除の代用品ではない」を参照してください。新規検体の試験であり、未使用と判断した索引の本番削除まで承認された意味ではありません。

インデックスは、CREATE INDEX 文でテーブルの特定の列に作成する検索用のデータ構造で、対象の列を条件にした検索を高速化するために使う。基本構文は CREATE INDEX インデックス名 ON テーブル名 (カラム名); である。

この記事の要点

インデックスは検索を速くする「索引」。CREATE INDEX 名前 ON テーブル (列); で作成する。 速くなるのは 読み取り（SELECT の検索・結合・並べ替え）。引き換えに 書き込み（INSERT / UPDATE / DELETE）が遅くなり、ディスク容量も増える。 複数列をまとめる複合インデックスでは 列の順序が決定的に重要。先頭列から順に絞り込みに使われる。 重複を許さない場合は CREATE UNIQUE INDEX、不要になったら DROP INDEX で削除する。 列に関数や演算を適用した条件（例: WHERE UPPER(name) = ...）や、前方一致でない LIKE '%xxx' ではインデックスが効きにくい。 カーディナリティ（値の種類）が低い列への単純なインデックスや、貼りすぎは効果が薄く逆効果になりうる。 細かな構文・既定の挙動は RDBMS（MySQL / PostgreSQL / Oracle / SQL Server など）によって差があるため、利用環境の公式ドキュメントで確認すること。

インデックスとは

インデックス（index、索引）は、本の巻末にある索引と同じ役割を果たす仕組みである。索引がなければ目的の語を探すのに本を最初から1ページずつめくる必要があるが、索引があれば「この語は何ページ」と一気にたどり着ける。DBでも索引は探索経路になりますが、当該列の索引がないだけで必ず全表走査とは限りません。他の条件/索引/結合経路も関わります。全表走査の負荷は行数だけでなく取得量/分布/メモリ/I/O等にも依存します。

インデックスを作成すると、対象列の値が並べ替えられた状態の専用データ構造（多くのRDBMSで B-Tree と呼ばれる木構造）が別途用意される。検索時はこの構造をたどることで、全行を見ずに目的の行へ短い手数で到達できる。これが検索高速化の基本的な仕組みである。

重要なのは、インデックスは探索経路であり、UNIQUEなら一意性の保護にも関わるという点だ。索引の保守/容量が必要です。InnoDBの主キーはクラスタ化された行の格納構造にも関わり、全索引を元データから完全に独立した構造とは扱いません。この性質が、後述するメリットとデメリットの根拠になる。

CREATE INDEX 構文と例

インデックスを作成する基本構文は次のとおり。

旧例の注意：基本形の記号です。実製品/対象schema/実名/既存定義を先に確認します。

CREATE INDEX インデックス名 ON テーブル名 (カラム名);

たとえば、ユーザー情報を持つ users テーブルの email 列でよく検索するなら、次のように作成する。

旧例の注意：新例の検体とは別のusers用の構文例です。既存索引/制約を照合せず実表へ追加しません。

CREATE INDEX idx_users_email ON users (email);

次の検索で利用候補になります。実際の計画/結果/負荷を確認し、作成だけで高速化したとは断定しません。

旧例の注意：抽出の別例で、新検体のordersへ順次流すものではありません。

SELECT * FROM users WHERE email = 'taro@example.com';

インデックス名は同じテーブル（環境によってはスキーマやDB全体）で一意である必要がある。慣習として idx_テーブル名_列名 のように、対象が分かる命名にしておくと管理しやすい。なお、主キー（PRIMARY KEY）や UNIQUE 制約を付けた列には、多くのRDBMSで自動的にインデックスが作成されるため、改めて手動で作る必要はない。

複合インデックス（列順の重要性）

複数の列をまとめて1つのインデックスにしたものを複合インデックス（マルチカラムインデックス）と呼ぶ。複数列を組み合わせた条件で検索・並べ替えする場合に有効だ。

旧例の注意：複合索引の別例です。列順はSQL全体/結果/計画と保護要件から判断し、常に同じ順序が最適とは限りません。

CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

複合インデックスで最も注意すべきは列の順序である。インデックスは指定した列の順に並べ替えられているため、原則として「左側（先頭）の列から順に」絞り込みに使われる。上の例（user_id, created_at の順）では次のように効き方が変わる。

• WHERE user_id = 10 AND created_at >= '2026-01-01' … 両方の列が活用され、よく効く。
• WHERE user_id = 10 … 先頭列だけでも効く。
• WHERE created_at >= '2026-01-01' … 先頭の user_id を飛ばして2番目の列だけを条件にしても、このインデックスは効きにくい（先頭列が条件にないため）。

つまり、よく使う検索条件・等価比較で使う列を先頭に置くのが基本方針となる。(A, B) と (B, A) はまったく別物として扱われる点に注意したい。具体的にどの順序が最適かはクエリの実行計画（後述）で確認するのが確実である。

UNIQUE INDEX（重複を許さないインデックス）

列の値の重複を許したくない場合は UNIQUE INDEX を使う。検索の高速化に加えて、同じ値が二重に登録されることを防ぐ制約としても機能する。

旧例の注意：同じusersへ通常索引とUNIQUEを続けて追加する指示ではありません。既存値/NULL/比較規則を確認し、重複を削除して通しません。

CREATE UNIQUE INDEX uq_users_email ON users (email);

この状態ですでに存在する email と同じ値を INSERT / UPDATE しようとすると、エラーになって弾かれる。複数列に対して CREATE UNIQUE INDEX uq_xxx ON テーブル (列1, 列2); とすれば、「列1と列2の組み合わせ」が一意であることを保証できる。

なお NULL の扱いはRDBMSによって差がある。一般に多くのRDBMSでは複数の NULL を重複と見なさず許容するが、挙動が異なる製品・設定もあるため、NULL を含む列に UNIQUE を付ける場合は利用環境の仕様を確認すること。

インデックスの削除（DROP INDEX）

不要になったインデックスは DROP INDEX で削除する。ただし、この構文にはRDBMSによる差が大きいので注意が必要だ。

旧例の注意：製品別の代替構文です。同じ索引に全DROPを順次実行せず、削除前に制約/依存と復旧条件を確認します。

-- PostgreSQL / Oracle など（インデックス名のみで削除） DROP INDEX idx_users_email;   -- MySQL（対象テーブルの指定が必要） DROP INDEX idx_users_email ON users; -- MySQL では ALTER TABLE 形式も使える ALTER TABLE users DROP INDEX idx_users_email;

このように、MySQL では ON テーブル名 が必要で ALTER TABLE 形式も使えるのに対し、PostgreSQL や Oracle ではインデックス名だけで削除する。使っているRDBMSの構文に合わせること。

インデックスが効くケース・効かないケース

インデックスは「作れば必ず速くなる」ものではない。クエリの書き方によって効いたり効かなかったりする。代表的なパターンを整理しておく。

• 効きやすい: WHERE 列 = 値 の等価比較、WHERE 列 BETWEEN ... AND ... や < / > の範囲検索、JOIN の結合条件に使う列、ORDER BY / GROUP BY の対象列。
• 前方一致の LIKE: WHERE name LIKE '田中%' のように末尾だけがワイルドカードなら効きやすい。
• 効きにくい: WHERE name LIKE '%田中%' のように先頭がワイルドカードの中間・後方一致。
• 効きにくい: 列に関数や演算を適用した条件。例として WHERE UPPER(email) = 'A@B.COM' や WHERE price * 1.1 > 1000 は、列 email / price 自体のインデックスが使われないことが多い（これを「インデックスが効かない＝索引が無視される」状態と呼ぶ）。
• 効きにくい: 検索対象が全体の大部分を占める場合。多くの行が該当するならフルスキャンの方が速いとオプティマイザが判断し、インデックスを使わないことがある。

実際にインデックスが使われているかは、実行計画を確認するのが最も確実だ。クエリの先頭に EXPLAIN（PostgreSQL では EXPLAIN ANALYZE も可）を付けて実行すると、フルスキャンかインデックス利用かを確認できる。構文や出力はRDBMSごとに異なる。

旧例の注意：計画を見る別例です。EXPLAIN ANALYZE等の実行を伴う診断と混同せず、本番へ負荷を自動追加しません。

EXPLAIN SELECT * FROM users WHERE email = 'taro@example.com';

メリットとデメリットの比較

インデックスは「検索速度」と「書き込み・容量コスト」のトレードオフで考えるのが基本である。

観点	インデックスあり	インデックスなし
検索（SELECT）の速度	利用条件と実行計画次第で改善候補になる	他の索引/結合経路や取得量次第。必ず全表走査/低速とは限らない
書き込み（INSERT/UPDATE/DELETE）	索引保守の追加コスト。競合/対象列/処理全体で計測する	追加索引の保守はないが、行探索/ロックもあり高速とは断定しない
ディスク容量	インデックス用の領域を別途消費する	追加索引分は不要。主キーの格納構造等はengine別に確認
運用・保守	設計と見直し（不要インデックスの削除等）が必要	管理対象が少ない

要するに、読み取りを速くする代わりに、書き込みと容量にコストを払うのがインデックスである。読み取りが多いテーブルほど恩恵が大きく、書き込みが極端に多いテーブルでは慎重に判断する必要がある。

いつインデックスを貼るべきか

むやみに貼るのではなく、次のような列・状況を優先するとよい。

• WHERE 句で頻繁に検索条件に使う列（特に等価比較で絞り込む列）。
• JOIN の結合条件に使う列（多くは外部キーに相当する列）。
• ORDER BY / GROUP BY でよく使う列。
• カーディナリティが高い列（値の種類が多く、条件で行をしっかり絞り込める列）。たとえばメールアドレスやユーザーIDなど。
• 行数が多く、検索が遅いと感じるテーブル。

逆に、行数が少ないテーブルや、検索に使われない列、書き込みが集中する一時テーブルなどは、インデックスを付けても効果が薄い、あるいはコストが上回ることがある。

落とし穴

よくある落とし穴

貼りすぎ: 「とりあえず全列にインデックス」は逆効果。INSERT/DELETEや索引対象値の変更に保守コストがあります。全UPDATEが必ず全索引を書き換える/目に見えて遅くなるという意味ではありません。容量も無駄に増える。利用回数だけでなく制約・定期処理・hint・観測範囲も確認します。 低カーディナリティ列への単純なインデックス: 性別フラグや「有効/無効」のように値の種類が2〜数種類しかない列に通常のインデックスを貼っても、多くの行が該当するため絞り込み効果が小さく、使われないことが多い。 列への関数・演算で無効化: WHERE UPPER(name) = 'TARO' や WHERE DATE(created_at) = '2026-06-12' のように列を加工すると、その列のインデックスは効かなくなりやすい。可能なら条件側ではなく値側を加工する、あるいは加工後の値に対応する仕組み（関数インデックスや生成列など、対応はRDBMSにより異なる）を検討する。 暗黙の型変換: 文字列型の列に対して数値で比較する（あるいはその逆）と、内部で型変換が起きてインデックスが使われないことがある。条件の値の型は列の型に合わせる。 作っただけで満足しない: 本当に使われているかは EXPLAIN で実行計画を確認する。1回の計画に出ないだけで不要とは判断しません。UNIQUE/FK保護や他の処理も確認してから見直します。

よくある質問（FAQ）

Q. インデックスを作るとデータの中身は変わりますか？
A. テーブルのデータそのものは変わりません。インデックスはあくまで検索を速くするための「索引」が別に追加されるだけで、行の値や件数は変化しません。通常索引の削除は行削除ではありませんが、UNIQUEなら保護も失い、依存するFKやhintで拒否/エラーにもなり得ます。検索速度だけの違いではありません。

Q. インデックスはいくつでも作っていいですか？
A. 技術的には複数作れますが、増やすほど書き込みが遅くなり容量も増えます。実際の検索パターンと保護/依存から必要性を判断します。観測期間の利用回数0だけで削除しません。「多ければ速い」ではない点に注意してください。

Q. 主キーにはインデックスを別途作る必要がありますか？
A. 通常は不要です。多くのRDBMSでは PRIMARY KEY や UNIQUE 制約を定義した時点で自動的にインデックスが作成されます。同じ列に重ねて手動で作ると重複になるため避けましょう。

Q. CREATE INDEX や DROP INDEX の構文はどのデータベースでも同じですか？
A. 基本形は共通ですが、細部はRDBMS（MySQL / PostgreSQL / Oracle / SQL Server など）で異なります。特に DROP INDEX はテーブル指定の要否が分かれ、UNIQUE 列の NULL の扱いや、関数インデックス・部分インデックスなどの拡張機能の有無も製品によって違います。利用している環境の公式ドキュメントで確認してください。

元の書式付きSQLの記録

元記事の構文表も保持します。日本語は置換位置を示す記号で、そのまま実行しません。元作成v1〜3のCREATE NDEXは誤記で、修正済みv5のCREATE INDEXを掲載しています。削除の表は対象/依存/復旧条件の確認なしに実表へ実行する手順ではありません。

CREATE INDEX インデックス名 ON テーブル名(カラム名);

確認範囲と公式資料

新規の小規模検体で構文・失敗・行/索引の保持を確認したもので、速度倍率/巨大負荷/全engine/全製品版/本番の停止時間/完全復旧を保証しません。本番schema・索引・設定・認証は変更していません。

• MySQL8.4 CREATE INDEX / DROP INDEX / 不可視索引
• MariaDB DROP INDEX / Ignored Indexes
• PostgreSQL18 DROP INDEX / SQL Server DROP INDEX