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