小心MySQL的隐式类型转换陷阱
今天生产库上突然出现MySQL线程数告警,IOPS很高,实例会话里面出现许多类似下面的sql:(修改了相关字段和值)
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)
用 explain 看了下扫描行数和索引选择情况:
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,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 | Using index condition; Using where | +------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+ 共返回 1 行记录,花费 11.52 ms.
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 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,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 | 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,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233))) | +-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+ 共返回 2 行记录,花费 10.81 ms.
rows列达到200w行,但问题也发现了:select_type应该是 range 才对,key_len看出来只用到了idx_corpid_qq1id索引的第一列。上面explain使用了 extended,所以show warnings;可以很明确的看到 f_qq1_id 出现了隐式类型转换:f_qq1_id是varchar,而后面的比较值是整型。
解决该问题就是避免出现隐式类型转换(implicit type conversion)带来的不可控:把f_qq1_id in的内容写成字符串:
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.
扫描行数从1386减少为40。
类似的还出现过一例:
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
优化后直接从扫描rows 100w行降为1。
借这个机会,系统的来看一下mysql中的隐式类型转换。
2. mysql隐式转换规则 2.1 规则下面来分析一下隐式转换的规则:
两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 = 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换
有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
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)
上面可以看出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_mtime between 2016-05-01 00:00:00 and 2016-05-04 23:59:59就不会影响索引选择,因为前者f_id是整型,即使与后面的字符串型数字转换成double比较,依然能根据double确定f_id的值,索引依然有效。后者是因为符合第e条,只是右边的常量做了转换。
开发人员可能都只要存在这么一个隐式类型转换的坑,但却又经常不注意,所以干脆无需记住那么多规则,该什么类型就与什么类型比较。
2.2 隐式类型转换的安全问题implicit type conversion 不仅可能引起性能问题,还有可能产生安全问题。
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 | +-----+-----------+-------------+ 假如应用前端没有WAF防护,那么下面的sql很容易注入: mysql select * from t_account where fname=A ; fname传入 A OR 1=1 mysql select * from t_account where fname=A OR 1=1;
攻击者更聪明一点: fname传入 A+B ,fpassword传入 ccc+0 :
全选复制放进笔记 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)
官方解读: 1.如果一个或两个参数都是NULL,比较的结果是NULL,除了NULL安全的相等比较运算符。对于NULL NULL,结果为true。不需要转换 2.如果比较操作中的两个参数都是字符串,则将它们作为字符串进行比较。
相关文章
- 转换MySQL数据表类型转换: 极速变化、妙用无穷(mysql数据表类型)
- 学习MySQL:取决于你有多快!(mysql要学多久)
- MySQL字段类型转换:实现正确的数据格式(mysql字段类型转换)
- MySQL:快速安装的免安装包(mysql免安装包)
- 的优点MySQL存储字符串的优势分享(mysql存字符串)
- MySQL数据类型转换:简单有效的数据转换方式(mysql数据类型转换)
- 数据PHP操作MySQL:删除数据(php删除mysql)
- 安装MySQL,实现数据管理的梦想(能安装的mysql)
- 通过MySQL二级考试的秘诀(mysql二级考试)
- MySQL如何配置ini文件(mysql配置ini)
- 解读:MySQL数据库在分区技术方面的不足(mysql不支持分区)
- MySQL中常用数据类型转换实现(mysql数据类型转化)
- MySQL中类型转换实现技巧(mysql类型转换)
- 深入剖析MySQL:实用案例分析探究数据库技术(mysql案例分析)
- MySQL参数详解,让你更加了解Mysql参数定义及优化。(mysql参数定义)
- MySQL索引:优化查询效率的首选(mysql建立索引)
- MySQL如何将字符串类型转换成整型(mysql 转整形)
- MySQL使用详解:全面学习MYSQL技术(mysql大全)
- MySQL 中快速删除表数据的方法(mysql快速删除表数据)
- 警惕MySQL中SQL语句的陷阱(mysql中sql语句坑)
- 数据库中的MySQL 理解Mysql数据库的重要性(MySql中mysql)
- MySQL中CAST将数据类型转换为你想要的类型(mysql 中cast)
- 掌握App接入MySQL的技术(app如何接入mysql)
- MySQL数据库对dat文件的处理(.dat mysql)
- MySQL教程使用TRIM函数去掉字符串中的空格(mysql中去掉空格函数)
- MYSQL收费了 了解MySQL开源版的最新政策(mysql不再免费了吗)
- MySQL下载后如何安装和配置(mysql下载过后怎么办)
- MYSQL下载电视剧片段教你轻松获取你想要的剧集片段,让你更便利地观看喜爱的电视剧
- MySQL下载及安装教程快速掌握MySql下载及安装方法,更高效地使用MySql数据库(mysql下载了在哪)
- MySQL试用不可用,需要一个许可证(mysql不能点击试用了)