MySQL数据库中把int转化varchar引发的慢查询

2024-03-16 03:18:02 150

最近一周接连处理了2个由于int向varchar转换无法使用索引，从而引发的慢查询。

CREATETABLE`appstat_day_prototype_201305`(
`day_key`dateNOTNULLDEFAULT'1900-01-01',
`appkey`varchar(20)NOTNULLDEFAULT'',
`user_total`bigint(20)NOTNULLDEFAULT'0',
`user_activity`bigint(20)NOTNULLDEFAULT'0',
`times_total`bigint(20)NOTNULLDEFAULT'0',
`times_activity`bigint(20)NOTNULLDEFAULT'0',
`incr_login_daily`bigint(20)NOTNULLDEFAULT'0',
`unbind_total`bigint(20)NOTNULLDEFAULT'0',
`unbind_activitys`bigint(20)NOTNULLDEFAULT'0',
PRIMARYKEY(`appkey`,`day_key`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8
mysql>explainSELECT*fromappstat_day_prototype_201305whereappkey=xxxxxandday_keybetween'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|Usingwhere|
+----+-------------+------------------------------+------+---------------+------+---------+------+----------+-------------+
1rowinset(0.00sec)
mysql>explainSELECT*fromappstat_day_prototype_201305whereappkey='xxxxx'andday_keybetween'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|Usingwhere|
+----+-------------+------------------------------+-------+---------------+---------+---------+------+------+-------------+
1rowinset(0.00sec)

从上面可以很明显的看到由于appkey是varchar，而在where条件中不加''，会引发全表查询，加了就可以用到索引，这扫描的行数可是天差地别，对于服务器的压力和响应时间自然也是天差地别的。

我们再看另外一个例子：

***************************1.row***************************
Table:poll_joined_151
CreateTable:CREATETABLE`poll_joined_151`(
`poll_id`bigint(11)NOTNULL,
`uid`bigint(11)NOTNULL,
`item_id`varchar(60)NOTNULL,
`add_time`int(11)NOTNULLDEFAULT'0',
`anonymous`tinyint(1)NOTNULLDEFAULT'0',
`sub_item`varchar(1200)NOTNULLDEFAULT'',
KEY`idx_poll_id_uid_add_time`(`poll_id`,`uid`,`add_time`),
KEY`idx_anonymous_id_addtime`(`anonymous`,`poll_id`,`add_time`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8
SELECT*FROMpoll_joined_151WHEREpoll_id='2348993'ANDanonymous=0ORDERBYadd_timeDESCLIMIT0,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:Usingwhere

从上面的例子看，虽然poll_id的类型为bigint，但是SQL中添加了''，但是这个语句仍然用到了索引，虽然扫描行数也不少，但是能用到索引就是好SQL。

那么一个小小的''为什么会有这么大的影响呢？根本原因是因为MySQL在对文本类型和数字类型进行比较的时候会进行隐式的类型转换。

以下是5.5官方手册的说明：

Ifbothargumentsinacomparisonoperationarestrings,theyarecomparedasstrings.
两个参数都是字符串，会按照字符串来比较，不做类型转换。
Ifbothargumentsareintegers,theyarecomparedasintegers.
两个参数都是整数，按照整数来比较，不做类型转换。
Hexadecimalvaluesaretreatedasbinarystringsifnotcomparedtoanumber.
十六进制的值和非数字做比较时，会被当做二进制串。
IfoneoftheargumentsisaTIMESTAMPorDATETIMEcolumnandtheotherargumentisaconstant,theconstantisconvertedtoatimestampbeforethecomparisonisperformed.ThisisdonetobemoreODBC-friendly.NotethatthisisnotdonefortheargumentstoIN()!Tobesafe,alwaysusecompletedatetime,date,ortimestringswhendoingcomparisons.Forexample,toachievebestresultswhenusingBETWEENwithdateortimevalues,useCAST()toexplicitlyconvertthevaluestothedesireddatatype.
有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp
Ifoneoftheargumentsisadecimalvalue,comparisondependsontheotherargument.Theargumentsarecomparedasdecimalvaluesiftheotherargumentisadecimalorintegervalue,orasfloating-pointvaluesiftheotherargumentisafloating-pointvalue.
有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较，如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较
Inallothercases,theargumentsarecomparedasfloating-point(real)numbers.所有其他情况下，两个参数都会被转换为浮点数再进行比较

根据以上的说明，当where条件之后的值的类型和表结构不一致的时候，MySQL会做隐式的类型转换，都将其转换为浮点数在比较。

对于第一种情况：

比如wherestring=1；

需要将索引中的字符串转换成浮点数，但是由于'1','1','1a'都会比转化成1,故MySQL无法使用索引只能进行全表扫描，故造成了慢查询的产生。

mysql>SELECTCAST('1'ASSIGNED)=1;
+-------------------------+
|CAST('1'ASSIGNED)=1|
+-------------------------+
|1|
+-------------------------+
1rowinset(0.00sec)
mysql>SELECTCAST('1a'ASSIGNED)=1;
+--------------------------+
|CAST('1a'ASSIGNED)=1|
+--------------------------+
|1|
+--------------------------+
1rowinset,1warning(0.00sec)
mysql>SELECTCAST('1'ASSIGNED)=1;
+-----------------------+
|CAST('1'ASSIGNED)=1|
+-----------------------+
|1|
+-----------------------+
1rowinset(0.00sec)

同时需要注意一点，由于都会转换成浮点数进行比较，而浮点数只有53bit，故当超过最大值的时候，比较会出现问题。

对于第二种情况：

由于索引建立在int的基础上，而将纯数字的字符串可以百分百转换成数字，故可以使用到索引，虽然也会进行一定的转换，消耗一定的资源，但是最终仍然使用了索引，不会产生慢查询。

mysql>selectCAST('30'asSIGNED)=30;
+----------------------------+
|CAST('30'asSIGNED)=30|
+----------------------------+
|1|
+----------------------------+
1rowinset(0.00sec)

以上所述是小编给大家介绍的MySQL数据库中把int转化varchar引发的慢查询，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！

MySQL数据库中把int转化varchar引发的慢查询

热门推荐

随机推荐