WEB+DB PRESS plus の DB 本 3冊を読んで index を学んだ

Posted on Sep 24, 2015

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

Webエンジニアのためのデータベース技術[実践]入門 (Software Design plus)
- RDBMS としては mysql を題材にしている。
- インデックスから my.cnf の項目の説明や mysql のソースコードを読む所まで、 mysql をメインに書かれている。
理論から学ぶデータベース実践入門 ~リレーショナルモデルによる効率的なSQL (WEB+DB PRESS plus)
- RDBMS の構造や設計についても書かれていて、正規化理論や NULL について、またインデックスの設計戦略までカバーされている。
- 個別の RDBMS 製品に偏った記載はほぼ無い。
- 著者は、漢(オトコ)のコンピュータ道の人。
SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)
- SQL と実行計画を主軸に、効果的な SQL の書き方、実行計画の見方などについて書かれている。
- クエリと実行計画のサンプルには、 Oracle と PostgresQL のものが使われている。

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

B+Tree

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

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

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

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

データ件数が少ないテーブル
- index を参照してからデータ領域を参照することになるので、データ件数が少ない場合は逆にテーブルフルスキャンの方が速い。
値のばらつきが少ないカラム
- boolean なカラムのように取りうる値にバラつきが少ないカラムや、大半のレコードに同じ値が入っているカラムには、 index を貼っても効果が少ない。逆に、1件に絞り込めるような値が入っているカラムは、インデックスが有効に作用する。
- 値のバラつきのことをカーディナリティ（集合の濃度）という。

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

これは index 有効。

SELECT * WHERE name LIKE '高田%';

これは index 効かない。

SELECT * WHERE name LIKE '%祐一';

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

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

SELECT * WHERE col1 * 10 > 123;

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

SELECT * WHERE col1 > 123 / 10;

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

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

SELECT * WHERE col1 > 100 AND col2 = 3;

index オンリースキャン

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

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

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

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

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

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

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

まとめ

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