MySQLの実行計画

MySQLの実行計画を読むためのヒントとして項目がどういうケースで表示するかまとめた資料

Transcript

1. MySQLの実行計画
 MySQLの動きを知る

2. - 目的
 - MySQLの実行計画の見方を知る 
 - MySQL(RDB)の基本的な動きを知る 
 - 前提知識
 - 基本的なSQL
 - 想定環境
 - MySQL8.0
 - AppleシリコンのMac
 本資料について

3. - 実行したSQLがどのように動くかを知るため
 - 以下のようなケースで役立つ 
 - SQLのパフォーマンスの確認 
 - DBのロックの発生原因の推定 
 なぜ実行計画を読む必要があるのか？

4. 前提のお話
5. - シングルプロセス/マルチスレッド
 - 1コネクション・1スレッド 
 - ストレージエンジンによるデータ格納レイヤーの抽象化
 - InnoDB、MyISAM、MEMORY、MySQL Cluster(NDB) 
 - コストベースのオプティマイザ
 - データの件数やデータの偏り、分布などを加味して実行計画が変化 
 MySQLの特徴

6. MySQLのアーキテクチャ概要
 ストレージエンジンAPI MyISAM InnoDB パーサー、オプティマイザ、エグゼキュータ バイナリログ、レプリケーション、アクセス管理 等 コネクション スレッド コネクション スレッド コネクション スレッド コネクション スレッド memcached プラグイン その他
7. アーキテクチャの用語
 - パーサー
 - クエリ(SELECT * FROM some_table WHERE id = 1) を解析して抽象構文木を作る
 - オプティマイザ
 - 構文木を元にクエリの最適化を行い、実行計画を作成
 - エグゼキュータ
 - テーブルの利用権限を確認し、実行計画を元にSQLの実行を担当
 - 実際の結果の取得はストレージエンジンAPIを介してストレージエンジンが実行(するはず)
 - バイナリログ
 - DB上の変更イベントを記録するログファイル
 - 基本的にテーブルの内容の変更前に書き込まれる

8. SQLの実行の流れ
 パーサー (SQLを解析し抽象構文木を構築 ) InnoDB(行データを返す) コネクションスレッド(SQLの受信・結果の送信 ) オプティマイザ (最適化と実行計画作成) エグゼキュータ(クエリの実行) クライアント 結果セット
9. オプティマイザの役割
 - 「式の等価な変換」によって複数の実行計画を立て、最適なものを選択 
 - 式の等価な交換とは？ 
 - 内部結合(INNER JOIN) の順序入れ替え 
 - WHERE句の条件の評価順序の入れ替え 
 - サブクエリの別のアルゴリズムへの置き換え 
 - 最適なもの
 - MySQLはコストベースで実行計画を選択 
 - 「こういう処理をするとこのくらいコストがかかる」と予め定義されている 
 - あくまで見積もられるコストは見積もりで正確とは限らない 
 - コストはテーブルの統計情報から得られる概算値 

10. 前準備
11. mysqlのコンテナを起動してみる
 docker run --name some-mysql --platform linux/x86_64 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d -p 3306:3306 mysql:8
 作業
 - すでにコンテナ:some-mysqlがある場合
 - 適当に名前を変更してください
 - すでにポート3306で他のプロセスが動いている場合
 - ポート番号を変更するか、既存プロセスをkillしてください 

12. mysql -u root -pmy-secret-pw -h 127.0.0.1 
 - -u
 - ユーザー名の指定
 - 後ろに半角スペースを入れてユーザー名(root)を指定
 - -p
 - パスワードの指定
 - 後ろに半角スペースを入れずにパスワード(my-secret-pw)を指定
 - -h
 - 接続先ホストの指定
 - 省略もしくは localhost を指定するとUNIXドメインソケットというファイルを介した通信となる。仮想環境だとうまくいかない。 
 cliでmysqlに接続する
 作業

13. - 接続できたら以下のような画面が表示される
 cliでmysqlに接続
 確認

14. CREATE DATABASE test_db; 
 
 use test_db;
 
 CREATE TABLE `t` ( 
 `c1` int(10) unsigned NOT NULL, 
 PRIMARY KEY (`c1`) 
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
 INSERT INTO `t` (`c1`) VALUES (10), (14), (20); 
 DBとテーブルを作る
 作業

15. - テーブル t から レコードを取得できるか確認
 DBとテーブルの作成の確認
 確認

16. 実行計画
17. 実行計画
 - MySQLがどのようにクエリを実行して結果を組み立てるかの計画
 - EXPLAINコマンド
 - 実行するクエリを渡すとMySQLが選択した実行計画を返す 
 - 読むには慣れが必要なので実行して、読んでみよう 
 ポイント
 EXPLAIN SQL文;
 
 ex. EXPLAIN SELECT * FROM t WHERE c1 = 10; 

18. 実行計画を取得してみる
 確認
 EXPLAIN SELECT * FROM t WHERE c1 = 10; 
 作業

19. 実行結果の取得結果例(;の代わりに\Gを最後に付けた場合)

20. EXPLAINの各カラムの意味(1)
 - id - クエリの実行単位 - MySQLはJOINを1つの単位として実行する - JOINしか行っていない場合は常に 1 - 実行順ではないので注意 - サブクエリや UNIONがある場合は複数の idが実行計画に現れる - select_type - テーブルにどのようにアクセスしたか - 単一テーブルや JOINの場合はSIMPLE - サブクエリが絡む場合は PRIMARY, SUBQUERY, DEPENDENT SUBQUERY など
21. EXPLAINの各カラムの意味(2)
 - table - 実行単位でアクセスするテーブル名 - UNIONを使った場合は <union $id1,$id2>のようになる - partitions - パーティショニングが行われているフィールド - パーティショニングしていない場合は NUL - アクセス対象の絞り込みが有効な場合のみ表示
22. EXPLAINの各カラムの意味(3)
 - key - クエリ実行時に 実際に利用するインデックス - インデックスが使われないと NULLになる - possible_keys - クエリ実行時に 利用できるうるインデックス - type - どのようにそのテーブルからデータを読み込むか
23. - key_len - 読み込むインデックスの長さ - 選択されたキーの長さ - 長さは短いほうが高速に読める - 複合インデックスの読み込み範囲を判断できるらしい - ref - 検索条件で keyと比較されている値やカラムの種類 - 定数の場合 : const - JOINを使用している場合 : 結合する相手側のテーブルで検索条件として 利用されるカラム EXPLAINの各カラムの意味(4)

24. - filtered - 取得された行データに WHERE句が適用さ、 どれだけの行数が残るか (概算) - ストレージエンジンではなく、 MySQLサーバーが行うフィルタリング - rows - アクセスによってどれだけの行が取得されるかを示す (概算) - 最初にアクセスされるテーブル : クエリ全体によってアクセスされる行数 - JOINしてる場合、以降のテーブル : 1行のJOINごとに何行のアクセスが発生する行数 EXPLAINの各カラムの意味(6)

25. EXPLAINの各カラムの意味(5)
 - Extra - オプティマイザが残してくれる動作ヒント
26. - クエリは1つ
 - 単一テーブルだけのクエリ
 - アクセスするテーブルは t
 - パーティションはなし
 - 主キーに対する等価比較
 - 利用できる候補のインデックスはPRIMARY 
 - 利用するインデックスはPRIMARY 
 - インデックスの長さは4
 - 比較方法は定数との比較
 - 読み込む行数は1行(概算)
 - フィルタリング後も100%残る
 例のEXPLAINを読んでみると　

27. これで完璧！？
 - これで本番環境で「SQLの実行が遅いな…」となっても対処できませんね
 - 実際に各項目にどんなものが表示されるのかイメージが難しいと思います
 - いくつかの例を見ながら実行計画を読む練習をしましょう

28. 補足：実行計画に表示される項目詳細 (本編はこの後も続くよ)
29. - SIMPLE
 - 単一テーブルに対するクエリ 
 - PRIMARY
 - もっとも外側の SELECT(最後に実行される) 
 - UNIONを使うクエリでは最初にフェッチされるテーブル 
 - SUBQUERY
 - サブクエリ内の最初の SELECT 
 - DEPENDENT SUBQUERY
 - サブクエリ内のSELECTが外側のクエリのカラムを参照している(相関サブクエリ) 
 - DERIVED
 - FROM句で用いられているサブクエリ 
 select_typeカラムに表示される値

30. - UNION
 - UNION 内の 2 つめ以降の SELECT 文 
 - DEPENDENT UNION
 - DEPENDENT SUBQUERYがUNIONになっている 
 - DEPENDENT SUBQUERYについては次のページを参照 
 - UNION RESULT
 - UNIONの実行結果 
 - MATERIALIZED
 - 実体化されたサブクエリ 
 - UNCACHEABLE SUBQUERY
 - 実行する度に結果が変わる可能性のあるサブクエリ 
 - UNCACHEABLE UNION 
 - キャッシュ不能なサブクエリに属する UNION 内の 2 つめ以降の SELECT 
 select_typeカラムに表示される値

31. - ALL
 - テーブルのデータの全件スキャン 
 - index
 - インデックスの全件を読み込む 
 - const
 - 主キーまたはユニークキーによる結果が1行となる等価比較 
 - 該当する行がない場合はクエリ自体が実行されずconstにならない 
 - JOINではなく最外部のクエリに対するアクセスタイプ 
 - range
 - インデックスのとくていの範囲へのアクセス 
 - ref
 - ユニークでないインデックスに対する等価比較 
 - 複数行をフェッチする可能性がある 
 - last_name = 'taketo' AND first_name = 'iseki' 
 typeカラムに表示される値

32. - eq_ref
 - JOINにおいて主キーやユニークキーによる一意な値によるアクセス 
 - JOIN対象を1行だけ正確にルックアップし結合できる場合 
 - 
 - ref_or_null
 - refに加えて、NULLのエントリにアクセスする 
 - NULLになっている行はインデックスの先頭に格納されている 
 - col1 = 'some value' OR col1 IS NULL　のような条件式 で使われる 
 - index_merge
 - 複数のインデックスを使って行を取得し、その結果をマージする 
 - NULL
 - WHERE句の条件に合致する行がテーブル情報や統計情報、インデックスから存在しないことがわかった場合 
 typeカラムに表示される値

33. typeカラムに表示される値
 - unique_subquery
 - 主キーまたはユニークキーを使ったINサブクエリへのアクセス(速い) 
 - サブクエリの結果として返されるカラムにインデックスが存在している場合に利用 
 - 内部的にサブクエリをJOINに変換する 
 - index_subquery
 - インデックスを使った、INサブクエリへのアクセス(速い) 
 - サブクエリの結果として返されるカラムにインデックスが存在している場合に利用 
 - 内部的にサブクエリをJOINに変換する 
 - system
 - テーブルに1行しかない場合の特殊なアクセスタイプ 
 - MEMORYストレージエンジンの場合表示される 
 - fulltext
 - 全文検索で利用されるアクセスタイプ 

34. - Using where
 - アクセスタイプの方法で行を取得後、更に検索条件で絞り込む
 - Using index
 - インデックスオンリースキャン(カバリングインデックスでのクエリ解決)
 - Using index condition
 - インデックスコンディションプッシュダウン最適化が行われたことを示す
 - セカンダリインデックスに適用できる仕組み
 - ストレージエンジンにクエリの条件式を渡す（Pushdownする）
 - インデックスの走査中に条件式を適用し行へのアクセスを削減できる
 - 5.5までなら走査中にみつかったインデックスレコードすべてに対応する
 全てのレコードを取得してMySQLに返す
 Extraカラムに表示される値

35. - Using index for group by
 - GROUP BY をカバリングインデックスで解決
 - Using filesort
 - インデックスだけでの並び替えができず、ファイルソートを行う
 - Using tmporary
 - ソート時やUNION時に一時テーブルが作成されることを示す
 - Using MRR
 - マルチレンジリード最適化が用いられる
 - セカンダリインデックスを範囲スキャンし、プライマリキーを取得
 - プライマリキーをソートした上で行にアクセスすることでランダムアクセスを削減
 Extraカラムに表示される値

36. - Using join buffer(Block Nested Loop)
 - JOINに適切なインデックスがなくJOINバッファが利用された 
 - 何もしないと駆動表(ベースになるテーブル)から1行を読み取り、 
 内部表(結合先を探すテーブル)からマッチする行を探す 
 - Block Nested Loopは駆動表から一旦JOINバッファにデータを移し、 
 JOINバッファが満タンになったら内部表を操作する 
 - これにより「駆動表からフェッチしたレコード数 / JOINバッファに貯めることができるレコード 数」まで内部表のスキャン回数が減る 
 Extraカラムに表示される値

37. - Using join buffer (Batched Key Access) 
 - バッチキーアクセスによりJOINバッファが使われた 
 - 先述のBlock Nested Loopと基本は同じだが、 
 内部表に対してMRR最適化を用いインデックスを用いてアクセスする 
 - Using sort union(...), Using union(...), Using intersect(...) 
 - インデックスマージによってテーブルへアクセスされていることを示す 
 - Distinct
 - DISTINCTが指定され、なおかつJOINするクエリでは、 
 内部表では最低1行のレコードがあれば、それ以外の行は不要なので、 
 探索を途中で中止できる 
 - Range checked for each record(index map: N) 
 - JOINにおいて内部表へアクセスする際、駆動表から取得した行に基づき 
 適切なインデックスがあるか確認する 
 Extraカラムに表示される値

38. - Not exist
 - LEFT JOIN において内部表にマッチする行がないものを検索するために、
 joined_tabke.key IS NULL を指定した場合、内部表にマッチする行があれば
 直ちにそのキーに対する検索をやめても構わない
 - Full scan on NULL key？
 - INサブクエリが、外側のクエリで SELECTの結果として返される可能性があり、
 IN句のキーがNULLである可能性がある場合、
 インデックスによる検索の代わりにフルスキャンをする必要がある
 - インデックスルックアップアクセスメソッドを使用できない場合に行われる
 - const row not found
 - テーブルが空であることを占めす
 Extraカラムに表示される値

39. - no matching row in const table 
 - 主キーまたはユニークキーでの検索で該当する行がなかったことを示す 
 - FirstMatch(tbl_name) 
 - セミジョイン最適化においてFirstMatchアルゴリズムが採用された 
 - セミジョイン最適化 
 - 駆動表の1行に対して内部表からマッチする行が1行だけになるJOIN 
 - WHERE句の条件を JOIN に変換して実行する戦略を取れる 
 - FisrsMatch
 - JOINする際に、内部表に1行でも条件に合致するものがあれば、 
 外部表の次の行と合致するものを探し始める 
 Extraカラムに表示される値

40. - LooseScan(m..n)
 - セミジョイン最適化においてLooseScanアルゴリズムが採用された 
 - LooseScan(本来の意味と違う気もするが…) 
 - FisrsMatch に似ているが、こちらはインデックスを利用し 
 重複するレコードを避けて JOINを実行する 
 - Start temporary, End temporary
 - セミジョイン最適化において、一時テーブルによる重複排除アルゴリズムが採用された 
 - 行IDをプライマリキーとした一時テーブルを作成 
 - INNER JOINを行い、結果を一時テーブルに挿入 
 - この際、行IDがプライマリキーとなっているので、重複は排除される 
 - Impossible HAVING
 - HAVING句の条件が常に偽になってしまう 
 - Impossible WHERE 
 - WHERE句の条件が常に偽になってしまう 
 Extraカラムに表示される値

41. - Impossible WHERE noticed after reading const tables 
 - アクセスタイプがconstになってるテーブルから行を読み込んだ結果、 
 WHERE句の条件が偽であることが判明した 
 - No matching min/max row
 - MINまたはMAXを取得するクエリで、WHERE句の条件にマッチする行が存在しない 
 - No matching rows after partition pruning 
 - パーティションの刈り込みを行ったあとで、検索条件にマッチする行がない場合に表示 
 - No tables used
 - ダミーテーブル(DUALという名前)への参照か、FROM句の省略 
 - Plan isn’t ready yet
 - 現在実行中のクエリに対して実行計画を取ったが、まだ作成できていない 
 - Select tables optimized away
 - MINまたはMAXを取得するクエリで、インデックスから1行だけ読み込めば良い場合 
 Extraカラムに表示される値

42. select_type
43. テーブルの作成(1)
 CREATE TABLE `parent_table` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `uc` int(11) DEFAULT NULL, `dc` int(11) DEFAULT NULL, `nc` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uc_index` (`uc`), KEY `dc_index` (`dc`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; 作業

44. テーブルの作成(2)
 CREATE TABLE `child_table` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `uc` int(11) DEFAULT NULL, `dc` int(11) DEFAULT NULL, `nc` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uc_index` (`uc`), KEY `dc_index` (`dc`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; 作業

45. - カラム名の説明
 - id : プライマリキー
 - uc: ユニークキー(一意制約)をかけているカラム (Unique Column) 
 - dc: 普通のインデックスがはられているカラム(Default index Column) 
 - もしかしたらDuplicatableかも… 
 - nc: インデックスが貼られていないカラム(Normal Column) 
 補足

46. レコードの作成(1)
 INSERT INTO `parent_table` (`id`, `uc`, `dc`, `nc`) VALUES (1,10,1,1), (3,30,8,9), (5,50,0,3), (7,70,1,1), (9,90,3,10); 作業

47. レコードの作成(2)
 INSERT INTO `child_table` (`id`, `parent_id`, `uc`, `dc`, `nc`) VALUES (1,1,100,3,5), (3,3,300,3,10), (5,5,500,10,2), (7,7,700,1,8), (9,9,900,4,5); 作業

48. - レコードがうまくできたか確認する
 テーブルの確認
 確認

49. select_type: SIMPLEな実行計画(1)
 確認
 EXPLAIN SELECT * FROM parent_table; 
 作業

50. - 単純な SELECT (UNION やサブクエリを使用しない)
 - あくまで実行時にUNION やサブクエリを使用しない場合 
 - MySQL5.6以降、一部のサブクエリであっても SIMPLE になりえる 
 - 可能であればサブクエリがセミジョインという仕組みで実行されるから 
 - セミジョイン
 - サブクエリ内のテーブルの重複レコードを取り除き、 
 JOINと同じように動作する 
 select_type: SIMPLE

51. select_type: SIMPLEな実行計画(1)
 ポイント
 - select_type:SIMPLE
 - 単純な SELECT文 (UNION やサブクエリを使用しない) 
 - type: ALL
 - テーブルのデータの全件スキャン(インデックスを使っていない) 

52. select_type: SIMPLEな実行計画(2)
 確認
 作業
 EXPLAIN SELECT * FROM parent_table 
 INNER JOIN child_table ON parent_table.id = child_table.parent_id; 

53. select_type: SIMPLEな実行計画(2)
 ポイント
 1. child_tableから全行(5レコード)を読み込む
 2. parent_tableから PRIMARY キーを使ってレコードを読み込む 
 a. type : eq_ref
 i. JOINにおいて主キーやユニークキーによる一意な値によるアクセス 

54. EXPLAIN SELECT * FROM parent_table 
 WHERE id IN (SELECT parent_id FROM child_table); 
 select_type: MATERIALIZEDな実行計画
 確認
 作業

55. select_type: MATERIALIZED
 - サブクエリが一時テーブルに実体化されたことを表す
 - 一時テーブル
 - 内部的に作成されるテーブル 
 - ユーザーは直接利用できない 
 - SQL文の実行が終了すると自動的に削除される 

56. select_type: MATERIALIZEDな実行計画
 ポイント
 1. id:2のサブクエリ部分が実行される 
 a. child_tableからインデックスを使わずに全行を取得
 b. 取得結果を元に一時テーブル<subquery2> が作成される
 2. id:1の1行目の部分を実行し、一時テーブル<subquery2>を1行ずつ取得 
 あわせて id:1の2行目の部分を実行し、parent_tableをプライマリキーでレコードを取得し結合 

57. - 実際にSQL文を実行し、実行計画がどのように実行されたかを出力
 - MySQL 8.0.18 から追加された機能 
 SQL実行時の実際の挙動の確認
 ポイント
 EXPLAIN ANALYZE SQL文;
 
 ex. EXPLAIN ANALYZE SELECT * FROM t WHERE c1 = 10; 

58. select_type: MATERIALIZEDな実行計画
 ポイント
 1. id:2のサブクエリ部分が実行される 
 a. child_tableからインデックスを使わずに全行を取得
 b. 取得結果を元に一時テーブル<subquery2> が作成される
 2. id:1の1行目の部分を実行し、一時テーブル<subquery2>を1行ずつ取得 (Nested loop join)
 あわせて id:1の2行目の部分を実行し、parent_tableをプライマリキーでレコードを取得し結合 

59. ネスティッドループJOIN
 - ネストしたfor ループのように、合致するレコードを探していく手法
 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 ポイント

60. EXPLAIN SELECT * FROM parent_table WHERE id = 7 
 UNION SELECT * FROM parent_table WHERE id BETWEEN 1 AND 3; 
 select_type: MATERIALIZEDな実行計画
 確認
 作業

61. select_type: UNION
 - UNION
 - UNION 内の 2 つめ以降の SELECT 文 
 - UNION RESULT
 - UNIONの実行結果

62. 1. id:1 の2行目の部分を実行
 a. parent_tableからPRIMARYキーを使って 1 <=id <=3 の範囲のレコードを取得
 2. id:1 の1行目の部分を実行
 a. parent_tableからPRIMARYキーを使って id = 7 のレコードを取得
 3. 1, 2の結果を結合した一時テーブル<union1, 2>を作成し、全内容を読み込む 
 select_type: UNIONな実行計画
 ポイント

63. type
64. type: ALLな実行計画
 確認
 EXPLAIN SELECT * FROM parent_table; 
 作業

65. type: ALLな実行計画
 ポイント
 - type : ALLはテーブルの全レコードの読み込み
 - parent_tableを最初から最後まで全行読み取る
 - インデックスが使われない 
 - その他の発生パターン
 - インデックスの貼っていないカラムでの検索・並び替え 
 - SELECT * FROM parent_table WHERE nc = 10; 
 - SELECT * FROM parent_table ORDER BY nc; 

66. type: indexな実行計画
 確認
 EXPLAIN SELECT * FROM parent_table ORDER BY id; 
 作業

67. - type : indexは指定インデックスの全読み込み 
 - parent_tableのインデックス PRIMARY を最初から最後まで全行読み取る 
 - その他の発生パターン
 - インデックスだけのアクセスで結果が返せる場合 
 - SELECT uc FROM parent_table ORDER BY uc; 
 type: indexな実行計画
 ポイント

68. - 行のデータが格納されているクラスタ化された 
 特別なインデックス
 - InnoDBでは一般的に主キー(一意インデックス)と同意 
 - テーブルの行データは実際にはインデックスのリーフページに格納さ れるので
 InnoDBでは実質的にテーブルと同じ扱いのはず 
 - プライマリキーを作らなかった場合、内部にあるROW_ID順にクラスタ インデックスが構成される
 クラスタインデックス(プライマリキー)
 ポイント
 クラスタインデックス PK:4 PK:5 PK:6 その他のカラム その他のカラム その他のカラム PK:7 PK:8 PK:9 その他のカラム その他のカラム その他のカラム PK:10 PK:11 PK:12 その他のカラム その他のカラム その他のカラム PK:1 PK:2 PK:3 ... その他のカラム その他のカラム その他のカラム ...
69. - 指定されたのキーの値とプライマリキーの値を保持するデータ構造
 セカンダリインデックス(普通のインデックス)
 ポイント
 セカンダリインデックス Key:1004 Key:1005 Key:1007 PK:7 PK:2 PK:9 Key:1010 Key:1011 Key:1013 PK:11 PK:10 PK:4 Key:1014 Key:1015 Key:1017 PK:6 PK:8 PK:12 Key:1000 Key:1001 Key:1002 ... PK:1 PK:5 PK:3 ...
70. 何の最適化もしない場合
 - セカンダリインデックスを走査する
 - セカンダリインデックスに格納されたPKの値で、クラスタ インデックスのレコードをルックアップ
 - MySQLサーバーにテーブルのレコードを返す
 例:
 SELECT * FROM tbl 
 WHERE indexed_col 
 BETWEEN 1004 AND 1007;
 セカンダリインデックスを使ったテーブルの読み込み
 セカンダリインデックス Key:1004 Key:1005 Key:1007 PK:7 PK:2 PK:9 Key:1010 Key:1011 Key:1013 PK:11 PK:10 PK:4 Key:1014 Key:1015 Key:1017 PK:6 PK:8 PK:12 Key:1000 Key:1001 Key:1002 ... PK:1 PK:5 PK:3 ... クラスタインデックス PK:4 PK:5 PK:6 その他のカラム その他のカラム その他のカラム PK:7 PK:8 PK:9 その他のカラム その他のカラム その他のカラム PK:10 PK:11 PK:12 その他のカラム その他のカラム その他のカラム PK:1 PK:2 PK:3 ... その他のカラム その他のカラム その他のカラム ...
71. カバリングイングインデックス
 - クエリ実行に必要な内容が 
 セカンダリインデックスだけでまかなえる 場合、クラスタインデックスへのアクセス を省略できる
 - この手法をカバリングインデックスと呼ぶ
 - SELECT id, indexed_col FROM tbl 
 WHERE indexed_col 
 BETWEEN 1004 AND 1007; 
 セカンダリインデックス Key:1004 Key:1005 Key:1007 PK:7 PK:2 PK:9 Key:1010 Key:1011 Key:1013 PK:11 PK:10 PK:4 Key:1014 Key:1015 Key:1017 PK:6 PK:8 PK:12 Key:1000 Key:1001 Key:1002 ... PK:1 PK:5 PK:3 ... クラスタインデックス PK:4 PK:5 PK:6 その他のカラム その他のカラム その他のカラム PK:7 PK:8 PK:9 その他のカラム その他のカラム その他のカラム PK:10 PK:11 PK:12 その他のカラム その他のカラム その他のカラム PK:1 PK:2 PK:3 ... その他のカラム その他のカラム その他のカラム ...
72. インデックスの構造に関する注意
 - 先ほどのインデックスの構造は理解しやすくするために簡略化しています 
 - 実際には以下のようにB木構造になっています 
 - リーフノード上の各レコードにはカラムの情報以外にも 
 トランザクションIDやロールバックポインタなどを含みます 

73. type: constな実行計画
 確認
 EXPLAIN SELECT * FROM child_table WHERE id = 1; 
 作業

74. - type : constは主キーまたはユニークキーに対する結果が1行となる等価比較 
 - child_tableの PRIMARYインデックスから、id=1の行を取得する 
 type: constな実行計画
 ポイント

75. type: rangeな実行計画
 確認
 EXPLAIN SELECT * FROM parent_table WHERE uc < 4; 
 作業

76. - type : rangeは指定インデックスの特定の範囲へのアクセス 
 - parent_tableにあるインデックスuc_indexの一部を読み取る 
 - その他の発生パターン
 - セカンダリインデックスのアクセスで結果が返せる場合 
 - SELECT uc FROM parent_table ORDER BY uc; 
 type: rangeな実行計画
 ポイント

77. type: eq_refな実行計画
 確認
 EXPLAIN SELECT * FROM parent_table 
 INNER JOIN child_table ON parent_table.id = child_table.parent_id; 
 作業

78. - type : eq_ref はJOIN時に主キーやユニークキーによる=(等号)を用いた結合時に発生 
 - 動作
 - child_tableのテーブルから1行ずつ読み込む
 - child_tableから取得したparent_id と parent_tableのPRIMARYインデックスを使い、parent_tableの行を取得
 - parent_tableから取得した行とchild_tableで取得した行を結合、結果セットに入れる
 - これをchild_tableの最後の行まで繰り返す
 type: eq_refな実行計画
 ポイント

79. EXPLAIN SELECT * FROM parent_table WHERE dc = 10; 
 type: refな実行計画
 確認
 作業

80. - type : ref はユニークでないインデックス に対する比較(= , <=>)時に発生
 - parent_tableのdc_indexを使って、レコードを読み込む 
 - その他のケース
 - (col1, col2, col3)のような複合キーで、
 キーの左側の一部(例: (col1, col2))だけが検索に利用される場合もrefになる 
 type: refな実行計画
 ポイント

81. EXPLAIN SELECT * FROM parent_table WHERE dc = 1 OR dc IS NULL; 
 type: ref_or_nullな実行計画
 確認
 作業

82. - type : ref_or_null はユニークでないインデックスに対する等価比較 OR NULL比較のアクセス 
 - NULLはインデックスの先頭に格納されるので、 IS NULLと IS NOT NULLの検索にはインデックスを使える 
 - prent_tableのdc_indexからカラムdcの値が1の箇所、dcの値がNULLの箇所を読み込み、行を取得している 
 - 注意点
 - レコード数が少ない場合はテーブルの全件スキャンが行われる場合がある
 type: ref_or_nullな実行計画
 ポイント

83. テーブルの作成
 CREATE TABLE `parent_table_copy` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `uc` int(11) DEFAULT NULL, `dc` int(11) DEFAULT NULL, `dc2` int(11) DEFAULT NULL, `nc` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uc_index` (`uc`), KEY `dc_index` (`dc`), KEY `dc2_index` (`dc2`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; 作業

84. レコードの作成
 INSERT INTO `parent_table_copy` (`id`, `uc`, `dc`, `dc2`, `nc`) VALUES (1,10,1,1,1), (3,30,8,8,9), (5,50,0,0,3), (7,70,1,1,1), (9,90,3,3,10); 作業

85. EXPLAIN SELECT * FROM child_table_copy WHERE dc = 3 OR dc2 = 3; 
 type: index_mergeな実行計画
 確認
 作業

86. - type : index_merge は 複数のインデックスを使って行を取得し、その結果をマージした場合 
 - マージのアルゴリズムはいくつかある(本資料では紹介しない)
 - 動作
 - dc_indexを用いて、dc = 3 を検索
 - dc_index2を用いて、dc2 = 3 を検索
 - それぞれの検索結果をマージしたものを返す
 type: index_mergeな実行計画
 ポイント

87. EXPLAIN SELECT * FROM child_table WHERE id IS NULL; 
 type: NULL な実行計画
 確認
 作業

88. - type : NULL は 統計情報を見た時点で結果がない場合に発生
 - 統計情報は以下のようなもの 
 - インデックス情報 
 - テーブルのレコード数, カラム数 
 - カラム値のデータ分布, カーディナリティ 
 - 他にも存在しないユニークキーに対する検索時にも発生
 - EXPLAIN SELECT * FROM child_table WHERE id = 2; 
 type: NULLな実行計画
 ポイント

89. - 基本的には…
 - 良いやつ
 - WHERE句などで条件を絞り込んだ 
 - プライマリインデックス単体へのアクセス 
 - セカンダリインデックス単体へのアクセス 
 - カバリングインデックス 
 - 良くないやつ
 - テーブルフルスキャン(ALL) 
 - インデックスフルスキャン(index) 
 - 一時テーブルの作成(可能であれば) 
 結局どんなクエリがいいの？
 ポイント

90. Extra
91. EXPLAIN SELECT dc, id FROM parent_table; 
 Extra: Using index
 確認
 作業

92. - Extra: Using index は インデックスオンリースキャン(カバリングインデックスでのクエリ解決)時に発生 
 - インデックスに含まれるカラム値のみだけを返すことができる 
 - その他の例
 - SELECT id FROM parent_table; 
 - SELECT dc, id FROM parent_table; 
 Extra: Using index
 ポイント

93. EXPLAIN SELECT * FROM parent_table_copy WHERE id IN (1, 2, 3) AND nc < 4; 
 Extra: Using where
 確認
 作業

94. - Extra: Using where は typeで指定したの方法でストレージエンジン(InnoDB)から行を取得後、 
 更にMySQLサーバーで絞り込む必要がある場合に発生 
 - インデックスが有効でないカラムへの条件適用が必要な時(今回の例の nc < 4 の部分) 
 - インデックスが貼られているカラムへのtype:rangeでの読み取り時 
 - その他の例
 - SELECT dc FROM parent_table_copy WHERE dc IN (1, 4, 7); 
 - type : rangeで範囲読み込みしてるためMySQLサーバでフィルタリングしなくても問題なさそうだがダメらしい 
 Extra: Using where
 ポイント

95. EXPLAIN SELECT * FROM parent_table_copy 
 WHERE dc BETWEEN 1 AND 10 AND dc <> 3; 
 Extra: Using index condition
 確認
 作業

96. - Extra: Using index condition は セカンダリインデックスでの検索時にICP最適化が行われる場合に発生 
 - ICP(Index Condition Pushdown)
 - MySQL5.6まで
 - ICP最適化がなく、インデックス読み込み時は「この範囲を読み込んで返して」という処理 
 - MySQL5.6以降
 - ICP最適化により、インデックス読み込み時は「この範囲を読み込んで、この条件に合致するものだけ返して」という処理 
 Extra: Using where
 ポイント

97. - MySQL5.6からの新機能
 - 従来
 - InnoDBがセカンダリインデックス走査
 - MySQLサーバーがセカンダリインデックスの
 情報を元に更に絞り込み
 - PKからテーブルのレコードを読み出す
 - ICP
 - InnoDBセカンダリインデックス走査
 - InnoDBが走査中に押し付けられた検索条件でフィルタリング
 - PKからテーブルのレコードを読み出す
 ICP(インデックスコンディションプッシュダウン)とは？
 引用：https://developpaper.com/question/mysql-execution-plan-use-where-use-index-and-use-index-condition/
98. EXPLAIN SELECT * FROM parent_table WHERE uc BETWEEN 10 AND 50 ORDER BY dc; 
 Extra: Using filesort
 確認
 作業

99. - Extra: Using filesortは以下のような時に発生、ファイルソートが発生することを示す 
 - ORDER BYで指定したカラムがインデックスに含まれていない場合 
 - 上記例のケース
 - テーブルを絞り込まずにソートした場合
 - EXPLAIN SELECT * FROM parent_table ORDER BY uc; 
 - filesortはクイックソートのこと
 - ある程度のサイズであればメモリ上で、閾値を超えると一時ファイルを作ってソートするので遅い 
 Extra: Using filesort
 ポイント

100. EXPLAIN SELECT dc + 1, MIN(uc) FROM parent_table GROUP BY dc + 1; 
 Extra: Using temporary
 確認
 作業

101. - Extra: Using temporaryは以下のような時に発生、一時表が作られることを示す
 - Group By句に計算式がある
 - 上記ケース
 - Group By句があるクエリでソートの条件に集約関数を利用
 - SELECT dc, MIN(uc) FROM parent_table GROUP BY dc ORDER BY MIN(uc);
 - UNION実行時
 - (SELECT * FROM parent_table) UNION (SELECT * FROM parent_table);
 - ディスク上に一時表が作られるので重たい処理になる
 Extra: Using filesort
 ポイント

102. EXPLAIN SELECT * FROM parent_table 
 INNER JOIN child_table ON parent_table.nc = child_table.nc; 
 Extra: Using join buffer(Block Nested Loop)
 確認
 作業

103. Extra: Using join buffer(Block Nested Loop)
 ポイント
 - JOIN時に適切なインデックスがなく 、JOINバッファが利用されたことを示す 
 - BNLを使うことで少しだけパフォーマンスを改善しようとしている 
 - インデックスが貼れないか検討しても良い 
 - Extra: Using join buffer(Block Nested Loop)は以下のような時に発生 
 - JOINに適切なインデックスがなくJOINバッファが利用された時 

104. 内部表 ブロックネスティッドループJOIN
 - 内部表にアクセスする際に、インデックスが使えない場合に利用するアルゴリズム
 - JOINバッファ (JOIN用のメモリ) を使って内部表がスキャンされる回数を減らす 
 - スキャン回数を「駆動表の行数/バッファプールに格納できる行数」回に減らせる 
 - 駆動表
 - 1行ずつレコードが取り出されるテーブル 
 - 内部表
 - 結合対象のレコードが検索されるテーブル 
 - BNLJじゃない場合 
 - 駆動表から1行取り出すたびに、内部表がスキャンする 
 - すべてのデータがバッファプール(メモリ上)に乗り切るなら効果は薄い 
 JOINバッファ 駆動表
105. - なぜ実行計画を読む必要があるのか？ 
 - 実行したSQLがどのように動くかを知るため 
 - 以下のようなケースで役立つ
 - SQLのパフォーマンスの確認
 - DBのロックの発生状態の推定
 - ただし実行計画はテーブルのレコード数などの統計情報に依存 
 - 開発環境と本番環境で一致するとは限らないので注意 
 - 改善のためにインデックスを追加すると更新速度が遅くなる可能性がある 
 - きちんと参照、更新の速度がどう変化するかを検証する 
 まとめ(1)

106. - EXPLAINはあくまでDBが「レコードをこう読んでるよ」という説明
 - 見知らぬものが表示されてるから悪いものとは限らないので冷静に読もう 
 - 本資料でも全ては説明しきれてないので、遅いクエリに遭遇した際には調べて理解しよう 
 - 参考
 - MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format 
 まとめ(2)

107. 参考書籍
 - 詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE) 
 - 実践ハイパフォーマンスMySQL 第3版
 - エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド

108. おわり