WEB+DB PRESS Plus の DB 本 3冊を読んで Index を学んだ

シルバーウィークに、この3冊を読んだ。

特に index について知りたかったのでその辺まとめとく。

B+Tree

RDBMS の index で最も使われている。
データ構造が木みたいに、ルート、ブランチ、リーフから成っている。
B+Tree は B-Tree と違って、必ずリーフノードに値を持つ。ブランチノードがコンパクトになり、検索の効率が良いらしい。

index を貼ることのデメリット

  • 更新処理が遅くなる
  • データ量が増える

index をはらない方が良いケース

  • データ件数が少ないテーブル

    • index を参照してからデータ領域を参照することになるので、データ件数が少ない場合は逆にテーブルフルスキャンの方が速い。
  • 値のばらつきが少ないカラム

    • boolean なカラムのように取りうる値にバラつきが少ないカラムや、大半のレコードに同じ値が入っているカラムには、 index を貼っても効果が少ない。逆に、1件に絞り込めるような値が入っているカラムは、インデックスが有効に作用する。
    • 値のバラつきのことをカーディナリティ(集合の濃度)という。

index は前方一致に対してのみ有効

これは index 有効。

1
SELECT * WHERE name LIKE '高田%';

これは index 効かない。

1
SELECT * WHERE name LIKE '%祐一';

演算や関数にかけてしまうと index は効かない

col1 に index を貼っていても、下記のクエリは index が効かない。

1
SELECT * WHERE col1 * 10 > 123;

index を効かせたい場合は、こうする。

1
SELECT * WHERE col1 > 123 / 10;

マルチカラムインデックスでは、カラムの順番も意味がある

下記のクエリに対して効果的な index は、(col1, col2) ではなく (col2, col1) な index である。
index が前方一致な事を思い出す。

1
SELECT * WHERE col1 > 100 AND col2 = 3;

index オンリースキャン

index を貼ると、index を見てから、データ領域を参照する事になる。
しかし、index の参照だけで取得できるようなクエリだと、データ領域への参照が必要無いのでさらに高速になる。
(col1, col2) というindex を貼ってあるテーブル tbl に対して、下記のクエリを発行すると、index の参照だけで済む。

1
SELECT col1, col2 FROM tbl WHERE col1 = 1 AND col2 > 100;

集計関数の場合も、うまく使えば index だけで済ませられる。

1
SELECT COUNT(1) FROM tbl WHERE col1 = 1 AND col2 > 100;

B+Tree はキーの順にソートされて格納されている

index のキーはソートされているので、name に index があるテーブルに、下記のようなクエリを発行すると範囲検索とソートの両方にインデックスが使われるので高速。

1
SELECT * FROM tbl WHERE name LIKE '高田%' ORDER BY name;

まとめ

index という概念が導入される前の hiveQL を長年使っていたので、 SQL は書けるけど index に対しては理解弱かったので、勉強になった。
頭の中で B+Tree をイメージすると、効果的な index をイメージしやすい気がする。
あと、前方一致の特徴などからも やはり index は、本の索引をイメージするとわかりやすい。