MySQLパフォーマンスチューニングTIPS

Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
MySQLパフォーマンスチューニングTIPS
updated: 2019/07/19
Yoshiaki Yamasaki / 山﨑由章
MySQL Senior Solution Engineer, Asia Pacific and Japan

View Slide

Safe Harbor Statement
以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。
また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはでき
ません。以下の事項は、マテリアルやコード、機能を提供することをコミットメントするも
のではない為、購買決定を行う際の判断材料になさらないで下さい。
オラクル製品に関して記載されている機能の開発、リリースおよび時期については、
弊社の裁量により決定されます。
2

View Slide

アジェンダ
MySQL 5.7でのパフォーマンス改善点
EXPLAINの読み方
3
1
2
3

View Slide

アジェンダ
EXPLAINの読み方
4
1
2
3

View Slide

• オプティマイザーの改善
• コンディションフィルターの適用
• MTS(マルチスレッドスレーブ)
• オプティマイザヒントの拡張
5

View Slide

オプティマイザーの改善
• オプティマイザーが進化し、OLTP系以外の処理に関しても実行速度が
改善している
6

View Slide

0
20
40
60
80
100
Q3 Q7 Q8 Q9 Q12
Execution time relative to 5.6
(%)
22クエリ中5クエリは、オプティマイザの改善により大幅にパフォーマンス改善
MySQL 5.6
MySQL 5.7
DBT-3 (Size Factor 10, CPU bound)
※Q8は 5.6：約25秒 ⇒ 5.7：約3秒まで短縮（データがキャッシュ上にあるテスト）
7

View Slide

DBT-3 (Size Factor 10)
0
20
40
60
80
100
Q2 Q18
(%)
CPU bound
5.6
5.7
22クエリ中2クエリは、オプティマイザの改善により著しくパフォーマンス改善
0
20
40
60
80
100
Q2 Q18
(%)
Disk bound
5.6
5.7
※Q2は 5.6：約3分 ⇒ 5.7：1秒、Q18は 5.6：約20分 ⇒ 5.7：40秒まで短縮（データがキャッシュ上にないテスト）
8

View Slide

コンディションフィルターの適用
• オプティマイザがJOINを実行する時のテーブルアクセス順序を決める時に、
WHERE句に指定した条件による絞り込みの影響を考慮するようになった
– JOINを実行する時は、行数の少ないテーブルからアクセスすることが望ましい
– WHERE句による絞り込みによって行数が削減できる場合がある
9
※パフォーマンスダウンする場合は、optimizer_switch='condition_fanout_filter=off' などで回避可能

View Slide

MTS（マルチスレッドスレーブ）
• レプリケーション使用時、デフォルトではスレーブはシングルスレッドで
動作するため、それによって遅延が発生することがある
– マスターでは更新処理内容をシリアライズ（直列化）してバイナリログに記録
– スレーブでは、リレーログで更新処理内容を受信
– スレーブでは、リレーログの内容を読み取って更新処理を反映
• マルチスレッドスレーブを使用することで、このような遅延を軽減可能
– MySQL 5.6では、スキーマが分かれていればマルチスレッドで処理可能
– MySQL 5.7では、同じスキーマであってもマルチスレッドで処理可能
10

View Slide

MTS（マルチスレッドスレーブ）
• マルチスレッドスレーブを有効にするためのシステム変数
– slave_parallel_workers=N ※Nにはスレッド数を指定
– slave_parallel_type=LOGICAL_CLOCK
• 注意事項
– マルチスレッドスレーブ有効時、スレーブでの更新処理順序がマスターと同一である
ことを保証するためには、slave_preserve_commit_order=ONに設定する必要がある
11

View Slide

MTS（マルチスレッドスレーブ）を効率化するための設定1
• マルチスレッドスレーブはマスターでグループコミットが行われた処理に対し
て適用されるため、グループコミットの効率化がMTSの効率化につながる
• グループコミットの効率化につながるシステム変数
– binlog_group_commit_sync_delay
• グループコミットを効率化するために、COMMIT実行直後にsyncせずに、しばらく待つための設定
• 待ち時間をマイクロ秒単位で設定
• 設定すると、単体のCOMMIT性能は落ちるので注意
– binlog_group_commit_sync_no_delay_count
• 上記パラメーターで待ち過ぎないために設定できるオプション
• このパラメーターで指定した数のCOMMIT待ちトランザクションが溜まれば
binlog_group_commit_sync_delay まで待たずに、即座にCOMMITする
12

View Slide

• MySQL 5.7.22以降、MySQL 8.0ではWRITESET方式による並列化も可能
– グループレプリケーションで使用している競合検出の仕組みを応用
– マスターでグループコミットが行われなかったトランザクションに対しても、
同じ行を更新していないトランザクションであればスレーブ側で並列化可能
⇒マスター側でシングルスレッドで実行された処理も、スレーブ側で並列化可能
– マスター側でのバイナリログの書込みに対して、追加のオーバーヘッドがかかる
ことに注意
13

View Slide

• WRITESET方式による並列化を使用するためのシステム変数
– transaction_write_set_extraction
• 「XXHASH64」に設定 ※MySQL 8.0.2以降の場合は、デフォルト設定がXXHASH64
– binlog_transaction_dependency_tracking
• 「WRITESET」もしくは「WRITESET_SESSION」と設定することで並列化可能
• 「WRITESET_SESSION」の場合は、セッション単位でのトランザクション実行順序は担保した上で並列化
• 「slave_preserve_commit_order=ON」を設定している場合、基本的には「WRITESET」を選択すると良い
• WRITESET方式による並列化をチューニングするためのシステム変数
– binlog_transaction_dependency_history_size
• 「同じ行を更新していない」という判断をするために保持する行ハッシュの数を設定
• デフォルト値は25000(行)
• 上限に達した場合、行ハッシュは一旦全てパージされるため更新対象行数が多い環境では
チューニングを推奨
14

View Slide

WRITESET方式による並列化の効果
15
• マスター側での並列度が低い環境でも、スレーブ側で高い並列度を実現
0
5000
10000
15000
20000
25000
30000
35000
40000
45000
50000
1 2 4 8 16 32 64 128 256
Updates/second Applies on the Replica
Number of Clients on the Master
Applier Throughput: Sysbench Update Index
COMMIT_ORDER WRITESET WRITESET_SESSION

View Slide

備考： MTS（マルチスレッドスレーブ）の進化の歴史
16
「MTS（マルチスレッドス
レーブ）を効率化するた
めの設定1」の話
「MTS（マルチスレッドス
レーブ）を効率化するた
めの設定2」の話

View Slide

オプティマイザヒント（MySQL 5.7以降で使用可能なヒント構文）
• 新しいヒント構文
– SELECT /*+ HINT1(args) HINT2(args) */ … FROM …
• 指定できるヒントはoptimizer_switchで指定できる内容と同様だが、
optimizer_switchセッション変数よりも細かい粒度で指定可能
– グローバル
– クエリーブロック
– テーブル
– インデックス
17

View Slide

optimizer_switch
• optimizer_switch により、オプティマイザの動作を制御可能
• BKA(Batched Key Access)、BNL(Block Nested Loop)、MRR(Multi-Range
Read)、などの最適化アルゴリズムの有効/無効を制御できる
• 詳細情報
8.9.3 Switchable Optimizations
https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html
• 8.8.5.2 切り替え可能な最適化の制御
http://dev.mysql.com/doc/refman/5.6/ja/switchable-optimizations.html
18

View Slide

オプティマイザヒント（MySQL 5.7以降）
ヒント名説明スコープ
BKA, NO_BKA BKA(Batched Key Access) join の有効/無効クエリーブロック, テーブル
BNL, NO_BNL BNL(Block Nested-Loop) join の有効/無効クエリーブロック, テーブル
MAX_EXECUTION_TIME クエリーの実行時間制限グローバル
MRR, NO_MRR MRR(Multi-Range Read) の有効/無効テーブル, インデックス
NO_ICP ICP(Index Condition Pushdown) の有効/無効テーブル, インデックス
NO_RANGE_OPTIMIZATION インデックスレンジスキャン、インデックスマージ、ルースインデックスス
キャン（Using index for group-by）の無効
テーブル, インデックス
QB_NAME クエリーブロック（1つ1つのサブクエリー）に名前を付ける
QB_NAMEヒントで名付けた名前を、他のヒント（BKAなど）で指定できる
クエリーブロック
SEMIJOIN, NO_SEMIJOIN 純結合変換による最適化の有効/無効
最適化を行う場合に指摘できる方式は以下の4種類
DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION
クエリーブロック
SUBQUERY サブクエリーの最適化方法を指定（INTOEXISTS or MATERIALIZATION）クエリーブロック
※参考マニュアル：8.9.2 Optimizer Hints
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
19

View Slide

オプティマイザヒント（MySQL 8.0以降）
ヒント名説明スコープ
INDEX_MERGE, NO_INDEX_MERGE インデックスマージの有効/無効テーブル, インデックス
JOIN_FIXED_ORDER FROM句で指定した順番に結合するクエリーブロック
JOIN_ORDER このヒントで指定した順番に結合するクエリーブロック
JOIN_PREFIX 最初に結合するテーブルを指定するクエリーブロック
JOIN_SUFFIX 最後に結合するテーブルを指定するクエリーブロック
MERGE, NO_MERGE 外部クエリーブロックと派生テーブル/ビューのマージの有効/無効テーブル
RESOURCE_GROUP SQL文の実行中にリソースグループを設定するグローバル
SKIP_SCAN, NO_SKIP_SCAN スキップスキャンの有効/無効テーブル, インデックス
SET_VAR SQL文の実行中だけシステム変数を変更するグローバル
※参考マニュアル：8.9.2 Optimizer Hints
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
20

View Slide

アジェンダ
EXPLAINの読み方
21
1
2
3

View Slide

• インビジブルインデックス（不可視索引）
• CTE（WITH句）と再帰的CTE
• Window 関数
• SKIP LOCKED と NOWAIT オプション
• 降順索引（Descending Indexes）
• オプティマイザーヒントの拡張
• リソースグループ
• 実行計画の精度向上（コストモデルの改善、ヒストグラム）
22

View Slide

インビジブルインデックス（不可視索引）
• オプティマイザーから見えない索引
– 索引の無効化とは異なる
– データ更新時にInvisible Indexesも更新される
• 2つのユースケース:
– 仮削除（ゴミ箱）
– 段階的な展開にてインデックスの有効性の確認
Feature Request
from DBAs
WL#8697: Support for INVISIBLE indexes
https://dev.mysql.com/worklog/task/?id=8697
23

View Slide

MySQL 8.0: 新しいSQL構文
• もっとも要求の多かった2つの機能
– CTE（Common Table Expressions） ※WITH句
– Window 関数
24
Feature Request
from Developers

View Slide

CTE（Common Table Expressions） ※WITH句
• サブクエリーの導出表 (derived table) の代替
• メリット
– 分析処理 SQL 文の可読性や処理性能の向上
– 階層構造データの検索効率化（再帰CTEを使用）、など
25
WITH tickets_filtered AS (
SELECT tickets.*, seats.doc
FROM tickets
INNER JOIN seats ON
tickets.seat_id = seats.id
WHERE tickets.event_id = 3
)
SELECT * FROM tickets_filtered
WHERE doc->"$.section" = 201¥G

View Slide

Window関数の例
• RANK関数
– ランキングを求めることが出来る
• LAG関数
– 1行前の値を参照できる
• SUM関数
– ウィンドウごとの合計値を求めることが出来る
26

View Slide

Window関数: RANK
SELECT name, dept_id AS dept, salary,
RANK() OVER w AS `rank`
FROM employee
WINDOW w AS (PARTITION BY dept_id
ORDER BY salary DESC);
name dept_id salary rank
Newt NULL 75000 1
Ed 10 100000 1
Newt 10 80000 2
Fred 10 70000 3
Michael 10 70000 3
Jon 10 60000 5
Dag 10 NULL 6
Pete 20 65000 1
Lebedev 20 65000 1
Jeff 30 300000 1
Will 30 70000 2
27

View Slide

Window関数の種類
• 集計
– COUNT, SUM, AVG, MAX, MIN
• ランキング
– RANK, DENSE_RANK, PERCENT_RANK,
– CUME_DIST, ROW_NUMBER
• 分析
– NTILE, LEAD, LAG
– NTH_VALUE, FIRST_VALUE, LAST_VALUE
28

View Slide

SELECT… FOR UPDATE の拡張
SELECT * FROM tickets
WHERE id IN (1,2,3,4)
AND order_id IS NULL
FOR UPDATE
NOWAIT;
SELECT * FROM tickets
WHERE id IN (1,2,3,4)
AND order_id IS NULL
FOR UPDATE
SKIP LOCKED;
行が既にロックされ
ていれば、直ぐに
エラーを返す
行が既にロックされてい
れば、その行に対する
ロック取得はあきらめる
29

View Slide

降順索引 (Descending Indexes)
CREATE TABLE t1 (
a INT,b INT,
INDEX a_b (a DESC, b ASC));
• 5.7: 昇順インデックスが作成され,サーバーがそれを逆方向にスキャンします
• 8.0: 降順でインデックスが作成され,サーバはそれをフォワードスキャンします
• メリット:
– 前方索引スキャンは後方索引スキャンより高速
– ASC / DESCソートキーでORDER BYにてfilesortの代わりにインデックスを使用可
For B+tree indexes
30

View Slide

オプティマイザヒントの拡張
• SET_VARヒント
– SQL単位でシステム変数を変更できるヒント
– セッション単位で変更可能なシステム変数をSQL単位で変更可能に
（max_allowed_packetなど一部のセッション変数は変更不可）
SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);
SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=ON') */ name,region FROM
country WHERE region='Eastern Asia';
使用例
31

View Slide

MySQL 8.0: リソースグループの例
0
20,000
40,000
60,000
80,000
100,000
120,000
140,000
160,000
No Resource Group With Resource Group
Queries per Second
Select
Update
System Configuration :
Oracle Linux 7,
Intel(R) Xeon(R) CPU E7-4860 2.27GHz
40 cores-HT
(40 Cores Shared) (40 Cores for Select)
(10 Cores for Update RG)
32

View Slide

実行計画の精度向上
• コストモデルの改善
– データとインデックスがメモリ上にあるかストレージ上にあるかを考慮して
コストを計算
• ヒストグラム
– データの分布に偏りがある場合でも精度の高い実行計画を作成
33

View Slide

アジェンダ
EXPLAINの読み方
34
1
2
3

View Slide

実行計画（Query Execution Plan）とは？
• SQL処理する時の内部的処理手順
– SQLは内部的な処理手順を定めていないため、内部的な処理手順はRDBMSが決め
ている
– 内部的な処理手順
• インデックススキャン、テーブルスキャン、JOIN順番、サブクエリーの処理方法、など
• 同じSQLであっても、実行計画が違えばパフォーマンスが大きく変わること
がある
• 実行計画はオプティマイザが作成する
• 実行計画はEXPLAINで確認可能
35

View Slide

オプティマイザ（Optimizer）とは？
• SQLの実行計画を作成する役割を持つ
• MySQLでは、コストベースのオプティマイザを採用しているため、コストに
基づいて実行計画を作成する
– コストに基づいて、最適な（最もコストが低い）実行計画を作成する
（Optimize：最適化する）
• オプティマイザの判断が必ずしも最適だとは限らない
• オプティマイザがより良い実行計画を作成できるように、SQLチューニング
を行う
36

View Slide

他のコネクションで実行中のSQLの実行計画
• SQL文の文字列をコピーしてEXPLAINした場合と実際の実行時の挙動が
異なる可能性がある
– データそのものや統計情報が異なる場合など
• 実行中のSQLの実行計画を取得
• EXPLAIN FOR CONNECTION connection_id;
• connection_idはSHOW PROCESS LISTで確認
38
https://dev.mysql.com/doc/refman/8.0/en/explain-for-connection.html

View Slide

EXPLAINの各項目
39
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
id クエリーのID（テーブルのIDではないので注意）
select_type クエリーの種類
table 対象のテーブル
partitions 対象のパーティション（パーティションテーブルでない場合はNULLが出力される）
type レコードアクセスタイプ（どのようにテーブルにアクセスされるかを示す）
possible_keys 利用可能なインデックス
key 選択されたインデックス
key_len 選択されたインデックスの長さ
ref インデックスと比較される列
rows 行数の概算見積もり
filtered 条件によってフィルタリングされる行の割合
Extra 追加情報

View Slide

id
40
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
• SELECT 識別子
– クエリー内の SELECT の連番
– 同じ番号は、一回の処理に含まれているという意味
– 必ずしも番号順に処理されるわけではない
• SUBQUERYが含まれている場合は、そちらが先に処理される

View Slide

select type
41
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
• SELECT の種類で、次の表のいずれかになる
SIMPLE 単純なSELECT（UNIONやサブクエリーを使用しない）
PRIMARY 最も外側のSELECT
UNION UNION内の2つめ以降のSELECTステートメント
DEPENDENT UNION UNION内の2つめ以降のSELECTステートメントで、外側のクエリーに依存
UNION RESULT UNIONの結果
SUBQUERY サブクエリー内の最初のSELECT
DEPENDENT SUBQUERY サブクエリー内の最初のSELECTで、外側のクエリーに依存
DERIVED 派生テーブル（FROM句内のサブクエリー）
DEPENDENT DERIVED 他のテーブルに依存した派生テーブル
MATERIALIZED 実体化されたサブクエリー
UNCACHEABLE SUBQUERY 結果をキャッシュできず、外側のクエリーの行ごとに再評価される必要があるサブクエリー
UNCACHEABLE UNION キャッシュ不可能なサブクエリー（UNCACHEABLE SUBQUERY）に属するUNION内の2つめ以降のSELECT

View Slide

table
42
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
• Table
– 参照しているテーブルの名前

View Slide

partitions
43
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
• Partitions
– パーティションテーブルにアクセスしている場合、アクセス対象のパーティションが
表示される
– この値がNULLの場合、テーブルはパーティション化されていないと判断できる

View Slide

type
44
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
• テーブルへのアクセス方法
– 次ページにて適切なアクセスタイプから不適切なアクセスタイプの順で記載

View Slide

typeの値意味
system 1行しかないテーブル（systemテーブル） ※constの特殊な例
const PRIMARY/UNIQUEインデックスによる等価検索（一意検索）
eq_ref PRIMARY/UNIQUEインデックスによるJOIN
ref ユニークでないインデクスによる等価検索、JOIN
fulltext 全文検索インデックスを使用した全文検索
ref_or_null ユニークでないインデックスによる等価検索とIS NULLのOR
index_merge 複数のインデックスをマージ
unique_subquery サブクエリー内で、PRIMARY/UNIQUEインデックスで等価検索（一意検索）
index_subquery サブクエリー内で、ユニークでないインデクスによる等価検索、
range 範囲検索
index インデックスのフルスキャン
ALL フルテーブルスキャン
望ましい
望ましくない
45

View Slide

• const（次ページ参照）の特殊なケース
• システムテーブルへのアクセス
Type: system
46
typeの値
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

View Slide

• PRIMARY KEYまたはUNIQUE KEYによる等
価比較（イコールでの比較）が行われる
• 結果は必ず1行になる
• オプティマイザは検索結果を「const(定数)」
と見なすことから名づけられた
Type: const
47
typeの値
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

View Slide

• JOINにおいて、PRIMARY KEYまたはUNIQUE
KEYが利用される
• constと似ているが、内部表へのアクセスに
使われるという点が異なる
• JOIN実行時に、内部表に対して最適な
アクセスが出来ていると判断できる
– JOIN対象の表から主キー/ユニークキーにより
結合対象行が1件だけ取り出せる
Type: eq_ref
48
typeの値
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

View Slide

• ユニークでないインデックスを使って
等価比較（イコールでの比較）が行われる
• SIMPLE（単純なSELECT）の場合でもJOINの
場合でも出現する
Type: ref
49
typeの値
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

View Slide

• 全文検索インデックスを使用する
Type: fulltext
50
typeの値
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

View Slide

• ref と似ているが、NULL 値を含む行の追加
検索を実行する
• 以下のようなSQLの場合に出力される
Type: ref_or_null
51
typeの値
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

View Slide

• 2種類のインデックスをバラバラに使用して、
フェッチした各行をマージする処理が行われ
る
• KeyとKey_lenにはそれぞれ以下の値が入る
– Key：使用されるインデックスのりスト
– Key_len：使用されるインデックスの中で
最長のもの
Type: index_merge
52
typeの値
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

View Slide

• DEPENDENT SUBQUERY（最も遅いSELECT
タイプ）で、PRIMARY KEYもしくはUNIQUE KEY
キーによって評価が行われる
• インデックスでサブクエリーが完結するため、
サブクエリーの中では高速
Type: unique_subquery
53
typeの値
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

View Slide

• unique_subquery と似ている
• PRIMARY KEY/UNIQUEキーでない
インデックスを使って評価する
Type: index_subquery
54
typeの値
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

View Slide

• インデックスを使って、特定の範囲の
レコードを取得する
• BETWEENや不等号(>, <=, =)、INを使うと
出現
Type: range
55
typeの値
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

View Slide

• インデックスフルスキャン
• 以下の2つのケースで出現
– インデックスをフルスキャンすることでテーブル
フルスキャンを回避できる場合
（Extraに Using index が表示される）
– テーブルフルスキャン実行時に、ソート処理を回避
するためにインデックスを使用できる場合
（Extraに Using index が表示されない）
• 効率よさそうな名前だが、実際は重い
処理の場合もある
– 可能な場合は、インデックスで絞り込めるよう
テーブル構造かSQLを変更すべき
Type: index
56
typeの値
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

View Slide

• テーブルフルスキャン
（インデックスを一切用いない処理）
• JOINの内部表で出てきたら完全にアンチ
パターン（最悪）
– MySQL WorkbenchのVisual Explainでも赤色で
表示される
• JOIN以外でも、全行を処理しないといけない
ような処理以外では避けるべき
– データ量が非常に少ないテーブルを除く
Type: ALL
57
typeの値
system
const
eq_ref
ref
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

View Slide

possible_keys
58
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
• テーブルへのアクセスに利用可能なキー（インデックス）の候補
• ヒント句の「FORCE INDEX」を使っても、ここに挙がっていないキーは無視
• テーブルにあるキーを確認するには、「SHOW INDEX FROM tbl_name」を
使用
• 5.5ではSHOW INDEX文により自動的に統計情報再収集が行われるので
注意

View Slide

key
59
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
• 実際に使用することを決定したキーを示す
• key は possible_keys 値に存在しないキーを指定している可能性あり
– possible_keys のどれも行のルックアップに適していない場合、テーブルフルスキャン
よりもインデックスフルスキャンの方が効率的と判断した場合に発生することがある

View Slide

key_len
60
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
• 使用することを決定したキーの長さ（サイズ）
• key カラムがNULL の場合、この長さも NULL

View Slide

ref
61
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
• 検索条件でkeyと比較されるカラムまたは定数

View Slide

rows
62
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
• クエリーを実行するためにフェッチされる行数の推測値
– 実際の値はやってみないとわからない
– ただしサブクエリー部分のrowsは必ず正確な値
（Explain時に実際にサブクエリーが実行されるため）

View Slide

filtered
63
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
• フェッチされる行数の中で実際に検索に使われそうな行数
– 100%と出る事が多い
– あまり参考にならない

View Slide

Extra
64
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
• クエリーを解決する方法に関連する追加情報
Using filesort
Using index
Using index condition
Using index for group-by
Using join buffer (Block Nested Loop)
Using join buffer (Batched Key Access)
Using MRR
Using temporary
Using where

View Slide

• ソートに必要な領域がメモリ上の領域
（sort_buffer_size）を超えた場合に出現
• 行数が少ない場合を除き、非常に遅い
• JOIN時、JOIN実行後にファイルソートする
場合は特に遅くなる
– EXPLAINの最初の行にUsing temporary; Using
filesortが表示される
Extra: Using filesort
65
Using filesort
Using index
Using MRR
Using temporary
Using where

View Slide

• インデックスにアクセスするだけでクエリー
を完結できる場合に出現
• 行データにアクセスする必要が無いため、
非常に高速
Extra: Using index
66
Using filesort
Using index
Using MRR
Using temporary
Using where

View Slide

• Using index condition
– インデックスコンディションプッシュダウンを行う
（ストレージエンジン側でWHERE句による絞込みを
行う）
• Using index for group-by
– GROUP BY または DISTINCT を使ったクエリーを、
インデックスだけで処理できる
Extra: Using index condition / Using index for group-by
67
Using filesort
Using index
Using MRR
Using temporary
Using where

View Slide

• JOINバッファを使用して結合処理を行う
• 結合アルゴリズムによって、Block Nested
Loop / Batched Key Access が出力される
Extra: Using join buffer
68
Using filesort
Using index
Using MRR
Using temporary
Using where

View Slide

• MRR(Multi-Range Read)最適化を行う
– テーブルが大きくストレージエンジンのキャッシュに
格納されていない場合、セカンダリインデックスでの
範囲スキャンを使用して行を読み取る時に、ベース
テーブルへのランダムディスクアクセスが多発する
場合がある
– Disk-Sweep Multi-Range Read (MRR) 最適化を使用す
ると、MySQLは最初にインデックスだけをスキャンし、
該当する行のキーを収集することによって、範囲ス
キャンのランダムディスクアクセスの回数を軽減する
– [MRRの動作]
インデックスだけをスキャン
=> ソート
=> 主キーの順番にベーステーブルから行を取得
Extra: Using MRR
69
Using filesort
Using index
Using MRR
Using temporary
Using where

View Slide

• クエリーの実行に内部的なテンポラリ
テーブルを使う場合に出現
• 遅くなる傾向あり
• 以下のような処理で出現
– JOIN後にソート
– GROUP BYとORDER BYの併用
– UNION
– 集合関数（SUM等）
、、、など
Extra: Using temporary
70
Using filesort
Using index
Using MRR
Using temporary
Using where

View Slide

• 行をフェッチした後に、更にWHERE条件で
の絞り込みが必要な場合に出現
• WHERE句があって、インデックスが使われ
ていない場合にこれが出てくる
• インデックスが使われても、インデックスで
の検索後に更にもう一段階WHEREによる
絞込みが必要な場合にも出てくる
Extra: Using where
71
Using filesort
Using index
Using MRR
Using temporary
Using where

View Slide

MySQL EXPLAIN結果まとめ
• select_typeで気をつけるべき事項
– DEPENDENT UNIONおよび、DEPENDENT SUBQUERYは可能な限り避ける
• サブクエリーの結果1行1行に対して外側の表のマッチング処理が行われるため、非常に工数が大きい
• typeで気をつけるべき事項
– indexおよび、ALLは可能な限り避ける
• 特に、JOIN実行時、内部表に対してフルスキャンが発生するのは最悪
• テーブルのサイズが小さい場合は問題無い
– indexはたいていrefか、eq_refにできる
• Extraで気をつけるべき事項
– Using Indexが理想的
– Using where, Using filesort, Using temporaryは可能な限り避ける
• SELECT句の選択リストに＊は使わない
72

View Slide

EXPLAINの出力フォーマット
• デフォルトは表形式
• JSON形式の出力も可能
– EXPLAIN FORMAT=JSON …
+----+-------------+---------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+
+----+-------------+---------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+
| 1 | PRIMARY | Country | NULL | const | PRIMARY | PRIMARY | 3 | const | 1 | 100 | Using index |
| 1 | PRIMARY | a | NULL | ref | CountryCode | CountryCode | 3 | const | 248 | 33.33 | Using where |
| 2 | SUBQUERY | Country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 10 | Using where |
| 2 | SUBQUERY | City | NULL | ref | CountryCode | CountryCode | 3 | world.Country.Code | 18 | 100 | NULL |
+----+-------------+---------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+
4 rows in set (0.0018 sec)
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "43.55"
},
"nested_loop": [
{
"table": {
"table_name": "Country",
"access_type": "const",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"Code”
],
"key_length": "3",
…
73

View Slide

Visual EXPLAIN
• MySQL WorkbenchからVisual EXPLAINを取得可能
• オブジェクトへのアクセスタイプを一目で確認可能
(typeの値を色で判別可能)
• JOINの順番も一目で分かる
• オプティマイザが見積ったコストも
確認出来る
※チュートリアル：7.5 Tutorial: Using Visual Explain to improve query performance
http://dev.mysql.com/doc/workbench/en/wb-tutorial-visual-explain-dbt3.html
74

View Slide

Visual EXPLAIN（typeの値による色の違い）
typeの値色意味
system 青色 1行しかないテーブル(systemテーブル) ※constの特殊な例
const 青色 PRIMARY/UNIQUEインデックスによる等価検索（一意検索）
eq_ref 緑色 PRIMARY/UNIQUEインデックスによるJOIN
ref 緑色ユニークでないインデクスによる等価検索、JOIN
fulltext 黄色全文検索インデックスを使用した全文検索
ref_or_null 緑色ユニークでないインデックスによる等価検索とIS NULLのOR
index_merge 緑色複数のインデックスをマージ
unique_subquery 橙色サブクエリー内で、PRIMARY/UNIQUEインデックスで等価検索（一意検索）
index_subquery 橙色サブクエリー内で、ユニークでないインデクスによる等価検索、
range 橙色範囲検索
index 赤色インデックスのフルスキャン
ALL 赤色フルテーブルスキャン
望ましい
75

View Slide

Query Statistics
• MySQL WorkbenchのQuery Statisticsから、SQLチューニング時に
確認すべき基本的な情報をまとめて確認できる
76

View Slide

TREE形式のEXPLAINも開発中
• MySQL 8.0.16のリリースノートより抜粋
– 「Added an experimental tree format for EXPLAIN output, which prints the generated
iterator tree, and is intended to help users understand how execution was actually set
up. EXPLAINFORMAT=TREE is currently unsupported in production and both its syntax
and output are subject to change in subsequent versions of MySQL.」
• 参考情報
– TREE explain format in MySQL 8.0.16
https://gdsotirov.blogspot.com/2019/06/tree-explain-format-in-mysql-8016.html
※現時点では本番環境での使用はサポートされていません。
また、今後のバージョンでフォーマットが変更される可能性もあります。
77

View Slide


View Slide

MySQLパフォーマンスチューニングTIPS

MySQLパフォーマンスチューニングTIPS

YoshiakiYamasaki

More Decks by YoshiakiYamasaki

Other Decks in Technology

Featured

Transcript