More than 1 year has passed since last update.

DB設計基礎講座 #1 ノウハウ

Last updated at 2023-08-12Posted at 2023-08-11

システムとデータベースの関係

システム、ソフトウェアは情報の流通機構である
情報とはデータと文脈(分析や加工)を合成して生まれる
全てのシステムはデータを取り扱っており、このデータから情報を生み出しユーザに届ける
そしてデータはほとんどの場合、データベースを用いて処理される

設計工程とデータベース

データ設計とは基本的にデータベース設計と同義
データベース設計に失敗するとシステムの品質が著しく下がる

POA(Process Oriented Approach)

システムを作る際に、プログラムから作り始める手法
プロセスから作り始めると、プロセス単位でデータ設計を行うことになるため、複数のプロセスで同じデータを別個に持つという冗長性が生じる可能性がある

DOA(Data Oriented Approach)

システムを作る際に、プログラムよりも前にデータ設計から始める手法
DOAはPOAの欠点を克服するために登場した
データの意味や形式が先に決まっていれば、複数のプログラムで共用することも容易で、業務要件の仕様変更にも柔軟に対応できるというメリットがある
そのため設計工程ではプログラム設計よりも先に、データ設計が行われる

データクレンジング

それまでの業務で利用されていたデータをDBに登録できる状態にすること、またその作業のこと
データクレンジングを疎かにすると、バッドノウハウが発生し、システムの品質が下がる
通常の業務データは、掃除が必要な汚れている状態であることを認識する必要がある
ただしそれはデータがあらゆる意味で使えないということではない
新たに構築されるシステムにとっては、使えなかったり非効率的なフォーマットであるというだけ
データベースの論理設計の前に、入力データのフォーマットが適切なものか調査し、必要であればデータそのものの変更やフォーマット変換が必要になる
データクレンジングとしては以下のようなものが挙げられる

一意キーの特定
顧客名でしか管理していない場合は、顧客コードを準備したりなど
名寄せ
標準化の一種。似通った名前を寄せ集めて統合する
人名や企業名の表記揺れを解消して名称を統一する
例えば人名において、苗字と名前の間のスペースがある場合とない場合の２つのデータが存在する場合に、それらを住所や他のデータと照合して、同一人物だった場合は統合する

３層スキーマモデル

スキーマとは、枠組みや構図という意味の単語
データベース設計においては、データベースの構造やフォーマットという意味で使う
スキーマは一般的に３つのレベルに分けられる

外部スキーマ(外部モデル) = ビュー
システムの利用者であるユーザから見て、データベースがどのような機能とインターフェースを持っているかを定義するスキーマ
データベースだけでなく、画面のUIや入力データなど、ユーザから見えるシステムの姿の一部であり、「ユーザから見たデータベース」といえる
概念スキーマ(論理データモデル) = テーブル
データベースに保持するデータの要素および、データ同士の関係を記述するスキーマ
開発者から見たデータベース
概念スキーマの設計を論理設計とも呼ぶ
内部スキーマと外部スキーマの独立性を高めるための、緩衝材のような役割を担う
内部スキーマ(物理データモデル) = ファイル
概念スキーマで定義された論理データモデルを、具体的にどのようにDBMS内部に格納するかを定義するスキーマ
DBMSから見たデータベース
テーブルやインデックスの物理的定義を含む
RDBもコンピュータ上で動く以上は、データは最終的にはファイルの形で管理される

概念スキーマと論理設計

論理とは物理層の制約にとらわれないという意味
物理層の制約には以下のようなものがある

CPUパワー
ストレージのデータ格納場所
SQLの構文
データ型

論理設計のステップ

エンティティの抽出

一部要件定義と重なっている
どのようなエンティティが必要となるか = どういうデータを扱いたいか
そのため要件を詰めていく段階で行うこととなる
エンティティの定義

各エンティティがどのようなデータを保持するかを決める
エンティティはデータを属性(attribute)、つまり列(column)という形で保持する
特に重要なことはキー列を定義すること
正規化

エンティティについて、システムでの利用がスムーズに行えるよう整理する作業
特にinsert, update deleteが整合的に行えるように、エンティティのフォーマットを整理することが目的
物理的な観点から見ると、エンティティを細かく分割していく作業
ER(Entity -Relational)図(Diagram)の作成

正規化を行うとエンティティの数が増える
エンティティ同士の関係が把握しにくくなるので、それを表現する図であるER図を作成する
ER記法とIDEFIXの２つを学ぶ

キー

主キー(プライマリキー)

その値を指定すれば、必ず１行のレコードを特定できるような列の組み合わせ
テーブルにおいて必ず１つ存在しなければならず、１つしか存在しない

複合キー

複数列を組み合わせて作るキー
複数列を組み合わせなければ主キーが作れない場合のキー

外部キー

AテーブルのカラムXがBテーブルのカラムYに存在することを保証している場合のカラムXのこと
外部キーの役割はテーブルに対して制約を課すことであり、この制約を「参照整合性制約」と呼ぶ
カラムYのレコードを削除する際には、それに応じたカラムXのレコードも削除するか、削除SQL文をエラーにするか選択可能
併せて削除する動作を「カスケード」と呼ぶ
更新の場合も併せて更新するか、更新SQLをエラーにするか選択可能

キーに選択するべきカラムの特徴

キーに選択するべきカラムは、コードやIDといった表記体系のきっちり定まったデータを、固定長文字列のデータ型に格納して使う
逆に可変長文字列をキーにはしない

制約

データの整合性を担保するために、表の列に入れられる値を制限する規則
データを挿入または更新するときに整合性制約がチェックされ、違反する場合はエラーが発生する

NOT NULL制約

NOT NULL制約が設定された列にNULLのデータを登録しようとしたり、NULLに更新しようとすると、そのSQL文はエラーとなる
テーブル定義において、列には可能な限りNOT NULL制約を付加する

UNIQUE 制約

ある列の組に一意性を求める制約。
主キーと似ているが、主キーがテーブルに対して１つなのに対して、一意制約は任意の数だけ設定できる

CHECK制約

ある列の取りうる値の範囲を制限するための制約。以下の様な条件を指定できる

20 ~ 100までの整数
開発、人事、営業のいずれかの文字列

正規化と正規形

正規形

DBで保持するデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式
１つの情報が複数テーブルに存在して無駄なデータ領域と面倒な更新処理を発生させてしまうことを避ける
DBはユーザの利用するデータを一元的に集約しているので、データ誤りは許されない。そのため正規化をする
ただし、正規化されたテーブル群に対するSQLが非常に遅くなってしまい、システム実用に耐えられないこともある
整合性とパフォーマンスはトレードオフの関係にある
ただし原則としては非正規化は許さない。あくまでも非正規化は最後の手段である
正規化の次数は高ければ高いほど良いと考える

正規化の利点

データの冗長性が排除され、更新時の不整合を防止
テーブルの持つ意味が明確になり、開発者が理解しやすい

正規化の欠点

テーブル数が増加するため、SQL文で結合を多用することになり、パフォーマンスが悪化する

第１正規形

定義 : １つのセルには１つの値しか含まない
１つのセルに複数の値を許すと、主キーが各列の値を一意に決定できない。つまり主キーの定義に反する
第１正規形のセルのことをスカラと呼ぶ

関数従属性

{X} → {Y}
YはXに従属する : Xの値が定まればYの値が一つに定まる
(XとYは一つの列ではなく、複数列の組み合わせでも問題無い)
正規化とは、テーブルの全ての列が関数従属性を満たすように整理していくこと

第２正規形

主キーの一部の列に対して従属する列がある場合、この関係を部分関数従属と呼ぶ
一方、主キーを構成する全ての列に従属性がある場合を、完全関数従属と呼ぶ
第２正規形とは、テーブル内で部分関数従属を解消し、完全関数従属のみのテーブルを作ること
第２正規化は、部分関数従属の関係にあるキー列と従属列だけ、独立のテーブルにすることで可能
平たく言うと、元のテーブルで邪魔だった従属列を外に追放すること

無損失分解

情報を完全に保存したままテーブルを分解する操作のこと
第２正規化は可逆的な操作なので、必ず正規化する前の状態にテーブルを戻すことができる
正規化されたテーブルから、非正規化状態のテーブルへ戻すには、分解したテーブルを結合すればよい

推移的関数従属

{X} → {Y} → {Z}
という段階的な関数従属のこと
非キー列がキー列に対してのみ従属する状態

第３正規形

非キー列が非キー列に対する関数従属性を持たないようにする
推移的関数従属を解消する
第３正規形も無損失分解である

正規化は常にするべきか？

第３正規形までは原則として行う
関連エンティティが存在する場合は、関連とエンティティが１対１に対応するように注意

正規化によるコストとその解決

検索

SQLにおける結合は非常にコストが高い操作である
結合するテーブル数、およびレコード数が増えれば増えるほど処理時間がかかる
正規化することでシステムパフォーマンスが劣化する原因の多くは、SQLの結合操作である

更新

第３正規化された状態での更新処理を行う場合は、１テーブルの１レコードが対象となり、更新コストは常に低く、かつ一定である
第２正規化以前の場合は、複数テーブルの複数レコードを更新する必要がある

非正規化による解決

あえて冗長性を追加することで、パフォーマンスを向上させる方法

非正規化は、検索パフォーマンスを向上させるが、更新パフォーマンスを低下させる
サマリデータを追加した場合は、データを登録する際に計算が必要となる
また更新時も計算が必要となる
データのリアルタイム性を低下させる
データの更新を都度行う負荷が大きい場合は定時バッチ処理が必要となるが、ユーザとしてはリアルタイム更新が最も便利である。その点でリアルタイム性を損なう可能性がある
後続の工程で設計変更すると手戻りが大きい
データモデルの変更はコードベースの修正に比べて、非常に改修コストが高い
データのフォーマットがプログラムを決めるDAO(Data Access Object)の大原則である
性能試験に問題があったのでテーブル構成を変更したい場合でも、コストの関係で却下されることも多い
ただしこれは正規化→非正規化であっても、非正規化→正規化であっても変わらない
そのため論理設計はかなり重要なタスクであると言える
そして論理設計には物理層の知識もかなり必要となる

冗長性の追加には以下の２パターンが考えられる

サマリ(集計)データ
その日の取引数であるとか、ある請求書の明細数であるとか、他のテーブルと結合することで得られる統計情報を、結合する必要が無い形でテーブルにカラムとして保持する
推移的関数従属が発生するため、第３正規形ではなくなってしまう
したがって更新時における問題が発生することになるが、検索処理が非常に簡単でハイパフォーマンスとなる
選択条件
ある期間に在籍していた社員の一覧を見たいなど、他のテーブルと結合することで得られる一覧情報を、結合する必要が無い形でテーブルにカラムとして保持する
主キーの一部から非キー列への部分関数従属が発生するため、第２正規形ではなくなってしまう
したがって更新時における問題が発生することになるが、検索処理が非常に簡単でハイパフォーマンスとなる

ER図

Entity Relationship Diagram
それぞれのテーブルがどういう意味を持って、テーブル同士が互いにどういう関係にあるのか
Entityとはテーブルのこと
Relationshipは関連のこと

2つの一般的な記法

IE(Information Engineering)記法
IDEF1X

1対1

2つのテーブルが１対１に対応するということは、２つのテーブルの主キーが一致するケース
普通は１つのテーブルにまとめてしまって問題ない
正規化の過程でこのようなテーブルが作られることは無い

1対多

最もよくある関連のタイプ
厳密には「1対多」と「0または1対多」に分かれるが、この２つのサブカテゴリをまとめて「1対多」と呼ぶ
また「多」についても「0以上」と「1以上」の場合がある

多対多

これは作ってはいけない関連だが、正規化の段階でしばしば出来上がる
そのような場合には関連実体(associative entity)を作成し、1対多を２つに分割することで多対多を解消する

カーディナリティ

相手のエンティティと対応するレコード数。
ER図の自らのエンティティの傍に記号として記述する。

独立エンティティ(非依存リレーションシップ)

他のテーブルのデータに依存することなく、データを保持できるエンティティ
主キーに外部キーを含んでいないことが条件。

従属エンティティ(依存リレーションシップ)

他のテーブルにデータが存在しなければ、データを保持することができないエンティティ

内部スキーマと物理設計

論理設計の結果を受けて、データを格納するための物理的な領域や格納方法を決める工程
ハードウェアやDBMSの個々の製品についての知識が必要となる

物理設計のステップ

テーブル定義
論理設計で定義された概念スキーマをもとに、DBMS内部に格納するためのテーブルの単位に変換していく作業
インデックス定義

インデックスは無くても機能的には問題無いが、パフォーマンスにおいて重要な役割を果たす
本の目次と同じように、どこに何があるかをダイレクトに示すので、直接該当ページへ飛ぶことを可能にする
ハードウェアのサイジング

１つ目は、システムで利用するデータサイズを見積り、十分な容量のストレージを選定すること
２つ目は、CPUやメモリ、ストレージのIOスピードなど、十分な性能を発揮できるハードウェアを選定すること
DBMSにおいて、データの整合性とパフォーマンスはトレードオフとなる
ストレージの冗長構成決定

可能な限り高い耐障害性を持つように構築する必要がある
ここでRAID(Redundant Array of Independent Disks) = 独立したディスクの冗長配列という技術を用いる
基本的な考え方は複数のディスクに同じデータを書き込んで冗長化することで、そのうち１本が壊れても残りのディスクが生きていればデータを保全できるようにするというもの
またRAIDによる利点の１つにI/Oを分散化することによる性能向上がある
ファイルの物理配置決定
DBのファイルをどのディスクに配置するかを考える
DBに格納されるファイルは用途別に以下の5種類に大別される

データファイル

ユーザがDB(テーブル)に格納するデータを保持するためのファイル
業務アプリケーションがSQLを通じて参照および、更新を行うファイルでもある
アプリケーションから見えるのはあくまでも「テーブル」という論理的単位であり、「ファイル」が直接見えることはない
最もファイルI/O量が多い。
インデックスファイル

テーブルに作成されたインデックスが格納されるファイル
DBMSではテーブルとインデックスは普通異なるファイルとして管理される
SQLでは特定のインデックスに対するアクセスを記述することは無いため、このファイルも直接は見えない
インデックスを使うかどうかはDBMSが内部で勝手に判断するため、業務アプリケーションやユーザがインデックスの存在を意識することは無い
ファイルI/O量が多い
システムファイル
DBMSの内部管理用に使われるデータを格納する
基本的に業務アプリケーションやユーザがアクセスすることはない
I/O量は非常に少ない
一時ファイル
DBMMS内部での一時的なデータを格納するために使われる
一時的なデータとは、SQLで使われたサブクエリを展開したデータや、 GROUP BY 句や DISTINCT を利用したときのソートデータなど
こうした一時データは、処理が終了すれば削除されてなくなるため、継続的にサイズが増加することは無い
ファイルI/O量が多い
ログファイル
DBMSはテーブルデータに対する変更を受け付けた場合、即座にデータファイルを更新しているわけではない
パフィーマンスを考慮して、一旦ログファイルに変更分をため込んだ後に、一括してデータファイルに変更を反映する
データファイルに反映が終われば不要になるため、継続的にサイズが増加するタイプのものではない
データ更新が多いシステムではI/O量が大きくなるが、一般的にはデータファイルに比べると少ない

インデックス

インデックスはDBMS内にテーブルとは独立に保持されるオブジェクト
(x, a)という形式の配列
x : キー
a : 値(実データへのポインタであることがほとんど)

アプリケーション透過的

インデックスを使う場合、DB側にインデックスを作成すれば良く、アプリケーションプログラムの変更は不要。
非正規化ではプログラム修正が必要だったので、それに比べるとハードルがかなり低い。

データ透過的

インデックスを作成することでテーブルに格納されているデータに影響を及ぼすことが無い。
またテーブル構造が変わることも無い。
そのため論理設計を修正する手戻りが一切無い。

性能改善効果

インデックスのもたらす性能改善効果は劇的。
これはインデックスの性能が、データ量に対して線形よりも緩く劣化するためである。
そのためインデックスによる性能改善はデメリットをメリットが大きく上回る。
ただし闇雲にインデックスを作っても効果がでる訳ではない。

B-treeインデックス

最もポピュラーなインデックスなのでデフォルトで使われることが多い。
汎用性が高くどんな状況でもきちんと仕事をする。

B-treeインデックスを作るべきとき

大規模なテーブルに
目安としてレコードが１万件以下であればほぼ効果は無い
ストレージやサーバ性能などによるので、固定的な値が無いのが実際のところなので、簡単に計測するのがベスト
カーディナリティの高い列に
カーディナリティとは特定の列の値が、どれくらいの種類の多さを持つかということ
特定のキー値を指定したときに、全体のレコード数が5%以上に絞り込めるだけのカーディナリティの場合はインデックス不要
それ以上の場合はインデックスが必要という判断となる
ただし注意点が2点
１つ目は、複合列に対してインデックスを作成する場合は、組み合わせで考えること
そして先頭に近いキーのカーディナリティが高いほど、効率的に絞り込める
２つ目は、カーディナリティが高くても、特定の値にデータが集中しているような列は向いていない
例えば1~100を取る列があり、100の出る可能性が99%で、それ以外が1%のような場合
100を指定したSQLでは非常に広範囲を探索する必要があり時間がかかる一方で、1~99を指定した場合はほぼピンポイントでヒットするため、検索性能が安定しない
WHERE句の選択条件、JOINの結合条件

B-treeインデックスの特徴

均一性

平衡木であるため、各キー値の間で検索速度にバラツキが少ない
平衡木とは、リーフもルートからの距離が一定の木
ただし平衡木であるのは、インデックスが貼られたときであり、テーブルへの挿入、更新、削除などが繰り返されることによって、インデックスの構造も崩れていき、非平衡木になっていくことがある。
自動的な修復機能も備わっているが、長期間の運用によって更新が重なれば木のバランスは悪くなり、探索にかかるコストもバラツキが出る。

持続性

データ量の増加に対してパフォーマンス低下が少ない
厳密にはlog nで増える。O(log n)
テーブルのフルスキャン O(n)に比べてかなり高速。

処理汎用性

検索、挿入、更新、削除のいずれの処理もそこそこ速い
O(log n)

非等値性

等号(=)に限らず、不等号(<, >, <=, >=, BETWEEN)を使ってもそこそこ速い
構築されるとき必ずキー値をソートするため、たとえリーフノードを一つに絞れなくとも、特定のノードよりも左とか右のノードだけに探索範囲を絞ることが可能となる。
逆に否定条件は、特定のノード以外の全てのノードが該当してしまうため効果が見込めない。

親ソート性

SQLは以下のような処理を記述した際は、暗黙にDBMS内部でソートが行われる。

集約関数(COUNT, SUM, AVG, MAX, MIN)
ORDER BY句
集合演算(UNION, INTERSECT, EXCEPT)
OLAP関数(RANK, ROW_NUMBER)

ソートはDBMS内部で専用のメモリ領域が与えられており、一時的にデータを保持した上で実施される。
ただしメモリに乗り切らないほどの大量データのソートの場合は、一時的にディスクに書き出している。
この場合のI/Oコストが非常に高いため、極力大きなソートは避けることがパフォーマンス上は望ましい。
B-treeインデックスは、構築時にキー値をソートして保持するため、B-treeインデックスが存在する列をORDER BY句のキーとして指定した場合、ソート処理をスキップすることが可能となる。

インデックスを使っている場合のSQLの注意点

インデックス列には演算を行わない
索引列に対してSQL関数を使わない
IS NULLを使わない
否定形を用いない
ORを用いない → INで代用する
LIKE述語では前方一致のみ有効
暗黙の型変換を行わずにCASTする

インデックス作成の注意点

主キーおよび一意制約の列には作成しない(二重インデックスとなる)
B-treeインデックスは更新性能を劣化させる(インデックスは独立のオブジェクトのため二重更新が必要)
定期的にメンテナンスを行う

ビュー

ビューとは、物理的にはSELECT文が書かれたファイルに過ぎない。ただし見た目はテーブルと同様。
ビューに対してSQL文によってアクセスが行われたとき、ビュー定義のSELECT文を実行して、オリジナルのテーブル(基底テーブル)にアクセスしている。
つまり、ビューへのアクセスは２段階のSQLが発行されているようなイメージ。
実際にはDBMS内部では、可能な限り２つのSELECT文をマージして、効率の良い形で実行しようとする。

RAIDによる冗長構成

基本的に全てのファイルを異なるディスク(RAIDグループ)に配置するのが理想だが、妥協する場合はI/Oコストの低いファイルから順にディスクをまとめていくことが考えられる
RAIDについて以下のことを考える必要がある

そのデータには、信頼性と性能のどちらが求められるのか
どのレベルでのRAIDを採用するか
何本のディスクでRAIDを構成するか

RAID0(=ストライピング)

データを異なるディスクに分散して保持する
I/O性能はディスク本数が増えるほど向上するが、冗長性は0
そのためRAIDではないという意見もある。
基本的に採用してはならない。

RAID1(=ミラーリング)

2本のディスクに全く同じデータを持つ。
冗長性は1本の場合に比べて2倍になり、2本が同時に壊れない限りデータは保全される。
ただしデータは分散されないため、性能は1本の場合と変わらない
また2本で1つのデータを保持するので、ディスクの使用効率も良くない
性能は求められないが、信頼性を求められる場合に採用

RAID5(=パリティ分散)

最低3本で構成し、パリティチェック(誤り符号訂正)を分散して格納する。
ディスクが壊れたとしても、パリティから実データを復元することが可能。
このため1本までならどのディスクが壊れてもデータを保全できる。
またデータを分散できるためI/O性能(read)の向上も期待できる。ディスク本数が増えるほど性能が向上する。
パリティの計算を行うためwrite性能は高くないが、DBはwriteよりreadの方がデータ量が多いため、read性能が重視される。
RAID10が採用できない場合に採用。

RAID10(=RAID1+0)

RAID1とRAID0を組み合わせたもの。
最初にRAID1のグループを2つ作り、そのグループを使ってRAID0を作る。
RAID1は高信頼性、RAID0は高速性があり、その両立を目論む。
欠点は最低でも4本のディスクが必要となるためコストが高いこと。
コスト問題が無いのであれば基本的にはこの構成を採用するべき。

バックアップ設計

バックアップとは基本的にはファイルのコピーのこと
バックアップ設計には２つの方針がある

極力データを失わないように設計する
障害によってデータが失われた際に、復旧できるようにしておくこと

主要なバックアップ方式

以下の３通りで、これらを組み合わせて行う
バックアップコストとリカバリコストはトレードオフの関係にあることを意識する必要がある

完全バックアップ

ある時点でそのシステムで保持されている全てのデータをバックアップする方式
あるタイミングにおけるスナップショットを取るという表現もされる
そのファイルさえあれば、バックアップ時点のデータを全て復元することが可能
欠点としてバックアップの時間が長い、ハードウェアリソースへの負荷が高い、サービス停止が必要などがあげられる

差分バックアップ

フルバックアップを行った時点に対して、変更分(差分)をバックアップする方式
差分管理はログファイルで行う。ログファイルにはデータベース内のデータに対するあらゆる変更操作の履歴が残っているので、データベースに対する変更操作をもう一度再現することが可能
利点はバックアップデータ量が減ること
欠点はリカバリの際は、フルバックアップファイルと、差分ファイルの両者が必要となること。また差分ファイルには重複が生じる
またリカバリの手順も複雑化しやすい

増分バックアップ

差分バックアップの差分ファイルの重複を無くした方式
利点はバックアップデータ量が最小となること。もちろんバックアップに要する時間も最短となり、ハードウェアリソースへの負荷も最小となる。コスト的には最も優れた形式
欠点はリカバリ手順が複雑化したり、リカバリにかかる時間が長くなること
復旧に必要なファイルが増えるため、完全にデータ復旧できる可能性が最も低くなる

リカバリ設計

リカバリ設計はバックアップ方式が決まれば自動的に決まる
バックアップファイルだけでは「障害の直前」の状態まで戻すことは不可能(バックアップ後もデータ更新があるため)

リストアとリカバリ

リストアとは、バックアップファイルを戻す作業のこと
リストアは和訳すると「ファイルの再配置」
リカバリとは、リストアされたファイルに対して、トランザクションログを適用して変更分を反映する作業
トランザクションログはDBMS内部にも残っており、最後のバックアップ後に実施されたユーザからの変更がファイルとしてDBサーバに残っている

リストアとロールフォワード

リストアおよびリカバリの手順は以下の３段階となる

フルバックアップのファイルをDBに戻す : リストア
差分(or 増分)バックアップしていたトランザクションログを適用する : リカバリ
DBサーバに残っているトランザクションログを適用する : ロールフォワード

統計情報

SQLのアクセスパスを決める要因であるメタデータ
DBMSはSQLを受け取ると、どのようなパスでデータを探しに行くのが最も効率的かを判定する
それはユーザが指定するのではなくDBMS側の仕事

オプティマイザと実行計画

DBMSはSQL文を受け取ると最初にparserモジュールが、適切な構文であるかをチェックする
次にoptimizerモジュールが、アクセスパス(実行計画)を決定する
その際に必要となるのが統計情報。optimizerはカタログマネージャというモジュールに、統計情報の照会をかける
カタログマネージャは統計情報を管理するモジュール
optimizerが統計情報を元に、最短経路を選択し、SQLを手続きに変換し、実行計画を得る
それに従って実データであるテーブルへとアクセスを行う

統計情報の設計指針

統計情報収集タイミング

データが大きく更新された後、なるべく早くに収集すべき
更新処理のよってテーブルデータが大きく変われば、古い統計情報と最新のテーブル状態に齟齬が生じるため
ただし統計情報収集というのは、それなりにリソースを消費し、処理時間も長い(テーブルの規模が大きいと数時間)
そのため実際には統計情報収集は、システムの利用者が少ない夜間帯に実施することが原則

統計情報収集の対象範囲

大きな更新のあったテーブルが対象となる
データがあまり変更されていないテーブルまで収集すると、時間とリソースを無駄にする

統計情報の凍結(ロック)

設計の基本は統計情報を最新の状態にすること
しかし現状の実行計画を変化させたくない場合、あえて統計情報の収集を行わないという例外的なケースも存在する
現在使われている経路が、将来にわたってもデータへの最短ルートであり、現状維持が最適とわかっている場合に凍結する
具体的にはサービス終了時のデータを想定した状態での統計情報が存在する場合

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up