乐趣区

关于mysql:MySQL分区表

一、背景

咱们的业务只存近一段时间的数据,因而有大量表须要清理历史数据,目前应用的 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)
);

小提示:

List 分区表不存在 MAXVALUE 之类的,所以在设计时肯定要全面评估,否则分区不存在时写入会报错;

能够显示指定一个 null 分区,eg: pxx values in (null);

如果没有指定 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;

小提示:

当分区表达式输入为 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;

小提示:

分区字段不填写时,能够不填写分区字段,优先应用主键作为分区字段,其次是惟一键;没有主键或惟一键时,分区字段必填

分区字段能够是非整数或 null 值;

当分区表达式输入为 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    
  );

小提示:

每个分区必须具备雷同数量的子分区;

五、分区治理

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;

小提示:

应用 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 值会被当作零值来解决。
退出移动版