X(Twitter) Zenn GitHub RSS 共有

Relational Database

作成日時：2024-08-01以前
更新日時：2024-11-16

最少・最少・最速であれ

システムが遅いのは外部APIの呼び出し。
つまりDBの所為。
なのでパフォーマンスを改善したければDBを何とかしろ。
アクセスプランを取れ。

非機能要件にはねる。

色々な製品

製品メモ
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台か。

データ永続化の基本

PostgreSQL

cron使って何らかの処理を定期実行できる。
pg_cron。

テンプレート

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におけるインデックススキャン

ロストアップデート

  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は日時。

SQLの評価順序

但し、あくまで一般的な評価順序であって、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した場合、使う使わないにかかわらず、関係なくロードされる。
行指向であるため、関係のないデータも読み込むから。

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

テーブル設計

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

トランとマスタ

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

設計

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

マルチテナントにおけるRLS

HWM(High Water Mark)

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

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

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

2. セグメント (Segment)

3. エクステント (Extent)

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

5. レコード (Record)

駆動表は小さく

※内部表の結合キーにインデックスが貼られているという前提条件で
レコードのアクセス数は「駆動表のレコード数 × 2」となるので、駆動表を小さくすればするほど早くなる。

らしいが、インデックスを走査するコストは？とは思う。
内部表フルスキャンに比べたら微々たるものだから0としたか。

一応、インデックスなしでも駆動表は小さいほうがやや早いらしい。

外部表サイズ内部表サイズ外部表スキャン内部表スキャン総スキャン数
100件10件1 * 100 = 100100 * 10 = 1,0001,100
10件100件1 * 10 = 1010 * 100 = 1,0001,010

でもRDBMSが駆動表を選んでくれるし。

なので確認することは

だろうか。

ORMにおけるクラス分割

下記の感じ。

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

カラム数が大きすぎると、INSERT時にエラーが出る

row is too big: size XXXXX, maximum size 8160

Postgresのカラム数と1レコードサイズの上限に関するメモ #postgres - Qiita

整理するとPostgreSQLでは、

TEXT

TOAST

文字長さに応じて、格納するデータサイズが異なる。
大小の閾値は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以下にした場合で、更新した場合、そのページ内に追記される。
すなわち新しくページを作らなくてよいので、多少パフォーマンスがよくなる。

リンク

その他

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

FDW

外部データとの連携 ～FDWで様々なデータソースとつなぐ～｜PostgreSQLインサイド : 富士通

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のロックがずっと解除されない可能性あり

対策

データの存在確認

COUNTよりLIMIT 1。
COUNTはフルスキャンが発生しかねない。

集約関数

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

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に投げる正しい命題を作る能力が重宝されると思うがどうか。

OR

UNION使ってORを消す。
インデックスを使えるから。

Math.MAX/MIN

GREATEST関数/LEAST関数。
MySQLとPostgreSQLにある。

構造

すごい大雑把な比較

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

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