これはなんですか

アプリから出している変更不可能なSQLの性能調査の例
大したことはしていない。

主文

運用君「どんどん実行時間が長くなっていくSQLがあるんです！助けて！」
ワイ「わかった！」

ただ、これらのテーブルは厳密にRelationalなDBではなくて（FKとか貼られているわけではなく）、廃棄となった行を理論削除で保持するなど効率的ではない運用が見られる。

どうも時間がたつにつれ集計用SQLが時間を食うようになっているらしい。
先日アプリ側で設定したSQL実行時間(30秒)を超過したらしい。

SQLの構成には一部WHERE句で演算すんなと文句いいたいところはあるが、おおむね問題はないように見える。
EXPLAIN ANALYZEを見ても使ってるfunctionの数が多いことが気になるけどSEC SCANはしていない集計もスムーズに行われているように見える。

しかしこのSQL、４テーブルしか関わってない割には複雑な処理をしている。
マテリアライズド・ビュー使うとか、中間テーブルみたいなのに中間集計結果をputすることでもっと実行時間を短くできそうだなあという感触があった。

だが、ベンダーが渋るためアプリの改修ができないためこの先の見通しと、改善について考える。

このSQLの実行時間はこの先どんな増加の仕方をするのか

まず、キーとなるテーブルを探す・・この場合は「購買履歴」のテーブルに応じて他のテーブルの行数が変化する。

キャンセルや変更の履歴は他のテーブルに比べて少ししかないため無視する。
商品マスタも購買系のテーブルに対して少数である上にPKでの参照であるためこれも無視する。

となると、純粋に購買履歴と明細ですよね・・

対象の４テーブルだけを別な実験用DBに移して試験を行う。

まず、諸元について、2023年３月１日時点で下記の行数である。

購買履歴テーブル: 310,000 行
購買明細テーブル： 2,320,000 行
実行時間: 32秒

過去を見てみよう。

created_at というカラムが行の挿入時にタイムスタンプを記録しているため、これを使って去年の３月１日の状態を作り出す。

DELETE FROM 購買履歴 WHERE created_at >= '2022-03-02';
DELETE FROM 購買明細 WHERE created_at >= '2022-03-02';

購買履歴テーブル: 220,000 行
購買明細テーブル： ... 行
実行時間: 24秒

なるほど、

おっとここで性能試験の資料がでてきました。
（ダミーデータであることに留意）

購買履歴テーブル: 100,000 行
購買明細テーブル： 8,00,000 行
実行時間: 5秒

ちょっと購買明細のテーブル・・行が多すぎないかな？と思うけど、参考にさせてもらおう・・

グラフどん
青い線が実行時間、オレンジの線が１行あたりの処理時間。

オレンジの線が下がっているのが性能的によいSQLで、上がっているとてのつけられないクソSQLになります。

データベースにはB-treeによるindexという仕組みがあるため、検索対象となる行数が増えても、１行あたりの検索にかかる時間はどんどん小さくなるようになっているんですよね。

それを踏まえるとこのSQLは複雑な処理をやってるので・・まー及第点かな・・
購買履歴の増加に伴って一次曲線で実行時間が増えていく・・と。

この事実を踏まえて対策として下記を打ちます

この先はかすれていて読めない。