文章目录
- MySQL分区表的应用
- 1 背景
- 2 功能设计
- 2.1 存储功能设计
- 2.2 管理功能设计
- 2.2.1 分区表
- 2.2.1.1 分区的类型
- 2.2.1.2 分区列的选择限制
- 2.2.1.3 COLUMNS分区
- 2.2.1.4 性能
- 2.2.1.5 优点
- 2.2.2 任务调度
MySQL分区表的应用
1 背景
设计和开发一个客户端日志接收服务，接收来自客户端的日志文件（每日十万级别）的上传请求，并将日志文件落库进行存储和管理。
日志主要作用是，提供给客户端开发人员对近期用户反馈的问题进行分析，限定存储时间范围为最近3个月。
本方案不分析接收和处理日志文件相关问题，只讨论日志文件的存储和管理问题。
2 功能设计
将需求拆分为两个子需求：存储功能设计（数据表设计）、管理功能设计（定期清理历史数据）
2.1 存储功能设计
将数据表内容分为两个部分：
- 日志文件
- 日志文件的二进制数据并不需要存入数据库中，而是将其上传到对象存储，数据库中只存放日志文件的下载地址
- 日志信息数据
- 主要用于检索的内容，如日期、用户信息、应用版本等
这里不讨论如何建立索引和优化。
2.2 管理功能设计
主要提供日志数据的定期清理功能，经过调研选用分区表
2.2.1 分区表
MySQL在5.1版本增加了对分区的支持，分区表能够将逻辑上的单表在物理上分解为多个分区进行管理。
因为该功能并非在存储引擎层完成的，所以常见的存储引擎都支持（InnoDB、MyISAM）。
在低版本中一张表只允许最多1024个分区，5.6版本支持最多创建8192个分区。
2.2.1.1 分区的类型
- RANGE分区：行数据基于选定列的列值范围，放入每个范围对应的分区中。5.5版本支持RANGE COLUMNS分区。
- LIST分区：与Range分区类似，区别是List分区的列值是离散的值。5.5版本支持LIST COLUMNS分区。
- HASH分区：根据用户自定义的表达式的返回值（非负）进行分区。
- KEY分区：根据MySQL提供的哈希函数来进行分区。
2.2.1.2 分区列的选择限制
分区列的指定存在如下限制：
- 表中存在主键：分区列必须要包含主键
- 表中存在唯一索引：分区列必须要是唯一索引的一个组成部分
- 表中存在主键和唯一索引：分区列必须要是主键与唯一索引的一个组成部分
当表中不存在主键和唯一索引时，便可以选择表中的任意列作为分区列。
2.2.1.3 COLUMNS分区
未支持COLUMNS之前，四种类型的分区只能选择整型类型的列作为其分区列，对于非整型的数据不得不使用MySQL的内置函数，如YEAR()、TO_DAYS()、MONTH()等，将其转化为整型，这无疑降低了分区表的整体性能。
MySQL5.5版本针对最常用的RANGE和LIST分区类型增加了RANGE COLUMNS、LIST LIST分区支持，它可以直接使用非整数类型的列进行分区，并且不需要转化为整型进行比较。
COLUMNS分区支持以下类型：
- 整数类型：INT、SMALLINT、TINYINT、BIGINT
- 日期类型：DATE、DATETIME
- 字符串类型：CHAR、VARCHAR、BINARY、VARBINARY
2.2.1.4 性能
如果分区表使用不当，不仅不会提高查询性能，还会使查询性能成倍下降。
假设表A未分区，其索引生成的B+树层高为3；将表A做10个HASH分区后，由于数据量减小，每个分区的索引的B+层高为2。
那么当我们的查询只会搜索1个分区时，查询性能相较分区前是有不错的提升（最多3次I/O变为最多2次），但搜索超过1个分区时，则查询性能会更差。由于数据表已经在物理上被划分为多个分区，每个分区上的数据和索引是独立的，当我们搜索的分区越多，则每多遍历一个分区，都会产生不小的I/O开销会。
所以使用分区表是否能够提高查询性能，这取决于查询SQL是否符合分区的特性，即尽可能的避免搜索额外的分区。
2.2.1.5 优点
- 方便表管理：使用分区表的好处是，方便表的管理，当我们要删除表中某一天的日志文件记录时，不需要执行DELETE，而是执行alter table log drop partition p20210701删除对应分区即可。
- 加快某些查询的性能：根据分区的特性编写SQL语句，可以避免扫描所有分区，能够大大提高执行的速度。
2.2.2 任务调度
使用Elastic-Job每天定时调度任务，创建明天即将写入的分区，以及删除需要清理的分区。
