Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

soudai sone
February 11, 2019

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

soudai sone

February 11, 2019
Tweet

More Decks by soudai sone

Other Decks in Technology

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