今天发现有一条delete语句长时间处于updating状态而执行不完,导致后边的读操作都无法进行。我根据delete的where条件写成select语句,用explain查看执行计划,发现没有用到任何索引(其实相关where条件字段是建立了索引的,而且还是主键),并且被描述为“Impossible WHERE noticed after reading const tables”。+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 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 |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
按我的理解是即便是不可能的结果,mysql在delete时会因为索引中没有这条记录而没有返回结果,但现在的表现显然是正在全表扫描找这条记录。更令我奇怪的是,执行select查询时倒是很快地返回没有这条记录select * FROM `tbl` WHERE id = '-1';
Empty set (0.00 sec)
但delete操作却依然诡异,长时间。| 716353 | root | localhost | db | Query | 275 | updating | delete FROM `tbl` WHERE id = '-1' |
这个问题有没有办法解决呢?
| 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 |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
按我的理解是即便是不可能的结果,mysql在delete时会因为索引中没有这条记录而没有返回结果,但现在的表现显然是正在全表扫描找这条记录。更令我奇怪的是,执行select查询时倒是很快地返回没有这条记录select * FROM `tbl` WHERE id = '-1';
Empty set (0.00 sec)
但delete操作却依然诡异,长时间。| 716353 | root | localhost | db | Query | 275 | updating | delete FROM `tbl` WHERE id = '-1' |
这个问题有没有办法解决呢?
show create table tt
确实不加单引号即可,您太厉害了,谢谢!经过试验,单引号内包括负号的情况都会出现这个问题,如:delete from tb where id='-1'但select * from tb where id='-1' 则无此问题。为什么会出现这种问题呢?我的mysql版本是5.1.52