mysql表的四種分區(qū)方式總結
1、什么是表分區(qū)？
mysql數(shù)據(jù)庫中的數(shù)據(jù)是以文件的形勢存在磁盤上的，默認放在/mysql/data下面（可以通過my.cnf中的datadir來查看），一張表主要對應著三個文件，一個是frm存放表結構的，一個是myd存放表數(shù)據(jù)的，一個是myi存表索引的。如果一張表的數(shù)據(jù)量太大的話，那么myd,myi就會變的很大，查找數(shù)據(jù)就會變的很慢，這個時候我們可以利用mysql的分區(qū)功能，在物理上將這一張表對應的三個文件，分割成許多個小塊，這樣呢，我們查找一條數(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ù)有關的分區(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ū)上同時進行，最終結果只需通過總計所有分區(qū)得到的結果。
（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ū)，也不能只對表的一部分數(shù)據(jù)分區(qū)。
5、如何判斷當前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)生非負整數(shù)值的任何表達式。
（4）、KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。
說明：在MySQL5.1版本中，RANGE,LIST,HASH分區(qū)要求分區(qū)鍵必須是INT類型，或者通過表達式返回INT類型。但KEY分區(qū)的時候，可以使用其他類型的列(BLOB，TEXT類型除外)作為分區(qū)鍵。
6.1、RANGE分區(qū)
根據(jù)范圍分區(qū)，范圍應該連續(xù)但是不重疊，使用PARTITION BY RANGE, VALUES LESS THAN關鍵字。不使用COLUMNS關鍵字時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工作的雇員相對應的所有行被保存在分區(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)，服務器將不知道把該行保存在何處，將會導致錯誤。
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關鍵字可定義非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）、當需要刪除一個分區(qū)上的“舊的”數(shù)據(jù)時，只刪除分區(qū)即可。如果你使用上面最近的那個例子給出的分區(qū)方案，你只需簡單地使用”alter table staff drop partition p0;”來刪除所有在1991年前就已經(jīng)停止工作的雇員相對應的所有行。對于有大量行的表，這比運行一個如”delete from staff WHERE year(separated) <= 1990;”這樣的一個DELETE查詢要有效得多。
（2）、想要使用一個包含有日期或時間值，或包含有從一些其他級數(shù)開始增長的值的列。
（3）、經(jī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關鍵字。跟Range分區(qū)類似，不使用COLUMNS關鍵字時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)銷權的地區(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ū)音像店工作雇員相關的所有記錄（行）可以使用查詢“ALTER TABLE staff DROP PARTITION pWest；”來進行刪除，它與具有同樣作用的DELETE（刪除）“DELETE FROM staff WHERE store_id IN (4,12,13,14,18)；”比起來，要有效得多。
如果試圖插入列值（或分區(qū)表達式的返回值）不在分區(qū)值列表中的一行時，那么“INSERT”查詢將失敗并報錯。
當插入多條數(shù)據(jù)出錯時，如果表的引擎支持事務（Innodb），則不會插入任何數(shù)據(jù)；如果不支持事務，則出錯前的數(shù)據(jù)會插入，后面的不會執(zhí)行。
與Range分區(qū)相同，添加COLUMNS關鍵字可支持非整數(shù)和多列。
6.3、HASH分區(qū)
Hash分區(qū)主要用來確保數(shù)據(jù)在預先確定數(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是一個非負的整數(shù)，它表示表將要被分割成分區(qū)的數(shù)量。
如果沒有包括一個PARTITIONS子句，那么分區(qū)的數(shù)量將默認為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關鍵字。
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關鍵字替換成Key即可，同樣Key分區(qū)也有對應與線性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ū)中使用關鍵字LINEAR和在HASH分區(qū)中使用具有同樣的作用，分區(qū)的編號是通過2的冪（powers-of-two）算法得到，而不是通過模數(shù)算法。
另外，當表存在主鍵或唯一索引時可省略Key括號內(nèi)的列名，Mysql將按照主鍵－唯一索引的順序選擇，當找不到唯一索引時報錯。
總結
到此這篇關于mysql表的四種分區(qū)方式的文章就介紹到這了,更多相關mysql分區(qū)方式內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
MySQL BinLog如何恢復誤更新刪除數(shù)據(jù)
這篇文章主要介紹了MySQL BinLog如何恢復誤更新刪除數(shù)據(jù)問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-06-06
最近想到創(chuàng)建一個大量數(shù)據(jù)的測試環(huán)境，于是找了一下怎么插入100W條數(shù)據(jù)，我用的是20個字段2012-04-04
這篇文章主要給大家介紹了關于MYSQL中解析json格式數(shù)據(jù)的相關資料,JSON是一種輕量級的數(shù)據(jù)交換格式,采用了獨立于語言的文本格式,類似XML,但是比XML簡單,易讀并且易編寫,需要的朋友可以參考下2023-08-08
最近老是忘記mysql密碼。網(wǎng)上學習了四種解決辦法，記錄下來。分享給大家2014-07-07
MySQL對JSON類型字段數(shù)據(jù)進行提取和查詢的實現(xiàn)
本文主要介紹了MySQL對JSON類型字段數(shù)據(jù)進行提取和查詢的實現(xiàn)，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-04-04
MySQL server has gone away錯誤提示解決方法
今天遇到類似的情景，MySQL只是冷冷的說：MySQL server has gone away。2008-11-11
這篇文章主要為大家分享了mysql 8.0.16 安裝配置方法圖文教程，具有一定的參考價值，感興趣的朋友可以參考一下2019-05-05
node 多種方法連接mysql數(shù)據(jù)庫(最新推薦)
mysql是一個流行的第三方模塊，可以通過npm安裝，在Node.js 中，有多種方法可以連接 MySQL 數(shù)據(jù)庫，本文通過實例代碼講解node 多種方法連接mysql數(shù)據(jù)庫的示例代碼，感興趣的朋友跟隨小編一起看看吧2023-07-07
這篇文章主要介紹了MySQL InnoDB的索引擴展的相關資料，幫助大家更好的理解和學習MySQL，感興趣的朋友可以了解下2020-08-08
mysql Non-Transactional Database Only(只支持MyISAM)
按照discuz官方的建議，選的都是Non-Transactional Database Only 只支持MyISAM，其實默認都安裝也挺好2016-04-04