MySQL WorkbenchのVISUAL EXPLAINでインデックスの挙動を確認する方法

この記事は、合併前の旧ブログに掲載していた記事（初出：2018 年 8 月 20 日）を、現在のブログへ移管したものです。現時点の情報に合わせ、表記やリンクの調整を行っています。 開発 3 センターでサーバサイドの開発を行っている大原（@kory1202）です。私の部署では LINE ポイントの開発を行っています。 先日、あるテーブルからデータを抽出するコードを書いていたら先輩に「こういうインデックスが必要だよね。」と言われてインデックスについて知識が浅いことに気づかされました。そこで今回はインデックスについて MySQL Workbench の VISUAL EXPLAIN を使いながら勉強した内容を記事にしました。VISUAL EXPLAIN は SQL の EXPLAIN（説明）を図で表示してくれるので、直感的にどの部分が悪いのか、インデックスを導入した時にどの処理が改善されるのかが直感的に分かるので非常にオススメです。 今回は MySQL 5.6 の InnoDB について話します。実験に使った OS は macOS High Sierra 10.13.4 です。以下目次。 インデックスの基礎 インデックスの構造 MySQL Workbench と VISUAL EXPLAIN VISUAL EXPLAIN 実行方法 VISUAL EXPLAIN の色とテーブル図形のテキストの説明ちょっとだけ VISUAL EXPLAIN でインデックスの挙動を確かめる（本題） インデックスなし（簡単に VISUAL EXPLAIN の見方） WHERE の条件にインデックス ORDER BY の条件にインデックス GROUP BY の条件にインデックス MySQL 5.6 リファレンスマニュアル / InnoDB インデックスの物理構造にある通り、B-trees（B 木）の構造になっています。この記事は B-tree の構造の詳細には触れないので、本当にざっくりとしたイメージを以下に載せます。 葉ノードはインデックスで指定したカラムの昇順にソートされていて、実際の行がどこに保存されているかのデータを保持しています。WHERE col1 = 3 ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/3ad315c4e1f2404d8f5dd39ee3ae549b.png?updatedAt=1771310926000) 複合インデックスの場合は以下の図のように、指定したカラムの順（ここでは col1 col2 の順）にソートされます。そのため、WHERE col1 = 2 WHERE col1 = 1 AND col2 >= 2 ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/409e29f3d69c416a8903cb33c082347f.png?updatedAt=1771310938000) MySQL 5.6 リファレンスマニュアル / 8.3.1 MySQL のインデックスの使用の仕組み から辿って読めば、どういう時に使われるか、使えるかについて分かります。 WHERE ・ ORDER BY ・ GROUP BY を素早く実行するために使われる 原則として、1 つのテーブルからは 1 つのインデックスしか使用されません。 テーブル内の行が長く、検索結果の行数が少ない場合に利用されます。 フルテーブルスキャンはディスクシーク回数が最小となるため、クエリの大半にアクセスする場合はインデックスを使用するよりも高速です。 10 行未満や短い行数のテーブルではフルテーブルスキャンが行われます。 「全テーブルの 30% を超えるデータが検索結果に含まれる場合はインデックスは使われない」と言われてきましたが、MySQL5.6 のドキュメントを確認すると、テーブルサイズ、行数、I/O ブロックサイズなどの要因からインデックスを使用するか否かが決定されているようです。 複合インデックス（Composite Index）は単一カラムインデックスの代わりにも使用できます。 (col1, col2, col3) の 3 カラムで構成されるインデックスがある場合、(col1)、(col1, col2)、(col1, col2, col3) に対してそれぞれインデックスを利用可能です（前述したインデックスの構造の節で説明した通りです）。 データ行を参照せずに値のみを取得できるインデックス（カバリングインデックス：Covering Index）を使用すると高速化が図れます。 クエリが必要とする全てのカラムをインデックスが含んでいる場合、例えば column1 と column2 がインデックスに含まれている状態で以下のクエリを実行すると、インデックスから全ての値が得られるため、データ行にアクセスする必要がありません。 SELECT column1 FROM tbl_name WHERE column2 = 1; 一方、以下のクエリはデータ行へのアクセスが必要となります。 SELECT * FROM tbl_name WHERE column2 = 1; インデックスが設定されたカラムに対して MAX() や MIN() 値を見つける際にも利用されます。 複数のインデックスから選択する場合は、最小数の行を処理できるインデックスが使用されます。 別テーブルと JOIN（結合）して使用する際、カラムの型とサイズが一致しているとインデックスが効率的に利用できます。 MySQL Workbench と VISUAL EXPLAIN MySQL Workbench は MySQL の GUI クライアントです。インデックスを試しながら試行錯誤する際に、GUI でインデックスの追加・削除を行えたり、ER 図も作成できたりと、非常に便利です。 そして何より、VISUAL EXPLAIN が利用できます！ ここから「ダウンロードはこちら」ボタンをクリックした先でダウンロード可能です。 VISUAL EXPLAIN の実行方法 SQL 文をクエリ入力欄に記述し、左上にある虫眼鏡マークをクリックするか、メニューの Query > Explain Current Statement を選択します。 または Cmd + Option + x を押します。 ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/dae3575cab034bd9abc5dff5991a43a9.png?updatedAt=1771310956000) EXPLAIN の結果を見たい場合は、図に示した画面中央左の select タグを変更するだけで表示が切り替わるため便利です。また、画面中央右の View Source ボタンを押すことで、JSON 形式で VISUAL EXPLAIN の元の情報を確認できるので、これも非常に便利です。 VISUAL EXPLAIN の色とテーブル図形のテキストの説明（ちょっとだけ） カラフルな四角いオブジェクトは、テーブルに対してどのようにアクセスするかを示しています。青からコストが低く、赤がコストが高いことを示しています。下2つの赤い index と ALL はインデックスを考えるなどしてチューニングしようとよく言われてるやつですね。 EXPLAIN における type ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/c995e22b9f1f4d7bb7a62c72b2f1f418.png?updatedAt=1771395781000) Single row: constant（単一行：定数） ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/2fc4bb12b54d4c39a7a5372d7c5bb522.png?updatedAt=1771395804000) Unique Key Lookup（一意キー検索） ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/2506b8dd9e6d4e06a1c3febf53d034a5.png?updatedAt=1771395828000) Non-Unique Key Lookup（非一意キー検索） ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/9e122ab5b11c45b4868d2aefd68398f0.png?updatedAt=1771395845000) Fulltext Index Search（全文インデックス検索） ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/44796b3a732e419ca24026f7e885e014.png?updatedAt=1771395858000) Index Range Scan（インデックス範囲スキャン） ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/15670c4ec0464ee19ccb6e748b7354cf.png?updatedAt=1771395871000) Full Index Scan（インデックス全体スキャン） ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/8f2a1eb62f0f427e834ed0b42aec8de2.png?updatedAt=1771395885000) Full Table Scan（テーブル全体スキャン） Lookup: where col = 1 のような等価比較 VISUAL EXPLAIN でインデックスの挙動を確かめる（本題） 本題です。青→赤の順にコストが大きいことだけ分かっていれば、詳細に見方を覚えなくても使えます。 今回使うのは下の2つのテーブルです。どのユーザがコンバージョンしたかを持っておく cv テーブルと、それが紐付く広告の ad テーブルです。 今回実験のためにざっくり作成したデータについて下に列挙します。 インデックスは簡略化のため、現時点では PRIMARY KEY のみです。 cv は約 100 万件、ad は約 4000 件です。 cv の status と ad の type はそれぞれ 10 種類あり、偏りはありません。 時刻を保存するカラムは UNIX TIME で、ここ一ヶ月のデータを格納しています。ER 図はせっかくなので MySQL Workbench で出力しました。Workbench は外部キーを貼っておけば自動的にリレーションが図に描かれますが（公式）、今回は説明を簡単にするために PRIMARY KEY のみにしたので下図のようになっています。 ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/3cfd53c1ce9e4edea974c3b46ea9a92d.png?updatedAt=1771310971000) インデックスなし（簡単に VISUAL EXPLAIN の見方） SELECT * FROM cv WHERE status = 2 ORDER BY created_at LIMIT 100; このクエリに対して VISUAL EXPLAIN を実行すると以下の画像が出ます。 ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/4e0b33cba1fc4a74ade63e03f4af8811.png?updatedAt=1771310982000) 比較用：上の VISUAL EXPLAIN に対する EXPLAIN ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/e7f9a4f1438a493ca2ee364154612dfc.png?updatedAt=1771310993000) 矢印の通り読み、cv テーブルの全テーブルをスキャンして得る 100 万行を ORDER BY でソートして結果を得ていることが分かります。真っ赤ですね。100 万行もスキャンして WHERE の条件にマッチする行を探して、全体をソートして 100 件を返しているので重い処理になっています。インデックスを貼る必要があります。 WHERE の条件にインデックス 上記 SQL の為にインデックスを追加していきます。まずは WHERE の条件である status にインデックスを貼ります。インデックスによって status = 2 の行だけを取ってくるのが簡単になります。 ALTER TABLE cv ADD INDEX idx_status (status); ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/0b37c200891c4e1e8306731c4cf97cb8.png?updatedAt=1771311004000) ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/b8a332bc0ffc4440beb70339b74c84a9.png?updatedAt=1771311015000) cv テーブルが緑色になりました。約 21 万件が取得されてソートされるため、先ほどの 100 万件と比べるとかなり軽くなりそうです。この 21 万件という数字は MySQL の予測値ですが、実際には status = 2 のレコードは約 10 万件です。status に偏りがあり大半が status = 2 の場合、インデックスを使わない方が高速であると判断され、フルテーブルスキャン（Full Table Scan）になる可能性があります（インデックスの使われ方の節を参照）。そのような場合は ORDER BY 句にインデックスを適用しましょう。 補足：WHERE 条件におけるインデックスの利用 status に偏りがあり大半が status = 2 の場合、インデックスを使わない方が高速であると判断されフルテーブルスキャンになる可能性があります 上の記述を検証するために実験を行いました。大半どころか、レコード数が 100 万件ある cv テーブルの全レコードを status = 2 に設定し、ANALYZE TABLE cv; を実行しました。 SELECT * FROM cv WHERE status = 2; ORDER BY 条件におけるインデックスの利用 （上で適用したインデックスは DROP 済み） ALTER TABLE cv ADD INDEX idx_created_at (created_at); ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/7fa1c9538b9b4327a7d20452ee1299ad.png?updatedAt=1771311028000) 比較用：上記の VISUAL EXPLAIN に対する EXPLAIN ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/86575acc499f448ea808f3ab57e40a47.png?updatedAt=1771311040000) 今度は ORDER BY 句が黄緑色になりました。テーブル側ではフルインデックススキャン（Full Index Scan）となっています。どのような処理が行われているかというと、created_at のインデックスから順番にレコードを取り出し、status = 2 であるかどうかを確認していきます。そして LIMIT で指定された数だけ status = 2 のレコードが見つかった時点で探索を終了できます（created_at について既にソート済みであるため）。そのため、テーブルから取得されるレコード数の予測は 100 行となっています。status = 2 のレコードが早く見つかるのが理想ですが、逆に最後の方まで見つからない場合はインデックスをすべて読み込まなければなりません。status = 2 がレアなケースではこの状況に陥るため、status にインデックスを適用して WHERE 句で取得されるレコード数を減らすのが良さそうです（数が少なければソートのコストも低くなります）。この辺りの詳細については、『雑な MySQL パフォーマンスチューニング』という記事が分かりやすかったです。 WHERE の条件と ORDER BY の条件のどちらにインデックスを貼るのが良いかは、status の偏りなどを見てつけるのが良いと思います。 または、idx_status GROUP BY の条件にインデックス （上で貼ったインデックスは DROP） SELECT ad_id, COUNT(*) FROM cv WHERE status = 2 GROUP BY ad_id; ad_id ごとの cv の数を確認するクエリです。インデックス無しでは下図のように真っ赤になります。 ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/b0871c509c5a4110ad19e627bd49aab8.png?updatedAt=1771311054000) 比較用：上の VISUAL EXPLAIN に対する EXPLAIN ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/1a0c1ae2054c4c02a87a476ba6346dfc.png?updatedAt=1771311065000) GROUP BY の条件である ad_id にインデックスを貼ると下図のように GROUP BY が黄緑になりました。 ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/51b99e728be543c3b5a8a4cc887cc380.png?updatedAt=1771311075000) 比較用：上の VISUAL EXPLAIN に対する EXPLAIN ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/5fddffa93bbb4697bdf64fdefa88c12b.png?updatedAt=1771311085000) 上図と下図を見比べると、 GROUP の下にあった tmp table, filesort インデックスがまさに一時テーブルの役割を果たすので、この処理が必要なくなってることをこの図が表しています。 GROUP BY の処理は早くなりますが、テーブルへのアクセスは Full Index Scan となっていて重いので WHERE の条件の方にインデックスを貼ることを検討した方が良さそうです。 （上で貼ったインデックスは DROP） ALTER TABLE cv ADD INDEX idx_status_created_at (status, created_at); この複合インデックスをつけて再び以下の SQL を VISUAL EXPLAIN します。 SELECT * FROM cv WHERE status = 2 ORDER BY created_at LIMIT 100; ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/1d2398ed8ba642d8ba1f014235502fd9.png?updatedAt=1771311097000) 比較用：上の VISUAL EXPLAIN に対する EXPLAIN ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/90ca7b64db0d4854b091e0fe4070df02.png?updatedAt=1771311108000) テーブルへのアクセスも ORDER も緑になっていて良い感じです。表形式の EXPLAIN と比較すると VISUAL EXPLAIN は一目でどの部分が改善したのかが分かります。 （上で貼ったインデックスは DROP） 一週間前からカウントして、コンバージョン数が多い順に ad テーブルのレコードを並べたい場合のクエリについて見ます。細かい条件として cv の status は 1、ad の type は 2 のデータを抽出したいです。 SELECT ad.id, COUNT(DISTINCT cv.user_id) as cv_count FROM ad INNER JOIN cv ON cv.ad_id = ad.id AND cv.status = 1 AND cv.created_at >= UNIX_TIMESTAMP(CURDATE() - INTERVAL 7 DAY) WHERE ad.type = 2 AND ad.end_at >= UNIX_TIMESTAMP(CURDATE() - INTERVAL 7 DAY) GROUP BY ad.id ORDER BY cv_count DESC LIMIT 100 ; 上のクエリを VISUAL EXPLAIN すると下のような画像になりました。 ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/1cd8b70d467f40989d3c3f79e0aa445e.png?updatedAt=1771311119000) 比較用：上の VISUAL EXPLAIN に対する EXPLAIN ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/c934f247fa5f4bca9d10e9021367dc2f.png?updatedAt=1771311135000) 複雑になりましたが、VISUAL EXPLAIN は左下から右上へと処理が流れるように図が描かれているようなので、左下から見ていけば良さそうです。この図から分かることについて列挙しておきます。 JOIN が nested loop で処理されている（nested loop についてはここでは詳細に触れませんが以下参考：実例で学ぶ、JOIN (NLJ) が遅くなる理屈と対処法 / MySQL 5.6 リファレンスマニュアル / Nested Loop 結合アルゴリズム） 駆動表の行 1 行に対して、内部表を 1 行ずつスキャンして結合条件にマッチするものを取り出しています。 cv テーブルから読み取られた行に対して、対応する ad テーブルの行が PRIMARY KEY を使って読み取られている（cv テーブルが外側のループ（駆動表・外部表）、ad テーブルが内側のループ（内部表）） buffer_result と記述されている一時テーブルが作られる この一時テーブルを使って GROUP BY （以下インデックスをどのように貼るか試行錯誤していますが、これは私の試行錯誤を記述しているに過ぎず、必ずこうしなければいけないとか、こうするべきとかを示すものではない事をご了承ください。一例として見ていただけると嬉しいです。） まずこの図を見ると cv テーブルの Full Table Scan をやめさせたい気持ちになります。また、ad テーブルは緑で PRIMARY KEY を使用しているので放っておいても良いような気持ちになりますが、PRIMARY KEY は ad と cv を結合する為に使えているのであって ad 自体を type や end_at で絞り込む際にインデックスを使えていないので、この type や end_at の絞り込みは遅いはずです。それなので ad の方にもインデックスを作って様子を見てみます。以下のようなインデックスをつけて何が使われるか確認してみました。 cv テーブル idx_status_created_at: (status, created_at) cv テーブルを絞り込む条件につけた idx_ad_id_status_created_at: (ad_id, status, created_at) cv に関する条件順につけた adテーブル idx_type_end_at: (type, end_at) ad テーブルを絞り込む条件につけた この状態で VISUAL EXPLAIN を実行すると以下のようになりました。 ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/aa14dfeb514d41a4b4e22f7f618f0d44.png?updatedAt=1771311150000) 比較用：上の VISUAL EXPLAIN に対する EXPLAIN ![](https://vos.line-scdn.net/landpress-content-v2-vcfc68aynwenkh3bno0ixfx8/408a1ba2fb8c45adaf5bfc73b3cb3108.png?updatedAt=1771311161000) 色々変わってスッキリしました。ad と cv の位置が入れ替わりました。図から分かる事を列挙します。 ad テーブルが駆動表となり、type や end_at の条件を絞るためにインデックス idx_type_end_at が使われる cv テーブルの idx_ad_id_status_created_at を使って ad テーブルの対応する行を読み取っている cv に関する status や created_at の条件をこのインデックスで絞れているかは分からない 一時テーブル buffer_result が消え、GROUP BY の tmp table も消えました（GROUP BY は色もオレンジになった） 一時テーブル buffer_result が消える理由は、GROUP BY の条件が駆動表（二重ループの外側）の ad.id であるため、あらかじめソートしてからループを回せば GROUP BY の条件にインデックスの節で説明した一時テーブルが不要になるためだと考えられます（参考：実例で学ぶ、JOIN (NLJ) が遅くなる理屈と対処法 / 高速化が難しい JOIN SQL）。内部表で GROUP BY をすると一時テーブルが必須となります。実際、インデックスを貼る前の状態（cv が駆動表の状態）で、GROUP BY を内部表の ad.id ではなく cv.ad_id とすると一時テーブルは出てきません。 さて、複合インデックスを使用しましたが、その一部しか使われていないようであれば単一のインデックスを貼ったほうが良い（例えば cv テーブルのインデックスが ad テーブルとの対応を取るためのみ、つまり a

MySQL WorkbenchのVISUAL EXPLAINでインデックスの挙動を確認する方法

背景や根拠まで確認しますか？

調べる

選ぶ

サイト