X(Twitter) Zenn GitHub RSS 共有

Relational Database

作成日時：2024-08-01以前
更新日時：2025-01-18

色々な製品

製品メモ
AlloyDBHTAP
Azure Cosmos DB分散
〃 for PostgreSQLCitus(サイタス)を使った分散
AuroraMySQL/PostgreSQLと互換性がある性能良い奴
〃 Serverlessサーバーレス
SpannerNewSQL
TiDBNewSQL
CockroachDBNewSQL

NULL

NULLは撲滅する。
全カラムにNOT NULLつけろ。

集約関数系とか0件時とかの扱いが面倒くさい。
COALESCEを使え。

「どうしてもNULLになるからデフォルト値を入れよう」
っていうのもやめろ。

不要なフラグ

やめろ。
2値で表現できないからって、フラグをたくさん作るな。
区分作って1カラムで表現しろ。

コーディング原則適用思想

コーディング原則に基づいた設計・製造をしろ。

SQLの大文字小文字

全部小文字で書いてもパフォーマンスはそこまで向上しないらしい。
可読性とルールを優先すべき。
クエリキャッシュを利用できるようにルールを徹底。

SQLのクエリキャッシュ

SQLそのものは大文字小文字の区別をしないがキャッシュは区別する
ルールを決めて書き方を統一。

クエリキャッシュは製品によってあったりなかったり。

ページング

フルスキャンになりがち。
データ件数が多いと、最初のページと最後のページで検索速度が大きく異なる。

検索するときは日付を必須にし、かつ「検索可能なのは1年の範囲だけ」とかにする。
要するに取り扱うデータ量を小さくする + インデックスを使用して走査しろ。

もしくは、検索結果の最後のid(もしくはソート対象)を付与しろ。
検索条件にそれ以降のデータを取るようにすればオフセットいらない。

異なった型でのWHERE

やめろ。インデックスが使われない。

DBの継承のやつ

実装方法は下記の3パターン。

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台か。

データ永続化の基本

ロストアップデート

  1. (TX1)対象行ロック
  2. (TX1)バージョンカラムを更新
  3. (TX2)対象行のバージョンカラムの値が、画面で保持していたバージョンと異なる⇒上書きされているのでエラーとする。

悲観的ロックで、既にロックされていたら即時APでエラー返す。
分離性や楽観的ロックでは発生する。
後者は分離性上げてもMVCCで。

ABA問題 - Wikipedia

KISS原則

DBに限らんが、シンプルがベスト。
NoSQLは必要あるか。RDBで良いのでは。
マテビューは必要あるか。Viewで良いのでは。

マテビュー

NoSQL

GROUP_CONCAT関数

SELECT id, GROUP_CONCAT(col, '+') FROM tbl GROUP BY id;
=> 1, col1+col2+col3

再帰With

基本遅いから使うな。
駆動表が小さい時だけ使え。

2PL

ツーフェーズロック - Wikipedia

イベントソーシングとマテビュー

イベントをDBに流し込んで、夜間バッチで参照可能な形にマテビューを構築するとか。
普通のビューでも良さそうだが、マテビューはリフレッシュコマンド打つだけだから楽そう。

日時を表すカラム

XXX_onは日付、XXX_atは日時。

大きいデータの除外

TEXTやBLOBは別テーブルに切り出すのもあり。
SELECTした場合、使う使わないにかかわらず、関係なくロードされる。
行指向であるため、関係のないデータも読み込むから。

結合を頻繁にするならそうでもない。

テーブル設計

下記がテーブルの対象となる。

トランとマスタ

不要。
いちいちt_とか名称にXXマスタとか不要。

設計

だからDB設計はちゃんとやらなければならない。

HWM(High Water Mark)

リンク

DBのデータ構造(要検証)

テーブルスペース -> セグメント -> エクステント -> ページ -> レコード。

1. テーブルスペース (Tablespace)

2. セグメント (Segment)

3. エクステント (Extent)

4. ページ (Page) またはブロック (Block)

5. レコード (Record)

ORMにおけるクラス分割

下記の感じ。

単純なテーブル操作は”テーブル名 + Dao”クラス。
複数のテーブルが絡んだり、ドメインに紐づく者は”ドメイン名 + Dao”クラス。

TEXT

TOAST

文字長さに応じて、格納するデータサイズが異なる。
大小の閾値は2KB。

大きいTEXT = TOASTポインタ(18Byte) + 文字列長(4Byte)
小さいTEXT = 実際の文字列サイス + 文字列長(4Byte)

fillfactor

PostgreSQLはデフォルトで100。
100以下にした場合で、更新した場合、そのページ内に追記される。
すなわち新しくページを作らなくてよいので、多少パフォーマンスがよくなる。

boolとsmallint

t / fの2値よりは、なるべく多い方がいいか。
1Byte増えるけどsmallintで。
1ページ(8KB)に収まるなら

行サイズ = タプルヘッダ(23バイト)
+ NULLビットマップ(必要な場合)
+ データ(アライメント込み)
+ パディング(8バイト境界に合わせる)

タイムアウト

postgresql
デフォルトは永久にロック解除を待つ。

-- セッションレベルでのタイムアウト設定 (秒単位)
SET statement_timeout = '30s';

-- データベースレベルでのタイムアウト設定
ALTER DATABASE your_database SET statement_timeout = '30s';

-- サーバー全体でのタイムアウト設定
ALTER SYSTEM SET statement_timeout = '30s';

ディスク容量不足でロックが解除されない

発生しうる。

  1. DBにロック。暗黙的でも明示的でも。
  2. 何らかのファイルを作成。
  3. しかし、ディスク容量がいっぱいなので作成不可
  4. この時に、1のロックがずっと解除されない可能性あり

対策

集約関数

グループ内の全てのレコードが条件を満たすかの判定

R(id, sub_id, created_at)
主キーは(id, sub)

上記のテーブルから、id単位でcreated_atが特定の時間以前であるレコードを削除。
例えば、下記のレコードがある。

1,1,12/12
1,2,12/11
2,1,12/11
2,2,12/11

12/11以前を条件とした場合、idが1のレコードは消えず、2のレコードは消える。

DELETE FROM R
WHERE id IN (
    SELECT id
    FROM R
    GROUP BY id
    HAVING MAX(created_at) <= NOW()
);

DBにまつわるAIとエンジニア

今後、SQLはAIを使った方が正しいクエリを出せると思う。
そうなった場合、エンジニアはAIに投げる正しい命題を作る能力が重宝されると思うがどうか。

構造

すごい大雑把な比較

MySQL:シンプル、速い
PostgreSQL:高機能、複雑なクエリ◯、ちょっと遅い、リソースを食う

小規模システムならMySQLのほうがいいのか？
RDSのインスタンスをスケールダウン出来るんじゃない？

後はクラスタと追記型アーキテクチャとか。

階層型DB

金融系とか。

大量データを扱う

統計情報(PostgreSQL)

下記を参照。

更新/チューニング

デフォルトで自動更新はされる。(autovacuum_enabled)
細かくチューニングしたい場合は下記。
autovacuum_max_workerとかも。足りないと自動vacuumが遅れるかもしれない。
20.10. 自動Vacuum作業
25.1. 定常的なバキューム作業

手動も可能。
コマンド例は下記。

-- PostgreSQL
-- テーブル全体の統計情報を更新
ANALYZE table_name;

-- 特定のカラムの統計情報を更新
ANALYZE table_name(column1, column2);

-- データベース全体の統計情報を更新
ANALYZE VERBOSE;

大量データ投入時とかに手動実行する。
統計情報が古いと最適なクエリが発行されない。

凍結(固定化)

PostgreSQLは完全な凍結がないっぽい。
pg_dbms_statsを使えばいいけど。

autovacuum_naptimeのデフォルトが1分ということは、
大量にデータを投入して1分以内に検索を行うと、古い統計情報に基づいたクエリが発行される。
それでCPUが100%になったら、ANALYZEはなかなか起動or完了しないかも。

VACUUMに仕様するメモリや、ワーカーを増やす。

ALTER TABLE t SET (autovacuum_enabled = false)

で自動VACUUMを止められるらしいが、手動ANALYZEや再起動で更新されるらしい。

そもそもVACUUM(+ANALYZE)を停止するのは推奨されていない。
クエリの安定化には、デューン/砂の惑星ダミーデータ投入+自動VACUUMのチューニングが良いのでは。もしくはヒントを使う。

PostgreSQLの実行計画の推定行数と実行数の乖離改善の考え方

パラレルスキャンのチューニング

parallel_setup_costとparallel_tuple_costは小さくすれば、パラレルになりやすいらしい。
下記のページでparallelがついているものを調整する。

20.7. 問い合わせ計画

高速なクエリより安定したクエリ

データ件数の変動で急激に遅くなるよりかは、どんな件数でも安定した実行計画が出るようなSQLがいいかもしれない。

パーティショニング

パーティショニングの概要｜PostgreSQLインサイド : 富士通

シャーディング

パーティショニングは同一DBのインスタンス内、シャーディングは複数DBのインスタンスにデータを分散。
という認識でいる。

JSON

挿入のJSON、検索のJSONB

論理学

x∈y：xはyに属する。
x⊃y：xならばy。
x⊆y,x⊂y：xはyの部分集合。
∀xf(x)：全称量化子：集団の全要素がf(x)を満たす。
∃xf(x)：存在量化子：集団の中にf(x)を満たす要素が存在する。

カラム追加よりテーブル追加を

SAVEPOINT

部分的なロールバックが可能。

データの一貫性

アプリケーションロジックが正しいことを前提とし、パフォーマンスを無視した場合、
楽観的同時実行制御と悲観的同時実行制御を組み合わせることで、理論上はデータの一貫性を常に保つことができる。

RDBと関係モデル

名称の対応。

タプル、組、ロー：行
アトリビュート、属性、カラム：列：
定義域(ドメイン)：属性の取りうる値のこと
次数：属性の数
基数：タプルの数

REPEATABLE READでファントムリードが発生しない

だいたいMVCCのおかげ。
MySQLとPostgreSQLにおいて、REPEATABLE READでファントムリードが発生しないのは、スナップショットを見ているため。
PostgreSQLは追記型アーキテクチャによって。
MySQLはUndoログによって。
自身のトランザクションIDをキーに見るべきデータを見ている。

※MySQLはMVCCに加えて、ギャップロック、ネクストキーロックも関わる。

その他

NoSQLにおけるスキーマ定義の重要性
redisかます。