Tweet
Tweet

Slide 1

Slide 1 text

索引づけ ⼭本 祐輔 名古屋市⽴⼤学 データサイエンス研究科 [email protected] 第14回 データベース 〜 B+木 & ハッシュ索引

Slide 2

Slide 2 text

講義ノート https://bit.ly/3xqTSds

Slide 3

Slide 3 text

レポート課題の講評 0

Slide 4

Slide 4 text

⾔いたいこと ER図には主キーや多重度を 忘れずに書くこと 1. 2. 関係スキーマには参照制約を 忘れずに書くことを 3. FDダイアグラムは 関係スキーマではない

Slide 5

Slide 5 text

ER図：主キーや多重度制約を記すのを忘れるないこと 商品 商品ID 商品名 価格 発売⽇ ユーザ ユーザ名 ⽒名 email 住所 購⼊希望 登録⽇ 製造 メーカー 企業名 email TEL ショッピングサイトにおける「ユーザが購⼊希望の商品」 「商品の製造メーカー」の情報の管理

Slide 6

Slide 6 text

関係スキーマ 関係 関係スキーマ インスタンス = + 関係の名前と関係がもつ属性，⼀貫性制約に関する情報 (" A! , … , A" , {'! , … , '# }) 記法 関係名 属性 ⼀貫性制約の集合 " A! , … , A" ⼀貫性制約が⾃明 or 考慮しないとき

Slide 7

Slide 7 text

参照整合性制約 Q. 以下の関係スキーマをもつ5つの関係かなる データベースにおいて，定義すべき参照制約は？ 顧客( 顧客ID, ⽒名, 性別 ) 店舗( 店舗ID, 店舗, 住所 ) 商品( 商品ID, 商品名, 商品カテゴリID, 単価 ) 商品カテゴリ( 商品カテゴリID, カテゴリ名) 購買( 購買ID, 店舗ID, 顧客ID, 商品ID, 個数, 購買⽇)

Slide 8

Slide 8 text

参照整合性制約 A. 購買. 顧客ID ⊂ 顧客.顧客ID 購買. 店舗ID ⊂ 店舗.店舗ID 購買. 商品ID ⊂ 購買.商品ID 商品. 商品カテゴリID ⊂ 商品カテゴリ.商品カテゴリID

Slide 9

Slide 9 text

分解法に関する課題 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 1NF 営業担当 営業成績 FD4 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 BCNF 1NF FD4 で分解 次の分解ターゲット

Slide 10

Slide 10 text

分解法に関する課題 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 1NF ユーザ 連絡先 FD2 BCNF 商品 ユーザ 営業担当 メーカー FD3 FD1 1NF FD2 で分解 次の分解ターゲット

Slide 11

Slide 11 text

分解法に関する課題 1NF 商品 メーカー FD3 BCNF 商品 ユーザ 営業担当 メーカー FD3 FD1 BCNF FD3 で分解 商品 ユーザ 営業担当 FD1

Slide 12

Slide 12 text

分解法に関する課題 営業担当 営業成績 FD4 BCNF ユーザ 連絡先 FD2 BCNF 商品 メーカー FD3 BCNF BCNF 商品 ユーザ 営業担当 FD1 R1 ( 営業担当 , 営業成績 ) FD4 : 営業担当 → 営業成績 R3 ( 商品 , メーカー ) FD3 : 商品 → メーカー R2 ( ユーザ , 連絡先 ) FD4 : ユーザ → 連絡先 R4 ( 商品, ユーザ , 営業担当 ) FD1 : 商品, ユーザ→営業担当 FDダイアグラムだけでなく関係スキーマも書くこと

Slide 13

Slide 13 text

データベースの意義 ⼤規模データを 正しく適切に管理・処理 ⾼速な問い合わせ処理 = + … ⾼速なデータ処理は「索引」と「クエリ最適化」で実現

Slide 14

Slide 14 text

関係データの物理的な格納方式 1

Slide 15

Slide 15 text

関係データはどこに保存されている?（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

Slide 16

Slide 16 text

関係データはどこに保存されている?（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のような 記憶装置

Slide 17

Slide 17 text

ハードディスク(HDD) の動作 ⽬標位置まで磁気ヘッドを移動させ利⽤データを読み込む

Slide 18

Slide 18 text

関係データの物理的な格納イメージ（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 関係「商品」 テーブルは レコード単位に 分割され…

Slide 19

Slide 19 text

関係データの物理的な格納イメージ（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が該当レコードの格納位置を調べデータ取得

Slide 20

Slide 20 text

索引 2 Index

Slide 21

Slide 21 text

該当レコードの探索⽅法 (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 商品名 = ‘ブリッツ’;

Slide 22

Slide 22 text

該当レコードの探索⽅法 (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 商品名 = ‘ブリッツ’; 全部の場所にアクセスしてチェックするのは⼤変 記憶装置の各場所を順にアクセスしてチェック

Slide 23

Slide 23 text

本における索引 索引 [ア⾏] ⼀貫性制約 47, 100 異状 220 インスタンス 11, 22, 33 … [カ⾏] 外部キー 50 外部結合 100 関係 22, 44, 63 … 100ページ 索引で見たいキーワードが載っているページ番号を探せば 全ページを調べる手間は省ける

Slide 24

Slide 24 text

データベースにおける索引 属性「単価」の索引 単価 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

Slide 25

Slide 25 text

索引と探索コスト 商品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万件の索引キー 索引づけ 探したい値を ⾒ていく… サイズが⼤きいと索引と⾔えども探すのは⼤変…

Slide 26

Slide 26 text

素朴な⾼速化⼿法 - 2分探索（1/3） ソート済み配列から欲しい値を高速に見つける手法 ⼿順1. 配列の真ん中の位置の値をチェック． その値が探索キーより⼩さければ、 配列の右半分を新たな検索対象配列とする． その値が探索キーより⼤きければ、 配列の左半分を新たな検索対象配列とする． ⼿順3. 配列の欲しい値が⾒つかるまで ⼿順1-2を繰り返す． ⼿順2. その値が探索キーであればそこで終了．

Slide 27

Slide 27 text

素朴な⾼速化⼿法 - 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回の配列アクセスで欲しい値にたどり着く

Slide 28

Slide 28 text

素朴な⾼速化⼿法 - 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回の配列アクセスで探索完了

Slide 29

Slide 29 text

B+木 3 B-plus tree

Slide 30

Slide 30 text

データベースの索引探索に 2分探索を利⽤する際の問題点 1. 索引の構築にかかるコスト大 2. 範囲探索に難がある 3. できるなら探索速度UPしたい DBの更新の度に配列をソートする必要あり 「Xより⼤きくYより⼩さい」検索には不向き O(log2 N) の探索コストをより⼩さくできれば嬉しい

Slide 31

Slide 31 text

B+⽊ 更新コストが比較的小さく、探索効率もよい 索引用の木形式のデータ構造 属性値に4をもつ レコードのリスト … 属性値に9をもつ レコードのリスト 属性値に16をもつ レコードのリスト 1 16 9 4 25 36 49 81 64 100 121 144 169 196 索引キー（検索対象となる属性の値） …

Slide 32

Slide 32 text

B+⽊ 属性値に1をもつ レコードのリスト … B+⽊によって 構築された索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に4をもつ レコードのリスト 更新コストが比較的小さく、探索効率もよい 索引用の木形式のデータ構造

Slide 33

Slide 33 text

B+⽊ 属性値に1をもつ レコードのリスト … B+⽊によって 構築された索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に4をもつ レコードのリスト 更新コストが比較的小さく、探索効率もよい 索引用の木形式のデータ構造 レコードに対する索引

Slide 34

Slide 34 text

B+⽊ 属性値に1をもつ レコードのリスト … B+⽊によって 構築された索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に4をもつ レコードのリスト 更新コストが比較的小さく、探索効率もよい 索引用の木形式のデータ構造 索引に対する索引

Slide 35

Slide 35 text

B+⽊ 属性値に1をもつ レコードのリスト … B+⽊によって 構築された索引 64 25 100 9 196 1 4 9 16 25 36 49 64 81 100 121 144 169 144 属性値に4をもつ レコードのリスト 属性値に4をもつ レコードのリスト 更新コストが比較的小さく、探索効率もよい 索引用の木形式のデータ構造 索引の索引に対する索引 索引に対する索引を木構造で保持

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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以上

Slide 39

Slide 39 text

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以上

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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. 値が⾒つかればそれに紐付くレコードを取得

Slide 42

Slide 42 text

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. 上限値が⾒つかるまで葉ノード右にたどりレコードを取得

Slide 43

Slide 43 text

B+の⻑所 探索効率がよい 値の探索にかかる時間はO(logd N) 範囲検索が可能 葉ノードをたどるだけでOK 索引の追加・削除が比較的容易 ⽊の構造を動的に変更可能 関係データベースでは最もよく利⽤される索引はB+⽊ dを3以上にすれば ⼆分探索より⾼速 B+⽊への要素の追加・削除⽅法は 参考書等を参考に

Slide 44

Slide 44 text

ハッシュ索引 4 Hash index

Slide 45

Slide 45 text

B+⽊を使ったレコード探索 B+木を使っても多少計算時間はかかる 学籍番号 cxxxx002 B+⽊ 記憶装置 学籍番号cxxx5000 のレコード 場所1 学籍番号cxxxx002 のレコード 場所100 学籍番号cxxxx001 のレコード 場所 4999 場所 100 計算時間O(logd N)

Slide 46

Slide 46 text

ハッシュ索引 検索値からレコード格納場所を直接求める索引 学籍番号 cxxxx001 cxxxx002 cxxx5000 ハッシュ関数 1 100 4999 ハッシュ値 記憶装置 学籍番号cxxx5000 のレコード 場所1 学籍番号cxxxx002 のレコード 場所100 学籍番号cxxxx001 のレコード 場所 4999 ハッシュ関数の計算時間はO(1)で超⾼速 f 計算時間O(1) ハッシュ関数を用いて 範囲検索はできない…

Slide 47

Slide 47 text

ハッシュ関数とその例 代表的なハッシュ関数 § 剰余演算関数mod: 整数dで値を割った際の剰余を返す ハッシュ関数 § 任意の値を別の（⻑さが短い）値に変換するための関数 § 同じ⼊⼒に対しては同じ出⼒を返す § 計算時間が短い

Slide 48

Slide 48 text

ハッシュ索引の⻑所と短所 長所 § 値の探索時間はO(1) で超⾼速 § 索引のサイズがB+⽊より⼩さい 短所 § 範囲検索ができない § 適切なハッシュ関数を選ぶ必要あり § 複数の異なるキーが同じハッシュ値になる「衝突」 が起きると，計算時間が悪化する ハッシュ索引は完全⼀致検索を⾼速に⾏い時に使う

Slide 49

Slide 49 text

索引づけのためのSQL 5 SQL for indexing

Slide 50

Slide 50 text

索引づけのためのSQL CREATE INDEX 索引名 ON テーブル名（索引の対象属性） USING 索引の種類; CREATE INDEX product_price_idx ON product (price) USING btree; 構⽂ 例 Productテーブルのprice属性が対象 B+⽊を使う 索引は検索の⾼速化に⾮常に効果的 記憶領域を使うのでむやみに索引を作るのは避ける

Slide 51

Slide 51 text

回 実施日 トピック 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