# 简介
当单表数据量过大时，就需要考虑对表进行分表或者分区了。分表和分区都是用来解决数据库中大量数据存储和查询效率的问题，但它们的实现方式和解决的问题有所不同。
分表（Sharding）：
- 分表是将一个大表按照某种规则拆分成多个较小的表，每个小表称为一个分表，这些分表可以分布在不同的数据库实例中。
- 分表通常基于某种规则进行拆分，例如按照用户 ID、地理位置等进行拆分。
- 分表的主要目的是解决单个表过大的问题，提高查询效率和并发能力。
分区（Partitioning）：
- 分区是将一个表按照某种规则拆分成多个逻辑上的部分，每个部分称为一个分区，但这些分区仍然属于同一个表。
- 分区通常基于某种规则进行拆分，例如按照时间、范围、哈希值等进行拆分。
- 分区的主要目的是提高数据的管理和查询效率，通过将数据分散到不同的分区中，可以提高查询性能和管理灵活性。
区别：
- 实现方式：分表是将一个表拆分成多个子表，每个子表都是一个独立的表；而分区是将一个表按照某种规则拆分成多个逻辑上的部分，这些部分仍然属于同一个表。
- 解决的问题：分表主要解决单个表过大的问题，提高查询效率和并发能力；而分区主要提高数据的管理和查询效率，通过将数据分散到不同的分区中，可以提高查询性能和管理灵活性。
- 适用场景：分表适用于单表数据量巨大的情况，例如大型电商平台的订单表；而分区适用于需要根据某种规则对数据进行划分管理的情况，例如按照时间对日志表进行分区。
# 实现细节
本文章介绍的是如何对单表进行分区。
# 给表添加分区
ALTER TABLE 表名（字段） | |
PARTITION BY RANGE COLUMNS (时间字段名) ( | |
-- 分区条件（时间小于 2022-02-01 的数据放入 p202201 分区中） | |
PARTITION p202201 VALUES LESS THAN ('2022-02-01'), | |
PARTITION p202202 VALUES LESS THAN ('2022-03-01'), | |
PARTITION p202203 VALUES LESS THAN ('2022-04-01'), | |
-- 继续定义更多的分区... | |
); |
# 按指定表名创建当前年月的分区
CREATE PROCEDURE create_monthly_partition(IN tableName VARCHAR(255)) | |
BEGIN | |
DECLARE currentYear INT; | |
DECLARE currentMonth INT; | |
DECLARE nextYear INT; | |
DECLARE nextMonth INT; | |
DECLARE partitionName VARCHAR(255); | |
SET currentYear = YEAR(CURRENT_DATE); | |
SET currentMonth = MONTH(CURRENT_DATE); | |
-- 计算下一个月的年份和月份 | |
IF currentMonth = 12 THEN | |
SET nextYear = currentYear + 1; | |
SET nextMonth = 1; | |
ELSE | |
SET nextYear = currentYear; | |
SET nextMonth = currentMonth + 1; | |
END IF; | |
SET partitionName = CONCAT('p', currentYear, LPAD(currentMonth, 2, 0)); | |
SET @sql = CONCAT('ALTER TABLE ', tableName, | |
' ADD PARTITION (PARTITION ', partitionName, | |
' VALUES LESS THAN (\'', nextYear, '-', LPAD(nextMonth, 2, 0), '-01\'', '))'); | |
PREPARE stmt FROM @sql; | |
EXECUTE stmt; | |
DEALLOCATE PREPARE stmt; | |
END |
# 调用创建新分区
CALL create_monthly_partition('表名'); |
# 删除指定分区
ALTER TABLE 表名 drop PARTITION 分区名; |
# 每月执行一次调用指定表添加新分区
CREATE EVENT create_monthly_partition_event | |
ON SCHEDULE EVERY 1 MONTH | |
STARTS '2024-01-01 00:00:00.000' | |
ON COMPLETION NOT PRESERVE | |
ENABLE | |
DO begin | |
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION | |
BEGIN | |
-- 捕获异常，记录错误信息，然后继续执行 | |
SET @error_message = CONCAT('Error occurred while processing table: ', @tableName); | |
-- 可以将错误信息插入到日志表，或者使用 SELECT 输出错误信息 | |
SELECT @error_message; | |
END; | |
-- 定义需要分区的表名列表 | |
SET @tables = '表名1,表名2'; | |
-- 遍历表名列表并为每个表创建分区 | |
WHILE CHAR_LENGTH(@tables) > 0 DO | |
SET @tableName = SUBSTRING_INDEX(@tables, ',', 1); | |
SET @tables = SUBSTRING(@tables, CHAR_LENGTH(@tableName) + 2); | |
CALL create_monthly_partition(@tableName); | |
END WHILE; | |
end |
