注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

SQL编程:隐式转换详解

MySQL SQL 彭东稳 3800次浏览 已收录 0个评论

索引问题是SQL问题中出现频率最高的,常见的索引问题包括:无索引,隐式转换。当数据库中出现访问表的SQL无索引导致全表扫描,如果表的数据量很大,扫描大量的数据,应用请求变慢占用数据库连接,连接堆积很快达到数据库的最大连接数设置,新的应用请求将会被拒绝导致故障发生。

一、什么是隐式转换?

当我们对不同类型的值进行比较的时候,为了使得这些数值「可比较」(也可以称为类型的兼容性),MySQL会做一些隐式转化(Implicit type conversion)。比如下面的例子:

很明显,上面的SQL语句的执行过程中就出现了隐式转化。并且从结果们可以判断出,第一条SQL中,将字符串的“1”转换为数字1,而在第二条的SQL中,将数字2转换为字符串“2”。MySQL也提供了CAST()函数,我们可以使用它明确的把数值转换为字符串。

当隐式转换出现在查询中,指SQL查询条件中的传入值与对应字段的数据定义不一致,此时MySQL在执行条件判断时,若参数类型与字段类型不匹配,则会做类型的隐式转换,符合转换规则的,转换完成后可以利用索引,如果无法转换则会导致索引无法使用,进而出现上述慢SQL堆积数据库连接数跑满的情况。常见的隐式转换如字段的表结构定义为字符类型,但SQL传入值为数字;或者是字段定义collation为区分大小写,在多表关联的场景下,其表的关联字段大小写敏感定义各不相同。

隐式类型转换有无法命中索引的风险,在高并发、大数据量的情况下,命不中索引带来的后果非常严重。将数据库拖死,继而整个系统崩溃,对于大规模系统损失惨重。

官方文档中关于隐式转化的规则是如下描述的:

  • 当两个参数至少有一个是NULL时,比较的结果也是NULL;另外是使用<=>(安全的等于)对两个NULL做比较时会返回1,这两种情况都不需要做类型转换。

  • 当两个参数都是字符串,会按照字符串来比较,不做类型转换。

  • 同样当两个参数都是整数,按照整数来比较,不做类型转换。

  • 十六进制的值和非数字做比较时,会被当做二进制串,和数字做比较时会按下面的规则处理。

有一个参数是TIMESTAMP或DATETIME,并且另外一个参数是常量,常量会被转换为timestamp。

有一个参数是decimal类型,如果另外一个参数是decimal或者整数,会将整数转换为decimal后进行比较;如果另外一个参数是浮点数,则会把decimal转换为浮点数进行比较;所有其他情况下,两个参数都会被转换为浮点数再进行比较。

由于MySQL隐式类型转换规则比较复杂,依赖MySQL隐式转换很容易出现各种想想不到的问题,而且MySQL隐式类型转换本身也是非常耗费MySQL服务器性能的,所以非常不推荐这样使用。

二、隐式转换分析

为了便于理解直接从隐式转换的注入例子开始来往下分析。首先创建表,加入俩个用户。

看如下查询

好奇这个结果怎么会是这样的,没错,这里利用了隐式转换注入,回过头来分析一下原理。

如上查询没什么问题…这里不说了,接着下面。

这里发生了隐式转换,结果为查询结果为1,这里’foo’被转换成double类型,但是显然他不是数字,所以它将转换成0。

MySQL手册说明如下:当操作符与不同类型的操作数一起使用时,会发生类型转换,使操作数兼容。

那么添加俩个字符串呢?不需要转换吗?看下面SQL执行。

这里“+”是个算术运算符,俩个字符串转化为数值0+0,结果自然就是0了。

现在知道俩个字符串的总合是0,通过查询我们知道SELECT ‘a’ + ‘b’ = 0;的结果是true(数值就是1),那么比较下俩个字符串总和和另外一个字符串。

从warnings我们可以看出都进行了隐式转换,查询执行应该是select 0=0…结果就是1。

MySQL手册说明如下:在所有其他情况下,将参数作为浮点数(实数)进行比较。

好了,基础了解到这里,来理解下文章开头那个案例。

在查询的时候数值进行隐式转换和比较,比如第一条记录的username过程应该是这样的,select ‘admin’=’a’+’b’结果自然为1,password以此类推。到第二条记录的时候,select ‘666admin’=’a’+’b’结果自然为0(字符串666admin即不等1也不等于0),进一步分析select 666=’a’+’b’,结果为假,数值就是0,所以开头那个案例的查询结果就是那样的了。

如果需要查询满足第二个条件select ‘666admin’=666这个可以满足,利用如下:

还有更多运算符可以在此利用。

三、隐式转换案例

案例一:int转string

表结构

执行计划

可以看见没有转换成功,所以也无法使用索引,那么解决办法如下:

上述案例中由于表结构定义mo字段后字符串数据类型,而应用传入的则是数字,进而导致了隐式转换,索引无法使用,所以有两种方案:

第一,将表结构mo修改为数字数据类型。

第二,修改应用将应用中传入的字符类型改为数据类型。

案例二:datetime转string

表结构

执行计划

解决办法

数据类型定义错误隐式转换,导致数据库cpu压力100%,所以我们在定义时间字段的时候一定要采用时间类型的数据类型。

案例三:字符集转换

表结构

执行计划

修改COLLATE

执行计划

可以看到修改了字段的COLLATE后执行计划使用到了索引,所以一定要注意表字段的collate属性的定义保持一致。

案例四:string转int

上面三个案例都没有转换成功,但是也并不是所有的都无法转换,比如string转int在某些情况下就没有问题。

执行计划

从结果上看已经转换成功了,并且正常使用了索引。那么如果查询条件是 where uid in ('1',2)呢?还能转换成功吗?可以看看淘宝月报这篇文章的分析:MySQL · 最佳实践 · 一个“异常”的索引选择

四、索引常见误区

  • 误区一:对查询条件的每个字段建立单列索引,例如查询条件为:A=?and B=?and C=?。

在表上创建了3个单列查询条件的索引idx_A(A),idx_B(B),idx_C(C),应该根据条件的过滤性,创建适当的单列索引或者组合索引。

  • 误区二:对查询的所有字段建立组合索引,例如查询条件为select A,B,C,D,E,F from T where G=?。

在表上创建了idx_A_B_C_D_E_F_G(A,B,C,D,E,F,G)。

五、索引最佳实践

  • 在使用索引时,我们可以通过explain+extended查看SQL的执行计划,判断是否使用了索引以及发生了隐式转换。
  • 由于常见的隐式转换是由字段数据类型以及collation定义不当导致,因此我们在设计开发阶段,要避免数据库字段定义,避免出现隐式转换。
  • 由于MySQL不支持函数索引,在开发时要避免在查询条件加入函数,例如date(gmt_create)。
  • 所有上线的SQL都要经过严格的审核,创建合适的索引。

<参考>

MySQL隐式转化整理


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (1)or分享 (0)
关于作者:

您必须 登录 才能发表评论!