関数とうまく付き合おう - マイグレーションにやさしい「データベースとのかかわり方」 #PostgreSQL

少し前に「分析SQLスタイルガイド」をかなり真面目に考えたが2021年の最新記事
として話題になった。一方2015年の記事としてSQLスタイルガイドがある。
またGithubに SQLスタイルガイド · SQL Style Guide というのもある。
https://github.com/treffynnon/sqlstyle.guide 日本語版の翻訳プロジェクトあり。
マイグレーションといえば

RDBMSを置き換える
テーブル定義を置き換える

どちらを指すかとなってしまいそうだがここではいずれもひっくるめて、「作り直しを利かせたい」観点から、便利な内容をまとめる。筆者がOracle利用なのでOracleの中の癖のある内容が中心となるが、一言でいえば以下がコツ。

結論

**「優秀なデータベースに依存しすぎない」、「優秀なストアド関数、ユーザ定義関数、プロシージャに依存しすぎない」。**それが作り直しの利くマイグレーションにやさしい「データベースとのかかわり方」。

スタイルガイドを意識したSQL

最初から移行しやすい形に作っておくことの大前提その1。
「守る」と言ってしまうと局面によるので、「意識する」ところから。

オンラインフォーマッタ

[DeepL翻訳をはじめとしたテキストコピペ系Webサービスは機密情報の扱いに注意しよう]
(https://qiita.com/jnchito/items/5667091e00cdf38d299a) を意識しつつ、オンラインでも手に入るSQLフォーマッタ。

書籍: SQLアンチパターン

大前提の2つ目。

「DB設計やSQL記述の際に避けるべき事柄を1章で1つ、25個紹介する書籍です。リレーショナルデータベースを中心に据えたシステム開発には、様々な場面で陥りやすい失敗（アンチパターン）があります。本書はデータベース論理設計、データベース物理設計、クエリの記述、アプリケーション開発という4つのカテゴリに分け、それぞれの分野におけるアンチパターンを紹介し、失敗を避けるためのより良い方法を紹介します。複数の値を持つ属性や再帰的なツリー構造の格納から、小数値の丸めやNULLの扱いに起因する問題、全文検索やSQLインジェクション、MVCアーキテクチャなど、実践的かつ幅広いトピックを網羅します」(O’Reilly)

設計については以下も。
4ステップで作成する、DB論理設計の手順とチェックポイントまとめ
4ステップとは

ステップ1: エンティティの抽出
ステップ2: エンティティの定義
ステップ3: 正規化
ステップ4: ER図の作成

話題別

さてここまでを前置きとし、以降、具体的なコツを紹介していきます！

ストアド関数、ユーザ定義関数、プロシージャ

当記事の肝です。
例えば PostgreSQL on AWS RDSで利用できるOracle Database互換関数の紹介を見ると、一通り、Oracleとの互換を意図した関数はベンダー側で網羅してくれている。
が、その互換関数が用意されているからOKというわけではないのです。

例えば以下が具体例。
ユーザー定義関数と「ひとつの SQL」ではパフォーマンスが数百% も違う!?
「データ件数が増えていくような処理、またそうした可能性のある処理では、常に『ひとつのSQL』の方が大幅に処理時間が短い、ということです。」つまりそういった関数に依存したプログラムや、DB間移行用の関数を用いるとパフォーマンスに大きく影響が出ることがある。

自分が経験した例は、フロントエンドを100000000倍速くした(ﾟ∀ﾟ)


db2 => select
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0),
db2 (続き) =>  object_pkg.get_content(1, current timestamp, 0)
db2 (続き) => from dual
db2 (続き) =>
db2 (続き) => /

1                    2                    3                    4
    5                    6                    7                    8
        9                    10
-------------------- -------------------- -------------------- -----------------
--- -------------------- -------------------- -------------------- -------------
------- -------------------- --------------------
SQL0437W  この複合照会のパフォーマンスが最適ではない可能性があります。理由コード

: "1"。  SQLSTATE=01602

         73.00000000          73.00000000          73.00000000          73.00000
000          73.00000000          73.00000000          73.00000000          73.0
0000000          73.00000000          73.00000000

  選択された 1 レコード (1 警告メッセージ) が表示されました。

100000000倍は誇張ではありません。 データベース構成ファイルのステートメント・ヒープ (stmtheap)の大きさを増やしてくださいと言われ、1クエリの実行にかかる時間、およそ30分 ... Oracleではもともと、3秒ほどで結果がかえっていたもの。そんなトラブル事象が起こることも事実、ある。

動的なSQLクエリの生成でちょっとトリッキーな構築方法やぐるぐるSQL 等も、「工夫」と言い切ればそれはそれだが、使いどころを考えるのがマイグレーションにやさしいDB設計である。

やらかしDB設計から関数に関して参考

やらかしDB設計シリーズ。
本当にあったやらかしDB設計③【ロジカルクエリー】

クエリーチューニングをしよう！
ロジカルクエリーは実質的にブラックボックス化してしまっているため解読に時間が掛かり、もしかしたらアプリケーション側の変更が必要になるかもしれない。
「使える」と「使うしかない」は別物、本当にクエリーじゃないとダメか？
実行速度を無視したクエリーを作るな
致し方ない場合は必ずドキュメントを残せ

本当にあったやらかしDB設計⑧【ファンクションDB】
DBを使う側の人間が内部構造を気にしなくて良い、というのは理にかなっているが、DBを作る人が内部構造に興味がないとファンクションDBが生まれる、という。

アプリケーションとDBの関係性がおかしいとき、アプリケーションで行うのがめんどくさい処理があり、それをDBに押し付けようとしてしまう。
その結果、DBが下っ端仕事をメインにしてしまうことになり、秘伝のタレになっていく。
アプリケーションで行うべき処理はアプリケーションで行う。めんどくさいからと言ってDBに責任を押し付けてはいけないし、それを受け入れてはいけない。同じ処理をしているとしても、SQLとプログラミング言語では扱いやすさが変わる。
DBにはDBの設計をする。アプリケーションとDBは別物なので、別の観点で設計する。
「変に高度なことをして悦に入るな。」

TO_DATE

TO_DATEも関数の一つですが、取り上げてみます。
Oracle19cにおけるTO_DATE関数の挙動
「TO_DATE関数の挙動が変わってTO_DATE('20190520','YYMMDD')がエラーになる一方でTO_DATE('20120520','YYMMDD')はエラーにならない」などという挙動の解説。日付を解析するパーサーが変わったためではないかとのことだが、このような小さな挙動変更はVerupで起こりえる。
日付フォーマットも、上記の記事では YYMMDD が出てくるが幾つかあるので、端的にはプログラム上で「TO_DATEに頼らず処理できる方法があればそれを検討する」、もひとつの案。

日付型のフォーマットの手段として TO_CHARにTO_DATEを重ねる意味のように使われることもあるが、**「意味を考えて使うこと」**が大事。

DELETE, TRUNCATE, DROP

3者の違いを理解しないで使った場合TRIGGERの挙動、作成などに影響する。

TruncateVsDelete
DELETE文とTRUNCATE文の違い
 DELETEとTRUNCATEの違い

WHERE句がつけられるかつまり削除対象を選べるかどうか。
IDを含め消えるか。
TRIGGERが効くか効かないか。
ロールバックできるかどうか。
処理時間が速いかどうか。
HWM (ハイウォーターマーク) をリセットするかしないか。

特に SQL文　DELETE・TRUNCATE・DROPの違いにあるようにトリガーが依存するスキーマ・オブジェクト（テーブル）を抽出するSQL等を用いて要確認です。

SELECT * FROM ALL_DEPENDENCIES WHERE TYPE = 'TRIGGER' ORDER BY REFERENCED_NAME;

トリガーは、インデックスや整合性制約と同じくテーブルに依存するため、依存するテーブルがDROPされると一緒に削除される。テーブルをDROPする際に、そのテーブルに依存するトリガーがないかを確認する作業が必要になる。これはデータベースが変わるとひと手間変わる可能性が出る。

外部結合の (+)

Oracleでは外部結合の(+)が非推奨なので勉強してみた
左外部結合、右外部結合、完全外部結合の書き方を分かっておくこと。

BETWEEN

方言たり得るので、つかわない。
<=、 >=　でことたりる。

* ではなく列名を使う

出力データとして必要なのはA列だけ、といった場合

SELECT * FROM TABLE1

と

SELECT A FROM TABLE1

では、(大量のデータを扱う場合は特に) レスポンスに差が出る。

行数を数えるときは COUNT(*) よりも COUNT(列名) を使う

上と同様、レスポンスに差が出る。

表に別名を付ける

テーブルに別名を指定すると、オプティマイザが解析時にどの列がどのテーブルに属するかの判定を省略できる。特に複数のテーブルを使う SQL で効果的だが、単純な SQL でも大量に発行する場合は有効。

SELECT m.id, m.name FROM master m ORDER BY m.id

Indexの貼り方

TECHSCORE 15. インデックス
次の点に注意する必要があります。

パフォーマンス向上

表の結合条件に使用される列に対してインデックスを作成するとパフォーマンスが向上する。
値の分布が大きな列に対してインデックスを作成する。

パフォーマンス低下

逆に、値の分布が小さな列に対してインデックスを作成するとパフォーマンスが低下する。
また、テーブルを更新すると、インデックスも更新されるため、頻繁に更新されるような場合にインデックスを利用するとパフォーマンスは低下する。

その他、インデックスは表のデータとは別の領域に保存されるので、データベース設計時にはインデックスの領域も見込まなければならない。

Postgre でしか使えない機能

「ワイルドだろう？」とのタイトルだが、いずれも、ワイルドではない他のRDBMSでは通用しないことが考えられる。Postgreにロックインすることになるので、要注意。

UpdateにFrom句

ポスグレ、UPDATEにFROM句が使えるんだぜ、ワイルドだろう?

テーブルのInherits

なんとポスグレ、テーブルの継承ができる。
【PostgreSQL】テーブル継承は、使用上の注意をよく読んで正しく使いましょう（自戒）

PostgreSQL Advent Calendar

以下、ことPostgreの話題になってしまうが、Postgreならではの話、Postgreへの移行の話等が一通り。
https://qiita.com/advent-calendar/2018/postgresql
https://qiita.com/advent-calendar/2019/postgresql
https://qiita.com/advent-calendar/2020/postgresql

本当にあったやらかしDB設計シリーズ

「SQLアンチパターンで書かれているほど高尚な問題ではなく、もっと初歩的な、でもありがちな問題を取り上げています。初心者を脱出したと思っている人に是非読んでもらい、正しく設計してもらうことを目的としています」というもの。

本当にあったやらかしDB設計シリーズ一覧
 本当にあったやらかしDB設計①【R無しRDB】
本当にあったやらかしDB設計②【囚人番号テーブル】
本当にあったやらかしDB設計④【テストチューニング】

等により「いい加減な設計をするな」ということ、あるいは「おかしなチューニングをしない」等ということが説明されている。チューニングをしない、の心としては以下。

インデックスを貼るのはノーコストではありません
データを更新するときにはインデックスにも更新がかかります。そのため、無駄にインデックスが貼られているとパフォーマンスが悪くなります

またその他項目
本当にあったやらかしDB設計⑤【第三正規化病】
本当にあったやらかしDB設計⑥【見えない削除フラグ】
[本当にあったやらかしDB設計⑦【ステートフルDB】]
(https://qiita.com/bouitengineer12/items/325fec42c1c3bb385c64)
本当にあったやらかしDB設計⑨【文字列日付】
どこまでデータを消すのか等、「考えてデータベースと付き合う」ための例が多数網羅されている。

その他ブログ、Quora

1億件も 3億件も 5億件も RDB の守備範囲!? はじめ、パフォーマンスチューニングの話題多数。

移行そのものに関する話題なので、「データベースとのかかわり方」とは異なるが、参考まで。

等は、各種移行に関する質問。

SQLが学べる学習サイト

もっと根本的にデータベースとうまく関わるには勉強、と言えなくもない。
学習ツールとしては以下のようなものを活用できます。

w3schools
Codeprep
SQL学習サイト

まとめ

表題は「マイグレーションにやさしいすばらしいデータベース」とのかかわり方なのか、マイグレーションにやさしい「データベースとのすばらしいかかわり方」なのかどちらなのかと思われるかもしれない。もちろん前者におよそ期待するより後者でより積極的な改善をお勧めしたい、というスタンスで以上書きました。過去の経験＋情報まとめ。

2021年トレンドを考えると引き続きデータベースは群雄割拠。よって現実的な選択肢はいつでもマイグレーションできるようにしやすい形に作っておくことだと思っています。

データベースにおいても疎、適度な距離感が大事なのだな。以上です～

関数とうまく付き合おう - マイグレーションにやさしい「データベースとのかかわり方」

結論