表结构CREATE TABLE `p_test` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`beginTime` datetime DEFAULT NULL,
`data` varchar(32) NULL,
PRIMARY KEY (`Id`, `beginTime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 PARTITION BY RANGE (TO_DAYS(beginTime)) (
/*2011*/
PARTITION p201107 VALUES LESS THAN (TO_DAYS('2011-08-01')),
PARTITION p201108 VALUES LESS THAN (TO_DAYS('2011-09-01')),
PARTITION p201109 VALUES LESS THAN (TO_DAYS('2011-10-01')),
PARTITION p201110 VALUES LESS THAN (TO_DAYS('2011-11-01')),
PARTITION p201111 VALUES LESS THAN (TO_DAYS('2011-12-01')),
PARTITION p201112 VALUES LESS THAN (TO_DAYS('2012-01-01')),
);
上面的表按年月,进行了分区EXPLAIN PARTITIONS SELECT * FROM `p_test` WHERE beginTime >= '2011-10-01 00:00:01' AND beginTime < '2011-12-01 00:00:01'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: p_test
partitions: p201107,p201110,p201111,p201112
type: range
possible_keys: idx_beginTime
key: idx_beginTime
key_len: 8
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)上面的语句,beginTime指定的范围是10到12月,按我的理解,它只扫描p201110, p201111, p201112分区才对,但是实际情况是它还扫描了p201107分区,请问各位这是为什么啊?
`Id` int(11) NOT NULL AUTO_INCREMENT,
`beginTime` datetime DEFAULT NULL,
`data` varchar(32) NULL,
PRIMARY KEY (`Id`, `beginTime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 PARTITION BY RANGE (TO_DAYS(beginTime)) (
/*2011*/
PARTITION p201107 VALUES LESS THAN (TO_DAYS('2011-08-01')),
PARTITION p201108 VALUES LESS THAN (TO_DAYS('2011-09-01')),
PARTITION p201109 VALUES LESS THAN (TO_DAYS('2011-10-01')),
PARTITION p201110 VALUES LESS THAN (TO_DAYS('2011-11-01')),
PARTITION p201111 VALUES LESS THAN (TO_DAYS('2011-12-01')),
PARTITION p201112 VALUES LESS THAN (TO_DAYS('2012-01-01')),
);
上面的表按年月,进行了分区EXPLAIN PARTITIONS SELECT * FROM `p_test` WHERE beginTime >= '2011-10-01 00:00:01' AND beginTime < '2011-12-01 00:00:01'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: p_test
partitions: p201107,p201110,p201111,p201112
type: range
possible_keys: idx_beginTime
key: idx_beginTime
key_len: 8
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)上面的语句,beginTime指定的范围是10到12月,按我的理解,它只扫描p201110, p201111, p201112分区才对,但是实际情况是它还扫描了p201107分区,请问各位这是为什么啊?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货