目次
sqlのwhere句で使用しているカラムにindexを張ると検索が早くなる理由
結論
イメージ
複合indexを張った場合にできるキーとアドレスのテーブルのイメージ
indexを張ると逆に処理が遅くなってしまう場合
キーがやたらと被っている場合のイメージ
indexは張れば張るだけ検索が早くなるわけではない
SQLのWHERE句で使用しているカラムにIndexを張ると検索が早くなる理由
結論
「Indexを付けたカラム（キー）」で二分探索ができるようになるから
イメージ
1
以下のように、商品テーブルがあったとする。
その商品テーブルの商品名カラムにIndexを張る。（商品名がキーとなる）
商品テーブル
- この時のキー（Indexを付けたカラム）の順番はバラバラ（昇順や降順に並べられていない）で、値が重複している可能性がある
- キー（Indexを付けたカラム）は、テーブル定義の「主キー」や「複合キー」とは違う意味
- アドレスはレコードの場所を示すため、重複しない
2
Indexを張ると、キー（Indexを張ったカラム）とアドレスのテーブルが作られる。
この時、キー（Indexを張ったカラム）は、昇順か降順に並べられて登録される。
（アドレスの順番はバラバラ）
これで二分探索を行う準備が整う。
Indexを張ったことにより作成された、キー（Indexを張った商品名カラム）とアドレスのテーブル
- あくまでもIndexを張ったら、二分探索をする準備（キーアドレスのテーブル作成）を行うだけ
4
「商品名が『ノート』のレコードが欲しい！」となった場合
キー（Indexを張った商品名カラム）とアドレスのテーブルに、キー（商品名）が昇順（五十音順）に並んでいるため、二分探索が使える。
つまり、テーブルを端から総舐めするより検索が早い！！
5
4でキー（Indexを張った商品名カラム）とアドレスのテーブルから「ノート」が見つかったら、「ノート」と一緒に登録されているアドレスを使って、商品テーブルのアドレスに飛ぶ。
このことにより、商品名が『ノート』のレコードが取得できる。
- インデックス検索は、キーを見つけたらキーに紐づくアドレスを使って、実際のテーブルのアドレスに飛ぶ
複合Indexを張った場合にできるキーとアドレスのテーブルのイメージ
キー1の塊の中で、キー2が昇順か降順になる
Indexを張ると、逆に処理が遅くなってしまう場合
キーがやたらと被っている場合や、まとまったデータを取得するときは、Indexを使うと使わないよりも遅くなってしまう場合がある
キーがやたらと被っている場合のイメージ
Index検索は、キーを見つけたらそれに紐づくアドレスを使って、実際のテーブルのアドレスに飛ぶという処理が必要
実際のテーブルのアドレスに飛ぶ回数が多くなると、処理が遅くなってしまう。
そのため、このような場合は、実際のテーブルに飛ぶという処理が発生しない（Indexを張らない）データ総舐めの方が早かったりする。
（実施のテーブルだけを使って、上から順に検索をかけて、見つけ次第データを抜いていくだけになる）
- Indexは、1個ずつのデータの取得は得意だが、まとまったデータの取得は苦手
- 例えば「テーブルのデータを半分ちょうだい」となったら、Indexを使わない方が処理が少ない分早かったりする
Indexは張れば張るだけ検索が早くなるわけではない
よくWHERE句で使っているカラムにIndexを張ると検索が早くなると言われる。
しかし、Indexを複数張っても基本的に1つしか使われない。（複合Indexは話が別だけど）
そのため、複合Indexではなく単独でIndexを張りまくると、使われないIndexが出てくる。
使われないIndexはただのゴミデータとなってしまう。
また、Indexを張ると張った分だけINSERT文やUPDATE文が必然的に遅くなる。
理由：INSERT文やUPDATE文を使うと、Indexのテーブルにも変更を加えなくてはいけない。
（Indexを張ると、張った分だけテーブルの数が増える）
以上のことから、Indexの仕組みを理解せずに「Indexを使うと早くなるので、全部のカラムにIndexを付けてしまおう！！＼(^o^)／」というのはよろしくない。
追記
Indexのキーとは
- テーブル定義のキー（主キー、複合キー）とは別もの
- テーブル定義の主キー以外で、Indexを張ったカラムのことをIndexのキーという
→そのため、Indexのキーはバラバラ＆重複している可能性がある
Indexを指定すると起きること
- Indexが指定されると、「Indexを指定したカラムのデータを、昇順または降順に並び替えて、二分探査をする準備をする」←これだけやる
→これで出来上がったデータのイメージが本とかの索引（Index）にそっくり
（Indexのキー＝検索に書かれている用語、アドレス＝ページ番号）
テーブル定義上のキー（主キー、複合キー）に暗黙的にIndexが張られる理由
- テーブル定義上のキー（主キー、複合キー）は、重複が許されない
そのため、重複していないかの確認をするために、データを昇順または降順に並べ替える（←並べ替えると、重複しているか否かが分かりやすくなるため）
この「並び替える」というのが、実際のIndexを張るのと同じことになるため、「テーブル定義上のキー（主キー、複合キー）は何もしなくてもIndexが張られる」ということになる
Indexをユニークになるように指定した場合
例1：
①テーブルを作成
②Indexを指定（オプションでIndexがユニークになるように指定）
③データをINSERT（←Indexを張ったカラムで、データが重複しているものもある）
④Indexをユニークになるように指定したのに、入れられたデータではユニークになっていないため、エラーが起こる
例2：
①テーブルを作成
②データをINSERT（←③でIndexを張る予定のカラムに、データが重複しているものもある）
③Indexを指定（オプションでIndexがユニークになるように指定）
④Indexをユニークになるように指定したのに、既に登録されているデータがユニークになっていないため、エラーが起きる
Indexを張っても意味がないものもある
- データが片手で数えられる量しかないものは、二分探索でデータを探しても旨味が無い（逆に遅くなってしまう）
そのため、最低でも両手で数えられるくらいの量のデータが無いと効果が無い - 最近のDBは賢いため、Indexが張ってあるが二分探索でデータを探しても旨味が無い（逆に遅くなる）ものは、勝手にIndexを使わずに頭から順に見て行って速度が速くなるようにしてくれる
言葉の意味（ニュアンス）
- 複合キー：複数のカラムを組み合わせて一つのキーとして扱うもの（苗字と名前を合わせてキーとするみたいな）
- データ設計で言うキー：候補キー（その値が分かればレコードを特定することができるもの。一つのカラムで決まる必要がなく、複合キーでもOK）
※候補キーの中から主キーを選ぶ。主キーの候補だから「候補キー」。 - Index関連で出てくるキー：検索キー（レコードの特定にまでは至らないかもしれないが、大体のデータの塊が得られるもの。こちらも一つである必要は無く、複合キーでもOK）
- データベースオブジェクトのIndex：検索キーとレコードのアドレスを持ち、二分探索ができるように検索キーの昇順（もしくは降順）でソートしたもの
→準備をするだけではあるが、検索キーとレコードのアドレスの情報はテーブルの情報と別にディスク上に書き出すので、大量更新があるとそれなりに重くなる
重複を許さないものはUnique制約。主キーには暗黙的に設定される（+ Not Null制約も）。複合キーは↑の通りで、ただ複数項目をキーとしているかだけなので無関係。