我的编程空间,编程开发者的网络收藏夹
学习永远不晚

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

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

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

问题概述

今天在上班时,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连接两个查询排序失效相同的数据,但是要注意表结构不同。)

ID USER_ID USER_NAME AGE COMMENT
1 111 开心菜鸟 18 今天很开心
2 222 悲伤菜鸟 21 今天很悲伤
3 333 认真菜鸟 30 今天很认真
4 444 高兴菜鸟 18 今天很高兴
5 555 严肃菜鸟 21 今天很严肃

接下来我们执行以下sql


EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111;

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

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE test_user ALL 5 Using where

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


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的时候注意最好都加上引号,避免这种字符串类型的不走索引的情况发生,更深层次的原理需要再挖掘一下,如果大家有什么意见可以探讨一下。

到此这篇关于浅谈MySql整型索引和字符串索引失效或隐式转换问题的文章就介绍到这了,更多相关MySql整型索引和字符串索引失效内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

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

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

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

这篇文章主要为大家展示了“如何解决MySql整型索引和字符串索引失效或隐式转换问题”,内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让小编带领大家一起研究并学习一下“如何解决MySql整型索引和字符串索引失效或隐式转换问题”这篇文章
2023-06-25

MySQL索引失效后隐式转换的问题这么解决

MySQL索引失效后隐式转换的问题这么解决,针对这个问题,这篇文章详细介绍了相对应的分析和解答,希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。常见索引失效:1. 条件索引字段"不干净":函数操作、运算操作2. 隐式类型转换:字
2023-06-26

Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的问题

背景在一次进行SQl查询时,我试着对where条件中vachar类型的字段去掉单引号查询,这个时候发现这条本应该很快的语句竟然很慢。这个varchar字段有一个复合索引。其中的总条数有58989,甚至不加单引号查出来的数据不是我们想要的数据
2022-05-17

编程热搜

目录