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

PHPer が知るべき

まみー (@mamy1326) / Lancers

View Slide

Twitter

Work

Like

✔︎
2017年の趣味：MySQL

✔︎
2018年の趣味：DNS

✔︎
2020年の趣味：CakePHP4

✔︎
2021年の趣味：DBRE
：@mamy1326（まみー）

：Lancers,Inc. @ PHPer

： cune.jp
自己紹介 2

View Slide

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

View Slide

✔︎
Discord

#track2-2-mysql-tune

✔︎
Twitter

@mamy1326

View Slide

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

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

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

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

✔︎
SQL が正直怖い

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

View Slide

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

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

✔︎
開発効率の向上

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

View Slide

しかし

アラートは突然

やってくる
7

View Slide

8
落ちる

ページ表示速度

View Slide

9
上がる

Load Average

(DB -> Web)

View Slide

10
そして

大量のスロークエリ

View Slide

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

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

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

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

View Slide

12
つまり

障害発生

View Slide

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

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

✔︎
コードのどこなのか

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

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

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

View Slide

✔︎
スローログ監視

　　→ 常に状況を把握

✔︎
スロークエリ改善

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

✔︎
スロークエリ予防

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

View Slide

15
しかし

障害の都度対応は

後手

View Slide

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

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

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

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

View Slide

17
先手のために

クエリを

チューニングしよう

View Slide

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

現場で起きていること

スロークエリの見える化

SQL の分析・改善手法（一例）


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

View Slide

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

テーブル設計・正規化

SQL の細かい見方・説明
19

View Slide

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

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

| AURORA_VERSION()
|

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

| 2.09.2
|

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

1 row in set (0.00 sec)

View Slide

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

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

| version()
|

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

| 5.7.12-log
|

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


View Slide

お話しすること





23

View Slide

✔︎
ORM で十分使えている

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

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

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

✔︎
障害対応できている

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

View Slide

✔︎
ORM で十分使えている

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

✔︎
お金で解決できる

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

✔︎
障害対応できている

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

放置している

View Slide

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

→ 新規ユーザー獲得困難

✔︎
サービス信用失墜

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

View Slide

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

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

✔︎
Dos 攻撃を受ける

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

View Slide

28
1つの SQL が

障害を発生させる

View Slide

29
スロークエリ

改善しよう

View Slide

✔︎
サービス品質向上

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

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

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

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

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

View Slide






31

View Slide

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

特定の時間（潜在的なスロークエリ）

ある日突然（レコード数の増加）

View Slide

1
2
3



View Slide

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

→ ページ表示も高速

✔︎
テストも通っている

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

View Slide

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

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

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

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

View Slide

実例 36
mysql> select count(1) from results;
+----------+
| count(1) |
+----------+
| 37858 |
+----------+
+----------+
| count(1) |
+----------+
| 31537676 |
+----------+
開発環境 (local) 本番環境

View Slide

実例 37
開発環境 (local) 本番環境
+----------+
| count(1) |
+----------+
| 37858 |
+----------+
+----------+
| count(1) |
+----------+
| 31537676 |
+----------+
データで検証し

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

View Slide

開発環境のレコードは

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

無価値
38

View Slide

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

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

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

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

View Slide

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

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

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

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

View Slide

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

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

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

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

ステージングで

実施しましょう

View Slide

1
2
3
2



View Slide

✔︎
10 - 19 時は問題なかった


✔︎
テストも通っている

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

View Slide

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

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

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

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

View Slide

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

View Slide

潜在 -> 顕在へ

View Slide

✔︎
EXPLAIN (実行計画) を確認

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

潜在 -> 顕在へ

View Slide

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

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

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

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

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

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

View Slide

✔︎


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

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

View Slide

1
2
3



View Slide

✔︎
数ヶ月問題はなかった


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

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

View Slide

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

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

✔︎
検索対象の割合増加

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

View Slide

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

View Slide

EXPLAIN
SELECT
id,
work_id,
user_id,
status
FROM
results
WHERE
created <
‘2011-03-26 10:25:36’
EXPLAIN
SELECT
id,
work_id,
user_id,
status
FROM
results
WHERE
created <
‘2021-03-26 10:25:36’
833 倍
半年前の

一覧を検索

View Slide

EXPLAIN
SELECT
id,
work_id,
user_id,
status
FROM
results
WHERE
created <
‘2011-03-26 10:25:36’
EXPLAIN
SELECT
id,
work_id,
user_id,
status
FROM
results
WHERE
created <
‘2021-03-26 10:25:36’
833 倍
10 年前と

現在を比較

View Slide

過去の 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
key: NULL
key_len: NULL
ref: NULL
rows: 31069538
filtered: 50.00
Extra: Using where
1 row in set, 1 warning (0.01
sec)
現在の EXPLAIN

View Slide

************ 1. row ************
id: 1
select_type: SIMPLE
table: results
partitions: NULL
type: range
key: created
key_len: 6
ref: NULL
rows: 116270
filtered: 100.00
Extra: Using inde
x

condition
************ 1. row ************
id: 1
select_type: SIMPLE
table: results
partitions: NULL
type: ALL
key: NULL
key_len: NULL
ref: NULL
rows: 31069538
filtered: 50.00
Extra: Using where
sec)
半年前は

renge スキャン
10 年前は

range スキャンで

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

View Slide

************ 1. row ************
id: 1
select_type: SIMPLE
table: results
partitions: NULL
type: range
key: created
key_len: 6
ref: NULL
rows: 116270
filtered: 100.00
Extra: Using inde
x

condition
************ 1. row ************
id: 1
select_type: SIMPLE
table: results
partitions: NULL
type: ALL
key: NULL
key_len: NULL
ref: NULL
rows: 31069538
filtered: 50.00
Extra: Using where
sec)
対象行数は

11 万程度

View Slide

************ 1. row ************
id: 1
select_type: SIMPLE
table: results
partitions: NULL
type: range
key: created
key_len: 6
ref: NULL
rows: 116270
filtered: 100.00
Extra: Using inde
x

condition
************ 1. row ************
id: 1
select_type: SIMPLE
table: results
partitions: NULL
type: ALL
key: NULL
key_len: NULL
ref: NULL
rows: 31069538
filtered: 50.00
Extra: Using where
sec)
現在は

フルスキャン

View Slide

************ 1. row ************
id: 1
select_type: SIMPLE
table: results
partitions: NULL
type: range
key: created
key_len: 6
ref: NULL
rows: 116270
filtered: 100.00
Extra: Using inde
x

condition
************ 1. row ************
id: 1
select_type: SIMPLE
table: results
partitions: NULL
type: ALL
key: NULL
key_len: NULL
ref: NULL
rows: 31069538
filtered: 50.00
Extra: Using where
sec)
対象行数は

3106 万

View Slide

************ 1. row ************
id: 1
select_type: SIMPLE
table: results
partitions: NULL
type: range
key: created
key_len: 6
ref: NULL
rows: 116270
filtered: 100.00
Extra: Using inde
x

condition
************ 1. row ************
id: 1
select_type: SIMPLE
table: results
partitions: NULL
type: ALL
key: NULL
key_len: NULL
ref: NULL
rows: 31069538
filtered: 50.00
Extra: Using where
sec)
遅い！
遅い！

View Slide

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

もっと短い期間でも

起こる

View Slide

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

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

✔︎
他の条件を追加

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

View Slide

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
key: created
key_len: 6
ref: NULL
rows: 433396
filtered: 100.00
Extra: Using index
condition
1ヶ月単位で

絞り込む

View Slide

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
key: created
key_len: 6
ref: NULL
rows: 433396
filtered: 100.00
Extra: Using index
condition
インデックスが

利用される

利用される
SQL EXPLAIN

View Slide

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
key: created
key_len: 6
ref: NULL
rows: 433396
filtered: 100.00
Extra: Using index
condition
対象行数は

43 万程度
SQL EXPLAIN

View Slide

68
レコード増加傾向は

必ず予測して

SQL を書こう

View Slide

1
2
3


2つ目の事例紹介

View Slide

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

　　→ 多対多

・複数のテーブルに対応

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

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

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

View Slide

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

View Slide

テーブル設計
各テーブル名と

PK を持つ

View Slide

偏りを見る SQL
SELECT
count(id) AS counter,
target_table_name
FROM
watchlists
GROUP BY
target_table_name
ORDER BY
counter DESC

View Slide

偏りを見る SQL
SELECT
target_table_name
FROM
watchlists
GROUP BY
target_table_name
ORDER BY
counter DESC
テーブルごとの

お気に入り数と

テーブル名

View Slide

偏りを見る SQL
SELECT
target_table_name
FROM
watchlists
GROUP BY
target_table_name
ORDER BY
counter DESC
テーブル名で

GROUP BY

View Slide

偏りを見る SQL
SELECT
target_table_name
FROM
watchlists
GROUP BY
target_table_name
ORDER BY
counter DESC
お気に入り数で

ORDER BY

View Slide

+---------+----------------------+
| counter | target_table_name |
+---------+----------------------+
| 14736 | Works |
| 9182 | Users |
| 1532 | Packages |
| 921 | Campaigns |
| 686 | Consultations |
| 258 | Laboratories |
+---------+----------------------+
6 rows in set (1.42 sec)
過去の偏り現在の偏り
+----------+----------------------+
+----------+----------------------+
| 13115751 | Works |
| 1509182 | Users |
| 72999 | Packages |
| 2956 | Campaigns |
+----------+----------------------+

View Slide

+---------+----------------------+
+---------+----------------------+
| 14736 | Works |
| 9182 | Users |
| 1532 | Packages |
| 921 | Campaigns |
+---------+----------------------+
+----------+----------------------+
+----------+----------------------+
| 13115751 | Works |
| 1509182 | Users |
+----------+----------------------+
当初は

偏りは少なかった

View Slide

+---------+----------------------+
+---------+----------------------+
| 14736 | Works |
| 9182 | Users |
| 1532 | Packages |
| 921 | Campaigns |
+---------+----------------------+
+----------+----------------------+
+----------+----------------------+
| 13115751 | Works |
| 1509182 | Users |
+----------+----------------------+
現在は

大きな偏り

View Slide

+---------+----------------------+
+---------+----------------------+
| 14736 | Works |
| 9182 | Users |
| 1532 | Packages |
| 921 | Campaigns |
+---------+----------------------+
+----------+----------------------+
+----------+----------------------+
| 13115751 | Works |
| 1509182 | Users |
+----------+----------------------+
Works と

Packages で

計測

View Slide

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

View Slide

+---------+---------+
| user_id | counter |
+---------+---------+
| 217007 | 112542 |
| 133504 | 52666 |
| 1173542 | 51593 |
| 491368 | 43274 |
| 1202492 | 42547 |
| 1128178 | 37218 |
| 308768 | 34025 |
| 1299933 | 33739 |
| 192303 | 32135 |
| 19049 | 30996 |
+---------+---------+
+---------+---------+
+---------+---------+
| 2108334 | 11932 |
| 1656807 | 858 |
| 1195748 | 248 |
| 1315163 | 199 |
| 508175 | 165 |
| 202871 | 144 |
| 20867 | 122 |
| 387129 | 120 |
| 162001 | 105 |
| 51974 | 105 |
+---------+---------+
Pakages の結果 Works の結果

View Slide

+---------+---------+
+---------+---------+
| 217007 | 112542 |
| 133504 | 52666 |
| 1173542 | 51593 |
| 491368 | 43274 |
| 1202492 | 42547 |
| 1128178 | 37218 |
| 308768 | 34025 |
| 1299933 | 33739 |
| 192303 | 32135 |
| 19049 | 30996 |
+---------+---------+
+---------+---------+
+---------+---------+
| 2108334 | 11932 |
| 1656807 | 858 |
| 1195748 | 248 |
| 1315163 | 199 |
| 508175 | 165 |
| 202871 | 144 |
| 20867 | 122 |
| 387129 | 120 |
| 162001 | 105 |
| 51974 | 105 |
+---------+---------+
パフォーマンスに

問題はない

View Slide

+---------+---------+
+---------+---------+
| 217007 | 112542 |
| 133504 | 52666 |
| 1173542 | 51593 |
| 491368 | 43274 |
| 1202492 | 42547 |
| 1128178 | 37218 |
| 308768 | 34025 |
| 1299933 | 33739 |
| 192303 | 32135 |
| 19049 | 30996 |
+---------+---------+
+---------+---------+
+---------+---------+
| 2108334 | 11932 |
| 1656807 | 858 |
| 1195748 | 248 |
| 1315163 | 199 |
| 508175 | 165 |
| 202871 | 144 |
| 20867 | 122 |
| 387129 | 120 |
| 162001 | 105 |
| 51974 | 105 |
+---------+---------+

問題あり

View Slide

**************** 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 ****************
id: 1
select_type: SIMPLE
table: watchlists
partitions: NULL
type: ref
target_table_name_user_i
d

key_len: 768
ref: const
rows: 139616
filtered: 100.00
Pakages の EXPLAIN Works の EXPLAIN

View Slide

Pakages の EXPLAIN
**************** 1. row ****************
id: 1
select_type: SIMPLE
table: watchlists
partitions: NULL
type: ref
key_len: 768
ref: const
rows: 6791813
filtered: 100.00
**************** 1. row ****************
id: 1
select_type: SIMPLE
table: watchlists
partitions: NULL
type: ref
d

key_len: 768
ref: const
rows: 139616
filtered: 100.00
Works の EXPLAIN

問題はない

利用されているけど

View Slide

Pakages の EXPLAIN
**************** 1. row ****************
id: 1
select_type: SIMPLE
table: watchlists
partitions: NULL
type: ref
key_len: 768
ref: const
rows: 6791813
filtered: 100.00
**************** 1. row ****************
id: 1
select_type: SIMPLE
table: watchlists
partitions: NULL
type: ref
d

key_len: 768
ref: const
rows: 139616
filtered: 100.00
Works の EXPLAIN
対象行数が多く

遅くなる可能性

View Slide

88
大きな偏りは

もはや別物

View Slide

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

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

✔︎
元々分けるべき設計

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

View Slide

90

から

やるべきことが見える

View Slide

✔︎
ステージングで確認

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

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

　　→ EXPLAIN 実行で事前に解決

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

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

View Slide






92

View Slide

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

MySQL のスローログ

View Slide

1
2
2


View Slide

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

View Slide

CakePHP 4
画面で実行された

SQL が全て

表示される

View Slide

CakePHP 4
n+1 な SQL も

一目でわかる

View Slide

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

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

✔︎
1つずつ EXPLAIN

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

View Slide

99
次は

本番環境の監視

View Slide

1
2
2

2
1

View Slide

101
障害時に

流れた SQL は

記録しないと

残らない

View Slide

102
障害発生後に

原因を探せない

View Slide

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

View Slide

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

View Slide

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

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

View Slide

106
CloudWatch じゃなく

もっと

便利に可視化

したい

View Slide

✔︎
CloudWatch から Slack へ

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

✔︎
DataDog などでコード特定

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

View Slide

Slack 通知例

View Slide

Slack 通知例実際の

スロークエリ

View Slide

Slack 通知例
実行時間が

わかる

View Slide

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

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

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

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

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

特定
テーブル名、機能、

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

PullRequest 作成
EXPLAIN、分析、修正

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

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

修正した機能の確認

View Slide

RDS アラート

を検知

スローログを

検索・特定

コードを

特定

修正して

PullRequest 作成

リリース

経過を観察


View Slide

118
もっと良くするため

課題がある

View Slide

✔︎
コード特定の自動化

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

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

　　→ 同じ SQL の回数、実行時間を集計
課題

View Slide

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

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

✔︎
通知時にコード特定

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

View Slide

RDS アラート

を検知

スローログを

検索・特定

コードを

特定

修正して

PullRequest 作成

リリース

経過を観察

ここが

View Slide

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

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

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

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

View Slide

RDS アラート

を検知

スローログを

検索・特定

コードを

特定

修正して

PullRequest 作成

リリース

経過を観察

発生率が

View Slide

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

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

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

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

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

　　→ DataDog、pt-query-digest など検討
まとめ

View Slide

125
後手から

先手を目指し

より本質対応を

View Slide






126

View Slide

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

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

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

View Slide



1
2
2
2
1
1
3

View Slide

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

徹底解説!!

読みましょう！

View Slide

130
だとこの章が

終わるので…

View Slide

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

View Slide

EXPLAIN 実行例
************** 1. row **************
id: 1
select_type: SIMPLE
table: results
partitions: NULL
type: range
key: created
key_len: 6
ref: NULL
rows: 433396
filtered: 100.00
mysql> EXPLAIN
-> SELECT
-> id,
-> work_id,
-> user_id,
-> status
-> FROM
-> results
-> WHERE
-> created BETWEEN
'2021-02-26 10:25:36' AND
'2021-03-26 10:25:36'\G
先頭に

EXPLAIN と

書くだけ

View Slide

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

View Slide

EXPLAIN 実行例
************** 1. row **************
id: 1
select_type: SIMPLE
table: results
partitions: NULL
type: range
key: created
key_len: 6
ref: NULL
rows: 433396
filtered: 100.00
mysql> EXPLAIN
-> SELECT
-> id,
-> work_id,
-> user_id,
-> status
-> FROM
-> results
-> WHERE
-> created BETWEEN
'2021-02-26 10:25:36' AND
'2021-03-26 10:25:36'\G
この 4つを

お話しします

View Slide

✔︎
複合的に見る

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

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

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

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

　　・対象が多い
前提

View Slide

・ALL：対応必須

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

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

・index：対応必須

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

　　→ 対象が多い
type

View Slide

・range：注意

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

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

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

・const：安心

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

　　→ 等価比較で最高速
type

View Slide

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

　　→ 使われるインデックス

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

　　→ その過程で選択される

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

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

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

View Slide

・検索対象のレコード数

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

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

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

　　→ 絞り込みが足りない

　　→ 当初よりレコード数が増えている
rows

View Slide

・Using index：高速

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

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

　　→ ただし rows の割合が多い場合は注意
Extra

View Slide

・Using index condition (ICP)：高速

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

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

　　→ MySQL 5.6 からの機能

Extra

View Slide

・Using
fi
lesort：注意

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

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

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

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

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

　　→ Using temporary が一緒に出ることが多い
Extra

View Slide

✔︎
複合的に見る

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

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

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

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

　　・対象が多い
前提（再掲）

View Slide



1
2
2

2
1
1
3

View Slide

145
単純に


足りない場合

View Slide

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

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

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

View Slide

147
SQL
SELECT
Feedbacks.user_id,
Feedbacks.work_id,
FROM
feedbacks Feedbacks
WHERE

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

AND ‘2021-09-18 15:19:21'
仕事に対する

評価のテーブル

View Slide

148
SQL
SELECT
Feedbacks.user_id,
Feedbacks.work_id,
FROM
feedbacks Feedbacks
WHERE

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

AND ‘2021-09-18 15:19:21'
評価の値が

まだない人

View Slide

149
SQL
SELECT
Feedbacks.user_id,
Feedbacks.work_id,
FROM
feedbacks Feedbacks
WHERE

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

AND ‘2021-09-18 15:19:21'
とある

2日間

View Slide

150
実測
+---------+------------------+---------+------------+-------------+
| user_id | feedback_user_id | work_id | evaluation | description |
+---------+------------------+---------+------------+-------------+
| 2583253 | 2584632 | 3759308 | NULL | |
(தུ)
| 71384 | 2331284 | 3743634 | NULL | |
+---------+------------------+---------+------------+-------------+

View Slide

151
実測
+---------+------------------+---------+------------+-------------+
+---------+------------------+---------+------------+-------------+
| 2583253 | 2584632 | 3759308 | NULL | |
(தུ)
| 71384 | 2331284 | 3743634 | NULL | |
+---------+------------------+---------+------------+-------------+

View Slide

152
EXPLAIN
************************* 1. row *************************
id: 1
select_type: SIMPLE
table: Feedback
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2832867
filtered: 1.11
Extra: Using where

View Slide

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

View Slide

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

多い

(全体の 90 %)

View Slide

156
mysql> show index from feedbacks;
+-----------+------------+--------------------+--------------+------------------+
+-----------+------------+--------------------+--------------+------------------+
| 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 |
+-----------+------------+--------------------+--------------+------------------+
WHERE 句に該当する

インデックスがない

View Slide

157
✔︎
もっとも単純

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

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

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

View Slide

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

View Slide

159
実測
+---------+------------------+---------+------------+-------------+
+---------+------------------+---------+------------+-------------+
| 2583253 | 2584632 | 3759308 | NULL | |
(தུ)
| 71384 | 2331284 | 3743634 | NULL | |
+---------+------------------+---------+------------+-------------+
十分な

パフォーマンス

View Slide

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

View Slide

161
EXPLAIN
************************* 1. row *************************
id: 1
select_type: SIMPLE
table: Feedbacks
partitions: NULL
type: range
key_len: 11
ref: NULL
rows: 525
filtered: 100.00
インデックスでの

範囲 (range) 検索

View Slide

162
EXPLAIN
************************* 1. row *************************
id: 1
select_type: SIMPLE
table: Feedbacks
partitions: NULL
type: range
key_len: 11
ref: NULL
rows: 525
filtered: 100.00
作ったインデックス
が選択された

View Slide

163
EXPLAIN
************************* 1. row *************************
id: 1
select_type: SIMPLE
table: Feedbacks
partitions: NULL
type: range
key_len: 11
ref: NULL
rows: 525
filtered: 100.00
絞り込み行数が

実際の検索行数へ

View Slide

164
EXPLAIN
************************* 1. row *************************
id: 1
select_type: SIMPLE
table: Feedbacks
partitions: NULL
type: range
key_len: 11
ref: NULL
rows: 525
filtered: 100.00
インデックスを使っ
た検索ができている

View Slide

165
EXPLAIN
************************* 1. row *************************
id: 1
select_type: SIMPLE
table: Feedbacks
partitions: NULL
type: range
key_len: 11
ref: NULL
rows: 525
filtered: 100.00
ちょっと気になる

warning

View Slide

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

View Slide

167
warning 表示
************************ 1. row ************************
Level: Note
Code: 1003
15:19:21' and '2021-09-18 15:19:21'))
EXPLAIN
EXTENDED

（追加情報）

View Slide

168
warning 表示
************************ 1. row ************************
Level: Note
Code: 1003
15:19:21' and '2021-09-18 15:19:21'))
EXPLAIN 時に

常に表示される

View Slide

169
warning 表示
************************ 1. row ************************
Level: Note
Code: 1003
15:19:21' and '2021-09-18 15:19:21'))
オプティマイザの

最適化の際のメモ

View Slide

170
warning 表示
************************ 1. row ************************
Level: Note
Code: 1003
15:19:21' and '2021-09-18 15:19:21'))
SQL を

書き換えたよ

View Slide

171
warning 表示
************************ 1. row ************************
Level: Note
Code: 1003
15:19:21' and '2021-09-18 15:19:21'))
IS NULL から

isnull 関数に

書き換え

View Slide




Extra (再掲)

View Slide




Extra (再掲)
カバリングインデックスを

View Slide

SQL
SELECT
Feedbacks.created
FROM
feedbacks Feedbacks
WHERE

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

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

インデックスに

合わせる

View Slide

EXPLAIN
id: 1
select_type: SIMPLE
table: Feedbacks
partitions: NULL
type: range
key_len: 11
ref: NULL
rows: 525
filtered: 100.00
Extra: Using index
カバリング

インデックス

View Slide

実測
+------------+---------------------+
| evaluation | created |
+------------+---------------------+
| NULL | 2021-09-16 15:29:36 |
(தུ)
| NULL | 2021-09-18 14:53:13 |
+------------+---------------------+
0.06 -> 0.00

若干高速に

View Slide

178
インデックス

フルスキャンで

遅い例

View Slide

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

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

AND ‘2021-05-01 23:59:59'
GROUP BY
target_id

View Slide

SELECT
target_id,
FROM
watchlists
WHERE
AND created

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

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

お気に入り数

View Slide

SELECT
target_id,
FROM
watchlists
WHERE
AND created

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

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

2 日間

View Slide

SELECT
target_id,
FROM
watchlists
WHERE
AND created

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

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

グルーピングして

count で集計

View Slide

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

非常に遅い

View Slide

184
watchlists 総レコード数
+----------+
| count(1) |
+----------+
| 14666790 |
+----------+
1466 万

レコード

View Slide

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

View Slide

186
EXPLAIN
************************* 1. row *************************
id: 1
select_type: SIMPLE
table: Watchlists
partitions: NULL
type: index
key: work_id
key_len: 4
ref: NULL
rows: 13001409
filtered: 1.11
Extra: Using where
インデックス

フルスキャン

View Slide

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

インデックスがなく

View Slide

188
EXPLAIN
************************* 1. row *************************
id: 1
select_type: SIMPLE
table: Watchlists
partitions: NULL
type: index
key: work_id
key_len: 4
ref: NULL
rows: 13001409
filtered: 1.11
Extra: Using where
マッチしない

インデックスを

選択した

View Slide

189
EXPLAIN
************************* 1. row *************************
id: 1
select_type: SIMPLE
table: Watchlists
partitions: NULL
type: index
key: work_id
key_len: 4
ref: NULL
rows: 13001409
filtered: 1.11
Extra: Using where
1300 / 1466 万

全体の 88 % が

検索対象

View Slide

190
mysql> show index from watchlists;
+------------+------------+-----------------------------+--------------+---------------------+
+------------+------------+-----------------------------+--------------+---------------------+
| 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 |
+------------+------------+-----------------------------+--------------+---------------------+
WHERE 句に該当する

インデックスがない

View Slide

191
mysql> show index from watchlists;
+------------+------------+-----------------------------+--------------+---------------------+
+------------+------------+-----------------------------+--------------+---------------------+
| 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 |
+------------+------------+-----------------------------+--------------+---------------------+
オプティマイザが

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

インデックスを選択

View Slide

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

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

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

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

View Slide

193
mysql> ALTER TABLE watchlists ADD INDEX
target_table_name_created_target_id(
-> target_table_name,
-> created,
-> target_id
-> )
-> ;

View Slide

194
-> created,
-> target_id
-> )
-> ;
WHERE 句の

条件

View Slide

195
-> created,
-> target_id
-> )
-> ;
ORDER BY 句の

条件

View Slide

196
SQL の実行順序
1. FROM

　↓

2. ON

　↓

3. JOIN

　↓

4. WHERE

　↓
5. GROUP BY

　↓

6. HAVING

　↓

7. SELECT

　↓

8. ORDER BY

　↓

9. LIMIT

View Slide

197
-> created,
-> target_id
-> )
-> ;
SQL の実行順に

合わせる

View Slide

198
実測
+-----------+-----------------+
+-----------+-----------------+
| 274 | 1 |
| 2515788 | 1 |
+-----------+-----------------+

View Slide

+-----------+-----------------+
+-----------+-----------------+
| 274 | 1 |
| 2515788 | 1 |
+-----------+-----------------+
199
実測
1 min 46.02 sec

から改善

View Slide

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

View Slide

************************* 1. row *************************
id: 1
select_type: SIMPLE
table: watchlists
partitions: NULL
type: range
key_len: 774
ref: NULL
rows: 635
filtered: 100.00
filesort
201
EXPLAIN
インデックスでの

範囲 (range) 検索

View Slide

************************* 1. row *************************
id: 1
select_type: SIMPLE
table: watchlists
partitions: NULL
type: range
key_len: 774
ref: NULL
rows: 635
filtered: 100.00
filesort
202
EXPLAIN
作ったインデックス
が選択された

View Slide

************************* 1. row *************************
id: 1
select_type: SIMPLE
table: watchlists
partitions: NULL
type: range
key_len: 774
ref: NULL
rows: 635
filtered: 100.00
filesort
203
EXPLAIN
絞り込み行数が

実際の検索行数に

近くなった

View Slide

************************* 1. row *************************
id: 1
select_type: SIMPLE
table: watchlists
partitions: NULL
type: range
key_len: 774
ref: NULL
rows: 635
filtered: 100.00
filesort
204
EXPLAIN
カバリングインデックス

で検索できている

View Slide

************************* 1. row *************************
id: 1
select_type: SIMPLE
table: watchlists
partitions: NULL
type: range
key_len: 774
ref: NULL
rows: 635
filtered: 100.00
filesort
205
EXPLAIN
Using temporaty,

Using
fi
lesort

View Slide

************************* 1. row *************************
id: 1
select_type: SIMPLE
table: watchlists
partitions: NULL
type: range
key_len: 774
ref: NULL
rows: 635
filtered: 100.00
filesort
206
EXPLAIN
GROUP BY による

暗黙の ORDER BY

によるもの

View Slide

************************* 1. row *************************
id: 1
select_type: SIMPLE
table: watchlists
partitions: NULL
type: range
key_len: 774
ref: NULL
rows: 635
filtered: 100.00
filesort
207
EXPLAIN
rows と合わせて


影響なし

View Slide

208
GROUP BY を外した SQL
SELECT
target_id
FROM
watchlists
WHERE
AND created

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

AND ‘2021-05-01 23:59:59'
GROUP BY を

外して

EXPLAIN 確認

View Slide

209
GROUP BY を外した SQL
SELECT
target_id
FROM
watchlists
WHERE
AND created

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

AND ‘2021-05-01 23:59:59'
集計関数 count

も外す

View Slide

************************* 1. row *************************
id: 1
select_type: SIMPLE
table: watchlists
partitions: NULL
type: range
possible_keys: target_table_name_created_target_id
key_len: 774
ref: NULL
rows: 635
filtered: 100.00
Extra: Using where; Using index
210
GROUP BY を外した EXPLAIN

View Slide

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

Using
fi
lesort が

消える

View Slide



1
2
2
2
1
1
3

View Slide

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

View Slide

✔︎
本番環境で確認する

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

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

✔︎
master と replica 全台で調べる

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

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

View Slide

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`

View Slide

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

→ type, key, rows, Extra, etc

✔︎
インデックスを作成

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

View Slide

✔︎
検索対象 (rows) を判断

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

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

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

View Slide

✔︎
書き込み性能に影響する

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

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

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

View Slide






219

View Slide

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

ペアプロ

これからの課題
2
1
1
3

View Slide

1
2
2

ペアプロ

2
1
1
3

View Slide

レビュー（コード・設計・要件） 222
✔︎
SQL から要件まで

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

✔︎
原点に返ろう

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

View Slide

✔︎
正規化できないか

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

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

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

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

　　・ステータスでは？別テーブルに待避では？
223
レビュー観点例 (1/2)

View Slide

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

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

✔︎
本当に RDB に必要か？

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

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

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

View Slide

1
2
2

ペアプロ

2
1
1
3

View Slide

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

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

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

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

View Slide

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

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

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

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

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

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

View Slide

1
2
2

ペアプロ

2
1
1
3

View Slide

✔︎
社内勉強会

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

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

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

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

　　・https://github.com/pyama86/welcome-new-query
229

View Slide






230

View Slide

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

本質解決への流れ

PjM ・ PdM にヒアリング

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

一緒に実装
2
1
1
3
4
5

View Slide

1
2
2




一緒に実装
2
1
1
3
4
5

View Slide

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

　　・半端な機能でリリース

✔︎
要件と実装のズレ

　　・品質維持に苦労する

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

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

View Slide

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

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

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

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

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

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

View Slide

235
要件と実装のはざまで

苦しい

View Slide

236
どうアプローチ

すればいいのか

View Slide

1
2
2




一緒に実装
2
1
1
3
4
5

View Slide

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

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

✔︎

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

✔︎
一緒に実装

　　・指摘するだけではなく、自ら解決へ動く
238

View Slide

239
指摘だけじゃなく

一緒に改善

View Slide

1
2
2




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

View Slide

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

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

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

✔︎
リソース不足の理由

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

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

View Slide

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

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

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

✔︎
リソース認識合わせ

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

View Slide

243
ヒアリングせず

放置したら…

View Slide

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

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

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

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

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

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

View Slide

245
妥協は

水面下での

パフォーマンス劣化の

始まり

View Slide

246
層を超えた

ヒアリングを

View Slide

1
2
2




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

View Slide

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

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

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

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

View Slide

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

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

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

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

View Slide

1
2
2




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

View Slide

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

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

✔︎
パフォーマンス検証

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

View Slide

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

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

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

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

View Slide

253
0 → 1 が得意

100 → 200 が得意

ひとそれぞれ

View Slide

254
役割を認識して

自分から動く

View Slide

255
自分から動くから

相手も動いてくれる

View Slide

256
ここまでやって

初めて DBRE と

言える

View Slide

257
おわりに

View Slide

258
SQL 1つで障害になる

＝

SQL 1つで品質が上がる

View Slide

259
SQL から遡れば

ユーザーにたどり着く

View Slide

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

改善しよう

View Slide

261
SQL

怖い😱 -> やれるかも💡

View Slide

262
SQL への

敷居を低くして

View Slide

263
SQL 見てみよう

EXPLAIN してみよう

インデックス作ろう

View Slide

264

View Slide

どんなࠣ細な内容も大歓迎！
https://joind.in/talk/0b395

View Slide

✔︎
Discord

#track2-2-mysql-tune

✔︎
Twitter

@mamy1326

View Slide

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

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

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

View Slide

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

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

mamy1326

More Decks by mamy1326

Other Decks in Programming

Featured

Transcript