分区表
概念
表分区就是将一个大表按照mysq提供的几种方式,分成几个小表。
日常开发中我们经常会遇到大表的情況,所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大,导致数据库在査询和插入的时候耗时太长、性能低下,
如果涉及联合查询的情况,性能会更加糟糕。对表进行分区,目的就是减少数据库的负担,提高数据库的效率,通常来讲就是提高表的增删改查效率。
分区是将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器。
分区后,表面上还是一张表,但数据散列到多个位置了。应用程序读写的时候操作的还是大表名字,数据库系统自动去组织分区的数据。
与单个磁盘或文件系统分区相比,可以存储更多的数据。
很容易就能删除不用或者过时的数据。
些查询可以得到极大的优化。
涉及到 SUMO/COUNTO等聚合函数时,可以并行进行。
IO吞吐量更大。分区允许可以设置为任意大小的规则,跨文件系统分配单个表的多个部分。
实际上,表的不同部分在不同的位置被存储为单独的表。
show plugins; 查看分区表 partition:ACTIVE
范围分区
通常是使用频率最高的分区,如按月份划分,这样的数据保持均匀性比较好,如果划分的均匀性不是很好,需要考虑其他分区方法。
例如,可以将一个表通过年份划分成两个分区,2001-2010年、2011-2020。
创建分区表
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分区 , MAXVALUE 其他数字走p11分区
创建普通表
CREATE TABLE no_part_tab(
c1 int default NULL,
c2 varchar (30) default NULL,
c3 date default Null
)Engine=INNODB;
查看分区表
SELECT
PARTITION_name,
PARTITION_expression,
table_rows
FROM
information_schema.PARTITIONS
WHERE
table_schema = SCHEMA ()
AND table_name = 'part_tab';
创建函数插入数据
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 //
# 两个表都插入数据
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';
哈希分区
如果数据不是那么容易进行划分,通过这种方式就很灵活了。
可以将数据均匀的插入到不同的块,在并发时有利于提高效率,当无法用 Range分区时,就可以用Hash分区。
创建哈希分区
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;
必须定在主键之上，如果没有主键定义在唯一键列上，同理不允许为 NULL；
分区列必须是正整数int类型
列表分区
当需要明确控制如何将数据进行分区时,采用这种方式。
只能进行单列分区,可以讲数据进行分组,比如按城市分区,几个城市放一起。
创建列表分区
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)
);
需要注意: 年份要写全否则报错,如果添加了分区外的年份也会报错,没有像范围分区那样默认分区
复合分区
侧重于数据归档了,将上述三个组合起来用。根据业务需求的数据分布来了选择合适的组合。
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 分区；（子分区）
删除分区表
ALTER TABLE part_tab DROP PARTITION p0;
