optimized mysql_mysql中的Select tables optimized away

mysql中的Select tables optimized away

mysql> explain select * from bet_match_result where lottery_no='111201' and lottery_type='WDL' and serial_no=(select max(serial_no) from bet_match_result where lottery_no='111201' and lottery_type='WDL' );

+----+-------------+------------------+------+---------------+---------+---------+-------------------+------+------------------------------+

| id | select_type | table            | type | possible_keys | key     | key_len | ref               | rows | Extra                        |

+----+-------------+------------------+------+---------------+---------+---------+-------------------+------+------------------------------+

|  1 | PRIMARY     | bet_match_result | ref  | PRIMARY,ln_lt | PRIMARY | 126     | const,const,const |    1 | Using where                  |

|  2 | SUBQUERY    | NULL             | NULL | NULL          | NULL    | NULL    | NULL              | NULL | Select tables optimized away |

+----+-------------+------------------+------+---------------+---------+---------+-------------------+------+------------------------------+

这个输出的结果里,Extra列输出了"Select tables optimized away"语句。第个很明显，myisam已经保存了记录的总数，直接返回结果，，而innodb还需要全表扫描。这个在MySQL的手册里面没有任何提及，不过看其他各列的数据大概能猜到意思：SELECT操作已经优化到不能再优化了(MySQL根本没有遍历表或索引就返回数据了)。在MySQL官方站点翻到两段相关的描述，印证了上述观点，原文如下：For explains on simple count queries (i.e. explain select count(*) from people) the extra section will read "Select tables optimized away." This is due to the fact that MySQL can read the result directly from the table internals and therefore does not need to perform the select.官方地址如下：http://mysql2.mirrors-r-us.net/doc/refman/5.0/en/explain.html