程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> MySQL數據庫中把int轉化varchar引發的慢查詢

MySQL數據庫中把int轉化varchar引發的慢查詢

編輯：關於MYSQL數據庫

最近一周接連處理了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引發的慢查詢，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對網站的支持！