一文讀懂MySQL 表分割區

2022-03-28 16:00:16

1. 什麼是表分割區

小夥伴們知道，MySQL 資料庫中的資料是以檔案的形勢存在磁碟上的，預設放在 /var/lib/mysql/ 目錄下面，我們可以通過 show variables like '%datadir%'; 命令來檢視：

我們進入到這個目錄下，就可以看到我們定義的所有資料庫了，一個資料庫就是一個資料夾，一個庫中，有其對應的表的資訊，如下：

在 MySQL 中，如果儲存引擎是 MyISAM，那麼在 data 目錄下會看到 3 類檔案：.frm、.myi、.myd，作用如下：

*.frm：這個是表定義，是描述表結構的檔案。
*.myd：這個是資料資訊檔案，是表的資料檔案。
*.myi：這個是索引資訊檔案。

如果儲存引擎是 InnoDB, 那麼在 data 目錄下會看到兩類檔案：.frm、.ibd，作用分別如下：

*.frm：表結構檔案。
*.ibd：表資料和索引的檔案。

無論是哪種儲存引擎，只要一張表的資料量過大，就會導致 *.myd、*.myi 以及 *.ibd 檔案過大，資料的查詢就會變的很慢。

為了解決這個問題，我們可以利用 MySQL 的分割區功能，在物理上將這一張表對應的檔案，分割成許多小塊，如此，當我們查詢一條資料時，就不用在某一個檔案中進行整個遍歷了，我們只需要知道這條資料位於哪一個資料塊，然後在那一個資料塊上查詢就行了；另一方面，如果一張表的資料量太大，可能一個磁碟放不下，這個時候，通過表分割區我們就可以把資料分配到不同的磁碟裡面去。

MySQL 從 5.1 開始新增了對分割區的支援，分割區的過程是將一個表或索引分解為多個更小、更可管理的部分。對於開發者而言，分割區後的表使用方式和不分割區基本上還是一模一樣，只不過在物理儲存上，原本該表只有一個資料檔案，現在變成了多個，每個分割區都是獨立的物件，可以獨自處理，也可以作為一個更大物件的一部分進行處理。

需要注意的是，分割區功能並不是在儲存引擎層完成的，常見的儲存引擎如 InnoDB、MyISAM、NDB 等都支援分割區。但並不是所有的儲存引擎都支援，如 CSV、FEDORATED、MERGE 等就不支援分割區，因此在使用此分割區功能前，應該對選擇的儲存引擎對分割區的支援有所瞭解。

2. 分割區的兩種方式

不同於 MyCat 中既可以垂直切分又可以水平切分，MySQL 資料庫支援的分割區型別為水平分割區，它不支援垂直分割區。

2.1 水平切分

先來一張簡單的示意圖，大家感受一下什麼是水平切分：

假設我的 DB 中有 table-1、table-2 以及 table-3 三張表，水平切分就是拿著我 40 米大刀，對準黑色的線條，砍一劍或者砍 N 劍！

砍完之後，將砍掉的部分放到另外一個資料庫範例中，變成下面這樣：

這樣，原本放在一個 DB 中的 table 現在放在兩個 DB 中了，觀察之後我們發現：

兩個 DB 中表的個數都是完整的，就是原來 DB 中有幾張表，現在還是幾張。每張表中的資料是不完整的，資料被拆分到了不同的 DB 中去了。

這就是資料庫的水平切分，也可以理解為按照資料行進行切分，即按照表中某個欄位的某種規則來將表資料分散到多個庫之中，每個表中包含一部分資料，即水平切分不改變表結構。

2.2 垂直切分

先來一張簡單的示意圖，大家感受一下垂直切分：

所謂的垂直切分就是拿著我 40 米大刀，對準了黑色的線條砍。砍完之後，將不同的表放到不同的資料庫範例中去，變成下面這個樣子：

這個時候我們發現如下幾個特點：

每一個資料庫範例中的表的數量都是不完整的。每一個資料庫範例中表的資料是完整的。

這就是垂直切分。一般來說，垂直切分我們可以按照業務來劃分，不同業務的表放到不同的資料庫範例中。

MySQL 資料庫支援的分割區型別為水平分割區。

此外，MySQL 資料庫的分割區是區域性分割區索引，即一個分割區中既存放了資料又存放了索引，目前，MySQL資料庫還不支援全域性分割區（資料存放在各個分割區中，但是所有資料的索引放在一個物件中）。

3. 為什麼需要表分割區

可以讓單表儲存更多的資料。
分割區表的資料更容易維護，可以通過清除整個分割區批次刪除大量資料，也可以增加新的分割區來支援新插入的資料。另外，還可以對一個獨立分割區進行優化、檢查、修復等操作。
部分查詢能夠從查詢條件確定只落在少數分割區上，查詢速度會很快。
分割區表的資料還可以分佈在不同的物理裝置上，從而高效利用多個硬體裝置。
可以使用分割區表來避免某些特殊瓶頸，例如 InnoDB 單個索引的互斥存取、ext3 檔案系統的 inode 鎖競爭。
可以備份和恢復單個分割區。

分割區的限制和缺點：

一個表最多隻能有 1024 個分割區。
如果分割區欄位中有主鍵或者唯一索引的列，那麼所有主鍵列和唯一索引列都必須包含進來。
分割區表無法使用外來鍵約束。
NULL 值會使分割區過濾無效。
所有分割區必須使用相同的儲存引擎。

4. 分割區實踐

說了這麼多，來個例子看一下。

首先我們先來檢視一下當前的 MySQL 是否支援分割區。

在 MySQL5.6.1 之前可以通過命令 show variables like '%have_partitioning%' 來檢視 MySQL 是否支援分割區。如果 have_partitioning 的值為 YES，則表示支援分割區。

從 MySQL5.6.1 開始，have_partitioning 引數已經被去掉了，而是用 SHOW PLUGINS 來代替。若有 partition 行且 STATUS 列的值為 ACTIVE，則表示支援分割區，如下所示：

確認我們的 MySQL 支援分割區後，我們就可以開始分割區啦！

接下來我們來看幾種不同的分割區策略。

4.1 RANGE 分割區

RANGE 分割區比較簡單，就是根據某一個欄位的值進行分割區。不過這個欄位有一個要求，就是必須是主鍵或者是聯合主鍵中的某個欄位。

例如根據 user 表的 id 進行分割區：

當 id 小於 100，資料插入 p0 分割區；
當 id 大於等於 100 小於 200 的時候，插入 p1 分割區；
如果 id 大於等於 200 則插入 p2 分割區。

上面的規則涉及到了 id 的所有範圍了，如果沒有第三條規則，那麼插入一個 id 為 300 的記錄時，就會報錯。

建表 SQL 如下：

create  table  user(
  id int primary key,
  username varchar(255)
)engine=innodb
  partition by range(id)(
     partition  p0  values  less  than(100),
     partition  p1  values  less  than(200),
     partition  p2  values  less  than maxvalue  
);

表建立成功後，我們進入到 /var/lib/mysql/test08 資料夾中，來看剛剛建立的表檔案：

可以看到，此時的資料檔案分為好幾個了。

在 information_schema.partitions 表中，我們可以檢視分割區的詳細資訊：

也可以自己寫個 SQL 去查詢：

select * from information_schema.partitions where table_schema='test08' and table_name='user'G

每一行展示一個分割區的資訊，包括分割區的方式、該區的範圍、分割區的欄位、該區目前有幾條記錄等等。

RANGE 分割區有一個比較典型的使用場景，就是按照日期對錶進行分割區，例如同一年註冊的使用者放在一個分割區中，如下：

create  table  user(
  id int,
  username varchar(255),
  password varchar(255),
  createDate date,
  primary key (id,createDate)
)engine=innodb
  partition by range(year(createDate))(
     partition  p2022  values  less  than(2023),
     partition  p2023  values  less  than(2024),
     partition  p2024  values  less  than(2025)  
);

**注意，createDate 是聯合主鍵的一員。**如果 createDate 不是主鍵，只是一個普通欄位，那麼建立時就會丟擲如下錯誤：

現在，如果我們要查詢 2022 年註冊的使用者，系統就只會去搜尋 p2022 這個分割區，通過 explain 執行計劃可以證實我們的想法：

如果想要刪除 2022 年註冊的使用者，則只需要刪除該分割區即可：

alter table user drop partition p2022;

由上圖可以看到，刪除之後，資料就沒了。

4.2 LIST 分割區

LIST 分割區和 RANGE 分割區類似，區別在於 LIST 分割區是基於列值匹配一個離散值集合中的某個值來進行選擇，而非連續的。舉個例子大家看下就明白了：

假設我有一個使用者表，使用者有性別，現在想按照性別將使用者分開儲存，男性儲存在一個分割區中，女性儲存在一個分割區中，SQL 如下：

create  table  user(
  id int,
  username varchar(255),
  password varchar(255),
  gender int,
  primary key(id, gender)
)engine=innodb
  partition by list(gender)(
     partition  man  values  in  (1),
     partition  woman  values  in  (0));

這個表將來就兩個分割區，分別儲存男性和女性，gender 的取值為 1 或者 0，gender 如果取其他值，執行就會出錯，最終執行結果如下：

這樣分割區之後，將來查詢男性或者查詢女性效率都會比較高，刪除某一性別的使用者時刪除效率也高。

4.3 HASH 分割區

HASH 分割區的目的是將資料均勻地分佈到預先定義的各個分割區中，保證各分割區的資料量大致都是一樣的。在 RANGE 和 LIST 分割區中，必須明確指定一個給定的列值或列值集合應該儲存在哪個分割區中；而在 HASH 分割區中，MySQL 自動完成這些工作，使用者所要做的只是基於將要進行雜湊分割區的列指定一個表示式，並且分割區的數量。

使用 HASH 分割區來分割一個表，要在 CREATE TABLE 語句上新增 PARTITION BY HASH (expr)，其中 expr 是一個欄位或者是一個返回整數的表示式；另外通過 PARTITIONS 屬性指定分割區的數量，如果沒有指定，那麼分割區的數量預設為 1，另外，HASH 分割區不能刪除分割區，所以不能使用 DROP PARTITION 操作進行分割區刪除操作。

create  table  user(
  id int,
  username varchar(255),
  password varchar(255),
  gender int,
  primary key(id, gender)
)engine=innodb partition by hash(id) partitions 4;

4.4 KEY 分割區

KEY 分割區和 HASH 分割區相似，但是 KEY 分割區支援除 text 和 BLOB 之外的所有資料型別的分割區，而 HASH 分割區只支援數位分割區。

KEY 分割區不允許使用使用者自定義的表示式進行分割區，KEY 分割區使用系統提供的 HASH 函數進行分割區。

當表中存在主鍵或者唯一索引時，如果建立 KEY 分割區時沒有指定欄位系統預設會首選主鍵列作為分割區欄位,如果不存在主鍵列會選擇非空唯一索引列作為分割區欄位。

舉個例子：

create  table  user(
  id int,
  username varchar(255),
  password varchar(255),
  gender int,
  primary key(id, gender)
)engine=innodb partition by key(id) partitions 4;

4.5 COLUMNS 分割區

COLUMN 分割區是 5.5 開始引入的分割區功能，只有 RANGE COLUMN 和 LIST COLUMN 這兩種分割區；支援整形、日期、字串；這種分割區方式和 RANGE、LIST 的分割區方式非常的相似。

COLUMNS Vs RANGE Vs LIST 分割區：

針對日期欄位的分割區不需要再使用函數進行轉換了。
COLUMN 分割區支援多個欄位作為分割區鍵但是不支援表示式作為分割區鍵。

COLUMNS 支援的型別

整形支援：tinyint、smallint、mediumint、int、bigint；不支援 decimal 和 float。
時間型別支援：date、datetime。
字元型別支援：char、varchar、binary、varbinary；不支援text、blob。

舉個例子看下：

create  table  user(
  id int,
  username varchar(255),
  password varchar(255),
  gender int,
  createDate date,
  primary key(id, createDate)
)engine=innodb PARTITION BY RANGE COLUMNS(createDate) (
    PARTITION p0 VALUES LESS THAN ('1990-01-01'),
    PARTITION p1 VALUES LESS THAN ('2000-01-01'),
    PARTITION p2 VALUES LESS THAN ('2010-01-01'),
    PARTITION p3 VALUES LESS THAN ('2020-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

這是 RANGE COLUMNS，分割區值是連續的。

再來看 LIST COLUMNS 分割區，這個就類似於列舉了：

create  table  user(
  id int,
  username varchar(255),
  password varchar(255),
  gender int,
  createDate date,
  primary key(id, createDate)
)engine=innodb PARTITION BY LIST COLUMNS(createDate) (
    PARTITION p0 VALUES IN ('1990-01-01'),
    PARTITION p1 VALUES IN ('2000-01-01'),
    PARTITION p2 VALUES IN ('2010-01-01'),
    PARTITION p3 VALUES IN ('2020-01-01')
);

5. 常見分割區命令

新增分割區：

alter table user add partition (partition p3 values less than (4000)); -- range 分割區

alter table user add partition (partition p3 values in (40));  -- lists分割區

刪除表分割區（會刪除資料）：

alter table user drop partition p30;

刪除表的所有分割區（不會丟失資料）：

alter table user remove partitioning;

重新定義 range 分割區表（不會丟失資料）：

alter table user partition by range(salary)(
partition p1 values less than (2000),
partition p2 values less than (4000));

重新定義 hash 分割區表（不會丟失資料）：

alter table user partition by hash(salary) partitions 7;

合併分割區：把 2 個分割區合併為一個，不會丟失資料：

alter table user  reorganize partition p1,p2 into (partition p1 values less than (1000));

6. 小結

不知道小夥伴們是否還記得鬆哥 2019 年寫的 MyCat 教學（公眾號江南一點雨後臺回覆 2019 有文章索引），這些分割區策略是不是和 MyCat 中的策略非常相似呀？感興趣的小夥伴趕緊去試一把吧～

到此這篇關於一文讀懂MySQL 表分割區的文章就介紹到這了,更多相關MySQL 表分割區內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！