MySQL 表分区实践案例：基于 ERP 订单系统的租户分区

MySQL 表分区实践案例：基于 ERP 订单系统的租户分区

在处理海量数据的 ERP 系统中，表分区是一种有效的数据管理策略。本文通过一个基于 ERP 订单系统的实践案例，详细讲解 MySQL 表分区的实现、维护及查看。

一、业务背景

在 ERP 系统中，订单表存储了多个租户的订单数据。随着租户数量和订单量的增加，单表数据量变得巨大，查询性能下降。为优化查询性能和数据管理，决定按租户对订单表进行分区。

二、表结构设计

CREATE TABLE `orders` (
  `order_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `order_amount` decimal(10,2) NOT NULL,
  `order_time` datetime NOT NULL,
  `order_status` varchar(20) NOT NULL,
  PRIMARY KEY (`order_id`,`tenant_id`) -- 主键包含分区键
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY KEY (tenant_id)
PARTITIONS 4 */;

设计说明

• 分区键选择 ：按租户 ID（tenant_id）分区，因为 ERP 系统中经常按租户查询数据。
• 分区方式 ：使用KEY分区，将租户 ID 哈希到不同分区。
• 分区数量 ：初始设置为 4 个分区，可根据租户数量和数据分布调整。
• 主键设计 ：主键包含分区键（tenant_id），避免 MySQL 分区表的限制。

三、数据插入与查询

插入数据

INSERT INTO orders (tenant_id, user_id, order_amount, order_time, order_status)
VALUES
(1, 12345, 99.99, '2023-05-15 10:00:00', '已完成'),
(2, 12346, 199.99, '2023-06-20 15:30:00', '待支付');

查询特定租户数据

-- 查询租户 ID 为 1 的订单
SELECT * FROM orders WHERE tenant_id = 1;

-- 查询租户 ID 为 2 的已完成订单
SELECT * FROM orders WHERE tenant_id = 2 AND order_status = '已完成';

四、分区维护

增加新分区

当租户数量增加，现有分区无法满足需求时，可增加新分区：

ALTER TABLE orders ADD PARTITION PARTITIONS 2;

合并分区

当某些分区数据量过小，可合并分区：

ALTER TABLE orders COALESCE PARTITION 2;

五、性能优化实践

索引设计

• 在 tenant_id 和 order_time 上建组合索引，提升按租户和时间范围查询性能：

ALTER TABLE orders ADD INDEX idx_tenant_time (tenant_id, order_time);

• 对频繁查询的 user_id 建索引：

ALTER TABLE orders ADD INDEX idx_user (user_id);

查询优化

• 分区裁剪 ：查询时明确租户 ID，让 MySQL 仅扫描相关分区。
• 避免全表扫描 ：用索引优化查询，减少分区扫描范围。

定期维护

• 分析表 ：定期分析表结构，更新统计信息，帮助优化器选择更好执行计划：

ANALYZE TABLE orders;

• 优化表 ：碎片过多时优化表：

OPTIMIZE TABLE orders;

六、查看分区

查询 information_schema.partitions 表

SELECT * FROM information_schema.partitions
WHERE table_schema = 'erp_db' AND table_name = 'orders';

使用 SHOW CREATE TABLE 语句

SHOW CREATE TABLE orders;

这将显示表的创建语句，其中包含分区定义部分。

使用 PARTITION_NAME 函数（MySQL 8.0+）

SELECT PARTITION_NAME FROM information_schema.partitions
WHERE table_schema = 'erp_db' AND table_name = 'orders';

七、注意事项

1. 分区键选择 ：需结合查询模式和数据分布，确保常用查询能利用分区裁剪。
2. 分区数量 ：过多分区会增加管理复杂度，降低性能。单表分区数建议不超过 1000。
3. 数据分布 ：数据需均匀分布，避免某些分区数据过多，影响性能。
4. 备份策略 ：分区表备份需考虑分区情况，可单独备份关键分区数据。
5. 兼容性 ：使用分区前确认 MySQL 版本支持，部分功能在高版本才有。

不超过 1000。
3. 数据分布 ：数据需均匀分布，避免某些分区数据过多，影响性能。
4. 备份策略 ：分区表备份需考虑分区情况，可单独备份关键分区数据。
5. 兼容性 ：使用分区前确认 MySQL 版本支持，部分功能在高版本才有。

通过以上实践案例，可有效管理 ERP 系统中多租户订单数据，提升查询性能和数据管理效率。