当业务数据量达到单表存储上限时，水平拆分是将大表按规则拆分成多个结构相同的小表，再通过统一逻辑调用这些分表的核心方案。通过SQL存储过程封装分片逻辑，可以避免上层业务反复编写路由代码，降低耦合度。
数据分片的核心准备
要实现存储过程的分片逻辑，首先需要明确几个核心要素：
- 分片键：用于判断数据归属分表的字段，比如用户表的user_id、订单表的order_id
- 分片规则：常见的取模、范围、哈希规则，本文以最常用的取模规则为例
- 分表命名规范：比如原表为t_order，拆分后的分表命名为t_order_0、t_order_1，后缀为分片编号
存储过程实现分片逻辑的核心步骤
1. 分片编号计算函数
首先可以创建一个辅助函数来计算分片编号，避免在存储过程中重复编写计算逻辑：
-- 创建分片编号计算函数，入参为分片键和分片总数，返回分片编号
CREATE FUNCTION calc_shard_index(p_shard_key INT, p_shard_count INT)
RETURNS INT
DETERMINISTIC
BEGIN
-- 取模计算分片编号，确保结果在0到p_shard_count-1之间
DECLARE v_shard_index INT;
SET v_shard_index = p_shard_key % p_shard_count;
RETURN v_shard_index;
END;
2. 插入数据的分片存储过程
插入数据时，先根据分片键计算分片编号，再动态拼接分表名执行插入操作：
-- 创建插入数据的分片存储过程，假设分片键为order_id，分片总数为4
CREATE PROCEDURE proc_insert_order(
IN p_order_id INT,
IN p_user_id INT,
IN p_order_amount DECIMAL(10,2),
IN p_order_status VARCHAR(20)
)
BEGIN
-- 声明变量存储分片编号和动态SQL
DECLARE v_shard_index INT;
DECLARE v_sql VARCHAR(1000);
-- 计算分片编号
SET v_shard_index = calc_shard_index(p_order_id, 4);
-- 拼接插入SQL，分表名为t_order_ + 分片编号
SET v_sql = CONCAT(
'INSERT INTO t_order_',
v_shard_index,
' (order_id, user_id, order_amount, order_status) VALUES (',
p_order_id, ',', p_user_id, ',', p_order_amount, ',''', p_order_status, ''')'
);
-- 执行动态SQL
SET @dynamic_sql = v_sql;
PREPARE stmt FROM @dynamic_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
3. 查询数据的分片存储过程
查询数据时同样先根据分片键路由到对应分表，如果查询条件不包含分片键，可能需要遍历所有分表：
-- 创建根据order_id查询订单的分片存储过程
CREATE PROCEDURE proc_query_order_by_id(
IN p_order_id INT
)
BEGIN
DECLARE v_shard_index INT;
DECLARE v_sql VARCHAR(1000);
-- 计算分片编号
SET v_shard_index = calc_shard_index(p_order_id, 4);
-- 拼接查询SQL
SET v_sql = CONCAT(
'SELECT order_id, user_id, order_amount, order_status FROM t_order_',
v_shard_index,
' WHERE order_id = ',
p_order_id
);
-- 执行动态SQL
SET @dynamic_sql = v_sql;
PREPARE stmt FROM @dynamic_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
分片逻辑的注意事项
- 动态SQL拼接时需要注意字符串类型的参数要加单引号，避免SQL语法错误
- 如果分片规则后续需要调整，比如分片总数从4扩展到8，需要提前做好数据迁移和规则的兼容处理
- 建议对分表建立和原表一致的索引，避免分片后查询性能下降
- 如果业务需要跨分表聚合查询，可以在存储过程中循环遍历所有分表，将结果合并后返回，不过这种场景性能会有所下降，需要谨慎使用
分片逻辑调用示例
调用上述存储过程的方式和普通存储过程一致：
-- 插入一条订单数据，order_id为1001，会路由到t_order_1表（1001%4=1） CALL proc_insert_order(1001, 2001, 199.99, 'PAID'); -- 查询order_id为1001的订单数据 CALL proc_query_order_by_id(1001);
通过上述方式，就可以在SQL存储过程中封装完整的水平拆分分片逻辑，上层业务只需要调用存储过程传入对应参数，不需要关心数据具体存储在哪个分表中，降低了业务代码的复杂度。