MySQL分區(qū)表概述
隨著MySQL越來(lái)越流行，Mysql里面的保存的數(shù)據(jù)也越來(lái)越大。在日常的工作中，我們經(jīng)常遇到一張表里面保存了上億甚至過(guò)十億的記錄。這些表里面保存了大量的歷史記錄。 對(duì)于這些歷史數(shù)據(jù)的清理是一個(gè)非常頭疼事情，由于所有的數(shù)據(jù)都一個(gè)普通的表里。所以只能是啟用一個(gè)或多個(gè)帶where條件的delete語(yǔ)句去刪除（一般where條件是時(shí)間）。 這對(duì)數(shù)據(jù)庫(kù)的造成了很大壓力。即使我們把這些刪除了，但底層的數(shù)據(jù)文件并沒(méi)有變小。面對(duì)這類(lèi)問(wèn)題，最有效的方法就是在使用分區(qū)表。最常見(jiàn)的分區(qū)方法就是按照時(shí)間進(jìn)行分區(qū)。 分區(qū)一個(gè)最大的優(yōu)點(diǎn)就是可以非常高效的進(jìn)行歷史數(shù)據(jù)的清理。
分區(qū)類(lèi)型
目前MySQL支持范圍分區(qū)（RANGE），列表分區(qū)（LIST），哈希分區(qū)（HASH）以及KEY分區(qū)四種。下面我們逐一介紹每種分區(qū)：
RANGE分區(qū)
基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。最常見(jiàn)的是基于時(shí)間字段. 基于分區(qū)的列最好是整型，如果日期型的可以使用函數(shù)轉(zhuǎn)換為整型。本例中使用to_days函數(shù)
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是一個(gè)默認(rèn)分區(qū)，所有大于20171211的記錄都會(huì)在這個(gè)分區(qū)。MAXVALUE是一個(gè)無(wú)窮大的值。p11是一個(gè)可選分區(qū)。如果在定義表的沒(méi)有指定的這個(gè)分區(qū)，當(dāng)我們插入大于20171211的數(shù)據(jù)的時(shí)候，會(huì)收到一個(gè)錯(cuò)誤。
我們?cè)趫?zhí)行查詢(xún)的時(shí)候，必須帶上分區(qū)字段。這樣可以使用分區(qū)剪裁功能
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)
注意執(zhí)行計(jì)劃中的partitions的內(nèi)容，只查詢(xún)了p7，p8，p9，p10三個(gè)分區(qū)，由此來(lái)看，使用to_days函數(shù)確實(shí)可以實(shí)現(xiàn)分區(qū)裁剪。
上面是基于datetime的，如果是timestamp類(lèi)型，我們遇到上面問(wèn)題呢？
事實(shí)上，MySQL提供了一種基于UNIX_TIMESTAMP函數(shù)的RANGE分區(qū)方案，而且，只能使用UNIX_TIMESTAMP函數(shù)，如果使用其它函數(shù)，譬如to_days，會(huì)報(bào)如下錯(cuò)誤：“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.)”。
下面來(lái)測(cè)試一下基于UNIX_TIMESTAMP函數(shù)的RANGE分區(qū)方案，看其能否實(shí)現(xiàn)分區(qū)裁剪。
針對(duì)TIMESTAMP的分區(qū)方案
創(chuàng)表語(yǔ)句如下：
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') )
);
插入數(shù)據(jù)并查看上述查詢(xún)的執(zhí)行計(jì)劃
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)
同樣也能實(shí)現(xiàn)分區(qū)裁剪。
在5.7版本之前，對(duì)于DATA和DATETIME類(lèi)型的列，如果要實(shí)現(xiàn)分區(qū)裁剪，只能使用YEAR() 和TO_DAYS()函數(shù)，在5.7版本中，又新增了TO_SECONDS()函數(shù)。
LIST 分區(qū)
LIST分區(qū)
LIST分區(qū)和RANGE分區(qū)類(lèi)似，區(qū)別在于LIST是枚舉值列表的集合，RANGE是連續(xù)的區(qū)間值的集合。二者在語(yǔ)法方面非常的相似。同樣建議LIST分區(qū)列是非null列，否則插入null值如果枚舉列表里面不存在null值會(huì)插入失敗，這點(diǎn)和其它的分區(qū)不一樣，RANGE分區(qū)會(huì)將其作為最小分區(qū)值存儲(chǔ)，HASH\KEY分為會(huì)將其轉(zhuǎn)換成0存儲(chǔ)，主要LIST分區(qū)只支持整形，非整形字段需要通過(guò)函數(shù)轉(zhuǎn)換成整形.
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 分區(qū)
我們?cè)趯?shí)際工作中經(jīng)常遇到像會(huì)員表的這種表。并沒(méi)有明顯可以分區(qū)的特征字段。但表數(shù)據(jù)有非常龐大。為了把這類(lèi)的數(shù)據(jù)進(jìn)行分區(qū)打散mysql 提供了hash分區(qū)。基于給定的分區(qū)個(gè)數(shù)，將數(shù)據(jù)分配到不同的分區(qū)，HASH分區(qū)只能針對(duì)整數(shù)進(jìn)行HASH，對(duì)于非整形的字段只能通過(guò)表達(dá)式將其轉(zhuǎn)換成整數(shù)。表達(dá)式可以是mysql中任意有效的函數(shù)或者表達(dá)式，對(duì)于非整形的HASH往表插入數(shù)據(jù)的過(guò)程中會(huì)多一步表達(dá)式的計(jì)算操作，所以不建議使用復(fù)雜的表達(dá)式這樣會(huì)影響性能。
Hash分區(qū)表的基本語(yǔ)句如下：
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;
注意：
- HASH分區(qū)可以不用指定PARTITIONS子句，如上文中的PARTITIONS 4，則默認(rèn)分區(qū)數(shù)為1。
- 不允許只寫(xiě)PARTITIONS，而不指定分區(qū)數(shù)。
- 同RANGE分區(qū)和LIST分區(qū)一樣，PARTITION BY HASH (expr)子句中的expr返回的必須是整數(shù)值。
- HASH分區(qū)的底層實(shí)現(xiàn)其實(shí)是基于MOD函數(shù)。譬如，對(duì)于下表
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4; 如果你要插入一個(gè)col3為“2017-09-15”的記錄，則分區(qū)的選擇是根據(jù)以下值決定的：
MOD(YEAR(‘2017-09-01'),4) = MOD(2017,4) = 1
LINEAR HASH分區(qū)
LINEAR HASH分區(qū)是HASH分區(qū)的一種特殊類(lèi)型，與HASH分區(qū)是基于MOD函數(shù)不同的是，它基于的是另外一種算法。
格式如下：
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;
說(shuō)明： 它的優(yōu)點(diǎn)是在數(shù)據(jù)量大的場(chǎng)景，譬如TB級(jí)，增加、刪除、合并和拆分分區(qū)會(huì)更快，缺點(diǎn)是，相對(duì)于HASH分區(qū)，它數(shù)據(jù)分布不均勻的概率更大。
KEY分區(qū)
KEY分區(qū)其實(shí)跟HASH分區(qū)差不多，不同點(diǎn)如下：
- KEY分區(qū)允許多列，而HASH分區(qū)只允許一列。
- 如果在有主鍵或者唯一鍵的情況下，key中分區(qū)列可不指定，默認(rèn)為主鍵或者唯一鍵，如果沒(méi)有，則必須顯性指定列。
- KEY分區(qū)對(duì)象必須為列，而不能是基于列的表達(dá)式。
- KEY分區(qū)和HASH分區(qū)的算法不一樣，PARTITION BY HASH (expr)，MOD取值的對(duì)象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。
格式如下：
CREATE TABLE k1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;
在沒(méi)有主鍵或者唯一鍵的情況下，格式如下：
CREATE TABLE tm1 (
s1 CHAR(32)
)
PARTITION BY KEY(s1)
PARTITIONS 10;
總結(jié)：
MySQL分區(qū)中如果存在主鍵或唯一鍵，則分區(qū)列必須包含在其中。
對(duì)于原生的RANGE分區(qū)，LIST分區(qū)，HASH分區(qū)，分區(qū)對(duì)象返回的只能是整數(shù)值。
分區(qū)字段不能為NULL，要不然怎么確定分區(qū)范圍呢，所以盡量NOT NULL
到此這篇關(guān)于MySQL最佳實(shí)踐之分區(qū)表基本類(lèi)型的文章就介紹到這了,更多相關(guān)MySQL分區(qū)表基本類(lèi)型內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- Mysql臨時(shí)表及分區(qū)表區(qū)別詳解
- MySQL分區(qū)表的基本入門(mén)教程
- MySQL優(yōu)化之分區(qū)表
- 解決mysql刪除用戶(hù) bug的問(wèn)題
- MySQL對(duì)window函數(shù)執(zhí)行sum函數(shù)可能出現(xiàn)的一個(gè)Bug
- MySQL關(guān)于exists的一個(gè)bug
- CentOS 安裝 PHP5.5+Redis+XDebug+Nginx+MySQL全紀(jì)錄
- 關(guān)于MySQL分區(qū)表的一個(gè)性能BUG
