最近一周接连处理了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=utf8mysql> 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_151Create 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=utf8SELECT * FROM poll_joined_151 WHERE poll_id = '2348993' AND anonymous =0 ORDER BY add_time DESC LIMIT 0 , 3*************************** 1. row ***************************id: 1select_type: SIMPLEtable: poll_joined_151type: refpossible_keys: idx_poll_id_uid_add_time,idx_anonymous_id_addtimekey: idx_anonymous_id_addtimekey_len: 9ref: const,constrows: 30240Extra: Using where |
