RDBMS DBのベストプラクティスは禁止事項がいっぱい

  DB >  

RDBMSを使う上で小松が思うベストプラクティスを説明します。

#8 RDBMS DBのベストプラクティスは禁止事項がいっぱい【主張】 - YouTube

DBはあくまでデータをためる機能なので、餅屋は餅屋的な考えで基本的にはDBに機能を持たせないようにすべきです。

なので、・トリガー・ストアド関数、ストアドプロシージャ・外部キー・OLTP上のプログラムでのJOIN・サロゲートキー・on update更新時のデフォルト値を禁止すべきです

トリガー、ストアド関数の禁止の理由

コーディングすれば誰がいつ何を変更したかのバージョン管理ができる所をバージョン管理できないトリガー、ストアドをあえて使用する理由はないです

ストアドでシステムに組み込んでしまうとそのストアドがなんのために作られたのか　どこまでの影響があるのかなど違う人からするとわからないため、運用保守で苦労します
実際一旦システムに組み込んでしまったストアド関数を削除も変更もできずどんどん　深みにはまって抜けられなくなっている現場を経験しています。

どこで使われているか一つのレポジトリだけでなく複数のレポジトリを調査しないといけない　分ゴッドオブジェクトのさらに強力版と考えれるかと思います。

外部キーの禁止の理由

外部キーがあると物理削除でキーがあるため削除できないとエラーになります。　

じゃぁ、カスケードで一気に削除するのかっていうとそんなリスクは取れません

なので、エラーになったキーの元を逐一調べて、そのカラムの影響範囲を調べてと　削除にかかる工数が膨大になった経験があります。

削除する運用保守の時期でなくても開発の時期でも部分的にデータを登録して　テストしたい時でも外部キーがあるがゆえいちいちそれにまつわるデータも登録しないといけないってなります。

その分開発のテストに工数が膨らみ遅れます外部キーもインデックスの一部でインデックスはできる限り最小に留めるべきです。

OLTP上のJOINを禁止の理由

JOINを使うとインデックスを貼りまくりたくなります

インデックスの貼り過ぎは逆にそのテーブルの速度を遅くさせてしまいます。

インデックスを一回貼ると削除したい時が出ても、削除すればどこかのシステムが急激に遅くなる可能性があるため全てのシステムの確認が必要になってきます。

現実的にはそういうことは不可能なのでインデックスの削除をした現場を経験したことはないです。

なので一旦インデックスを貼ると後戻りはできないです。

それと、JOINがいっぱいあるシステムからDBを分けて負荷を下げたい場合　ほぼ不可能に近いぐらいにリスクも高く工数も高くなります。

あらかじめJOINではなくプログラムの配列の結合でデータの結合をおこなっていれば　DBの接続の部分だけの変更で済みます。

JOIN禁止の検証は別動画で検証していきます。

これらの理由がDBに機能を持たせない理由です

スキーマの構成については当たり前かも知れませんが、基本的にテーブル数　カラム数　インデックス数は増やせば増やすほど複雑になって保守が大変になるので最小限に留めるべきです。

 

プライマリーキーについてはテーブルに１つが原則

１つのテーブルに複数のプライマリーキーを今まで見たことがないのと、どういう時に必要なのかわからないですが、経験上ないので不要でいいかと思います。

サロゲートキーより、複合プライマリーキー

サロゲートキーをプライマリーキーにしても結局使わないので、複合プライマリーキーにすればその分カラム数を減らせます。

なので、サロゲートキーも禁止でいいかと思います。

マスターテーブルのプライマリーキーはintegerにしない方がいい場合もありました。

100ぐらいあるEメールのテンプレートをマスターテーブルで管理する時プライマリーキーをauto incrementのintegerにすると、検証DBと本番DBでIDを合わせる事が大変でしたので、テンプレートの名前をプライマリーキーにした経験もあります。

ユニークキーの機能に関しては禁止はなく必要な時に使えばいいかと思います。

 

名前に関しては

IDというプライマリーキーは存在すべきではなく、テーブル名＿＋ID　user_idとかにすべきです。

でないと「user_id」のキーワードでソースコードが検索できず、「id」で検索すると不要なidで引っかかる全ての単語が検索されてしまうのでDB内でユニークにすべきです。

プライマリーキーでなくても、status,category,type,group,nameなどもテーブル名＿＋statusにして少しカラム名は長くなってしまいますが、違う意味で同じカラム名は避けるべきかと思います。

 

DBのアカウントとアクセス権ですが

少なければ少ないほうがシンプルでいいのですが、プログラマーの権限、オペレータの権限などはどうしても分けざるを得ないと思います。

その理由は、誰のクエリーが原因でシステムが止まっている、誰のクエリーがスロークエリーになっているなどの時にデバッグがしやすいからです。

分け方はbackend,frontengineer,marketing,analysisのようなチーム毎がいいかと思います。

このチーム毎のアカウントを次の動画で説明するLinuxのアカウントと同じ単位でしておけば運用はしやすくなるかと思います。

そうする事でAPIをわざわざ作らずともDBのアカウントを分ければマイクロサービスの一部の要件を満たせるようになります。

マイクロサービスに関してはまた別の動画で説明していきます。

 

データタイプに関して

数値型はinteger, bigint, real, numericなど使い分けをする必要がありますが、文字列はtextの一択で問題ないです。

パフォーマンスも変わらないのとvarcharで文字列制限すると運用しているとどこかで文字列制限にぶつかってエラーになる事が多々ありました。

時間日付型はdate, time, timestampの３つのデータタイプを使い分けるだけでいいかと思います。

基本この３つでそれ以外のjsonやenumなどはよほどの事がない限り使わないほうがいいです

jsonのカラムで複数の値を入れる時はリレーションを付けて違うテーブルで管理したほうが管理しやすいです。

以前にjsonのカラムに複数の値を入れるようにして検索がかなり遅くなって失敗した経験があります。

 

nullに関しては

バグの温床になるので許容はしないほうがいいです。

「null 参照」で検索するといっぱい問題が指摘されています

なので、nullを許容する代わりにデフォルト値を文字列であれば空文字タイムスタンプであればnow()で、数値型の場合は０でカラム登録すべきです。

ただ、この運用の問題点はユーザー情報が入っているような件数が多くて負荷の高いテーブルで単純に

alter table user alter column age set default 0;

などをやってしまうとテーブルにロックがかかりシステムがダウンしてしまいます。

カラムの登録＞デフォルト値の設定＞データ更新＞NULLの不許可の設定と段階を踏まないといけないので、手間がかかるところがデメリットです。

しかし、運用を考えるとNULLは撲滅したほうがいいので、工数をかけてもNULLを不許可にすべきです。

 

デフォルト値でon update

と更新される時にupdated_atのデータも変更するようなデフォルト値が設定されている事がありますが、デフォルト値はあくまでNULLを不許可にするための回避策であって機能やロジックをDBに持たせるべきではないです。

なのでトリガーなどと同じくon updateも禁止にすべきです。

テーブル、カラムの命名規則に関してはt_がメインのデータがはいるテーブルでm_がマスターテーブルという命名規則にしている現場が多かったです。

追加で、h_がhistoryの意味のinsertのみのテーブルで、c_がマスターテーブルよりもさらに更新が少ないconfigという意味のテーブルとd_がメインではないサブ機能のデータが入るテーブルというのも加えてもいいかと思います。

ただし、運用をしていく上でm_がほぼt_の機能を果たしてしまうことになったりと変化してしまうのは仕方がないことだと思うので、命名規則もそこまで厳しくする必要もなくてだいたいでいいかと思います。

 

次にどこまで正規化がいいのかを考えていきます。

正規化が好きな人からすればなんでもかんでも正規化したくなる人もいると思います。

例えば、男女の性別のデータを1,2に分けてマスターテーブルを作成したくなる人もいるかと思いますが、性別のケースは単純にM、Fで直接データ登録した方がいい場合もあります。

正規化する要素とマスターテーブルの必要性の要素としては

1. 該当データの文字数（３文字以上かどうか）でインデックスが必要かどうか
2. 該当データのパターンの数（５，６個以上かどうか）
3. 該当データをWEBページに表示するパターン数（多言語やW,Woman,Femaleなど）
4. 該当データの変更がおきる頻度の条件

を加味してマスターテーブルを作成するなり、文字列から数値にするなり判断する必要があると思います。

例を挙げるとユーザーテーブルがあったとして、そのユーザーの性別をMとFと登録すれば問題ないですが、Male,Femaleで登録するとレコード件数が多くなった時にディスク容量を不必要に浪費してしまいます。

それとインデックスを張った時、キャッシュを利用した時などに上限が少ないメモリの消費もしてしまうため、この場合は1,2とかの数値型にすればそれらのリソース消費は抑えられます。

なのでこの場合は①の「該当データの文字数が多くインデックスが必要」に当てはまります。

③に当てはまるケースとしてあるWEBページにMan,Womanの表示が必要となり違うページでは、M,Fの表示が必要、さらに多言語化で英語表記だけでなく日本語も必要となった場合はマスターテーブルを作成して各々の表記をデータとして持っておいたほうが運用がしやすいかと思います。

③の表示するパターンが一つしかない場合でも男女だけでなくLGBTQのさらに細かい所まで考慮しないといけないとなると条件②の該当データのパターンの数が５個以上のケースに当てはまり、カラムのコメントだけでは運用がしづらくなってきます。

それと④の該当データの変更がおきやすいということに該当しそうなので、マスターテーブルを作成した方が管理がしやすくなるという事になります。

 

DBの設計は一度設計したDBを変更する事

は運用上困難なので、新規登録、変更の時はソースコード以上に慎重にチェック、承認のフローを徹底したほうがいいかと思います。

DBのドキュメントについて、設計書をエクセルに落とし込んで、管理したほうがいいという方もいるかもですが、実際その設計書を誰も見ずに放置され更新もされない事ってDB設計書のあるあるです。

ER図に関しても同じように放置される事が多々あって管理工数もかかる事から、そもそも設計書もER図もなしの方がいいかと思います。

その分カラムのコメントにできるだけ細かくどのテーブルとリンクしているかを記載してた方が運用がしやすいかと思います。

JOINの禁止、外部キーの禁止などは暴論だと思う方もいると思いますが、異論・反論もしくは改善点などがあれば是非コメントしてもらえればと思います。

by プログラマーこまつ