准备语句:
DROP TABLE ix_test; CREATE TABLE ix_test (id_1 varchar(20) NOT NULL, PRIMARY KEY(id_1)); INSERT INTO ix_test VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
mysql会将数字在×××和字符串之间自动转换!
这样下面两条语句的结果是一样的:
SELECT * FROM ix_test WHERE id_1=1;
SELECT * FROM ix_test WHERE id_1='1';
但是在索引使用情况方面,结果就完全不一样了!第一条不使用索引,第二条使用索引!
两条语句,第一条使用了索引,但是扫描了全表,第二条直接索引到数据,只需要读取一行!
mysql> explain select * from ix_test where id_1=1; +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | ix_test | index | PRIMARY | PRIMARY | 302 | NULL | 11 | Using where; Using index | +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select * from ix_test where id_1='1'; +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | ix_test | const | PRIMARY | PRIMARY | 302 | const | 1 | Using index | +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.01 sec)
但是如果将id_1字段变为×××,后面用×××或者是字符串去匹配都可以使用索引,而且索引直接命中!
所以,多么坑爹的mysql sql优化器,多么痛的领悟!
得出结论,对于where后面字段类型为字符串的数字,如果用×××去匹配(就是不用引号引上数字),则不能由索引直接命中,需要全部扫描。
特别需要注意!