目录
- 一、表分区条件
- 二、常规表和分区表的区别
- 三、表分区的创建
- 四、将既有表转换分区表脚本
- 五、批量转换表为分区表
- 六、表分区维护：添加表分区
- 七、批量维护：批量添加表分区
- 总结
对于单表大数据量大的问题，如果数据支持分片，使用表分区是个不错的选择，那么mysql是如何实现表分区的？
一、表分区条件
1.数据库存储引擎支持：InnoDB 和 MyISAM引擎
2.数据库版本支持：MySQL 5.1以后（版本不同，具体的特性支持可能会有所不同）
3.数据必须有一个或多个分区键：作为分区的键（字段）必须是主键的一部分（联合主键）
4.分区定义：每个分区必须明确地定义数据范围
5.分区维护：随着时间推移，可能需要添加新的分区或删除旧的分区，以保持数据库的性能和结构
二、常规表和分区表的区别
常规表和分区表对比
| 常规表 | 分区表 | |
|---|---|---|
| 数据结构 | 所有数据存储在单一数据文件 | 数据被逻辑上分成多个部分，可能存放在多个文件甚至多个磁盘 |
| 查询优化 | 查询时默认扫描整表数据 | 只访问相关分区数据 |
| I/O操作 | 添加、删除或修改行操作直接作用于整表 | 只对单个分区操作，不影响其他分区数据 |
| 备份恢复 | 通常备份整表数据 | 可以单独备份或恢复特定分区 |
| 存储管理 | 所有数据集中存储 | 数据分散到多个分区 |
| 扩展性 | 随着数据量增长，可能会遇到性能瓶颈 | 更容易水平扩展，可以通过增加新分区来处理更大的数据集，而不需要改变应用程序逻辑 |
| 限值和复杂性 | 相对简单，没有特殊的创建或维护要求 | 设计和实现更加复杂，需要考虑如何正确地设置分区策略以满足业务需求 |
从上面看分区表是否有很大的优势？但是同样分区表也存在一些限值：
分区表的限制
| 常规表 | 分区表 | |
|---|---|---|
| 外键约束 | √ | × |
| 全文索引 | √ | ×（5.6以前版本） ⍻（5.6以后版本） |
| 临时表 | √ | × |
| 列修改 | √ | × |
| 特定的ALTER TABLE语句 | √ | ×（修改主键、唯一键等） |
| 性能影响 | 数据量影响 | 添加、删除、合并表分区，可能会导致锁表从而影响性能 |
| 备份和恢复工具支持 | 通常工具都支持 | 不是所有的备份和恢复工具都完全支持分区表的所有特性 |
| 主备服务器数据复制 | 无特殊要求 | 必须保证分区规则一致性，任何不匹配都可能导致复制失败或数据不一致 |
| 分区类型限制 | 无 | 存储引擎可能限制分区类型 |
| 查询优化器的行为 | 简单索引 | 表分区+索引，特情情况的复杂查询可能会有表分区裁剪失效问题 |
三、表分区的创建
表分区创建关键的三个点：创建表、设置分区键、设置分片策略
示例：
CREATE TABLE sales (
id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p0 VALUES LESS THAN (2020),
PARTITION p1 VALUES LESS THAN (2021),
PARTITION p2 VALUES LESS THAN (2022),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
创建表名：sales
分区键：sale_date字段的year()结果，即sale_date字段的年份
分片策略：p0分区存储小于2020年数据、 p1分区存储小于2021年数据、p2分区存储小于2022年数据、p3分区存储其他年份数据（注意：这里的数据“挡板”很重要，设置时一定要小心）
注意：这里的分片策略是“LESS THAN xxx”，表示小于后面策略的数据数据，如上面就是小于指定年份的数据归属于这个分区，因此上面用“数据挡板”这个词
四、将既有表转换分区表脚本
因为表的创建结构不同，因此既有表不能直接转换为分区表，要实现既有表转换为分区表，需要经过以下几步：
1.根据既有表创建同字段结构的新分区表、定义好相关分区策略
2.迁移数据到分区表
3.删除旧表、并将分区表改名为原表
具体实现脚本如下：
CREATE DEFINER=`root`@`%` PRjsOCEDURE `convert_table_to_partition`(IN tbl_name VARCHAR(200),OUT out_status INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
-- 输出状态，开始执行状态 100，执行成功状态 200，执行失败状态 50
SET out_status = 100;
-- 创建一个新的空表，不包含表分区（要转换为分区表，必须是空表）
SET @create_empty_tbl_sql = CONCAT(
'CREATE TABLE ', tbl_name, '_partitioned LIKE ', tbl_name, ';'
);
PREPARE stmt FROM @create_empty_tbl_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 获取所有唯一的 year_no 和 month_no 组合作为构建分区定义分区键
SET @partition_def = '';
SET @query = CONCAT(
'SELECT GROUP_CONCAT(
CONCAT("PARTITION p_", year_no, "_", LPAD(month_no, 2, "0"),
" VALUES LESS THAN (",
CASE WHEN month_no = 12 THEN year_no + 1 ELSE year_no END, ", ",
CASE WHEN month_no = 12 THEN 1 ELSE month_no + 1 END, ")")
ORDER BY year_no, month_no SEPARATOR ",\n"
) INTO @partition_def
FROM (
SELECT DISTINCT year_no, month_no
FROM ', tbl_name, '
ORDER BY year_no, month_no
) AS unique_years_months;'
);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 调试信息：输出分区定义字符串
--SELECT tbl_name,@partition_def;
-- 检查是否有有效的分区定义
IF @partition_def IS NULL OR @partition_def = '' THEN
SELECT tbl_name,'No data found for partitioning. Skipping partition creation and data migration.';
-- 空表则直接添加 p_max 分区用于捕获未来数据
SET @partition_def = '\nPARTITION p_max VALUES LESS THAN (MAXVALUE, MAXVALUE)';
ELSE
-- 添加 p_max 分区用于捕获未来数据
http://www.devze.com SET @partition_def = CONCAT(@partition_def, ',\nPARTITION p_max VALUES LESS THAN (MAXVALUE, MAXVALUE)');
END IF;
-- 使用 ALTER TABLE 添加分区定义
SET @add_partitions_sql = CONCAT(
'ALTER TABLE ', tbl_name, '_partitioned
PARTITION BY RANGE COLUMNS(year_no, month_no) (', @partition_def, ');'
);
-- 调试信息：输出添加分区的 SQL 语句
--SELECT tbl_name,@add_partitions_sql;
PREPARE stmt FROM @add_partitions_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 迁移数据到新的分区表
SET @insert_into_partitioned_sql = CONCAT(
'INSERT INTO ', tbl_name, '_partitioned SELECT * FROM ', tbl_name, ';'
);
-- 调试信息：输出插入数据的 SQL 语句
-- SELECT tbl_name,@insert_into_partitioned_sql;
PREPARE stmt FROM @insert_into_partitioned_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 验证数据迁移是否成功
SET @count_original = CONCAT('SELECT COUNT(*) INTO @count_original FROM ', tbl_name);
PREPARE stmt FROM @count_original;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @count_partitioned = CONCAT('SELECT COUNT(*) INTO @count_partitioned FROM ', tbl_name, '_partitioned');
PREPARE stmt FROM @count_partitioned;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 比较原表和新分区表的数据行数
-- SELECT tbl_name,@count_original, @count_partitioned;
-- 如果数据迁移成功，删除旧表并重命名新表（无论是否有数据，均删除缓存表）
IF @count_original = @count_partitioned THEN
-- 删除旧表
SET @drop_old_table_sql = CONCAT('DROP TABLE IF EXISTS ', tbl_name);
PREPARE stmt FROM @drop_old_table_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 重命名新表为旧表名
SET @rename_tables_sql = CONCAT('RENAME TABLE ', tbl_name, '_partitioned TO ', tbl_name);
PREPARE stmt F编程ROM @rename_tables_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- SELECT tbl_name,'Table conversion and data migration completed successfully.';
SET out_status = 200;
ELSE
-- SELECT tbl_name,'Data migration failed, check the logs for more information.';
SET out_status = 50;
END IF;
END
上面脚本是一个完整的将既有表转换为以“year_no”和“month_no”字段为分区键的分区表，主要有以下几步操作：
1）以既有表为模板创建一个新的空表，不包含表分区（要转换为分区表，必须是空表）
2）获取所有唯一的 year_no 和 month_no 组合并构建分区定义字符串（对既有数据分析需要划分的分区策略）
3）检查是否有效的分区定义，若无分区定义，强烈建议则创建一个默认的分区策略p_max以存储未来的数据
4）更新空表，添加相关的分区策略
5）迁移历史数据到分区表
6）数据迁移校验（验证数据完整性）
7）删除旧表（回收表名）
8）将新分区表改名为原表名
五、批量转换表为分区表
批量将常规表转换python为分区表，具体脚本如下：
CREATE DEFINER=`root`@`%` PROCEDURE `tables_convert_to_partition`()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tbl_name VARCHAR(64);
DECLARE convert_status INT;
DECLARE cur CURSOR FOR
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE 'ai_result_%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO tbl_name;
IF done THEN
LEAVE read_loop;
END IF;
-- 调试信息：输出正在转换的表
SELECT tbl_name,'covering...';
CALL convert_table_to_partition(tbl_name,@status);
SET convert_status = @status;
-- 根据返回的状态进行相应的处理
CASE convert_status
WHEN 100 THEN
-- 开始状态，可以忽略，因为这是预期的初始状态
SELECT tbl_name, 'Started conversion.';
WHEN 200 THEN
-- 成功完成
SELECT tbl_name, 'Conversion and data migration completed successfully.';
WHEN 50 THEN
-- 失败
SELECT tbl_name, 'Data migration failed. Check the logs for more information.';
ELSE
-- 未知状态
SELECT tbl_name, CONCAT('Unknown status: ', status);
END CASE;
END LOOP;
CLOSE cur;
END
这里是以“ai_result_”开头的表为例，将所有相关表转换为分区表，在执行这个存储过程时，操作用户必须要有information_schema数据库读取权限，这样才能查询出相关的表名从而进行转换。
该脚本建议为一次性执行脚本，避免对标频繁转换，防止锁表（因此表名前缀已固定在代码中，需根据自身需求修改）
六、表分区维护：添加表分区
表分区经过上面的过程创建，理论上已经对历史数据进行表分区，对未来数据也能存储到p_max分区，但是p_max分区数据如果不进行维护，同样会有数据量过大问题，因此我们需要定期切割p_max分区并增加相关表分区，这个操作需要在数据进入之前执行，具体执行脚本如下：
CREATE DEFINER=`root`@`%` PROCEDURE `add_monthly_partitions`(IN tbl_name VARCHAR(64), IN year_no INT, IN month_no INT,OUT out_status INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
-- 输出状态，开始执行状态 100，执行成功状态 200，执行失败状态 50
SET out_status = 100;
-- 检查待添加的分区是否已经存在
SET @partition_exists = EXISTS (SELECT 1 FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl_name
AND PARTITION_NAME = CONCAT('p_', year_no, '_', LPAD(month_no, 2, '0')));
IF @partition_exists THEN
-- 如果分区已存在，直接返回消息
-- SELECT CONCAT('Partition p_', year_no, '_', LPAD(month_no, 2, '0'), ' already exists. No action taken.') AS message;
SET out_status = 200;
ELSE
-- 检查表中是否已经存在 p_max 分区
SET @has_p_max = EXISTS (SELECT 1 FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl_name
AND PARTITION_NAME = 'p_max');
-- 构建添加分区的 SQL 语句
IF @has_p_max THEN
-- 如果存在 p_max 分区，则重新组织分区，将 p_max 分割成新分区和更新后的 p_max
SET @reorganize_partition_sql = CONCAT(
'ALTER TABLE ', tbl_name, ' REORGANIZE PARTITION p_max INTO (
PARTITION p_'beeIChS, year_no, '_', LPAD(month_no, 2, '0'),
' VALUES LESS THAN (',
CASE WHEN month_no = 12 THEN year_no + 1 ELSE year_no END, ', ',
CASE WHEN month_no = 12 THEN 1 ELSE month_no + 1 END, '),
PARTITION p_max VALUES LESS THAN (MAXVALUE, MAXVALUE)
)'
);
PREPARE stmt FROM @reorganize_partition_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- SELECT CONCAT('Partition p_', year_no, '_', LPAD(month_no, 2, '0'), ' and updated p_max added successfully.') AS message;
SET out_status = 200;
ELSE
-- 如果不存在 p_max 分区，则直接添加新分区
SET @add_partition_sql = CONCAT(
'ALTER TABLE ', tbl_name, ' ADD PARTITION (
PARTITION p_', year_no, '_', LPAD(month_no, 2, '0'),
' VALUES LESS THAN (',
CASE WHEN month_no = 12 THEN year_no + 1 ELSE year_no END, ', ',
CASE WHEN month_no = 12 THEN 1 ELSE month_no + 1 END, ')
)'
);
PREPARE stmt FROM @add_partition_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- SELECT CONCAT('Partition p_', year_no, '_', LPAD(month_no, 2, '0'), ' added successfully.') AS message;
SET out_status = 200;
END IF;
END IF;
END
上面脚本的执行过程如下：
1）检测待添加的分区是否已存在（已存在则不添加，不存在才添加）
2）检测表中是否存在p_max 分区（检测待切割分区，若存在则切割分区，若不存在这创建分区）
3）切割p_max分区为新分区和新的p_max分区（此处会调整p_max分区的分片策略）
七、批量维护：批量添加表分区
批量给相关表添加表分区，具体脚本如下：
CREATE DEFINER=`root`@`%` PROCEDURE `tables_add_monthly_partition`(IN tbl_prefix VARCHAR(64), IN year_no INT, IN month_no INT,OUT out_status INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tbl_name VARCHAR(64);
DECLARE cur CURSOR FOR
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE CONCAT(tbl_prefix, '%');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 输出状态，开始执行状态 100，执行成功状态 200，执行失败状态 50
SET out_status = 100;
-- 打开游标
OPEN cur;
read_loop: LOOP
FETCH cur INTO tbl_name;
IF done THEN
LEAVE read_loop;
END IF;
-- 检查表是否已经是分区表
SET @is_partitioned = EXISTS (SELECT 1 FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl_name);
IF NOT @is_partitioned THEN
-- 如果表不是分区表，先调用 convert_table_to_partition 进行转换
CALL convert_table_to_partition(tbl_name,@status);
-- 转换后再次检查是否成功转换为分区表
SET @is_partitioned = EXISTS (SELECT 1 FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl_name);
IF NOT @is_partitioned THEN
-- 如果转换失败，跳过后续操作并输出错误信息
SELECT CONCAT('Failed to convert table ', tbl_name, ' to partitioned. Skipping.') AS message;
SET out_status = 50;
ITERATE read_loop;
END IF;
END IF;
-- 调用 add_monthly_partitions 为当前表添加分区
CALL add_monthly_partitions(tbl_name, year_no, month_no,@status);
-- 可选：输出操作结果（用于调试）
-- SELECT CONCAT('Processed table: ', tbl_name) AS status;
END LOOP;
-- 关闭游标
CLOSE cur;
-- 输出完成信息
-- SELECT CONCAT('BATch partition addition completed for tables with prefix "', tbl_prefix, '".') AS message;
SET out_status = 200;
END
批量添加表分区需要传入相关表前缀，如上面示例中的“ai_result_”，此脚本会将非分区表转换为分区表，再给分区表添加相应的表分区，具体执行过程如下：
1）获取所有相关表
2）遍历判断表是否是分区表
3）非分区表被转换为分区表
4）给分区表添加表分区策略
该脚本请慎重执行，上面我们有常规表和分区表的对比，执行脚本很简单（批量自动完成），但执行的后果请慎重考虑！
总结
到此这篇关于MySQL数据库实现批量表分区的文章就介绍到这了,更多相关MySQL批量表分区内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！
加载中,请稍侯......
精彩评论