程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL基於時間字段進行分區的方案總結

MySQL基於時間字段進行分區的方案總結

編輯:MySQL綜合教程

MySQL基於時間字段進行分區的方案總結


MySQL支持的分區類型一共有四種:RANGE,LIST,HASH,KEY。其中,RANGE又可分為原生RANGE和RANGE COLUMNS,LIST分為原生LIST和LIST COLUMNS,HASH分為原生HASH和LINEAR HASH,KEY包含原生KEY和LINEAR HASH。關於這些分區之間的差別,改日另寫文章進行闡述。   最近,碰到一個需求,要對表的時間字段(類型:datetime)基於天進行分區。於是遍歷MySQL官方文檔分區章節,總結如下:   實現方式   主要是以下幾種:   1. 基於RANGE   2. 基於RANGE COLUMNS   3. 基於HASH   測試數據    為了測試以上三種方案,特構造了100萬的測試數據,放在test表中,test表只有兩列:id和hiredate,其中hiredate只包含10天的數據,從2015-12-01到2015-12-10。具體信息如下:  
mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select min(hiredate),max(hiredate) from test;
+---------------------+---------------------+
| min(hiredate)       | max(hiredate)       |
+---------------------+---------------------+
| 2015-12-01 00:00:00 | 2015-12-10 23:59:56 |
+---------------------+---------------------+
1 row in set (0.44 sec)

mysql> select date(hiredate),count(*) from test group by date(hiredate);
+----------------+----------+
| date(hiredate) | count(*) |
+----------------+----------+
| 2015-12-01     |    99963 |
| 2015-12-02     |   100032 |
| 2015-12-03     |   100150 |
| 2015-12-04     |    99989 |
| 2015-12-05     |    99908 |
| 2015-12-06     |    99897 |
| 2015-12-07     |   100137 |
| 2015-12-08     |   100171 |
| 2015-12-09     |    99851 |
| 2015-12-10     |    99902 |
+----------------+----------+
10 rows in set (0.98 sec)

 

測試的維度   測試的維度主要從兩個方面進行,   一、分區剪裁   針對特定的查詢,是否能進行分區剪裁(即只查詢相關的分區,而不是所有分區)   二、查詢時間   鑒於該批測試數據是靜止的(即沒有並發進行的insert,update和delete操作),數據量也不太大,從這個維度來考量貌似意義也不是很大。   因此,重點測試第一個維度。   基於RANGE的分區方案   在這裡,選用了TO_DAYS函數    
CREATE TABLE range_datetime(
    id INT,
    hiredate DATETIME
)
PARTITION BY RANGE (TO_DAYS(hiredate) ) (
    PARTITION p1 VALUES LESS THAN ( TO_DAYS('20151202') ),
    PARTITION p2 VALUES LESS THAN ( TO_DAYS('20151203') ),
    PARTITION p3 VALUES LESS THAN ( TO_DAYS('20151204') ),
    PARTITION p4 VALUES LESS THAN ( TO_DAYS('20151205') ),
    PARTITION p5 VALUES LESS THAN ( TO_DAYS('20151206') ),
    PARTITION p6 VALUES LESS THAN ( TO_DAYS('20151207') ),
    PARTITION p7 VALUES LESS THAN ( TO_DAYS('20151208') ),
    PARTITION p8 VALUES LESS THAN ( TO_DAYS('20151209') ),
    PARTITION p9 VALUES LESS THAN ( TO_DAYS('20151210') ),
    PARTITION p10 VALUES LESS THAN ( TO_DAYS('20151211') )
);

 

  插入數據並查看特定查詢的執行計劃    
mysql> insert into range_datetime select * from test;                                                                    
Query OK, 1000000 rows affected (8.15 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> explain partitions select * from range_datetime where hiredate >= '20151207124503' and hiredate<='20151210111230'; 
+----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table          | partitions   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | range_datetime | p7,p8,p9,p10 | ALL  | NULL          | NULL | NULL    | NULL | 400061 | Using where |
+----+-------------+----------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.03 sec)

 

注意執行計劃中的partitions的內容,只查詢了p7,p8,p9,p10三個分區,由此來看,使用to_days函數確實可以實現分區裁剪。       基於RANGE COLUMNS的分區方案   RANGE COLUMNS可以直接基於列,而無需像上述RANGE那種,分區的對象只能為整數。   創表語句如下:    
CREATE TABLE range_columns ( 
    id INT,
    hiredate DATETIME
)
PARTITION BY RANGE COLUMNS(hiredate) (
    PARTITION p1 VALUES LESS THAN ( '20151202' ),
    PARTITION p2 VALUES LESS THAN ( '20151203' ),
    PARTITION p3 VALUES LESS THAN ( '20151204' ),
    PARTITION p4 VALUES LESS THAN ( '20151205' ),
    PARTITION p5 VALUES LESS THAN ( '20151206' ),
    PARTITION p6 VALUES LESS THAN ( '20151207' ),
    PARTITION p7 VALUES LESS THAN ( '20151208' ),
    PARTITION p8 VALUES LESS THAN ( '20151209' ),
    PARTITION p9 VALUES LESS THAN ( '20151210' ),
    PARTITION p10 VALUES LESS THAN ('20151211' )
);

 

插入數據並查看上述查詢的執行計劃    
mysql> insert into range_columns select * from test;                                                                    
Query OK, 1000000 rows affected (9.20 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> explain partitions select * from range_columns where hiredate >= '20151207124503' and hiredate<='20151210111230'; 
+----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table         | partitions   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | range_columns | p7,p8,p9,p10 | ALL  | NULL          | NULL | NULL    | NULL | 400210 | Using where |
+----+-------------+---------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.11 sec)

 

  同樣,使用該分區方案也實現了分區剪裁。       基於HASH的分區方案   因HASH分區對象同樣只能為整數,所以我們無法像上述RANGE COLUMNS那種直接引用列,在這裡,同樣用了TO_DAYS函數進行轉換。   創表語句如下:  
CREATE TABLE hash_datetime (
   id INT,
   hiredate DATETIME
)
PARTITION BY HASH( TO_DAYS(hiredate) )
PARTITIONS 10;

 

插入數據並查看上述查詢的執行計劃    
mysql> insert into hash_datetime select * from test;
Query OK, 1000000 rows affected (9.43 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> explain partitions select * from hash_datetime where hiredate >= '20151207124503' and hiredate<='20151210111230';
+----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table         | partitions                    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | hash_datetime | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | ALL  | NULL          | NULL | NULL    | NULL | 1000500 | Using where |
+----+-------------+---------------+-------------------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

 

不難看出,使用hash分區並不能有效的實現分區裁剪,至少在本例,基於天的需求中如此。       以上三種方案都是基於datetime的,那麼,對於timestamp類型,又該如何選擇呢?   事實上,MySQL提供了一種基於UNIX_TIMESTAMP函數的RANGE分區方案,而且,只能使用UNIX_TIMESTAMP函數,如果使用其它函數,譬如to_days,會報如下錯誤:“ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed”。   而且官方文檔中也提到“Any other expressions involving TIMESTAMP values are not permitted. (See Bug #42849.)”。   下面來測試一下基於UNIX_TIMESTAMP函數的RANGE分區方案,看其能否實現分區裁剪。       針對TIMESTAMP的分區方案   創表語句如下:    
CREATE TABLE range_timestamp (
    id INT,
    hiredate TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(hiredate) ) (
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-02 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-03 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-04 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-05 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-06 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-07 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-08 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-09 00:00:00') ),
    PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-10 00:00:00') ),
    PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2015-12-11 00:00:00') )
);

 

插入數據並查看上述查詢的執行計劃  
mysql> insert into range_timestamp select * from test;
Query OK, 1000000 rows affected (13.25 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> explain partitions select * from range_timestamp where hiredate >= '20151207124503' and hiredate<='20151210111230';
+----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table           | partitions   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | range_timestamp | p7,p8,p9,p10 | ALL  | NULL          | NULL | NULL    | NULL | 400448 | Using where |
+----+-------------+-----------------+--------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

 

同樣也能實現分區裁剪。     總結:   1. 經過對比,個人傾向於第二種方案,即基於RANGE COLUMNS的分區實現。   2. 在5.7版本之前,對於DATA和DATETIME類型的列,如果要實現分區裁剪,只能使用YEAR() 和TO_DAYS()函數,在5.7版本中,又新增了TO_SECONDS()函數。   3. 其實LIST也能實現基於天的分區方案,但在這個需求上,相比於RANGE,還是顯得很雞肋。   4. TIMESTAMP類型的列,只能基於UNIX_TIMESTAMP函數進行分區,切記!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved