このページでは、インデックスの種類と効かない場合、オプティマイザと統計情報、データの分割や再編成によるデータベースのチューニングがわかります。
- 一言で言うと：
- DBMS（データベース管理システム）が、たくさんの人が同時に使っても、壊れても、データを正しく保つ仕組みと、速く動かす工夫の話
- 正しさの約束（ACID特性）、同時に使うときの交通整理（ロック、MVCC）、壊れたときの復旧（ログ、チェックポイント、ロールバック／ロールフォワード）、速さ（インデックス、オプティマイザ、チューニング）が出てくる
約束（トランザクション、ACID特性）→ 同時に使う（ロック、デッドロック、2相ロック、MVCC）→ 壊れたら戻す（ログ、WAL、チェックポイント、障害の3種類、バックアップ）→ 速く探す（インデックス、オプティマイザ）→ 速さを保つ（分割、再編成）
覚え方の軸
- インデックスは「よく絞り込む列に。でも効かない場合が5つ」
- B＋木は範囲検索に強い、ビットマップは値の種類が少ない列に、ハッシュは一致検索だけ
- コストベースのオプティマイザのキーワードは「統計情報」
問合せ処理の効率化：速く探す（★）
① インデックス（索引）
- インデックス：
- データベースへのアクセス効率を上げるために使う
- 一般に、WHERE句に指定する問合せ条件や、ORDER BY句、GROUP BY句によく使われる列に付けると、処理速度の向上が期待できる
- ただし、必ずしも効果が期待できるとは限らない
- ユニークインデックスとデュプリケートインデックス：
- ユニークインデックスは、重複を許さないインデックス。主キー項目には、これが付与される
- デュプリケートインデックスは、重複を許すインデックス
- インデックスの例え：
- 本の巻末の索引。全ページをめくらなくても、「データベース……123ページ」と、すぐに目的のページに行ける
インデックスを付与する際の留意点（POINT）（★）
- ①効かない条件：
- インデックス列に対して、NOT条件やNULL条件、LIKE条件、または計算や関数を使った問合せ条件では、インデックスが使われない可能性があるので、効果は期待できない
- ②更新が多い表：
- 更新が頻繁な表は、データの更新とともにインデックスの更新も起きるので、かえって処理時間が長くなる
- ③行数が少ない表：
- レコード（行）数が少ない場合は、インデックスの効果は期待できない
- ④値の種類が少ない列：
- データ値の種類が少ない列（例えば、性別）に、通常のインデックスを付けても使われず、効果がない
- このような列には、ビットマップインデックスを使う
- ⑤値の重複に偏りがある列：
- データ値の重複具合に大きな偏りがある場合は、効果が少ない
⑤の補足
| 列Xの値 | 例1の行数 | 例2の行数 |
|---|---|---|
| A | 300 | 100 |
| B | 300 | 900 |
| C | 200 | 0 |
| D | 200 | 0 |
- どちらが効くか：
- データ値の種類は同じだが、例1は重複の程度が平準、例2は大きく偏っている
- 列Xにインデックスを付けて、平均検索速度の向上が期待できるのは例1
- 例2は、ほとんどの行が「X＝“B”」なので、Bで絞り込んでも900件も残り、そこからさらに順に検索する必要がある（Bである確率は 900 ÷ 1,000）。だから平均検索速度は例1より遅くなる
- 絞込率の目安：
- インデックスで絞り込んだときの絞込率が、10〜20％を大きく超える場合は、インデックスの効果はあまり期待できない
例題（絞り込んだ後に残る件数）
- 問題：
- 上の表で、列Xの値をランダムに1つ選んで（どの行も同じ確率で選ばれるとする）インデックスで絞り込んだとき、残る行数の期待値は、例1と例2でそれぞれいくつか
- 答え：
- 例1は、0.3 × 300 ＋ 0.3 × 300 ＋ 0.2 × 200 ＋ 0.2 × 200 ＝ 90 ＋ 90 ＋ 40 ＋ 40 ＝ 260件
- 例2は、Aが選ばれる確率が100 ÷ 1,000 ＝ 0.1で100件、Bが900 ÷ 1,000 ＝ 0.9で900件なので、0.1 × 100 ＋ 0.9 × 900 ＝ 10 ＋ 810 ＝ 820件
- 検算：
- 例2の絞込率は、平均 820 ÷ 1,000 ＝ 82％。目安の10〜20％を大きく超えるので、インデックスの効果は薄い
- 例1の絞込率は、260 ÷ 1,000 ＝ 26％
② インデックスの方式
代表的なインデックスの方式（★）
| 方式 | 仕組み | 向いている検索 |
|---|---|---|
| B＋木インデックス | B木を拡張したB＋木の構造を使う。節（索引部）にはキー値と部分木へのポインタ、葉にはデータ（キー値とデータ格納位置）を入れる。今のRDBMSで最も多く使われている | 値一致検索、範囲検索、順次検索 |
| ビットマップインデックス | インデックスを付ける列のデータ値ごとにビットマップを作る。値に該当するかをビット「1、0」で管理する | データ量に比べてデータ値の種類が少ない列（性別など） |
| ハッシュインデックス | ハッシュ関数を使って、キー値とデータを直接関係付ける | 「X = ‘A’」のような一意検索。全件検索や、不等号などを使った範囲検索には不向き |
B＋木インデックスの特徴（POINT）
- 範囲検索に強い：
- 値一致検索だけでなく、範囲検索にも優れている
- 葉をつないで順次検索も速い：
- 最下位の葉（リーフ）どうしをポインタで結ぶことで、順次検索も高速化している
- 分割と併合：
- データの追加・削除にともない、必要ならブロックの分割や併合を行う
- 木を浅くする：
- 1件のデータを検索するときの節へのアクセス回数は、木の深さに比例する
- だから、索引部の節に多くのキー値を持たせると、木の深さが浅くなり、検索が効率化できる
- B木・B＋木：
- アルゴリズムの「木構造」で出てくるB木・B＋木と同じもの
ビットマップインデックスの例
| ROWID（行番号） | 名前 | 男 | 女 |
|---|---|---|---|
| 1 | 加藤 | 1 | 0 |
| 2 | 吉田 | 0 | 1 |
| 3 | 松本 | 1 | 0 |
- インデックスの方式の例え：
- B＋木は、国語辞典の「あ行 → あ → あい…」と段階的にたどるつめ。隣のページにもすぐ行ける
- ビットマップは、出席簿の「男」「女」の欄に○を付けた一覧
- ハッシュは、ロッカーの番号を名前から計算で出す仕組み。「田中さんのロッカー」はすぐわかるが、「た行の人全員」は探せない
③ 複合インデックス
- 複合インデックス（連結インデックス）：
- 複数の列を組み合わせて1つのインデックスにしたもの
- 使い方：
- 「WHERE A = ‘a’ and B = ‘b’」のように、列AとBがよく検索条件に使われるなら、複合インデックス（A, B）を定義すると、検索が速くなる
- 注意（第1キーが大事）：
- 列Aを第1キー、列Bを第2キーとして検索木（B＋木）が作られる
- だから、第1キーである列Aが含まれた検索は速くなるが、列Aが含まれない検索（Bだけの検索）は速くならない
- 例：
- 「CREATE INDEX 商品_idx ON 商品表（カテゴリ, サイズ）」
- リーフブロックには、カテゴリ、サイズの順にソートされて入る（衣類-L、衣類-S、雑貨-M、食品-L…）
- 複合インデックスの例え：
- 電話帳は「名字 → 名前」の順に並んでいる。名字がわかれば速く探せるが、名前だけでは探せない
④ オプティマイザ
- オプティマイザ：
- RDBMSで、SQL文を実行するときに、問合せをどのように処理するかを決めるクエリ最適化の機能
- コストベースとルールベースの2つがある
| 種類 | やり方 |
|---|---|
| コストベース | ディスクファイルのI/O回数、入出力バッファやログバッファの使用状況といった、RDBMSが集めた統計情報を基に、表へのアクセスや表の結合にかかるI/O、CPUのコストなどを見積もり、最適なアクセス方法や結合順序・結合方法を選ぶ |
| ルールベース | 実行するSQL文を分解し、その情報と所定のルールでアクセス方法を選ぶ。同じSQL文なら同じアクセスパスになり、たとえ全表を走査したほうが速い場合でも、インデックスが定義されていれば、インデックスを使ったアクセスパスが選ばれる |
- 出題傾向（試験）：
- 「コストベースのオプティマイザが、SQLの実行計画を作るときに必要なものは？」「コストベースのオプティマイザの機能は？」のように、コストベースが問われる
- 解答のキーワードは「統計情報」
- オプティマイザの例え：
- コストベースは、カーナビ。今日の渋滞情報（統計情報）を見て、いちばん速い道を選ぶ
- ルールベースは、「必ず高速道路を使う」という決まりで走る。高速が渋滞していても高速を選ぶ
データベースのチューニング：速さを保つ
- チューニング：
- データベースシステムでは、アクセス性能（効率）の確保が大事
- 運用にともなって性能低下が目立ってきたら、適切なチューニングをしなければならない
- データベース管理者（DBA）：
- データベースシステムの運用管理をする人
- 職務は、データベースの設計、保守、運用の監視、障害からの回復
- データ管理者（DA）：
- DBAの上位に置く場合がある
- データベースの概念・論理設計を行い、データ項目を管理して標準化する
① 複数ディスクへの分割
- 複数ディスクへの分割：
- アクセスの集中でディスクのI/O待ち時間が増えた場合、表単位でデータを複数のディスクへ分割する
- また、データと索引を別々のディスクに分けることでも、性能向上を図る
② 表の分割
- 表の分割：
- 1つの表に入るデータが大量の場合、データを複数のディスクに入れると、並列処理ができ、アクセス性能が上がる
分割方式（POINT）
| 方式 | やり方 |
|---|---|
| キーレンジ分割方式 | 分割に使うキーの値の範囲で、その値に割り当てられたディスクに分割して入れる |
| ハッシュ分割方式 | 分割に使うキーの値にハッシュ関数を適用し、その値に割り当てられたディスクに分割して入れる |
- パーティショニングとシャーディング：
- データを複数に分割して格納することを、一般にパーティショニングという
- 複数のサイトに水平あるいは垂直に分割し、分散する場合は、シャーディングという
- コンポジット分割：
- キーレンジ分割したものを、さらにハッシュ分割するといった、組み合わせた分割方法
- 分割方式の例え：
- キーレンジ分割は、「会員番号1〜1000は棚A、1001〜2000は棚B」と範囲で分ける（新しい番号の棚に偏りやすい）
- ハッシュ分割は、「会員番号を3で割った余りで棚を決める」（まんべんなく散らばるが、範囲で探すと全部の棚を見る）
③ データベースの再編成
- 再編成が必要な理由：
- データの追加、変更、削除がたくさん繰り返されると、使えない断片的な未使用部分が増えて、データベース全体のアクセス効率が下がる
- データベースの再編成（ガーベジコレクション）：
- これを防ぐため、定期的に行う
- 再編成の例え：
- 本棚から本を抜いたり差したりしているうちに、すき間だらけになる。ときどき本を詰め直して整理する
混同しやすいペア
- ユニークインデックス ／ デュプリケートインデックス
- ユニークインデックスは、重複を許さない（主キー）
- デュプリケートインデックスは、重複を許す
- B＋木インデックス ／ ハッシュインデックス
- B＋木は、一致検索も範囲検索も得意。葉どうしをポインタでつなぐ
- ハッシュは、一意検索だけ得意。範囲検索や全件検索には不向き
- B＋木インデックス ／ ビットマップインデックス
- B＋木は、一般的な列に使う（今のRDBMSで最も多い）
- ビットマップは、値の種類が少ない列（性別など）に使う
- 複合インデックスの第1キー ／ 第2キー
- 第1キーを含む検索は速くなる
- 第2キーだけの検索は速くならない
- コストベース ／ ルールベース（オプティマイザ）
- コストベースは、統計情報でコストを見積もって、最適なアクセス方法を選ぶ
- ルールベースは、決まったルールで選ぶ。インデックスがあれば、全表走査が速くてもインデックスを使う
- DBA ／ DA
- DBA（データベース管理者）は、データベースの設計、保守、運用監視、障害回復をする
- DA（データ管理者）は、概念・論理設計を行い、データ項目を管理・標準化する（DBAの上位）
- キーレンジ分割 ／ ハッシュ分割
- キーレンジ分割は、キーの値の範囲で分ける
- ハッシュ分割は、キーの値のハッシュ値で分ける
- パーティショニング ／ シャーディング
- パーティショニングは、データを複数に分割して格納すること全般
- シャーディングは、複数のサイトに分割して分散すること