使用 Java Spring Boot 和 MySQL 实现分库和分区表操作

在现代应用中，随着数据量的增加，单一数据库可能会成为性能瓶颈。分库（Sharding）和分区表是两种常见的解决方案，它们可以将数据分散到多个数据库或表中，从而提高性能和可靠性。本文将演示如何使用 Java Spring Boot 和 MySQL 实现简单的分库和分区表操作。

分区表的实现

分区表将数据在单个数据库中进行分区，提高查询效率。以下是实现分区表的步骤：

1. 配置分区表

在 MySQL 中创建分区表：

CREATE TABLE user_logs (
    id INT NOT NULL,
    user_id INT NOT NULL,
    activity VARCHAR(255) NOT NULL,
    activity_date DATE NOT NULL,
    PRIMARY KEY (id, user_id, activity_date)
)
PARTITION BY RANGE (YEAR(activity_date)) (
    PARTITION p0 VALUES LESS THAN (2023),
    PARTITION p1 VALUES LESS THAN (2024),
    PARTITION p2 VALUES LESS THAN (2025),
    PARTITION p3 VALUES LESS THAN (2026)
);

实现说明：

• 目的：在 MySQL 中创建分区表，按 activity_date 的年份进行分区。

• 结构：

  • 表结构包括 id, user_id, activity, 和 activity_date。
  • 使用 PARTITION BY RANGE 定义分区规则，按年份划分分区。

2. 更新存储库实现

更新 UserLogRepositoryImpl 以支持分区表操作：

package com.example.sharding.repository;

import com.example.sharding.config.DataSourceContextHolder;
import com.example.sharding.entity.UserLog;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class UserLogRepositoryImpl implements UserLogRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private void setDataSource(int userId) {
        int hash = userId % 4;
        DataSourceContextHolder.setDb("db" + hash);
    }

    @Override
    public void save(UserLog userLog) {
        setDataSource(userLog.getUserId());
        String sql = "INSERT INTO user_logs (id, user_id, activity, activity_date) VALUES (?, ?, ?, ?)";
        jdbcTemplate.update(sql, userLog.getId(), userLog.getUserId(), userLog.getActivity(), userLog.getActivityDate());
    }

    @Override
    public List<UserLog> findByUserIdAndDateRange(int userId, String startDate, String endDate) {
        setDataSource(userId);
        String sql = "SELECT * FROM user_logs WHERE user_id = ? AND activity_date BETWEEN ? AND ?";
        return jdbcTemplate.query(sql, new Object[]{userId, startDate, endDate}, this::mapRowToUserLog);
    }

    @Override
    public void update(UserLog userLog) {
        setDataSource(userLog.getUserId());
        String sql = "UPDATE user_logs SET activity = ?, activity_date = ? WHERE id = ? AND user_id = ?";
        jdbcTemplate.update(sql, userLog.getActivity(), userLog.getActivityDate(), userLog.getId(), userLog.getUserId());
    }

    @Override
    public void deleteById(int id, int userId) {
        setDataSource(userId);
        String sql = "DELETE FROM user_logs WHERE id = ? AND user_id = ?";
        jdbcTemplate.update(sql, id, userId);
    }

    private UserLog mapRowToUserLog(ResultSet rs, int rowNum) throws SQLException {
        UserLog userLog = new UserLog();
        userLog.setId(rs.getInt("id"));
        userLog.setUserId(rs.getInt("user_id"));
        userLog.setActivity(rs.getString("activity"));
        userLog.setActivityDate(rs.getDate("activity_date"));
        return userLog;
    }
}

实现说明：

• 目的：实现 UserLogRepository 接口，支持分区表的操作。

• 方法：

  • setDataSource 根据 userId 确定数据源。
  • save 方法执行插入操作。
  • findByUserIdAndDateRange 方法查询指定用户在日期范围内的日志。
  • update 方法更新日志。
  • deleteById 方法删除指定用户的日志。
  • mapRowToUserLog 方法将 ResultSet 转换为 UserLog 实体对象。

10. 测试与验证

现在，我们已经完成了所有必要的代码，实现了分库和分区表的操作。可以通过以下步骤进行测试与验证：

1. 启动应用程序：运行 ShardingApplication 类，启动 Spring Boot 应用程序。

2. 插入数据：

   curl -X POST http://localhost:8080/user-logs -H "Content-Type: application/json" -d '{
     "id": 1,
     "userId": 1001,
     "activity": "Login",
     "activityDate": "2023-07-04"
   }'
   

3. 查询数据：

   curl -X GET "http://localhost:8080/user-logs?userId=1001&startDate=2023-01-01&endDate=2023-12-31"
   

4. 更新数据：

   curl -X PUT http://localhost:8080/user-logs -H "Content-Type: application/json" -d '{
     "id": 1,
     "userId": 1001,
     "activity": "Logout",
     "activityDate": "2023-07-04"
   }'
   

5. 删除数据：

   curl -X DELETE "http://localhost:8080/user-logs/1?userId=1001"
   

分库（Sharding）实现

项目结构

假设项目结构如下：

src/main/java/com/example/sharding
│
├── config
│   ├── DataSourceConfig.java
│   └── JdbcTemplateRoutingDataSource.java
│
├── controller
│   └── UserLogController.java
│
├── entity
│   └── UserLog.java
│
├── repository
│   ├── UserLogRepository.java
│   └── UserLogRepositoryImpl.java
│
└── service
    └── UserLogService.java

1. 配置文件

在 application.yml 中配置多个数据源：

spring:
  datasource:
    db0:
      url: jdbc:mysql://localhost:3306/db0
      username: user
      password: password
      driver-class-name: com.mysql.cj.jdbc.Driver
    db1:
      url: jdbc:mysql://localhost:3306/db1
      username: user
      password: password
      driver-class-name: com.mysql.cj.jdbc.Driver
    db2:
      url: jdbc:mysql://localhost:3306/db2
      username: user
      password: password
      driver-class-name: com.mysql.cj.jdbc.Driver
    db3:
      url: jdbc:mysql://localhost:3306/db3
      username: user
      password: password
      driver-class-name: com.mysql.cj.jdbc.Driver

实现说明：

• 目的：配置多个数据源，分别连接到不同的数据库实例。

• 配置：

  • db0, db1, db2, db3 是四个数据源，每个数据源连接到不同的 MySQL 数据库实例。

2. 数据源配置

在 DataSourceConfig.java 中配置数据源：

package com.example.sharding.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.db0")
    public DataSource dataSourceDb0() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource dataSourceDb1() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource dataSourceDb2() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.db3")
    public DataSource dataSourceDb3() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public Map<String, DataSource> dataSourceMap(
            @Autowired DataSource dataSourceDb0,
            @Autowired DataSource dataSourceDb1,
            @Autowired DataSource dataSourceDb2,
            @Autowired DataSource dataSourceDb3
    ) {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("db0", dataSourceDb0);
        dataSourceMap.put("db1", dataSourceDb1);
        dataSourceMap.put("db2", dataSourceDb2);
        dataSourceMap.put("db3", dataSourceDb3);
        return dataSourceMap;
    }

    @Bean
    public JdbcTemplate jdbcTemplate(Map<String, DataSource> dataSourceMap) {
        return new JdbcTemplateRoutingDataSource(dataSourceMap);
    }
}

实现说明：

• 目的：配置多个数据源，并创建一个 JdbcTemplate 实例，使用 JdbcTemplateRoutingDataSource 进行路由。

• 流程：

  • 使用 @ConfigurationProperties 注解自动配置数据源。
  • 创建 dataSourceMap Bean，返回一个包含所有数据源的 Map。
  • jdbcTemplate Bean 将使用 JdbcTemplateRoutingDataSource，实现数据源的路由。

3. JdbcTemplateRoutingDataSource

创建一个 JdbcTemplateRoutingDataSource 类，用于路由到不同的数据源：

package com.example.sharding.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.sql.DataSource;
import java.util.Map;

public class JdbcTemplateRoutingDataSource extends AbstractRoutingDataSource {

    private Map<String, DataSource> dataSourceMap;

    public JdbcTemplateRoutingDataSource(Map<String, DataSource> dataSourceMap) {
        this.dataSourceMap = dataSourceMap;
        setTargetDataSources((Map) dataSourceMap);
        setDefaultTargetDataSource(dataSourceMap.get("db0"));
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDb();
    }
}

实现说明：

• 目的：继承 AbstractRoutingDataSource，实现数据源的动态路由。

• 方法：

  • 在构造函数中，设置目标数据源和默认数据源。
  • determineCurrentLookupKey 方法从 DataSourceContextHolder 获取当前数据源的键值。

4. 数据源上下文

创建一个 DataSourceContextHolder 类，用于管理当前线程的数据源上下文：

package com.example.sharding.config;

public class DataSourceContextHolder {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    public static void setDb(String dbType) {
        contextHolder.set(dbType);
    }

    public static String getDb() {
        return contextHolder.get();
    }

    public static void clearDb() {
        contextHolder.remove();
    }
}

实现说明：

• 目的：管理当前线程的数据源，使用 ThreadLocal 存储数据源键值。

• 方法：

  • setDb 设置当前数据源。
  • getDb 获取当前数据源。
  • clearDb 清除数据源。

5. 实体类

创建一个 UserLog 实体类：

package com.example.sharding.entity;

import java.util.Date;

public class UserLog {
    private int id;
    private int userId;
    private String activity;
    private Date activityDate;

    // Getters and Setters
}

实现说明：

• 目的：定义用户日志实体类。

• 属性：

  • id: 日志记录的唯一标识符。
  • userId: 用户ID。
  • activity: 用户活动描述。
  • activityDate: 活动日期。

6. 仓库层

创建一个 UserLogRepository 接口：

package com.example.sharding.repository;

import com.example.sharding.entity.UserLog;

import java.util.List;

public interface UserLogRepository {
    void save(UserLog userLog);
    List<UserLog> findByUserIdAndDateRange(int userId, String startDate, String endDate);
    void update(UserLog userLog);
    void deleteById(int id, int userId);
}

实现说明：

• 目的：定义数据访问层接口，提供对用户日志的 CRUD 操作。

• 方法：

  • save: 保存用户日志。
  • findByUserIdAndDateRange: 根据用户ID和日期范围查询日志。
  • update: 更新用户日志。
  • deleteById: 根据ID和用户ID删除日志。

以及 UserLogRepositoryImpl 实现类：

package com.example.sharding.repository;

import com.example.sharding.config.DataSourceContextHolder;
import com.example.sharding.entity.UserLog;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class UserLogRepositoryImpl implements UserLogRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private void setDataSource(int userId) {
        int hash = userId % 4;
        DataSourceContextHolder.setDb("db" + hash);
    }

    @Override
    public void save(UserLog userLog) {
        setDataSource(userLog.getUserId());
        String sql = "INSERT INTO user_logs (id, user_id, activity, activity_date) VALUES (?, ?, ?, ?)";
        jdbcTemplate.update(sql, userLog.getId(), userLog.getUserId(), userLog.getActivity(), userLog.getActivityDate());
    }

    @Override
    public List<UserLog> findByUserIdAndDateRange(int userId, String startDate, String endDate) {
        setDataSource(userId);
        String sql = "SELECT * FROM user_logs WHERE user_id = ? AND activity_date BETWEEN ? AND ?";
        return jdbcTemplate.query(sql, new Object[]{userId, startDate, endDate}, this::mapRowToUserLog);
    }

    @Override
    public void update(UserLog userLog) {
        setDataSource(userLog.getUserId());
        String sql = "UPDATE user_logs SET activity = ?, activity_date = ? WHERE id = ? AND user_id = ?";
        jdbcTemplate.update(sql, userLog.getActivity(), userLog.getActivityDate(), userLog.getId(), userLog.getUserId());
    }

    @Override
    public void deleteById(int id, int userId) {
        setDataSource(userId);
        String sql = "DELETE FROM user_logs WHERE id = ? AND user_id = ?";
        jdbcTemplate.update(sql, id, userId);
    }

    private UserLog mapRowToUserLog(ResultSet rs, int rowNum) throws SQLException {
        UserLog userLog = new UserLog();
        userLog.setId(rs.getInt("id"));
        userLog.setUserId(rs.getInt("user_id"));
        userLog.setActivity(rs.getString("activity"));
        userLog.setActivityDate(rs.getDate("activity_date"));
        return userLog;
    }
}

实现说明：

• 目的：实现 UserLogRepository 接口，具体操作数据库。

• 方法：

  • setDataSource 根据 userId 确定数据源。
  • save 方法执行插入操作。
  • findByUserIdAndDateRange 方法查询指定用户在日期范围内的日志。
  • update 方法更新日志。
  • deleteById 方法删除指定用户的日志。
  • mapRowToUserLog 方法将 ResultSet 转换为 UserLog 实体对象。

7. 服务层

创建一个 UserLogService 类：

package com.example.sharding.service;

import com.example.sharding.entity.UserLog;
import com.example.sharding.repository.UserLogRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserLogService {

    @Autowired
    private UserLogRepository userLogRepository;

    public void save(UserLog userLog) {
        userLogRepository.save(userLog);
    }

    public List<UserLog> findByUserIdAndDateRange(int userId, String startDate, String endDate) {
        return userLogRepository.findByUserIdAndDateRange(userId, startDate, endDate);
    }

    public void update(UserLog userLog) {
        userLogRepository.update(userLog);
    }

    public void deleteById(int id, int userId) {
        userLogRepository.deleteById(id, userId);
    }
}

实现说明：

• 目的：提供业务逻辑层，调用 UserLogRepository 接口进行数据操作。

• 方法：

  • save：调用 UserLogRepository 的 save 方法保存日志。
  • findByUserIdAndDateRange：调用 UserLogRepository 的 findByUserIdAndDateRange 方法查询日志。
  • update：调用 UserLogRepository 的 update 方法更新日志。
  • deleteById：调用 UserLogRepository 的 deleteById 方法删除日志。

8. 控制层

创建一个 UserLogController 类：

package com.example.sharding.controller;

import com.example.sharding.entity.UserLog;
import com.example.sharding.service.UserLogService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/user-logs")
public class UserLogController {

    @Autowired
    private UserLogService userLogService;

    @PostMapping
    public void saveUserLog(@RequestBody UserLog userLog) {
        userLogService.save(userLog);
    }

    @GetMapping
    public List<UserLog> getUserLogs(
            @RequestParam int userId,
            @RequestParam String startDate,
            @RequestParam String endDate
    ) {
        return userLogService.findByUserIdAndDateRange(userId, startDate, endDate);
    }

    @PutMapping
    public void updateUserLog(@RequestBody UserLog userLog) {
        userLogService.update(userLog);
    }

    @DeleteMapping("/{id}")
    public void deleteUserLog(@PathVariable int id, @RequestParam int userId) {
        userLogService.deleteById(id, userId);
    }
}

实现说明：

• 目的：处理 HTTP 请求，调用服务层逻辑，执行数据操作。

• 方法：

  • saveUserLog：处理 POST 请求，保存用户日志。
  • getUserLogs：处理 GET 请求，查询指定用户在日期范围内的日志。
  • updateUserLog：处理 PUT 请求，更新用户日志。
  • deleteUserLog：处理 DELETE 请求，根据 ID 和用户ID删除日志。

9. 主应用类

创建一个 ShardingApplication 类：

package com.example.sharding;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

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

实现说明：

• 目的：启动 Spring Boot 应用程序。
• 方法：main 方法调用 SpringApplication.run 启动应用。

总结

通过本文，我们学习了如何使用 Java Spring Boot 和 MySQL 实现分库和分区表操作。分库（Sharding）和分区表（Partitioning）是提升数据库性能和扩展性的有效手段，适用于大数据量的应用场景。