1. データベースのインデックスについてのまとめ(MySQL)

    恥ずかしながらインデックスについて知識が皆無だったので調べたことをまとめておきます。

    とはいっても奥は果てしなく深いので今回は


    • インデックスとは何か

    • メリット、デメリット

    • 使い方


    ぐらいの、にわか知識までとします。



    ?インデックスとは

    • 特定の絡むデータを複製し検索を行いやすくするもの。

    • 本の目次のようなもので、全ページ(全レコード)見なくても該当のページがわかる。

    • select文に特別な追加は必要なく、インデックスを貼ったカラムを検索対象としたselect文を実行するだけ。

    • 実行後は先にインデックスを検索し、その情報をもとにテーブルからデータを取得する。


    ?メリット、デメリット

    • インデックスを作成しておくと検索が早くなるが全てのカラムに対してインデックスを作成すればいいかというとそうではない。

    • インデックスを作成するとデータを追加、変更するたびにインデックスにも同じようにデータが追加されるため処理時間がかかるうえ、容量も食われる。


    ?効果的なインデックス

    • データ量が多く、格納値が異なりよく検索されるカラムに対しインデックスを作成すると効果的。

    • データ量の少ないテーブルや検索対象とならないカラム、フラグのような一意性の少ないカラムにインデックスを作成しても効果なし。

    • 読み込み中心のテーブルはインデックスが多くても問題ない。


    ?使い方

    • 主キーのカラムは自動的にインデックスが作成される。

    • 新たにインデックスを作成する場合は、
      CREATE INDEX index_name ON table_name (col_name);
      または,
      ALTER TABLE table_name ADD INDEX index_name (col_name);

    • 複合インデックスも可能。しかし効果が大きくでるような複数カラムを対象にしたインデックスはそれほど多くないと言われているらしい、、、
      CREATE INDEX index_name ON table_name (col1, col2,...);

    • インデックスの削除
      ALTER TABLE table_name DROP INDEX index_name;


    ?注意

    • ワイルドカードを使用したlike検索を行う場合、"%hoge%"のように前方に%をつけてしまうと結局全レコードを検索するため効果がなくなってしまう。

    • カラム値に対し演算して取り出すような検索も、一旦データを取り出してから演算後検索するため効果なし。(すべてのDBがこの限りではないみたい。)

    • 複合インデックスの場合、インデックス左側の検索要素がない検索は効果なし。(上記の例だとcol1)
      × SELECT * FROM table_name WHERE col2 = "hogehoge";
      ○ SELECT * FROM table_name WHERE col1 = "hogehoge";
      ○ SELECT * FROM table_name WHERE col1 = "hogehoge" AND col2 = "hogehoge";

    • 使われていないインデックスは更新が遅くなり、ディスクを消費するだけなので削除する。


     

    ざっくりとですが、以上です。実際に効果の検証や時間がかかったSQL発行時のログ等もできるようなので今後検証してみたいと思います。

    以下、参考にさせていただいたサイト様です。感謝です!

    http://dev.mysql.com/doc/refman/4.1/ja/create-index.html

    http://www.dbonline.jp/sqlite/index/index1.html

    http://tech.aktsk.jp/%E6%8A%80%E8%A1%93/database/20111130/228

    http://www.slideshare.net/simosako/rdb

    Posted by Shunsuke Hayashi on 2013年12月17日
    Categories database MySQL