mysql表分区

综述

概念：表分区是将一个大表按照mysql提供的几种方式，分成几个小表。

大表是存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长、性能低下。如果涉及联合查询的情况，性能会更加糟糕。对表进行分区，目的就是减少数据库的负担，提高数据库的效率，通常来讲就是提高表的增删改查效率。

分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。

mysql5.1以后新增了表分区(Partition)的功能。
与单个磁盘或文件系统分区相比，可以存储更多的数据。
很容易就能删除不用或者过时的数据。
一些查询可以得到极大的优化。
涉及到SUM()/COUNT()等聚合函数时，可以并行进行计算。
IO吞吐量更大。
分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独表。

如果通过有效的隔离，全表扫描就变成了分区扫描，降低IO，但如果已经使用索引，且结果集较小时，做分区不一定会提高效率。

表分区分类

从表的方向上分：有按行分区、按列分区。

范围分区(Range Partition)

通常使用频率最高的分区类型，如按月份划分，这样的数据保持均匀性比较好，如果划分的均匀性不是很好，需要考虑其他分区方法。

例如：可以将一个表通过年份划分成两个分区：2001-2010年、2011-2020年。

哈希分区(Hash Partition)

如果数据不是那么容易进行划分，通过这种方式就很灵活了。可以将数据均匀的插入到不同的块，在并发时有利于提高效率，当无法使用Range分区时，就可以用Hash分区。

列表分区(List Partition)

当需要明确控制如何将数据进行分区时，采用这种方式。只能进行单列分区，可以将数据进行分组，比如按城市分区，几个城市放在一起。

复合分区

侧重于数据归档，将范围分区、哈希分区、列表分区三个组合起来使用。根据业务需求的数据分布来选择合适的组合。

表分区试验

-- 查看当前mysql版本
SELECT VERSION();

查看mysql5.6以下是否支持分区：

show variables like '%partition%';

-- 即可查看mysql是否支持分区，显示empty则表示不支持分区。

查看mysql5.6以上是否支持分区：

show plugins;

-- 在方框处可以看到partition ACTIVE，表示当前mysql支持分区。

试验：

创建分区表part_goods

create table part_goods(
	id int(11) default null,
	name varchar(100) default null,
	create_time date date null
) engine=myisam
partition by range(year(create_time))(
partition p1 values less then (2001),
partition p1 values less then (2002),
partition p1 values less then (2003),
partition p1 values less then (2004),
partition p1 values less then (2005),
partition p1 values less then (2006),
partition p1 values less then (2007),
)

查看分区信息

select partition_name,
partition_expression,
partition_description,
table_rows
from information_schema.partitions where table_schema=schema() and table_name='part_goods';

创建一个非分区表no_part_goods

create table no_part_goods(
	id int(11) default null,
	name varchar(100) default null,
	create_time date date null
) engine=myisam;

导入数据到part_goods

delimiter //
create procedure load_part_goods()
begin
	declare v int default 0;
	while v < 1000000 do
		insert into part_goods values(v,'good_name',adddate('1995-01-01',(rand(v)*36520 mod 3652)));
		set v = v + 1;
	end while;
end //

call load_part_goods();

导入数据到no_part_goods

insert into no_part_goods select * from part_goods;

测试语句：

select count(*) from part_goods where create_time > date '1990-01-01' and create_time < date '1995-12-31';

select count(*) from no_part_goods where create_time > date '1990-01-01' and create_time < date '1995-12-31';

---

分区表创建

create table table_name(
	...
) partition by range|list|hash(table_column)(
	partition p0 ...
);

范围分区

create table table_name(
	...
) partition by range(table_column)(
	partition p0 values less then (value0),
	partition p1 values less then (value1),
	partition p2 values less then (value2),
	...
);

列表分区

create table table_name(
	...
) partition by list(table_column)(
	partition p0 values in (0,5),
	partition p0 values in (1,6),
	partition p0 values in (2,7),
	...
);

哈希分区

create table table_name(
	...
) partition by hash(table_column) partitions nums;

nums为分区个数，是一个正整数。

复合分区

create table table_name(
	id int,
	birthday date
) 
partition by range(year(birthday))
subpartition by hash(to_days(birthday))(
	partition p0 values less then (2000)(
		subpartition s0,
		subpartition s1
	),
	partition p1 values less then (2010)(
		subpartition s2,
		subpartition s3
	),
	partition p2 values less then maxvalue(
		subpartition s4,
		subpartition s5
	)
);