Tweet
Tweet

Slide 1

Slide 1 text

索引づけ ⼭本 祐輔 名古屋市⽴⼤学 データサイエンス学部 yusuke_yamamoto@acm.org 第14回 データベース 2024年7月22日 〜 B+木 & ハッシュ索引

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 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 5

Slide 5 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 6

Slide 6 text

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

Slide 7

Slide 7 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 8

Slide 8 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 9

Slide 9 text

索引 2 Index

Slide 10

Slide 10 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 11

Slide 11 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 12

Slide 12 text

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

Slide 13

Slide 13 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 14

Slide 14 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 15

Slide 15 text

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

Slide 16

Slide 16 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 17

Slide 17 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 18

Slide 18 text

B+木 3 B-plus tree

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 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 22

Slide 22 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 23

Slide 23 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 24

Slide 24 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 25

Slide 25 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 26

Slide 26 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 27

Slide 27 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 28

Slide 28 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 29

Slide 29 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 30

Slide 30 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 31

Slide 31 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 32

Slide 32 text

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

Slide 33

Slide 33 text

ハッシュ索引 4 Hash index

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

回 実施日 トピック 1 04/15 ガイダンス：データベースを使わない世界 2 04/22 データベースの概念 3 04/29（祝） 関係データモデル 4 05/13 SQL (1/3) 5 05/20 SQL (2/3) 6 05/27 SQL (3/3) 7 06/03 SQL演習 – レポート課題1 8 06/10 実体関連モデル (1/3) 9 06/17 実体関連モデル (2/3) 10 06/24 実体関連モデル (3/3) 11 07/01 正規化 (1/2) 12 07/08 正規化 (2/2) 13 07/15（祝） データベース設計演習 – レポート課題2 14 07/22 索引付け 15 07/29 NoSQL 16 08/05 期末試験 今後の予定 41