MySQL为什么varchar字段用数字查无法命中索引，而int字段用字符串查却能命中?

你在学习数据库相关的知识吗？本文《MySQL为什么varchar字段用数字查无法命中索引，而int字段用字符串查却能命中?》，主要介绍的内容就涉及到MySQL、索引、数据库，如果你想提升自己的开发能力，就不要错过这篇文章，大家要知道编程理论基础和实战操作都是不可或缺的哦！

字符串字段误使用数字进行查询，会导致隐式类型转换，无法命中索引的坑我相信大多数小伙伴都踩过。
特别是当字段中存的大多数数据都是数字时，很容易先入为主地认为字段是

mysql> SELECT VERSION();
+---------------------+
| VERSION()           |
+---------------------+
| 5.7.18-20170830-log |
+---------------------+

mysql> DESC `file`;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(11)             | NO   | PRI | NULL    | auto_increment |
| fs_id    | varchar(20)         | NO   | MUL | NULL    |                |
| filename | varchar(255)        | NO   |     | NULL    |                |
| shareid  | bigint(20) unsigned | NO   | MUL | NULL    |                |
| uk       | bigint(20) unsigned | NO   |     | NULL    |                |
| pid      | varchar(32)         | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+

mysql> SELECT COUNT(*) FROM `file`;
+----------+
| COUNT(*) |
+----------+
|  5416697 |
+----------+

varchar 字段用数字进行查询

数据表

mysql> SELECT * FROM `file` WHERE `fs_id`='635341798980956';
+---------+-----------------+-------------+------------+------------+---------+
| id      | fs_id           | filename    | shareid    | uk         | pid     |
+---------+-----------------+-------------+------------+------------+---------+
| 1043170 | 635341798980956 | ⑮MySQL高级 | 3181065465 | 3959617630 | o6RlSp0 |
+---------+-----------------+-------------+------------+------------+---------+
1 row in set (0.07 sec)

mysql> EXPLAIN SELECT * FROM `file` WHERE `fs_id`='635341798980956';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | file  | NULL       | ref  | idx_fs_id     | idx_fs_id | 62      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+

然而，当使用数字进行查询时，耗时 7.04 秒。
通过 EXPLAIN 命令查看执行计划，发现查询时进行了全表扫描，并未使用到索引。

mysql> SELECT * FROM `file` WHERE `fs_id`=635341798980956;
+---------+-----------------+-------------+------------+------------+---------+
| id      | fs_id           | filename    | shareid    | uk         | pid     |
+---------+-----------------+-------------+------------+------------+---------+
| 1043170 | 635341798980956 | ⑮MySQL高级 | 3181065465 | 3959617630 | o6RlSp0 |
+---------+-----------------+-------------+------------+------------+---------+
1 row in set (7.04 sec)

mysql> EXPLAIN SELECT * FROM `file` WHERE `fs_id`=635341798980956;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | file  | NULL       | ALL  | idx_fs_id     | NULL | NULL    | NULL | 4878670 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

SELECT * FROM `file` WHERE `fs_id`=635341798980956;

对于MySQL优化器来说，这个语句相当于将

SELECT * FROM `file` WHERE CAST(`fs_id` AS signed INT)=635341798980956;

众所周知，如果查询时对索引字段进行函数操作，查询过程将无法使用索引。

2. 为什么触发隐式类型转换，查询数据时就需要全表扫描？

对于 InnoDB 的 B+树索引结构，相信大多数小伙伴都有一定的了解。

示例有下面一组数据：

SELECT * FROM `file` WHERE `shareid`='3181065465'

对于MySQL优化器来说，这个语句相当于将输入参数

SELECT * FROM `file` WHERE `shareid`=CAST('3181065465' AS signed INT);

而对等号后面的输入参数进行函数操作，是不影响字段

shareid

总结

MySQL查询中，当字符串和数字做比较时，会触发隐式类型转换，转换规则是将字符串转换成数字。

当索引字段是字符串类型，输入参数是数值类型时，会将字段转换成数值类型再进行查找，也就是对索引字段做了函数操作，破坏了索引的有序性，因此无法使用索引。
当索引字段是数值类型，输参数是字符串类型时，会将输入参数转换成数值类型再进行查找，对等号后面的输入参数进行函数操作，并不影响索引字段的有序性，因此可以使用索引。

本篇关于《MySQL为什么varchar字段用数字查无法命中索引，而int字段用字符串查却能命中?》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于数据库的相关知识，请关注golang学习网公众号！