MySQL EXPLAIN 解説

MySQL の EXPLAIN で表示される各項目の解説や、チューニングのポイントなどを社内向け勉強会で解説したものです

Transcript

1. MySQL EXPLAIN 解説 2023-08-25 新卒講義 （株）インフィニットループ 事業基盤マネージャー 波多野信広
2. 内容 • EXPLAIN で出力される実行計画の解説 • ota さん資料 https://www.infiniteloop.co.jp/tech-blog/2011/03/mysql-index-explain/ の EXPLAIN のパートを補完 • EXPLAIN のさらなる詳細は本家リファレンス・マニュアルの「8.8.2 EXPLAIN 出力 フォーマット」にてhttps://dev.mysql.com/doc/refman/8.0/ja/explain-output.html
3. EXPLAIN を読む上で、理解しやすくなるポイント MySQL は SQL を理解し実行する SQL のレイヤーとトランザクショナル Key Value Store (KVS) の InnoDB ストレージエンジンとの２層構造 ざっくり言うと SQL を単純な InnoDB 操作へとコンパイルしたものが実行計画 MySQL InnoDB SELECT * FROM t1 INNER JOIN t2 ON t1.pkey = t2.pkey HANDLER t1 READ pkey HANDLER t2 READ pkey 積集合
4. EXPLAIN を読む上で、理解しやすくなるポイント MySQL (InnoDB) のテーブル構造は、全てのインデックスがプライマリキーへの参照になっている「クラス ター・インデックス」「索引編成表」 pkey ・・・ ix1 ・・・ ix2 ix1 pkey ix2 pkey インデックスでは pkey が value プライマリキー（テーブル）では pkey 以外の全てが１個の value インデックスマージ AND 積集合 = 共通 pkey 抽出 OR 和集合 = 両方の pkey 抽出 ix1ix2 pkey 複合インデックス インデックスにあるか？ など pkey 不要なアクセス = カバリングインデックス
5. EXPLAIN を読む上で、理解しやすくなるポイント JOIN には単純なネステッド・ループを使います INNER JOIN で２つの列の同値を探す場合、人間の感覚では２つ並べて交互に比較しますが・・・ 1 2 3 4 1 2 4 8 ２つのクエリを同時に読み進め ながら比較 同じ値 あり と なし 残念ながらこうはやってない
6. EXPLAIN を読む上で、理解しやすくなるポイント 左の１行ごとに、右のテーブルを毎回スキャンして愚直に INNER JOIN を解決 1 2 3 4 1 2 4 8 多段の for ループ で Join Nested Loop Join (NLJ) これ JOIN ですが、何かに似てませ んか？ 左の値に関して右でクエリする、 そう・サブクエリです！ JOIN = サブクエリ なんだ！
7. EXPLAIN を読む上で、理解しやすくなるポイント SELECT文の評価順序 https://qiita.com/suzukito/items/edcd00e680186f2930a8 1. FROM 2. ON 3. JOIN 4. WHERE 5. GROUP BY 6. HAVING 7. SELECT 8. DISTINCT 9. ORDER BY 10. LIMIT 一つのサブクエリ 内での評価順 どの RDB でもだいたい同じ • アクセスするテーブルを用意して • WHERE でフィルタしながら値を得て • JOIN する • GROUP BY の集合や集約の計算 • 最後にレポート列の整形やソート
8. ota さん 資料拝借
10. EXPLAIN の id, select_type, table 補足 • id : サブクエリ select 毎に振られる番号（と思って ok） ◦ JOIN は同じ id で実行 ◦ 無関係なクエリだと id が変わる • select_type: ◦ id が変わる select_type ▪ UNION UNION ALL はクエリとして完全独立 ▪ UNION RESULT UNION の重複削除して結果を結合するフェーズ ▪ SUBQUERY SELECT の列選択にサブクエリが埋め込まれている ▪ DERIVED サブクエリ結果が派生したテーブルとして利用されている ▪ DEPENDENT 〜 上のレベルのクエリのデータに依存して結果が変わる ▪ UNCACHEABLE 〜 サブクエリの繰り返し時にキャッシュ利用出来てない ◦ JOIN で同じ id が使われる select_type ▪ SIMPLE 関係したサブクエリを含まない ▪ PRIMARY サブクエリを含む場合のトップレベルクエリ
11. UNION, UNION RESULT mysql> EXPLAIN SELECT * FROM T1 UNION ALL SELECT * FROM T2; +----+-------------+-------+- | id | select_type | table | +----+-------------+-------+- | 1 | PRIMARY | T1 | | 2 | UNION | T2 | +----+-------------+-------+- mysql> EXPLAIN SELECT * FROM T1 UNION SELECT * FROM T2; +----+--------------+------------+- | id | select_type | table | +----+--------------+------------+- | 1 | PRIMARY | T1 | | 2 | UNION | T2 | | 3 | UNION RESULT | <union1,2> | +----+--------------+------------+- T1 の SELECT は後続の SELECT の値を結合するというこ とで SIMPLE ではなく PRIMARY 重複削除の UNION RESULT 物理テーブルではない生成したテーブル <”union” “id”> で表記
12. SUBQUERY mysql> EXPLAIN SELECT *, (SELECT id FROM T2 WHERE id = 1) FROM T1; +----+-------------+-------+- | id | select_type | table | +----+-------------+-------+- | 1 | PRIMARY | T1 | | 2 | SUBQUERY | T2 | +----+-------------+-------+- ここがテーブル T2 にアクセスす る SUBQUERY
13. DERIVED mysql> EXPLAIN SELECT * FROM (SELECT id FROM T1 LIMIT 2) D1 WHERE D1.id > 5; +----+-------------+------------+- | id | select_type | table | +----+-------------+------------+- | 1 | PRIMARY | <derived2> | | 2 | DERIVED | T1 | +----+-------------+------------+- id 1: 後続のクエリが生成する派 生テーブル <derived”id”> への クエリ id 2: 派生テーブルを生成するク エリ
14. DEPENDENT SUBQUERY mysql> EXPLAIN SELECT *, (SELECT id FROM T1 WHERE T1.id = D2.id) D1 FROM (SELECT id FROM T2 LIMIT 2) D2; +----+--------------------+------------+- | id | select_type | table | +----+--------------------+------------+- | 1 | PRIMARY | <derived3> | | 3 | DERIVED | T2 | | 2 | DEPENDENT SUBQUERY | T1 | +----+--------------------+------------+- EXPLAIN の実行計画はネステッドループの順番表みたいに なっている • SELECT * のループ ◦ 派生テーブル を生成する T2 への SELECT ▪ T1 への SELECT（ T2 の値に依存）
15. UNCACHEABLE SUBQUERY mysql> EXPLAIN SELECT * ,(SELECT @a FROM T1 LIMIT 1) T FROM T2; +----+----------------------+-------+- | id | select_type | table | +----+----------------------+-------+- | 1 | PRIMARY | T2 | | 2 | UNCACHEABLE SUBQUERY | T1 | +----+----------------------+-------+- サブクエリがキャッシュされないケースとしてユーザー 変数 @a が含まれている場合 ユーザー変数使うなどがんばらないと Uncacheable にはならない、多くはキャッシュさ れるならネストされてたり行数多くても良いので は？ メモリでもコンパクトに、 L1,2,3キャッシュを意識しな いと DB では遅い時代に
16. SIMPLE mysql> EXPLAIN SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.id; +----+-------------+-------+- | id | select_type | table | +----+-------------+-------+- | 1 | SIMPLE | T1 | | 1 | SIMPLE | T2 | +----+-------------+-------+- それぞれのテーブルへのアクセスはサブクエリなど含 まないので SIMPLE JOIN は一つの id で実行されるので同じ id : 1
17. 何故 EXPLAIN で ref という用語が 頻出なのか？ 実行計画 = JOIN = サブクエリの計 画 サブクエリの関心は外のデータをど う参照しているか? になるので reference ワード多用
18. 結合タイプの話の前に、使っているテーブル定義 mysql> desc inventories; +---------+--------+------+-----+ | Field | Type | Null | Key | +---------+--------+------+-----+ | id | bigint | NO | PRI | | user_id | bigint | YES | MUL | | item_id | bigint | YES | MUL | +---------+--------+------+-----+ +----+---------+---------+ | id | user_id | item_id | +----+---------+---------+ | 1 | 594 | 831 | +----+---------+---------+ mysql> desc users; +-------+-------------+- | Field | Type | +-------+-------------+- | id | bigint | | name | varchar(64) | +-------+-------------+- mysql> desc items; +-------------+-------------+- | Field | Type | +-------------+-------------+- | id | bigint | | name | varchar(64) | | description | text | +-------------+-------------+- foreign key 注）業務における最適なテーブル定義については所属チームの先輩方に確認しましょう！
19. type : const mysql> EXPLAIN SELECT * FROM inventories WHERE id = 1; +----+-------------+-------------+------------+-------+ | id | select_type | table | partitions | type | +----+-------------+-------------+------------+-------+ | 1 | SIMPLE | inventories | NULL | const | +----+-------------+-------------+------------+-------+ 定数で抽出 = ユニークなインデックスで等価検索
20. type : eq_ref と ALL mysql> EXPLAIN SELECT U.name, I.name FROM items I INNER JOIN users U ON I.id = U.id; +----+-------------+-------+------------+--------+---------------+---------+---------+- | id | select_type | table | partitions | type | possible_keys | key | key_len | +----+-------------+-------+------------+--------+---------------+---------+---------+- | 1 | SIMPLE | I | NULL | ALL | PRIMARY | NULL | NULL | | 1 | SIMPLE | U | NULL | eq_ref | PRIMARY | PRIMARY | 8 | +----+-------------+-------+------------+--------+---------------+---------+---------+- ユーザーとアイテムでたまたま同じ id 番号のものを探 すという意味ないクエリですが、 eq_ref 一致検索のシ ンプルな例 ユニークなインデックス同士で JOIN する場合に等価一致の検索になるので eq_ref に ALL は全件検索
21. type : ref と ALL mysql> EXPLAIN SELECT I.id, U.name FROM inventories I INNER JOIN users U ON I.user_id = U.id; +----+-------------+-------+------------+------+---------------+----------+- | id | select_type | table | partitions | type | possible_keys | key | +----+-------------+-------+------------+------+---------------+----------+- | 1 | SIMPLE | U | NULL | ALL | PRIMARY | NULL | | 1 | SIMPLE | I | NULL | ref | users_id | users_id | +----+-------------+-------+------------+------+---------------+----------+- inventories の users_id のインデックスはユニークではない 比較参照は複数行で行われる 非ユニークなインデックスでの一致の検索は複数行対象になる ref で
22. type : ref_or_null mysql> EXPLAIN SELECT * FROM inventories I1 WHERE item_id = 1 OR item_id IS NULL; +----+-------------+-------+------------+-------------+---------------+----------+- | id | select_type | table | partitions | type | possible_keys | key | +----+-------------+-------+------------+-------------+---------------+----------+- | 1 | SIMPLE | I1 | NULL | ref_or_null | items_id | items_id | +----+-------------+-------+------------+-------------+---------------+----------+- 非ユニークかつ NULL 許容のインデックスで、実際 に NULL も対象にした検索を行うと このタイプに
23. index フルスキャンの例外とし て、 Extra に “Using Index” がつ いている場合はデータ的にイン デックスの取得で足りていてプラ イマリキー不要な「カバリングイン デックス 」で動作 この場合重くないこともある オプティマイザは行数が 4〜16倍程度で済む ならインデックスの range 検索ではなくプライ マリキーの ALL を選択しがち。実際その方が 速いことも多い。 type 続き
26. ここ重要！ 加えて EXPLAIN のサブクエリ行毎 に、ネストしたループの掛け算の行 数でのアクセスになることも忘れな く！ 多段のループも静的な場合と、外の 値に依存してて毎回クエリ実行され ている場合とがあるので、さらに注 意！！
27. おまけ
28. 何故 NLJ 採用か？を考えることで見えてくる物 • 殆どの JOIN は CROSS JOIN の特殊系としても実装出来る • JOIN = サブクエリ でもある • JOIN の自然な実装として NLJ で作っておけば SQL の仕事だいたいカバー 100 200 200 300 MySQL 5.7 で scores テーブル cross join の自己結合で <= の行を集計 していくとランキングになる 100 200 200 300 SELECT s1.id, s1.score, COUNT(*) AS ranking FROM scores s1 CROSS JOIN scores s2 ON s1.score <= s2.score GROUP BY s1.id; （パフォーマンスが悪い例なので仕事では使わないように！仕事のクエリは所 属チームの先輩に聞きましょうｗ） +----+-------+---------+ | id | score | ranking | +----+-------+---------+ | 1 | 100 | 4 | | 3 | 200 | 3 | | 2 | 200 | 3 | | 4 | 300 | 1 | +----+-------+---------+
29. DB で何故 NLJ 採用か？を考えることで見えてくる物 • MySQL 出来たころ：HDD はシーケンシャルならまぁまぁ速く、メモリなら速い • 近年：大容量メモリに載っただけの DBでは戦えない。効率的でコンパクトなデータ設計必要 ◦ 例：廃止された MySQL のクエリキャッシュ。その他あんまり速くないインメモリ DB • MySQL の JOIN も進化 ◦ 5.6〜 Block Nested Loop：Index で JOIN 出来なくてもブロック化で対応 ◦ 8.0.20〜 Hash Join ：コンパクトなハッシュ値テーブルを生成して JOIN の値比較 例：前のページのクエリの EXPLAIN SELECT s1.id, s1.score, COUNT(*) AS ranking FROM scores s1 CROSS JOIN scores s2 ON s1.score <= s2.score GROUP BY s1.id; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 4 | 100.00 | Using temporary | | 1 | SIMPLE | s2 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 33.33 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+