インデックスの役割

1000 万行から 1 件を、毎回先頭から数える

技術書の巻末には索引が付いています。「デッドロック」という語がどこに出てくるかを知りたいとき、500 ページを 1 ページずつめくる人はいません。索引を引いて、書いてあるページだけを開きます。

索引のないテーブルは、この巻末索引がない本と同じです。1000 万行の利用者の表から 1 件を取り出すとき、DB は先頭の行から順に値を比べていきます。目的の行が最後にあれば 1000 万回の比較が要ります。1 回 1 マイクロ秒でも 10 秒です。

索引を貼るというのは、本文とは別に「その列の値と、行のありか」を値の順に並べた一覧を持つことです。並んでいるので、端から見ていく必要がありません。

ここで効いているのは、並んでいるという性質だけです。並んでいれば、真ん中を見て前半か後半かを決められます。決めるたびに候補が半分になるので、1000 万件でも 24 回ほどで 1 件まで絞れます。あとは、そこに書いてある行のありかを辿るだけです。

索引を足すと、書き込みが遅くなる

ここで止めると片手落ちです。本に索引を付けるということは、本文を 1 行足すたびに索引も直す、ということでもあります。

DB でもまったく同じです。1 行追加するたびに、その表に貼ってある索引を全部更新します。索引が 5 つあれば、書き込みのときに直す場所が 5 か所増えます。値を書き換えたときも、その列を含む索引は並び直しが必要です。索引を増やすほど読み取りは速くなり、書き込みは確実に遅くなります。

容量も増えます。索引はそれ自体がデータなので、列の多い表にいくつも貼ると、索引の合計が本体と同じくらいの大きさになることもあります。よく使う索引はメモリにも常駐するので、そのぶん他のものが乗らなくなります。

主キーには、宣言した時点で索引が自動的に作られます。重複していないことを確かめるには、結局その列を探せる必要があるからです。重複を許さない制約も同じで、制約と索引はここで一体になっています。

使われていない索引は、書き込みを遅くしているだけの存在です。貼りっぱなしにせず、消す判断もします。索引はデータそのものではなく、データへの道順です。消してもデータは 1 件も減りませんし、あとから貼り直せます。効いているか分からない索引は、消して測ってみるのがいちばん早い確かめ方です。

性別に索引を貼っても、ほとんど効かない

貼れば速くなる、でもありません。効くかどうかは、その列で行がどれだけ絞れるかで決まります。

性別のように値が 2 種類しかない列を考えます。索引を引いて「該当する行のありか」を得ても、それが全体の半分あります。半分の行を、索引が示す場所を頼りにばらばらに読みに行くくらいなら、本体を頭から順に読んだほうが速い。飛び飛びに読むより、続けて読むほうがディスクにとって都合がよいからです。DB 自身もそう判断して、索引を使わないことがあります。削除フラグや、ほとんどが同じ値の状態列も同じです。

逆に候補になるのは、値の種類が多くて絞り込みが効く列です。検索条件によく出てくる列、他の表とつなぐときの相手側の列、並べ替えに使う列が該当します。

複数の列をまとめて 1 つの索引にすることもできます。このとき順番に意味があります。姓と名でこの順に並んだ電話帳から、名前だけを頼りに人を探すことはできません。同じように、先頭の列が条件に含まれていない検索では、その索引は使われません。