DROP TABLE IF EXISTS `part_test`;
CREATE TABLE `part_test` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
PRIMARY KEY (`id`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=5 ;--
-- 导出表中的数据 `part_test`
-- INSERT INTO `part_test` (`id`, `date`) VALUES
(3, '2012-01-03'),
(4, '2012-01-22');--分区
ALTER TABLE `part_test` PARTITION BY RANGE(yearweek(date)) (
PARTITION p201201 VALUES LESS THAN (201202),
PARTITION p201202 VALUES LESS THAN (201203),
PARTITION p201203 VALUES LESS THAN (201204),
PARTITION p201204 VALUES LESS THAN (201205)
);//下边应该是属于今年的第三个星期的也就是等于201203 ,预计查询不会访问第一分区(p201201)才对,但是执行结果是扫描了不该扫描的第一个分区mysql> explain partitions SELECT count(*) FROM `part_test` WHERE `date` > '2012-01-08';
+----+-------------+-----------+---------------------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+---------------------------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | part_test | p201201,p201202,p201203,p201204 | index | NULL | PRIMARY | 6 | NULL | 2 | Using where; Using index |
+----+-------------+-----------+---------------------------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)但是用TO_DAYS函数就符合我的扫描预期。DROP TABLE IF EXISTS `part_test`;
CREATE TABLE `part_test` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
PRIMARY KEY (`id`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=5 ;--
-- 导出表中的数据 `part_test`
-- INSERT INTO `part_test` (`id`, `date`) VALUES
(3, '2012-01-03'),
(4, '2012-01-22');ALTER TABLE `part_test` PARTITION BY RANGE(to_days(date)) (
PARTITION p201201 VALUES LESS THAN (to_days('2012-01-08')),
PARTITION p201202 VALUES LESS THAN (to_days('2012-01-15')),
PARTITION p201203 VALUES LESS THAN (to_days('2012-01-23')),
PARTITION p201204 VALUES LESS THAN (to_days('2012-01-30'))
);//下边应该是属于今年的第三个星期的也就是等于201203 ,符合预期
mysql> explain partitions SELECT count(*) FROM `part_test` WHERE `date` > '2012-01-16';
+----+-------------+-----------+-----------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-----------------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | part_test | p201203,p201204 | system | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-----------+-----------------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
另外付服务器版本信息mysql> \s
--------------
mysql Ver 14.14 Distrib 5.1.28-rc, for Win32 (ia32)
谢谢了。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货