MySQL隱式類型的轉換陷阱與規則_MySQL

前言

相信大家都知道隱式型別轉換有無法命中索引的風險，在高併發、大數據量的情況下，命不中索引帶來的後果非常嚴重。將資料庫拖死，繼而整個系統崩潰，對於大規模系統損失慘重。所以下面透過本文來好好學習下MySQL隱式類型的轉換陷阱和規則。

1. 隱式型別轉換實例

今天生產庫上突然出現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) ，而且如果選擇後者時，後者選擇了一個很好的過濾當使用 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 規則

下面來分析一下隱式轉換的規則：

     a. 兩個參數至少有一個是

NULL

時，比較的結果也是NULL，例外是使用<=> 對兩個NULL 做比較時會回傳1，這兩種情況都不需要做型別轉換
     b. 兩個參數都是字串，會依照字串來比較，不做型別轉換

     c. 兩個參數都是整數，依照整數來比較，不做型別轉換

     d. 十六進制的值和非數字做比較時，會被當作二進位串

     e. 有參數是

TIMESTAMP

或 DATETIME，而另一個參數是常數，常數會轉換為 timestamp
     f. 有一個參數是

decimal

型，如果另一個參數是decimal 或整數，會將整數轉換成decimal 後進行比較，如果另外一個參數是浮點數為浮點數進行比較      g. 所有其他情況下，兩個參數都會轉換為浮點數再進行比較

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''2016-05-04 23:59:59''2016-05-04 23:59:59''2016-05-04 23:59:59''2016-05-04 23:59:59''2016-05-04 23:59:59'

'2016-05-04 23:59:59' 是是整數，即使與後面的字串型數字轉換成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)

总结

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