文章目錄

• • 1. 什么是表分區
  • 2. 分區的兩種方式
  • • 2.1 水平切分
    • 2.2 垂直切分
  • 3. 為什么需要表分區
  • 4. 分區實踐
  • • 4.1 RANGE 分區
    • 4.2 LIST 分區
    • 4.3 HASH 分區
    • 4.4 KEY 分區
    • 4.5 COLUMNS 分區
  • 5. 常見分區命令
  • 6. 小結

松哥之前寫過文章跟大家介紹過用 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 開始添加了對分區的支持，分區的過程是將一個表或索引分解為多個更小、更可管理的部分。對于開發者而言，分區后的表使用方式和不分區基本上還是一模一樣，只不過在物理存儲上，原本該表只有一個數據文件，現在變成了多個，每個分區都是獨立的對象，可以獨自處理，也可以作為一個更大對象的一部分進行處理。

需要注意的是，分區功能并不是在存儲引擎層完成的，常見的存儲引擎如 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=innodbpartition 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=innodbpartition 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=innodbpartition 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 中的策略非常相似呀？感興趣的小伙伴趕緊去試一把吧～

參考資料：

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

總結

以上是生活随笔為你收集整理的MySQL 表分区？涨知识了的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。