mysql索引字段类型int和varchar的不同使用_努力向上的小姑娘的博客

发布时间：2023-12-14 12:34:11 所属栏目：MySql 来源：DaWei

导读： 起因：

这两天线上发现功能异常，排查了日志发现有报错：

The last packet successfully received from the server was 5,005 milliseconds ago. The last packet sent successfully

起因：

这两天线上发现功能异常，排查了日志发现有报错：

The last packet successfully received from the server was 5,005 milliseconds ago. The last packet sent successfully to the server was 5,005 milliseconds ago
发现是mysql链接超时导致的，首先想到是不是慢sql导致的超时，于是就拿出了这个sql在线上执行了一下，发现执行时间24s，这个让我很惊讶；于是进行几个检查分析。检查sql；没发现什么问题，就是一个简单的两张表的join操作检查explain执行计划；发现有一个字段索引key=null，ref=null，rows=11W（全表数据），这个是个问题检查表大小；因为都是配置表，表数据不大，大概左表50w右表11w数据检查表索引；where条件里的字段都有索引

怀疑肯定是索引出问题导致的；于是就又仔细检查了sql；发现xml文件中parameterType=Long，where条件中字段类型是varchar！！！（rule_c_id类型为varchar，rule_c_id=123，索引失效，需要修改为rule_c_id='123')

于是改了下sql重新执行，发现0.1秒执行成功；

原因：

parameterType=String时，索引字段对应类型是int或varchar，索引都能生效；

parameterType=Int，Long时，索引字段对应类型是int，索引生效；索引字段类型是Varchar，索引失效；

举例：

线上数据敏感mysql表索引，线下新建一张测试表，复现一下

（1）新建测试表、索引

CREATE TABLE index_test_1 (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
str_test VARCHAR(100) NOT NULL DEFAULT '',
num_test INT(11) NOT NULL DEFAULT '0',
other_test VARCHAR(200) DEFAULT NULL,
PRIMARY KEY(id),
KEY idx_str (str_test),
KEY idx_num (num_test)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
（2）插入测试数据

INSERT INTO index_test_1(str_test,num_test,other_test) VALUE("111", 111, "测试数据1");
INSERT INTO index_test_1(str_test,num_test,other_test) VALUE("111", 111, "测试数据2");
INSERT INTO index_test_1(str_test,num_test,other_test) VALUE("222", 222, "测试数据3");
（3）int类型输入，分别使用idx_num和idx_str两种索引查看执行计划

idx_num对应的num_test字段类型是int；执行计划如下图：

mysql 创建表索引_mysql创建表索引语句_mysql表索引

idx_str对应的str_test字段类型是varchar；执行计划如下图：

mysql创建表索引语句_mysql 创建表索引_mysql表索引

从两个执行计划很明显的发现，idx_str索引的执行计划中key=null，ref=null，rows=3；没有使用到索引

（4）varchar类型输入，分别使用idx_num和idx_str两种索引查看执行计划

idx_num对应的num_test字段类型是int；执行计划如下图：

mysql 创建表索引_mysql表索引_mysql创建表索引语句

idx_str对应的str_test字段类型是varchar；执行计划如下图：

mysql表索引_mysql 创建表索引_mysql创建表索引语句

从两个执行计划看出，varchar类型输入，不管索引字段是int还是varchar都可以使用索引。

说明：

为什么varchar类型输入，索引是int类型或者是varchar类型的字段，都能生效呢？

原因是mysql在执行的时候，会将数值隐式转换，但是这个操作会有无法命中索引的风险。隐式转换还有其他的风险，比如sql注入、多查数据、多删数据等，所以大家在写sql时一定要细心。

（编辑：商洛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!