首頁 > 軟體

一文讀懂MySQL 表分割區

2022-03-28 16:00:16

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

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

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

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!


IT145.com E-mail:sddin#qq.com