为什么分区成功了还是全表扫描?求大牛解答。
夜灯下的人 2014-07-04 11:02:33 首先看来看分区和表结构:
CREATE TABLE `tj_user_login_detail` (
`uid` varchar(64) NOT NULL COMMENT '用户id',
`passport` varchar(50) NOT NULL COMMENT '用户账号',
`game_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '游戏id',
`referer` varchar(32) NOT NULL COMMENT '广告渠道',
`partner_id` bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT '联运商',
`device_no` char(32) NOT NULL COMMENT '设备序号',
`ip` varchar(64) NOT NULL COMMENT '登录ip',
`login_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '登录时间',
`logindate` int(11) NOT NULL DEFAULT '0' COMMENT '登录日期',
`belong_no` varchar(32) NOT NULL DEFAULT '' COMMENT '归属',
`reg_referer` varchar(32) NOT NULL DEFAULT '' COMMENT '注册渠道(归属)',
`reg_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '注册时间(归属)',
`version` varchar(32) NOT NULL COMMENT 'sdk版本',
KEY `uid` (`uid`),
KEY `dategameref` (`logindate`,`game_id`,`referer`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (logindate)
(PARTITION p201309 VALUES LESS THAN (20131000) ENGINE = MyISAM,
PARTITION p201310 VALUES LESS THAN (20131100) ENGINE = MyISAM,
PARTITION p201311 VALUES LESS THAN (20131200) ENGINE = MyISAM,
PARTITION p201312 VALUES LESS THAN (20140100) ENGINE = MyISAM,
PARTITION p201401 VALUES LESS THAN (20140200) ENGINE = MyISAM,
PARTITION p201402 VALUES LESS THAN (20140300) ENGINE = MyISAM,
PARTITION p201403 VALUES LESS THAN (20140400) ENGINE = MyISAM,
PARTITION p201404 VALUES LESS THAN (20140500) ENGINE = MyISAM,
PARTITION p201405 VALUES LESS THAN (20140600) ENGINE = MyISAM,
PARTITION p201406 VALUES LESS THAN (20140700) ENGINE = MyISAM,
PARTITION p201407 VALUES LESS THAN (20140800) ENGINE = MyISAM,
PARTITION p201408 VALUES LESS THAN (20140900) ENGINE = MyISAM,
PARTITION p201409 VALUES LESS THAN (20141000) ENGINE = MyISAM,
PARTITION p201410 VALUES LESS THAN (20141100) ENGINE = MyISAM,
PARTITION p201411 VALUES LESS THAN (20141200) ENGINE = MyISAM,
PARTITION p201412 VALUES LESS THAN (20150100) ENGINE = MyISAM) */
分完区后导入数据,全表一共5900W行,但是查询时,没有在条件分区 里进行查询而是扫描了全表 :
explain partitions select count(*) as aa from tj_user_login_detail where logindate > 20140600 and logindate < 20140800 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tj_user_login_detail
partitions: p201406,p201407
type: index
possible_keys: dategameref
key: dategameref
key_len: 106
ref: NULL
rows: 59924989
Extra: Using where; Using index
1 row in set (0.00 sec)
分完区后导入数据,全表一共5900W行,但是查询时,没有在条件分区 里进行查询而是扫描了全表 ,分区 6月份和7月份最多800W行,但为什么还是全表扫描了?这里不明,求大牛指点?