見出し画像

MySQL で explain してますか？

clown

はじめに

この記事では MySQL での explain ステートメントの使い方と結果の見方について、ここだけでも知っておけというところを説明します。

あなたは MySQL で explain してますか？

MySQL での explain は、クエリがどのように実行されているかを取得して表示してくれるステートメントです。
explain を使うとクエリの状況を可視化できて、どこを改善すべきか判るから、使えた方がワンランク上のエンジニアになれます。

対象者

  • RSDB を使うシステムに関わってて、ユーザから重いって言われてる人

  • DB 担当になったけど、何から手を付けていいかわかんない人

  • MySQL を使っている人（他 DB でも基本はそんなに違わないです）

という方々です。


ざっくり説明

「はじめに」でも書いた通り、explain は、クエリがどのように実行されているかを取得して表示してくれるステートメントです。
要約しちゃうと、クエリがどのインデックスを使って実行されているかを表示してくれます。使えるようになって損をすることはありません。

実行方法

例を挙げます。

EXPLAIN SELECT * FROM `sumple_users` WHERE `username` = 'clown1';

ターミナルや MySQL Workbench などを開いて、クエリを実行可能な状態にしたら、どのように実行されているかを知りたいクエリの先頭に "explain" の文字列を追加して実行します。
要は、クエリの先頭に "explain" という文字を付け加えるだけ。簡単ですね。

準備

では実際に使ってみま…
なんですが、clown のローカル環境にテーブルがないので、テスト用のテーブルを作ります

CREATE TABLE `sumple_users` (
    `id`         int(11)      NOT NULL AUTO_INCREMENT
   ,`username`   varchar(64)  NOT NULL DEFAULT ''
   ,`email`      varchar(255) NOT NULL
   ,`password`   varchar(255) NOT NULL
   ,`login_hash` varchar(255) NOT NULL DEFAULT ''
   ,`created_at` timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   ,`updated_at` timestamp    NOT NULL DEFAULT '0000-00-00 00:00:00'
   ,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

ユーザ情報を格納する簡素なテーブルを作りました。
ここには、こんなデータが入ってます。

+-------+------------+----------------------+----------+------------+---------------------+---------------------+
| id    | username   | email                | password | login_hash | created_at          | updated_at          |
+-------+------------+----------------------+----------+------------+---------------------+---------------------+
|     1 | clown1     | clown1@email.com     | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|     2 | clown2     | clown2@email.com     | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|     3 | clown3     | clown3@email.com     | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|     4 | clown4     | clown4@email.com     | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|     5 | clown5     | clown5@email.com     | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |

～
中略
～

|  9996 | clown9996  | clown9996@email.com  | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|  9997 | clown9997  | clown9997@email.com  | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|  9998 | clown9998  | clown9998@email.com  | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
|  9999 | clown9999  | clown9999@email.com  | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
| 10000 | clown10000 | clown10000@email.com | abc      | def        | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
+-------+------------+----------------------+----------+------------+---------------------+---------------------+
10000 rows in set (0.01 sec)

実際は全10,000件のレコードが入っています。先頭と末尾のみ抽出しました。

実験

基本

では、今度こそ使ってみましょう。
さきほどのテーブルから特定のレコードを取得するクエリを書きます。

SELECT * FROM `sumple_users` WHERE `username` = 'clown1';

これに explain を付与すればいいので、

EXPLAIN SELECT * FROM `sumple_users` WHERE `username` = 'clown1';

こんな感じになります。

基本の実行結果

+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sumple_users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9949 |    10.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

見るべきところ

  • 左からみっつめのカラム： "table"

このクエリでどのテーブルが対象となっているのか。explain の結果、どのテーブルを確認したのか。といったことがわかります。


  • 左からふたつめのカラム："select_type"

このクエリでどういう検索をしたのかを知らせてくれます。ここでは、

SIMPLE

と書いてあって、これは、とても単純な select をしたことを表しています。サブクエリとかありませんから、単純に select しただけです。
一覧を置いておきますから、都度読んで調べてみてください。改めて記事にしますが、今回は一覧を見るだけで大丈夫です。

出典


  • 左からいつつめのカラム："type"

このクエリでテーブルを検索したとき、どうやって検索対象のレコードを探したかがわかります。ここでは、

ALL

と書いてあって、フルテーブルスキャンを行って目的のレコードを探したことがわかります。
一覧を置いておきますから、都度読んで調べてみてください。改めて記事にしますが、今回は一覧を見るだけで大丈夫です。


  • 左からななつめのカラム："key"

対象テーブルで指定のレコードを探すときに使ったインデックスがわかります。
ここでは何も書かれていないので、対象のレコードを探す際にインデックスは使わなくていいと MySQL が判断したと読むことができます。

とにかく要対応な結果とは

type = ALL はテーブルをフルスキャンしてたり、フルインデックススキャンをしています。
すごく大雑把に言えば、テーブルの全てをスキャンしていてパフォーマンスに影響があるから、すぐに対策が必要です。

データ量、特にレコード数が少なければそれほど大した問題ではありませんが、レコード数が多いと即パフォーマンスが悪化します。
DB チューニングやパフォーマンス改善の初手にこれを確認して、適切にインデックスを貼るだけでも大幅なパフォーマンス改善になるケースが多いです。だから、まずは "type = ALL" の結果が無いかだけでも確認してみてください。

今回はここまで。

支援のお願い

素材引用元

アイコン

見出し


いいなと思ったら応援しよう！