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

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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. アジェンダ MySQL 5.7でのパフォーマンス改善点 MySQL 8.0でのパフォーマンス改善点 EXPLAINの読み方 3 1 2 3

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. アジェンダ MySQL 5.7でのパフォーマンス改善点 MySQL 8.0でのパフォーマンス改善点 EXPLAINの読み方 4 1 2 3

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MySQL 5.7でのパフォーマンス改善点 • オプティマイザーの改善 • コンディションフィルターの適用 • MTS(マルチスレッドスレーブ) • オプティマイザヒントの拡張 5

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. オプティマイザーの改善 • オプティマイザーが進化し、OLTP系以外の処理に関しても実行速度が 改善している 6

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. オプティマイザーの改善 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

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

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

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

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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MTS（マルチスレッドスレーブ）を効率化するための設定2 • MySQL 5.7.22以降、MySQL 8.0ではWRITESET方式による並列化も可能 – グループレプリケーションで使用している競合検出の仕組みを応用 – マスターでグループコミットが行われなかったトランザクションに対しても、 同じ行を更新していないトランザクションであればスレーブ側で並列化可能 ⇒マスター側でシングルスレッドで実行された処理も、スレーブ側で並列化可能 – マスター側でのバイナリログの書込みに対して、追加のオーバーヘッドがかかる ことに注意 13

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MTS（マルチスレッドスレーブ）を効率化するための設定2 • 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 備考： MTS（マルチスレッドスレーブ） の進化の歴史 16 「MTS（マルチスレッドス レーブ）を効率化するた めの設定1」の話 「MTS（マルチスレッドス レーブ）を効率化するた めの設定2」の話

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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. オプティマイザヒント（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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. オプティマイザヒント（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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. アジェンダ MySQL 5.7でのパフォーマンス改善点 MySQL 8.0でのパフォーマンス改善点 EXPLAINの読み方 21 1 2 3

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MySQL 8.0でのパフォーマンス改善点 • インビジブルインデックス（不可視索引） • CTE（WITH句） と 再帰的CTE • Window 関数 • SKIP LOCKED と NOWAIT オプション • 降順索引（Descending Indexes） • オプティマイザーヒントの拡張 • リソースグループ • 実行計画の精度向上（コストモデルの改善、ヒストグラム） 22

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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MySQL 8.0: 新しいSQL構文 • もっとも要求の多かった2つの機能 – CTE（Common Table Expressions） ※WITH句 – Window 関数 24 Feature Request from Developers

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Window関数の例 • RANK関数 – ランキングを求めることが出来る • LAG関数 – 1行前の値を参照できる • SUM関数 – ウィンドウごとの合計値を求めることが出来る 26

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 降順索引 (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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. オプティマイザヒントの拡張 • 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. アジェンダ MySQL 5.7でのパフォーマンス改善点 MySQL 8.0でのパフォーマンス改善点 EXPLAINの読み方 34 1 2 3

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

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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 実行計画の出力 37 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) explain <実行計画を見たいSQL文>;

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 他のコネクションで実行中の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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. EXPLAINの各項目 39 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) id クエリーのID（テーブルのIDではないので注意） select_type クエリーの種類 table 対象のテーブル partitions 対象のパーティション（パーティションテーブルでない場合はNULLが出力される） type レコードアクセスタイプ（どのようにテーブルにアクセスされるかを示す） possible_keys 利用可能なインデックス key 選択されたインデックス key_len 選択されたインデックスの長さ ref インデックスと比較される列 rows 行数の概算見積もり filtered 条件によってフィルタリングされる行の割合 Extra 追加情報

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. id 40 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) • SELECT 識別子 – クエリー内の SELECT の連番 – 同じ番号は、一回の処理に含まれているという意味 – 必ずしも番号順に処理されるわけではない • SUBQUERYが含まれている場合は、そちらが先に処理される

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. select type 41 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) • 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. table 42 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) • Table – 参照しているテーブルの名前

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. partitions 43 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) • Partitions – パーティションテーブルにアクセスしている場合、アクセス対象のパーティションが 表示される – この値がNULLの場合、テーブルはパーティション化されていないと判断できる

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. type 44 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) • テーブルへのアクセス方法 – 次ページにて適切なアクセスタイプから不適切なアクセスタイプの順で記載

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • const（次ページ参照）の特殊なケース • システムテーブルへのアクセス Type: system 46 typeの値 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • ユニークでないインデックスを使って 等価比較（イコールでの比較）が行われる • 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • 全文検索インデックスを使用する Type: fulltext 50 typeの値 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • 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

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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • インデックスフルスキャン • 以下の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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • テーブルフルスキャン （インデックスを一切用いない処理） • 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. possible_keys 58 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) • テーブルへのアクセスに利用可能なキー（インデックス）の候補 • ヒント句の「FORCE INDEX」を使っても、ここに挙がっていないキーは無視 • テーブルにあるキーを確認するには、「SHOW INDEX FROM tbl_name」を 使用 • 5.5ではSHOW INDEX文により自動的に統計情報再収集が行われるので 注意

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. key 59 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) • 実際に使用することを決定したキーを示す • key は possible_keys 値に存在しないキーを指定している可能性あり – possible_keys のどれも行のルックアップに適していない場合、テーブルフルスキャン よりもインデックスフルスキャンの方が効率的と判断した場合に発生することがある

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. key_len 60 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) • 使用することを決定したキーの長さ（サイズ） • key カラムがNULL の場合、この長さも NULL

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. ref 61 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) • 検索条件でkeyと比較されるカラムまたは定数

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. rows 62 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) • クエリーを実行するためにフェッチされる行数の推測値 – 実際の値はやってみないとわからない – ただしサブクエリー部分のrowsは必ず正確な値 （Explain時に実際にサブクエリーが実行されるため）

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. filtered 63 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) • フェッチされる行数の中で実際に検索に使われそうな行数 – 100%と出る事が多い – あまり参考にならない

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Extra 64 mysql> explain select * from nodes where id = 31236601; +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | nodes | NULL | const | i_nodeids | i_nodeids | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) • クエリーを解決する方法に関連する追加情報 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • ソートに必要な領域がメモリ上の領域 （sort_buffer_size）を超えた場合に出現 • 行数が少ない場合を除き、非常に遅い • JOIN時、JOIN実行後にファイルソートする 場合は特に遅くなる – EXPLAINの最初の行にUsing temporary; Using filesortが表示される Extra: Using filesort 65 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • インデックスにアクセスするだけでクエリー を完結できる場合に出現 • 行データにアクセスする必要が無いため、 非常に高速 Extra: Using index 66 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • 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 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • JOINバッファを使用して結合処理を行う • 結合アルゴリズムによって、Block Nested Loop / Batched Key Access が出力される Extra: Using join buffer 68 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

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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • クエリーの実行に内部的なテンポラリ テーブルを使う場合に出現 • 遅くなる傾向あり • 以下のような処理で出現 – JOIN後にソート – GROUP BYとORDER BYの併用 – UNION – 集合関数（SUM等） 、、、など Extra: Using temporary 70 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. • 行をフェッチした後に、更にWHERE条件で の絞り込みが必要な場合に出現 • WHERE句があって、インデックスが使われ ていない場合にこれが出てくる • インデックスが使われても、インデックスで の検索後に更にもう一段階WHEREによる 絞込みが必要な場合にも出てくる Extra: Using where 71 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. EXPLAINの出力フォーマット • デフォルトは表形式 • JSON形式の出力も可能 – EXPLAIN FORMAT=JSON … +----+-------------+---------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+-------------+---------+--------------------+------+----------+-------------+ | 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Query Statistics • MySQL WorkbenchのQuery Statisticsから、SQLチューニング時に 確認すべき基本的な情報をまとめて確認できる 76

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. 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

Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

No content