データベースのINDEX理解に向けて

私自身「しっかり理解してるぜ！」と胸が張れるようなものではないですが、
ちょっとこれはいいたとえではないかと思ったので書いてみます。

イメージをつかむための例えなので、厳密な技術的内容としては突っ込みどころがあるものです。こういうものだと過信はしないでください＆突っ込みお待ちしてます。

大漢和辞典とは

世界最大の漢字辞典として有名な辞典で、「本体が12巻」「索引だけで2巻」という莫大なものです。学校の図書室にあったから「うわでけえ」「なんだこの漢字」と遊びに使った思い出のある方もいるのではないかと。
今回例えに出すのにちょうどよかったのは

データ数が適度に多い
索引だけで本ができるほど索引に意義と種類がある

という点でした。

データベースのINDEXとは

「索引だよ」
「・・・お、おう」
というやり取りは結構ある気がします。

基本概念としてはその通りなのですが、ここで普通の国語辞典のあいうえお索引を思い浮かべると少し腑に落ちない気がします。
「なんかINDEXっていろんな列についてない？　でも国語辞典で索引ってそんないろいろあるっけ？」
なんて思うのは普通だと思います。（これでそんなバカはお前だけだ　とかだと悲しい）

大漢和辞典の「索引」実例

5万字以上の収録がある大漢和辞典。もちろん索引がありまして、索引を使うと「その漢字は何巻の何ページにあるよ」と教えてくれます。
その索引、何種類かあるのですが、わかりやすいのは

音読み
訓読み
画数

の索引あたりでしょう。これによって例えば「音」という字なら
「音読みが『オン』の漢字」「訓読みが『おと』の漢字」「画数が9画の漢字」といった探し方が可能です。「ある辞典に対し、複数の索引がある」実例ですね

もし大漢和辞典がデータベースだったら？

「大漢和辞典」というデータベースがあって、「漢字」というテーブルがあって、
漢字テーブルには「字」「音読み」「訓読み」「画数」「部首」「用例」・・・といった属性があることでしょう。
その上で、前項の内容からすると「音読み」「訓読み」「画数」にそれぞれINDEXが張ってある　という状態ですね。

INDEXの威力

「なんか遅いと思ったらこのテーブルINDEX張り忘れてるじゃねえか！」
なんてこと、データベースをある程度いじった方なら経験がおありではないでしょうか。

実感がわかない方は先ほどの大漢和辞典で「索引なしである漢字を探す」と考えてみてください。
収録数5万字以上で10冊以上に分かれている。索引なしでは何巻から探せばいいのかもわからない・・・何時間かかるやら。気の遠くなる作業です。
それが何らかの索引を使えば「何巻の何ページだよ」まで絞り込めるわけです。これなら数分で済みそうですね。数百倍の時間差でしょう。
実際にデータベースでも、INDEXの有無でSELECT文にて数百倍、数千倍の処理時間差があることは珍しくありません。

意味のない索引、INDEX

少し進んで、INDEXについて調べたりしたらきっと「INDEXはやたらめったらどこにでも張ればいいものではない」と言われていることに気づくでしょう。これはどういうことでしょうか。
INDEX、ひいては索引は「探すために使うもの」です。なので「そんな探し方しねえよ！」ってものを作っても意味がありません。

例えば先ほどの大漢和辞典。「文字の最後がハネかトメかハライか」なんて索引があったとしたらどうでしょう。
まず「とにかく最後がハライで終わる漢字を探したいんだ！」という需要はあまりなさそうです。
そしてその需要があったとして、この索引を使ったとしても、全体から3分の1程度にしか絞り込めません。「絞り込んで探しやすくする」という効果にも乏しいわけです。無駄ですね。

INSERTでINDEXは不利？

前項と関連してもう一つ「INDEXを張ると、INSERT速度が下がる」とは何でしょうか。
INSERTは要素の追加です。大漢和辞典に新しい漢字を一つ追加するとしましょう。
もし索引が一切なかったら、字を追加すれば終わりです。簡単ですね。
ですが実際には索引があるので、

収録する字を追加する
音読みの索引に新しい文字を追加する
訓読みの索引に新しい文字を追加する
画数の索引に新しい文字を追加する

と、索引ごとに直してあげる手間がかかります。ちゃんと索引も直してあげないと「索引が正しく仕事をしてくれない」ですからね。確かにINDEXがあればあるほどINSERT時にやらないといけないことが多いですね

なのでINDEXがたくさん張ってあるほど、INSERTが不利というのは事実です。
もちろんだからINDEXは悪なのではなく「INDEXが張ってあることによるSELECTのメリット」と「INDEXが張ってあることによるINSERTのデメリット」を比べて、メリットのほうが大きければ張ればいいのです。
上記からよく一般論として、

「検索対象によくなるけど、INSERTが少ないようなテーブルはINDEXを張るべき」
「INSERT頻度が高いテーブルでは慎重に」

といったことが言われるわけですね。

まとめ　イメージをつかむために

今回は大漢和辞典を例に出しましたが、「索引が複数種類ある本」であれば他の本や辞書でもいいかもしれません（主な材料、所要時間等で索引のあるレシピ本とか？　そんなものがあるだろうか）。
その本を思い浮かべて。「こういう項目を探すときに、索引があったら、なかったらどうなるだろう」「この本に追記をする際に、索引があったら、なかったらどうなるだろう」と考えてみてください。少しイメージが楽になるかもしれません。

拙い内容ですが、どなたかのお役に立てば幸いです。