>本文作者：梁开权，叩丁狼高级讲师。原创文章，转载请注明出处。 ## 环境准备 ### pom.xml ```xml org.springframework.boot spring-boot-starter-parent 2.1.3.RELEASE 1.8 3.1.0 io.shardingsphere sharding-jdbc-core ${sharding.version} io.shardingsphere sharding-jdbc-spring-boot-starter ${sharding.version} com.alibaba druid 1.1.10 org.mybatis mybatis 3.4.5 org.mybatis.spring.boot mybatis-spring-boot-starter 1.3.1 mysql mysql-connector-java 5.1.46 org.springframework.boot spring-boot-starter org.projectlombok lombok org.springframework.boot spring-boot-starter-test test org.springframework.boot spring-boot-maven-plugin ``` ### domain ```java // 建立domain @Setter@Getter@ToString @NoArgsConstructor @AllArgsConstructor public class Employee { private Long id; private String name; } ``` ### 配置类 ```java @SpringBootApplication @MapperScan("cn.wolfcode.sharding.mapper") public class ShardingApplication { } ``` ### ## 分库分表 ### 案例模型 把数据分别存放在两台服务器的两个数据库中表，通过分片算法来决定当前的数据存放在哪个数据库的哪个表中，由于一个连接池只能连接一个特定的数据库，所以这里需要创建多个连接池对象 ### 建表 ```sql -- 分别在2台服务器中建立数据库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 ```properties # 定义连接池 sharding.jdbc.datasource.names=db0,db1 # 格式sharding.jdbc.datasource.连接池名.xxx:设置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 # 设置分库规则 # 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 # 设置分表规则 # sharding.jdbc.config.sharding.tables.逻辑表.actual-data-nodes：逻辑表对应的真实表 # 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 ```java /** * 这里写的employee表是上面所配置的逻辑表 * 底层会根据分片规则，把我们写的逻辑表改写为数据库中的真实表 */ @Mapper public interface EmployeeMapper { @Select("select * from employee") List selectAll(); @Insert("insert into employee (name) values (#{name})") void inser(Employee entity); } ``` ### 测试 ```java @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); } } ``` ### 优缺点 - 拆分后单表数据量比较小,单表大数据被拆分,解决了单表大数据访问问题 - 分表以什么切分如果弄的不好,导致多次查询,而且有时候要跨库操作,甚至导致join无法使用,对排序分组等有性能影响 - 之前的原子操作被拆分成多个操作,事务处理变得复杂 - 多个DB维护成本增加