takc-techRelational Database
作成日時：2024-08-01以前
更新日時：2024-11-16
最少・最少・最速であれ
- SQLの発行回数は最少
- 取得するデータ量は最少。DBで集約したりしろ
- クエリは最速
システムが遅いのは外部APIの呼び出し。
つまりDBの所為。
なのでパフォーマンスを改善したければDBを何とかしろ。
アクセスプランを取れ。
非機能要件にはねる。
色々な製品
| 製品 | メモ |
|---|---|
| AlloyDB | HTAP |
| Azure Cosmos DB | 分散 |
| 〃 for PostgreSQL | Citus(サイタス)を使った分散 |
| Aurora | MySQL/PostgreSQLと互換性がある性能良い奴 |
| 〃 Serverless | サーバーレス |
| Spanner | NewSQL |
| TiDB | NewSQL |
| CockroachDB | NewSQL |
NULL
NULLはカス。
全カラムにNOT NULLつけろ。
集約関数系とか0件時とかの扱いが面倒くさい。
COALESCEを使え。
不要なフラグ
やめろ。
2値で表現できないからって、フラグをたくさん作るな。
区分作って1カラムで表現しろ。
コーディング原則適用思想
コーディング原則に基づいた設計・製造をしろ。
- DRY原則
- (DML)CTE、VIEW
- (DDL)正規化、VIEW、算出可能カラムの排除
- KISS原則
- (DML)CTE、VIEW
- (DDL)正規化、VIEW
- YAGNI原則
- (DML)必要のないカラムを持ってくるな
- (DDL)計算で算出できるような情報はカラムとして持たない
- SRP原則
- (DDL)1カラムに複数の意味を持たせない
- SLAP原則
- 段落を揃えろ
- PIE原則
- 読みやすさを重視して書く
- 名前重要
- テーブル名やカラム名
- 小は美なり
- 発行回数
- 駆動表
- 取得するデータ量
SQLの大文字小文字
全部小文字で書いてもパフォーマンスはそこまで向上しないらしい。
可読性とルールを優先すべき。
クエリキャッシュを利用できるようにルールを徹底。
SQLのクエリキャッシュ
SQLそのものは大文字小文字の区別をしないがキャッシュは区別する
ルールを決めて書き方を統一。
クエリキャッシュは製品によってあったりなかったり。
ページング
フルスキャンになりがち。
データ件数が多いと、最初のページと最後のページで検索速度が大きく異なる。
検索するときは日付を必須にし、かつ「検索可能なのは1年の範囲だけ」とかにする。
要するに取り扱うデータ量を小さくする + インデックスを使用して走査しろ。
もしくは、検索結果の最後のid(もしくはソート対象)を付与しろ。
検索条件にそれ以降のデータを取るようにすればオフセットいらない。
異なった型でのWHERE
やめろ。インデックスが使われない。
DBの継承のやつ
実装方法は下記。
- 元のテーブルに両方の属性を持たせる
- 別のテーブルに分ける
- 共通属性を持つテーブルと特化した属性を持つテーブルを作成
VALUESの利便性
SELECT * FROM (VALUES(1,2),(4,5)) AS a;
WITH A AS (VALUES(A),(B))
日付型
データ格納時: 現在のシステムタイムゾーンからUTCに変換して保存される。
データ取得時: 保存されているUTCの値が現在のシステムタイムゾーンに変換されて返される。
MySQLはUTCで保持。
postgreSQLも。DB側でタイムゾーンの保持のありなしを選べる。
Java側の日付はoffsetDateTime。
NOW()は使うな。一括登録でずれる可能性があるし、サーバーの時刻に依存する。
postgreSQL WITH TIME ZONE + Java OffsetDateTime
H2
Java上で動くDB。
色々なRDBMSのSQLと互換性があるらしい。
その為、単体テストでのモックにいい。
レプリケーション
基本は1台か。
- master-slaveレプリケーション
- master(メイン)とslave(マスタの更新を随時反映)で構成
- masterが死んだらslaveにフェールオーバ
- slaveはホットスタンバイ。基本アクセス不可。
- master-masterレプリケーション
- masterが複数台でどれにでも更新できる。
- master間で同期してくれる。
- AWSはリードレプリカしかサポートしていない
- リードレプリカ
- 参照用のレプリケーション
- 参照するだけならそっちを見る
- リードレプリカを複数台にするならDNSラウンドロビンとか
- Springだとアノテーション使って切り替えられる。
- 参照系のAPIならレプリカアノテーションをつけたり
データ永続化の基本
- IOを減らす為、基本的にはメモリ上でデータをいじる。(ダーティページ)
- チェックポイントでディスクに保存。
- WALはコミットされたら即時。シーケンシャルなので早い。
PostgreSQL
- VACUUM：不要領域の回収。再利用可能にする
- VACUUM FULL：↑ + 不要領域を詰める。⇒ファイルサイズを減らせる。
- REINDEX：インデックス再編成
- CLUSTER：指定したインデックス順に物理データを再作成。
- EXPLAIN：実行計画
- EXPLAIN ANALYZE：実際にSQLを流して実行計画を見る
- ANALYZE：統計情報の更新
テンプレート
PostgreSQLのやつ。
新規DB作成時のもとになる。
InnoDB
基本的にPKのクラスタインデックスでデータを保持している。
リーフノードに実データを持っている。
PK以外のインデックスはセカンダリインデックス。
リーフに主キーの値が格納されている。
しかしセカンダリ⇒プライマリの2段階アクセスなのでパフォーマンスが問題。
つまりインデックスを2回見ている。
この主キー検索を高速化するためにアダプティブハッシュインデックスがある。
イメージとしてはkeyがセカンダリインデックスの検索値のハッシュ、valueがレコードへのポインタ。
ハッシュなので等価検索のときにのみ使われる。
バッファプール上に作られる。
ギャップロックとネクストキーロック
InnoDBのやつ。
格納されているレコードはx = {10,20,30}の3件とする。
SELECT * FROM tbl WHERE x = 20 FOR UPDATE;
= で検索して、存在する場合はレコードロック。
その行のみロックする。
上記の場合は20のレコードのみ。
SELECT * FROM tbl WHERE x = 19 FOR UPDATE;
= で検索して、存在しない場合はギャップロック。
実レコードの間をロックする。
上記の場合は11-19をロックする。
SELECT * FROM tbl WHERE x BETWEEN 18 AND 21 FOR UPDATE;
範囲で検索した場合、実レコード間のギャップと次の実データをロックする。
上記の場合は11-30をロックする。
ギャップロック同士は競合しない。
ギャップロックとネクストキーロックは競合する。
ロックに対して更新等は出来ない。
これにより、MySQLはREPEATABLE READでもファントムリードは発生しない。(関係ないけどPostgreSQLも)
検索した範囲内におけるデータ操作を禁止しているから。
ネクストキーロックはギャップ領域の拡大を防ぐためという解釈。
実存するインデックスデータでロックを管理しているのだから、データを消されるとギャップを管理できなくなる。
REPEATABLE READ以上かつFOR UPDATEなどのクエリで初めてこのロックが発生する。
MVCC⇒完全にファントムリードを防ぐ
ネクストキーロック⇒ロックのついでにファントムリードも実質防ぐ。
まあ、FOR UPDATEには気をつけとけ。ネクストキーロックが発生しうる。
GROUP BY (MySQL)
基本、一時ファイルが作成される。
関連：GROUP BYにおけるインデックススキャン
ロストアップデート
- (TX1)対象行ロック
- (TX1)バージョンカラムを更新
- (TX2)対象行のバージョンカラムの値が、画面で保持していたバージョンと異なる⇒上書きされているのでエラーとする。
悲観的ロックで、既にロックされていたら即時APでエラー返す。
分離性や楽観的ロックでは発生する。
後者は分離性上げてもMVCCで。
KISS原則
DBに限らんが、シンプルがベスト。
NoSQLは必要あるか。RDBで良いのでは。
マテビューは必要あるか。Viewで良いのでは。
マテビュー
- 更新可能なマテビューも製品によってはある
- 更新は手動更新が定期的な自動更新
- そのため情報はリアルタイムに反映されない。
- 参照系の操作が多い場合、更新時に手動更新すれば全体のパフォーマンスを下げず整合性が取れそう
NoSQL
- DynamoDB：key-value
- MongoDB：ドキュメント。JSONとかそのままぶち込む。
GROUP_CONCAT関数
- グループに紐づく対象項目を列挙する
SELECT id, GROUP_CONCAT(col, '+') FROM tbl GROUP BY id;
=> 1, col1+col2+col3
再帰With
基本遅いから使うな。
駆動表が小さい時だけ使え。
2PL
- よく使うのが保守的な2PL
- 最初に全部決められた順でロックする
- 厳格な2PLはデッドロックになる
イベントソーシングとマテビュー
イベントをDBに流し込んで、夜間バッチで参照可能な形にマテビューを構築するとか。
普通のビューでも良さそうだが、マテビューはリフレッシュコマンド打つだけだから楽そう。
日時を表すカラム
XXX_onは日付、XXX_atは日時。
SQLの評価順序
- FROM
- JOIN
- WHERE
- GROUP BY
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- LIMIT/OFFSET
但し、あくまで一般的な評価順序であって、RDBMSによっては論理的に異なる可能性がある。
SELECT * FROM
A JOIN B ON A.a = B.a
WHERE A.xxx = 'value'
-- ↓内部的に書き替えて実行
SELECT * FROM
(SELECT * FROM A WHERE A.xxx = 'value') A
JOIN B ON A.a = B.a
例えば上記のSQLでは、WHEREの適用場所を変えることで駆動表が小さくなり、結合のコストが減る。
実行計画やログを確認することで、最終的にどのようなクエリが投げられたかを確認できる。
述語のプッシュダウン
このように、WHERE句などの述語条件を、データの取得元であるテーブルやインデックスといったより低いレベルに移動させる処理の事を「述語のプッシュダウン」と言う。
大きいデータの除外
TEXTやBLOBは別テーブルに切り出すのもあり。
SELECTした場合、使う使わないにかかわらず、関係なくロードされる。
行指向であるため、関係のないデータも読み込むから。
結合を頻繁にするならそうでもない。
テーブル設計
下記がテーブルの対象となる。
- ヒト・モノ・コト
- 5W1H
- イベントとリソースに分ける
- イベントは時刻を持つ
- 前者は動詞、後者は名詞
トランとマスタ
不要。
いちいちt_とか名称にXXマスタとか不要。
設計
- パフォーマンスに直結する
- 変更が困難。思わぬところに影響が出る。
だからDB設計はちゃんとやらなければならない。
マルチテナントにおけるRLS
HWM(High Water Mark)
- セグメントの使用済み領域の終端。未使用領域との境界。
- データ追加(行追加やインデックス更新)したりすると増える。
- DELETE等でレコードが削除されても、基本減らない。
- HWMと実際の使用領域に乖離があると、断片化している可能性がある。
- セグメント単位でメモリにロードするならば、不要なスキャンが発生している。
DBのデータ構造(要検証)
テーブルスペース -> セグメント -> エクステント -> ページ -> レコード。
1. テーブルスペース (Tablespace)
- データベース内の最大の論理的な記憶単位です。
- 1つ以上の物理データファイルで構成されます。
- 複数のセグメントを含むことができます。
- データベース管理者が管理し、データの物理的な配置を制御するのに役立ちます。
2. セグメント (Segment)
- 特定のデータベースオブジェクト（テーブル、インデックスなど）に割り当てられた記憶領域です。
- 1つ以上のエクステントで構成されます。
- 例：テーブルセグメント、インデックスセグメント、一時セグメントなど。
3. エクステント (Extent)
- 連続した複数のデータブロック（ページ）の集まりです。
- セグメントに割り当てられる最小単位です。
- データベースの拡張時、セグメントに追加される単位となります。
4. ページ (Page) またはブロック (Block)
- データベースのI/O操作の最小単位です。
- 固定サイズで、通常は4KBから16KBの範囲です（データベース製品により異なる）。
- 複数のレコードを含むことができます。
- データベースがメモリとディスク間でデータを転送する際の単位となります。
5. レコード (Record)
- データベース内の個々のデータエントリです。
- テーブルの1行に相当します。
- フィールド（列）の集合で構成されます。
- データベースの最小の論理単位です。
駆動表は小さく
| 外部表サイズ | 内部表サイズ | 外部表スキャン | 内部表スキャン | 総スキャン数 |
|---|---|---|---|---|
| 100件 | 10件 | 1 * 100 = 100 | 100 * 10 = 1,000 | 1,100 |
| 10件 | 100件 | 1 * 10 = 10 | 10 * 100 = 1,000 | 1,010 |
外部表のスキャン数が減る感じ。
ORMにおけるクラス分割
下記の感じ。
単純なテーブル操作は”テーブル名 + Dao”クラス。
複数のテーブルが絡んだり、ドメインに紐づく者は”ドメイン名 + Dao”クラス。
- リポジトリパターンとの組み合わせ
- クリーンアーキテクチャへの適合
- マイクロサービスでの境界の明確化
カラム数が大きすぎると、INSERT時にエラーが出る
row is too big: size XXXXX, maximum size 8160
Postgresのカラム数と1レコードサイズの上限に関するメモ #postgres - Qiita
整理するとPostgreSQLでは、
- 最大1600個のカラムを持つテーブルを作成できる。
- しかし、そのテーブルにINSERTやUPDATEをすると下記のエラーが発生しうる。
- 「row is too big: size XXXXX, maximum size 8160」
- 1行のデータサイズが8,160を超える場合に発生する。
- doubleは8バイトなので、doubleのカラムが1020個以上の場合、エラーとなる。
- ↑インデックスサイズも加算されうるので、厳密にはこの限りではない。
- 対策としては、投入するデータのサイズが8,160を超えないように分割して登録する。
- 例えば、INSERT時は200個のカラムに投入、残りはUPDATEで投入など。
- そもそもそんなテーブル作るな。
TEXT
- TEXT型の大きいデータは、TOASTメカニズムによって別テーブルに保存される。
- データサイズが2KB（2048バイト）を超える場合
- 圧縮可能な場合は、まず圧縮を試みる
- それでも大きい場合は分割して格納
文字長さに応じて、格納するデータサイズが異なる。
大小の閾値は2KB。
大きいTEXT = TOASTポインタ(18Byte) + 文字列長(4Byte)
小さいTEXT = 実際の文字列サイス + 文字列長(4Byte)
VACUUM FULL後の大量データ投入処理が遅い
概要
大量データの投入処理において、VACUUM前は数秒で終わったが、
VACUUM FULL後は数分かかった。
原因
テーブルの物理サイズが最小化される。
余分に確保している領域も小さい。
その状態で大量のデータを登録すると、新しく記憶領域を確保しなければならない。
これでパフォーマンス低下。
対応
事前に領域を確保しておく。
ダミーデータ投入とかで。
INSERT INTO your_table
SELECT /* ダミーデータ */
FROM generate_series(1, 400000);
DELETE FROM your_table;
投入の過程で記憶領域を確保する。
その後に削除しても、確保した記憶領域はそのまま。
メモ
サービスIN直後の大量データ投入とかまずそう。
初期エクステントサイズが1ページとかの場合とか。
fillfactor
PostgreSQLはデフォルトで100。
100以下にした場合で、更新した場合、そのページ内に追記される。
すなわち新しくページを作らなくてよいので、多少パフォーマンスがよくなる。
リンク
- DELETE文とTRUNCATE文の違い〜HWMの引き下げ - 日常メモ
- データファイルのハイウォーター・マーク(最高水位標/High-water Mark/HWM)を確認する | SSJ Tech Lab
- MySQL InnoDBの領域管理 #MySQL - Qiita
その他
NoSQLにおけるスキーマ定義の重要性
redisかます。
- 実行計画を取る時は実運用上、発生しうるデータで行う
- EXPLAIN ANALYZE使え
- ページサイズとバッファ
- MySQLはマテビューない
- MySQLはシングルプロセスマルチスレッド、PostgreSQLはマルチプロセス
- 一時テーブル(temporary table)
- セッション中有効
- SQL ServerのCDC(Change Data Capture)
- データの変更内容を取得できる
FDW
外部データとの連携 ～FDWで様々なデータソースとつなぐ～｜PostgreSQLインサイド : 富士通
boolとsmallint
t / fの2値よりは、なるべく多い方がいいか。
1Byte増えるけどsmallintで。
1ページ(8KB)に収まるなら
行サイズ = タプルヘッダ(23バイト)
+ NULLビットマップ(必要な場合)
+ データ(アライメント込み)
+ パディング(8バイト境界に合わせる)