浅谈MySql整型索引和字符串索引失效或隐式转换问题
作者:试着奔跑的菜鸟 发布时间:2024-01-15 20:41:39
标签: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类型的字段无论是以字符串查询还是以数值型查询都会走索引。
结论
当我们使用的字段是数值类型时,加引号或者不加引号(sql中单引号和双引号实现相同效果)都不影响索引的使用
当我们的字段是字符串类型时,不加引号的查询无法使用索引,加引号的查询才可正常使用索引
综上所述,我认为以后写sql的时候注意最好都加上引号,避免这种字符串类型的不走索引的情况发生,更深层次的原理需要再挖掘一下,如果大家有什么意见可以探讨一下。
来源:https://blog.csdn.net/qq_28194001/article/details/90488782
![](https://www.aspxhome.com/images/zang.png)
![](https://www.aspxhome.com/images/jiucuo.png)
猜你喜欢
- 本文实例为大家分享了Python版名片管理系统的具体代码,供大家参考,具体内容如下先建立cards_main的文件import cards_
- 本项目为python项目需要安装python及python的opencv模块:opencv_python-4.0.1-cp37-cp37m-
- 前言加密技术在数据安全存储,数据传输中发挥着重要作用,能够保护用户隐私数据安全,防止信息窃取。RSA是一种非对称加密技术,在软件、网页中已得
- 以下是SQL连接代码: 代码如下:Function Open_conn() dim Conn,Strconn&
- 本文实例讲述了Python基于ThreadingTCPServer创建多线程代理的方法。分享给大家供大家参考,具体如下:#coding=ut
- eval 跟json.loads 是不一样的函数,是有实现不一样功能的地方,但是在某些地方它们两个函数的功能是一样的,在这个时候如果对执行效
- 学习vue和nodejs的过程当中,涉及到了axios,今天为了测试,写了get和post两个方法来跟node服务端交互,结果因为heade
- 1、实现效果2、实现步骤模块导入import os,sys,timefrom PyQt5 import QtCore,QtWidgets,Q
- 本文实例为大家分享了python实现K折交叉验证的具体代码,供大家参考,具体内容如下用KNN算法训练iris数据,并使用K折交叉验证方法找出
- IE的特殊性 IE的DOM元素属性与Firefox, Opera, Safari有些不同。在IE中,我们可以给DOM添加任意自定
- 1.准备工作1.在文件里找到设置2.在项目里找到python解释器,点击右边的加号3.搜素pygame并安装同理下载pgzero安装包2.开
- PHP htmlentities() 函数实例把一些字符转换为 HTML 实体:<?php $str = "<&
- mint19.2 本来pip 和 pip2 对应 python2.7 pip3对应pytho
- 之前在做数据分析的过程中,需要对数据进行实时的写入,比如对新生成的数据写入之前已经生成的txt或csv文件中。现在想想其实很简单,所以做一个
- 一:从版本3.23升级到4.104.10新特性:支持事务处理和存储过程升级过程的注意事项:1:升级采用直接复制安装文件的方法,简单实用,但要
- 原来sql还有个stuff的函数,很强悍。 一个列的格式是单引号后面跟着4位的数字,比如'0003,'0120,'4
- 本文实例为大家分享了python脚本筛选出两个文件中重复的行数,供大家参考,具体内容如下'''查找A文件中,与B文件
- 刚刚换用windows7 64位旗舰版,使用其自带的iis7作为调试工具,今天调试一个ASP+ACCESS的网站的时候遇到了“ADODB.C
- 导语:使用 python-plotly 模块来进行压测数据的绘制,并且生成静态 html 页面结果展示。不少小伙伴在开发过程中都有对模块进行
- Django的QuerySets酷毙了!在本文中我将解释一下QuerySets是什么,它是如何工作的(如果你对它已经熟悉了,你可