一、背景
我们的业务只存近一段时间的数据，因此有大量表需要清理历史数据，目前使用的delete清理数据，存在以下问题。为避免同时支持大量delete，我们的清理任务只在低峰期串行执行，导致任务过多时需要排队，甚至失败的情况；数据清理使用delete语句，表数据量较大时，对数据库造成很大压力；即使我们删除了旧数据，已删除的数据仍占据存储空间，底层数据文件并没有立刻变小，以至于形成数据空洞。
查看MySQL官方文档时，发现了分区表，因此基于官方文档总结一下。
二、什么是分区表？
MySQL逻辑上为一个表，物理上存储在多个文件中，这是 MySQL 支持的功能（5.1 开始）, 8.0 版本只 InnoDB 和 NDB 支持分区表。
三、分区表有啥优缺点？
优点：
和单个磁盘或文件系统分区相比，可以存储更多的数据。
优化查询：

where 子句中包含分区条件时，可以直接扫描必要的分区。

【MySQL分区表】涉及聚合函数的查询时，可以很容易地在每个分区上并行处理，最终只需汇总得到结果。
对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据。
跨多个磁盘来分散数据查询，以获得更大的查询吞吐量。
缺点：
新建、删除分区操作的时候，会获取表的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_3REORGANIZE 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 值会被当作零值来处理。

推荐阅读

• 

  南宁：房贷利率新政实施后买房能省多少钱？
• 

  drive|希捷推出 Xbox 专用外置 SSD：1TB 约 1095元，USB 3.2 Gen1 接口
• 

  农户贷款如何界定 「一般农户指什么」
• 

  java用什么软件写代码，JAVA用什么软件编写
• 

  不要吃发物 不要吃过甜过咸和酸性的食物
• 

  八角金盘的介绍-八角金盘的作用
• 

  flutter添加横线，flutter虚线边框
• 

  为什么戒烟后会发胖？
• 

  佛珠多肉烂根怎么办 佛珠多肉烂了怎么办
• 

  中国知名菜刀有什么牌子？中国十大菜刀品牌排行
• 

  从入门到精通，轻松应对各种错误 打印机1700代码解析
• 

  水果礼盒制作步骤大全图解 水果礼盒的制作技巧是怎么样的
• 

  支付宝花呗当面花怎么使用？使用方法及店铺详细介绍
• 

  恒为科技的军方服务器具有怎样的性能？ 恒为科技军方服务器怎么样
• 

  gis中画的图成无效的书签，gis图没了
• 

  工商银行主要业务有哪些
• 

  iphone皮套
• 

  爱普生1430喷墨机清零
• 

  关于vb.neteval的信息
• 

  非诚勿扰影视产业分析

• mysql|InnoDB数据页结构
• javaweb|基于Servlet+jsp+mysql开发javaWeb学生成绩管理系统
• mysql|一文深入理解mysql
• Java毕业设计项目实战篇|Java项目:在线嘿嘿网盘系统设计和实现(java+Springboot+ssm+mysql+maven)
• SQL|SQL基本功（五）--函数、谓词、CASE表达式
• vue|电商后台管理系统(vue+python|node.js)
• Java及基础算法及数据结构|旧笔记整理（MySQL）
• mysql|双非本211硕，无实习无项目，自学大数据开发，秋招上岸
• 数据库|Mysql--InnoDB存储引擎详解
• MySQL学习笔记-9-order by