mysql分区

分区

分区有利于管理非常大的表，采用“分而治之”的逻辑，分区引入了分区键的概念。分区键用于根据某个分区、特定值列表或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象变成一些小对象。

分区优点

• 和单个磁盘或者文件系统分区相比，可以存储更多数据
• 优化查询。where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；同时在涉及聚合函数查询时，可以容易地在每个分区上并行处理，最终汇总所有分区得到的结果。
• 对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据
• 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。

查看mysql是否支持分区

show variables like '%partition%';

如果变量have_partition_engine的值为YES，那么mysql的版本就支持分区。MyISAM, InnoDB,Memory都是支持分区的，merge，csv不支持。

分区类型

• range分区：基于一个给定连续区间范围，把数据分配到不同的分区
• list分区：类似range分区，区别在list分区是基于枚举出的值列表分区
• hash分区：基于给定的分区个数，把数据分配到不同的分区
• key分区：类似于hash分区

range分区，list分区，hash分区都要求分区键必须是int类型，但是key分区不要求，可以为其他类型。无论哪种分区类型，要么没有主键或者唯一键，要么主键或者唯一键含有分区键。

range分区

mysql5.5改进了range分区，提供了range columns分区支持非整数分区。

range分区功能特别适用下面两种情况

• 当需要删除过期的数据时，只需要简单的alter table emp drop partition p0来删除p0分区中的数据。对于具有上百万条记录的表来说，删除分区要比运行一个delete语句有效的多。
• 经常运行包含分区键的查询

示例

create table emp(    id int not null,    ename varchar(30),    hired date not null default '1970-01-01',    job varchar(30) not null,    store_id int not null)partition by range(store_id)(    partition p0 values less than (10),    partition p1 values less than (20),    partition p2 values less than (30));

list分区

示例

create table expenses(    expense_date date not null,    category int,    amount decimal (10, 3))partition by list(category)(    partition p0 values in (3, 5),    partition p1 values in (1, 10),    partition p2 values in (4, 9),    partition p3 values in (2),    partition p4 values in (6),);

columns分区

• range columns
• list columns

特点：支持非整数分区，支持多列分区.

• 所有的整数类型：tinyint, smallint, mediumint, int和bigint; 其他数值类型都不支持
• 日期时间类型：date和datetime
• 字符类型：char,varchar, binary和varbinary

hash分区

示例

create table emp(    id int not null,    ename varchar(30),    hired date not null default '1970-01-01',    job varchar(30) not null,    store_id int not null)partition by hash(store_id) partitions 4;

    每次更新数据都要计算一次哈希，非常复杂的表达式会引起性能问题，不推荐涉及多列的哈希表达式。hash分区看上去将数据平均分布在各个分区，每个分区管理数据减少了，提高了查询效率，但是增加分区或者合并分区的时候，问题就出现了。比如原先5个分区，于是数据都是对5取模，现在变成6个分区，那么就要对6取模，这样所有的数据基本都要重新分配，这样会出现哈希性能问题。

    为了降低分区管理代价，mysql提供了线性hash分区，分区函数式一个线性的2的幂的运算法则。

线性hash分区

记录将要保存到的分区是num个分区中的分区N，其中N是根据下面的算法得到：

找到下一个大于num的2的幂，我们把这个值称为V ，它可以通过下面的公式得到：

V = POWER(2, CEILING(LOG(2, num)))

（例如，假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4，则V = POWER(2,4), 即等于16）。

设置 N = F(column_list) & (V - 1)当 N >= num:    设置 V = CEIL(V / 2)     设置 N = N & (V - 1) 否则：    N不变

线性hash分区的优点：

• 在分区维护(包含增加，删除，合并，拆分分区)时，mysql能够处理得更加迅速

缺点

• 对比常规hash分区的时候，线性hash各个分区之间数据的分布不太均衡

key分区

示例

create table emp(    id int not null,    ename varchar(30),    hired date not null default '1970-01-01',    job varchar(30) not null,    store_id int not null)partition by key(job) partitions 4;

线性key分区与线性hash分区类似

子分区

分区表中对每个分区的再次分割，又称之为复合分区。

分区对null处理

• range分区，null值会被当做是最小的值来处理
• list分区，null值必须出现在枚举列表中，否则不被接受
• hash/key分区，null当作0来处理

分区管理

删除分区

• range分区：直接删，几乎没有影响
• list分区：直接删，但是list分区中不再包含被删除的分区中含有的值的列表，后续无法写入包含有已经被删除了的分区的值列表。
• hash/key分区：重新hash

添加分区

• range分区：直接添加，几乎没有影响
• list分区：直接添加，几乎没有影响
• hash/key分区：重新hash

拆分分区

• range分区：reorganize partition重新分，几乎影没有影响
• list分区：reorganize partition重新分，几乎没有影响

合并分区

• range分区：reorganize partition重新分，几乎没有影响
• list分区：reorganize partition重新分，需要将涉及到的连续分区都重新分