本文作者：梁開權(quán)，叩丁狼高級講師。原創(chuàng)文章，轉(zhuǎn)載請注明出處。

環(huán)境準備

pom.xml

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.3.RELEASE</version>
</parent>

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

<dependencies>
    <dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-core</artifactId>
        <version>${sharding.version}</version>
    </dependency>

    <dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>${sharding.version}</version>
    </dependency>

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

    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.4.5</version>
    </dependency>

    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>1.3.1</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.46</version>
    </dependency>

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

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

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

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

domain

// 建立domain
@Setter@Getter@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Employee {
    private Long id;
    private String name;
}

配置類

@SpringBootApplication
@MapperScan("cn.wolfcode.sharding.mapper")
public class ShardingApplication { }

分庫分表

案例模型

把數(shù)據(jù)分別存放在兩臺服務(wù)器的兩個數(shù)據(jù)庫中表，通過分片算法來決定當前的數(shù)據(jù)存放在哪個數(shù)據(jù)庫的哪個表中，由于一個連接池只能連接一個特定的數(shù)據(jù)庫，所以這里需要創(chuàng)建多個連接池對象

建表

-- 分別在2臺服務(wù)器中建立數(shù)據(jù)庫sharding，并且建表employee_0和employee_1
CREATE TABLE `employee_0` (
  `id` bigint(20) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ###################################
CREATE TABLE `employee_1` (
  `id` bigint(20) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

application.properties

# 定義連接池
sharding.jdbc.datasource.names=db0,db1

# 格式sharding.jdbc.datasource.連接池名.xxx:設(shè)置4要素信息
sharding.jdbc.datasource.db0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.db0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.db0.url=jdbc:mysql://db0Ip:port/sharing
sharding.jdbc.datasource.db0.username=xxx
sharding.jdbc.datasource.db0.password=xxx

sharding.jdbc.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.db1.url=jdbc:mysql://db1Ip:port/sharing
sharding.jdbc.datasource.db1.username=xxx
sharding.jdbc.datasource.db1.password=xxx

# 設(shè)置分庫規(guī)則
# sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column：分庫列
# sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression：分庫算法
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=db$->{id % 2}

# 綁定邏輯表
sharding.jdbc.config.sharding.binding-tables=employee

# 設(shè)置分表規(guī)則
# sharding.jdbc.config.sharding.tables.邏輯表.actual-data-nodes：邏輯表對應(yīng)的真實表
# sharding.jdbc.config.sharding.tables.邏輯表.table-strategy.inline.sharding-column：分表列
# sharding.jdbc.config.sharding.tables.邏輯表.table-strategy.inline.algorithm-expression：分表算法
# sharding.jdbc.config.sharding.tables.邏輯表.key-generator-column-name：主鍵列
sharding.jdbc.config.sharding.tables.employee.actual-data-nodes=db$->{0..1}.employee_$->{0..1}
sharding.jdbc.config.sharding.tables.employee.table-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.tables.employee.table-strategy.inline.algorithm-expression=employee_$->{id % 2}
sharding.jdbc.config.sharding.tables.employee.key-generator-column-name=id

# 打印日志
sharding.jdbc.config.props.sql.show=true

mapper

/**
 * 這里寫的employee表是上面所配置的邏輯表
 * 底層會根據(jù)分片規(guī)則，把我們寫的邏輯表改寫為數(shù)據(jù)庫中的真實表
 */
@Mapper
public interface EmployeeMapper {
    @Select("select * from employee")
    List<Employee> selectAll();

    @Insert("insert into employee (name) values (#{name})")
    void inser(Employee entity);
}

測試

@RunWith(SpringRunner.class)
@SpringBootTest(classes=ShardingApplication.class)
public class ShardingApplicationTests {

    @Autowired
    private EmployeeMapper employeeMapper;

    @Test
    public void save() {
        for (int i = 0; i < 10; i++) {
            Employee employee = new Employee();
            employee.setName("xx"+i);
            employeeMapper.inser(employee);
        }
    }

    @Test
    public void list() {
        employeeMapper.selectAll().forEach(System.out::println);
    }
}

優(yōu)缺點

• 拆分后單表數(shù)據(jù)量比較小,單表大數(shù)據(jù)被拆分,解決了單表大數(shù)據(jù)訪問問題
• 分表以什么切分如果弄的不好,導(dǎo)致多次查詢,而且有時候要跨庫操作,甚至導(dǎo)致join無法使用,對排序分組等有性能影響
• 之前的原子操作被拆分成多個操作,事務(wù)處理變得復(fù)雜
• 多個DB維護成本增加