データベース14 - データベース構造とインデックス
Transcript of データベース14 - データベース構造とインデックス
データベース第14回
データベース構造とインデックス
1
2015年7⽉9⽇(⽊) 7・8時限担当:奥 健太
トランザクションとデータベース構造編回 ⽇付 テーマ12 6/25 トランザクションと同時実⾏制御13 7/2 トランザクションと障害回復14 7/9 データベース構造とインデックス
2
トランザクションとデータベース構造編での学習⽬標
3
トランザクションを理解する
データベースの同時実⾏制御の仕組みを理解する
データベースの障害回復⽅法を理解する
効率的なデータアクセスのためのデータベース構造およびインデックスを理解する
効率的なデータアクセス機構
4
vs.
復習
効率的なデータアクセス機構
5
2分経過…
ユーザは1分も待ってくれない…
検索中…
効率的なデータアクセス機構
6
0.01秒後!
⽬的のものが瞬時に検索される© 1996-2015, Amazon.com, Inc. or its affiliates
効率的なデータアクセス機構回 ⽇付 テーマ12 6/25 トランザクションと同時実⾏制御13 7/2 トランザクションと障害回復14 7/9 データベース構造とインデックス
7
データベース構造,ファイル編成,インデックスについて学ぶ
ファイル編成本⽇の講義で学ぶこと
8
インデックス
結合処理のアルゴリズム
⼤量のデータの管理
9
記憶領域の格納効率
検索効率
⼤量のデータを効率良く管理せよ
10
フィールド
レコード
ファイル
ファイル,レコード,フィールド
11
会員番号 ⽒名1 ⿃⾕3 関本4 上本
1 ⿃⾕3 関本4 上本
ディスク関係
ファイル 記録媒体に保存されたひとまとまりのデータやプログラム
レコード データの記録単位であり,1件のデータを表す
フィールド データの記録単位であり,レコードにおける⼀つ⼀つの項⽬を表す
順次編成ファイル
12
ディスク上にレコードの順序通りに作られる編成 テープや巻物のイメージ
レコード1 レコード2 レコード3レコード4
レコードn書込み順序
読出し順序
ファイルの先頭位置
次の書込み位置記憶領域の使⽤効率が良い
順次編成ファイル
13
順次アクセスしかできない
データの更新,削除,挿⼊に⼿間がかかる
レコード1 レコード2 レコード3
レコード1 レコード2 レコード3
レコード1 レコード3
直接編成ファイル
14
レコードをある単位ごとにディスクの場所におき,その番地を直接指定することで読書きができる編成
レコード1レコード2
...レコード123
...レコードn
...
レコード先頭位置1バイト⽬
101バイト⽬
12,201バイト⽬
100バイト固定⻑
レコード123はどこにある?100 × (123 - 1) + 1 = 12,201バイト⽬
直接編成ファイル
15
固定⻑であるため,記憶領域に無駄が⽣じる
順次アクセスとランダムアクセスができるレコード1
レコード2...
レコード123
レコード1レコード2
最⼤レコード⻑を超えたレコードには対応不可
可変⻑でデータを⾼速に検索する⽅法は?
16
本の索引
17
索引編成ファイル レコードのキー値から作成した索引(インデックス)を
もつ編成
レコードのキー値 格納先の位置1 1バイト⽬2 83バイト⽬3 183バイト⽬
...123 9,629バイト⽬
インデックスファイル
レコード1 レコード2 レコード3
レコード123レコードn
順次編成ファイルレコード123はどこにある?
検索効率が⽐較的良い
レコードの挿⼊や削除が⽐較的容易
インデックス構築BEFORE & AFTER
19
BEFORE
CREATE INDEX idx_user_id ON tweet_kansai(user_id)
インデックス構築
14.24 sec
ハッシュインデックス
20
キー値をもとにハッシュ関数の計算により,レコードアドレスを求め,そのアドレスにレコードを格納する⽅法
51バイト⽬512バイト⽬
2,376バイト⽬
レコード246レコード123
......
...レコード569
123 512246 51569 2,376
ハッシュ関数
ハッシュ関数の種類
21
⾃乗・中央法キー値を⾃乗して中央の数字をアドレスとする246 × 246 = 60516
除算法キー値をある値で割った余りをアドレスとする246 ÷ 61 = 4 あまり 2
基数変換法キー値の基数を変換して下の桁をアドレスとする(246)16=2 × 162 + 4 × 161 + 6 × 160 = 582
ハッシュの衝突
22
246 2
490 2ハッシュ関数
ハッシュの衝突(コンフリクト)
2バイト⽬20バイト⽬
レコード246レコード569
......
...レコード490 ...
...空き領域
空き領域へ
シノニム
ハッシュインデックス
23
検索効率がデータ量に依存しない
シノニムの発⽣が増えると,アクセス効率が低下する
範囲検索には利⽤できない
ソートには利⽤できない
B⽊インデックス
24
例;格納するレコード
25
キー値 キー以外2 …10 …20 …22 …51 …60 …71 …89 …90 …98 …
B⽊インデックス
26
効率良くデータを格納し,引き出すための多分⽊構造
(60,…)
(20,…)
(2,…) (10,…) (22,…) (51,…) (71,…) (90,…) (98,…)
(89,…)
ノード
27
ルートノード下位ノードへのポインタをもつ
中間ノード下位ノードへのポインタをもつ
リーフノード最下位ノード
ルートノードと中間ノード
(60,…)
k個のレコードとk+1個の下位ノードへのポインタをもつ ルートノードのレコード数はd個 (d ≦ k) とする レコードは,キー値とキー以外のデータから構成される ノード内のレコードはキー値でソートされている 下位ノードへのポインタは,最初のレコードの前,最後
のレコードの後,レコード間に格納される
レコードレコード
レコードレコード下位ノードへのポインタ
ノード
28
B⽊はバランス⽊
29
⽊の⾼さ(ルートノードからリーフノードまでのノード数)は⼀定
すべてのリーフノードの深さが⼀定になるように,レコードの挿⼊,更新,削除に伴って,インデックスが動的に再構成される
⾼々,⽊の⾼さ分のアクセス回数で済む
B⽊(参照操作)22を取り出そう
30
(60,…)
(20,…)
(2,…) (10,…) (22,…) (51,…) (71,…) (90,…) (98,…)
(89,…)
22と⽐較
22と⽐較
60より⼩さいのでこちら
20より⼤きいのでこちら
22と⽐較
B⽊(登録操作)76を登録しよう
31
(60,…)
(20,…)
(2,…) (10,…) (22,…) (51,…) (71,…) (90,…) (98,…)
(89,…)
76と⽐較
76と⽐較60より⼤きいのでこちら
89より⼩さいのでこちら
76を登録(76,…)
71の後ろが空いているので
B⽊(登録操作)93を登録しよう
32
(60,…)
(20,…)
(2,…) (10,…) (22,…) (51,…) (71,…) (90,…) (98,…)
(89,…)
93と⽐較
93と⽐較60より⼤きいのでこちら
89より⼤きいのでこちら
93と⽐較90と98の間が空いていない
B⽊(登録操作)93を登録しよう
33
(71,…) (90,…) (98,…)
(89,…)
93と⽐較
中央のレコードを上位ノードへ
(93,…)
93を登録
中央より左側のレコードは現在のリーフノードに残す
(98,…)
中央より右側のレコードは新しいリーフノードに移す
98を移動
(90,…)
上位ノードに新しいリーフノードへのポインタを格納
B⽊(登録操作)80を登録しよう
(60,…)
(71,…) (75,…) (90,…) (92,…)
(89,…) (93,…)
(98,…) (99,…)
34
B⽊(登録操作)80を登録しよう
(60,…)
(71,…) (75,…) (90,…) (92,…)
(89,…) (93,…)
(98,…) (99,…)
80と⽐較
75と⽐較
89を登録
(75,…)
(89,…)
35
B⽊(登録操作)80を登録しよう
(60,…)
(71,…) (75,…) (90,…) (92,…)
(89,…) (93,…)
(98,…) (99,…)
80と⽐較
75と⽐較
89を登録
(75,…)
(89,…)
(93,…)
36
93を移動
B⽊(登録操作)80を登録しよう
(60,…)
(71,…) (75,…) (90,…) (92,…)
(89,…) (93,…)
(98,…) (99,…)
80と⽐較
75と⽐較
89を登録
(75,…)
(89,…)
(93,…)
37
(80,…)
80を登録
93を移動
B⽊インデックス
38
範囲検索では,すべてのノードを上下にたどる必要があり⾮効率
2〜71の値を取り出したい
B+⽊インデックス
39
B⽊インデックスを改良
B+⽊インデックス索引部
データ部
索引部とデータ部に分けた構造
60
20
(2,…) (10,…)
(20,…) (22,…)
(60,…) (71,…)
(89,…) (90,…)
89
(51,…) (98,…)
索引部
41
索引部
ルートノードと中間ノードには,キー値とポインタのみを格納
データ部
42
データ部
すべてのレコードがリーフノードに格納される 隣同⼠のリーフノード間でポインタによって結合される
B+⽊インデックス
43
2〜71の値を取り出したい
隣のリーフノードをたどることにより,範囲検索の効率が良い
インデックス構築BEFORE & AFTER
44
BEFORE
AFTER
14.24 sec
0.01 sec
SHOW INDEX FROM 【テーブル名】
45
BEFORE
AFTERuser_idにインデックスが作成されている
BTREE = B⽊
主キーのidには既にインデックスが作成されている
EXPLAIN 【SELECT⽂】
46
BEFORE
AFTER
ALL: フルテーブルスキャン(インデックス未使⽤)
ref: UNIQUEでないインデックスを使った等価検索
インデックスidx_user_idを使⽤
結合処理のアルゴリズム
47
SELECT * FROM novel JOIN publisherON novel.pub_id = publisher.id;
等結合
48
novel_id title writer_id pub_id year id name101秘密 1 1003 2001 1003⽂藝春秋102⽩夜⾏ 1 1004 2002 1004集英社103容疑者Xの献⾝ 1 1003 2008 1003⽂藝春秋104模倣犯 2 1001 2005 1001新潮社
... ... ... ... ... ... ...
novel_id
title writer_id
pub_id year
101秘密 1 1003 2001102⽩夜⾏ 1 1004 2002103容疑者Xの献⾝ 1 1003 2008104模倣犯 2 1001 2005
...... ... ... ...
novelid name
1001新潮社1002講談社1003⽂藝春秋1004集英社1005岩波書店
publisher
⼆つのテーブルで条件に合致するもののみを結合
結合演算が最も時間のかかる演算
⼊れ⼦結合
49
..
(1) 駆動表の各⾏について,内部表を1⾏ずつスキャンし,結合条件に合致するかチェック(2) 条件に合致していれば,結合した⾏を出⼒
駆動表または外部表
内部表
novel_id
title writer_id
pub_id
year
101秘密 1 1003 2001102⽩夜⾏ 1 1004 2002103容疑者Xの献⾝ 1 1003 2008104模倣犯 2 1001 2005105⽕⾞ 2 1001 19981061Q84 3 1001 2009107⾵の歌を聴け 3 1002 2004108海辺のカフカ 3 1001 2002109ノルウェイの森 3 1002 1987110使命と魂のリミット 1 1006 2010
novelid name
1001新潮社1002講談社1003⽂藝春秋1004集英社1005岩波書店
publisher
id name
1001新潮社1002講談社1003⽂藝春秋1004集英社1005岩波書店
novel_id
title writer_id
pub_id
year
101秘密 1 1003 2001102⽩夜⾏ 1 1004 2002103容疑者Xの献⾝ 1 1003 2008104模倣犯 2 1001 2005105⽕⾞ 2 1001 19981061Q84 3 1001 2009107⾵の歌を聴け 3 1002 2004108海辺のカフカ 3 1001 2002109ノルウェイの森 3 1002 1987110使命と魂のリミット 1 1006 2010
インデックスによる結合
50
novel
(1) 駆動表の各⾏について,内部表からインデックスをたどって,結合キーが合致する⾏を検索(2) 検索された⾏を結合した⾏を出⼒
駆動表または外部表
内部表
publisher
内部表のidに対するインデックス
id name
1001新潮社1002講談社1003⽂藝春秋1004集英社1005岩波書店
novel_id
title writer_id
pub_id
year
101秘密 1 1003 2001102⽩夜⾏ 1 1004 2002103容疑者Xの献⾝ 1 1003 2008104模倣犯 2 1001 2005105⽕⾞ 2 1001 19981061Q84 3 1001 2009107⾵の歌を聴け 3 1002 2004108海辺のカフカ 3 1001 2002109ノルウェイの森 3 1002 1987110使命と魂のリミット 1 1006 2010
ハッシュ結合
51
novel
(1) ⽚⽅のテーブルをスキャンし,結合キーに対し,ハッシュ関数を適⽤することで,ハッシュテーブルを作成(2) もう⼀⽅のテーブルをスキャンし,結合キーがハッシュテーブルに存在するかチェック
id hash1001 201002 401003 601004 801005 100
ハッシュテーブル
publisher
MySQLではサポート外
マージ結合
52
id name
1001新潮社1002講談社1003⽂藝春秋1004集英社1005岩波書店
novel_id
title writer_id
pub_id
year
104模倣犯 2 1001 2005105⽕⾞ 2 1001 19981061Q84 3 1001 2009108海辺のカフカ 3 1001 2002107⾵の歌を聴け 3 1002 2004109ノルウェイの森 3 1002 1987101秘密 1 1003 2001103容疑者Xの献⾝ 1 1003 2008102⽩夜⾏ 1 1004 2002110使命と魂のリミット 1 1006 2010
novel
pub_idでソート済み
idでソート済み
...
....
(1) あらかじめ両⽅のテーブルを結合キーでソート(2) ソートされたテーブルの各値を⽐較しながら,結合条件をチェック
publisher
MySQLではサポート外
EXPLAIN 【SELECT⽂】
53
Block Nested Loop:⼊れ⼦結合の改良版
ファイル編成まとめ
54
インデックス
結合処理のアルゴリズム
まとめ
55
⼊れ⼦結合インデックスによる結合ハッシュ結合マージ結合
ハッシュインデックスB⽊インデックスB+⽊インデックス
順次編成ファイル直接編成ファイル索引編成ファイル
本⽇学習したキーワード〜トランザクションとデータベース構造編〜
56
2相ロッキング 縮退相 ハッシュ関数ACID特性 順次編成ファイル ハッシュ結合Atomicity(原⼦性) 障害回復 ハッシュの衝突B+⽊インデックス 除算法 バランス⽊B⽊インデックス ⼈的障害 ビフォアイメージConsistency(整合性) 成⻑相 ファイルDurability(耐久性) ダーティリード ファントムリードIsolation(隔離性) ダンプファイル フィールドWALプロトコル チェックポイント マージ結合アフタイメージ 中間ノード 待合せグラフ⼊れ⼦結合 直接編成ファイル リーフノードインデックス 直列化可能性 両⽴性⾏列インデックスによる結合 データ部 ルートノードインデックスファイル データベースダンプ レコード基数変換法 データベースバックアップ ロールバック共有ロック デッドロック ロールフォワード更新の喪失 同時実⾏制御 ロギングコミット トランザクション ログ(ジャーナル)索引部 トランザクション障害 ログファイル索引編成ファイル ノンリピータブルリード ロッキング⾃乗・中央法 媒体障害 ロックシステム障害 排他ロックシノニム ハッシュインデックス
これまでに学習したキーワード〜トランザクションとデータベース構造編〜
57
2相ロッキング 縮退相 ハッシュ関数ACID特性 順次編成ファイル ハッシュ結合Atomicity(原⼦性) 障害回復 ハッシュの衝突B+⽊インデックス 除算法 バランス⽊B⽊インデックス ⼈的障害 ビフォアイメージConsistency(整合性) 成⻑相 ファイルDurability(耐久性) ダーティリード ファントムリードIsolation(隔離性) ダンプファイル フィールドWALプロトコル チェックポイント マージ結合アフタイメージ 中間ノード 待合せグラフ⼊れ⼦結合 直接編成ファイル リーフノードインデックス 直列化可能性 両⽴性⾏列インデックスによる結合 データ部 ルートノードインデックスファイル データベースダンプ レコード基数変換法 データベースバックアップ ロールバック共有ロック デッドロック ロールフォワード更新の喪失 同時実⾏制御 ロギングコミット トランザクション ログ(ジャーナル)索引部 トランザクション障害 ログファイル索引編成ファイル ノンリピータブルリード ロッキング⾃乗・中央法 媒体障害 ロックシステム障害 排他ロックシノニム ハッシュインデックス