ShardingSphere-Proxy分表场景测试案例

快速入门文章参考：《ShardingSphereProxy:快速入门》

基于K8S部署文章参考：《基于K8s部署ShardingSphere-Proxy》

基于golang的测试用例参考：《ShardingSphere-Proxy 连接实战：从 Golang 原生 SQL 到 GORM 的应用》

背景

我们需要具体的看一下ShardingSphere-Proxy分表组件的边界，因此，我测试了常用的41个场景。

数据库表结构

我们对订单表（orders）基于 user_id 进行了分表操作，共分为四张表。而用户表（user）和产品表（product）则未进行分表处理，它们的建表语句如下：

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders_0`  (`id` bigint NOT NULL,`order_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`user_id` int NOT NULL,`product_id` int NOT NULL,`order_date` bigint NOT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (`id` bigint NOT NULL,`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (`id` bigint NOT NULL,`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

测试场景

项目	说明	是否包含分表键	测试sql	结果
全表查询			SELECT * FROM `orders`;	支持
等值查询		否	SELECT * FROM `orders` WHERE order_id='20240101ORDER9503';	支持
		是	SELECT * FROM `orders` WHERE user_id=8648;	支持
	and查询	否	SELECT * FROM `orders` WHERE order_id='20240101ORDER6546' AND product_id=861;	支持
	and查询	是	SELECT * FROM `orders` WHERE user_id=4581 AND product_id=213	支持
范围查询	in查询	是	SELECT * FROM `orders` WHERE user_id IN (2608,4581,2142,3519)	支持
	in查询	否	SELECT * FROM `orders` WHERE order_id IN ('20240101ORDER6546','20250101ORDER2295','20250101ORDER4465','20240101ORDER7826')	支持
	between查询	否	SELECT * FROM `orders` WHERE order_id BETWEEN '20240101ORDER6546' AND '20240101ORDER6548	支持
	between查询	是	SELECT * FROM `orders` WHERE user_id BETWEEN 4581 AND 4583	支持
	or查询	否	SELECT * FROM `orders` WHERE order_id = '20240101ORDER6546' OR order_id = '20250101ORDER2295'	支持
	or查询	是	SELECT * FROM `orders` WHERE user_id = 4581 OR user_id = 3519	支持
	>查询	否	SELECT * FROM `orders` WHERE order_id > '20240101ORDER6546'	支持
	>查询	是	SELECT * FROM `orders` WHERE user_id > 4581	支持
累加聚合	sum	否	SELECT SUM(product_id) FROM `orders` WHERE order_id='20240101ORDER6546'	支持
	sum	是	SELECT SUM(product_id) FROM `orders` WHERE user_id=4581	支持
	sum	否	SELECT SUM(product_id) FROM `orders`
	count	否	SELECT count(*) FROM `orders` WHERE order_id='20240101ORDER6546'	支持
	count	是	SELECT count(*) FROM `orders` WHERE user_id=4581	支持
	count	否	SELECT count(*) FROM `orders`	支持
比较聚合	max	否	SELECT MAX(user_id) FROM `orders`	支持
	min	否	SELECT MIN(user_id) FROM `orders`	支持
平均聚合	avg	否	SELECT AVG(user_id) FROM `orders`	支持
分组	group	否	SELECT *,SUM(product_id) AS sum_product,MAX(product_id) as max_product FROM `orders` GROUP BY `order_id`	支持
	group	是	SELECT *,SUM(product_id) AS sum_product,MAX(product_id) as max_product FROM `orders` GROUP BY `user_id`	支持
排序、分页	order/limit	否	SELECT * FROM `orders` ORDER BY order_id desc LIMIT 5	支持
	order/limit	是	SELECT * FROM `orders` ORDER BY user_id desc LIMIT 5	支持
去重	DISTINCT	否	SELECT DISTINCT `order_id` FROM `orders`	支持
	DISTINCT	是	SELECT DISTINCT `user_id` FROM `orders`	支持
关联查询	join	否	SELECT orders.*,product.* FROM `orders` JOIN product ON orders.product_id = product.id	支持
	join	是	SELECT orders.*,user.* FROM `orders` JOIN user ON orders.user_id = user.id	支持
子查询		否	SELECT subquery.* FROM (SELECT orders.* FROM orders JOIN product ON orders.product_id = product.id) AS subquery	支持
		是	SELECT subquery.* FROM (SELECT orders.* FROM orders JOIN user ON orders.user_id = user.id) AS subquery	支持
	IN	是	SELECT * FROM orders WHERE user_id IN (SELECT id FROM user)	支持
UNION	UNION	是	SELECT * FROM orders WHERE user_id = 8648 UNION SELECT * FROM orders WHERE user_id = 3401	支持
	UNION	否	SELECT * FROM orders WHERE order_id = '20240101ORDER6546' UNION SELECT * FROM orders WHERE order_id = '20240101ORDER9728'
	UNION ALL	否	SELECT * FROM orders WHERE order_id = '20240101ORDER6546' UNION SELECT * FROM orders WHERE order_id = '20240101ORDER9728'	支持
	UNION ALL	否	SELECT * FROM orders WHERE order_id = '20240101ORDER6546' UNION ALL SELECT * FROM orders WHERE order_id = '20240101ORDER9728'	支持
UPDATE	根据主键更新	否	UPDATE `orders` SET `product_id`=1 WHERE id = 1866023311733952512	支持
		是	UPDATE `orders` SET `product_id`=1 WHERE user_id = 9148	支持
DELETE	根据主键删除	否	DELETE FROM `orders` WHERE id = 1866023311071252480	支持
		是	DELETE FROM `orders` WHERE user_id = 4389	支持
INSERT			INSERT INTO `orders` (`order_id`,`user_id`,`product_id`,`order_date`,`id`) VALUES ('20240101ORDER1227',1660,106,1735632223,1874003486933258240)	支持