X(Twitter) Zenn GitHub RSS 共有

Relational Database

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

色々な製品

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

最小・最小・最速であれ

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

NULL

NULLはカス。
全カラムにNOT NULLつけろ。

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

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

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

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

データ永続化の基本

PostgreSQL

テンプレート

PostgreSQLのやつ。
新規DB作成時のもとになる。

クラスタ化インデックス

インデックス上に実データが格納されているもの。
MySQLのInnoDBなど。

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指定カラム, 集計対象値}でインデックスを貼っているとする。

タイトインデックススキャン

インデックスを使用してGROUP BYクエリを解決

ルースインデックススキャン

集計対象値のMINやMAXならば、各GROUP BY指定カラムの最初のインデックスデータを見ればすぐわかる。

(A, 1),(A, 2),(A, 3),(B, 4),(B, 5)
ならば先頭のカラムの値、つまり1と4のカラムだけを見ればいいから
パフォーマンスはよい。

MIN()を取ろうとすれば、A1,B4が返却される。

ロストアップデート

  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

その他

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