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

88f4e84b94fe07cddbd9e6479d689192?s=47 soudai sone
February 11, 2019

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

88f4e84b94fe07cddbd9e6479d689192?s=128

soudai sone

February 11, 2019
Tweet

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. ご清聴ありがとうございました