講義資料 DBMS講義 1 DBMS

データベース

第 10 回 DBMS

　　　　 DataBase Management

System

理解度調査講評 (1-1)

a b c

1 2 3

4 5 6

7 8 9

10 11 12

13 14 15

SELECT a FROM S WHERE b > 10;

FROM S

理解度調査講評 (1-2)

a b c

1 2 3

4 5 6

7 8 9

10 11 12

13 14 15

SELECT a FROM S WHERE b > 10;

FROM S WHERE b > 10

理解度調査講評 (1-3)

a b c

1 2 3

4 5 6

7 8 9

10 11 12

13 14 15

16 17 18

SELECT a FROM S WHERE b > 10;

SELECT a FROM S WHERE b > 10 a

10 13 16 19

理解度調査講評 (2-1)

a b c

1 2 3

4 5 6

7 8 9

10 11 12

13 14 15

16 17 18

19 20 21

SELECT a,d FROM S,T WHERE b=e AND a>10; S

d e f

21 20 19

18 17 16

15 14 13

12 11 10

9 8 7

6 5 4

3 2 1

T

理解度調査講評 (2-2)

a b c d e f

SELECT a,d FROM S,T WHERE b=e AND a>10; FROM S,T ( その

１ )

理解度調査講評 (2-3)

a b c d e f

1 2 3 21 20 19

1 2 3 18 17 16

1 2 3 15 14 13

1 2 3 12 11 10

1 2 3 9 8 7

1 2 3 6 5 4

1 2 3 3 2 1

4 5 6 21 20 19

SELECT a,d FROM S,T WHERE b=e AND a>10; FROM S,T ( その

2)

理解度調査講評 (2-4)

a b c d e f

1 2 3 3 2 1

4 5 6 6 5 4

7 8 9 9 8 7

10 11 12 12 11 10

13 14 15 15 14 13

SELECT a,d FROM S,T WHERE b=e AND a>10; FROM S,T WHERE b=e

理解度調査講評 (2-5)

a b c d e f

1 2 3 3 2 1

4 5 6 6 5 4

7 8 9 9 8 7

10 11 12 12 11 10

13 14 15 15 14 13

SELECT a,d FROM S,T WHERE b=e AND a>10; FROM S,T WHERE b=e AND a>10

理解度調査講評 (2-6)

a b c d e f

1 2 3 3 2 1

4 5 6 6 5 4

7 8 9 9 8 7

10 11 12 12 11 10 13 14 15 15 14 13 16 17 18 18 17 16 19 20 21 21 20 19

SELECT a,d FROM S,T WHERE b=e AND a>10; SELECT a, d FROM S,T WHERE b=e AND a>10

理解度調査講評 (3-1)

a b c

1 2 3

4 5 6

7 8 9

10 11 12

13 14 15

16 17 18

19 20 21

SELECT a,d FROM S,T WHERE b=e AND a>10; S

d e f

21 20 19

18 17 16

15 14 13

12 11 10

9 8 7

6 5 4

3 2 1

T

理解度調査講評 (3-2)

SELECT count(*) FROM T WHERE d > 5;

d e f

21 20 19

18 17 16

15 14 13

12 11 10

9 8 7

6 5 4

3 2 1

FROM T

理解度調査講評 (3-3)

SELECT count(*) FROM T WHERE d > 5;

d e f

21 20 19

18 17 16

15 14 13

12 11 10

9 8 7

6 5 4

3 2 1

FROM T WHERE d>5

理解度調査講評 (3-4)

SELECT count(*) FROM T WHERE d > 5;

d e f

21 20 19

18 17 16

15 14 13

12 11 10

9 8 7

6 5 4

3 2 1

SELECT count(*) FROM T WHERE d>5

6行

count(*) 6

今日の内容

 _{DBMS の概要}

 _DBMS の仕事 (RDBMS, ORDBMS)

 _{SQL の翻訳}

 _{SQL の実行計画}

 _{SQL の実行}

 _{同時実行制御}

 _{トランザクション管理}

 _{ストレージ管理}

バッファ管理

DBMS _の概要

 データーベースの機能を実現しているミドルウェア

 データベース (磁気ディスク )とアプリケーションの間にある

DBMS の主な機能 (1) SQL の翻訳

 _SQL 文を読んでプログラムで処理できるようにする

 省略された記述などもすべて付け加える

 _{形だけではだめ}



テーブルが実際にデータベース中に存在するか確かめる

 テーブルを作るなら、そのテーブルがまだデータベースにない ことを確かめる



カラムがそのテーブルに存在するかを確かめる



カラムの型が SQL の記述と一致しているか

 文字列のカラムに数値の記述を当てはめてもダメ

 _{SQL の翻訳タイミング}



_{実行時に行う}

 _{確実だが時間がかかる}



アプリケーションのコンパイル時に行う

DBMS の機能 (2): SQL の実行計画

 どうやれば最も効率よく SQL が処理でき

るかを見つける

 _SQL には「ほしい結果」しか書かれていない

 この結果を得るための方法は複数ある

 _{行を見つける方法}

 _{結合の処理の方法}

 やってみなければわからないがやってみたの

では遅すぎる

DBMS の機能 (2): SQL の実行計画の例

 (1) 1 つのテーブル内の行を見つける

 カラムの値が決まっていたら

 このカラムにインデックスがあったら、インデッ

クスから直接行を探す

 インデックスがなければテーブルのデータをすべ

て調べる以外に方法はない

 カラムの値の範囲が決まっていたら

 このカラムにインデックスがあったらここから行

を探せばよさそうだが、、、



たとえば、該当する行がテーブル全体の半分くらい

DBMS の機能 (2): SQL の実行計画の例



(2) _SELECT 名前 FROM 従業員 WHERE 従業員 ID = 123;

 テーブルをすべて調べてもいいが、、、

 従業員が 1 万人いたら、平均 5000 人のデータを無駄に読む

 従業員 ID にインデックス張ってあれば、これを使った方が無駄 に読むデータは少ない

 この場合はインデックスを経由してデータを見つける方法を選ぶ

 第 2 回講義の B ツリーを思い出してみよう

 下の図だと、ファイルを 4 回読めば必要なデータにたどり着ける

DBMS の機能 (2): SQL の実行計画の例

 (3) _SELECT 名前 FROM 従業員 WHERE 従業員

ID > 1 AND 従業員 ID < 5000;

 前のスライドと同じ形だが、 WHERE 句が違う

 この条件に合う従業員は 5000 人くらいはいそうな感じ

 インデックスをそれぞれ読んでいくと、インデックスの

分だけ読む回数が増える

 最後の従業員の行は「ランダムアクセス」で読むことに

なる

 この場合は、テーブルをすべて読み込んだ方が早い

 この場合は「シーケンシャルアクセス」になるので、同

じ量のデータを読む場合はランダムアクセスよりも早い

DBMS の機能 (2): SQL の実行計画の例

 (4) _{条件の適用順序}

 _WHERE 句の中をどの順序で調べていくか

 _{結合演算のやり方}

 3 つ以上だと、どの順に結合するか

 大規模な SQL だと 5 つ以上のテーブルの結合も

珍しくない

 2 つのテーブルの結合のやり方はたくさんある

 インデックスを使うやりかた

DBMS の機能 (2): SQL の実行計画を調

べる

 _EXPLAIN 文を使うと調べることができる

 データベースによって少しずつ違うので注意

 _{PostgreSQL の例}

postgres=# explain select * from accounts where aid <= 500000; QUERY PLAN ---

Index Scan using accounts_pkey on accounts (cost=0.00..20992.71 rows=491907 width=100)

Index Cond: (aid <= 500000)

調べる SQL 文 EXPLAIN文

DBMS の機能 (3): SQL の実行

 _{ここは単純}

 ₍₂₎ で作った計画を実行するだけ

 必要なロジックは準備しておく必要がある

 一番コストがかかるのは行の探索と結合演算

 次の２つを考える必要がある

 _{同時実効制御}

 _{トランザクション制御}

同時実行制御 (1)

 複数のプログラムから同時にデータベース

に読み書きの要求がくる

 それぞれのプログラムがあたかもデータベー

スを占有しているように見せる

 トランザクションの隔離 (isolation) という

 それぞれのプログラムが別なデータを読み書

きすればいいのだが…

 実際には読み書きが衝突することがある

 _{片方を待たせる}

同時実行制御 (1) ： 読み書きの衝突の検出と

制御

同時実行制御 (1) ： ロックによる

データ保護

 読み書きの衝突の検出と制御は「ロック」を使って行う

 ロックを使うと共有データを保護することができる ^{実際にはこの間に他の処理が割り込まな}

いようにする

同時実行制御 (1) ： ロックの種類

 _{単純なロック}

 同時に一つのアプリケーション (プロセス /スレッド )しか使えない

 保護されている間は誰も書き込めないので安心して読み書きができ る

 リードロックとライトロック

 ロックするやり方を 2 種類持たせる場合もある

 _{リードロック}

 保護されたデータは読めるが書き換えてはいけない

 このロックは同時に複数のアプリケーションが確保できるので、効率が上が ることがある

 次のライトロックを取得するためには、すべてのアプリケーションがロック を解放するのを待たなければならない。

 _{ライトロック}

 保護されたデータは読んでも書き換えてもよい

同時実行制御 (1) ： ロック確保のマ

 ナー ロックを取得する時間は最小限度に

 他のアプリケーションの実行をストップさせ

る

 必要最小限度のロックを取ること

 リードロックで十分なのにライトロックを

取ってはいけない

 他のアプリケーションへの思いやりが大事

トランザクションとは：第５回講義の復

習 (1)

 アプリケーションの処理の単位

A さんの口座の残額を読む

振り込み額を A さんの口座から減額 B さんの口座の残額を読む

振込み額を B さんの口座に加える

トランザクションの

例 _こ

の 途 中 で 止ま っ て は 困 る

止まったら最初 に戻ってほしい

無事ここまで処理できたら後で消 えてもらっては困る

トランザクションとは：第５回講義の復習

 (2) トランザクションの ACID 特性

 トランザクションの持つべき重要な 4 つの特性



A (Atomicity)

 トランザクションが中途半端に実行されないこと



C (Consistency)

 データに矛盾が生じる書き込みをさせないこと

 データに矛盾がないようにデータを格納できること



I (Isolation)

 他のトランザクションが実行中であることを意識しないで処理 できること



D (Durability)

DBMS のトランザクション管理 (1)

 トランザクション管理が実行してくれるもの

→トランザクションの ACID 特性の実現



_A: 原子性 (Atomicity)

 トランザクションの開始と終了をアプリケーションから教えて もらう

 トランザクションが失敗したら ( アボート , Abort) トランザク ションで行ったデータの変更を元に戻す ( なかったことにす る )→ アンドゥログ ( 障害復旧機能 )



_C: 一貫性 (Consistency)

 制約 ( 第 3 回の講義参考 )に従ったデータ変更チェック

 制約に違反する変更はさせない→トランザクションは失敗する



_{I :} 隔離性 (Isolation)

 トランザクションが成功するまで、トランザクションの変更結 果は他のトランザクションには見せない

トランザクションが成功したら、その結果が他のトランザク

DBMS のトランザクション管理 (2) ：原

子性 (1)

 トランザクションが失敗してもいいように

 変更を元に戻すのに必要な情報を記録しておく

 これをアンドゥログという

 記録場所、記録方法はデータベースによってまち

まち

 トランザクションが失敗したら、アンドゥログを

使ってデータベースを元に戻す

 トランザクションの開始と終了はデータ

ベースではわからない

DBMS のトランザクション管理 (2) ：原

子性 (2)

 アンドゥログを使って失敗を元に戻す

DBMS のトランザクション管理 (3) ：隔

離性 (1)



他のトランザクションが実行していないようにアプリケーション

に見せる

 _{同時実行制御を使う}

 _{変更の衝突はしない}

 _{問題が少し}

 自分より後のトランザクションの変更結果は見えるか？

 見えなくするのが理想だが

 _{実際には見える}

 トランザクション実行中に他のトランザクションがコミットした データは見えるか？

 見えなくすることも可能 (serializable)

 見えるようにすることも可能 (read committed)

 トランザクション実行中に他のトランザクションがコミットしてい ない変更は見えるか？

 見えるような設定も可能 (read uncommitted: これがないデータベー

他のトランザクションによるデータ更新

 (1) _{理想的には}

 全部のトランザクションが直列に ( 他のトラン

ザクションが全く実行されていないように ) 見

せるのがいい

他のトランザクションによるデータ更新

 (2) _現実は

 実際にはそうはうまく行かない。トランザクションの

実行は互いに重なって行われる

トランザクションが終わるまで待 たせる

古いデータを見せる (MVCC^*1) 新しいデータを見せる

ストレージ管理

 どのテーブルをディスクのどの部分に格納

するかを決める

 _OS のファイルをそのまま使うやり方

 ディスク全体を直接管理するやり方

ストレージ管理の要点

 どのテーブルをどのファイル ( ディスク )

に入れるかは、物理設計で決める

 テーブルをうまく配置すると性能が上がる

 テーブルのサイズを考えて配置を決める

 テーブルがファイルやディスクからはみ出さない

ように

バッファ管理

 データは一旦ディスクからメモリに読み込まないと処理でき

ない

 データーベース全体でこのメモリを共有する

 時間がかかるファイルの読み書きの回数を減らす

 バッファ管理も物理設計の対象

 _{メモリの量}

 どの用途にどれだけ割り当てるか

 _{ファイルの読み書き}

 内部処理 (並べ替えなど )

障害復旧

 データベースが停止する

 _停電

 ディスクの故障→データが壊れた

 その他サーバのハードウェア故障

 _{ソフトウェアのバグ}

 _誤操作

 _災害

障害復旧の種類→やりかたはバックアッ

 プとログ _{クラッシュリカバリ}

 ハードウェアは壊れていない



中途半端な更新データを元に戻す

 コミットしていないトランザクションを強制アボートする

 _{アーカイブリカバリ}

 ディスクが壊れてデータが失われた

誤操作をして大事なデータを消してしまった



バックアップとログを使ってデータを元に戻す (後述 )

 _{ディザスタリカバリ}

 設備が壊滅的被害： 地震、火災、その他の天災・人災



_{まずは設備の復旧}



データを元に戻すにはやはりバックアップとログが必要

バックアップとログ

 データベースを安全に使うために必須

 _{バックアップ}

 データベースのコピーを作る

 _{運転中でも作れる}

 ログ： Log 、もともと船の「航海日誌」のこと

 データベースの変更履歴：あらゆる「変更」の履歴

が書かれている

 _{アクティブログ}

 クラッシュリカバリに必要

バックアップとログのとり方

ケーションアプリ

データファイル 共有バッファ _{マネージャ}^バッファ

クラッシュしたらこの データを使って復旧 ( リ

カバリ )する

ランダムアクセス 非同期

ログ

バックアッ プ：

別な場所にコ ピー

データ更新中でも きちんとバック アップが取れる