MySQL最佳實踐之分區表基本類型

MySQL分區表概述

隨著MySQL越來越流行，Mysql裡面的保存的數據也越來越大。在日常的工作中，我們經常遇到一張表裡面保存了上億甚至過十億的記錄。這些表裡面保存了大量的歷史記錄。 對於這些歷史數據的清理是一個非常頭疼事情，由於所有的數據都一個普通的表裡。所以只能是啟用一個或多個帶where條件的delete語句去刪除（一般where條件是時間）。 這對數據庫的造成了很大壓力。即使我們把這些刪除了，但底層的數據文件並沒有變小。面對這類問題，最有效的方法就是在使用分區表。最常見的分區方法就是按照時間進行分區。 分區一個最大的優點就是可以非常高效的進行歷史數據的清理。

分區類型

目前MySQL支持範圍分區（RANGE），列表分區（LIST），哈希分區（HASH）以及KEY分區四種。下面我們逐一介紹每種分區：

RANGE分區

基於屬於一個給定連續區間的列值，把多行分配給分區。最常見的是基於時間字段. 基於分區的列最好是整型，如果日期型的可以使用函數轉換為整型。本例中使用to_days函數

  CREATE TABLE my_range_datetime(   id INT,   hiredate DATETIME  )   PARTITION BY RANGE (TO_DAYS(hiredate) ) (   PARTITION p1 VALUES LESS THAN ( TO_DAYS('20171202') ),   PARTITION p2 VALUES LESS THAN ( TO_DAYS('20171203') ),   PARTITION p3 VALUES LESS THAN ( TO_DAYS('20171204') ),   PARTITION p4 VALUES LESS THAN ( TO_DAYS('20171205') ),   PARTITION p5 VALUES LESS THAN ( TO_DAYS('20171206') ),   PARTITION p6 VALUES LESS THAN ( TO_DAYS('20171207') ),   PARTITION p7 VALUES LESS THAN ( TO_DAYS('20171208') ),   PARTITION p8 VALUES LESS THAN ( TO_DAYS('20171209') ),   PARTITION p9 VALUES LESS THAN ( TO_DAYS('20171210') ),   PARTITION p10 VALUES LESS THAN ( TO_DAYS('20171211') )，   PARTITION p11 VALUES LESS THAN (MAXVALUE)   );

p11是一個默認分區，所有大於20171211的記錄都會在這個分區。MAXVALUE是一個無窮大的值。p11是一個可選分區。如果在定義表的沒有指定的這個分區，當我們插入大於20171211的數據的時候，會收到一個錯誤。

我們在執行查詢的時候，必須帶上分區字段。這樣可以使用分區剪裁功能

  mysql> insert into my_range_datetime select * from test;                                    Query OK, 1000000 rows affected (8.15 sec)  Records: 1000000 Duplicates: 0 Warnings: 0    mysql> explain partitions select * from my_range_datetime where hiredate >= '20171207124503' and hiredate<='20171210111230';   +----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+  | id | select_type | table       | partitions  | type | possible_keys | key | key_len | ref | rows  | Extra    |  +----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+  | 1 | SIMPLE   | my_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函數確實可以實現分區裁剪。

上面是基於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 my_range_timestamp (    id INT,    hiredate TIMESTAMP  )  PARTITION BY RANGE ( UNIX_TIMESTAMP(hiredate) ) (    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-02 00:00:00') ),    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-03 00:00:00') ),    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-04 00:00:00') ),    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-05 00:00:00') ),    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-06 00:00:00') ),    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-07 00:00:00') ),    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-08 00:00:00') ),    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-09 00:00:00') ),    PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-12-10 00:00:00') ),    PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2017-12-11 00:00:00') )  );

插入數據並查看上述查詢的執行計劃

  mysql> insert into my_range_timestamp select * from test;  Query OK, 1000000 rows affected (13.25 sec)  Records: 1000000 Duplicates: 0 Warnings: 0    mysql> explain partitions select * from my_range_timestamp where hiredate >= '20171207124503' and hiredate<='20171210111230';  +----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+  | id | select_type | table       | partitions  | type | possible_keys | key | key_len | ref | rows  | Extra    |  +----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+  | 1 | SIMPLE   | my_range_timestamp | p7,p8,p9,p10 | ALL | NULL     | NULL | NULL  | NULL | 400448 | Using where |  +----+-------------+-------------------+--------------+------+---------------+------+---------+------+--------+-------------+  1 row in set (0.00 sec)

同樣也能實現分區裁剪。

在5.7版本之前，對於DATA和DATETIME類型的列，如果要實現分區裁剪，只能使用YEAR() 和TO_DAYS()函數，在5.7版本中，又新增了TO_SECONDS()函數。

LIST 分區

LIST分區

LIST分區和RANGE分區類似，區別在於LIST是枚舉值列表的集合，RANGE是連續的區間值的集合。二者在語法方面非常的相似。同樣建議LIST分區列是非null列，否則插入null值如果枚舉列表裡面不存在null值會插入失敗，這點和其它的分區不一樣，RANGE分區會將其作為最小分區值存儲，HASHKEY分為會將其轉換成0存儲，主要LIST分區只支持整形，非整形字段需要通過函數轉換成整形.

  create table t_list(   　　a int(11),   　　b int(11)   　　)(partition by list (b)   　　partition p0 values in (1,3,5,7,9),   　　partition p1 values in (2,4,6,8,0)   　　);

Hash 分區

我們在實際工作中經常遇到像會員表的這種表。並沒有明顯可以分區的特徵字段。但表數據有非常龐大。為了把這類的數據進行分區打散mysql 提供了hash分區。基於給定的分區個數，將數據分配到不同的分區，HASH分區只能針對整數進行HASH，對於非整形的字段只能通過表達式將其轉換成整數。表達式可以是mysql中任意有效的函數或者表達式，對於非整形的HASH往表插入數據的過程中會多一步表達式的計算操作，所以不建議使用複雜的表達式這樣會影響性能。

Hash分區表的基本語句如下：

  CREATE TABLE my_member (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    created DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT,    store_id INT  )  PARTITION BY HASH(id)  PARTITIONS 4;

注意：

1. HASH分區可以不用指定PARTITIONS子句，如上文中的PARTITIONS 4，則默認分區數為1。

2. 不允許只寫PARTITIONS，而不指定分區數。

3. 同RANGE分區和LIST分區一樣，PARTITION BY HASH (expr)子句中的expr返回的必須是整數值。

4. HASH分區的底層實現其實是基於MOD函數。譬如，對於下表
   

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4; 如果你要插入一個col3為“2017-09-15”的記錄，則分區的選擇是根據以下值決定的：

MOD(YEAR(‘2017-09-01'),4) = MOD(2017,4) = 1

LINEAR HASH分區

LINEAR HASH分區是HASH分區的一種特殊類型，與HASH分區是基於MOD函數不同的是，它基於的是另外一種算法。

格式如下：

  CREATE TABLE my_members (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT,    store_id INT  )  PARTITION BY LINEAR HASH( id )  PARTITIONS 4;

說明： 它的優點是在數據量大的場景，譬如TB級，增加、刪除、合併和拆分分區會更快，缺點是，相對於HASH分區，它數據分佈不均勻的概率更大。

KEY分區

KEY分區其實跟HASH分區差不多，不同點如下：

1. KEY分區允許多列，而HASH分區只允許一列。

2. 如果在有主鍵或者唯一鍵的情況下，key中分區列可不指定，默認為主鍵或者唯一鍵，如果沒有，則必須顯性指定列。

3. KEY分區對象必須為列，而不能是基於列的表達式。

4. KEY分區和HASH分區的算法不一樣，PARTITION BY HASH (expr)，MOD取值的對象是expr返回的值，而PARTITION BY KEY (column_list)，基於的是列的MD5值。
   

格式如下：

  CREATE TABLE k1 (    id INT NOT NULL PRIMARY KEY,      name VARCHAR(20)  )  PARTITION BY KEY()  PARTITIONS 2;

在沒有主鍵或者唯一鍵的情況下，格式如下：

  CREATE TABLE tm1 (    s1 CHAR(32)  )  PARTITION BY KEY(s1)  PARTITIONS 10;

總結：

MySQL分區中如果存在主鍵或唯一鍵，則分區列必須包含在其中。

對於原生的RANGE分區，LIST分區，HASH分區，分區對象返回的只能是整數值。

分區字段不能為NULL，要不然怎麼確定分區範圍呢，所以儘量NOT NULL