前文讲述了怎么实现mysql集群，高可用，以及如何实现自动邮件告警，前篇文章
现在我将主要讲述分库分表以及读写分离的实现。
- 安装shardingsphare
在此之前，请另装一台centos7机器
# 基于java,所以得安装java环境
yum install java-1.8.0-openjdk -y
yum install wget
# 若要获得其他版本，请访问官网
# 获得shardingsphere 5.4.1版本
wget https://archive.apache.org/dist/shardingsphere/5.4.1/apache-shardingsphere-5.4.1-shardingsphere-proxy-bin.tar.gz
# 解压
tar -zxvf apache-shardingsphere-5.4.1-shardingsphere-proxy-bin.tar.gz -C /usr/local
cd /usr/local
# 下载jdbc驱动
cd apache-shardingsphere-5.4.1-shardingsphere-proxy-bin
mkdir ext-lib
cd ext-lib
# 可直接搜索mysql-jdbc驱动选择合适的系统来获取
wget https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-j-8.0.33.tar.gz
# 解压驱动
tar -zxvf mysql-connector-j-8.0.33.tar.gz
# 进入驱动文件
cd mysql-connector-j-8.0.33
# 移动jar包到上层目录
mv mysql-connector-j-8.0.33.jar ../
# 删除驱动包以及驱动压缩包
rm -f mysql-connector-j-8.0.33.tar.gz
rm -rf mysql-connector-j-8.0.33
# 返回apache-shardingsphere-5.4.1-shardingsphere-proxy-bin文件层
cd ..
cd ..
编辑配置文件 vim conf/server.yaml 将以下配置开启并将sql-show更改为true
authority:
users:
- user: root@%
password: 1234
props:
sql-show:true # 开启日志
sudo firewall-cmd --zone=public --add-port=3307/tcp --permanent sudo firewall-cmd reload 连接shardingsphere 节点 mysql -uroot -p"1234" -h192.168.101.200 -P3307 --ssl-mode disabled 默认端口为3307 注意ip要正确 若是mysql不同版本之间连接，比如5.7和8.0之间连接，就需要加入取消ssl验证命令
以下为shardingsphere架构图
shardingshere-Proxy 作为中转来连接多台同一集群的机器，来实现读写分离，分库分表等操作
-- 在主节点创建物理库
create database db1;
-- 创建逻辑库
CREATE DATABASE db1;
USE db1;
-- 创建存储单元
REGISTER STORAGE UNIT ds_0 (
HOST="192.168.101.201",
PORT=3306,
DB="db1",
USER="root",
PASSWORD="1234"
),ds_1 (
HOST="192.168.101.202",
PORT=3306,
DB="db1",
USER="root",
PASSWORD="1234"
),ds_2 (
HOST="192.168.101.203",
PORT=3306,
DB="db1",
USER="root",
PASSWORD="1234"
);
Dsql用法
-- 查询存储单元
SHOW STORAGE UNITS\G;
-- 删除存储单元
-- UNREGISTER STORAGE UNIT ds_0, ds_1, ds_2;
-- 加载全部单表
LOAD SINGLE TABLE *.*;
-- 在shardinsphere中查询
SHOW TABLES;
SELECT * FROM table_name; -- 查看是否能够查到
-- 创建读写分离规则
-- TYPE(负载均衡算法): round_robin(轮询),random(随机),weight(权重)
CREATE READWRITE_SPLITTING RULE ms_group_0 (
WRITE_STORAGE_UNIT=ds_0,
READ_STORAGE_UNITS(ds_1,ds_2),
TYPE(NAME="round_robin")
);
-- 读写分离后，单表规则将会失效，这是shardingsphere bug导致的
-- 我们可以通过配置读写分离规则来实现读写分离
-- 查询规则
SHOW READWRITE_SPLITTING RULES;
-- 删除规则
DROP READWRITE_SPLITTING RULE ms_group_0;
-- 卸载全部单表
UNLOAD SINGLE TABLE *;
-- 查看逻辑表的元数据
SHOW TABLE METADATA t_user;
# 导出当前逻辑库配置
EXPORT DATABASE CONFIGURATION;
EXPORT DATABASE CONFIGURATION TO FILE "/root/my.yaml";
# 导入(仅支持对空逻辑库进行导入操作)
IMPORT DATABASE CONFIGURATION FROM FILE "/root/my.yaml";
读写分离 路由节点查询，可以通过监控日志文件来实现 tail -f /usr/local/apache-shardingsphere-5.4.1-shardingsphere-proxy-bin/logs/stdout.log 由于Dsql bug，我们无法同时配置读写分离和加载所有单表，我们可以把加载单表后的配置导出到/root/my.yaml， 把加载读写分离的配置导出到/root/my1.yaml，然后把两者配置不一样的地方拼接到my.yaml 最终 /root/my.yaml
databaseName: db1
dataSources:
ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db1
username: root
ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db1
username: root
ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db1
username: root
rules:
- !SINGLE
tables:
- ms_group_0.user1
- ms_group_0.user
- !READWRITE_SPLITTING
dataSources:
ms_group_0:
loadBalancerName: ms_group_0_round_robin
readDataSourceNames:
- ds_1
- ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: ds_0
loadBalancers:
ms_group_0_round_robin:
type: round_robin
将上述文件粘贴到 /usr/local/apache-shardingsphere-5.4.1-shardingsphere-proxy-bin/conf/config-readwrite-splitting.yaml 开头 vim usr/local/apache-shardingsphere-5.4.1-shardingsphere-proxy-bin/conf/config-readwrite-splitting.yaml
# 启动shardingsphere
cd usr/local/apache-shardingsphere-5.4.1-shardingsphere-proxy-bin/bin
./start.sh
# 终止shardingsphere
./stop.sh
查看读写分离是否配置成功 tail -f /usr/local/apache-shardingsphere-5.4.1-shardingsphere-proxy-bin/logs/stdout.log 如果查询时路由到ds1, ds2, 写入时路由到ds0,则说明配置正确
例如
[INFO ] 2025-02-13 13:41:42.214 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: select * from user
[INFO ] 2025-02-13 13:41:42.215 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from user
[INFO ] 2025-02-13 13:42:31.139 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: insert into user values(14, 'xiaoai')
[INFO ] 2025-02-13 13:42:31.140 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: ds_0 ::: insert into user values(14, 'xiaoai')
[INFO ] 2025-02-13 13:42:46.030 [ShardingSphere-Command-0] ShardingSphere-SQL - Logic SQL: select * from user
[INFO ] 2025-02-13 13:42:46.030 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: ds_2 ::: select * from user
到此处，读写分离已经配置成功
- 垂直分库
现在我将介绍垂直分库，垂直分库达到的效果是看似我们在处理一个库，其实是操作不同机器上的多个库, 现在创建两个库，一张库是db_goods, 一张库是db_users， db_goods库中有两张表：t_goods_detail， t_goods, db_user库中有一张表 t_user
- 在真实库中执行
如：在主库中执行
CREATE DATABASE db_user;
USE db_user;
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
user_id BIGINT,
name VARCHAR(16),
PRIMARY KEY(user_id)
);
INSERT INTO t_user VALUES
(2008228188779059194, 'fei1'),
(1982685543351059195, 'fei2'),
(1962498938790059196, 'fei3'),
(1942727340830059197, 'fei4');
-- db_goods
CREATE DATABASE db_goods;
USE db_goods;
DROP TABLE IF EXISTS t_goods;
CREATE TABLE t_goods(
good_id BIGINT,
name VARCHAR(16),
PRIMARY KEY(good_id)
);
INSERT INTO t_goods VALUES
(11200822819059194, 'goods1'),
(11198268551059195, 'goods2'),
(11196249890059196, 'goods3'),
(11194272730059197, 'goods4');
2965198946684
DROP TABLE IF EXISTS t_goods_detail;
CREATE TABLE t_goods_detail(
good_id BIGINT,
detail VARCHAR(16),
PRIMARY KEY(good_id)
);
INSERT INTO t_goods_detail VALUES
(11200822819059194, 'goods_detail_1'),
(11198268551059195, 'goods_detail_2'),
(11196249890059196, 'goods_detail_3'),
(11194272730059197, 'goods_detail_4');
- 修改shardingsphere 配置文件
vim /usr/local/apache-shardingsphere-5.4.1-shardingsphere-proxy-bin/conf/config-readwrite-splitting.yaml
databaseName: sharding_db # 逻辑库
dataSources:
db_goods_ds_2: # 真实库其中一个节点
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_goods
username: root
db_goods_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_goods
username: root
db_goods_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_goods
username: root
db_user_ds_2: # 上面为一个集群中的一个库，这些配置是集群中的另一个库
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_user
username: root
db_user_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_user
username: root
db_user_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_user
username: root
rules: # 加载所有单表
- !SINGLE
tables:
- "*.*"
- !READWRITE_SPLITTING
dataSources:
db_goods: # db_goods库的配置
loadBalancerName: round_robin # 轮询算法，就是下面的loadBalancers
readDataSourceNames:
- db_goods_ds_1 # db_goods库其中一个读节点
- db_goods_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_goods_ds_0 # 写节点
db_user:
loadBalancerName: round_robin
readDataSourceNames:
- db_user_ds_1
- db_user_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_user_ds_0
loadBalancers:
round_robin:
type: round_robin
- 水平分表
一张库创建六张表，这六张表合起来才是完整数据， 在这几张表通过分表策略来插入，增加，删除，改写数据 在真实库：如主库中执行 mysql
CREATE DATABASE db_order;
逻辑库配置
databaseName: sharding_db
dataSources:
db_goods_ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_goods
username: root
db_goods_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_goods
username: root
db_goods_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_goods
username: root
db_user_ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_user
username: root
db_user_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_user
username: root
db_user_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_user
username: root
db_order_ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_order
username: root
db_order_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_order
username: root
db_order_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_order
username: root
rules:
- !SINGLE
tables:
- db_user.*
- db_goods.*
- !SHARDING
tables:
t_order:
actualDataNodes: db_order.t_order${0..5}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: tb_inline_mod
shardingAlgorithms:
tb_inline_mod:
type: INLINE
props:
algorithm-expression: t_order${order_id % 6} # 分表策略
- !READWRITE_SPLITTING
dataSources:
db_goods:
loadBalancerName: round_robin
readDataSourceNames:
- db_goods_ds_1
- db_goods_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_goods_ds_0
db_user:
loadBalancerName: round_robin
readDataSourceNames:
- db_user_ds_1
- db_user_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_user_ds_0
db_order:
loadBalancerName: round_robin
readDataSourceNames:
- db_order_ds_1
- db_order_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_order_ds_0
loadBalancers:
round_robin:
type: round_robin
配置后重启
proxy中执行
CREATE TABLE t_order(
order_id BIGINT,
user_id BIGINT NOT NULL,
is_pay ENUM('yes', 'no') NOT NULL,
PRIMARY KEY(order_id)
); # 集群必须创建主键表，不含主键表集群重启会发生错误
INSERT INTO t_order VALUES
(1, 130, 'yes'),
(2, 132, 'yes'),
(3, 133, 'yes'),
(4, 134, 'yes'),
(5, 135, 'yes'),
(6, 136, 'yes'),
(7, 137, 'yes');
proxy 执行查询操作
use sharding_db;
select * from t_order;
路由信息
[INFO ] 2025-02-14 17:10:15.060 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: db_order_ds_1 ::: select * from t_order0 UNION ALL select * from t_order2 UNION ALL select * from t_order4
[INFO ] 2025-02-14 17:10:15.060 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: db_order_ds_2 ::: select * from t_order1 UNION ALL select * from t_order3 UNION ALL select * from t_order5
主库表中的数据
+--------------------+
| Tables_in_db_order |
+--------------------+
| t_order0 |
| t_order1 |
| t_order2 |
| t_order3 |
| t_order4 |
| t_order5 |
+--------------------+
每一条数据都落在想对应的表中
上述操作会将所有的增删改查操作根据分表规则路由到不同的表中，来实现水平分表 但是，在真实场景中，请设置比较多的分片，也就是将表分的足够多，如果分片比较少，后续若要在增加分片，极其极其困难，另外分库分表若无必要，请不要设置分库分表，详情请看阿里巴巴开发手册
- 数据迁移与水平分库
上述的数据只是存储在一个库中，后续我将将数据存储在多张库，多张表，如三库，每个库两张表 三库 db_order0, db_order1, db_order2 两表 t_order0, t_order1 可以手动迁移，也可以使用脚本迁移，我就用python脚本迁移了，这个脚本是小飞有点东西博主提供，详情请看他的视频，在各大视频网站都有 vim /root/shardings.py
# coding: utf-8
# 分片迁移
# @Author: 小飞有点东西
import argparse
def check_args(args):
"""
检查参数
:param args:
:return:
"""
# print(args)
if (args.mode == 'sharding' and None not in [args.db_name, args.db_num, args.tb_name, args.tb_num]) or (args.mode == 'drop' and None not in [args.db_name, args.db_num]):
return True
print(args.verbose)
return
def sharding(args):
"""
分片迁移
:param args:
:return:
"""
db_num = args.db_num
tb_num = args.tb_num
if db_num.isdigit() and tb_num.isdigit():
db_num = int(db_num)
tb_num = int(tb_num)
else:
print(verbose)
return
print(f'\n{args.mode} start..........')
db_tb_num = int(tb_num / db_num) # 每个库里面的分片数量
for i in range(db_num):
sql0 = f'CREATE DATABASE {args.db_name}{i}'
if not args.print:
# 创建库
session.sql(sql0).execute()
if args.sql:
print(sql0)
print(f'{args.db_name}{i}: ')
for j in range(db_tb_num):
sql1 = f'CREATE TABLE {args.db_name}{i}.{args.tb_name}{j} LIKE {args.db_name}.{args.tb_name}{j * db_num + i}' # 创建表
sql2 = f'INSERT INTO {args.db_name}{i}.{args.tb_name}{j} SELECT * FROM {args.db_name}.{args.tb_name}{j * db_num + i}' # 导入数据
if not args.print:
session.sql(sql1).execute()
session.sql(sql2).execute()
print(f'\t{args.tb_name}{j} <-- {args.db_name}.{args.tb_name}{j * db_num + i}')
if args.sql:
print(f'\t\t{sql1}')
print(f'\t\t{sql2}\n')
print(f'{args.mode} over..........')
def drop(args):
"""
批量删除库
:param args:
:return:
"""
db_num = args.db_num
if db_num.isdigit():
db_num = int(db_num)
else:
print(verbose)
return
print(f'\n{args.mode} start..........')
for i in range(db_num):
if not args.print:
session.sql(f'DROP DATABASE {args.db_name}{i}').execute()
print(f'DROP DATABASE {args.db_name}{i}')
print(f'{args.mode} over..........')
# 创建ArgumentParser对象，用于处理命令行参数
parser = argparse.ArgumentParser(description="这是一个命令行参数识别的示例")
# 添加命令行参数
parser.add_argument('-m', '--mode', choices=['sharding', 'drop'], help="运行模式")
parser.add_argument('-d', '--db_name', nargs='?', help="数据库名")
parser.add_argument('-n', '--db_num', nargs='?', help="分库数量")
parser.add_argument('-t', '--tb_name', nargs='?', help="表名")
parser.add_argument('-c', '--tb_num', nargs='?', help="分片数量")
parser.add_argument('-p', '--print', action='store_true', help="是否打印过程")
parser.add_argument('-s', '--sql', action='store_true', help="是否打印SQL")
parser.add_argument('-v', '--verbose', action='store_true', help="打印详细信息")
# 解析命令行参数
args = parser.parse_args() # Namespace(db_name='db_order', db_num='3', mode='drop', print=True, tb_name='t_order', tb_num='6', verbose=False)
# 参考文档
verbose = f"""
请在MySQL Shell \py里面执行:. {parser.prog} -d 库名 -n 分库数量 -t 表名 -c 分片数量 -m 模式')
分片迁移: MySQL Shell Py>. {parser.prog} -d db_order -n 3 -t t_order -c 6 -m sharding
批量删库: MySQL Shell Py>. {parser.prog} -d db_order -n 3 -m drop
-s: 打印sql
-p: 只打印过程,不执行
"""
# 使用解析后的命令行参数执行相应的操作
opt_dic = {
'sharding': sharding,
'drop': drop
}
# 入口
if args.verbose: # 有-v参数就打印文档
print(verbose)
elif args.mode and check_args(args): # 选择了模式,且参数有效,则执行对应方法
opt_dic.get(args.mode)(args)
else:
print(verbose)
执行脚本，使用 mysqlsh 执行, 并且在主库中执行 mysqlsh -uroot -p1234
\py // 使用py语法
./root/shardings.py -v # 查看帮助信息
# 查看迁移过程
./root/shardings.py -d db_order -n 3 -t t_order -c 6 -m sharding -p
# 执行迁移
./root/shardings.py -d db_order -n 3 -t t_order -c 6 -m sharding
迁移过程的展现
db_order0:
t_order0 <-- db_order.t_order0
t_order1 <-- db_order.t_order3
db_order1:
t_order0 <-- db_order.t_order1
t_order1 <-- db_order.t_order4
db_order2:
t_order0 <-- db_order.t_order2
t_order0 <-- db_order.t_order5
实际库中的库和表， 请主库中查询
db_order0
db_order1
db_order2
每张库中的表
t_order0
t_order0
shardingshere 配置
分库规则 order_id % 3 分表规则 (order_id / 3 ).toInteger() % 2 使用tabby中的sftp 的本地编辑，并用sublime软件比较好 也可以vim编辑，配置写得东西比较复杂，极其不推荐 vim /usr/local/apache-shardingsphere-5.4.1-shardingsphere-proxy-bin/conf/config-readwrite-splitting.yaml
databaseName: sharding_db
dataSources:
db_goods_ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_goods
username: root
db_goods_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_goods
username: root
db_goods_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_goods
username: root
db_user_ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_user
username: root
db_user_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_user
username: root
db_user_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_user
username: root
db_order0_ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_order0
username: root
db_order0_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_order0
username: root
db_order0_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_order0
username: root
db_order1_ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_order1
username: root
db_order1_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_order1
username: root
db_order1_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_order1
username: root
db_order2_ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_order2
username: root
db_order2_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_order2
username: root
db_order2_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_order2
username: root
rules:
- !SINGLE
tables:
- db_user.*
- db_goods.*
- !SHARDING
tables:
t_order:
actualDataNodes: db_order${0..2}.t_order${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: tb_inline_mod
databaseStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: db_inline_mod
shardingAlgorithms:
tb_inline_mod:
type: INLINE
props:
algorithm-expression: t_order${(order_id / 3).toInteger() % 2} # 分表策略
db_inline_mod:
type: INLINE
props:
algorithm-expression: db_order${order_id % 3} # 分库策略
- !READWRITE_SPLITTING
dataSources:
db_goods:
loadBalancerName: round_robin
readDataSourceNames:
- db_goods_ds_1
- db_goods_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_goods_ds_0
db_user:
loadBalancerName: round_robin
readDataSourceNames:
- db_user_ds_1
- db_user_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_user_ds_0
db_order0:
loadBalancerName: round_robin
readDataSourceNames:
- db_order0_ds_1
- db_order0_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_order0_ds_0
db_order1:
loadBalancerName: round_robin
readDataSourceNames:
- db_order1_ds_1
- db_order1_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_order1_ds_0
db_order2:
loadBalancerName: round_robin
readDataSourceNames:
- db_order2_ds_1
- db_order2_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_order2_ds_0
loadBalancers:
round_robin:
type: round_robin
数据迁移和分库分表时一定要注意，数据迁移前后的路由规则应该打到同一个分片上.这样才能实现真正的迁移和拓展
- 分片键的选择
以上部署只能使用一个分片键， 即order_id, 但实际场景我们不可能只使用一个分片键，如果不用分片键查询，则shardingsphere 不知道应该路由到哪一个库中，就会查询所有的分片，这样会极大降低效率. 解决以上问题，我们就需要加入业务自定义主键，在用户id, 客户id,订单编号等信息后面加入一些额外的信息，然后通过这些额外信息来具体定位是哪一个库，哪一个表。
下面是实际操作
进行下面操作时，请将 t_order清空， 不然上面的路由算法会和下面的路由算法冲突
shardingsphere 配置
databaseName: sharding_db
dataSources:
db_goods_ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_goods
username: root
db_goods_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_goods
username: root
db_goods_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_goods
username: root
db_user_ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_user
username: root
db_user_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_user
username: root
db_user_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_user
username: root
db_order0_ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_order0
username: root
db_order0_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_order0
username: root
db_order0_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_order0
username: root
db_order1_ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_order1
username: root
db_order1_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_order1
username: root
db_order1_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_order1
username: root
db_order2_ds_2:
password: 1234
url: jdbc:mysql://192.168.101.203:3306/db_order2
username: root
db_order2_ds_1:
password: 1234
url: jdbc:mysql://192.168.101.202:3306/db_order2
username: root
db_order2_ds_0:
password: 1234
url: jdbc:mysql://192.168.101.201:3306/db_order2
username: root
rules:
- !SINGLE
tables:
- db_user.*
- db_goods.*
- !SHARDING
tables:
t_order:
actualDataNodes: db_order${0..2}.t_order${0..1}
tableStrategy:
hint:
shardingAlgorithmName: t_order_inline_mod
t_lineitem:
actualDataNodes: db_order${0..2}.t_lineitem${0..1}
tableStrategy:
hint:
shardingAlgorithmName: t_lineitem_inline_mod
t_customer:
actualDataNodes: db_order${0..2}.t_customer${0..1}
tableStrategy:
hint:
shardingAlgorithmName: t_customer_inline_mod
defaultDatabaseStrategy:
hint:
shardingAlgorithmName: db_inline_mod
shardingAlgorithms:
t_order_inline_mod:
type: HINT_INLINE
props:
algorithm-expression: t_order${(value.toString().substring(value.toString().length() - 6).toInteger() / 3).toInteger() % 2}
t_lineitem_inline_mod:
type: HINT_INLINE
props:
algorithm-expression: t_lineitem${(value.toString().substring(value.toString().length() - 6).toInteger() / 3).toInteger() % 2}
t_customer_inline_mod:
type: HINT_INLINE
props:
algorithm-expression: t_customer${(value.toString().substring(value.toString().length() - 6).toInteger() / 3).toInteger() % 2}
db_inline_mod:
type: HINT_INLINE
props:
algorithm-expression: db_order${value.toString().substring(value.toString().length() - 6).toInteger() % 3}
- !READWRITE_SPLITTING
dataSources:
db_goods:
loadBalancerName: round_robin
readDataSourceNames:
- db_goods_ds_1
- db_goods_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_goods_ds_0
db_user:
loadBalancerName: round_robin
readDataSourceNames:
- db_user_ds_1
- db_user_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_user_ds_0
db_order0:
loadBalancerName: round_robin
readDataSourceNames:
- db_order0_ds_1
- db_order0_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_order0_ds_0
db_order1:
loadBalancerName: round_robin
readDataSourceNames:
- db_order1_ds_1
- db_order1_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_order1_ds_0
db_order2:
loadBalancerName: round_robin
readDataSourceNames:
- db_order2_ds_1
- db_order2_ds_2
transactionalReadQueryStrategy: DYNAMIC
writeDataSourceName: db_order2_ds_0
loadBalancers:
round_robin:
type: round_robin
重启之后再proxy中进行如下操作
CREATE TABLE t_order(
order_id BIGINT,
user_id BIGINT NOT NULL,
customer_id BIGINT NOT NULL,
is_pay ENUM('y', 'n') NOT NULL,
PRIMARY KEY(order_id)
);
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=1303544059194, SHARDING_TABLE_VALUE=1303544059194 */ INSERT INTO t_order VALUES(2029444142191059194, 1303544059194, 24425853092, 'y');
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=1303544059195, SHARDING_TABLE_VALUE=1303544059195 */ INSERT INTO t_order VALUES(1982685543351059195, 1303544059195, 24425853093, 'y');
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=1303544059196, SHARDING_TABLE_VALUE=1303544059196 */ INSERT INTO t_order VALUES(1962498938790059196, 1303544059196, 24425853094, 'y');
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=1303544059197, SHARDING_TABLE_VALUE=1303544059197 */ INSERT INTO t_order VALUES(1942727340830059197, 1303544059197, 24425853095, 'y');
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=1303544059194, SHARDING_TABLE_VALUE=1303544059194 */ SELECT * FROM t_order WHERE user_id=1303544059194;
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=2029444142191059194, SHARDING_TABLE_VALUE=2029444142191059194 */ SELECT * FROM t_order WHERE order_id=2029444142191059194;
在监控中如果看到以下信息，代表配置正确，且实现了路由
[INFO ] 2025-02-15 22:17:36.675 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: SELECT * FROM t_order WHERE order_id=2029444142191059194
[INFO ] 2025-02-15 22:17:36.675 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: db_order1_ds_1 ::: SELECT * FROM t_order1 WHERE order_id=2029444142191059194
将其余两张表补充完整、
CREATE TABLE t_lineitem(
lineitem_id BIGINT,
order_id BIGINT NOT NULL,
goods_id BIGINT NOT NULL,
num INT NOT NULL,
price INT NOT NULL,
PRIMARY KEY(lineitem_id)
);
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=2029444142191059194, SHARDING_TABLE_VALUE=2029444142191059194 */ INSERT INTO t_lineitem values(2100082092868518, 2029444142191059194, 2502058252851858, 2, 999),
(2100082092868519, 2029444142191059194, 2502058252851859, 1, 888);
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=2029444142191059195, SHARDING_TABLE_VALUE=2029444142191059195 */ INSERT INTO t_lineitem values(1190826098865554, 1982685543351059195, 1592852658555453, 2, 999);
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=2029444142191059196, SHARDING_TABLE_VALUE=2029444142191059196 */ INSERT INTO t_lineitem values(1190624099868593, 1962498938790059196, 1592652459859358, 1, 999);
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=2029444142191059197, SHARDING_TABLE_VALUE=2029444142191059197 */ INSERT INTO t_lineitem values(1190427092867534, 1942727340830059197, 1592452752753450, 1, 999);
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=2029444142191059194, SHARDING_TABLE_VALUE=2029444142191059194 */ SELECT * FROM t_lineitem WHERE order_id=2029444142191059194;
CREATE TABLE t_customer(
customer_id BIGINT,
user_id BIGINT NOT NULL,
name VARCHAR(16) NOT NULL,
addr VARCHAR(64) NOT NULL,
phone_number VARCHAR(11) NOT NULL,
PRIMARY KEY(customer_id)
);
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=1303544059194, SHARDING_TABLE_VALUE=1303544059194 */ INSERT INTO t_customer values(24425853092, 1303544059194, 'fei1', '四川省成都市高新区xx街道66号', '13035445001');
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=1303544059195, SHARDING_TABLE_VALUE=1303544059195 */ INSERT INTO t_customer values(24425853093, 1303544059195, 'fei2', '四川省成都市高新区xx街道67号', '13035445002');
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=1303544059196, SHARDING_TABLE_VALUE=1303544059196 */ INSERT INTO t_customer values(24425853094, 1303544059196, 'fei3', '四川省成都市高新区xx街道68号', '13035445003');
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=1303544059197, SHARDING_TABLE_VALUE=1303544059197 */ INSERT INTO t_customer values(24425853095, 1303544059197, 'fei4', '四川省成都市高新区xx街道69号', '13035445004');
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=1303544059194, SHARDING_TABLE_VALUE=1303544059194 */ SELECT * FROM t_customer WHERE user_id=1303544059194;
以上实现了业务自定义主键，且同一个用户都储存在同一个库中，但是如果连表查询且不用线索查询，会出现类似笛卡尔积的情况，仍然会出现查询多次的情况，例如
SELECT o.order_id, l.num, l.price, c.*
FROM t_order o
JOIN t_lineitem l ON o.order_id=l.order_id
JOIN t_customer c ON o.customer_id=c.customer_id;
监控中的信息
[INFO ] 2025-02-16 16:39:52.062 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: SELECT o.order_id, l.num, l.price, c.*
FROM t_order o
JOIN t_lineitem l ON o.order_id=l.order_id
JOIN t_customer c ON o.customer_id=c.customer_id
[INFO ] 2025-02-16 16:39:52.062 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: db_order2_ds_1 ::: SELECT o.order_id, l.num, l.price, c.*
FROM t_order0 o
JOIN t_lineitem0 l ON o.order_id=l.order_id
JOIN t_customer0 c ON o.customer_id=c.customer_id
实际sql连表会连很多表，严重影响效率，此时，就可以使用绑定表
# 绑定表配置 请写在分片（- !SHARDING）下面，和tables同级
bindingTables:
- t_order,t_lineitem,t_customer
绑定表将每一个相同库中的表绑定为一个表，这样配置以后，实际sql就只会查询六次，大大提高查询效
- 广播表
对于省市区，五十六个民族， 等这些固定信息，且短期一般不会进行更换信息的表，如果把这些信息都存储在一个库中，那么查询就要跨节点查询， 所以为提高查询效率，我们一般会把这些广播表在每个节点中都存储一份，在连表查询时，就只会查自己节点（每一个库）的呢份数据。从而提高效率
在shardingsphere中，如果已经配置了读写分离，再配置广播表，shardingsphere会将广播表路由到read_ds(读节点）而引发报错可以先配置广播表，再配置读写分离。 下面是具体配置
- !BROADCAST
tables:
- addr_province
- addr_city
- addr_county
请在proxy中执行
DROP TABLE IF EXISTS `addr_province`;
CREATE TABLE `addr_province` (
`id` int NOT NULL COMMENT '唯一ID',
`province_code` varchar(16) NOT NULL COMMENT '省份CODE',
`province_name` varchar(32) NOT NULL COMMENT '省份名称',
PRIMARY KEY (`id`)
) COMMENT='省份信息表';
DROP TABLE IF EXISTS `addr_city`;
CREATE TABLE `addr_city` (
`id` int NOT NULL COMMENT '唯一ID',
`city_code` varchar(16) NOT NULL COMMENT '城市CODE',
`city_name` varchar(32) NOT NULL COMMENT '城市名称',
`province_code` varchar(16) NOT NULL COMMENT '所属省份CODE',
PRIMARY KEY (`id`)
) COMMENT='行政区域地州市信息表';
DROP TABLE IF EXISTS `addr_county`;
CREATE TABLE `addr_county` (
`id` int NOT NULL COMMENT '唯一ID',
`county_code` varchar(16) NOT NULL COMMENT '区县CODE',
`county_name` varchar(32) NOT NULL COMMENT '区县名称',
`city_code` varchar(16) NOT NULL COMMENT '所属城市CODE',
PRIMARY KEY (`id`)
) COMMENT='行政区域县区信息表';
shardingsphere 中的事务设计 local 事务， xa 事务 ， 柔性事务
这里不做过多赘述，如要深入学习，请看下面文章
结尾
到此处，我的讲解也基本结束了，其实还有很多知识没有讲到，比如排序uuid, 怎么使用排序uuid来做分片键的设计与路由等等，但由于时间关系我就不说了，如果各位能够看到这里，那是我莫大的荣幸，我也希望各位能够对我的文章进行指点，如果有不足之处，欢迎大家的批评与指导.
联系方式：1684923397@qq.com
