如何按datetime列分区表？ [英] how to partition a table by datetime column?

问题描述

我想通过datetime列分割一个mysql表。创建表脚本是这样的：

  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））分区31; 
  

但是当我选择某一天的数据时，找不到分区。select语句就像这是：

 解释分区select * from raw_log_2011_4 where day（ftime）= 30; 
  

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

 解释分区select * from raw_log_2011_4其中ftime ='2011-03-30'; 
  

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

解决方案

HASH分区是一个非常糟糕的想法，使用datetime列，因为它不能使用分区修剪。从MySQL文档：

修剪只能用于由
HASH或KEY分区的整数列的整数列。例如，表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可以
修剪。

所以您可以在额外的INTEGER列中存储TO_DAYS（DATE（））的值以使用修剪。

另一个选项是使用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 
按范围划分（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价值不超过（TO_DAYS（'2011-04-05'）），
 ... 
 PARTITION p20110426值不超过（TO_DAYS（'2011-04-27'）） ，
 PARTITION p20110427价值不超过（TO_DAYS（'2011-04-28'）），
 PARTITION p20110428价值不超过（TO_DAYS（'2011-04-29'）），
 PARTITION 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(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
  );

Now the following query will only use partition p20110403:

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

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