はじめに

自称Webアプリ屋のサカベです。
先週PHP7の記事を執筆して、それを連載化するつもりだったのですが、ちょっと気になるネタが出てきたので割り込みで今回取り上げます。

今まであまり使う機会が無かったのでずっと後回しにしてきたのですが。。

恥ずかしながら。。 実はちゃんと「EXPLAIN」を使ったことが無い!!!

というわけで、ここらでEXPLAINをちゃんと覚えるべく、いろいろ試してみることにしました。

EXPLAINとは

EXPLAIN 」とは、SQL、特にSELECTステートメントの実行プランに関する情報を提供してくれるツールです。
つまりは、実行したSELECTステートメントが、どのINDEXを元に検索を掛けているのか、はたまたちゃんとINDEXが利いているのかどうか。。そうしたことを検証出来るツールです。

検証環境

XAMPPの中に入っているMariaDBを使用しました。

DB構成

検証用のDBの構成は下記のようになっています。

F25b2c08 55d6 43f5 ad6c c71d98888c30

携帯電話のデータみたいな感じです。
顧客情報があって、使用しているプランと、今までの支払い履歴がその顧客に紐付いている、という構成です。

ちなみに、各テーブルにはPRIMARY KEYとしてのid以外、 一切インデックスを貼っていません

users

顧客情報テーブルを想定。
とりあえずテストユーザーを50万人分用意しました。

MariaDB [explain_test]> desc users;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(16) | YES  |     | NULL    |                |
| plan_id | int(11)     | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [explain_test]> select * from users limit 3;
+----+-------+---------+
| id | name  | plan_id |
+----+-------+---------+
|  1 | test1 |       5 |
|  2 | test2 |       1 |
|  3 | test3 |       6 |
+----+-------+---------+
3 rows in set (0.00 sec)

MariaDB [explain_test]> select count(*) from users;
+----------+
| count(*) |
+----------+
|   500000 |
+----------+
1 row in set (0.11 sec)

payments

支払い履歴テーブルを想定。
ユーザー1人につき、2~7件ランダムで支払い情報のテストデータを用意しています。
合計 237万件 になりました。
SELECT COUNTするだけで0.5秒も掛かっているので、明らかに速度落ちてますね。

MariaDB [explain_test]> desc payments;
+---------+------------+------+-----+---------+----------------+
| Field   | Type       | Null | Key | Default | Extra          |
+---------+------------+------+-----+---------+----------------+
| id      | int(11)    | NO   | PRI | NULL    | auto_increment |
| ym      | varchar(6) | YES  |     | NULL    |                |
| user_id | int(11)    | YES  |     | NULL    |                |
| plan_id | int(11)    | YES  |     | NULL    |                |
| fee     | int(11)    | YES  |     | NULL    |                |
+---------+------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

MariaDB [explain_test]> select * from payments limit 3;
+----+--------+---------+---------+------+
| id | ym     | user_id | plan_id | fee  |
+----+--------+---------+---------+------+
|  1 | 201706 |       1 |       5 | 2200 |
|  2 | 201707 |       1 |       5 | 2200 |
|  3 | 201701 |       2 |       1 | 1000 |
+----+--------+---------+---------+------+
3 rows in set (0.00 sec)

MariaDB [explain_test]> select count(*) from payments;
+----------+
| count(*) |
+----------+
|  2372645 |
+----------+
1 row in set (0.53 sec)

plans

料金プランマスタを想定。
こちらは固定で6件。

MariaDB [explain_test]> desc plans;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| plan_name | varchar(16) | YES  |     | NULL    |                |
| fee       | smallint(6) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.09 sec)

MariaDB [explain_test]> select * from plans;
+----+----------------+------+
| id | plan_name      | fee  |
+----+----------------+------+
|  1 | Data Only 3GB  | 1000 |
|  2 | Data Only 6GB  | 1500 |
|  3 | Data Only 10GB | 2500 |
|  4 | Telephone 3GB  | 1700 |
|  5 | Telephone 6GB  | 2200 |
|  6 | Telephone 10GB | 3200 |
+----+----------------+------+
6 rows in set (0.00 sec)

ちなみにテストデータは、テストデータを生成する用のPHPバッチを作ってそれで流し込みました。
https://github.com/saken649/makeTestData

早速EXPLAINしてみる

さて、ひとまずまずはEXPLAINをしてみましょう。
使い方は、測りたいSELECT文の前に「EXPLAIN」と付けるだけ。

MariaDB [explain_test]> explain select * from payments;
+------+-------------+----------+------+---------------+------+---------+------+---------+-------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------+
|    1 | SIMPLE      | payments | ALL  | NULL          | NULL | NULL    | NULL | 2366196 |       |
+------+-------------+----------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.03 sec)

なんかいろいろ書いてあります。1つ1つ見ていきましょう。

id

SELECT文の順番に相当します。
JOINやサブクエリなどで、検索するテーブルが増えると、ここの数字も増えます。
実質的にテーブルごとに振られる番号だと思えば良いでしょう。

select_type

SELECT文の種類を示しています。
結構いろんな種類がありますが、主要なものだけ。。

SIMPLE

単純なSELECT文で、UNIONやサブクエリを使わない場合が該当するので、大多数の場合はこれでしょう。
JOINもここに含まれます。

PRIMARY

外部クエリのことを指します。
サブクエリを用いた際の、外側のクエリのことを指している。。ようです(自信なし。。)

例えば下記のようなSQLをEXPLAINしてみます。

EXPLAIN SELECT
    *
FROM
    users
    INNER JOIN
        payments
        ON users.id = payments.user_id
WHERE
    users.id IN (
        SELECT
            payments.user_id
        FROM
            payments
        WHERE
            payments.fee <= (
                SELECT
                    AVG(plans.fee)
                FROM
                    plans
            )
);

こちらのEXPLAIN結果は下記のようになります。

+------+--------------+-------------+--------+---------------+--------------+---------+------+---------+-------------------------------------------------+
| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref  | rows    | Extra                                           |
+------+--------------+-------------+--------+---------------+--------------+---------+------+---------+-------------------------------------------------+
|    1 | PRIMARY      | users       | ALL    | PRIMARY       | NULL         | NULL    | NULL |  498815 |                                                 |
|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 4       | func |       1 |                                                 |
|    1 | PRIMARY      | payments    | ALL    | NULL          | NULL         | NULL    | NULL | 2366196 | Using where; Using join buffer (flat, BNL join) |
|    2 | MATERIALIZED | payments    | ALL    | NULL          | NULL         | NULL    | NULL | 2366196 | Using where                                     |
|    3 | SUBQUERY     | plans       | ALL    | NULL          | NULL         | NULL    | NULL |       6 |                                                 |
+------+--------------+-------------+--------+---------------+--------------+---------+------+---------+-------------------------------------------------+

PRIMARYと表示されているのは、usersテーブルに対してとpaymentsテーブルに対して表示されています。
一方、サブクエリの中に挟まっているplansテーブルに対してはSUBQUERYと書いてあります。
多重のサブクエリの時に気にする必要がありそうですね。

UNION

UNIONを使用した際の2つ目のステートメントはこれに該当するようです。
ちなみに1つ目に該当するのは先述のPRIMARY

table

検索を掛けているテーブルの名前が表示されます。
場合によっては、<subquery2>のようなテーブル名以外のものが表示されることもあります。

type

検索対象のテーブルに対して、どのようにアクセスしているかを示しているのがここ。
INDEXがちゃんと使えているのかどうか、といった内容はここを確認していきます。

種類 説明
const PRIMARY KEYか、UNIQUEなINDEXで以ってアクセスしている。最速。はっやーい!
eq_ref JOINにおけるconst。const以外では最適らしいです。
ref ユニークではないINDEXでの等価検索。「=」のこと。適切っちゃ適切。
range INDEXによる範囲検索。
ALL フルテーブルスキャン、つまりは、 目次無しで地道に探している 状態。要改善。
index INDEXツリーを見ているものの、ALLと大して変わらない らしい。

結構重要じゃないすかここ。
ここはもう少しちゃんと掘り下げたいですね。

分割商法です!

かなり長くなってきたので、ここらで一旦分割します。別名、分割商法
EXPLAINの項目の続きや、実際にチューニングしてみる、といった内容は次回以降。。。

参考サイト

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8.2 EXPLAIN 出力フォーマット
EXPLAIN - MariaDB Knowledge Base
漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!!

Shere
  • はてなブログ
  • Twitter
  • Facebook
【MySQL】EXPLAINをちゃんと使ってみる(1)基礎知識を知る(前編)

Writer

  • Name

    サカベ ケンイチ

  • Position

    横浜と大洗を往復する自称Webアプリ屋。

  • Profile

    必要あればサーバー構築からバックエンド、ちょっぴりフロントまで担当するWebアプリ屋。 本業はPHPエンジニア。 アプリも手を出そうとC#+Xamarin勉強中。 そして必要あれば曲も作る。なお出番は無い模様。