Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

2019年7月10日に開催された「WEBエンジニア MeetUp@札幌 #6 MySQL Special」での発表資料です。
発表時の資料に少し説明を加筆・修正してから公開しています。

※追加で以下の更新をしました。(2019年7月19日)
- MTSを効率化するための設定に関して、WRITESET方式による並列化の説明を追記(13~16ページを追記)

YoshiakiYamasaki

July 10, 2019
Tweet

More Decks by YoshiakiYamasaki

Other Decks in Technology

Transcript

  1. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  7. 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

    View full-size slide

  8. 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  11. 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

    View full-size slide

  12. 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

    View full-size slide

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

    View full-size slide

  14. 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

    View full-size slide

  15. 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  18. 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

    View full-size slide

  19. 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

    View full-size slide

  20. 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  23. 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

    View full-size slide

  24. 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

    View full-size slide

  25. 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

    View full-size slide

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

    View full-size slide

  27. 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

    View full-size slide

  28. 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

    View full-size slide

  29. 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

    View full-size slide

  30. 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

    View full-size slide

  31. 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

    View full-size slide

  32. 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  37. 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文>;

    View full-size slide

  38. 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

    View full-size slide

  39. 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 追加情報

    View full-size slide

  40. 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が含まれている場合は、そちらが先に処理される

    View full-size slide

  41. 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

    View full-size slide

  42. 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
    – 参照しているテーブルの名前

    View full-size slide

  43. 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の場合、テーブルはパーティション化されていないと判断できる

    View full-size slide

  44. 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)
    • テーブルへのアクセス方法
    – 次ページにて適切なアクセスタイプから不適切なアクセスタイプの順で記載

    View full-size slide

  45. 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

    View full-size slide

  46. 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

    View full-size slide

  47. 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

    View full-size slide

  48. 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

    View full-size slide

  49. 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

    View full-size slide

  50. 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

    View full-size slide

  51. 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

    View full-size slide

  52. 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

    View full-size slide

  53. 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

    View full-size slide

  54. 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

    View full-size slide

  55. 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

    View full-size slide

  56. 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

    View full-size slide

  57. 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

    View full-size slide

  58. 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文により自動的に統計情報再収集が行われるので
    注意

    View full-size slide

  59. 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 のどれも行のルックアップに適していない場合、テーブルフルスキャン
    よりもインデックスフルスキャンの方が効率的と判断した場合に発生することがある

    View full-size slide

  60. 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

    View full-size slide

  61. 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と比較されるカラムまたは定数

    View full-size slide

  62. 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時に実際にサブクエリーが実行されるため)

    View full-size slide

  63. 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%と出る事が多い
    – あまり参考にならない

    View full-size slide

  64. 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

    View full-size slide

  65. 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

    View full-size slide

  66. 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

    View full-size slide

  67. 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

    View full-size slide

  68. 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

    View full-size slide

  69. 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

    View full-size slide

  70. 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

    View full-size slide

  71. 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

    View full-size slide

  72. 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

    View full-size slide

  73. 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

    View full-size slide

  74. 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

    View full-size slide

  75. 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

    View full-size slide

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

    View full-size slide

  77. 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

    View full-size slide

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

    View full-size slide