Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
MySQL EXPLAIN 解説
Search
Infiniteloop
August 29, 2023
5
4.2k
MySQL EXPLAIN 解説
MySQL の EXPLAIN で表示される各項目の解説や、チューニングのポイントなどを社内向け勉強会で解説したものです
Infiniteloop
August 29, 2023
Tweet
Share
More Decks by Infiniteloop
See All by Infiniteloop
俺の PHP プロファイラの話 PHP スクリプトで PHP 処理系のメモリをのぞき込む
infiniteloop_inc
0
260
心理的安全性を学び直し、 「いい組織とは何か?」を考えてみる
infiniteloop_inc
0
320
ゼロからつくる 2D物理シミュレーション ~物理現象をコードに落とし込む方法~
infiniteloop_inc
0
380
詫び石の裏側
infiniteloop_inc
0
360
[新卒向け研修資料] テスト文字列に「うんこ」と入れるな(2024年版)
infiniteloop_inc
5
24k
リファクタリングで実装が○○分短縮した話
infiniteloop_inc
0
130
ADRという考えを取り入れてみて
infiniteloop_inc
0
120
500万行のPHPプロジェクトにおけるログ出力の歩み
infiniteloop_inc
0
100
I ❤ Virtual Machines 仮想環境をより便利に使うツールたち
infiniteloop_inc
0
83
Featured
See All Featured
The Psychology of Web Performance [Beyond Tellerrand 2023]
tammyeverts
41
2.1k
The Art of Programming - Codeland 2020
erikaheidi
51
13k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
328
21k
Automating Front-end Workflow
addyosmani
1365
200k
Building Flexible Design Systems
yeseniaperezcruz
327
38k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
107
49k
It's Worth the Effort
3n
183
27k
Fashionably flexible responsive web design (full day workshop)
malarkey
404
65k
Optimising Largest Contentful Paint
csswizardry
33
2.9k
A designer walks into a library…
pauljervisheath
202
24k
Imperfection Machines: The Place of Print at Facebook
scottboms
264
13k
Mobile First: as difficult as doing things right
swwweet
222
8.9k
Transcript
MySQL EXPLAIN 解説 2023-08-25 新卒講義 (株)インフィニットループ 事業基盤マネージャー 波多野信広
内容 • 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
EXPLAIN を読む上で、理解しやすくなるポイント MySQL は SQL を理解し実行する SQL のレイヤーとトランザクショナル Key Value
Store (KVS) の InnoDB ストレージエンジンとの2層構造 ざっくり言うと SQL を単純な InnoDB 操作へとコンパイルしたものが実行計画 MySQL InnoDB SELECT * FROM t1 INNER JOIN t2 ON t1.pkey = t2.pkey HANDLER t1 READ pkey HANDLER t2 READ pkey 積集合
EXPLAIN を読む上で、理解しやすくなるポイント MySQL (InnoDB) のテーブル構造は、全てのインデックスがプライマリキーへの参照になっている「クラス ター・インデックス」「索引編成表」 pkey ・・・ ix1 ・・・
ix2 ix1 pkey ix2 pkey インデックスでは pkey が value プライマリキー(テーブル)では pkey 以外の全てが1個の value インデックスマージ AND 積集合 = 共通 pkey 抽出 OR 和集合 = 両方の pkey 抽出 ix1ix2 pkey 複合インデックス インデックスにあるか? など pkey 不要なアクセス = カバリングインデックス
EXPLAIN を読む上で、理解しやすくなるポイント JOIN には単純なネステッド・ループを使います INNER JOIN で2つの列の同値を探す場合、人間の感覚では2つ並べて交互に比較しますが・・・ 1 2 3
4 1 2 4 8 2つのクエリを同時に読み進め ながら比較 同じ値 あり と なし 残念ながらこうはやってない
EXPLAIN を読む上で、理解しやすくなるポイント 左の1行ごとに、右のテーブルを毎回スキャンして愚直に INNER JOIN を解決 1 2 3 4
1 2 4 8 多段の for ループ で Join Nested Loop Join (NLJ) これ JOIN ですが、何かに似てませ んか? 左の値に関して右でクエリする、 そう・サブクエリです! JOIN = サブクエリ なんだ!
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 の集合や集約の計算 • 最後にレポート列の整形やソート
ota さん 資料拝借
None
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 サブクエリを含む場合のトップレベルクエリ
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”> で表記
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
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: 派生テーブルを生成するク エリ
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 の値に依存)
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 では遅い時代に
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
何故 EXPLAIN で ref という用語が 頻出なのか? 実行計画 = JOIN =
サブクエリの計 画 サブクエリの関心は外のデータをど う参照しているか? になるので reference ワード多用
結合タイプの話の前に、使っているテーブル定義 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 注)業務における最適なテーブル定義については所属チームの先輩方に確認しましょう!
type : const mysql> EXPLAIN SELECT * FROM inventories WHERE
id = 1; +----+-------------+-------------+------------+-------+ | id | select_type | table | partitions | type | +----+-------------+-------------+------------+-------+ | 1 | SIMPLE | inventories | NULL | const | +----+-------------+-------------+------------+-------+ 定数で抽出 = ユニークなインデックスで等価検索
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 は全件検索
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 で
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 も対象にした検索を行うと このタイプに
index フルスキャンの例外とし て、 Extra に “Using Index” がつ いている場合はデータ的にイン デックスの取得で足りていてプラ
イマリキー不要な「カバリングイン デックス 」で動作 この場合重くないこともある オプティマイザは行数が 4〜16倍程度で済む ならインデックスの range 検索ではなくプライ マリキーの ALL を選択しがち。実際その方が 速いことも多い。 type 続き
None
None
ここ重要! 加えて EXPLAIN のサブクエリ行毎 に、ネストしたループの掛け算の行 数でのアクセスになることも忘れな く! 多段のループも静的な場合と、外の 値に依存してて毎回クエリ実行され ている場合とがあるので、さらに注
意!!
おまけ
何故 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; (パフォーマンスが悪い例なので仕事では使わないように!仕事のクエリは所 属チームの先輩に聞きましょうw) +----+-------+---------+ | id | score | ranking | +----+-------+---------+ | 1 | 100 | 4 | | 3 | 200 | 3 | | 2 | 200 | 3 | | 4 | 300 | 1 | +----+-------+---------+
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) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+