mysql正确的日期分区例子

正确的日期分区例子

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

TO_DAYS()

YEAR()

看个例子：

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’ ；

*************************** 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做一个查询对比：

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验证，这样才能获得真正的性能提升。