mysql怎么按时间分区 mysql按时间分表

求助：MySQL如何按天分区

所谓按天，不过是日期精确到天而已。

创新互联专注于做网站、成都网站制作、网页设计、网站制作、网站开发。公司秉持“客户至上,用心服务”的宗旨,从客户的利益和观点出发,让客户在网络营销中找到自己的驻足之地。尊重和关怀每一位客户，用严谨的态度对待客户，用专业的服务创造价值，成为客户值得信赖的朋友，为客户解除后顾之忧。

错误的按日期分区例子

最直观的方法，就是直接用年月日这种日期格式来进行常规的分区：

mysql  create table rms (d date)  

-  partition by range (d)  

- (partition p0 values less than ('1995-01-01'),  

-  partition p1 VALUES LESS THAN ('2010-01-01'));

上面的例子中，就是直接用"Y-m-d"的格式来对一个table进行分区，可惜想当然往往不能奏效，会得到一个错误信息:

ERROR 1064 (42000): VALUES value must be of same type as partition function near '),

partition p1 VALUES LESS THAN ('2010-01-01'))' at line 3

上述分区方式没有成功，而且明显的不经济，老练的DBA会用整型数值来进行分区：

mysql CREATE TABLE part_date1  

-      (  c1 int default NULL,  

-  c2 varchar(30) default NULL,  

-  c3 date default NULL) engine=myisam  

-      partition by range (cast(date_format(c3,'%Y%m%d') as signed))  

- (PARTITION p0 VALUES LESS THAN (19950101),  

- PARTITION p1 VALUES LESS THAN (19960101) ,  

- PARTITION p2 VALUES LESS THAN (19970101) ,  

- PARTITION p3 VALUES LESS THAN (19980101) ,  

- PARTITION p4 VALUES LESS THAN (19990101) ,  

- PARTITION p5 VALUES LESS THAN (20000101) ,  

- PARTITION p6 VALUES LESS THAN (20010101) ,  

- PARTITION p7 VALUES LESS THAN (20020101) ,  

- PARTITION p8 VALUES LESS THAN (20030101) ,  

- PARTITION p9 VALUES LESS THAN (20040101) ,  

- PARTITION p10 VALUES LESS THAN (20100101),  

- PARTITION p11 VALUES LESS THAN MAXVALUE );

Query OK, 0 rows affected (0.01 sec)

搞定？接着往下分析

mysql explain partitions  

- select count(*) from part_date1 where  

-      c3 '1995-01-01' and c3 '1995-12-31'\G  

*************************** 1. row ***************************  

id: 1  

select_type: SIMPLE  

table: part_date1  

partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11  

type: ALL  

possible_keys: NULL  

key: NULL  

key_len: NULL  

ref: NULL  

rows: 8100000  

Extra: Using where  

1 row in set (0.00 sec)

万恶的mysql居然对上面的sql使用全表扫描，而不是按照我们的日期分区分块查询。原文中解释到MYSQL的优化器并不认这种日期形式的分区，花了大量的篇幅来引诱俺走上歧路，过分。

正确的日期分区例子

mysql优化器支持以下两种内置的日期函数进行分区：

TO_DAYS()

YEAR()

看个例子：

mysql CREATE TABLE part_date3  

-      (  c1 int default NULL,  

-  c2 varchar(30) default NULL,  

-  c3 date default NULL) engine=myisam  

-      partition by range (to_days(c3))  

- (PARTITION p0 VALUES LESS THAN (to_days('1995-01-01')),  

- PARTITION p1 VALUES LESS THAN (to_days('1996-01-01')) ,  

- PARTITION p2 VALUES LESS THAN (to_days('1997-01-01')) ,  

- PARTITION p3 VALUES LESS THAN (to_days('1998-01-01')) ,  

- PARTITION p4 VALUES LESS THAN (to_days('1999-01-01')) ,  

- PARTITION p5 VALUES LESS THAN (to_days('2000-01-01')) ,  

- PARTITION p6 VALUES LESS THAN (to_days('2001-01-01')) ,  

- PARTITION p7 VALUES LESS THAN (to_days('2002-01-01')) ,  

- PARTITION p8 VALUES LESS THAN (to_days('2003-01-01')) ,  

- PARTITION p9 VALUES LESS THAN (to_days('2004-01-01')) ,  

- PARTITION p10 VALUES LESS THAN (to_days('2010-01-01')),  

- PARTITION p11 VALUES LESS THAN MAXVALUE );

Query OK, 0 rows affected (0.00 sec)

以to_days()函数分区成功，我们分析一下看看：

mysql explain partitions  

- select count(*) from part_date3 where  

-      c3 date '1995-01-01' and c3 date '1995-12-31'\G  

*************************** 1. row ***************************  

id: 1  

select_type: SIMPLE  

table: part_date3  

partitions: p1  

type: ALL  

possible_keys: NULL  

key: NULL  

key_len: NULL  

ref: NULL  

rows: 808431  

Extra: Using where  

1 row in set (0.00 sec)

可以看到，mysql优化器这次不负众望，仅仅在p1分区进行查询。在这种情况下查询，真的能够带来提升查询效率么？下面分别对这次建立的part_date3和之前分区失败的part_date1做一个查询对比：

mysql select count(*) from part_date3 where  

-      c3 date '1995-01-01' and c3 date '1995-12-31';  

+----------+  

| count(*) |  

+----------+  

|   805114 |  

+----------+  

1 row in set (4.11 sec)  

mysql select count(*) from part_date1 where  

-      c3 date '1995-01-01' and c3 date '1995-12-31';  

+----------+  

| count(*) |  

+----------+  

|   805114 |  

+----------+  

1 row in set (40.33 sec)

可以看到，分区正确的话query花费时间为4秒，而分区错误则花费时间40秒（相当于没有分区），效率有90％的提升！所以我们千万要正确的使用分区功能，分区后务必用explain验证，这样才能获得真正的性能提升。

注意：

在mysql5.1中建立分区表的语句中，只能包含下列函数：

ABS()

CEILING() and FLOOR() （在使用这2个函数的建立分区表的前提是使用函数的分区键是INT类型），例如

mysql CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )( - PARTITION p0 VALUES IN (1,3,5), - PARTITION p1 VALUES IN (2,4,6) - );; ERROR 1491 (HY000): The PARTITION function returns the wrong type   mysql CREATE TABLE t (c int) PARTITION BY LIST( FLOOR(c) )( - PARTITION p0 VALUES IN (1,3,5), - PARTITION p1 VALUES IN (2,4,6) - ); Query OK, 0 rows affected (0.01 sec)  

DAY()  

DAYOFMONTH()  

DAYOFWEEK()  

DAYOFYEAR()  

DATEDIFF()  

EXTRACT()  

HOUR()  

MICROSECOND()  

MINUTE()  

MOD()  

MONTH()  

QUARTER()  

SECOND()  

TIME_TO_SEC()  

TO_DAYS()  

WEEKDAY()  

YEAR()  

YEARWEEK()

Mysql按时间进行表分区

以下是创建一张测试表TEST并且按照时间CREATE_TIME创建RANGE分区，并使用ID创建hash分区，组成复合分区。

CREATE TABLE TEST (

CREATE_TIME DATETIME DEFAULT NULL, ID BIGINT(15) DEFAULT NULL

) ENGINE=INNODB DEFAULT CHARSET=utf8

PARTITION BY RANGE(TO_DAYS(CREATE_TIME)) PARTITIONS 7 SUBPARTITION BY HASH(ID) SUBPARTITIONS 16

(PARTITION P1710 VALUES LESS THAN (TO_DAYS ('2017-10-01'))

(SUBPARTITION P1710sp0 ,SUBPARTITION P1710sp1 ,

SUBPARTITION P1710sp2 ,SUBPARTITION P1710sp3 ,

SUBPARTITION P1710sp4 ,SUBPARTITION P1710sp5 ,

SUBPARTITION P1710sp6 ,SUBPARTITION P1710sp7 ,

SUBPARTITION P1710sp8 ,SUBPARTITION P1710sp9 ,

SUBPARTITION P1710sp10 ,SUBPARTITION P1710sp11 ,

SUBPARTITION P1710sp12 ,SUBPARTITION P1710sp13 ,

SUBPARTITION P1710sp14 ,SUBPARTITION P1710sp15 ),

PARTITION P1711 VALUES LESS THAN (TO_DAYS ('2017-11-01'))

(SUBPARTITION P1711sp0 ,SUBPARTITION P1711sp1 ,

SUBPARTITION P1711sp2 , SUBPARTITION P1711sp3 ,

SUBPARTITION P1711sp4 , SUBPARTITION P1711sp5 ,

SUBPARTITION P1711sp6 , SUBPARTITION P1711sp7 ,

SUBPARTITION P1711sp8 , SUBPARTITION P1711sp9 ,

SUBPARTITION P1711sp10 , SUBPARTITION P1711sp11 ,

SUBPARTITION P1711sp12 , SUBPARTITION P1711sp13 ,

SUBPARTITION P1711sp14 , SUBPARTITION P1711sp15 ),

mysql 怎么按照时间做表分区

用mysql的表分区功能（逻辑上还是一个表，对程序来说是透明的），通过分区函数可实现自动分表。如果想实现根据数据每月或每周动态的再分区，可以写一个存储过程实现分区调整逻辑，最后写一个mysql event（自动化作业）按周期调用这个存储过程就行了。