読者です 読者をやめる 読者になる 読者になる

MySQL の Select tables optimized away とは何か?

MySQL で EXPLAIN を使って実行計画を調べていると Extra フィールドに「Select tables optimized away」というメッセージが表示された。これはなんだろうか?

mysql> explain select max(id) from products ;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

リファレンスマニュアル

最近日本語化された MySQL 5.6 のリファレンスマニュアルを見てみよう。

Select tables optimized away

クエリーにはすべてインデックスを使用して解決された集約関数 (MIN()、MAX())、または COUNT(*) のみが含まれていますが、GROUP BY 句は含まれていませんでした。オプティマイザは 1 行のみを返すべきであると判断しました。 https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html

なるほどです。

というか、これがすべてなのでこれ以上書く必要がない気もしてきましたが続けます。

オプティマイザによって最適化された

このクエリはインデックスを使って解決できることがわかった。オプティマイザはこれ以上処理を続けない。 オプティマイザのコードを読むと optimized away になったときは後続の処理をすっ飛ばしてメソッドの末尾まで行ってしまうことがわかる(goto setup_subq_exit)。

sql/sql_optimizer.cc

 278   /*
 279      Try to optimize count(*), min() and max() to const fields if
 280      there is implicit grouping (aggregate functions but no
 281      group_list). In this case, the result set shall only contain one
 282      row.
 283   */
 284   if (tables_list && implicit_grouping)
 285   {
 286     int res;
 287     /*
 288       opt_sum_query() returns HA_ERR_KEY_NOT_FOUND if no rows match
 289       to the WHERE conditions,
 290       or 1 if all items were resolved (optimized away),
 291       or 0, or an error number HA_ERR_...
 292
 293       If all items were resolved by opt_sum_query, there is no need to
 294       open any tables.
 295     */
 296     if ((res=opt_sum_query(thd, select_lex->leaf_tables, all_fields, conds)))
 297     {
 298       best_rowcount= 0;
 299       if (res == HA_ERR_KEY_NOT_FOUND)
 300       {
 301         DBUG_PRINT("info",("No matching min/max row"));
 302         zero_result_cause= "No matching min/max row";
 303         tables= 0;
 304         primary_tables= 0;
 305         goto setup_subq_exit;
 306       }
 307       if (res > 1)
 308       {
 309         error= res;
 310         DBUG_PRINT("error",("Error from opt_sum_query"));
 311         DBUG_RETURN(1);
 312       }
 313       if (res < 0)
 314       {
 315         DBUG_PRINT("info",("No matching min/max row"));
 316         zero_result_cause= "No matching min/max row";
 317         tables= 0;
 318         primary_tables= 0;
 319         goto setup_subq_exit;
 320       }
 321       DBUG_PRINT("info",("Select tables optimized away"));
 322       zero_result_cause= "Select tables optimized away";
 323       tables_list= 0;                           // All tables resolved
 324       best_rowcount= 1;
 325       const_tables= primary_tables;
 326       /*
 327         Extract all table-independent conditions and replace the WHERE
 328         clause with them. All other conditions were computed by opt_sum_query
 329         and the MIN/MAX/COUNT function(s) have been replaced by constants,
 330         so there is no need to compute the whole WHERE clause again.
 331         Notice that make_cond_for_table() will always succeed to remove all
 332         computed conditions, because opt_sum_query() is applicable only to
 333         conjunctions.
 334         Preserve conditions for EXPLAIN.
 335       */
 336       if (conds && !(thd->lex->describe & DESCRIBE_EXTENDED))
 337       {
 338         Item *table_independent_conds=
 339           make_cond_for_table(conds, PSEUDO_TABLE_BITS, 0, 0);
 340         DBUG_EXECUTE("where",
 341                      print_where(table_independent_conds,
 342                                  "where after opt_sum_query()",
 343                                  QT_ORDINARY););
 344         conds= table_independent_conds;
 345       }
 346       goto setup_subq_exit;
 347     }
 348   }

そのあと MySQL がどのようにデータを読み取るのかまでは、自分の理解が足りずに説明できないのですが、ここでは推測を述べてみたい。

f:id:takatoshiono:20150613011620p:plain

この図は B-Tree インデックスを描いたつもりなのだが、以下のことがわかる。

  • 最大値 MAX(id) を得るときはルートノードだけ読めばよい
  • 最小値 MIN(id) を得るときはブランチノードまで読めばよい

つまりリーフノードにあるデータまで読みに行く必要がない。速い。ということなのでは?(あくまで推測です)。

COUNT(*) について

COUNT(*) が optimized away になるのは MyISAM だけだ。MyISAM はレコード件数を持っているからすぐに返せるけど、InnoDB は持っていないので数える必要がある。

InnoDB の場合

mysql> explain select count(*) from products ;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | products | index | NULL          | PRIMARY | 4       | NULL |    4 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

MyISAM の場合

mysql> create table products_myisam like products ;
Query OK, 0 rows affected (0.11 sec)

mysql> alter table products_myisam engine=myisam ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count(*) from products_myisam ;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

まとめ

コードを読んでも理解できないので、図を書いてこうじゃないの?という推測を述べました。「おいコラ、違うぞ!」という点がありましたらご教授いただけるとありがたいです。