釣りキチ翔平プログラマー備忘録 
はじめに
この記事では、
アプリケーションエンジニア(どちらかというとバックエンドエンジニア)が、
現場でデータベースを利用する際に抑えておきたい、
データベースの基礎知識についてまとめました。
このあたりについてまとめています。
- テーブル設計の基礎と正規化について
- トランザクションとACID特性について
- コミットメントとロックについて
- 実行計画の読み方とクエリチューニングのポイントについて
- パーティショニングテーブルについて
テーブル設計の基礎と正規化について
テーブル設計の際、
まずはどのカラム(属性)をキーとして扱うのかを整理する必要があります。
データベースのテーブル設計で登場するキーの種類は様々あります。
それらキー情報の種類とそれらの特徴についてまとめたのが以下の記事です。
主キー、候補キー、外部キー、スーパーキーなどのいろんなキーについて｜データベースの基礎
どの属性をどのキーとして取り扱うかが整理できたら、
次は属性間の従属関係を整理する必要があります。
とあるキーが決まったら、他の属性も一意に決まったり、
とあるキーが決まったら推移的に他の属性が一意に決まったり、
複数のキーの組み合わせが決まるとようやく他の属性が一意に決まったり・・・
属性間の従属関係を整理する方法や考え方については以下の記事にまとめました。
関数従属性と関数従属性の推論則
データベースのテーブルで登場するキーの種類とキーの選定、
また、属性間の従属関係の整理のやり方について整理できたら、
いよいよテーブルの正規化についてです。
正規化とは何か、
属性情報をどのように整理して正規化していくのか、
を以下の記事にまとめました。
データベースの正規化の目的とは？？｜非正規形〜第３正規形の正規化のやり方
正規化については、
データベーススペシャリストの問題を使って
手を動かしながら理解するのが個人的にはオススメです。
以下の記事でデータベーススペシャリストの問題を解きながら
具体例を使って正規化していく流れを解説しました。
データベースの正規化の練習｜データベーススペシャリスト過去問を解きながら練習方法解説
ここまでで、
データベースのテーブル設計をするための基本的な考え方は一通り抑えられ、
現場でER図を見たり、テーブル設計をする際、スムーズだと思います。
自分は、
データベーススペシャリストの問題文から
データモデリングをしたり属性の関係性やキー情報を整理して
正規化する作業が楽しくてハマってしまったのですが、
そこまでやらなくてもいいとは思います（笑）
データモデリングをひたすらやってた経験が、
ドメインモデリングをする際にもに役に立ってる気はします。
(ドメインモデル図を作るのもめちゃくちゃ好きです)
これはおまけですが、
正規化が十分できていない場合に発生する
更新時異常についても理解しておくと、
システムリリース後、
本番環境でトラブルを回避できるので、
興味があれば以下の記事を参考にしてみてください。
テーブルの正規化が不十分な場合に発生する更新時異状の具体的なパターンと対策｜データベース基礎
トランザクションとACID特性
例えば口座振替をする際、
振込元口座のお金を出金＋振込先口座にお金を入金
をセットで行う必要があります。
お金を出金したのはいいけど入金は途中で失敗するなどは許されません。
その辺りの基本的なトランザクションやACID特性の考え方を
以下の記事にまとめました。
トランザクションのACID特性とトランザクション分離レベルが不十分な場合の挙動
また、
データベースには複数人が同時にアクセスしてきます。
それに対してどのように整合性を取るのかということへの考慮も必要です。
ロックや排他制御、コミットメントなどについて以下の記事にまとめました。
データベースの基礎【トランザクション管理機能(コミットメント制御)】
共有ロック、占有ロック、デッドロック｜データベースの排他制御
ここまでの内容が理解できると、
アプリケーションを開発する際に、
どこまでを同一トランザクションとしてまとめ、
どこから別トランザクションとすべきか、
トランザクションを分ける方針にするなら途中でエラーが発生したら
どのようにコミットしたトランザクションを戻すか。
といった、エラーハンドリングを考えられるようになります。
また、同時アクセスされても整合性を保つためには、
どの範囲でロックをかけるのか、
デッドロックを発生させないためには
アプリケーションをどのような作りにすべきか、
なども考えられるようになります。
アプリケーションを機能性・保守性を考慮して作ることと同じくらい、
このあたりを考慮して作ることは重要だと思います。
僕はこのあたりの考慮が足りず、
アクセスが集中してくるとデッドロックが発生したり、
同時アクセスでデータが壊れてめちゃくちゃ大変なデータパッチする。
という修羅場を何度も現場で見かけました。。。
(自分はまだやらかしたことはないけど、、、)
SQLの読み書きとSQLチューニングのポイントと実行計画の見方
データベースを利用するアプリケーションを作成する際、
SQLを書くことになります。
僕は新卒で開発現場に入って、
このSQLが複雑で読むのに苦労した記憶があります。。。
SQLが苦手な方はまずこの本で基礎を学ぶのがオススメです。
(自分はこの本と現場の汚くて複雑なクエリを読み漁ってSQLの基礎を身につけました)
SQLの基礎を学習したら、
実際に書いてデータを取得したり、更新したりして練習するのが
スキルアップの近道だと思います。
以下の記事でローカル環境でPostgreSQLを立ち上げて、
SQLを発行する練習をしてみてください。
DBeaverの使い方を解説！！postgreSQLに繋いでSQLを投げたりER図を見たり
現場では、
データベースにクライアントツール(上の記事だとDBeaver)を使って
SQLを作って実行したり、データを取得することは当たり前のように
求められるので、苦手な人はローカル環境で練習しておきましょう！
ここまででSQLは書けるようになったら、
今度は本番環境で数百、数千、数億のデータを取り扱うのに必要な
知識を身につけていきましょう。
そのためには、
DBMS(Database Management System)の内部構造や、
クエリ実行時の動作イメージ、
インデックスについての理解や実行計画の見方
などを抑えておく必要が有ります。
自分が今まで触ったことがあるDBMSがSQL ServerとPostgreSQLなので、
それらを例に解説していますが、
基本的な仕組みや考え方は他のDBMSでも共通している部分が多いと思います。
DBMSの内部構造やインデックスについて解説した記事が以下になります。
SQL Server
データベースのインデックスの種類と内部の仕組み｜SQL Serverで数億規模のデータ量を扱ってみて
PostgreSQL
PostgreSQLの内部で動作しているプロセス｜パフォーマンスチューニング時に役立つポイントに絞って解説
SQLチューニングのポイントと実行計画の見方について解説した記事は以下になります。
SQL Server
SSMSでSQL Serverの実行計画を見てSQLチューニング｜ポイントに絞って解説
PostgreSQL
PostgreSQLの内部構造とクエリチューニングのポイントについて図で解説
アプリケーション開発では、
機能要件を満たすように開発するのは当たり前ですが、
非機能要件についても満たせなければ本番では使えないシステムになります。
ただ、機能要件に比べると、
非機能要件についての対応はどうしても後回しになりがちです。
(非機能要件が軽視されて性能検証する工数すら確保してもらえないこともあったり。。。)
大量データを取り扱うようなバッチ処理や、
大量データを取り扱うオンライン処理を作る場合は、
DBMSの内部構造を抑えた上で、最適なチューニングが行えるスキルはかなり重要です。
今まで現場で多くのアプリケーションエンジニアを見てきましたが、
非機能要件面までの対応がきちんとできるアプリケーションエンジニアは
少なく、かなり貴重だと感じています。
他のアプリケーションエンジニアとの差別化を図り、
自分の市場価値を高めるという意味でも、
この辺りまでできるアプリケーションエンジニアは強いと思います。
パーティションテーブル
僕は数億規模のデータが登録されたテーブルを取り扱ってみて、
テーブルのパーティショニング化をきちんと最初に考えられなかったことを
非常に後悔しました。。。
テーブルをパーティショニングすることで、
- 定期的に過去データを削除する際のオーバーヘッドが減らせる
- パーティショニングキーが検索条件の場合検索性能が向上させられる
などのメリットがあります。
データベースの本番運用まで考えると、ここまでやるべきだったのですが、
現場ではそこまでできず、
パーティショニング化の検証結果のみ記事にまとめました。
SQL Serverでテーブルパーティション化して実行計画の前後比較
データベース運用
データベースを運用していく中で必要な、
テーブルメンテナンスとインデックスメンテナンスのポイントを、
データベースの内部構造を図解しつつまとめて見ました。
PostgreSQLでのテーブル・インデックスメンテナンスについて内部構造からポイントを理解する
今まで自分が入った現場では、
DBAを専属でシステム開発のチームに入れた体制を組むことができず、、、
性能劣化を検知りたら、
都度アプリケーションエンジニアの自分が調べて解決してきたのですが・・・
その経験の中で、
アプリケーションエンジニアでも
ナレッジとして持っているといいかなと感じたポイントに絞ってまとめました。
まとめ
今回は自分の10年間プログラマーとして現場を経験してきて、
プログラマーが抑えておきたいデータベース周りの知識をまとめました。
参考になることがあれば幸いです。
今後も、データベース関連で現場でハマったり重要だと感じることがあれば、
都度追記していきます！！！
