mysql 5.1新功能 -- 按日期分割槽

yantaicuiwei發表於2011-04-11

mysql 5.1已經到了beta版,官方網站上也陸續有一些文章介紹,比如上次看到的。在使用分割槽的前提下,可以用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 > 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居然對上面的sql使用全表掃描,而不是按照我們的日期分割槽分塊查詢。原文中解釋到的最佳化器並不認這種日期形式的分割槽,花了大量的篇幅來引誘俺走上歧路,過分。

正確的日期分割槽例子

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)

可以看到,最佳化器這次不負眾望,僅僅在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驗證,這樣才能獲得真正的效能提升。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/21601207/viewspace-692205/,如需轉載,請註明出處,否則將追究法律責任。

相關文章