Sharding-JDBC之分库分表与读写分离

一、分库分表

什么是分库分表

分库分表是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

分库分表的方式

分库分表包括分库和分表两个部分，在生产中通常包括：垂直分库、水平分库、垂直分表、水平分表四种方式。

垂直分表：

把表的字段按访问频次、是否是大字段的原则拆分为多个表，既能使业务清晰，还能提升部分性能。

垂直分库：

把多个表按业务进行分类，分别存放在不同的库，从而将压力分散至不同的数据库，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能，但是需要解决跨库带来的所有复杂问题。

水平分库：

把一个表的数据(按数据行:某个字段或某几个字段根据某种规则)分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。但是它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题。

水平分表：

把一个表的数据(按数据行:某个字段或某几个字段根据某种规则)分到多个同一个数据库的多张表中，每个表只有这个表的部分数据，能小幅提升性能，仅仅作为水平分库的一个补充优化。

分库分表的技术模式

中间件Proxy模式

Proxy属于中间层方案，在应用程序和MySQL之间搭建一层Proxy。中间层介于应用程序与数据库间，
需要做一次转发，基于JDBC协议转发，如Mycat

Client 客户端模式

Client模式属于客户端直连方案。此方案的优势在于轻便，对兼容性、性能以及DBA影响小。 如当当
网的sharding-jdbc，阿里的TDDL

二、初识Sharding-JDBC

官网: https://shardingsphere.apache.org/index_zh.html

Sharding-JDBC概述

Sharding-JDBC定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。

Sharding-JDBC的核心功能为数据分片和读写分离 ，通过Sharding-JDBC，应用可以透明的使用 jdbc访问已经分库分表、读写分离的多个数据源，而不用关心数据源的数量以及数据如何分布。

Sharding-JDBC基本概念

逻辑表：

逻辑表是指一组具有相同逻辑和数据结构表的总称。如：订单表t_order 拆分成 t_order_0,t_order1…t_orderN。t_order就可以表示为逻辑表，在应用程序中操作的就是逻辑表。

真实表：

数据库中真实存在的物理表，即t_order_0 到 t_order_N

数据节点：

数据节点是分库分表中一个不可再分的最小数据单元（表），它由数据源名称和数据表组成，如：db1.t_order_1

绑定表：

指分片规则一致的主表和子表。绑定表之间的分区键完全相同，则此两张表互为绑定表关系。

如:product_info,product_descript,它们都是按 product_id 字段分片，因此两张表互为绑定表关系。如：在垂直分表时,一张表拆分成两张表,如果不配置绑定表关系，就会出现笛卡尔积关联查询

广播表：

存在于所有的分片数据源中的表，表结构和表中的数据在每个数据库中均完全一致。一般是为字典表或者配置表 t_config，某个表一旦被配置为广播表，只要修改某个数据库的广播表，所有数据源中广播表的数据都会跟着同步。

分片键：

用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。

如: 将 t_order 表分片以后,当执行一条SQL时，通过对字段order_id取模的方式决定，这条数据该在哪个数据库中的哪个 表中执行，此时 order_id 字段就是 t_order 表的分片健。

分片算法：

通过分片算法将数据分片，在实际开发中,会用 >=、<=、>、<、BETWEEN 和 IN 等条件作为分片规则，自定义分片逻辑进行分片。

由于分片算法和业务实现紧密相关，因此Sharding-JDBC是通过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算法。自定义的算法要实现Sharding-jdbc 提供的接口

sharding-jdbc提供了4种分片算法接口

1、精确分片算法 PreciseShardingAlgorithm
精确分片算法用于单个字段作为分片键，有 = 与 IN 条件的分片，在标准分片策略下使用。2、范围分片算法 RangeShardingAlgorithm
范围分片算法用于单个字段作为分片键，有BETWEEN AND、>、<、>=、<=  等条件的分片，在标准分片策略下使用。3、复合分片算法 ComplexKeysShardingAlgorithm
复合分片算法用于多个字段作为分片键的分片操作，同时获取到多个分片健的值，根据多个字段处理业务逻辑。在复合分片策略下使用。4、Hint分片算法 HintShardingAlgorithm
Hint分片算法不使用任何的分片键和分片策略，将 SQL 路由到目标数据库和表，通过手动干预指定SQL的目标数据库和表信息，也叫强制路由。

自定义分片算法:
自定义分片算法实现按月分表

@Slf4j
public class ShardingAlgorithmMonth implements PreciseShardingAlgorithm<Date> {
    /*** 执行分片策略* @param collection 候选表集合* @param preciseShardingValue 精确分片值:任务的执行时间* @return 数据路由到的表名称*/@Overridepublic String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
    String node = null;try {
    DateFormat dateFormat = new SimpleDateFormat("yyyy_M");String dateStr = dateFormat.format(preciseShardingValue.getValue());for (String nodeCandidate : collection) {
    if (nodeCandidate.endsWith(dateStr)) {
    node = nodeCandidate;break;}}} catch (Exception e) {
    log.error("sharding-sphere doSharding exception {}", e.getMessage());}return node;}
}

# 指定t_order表的分片策略,使用标准分片策略,以及自定义分片算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=cn.ybzy.demo.conf.ShardingAlgorithmMonth

分片策略:

分片策略由分片算法和分片健组合而成，正可用于分片操作的是分片键 + 分片算法, 分片算法做具体的数据分片逻辑。

分片策略分类

Sharding分片策略继承自ShardingStrategy，提供了5种分片策略：

1、标准分片策略 StandardShardingStrategy标准分片策略适用于单分片键，此策略支持 PreciseShardingAlgorithm 和 RangeShardingAlgorithm 两个分片算法。其中PreciseShardingAlgorithm是必选的，用于处理 = 和 IN 的分片。
RangeShardingAlgorithm是可选的，用于处理BETWEEN AND， >， <，>=，<= 条件分片，如果不配置RangeShardingAlgorithm，SQL中的条件等将按照全库路由处理。2、复合分片策略 ComplexShardingStrategy复合分片策略，同样支持对 SQL语句中的 =，>， <， >=， <=，IN和 BETWEEN AND 的分片操作。不同的是它支持多分片键，具体分配片细节完全由应用开发者实现。3、行表达式分片策略 InlineShardingStrategy
行表达式分片策略，支持对 SQL语句中的 = 和 IN 的分片操作，但只支持单分片键。这种策略通常用于简单的分片，不需要自定义分片算法，可以直接在配置文件中接着写规则。t_order_$->{t_order_id % 2} 代表 t_order 对其字段 t_order_id取模，拆分成2张表，而表名分别是t_order_0 到 t_order_1。4、Hint分片策略 HintShardingStrategy
Hint分片策略，对应上边的Hint分片算法，通过指定分片健而非从 SQL中提取分片健的方式进行分片的策略。5.不分片的策略 NoneShardingStrategy
如果某张表不需要分库分表，就可以不指定分库分表策略，让这张表的数据直接落到指定的数据源中即可

分布式主键：

通过在客户端生成自增主键替换以数据库原生自增主键的方式，做到分布式主键无重复。

ApacheShardingSphere 内置了UUID、SNOWFLAKE 两种分布式主键?成器，默认使?雪花算法（snowflake）?成64bit的?整型数据。不仅如此它还抽离出分布式主键?成器的接口，?便我们实现?定义的?增主键?成算法。

Sharding-JDBC执行过程

当Sharding-JDBC接受到一条SQL语句时，会陆续执行 SQL解析 => 查询优化 => SQL路由 => SQL改写 => SQL执行 =>结果归并 ，最终返回执行结果。

引入依赖

<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.49</version></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.1.3</version></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.1.1</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.21</version></dependency> 			

规则配置

Sharding-JDBC可以通过Java，YAML，Spring命名空间和Spring Boot Starter四种方式配置，开发者可根据场景选择适合的配置方式。配置是整个Sharding-JDBC的核心，是Sharding-JDBC中唯一与应用开发者打交道的模块。配置模块也是Sharding-JDBC的门户，通过它可以快速清晰的理解Sharding-JDBC所提供的功能。

创建DataSource

通过ShardingDataSourceFactory工厂和规则配置对象获取ShardingDataSource，ShardingDataSource实现自JDBC的标准接口DataSource。然后即可通过DataSource选择使用原生JDBC开发，或者使用JPA, MyBatis等ORM工具。

三、水平分表

水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中

优点：

1.优化了单一表数据量过大而产生的性能问题2.避免 IO争抢并减少锁表的几率

创建数据库/表

CREATE DATABASE order_db CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

```java
DROP TABLE
IFEXISTS `t_order_1`;
CREATE TABLE `t_order_1` (`order_id` BIGINT ( 20 ) NOT NULL COMMENT '订单id',`price` DECIMAL ( 10, 2 ) NOT NULL COMMENT '订单价格',`user_id` BIGINT ( 20 ) NOT NULL COMMENT '下单用户id',`status` VARCHAR ( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',PRIMARY KEY ( `order_id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;DROP TABLE
IFEXISTS `t_order_2`;
CREATE TABLE `t_order_2` (`order_id` BIGINT ( 20 ) NOT NULL COMMENT '订单id',`price` DECIMAL ( 10, 2 ) NOT NULL COMMENT '订单价格',`user_id` BIGINT ( 20 ) NOT NULL COMMENT '下单用户id',`status` VARCHAR ( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',PRIMARY KEY ( `order_id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

配置分片规则

分片规则配置是sharding-jdbc进行对分库分表操作的重要依据，配置内容包括：数据源、主键生成策略、分片策略等。

1.properties配置

server.port=8888
spring.application.name = sharding-jdbc
server.servlet.context-path = /#sharding-jdbc分片规则配置#数据源 起名:db1
spring.shardingsphere.datasource.names = db1spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/order_db?useUnicode=true
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456# 指定t_order表的数据分布情况，配置数据节点 分布在db1.t_order_1，db1.t_order_2  t_order:逻辑表非真实表,任意符合场景即可  db1.t_order_$->{
    1..2}: db1.t_order_1,db1.t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = db1.t_order_$->{
    1..2}# 指定t_order表的主键生成策略为SNOWFLAKE,SNOWFLAKE是一种分布式自增算法，保证id全局唯一  order_id:主键ID SNOWFLAKE:雪花片算法
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE# 指定t_order表的分片策略，分片策略包括分片键和分片算法 分表策略的表达式: t_order_$->{
    order_id % 2 + 1}=>> order_id基数时:t_order2 偶数时:t_order1
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{
    order_id % 2 + 1}# 打开sql输出日志
spring.shardingsphere.props.sql.show = true#数据字段的映射,驼峰映射
mybatis.configuration.map-underscore-to-camel-case = true#日志配置信息
logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding  = debug
logging.level.druid.sql = debug

2.yaml配置

server:port: 8888servlet:context-path: /spring:application:name: sharding-jdbcshardingsphere:#数据源 起名:db1datasource:names: db1db1:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/order_db?useUnicode=trueusername: rootpassword: 123456#指定t_order表的数据分布情况，配置数据节点 sharding:tables:t_order:actualDataNodes: m1.t_order_$->{
    1..2}#指定t_order表的分片策略,片策略包括分片键和分片算法tableStrategy:inline:shardingColumn: order_idalgorithmExpression: t_order_$->{
    order_id % 2 + 1}#指定t_order表的主键生成策略为SNOWFLAKE keyGenerator:type: SNOWFLAKEcolumn: order_id#打开sql输出日志 props:sql:show: true#驼峰映射 
mybatis:configuration:map-underscore-to-camel-case: true# 日志配置 
logging:level:root: infoorg.springframework.web: infocom.itheima.dbsharding: debugdruid.sql: debug

3.Java配置

使用配置类需要在SpringBoot启动类中屏蔽使用spring.shardingsphere配置项的类，否则该配置类会读取配置文件，从而报错。

@SpringBootApplication(exclude = {SpringBootConfiguration.class})

@Configuration
public class ShardingJdbcConfig {
    /*** 定义数据源集合** @return*/Map<String, DataSource> createDataSourceMap() {
    DruidDataSource dataSource1 = new DruidDataSource();dataSource1.setDriverClassName("com.mysql.jdbc.Driver");dataSource1.setUrl("jdbc:mysql://localhost:3306/order_db?useUnicode=true");dataSource1.setUsername("root");dataSource1.setPassword("123456");Map<String, DataSource> result = new HashMap<>();result.put("db1", dataSource1);return result;}/*** 定义主键生成策略** @return*/private static KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
    KeyGeneratorConfiguration result = new KeyGeneratorConfiguration("SNOWFLAKE", "order_id");return result;}/*** 定义t_order表的分片策略** @return*/TableRuleConfiguration getOrderTableRuleConfiguration() {
    TableRuleConfiguration result = new TableRuleConfiguration("t_order", "m1.t_order_$->{1..2}");result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_$->{order_id % 2 + 1}"));result.setKeyGeneratorConfig(getKeyGeneratorConfiguration());return result;}/*** 定义sharding-Jdbc数据源** @return* @throws SQLException*/@BeanDataSource getShardingDataSource() throws SQLException {
    //创建sharding-jdbc配置对象ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();//添加使用定义的分片策略shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());//创建配置类对象,添加一条配置信息Properties properties = new Properties();properties.put("sql.show", "true");//创建ShardingDataSourcereturn ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, properties);}}

4.xml方式配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:context="http://www.springframework.org/schema/context"xmlns:sharding="http://www.springframework.org/schema/p"xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsd"><context:annotation-config/><!--定义数据源--><bean id="db1" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"><property name="driverClassName" value="com.mysql.jdbc.Driver"/><property name="url" value="jdbc:mysql://localhost:3306/order_db?useUnicode=true"/><property name="username" value="root"/><property name="password" value="123456"/></bean><!--定义分库策略--><sharding:inline-strategy id="tableShardingStrategy" sharding-column="order_id"algorithmexpression="t_order_$->{order_id % 2 + 1}"/><!--定义主键生成策略--><sharding:key-generator id="orderKeyGenerator" type="SNOWFLAKE" column="order_id"/><!--定义sharding-Jdbc数据源--><sharding:data-source id="shardingDataSource"><sharding:sharding-rule data-source-names="db1"><sharding:table-rules><sharding:table-rule logic-table="t_order" table-strategyref="tableShardingStrategy"key-generator-ref="orderKeyGenerator"/></sharding:table-rules></sharding:sharding-rule></sharding:data-source></beans>

编写代码

@Mapper
@Component
public interface OrderDao {
    /*** 新增订单** @param price* @param userId* @param status* @return*/@Insert("insert into t_order(price,user_id,status)values(#{price},#{userId},#{status})")int insertOrder(@Param("price") BigDecimal price, @Param("userId") Long userId, @Param("status") String status);/*** 修改订单** @param orderId* @param price* @param userId* @return*/@Update("update t_order_1 SET price=#{price},user_id=#{userId} WHERE order_id=${orderId}")int updateOrder(@Param("orderId") Long orderId, @Param("price") BigDecimal price, @Param("userId") Long userId);/*** 根据ids查询order** @param orderIds* @return*/@Select("<script>" +"select * from t_order t where t.order_id in <foreach collection='orderIds' open='(' separator=',' close=')' item='id'> #{id} </foreach>" +"</script>")List<Map> selectOrderbyIds(@Param("orderIds") List<Long> orderIds);
}

执行测试

@RunWith(SpringRunner.class)
@SpringBootTest(classes = {
    ShardingJdbc.class})
public class OrderDaoTest {
    @AutowiredOrderDao orderDao;@Testpublic void insertOrder() {
    for (int i = 1; i < 10; i++) {
    orderDao.insertOrder(new BigDecimal(i), 1L, "SUCCESS");}}@Testpublic void updateOrderById() {
    Long id = 564780984948490240L;int i = orderDao.updateOrder(id, new BigDecimal(1000), 2L);System.out.println(i);}@Testpublic void selectOrderbyIds() {
    List<Long> ids = new ArrayList<>();ids.add(564780984948490240L);ids.add(564780986995310593L);List<Map> maps = orderDao.selectOrderbyIds(ids);for (Map map : maps) {
    System.out.println(map);}}
}

order_id为奇数的被插入到t_order_2表，为偶数的被插入到t_order_1表

传入参数,根据id找到对应表修改order信息

根据传入order_id的奇偶不同，sharding-jdbc分别去不同的表查询数据

四、水平分库

水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上

优点：

解决了单库大数据，高并发的性能瓶颈。

缺点：

由于同一个表被分配在不同的数据库，需要额外进行数据操作的路由工作，因此大大提升了系统复杂度。

创建数据库/表

创建数据 : order_db1与order_db2

CREATE DATABASE order_db1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

每个库创建表 : t_order_1与t_order_2

DROP TABLE
IFEXISTS `t_order_1`;
CREATE TABLE `t_order_1` (`order_id` BIGINT ( 20 ) NOT NULL COMMENT '订单id',`price` DECIMAL ( 10, 2 ) NOT NULL COMMENT '订单价格',`user_id` BIGINT ( 20 ) NOT NULL COMMENT '下单用户id',`status` VARCHAR ( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',PRIMARY KEY ( `order_id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;DROP TABLE
IFEXISTS `t_order_2`;
CREATE TABLE `t_order_2` (`order_id` BIGINT ( 20 ) NOT NULL COMMENT '订单id',`price` DECIMAL ( 10, 2 ) NOT NULL COMMENT '订单价格',`user_id` BIGINT ( 20 ) NOT NULL COMMENT '下单用户id',`status` VARCHAR ( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',PRIMARY KEY ( `order_id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

配置分片规则

分库需要配置两个数据源，分库需要配置分库策略，和分表策略类似，通过分库策略实现数据操作针对分库的数据库进行操作

#分库策略，将一个逻辑表映射到多个数据源
spring.shardingsphere.sharding.tables.<逻辑表名称>.database‐strategy.<分片策略>.<分片策略属性名>= #分片策略属性值#分表策略，将一个逻辑表映射为多个实际表
spring.shardingsphere.sharding.tables.<逻辑表名称>.table‐strategy.<分片策略>.<分片策略属性名>= #分片策略属性值

#数据源
spring.shardingsphere.datasource.names =db1,db2spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/order_db1?useUnicode=true
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456spring.shardingsphere.datasource.db2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db2.url = jdbc:mysql://localhost:3306/order_db2?useUnicode=true
spring.shardingsphere.datasource.db2.username = root
spring.shardingsphere.datasource.db2.password = 123456# 分库策略，以user_id为分片键，分片策略为user_id % 2 + 1，user_id为偶数操作db1数据源，否则操作db2。
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression = db$->{
    user_id % 2 + 1}# 指定t_order表的数据分布情况，配置数据节点  t_order:逻辑表非真实表,任意符合场景即可 db$->{
    1..2}=>> db1或db2  t_order_$->{
    1..2}=> t_order_1 或 t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = db$->{
    1..2}.t_order_$->{
    1..2}# 指定t_order表的主键生成策略为SNOWFLAKE order_id:主键ID SNOWFLAKE:雪花片算法
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE# 指定t_order表的分片策略，分片策略包括分片键和分片算法 t_order_$->{
    order_id % 2 + 1}=>> order_id基数时:t_order2 偶数时:t_order1
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{
    order_id % 2 + 1}

执行测试

插入测试

@Insert("insert into t_order(price,user_id,status)values(#{price},#{userId},#{status})")
int insertOrder(@Param("price") BigDecimal price, @Param("userId") Long userId, @Param("status") String status);

    @Testpublic void insertOrder(){
    for(int i=1;i<10;i++){
    orderDao.insertOrder(new BigDecimal(i),Long.parseLong(i+""),"SUCCESS");}}

根据user_id的奇偶不同，数据分别插入到db2与db1，order_id也同样根据奇偶不同插入到t_order_1与t_order_2

查询测试(无分片键)

    @Select("<script>" +"select * from t_order t where t.order_id in <foreach collection='orderIds' open='(' separator=',' close=')' item='id'> #{id} </foreach>" +"</script>")List<Map> selectOrderbyIds(@Param("orderIds") List<Long> orderIds);

    @Testpublic void testSelectOrderbyUserAndIds(){
    List<Long> ids = new ArrayList<>();ids.add(374121806463762432L);
// ids.add(373897037306920961L);List<Map> maps = orderDao.selectOrderbyUserAndIds(4L,ids);System.out.println(maps);}

查询奇数订单，根据分片策略t_order_KaTeX parse error: Expected group after '_' at position 339: …单，根据分片策略t_order_?->{order_id % 2 + 1}计算得出t_order_1与t_order_2，但因没有指定查询user_id，即没有使用分片键user_id，所以sharding-jdbc将广播路由到每个数据结点，即会查询两个数据库，每个数据库查询2次

查询测试(含分片键)

    @Select("<script>" +"select * from t_order t where t.order_id in <foreach collection='orderIds' open='(' separator=',' close=')' item='id'> #{id} </foreach>" + "and user_id=#{userId}" +"</script>")List<Map> selectOrderByUserAndIds(@Param("userId") Long userId, @Param("orderIds") List<Long> orderIds);

   @Testpublic void selectOrderByUserAndIds(){
    List<Long> ids = new ArrayList<>();ids.add(564863163246313472L);List<Map> maps = orderDao.selectOrderByUserAndIds(1L,ids);for (Map map : maps) {
    System.out.println(map);}}

分片键user_id为1，根据分片策略dbKaTeX parse error: Expected group after '_' at position 72: …> 根据分片策略t_order_?->{order_id % 2 + 1}计算得出t_order_1,sharding-jdbc将sql路由到t_order_1

    @Testpublic void selectOrderByUserAndIds(){
    List<Long> ids = new ArrayList<>();ids.add(564863163246313472L);ids.add(564863164945006593L);List<Map> maps = orderDao.selectOrderByUserAndIds(2L,ids);for (Map map : maps) {
    System.out.println(map);}}

分片键user_id为2，根据分片策略dbKaTeX parse error: Expected group after '_' at position 72: …> 根据分片策略t_order_?->{order_id % 2 + 1}计算得出t_order_1与t_order_2,sharding-jdbc将sql路由到t_order_1与t_order_2

五、垂直分库

垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用

优点:

1.业务层面解耦,业务分类更清晰2.针对不同业务的数据进行不同管理、维护、扩展3.提升了一定的IO性能，降低了单机硬件资源的瓶颈4.将表按业务分类，分布在不同数据库，数据库在不同服务器上，多个服务器共同分摊压力

缺点：

单表数据量过大

创建数据库/表

CREATE DATABASE `user_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

DROP TABLE
IFEXISTS `t_user`;
CREATE TABLE `t_user` (`user_id` BIGINT ( 20 ) NOT NULL COMMENT '用户id',`user_name` VARCHAR ( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户姓名',`user_type` CHAR ( 1 ) DEFAULT NULL COMMENT '用户类型',PRIMARY KEY ( `user_id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

配置分片规则

#数据源
spring.shardingsphere.datasource.names =db3spring.shardingsphere.datasource.db3.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db3.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db3.url = jdbc:mysql://localhost:3306/user_db?useUnicode=true
spring.shardingsphere.datasource.db3.username = root
spring.shardingsphere.datasource.db3.password = 123456# 指定t_user表的数据分布情况，配置数据节点 固定分配至db3的t_user真实表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = db3.t_user#t_user分表策略,分片策略包括分片键和分片算法 固定分配至t_user真实表
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression = t_user

执行测试

插入测试

    @Insert("insert into t_user(user_id, user_name,user_type) value(#{userId},#{userName},#{userType})")int insertUser(@Param("userId") Long userId, @Param("userName") String userName, @Param("userType") String userType);

    @Testpublic void insertUser(){
    for (int i = 1 ; i<10; i++){
    Long id =Long.parseLong(i+"");userDao.insertUser(id,"姓名"+ id,i%2==0 ? "男" : "女");}}

查询测试

 @Select({
    "<script>"," select * from t_user t where t.user_id in <foreach collection='userIds' item='id' open='(' separator=',' close=')'> #{id} </foreach>","</script>"})List<Map> selectUserbyIds(@Param("userIds") List<Long> userIds);

    @Testpublic void selectUserbyIds(){
    List<Long> userIds = new ArrayList<>();userIds.add(1L);userIds.add(2L);List<Map> maps = userDao.selectUserbyIds(userIds);for (Map map : maps) {
    System.out.println(map);}}

六、垂直分表

垂直分表是将一个表按照字段分成多表，每个表存储其中一部分字段。

拆分原则:

1. 把不常用的字段单独放在一张表;2. 把text，blob等大字段拆分出来放在附表中;3. 经常组合查询的列放在一张表中;

优点:

避免IO争抢并减少锁表的几率

缺点:

只解决了单一表数据量过大的问题，但没有将表分布到不同的服务器上，因此每个表还是竞争同一个物理机的CPU、内存、网络IO、磁盘。

创建数据库/表

创建数据库product_db

CREATE DATABASE product_db CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

创建product_info_1与product_info_2两张表

DROP TABLE
IFEXISTS `product_info_1`;
CREATE TABLE `product_info_1` (`product_info_id` BIGINT ( 20 ) NOT NULL COMMENT 'id',`product_name` VARCHAR ( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',`price` DECIMAL ( 10, 0 ) NULL DEFAULT NULL COMMENT '商品价格',PRIMARY KEY ( `product_info_id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

创建product_descript_1与product_descript_2两张表

DROP TABLE
IFEXISTS `product_descript_2`;
CREATE TABLE `product_descript_2` (`id` BIGINT ( 20 ) NOT NULL COMMENT 'id',`product_info_id` BIGINT ( 20 ) NULL DEFAULT NULL COMMENT '所属商品id',`descript` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述',PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

配置分片

spring.shardingsphere.datasource.names = dbspring.shardingsphere.datasource.db.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db.url = jdbc:mysql://localhost:3306/product_db?useUnicode=true
spring.shardingsphere.datasource.db.username = root
spring.shardingsphere.datasource.db.password = 123456# product_info数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.product_info.actual-data-nodes = db.product_info_$->{
    1..2}
# product_info分表策略
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.sharding-column = product_info_id
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.algorithm-expression = product_info_$->{
    product_info_id%2+1}
# product_info主键生成策略
spring.shardingsphere.sharding.tables.product_info.key-generator.column=product_info_id
spring.shardingsphere.sharding.tables.product_info.key-generator.type=SNOWFLAKE#product_descript数据分布情况，配置数据节点
spring.shardingsphere.sharding.tables.product_descript.actual-data-nodes = db.product_descript_$->{
    1..2}
#product_descript分表策略
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.sharding-column = product_info_id
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.algorithm-expression = product_descript_$->{
    product_info_id % 2 + 1}
#product_descript主键生成策略
spring.shardingsphere.sharding.tables.product_descript.key-generator.column=id
spring.shardingsphere.sharding.tables.product_descript.key-generator.type=SNOWFLAKE# 设置product_info,product_descript为绑定表
spring.shardingsphere.sharding.binding-tables[0] = product_info,product_descript

新增查询

 public void createProduct(ProductInfo product);@Override@Transactionalpublic void createProduct(ProductInfo productInfo) {
    ProductDescript productDescript =new ProductDescript();productDescript.setDescript(productInfo.getDescript());productDao.insertProductInfo(productInfo);//将商品信息id设置到productDescriptproductDescript.setProductInfoId(productInfo.getProductInfoId());productDao.insertProductDescript(productDescript);}/*** 添加商品信息* 需要指定主键生成规则* @param productInfo* @return*/@Insert("insert into product_info(product_name,price) " +" values (#{productName},#{price})")@Options(useGeneratedKeys = true,keyProperty = "productInfoId",keyColumn = "product_info_id")int insertProductInfo(ProductInfo productInfo);/*** 添加商品描述信息* 需要指定主键生成规则* @param productDescript* @return*/@Insert("insert into product_descript(product_info_id,descript) " +" value(#{productInfoId},#{descript})")@Options(useGeneratedKeys = true,keyProperty = "id",keyColumn = "id")int insertProductDescript(ProductDescript productDescript);

    @Testpublic void createProduct(){
    for (int i=1;i<10;i++){
    ProductInfo productInfo = new ProductInfo();productInfo.setProductName("商品名称"+i);productInfo.setPrice(new BigDecimal(i));productInfo.setDescript("商品描述"+i);productService.createProduct(productInfo);}}

使用sharding-jdbc提供的全局主键生成方式：雪花算法，生成全局业务唯一主键product_info_id。保证product_info_id为偶数的数据插入product_info_1与product_descript_1,为奇数的数据插入product_info_2与product_descript_2

普通查询

List<ProductInfo> selectProductByIds(List<Long> productIds);
@Overridepublic List<ProductInfo> selectProductByIds(List<Long> ids) {
    return productDao.selectProductByIds(ids);}@Select("<script>" +"select i.*,d.descript from product_info i join product_descript d on i.product_info_id = d.product_info_id where i.product_info_id in <foreach collection='productIds' open='(' separator=',' close=')' item='id'> #{id} </foreach> order by product_info_id desc" +"</script>")List<ProductInfo> selectProductByIds(@Param("productIds") List<Long> productIds);

   @Testpublic void selectProductByIds(){
    List<Long> productIds = new ArrayList<>();productIds.add(565306560192970753L);productIds.add(565306560155222016L);List<ProductInfo> productInfos = productService.selectProductByIds(productIds);for (ProductInfo productInfo : productInfos) {
    System.out.println(productInfo);}}

分页查询

    public List<ProductInfo> selectProduct(int page, int pageSize);@Overridepublic List<ProductInfo> selectProduct(int page, int pageSize) {
    int start = (page - 1) * pageSize;return productDao.selectProductList(start,pageSize);}@Select("select i.*,d.descript from product_info i join product_descript d on i.product_info_id = d.product_info_id order by product_info_id desc limit #{start},#{pageSize}")List<ProductInfo> selectProduct(@Param("start")int start, @Param("pageSize") int pageSize);

   public void selectProduct(){
    List<ProductInfo> productInfos = productService.selectProduct(1, 5);for (ProductInfo productInfo : productInfos) {
    System.out.println(productInfo);}}

七、公共表

公共表属于系统中数据量较小，变动少，而且属于高频联合查询的依赖表。参数表、数据字典表等属于此类型。可以将这类表在每个数据库都保存一份，所有更新操作都同时发送到所有分库执行。

创建公共表

在springboot配置文件中涉及使用的数据库中添加t_dict表

CREATE TABLE `t_dict` (`dict_id` BIGINT ( 20 ) NOT NULL COMMENT '字典id',`type` VARCHAR ( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典类型',`code` VARCHAR ( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典编码',`value` VARCHAR ( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '字典值',PRIMARY KEY ( `dict_id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

指定公共表

指定t_dict为公共表

spring.shardingsphere.sharding.broadcast‐tables=t_dict

执行测试

插入测试

 @Insert("insert into t_dict(dict_id,type,code,value) value(#{dictId},#{type},#{code},#{value})")int insertDict(@Param("dictId") Long dictId, @Param("type") String type, @Param("code") String code, @Param("value") String value);

   @Testpublic void insertDict(){
    dictDao.insertDict(1L,"role","role_1","1号管理员");dictDao.insertDict(2L,"role","role_2","2号管理员");}

对t_dict表进行插入操作，被广播至所有数据源

修改测试

@Update("update t_dict set type=#{type},code=#{code},value=#{value} where dict_id = #{dictId}")int updateDict(@Param("type") String type, @Param("code") String code, @Param("value") String value, @Param("dictId") Long dictId);

   @Testpublic void updateDict(){
    dictDao.updateDict("role","1","test",2L);}

关联查询测试

    @Select({
    "<script>"," select * from t_user t ,t_dict b where t.user_type = b.code and t.user_id in <foreach collection='userIds' item='id' open='(' separator=',' close=')'> #{id} </foreach>","</script>"})List<Map> selectUserByIdsAndCode(@Param("userIds") List<Long> userIds, @Param("code") String code);

   @Testpublic void selectUserByIdsAndCode(){
    List<Long> userIds = new ArrayList<>();userIds.add(6L);userIds.add(8L);List<Map> maps = userDao.selectUserByIdsAndCode(userIds,"1");for (Map map : maps) {
    System.out.println(map);}}

删除测试

 @Delete("delete from t_dict where dict_id = #{dictId}")int deleteDict(@Param("dictId") Long dictId);

   @Testpublic void deleteDict(){
    dictDao.deleteDict(1L);}

对t_dict表进行删除操作，被广播至所有数据源

八、Sharding-JDBC读写分离

Sharding-JDBC提供一主多从的读写分离配置，可独立使用，也可配合分库分表使用。Sharding-JDBC不提供主从数据库的数据同步功能，需要采用其他机制支持。

Sharding-JDBC读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库，提供透明化读写分离，让使用方尽量像使用一个数据库一样使用主从数据库集群

要实现Sharding-JDBC的读写分离，首先要进行MySQL主从同步配置 || 其他方式实现同步关系。

添加同步数据库

具体配置参考MySQL主从同步配置

[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\Mysql\mysql-master
# 设置mysql数据库的数据的存放目录
datadir=D:\Mysql\mysql-master\data#开启日志
log-bin=mysql-bin
#设置服务id，主从不能一致
server-id=1
#设置需要同步的数据库,不配置则同步全部数据库 
binlog-do-db=user_db
#屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

规则配置

#数据源 db1:主库 db2:从库
spring.shardingsphere.datasource.names =db1,db2spring.shardingsphere.datasource.db1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://localhost:3306/user_db?useUnicode=true
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456spring.shardingsphere.datasource.db2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db2.url = jdbc:mysql://localhost:3307/user_db?useUnicode=true
spring.shardingsphere.datasource.db2.username = root
spring.shardingsphere.datasource.db2.password = 123456# 主库从库逻辑数据源定义 db为user_db
spring.shardingsphere.sharding.master-slave-rules.db.master-data-source-name=db1
spring.shardingsphere.sharding.master-slave-rules.db.slave-data-source-names=db2#t_user分表策略，固定分配至db的t_user真实表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = db.t_user

写入测试

  @Insert("insert into t_user(user_id, user_name,user_type) value(#{userId},#{userName},#{userType})")int insertUser(@Param("userId") Long userId, @Param("userName") String userName, @Param("userType") String userType);

    @Testpublic void insertUser(){
    for (int i = 1 ; i<10; i++){
    Long id =Long.parseLong(i+"");userDao.insertUser(id,"姓名"+ id,i%2==0 ? "1" : "2");}}

插入数据，数据全部写入db1主库，在同步到db2从库。

查询测试

   @Select({
    "<script>"," select * from t_user t where t.user_id in <foreach collection='userIds' item='id' open='(' separator=',' close=')'> #{id} </foreach>","</script>"})List<Map> selectUserbyIds(@Param("userIds") List<Long> userIds);

    @Testpublic void selectUserByIdsAndCode(){
    List<Long> userIds = new ArrayList<>();userIds.add(3L);userIds.add(8L);List<Map> maps = userDao.selectUserbyIds(userIds);for (Map map : maps) {
    System.out.println(map);}}

查询数据，查询db2从库