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
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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)
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

27. RDBMSの基本的な仕組み
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

50. RDBMSの基本的な仕組み
    

    View Slide

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

    View Slide

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

    View Slide

53. INDEXとJOIN
    B+Tree INDEX
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

68. INDEXとJOIN
    Nested Loop Joinは掛け算
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide