mysql datetime 分区_mysql – 如何按datetime列分区表？

HASH的分区是一个非常糟糕的想法,datetime列,因为它不能使用

partition pruning.从MySQL文档：

Pruning can be used only on integer columns of tables partitioned by

HASH or KEY. For example, this query on table t4 cannot use pruning

because dob is a DATE column:

SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';

However, if the table stores year values in an INT column, then a

query having WHERE year_col >= 2001 AND year_col <= 2005 can be

pruned.

因此,您可以将TO_DAYS(DATE())的值存储在额外的INTEGER列中以使用修剪.

另一个选择是使用RANGE分区：

CREATE TABLE raw_log_2011_4 (

id bigint(20) NOT NULL AUTO_INCREMENT,

logid char(16) NOT NULL,

tid char(16) NOT NULL,

reporterip char(46) DEFAULT NULL,

ftime datetime DEFAULT NULL,

KEY id (id)

) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8

PARTITION BY RANGE( TO_DAYS(datetime) ) (

PARTITION p20110401 VALUES LESS THAN (TO_DAYS('2011-04-02')),

PARTITION p20110402 VALUES LESS THAN (TO_DAYS('2011-04-03')),

PARTITION p20110403 VALUES LESS THAN (TO_DAYS('2011-04-04')),

PARTITION p20110404 VALUES LESS THAN (TO_DAYS('2011-04-05')),

...

PARTITION p20110426 VALUES LESS THAN (TO_DAYS('2011-04-27')),

PARTITION p20110427 VALUES LESS THAN (TO_DAYS('2011-04-28')),

PARTITION p20110428 VALUES LESS THAN (TO_DAYS('2011-04-29')),

PARTITION p20110429 VALUES LESS THAN (TO_DAYS('2011-04-30')),

PARTITION future VALUES LESS THAN MAXVALUE

);

现在以下查询将只使用分区p20110403：

SELECT * FROM raw_log_2011_4 WHERE ftime = '2011-04-03';