データベースと応用システム：データの検索機構

Transcript

1. (c)長岡技術科学大学 電気系 1 データベースと応用システム データの検索機構 山本和英 長岡技術科学大学 電気系
2. (c)長岡技術科学大学 電気系 2 ディスクアクセス
3. (c)長岡技術科学大学 電気系 3 ハードディスク(HDD) • セクタ：記憶単位 • トラック：セクタの集まり • シリンダ：トラックの集まり • 記憶容量＝ シリンダ数 　　　　x 1シリンダのトラック数 　　　　x １トラックのセクタ数
4. (c)長岡技術科学大学 電気系 4 アクセス時間 • シーク時間 (位置決め時間) – 磁気ヘッドを目的のトラック上まで移動させる時間 • サーチ時間 (回転待ち時間) – （データにたどりつくための）ディスクの回転時間 – （平均を取って）1/2回転する時間を使う • データ転送時間 – データの１回転時間 = 1トラックのデータ転送時間
5. (c)長岡技術科学大学 電気系 5 アクセス時間の計算例 • 平均位置決め時間： 20ms • 記憶容量： 20kB/トラック • 回転数： 3000 rpm – １回転に要する時間： 60000ms/3000回転 = 20ms/回転 – 20msで20kBアクセスできる ⇒ 1msで1kB • 5kBのデータのアクセス時間は、 20ms (シーク時間) + 10ms (サーチ時間＝0.5回転する時間) + 5ms （5kBのデータ転送時間） = 35ms
6. (c)長岡技術科学大学 電気系 6 インデックス（索引）
7. (c)長岡技術科学大学 電気系 7 インデックス • データを効率的（＝高速）に検索するための仕組み • 一般にインデックスがあったほうが検索効率は向 上するが、必ずそうなるとは限らない（例：データ量 が少ない時） • データを更新するとインデックスも更新する必要が ある
8. (c)長岡技術科学大学 電気系 8 インデックスの効果 データ数が同じでも、データの内容によってインデッ クスの効果は異なる： • データ値が多様である – ２値（０と１）しかないようなデータでは、いくらインデッ クスを作成しても効果は低い • データ値による頻度の分布が小さい – 種類数が同じでもデータ値に偏りが大きいと平均検索 効率は悪くなる
9. (c)長岡技術科学大学 電気系 9 B木インデックス • 多分岐の木構造となっているインデックス • バランス木 – リーフノード（最下位ノード）の深さが同じ • 検索時間のばらつきが低い • データ数が２倍になっても検索時間（＝探索する階 層）は１増えるだけ
10. (c)長岡技術科学大学 電気系 10 Ｂ木の例 11 6 17 3 9 14 20 22 1 3 4 6 7 9 10 11 13 14 16 17 19 20
11. (c)長岡技術科学大学 電気系 11 Ｂ＋木インデックス • B木インデックスは順次アクセスの際に効率が悪い • よって、B木インデックスの条件を満たしたまま、さ らに各リーフノードをポインタで順に連結した – この時のリーフノードをシーケンスセットと呼ぶ • これによってB木の特徴を生かしたまま、順次アクセ スの非効率を解消
12. (c)長岡技術科学大学 電気系 12 Ｂ＋木の例 11 6 17 3 9 14 20 22 1 3 4 6 7 9 10 11 13 14 16 17 19 20
13. (c)長岡技術科学大学 電気系 13 ハッシュインデックス • キー値にある関数を適用して、その値によって行の 格納位置を求めるインデックス • ハッシュ関数の例：「ある素数で割った余り」 • 検索効率がデータベースの規模に左右されない • 衝突（コンフリクト）が起こると同義語（シノニム）が 発生し、これが多いとアクセス効率が低下する • 範囲検索（例：不等号条件）や曖昧検索（部分文 字列条件）などには使えない
14. (c)長岡技術科学大学 電気系 14 ビットマップインデックス • 取り得る値の種類が少ないフィールド（例： ON/OFF、男・女）に対して有効 • リーフページ以外はB+木インデックスと同じ • 各キー値に対して、複数の格納位置(ROWID)では なく、各ビットが表中の各行に対応するビット列を 持つ • ビット演算が容易にできるので、AND/OR/NOTだけ で行う検索には効率的
15. (c)長岡技術科学大学 電気系 15 使われないインデックス こういう検索をするとインデックスがあっても使われな い： DISTINCTやGROUP BYを使った検索 WHERE 商品名 LIKE '%パソコン' WHERE 売上 * 1.05 = 5250 WHERE NOT 学籍番号 = 999 WHERE 従業員 IS NULL WHERE コード = 'HT%' OR 商品名 LIKE '%パソコン ' – AND検索はインデックスを使用する
16. (c)長岡技術科学大学 電気系 16 チューニングと保守管理
17. (c)長岡技術科学大学 電気系 17 チューニング • データベースへのアクセス性能を向上させる改良の こと。 • データ構造、アプリケーション（SQLやインデック ス）、システムリソース（メモリやディスク）の順に検 討する • データ構造のチューニングは性能改善の可能性が 高いが影響も大きい。
18. (c)長岡技術科学大学 電気系 18 チューニング：データモデル • テーブルを非正規化する – 正規化は冗長性を除去して更新時異常を防止できる が、検索時にデータの結合処理が必要になる • サマリテーブルを作成する – 集合関数(GROUP BY)などの計算を、その都度計算す るのでなくあらかじめ計算して結果をテーブルにしてお く。 • 分散データベースを使用する – スケーラビリティや信頼性を高め、アクセス性能を向上 させる
19. (c)長岡技術科学大学 電気系 19 チューニング：SQL インデックスを利用する • 効果大：主キー、外部キー、WHEREやORDER BYで よく使われる列 • 効果小：ON/OFFや男・女など、種類数が少ない 時 • 更新が頻繁に行われるテーブルは、インデックスも 頻繁に更新する必要があるため、注意が必要
20. (c)長岡技術科学大学 電気系 20 チューニング：ストアドプロシージャの利用 よく使う一連の処理（プロシージャ）をまとめてサー バーに保存（ストア）しておくこと • メリット： – 手間いらず – ネットワーク負荷が軽減する – 処理時間も軽減する（コンパイルしてあるので） • 注意点： – １回目に読み込み時間がかかる
21. (c)長岡技術科学大学 電気系 21 チューニング：ディスクアクセス • クラスタリング – 一緒に用いることの多いデータの集まり（＝クラスタ）を、事 前に同じブロックに格納すること • データベースの再編成 – 追加、削除等を繰り返して断片的になったデータベースの データをきれいに「置き直す」こと – ディスクアクセスやメモリの効率が改善 • 一時作業領域を増やす • ディスク競合の回避 – データとインデックスのアクセス先を分離 – データとログのアクセス先も分離
22. (c)長岡技術科学大学 電気系 22 チューニング：トランザクション • ロックの粒度の検討 – 大きくロックする（例：テーブル）と、ロック待ちが増える – 小さくロックする（例：行）と、ロック待ちとロック解除が 頻繁になって負荷がかかる • よって、 – 頻繁にアクセスされるデータは小さくロック – 同時アクセスが少ないデータは大きくロック
23. (c)長岡技術科学大学 電気系 23 チューニング：メモリ バッファヒット率を改善させる • できるだけメモリ内でアクセスを完結させ、ディスク アクセスしないことが望ましい • 一般に、バッファヒット率が80%未満の場合はバッ ファの拡張やメモリの増設が必要
24. (c)長岡技術科学大学 電気系 24 データベースの保守管理 • データベースを一定期間使用した後、利用状況の 変化に対応するために保守管理を行う • 再構成 – テーブルに列やインデックスを追加 – データ構造を変更、など • 再編成 – 未使用断片領域を解消してデータを再配置 – 記憶効率やアクセス効率を改善 – データ構造は変更されない
25. (c)長岡技術科学大学 電気系 25 テーブルの結合
26. (c)長岡技術科学大学 電気系 26 テーブルの結合方法 • SQL文の処理は、選択、射影、結合の３種類 • このうち、結合が最も負荷がかかる • ３種類の結合方法 – 入れ子ループ（ネストループ）結合 – マージ結合 – ハッシュ結合 • 普通はハッシュ結合が効率的だが、条件によって は異なる
27. (c)長岡技術科学大学 電気系 27 ネストループ結合 • 要するに、２レコードを総当たりで比較する探索 • 処理コストはレコードの積に比例 • 一般に、 – インデックスのない小テーブルと – インデックスのある大テーブル を結合する場合に効果的
28. (c)長岡技術科学大学 電気系 28 マージ結合 • 要するに、両方のテーブルのフィールドをソートして おいて、順に走査する探索 • レコードの走査が１回で済むので効率的
29. (c)長岡技術科学大学 電気系 29 ハッシュ結合 • 一般には最も効率的 • テーブルＢのハッシュテーブルを作る • テーブルＡのレコードごとに、ハッシュテーブルを検 索