分區(qū)表
概念
表分區(qū)就是將一個(gè)大表按照mysq提供的幾種方式,分成幾個(gè)小表塌西。
日常開(kāi)發(fā)中我們經(jīng)常會(huì)遇到大表的情況,所謂的大表是指存儲(chǔ)了百萬(wàn)級(jí)乃至千萬(wàn)級(jí)條記錄的表。這樣的表過(guò)于龐大,導(dǎo)致數(shù)據(jù)庫(kù)在査詢和插入的時(shí)候耗時(shí)太長(zhǎng)筝尾、性能低下,
如果涉及聯(lián)合查詢的情況,性能會(huì)更加糟糕捡需。對(duì)表進(jìn)行分區(qū),目的就是減少數(shù)據(jù)庫(kù)的負(fù)擔(dān),提高數(shù)據(jù)庫(kù)的效率,通常來(lái)講就是提高表的增刪改查效率。
分區(qū)是將數(shù)據(jù)分段劃分在多個(gè)位置存放,可以是同一塊磁盤(pán)也可以在不同的機(jī)器忿等。
分區(qū)后,表面上還是一張表,但數(shù)據(jù)散列到多個(gè)位置了。應(yīng)用程序讀寫(xiě)的時(shí)候操作的還是大表名字,數(shù)據(jù)庫(kù)系統(tǒng)自動(dòng)去組織分區(qū)的數(shù)據(jù)崔挖。
與單個(gè)磁盤(pán)或文件系統(tǒng)分區(qū)相比,可以存儲(chǔ)更多的數(shù)據(jù)贸街。
很容易就能刪除不用或者過(guò)時(shí)的數(shù)據(jù)。
些查詢可以得到極大的優(yōu)化狸相。
涉及到 SUMO/COUNTO等聚合函數(shù)時(shí),可以并行進(jìn)行薛匪。
IO吞吐量更大。分區(qū)允許可以設(shè)置為任意大小的規(guī)則,跨文件系統(tǒng)分配單個(gè)表的多個(gè)部分脓鹃。
實(shí)際上,表的不同部分在不同的位置被存儲(chǔ)為單獨(dú)的表逸尖。
show plugins; 查看分區(qū)表 partition:ACTIVE
范圍分區(qū)
通常是使用頻率最高的分區(qū),如按月份劃分,這樣的數(shù)據(jù)保持均勻性比較好,如果劃分的均勻性不是很好,需要考慮其他分區(qū)方法。
例如,可以將一個(gè)表通過(guò)年份劃分成兩個(gè)分區(qū),2001-2010年、2011-2020娇跟。
創(chuàng)建分區(qū)表
CREATE TABLE part_tab(
c1 int default NULL,
c2 varchar (30) default NULL,
c3 date default Null
)Engine=INNODB
PARTITION BY RANGE (year(c3))
(
PARTITION p0 VALUES LESS THAN(1995),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (1997),
PARTITION p3 VALUES LESS THAN (1998),
PARTITION p4 VALUES LESS THAN (1999),
PARTITION p5 VALUES LESS THAN (2000),
PARTITION p6 VALUES LESS THAN (2001),
PARTITION p7 VALUES LESS THAN (2002),
PARTITION p8 VALUES LESS THAN (2003),
PARTITION p9 VALUES LESS THAN (2004),
PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN MAXVALUE
);
(0 - 1995) 走p0分區(qū) , MAXVALUE 其他數(shù)字走p11分區(qū)
創(chuàng)建普通表
CREATE TABLE no_part_tab(
c1 int default NULL,
c2 varchar (30) default NULL,
c3 date default Null
)Engine=INNODB;
查看分區(qū)表
SELECT
PARTITION_name,
PARTITION_expression,
table_rows
FROM
information_schema.PARTITIONS
WHERE
table_schema = SCHEMA ()
AND table_name = 'part_tab';
創(chuàng)建函數(shù)插入數(shù)據(jù)
delimiter //
CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
while v < 1000000
do
insert into part_tab values (v, 'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
set v = V + 1;
end while ;
end //
# 兩個(gè)表都插入數(shù)據(jù)
call load_part_tab();
insert into no_part_tab select * from part_tab;
查詢
select COUNT(*) from part_tab where c3 > date '1990-01-01' and c3 < date '1995-12-31';
select COUNT(*) from no_part_tab where c3 > date '1990-01-01' and c3 < date '1995-12-31';
哈希分區(qū)
如果數(shù)據(jù)不是那么容易進(jìn)行劃分,通過(guò)這種方式就很靈活了岩齿。
可以將數(shù)據(jù)均勻的插入到不同的塊,在并發(fā)時(shí)有利于提高效率,當(dāng)無(wú)法用 Range分區(qū)時(shí),就可以用Hash分區(qū)。
創(chuàng)建哈希分區(qū)
CREATE TABLE hash_tab(
c1 int default NULL,
c2 varchar (30) default NULL,
c3 date default Null
)Engine=INNODB
partition by HASH(YEAR(c3))
PARTITIONS 5;
必須定在主鍵之上苞俘，如果沒(méi)有主鍵定義在唯一鍵列上盹沈，同理不允許為 NULL；
分區(qū)列必須是正整數(shù)int類(lèi)型
列表分區(qū)
當(dāng)需要明確控制如何將數(shù)據(jù)進(jìn)行分區(qū)時(shí),采用這種方式吃谣。
只能進(jìn)行單列分區(qū),可以講數(shù)據(jù)進(jìn)行分組,比如按城市分區(qū),幾個(gè)城市放一起乞封。
創(chuàng)建列表分區(qū)
CREATE TABLE list_tab(
c1 int default NULL,
c2 varchar (30) default NULL,
c3 date default Null
)Engine=INNODB
partition by LIST(YEAR(c3))(
PARTITION PO VALUES IN (1999,2000,2001),
PARTITION p1 VALUES IN (2002,2003),
PARTITION p2 VALUES IN (1995,1996,1997),
PARTITION p3 VALUES IN (1998,2004)
);
需要注意: 年份要寫(xiě)全否則報(bào)錯(cuò),如果添加了分區(qū)外的年份也會(huì)報(bào)錯(cuò),沒(méi)有像范圍分區(qū)那樣默認(rèn)分區(qū)
復(fù)合分區(qū)
側(cè)重于數(shù)據(jù)歸檔了,將上述三個(gè)組合起來(lái)用。根據(jù)業(yè)務(wù)需求的數(shù)據(jù)分布來(lái)了選擇合適的組合岗憋。
CREATE TABLE fuhe_tab ( id INT , birth_day DATE)
PARTITION BY RANGE (YEAR(birth_day))
SUBPARTITION BY HASH(TO_DAYS(birth_day))
(
PARTITION P0 VALUES LESS THAN (1990)
(
SUBPARTITION S0,
SUBPARTITION S1
),
PARTITION p1 VALUES LESS THAN (2000)
(
SUBPARTITION s2,
SUBPARTITION S3
),
PARTITION p2 VALUES LESS THAN MAXVALUE
(
SUBPARTITION s4,
SUBPARTITION S5
)
);
Subpartitioning 分區(qū)肃晚；（子分區(qū)）
刪除分區(qū)表
ALTER TABLE part_tab DROP PARTITION p0;
