Slide 1
Slide 1 text
開発者向け MySQL 入門
2024/02/21
1
Slide 2
Slide 2 text
$ whoami
@wrsn0
SQL は書けません
3 年ぐらい DBA をやっていました
最近はセキュリティが気になっています
2
Slide 3
Slide 3 text
おことわり
社内の勉強会用に作成した資料であり、内容を保証するものではありません
「MySQL を触っているが運用に自信がない」くらいの、
アプリケーションエンジニアの方に向けて書いています
「SQL の書き方」は最後まで出てきません
出来るだけ「通常の MySQL」と「Aurora MySQL」の事情は、
意識して書いていますが、完全ではないです
3
Slide 4
Slide 4 text
目次
1. はじめに
2. アーキテクチャの概観
3. インデックス
4. オプティマイザと実行計画
5. チューニング
6. ケーススタディ
7. Q&A
4
Slide 5
Slide 5 text
1. はじめに
5
Slide 6
Slide 6 text
実施の意図
ほとんどのシステムにおいて、 DB のダウン ≒ サービスダウン となる
RDS のようなマネージドサービスの台頭により、DB を運用する負荷は下がって
きているが、その内部の知識が助けになることは未だにある
そこで、トラブル時の調査の勘所や、パフォーマンスの出し方について、
アーキテクチャから演繹的に理解する ことを目指す
「こういう仕組みだった気がするから、この事象の対応方針はこれでいいかな？」と
考えることができたらいいな
6
Slide 7
Slide 7 text
運用の難易度は、概ね規模に比例する
（当たり前かもしれないが）データサイズ・QPS が小さいうちは、
運用で困ることはほとんどない
サービスが成長することで問題は顕在化してくる一方、
その頃には身動きが取りづらくなっているというのが DB の悩ましいところ
7
Slide 8
Slide 8 text
MySQL とは
世界で最も利用されている OSS RDBMS の一つ
Facebook, Google, Youtube など多くの利用実績
https://db-engines.com/en/ranking
GPL・コマーシャルライセンスのデュアルライセンス
（他 RDBMS に比べ）シンプル・高速
OL T P（Online Transaction Processing） 向け
vs OL A P (Online Analytical Processing)
Redshift とか BigQuery のような DWH
近年は HTAP（Hybrid Transaction Analytical Processing）という、
OLTP/OLAP を両立したものも出てきた
8
Slide 9
Slide 9 text
なぜ流行ったか?
https://www.slideshare.net/matsunobu/ss-28303485/8
Facebook で DB Engineer をされている松信さんという方の言葉
抜粋すると（当時は）
無償だったから
導入の障壁が低かったから
似た立場の PostgreSQL より高速だと言われていたから
レプリケーションで簡単にスケールアウト出来たから
9
Slide 10
Slide 10 text
現時点での主流なバージョン
MySQL（Community 版）
https://blogs.oracle.com/mysql-jp/post/introducing-mysql-innovation-and-longterm-
support-lts-versions-jp
LTS（Long Term Support） の 8.0.x シリーズ
Bug fix, Security fix が主
Innovation Release の 8.1 ~ 8.3 シリーズ
機能追加をやっていくほう
Aurora MySQL
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraMySQLReleaseNotes/Auror
aMySQL.Updates.30Updates.html
3 系（8.0.x 系互換）
10
Slide 11
Slide 11 text
2. アーキテクチャの概観
11
Slide 12
Slide 12 text
Client
Connector
Parser
Optimizer
Executor
Storage Engine
（InnoDB, etc
）
MySQL Server
AST
SQL Query
Execution Plan
Send Result
Record Access
クエリが実行されるまで
Connector
クライアントからの接続管理と認証
Parser
文法チェック・SQL のパース
Optimizer
どうデータにアクセスすると効率が良いか、
主に統計情報から実行計画を立てる
（インデックスの選択, JOIN 順序等）
Executor / Handler
ストレージエンジンへのアクセスと、ソート等
「データを引っ張ってきてから」の色々
Storage Engine
テーブルへのデータの読み書き 12
Slide 13
Slide 13 text
ストレージエンジン
https://dev.mysql.com/doc/refman/8.0/ja/storage-engines.html
MySQL には複数のストレージエンジンが存在する
“Pluggable storage engine architecture”
テーブル単位でどのストレージエンジンを使うか選択できる
xxx_innodb_yyy
といった各種変数名はこの名残
が、MySQL ≒
InnoDB として、もはや InnoDB 以外は忘れていい
Aurora MySQL は InnoDB のみ対応
13
Slide 14
Slide 14 text
"素朴なデータベースの実装" を考えてみる
Client
Disk
Memory
参照
更新
（ディスクにデータを永続化したい・ディスクよりメモリのほうが高速という前提で）
参照クエリ
メモリ上にレコードがあれば、そのレコードをクライアントに返す。
メモリ上にレコードがなければ、ディスクから読み、メモリに追加しつつ返す。
更新クエリ
コミット時、ディスク上のテーブルに直接書き込む。
メモリとの整合性のため、対応するメモリ上のレコードは破棄する。
14
Slide 15
Slide 15 text
"素朴なデータベースの実装" の課題を想像してみる
Client
Disk
Memory
参照
更新
更新のたびにテーブルへの書き込みが発生してしまい、レイテンシが大きそう
メモリが十分に大きいか、賢く管理しないと、
むしろ参照系でメモリを経由するオーバーヘッドが大きくなりそう
レコードという単位はそのまま扱うには細かく、スループットが出なさそう
etc, といったことが考えられる
15
Slide 16
Slide 16 text
改めて InnoDB のアーキテクチャを見る
我々の想像している「レコード」というものは Tablespaces（*.ibd）にある
In-Memory に "Buffer Pool" なる重要なコンポーネントが居る
（ https://dev.mysql.com/doc/refman/8.0/ja/innodb-architecture.html より） 16
Slide 17
Slide 17 text
Tablespaces？
複数のレコードの塊が "ページ" という単位で管理される、ということだけ知ろう
"ページ" が MySQL が裏側でよしなに操作する際の主なデータ単位だから
（ https://jackyfkc.github.io/cs/data_store/mysql/innodb.html より） 17
Slide 18
Slide 18 text
SELECT クエリで何が起こるか
欲しいデータ（ページ）がバッファプールにあれば、バッファプールから返す。
なければ、テーブルスペースにアクセスして バッファプールに載せてから 返す
バッファプールは LRU（Least Recently Used）を改良したアルゴリズムで管理
雑な SELECT でうっかり全てのデータが入れ替わってしまわないための工夫
https://dev.mysql.com/doc/refman/8.0/ja/innodb-buffer-pool.html
Client
Buffer Pool
Log Buffer
Tablespace
Redo Log
Disk
Memory
Server
バッファプールにデータが無ければ
ディスクにアクセス
SELECT
バッファプールにデータがあれば
バッファプールからデータを返す
18
Slide 19
Slide 19 text
INSERT, UPDATE, DELETE クエリで何が起こるか (1)
バッファプール上のデータを更新しつつ、更新内容をログバッファに書き込み
Client
Buffer Pool
Log Buffer
Tablespace
Redo Log
Disk
Memory
Server
3.
ログバッファに書き込み
1.
更新クエリ
2.
バッファプール上のデータを更新
19
Slide 20
Slide 20 text
INSERT, UPDATE, DELETE クエリで何が起こるか (2)
コミット時はテーブルよりも先にログファイルが永続化される
WAL: Write-Ahead-Log という手法
Redo ログが永続化されたら書き込み完了とみなす
Client
Buffer Pool
Log Buffer
Tablespace
Redo Log
Disk
Memory
Server
2. Redo
ログに flush
1.
コミット
3. flush
出来たら Ack
コミット !=
テーブルへの永続化
20
Slide 21
Slide 21 text
INSERT, UPDATE, DELETE クエリで何が起こるか (3)
テーブルへの永続化（チェックポイント処理）は非同期に行う
これにより「Redo ログとバッファプールには反映されているけど、
テーブルスペースには反映されていない」なる状態が存在する
ある意味、バッファプールこそが最新のデータを持つとも言える
Client
Buffer Pool
Log Buffer
Tablespace
Redo Log
Disk
Memory
Server
ダーティページ :
テーブルに反映されていないデータ
バックグラウンドでテーブルに更新される
21
Slide 22
Slide 22 text
なぜ非同期に永続化するのか？
ディスクへの I/O 量と回数を削減したいから
回転メディアを前提とした当時の設計においては、特に重要だった
都度テーブルへ同期的に更新してしまうと、毎回ランダム書き込みになり高コスト
代わりに Redo ログへ、ページの変更操作を 追記
低コストなシーケンシャル書き込みに変換している、とも
「同じレコードへの操作なら最終状態だけ反映したろ！」といった細かいテクも
22
Slide 23
Slide 23 text
クラッシュリカバリ
サーバプロセスがクラッシュすると、メモリ上のページは失われる
Redo ログに書かれた、ページへの変更操作を再実行し、リカバリする
Client
Buffer Pool
Log Buffer
Tablespace
Redo Log
Disk
Memory
Server
Redo
ログをテーブルに適⽤
ロールフォワードリカバリ
23
Slide 24
Slide 24 text
Aurora での大きな改良点
ディスク I/O の責務の多くを専用の分散ストレージシステムに移譲
チェックポイントを気にする必要がなくなり、安定したレイテンシを出せるように
クラッシュリカバリも早くなった
（図は https://bit.ly/31ZMsMm より抜粋） 24
Slide 25
Slide 25 text
運用を考えたときに
データ構造やクエリに手を加えていなくても、将来的に「データ量が増える → バッファ
プールからあふれ、ストレージへのアクセスが増える → パフォーマンスが落ちる」とい
ったことがありえる
一般的にデータ量・レコード量が多いほど、あらゆる操作のコストが高くなり、
運用の難易度は上がる（それはそう）
より正確には、バッファプールは LRU ベースのため「生きたデータの量が」ではある
25
Slide 26
Slide 26 text
この節のまとめ
ディスク I/O は常に高コスト という前提がある
DB に限らず、あらゆるシステムで I/O 削減の工夫がされている
InnoDB アーキテクチャを見ても、様々な箇所に「バッファ」が登場する
参照・更新いずれの場合も データはメモリ（バッファプール）を通る
innodb_buffer_pool_size
は非常に重要なパラメータである
デフォルトは {DBInstanceClassMemory*3/4}
バッファプールは共有リソース
非効率なクエリは全体のパフォーマンスに影響しうる
他で使いたいキャッシュを追い出しちゃう可能性があるから
26
Slide 27
Slide 27 text
3. インデックス
27
Slide 28
Slide 28 text
MySQL のインデックス
イメージ図
実際には B+Tree というデータ構造ですが、今回はこのイメージで説明します
#
紙面の都合で擬似コードです
CREATE TABLE `member` (
`id`, `name`, `age`, `address`, #
カラム
PRIMARY KEY (`id`), #
プライマリキー
KEY `idx_age` (`age`) # age
のみのインデックス
KEY `idx_name_age` (`name`, `age`), # name
と age
の複合インデックス
)
id name age address
1 Deguchi 33 Tokyo
2 Bessho 26 Tokyo
3 Abe 41 Kyoto
4 Deguchi 50 Mie
5 Fukuda 22 Shiga
age (id)
22 5
26 2
33 1
41 3
50 4
クラスタインデックス
（Primary key : id
）
セカンダリインデックス
（ idx_name_age
）
セカンダリインデックス
（ idx_age
）
name age (id)
Abe 41 3
Bessho 26 2
Deguchi 33 1
Deguchi 50 4
Fukuda 22 3
28
Slide 29
Slide 29 text
全てはインデックス
https://dev.mysql.com/doc/refman/8.0/ja/innodb-index-types.html
クラスタインデックス
"テーブルそのもの" と言ってしまっていい
プライマリキーの順番に並んでいる
一部の例外を除き、すべてのカラムの値を持つ
セカンダリインデックス
"データを並べ替えた、テーブルの部分的なコピー" と言ってしまっていい
クラスタインデックス以外を指す
指定したカラムの値の順番に並んでいる
指定したカラム以外のカラムを参照するときのために、プライマリキーの値を持つ 29
Slide 30
Slide 30 text
例 1
SELECT * FROM member WHERE id < 4
クラスタインデックスは PK である id の順に並んでいるので、
その順にスキャンし全てのカラムを返す
セカンダリインデックスを使う理由がないので、使わない
id name age address
1 Deguchi 33 Tokyo
2 Bessho 26 Tokyo
3 Abe 41 Kyoto
4 Deguchi 50 Mie
5 Fukuda 22 Shiga
クラスタインデックス
（Primary key : id
）
age (id)
22 5
26 2
33 1
41 3
50 4
セカンダリインデックス
（ idx_name_age
）
セカンダリインデックス
（ idx_age
）
name age (id)
Abe 41 3
Bessho 26 2
Deguchi 33 1
Deguchi 50 4
Fukuda 22 3
30
Slide 31
Slide 31 text
例 2
SELECT * FROM member WHERE age < 23
クラスタインデックスは age の順には並んでいないので、
セカンダリインデックス idx_age
を使って age < 23 を探す
age = 22 のレコードは見つかったが、セカンダリインデックスだけでは全ての
カラムの値は分からないので、id = 5 を用いてクラスタインデックスから値を取得する
id name age address
1 Deguchi 33 Tokyo
2 Bessho 26 Tokyo
3 Abe 41 Kyoto
4 Deguchi 50 Mie
5 Fukuda 22 Shiga
age (id)
22 5
26 2
33 1
41 3
50 4
クラスタインデックス
（Primary key : id
）
セカンダリインデックス
（ idx_age
）
セカンダリインデックス
（ idx_name_age
）
name age (id)
Abe 41 3
Bessho 26 2
Deguchi 33 1
Deguchi 50 4
Fukuda 22 3
31
Slide 32
Slide 32 text
例 3
SELECT age FROM member WHERE age < 23
クラスタインデックスは age の順には並んでいないので、
セカンダリインデックス idx_age
を使って age < 23 を探す
age = 22 のレコードが見つかり、age カラムだけを返せばよいので、この時点でクライアン
トに値を返してしまうことができる
クラスタインデックスは利用されない。covering index とも言う
id name age address
1 Deguchi 33 Tokyo
2 Bessho 26 Tokyo
3 Abe 41 Kyoto
4 Deguchi 50 Mie
5 Fukuda 22 Shiga
age (id)
22 5
26 2
33 1
41 3
50 4
クラスタインデックス
（Primary key : id
）
セカンダリインデックス
（ idx_age
）
セカンダリインデックス
（ idx_name_age
）
name age (id)
Abe 41 3
Bessho 26 2
Deguchi 33 1
Deguchi 50 4
Fukuda 22 3 32
Slide 33
Slide 33 text
例 3'
SELECT id, age FROM member WHERE age < 23
クラスタインデックスは age の順に並んでいないので、
セカンダリインデックス idx_age
を使って age < 23 を探す
age = 22 のレコードが見つかる。id カラムも 暗黙的に セカンダリインデックスに含まれてい
るので、age カラムと共にクライアントに値を返してしまうことができる
これも covering index
id name age address
1 Deguchi 33 Tokyo
2 Bessho 26 Tokyo
3 Abe 41 Kyoto
4 Deguchi 50 Mie
5 Fukuda 22 Shiga
age (id)
22 5
26 2
33 1
41 3
50 4
クラスタインデックス
（Primary key : id
）
セカンダリインデックス
（ idx_age
）
セカンダリインデックス
（ idx_name_age
）
name age (id)
Abe 41 3
Bessho 26 2
Deguchi 33 1
Deguchi 50 4
Fukuda 22 3 33
Slide 34
Slide 34 text
例 4
SELECT * FROM member WHERE address = 'Mie'
どのインデックスも address の順に並んでいないので、
クラスタインデックスからすべての行を探す
id name age address
1 Deguchi 33 Tokyo
2 Bessho 26 Tokyo
3 Abe 41 Kyoto
4 Deguchi 50 Mie
5 Fukuda 22 Shiga
クラスタインデックス
（Primary key : id
）
age (id)
22 5
26 2
33 1
41 3
50 4
セカンダリインデックス
（ idx_name_age
）
セカンダリインデックス
（ idx_age
）
name age (id)
Abe 41 3
Bessho 26 2
Deguchi 33 1
Deguchi 50 4
Fukuda 22 3
34
Slide 35
Slide 35 text
例 5
SELECT id, name, age FROM member WHERE name LIKE 'A%'
idx_name_age
の name 部を使う。前方一致はインデックスが使える
id name age address
1 Deguchi 33 Tokyo
2 Bessho 26 Tokyo
3 Abe 41 Kyoto
4 Deguchi 50 Mie
5 Fukuda 22 Shiga
name age (id)
Abe 41 3
Bessho 26 2
Deguchi 33 1
Deguchi 50 4
Fukuda 22 3
クラスタインデックス
（Primary key : id
）
セカンダリインデックス
（ idx_name_age
）
age (id)
22 5
26 2
33 1
41 3
50 4
セカンダリインデックス
（ idx_age
）
35
Slide 36
Slide 36 text
例 6
SELECT * FROM member WHERE name LIKE '%eguchi%'
idx_name_age
の name 部は値の順に並んでいるだけなので、
「eguchi の中間一致・後方一致」はすべての行を見ないと分からない
クラスタインデックスからすべての行を探す
id name age address
1 Deguchi 33 Tokyo
2 Bessho 26 Tokyo
3 Abe 41 Kyoto
4 Deguchi 50 Mie
5 Fukuda 22 Shiga
クラスタインデックス
（Primary key : id
）
age (id)
22 5
26 2
33 1
41 3
50 4
セカンダリインデックス
（ idx_name_age
）
セカンダリインデックス
（ idx_age
）
name age (id)
Abe 41 3
Bessho 26 2
Deguchi 33 1
Deguchi 50 4
Fukuda 22 3
36
Slide 37
Slide 37 text
例 7
SELECT id FROM member WHERE age < 30 LIMIT 1
セカンダリインデックス idx_age
を使って age < 30 を探す
条件を満たすレコードが見つかった時点で、LIMIT 1 によって探索が打ち切られる
id name age address
1 Deguchi 33 Tokyo
2 Bessho 26 Tokyo
3 Abe 41 Kyoto
4 Deguchi 50 Mie
5 Fukuda 22 Shiga
age (id)
22 5
26 2
33 1
41 3
50 4
クラスタインデックス
（Primary key : id
）
セカンダリインデックス
（ idx_age
）
セカンダリインデックス
（ idx_name_age
）
name age (id)
Abe 41 3
Bessho 26 2
Deguchi 33 1
Deguchi 50 4
Fukuda 22 3
37
Slide 38
Slide 38 text
例 8
SELECT id FROM member WHERE address = 'Berlin' LIMIT 1
どのインデックスも address の順に並んでいないので、
クラスタインデックスからすべての行を探す
レコードが何億件あろうが address = 'Berlin' にマッチする行が見つかるまで探す
id name age address
1 Deguchi 33 Tokyo
2 Bessho 26 Tokyo
3 Abe 41 Kyoto
4 Deguchi 50 Mie
5 Fukuda 22 Shiga
クラスタインデックス
（Primary key : id
）
age (id)
22 5
26 2
33 1
41 3
50 4
セカンダリインデックス
（ idx_name_age
）
セカンダリインデックス
（ idx_age
）
name age (id)
Abe 41 3
Bessho 26 2
Deguchi 33 1
Deguchi 50 4
Fukuda 22 3
38
Slide 39
Slide 39 text
例 9
DELETE FROM member WHERE address = 'Berlin' LIMIT 1
更新系クエリも「どこにレコードがあるか」を調べるためにインデックスを利用する
さっきの例 8 同様、レコードが何億件あろうが address = 'Berlin' にマッチする行が見つかる
まで探す
id name age address
1 Deguchi 33 Tokyo
2 Bessho 26 Tokyo
3 Abe 41 Kyoto
4 Deguchi 50 Mie
5 Fukuda 22 Shiga
クラスタインデックス
（Primary key : id
）
age (id)
22 5
26 2
33 1
41 3
50 4
セカンダリインデックス
（ idx_name_age
）
セカンダリインデックス
（ idx_age
）
name age (id)
Abe 41 3
Bessho 26 2
Deguchi 33 1
Deguchi 50 4
Fukuda 22 3
39
Slide 40
Slide 40 text
インデックスがロックの範囲も決める
例 9 のようなケースではテーブルの全ての行にロックを取ってしまう
「条件に合った行をロック」ではなく、
「インデックスに対してロック」
もっと言うとネクストキーロックだが今回のスコープ外とする
そのため「 LIMIT 1
しておけばよくね？」は、よくない
（イメージで説明すると） age < 30
を条件に更新したいトランザクション A と、
age > 40
を条件に更新したいトランザクション B があったとしよう。
age
のインデックスが
ある : age でソートされているから A, B の操作は干渉しないことが分かる。
A → B の順に実行しても、B → A の順に実行しても問題ないので、並列に処理できる
ない : お互いの条件に合うレコードがどこにあるか分からないので、
全部のレコードを探す必要がある。A, B は並列に実行できない 40
Slide 41
Slide 41 text
インデックスは更新コストとのトレードオフ
セカンダリインデックスは "データを並べ替えた、テーブルの部分的なコピー"
なので、レコードの更新時にはセカンダリインデックスにも更新がされる
データの書き換え + データの順序を維持するためのコストがある
インデックスを作成しすぎると更新性能に影響が出る、という現象は
index shotgun という有名なアンチパターンで知られている
参照コストと更新コストのトレードオフだが、現実は参照重視なことが多い
id name age address
1 Deguchi 33 Tokyo
2 Bessho 26 Tokyo
3 Abe 41 → 51 Kyoto
4 Deguchi 50 Mie
5 Fukuda 22 Shiga
age (id)
22 5
26 2
33 1
50 4
41 → 51 3
クラスタインデックス
（Primary key : id
）
セカンダリインデックス
（ idx_age
）
name age (id)
Abe 41 → 51 3
Bessho 26 2
Deguchi 33 1
Deguchi 50 4
Fukuda 22 3
セカンダリインデックス
（ idx_name_age
）
41
Slide 42
Slide 42 text
原則 1 テーブルに 1 インデックスを使って探索する
member
テーブルには idx_age
と idx_name
のみが存在する。
WHERE age < ? AND name = ?
のような複合条件はどう処理されるか？
MySQL は 1 テーブルに 1 インデックスを使って探索する ので、
idx_name
か idx_age
のどちらか一方のインデックスが利用されるのが原則
idx_age
と idx_name
を候補とし、
「絞り込み やすそう な方」を
MySQL が判断（後述）してどちらかを利用する
"インデックスマージ" という、複数のインデックスを利用して探索したあと、
それらの集合演算を行うしくみが存在ものの、初手では考慮に入れなくていい
適用できるパターンの把握も難しいので、素直に複合インデックスを作るべき
「全カラムに単一カラムのインデックスを貼って、インデックスマージを狙えばよく
ね？」とはならない
42
Slide 43
Slide 43 text
複合インデックスのカラム順序
(name, age)
と (age, name)
は別物ですか
はい
前者ならまず name の順に並び替えて、name が同値のものを age で並び替えています
WHERE age < ?
だけで絞り込みたいときに (name, age)
は利用できません
(name, age)
と (age, name)
のどちらを作るべきですか
その DB に流れるクエリによりけりです
両方とも欲しい場合もありえます
雑に両方作って使ってない方を消す、という手もなくはないです
どちらも使える場合、最初のカラムでより絞り込める方を使うはずです
例えば (name, age)
があるなら (name)
は要らないことがほとんどです
43
Slide 44
Slide 44 text
十分なインデックスが存在するか？
「作るべきか」を最終的に判断するのは人間なので、都度やっていく
スロークエリログや APM から時間のかかるクエリを特定する
スロークエリログ には log_queries_not_using_indexes
のような便利そうな
変数もあるけど、フォーカスしたいのは 「クエリが遅いか」 なので、好みで
long_query_time
は 0.5 など 1 以下も設定できる
都度 EXPLAIN
で実行計画を確認する
44
Slide 45
Slide 45 text
不要なインデックスがないか？
performance_schema
という サーバが起動してから の稼働統計の入った便利な
テーブル群があり、さらにそれらを人間が読みやすくした sys
スキーマがある
https://dev.mysql.com/doc/refman/8.0/ja/sys-schema-views.html
schema_unused_indexes
は使っていないインデックスを教えてくれる
schema_redundant_indexes
は冗長なインデックスを教えてくれる
idx_name_age
が既にあるときの idx_name
みたいなヤツ
schema_index_statistics
はインデックスに対する統計情報を教えてくれる
読み取り行数、更新行数、それらのレイテンシ etc
MySQL 8.0 からはinvisible index を使って、いきなり削除ではなく
「一旦使わないようにして様子を見る」ことが出来るようになった
45
Slide 46
Slide 46 text
この節のまとめ
InnoDB は、クラスタインデックスというデータの持ち方をしている
セカンダリインデックスは "データを並べ替えた、テーブルの部分的なコピー"
インデックスは参照処理と更新のトレードオフ
とはいえ、現実は参照重視なことが多く、
「足りなくて困る」ことが多い
インデックスがロックの範囲も決める
適切なインデックスを作ると、バッファプールが効率よく使える
バッファプールに載るのは「クライアントに返したレコード」ではなく
「スキャンしたレコード」
。
「実際に必要なデータ」を不足なく載せたい
インデックスもバッファプールに載る
46
Slide 47
Slide 47 text
4. オプティマイザと実行計画
47
Slide 48
Slide 48 text
前節で出るであろう疑問
そもそも「どのインデックスを使うべきか」はどうやって判断しているのか？
使えそうなインデックスが複数あるとき、どのように優先されるのか
もっと広く言うと、JOIN の順序だったり、アクセスパス全般
何らかのロジックに基づいてデータへのアクセス方法が決まるのであれば、
それは制御できるのか？
48
Slide 49
Slide 49 text
Client
Connector
Parser
Optimizer
Executor
Storage Engine
（InnoDB, etc
）
MySQL Server
AST
SQL Query
Execution Plan
Send Result
Record Access
クエリが実行されるまで
Connector
クライアントからの接続管理と認証
Parser
文法チェック・SQL のパース
Optimizer
どうデータにアクセスすると効率が良いか、主
に統計情報から実行計画を立てる
（インデックスの選択, JOIN 順序等）
Executor / Handler
ストレージエンジンへのアクセスと、ソート等
「データを引っ張ってきてから」の色々
Storage Engine
テーブルへのデータの読み書き 49
Slide 50
Slide 50 text
オプティマイザと実行計画
https://dev.mysql.com/doc/refman/8.0/ja/explain.html
SQL は「どのようにデータを取得するか」を記述しない
オプティマイザがクエリ・テーブル構造・統計情報から、
どのようにデータを取得するのが最も効率がよいかを判断している
確実に 人間が正しい自信があるとき、ヒント句などで調整できる
インデックスヒント
https://dev.mysql.com/doc/refman/8.0/ja/index-hints.html
オプティマイザヒント
https://dev.mysql.com/doc/refman/8.0/ja/optimizer-hints.html
JOIN 順の指定
https://dev.mysql.com/doc/refman/8.0/ja/join.html
50
Slide 51
Slide 51 text
統計情報？
https://dev.mysql.com/doc/refman/8.0/ja/innodb-statistics-estimation.html
MySQL は各テーブルのページをサンプリングして、統計情報として把握している
mysql.innodb_table_stats
で見えたりする
データ移行等で「概算値」を見てしまい、行数が違って焦るのはあるある話
余談として、 innodb_stats_persistent_sample_pages
という「何ページをサンプリング
対象にするか」というパラメータがある。MySQL デフォルトは 20
Aurora MySQL 2 系はデフォルトで 128 だが、3 系でまた 20 へ…… なんで？
51
Slide 52
Slide 52 text
実行計画
https://dev.mysql.com/doc/refman/8.0/ja/explain.html
おなじみ EXPLAIN
で確認できる
詳しい読み方は、都度調べましょう
mysql> explain select * from employees where emp_no = 10001\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
52
Slide 53
Slide 53 text
クエリが同じなら実行計画も同じ、とは限らない
たとえば
インデックスの有無
バージョンアップによるオプティマイザの変化
8.0 から入った Hash join や、新しい最適化アルゴリズム
データ量、カーディナリティの変化
そもそもデータは偏るもの（大口の顧客的な）
統計情報の再取得
一定量のデータが更新されるなどを契機に、統計情報も更新される
一応、 ANALYZE TABLE
という DDL で ガチャ 能動的な更新が出来る
53
Slide 54
Slide 54 text
カーディナリティ
「インデックス」の節で挙げた例は、いずれも「インデックスを使うことで十分にしぼ
り込めることが期待される」という前提がある
実際には、
「セカンダリインデックスを使っても大して絞り込めないので、最初からクラ
スタインデックスにアクセスしたほうがよさそう」とオプティマイザが判断することも
よくある
カラム内に異なる値がどれほどあるかを カーディナリティ と呼ぶ。インデックスを貼る
ときは、カーディナリティの高い（値の種類の多い ≒ 絞り込みやすい）カラムを選ぶと
効果が高い。
54
Slide 55
Slide 55 text
補足: 「MySQL は JOIN が遅い」という言説
おそらく、そう言われるもとになったのは
（その頃の）オプティマイザが賢くなかった
最適化のアレコレが（他の DB に比べ）不足していた
JOIN アルゴリズムが NLJ しかなかった
いい感じのクエリの書き換えを MySQL 側でやってくれなかった
統計情報が不正確だった
MySQL 5.5 で innodb_stats_persistent_sample_pages
に相当するパラメータは、
デフォルト 8 ページしかなかった
など、様々な由来がありそうです。
が、通説を信じるのではなく試したほうがいいです
55
Slide 56
Slide 56 text
最大ケースを想像できるとよい
同じ構造のクエリに見えて、実行計画が変わってしまう例
LIMIT x の絞り込み件数
WHERE .. IN .. の対象の要素の数（参考: freee さんの記事）
特定の閾値を超えるとパフォーマンスが劣化する例
GROUP BY
や ORDER BY
のような一時テーブルを利用するクエリで件数が多い
https://dev.mysql.com/doc/refman/8.0/ja/internal-temporary-tables.html
集計操作を伴うクエリに対し、一定サイズまではメモリ上に一時テーブルを用いるが、
それより大きいとメモリからディスクに書き出す挙動をする
ディスクに書き出す処理は高コストなので「特定の ID を指定したクエリでやたら遅いんだけ
ど」という事象が発生する 56
Slide 57
Slide 57 text
この節のまとめ
どのようにデータにアクセスするかはオプティマイザが決めている
インデックスを使うべきか、どれを使うべきか、etc
基本的にはオプティマイザ任せでいい
EXPLAIN
は読めるようになろう
各種ヒントや JOIN 順の指定で、オプティマイザの動作をある程度制御できる
あくまで「その時点での（データ量・分布での）判断では」になるので、
ヒントによる制御は、運用（見直し）が発生するとも考えられる
クリティカルなシステムなどで、実行計画を安定させたいモチベーションはあって、
Oracle や PostgreSQL はこの辺の運用がもっと細かくやれる
57
Slide 58
Slide 58 text
5. チューニング
58
Slide 59
Slide 59 text
性能の悩みにどう向き合うか
パフォーマンス問題はワークロードによりけり
システムの数だけ課題があるので、テストとモニタリングが重要
机上ですべての挙動を把握しようとするより、手軽に本番同様の環境で
テスト出来るようにしたほうがコスパがいいことがよくある
データ量、カーディナリティ、リクエスト量 は近くあるべき
トラブルが発生した際に再現性も確保しやすくなる
Aurora であれば、クローン機能などは積極的に使いたい
パフォーマンステストも、なるべく本番同様のリクエストを模する
キャッシュの影響を丸めるためにある程度の時間、負荷をかけ続ける
単発のクエリだけでは心もとないので、シナリオを用意するとよい
59
Slide 60
Slide 60 text
前提知識｜ステータス変数
https://dev.mysql.com/doc/refman/8.0/ja/server-status-variables.html
Connections
や Uptime
のように、サーバの稼働状況を表すもの
show [global] status like '%{variable_name}%';
で確認できる
ただ、主要なものは Performance Insights（後述）や CloudWatch Metrics でも確認出来る
ので、これを直接実行する機会は（難しめの問題に当たらない限り）そうないかもしれない
スコープの概念が存在する
グローバル: サーバ全体で持つ値
セッション: セッション（接続）ごとに持つ値
60
Slide 61
Slide 61 text
前提知識｜システム変数
https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html
innodb_buffer_pool_size
のように、そのサーバの設定を表すもの
RDS のパラメータグループで変更しているのはこれ
show [global] variables like '%{variable_name}%';
で確認できる
スコープの概念が存在する
グローバル : サーバ全体で持つ値
セッション : セッション（接続）ごとに持つ値
このことを知っていると、
「とある接続にだけ set wait_timeout = x;
を実行して切り分
ける」といった所作ができるようになる
61
Slide 62
Slide 62 text
チューニングのサイクル
1. 問題を特定する
show full processlist
、スロークエリログおよび pt-query-digest
、
show engine innodb status
、 sys
スキーマ など
2. 遅い理由を分析する
EXPLAIN
、Performance Insights、 show status
、各種メトリクスを見る など
3. 対処（チューニング）
・検証
インデックスの作成、ヒント句付与、クエリの書き換え など
対処が正しいか効果を測定し、最初に戻る
62
Slide 63
Slide 63 text
1. 特定｜show full processlist
https://dev.mysql.com/doc/refman/8.0/ja/show-processlist.html
「現在実行中の」 クエリを確認できる
Command が Query で実行時間が長い = 時間のかかっているクエリ
障害時は数秒おきに実行して、スタックしているのかを見たりする
あくまで実行時点のスナップショットを示すものなので、定点観測は別途工夫が必要
最近では p_s.processlist
テーブルを使うといいらしい
https://dev.mysql.com/doc/refman/8.0/ja/performance-schema-processlist-table.html
63
Slide 64
Slide 64 text
1. 特定｜スロークエリログ
https://dev.mysql.com/doc/refman/8.0/ja/slow-query-log.html
long_query_time
より時間のかかった 「実行完了した」 クエリを出力する
実行完了時に出力されるログなので、実行中のクエリはここに載っていない
慢性的に DB が遅いとき、一時的に long_query_time
を 0.1 などの値に小さくして
記録しつつ pt-query-digest で概観を把握する、といったことをよくやる
MySQL 8.0.14 以降・Aurora 3 系であれば、 log_slow_extra
変数が追
加の情報を出力するようになる。ON にしよう
Created_tmp_disk_tables
など「なぜ遅かったのか」の手がかりが増えるのが嬉しい
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0154
64
Slide 65
Slide 65 text
2. 分析｜クエリの効率の定量的な判断
Rows sent
に対し Rows examined
が大きいものは、一般に効率が悪い
Rows_sent : クライアントに返した行数
Rows_examined : 読み込んだ行数
MySQL は OLTP がメインターゲットなので、
「ゴソッと取ってくる」ような操作はそもそも向いていない
//
スロークエリログから見た例
// 1
行返したいだけなのに 370
万行読んでいる
// name
で絞り込むための適切なインデックスが存在すれば Rows_examined
を減らせそう
# Time: 2021-12-21T09:35:48.286685Z
# User@Host: hoge[fuga] @ [10.30.204.100] Id: 20051
# Query_time: 6.650755 Lock_time: 0.000064 Rows_sent: 1 Rows_examined: 3759162
SET timestamp=1640079348;
SELECT * FROM huge_table WHERE name = 'warashina' LIMIT 1;
65
Slide 66
Slide 66 text
2. 分析｜EXPLAIN
https://dev.mysql.com/doc/refman/8.0/ja/explain.html
おなじみ EXPLAIN
読み方を説明すると時間が足りないので、いいドキュメントを見つけてください
type, key, rows, Extra 列が読めればけっこう戦えます
https://nippondanji.blogspot.com/2009/03/mysqlexplain.html など
通常の EXPLAIN
が示すものは見積もりだけど、MySQL 8.0.18 からは
EXPLAIN ANALYZE
が追加され、
「実際にクエリを実行して」の実行時間を教えてくれ
るようになった
時間がかかっているステップがどこなのかを知れるようになったが、読み方は要練習
66
Slide 67
Slide 67 text
2. 分析｜Performance Insights
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/USER_PerfInsig
hts.Overview.html
RDS 向けの DB 版 APM のようなもの
無料枠でいいのでとりあえず有効化しておくのがオススメ
MySQL 側も performance_schema = ON
で情報を取得できるようにしておく
多少のメモリを消費するので、これを ON にした前提でサイズを考える
「トップ SQL」
「トップ待機イベント」からもぐら叩きをしていけばいい
io/table/sql/handler
等の具体的な待機イベント名の示す意味は、都度ググる
Rows sent, examined, そのクエリの実行回数等の情報が出るのも嬉しい
67
Slide 68
Slide 68 text
3. 対処｜チューニングの手段
a. パラメータチューニング
innodb_buffer_pool_size
など、MySQL のパラメータを変更する
いわゆる「全体最適」を目指して、スループットを向上させることが多い
Aurora MySQL の場合、
（経験上）積極的に行う機会はあまりない
b. クエリチューニング
クエリ単体をチューニングし、レイテンシ向上を狙う
「個別最適」のように見えて効果が小さいように見えるが、現実には非常に重要
バッファプールや CPU は共有リソース
効率のよいクエリは CPU を専有しないしバッファプールも荒らさないので、
結果的に DB 全体のパフォーマンスが向上することがよくある 68
Slide 69
Slide 69 text
3-a. 対処｜パラメータチューニング
以下の繰り返し
サーバのステータス変数やスロークエリログから、稼働状況を確認する
システム変数が適切かを判断する
設定値を調整する
Aurora MySQL の場合、パフォーマンス関連のシステム変数については、
わりと「そのまま」でうまく動くように設計されている
Amazon Aurora を使いこなすためのベストプラクティス 「チューニング指針」あたり
Amazon Aurora MySQL データベース設定のベストプラクティス も参考になる
広い意味では、スケールアップ・アウトもこれに相当する
69
Slide 70
Slide 70 text
スケールアップ
インスタンスサイズを変更し、単体での性能改善を図る
一部のパラメータはインスタンスサイズに比例している
お手軽なので、一時的な負荷増の緩和策にも
スケールアウト
Reader の利用やシャーディングで、全体での性能改善を図る
Reader の利用 : 読み込みのスケールアウト
シャーディング : 読み込み・書き込み両方のスケールアウト
それなりに工数は必要で、アプリ側の複雑性も増す
トラブル時の影響範囲（Blast radius）のコントロールにもなる
特定インスタンスのダウン = サービスダウン を回避
70
Slide 71
Slide 71 text
スケールアップが効果の無い・薄い例
事業者としては「お金で解決できる問題かどうか」を判断できると嬉しい
集計クエリの速度改善のためにコア数を増やす
MySQL の SQL 実行は原則シングルスレッド（ 1 クエリ = 1 CPU）なので、
大量行スキャンの 1 クエリを高速にしたいとき、コア数を増やしても改善しない
特定の行にロックが集中するケースも、コアを増やす意味はない
データサイズよりもはるかに大きいメモリを持つインスタンスサイズ
もちろん CPU や NW がサチる例もあるが、
クエリチューニング不足か、ロジックの問題なパターンのほうがよく見る
71
Slide 72
Slide 72 text
3-b. 対処｜クエリチューニング
インデックスを使いこなしてほしい
実行計画の制御
インデックスヒント
JOIN ヒント
オプティマイザヒント
「クエリチューニングで対処すべき」という状態までたどり着けば、
既にたくさんの有用なドキュメントが存在するので、あとは手際よくやるだけ
「 Rows examined
の値を小さくする」という原則に従っていけば、そうそう外さない
72
Slide 73
Slide 73 text
6. ケーススタディ
73
Slide 74
Slide 74 text
例題
※ 社内情報のため非公開
74
Slide 75
Slide 75 text
問題が発生した時に何を考えるか
例えば「DB が遅い」と相談されたとき
1. 「遅い」という事象を確定させる
DB が遅いということが、客観的事実（スロークエリログなど）により
分かっているのか、単に DB が疑わしいからそう言っているのか
2. 「遅い」ことを深堀りする
クエリ単体で遅いのか、DB が全体的に遅いのか
常に遅いのか、一定の条件で遅いのか
後者なら「たまに」
、
「とあるタイミングで」
、
「特定の顧客への処理で」
75
Slide 76
Slide 76 text
周辺の情報を整理する
継続中の問題か、過去発生した問題なのか
いつから問題が起こっているのか
対処しないとどうなるのか
対策にどこまでの工数をかけられるのか
このあたりで「まず緩和策を考える」かどうかを決める
76
Slide 77
Slide 77 text
補足: よくある「クエリが遅い」理由いろいろ
単純に処理するレコードが多い
インデックスがないか、使われていない
ロック範囲の競合が発生している
リソースが足りていない
バッファプールが足りなくてディスク IO が発生している
CPU usage が飽和している
クエリが余計な処理をしている
MySQL が苦手な処理をしている
一時テーブルの実体化（ Created_tmp_disk_tables
のカウント）が典型的
77
Slide 78
Slide 78 text
対応方針
※ 社内情報のため非公開
78
Slide 79
Slide 79 text
根本対応｜メトリクスを確認する (1/3)
Performance Insights を見る
io/table/sql/handler
というイベントが多くを占めている
ドキュメント より、テーブルへのアクセスに関するイベントであることが分かる
※ 社内情報のため画像非公開
79
Slide 80
Slide 80 text
根本対応｜メトリクスを確認する (2/3)
カウンターメトリクス から、ディスクアクセスが発生しているかを判断する
db.Cache.innoDB_buffer_pool_hits
はバッファプールから読めた回数
db.Cache.Innodb_buffer_pool_reads
は 読めなかった（ディスクアクセス） 回数
⇒ よって、リソース（メモリ）は足りていそう。CPU コア数を増やしても
単発のクエリは早くならないので、スケールアップは効果が薄い。
※ 社内情報のため画像非公開
80
Slide 81
Slide 81 text
根本対応｜メトリクスを確認する (3/3)
Created_tmp_disk_tables
ステータス変数は増加していない
スロークエリログからは、集計クエリが多数流れていることは分かる
ログに出ているということは、
「全部のクエリが帰ってこない」というわけではない
一連のタスクのうち一部（のテナント？）が終わらない
適当にピックアップしたログでは Rows_examined: 14088579
とかなりの行数だが、
この時点では「本当に 1400 万レコードを読むことが集計に必要」なのかは知らない
※ 社内情報のため非公開
81
Slide 82
Slide 82 text
補足: よくある「クエリが遅い」理由いろいろ
単純に処理するレコードが多い
インデックスがないか、使われていない
ロック範囲の競合が発生している
リソースが足りていない
バッファプールが足りなくてディスク IO が発生している
CPU usage が飽和している
クエリが余計な処理をしている
MySQL が苦手な処理をしている
一時テーブルの実体化（ Created_tmp_disk_tables
のカウント）が典型的
82
Slide 83
Slide 83 text
根本対応｜インデックス作成の検討
結局、素直に実行計画を見てインデックスの作成を検討すべき、となる
※ 社内情報のため非公開
83
Slide 84
Slide 84 text
根本対応｜インデックス作成のリリース
※ 社内情報のため非公開
84
Slide 85
Slide 85 text
対処のコツ
（DB に限った話ではないけど）
再現環境を作る
現代は clone といった機能で簡単に複製できる、ありがたい時代
最小の再現方法を探す
どの WHERE id = x なら再現する、など
共有する・人に聞く
後から来た人でも分かるよう、マメに post しておく
「人に聞く」ために状況を整理しておく
85
Slide 86
Slide 86 text
ex. その他雑多な話
86
Slide 87
Slide 87 text
メンテナンスの心得
大きめのメンテであれば、これぐらいは確認したい
動作テストを行う
メンテナンスの リハーサル を行う
切り戻しの判断基準 と手順 を決めておく
よく寝て 2 人で参加する
メンテ開始時に 確実に バックアップを取る
更新クエリが飛んでこないことを DB 側で 確認する
アプリを止めたから OK としない
87
Slide 88
Slide 88 text
トラブルを後から追うのは難しい
「ある時点でどのようにクエリが動作していたか」を追いかけるのは難しい。
ゆえに APM のようなツールでメトリクスを収集しておくことは大事
ロック競合なども、その時点の状態を保存したい
いつ発生するか分からない障害は手強い
問題を任意のタイミングで再現出来るのであれば、
メトリクスの収集もし放題と言えるので、再現検証もとても大事
88
Slide 89
Slide 89 text
普段は何をモニタリングすればいいのか
データベースは「クエリの結果を返す箱」のはずだから、
許容できるレイテンシでクエリが返せているか が最優先に見たいはず
接続性
エラーログ
スロークエリ、各種レイテンシ
CPU, RAM, 接続数といったメトリクスに機械的にアラートを設定するよりも、
ベースラインを知り、異常かどうかを把握できるようにしておきたい
大事なメトリクスだが、CPU High それ自身でユーザに影響があるわけではない
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/MonitoringOverview.ht
ml も参考に
89
Slide 90
Slide 90 text
普段は何をモニタリングすればいいのか
New Relic や Datadog のような監視 SaaS はよく出来ている
餅は餅屋
APM だけで十分なこともよくありそう
監視の考え方がドキュメントに記されていたりして参考になることも
メトリックの保存期間
季節性のあるワークロードなら 1年+1ヶ月 は保存したい
90
Slide 91
Slide 91 text
Aurora Serverless v2
よく出来ててすごいヤツ
ただし、サーバがスケールする のと システムがスケールする のは別の話
投資したリソースの分だけパフォーマンスも上がってほしいが、そうでないことはよくある
今まで通りクエリチューニング etc が重要なのは変わらない
細かいことを考えたくない・初期の運用の場合に特におすすめ
Provisioned は RI も使える（ 40〜60 % オフ）ことを加味すると、
コスト削減の方面はそんなに期待できない
スケールダウン時に縮小されたメモリの内容が、再度スケールアップした時に
再配置されているわけではない（多分）ので、p99 latency は安定しないかも
試しましょう
91
Slide 92
Slide 92 text
Aurora の場合のバージョンアップ戦略
バージョンポリシーなるものが公開されています
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/Aurora.VersionP
olicy.html
迷ったら最新版 or 一個前 or LTS で OK だと思います
基本的に、バージョンダウンは出来ない・やらないものと思ったほうがいいです
バージョンアップのテストには、clone を使ったり、
B/G deploy で作ったクラスタに読み込みワークロードを流すのもよいです
92
Slide 93
Slide 93 text
セオリーから外れない
データベースの失敗はダメージがでかいので、手堅い方に倒す
DB のやらなさそうな事をムリにさせないようにする
なんとなく正規化を崩さない
画像を RDB に入れない
ログを何でも全部 RDB に入れない
マイナーな機能に注意する
"日本語のカラム名にしてみました" は、マイナーなバグを踏みそうですよね
「機能として存在する」ことと「実運用に耐えている」ことは別物
93
Slide 94
Slide 94 text
Q&A
94
Slide 95
Slide 95 text
参考文献（書籍）
この資料を書いた人はこの本にお世話になっています:
『MySQL徹底入門 第4版』
『データ指向アプリケーションデザイン』
『入門 監視』
95
Slide 96
Slide 96 text
参考文献（Web ページ）
この資料を書いた人はこのページにお世話になっています:
MySQL 8.0 リファレンスマニュアル
MySQL道普請便り 記事一覧 | gihyo.jp
漢のコンピュータ道
yoku0825 さんの speakerdeck
Percona Database Performance Blog
USE THE INDEX, LUKE!
Hack MySQL
96