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)。
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 がどのようにデータを読み取るのかまでは、自分の理解が足りずに説明できないのですが、ここでは推測を述べてみたい。
この図は 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)
まとめ
コードを読んでも理解できないので、図を書いてこうじゃないの？という推測を述べました。「おいコラ、違うぞ！」という点がありましたらご教授いただけるとありがたいです。
