一、簡介

??在我之前的文章里，數(shù)據(jù)的分庫分表都是基于行表達(dá)式的方式來實(shí)現(xiàn)的，看起來也蠻好用，也挺簡單的，但是有時(shí)會有些復(fù)雜的規(guī)則，可能使用行表達(dá)式策略會很復(fù)雜或者實(shí)現(xiàn)不了，我們就講另外一種分片策略，精確分片算法，通常用來處理=或者in條件的情況比較多。

??本文示例大概架構(gòu)如下圖：

二、maven依賴

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.alian</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding-jdbc</name>
    <description>sharding-jdbc</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.15</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.12.0</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

??有些小伙伴的 druid 可能用的是 druid-spring-boot-starter

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.6</version>
</dependency>

??然后出現(xiàn)可能使用不了的各種問題，這個(gè)時(shí)候你只需要在主類上添加 @SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class}) 即可

package com.alian.shardingjdbc;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class})
@SpringBootApplication
public class ShardingJdbcApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShardingJdbcApplication.class, args);
    }

}

三、數(shù)據(jù)庫

3.1、創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE `sharding_9` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE `sharding_10` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE `sharding_11` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

3.2、創(chuàng)建表

??在數(shù)據(jù)庫sharding_9、sharding_10、sharding_11下面分別創(chuàng)建兩張表：tb_order_1和tb_order_2的結(jié)構(gòu)是一樣的

tb_order_1

CREATE TABLE `tb_order_1` (
  `order_id` bigint(20) NOT NULL COMMENT '主鍵',
  `user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '用戶id',
  `price` int unsigned NOT NULL DEFAULT '0' COMMENT '價(jià)格（單位：分）',
  `order_status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '訂單狀態(tài)(1:待付款,2:已付款,3:已取消)',
  `order_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  `title` varchar(100)  NOT NULL DEFAULT '' COMMENT '訂單標(biāo)題',
  PRIMARY KEY (`order_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_order_time` (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='訂單表';

tb_order_2

CREATE TABLE `tb_order_2` (
  `order_id` bigint(20) NOT NULL COMMENT '主鍵',
  `user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '用戶id',
  `price` int unsigned NOT NULL DEFAULT '0' COMMENT '價(jià)格（單位：分）',
  `order_status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '訂單狀態(tài)(1:待付款,2:已付款,3:已取消)',
  `order_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  `title` varchar(100)  NOT NULL DEFAULT '' COMMENT '訂單標(biāo)題',
  PRIMARY KEY (`order_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_order_time` (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='訂單表';

四、配置（二選一）

4.1、properties配置

application.properties

server.port=8899
server.servlet.context-path=/sharding-jdbc

# 允許定義相同的bean對象去覆蓋原有的
spring.main.allow-bean-definition-overriding=true
# 數(shù)據(jù)源名稱，多數(shù)據(jù)源以逗號分隔
spring.shardingsphere.datasource.names=ds1,ds2,ds3
# 未配置分片規(guī)則的表將通過默認(rèn)數(shù)據(jù)源定位
spring.shardingsphere.sharding.default-data-source-name=ds1

# sharding_9數(shù)據(jù)庫連接池類名稱
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
# sharding_9數(shù)據(jù)庫驅(qū)動類名
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
# sharding_9數(shù)據(jù)庫url連接
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.0.129:3306/sharding_9?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
# sharding_9數(shù)據(jù)庫用戶名
spring.shardingsphere.datasource.ds1.username=alian
# sharding_9數(shù)據(jù)庫密碼
spring.shardingsphere.datasource.ds1.password=123456

# sharding_10數(shù)據(jù)庫連接池類名稱
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
# sharding_10數(shù)據(jù)庫驅(qū)動類名
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
# sharding_10數(shù)據(jù)庫url連接
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://192.168.0.129:3306/sharding_10?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
# sharding_10數(shù)據(jù)庫用戶名
spring.shardingsphere.datasource.ds2.username=alian
# sharding_10數(shù)據(jù)庫密碼
spring.shardingsphere.datasource.ds2.password=123456

# sharding_11數(shù)據(jù)庫連接池類名稱
spring.shardingsphere.datasource.ds3.type=com.alibaba.druid.pool.DruidDataSource
# sharding_11數(shù)據(jù)庫驅(qū)動類名
spring.shardingsphere.datasource.ds3.driver-class-name=com.mysql.cj.jdbc.Driver
# sharding_11數(shù)據(jù)庫url連接
spring.shardingsphere.datasource.ds3.url=jdbc:mysql://192.168.0.129:3306/sharding_11?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
# sharding_11數(shù)據(jù)庫用戶名
spring.shardingsphere.datasource.ds3.username=alian
# sharding_11數(shù)據(jù)庫密碼
spring.shardingsphere.datasource.ds3.password=123456

# 采用精確分片策略：PreciseShardingStrategy，根據(jù)user_id的奇偶性來添加到不同的庫中
spring.shardingsphere.sharding.tables.tb_order.database-strategy.standard.sharding-column=user_id
spring.shardingsphere.sharding.tables.tb_order.database-strategy.standard.precise-algorithm-class-name=com.alian.shardingjdbc.algorithm.DatabasePreciseShardingAlgorithm
# 指定tb_order表的數(shù)據(jù)分布情況，配置數(shù)據(jù)節(jié)點(diǎn)，使用Groovy的表達(dá)式,邏輯表tb_order對應(yīng)的節(jié)點(diǎn)是：ds1.tb_order_1, ds1.tb_order_2,ds2.tb_order_1, ds2.tb_order_2,ds3.tb_order_1, ds3.tb_order_2
spring.shardingsphere.sharding.tables.tb_order.actual-data-nodes=ds$->{1..3}.tb_order_$->{1..2}

# 采用精確分片策略：PreciseShardingStrategy
# 指定tb_order表的分片策略中的分片鍵
spring.shardingsphere.sharding.tables.tb_order.table-strategy.standard.sharding-column=order_id
# 指定tb_order表的分片策略中的分片算法表達(dá)式,使用Groovy的表達(dá)式
spring.shardingsphere.sharding.tables.tb_order.table-strategy.standard.precise-algorithm-class-name=com.alian.shardingjdbc.algorithm.OrderTablePreciseShardingAlgorithm

# 指定tb_order表的主鍵為order_id
spring.shardingsphere.sharding.tables.tb_order.key-generator.column=order_id
# 指定tb_order表的主鍵生成策略為SNOWFLAKE
spring.shardingsphere.sharding.tables.tb_order.key-generator.type=SNOWFLAKE
# 指定雪花算法的worker.id
spring.shardingsphere.sharding.tables.tb_order.key-generator.props.worker.id=100
# 指定雪花算法的max.tolerate.time.difference.milliseconds
spring.shardingsphere.sharding.tables.tb_order.key-generator.props.max.tolerate.time.difference.milliseconds=20

# 打開sql輸出日志
spring.shardingsphere.props.sql.show=true

4.2、yml配置

application.yml

server:
  port: 8899
  servlet:
    context-path: /sharding-jdbc

spring:
  main:
    # 允許定義相同的bean對象去覆蓋原有的
    allow-bean-definition-overriding: true
  shardingsphere:
    props:
      sql:
       # 打開sql輸出日志
       show: true
    datasource:
      # 數(shù)據(jù)源名稱，多數(shù)據(jù)源以逗號分隔
      names: ds1,ds2,ds3
      ds1:
        # 數(shù)據(jù)庫連接池類名稱
        type: com.alibaba.druid.pool.DruidDataSource
        # 數(shù)據(jù)庫驅(qū)動類名
        driver-class-name: com.mysql.cj.jdbc.Driver
        # 數(shù)據(jù)庫url連接
        url: jdbc:mysql://192.168.0.129:3306/sharding_9?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
        # 數(shù)據(jù)庫用戶名
        username: alian
        # 數(shù)據(jù)庫密碼
        password: 123456
      ds2:
        # 數(shù)據(jù)庫連接池類名稱
        type: com.alibaba.druid.pool.DruidDataSource
        # 數(shù)據(jù)庫驅(qū)動類名
        driver-class-name: com.mysql.cj.jdbc.Driver
        # 數(shù)據(jù)庫url連接
        url: jdbc:mysql://192.168.0.129:3306/sharding_10?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
        # 數(shù)據(jù)庫用戶名
        username: alian
        # 數(shù)據(jù)庫密碼
        password: 123456
      ds3:
        # 數(shù)據(jù)庫連接池類名稱
        type: com.alibaba.druid.pool.DruidDataSource
        # 數(shù)據(jù)庫驅(qū)動類名
        driver-class-name: com.mysql.cj.jdbc.Driver
        # 數(shù)據(jù)庫url連接
        url: jdbc:mysql://192.168.0.129:3306/sharding_11?serverTimezone=GMT%2B8&characterEncoding=utf8&useUnicode=true&useSSL=false&zeroDateTimeBehavior=CONVERT_TO_NULL&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false&connectTimeout=6000&maxReconnects=5
        # 數(shù)據(jù)庫用戶名
        username: alian
        # 數(shù)據(jù)庫密碼
        password: 123456
    sharding:
      # 未配置分片規(guī)則的表將通過默認(rèn)數(shù)據(jù)源定位
      default-data-source-name: ds1
      tables:
        tb_order:
          # 由數(shù)據(jù)源名 + 表名組成，以小數(shù)點(diǎn)分隔。多個(gè)表以逗號分隔，支持inline表達(dá)式
          actual-data-nodes: ds$->{1..3}.tb_order_$->{1..2}
          # 分庫策略
          database-strategy:
            # 精確分片策略
            standard:
              # 分片鍵
              sharding-column: user_id
              # 精確分片算法類名稱，用于=和IN
              precise-algorithm-class-name: com.alian.shardingjdbc.algorithm.DatabasePreciseShardingAlgorithm
          # 分表策略
          table-strategy:
            # 精確分片策略
            standard:
              # 分片鍵
              sharding-column: order_id
              # 精確分片算法類名稱，用于=和IN
              precise-algorithm-class-name: com.alian.shardingjdbc.algorithm.OrderTablePreciseShardingAlgorithm
          # key生成器
          key-generator:
            # 自增列名稱，缺省表示不使用自增主鍵生成器
            column: order_id
            # 自增列值生成器類型，缺省表示使用默認(rèn)自增列值生成器（SNOWFLAKE/UUID）
            type: SNOWFLAKE
            # SnowflakeShardingKeyGenerator
            props:
              # SNOWFLAKE算法的worker.id
              worker:
                id: 100
              # SNOWFLAKE算法的max.tolerate.time.difference.milliseconds
              max:
                tolerate:
                  time:
                    difference:
                      milliseconds: 20

• 通過精確分片算法完成分庫分表

• database-strategy 采用的是 精確分片策略 ，算法實(shí)現(xiàn)類是我們自定義的類 com.alian.shardingjdbc.algorithm.DatabasePreciseShardingAlgorithm

• table-strategy 采用的是 精確分片策略 ，算法實(shí)現(xiàn)類是我們自定義的類 com.alian.shardingjdbc.algorithm.OrderTablePreciseShardingAlgorithm

• actual-data-nodes 使用Groovy的表達(dá)式 ds$->{1…3}.tb_order_$->{1…2}，對應(yīng)的數(shù)據(jù)源是：ds1、 ds2、 ds3，物理表是：tb_order_1、 tb_order_2，組合起來就有6種方式，這里就不一一列舉了

• key-generator ：key生成器，需要指定字段和類型，比如這里如果是SNOWFLAKE，最好也配置下props中的兩個(gè)屬性： worker.id 與 max.tolerate.time.difference.milliseconds 屬性

五、精確分片算法

??在行表示式分片策略中，基本上只需要配置行表示即可，不需要我們開發(fā)java，如果有一些比較特殊的要求，表達(dá)式很復(fù)雜或者是沒辦法使用表達(dá)式，假設(shè)我要求根據(jù) userId 進(jìn)行分庫，要滿足：

使用行表示就很復(fù)雜，我們就可以使用自定義分片算法，這里采用精確分片算法。

5.1、精確分庫算法

DatabasePreciseShardingAlgorithm.java

@Slf4j
public class DatabasePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {

    public DatabasePreciseShardingAlgorithm() {
    }

    @Override
    public String doSharding(Collection<String> dataSourceCollection, PreciseShardingValue<Integer> preciseShardingValue) {
        // 獲取分片鍵的值
        Integer shardingValue = preciseShardingValue.getValue();
        // 獲取邏輯
        String logicTableName = preciseShardingValue.getLogicTableName();
        log.info("分片鍵的值:{},邏輯表:{}", shardingValue, logicTableName);

        // 對分片鍵的值對10取模，得到（0-9）,我這里就配置了三個(gè)庫，實(shí)際根據(jù)需要修改
        // 0,8插入到 ds1
        // 1,3,6,9插入到 ds2
        // 2,4,5,7插入到 ds3
        int index = shardingValue % 10;
        int sourceTarget;
        if (ArrayUtils.contains(new int[]{0, 8}, index)) {
            sourceTarget = 1;
        } else if (ArrayUtils.contains(new int[]{1, 3, 6, 9}, index)) {
            sourceTarget = 2;
        } else {
            sourceTarget = 3;
        }

        // 遍歷數(shù)據(jù)源
        for (String databaseSource : dataSourceCollection) {
            // 判斷數(shù)據(jù)源是否存在
            if (databaseSource.endsWith(sourceTarget + "")) {
                return databaseSource;
            }
        }
        // 不存在則拋出異常
        throw new UnsupportedOperationException();
    }
}

??實(shí)際使用也很簡單，我們只需要實(shí)現(xiàn)接口 PreciseShardingAlgorithm<Integer> ，需要注意的是這里的類型 Integer 就是分片鍵 userId 的類型。然后重寫方法 doSharding ，這個(gè)方法會有兩個(gè)參數(shù)，第一個(gè)就是數(shù)據(jù)源的集合，第二個(gè)是分片對象，我們可以獲取到 分片鍵的值 及其 邏輯表 ，具體見上面代碼。
??分庫時(shí)就是需要我們通過自定義的算法計(jì)算出需要使用的數(shù)據(jù)源 databaseSource 。

5.2、精確分表算法

OrderTablePreciseShardingAlgorithm.java

@Slf4j
public class OrderTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    public OrderTablePreciseShardingAlgorithm() {
    }

    @Override
    public String doSharding(Collection<String> tableCollection, PreciseShardingValue<Long> preciseShardingValue) {
        // 獲取分片鍵的值
        Long shardingValue = preciseShardingValue.getValue();
        // 取模分表(取模都是從0到collection.size())
        long index = shardingValue % tableCollection.size();
        // 判斷邏輯表名
        String logicTableName = preciseShardingValue.getLogicTableName();
        // 物理表名
        String PhysicalTableName = logicTableName + "_" + (index + 1);

        log.info("分片鍵的值:{},物理表名:{}", shardingValue, PhysicalTableName);
        // 判斷是否存在該表
        if (tableCollection.contains(PhysicalTableName)) {
            return PhysicalTableName;
        }
        // 不存在則拋出異常
        throw new UnsupportedOperationException();
    }
}

??精確分表也是要實(shí)現(xiàn)接口 PreciseShardingAlgorithm<Long> ，需要注意的是這里的 Long 就是分片鍵 orderId 的類型。然后重寫方法 doSharding ，這個(gè)方法會有兩個(gè)參數(shù)，第一個(gè)就是物理表的集合，第二個(gè)是分片對象，我們可以獲取到 分片鍵的值 及其 邏輯表 ，具體見上面代碼。

??我們就簡單取模分片了，不過我們是通過我們自定義方法去實(shí)現(xiàn)的，而不是行表示，因?yàn)檫@樣你可以很靈活的設(shè)計(jì)你們的分片算法，比如你們可以使用基因法等等方式去處理，我這里只是為了演示方便。

六、實(shí)現(xiàn)

6.1、實(shí)體層

Order.java

@Data
@Entity
@Table(name = "tb_order")
public class Order implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "order_id")
    private Long orderId;

    @Column(name = "user_id")
    private Integer userId;

    @Column(name = "price")
    private Integer price;

    @Column(name = "order_status")
    private Integer orderStatus;

    @Column(name = "title")
    private String title;

    @Column(name = "order_time")
    private Date orderTime;

}

6.2、持久層

OrderRepository.java

public interface OrderRepository extends PagingAndSortingRepository<Order, Long> {

    /**
     * 根據(jù)訂單id查詢訂單
     * @param orderId
     * @return
     */
    Order findOrderByOrderId(Long orderId);

    /**
     * 根據(jù)訂單id和用戶id查詢訂單
     * @param orderId
     * @param userId
     * @return
     */
    Order findOrderByOrderIdAndUserId(Long orderId,Integer userId);
}

6.3、服務(wù)層

OrderService.java

@Slf4j
@Service
public class OrderService {

    @Autowired
    private OrderRepository orderRepository;

    public void saveOrder(Order order) {
        orderRepository.save(order);
    }

    public Order queryOrder(Long orderId) {
        return orderRepository.findOrderByOrderId(orderId);
    }

    public Order findOrderByOrderIdAndUserId(Long orderId, Integer userId) {
        return orderRepository.findOrderByOrderIdAndUserId(orderId, userId);
    }
}

6.4、測試類

OrderTests.java

@Slf4j
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class OrderTests {

    @Autowired
    private OrderService orderService;

    @Test
    public void saveOrder() {
        for (int i = 0; i < 20; i++) {
            Order order = new Order();
            // 隨機(jī)生成1000到1009的用戶id
            int userId = (int) Math.round(Math.random() * (1009 - 1000) + 1000);
            order.setUserId(userId);
            // 隨機(jī)生成50到100的金額
            int price = (int) Math.round(Math.random() * (10000 - 5000) + 5000);
            order.setPrice(price);
            order.setOrderStatus(2);
            order.setOrderTime(new Date());
            order.setTitle("");
            orderService.saveOrder(order);
        }
    }

    @Test
    public void queryOrder() {
        Long orderId = 875100237105348608L;
        Order order = orderService.queryOrder(orderId);
        log.info("查詢的結(jié)果：{}", order);
    }

    @Test
    public void findOrderByOrderIdAndUserId() {
        Long orderId = 875100237105348608L;
        Integer userId=1009;
        Order order = orderService.findOrderByOrderIdAndUserId(orderId,userId);
        log.info("查詢的結(jié)果：{}", order);
    }

}

6.4.1、保存訂單數(shù)據(jù)

效果圖：

??從上面的數(shù)據(jù)來看，滿足我們分庫分表的要求的，實(shí)現(xiàn)都是基于我們自定義的算法實(shí)現(xiàn)。

6.4.2、根據(jù)訂單號查詢訂單

    @Test
    public void queryOrder() {
        Long orderId = 875112578379300864L;
        Order order = orderService.queryOrder(orderId);
        log.info("查詢的結(jié)果：{}", order);
    }

20:37:23 575 INFO [main]:分片鍵的值:875112578379300864,物理表名:tb_order_2
20:37:23 575 INFO [main]:分片鍵的值:875112578379300864,物理表名:tb_order_2
20:37:23 575 INFO [main]:分片鍵的值:875112578379300864,物理表名:tb_order_2
20:37:23 595 INFO [main]:Logic SQL: select order0_.order_id as order_id1_0_, order0_.order_status as order_st2_0_, order0_.order_time as order_ti3_0_, order0_.price as price4_0_, order0_.title as title5_0_, order0_.user_id as user_id6_0_ from tb_order order0_ where order0_.order_id=?
20:37:23 595 INFO [main]:SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@28b68067, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@19540247), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@19540247, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=200, distinctRow=false, projections=[ColumnProjection(owner=order0_, name=order_id, alias=Optional[order_id1_0_]), ColumnProjection(owner=order0_, name=order_status, alias=Optional[order_st2_0_]), ColumnProjection(owner=order0_, name=order_time, alias=Optional[order_ti3_0_]), ColumnProjection(owner=order0_, name=price, alias=Optional[price4_0_]), ColumnProjection(owner=order0_, name=title, alias=Optional[title5_0_]), ColumnProjection(owner=order0_, name=user_id, alias=Optional[user_id6_0_])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@acb1c9c, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@1c681761, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@411933, containsSubquery=false)
20:37:23 595 INFO [main]:Actual SQL: ds1 ::: select order0_.order_id as order_id1_0_, order0_.order_status as order_st2_0_, order0_.order_time as order_ti3_0_, order0_.price as price4_0_, order0_.title as title5_0_, order0_.user_id as user_id6_0_ from tb_order_2 order0_ where order0_.order_id=? ::: [875112578379300864]
20:37:23 595 INFO [main]:Actual SQL: ds2 ::: select order0_.order_id as order_id1_0_, order0_.order_status as order_st2_0_, order0_.order_time as order_ti3_0_, order0_.price as price4_0_, order0_.title as title5_0_, order0_.user_id as user_id6_0_ from tb_order_2 order0_ where order0_.order_id=? ::: [875112578379300864]
20:37:23 595 INFO [main]:Actual SQL: ds3 ::: select order0_.order_id as order_id1_0_, order0_.order_status as order_st2_0_, order0_.order_time as order_ti3_0_, order0_.price as price4_0_, order0_.title as title5_0_, order0_.user_id as user_id6_0_ from tb_order_2 order0_ where order0_.order_id=? ::: [875112578379300864]
20:37:23 640 INFO [main]:查詢的結(jié)果：Order(orderId=875112578379300864, userId=1009, price=7811, orderStatus=2, title=, orderTime=2023-06-12 20:24:57.0)

??從上面的結(jié)果我們可以看到當(dāng)我們查詢order_id為 875112578379300864 的記錄時(shí)，因?yàn)槲覀冎笆前?font color="blue"> order_id 取模進(jìn)行的分表，最終得到的是 tb_order_2 ，但是這里根本不知道是哪個(gè)庫，所以把 ds1、ds2、ds3 都查了一遍，那有什么方法可以改善么？

6.4.2、根據(jù)訂單號和用戶查詢訂單

    @Test
    public void findOrderByOrderIdAndUserId() {
        Long orderId = 875112578379300864L;
        Integer userId=1009;
        Order order = orderService.findOrderByOrderIdAndUserId(orderId,userId);
        log.info("查詢的結(jié)果：{}", order);
    }

20:41:09 242 INFO [main]:分片鍵的值:1009,邏輯表:tb_order
20:41:09 246 INFO [main]:分片鍵的值:875112578379300864,物理表名:tb_order_2
20:41:09 264 INFO [main]:Logic SQL: select order0_.order_id as order_id1_0_, order0_.order_status as order_st2_0_, order0_.order_time as order_ti3_0_, order0_.price as price4_0_, order0_.title as title5_0_, order0_.user_id as user_id6_0_ from tb_order order0_ where order0_.order_id=? and order0_.user_id=?
20:41:09 264 INFO [main]:SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@58d79479, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@102c24d1), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@102c24d1, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=200, distinctRow=false, projections=[ColumnProjection(owner=order0_, name=order_id, alias=Optional[order_id1_0_]), ColumnProjection(owner=order0_, name=order_status, alias=Optional[order_st2_0_]), ColumnProjection(owner=order0_, name=order_time, alias=Optional[order_ti3_0_]), ColumnProjection(owner=order0_, name=price, alias=Optional[price4_0_]), ColumnProjection(owner=order0_, name=title, alias=Optional[title5_0_]), ColumnProjection(owner=order0_, name=user_id, alias=Optional[user_id6_0_])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@495f7ca4, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@700202fa, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@141234df, containsSubquery=false)
20:41:09 264 INFO [main]:Actual SQL: ds2 ::: select order0_.order_id as order_id1_0_, order0_.order_status as order_st2_0_, order0_.order_time as order_ti3_0_, order0_.price as price4_0_, order0_.title as title5_0_, order0_.user_id as user_id6_0_ from tb_order_2 order0_ where order0_.order_id=? and order0_.user_id=? ::: [875112578379300864, 1009]
20:41:09 318 INFO [main]:查詢的結(jié)果：Order(orderId=875112578379300864, userId=1009, price=7811, orderStatus=2, title=, orderTime=2023-06-12 20:24:57.0)

??從上面的結(jié)果我們可以看到當(dāng)我們查詢order_id為 875112578379300864 的記錄時(shí)，用戶id為 1009 的記錄時(shí)，最終直接查詢到 ds2.tb_order_2 ，并沒有把所有的庫都去查了一遍，因?yàn)槲覀兊牟樵儣l件里有 userId ，會自動計(jì)算到對應(yīng)的數(shù)據(jù)源，而按 order_id 取模進(jìn)行的分表會找到對應(yīng)的表。所以對于這種一個(gè)表多個(gè)字段同時(shí)分庫分表的時(shí)候，一定要注意這一點(diǎn)，這樣的查詢能提高效率。文章來源地址http://www.zghlxwxcb.cn/news/detail-676952.html

到了這里，關(guān)于Sharding-JDBC之PreciseShardingAlgorithm（精確分片算法）的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！