MySQL字段为varchar类型和int类型索引的使用技巧

逃离我推掉我的手 2022-03-07 10:08 1499阅读 0赞

**MySQL索引使用：字段为varchar类型时，条件要使用' '包起来**

结论：

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

验证过程：

**    建表语句：**

> 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;
> 
> Query OK, 0 rows affected (0.04 sec)

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

> mysql> insert into gyl (\`str\`,\`num\`) values (123123,'12313');  
> Query OK, 1 row affected (0.02 sec)
> 
> mysql>  insert into gyl (\`str\`,\`num\`) select \`str\`,\`num\` from gyl;  
> Query OK, 1 row affected (0.01 sec)  
> Records: 1  Duplicates: 0  Warnings: 0
> 
> 更改数据 update gyl set num=id,str=id

结果：

mysql> select \* from gyl;  
\+----+--------+-------+------+  
| id | str    | num   | obj  |  
\+----+--------+-------+------+  
|  1 | 123123 | 12313 | NULL |  
|  2 | 123123 | 12313 | NULL |  
\+----+--------+-------+------+  
2 rows in set (0.00 sec)

（1）验证：当MySQL中字段为varchar类型时，搜索条件where num=‘111‘ 可以使用索引，where num=111 不可以使用索引

> mysql> explain select \* from gyl where str=123123 limit 1;  
> \+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  
> | id | select\_type | table | partitions | type | possible\_keys | key  | key\_len | ref  | rows | filtered | Extra       |  
> \+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  
> |  1 | SIMPLE      | gyl   | NULL       | ALL  | str\_x         | NULL | NULL    | NULL |    2 |    50.00 | Using where |  
> \+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  
> 1 row in set, 3 warnings (0.00 sec)
> 
> mysql> explain select \* from gyl where str='123123' limit 1;  
> \+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+  
> | id | select\_type | table | partitions | type | possible\_keys | key   | key\_len | ref   | rows | filtered | Extra |  
> \+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+  
> |  1 | SIMPLE      | gyl   | NULL       | ref  | str\_x         | str\_x | 767     | const |    2 |   100.00 | NULL  |  
> \+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+  
> 1 row in set, 1 warning (0.00 sec)

（2）验证：当MySQL中字段为int类型时，搜索条件where num=‘111‘ 与where num=111都可以使用该字段的索引。

> mysql> explain select \* from gyl where num='12313' limit 1;  
> \+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+  
> | id | select\_type | table | partitions | type | possible\_keys | key   | key\_len | ref   | rows | filtered | Extra |  
> \+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+  
> |  1 | SIMPLE      | gyl   | NULL       | ref  | num\_x         | num\_x | 4       | const |    2 |   100.00 | NULL  |  
> \+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+  
> 1 row in set, 1 warning (0.00 sec)
> 
> mysql> explain select \* from gyl where num=12313 limit 1;  
> \+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+  
> | id | select\_type | table | partitions | type | possible\_keys | key   | key\_len | ref   | rows | filtered | Extra |  
> \+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+  
> |  1 | SIMPLE      | gyl   | NULL       | ref  | num\_x         | num\_x | 4       | const |    2 |   100.00 | NULL  |  
> \+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+  
> 1 row in set, 1 warning (0.00 sec)

（3）MySQL对varchar型字段的索引，字段类型不同造成的隐式转换，导致索引失效

MySQL索引使用：字段为varchar类型时，条件要使用”包起来，才能正常走索引   
表user 里的id是BigInt类型，order表里关联到user表的id误建成userId varchar类型了，在ibatis上查询时，直接传入id Long类型，SQL查询非常慢，即使建立了userId索引，于是改成String 类型的userId去查询，SQL立马变的很快了。

通过explain sql可看下SQL是否走了索引，很快对比出来   
原因：字段类型不同造成的隐式转换，导致索引失效  
   
参考：https://blog.csdn.net/wangjianhua\_love/article/details/80607021   
          [http://www.mamicode.com/info-detail-2206023.html][http_www.mamicode.com_info-detail-2206023.html]

[http_www.mamicode.com_info-detail-2206023.html]: http://www.mamicode.com/info-detail-2206023.html

发表评论取消回复

表情：

评论列表（有 0 条评论，1499人围观）

还没有评论，来说两句吧...

相关阅读

相关 Mysql的数据类型和字段类型

在选择了某种数值类型时，应该考虑所要表示的值的范围，只需选择能覆盖要取值的范围的最小类型即可。请注意，可变长类型的最大长度、此类型所需的额外字节数以及占用相同字节数的无符...

绝地灬酷狼/ 2024年04月22日 19:31/ 0 赞/ 90 阅读

相关 MySQL中字段类型char、varchar和text的区别

在MySQL中，char、varchar和text类型的字段都可以用来存储字符类型的数据，char、varchar都可以指定最大的字符长度，但text不可以。它们的存储方式

待我称王封你为后i/ 2023年06月27日 03:55/ 0 赞/ 22 阅读

相关 Oracle由Varchar2字段类型修改为Clob字段类型

1、在oracle中，一单物理表中已存储有数据，则由Varchar2字段转为Clob字段会报错；不能转。 2、可用以下方法转，示例代码如下： --重命名要修改字段

待我称王封你为后i/ 2023年03月13日 06:30/ 0 赞/ 458 阅读

相关 MySQL索引类型总结和使用技巧以及注意事项

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型在数据库表中，对字段建立索引可以大大提高查询速度

╰半夏微凉°/ 2022年08月10日 21:28/ 0 赞/ 179 阅读

相关 MySQL索引类型总结和使用技巧以及注意事项

在数据库表中，对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表：复制代码代码如下: CREATE TABLE mytable( ID INT

Bertha 。/ 2022年06月12日 03:20/ 0 赞/ 174 阅读

相关为什么数据库 phone 字段不能为设为 int 类型？

今天遇到一个小bug。问题：数据库字段中phone 类型为 int 20. 然后将封装入参的 dto 对象中的 phone 字段改为 int.

向右看齐/ 2022年05月29日 11:08/ 0 赞/ 353 阅读

相关 MySQL索引类型总结和使用技巧以及注意事项

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型 ![1001990-20170228232912016-1

墨蓝/ 2022年03月27日 09:58/ 0 赞/ 266 阅读

相关 MySQL字段为varchar类型和int类型索引的使用技巧

MySQL索引使用：字段为varchar类型时，条件要使用' '包起来结论：当MySQL中字段为int类型时，搜索条件where num=‘111‘ 与where nu

逃离我推掉我的手/ 2022年03月07日 10:08/ 0 赞/ 1500 阅读

相关 Oracle中Clob字段类型修改为Varchar2类型实例

\--新建一个字段 alter table KQSERVER add temp VARCHAR2(2000); \--将原来字段数据赋值到新字段 update KQ

╰半夏微凉°/ 2022年02月21日 08:21/ 0 赞/ 964 阅读

相关 mysql varchar转换为int

1.手动转化类型(直接+0) select server_id from cardserver where game_id = 1 order by server_i

怼烎@/ 2021年12月10日 16:15/ 0 赞/ 255 阅读