MySQL字段为varchar类型和int类型索引的使用技巧

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