WHRER条件里的数据类型必须和字段数据类型一致

首先看案例：

 

表中字段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发生隐式类型转换，隐式转换不一定按我们预想的来转换，在一些转换条件下会产生性能影响，而且往往这种情况下性能问题比较难以发现和定位

引用自：http://blog.itpub.net/22418990/viewspace-1302080/