如何按日期时间列对表进行分区? [英] how to partition a table by datetime column?

问题描述

我想按日期时间列对 mysql 表进行分区.一天一个分区.创建表的脚本是这样的:

创建表 raw_log_2011_4 (id bigint(20) NOT NULL AUTO_INCREMENT,logid char(16) 非空，tid char(16) 非空，记者字符(46)默认为空，ftime 日期时间 DEFAULT NULL，密钥 ID (id)) ENGINE=InnoDB AUTO_INCREMENT=286802795 默认字符集=utf8PARTITION BY hash (day(ftime)) 分区 31；

但是当我选择某天的数据时，找不到分区，select语句是这样的:

explain partitions select * from raw_log_2011_4 where day(ftime) = 30;

当我使用另一个语句时，它可以定位分区，但我无法选择某天的数据.

explain partitions select * from raw_log_2011_4 where ftime = '2011-03-30';

有没有人告诉我如何选择某一天的数据并使用分区.谢谢！

解决方案

Partitions by HASH 对于 datetime 列是一个非常糟糕的主意，因为它不能使用 分区修剪.来自 MySQL 文档:

<块引用>

修剪只能用于分区表的整数列哈希或密钥.例如，表 t4 上的这个查询不能使用修剪因为 dob 是一个 DATE 列:

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

<块引用>

但是，如果表将年份值存储在 INT 列中，则具有 WHERE year_col >= 2001 AND year_col <= 2005 的查询可以是修剪.

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

另一种选择是使用范围分区:

创建表 raw_log_2011_4 (id bigint(20) NOT NULL AUTO_INCREMENT,logid char(16) 非空，tid char(16) 非空，记者字符(46)默认为空，ftime 日期时间 DEFAULT NULL，密钥 ID (id)) ENGINE=InnoDB AUTO_INCREMENT=286802795 默认字符集=utf8按范围分区(TO_DAYS(ftime))(分区 p20110401 值小于 (TO_DAYS('2011-04-02'))，分区 p20110402 值小于 (TO_DAYS('2011-04-03'))，分区 p20110403 值小于 (TO_DAYS('2011-04-04'))，分区 p20110404 值小于 (TO_DAYS('2011-04-05'))，...分区 p20110426 值小于 (TO_DAYS('2011-04-27'))，分区 p20110427 值小于 (TO_DAYS('2011-04-28'))，分区 p20110428 值小于 (TO_DAYS('2011-04-29'))，分区 p20110429 值小于 (TO_DAYS('2011-04-30'))，分区未来值小于 MAXVALUE);

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

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

I want to partition a mysql table by datetime column. One day a partition.The create table scripts is like this:

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 hash (day(ftime)) partitions 31;

But when I select data of some day.It could not locate the partition.The select statement is like this:

explain partitions select * from raw_log_2011_4 where day(ftime) = 30;

when i use another statement,it could locate the partition,but I coluld not select data of some day.

explain partitions select * from raw_log_2011_4 where ftime = '2011-03-30';

Is there anyone tell me How I could select data of some day and make use of partition.Thanks!

解决方案

Partitions by HASH is a very bad idea with datetime columns, because it cannot use partition pruning. From the MySQL docs:

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.

So you can store the value of TO_DAYS(DATE()) in an extra INTEGER column to use pruning.

Another option is to use RANGE partitioning:

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(ftime) ) (
    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
  );

Now the following query will only use partition p20110403:

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

这篇关于如何按日期时间列对表进行分区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！