MySQL 表分區(qū)？漲知識了

文章目錄

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

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

2.1 水平切分

2.2 垂直切分

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

4. 分區(qū)實踐

4.1 RANGE 分區(qū)

4.2 LIST 分區(qū)

4.3 HASH 分區(qū)

4.4 KEY 分區(qū)

4.5 COLUMNS 分區(qū)

5. 常見分區(qū)命令

6. 小結(jié)

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

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

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

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

*.frm：這個是表定義，是描述表結(jié)構(gòu)的文件。

*.myd：這個是數(shù)據(jù)信息文件，是表的數(shù)據(jù)文件。

*.myi：這個是索引信息文件。

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

*.frm：表結(jié)構(gòu)文件。

*.ibd：表數(shù)據(jù)和索引的文件。

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

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

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

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

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

兩個 DB 中表的個數(shù)都是完整的，就是原來 DB 中有幾張表，現(xiàn)在還是幾張。

每張表中的數(shù)據(jù)是不完整的，數(shù)據(jù)被拆分到了不同的 DB 中去了。

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

2.2 垂直切分

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

所謂的垂直切分就是拿著我 40 米大刀，對準(zhǔn)了黑色的線條砍。砍完之后，將不同的表放到不同的數(shù)據(jù)庫實例中去，變成下面這個樣子：

這個時候我們發(fā)現(xiàn)如下幾個特點：

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

每一個數(shù)據(jù)庫實例中表的數(shù)據(jù)是完整的。

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

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

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

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

可以讓單表存儲更多的數(shù)據(jù)。

分區(qū)表的數(shù)據(jù)更容易維護(hù)，可以通過清除整個分區(qū)批量刪除大量數(shù)據(jù)，也可以增加新的分區(qū)來支持新插入的數(shù)據(jù)。另外，還可以對一個獨立分區(qū)進(jìn)行優(yōu)化、檢查、修復(fù)等操作。

部分查詢能夠從查詢條件確定只落在少數(shù)分區(qū)上，查詢速度會很快。

分區(qū)表的數(shù)據(jù)還可以分布在不同的物理設(shè)備上，從而高效利用多個硬件設(shè)備。

可以使用分區(qū)表來避免某些特殊瓶頸，例如 InnoDB 單個索引的互斥訪問、ext3 文件系統(tǒng)的 inode 鎖競爭。

可以備份和恢復(fù)單個分區(qū)。

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

一個表最多只能有 1024 個分區(qū)。

如果分區(qū)字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進(jìn)來。

分區(qū)表無法使用外鍵約束。

NULL 值會使分區(qū)過濾無效。

所有分區(qū)必須使用相同的存儲引擎。

4. 分區(qū)實踐

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

首先我們先來查看一下當(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ù)某一個字段的值進(jìn)行分區(qū)。不過這個字段有一個要求，就是必須是主鍵或者是聯(lián)合主鍵中的某個字段。

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

當(dāng) id 小于 100，數(shù)據(jù)插入 p0 分區(qū)；

當(dāng) id 大于等于 100 小于 200 的時候，插入 p1 分區(qū)；

如果 id 大于等于 200 則插入 p2 分區(qū)。

上面的規(guī)則涉及到了 id 的所有范圍了，如果沒有第三條規(guī)則，那么插入一個 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 );

1

2

3

4

5

6

7

8

9

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

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

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

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

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

1

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

RANGE 分區(qū)有一個比較典型的使用場景，就是按照日期對表進(jìn)行分區(qū)，例如同一年注冊的用戶放在一個分區(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) );

1

2

3

4

5

6

7

8

9

10

11

12

**注意，createDate 是聯(lián)合主鍵的一員。**如果 createDate 不是主鍵，只是一個普通字段，那么創(chuàng)建時就會拋出如下錯誤：

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

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

alter table user drop partition p2022;

1

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

4.2 LIST 分區(qū)

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

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

1

2

3

4

5

6

7

8

9

10

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

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

4.3 HASH 分區(qū)

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

使用 HASH 分區(qū)來分割一個表，要在 CREATE TABLE 語句上添加 PARTITION BY HASH (expr)，其中 expr 是一個字段或者是一個返回整數(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;

1

2

3

4

5

6

7

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

舉個例子：

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;

1

2

3

4

5

6

7

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ū)支持多個字段作為分區(qū)鍵但是不支持表達(dá)式作為分區(qū)鍵。

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

這是 RANGE COLUMNS，分區(qū)值是連續(xù)的。

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

5. 常見分區(qū)命令

添加分區(qū)：

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

1

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

1

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

alter table user drop partition p30;

1

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

alter table user remove partitioning;

1

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

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

1

2

3

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

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

1

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

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

1

6. 小結(jié)

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

參考資料：

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

MySQL 數(shù)據(jù)庫

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。