インデックスは、どのようにして利用するのか

Question

Mysqlでテーブルに索引(インデックス)を設定することで特に情報量の多いテーブルなどで役に立つけれど、情報を更新するときに索引の設定をし直す必要があるということは、書籍を見て理解できたのですが、
実際、この索引をSQL文でどのようにして利用するのか、ということが書いておらずいまいちピンときません。
インデックスは、どのようにして利用するのでしょうか。

Answer 1

インデックスを利用するのはDBMSです。

SQLで条件（結合や絞り込み）を指定した場合に、インデックス（プライマリーキーも含め）が無いと、基本データを全件捜査します。

インデックスがある場合、インデックスで指定している項目が順序良く並んでいるので、インデックスの形式に応じたアルゴリズムにより、全件捜査せずとも特定できるような仕組みになっています。

この仕組みを知った上で、インデックスを設定し、インデックスが使用されるようなSQL文を組み立てる必要があります。

Comments

[aaaaaaaa]

ご回答ありがとうございました。
つまり、インデックスを指定していない場合、あらゆるカラムを検索してしまうけれどインデックスを指定しているカラムがあれば、指定しているインデックスだけを
検索するので、すべて検索する必要がないということでしょうか。

[sazi]

いえ、あらゆるカラムを検索というわけではなく、実表の該当カラムを検索しますが、そのカラムの並びが保証されていない為、全件を検索するということです。
インデックスがあれば並びは保証されているので、btree形式であれば二分探索木等のアルゴリズムですべてを検索する必要が無くなります。
というか、すべてを検索しないでいいようにインデックスによる検索機能が作られ、その検索アルゴリズムは様々です。

Answer 2

基本的には、インデックスは自動的に利用されます。

ただ、「こっちを使ったほうが有効なのにインデックスが効かない」というときには、USE INDEXなどで手動指定もできます。

Answer 3

インデックスが内部的に使用されるのは、
SQLに ORDER BY または WHERE、JOIN句のON条項などに対象カラムがあるときです。
その他、外部キー制約を使う場合に、対象カラムを第一カラムとしたインデックスが必要です。

実際、この索引をSQL文でどのようにして利用するのか

実例を知りたいということでしたら典型的な利用ケースとしては

• メッセージボックスから「未読」のメッセージを検索
• ユーザ検索で、最終ログイン日時カラムを作成し、1週間以内にログインしたアクティブユーザを検索
• オークションなどで締め切り日時が近い順に並べ替える
• ブログ記事の一覧から、各記事にカテゴリIDを設定し、特定のカテゴリの投稿のみを一覧する
• ブログ記事のカテゴリ一覧で、各カテゴリごとの投稿数を同時に表示する(JOIN)

こういうものがあります。

いまいちピンときません

例えば10万語収録されている英和辞典(紙の本)で、
単語がABC順ではなくデタラメな順序に収録されていたら、
目的の単語を見つけるには最悪全てのページを開かなければなりません。

インデックスとは全ての単語をABC順など規則に沿って整理した目次を添付し、
目的の語句に最短でたどり着けるようにするものです。

INDEXの作成は(たいていは)手動で行いますが、
使用するかは必要に応じてSQLプランナーシステムが自動的に決定します。
EXPLAIN文を使う事でプランナーがどのインデックスを使うか事前に知ることができます。
INDEXが使われない場合、テーブルの行数が10万行かある程度のサイズを超えたあたりから
メモリに検索途中の結果が入りきらなくなり、応答速度が不安定になって来ます。