IT系/インフラ系/DB/基礎/DBMS

Last-modified: 2020-10-17 (土) 21:30:34

目次

概要

DBの基礎となる知識・理論について、覚えたこととかをまとめる。というか、リンク集。

DBMS

データベースはデータをプログラムから切り離しDBMS（Data Base Management System）によって統合、管理・運用を行う。

  • データを一元管理することにより一貫性が保たれる
  • ユーザごとにアクセス権限を与えることでセキュリティを確保
  • 複数のトランザクションの同時実行による高速化の実現


    DBMSは上記を実現するため、メタデータ管理、質問処理、トランザクション管理の三大機能を備えている。

DBMSの三大機能

  • メタデータ管理
    データディクショナリに格納される。
  • 質問処理
    クエリの最適化を行う必要がある。これを行う機能をクエリオプティマイザと呼ぶ。
  • トランザクション管理
    同時実行を制御。トランザクション単位での障害児の回復処理も可能とする。

インデックス

データベースのレコードを取得するときに利用。

  • 検索に使用する列の値、その値に対応するレコードにアクセスするためのポインタで構成される。
  • データの検索時には高速にテーブルにアクセスすることが可能
  • データの更新時にはインデックスの再構築が必要（インデックスを作りすぎると更新処理が遅くなる）。

インデックスのデータ構造

インデックスのデータ構造には、大別してB木、ビットマップ、ハッシュの3種類がある。

  • B木（B Tree）
    木構造の一つでデータをツリー状に管理。多分木、バランス木。
    データの大きさを比較することで、該当データを探索する。
    一つ一つのデータの塊をノードという。一番上のノードを根ノード、一番下のノードを葉ノードと呼ぶ。
    B木の応用として、データをすべて葉に持たせ、葉ノードを順に探索することで順次探索を行うB+木がある。
    ノードに含まれるデータの最低数を最大値の2/3以上にして効率化を図ったB*木もある。
    特定の一つのデータを探すのに向いているので、主キー等に設定される。
    範囲検索も可能。
    検索要件は１つしか指定できず、AND・ORの条件指定等で複数のインデックスを同時に扱うことはできない。
  • ビットマップ
    ビットマップインデックスは、取りうる値の数が少なく、複雑な検索が行われる場合に利用される。
    AND・ORの条件指定等、服札に処理を組み合わせることが可能。
  • ハッシュ
    ハッシュ関数と呼ばれる関数を使って、検索に使用するキーからハッシュ値を計算し、
    その値からデータの格納位置を求める。
    ハッシュは、あらかじめデータ数などが決まっており、ハッシュ値の重複が起こりづらい場合に利用される。

ユニーク／非ユニークインデックス

  • ユニークインデックス
    インデックス値に対応するレコードが１つしかないインデックス。
    主キーや値が重複しない列のインデックス。ポインタは１つ。
  • 非ユニークインデックス
    複数のレコードが１つのインデックス値に対応するインデックス。
    ポインタを複数持つ。

クラスタ化／非クラスタ化インデックス

  • クラスタ化インデックス
    キー値ごとにデータの格納位置を定め、同じ場所に格納するクラスタ化を行ったデータのインデックス。
    データの実際の格納位置を操作するため、クラスタ化できるインデックスは１テーブルにつき１つに限られる。
  • 非クラスタ化インデックス
    クラスタ化を行っていないインデックス。

性能設計

インデックスを設定すると、SELECT文での検索処理のアクセスは速くなる。
一方、INSERT、UPDATE、DELETE分では、使用するたびにインデックスの再構成が必要になり、処理が遅くなる。
インデックス設計のポイントは以下のとおりである。

  • 一つのテーブルにインデックスを設定しすぎない
  • データ量が少ない時には設定しない方がいい場合もある
  • クラスタ化インデックスは頻繁に利用される一意性が高い列（主キー等）に設定する

SQLの速度、処理時間の測定・演算については以下のポイントを考慮する。

  • 索引検索は索引を読み込んでからデータを読み込む
    索引を読み込む分処理時間がかかるため、非効率なインデックスであれば直接テーブルを読みに行った方が速い場合もある。
  • クラスタ化インデックスは、範囲内の複数のデータにアクセス可能
    クラスタ化されている場合キー値の順にデータがまとまっているため範囲検索に効果的。
  • データだけでなくログの出力時間も考慮
    処理時間にはデータの更新時間に加えて更新前ログ、更新後ログの更新時間、インデックスの再構成時間を考慮に入れる。

ディスク容量の計算にも、インデックスを考慮に入れる必要がある。

  • テーブルの表領域の容量：1行当たりの平均行長（バイト） × 見積行数
  • データページの1ブロックは1024の整数倍の値を指定
  • テーブルの1行を複数ブロックに分けて格納することはない
  • ブロックごとに格納できる行数：ブロックサイズ÷平均行長（小数点以下切り捨て）

性能向上の手段の1つとして、1つのデータを分割して管理するパーティション化（パーティショニング）がある。

  • 論理的に区分けしたパーティションについて、パーティションキーを設定
  • パーティションキーごとにデータを分割
  • 一部のデータにだけアクセスする場合は、テーブル全体を読み込む必要がなくなる

テーブル結合

テーブル結合のアルゴリズムについては、代表的なものとして、入れ子ループ法、マージジョイン法、セミジョイン法、ハッシュジョイン法がある。
一般的に高速な順で並べると、ハッシュジョイン法、マージジョイン法、入れ子ループ法となる（セミジョイン法は分散データベースでの利用のため除外。）

  • 入れ子ループ法（ネストループ法）
    単純に二重ループ（入れ子ループ）を使用してテーブル結合する方法。
    全ての行に対して互いの行を比較し、条件にあてはまる行を選択する。
    A表がM行、B表がN行の場合：M×N 回 比較　→　行数が多いと効率が落ちる
  • マージジョイン法
    ２つのテーブルの結合列をあらかじめソートしておいてから結合する方法。
    ソート済みの２つのレコードを上から順に比較し一致するものを取り出すため、比較的高速。
  • セミジョイン法
    別々の場所にあるデータベース間でテーブル結合を行う場合、一方のデータベースのテーブルを他方のデータベースに転送する必要がある。
    この時、結合に必要な属性だけを先に転送し、結合に成功したデータのみを元のデータベースに転送して結合を行う方法をセミジョイン法という。
    通信量を減らすことで高速化が可能になる。
  • ハッシュジョイン法（ハッシュ法）
    あらかじめ結合列の値に対してハッシュ関数を用い、ハッシュ表を生成しておき、
    実テーブルではなく、ハッシュ値を比較することで高速に結合を行う。

トランザクション管理

トランザクション

一般的に、分けることのできない情報処理の単位のことをトランザクションという。
データベースでは、複数のデータの読み書きなどの命令を一つにまとめたものをトランザクションと呼ぶ。
トランザクションが満たすべき４つの特性をACID特性という。

  • 原子性（Atomicity）
    完全に終わるか元に戻すかのいづれかで完結すること。中途半端な状態で終わらないこと。
  • 一貫性（Consistency）
    データの整合性を保ち、一貫したデータを確保すること。
    トランザクションを完全に独立して順番に実行した結果と同じになることを直列可能性（Serializability）といい、
    これを満たす必要がある。
  • 独立性（Isolation）または隔離性
    複数のトランザクションはそれぞれ独立して実行できなければならない。
    AとBのトランザクションがある場合、Aで変更中のデータをBが変更できないようにする必要がある。
    他のトランザクションに影響を与える度合いのことを分離レベル（隔離性水準）という。
  • 耐久性（Durability）または障害耐久性、持続性
    コミットしたデータは障害が起こっても回復できるようにすること。

ACID特性のうち、一貫性と独立性を満たすために、複数のトランザクションの同時実行を制御する必要がある。
このとき行われるのが、排他制御または同時実行制御がある。
排他制御を全く行わない場合、ロストアップデートと呼ばれる更新データの喪失が起こる場合がある。

  • 悲観的（ぺシミスティック）制御法
    ロックなどデータの書き込みを禁止する手法
  • 楽観的（オプティミスティック）制御法
    完了時に他のトランザクションに上書きされていなければ問題なしとする手法。
    上書きされていないことを更新時刻で確認する時刻印アルゴリズムがよく用いられる。

ロック

あるトランザクションがアクセスするデータに対して、
ほかのトランザクションが同じデータの読み書きができないようにアクセスを制限すること。
ロックのかけ方には、共有ロックと専有ロックの2種類がある。

  • 共有ロック
    データを参照するときだけにかけるロック。他のトランザクションからもデータを参照可能。
  • 専有ロック
    データを更新するときにかけるロック。他のトランザクションでデータを参照することも不可。

ロックをかける範囲の広さのことをロックの粒度と呼ぶ。

  • 行ロック
  • テーブルロック

複数のトランザクションを実行した結果と、トランザクションを完全に独立して順番に実行した結果が同じになることを
直列可能性という。
トランザクションA、Bを同時に実行した結果が、A→B、またはB→Aの順番で１つずつ実行した結果と同じになるということであり、
この時直列可能性が保証されたという。
トランザクションA、Bでデータを読み込む順序が同じ場合、このA、Bの関係を競合等価という。
競合等価の場合の直列可能性のことを、競合直列可能性という。
ロックをかけるタイミングによっては、直列可能性を保証できないことがある。

確実に直列可能性を保証するロック方式に、2相ロック方式がある。
2相ロック方式は、読み書きに必要なすべてのロックについて処理が完了するまでアンロックを行わない方式。
2相とは、
最初のロックをかけ続ける単調増加の相
最後にロックを外す単調減少の相
の2つにトランザクションが分かれる、という意味。

ロックを取得する順番によって２つのトランザクションで互いのロックの開放を待つ状態になることを、デッドロックという。
各トランザクションで複数のデータにロックをかける場合に、発生しうる。
DBMSではデッドロックを検出し、一方のトランザクションをロールバックすることで解決する仕組みがある。
デッドロックを検出する際に使用されるものとして、待ちグラフがある。
トランザクションの様子を待ちグラフで観察し、閉路（ループ）が発生したらデッドロックと判断する。

分離レベル

トランザクションの独立性を完全に満たそうとすると、トランザクションの同時実行ができず、処理速度が低下する恐れがある。
そのため、速度と信頼性を天秤にかけ、業務に最適な分離レベルを設定して処理を行う必要がある。
標準SQLでは、トランザクションの分離レベルを低い順に以下の4つと定めている。

  • READ UNCOMMITED（未コミット読取り）
    未コミットデータも含めて読み取る。
    変更途中のデータを読み取るダーティリードが発生するが、その分処理性能は高くなる。
  • READ COMIMTED（コミット読取り）
    コミット済みデータのみを読み取る。
    他のトランザクションのコミット前とコミット後の両方でデータの読み取りを行うと
    値が変わってしまうアンリピータブルリードが発生する。
  • REPEATABLE READ（反復読み出し可能）
    トランザクション処理中は読み取り対象のデータに変更が発生しないことを保証する。
    他のトランザクションが新たに追加したデータが途中で見えるようになるファントムリードが発生する。
  • SERIALIZABLE（直列化可能）
    必ず直列可能性が保証されるように制御を行うが、並行処理ができないため、性能が低くなる。

障害回復機能

ログ

更新前ログ
更新後ログ
チェックポイント
ログバッファ
ロールバック（後退復帰）
ロールフォワード（前進復帰）

WALプロトコル

①実行前レコードの書き出し
②実データの更新
③更新後レコードの書き出し
WAL（Write Ahead Log）
①③②

障害の種類

  • トランザクション障害
  • ソフトウェア（システム）障害
  • ハードウェア（メディア）障害

バックアップ

  • フルバックアップ
  • 増分バックアップ
  • 差分バックアップ

分散データベース

分断耐性
CAP定理

透過性

  • 位置に対する透過性
  • 分割に対する透過性
  • 複製に対する透過性

2相コミット

  • 第1相
    コミット要求
    ロールバック指示
  • 第2相
    ロールフォワード
    セキュア状態

ページ一覧

'IT系/インフラ系/DB/基礎/DBMS/' には、下位層のページがありません。

リンク集

重複を恐れないリンク集。

  • [[xxxxxx :xxxxxxxx]]
  • [[xxxxxx :xxxxxxxx]]
  • [[xxxxxx :xxxxxxxx]]
  • [[xxxxxx :xxxxxxxx]]
  • [[xxxxxx :xxxxxxxx]]
  • [[xxxxxx :xxxxxxxx]]
  • [[xxxxxx :xxxxxxxx]]
  • [[xxxxxx :xxxxxxxx]]
  • [[xxxxxx :xxxxxxxx]]
  • [[xxxxxx :xxxxxxxx]]
  • [[xxxxxx :xxxxxxxx]]
  • [[xxxxxx :xxxxxxxx]]

その他メモ

なにかあれば。