20 分區 20 分區... 1 20.1 MySQL的分區概述... 2 20.2 分區類型... 3 20.2.1 RANGE分區... 3 20.2.2 LIST分區... 5 20.2.3 COLUMNS分區... 7 20.2.3.1 RANGE COLUMNS分區... 7 20.2.3 ...
20 分區
可以通過show plugins查看是否支持分區。
mysql> SHOW PLUGINS;
+------------+----------+----------------+---------+---------+
| Name | Status | Type | Library | License |
+------------+----------+----------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndbcluster | DISABLED | STORAGE ENGINE | NULL | GPL |
+------------+----------+----------------+---------+---------+
11 rows in set (0.00 sec)
也可以檢查information_schema.plugins表檢查是否支持。
如果partition的status不是active，或者沒有記錄。那麼就不支持分區。
如果編譯的時候已經支持了分區，就不需要去啟動。如果想要不支持分區，可以使用參數—skip-partition選項。不啟動分區後，不能看到已經分區的表，也不能刪除他們。
20.1 MySQL的分區概述
SQL標準一般不會涉及到關於數據存放物理方面。SQL語言本身儘量從數據結構，schema，表，行，列中獨立出來。但是很多高級的資料庫管理系統都涉及了某些數據存放的物理位置，文件系統，硬體等。在MySQL，InnoDB存儲引擎，支持表空間已經很久，MySQL服務可以把不同的資料庫存放到不同的物理目錄中。
分區者更進了一步，把一個表根據規則分佈到文件系統。實際上表的不同分區以獨立表的方式被保存在不同的位置上。用戶選擇的分區規則，在MySQL中可以是一個模塊，一個range或者一個值的列表，或者內部hash函數，或者線性hash函數。這個方法根據用戶指定的分區類型決定，參數為用戶提供表達式的值。這個表達式可以是一個列的值，可以是一個或者多個列的值，也可以是列值的集合，根據分區類型決定。
比如RANGE,LIST,[LINEAR]HASH分區，把分區列傳遞到分區函數，然後返回一個整型表示該行應該存放的分區號。函數必須是非常量或者非隨機。也不能包含查詢但是可以使用SQL表達式只要表達式返回要不是NULL要不是整型數據。
對於[LINEAR] KEY,RANGE COLUMNS,LIST COLUMNS分區，分區表達式由一個或者多個列組成。
對於[LINEAR] KEY表達式，分區函數由MySQL提供。
這個就是水平分區，對於垂直分區目前不支持。大多數存儲引擎是支持分區的，MySQL分區引擎是獨立的一層，可以和其他引擎進行交互。在MySQL 5.7一個表的所有分區必須使用相同的引擎。以下引擎不支持分區：MERGE，CSV，FEDERATED存儲引擎。
要指定分區存儲引擎，和非分區表一樣，指定engine參數：
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;
每個分區都可以指定存儲引擎，但是在MySQL 5.7中沒有效果。
每個分區的數據和索引可以適應data directory 和index directory選項分配獨立的目錄。DATA DIRECTOY和INDEX DIRECTORY對於windows的myisam存儲引擎不支持。Innodb所有平臺都支持。
分區表達式中的涉及到的列必須是唯一索引的一部分，包括主鍵。也就是說以下表不能創建分區：
CREATE TABLE tnp (
id INT NOT NULL AUTO_INCREMENT,
ref BIGINT NOT NULL,
name VARCHAR(255),
PRIMARY KEY pk (id),
UNIQUE KEY uk (name)
);
因為pk，uk沒有相同的列，沒有列可以用來作為分區列。要麼pk上加上name，id加到uk，也可以直接刪掉uk。
分區表的一些好處：
1.分區表可以把一個表的數據分散到不同的文件系統或者磁碟中。
2.分區表的數據比較容易刪除，可以直接刪除一個分區。
3.如果where子句可以滿足分區列，那麼查詢性能會得到提升。
其他的好處：
1.聚合函數，如果在分區表上可以併發。
2.數據分散在多個磁碟上，加大查詢的吞吐量。
20.2 分區類型
20.2.1 RANGE分區
安裝RANGE分區表的，行會根據RANGE的劃分存放到分區中。RANGE是連續的但是沒有重疊，使用VALEUS LESS THAN定義。對於store_id進行分區：
CREATE TABLE employees (
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 NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);
在這個分區框架，所有的行store_id從1到5都保存在p0分區中，6到10保存在p1中類推。在這裡如果插入21就會報錯因為沒有地方存放這個記錄。可以做一下修改存放大記錄：
CREATE TABLE employees (
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 NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION
p3 VALUES LESS THAN MAXVALUE
);
MAXVALUE表示最大值，比int型最大值還要大。所以大於16的都會被放在p3分區中。分區列也可以使用表達式：
CREATE TABLE employees (
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 RANGE ( YEAR(separated) ) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
那麼在1991年之前離職的員工都會保存在p0依次類推。如果分區列時個時間戳欄位，可以如下分區：
CREATE TABLE quarterly_report_status (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01
00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01
00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01
00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01
00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01
00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01
00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01
00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01
00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01
00:00:00') ),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
RANGE在以下場景下非常有用：
1.想要刪除老的數據，剛好在p0上，那麼可以直接alter table employee drop partition p0刪除分區，達到刪除數據的目的
2.想要使用包含時間或者日期的列，或者其他連續的升序列。
3.想要頻繁的根據分區列進行查詢。可以快速的定位到某個分區。
有個RANGE分區的變種RANGE COLUMNS，可以多個列一起決定一個分區。
20.2.2 LIST分區
很多情況下range分區和list分區很像。LIST和RANGE的區別是LIST是一組值，RANGE是一組連續的區間。
CREATE TABLE employees (
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 LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
LIST分區刪除數據也會很快比如要刪除pWest分區數據，用階段分區比delete快。和RANGE不同沒有MAXVALUE。所有的值都要在分區裡面，不然就報錯。
mysql> CREATE TABLE h2 (
-> c1 INT,
-> c2 INT
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (1, 4, 7),
-> PARTITION p1 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3
報錯的時候，如果表示innodb表就會全部回滾。如果是非事務表，錯誤之前都插入了，之後的都沒插入。
也可以通過IGNORE關鍵字，對錯誤行進行忽略，這樣錯誤行就會自動被忽略，正常行就可以被全部插入。
mysql> TRUNCATE h2;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM h2;
Empty set (0.00 sec)
mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected (0.00 sec)
Records: 5 Duplicates: 2 Warnings: 0
mysql> SELECT * FROM h2;
+------+------+
| c1 | c2 |
+------+------+
| 7 | 5 |
| 1 | 9 |
| 2 | 5 |
+------+------+
3 rows in set (0.00 sec)
20.2.3 COLUMNS分區
COLUMNS分區是RANGE和LIST的變種。COLUMNS分區可以可以使用多個列作為分解鍵。2個列都可以用來分配分區。另外RANGE COLUMNS和LIST COLUMNS分區支持費insert定義range或者list項。允許的欄位類型如下：
1.所有int類型
2.date和datetime類型
3.字元串類型，char，varchar，binary和varbinary。TEXT和BLOB列不支持分區。
20.2.3.1 RANGE COLUMNS分區
RANGE列分區和RANGE分區很像。就是啟用了多列range。另外你可以使用其他數據類型，不一定只用int類型。
RANGE COLUMNS和RANGE明顯的區別有以下幾點：
1.RANGE COLUMNS不能用表達式，只能是列名
2.RANGE COLUMNS可以使用一個或者多個列。
3.RANGE COLUMNS分區列可以不是int類型。
mysql> CREATE TABLE rcx (
-> a INT,
-> b INT,
-> c CHAR(3),
-> d INT
-> )
-> PARTITION BY RANGE COLUMNS(a,d,c) (
-> PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
-> PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),
-> PARTITION p2 VALUES LESS THAN (15,30,'sss'),
-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
-> );
Query OK, 0 rows affected (0.15 sec)
如創建了一個rcx表，有4個列，a,b,c進行分區。那麼如果有一樣要插入，根據a,b,c依次對比，來決定放入哪個分區。
20.2.3.2 LIST COLUMNS分區
MySQL 5.7支持LIST COLUMNS分區。是LIST分區的變種可以使用非int類型作為分區列，而且可以使用多個列。
20.2.4 Hash分區
使用HASH分區可以保證數據均勻的分佈在各分區上面。使用RANGE，LIST分區需要顯示給定值進行分區。使用hash分區，MySQL會幫你處理。
使用hash分區，使用create table子句PARTITION BY HASH(expr)，表達式返回int類型。然後需要指定分區個數，如PARTITIONS 4。
CREATE TABLE employees (
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 HASH(store_id)
PARTITIONS 4;
如果不包含PARTITIONS 那麼預設分區個數是1個。
20.2.4.1 LINEAR HASH分區
MySQL也支持線性hash分區，和傳統分區不一樣線性hash利用線性2的指數演算法，來代替老的hash函數。
演算法大致如下：
num表示分區個數
1.V=POWER(2,CEILING(LOG(2,NUM)))，V為num的2的最小冪次的值。
2.N=expr&(V-1)計算分區號
3.如果N>=num,N=N&(ceil(V/2)-1)，否則就用N編號的分區。
如表如下：
CREATE TABLE t1 (col1 INT, col2 CHAR(5),
col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;
插入了col3為‘20030414’的列：
V = POWER(2, CEILING( LOG(2,6) )) = 8
N = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7
= 3
(3 >= 6 is FALSE: record stored in partition #3)
如果N>=num那麼就需要額外計算，比如：
V = 8
N = YEAR('1998-10-19') & (8-1)
= 1998 & 7
= 6
(6 >= 6 is TRUE: additional step required)
