最近一周接連處理了2個(gè)由于int向varchar轉(zhuǎn)換無法使用索引，從而引發(fā)的慢查詢。
| 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條件中不加''，會(huì)引發(fā)全表查詢，加了就可以用到索引，這掃描的行數(shù)可是天差地別，對于服務(wù)器的壓力和響應(yīng)時(shí)間自然也是天差地別的。
我們再看另外一個(gè)例子：
| *************************** 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 |
