mysql explain中的 “Select tables optimized away”

標籤：儲存   where   extra   測試   mirror   ssi   man   from   pos   

mysql explain中的 “Select tables optimized away”

http://blog.chinaunix.net/uid-10449864-id-2956845.html
2009年

 

今天在做SQL語句最佳化的時候，在explain的時候，有這樣一個提示：

mysql> explain SELECT max( up_start ) AS up_start FROM test WHERE up_start > ‘2008-01-19 00:00:00‘ and up_start < ‘2008-01-19 23:59:59‘;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 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)

 

我們還可以做這樣一個測試：
用一個innodb表和一個myisam表進行select count(*)測試:

myisam表測試
mysql> explain select count(*) from 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)

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

 

這2個輸出的結果裡,Extra列輸出了"Select tables optimized away"語句。
第2個很明顯，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

mysql explain中的 “Select tables optimized away”