mysql中如何查看优化器优化后的执行计划

test>desc items;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| short_desc  | varchar(255) | YES  |     | NULL    |                |
| example     | text         | YES  |     | NULL    |                |
| explanation | text         | YES  |     | NULL    |                |
| additional  | text         | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

test>desc items_links;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| iid    | int(11) | YES  |     | NULL    |       |
| linkid | int(11) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

.test>explain extended select count(*) from items where id in(select id from items_links);

+----+-------------+-------------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------------------------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                                             |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------------------------------+
|  1 | SIMPLE      | items_links | ALL   | NULL          | NULL    | NULL    | NULL |    3 |   100.00 | Start temporary                                                   |
|  1 | SIMPLE      | items       | index | NULL          | PRIMARY | 4       | NULL |    5 |   100.00 | Using index; End temporary; Using join buffer (Block Nested Loop) |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------------------------------+
2 rows in set, 2 warnings (0.01 sec)


test>show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                   |
+-------+------+-----------------------------------------------------------------------------------------------------------+
| Note  | 1276 | Field or reference 'test.items.id' of SELECT #2 was resolved in SELECT #1                                 |
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`items` semi join (`test`.`items_links`) where 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

在使用explain extended查看执行计划后，可以使用show warnings来查看具体实际是怎么执行的，上面的例子中，子查询被转换成了半连接，由于links表中没有id字段，所以其实这个语句只是item自己跟自己的关联，我们在warning的实际的执行计划中也就么有看到关联的条件where后边就是个1，这种语句在oracle中不会执行。