如果在 IN 语句中用到 NULL 条件, 会导致全表扫描:
CREATE TABLE foo ( a INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (a) );
来看看2次查询的区别:
mysql> EXPLAIN * FROM foo WHERE a IN (160000, 160001, 160002)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: foo type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 3 Extra: Using where 1 row in set (0.06 sec) mysql> EXPLAIN SELECT * FROM foo WHERE a IN (NULL, 160000, 160001, 160002)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: foo type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 327680 Extra: Using where 1 row in set (0.00 sec)
再加上之前其他同学测试出来的问题:
MySQL 5.1 中 Innodb 的事务完整性Bug,看来mysql5.1还是不太靠谱啊,如果不是迫不得已,还是少用吧 :)
本文转自叶金荣51CTO博客,原文链接:http://blog.51cto.com/imysql/308916,如需转载请自行联系原作者