mysql索引失效的问题,差点我的工作凉了

下面分享的是我在工作中遇到的一个问题。 有关 mysql 索引失效的问题。

处于保密考虑,我拿一个类似的场景举例子。

1、现象描述

先说结论。我遇到的问题是,mysql varchar 类型的字段,传入的查询条件没有加引号,导致索引失效。

比如我有一张表,结构如下:

CREATE TABLE `order_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(32) DEFAULT '',
  `name` varchar(11) DEFAULT '',
  `age` tinyint(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

可以看到 user_id 字段是 varchar 类型,并且这个字段傻姑娘有一个普通索引。

我分别用下面两条语句查询,你看看效果,

explain SELECT * from order_test t where t.`user_id` = '11223344';

结果,

explain SELECT * from order_test t where t.`user_id` = 11223344;

结果,

很明显,第二条 sql 语句索引没有生效。

虽然 user_id 是字符串类型,但是我们传入一个整型的数字并没有报错,而是 mysql 帮我们做了转换并且进行了全表扫描查询。

2、场景复现

一般我们都是通过 mybatis 操作数据库,我当时遇到的情况就是在 mybatis 的 xml 里通过 map 作为入参传递查询字段。举例如下,

xml 文件,

<select id="selectByMap" resultMap="BaseResultMap" parameterType="java.util.Map" >
    select * from order_test where user_id = ${user_id}
  </select>

代码层是这样的,

public void test2(){
        Map<String, Object> map = new HashMap<>();
        map.put("user_id", 11223344);
        mapper.selectByMap(map);

    }

这种情况下,mysql 解析出来的 sql 是这样的,

select * from order_test where user_id = 11223344

根据第 1 部分的结论,没有引号的查询是不走索引的。

只所以没有加引号,是因为 ${user_id} ,如果我用 # {user_id} 就会加上引号了。这个原因就不在本文展开了。

3、索引失效的原因

我们做技术,要有刨根问底的精神。知其然还要知其所以然。

索引失效的原因,简单来说就是 mysql 内部的隐式类型转换,导致了优化器执行计划出问题。

mysql 底层有个叫优化器的东东,当我们执行一条 sql 时,会经过优化器确定是否使用索引。也就是说执行计划是在这个阶段确定的。

我借用一张网上的图:

如果对索引字段做函数操作(本例是 cast 函数做了隐式的转换),可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。

4、总结

  1. 字符串类型的索引查询语句中必须加单引号,否则 MySQL 不会使用该索引。

  2. MySQL 不支持函数索引 ,在开发时要避免在查询条件加入函数。

精彩回顾:

面试的时候,如果你没掌握索引,绝对没戏!

面试官:兄弟,说说 Spring Cloud 的核心架构原理吧

如果有人问你 Dubbo 中注册中心工作原理,就把这篇文章给他

-END-

Java 极客技术

关注Java极客技术,回复面试,即可获取独家面试宝典,《Java核心知识点整理.pdf》

我来评几句
登录后评论

已发表评论数()

相关站点

热门文章