• 博客访问: 347550
  • 博文数量: 96
  • 用 户 组: 普通用户
  • 注册时间: 2009-08-26 09:20
个人简介

暂无介绍

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(96)

文章存档

2015年(1)

2014年(14)

2013年(14)

2012年(57)

2011年(10)

我的朋友
微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: MySQL

首先看案例:


表中字段FPHONE_IMEI是varchar类型的,主键也建立在FPHONE_IMEI 字段上,原则上只要where条件中用到了这个字段,就会走索引,这也是建立索引的目的,可事实是这样子么。首先看下面这条查询,初看,唯一值查询走索引应该很快,实际上呢,从执行计划可以看出,没有使用到索引,而是全表扫描,所有这条查询语句性能好不到哪里。


看看实际的执行时间:


1.11s,超过一秒了我们定义为慢SQL,因此这条语句是有性能问题的。

再看下面这条查询语句,就和预想的一样,走主键索引,查询很快,也是我们希望看到的结果。


看看执行时间:


0.01s,这个时间才是我们需要的。

从写法上来看,上述两条语句差不多,为何性能有如此大的差距呢,问题就在差不多上面,注意看仔细咯,看第一条语句,where条件中给定的值没有引号,而FPHONE_IMEI 字段是字符串的,也就是两边类型不匹配,第二条语句类型是匹配的,因此没有性能问题,所以导致上述差异的原因是类型不匹配。那问题来了:

1.为何类型不匹配会导致性能如何大差异呢

2.是不是类型不匹配就一定会导致性能问题呢。

请看下文:

mysql在做比较时,如果发现类型不匹配,会发生自动隐式类型转换,是的,mysql是会自动隐式类型转换,既然是转换,那转换的规则是怎样的,规则如下:

1.如果一个或两个参数都是空的,比较的结果为null。null与null比较,结果为null,无需进行转换。

2.如果两个比较的两个参数都是string,按string比较,不做类型转换。

3.如果两个比较的两个参数都是integer,按integer比较,不做类型转换。

4.十六进制的值和非数字做比较时,会被当做二进制串来比较。

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

6.有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较

7.所有其他情况下,两个参数都会被转换为浮点数再进行比较。

 

说完转换的规则,再说下索引,索引的作用是为了让mysql根据查询条件快速定位到相应的记录,从而减少IO次数,达到快速返回结果的目的。但是如果mysql发现查询条件无法满足快速定位到记录的时候,会放弃索引,而走全表扫描,因为它觉得全表扫描比走索引更优,当然这一系列操作都是mysql优化器决定。

 

再接着上面的两条SQL语句说,从上述的规则来看,第一条SQL符合第七条规则,因此都需要转换为浮点数再进行比较。那就看看mysql对具体的数值是如何进行转换的:

我们可以看到,一个float数值可以由多个字符串转换而来,比如上面的1,发生转换的可以有 1,'1','1xxx','  1',对于’1xxx’这样的,会发生截断,非数字会丢弃。因此如果一个索引建立在string类型上,如果这个字段和一个int类型的值比较,这时两者同样是转换为float,但是因为索引是string的,而string转换为同一个float的情况很多,同一个float值对应的string可能有很多,这就有点类似%string%的情况,mysql认为这种情况无法通过访问索引来快速的定位记录,索引的效果是访问少而准,从而索引失效,进而走全表扫描,既然是全表扫描,这个性能是好不到哪里去的,这也是第一条语句出现的问题,字符串隐式转换为float类型,从而导致索引失效,第一条语句出现的warnings而正是因为发生了隐式转换导致字段截断而出现的,这也证实了隐式转换的发生。而对于两边都是string的话,不发生类型转换,而是直接比较,从而可以通过走索引进行快速查询。

反过来又会怎样呢,另外一种情况,当一个索引建立在int类型上,如果这个字段和一个字符串比较,会是怎么样的情况呢,同样会发生类型转换,两边都转换为float类型,但是int类型转换到float是唯一的,不会像上面情况一样,不会存在可能很多记录转换为同一个值的情况,同时字符串转换为唯一的int类型值,因此是可以用到索引定位到具体记录的(如果记录存在),从而可以达到走索引的效果,快速响应查询。看下面例子进一步说明:


表中x字段是int类型,建立有索引,

第一条查询语句,都是int类型比较,和预想一样,走索引。


第二条查询语句,和纯数字字符串比较,和我们上面说的转换第二种情况一样,同样走索引。


第三条查询语句,和混合字符串比较,同样我们上面说的转换第二种情况一样,同样走索引,只是字符串发生了截断。


上述这个例子和我们说的第二种情况一致,发生类型转换还是走索引。

我们再回到最前面提出的问题:

1.       为何类型不匹配会导致性能如何大差异

2.       是不是类型不匹配就一定会导致性能问题

相信上面的解读已经很好的回答了这两个问题。对1问题,类型不匹配,会导致发生隐式类型转换,根据转换的结果可能导致性能影响。对2问题,并不是发生类型转换就一定导致性能问题,这个要看具体转换的字段,所以也不是一定的。但是我们在拼写SQL语句的时候,不能只看到不一定,而是要做到一定不要,不要将类型不匹配的字段进行比较,要保证WHRER条件里的数据类型必须和字段数据类型一致。

 

总结: where查询条件中,务必保证提供的数据类型和表中字段类型一致,而不是让mysql发生隐式类型转换,隐式转换不一定按我们预想的来转换,在一些转换条件下会产生性能影响,而且往往这种情况下性能问题比较难以发现和定位
阅读(970) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册