回答

インデックスは、特定の列やテーブルの列のセットに基づいてデータを迅速に検索するための特別なデータ構造（通常はBツリーに基づく）です。インデックスは、スキャンする行の数を減らすことにより、データの取得、ソート、フィルタリングを高速化します。

インデックスの種類:

• 通常のインデックス (B-tree, hash)
• 構成インデックス (複数列インデックス)
• ユニークインデックス (値の一意性を保証)
• 被覆インデックス (covering)
• フルテキストインデックス (full-text)

インデックスは次のことを高速化します:

• WHERE ... = ...
• インデックス化された列によるJOIN
• インデックス化された列によるORDER BYおよびGROUP BY

インデックスは次のことを遅くする可能性があります:

• 挿入、更新、削除 — インデックスはその構造を維持するために追加の操作を必要とします。
• クエリが頻繁にインデックスがない列を使用する場合、インデックスは無駄になり、大量の挿入時には有害です。

インデックス作成の例:

CREATE INDEX idx_user_email ON users (email);

インデックスが助けにならない例:

SELECT * FROM users WHERE lower(email) = 'test@example.com';
-- もしインデックスがemailに構築されている場合、しかしクエリが関数lower(email)を使用している場合、インデックスは使用されません！

くぼみのある質問

テーブルのすべての列にインデックスを追加すると、すべてのSELECTクエリの実行が常に速くなりますか？

回答:
いいえ。インデックスは、フィルタリングまたはソートがインデックス化された列に対して厳密に行われる場合のみ、クエリを高速化します。関数や演算を使用してインデックスの使用を妨げる場合は、過剰なインデックスがINSERT/UPDATE/DELETEを遅くするだけでなく、多くのスペースを占有し、一部の複雑なクエリはインデックスを完全に回避することさえあります（例えば、範囲スキャンの際に式を使用する場合）。

例:

SELECT * FROM orders WHERE year(order_date) = 2023;
-- もしインデックスがorder_dateだけにあれば、year()関数のためにインデックスは機能しません

---

歴史

電子メールシステムでは、頻繁に使用されるフィールドごとにインデックスを作成するトリガーが作成されました。6ヶ月後、システムのパフォーマンスが低下 — 各行の挿入または変更に4〜5倍の時間がかかるようになりました。監査後、インデックスの数が減少し、システムが高速化されました。

---

歴史

広告プラットフォームでは、substring(url, 1, 10)でフィルタリングされたSELECTクエリが頻繁に見られました。urlにインデックスがあったにもかかわらず、SQLはsubstring関数のためにインデックスを利用しませんでした。解決策は、そのような抽出のために別のフィールドを導入し、そのフィールドにインデックスを作成することでした。

---

歴史

ロイヤリティプログラムでは、(customer_id, shop_id)フィールドに対して複合インデックスが作成されました。shop_idのみのクエリではインデックスが適用されず、テーブル全体がスキャンされました (full scan)。これはボーナス計算におけるインデックスの利益を失わせました。最適化が助けとなりました: shop_idのための別のインデックスを作成しました。