当用户量级超过10万，或者单表数据量超过1000万，单台数据库的性能将无法支撑业务的发展，所以我们必须考虑分库分表方案了，这里我们设计为2库4表方案
image.png
docker启动两个mysql实例，作为两库
image.png
image.png
建表语句 先建schema然后建table
create table t_order0
(
id bigint not null
primary key,
order_name varchar(20) null,
insert_date datetime null,
user_id bigint null
);
create table t_order1
(
id bigint not null
primary key,
order_name varchar(20) null,
insert_date datetime null,
user_id bigint null
);
pom文件需要的依赖
<!-- spring-boot-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<!-- 分库分表 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3.1</version>
</dependency>
<!-- spring-boot-devtools -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- <dependency>-->
<!-- <groupId>com.zaxxer</groupId>-->
<!-- <artifactId>HikariCP</artifactId>-->
<!-- <version>3.4.3</version>-->
<!-- </dependency>-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.6</version>
</dependency>
application.yml配置文件
spring:
shardingsphere:
datasource:
names: test0,test1
test0:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3306/schema_test0
username: root
password: 123456
test1:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3307/schema_test1
username: root
password: 123456
sharding:
default-database-strategy:
inline:
sharding-column: user_id
algorithm-expression: test$->{user_id % 2}
tables:
t_order:
actual-data-nodes: test$->{0..1}.t_order$->{0..1}
table-strategy:
inline:
sharding-column: id
algorithm-expression: t_order$->{id % 2}
props:
sql:
show:
true
整合mybatis-plus 采用configration方式
@Configuration
@MapperScan("com.cmx.dbasy.sharding.mapper")
public class MybatisPlusConfigration {
}
创建实体类
注意事项
1.使用IdType.ASSIGN_ID策略（自3.3.0起）。该策略会使用雪花算法自动生成主键ID，主键类型为长或字符串（分别对应的MySQL的表字段为BIGINT和VARCHAR）
2.id类型用封装类，否则会提示This primary key of "id" is primitive !不建议如此请使用包装类 in Class: "com
@Data
@TableName("t_order")
public class Order {
@TableId(type = IdType.ASSIGN_ID)
private Long id;
private String orderName;
private Long userId;
private Date insertDate;
}
创建mapper类，实现BaseMapper
@Mapper
public interface OrderMapper extends BaseMapper<Order> {
}
创建测试类
1.模拟10个随机用户产生订单，先根据用户id除以2取模，进行分库。
2.然后根据用户生产的订单id，再次除以2取模，进行分表。
3.生产100个订单
@SpringBootTest
public class ShadingMapperTests {
@Autowired(required = false)
private OrderMapper orderMapper;
/*模拟10个用户*/
private Long[] userIds = new Long[]{0L, 1L, 2L, 3L, 4L, 5L, 7L, 8L, 9L};
@Test
public void testInsert() {
for (int i = 1; i <= 100; i++) {
Order order = new Order();
order.setUserId(userIds[new Random().nextInt(9)]);
order.setOrderName("orderName" + i);
order.setInsertDate(new Date());
orderMapper.insert(order);
}
}
}
查看结果
user_id为偶数，且id为偶数的进入db0 table0表
image.png
总共产生100条订单数据
分析
2021-08-05 23:09:22.762 INFO 1122 --- [ main] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@20cff21e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@49b89425), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@49b89425, columnNames=[id, order_name, user_id, insert_date], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=73, stopIndex=73, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=76, stopIndex=76, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=79, stopIndex=79, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=82, stopIndex=82, parameterMarkerIndex=3)], parameters=[1423300102633619458, orderName99, 7, 2021-08-05 23:09:22.762])], generatedKeyContext=Optional.empty)
2021-08-05 23:09:22.762 INFO 1122 --- [ main] ShardingSphere-SQL : Actual SQL: test1 ::: INSERT INTO t_order0 ( id,
order_name,
user_id,
insert_date ) VALUES (?, ?, ?, ?) ::: [1423300102633619458, orderName99, 7, 2021-08-05 23:09:22.762]
2021-08-05 23:09:22.769 INFO 1122 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( id,
order_name,
user_id,
insert_date ) VALUES ( ?,
?,
?,
? )
逻辑sql语句
Logic SQL: INSERT INTO t_order ( id,
order_name,
user_id,
insert_date ) VALUES ( ?,
?,
?,
? )
实际sql语句
Actual SQL: test0 ::: INSERT INTO t_order1 ( id,
order_name,
user_id,
insert_date ) VALUES (?, ?, ?, ?) ::: [1423300096988086273, orderName2, 2, 2021-08-05 23:09:21.415]
网友评论