あなたの知らない(かもしれない)EXPLAIN文のこんな使い方

この記事は MySQL Casual Advent Calendar 2013 の18日目の記事です。こんな記事をAdvent Calendar初日に書いちゃったので今回は別のネタで。
メモ：ENUM型をフラグにするのどうなんだろうと思った件 - rkajiyamaの日記

みんな大好きEXPLAIN文。インデックス利用の有無やファイルソートしないかの確認など、MySQLでの実行計画の確認に使う、便利なあれです。MySQL 5.6.3からは、SELECT文だけではなく、UPDATE文、DELETE文、INSERT文とREPLACE文でも使えるようになっています。
EXPLAIN文の一般的な使い方やアウトプットの見方は漢の記事が役に立つのでそっちを見てください。

ここでは小ネタを2つほど。

SQLの実行計画の確認だけじゃないEXPLAIN文

EXPLAINに続けてテーブル名を付けると、

mysql> EXPLAIN world.City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

DESCRIBE文やDESC文と同じ結果に。それだけ。
「何がうれしいの？」とか聞いちゃいけません。

EXPLAIN文のEXTENDEDオプション

オプティマイザによるクエリの書き換えやサブクエリなどで一時表に結果を生成(materialize)した処理などが含まれるなどの判断結果を、なぜかWARNINGとして出力してくれるのがEXPLAIN文のEXTENDEDオプションです。EXPLAIN EXTENDEDを実行したら、SHOW WARNINGSを続けて実行しましょう。結果にが出ていればクエリの一部の処理は主キーのルックアップで行われているので効率は悪くありませんが、が出ていればmax_heap_sizeが十分かなどの検討が必要です。各出力の説明はリファレンスマニュアルを参照してください。
http://dev.mysql.com/doc/refman/5.6/en/explain-extended.html

mysql> EXPLAIN EXTENDED
    -> SELECT City.Name, City.CountryCode IN
    ->  (SELECT Country.Code FROM Country)
    -> FROM City LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: City
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4188
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: Country
   partitions: NULL
         type: unique_subquery
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: func
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1681
Message: 'EXTENDED' is deprecated and will be removed in a future release.
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `world`.`city`.`Name` AS `Name`,<in_optimizer>(`world`.`city`.`CountryCode`,<exists>(<primary_index_lookup>(<cache>(`world`.`city`.`CountryCode`) in country on PRIMARY))) AS `City.CountryCode IN
 (SELECT Country.Code FROM Country)` from `world`.`city` limit 2
2 rows in set (0.00 sec)

EXPLAIN EXTENDEDを実行すると普通のEXPLAIN文との違いはfiltered列の有無になります。このfiltered列にはデーブルに対して何％ぐらいがWHERE句などで絞り込まれるかの推測値が出力されることになっています。
http://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain_filtered
filtered列の値が100%以外になるようなクエリが書けなかったので、誰か書けたら教えてください。

EXPLAIN EXTENDED文の出力結果のSQL文は、そのまま実行できるようには考慮されていないので注意してください。

ちなみにMySQL 5.7のEXPLAIN文はデフォルトEXTENDEDオプションがついているのと同じ状態となります。

MySQL Casual Advent Calendarのネタとして、MySQL Utilitiesの一つであるシャーディング環境構築ツールのMySQL Fabricの解説を書こうと思っていましたが、記事を書く当日になってlabs版からアルファ版に変わるという想定外の事態があったので内容を差し替えました。

新しくなったMySQL UtilitiesのMac OS X版インストーラ

明日は hrokmimさんが担当です。