-- 主庫(kù)創(chuàng)建測(cè)試庫(kù)
CREATE DATABASE IF NOT EXISTS sharding_db;
USE sharding_db;

-- 創(chuàng)建用戶表
CREATE TABLE `t_user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `age` INT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入測(cè)試數(shù)據(jù)（從庫(kù)需同步該數(shù)據(jù)）
INSERT INTO t_user (username, age) VALUES ('zhangsan', 25), ('lisi', 30);

# 服務(wù)端口（應(yīng)用連接ShardingProxy的端口）
serverPort: 3307
# 管理端口（用于監(jiān)控和運(yùn)維）
adminPort: 8088
# 數(shù)據(jù)庫(kù)驅(qū)動(dòng)類名
driverClassName: com.mysql.cj.jdbc.Driver
# 認(rèn)證配置（應(yīng)用連接ShardingProxy的賬號(hào)密碼）
authentication:
  users:
    root:
      password: 123456  # 應(yīng)用連接密碼
    sharding:
      password: sharding 
      authorizedSchemas: sharding_db  # 授權(quán)訪問(wèn)的數(shù)據(jù)庫(kù)

# 日志配置（輸出SQL路由日志，便于調(diào)試）
props:
  max.connections.size.per.query: 1
  acceptor.size: 16  # 用于接收連接的線程數(shù)
  executor.size: 16  # 用于處理SQL的線程數(shù)
  proxy.frontend.flush.threshold: 128  # 前端刷盤閾值
  proxy.transaction.type: LOCAL
  proxy.opentracing.enabled: false
  query.with.cipher.column: true
  sql.show: true  # 開啟SQL顯示（打印路由后的SQL，調(diào)試必備）

# 數(shù)據(jù)源配置（主庫(kù)+從庫(kù)）
dataSources:
  # 主庫(kù)數(shù)據(jù)源
  master_ds:
    type: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.1.100:3306/sharding_db?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: root
    password: root123
    connectionTimeout: 30000
    idleTimeout: 60000
    maxLifetime: 1800000
    maximumPoolSize: 50
  
  # 從庫(kù)1數(shù)據(jù)源
  slave_ds_0:
    type: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.1.101:3306/sharding_db?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: root
    password: root123
    connectionTimeout: 30000
    idleTimeout: 60000
    maxLifetime: 1800000
    maximumPoolSize: 50
  
  # 從庫(kù)2數(shù)據(jù)源（可擴(kuò)展多個(gè)從庫(kù)）
  slave_ds_1:
    type: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.1.102:3306/sharding_db?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: root
    password: root123
    connectionTimeout: 30000
    idleTimeout: 60000
    maxLifetime: 1800000
    maximumPoolSize: 50

# 讀寫分離規(guī)則配置
rules:
- !READWRITE_SPLITTING
  dataSources:
    # 讀寫分離數(shù)據(jù)源名稱（應(yīng)用實(shí)際訪問(wèn)的數(shù)據(jù)源名）
    sharding_rw_ds:
      type: Static
      props:
        write-data-source-name: master_ds  # 寫操作路由至主庫(kù)
        read-data-source-names: slave_ds_0,slave_ds_1  # 讀操作路由至兩個(gè)從庫(kù)
        load-balance-algorithm-type: ROUND_ROBIN  # 從庫(kù)負(fù)載均衡算法（輪詢）
        # 可選：主從延遲控制（超過(guò)500ms則讀主庫(kù)）
        # read-write-splitting-delay-threshold: 500

# 綁定表規(guī)則（無(wú)分表時(shí)可省略，此處僅作示例）
bindingTables:
- t_user

# 默認(rèn)數(shù)據(jù)源（當(dāng)SQL未匹配任何規(guī)則時(shí)，路由至該數(shù)據(jù)源）
defaultDataSourceName: sharding_rw_ds

# Linux啟動(dòng)命令（指定配置文件目錄）
./start.sh ../conf

[INFO ] 2024-05-20 10:00:00.000 [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy start success.

-- 1. 寫操作（INSERT）：查看ShardingProxy日志，確認(rèn)路由至master_ds
INSERT INTO t_user (username, age) VALUES ('wangwu', 35);

-- 2. 讀操作（SELECT）：查看日志，確認(rèn)輪詢路由至slave_ds_0、slave_ds_1
SELECT * FROM t_user;

-- 3. 強(qiáng)制讀主庫(kù)（Hint語(yǔ)法）：核心業(yè)務(wù)讀請(qǐng)求用此方式保障一致性
/*!SHARDINGSPHERE_HINT: DATA_SOURCE_NAME=master_ds */ 
SELECT * FROM t_user WHERE id = 3;

<dependencies>
    <!-- SpringBoot JDBC Starter -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    
    <!-- MySQL驅(qū)動(dòng) -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.32</version>
        <scope>runtime</scope>
    </dependency>
    
    <!-- 數(shù)據(jù)庫(kù)連接池（HikariCP） -->
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
    </dependency>
    
    <!-- SpringBoot Test（用于測(cè)試） -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.1.103:3307/sharding_db?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    username: root
    password: 123456
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000

public class User {
    private Long id;
    private String username;
    private Integer age;

    // 無(wú)參構(gòu)造、有參構(gòu)造、getter、setter
    public User() {}
    public User(String username, Integer age) {
        this.username = username;
        this.age = age;
    }

    // getter和setter
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }
    public Integer getAge() { return age; }
    public void setAge(Integer age) { this.age = age; }
}

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
import java.util.List;

@Repository
public class UserDao {
    @Resource
    private JdbcTemplate jdbcTemplate;

    // 寫操作：新增用戶（路由至主庫(kù)）
    public int addUser(User user) {
        String sql = "INSERT INTO t_user (username, age) VALUES (?, ?)";
        return jdbcTemplate.update(sql, user.getUsername(), user.getAge());
    }

    // 讀操作：查詢所有用戶（路由至從庫(kù)，輪詢）
    public List<User> listAllUsers() {
        String sql = "SELECT id, username, age FROM t_user";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
    }

    // 讀操作：強(qiáng)制讀主庫(kù)（核心業(yè)務(wù)，保障數(shù)據(jù)一致性）
    public User getUserById(Long id) {
        // Hint語(yǔ)法：強(qiáng)制路由至主庫(kù)
        String sql = "/*!SHARDINGSPHERE_HINT: DATA_SOURCE_NAME=master_ds */ " +
                     "SELECT id, username, age FROM t_user WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
    }
}

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import javax.annotation.Resource;
import java.util.List;
import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
public class UserDaoTest {
    @Resource
    private UserDao userDao;

    // 測(cè)試寫操作（路由至主庫(kù)）
    @Test
    public void testAddUser() {
        User user = new User("zhaoliu", 40);
        int rows = userDao.addUser(user);
        assertEquals(1, rows); // 新增成功返回1
    }

    // 測(cè)試讀操作（路由至從庫(kù)，輪詢）
    @Test
    public void testListAllUsers() {
        List<User> userList = userDao.listAllUsers();
        assertNotNull(userList);
        System.out.println("查詢用戶數(shù)量：" + userList.size());
        // 連續(xù)查詢3次，觀察ShardingProxy日志，確認(rèn)從庫(kù)輪詢（slave_ds_0 → slave_ds_1 → slave_ds_0）
        for (int i = 0; i < 3; i++) {
            userDao.listAllUsers();
        }
    }

    // 測(cè)試強(qiáng)制讀主庫(kù)（保障數(shù)據(jù)一致性）
    @Test
    public void testGetUserById() {
        // 先新增用戶（寫主庫(kù)）
        User user = new User("qianqi", 28);
        userDao.addUser(user);
        // 立即查詢剛新增的用戶（強(qiáng)制讀主庫(kù)，避免主從延遲導(dǎo)致查不到數(shù)據(jù)）
        User result = userDao.getUserById(user.getId());
        assertNotNull(result);
        assertEquals("qianqi", result.getUsername());
    }
}


## 五、常見問(wèn)題與解決方案
### 5.1 主從數(shù)據(jù)不一致
#### 問(wèn)題表現(xiàn)
應(yīng)用執(zhí)行“寫主庫(kù)后立即讀從庫(kù)”時(shí)，可能查詢不到最新數(shù)據(jù)（因主從復(fù)制存在延遲）。

#### 解決方案
1. **強(qiáng)制路由主庫(kù)**：對(duì)核心業(yè)務(wù)場(chǎng)景（如用戶注冊(cè)后立即查詢用戶信息），使用Hint語(yǔ)法強(qiáng)制讀主庫(kù)：
   ```sql
   /*!SHARDINGSPHERE_HINT: DATA_SOURCE_NAME=master_ds */ 
   SELECT * FROM t_user WHERE id = ?

props:
  read-write-splitting-delay-threshold: 500  # 從庫(kù)延遲>500ms時(shí)讀主庫(kù)

dataSources:
  slave_ds_0:
    # 其他配置省略...
    health:
      check:
        enable: true  # 開啟健康檢查
        timeout: 3000  # 檢查超時(shí)時(shí)間（毫秒）
        interval: 5000  # 檢查間隔（毫秒）
  slave_ds_1:
    # 同上配置...

rules:
- !READWRITE_SPLITTING
  dataSources:
    sharding_rw_ds:
      # 其他配置省略...
      props:
        # 自定義SQL路由規(guī)則：將包含F(xiàn)OR UPDATE的SELECT視為寫操作
        sql-parser-rule: "SELECT.*FOR UPDATE"
        sql-parser-rule-type: WRITE  # 匹配上述規(guī)則的SQL視為寫操作