MySql整型索引和字符串索引失效或隐式转换问题

悠悠 2021-12-10 06:23 265阅读 0赞

## 问题概述 ##

今天在上班时，DBA突然找出来一段sql，表示该sql存在隐式转换，不走索引。经过我们的查看后，发现是类型varchar的字段， 我们使用条件传入了数值型的值，由于担心违反保密协议，在此就不贴图了，由我重现一下类似情况给大家看一下。

## 问题重现 ##

首先我们先创建一张用户表test\_user，其中USER\_ID为了效果我们设置为varchar类型且加上唯一索引。

CREATE TABLE test_user (
      ID int(11) NOT NULL AUTO_INCREMENT,
      USER_ID varchar(11) DEFAULT NULL COMMENT '用户账号',
      USER_NAME varchar(255) DEFAULT NULL COMMENT '用户名',
      AGE int(5) DEFAULT NULL COMMENT '年龄',
      COMMENT varchar(255) DEFAULT NULL COMMENT '简介',
      PRIMARY KEY (ID)
      UNIQUE KEY UNIQUE_USER_ID (USER_ID) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表格数据如下（嘻嘻 数据依旧使用与上次Mysql的文章[MySQL使用UNION连接两个查询排序失效][MySQL_UNION]相同的数据，但是要注意表结构不同。）

<table> 
 <thead> 
  <tr> 
   <th>ID</th> 
   <th>USER_ID</th> 
   <th>USER_NAME</th> 
   <th>AGE</th> 
   <th>COMMENT</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>111</td> 
   <td>开心菜鸟</td> 
   <td>18</td> 
   <td>今天很开心</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>222</td> 
   <td>悲伤菜鸟</td> 
   <td>21</td> 
   <td>今天很悲伤</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>333</td> 
   <td>认真菜鸟</td> 
   <td>30</td> 
   <td>今天很认真</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>444</td> 
   <td>高兴菜鸟</td> 
   <td>18</td> 
   <td>今天很高兴</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>555</td> 
   <td>严肃菜鸟</td> 
   <td>21</td> 
   <td>今天很严肃</td> 
  </tr> 
 </tbody> 
</table>

接下来我们执行以下sql

EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111;

发现给出的解释结果如下：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th style="width:79px;">partitions</th> 
   <th style="width:56px;">type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>filtered</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE&nbsp;&nbsp; &nbsp;</td> 
   <td>test_user</td> 
   <td style="width:79px;">&nbsp;</td> 
   <td style="width:56px;">ALL</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>5</td> 
   <td>&nbsp;</td> 
   <td>Using where</td> 
  </tr> 
 </tbody> 
</table>

我们给条件加上引号后再解释以下：

EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111';

这时候我们发现varchar类型的字段在作为字符串查询的时候使用了索引，在以数值类型进行查询时是不使用索引的。

## 问题引申 ##

那么问题来了，如果字段是整型的且加上索引，以字符串查询时会不会也不走索引呢？实践出真知，让我们再接着往下测试一下。

-- 将USER_ID的类型修改为整型
    CREATE TABLE test_user (
      ID int(11) NOT NULL AUTO_INCREMENT,
      USER_ID int(11) DEFAULT NULL COMMENT '用户账号',
      USER_NAME varchar(255) DEFAULT NULL COMMENT '用户名',
      AGE int(5) DEFAULT NULL COMMENT '年龄',
      COMMENT varchar(255) DEFAULT NULL COMMENT '简介',
      PRIMARY KEY (ID),
      UNIQUE KEY UNIQUE_USER_ID (USER_ID) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111;

EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111';

在执行了上面两个语句后我们发现，int类型的字段无论是以字符串查询还是以数值型查询都会走索引。

## 结论 ##

1.  当我们使用的字段是数值类型时，加引号或者不加引号（sql中单引号和双引号实现相同效果）都不影响索引的使用
2.  当我们的字段是字符串类型时，不加引号的查询无法使用索引，加引号的查询才可正常使用索引

综上所述，我认为以后写sql的时候注意最好都加上引号，避免这种字符串类型的不走索引的情况发生，更深层次的原理需要再挖掘一下，如果大家有什么意见可以探讨一下。

才疏学浅，如文中有错误，感谢大家指出。

转载于:https://www.cnblogs.com/runningRookie/p/11108768.html

[MySQL_UNION]: https://blog.csdn.net/qq_28194001/article/details/88559736