mysql表的四種分割區方式總結

2022-04-14 10:03:52

1、什麼是表分割區？

mysql資料庫中的資料是以檔案的形勢存在磁碟上的，預設放在/mysql/data下面（可以通過my.cnf中的datadir來檢視），一張表主要對應著三個檔案，一個是frm存放表結構的，一個是myd存放表資料的，一個是myi存表索引的。如果一張表的資料量太大的話，那麼myd,myi就會變的很大，查詢資料就會變的很慢，這個時候我們可以利用mysql的分割區功能，在物理上將這一張表對應的三個檔案，分割成許多個小塊，這樣呢，我們查詢一條資料時，就不用全部查詢了，只要知道這條資料在哪一塊，然後在那一塊找就行了。如果表的資料太大，可能一個磁碟放不下，這個時候，我們可以把資料分配到不同的磁碟裡面去。

表分割區，是指根據一定規則，將資料庫中的一張表分解成多個更小的，容易管理的部分。從邏輯上看，只有一張表，但是底層卻是由多個物理分割區組成。

2、表分割區與分表的區別

分表：指的是通過一定規則，將一張表分解成多張不同的表。比如將使用者訂單記錄根據時間成多個表。分表與分割區的區別在於：分割區從邏輯上來講只有一張表，而分表則是將一張表分解成多張表。

3、表分割區有什麼好處？

（1）、與單個磁碟或檔案系統分割區相比，可以儲存更多的資料。

（2）、對於那些已經失去儲存意義的資料，通常可以通過刪除與那些資料有關的分割區，很容易地刪除那些資料。相反地，在某些情況下，新增新資料的過程又可以通過為那些新資料專門增加一個新的分割區，來很方便地實現。

（3）、一些查詢可以得到極大的優化，這主要是藉助於滿足一個給定WHERE語句的資料可以只儲存在一個或多個分割區內，這樣在查詢時就不用查詢其他剩餘的分割區。因為分割區可以在建立了分割區表後進行修改，所以在第一次設定分割區方案時還不曾這麼做時，可以重新組織資料，來提高那些常用查詢的效率。

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

（5）、通過跨多個磁碟來分散資料查詢，來獲得更大的查詢吞吐量。

4、分割區表的限制因素

（1）、一個表最多隻能有1024個分割區。

（2）、 MySQL5.1中，分割區表示式必須是整數，或者返回整數的表示式。在MySQL5.5中提供了非整數表示式分割區的支援。

（3）、如果分割區欄位中有主鍵或者唯一索引的列，那麼多有主鍵列和唯一索引列都必須包含進來。即：分割區欄位要麼不包含主鍵或者索引列，要麼包含全部主鍵和索引列。

（4）、分割區表中無法使用外來鍵約束。

（5）、MySQL的分割區適用於一個表的所有資料和索引，不能只對表資料分割區而不對索引分割區，也不能只對索引分割區而不對錶分割區，也不能只對表的一部分資料分割區。

5、如何判斷當前MySQL是否支援分割區？

mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

have_partintioning 的值為YES，表示支援分割區。

6、MySQL支援的分割區型別有哪些？

（1）、RANGE分割區：基於屬於一個給定連續區間的列值，把多行分配給分割區。

（2）、LIST分割區：類似於按RANGE分割區，區別在於LIST分割區是基於列值匹配一個離散值集合中的某個值來進行選擇。

（3）、HASH分割區：基於使用者定義的表示式的返回值來進行選擇的分割區，該表示式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效的、產生非負整數值的任何表示式。

（4）、KEY分割區：類似於按HASH分割區，區別在於KEY分割區只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函數。必須有一列或多列包含整數值。

說明：在MySQL5.1版本中，RANGE,LIST,HASH分割區要求分割區鍵必須是INT型別，或者通過表示式返回INT型別。但KEY分割區的時候，可以使用其他型別的列(BLOB，TEXT型別除外)作為分割區鍵。

6.1、RANGE分割區

根據範圍分割區，範圍應該連續但是不重疊，使用PARTITION BY RANGE, VALUES LESS THAN關鍵字。不使用COLUMNS關鍵字時RANGE括號內必須為整數位段名或返回確定整數的函數。

6.1.1、根據數值範圍

drop table if exists employees;
create table employees(
    id int not null,
    fname varchar(30),
    lname varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job_code int not null default 0,
    store_id int not null default 0
)engine=myisam default charset=utf8
partition by range(store_id)(
    partition p0 values less than (6),
    partition p1 values less than (11),
    partition p2 values less than (16),
    partition p3 values less than (21)
);

insert into employees (id,fname,lname,hired,store_id) values(1,'張三','張','2015-05-04',1);
insert into employees (id,fname,lname,hired,store_id) values(2,'李四','李','2016-10-01',5);
insert into employees (id,fname,lname,hired,store_id) values(3,'王五','王','2016-11-14',10);
insert into employees (id,fname,lname,hired,store_id) values(4,'趙六','趙','2017-08-24',15);
insert into employees (id,fname,lname,hired,store_id) values(5,'田七','田','2018-05-20',20);

按照這種分割區方案，在商店1到5工作的僱員相對應的所有行被儲存在分割區P0中，商店6到10的僱員儲存在P1中，依次類推。注意，每個分割區都是按順序進行定義，從最低到最高。這是PARTITION BY RANGE 語法的要求。

對於包含資料(6,'亢八','亢','2018-06-24',13)的一個新行，可以很容易地確定它將插入到p2分割區中。

insert into employees (id,fname,lname,hired,store_id) values(6,'亢八','亢','2018-06-24',13);

但是如果增加了一個編號為第21的商店(7,'周九','周','2018-07-24',21)，將會發生什麼呢？在這種方案下，由於沒有規則把store_id大於20的商店包含在內，伺服器將不知道把該行儲存在何處，將會導致錯誤。

insert into employees (id,fname,lname,hired,store_id) values(7,'周九','周','2018-07-24',21);
 
ERROR 1526 (HY000): Table has no partition for value 21

要避免這種錯誤，可以通過在CREATE TABLE語句中使用一個“catchall” VALUES LESS THAN子句，該子句提供給所有大於明確指定的最高值的值：

create table employees(
    id int not null,
    fname varchar(30),
    lname varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job_code int not null default 0,
    store_id int not null default 0
)engine=myisam default charset=utf8
partition by range(store_id)(
    partition p0 values less than (6),
    partition p1 values less than (11),
    partition p2 values less than (16),
    partition p3 values less than (21),
  partition p4 values less than MAXVALUE 
);

6.1.2、根據TIMESTAMP範圍

drop table if exists quarterly_report_status;
create table quarterly_report_status(
  report_id int not null,
  report_status varchar(20) not null,
  report_updated timestamp not null default current_timestamp on update current_timestamp
)
partition by range(unix_timestamp(report_updated))(
  partition p0 values less than (unix_timestamp('2008-01-01 00:00:00')),
  partition p1 values less than (unix_timestamp('2008-04-01 00:00:00')),
  partition p2 values less than (unix_timestamp('2008-07-01 00:00:00')),
  partition p3 values less than (unix_timestamp('2008-10-01 00:00:00')),
  partition p4 values less than (unix_timestamp('2009-01-01 00:00:00')),
  partition p5 values less than (unix_timestamp('2009-04-01 00:00:00')),
  partition p6 values less than (unix_timestamp('2009-07-01 00:00:00')),
  partition p7 values less than (unix_timestamp('2009-10-01 00:00:00')),
  partition p8 values less than (unix_timestamp('2010-01-01 00:00:00')),
  partition p9 values less than maxvalue
);

6.1.3、根據DATE、DATETIME範圍

新增COLUMNS關鍵字可定義非integer範圍及多列範圍，不過需要注意COLUMNS括號內只能是列名，不支援函數；多列範圍時，多列範圍必須呈遞增趨勢：

drop table if exists member;
create table member(
  firstname varchar(25) not null,
  lastname varchar(25) not null,
  username varchar(16) not null,
  email varchar(35),
  joined date not null
)
partition by range columns(joined)(
  partition p0 values less than ('1960-01-01'),
  partition p1 values less than ('1970-01-01'),
  partition p2 values less than ('1980-01-01'),
  partition p3 values less than ('1990-01-01'),
  partition p4 values less than maxvalue
)

6.1.4、根據多列範圍

drop table if exists rc3;
create table rc3(
  a int,
  b int
)
partition by range columns(a,b)(
  partition p0 values less than (0,10),
  partition p1 values less than (10,20),
  partition p2 values less than (20,30),
  partition p3 values less than (30,40),
  partition p4 values less than (40,50),
  partition p5 values less than (maxvalue,maxvalue)
)

6.1.5、RANGE分割區在如下場合特別有用

drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)engine=myisam default charset=utf8
partition by range(year(separated))(
  partition p0 values less than (1991),
  partition p1 values less than (1996),
  partition p2 values less than (2001),
  partition p4 values less than MAXVALUE
);

（1）、當需要刪除一個分割區上的“舊的”資料時，只刪除分割區即可。如果你使用上面最近的那個例子給出的分割區方案，你只需簡單地使用”alter table staff drop partition p0;”來刪除所有在1991年前就已經停止工作的僱員相對應的所有行。對於有大量行的表，這比執行一個如”delete from staff WHERE year(separated) <= 1990;”這樣的一個DELETE查詢要有效得多。

（2）、想要使用一個包含有日期或時間值，或包含有從一些其他級數開始增長的值的列。

（3）、經常執行直接依賴於用於分割表的列的查詢。例如，當執行一個如”select count(*) from staff where year(separated) = 200 group by store_id;”這樣的查詢時，MySQL可以很迅速地確定只有分割區p2需要掃描，這是因為餘下的分割區不可能包含有符合該WHERE子句的任何記錄。

6.2、LIST分割區

根據具體數值分割區，每個分割區數值不重疊，使用PARTITION BY LIST、VALUES IN關鍵字。跟Range分割區類似，不使用COLUMNS關鍵字時List括號內必須為整數位段名或返回確定整數的函數。

類似於按RANGE分割區，區別在於LIST分割區是基於列值匹配一個離散值集合中的某個值來進行選擇。

LIST分割區通過使用“PARTITION BY LIST(expr)”來實現，其中“expr”是某列值或一個基於某個列值、並返回一個整數值的表示式，然後通過“VALUES IN (value_list)”的方式來定義每個分割區，其中“value_list”是一個通過逗號分隔的整數列表。

假定有20個音像店，分佈在4個有經銷權的地區，如下表所示：

====================

地區商店ID 號

北區 3, 5, 6, 9, 17

東區 1, 2, 10, 11, 19, 20

西區 4, 12, 13, 14, 18

中心區 7, 8, 15, 16

drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)
partition by list(store_id)(
  partition pNorth values in (3,5,6,9,17),
  partition pEast values in (1,2,10,11,19,20),
  partition pWest values in (4,12,13,14,18),
  partition pCentral values in (7,8,15,16)
);

這使得在表中增加或刪除指定地區的僱員記錄變得容易起來。例如，假定西區的所有音像店都賣給了其他公司。那麼與在西區音像店工作僱員相關的所有記錄（行）可以使用查詢“ALTER TABLE staff DROP PARTITION pWest；”來進行刪除，它與具有同樣作用的DELETE（刪除）“DELETE FROM staff WHERE store_id IN (4,12,13,14,18)；”比起來，要有效得多。

如果試圖插入列值（或分割區表示式的返回值）不在分割區值列表中的一行時，那麼“INSERT”查詢將失敗並報錯。

當插入多條資料出錯時，如果表的引擎支援事務（Innodb），則不會插入任何資料；如果不支援事務，則出錯前的資料會插入，後面的不會執行。

與Range分割區相同，新增COLUMNS關鍵字可支援非整數和多列。

6.3、HASH分割區

Hash分割區主要用來確保資料在預先確定數目的分割區中平均分佈，Hash括號內只能是整數列或返回確定整數的函數，實際上就是使用返回的整數對分割區數取模。

要使用HASH分割區來分割一個表，要在CREATE TABLE 語句上新增一個“PARTITION BY HASH (expr)”子句，其中“expr”是一個返回一個整數的表示式。它可以僅僅是欄位型別為MySQL整型的一列的名字。此外，你很可能需要在後面再新增一個“PARTITIONS num”子句，其中num是一個非負的整數，它表示表將要被分割成分割區的數量。

如果沒有包括一個PARTITIONS子句，那麼分割區的數量將預設為1

drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)
partition by hash(store_id)
partitions 4;

drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)
partition by hash(year(hired))
partitions 4;

Hash分割區也存在與傳統Hash分表一樣的問題，可延伸性差。MySQL也提供了一個類似於一致Hash的分割區方法－線性Hash分割區，只需要在定義分割區時新增LINEAR關鍵字。

drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)
partition by linear hash(year(hired))
partitions 4;

線性雜湊功能，它與常規雜湊的區別在於，線性雜湊功能使用的一個線性的2的冪（powers-of-two）運演演算法則，而常規雜湊使用的是求雜湊函數值的模數。

6.4、KEY分割區

Key分割區與Hash分割區很相似，只是Hash函數不同，定義時把Hash關鍵字替換成Key即可，同樣Key分割區也有對應與線性Hash的線性Key分割區方法。

drop table if exists staff;
create table staff(
  id int not null,
  fname varchar(30),
  lname varchar(30),
  hired date not null default '1970-01-01',
  separated date not null default '9999-12-31',
  job_code int not null default 0,
  store_id int not null default 0
)
partition by key(store_id)
partitions 4;

在KEY分割區中使用關鍵字LINEAR和在HASH分割區中使用具有同樣的作用，分割區的編號是通過2的冪（powers-of-two）演演算法得到，而不是通過模數演演算法。

另外，當表存在主鍵或唯一索引時可省略Key括號內的列名，Mysql將按照主鍵－唯一索引的順序選擇，當找不到唯一索引時報錯。