MySQL分区表:入门指南

发表时间: 2022-01-18 11:33

一、背景

我们的业务只存近一段时间的数据,因此有大量表需要清理历史数据,目前使用的delete清理数据,存在以下问题。为避免同时支持大量delete,我们的清理任务只在低峰期串行执行,导致任务过多时需要排队,甚至失败的情况;数据清理使用delete语句,表数据量较大时,对数据库造成很大压力;即使我们删除了旧数据,已删除的数据仍占据存储空间,底层数据文件并没有立刻变小,以至于形成数据空洞。

查看MySQL官方文档时,发现了分区表,因此基于官方文档总结一下。

二、什么是分区表?

MySQL逻辑上为一个表,物理上存储在多个文件中,这是 MySQL 支持的功能(5.1 开始), 8.0 版本只 InnoDB 和 NDB 支持分区表。

三、分区表有啥优缺点?

优点:

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

缺点:

  • 新建、删除分区操作的时候,会获取表的MDL锁,这是一把全局锁,可能会对业务产生慢查的影响;
  • 分区表在执行alter语句时,比如增加、删除分区,更容易出现MDL锁冲突,引起慢查等情况;
  • InnoDB分区表不支持外键

四、分区类型

4.1 Range 分区

根据分区表键值的范围把数据存储到表的不同分区中,适用于以时间或日期作为分区类型,方便数据清理。

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)   ); -- 使用函数将日期转换 CREATE TABLE employees_1 (       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 (year(hired)) (         PARTITION p0 VALUES LESS THAN (6),         PARTITION p1 VALUES LESS THAN (11),         PARTITION p2 VALUES LESS THAN (16),         PARTITION p3 VALUES LESS THAN (21) ); -- 使用date 或 datetime 例作为分区例时 可使用 RANGE COLUMNS 对表进行分区 CREATE TABLE employees_2 (       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 COLUMNS (hired) (         PARTITION p0 VALUES LESS THAN ('1970-01-01'),         PARTITION p1 VALUES LESS THAN ('1971-01-01'),         PARTITION p2 VALUES LESS THAN ('1972-01-01'),         PARTITION p3 VALUES LESS THAN ('1973-01-01')   );

小提示:

1.当插入数据分区不存在时会报错:Table has no partition for value xxx;

2.Range类型分区字段必须是数值,时间类型可用函数转换为数值;

3.分区字段列值可以为null,所有为null的数据将存在最小的分区中;

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

小提示:

  1. List分区表不存在 MAXVALUE 之类的,所以在设计时一定要全面评估,否则分区不存在时写入会报错;
  2. 可以显示指定一个null分区,eg: pxx values in (null);
  3. 如果没有指定null分区时,分区字段值为null会报错:Table has no partition for value NULL;

4.3 Hash分区

根据指定分区表达式的整数值以及分区数进行数据划分(mod函数)

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

小提示:

  1. 当分区表达式输出为null时,被当做0处理;

4.4 Key分区

按键分区类似于按哈希分区,只是哈希分区使用用户定义的表达式,用于键分区的哈希函数由 MySQL 服务器提供。NDB 集群为此使用 MD5() ; 对于使用其他存储引擎的表,服务器使用自己的内部哈希函数。

CREATE TABLE k1 (       id INT NOT NULL,       name VARCHAR(20),       UNIQUE KEY (id) )   PARTITION BY KEY() PARTITIONS 2;

小提示:

  1. 分区字段不填写时,可以不填写分区字段,优先使用主键作为分区字段,其次是唯一键;没有主键或唯一键时,分区字段必填
  2. 分区字段可以是非整数或null值;
  3. 当分区表达式输出为null时,被当做0处理;

4.3 子分区

子分区(subpartitioning)也称为复合分区(composite partitioning) ,是已分区表中每个分区的进一步划分

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

小提示:

  1. 每个分区必须具有相同数量的子分区;

五、分区管理

5.1 Range 和 List

-- 增加分区 alter table employees_list add partition (partition pxx values in (null));  ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21)); -- 删除分区 ALTER TABLE employees_list DROP PARTITION pxx; -- 将一个分区分为多个分区 ALTER TABLE employees_3  REORGANIZE PARTITION employees_list INTO (           PARTITION n0 VALUES LESS THAN (1970), PARTITION n1 VALUES LESS THAN (1980) ); -- 清空分区ALTER TABLE employees_3 TRUNCATE PARTITION pxx; 

5.2 Hash 和 Key分区

-- 缩减n个分区 ALTER TABLE employees_hash COALESCE PARTITION 4; -- 增加n个分区 ALTER TABLE employees_hash ADD PARTITION PARTITIONS 6;

小提示:

  1. 使用COALESCE 减少分区数时,不能减少至0;

六、指定分区查询

-- 可以指定分区号或以分区字段作为条件 select * from employees_hired PARTITION (p2021) where store_id = 551 and hired >= '2020-12-31' and hired <= '2021-01-01'

七、分区表注意事项

  1. PRIMARY KEY、UNIQUE KEY 必须包含分区字段;
  2. 分区表达式支持函数,但不是所有函数都支持,存储过程、存储函数、可加载函数和插件都不支持,算术运算符支持 +、- 、* 、div,不支持"/",位运算也不支持;
  3. MySQL 8.0 仅InnoDB和NDB支持分区表,其他engine不支持;
  4. range 分区中,null 值会被当作最小值来处理;
  5. list 分区中,null 值必须出现在枚举列表中,否则不被接受;
  6. hash/key 分区中,null 值会被当作零值来处理。