MySQL分区表深度解析：优化大量数据插入的实战策略！

本文链接：https://blog.csdn.net/cg_i/article/details/141030345

MySQL分区表深度解析：优化大量数据插入的实战策略！

作者：高玉涵
时间：2024.8.8
博客：blog.csdn.net/cg_i
环境：MySQL5.7.26

如果你今天对自己满意，明天就会淘汰——田元灏

一、随时应对变化胜过遵循计划

近期，我受业务部门的同事们所托，为他们开发一套系统，以满足实际的业务需求。尽管我的团队实际上只有我一个人，但我对系统的每一个环节都认真负责，从需求分析、设计构思、代码实现，到严格测试、顺利部署、用户培训，再到后期维护、效果评估和版本迭代，每一个环节我都力求尽善尽美，不敢有丝毫马虎 😛。

一个人的团队也有其显而易见的优势。最突出的就是互动效率的提升，我可以迅速做出决策并快速响应各种需求变化。比如，在下面的微信聊天记录中，业务部门提出了多项新的需求变更，我仅仅用了几分钟的时间就确认了需求细节，并在短时间内调整了系统设计方案，整个过程高效流畅。这种灵活性不仅让业务部门能够更加灵活地应对业务指标变化，也让他们对我的工作表示了高度的认可和满意 😄。
Alt
Alt
Alt
Alt
Alt
Alt

二、设计从单表向分区表的演变：一个现实的例子

就如上面微信聊天记录中所示（如图 2 所示），在我提供的第一个版本中，基于当时对业务部门需求的初步理解，我在设计导出记录明细时（数据存储在一张单表里且只保存最近日期的数据），主要考虑了系统性能和存储效率。虽然我也意识到这一设计可能并不完美，但我认为可以工作的软件应放在首位。因此，我决定先推出这一版本以供使用。

然而，这一设计，虽然初步满足了业务部门的需求，但实际上并未考虑到后续提出的不同日期的明细表，增加对比功能，筛选指定条件下的明细等新需求（如图 5 所示）。因为按照我先前的设计，系统并未保存每日的数据，只保留了最近日期的数据，所以无法直接完成这样的对比和筛选功能。同时，当时采用的单表设计，在面对每日大量数据插入时，其效率和性能也并非最优选择。

三、优化设计方案：从单表到分区表的转变

面对业务部门提出的新需求以及单表设计在性能和效率上的局限性，我迅速调整了设计方案。我决定将原本的单表设计转变为分区表设计，以满足业务部门更加复杂和多样化的数据查询与对比需求（如图 6 所示）。

MySQL从5.1版本开始支持分区功能。分区表是将表中的数据分布到不同的物理部分，但在逻辑上仍然表现为一个整体。这样做的好处包括提高查询性能、简化数据管理以及优化数据维护任务。

在MySQL中，提供了多种灵活的分区策略，以适应不同的应用场景。这些策略包括范围分区（RANGE）、列表分区（LIST）、哈希（HASH）分区以及键（KEY）分区。对于基于日期的数据插入操作，范围分区无疑是一个明智的选择。它允许我们根据日期范围将数据智能地分布到不同的分区中，从而进一步提升查询效率和数据管理的便捷性。

当然，除了范围分区之外，其他分区策略也各有其独特的应用场景和优势。但考虑到篇幅限制，这里不再一一展开。对于对这些分区策略感兴趣的朋友，我建议您自行搜索相关资料，以更深入地了解它们的原理和应用方法。

四、实战案例

针对业务需求我设计一个名为classify的表，该表的主要功能是记录每日的分类数据。考虑到在同个日期内会有大量不重复的数据插入，并且在多个日期内允许存在重复数据，我决定采用范围分区策略来优化这个表的设计。

1.创建分区表

首先，我根据日期列dt进行范围分区：

CREATE TABLE `classify` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `gid` tinyint(3) unsigned NOT NULL COMMENT '机构ID',
  `org` varchar(200) NOT NULL COMMENT '机构名称',
  `no` varchar(30) NOT NULL COMMENT '帐号',
  `dt` DATE NOT NULL COMMENT '数据日期',
  PRIMARY KEY (`id`, `dt`),  		-- 主键包含分区函数的所有列
  UNIQUE KEY `no_dt` (`no`, `dt`)	-- 帐号和日期的组合唯一
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
PARTITION BY RANGE (YEAR(`dt`) * 100 + MONTH(`dt`)) (
    PARTITION p202408 VALUES LESS THAN (202409), -- 2024年8月的数据（起始）
    PARTITION p202409 VALUES LESS THAN (202410),
    PARTITION p202410 VALUES LESS THAN (202411),
    PARTITION p202411 VALUES LESS THAN (202412),
    PARTITION p202412 VALUES LESS THAN (202501),
    PARTITION pmax VALUES LESS THAN MAXVALUE  -- 为未来的日期保留一个分区
);

这个SQL语句创建了一个名为classify的表，该表使用InnoDB存储引擎，它提供了事务支持、行级锁定等高级功能，对于大量数据的插入和更新操作更加高效。并默认使用utf8字符集。图 7 所示，分区表创建后在磁盘中的物理样式。
在这里插入图片描述

（图 7 分区表创建后在磁盘中的物理样式）以下是表结构和分区设置的详细解释：

表结构：

id：一个无符号的整数，作为表的主键之一，自动增长。
gid：一个无符号的小整数，表示机构ID。
org：一个变长字符串，用于存储机构名称。
no：一个变长字符串，用于存储帐号，与dt一起构成唯一键。
dt：一个日期字段，表示数据日期，也是主键的一部分。

分区设置：

表通过PARTITION BY RANGE根据dt字段的年份和月份进行分区。
分区函数是YEAR(dt) * 100 + MONTH(dt)，这意味着每个分区包含一个月的数据。
p202408 分区将包含所有 dt 列值为 2024-08-01 到 2024-08-31 的记录。
每个分区代表一个月的数据，分区名 pYYYYMM 表示年份和月份。
pmax 分区用于存储所有超过最后一个定义分区日期的数据。

通过这种方式，表classify能够有效地管理大量数据，查询、删除等操作也可以根据日期范围有效地使用分区来提高性能。分区还可以帮助进行更有效的数据维护，例如，可以通过删除或归档旧分区来管理数据的大小。

注：分区可按天创建。在大多数情况下，按天创建分区对于大多数业务需求来说可能过于细粒度，特别是如果查询通常针对更大的时间范围（如按月或按年）。如果查询性能是一个关键问题，通常建议使用更粗粒度的分区策略，如按月或按年分区。

2.管理和维护分区

可以通过ALTER TABLE语句来添加、删除、合并或重新组织分区。
例如，添加新分区：

ALTER TABLE classify ADD PARTITION (PARTITION p202501 VALUES LESS THAN (TO_DAYS('202502')));

3.查询分区表：

查询分区表时，数据库会自动优化查询（自动分区裁剪），只扫描相关的分区。
例如，查询9至11月的记录：

SELECT * FROM classify WHERE dt BETWEEN '2024-09-01' AND '2024-11-31';

在这个例子中，MySQL 会自动确定只需要扫描对应于 2024 年 9 月至11月的分区。

注：MySQL 5.7+ 的改进：从 MySQL 5.7 开始，引入了更智能的分区裁剪功能，可以更有效地处理某些类型的查询，即使它们不直接使用分区键。但是，这仍然依赖于查询条件的形式和分区键的使用。
在这里插入图片描述

（图 8 展显给业务人员样式）

五、优化大量数据插入的策略

在面临大量数据插入的挑战时，我采取了一系列策略来优化classify表的性能。以下是我实施的关键策略：

使用批量插入：批量插入可以显著减少网络开销和数据库的开销。通过一次插入多条记录，我们可以提高数据插入的效率。
选择合适的索引：索引是优化查询性能的关键。然而，在大量数据插入的场景中，过多的索引可能会减慢插入速度。因此，我们需要仔细选择索引，确保它们对查询性能有明显的提升，同时不会过多地影响插入性能。
考虑禁用唯一性检查或外键约束：在大量数据插入时，唯一性检查和外键约束可能会显著减慢速度。如果可能，我们可以考虑在插入过程中禁用这些检查，以提高性能。当然，这需要在数据完整性和插入性能之间做出权衡。
定期维护：定期优化表可以帮助我们保持数据库的性能。使用OPTIMIZE TABLE语句可以重新组织表的物理存储，提高查询和插入性能。
监控性能：使用性能监控工具来跟踪查询性能和系统负载是非常重要的。这可以帮助我们及时发现性能瓶颈，并采取相应的优化措施。