mysql分区表

目录

• 表分区
• 范围分区(Range Partition)
• 哈希分区(Hash Partition)
• 列表分区(List Partition)
• 复合分区
• 语法
  • 查看是否支持分区
  • 分区表创建
    • 范围分区
    • 哈希分区
    • 复合分区
• 举例🌰
  • 创建分区表part_tab
  • 查看分区信息

表分区

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

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

分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。应用程序读写的时候操作的还是大表名字，数据库系统自动去组织分区的数据。

MySQL5.1以后新增了表分区（Partition）的功能。

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

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

从表的方向上看，又可分为按行分区和按列分区。

---

范围分区(Range Partition)

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

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

---

哈希分区(Hash Partition)

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

---

列表分区(List Partition)

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

---

复合分区

侧重于数据归档了，将上述三个组合起来用。根据业务需求的数据分布来选择合适的组合。

语法

查看是否支持分区

show plugins;

分区表创建

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 THAN (values1),
    PARTITION p1 VALUES LESS THAN (values2),
    PARTITION p2 VALUES LESS THAN (values3),
    ...
)

哈希分区

CREATE TABLE table_name(
	...
)
PARTITION BY HASH(table_column)
PARTITIONS nums; #nums为分区个数，是一个整数值

复合分区

CREATE TABLE ts (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
        )
    );

举例🌰

创建分区表part_tab

CREATE TABLE part_tab(
	c1 int default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL
)engine=myisam
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
);

查看分区信息

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

建一个不分区的表对比测试：

create table no_part_tab(
	c1 int(11) default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL
)engine=myisam;

存储过程存入数据：

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();

#复制分区表part_tab数据到不分区表no_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';