• 问题描述
  • 揭秘隐式转换
  • 问题产生原因以及解决

问题描述

在一次业务查询的代码中，表aggregation_learn中字段ref_id的类型为varchar，但是在sql编写认为ref_id为bigint类型，所以写出来下述sql。ref_id后面用的是Java中的Long形数据。下述代码在测试环境并没有发现什么问题，但是上线后却带来了线上问题。

select learner_id as id from aggregation_learn  where ref_type=0 and ref_id=4123124123;

后经排查发现，线上环境此条sql每次执行都超过了阀值30s，然后抛出了超时，导致了系统功能错误。虽然针对ref_id与ref_type是有建立索引的，但是在执行时却走了全表扫描，aggregation_learn一共有8000多万行数据，从而造成了超时。

揭秘mysql隐式转换

  • 什么是隐式转换
    mysql在进行字段对比时，如果两个参数类型不一致，此时便会进行类型转换。让他们可以进行比较。你可以在sql中进行显示转换，或者在执行时自动进行隐式转换（应该避免，效率低且容易出错）。
  • mysql隐式转换规则 这里直接贴出官网给出的转换规则，就不再进行赘述。

MySQL 发生隐式转换的场景 隐式转换索引失效_隐式转换

问题产生原因以及解决

  • 转换规则导致的索引失效 既然我们已经知道了mysql的类型转换规则，那么我们string与number的类型进行对比为什么会导致索引失效以至于全表扫描呢。原来对于column为string类型的索引，如果有三个值分别为" 1"," 1",“1a”。在进行隐式转换成number后的值都为1。这种情况下索引自然就不可用。mysql便不会通过索引进行快速查询，而是将对对应column全部进行类型转换后通过全表扫描来进行查询。

MySQL 发生隐式转换的场景 隐式转换索引失效_mysql_02

  • 解决办法
    在进行sql编写时一定要关注列的字段类型，避免隐式转换而发生意料之外的问题。

官方文档地址：https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html