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条件中不加'',会引发全表查询,加了就可以用到索引,这扫描的行数可是天差地别,对于服务器的压力和响应时间自然也是天差地别的。
虽然poll_id的类型为bigint,但是SQL中添加了'',但是这个语句仍然用到了索引,虽然扫描行数也不少,但是能用到索引就是好SQL。
那么一个小小的''为什么会有这么大的影响呢?根本原因是因为MySQL在对文本类型和数字类型进行比较的时候会进行隐式的类型转换。以下是5.5官方手册的说明……
根据以上的说明,当where条件之后的值的类型和表结构不一致的时候,MySQL会做隐式的类型转换,都将其转换为浮点数在比较。
对于第一种情况:
比如where string = 1;
需要将索引中的字符串转换成浮点数,但是由于'1',' 1','1a'都会比转化成1,故MySQL无法使用索引只能进行全表扫描,故造成了慢查询的产生。