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

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. Twitter Work Like ✔︎ 2017年の趣味:MySQL ✔︎ 2018年の趣味:DNS ✔︎ 2020年の趣味:CakePHP4 ✔︎

    2021年の趣味:DBRE :@mamy1326(まみー) :Lancers,Inc. @ PHPer : cune.jp 自己紹介 2
  2. ✔︎ バックエンド エンジニア   →どちらかというと SQL は ORM 任せ ✔︎ SQL

    のパフォーマンスで 困っている   →でも 調べ方がよくわからない ✔︎ SQL が 正直怖い   →改善の 初手を知りたい 想定オーディエンスのみなさま 5
  3. プロローグ 6 ✔︎ 品質の 均一化 → 誰が書いても同じ SQL が実行される ✔︎

    開発効率 の向上 → SQL を書かずに開発できる SQL を 意識の外に置く 機会が増えた 近年の ORM は高性能
  4. 起きていたこと 11 ✔︎ スロークエリで パフォーマンス低下 → DB サーバーへの負荷が上昇 ✔︎ Web

    サーバーへの レスポンス低下 → ページ表示速度の低下 サービスの 品質低下 改善が必要な SQL の実行
  5. ✔︎ どんな SQL を実行 しているのか   → 実際の速度と頻度はどうなのか ✔︎ コードのどこ なのか

      → ORM 利用だと特定に時間がかかる ✔︎ どう改善すれば いいのか   → 解析方法は何があるのか 障害発生時に知りたいこと 13
  6. ✔︎ スローログ 監視   → 常に状況を把握 ✔︎ スロークエリ 改善   → 原因特定、影響範囲の把握、改善対応

    ✔︎ スロークエリ 予防   → 監視とレビュー エンジニアに求められること 14
  7. 目指すこと 16 ✔︎ SQL を 普段から見る → 自分の実装の影響を把握する ✔︎ 日常的に予防

    していく → 改善を回していく 安定した 品質を確保 後手から 先手へ
  8. 前提の環境 Amazon Aurora 20 mysql> select AURORA_VERSION() ; +------------------ +

    | AURORA_VERSION() | +------------------ + | 2.09.2 | +------------------ + 1 row in set (0.00 sec)
  9. 前提の環境 MySQL Engine 21 mysql> select version() ; +------------ +

    | version() | +------------ + | 5.7.12-log | +------------ + 1 row in set (0.00 sec)
  10. 前提の環境 スロークエリ検出時間 22 mysql> show variables like 'long%' ; +-----------------+-----------

    + | Variable_name | Value | +-----------------+----------- + | long_query_time | 10.000000 | +-----------------+----------- + 1 row in set (0.01 sec)
  11. ✔︎ ORM で十分 使えている   → 開発者は SQL 見なくていい ✔︎ 負荷増加でも

    お金で解決 できる   → オートスケール、スケールアップなど ✔︎ 障害対応 できている   → 発生は稀 だし サービスは継続できている こういうことありませんか? 24
  12. ✔︎ ORM で十分 使えている   → 開発者は SQL 見なくていい ✔︎ お金で解決

    できる   → オートスケール、スケールアップなど ✔︎ 障害対応 できている   → 発生は稀 だし サービスは継続できている こういうことありませんか? 25 スロークエリを 放置している
  13. スロークエリを放置すると… (1) 26 ✔︎ 慢性的な 品質低下 → 新規ユーザー獲得困難 ✔︎ サービス

    信用失墜 → 既存ユーザー離脱 売上・利益の低下、競争敗北 障害が慢性的 に発生
  14. スロークエリを放置すると… (2) 27 ✔︎ 攻撃者の的 になる → 遅い SQL があると予測できる

    ✔︎ Dos 攻撃 を受ける → DB サーバーの応答品質低下、Web サーバーも… 攻撃 によるサービス 品質低下・停止 特定の 機能が遅い
  15. ✔︎ サービス 品質向上   → SQL の改善 1つで品質は上がる ✔︎ SQL チェック習慣化で

    事前予防   → 障害のノウハウを日常のレビューへ ✔︎ コスト・技術の パフォーマンス向上   → 事業の安定化、チームの技術力底上げ スロークエリを改善 する 理由 30
  16. 原因 35 ✔︎ 開発環境では レコードを間引く → ストレージ容量、転送量の対策 ✔︎ 実は本番環境だと 遅い

    SQL → 開発時は数万レコード、本番は数千万レコード 本番環境 で レスポンスが低下 本番と開発環境の レコード数の差
  17. 実例 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) 本番環境
  18. 実例 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 倍
  19. 対策 39 ✔︎ 本番とほぼ同じ レコード数 → DB サーバーは本番と同じインスタンス ✔︎ リリース前に

    必ずテスト → 機能だけではなく、SQL 単体でも計測 性能を確保 してリリース ステージング環境 を作る
  20. Tips 40 ✔︎ 遅くて 待ちきれないのでタブ閉じ → SQL の実行を停止したと思っていても… ✔︎ プロセスが残る

    場合がある → 例:phpMyAdmin はタブを閉じても kill されない 軽い気持ちが本番障害 につながる ステージングないなら 本番で実行…
  21. Tips 41 ✔︎ 遅くて 待ちきれないのでタブ閉じ → SQL の実行を停止したと思っていても… ✔︎ プロセスが残る

    場合がある → 例:phpMyAdmin はタブを閉じても kill されない 軽い気持ちが本番障害 につながる ステージングないなら 本番で実行… 必ず ステージングで 実施しましょう
  22. 現場で起きていること 43 ✔︎ 10 - 19 時 は 問題なかった →

    ページ表示も高速 ✔︎ テストも通っている → プログラム品質も問題なし 特定の時間帯 で レスポンス低下 リリース時 は問題なく動作
  23. 原因 1:潜在的なスロークエリ 44 ✔︎ 遅い SQL でも 十分動いていた → リリース時は利用者が少なく速度低下はなかった

    ✔︎ ピーク時間帯に DB 負荷増加 → 例:1 sec の SQL がだんだんと遅くなりᮢ値を超える Webサーバー の レスポンスが低下 ピーク時間帯 の負荷増加
  24. 予防例:潜在的なスロークエリ 48 ✔︎ レビュー時に全ての SQL を洗い出す   → 1つの PR だと

    SQL の 数は限られる ✔︎ ステージング環境で EXPLAIN / 実測   → 遅い SQL がないか 1つずつチェック ✔︎ レビュー指摘項目を Wiki 化   → PR にリンク添付、ノウハウの蓄積と展開
  25. 対策:潜在的なスロークエリ 49 ✔︎ EXPLAIN (実行計画) を確認 → 遅くなる SQL を事前に改善

    ✔︎ (できれば) 負荷テスト を実施 → ピーク時の負荷に耐えられることを確認 潜在 -> 顕在 へ
  26. 対策:潜在的なスロークエリ 50 ✔︎ EXPLAIN (実行計画) を確認 → 遅くなる SQL を事前に改善

    ✔︎ (できれば) 負荷テスト を実施 → ピーク時の負荷に耐えられることを確認 性能を確保 してリリース 潜在 -> 顕在 へ
  27. 原因 1:レコード数のᮢ値超過 53 ✔︎ レコード数:数万 -> 数100万 → リリース時は数万、インデックスも効いていた ✔︎

    検索対象の 割合増加 → フルスキャンの方が効率的と判断される 前触れなくレスポンスが低下 フルスキャン 発生
  28. 実例 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’
  29. 実例 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 倍 半年前の 一覧を検索
  30. 実例 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 年前と 現在を比較
  31. 実例 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
  32. 実例 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
  33. 実例 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
  34. 実例 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
  35. 実例 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
  36. 実例 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 遅い!
  37. 対策:レコード数のᮢ値超過 64 ✔︎ 範囲を さらに絞る → 1ヶ月単位で表示など、要件も見直す ✔︎ 他の 条件を追加

    → ユーザーの検索傾向を掴み、要求に応じる 定常的にレスポンスが安定 絞り込み 条件を追加
  38. 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ヶ月単位で 絞り込む
  39. 実際に範囲を絞った結果 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
  40. 実際に範囲を絞った結果 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
  41. 70 ・お気に入りを管理 するテーブル   → 多 対 多 ・複数のテーブル に対応   →

    仕事、ユーザー、パッケージ、etc… ・対応する テーブル名をカラム で持つ   → ポリモーフィック関連テーブル 実例 2:カーディナリティの偏り 例えばこんな要件
  42. 実例 2:カーディナリティの偏り 74 偏りを見る SQL SELECT count(id) AS counter, target_table_name

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

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

    FROM watchlists GROUP BY target_table_name ORDER BY counter DESC お気に入り数で ORDER BY
  45. +---------+----------------------+ | 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)
  46. +---------+----------------------+ | 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) 当初は 偏りは少なかった
  47. +---------+----------------------+ | 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) 現在は 大きな偏り
  48. +---------+----------------------+ | 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 で 計測
  49. 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 を検索
  50. +---------+---------+ | 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 の 結果
  51. +---------+---------+ | 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 の 結果
  52. +---------+---------+ | 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 の 結果
  53. **************** 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
  54. 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 パフォーマンスに 問題はない インデックスが 利用されているけど
  55. 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 対象行数が多く 遅くなる可能性
  56. 対策:カーディナリティの偏り 89 ✔︎ もはや 別の要素 → いずれ全体的にパフォーマンスが低下する可能性 ✔︎ 元々 分けるべき設計

    → ポリモーフィック関連テーブルはアンチパターン パフォーマンスの 影響範囲を絞る 偏ったレコードは 別テーブルへ
  57. ✔︎ ステージング で確認   → 性能を確保 してリリース ✔︎ 潜在的 スロークエリを顕在化   →

    EXPLAIN 実行で 事前に解決 ✔︎ レコード 増加数を予測   → 予測した検索条件、テーブル分割 現場で起きていること 91 現場でやるべきこと
  58. SqlLog の実際の利用例 98 ✔︎ 1つずつ 実測 → リリース後の速度をあらかじめ計測 ✔︎ 1つずつ

    EXPLAIN → 潜在的なスロークエリを見つけて、改善 性能を確保 してリリース 列挙された SQL を ステージングで確認
  59. 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 忘れずに
  60. MySQL のスローログ 107 ✔︎ CloudWatch から Slack へ → Lambda

    でサクッと実装(事例がたくさんある) ✔︎ DataDog などで コード特定 → スロークエリを実行している場所がわかる スピーディな対応 を可能に 更に可視化する
  61. 発見から改善までの流れ 111 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で

    絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
  62. 発見から改善までの流れ 112 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で

    絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
  63. 発見から改善までの流れ 113 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で

    絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
  64. 発見から改善までの流れ 114 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で

    絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
  65. 発見から改善までの流れ 115 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で

    絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
  66. 発見から改善までの流れ 116 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で

    絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
  67. 発見から改善までの流れ 117 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で

    絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
  68. スロークエリの見える化の課題 (1) 120 ✔︎ 特定の手作業が不要に → 短いと 30 分以内、長いと数時間がゼロに ✔︎

    通知時に コード特定 → ピンポイントで修正箇所がわかる (DataDog など) スピーディ・正確な対応 を可能に コード 特定の自動化
  69. 発見から改善までの流れ 121 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で

    絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認 ここが
  70. スロークエリの見える化の課題 (2) 122 ✔︎ 回数、時間、割合の集計 → スロークエリ全体の中での SQL の割合、回数、合計時間 ✔︎

    数値の高い SQL から順に対応 → より効果の高い対応が可能 (pt-query-digest) 先手対応 が可能に スロークエリ 分析の可視化
  71. 発見から改善までの流れ 123 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で

    絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認 発生率が
  72. ✔︎ 実装した SQL を列挙・確認   → フレームワークのデバッガで見る ✔︎ スロークエリ 通知で SQL

    特定   → 原因の見える化で 確実・スピーディな対応 ✔︎ コード特定、分析で 先手を打つ   → DataDog、pt-query-digest など検討 スロークエリの見える化 124 まとめ
  73. 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
  74. 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 と 書くだけ
  75. 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 を処理 するかがわかる
  76. 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つを お話しします
  77. ✔︎ 複合的 に見る   ・type, rows, Extra など 合わせて判断 ✔︎ インデックスの使われ方

    を見る   ・インデックスが 足りない   ・インデックスでは 検索しきれない   ・対象が多い EXPLAIN の主な見方 135 前提
  78. ・range:注意   → インデックスを使った 範囲検索   → 対象行数 rows が十分 絞られていれば高速   →

    rows の 割合が多い場合 は改善が必要 ・const:安心   → PK または UK で 1行 または 0行 を取得   → 等価比較で 最高速 EXPLAIN の主な見方 137 type
  79. ・オプティマイザが 選択したインデックス   → 使われるインデックス   → 実行時間が最小 になるよう処理方法を決める   → その 過程で選択される

    ・狙いと違う インデックスなら注意   → インデックスの 実態が最適化に沿っていない   → 統計情報の再作成 など対策が必要な場合も EXPLAIN の主な見方 138 key
  80. ・検索対象の レコード数   → 絞りたかった数と 同じか近い ことを確認 ・想定より 多い場合は注意   → パフォーマンス低下

    の可能性   → 絞り込みが足りない   → 当初よりレコード数が増えている EXPLAIN の主な見方 139 rows
  81. ・Using index condition (ICP):高速   → 複合インデックスを より効率的に使う   → 余計なレコード検索を 減らす

      → MySQL 5.6 からの機能   → ただし rows の割合が多い場合は注意 EXPLAIN の主な見方 141 Extra
  82. ・Using fi lesort:注意   → JOIN と ORDER BY の組み合わせでよく見る   →

    メモリとファイルでクイックソート   → 駆動表と ORDER BY が別テーブルの場合   → GROUP BY による暗黙の ORDER BY でも出る   → LIMIT 前のレコード数が多ければ遅い   → Using temporary が一緒に出ることが多い EXPLAIN の主な見方 142 Extra
  83. ✔︎ 複合的 に見る   ・type, rows, Extra など 合わせて判断 ✔︎ インデックスの使われ方

    を見る   ・インデックスが 足りない   ・インデックスでは 検索しきれない   ・対象が多い EXPLAIN の主な見方 143 前提(再掲)
  84. 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' インデックス改善事例
  85. 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' インデックス改善事例 仕事に対する 評価のテーブル
  86. 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' インデックス改善事例 評価の値が まだない人
  87. 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日間
  88. 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) インデックス改善事例
  89. 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) インデックス改善事例
  90. 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 インデックス改善事例
  91. 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 フルスキャン インデックス改善事例
  92. 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 %) インデックス改善事例
  93. 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) インデックス改善事例
  94. 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 句に該当する インデックスがない
  95. 157 ✔︎ もっとも単純 → インデックス追加で大抵は高速に ✔︎ なるべく 単純な SQL を目指す

    → 絞り込みに無理はないか、要件は妥当か インデックス追加、EXPLAIN、計測 インデックスが 無くて遅い インデックス改善事例
  96. 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 インデックス改善事例
  97. 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) インデックス改善事例 十分な パフォーマンス
  98. 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) インデックス改善事例
  99. 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) 検索 インデックス改善事例
  100. 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) 作ったインデックス が選択された インデックス改善事例
  101. 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) 絞り込み行数が 実際の検索行数へ インデックス改善事例
  102. 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) インデックスを使っ た検索ができている インデックス改善事例
  103. 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 インデックス改善事例
  104. 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) インデックス改善事例
  105. 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 (追加情報) インデックス改善事例
  106. 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 時に 常に表示される インデックス改善事例
  107. 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) オプティマイザの 最適化の際のメモ インデックス改善事例
  108. 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 を 書き換えたよ インデックス改善事例
  109. 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 関数に 書き換え インデックス改善事例
  110. ・Using index:高速   → カバリングインデックスなど   → インデックスのみで検索 が完結する   → ただし rows

    の割合が多い場合は注意 EXPLAIN の主な見方 173 Extra (再掲) カバリングインデックスを
  111. カバリングインデックスを試す 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)
  112. カバリングインデックスを試す 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' 取得カラムを インデックスに 合わせる
  113. カバリングインデックスを試す 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) カバリング インデックス
  114. カバリングインデックスを試す 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 若干高速に
  115. 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 インデックス改善事例
  116. 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 ユーザーごとの お気に入り数 インデックス改善事例
  117. 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 日間 インデックス改善事例
  118. 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 で集計 インデックス改善事例
  119. 183 実測 +-----------+-----------------+ | target_id | watchlist_count | +-----------+-----------------+ |

    274 | 1 | (தུ) | 2515788 | 1 | +-----------+-----------------+ 48 rows in set (1 min 46.02 sec) 1 min 46.02 sec 非常に遅い インデックス改善事例
  120. 184 watchlists 総レコード数 +----------+ | count(1) | +----------+ | 14666790

    | +----------+ 1 row in set (8.51 sec) 1466 万 レコード インデックス改善事例
  121. 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) インデックス改善事例
  122. 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) インデックス フルスキャン インデックス改善事例
  123. 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) 検索条件に該当する インデックスがなく インデックス改善事例
  124. 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) マッチしない インデックスを 選択した インデックス改善事例
  125. 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 % が 検索対象 インデックス改善事例
  126. 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 句に該当する インデックスがない インデックス改善事例
  127. 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) オプティマイザが この中でもっともコストの低い インデックスを選択 インデックス改善事例
  128. インデックス追加で改善事例 192 ✔︎ 不適切なインデックス を選択 → 既存インデックスが WHERE 句にマッチしない ✔︎

    インデックス内を フルスキャン → パフォーマンス低下 インデックス追加、EXPLAIN、計測 インデックスフルスキャンで遅い
  129. 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 インデックス改善事例
  130. 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 句の 条件
  131. 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 句の 条件
  132. 196 SQL の実行順序 インデックス改善事例 1. FROM  ↓ 2. ON  ↓

    3. JOIN  ↓ 4. WHERE  ↓ 5. GROUP BY  ↓ 6. HAVING  ↓ 7. SELECT  ↓ 8. ORDER BY  ↓ 9. LIMIT
  133. 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 の実行順に 合わせる
  134. 198 実測 +-----------+-----------------+ | target_id | watchlist_count | +-----------+-----------------+ |

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

    1 | | 2515788 | 1 | +-----------+-----------------+ 549 rows in set (0.00 sec) 199 実測 インデックス改善事例 1 min 46.02 sec から改善
  136. 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) インデックス改善事例
  137. ************************* 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) 検索 インデックス改善事例
  138. ************************* 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 作ったインデックス が選択された インデックス改善事例
  139. ************************* 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 絞り込み行数が 実際の検索行数に 近くなった インデックス改善事例
  140. ************************* 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 カバリングインデックス で検索できている インデックス改善事例
  141. ************************* 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 インデックス改善事例
  142. ************************* 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 によるもの インデックス改善事例
  143. ************************* 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 と合わせて パフォーマンスに 影響なし インデックス改善事例
  144. 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 確認
  145. 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 も外す
  146. ************************* 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 インデックス改善事例
  147. ************************* 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 が 消える インデックス改善事例
  148. 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) インデックスの管理
  149. ✔︎ 本番環境で確認 する   ・ステージングと本番の アクセス状況の違い   ・そもそもアクセスが少ないと利用されない ✔︎ master と replica

    全台 で調べる   ・負荷分散で SELECT は replica の場合を考慮   ・つまり replica では使用中 の場合がある 214 未使用インデックスの注意点 インデックスの管理
  150. 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) インデックスの管理
  151. インデックス改善事例・まとめ (1) 216 ✔︎ 複合的に判断 → type, key, rows, Extra,

    etc ✔︎ インデックスを作成 → WHERE 句にマッチ、SQL 実行順に対応して作成 パフォーマンス 計測・改善 EXPLAIN を見る
  152. インデックス改善事例・まとめ (2) 217 ✔︎ 検索対象 (rows) を判断 → インデックスでもフルスキャンは遅い ✔︎

    仕様を見直す ことも重要 → 多すぎる場合は条件追加・変更が必要 ユーザーファースト インデックスを 過信しない
  153. インデックス改善事例・まとめ (3) 218 ✔︎ 書き込み性能 に影響する → インデックスは INSERT, UPDATE,

    DELETE 時に追加 ✔︎ ストレージ容量 が増える → RDS だからといってリソースを無駄遣いしない 必要に応じた チューニング インデックスを 無闇に増やさない
  154. レビュー(コード・設計・要件) 222 ✔︎ SQL から要件 まで → 本当にその検索条件でいいのか ✔︎ 原点に返ろう

    → 正規化、テーブル分割、論理削除、レコード増加傾向、etc 水面下の パフォーマンス劣化を防止 障害対応ノウハウを活かし レビュー
  155. ✔︎ 正規化 できないか   ・カラム追加ではなく別の役割では? ✔︎ テーブル分割 できないか   ・ポリモーフィック関連テーブルの偏り ✔︎ 本当に論理削除

    が必要か?   ・ステータスでは?別テーブルに待避では? 223 レビュー観点例 (1/2) レビュー(コード・設計・要件)
  156. ✔︎ レコード 増加傾向を予測 しているか   ・先々を予測した検索条件か ✔︎ 本当に RDB に必要か?   ・ログなら分析基盤、履歴なら活用手法確認

    ✔︎ 古いレコードは アーカイブ できないか   ・レコードが特に多いテーブルの場合に考慮 224 レビュー観点例 (2/2) レビュー(コード・設計・要件)
  157. ペアプロ 226 ✔︎ 画面共有 して分析から改善まで → EXPLAIN 解説からインデックス作成、実測まで ✔︎ SQL

    への 敷居を低くしよう → 知ってる人が伝えていくスタイル、恩は送ろう 個人の底上げは チームの底上げ 実例は最高の学び
  158. SQL から始まる本質の追求 231 1 2 2 要求と実装のはざま 本質解決への流れ PjM ・

    PdM にヒアリング 実現の代替案を検討・提案 一緒に実装 2 1 1 3 4 5
  159. SQL から始まる本質の追求 232 1 2 2 要求と実装のはざま 本質解決への流れ PjM ・

    PdM にヒアリング 実現の代替案を検討・提案 一緒に実装 2 1 1 3 4 5
  160. ✔︎ ユーザー価値から 剥離   ・半端な機能でリリース ✔︎ 要件と実装の ズレ   ・品質維持に苦労 する ✔︎

    無茶な SQL やテーブルが生まれる   ・そんな検索条件で大丈夫?その JOIN 必要? 233 こんなことありませんか? 実装と要求のはざま
  161. SQL から始まる本質の追求 237 1 2 2 要求と実装のはざま 本質解決への流れ PjM ・

    PdM にヒアリング 実現の代替案を検討・提案 一緒に実装 2 1 1 3 4 5
  162. ✔︎ PjM, PdM に ヒアリング・相談   ・要件確認、現状報告、未来予想、軌道修正 ✔︎ 実現の 代替案 を検討・提案

      ・否定ではなく 他の方法で実現 を目指す ✔︎ 一緒に実装   ・指摘するだけではなく、自ら解決へ 動く 238 本質解決への流れ
  163. SQL から始まる本質の追求 240 1 2 2 要求と実装のはざま 本質解決への流れ PjM ・

    PdM にヒアリング 実現の代替案を検討・提案 一緒に実装 2 1 1 3 4 5 3 2
  164. 241 ✔︎ 指摘事項とその理由の 説明 ✔︎ 具体的な要件、期限とその理由 ✔︎ ユーザーは 何が嬉しいのか ✔︎

    リソース 不足の理由 ✔︎ リスケ可能か、可能ならいつまでか ✔︎ 機能の中での実装の 優先順位 PjM ・ PdM にヒアリング PjM にヒアリング
  165. 242 ✔︎ 指摘事項が品質にどう影響するか 説明 ✔︎ 要件と実装の ズレを是正 ✔︎ ユーザーに提供できる 価値確認

    ✔︎ リソース認識 合わせ ✔︎ リスケ判断、品質確保体制 PjM ・ PdM にヒアリング PdM にヒアリング
  166. 244 PjM ・ PdM にヒアリング ヒアリングをせず放置したら ✔︎ あれもこれも 1 機能

    に   ・ファットな画面・SQL で品質低下 ✔︎ リリース優先で 要件からズレ ていく   ・ユーザーへの価値提供が歪む ✔︎ 機能要件と非機能要件が 混在   ・ログテーブル、ファットカラム、etc
  167. SQL から始まる本質の追求 247 1 2 2 要求と実装のはざま 本質解決への流れ PjM ・

    PdM にヒアリング 実現の代替案を検討・提案 一緒に実装 2 1 1 3 4 5 3 2 3 4
  168. 実例:実現の代替案を検討・提案 249 ✔︎ ディレクターが SQL で分析したい → 逆に SQL で分析できるならなんでもいい

    ✔︎ S3 -> BigQuery 経由の分析基盤 → 他チームで開発中で、ちょうど把握していた API 実装を提案、ログ基盤へ蓄積 行動 ログをテーブルに保存 したい
  169. SQL から始まる本質の追求 250 1 2 2 要求と実装のはざま 本質解決への流れ PjM ・

    PdM にヒアリング 実現の代替案を検討・提案 一緒に実装 2 1 1 3 4 5 3 2 3 5
  170. 実例:一緒に実装 252 ✔︎ タイムラグは 許容できない → 要件確認、ElasticSearch は使えない ✔︎ fulltext

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

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

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