takc-techデータベース
作成日時：2025-10-22
更新日時：2026-03-15
私は、業務アプリケーションのパッケージを開発しているバックエンドエンジニアである。
業務アプリケーションにおけるパフォーマンス問題は、ほぼデータベースが起因だと思っている。
故にデータベースの知識を増やして、問題の発生を防げ。
なぜチューニングをするか
ランニングコストが異常に掛かるパッケージを誰が買うというのか。
待てども待てども処理が終わらないパッケージを誰が買うというのか。
新機能追加や不具合改善が遅いパッケージを誰が買うというのか。
ユーザーとベンダー、互いの生産性を向上せよ。
トラブルがあればベンダーは対応せにゃならん→ベロシティの低下。
監視
メモリ、ディスク、CPU、スロークエリ。
コスト
大体の場合においてDBのコストが一番かかる。
気軽に台数を増やせないからスケールアップする。
DBが遅い場合、APの台数を増やさなければならないこともある。
パフォーマンスにおける大原則
「小さくシンプルにする」
そうしておけば、割といろいろな問題は解決する。
- SQLの取得データ量を最少にする
- SQLの発行回数を最少にする
- SQLのトランザクションを最短にする
- SQLは最速にする
- SQLはシンプルにする
- テーブル構造は小さくする
- レコード数は少なくする
- インデックス数は少なくする
- インデックスサイズは小さくする
- ページ数は少なくする
- Disk I/Oは少なくする
例外はもちろんある。
非正規化、カバリングインデックスなど。
要件定義
- データ量
- 増加想定
- アクセスする端末数
- 許容速度
- 帯域/回線速度
- リクエストパターン
- スパイクリクエストの可能性
- 年一の棚卸
- ユーザー数増加
- SNSからの流入
- バッチ処理
- SLA/RTO/RPO
トリアージをする。
データ量とアクセス頻度でランク付け。
そのテーブルに対するSQLを書くときはパフォーマンスに注意する。
クエリが遅い
- Disk I/Oを減らせ
- キャッシュヒット率を上げろ
- SQLのチューニングをしろ
- 実行計画をとれ
- インデックスを使え
事前に生成しておく
遅いクエリーは事前に生成しておく。
ユーザーが少ない夜間などに。
- サマリーテーブル
- マテリアライズドビュー
- 一時テーブル(temporary table)
- ファイル
- etc…
テーブル系はインデックスも使える。
注意すべきは、ディスク容量を食うことと、最新のデータではないこと。
CTE
CTEごとにテストしたほうがいい
ルールベースとコストベース
かつてのオプティマイザーがそうだったように、文章校正のリンターもそんな感じか。
ルールではなく、意図が明確かをチェックする感じで。
シェアード・エブリシング
近年はデータ量が増加しているから、あまり使われない傾向。
インスタンスを増加しても、Disk I/Oがボトルネックになる。
シェアード・ナッシングやリードレプリカなどのほうがパフォーマンスがよいか。
データの一貫性を最重視する場合は分散させないほうがいい。
DB
ALTER文が終わらない可能性を考えろ
VACUUMとかも。
DB
インデックスの見直し
- 最適化されているか
- クエリで何が使われているか
- スロークエリ発生時の実行計画
- 運用で回避できないか
スケールアップ
- コネクションプールの見直し
- EBSやVPCの見直し
readが多いならスケールアップよりスケールアウト？
スペックを下げて台数を増やす
SRPとCUPID特性
「過度な正規化はパフォーマンスの悪化をもたらす」
SRPとCUPID特性の「C」みたい。
CUPID特性はDBにも当てはめられるのでは。
TRIMモデル
自作モデル。クエリーのパフォーマンスを上げるための方法をまとめたもの。
「最少・最少・最短・最速の原則」は、SQLにおける原則である。
この原則はテーブルに対する考え方である。
データベースのパフォーマンスを上げるため、テーブルにおいて下記の要素を最小(Minimize)にする。
- T: テーブル構造
- R: レコード数
- I: インデックス
これらを小さくする目的は、Disk I/Oの削減と共有バッファーにおけるキャッシュヒット率向上である。
データが小さければページ単位のデータ格納量は増加し、ページ数は減少する。
ページ数の減少は目的を達成する。
このモデルはあくまで基本的な方向性であり、厳守すると逆にパフォーマンスの悪化を招きかねない。
実行計画などを取るなど、検証を行うこと。
テーブル構造
テーブル構造が小さければ必然的にページ単位のデータ格納量は増加し、ページ数は減少する。
- 正規化を行う
- 適切な型を設定する
レコード数
少なければ検索や結合が速く終わるし、ページ数は減る。
- 定期的に不要なレコードを削除する
- 将来的に不要ならば物理削除
- 法や契約上消せないならば削除データテーブルに移すかアーカイブする
- パーティショニング
インデックス
インデックスの数とサイズを小さくする。
過剰なインデックスは、挿入/更新/削除時のパフォーマンスを低下させる。
インデックスを作れば、それだけ余計なページが増える。
必要なカラムにのみインデックスを付与する。
- 検索/結合に使用するカラム
- カーディナリティが高いカラム
- INDEX ONLY SCANをしたい場合
- カバリングインデックス
- 外部キー列
- ただし、親テーブルが更新されないならば無くともよい
- 親テーブルが更新される場合、子テーブルのロックが発生しうる
サロゲートキーを採用して、サイズを小さくする選択肢もある。
VARCHARよりINTのほうが小さい。
レコード数が少なければフルスキャンになる可能性が高いので、なくてもいい。
メモリー
ほぼ使われていないという状況を発生させるな。
もったいから。
余っているならば、work_memやshared_bufferに割り当てる。
制約
データの整合性を担保するならつけるべき。
APでもチェックはするが、制約は最後の砦である。
パフォーマンスを重視するなら外すのもよい。
大量データ投入時にいったん外して、投入後に再付与するのもいい。
遅延評価の設定をONにしてもいい。
正規化
重複と操作異常を防ぐ。
テーブルのカラム数が減るから、ページ単位のデータ格納量が上がり、キャッシュヒット率が上がる。
正規化崩し
- キャッシュ
- その時点の情報保持
- 結合速度が遅い場合
圧縮
レスポンスの圧縮機能はあるにはある。
クライアントが対応していなかったりする。
データ量が少なかったり、AP-DBの距離が近かったりすれば、むしろ遅くなる。
トリガー
個人の考えだが、基本的に使うな。
- デバッグが面倒
- パフォーマンスへの影響
- 移植性が無い
- そもそもビジネスロジックを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
-- 従来: 行ごとにスカラーサブクエリが実行される
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
WITHにおいて、複数問い合わせが発生するようなWITHは実体化される。
逆に、1回しか参照されなかったり、副作用が無い場合はインライン展開される。
MATERIALIZEDキーワードはそれを強制的に上書きするキーワード。
MATERIALIZEDは実体を作り、NOT MATERIALIZEDはインライン展開を強制する。
基本的にはMATERIALIZEDをつけず、自動最適化に任せる。
カラムナ
データベースに関して。
カラムナデータストアにおいて単一カラムの検索が高速なのは、下記のためか。
- ページにそのカラムのデータしかないため、キャッシュヒット率が高くディスクI/Oが発生しにくい
- ↑でシーケンシャルアクセスでデータを取得できる
- ランレングス符号化による圧縮も
更新時
基本的にUPDATE文で対象行を更新する際は、その行に対して排他ロック（占有ロック）がかかる。
ロックされていても参照はできる。
更新時は最新データを見る。
空集合
くうしゅうごう。
prepared statement上限
製品によって上限がある。
使わずに手動でエスケープすれば上限は理論上消えるけども。
しかし、そうするとSQLの長さの制限に引っかかりかねない。
1,000件ずつとかで分割して実行したほうが安牌ではある。
MyBatis Generator
接続文字列に参照するスキーマを指定していても、
テーブル定義にスキーマを記載しないと、すべてのスキーマの同名テーブルを参照するっぽい。
テーブル構造を変更して生成しても、変更分が反映されていなかった。
原因は変更分を反映したORMを作成してはいたが、別スキーマの同じテーブルの内容で上書きしていた。
ログに同じファイルが何回も出ていれば、原因はそれ。
build.gradleのmybatisGeneratorの設定に、overwrite=trueを定義しておけば上書きされず、すべてのファイルが生成される。