そのSQL、もっと速くなりますよ。

Slide 1

Slide 1 text

そのSQL、もっと速くなりますよ。  長尾和昌   2022.02.15 FORCIA Meetup #4 

Slide 2

Slide 2 text

自己紹介  ● 長尾和昌 (Kazumasa Nagao)  ● ソフトウェアエンジニア  旅行系アプリケーションの開発・保守  ● 職歴  某電力会社の営業・経理 → プログラミング独学  → 起業 → 資金が底をつく → 就職  2

Slide 3

Slide 3 text

用語解説（バッチ処理とは）  ● オンライン処理のSQL  エンドユーザーからのリクエストに応じて結果を返す。  数秒で完了する（べき）もの。    ● バッチ処理のSQL  最新の料金在庫や商品情報などを顧客DBからコピーして  検索に必要な各種テーブルを作成する集中処理。  数十分で終わるものから数時間、なかには10時間を超えるものも。  3

Slide 4

Slide 4 text

バッチ処理のSQLの高速化に取り組む  4アプリケーションで計11時間短縮しました。  26.5時間 → 15.5時間（4アプリの合計）  4

Slide 5

Slide 5 text

高速化のノウハウを  4つ紹介させていただきます！  5

Slide 6

Slide 6 text

Case1. 1億レコードの超巨大テーブル  6

Slide 7

Slide 7 text

Case1. 1億レコードの超巨大テーブル  特に時間がかかっている処理をいくつか  調べたところ共通点が見つかりました。  7

Slide 8

Slide 8 text

Case1. 1億レコードの超巨大テーブル  いつも同じテーブルが登場する。  8

Slide 9

Slide 9 text

Case1. 1億レコードの超巨大テーブル  なんだこのテーブルは。  9

Slide 10

Slide 10 text

Case1. 1億レコードの超巨大テーブル  SELECT count(*) FROM hoge1; -[ RECORD 1 ]- count | 100000000 10

Slide 11

Slide 11 text

Case1. 1億レコードの超巨大テーブル  でかい。  11

Slide 12

Slide 12 text

Case1. 1億レコードの超巨大テーブル  これを見て疑問に思いました。  12

Slide 13

Slide 13 text

Case1. 1億レコードの超巨大テーブル  このレコード、本当に全部使われているの？  13

Slide 14

Slide 14 text

Case1. 1億レコードの超巨大テーブル  SQLをじっと眺めていると  さらに共通点が見つかりました。  14

Slide 15

Slide 15 text

Case1. 1億レコードの超巨大テーブル  hoge1 が使われるときは  必ず hoge2 もINNER JOIN されている。  （部屋IDにてJOIN）  15

Slide 16

Slide 16 text

Case1. 1億レコードの超巨大テーブル  つまり hoge2 に存在しない部屋IDは  絶対に使われることがない。  ……と、いうことで試してみました。  16

Slide 17

Slide 17 text

Case1. 1億レコードの超巨大テーブル  SELECT count(*) FROM hoge1 INNER JOIN hoge2 using (部屋ID) ; -[ RECORD 1 ]- count | 8000000 17

Slide 18

Slide 18 text

Case1. 1億レコードの超巨大テーブル  めっちゃ減った。  1億 → 800万  18

Slide 19

Slide 19 text

Case1. 1億レコードの超巨大テーブル  hoge1 も hoge2 も顧客DBから取得した  生テーブルだったため、取得のタイミングで  hoge1 に hoge2 をJOINして  不要なデータを落としました。  19

Slide 20

Slide 20 text

Case1. 1億レコードの超巨大テーブル  これにより、hoge1 を使っている全ての  SQLが爆速化しました。  計165分の大幅な短縮となりました。  20

Slide 21

Slide 21 text

Case1. 1億レコードの超巨大テーブル  【まとめ】  そのレコードは本当に使っているのか。  使っていないなら可能な限り上流で落とす。  21

Slide 22

Slide 22 text

Case2. WHERE句から条件を消したい  22

Slide 23

Slide 23 text

Case2. WHERE句から条件を消したい  プレウォームが遅い。100分もかかる。  23

Slide 24

Slide 24 text

Case2. WHERE句から条件を消したい  プレウォームとは  バッチ処理の最後、サービス復帰の直前に  前もってよく投げられるクエリを発行し  結果をキャッシュに載せる。  24

Slide 25

Slide 25 text

Case2. WHERE句から条件を消したい  問題のSQLをいろいろいじっていて  あることに気づきました。  25

Slide 26

Slide 26 text

Case2. WHERE句から条件を消したい  SELECT * FROM hoge1 WHERE 方面 = ‘8’;  「方面=’8’」を消すと爆速化する。  26

Slide 27

Slide 27 text

Case2. WHERE句から条件を消したい  これ消したい。  27

Slide 28

Slide 28 text

Case2. WHERE句から条件を消したい  ただ、必要なので書かれているわけで  消したいからといって消せるもんじゃない。  28

Slide 29

Slide 29 text

Case2. WHERE句から条件を消したい  消したいなあ。でも消せないなあ。  29

Slide 30

Slide 30 text

Case2. WHERE句から条件を消したい  あっ！！！  30

Slide 31

Slide 31 text

Case2. WHERE句から条件を消したい  テーブルのほうを分ければよいのでは？！  31

Slide 32

Slide 32 text

Case2. WHERE句から条件を消したい  方面の数は有限なので  もとのテーブル（hoge1）を  hoge1_1, hoge1_2, hoge1_3 ……  と分割しました。  32

Slide 33

Slide 33 text

Case2. WHERE句から条件を消したい  SELECT * FROM hoge1 WHERE 方面 = ‘8’ ↓ SELECT * FROM hoge1_8; 33

Slide 34

Slide 34 text

Case2. WHERE句から条件を消したい  重かった WHERE がなくなり  プレウォームが100分から15分に短縮。  34

Slide 35

Slide 35 text

Case2. WHERE句から条件を消したい  【まとめ】  WHEREが重くて困ったら  テーブルのほうを分けられるか確認する。  35

Slide 36

Slide 36 text

Case3. ループするSQLにご注意  36

Slide 37

Slide 37 text

Case3. ループするSQLにご注意  こんなSQLがありました。  37

Slide 38

Slide 38 text

Case3. ループするSQLにご注意  38 ５回ループ { CREATE TABLE 新テーブル_{1~5} AS SELECT hoge FROM Aテーブル INNER JOIN Bテーブル INNER JOIN Cテーブル INNER JOIN Dテーブル INNER JOIN Eテーブル_{1~5} ; }

Slide 39

Slide 39 text

Case3. ループするSQLにご注意  ん？なんか無駄じゃないか？  39

Slide 40

Slide 40 text

Case3. ループするSQLにご注意  40 CREATE TEMPORARY TABLE 一時テーブル AS SELECT hoge FROM Aテーブル INNER JOIN Bテーブル INNER JOIN Cテーブル INNER JOIN Dテーブル ; ５回ループ { CREATE TABLE 新テーブル_{1~5} AS SELECT hoge FROM 一時テーブル INNER JOIN Eテーブル_{1~5} ; }

Slide 41

Slide 41 text

Case3. ループするSQLにご注意  これでB~DテーブルをJOINする回数が  5回から1回に減りました。  処理時間も当然ながら1/5ほどになりました。  41

Slide 42

Slide 42 text

Case3. ループするSQLにご注意  【まとめ】  ループさせるときは  本当にループすべきもの以外は  一時テーブルとして外だしする。  42

Slide 43

Slide 43 text

Case4. 理解不能な激ムズSQLでも速くしたい  43

Slide 44

Slide 44 text

Case4. 理解不能な激ムズSQLでも速くしたい  なんだこのSQLは……読めん。  44

Slide 45

Slide 45 text

Case4. 理解不能な激ムズSQLでも速くしたい  45 SELECT 超複雑な処理 FROM ( SELECT 超複雑な処理 FROM ( SELECT 超複雑な処理 FROM ( -- かろうじて理解できた部分、ここから SELECT hoge FROM Aテーブル INNER JOIN Bテーブル INNER JOIN Cテーブル INNER JOIN Dテーブル INNER JOIN Eテーブル -- かろうじて理解できた部分、ここまで ) ) );

Slide 46

Slide 46 text

Case4. 理解不能な激ムズSQLでも速くしたい  階層深いし、ループしてるし  SELECTのなかに独自実装の関数がいっぱい。  46

Slide 47

Slide 47 text

Case4. 理解不能な激ムズSQLでも速くしたい  しかも処理が遅いのは  まさにその超複雑な箇所でした。  47

Slide 48

Slide 48 text

Case4. 理解不能な激ムズSQLでも速くしたい  この超複雑な箇所を直接触らずに  しかもその箇所の速度を上げたい。  でも、そんなうまい話あるわけ……  48

Slide 49

Slide 49 text

Case4. 理解不能な激ムズSQLでも速くしたい  ANALYZE !!  49

Slide 50

Slide 50 text

Case4. 理解不能な激ムズSQLでも速くしたい  かろうじて理解できた箇所を外だしして  ANALYZEをかけた状態で  超複雑な処理を迎えるようにすれば……  50

Slide 51

Slide 51 text

Case4. 理解不能な激ムズSQLでも速くしたい  51 CREATE TEMPORARY TABLE 一時テーブル AS SELECT hoge FROM Aテーブル INNER JOIN Bテーブル INNER JOIN Cテーブル INNER JOIN Dテーブル INNER JOIN Eテーブル ; ANALYZE 一時テーブル; SELECT 超複雑な処理 FROM ( SELECT 超複雑な処理 FROM ( SELECT 超複雑な処理 FROM 一時テーブル ) );

Slide 52

Slide 52 text

Case4. 理解不能な激ムズSQLでも速くしたい  たったこれだけで25%の高速化に成功しました。  52

Slide 53

Slide 53 text

Case4. 理解不能な激ムズSQLでも速くしたい  【まとめ】  困ったときのANALYZE  53

Slide 54

Slide 54 text

まとめ 54

Slide 55

Slide 55 text

まとめ  ● Case1. 1億レコードの超巨大テーブル  そのレコードは本当に使っているのか。  使っていないなら可能な限り上流で落とす。  ● Case2. WHERE句から条件を消したい  WHEREが重くて困ったら  テーブルのほうを分けられるか確認する。    55

Slide 56

Slide 56 text

まとめ  ● Case3. ループするSQLにご注意  ループさせるときは  本当にループすべきもの以外は  一時テーブルとして外だしする。  ● Case4. 理解不能な激ムズSQLでも速くしたい  困ったときのANALYZE    56

Slide 57

Slide 57 text

さいごに  今回ご紹介させていただいたノウハウは  いずれもトリッキーな技術ではなく  「なんだそんな単純なことか」  「そんなこと既にできているに決まっている」  と思われるようなものも多々あったかと思います。  57

Slide 58

Slide 58 text

さいごに  ただ、ひとつのSQLが数百行数千行あったとしたら  どうでしょうか。  その中にたった数行問題のある重い処理が存在する  ということは十分にあり得ることだと思います。  そして、それを探し出すのがSQLチューニングの  楽しさかなと思います。  58

Slide 59

Slide 59 text

ご清聴ありがとうございました！  59