エンジニアとしての強み

各カテゴリの詳細を確認できます

データベース設計/SQL開発

効率的で保守性の高いデータベース設計とSQL開発のノウハウについて紹介します。

変更のための正規化

データベース設計における正規化の重要性と実践方法

第一正規化：カラムにリスト(CSV)やネスト構造(JSON)、シリアライズされたオブジェクトを挿入しない

理由：

論理設計（カラムの意味）の変更に対して脆弱となる
- 古いルールで作成されたデータが現在のビジネスロジックで利用できない、または別の意味のデータとなってしまう等
内容に関して条件のあるクエリを実行する場合のパフォーマンスが低下する恐れがある
- 例えばリストの包含関係を条件とする場合、IN句条件よりも効率が悪くなってしまう。（IN句の場合、INDEXを使用した範囲検索が効率的に可能なことが多い）

例外：

論理設計の変更スパンよりもそのデータの生存期間が短い場合
例：ある1回のゲームにおいてのみ使用するゲームの状態等、終了後トレースする可能性のある情報がカラムとして切り出されているのであればさほど問題はない。（ただし、テーブルの行として残すよりも期限付きのインメモリキャッシュ等で管理する方が適切）
カラム定義を厳密に管理しており、尚且つ別テーブルを用意するほど従属する情報がない場合

解決策：

中間テーブル（交叉テーブル）を作成し、テーブルを分割する
オブジェクトストレージを使用し、明示的に保守する

第二/三正規化：PK/非キーの真部分集合に従属するデータを同テーブルのカラムに設定しない

理由：

ある単一のデータの変更時、整合性を持たせるために複数のデータの変更が必要になる可能性があります。

例外：

変更前のデータに即したレコードを残しておきたい場合
例：ログテーブルや集計トラン等
読込の頻度に対して変更頻度が極めて小さい場合
そもそも第二正規化を行うべき理由が弱いことと、テーブルの結合は通常計算に時間を要するため、特にSELECTのパフォーマンス向上が必要な場合は非正規構造で扱った方が良いこともあります。
あるビジネスロジックにおいてリソースの抽出効率を向上する為に必要な場合

解決策：

PKの部分集合がビジネスロジック上、実際にそのリソースを特定するものである場合：
その部分集合をPKとして設定する
それ以外：
部分集合をPKとし、従属要素を切り出した別のテーブルに分割する（通常の正規化方法）

オプティマイザを過信しない

SQLオプティマイザの限界と対策

最適化の失敗

オプティマイザは統計情報とINDEXを元に、結合方法（テーブルの順番、結合方法（FULL SCAN、INDEX RANGE SCAN、HASH JOIN、INDEX FFS））を決定しますが、テーブル数やINDEX数が多い場合や統計情報と実際のデータ分布が大きく異なる場合はうまく機能せず、結合方法や利用INDEXを明示的に指定した方がパフォーマンスが良いことがあります。（無論、データ分布が変わった場合等はヒント句を修正する運用コストはどうしても生じてしまいます。）

SQLの保守性の低下

INDEXの追加、変更、削除を行う場合、その変更による影響調査を行う必要がありますが、一般的にオプティマイザに頼って��る場合、そのテーブルにアクセスするクエリ全てが調査対象となります。

スケーリングのためにメタデータを追加しない

データベースのスケーリング戦略

理由：その為のコードが肥大化し保守が難しくなる為

例：テーブルのレコード数が多くなったため、テーブル名_○○年等のようにテーブルを複数に分割する

多くの場合テーブル名を変数化することは難しいため、データの操作のためのバックエンドロジックをその分だけ開発保守することになります。

解決策:

水平パーティションの作成：年ごとにデータを取得する機会が多い場合、パーティションを設定することで解決できる可能性があります。
ダンプデータとして出力し、過去のデータをテーブルから削除：過去のデータがバックアップ以上の意味を持たないのであれば、いつでも戻すことが可能な形とすることもできます。

例：年次ごとの統計量を記録したいので集計値＿○○年というようなカラムを毎年足すことにする

毎年カラムを追加し、それに応じてアプリケーションロジックを保守開発しなければならなくなります。

解決策:

統計データは別テーブルとする：元テーブルと統計テーブルのレコードの整合性を持たせたい場合は、データ作成について適切なトランザクション管理が必要となります。統計量使用ケースのWhenを整理することでいくらでも遅延させられるので統計テーブルレベルで管理すると良いでしょう。
計上年というメタデータをカラムに追加し、最新年のみカラムに持たせる：過去の統計量は過去にしか変更されえない場合などはこの方法を取ることができます。年を跨いだ統計量等も集計関数で算出できます。

複キーINDEXをできるだけ必要としない論理設計とする

効率的なインデックス設計

理由：INDEX数が増加し、挿入変更削除のパフォーマンスが低下する可能性が高いため

RDBはデータの更新の際、テーブルにあるINDEXの構成を修正します。ドメインと全単射の関係を保つこととトレードオフの関係になることも多いですが、対象を観察し、カラムに落とし込めそうなものがないか考える余地があります。

ハッシュ表の生成を削減する

効率的なクエリ実行計画の設計

GROUP BY による集約やHASH JOINは集約キー、結合キーが同一のものを整理するためにハッシュテーブルを利用します。同じ集約を行ったビューを結合するよりも、同じ集約を行うビューを作成、UNION ALLしてから1度集約を行うことでハッシュ表の作成を最小限とできます。

SQL例①：同じ集約キーで集約したビューをJOINして結果を得るもの

WITH 
  view1 AS (
    SELECT customer_id, SUM(amount) as total_amount
    FROM sales
    WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31'
    GROUP BY customer_id
  ),
  view2 AS (
    SELECT customer_id, SUM(amount) as total_amount
    FROM sales
    WHERE sale_date BETWEEN '2023-02-01' AND '2023-02-28'
    GROUP BY customer_id
  )
SELECT 
  v1.customer_id, 
  v1.total_amount as jan_amount, 
  v2.total_amount as feb_amount
FROM view1 v1
JOIN view2 v2 ON v1.customer_id = v2.customer_id

SQL例②：まず、集約予定のビューを作成しUNION ALLしてから１度で集約を行う

WITH combined_data AS (
  SELECT 
    customer_id, 
    amount,
    'JAN' as month
  FROM sales
  WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31'
  
  UNION ALL
  
  SELECT 
    customer_id, 
    amount,
    'FEB' as month
  FROM sales
  WHERE sale_date BETWEEN '2023-02-01' AND '2023-02-28'
)
SELECT 
  customer_id,
  SUM(CASE WHEN month = 'JAN' THEN amount ELSE 0 END) as jan_amount,
  SUM(CASE WHEN month = 'FEB' THEN amount ELSE 0 END) as feb_amount
FROM combined_data
GROUP BY customer_id

I/Oを削減する

データベースパフォーマンスの最適化

バックエンドロジックにおけるパフォーマンスは、基礎算術計算の総量（/並列度）で評価できるCPU集約性の高いタスクとなりますが、SQLはテーブルレコードの出し入れがありテーブルデータが保存されている補助記憶装置はI/O性能が低いためにI/O集約性の高い処理です。そのため、パフォーマンスを疑う際は無用なテーブルデータの読み出しがないか確認すると功を奏す可能性があります。

解決策：

FROM句に同じテーブルを登場させない
INDEXフルスキャンを活用する
SQLに登場するカラムを全て含むINDEXがある場合、テーブル本体を読み込まずともINDEXだけで事が済む可能性があります。
ループの中でSQLを呼ばない
forでidをループし、そのidに対するSQLを実行した場合idの数だけSQLが実行され、それだけテーブルの読み込みも発生してしまいます。IN句を使用するなどして一つのSQLにした方が良いでしょう。

INDEXが有効なSQLに変形

インデックスを活用するSQL最適化テクニック

オプティマイザがSQLに合わせて結合方法や利用INDEXを調整してくれますが、SQLそのものを組み替えなければ有効に作用しないこともあります。

例：OR句の使用

INDEXはOR句に含まれる条件式に対して利用することはできません
対策：（部分的な）条件式がP or Q であるのだとして P and not Q に対するSELECTとQに対するSELECTのUNION ALL をSELECT する

SQL例①：悪い例

SELECT * FROM customers
WHERE status = 'active' OR last_purchase_date > '2023-01-01'

SQL例②：良い例

SELECT * FROM customers
WHERE status = 'active' AND last_purchase_date <= '2023-01-01'

UNION ALL

SELECT * FROM customers
WHERE last_purchase_date > '2023-01-01'

例：カラムに対する演算の除去

INDEXはあくまで、カラムの値に対して順序を保存しているにすぎません。そのため、SQL内でカラムを元に計算した値についての条件にはINDEXは利用できません。
対策：例えばカラムtrade_timesについて、条件trade_times/100>=0.1 を trade_times>=10とする等、カラムに対して演算を行わないSQLに変換を行います。

SQL例①：悪い例

SELECT * FROM trades
WHERE trade_times / 100 >= 0.1

SQL例②：良い例

SELECT * FROM trades
WHERE trade_times >= 10

設計思想カテゴリ

他のカテゴリを見る