背景
目前系统中有部分日志表，每天增量接近百万条，这导致了几个问题：
- 占用过多主库的存储
- 影响整体查询效率
由于该部分数据是日志数据，核心我们只要保留近几天的日志即可，其他日志数据需要做归档备份。所以我们需要将近几天之前的数据导出到备份表，然后上传归档存储，然后删除该表中的已备份数据。
P.S: 这里说明下，确实直接按天分表，然后处理按照表进行备份和 drop更合理。但是由于系统的种种限制，这里选择了上述流程。
在使用上述方式时，遇到了一个核心问题，就是删除历史数据时，导致产生了不可忍受的慢SQL，嫉妒影响性能。
基于上面这个场景，才考虑引入 MySQL Partition，按照时间创建分区，然后根据分区进行数据删除的场景，解决性能问题。
先说直接删除和引入 Partition后删除的性能对比结果：
- 在本地测试删除800w条数据时耗时140s+(按照ID 范围删除)
- 数据按天进行分区，直接 drop指定partition，本地测试为0.002s左右
下面，开始聊聊本次引入partition遇到的一些坑吧！
各种坑
一、确认你是用的MySQL支持分区
SHOW PLUGINS
-- 查看 partition 的 status = ACTIVE
二、由于需要按照日期创建分期，使用字段 created_at 按照指定日期创建分区:
创建分区命令：
-- 添加分区
ALTER TABLE partition_test
PARTITION BY RANGE(TO_DAYS(created_at)) (
PARTITION p20230809 VALUES LESS THAN (TO_DAYS('2023-08-10'))
);
遇到如下报错：
1486 – Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed, Time: 0.042000s
原因：
created_at 是一个时间戳列，而 TO_DAYS() 函数不起作用，需要使用 UNIX_TIMESTAMP() 函数来获得时间戳的秒数，然后以秒数为基准进行分区。
以下是一个示例代码，展示如何使用 UNIX_TIMESTAMP() 函数进行分区：
-- 添加分区
ALTER TABLE partition_test
PARTITION BY RANGE(UNIX_TIMESTAMP(created_at)) (
PARTITION p20230101 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-02')),
PARTITION p20230102 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-03'))
);
三、使用UNIX_TIMESTAMP，仍然报错，具体如下：
1171 – All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead, Time: 0.003000s
原因：
created_at作为分区列，需要是非空。
解决方案： 调整created_at为 Not null，同时设置default值
ALTER TABLE partition_test_new
MODIFY COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `sex`;
四、继续执行，还是报错：
1503 – A PRIMARY KEY must include all columns in the table’s partitioning function, Time: 0.025000s
原因：
这个错误表示，在使用分区函数进行分区时，主键必须包含所有参与分区的列。
解决方案：
需要先添加 created_at 为联合主键。
ALTER TABLE `partition_test`
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `created_at`) USING BTREE;
五、完成上述调整后，继续执行，仍出现报错：
1526 – Table has no partition for value 1691597196, Time: 0.045000s
原因：
对应的数据无法找到分区，需要创建对应分区。由于创建分区时，包括了如下分区：
PARTITION p20230101 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-02')),
PARTITION p20230102 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-03'))
实际数据中， 存在 created_at > 2023-01-03 的数据，导致该部分数据无法找到分区。因此需要创建包括所有数据的分区
六、想要新增一个partition
新增命令如下：
ALTER TABLE partition_test
ADD PARTITION (
PARTITION p20220101 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-02'))
);
报错：
1493 – VALUES LESS THAN value must be strictly increasing for each partition, Time: 0.003000s
原因：
在重新组织分区时，新分区的划分值必须严格大于旧分区的划分值。
需要确保在创建新分区时，新分区的划分值（VALUES LESS THAN）是严格大于原始分区划分值的。例如，如果原始分区的划分值是 ‘2023-01-03’，那么新分区的划分值应该是更大的日期，比如 ‘2023-01-04’。这是保证分区按顺序排列的关键。
七、对于没有创建分区的表，需要创建分区时
不能使用命令：
ALTER TABLE table_name
ADD PARTITION (
PARTITION partition_name VALUES LESS THAN (UNIX_TIMESTAMP('YYYY-MM-DD'))
);
会报错如下：
1505 – Partition management on a not partitioned table is not possible, Time: 0.001000s
此时要求至少存在一个分区，否则使用alter table add partition 添加分区的时候就会报错。
需要使用命令：
-- 添加分区
ALTER TABLE partition_test
PARTITION BY RANGE(UNIX_TIMESTAMP(created_at)) (
PARTITION p20230809 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-10'))
);
官方建议
最好的是在创建表时就规划好分区，原因如下：
在创建表时定义分区的好处包括：
- 提前规划： 你可以在设计表结构时就考虑到数据的分布和访问模式，从而更好地规划分区策略。
- 更高效的插入： 表在插入数据之前已经按照分区规则划分好，插入数据时可以直接插入到相应的分区，提高插入性能。
- 更容易维护： 表在创建时就设置了分区，将来你可以更方便地进行数据维护、备份和恢复操作。
为了最大程度地提高性能和避免后续操作的复杂性，建议在创建表时就定义好分区策略。这样可以更好地规划和管理数据，并确保在插入数据时就充分利用分区的性能优势。
留言