前回までのあらすじ

自称Webアプリ屋のサカベです。
前回、MySQLのEXPLAINについて執筆し始めたところ、長くなってしまったので分割商法したのでした。
【MySQL】EXPLAINをちゃんと使ってみる(1)基礎知識を知る(前編)

typeまでは前回までで説明済みなので、今回はpossible_keysから再開します。

possible_keys

MySQLがそのSQL文が発行された際に、使用出来うるINDEXの候補のことです。
あくまでも「候補」であり、実際に使われたものとイコールではないことに注意です。
次のkeysで詳しく述べます。

key

possible_keysがあくまでも「候補」であれば、こちらは実際に使ったINDEXが表示されます。
何もINDEXを使用せず、フルテーブルスキャンが掛かっている場合、NULLが表示されます。
possible_keysに出ているのに、使ってくれないケースもあります。
前回も使ったSQLをまた例に挙げてみたいと思います。

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 |                                                 |
+------+--------------+-------------+--------+---------------+--------------+---------+------+---------+-------------------------------------------------+

このEXPLAIN結果のうち、usersテーブルへのSELECTに関係するものだけ抜き出してみます。

+------+--------------+-------------+--------+---------------+--------------+---------+------+---------+-------------------------------------------------+
| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref  | rows    | Extra                                           |
+------+--------------+-------------+--------+---------------+--------------+---------+------+---------+-------------------------------------------------+
|    1 | PRIMARY      | users       | ALL    | PRIMARY       | NULL         | NULL    | NULL |  498815 |                                                 |
+------+--------------+-------------+--------+---------------+--------------+---------+------+---------+-------------------------------------------------+

possible_keysにはPRIMARYと候補が挙がっているにも関わらず、keyNULL、つまり実際発行されたSQLとしては、何もINDEXが効いていなかった、ということになります。
こういうこともあるようです。

というかこれINDEX効いてなかったんや。。。

INDEXが効いてないワケ

ちなみにPRIMARY KEYが効いていても良さそうなものですが、なぜ効いていないのでしょうね。
「MySQL初級者を脱するために勉強してること -INDEX編- - Qiita」では、公式の引用としてこのような記述がありました。

MySQL では利用可能な場合でもインデックスが使用されない場合があることに注意してください。この一例として、インデックスの使用によって、MySQL がテーブルの 30% を超えるレコードにアクセスする必要が生じる場合が挙げられます(この場合は、必要なシークが大幅に減少するため、テーブルスキャンのほうが高速になる可能性が高くなります)。

ほー、と思って出典元であるところの公式マニュアルを見に行ったのですが、なんと今はこうなっているようです。

オプティマイザがテーブルスキャンを使用する方が効率的であると判断しないかぎり、各テーブルインデックスがクエリーされ、最適なインデックスが使用されます。かつて、スキャンは、最適なインデックスがテーブルの 30% 超にまたがっているかどうかに基づいて使用されていましたが、固定のパーセンテージによって、インデックスを使用するか、スキャンを使用するかの選択が決定されなくなりました。現在のオプティマイザは複雑になり、テーブルサイズ、行数、I/O ブロックサイズなどの追加の要因に基づいて推定します。

引用元: MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.1.2 MySQL の WHERE 句の最適化の方法

ということはレコード数に関係なく、MySQLが様々な要員から判断していると。。なおのこと謎。

ただこれはあくまでも、MySQL5.6において、なので今回検証環境として使用しているMariaDBにおいてもこうなのかは分かりません。
MariaDBの公式ドキュメントにもそういう記述無いのか探しているのですが、今のところ見つけられず。。。
これは引き続き調査する必要がありそうです。。

key_len

MySQLが実際に使用したキーの長さを示していて、この長さが短いほど高速になるようです。
つまり、INDEXが効いていたとしても、ここの長さが長いようならまだ改善の余地あり、ということになるようです。

ref

対象テーブルから行を選択するために、keyのカラムに対して、何の値、もしくはカラムを充てがっているかを表示しています。

種類 説明
NULL 比較されているものが特に無い。。で合っているのか?引き続き要調査ですね。
const 定数。固定の値が充てがわれている
func 関数の結果が充てがわれている

rows

そのSELECT文の結果を取得するために、MySQLが見ないといけないと考えた件数です。
要は最低◯◯件見ないとアカンよね、という見積もりです。
下記の結果を見て頂ければ一目瞭然かと。

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.00 sec)

MariaDB [explain_test]> explain select * from payments where id = 1000;
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | payments | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

1つ目のSQL文は全件取得なので、当然ながらrowsの数も多くなります。
逆に、2つ目は`PRIMARY KEYで条件指定しているので、rowsも1で済んでます。

Extra

ここには、SQL文に関する追加情報が表示されます。
ここは、調べれば調べるほど重いところなので、また分割商法とさせてください。。

分割商法再び、です!

2回に渡って、EXPLAINで表示される内容について、ひとまずざっと触れることは出来たかと思います。
まだ先送りした内容はあれど。。
次回以降は、Extraの内容や、実際にINDEXを着脱してみて、結果がどう変わるのか、といったことにまで触れてみたいと思います。

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

Writer

  • Name

    サカベ ケンイチ

  • Position

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

  • Profile

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