SQL

作成日時：2025-01-24
更新日時：2025-01-24

最少・最少・最速であれ

SQLの発行回数は最少
- × N + 1
- × 1件ずつ挿入
取得するデータ量は最少
- 集計はDB側でやれ
- 不要なデータは取るな
- 大量データのETLはDBだけで完結させろ
  - 大量にSELECT→APで加工→DBに大量投入とかやめろ
クエリは最速
- 実行計画を取れ

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

非機能要件にはねる。

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

row is too big: size XXXXX, maximum size 8160

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

整理するとPostgreSQLでは、

最大1600個のカラムを持つテーブルを作成できる。
しかし、そのテーブルにINSERTやUPDATEをすると下記のエラーが発生しうる。
- 「row is too big: size XXXXX, maximum size 8160」
- 1行のデータサイズが8,160を超える場合に発生する。
- doubleは8バイトなので、doubleのカラムが1020個以上の場合、エラーとなる。
  - ↑インデックスサイズも加算されうるので、厳密にはこの限りではない。
対策としては、投入するデータのサイズが8,160を超えないように分割して登録する。
- 例えば、INSERT時は200個のカラムに投入、残りはUPDATEで投入など。
- そもそもそんなテーブル作るな。

行値構成子

ぎょうち。
または行値式、行式。

WHERE (column1, column2) = (1, 'a')
WHERE (column1, column2) > (1, 'a')
WHERE (column1, column2) IN ((a, b), (c, d))
WHERE (column1, column2) IN (SELECT column1, column2 FROM...)

一番下が便利すぎる。
UPDATEに使う場合は、NULLに注意。

INSERT-SELECT

SELECTの結果をINSERTするやつ。
SELECT-INSERTと呼ぶと思っていたら違った。

セミジョイン

セミ(半分)
INNER JOINして内部表は取得しない的な奴。
下記みたいな。

SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE A.id = B.id);
SELECT * FROM A WHERE id IN (SELECT id FROM B)

第43回　MySQLの準結合（セミジョイン）について | gihyo.jp
SQLセミジョイン（準結合）

逆にINやEXISTSはプランナによって、セミジョインに変換されうる。

hashジョイン

片方からハッシュテーブルを作成。
もう片方のテーブルを順に読み、結合キーをハッシュ化して、突合。

木

二分木⇒log₂N
多分木⇒logᴍN

あえて駆動表を大きくする

表AとBの関連が、1:Nの場合、
Aを駆動表としたら、INDEX RANGE SCANとなる。
Bを駆動表とした場合は、INDEX SCANとなる。

外部キー更新時の挙動

外部キー制約を変更した際に、それを参照しているカラムを更新するように設定している場合。
製品によってはパフォーマンスが悪化する可能性あり。
その更新処理が1行ずつの更新だったりする。(Oracle)

OR

UNION使ってORを消す。
インデックスを使えるから。(インデックスマージ)
もしくはプランナが勝手にやってくれる。

Math.MAX/MIN

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

GROUP句の省略

MAXを使えば、項目を省略できる。
下記の受注テーブルと、受注明細テーブルがある。

orders(order_id, name)
order_details(order_id, seq, quantity)

nameはorder_idに対して一意。
この場合、MAXを使えばGROUP句にしてせずに済む。
(でもパフォーマンスはほぼ変わらんし、後者の方がややメモリの使用量が少ない)

-- 1
SELECT
    order_id
    , MAX(name)
    , SUM(quantity) 
FROM
    orders 
    INNER JOIN order_details 
        USING (order_id) 
GROUP BY
    order_id; 

-- 2
SELECT
    order_id
    , name
    , SUM(quantity) 
FROM
    orders 
    INNER JOIN order_details 
        USING (order_id) 
GROUP BY
    order_id
    , name;

データの存在確認

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

駆動表は小さく

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

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

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

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

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

なので確認することは

内部表の結合キーにインデックスが貼られていること
実行計画を見て、駆動表が十分に絞られていること
- 述語のプッシュダウン

だろうか。

SQLの評価順序

FROM
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT/OFFSET

但し、あくまで一般的な評価順序であって、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句などの述語条件を、データの取得元であるテーブルやインデックスといったより低いレベルに移動させる処理の事を「述語のプッシュダウン」と言う。

データの加工はAPでやるべきか？

加工の内容によるが。

ビジネスロジックをSQLに持たせたくないというのもある。
負荷が高くなった場合、APは台数を容易に増やせるが、DBはややめんどい。

加工によりデータ量が小さくなるならば、DBで加工するのもあり。

集計とかフィルタリングはDB、その他の加工はAP？

結局はケースバイケースで、最適なものはその都度考える。