mysql执行计划 Select tables optimized away

Select tables optimized away 

 

官网说明 

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

 

只返回一个值，并且是一个确定的行，我理解只扫描一行记录就能确定这个值。

 

 

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> create table t1(id int primary key auto_increment,name varchar(200));
Query OK, 0 rows affected (0.01 sec)

mysql> alter table t1 add index idx_name(name)
    -> ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> insert into t1 select 1,'a';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select 2,'a';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select 3,'c';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select 4,'d';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select max(id) from t1 where name='d'
    -> ;
+---------+
| max(id) |
+---------+
|       4 |
+---------+
1 row in set (0.00 sec)

mysql> select * from t1 where id=(select max(id) from t1 where name='d');
+----+------+
| id | name |
+----+------+
|  4 | d    |
+----+------+
1 row in set (0.00 sec)

mysql> explain select * from t1 where id=(select max(id) from t1 where name='d');
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
|  1 | PRIMARY     | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL                         |
|  2 | SUBQUERY    | NULL  | NULL       | NULL  | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

 

mysql> explain select min(id) from t1 where name='d'
    -> ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)