一、前言

MySQL數據庫作為一個程序員，我想大家肯定是非常的熟悉的,并且我們在面試中也經常被問到MySQL相關的知識點，其中MySQL的優化這個問題肯定是中高級面試跑不掉的問題。大部分同學都知道進行SQL語句優化、分庫、分表等等操作，但是進行表分區這個操作可能還有些陌生。說實話我之前也是不知道這個東西的，直到項目中同事使用了，我才知道有這個東西可以進行MySQL的優化。在這里我不講解SQL語句的優化及如何進行分庫分表，只講解如何對MySQL進行表分區及表分區相關的知識，看完本文后你可以了解到如下內容：

• MySQL數據庫表分區相關的知識點
• 如何快速的構建一張大數據量的MySQL表
• 項目中如何對已存在的大數據表進行表分區

提示：此處我使用的MySQL版本號為5.7.33-log

二、MySQL表分區

1.什么是表分區以及為什么要分區

我們首先找到mysql的數據存儲目錄，可以通過語句show variables like ‘%datadir%’;查看，我本機的是"C:\ProgramData\MySQL\MySQL Server 8.0\Data"，在該目錄下，可以看到每個數據庫對應著一個文件夾，對于沒有分區的表，庫中的每個表就對應著文件夾下的一個ibd文件

當一個表中的數據量太大時，會面臨兩個問題，一是對數據的操作會變慢，比如select、join、update、delete時，會對全表操作；二是不便于存儲，可能會出現剩余磁盤空間存儲不下這張表的情況。而分區就可以在一定程度上解決這兩個問題。

簡要的說，分區就是將表物理截斷，但在邏輯上依然是一個整體，開發人員在數據操作時仍然是對這個整體大表進行操作，之后由數據庫底層自己去尋找對應的分區進行操作，數據庫底層尋找分區這個過程對開發人員來說是透明的，這樣在數據操作時可以只對特定分區操作以提高效率，存儲時也可以將不同分區的物理文件分開存放，下面是一個有3個分區(p1、p2、p3)的表(p_table)的實際存儲

提示：只有當過濾條件為分區的字段時才會自動尋找分區，否則還是全表掃描

2.表分區與分表的區別

分區是指將數據庫底層存儲數據的文件進行切割，切割成多個小文件以此來提高速度
分表是指通過一定規則，將一張表分解成多張不同的表。比如將用戶訂單記錄根據時間成多個表。
分表與分區的區別
分區從邏輯上來講只有一張表，而分表則是將一張表分解成多張表。

3.表分區的優缺點

優點

與單個磁盤或文件系統分區相比，可以存儲更多的數據。

對于那些已經失去保存意義的數據，通常可以通過刪除與那些數據有關的分區，很容易地刪除那些數據。相反地，在某些情況下，添加新數據的過程又可以通過為那些新數據專門增加一個新的分區，來很方便地實現。

一些查詢可以得到極大的優化，這主要是借助于滿足一個給定WHERE語句的數據可以只保存在一個或多個分區內，這樣在查找時就不用查找其他剩余的分區。因為分區可以在創建了分區表后進行修改，所以在第一次配置分區方案時還不曾這么做時，可以重新組織數據，來提高那些常用查詢的效率。

涉及到例如SUM()和COUNT()這樣聚合函數的查詢，可以很容易地進行并行處理。這種查詢的一個簡單例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通過“并行”，這意味著該查詢可以在每個分區上同時進行，最終結果只需通過總計所有分區得到的結果。

通過跨多個磁盤來分散數據查詢，來獲得更大的查詢吞吐量。

缺點

一個表最多只能有1024個分區

在MySQL5.1中，分區表達式必須是整數，或者返回整數的表達式，但是在MySQL5.5中提供了非整數表達式分區的支持。

如果分區字段中有主鍵或者唯一索引的列，那么多有主鍵列和唯一索引列都必須包含進來。即：分區字段要么不包含主鍵或者索引列，要么包含全部主鍵和索引列。

分區表中無法使用外鍵約束

MySQL的分區適用于一個表的所有數據和索引，不能只對表數據分區而不對索引分區，也不能只對索引分區而不對表分區，也不能只對表的一部分數據分區。

4.如何判斷當前MySQL是否支持分區？

MySQL中5.1版本開始支持分區，但是5.6版本之前和5.6版本之后的查看是否支持分區命令存在差異，首先我們先查看我們的數據庫版本號。

// 先查詢MySQL版本號 SELECT VERSION();

MySQL版本號小于5.6使用如下命令

show variables like '%partiotion%';

MySQL版本號大于5.6使用如下命令

show plugins;

查看查詢結果中是否包含如下內容，若包含則說明支持分區，否則不支持分區

5.MySQL水分分區的類型

之所以特別說明一下是水平分區，是因為還有一種垂直分區的分區方式，二者一個橫向切割一個縱向切割，(對比之下感覺水平分區類似于HBase中的segment，垂直分區類似于HBase中的region~)，關于垂直分區先跳過，一是沒找到多少相關的資料，二是感覺業務中用到的也不多，大多用的都是水平分區，有時間日后再補。

常見的水平分區類型有 RANGE、LIST、HASH、KEY 、復合等五種。
??如果一個分區表定義了主鍵或唯一索引，則分區鍵必須被包含在主鍵或唯一索引的字段中，否則無法建表成功。這很好理解，使用主鍵或唯一索引是最常見、最高效檢索數據的方式，如果使用主鍵或唯一索引做分區鍵，根據分區定義，可以直接定位數據在哪個分區，不需要查詢其余分區，最大程度發揮分區的優點。

1.RANGE 分區（常用）

range分區，顧名思義，就是按照范圍進行分區，下面是創建一個range分區表：

drop table if exists `range_table`; create table `range_table`(`id` int,`name` varchar(10) ) partition by range(id)(partition p1 values less than (10),partition p2 values less than (20),partition p3 values less than maxvalue );

上面以id為分區字段，根據id大小劃分為[-∞, 10)，[10, 20)，[20, +∞]三個區間，注意包前不包后，在數據插入時會自動根據id插入到各自分區

# 插入數據 insert into range_table (id, name) values (1,"梁靜茹"),(10,"金泰妍"),(15,"王菲"),(50,"邵夷貝"); # 查看各個分區數據條數 select partition_name,table_rows from information_schema.partitions where table_name = 'range_table';

分別指定分區查看各個分區里面的數據，可以看到id為1的保存到了p1，id為10和15的保存到了p2，id為50的保存到了p3

select * from range_table partition (p1); select * from range_table partition (p2); select * from range_table partition (p3);

在進行select/update/delete時如果where后面的限制條件包含分區字段id時會自動去對應分區中查找，否則還是全表掃描，如下所示。

explain select * from range_table where id = "1" and name = '梁靜茹';

explain select * from range_table where name = '梁靜茹';

提示：range分區字段只支持整型，如果需要對時間日期這樣的字段進行range分區，可以通過相關函數將類型轉為整型再分區。

2.LIST分區

list就是枚舉的意思，list分區就是在創建各分區時具體指定哪些值屬于這些分區，下面是創建list分區表的代碼：

drop table if exists `list_table`; create table `list_table`(`id` int,`name` varchar(10) ) partition by list(id)(partition p1 values in (1),partition p2 values in (10,15,50) );

插入數據

insert into list_table (id, name) values (1,"梁靜茹"),(10,"金泰妍"),(15,"王菲"),(50,"邵夷貝");

id為1的保存到了p1分區，id為10，15，20的保存到了p2分區，需要注意如果插入數據的 id 在各個分區所對應著的列表里面都沒找到，則會報錯。
查詢分區數據結果

select partition_name,table_rows from information_schema.partitions where table_name = 'list_table';

提示：list分區分區字段同樣只能是int型，該分區使用較少

3.HASH分區

hash分區分為常規hash和線性hash，常規hash是在分區字段上基于分區個數的取模運算，根據余數分區。線性hash是對分區字段進行二次方運算，根據運算結果分區，所以hash分區同樣要求分區字段為整型或者是可以返回整型結果的表達式。二者在建表時候的區別只是線性hash比常規hash多了個linear(線性的)限定。

3.1.常規hash

常規hash分區建表：

drop table if exists `hash_table`; create table `hash_table`(`id` int,`name` varchar(10) ) partition by hash(id) partitions 3;

hash分區不能指定分區名，會默認創建名為pn的分區，n從0開始自增。上面這段代碼會創建p0，p1，p2三個分區，分區名可以通過下面的sql查看，

select partition_name from information_schema.PARTITIONS where table_schema = schema() and table_name = "hash_table";

上面說的常規hash就是基于分區數對分區字段進行取模求余操作，按照這種計算，插入下面的數據，

insert into hash_table (id, name) values (1,"梁靜茹"),(10,"金泰妍"),(15,"王菲"),(50,"邵夷貝");

1 10 15 50 分別對3求余對應的結果 1 1 0 2，也就是上面4條數據應該分別被保存到p1, p1, p0, p2分區，對此進行驗證：

select 'p0' as part, t.* from hash_table partition (p0) t union select 'p1' as part, t.* from hash_table partition (p1) t union select 'p2' as part, t.* from hash_table partition (p2) t;

這樣當在查詢的時候會采用相同的取模運算到對應分區下查找，比如查id為5的數據，就會去p2分區查找。

3.2.線性hash

線性hash在建表時只是比常規hash多了個linear字段：

drop table if exists `hash_linear_table`; create table `hash_linear_table`(`id` int,`name` varchar(10) ) partition by linear hash(id) partitions 3;

插入數據

insert into hash_linear_table (id, name) values (1,"梁靜茹"),(10,"金泰妍"),(15,"王菲"),(50,"邵夷貝");

關于線性分區的具體計算規則可以參考官方文檔：https://dev.mysql.com/doc/refman/8.0/en/partitioning-linear-hash.html，這里假設num是分區個數，value是某條記錄的分區字段對應的值，N是最終經過計算得到的某個分區編號，則N的計算過程如下：

step1：V = power(2, ceil(log(2, num)))
step2：N = value & (V-1) step3：if
N>=num: N=N & (ceil(V/2) - 1)

按照上面步驟，將id為50的這條數據代入計算：

step1：V = power(2, ceil(log(2, num))) = power(2, ceil(log(2, 3))) = power(2, 2) = 4
step2：N = value & (V-1) = 50 & 3 = 110010 & 000011 = 000010 = 2
step3：N>=num? <=> 2>=3? False：N=2

即id為50的這條數據保存到p2分區，同理可以計算出id為10時N=2，id為1時N=1，id為15是N=1，驗證一下計算結果：

select 'p0' as part, t.* from hash_linear_table partition (p0) t union select 'p1' as part, t.* from hash_linear_table partition (p1) t union select 'p2' as part, t.* from hash_linear_table partition (p2) t;

結果計算正確。

4.KEY 分區

主要還是參考官方文檔吧，https://dev.mysql.com/doc/refman/8.0/en/partitioning-key.html，里面主要說的是，key分區類似于hash分區，只不過分區列不再強制為整型，可以為除text和BLOB兩種類型外的其它類型。key分區也有兩種，常規key和線性key，常規key對分區字段采用的是MD5算法，線性key對分區字段采用的是二次方算法，參考hash分區中的線性hash，分區列選取的具體規則為：

• 當表中只有主鍵primary key或只有唯一鍵unique
  key時，分區列必須包含主鍵或唯一鍵中的部分或全部字段，不允許出現主鍵或唯一鍵中字段以外的其它字段
• 當表中主鍵和唯一鍵同時存在時，分區列為主鍵和唯一鍵公共字段的部分或全部
• 當表中主鍵唯一鍵都沒有時：任意指定除text和BLOB類型外的其它字段，可以為1個或多個

分區列也可以缺省不指定，但必須要求表中存在主鍵或唯一鍵，優先以主鍵作為分區字段，沒有主鍵時以唯一鍵作為分區字段，此時唯一鍵必須顯示指定not null。

下面是常規key分區建表的一個demo，name為分區字段：

drop table if exists `key_table`; create table `key_table`(`id` int,`name` varchar(10) not null,unique `uk_name` (name) ) partition by key() partitions 3;

線性key分區的建表也只是多了一個linear字段：

drop table if exists `key_table`; create table `key_table`(`id` int,`name` varchar(10) not null,unique `uk_name` (name) ) partition by linear key() partitions 3;

5.復合分區

文檔地址：https://dev.mysql.com/doc/refman/8.0/en/partitioning-subpartitions.html，里面有這么一段話，
說的是我們可以對采用range分區或者list分區的表，進行二次分區，二次分區只能為hash分區或者key分區。這種分區方式有兩種建表寫法，一種是指定子分區名，一種是不指定子分區名由系統默認。
不指定子分區名創建：

drop table if exists `subpart_table`; create table `subpart_table`(dt date ) partition by range(year(dt)) subpartition by hash(month(dt)) subpartitions 2 (partition p1 values less than (1990),partition p2 values less than (2000),partition p3 values less than maxvalue );

通過如下命令查看各個分區情況：

select partition_name, subpartition_name from information_schema.partitions where table_schema = schema() and table_name = 'subpart_table';

查詢結果如下：

物理上也被分成了單獨的6個文件：

指定分區名創建，這種方式要求每個一級分區下的子分區數量必須一致，所有子分區的分區名不能重復：

drop table if exists `subpart_table`; create table `subpart_table`(dt date ) partition by range(year(dt)) subpartition by hash(month(dt)) (partition p1 values less than (1990)(subpartition s1,subpartition s2),partition p2 values less than (2000)(subpartition s3,subpartition s4),partition p3 values less than maxvalue(subpartition s5,subpartition s6) );

上表根據日期的年份進行一級分區，根據日期的月份二級分區，s1、s3、s5存偶數月，s2、s4、s6存奇數月，插入數據驗證一下：

insert into subpart_table values('1989-01-01'), ('1989-02-01'), ('1995-01-01'), ('1989-02-01'), ('2022-01-01'), ('2022-02-01'); select 's1' as part, t.* from subpart_table partition (s1) t union select 's2' as part, t.* from subpart_table partition (s2) t union select 's3' as part, t.* from subpart_table partition (s3) t union select 's4' as part, t.* from subpart_table partition (s4) t union select 's5' as part, t.* from subpart_table partition (s5) t union select 's6' as part, t.* from subpart_table partition (s6) t;

查詢結果如下：

三、MySQL中如何快速創建大數據表

1.創建表

drop table if exists user; create table user(id int not null primary key auto_increment,number int not null,name varchar(20) not null )engine=innodb default charset=utf8;

2.創建生產編號和名稱的函數

DELIMITER $ CREATE FUNCTION rand_number() RETURNS INT BEGINDECLARE i INT DEFAULT 0;SET i= FLOOR(1+RAND()*100);RETURN i; END $ DELIMITER ; DELIMITER $ CREATE FUNCTION rand_name(n INT) RETURNS VARCHAR(255) BEGINDECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i<n DOSET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); SET i=i+1;END WHILE;RETURN return_str; END $ DELIMITER ;

3.創建生成數據的存儲過程

DELIMITER $ CREATE PROCEDURE insert_user(IN max_num INT(10)) BEGINDECLARE i INT DEFAULT 0;DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; START TRANSACTION;while i < max_num doinsert into user(id,number,name) values(null,rand_number(),rand_name(5));set i = i + 1; END WHILE; COMMIT; END $ DELIMITER ;

4.插入數據

我此處插入的是100W條，如果電腦配置差的話可少插入一點，100W條插入需要耗費些時間，請耐心等待。我插入100W條耗時217秒

call insert_user(1000*10000);

四、項目中如何對已存在的大數據表進行表分區

直接使用ALTER TABLE來進行更改表為分區表，這個操作會創建一個分區表，然后自動進行數據copy然后刪除原表，猜測該操作服務器資源消耗比較大，請謹慎操作。
警告：生產環境中操作時，千萬要記得備份之后再進行操作，以防出現問題導致數據丟失。

本案例使用RANG分區以上面創建的user表為例：
總共100W條數據，分成5個區，每個區20W條數據，操作如下
創建分區語句：

ALTER TABLE user PARTITION BY RANGE (id) ( PARTITION p_0 VALUES LESS THAN (200000), PARTITION p_1 VALUES LESS THAN (400000), PARTITION p_2 VALUES LESS THAN (600000), PARTITION p_3 VALUES LESS THAN (800000), PARTITION p_4 VALUES LESS THAN MAXVALUE );

執行后，回發現ibd文件發生了變化，如下圖所示：

查看表的分區狀態：

select partition_name, subpartition_name,table_rows from information_schema.partitions where table_schema = schema() and table_name = 'user';

驗證分區是否成功

explain select * from user where id=333;

如圖所示，數據已經分區成功！

五、小結

表分區的相關知識大家可以多了解一些，這部分內容雖然工作中可能回很少用到，但是面試的過程中被問到的可能性還是很大的，因此我們還是需要進行掌握的。
本文參考鏈接如下：
https://www.cnblogs.com/liuqiyun/p/15787045.html
https://blog.csdn.net/qq_41487004/article/details/125303464
https://blog.51cto.com/u_13675040/2114580
https://blog.csdn.net/atwdy/article/details/125181469

總結

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

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