mysql分库分表-shardingJDBC学习一（概念和问题）

为什么分库分表

解决数据库本身瓶颈

连接数，mysql默认的连接数是100，可以修改，最大为16384

数据库分表可以解决单表海量数据的查询性能问题

数据库分库可以解决单台数据库的并发访问压力问题

解决系统本身IO,CPU瓶颈

磁盘读写IO瓶颈，热点数据太多，尽管使用的数据库本身缓存，但是依旧有大量IO，导致sql执行速度慢

网络IO瓶颈，请求数据多，数据传输大，网络带宽不够，链路响应时间变长

CPU瓶颈，尤其是在基础数据量大单机复杂sql计算，sql语句执行占用CPU使用率高，也有扫描行数大，锁冲突，锁等待等原因

分库分表带来的新问题

问题⼀：跨节点数据库Join关联查询

数据库切分前，多表关联查询，可以通过sql join实现

分库分表后，数据可能在分布不同节点上，sql join带来的问题比较麻烦

问题⼆：分库操作带来的分布式事务问题

操作内容同时分布在不同库中，不可避免会带来跨库事务问题，即分布式事务

问题三：执⾏的SQL排序、翻⻚、函数计算问题

分库后，数据分布再不同的节点上， 跨节点多库进⾏查询时，会出现limit分⻚、order by排序等问

题。⽽且当排序字段⾮分⽚字段时，更加复杂了，要在不同的分⽚节点中将数据进⾏排序并返回，

然后将不同分⽚返回的结果集进⾏汇总和再次排序（也会带来更多的CPU/IO资源损耗）

问题四：数据库全局主键重复问题

常规表的id是使⽤⾃增id进⾏实现，分库分表后，由于表中数据同时存在不同数据库中，如果⽤

增id，则会出现冲突问题

问题五：容量规划,分库分表后⼆次扩容问题

业务发展快，初次分库分表后，满⾜不了数据存储，导致需要多次扩容

问题六：分库分表技术选型问题

市场分库分表中间件相对较多，框架各有各的优势与短板，应该如何选择

垂直分表与垂直分库介绍

垂直分表：即大表拆小表，把不常用的字段单独放在一张表中，把text，blog等大字段拆分出来放在附表中，业务经常组合查询的列放在一张表中

垂直分库：针对的是一个系统中的不同业务进行拆分，数据库的连接资源比较宝贵且单机处理能力有限；拆分之后，避免不同库竞争同一个物理机的CPU、内存、网络IO等，所以在高并发情况下，垂直分库能一定程度上突破IO、连接数及单机硬件资源的瓶颈；也可以更好的解决业务层面的耦合，业务清晰，且方便管理维护。问题是并没有解决单表数据量过大的问题

水平分表与水平分库介绍

水平分表：把一个大表分割成N个小表，每个表的结构一样，数据不一样，全部表的数据结合起来就是全部数据，减少锁表时间，但是所有表还是在一个库中，没有解决IO瓶颈。

水平分库：把同个表的数据按照一定的规则分到不同的数据库中，数据库在不同的服务器上

常用的分库分表中间件对比

Mycat

Java 语⾔编写的 MySQL 数据库⽹络协议的开源中间件，前身 Cobar

遵守 Mysql 原⽣协议，跨语⾔，跨平台，跨数据库的通⽤ 中间件代理

是基于 Proxy ，它复写了 MySQL 协议，将 Mycat Server 伪装成⼀个 MySQL 数据库

需要单独部署，

缺点是效率偏低，中间包装了⼀层 ；代码⽆侵⼊性

Sharding-JDBC

基于jdbc驱动，不⽤额外的proxy，⽀持任意实现 JDBC规范的数据库

它使⽤客户端直连数据库，以 jar 包形式提供服务，⽆需额外部署和依赖

可理解为加强版的 JDBC 驱动，兼容 JDBC 和各类 ORM框架

代码有侵⼊性

两者设计理念相同，主流程都是 SQL 解析 -->SQL 路由 -->SQL 改写--> 结果归并

分库分表实战

<properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <spring.boot.version>2.5.5</spring.boot.version>

        <mybatisplus.boot.starter.version>3.4.0</mybatisplus.boot.starter.version>
        <lombok.version>1.18.16</lombok.version>
        <sharding-jdbc.version>4.1.1</sharding-jdbc.version>
        <junit.version>4.12</junit.version>
        <druid.version>1.1.16</druid.version>
        <!--跳过单元测试-->
        <skipTests>true</skipTests>
    </properties>
    <dependencies>

        <dependency>

            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${spring.boot.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${spring.boot.version}</version>
            <scope>test</scope>
        </dependency>
        <!--mybatis plus和springboot整合-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatisplus.boot.starter.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <!--<scope>provided</scope>-->
        </dependency>
        <dependency>

            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-jdbc.version}
            </version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
        </dependency>
    </dependencies>

广播表

简介：指所有的分⽚数据源中都存在的表，表结构和表中的数据在每个数据库中均完全⼀致

          适⽤于数据量不⼤且需要与海量数据的表进⾏关联查询的场景 。例如：字典表、配置表

spring.application.name=xdcalss-sharding-jdbc
server.port=8080

logging.level.root=INFO
# 控制台打印sql
spring.shardingsphere.props.sql.show=true
# 数据源
spring.shardingsphere.datasource.names=ds0,ds1
# 数据库 ds0
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://ip:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=账号
spring.shardingsphere.datasource.ds0.password=密码

# 数据库 ds1
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://ip:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=账号
spring.shardingsphere.datasource.ds1.password=密码

#广播表配置
spring.shardingsphere.sharding.broadcast-tables=ad_config
#id生成策略  雪花算法
spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE

package net.xdclass;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.extern.slf4j.Slf4j;
import net.xdclass.DemoApplication;
import net.xdclass.mapper.AdConfigMapper;
import net.xdclass.model.AdConfigDO;
import net.xdclass.model.ProductOrderDO;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.util.List;
import java.util.Random;
import java.util.UUID;

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
public class Test {


    
    @Resource
    private AdConfigMapper adConfigMapper;

    @org.junit.Test
    public void testSaveAdConfig(){
        AdConfigDO adConfigDO = new AdConfigDO();
        adConfigDO.setConfigKey("测试");
        adConfigDO.setConfigValue("test");
        adConfigDO.setType("t");
        adConfigMapper.insert(adConfigDO);
    }

}

 两个数据库插入数据完全一致。

水平分库与水平分表：策略（行表达式）

分库规则 根据 user_id 进⾏分库

分表规则 根据 product_order_id 订单号进⾏分表

spring.application.name=xdcalss-sharding-jdbc
server.port=8080

logging.level.root=INFO
# 控制台打印sql
spring.shardingsphere.props.sql.show=true
# 数据源
spring.shardingsphere.datasource.names=ds0,ds1
# 数据库 ds0
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://ip:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=用户名
spring.shardingsphere.datasource.ds0.password=密码

# 数据库 ds1
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://ip:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=用户名
spring.shardingsphere.datasource.ds1.password=密码

# 配置workId
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1


#库和表的节点配置
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}

# 配置分库分表规则  库的分片规则，包括分片键和分片策略，根据user_id分库
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.algorithm-expression=ds$->{user_id%2}

# 配置分库分表规则  表的分片策略，包括分片键和分片策略 根据订单id分表
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{id%2}

#id生成策略  雪花算法
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
public class Test {



    @Resource
    private ProductOrderMapper productOrderMapper;

    @Resource
    private AdConfigMapper adConfigMapper;


    @org.junit.Test
    public void testSaveProductOrder(){
        Random random = new Random();
        for (int i = 0; i < 10; i++) {
            ProductOrderDO productOrderDO = new ProductOrderDO();
            productOrderDO.setNickname("小滴课堂 "+i);
            productOrderDO.setOutTradeNo(UUID.randomUUID().toString().substring(0,32));
            productOrderDO.setUserId(Long.valueOf(random.nextInt(50)));
            productOrderMapper.insert(productOrderDO);
        }

    }

绑定表

简介：分⽚规则⼀致的主表和⼦表; ⽐如product_order表和product_order_item表，均按照

        order_id分⽚，则此两张表互为绑定表关系

         绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将⼤⼤提升

spring.application.name=xdcalss-sharding-jdbc
server.port=8080

logging.level.root=INFO
# 控制台打印sql
spring.shardingsphere.props.sql.show=true
# 数据源
spring.shardingsphere.datasource.names=ds0,ds1
# 数据库 ds0
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://ip:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=用户名
spring.shardingsphere.datasource.ds0.password=密码

# 数据库 ds1
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://ip:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=用户名
spring.shardingsphere.datasource.ds1.password=密码

# 配置workId
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1

#广播表配置
spring.shardingsphere.sharding.broadcast-tables=ad_config
#id生成策略  雪花算法
spring.shardingsphere.sharding.tables.ad_config.key-generator.column=id
spring.shardingsphere.sharding.tables.ad_config.key-generator.type=SNOWFLAKE


#库和表的节点配置
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}

# 配置分库分表规则  库的分片规则，包括分片键和分片策略，根据user_id分库
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.database-strategy.inline.algorithm-expression=ds$->{user_id%2}

# 配置分库分表规则  表的分片策略，包括分片键和分片策略 根据订单id分表
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{id%2}

#id生成策略  雪花算法
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE


#默认分库策略
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id%2}

# product_order_item分库分表配置  库和表的节点配置
spring.shardingsphere.sharding.tables.product_order_item.actual-data-nodes=ds$->{0..1}.product_order_item_$->{0..1}
# 配置分库分表规则  表的分片策略，包括分片键和分片策略 根据订单id分表
spring.shardingsphere.sharding.tables.product_order_item.table-strategy.inline.sharding-column=product_order_id
spring.shardingsphere.sharding.tables.product_order_item.table-strategy.inline.algorithm-expression=product_order_item_$->{product_order_id % 2}

#绑定表配置
spring.shardingsphere.sharding.binding?tables[0] =product_order,product_order_item

public interface ProductOrderMapper extends BaseMapper<ProductOrderDO> {

    @Select("select * from product_order o left join product_order_item i on o.id=i.product_order_id")
    List<Object> listProductOrderDetail();
}

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
public class Test {



    @Resource
    private ProductOrderMapper productOrderMapper;


    @org.junit.Test
    public void testBingding(){
        List<Object> objects = productOrderMapper.listProductOrderDetail();
        System.out.println(objects);
    }
}

查询时两表一一对应。