MySQL 表分割槽?漲知識了!

語言: CN / TW / HK

@[toc] 鬆哥之前寫過文章跟大家介紹過用 MyCat 實現 MySQL 的分庫分表,不知道有沒有小夥伴研究過,MySQL 其實也自帶了分割槽功能,我們可以建立一個帶有分割槽的表,而且不需要藉助任何外部工具,今天我們就一起來看看。

1. 什麼是表分割槽

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

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

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

  1. *.frm:這個是表定義,是描述表結構的檔案。
  2. *.myd:這個是資料資訊檔案,是表的資料檔案。
  3. *.myi:這個是索引資訊檔案。

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

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

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

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

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

需要注意的是,分割槽功能並不是在儲存引擎層完成的,常見的儲存引擎如 InnoDBMyISAMNDB 等都支援分割槽。但並不是所有的儲存引擎都支援,如 CSVFEDORATEDMERGE 等就不支援分割槽,因此在使用此分割槽功能前,應該對選擇的儲存引擎對分割槽的支援有所瞭解。

2. 分割槽的兩種方式

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

2.1 水平切分

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

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

砍完之後,將砍掉的部分放到另外一個數據庫例項中,變成下面這樣:

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

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

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

2.2 垂直切分

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

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

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

  1. 每一個數據庫例項中的表的數量都是不完整的。
  2. 每一個數據庫例項中表的資料是完整的。

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

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

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

3. 為什麼需要表分割槽

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

分割槽的限制和缺點:

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

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 進行分割槽:

  1. 當 id 小於 100,資料插入 p0 分割槽;
  2. 當 id 大於等於 100 小於 200 的時候,插入 p1 分割槽;
  3. 如果 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 分割槽:

  1. 針對日期欄位的分割槽不需要再使用函式進行轉換了。
  2. 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. 常見分割槽命令

  1. 新增分割槽:
alter table user add partition (partition p3 values less than (4000)); -- range 分割槽
alter table user add partition (partition p3 values in (40));  -- lists分割槽
  1. 刪除表分割槽(會刪除資料):
alter table user drop partition p30;
  1. 刪除表的所有分割槽(不會丟失資料):
alter table user remove partitioning; 
  1. 重新定義 range 分割槽表(不會丟失資料):
alter table user partition by range(salary)(
partition p1 values less than (2000),
partition p2 values less than (4000)); 
  1. 重新定義 hash 分割槽表(不會丟失資料):
alter table user partition by hash(salary) partitions 7; 
  1. 合併分割槽:把 2 個分割槽合併為一個,不會丟失資料:
alter table user  reorganize partition p1,p2 into (partition p1 values less than (1000));

6. 小結

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

參考資料:

https://www.cnblogs.com/dw3306/p/12620O42.html