一、背景
1.1 环境信息
组件 | 版本版本 |
---|
windows | 10 | mysql | Mysql 8.0.25 | springboot | 2.4.10 | sharding-jdbc-spring-boot-starter | 4.0.0-RC1 |
1.2 前言
前面已经使用了sharding-jdbc的分库分表功能,sharding-jdbc的另一大功能就是读写分离。对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说,将数据库拆分为主库和从库,主库负责处理事务性的增删改操作,从库负责处理查询操作,能够有效的避免由数据更新导致的行锁,使得整个系统的查询性能得到极大的改善。
我们对前面讲的分库分表的案例再进行读写分离设计。开发前我们需要安装一个从库,并配置好主从同步(参考)。
1.3 表结构信息
1.4 数据库信息
类型 | 信息 |
---|
|
mysql服务 | 主服务(localhsot:3306) | 从服务(localhsot:3307) |
---|
|
ds1逻辑数据库 | 主库(m1):localhsot:3306/product_db_1 | 从库(s1):localhsot:3307/product_db_1 |
---|
ds2逻辑数据库 | 主库(m2):localhsot:3306/product_db_2 | 从库(s2):localhsot:3307/product_db_2 |
---|
- 逻辑数据库ds1由主库m1(3306/product_db_1)和从库s1(3307/product_db_1)组成。
- 逻辑数据库ds1由主库m2(3306/product_db_2)和从库s2(3307/product_db_2)组成。
- 主服务(localhsot:3306)和从服务(localhsot:3307)之间要配置主从同步。
- 产品库中的产品表(product_info)和产品描述表(product_desc)根据店铺id取模的方式水平分库,分为product_db_1和product_db_2两个库。
- 产品表(product_info)和产品描述表(product_desc)又根据产品id取模的方式进行水平分表。product_info分为product_info_1和product_info_2,product_desc分为product_desc_1和product_desc_2。
- 产品表(product_info)和产品描述表(product_desc)水平分库和水平分表的方式都相同,因此可以作为绑定表。
- 城市表(t_city)是配置表,数据量小。是作为和产品表关联查询产品信息的表。作为广播表。
二、代码实现
2.1 概述
sharding-jdbc实现读写分离,只需要引入对应的jar依赖,在配置文件里配置即可。这里的分表策略使用shrding-jdbc的行表达式分片策略实现。行表达式使用的是Groovy的表达式,提供对SQL语句中的=和IN的分片操作支持,只支持单分片键。对于简单的分片算法,可以通过简单的配置使用,从而避免繁琐的Java代码开发,如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8,而分成8张表,表名称为t_user_0到t_user_7。
其他分片策略请参考:
2.2 主要代码
源码地址
2.2.1 pom.xml
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.2</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.6.5</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
</dependencies>
2.2.3 application.properties
server.port=8081
spring.profiles.active=dxfl
spring.main.allow-bean-definition-overriding=true
mybatis.mapper-locations=classpath:mapper/*Mapper.xml
mybatis.type-aliases-package=com.lh.boot.sharding.jdbc.entity
pagehelper.helper-dialect=mysql
pagehelper.reasonable=true
pagehelper.offset-as-page-num=true
pagehelper.row-bounds-with-count=true
2.2.4 application-dxfl.properties
datasource.type=com.alibaba.druid.pool.DruidDataSource
datasource.driver-class-name=com.mysql.cj.jdbc.Driver
datasource.filters=stat
datasource.maxActive=100
datasource.initialSize=40
datasource.maxWait=10000
datasource.minIdle=40
datasource.timeBetweenEvictionRunsMillis=60000
datasource.minEvictableIdleTimeMillis=300000
datasource.validationQuery=SELECT 1
datasource.testWhileIdle=true
datasource.testOnBorrow=false
datasource.testOnReturn=false
datasource.poolPreparedStatements=true
datasource.maxOpenPreparedStatements=20
spring.shardingsphere.datasource.names=m1,m2,s1,s2
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/product_db_1?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true&noAccessToProcedureBodies=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456
spring.shardingsphere.datasource.m1.type=${datasource.type}
spring.shardingsphere.datasource.m1.driver-class-name=${datasource.driver-class-name}
spring.shardingsphere.datasource.m1.filters=${datasource.filters}
spring.shardingsphere.datasource.m1.maxActive=${datasource.maxActive}
spring.shardingsphere.datasource.m1.initialSize=${datasource.initialSize}
spring.shardingsphere.datasource.m1.maxWait=${datasource.maxWait}
spring.shardingsphere.datasource.m1.minIdle=${datasource.minIdle}
spring.shardingsphere.datasource.m1.timeBetweenEvictionRunsMillis=${datasource.timeBetweenEvictionRunsMillis}
spring.shardingsphere.datasource.m1.minEvictableIdleTimeMillis=${datasource.minEvictableIdleTimeMillis}
spring.shardingsphere.datasource.m1.validationQuery=${datasource.validationQuery}
spring.shardingsphere.datasource.m1.testWhileIdle=${datasource.testWhileIdle}
spring.shardingsphere.datasource.m1.testOnBorrow=${datasource.testOnBorrow}
spring.shardingsphere.datasource.m1.testOnReturn=${datasource.testOnReturn}
spring.shardingsphere.datasource.m1.poolPreparedStatements=${datasource.poolPreparedStatements}
spring.shardingsphere.datasource.m1.maxOpenPreparedStatements=${datasource.maxOpenPreparedStatements}
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/product_db_2?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true&noAccessToProcedureBodies=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=123456
spring.shardingsphere.datasource.m2.type=${datasource.type}
spring.shardingsphere.datasource.m2.driver-class-name=${datasource.driver-class-name}
spring.shardingsphere.datasource.m2.filters=${datasource.filters}
spring.shardingsphere.datasource.m2.maxActive=${datasource.maxActive}
spring.shardingsphere.datasource.m2.initialSize=${datasource.initialSize}
spring.shardingsphere.datasource.m2.maxWait=${datasource.maxWait}
spring.shardingsphere.datasource.m2.minIdle=${datasource.minIdle}
spring.shardingsphere.datasource.m2.timeBetweenEvictionRunsMillis=${datasource.timeBetweenEvictionRunsMillis}
spring.shardingsphere.datasource.m2.minEvictableIdleTimeMillis=${datasource.minEvictableIdleTimeMillis}
spring.shardingsphere.datasource.m2.validationQuery=${datasource.validationQuery}
spring.shardingsphere.datasource.m2.testWhileIdle=${datasource.testWhileIdle}
spring.shardingsphere.datasource.m2.testOnBorrow=${datasource.testOnBorrow}
spring.shardingsphere.datasource.m2.testOnReturn=${datasource.testOnReturn}
spring.shardingsphere.datasource.m2.poolPreparedStatements=${datasource.poolPreparedStatements}
spring.shardingsphere.datasource.m2.maxOpenPreparedStatements=${datasource.maxOpenPreparedStatements}
spring.shardingsphere.datasource.s1.url=jdbc:mysql://localhost:3307/product_db_1?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true&noAccessToProcedureBodies=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.s1.username=root
spring.shardingsphere.datasource.s1.password=123456
spring.shardingsphere.datasource.s1.type=${datasource.type}
spring.shardingsphere.datasource.s1.driver-class-name=${datasource.driver-class-name}
spring.shardingsphere.datasource.s1.filters=${datasource.filters}
spring.shardingsphere.datasource.s1.maxActive=${datasource.maxActive}
spring.shardingsphere.datasource.s1.initialSize=${datasource.initialSize}
spring.shardingsphere.datasource.s1.maxWait=${datasource.maxWait}
spring.shardingsphere.datasource.s1.minIdle=${datasource.minIdle}
spring.shardingsphere.datasource.s1.timeBetweenEvictionRunsMillis=${datasource.timeBetweenEvictionRunsMillis}
spring.shardingsphere.datasource.s1.minEvictableIdleTimeMillis=${datasource.minEvictableIdleTimeMillis}
spring.shardingsphere.datasource.s1.validationQuery=${datasource.validationQuery}
spring.shardingsphere.datasource.s1.testWhileIdle=${datasource.testWhileIdle}
spring.shardingsphere.datasource.s1.testOnBorrow=${datasource.testOnBorrow}
spring.shardingsphere.datasource.s1.testOnReturn=${datasource.testOnReturn}
spring.shardingsphere.datasource.s1.poolPreparedStatements=${datasource.poolPreparedStatements}
spring.shardingsphere.datasource.s1.maxOpenPreparedStatements=${datasource.maxOpenPreparedStatements}
spring.shardingsphere.datasource.s2.url=jdbc:mysql://localhost:3307/product_db_2?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true&noAccessToProcedureBodies=true&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.s2.username=root
spring.shardingsphere.datasource.s2.password=123456
spring.shardingsphere.datasource.s2.type=${datasource.type}
spring.shardingsphere.datasource.s2.driver-class-name=${datasource.driver-class-name}
spring.shardingsphere.datasource.s2.filters=${datasource.filters}
spring.shardingsphere.datasource.s2.maxActive=${datasource.maxActive}
spring.shardingsphere.datasource.s2.initialSize=${datasource.initialSize}
spring.shardingsphere.datasource.s2.maxWait=${datasource.maxWait}
spring.shardingsphere.datasource.s2.minIdle=${datasource.minIdle}
spring.shardingsphere.datasource.s2.timeBetweenEvictionRunsMillis=${datasource.timeBetweenEvictionRunsMillis}
spring.shardingsphere.datasource.s2.minEvictableIdleTimeMillis=${datasource.minEvictableIdleTimeMillis}
spring.shardingsphere.datasource.s2.validationQuery=${datasource.validationQuery}
spring.shardingsphere.datasource.s2.testWhileIdle=${datasource.testWhileIdle}
spring.shardingsphere.datasource.s2.testOnBorrow=${datasource.testOnBorrow}
spring.shardingsphere.datasource.s2.testOnReturn=${datasource.testOnReturn}
spring.shardingsphere.datasource.s2.poolPreparedStatements=${datasource.poolPreparedStatements}
spring.shardingsphere.datasource.s2.maxOpenPreparedStatements=${datasource.maxOpenPreparedStatements}
spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=s1
spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=m2
spring.shardingsphere.sharding.master-slave-rules.ds2.slave-data-source-names=s2
spring.shardingsphere.sharding.tables.product_info.actual-data-nodes=ds$->{1..2}.product_info_$->{1..2}
spring.shardingsphere.sharding.tables.product_info.database-strategy.inline.sharding-column=store_id
spring.shardingsphere.sharding.tables.product_info.database-strategy.inline.algorithm-expression=ds$->{store_id % 2 + 1}
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.sharding-column=product_id
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.algorithm-expression=product_info_$->{product_id % 2 +1}
spring.shardingsphere.sharding.tables.product_desc.actual-data-nodes=ds$->{1..2}.product_desc_$->{1..2}
spring.shardingsphere.sharding.tables.product_desc.database-strategy.inline.sharding-column=store_id
spring.shardingsphere.sharding.tables.product_desc.database-strategy.inline.algorithm-expression=ds$->{store_id % 2 + 1}
spring.shardingsphere.sharding.tables.product_desc.table-strategy.inline.sharding-column=product_id
spring.shardingsphere.sharding.tables.product_desc.table-strategy.inline.algorithm-expression=product_desc_$->{product_id % 2 +1}
spring.shardingsphere.sharding.binding-tables[0]=product_info,product_desc
spring.shardingsphere.sharding.broadcast-tables=t_city
spring.shardingsphere.props.sql.show=true
logging.level.root=info
logging.level.org.springframework.web=info
logging.level.druid.sql=debug
logging.level.com.lh.boot.sharding.jdbc.mapper=debug
2.2.5 ProductInfoMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lh.boot.sharding.jdbc.mapper.ProductInfoMapper">
<resultMap id="BaseResultMap" type="com.lh.boot.sharding.jdbc.entity.ProductInfo">
<constructor>
<idArg column="product_id" javaType="java.lang.Long" jdbcType="BIGINT"/>
<arg column="store_id" javaType="java.lang.Long" jdbcType="BIGINT"/>
<arg column="price" javaType="java.math.BigDecimal" jdbcType="DECIMAL"/>
<arg column="product_name" javaType="java.lang.String" jdbcType="VARCHAR"/>
<arg column="city" javaType="java.lang.String" jdbcType="VARCHAR"/>
<arg column="status" javaType="java.lang.String" jdbcType="VARCHAR"/>
</constructor>
</resultMap>
<sql id="Base_Column_List">
product_id, store_id, price, product_name, city, status
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from product_info
where product_id = #{productId,jdbcType=BIGINT}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
delete from product_info
where product_id = #{productId,jdbcType=BIGINT}
</delete>
<insert id="insert" parameterType="productInfo">
insert into product_info (product_id, store_id, price,
product_name, city, status
)
values (#{productId,jdbcType=BIGINT}, #{storeId,jdbcType=BIGINT}, #{price,jdbcType=DECIMAL},
#{productName,jdbcType=VARCHAR}, #{city,jdbcType=VARCHAR}, #{status,jdbcType=VARCHAR}
)
</insert>
<insert id="insertSelective" parameterType="productInfo">
insert into product_info
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="productId != null">
product_id,
</if>
<if test="storeId != null">
store_id,
</if>
<if test="price != null">
price,
</if>
<if test="productName != null">
product_name,
</if>
<if test="city != null">
city,
</if>
<if test="status != null">
status,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="productId != null">
#{productId,jdbcType=BIGINT},
</if>
<if test="storeId != null">
#{storeId,jdbcType=BIGINT},
</if>
<if test="price != null">
#{price,jdbcType=DECIMAL},
</if>
<if test="productName != null">
#{productName,jdbcType=VARCHAR},
</if>
<if test="city != null">
#{city,jdbcType=VARCHAR},
</if>
<if test="status != null">
#{status,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="productInfo">
update product_info
<set>
<if test="storeId != null">
store_id = #{storeId,jdbcType=BIGINT},
</if>
<if test="price != null">
price = #{price,jdbcType=DECIMAL},
</if>
<if test="productName != null">
product_name = #{productName,jdbcType=VARCHAR},
</if>
<if test="city != null">
city = #{city,jdbcType=VARCHAR},
</if>
<if test="status != null">
status = #{status,jdbcType=VARCHAR},
</if>
</set>
where product_id = #{productId,jdbcType=BIGINT}
</update>
<update id="updateByPrimaryKey" parameterType="productInfo">
update product_info
set store_id = #{storeId,jdbcType=BIGINT},
price = #{price,jdbcType=DECIMAL},
product_name = #{productName,jdbcType=VARCHAR},
city = #{city,jdbcType=VARCHAR},
status = #{status,jdbcType=VARCHAR}
where product_id = #{productId,jdbcType=BIGINT}
</update>
<resultMap type="com.lh.boot.sharding.jdbc.entity.vo.ProductInfoVO" id="ProductInfoResultMap">
<id property="productId" column="product_id"/>
<result property="storeId" column="store_id"/>
<result property="price" column="price"/>
<result property="productName" column="product_name"/>
<result property="city" column="city"/>
<result property="cityName" column="city_name"/>
<result property="status" column="status"/>
<result property="productSize" column="product_size"/>
<result property="stock" column="stock"/>
<result property="descInfo" column="desc_info"/>
</resultMap>
<select id="selectProductInfoVOList" resultMap="ProductInfoResultMap">
select
pri.product_id ,
pri.product_name ,
pri.price ,
pri.status ,
pri.store_id ,
pri.city ,
pd.desc_info ,
pd.product_size ,
pd.stock ,
tc.city_name
from
product_info pri
left join product_desc pd on
pd.product_id = pri.product_id
left join t_city tc on
tc.city_code = pri.city
order by pri.product_id
</select>
</mapper>
2.2.6 ProductInfoMapper.java
package com.lh.boot.sharding.jdbc.mapper;
import com.lh.boot.sharding.jdbc.entity.ProductInfo;
import com.lh.boot.sharding.jdbc.entity.vo.ProductInfoVO;
import java.util.List;
public interface ProductInfoMapper {
int deleteByPrimaryKey(Long productId);
int insert(ProductInfo record);
int insertSelective(ProductInfo record);
ProductInfo selectByPrimaryKey(Long productId);
int updateByPrimaryKeySelective(ProductInfo record);
int updateByPrimaryKey(ProductInfo record);
List<ProductInfoVO> selectProductInfoVOList();
}
三、个人总结
- 在使用sharding-jdbc读写分离功能前,需要我们自己配置数据库的主从同步。
- sharding-jdbc会分析我们要执行的sql语句,然后将增删改操作路由到主库,就是我们案例上面的m1和m2数据库。会将查询操作路由到从库,即s1和s2。
- sharding-jdbc的读写分离和分库分表可以分开来使用,我在平常开发中可以根据自己的具体业务选择合适的方式即可。
|