【sql】隐式转换造成的索引失效

测试数据准备：
测试表一共包含7个字段，num1和num2值为数字，其中num1是int类型，num2是varchar类型。
type1和type2保存的都是主键对5的取模，目的是模拟实际应用中常用类似type类型的数据，但是type2没有建立索引。
str1和str2都是保存了一个20位长度的随机字符串。
数据量为442万条。

测试执行：
SELECT * FROM test1 WHERE num1 = 10000;
SELECT * FROM test1 WHERE num1 = ‘10000’;
SELECT * FROM test1 WHERE num2 = 10000;
SELECT * FROM test1 WHERE num2 = ‘10000’;
1、2查询的字段是int类型，3、4查询的字段是varchar类型。1、2或3、4查询的字段虽然都相同，但是一个条件是数字，一个条件是用引号引起来的字符串。

经测试这四条SQL最后的执行结果相差很大，其中1、2、4三条SQL基本都是瞬间出结果，在百万级的数据量下这样的结果可以判定这三条SQL性能基本没差别了。但是第三条SQL，多次测试耗时都是秒级的。
来看一下执行计划：




可以看到，124三条SQL都能使用到索引，连接类型都为ref，扫描行数都为1，所以效率非常高。再看看第三条SQL，没有用上索引，全表扫描，所以性能差别很大。

这是因为，第23两条SQL都发生了隐式转换，第2条SQL的查询条件num1 = ‘10000’，左边是int类型右边是字符串，第3条SQL相反，左右两边都会转换为浮点数再进行比较。

第2条SQL：SELECT * FROM test1 WHERE num1 = ‘10000’; 左边为int类型10000，转换为浮点数还是10000，右边字符串类型’10000’，转换为浮点数也是10000。两边的转换结果都是唯一确定的，所以不影响使用索引。

第3条SQL：SELECT * FROM test1 WHERE num2 = 10000; 左边是字符串类型’10000’，转浮点数为10000是唯一的，右边int类型10000转换结果也是唯一的。但是，因为左边是检索条件，‘10000’转到10000虽然是唯一，但是其他字符串也可以转换为10000，比如’10000a’，‘010000’，'10000’等等都能转为浮点数10000，这样的情况下，是不能用到索引的。

结论：
1 当操作符左右两边的数据类型不一致时，会发生隐式转换。
2 当where查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
3 当where查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。

====================================================================================

扩展知识：
Explain关键字是Mysql中sql优化的常用「关键字」，通常都会使用Explain来「查看sql的执行计划，而不用执行sql」，从而快速的找出sql的问题所在。

这是一条sql的执行计划。共输出了10个字段，这10个字段的含义解释如下：
id字段：
id就是标识sql的执行顺序，一般在复杂查询中会有多条记录，简单查询只有一条记录，复杂查询中id相同的为一组，执行的顺序是从上往下，而id越大的越先执行。

select_type字段：
select_type表示查询的类型，也就是对应的是简单查询还是复杂查询，若是复杂查询又包含：「简单的子查询、from子句的子查询、union查询」。下面就分别来看看select_type中的所有查询类型：

SIMPLE：表示简单查询，不含有任何的复杂查询。
PRIMARY：复杂查询中，最外层的select语句的查询类型就是PRIMARY。
SUBQUERY：在select或者where中包含的子查询会被表示为SUBQUERY类型。
DERIVED：表示派生表或者衍生表的意思，在from包含的子查询中会被表示为DERIVED类型，Mysql会递归执行这些子查询，并且把结果放在临时表中。
UNION：在出现UNION查询语句中，第二个select的查询语句就会被表示为UNION。
UNION RESULT：UNION查询语句的结果被标记为UNION RESULT。
其它
上面的七个select_type都是比较常见的，还有一些不常见的：
DEPENDENT UNION：也表示UNION查询语句中第二个或者后面的语句，但是取决于外面的查询。
DEPENDENT SUBQUERY：子查询中的第一个select语句，也是依赖于外部的查询。
UNCACHEABLE SUBQUERY：子查询的结果不能被缓存，必须重新评估外连接的第一行。

table字段：table字段表示的是查询的是哪个表。

partitions字段：partitions表示所匹配的分区。

type字段：type字段表示的sql关联的类型或者说是访问的类型。从这个字段中我们可以确定这条sql查找数据库表的时候，查找记录的大概范围是怎么样的，直接就能体现sql的效率问题。
type字段的类型也是有比较多，主要常见掌握的有以下几个：system、const 、eq_ref 、ref 、range 、index 、ALL。它的性能体现是从高到低。
system > const > eq_ref > ref > range > index > ALL，下面就来详细的说一说这属性。

system：system是const的特例，「表示表中只有一行记录」，这个几乎不会出现，也作为了解。
const：const表示通过索引一次就查找到了数据，一般const出现在「唯一索引或者主键索引中使用等值查询」，因为表中只有一条数据匹配，所以查找的速度很快。
eq_ref：eq_ref表示使用唯一索引或者主键索引扫描作为表链接匹配条件，对于每一个索引键，表中只有一条记录与之匹配。
ref：ref性能比eq_ref差，也表示表的链接匹配条件，也就是使用哪些表字段作为查询索引列上的值。
ref与eq_ref的区别就是：
eq_ref使用的是唯一索引或者主键索引。
ref扫描后的结果可能会找到多条符合条件的行数据，本质上是一种索引访问，返回匹配的行。
range：range使用索引来检索给定范围的行数据，一般是在where后面使用between、<>、in等查询语句就会出现range
index：index表示会遍历索引树，index回避ALL速度快一些，但是出现index说明需要检查自己的索引是否使用正确。
ALL：ALL与index的区别就是ALL是从硬盘中读取，而index是从索引文件中读取，ALL全表扫描意味着Mysql会从表的头到尾进行扫描，这时候表示通常需要增加索引来进行优化了，或者说是查询中并没有使用索引作为条件进行查询。

possible_keys字段：possible_keys表示这一列查询语句可能使用到的索引，仅仅只是可能，列出来的索引并不一定真正的使用到。
当没有使用索引为NULL时，说明需要增加索引来优化查询了，若是表的数据比较少的话，数据库觉得全表扫描更快，也可能为NULL。

key字段：key字段与possible_keys的区别就是，表示的真正使用到的索引，即possible_keys中包含key的值。

key_len字段：表示sql查询语句中索引使用到的字节数，这个字节数并不是实际的长度，而是通过计算查询中使用到的索引中的长度得出来的，显示的是索引字段最大的可能长度。
一般来说在不损失精度的前提下。

ref字段：ref表示列与索引的比较，表连接的匹配条件，表示哪些列或者常量被用于查询索引列上的值。

rows字段：rows表示估算的要扫描的行数，一般Mysql会根据统计表信息和索引的选用情况，估算出查找记录所要扫描的行数，注意这个并不是实际结果集的行数。

filtered字段：filtered表示的是查询表行所占表的百分比。

Extra字段：该字段显示的是sql查询的额外信息，常见的有以下几种情况：

Using index：表示查询的列被索引覆盖，这个是查询性能比较高的体现，即所要查询的信息搜在索引里面可以得到，不用回表，索引被正确的使用。假如同时出现了using where，表示索引用于执行索引键值的查找；若是没有出现using where，则表示索引用于读取数据，而非执行查询的动作。
Using where：该属性与Using index相反，查询的列并没有被索引覆盖，where条件后面使用的是非索引的前导列，它仅仅是使用了where条件而已。
Using temporary：Using temporary表示使用了临时表存储中间的结果，一般在对结果排序的时候会使用临时表。
Using filesort：Using filesort表示文件排序，说明Mysql对数据使用了外部的索引进行排序，并没有使用表中的索引进行排序。它强调在获取连接条件时，并没有使用索引，而是使用连接缓冲区来存储中间结果，若是出现该值，一般说明需要添加索引来进行优化了。
Impossible where：Impossible where会出现在where后的条件一直为false的情况下，这种可以忽视，比较少出现。
Select tables optimized away：表示select语句没有遍历表或者索引就返回数据了。