錯(cuò)誤的按日期分區(qū)例子
最直觀的方法，就是直接用年月日這種日期格式來進(jìn)行常規(guī)的分區(qū)：
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"的格式來對(duì)一個(gè)table進(jìn)行分區(qū)，可惜想當(dāng)然往往不能奏效，會(huì)得到一個(gè)錯(cuò)誤信息:
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
上述分區(qū)方式?jīng)]有成功，而且明顯的不經(jīng)濟(jì)，老練的DBA會(huì)用整型數(shù)值來進(jìn)行分區(qū)：
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 <date '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居然對(duì)上面的sql使用全表掃描，而不是按照我們的日期分區(qū)分塊查詢。原文中解釋到MYSQL的優(yōu)化器并不認(rèn)這種日期形式的分區(qū)，花了大量的篇幅來引誘俺走上歧路，過分。
正確的日期分區(qū)例子
mysql優(yōu)化器支持以下兩種內(nèi)置的日期函數(shù)進(jìn)行分區(qū)：
看個(gè)例子：
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()函數(shù)分區(qū)成功，我們分析一下看看：
CODE:
-
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優(yōu)化器這次不負(fù)眾望，僅僅在p1分區(qū)進(jìn)行查詢。在這種情況下查詢，真的能夠帶來提升查詢效率么？下面分別對(duì)這次建立的part_date3和之前分區(qū)失敗的part_date1做一個(gè)查詢對(duì)比：
CODE:
-
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)
可以看到，分區(qū)正確的話query花費(fèi)時(shí)間為4秒，而分區(qū)錯(cuò)誤則花費(fèi)時(shí)間40秒（相當(dāng)于沒有分區(qū)），效率有90％的提升！所以我們千萬要正確的使用分區(qū)功能，分區(qū)后務(wù)必用explain驗(yàn)證，這樣才能獲得真正的性能提升。