實(shí)驗(yàn)環(huán)境

數(shù)據(jù)庫(kù)：MySQL 5.7.24
官方文檔：
https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
參考文章：
https://www.cnblogs.com/zhouguowei/p/9360136.html
https://blog.csdn.net/yongqi_wang/article/details/86576006
https://blog.csdn.net/weixin_40901788/article/details/83215889
參考書(shū)籍：《高性能 MySQL》

說(shuō)明

在某些場(chǎng)景中，需要將表以一定的規(guī)律分割以達(dá)到優(yōu)化效果。通常的做法是分表或者分區(qū)。分表是將一張表以 不同的規(guī)則分割（ 水平分割或垂直分割 ）成多張表。而分區(qū)與分表不同，分區(qū)只支持水平分區(qū)，表分區(qū)之后，從邏輯上來(lái)看仍是一張獨(dú)立的表（ 執(zhí)行 SQL 時(shí)仍將其作為一張表來(lái)查詢 ），而底層則是分為多個(gè)物理子表（ 文件系統(tǒng)中表的每個(gè)分區(qū)都有各自的存儲(chǔ)文件 ）。

注意：

1、從 MySQL 5.7.17 開(kāi)始，通用的分區(qū)處理程序（ generic partitioning handler ）將被棄用，并在 MySQL 8.0 中正式刪除。而后由表的存儲(chǔ)引擎提供自己的分區(qū)處理程序（ native partitioning handler ），當(dāng)前只有 InnoDB 和 NDB 存儲(chǔ)引擎做到這一點(diǎn)。為了兼容 MySQL 8.0，需要將存儲(chǔ)引擎修改為提供分區(qū)處理程序的引擎，如：

ALTER TABLE table_name ENGINE = INNODB;

當(dāng)然，在 MySQL 8.0 之前還是可以使用大部分存儲(chǔ)引擎來(lái)實(shí)現(xiàn)分區(qū)的，除了 MERGE, CSV, 和 FEDERATED 存儲(chǔ)引擎。另外不可以在同一張表中為各個(gè)分區(qū)指定不同的存儲(chǔ)引擎。

2、NDB 存儲(chǔ)引擎只支持通過(guò) KEY 或 LINEAR KEY 進(jìn)行分區(qū)

3、分區(qū)適用于表的所有數(shù)據(jù)和索引;不能只對(duì)數(shù)據(jù)進(jìn)行分區(qū)而不對(duì)索引進(jìn)行分區(qū)，反之亦然，也不能只對(duì)表的一部分進(jìn)行分區(qū)

4、可以在 CREATE TABLE 語(yǔ)句中的分區(qū)子句使用 DATA DIRECTORY 和 INDEX DIRECTORY 來(lái)為數(shù)據(jù)和索引指定特定的目錄。在 Windows 上，MyISAM 表的各個(gè)分區(qū)和子分區(qū)不支持這兩個(gè)選項(xiàng)。對(duì)于InnoDB表的各個(gè)分區(qū)和子分區(qū)，則只支持 DATA DIRECTORY 選項(xiàng)。

5、表的分區(qū)表達(dá)式中使用的所有列必須是表可能具有的每個(gè)惟一鍵的一部分，包括任何主鍵

-- 不能對(duì)以下 SQL 語(yǔ)句創(chuàng)建的表進(jìn)行分組
-- 因?yàn)殒I pk 和 uk 沒(méi)有相同的列，所以在分區(qū)表達(dá)式中沒(méi)有可用的列
CREATE TABLE tnp ( 
    id INT NOT NULL AUTO_INCREMENT,
    ref BIGINT NOT NULL,
    name VARCHAR(255), 
    PRIMARY KEY pk (id), 
    UNIQUE KEY uk (name) 
);

開(kāi)始

檢查是否支持分區(qū)

使用以下語(yǔ)句檢查數(shù)據(jù)庫(kù)是否支持分區(qū)：

SHOW plugins;

執(zhí)行結(jié)果

也可以使用以下語(yǔ)句查詢：

SELECT 
    PLUGIN_NAME as Name,
    PLUGIN_VERSION as Version,
    PLUGIN_STATUS as Status
FROM 
    INFORMATION_SCHEMA.PLUGINS
WHERE
    PLUGIN_TYPE='STORAGE ENGINE';

執(zhí)行結(jié)果

分區(qū)類型

MySQL 5.7 中可用的分區(qū)類型如下：

• RANGE 分區(qū)：這一類型的分區(qū)根據(jù)給定范圍的列值將行分配給分區(qū)
• LIST 分區(qū)：類似于 RANGE 分區(qū)，只是該類型是根據(jù)與一組離散值之一匹配的列來(lái)進(jìn)行選擇的
• HASH 分區(qū)：對(duì)于這種類型的分區(qū)，將根據(jù)用戶定義的表達(dá)式返回的值來(lái)選擇分區(qū)，該表達(dá)式對(duì)要插入到表中的行中的列值進(jìn)行操作。該函數(shù)可以由 MySQL 中任何有效的表達(dá)式組成，表達(dá)式產(chǎn)生一個(gè)非負(fù)整數(shù)值。對(duì)這種類型的擴(kuò)展 LINEAR HASH 也可用。
• KEY 分區(qū)：這個(gè)類型的分區(qū)類似于 HASH 分區(qū)，除了提供一個(gè)或多個(gè)要計(jì)算的列之外，MySQL 還提供了自己的哈希函數(shù)。這些列可以包含整數(shù)之外的值，因?yàn)?MySQL 提供的哈希函數(shù)保證無(wú)論列的數(shù)據(jù)類型是什么，結(jié)果都是一個(gè)整數(shù)值。

務(wù)必要記住，無(wú)論使用的分區(qū)類型是什么，分區(qū)總是在創(chuàng)建時(shí)自動(dòng)從 0 開(kāi)始編號(hào)和排序。當(dāng)新的一行插入到一個(gè)分區(qū)表中時(shí)，將使用這些分區(qū)號(hào)來(lái)標(biāo)識(shí)正確的分區(qū)。比如，表使用了 4 個(gè)分區(qū)，則這些分區(qū)的編號(hào)為 0，1，2 和 3。對(duì)于 RANGE 和 LIST 類型的分區(qū)，有必要確保為每個(gè)分區(qū)號(hào)定義了一個(gè)分區(qū)。對(duì)于 HASH 分區(qū)類型，用戶提供的表達(dá)式計(jì)算的整數(shù)值必須大于 0。對(duì)于 KEY 分區(qū)類型，將由 MySQL 提供的哈希函數(shù)自動(dòng)處理。

RANGE 分區(qū)

以范圍分區(qū)的表的分區(qū)方式是，每個(gè)分區(qū)包含分區(qū)表達(dá)式返回值位于給定范圍內(nèi)的行。給定范圍應(yīng)該是連續(xù)而不重疊的，并且使用 VALUES LESS THAN 運(yùn)算符來(lái)定義。

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 
);

現(xiàn)在有如上一張表，按 RANGE 方式分區(qū)有多種方式，比如根據(jù) 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 MAXVALUE 
);

該 SQL 語(yǔ)句劃分了 4 個(gè)分區(qū)，當(dāng)插入記錄的 store_id 小于 6 時(shí)，該記錄分配到 p0 分區(qū)，當(dāng) store_id 的值小于 11 時(shí)分配到 p1 分區(qū)，其它同理。最后一個(gè)分區(qū)定義使用了 MAXVALUE 以涵蓋 store_id 大于 16 的行。假如沒(méi)有這樣做，當(dāng)插入一條 store_id 大于 16 的記錄時(shí)，MySQL 不知道將記錄插入哪個(gè)分區(qū)，就會(huì)返回一個(gè)錯(cuò)誤。

從數(shù)據(jù)文件可以看出， employees 表的底層已經(jīng)被分為四份，以 # 加分區(qū)編號(hào)對(duì)應(yīng)分區(qū)。

文件

此外還可以根據(jù)日期范圍來(lái)分區(qū)，比如根據(jù) separated 字段的年份來(lái)分區(qū)， YEAR 方法將返回日期的年份：

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 
);