MySQL EXPLAIN：Impossible WHERE noticed after re...

最新推荐文章于 2021-12-02 21:17:14 发布

weixin_34218890

最新推荐文章于 2021-12-02 21:17:14 发布

阅读量214

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/lxrm/blog/108309

版权

为什么80%的码农都做不了架构师？>>>

mysql 的 explain命令用于查看执行效果，好久没有来分析sql了（ps:有段时间没写SQL了）无解……还好，有google。

mysql> create table t_william(id int primary key,name char(2))engine=innodb;
Query OK, 0 rows affected (0.13 sec)

/*上两条数据*/

insert into t_william value(1,'ad');
insert into t_william value(2,'a2');

mysql> explain select *from t_willam where id=1;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t_willam | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

对于已存在的记录还正常。换一个不存在的主键id，则奇葩出现了，出现了，

mysql> explain select *from t_willam where id=3; 
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

Impossible WHERE noticed after reading const tables。（据说，这是因为mysql是首先对索引记录进行查询，然后在分析效果。）

附录：

知识回顾：

Explain的type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

参考：

http://wangyuanzju.blog.163.com/blog/static/13029200691114035831/

http://hi.baidu.com/thinkinginlamp/item/88fd66da58ff0ae4795daad3

转载于:https://my.oschina.net/lxrm/blog/108309