大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说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
