MySQL數據庫中把int轉化varchar激發的慢查詢。本站提示廣大學習愛好者：（MySQL數據庫中把int轉化varchar激發的慢查詢）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL數據庫中把int轉化varchar激發的慢查詢正文
比來一周接連處置了2個因為int向varchar轉換沒法應用索引，從而激發的慢查詢。
CREATE TABLE `appstat_day_prototype_201305` ( `day_key` date NOT NULL DEFAULT '1900-01-01', `appkey` varchar(20) NOT NULL DEFAULT '', `user_total` bigint(20) NOT NULL DEFAULT '0', `user_activity` bigint(20) NOT NULL DEFAULT '0', `times_total` bigint(20) NOT NULL DEFAULT '0', `times_activity` bigint(20) NOT NULL DEFAULT '0', `incr_login_daily` bigint(20) NOT NULL DEFAULT '0', `unbind_total` bigint(20) NOT NULL DEFAULT '0', `unbind_activitys` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`appkey`,`day_key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> explain SELECT * from appstat_day_prototype_201305 where appkey = xxxxx and day_key between '2013-05-23' and '2013-05-30'; +----+-------------+------------------------------+------+---------------+------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------------+------+---------------+------+---------+------+----------+-------------+ | 1 | SIMPLE | appstat_day_prototype_201305 | ALL | PRIMARY | NULL | NULL | NULL | 19285787 | Using where | +----+-------------+------------------------------+------+---------------+------+---------+------+----------+-------------+ 1 row in set (0.00 sec) mysql> explain SELECT * from appstat_day_prototype_201305 where appkey = 'xxxxx' and day_key between '2013-05-23' and '2013-05-30'; +----+-------------+------------------------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | appstat_day_prototype_201305 | range | PRIMARY | PRIMARY | 65 | NULL | 1 | Using where | +----+-------------+------------------------------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
從下面可以很顯著的看到因為appkey是varchar，而在where前提中不加''，會激發全表查詢，加了便可以用到索引，這掃描的行數可是天差地別，關於辦事器的壓力和呼應時光天然也是天差地其余。
我們再看別的一個例子：
*************************** 1. row *************************** Table: poll_joined_151 Create Table: CREATE TABLE `poll_joined_151` ( `poll_id` bigint(11) NOT NULL, `uid` bigint(11) NOT NULL, `item_id` varchar(60) NOT NULL, `add_time` int(11) NOT NULL DEFAULT '0', `anonymous` tinyint(1) NOT NULL DEFAULT '0', `sub_item` varchar(1200) NOT NULL DEFAULT '', KEY `idx_poll_id_uid_add_time` (`poll_id`,`uid`,`add_time`), KEY `idx_anonymous_id_addtime` (`anonymous`,`poll_id`,`add_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 SELECT * FROM poll_joined_151 WHERE poll_id = '2348993' AND anonymous =0 ORDER BY add_time DESC LIMIT 0 , 3 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: poll_joined_151 type: ref possible_keys: idx_poll_id_uid_add_time,idx_anonymous_id_addtime key: idx_anonymous_id_addtime key_len: 9 ref: const,const rows: 30240 Extra: Using where
從下面的例子看，固然poll_id的類型為bigint，然則SQL中添加了''，然則這個語句依然用到了索引，固然掃描行數也很多，然則能用到索引就是好SQL。
那末一個小小的''為何會有這麼年夜的影響呢？基本緣由是由於MySQL在對文本類型和數字類型停止比擬的時刻會停止隱式的類型轉換。
以下是5.5官方手冊的解釋：
If both arguments in a comparison operation are strings, they are compared as strings. 兩個參數都是字符串，會依照字符串來比擬，不做類型轉換。 If both arguments are integers, they are compared as integers. 兩個參數都是整數，依照整數來比擬，不做類型轉換。 Hexadecimal values are treated as binary strings if not compared to a number. 十六進制的值和非數字做比擬時，會被當作二進制串。 If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. 有一個參數是 TIMESTAMP 或 DATETIME，而且別的一個參數是常量，常量會被轉換為 timestamp If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value. 有一個參數是 decimal 類型，假如別的一個參數是 decimal 或許整數，會將整數轉換為 decimal 落後行比擬，假如別的一個參數是浮點數，則會把 decimal 轉換為浮點數停止比擬 In all other cases, the arguments are compared as floating-point (real) numbers.一切其他情形下，兩個參數都邑被轉換為浮點數再停止比擬
依據以上的解釋，當where前提以後的值的類型和表構造紛歧致的時刻，MySQL會做隱式的類型轉換，都將其轉換為浮點數在比擬。
關於第一種情形：
好比where string = 1；
須要將索引中的字符串轉換成浮點數，然則因為'1',' 1','1a'都邑比轉化成1,故MySQL沒法應用索引只能停止全表掃描，故形成了慢查詢的發生。
mysql> SELECT CAST(' 1' AS SIGNED)=1;
+-------------------------+
| CAST(' 1' AS SIGNED)=1 |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT CAST(' 1a' AS SIGNED)=1;
+--------------------------+
| CAST(' 1a' AS SIGNED)=1 |
+--------------------------+
| 1 |
+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT CAST('1' AS SIGNED)=1;
+-----------------------+
| CAST('1' AS SIGNED)=1 |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
同時須要留意一點，因為都邑轉換成浮點數停止比擬，而浮點數只要53bit，故當跨越最年夜值的時刻，比擬會湧現成績。
關於第二種情形：
因為索引樹立在int的基本上，而將純數字的字符串可以百分百轉換成數字，故可使用到索引，固然也會停止必定的轉換，消費必定的資本，然則終究依然應用了索引，不會發生慢查詢。
mysql> select CAST( '30' as SIGNED) = 30; +----------------------------+ | CAST( '30' as SIGNED) = 30 | +----------------------------+ | 1 | +----------------------------+ 1 row in set (0.00 sec)
以上所述是小編給年夜家引見的MySQL數據庫中把int轉化varchar激發的慢查詢 ，願望對年夜家有所贊助，假如年夜家有任何疑問請給我留言，小編會實時答復年夜家的。在此也異常感激年夜家對網站的支撐！
