X(Twitter) Zenn GitHub RSS 共有

データベース

作成日時：2025-10-22
更新日時：2025-10-22

私は、業務アプリケーションのパッケージを開発しているバックエンドエンジニアである。

業務アプリケーションにおけるパフォーマンス問題は、ほぼデータベースが起因だと思っている。
故にデータベースの知識を増やして、問題の発生を防げ。

なぜチューニングをするか

ランニングコストが異常に掛かるパッケージを誰が買うというのか。
待てども待てども処理が終わらないパッケージを誰が買うというのか。
新機能追加や不具合改善が遅いパッケージを誰が買うというのか。

ユーザーとベンダー、互いの生産性を向上せよ。
トラブルがあればベンダーは対応せにゃならん→ベロシティの低下。

監視

メモリ、ディスク、CPU、スロークエリ。

コスト

大体の場合においてDBのコストが一番かかる。
気軽に台数を増やせないからスケールアップする。
DBが遅い場合、APの台数を増やさなければならないこともある。

パフォーマンスにおける大原則

「小さくシンプルにする」

そうしておけば、割といろいろな問題は解決する。

例外はもちろんある。
非正規化、カバリングインデックスなど。

要件定義

トリアージをする。
データ量とアクセス頻度でランク付け。
そのテーブルに対するSQLを書くときはパフォーマンスに注意する。

クエリが遅い

事前に生成しておく

遅いクエリーは事前に生成しておく。
ユーザーが少ない夜間などに。

テーブル系はインデックスも使える。
注意すべきは、ディスク容量を食うことと、最新のデータではないこと。

CTE

CTEごとにテストしたほうがいい

ルールベースとコストベース

かつてのオプティマイザーがそうだったように、文章校正のリンターもそんな感じか。
ルールではなく、意図が明確かをチェックする感じで。

シェアード・エブリシング

近年はデータ量が増加しているから、あまり使われない傾向。
インスタンスを増加しても、Disk I/Oがボトルネックになる。

シェアード・ナッシングやリードレプリカなどのほうがパフォーマンスがよいか。
データの一貫性を最重視する場合は分散させないほうがいい。

DB

ALTER文が終わらない可能性を考えろ
VACUUMとかも。

DB

インデックスの見直し

スケールアップ

readが多いならスケールアップよりスケールアウト？
スペックを下げて台数を増やす

SRPとCUPID特性

「過度な正規化はパフォーマンスの悪化をもたらす」
SRPとCUPID特性の「C」みたい。

CUPID特性はDBにも当てはめられるのでは。

TRIMモデル

自作モデル。クエリーのパフォーマンスを上げるための方法をまとめたもの。

「最少・最少・最短・最速の原則」は、SQLにおける原則である。
この原則はテーブルに対する考え方である。

データベースのパフォーマンスを上げるため、テーブルにおいて下記の要素を最小(Minimize)にする。

これらを小さくする目的は、Disk I/Oの削減と共有バッファーにおけるキャッシュヒット率向上である。
データが小さければページ単位のデータ格納量は増加し、ページ数は減少する。
ページ数の減少は目的を達成する。

このモデルはあくまで基本的な方向性であり、厳守すると逆にパフォーマンスの悪化を招きかねない。
実行計画などを取るなど、検証を行うこと。

テーブル構造

テーブル構造が小さければ必然的にページ単位のデータ格納量は増加し、ページ数は減少する。

レコード数

少なければ検索や結合が速く終わるし、ページ数は減る。

インデックス

インデックスの数とサイズを小さくする。

過剰なインデックスは、挿入/更新/削除時のパフォーマンスを低下させる。
インデックスを作れば、それだけ余計なページが増える。

必要なカラムにのみインデックスを付与する。

サロゲートキーを採用して、サイズを小さくする選択肢もある。
VARCHARよりINTのほうが小さい。

レコード数が少なければフルスキャンになる可能性が高いので、なくてもいい。

メモリー

ほぼ使われていないという状況を発生させるな。
もったいから。
余っているならば、work_memやshared_bufferに割り当てる。

制約

データの整合性を担保するならつけるべき。
APでもチェックはするが、制約は最後の砦である。

パフォーマンスを重視するなら外すのもよい。
大量データ投入時にいったん外して、投入後に再付与するのもいい。
遅延評価の設定をONにしてもいい。

正規化

重複と操作異常を防ぐ。
テーブルのカラム数が減るから、ページ単位のデータ格納量が上がり、キャッシュヒット率が上がる。

正規化崩し

圧縮

レスポンスの圧縮機能はあるにはある。
クライアントが対応していなかったりする。
データ量が少なかったり、AP-DBの距離が近かったりすれば、むしろ遅くなる。

トリガー

個人の考えだが、基本的に使うな。

データ移行などにおいて使うくらいか。

インデックス

カーディナリティが低ければ、フルスキャンになりうる。
カーディナリティが高いカラムにつけろ。
行を十分絞れるものに。

インデックスを貼るべきか

業務アプリケーション的には、貼るべき。
だいたい検索がメインだし、速度も遅いから。
挿入/更新/削除はあまり発生しない。

夜間バッチで大量投入がありそうだが、その時はいったんインデックスを消せばいい。

要はトレードオフ。

インビジブルインデックス

オプティマイザーからインデックスを見えなくする。
主に検証のために使う。
実際に消すわけではないので、すぐ戻せる。

不可視にしてもインデックスが消えるわけではない。
データ更新時にインデックスの更新は行われる。

OR

UNIONにすればインデックスを使える。
最近はORをそのまま使ってもUNIONに変換してくれたりする。

型

なるべくVARCHARを使うな。

VIEW

メソッドの抽出ともいえる。

TRUNCATE

トリガーは動かない。

実行計画

本番と同じデータ量で取れ

型

smallintでもアラインメントで4バイト使うこともある。

空集合とのデカルト積

R×φはφになる。
A CROSS JOIN Bで片方が空集合なら空集合になる。

Aに対してCTEで取得したスカラー値(B)を無条件で結合したいためにデカルト積を使用した際、
CTEの結果が空集合ならば、空集合が返却される。
取りたかった結果は、A LEFT OUTER JOIN BでBが無い場合はNULLのような形。
この場合はサブクエリで良い。

WITH tmp(col) AS (
    -- φまたはスカラー値になるような検索
    SELECT col FROM B
)
SELECT 
     *
    ,(SELECT col FROM tmp)
FROM A
-- CROSS JOIN tmp はtmpが空集合たと結果すべてが空集合になる
-- LEFT OUTER JOIN B ON true でも取れるがアンチパターン。意図が分かりづらいし、最適化を妨げる可能性がある。

LATERAL JOIN

7.2. テーブル式

-- 従来: 行ごとにスカラーサブクエリが実行される
SELECT 
    A.*,
    (SELECT MAX(value) FROM B WHERE B.a_id = A.id) as max_val,
    (SELECT MIN(value) FROM B WHERE B.a_id = A.id) as min_val
FROM A

-- LATERAL: 1回のスキャンで複数の値を取得
SELECT A.*, stats.*
FROM A
LEFT JOIN LATERAL (
    SELECT 
        MAX(value) as max_val,
        MIN(value) as min_val
    FROM B
    WHERE B.a_id = A.id
) stats ON true

別表の内容を参照できる。
通常のスカラーサブクエリよりはパフォーマンスがいい。

PostgreSQLとMySQL(キーワード省略可)にある。
他の製品にもキーワードは違うがある。

ウィンドウ関数でもできるが、ウィンドウ関数はメモリ使用量が大きい。
LATERALはCPUを使う。
まずLATERALで書いて、遅ければEXPLAINで確認→ウィンドウ関数に変更検討。

-- 1
WITH tmp(...) AS (
    SELECT id, ... FROM B WHERE ...
)
SELECT 
     id
    ,...
FROM A
INNER JOIN A.id = tmp.id

-- 2
SELECT 
     id
    ,...
FROM A
LEFT JOIN LATERAL (
    SELECT id, ... FROM B WHERE ...
) stats ON true

例えば上記のSQLにおいて、
1は表Aに該当するidが存在するかを考慮せず、表全体を対象に集計してからAと結合する可能性があるが、
2は確実にAに存在するidを元にBを集計している？

でも結局オプティマイザー次第なので、同じ実行計画になったりもする。
ただしLATERALはインデックスを使える

セミジョイン

片方の要素は取得せず、条件として見る。

SELECT A.*, ...
FROM A
WHERE EXISTS (SELECT 1 FROM B WHERE B.b_id = A.id AND ...)

WITH x AS MATERIALIZED

7.8. WITH問い合わせ（共通テーブル式）

WITHにおいて、複数問い合わせが発生するようなWITHは実体化される。
逆に、1回しか参照されなかったり、副作用が無い場合はインライン展開される。

MATERIALIZEDキーワードはそれを強制的に上書きするキーワード。
MATERIALIZEDは実体を作り、NOT MATERIALIZEDはインライン展開を強制する。

基本的にはMATERIALIZEDをつけず、自動最適化に任せる。

カラムナ

データベースに関して。
カラムナデータストアにおいて単一カラムの検索が高速なのは、下記のためか。

更新時

基本的にUPDATE文で対象行を更新する際は、その行に対して排他ロック（占有ロック）がかかる。

ロックされていても参照はできる。
更新時は最新データを見る。

空集合

くうしゅうごう。