当前位置:首页 > 数据库 > 数据库读写分离、分库分表——mycat与shardingjdbc

数据库读写分离、分库分表——mycat与shardingjdbc

6个月前 (05-28)64

文章目录

一、数据切分

  数据切分,简单的说,就是通过某种条件,将我们之前存储在一台数据库上的数据,分散到多台数据库中,从而达到降低单台数据库负载的效果。数据切分,根据其切分的规则,大致分为两种类型:

  • 垂直切分
  • 水平切分

1、垂直切分

  垂直切分就是按照不同的表或者Schema切分到不同的数据库中,比如:在我们的课程中,订单表(order)和商品表(product)在同一个数据库中,而我们现在要对其切分,使得订单表(order)和商品表(product)分别落到不同的物理机中的不同的数据库中,使其完全隔离,从而达到降低数据库负载的效果。
一句话概括:将不同的业务数据放到不同的库中

  垂直切分的特点就是规则简单,易于实施,可以根据业务模块进行划分,各个业务之间耦合性低,相互影响也较小。
  一个架构设计较好的应用系统,其总体功能肯定是有多个不同的功能模块组成的。每一个功能模块对应着数据库里的一系列表。
  意味着垂直拆分往往伴随着,系统程序分布式化、微服务化,当然单机的项目一般情况下也是用不到分库分表的。

  在架构设计中,各个功能模块之间的交互越统一、越少越好。这样,系统模块之间的耦合度会很低,各个系统模块的可扩展性、可维护性也会大大提高。这样的系统,实现数据的垂直切分就会很容易。
  但是,在实际的系统架构设计中,有一些表很难做到完全的独立,往往存在跨库join的现象。比如我们接到了一个需求,要求查询某一个类目产生了多少订单,如果在单体数据库中,我们直接连表查询就可以了。但是现在垂直切分成了两个数据库,跨库连表查询是十分影响性能的,也不推荐这样用,只能通过接口去调取服务,这样系统的复杂度又升高了。

优点:

  • 拆分后业务清晰,拆分规则明确;
  • 系统之间容易扩展和整合;
  • 数据维护简单

缺点:

  • 部分业务表无法join,只能通过接口调用,提升了系统的复杂度;
  • 跨库事务难以处理;
  • 垂直切分后,某些业务数据过于庞大,仍然存在单体性能瓶颈;

2、水平切分

  水平切分相比垂直切分,更为复杂。它需要将一个表中的数据,根据某种规则拆分到不同的数据库中,例如:订单尾号为奇数的订单放在了订单数据库1中,而订单尾号为偶数的订单放在了订单数据库2中。这样,原本存在一个数据库中的订单数据,被水平的切分成了两个数据库。在查询订单数据时,我们还要根据订单的尾号,判断这个订单在数据库1中,还是在数据库2中,然后将这条SQL语句发送到正确的数据库中,查出订单。

优点:

  • 解决了单库大数据、高并发的性能瓶颈;
  • 拆分规则封装好,对应用端几乎透明,开发人员无需关心拆分细节;
  • 提高了系统的稳定性和负载能力;

缺点:

  • 拆分规则很难抽象;
  • 分片事务一致性难以解决;
  • 二次扩展时,数据迁移、维护难度大。比如:开始我们按照用户id对2求模,但是随着业务的增长,2台数据库难以支撑,还是继续拆分成4个数据库,那么这时就需要做数据迁移了。

3、分库分表的两种模式

  • 客户端模式,在每个应用模块内,配置自己需要的数据源,直接访问数据库,在各模块内完成数据的整合;例如:sharding-jdbc
  • 中间代理模式,中间代理统一管理所有的数据源,数据库层对开发人员完全透明,开发人员无需关注拆分的细节。例如:MyCat

二、使用MyCat分库分表

1、系统环境

  • 使用VMware做虚拟机,创建3台机器
  • 操作系统使用Linux CentOS7
  • 采用yum方式,在两台机器上安装mysql
  • 在第三台机器上安装MyCat,并修改配置文件

2、mysql安装

(1)下载mysql的yum引导

下载地址:
https://dev.mysql.com/downloads/repo/yum/
注意选择对应的linux版本,这里采用linux7的,mysql 版本是8.0

(2)将文件上传到linux系统上
(3)安装mysql
yum localinstall mysql80-community-release-el7-3.noarch.rpm
yum install mysql-community-server
(4)启动mysql
service mysqld start
(5)查询登录的默认密码

查询结果最后一个冒号之后的就是密码

grep 'temporary password' /var/log/mysqld.log
(6)登录mysql
mysql -uroot -p

然后输入密码

(7)修改默认密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '这里想要的密码';

修改的密码需要复杂一点,一个大写字母、一个小写字母、一个数字和一个特殊字符,并且密码的总长度至少为8个字符,否则不符合密码策略无法修改成功

(8)创建用户并授权

创建用户
由于我们使用的是mysql8,密码的默认加密方式改变Navicat无法连接,需要指定为老的加密方式:identified with mysql_native_password
当我们指定了老的加密方式后,控制台的客户端连接时需要指定连接的加密方式:mysql -uroot -p --default-auth=mysql_native_password

create user '用户名'@'%' identified with mysql_natice_password by '密码';

授权所有权限

grant all on *.* to '用户名'@'%';

然刷新一下配置

flush privileges;

如果还是无法连接,尝试关闭linux的防火墙

3、mycat安装

(1)下载地址

dl.mycat.io/1.6.7.3

(2)将文件上传到linux系统上
(3)解压压缩包
tar -zxvf Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz
(4)修改server.xml

进入mycat根目录下的

vim config/server.xml

将root的schemas属性改成user

<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">user</property>
</user>

下面还有个user的配置可以直接删掉,或者把schemas属性也改成user

(5)修改schema.xml
vim config/schema.xml

配置分键表

<!-- 这个name要和server.xml配置中的schemas属性一致 />
<!-- sqlMaxLimit会默认在查询语句之后添加limit,防止一下查询多个库,数据量过大,想要额外查询需要自己添加limit />
<schema name="user" checkSQLschema="true" sqlMaxLimit="100">
    <table name="表名" dataNode="dn200,dn201" rule="auto-sharding-long">
</schema>

配置数据节点

<!-- dataHost需要和下面的配置的dataHost的name属性一致  />
<dataNode name="dn200" dataHost="db200" database="库名">
<dataNode name="dn201" dataHost="db201" database="库名">

修改dataHost

<!-- 配置第一个数据  />
<!-- balance:负载均衡类型:0不开启读写分离,1和2读写均匀分配,3读落在readHast上  />
<dataHost name="db200" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- 添加一个写库 />
    <writeHost host="M1" url="192.168.85.200:3306" user="用户名" password="密码">
        <!-- 添加一个读库 ,读写分离的时候使用,占时不需要 />
        <!-- <readHost host="S1" url="192.168.85.203" user="用户名" password="密码"> />
    </writeHost>
</dataHost>

<!-- 配置第二个数据 />
<dataHost name="db201" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- 添加一个写库 />
    <writeHost host="M2" url="192.168.85.201:3306" user="用户名" password="密码">
    </writeHost>
</dataHost>
(6)修改分键规则

在schema.xml中配置的分键规则为"auto-sharding-long"查看rule.xml,找到对应的规则

<tableRule name="auto-sharding-long">
    <rule>
        #分键的字段为id,就是表中必须有一个名为id的字段
        <columns>id</columns>
        <algorithm>rang-long</algorithm>
    </rule>
</tableRule>

看配置可以知道分键规则的计算方式为"algorithm",在文件的下面找到对应的计算方式

<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
</function>

具体的执行的类叫AutoPartitionByLong,配置文件叫autopartition-long.txt,在config目录下可以找到autopartition-long.txt,打开文件,并删除掉最后一行

#rang start-end,data node index
# K=1000,M=10000
0-500M=0
500M-1000M=1

这里的意思是,id在0-500*10000的时候放到第一个库,500*10000-1000*10000的时候放在第二个库,并且这里的规则要和schema.xml中配置的dataNode的数量一致

(7)在mysql中创建库和表
  • 创建库的时候,库名需要和 schema.xml配置文件中dataNode的database属性一致
  • 创建表的时候,表名需要和 schema.xml配置文件中schema下的table里的name属性一致
(8)启动mycat
#所有信息打到控制台
./bin/mycat console
#后台启动
./bin/mycat start
(9)使用Navicat进行连接

直接创建一个mysql的连接
连接地址:填写mycat的地址,我这里为192.168.85.202
端口:8066(mycat默认端口)
用户名:root(用户名密码都配置在server.xml中)
密码:123456

插入数据测试一下,可以根据id的规则分别插入不同的数据,再去对应的mysql里面查看数据是否分库成功

(10)使用Navicat连接mycat管理

直接创建一个mysql的连接
连接地址:填写mycat的地址,我这里为192.168.85.202
端口:9066(mycat管理端口)
用户名:root(用户名密码都配置在server.xml中)
密码:123456

这里面有个user库,双击是打不开的右键连接-》命令列介面,不要右键库,

#帮助
show @@help;

#刷新配置  这样就不用重启mycat,就能更变配置了
reload @@config_all;

4、mysql主从配置

(1)修改主配置文件

主机地址:192.168.85.200
打开mysql的配置文件

vim /etc/my.cnf

在mysqld添加两个配置

log-bin=test_mysql
server-id=1

重启mysql

service mysqld restart
(2)修改从配置文件

从机地址:192.168.85.204
打开mysql的配置文件

vim /etc/my.cnf

在mysqld添加两个配置

server-id=2

重启mysql

service mysqld restart
(3)主创建备份账号并授权REPLICATION SLAVE

主机上登录mysql客户端

mysql -uroot -p

创建replication的账号

create user 'repl'@'%' identified by '密码';

进行授权

grant replication slave on *.* to 'repl'@'%';

刷新权限

flush privileges;
(4)主进行锁表,用于向从库同步数据,锁表之后就无法进行写操作

主数客户端控制台中进行锁表

flush tables with read lock;

查询bin-log的日志定位,查询出来的东西需要记住,后面配置需要使用

show master status;
(5)主库原本就有的数据需要手动的复制到从库中

备份主库数据
复制一个新的会话界面,在linux下执行下面语句,之前的mysql客户端不能关闭,关闭后会释放表锁
在当前文件夹下会生成dbdump.db文件,根据数据量大小,执行时间会有所不同,可能会很长时间

mysqldump --all-databases --master-data > dbdump.db -uroot -p

将dbdump.db复制到从机上,什么方法都行复制过去就可以了,我这里使用scp命令

scp root@192.168.85.200:~/dbdump.db .

将数据还原到从数据库中,从数据原本的数据会被覆盖掉

mysql < dbdump.db -uroot -p
(6)解锁主库的表锁

在主机mysql的客户端里输入

unlock tables;
(7)在从上设置主的配置

在从机mysql的客户端里输入

CHANGE MASTER TO 
MASTER_HOST='主机ip地址', 
MASTER_USER='主从同步的用户(这里是repl)',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='第四部要记住的file名称',
MASTER_LOG_POS=第四部要记住的Positon(这里没有单引号的);

完整的如下

CHANGE MASTER TO 
MASTER_HOST='192.168.85.200', 
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='test-mysql.000001',
MASTER_LOG_POS=1460;

然后激活从库

start slave;

mysql的主从搭建完成!!

(8)mycat配置读写分离

修改schema.xml

vim config/schema.xml

修改dataHost

<!-- balance:负载均衡类型:0不开启读写分离,1和2读写均匀分配,3读落在readHast上  />
<!-- 这里的balance改成 1,2,3都没有问题>
<dataHost name="db200" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- 添加一个写库 />
    <writeHost host="M1" url="192.168.85.200:3306" user="用户名" password="密码">
        <!-- 添加一个读库/>
        <readHost host="S1" url="192.168.85.204" user="用户名" password="密码"> />
    </writeHost>
</dataHost>

连接的时候需要连接的mycat,这样写入操作mycat会分配给192.168.85.200来完成,然后192.168.85.200通过mysql的主从功能将数据同步给192.168.85.204,读操作mycat全部分配给192.168.85.204

5、mycat全局表和父子表

(1)全局表

在分片的情况下,当业务表因为规模而进行分片以后,业务表与这些附属的字典表之间的关联,就成了比较棘手的问题,考虑到字典表具有以下几个特性:
• 变动不频繁
• 数据量总体变化不大
• 数据规模不大,很少有超过数十万条记录。

鉴于此,MyCAT 定义了一种特殊的表,称之为“全局表”,全局表具有以下特性:
• 全局表的插入、更新操作会实时在所有节点上执行,保持各个分片的数据一致性
• 全局表的查询操作,只从一个节点获取
• 全局表可以跟任何一个表进行 JOIN 操作
打开schema.xml

vim schema.xml

再添加一个table标签

<table name="表名" dataNode="dn200,dn201" type="global" />

type为global的就是全局表

(2)父子表

在实际工作中经常会遇到父子级表或者叫详情表,例如:订单表和订单详情表,但是分库分表后,很有可能父表信息录入到了db1中,而子表信息录入进了db2中,这样就无法join查询了,mycat考虑到了这个问题,提供了父子表关系建立,让父子表的数据录入到同一个库中。
创建两张表,所有的数据库中都要创建
o_order表(order是关键字,这里使用o_order):

字段 中文解释
id id
total_amount 价格
order_status 订单状态

order_item表:

字段 中文解释
id id
order_id order表的id
product_name 商品名称
num 购买数量

修改mycat配置,打开schema.xml

vim schema.xml

再添加一个table标签

<table name="o_order" dataNode="dn200,dn201" type="auto-sharding-long">
    <childTable name="order_item" joinKey="order_id" parentKey="id"/>
</table>

三、MyCat-Ha

1、mycat高可用架构图

数据库读写分离、分库分表——mycat与shardingjdbc _ Java侠

2、mycat高可用架构搭建

(1)在两台机器上安装mycat

我这里安装的机器ip分别为:192.168.85.205和192.168.85.206
配置保持一致

(2)在两台机器上安装haproxy

我这里安装的机器ip分别为:192.168.85.210和192.168.85.211

#查询
yum search haproxy
#安装
yum -y install haproxy x86_64

修改配置文件

vim /etc/haproxy/haproxy.cfg

修改以下的配置,其他的保持不变

defaults
    mode        tcp
    option      tcplog
    #option    http-server-close
    #option    forwardfor   except  127.0.0.0/8
    ....
    
#这个5000是连接端口号
frontend main *:5000
    #ac | url_static     path_beg        -i /static /images /javascript /stylesheets
    #ac | url_static     path_end        -i .jpg .gif .png .css .js
    
    #这个是使用http的时候的配置,对应下方的backend static
    #use_backend static      if url_static
    #这个是使用tcp的时候的配置,对应下方的backend app
    default_packend         app
    
    ...
#配置连接的mycat
backend app
    balance     roundrobin
    server app1 192.168.85.205:8066 check
    server app2 192.168.85.206:8066 check

启动haproxy,会爆一些警告无所谓

haproxy -f /etc/haproxy/haproxy.cfg
(3)在两台机器上安装keepalived

安装keepalived,这里简单的安装一下
详细的可以参考,里面有详细的keepalived安装和说明:
https://blog.csdn.net/qq_34886352/article/details/103581973

#搜索
yum search keepalived
#安装
yum -y install keepalived.x86_64

修改keepalived配置文件

vim /etc/keepalived/keepalived.conf

注释掉vrrp_strict

#vrrp_strict

配置master节点

这里添加一个监听haproxy进程的脚本
vrrp_script chk_happroxy{
    #检测使用的语句  不存在返回1  存在返回0
    script "killall -0 haproxy"
    #两秒检测一次
    interval 2
}

#多余的vrrp_instance删除掉
vrrp_instance VI_1{
    state MASTER
    #网卡 这个需要自己查一下自己的网卡,推荐的文章里面有说明
    interface etc33
    ...(这些都不用改)
    virtual_ipaddress{
        192.168.85.20
    }
    #添加监听
    track_script{
        chk_haproxy
    }
}

#多余的virtual_server删除掉
virtual_server 192.168.85.20 6000{
    ...(这些都不用改)
    #对应的真实的主机的地址
    real_server 192.168.85.210 5000{
        weight 1
        TCP_CHECK{
            connect_port 5000
            connect_timeout 10000
        }
    }
}

配置slave节点

这里添加一个监听haproxy进程的脚本
vrrp_script chk_happroxy{
    #检测使用的语句  不存在返回1  存在返回0
    script "killall -0 haproxy"
    #两秒检测一次
    interval 2
}

#多余的vrrp_instance删除掉
vrrp_instance VI_1{
    state SLAVE
    #网卡 这个需要自己查一下自己的网卡,推荐的文章里面有说明
    interface etc33
    ...(这些都不用改)
    virtual_ipaddress{
        192.168.85.20
    }
    #添加监听
    track_script{
        chk_haproxy
    }
}

#多余的virtual_server删除掉
virtual_server 192.168.85.20 6000{
    ...(这些都不用改)
    #对应的真实的主机的地址
    real_server 192.168.85.211 5000{
        weight 1
        TCP_CHECK{
            connect_port 5000
            connect_timeout 10000
        }
    }
}

启动keepalived

keepalived -f /ect/keepalived/keepalived.conf
#或者
service keepalived start
(4)使用Navicat连接keepalived的虚拟ip

直接创建一个mysql的连接
连接地址:192.168.85.20(keepalivd的虚拟ip)
端口:6000(keepalived的监听端口)
用户名:root(用户名密码都配置在mycat的server.xml中)
密码:123456

四、基于Sharding-JDBC的读写分离和分库分表

官方文档:https://shardingsphere.apache.org/document/current/cn/quick-start/sharding-jdbc-quick-start/

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

每个库都创建2个t_order表,分别叫做t_order_1和t_order_2
t_order表:

字段 中文解释
order_id 订单id
total_amount 价格
order_status 订单状态
user_id 用户id

1、Spring整合sharding-jdbc

(1)引入maven依赖
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>4.0.0-RC2</version>
</dependency>
(2)springxml的配置
<?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:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
                        http://www.springframework.org/schema/context
                        http://www.springframework.org/schema/context/spring-context.xsd
                        http://www.springframework.org/schema/tx
                        http://www.springframework.org/schema/tx/spring-tx.xsd">
    
    <!-- 添加数据源 -->
    <bean id="ds0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <!-- 数据库驱动 -->
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="用户名"/>
        <property name="password" value="密码"/>
        <property name="jdbcUrl" value="jdbc:mysql://192.168.85.200:3306/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>
    
    <!-- 第二个数据源 -->
    <bean id="ds1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <!-- 数据库驱动 -->
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="用户名"/>
        <property name="password" value="密码"/>
        <property name="jdbcUrl" value="jdbc:mysql://192.168.85.201:3306/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>
    
    <!-- 配置sharding-jdbc -->
    <sharding:data-source id="sharding-data-source">
        <!-- 配置数据源 -->
        <sharding:sharding-rule data-source-name="ds0,ds1">
            <sharding:table-rules>
                <!-- logic-table :分片表的逻辑表名 -->
                <!-- atcual-data-nodes :实际的数据节点   ds$->{0..1}:分为两个部分ds是数据源的前缀,$->{0..1}是占位符,等同于${} -->
                <!--  database-strategy-ref :库的分片策略 -->
                <!--  table-strategy-ref :表的分片策略 -->
                <sharding:table-rule logic-table="t_order" 
                atcual-data-nodes="ds$->{0..1}.t_order_$->{1..2}"
                database-strategy-ref="databaseStrategy"
                table-strategy-ref="tableStrategy"
                />
            </sharding:table-rules>
        </sharding:sharding-rule>
    </sharding:data-source>
    
    <!-- 数据库的分片规则 -->
    <!-- sharding-column:分库使用的字段 -->
    <!-- algorithm-expression:分片规则,对user_id取模 -->
    <sharding:inline-strategy id="databaseStrategy" sharding-column="user_id" algorithm-expression="ds$->{user_id%2}"/>
    </beans>
    
    <!-- 表的分片规则 -->
    <sharding:inline-strategy id="tableStrategy" sharding-column="order_id" algorithm-expression="t_order_$->{order_id%2+1}"/>
    
    <bean class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="sharding-data-source"/>
        <property name="mapperLocations" value="classpath*:/mybatis/*.xml"/>
    </bean>
</beans>
(3)mybatis的注意事项
  • mybatis的xml文件里的表需要使用逻辑表名
  • @MapperScan不要忘记加了,不知道的回头看看mybatis的配置
  • 和正常使用mybatis一样就行了,会自动的去识别分库分表

2、SpringBoot整合sharding-jdbc

(1)引入maven依赖
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC2</version>
</dependency>
(2)application.properties配置
spring.shardingsphere.datasource.names=ds0,ds1

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.jdbcUrl=jdbc:mysql://192.168.85.200:3306/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false
spring.shardingsphere.datasource.ds0.username=
spring.shardingsphere.datasource.ds0.password=

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.jdbcUrl=jdbc:mysql://192.168.85.201:3306/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false
spring.shardingsphere.datasource.ds1.username=
spring.shardingsphere.datasource.ds1.password=

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order_$->{1..2}
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{user_id%2}

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}

mybatis.mapper-locations=/mybatis/*.xml
logging.pattern.dateformat=HH:mm:ss

3、广播表(全局表)配置

(1)创建表

所有数据库都创建一个地址表

字段 中文解释
id id
name 地区名
(2)设置广播表

spring修改xml文件

    <!-- 配置sharding-jdbc -->
    <sharding:data-source id="sharding-data-source">
        <!-- 配置数据源 -->
        <sharding:sharding-rule data-source-name="ds0,ds1">
            <sharding:table-rules>
                <!-- logic-table :分片表的逻辑表名 -->
                <!-- atcual-data-nodes :实际的数据节点   ds$->{0..1}:分为两个部分ds是数据源的前缀,$->{0..1}是占位符,等同于${} -->
                <!--  database-strategy-ref :库的分片策略 -->
                <!--  table-strategy-ref :表的分片策略 -->
                <sharding:table-rule logic-table="t_order" 
                atcual-data-nodes="ds$->{0..1}.t_order_$->{1..2}"
                database-strategy-ref="databaseStrategy"
                table-strategy-ref="tableStrategy"
                />
            </sharding:table-rules>
            
            <!-- 这里就是广播表的配置 -->
            <sharding:broadcast-table-rules>
                <sharding:broadcast-table-rule table="area"/>
            </sharding:broadcast-table-rules>
        </sharding:sharding-rule>
    </sharding:data-source>

springboot,修改application.properties配置

# 添加广播表配置
spring.shardingsphere.sharding.broadcast-tables=area

这样在插入和修改的时候,就会同时更新所有库中的这张表,也可以进行join查询了

4、绑定表(父子表)配置

(1)创建表

所有数据库都创建2个t_order_item表,分别叫做t_order_item_1和t_order_item_2

字段 中文解释
id id
order_id 订单表id
pruduct_name 商品名
user_id 用户id
(2)设置绑定表

spring修改xml文件

    <!-- 配置sharding-jdbc -->
    <sharding:data-source id="sharding-data-source">
        <!-- 配置数据源 -->
        <sharding:sharding-rule data-source-name="ds0,ds1">
            <sharding:table-rules>
                <!-- logic-table :分片表的逻辑表名 -->
                <!-- atcual-data-nodes :实际的数据节点   ds$->{0..1}:分为两个部分ds是数据源的前缀,$->{0..1}是占位符,等同于${} -->
                <!--  database-strategy-ref :库的分片策略 -->
                <!--  table-strategy-ref :表的分片策略 -->
                <sharding:table-rule logic-table="t_order" 
                atcual-data-nodes="ds$->{0..1}.t_order_$->{1..2}"
                database-strategy-ref="databaseStrategy"
                table-strategy-ref="tableStrategy"
                />
            </sharding:table-rules>
            
            <!-- 这里就是广播表的配置 -->
            <sharding:broadcast-table-rules>
                <sharding:broadcast-table-rule table="area"/>
            </sharding:broadcast-table-rules>
            
            <!-- 这里就是绑定表的配置 ,与mycat不同,sharding-jdbc 不需要指定,关联关系的字段,是通过两种表之间相同的字段进行关联的-->
            <sharding:binding-table-rules>
                <sharding:binding-table-rule logic-tables="t_order,t_order_item"/>
            </sharding:binding-table-rules>
        </sharding:sharding-rule>
    </sharding:data-source>

这里可能会有个bug,会提示广播表为空,主要原因是因为在初始化帮点表的时候,会检测是否同时是广播表,但是广播表尚未初始化,就会抛出空指针
如果以后问题解决,这里我再补上

5、读写分离

(1)springxml的配置
<?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:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
       xmlns:master-slave="http://shardingsphere.apache.org/schema/shardingsphere/masterslave"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/masterslave
                        http://shardingsphere.apache.org/schema/shardingsphere/masterslave/master-slave.xsd
                        http://www.springframework.org/schema/context
                        http://www.springframework.org/schema/context/spring-context.xsd
                        http://www.springframework.org/schema/tx
                        http://www.springframework.org/schema/tx/spring-tx.xsd">
    
    <!-- 添加数据源 -->
    <bean id="ds0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <!-- 数据库驱动 -->
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="用户名"/>
        <property name="password" value="密码"/>
        <property name="jdbcUrl" value="jdbc:mysql://192.168.85.200:3306/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>
    
    <!-- 添加一个从数据源 -->
    <bean id="slave0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <!-- 数据库驱动 -->
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="用户名"/>
        <property name="password" value="密码"/>
        <property name="jdbcUrl" value="jdbc:mysql://192.168.85.203:3306/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>
    
    <!-- 第二个数据源 -->
    <bean id="ms1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <!-- 数据库驱动 -->
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="username" value="用户名"/>
        <property name="password" value="密码"/>
        <property name="jdbcUrl" value="jdbc:mysql://192.168.85.201:3306/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false"/>
    </bean>
    
    <!-- 主从的读写规则  random:在所有的从库中随机查询-->
    <master-slave:load-balance-algorithm id="msStrategy" type="random">
    
    <!-- 配置sharding-jdbc -->
    <sharding:data-source id="sharding-data-source">
        <!-- 配置数据源 -->
        <sharding:sharding-rule data-source-name="ds0,slave0,ds1">
            <!-- 主从配置 -->
            <sharding:master-slave-rules>
                <sharding:master-slave-rule id="ms0" master-data-source-name="ds0" slave-data-source-names="slave0" strategy-ref="msStrategy"/>
            </sharding:master-slave-rules>
            <sharding:table-rules>
                <sharding:table-rule logic-table="t_order" 
                atcual-data-nodes="ms$->{0..1}.t_order_$->{1..2}"
                database-strategy-ref="databaseStrategy"
                table-strategy-ref="tableStrategy"
                />
            </sharding:table-rules>
        </sharding:sharding-rule>
    </sharding:data-source>
    
    <!-- 数据库的分片规则 -->
    <!-- sharding-column:分库使用的字段 -->
    <!-- algorithm-expression:分片规则,对user_id取模 -->
    <sharding:inline-strategy id="databaseStrategy" sharding-column="user_id" algorithm-expression="ms$->{user_id%2}"/>
    </beans>
    
    <!-- 表的分片规则 -->
    <sharding:inline-strategy id="tableStrategy" sharding-column="order_id" algorithm-expression="t_order_$->{order_id%2+1}"/>
    
    <bean class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="sharding-data-source"/>
        <property name="mapperLocations" value="classpath*:/mybatis/*.xml"/>
    </bean>
</beans>
(2)springboot,修改application.properties配置
spring.shardingsphere.datasource.names=ds0,ms1,slave0

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.jdbcUrl=jdbc:mysql://192.168.85.200:3306/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false
spring.shardingsphere.datasource.ds0.username=
spring.shardingsphere.datasource.ds0.password=

spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbcUrl=jdbc:mysql://192.168.85.203:3306/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false
spring.shardingsphere.datasource.slave0.username=
spring.shardingsphere.datasource.slave0.password=

spring.shardingsphere.datasource.ms1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ms1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ms1.jdbcUrl=jdbc:mysql://192.168.85.201:3306/sharding_order?serverTimezone=Asia/Shanghai&amp;useSSL=false
spring.shardingsphere.datasource.ms1.username=
spring.shardingsphere.datasource.ms1.password=

spring.shardingsphere.sharding.master-slave-rules.ms0.master-data-source-name=ds0
spring.shardingsphere.sharding.master-slave-rules.ms0.slave-data-source-name=slave0
spring.shardingsphere.sharding.master-slave-rules.ms0.load-balance.algorithm-type=RANDOM

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ms$->{0..1}.t_order_$->{1..2}
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ms$->{user_id%2}

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}

mybatis.mapper-locations=/mybatis/*.xml
logging.pattern.dateformat=HH:mm:ss
(3)配置完成之后,其他东西都是自动的,正常使用mybatis就可以了

作者:敲代码的旺财
来源链接:https://blog.csdn.net/qq_34886352/article/details/104458171

标签: 分库分表

“数据库读写分离、分库分表——mycat与shardingjdbc” 的相关文章

Mysql系列五:数据库分库分表中间件mycat的安装和mycat配置详解

Mysql系列五:数据库分库分表中间件mycat的安装和mycat配置详解

 一、mycat的安装 环境准备:准备一台虚拟机192.168.152.128 1. 下载mycat cd /softwarewget http:/...

看了这篇 MySQL 分库分表方案不懂才怪

看了这篇 MySQL 分库分表方案不懂才怪

点击上方「蓝字」关注我们 一、数据库瓶颈↑ 不管是IO瓶颈,还是CPU瓶颈,最终都会导致数据库的活跃连接数增加,进而逼近甚至达到...

数据库分库分表及MySQL主从复制实现数据库同步

数据库分库分表及MySQL主从复制实现数据库同步

一、为什么要分库分表 解决大数据存储时数据访问性能,具体来说就是解决超大容量问题和性能问题。 举例说明,订单表或用户表如果数据量达到上亿条记录,此时数据库的IO能力、处...

MySQL分库分表的技巧

分表是分散数据库压力的好方法。 分表,最直白的意思,就是将一个表结构分为多个表,然后,可以再同一个库里,也可以放到不同的库。 当然,首先要知道什...

MySQL锁,分库分表,慢查询

MySQL锁,分库分表,慢查询

mysql中各种锁详解 行锁 锁的基本模式 表锁...

MySQL数据库如何定制分库分表中间件详解

MySQL数据库如何定制分库分表中间件详解

一般来说,影响数据库最大的性能问题有两个,一个是对数据库的操作,一个是数据库中的数据太大。对于前者我们可以借助缓存来减少一部分读操作,针对一些复杂的报表分析和搜索可以交给&n...

MySQL之—分库分表的技巧

分表是分散数据库压力的好方法。分表,最直白的意思,就是将一个表结构分为多个表,然后,可以在同一个库里,也可以放到不同的库。当然,首先要知道什么情况下,才需要分表。个人觉得单表记录条数达到...

mysql分表 tidb

最近有一些同学跟我说『TiDB 搞不定分库分表的场景』,实话我当时真的二丈摸不着头脑,TiDB 从最开始设计的时候,就是要去解决分库分表这种解决方案所带来的一些问题...

mysql 分库分表介绍与水平切分和垂直切分的讲解

分库分表的介绍 当一个表太大不利于维护时,可考虑将大表拆分成小表,当然,这些表是属于同一个数据库的,这种技术成为分表;当一个数据库的处理能力不...

[MySQL] 分库分表需要考虑的问题

随着业务的增长,一般的公司都会经历一个从单库单表到分库分表的过程 , 需要考虑以下要素判断是否开始分库分表 1. 如果mysql单库的QPS超过1000就要考虑分库了 , 一般根据...