釣りキチプログラマー翔平の備忘録 
はじめに
この記事では、
- テーブル設計のきほん
- トランザクションのきほん
- クエリのきほん
- 実行計画の取得〜解析〜パフォーマンスチューニング
- パーティショニングテーブル
- データベース運用のきほん
についてまとめました。
僕の10年間現場でデータベースを触ってきて失敗した経験、
書籍・勉強会などを通じて学習した内容、
などを言語化して、
もし新人プログラマー時代の自分にデータベースについて伝えるとしたら？？
というテーマでまとめています。
テーブル設計のきほん
テーブル設計の際、
まずはどのカラム(属性)をキーとして扱うのかを整理する必要があります。
データベースのテーブル設計で登場するキーの種類は様々あります。
それらキー情報の種類とそれらの特徴について
こちらの記事にまとめました。
主キー、候補キー、外部キー、スーパーキーなどのいろんなキーについて｜データベースの基礎
どの属性をどのキーとして取り扱うか整理できたら、
次は属性間の従属関係を整理する必要があります。
とあるキーが決まったら、他の属性も一意に決まったり、
とあるキーが決まったら推移的に他の属性が一意に決まったり、
複数のキーの組み合わせが決まるとようやく他の属性が一意に決まったり・・・
属性間の従属関係を整理する方法や考え方について
こちらの記事にまとめました。
関数従属性と関数従属性の推論則
ここからは
いよいよテーブルの正規化についてです。
正規化とは何か、
属性情報をどのように整理して正規化していくのか、
をこちらの記事にまとめました。
データベースの正規化の目的とは？？｜非正規形〜第３正規形の正規化のやり方
正規化については、
実践形式で手を動かしながら理解するのが
僕はオススメです。
データベーススペシャリストの問題を使いながら、
実践形式で正規化する練習方法について
こちらの記事にまとめました。
データベースの正規化の練習｜データベーススペシャリスト過去問を解きながら練習方法解説
練習方法が分かったら
さらに他のデータベーススペシャリストの過去問を
使って実践練習を繰り返して自分のものにしてください。
ここまできたら、
現場でER図を見たり、テーブル設計をしたり、
他人のテーブル設計のレビューなどもできると思います。
おまけ・・・
テーブル設計で正規化が十分できていない場合に
発生する更新時異常について
こちらの記事にまとめたのでご覧ください。
テーブルの正規化が不十分な場合に発生する更新時異状の具体的なパターンと対策｜データベース基礎
トランザクションのきほん
例えば口座振替をする際、
振込元口座のお金を出金＋振込先口座にお金を入金
をセットで行う必要があります。
お金を出金したものの、入金は途中で失敗するなどは許されません。
その辺りの基本的なトランザクションやACID特性の考え方を
こちらの記事にまとめました。
トランザクションのACID特性とトランザクション分離レベルが不十分な場合の挙動
また、
データベースには複数人が同時にアクセスしてきます。
それに対してどのように整合性を取るのかということへの考慮も必要です。
ロックや排他制御、コミットメントなどについて
こちらの記事にまとめました。
データベースの基礎【トランザクション管理機能(コミットメント制御)】
共有ロック、占有ロック、デッドロック｜データベースの排他制御
ここまでくると、
アプリケーションを開発する際に、
どこまでを同一トランザクションとしてまとめ、
どこから別トランザクションとすべきか、
トランザクションを分ける方針にするなら途中でエラーが発生したら
どのようにコミットしたトランザクションを戻すか。
といった、エラーハンドリングを考えられるようになります。
また、同時アクセスされても整合性を保つためには、
どの範囲でロックをかけるのか、
デッドロックを発生させないためには
アプリケーションをどのような作りにすべきか、
なども考えられるようになります。
アプリケーションを機能性・保守性を考慮して作ることと同じくらい、
このあたりを考慮して作ることは重要だと思います。
クエリのきほん
現場でアプリケーション開発をする際、
ほぼ確実にSQLを書くことになります。
そもそもSQLの読み書きが苦手な方はまずこの本で
基礎を学ぶのがオススメです。
(自分はこの本と現場の複雑なクエリを読み漁りながらSQLの基礎を身につけました)
SQLの基礎を学んだら、
実際にクエリを書いてデータの取得・更新するのが
いいと思います。
こちらの記事を参考に、
クエリを書く練習をしてみてください。
DBeaverの使い方を解説！！postgreSQLに繋いでSQLを投げたりER図を見たり
実行計画の取得〜解析〜パフォーマンスチューニング
本番環境では数年間データベースが運用されるなかで、
大量のデータが積み上がっていたり、
短時間に大量のトランザクションが発生したり、
日次で大量のデータを集計しなければならないこともあります。
そのような場合、ただクエリが書けるだけでなく、
データベースの内部構造を知り、
クエリ実行時の動作イメージを理解し、
インデックスなども利用しながら
無駄なディスクI/Oを減らしながら目的のデータにアクセスする必要があります。
まずはデータベースの内部構造を知りましょう。
こちらの記事をご覧ください。(PostgreSQL)
内部で動くプロセスの役割を知る〜PostgreSQLのきほん〜
内部構造が理解できたら、
パフォーマンスチューニングに挑戦してみましょう！！(PostgreSQL、SQL Server)
パラメータチューニング〜PostgreSQLのきほん〜
クエリチューニング〜PostgreSQLのきほん〜
データベースのインデックスの種類と内部の仕組み｜SQL Serverで数億規模のデータ量を扱ってみて
SSMSでSQL Serverの実行計画を見てSQLチューニング
アプリケーション開発では、
機能要件を満たすように開発するのは当たり前ですが、
非機能要件も満たせなければ本番では使えないシステムになります。
ただ、機能要件に比べると、
非機能要件の対応はどうしても後回しにされがちです。
(非機能要件が軽視されて性能検証する工数すら確保してもらえないこともあったり。。。)
データベース周りの設計をする際は特に、
普段から非機能要件のことも考慮しましょう。
パーティションテーブル
テーブルをパーティショニングすることで、
- 定期的に過去データを削除する際のオーバーヘッドが減らせる
- パーティショニングキーが検索条件の場合検索性能が向上させられる
などのメリットがあります。
テーブルのパーティショニングについては
こちらの記事にまとめました。(SQL Server)
SQL Serverでテーブルパーティション化して実行計画の前後比較
データベース運用のきほん
本番環境ではデータベースを
トラブルなく何年間も運用していく必要があります。
データベースは計画的に定期的にメンテナンスしておかなければ
だんだんクエリの検索性能が劣化してきたりします。
こちらの記事にデータベースのメンテナンスについてまとめました。
テーブルをメンテナンスする〜PostgreSQLのきほん〜
インデックスをメンテナンスする〜PostgreSQLのきほん〜
また、
データベースを運用していくには、
何かトラブルが発生した際に素早く問題を特定したり、
予兆を早く察知する必要があります。
これらのためには監視が必要です。
監視についてこちらにまとめました。
監視する〜PostgreSQLのきほん〜
今回のテーマについては以上です。
