MySQL索引使用:字段为varchar类型时,条件要使用' '包起来
结论:
当MySQL中字段为int类型时,搜索条件where num=‘111‘ 与where num=111都可以使用该字段的索引。
当MySQL中字段为varchar类型时,搜索条件where num=‘111‘ 可以使用索引,where num=111 不可以使用索引
验证过程:
建表语句:
CREATE TABLE `gyl`(
`id` INT(11) NOT NULL AUTO_INCREMENT ,
`str` VARCHAR(255) NOT NULL ,
`num` INT(11) NOT NULL DEFAULT '0' ,
`obj` VARCHAR(255) DEFAULT NULL ,
PRIMARY KEY(`id`) ,
KEY `str_x`(`str`) ,
KEY `num_x`(`num`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;Query OK, 0 rows affected (0.04 sec)
向表中使用自复制语句插入数据
mysql> insert into gyl (`str`,`num`) values (123123,'12313');
Query OK, 1 row affected (0.02 sec)mysql> insert into gyl (`str`,`num`) select `str`,`num` from gyl;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0更改数据 update gyl set num=id,str=id
结果:
mysql> select * from gyl;
+----+--------+-------+------+
| id | str | num | obj |
+----+--------+-------+------+
| 1 | 123123 | 12313 | NULL |
| 2 | 123123 | 12313 | NULL |
+----+--------+-------+------+
2 rows in set (0.00 sec)
(1)验证:当MySQL中字段为varchar类型时,搜索条件where num=‘111‘ 可以使用索引,where num=111 不可以使用索引
mysql> explain select * from gyl where str=123123 limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | gyl | NULL | ALL | str_x | NULL | NULL | NULL | 2 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)mysql> explain select * from gyl where str='123123' limit 1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | gyl | NULL | ref | str_x | str_x | 767 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
(2)验证:当MySQL中字段为int类型时,搜索条件where num=‘111‘ 与where num=111都可以使用该字段的索引。
mysql> explain select * from gyl where num='12313' limit 1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | gyl | NULL | ref | num_x | num_x | 4 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql> explain select * from gyl where num=12313 limit 1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | gyl | NULL | ref | num_x | num_x | 4 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
(3)MySQL对varchar型字段的索引,字段类型不同造成的隐式转换,导致索引失效
MySQL索引使用:字段为varchar类型时,条件要使用”包起来,才能正常走索引
表user 里的id是BigInt类型,order表里关联到user表的id误建成userId varchar类型了,在ibatis上查询时,直接传入id Long类型,SQL查询非常慢,即使建立了userId索引,于是改成String 类型的userId去查询,SQL立马变的很快了。
通过explain sql可看下SQL是否走了索引,很快对比出来
原因:字段类型不同造成的隐式转换,导致索引失效
参考:https://blog.csdn.net/wangjianhua_love/article/details/80607021
http://www.mamicode.com/info-detail-2206023.html