達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ (CodeZine BOOKS)

作者: ミック
出版社/メーカー: 翔泳社
発売日: 2018/10/11
メディア: 単行本（ソフトカバー）
この商品を含むブログを見る

11 SQLを速くするぞ

この記事書いている人の雑感

本章はあくまでSQLのみの最適化
テーブル構成やインデックスの検討、統計情報、設定（メモリやジャーナルのサイズ等）、クエリログ等気にする項目は山ほどある
本章は今までの内容の集大成（読み解くのにそれほど苦労しないと思う）
- SQL中級者の入り口に立ったことが肌感覚として伝わってくるのでは

はじめに
効率の良い検索を利用する
- サブクエリを引数に取る場合、INよりもEXISTSを取る
- サブクエリを引数に取る場合、INよりも結合を使う
ソートを回避する
- 集合演算子のALLをうまく使う
- DISTINCTをEXISTSで代用する
極値関数（MAX/MIN）でインデックスを使う
WHERE句でかける条件はHAVING句には書かない
- GROUP BY句とORDER BY句でインデックスを使う
そのインデックス、本当に使われてますか？
- 索引列に加工を行っている
- インデックス列にNULLが存在する
- 否定形を使っている
- ORを使っている
- 複合索引の場合に、列の順番を間違えている
- 後方一致、または中間一致のLIKE述語を用いている
- 暗黙の型変換を行っている
中間テーブルを減らせ
- HAVING句を活用しよう
- IN述語で複数のキーを利用する場合は、一箇所にまとめる
- 集約よりも結合を先に行なう
- ビューのご利用は計画的に
まとめ
1. INにサブクエリを取る場合は、EXISTSまたは結合に書き換える。
2. インデックスを利用するときは「左辺は裸」が基本。
3. SQLは明示的にソートを記述することはないが、暗黙のソートを行う演算が多くあるので注意が必要。
4. 余計な中間テーブルをなるべく減らそう。
5. レコード数を絞れる条件は早い段階で記述する。負債は早く返さないと、あとでツケを払うことになる。

はじめに

本格的なパフォーマンスチューニングを行なうには、使用しているハードウェアやDBMSが持つ機能や特徴についての知識が不可欠
本章では、なるべく実装非依存で、SQLを見直すだけで手軽にできる方法を集めた

CREATE TABLE Class_A (id INT, name VARCHAR(16),
  PRIMARY KEY(id));
CREATE TABLE Class_B (id INT, name VARCHAR(16),
  PRIMARY KEY(id));

効率の良い検索を利用する

効率よいアクセスをオプティマイザに指示できる書き方を知る

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

IN
- パフォーマンス×
- 可読性○
EXISTS
- パフォーマンス○
- 可読性×

-- 遅い
SELECT *
  FROM Class_A
 WHERE id IN (SELECT id FROM Class_B);

-- 速い
SELECT *
  FROM Class_A A
 WHERE EXISTS
      (SELECT *
         FROM Class_B B
        WHERE A.id = B.id);

EXISTSのほうが速いと期待できる理由は以下の2つです。
1. もし結合キー（この場合はid）にインデックスが張られていれば、Class_Bテーブルの実表は見に行かず、インデックスを参照するのみで済む。
2. EXISTSは1行でも条件に合致する行を見つけたらそこで検索を打ち切るので、INのように全表検索の必要がない。これはNOT EXISTSの場合でも同様。
最近のDBMSはINでも速度改善が行われている

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

インデックスがない場合は、恐らくEXISTSに軍配があがる

-- INを結合で代用
SELECT A.id, A.name
  FROM Class_A A INNER JOIN Class_B B
    ON A.id = B.id;

ソートを回避する

ソートは頻繁に「暗黙裡」に行われている
ソートが発生する代表的な演算
- GROUP BY句
- ORDER BY句
- 集約関数（SUM, COUNT, AVG, MAX, MIN）
- DISTINCT
- 集合演算子（UNION, INTERSECT, EXCEPT）
- ウィンドウ関数（RANK, ROW_NUMBER等）
ストレージを使ったソートが行われると、パフォーマンスが大きく低下
- Using filesort

集合演算子のALLをうまく使う

UNION, INTERSECT, EXCEPT
普通に使うと重複排除のためのソートを行う
ALLを使うとソートを避けられる
- UNION ALL, INTERSECT ALL, EXCEPT ALL

-- 商品マスタ
CREATE TABLE Items (item_no INT, item VARCHAR(16),
  PRIMARY KEY(item_no));
-- 売り上げ履歴
CREATE TABLE SalesHistory (sale_date DATETIME, item_no INT, quantity INT,
  PRIMARY KEY(sale_date, item_no));

DISTINCTをEXISTSで代用する

結果を一意にするためにDISTINCTを使っている場合

-- DISTINCT版
SELECT DISTINCT I.item_no
  FROM Items I INNER JOIN SalesHistory SH
    ON I.item_no = SH.item_no;

-- EXISTS版
SELECT item_no
  FROM Items I
 WHERE EXISTS (SELECT *
                 FROM SalesHIstory SH
                WHERE I.item_no = SH.item_no);

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

-- これは全表検索が必要
SELECT MAX(item)
  FROM Items;

-- これはインデックスを利用できる
SELECT MAX(item_no)
  FROM Items;

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

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

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

GROUP BY句とORDER BY句でインデックスを使う

読んで字のごとく

そのインデックス、本当に使われてますか？

最もポピュラーなBツリーインデックス→二分探索

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

インデックスを利用するときは、列は裸
左辺で計算を行いたい場合は、関数索引という方法もある

-- NG
SELECT * FROM SomeTable WHERE col_1 * 1.1 > 100;

-- OK
SELECT * FROM SomeTable WHERE col_1 > 100 / 1.1;

-- NG
SELECT * FROM SomeTable WHERE SUBSTR(col_1, 1, 1) = 'a';

-- OK
SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';

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

インデックスが使用されないかも
- IS NULLやIS NOT NULLを使用する
- NULLが多い列

SELECT * FROM SomeTable WHERE col_1 IS NULL;

IS NOT NULLの条件でインデックスをどうしても使いたい場合
- すべての範囲を満たす条件を与える
- わかりにくいので使用には注意

-- IS NOT NULLの代用案
SELECT * FROM SomeTable WHERE col_1 > 0;

否定形を使っている

インデックスを使用できない
- <>
- !=
- NOT IN

SELECT * FROM SomeTable WHERE col_1 <> 100;

ORを使っている

ORにはインデックスを使用できないか、できても限定的
ORに向いたビットマップインデックス
- 更新コストが高い
- 更新処理が少ないBI/DWH（Business Intelligence/Data Ware House）向け

SELECT * FROM SomeTable WHERE col_1 > 100 OR col_2 = 'abc';

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

列の順番と同じ順番で条件を書く

-- (col_1, col_2, col_3)の複合インデックスが張られている場合
SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;

後方一致、または中間一致のLIKE述語を用いている

LIKE述語を使うときは、前方一致検索のみ索引が使用される

× SELECT * FROM SomeTable WHERE col_1 LIKE '%a';
× SELECT * FROM SomeTable WHERE col_1 LIKE '%a%';
○ SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';

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

-- もし文字列型なら…
× SELECT * FROM SomeTable WHERE col_1 = 10;
○ SELECT * FROM SomeTable WHERE col_1 = '10';
○ SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

中間テーブルを減らせ

中間テーブルの問題点
1. データを展開するためにメモリやストレージを消費すること
2. 元テーブルに存在したインデックスを使うのが難しくなる（特に集約した場合）

HAVING句を活用しよう

集約した結果に対する条件について
- HAVINGをまっとうに利用する

-- NG
SELECT *
  FROM (SELECT sale_date, MAX(quantity) AS max_qty
          FROM SalesHistory
         GROUP BY sale_date) TMP -- 無駄な中間テーブル
 WHERE max_qty >= 10;

-- OK
SELECT sale_date, MAX(quantity)
  FROM SalesHistory
 GROUP BY sale_date
HAVING MAX(quantity) >= 10;

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

キーを結合して上手に検索できる場合がある

-- NG
SELECT id, state, city
  FROM Addresses1 A1
 WHERE state IN (SELECT state
                   FROM Addresses2 A2
                  WHERE A1.id = A2.id)
   AND city IN (SELECT city
                  FROM Addresses2 A2
                 WHERE A1.id = A2.id);

-- OK
SELECT *
  FROM Addresses1 A1
 WHERE id || state || city IN (SELECT id || state || city
                                 FROM Addresses2 A2);

-- OK（行比較をサポートしているDB）
SELECT *
  FROM Addresses1 A1
 WHERE (id, state, city) IN (SELECT id, state, city
                               FROM Addresses2 A2);

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

「普通の設計」が前提

ビューのご利用は計画的に

以下要注意
- 集約関数（AVG, COUNT, SUM, MIN, MAX）
- 集合演算子（UNION, INTERSECT, EXCEPT等）

まとめ

低速ストレージへのアクセスを減らす
本章の要点
1. INにサブクエリを取る場合は、EXISTSまたは結合に書き換える。
2. インデックスを利用するときは「左辺は裸」が基本。
3. SQLは明示的にソートを記述することはないが、暗黙のソートを行う演算が多くあるので注意が必要。
4. 余計な中間テーブルをなるべく減らそう。
5. レコード数を絞れる条件は早い段階で記述する。負債は早く返さないと、あとでツケを払うことになる。
参考文献（本文中）
1. ミック『SQL実践入門』
  - 実行計画の判断

以上

zodiacjobprogrammer’s diary

達人に学ぶ SQL徹底指南書 11章 SQLを速くするぞ

11 SQLを速くするぞ

この記事書いている人の雑感

目次

はじめに

効率の良い検索を利用する

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

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

ソートを回避する

集合演算子のALLをうまく使う

DISTINCTをEXISTSで代用する

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

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

GROUP BY句とORDER BY句でインデックスを使う

そのインデックス、本当に使われてますか？

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

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

否定形を使っている

ORを使っている

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

後方一致、または中間一致のLIKE述語を用いている

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

中間テーブルを減らせ

HAVING句を活用しよう

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

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

ビューのご利用は計画的に

まとめ