欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql varchar int 123 走索引吗?

程序员文章站 2024-01-02 16:22:40
结论: 当MySQL中字段为int类型时,搜索条件where num='111' 与where num=111都可以使用该字段的索引。当MySQL中字段为varchar类型时,搜索条件where num='111' 可以使用索引,where num=111 不可以使用索引 验证过程: 建表语句: 1 ......

结论:

当mysql中字段为int类型时,搜索条件where num='111' 与where num=111都可以使用该字段的索引。
当mysql中字段为varchar类型时,搜索条件where num='111' 可以使用索引,where num=111 不可以使用索引

验证过程:

    建表语句:

1
2
3
4
5
6
7
8
9
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;

  向表中使用自复制语句插入数据

            insert into gyl (`str`,`num`)values(123123,'12313');

            insert into gyl (`str`,`num`) select `str`,`num` from gyl;

更改数据 update gyl set num=id,str=id

结果:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
mysql> explain
select * from gyl where str=123123 limit 1;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | simple      | gyl   | all  | str_x         | null | null    | null | 262756 | using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set
mysql> explain select * from gyl where str='123123' limit 1;
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows   | extra       |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
|  1 | simple      | gyl   | ref  | str_x         | str_x | 257     | const | 131378 | using where |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
1 row in set
 
mysql> explain select * from gyl where num='12313' limit 1;;
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows   | extra |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
|  1 | simple      | gyl   | ref  | num_x         | num_x | 4       | const | 131378 |       |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
1 row in set
 
1065 - query was empty
mysql> explain select * from gyl where num=12313 limit 1;
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows   | extra |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
|  1 | simple      | gyl   | ref  | num_x         | num_x | 4       | const | 131378 |       |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+
1 row in set

字段类型不同造成的隐式转换,导致索引失效

上一篇:

下一篇: