LoginSignup
3
3

More than 1 year has passed since last update.

@ono_s(ono)

SQL実践入門を読んで

Posted at

SQL実践入門

以下の本を読んだので、勉強内容まとめ。
image.png

目的

実行計画を読み解け、簡単なSQLチューニングならできるようになる。
（インデックスを貼る、結合アルゴリズムを変更させるようにSQLを改変する等）

DBMSのアーキテクチャ

image.png

メモリにデータがあれば高速、ディスクにしかなければ低速。頻繁にアクセスされるデータをメモリ上に置くかで速度は変わる。
image.png

基本的にSQL文が遅くなる原因は大きく分けて２つあるが、ほとんどの場合はストレージのI/Oによるものである。

  • ストレージのI/O
  • CPUによる演算

※非常に小さいデータにしかアクセスしない場合（単一主キーによる検索SQLなど）、CPUによる演算が原因となるときもある。

クエリの処理とデータアクセスの実行のイメージ

image.png

カタログマネージャ（統計情報）にテーブルやインデックスの情報が格納されている。
→そのため、統計情報が古いと正常な実行計画が返却されない。

統計情報として収集される代表的なものは以下のとおり

  • 各テーブルのレコード数
  • 各テーブルの列数と列のサイズ
  • 列値のカーディナリティ（値の個数）
  • 列値のヒストグラム（各値がいくつあるのかの分布）
  • 列内にあるNULLの数
  • インデックス情報

大きく更新がなされた際に統計情報を更新することで、適切な実行計画を作成できるようになる。
そのため、統計情報の更新についてもしっかり考える必要がある

  • 定期的に更新するジョブをジョブネットに組み込む
  • 自動的に統計情報を更新してくれるデータベースもある
  • Oracleならデフォルトで定期的に実行してくれる
  • PostgreSQLでも閾値以上のデータ更新処理が走った後に自動実行される

B+treeインデックスについて

image.png

インデックスを有効活用するポイント

以下の列にインデックスを貼ると効果が出やすい。

  • カーディナリティが高い列
  • 選択率が低い（少ない行に絞り込める）列（5~10%未満）

インデックスが使えない検索条件

  • 中間、後方一致のLIKE句
  • カラムの値に対して演算を実行、または、関数を適用している
  • IS NULL句を利用している
  • 否定形(<>、!=、NOT IN）を利用している

実行計画の確認方法

PostgreSQLの場合は以下で確認できる。
EXPLAIN SQLクエリ
また、以下にすることで、実際にSQL文を投げてみた結果を取得することができる
EXPLAIN ANALYZE SQLクエリ

テーブルフルスキャンの場合の実行計画

以下のSQLを投げた時の実行計画

SELECT * 
  FROM Shops;

-------------------------------------------------------
Seq Scan on shops (cost=0.00..1.60 rows=60 width=22)
  • on Shops

操作対象のオブジェクト（テーブル以外にもインデックス、パーティション、シーケンスなども現れる）

  • Seq Scan

シーケンシャルスキャン。当該テーブルの全データにアクセスするという意味。

  • rows=60

操作の対象となるレコード数（あくまで統計情報から取得した値なので実際に入っているデータとは違う場合が多い）

  • cost=0.00..1.60

実行時間の目安となる値ではあるが、あくまで目安なので、大きくても早い時もあるし、小さくても遅いときもある。
ただ、経験的にはコスト1000で1秒のイメージではある。（たしか。。）

cost=XXX...YYY
ちなみに上記のXXXとYYYの2つの値の意味は、XXXがレコードの1件目を取り出し始めるのにかかるコストで、YYYがレコードの最終行目を取り出し終わるのにかかるコストという意味。

インデックススキャンの場合の実行計画

以下のSQLを投げて実行計画を取得する。

SELECT *
  FROM Shops
 WHERE shop_id = '00050';

-------------------------------------------------------
Index Scan using pk_shops on shops (cost=0.00..8.27 rows=1 width=320)
　Filter: (shop_id = '00050'::bpchar)
  • Filter: (shop_id = '00050'::bpchar)

データ量が少なければインデックススキャンでもフルスキャンでも大差はない。
ただ、多量のレコードを取得する場合（一般に7%～10%程度）やレコード総数が少ない場合にはフルスキャンを行ったほうが効率が良い場合もある。
image.png

テーブル結合の実行計画

SELECT shop_name
  FROM Shops S 
 INNER JOIN Reservations R
    ON S.shop_id = R.shop_id;

-------------------------------------------------------
Nested Loop (cost=0.14..14.80 rows=10 width=2)
  -> Seq Scan on reservations r (cost=0.00..1.10 rows=10 width=6)
  -> Index Scan using pk_shops on shops s (cost=0.14..1.36 rows=1 width=8)
       Index Cond: (shop_id = r.shop_id)
  • Nested Loop

Nested Loop結合（後述）でテーブルを結合する

  • アクセスする順番
  1. 深い階層から処理される。
  2. 同じインデントなら上に位置した項目から処理される

→そのため、Seq Scan → Index Scan → Nested Loopの順で処理される。

実行計画の重要性

オプティマイザが選ぶ実行計画は動的に変わるため、基本はオプティマイザに任せるようにするのが良い。
（データベースのデータは時とともに変化するので）

ただ、絶対に使ったほうが早いはずのインデックスを使ってくれない、Nested Loop結合でJOINしたほうが早いのにMerge JOINされてしまう、など明らかにおかしいことも行ってしまうことがある。
そのため、ヒント句を用いて実行計画を手動で変えることもある。

以下はT1とT2をNested Loop結合に強制するヒント句。

/*+
 * NestLoop(T1 T2)
 */
SELECT *
  FROM TABLE_A T1
 INNER JOIN TABLE_B T2

SQLアンチパターン

UNIONによる条件分岐はNG

SELECT item_name, year, price_tax_ex AS price
  FROM Items
 WHERE year <= 2001
 UNION ALL
SELECT item_name, year, price_tax_in AS price
  FROM Items
 WHERE year >= 2002;
item_name year price_tax_ex price_tax_in
ITEM1 2000 PRICE_EX1
ITEM2 2001 PRICE_EX1
ITEM3 2002 PRICE_IN1
ITEM3 2003 PRICE_IN1 
------------------------------------------------------------
Append (cost=0.00..2.42 rows=12 width=47)
  -> Seq Scan on items (cost=0.00..1.15 rows=6 width=47)
       Filter: (year <= 2001)
  -> Seq Scan on items (cost=0.00..1.15 rows=6 width=47)
       Filter: (year >= 2002)

WHERE句で条件分岐させるのは素人

SELECT item_name, year,
  CASE WHEN year <= 2001 THEN price_tax_ex
       WHEN year >= 2002 THEN price_tax_in END AS price
  FROM Items;

---------------------------------------------------------
 Seq Scan on items (cost=0.00..1.18 rows=12 width=51)

※UNIONではインデックスを利用できていたが、CASE式を利用することでインデックスが働かなくなり、性能が悪化することもあるので注意。

SQLのループはNG

java
for (String string : productList) {
    DTO dto = select(string);
}
selectSQL
<select>
  SELECT *
    FROM PRODUCT
   WHERE productCd = #{string}
</select>
java
List<DTO> dtoList = select(productList);
selectSQL
<select>
  SELECT *
    FROM PRODUCT
   WHERE productCd IN 
    <foreach item="item" index="index" collection="productList"
        open="(" separator="," close=")">
          #{item}
    </foreach>
</select>

結合アルゴリズム

Nested Loop

image.png

結合コストはR(T1)×R(T2)となる。
ここで、内部表にインデックスを貼ることにより、R(T2)≒1と近似できるため、コストは実質R(T1)となる。
その際に駆動表に小さいテーブルを選ぶことで、よりコストを削減できる。

実際のSQLで確認

SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name
  FROM Employees E INNER JOIN Departments D
    ON E.dept_id = D.dept_id;
内部表のインデックスが使われない場合
----------------------------------------------------------------------------
Nested Loop (cost=0.00..5005.38 rows=510 width=212)
　 Join Filter: (e.dept_id = d.dept_id)
　 -> Seq Scan on departments d (cost=0.00..16.50 rows=650 width=94)
　 -> Materialize (cost=0.00..17.65 rows=510 width=130)
        -> Seq Scan on employees e (cost=0.00..15.10 rows=510 width=130)
内部表のインデックスが使われる場合
---------------------------------------------------------------------------------------------
Nested Loop (cost=0.15..150.90 rows=510 width=212)
　 -> Seq Scan on employees e (cost=0.00..15.10 rows=510 width=130)
　 -> Index Scan using departments_pkey on departments d (cost=0.15..0.26 rows=1 width=94)
        Index Cond: (dept_id = e.dept_id)

Hash

ハッシュ関数：入力に対してなるべく一意性と一様性を持った値を出力する関数
ハッシュアルゴリズムとして有名なものがMD5やSHA1、SHA256など
パスワードのDBへの格納等に利用されている。
'password'→5f4dcc3b5aa765d61d8327deb882cf99(md5)

image.png

  1. 小さいほうのテーブルの結合キー列を全行読み込みハッシュ関数にかける
  2. 生成したハッシュテーブルをメモリ上に読み込む
  3. もう片方のテーブルを上から順に読み込み、順番にハッシュ関数にかけて合致するものがないかを検索する。
hash結合の実行計画
------------------------------------------------------------------------
Hash Join (cost=24.63..46.74 rows=510 width=212)
　 Hash Cond: (e.dept_id = d.dept_id)
　 -> Seq Scan on employees e (cost=0.00..15.10 rows=510 width=130)
　 -> Hash (cost=16.50..16.50 rows=650 width=94)
        -> Seq Scan on departments d (cost=0.00..16.50 rows=650 width=94)

メリット
結合する前にソートを行う必要がない。（後述するSort Merge結合との比較）
Nested Loop結合における内部表にヒット件数が多い場合でも有効に働く。

デメリット
ハッシュテーブルをメモリ上に持つため、メモリ上に収まらない場合ストレージを使用するので遅延が発生する
等値結合でしか使えない（条件に大小条件を利用できない）

Sort Merge

image.png
それぞれのテーブルに対してソートを行ってマッチングするだけ。

→ソートコストが重い。

結合アルゴリズムの利点欠点

image.png

サブクエリの弊害

  • サブクエリの計算コストがかかる（中身のSELECTを実行するたびにデータを作る必要がある）
  • データのI/Oコストがかかる（メモリ上に収まらない場合ストレージI/Oが発生する。）
  • 最適化を受けられない（インデックス等の情報が存在しないのでクエリ解析で最適化できない）

※最近の実行計画ではサブクエリ内部のロジックと外部のロジックをマージして実行計画を立ててくれる。
しかし、2階層以上ネストしたサブクエリではその恩恵を授かれない。

サブクエリで性能問題を起こす例

SELECT R1.cust_id, R1.seq, R1.price
  FROM Receipts R1
 INNER JOIN (SELECT cust_id, MIN(seq) AS min_seq
               FROM Receipts
              GROUP BY cust_id) R2
    ON R1.cust_id = R2.cust_id
   AND R1.seq = R2.min_seq;

image.png

-----------------------------------------------------------------------------------
Hash Join (cost=1.34..2.57 rows=1 width=10)
  Hash Cond: ((r1.cust_id = receipts.cust_id) AND (r1.seq = (min(receipts.seq))))
  -> Seq Scan on receipts r1 (cost=0.00..1.13 rows=13 width=10)
  -> Hash (cost=1.27..1.27 rows=4 width=6)
       -> HashAggregate (cost=1.19..1.23 rows=4 width=6)
       -> Seq Scan on receipts (cost=0.00..1.13 rows=13 width=6)

問題点
receiptsテーブルに2回アクセスしている。

Window関数による修正
SELECT cust_id, seq, price
  FROM (SELECT cust_id, seq, price,
               ROW_NUMBER()
                 OVER (PARTITION BY cust_id
                           ORDER BY seq) AS row_seq
                            FROM Receipts ) WORK
 WHERE WORK.row_seq = 1;

----------------------------------------------------------------------------
Subquery Scan work (cost=1.37..1.79 rows=1 width=16)
   Filter: (work.row_seq = 1)
   -> WindowAgg (cost=1.37..1.63 rows=13 width=10)
        -> Sort (cost=1.37..1.40 rows=13 width=10)
             Sort Key: receipts.cust_id, receipts.seq
             -> Seq Scan on receipts (cost=0.00..1.13 rows=13 width=10)

サブクエリでパフォーマンスが上がるケース

image.png

結合を先に行う
SELECT C.co_cd, MAX(C.district),
       SUM(emp_nbr) AS sum_emp
  FROM Companies C
 INNER JOIN Shops S
    ON C.co_cd = S.co_cd
 WHERE main_flg = 'Y'
 GROUP BY C.co_cd;

----------------------------------------------------------------------
HashAggregate (cost=53.46..53.52 rows=6 width=28)
  -> Nested Loop (cost=0.00..53.43 rows=6 width=28)
       -> Seq Scan on shops s (cost=0.00..23.75 rows=6 width=20)
            Filter: (main_flg = 'Y'::bpchar)
       -> Index Scan using companies_pkey on companies c (cost=0.00..4.93 rows=1 width=24)
            Index Cond: (co_cd = s.co_cd)

サブクエリを利用した場合

集約を先に行う
SELECT C.co_cd, C.district, sum_emp
  FROM Companies C
 INNER JOIN (SELECT co_cd, SUM(emp_nbr) AS sum_emp
               FROM Shops
              WHERE main_flg = 'Y'
              GROUP BY co_cd) CSUM
    ON C.co_cd = CSUM.co_cd;

----------------------------------------------------------------------
Nested Loop (cost=23.78..40.38 rows=2 width=32)
   -> HashAggregate (cost=23.78..23.80 rows=2 width=20)
        -> Seq Scan on shops (cost=0.00..23.75 rows=6 width=20)
             Filter: (main_flg = 'Y'::bpchar)
　 -> Index Scan using companies_pkey on companies c (cost=0.00..8.27 rows=1 width=24)
        Index Cond: (co_cd = shops.co_cd)
3
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
3