前言
相信大家都知道隱式類型轉換有無法命中索引的風險,在高並發、大數據量的情況下,命不中索引帶來的後果非常嚴重。將數據庫拖死,繼而整個系統崩潰,對於大規模系統損失慘重。所以下面通過本文來好好學習下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)
,而且如果選擇後者時,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
後進行比較,如果另外一個參數是浮點數,則會把 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'
就不會影響索引選擇,因為前者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)
總結
以上就是為大家總結的MySQL隱式類型的轉換陷阱和規則,希望這篇文章對大家學習或者mysql能有所幫助,如果有疑問大家可以留言交流,謝謝大家對的支持。