MySQL 数据库分区是一个很重要的特性,特别是在表数据量比较大的情况下,常见的设计是分实例 进行读写分离,分库进行数据隔离,分区是针对单表大数据来设计的,分表和分区的区别是,一个侧重于业务层面的处理(分表),一个侧重更底层的逻辑存储(分区)。

在日常的工作中,我们经常遇到一张表里面保存了上亿甚至过十亿的记录。这些表里面保存了大量的历史记录。 对于这些历史数据的清理是一个非常头疼事情,由于所有的数据都一个普通的表里。所以只能是启用一个或多 个带 where 条件的 delete 语句去删除(一般 where 条件是时间)。这对数据库的造成了很大压力。即 使我们把这些删除了,但底层的数据文件并没有变小。面对这类问题,最有效的方法就是在使用分区表。最常 见的分区方法就是按照时间进行分区。分区一个最大的优点就是可以非常高效的进行历史数据的清理。

检查分区功能是否支持

本文是在 MySQL 5.6 的基础上进行学习的。

通过下面的命令可以检查当前数据库是否支持分区特性:

SHOW PLUGINS;

注意查看输出中 partition 是不是已经 ACTIVE 了。

如果已经进行了分区功能,可以通过 --skip-partition 选项来启动 MySQL 实例禁用分区功能。分区功能禁用后,你可以看到所有的分区表并删除他们,但是不能对表进行操作和数据访问。

分区字段选择

分区字段的选择是有限制的,总结起来就一句话:

表中的每一个 unique key 必须使用分区表达式中关联的所有列。

举个例子:

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1),
    UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

上面的两个都是错误的:

  1. 第一个表中的 UNIQUE KEY 没有使用 col3
  2. 第二个表中的第一个 UNIQUE KEY 没有包含 col3 列,第二个 UNIQUE KEY 没有包含 col1 列。

下面举一个特殊的例子:

CREATE TABLE t4 (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3),
    UNIQUE KEY (col2, col4)
);

上面的这个表没法创建分区,第一个 UNIQUE KEY 要求分区中有 col1, col3 第二个 UNIQUE KEY 要求分区中有 col2, col4,如果放在一起的话,最终分区需要包含 col1, col2, col3, col4,这个时候两个 UNIQUE KEY 都不再满足条件了。

分区类型

目前支持的分区类型有:

  • RANGE 分区
  • LIST 分区
  • COLUMNS 分区
  • HASH 分区
  • KEY 分区
  • 子分区

RANGE 分区

RANGE 分区翻译过来就是区间分区,把数据表中的数据分散在区间表达式计算的区间内。区间分区中,区间必须是连续的,不能重叠,通过操作符 VALUES LESS THAN 来定义区间。

举个例子:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
);

根据区间分区定义,按 store_id 列对表进行分区:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

上面定义的分区中,我们知道15将会落在 p2 中,而大于 21 的数字将会都落在 p4 分区中。

上面的分区相对比较简单,只是一个实例,没有特别的作用,实际上上面这种情况没有必要分区,下面举一个比较实用的例子,按照年份对数据进行分区:

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

RANGE COLUMNS 分区是 RANGE 分区的扩展,RANGE 分区只支持单列的数值型分区,但是 RANGE COLUMNS 支持多列的多种数值类型分区:

  • 所有数字类型
  • 时间类型:DATE / DATETIME
  • 字符串类型: CHAR, VARCHAR, BINARY, VARBINARY

RANGE COLUMNS 的比较方式,比较的是一个元组而不是单列的数据。

举个例子:

CREATE TABLE rc1 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
    PARTITION p0 VALUES LESS THAN (5, 12),
    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);

查询后输出如下:

+--------------+----------------+------------+
| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
+--------------+----------------+------------+
| p            | p0             |          2 |
| p            | p1             |          1 |
+--------------+----------------+------------+

LIST 分区

LIST 分区和 RANGE 分区很类似,只是 LIST 分区的时候检查是固定数组的成员关系,而不是大小关系,根据集合的成员关系进行分组,适用于某一列的数值可以枚举的情形。

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

LIST COLUMNS 分区与 RANGE COLUMNS 一样,增加了对多类型的支持,还是直接给个例子:

CREATE TABLE customers_1 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
    PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);

HASH 分区

HASH 分区通过对列计算 hash 来散列到不同的分区中,与前面的分区方式的差别是不需要显示的指定分区的名称,只需要指定分区的个数,HASH 中的表达式需要返回 Integer 类型。

举个实际的例子:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

具体的每个数据散列到那个分区是根据:

MOD(HASH(store_id), 4)

其中 4 是分区的个数。

LINEAR HASH 与 HASH 的不同之处在于线性 HASH 算法利用一个线性的 2 的幂次方,而 HASH 分区使用 HASH 函数的模量值。

线性 HASH 的计算方式:

V = POWER(2, CEILING(LOG(2, num)))
N = F(column_list) & (V - 1)
while N >= NUM:
    V = V / 2
    N = N & (V - 1)

举个实际的例子:

CREATE TABLE t1 (
    col1 INT, 
    col2 CHAR(5), 
    col3 DATE
    )
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;

现在假设插入的数据中 col3 的值是 “2003-04-14”, 计算法分区方式如下:

V = POWER(2, CEILING( LOG(2,6) )) = 8
N = YEAR('2003-04-14') & (8 - 1)
   = 2003 & 7
   = 3

(3 >= 6 is FALSE: record stored in partition #3)

KEY 分区

KEY 分区的方式与 HASH 类似,不同点是 HASH 分区需要使用表达式,而 KEY 分区不需要表达式,KEY 分区中可以不传递列值,默认使用 Primary Key 。

举个例子:

CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;

关于 KEY 分区的算法是由数据库提供的,也是计算列的一个整数,同样也有 LINEAR KEY 分区,与 HASH 分区一致。

子分区

可以针对 RANGE 和 LIST 分区进行子分区,子分区本身是一种复合分区,子分区的分区类型是 HASH 或者 KEY。

举个例子:

CREATE TABLE ts (
    id INT, 
    purchased DATE
)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

这一共创建了 6 个分区,先使用 RANGE 创建了三个分区,然后分别对三个分区进行在分区,一共是 6 个。

分区中处理 NULL 值

NULL 值的处理在不同的分区类型中是不一样的,在 RANGE 分区中,NULL 值通过表达式计算出的还是 NULL,是一个比任何非 NULL 值都小的数,所以一般都是在第一分区; LIST 分区中, NULL 值必须是某个分组的成员,不然不允许插入; HASH 和 KEY 分区中, NULL 值被当做 0 值返回,都会插入到同一个分区中。

本节(完~)

下一节再讲一下分区的管理方式。