mysql分区表按日期分区查询_MYSQL之表分区----按日期分区

mysql 5.1已经到了beta版，官方网站上也陆续有一些文章介绍，比如上次看到的Improving Database Performance withPartitioning。在使用分区的前提下，可以用mysql实现非常大的数据量存储。今天在mysql的站上又看到一篇进阶的文章—— 按日期分区存储。如果能够实现按日期分区，这对某些时效性很强的数据存储是相当实用的功能。下面是从这篇文章中摘录的一些内容。

错误的按日期分区例子

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

CODE:

mysql>

create table rms

(ddate

)

->

partition by range

(d

)

->

(partitionp0 values less than

(

'1995-01-01'

),

->

partition p1VALUES LESS THAN

(

'2010-01-01'

)

);

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

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

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

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

CODE:

mysql> CREATE TABLE part_date1

->

(

c1int default NULL,

->

c2varchar

(

30

) default NULL,

->

c3 date defaultNULL

)engine=myisam

->

partition by range

(cast

(date_format

(c3,

'%Y%m%d'

) as signed

)

)

->

(PARTITIONp0 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.

01sec

)

搞定？接着往下分析

CODE:

mysql> explain partitions

->

select count

(*

) from part_date1where

->

c3> date

'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.

00sec

)

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

正确的日期分区例子

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

TO_DAYS()

YEAR()

看个例子：

CODE:

mysql> CREATE TABLE part_date3

->

(

c1int default NULL,

->

c2varchar

(

30

) default NULL,

->

c3 date defaultNULL

)engine=myisam

->

partition by range

(to_days

(c3

)

)

->

(PARTITIONp0 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.

00sec

)

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

CODE:

mysql> explain partitions

->

select count

(*

) from part_date3where

->

c3> date

'1995-01-01' and c3

'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.

00sec

)

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

CODE:

mysql> select count

(*

) from part_date3where

->

c3> date

'1995-01-01' and c3

'1995-12-31';

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

| count

(*

) |

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

|

805114 |

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

1 row in set

(

4.

11sec

)

mysql> select count

(*

) from part_date1where

->

c3> date

'1995-01-01' and c3

'1995-12-31';

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

| count

(*

) |

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

|

805114 |

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

1 row in set

(

40.

33sec

)

可以看到，分区正确的话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()

http://blog.sina.com.cn/s/blog_888269b20100w7kf.html