Go to Qiita Advent Calendar 2024 Top
LoginSignup
66
37

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

ミライトデザインAdvent Calendar 2021Day 3
@Nyokkiin株式会社ミライトデザイン

MySQLのEXPLAIN ANALYZEの読み方を勉強したよ

Last updated at Posted at 2021-12-03

ミライトデザインアドベントカレンダー3日目です。
ミライトデザイン Advent Calendar 2021のカレンダー | Advent Calendar 2021 - Qiita

昨日は@tkek321さんのKeycloakでOIDC認可コードフローをやってみる記事でした。
OIDCを実際に動かしながら勉強できるので、凄く頭に入ってきやすい良記事でした！

本日は、MySQLに関して最近自分が勉強したことを記事にさせていただきます。

概要

MySQL 8.0.18からEXPLAIN ANALYZEが導入されました。
PostgreSQLでは昔からある機能ですが、MySQLに導入されたのはつい最近のことです。

この記事ではEXPLAIN ANALYZEの読み方について、自分が学んだ内容をご紹介できればと思います。

EXPLAIN ANALYZEってそもそも何？

その前にEXPLAINって何？

EXPLAIN ANALYZEを説明する前に、まずはEXPLAINとは何か軽く説明します。

EXPLAINは、MySQLがクエリをどのように実行するかについての情報を取得するものです。
この情報はよく実行計画と呼ばれます。

使い方は簡単で、実行計画を知りたいクエリの前にEXPLAINを付けて実行するだけです。
例えば、あるSELECT文の実行計画が知りたければ、下記の様にSQLを実行します。

EXPLAIN SELECT * FROM users WHERE user_no='U-00000005';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | user_no       | user_no | 42      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

実行計画を見ることで、

  • 検索が遅くなりそうなクエリになっていないか
  • どのテーブルに対する検索が遅くなっていそうか
  • インデックスが適切に使用されているか

などを調べることができます。

EXPLAINについての読み方については、本記事では詳しく触れません。
興味がある方は下記の記事で非常に詳しく解説されています。

自分もこちらの記事で大変勉強させていただいたので、是非一読をおすすめします。

EXPLAIN ANALYZEはEXPLAINと何が違うの？

EXPLAINはクエリを実行する前の計画を取得しています。

それに対してEXPLAIN ANALYZEは実際にクエリを実行して各ステップでの実行時間を測定してくれます。

そのため、クエリのどのステップに時間がかかっているかをより正確に掴むことができます。

また、実行計画と実行結果が大きく異なっているかを確認し、テーブルの統計情報がずれていないかを判断する材料にも使えます。

環境

MySQL: 8.0.26

今回は下記の2テーブルを使用して検証しています。

SHOW CREATE TABLE users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_no` varchar(10) COLLATE utf8mb4_bin NOT NULL,
  `name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_no` (`user_no`)
) ENGINE=InnoDB AUTO_INCREMENT=1048561 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

SHOW CREATE TABLE scores\G
*************************** 1. row ***************************
       Table: scores
Create Table: CREATE TABLE `scores` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `score` int NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2031586 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

検証用のデータとして、usersテーブルには100万件、scoresテーブルには200万件のレコードをINSERTしています。

Lv1. 単一テーブルに対するSELECT（全件取得）

まずは検索条件などを指定せず、最もシンプルなSELECT文に対してEXPLAIN ANALYZEを実行してみましょう。

EXPLAIN ANALYZE SELECT * FROM users;
+---------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                 |
+---------------------------------------------------------------------------------------------------------+
| -> Table scan on users  (cost=101290.81 rows=995849) (actual time=0.046..775.839 rows=1000000 loops=1)  |
+---------------------------------------------------------------------------------------------------------+

結果が取得できたので、これをいくつかのパーツに分解して見ていきましょう。

  • Table scan on users

この部分はusersテーブルをテーブルスキャンで検索したことを表しています。
テーブルスキャンとは、インデックスなどを使用せずにテーブル内の全てのレコードを検索したという意味になります。
非常に遅い検索方法なので、クエリチューニングの必要性が出た場合は改善する有力候補になります。

  • (cost=101290.81 rows=995849)

この部分は推定実行コスト1と戻される行の推定数を表しています。
ここはクエリ実行前の推定値なので、EXPLAINで取得できるものと同じ値になります。

  • (actual time=0.046..775.839 rows=1000000 loops=1)

actual timeは最初の行・全ての行を取得するのに実際にかかった時間（ミリ秒）を表しています。ここで特に重要なのは全ての行を取得するのにかかった時間の方で、実際のコストとみなすことができます。
rowsは実際にクエリで取得した行数となります。この場合は100万件のレコードが取得されたことになります（そのまんま）。
loopsはこのステップのループ数を表しています。この例ではloops=1なのでイメージしづらいかもしれませんので、後ほど説明します。

Lv.2 複数のテーブルをJOINしてSELECT

続いて2つのテーブルをJOINした例を見てみましょう。
usersテーブルとscoresテーブルをJOINしてみます。

EXPLAIN ANALYZE SELECT * FROM users u JOIN scores s ON u.id = s.user_id WHERE u.id < 100;
+----------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=2399258.64 rows=1994337) (actual time=9.622..598.250 rows=198 loops=1)                                |
|     -> Filter: (s.user_id < 100)  (cost=205487.94 rows=1994337) (actual time=9.598..597.860 rows=198 loops=1)                          |
|         -> Table scan on s  (cost=205487.94 rows=1994337) (actual time=9.595..530.336 rows=2000000 loops=1)                            |
|     -> Single-row index lookup on u using PRIMARY (id=s.user_id)  (cost=1.00 rows=1) (actual time=0.002..0.002 rows=1 loops=198)       |
+----------------------------------------------------------------------------------------------------------------------------------------+

Oh、、、頭痛くなってきた
大分複雑になりましたが、1つずつ見ていけば理解できるはずです。

まず、先程と異なり結果が4行になっています。
さらによく見ると単に4行並んでいるだけでなく、ネストしたツリー構造になっています。

これはMySQL 8.0.16から追加された、EXPLAIN FORMAT=TREEという表示形式です。

このツリー構造の読み方についてはMySQLの公式ドキュメントでは見つけられませんでした（ご存じの方、誰か教えて下さいm(_ _)m）が、基本的にはOracleやPostgreSQLの実行計画と同じ読み方ができると思います。

基本的にはツリー構造を見て、次のルール2で読んでいきます。

  • ネストが深い方→浅い方の順に読む
  • 同じ階層にあるものは上から読む

まずツリー構造を整理すると下記のような形式になっています。

A
├─ B
│  └ C
└─ D

この場合はC→B→D→Aの順で実行されています。
つまり、先程の結果は下記のような操作が行われていることを示しています。

  1. Table scan on s ... で、scoresテーブルをテーブルスキャンしている
  2. Filter: (s.user_id < 100) ...で、1の結果をscores.user_id < 100の条件で絞り込みしている
  3. Single-row index lookup on u using PRIMARY ...で、usersテーブルを1行ずつ主キー検索している
    • ここでloops=198となっているので、このステップが198回実行されたことを示しています。ここから、2で取得した行（rows=198）に対して1件ずつ検索が行われたことがわかります。
  4. Nested loop inner join ... で、2, 3の結果をNested Loopして結合している

何となく雰囲気を掴んでいただけたでしょうか。
こちとら、何となくの雰囲気でしか理解しとらんぞい

EXPLAIN ANALYZEの結果を見てクエリを改善しよう

ここで、クエリの実行速度を改善するためにEXPLAIN ANALYZEの結果を再度見てみましょう。

EXPLAIN ANALYZE SELECT * FROM users u JOIN scores s ON u.id = s.user_id WHERE u.id < 100;
+----------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------+
| -> A. Nested loop inner join  (cost=2399258.64 rows=1994337) (actual time=9.622..598.250 rows=198 loops=1)                                |
|     -> B. Filter: (s.user_id < 100)  (cost=205487.94 rows=1994337) (actual time=9.598..597.860 rows=198 loops=1)                          |
|         -> C. Table scan on s  (cost=205487.94 rows=1994337) (actual time=9.595..530.336 rows=2000000 loops=1)                            |
|     -> D. Single-row index lookup on u using PRIMARY (id=s.user_id)  (cost=1.00 rows=1) (actual time=0.002..0.002 rows=1 loops=198)       |
+----------------------------------------------------------------------------------------------------------------------------------------+

※便宜上、各ステップにA, B, C, Dとラベルを付けました。
更に、実行順はC→B→D→Aだったことも思い出しておきましょう。

さて、まずactual timeを見てみると、A, B, Cが500ミリ秒を超えており、Cの実行時点で既に時間がかかっていることがわかると思います。

また、CにTable scanの文言があり、取得した行（rows）が200万件であることからも、scoresテーブルを全件検索しているのが問題だと予想できます。

、、、犯人はCさん、アンタだよ！！

ここで、scores.user_idに外部キー制約をかけていなかったことに気づいたので、追加してみましょう。

ALTER TABLE scores ADD FOREIGN KEY (user_id) REFERENCES users(id);
Query OK, 2000000 rows affected (31.23 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

SHOW CREATE TABLE scores\G
*************************** 1. row ***************************
       Table: scores
Create Table: CREATE TABLE `scores` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `game_id` int NOT NULL,
  `score` int NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `scores_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2031586 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

再度EXPLAIN ANALYZEを実行してみましょう。

EXPLAIN ANALYZE SELECT * FROM users u JOIN scores s ON u.id = s.user_id WHERE u.id < 100;
+-----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=103.23 rows=99) (actual time=0.107..6.201 rows=198 loops=1)                          |
|     -> Filter: (u.id < 100)  (cost=20.94 rows=99) (actual time=0.032..0.273 rows=99 loops=1)                          |
|         -> Index range scan on u using PRIMARY  (cost=20.94 rows=99) (actual time=0.028..0.237 rows=99 loops=1)       |
|     -> Index lookup on s using user_id (user_id=u.id)  (cost=0.73 rows=1) (actual time=0.058..0.059 rows=2 loops=99)  |
+-----------------------------------------------------------------------------------------------------------------------+

actual timeを見ると、最終行取得まで6ミリ秒しかかかっていないことがわかります。改善前が598ミリ秒だったので、約100倍処理速度が改善したことになります。

なお、改善後の結果を軽く記しておくと

  1. Index range scan on u using PRIMARY ... で、usersテーブルを主キーを使用して範囲検索している
    • rows=99と取得している件数が減っていること、actual timeが改善していることに注目しましょう。なお、検索しているテーブルもusersが先になっています。
  2. Filter: (u.id < 100) ...で、1の結果をusers.id < 100の条件で絞り込みしている
  3. Index lookup on s using user_id ...で、scoresテーブルを1行ずつインデックス検索している
    • 先程外部キー制約を追加したことにより、scores.user_idにはインデックスが張られている
  4. Nested loop inner join ... で、2, 3の結果をNested Loopして結合している

となります。

まとめ

  • EXPLAINは実行計画（MySQLがクエリをどの様に実行するかの計画）を見ることができる
  • EXPLAIN ANALYZEは実行計画と、実際にクエリを実行した結果を見ることができる
    • (cost=xxx rows=xxx)の部分は推定実行コストと戻される行の推定数で、これはEXPLAINで取得できるものと同じである
    • (actual time=xxx..xxx rows=xxx loops=xxx)は実際にクエリを実行してかかった時間（ミリ秒）、取得した行数、実行された回数を表している
  • EXPLAIN ANALYZEが複数ステップでネストしている場合、ネストが深いところから順に読んでいく

明日は@polidogさんの記事になります。
PHPについて書いてくれるみたいなので、個人的にもめっちゃ気になっています。
お楽しみに！

参考

MySQLマニュアル

MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.8.2 EXPLAIN ステートメント
MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.8.2 EXPLAIN 出力フォーマット

EXPLAIN ANALYZEについて

Using Explain Analyze in MySQL 8 - Percona Database Performance Blog
MySQL :: WL#4168: Implement EXPLAIN ANALYZE

EXPLAIN FORMAT=TREEの読み方

Georgi Sotirov's Blog: TREE explain format in MySQL 8.0.16
Oracle SQL実行計画の読み方 | コーソルDatabaseエンジニアのBlog
PolarDB · 特性分析 · Explain Format Tree 详解

  1. 注意点として、costの数字は具体的な時間などを表すものではなく、後に出てくる実行時間（ミリ秒）とは直接比較できません。単に値が大きければコストが大きい（≒遅い、DBに負担がかかる）、値が小さければコストが小さい（≒速い、DBへの負担が少ない）という目安の数字だと思ってください。 ↩

  2. 厳密にはこちらの記事で紹介されている通り、ネストが最も深い順に実行されるとは限らないのですが、ここでは説明を単純化しています。もっと厳密に理解したい方は左記の記事を参考にしてみてください。 ↩

66
37
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
66
37

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?