インデックスのパフォーマンス調べてみた

2022/10月に行われた大LTでの発表資料です

イベントページはこちら
https://zli.connpass.com/event/261496/

Transcript

1. インデックスの パフォーマンス 調べてみた matumoto
2. 自己紹介 • ハンドルネーム：matumoto • 本名：松本響輝 • 学年：28期 • 趣味：イカᔦꙬᔨ • やってきた技術： ◦ ゲーム作り ◦ フロントエンド ◦ AtCoder 水💧 • Twitter：@matumoto_1234
3. インデックスについて
4. インデックスとは？ • インデックスとは、データの検索速度を向上させるために、どの行がどこにあるかを示した索引のこ と (https://www.techscore.com/tech/sql/15_01 より引用) • DBのテーブルに適切にインデックスを作ることで、パフォーマンス向上につながる • MySQLなどではCREATE TABLEなどの文でインデックスを指定できる • 例. CREATE TABLE people ( id INT, name VARCHAR(512), age INT, INDEX name_INDEX (name) )
5. インデックスがどう使われるのか？ • クエリに対応して、インデックスが自動で使われる • 例. ◦ peopleという名前の、こんなテーブルがあったとする id name age 1 matumoto 20 2 Aizu Taro 256 • SELECT id FROM people WHERE name = 'matumoto' というような、nameカラムに対しての検索クエリが きたとき、name_INDEXが使われる • SELECT idFROM people WHERE age = 20 というような、ageカラムに対しての検索クエリがきたらイン デックスは使われず、テーブル全体がそのまま読み込まれる
6. インデックスのパフォーマンス • インデックスは基本的に、ユニーク（重複がない）なもののほうがパフォーマンスが良い ◦ 例. PRIMARY KEYに基づくインデックスやUNIQUE制約のついたインデックスなど →なぜパフォーマンスが良いのか？（本題） →後述
7. インデックスの内部構造
8. インデックスを作るとどうなるか • インデックスを作ってもテーブル自体に変更が加わるわけではない • テーブルとは別にインデックス用の領域が取られ、まずはそこにアクセスする テーブル インデックス クエリ ソートとかはされていない！ テーブルの場所が効率よく検索で きるように保存されている
9. インデックスはどうなっているか • B-treeというようなデータ構造がよく使われている ◦ 厳密にはB+treeや、B*treeという改良版が使われることが多い
10. B-treeとは？ • B-treeは平衡探索木の一種 ◦ よくある、平衡二分探索木とかとは違って多分木 ◦ BはBinaryではなく、Balanceの略 ◦ よくデータベース管理システムや、ファイルシステムで使用される 5 70 2 1 3 8 6 20 82 91 71 85 97
11. B-treeの特徴 • B-treeの特徴 ◦ 完全に平衡になっている(根から任意の葉までのパスの長さが一定 ) ◦ ノードにいくつかの値を持つ ◦ 一つのノードにm個以下の枝があるものをオーダー mのB-treeと呼ぶ ◦ これはオーダー3のB-tree 5 70 2 1 3 8 6 20 82 91 71 85 97
12. 挿入操作でB-treeの平衡はどうやって保っているの？ • A. 気合い http://wwwa.pikara.ne.jp/okojisan/t23-java/index.html より図を引用
13. 削除操作でB-treeの平衡はどうやって保っているの？ • A. もちろん気合い http://wwwa.pikara.ne.jp/okojisan/t23-java/index.html より図を引用
14. B-treeの計算量 • B-treeの計算量 ◦ nを要素数とする ◦ 挿入：O(log n) ◦ 削除：O(log n) ◦ 検索：O(log n) • AVL木や、赤黒木といった平衡二分探索木より速い？ ◦ そんなことはなくて、遅い ◦ オーダーmのB-treeのノードを辿るときにO(m)回の値比較を行うので遅い ◦ データベース管理システムなどで使われるのは、「枝を辿るコスト」 >「値比較のコスト」な ため
15. B-treeの亜種 • B+treeというのが存在する ◦ 葉ノードがつながっており、範囲クエリに強い ◦ 葉ノードに実際のレコードが全て存在している ◦ MySQL/InnoDBなどで使われている https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html より図を引用
16. インデックスの パフォーマンス
17. インデックスのパフォーマンス • SQLクエリをEXPLAINすると表示される「type」 • 主なものとしては、以下がある ◦ const：PRIMARY KEYのインデックスやUNIQUEインデックスを使う。最速 ◦ eq_ref：JOINのときにPRIMARY KEYのインデックスやUNIQUEインデックスを使う ◦ ref：ユニークでないインデックスを使ったときの等価検索など ◦ range：インデックスを用いた範囲検索 (0 <= key <= 10を満たすkeyを検索するなど) ◦ index：フルインデックススキャン。インデックス全体を見る ◦ all：フルテーブルスキャン。インデックスが使用されていない • なぜユニークだと早くなる傾向にあるのか？
18. インデックスのパフォーマンス • 検索で遅くなるのは葉ノードの走査が大きな原因の一つとしてある • 検索対象がユニークなら、見つけ次第終了できるが、ユニークでない場合は他の 葉ノードを見る必要がある • 例. 20を見つけたとしても、20が他にあるかもしれないので葉ノードを辿る必要があ る https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html より図を引用
19. まとめ
20. まとめ • インデックスの内部構造はB-treeがベースになっていることが多くて、計算量は だ いたい O(log n) ◦ 範囲クエリでk個の要素がみつかるときは、 O(log n + k) 程度
21. ご静聴ありがとうございました