前言
一般互联网应用,都是读多写少。并采用mysql主从架构,读写分离。
shardingsphere也支持读写分离,实现原理很简单:
- 配置多个数据源,标注数据源是主还是从
- 读默认读主库,也可以强制读主库
- 读走从库,通过负载均衡选择其中一个从库
表结构设计
我在本地机器上,用3个库,模拟主从关系
在每个库中都建一个torder表
create database db1;
create database db2;
create database db3;
-- 在3个DB中建同样的表
CREATE TABLE torder (
order_id BIGINT(20) PRIMARY KEY,
status varchar(20) NOT NULL,
gmt_create datetime NOT NULL,
sku_code VARCHAR(50) NOT NULL,
sku_name VARCHAR(50) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
buyer_id BIGINT(20) NOT NULL
);
测试数据
-- 在3个DB中插入同样数据
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (1,'INIT','2023-06-28 08:30:37','sku_1','商品_1',1000.10,1);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (2,'INIT','2023-06-28 08:30:37','sku_2','商品_2',1000.10,2);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (3,'INIT','2023-06-28 08:30:37','sku_3','商品_3',1000.10,3);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (4,'INIT','2023-06-28 08:30:37','sku_4','商品_4',1000.10,4);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (5,'INIT','2023-06-28 08:30:37','sku_5','商品_5',1000.10,5);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (6,'INIT','2023-06-28 08:30:37','sku_6','商品_6',1000.10,6);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (7,'INIT','2023-06-28 08:30:37','sku_7','商品_7',1000.10,7);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (8,'INIT','2023-06-28 08:30:37','sku_8','商品_8',1000.10,8);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (9,'INIT','2023-06-28 08:30:37','sku_9','商品_9',1000.10,9);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (10,'INIT','2023-06-28 08:30:37','sku_10','商品_10',1000.10,10);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (11,'INIT','2023-06-28 08:30:37','sku_11','商品_11',1000.10,11);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (12,'INIT','2023-06-28 08:30:37','sku_12','商品_12',1000.10,12);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (13,'INIT','2023-06-28 08:30:37','sku_13','商品_13',1000.10,13);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (14,'INIT','2023-06-28 08:30:37','sku_14','商品_14',1000.10,14);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (15,'INIT','2023-06-28 08:30:37','sku_15','商品_15',1000.10,15);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (16,'INIT','2023-06-28 08:30:37','sku_16','商品_16',1000.10,16);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (17,'INIT','2023-06-28 08:30:37','sku_17','商品_17',1000.10,17);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (18,'INIT','2023-06-28 08:30:37','sku_18','商品_18',1000.10,18);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (19,'INIT','2023-06-28 08:30:37','sku_19','商品_19',1000.10,19);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (20,'INIT','2023-06-28 08:30:37','sku_20','商品_20',1000.10,20);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (21,'INIT','2023-06-28 08:30:37','sku_21','商品_21',1000.10,21);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (22,'INIT','2023-06-28 08:30:37','sku_22','商品_22',1000.10,22);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (23,'INIT','2023-06-28 08:30:37','sku_23','商品_23',1000.10,23);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (24,'INIT','2023-06-28 08:30:37','sku_24','商品_24',1000.10,24);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (25,'INIT','2023-06-28 08:30:37','sku_25','商品_25',1000.10,25);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (26,'INIT','2023-06-28 08:30:37','sku_26','商品_26',1000.10,26);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (27,'INIT','2023-06-28 08:30:37','sku_27','商品_27',1000.10,27);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (28,'INIT','2023-06-28 08:30:37','sku_28','商品_28',1000.10,28);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (29,'INIT','2023-06-28 08:30:37','sku_29','商品_29',1000.10,29);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (30,'INIT','2023-06-28 08:30:37','sku_30','商品_30',1000.10,30);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (31,'INIT','2023-06-28 08:30:37','sku_31','商品_31',1000.10,31);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (32,'INIT','2023-06-28 08:30:37','sku_32','商品_32',1000.10,32);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (33,'INIT','2023-06-28 08:30:37','sku_33','商品_33',1000.10,33);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (34,'INIT','2023-06-28 08:30:37','sku_34','商品_34',1000.10,34);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (35,'INIT','2023-06-28 08:30:37','sku_35','商品_35',1000.10,35);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (36,'INIT','2023-06-28 08:30:37','sku_36','商品_36',1000.10,36);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (37,'INIT','2023-06-28 08:30:37','sku_37','商品_37',1000.10,37);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (38,'INIT','2023-06-28 08:30:37','sku_38','商品_38',1000.10,38);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (39,'INIT','2023-06-28 08:30:37','sku_39','商品_39',1000.10,39);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (40,'INIT','2023-06-28 08:30:37','sku_40','商品_40',1000.10,40);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (41,'INIT','2023-06-28 08:30:37','sku_41','商品_41',1000.10,41);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (42,'INIT','2023-06-28 08:30:37','sku_42','商品_42',1000.10,42);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (43,'INIT','2023-06-28 08:30:37','sku_43','商品_43',1000.10,43);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (44,'INIT','2023-06-28 08:30:37','sku_44','商品_44',1000.10,44);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (45,'INIT','2023-06-28 08:30:37','sku_45','商品_45',1000.10,45);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (46,'INIT','2023-06-28 08:30:37','sku_46','商品_46',1000.10,46);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (47,'INIT','2023-06-28 08:30:37','sku_47','商品_47',1000.10,47);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (48,'INIT','2023-06-28 08:30:37','sku_48','商品_48',1000.10,48);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (49,'INIT','2023-06-28 08:30:37','sku_49','商品_49',1000.10,49);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (50,'INIT','2023-06-28 08:30:37','sku_50','商品_50',1000.10,50);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (51,'INIT','2023-06-28 08:30:37','sku_51','商品_51',1000.10,51);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (52,'INIT','2023-06-28 08:30:37','sku_52','商品_52',1000.10,52);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (53,'INIT','2023-06-28 08:30:37','sku_53','商品_53',1000.10,53);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (54,'INIT','2023-06-28 08:30:37','sku_54','商品_54',1000.10,54);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (55,'INIT','2023-06-28 08:30:37','sku_55','商品_55',1000.10,55);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (56,'INIT','2023-06-28 08:30:37','sku_56','商品_56',1000.10,56);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (57,'INIT','2023-06-28 08:30:37','sku_57','商品_57',1000.10,57);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (58,'INIT','2023-06-28 08:30:37','sku_58','商品_58',1000.10,58);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (59,'INIT','2023-06-28 08:30:37','sku_59','商品_59',1000.10,59);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (60,'INIT','2023-06-28 08:30:37','sku_60','商品_60',1000.10,60);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (61,'INIT','2023-06-28 08:30:37','sku_61','商品_61',1000.10,61);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (62,'INIT','2023-06-28 08:30:37','sku_62','商品_62',1000.10,62);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (63,'INIT','2023-06-28 08:30:37','sku_63','商品_63',1000.10,63);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (64,'INIT','2023-06-28 08:30:37','sku_64','商品_64',1000.10,64);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (65,'INIT','2023-06-28 08:30:37','sku_65','商品_65',1000.10,65);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (66,'INIT','2023-06-28 08:30:37','sku_66','商品_66',1000.10,66);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (67,'INIT','2023-06-28 08:30:37','sku_67','商品_67',1000.10,67);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (68,'INIT','2023-06-28 08:30:37','sku_68','商品_68',1000.10,68);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (69,'INIT','2023-06-28 08:30:37','sku_69','商品_69',1000.10,69);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (70,'INIT','2023-06-28 08:30:37','sku_70','商品_70',1000.10,70);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (71,'INIT','2023-06-28 08:30:37','sku_71','商品_71',1000.10,71);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (72,'INIT','2023-06-28 08:30:37','sku_72','商品_72',1000.10,72);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (73,'INIT','2023-06-28 08:30:37','sku_73','商品_73',1000.10,73);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (74,'INIT','2023-06-28 08:30:37','sku_74','商品_74',1000.10,74);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (75,'INIT','2023-06-28 08:30:37','sku_75','商品_75',1000.10,75);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (76,'INIT','2023-06-28 08:30:37','sku_76','商品_76',1000.10,76);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (77,'INIT','2023-06-28 08:30:37','sku_77','商品_77',1000.10,77);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (78,'INIT','2023-06-28 08:30:37','sku_78','商品_78',1000.10,78);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (79,'INIT','2023-06-28 08:30:37','sku_79','商品_79',1000.10,79);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (80,'INIT','2023-06-28 08:30:37','sku_80','商品_80',1000.10,80);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (81,'INIT','2023-06-28 08:30:37','sku_81','商品_81',1000.10,81);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (82,'INIT','2023-06-28 08:30:37','sku_82','商品_82',1000.10,82);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (83,'INIT','2023-06-28 08:30:37','sku_83','商品_83',1000.10,83);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (84,'INIT','2023-06-28 08:30:37','sku_84','商品_84',1000.10,84);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (85,'INIT','2023-06-28 08:30:37','sku_85','商品_85',1000.10,85);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (86,'INIT','2023-06-28 08:30:37','sku_86','商品_86',1000.10,86);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (87,'INIT','2023-06-28 08:30:37','sku_87','商品_87',1000.10,87);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (88,'INIT','2023-06-28 08:30:37','sku_88','商品_88',1000.10,88);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (89,'INIT','2023-06-28 08:30:37','sku_89','商品_89',1000.10,89);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (90,'INIT','2023-06-28 08:30:37','sku_90','商品_90',1000.10,90);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (91,'INIT','2023-06-28 08:30:37','sku_91','商品_91',1000.10,91);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (92,'INIT','2023-06-28 08:30:37','sku_92','商品_92',1000.10,92);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (93,'INIT','2023-06-28 08:30:37','sku_93','商品_93',1000.10,93);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (94,'INIT','2023-06-28 08:30:37','sku_94','商品_94',1000.10,94);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (95,'INIT','2023-06-28 08:30:37','sku_95','商品_95',1000.10,95);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (96,'INIT','2023-06-28 08:30:37','sku_96','商品_96',1000.10,96);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (97,'INIT','2023-06-28 08:30:37','sku_97','商品_97',1000.10,97);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (98,'INIT','2023-06-28 08:30:37','sku_98','商品_98',1000.10,98);
INSERT INTO `torder` (`order_id`,`status`,`gmt_create`,`sku_code`,`sku_name`,`price`,`buyer_id`) VALUES (99,'INIT','2023-06-28 08:30:37','sku_99','商品_99',1000.10,99);
二方包
<dependencies>
<!-- shardingJDBC核心依赖 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<!--XA 分布式事务 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-transaction-xa-core</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<!-- 数据源连接池 -->
<!--注意不要用这个依赖,他会创建数据源,跟上面ShardingJDBC的SpringBoot集成依赖有冲突 -->
<!-- <dependency>-->
<!-- <groupId>com.alibaba</groupId>-->
<!-- <artifactId>druid-spring-boot-starter</artifactId>-->
<!-- <version>1.1.20</version>-->
<!-- </dependency>-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.20</version>
</dependency>
<!-- mysql连接驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- mybatisplus依赖 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
</dependencies>
实体
package com.wgp.oms.dal.entity;
import java.math.BigDecimal;
import java.util.Date;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
/**
* @author gangpeng.wgp
* @date 2023/6/27 下午9:58
*/
@Data
//特别注意,不能踩坑:表名不能叫order,是mysql关键字。否则shardingsphere会启动失败
public class Torder {
@TableId
private Long orderId;
private String status;
private Date gmtCreate;
private String skuCode;
private String skuName;
private BigDecimal price;
private Long buyerId;
}
配置
spring:
# ShardingSphere 配置项
shardingsphere:
# 数据源配置
datasource:
# 所有数据源的名字
names: ds-master, ds-slave-1, ds-slave-2
# 订单 db1 主库的数据源配置
ds-master:
type: com.zaxxer.hikari.HikariDataSource # 使用 Hikari 数据库连接池
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/db1?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: root
password: 12345678
# 订单 db2 从库数据源配置
ds-slave-1:
type: com.zaxxer.hikari.HikariDataSource # 使用 Hikari 数据库连接池
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/db2?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: root
password: 12345678
# 订单 db3 从库数据源配置
ds-slave-2:
type: com.zaxxer.hikari.HikariDataSource # 使用 Hikari 数据库连接池
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/db3?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: root
password: 12345678
#打印sql,方便查看sql在哪个库中执行
props:
sql:
show : true
masterslave:
name: ms # 名字,任意,需要保证唯一
master-data-source-name: ds-master # 主库数据源
slave-data-source-names: ds-slave-1, ds-slave-2 # 从库数据源
单测
package com.wgp.oms.rw.separation;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import javax.annotation.Resource;
import com.wgp.oms.rw.separation.entity.Torder;
import com.wgp.oms.rw.separation.mapper.OrderMapper;
import org.apache.shardingsphere.api.hint.HintManager;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import static org.junit.jupiter.api.Assertions.assertNotNull;
/**
* @author gangpeng.wgp
* @date 2023/6/27 下午10:40
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class OrderServiceTest {
@Resource
private OrderService orderService;
@Resource
private OrderMapper orderMapper;
/**
* 测试读从库,负载均衡
*/
@Test
public void queryOrderById() {
for (int i = 0; i < 100; i++) {
Torder order = orderService.queryOrderById(1L);
assertNotNull(order);
System.out.println(order);
}
}
/**
* 强制读主库
*/
@Test
public void queryOrderFromMaster() {
//HintManager 实现了 AutoCloseable 接口,可以通过 Try-with-resources 机制,自动关闭
try (HintManager hintManager = HintManager.getInstance()) {
//强制读主库
hintManager.setMasterRouteOnly();
Torder order = orderService.queryOrderById(1L);
assertNotNull(order);
System.out.println(order);
}
}
/**
* 测试插入主库
*/
@Test
public void addOrder() {
delete();
for (int i = 1000; i < 1100; i++) {
Torder c = new Torder();
c.setOrderId((long)i);
c.setBuyerId((long)i);
c.setGmtCreate(new Date());
c.setStatus("INIT");
c.setPrice(BigDecimal.valueOf(1000.10));
c.setSkuCode("sku_" + i);
c.setSkuName("商品_" + i);
c.setOrderId((long)i);
orderMapper.insert(c);
}
}
private void delete() {
Map<String, Object> param = new HashMap<>();
orderMapper.deleteByMap(param);
}
}