mysql表的四種分割方式是什麼
- WBOY轉載
- 2023-05-30 17:17:211486瀏覽
1、什麼是表格分割區？
mysql資料庫中的資料是以檔案的形勢存在磁碟上的，預設放在/mysql/data下面（可以透過my.cnf中的datadir來查看），一張表主要對應三個文件，一個是frm存放表結構的，一個是myd存放表資料的，一個是myi存表索引的。如果一張表的資料量太大的話，那麼myd,myi就會變的很大，查找資料就會變的很慢，這個時候我們可以利用mysql的分區功能，在物理上將這張表對應的三個文件，分割成許多個小塊，這樣呢，我們查找一條數據時，就不用全部查找了，只要知道這條數據在哪一塊，然後在那一塊找就行了。當表的資料量很大時，可能需要將資料分散到多個磁碟中以避免一個磁碟無法容納所有資料。
表格分割區是將資料庫中的資料表根據特定規則拆分成多個更小且易於管理的部分。從邏輯上看，只有一張表，但是底層卻是由多個實體分割區組成。
2、表格分割區與分錶的差異
分錶：指的是通過某一規則，將一張表分解成多張不同的表。例如將用戶訂單記錄根據時間成多個表。分區和分錶的不同之處在於：在邏輯上，分區只是一張表，而分錶則是將一張表分解成多張表。
3、表格分割區有什麼好處？
（1）、與單一磁碟或檔案系統分割區相比，可以儲存更多的資料。
一般情況下，可以透過刪除與那些沒有保留價值的資料相關的分割區來輕鬆清除這些資料。有時候，為了方便地添加新數據，可以創建一個新的分區來專門儲存這些新數據。
（3）、一些查詢可以得到極大的優化，這主要是藉助於滿足一個給定WHERE語句的資料可以只保存在一個或多個分區內，這樣在查找時就不用查找其他剩餘的分區。因為分區可以在創建了分區表後進行修改，所以在第一次配置分區方案時還不曾這麼做時，可以重新組織數據，來提高那些常用查詢的效率。
查詢涉及到聚合函數例如SUM()和COUNT()等時，可以輕鬆地進行並行處理。這種查詢的一個簡單範例如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id;”。 「並行」查詢意味著可以同時在每個分區上執行查詢，最終結果僅需匯總所有分區的結果。
（5）、透過跨多個磁碟來分散資料查詢，來獲得更大的查詢吞吐量。
4、分區表的限制因素
（1）、一個表格最多只能有1024個分割區。
在MySQL5.1中，分割區表達式只能是整數，或是能傳回整數的表達式。在MySQL5.5中提供了非整數表達式分區的支援。
如果在分割欄位中包含有主鍵或唯一索引的資料列，則所有的主鍵列和唯一索引列都必須包含在內。要么分區字段不包括主鍵和索引列，要么包括所有的主鍵和索引列。
（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 );
只需删除分区，就能清除“旧的”数据。如果你使用上面最近的那个例子给出的分区方案，你只需简单地使用”alter table staff drop partition p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表，这比运行一个如”delete from staff WHERE year(separated)
（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关键字。在不使用COLUMNS关键字的情况下，与Range分区类似，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将按照主键－唯一索引的顺序选择，当找不到唯一索引时报错。
以上是mysql表的四種分割方式是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！