程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL隱式類型的轉換圈套和規矩

MySQL隱式類型的轉換圈套和規矩

編輯:MySQL綜合教程

MySQL隱式類型的轉換圈套和規矩。本站提示廣大學習愛好者:(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) ,並且假如選擇後者時,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_idvarchar,爾後面的比擬值是整型。

處理該成績就是防止湧現隱式類型轉換(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能有所贊助,假如有疑問年夜家可以留言交換,感謝年夜家對的支撐。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved