テーブル設計やSQLを学びながら、速いデータベースの作り方について考えてみる

「Software Design 2018年5月号」を読みました。

ソフトウェアデザイン 2018年5月号

ソフトウェアデザイン 2018年5月号

「速いデータベースの作り方」という、データベース特集の会だったのですが、私なりの考えをまとめてみました。この時期は、新人エンジニアの方も多いと思いますので、少しでも参考になる何かがあれば良いかなと思いまして。

それでは、スタートです。

正規化

実務では、第1〜第3正規形がよく利用されます。理想は第3正規形なんですが、正規化をすればするほどテーブル数は増えるので、SQLが長くなったり、テーブル結合が煩雑になるといったデメリットもあります。

第4回 データベースの正規化|OSS-DB入門|OSS-DB道場|受験対策|DBスペシャリストを認定する資格 OSS-DB技術者認定試験

私がオススメするのは、プログラムの定数やコンフィグファイルで賄えるマスタ系のデータは、極力テーブル化しないことです。

例えば、都道府県のデータは、テーブル化するまでもありません。「1 => '北海道', 2 => '青森県'・・・47 => '沖縄県'」と、定数としてプログラム上に記述しておけば良いのです。

このように、変更の確率が低いマスタ系のデータは、極力プログラム上の定数に書くことをオススメします。第3正規形で分離するようなテーブルでは、きっと対象となるようなテーブルも出てくると思います。

SQLとプログラムの使い分け

SQLの使い方を知った新人エンジニアの方の中には、SQLやテーブルを職人芸のように扱って、SQLで何でもやろうと考えている方もいらっしゃるかもしれません。

しかし、個人的にはあまりオススメしません。何故なら、DBサーバよりもプログラムが動くWEBサーバの方がスケール(並列化)しやすいからです。

それに、プログラムは後から処理を変えやすいので、変更に強いという特徴があります。速いデータベースの話をしているのに、プログラムで頑張れというのも変な話ですが、要はプログラムとDBのどちらで頑張るのかという、その見極めが重要なのです。

インデックス

インデックスが使われると、検索が早くなるのは有名かと思うのですが、意外とインデックスを貼ることに対するコストは無視されがちです。

当然ですが、インデックスを使うためには、インデックスの生成コストが代償として必要になります。つまり、検索が早くなるからといって、むやみやたらとインデックスを貼ってはならないのです。

・・・なので、インデックスは検索条件として利用頻度の高いカラムなどに絞り、利用シーンに合わせて設定する必要があります。

なお、気をつけてインデックスを貼るべき第二の理由は、インデックスの生成コストはINSERTやUPDATE文に対して発生するからです。データ参照のSELECT文は、DBのスレーブサーバーなどを立てて、負荷を分散することが比較的やりやすいのですが、INSERTやUPDATE文となりますと、そう簡単な話ではありません。

サーバの種類とDBサーバ超基礎入門 : LINE Corporation ディレクターブログ

データベースを早くするには

データベースを早くするためには、まず大前提としてテーブル設計が最も重要です。その上で、いかにシンプルなSQLで実現できるかが鍵になります。

例えば、正規化も絶対ではありません。極力正規化は行なうべきなのですが、あえて正規化をしないような事例も勿論あります。また、アクセスが集中するようなページでは、そのページ用にカスタマイズしてデータを切り出して最適化した、専用テーブルを作ることもあります。

また、SQLで複雑なことをしそうになったら、怪しい雰囲気が漂い始めたと思ったほうが良いです。相応のアクセス数が予想される、例えばWebAPIのような要件であれば、SQLはシンプルに越したことはありません。

但し、あまりアクセスの集中しない管理画面のような場所でしたら、問題になることはそうありません。複雑なSQLが許容される局面であるかどうかは、常に意識した方が良いと言えるでしょう。

さいごに

DBサーバは、システムの大切なデータが保存されている生命線の場所であることが多いため、大切に扱う必要があります。トラブルが起きると大規模な障害にも発生しやすいので、 DBもテーブルもSQLも、優しく扱ってあげて欲しいです。

ちなみに、私の過去には、商品ページへのアクセスの度にアクセス数を計上するSQLを書いてしまい、あまりのUPDATE文の多さにDBサーバがパンクしたとか。マスターとスレーブを間違えて、誤ってスレーブDBでUPDATE文を流してしまうなどの、苦い経験がいくつかあります。

そういう意味では、私がDBの本当の意味での大切さを知ったのは、上記のようなトラブルをやらかしたからかもしれませんね。

補足:PHPの現場 第14回

DB関連では「PHPの現場」というポッドキャストの、 第14回が非常におすすめです。

DBを軸にして周辺技術を活かす soudai1025(PHPの現場14) - このすみろぐ

プログラムとSQLの責務の切り分けのような話も出てきますので、ぜひ聴いてみてください。

ソフトウェアデザイン 2018年5月号

ソフトウェアデザイン 2018年5月号