您好,登錄后才能下訂單哦!
本篇內容介紹了“SpringBoot怎么使用Sharding-JDBC實現數據分片和讀寫分離”的有關知識,在實際案例的操作過程中,不少人都會遇到這樣的困境,接下來就讓小編帶領大家學習一下如何處理這些情況吧!希望大家仔細閱讀,能夠學有所成!
一、Sharding-JDBC簡介
二、具體的實現方式
1、maven引用
2、數據庫準備
3、Spring配置
4、精準分片算法和范圍分片算法的Java代碼
5、測試
Sharding-JDBC是Sharding-Sphere的一個產品,它有三個產品,分別是Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar,這三個產品提供了標準化的數據分片、讀寫分離、柔性事務和數據治理功能。我們這里用的是Sharding-JDBC,所以想了解后面兩個產品的話可以去它們官網查看。
Sharding-JDBC為輕量級Java框架,使用客戶端直連數據庫,以jar包形式提供服務,無需額外部署和依賴,可理解為增強版的JDBC驅動,兼容性特別強。適用的ORM框架有JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC;第三方的數據庫連接池有DBCP, C3P0, BoneCP, Druid等;支持的數據庫有MySQL,Oracle,SQLServer和PostgreSQL;多樣化的配置文件Java,yaml,Spring Boot ,Spring命名空間。其實這里說的都是廢話,大家可以不看,下面我們動手開始正式配置。
我這里用的配置方式是Spring命名空間配置,所以只需要引用sharding-jdbc-spring-namespace就可以了,還有要注意的是我用的不是當當網的sharding,注意groupId是io.shardingsphere。如果用的是其它配置方式可以去http://maven.aliyun.com/nexus/#nexus-search;quick~io.shardingsphere網站查找相應maven引用
<dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-namespace</artifactId> <version>3.0.0.M1</version> </dependency>
我這里用的是mysql數據庫,根據我們項目的具體需求,我準備了三個主庫和對應的從庫。模擬的主庫名有master,暫時沒有做對應從庫,所以對應的從庫還是指向master;第二個主庫有master_1,對應的從庫有master_1_slaver_1,master_1_slave_2;第三個主庫有master_2,對應的從庫有master_2_slave_1,master_2_slave_2。
數據庫中的表也做了分表,下面是對應的mysql截圖。
這第一幅圖上的主從庫都應該在不同的服務器上的,但這里只是為了模擬所以就建在了本地服務器上了。我們讀寫分離中的寫操作只會發生在主庫上,從庫會自動同步主庫上的數據并為讀提供數據。數據庫的主從復制在上篇博文中做了詳細的介紹,大家可以去看看http://www.mlszssj.com/article/226077.htm
這幅圖作為我們本來的主庫,下面做的分庫和分表都是基于這個庫中的訂單表分的。所以分庫中的表只有訂單表和訂單明細表。
第三幅圖截的是第二個主庫,里面對訂單和訂單明細表做了分表操作,具體的分片策略和分片算法下面再做介紹。第三個主表和第二個主表是一樣的,所有的從表都和對應的主表是一致的。
數據庫信息配置文件db.properties配置可以配置兩份,分為開發版和測試版,如下:
# master Master.url=jdbc:mysql://localhost:3306/master?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=true Master.username=root Master.password=123456 Slave.url=jdbc:mysql://localhost:3306/master?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=true Slave.username=root Slave.password=123456 # maste_1 Master_1.url=jdbc:mysql://localhost:3306/master_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=true Master_1.username=root Master_1.password=123456 Master_1_Slave_1.url=jdbc:mysql://localhost:3306/master_1_slave_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=true Master_1_Slave_1.username=root Master_1_Slave_1.password=123456 Master_1_Slave_2.url=jdbc:mysql://localhost:3306/master_1_slave_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=true Master_1_Slave_2.username=root Master_1_Slave_2.password=123456 # master_2 Master_2.url=jdbc:mysql://localhost:3306/master_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=true Master_2.username=root Master_2.password=123456 Master_2_Slave_1.url=jdbc:mysql://localhost:3306/master_2_slave_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=true Master_2_Slave_1.username=root Master_2_Slave_1.password=123456 Master_2_Slave_2.url=jdbc:mysql://localhost:3306/master_2_slave_2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=true Master_2_Slave_2.username=root Master_2_Slave_2.password=123456
Spring對應的配置:
Spring-Sphere官網中的demo里用的都是行表達式的分片策略,但是行表達式的策略不利于數據庫和表的橫向擴展,所以我這里用的是標準分片策略,精準分片算法和范圍分片算法。因為我們項目中暫時用的分片鍵都是user_id單一鍵,所以說不存在復合分片策略,也用不到Hint分片策略,行表達式分片策略和不分片策略。
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:sharding="http://shardingsphere.io/schema/shardingsphere/sharding" xmlns:master-slave="http://shardingsphere.io/schema/shardingsphere/masterslave" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://shardingsphere.io/schema/shardingsphere/sharding http://shardingsphere.io/schema/shardingsphere/sharding/sharding.xsd http://shardingsphere.io/schema/shardingsphere/masterslave http://shardingsphere.io/schema/shardingsphere/masterslave/master-slave.xsd"> <context:component-scan base-package="com.jihao" /> <!-- db.properties數據庫信息配置 --> <bean id="property" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="location" value="classpath:property/db_dev.properties" /> </bean> <!-- 主庫 --> <bean id="master" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="${Master.url}"/> <property name="username" value="${Master.username}"/> <property name="password" value="${Master.password}"/> </bean> <!-- 主庫的從庫 --> <bean id="slave" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="${Slave.url}"/> <property name="username" value="${Slave.username}"/> <property name="password" value="${Slave.password}"/> </bean> <!-- 主庫的分庫1 --> <bean id="master_1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="${Master_1.url}"/> <property name="username" value="${Master_1.username}"/> <property name="password" value="${Master_1.password}"/> </bean> <!-- 分庫1的從庫1 --> <bean id="master_1_slave_1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="${Master_1_Slave_1.url}"/> <property name="username" value="${Master_1_Slave_1.username}"/> <property name="password" value="${Master_1_Slave_1.password}"/> </bean> <!-- 分庫1的從庫2 --> <bean id="master_1_slave_2" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="${Master_1_Slave_2.url}"/> <property name="username" value="${Master_1_Slave_2.username}"/> <property name="password" value="${Master_1_Slave_2.password}"/> </bean> <!-- 主庫的分庫2 --> <bean id="master_2" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="${Master_2.url}"/> <property name="username" value="${Master_2.username}"/> <property name="password" value="${Master_2.password}"/> </bean> <!-- 分庫2的從庫1 --> <bean id="master_2_slave_1" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="${Master_2_Slave_1.url}"/> <property name="username" value="${Master_2_Slave_1.username}"/> <property name="password" value="${Master_2_Slave_1.password}"/> </bean> <!-- 分庫2的從庫2 --> <bean id="master_2_slave_2" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="${Master_2_Slave_2.url}"/> <property name="username" value="${Master_2_Slave_2.username}"/> <property name="password" value="${Master_2_Slave_2.password}"/> </bean> <!-- 主從關系配置 --> <bean id="randomStrategy" class="io.shardingsphere.core.api.algorithm.masterslave.RandomMasterSlaveLoadBalanceAlgorithm" /> <master-slave:data-source id="ms_master" master-data-source-name="master" slave-data-source-names="slave" strategy-ref="randomStrategy" /> <master-slave:data-source id="ms_master_1" master-data-source-name="master_1" slave-data-source-names="master_1_slave_1, master_1_slave_2" strategy-ref="randomStrategy" /> <master-slave:data-source id="ms_master_2" master-data-source-name="master_2" slave-data-source-names="master_2_slave_1, master_2_slave_2" strategy-ref="randomStrategy" /> <!-- 分庫策略 精確分片算法 --> <bean id="preciseDatabaseStrategy" class="com.jihao.algorithm.PreciseModuleDatabaseShardingAlgorithm" /> <!-- 分庫策略 范圍分片算法 --> <bean id="rangeDatabaseStrategy" class="com.jihao.algorithm.RangeModuleDatabaseShardingAlgorithm" /> <!-- 分表策略 精確分片算法 --> <bean id="preciseTableStrategy" class="com.jihao.algorithm.PreciseModuleTableShardingAlgorithm" /> <!-- 分表策略 范圍分片算法--> <bean id="rangeTableStrategy" class="com.jihao.algorithm.RangeModuleTableShardingAlgorithm" /> <sharding:standard-strategy id="databaseStrategy" sharding-column="user_id" precise-algorithm-ref="preciseDatabaseStrategy" range-algorithm-ref="rangeDatabaseStrategy" /> <!-- 分表策略 --> <sharding:standard-strategy id="tableStrategy" sharding-column="user_id" precise-algorithm-ref="preciseTableStrategy" range-algorithm-ref="rangeTableStrategy" /> <!-- 行表達式算法 --> <!-- <sharding:inline-strategy id="databaseStrategy" sharding-column="user_id" algorithm-expression="demo_ds_ms_$->{user_id % 2}" /> <sharding:inline-strategy id="orderTableStrategy" sharding-column="order_id" algorithm-expression="t_order_$->{order_id % 2}" /> <sharding:inline-strategy id="orderItemTableStrategy" sharding-column="order_item_id" algorithm-expression="t_order_item_$->{order_item_id % 2}" /> --> <sharding:data-source id="shardingDataSource"> <sharding:sharding-rule data-source-names="ms_master,ms_master_1,ms_master_2"> <sharding:table-rules> <sharding:table-rule logic-table="t_order" actual-data-nodes="ms_master_$->{1..2}.t_order_$->{1..3}" database-strategy-ref="databaseStrategy" table-strategy-ref="tableStrategy" generate-key-column-name="order_id"/> <sharding:table-rule logic-table="t_order_item" actual-data-nodes="ms_master_$->{1..2}.t_order_item_$->{1..3}" database-strategy-ref="databaseStrategy" table-strategy-ref="tableStrategy" generate-key-column-name="order_item_id"/> </sharding:table-rules> </sharding:sharding-rule> </sharding:data-source> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="shardingDataSource" /> </bean> <tx:annotation-driven /> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <!-- 用于在控制臺打印sql(不需要的可以注釋掉這一行) --> <property name="configLocation" value="classpath:log/mybatis-config.xml"></property> <property name="dataSource" ref="shardingDataSource"/> <property name="mapperLocations" value="classpath*:com/jihao/mapper/*.xml"/> </bean> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.jihao"/> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/> </bean> </beans>
標準分片策略,精準分片算法
package com.jihao.algorithm; import io.shardingsphere.core.api.algorithm.sharding.PreciseShardingValue; import io.shardingsphere.core.api.algorithm.sharding.standard.PreciseShardingAlgorithm; import java.util.Collection; import com.alibaba.fastjson.JSON; /** * 自定義標準分片策略,使用精確分片算法(=與IN) * @author JiHao * */ public class PreciseModuleDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long>{ @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> preciseShardingValue) { System.out.println("collection:" + JSON.toJSONString(availableTargetNames) + ",preciseShardingValue:" + JSON.toJSONString(preciseShardingValue)); for (String name : availableTargetNames) { // =與IN中分片鍵對應的值 String value = String.valueOf(preciseShardingValue.getValue()); // 分庫的后綴 int i = 1; // 求分庫后綴名的遞歸算法 if (name.endsWith("_" + countDatabaseNum(Long.parseLong(value), i))) { return name; } } throw new UnsupportedOperationException(); } /** * 計算該量級的數據在哪個數據庫 * @return */ private String countDatabaseNum(long columnValue, int i){ // ShardingSphereConstants每個庫中定義的數據量 long left = ShardingSphereConstants.databaseAmount * (i-1); long right = ShardingSphereConstants.databaseAmount * i; if(left < columnValue && columnValue <= right){ return String.valueOf(i); }else{ i++; return countDatabaseNum(columnValue, i); } } }
標準分片策略,范圍分片算法
package com.jihao.algorithm; import io.shardingsphere.core.api.algorithm.sharding.RangeShardingValue; import io.shardingsphere.core.api.algorithm.sharding.standard.RangeShardingAlgorithm; import java.util.ArrayList; import java.util.Collection; import java.util.List; import com.alibaba.fastjson.JSON; import com.google.common.collect.Range; /** * 自定義標準分庫策略,使用范圍分片算法(BETWEEN AND) * @author JiHao * */ public class RangeModuleDatabaseShardingAlgorithm implements RangeShardingAlgorithm<Long>{ @Override public Collection<String> doSharding( Collection<String> availableTargetNames, RangeShardingValue<Long> rangeShardingValue) { System.out.println("Range collection:" + JSON.toJSONString(availableTargetNames) + ",rangeShardingValue:" + JSON.toJSONString(rangeShardingValue)); Collection<String> collect = new ArrayList<>(); Range<Long> valueRange = rangeShardingValue.getValueRange(); // BETWEEN AND中分片鍵對應的最小值 long lowerEndpoint = Long.parseLong(String.valueOf(valueRange.lowerEndpoint())); // BETWEEN AND中分片鍵對應的最大值 long upperEndpoint = Long.parseLong(String.valueOf(valueRange.upperEndpoint())); // 分表的后綴 int i = 1; List<Integer> arrs = new ArrayList<Integer>(); // 求分表后綴名的遞歸算法 List<Integer> list = countDatabaseNum(i, lowerEndpoint, upperEndpoint, arrs); for (Integer integer : list) { for (String each : availableTargetNames) { if (each.endsWith("_" + integer)) { collect.add(each); } } } return collect; } /** * 計算該量級的數據在哪個表 * @param columnValue * @param i * @param lowerEndpoint 最小區間 * @param upperEndpoint 最大區間 * @return */ private List<Integer> countDatabaseNum(int i, long lowerEndpoint, long upperEndpoint, List<Integer> arrs){ long left = ShardingSphereConstants.databaseAmount * (i-1); long right = ShardingSphereConstants.databaseAmount * i; // 區間最大值小于分庫最大值 if(left < upperEndpoint && upperEndpoint <= right){ arrs.add(i); return arrs; }else{ if(left < lowerEndpoint && lowerEndpoint <= right){ arrs.add(i); } i++; return countDatabaseNum(i, lowerEndpoint, upperEndpoint, arrs); } } }
分庫的策略用的和分庫的代碼是一樣的,不同之處就是分庫用的是databaseAmount,分表用的是tableAmount。下面的ShardingSphereConstants的代碼。
package com.jihao.algorithm; /** * ShardingSphere中用到的常量 * @author JiHao * */ public class ShardingSphereConstants { /** * 訂單、優惠券相關的表,按用戶數量分庫,64w用戶數據為一個庫 * (0,64w] */ public static int databaseAmount = 640000; /** * 一個訂單表里存10000的用戶訂單 * (0,1w] */ public static int tableAmount = 10000; }
到這里所有的配置基本上都已經完成了,下面的測試。
下面是測試的mybatis的測試文件,都是最基礎的就不講解了。
<?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.jihao.dao.TestShardingMapper"> <resultMap id="BaseResultMap" type="com.jihao.entity.Order"> <id column="order_id" jdbcType="INTEGER" property="orderId" /> <result column="user_id" jdbcType="INTEGER" property="userId" /> <result column="status" jdbcType="INTEGER" property="status" /> </resultMap> <insert id="insert" parameterType="com.jihao.entity.Order" useGeneratedKeys="true" keyProperty="orderId"> INSERT INTO t_order ( user_id, status ) VALUES ( #{userId,jdbcType=INTEGER}, #{status,jdbcType=VARCHAR} ) </insert> <insert id="insertItem" useGeneratedKeys="true" keyProperty="orderItemId"> INSERT INTO t_order_item ( order_id, user_id ) VALUES ( #{orderId,jdbcType=INTEGER}, #{userId,jdbcType=INTEGER} ) </insert> <select id="searchOrder" resultMap="BaseResultMap"> SELECT * from t_order </select> <select id="queryWithEqual" resultMap="BaseResultMap"> SELECT * FROM t_order WHERE user_id=51 </select> <select id="queryWithIn" resultMap="BaseResultMap"> SELECT * FROM t_order WHERE user_id IN (50, 51) </select> <select id="queryWithBetween" resultMap="BaseResultMap"> SELECT * FROM t_order WHERE user_id between 10000 and 30000 </select> <select id="queryUser" resultType="Map"> SELECT * FROM t_user </select> </mapper>
下面對應的mapper的Java代碼
package com.jihao.dao; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.Mapper; import com.jihao.entity.Order; import com.jihao.entity.OrderItem; @Mapper public interface TestShardingMapper { int insert(Order record); int insertItem(OrderItem record); List<Order> searchOrder(); List<Order> queryWithEqual(); List<Order> queryWithIn(); List<Order> queryWithBetween(); List<Map<String, Object>> queryUser(); }
下面是對應的訂單entity代碼
package com.jihao.entity; /** * 訂單 * @author JiHao */ public class Order { private Long orderId; private Integer userId; private String status; public Long getOrderId() { return orderId; } public void setOrderId(Long orderId) { this.orderId = orderId; } public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getStatus() { return status; } public void setStatus(String status) { this.status = status; } }
下面是對應的訂單明細entity代碼
package com.jihao.entity; /** * 測試分片 * @author JiHao */ public class OrderItem { private Long orderItemId; private Long orderId; private Integer userId; public Long getOrderId() { return orderId; } public void setOrderId(Long orderId) { this.orderId = orderId; } public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public Long getOrderItemId() { return orderItemId; } public void setOrderItemId(Long orderItemId) { this.orderItemId = orderItemId; } }
下面是測試的controller,并沒有寫Junit測試。
package com.jihao.controller.test; import java.util.List; import java.util.Map; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.ResponseBody; import com.jihao.dao.TestShardingMapper; import com.jihao.entity.Order; import com.jihao.entity.OrderItem; import com.jihao.result.Result; import com.jihao.result.ResultUtil; /** * 測試分片 * @author JiHao * */ @Controller @RequestMapping(value = "test") public class TestShardingController { @Autowired private TestShardingMapper testShardingMapper; /** * 測試添加 * @return */ @ResponseBody @GetMapping(value = "/testAdd") public String testAdd(){ for (int i = 0; i < 10; i++) { Order order = new Order(); // order.setUserId(50); // order.setUserId(51); // order.setUserId(10001); order.setUserId(20001); order.setStatus("INSERT_TEST"); int count = testShardingMapper.insert(order); System.out.println(count); long orderId = order.getOrderId(); System.out.println(order.getOrderId()); OrderItem item = new OrderItem(); item.setOrderId(orderId); // order.setUserId(50); // order.setUserId(51); // order.setUserId(10001); order.setUserId(20001); testShardingMapper.insertItem(item); } return "success"; } /** * 測試搜索 * @return */ @ResponseBody @GetMapping(value = "/testSearch") public Result searchData(){ List<Order> list = testShardingMapper.searchOrder(); System.out.println(list.size() + " all"); List<Order> list1 = testShardingMapper.queryWithIn(); System.out.println(list1.size() + " In"); List<Order> list2 = testShardingMapper.queryWithEqual(); System.out.println(list2.size() + " Equal"); List<Order> list3 = testShardingMapper.queryWithBetween(); System.out.println(list3.size() + " Between"); List<Map<String, Object>> list4 = testShardingMapper.queryUser(); System.out.println(list4.size() + " user"); return ResultUtil.success(null); } }
這里要重點提出來的是做搜索測試的時候,因為主從庫都在我本地服務器上,并沒有做主從復制,大家可以根據我上篇博文配置一下就可以順利操作了,如果沒有配置的話從庫里是不會有數據的,所以在做完寫操作時把主庫中的數據手動傳輸給從庫,這樣才能讀出數據。
“SpringBoot怎么使用Sharding-JDBC實現數據分片和讀寫分離”的內容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站,小編將為大家輸出更多高質量的實用文章!
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。