MySQL分区表

1、分区表的基本介绍

分区是一种表的设计模式，正确的分区可以极大地提升数据库的查询效率，完成更高质量的SQL编程。但是如果错误地使用分区，那么分区可能带来毁灭性的的结果。

分区功能并不是在存储引擎层完成的，常见的存储引擎InnoDB、MyISAM、NDB等都支持分区。但是并不是所有的存储引擎都支持，如CSV、FEDORATED、MERGE等就不支持分区。在使用此分区功能前，应该对选择的存储引擎对分区的支持有所了解。

MySQL数据库在5.1版本及以上时添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

1.1、分区类型（水平分区、垂直分区）

MySQL数据库支持的分区类型为水平分区（指将同一个表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（指将同一表中不同列的记录分配到不同的物理文件中）。

此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。

1.2、表分区的优缺点

分区的好处：

1. 可以让单表存储更多的数据。
2. 分区表的数据更容易维护，可以通过清除整个分区来批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作。
3. 部分查询能够从查询条件确定只落在少数分区上，速度会很快（查询条件尽量扫描少的分区）。
4. 分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备。
5. 可以使用分区表来避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争。
6. 可以备份和恢复单个分区。

分区的限制和缺点：

• 在mysql5.6.7之前的版本，一个表最多有1024个分区；从5.6.7开始，一个表最多可以有8192个分区。
• MYSQL的分区字段，必须包含在主键字段内。如果一个表有主键，那么分区字段必须包含在主键内，也就是分区字段必须是主键的一部分或者全部，不能以非主键的字段作为分区字段。当然，也可以为没有主键的表建立分区。
• 分区表无法使用外键约束。
• NULL值会使分区过滤无效。
• 所有分区必须使用相同的存储引擎。

1.3、分表和表分区的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。

分表与分区的区别在于：分区从逻辑上来讲只有一张表（虽然在物理层面上是有多个表文件），而分表则是将一张表分解成多张表。

2、表分区的常见操作

2.1、判断是否支持分区

mysql可以通过下面语句判断是否支持分区：

SHOW VARIABLES LIKE '%partition%';

如果输出：have_partitioning   YES 则表示支持分区。

 

或者通过：

SHOW PLUGINS;

显示所有插件，如果有partition - ACTIVE - STORAGE ENGINE - GPL 插件则表明支持分区

2.2、创建分区

# 创建分区表
CREATE TABLE `tr` (
     `id` INT, 
     `name` VARCHAR(50), 
     `purchased` DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(purchased) ) (
     PARTITION p0 VALUES LESS THAN (1990),
     PARTITION p1 VALUES LESS THAN (1995),
     PARTITION p2 VALUES LESS THAN (2000),
     PARTITION p3 VALUES LESS THAN (2005),
     PARTITION p4 VALUES LESS THAN (2010),
     PARTITION p5 VALUES LESS THAN (2015)
);

上面假设以 date 字段作为分区字段。

操作分区表和操作普通表没什么差别，比如插数操作，mysql 会自动根据数据来将该数据插入对应分区中。

如下：

# 插入数据
INSERT INTO `tr` VALUES
 (1, 'desk organiser', '2003-10-15'),
 (2, 'alarm clock', '1997-11-05'),
 (3, 'chair', '2009-03-10'),
 (4, 'bookcase', '1989-01-10'),
 (5, 'exercise bike', '2014-05-09'),
 (6, 'sofa', '1987-06-05'),
 (7, 'espresso maker', '2011-11-22'),
 (8, 'aquarium', '1992-08-04'),
 (9, 'study desk', '2006-09-16'),
 (10, 'lava lamp', '1998-12-25');

2.3、增加分区

alter table tr add partition(PARTITION p6 VALUES LESS THAN (2020));

2.4、删除分区

# 删除分区
alter table tr drop partition p1;

2.5、查看分区

查询分区，各区表数据量。

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tr';

查询结果类似以下：

查看某个分区的数据：

# 查看某个分区的数据
SELECT * FROM tr PARTITION (p2);

注意，mysql5.5.41不支持对指定分区的查询，在5.6增强了分区表的分区的相关操作，其中包括支持了对指定分区的查询。

2.6、清空分区数据

# 清空某分区的数据
alter table tr truncate partition p0;

3、分区类型

3.1、RANGE分区（范围分区）

RANGE分区是最常用的一种分区类型，基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。不使用COLUMNS关键字时RANGE括号内必须为整数字段名或返回确定整数的函数。

RANG分区特点：

• 根据分区键的值的范围把不同数据存储到表的不同分区中。
• 多个分区的分区键的值的范围要连续，但是不能重叠。
• 默认情况下使用VALUES LESS THAN属性，但每个分区并不包括指定的那个值。

3.1.1、创建RANGE分区

下面创建一个以id列为区间分区表，当id小于10时，数据插入p0分区；当id大于等于10小于20时，数据插入p1分区。代码如下：

create  table  t(
  id int
)engine=innodb
partition by range(id)(
     partition  p0  values  less  than(10),
     partition  p1  values  less  than(20)
);

建立分区后，我们在插入数据时，mysql 会自动根据数据的值来将数据插插入到对应分区内。

insert into t values (9);
insert into t values (11);

插入上面数据后，查看分区：

# 查看p0分区的数据
SELECT * FROM t PARTITION (p0);

结果如下：

在对表建立分区后，插入的数据的值应该严格遵守分区的定义，当插入一个不属于任何分区的值时，MySQL数据库会直接报错。如下：

 

为了避免这种情况发生，我们可以对分区添加一个MAXVALUE值的分区，MAXVALUE可以理解为正无穷，因此所有大于等于20且小于MAXVALUE的值被放入p2分区。

alter table t add partition(partition p2 values less than maxvalue);

此时，所有 >=20 的值都会被插入到 p2 分区中。

3.2、LIST分区

LIST分区和RANGE分区类似，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择，而非连续的。

LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

LIST分区的特点：

• 按分区键取值的列表进行分区
• 同范围分区一样，各分区的列表值不能重复
• 每一行数据必须能找到对应的分区列表，否则数据插入失败

3.3、HASH分区

HASH分区的特点：

• 根据MOD(分区键，分区数)的值把数据行存储到表的不同分区中
• 数据可以平均的分布在各个分区中
• HASH分区的键值必须是一个INT类型的值，或是通过函数可以转为INT类型

4、分区字段和主键

MYSQL的分区字段，必须包含在主键字段或者唯一索引列中。如果一个表有主键或者唯一索引列，那么分区字段必须包含在主键或者唯一索引列内，也就是分区的字段必须全部都属于主键或者唯一索引列的一部分或者全部，不能以非主键且非唯一索引列的字段作为分区字段。当然，也可以为没有主键的表建立分区。

在对表进行分区时，如果分区字段没有包含在主键字段内那就会直接报错。

假设某个表主键为 id，我们想用 date 日期字段作为分区字段，此时必须得把 date 日期字段作为主键一部分才行，可以创建 (id, date) 的组合主键，否则创建分区将直接报错。