MySQL 分区表探索

zyxcba 的个人博客 / 2571 / 0 / 创建于 5年前 / 更新于 4年前

背景

如果需要定时清理一张普通大表里的历史数据。

可以使用一个或多个带 where 条件的 delete 语句去删除（where条件是时间）。如果表数据量较大，这对数据库的造成了很大压力。即使我们把这些旧数据删除了，但是底层的数据文件并没有变小。

为什么没有变小？
当删除数据时，MYSQL 并不会立即回收表空间。被已删除数据的占据的存储空间，以及索引位会空在那里，等待新的数据来弥补这个空缺。
强行回收： OPTIMIZE TABLE

面对这类问题，其实最有效的方法就是在使用分区表。分区表最大的优点就是可以非常高效的进行历史数据的清理。

关于分区表

分区表不是在存储引擎层完成的。这是 MySQL 支持的功能（5.1开始）

原理：
将表索引分解为多个更小、更可管理的部分。

从逻辑上讲，只有一个表或者索引，但是物理上这个表或者索引可能由数十个物理分区组成。

分区表最大的优点就是可以非常高效的进行历史数据的清理。

每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理（如果分区表很大，亦可以将分区分配到不同的磁盘上去）。

在执行查询的时候，优化器会根据分区定义过滤哪些没有我们需要数据的分区，这样查询就无须全表扫描所有分区，只查找包含需要数据的分区即可。

检查分区功能是否启用

mysql> SHOW PLUGINS \G;

*************************** 43. row ***************************
   Name: partition
 Status: ACTIVE
   Type: STORAGE ENGINE
Library: NULL
License: GPL

分区类型

MySQL目前只支持水平分区（水平分区就是将不同的行分配到不同的物理文件中）。

范围分区（RANGE）
行数据基于一个给定的连续区间的值被放入分区。
列表分区（LIST）
和 RANGE 分区类似，只不过面向的是离散的值。
哈希分区（HASH）
根据用户自定义的表达式返回的值来区分放入那个分区。
KEY分区
根据 MySQL 数据库提供的哈希函数来进行分区。
COLUMNS 分区
可以对多个列的值进行分区。（MySQL 5.5+ 开始支持）。

RANGE 分区

这是最常用的一种分区类型。最常见的是基于时间字段（基于分区的列最好是整型）来分区。
分区的列可以允许 null 值，如果分区的列值是 null，则会选择第一个分区。

CREATE TABLE range_partition_test (
    id INT,
    pdate INT
)
PARTITION BY RANGE (pdate) (
    PARTITION p1 VALUES LESS THAN ( 201702 ),
    PARTITION p2 VALUES LESS THAN ( 201703 ),
    PARTITION p3 VALUES LESS THAN ( 201704 ),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

MAXVALUE 是一个无穷大的值，所以p4 分区即为默认的分区。

在执行查询的时候，带上分区字段，这样可以使用分区剪裁功能。

mysql> select * from range_partition_test;
+------+--------+
| id   | pdate  |
+------+--------+
|    1 | 201701 |
|    2 | 201702 |
|    3 | 201703 |
|    4 | 201704 |
|    5 | 201705 |
+------+--------+

mysql> explain partitions select * from range_partition_test where pdate between 201702 and 201703;
+----+-------------+----------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table                | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | range_partition_test | p2,p3      | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+----------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

只查询了p2,p3分区。

LIST 分区

LIST 分区和 RANGE 分区类似。
区别在于 LIST 是枚举值列表的集合，RANGE 是连续的区间值的集合。二者在语法方面非常的相似。
建议 LIST 分区列是非 null 列，否则插入 null 值如果枚举列表里面不存在 null 值会插入失败（和 RANGE 分区不一样）。

CREATE TABLE list_partition_test (
    id INT,
    pdate INT
)
PARTITION BY LIST (pdate) (
    PARTITION p1 VALUES IN (1,3,5,7,9),
    PARTITION p2 VALUES IN (2,4,6,8,0)
);

Hash 分区

HASH 分区的目的是讲数据均匀的分不到预先定义的各个分区中。保证各个分区的记录数量大体上都是一致的。

在实际工作中经常遇到像会员表的这种表。并没有明显可以分区的特征字段。但表数据有非常庞大。这时候可以使用 HASH 分区。

基于给定的分区个数，将数据分配到不同的分区，HASH分区只能针对整数进行 HASH。

CREATE TABLE hash_partition_test (
    id INT,
    pdate INT
)
PARTITION BY HASH(id)
PARTITIONS 4;

上面的分区对象(id)也可以是一个表达式，表达式的结果必须是整数值。
HASH 分区可以不用指定 PARTITIONS 子句，则默认分区数为1。
不允许只写 PARTITIONS，而不指定分区数。
HASH 分区的底层实现其实是基于 MOD 函数。

KEY 分区

KEY 分区和 HASH 分区相似。不同之处在于：

KEY 分区允许多列，而 HASH 分区只允许一列。
如果在有主键或者唯一键的情况下，key 中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。
KEY 分区对象必须为列，而不能是基于列的表达式。
KEY 分区和 HASH 分区的算法不一样，对于 innodb 引擎，采用的是 MD5 值来分区。

COLUMNS 分区

可以直接使用非整型的数据进行分区。分区根据类型直接比较而得，不需要转化为整型。同时，可以对多个列值进行分区。

CREATE TABLE listvardou (
    id INT,
    pdate INT
)
PARTITION BY LIST COLUMNS(id,pdate)
(
    PARTITION a VALUES IN ( (1, 201701), (1, 201702), (1, 201703)),
    PARTITION b VALUES IN ( (2, 201702) )
    PARTITION b VALUES IN ( (3, 201703) )
);