【SQL】DS検定で公開されている模擬問題をチューニングをしてみた
この記事は Agent Grow Advent Calendar 2024 の記事です。
記事の概要
SQLのチューニングについて簡単な例として、DS検定(データサイエンティスト検定)にて公開されている模擬問題を利用して紹介する。
尚、模擬問題の利用については、事前にデータサイエンティスト協会事務局様より許可をいただいた。
対象者
初級の開発SEやデータエンジニアであるが、そうでなくとも下記のような方々の参考になれば幸いである。
- 仕様通りに動かすだけで精一杯
- チューニングしたいが、何をすれば良いか分からない
題材
データサイエンティスト協会が公開している模擬問題PDFが公開されているので、まずそちらを参照頂くのが良いが、これから記載するSQLのみを使っても問題ない。
今回扱うのは下記の問題である。
【出典】2021年度 データサイエンティスト検定 模擬問題② データエンジニアリング力
■模擬問題5
SQLに起こしてみた
DDLとDMLを用意したので、環境さえあればこれだけで確認可能である。
-- 模擬問題からSQLを書いてデータを作成し検証する
-- ※PostgreSQLに対応
-- 【出典】2021年度 データサイエンティスト検定 模擬問題② データエンジニアリング力
-- https://www.datascientist.or.jp/dscertification/what/
-- ◆事前準備 ---------------------------------------------------
-- テーブル定義
CREATE TABLE 売上テーブル (
販売Seq INTEGER PRIMARY KEY
, 製品 VARCHAR(20) NOT NULL
, 営業店 VARCHAR(20) NOT NULL
, 販売個数 INTEGER DEFAULT 0 NOT NULL
)
;
-- データ登録
INSERT INTO 売上テーブル
VALUES
(1,'商品A','東京',15)
,(2,'商品B','大阪',30)
,(3,'商品A','東京',27)
,(4,'商品B','東京',24)
,(5,'商品C','東京',30)
,(6,'商品B','大阪',15)
,(7,'商品B','大阪',25)
,(8,'商品A','大阪',20)
,(9,'商品C','名古屋',40)
,(10,'商品C','東京',30)
,(11,'商品B','名古屋',19)
,(12,'商品B','名古屋',22)
;
-- 全データ確認用のSQL
SELECT * FROM 売上テーブル
ORDER BY 販売Seq -- 主キー順にソート
;
-- ◆本題 ---------------------------------------------------
-- 【掲載されているSQL】
SELECT COUNT(*) FROM 売上テーブル
WHERE 製品 = '商品B'
AND (販売個数 >= 20 AND 販売個数 < 30)
AND (営業店 = '大阪' OR 営業店 = '名古屋')
;
-- 模擬問題では、上記SQLの実行結果が何件になるか?を問われている。
-- ※正解は出典元の解答を参照するか、実際に実行してみる。
環境はどうすれば?
PostgreSQLで作成したのだが、環境がなくても大丈夫。
WEBブラウザで実行可能なDB Fiddleを使えば確認可能だ。
※使い方はご自身で確認して頂きたい。
チューニングの観点
この模擬問題は、条件に該当するデータの件数を数えるSQLである。
では、条件が変わってしまわないように、且つ同じ結果を早く取得するようにSQLを変えてみる。
よって、変えるのは抽出条件の書き方である。
問題文のSQL実行計画
下記の『cost』を見ると、問題文のSQLでは初期コスト/トータルコストともに31.8程度である。
この値を基準に検証する。
-- ※再掲【掲載されているSQL】
SELECT COUNT(*) FROM 売上テーブル
WHERE 製品 = '商品B'
AND (販売個数 >= 20 AND 販売個数 < 30)
AND (営業店 = '大阪' OR 営業店 = '名古屋')
;
QUERY PLAN |
---|
Aggregate (cost=31.83..31.84 rows=1 width=8) |
-> Seq Scan on "売上テーブル" (cost=0.00..31.82 rows=1 width=0) |
Filter: (("販売個数" >= 20) AND ("販売個数" < 30) AND (("製品")::text = '商品B'::text) AND ((("営業店")::text = '大阪'::text) OR (("営業店")::text = '名古屋'::text))) |
条件の確認
そもそも条件は何だ? ということで、読み解くと下記のような条件である。
- 製品が『商品B』
- 販売個数が『20個』以上『30個』未満
- 営業店が『大阪』もしくは『名古屋』
では、条件が分かったので1つずつチューニングを試してみよう。
条件1:製品が『商品B』
ここは特に何もすることはないと考えられる。
製品 = '商品B'
条件2:販売個数が『20個』以上『30個』未満
問題文では、販売個数の条件に『20個以上』と『30個未満』の2つの条件が組合されている。
販売個数 >= 20 AND 販売個数 < 30
つまり『20個以上』という判定がされ、更に『30個未満』という判定がされるイメージである。
では、これを変更してみよう。
販売個数 BETWEEN 20 AND 29
BETWEENを使い『20個から29個まで』とした。
条件としては変わっていない。30個未満は29個以下であるからだ。
そして2つだった条件が1つにまとめられている。
実行計画の比較
では、変更したSQLとコストを見てみよう。
-- 販売個数の条件を[>= AND <]から[BETWEEN]に変更
SELECT COUNT(*) FROM 売上テーブル
WHERE 製品 = '商品B'
AND 販売個数 BETWEEN 20 AND 29 -- ←ここが変更箇所
AND (営業店 = '大阪' OR 営業店 = '名古屋')
;
QUERY PLAN |
---|
Aggregate (cost=31.83..31.84 rows=1 width=8) |
-> Seq Scan on "売上テーブル" (cost=0.00..31.82 rows=1 width=0) |
Filter: (("販売個数" >= 20) AND ("販売個数" <= 29) AND (("製品")::text = '商品B'::text) AND ((("営業店")::text = '大阪'::text) OR (("営業店")::text = '名古屋'::text))) |
ほとんど変わってないw
今回はデータ件数が12件と少ないので、差が感じられないのかもしれない。
件数が多かったり、他に影響する条件が絡むと差が出るのではないかとは思う。
よって、「確実にどちらが早い」とは言い切れない。
条件3:営業店が『大阪』もしくは『名古屋』
問題文では、営業店の条件にORを使い『大阪』もしくは『名古屋』どちらかに該当することとしている。
営業店 = '大阪' OR 営業店 = '名古屋'
では、これを変更してみよう。
営業店 IN ('大阪','名古屋')
ここではINを使っているが、条件としては変わっていない。
要はカッコの中のいずれかに当てはまるものであるからだ。
実行計画の比較
では、これも変更したSQLとコストを見てみよう。
なお、条件1は元に戻してあるので純粋に条件2だけの変更である。
-- 営業店の条件を[OR]から[IN]に変更
SELECT COUNT(*) FROM 売上テーブル
WHERE 製品 = '商品B'
AND (販売個数 >= 20 AND 販売個数 < 30)
AND 営業店 IN ('大阪','名古屋') -- ←ここが変更箇所
;
QUERY PLAN |
---|
Aggregate (cost=29.40..29.41 rows=1 width=8) |
-> Seq Scan on "売上テーブル" (cost=0.00..29.40 rows=1 width=0) |
Filter: (("販売個数" >= 20) AND ("販売個数" < 30) AND (("営業店")::text = ANY ('{大阪,名古屋}'::text[])) AND (("製品")::text = '商品B'::text)) |
ちょっと早くなった!
31.8 → 29.4
ちょっとだけなのは、件数が12件しかないからだと思いたいところである(汗)
条件2と条件3の両方を変更
先程までの変更を両方組み合わせたらどうなるか?
やってみよう。
SELECT COUNT(*) FROM 売上テーブル
WHERE 製品 = '商品B'
AND 販売個数 BETWEEN 20 AND 29 -- ←ここが変更箇所
AND 営業店 IN ('大阪','名古屋') -- ←ここが変更箇所
;
QUERY PLAN |
---|
Aggregate (cost=29.40..29.41 rows=1 width=8) |
-> Seq Scan on "売上テーブル" (cost=0.00..29.40 rows=1 width=0) |
Filter: (("販売個数" >= 20) AND ("販売個数" <= 29) AND (("営業店")::text = ANY ('{大阪,名古屋}'::text[])) AND (("製品")::text = '商品B'::text)) |
条件3の時と同じである。
条件2で変わらなかったのだから、当然ではあるが。
組合せ効果で変わることを期待したのだが、期待外れということでw
条件以外は変えるところ無いの?
そもそも単純なSQLなので、これ以上は思いつかないのだが。
唯一あるとすれば、COUNTだろうか?
SELECT COUNT(*) FROM 売上テーブル
このように 『*』 を『1』に変えると早くなるとかならないとか論争を見たことがあるので、試しに変えてみる。
SELECT COUNT(1) FROM 売上テーブル
実行計画の比較
では、これもコストを見てみよう。
まずは条件を変更していないバージョンである。
SELECT COUNT(1) FROM 売上テーブル
WHERE 製品 = '商品B'
AND (販売個数 >= 20 AND 販売個数 < 30)
AND (営業店 = '大阪' OR 営業店 = '名古屋')
;
QUERY PLAN |
---|
Aggregate (cost=31.83..31.84 rows=1 width=8) |
-> Seq Scan on "売上テーブル" (cost=0.00..31.82 rows=1 width=0) |
Filter: (("販売個数" >= 20) AND ("販売個数" < 30) AND (("製品")::text = '商品B'::text) AND ((("営業店")::text = '大阪'::text) OR (("営業店")::text = '名古屋'::text))) |
変わらない(31.8)
続けて、条件2と条件3を変更したバージョンである。
SELECT COUNT(1) FROM 売上テーブル
WHERE 製品 = '商品B'
AND 販売個数 BETWEEN 20 AND 29
AND 営業店 IN ('大阪','名古屋')
;
QUERY PLAN |
---|
Aggregate (cost=29.40..29.41 rows=1 width=8) |
-> Seq Scan on "売上テーブル" (cost=0.00..29.40 rows=1 width=0) |
Filter: (("販売個数" >= 20) AND ("販売個数" <= 29) AND (("営業店")::text = ANY ('{大阪,名古屋}'::text[])) AND (("製品")::text = '商品B'::text)) |
まあ、条件だけを変えた時と同じ(29.4)
ちなみに 『*』 と『1』の違いはNULLの判断ロジックの有無だそうだ。
今回のデータでは、あまり関係なさそうな感じか?
最後に
ということで、今回は簡単なチューニングについて考えてみた。
この企画をやろうと思ったきっかけは、DS検定の勉強をしていた時に
「自分ならこう書くかな」などと考え、検証したくなったのである。
私がパッと思いつく限りでは、今回の程度となる。
それでも、やってみようとなること自体が面白いと私は思う。
それでは、今回は以上にて締めようと思う。
ここまで読んで頂き、多謝!!m(_ _)m
Discussion