データベース14: B+木 & ハッシュ索引

1. 関係データの物理的な格納方式
2. 索引
3. B+木
4. ハッシュ索引
5. 索引づけのためのSQL

講義ノートURL
https://dbnote.hontolab.org/content/indexing/01.html

Transcript

1. 索引づけ ⼭本 祐輔 名古屋市⽴⼤学 データサイエンス研究科 yusuke_yamamoto@acm.org 第14回 データベース 〜 B+木 & ハッシュ索引
2. 講義ノート https://bit.ly/3xqTSds
3. レポート課題の講評 0
4. ⾔いたいこと ER図には主キーや多重度を 忘れずに書くこと 1. 2. 関係スキーマには参照制約を 忘れずに書くことを 3. FDダイアグラムは 関係スキーマではない
5. ER図：主キーや多重度制約を記すのを忘れるないこと 商品 商品ID 商品名 価格 発売⽇ ユーザ ユーザ名 ⽒名 email 住所 購⼊希望 登録⽇ 製造 メーカー 企業名 email TEL ショッピングサイトにおける「ユーザが購⼊希望の商品」 「商品の製造メーカー」の情報の管理
6. 関係スキーマ 関係 関係スキーマ インスタンス = + 関係の名前と関係がもつ属性，⼀貫性制約に関する情報 (" A! , … , A" , {'! , … , '# }) 記法 関係名 属性 ⼀貫性制約の集合 " A! , … , A" ⼀貫性制約が⾃明 or 考慮しないとき
7. 参照整合性制約 Q. 以下の関係スキーマをもつ5つの関係かなる データベースにおいて，定義すべき参照制約は？ 顧客( 顧客ID, ⽒名, 性別 ) 店舗( 店舗ID, 店舗, 住所 ) 商品( 商品ID, 商品名, 商品カテゴリID, 単価 ) 商品カテゴリ( 商品カテゴリID, カテゴリ名) 購買( 購買ID, 店舗ID, 顧客ID, 商品ID, 個数, 購買⽇)
8. 参照整合性制約 A. 購買. 顧客ID ⊂ 顧客.顧客ID 購買. 店舗ID ⊂ 店舗.店舗ID 購買. 商品ID ⊂ 購買.商品ID 商品. 商品カテゴリID ⊂ 商品カテゴリ.商品カテゴリID
9. 分解法に関する課題 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 1NF 営業担当 営業成績 FD4 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 BCNF 1NF FD4 で分解 次の分解ターゲット
10. 分解法に関する課題 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 1NF ユーザ 連絡先 FD2 BCNF 商品 ユーザ 営業担当 メーカー FD3 FD1 1NF FD2 で分解 次の分解ターゲット
11. 分解法に関する課題 1NF 商品 メーカー FD3 BCNF 商品 ユーザ 営業担当 メーカー FD3 FD1 BCNF FD3 で分解 商品 ユーザ 営業担当 FD1
12. 分解法に関する課題 営業担当 営業成績 FD4 BCNF ユーザ 連絡先 FD2 BCNF 商品 メーカー FD3 BCNF BCNF 商品 ユーザ 営業担当 FD1 R1 ( 営業担当 , 営業成績 ) FD4 : 営業担当 → 営業成績 R3 ( 商品 , メーカー ) FD3 : 商品 → メーカー R2 ( ユーザ , 連絡先 ) FD4 : ユーザ → 連絡先 R4 ( 商品, ユーザ , 営業担当 ) FD1 : 商品, ユーザ→営業担当 FDダイアグラムだけでなく関係スキーマも書くこと
13. データベースの意義 ⼤規模データを 正しく適切に管理・処理 ⾼速な問い合わせ処理 = + … ⾼速なデータ処理は「索引」と「クエリ最適化」で実現
14. 関係データの物理的な格納方式 1
15. 関係データはどこに保存されている?（1/2） 商品ID 商品 単価 登録日 PA001 はーいお茶 130 2018/10/25 PA002 きのこの里 230 2018/11/17 PB050 タケノコの山 170 2019/12/25 PC008 タケノコの山 200 2019/12/28 … … PZ015 滝子の里山 360 2023/07/07 関係「商品」 データベース DBS
16. 関係データはどこに保存されている?（2/2） 商品ID 商品 単価 登録日 PA001 はーいお茶 130 2018/10/25 PA002 きのこの里 230 2018/11/17 PB050 タケノコの山 170 2019/12/25 PC008 タケノコの山 200 2019/12/28 … … PZ015 滝子の里山 360 2023/07/07 関係「商品」 HDDやSSDのような 記憶装置
17. ハードディスク(HDD) の動作 ⽬標位置まで磁気ヘッドを移動させ利⽤データを読み込む
18. 関係データの物理的な格納イメージ（1/2） 商品ID PA001 商品名 はーいお茶 単価 130 登録⽇ 2018/10/25 商品ID PA002 商品名 きのこの⾥ 単価 230 登録⽇ 2018/11/17 商品ID PZ015 商品名 滝⼦の⾥⼭ 単価 360 登録⽇ 2023/07/07 … 商品ID 商品 単価 登録日 PA001 はーいお茶 130 2018/10/25 PA002 きのこの里 230 2018/11/17 … … PZ015 滝子の里山 360 2023/07/07 関係「商品」 テーブルは レコード単位に 分割され…
19. 関係データの物理的な格納イメージ（2/2） HDDのような 記憶装置 記憶装置の特定の 場所に格納される 商品ID PA001 商品名 はーいお茶 単価 130 登録⽇ 2018/10/25 アドレス1 商品ID PA002 商品名 きのこの⾥ 単価 230 登録⽇ 2018/11/17 アドレス2 商品ID PZ015 商品名 滝⼦の⾥⼭ 単価 360 登録⽇ 2023/07/07 アドレス10000 … 問合せ時は、DBが該当レコードの格納位置を調べデータ取得
20. 索引 2 Index
21. 該当レコードの探索⽅法 (1/2) 商品ID 商品名 単価 登録日 PA001 はーいお茶 130 2018/10/25 PA002 きのこの里 230 2018/11/17 PB050 タケノコの山 170 2019/12/25 PC008 午前の紅茶 150 2019/12/28 … … PZ015 たきのこの里山 360 2023/07/07 「商品」列の値を上からチェック 概念的には SELECT * FROM 商品 WHERE 商品名 = ‘ブリッツ’;
22. 該当レコードの探索⽅法 (2/2) HDDのような 記憶装置 商品ID PA001 商品名 はーいお茶 単価 130 登録⽇ 2018/10/25 アドレス1 商品ID PS715 商品名 ブリッツ 単価 150 登録⽇ 2018/11/17 アドレス50000 商品ID PZ935 商品名 たきのこの⾥⼭ 単価 360 登録⽇ 2023/07/07 アドレス1000000 … … SELECT * FROM 商品 WHERE 商品名 = ‘ブリッツ’; 全部の場所にアクセスしてチェックするのは⼤変 記憶装置の各場所を順にアクセスしてチェック
23. 本における索引 索引 [ア⾏] ⼀貫性制約 47, 100 異状 220 インスタンス 11, 22, 33 … [カ⾏] 外部キー 50 外部結合 100 関係 22, 44, 63 … 100ページ 索引で見たいキーワードが載っているページ番号を探せば 全ページを調べる手間は省ける
24. データベースにおける索引 属性「単価」の索引 単価 130 記憶装置の場所 1 150 3, 10, 54 170 50 200 8, 6 230 2 … 商品ID PA001 商品名 はーいお茶 単価 130 登録⽇ 2018/10/25 アドレス1 商品ID PA002 商品名 きのこの⾥ 単価 230 登録⽇ 2018/11/17 商品ID PZ 商品名 滝 単価 36 登録⽇ 20 アドレス10 … HDDのような記憶装置 属性値に対応するレコードの場所を記した特殊なデータを 余分に構築しデータ探索を高速化 アドレス2
25. 索引と探索コスト 商品ID 商品 単価 PA001 はーいお茶 130 PA002 きのこの里 230 PB050 タケノコの山 170 PC008 午前の紅茶 150 … … PZ015 たきのこの里山 360 単価 130 記憶装置の場所 1 150 3, 10, 54 170 50 200 8, 6 230 2 … 25000 19820925 1億件のレコード 1万件の索引キー 索引づけ 探したい値を ⾒ていく… サイズが⼤きいと索引と⾔えども探すのは⼤変…
26. 素朴な⾼速化⼿法 - 2分探索（1/3） ソート済み配列から欲しい値を高速に見つける手法 ⼿順1. 配列の真ん中の位置の値をチェック． その値が探索キーより⼩さければ、 配列の右半分を新たな検索対象配列とする． その値が探索キーより⼤きければ、 配列の左半分を新たな検索対象配列とする． ⼿順3. 配列の欲しい値が⾒つかるまで ⼿順1-2を繰り返す． ⼿順2. その値が探索キーであればそこで終了．
27. 素朴な⾼速化⼿法 - 2分探索（2/3） ソート済み配列から欲しい値を高速に見つける手法 130 150 170 230 270 300 1 2 3 6 4 5 360 7 索引キー 配列番号 (例: 単価) 欲しい値 = 170 130 150 170 1 2 3 170 3 230より⼩ 150より⼤ 2回の配列アクセスで欲しい値にたどり着く
28. 素朴な⾼速化⼿法 - 2分探索（3/3） ソート済み配列から欲しい値を高速に見つける手法 130 150 170 230 270 300 1 2 3 6 4 5 360 7 130 150 170 1 2 3 270 300 6 5 360 7 130 1 170 3 270 5 360 7 230より⼩ 230より⼤ 150より⼩ 150より⼤ 300より⼩ 300より⼤ 最悪でもLog2 N回の配列アクセスで探索完了
29. B+木 3 B-plus tree
30. データベースの索引探索に 2分探索を利⽤する際の問題点 1. 索引の構築にかかるコスト大 2. 範囲探索に難がある 3. できるなら探索速度UPしたい DBの更新の度に配列をソートする必要あり 「Xより⼤きくYより⼩さい」検索には不向き O(log2 N) の探索コストをより⼩さくできれば嬉しい
31. B+⽊ 更新コストが比較的小さく、探索効率もよい 索引用の木形式のデータ構造 属性値に4をもつ レコードのリスト … 属性値に9をもつ レコードのリスト 属性値に16をもつ レコードのリスト 1 16 9 4 25 36 49 81 64 100 121 144 169 196 索引キー（検索対象となる属性の値） …
32. B+⽊ 属性値に1をもつ レコードのリスト … B+⽊によって 構築された索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に4をもつ レコードのリスト 更新コストが比較的小さく、探索効率もよい 索引用の木形式のデータ構造
33. B+⽊ 属性値に1をもつ レコードのリスト … B+⽊によって 構築された索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に4をもつ レコードのリスト 更新コストが比較的小さく、探索効率もよい 索引用の木形式のデータ構造 レコードに対する索引
34. B+⽊ 属性値に1をもつ レコードのリスト … B+⽊によって 構築された索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に4をもつ レコードのリスト 更新コストが比較的小さく、探索効率もよい 索引用の木形式のデータ構造 索引に対する索引
35. B+⽊ 属性値に1をもつ レコードのリスト … B+⽊によって 構築された索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に4をもつ レコードのリスト 更新コストが比較的小さく、探索効率もよい 索引用の木形式のデータ構造 索引の索引に対する索引 索引に対する索引を木構造で保持
36. B+⽊の特徴 – 葉ノード 属性値に1をもつ レコードのリスト … B+⽊による索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に4をもつ レコードのリスト § 葉ノードは隣接した葉ノードと接続 § 最低 e/2 個、最大e個の索引キーで埋める 下のケースは e = 3
37. B+⽊の特徴 – 中間ノード (1/2) 属性値に1をもつ レコードのリスト … B+⽊による索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に4をもつ レコードのリスト § 最低d個、最大2d個の索引キーで埋める 下のケースは d = 1
38. B+⽊の特徴 – 中間ノード (2/2) 属性値に1をもつ レコードのリスト … B+⽊による索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に4をもつ レコードのリスト § 索引キーkの左にはk未満のキーを含む子ノードへのポインタを持つ 下のケースは d = 1 § 右にはk以上のキーを含む子ノードへのポインタを持つ 64未満 64以上
39. B+⽊の特徴 – 根ノード 属性値に1をもつ レコードのリスト … B+⽊による索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に4をもつ レコードのリスト § 中間ノードと同様のルールでポインタを張る § 最低1個、最大2d個の索引キーで埋める 25以上100未満 100以上
40. B+⽊の最⼤の特徴 属性値に1をもつ レコードのリスト … B+⽊によって 構築された索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に4をもつ レコードのリスト どの葉ノードも根からの距離が同じ B+⽊のBは“balanced”のB
41. B+⽊を使った完全⼀致検索（例: 81の値をもつレコードを探したい） 属性値に1をもつ レコードのリスト … B+⽊によって 構築された索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に81をもつ レコードのリスト ターゲットは64以上 ターゲットは25以上100未満 … 1. 根から葉ノードまでたどる 2. 葉ノード内を右にたどり探索値を探す 3. 値が⾒つかればそれに紐付くレコードを取得
42. B+⽊を使った範囲検索（例: 16以上40未満の値をもつレコードの検索） 属性値に1をもつ レコードのリスト … B+⽊ 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に16をもつ レコードのリスト 属性値に36をもつ レコードのリスト ③ターゲット上限の40まで右にたどる ①ターゲット下限25未満 ②ターゲット下限9以上 属性値に25をもつ レコードのリスト … 1. 下限の値に着⽬して根から葉ノードまでたどる 2. 下限値が⾒つかるまで葉ノード内を右にたどる 3. 上限値が⾒つかるまで葉ノード右にたどりレコードを取得
43. B+の⻑所 探索効率がよい 値の探索にかかる時間はO(logd N) 範囲検索が可能 葉ノードをたどるだけでOK 索引の追加・削除が比較的容易 ⽊の構造を動的に変更可能 関係データベースでは最もよく利⽤される索引はB+⽊ dを3以上にすれば ⼆分探索より⾼速 B+⽊への要素の追加・削除⽅法は 参考書等を参考に
44. ハッシュ索引 4 Hash index
45. B+⽊を使ったレコード探索 B+木を使っても多少計算時間はかかる 学籍番号 cxxxx002 B+⽊ 記憶装置 学籍番号cxxx5000 のレコード 場所1 学籍番号cxxxx002 のレコード 場所100 学籍番号cxxxx001 のレコード 場所 4999 場所 100 計算時間O(logd N)
46. ハッシュ索引 検索値からレコード格納場所を直接求める索引 学籍番号 cxxxx001 cxxxx002 cxxx5000 ハッシュ関数 1 100 4999 ハッシュ値 記憶装置 学籍番号cxxx5000 のレコード 場所1 学籍番号cxxxx002 のレコード 場所100 学籍番号cxxxx001 のレコード 場所 4999 ハッシュ関数の計算時間はO(1)で超⾼速 f 計算時間O(1) ハッシュ関数を用いて 範囲検索はできない…
47. ハッシュ関数とその例 代表的なハッシュ関数 § 剰余演算関数mod: 整数dで値を割った際の剰余を返す ハッシュ関数 § 任意の値を別の（⻑さが短い）値に変換するための関数 § 同じ⼊⼒に対しては同じ出⼒を返す § 計算時間が短い
48. ハッシュ索引の⻑所と短所 長所 § 値の探索時間はO(1) で超⾼速 § 索引のサイズがB+⽊より⼩さい 短所 § 範囲検索ができない § 適切なハッシュ関数を選ぶ必要あり § 複数の異なるキーが同じハッシュ値になる「衝突」 が起きると，計算時間が悪化する ハッシュ索引は完全⼀致検索を⾼速に⾏い時に使う
49. 索引づけのためのSQL 5 SQL for indexing
50. 索引づけのためのSQL CREATE INDEX 索引名 ON テーブル名（索引の対象属性） USING 索引の種類; CREATE INDEX product_price_idx ON product (price) USING btree; 構⽂ 例 Productテーブルのprice属性が対象 B+⽊を使う 索引は検索の⾼速化に⾮常に効果的 記憶領域を使うのでむやみに索引を作るのは避ける
51. 回 実施日 トピック 1 04/14 ガイダンス：データベースを使わない世界 2 04/21 データベースの概念 3 04/28 関係データモデル 4 05/12 SQL (1/3) 5 05/19 SQL (2/3) 6 05/26 SQL (3/3) 7 06/02 SQL演習 – レポート課題1 8 06/09 実体関連モデル (1/3) 9 06/16 実体関連モデル (2/3) 10 06/23 実体関連モデル (3/3) 11 06/30 正規化 (1/2) 12 07/07 正規化 (2/2) 13 07/14 データベース設計演習 – レポート課題2 14 07/21 索引付け 15 07/28 授業の振り返り or 発展的話題 16 08/04 期末試験 今後の予定 52