MySQL EXPLAIN:Impossible WHERE noticed after re...

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值