$30 off During Our Annual Pro Sale. View Details »

PHPer が知るべき MySQL クエリチューニング/What PHPers Need to Know about MySQL Query Tuning

mamy1326
October 02, 2021

PHPer が知るべき MySQL クエリチューニング/What PHPers Need to Know about MySQL Query Tuning

PHP カンファレンス 2021 10月2日(土) 15:40〜 Track2 でお話ししたスライドです
https://fortee.jp/phpcon-2021/proposal/a795874d-9f0d-48a7-924f-a386bd1cea02

少しずつ加筆修正するかもしれません

ご質問、ご指摘事項は Twitter (https://twitter.com/mamy1326) までいただけたら最高のご褒美です m(__)m

mamy1326

October 02, 2021
Tweet

More Decks by mamy1326

Other Decks in Programming

Transcript

  1. PHPer が知るべき


    まみー (@mamy1326) / Lancers

    View Slide

  2. Twitter


    Work


    Like


    ✔︎
    2017年の趣味:MySQL


    ✔︎
    2018年の趣味:DNS


    ✔︎
    2020年の趣味:CakePHP4


    ✔︎
    2021年の趣味:DBRE
    :@mamy1326(まみー)


    :Lancers,Inc. @ PHPer


    : cune.jp
    自己紹介 2

    View Slide

  3. 質問はこちら - 1 3
    どんなࠣ細な内容も大歓迎!
    https://joind.in/talk/0b395

    View Slide

  4. 質問はこちら - 2 4
    ✔︎
    Discord


    #track2-2-mysql-tune


    ✔︎
    Twitter


    @mamy1326

    View Slide

  5. ✔︎
    バックエンド エンジニア


      →どちらかというと SQL は ORM 任せ


    ✔︎
    SQL のパフォーマンスで 困っている


      →でも 調べ方がよくわからない


    ✔︎
    SQL が 正直怖い


      →改善の 初手を知りたい
    想定オーディエンスのみなさま 5

    View Slide

  6. プロローグ 6
    ✔︎
    品質の 均一化


    → 誰が書いても同じ SQL が実行される


    ✔︎
    開発効率 の向上


    → SQL を書かずに開発できる
    SQL を 意識の外に置く 機会が増えた
    近年の ORM は高性能

    View Slide

  7. しかし


    アラートは突然


    やってくる
    7

    View Slide

  8. 8
    落ちる


    ページ表示速度

    View Slide

  9. 9
    上がる


    Load Average


    (DB -> Web)

    View Slide

  10. 10
    そして


    大量のスロークエリ

    View Slide

  11. 起きていたこと 11
    ✔︎
    スロークエリで パフォーマンス低下


    → DB サーバーへの負荷が上昇


    ✔︎
    Web サーバーへの レスポンス低下


    → ページ表示速度の低下
    サービスの 品質低下
    改善が必要な SQL の実行

    View Slide

  12. 12
    つまり


    障害発生

    View Slide

  13. ✔︎
    どんな SQL を実行 しているのか


      → 実際の速度と頻度はどうなのか


    ✔︎
    コードのどこ なのか


      → ORM 利用だと特定に時間がかかる


    ✔︎
    どう改善すれば いいのか


      → 解析方法は何があるのか
    障害発生時に知りたいこと 13

    View Slide

  14. ✔︎
    スローログ 監視


      → 常に状況を把握


    ✔︎
    スロークエリ 改善


      → 原因特定、影響範囲の把握、改善対応


    ✔︎
    スロークエリ 予防


      → 監視とレビュー
    エンジニアに求められること 14

    View Slide

  15. 15
    しかし


    障害の都度対応は


    後手

    View Slide

  16. 目指すこと 16
    ✔︎
    SQL を 普段から見る


    → 自分の実装の影響を把握する


    ✔︎
    日常的に予防 していく


    → 改善を回していく
    安定した 品質を確保
    後手から 先手へ

    View Slide

  17. 17
    先手のために


    クエリを


    チューニングしよう

    View Slide

  18. アジェンダ
    スロークエリを 改善する理由


    現場で起きている こと


    スロークエリの 見える化


    SQL の 分析・改善手法(一例)


    スロークエリ 予防


    SQL から始まる 本質の追求
    18

    View Slide

  19. お話し しない こと
    B-tree などインデックスの仕組み


    テーブル設計・正規化


    SQL の細かい見方・説明
    19

    View Slide

  20. 前提の環境
    Amazon Aurora
    20
    mysql> select AURORA_VERSION()
    ;

    +------------------
    +

    | AURORA_VERSION()
    |

    +------------------
    +

    | 2.09.2
    |

    +------------------
    +

    1 row in set (0.00 sec)

    View Slide

  21. 前提の環境
    MySQL Engine
    21
    mysql> select version()
    ;

    +------------
    +

    | version()
    |

    +------------
    +

    | 5.7.12-log
    |

    +------------
    +

    1 row in set (0.00 sec)

    View Slide

  22. 前提の環境
    スロークエリ検出時間
    22
    mysql> show variables like 'long%'
    ;

    +-----------------+-----------
    +

    | Variable_name | Value
    |

    +-----------------+-----------
    +

    | long_query_time | 10.000000
    |

    +-----------------+-----------
    +

    1 row in set (0.01 sec)

    View Slide

  23. お話しすること
    スロークエリを 改善する理由


    現場で起きている こと


    スロークエリの 見える化


    SQL の 分析・改善手法(一例)


    スロークエリ 予防


    SQL から始まる 本質の追求
    23

    View Slide

  24. ✔︎
    ORM で十分 使えている


      → 開発者は SQL 見なくていい


    ✔︎
    負荷増加でも お金で解決 できる


      → オートスケール、スケールアップなど


    ✔︎
    障害対応 できている


      → 発生は稀 だし サービスは継続できている
    こういうことありませんか? 24

    View Slide

  25. ✔︎
    ORM で十分 使えている


      → 開発者は SQL 見なくていい


    ✔︎
    お金で解決 できる


      → オートスケール、スケールアップなど


    ✔︎
    障害対応 できている


      → 発生は稀 だし サービスは継続できている
    こういうことありませんか? 25
    スロークエリを


    放置している

    View Slide

  26. スロークエリを放置すると… (1) 26
    ✔︎
    慢性的な 品質低下


    → 新規ユーザー獲得困難


    ✔︎
    サービス 信用失墜


    → 既存ユーザー離脱
    売上・利益の低下、競争敗北
    障害が慢性的 に発生

    View Slide

  27. スロークエリを放置すると… (2) 27
    ✔︎
    攻撃者の的 になる


    → 遅い SQL があると予測できる


    ✔︎
    Dos 攻撃 を受ける


    → DB サーバーの応答品質低下、Web サーバーも…
    攻撃 によるサービス 品質低下・停止
    特定の 機能が遅い

    View Slide

  28. 28
    1つの SQL が


    障害を発生 させる

    View Slide

  29. 29
    スロークエリ


    改善 しよう

    View Slide

  30. ✔︎
    サービス 品質向上


      → SQL の改善 1つで品質は上がる


    ✔︎
    SQL チェック習慣化で 事前予防


      → 障害のノウハウを日常のレビューへ


    ✔︎
    コスト・技術の パフォーマンス向上


      → 事業の安定化、チームの技術力底上げ
    スロークエリを改善 する 理由 30

    View Slide

  31. お話しすること
    スロークエリを 改善する理由


    現場で起きている こと


    スロークエリの 見える化


    SQL の 分析・改善手法(一例)


    スロークエリ 予防


    SQL から始まる 本質の追求
    31

    View Slide

  32. パフォーマンス低下の実例 32
    1
    2
    3
    リリース直後(検証不足)


    特定の時間(潜在的なスロークエリ)


    ある日突然(レコード数の増加)

    View Slide

  33. パフォーマンス低下の実例 33
    1
    2
    3
    リリース直後(検証不足)


    特定の時間(潜在的なスロークエリ)


    ある日突然(レコード数の増加)

    View Slide

  34. 現場で起きていること 34
    ✔︎
    開発環境では問題なく動作


    → ページ表示も高速


    ✔︎
    テストも通っている


    → プログラム品質も問題なし
    本番環境 で レスポンスが低下
    新機能をリリース

    View Slide

  35. 原因 35
    ✔︎
    開発環境では レコードを間引く


    → ストレージ容量、転送量の対策


    ✔︎
    実は本番環境だと 遅い SQL


    → 開発時は数万レコード、本番は数千万レコード
    本番環境 で レスポンスが低下
    本番と開発環境の レコード数の差

    View Slide

  36. 実例 36
    mysql> select count(1) from results;
    +----------+
    | count(1) |
    +----------+
    | 37858 |
    +----------+
    1 row in set (0.06 sec)
    mysql> select count(1) from results;
    +----------+
    | count(1) |
    +----------+
    | 31537676 |
    +----------+
    1 row in set (17.53 sec)
    開発環境 (local) 本番環境

    View Slide

  37. 実例 37
    開発環境 (local) 本番環境
    mysql> select count(1) from results;
    +----------+
    | count(1) |
    +----------+
    | 37858 |
    +----------+
    1 row in set (0.06 sec)
    mysql> select count(1) from results;
    +----------+
    | count(1) |
    +----------+
    | 31537676 |
    +----------+
    1 row in set (17.53 sec)
    データで検証し


    解決までをお話しします
    833 倍

    View Slide

  38. 開発環境のレコードは


    パフォーマンスの観点では


    無価値
    38

    View Slide

  39. 対策 39
    ✔︎
    本番とほぼ同じ レコード数


    → DB サーバーは本番と同じインスタンス


    ✔︎
    リリース前に 必ずテスト


    → 機能だけではなく、SQL 単体でも計測
    性能を確保 してリリース
    ステージング環境 を作る

    View Slide

  40. Tips 40
    ✔︎
    遅くて 待ちきれないのでタブ閉じ


    → SQL の実行を停止したと思っていても…


    ✔︎
    プロセスが残る 場合がある


    → 例:phpMyAdmin はタブを閉じても kill されない
    軽い気持ちが本番障害 につながる
    ステージングないなら 本番で実行…

    View Slide

  41. Tips 41
    ✔︎
    遅くて 待ちきれないのでタブ閉じ


    → SQL の実行を停止したと思っていても…


    ✔︎
    プロセスが残る 場合がある


    → 例:phpMyAdmin はタブを閉じても kill されない
    軽い気持ちが本番障害 につながる
    ステージングないなら 本番で実行…
    必ず


    ステージングで


    実施しましょう

    View Slide

  42. パフォーマンス低下の実例 42
    1
    2
    3
    2
    リリース直後(検証不足)


    特定の時間(潜在的なスロークエリ)


    ある日突然(レコード数の増加)

    View Slide

  43. 現場で起きていること 43
    ✔︎
    10 - 19 時 は 問題なかった


    → ページ表示も高速


    ✔︎
    テストも通っている


    → プログラム品質も問題なし
    特定の時間帯 で レスポンス低下
    リリース時 は問題なく動作

    View Slide

  44. 原因 1:潜在的なスロークエリ 44
    ✔︎
    遅い SQL でも 十分動いていた


    → リリース時は利用者が少なく速度低下はなかった


    ✔︎
    ピーク時間帯に DB 負荷増加


    → 例:1 sec の SQL がだんだんと遅くなりᮢ値を超える
    Webサーバー の レスポンスが低下
    ピーク時間帯 の負荷増加

    View Slide

  45. 対策:潜在的なスロークエリ 45

    View Slide

  46. 対策:潜在的なスロークエリ 46
    潜在 -> 顕在 へ

    View Slide

  47. 対策:潜在的なスロークエリ 47
    ✔︎
    EXPLAIN (実行計画) を確認


    → 遅くなる SQL を事前に改善


    潜在 -> 顕在 へ

    View Slide

  48. 予防例:潜在的なスロークエリ 48
    ✔︎
    レビュー時に全ての SQL を洗い出す


      → 1つの PR だと SQL の 数は限られる


    ✔︎
    ステージング環境で EXPLAIN / 実測


      → 遅い SQL がないか 1つずつチェック


    ✔︎
    レビュー指摘項目を Wiki 化


      → PR にリンク添付、ノウハウの蓄積と展開

    View Slide

  49. 対策:潜在的なスロークエリ 49
    ✔︎
    EXPLAIN (実行計画) を確認


    → 遅くなる SQL を事前に改善


    ✔︎
    (できれば) 負荷テスト を実施


    → ピーク時の負荷に耐えられることを確認
    潜在 -> 顕在 へ

    View Slide

  50. 対策:潜在的なスロークエリ 50
    ✔︎
    EXPLAIN (実行計画) を確認


    → 遅くなる SQL を事前に改善


    ✔︎
    (できれば) 負荷テスト を実施


    → ピーク時の負荷に耐えられることを確認
    性能を確保 してリリース
    潜在 -> 顕在 へ

    View Slide

  51. パフォーマンス低下の実例 51
    1
    2
    3
    リリース直後(検証不足)


    特定の時間(潜在的なスロークエリ)


    ある日突然(レコード数の増加)

    View Slide

  52. 現場で起きていること 52
    ✔︎
    数ヶ月問題はなかった


    → ページ表示も高速


    ✔︎
    リリースもしていない


    → インフラの変更もなし
    ある日を境目 に レスポンス低下
    しばらく安定稼働

    View Slide

  53. 原因 1:レコード数のᮢ値超過 53
    ✔︎
    レコード数:数万 -> 数100万


    → リリース時は数万、インデックスも効いていた


    ✔︎
    検索対象の 割合増加


    → フルスキャンの方が効率的と判断される
    前触れなくレスポンスが低下
    フルスキャン 発生

    View Slide

  54. 実例 1:レコード数のᮢ値超過 54
    過去の SQL 現在の SQL
    EXPLAIN
    SELECT
    id,
    work_id,
    user_id,
    status
    FROM
    results
    WHERE
    created <
    ‘2011-03-26 10:25:36’
    EXPLAIN
    SELECT
    id,
    work_id,
    user_id,
    status
    FROM
    results
    WHERE
    created <
    ‘2021-03-26 10:25:36’

    View Slide

  55. 実例 1:レコード数のᮢ値超過 55
    過去の SQL 現在の SQL
    EXPLAIN
    SELECT
    id,
    work_id,
    user_id,
    status
    FROM
    results
    WHERE
    created <
    ‘2011-03-26 10:25:36’
    EXPLAIN
    SELECT
    id,
    work_id,
    user_id,
    status
    FROM
    results
    WHERE
    created <
    ‘2021-03-26 10:25:36’
    833 倍
    半年前の


    一覧を検索

    View Slide

  56. 実例 1:レコード数のᮢ値超過 56
    過去の SQL 現在の SQL
    EXPLAIN
    SELECT
    id,
    work_id,
    user_id,
    status
    FROM
    results
    WHERE
    created <
    ‘2011-03-26 10:25:36’
    EXPLAIN
    SELECT
    id,
    work_id,
    user_id,
    status
    FROM
    results
    WHERE
    created <
    ‘2021-03-26 10:25:36’
    833 倍
    10 年前と


    現在を比較

    View Slide

  57. 実例 1:レコード数のᮢ値超過 57
    過去の EXPLAIN
    ************ 1. row ************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: range
    possible_keys: created
    key: created
    key_len: 6
    ref: NULL
    rows: 116270
    filtered: 100.00
    Extra: Using inde
    x

    condition
    1 row in set, 1 warning (0.00 sec)
    ************ 1. row ************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: ALL
    possible_keys: created
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 31069538
    filtered: 50.00
    Extra: Using where
    1 row in set, 1 warning (0.01
    sec)
    現在の EXPLAIN

    View Slide

  58. 実例 1:レコード数のᮢ値超過 58
    ************ 1. row ************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: range
    possible_keys: created
    key: created
    key_len: 6
    ref: NULL
    rows: 116270
    filtered: 100.00
    Extra: Using inde
    x

    condition
    1 row in set, 1 warning (0.00 sec)
    ************ 1. row ************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: ALL
    possible_keys: created
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 31069538
    filtered: 50.00
    Extra: Using where
    1 row in set, 1 warning (0.01
    sec)
    半年前は


    renge スキャン
    10 年前は


    range スキャンで


    インデックス効いてた
    過去の EXPLAIN 現在の EXPLAIN

    View Slide

  59. 実例 1:レコード数のᮢ値超過 59
    ************ 1. row ************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: range
    possible_keys: created
    key: created
    key_len: 6
    ref: NULL
    rows: 116270
    filtered: 100.00
    Extra: Using inde
    x

    condition
    1 row in set, 1 warning (0.00 sec)
    ************ 1. row ************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: ALL
    possible_keys: created
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 31069538
    filtered: 50.00
    Extra: Using where
    1 row in set, 1 warning (0.01
    sec)
    対象行数は


    11 万程度
    過去の EXPLAIN 現在の EXPLAIN

    View Slide

  60. 実例 1:レコード数のᮢ値超過 60
    ************ 1. row ************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: range
    possible_keys: created
    key: created
    key_len: 6
    ref: NULL
    rows: 116270
    filtered: 100.00
    Extra: Using inde
    x

    condition
    1 row in set, 1 warning (0.00 sec)
    ************ 1. row ************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: ALL
    possible_keys: created
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 31069538
    filtered: 50.00
    Extra: Using where
    1 row in set, 1 warning (0.01
    sec)
    現在は


    フルスキャン
    過去の EXPLAIN 現在の EXPLAIN

    View Slide

  61. 実例 1:レコード数のᮢ値超過 61
    ************ 1. row ************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: range
    possible_keys: created
    key: created
    key_len: 6
    ref: NULL
    rows: 116270
    filtered: 100.00
    Extra: Using inde
    x

    condition
    1 row in set, 1 warning (0.00 sec)
    ************ 1. row ************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: ALL
    possible_keys: created
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 31069538
    filtered: 50.00
    Extra: Using where
    1 row in set, 1 warning (0.01
    sec)
    対象行数は


    3106 万
    過去の EXPLAIN 現在の EXPLAIN

    View Slide

  62. 実例 1:レコード数のᮢ値超過 62
    ************ 1. row ************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: range
    possible_keys: created
    key: created
    key_len: 6
    ref: NULL
    rows: 116270
    filtered: 100.00
    Extra: Using inde
    x

    condition
    1 row in set, 1 warning (0.00 sec)
    ************ 1. row ************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: ALL
    possible_keys: created
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 31069538
    filtered: 50.00
    Extra: Using where
    1 row in set, 1 warning (0.01
    sec)
    遅い!
    過去の EXPLAIN 現在の EXPLAIN
    遅い!

    View Slide

  63. 63
    爆発的なレコード増加は


    もっと 短い期間でも


    起こる

    View Slide

  64. 対策:レコード数のᮢ値超過 64
    ✔︎
    範囲を さらに絞る


    → 1ヶ月単位で表示など、要件も見直す


    ✔︎
    他の 条件を追加


    → ユーザーの検索傾向を掴み、要求に応じる
    定常的にレスポンスが安定
    絞り込み 条件を追加

    View Slide

  65. EXPLAIN
    実際に範囲を絞った結果 65
    SQL
    EXPLAIN
    SELECT
    id,
    work_id,
    user_id,
    status
    FROM
    results
    WHERE
    created BETWEEN
    '2021-02-26 10:25:36' AND
    '2021-03-26 10:25:36'
    ************ 1. row ***********
    *

    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: range
    possible_keys: created
    key: created
    key_len: 6
    ref: NULL
    rows: 433396
    filtered: 100.00
    Extra: Using index
    condition
    1 row in set, 1 warning (0.00 sec)
    1ヶ月単位で


    絞り込む

    View Slide

  66. 実際に範囲を絞った結果 66
    EXPLAIN
    SELECT
    id,
    work_id,
    user_id,
    status
    FROM
    results
    WHERE
    created BETWEEN
    '2021-02-26 10:25:36' AND
    '2021-03-26 10:25:36'
    ************ 1. row ***********
    *

    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: range
    possible_keys: created
    key: created
    key_len: 6
    ref: NULL
    rows: 433396
    filtered: 100.00
    Extra: Using index
    condition
    1 row in set, 1 warning (0.00 sec)
    インデックスが


    利用される
    インデックスが


    利用される
    SQL EXPLAIN

    View Slide

  67. 実際に範囲を絞った結果 67
    EXPLAIN
    SELECT
    id,
    work_id,
    user_id,
    status
    FROM
    results
    WHERE
    created BETWEEN
    '2021-02-26 10:25:36' AND
    '2021-03-26 10:25:36'
    ************ 1. row ***********
    *

    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: range
    possible_keys: created
    key: created
    key_len: 6
    ref: NULL
    rows: 433396
    filtered: 100.00
    Extra: Using index
    condition
    1 row in set, 1 warning (0.00 sec)
    対象行数は


    43 万程度
    SQL EXPLAIN

    View Slide

  68. 68
    レコード増加傾向は


    必ず 予測して


    SQL を書こう

    View Slide

  69. パフォーマンス低下の実例 69
    1
    2
    3
    リリース直後(検証不足)


    特定の時間(潜在的なスロークエリ)


    ある日突然(レコード数の増加)
    2つ目の事例紹介

    View Slide

  70. 70
    ・お気に入りを管理 するテーブル


      → 多 対 多


    ・複数のテーブル に対応


      → 仕事、ユーザー、パッケージ、etc…


    ・対応する テーブル名をカラム で持つ


      → ポリモーフィック関連テーブル
    実例 2:カーディナリティの偏り
    例えばこんな要件

    View Slide

  71. 実例 2:カーディナリティの偏り 71
    テーブル設計

    View Slide

  72. 実例 2:カーディナリティの偏り 72
    テーブル設計
    各テーブル名と


    PK を持つ

    View Slide

  73. 実例 2:カーディナリティの偏り 73
    偏りを見る SQL
    SELECT
    count(id) AS counter,
    target_table_name
    FROM
    watchlists
    GROUP BY
    target_table_name
    ORDER BY
    counter DESC

    View Slide

  74. 実例 2:カーディナリティの偏り 74
    偏りを見る SQL
    SELECT
    count(id) AS counter,
    target_table_name
    FROM
    watchlists
    GROUP BY
    target_table_name
    ORDER BY
    counter DESC
    テーブルごとの


    お気に入り数と


    テーブル名

    View Slide

  75. 実例 2:カーディナリティの偏り 75
    偏りを見る SQL
    SELECT
    count(id) AS counter,
    target_table_name
    FROM
    watchlists
    GROUP BY
    target_table_name
    ORDER BY
    counter DESC
    テーブル名で


    GROUP BY


    View Slide

  76. 実例 2:カーディナリティの偏り 76
    偏りを見る SQL
    SELECT
    count(id) AS counter,
    target_table_name
    FROM
    watchlists
    GROUP BY
    target_table_name
    ORDER BY
    counter DESC
    お気に入り数で


    ORDER BY


    View Slide

  77. +---------+----------------------+
    | counter | target_table_name |
    +---------+----------------------+
    | 14736 | Works |
    | 9182 | Users |
    | 1532 | Packages |
    | 921 | Campaigns |
    | 686 | Consultations |
    | 258 | Laboratories |
    +---------+----------------------+
    6 rows in set (1.42 sec)
    実例 2:カーディナリティの偏り 77
    過去の 偏り 現在の 偏り
    +----------+----------------------+
    | counter | target_table_name |
    +----------+----------------------+
    | 13115751 | Works |
    | 1509182 | Users |
    | 72999 | Packages |
    | 2956 | Campaigns |
    | 1599 | Consultations |
    | 1483 | Laboratories |
    +----------+----------------------+
    6 rows in set (15.94 sec)

    View Slide

  78. +---------+----------------------+
    | counter | target_table_name |
    +---------+----------------------+
    | 14736 | Works |
    | 9182 | Users |
    | 1532 | Packages |
    | 921 | Campaigns |
    | 686 | Consultations |
    | 258 | Laboratories |
    +---------+----------------------+
    6 rows in set (1.42 sec)
    実例 2:カーディナリティの偏り 78
    過去の 偏り 現在の 偏り
    +----------+----------------------+
    | counter | target_table_name |
    +----------+----------------------+
    | 13115751 | Works |
    | 1509182 | Users |
    | 72999 | Packages |
    | 2956 | Campaigns |
    | 1599 | Consultations |
    | 1483 | Laboratories |
    +----------+----------------------+
    6 rows in set (15.94 sec)
    当初は


    偏りは少なかった

    View Slide

  79. +---------+----------------------+
    | counter | target_table_name |
    +---------+----------------------+
    | 14736 | Works |
    | 9182 | Users |
    | 1532 | Packages |
    | 921 | Campaigns |
    | 686 | Consultations |
    | 258 | Laboratories |
    +---------+----------------------+
    6 rows in set (1.42 sec)
    実例 2:カーディナリティの偏り 79
    過去の 偏り 現在の 偏り
    +----------+----------------------+
    | counter | target_table_name |
    +----------+----------------------+
    | 13115751 | Works |
    | 1509182 | Users |
    | 72999 | Packages |
    | 2956 | Campaigns |
    | 1599 | Consultations |
    | 1483 | Laboratories |
    +----------+----------------------+
    6 rows in set (15.94 sec)
    現在は


    大きな偏り

    View Slide

  80. +---------+----------------------+
    | counter | target_table_name |
    +---------+----------------------+
    | 14736 | Works |
    | 9182 | Users |
    | 1532 | Packages |
    | 921 | Campaigns |
    | 686 | Consultations |
    | 258 | Laboratories |
    +---------+----------------------+
    6 rows in set (1.42 sec)
    実例 2:カーディナリティの偏り 80
    過去の 偏り 現在の 偏り
    +----------+----------------------+
    | counter | target_table_name |
    +----------+----------------------+
    | 13115751 | Works |
    | 1509182 | Users |
    | 72999 | Packages |
    | 2956 | Campaigns |
    | 1599 | Consultations |
    | 1483 | Laboratories |
    +----------+----------------------+
    6 rows in set (15.94 sec)
    Works と


    Packages で


    計測

    View Slide

  81. SELECT
    user_id,
    count(user_id) AS counter
    FROM
    watchlists
    WHERE
    target_table_name = 'Works'
    GROUP BY
    user_id
    ORDER BY
    counter DESC
    LIMIT 10
    SELECT
    user_id,
    count(user_id) AS counter
    FROM
    watchlists
    WHERE
    target_table_name = 'Packages'
    GROUP BY
    user_id
    ORDER BY
    counter DESC
    LIMIT 10
    計測:カーディナリティの偏り 81
    Pakages の SQL Works の SQL
    ユーザー別 お気に入り TOP 10 を検索

    View Slide

  82. +---------+---------+
    | user_id | counter |
    +---------+---------+
    | 217007 | 112542 |
    | 133504 | 52666 |
    | 1173542 | 51593 |
    | 491368 | 43274 |
    | 1202492 | 42547 |
    | 1128178 | 37218 |
    | 308768 | 34025 |
    | 1299933 | 33739 |
    | 192303 | 32135 |
    | 19049 | 30996 |
    +---------+---------+
    10 rows in set (4.97 sec)
    +---------+---------+
    | user_id | counter |
    +---------+---------+
    | 2108334 | 11932 |
    | 1656807 | 858 |
    | 1195748 | 248 |
    | 1315163 | 199 |
    | 508175 | 165 |
    | 202871 | 144 |
    | 20867 | 122 |
    | 387129 | 120 |
    | 162001 | 105 |
    | 51974 | 105 |
    +---------+---------+
    10 rows in set (0.03 sec)
    計測:カーディナリティの偏り 82
    Pakages の 結果 Works の 結果

    View Slide

  83. +---------+---------+
    | user_id | counter |
    +---------+---------+
    | 217007 | 112542 |
    | 133504 | 52666 |
    | 1173542 | 51593 |
    | 491368 | 43274 |
    | 1202492 | 42547 |
    | 1128178 | 37218 |
    | 308768 | 34025 |
    | 1299933 | 33739 |
    | 192303 | 32135 |
    | 19049 | 30996 |
    +---------+---------+
    10 rows in set (4.97 sec)
    +---------+---------+
    | user_id | counter |
    +---------+---------+
    | 2108334 | 11932 |
    | 1656807 | 858 |
    | 1195748 | 248 |
    | 1315163 | 199 |
    | 508175 | 165 |
    | 202871 | 144 |
    | 20867 | 122 |
    | 387129 | 120 |
    | 162001 | 105 |
    | 51974 | 105 |
    +---------+---------+
    10 rows in set (0.03 sec)
    計測:カーディナリティの偏り 83
    パフォーマンスに


    問題はない
    Pakages の 結果 Works の 結果

    View Slide

  84. +---------+---------+
    | user_id | counter |
    +---------+---------+
    | 217007 | 112542 |
    | 133504 | 52666 |
    | 1173542 | 51593 |
    | 491368 | 43274 |
    | 1202492 | 42547 |
    | 1128178 | 37218 |
    | 308768 | 34025 |
    | 1299933 | 33739 |
    | 192303 | 32135 |
    | 19049 | 30996 |
    +---------+---------+
    10 rows in set (4.97 sec)
    +---------+---------+
    | user_id | counter |
    +---------+---------+
    | 2108334 | 11932 |
    | 1656807 | 858 |
    | 1195748 | 248 |
    | 1315163 | 199 |
    | 508175 | 165 |
    | 202871 | 144 |
    | 20867 | 122 |
    | 387129 | 120 |
    | 162001 | 105 |
    | 51974 | 105 |
    +---------+---------+
    10 rows in set (0.03 sec)
    計測:カーディナリティの偏り 84
    パフォーマンスに


    問題あり
    Pakages の 結果 Works の 結果

    View Slide

  85. **************** 1. row ****************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: ref
    possible_keys: user_id,
    target_table_name_user_id
    key: target_table_name_user_id
    key_len: 768
    ref: const
    rows: 6791813
    filtered: 100.00
    Extra: Using where; Using index;
    Using temporary; Using filesort
    1 row in set, 1 warning (0.00 sec)
    **************** 1. row ****************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: ref
    possible_keys: user_id,
    target_table_name_user_i
    d

    key: target_table_name_user_id
    key_len: 768
    ref: const
    rows: 139616
    filtered: 100.00
    Extra: Using where; Using index;
    Using temporary; Using filesort
    1 row in set, 1 warning (0.00 sec)
    計測:カーディナリティの偏り 85
    Pakages の EXPLAIN Works の EXPLAIN

    View Slide

  86. Pakages の EXPLAIN
    **************** 1. row ****************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: ref
    possible_keys: user_id,
    target_table_name_user_id
    key: target_table_name_user_id
    key_len: 768
    ref: const
    rows: 6791813
    filtered: 100.00
    Extra: Using where; Using index;
    Using temporary; Using filesort
    1 row in set, 1 warning (0.00 sec)
    **************** 1. row ****************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: ref
    possible_keys: user_id,
    target_table_name_user_i
    d

    key: target_table_name_user_id
    key_len: 768
    ref: const
    rows: 139616
    filtered: 100.00
    Extra: Using where; Using index;
    Using temporary; Using filesort
    1 row in set, 1 warning (0.00 sec)
    Works の EXPLAIN
    計測:カーディナリティの偏り 86
    パフォーマンスに


    問題はない
    インデックスが


    利用されているけど

    View Slide

  87. Pakages の EXPLAIN
    **************** 1. row ****************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: ref
    possible_keys: user_id,
    target_table_name_user_id
    key: target_table_name_user_id
    key_len: 768
    ref: const
    rows: 6791813
    filtered: 100.00
    Extra: Using where; Using index;
    Using temporary; Using filesort
    1 row in set, 1 warning (0.00 sec)
    **************** 1. row ****************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: ref
    possible_keys: user_id,
    target_table_name_user_i
    d

    key: target_table_name_user_id
    key_len: 768
    ref: const
    rows: 139616
    filtered: 100.00
    Extra: Using where; Using index;
    Using temporary; Using filesort
    1 row in set, 1 warning (0.00 sec)
    Works の EXPLAIN
    計測:カーディナリティの偏り 87
    対象行数が多く


    遅くなる可能性

    View Slide

  88. 88
    大きな偏りは


    もはや 別物

    View Slide

  89. 対策:カーディナリティの偏り 89
    ✔︎
    もはや 別の要素


    → いずれ全体的にパフォーマンスが低下する可能性


    ✔︎
    元々 分けるべき設計


    → ポリモーフィック関連テーブルはアンチパターン
    パフォーマンスの 影響範囲を絞る
    偏ったレコードは 別テーブルへ

    View Slide

  90. 90
    現場で 起きていること


    から


    やるべきこと が見える

    View Slide

  91. ✔︎
    ステージング で確認


      → 性能を確保 してリリース


    ✔︎
    潜在的 スロークエリを顕在化


      → EXPLAIN 実行で 事前に解決


    ✔︎
    レコード 増加数を予測


      → 予測した検索条件、テーブル分割
    現場で起きていること 91
    現場でやるべきこと

    View Slide

  92. お話しすること
    スロークエリを 改善する理由


    現場で起きている こと


    スロークエリの 見える化


    SQL の 分析・改善手法(一例)


    スロークエリ 予防


    SQL から始まる 本質の追求
    92

    View Slide

  93. スロークエリの見える化 93
    1
    2
    2
    フレームワークの SqlLog


    MySQL のスローログ

    View Slide

  94. スロークエリの見える化 94
    1
    2
    2
    フレームワークの SqlLog


    MySQL のスローログ

    View Slide

  95. フレームワークの SqlLog 95
    CakePHP 4

    View Slide

  96. フレームワークの SqlLog 96
    CakePHP 4
    画面で実行された


    SQL が全て


    表示される

    View Slide

  97. フレームワークの SqlLog 97
    CakePHP 4
    n+1 な SQL も


    一目でわかる

    View Slide

  98. SqlLog の実際の利用例 98
    ✔︎
    1つずつ 実測


    → リリース後の速度をあらかじめ計測


    ✔︎
    1つずつ EXPLAIN


    → 潜在的なスロークエリを見つけて、改善
    性能を確保 してリリース
    列挙された SQL を ステージングで確認

    View Slide

  99. 99
    次は


    本番環境の監視

    View Slide

  100. スロークエリの見える化 100
    1
    2
    2
    フレームワークの SqlLog


    MySQL のスローログ
    2
    1

    View Slide

  101. 101
    障害時に


    流れた SQL は


    記録しないと


    残らない

    View Slide

  102. 102
    障害発生後に


    原因を探せない

    View Slide

  103. MySQL のスローログ 103
    my.cnf
    [mysqld]
    # εϩʔΫΤϦͷग़ྗઃఆ
    slow_query_log=ON
    # εϩʔΫΤϦͱ൑ఆ͢Δඵ਺
    long_query_time=10
    # εϩʔΫΤϦϩάͷ৔ॴ
    log-slow-queries=/var/log/slow.log
    /etc/init.d/mysqld restart 忘れずに

    View Slide

  104. MySQL のスローログ 104
    RDS のスローログ出力設定
    ✔︎
    CloudWatch で閲覧可能

    View Slide

  105. MySQL のスローログ 105
    RDS のスローログ秒数設定
    ✔︎
    パラメーターグループを作って


      再起動不要にしておきましょう

    View Slide

  106. 106
    CloudWatch じゃなく


    もっと


    便利に可視化


    したい

    View Slide

  107. MySQL のスローログ 107
    ✔︎
    CloudWatch から Slack へ


    → Lambda でサクッと実装(事例がたくさんある)


    ✔︎
    DataDog などで コード特定


    → スロークエリを実行している場所がわかる
    スピーディな対応 を可能に
    更に可視化する

    View Slide

  108. MySQL のスローログ 108
    Slack 通知例

    View Slide

  109. MySQL のスローログ 109
    Slack 通知例 実際の


    スロークエリ

    View Slide

  110. MySQL のスローログ 110
    Slack 通知例
    実行時間が


    わかる

    View Slide

  111. 発見から改善までの流れ 111
    RDS アラート


    を検知
    みんなで通知を見る


    当番制にする、etc
    スローログを


    検索・特定
    アラートの時間帯で


    絞り込む (〜前 10 分)
    コードを


    特定
    テーブル名、機能、


    特徴的な部分から特定
    修正して


    PullRequest 作成
    EXPLAIN、分析、修正


    有識者のレビュー必須
    リリース
    ステージング検証後


    レビュアーと一緒に実施
    経過を観察
    リリース後の負荷観察


    修正した機能の確認

    View Slide

  112. 発見から改善までの流れ 112
    RDS アラート


    を検知
    みんなで通知を見る


    当番制にする、etc
    スローログを


    検索・特定
    アラートの時間帯で


    絞り込む (〜前 10 分)
    コードを


    特定
    テーブル名、機能、


    特徴的な部分から特定
    修正して


    PullRequest 作成
    EXPLAIN、分析、修正


    有識者のレビュー必須
    リリース
    ステージング検証後


    レビュアーと一緒に実施
    経過を観察
    リリース後の負荷観察


    修正した機能の確認

    View Slide

  113. 発見から改善までの流れ 113
    RDS アラート


    を検知
    みんなで通知を見る


    当番制にする、etc
    スローログを


    検索・特定
    アラートの時間帯で


    絞り込む (〜前 10 分)
    コードを


    特定
    テーブル名、機能、


    特徴的な部分から特定
    修正して


    PullRequest 作成
    EXPLAIN、分析、修正


    有識者のレビュー必須
    リリース
    ステージング検証後


    レビュアーと一緒に実施
    経過を観察
    リリース後の負荷観察


    修正した機能の確認

    View Slide

  114. 発見から改善までの流れ 114
    RDS アラート


    を検知
    みんなで通知を見る


    当番制にする、etc
    スローログを


    検索・特定
    アラートの時間帯で


    絞り込む (〜前 10 分)
    コードを


    特定
    テーブル名、機能、


    特徴的な部分から特定
    修正して


    PullRequest 作成
    EXPLAIN、分析、修正


    有識者のレビュー必須
    リリース
    ステージング検証後


    レビュアーと一緒に実施
    経過を観察
    リリース後の負荷観察


    修正した機能の確認

    View Slide

  115. 発見から改善までの流れ 115
    RDS アラート


    を検知
    みんなで通知を見る


    当番制にする、etc
    スローログを


    検索・特定
    アラートの時間帯で


    絞り込む (〜前 10 分)
    コードを


    特定
    テーブル名、機能、


    特徴的な部分から特定
    修正して


    PullRequest 作成
    EXPLAIN、分析、修正


    有識者のレビュー必須
    リリース
    ステージング検証後


    レビュアーと一緒に実施
    経過を観察
    リリース後の負荷観察


    修正した機能の確認

    View Slide

  116. 発見から改善までの流れ 116
    RDS アラート


    を検知
    みんなで通知を見る


    当番制にする、etc
    スローログを


    検索・特定
    アラートの時間帯で


    絞り込む (〜前 10 分)
    コードを


    特定
    テーブル名、機能、


    特徴的な部分から特定
    修正して


    PullRequest 作成
    EXPLAIN、分析、修正


    有識者のレビュー必須
    リリース
    ステージング検証後


    レビュアーと一緒に実施
    経過を観察
    リリース後の負荷観察


    修正した機能の確認

    View Slide

  117. 発見から改善までの流れ 117
    RDS アラート


    を検知
    みんなで通知を見る


    当番制にする、etc
    スローログを


    検索・特定
    アラートの時間帯で


    絞り込む (〜前 10 分)
    コードを


    特定
    テーブル名、機能、


    特徴的な部分から特定
    修正して


    PullRequest 作成
    EXPLAIN、分析、修正


    有識者のレビュー必須
    リリース
    ステージング検証後


    レビュアーと一緒に実施
    経過を観察
    リリース後の負荷観察


    修正した機能の確認

    View Slide

  118. 118
    もっと良くするため


    課題 がある

    View Slide

  119. ✔︎
    コード 特定の自動化


      → スロークエリ 実行箇所の明示


    ✔︎
    スロークエリ 分析の可視化


      → 同じ SQL の 回数、実行時間を集計
    スロークエリの見える化 119
    課題

    View Slide

  120. スロークエリの見える化の課題 (1) 120
    ✔︎
    特定の手作業が不要に


    → 短いと 30 分以内、長いと数時間がゼロに


    ✔︎
    通知時に コード特定


    → ピンポイントで修正箇所がわかる (DataDog など)
    スピーディ・正確な対応 を可能に
    コード 特定の自動化

    View Slide

  121. 発見から改善までの流れ 121
    RDS アラート


    を検知
    みんなで通知を見る


    当番制にする、etc
    スローログを


    検索・特定
    アラートの時間帯で


    絞り込む (〜前 10 分)
    コードを


    特定
    テーブル名、機能、


    特徴的な部分から特定
    修正して


    PullRequest 作成
    EXPLAIN、分析、修正


    有識者のレビュー必須
    リリース
    ステージング検証後


    レビュアーと一緒に実施
    経過を観察
    リリース後の負荷観察


    修正した機能の確認
    ここが


    View Slide

  122. スロークエリの見える化の課題 (2) 122
    ✔︎
    回数、時間、割合の集計


    → スロークエリ全体の中での SQL の割合、回数、合計時間


    ✔︎
    数値の高い SQL から順に対応


    → より効果の高い対応が可能 (pt-query-digest)
    先手対応 が可能に
    スロークエリ 分析の可視化

    View Slide

  123. 発見から改善までの流れ 123
    RDS アラート


    を検知
    みんなで通知を見る


    当番制にする、etc
    スローログを


    検索・特定
    アラートの時間帯で


    絞り込む (〜前 10 分)
    コードを


    特定
    テーブル名、機能、


    特徴的な部分から特定
    修正して


    PullRequest 作成
    EXPLAIN、分析、修正


    有識者のレビュー必須
    リリース
    ステージング検証後


    レビュアーと一緒に実施
    経過を観察
    リリース後の負荷観察


    修正した機能の確認
    発生率が


    View Slide

  124. ✔︎
    実装した SQL を列挙・確認


      → フレームワークのデバッガで見る


    ✔︎
    スロークエリ 通知で SQL 特定


      → 原因の見える化で 確実・スピーディな対応


    ✔︎
    コード特定、分析で 先手を打つ


      → DataDog、pt-query-digest など検討
    スロークエリの見える化 124
    まとめ

    View Slide

  125. 125
    後手から


    先手を目指し


    より本質対応を

    View Slide

  126. お話しすること
    スロークエリを 改善する理由


    現場で起きている こと


    スロークエリの 見える化


    SQL の 分析・改善手法(一例)


    スロークエリ 予防


    SQL から始まる 本質の追求
    126

    View Slide

  127. SQL の分析・改善手法 127
    1
    2
    2
    EXPLAIN の主な見方


    インデックス追加で改善事例


    インデックスの管理
    2
    1
    1
    3

    View Slide

  128. EXPLAIN の主な見方


    インデックス追加で改善事例


    インデックスの管理
    SQL の分析・改善手法 128
    1
    2
    2
    2
    1
    1
    3

    View Slide

  129. 129
    漢のコンピュータ道
    MySQLのEXPLAINを


    徹底解説!!


    読みましょう!

    View Slide

  130. 130
    だとこの章が


    終わるので…

    View Slide

  131. EXPLAIN の主な見方 131
    EXPLAIN 実行例
    ************** 1. row **************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: range
    possible_keys: created
    key: created
    key_len: 6
    ref: NULL
    rows: 433396
    filtered: 100.00
    Extra: Using index condition
    1 row in set, 1 warning (0.03 sec)
    mysql> EXPLAIN
    -> SELECT
    -> id,
    -> work_id,
    -> user_id,
    -> status
    -> FROM
    -> results
    -> WHERE
    -> created BETWEEN
    '2021-02-26 10:25:36' AND
    '2021-03-26 10:25:36'\G

    View Slide

  132. EXPLAIN の主な見方 132
    EXPLAIN 実行例
    ************** 1. row **************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: range
    possible_keys: created
    key: created
    key_len: 6
    ref: NULL
    rows: 433396
    filtered: 100.00
    Extra: Using index condition
    1 row in set, 1 warning (0.03 sec)
    mysql> EXPLAIN
    -> SELECT
    -> id,
    -> work_id,
    -> user_id,
    -> status
    -> FROM
    -> results
    -> WHERE
    -> created BETWEEN
    '2021-02-26 10:25:36' AND
    '2021-03-26 10:25:36'\G
    先頭に


    EXPLAIN と


    書くだけ

    View Slide

  133. EXPLAIN の主な見方 133
    EXPLAIN 実行例
    ************** 1. row **************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: range
    possible_keys: created
    key: created
    key_len: 6
    ref: NULL
    rows: 433396
    filtered: 100.00
    Extra: Using index condition
    1 row in set, 1 warning (0.03 sec)
    mysql> EXPLAIN
    -> SELECT
    -> id,
    -> work_id,
    -> user_id,
    -> status
    -> FROM
    -> results
    -> WHERE
    -> created BETWEEN
    '2021-02-26 10:25:36' AND
    '2021-03-26 10:25:36'\G
    どのインデックスを
    使って SQL を処理
    するかがわかる

    View Slide

  134. EXPLAIN の主な見方 134
    EXPLAIN 実行例
    ************** 1. row **************
    id: 1
    select_type: SIMPLE
    table: results
    partitions: NULL
    type: range
    possible_keys: created
    key: created
    key_len: 6
    ref: NULL
    rows: 433396
    filtered: 100.00
    Extra: Using index condition
    1 row in set, 1 warning (0.03 sec)
    mysql> EXPLAIN
    -> SELECT
    -> id,
    -> work_id,
    -> user_id,
    -> status
    -> FROM
    -> results
    -> WHERE
    -> created BETWEEN
    '2021-02-26 10:25:36' AND
    '2021-03-26 10:25:36'\G
    この 4つを


    お話しします

    View Slide

  135. ✔︎
    複合的 に見る


      ・type, rows, Extra など 合わせて判断


    ✔︎
    インデックスの使われ方 を見る


      ・インデックスが 足りない


      ・インデックスでは 検索しきれない


      ・対象が多い
    EXPLAIN の主な見方 135
    前提

    View Slide

  136. ・ALL:対応必須


      → フルスキャン、最も遅い


      → インデックスが全く使用されない


    ・index:対応必須


      → インデックスでは 検索しきれない


      → 対象が多い
    EXPLAIN の主な見方 136
    type

    View Slide

  137. ・range:注意


      → インデックスを使った 範囲検索


      → 対象行数 rows が十分 絞られていれば高速


      → rows の 割合が多い場合 は改善が必要


    ・const:安心


      → PK または UK で 1行 または 0行 を取得


      → 等価比較で 最高速
    EXPLAIN の主な見方 137
    type

    View Slide

  138. ・オプティマイザが 選択したインデックス


      → 使われるインデックス


      → 実行時間が最小 になるよう処理方法を決める


      → その 過程で選択される


    ・狙いと違う インデックスなら注意


      → インデックスの 実態が最適化に沿っていない


      → 統計情報の再作成 など対策が必要な場合も
    EXPLAIN の主な見方 138
    key

    View Slide

  139. ・検索対象の レコード数


      → 絞りたかった数と 同じか近い ことを確認


    ・想定より 多い場合は注意


      → パフォーマンス低下 の可能性


      → 絞り込みが足りない


      → 当初よりレコード数が増えている
    EXPLAIN の主な見方 139
    rows

    View Slide

  140. ・Using index:高速


      → カバリングインデックスなど


      → インデックスのみで検索 が完結する


      → ただし rows の割合が多い場合は注意
    EXPLAIN の主な見方 140
    Extra

    View Slide

  141. ・Using index condition (ICP):高速


      → 複合インデックスを より効率的に使う


      → 余計なレコード検索を 減らす


      → MySQL 5.6 からの機能


      → ただし rows の割合が多い場合は注意
    EXPLAIN の主な見方 141
    Extra

    View Slide

  142. ・Using
    fi
    lesort:注意


      → JOIN と ORDER BY の組み合わせでよく見る


      → メモリとファイルでクイックソート


      → 駆動表と ORDER BY が別テーブルの場合


      → GROUP BY による暗黙の ORDER BY でも出る


      → LIMIT 前のレコード数が多ければ遅い


      → Using temporary が一緒に出ることが多い
    EXPLAIN の主な見方 142
    Extra

    View Slide

  143. ✔︎
    複合的 に見る


      ・type, rows, Extra など 合わせて判断


    ✔︎
    インデックスの使われ方 を見る


      ・インデックスが 足りない


      ・インデックスでは 検索しきれない


      ・対象が多い
    EXPLAIN の主な見方 143
    前提(再掲)

    View Slide

  144. EXPLAIN の主な見方


    インデックス追加で改善事例


    インデックスの管理
    SQL の分析・改善手法 144
    1
    2
    2
    EXPLAIN の主な見方


    インデックス追加で改善事例
    2
    1
    1
    3

    View Slide

  145. 145
    単純に


    インデックスが


    足りない 場合

    View Slide

  146. 146
    SQL
    SELECT
    Feedbacks.user_id,
    Feedbacks.feedback_user_id,
    Feedbacks.work_id,
    Feedbacks.evaluation,
    Feedbacks.description
    FROM
    feedbacks Feedbacks
    WHERE
    Feedbacks.evaluation IS NULL
    AND Feedbacks.created


    BETWEEN '2021-09-16 15:19:21'


    AND ‘2021-09-18 15:19:21'
    インデックス改善事例

    View Slide

  147. 147
    SQL
    SELECT
    Feedbacks.user_id,
    Feedbacks.feedback_user_id,
    Feedbacks.work_id,
    Feedbacks.evaluation,
    Feedbacks.description
    FROM
    feedbacks Feedbacks
    WHERE
    Feedbacks.evaluation IS NULL
    AND Feedbacks.created


    BETWEEN '2021-09-16 15:19:21'


    AND ‘2021-09-18 15:19:21'
    インデックス改善事例
    仕事に対する


    評価のテーブル

    View Slide

  148. 148
    SQL
    SELECT
    Feedbacks.user_id,
    Feedbacks.feedback_user_id,
    Feedbacks.work_id,
    Feedbacks.evaluation,
    Feedbacks.description
    FROM
    feedbacks Feedbacks
    WHERE
    Feedbacks.evaluation IS NULL
    AND Feedbacks.created


    BETWEEN '2021-09-16 15:19:21'


    AND ‘2021-09-18 15:19:21'
    インデックス改善事例
    評価の値が


    まだない人

    View Slide

  149. 149
    SQL
    SELECT
    Feedbacks.user_id,
    Feedbacks.feedback_user_id,
    Feedbacks.work_id,
    Feedbacks.evaluation,
    Feedbacks.description
    FROM
    feedbacks Feedbacks
    WHERE
    Feedbacks.evaluation IS NULL
    AND Feedbacks.created


    BETWEEN '2021-09-16 15:19:21'


    AND ‘2021-09-18 15:19:21'
    インデックス改善事例
    とある


    2日間

    View Slide

  150. 150
    実測
    +---------+------------------+---------+------------+-------------+
    | user_id | feedback_user_id | work_id | evaluation | description |
    +---------+------------------+---------+------------+-------------+
    | 2583253 | 2584632 | 3759308 | NULL | |
    (தུ)
    | 71384 | 2331284 | 3743634 | NULL | |
    +---------+------------------+---------+------------+-------------+
    525 rows in set (17.16 sec)
    インデックス改善事例

    View Slide

  151. 151
    実測
    +---------+------------------+---------+------------+-------------+
    | user_id | feedback_user_id | work_id | evaluation | description |
    +---------+------------------+---------+------------+-------------+
    | 2583253 | 2584632 | 3759308 | NULL | |
    (தུ)
    | 71384 | 2331284 | 3743634 | NULL | |
    +---------+------------------+---------+------------+-------------+
    525 rows in set (17.16 sec)
    インデックス改善事例

    View Slide

  152. 152
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Feedback
    partitions: NULL
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 2832867
    filtered: 1.11
    Extra: Using where
    インデックス改善事例

    View Slide

  153. 153
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Feedback
    partitions: NULL
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 2832867
    filtered: 1.11
    Extra: Using where
    フルスキャン
    インデックス改善事例

    View Slide

  154. 154
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Feedback
    partitions: NULL
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 2832867
    filtered: 1.11
    Extra: Using where
    対象レコードが


    多い


    (全体の 90 %)
    インデックス改善事例

    View Slide

  155. 155
    既存インデックス
    mysql> show index from feedbacks;
    +-----------+------------+--------------------+--------------+------------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name |
    +-----------+------------+--------------------+--------------+------------------+
    | feedbacks | 0 | PRIMARY | 1 | id |
    | feedbacks | 1 | work_id | 1 | work_id |
    | feedbacks | 1 | user_id | 1 | user_id |
    | feedbacks | 1 | feedback_user_id | 1 | feedback_user_id |
    | feedbacks | 1 | modified | 1 | modified |
    +-----------+------------+--------------------+--------------+------------------+
    5 rows in set (0.00 sec)
    インデックス改善事例

    View Slide

  156. 156
    既存インデックス
    mysql> show index from feedbacks;
    +-----------+------------+--------------------+--------------+------------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name |
    +-----------+------------+--------------------+--------------+------------------+
    | feedbacks | 0 | PRIMARY | 1 | id |
    | feedbacks | 1 | work_id | 1 | work_id |
    | feedbacks | 1 | user_id | 1 | user_id |
    | feedbacks | 1 | feedback_user_id | 1 | feedback_user_id |
    | feedbacks | 1 | modified | 1 | modified |
    +-----------+------------+--------------------+--------------+------------------+
    5 rows in set (0.00 sec)
    インデックス改善事例
    WHERE 句に該当する


    インデックスがない

    View Slide

  157. 157
    ✔︎
    もっとも単純


    → インデックス追加で大抵は高速に


    ✔︎
    なるべく 単純な SQL を目指す


    → 絞り込みに無理はないか、要件は妥当か
    インデックス追加、EXPLAIN、計測
    インデックスが 無くて遅い
    インデックス改善事例

    View Slide

  158. 158
    インデックス追加
    mysql> ALTER TABLE feedbacks ADD INDEX evaluation_created(
    -> evaluation,
    -> created
    -> )
    -> ;
    Query OK, 0 rows affected (6.44 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    インデックス改善事例

    View Slide

  159. 159
    実測
    +---------+------------------+---------+------------+-------------+
    | user_id | feedback_user_id | work_id | evaluation | description |
    +---------+------------------+---------+------------+-------------+
    | 2583253 | 2584632 | 3759308 | NULL | |
    (தུ)
    | 71384 | 2331284 | 3743634 | NULL | |
    +---------+------------------+---------+------------+-------------+
    525 rows in set (0.06 sec)
    インデックス改善事例
    十分な


    パフォーマンス

    View Slide

  160. 160
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Feedbacks
    partitions: NULL
    type: range
    possible_keys: evaluation_created
    key: evaluation_created
    key_len: 11
    ref: NULL
    rows: 525
    filtered: 100.00
    Extra: Using index condition
    1 row in set, 1 warning (0.00 sec)
    インデックス改善事例

    View Slide

  161. 161
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Feedbacks
    partitions: NULL
    type: range
    possible_keys: evaluation_created
    key: evaluation_created
    key_len: 11
    ref: NULL
    rows: 525
    filtered: 100.00
    Extra: Using index condition
    1 row in set, 1 warning (0.00 sec)
    インデックスでの


    範囲 (range) 検索
    インデックス改善事例

    View Slide

  162. 162
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Feedbacks
    partitions: NULL
    type: range
    possible_keys: evaluation_created
    key: evaluation_created
    key_len: 11
    ref: NULL
    rows: 525
    filtered: 100.00
    Extra: Using index condition
    1 row in set, 1 warning (0.00 sec)
    作ったインデックス
    が選択された
    インデックス改善事例

    View Slide

  163. 163
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Feedbacks
    partitions: NULL
    type: range
    possible_keys: evaluation_created
    key: evaluation_created
    key_len: 11
    ref: NULL
    rows: 525
    filtered: 100.00
    Extra: Using index condition
    1 row in set, 1 warning (0.00 sec)
    絞り込み行数が


    実際の検索行数へ
    インデックス改善事例

    View Slide

  164. 164
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Feedbacks
    partitions: NULL
    type: range
    possible_keys: evaluation_created
    key: evaluation_created
    key_len: 11
    ref: NULL
    rows: 525
    filtered: 100.00
    Extra: Using index condition
    1 row in set, 1 warning (0.00 sec)
    インデックスを使っ
    た検索ができている
    インデックス改善事例

    View Slide

  165. 165
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Feedbacks
    partitions: NULL
    type: range
    possible_keys: evaluation_created
    key: evaluation_created
    key_len: 11
    ref: NULL
    rows: 525
    filtered: 100.00
    Extra: Using index condition
    1 row in set, 1 warning (0.00 sec)
    ちょっと気になる


    warning
    インデックス改善事例

    View Slide

  166. 166
    warning 表示
    mysql> SHOW WARNINGS\G
    ************************ 1. row ************************
    Level: Note
    Code: 1003
    Message: /* select#1 */ select
    `db_name`.`Feedbacks`.`evaluation` AS
    `evaluation`,`db_name`.`Feedbacks`.`created` AS
    `created` from `db_name`.`feedbacks` `Feedbacks` where
    (isnull(`db_name`.`Feedbacks`.`evaluation`) and
    (`db_name`.`Feedbacks`.`created` between '2021-09-16
    15:19:21' and '2021-09-18 15:19:21'))
    1 row in set (0.00 sec)
    インデックス改善事例

    View Slide

  167. 167
    warning 表示
    mysql> SHOW WARNINGS\G
    ************************ 1. row ************************
    Level: Note
    Code: 1003
    Message: /* select#1 */ select
    `db_name`.`Feedbacks`.`evaluation` AS
    `evaluation`,`db_name`.`Feedbacks`.`created` AS
    `created` from `db_name`.`feedbacks` `Feedbacks` where
    (isnull(`db_name`.`Feedbacks`.`evaluation`) and
    (`db_name`.`Feedbacks`.`created` between '2021-09-16
    15:19:21' and '2021-09-18 15:19:21'))
    1 row in set (0.00 sec)
    EXPLAIN
    EXTENDED


    (追加情報)
    インデックス改善事例

    View Slide

  168. 168
    warning 表示
    mysql> SHOW WARNINGS\G
    ************************ 1. row ************************
    Level: Note
    Code: 1003
    Message: /* select#1 */ select
    `db_name`.`Feedbacks`.`evaluation` AS
    `evaluation`,`db_name`.`Feedbacks`.`created` AS
    `created` from `db_name`.`feedbacks` `Feedbacks` where
    (isnull(`db_name`.`Feedbacks`.`evaluation`) and
    (`db_name`.`Feedbacks`.`created` between '2021-09-16
    15:19:21' and '2021-09-18 15:19:21'))
    1 row in set (0.00 sec)
    EXPLAIN 時に


    常に表示される
    インデックス改善事例

    View Slide

  169. 169
    warning 表示
    mysql> SHOW WARNINGS\G
    ************************ 1. row ************************
    Level: Note
    Code: 1003
    Message: /* select#1 */ select
    `db_name`.`Feedbacks`.`evaluation` AS
    `evaluation`,`db_name`.`Feedbacks`.`created` AS
    `created` from `db_name`.`feedbacks` `Feedbacks` where
    (isnull(`db_name`.`Feedbacks`.`evaluation`) and
    (`db_name`.`Feedbacks`.`created` between '2021-09-16
    15:19:21' and '2021-09-18 15:19:21'))
    1 row in set (0.00 sec)
    オプティマイザの


    最適化の際のメモ
    インデックス改善事例

    View Slide

  170. 170
    warning 表示
    mysql> SHOW WARNINGS\G
    ************************ 1. row ************************
    Level: Note
    Code: 1003
    Message: /* select#1 */ select
    `db_name`.`Feedbacks`.`evaluation` AS
    `evaluation`,`db_name`.`Feedbacks`.`created` AS
    `created` from `db_name`.`feedbacks` `Feedbacks` where
    (isnull(`db_name`.`Feedbacks`.`evaluation`) and
    (`db_name`.`Feedbacks`.`created` between '2021-09-16
    15:19:21' and '2021-09-18 15:19:21'))
    1 row in set (0.00 sec)
    SQL を


    書き換えたよ
    インデックス改善事例

    View Slide

  171. 171
    warning 表示
    mysql> SHOW WARNINGS\G
    ************************ 1. row ************************
    Level: Note
    Code: 1003
    Message: /* select#1 */ select
    `db_name`.`Feedbacks`.`evaluation` AS
    `evaluation`,`db_name`.`Feedbacks`.`created` AS
    `created` from `db_name`.`feedbacks` `Feedbacks` where
    (isnull(`db_name`.`Feedbacks`.`evaluation`) and
    (`db_name`.`Feedbacks`.`created` between '2021-09-16
    15:19:21' and '2021-09-18 15:19:21'))
    1 row in set (0.00 sec)
    IS NULL から


    isnull 関数に


    書き換え
    インデックス改善事例

    View Slide

  172. ・Using index:高速


      → カバリングインデックスなど


      → インデックスのみで検索 が完結する


      → ただし rows の割合が多い場合は注意
    EXPLAIN の主な見方 172
    Extra (再掲)

    View Slide

  173. ・Using index:高速


      → カバリングインデックスなど


      → インデックスのみで検索 が完結する


      → ただし rows の割合が多い場合は注意
    EXPLAIN の主な見方 173
    Extra (再掲)
    カバリングインデックスを


    View Slide

  174. カバリングインデックスを試す 174
    追加したインデックス
    mysql> show index from feedbacks where Key_name='evaluation_created';
    +-----------+------------+--------------------+--------------+------------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name |
    +-----------+------------+--------------------+--------------+------------------+
    | feedbacks | 1 | evaluation_created | 1 | evaluation |
    | feedbacks | 1 | evaluation_created | 2 | created |
    +-----------+------------+--------------------+--------------+------------------+
    2 rows in set (0.00 sec)

    View Slide

  175. カバリングインデックスを試す 175
    SQL
    SELECT
    Feedbacks.evaluation,
    Feedbacks.created
    FROM
    feedbacks Feedbacks
    WHERE
    Feedbacks.evaluation IS NULL
    AND Feedbacks.created


    BETWEEN '2021-09-16 15:19:21'


    AND ‘2021-09-18 15:19:21'
    取得カラムを


    インデックスに


    合わせる

    View Slide

  176. カバリングインデックスを試す 176
    EXPLAIN
    id: 1
    select_type: SIMPLE
    table: Feedbacks
    partitions: NULL
    type: range
    possible_keys: evaluation_created
    key: evaluation_created
    key_len: 11
    ref: NULL
    rows: 525
    filtered: 100.00
    Extra: Using index
    1 row in set, 1 warning (0.00 sec)
    カバリング


    インデックス

    View Slide

  177. カバリングインデックスを試す 177
    実測
    +------------+---------------------+
    | evaluation | created |
    +------------+---------------------+
    | NULL | 2021-09-16 15:29:36 |
    (தུ)
    | NULL | 2021-09-18 14:53:13 |
    +------------+---------------------+
    525 rows in set (0.00 sec)
    0.06 -> 0.00


    若干高速に

    View Slide

  178. 178
    インデックス


    フルスキャンで


    遅い例

    View Slide

  179. 179
    SQL
    SELECT
    target_id,
    (COUNT(target_id)) AS watchlist_count
    FROM
    watchlists
    WHERE
    target_table_name = 'Users'
    AND created


    BETWEEN '2021-05-01 00:00:00'


    AND ‘2021-05-01 23:59:59'
    GROUP BY
    target_id
    インデックス改善事例

    View Slide

  180. SELECT
    target_id,
    (COUNT(target_id)) AS watchlist_count
    FROM
    watchlists
    WHERE
    target_table_name = 'Users'
    AND created


    BETWEEN '2021-05-01 00:00:00'


    AND ‘2021-05-01 23:59:59'
    GROUP BY
    target_id
    180
    SQL
    ユーザーごとの


    お気に入り数
    インデックス改善事例

    View Slide

  181. SELECT
    target_id,
    (COUNT(target_id)) AS watchlist_count
    FROM
    watchlists
    WHERE
    target_table_name = 'Users'
    AND created


    BETWEEN '2021-05-01 00:00:00'


    AND ‘2021-05-01 23:59:59'
    GROUP BY
    target_id
    181
    SQL とある


    2 日間
    インデックス改善事例

    View Slide

  182. SELECT
    target_id,
    (COUNT(target_id)) AS watchlist_count
    FROM
    watchlists
    WHERE
    target_table_name = 'Users'
    AND created


    BETWEEN '2021-05-01 00:00:00'


    AND ‘2021-05-01 23:59:59'
    GROUP BY
    target_id
    182
    SQL
    target_id ごとに


    グルーピングして


    count で集計
    インデックス改善事例

    View Slide

  183. 183
    実測
    +-----------+-----------------+
    | target_id | watchlist_count |
    +-----------+-----------------+
    | 274 | 1 |
    (தུ)
    | 2515788 | 1 |
    +-----------+-----------------+
    48 rows in set (1 min 46.02 sec)
    1 min 46.02 sec


    非常に遅い
    インデックス改善事例

    View Slide

  184. 184
    watchlists 総レコード数
    +----------+
    | count(1) |
    +----------+
    | 14666790 |
    +----------+
    1 row in set (8.51 sec)
    1466 万


    レコード
    インデックス改善事例

    View Slide

  185. 185
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Watchlists
    partitions: NULL
    type: index
    possible_keys: work_id
    key: work_id
    key_len: 4
    ref: NULL
    rows: 13001409
    filtered: 1.11
    Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    インデックス改善事例

    View Slide

  186. 186
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Watchlists
    partitions: NULL
    type: index
    possible_keys: work_id
    key: work_id
    key_len: 4
    ref: NULL
    rows: 13001409
    filtered: 1.11
    Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    インデックス


    フルスキャン
    インデックス改善事例

    View Slide

  187. 187
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Watchlists
    partitions: NULL
    type: index
    possible_keys: work_id
    key: work_id
    key_len: 4
    ref: NULL
    rows: 13001409
    filtered: 1.11
    Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    検索条件に該当する


    インデックスがなく
    インデックス改善事例

    View Slide

  188. 188
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Watchlists
    partitions: NULL
    type: index
    possible_keys: work_id
    key: work_id
    key_len: 4
    ref: NULL
    rows: 13001409
    filtered: 1.11
    Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    マッチしない


    インデックスを


    選択した
    インデックス改善事例

    View Slide

  189. 189
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: Watchlists
    partitions: NULL
    type: index
    possible_keys: work_id
    key: work_id
    key_len: 4
    ref: NULL
    rows: 13001409
    filtered: 1.11
    Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    1300 / 1466 万


    全体の 88 % が


    検索対象
    インデックス改善事例

    View Slide

  190. 190
    既存インデックス
    mysql> show index from watchlists;
    +------------+------------+-----------------------------+--------------+---------------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name |
    +------------+------------+-----------------------------+--------------+---------------------+
    | watchlists | 0 | PRIMARY | 1 | id |
    | watchlists | 1 | user_id | 1 | user_id |
    | watchlists | 1 | work_id | 1 | work_id |
    | watchlists | 1 | user_id_target_table_name | 1 | user_id |
    | watchlists | 1 | user_id_target_table_name | 2 | target_table_name |
    +------------+------------+-----------------------------+--------------+---------------------+
    5 rows in set (0.00 sec)
    WHERE 句に該当する


    インデックスがない
    インデックス改善事例

    View Slide

  191. 191
    既存インデックス
    mysql> show index from watchlists;
    +------------+------------+-----------------------------+--------------+---------------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name |
    +------------+------------+-----------------------------+--------------+---------------------+
    | watchlists | 0 | PRIMARY | 1 | id |
    | watchlists | 1 | user_id | 1 | user_id |
    | watchlists | 1 | work_id | 1 | work_id |
    | watchlists | 1 | user_id_target_table_name | 1 | user_id |
    | watchlists | 1 | user_id_target_table_name | 2 | target_table_name |
    +------------+------------+-----------------------------+--------------+---------------------+
    5 rows in set (0.00 sec)
    オプティマイザが


    この中でもっともコストの低い


    インデックスを選択
    インデックス改善事例

    View Slide

  192. インデックス追加で改善事例 192
    ✔︎
    不適切なインデックス を選択


    → 既存インデックスが WHERE 句にマッチしない


    ✔︎
    インデックス内を フルスキャン


    → パフォーマンス低下
    インデックス追加、EXPLAIN、計測
    インデックスフルスキャンで遅い

    View Slide

  193. 193
    インデックス追加
    mysql> ALTER TABLE watchlists ADD INDEX
    target_table_name_created_target_id(
    -> target_table_name,
    -> created,
    -> target_id
    -> )
    -> ;
    Query OK, 0 rows affected (30.82 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    インデックス改善事例

    View Slide

  194. 194
    インデックス追加
    mysql> ALTER TABLE watchlists ADD INDEX
    target_table_name_created_target_id(
    -> target_table_name,
    -> created,
    -> target_id
    -> )
    -> ;
    Query OK, 0 rows affected (30.82 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    インデックス改善事例
    WHERE 句の


    条件

    View Slide

  195. 195
    インデックス追加
    mysql> ALTER TABLE watchlists ADD INDEX
    target_table_name_created_target_id(
    -> target_table_name,
    -> created,
    -> target_id
    -> )
    -> ;
    Query OK, 0 rows affected (30.82 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    インデックス改善事例
    ORDER BY 句の


    条件

    View Slide

  196. 196
    SQL の実行順序
    インデックス改善事例
    1. FROM


     ↓


    2. ON


     ↓


    3. JOIN


     ↓


    4. WHERE


     ↓
    5. GROUP BY


     ↓


    6. HAVING


     ↓


    7. SELECT


     ↓


    8. ORDER BY


     ↓


    9. LIMIT

    View Slide

  197. 197
    インデックス追加
    mysql> ALTER TABLE watchlists ADD INDEX
    target_table_name_created_target_id(
    -> target_table_name,
    -> created,
    -> target_id
    -> )
    -> ;
    Query OK, 0 rows affected (30.82 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    インデックス改善事例
    SQL の実行順に


    合わせる

    View Slide

  198. 198
    実測
    +-----------+-----------------+
    | target_id | watchlist_count |
    +-----------+-----------------+
    | 274 | 1 |
    | 2515788 | 1 |
    +-----------+-----------------+
    549 rows in set (0.00 sec)
    インデックス改善事例

    View Slide

  199. +-----------+-----------------+
    | target_id | watchlist_count |
    +-----------+-----------------+
    | 274 | 1 |
    | 2515788 | 1 |
    +-----------+-----------------+
    549 rows in set (0.00 sec)
    199
    実測
    インデックス改善事例
    1 min 46.02 sec


    から改善

    View Slide

  200. 200
    EXPLAIN
    ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: range
    possible_keys: work_id,target_table_name_created_target_id
    key: target_table_name_created_target_id
    key_len: 774
    ref: NULL
    rows: 635
    filtered: 100.00
    Extra: Using where; Using index; Using temporary; Using
    filesort
    1 row in set, 1 warning (0.00 sec)
    インデックス改善事例

    View Slide

  201. ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: range
    possible_keys: work_id,target_table_name_created_target_id
    key: target_table_name_created_target_id
    key_len: 774
    ref: NULL
    rows: 635
    filtered: 100.00
    Extra: Using where; Using index; Using temporary; Using
    filesort
    1 row in set, 1 warning (0.00 sec)
    201
    EXPLAIN
    インデックスでの


    範囲 (range) 検索
    インデックス改善事例

    View Slide

  202. ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: range
    possible_keys: work_id,target_table_name_created_target_id
    key: target_table_name_created_target_id
    key_len: 774
    ref: NULL
    rows: 635
    filtered: 100.00
    Extra: Using where; Using index; Using temporary; Using
    filesort
    1 row in set, 1 warning (0.00 sec)
    202
    EXPLAIN
    作ったインデックス
    が選択された
    インデックス改善事例

    View Slide

  203. ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: range
    possible_keys: work_id,target_table_name_created_target_id
    key: target_table_name_created_target_id
    key_len: 774
    ref: NULL
    rows: 635
    filtered: 100.00
    Extra: Using where; Using index; Using temporary; Using
    filesort
    1 row in set, 1 warning (0.00 sec)
    203
    EXPLAIN
    絞り込み行数が


    実際の検索行数に


    近くなった
    インデックス改善事例

    View Slide

  204. ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: range
    possible_keys: work_id,target_table_name_created_target_id
    key: target_table_name_created_target_id
    key_len: 774
    ref: NULL
    rows: 635
    filtered: 100.00
    Extra: Using where; Using index; Using temporary; Using
    filesort
    1 row in set, 1 warning (0.00 sec)
    204
    EXPLAIN
    カバリングインデックス


    で検索できている
    インデックス改善事例

    View Slide

  205. ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: range
    possible_keys: work_id,target_table_name_created_target_id
    key: target_table_name_created_target_id
    key_len: 774
    ref: NULL
    rows: 635
    filtered: 100.00
    Extra: Using where; Using index; Using temporary; Using
    filesort
    1 row in set, 1 warning (0.00 sec)
    205
    EXPLAIN
    Using temporaty,


    Using
    fi
    lesort
    インデックス改善事例

    View Slide

  206. ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: range
    possible_keys: work_id,target_table_name_created_target_id
    key: target_table_name_created_target_id
    key_len: 774
    ref: NULL
    rows: 635
    filtered: 100.00
    Extra: Using where; Using index; Using temporary; Using
    filesort
    1 row in set, 1 warning (0.00 sec)
    206
    EXPLAIN
    GROUP BY による


    暗黙の ORDER BY


    によるもの
    インデックス改善事例

    View Slide

  207. ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: range
    possible_keys: work_id,target_table_name_created_target_id
    key: target_table_name_created_target_id
    key_len: 774
    ref: NULL
    rows: 635
    filtered: 100.00
    Extra: Using where; Using index; Using temporary; Using
    filesort
    1 row in set, 1 warning (0.00 sec)
    207
    EXPLAIN
    rows と合わせて


    パフォーマンスに


    影響なし
    インデックス改善事例

    View Slide

  208. 208
    GROUP BY を外した SQL
    SELECT
    target_id
    FROM
    watchlists
    WHERE
    target_table_name = 'Users'
    AND created


    BETWEEN '2021-05-01 00:00:00'


    AND ‘2021-05-01 23:59:59'
    インデックス改善事例
    GROUP BY を


    外して


    EXPLAIN 確認

    View Slide

  209. 209
    GROUP BY を外した SQL
    SELECT
    target_id
    FROM
    watchlists
    WHERE
    target_table_name = 'Users'
    AND created


    BETWEEN '2021-05-01 00:00:00'


    AND ‘2021-05-01 23:59:59'
    インデックス改善事例
    集計関数 count


    も外す

    View Slide

  210. ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: range
    possible_keys: target_table_name_created_target_id
    key: target_table_name_created_target_id
    key_len: 774
    ref: NULL
    rows: 635
    filtered: 100.00
    Extra: Using where; Using index
    1 row in set, 1 warning (0.00 sec)
    210
    GROUP BY を外した EXPLAIN
    インデックス改善事例

    View Slide

  211. ************************* 1. row *************************
    id: 1
    select_type: SIMPLE
    table: watchlists
    partitions: NULL
    type: range
    possible_keys: target_table_name_created_target_id
    key: target_table_name_created_target_id
    key_len: 774
    ref: NULL
    rows: 635
    filtered: 100.00
    Extra: Using where; Using index
    1 row in set, 1 warning (0.00 sec)
    211
    GROUP BY を外した EXPLAIN
    Using temporaty,


    Using
    fi
    lesort が


    消える
    インデックス改善事例

    View Slide

  212. EXPLAIN の主な見方


    インデックス追加で改善事例


    インデックスの管理
    SQL の分析・改善手法 212
    1
    2
    2
    2
    1
    1
    3

    View Slide

  213. 213
    未使用 インデックス確認・削除
    mysql> SELECT
    -> *
    -> FROM
    -> sys.schema_unused_indexes
    -> WHERE
    -> object_schema = 'hoge'
    -> AND object_name = 'watchlists'\G
    ********************** 1. row **********************
    object_schema: hoge
    object_name: watchlists
    index_name: user_id_target_table_name
    1 row in set (0.05 sec)
    インデックスの管理

    View Slide

  214. ✔︎
    本番環境で確認 する


      ・ステージングと本番の アクセス状況の違い


      ・そもそもアクセスが少ないと利用されない


    ✔︎
    master と replica 全台 で調べる


      ・負荷分散で SELECT は replica の場合を考慮


      ・つまり replica では使用中 の場合がある
    214
    未使用インデックスの注意点
    インデックスの管理

    View Slide

  215. 215
    重複 インデックス確認・削除
    mysql> SELECT
    -> *
    -> FROM
    -> sys.schema_redundant_indexes
    -> WHERE
    -> table_name = 'watchlists'\G
    *************************** 1. row ***************************
    table_schema: hoge
    table_name: watchlists
    redundant_index_name: user_id
    redundant_index_columns: user_id
    redundant_index_non_unique: 1
    dominant_index_name: user_id_target_table_name
    dominant_index_columns: user_id,target_table_name
    dominant_index_non_unique: 1
    subpart_exists: 0
    sql_drop_index: ALTER TABLE `hoge`.`watchlists` DROP INDEX `user_id`
    1 row in set (0.35 sec)
    インデックスの管理

    View Slide

  216. インデックス改善事例・まとめ (1) 216
    ✔︎
    複合的に判断


    → type, key, rows, Extra, etc


    ✔︎
    インデックスを作成


    → WHERE 句にマッチ、SQL 実行順に対応して作成
    パフォーマンス 計測・改善
    EXPLAIN を見る

    View Slide

  217. インデックス改善事例・まとめ (2) 217
    ✔︎
    検索対象 (rows) を判断


    → インデックスでもフルスキャンは遅い


    ✔︎
    仕様を見直す ことも重要


    → 多すぎる場合は条件追加・変更が必要
    ユーザーファースト
    インデックスを 過信しない

    View Slide

  218. インデックス改善事例・まとめ (3) 218
    ✔︎
    書き込み性能 に影響する


    → インデックスは INSERT, UPDATE, DELETE 時に追加


    ✔︎
    ストレージ容量 が増える


    → RDS だからといってリソースを無駄遣いしない
    必要に応じた チューニング
    インデックスを 無闇に増やさない

    View Slide

  219. お話しすること
    スロークエリを 改善する理由


    現場で起きている こと


    スロークエリの 見える化


    SQL の 分析・改善手法(一例)


    スロークエリ 予防


    SQL から始まる 本質の追求
    219

    View Slide

  220. スロークエリ予防 220
    1
    2
    2
    レビュー(コード・設計・要件)


    ペアプロ


    これからの課題
    2
    1
    1
    3

    View Slide

  221. スロークエリ予防 221
    1
    2
    2
    レビュー(コード・設計・要件)


    ペアプロ


    これからの課題
    2
    1
    1
    3

    View Slide

  222. レビュー(コード・設計・要件) 222
    ✔︎
    SQL から要件 まで


    → 本当にその検索条件でいいのか


    ✔︎
    原点に返ろう


    → 正規化、テーブル分割、論理削除、レコード増加傾向、etc
    水面下の パフォーマンス劣化を防止
    障害対応ノウハウを活かし レビュー

    View Slide

  223. ✔︎
    正規化 できないか


      ・カラム追加ではなく別の役割では?


    ✔︎
    テーブル分割 できないか


      ・ポリモーフィック関連テーブルの偏り


    ✔︎
    本当に論理削除 が必要か?


      ・ステータスでは?別テーブルに待避では?
    223
    レビュー観点例 (1/2)
    レビュー(コード・設計・要件)

    View Slide

  224. ✔︎
    レコード 増加傾向を予測 しているか


      ・先々を予測した検索条件か


    ✔︎
    本当に RDB に必要か?


      ・ログなら分析基盤、履歴なら活用手法確認


    ✔︎
    古いレコードは アーカイブ できないか


      ・レコードが特に多いテーブルの場合に考慮
    224
    レビュー観点例 (2/2)
    レビュー(コード・設計・要件)

    View Slide

  225. スロークエリ予防 225
    1
    2
    2
    レビュー(コード・設計・要件)


    ペアプロ


    これからの課題
    2
    1
    1
    3

    View Slide

  226. ペアプロ 226
    ✔︎
    画面共有 して分析から改善まで


    → EXPLAIN 解説からインデックス作成、実測まで


    ✔︎
    SQL への 敷居を低くしよう


    → 知ってる人が伝えていくスタイル、恩は送ろう
    個人の底上げは チームの底上げ
    実例は最高の学び

    View Slide

  227. ✔︎
    なるべく 1次情報を示す・残す


      ・揺らぎない技術提供、復習のしやすさ


    ✔︎
    意訳を交え 相手に寄り添う


      ・言葉の説明も交えながら進める


    ✔︎
    説明できないことは 一緒に探す


      ・ごまかさない、放置が一番危険
    227
    気をつけていること
    ペアプロ

    View Slide

  228. スロークエリ予防 228
    1
    2
    2
    レビュー(コード・設計・要件)


    ペアプロ


    これからの課題
    2
    1
    1
    3

    View Slide

  229. ✔︎
    社内勉強会


      ・ノウハウの共有、更なる品質向上、楽しさ


    ✔︎
    コード特定自動化、スロークエリ分析


      ・よりスピーディに、より先手を打つ


    ✔︎
    PR の 新規 SQL を CI/CD で事前検知


      ・https://github.com/pyama86/welcome-new-query
    229
    これからの課題
    スロークエリ予防

    View Slide

  230. お話しすること
    スロークエリを 改善する理由


    現場で起きている こと


    スロークエリの 見える化


    SQL の 分析・改善手法(一例)


    スロークエリ 予防


    SQL から始まる 本質の追求
    230

    View Slide

  231. SQL から始まる本質の追求 231
    1
    2
    2
    要求と実装のはざま


    本質解決への流れ


    PjM ・ PdM にヒアリング


    実現の代替案を検討・提案


    一緒に実装
    2
    1
    1
    3
    4
    5

    View Slide

  232. SQL から始まる本質の追求 232
    1
    2
    2
    要求と実装のはざま


    本質解決への流れ


    PjM ・ PdM にヒアリング


    実現の代替案を検討・提案


    一緒に実装
    2
    1
    1
    3
    4
    5

    View Slide

  233. ✔︎
    ユーザー価値から 剥離


      ・半端な機能でリリース


    ✔︎
    要件と実装の ズレ


      ・品質維持に苦労 する


    ✔︎
    無茶な SQL やテーブルが生まれる


      ・そんな検索条件で大丈夫?その JOIN 必要?
    233
    こんなことありませんか?
    実装と要求のはざま

    View Slide

  234. ✔︎
    納期に追われ リソース不足


      ・スケジュール調整の決断が難しく見直さない


    ✔︎
    設計不足で 運用保守がしんどい


      ・リリース後の障害で後手に回る


    ✔︎
    なんでも SQL でやろうとする


      ・足りない工数を無理に補って品質低下
    234
    なにが起きているか
    実装と要求のはざま

    View Slide

  235. 235
    要件と実装のはざまで


    苦しい

    View Slide

  236. 236
    どう アプローチ


    すればいいのか

    View Slide

  237. SQL から始まる本質の追求 237
    1
    2
    2
    要求と実装のはざま


    本質解決への流れ


    PjM ・ PdM にヒアリング


    実現の代替案を検討・提案


    一緒に実装
    2
    1
    1
    3
    4
    5

    View Slide

  238. ✔︎
    PjM, PdM に ヒアリング・相談


      ・要件確認、現状報告、未来予想、軌道修正


    ✔︎
    実現の 代替案 を検討・提案


      ・否定ではなく 他の方法で実現 を目指す


    ✔︎
    一緒に実装


      ・指摘するだけではなく、自ら解決へ 動く
    238
    本質解決への流れ

    View Slide

  239. 239
    指摘だけじゃなく


    一緒に改善

    View Slide

  240. SQL から始まる本質の追求 240
    1
    2
    2
    要求と実装のはざま


    本質解決への流れ


    PjM ・ PdM にヒアリング


    実現の代替案を検討・提案


    一緒に実装
    2
    1
    1
    3
    4
    5
    3
    2

    View Slide

  241. 241
    ✔︎
    指摘事項とその理由の 説明


    ✔︎
    具体的な要件、期限とその理由


    ✔︎
    ユーザーは 何が嬉しいのか


    ✔︎
    リソース 不足の理由


    ✔︎
    リスケ可能か、可能ならいつまでか


    ✔︎
    機能の中での実装の 優先順位
    PjM ・ PdM にヒアリング
    PjM にヒアリング

    View Slide

  242. 242
    ✔︎
    指摘事項が品質にどう影響するか 説明


    ✔︎
    要件と実装の ズレを是正


    ✔︎
    ユーザーに提供できる 価値確認


    ✔︎
    リソース認識 合わせ


    ✔︎
    リスケ判断、品質確保体制
    PjM ・ PdM にヒアリング
    PdM にヒアリング

    View Slide

  243. 243
    ヒアリングせず


    放置したら…

    View Slide

  244. 244
    PjM ・ PdM にヒアリング
    ヒアリングをせず放置したら
    ✔︎
    あれもこれも 1 機能 に


      ・ファットな画面・SQL で品質低下


    ✔︎
    リリース優先で 要件からズレ ていく


      ・ユーザーへの価値提供が歪む


    ✔︎
    機能要件と非機能要件が 混在


      ・ログテーブル、ファットカラム、etc

    View Slide

  245. 245
    妥協は


    水面下での


    パフォーマンス劣化の


    始まり

    View Slide

  246. 246
    層を超えた


    ヒアリングを

    View Slide

  247. SQL から始まる本質の追求 247
    1
    2
    2
    要求と実装のはざま


    本質解決への流れ


    PjM ・ PdM にヒアリング


    実現の代替案を検討・提案


    一緒に実装
    2
    1
    1
    3
    4
    5
    3
    2
    3
    4

    View Slide

  248. 実現の代替案を検討・提案 248
    ✔︎
    指摘と提案はセット


    → みんなチーム、互いの重なりを広げよう


    ✔︎
    チーム横断で実現案を考える


    → インフラ、テックリード、マネージャー、etc
    代替案で 一緒に実現を目指す
    打ち返しでは 解決しない

    View Slide

  249. 実例:実現の代替案を検討・提案 249
    ✔︎
    ディレクターが SQL で分析したい


    → 逆に SQL で分析できるならなんでもいい


    ✔︎
    S3 -> BigQuery 経由の分析基盤


    → 他チームで開発中で、ちょうど把握していた
    API 実装を提案、ログ基盤へ蓄積
    行動 ログをテーブルに保存 したい

    View Slide

  250. SQL から始まる本質の追求 250
    1
    2
    2
    要求と実装のはざま


    本質解決への流れ


    PjM ・ PdM にヒアリング


    実現の代替案を検討・提案


    一緒に実装
    2
    1
    1
    3
    4
    5
    3
    2
    3
    5

    View Slide

  251. 一緒に実装 251
    ✔︎
    プロトタイプ作成


    → 実装を後ろから支え、品質保証


    ✔︎
    パフォーマンス検証


    → データを用意しパフォーマンステスト
    戦う兵站部隊 で 背中を任せてもらう
    後ろを支えて ともに戦う

    View Slide

  252. 実例:一緒に実装 252
    ✔︎
    タイムラグは 許容できない


    → 要件確認、ElasticSearch は使えない


    ✔︎
    fulltext index 環境を作りテスト


    → 実データと同じ環境を作り、パフォーマンス検証
    開発を止めずに フィードバック
    リアルタイム全文検索が欲しい

    View Slide

  253. 253
    0 → 1 が得意


    100 → 200 が得意


    ひとそれぞれ

    View Slide

  254. 254
    役割を認識して


    自分から動く

    View Slide

  255. 255
    自分から動くから


    相手も動いてくれる

    View Slide

  256. 256
    ここまでやって


    初めて DBRE と


    言える

    View Slide

  257. 257
    おわりに

    View Slide

  258. 258
    SQL 1つで 障害になる





    SQL 1つで 品質が上がる

    View Slide

  259. 259
    SQL から遡れば


    ユーザーにたどり着く

    View Slide

  260. 260
    クエリチューニングして


    改善しよう

    View Slide

  261. 261
    SQL


    怖い😱 -> やれるかも💡

    View Slide

  262. 262
    SQL への


    敷居を低くして

    View Slide

  263. 263
    SQL 見てみよう


    EXPLAIN してみよう


    インデックス 作ろう

    View Slide

  264. 264

    View Slide

  265. 質問はこちら - 1 265
    どんなࠣ細な内容も大歓迎!
    https://joind.in/talk/0b395

    View Slide

  266. 質問はこちら - 2 266
    ✔︎
    Discord


    #track2-2-mysql-tune


    ✔︎
    Twitter


    @mamy1326

    View Slide

  267. SQL! EXPLAIN! インデックス!
    #F0F0F0 #666666 #E6855E #5EC84E
    #F0F0F0  あいうえおかきくけこさしすせそ


    #E6855E   あいうえおかきくけこさしすせそ


    #5EC84E  あいうえおかきくけこさしすせそ

    View Slide