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

1. PHPer が知るべき まみー (@mamy1326) / Lancers
2. Twitter Work Like ✔︎ 2017年の趣味：MySQL ✔︎ 2018年の趣味：DNS ✔︎ 2020年の趣味：CakePHP4 ✔︎ 2021年の趣味：DBRE ：@mamy1326（まみー） ：Lancers,Inc. @ PHPer ： cune.jp 自己紹介 2
3. 質問はこちら - 1 3 どんなࠣ細な内容も大歓迎！ https://joind.in/talk/0b395
4. 質問はこちら - 2 4 ✔︎ Discord #track2-2-mysql-tune ✔︎ Twitter @mamy1326
5. ✔︎ バックエンド エンジニア 　　→どちらかというと SQL は ORM 任せ ✔︎ SQL のパフォーマンスで 困っている 　　→でも 調べ方がよくわからない ✔︎ SQL が 正直怖い 　　→改善の 初手を知りたい 想定オーディエンスのみなさま 5
6. プロローグ 6 ✔︎ 品質の 均一化 → 誰が書いても同じ SQL が実行される ✔︎ 開発効率 の向上 → SQL を書かずに開発できる SQL を 意識の外に置く 機会が増えた 近年の ORM は高性能
7. しかし アラートは突然 やってくる 7
8. 8 落ちる ページ表示速度
9. 9 上がる Load Average (DB -> Web)
10. 10 そして 大量のスロークエリ
11. 起きていたこと 11 ✔︎ スロークエリで パフォーマンス低下 → DB サーバーへの負荷が上昇 ✔︎ Web サーバーへの レスポンス低下 → ページ表示速度の低下 サービスの 品質低下 改善が必要な SQL の実行
12. 12 つまり 障害発生
13. ✔︎ どんな SQL を実行 しているのか 　　→ 実際の速度と頻度はどうなのか ✔︎ コードのどこ なのか 　　→ ORM 利用だと特定に時間がかかる ✔︎ どう改善すれば いいのか 　　→ 解析方法は何があるのか 障害発生時に知りたいこと 13
14. ✔︎ スローログ 監視 　　→ 常に状況を把握 ✔︎ スロークエリ 改善 　　→ 原因特定、影響範囲の把握、改善対応 ✔︎ スロークエリ 予防 　　→ 監視とレビュー エンジニアに求められること 14
15. 15 しかし 障害の都度対応は 後手
16. 目指すこと 16 ✔︎ SQL を 普段から見る → 自分の実装の影響を把握する ✔︎ 日常的に予防 していく → 改善を回していく 安定した 品質を確保 後手から 先手へ
17. 17 先手のために クエリを チューニングしよう
18. アジェンダ スロークエリを 改善する理由 現場で起きている こと スロークエリの 見える化 SQL の 分析・改善手法（一例） スロークエリ 予防 SQL から始まる 本質の追求 18
19. お話し しない こと B-tree などインデックスの仕組み テーブル設計・正規化 SQL の細かい見方・説明 19
20. 前提の環境 Amazon Aurora 20 mysql> select AURORA_VERSION() ; +------------------ + | AURORA_VERSION() | +------------------ + | 2.09.2 | +------------------ + 1 row in set (0.00 sec)
21. 前提の環境 MySQL Engine 21 mysql> select version() ; +------------ + | version() | +------------ + | 5.7.12-log | +------------ + 1 row in set (0.00 sec)
22. 前提の環境 スロークエリ検出時間 22 mysql> show variables like 'long%' ; +-----------------+----------- + | Variable_name | Value | +-----------------+----------- + | long_query_time | 10.000000 | +-----------------+----------- + 1 row in set (0.01 sec)
23. お話しすること スロークエリを 改善する理由 現場で起きている こと スロークエリの 見える化 SQL の 分析・改善手法（一例） スロークエリ 予防 SQL から始まる 本質の追求 23
24. ✔︎ ORM で十分 使えている 　　→ 開発者は SQL 見なくていい ✔︎ 負荷増加でも お金で解決 できる 　　→ オートスケール、スケールアップなど ✔︎ 障害対応 できている 　　→ 発生は稀 だし サービスは継続できている こういうことありませんか？ 24
25. ✔︎ ORM で十分 使えている 　　→ 開発者は SQL 見なくていい ✔︎ お金で解決 できる 　　→ オートスケール、スケールアップなど ✔︎ 障害対応 できている 　　→ 発生は稀 だし サービスは継続できている こういうことありませんか？ 25 スロークエリを 放置している
26. スロークエリを放置すると… (1) 26 ✔︎ 慢性的な 品質低下 → 新規ユーザー獲得困難 ✔︎ サービス 信用失墜 → 既存ユーザー離脱 売上・利益の低下、競争敗北 障害が慢性的 に発生
27. スロークエリを放置すると… (2) 27 ✔︎ 攻撃者の的 になる → 遅い SQL があると予測できる ✔︎ Dos 攻撃 を受ける → DB サーバーの応答品質低下、Web サーバーも… 攻撃 によるサービス 品質低下・停止 特定の 機能が遅い
28. 28 1つの SQL が 障害を発生 させる
29. 29 スロークエリ 改善 しよう
30. ✔︎ サービス 品質向上 　　→ SQL の改善 1つで品質は上がる ✔︎ SQL チェック習慣化で 事前予防 　　→ 障害のノウハウを日常のレビューへ ✔︎ コスト・技術の パフォーマンス向上 　　→ 事業の安定化、チームの技術力底上げ スロークエリを改善 する 理由 30
31. お話しすること スロークエリを 改善する理由 現場で起きている こと スロークエリの 見える化 SQL の 分析・改善手法（一例） スロークエリ 予防 SQL から始まる 本質の追求 31
32. パフォーマンス低下の実例 32 1 2 3 リリース直後（検証不足） 特定の時間（潜在的なスロークエリ） ある日突然（レコード数の増加）
33. パフォーマンス低下の実例 33 1 2 3 リリース直後（検証不足） 特定の時間（潜在的なスロークエリ） ある日突然（レコード数の増加）
34. 現場で起きていること 34 ✔︎ 開発環境では問題なく動作 → ページ表示も高速 ✔︎ テストも通っている → プログラム品質も問題なし 本番環境 で レスポンスが低下 新機能をリリース
35. 原因 35 ✔︎ 開発環境では レコードを間引く → ストレージ容量、転送量の対策 ✔︎ 実は本番環境だと 遅い SQL → 開発時は数万レコード、本番は数千万レコード 本番環境 で レスポンスが低下 本番と開発環境の レコード数の差
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) 本番環境
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 倍
38. 開発環境のレコードは パフォーマンスの観点では 無価値 38
39. 対策 39 ✔︎ 本番とほぼ同じ レコード数 → DB サーバーは本番と同じインスタンス ✔︎ リリース前に 必ずテスト → 機能だけではなく、SQL 単体でも計測 性能を確保 してリリース ステージング環境 を作る
40. Tips 40 ✔︎ 遅くて 待ちきれないのでタブ閉じ → SQL の実行を停止したと思っていても… ✔︎ プロセスが残る 場合がある → 例：phpMyAdmin はタブを閉じても kill されない 軽い気持ちが本番障害 につながる ステージングないなら 本番で実行…
41. Tips 41 ✔︎ 遅くて 待ちきれないのでタブ閉じ → SQL の実行を停止したと思っていても… ✔︎ プロセスが残る 場合がある → 例：phpMyAdmin はタブを閉じても kill されない 軽い気持ちが本番障害 につながる ステージングないなら 本番で実行… 必ず ステージングで 実施しましょう
42. パフォーマンス低下の実例 42 1 2 3 2 リリース直後（検証不足） 特定の時間（潜在的なスロークエリ） ある日突然（レコード数の増加）
43. 現場で起きていること 43 ✔︎ 10 - 19 時 は 問題なかった → ページ表示も高速 ✔︎ テストも通っている → プログラム品質も問題なし 特定の時間帯 で レスポンス低下 リリース時 は問題なく動作
44. 原因 1：潜在的なスロークエリ 44 ✔︎ 遅い SQL でも 十分動いていた → リリース時は利用者が少なく速度低下はなかった ✔︎ ピーク時間帯に DB 負荷増加 → 例：1 sec の SQL がだんだんと遅くなりᮢ値を超える Webサーバー の レスポンスが低下 ピーク時間帯 の負荷増加
45. 対策：潜在的なスロークエリ 45
46. 対策：潜在的なスロークエリ 46 潜在 -> 顕在 へ
47. 対策：潜在的なスロークエリ 47 ✔︎ EXPLAIN (実行計画) を確認 → 遅くなる SQL を事前に改善 潜在 -> 顕在 へ
48. 予防例：潜在的なスロークエリ 48 ✔︎ レビュー時に全ての SQL を洗い出す 　　→ 1つの PR だと SQL の 数は限られる ✔︎ ステージング環境で EXPLAIN / 実測 　　→ 遅い SQL がないか 1つずつチェック ✔︎ レビュー指摘項目を Wiki 化 　　→ PR にリンク添付、ノウハウの蓄積と展開
49. 対策：潜在的なスロークエリ 49 ✔︎ EXPLAIN (実行計画) を確認 → 遅くなる SQL を事前に改善 ✔︎ (できれば) 負荷テスト を実施 → ピーク時の負荷に耐えられることを確認 潜在 -> 顕在 へ
50. 対策：潜在的なスロークエリ 50 ✔︎ EXPLAIN (実行計画) を確認 → 遅くなる SQL を事前に改善 ✔︎ (できれば) 負荷テスト を実施 → ピーク時の負荷に耐えられることを確認 性能を確保 してリリース 潜在 -> 顕在 へ
51. パフォーマンス低下の実例 51 1 2 3 リリース直後（検証不足） 特定の時間（潜在的なスロークエリ） ある日突然（レコード数の増加）
52. 現場で起きていること 52 ✔︎ 数ヶ月問題はなかった → ページ表示も高速 ✔︎ リリースもしていない → インフラの変更もなし ある日を境目 に レスポンス低下 しばらく安定稼働
53. 原因 1：レコード数のᮢ値超過 53 ✔︎ レコード数：数万 -> 数100万 → リリース時は数万、インデックスも効いていた ✔︎ 検索対象の 割合増加 → フルスキャンの方が効率的と判断される 前触れなくレスポンスが低下 フルスキャン 発生
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’
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 倍 半年前の 一覧を検索
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 年前と 現在を比較
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
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
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
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
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
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 遅い！
63. 63 爆発的なレコード増加は もっと 短い期間でも 起こる
64. 対策：レコード数のᮢ値超過 64 ✔︎ 範囲を さらに絞る → 1ヶ月単位で表示など、要件も見直す ✔︎ 他の 条件を追加 → ユーザーの検索傾向を掴み、要求に応じる 定常的にレスポンスが安定 絞り込み 条件を追加
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ヶ月単位で 絞り込む
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
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
68. 68 レコード増加傾向は 必ず 予測して SQL を書こう
69. パフォーマンス低下の実例 69 1 2 3 リリース直後（検証不足） 特定の時間（潜在的なスロークエリ） ある日突然（レコード数の増加） 2つ目の事例紹介
70. 70 ・お気に入りを管理 するテーブル 　　→ 多 対 多 ・複数のテーブル に対応 　　→ 仕事、ユーザー、パッケージ、etc… ・対応する テーブル名をカラム で持つ 　　→ ポリモーフィック関連テーブル 実例 2：カーディナリティの偏り 例えばこんな要件
71. 実例 2：カーディナリティの偏り 71 テーブル設計
72. 実例 2：カーディナリティの偏り 72 テーブル設計 各テーブル名と PK を持つ
73. 実例 2：カーディナリティの偏り 73 偏りを見る SQL SELECT count(id) AS counter, target_table_name FROM watchlists GROUP BY target_table_name ORDER BY counter DESC
74. 実例 2：カーディナリティの偏り 74 偏りを見る SQL SELECT count(id) AS counter, target_table_name FROM watchlists GROUP BY target_table_name ORDER BY counter DESC テーブルごとの お気に入り数と テーブル名
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
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
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)
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) 当初は 偏りは少なかった
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) 現在は 大きな偏り
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 で 計測
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 を検索
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 の 結果
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 の 結果
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 の 結果
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
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 パフォーマンスに 問題はない インデックスが 利用されているけど
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 対象行数が多く 遅くなる可能性
88. 88 大きな偏りは もはや 別物
89. 対策：カーディナリティの偏り 89 ✔︎ もはや 別の要素 → いずれ全体的にパフォーマンスが低下する可能性 ✔︎ 元々 分けるべき設計 → ポリモーフィック関連テーブルはアンチパターン パフォーマンスの 影響範囲を絞る 偏ったレコードは 別テーブルへ
90. 90 現場で 起きていること から やるべきこと が見える
91. ✔︎ ステージング で確認 　　→ 性能を確保 してリリース ✔︎ 潜在的 スロークエリを顕在化 　　→ EXPLAIN 実行で 事前に解決 ✔︎ レコード 増加数を予測 　　→ 予測した検索条件、テーブル分割 現場で起きていること 91 現場でやるべきこと
92. お話しすること スロークエリを 改善する理由 現場で起きている こと スロークエリの 見える化 SQL の 分析・改善手法（一例） スロークエリ 予防 SQL から始まる 本質の追求 92
93. スロークエリの見える化 93 1 2 2 フレームワークの SqlLog MySQL のスローログ
94. スロークエリの見える化 94 1 2 2 フレームワークの SqlLog MySQL のスローログ
95. フレームワークの SqlLog 95 CakePHP 4
96. フレームワークの SqlLog 96 CakePHP 4 画面で実行された SQL が全て 表示される
97. フレームワークの SqlLog 97 CakePHP 4 n+1 な SQL も 一目でわかる
98. SqlLog の実際の利用例 98 ✔︎ 1つずつ 実測 → リリース後の速度をあらかじめ計測 ✔︎ 1つずつ EXPLAIN → 潜在的なスロークエリを見つけて、改善 性能を確保 してリリース 列挙された SQL を ステージングで確認
99. 99 次は 本番環境の監視
100. スロークエリの見える化 100 1 2 2 フレームワークの SqlLog MySQL のスローログ 2 1
101. 101 障害時に 流れた SQL は 記録しないと 残らない
102. 102 障害発生後に 原因を探せない
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 忘れずに
104. MySQL のスローログ 104 RDS のスローログ出力設定 ✔︎ CloudWatch で閲覧可能
105. MySQL のスローログ 105 RDS のスローログ秒数設定 ✔︎ パラメーターグループを作って 　 再起動不要にしておきましょう
106. 106 CloudWatch じゃなく もっと 便利に可視化 したい
107. MySQL のスローログ 107 ✔︎ CloudWatch から Slack へ → Lambda でサクッと実装（事例がたくさんある） ✔︎ DataDog などで コード特定 → スロークエリを実行している場所がわかる スピーディな対応 を可能に 更に可視化する
108. MySQL のスローログ 108 Slack 通知例
109. MySQL のスローログ 109 Slack 通知例 実際の スロークエリ
110. MySQL のスローログ 110 Slack 通知例 実行時間が わかる
111. 発見から改善までの流れ 111 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で 絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
112. 発見から改善までの流れ 112 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で 絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
113. 発見から改善までの流れ 113 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で 絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
114. 発見から改善までの流れ 114 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で 絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
115. 発見から改善までの流れ 115 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で 絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
116. 発見から改善までの流れ 116 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で 絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
117. 発見から改善までの流れ 117 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で 絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認
118. 118 もっと良くするため 課題 がある
119. ✔︎ コード 特定の自動化 　　→ スロークエリ 実行箇所の明示 ✔︎ スロークエリ 分析の可視化 　　→ 同じ SQL の 回数、実行時間を集計 スロークエリの見える化 119 課題
120. スロークエリの見える化の課題 (1) 120 ✔︎ 特定の手作業が不要に → 短いと 30 分以内、長いと数時間がゼロに ✔︎ 通知時に コード特定 → ピンポイントで修正箇所がわかる (DataDog など) スピーディ・正確な対応 を可能に コード 特定の自動化
121. 発見から改善までの流れ 121 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で 絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認 ここが
122. スロークエリの見える化の課題 (2) 122 ✔︎ 回数、時間、割合の集計 → スロークエリ全体の中での SQL の割合、回数、合計時間 ✔︎ 数値の高い SQL から順に対応 → より効果の高い対応が可能 (pt-query-digest) 先手対応 が可能に スロークエリ 分析の可視化
123. 発見から改善までの流れ 123 RDS アラート を検知 みんなで通知を見る 当番制にする、etc スローログを 検索・特定 アラートの時間帯で 絞り込む (〜前 10 分) コードを 特定 テーブル名、機能、 特徴的な部分から特定 修正して PullRequest 作成 EXPLAIN、分析、修正 有識者のレビュー必須 リリース ステージング検証後 レビュアーと一緒に実施 経過を観察 リリース後の負荷観察 修正した機能の確認 発生率が
124. ✔︎ 実装した SQL を列挙・確認 　　→ フレームワークのデバッガで見る ✔︎ スロークエリ 通知で SQL 特定 　　→ 原因の見える化で 確実・スピーディな対応 ✔︎ コード特定、分析で 先手を打つ 　　→ DataDog、pt-query-digest など検討 スロークエリの見える化 124 まとめ
125. 125 後手から 先手を目指し より本質対応を
126. お話しすること スロークエリを 改善する理由 現場で起きている こと スロークエリの 見える化 SQL の 分析・改善手法（一例） スロークエリ 予防 SQL から始まる 本質の追求 126
127. SQL の分析・改善手法 127 1 2 2 EXPLAIN の主な見方 インデックス追加で改善事例 インデックスの管理 2 1 1 3
128. EXPLAIN の主な見方 インデックス追加で改善事例 インデックスの管理 SQL の分析・改善手法 128 1 2 2 2 1 1 3
129. 129 漢のコンピュータ道 MySQLのEXPLAINを 徹底解説!! 読みましょう！
130. 130 だとこの章が 終わるので…
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
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 と 書くだけ
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 を処理 するかがわかる
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つを お話しします
135. ✔︎ 複合的 に見る 　　・type, rows, Extra など 合わせて判断 ✔︎ インデックスの使われ方 を見る 　　・インデックスが 足りない 　　・インデックスでは 検索しきれない 　　・対象が多い EXPLAIN の主な見方 135 前提
136. ・ALL：対応必須 　　→ フルスキャン、最も遅い 　　→ インデックスが全く使用されない ・index：対応必須 　　→ インデックスでは 検索しきれない 　　→ 対象が多い EXPLAIN の主な見方 136 type
137. ・range：注意 　　→ インデックスを使った 範囲検索 　　→ 対象行数 rows が十分 絞られていれば高速 　　→ rows の 割合が多い場合 は改善が必要 ・const：安心 　　→ PK または UK で 1行 または 0行 を取得 　　→ 等価比較で 最高速 EXPLAIN の主な見方 137 type
138. ・オプティマイザが 選択したインデックス 　　→ 使われるインデックス 　　→ 実行時間が最小 になるよう処理方法を決める 　　→ その 過程で選択される ・狙いと違う インデックスなら注意 　　→ インデックスの 実態が最適化に沿っていない 　　→ 統計情報の再作成 など対策が必要な場合も EXPLAIN の主な見方 138 key
139. ・検索対象の レコード数 　　→ 絞りたかった数と 同じか近い ことを確認 ・想定より 多い場合は注意 　　→ パフォーマンス低下 の可能性 　　→ 絞り込みが足りない 　　→ 当初よりレコード数が増えている EXPLAIN の主な見方 139 rows
140. ・Using index：高速 　　→ カバリングインデックスなど 　　→ インデックスのみで検索 が完結する 　　→ ただし rows の割合が多い場合は注意 EXPLAIN の主な見方 140 Extra
141. ・Using index condition (ICP)：高速 　　→ 複合インデックスを より効率的に使う 　　→ 余計なレコード検索を 減らす 　　→ MySQL 5.6 からの機能 　　→ ただし rows の割合が多い場合は注意 EXPLAIN の主な見方 141 Extra
142. ・Using fi lesort：注意 　　→ JOIN と ORDER BY の組み合わせでよく見る 　　→ メモリとファイルでクイックソート 　　→ 駆動表と ORDER BY が別テーブルの場合 　　→ GROUP BY による暗黙の ORDER BY でも出る 　　→ LIMIT 前のレコード数が多ければ遅い 　　→ Using temporary が一緒に出ることが多い EXPLAIN の主な見方 142 Extra
143. ✔︎ 複合的 に見る 　　・type, rows, Extra など 合わせて判断 ✔︎ インデックスの使われ方 を見る 　　・インデックスが 足りない 　　・インデックスでは 検索しきれない 　　・対象が多い EXPLAIN の主な見方 143 前提（再掲）
144. EXPLAIN の主な見方 インデックス追加で改善事例 インデックスの管理 SQL の分析・改善手法 144 1 2 2 EXPLAIN の主な見方 インデックス追加で改善事例 2 1 1 3
145. 145 単純に インデックスが 足りない 場合
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' インデックス改善事例
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' インデックス改善事例 仕事に対する 評価のテーブル
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' インデックス改善事例 評価の値が まだない人
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日間
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) インデックス改善事例
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) インデックス改善事例
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 インデックス改善事例
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 フルスキャン インデックス改善事例
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 %) インデックス改善事例
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) インデックス改善事例
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 句に該当する インデックスがない
157. 157 ✔︎ もっとも単純 → インデックス追加で大抵は高速に ✔︎ なるべく 単純な SQL を目指す → 絞り込みに無理はないか、要件は妥当か インデックス追加、EXPLAIN、計測 インデックスが 無くて遅い インデックス改善事例
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 インデックス改善事例
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) インデックス改善事例 十分な パフォーマンス
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) インデックス改善事例
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) 検索 インデックス改善事例
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) 作ったインデックス が選択された インデックス改善事例
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) 絞り込み行数が 実際の検索行数へ インデックス改善事例
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) インデックスを使っ た検索ができている インデックス改善事例
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 インデックス改善事例
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) インデックス改善事例
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 （追加情報） インデックス改善事例
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 時に 常に表示される インデックス改善事例
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) オプティマイザの 最適化の際のメモ インデックス改善事例
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 を 書き換えたよ インデックス改善事例
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 関数に 書き換え インデックス改善事例
172. ・Using index：高速 　　→ カバリングインデックスなど 　　→ インデックスのみで検索 が完結する 　　→ ただし rows の割合が多い場合は注意 EXPLAIN の主な見方 172 Extra (再掲)
173. ・Using index：高速 　　→ カバリングインデックスなど 　　→ インデックスのみで検索 が完結する 　　→ ただし rows の割合が多い場合は注意 EXPLAIN の主な見方 173 Extra (再掲) カバリングインデックスを
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)
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' 取得カラムを インデックスに 合わせる
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) カバリング インデックス
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 若干高速に
178. 178 インデックス フルスキャンで 遅い例
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 インデックス改善事例
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 ユーザーごとの お気に入り数 インデックス改善事例
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 日間 インデックス改善事例
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 で集計 インデックス改善事例
183. 183 実測 +-----------+-----------------+ | target_id | watchlist_count | +-----------+-----------------+ | 274 | 1 | (தུ) | 2515788 | 1 | +-----------+-----------------+ 48 rows in set (1 min 46.02 sec) 1 min 46.02 sec 非常に遅い インデックス改善事例
184. 184 watchlists 総レコード数 +----------+ | count(1) | +----------+ | 14666790 | +----------+ 1 row in set (8.51 sec) 1466 万 レコード インデックス改善事例
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) インデックス改善事例
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) インデックス フルスキャン インデックス改善事例
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) 検索条件に該当する インデックスがなく インデックス改善事例
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) マッチしない インデックスを 選択した インデックス改善事例
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 % が 検索対象 インデックス改善事例
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 句に該当する インデックスがない インデックス改善事例
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) オプティマイザが この中でもっともコストの低い インデックスを選択 インデックス改善事例
192. インデックス追加で改善事例 192 ✔︎ 不適切なインデックス を選択 → 既存インデックスが WHERE 句にマッチしない ✔︎ インデックス内を フルスキャン → パフォーマンス低下 インデックス追加、EXPLAIN、計測 インデックスフルスキャンで遅い
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 インデックス改善事例
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 句の 条件
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 句の 条件
196. 196 SQL の実行順序 インデックス改善事例 1. FROM 　↓ 2. ON 　↓ 3. JOIN 　↓ 4. WHERE 　↓ 5. GROUP BY 　↓ 6. HAVING 　↓ 7. SELECT 　↓ 8. ORDER BY 　↓ 9. LIMIT
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 の実行順に 合わせる
198. 198 実測 +-----------+-----------------+ | target_id | watchlist_count | +-----------+-----------------+ | 274 | 1 | | 2515788 | 1 | +-----------+-----------------+ 549 rows in set (0.00 sec) インデックス改善事例
199. +-----------+-----------------+ | target_id | watchlist_count | +-----------+-----------------+ | 274 | 1 | | 2515788 | 1 | +-----------+-----------------+ 549 rows in set (0.00 sec) 199 実測 インデックス改善事例 1 min 46.02 sec から改善
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) インデックス改善事例
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) 検索 インデックス改善事例
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 作ったインデックス が選択された インデックス改善事例
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 絞り込み行数が 実際の検索行数に 近くなった インデックス改善事例
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 カバリングインデックス で検索できている インデックス改善事例
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 インデックス改善事例
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 によるもの インデックス改善事例
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 と合わせて パフォーマンスに 影響なし インデックス改善事例
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 確認
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 も外す
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 インデックス改善事例
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 が 消える インデックス改善事例
212. EXPLAIN の主な見方 インデックス追加で改善事例 インデックスの管理 SQL の分析・改善手法 212 1 2 2 2 1 1 3
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) インデックスの管理
214. ✔︎ 本番環境で確認 する 　　・ステージングと本番の アクセス状況の違い 　　・そもそもアクセスが少ないと利用されない ✔︎ master と replica 全台 で調べる 　　・負荷分散で SELECT は replica の場合を考慮 　　・つまり replica では使用中 の場合がある 214 未使用インデックスの注意点 インデックスの管理
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) インデックスの管理
216. インデックス改善事例・まとめ (1) 216 ✔︎ 複合的に判断 → type, key, rows, Extra, etc ✔︎ インデックスを作成 → WHERE 句にマッチ、SQL 実行順に対応して作成 パフォーマンス 計測・改善 EXPLAIN を見る
217. インデックス改善事例・まとめ (2) 217 ✔︎ 検索対象 (rows) を判断 → インデックスでもフルスキャンは遅い ✔︎ 仕様を見直す ことも重要 → 多すぎる場合は条件追加・変更が必要 ユーザーファースト インデックスを 過信しない
218. インデックス改善事例・まとめ (3) 218 ✔︎ 書き込み性能 に影響する → インデックスは INSERT, UPDATE, DELETE 時に追加 ✔︎ ストレージ容量 が増える → RDS だからといってリソースを無駄遣いしない 必要に応じた チューニング インデックスを 無闇に増やさない
219. お話しすること スロークエリを 改善する理由 現場で起きている こと スロークエリの 見える化 SQL の 分析・改善手法（一例） スロークエリ 予防 SQL から始まる 本質の追求 219
220. スロークエリ予防 220 1 2 2 レビュー（コード・設計・要件） ペアプロ これからの課題 2 1 1 3
221. スロークエリ予防 221 1 2 2 レビュー（コード・設計・要件） ペアプロ これからの課題 2 1 1 3
222. レビュー（コード・設計・要件） 222 ✔︎ SQL から要件 まで → 本当にその検索条件でいいのか ✔︎ 原点に返ろう → 正規化、テーブル分割、論理削除、レコード増加傾向、etc 水面下の パフォーマンス劣化を防止 障害対応ノウハウを活かし レビュー
223. ✔︎ 正規化 できないか 　　・カラム追加ではなく別の役割では？ ✔︎ テーブル分割 できないか 　　・ポリモーフィック関連テーブルの偏り ✔︎ 本当に論理削除 が必要か？ 　　・ステータスでは？別テーブルに待避では？ 223 レビュー観点例 (1/2) レビュー（コード・設計・要件）
224. ✔︎ レコード 増加傾向を予測 しているか 　　・先々を予測した検索条件か ✔︎ 本当に RDB に必要か？ 　　・ログなら分析基盤、履歴なら活用手法確認 ✔︎ 古いレコードは アーカイブ できないか 　　・レコードが特に多いテーブルの場合に考慮 224 レビュー観点例 (2/2) レビュー（コード・設計・要件）
225. スロークエリ予防 225 1 2 2 レビュー（コード・設計・要件） ペアプロ これからの課題 2 1 1 3
226. ペアプロ 226 ✔︎ 画面共有 して分析から改善まで → EXPLAIN 解説からインデックス作成、実測まで ✔︎ SQL への 敷居を低くしよう → 知ってる人が伝えていくスタイル、恩は送ろう 個人の底上げは チームの底上げ 実例は最高の学び
227. ✔︎ なるべく 1次情報を示す・残す 　　・揺らぎない技術提供、復習のしやすさ ✔︎ 意訳を交え 相手に寄り添う 　　・言葉の説明も交えながら進める ✔︎ 説明できないことは 一緒に探す 　　・ごまかさない、放置が一番危険 227 気をつけていること ペアプロ
228. スロークエリ予防 228 1 2 2 レビュー（コード・設計・要件） ペアプロ これからの課題 2 1 1 3
229. ✔︎ 社内勉強会 　　・ノウハウの共有、更なる品質向上、楽しさ ✔︎ コード特定自動化、スロークエリ分析 　　・よりスピーディに、より先手を打つ ✔︎ PR の 新規 SQL を CI/CD で事前検知 　　・https://github.com/pyama86/welcome-new-query 229 これからの課題 スロークエリ予防
230. お話しすること スロークエリを 改善する理由 現場で起きている こと スロークエリの 見える化 SQL の 分析・改善手法（一例） スロークエリ 予防 SQL から始まる 本質の追求 230
231. SQL から始まる本質の追求 231 1 2 2 要求と実装のはざま 本質解決への流れ PjM ・ PdM にヒアリング 実現の代替案を検討・提案 一緒に実装 2 1 1 3 4 5
232. SQL から始まる本質の追求 232 1 2 2 要求と実装のはざま 本質解決への流れ PjM ・ PdM にヒアリング 実現の代替案を検討・提案 一緒に実装 2 1 1 3 4 5
233. ✔︎ ユーザー価値から 剥離 　　・半端な機能でリリース ✔︎ 要件と実装の ズレ 　　・品質維持に苦労 する ✔︎ 無茶な SQL やテーブルが生まれる 　　・そんな検索条件で大丈夫？その JOIN 必要？ 233 こんなことありませんか？ 実装と要求のはざま
234. ✔︎ 納期に追われ リソース不足 　　・スケジュール調整の決断が難しく見直さない ✔︎ 設計不足で 運用保守がしんどい 　　・リリース後の障害で後手に回る ✔︎ なんでも SQL でやろうとする 　　・足りない工数を無理に補って品質低下 234 なにが起きているか 実装と要求のはざま
235. 235 要件と実装のはざまで 苦しい
236. 236 どう アプローチ すればいいのか
237. SQL から始まる本質の追求 237 1 2 2 要求と実装のはざま 本質解決への流れ PjM ・ PdM にヒアリング 実現の代替案を検討・提案 一緒に実装 2 1 1 3 4 5
238. ✔︎ PjM, PdM に ヒアリング・相談 　　・要件確認、現状報告、未来予想、軌道修正 ✔︎ 実現の 代替案 を検討・提案 　　・否定ではなく 他の方法で実現 を目指す ✔︎ 一緒に実装 　　・指摘するだけではなく、自ら解決へ 動く 238 本質解決への流れ
239. 239 指摘だけじゃなく 一緒に改善
240. SQL から始まる本質の追求 240 1 2 2 要求と実装のはざま 本質解決への流れ PjM ・ PdM にヒアリング 実現の代替案を検討・提案 一緒に実装 2 1 1 3 4 5 3 2
241. 241 ✔︎ 指摘事項とその理由の 説明 ✔︎ 具体的な要件、期限とその理由 ✔︎ ユーザーは 何が嬉しいのか ✔︎ リソース 不足の理由 ✔︎ リスケ可能か、可能ならいつまでか ✔︎ 機能の中での実装の 優先順位 PjM ・ PdM にヒアリング PjM にヒアリング
242. 242 ✔︎ 指摘事項が品質にどう影響するか 説明 ✔︎ 要件と実装の ズレを是正 ✔︎ ユーザーに提供できる 価値確認 ✔︎ リソース認識 合わせ ✔︎ リスケ判断、品質確保体制 PjM ・ PdM にヒアリング PdM にヒアリング
243. 243 ヒアリングせず 放置したら…
244. 244 PjM ・ PdM にヒアリング ヒアリングをせず放置したら ✔︎ あれもこれも 1 機能 に 　　・ファットな画面・SQL で品質低下 ✔︎ リリース優先で 要件からズレ ていく 　　・ユーザーへの価値提供が歪む ✔︎ 機能要件と非機能要件が 混在 　　・ログテーブル、ファットカラム、etc
245. 245 妥協は 水面下での パフォーマンス劣化の 始まり
246. 246 層を超えた ヒアリングを
247. SQL から始まる本質の追求 247 1 2 2 要求と実装のはざま 本質解決への流れ PjM ・ PdM にヒアリング 実現の代替案を検討・提案 一緒に実装 2 1 1 3 4 5 3 2 3 4
248. 実現の代替案を検討・提案 248 ✔︎ 指摘と提案はセット → みんなチーム、互いの重なりを広げよう ✔︎ チーム横断で実現案を考える → インフラ、テックリード、マネージャー、etc 代替案で 一緒に実現を目指す 打ち返しでは 解決しない
249. 実例：実現の代替案を検討・提案 249 ✔︎ ディレクターが SQL で分析したい → 逆に SQL で分析できるならなんでもいい ✔︎ S3 -> BigQuery 経由の分析基盤 → 他チームで開発中で、ちょうど把握していた API 実装を提案、ログ基盤へ蓄積 行動 ログをテーブルに保存 したい
250. SQL から始まる本質の追求 250 1 2 2 要求と実装のはざま 本質解決への流れ PjM ・ PdM にヒアリング 実現の代替案を検討・提案 一緒に実装 2 1 1 3 4 5 3 2 3 5
251. 一緒に実装 251 ✔︎ プロトタイプ作成 → 実装を後ろから支え、品質保証 ✔︎ パフォーマンス検証 → データを用意しパフォーマンステスト 戦う兵站部隊 で 背中を任せてもらう 後ろを支えて ともに戦う
252. 実例：一緒に実装 252 ✔︎ タイムラグは 許容できない → 要件確認、ElasticSearch は使えない ✔︎ fulltext index 環境を作りテスト → 実データと同じ環境を作り、パフォーマンス検証 開発を止めずに フィードバック リアルタイム全文検索が欲しい
253. 253 0 → 1 が得意 100 → 200 が得意 ひとそれぞれ
254. 254 役割を認識して 自分から動く
255. 255 自分から動くから 相手も動いてくれる
256. 256 ここまでやって 初めて DBRE と 言える
257. 257 おわりに
258. 258 SQL 1つで 障害になる ＝ SQL 1つで 品質が上がる
259. 259 SQL から遡れば ユーザーにたどり着く
260. 260 クエリチューニングして 改善しよう
261. 261 SQL 怖い😱 -> やれるかも💡
262. 262 SQL への 敷居を低くして
263. 263 SQL 見てみよう EXPLAIN してみよう インデックス 作ろう
265. 質問はこちら - 1 265 どんなࠣ細な内容も大歓迎！ https://joind.in/talk/0b395
266. 質問はこちら - 2 266 ✔︎ Discord #track2-2-mysql-tune ✔︎ Twitter @mamy1326
267. SQL! EXPLAIN! インデックス! #F0F0F0 #666666 #E6855E #5EC84E #F0F0F0 　あいうえおかきくけこさしすせそ #E6855E 　　あいうえおかきくけこさしすせそ #5EC84E 　あいうえおかきくけこさしすせそ