Tweet
Tweet

Slide 1

Slide 1 text

RDBのインデックス完全に理解する 多田 信洋

Slide 2

Slide 2 text

自己紹介 • 株式会社Fusic • 福岡の会社でRuby書いてます • 27歳

Slide 3

Slide 3 text

RDB(リレーショナルデータベース)とは • 業務でデータを保存する方法の一つ • データを複数の表として管理 ユーザー名 ユーザーID 多田 信洋 1 田中太郎 2 織田信長 3 趣味 ユーザーID ベース 1 将棋 1 マラソン 2

Slide 4

Slide 4 text

RDBのインデックスって何？ • なんか貼ると早くなるらしい？ • 本の目次のようなもの？

Slide 5

Slide 5 text

RDBの仕組みその① • ある100万人いる巨大な表がある • 表をSQLで検索する • SELECT name WHERE name=’多田信洋’ FROM Users • この時100万行全てを読まずに見つける方法は？ ユーザー名 ユーザーID 多田 信洋 1 田中太郎 2 織田信長 3 … …

Slide 6

Slide 6 text

二分探索 今から私が1から100までの数字を思い浮かべます 「答えはXですか？」という質問を繰り返して答えを見つけてください。 答えがXでなかった場合は私は「もっと大きいです。」「もっと小さいで す。」と返答します 最大何回の質問で答えを見つけることができますか？ シンキングタイム！10秒

Slide 7

Slide 7 text

答え 7回

Slide 8

Slide 8 text

解説 • 最初に「答えは50ですか？」と聞く • それより大きい数だった場合は「75ですか？」 • 小さい数だった場合は「25ですか？」 • このように質問することで質問の度に答えの候補が半分になる • このような計算のオーダーを𝑂(log 𝑁) と言ったりする 50 25 75

Slide 9

Slide 9 text

データベースでは？ • データベースは先ほどの問題のように二分探索ができる！ • 文字列の場合も文字コードによって順序が定義されます • 二分探索をするために必要な条件 • ソートされたデータ • データベースは追加と削除が必要！ 平衡二分探索木

Slide 10

Slide 10 text

平衡二分探索木 • 平衡二分探索木と呼ばれる構造がある • 偏らず二分探索ができて尚且つ追加も削除も重くないすごい構造 • 詳細は省略

Slide 11

Slide 11 text

B木(B-Tree) • 二分探索木の代わりにN分探索木としたもの • 多くのデータベースではこの構造を使われている

Slide 12

Slide 12 text

なぜ二分探索木ではないのか？ • 純粋な計算回数を見れば二分探索木に軍配が上がる • しかし、データベースはディスクに保存されるためディスクの特性で 適したアルゴリズムが変わる 種類 1Kbit読み出しにかかる時間 読み出しまでにかかる時間 メインメモリ 5 ナノ秒(200Gbps) 80ナノ秒 HDD 1 マイクロ秒(1Gbps) 15ミリ秒 SSD 0.5マイクロ秒(2Gbps) 150マイクロ秒

Slide 13

Slide 13 text

赤黒木 • 平衡二分探索木の実装の一つ • B-Treeとの親和性が高く、メモリ上に展開する場合はこちらを使う

Slide 14

Slide 14 text

RDBの説明はここまで！ 少し具体的な話に入ります

Slide 15

Slide 15 text

先にN+1問題の話 • SQLクエリをたくさん発行するととても重い • 実行計画を立てる • コネクションの確立 • などなどSQLクエリごとに実行される処理のオーバーヘッドが重い

Slide 16

Slide 16 text

いよいよインデックス • ただRDBのインデックスといった場合はほとんどB-Tree インデックス を指す • B-Treeインデックスを貼ると指定したカラムで二分探索ができるよう になる

Slide 17

Slide 17 text

いろいろと実験 • テーブルの説明 • 作成したテーブルは簡単な構造 • Idとnameとageのみの単純なテーブル • レコードは1000万件 • レコードの挿入は1万レコード2000msから2500ms程度 • インデックスをnameとageに作成した後では1万レコード3000msから 3500msほど

Slide 18

Slide 18 text

使ったSQL テーブル作成 挿入

Slide 19

Slide 19 text

全件取得 • SELECT COUNT(*) FROM staffs; • 2.17s • CREATE INDEX age ON staffs; (年齢にインデックスを貼る) • 15.6s • もう一度SELECT COUNT(*) FROM staffs; • 2.04s

Slide 20

Slide 20 text

Whereによる絞り込み age<10名レコードは100万件中9万件 • SELECT COUNT(*) FROM staffs WHERE age<10; (INDEXなし) • 2.09s • CREATE INDEX age ON staffs(age); • SELECT COUNT(*) FROM staffs WHERE age<10; • 0.28s • インデックスを貼ると早くなった

Slide 21

Slide 21 text

LIKE句による比較 • SELECT COUNT(*) FROM staffs WHERE name LIKE ’a%’; • 2.045s • CREATE INDEX name ON staffs (name); (インデックスを貼る) • SELECT COUNT(*) FROM staffs WHERE name LIKE ’a%’; • 1.972s (効果なし！！) • CREATE INDEX name2 ON staffs (name text_pattern_ops); • SELECT COUNT(*) FROM staffs WHERE name LIKE ’a%’; • 0.198s ロケールの設定次第では前方一致にインデックスが効かない

Slide 22

Slide 22 text

LIKE句を使った比較2 • SELECT COUNT(*) FROM staffs WHERE name LIKE ‘ab%cd’ • これは’ab%’までの前方一致が効く 0.09s • SELECT COUNT(*) FROM staffs WHERE name LIKE ‘%cd’ • 後方一致には全く効果がない 2.04s

Slide 23

Slide 23 text

EXPLAINの話 • EXPLAIN SELECT name FROM staffs WHERE age=50 ORDER BY name; • どのインデックスを使うか？ • どのように実行をするつもりか？がわかる。 • 詳細は置いといて、costを比較すれば改善したかどうかがわかる

Slide 24

Slide 24 text

複合インデックス① • インデックス無し • EXPLAIN SELECT name FROM staffs WHERE age=50 ORDER BY name; • age=50 のものを探索 した後にソートしている。

Slide 25

Slide 25 text

複合インデックス② • ageにインデックスを貼った場合 • EXPLAIN SELECT name FROM staffs WHERE age=50 ORDER BY name; • age=50 のものをインデックスを用いて探索 した後にソートしている。 • Costは128993から77094に下がっている

Slide 26

Slide 26 text

複合インデックス③ • age,nameの順にインデックスを貼った場合 • EXPLAIN SELECT name FROM staffs WHERE age=50 ORDER BY name; • age=50 かつソート済みのものをインデックスを用いて探索 するだけ。 • Costは128993から30322に下がっている

Slide 27

Slide 27 text

JOINした時にインデックスは効いているか？ • JOINでも効いている！インデックス有無で2.5秒から100ミリ秒に • JOINした時の実行計画はちょっと複雑！

Slide 28

Slide 28 text

軽い説明 • Nested Loop • JOIN時に1つ目のテーブルの各列に合わせて2つ目のテーブルを全捜査 • Bitmap Heap Scan • 動的にBitmapを構築してスキャン • Bitmap Index Scan • Indexを用いてBitmapスキャンする • OR演算など簡単でないクエリで使用

Slide 29

Slide 29 text

サブクエリでインデックスは効いているか？ • 効いてる！ 2.4秒から100ミリ秒に • 以下は年齢が最大のユーザの数を出すクエリ

Slide 30

Slide 30 text

結局パフォーマンスを気にするのはいつか ら？ • 1000万レコードでレコード数を出すクエリが2秒 • JOINの形式が悪ければ計算量は2乗になるため3000レコードから • N+1に気をつけるべきなのもそのあたり • 他の様々なクエリが厳しくなるのは数万〜数十万レコード

Slide 31

Slide 31 text

効果的なインデックスの貼り方は？ • インデックスが効果をなす場面の一例を見てきた • ここからはより効率的なインデックスの貼り方を

Slide 32

Slide 32 text

カーディナリティ • その列が取り得る値の種類 • 曜日であれば7、月であれば12、名前ならたくさん

Slide 33

Slide 33 text

インデックスを貼る場合に効果のある列 • カーディナリティが高い(値の種類数が多い) • 辞書で自分の調べたい単語のヒントが「あ」から始まることのみなのに「あ」 のページ数が多いと残念ですね？ • 複合列に貼るとカーディナリティは高まる • 値の分布の偏りが少ない • 十分にWHEREで検索結果を減らしている

Slide 34

Slide 34 text

やめた方がいいこと • カーディナリティの低い列にインデックスを貼る • 良かれと思って貼ったのに書き込みが遅くなるだけ • SQL中で演算を行う • SELECT * FROM staffs WHERE age*2=100; • ↑のように演算が入るとインデックスが効かない • 暗黙の型変換を行う • 上記の演算と同じ

Slide 35

Slide 35 text

どこから始めていいのかわからない • スロークエリをまずは見てみよう！ • 遅いクエリを検索する機能がDBにはある • 大抵設定が必要

Slide 36

Slide 36 text

遅いクエリを特定したら • N+1おきていない？ • バルクインサートなどで早くならない？ • EXPLAINをしてみてみよう！ • INDEXで改善しそうか？

Slide 37

Slide 37 text

まとめ • RDBは二分探索をしている • 二分探索をするためにキーを決めてINDEXを貼る • カーディナリティが高い（値の種類が多い）場所にINDEXを貼ると良 い

Slide 38

Slide 38 text

ご清聴ありがとうございました