mysql 5.1新功能 -- 按日期分割槽
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 按日期分割nginx日誌Nginx
- ORACLE 11G分割槽表新功能:列表--範圍分割槽Oracle
- mysql 分割槽MySql
- MySQL分割槽MySql
- 理解MySQL分割槽MySql
- 搞懂MySQL分割槽MySql
- 【MYSQL】 分割槽表MySql
- MySql建立分割槽MySql
- MySQL 子分割槽MySql
- MySQL KEY分割槽MySql
- MySQL HASH分割槽MySql
- MySQL COLUMNS分割槽MySql
- MySQL LIST分割槽MySql
- MySQL RANGE分割槽MySql
- MySQL 分割槽表MySql
- MySQL分割槽表MySql
- mysql分割槽nullMySqlNull
- mysql 分割槽示例MySql
- MySql資料分割槽操作之新增分割槽操作MySql
- MySQL 分割槽表 partition線上修改分割槽欄位MySql
- MySQL的分割槽(一)MySql
- MySQL的分割槽(二)MySql
- MySQL 分割槽表探索MySql
- mysql 分表 分割槽MySql
- MySQL分割槽學習MySql
- MySQL 分割槽建索引MySql索引
- MySQL分割槽介紹MySql
- mysql 分割槽表用法MySql
- MySQL表分割槽管理MySql
- hyperf 如何實現按日期分割日誌
- MySQL分割槽表的分割槽原理和優缺點MySql
- 將mysql非分割槽錶轉換為分割槽表MySql
- oracle表分割槽詳解(按天、按月、按年等)Oracle
- MySQL分割槽如何遷移MySql
- mysql 進行表分割槽MySql
- Mysql表分割槽實現MySql
- Mysql 的分割槽型別MySql型別
- mysql分割槽表筆記MySql筆記