RDBのインデックス完全に理解する

RDBのインデックス完全に理解します

Transcript

1. RDBのインデックス完全に理解する 多田 信洋
2. 自己紹介 • 株式会社Fusic • 福岡の会社でRuby書いてます • 27歳
3. RDB(リレーショナルデータベース)とは • 業務でデータを保存する方法の一つ • データを複数の表として管理 ユーザー名 ユーザーID 多田 信洋 1 田中太郎 2 織田信長 3 趣味 ユーザーID ベース 1 将棋 1 マラソン 2
4. RDBのインデックスって何？ • なんか貼ると早くなるらしい？ • 本の目次のようなもの？
5. RDBの仕組みその① • ある100万人いる巨大な表がある • 表をSQLで検索する • SELECT name WHERE name=’多田信洋’ FROM Users • この時100万行全てを読まずに見つける方法は？ ユーザー名 ユーザーID 多田 信洋 1 田中太郎 2 織田信長 3 … …
6. 二分探索 今から私が1から100までの数字を思い浮かべます 「答えはXですか？」という質問を繰り返して答えを見つけてください。 答えがXでなかった場合は私は「もっと大きいです。」「もっと小さいで す。」と返答します 最大何回の質問で答えを見つけることができますか？ シンキングタイム！10秒
7. 答え 7回
8. 解説 • 最初に「答えは50ですか？」と聞く • それより大きい数だった場合は「75ですか？」 • 小さい数だった場合は「25ですか？」 • このように質問することで質問の度に答えの候補が半分になる • このような計算のオーダーを𝑂(log 𝑁) と言ったりする 50 25 75
9. データベースでは？ • データベースは先ほどの問題のように二分探索ができる！ • 文字列の場合も文字コードによって順序が定義されます • 二分探索をするために必要な条件 • ソートされたデータ • データベースは追加と削除が必要！ 平衡二分探索木
10. 平衡二分探索木 • 平衡二分探索木と呼ばれる構造がある • 偏らず二分探索ができて尚且つ追加も削除も重くないすごい構造 • 詳細は省略
11. B木(B-Tree) • 二分探索木の代わりにN分探索木としたもの • 多くのデータベースではこの構造を使われている
12. なぜ二分探索木ではないのか？ • 純粋な計算回数を見れば二分探索木に軍配が上がる • しかし、データベースはディスクに保存されるためディスクの特性で 適したアルゴリズムが変わる 種類 1Kbit読み出しにかかる時間 読み出しまでにかかる時間 メインメモリ 5 ナノ秒(200Gbps) 80ナノ秒 HDD 1 マイクロ秒(1Gbps) 15ミリ秒 SSD 0.5マイクロ秒(2Gbps) 150マイクロ秒
13. 赤黒木 • 平衡二分探索木の実装の一つ • B-Treeとの親和性が高く、メモリ上に展開する場合はこちらを使う
14. RDBの説明はここまで！ 少し具体的な話に入ります
15. 先にN+1問題の話 • SQLクエリをたくさん発行するととても重い • 実行計画を立てる • コネクションの確立 • などなどSQLクエリごとに実行される処理のオーバーヘッドが重い
16. いよいよインデックス • ただRDBのインデックスといった場合はほとんどB-Tree インデックス を指す • B-Treeインデックスを貼ると指定したカラムで二分探索ができるよう になる
17. いろいろと実験 • テーブルの説明 • 作成したテーブルは簡単な構造 • Idとnameとageのみの単純なテーブル • レコードは1000万件 • レコードの挿入は1万レコード2000msから2500ms程度 • インデックスをnameとageに作成した後では1万レコード3000msから 3500msほど
18. 使ったSQL テーブル作成 挿入
19. 全件取得 • SELECT COUNT(*) FROM staffs; • 2.17s • CREATE INDEX age ON staffs; (年齢にインデックスを貼る) • 15.6s • もう一度SELECT COUNT(*) FROM staffs; • 2.04s
20. 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 • インデックスを貼ると早くなった
21. 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 ロケールの設定次第では前方一致にインデックスが効かない
22. LIKE句を使った比較2 • SELECT COUNT(*) FROM staffs WHERE name LIKE ‘ab%cd’ • これは’ab%’までの前方一致が効く 0.09s • SELECT COUNT(*) FROM staffs WHERE name LIKE ‘%cd’ • 後方一致には全く効果がない 2.04s
23. EXPLAINの話 • EXPLAIN SELECT name FROM staffs WHERE age=50 ORDER BY name; • どのインデックスを使うか？ • どのように実行をするつもりか？がわかる。 • 詳細は置いといて、costを比較すれば改善したかどうかがわかる
24. 複合インデックス① • インデックス無し • EXPLAIN SELECT name FROM staffs WHERE age=50 ORDER BY name; • age=50 のものを探索 した後にソートしている。
25. 複合インデックス② • ageにインデックスを貼った場合 • EXPLAIN SELECT name FROM staffs WHERE age=50 ORDER BY name; • age=50 のものをインデックスを用いて探索 した後にソートしている。 • Costは128993から77094に下がっている
26. 複合インデックス③ • age,nameの順にインデックスを貼った場合 • EXPLAIN SELECT name FROM staffs WHERE age=50 ORDER BY name; • age=50 かつソート済みのものをインデックスを用いて探索 するだけ。 • Costは128993から30322に下がっている
27. JOINした時にインデックスは効いているか？ • JOINでも効いている！インデックス有無で2.5秒から100ミリ秒に • JOINした時の実行計画はちょっと複雑！
28. 軽い説明 • Nested Loop • JOIN時に1つ目のテーブルの各列に合わせて2つ目のテーブルを全捜査 • Bitmap Heap Scan • 動的にBitmapを構築してスキャン • Bitmap Index Scan • Indexを用いてBitmapスキャンする • OR演算など簡単でないクエリで使用
29. サブクエリでインデックスは効いているか？ • 効いてる！ 2.4秒から100ミリ秒に • 以下は年齢が最大のユーザの数を出すクエリ
30. 結局パフォーマンスを気にするのはいつか ら？ • 1000万レコードでレコード数を出すクエリが2秒 • JOINの形式が悪ければ計算量は2乗になるため3000レコードから • N+1に気をつけるべきなのもそのあたり • 他の様々なクエリが厳しくなるのは数万〜数十万レコード
31. 効果的なインデックスの貼り方は？ • インデックスが効果をなす場面の一例を見てきた • ここからはより効率的なインデックスの貼り方を
32. カーディナリティ • その列が取り得る値の種類 • 曜日であれば7、月であれば12、名前ならたくさん
33. インデックスを貼る場合に効果のある列 • カーディナリティが高い(値の種類数が多い) • 辞書で自分の調べたい単語のヒントが「あ」から始まることのみなのに「あ」 のページ数が多いと残念ですね？ • 複合列に貼るとカーディナリティは高まる • 値の分布の偏りが少ない • 十分にWHEREで検索結果を減らしている
34. やめた方がいいこと • カーディナリティの低い列にインデックスを貼る • 良かれと思って貼ったのに書き込みが遅くなるだけ • SQL中で演算を行う • SELECT * FROM staffs WHERE age*2=100; • ↑のように演算が入るとインデックスが効かない • 暗黙の型変換を行う • 上記の演算と同じ
35. どこから始めていいのかわからない • スロークエリをまずは見てみよう！ • 遅いクエリを検索する機能がDBにはある • 大抵設定が必要
36. 遅いクエリを特定したら • N+1おきていない？ • バルクインサートなどで早くならない？ • EXPLAINをしてみてみよう！ • INDEXで改善しそうか？
37. まとめ • RDBは二分探索をしている • 二分探索をするためにキーを決めてINDEXを貼る • カーディナリティが高い（値の種類が多い）場所にINDEXを貼ると良 い
38. ご清聴ありがとうございました