MySQL隐式类型的转换陷阱和规则
前言
相信大家都知道隐式类型转换有无法命中索引的风险，在高并发、大数据量的情况下，命不中索引带来的后果非常严重。将数据库拖死，继而整个系统崩溃，对于大规模系统损失惨重。所以下面通过本文来好好学习下MySQL隐式类型的转换陷阱和规则。
1.隐式类型转换实例
今天生产库上突然出现MySQL线程数告警，IOPS很高，实例会话里面出现许多类似下面的sql：(修改了相关字段和值)
SELECTf_col3_id,f_qq1_idFROMd_dbname.t_tb1WHEREf_col1_id=1226391andf_col2_id=1244378and f_qq1_idin(12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233)
用explain看了下扫描行数和索引选择情况：
mysql>explainSELECTf_col3_id,f_qq1_idFROMd_dbname.t_tb1WHEREf_col1_id=1226391 andf_col2_id=1244378andf_qq1_idin(12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233); +------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+ |1|SIMPLE|t_tb1|ref|uid_type_frid,idx_corpid_qq1id|uid_type_frid|8|const|1386|Usingindexcondition;Usingwhere| +------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+ 共返回1行记录,花费11.52ms.
t_tb1表上有个索引uid_type_frid(f_col2_id,f_type)、idx_corp_id_qq1id(f_col1_id,f_qq1_id)，而且如果选择后者时，f_qq1_id的过滤效果应该很佳，但却选择了前者。当使用hintuseindex(idx_corp_id_qq1id)时：
mysql>explainextendedSELECTf_col3_id,f_qq1_idFROMd_dbname.t_tb1useindex(idx_corpid_qq1id)WHEREf_col1_id=1226391andf_col2_id=1244378andf_qq1_idin(12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233); +------+---------------+--------+--------+---------------------+------------------+------------+----------+-------------+------------------------------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +------+---------------+--------+--------+---------------------+------------------+------------+----------+-------------+------------------------------------+ |1|SIMPLE|t_tb1|ref|idx_corpid_qq1id|idx_corpid_qq1id|8|const|2375752|Usingindexcondition;Usingwhere| +-----+---------------+--------+--------+---------------------+------------------+------------+----------+-------------+------------------------------------+ 共返回1行记录,花费17.48ms. mysql>showwarnings; +-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+ |Level|Code|Message| +-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+ |Warning|1739|Cannotuserangeaccessonindex'idx_corpid_qq1id'duetotypeorcollationconversiononfield'f_qq1_id'| |Note|1003|/*select#1*/select`d_dbname`.`t_tb1`.`f_col3_id`AS`f_col3_id`,`d_dbname`.`t_tb1`.`f_qq1_id`AS`f_qq1_id`from`d_dbname`.`t_tb1`USEINDEX(`idx_corpid_qq1id`)where| |||((`d_dbname`.`t_tb1`.`f_col2_id`=1244378)and(`d_dbname`.`t_tb1`.`f_col1_id`=1226391)and(`d_dbname`.`t_tb1`.`f_qq1_id`in| |||(12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233)))| +-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+ 共返回2行记录,花费10.81ms.
rows列达到200w行，但问题也发现了：select_type应该是range才对，key_len看出来只用到了idx_corpid_qq1id索引的第一列。上面explain使用了extended，所以showwarnings;可以很明确的看到f_qq1_id出现了隐式类型转换：f_qq1_id是varchar，而后面的比较值是整型。
解决该问题就是避免出现隐式类型转换(implicittypeconversion)带来的不可控：把f_qq1_idin的内容写成字符串：
mysql>explainSELECTf_col3_id,f_qq1_idFROMd_dbname.t_tb1WHEREf_col1_id=1226391andf_col2_id=1244378and
f_qq1_idin('12345','23456','34567','45678','56789','67890','78901','89012','90123','901231');
+-------+---------------+--------+---------+--------------------------------+------------------+-------------+---------+---------+------------------------------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+-------+---------------+--------+---------+--------------------------------+------------------+-------------+---------+---------+------------------------------------+
|1|SIMPLE|t_tb1|range|uid_type_frid,idx_corpid_qq1id|idx_corpid_qq1id|70||40|Usingindexcondition;Usingwhere|
+-------+---------------+--------+---------+--------------------------------+------------------+-------------+---------+---------+------------------------------------+
共返回1行记录,花费12.41ms.
扫描行数从1386减少为40。
类似的还出现过一例：
SELECTcount(0)FROMd_dbname.t_tb2wheref_col1_id='1931231'ANDf_phonein(098890); |Warning|1292|TruncatedincorrectDOUBLEvalue:'1512-98464356'
优化后直接从扫描rows100w行降为1。
借这个机会，系统的来看一下mysql中的隐式类型转换。
2.mysql隐式转换规则
2.1规则
下面来分析一下隐式转换的规则：
a.两个参数至少有一个是NULL时，比较的结果也是NULL，例外是使用<=>对两个NULL做比较时会返回1，这两种情况都不需要做类型转换
b.两个参数都是字符串，会按照字符串来比较，不做类型转换
c.两个参数都是整数，按照整数来比较，不做类型转换
d.十六进制的值和非数字做比较时，会被当做二进制串
e.有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp
f.有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较，如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较
g.所有其他情况下，两个参数都会被转换为浮点数再进行比较
mysql>select11+'11',11+'aa','a1'+'bb',11+'0.01a'; +-----------+-----------+-------------+--------------+ |11+'11'|11+'aa'|'a1'+'bb'|11+'0.01a'| +-----------+-----------+-------------+--------------+ |22|11|0|11.01| +-----------+-----------+-------------+--------------+ 1rowinset,4warnings(0.00sec) mysql>showwarnings; +---------+------+-------------------------------------------+ |Level|Code|Message| +---------+------+-------------------------------------------+ |Warning|1292|TruncatedincorrectDOUBLEvalue:'aa'| |Warning|1292|TruncatedincorrectDOUBLEvalue:'a1'| |Warning|1292|TruncatedincorrectDOUBLEvalue:'bb'| |Warning|1292|TruncatedincorrectDOUBLEvalue:'0.01a'| +---------+------+-------------------------------------------+ 4rowsinset(0.00sec) mysql>select'11a'=11,'11.0'=11,'11.0'='11',NULL=1; +------------+-------------+---------------+----------+ |'11a'=11|'11.0'=11|'11.0'='11'|NULL=1| +------------+-------------+---------------+----------+ |1|1|0|NULL| +------------+-------------+---------------+----------+ 1rowinset,1warning(0.01sec)
上面可以看出11+'aa'，由于操作符两边的类型不一样且符合第g条，aa要被转换成浮点型小数，然而转换失败（字母被截断），可以认为转成了0，整数11被转成浮点型还是它自己，所以11+'aa'=11。
0.01a转成double型也是被截断成0.01，所以11+'0.01a'=11.01。
等式比较也说明了这一点，'11a'和'11.0'转换后都等于11，这也正是文章开头实例为什么没走索引的原因：varchar型的f_qq1_id，转换成浮点型比较时，等于12345的情况有无数种如12345a、12345.b等待，MySQL优化器无法确定索引是否更有效，所以选择了其它方案。
但并不是只要出现隐式类型转换，就会引起上面类似的性能问题，最终是要看转换后能否有效选择索引。像f_id='654321'、f_mtimebetween'2016-05-0100:00:00'and'2016-05-0423:59:59'就不会影响索引选择，因为前者f_id是整型，即使与后面的字符串型数字转换成double比较，依然能根据double确定f_id的值，索引依然有效。后者是因为符合第e条，只是右边的常量做了转换。
开发人员可能都只要存在这么一个隐式类型转换的坑，但却又经常不注意，所以干脆无需记住那么多规则，该什么类型就与什么类型比较。
2.2隐式类型转换的安全问题
implicittypeconversion不仅可能引起性能问题，还有可能产生安全问题。
mysql>desct_account; +-----------+-------------+------+-----+---------+----------------+ |Field|Type|Null|Key|Default|Extra| +-----------+-------------+------+-----+---------+----------------+ |fid|int(11)|NO|PRI|NULL|auto_increment| |fname|varchar(20)|YES||NULL|| |fpassword|varchar(50)|YES||NULL|| +-----------+-------------+------+-----+---------+----------------+ mysql>select*fromt_account; +-----+-----------+-------------+ |fid|fname|fpassword| +-----+-----------+-------------+ |1|xiaoming|p_xiaoming| |2|xiaoming1|p_xiaoming1| +-----+-----------+-------------+
假如应用前端没有WAF防护，那么下面的sql很容易注入：
mysql>select*fromt_accountwherefname='A'; fname传入A'OR1='1 mysql>select*fromt_accountwherefname='A'OR1='1';
攻击者更聪明一点：fname传入A'+'B，fpassword传入ccc'+0：
mysql>select*fromt_accountwherefname='A'+'B'andfpassword='ccc'+0; +-----+-----------+-------------+ |fid|fname|fpassword| +-----+-----------+-------------+ |1|xiaoming|p_xiaoming| |2|xiaoming1|p_xiaoming1| +-----+-----------+-------------+ 2rowsinset,7warnings(0.00sec)
总结
以上就是为大家总结的MySQL隐式类型的转换陷阱和规则，希望这篇文章对大家学习或者mysql能有所帮助，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。