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

Laravel JP Conference 2019の登壇資料です
https://conference2019.laravel.jp/

■資料内リンク
- https://www.amazon.co.jp/exec/obidos/ASIN/4297104083/maple036-22/
- https://speakerdeck.com/soudai/shi-xing-ji-hua-falsehua
- https://sql-performance-explained.jp/
- http://soudai1025.blogspot.com/2016/11/rdbantipattern1.html
- https://twitter.com/ikkitang/status/1092046172200390656
- https://twitter.com/ikkitang/status/1092046332825456640
- https://twitter.com/ikkitang/status/1092046475092054019

Transcript

1. Webアプリケーションが今こそ知るべき、 RDBMSのパフォーマンスチューニングの勘所 ～ 未踏の速度を目指して～ Laravel JP Conference 2019
2. What is it? Laravelで開発速度は超速！！ ではアプリケーションの実行速度は？
3. 現場でよく見る話 • キャッシュが効かないところが重い • 夜間の集計batchが終わらない • 特定のページがめちゃめちゃ重い • ユーザが増えて、急に重くなった …etc
4. What is it? 開発速度と実行速度 どちらも同じくらい大切
5. What is it? パフォーマンスの問題の原因が RDBMSのこと多くないですか？
6. What is it? 今日はRDBMSに 敢えてフォーカスして話します
7. Webサービスを見る クライアント インターネット サーバサイド 通信は常に往来してる DNS BGP ISP サーバ ネットワーク アプリケーション ここが今日の対象
8. RDBMS 一般的なWebアプリケーション アプリケーション ブラウザ Laravel
9. Laravel RDBMS 一般的なWebアプリケーション アプリケーション ブラウザ ここが今日の対象
10. あじぇんだ 1 自己紹介 2 RDBMSの基本的な仕組み 3 INDEXとJOIN 4 フレームワーク依存症 5 まとめ
11. あじぇんだ 1 自己紹介 2 RDBMSの基本的な仕組み 3 INDEXとJOIN 4 フレームワーク依存症 5 まとめ
12. 自己紹介 曽根 壮大（34歳) 株式会社オミカレ 副社長/CTO • 日本PostgreSQLユーザ会 勉強会分科会 座長 • 3人の子供がいます • 技術的にはWeb/LL言語/RDBが好きです そ ね た け と も
13. 自己紹介 曽根 壮大（34歳) 株式会社オミカレ 副社長/CTO • 日本PostgreSQLユーザ会 勉強会分科会 座長 • 3人の子供がいます • 技術的にはWeb/LL言語/RDBが好きです そ ね た け と も
14. 婚活といえばオミカレ https://party-calendar.net/
15. 本を書きました https://www.amazon.co.jp/exec/obidos/ASIN/4297104083/maple036-22/
16. あじぇんだ 1 自己紹介 2 RDBMSの基本的な仕組み 3 INDEXとJOIN 4 フレームワーク依存症 5 まとめ
17. RDBMSの基本的な仕組み まずはRDBMSを知る
18. RDBMSの基本的な仕組み SQLを実行するとき
19. • パーサ • リライタ • プランナ • オプティマイザ エグゼキュータ データ クライアント ① SQLを実行する ②SQLの構文を解析 構文木の作成を行う ③最適な実行計画を生成 INDEXの利用の有無、 JOINのアルゴリズムなどを決める ④実行計画に沿ってクエリを 実行し、データを取得 ⑤取得した結果を クライアントに返す
20. RDBMSの基本的な仕組み 大体遅いのはエグゼキュータ
21. RDBMSの基本的な仕組み 大体遅いのはエグゼキュータ ↓ エグゼキュータの振る舞いは 実行計画で決まる
22. 実行計画 -- MySQL mysql> EXPLAIN SELECT * FROM demo WHERE id = 100; +----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | demo | NULL | const | id | id | 8 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) -- PostgreSQL postgres=# EXPLAIN SELECT * FROM demo WHERE id = 100; QUERY PLAN ----------------------------------------------------- Seq Scan on demo (cost=0.00..2.40 rows=1 width=38) Filter: (id = 100) (2 rows)
23. RDBMSの基本的な仕組み https://speakerdeck.com/soudai/shi-xing-ji-hua-falsehua 今日はEXPLAINの話はしません この資料でまとめてます
24. RDBMSの基本的な仕組み エグゼキュータが遅いとき
25. RDBMSの基本的な仕組み エグゼキュータが遅いとき ↓ RDBMSが苦手な実行計画になっている
26. RDBMSの基本的な仕組み RDBMSが苦手なこと ↓ リレーショナルモデルじゃないとき
27. RDBMSの基本的な仕組み
28. RDBMSの基本的な仕組み 苦手じゃないけど遅いとき ↓ 正しくRDBMSを活用出来てない
29. RDBMSを活用出来てない例 • INDEXを利用できていない • 不要で大きなデータを取得してる • 複数回クエリを実行している（N+1問題） • テーブル設計が悪い …etc
30. RDBMSを活用出来てない例 • INDEXを利用できていない • 不要で大きなデータを取得してる • 複数回クエリを実行している（N+1問題） • テーブル設計が悪い …etc 次の章で話をします
31. RDBMSを活用出来てない例 • INDEXを利用できていない • 不要で大きなデータを取得してる • 複数回クエリを実行している（N+1問題） • テーブル設計が悪い …etc
32. 不要で大きなデータの取得例 記事id タイトル 記事 1 Laravelカンファレンス登壇 とても多い記事の詳細 2 失敗から学ぶRDBの正しい歩き方 だだぢぢづづででどどーん！！ ︙ ︙ ︙
33. 不要で大きなデータの取得例 記事一覧 1. Laravelカンファレンス登壇 2. 失敗から学ぶRDBの正しい歩き ︙ 1 2 3 4 5 … 次へ データ SELECT * FROM 記事 ORDER BY id LIMIT 10 OFFSET 1; ブログid タイトル 記事 クエリの結果イメージ 記事データを使っていないのに 取得している
34. 不要で大きなデータの取得例 記事一覧 1. Laravelカンファレンス登壇 2. 失敗から学ぶRDBの正しい歩き ︙ 1 2 3 4 5 … 次へ データ SELECT ブログid,タイトル FROM 記事 ORDER BY id LIMIT 10 OFFSET 1; ブログid タイトル クエリの結果イメージ 不要なデータを削除して小さく
35. RDBMSの基本的な仕組み SQLアンチパターンの インプリシットカラムの1例
36. RDBMSの基本的な仕組み クエリで扱うデータを小さくする
37. 取得するデータを小さくする • WHERE句で小さくする • FROM句で小さくする • 不要なJOIN句を無くす …etc
38. RDBMSを活用出来てない例 • INDEXを利用できていない • 不要で大きなデータを取得してる • 複数回クエリを実行している（N+1問題） • テーブル設計が悪い …etc
39. N+1問題 foreach ($blogs as $id => $blog) { echo $blog->get(); } もしget()の度にSQLが発行されたら？
40. RDBMSの基本的な仕組み ORMやクエリビルダが 実行するクエリを知ることが大事
41. RDBMSを活用出来てない例 • INDEXを利用できていない • 不要で大きなデータを取得してる • 複数回クエリを実行している（N+1問題） • テーブル設計が悪い …etc
42. RDBMSの基本的な仕組み データ設計は積み木
43. RDBMSの基本的な仕組み データベース設計は積み木
44. RDBMSの基本的な仕組み しっかりと練られた初期設計 検討された仕様追加 1 検討された仕様追加 2 正しく積み上げて行ける
45. RDBMSの基本的な仕組み 初期設計
46. RDBMSの基本的な仕組み 初期設計 マジカルな初期設計を すると仕様変更が出来ない
47. RDBMSの基本的な仕組み 初期設計 仕様追加
48. RDBMSの基本的な仕組み 初期設計 仕様追加 次の仕様追加は どうにも出来ない データを取り出したり、 保存することもままならない
49. RDBMSの基本的な仕組み RDBMSに適した設計が必要
50. RDBMSの基本的な仕組み
51. RDBMSの基本的な仕組み RDBMSに適した設計が必要 ↓ 苦手なことはNoSQLを検討する
52. あじぇんだ 1 自己紹介 2 RDBMSの基本的な仕組み 3 INDEXとJOIN 4 フレームワーク依存症 5 まとめ
53. INDEXとJOIN B+Tree INDEX
54. 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
55. 200番 199番 201番 10 テーブルスキャン 1回で全行を取得 300ブロックの表なら、当然300ブロック取得 ＝シーケンシャルI/O 1番 300番 9 … …
56. INDEXを利用した場合 表の1ブロックを利用するために 最低4ブロック（①～④）取得 ＝ランダムI/O ~5000 200番 ブロック 199番 ブロック 201番 ブロック 10 9 ① ② ③ ④
57. INDEXとJOIN INDEXを利用できると 200 → 4 で50倍の効果
58. 設定したINDEXが効かない • 検索結果が多い、全体の件数が少ない • 条件にその列を使っていない • カーディナリティの低い列に対する検索 • あいまいな検索 • 統計情報と実際のテーブルで乖離がある場合 …etc
59. INDEXの仕組み https://sql-performance-explained.jp/
60. JOINの種類と仕組み JOINは集合の組み合わせ
61. 都道府県 会員 FULL OUTER JOIN 都道府県 会員 LEFT OUTER JOIN 都道府県 会員 RIGHT OUTER JOIN 都道府県 会員 INNER JOIN
62. JOINの種類と仕組み JOINのアルゴリズム
63. Nested Loop Join https://twitter.com/ikkitang/status/1092046172200390656
64. Hash Join https://twitter.com/ikkitang/status/1092046332825456640
65. Merge Join https://twitter.com/ikkitang/status/1092046475092054019
66. INDEXとJOIN みんなが大体使うのはNLJ なぜならMySQLはこれしかないから
67. Nested Loop Joinの特性 • 内部表の結合キーの列に利用できるINDEXがある場合、 ループ数を省略できるため外部表が小さいほど高速になる • 内部表の結合キーが一意の場合は内部表対象レコードを 絞りこめるため、より高速になる • 1レコードずつ確定するので、確定したレコードは レスポンスとして返すことができる
68. INDEXとJOIN Nested Loop Joinは掛け算
69. INDEXとJOIN Nested Loop Joinは掛け算 ↓ 1,000行と1,000行のJOIN=1,000,000行
70. INDEXとJOIN 適切なINDEXがある場合 1,000行 +（1,000×1）行となり 2,000行相当
71. INDEXとJOIN JOINはハイコストな処理だが INDEXと合わせて有効に使えば高速になる
72. あじぇんだ 1 自己紹介 2 RDBMSの基本的な仕組み 3 INDEXとJOIN 4 フレームワーク依存症 5 まとめ
73. フレームワーク依存症 基本的なSQLの話をしてきました
74. フレームワーク依存症 ここまでの話を フレームワークを使ってるときに 意識してますか？
75. RDBMS Model フレームワーク View 情報をやり取りする Modelが事実を加工し、 情報に変更する 事実をやり取りする SQLでやり取りする プログラミング言語 でやり取りする ORM リレーショナルモデルをプログラミング言語が 扱うオブジェクトモデルに変換する またはその逆を行う
76. フレームワーク依存症 ORMは漏れのある抽象化
77. http://soudai1025.blogspot.com/2016/11/rdbantipattern1.html
78. フレームワーク依存症 フレームワークは 強い制約と規約によって 高い生産性を与えてくれる
79. フレームワーク依存症 SQLアンチパターンに出てくる フレームワーク依存の話
80. フレームワーク依存症 • マジックビーンズ • ポリモーフィック関連 • IDリクワイアド • キーレスエントリー
81. フレームワーク依存症 これらの話は今日はしません
82. フレームワーク依存症 ORMもフレームワークも絶対悪では無い
83. フレームワーク依存症 ORMもフレームワークも絶対悪では無い ↓ 漏れのある抽象化と付き合う よりよい抽象化を目指す
84. フレームワーク依存症 例えばRepositoryパターン
85. RDBMS View 会員ページ 会員テーブル 会員サービス 会員データ Model ORM SQL 会員リポジトリ サービスに必要な ビジネスロジック キャッシュ ストレージ フレームワーク データクラスはサービスが必要な会員のデー タを取り出し、加工する。 場合によってはキャッシュから取り出したり、 RDBMSから取り出したりする リポジトリクラスはデータのCRUD部分だけを 担う。 ORM経由でもSQLでもサービスクラスからは 関係ない
86. フレームワーク依存症 フレームワークに依存しない 最適な設計を常に模索する
87. あじぇんだ 1 自己紹介 2 RDBMSの基本的な仕組み 3 INDEXとJOIN 4 フレームワーク依存症 5 まとめ
88. まとめ Laravelは素晴らしいフレームワーク
89. まとめ イチローのバットを使っても イチローにはなれない
90. まとめ 抽象化されている箇所については 忘れないこと
91. まとめ ときにはRDBMSだったり ときにはHTTPプロトコルだったり
92. まとめ 仕組みには先人の知恵があり 抽象化には理由がある
93. まとめ 仕組み（フレームワーク）を活用するには 先人の歴史を知ることも大切
94. まとめ 使い方だけに囚われずに本質を知り より良いソフトウェアを生み出しましょう
95. ご清聴ありがとうございました