我试图分区表,这是它的结构:
CREATE TABLE IF NOT EXISTS `actions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uuid` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`username` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
`event` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
`obj_id` int(11) unsigned NOT NULL,
`inline` text COLLATE utf8_unicode_ci NOT NULL,
`meta` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`,`date`),
KEY `event` (`event`),
KEY `obj_id` (`obj_id`),
KEY `username` (`username`),
KEY `date` (`date`),
KEY `user_date` (`username`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY RANGE ( UNIX_TIMESTAMP(date))
(PARTITION actions_0 VALUES LESS THAN (1209592800) ENGINE = InnoDB, # 2008-05-01
PARTITION actions_images_1 VALUES LESS THAN (1212271200) ENGINE = InnoDB, # 2008-06-01
[…]
PARTITION actions_67 VALUES LESS THAN (1388530800) ENGINE = InnoDB,
PARTITION actions_68 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)现在,当我选择特定的日期时,它似乎很好:
EXPLAIN PARTITIONS SELECT * FROM `actions` WHERE `date` = ‘2008-05-04 16:13:15’ ;
+—-+————-+—————-+——————
| id | select_type | table | partitions
+—-+————-+—————-+——————
| 1 | SIMPLE | actions | actions_1但如果我做这个查询:
EXPLAIN PARTITIONS SELECT * FROM `actions` WHERE `date` > ‘2008-05-04 16:13:15’ AND `date` < ‘2008-05-05 16:13:15’;Mysql扫描所有的actions_N表,我做错了什么?
这是我第一次使用分区,但我遵循手册,我无法找到我错误的地方。
谢谢!