目录
一、读写分离配置
注意是分库分表 + 读写分离的配置。ShardingSphere的版本3.x、4.0.1、4.1.1、5.0.0-alpha都存在很大的配置差异,这里使用4.1.1的配置。
其中多从库负载均衡算法类型,可选值:ROUND_ROBIN(轮询)、RANDOM(随机)、WEIGHT(权重)。
spring:
# 分表分库
shardingsphere:
datasource:
# 定义数据源名称
names: ds1,ds2,ds1-salve,ds2-salve
# ds1数据源配置
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://${remote.ip}:3307/nomswc_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
username: root
password: 123456
initialSize: 5
minIdle: 2
maxActive: 20
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
logSlowSql: true
# ds2数据源配置
ds2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://${remote.ip}:3307/nomswc_db_2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
username: root
password: 123456
initialSize: 5
minIdle: 2
maxActive: 20
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
logSlowSql: true
ds1-salve:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://${remote.ip}:3308/nomswc_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
username: root
password: 123456
initialSize: 5
minIdle: 2
maxActive: 20
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
logSlowSql: true
ds2-salve:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://${remote.ip}:3308/nomswc_db_2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
username: root
password: 123456
initialSize: 5
minIdle: 2
maxActive: 20
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
logSlowSql: true
sharding:
# 默认数据源,即未配置分表规则的表数据存储表
default-data-source-name: ds1
# 绑定表关系,
binding-tables: wc_pendant_tab_detail,wc_pendant_tab_extend
# 分库分表情况
# ds1 ds2:wc_pendant_tab_detail_1 wc_pendant_tab_detail_2
tables:
# 对应binding-tables配置
wc_pendant_tab_detail:
# 表节点
actual-data-nodes: ds$->{1..2}.wc_pendant_tab_detail_$->{1..2}
# 主键字段及生成策略(雪花算法)
# key-generator:
# column: tab_id
# type: SNOWFLAKE
# 分库规则
database-strategy:
inline:
sharding-column: mgdb_id
algorithm-expression: ds$->{Long.valueOf(mgdb_id) % 2 + 1}
# 分表规则
table-strategy:
inline:
sharding-column: mgdb_id
algorithm-expression: wc_pendant_tab_detail_$->{Math.floorDiv(Long.valueOf(mgdb_id),2L) % 2 + 1}
# 对应binding-tables配置
wc_pendant_tab_extend:
actual-data-nodes: ds$->{1..2}.wc_pendant_tab_extend_$->{1..2}
key-generator:
column: id
type: SNOWFLAKE
# 分库规则
database-strategy:
inline:
sharding-column: mgdb_id
algorithm-expression: ds$->{Long.valueOf(mgdb_id) % 2 + 1}
# 分表规则
table-strategy:
inline:
sharding-column: mgdb_id
algorithm-expression: wc_pendant_tab_extend_$->{Math.floorDiv(Long.valueOf(mgdb_id),2L) % 2 + 1}
# 读写分离
master-slave-rules:
ds1:
# 从库负载均衡算法类型,可选值:ROUND_ROBIN(轮询)、RANDOM(随机)、WEIGHT(权重)
load-balance-algorithm-type: round_robin
# 主库数据源名称
master-data-source-name: ds1
# 从库数据源名称列表,多个逗号分隔
slave-data-source-names: ds1-salve
ds2:
load-balance-algorithm-type: round_robin
master-data-source-name: ds2
slave-data-source-names: ds2-salve
# 打开sql输出日志
props:
sql:
show: true
二、验证读写分离
1. 写验证
a. SQL语句
// 插入wc_pendant_tab_detail
<insert id="insert">
insert into wc_pendant_tab_detail(tab_id,mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id)
values (#{tabId},#{mgdbId}, #{period}, #{tabType}, #{isShow}, #{name}, #{url}, #{descripton}, #{delFalg}, #{createTime}, #{createBy}, #{updateTime}, #{updateBy}, #{appId}, #{terminalId})
</insert>
// 插入wc_pendant_tab_extend
<insert id="insert" keyColumn="id" keyProperty="id" useGeneratedKeys="true">
insert into wc_pendant_tab_extend(mgdb_id, name, keywords, create_time, create_by, update_time, update_by)
values (#{mgdbId}, #{name}, #{keywords}, #{createTime}, #{createBy}, #{updateTime}, #{updateBy})
</insert>
b. Service层
@Transactional
@ShardingTransactionType(TransactionType.XA)
@Override
public void testTransactionXA() {
// 获取分布式主键
String tabId = getRedisId();
// 组装对象
WcPendantTabDetail wcPendantTabDetail = getWcPendantTabDetail(tabId);
WcPendantTabExtend wcPendantTabExtend = getWcPendantTabExtend(wcPendantTabDetail);
// 保存DB
int detail = wcPendantTabDetailDao.insert(wcPendantTabDetail);
LogUtil.info(String.format("insert wcPendantTabDetail: %s", detail));
int extend = wcPendantTabExtendDao.insert(wcPendantTabExtend);
LogUtil.info(String.format("insert wcPendantTabExtend: %s", extend));
}
c. 日志打印
从日志看出,数据插入到ds1数据源分片主库中。
2021-12-22 11:00:42.853 INFO [,70af00327f162fdf,70af00327f162fdf,true] 8112 --- [nio-9014-exec-3] ShardingSphere-SQL : Logic SQL: insert into wc_pendant_tab_detail(tab_id,mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id)
values (?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2021-12-22 11:00:42.854 INFO [,70af00327f162fdf,70af00327f162fdf,true] 8112 --- [nio-9014-exec-3] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@24f79cb4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@70750d6), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@70750d6, columnNames=[tab_id, mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id], insertValueContexts=[InsertValueContext(parametersCount=15, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=195, stopIndex=195, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=197, stopIndex=197, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=200, stopIndex=200, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=203, stopIndex=203, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=206, stopIndex=206, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=209, stopIndex=209, parameterMarkerIndex=5), ParameterMarkerExpressionSegment(startIndex=212, stopIndex=212, parameterMarkerIndex=6), ParameterMarkerExpressionSegment(startIndex=215, stopIndex=215, parameterMarkerIndex=7), ParameterMarkerExpressionSegment(startIndex=218, stopIndex=218, parameterMarkerIndex=8), ParameterMarkerExpressionSegment(startIndex=221, stopIndex=221, parameterMarkerIndex=9), ParameterMarkerExpressionSegment(startIndex=224, stopIndex=224, parameterMarkerIndex=10), ParameterMarkerExpressionSegment(startIndex=227, stopIndex=227, parameterMarkerIndex=11), ParameterMarkerExpressionSegment(startIndex=230, stopIndex=230, parameterMarkerIndex=12), ParameterMarkerExpressionSegment(startIndex=233, stopIndex=233, parameterMarkerIndex=13), ParameterMarkerExpressionSegment(startIndex=236, stopIndex=236, parameterMarkerIndex=14)], parameters=[300004407484, 300004407484, 0, ENTERTAINMENT_R_DETAIL, 1, test300004407484, https://assets.alicdn.com/g/tm/login/3.0.2/img/logo.png, Description300004407484, 0, 2021-12-22 11:00:42.657, admin, 2021-12-22 11:00:42.657, admin, 203023, android])], generatedKeyContext=Optional.empty)
2021-12-22 11:00:42.854 INFO [,70af00327f162fdf,70af00327f162fdf,true] 8112 --- [nio-9014-exec-3] ShardingSphere-SQL : Actual SQL: ds1 ::: insert into wc_pendant_tab_detail_1(tab_id,mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id)
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [300004407484, 300004407484, 0, ENTERTAINMENT_R_DETAIL, 1, test300004407484, https://assets.alicdn.com/g/tm/login/3.0.2/img/logo.png, Description300004407484, 0, 2021-12-22 11:00:42.657, admin, 2021-12-22 11:00:42.657, admin, 203023, android]
2021-12-22 11:00:42.890 WARN [,70af00327f162fdf,70af00327f162fdf,true] 8112 --- [nio-9014-exec-3] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.151.202:3307/nomswc_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC, version : 1.2.8, lastPacketReceivedIdleMillis : 1210416
2021-12-22 11:00:42.902 WARN [,70af00327f162fdf,70af00327f162fdf,true] 8112 --- [nio-9014-exec-3] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.151.202:3307/nomswc_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC, version : 1.2.8, lastPacketReceivedIdleMillis : 1212220
2021-12-22 11:00:43.136 INFO [,70af00327f162fdf,70af00327f162fdf,true] 8112 --- [nio-9014-exec-3] ShardingSphere-SQL : Logic SQL: insert into wc_pendant_tab_extend(mgdb_id, name, keywords, create_time, create_by, update_time, update_by)
values (?, ?, ?, ?, ?, ?, ?)
2021-12-22 11:00:43.136 INFO [,70af00327f162fdf,70af00327f162fdf,true] 8112 --- [nio-9014-exec-3] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@5339edc4, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3175358a), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3175358a, columnNames=[mgdb_id, name, keywords, create_time, create_by, update_time, update_by], insertValueContexts=[InsertValueContext(parametersCount=7, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=123, stopIndex=123, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=126, stopIndex=126, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=129, stopIndex=129, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=132, stopIndex=132, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=135, stopIndex=135, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=138, stopIndex=138, parameterMarkerIndex=5), ParameterMarkerExpressionSegment(startIndex=141, stopIndex=141, parameterMarkerIndex=6), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=7))], parameters=[300004407484, test300004407484, Keywords300004407484, 2021-12-22 11:00:42.657, admin, 2021-12-22 11:00:42.657, admin])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=id, generated=true, generatedValues=[680368298893770752])])
2021-12-22 11:00:43.136 INFO [,70af00327f162fdf,70af00327f162fdf,true] 8112 --- [nio-9014-exec-3] ShardingSphere-SQL : Actual SQL: ds1 ::: insert into wc_pendant_tab_extend_1(mgdb_id, name, keywords, create_time, create_by, update_time, update_by, id)
values (?, ?, ?, ?, ?, ?, ?, ?) ::: [300004407484, test300004407484, Keywords300004407484, 2021-12-22 11:00:42.657, admin, 2021-12-22 11:00:42.657, admin, 680368298893770752]
2. 读验证
a. SQL语句
<select id="queryAllByLimit" resultMap="WcPendantTabDetailMap">
select
tab_id, mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id
from wc_pendant_tab_detail order by tab_id asc
limit #{offset}, #{limit}
</select>
b. 日志打印
从日志看出,从ds1-slave、ds2-slave两个数据源分片从库中读取数据。
2021-12-22 11:09:03.652 WARN [,,,] 8112 --- [ngPollService-1] c.c.framework.apollo.util.ConfigUtil : app.id is not set, please make sure it is set in classpath:/META-INF/app.properties, now apollo will only load public namespace configurations!
2021-12-22 11:09:04.462 INFO [,a57cdff8de8884d3,a57cdff8de8884d3,true] 8112 --- [nio-9014-exec-5] ShardingSphere-SQL : Logic SQL: select
tab_id, mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id
from wc_pendant_tab_detail order by tab_id asc
limit ?, ?
2021-12-22 11:09:04.462 INFO [,a57cdff8de8884d3,a57cdff8de8884d3,true] 8112 --- [nio-9014-exec-5] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@5b549992, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2ca6de94), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@2ca6de94, projectionsContext=ProjectionsContext(startIndex=17, stopIndex=160, distinctRow=false, projections=[ColumnProjection(owner=null, name=tab_id, alias=Optional.empty), ColumnProjection(owner=null, name=mgdb_id, alias=Optional.empty), ColumnProjection(owner=null, name=period, alias=Optional.empty), ColumnProjection(owner=null, name=tab_type, alias=Optional.empty), ColumnProjection(owner=null, name=is_show, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=null, name=url, alias=Optional.empty), ColumnProjection(owner=null, name=descripton, alias=Optional.empty), ColumnProjection(owner=null, name=del_falg, alias=Optional.empty), ColumnProjection(owner=null, name=create_time, alias=Optional.empty), ColumnProjection(owner=null, name=create_by, alias=Optional.empty), ColumnProjection(owner=null, name=update_time, alias=Optional.empty), ColumnProjection(owner=null, name=update_by, alias=Optional.empty), ColumnProjection(owner=null, name=app_id, alias=Optional.empty), ColumnProjection(owner=null, name=terminal_id, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@22613570, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@265eb5e4, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@635c3f4b, containsSubquery=false)
2021-12-22 11:09:04.462 INFO [,a57cdff8de8884d3,a57cdff8de8884d3,true] 8112 --- [nio-9014-exec-5] ShardingSphere-SQL : Actual SQL: ds1-salve ::: select
tab_id, mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id
from wc_pendant_tab_detail_1 order by tab_id asc
limit ?, ? ::: [0, 2]
2021-12-22 11:09:04.462 INFO [,a57cdff8de8884d3,a57cdff8de8884d3,true] 8112 --- [nio-9014-exec-5] ShardingSphere-SQL : Actual SQL: ds1-salve ::: select
tab_id, mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id
from wc_pendant_tab_detail_2 order by tab_id asc
limit ?, ? ::: [0, 2]
2021-12-22 11:09:04.462 INFO [,a57cdff8de8884d3,a57cdff8de8884d3,true] 8112 --- [nio-9014-exec-5] ShardingSphere-SQL : Actual SQL: ds2-salve ::: select
tab_id, mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id
from wc_pendant_tab_detail_1 order by tab_id asc
limit ?, ? ::: [0, 2]
2021-12-22 11:09:04.462 INFO [,a57cdff8de8884d3,a57cdff8de8884d3,true] 8112 --- [nio-9014-exec-5] ShardingSphere-SQL : Actual SQL: ds2-salve ::: select
tab_id, mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id
from wc_pendant_tab_detail_2 order by tab_id asc
limit ?, ? ::: [0, 2]
2021-12-22 11:09:04.472 WARN [,a57cdff8de8884d3,a57cdff8de8884d3,true] 8112 --- [nio-9014-exec-5] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.151.202:3308/nomswc_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC, version : 1.2.8, lastPacketReceivedIdleMillis : 1671641
2021-12-22 11:09:04.481 WARN [,a57cdff8de8884d3,a57cdff8de8884d3,true] 8112 --- [nio-9014-exec-5] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.151.202:3308/nomswc_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC, version : 1.2.8, lastPacketReceivedIdleMillis : 1713437
2021-12-22 11:09:04.569 WARN [,a57cdff8de8884d3,a57cdff8de8884d3,true] 8112 --- [nio-9014-exec-5] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.151.202:3308/nomswc_db_2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC, version : 1.2.8, lastPacketReceivedIdleMillis : 1671738
2021-12-22 11:09:04.575 WARN [,a57cdff8de8884d3,a57cdff8de8884d3,true] 8112 --- [nio-9014-exec-5] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.151.202:3308/nomswc_db_2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC, version : 1.2.8, lastPacketReceivedIdleMillis : 1713358
三、强制读取主库
假如写入数据后,需要立刻读该数据(可能读不到),原因是Mysql默认使用异步复制,具有一定延时性,从库并没有该数据。这时可以通过Hint强制读取主库。
ShardingSphere-JDBC的读写分离为了最大限度避免由于同步延迟而产生强制读取主库的场景,这方面做了优化。在同一请求线程中,只要先更新数据库操作,则该操作之后的任何访问数据库都会自动路由到主库。因此,在写后读的场景下不需要使用HintManager。只有在读的场景下,需要强制读主库,才可以使用HintManager。
已下示例代码,演示HintManager的使用,强制读取主库。并附有打印日志,可以看出从ds1、ds2两个数据源分片主库中读取数据。
@Override
public WcPendantTabDetail testHintManager(){
// 获取分布式主键
String tabId = getRedisId();
// 组装对象
WcPendantTabDetail wcPendantTabDetail = getWcPendantTabDetail(tabId);
WcPendantTabExtend wcPendantTabExtend = getWcPendantTabExtend(wcPendantTabDetail);
// 插入数据
wcPendantTabDetailDao.insert(wcPendantTabDetail);
wcPendantTabExtendDao.insert(wcPendantTabExtend);
// 强制从主库查询数据
HintManager.getInstance().setMasterRouteOnly();
WcPendantTabDetail result = wcPendantTabDetailDao.queryByTabId(tabId);
return result;
}
2021-12-22 14:38:41.416 INFO [,eb9cf006cdcb72d4,eb9cf006cdcb72d4,true] 14076 --- [nio-9014-exec-1] ShardingSphere-SQL : Logic SQL: insert into wc_pendant_tab_detail(tab_id,mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id)
values (?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2021-12-22 14:38:41.416 INFO [,eb9cf006cdcb72d4,eb9cf006cdcb72d4,true] 14076 --- [nio-9014-exec-1] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@fd54114, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@48a350d6), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@48a350d6, columnNames=[tab_id, mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id], insertValueContexts=[InsertValueContext(parametersCount=15, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=195, stopIndex=195, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=197, stopIndex=197, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=200, stopIndex=200, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=203, stopIndex=203, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=206, stopIndex=206, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=209, stopIndex=209, parameterMarkerIndex=5), ParameterMarkerExpressionSegment(startIndex=212, stopIndex=212, parameterMarkerIndex=6), ParameterMarkerExpressionSegment(startIndex=215, stopIndex=215, parameterMarkerIndex=7), ParameterMarkerExpressionSegment(startIndex=218, stopIndex=218, parameterMarkerIndex=8), ParameterMarkerExpressionSegment(startIndex=221, stopIndex=221, parameterMarkerIndex=9), ParameterMarkerExpressionSegment(startIndex=224, stopIndex=224, parameterMarkerIndex=10), ParameterMarkerExpressionSegment(startIndex=227, stopIndex=227, parameterMarkerIndex=11), ParameterMarkerExpressionSegment(startIndex=230, stopIndex=230, parameterMarkerIndex=12), ParameterMarkerExpressionSegment(startIndex=233, stopIndex=233, parameterMarkerIndex=13), ParameterMarkerExpressionSegment(startIndex=236, stopIndex=236, parameterMarkerIndex=14)], parameters=[300004407490, 300004407490, 0, ENTERTAINMENT_R_DETAIL, 1, test300004407490, https://assets.alicdn.com/g/tm/login/3.0.2/img/logo.png, Description300004407490, 0, 2021-12-22 14:38:40.111, admin, 2021-12-22 14:38:40.111, admin, 203023, android])], generatedKeyContext=Optional.empty)
2021-12-22 14:38:41.417 INFO [,eb9cf006cdcb72d4,eb9cf006cdcb72d4,true] 14076 --- [nio-9014-exec-1] ShardingSphere-SQL : Actual SQL: ds1 ::: insert into wc_pendant_tab_detail_2(tab_id,mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id)
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [300004407490, 300004407490, 0, ENTERTAINMENT_R_DETAIL, 1, test300004407490, https://assets.alicdn.com/g/tm/login/3.0.2/img/logo.png, Description300004407490, 0, 2021-12-22 14:38:40.111, admin, 2021-12-22 14:38:40.111, admin, 203023, android]
2021-12-22 14:38:41.566 INFO [,eb9cf006cdcb72d4,eb9cf006cdcb72d4,true] 14076 --- [nio-9014-exec-1] ShardingSphere-SQL : Logic SQL: insert into wc_pendant_tab_extend(mgdb_id, name, keywords, create_time, create_by, update_time, update_by)
values (?, ?, ?, ?, ?, ?, ?)
2021-12-22 14:38:41.566 INFO [,eb9cf006cdcb72d4,eb9cf006cdcb72d4,true] 14076 --- [nio-9014-exec-1] ShardingSphere-SQL : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@76cc3d5, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@65f08eb7), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@65f08eb7, columnNames=[mgdb_id, name, keywords, create_time, create_by, update_time, update_by], insertValueContexts=[InsertValueContext(parametersCount=7, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=123, stopIndex=123, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=126, stopIndex=126, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=129, stopIndex=129, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=132, stopIndex=132, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=135, stopIndex=135, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=138, stopIndex=138, parameterMarkerIndex=5), ParameterMarkerExpressionSegment(startIndex=141, stopIndex=141, parameterMarkerIndex=6), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=7))], parameters=[300004407490, test300004407490, Keywords300004407490, 2021-12-22 14:38:40.111, admin, 2021-12-22 14:38:40.111, admin])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=id, generated=true, generatedValues=[680423153981194240])])
2021-12-22 14:38:41.803 INFO [,eb9cf006cdcb72d4,eb9cf006cdcb72d4,true] 14076 --- [nio-9014-exec-1] ShardingSphere-SQL : Actual SQL: ds1 ::: insert into wc_pendant_tab_extend_2(mgdb_id, name, keywords, create_time, create_by, update_time, update_by, id)
values (?, ?, ?, ?, ?, ?, ?, ?) ::: [300004407490, test300004407490, Keywords300004407490, 2021-12-22 14:38:40.111, admin, 2021-12-22 14:38:40.111, admin, 680423153981194240]
2021-12-22 14:38:42.124 INFO [,eb9cf006cdcb72d4,eb9cf006cdcb72d4,true] 14076 --- [nio-9014-exec-1] ShardingSphere-SQL : Logic SQL: select
tab_id, mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id
from wc_pendant_tab_detail where tab_id = ?
2021-12-22 14:38:42.124 INFO [,eb9cf006cdcb72d4,eb9cf006cdcb72d4,true] 14076 --- [nio-9014-exec-1] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@1d67fb85, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@655800cc), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@655800cc, projectionsContext=ProjectionsContext(startIndex=17, stopIndex=160, distinctRow=false, projections=[ColumnProjection(owner=null, name=tab_id, alias=Optional.empty), ColumnProjection(owner=null, name=mgdb_id, alias=Optional.empty), ColumnProjection(owner=null, name=period, alias=Optional.empty), ColumnProjection(owner=null, name=tab_type, alias=Optional.empty), ColumnProjection(owner=null, name=is_show, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=null, name=url, alias=Optional.empty), ColumnProjection(owner=null, name=descripton, alias=Optional.empty), ColumnProjection(owner=null, name=del_falg, alias=Optional.empty), ColumnProjection(owner=null, name=create_time, alias=Optional.empty), ColumnProjection(owner=null, name=create_by, alias=Optional.empty), ColumnProjection(owner=null, name=update_time, alias=Optional.empty), ColumnProjection(owner=null, name=update_by, alias=Optional.empty), ColumnProjection(owner=null, name=app_id, alias=Optional.empty), ColumnProjection(owner=null, name=terminal_id, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@3beaf7c4, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@b6c1aab, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@29757390, containsSubquery=false)
2021-12-22 14:38:42.124 INFO [,eb9cf006cdcb72d4,eb9cf006cdcb72d4,true] 14076 --- [nio-9014-exec-1] ShardingSphere-SQL : Actual SQL: ds1 ::: select
tab_id, mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id
from wc_pendant_tab_detail_1 where tab_id = ? ::: [300004407490]
2021-12-22 14:38:42.125 INFO [,eb9cf006cdcb72d4,eb9cf006cdcb72d4,true] 14076 --- [nio-9014-exec-1] ShardingSphere-SQL : Actual SQL: ds1 ::: select
tab_id, mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id
from wc_pendant_tab_detail_2 where tab_id = ? ::: [300004407490]
2021-12-22 14:38:42.125 INFO [,eb9cf006cdcb72d4,eb9cf006cdcb72d4,true] 14076 --- [nio-9014-exec-1] ShardingSphere-SQL : Actual SQL: ds2 ::: select
tab_id, mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id
from wc_pendant_tab_detail_1 where tab_id = ? ::: [300004407490]
2021-12-22 14:38:42.125 INFO [,eb9cf006cdcb72d4,eb9cf006cdcb72d4,true] 14076 --- [nio-9014-exec-1] ShardingSphere-SQL : Actual SQL: ds2 ::: select
tab_id, mgdb_id, period, tab_type, is_show, name, url, descripton, del_falg, create_time, create_by, update_time, update_by, app_id, terminal_id
from wc_pendant_tab_detail_2 where tab_id = ? ::: [300004407490]
四、参考资料
Spring Boot Configuration :: ShardingSphere
ShardingSphere应用专题--4.1.1版本--Sharding-JDBC读写分离+分库分表(七)_拉丝的裤衩的博客-CSDN博客
单库分表:ShardingSphere4.1.1之Sharding-JDBC与SpringBoot项目集成案例(行表达式和强制路由分片策略)_wang7241的博客-CSDN博客_shardingjdbc4.1.1基于Docker的Mysql主从复制搭建 - CoderFocus - 博客园