mysql 5.1已经到了beta版，官方网站上也陆续有一些文章介绍，比如上次看到的。在使用分区的前提下，可以用mysql实现非常大的数据量存储。今天在mysql的站上又看到一篇进阶的文章 —— 。如果能够实现按日期分区，这对某些时效性很强的数据存储是相当实用的功能。下面是从这篇文章中摘录的一些内容。
错误的按日期分区例子
最直观的方法，就是直接用年月日这种日期格式来进行常规的分区：
code: -
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会用整型数值来进行分区：
code: -
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)
搞定？接着往下分析
code: -
mysql> explain partitions
-
-> select count(*) from part_date1 where
-
-> 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.00 sec)
万恶的mysql居然对上面的sql使用全表扫描，而不是按照我们的日期分区分块查询。原文中解释到的优化器并不认这种日期形式的分区，花了大量的篇幅来引诱俺走上歧路，过分。
正确的日期分区例子
mysql优化器支持以下两种内置的日期函数进行分区：
看个例子：
code: -
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()函数分区成功，我们分析一下看看：
code: -
mysql> explain partitions
-
-> select count(*) from part_date3 where
-
-> 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.00 sec)
可以看到，优化器这次不负众望，仅仅在p1分区进行查询。在这种情况下查询，真的能够带来提升查询效率么？下面分别对这次建立的part_date3和之前分区失败的part_date1做一个查询对比：
code: -
mysql> select count(*) from part_date3 where
-
-> c3> date '1995-01-01' and c3 '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 '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()