varchar int 查询 到底什么情况下走索引?

varchar int 查询 到底什么情况下走索引?一个字符类型的、一个int类型的,查询的时候到底会不会走索引,其实很多工作了几年的开发人员有时也会晕,下面就用具体事例来测试一下。 1. 准备工作 先准备2张表,以备后续测试使用。 表1:创建表tes

varchar  int 查询 到底什么情况下走索引?

 

一个字符类型的、一个int类型的,查询的时候到底会不会走索引,其实很多工作了几年的开发人员有时也会晕,下面就用具体事例来测试一下。

1.  准备工作

先准备2张表,以备后续测试使用。

表1:创建表test1,总共3列,其中id 是主键(int),c_no 为int型,且有索引,c_2为普通字段

/*创建表test1 */
create table  test1(id int primary key,c_no  int ,c_2 varchar(1),key c_no(c_no));

/* 插入一些测试数据 */
insert  into test1 values(1,1,"0"),(2,2,"1"),(3,4,"1"),(4,6,"0"),(5,7,""1),(6,11,"2"),(7,5,"3"),(8,100,"0"),(9,30,"1"),(10,50,"0");

代码100分

表2: 创建表test1,总共3列,其中id 是主键(int),c_no 为字符型,且有索引,c_2为普通字段

代码100分/* 创建test2 */
create table  test2(id int primary key  auto_increment,c_no  varchar(11) ,c2 varchar(2),key c_no(c_no));

/* 插入一些测试数据 */
 insert  into test2 values(1,"5","1"),(4,"100","0"),(3,"30","1"),(10,"500","0"),(11,"20","0"),(12,"20a","0"),(15,"020b","1");

两张表的差异是c_no的字段类型不同。

 

2.    等值查询测试

2.1  测试test1

test1.c_no字段为int类型,下面分别用整型和字符串进行比较,查看是否走索引。对应的执行计划如下:

mysql> explain  select *  from test1 where c_no="100";
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test1 | NULL       | ref  | c_no          | c_no | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain  select *  from test1 where c_no=100;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test1 | NULL       | ref  | c_no          | c_no | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可见,两种方式均走索引了,且走的是c_no的索引,类型为ref为const(常量的等值查询),扫行数为1

也就是说当表中的字段类型为整型时,无论查询用字符串类型的数字还是int类型的数字均能走索引。其中用int类型的值查询能走索引可以容易理解,那么,字符型的为什么能走? 其实这里的字符类型做了隐式转化,上例中就相当于

代码100分mysql> explain  select *  from test1 where c_no=CAST("100" as UNSIGNED); +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test1 | NULL       | ref  | c_no          | c_no | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

 

2.2  测试 test2 表

以同样的方式测试一下test2的查询情况

先测试正常情况下字符型与字符型比较,结果可想而知,可以走索引,如下:

mysql> explain  select *  from test2 where c_no="100";
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test2 | NULL       | ref  | c_no          | c_no | 47      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

而,如果是整型再查呢?结果如下(很遗憾,不能走索引了)

mysql> explain  select *  from test2 where c_no=100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | ALL  | c_no          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

也就是说,表中字段为字符类型的时候,查询的值为整型时,无法走索引了

那这句相当于如下情况:

mysql> explain  select *  from test2 where cast(c_no  as  unsigned)=100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

也就是c_no做了隐式转化。因为如果是100做了影视转化,那么结果应该是可以走索引,例如:

mysql> explain  select *  from test2 where c_no=cast(100 as char);
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test2 | NULL       | ref  | c_no          | c_no | 47      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

由此,我们也应证了如果字段做了函数计算后,该列上即使有索引也无法使用(MySQL8.0之前的版本)。

2.3  进一步测试

其实针对test2表 还可以测试一点,进一步证明是c_no字段做了隐式转化,例如:

mysql> select  * from test2 where c_no=20;
+----+------+------+
| id | c_no | c2   |
+----+------+------+
| 11 | 20   | 0    |
| 12 | 20a  | 0    |
| 15 | 020b | 1    |
+----+------+------+
3 rows in set, 2 warnings (0.00 sec)

另外,看到了2个警告,内容如下:

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: "20a"  |
| Warning | 1292 | Truncated incorrect DOUBLE value: "020b" |
+---------+------+------------------------------------------+
2 rows in set (0.00 sec)

更加证明了转化为数字型(预转为double)

 

3.  小结

通过上面的简单测试,即可发现如下结论:

  • 当表中的字段类型为整型时,无论查询用字符串类型的数字还是int类型的数字均能走索引;
  • 表中字段为字符类型的时候,查询的值为整型时,无法走索引;
  • 如果字段做了函数计算后,该列上即使有索引也无法使用(MySQL8.0之前的版本)

因此开发同学在写SQL的时候要注意SQL的写法,缺少一个单引号可能导致很大的性能差异。

 

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/9222.html

(0)
上一篇 2023-02-13 09:30
下一篇 2023-02-13

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注