Webアプリケーションが今こそ知るべき、 RDBMSのパフォーマンスチューニングの勘所 / Basic-of-RDB

by soudai sone

Slide 1

Slide 1 text

Webアプリケーションが今こそ知るべき、 RDBMSのパフォーマンスチューニングの勘所～未踏の速度を目指して～ Laravel JP Conference 2019

Slide 2

Slide 2 text

What is it? Laravelで開発速度は超速！！ではアプリケーションの実行速度は？

Slide 3

Slide 3 text

現場でよく見る話 • キャッシュが効かないところが重い • 夜間の集計batchが終わらない • 特定のページがめちゃめちゃ重い • ユーザが増えて、急に重くなった …etc

Slide 4

Slide 4 text

What is it? 開発速度と実行速度どちらも同じくらい大切

Slide 5

Slide 5 text

What is it? パフォーマンスの問題の原因が RDBMSのこと多くないですか？

Slide 6

Slide 6 text

What is it? 今日はRDBMSに敢えてフォーカスして話します

Slide 7

Slide 7 text

Webサービスを見るクライアントインターネットサーバサイド通信は常に往来してる DNS BGP ISP サーバネットワークアプリケーションここが今日の対象

Slide 8

Slide 8 text

RDBMS 一般的なWebアプリケーションアプリケーションブラウザ Laravel

Slide 9

Slide 9 text

Laravel RDBMS 一般的なWebアプリケーションアプリケーションブラウザここが今日の対象

Slide 10

Slide 10 text

あじぇんだ 1 自己紹介 2 RDBMSの基本的な仕組み 3 INDEXとJOIN 4 フレームワーク依存症 5 まとめ

Slide 11

Slide 11 text

あじぇんだ 1 自己紹介 2 RDBMSの基本的な仕組み 3 INDEXとJOIN 4 フレームワーク依存症 5 まとめ

Slide 12

Slide 12 text

自己紹介曽根壮大（34歳) 株式会社オミカレ副社長/CTO • 日本PostgreSQLユーザ会勉強会分科会座長 • 3人の子供がいます • 技術的にはWeb/LL言語/RDBが好きですそねたけとも

Slide 13

Slide 13 text

Slide 14

Slide 14 text

婚活といえばオミカレ https://party-calendar.net/

Slide 15

Slide 15 text

本を書きました https://www.amazon.co.jp/exec/obidos/ASIN/4297104083/maple036-22/

Slide 16

Slide 16 text

あじぇんだ 1 自己紹介 2 RDBMSの基本的な仕組み 3 INDEXとJOIN 4 フレームワーク依存症 5 まとめ

Slide 17

Slide 17 text

RDBMSの基本的な仕組みまずはRDBMSを知る

Slide 18

Slide 18 text

RDBMSの基本的な仕組み SQLを実行するとき

Slide 19

Slide 19 text

• パーサ • リライタ • プランナ • オプティマイザエグゼキュータデータクライアント ① SQLを実行する ②SQLの構文を解析構文木の作成を行う ③最適な実行計画を生成 INDEXの利用の有無、 JOINのアルゴリズムなどを決める ④実行計画に沿ってクエリを実行し、データを取得 ⑤取得した結果をクライアントに返す

Slide 20

Slide 20 text

RDBMSの基本的な仕組み大体遅いのはエグゼキュータ

Slide 21

Slide 21 text

RDBMSの基本的な仕組み大体遅いのはエグゼキュータ ↓ エグゼキュータの振る舞いは実行計画で決まる

Slide 22

Slide 22 text

Slide 23

Slide 23 text

RDBMSの基本的な仕組み https://speakerdeck.com/soudai/shi-xing-ji-hua-falsehua 今日はEXPLAINの話はしませんこの資料でまとめてます

Slide 24

Slide 24 text

RDBMSの基本的な仕組みエグゼキュータが遅いとき

Slide 25

Slide 25 text

RDBMSの基本的な仕組みエグゼキュータが遅いとき ↓ RDBMSが苦手な実行計画になっている

Slide 26

Slide 26 text

RDBMSの基本的な仕組み RDBMSが苦手なこと ↓ リレーショナルモデルじゃないとき

Slide 27

Slide 27 text

RDBMSの基本的な仕組み

Slide 28

Slide 28 text

RDBMSの基本的な仕組み苦手じゃないけど遅いとき ↓ 正しくRDBMSを活用出来てない

Slide 29

Slide 29 text

RDBMSを活用出来てない例 • INDEXを利用できていない • 不要で大きなデータを取得してる • 複数回クエリを実行している（N+1問題） • テーブル設計が悪い …etc

Slide 30

Slide 30 text

RDBMSを活用出来てない例 • INDEXを利用できていない • 不要で大きなデータを取得してる • 複数回クエリを実行している（N+1問題） • テーブル設計が悪い …etc 次の章で話をします

Slide 31

Slide 31 text

Slide 32

Slide 32 text

不要で大きなデータの取得例記事id タイトル記事 1 Laravelカンファレンス登壇とても多い記事の詳細 2 失敗から学ぶRDBの正しい歩き方だだぢぢづづででどどーん！！ ︙ ︙ ︙

Slide 33

Slide 33 text

不要で大きなデータの取得例記事一覧 1. Laravelカンファレンス登壇 2. 失敗から学ぶRDBの正しい歩き ︙ 1 2 3 4 5 … 次へデータ SELECT * FROM 記事 ORDER BY id LIMIT 10 OFFSET 1; ブログid タイトル記事クエリの結果イメージ記事データを使っていないのに取得している

Slide 34

Slide 34 text

不要で大きなデータの取得例記事一覧 1. Laravelカンファレンス登壇 2. 失敗から学ぶRDBの正しい歩き ︙ 1 2 3 4 5 … 次へデータ SELECT ブログid,タイトル FROM 記事 ORDER BY id LIMIT 10 OFFSET 1; ブログid タイトルクエリの結果イメージ不要なデータを削除して小さく

Slide 35

Slide 35 text

RDBMSの基本的な仕組み SQLアンチパターンのインプリシットカラムの1例

Slide 36

Slide 36 text

RDBMSの基本的な仕組みクエリで扱うデータを小さくする

Slide 37

Slide 37 text

取得するデータを小さくする • WHERE句で小さくする • FROM句で小さくする • 不要なJOIN句を無くす …etc

Slide 38

Slide 38 text

Slide 39

Slide 39 text

N+1問題 foreach ($blogs as $id => $blog) { echo $blog->get(); } もしget()の度にSQLが発行されたら？

Slide 40

Slide 40 text

RDBMSの基本的な仕組み ORMやクエリビルダが実行するクエリを知ることが大事

Slide 41

Slide 41 text

Slide 42

Slide 42 text

RDBMSの基本的な仕組みデータ設計は積み木

Slide 43

Slide 43 text

RDBMSの基本的な仕組みデータベース設計は積み木

Slide 44

Slide 44 text

RDBMSの基本的な仕組みしっかりと練られた初期設計検討された仕様追加 1 検討された仕様追加 2 正しく積み上げて行ける

Slide 45

Slide 45 text

RDBMSの基本的な仕組み初期設計

Slide 46

Slide 46 text

RDBMSの基本的な仕組み初期設計マジカルな初期設計をすると仕様変更が出来ない

Slide 47

Slide 47 text

RDBMSの基本的な仕組み初期設計仕様追加

Slide 48

Slide 48 text

RDBMSの基本的な仕組み初期設計仕様追加次の仕様追加はどうにも出来ないデータを取り出したり、保存することもままならない

Slide 49

Slide 49 text

RDBMSの基本的な仕組み RDBMSに適した設計が必要

Slide 50

Slide 50 text

RDBMSの基本的な仕組み

Slide 51

Slide 51 text

RDBMSの基本的な仕組み RDBMSに適した設計が必要 ↓ 苦手なことはNoSQLを検討する

Slide 52

Slide 52 text

あじぇんだ 1 自己紹介 2 RDBMSの基本的な仕組み 3 INDEXとJOIN 4 フレームワーク依存症 5 まとめ

Slide 53

Slide 53 text

INDEXとJOIN B+Tree INDEX

Slide 54

Slide 54 text

B+Tree INDEXの仕組み WHERE user_id = 4000 ~5000 ～10000 ~2500 ~5000 ~7500 ~10000 索引ブロックヘッダ user_id = 2501 (ブロックID,行ID)=(100,1) user_id = 2502 (ブロックID,行ID)=(100,2) user_id = 4000 (ブロックID,行ID)=(200,10) ： user_id = 4001 (ブロックID,行ID)=(201,1) user_id = 5000 (ブロックID,行ID)=(300,5) ： 201番ブロック 199番ブロック 200番ブロック表データ 10 9

Slide 55

Slide 55 text

200番 199番 201番 10 テーブルスキャン 1回で全行を取得 300ブロックの表なら、当然300ブロック取得＝シーケンシャルI/O 1番 300番 9 … …

Slide 56

Slide 56 text

INDEXを利用した場合表の1ブロックを利用するために最低4ブロック（①～④）取得＝ランダムI/O ~5000 200番ブロック 199番ブロック 201番ブロック 10 9 ① ② ③ ④

Slide 57

Slide 57 text

INDEXとJOIN INDEXを利用できると 200 → 4 で50倍の効果

Slide 58

Slide 58 text

設定したINDEXが効かない • 検索結果が多い、全体の件数が少ない • 条件にその列を使っていない • カーディナリティの低い列に対する検索 • あいまいな検索 • 統計情報と実際のテーブルで乖離がある場合 …etc

Slide 59

Slide 59 text

INDEXの仕組み https://sql-performance-explained.jp/

Slide 60

Slide 60 text

JOINの種類と仕組み JOINは集合の組み合わせ

Slide 61

Slide 61 text

都道府県会員 FULL OUTER JOIN 都道府県会員 LEFT OUTER JOIN 都道府県会員 RIGHT OUTER JOIN 都道府県会員 INNER JOIN

Slide 62

Slide 62 text

JOINの種類と仕組み JOINのアルゴリズム

Slide 63

Slide 63 text

Nested Loop Join https://twitter.com/ikkitang/status/1092046172200390656

Slide 64

Slide 64 text

Hash Join https://twitter.com/ikkitang/status/1092046332825456640

Slide 65

Slide 65 text

Merge Join https://twitter.com/ikkitang/status/1092046475092054019

Slide 66

Slide 66 text

INDEXとJOIN みんなが大体使うのはNLJ なぜならMySQLはこれしかないから

Slide 67

Slide 67 text

Nested Loop Joinの特性 • 内部表の結合キーの列に利用できるINDEXがある場合、ループ数を省略できるため外部表が小さいほど高速になる • 内部表の結合キーが一意の場合は内部表対象レコードを絞りこめるため、より高速になる • 1レコードずつ確定するので、確定したレコードはレスポンスとして返すことができる

Slide 68

Slide 68 text

INDEXとJOIN Nested Loop Joinは掛け算

Slide 69

Slide 69 text

INDEXとJOIN Nested Loop Joinは掛け算 ↓ 1,000行と1,000行のJOIN=1,000,000行

Slide 70

Slide 70 text

INDEXとJOIN 適切なINDEXがある場合 1,000行 +（1,000×1）行となり 2,000行相当

Slide 71

Slide 71 text

INDEXとJOIN JOINはハイコストな処理だが INDEXと合わせて有効に使えば高速になる

Slide 72

Slide 72 text

あじぇんだ 1 自己紹介 2 RDBMSの基本的な仕組み 3 INDEXとJOIN 4 フレームワーク依存症 5 まとめ

Slide 73

Slide 73 text

フレームワーク依存症基本的なSQLの話をしてきました

Slide 74

Slide 74 text

フレームワーク依存症ここまでの話をフレームワークを使ってるときに意識してますか？

Slide 75

Slide 75 text

RDBMS Model フレームワーク View 情報をやり取りする Modelが事実を加工し、情報に変更する事実をやり取りする SQLでやり取りするプログラミング言語でやり取りする ORM リレーショナルモデルをプログラミング言語が扱うオブジェクトモデルに変換するまたはその逆を行う

Slide 76

Slide 76 text

フレームワーク依存症 ORMは漏れのある抽象化

Slide 77

Slide 77 text

http://soudai1025.blogspot.com/2016/11/rdbantipattern1.html

Slide 78

Slide 78 text

フレームワーク依存症フレームワークは強い制約と規約によって高い生産性を与えてくれる

Slide 79

Slide 79 text

フレームワーク依存症 SQLアンチパターンに出てくるフレームワーク依存の話

Slide 80

Slide 80 text

フレームワーク依存症 • マジックビーンズ • ポリモーフィック関連 • IDリクワイアド • キーレスエントリー

Slide 81

Slide 81 text

フレームワーク依存症これらの話は今日はしません

Slide 82

Slide 82 text

フレームワーク依存症 ORMもフレームワークも絶対悪では無い

Slide 83

Slide 83 text

フレームワーク依存症 ORMもフレームワークも絶対悪では無い ↓ 漏れのある抽象化と付き合うよりよい抽象化を目指す

Slide 84

Slide 84 text

フレームワーク依存症例えばRepositoryパターン

Slide 85

Slide 85 text

RDBMS View 会員ページ会員テーブル会員サービス会員データ Model ORM SQL 会員リポジトリサービスに必要なビジネスロジックキャッシュストレージフレームワークデータクラスはサービスが必要な会員のデータを取り出し、加工する。場合によってはキャッシュから取り出したり、 RDBMSから取り出したりするリポジトリクラスはデータのCRUD部分だけを担う。 ORM経由でもSQLでもサービスクラスからは関係ない

Slide 86

Slide 86 text

フレームワーク依存症フレームワークに依存しない最適な設計を常に模索する

Slide 87

Slide 87 text

あじぇんだ 1 自己紹介 2 RDBMSの基本的な仕組み 3 INDEXとJOIN 4 フレームワーク依存症 5 まとめ

Slide 88

Slide 88 text

まとめ Laravelは素晴らしいフレームワーク

Slide 89

Slide 89 text

まとめイチローのバットを使ってもイチローにはなれない

Slide 90

Slide 90 text

まとめ抽象化されている箇所については忘れないこと

Slide 91

Slide 91 text

まとめときにはRDBMSだったりときにはHTTPプロトコルだったり

Slide 92

Slide 92 text

まとめ仕組みには先人の知恵があり抽象化には理由がある

Slide 93

Slide 93 text

まとめ仕組み（フレームワーク）を活用するには先人の歴史を知ることも大切

Slide 94

Slide 94 text

まとめ使い方だけに囚われずに本質を知りより良いソフトウェアを生み出しましょう

Slide 95

Slide 95 text

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