いちから復習したい方のための「物理設計」概論 研修コースに参加してみた
今回参加した研修コースは いちから復習したい方のための「物理設計」概論 です。
実はデータベースを含め、設計を研修で扱おうとすると、とっても難しいんです。なぜなら、各社、各プロジェクトによって設計書のフォーマットが異なるからです。なので、受講者1人1人でフォーマットが異なると、期待することが変わってしまいます。
林さんはその問題に対して工夫して、はじめに受講者にその違うことを体験してもらい、そこから共通化するところにフォーカスして、構築仕様書にどんな項目が必要なのか、解説いただきました。
担当プロジェクトが変わっても抑えるべき要点は理解している、ということに繋がるので、構築作業だけから構築仕様書などを書くフェーズにステップアップしようとするデータベースエンジニアにはとてもオススメです !!
では、どんな内容だったのかレポートします!
もくじ
想定している受講者
- リレーショナルデータベースとはどんなものか理解している
受講目標
- データベース構築仕様書、物理設計書の作成ができる
今日の事前アンケートと講師紹介
SEカレッジではコース開始前に事前アンケートを入力いただき、できるだけ期待内容にできるよう講師も準備しています。
このコースでは、
いちから復習したい
とタイトルに記載していたのですが、パフォーマンス改善に関するご希望も割とあったので、その点を配慮しながら進めます。もし違っていたら、表情で示してください、という配慮されたコメントでスタートしました。
そんな柔らかなスタイルでスタートされる 林 優子さん が登壇されました。
データベース設計
実は人によって設計の解釈が違うので、まずは認識合わせです。
また、サンプルとして 給与テーブルの設計 を例に話を進めます。
- 概念設計
- 業務要件を把握 -> 管理する項目を洗い出す -> 紐付ける
- 例えば、日給制、月給制なのか、基本給があって、残業は何時からなど
- 正規化されたER図がアウトプットされる
- 論理設計
- ER図をもとに性能要件もここでやります
- 性能要件をここでやるのが林さんのオススメ
- 例えば、5000人が1年間働いて7年間データを保管する、という要件なら?
- 「出勤/退勤だけでなく工数管理にも使うとなると?」
- 「primary keyだけでなく索引検索も必要だなぁ」
- 「テーブル分割しておいたほうがよいなぁ」
- ここで非正規化したER図がアウトプットされる
- ER図をもとに性能要件もここでやります
- 物理設計 // 性能要件をここでやる人もいる
- 非正規化したER図をもとにハードウェア/ソフトウェアの性能を加味して設計する
- 例えば、ストライピングディスクを使って、マルチCPUを使えるなら?
- テーブル分割はパーティショニングでよいなぁ
- 設計の方針をもとに実装方法が変わる
- オブジェクト定義書と構築仕様書がアウトプットされる
- SQL文
CREATE TABLE
やCREATE INDEX
も含まれる
このコースでは、この構築仕様書を作る、ことが主題になります。
というわけで、この2つのドキュメントにどんな項目が含まれていると良いのか、アプリケーションとインフラ、受講者が双方の立場になって考えてみます。
受講者が考えた項目
- サーバー構成
- 操作するためのツール
- 許可するSQL (使い方など)
- データ型
- primary key や foreign key
- カラムと桁数
- 項目名と名称
- テーブル間の関係
- バックアップやリカバリの手順書
- ミドルウェアの構成やバージョン
- テーブルサイズや成長率
- ログの場所
- 削除タイミングなど初リサイクル
- テーブルスペースやエクステントの定義
- NULLが含まれるかどうか
- 区分値
役割、各社、各PJによってぜんぜん違っていることに気付きますね。
データベース
エクステントなど色々な項目が出てきたので、改めて、データベースの構造を解説します。
- 三本足の線は 1:多 の関係を表す
- RDBMS によって呼称が異なるので、ここではデータファイルとログファイルをデータベースと呼ぶ
- 表領域というのがテーブルを格納する論理的な場所
- OSからみた物理的なディスクの割当は違うので、表領域を複数のセグメントで保存している
- そのセグメントに
CREATE TABLE
するとどれぐらいのサイズを確保するのか- そのサイズのデフォルト値をエクステントという単位で決めている
- 通常は連続した8ブロック (8KB/1ブロック)、64KBを指している
- エクステントという単位を挟まず、ブロックだけでよくない?
- 現にSQL Serverでは1ブロックしか確保しない
- ブロックが点在すると、全件検索で使うシーケンシャルサーチで使いにくい
- なので 1エクステント 連続した8ブロックを確保する
- 昔はテーブルごとにエクステントの単位を決めていたが、いまは64KBでやっている
- 索引検索中心なのか全件検索中心なのかでテーブル毎に単位を変える場合がある
- 索引検索は1エクステント64KB、全件検索は1エクステント128KBなど
- 一応ブロックサイズ (8KB) も変えられる
ER図をもとに構築仕様書を書いてみましょう
このER図をもとに演習してみましょう!!
- テーブル
- 注文、注文明細、顧客、商品番号のうち2つ
- 演習内容
- テーブル定義書を書く
- (索引定義書を書く)
項目は先に挙がった項目を埋めていきましょう
林さんが考えた仕様書
- ドキュメントは直感的に理解しやすいよう、テーブル名やカラム名も日本語で書くと良いです
- 空白だと書き忘れたと思ってしまうので、例えば、有効桁数のセルは ‘デフォルト値’ など何かしら入力しておく
- 有効値はあると、テストのときに楽です
- 制約はFK、PKなどそれぞれ項目を作っておき、ありナシとかではなく数値で管理すると良いです
- 索引もパフォーマンスを考えて 1, 2 で順番を付けられる
- 例えば、削除フラグのようなカラムがあれば、入る値は0,1なのかなど有効値を書いておくとよいです
- それに合わせてDFAULT値をDBで設定できるので、それがなにかも書いておくとよいでしょう
- データ型もRDBMSによって変わるので、それも書いておくとよいでしょう
- 年月日を扱うのか、日時を扱うのか、など
成長率は下のように日、月、年単位でテーブル、カラムごとに書いておくとよいでしょう
CRUDマトリックスも書いておくと良いです (下のような表)
- 縦軸に処理名と横軸にテーブル
- C: INSERT // Create
- R: SELECT // Read
- U: UPDATE
- D: DELETE
- 例えば、RとDが集中する処理がわかる
- ディスクI/Oが発生するのでディスクを分けようか、というアプリ開発側とインフラ側がディスカッションができる
データファイルとログファイル
データファイルとログファイルをどのように管理するのか、これも構築仕様書に書くので、まず、どのようにログファイルを生成しているのか、RDBMSのアーキテクチャを振り返りました。
ただ、このアーキテクチャは林さんの他コースでも触れられていますので、割愛して、どのような点を仕様書に反映すべきなのか、仕様書とともに解説いただきました。
- データファイルとログファイルの構築仕様書
- データファイルとログファイルの注意点
- データファイルとログファイルはそれぞれリカバリで使う目的・タイミングが異なる
- それぞれ別のディレクトリに、書き込みが速いディスクにはログファイルを保存するなど指示が必要
- ログファイルが失われるとリカバリできないので、冗長化しましょう
- データベースにその機能がある
- ログファイルは循環的に更新すれば良いので、増分することはありません
- RDBMSによってログファイルのファイル数は変わるので気をつけましょう
その他、受講者の方から挙がった項目を解説して、このコースは終了となりました。
まとめ
このコースでは物理設計を受講者の方が考えた項目をもとに、どのように構築仕様書で表現するのか、自身で演習したり、講師の解説で学びました。
また、なぜその項目が必要なのか、どのように書くべきなのか、アーキテクチャから振り返って順序よく解説頂いたことで、とても理解しやすいものでした。
特定プロジェクトの仕様だけを知っているだけではない汎用的なスキルや、構築を行うだけからステップアップしようとするエンジニアにはとてもオススメの内容でした !!
SEプラスにしかないコンテンツや、研修サービスの運営情報を発信しています。