溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“mysql表的四種分區方式是什么”的相關知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“mysql表的四種分區方式是什么”文章能幫助大家解決問題。
mysql數據庫中的數據是以文件的形勢存在磁盤上的，默認放在/mysql/data下面（可以通過my.cnf中的datadir來查看），一張表主要對應著三個文件，一個是frm存放表結構的，一個是myd存放表數據的，一個是myi存表索引的。如果一張表的數據量太大的話，那么myd,myi就會變的很大，查找數據就會變的很慢，這個時候我們可以利用mysql的分區功能，在物理上將這一張表對應的三個文件，分割成許多個小塊，這樣呢，我們查找一條數據時，就不用全部查找了，只要知道這條數據在哪一塊，然后在那一塊找就行了。如果表的數據太大，可能一個磁盤放不下，這個時候，我們可以把數據分配到不同的磁盤里面去。
表分區，是指根據一定規則，將數據庫中的一張表分解成多個更小的，容易管理的部分。從邏輯上看，只有一張表，但是底層卻是由多個物理分區組成。
分表：指的是通過一定規則，將一張表分解成多張不同的表。比如將用戶訂單記錄根據時間成多個表。 分表與分區的區別在于：分區從邏輯上來講只有一張表，而分表則是將一張表分解成多張表。
（1）、與單個磁盤或文件系統分區相比，可以存儲更多的數據。
（2）、對于那些已經失去保存意義的數據，通常可以通過刪除與那些數據有關的分區，很容易地刪除那些數據。相反地，在某些情況下，添加新數據的過程又可以通過為那些新數據專門增加一個新的分區，來很方便地實現。
（3）、一些查詢可以得到極大的優化，這主要是借助于滿足一個給定WHERE語句的數據可以只保存在一個或多個分區內，這樣在查找時就不用查找其他剩余的分區。因為分區可以在創建了分區表后進行修改，所以在第一次配置分區方案時還不曾這么做時，可以重新組織數據，來提高那些常用查詢的效率。
（4）、涉及到例如SUM()和COUNT()這樣聚合函數的查詢，可以很容易地進行并行處理。這種查詢的一個簡單例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通過“并行”，這意味著該查詢可以在每個分區上同時進行，最終結果只需通過總計所有分區得到的結果。
（5）、通過跨多個磁盤來分散數據查詢，來獲得更大的查詢吞吐量。
（1）、一個表最多只能有1024個分區。
（2）、 MySQL5.1中，分區表達式必須是整數，或者返回整數的表達式。在MySQL5.5中提供了非整數表達式分區的支持。
（3）、如果分區字段中有主鍵或者唯一索引的列，那么多有主鍵列和唯一索引列都必須包含進來。即：分區字段要么不包含主鍵或者索引列，要么包含全部主鍵和索引列。
（4）、分區表中無法使用外鍵約束。
（5）、MySQL的分區適用于一個表的所有數據和索引，不能只對表數據分區而不對索引分區，也不能只對索引分區而不對表分區，也不能只對表的一部分數據分區。
mysql> show variables like '%partition%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | have_partitioning | YES | +-------------------+-------+ 1 row in set (0.00 sec)
have_partintioning 的值為YES，表示支持分區。
（1）、RANGE分區：基于屬于一個給定連續區間的列值，把多行分配給分區。
（2）、LIST分區：類似于按RANGE分區，區別在于LIST分區是基于列值匹配一個離散值集合中的某個值來進行選擇。
（3）、HASH分區：基于用戶定義的表達式的返回值來進行選擇的分區，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效的、產生非負整數值的任何表達式。
（4）、KEY分區：類似于按HASH分區，區別在于KEY分區只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數。必須有一列或多列包含整數值。
說明：在MySQL5.1版本中，RANGE,LIST,HASH分區要求分區鍵必須是INT類型，或者通過表達式返回INT類型。但KEY分區的時候，可以使用其他類型的列(BLOB，TEXT類型除外)作為分區鍵。
根據范圍分區，范圍應該連續但是不重疊，使用PARTITION BY RANGE, VALUES LESS THAN關鍵字。不使用COLUMNS關鍵字時RANGE括號內必須為整數字段名或返回確定整數的函數。
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 );
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
);添加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
)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) )
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子句的任何記錄。
根據具體數值分區，每個分區數值不重疊，使用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關鍵字可支持非整數和多列。
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）運算法則，而常規哈希使用的是求哈希函數值的模數。
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將按照主鍵－唯一索引的順序選擇，當找不到唯一索引時報錯。
關于“mysql表的四種分區方式是什么”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識，可以關注億速云行業資訊頻道，小編每天都會為大家更新不同的知識點。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
