《Mysql應(yīng)用MySQL隱式類型的轉(zhuǎn)換陷阱和規(guī)則》要點(diǎn)：
本文介紹了Mysql應(yīng)用MySQL隱式類型的轉(zhuǎn)換陷阱和規(guī)則，希望對您有用。如果有疑問，可以聯(lián)系我們。
MYSQL必讀前言
MYSQL必讀相信大家都知道隱式類型轉(zhuǎn)換有無法命中索引的風(fēng)險(xiǎn),在高并發(fā)、大數(shù)據(jù)量的情況下,命不中索引帶來的后果非常嚴(yán)重.將數(shù)據(jù)庫拖死,繼而整個(gè)系統(tǒng)崩潰,對于大規(guī)模系統(tǒng)損失慘重.所以下面通過本文來好好學(xué)習(xí)下MySQL隱式類型的轉(zhuǎn)換陷阱和規(guī)則.
MYSQL必讀1. 隱式類型轉(zhuǎn)換實(shí)例
MYSQL必讀今天生產(chǎn)庫上突然出現(xiàn)MySQL線程數(shù)告警,IOPS很高,實(shí)例會話里面出現(xiàn)許多類似下面的sql：(修改了相關(guān)字段和值)
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 看了下掃描行數(shù)和索引選擇情況：
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,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 行記錄,花費(fèi) 11.52 ms.
MYSQL必讀t_tb1 表上有個(gè)索引uid_type_frid(f_col2_id,f_type) 、idx_corp_id_qq1id(f_col1_id,f_qq1_id) ,而且如果選擇后者時(shí),f_qq1_id的過濾效果應(yīng)該很佳,但卻選擇了前者.當(dāng)使用 hint use index(idx_corp_id_qq1id)時(shí)：
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,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 行記錄,花費(fèi) 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 行記錄,花費(fèi) 10.81 ms.
MYSQL必讀rows列達(dá)到200w行,但問題也發(fā)現(xiàn)了：select_type應(yīng)該是 range 才對,key_len看出來只用到了idx_corpid_qq1id索引的第一列.上面explain使用了 extended,所以show warnings;可以很明確的看到 f_qq1_id 出現(xiàn)了隱式類型轉(zhuǎn)換：f_qq1_id是varchar,而后面的比較值是整型.
MYSQL必讀解決該問題就是避免出現(xiàn)隱式類型轉(zhuǎn)換(implicit type conversion)帶來的不可控：把f_qq1_id in的內(nèi)容寫成字符串：
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 行記錄,花費(fèi) 12.41 ms.
MYSQL必讀掃描行數(shù)從1386減少為40.
MYSQL必讀類似的還出現(xiàn)過一例：
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必讀優(yōu)化后直接從掃描rows 100w行降為1.
MYSQL必讀借這個(gè)機(jī)會,系統(tǒng)的來看一下mysql中的隱式類型轉(zhuǎn)換.
MYSQL必讀2. mysql隱式轉(zhuǎn)換規(guī)則
MYSQL必讀2.1 規(guī)則
MYSQL必讀下面來分析一下隱式轉(zhuǎn)換的規(guī)則：
MYSQL必讀???? a. 兩個(gè)參數(shù)至少有一個(gè)是 NULL 時(shí),比較的結(jié)果也是 NULL,例外是使用 <=> 對兩個(gè) NULL 做比較時(shí)會返回 1,這兩種情況都不需要做類型轉(zhuǎn)換
MYSQL必讀???? b. 兩個(gè)參數(shù)都是字符串,會按照字符串來比較,不做類型轉(zhuǎn)換
MYSQL必讀???? c. 兩個(gè)參數(shù)都是整數(shù),按照整數(shù)來比較,不做類型轉(zhuǎn)換
MYSQL必讀???? d. 十六進(jìn)制的值和非數(shù)字做比較時(shí),會被當(dāng)做二進(jìn)制串
MYSQL必讀???? e. 有一個(gè)參數(shù)是 TIMESTAMP 或 DATETIME,并且另外一個(gè)參數(shù)是常量,常量會被轉(zhuǎn)換為 timestamp
MYSQL必讀???? f. 有一個(gè)參數(shù)是 decimal 類型,如果另外一個(gè)參數(shù)是 decimal 或者整數(shù),會將整數(shù)轉(zhuǎn)換為 decimal 后進(jìn)行比較,如果另外一個(gè)參數(shù)是浮點(diǎn)數(shù),則會把 decimal 轉(zhuǎn)換為浮點(diǎn)數(shù)進(jìn)行比較
MYSQL必讀???? g. 所有其他情況下,兩個(gè)參數(shù)都會被轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較
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要被轉(zhuǎn)換成浮點(diǎn)型小數(shù),然而轉(zhuǎn)換失敗(字母被截?cái)?,可以認(rèn)為轉(zhuǎn)成了 0,整數(shù)11被轉(zhuǎn)成浮點(diǎn)型還是它自己,所以11 + 'aa' = 11.
MYSQL必讀0.01a轉(zhuǎn)成double型也是被截?cái)喑?.01,所以11 + '0.01a' = 11.01.
MYSQL必讀等式比較也說明了這一點(diǎn),'11a'和'11.0'轉(zhuǎn)換后都等于 11,這也正是文章開頭實(shí)例為什么沒走索引的原因： varchar型的f_qq1_id,轉(zhuǎn)換成浮點(diǎn)型比較時(shí),等于 12345 的情況有無數(shù)種如12345a、12345.b等待,MySQL優(yōu)化器無法確定索引是否更有效,所以選擇了其它方案.
MYSQL必讀但并不是只要出現(xiàn)隱式類型轉(zhuǎn)換,就會引起上面類似的性能問題,最終是要看轉(zhuǎn)換后能否有效選擇索引.像f_id = '654321' 、f_mtime between '2016-05-01 00:00:00' and '2016-05-04 23:59:59'就不會影響索引選擇,因?yàn)榍罢遞_id是整型,即使與后面的字符串型數(shù)字轉(zhuǎn)換成double比較,依然能根據(jù)double確定f_id的值,索引依然有效.后者是因?yàn)榉系趀條,只是右邊的常量做了轉(zhuǎn)換.
MYSQL必讀開發(fā)人員可能都只要存在這么一個(gè)隱式類型轉(zhuǎn)換的坑,但卻又經(jīng)常不注意,所以干脆無需記住那么多規(guī)則,該什么類型就與什么類型比較.
MYSQL必讀2.2 隱式類型轉(zhuǎn)換的安全問題
MYSQL必讀implicit type conversion 不僅可能引起性能問題,還有可能產(chǎn)生安全問題.
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必讀假如應(yīng)用前端沒有WAF防護(hù),那么下面的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必讀攻擊者更聰明一點(diǎn)： 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必讀總結(jié)
MYSQL必讀以上就是為大家總結(jié)的MySQL隱式類型的轉(zhuǎn)換陷阱和規(guī)則,希望這篇文章對大家學(xué)習(xí)或者mysql能有所幫助,如果有疑問大家可以留言交流,謝謝大家對維易PHP的支持.
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/3671.html
