优化网站流量seo自学教程推荐
快速入门文章参考：《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) | 支持 |