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

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

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

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

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

オプティマイザーの改善 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

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

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

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

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

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

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

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

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

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

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

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

オプティマイザヒント（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

オプティマイザヒント（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

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

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

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

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

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

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

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

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

降順索引 (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

オプティマイザヒントの拡張 • 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

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

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

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

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

他のコネクションで実行中の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

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

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

• 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

• 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

• ユニークでないインデックスを使って等価比較（イコールでの比較）が行われる • 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

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

• 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

• 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

• 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

• 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

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

• インデックスフルスキャン • 以下の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

• テーブルフルスキャン（インデックスを一切用いない処理） • 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

• ソートに必要な領域がメモリ上の領域（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

• インデックスにアクセスするだけでクエリーを完結できる場合に出現 • 行データにアクセスする必要が無いため、非常に高速 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

• 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

• 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

• 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

• クエリーの実行に内部的なテンポラリテーブルを使う場合に出現 • 遅くなる傾向あり • 以下のような処理で出現 – 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

• 行をフェッチした後に、更に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

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

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

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

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

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

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

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

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

More Decks by YoshiakiYamasaki

Other Decks in Technology

Featured

Transcript