ShardingSphere你還不會嗎？(第一篇)

作者：星晴（當(dāng)?shù)匦∮忻麣猓〉街挥凶约褐赖慕軅惙郏?/strong>

一.需求

我們做項目的時候，數(shù)據(jù)量比較大,單表千萬級別的,需要分庫分表，于是在網(wǎng)上搜索這方面的開源框架,最常見的就是mycat,sharding-sphere,最終我選擇后者,用它來做分庫分表比較容易上手。

二. 簡介sharding-sphere

官網(wǎng)地址: https://shardingsphere.apache.org/

三.分庫分表

3.1 pom.xml

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<!--shardingsphere start-->
<!-- for spring boot -->
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>3.1.0</version>
</dependency>
<!-- for spring namespace -->
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>3.1.0</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

3.2 application.yml

# 數(shù)據(jù)源 cloud-db-0,cloud-db-1
sharding:
  jdbc:
    datasource:
      names: cloud-db-0,cloud-db-1
    # 第一個數(shù)據(jù)庫
      cloud-db-0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://47.115.143.33:3306/cloud-db-0?characterEncoding=utf-8&&serverTimezone=GMT%2B8
        username: root
        password: 123456
    # 第二個數(shù)據(jù)庫
      cloud-db-1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://47.115.143.33:3306/cloud-db-1?characterEncoding=utf-8&&serverTimezone=GMT%2B8
        username: root
        password: 123456

# 水平拆分的數(shù)據(jù)庫（表） 配置分庫 + 分表策略 行表達式分片策略
# 分庫策略
    config:
      sharding:
        default-database-strategy:
          inline:
            sharding-column: id
            algorithm-expression: cloud-db-$->{id % 2}
        #分表策略 其中user為邏輯表 分表主要取決于age行
        tables:
           user:
             actual-data-nodes: cloud-db-$->{0..1}.user_$->{0..1}
             table-strategy:
               inline:
                 sharding-column: age
                 # 分片算法表達式
                 algorithm-expression: user_$->{age % 2}
      # 打印執(zhí)行的數(shù)據(jù)庫以及語句
      props:
        sql:
          show: true

3.3 數(shù)據(jù)庫腳本

cloud-db-0:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user_0
-- ----------------------------
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE `user_0` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for user_1
-- ----------------------------
DROP TABLE IF EXISTS `user_1`;
CREATE TABLE `user_1` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

cloud-db-1:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user_0
-- ----------------------------
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE `user_0` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Table structure for user_1
-- ----------------------------
DROP TABLE IF EXISTS `user_1`;
CREATE TABLE `user_1` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

3.4 代碼實現(xiàn)

User

@Data
@Entity
@Table(name = "user")
public class User {

    /**
     * 主鍵Id
     */
    @Id
    private int id;

    /**
     * 名稱
     */
    private String name;

    /**
     * 年齡
     */
    private int age;
}

UserRepository

public interface UserRepository extends JpaRepository<User,Integer> {
}

UserServiceImpl

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserRepository userRepository;

    @Override
    public boolean save(User entity) {
        userRepository.save(entity);
        return true;
    }

    @Override
    public List<User> getUserList() {
        return userRepository.findAll();
    }

}

UserController

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/select")
    public List<User> select() {
        return userService.getUserList();
    }

    @GetMapping("/insert")
    public Boolean insert(User user) {
        return userService.save(user);
    }

}

3.5 測試

1.分庫、分表插入

http://localhost:8080/insert?id=1&name=lhd&age=12    
http://localhost:8080/insert?id=2&name=lhd&age=13    
http://localhost:8080/insert?id=3&name=lhd&age=14    
http://localhost:8080/insert?id=4&name=lhd&age=15

2.分庫、分表查詢

http://localhost:8080/select