Mysql应用MySQL隐式类型的转换陷阱和规则

发布时间：2020-12-12 01:08:01 所属栏目：MySql教程来源：网络整理

导读：《Mysql应用MySQL隐式类型的转换陷阱和规则》要点：本文介绍了Mysql应用MySQL隐式类型的转换陷阱和规则，希望对您有用。如果有疑问，可以联系我们。 MYSQL必读前言 MYSQL必读相信大家都知道隐式类型转换有无法命中索引的风险,在高并发、大数据量的情况下,

《Mysql应用MySQL隐式类型的转换陷阱和规则》要点：
本文介绍了Mysql应用MySQL隐式类型的转换陷阱和规则，希望对您有用。如果有疑问，可以联系我们。

MYSQL必读前言

MYSQL必读相信大家都知道隐式类型转换有无法命中索引的风险,在高并发、大数据量的情况下,命不中索引带来的后果非常严重.将数据库拖死,继而整个系统崩溃,对于大规模系统损失惨重.所以下面通过本文来好好学习下MySQL隐式类型的转换陷阱和规则.

MYSQL必读1. 隐式类型转换实例

MYSQL必读今天生产库上突然出现MySQL线程数告警,IOPS很高,实例会话里面出现许多类似下面的sql：(修改了相关字段和值)

MYSQL必读
SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 and f_col2_id=1244378 and 
f_qq1_id in (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233)

MYSQL必读用 explain 看了下扫描行数和索引选择情况：

MYSQL必读
mysql>explain SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 
and f_col2_id=1244378 and f_qq1_id in (12345,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 | Using index condition; Using where |
+------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+
共返回 1 行记录,花费 11.52 ms.

MYSQL必读t_tb1 表上有个索引uid_type_frid(f_col2_id,f_type) 、idx_corp_id_qq1id(f_col1_id,f_qq1_id),而且如果选择后者时,f_qq1_id的过滤效果应该很佳,但却选择了前者.当使用 hint use index(idx_corp_id_qq1id)时：

MYSQL必读
mysql>explain extended SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 use index(idx_corpid_qq1id) WHERE f_col1_id=1226391 and f_col2_id=1244378 and f_qq1_id in (12345,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  | Using index condition; Using where |
+---- -+---------------+--------+--------+---------------------+------------------+------------+----------+-------------+------------------------------------+
共返回 1 行记录,花费 17.48 ms.
mysql>show warnings;
+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+
| Level   | Code   | Message                            |
+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+
| Warning   |   1739 | Cannot use range access on index 'idx_corpid_qq1id' due to type or collation conversion on field '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` USE INDEX (`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,901233)))          |
+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+
共返回 2 行记录,花费 10.81 ms.

MYSQL必读rows列达到200w行,但问题也发现了：select_type应该是 range 才对,key_len看出来只用到了idx_corpid_qq1id索引的第一列.上面explain使用了 extended,所以show warnings;可以很明确的看到 f_qq1_id 出现了隐式类型转换：f_qq1_id是varchar,而后面的比较值是整型.

MYSQL必读解决该问题就是避免出现隐式类型转换(implicit type conversion)带来的不可控：把f_qq1_id in的内容写成字符串：

MYSQL必读
mysql>explain SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 and f_col2_id=1244378 and 
f_qq1_id in ('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  | Using index condition; Using where |
+-------+---------------+--------+---------+--------------------------------+------------------+-------------+---------+---------+------------------------------------+
共返回 1 行记录,花费 12.41 ms.

MYSQL必读扫描行数从1386减少为40.

MYSQL必读类似的还出现过一例：

MYSQL必读
SELECT count(0) FROM d_dbname.t_tb2 where f_col1_id= '1931231' AND f_phone in(098890);
| Warning | 1292 | Truncated incorrect DOUBLE value: '1512-98464356'

MYSQL必读优化后直接从扫描rows 100w行降为1.

MYSQL必读借这个机会,系统的来看一下mysql中的隐式类型转换.

MYSQL必读2. mysql隐式转换规则

MYSQL必读2.1 规则

MYSQL必读下面来分析一下隐式转换的规则：

MYSQL必读???? a. 两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换

MYSQL必读???? b. 两个参数都是字符串,会按照字符串来比较,不做类型转换

MYSQL必读???? c. 两个参数都是整数,按照整数来比较,不做类型转换

MYSQL必读???? d. 十六进制的值和非数字做比较时,会被当做二进制串

MYSQL必读???? e. 有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp

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

MYSQL必读???? g. 所有其他情况下,两个参数都会被转换为浮点数再进行比较

MYSQL必读
mysql> select 11 + '11',11 + 'aa','a1' + 'bb',11 + '0.01a'; 
+-----------+-----------+-------------+--------------+
| 11 + '11' | 11 + 'aa' | 'a1' + 'bb' | 11 + '0.01a' |
+-----------+-----------+-------------+--------------+
|  22 |  11 |   0 |  11.01 |
+-----------+-----------+-------------+--------------+
1 row in set,4 warnings (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message         |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'aa' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a1' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'bb' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '0.01a' |
+---------+------+-------------------------------------------+
4 rows in set (0.00 sec)
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 |
+------------+-------------+---------------+----------+
1 row in set,1 warning (0.01 sec)

MYSQL必读上面可以看出11 + 'aa',由于操作符两边的类型不一样且符合第g条,aa要被转换成浮点型小数,然而转换失败(字母被截断),可以认为转成了 0,整数11被转成浮点型还是它自己,所以11 + 'aa' = 11.

MYSQL必读0.01a转成double型也是被截断成0.01,所以11 + '0.01a' = 11.01.

MYSQL必读等式比较也说明了这一点,'11a'和'11.0'转换后都等于 11,这也正是文章开头实例为什么没走索引的原因： varchar型的f_qq1_id,转换成浮点型比较时,等于 12345 的情况有无数种如12345a、12345.b等待,MySQL优化器无法确定索引是否更有效,所以选择了其它方案.

MYSQL必读但并不是只要出现隐式类型转换,就会引起上面类似的性能问题,最终是要看转换后能否有效选择索引.像f_id = '654321' 、f_mtime between '2016-05-01 00:00:00' and '2016-05-04 23:59:59'就不会影响索引选择,因为前者f_id是整型,即使与后面的字符串型数字转换成double比较,依然能根据double确定f_id的值,索引依然有效.后者是因为符合第e条,只是右边的常量做了转换.

MYSQL必读开发人员可能都只要存在这么一个隐式类型转换的坑,但却又经常不注意,所以干脆无需记住那么多规则,该什么类型就与什么类型比较.

MYSQL必读2.2 隐式类型转换的安全问题

MYSQL必读implicit type conversion 不仅可能引起性能问题,还有可能产生安全问题.

MYSQL必读
mysql> desc t_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 * from t_account;
+-----+-----------+-------------+
| fid | fname  | fpassword |
+-----+-----------+-------------+
| 1 | xiaoming | p_xiaoming |
| 2 | xiaoming1 | p_xiaoming1 |
+-----+-----------+-------------+

MYSQL必读假如应用前端没有WAF防护,那么下面的sql很容易注入：

MYSQL必读
mysql> select * from t_account where fname='A' ;
fname传入 A' OR 1='1 
mysql> select * from t_account where fname='A' OR 1='1';

MYSQL必读攻击者更聪明一点： fname传入 A'+'B,fpassword传入 ccc'+0 ：

MYSQL必读
mysql> select * from t_account where fname='A'+'B' and fpassword='ccc'+0;
+-----+-----------+-------------+
| fid | fname  | fpassword |
+-----+-----------+-------------+
| 1 | xiaoming | p_xiaoming |
| 2 | xiaoming1 | p_xiaoming1 |
+-----+-----------+-------------+
2 rows in set,7 warnings (0.00 sec)

MYSQL必读总结

MYSQL必读以上就是为大家总结的MySQL隐式类型的转换陷阱和规则,希望这篇文章对大家学习或者mysql能有所帮助,如果有疑问大家可以留言交流,谢谢大家对编程之家PHP的支持.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!