MariaDB講座 / 実際の検索に合わせて単一・複合インデックスを設計する
インデックスは行の検索や順序付けを助ける別の構造で、複合索引の列順が対応できる問い合わせを左右します。検索条件、ソート、必要列と既存索引を調べ、読み取りの効果を更新と保存の費用と比較します。
索引は維持が必要なデータである
挿入・更新・削除は索引も更新するため、多ければよいとは限りません。頻度、絞り込み割合、結合と順序を合わせて考えます。顧客ごとの期間一覧ならcustomer_id、ordered_at、order_id、全顧客の時刻一覧なら時刻先頭の構造が候補です。
UNIQUEは速度だけでなく整合性の規則です。EXPLAINで実際の選択、同じ結果を保つ改善で条件やページ方式を比較します。
すでにある索引を調べる
course_userでshopcourseに接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;で対象を確認します。以下は独立した実習表の作成・追加を含む完全な例で、UTF-8ファイルとして実行できます。同名表があれば定義と行を調べ、必要なものを保存してから判断します。
USE shopcourse; CREATE TABLE c39_customers ( customer_id INT NOT NULL PRIMARY KEY, customer_name VARCHAR(50) NOT NULL, city VARCHAR(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE c39_products ( product_id INT NOT NULL PRIMARY KEY, product_name VARCHAR(100) NOT NULL, category VARCHAR(30) NOT NULL, price DECIMAL(10,2) NOT NULL CHECK (price >= 0), stock INT NOT NULL CHECK (stock >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE c39_orders ( order_id INT NOT NULL PRIMARY KEY, customer_id INT NOT NULL, ordered_at DATETIME NOT NULL, status VARCHAR(20) NOT NULL, shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000, CONSTRAINT fk_c39_order_customer FOREIGN KEY (customer_id) REFERENCES c39_customers(customer_id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE c39_items ( order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL CHECK (quantity > 0), unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0), PRIMARY KEY (order_id, product_id), CONSTRAINT fk_c39_item_order FOREIGN KEY (order_id) REFERENCES c39_orders(order_id) ON DELETE RESTRICT, CONSTRAINT fk_c39_item_product FOREIGN KEY (product_id) REFERENCES c39_products(product_id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO c39_customers VALUES (1,'김민수','서울'),(2,'이서연','부산'), (3,'박지우','서울'),(4,'최하늘','대전'); INSERT INTO c39_products VALUES (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50), (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5); INSERT INTO c39_orders (order_id,customer_id,ordered_at,status,shipping_fee) VALUES (101,1,'2026-01-10 10:00:00','paid',1000), (102,1,'2026-01-20 11:00:00','paid',1000), (103,2,'2026-02-05 12:00:00','paid',1000), (104,3,'2026-02-10 13:00:00','canceled',1000); INSERT INTO c39_items VALUES (101,1,2,3500),(101,2,3,1500),(102,3,1,8000), (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
文字列は原文の韓国語サンプルを維持しています。商品1～4はノート、ペン、マグカップ、ライト、顧客1・3はソウル、2は釜山、4は大田です。UTF-8で保存・接続し、安定したIDと数値で照合します。
SHOW INDEX FROM c39_orders;
主キーや外部キーのため索引がすでにある場合があります。名前だけで違いを判断せず、列、Seq_in_index、一意性、長さと依存制約を読みます。
顧客・時刻・識別子の複合索引を作る
候補を作り、計画と実際の結果を比較します。
CREATE INDEX idx_c39_customer_date ON c39_orders(customer_id,ordered_at,order_id); EXPLAIN SELECT order_id,ordered_at,status FROM c39_orders WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01' ORDER BY ordered_at,order_id; SELECT order_id FROM c39_orders WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01' ORDER BY ordered_at,order_id; SHOW INDEX FROM c39_orders;
結果は注文101・102です。顧客を等価で絞り、時刻を範囲にし、同時刻はIDで順序を決めます。四行しかないため全走査が合理的な場合もあり、速度向上の証明にはなりません。
先頭列・範囲・カバリングを読む
複合索引は一般に先頭列が重要です。日付だけの条件で、顧客先頭と同じ効率の範囲検索を期待しません。ただし先頭等価条件なしでは一切の索引読み取りが不可能、という断定でもありません。
範囲条件、方向、文字列のプレフィックスと出力列で経路が変わります。必要情報を索引から得るカバリングは元行アクセスを減らし得ますが、全列を無条件に追加するとキーと更新費用が増えます。EXPLAINのkeyとExtraを実際に読みます。
重複と制約依存を確認する
違う名前で同じ列列順があるか、既存キーとどこまで重なるか調べます。複合索引作成後にもSHOW INDEXを読み、外部キーを支える唯一の索引を消さないでください。必要なら代替を準備します。あるSELECTに使われない主キーやUNIQUEでも、整合性のため必要なことがあります。
索引の有無で必要な行と順序が変わらないことを先に照合し、代表分布で計画、読み取り量、待ち時間と更新コストを比較します。二回目のキャッシュだけを構造改善の効果とはしません。
やり直すときは、実際のDB・定義・行を確認し、必要なデータを保存して、この記事の削除可能な表だけを子から親の順に消します。DROP TABLEは定義とデータを削除し、通常のROLLBACKでは戻せません。
DROP TABLE shopcourse.c39_items; DROP TABLE shopcourse.c39_orders; DROP TABLE shopcourse.c39_products; DROP TABLE shopcourse.c39_customers;
