MySQL 分割槽表原理及資料備份轉移實戰
1、分割槽表含義
分割槽表定義指根據可以設定為任意大小的規則，跨檔案系統分配單個表的多個部分。實際上，表的不同部分在不同的位置被儲存為單獨的表。使用者所選擇的、實現資料分割的規則被稱為分割槽函式，這在MySQL中它可以是模數，或者是簡單的匹配一個連續的數值區間或數值列表，或者是一個內部HASH函式，或一個線性HASH函式。
分表與分割槽的區別在於：分割槽從邏輯上來講只有一張表，而分表則是將一張表分解成多張表。
2、分割槽表優點
1)分割槽表更容易維護。對於那些已經失去儲存意義的資料，通常可以通過刪除與那些資料有關的分割槽，很容易地刪除那些資料。相反地，在某些情況下，新增新資料的過程又可以通過為那些新資料專門增加一個新的分割槽，來很方便地實現。
2)一些查詢可以得到極大的優化，這主要是藉助於滿足一個給定WHERE語句的資料可以只儲存在一個或多個分割槽內，這樣在查詢時就不用查詢其他剩餘的分割槽。因為分割槽可以在建立了分割槽表後進行修改，所以在第一次配置分割槽方案時還不曾這麼做時，可以重新組織資料，來提高那些常用查詢的效率。
3)優化查詢。涉及到例如SUM()和COUNT(),可以在多個分割槽上並行處理，最終結果只需通過總計所有分割槽得到的結果。
4)通過跨多個磁碟來分散資料查詢，來獲得更大的查詢吞吐量。
3、分割槽表限制
1)一個表最多隻能有1024個分割槽；
2) MySQL5.1中，分割槽表示式必須是整數，或者返回整數的表示式。在MySQL5.5中提供了非整數表示式分割槽的支援；
3)如果分割槽欄位中有主鍵或者唯一索引的列，那麼多有主鍵列和唯一索引列都必須包含進來。即：分割槽欄位要麼不包含主鍵或者索引列，要麼包含全部主鍵和索引列；
4)分割槽表中無法使用外來鍵約束；
5)MySQL的分割槽適用於一個表的所有資料和索引，不能只對表資料分割槽而不對索引分割槽，也不能只對索引分割槽而不對錶分割槽，也不能只對表的一部分資料分割槽。
6)分割槽鍵必須是INT型別，或者通過表示式返回INT型別，可以為NULL。唯一的例外是當分割槽型別為KEY分割槽的時候，可以使用其他型別的列作為分割槽鍵(BLOB or TEXT 列除外)
7)如果表中有主鍵和唯一索引，按主鍵欄位進行分割槽時，唯一索引列應該包含分割槽鍵。
8)目前mysql不支援空間型別和臨時表型別進行分割槽。不支援全文索引。
9)物件限制(分割槽表示式不能出現Stored functions, stored procedures, UDFs, orplugins,Declared variables or user variables.)
10)運算限制(支援加減乘等運算出現在分割槽表示式，但是運算後的結果必須是一個INT或者NULL。支援DIV,不支援/,|, &, ^, <<, >>, and ~ 不允許出現在分割槽表示式中)
11)sql_mode限制(官方強烈建議你在建立分割槽表後，永遠別改變mysql的sql_mode。因為在不同的模式下，某些函式或者運算返回的結果可能會不一樣)
12)不支援query_cache和INSERT DELAYED
13)分割槽鍵不能是一個子查詢(即使是子查詢返回的是int值或者null.)
14)子分割槽限制(只有RANG和LIST分割槽能進行子分割槽。HASH和KEY分割槽不能進行子分割槽並且子分割槽必須是HASH 或 KEY型別)
4、分割槽型別
1)水平分割槽(根據列屬性按行分)
如:一個包含十年發票記錄的表可以被分割槽為十個不同的分割槽，每個分割槽包含的是其中一年的記錄。
水平分割槽的幾種模式：
* Range（範圍）：這種模式允許DBA將資料劃分不同範圍。
如:可以將一個表通過年份劃分成三個分割槽，80年代（1980`s）的資料，90年代（1990`s）的資料以及任何在2000年（包括2000年）後的資料。
* Hash（雜湊）：這中模式允許DBA通過對錶的一個或多個列的Hash Key進行計算，最後通過這個Hash碼不同數值對應的資料區域進行分割槽。
如：可以建立一個對錶主鍵進行分割槽的表。
* Key（鍵值）：上面Hash模式的一種延伸，這裡的Hash Key是MySQL系統產生的。
* List（預定義列表）：這種模式允許系統通過DBA定義的列表的值所對應的行資料進行分割。例如：DBA建立了一個橫跨三個分割槽的表，分別根據2004年2005年和2006年值所對應的資料。
* Columns分割槽是對range，list分割槽的補充，彌補了後兩者只支援整型數分割槽（或者通過轉換為整型數），使得支援資料型別增加很多（所有整數型別，日期時間型別，字元型別），還支援多列分割槽。
注:在多列分割槽表上插入資料時，採用元組的比較，即多列排序，先根據field1排序，再根據field2排序，根據排序結果來來分割槽儲存資料。
* Composite（複合模式）：以上模式的組合使用。
如：在初始化已經進行了Range範圍分割槽的表上，可以對其中一個分割槽再進行hash雜湊分割槽。
垂直分割槽（按列分）：
如：一個包含了大text和BLOB列的表，這些text和BLOB列又不經常被訪問，可以把這些不經常使用的text和BLOB劃分到另一個分割槽，在保證它們資料相關性的同時還能提高訪問速度。
注意：子分割槽（關鍵字subparttition）：使用RANGE或LIST分割槽可以再次分割形成子分割槽，子分割槽可以是HASH分割槽或者KEY分割槽。建議在多磁碟上使用。
5、檢視是否有支援Partition分割槽表
mysql> SHOW PLUGINS ; +----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+---------+---------+ | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +----------------------------+----------+--------------------+---------+---------+ 或使用 mysql> SELECT PLUGIN_NAME as Name, PLUGIN_VERSION as Version, PLUGIN_STATUS as Status -> FROM INFORMATION_SCHEMA.PLUGINS -> WHERE PLUGIN_TYPE=`STORAGE ENGINE`;
注意:MySQL 5.6.1 之前的版本，可以下命令檢視 have_partitioning 引數，新的版本已移除該引數。
mysql> SHOW VARIABLES LIKE `%partition%`;
5、實戰常用分割槽表幾種模式
1)使用RANGE分割槽模式
####建立測試表t1，並插入接近400萬行資料，再沒有分割槽的情況下，對查詢某一條件耗時
mysql> CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT `表主鍵`, `pid` int(10) unsigned NOT NULL COMMENT `產品ID`, `price` decimal(15,2) NOT NULL COMMENT `單價`, `num` int(11) NOT NULL COMMENT `購買數量`, `uid` int(10) unsigned NOT NULL COMMENT `客戶ID`, `atime` datetime NOT NULL COMMENT `下單時間`, `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT `修改時間`, `isdel` tinyint(4) NOT NULL DEFAULT `0` COMMENT `軟刪除標識`, PRIMARY KEY (`id`,`atime`) ) INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,`2016-05-01 00:00:00`); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,`2017-05-01 00:00:00`); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,`2018-05-01 00:00:00`); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,`2015-05-01 00:00:00`); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,`2016-05-01 00:00:00`); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,`2017-05-01 00:00:00`); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,`2018-05-01 00:00:00`); /**********************************主從複製大量資料******************************/ mysql> INSERT INTO `t1`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `t1`; mysql> SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); 1048576 rows in set (5.62 sec) #沒有分割槽表情況耗時5.62s
如果是針對已有的表進行表分割槽，可以使用ALTER TABLE來進行更改表為分割槽表，這個操作會建立一個分割槽表，然後自動進行資料copy然後刪除原表。
注: 這種會使伺服器資源消耗比較大(400多萬資料要1分多鐘)
mysql> ALTER TABLE t1 PARTITION BY RANGE (YEAR(atime)) -> ( -> PARTITION p0 VALUES LESS THAN (2016), -> PARTITION p1 VALUES LESS THAN (2017), -> PARTITION p2 VALUES LESS THAN (2018), -> PARTITION p3 VALUES LESS THAN MAXVALUE ); Query OK, 4194304 rows affected (1 min 8.32 sec) mysql> EXPLAIN PARTITIONS SELECT * FROM `t1`; #檢視分割槽情況 +----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+ | 1 | SIMPLE | t1 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 4180974 | 100.00 | NULL | +----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+ 1 row in set, 2 warnings (0.00 sec)
同樣用上面的查詢測試結果
mysql> SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); 1048576 rows in set (4.46 sec) #與上面沒有分割槽查詢執行的時間相比少了接近1s mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); #檢視查詢使用的分割槽情況 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | t1 | p0,p1,p2 | ALL | NULL | NULL | NULL | NULL | 3135804 | 3.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec)
同時也要注意，進行表分割槽以後，mysql存放的資料資料夾中該表的存放檔案也被拆分為多個
-rw-r----- 1 mysql mysql 8.7K 2月 14 14:49 t1.frm -rw-r----- 1 mysql mysql 36M 2月 14 14:50 t1#P#p0.ibd -rw-r----- 1 mysql mysql 64M 2月 14 14:50 t1#P#p1.ibd -rw-r----- 1 mysql mysql 92M 2月 14 14:50 t1#P#p2.ibd -rw-r----- 1 mysql mysql 64M 2月 14 14:50 t1#P#p3.ibd
實際生產環境中，大多是採用另外一種方式:新建一個和原來表一樣的分割槽表，然後把資料從原表匯出，接著匯入新表，最後建立普通索引。
mysql> CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT `表主鍵`, `pid` int(10) unsigned NOT NULL COMMENT `產品ID`, `price` decimal(15,2) NOT NULL COMMENT `單價`, `num` int(11) NOT NULL COMMENT `購買數量`, `uid` int(10) unsigned NOT NULL COMMENT `客戶ID`, `atime` datetime NOT NULL COMMENT `下單時間`, `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT `修改時間`, `isdel` tinyint(4) NOT NULL DEFAULT `0` COMMENT `軟刪除標識`, PRIMARY KEY (`id`,`atime`) ) PARTITION BY RANGE COLUMNS(atime) ( PARTITION p0 VALUES LESS THAN (`2016-01-01`), PARTITION p1 VALUES LESS THAN (`2016-02-01`), PARTITION p2 VALUES LESS THAN (`2016-03-01`), PARTITION p3 VALUES LESS THAN (`2016-04-01`), PARTITION p4 VALUES LESS THAN (`2016-05-01`), PARTITION p5 VALUES LESS THAN (`2016-06-01`), PARTITION p6 VALUES LESS THAN (`2016-07-01`), PARTITION p7 VALUES LESS THAN (`2016-08-01`), PARTITION p8 VALUES LESS THAN (`2016-09-01`), PARTITION p9 VALUES LESS THAN (`2016-10-01`), PARTITION p10 VALUES LESS THAN (`2016-11-01`), PARTITION p11 VALUES LESS THAN (`2016-12-01`), PARTITION p12 VALUES LESS THAN (`2017-01-01`), PARTITION p13 VALUES LESS THAN (`2017-02-01`), PARTITION p14 VALUES LESS THAN (`2017-03-01`), PARTITION p15 VALUES LESS THAN (`2017-04-01`), PARTITION p16 VALUES LESS THAN (`2017-05-01`), PARTITION p17 VALUES LESS THAN (`2017-06-01`), PARTITION p18 VALUES LESS THAN (`2017-07-01`), PARTITION p19 VALUES LESS THAN (`2017-08-01`), PARTITION p20 VALUES LESS THAN (`2017-09-01`), PARTITION p21 VALUES LESS THAN (`2017-10-01`), PARTITION p22 VALUES LESS THAN (`2017-11-01`), PARTITION p23 VALUES LESS THAN (`2017-12-01`), PARTITION p24 VALUES LESS THAN (`2018-01-01`), PARTITION p25 VALUES LESS THAN (`2018-02-01`), PARTITION p26 VALUES LESS THAN (`2018-03-01`), PARTITION p27 VALUES LESS THAN (`2018-04-01`), PARTITION p28 VALUES LESS THAN (`2018-05-01`), PARTITION p29 VALUES LESS THAN (`2018-06-01`), PARTITION p30 VALUES LESS THAN (`2018-07-01`), PARTITION p31 VALUES LESS THAN (`2018-08-01`), PARTITION p32 VALUES LESS THAN (`2018-09-01`), PARTITION p33 VALUES LESS THAN (`2018-10-01`), PARTITION p34 VALUES LESS THAN (`2018-11-01`), PARTITION p35 VALUES LESS THAN (`2018-12-01`), PARTITION p36 VALUES LESS THAN MAXVALUE );
注:表主鍵只有id，而分割槽欄位是atime, 這裡主鍵要修改為 id,stsdate 聯合主鍵，分割槽表要求分割槽欄位要是主鍵或者是主鍵的一部分!!!
mysql> EXPLAIN PARTITIONS SELECT * FROM `t2`G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 2 warnings (0.00 sec) *******************************************插入資料************************************************* INSERT INTO `t2`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `t1`; Query OK, 4194304 rows affected (1 min 18.54 sec) Records: 4194304 Duplicates: 0 Warnings: 0
或採用匯出資料再匯入資料，可再新增索引
mysqldump -u dbname -p --no-create-info dbname t2 > t2.sql
修改表名，匯入資料，測試下ok，刪除原來的表。
2)使用LIST分割槽模式(如果原表存在主鍵強烈建立新表時，把原主鍵和要分割槽欄位作為聯合主鍵一併建立)
mysql> CREATE TABLE `tb01` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT `表主鍵`, `pid` int(10) unsigned NOT NULL COMMENT `產品ID`, `price` decimal(15,2) NOT NULL COMMENT `單價`, `num` int(11) NOT NULL COMMENT `購買數量`, `uid` int(10) unsigned NOT NULL COMMENT `客戶ID`, `atime` datetime NOT NULL COMMENT `下單時間`, `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT `修改時間`, `isdel` tinyint(4) NOT NULL DEFAULT `0` COMMENT `軟刪除標識`, PRIMARY KEY (`id`,`num`) ); *****************************插入測試資料****************************************************** INSERT INTO `tb01`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `tb`; Query OK, 3145728 rows affected (46.26 sec) Records: 3145728 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE tb01 PARTITION BY LIST(num) ( PARTITION pl01 VALUES IN (1,3), PARTITION pl02 VALUES IN (2,4), PARTITION pl03 VALUES IN (5,7), PARTITION pl04 VALUES IN (6,8), PARTITION pl05 VALUES IN (9,10) ); Query OK, 3145728 rows affected (48.86 sec) Records: 3145728 Duplicates: 0 Warnings: 0
存放mysql資料檔案中生成，以下檔案
-rw-r----- 1 mysql mysql 8.7K 2月 15 11:35 tb01.frm -rw-r----- 1 mysql mysql 56M 2月 15 11:36 tb01#P#pl01.ibd -rw-r----- 1 mysql mysql 32M 2月 15 11:36 tb01#P#pl02.ibd -rw-r----- 1 mysql mysql 36M 2月 15 11:36 tb01#P#pl03.ibd -rw-r----- 1 mysql mysql 36M 2月 15 11:36 tb01#P#pl04.ibd -rw-r----- 1 mysql mysql 52M 2月 15 11:36 tb01#P#pl05.ibd
mysql> EXPLAIN PARTITIONS SELECT * FROM `tb01`; +----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+-------+ | 1 | SIMPLE | tb01 | pl01,pl02,pl03,pl04,pl05 | ALL | NULL | NULL | NULL | NULL | 3136392 | 100.00 | NULL | +----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+-------+ 1 row in set, 2 warnings (0.00 sec)
3)COLUMNS分割槽
建立多列分割槽表tb02，這裡兩列都不是聯合主鍵
mysql> CREATE TABLE tb02( -> a int not null, -> b int not null -> ) -> PARTITION BY RANGE COLUMNS(a,b)( -> partition p0 values less than(0,10), -> partition p1 values less than(10,20), -> partition p2 values less than(10,30), -> partition p3 values less than(maxvalue,maxvalue) -> ); mysql> EXPLAIN PARTITIONS SELECT * FROM `tb02`; #檢視 +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | tb02 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) mysql> insert into tb02 values (11,13); #手工插入測試資料 Query OK, 1 row affected (0.01 sec) mysql> select PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.partitions where table_schema=schema() and table_name=`tb02`; +----------------+----------------------+------------+ | PARTITION_NAME | PARTITION_EXPRESSION | TABLE_ROWS | +----------------+----------------------+------------+ | p0 | `a`,`b` | 0 | | p1 | `a`,`b` | 0 | | p2 | `a`,`b` | 0 | | p3 | `a`,`b` | 1 | +----------------+----------------------+------------+ 4 rows in set (0.03 sec)
4)Hase分割槽
HASH主要是為了讓資料在設定個數的分割槽中儘可能分佈平均，執行雜湊分割槽時，mysql會對分割槽鍵執行雜湊函式，以確定資料放在哪個分割槽中。HASH分割槽分為常規HASH分割槽和線性HASH分割槽，前者使用取模演算法，後者使用線性2的冪的運算規則。
CREATE TABLE `tb03` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT `表主鍵`, `pid` int(10) unsigned NOT NULL COMMENT `產品ID`, `price` decimal(15,2) NOT NULL COMMENT `單價`, `num` int(11) NOT NULL COMMENT `購買數量`, `uid` int(10) unsigned NOT NULL COMMENT `客戶ID`, `atime` datetime NOT NULL COMMENT `下單時間`, `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT `修改時間`, `isdel` tinyint(4) NOT NULL DEFAULT `0` COMMENT `軟刪除標識`, PRIMARY KEY (`id`) ) PARTITION BY HASH(id) partitions 4; 插入2行資料： INSERT INTO tb03(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); INSERT INTO tb03(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); mysql> explain partitions select * from tb03 where id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | tb03 | p1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) mysql> explain partitions select * from tb03 where id=2; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | tb03 | p2 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec)
注意:HASH分割槽雖然儘可能讓資料平均地分佈在每個分割槽上，提高了查詢效率，但增加了分割槽管理的代價，比如以前有5個分割槽，現在要加上一個分割槽，演算法有mod(expr,5)變成(expr,6)，原5個分割槽的資料大部分要重新計算重新分割槽。雖然使用線性HASH分割槽會降低分割槽管理的代價，但是資料卻沒有常規HASH分佈得那麼均勻。
5)KEY分割槽
KEY分割槽類似與HASH分割槽，但是不能自定義表示式，不過支援分割槽鍵的型別很多，除Text,Blob等文字型別。
CREATE TABLE `tb04` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT `表主鍵`, `pid` int(10) unsigned NOT NULL COMMENT `產品ID`, `price` decimal(15,2) NOT NULL COMMENT `單價`, `num` int(11) NOT NULL COMMENT `購買數量`, `uid` int(10) unsigned NOT NULL COMMENT `客戶ID`, `atime` datetime NOT NULL COMMENT `下單時間`, `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT `修改時間`, `isdel` tinyint(4) NOT NULL DEFAULT `0` COMMENT `軟刪除標識`, PRIMARY KEY (`id`) ) PARTITION BY KEY(id) partitions 4; 插入2行資料： INSERT INTO tb04(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); INSERT INTO tb04(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); #用執行任務檢視記錄落在分割槽情況 mysql> explain partitions select * from tb04 where id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | tb04 | p0 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) mysql> explain partitions select * from tb04 where id=2; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | tb04 | p3 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec)
6)分割槽表管理
建議在生產環境中儘量不要修改分割槽，alter會讀出存在舊錶中的資料，再存入新定義的表中，過程IO將很大，而且全表都會鎖住。
*1*刪除分割槽:示例以上面tb01表
–未刪除p05分割槽查詢資料，主要驗證當刪除分割槽資料是否被刪除
mysql> select count(1) from tb01 where num=10; +----------+ | count(1) | +----------+ | 524288 | +----------+ 1 row in set (0.37 sec) mysql> alter table tb01 drop partition pl05; #刪除pl05分割槽,如：一次性刪除多個分割槽，alter table tb01 drop partition pl04,pl05; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(1) from tb01 where num=10; #結果資料也被刪除，慎重操作 +----------+ | count(1) | +----------+ | 0 | +----------+ 1 row in set (0.01 sec)
注意：刪除分割槽會刪除資料，謹慎操作；不可以刪除hash或者key分割槽。
*2*增加分割槽
注：新分割槽的值不能包含任意一個現有分割槽中值列表中的值，否則報錯;新增分割槽會重新整理資料，原有資料不會丟失。有MAXVALUE值後，直接不能直接加分割槽，如示例以上面的t1表為例子。
mysql> ALTER TABLE t1 ADD PARTITION (PARTITION P4 VALUES LESS THAN (2018) ) ; ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition 示例：把tb01上面刪除的pl05分割槽新增 mysql> ALTER TABLE tb01 ADD PARTITION(PARTITION pl05 VALUES IN (9,10)); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0
*3*分解分割槽
注：Reorganize partition關鍵字可以對錶的部分分割槽或全部分割槽進行修改，並且不會丟失資料。分解前後分割槽的整體範圍應該一致。
示例：
mysql> create table tb05 -> (dep int, -> birthdate date, -> salary int -> ) -> partition by range(salary) -> ( -> partition p1 values less than (1000), -> partition p2 values less than (2000), -> partition p3 values less than maxvalue -> ); Query OK, 0 rows affected (0.08 sec) ****插入一條測試資料 mysql> insert tb05 values(1,`2016-03-06`,80); Query OK, 1 row affected (0.01 sec) mysql>alter table tb05 reorganize partition p1 into( partition p01 values less than (100), partition p02 values less than (1000) ); ----不會丟失資料 mysql> explain partitions select * from tb05 where salary=80; #檢視已經落在新的分割槽p01上 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tb05 | p01 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec)
*4*合併分割槽
注:把2個分割槽合併為一個。
示例：把上面的tb05表中分解的p01和p02合併至p1上
mysql> alter table tb05 reorganize partition p01,p02 into(partition p1 values less than (1000)); --不會丟失資料 Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain partitions select * from tb05 where salary=80; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tb05 | p1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec)
*5*重新定義hash分割槽表:
RANGE和LIST分割槽在重新定義時，只能重新定義相鄰的分割槽，不可以跳過分割槽，並且重新定義的分割槽區間必須和原分割槽區間一致，也不可以改變分割槽的型別。
示例:
mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`; +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | tb03 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) mysql> Alter table tb03 partition by hash(id)partitions 8; #不會丟失資料 Query OK, 4 rows affected (0.13 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`; +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | tb03 | p0,p1,p2,p3,p4,p5,p6,p7 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 2 warnings (0.02 sec)
*6*刪除表的所有分割槽:
示例:刪除tb03表所有分割槽
mysql> Alter table tb03 remove partitioning; #不會丟失資料 Query OK, 4 rows affected (0.07 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | tb03 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec)
*7*整理分割槽碎片
注:如果從分割槽中刪除了大量的行，或者對一個帶有可變長度的行（也就是說，有VARCHAR，BLOB，或TEXT型別的列）作了許多修改，可以使用“ALTER TABLE … OPTIMIZE PARTITION”來收回沒有使用的空間，並整理分割槽資料檔案的碎片。
ALTER TABLE tb03 optimize partition p1,p2;
*8*分析分割槽：
讀取並儲存分割槽的鍵分佈。
ALTER TABLE tb03 analyze partition p0,p1,p2,p3,p4,p5,p6,p7;
*9*檢查分割槽：
可以使用幾乎與對非分割槽表使用CHECK TABLE 相同的方式檢查分割槽。這個命令可以告訴tb04表分割槽p1,p2中的資料或索引是否已經被破壞。如果發生了這種情況，使用“ALTER TABLE … REPAIR PARTITION”來修補該分割槽。
mysql> ALTER TABLE tb04 CHECK partition p1,p2; +--------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+----------+ | testsms.tb04 | check | status | OK | +--------------+-------+----------+----------+ 1 row in set (0.01 sec)
6、實際生產簡單應用，場景:之前有個沒有分割槽的大資料量表SmsSend(例表，大概2800萬行記錄)，統計過程非常的耗時，考慮用年分割槽，並且對歷史資料庫進行備份，把過去2014年的資料轉移至新的備份表smssendbak。如線上重定義比較耗時間，可採用exchange處理！
1)檢視當前SmsSend表
mysql> SHOW CREATE TABLE SmsSend; #檢視建立資訊，未進行分割槽 | SmsSend | CREATE TABLE `SmsSend` ( `Guid` char(36) NOT NULL COMMENT `唯一標識`, `SID` varbinary(85) DEFAULT NULL COMMENT `商家唯一編號`, `Mobile` longtext NOT NULL COMMENT `接收手機號(以","分割)`, `SmsContent` varchar(500) NOT NULL COMMENT `簡訊內容`, `SmsCount` int(11) NOT NULL DEFAULT `1` COMMENT `條數`, `Status` int(11) NOT NULL COMMENT `當前狀態(0,等待傳送;1,傳送成功;-1,傳送失敗)`, `SendChanelKeyName` varchar(20) DEFAULT NULL COMMENT `傳送通道標識`, `SendTime` datetime NOT NULL COMMENT `傳送成功時間`, `SendType` int(11) NOT NULL DEFAULT `1` COMMENT `簡訊傳送型別(1,單發;2,群發)`, `ReceiveTime` datetime DEFAULT NULL COMMENT `接收到回覆報告的時間`, `Priority` int(11) NOT NULL DEFAULT `0` COMMENT `優先順序`, `UserAccount` varchar(50) DEFAULT NULL COMMENT `操作員`, `ChainStoreGuid` char(36) DEFAULT NULL COMMENT `操作店面唯一標識`, `RelationKey` longtext COMMENT `回覆報告關聯標識`, `Meno` text COMMENT `備註`, `IsFree` bit(1) NOT NULL DEFAULT b`0` COMMENT `是否免費` ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | mysql> SELECT COUNT(*) FROM SmsSend; #行記錄 +----------+ | COUNT(*) | +----------+ | 28259803 | +----------+ 1 row in set (1 min 52.60 sec) #可得知大資料表下線上分割槽比較慢並且耗效能 mysql> ALTER TABLE SmsSend PARTITION BY RANGE (YEAR(SendTime)) -> ( -> PARTITION py01 VALUES LESS THAN (2015), -> PARTITION py02 VALUES LESS THAN (2016), -> PARTITION py03 VALUES LESS THAN (2017) ); Query OK, 28259803 rows affected (20 min 36.05 sec) Records: 28259803 Duplicates: 0 Warnings: 0 #檢視分割槽記錄數 mysql> select count(1) from SmsSend partition(py01); +----------+ | count(1) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) mysql> explain partitions select * from SmsSend where SendTime < `2015-01-01`; #2014年的資料落在第一分割槽 +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | SmsSend | py01 | ALL | NULL | NULL | NULL | NULL | 10 | 33.33 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql> select count(1) from SmsSend partition(py02); +----------+ | count(1) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec)
2)快速建立一個smssendbak備份表與原SmsSend表結構一致，並刪除備份表所有分割槽
mysql> CREATE TABLE smssendbak LIKE SmsSend; Query OK, 0 rows affected (0.14 sec) mysql> ALTER TABLE smssendbak REMOVE PARTITIONING; Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0
3)使用EXCHANGE PARTITION轉移分割槽資料至備份表，並檢視原來表分割槽記錄以及新備份表smssendbak記錄
mysql> ALTER TABLE SmsSend EXCHANGE PARTITION py01 WITH TABLE smssendbak; Query OK, 0 rows affected (0.13 sec) mysql> select count(1) from SmsSend partition(py01); #對比上面原SmsSend表分割槽的記錄 +----------+ | count(1) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(1) FROM smssendbak; #檢視新smssendbak備份錶轉移記錄 +----------+ | COUNT(1) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec)
*****************測試使用的表*********************************************************************** 建立一個基礎測試表: CREATE TABLE `tb` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT `表主鍵`, `pid` int(10) unsigned NOT NULL COMMENT `產品ID`, `price` decimal(15,2) NOT NULL COMMENT `單價`, `num` int(11) NOT NULL COMMENT `購買數量`, `uid` int(10) unsigned NOT NULL COMMENT `客戶ID`, `atime` datetime NOT NULL COMMENT `下單時間`, `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT `修改時間`, `isdel` tinyint(4) NOT NULL DEFAULT `0` COMMENT `軟刪除標識`, ) ; 插入資料： INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,`2016-05-01 00:00:00`); INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,`2017-05-01 00:00:00`); INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,`2018-05-01 00:00:00`); INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,`2015-05-01 00:00:00`); INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,`2016-05-01 00:00:00`); INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,`2017-05-01 00:00:00`); ************************************插入大量的資料(建議百萬以上)************************************* INSERT INTO `tb`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `tb`; ****注意，如果要刪除自增長的主鍵id(修改過程中，建議該庫改為只讀)，如下操作： Alter table tb change id id int(10)； #先刪除自增長 Alter table tb drop primary key；#刪除主建 Alter table tb change id id int not null auto_increment; #如果想重新設定為自增欄位 Alter table tb auto_increment=1; #自增起始值