🗂

DBパフォーマンスチューニングのためのSQL見直しリスト

2024/04/02に公開

SQL

はじめに

パフォーマンスが遅いからといってSQL単体に問題があるとは限りません。
本格的なパフォーマンスチューニングは、使用しているハードウェアやRDBMSの機能や特徴も意識することになります。
紹介するものは、SQL単体として解決できる一つのチューニング手段になります。

効率の良い検索を利用
ソートを回避
極値関数（MAX/MIN）でインデックスを使う
WHERE句で書ける条件はHAVING句には書かない
そのインデックスは本当に使われているか？
中間テーブルを減らす

1.効率の良い検索を利用

SQLでは、同じ結果を得るコードにも複数の書き方が存在します。
しかし、同じ結果を得られるコード同士にはパフォーマンスの差が生じます。
そのため、パフォーマンスを追求する場合には、効率の良いアクセスを支持できる書き方を知る必要があります。

1.1. サブクエリを引数に取る場合、INよりもEXISTSを使う

IN句は利便性とわかりやすさから、使用頻度の高いツール。
その反面、パフォーマンス面から見るとボトルネックになることがある。

EXISTSは結合キーにインデックスが貼られていれば、テーブルBの実表は見に行かず、インデックスを参照するのみで済む
EXISTSは1行でも条件に合致する行を見つけたらそこで検索を打ち切るので、INのように全表検索の必要がない（NOT EXISTSでも同様）

--遅い
SELECT *
FROM TableA
WHERE UserID IN (SELECT UserID FROM TableB);

--速い
SELECT *
FROM TableA A
WHERE EXISTS (SELECT * FROM TableB B WHERE B.UserID = A.UserID);

IN句では、サブクエリの結果が一時的なワークテーブルに格納され、その後ビューを全件走査するため、パフォーマンスに影響が出る可能性があります。
また、一般的にはワークテーブルにはインデックスが存在しない点もパフォーマンスに影響があります。
一方でEXISTSにはワークテーブルが作成されないため、パフォーマンスに優れることがあります。
ただし、ソースの可読性という点においてINはEXISTSに勝るため、INを用いても十分に短い応答時間が確保されているなら、あえて書き直す必要はない。

1.2. サブクエリを引数に取る場合、INよりも結合を使う

INの代わりはEXISTSだけでなく。結合に書き換える方法もある。

SELECT *
FROM TableA A
INNER JOIN TableB B 
ON A.UserID = B.UserID;

こうすることで、少なくともどちらかのテーブルのUserIDのインデックスが利用でき、サブクエリがなくなることで中間テーブルも作られない。

2.ソートを回避

使用する演算によってはDBMS内部で暗黙的にソートが行われています。
ソートがメモリ上で行われる間はまだいいですが、メモリ不足になりストレージを使ったソートが行われるようになると、パフォーマンスが著しく低下します。
ハードウェアリソースは有限なため、無駄なソートは極力回避する必要があります。

【ソートが発生する代表的な演算例】

GROUP BY句
ORDER BY句
集約関数（SUM, COUNT, AVG, MAX, MIN）
DISTINCT
集合演算子（UNION, INTERSECT, EXCEPT）
ウィンドウ関数（RANK, ROW_NUMBERなど）

2.1. 集合演算子のALLオプションをうまく使う

SQLにはUNION、INTERSECT、EXCEPTという集合演算があります。
これら集合演算は通常、重複排除のためのソートを行います。

--UNION 重複排除される
SELECT * FROM TableA
UNION
SELECT * FROM TableB;

--UNION ALL 重複排除されない
SELECT * FROM TableA
UNION ALL
SELECT * FROM TableB;

重複を気にする必要がない場合、重複が発生しないと明らかな場合は、UNIONの代わりにUNION ALLを用いる。
そうすることでソートの発生が行われない。

同様なことは、INTERSECTとEXCEPTにも当てはまります。

2.2. DISTINCTをEXISTSで代用する

DISTINCTも重複排除のためのソートを行なっています。
2つのテーブルを結合した結果を一意にするためにDISTINCTを使っているケースでは、EXISTSを利用することでソートを回避できます。

以下のような商品マスタから売上履歴に存在する商品を選択することを例に挙げる。

商品マスタ（Itemsテーブル）

item_no	item
1	えんぴつ
2	消しゴム
3	ボールペン
4	シャーペン

売上履歴（SalesHistoryテーブル）

sale_date	item_no	quantity
2024-03-01	1	3
2024-03-01	2	1
2024-03-01	3	4
2024-03-02	1	10
2024-03-02	3	7
2024-03-05	2	5
2024-03-05	3	4

売上のあった商品を探す例（同様の結果が得れる）

-- DISTINCT例（ソートが行われる）
SELECT DISTINCT item_no, item
  FROM Items I INNER JOIN SalesHistory SH
    ON I.item_no = SH.item_no;

-- EXISTS例（ソートが行われない）
SELECT item_no, item
  FROM Items I
 WHERE EXISTS ( SELECT *
                  FROM SalesHistory SH
                 WHERE I.item_no = SH.item_no);

3.極値関数（MAX/MIN）でインデックスを使う

SQLはMAXとMINという2つの極値関数を持っており、これら2つはソートを発生させます。
しかし、引数列にインデックスが存在する場合、そのインデックスのスキャンだけで済ませ、実表への検索を回避できる。

4.WHERE句で書ける条件はHAVING句には書かない

GROUP BYによる集約はソートやハッシュによる計算を行うため、事前に絞り込みを行う方がパフォーマンスが高くなる。
WHEREの条件にインデックスが利用できる場合があり、その場合には絞り込みが効率的になる。

結果的に以下の２パターンの場合、後者の方が効率良い動作をする。

--集約した後に、HAVING句でフィルタリング
SELECT sale_date, SUM(quantity)
  FROM SalesHitory
 GROUP BY sale_date
HAVING sale_date = '2024-03-01';

--集約する前にWHERE句でフィルタリング
SELECT sale_date, SUM(quantity)
  FROM SalesHistory
 WHERE sale_date = '2024-03-01'
 GROUP BY sale_date

5.インデックスの利用

そのインデックスは本当に使われているか？
インデックスを使うつもりで、SQLを記述するが実際はインデックスが使われない場合があります。
以降に適用されない例を挙げます。

5.1. 索引列に加工を行っている

-- BAD
SELECT * FROM TableA WHERE idx_1 * 1.1 > 100;

-- GOOD
SELECT * FROM TableA WHERE idx_1 > 100 / 1.1;

5.2. インデックス列にNULLが存在する

インデックスにおいてIS NOT NULLやIS NULLを使用するとインデックスが利用されなかったりと制限を受けることがある。
IS NOT NULLと同様の条件でインデックスをどうしても利用することもできないこともない。
しかし、コードの意味を混乱させることため、非推奨ではあるものの応急処置知識として考えておく。

-- インデックスが利用されない
SELECT * FROM TableA WHERE idx_1 IS NULL;

-- IS NOT NULLの応急処置 idx_1の最小値未満を指定する
SELECT * FROM TableA WHERE idx_1 > 0;

5.3. 否定系を使っている

以下の否定系はインデックスを使用できません。

<>
!=
NOT IN

-- BAD
SELECT * FROM TableA WHERE idx_1 <> 100;

5.4. ORを使っている

ORを使って条件を結合するとインデックスが利用できなくなる。
また、利用できたとしてもANDに比べれば非効率となる。

-- BAD
SELECT * FROM TableA WHERE idx_1 >100 OR idx_2 = 'hoge';

5.5. 複数索引の場合に列の順番を間違えている

idx_1、idx_2、idx_3に対してこの順番でインデックスが張られているとします。
この場合、必ずidx_1を先頭に記述する必要があり、順番も崩してはいけない。

-- BAD
SELECT * FROM TableA WHERE idx_1 = 1 AND idx_3 = 300;
SELECT * FROM TableA WHERE idx_2 = 20 AND idx_3 = 300;

-- GOOD
SELECT * FROM TableA WHERE idx_1 = 1 AND idx_2 = 20;
SELECT * FROM TableA WHERE idx_1 = 1 AND idx_2 = 20 AND idx_3 = 300;

5.6. 後方一致、中間一致のLIKE述語を用いている

LIKEを使う場合は、前方一致検索のみインデックスが利用される。

-- BAD
SELECT * FROM TableA WHERE idx_1 LIKE '%a';
SELECT * FROM TableA WHERE idx_1 LIKE '%a%';

-- GOOD
SELECT * FROM TableA WHERE idx_1 LIKE 'a%';

5.7. 暗黙の型変換を行っている

暗黙の型変換は、オーバヘッドを発生させ、インデックスの使用までも不可になります。
手間だからと言わず、明示的な型変換を行うようにする。

--文字列で定義された`str_1`に対する条件例
-- BAD
SELECT * FROM TableA WHERE str_1 = 10;

-- GOOD
SELECT * FROM TableA WHERE str_1 = '10';
SELECT * FROM TableA WHERE str_1 = CAST(10, AS CHAR(2));

6.中間テーブルを減らす

SQLでは、サブクエリの結果を新たなテーブル（中間テーブル）とみなして、あたかもオリジナルのテーブルと同じようにコードの中で扱うことができる。
高い柔軟性を得る一方で、その中間テーブルを不用意にたくさん用いるとパフォーマンス低下の要因になりうる。

6.1. HAVING句を活用する

集約した結果に対する条件は、WHEREに頼らずHAVINGを使って設定をする。

-- BAD 不要な中間テーブル TMP
SELECT * 
  FROM (SELECT * sale_date, MAX(num) AS max_num
          FROM TableA
         GROUP BY sale_date) TMP
 WHERE max_num >= 10;

-- GOOD
SELECT sale_date, MAX(num)
  FROM TableA
 GROUP BY sale_date
HAVING MAX(num) >= 10;

6.2. IN述語で複数のキーを利用する場合は、一箇所にまとめる

=、<、>といった比較述語やINの引数に、値のリストを取ることができます。
以下に複数のキーを用いてIN述語を組み立てるケースを例に挙げる。

-- BAD サブクエリを2つ使用している
SELECT id, state, city
  FROM Addrresses1 A1
 WHERE state IN (SELECT state
                   FROM Addresses2 A2
                  WHERE AA1.id = A2.id)
    AND city IN (SELECT city
                   FROM Addresses2 A2
                  WHERE AA1.id = A2.id);

-- GOOD キーの結合
SELECT *
  FROM Addrresses1 A1
 WHERE id || state || city IN (SELECT id || state || city
                                 FROM Addresses2 A2);
-- GOOD インデックスが利用できる
SELECT *
  FROM Addrresses1 A1
 WHERE (id, state, city) IN (id, state, city
                                 FROM Addresses2 A2);

6.3. 集約よりも結合を先に行う

結合と集約を併用するケースでは、集約よりも先に結合を行うことで、中間テーブルを省略できます。
これは、集合演算としての結合が「掛け算」として機能するからです。
通常のDB設計では、「多対多の関係」は関連エンティティによって2つの「一対多の関係」に分解されています。
そのため、結合によって行数が増えることはありません。

6.4. ビューの利用は計画的に行う

ビューで集約をしていいたら要注意です。
ビュー定義のクエリに以下のような演算が含まれている場合、非効率なSQLとなり、思わぬ速度低下を招くことがあります。

集約関数(AVG, COUNT, SUM, MIN, MAX)
集合演算子(UNION, INTERSECT, EXCEPTなど)

まとめ

チューニングにおいて本質的に大事なことは「ボトルネックを見つけ、そこを重点的に解消すること」
データベースとSQLにおいてボトルネックになるのはストレージへのアクセスです。
本記事で紹介した「ソートを減らす」、「インデックスの利用」、「中間テーブルの省略」も全ては。低速なストレージへのアクセスを減らすことを目的としています。