松哥之前寫過文章跟大家介紹過用 MyCat 實(shí)現(xiàn) MySQL 的分庫分表，不知道有沒有小伙伴研究過，MySQL 其實(shí)也自帶了分區(qū)功能，我們可以創(chuàng)建一個(gè)帶有分區(qū)的表，而且不需要借助任何外部工具，今天我們就一起來看看。

1. 什么是表分區(qū)

小伙伴們知道，MySQL 數(shù)據(jù)庫中的數(shù)據(jù)是以文件的形勢存在磁盤上的，默認(rèn)放在 /var/lib/mysql/ 目錄下面，我們可以通過 show variables like '%datadir%'; 命令來查看：

我們進(jìn)入到這個(gè)目錄下，就可以看到我們定義的所有數(shù)據(jù)庫了，一個(gè)數(shù)據(jù)庫就是一個(gè)文件夾，一個(gè)庫中，有其對應(yīng)的表的信息，如下：

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

• *.frm：這個(gè)是表定義，是描述表結(jié)構(gòu)的文件。
• *.myd：這個(gè)是數(shù)據(jù)信息文件，是表的數(shù)據(jù)文件。
• *.myi：這個(gè)是索引信息文件。

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

• *.frm：表結(jié)構(gòu)文件。
• *.ibd：表數(shù)據(jù)和索引的文件。

無論是哪種存儲引擎，只要一張表的數(shù)據(jù)量過大，就會導(dǎo)致 *.myd、*.myi 以及 *.ibd 文件過大，數(shù)據(jù)的查找就會變的很慢。

為了解決這個(gè)問題，我們可以利用 MySQL 的分區(qū)功能，在物理上將這一張表對應(yīng)的文件，分割成許多小塊，如此，當(dāng)我們查找一條數(shù)據(jù)時(shí)，就不用在某一個(gè)文件中進(jìn)行整個(gè)遍歷了，我們只需要知道這條數(shù)據(jù)位于哪一個(gè)數(shù)據(jù)塊，然后在那一個(gè)數(shù)據(jù)塊上查找就行了；另一方面，如果一張表的數(shù)據(jù)量太大，可能一個(gè)磁盤放不下，這個(gè)時(shí)候，通過表分區(qū)我們就可以把數(shù)據(jù)分配到不同的磁盤里面去。

MySQL 從 5.1 開始添加了對分區(qū)的支持，分區(qū)的過程是將一個(gè)表或索引分解為多個(gè)更小、更可管理的部分。對于開發(fā)者而言，分區(qū)后的表使用方式和不分區(qū)基本上還是一模一樣，只不過在物理存儲上，原本該表只有一個(gè)數(shù)據(jù)文件，現(xiàn)在變成了多個(gè)，每個(gè)分區(qū)都是獨(dú)立的對象，可以獨(dú)自處理，也可以作為一個(gè)更大對象的一部分進(jìn)行處理。

需要注意的是，分區(qū)功能并不是在存儲引擎層完成的，常見的存儲引擎如 InnoDB、MyISAM、NDB 等都支持分區(qū)。但并不是所有的存儲引擎都支持，如 CSV、FEDORATED、MERGE 等就不支持分區(qū)，因此在使用此分區(qū)功能前，應(yīng)該對選擇的存儲引擎對分區(qū)的支持有所了解。

2. 分區(qū)的兩種方式

不同于 MyCat 中既可以垂直切分又可以水平切分，MySQL 數(shù)據(jù)庫支持的分區(qū)類型為水平分區(qū)，它不支持垂直分區(qū)。

2.1 水平切分

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

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

砍完之后，將砍掉的部分放到另外一個(gè)數(shù)據(jù)庫實(shí)例中，變成下面這樣：

這樣，原本放在一個(gè) DB 中的 table 現(xiàn)在放在兩個(gè) DB 中了，觀察之后我們發(fā)現(xiàn)：

兩個(gè) DB 中表的個(gè)數(shù)都是完整的，就是原來 DB 中有幾張表，現(xiàn)在還是幾張。每張表中的數(shù)據(jù)是不完整的，數(shù)據(jù)被拆分到了不同的 DB 中去了。

這就是數(shù)據(jù)庫的水平切分，也可以理解為按照數(shù)據(jù)行進(jìn)行切分，即按照表中某個(gè)字段的某種規(guī)則來將表數(shù)據(jù)分散到多個(gè)庫之中，每個(gè)表中包含一部分?jǐn)?shù)據(jù)，即水平切分不改變表結(jié)構(gòu)。

2.2 垂直切分

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

所謂的垂直切分就是拿著我 40 米大刀，對準(zhǔn)了黑色的線條砍?？惩曛螅瑢⒉煌谋矸诺讲煌臄?shù)據(jù)庫實(shí)例中去，變成下面這個(gè)樣子：

這個(gè)時(shí)候我們發(fā)現(xiàn)如下幾個(gè)特點(diǎn)：

每一個(gè)數(shù)據(jù)庫實(shí)例中的表的數(shù)量都是不完整的。每一個(gè)數(shù)據(jù)庫實(shí)例中表的數(shù)據(jù)是完整的。

這就是垂直切分。一般來說，垂直切分我們可以按照業(yè)務(wù)來劃分，不同業(yè)務(wù)的表放到不同的數(shù)據(jù)庫實(shí)例中。

MySQL 數(shù)據(jù)庫支持的分區(qū)類型為水平分區(qū)。

此外，MySQL 數(shù)據(jù)庫的分區(qū)是局部分區(qū)索引，即一個(gè)分區(qū)中既存放了數(shù)據(jù)又存放了索引，目前，MySQL數(shù)據(jù)庫還不支持全局分區(qū)（數(shù)據(jù)存放在各個(gè)分區(qū)中，但是所有數(shù)據(jù)的索引放在一個(gè)對象中）。

3. 為什么需要表分區(qū)

• 可以讓單表存儲更多的數(shù)據(jù)。
• 分區(qū)表的數(shù)據(jù)更容易維護(hù)，可以通過清除整個(gè)分區(qū)批量刪除大量數(shù)據(jù)，也可以增加新的分區(qū)來支持新插入的數(shù)據(jù)。另外，還可以對一個(gè)獨(dú)立分區(qū)進(jìn)行優(yōu)化、檢查、修復(fù)等操作。
• 部分查詢能夠從查詢條件確定只落在少數(shù)分區(qū)上，查詢速度會很快。
• 分區(qū)表的數(shù)據(jù)還可以分布在不同的物理設(shè)備上，從而高效利用多個(gè)硬件設(shè)備。
• 可以使用分區(qū)表來避免某些特殊瓶頸，例如 InnoDB 單個(gè)索引的互斥訪問、ext3 文件系統(tǒng)的 inode 鎖競爭。
• 可以備份和恢復(fù)單個(gè)分區(qū)。

分區(qū)的限制和缺點(diǎn)：

• 一個(gè)表最多只能有 1024 個(gè)分區(qū)。
• 如果分區(qū)字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進(jìn)來。
• 分區(qū)表無法使用外鍵約束。
• NULL 值會使分區(qū)過濾無效。
• 所有分區(qū)必須使用相同的存儲引擎。

4. 分區(qū)實(shí)踐

說了這么多，來個(gè)例子看一下。

首先我們先來查看一下當(dāng)前的 MySQL 是否支持分區(qū)。

在 MySQL5.6.1 之前可以通過命令 show variables like '%have_partitioning%' 來查看 MySQL 是否支持分區(qū)。如果 have_partitioning 的值為 YES，則表示支持分區(qū)。

從 MySQL5.6.1 開始，have_partitioning 參數(shù)已經(jīng)被去掉了，而是用 SHOW PLUGINS 來代替。若有 partition 行且 STATUS 列的值為 ACTIVE，則表示支持分區(qū)，如下所示：

確認(rèn)我們的 MySQL 支持分區(qū)后，我們就可以開始分區(qū)啦！

接下來我們來看幾種不同的分區(qū)策略。

4.1 RANGE 分區(qū)

RANGE 分區(qū)比較簡單，就是根據(jù)某一個(gè)字段的值進(jìn)行分區(qū)。不過這個(gè)字段有一個(gè)要求，就是必須是主鍵或者是聯(lián)合主鍵中的某個(gè)字段。

例如根據(jù) user 表的 id 進(jìn)行分區(qū)：

• 當(dāng) id 小于 100，數(shù)據(jù)插入 p0 分區(qū)；
• 當(dāng) id 大于等于 100 小于 200 的時(shí)候，插入 p1 分區(qū)；
• 如果 id 大于等于 200 則插入 p2 分區(qū)。

上面的規(guī)則涉及到了 id 的所有范圍了，如果沒有第三條規(guī)則，那么插入一個(gè) id 為 300 的記錄時(shí)，就會報(bào)錯(cuò)。

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

表創(chuàng)建成功后，我們進(jìn)入到 /var/lib/mysql/test08 文件夾中，來看剛剛創(chuàng)建的表文件：

可以看到，此時(shí)的數(shù)據(jù)文件分為好幾個(gè)了。

在 information_schema.partitions 表中，我們可以查看分區(qū)的詳細(xì)信息：

也可以自己寫個(gè) SQL 去查詢：

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

每一行展示一個(gè)分區(qū)的信息，包括分區(qū)的方式、該區(qū)的范圍、分區(qū)的字段、該區(qū)目前有幾條記錄等等。

RANGE 分區(qū)有一個(gè)比較典型的使用場景，就是按照日期對表進(jìn)行分區(qū)，例如同一年注冊的用戶放在一個(gè)分區(qū)中，如下：

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 是聯(lián)合主鍵的一員。**如果 createDate 不是主鍵，只是一個(gè)普通字段，那么創(chuàng)建時(shí)就會拋出如下錯(cuò)誤：

現(xiàn)在，如果我們要查詢 2022 年注冊的用戶，系統(tǒng)就只會去搜索 p2022 這個(gè)分區(qū)，通過 explain 執(zhí)行計(jì)劃可以證實(shí)我們的想法：

如果想要?jiǎng)h除 2022 年注冊的用戶，則只需要?jiǎng)h除該分區(qū)即可：

alter table user drop partition p2022;

由上圖可以看到，刪除之后，數(shù)據(jù)就沒了。

4.2 LIST 分區(qū)

LIST 分區(qū)和 RANGE 分區(qū)類似，區(qū)別在于 LIST 分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來進(jìn)行選擇，而非連續(xù)的。舉個(gè)例子大家看下就明白了：

假設(shè)我有一個(gè)用戶表，用戶有性別，現(xiàn)在想按照性別將用戶分開存儲，男性存儲在一個(gè)分區(qū)中，女性存儲在一個(gè)分區(qū)中，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));

這個(gè)表將來就兩個(gè)分區(qū)，分別存儲男性和女性，gender 的取值為 1 或者 0，gender 如果取其他值，執(zhí)行就會出錯(cuò)，最終執(zhí)行結(jié)果如下：

這樣分區(qū)之后，將來查詢男性或者查詢女性效率都會比較高，刪除某一性別的用戶時(shí)刪除效率也高。

4.3 HASH 分區(qū)

HASH 分區(qū)的目的是將數(shù)據(jù)均勻地分布到預(yù)先定義的各個(gè)分區(qū)中，保證各分區(qū)的數(shù)據(jù)量大致都是一樣的。在 RANGE 和 LIST 分區(qū)中，必須明確指定一個(gè)給定的列值或列值集合應(yīng)該保存在哪個(gè)分區(qū)中；而在 HASH 分區(qū)中，MySQL 自動完成這些工作，用戶所要做的只是基于將要進(jìn)行哈希分區(qū)的列指定一個(gè)表達(dá)式，并且分區(qū)的數(shù)量。

使用 HASH 分區(qū)來分割一個(gè)表，要在 CREATE TABLE 語句上添加 PARTITION BY HASH (expr)，其中 expr 是一個(gè)字段或者是一個(gè)返回整數(shù)的表達(dá)式；另外通過 PARTITIONS 屬性指定分區(qū)的數(shù)量，如果沒有指定，那么分區(qū)的數(shù)量默認(rèn)為 1，另外，HASH 分區(qū)不能刪除分區(qū)，所以不能使用 DROP PARTITION 操作進(jìn)行分區(qū)刪除操作。

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 分區(qū)

KEY 分區(qū)和 HASH 分區(qū)相似，但是 KEY 分區(qū)支持除 text 和 BLOB 之外的所有數(shù)據(jù)類型的分區(qū)，而 HASH 分區(qū)只支持?jǐn)?shù)字分區(qū)。

KEY 分區(qū)不允許使用用戶自定義的表達(dá)式進(jìn)行分區(qū)，KEY 分區(qū)使用系統(tǒng)提供的 HASH 函數(shù)進(jìn)行分區(qū)。

當(dāng)表中存在主鍵或者唯一索引時(shí)，如果創(chuàng)建 KEY 分區(qū)時(shí)沒有指定字段系統(tǒng)默認(rèn)會首選主鍵列作為分區(qū)字段,如果不存在主鍵列會選擇非空唯一索引列作為分區(qū)字段。

舉個(gè)例子：

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 分區(qū)

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

COLUMNS Vs RANGE Vs LIST 分區(qū)：

• 針對日期字段的分區(qū)不需要再使用函數(shù)進(jìn)行轉(zhuǎn)換了。
• COLUMN 分區(qū)支持多個(gè)字段作為分區(qū)鍵但是不支持表達(dá)式作為分區(qū)鍵。

COLUMNS 支持的類型

• 整形支持：tinyint、smallint、mediumint、int、bigint；不支持 decimal 和 float。
• 時(shí)間類型支持：date、datetime。
• 字符類型支持：char、varchar、binary、varbinary；不支持text、blob。

舉個(gè)例子看下：

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，分區(qū)值是連續(xù)的。

再來看 LIST COLUMNS 分區(qū)，這個(gè)就類似于枚舉了：

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. 常見分區(qū)命令

添加分區(qū)：

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

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

刪除表分區(qū)（會刪除數(shù)據(jù)）：

alter table user drop partition p30;

刪除表的所有分區(qū)（不會丟失數(shù)據(jù)）：

alter table user remove partitioning; 

重新定義 range 分區(qū)表（不會丟失數(shù)據(jù)）：

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

重新定義 hash 分區(qū)表（不會丟失數(shù)據(jù)）：

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

合并分區(qū)：把 2 個(gè)分區(qū)合并為一個(gè)，不會丟失數(shù)據(jù)：

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

6. 小結(jié)

不知道小伙伴們是否還記得松哥 2019 年寫的 MyCat 教程（公眾號江南一點(diǎn)雨后臺回復(fù) 2019 有文章索引），這些分區(qū)策略是不是和 MyCat 中的策略非常相似呀？感興趣的小伙伴趕緊去試一把吧～

到此這篇關(guān)于一文讀懂MySQL 表分區(qū)的文章就介紹到這了,更多相關(guān)MySQL 表分區(qū)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論