mysql 分割槽

MYSQL分割槽

分割槽的優點

1. 儲存更多資料
2. 優化查詢，只掃描必要的一個或者多個分割槽，針對count()和sum()只要對分割槽統計再彙總
3. 對於過期或不需要儲存的資料，操作分割槽更快
4. 跨多個磁碟來分散資料查詢，以獲得更大的查詢吞吐量

分割槽概述

分割槽鍵的引入。

查詢是否支援分割槽

mysql> show variables like '%partition%';
Empty set (0.01 sec)

mysql> show variables like '%partition%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| innodb_adaptive_hash_index_partitions | 1     |
+---------------------------------------+-------+

MySQL支援建立分割槽的引擎：MyISam、InnoDB、Memory，不支援分割槽：MERGE、CSV

在MySQL5.1中，同一個分割槽表的所有分割槽必須使用同一個儲存引擎，但是在同一個MySQL伺服器中或者同一個資料庫中、對於不同的分割槽表可以使用不同的儲存引擎。

MySQL的分割槽適用於一個表的所有資料和索引。

設定引擎ENGINE必須在CREATE TABLE語句中的其他任何部分之前

mysql> create table emp(empid int,salay decimal(7,2),birth_date date) 
engine=innodb 
partition by hash(month(birth_date)) 
partitions 6;
Query OK, 0 rows affected (0.06 sec)

分割槽型別

1. RANGE 分割槽：基於屬於一個給定連續區間的列值，把多行分配給分割槽。
2. LIST 分割槽：類似於RANGE分割槽，區別在於LIST分割槽是基於列舉出的值列表分割槽，RANGE是基於給定的連續區間範圍分割槽
3. HASH分割槽：基於給定的分割槽個數，把資料分配到不同的分割槽
4. KEY**分割槽**：類似於RANGE分割槽

在mysql5.1中：range、list、hash分割槽鍵必須是int型別，key還可以使用blob、text。在mysql5.5中已經支援非整數型別做分割槽鍵

分割槽時注意

1. 要麼分割槽表上沒有主鍵/唯一鍵，要麼分割槽表主鍵/唯一鍵必須包含分割槽鍵。（否則會報錯）
2. 分割槽的名字不區分大小寫

RANGE分割槽

利用取值範圍將資料分成分割槽，區間要連續且不能互相重疊。

RANGE分割槽中，分割槽鍵如果是NULL值會被當作一個最小值來處理。

mysql> create table emp_date(
    id int not null,
    ename varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job varchar(30) not null,
    store_id int not null
) 
    partition by range (year(separated)) ( 
        partition p0 values less than (1995), 
        partition p1 values less than (2000), 
        partition p2 values less than (2005) 
    );
Query OK, 0 rows affected (0.04 sec)

超出最大分割槽範圍會報錯，要是有個最大值maxvalue兜底就好了！你想要的都給你！

mysql> alter table emp_date add partition (partition p3 values less than maxvalue);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

前面說了RANGE只支援int做分割槽鍵，太沒有人性了，現實業務場景那麼多，MySQL5.5起改進了這個問題，新增RANGE COLUMNS 分割槽支援非整型分割槽，這樣建立日期分割槽就不用通過函式多此一舉了。no code no bb!

mysql> drop  table `emp_date`;
Query OK, 0 rows affected (0.04 sec)

mysql> create table emp_date(
    ->     id int not null,
    ->     ename varchar(30),
    ->     hired date not null default '1970-01-01',
    ->     separated date not null default '9999-12-31',
    ->     job varchar(30) not null,
    ->     store_id int not null
    -> ) 
    ->     partition by range columns (separated) ( 
    ->         partition p0 values less than ('1995-01-01'), 
    ->         partition p1 values less than ('2000-01-01'), 
    ->         partition p2 values less than ('2005-01-01') 
    ->     );
Query OK, 0 rows affected (0.04 sec)

這種操作還不夠常用，經常要按天分割槽怎麼搞？

MySQL5.1：分割槽日期處理函式只有year()和to_days()

MySQL5.5：增加了to_seconds()，把日期轉換成秒。

說了那麼多，RANGE分割槽功能特別適用哪些情況？

1. 當需要刪除過期的資料，比如某個分割槽的資料已經完全沒有意義了，請執行alter table emp_date drop partition p0刪除分割槽。對動輒成千上萬的資料，比執行delete要高效的多！
2. 經常執行包含分割槽鍵的查詢，MySQL很快能找到對應的分割槽，並且在對應的分割槽掃描。

mysql> insert into emp_date (id,ename,hired,separated,job,store_id) values('7934','miller','1995-01-01','1995-01-01','care',50);
Query OK, 1 row affected (0.01 sec)

mysql> explain partitions select count(1) from emp_date where store_id >=20\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp_date
   partitions: p0,p1,p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

ERROR: 
No query specified

LIST分割槽

特點：一個列舉列表的值的集合。RANGE是連續區間值的集合

mysql> CREATE TABLE expenses ( 
    expense_date date NOT NULL, 
    category INT, amount DECIMAL ( 10, 3 ) 
) 
    PARTITION BY list ( category ) (
    -> PARTITION p0 VALUES IN ( 3, 5 ),
    -> PARTITION p1 VALUES IN ( 1, 11 ),
    -> PARTITION p2 VALUES IN ( 4, 9 ),
    -> PARTITION p3 VALUES IN ( 2 ) 
    -> );
Query OK, 0 rows affected (0.07 sec)

前面有說過，LIST也是僅支援整型，如果你是MySQL5.1，還得單獨建個表。

MYSQL5.5中支援非整型分割槽，真貼心！

mysql> CREATE TABLE expensess ( 
    -> expense_date date NOT NULL, 
    -> category varchar (30), 
    -> amount DECIMAL ( 10, 3 ) 
    -> ) 
    -> PARTITION BY list columns ( category ) (
    -> PARTITION p0 VALUES IN ('loading','food' ),
    -> PARTITION p1 VALUES IN ( 'ear', 'frist' ),
    -> PARTITION p2 VALUES IN ( 'hire','horse' ),
    -> PARTITION p3 VALUES IN ( 'fees' ) 
    -> );
Query OK, 0 rows affected (0.06 sec)

LIST分割槽，整型是list （expr） ，字串是list columns (expr)

HASH分割槽

HASH分割槽主要用來分散熱點讀，確保資料在預先確定個數的分割槽中盡肯能平均分佈。對一個表執行HASH分割槽時，MYQSL會對分割槽鍵應用一個雜湊函式，以此確定資料應當放在N個分割槽中的哪個分割槽

1、HASH分割槽分兩種

1. 常規分割槽(HASH分割槽)—>取模演算法
2. 線性分割槽(LINEAR HASH分割槽)——>一個線性的2的冥的運演算法則

2、常規分割槽

​ 語法：PARTITION BY HASH(expr) PARTITIONS num

​ expt：某列值或者一個基於某列值返回一個整數值的表示式

​ num：非負整數，分幾個區

例項：

CREATE TABLE emp_date (
    id INT NOT NULL,
    ename VARCHAR ( 30 ),
    hired date NOT NULL DEFAULT '1970-01-01',
    separated date NOT NULL DEFAULT '9999-12-31',
    job VARCHAR ( 30 ) NOT NULL,
    store_id INT NOT NULL 
) 
PARTITION BY HASH ( store_id ) partitions 4;

根據expr算分割槽：

N=MOD(expr,num)

Store_id = 234；根據公式取模：N=MOD(234，4) = 2；分佈在第二個分割槽

測試：

insert into emp_date values(1,'care','2010-10-10','9999-12-31','tos',234);

通過執行計劃看看 ：

mysql> EXPLAIN PARTITIONS SELECT * FROM emp_date WHERE store_id = 234\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp_date
   partitions: p2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

ERROR: 
No query specified

MySQL不推薦使用涉及多列的雜湊表示式，expr可以是非隨機非常數，每次增刪改都需要計算一次，存在效能問題！

優點：資料平均的分佈在每個分割槽、提高了效率

缺點：增加或合併分割槽，原來平均的資料需要重新通過取模再分配，不適合需要靈活變動分割槽的需求

3、線性HASH分割槽

例項：

CREATE TABLE emp_dates (
    id INT NOT NULL,
    ename VARCHAR ( 30 ),
    hired date NOT NULL DEFAULT '1970-01-01',
    separated date NOT NULL DEFAULT '9999-12-31',
    job VARCHAR ( 30 ) NOT NULL,
    store_id INT NOT NULL 
) PARTITION BY LINEAR HASH ( store_id ) 
partitions 4;

記錄將要儲存到的分割槽是num 個分割槽中的分割槽N，其中N是根據下面的演算法得到： 找到下一個大於num.的、2的冪，我們把這個值稱為V

V = POWER(2, CEILING(LOG(2, num)))

N = F(column_list) & (V – 1)

當 N >= num: · 設定 V = CEIL(V / 2) · 設定 N = N & (V – 1)

是不是跟我一樣懵逼中？例項走一波！

設定4個分割槽，expr=234
V = 4；
N = 234 & (4-1);
N = 2;
因為N<= 4;會被分配到第二個分割槽

線性HASH分割槽優缺點

優點：在分割槽維護(包含增加、刪除、合併、拆分分割槽)時，MySQL能夠處理的更迅速

缺點：相比線性分割槽，各個分割槽之間資料的分佈不太均衡

KEY分割槽

與HASH分割槽型別。不同點：

1. HASH分割槽允許使用使用者自定義的表示式，KEY分割槽不允許使用使用者自定義的表示式，需要使用HASH函式
2. HASH分割槽只支援整數分割槽，KEY分割槽支援使用除BLOB 、TEXT型別外的其他型別作為分割槽鍵
3. 建立KEY分割槽可以不指定分割槽鍵，預設使用主鍵

key分割槽的語法：partition by keys(expr)；expr是零個或者多個欄位名名的列表

mysql> CREATE TABLE `emp1` (
    -> id int not null,
    -> ename varchar(30),
    ->  hired date not null DEFAULT '1970-01-01',
    ->   separated date  not null DEFAULT '9999-12-31',
    ->   job varchar(30) not null,
    -> store_id int not null
    -> )
    -> 
    -> PARTITION BY key  ( job ) partitions 4;
Query OK, 0 rows affected (0.04 sec)

試試看不指定分割槽鍵，前提得有主鍵！

mysql> CREATE TABLE `emp2` (
    -> id int not null,
    -> ename varchar(30),
    ->  hired date not null DEFAULT '1970-01-01',
    ->   separated date  not null DEFAULT '9999-12-31',
    ->   job varchar(30) not null,
    -> store_id int not null,
    -> primary key (id)
    -> )
    -> 
    -> PARTITION BY key  ( ) partitions 4;
Query OK, 0 rows affected (0.05 sec)

退一步，沒有主鍵也可以，但是必須要有唯一鍵，unique key，同時唯一鍵必須為非空，你搞個空鬼才知道你要存哪個分割槽！

既沒有主鍵又沒有唯一鍵，報錯！

在按照key分割槽的分割槽表上，不能執行alter table drop primary key

與HASH分割槽類似，可以使用關鍵字LINEAR KEY分割槽時，分割槽的編號是通過2的冥演算法得到而不是取模。在處理大量資料時，能夠有效的分散熱點！

子分割槽

子分割槽是分割槽表中對每個分割槽的再次分割。也稱為複合分割槽。

MySQL5.1開始支援對已經通過RANGE或者LIST分割槽了的表再進行子分割槽。子分割槽既可以使用HASH分割槽，也可以使用KEY分割槽。

mysql> CREATE TABLE `ts` (
    -> id int,
    ->   purchased date 
    -> )
    -> 
    -> PARTITION by range (year(purchased))
    -> SUBPARTITION by hash (TO_DAYS(purchased))
    -> SUBPARTITIONS 2
    -> (
    ->  PARTITION p0 VALUES LESS THAN (1900 ),
    ->  PARTITION p1 VALUES LESS THAN (2000 ),
    ->   PARTITION p2 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.06 sec)

原先有三個分割槽，p0、p1、p2

每個分割槽又再被分為兩個子分割槽，一共6個分割槽

子分割槽適合資料量非常大量的資料記錄

MySQL分割槽處理null值的方式

MySQL不禁止在分割槽鍵上使用null值。

具體分割槽型別的null值

1. RANGE分割槽，null會被當成最小值
2. LIST分割槽，null值必須出現在列舉列表中，否則不會被接受(報錯)
3. HASH/KEY分割槽，null值當成0；

分割槽管理

MySQL5.1提供了新增、刪除、重定義、合併、拆分分割槽的命令。都可以通過ALTER TABLE 來實現。

1、RANGE&LIST分割槽管理

在新增、刪除、重新定義分割槽的處理上，RANGE和LIST分割槽很相似。

1-1、刪除分割槽

刪除分割槽：ALTER TABLE DROP PARTITION

刪除分割槽同時也會刪除該分割槽對應的資料

//新建分割槽
create table emp_date(
    id int not null,
    ename varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job varchar(30) not null,
    store_id int not null
) 
    partition by range (YEAR(separated)) ( 
        partition p0 values less than (1995), 
        partition p1 values less than (2000), 
        partition p2 values less than (2005),
                partition p3 values less than (2015),
                partition p4 values less than (2020)
    );

//插入資料
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (1, 'care', '1970-01-01', '1991-12-31', 'a', 1);
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (2, 'tony', '1970-01-01', '1996-12-31', 'b', 2);
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (3, 'pony', '1970-01-01', '2001-12-31', 'c', 3);
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (4, 'foly', '1970-01-01', '2006-12-31', 'd', 4);
INSERT INTO `sakila`.`emp_date`(`id`, `ename`, `hired`, `separated`, `job`, `store_id`) VALUES (5, 'quly', '1970-01-01', '2016-12-31', 'e', 5);

//刪除p2分割槽
ALTER TABLE emp_date DROP PARTITION p2;
//檢視建表語句
mysql> SHOW CREATE TABLE emp_date\G;
*************************** 1. row ***************************
       Table: emp_date
Create Table: CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (year(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2020) ENGINE = InnoDB) */
1 row in set (0.00 sec)

ERROR: 
No query specified

查詢表的分割槽對應的情況

SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
information_schema.PARTITIONS
WHERE
table_schema = SCHEMA()
AND table_name = 'emp_date';

+------+-----------------+-------+------------+
| part | expr            | descr | table_rows |
+------+-----------------+-------+------------+
| p0   | year(separated) | 1995  |          1 |
| p1   | year(separated) | 2000  |          1 |
| p3   | year(separated) | 2015  |          1 |
| p4   | year(separated) | 2020  |          1 |
+------+-----------------+-------+------------+

LIST分割槽因為是列舉型，刪除分割槽之後如果不新建對應分割槽，直接插入原先被刪除的資料，報錯！

1-2、增加分割槽

增加：`ALTER TABLE ADD PARTITION

剛才不是刪了一個p2分割槽嗎？好！現在加上去

ALTER TABLE emp_date ADD PARTITION (PARTITION p2 VALUES less than (2005));
或者
ALTER TABLE emp_date ADD PARTITION (PARTITION p5 VALUES less than (2005));
//報錯
1493 - VALUES LESS THAN value must be strictly increasing for each partition, Time: 0.001000s
只能從最末端新增
ALTER TABLE emp_date ADD PARTITION (PARTITION p2 VALUES less than (2025));
這樣才是正確的！

==問題==

如果是想恢復此區間的分割槽，怎麼弄呢？

比如刪除：

ALTER TABLE emp_date DROP PARTITION p2;

此分割槽對應的範圍是：less than (2005)

現在要恢復這個範圍的分割槽怎麼辦？

例項：LIST增加分割槽

CREATE TABLE expenses ( 
    expense_date date NOT NULL, 
    category INT, amount DECIMAL ( 10, 3 ) 
) 
    PARTITION BY list ( category ) (
        PARTITION p0 VALUES IN ( 3, 5 ),
    PARTITION p1 VALUES IN ( 1, 11),
    PARTITION p2 VALUES IN ( 4, 9),
    PARTITION p3 VALUES IN ( 2 ) 
);

增加分割槽
ALTER TABLE expenses ADD PARTITION (PARTITION p4 values in (6,7,8));

LIST必須要注意的問題是列舉值必須唯一
ALTER TABLE expenses ADD PARTITION (PARTITION p5 values in (8));
(報錯)
1495 - Multiple definition of same constant in list partitioning, Time: 0.000000s

1-3、重定義分割槽

剛開始定義分割槽發現分的不好，比如RANGE分割槽p4的範圍(2000~2015)，後來這個區的資料太大了，需要重新分割槽，分成p3(2000~2010)，p4(2010~2020)，前提之前沒有p2，p3分割槽！

RANGE拆分分割槽

//原有分割槽
create table emp_date(
    id int not null,
    ename varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job varchar(30) not null,
    store_id int not null
) 
    partition by range (YEAR(separated)) ( 
        partition p0 values less than (1995), 
        partition p1 values less than (2000), 
                partition p4 values less than (2020)
    );

//過一段時間發現臥槽，都集中在p4分割槽了，那怎麼行，趕緊重定義分割槽

ALTER TABLE emp_date REORGANIZE PARTITION p4 INTO(
PARTITION p3 VALUES less than (2010),
PARTITION p4 VALUES less than (2020)
)

RANGE合併分割槽

//合併之前的分割槽情況
CREATE TABLE `emp_date` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (YEAR(separated))
(PARTITION p0 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2020) ENGINE = InnoDB) */;

 //操作合併，
 p0\p1\p3合併成一個區p1

 ALTER TABLE emp_date REORGANIZE PARTITION p0,p1,p3 INTO(
PARTITION p1 VALUES less than (2010)
)

LIST拆分分割槽

CREATE TABLE expenses ( 
    expense_date date NOT NULL, 
    category INT, amount DECIMAL ( 10, 3 ) 
) 
    PARTITION BY list ( category ) (
    PARTITION p0 VALUES IN ( 3, 5 ),
    PARTITION p1 VALUES IN ( 1, 11 ),
    PARTITION p2 VALUES IN ( 4, 9 ),
    PARTITION p3 VALUES IN ( 2 ),
        PARTITION p4 VALUES IN ( 6),
        PARTITION p5 VALUES IN ( 7,8 )
    );

目標：將P4分割槽包含值(6，12)

方案一：和之前一樣新增分割槽
 ALTER TABLE expenses ADD PARTITION (PARTITION p6 VALUES IN ( 6,12 ));
 //報錯
 1495 - Multiple definition of same constant in list partitioning, Time: 0.000000s

 方案二：先增加分割槽，後重定義分割槽
 ALTER TABLE expenses ADD PARTITION (PARTITION p6 VALUES IN ( 12));

//p4,p5,p6重定義到 p4 (6,12) p5(7,8)
 ALTER TABLE expenses REORGANIZE PARTITION p4,p5,p6 INTO(
PARTITION p4 VALUES IN (6,12),
PARTITION p5 VALUES IN (7,8)
)

總結：

重定義分割槽的時候：

1. 不能通過重定義分割槽改表原有分割槽型別

2. RANGE和LIST只能重新定義相鄰的分割槽、重新定義的區間必須和原分割槽區間覆蓋相同的區間

2、HASH&KEY分割槽管理

HASH&KEY分割槽管理類似，以HASH舉例

CREATE TABLE emp(
    id INT NOT NULL,
    ename VARCHAR ( 30 ),
    hired date NOT NULL DEFAULT '1970-01-01',
    separated date NOT NULL DEFAULT '9999-12-31',
    job VARCHAR ( 30 ) NOT NULL,
    store_id INT NOT NULL 
) 
PARTITION BY HASH ( store_id ) partitions 4;

//減少分割槽
ALTER TABLE emp COALESCE PARTITION 2;

//增加分割槽
coalesce不能用來增加分割槽的數量
ALTER TABLE emp COALESCE PARTITION 8;
報錯
1508 - Cannot remove all partitions, use DROP TABLE instead, Time: 0.003000s

//注意是增加8個分割槽
ALTER TABLE emp add PARTITION PARTITIONS 8;

mysql> SHOW CREATE TABLE emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `ename` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job` varchar(30) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH ( store_id)
PARTITIONS 10 */
1 row in set (0.00 sec)

ERROR: 
No query specified

本作品採用《CC 協議》，轉載必須註明作者和本文連結