インデックスとは何ですか。付けすぎると何が困りますか｜技術面接問題

こう聞かれます

「インデックスとは何ですか。付けすぎると何が困りますか」
「検索が遅いと言われたら、まず何を調べますか」
「2 つの列をまとめたインデックスでは、列の順に意味がありますか」

面接官が見ている点

「速くなる」だけでなく、書き込みが重くなる引き換えまで話せるか
複合インデックスは先頭の列から使われる、と分かっているか
勘ではなく、EXPLAIN で実際に使われているかを確かめる、と言えるか

短い答え

インデックスは本の巻末の索引のような物で、表を頭から全部読まずに、条件に合う行へ近道できます。
そのかわり、行を足す、直す、消すたびにインデックスも書き直すので、書き込みが重くなり、置き場所も使います。
だから、よく使う検索の条件に絞って付け、本当に使われているかを EXPLAIN で確かめます。

少し詳しい答え

読む速さと書く手間

インデックスの無い列で探すと、DB は表を頭から全部読みます。インデックスがあると、値の順に並んだ索引をたどって、合う行だけを読めます。行が多いほど差が開きます。

ただし、表の行を変えるたびに、その表のインデックスも全部直す必要があります。付けるほど書き込みが遅くなり、置き場所も増えます。行が少ない表や、ほとんどの行が当てはまる条件では、索引をたどるより全部読むほうが早いので、DB はインデックスを使わないこともあります。

複合インデックスの列の順

「ある顧客の、ある日より後の注文」をよく探すなら、2 つの列をまとめたインデックスを作ります。

SQL クエリ

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  ordered_on DATE
);
CREATE INDEX idx_orders_cust
  ON orders (customer_id,
             ordered_on);

この索引は、顧客の番号で並べ、同じ顧客の中を日付で並べた形です。電話帳が名字で並び、同じ名字の中を名前で並べているのと同じです。そのため、顧客の番号だけ、または顧客の番号と日付の両方を条件にした検索には効きます。日付だけの検索には効きにくくなります。名前だけで電話帳を引けないのと同じです。

EXPLAIN で確かめる

SELECT の前に EXPLAIN を付けると、DB がその問いをどう読むつもりか (実行計画) が出ます。表を全部読むのか、どのインデックスを使うのかが分かります。出る形は DB ごとに違いますが、PostgreSQL も MySQL も EXPLAIN で見られます。インデックスを足す前と後で、実行計画と時間を比べます。

作った物と結べると伝わりやすくなります。たとえば「ログインのたびにメールアドレスで利用者を探していたので、その列にインデックスがあるかを EXPLAIN で確かめた」のように話します。

よくある間違い

「インデックスは多いほど速い」と言う読むのは速くなっても、書き込みのたびに全部のインデックスを直すので、書き込みは遅くなります
複合インデックスの列の順を気にしない先頭の列を条件に使わない検索には、効きにくくなります。よく使う条件から順を決めます
付けたら必ず使われると思う行が少ない、ほとんどの行が当てはまる、などのときは使われません。EXPLAIN で確かめます

深掘りの問い

「インデックスがあるのに使われないのは、どんなときですか」列に関数をかけて比べる、LIKE '%語' のように途中から探す、ほとんどの行が当てはまる、などを挙げます
「主キーにはインデックスが要りますか」 主キーや一意制約を置くと、多くの DB が自動でインデックスを作る、と答えます。外部キーの列には自分で付ける必要がある DB もあります
「どの列に付けるかは、どう決めますか」よく使う WHERE、JOIN、ORDER BY の列から考え、遅い問いを EXPLAIN で見て、足す前と後を測って決める、と答えます