varcharint查询到底什么情况下走索引？

Z时代
2024-01-10
分类：综合

database

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

1. 准备工作

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

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

/*创建表test1 */
createtable  test1(id intprimarykey,c_no  int ,c_2 varchar(1),key c_no(c_no));
/* 插入一些测试数据 */
insertinto 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");

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

/* 创建test2 */
createtable  test2(id intprimarykey  auto_increment,c_no  varchar(11) ,c2 varchar(2),key c_no(c_no));
/* 插入一些测试数据 */
insertinto 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 inset, 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 inset, 1 warning (0.00 sec)

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

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

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 inset, 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 inset, 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 inset, 3 warnings (0.00 sec)

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

那这句相当于如下情况：

mysql> explain  select*from test2 wherecast(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 inset, 1 warning (0.00 sec)

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

mysql> explain  select*from test2 where c_no=cast(100aschar);
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 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 inset, 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 inset, 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 inset (0.00 sec)

更加证明了转化为数字型（预转为double）

3. 小结

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

当表中的字段类型为整型时，无论查询用字符串类型的数字还是int类型的数字均能走索引；

表中字段为字符类型的时候，查询的值为整型时，无法走索引；

如果字段做了函数计算后，该列上即使有索引也无法使用（MySQL8.0之前的版本）

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

以上是 varcharint查询到底什么情况下走索引？的全部内容，来源链接： utcz.com/z/532962.html