如何写sql才能利用到mysql分区表的相应分区?

Question

CREATE TABLE `virus_info` (
  `virus_id` VARCHAR(32) NOT NULL,
  `import_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY  (`virus_id`,`import_date`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE ( UNIX_TIMESTAMP(import_date) ) (
    PARTITION p201000 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p201001 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-02-01 00:00:00') ),
    PARTITION p201002 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-03-01 00:00:00') ),
    PARTITION p201003 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-04-01 00:00:00') ),
    PARTITION p201004 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-05-01 00:00:00') ),
    PARTITION p201005 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-06-01 00:00:00') ),
    PARTITION p201006 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-07-01 00:00:00') ),
    PARTITION p201007 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-08-01 00:00:00') ),
    PARTITION p201008 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-09-01 00:00:00') ),
    PARTITION p201009 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-10-01 00:00:00') ),
    PARTITION p201010 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-11-01 00:00:00') ),
    PARTITION p201011 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-12-01 00:00:00') ),
    PARTITION p201012 VALUES LESS THAN ( UNIX_TIMESTAMP('2011-01-01 00:00:00') ),
    PARTITION p201101 VALUES LESS THAN (MAXVALUE)
);INSERT INTO virus_info VALUES
('1',CURRENT_TIMESTAMP ),
('2','2010-01-02 00:00:00'),
('3','2010-02-02 00:00:00'),
('4','2010-03-02 00:00:00');EXPLAIN PARTITIONS SELECT * FROM virus_info WHERE import_date BETWEEN '2011-01-01 00:00:00' AND '2011-05-01 00:00:00';
查看了下partitions列包含了所有分区,是不是意味着没有利用到分区?怎么才能利用到p201101分区?

Answer 1

EXPLAIN PARTITIONS SELECT * FROM virus_info WHERE import_date BETWEEN UNIX_TIMESTAMP('2011-01-01 00:00:00') AND UNIX_TIMESTAMP('2011-05-01 00:00:00');这样看看。

Answer 2

MYSQL的优化器并不认这种日期形式的分区http://www.ooso.net/archives/217

Answer 3

确实如此啊。
帮我看看这个
http://topic.csdn.net/u/20110503/15/f1fdc5d3-133f-472c-9e34-4543936bb6c6.html?seed=499521309&r=73096259#r_73096259

Answer 4

在你的查询条件中 同样使用 UNIX_TIMESTAMP(import_date)来查询，否则MYSQL无法知道是否应该使用哪个索引。这个与分区无关，与索引相关。

Answer 5

的确,应该是to_days,但是import_date我使用timestamp类型,一边使用current_timestamp来设置默认值.对timestamp类型字段使用to_days进行分区会遇到Bug#50036.

Answer 6

恩,to_days是可行的,但是要放弃默认值.
之所以说按时间分区导致无法去重正是因为分区字段必须包含在主键中,导致一个virus_id在不同时间可以多次录入,没法做到一个virus_id只录入一次,使用触发器等手段又怕效率问题.

Answer 7

CREATE TABLE t_test
(id INT PRIMARY KEY,
 NAME VARCHAR(10));
 
 CREATE TABLE t_test_part
(id INT ,
 NAME VARCHAR(10),
 import_date DATE NOT NULL,
 PRIMARY KEY(`id`,`import_date`))
 PARTITION BY RANGE(TO_DAYS(import_date))(
 PARTITION p00 VALUES LESS THAN (TO_DAYS('2011-01-01')), 
 PARTITION p01 VALUES LESS THAN (TO_DAYS('2011-02-01')), 
 PARTITION p02 VALUES LESS THAN (TO_DAYS('2011-03-01')), 
 PARTITION p03 VALUES LESS THAN (TO_DAYS('2011-04-01')), 
 PARTITION p04 VALUES LESS THAN (TO_DAYS('2011-05-01')),
 PARTITION p05 VALUES LESS THAN (TO_DAYS('2011-06-01')),
 PARTITION p06 VALUES LESS THAN (maxvalue)
 );CREATE TRIGGER tri_test BEFORE INSERT ON t_test   
FOR EACH ROW   
BEGIN   
 INSERT INTO t_test_part(id,NAME,import_date)
 VALUES(NEW.id,NEW.name,NOW());
END; trigger创建失败是什么原因?奇怪

Answer 8

找到原因了
DELIMITER $$CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `virus`.`tri_test` AFTER INSERT
    ON `virus`.`t_test`
    FOR EACH ROW BEGIN
    INSERT INTO t_test_part(id,NAME,import_date)
    VALUES(new.id,new.name,NOW());
    END$$DELIMITER ;