はじめに
本記事の目的は、リレーショナルデータベース(RDB)の概念を掴むこと。
今までデータベースを、データを大量に保管する場所と抽象的に捉えていた。
それが原因で実際に利用する時、他者の説明通りにしか操作できなかった。
それを払拭するため基礎をしっかり理解したいと思う。
具体的な記事の内容は、SQLによる実践的な操作方法ではなく
データベース用語の定義や内部的な仕組みについてである。
データベース
データベースの意義
データベースはデータを保管する場所ではあるが、
データ自体の用途は保管ではなくユーザによる利用である。
つまり、データベースの役割は、
データへのアクセスのし易さを目的としたデータの保管である。
それを実現するためには、
管理するデータフォーマットが一意性を持っていること、
甚大なデータを操作するためのパフォーマンスの良さ、
パフォーマンス：
レスポンスタイムやスループッドを指す。
同時に複数ユーザからのアクセス制御による整合性の保証、
システム障害やセキュリティ対策、
を考慮したサービスを提供しなければいけない。
データの用途
ユーザによるデータへのアクセスの具体的な目的は、
必要なデータがデータベース内に存在するか検索し抽出することや、
既存データの更新や削除、新規データの登録である。
種類
データベースにはデータの管理方法によって、いくつかの種類が存在する。
階層型やオブジェクト型などがあるが、
現在最も普及しているものがリレーショナルデータベース(RDB)である。
スキーマ
データベースの構造を意味する言葉。
データベースは構造を3つに分けて設計されている。
概念スキーマでは、テーブルやカラム名、データ間の関係、制約など
のメタデータを定義している。
内部スキーマでは、物理的にストレージ内にデータを格納する方法を定義している。
外部スキーマでは、概念スキーマによって定義されたものをデータベース利用者が
アクセスする際のルールや表示形式を定義する。
スキーマを分割することによって、設計変更があった場合の影響範囲を
少なくすることができる。
DBMS
ハードディスクなどのストレージ内にデータベースを構築し、
またそれを操作する機能を提供するソフトウェア。
操作以外にも様々な機能が提供されている(以下wiki参照)。
コネクション確立
データベースを操作するには、DBMSとコネクションを確立(ログイン)する必要がある。
DMBSは、複数ユーザと同時にコネクション確立することができ、
それらを並行処理することができる。
種類
リレーショナル型のデータベースのDBMSには以下などがある。
・Oracle社のOracle
・Microsoft社のSQL server
・IBM社のDB2
・オープンソースのPostgreSQLやMySQL
リレーショナルデータベース
二次元表(テーブル)を使ってデータを管理するデータベース。
テーブルは、行と列(カラムもしくは属性)で構成されている。
複数の列を跨いだ1行分をレコードといい、1つのデータを表している。
つまり、テーブルとは共通の属性を持ったレコードの集合である。
そのため、テーブル名が複数形で表現される。
レコード
レコードは、それ自身を一意に識別できるものを含む、複数の属性(カラム)を持つ。
一意の定義：
同テーブルに重複した値が存在しないことや値が変更されない、
NULL(値がない)ではないことである。
社員IDや学籍番号などの、レコード間で重複しない属性を持つことにより
レコードを一意に識別し検索抽出することができるようになる。
正規化
テーブルは、データへのアクセスがし易いような構造であるべきである。
そのため、テーブルに定義する属性(カラム)は考慮する必要がある。
適切な属性をテーブルに持たせることを正規化という。
正規化の目的は、データの整合性を保つことである。
それを実現するには、テーブルの冗長性を省く必要がある。
冗長性の例は、テーブル内に同一の値が複数存在していることである。
その値を修正した際に一部修正漏れが発生すると、データが不整合なもの
になってしまう。
SQL
リレーショナルデータベースを操作するための言語。
DBMSに対して問い合わせ(命令)を行うことをクエリといい、
その際に使用する言語がSQLである。
種類
SQLは命令の役割によって3つの種類に分類されている。
1つ目はデータ定義言語(DDL)で、RDBMSで扱うオブジェクト(テーブルやインデックスな
ど)の作成や削除を行う。CREATE、DROP、ALTER、JOIN、TRUNCATEなどが該当する。
2つ目はデータ操作言語(DML)で、データベースにあるデータを検索したり更新、削除な
ど操作する。SELECT、DELETE、INSERT、UPDATEなどが該当する。
3つ目はデータ制御言語(DCL)で、トランザクションやアクセス権限などデータへのアク
セス制御を行う。COMMIT、ROLLBACK、GRANT、REVOKEなどが該当する。
SQLの内部プロセス
SQL文を実行すると、以下の順序で処理が行われる。
1.データベースの内部プログラムであるパーサが構文解析を行う。
2.同じく内部プログラムのオプティマイザが、
テーブルの統計情報を元にSQL文が示すデータまでの経路の実行計画を立てる。
3.計画を実行し、データにアクセスする。
補足
SQL文に存在しない句や間違った構文、スペルミスなどがあると、
1の段階で構文エラーが発生する。
統計情報とは、テーブルの行数や行長、データの総バイト数、インデックスのバイト数
などのことでshow table statusによって一覧を確認できる。
実行計画は、無数にあるものから最適なアルゴリズムであろうものを
オプティマイザが作成する。SELECT文の場合、カラムにインデックスが設定されていれば
使用される。
インデックス
SELECT文が実行されると、クエリに該当するレコードの検索のために
すべてのレコードを1行目からチェックしてくフルスキャンと
一部のレコードのみをチェックするレンジスキャンのどちらかが行われる。
パフォーマンス
テーブルが扱うデータ量が増加すると、
フルスキャンの場合レスポンスタイムも比例して多くなってしまう。
一方レンジスキャンの場合は、インデックスを用いることで
特定の範囲に限定した検索を実行するためパフォーマンスに影響を与えることは少ない。
SQL文の先頭にEXPLAIN句を記述すると、どちらのスキャンが実行されたかやチェック
した行数を確認することができる。
インデックスとは
インデックスとは、あるカラムの値を特殊な構造にして記憶したデータである。
一般的にはBtreeと呼ばれる、カラムの値をアルファベット順など特定の規則によって
ソートし、ツリー構造にしたものである。
探索方法は2分探索であり、先頭から順番に確認してく線形探索より探索回数を少なくする
ことができる。また、線形探索の様に目的の値の場所(先頭や最後尾)によって
回数のバラツキがないため、どの値が対象でも性能を一定に保つことができる。
デメリット
インデックスはデータであるためストレージに容量を確保しなければならない。
また、テーブルが更新されるとインデックスも更新が必要であるため、
テーブル更新時に付随してインデックス更新のオーバヘッドが発生してしまう。
作成基準
どのカラムにインデックスを作成するかの指標の1つとしてカーディナリティがある。
カーディナリティとは、カラムが持つ値の分散度である。
例えば、月カラムなら12つの値が存在しているためカーディナリティは12である。
マイナンバーなら12桁の一意の値が存在しているため、1兆に上る。
この場合、後者の方がカーディナリティが高いためインデックスを作成することによる
効果が期待できる。
SHOW INDEX FROM ﾃｰﾌﾞﾙ名でカーディナリティを確認することができる。
トランザクション
複数のSQL文をまとめた単位のこと。
意義
ユーザがDBMSに対して行う命令は、通常1つのSQL文ではなく複数のSQL文で構成
されている。そして、それらを1つの命令としてDBMSに実行させる。
しかし、DBMSはSQL文を1つずつ処理していくだけのため、与えられた命令を人が
意図するように関連のある複数のSQL文のまとまりだと理解できない。
そのため、ユーザが命令の区切りを明示してあげる必要がある。そのつながりある
一連のSQL文群をトランザクションという。
ACID特性
トランザクションはそれ1つが1つの命令であるため、構成するSQL文の1つでも上手く
処理できなかった場合データの整合性が失われてしまう。
そのため、トランザクションはACIDという4つの特性を持っている必要がある。
・Atomicity(原子性)
命令を構成するSQL文全てが成功か失敗という結果でなければいけない。
・Consistency(一貫性)
データベースのルール(整合性制約)を満たしていること。
例えば、一意制約やデータの値がとりえる範囲(整数のみなど)に収まっているかなど。
・Isolation(独立性)
複数のトランザクションが、同一データに対して同時に操作を行っても
矛盾を生じさせないこと。
成立させるには、データにロックをしトランザクションを順次実行させる必要がある。
しかしその場合、処理速度が悪くなってしまう。独立性と速度のトレードオフを解消
させるためにロックの強度を緩和させたいくつかのIsolationレベルがある。
例えば、あるトランザクション(商品在庫を10から9に変更する処理)がcommitされる前
に別のトランザクションからデータ(10ではなく9)を読み出せる。つまり後続のトラン
ザクションが前者のコミット処理を待たずに実行できる。
・Durability(永続性)
トランザクションをcommitし、ユーザが完了通知を受けた時点でその操作が
永続的に失われることがないこと。
主にシステム障害に対する処理の性質であり、ログを用いて実現している。
成立させるには、commitのたびに逐一データベースファイル本体に変更内容を反映さ
せる必要がある。しかしその場合、ストレージに対するアクセス回数が多くなってし
まいパフォーマンスが低下してしまう。つまり、永続性とパーフォーマンスはトレー
ドオフ関係にある。それを改善する仕組みの1つがログ先行書き込み(WAL)である。
ログ先行書き込み(WAL)
データベースファイル本体ではなく、ログにcommitの度に変更内容を一旦書き込み、
適切なタイミングでデータベースファイル本体に書き込む。そうすることで複数の
commitを1度のアクセスで反映させることができる。
また、途中でシステム障害が発生した場合でもログを元に修復が可能である。
処理手順
・トランザクション対象のデータをストレージ上にあるデータベースファイルから
メモリ(バッファ)に読み込む。
・トランザクションによるデータ更新がバッファ内のデータに対して行われる。
・バッファ内データはcommitされるか一定数溜まるとログ(ストレージにある)に
記録される。
・バッファ内データは適切なタイミング(チェックポイント)でデータベースファイル
に書き込まれる。
疑問点
・なぜログはストレージにあるのか？
メモリは揮発性であるため、障害発生するとログが消えてしまうため。
・ログがストレージにあるなら直接データベースファイル本体に書く場合より
回数が増えるのでは？
ログファイルにはシーケンシャルライトによって変更内容のみを連続して
書き込み蓄積している。
一方データベースファイル本体はランダムライトのため毎回アクセスして
書き込むと時間が掛かってしまう。