mysql表的四種分區(qū)方式是什么

這篇文章主要介紹“mysql表的四種分區(qū)方式是什么”的相關(guān)知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“mysql表的四種分區(qū)方式是什么”文章能幫助大家解決問題。

1、什么是表分區(qū)？

mysql數(shù)據(jù)庫中的數(shù)據(jù)是以文件的形勢存在磁盤上的，默認(rèn)放在/mysql/data下面（可以通過my.cnf中的datadir來查看），一張表主要對應(yīng)著三個文件，一個是frm存放表結(jié)構(gòu)的，一個是myd存放表數(shù)據(jù)的，一個是myi存表索引的。如果一張表的數(shù)據(jù)量太大的話，那么myd,myi就會變的很大，查找數(shù)據(jù)就會變的很慢，這個時候我們可以利用mysql的分區(qū)功能，在物理上將這一張表對應(yīng)的三個文件，分割成許多個小塊，這樣呢，我們查找一條數(shù)據(jù)時，就不用全部查找了，只要知道這條數(shù)據(jù)在哪一塊，然后在那一塊找就行了。如果表的數(shù)據(jù)太大，可能一個磁盤放不下，這個時候，我們可以把數(shù)據(jù)分配到不同的磁盤里面去。

表分區(qū)，是指根據(jù)一定規(guī)則，將數(shù)據(jù)庫中的一張表分解成多個更小的，容易管理的部分。從邏輯上看，只有一張表，但是底層卻是由多個物理分區(qū)組成。

2、表分區(qū)與分表的區(qū)別

分表：指的是通過一定規(guī)則，將一張表分解成多張不同的表。比如將用戶訂單記錄根據(jù)時間成多個表。 分表與分區(qū)的區(qū)別在于：分區(qū)從邏輯上來講只有一張表，而分表則是將一張表分解成多張表。

3、表分區(qū)有什么好處？

（1）、與單個磁盤或文件系統(tǒng)分區(qū)相比，可以存儲更多的數(shù)據(jù)。

（2）、對于那些已經(jīng)失去保存意義的數(shù)據(jù)，通常可以通過刪除與那些數(shù)據(jù)有關(guān)的分區(qū)，很容易地刪除那些數(shù)據(jù)。相反地，在某些情況下，添加新數(shù)據(jù)的過程又可以通過為那些新數(shù)據(jù)專門增加一個新的分區(qū)，來很方便地實現(xiàn)。

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

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

（5）、通過跨多個磁盤來分散數(shù)據(jù)查詢，來獲得更大的查詢吞吐量。

4、分區(qū)表的限制因素

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

（2）、 MySQL5.1中，分區(qū)表達式必須是整數(shù)，或者返回整數(shù)的表達式。在MySQL5.5中提供了非整數(shù)表達式分區(qū)的支持。

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

（4）、分區(qū)表中無法使用外鍵約束。

（5）、MySQL的分區(qū)適用于一個表的所有數(shù)據(jù)和索引，不能只對表數(shù)據(jù)分區(qū)而不對索引分區(qū)，也不能只對索引分區(qū)而不對表分區(qū)，也不能只對表的一部分?jǐn)?shù)據(jù)分區(qū)。

5、如何判斷當(dāng)前MySQL是否支持分區(qū)？

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

have_partintioning 的值為YES，表示支持分區(qū)。

 6、MySQL支持的分區(qū)類型有哪些？

（1）、RANGE分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

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

（3）、HASH分區(qū)：基于用戶定義的表達式的返回值來進行選擇的分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達式。

（4）、KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。

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

6.1、RANGE分區(qū)

根據(jù)范圍分區(qū)，范圍應(yīng)該連續(xù)但是不重疊，使用PARTITION BY RANGE, VALUES LESS THAN關(guān)鍵字。不使用COLUMNS關(guān)鍵字時RANGE括號內(nèi)必須為整數(shù)字段名或返回確定整數(shù)的函數(shù)。

6.1.1、根據(jù)數(shù)值范圍

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

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

對于包含數(shù)據(jù)(6,'亢八','亢','2018-06-24',13)的一個新行，可以很容易地確定它將插入到p2分區(qū)中。

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

但是如果增加了一個編號為第21的商店(7,'周九','周','2018-07-24',21)，將會發(fā)生什么呢？在這種方案下，由于沒有規(guī)則把store_id大于20的商店包含在內(nèi)，服務(wù)器將不知道把該行保存在何處，將會導(dǎo)致錯誤。

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、根據(jù)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、根據(jù)DATE、DATETIME范圍

添加COLUMNS關(guān)鍵字可定義非integer范圍及多列范圍，不過需要注意COLUMNS括號內(nèi)只能是列名，不支持函數(shù)；多列范圍時，多列范圍必須呈遞增趨勢：

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、根據(jù)多列范圍

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分區(qū)在如下場合特別有用

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）、當(dāng)需要刪除一個分區(qū)上的“舊的”數(shù)據(jù)時，只刪除分區(qū)即可。如果你使用上面最近的那個例子給出的分區(qū)方案，你只需簡單地使用”alter table staff drop partition p0;”來刪除所有在1991年前就已經(jīng)停止工作的雇員相對應(yīng)的所有行。對于有大量行的表，這比運行一個如”delete from staff WHERE year(separated) <= 1990;”這樣的一個DELETE查詢要有效得多。

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

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

6.2、LIST分區(qū)

根據(jù)具體數(shù)值分區(qū)，每個分區(qū)數(shù)值不重疊，使用PARTITION BY LIST、VALUES IN關(guān)鍵字。跟Range分區(qū)類似，不使用COLUMNS關(guān)鍵字時List括號內(nèi)必須為整數(shù)字段名或返回確定整數(shù)的函數(shù)。

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

LIST分區(qū)通過使用“PARTITION BY LIST(expr)”來實現(xiàn)，其中“expr”是某列值或一個基于某個列值、并返回一個整數(shù)值的表達式，然后通過“VALUES IN (value_list)”的方式來定義每個分區(qū)，其中“value_list”是一個通過逗號分隔的整數(shù)列表。

假定有20個音像店，分布在4個有經(jīng)銷權(quán)的地區(qū)，如下表所示：

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

地區(qū)      商店ID 號

北區(qū)      3, 5, 6, 9, 17

東區(qū)      1, 2, 10, 11, 19, 20

西區(qū)      4, 12, 13, 14, 18

中心區(qū)   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)
);

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

如果試圖插入列值（或分區(qū)表達式的返回值）不在分區(qū)值列表中的一行時，那么“INSERT”查詢將失敗并報錯。

當(dāng)插入多條數(shù)據(jù)出錯時，如果表的引擎支持事務(wù)（Innodb），則不會插入任何數(shù)據(jù)；如果不支持事務(wù)，則出錯前的數(shù)據(jù)會插入，后面的不會執(zhí)行。

與Range分區(qū)相同，添加COLUMNS關(guān)鍵字可支持非整數(shù)和多列。

6.3、HASH分區(qū)

 Hash分區(qū)主要用來確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布，Hash括號內(nèi)只能是整數(shù)列或返回確定整數(shù)的函數(shù)，實際上就是使用返回的整數(shù)對分區(qū)數(shù)取模。

要使用HASH分區(qū)來分割一個表，要在CREATE TABLE 語句上添加一個“PARTITION BY HASH (expr)”子句，其中“expr”是一個返回一個整數(shù)的表達式。它可以僅僅是字段類型為MySQL整型的一列的名字。此外，你很可能需要在后面再添加一個“PARTITIONS num”子句，其中num是一個非負(fù)的整數(shù)，它表示表將要被分割成分區(qū)的數(shù)量。

如果沒有包括一個PARTITIONS子句，那么分區(qū)的數(shù)量將默認(rèn)為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分區(qū)也存在與傳統(tǒng)Hash分表一樣的問題，可擴展性差。MySQL也提供了一個類似于一致Hash的分區(qū)方法－線性Hash分區(qū)，只需要在定義分區(qū)時添加LINEAR關(guān)鍵字。

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;

線性哈希功能，它與常規(guī)哈希的區(qū)別在于，線性哈希功能使用的一個線性的2的冪（powers-of-two）運算法則，而常規(guī)哈希使用的是求哈希函數(shù)值的模數(shù)。

6.4、KEY分區(qū)

Key分區(qū)與Hash分區(qū)很相似，只是Hash函數(shù)不同，定義時把Hash關(guān)鍵字替換成Key即可，同樣Key分區(qū)也有對應(yīng)與線性Hash的線性Key分區(qū)方法。

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分區(qū)中使用關(guān)鍵字LINEAR和在HASH分區(qū)中使用具有同樣的作用，分區(qū)的編號是通過2的冪（powers-of-two）算法得到，而不是通過模數(shù)算法。

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

關(guān)于“mysql表的四種分區(qū)方式是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識，可以關(guān)注億速云行業(yè)資訊頻道，小編每天都會為大家更新不同的知識點。