分库分表ShardingSphere＜二＞ _ 示例
·
目录
一、分库分表实例
1. 依赖jar包
注意：依赖druid，需要使用第三方连接池：com.alibaba.druid.pool.DruidDataSource
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-core-common</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.1</version>
<exclusions>
<exclusion>
<artifactId>mybatis-spring-boot-starter</artifactId>
<groupId>org.mybatis.spring.boot</groupId>
</exclusion>
</exclusions>
</dependency>2. 表结构
注意：a. 两个数据源（库）：nomswc_db_1、nomswc_db_2
b. nomswc_db_1库：wc_pendant_tab_detail_1、wc_pendant_tab_detail_2
wc_pendant_tab_extend_1、wc_pendant_tab_extend_2
nomswc_db_2库：wc_pendant_tab_detail_1、wc_pendant_tab_detail_2
wc_pendant_tab_extend_1、wc_pendant_tab_extend_2
// 创建wc_pendant_tab_detail_1
CREATE TABLE `wc_pendant_tab_detail_1` (
`tab_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键Id',
`mgdb_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`period` smallint(6) NULL DEFAULT 1 COMMENT '时间阶段: 1 直播 2 回看 3 点播 ',
`tab_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`is_show` smallint(6) NULL DEFAULT 1 COMMENT '是否显示: 0：否（不显示） 1：是（显示）',
`descripton` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
`name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'tab名称',
`url` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'tab页链接地址',
`del_falg` smallint(6) NULL DEFAULT NULL COMMENT '是否删除 0否(未删除) 1是（删除）',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
`app_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`terminal_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`tab_id`) USING BTREE,
INDEX `idx_mgdb_id_period`(`mgdb_id`, `period`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'Tab菜单' ROW_FORMAT = Dynamic;
// 创建wc_pendant_tab_detail_2
CREATE TABLE `wc_pendant_tab_detail_2` (
`tab_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键Id',
`mgdb_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`period` smallint(6) NULL DEFAULT 1 COMMENT '时间阶段: 1 直播 2 回看 3 点播 ',
`tab_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`is_show` smallint(6) NULL DEFAULT 1 COMMENT '是否显示: 0：否（不显示） 1：是（显示）',
`descripton` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
`name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'tab名称',
`url` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'tab页链接地址',
`del_falg` smallint(6) NULL DEFAULT NULL COMMENT '是否删除 0否(未删除) 1是（删除）',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
`update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
`app_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`terminal_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`tab_id`) USING BTREE,
INDEX `idx_mgdb_id_period`(`mgdb_id`, `period`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'Tab菜单' ROW_FORMAT = Dynamic;
// 创建wc_pendant_tab_extend_1
CREATE TABLE `wc_pendant_tab_extend_1` (
`id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`mgdb_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`keywords` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`create_time` datetime(0) NULL DEFAULT NULL,
`create_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`update_time` datetime(0) NULL DEFAULT NULL,
`update_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
// 创建wc_pendant_tab_extend_2
CREATE TABLE `wc_pendant_tab_extend_2` (
`id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`mgdb_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`keywords` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`create_time` datetime(0) NULL DEFAULT NULL,
`create_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`update_time` datetime(0) NULL DEFAULT NULL,
`update_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;3. 配置
spring:
# 分表分库
shardingsphere:
datasource:
# 定义数据源名称
names: ds1,ds2
# 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
sharding:
# 默认数据源，即未配置分表规则的表数据存储表
default-data-source-name: ds1
# 设置绑定关系（逻辑表名）
binding-tables: wc_pendant_tab_detail,wc_pendant_tab_extend
# 全局表
# broadcast-tables: wc_pendant_tab
# 分库分表情况
# ds1 ds2：wc_pendant_tab_detail_1 wc_pendant_tab_detail_2
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}
# 表配置
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}
# 打开sql输出日志
props:
sql:
show: true
# pageHelper
pagehelper:
# 数据库类型
helper-dialect: mysql
# 启用合理化时，如果pageNum<1会查询第一页，如果pageNum>pages会查询最后一页
# 默认不启用，如果pageNum<1或pageNum>pages会返回空数据
reasonable: false
# 支持通过 Mapper 接口参数来传递分页参数
support-methods-arguments: true
params: count=countSql4. Mapper层
a. WcPendantTabDetailDao.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.common.instance.demo.dao.WcPendantTabDetailDao">
<resultMap type="com.common.instance.demo.entity.WcPendantTabDetail" id="WcPendantTabDetailMap">
<result property="tabId" column="tab_id" jdbcType="VARCHAR"/>
<result property="mgdbId" column="mgdb_id" jdbcType="VARCHAR"/>
<result property="period" column="period" jdbcType="INTEGER"/>
<result property="tabType" column="tab_type" jdbcType="VARCHAR"/>
<result property="isShow" column="is_show" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="url" column="url" jdbcType="VARCHAR"/>
<result property="descripton" column="descripton" jdbcType="VARCHAR"/>
<result property="delFalg" column="del_falg" jdbcType="INTEGER"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="createBy" column="create_by" jdbcType="VARCHAR"/>
<result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
<result property="updateBy" column="update_by" jdbcType="VARCHAR"/>
<result property="appId" column="app_id" jdbcType="VARCHAR"/>
<result property="terminalId" column="terminal_id" jdbcType="VARCHAR"/>
</resultMap>
<!--查询指定行数据-->
<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
limit #{offset}, #{limit}
</select>
<select id="queryAllByShard" 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 where tab_id like '3000006%' order by tab_id desc
limit #{offset}, #{limit}
</select>
<select id="queryAllByShardAndPageHelper" 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 where tab_id like '3000006%' order by tab_id desc
</select>
<!--新增所有列-->
<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>
<insert id="insertBatch">
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
<foreach collection="entities" item="entity" separator=",">
(#{entity.tabId},#{entity.mgdbId}, #{entity.period}, #{entity.tabType}, #{entity.isShow}, #{entity.name}, #{entity.url},
#{entity.descripton}, #{entity.delFalg}, #{entity.createTime}, #{entity.createBy}, #{entity.updateTime},
#{entity.updateBy}, #{entity.appId}, #{entity.terminalId})
</foreach>
</insert>
</mapper>b. WcPendantTabExtendDao.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.common.instance.demo.dao.WcPendantTabExtendDao">
<resultMap type="com.common.instance.demo.entity.WcPendantTabExtend" id="WcPendantTabExtendMap">
<result property="id" column="id" jdbcType="VARCHAR"/>
<result property="mgdbId" column="mgdb_id" jdbcType="VARCHAR"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="keywords" column="keywords" jdbcType="VARCHAR"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="createBy" column="create_by" jdbcType="VARCHAR"/>
<result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
<result property="updateBy" column="update_by" jdbcType="VARCHAR"/>
</resultMap>
<select id="queryAllByShardAndPageHelper" resultMap="WcPendantTabExtendMap">
select
te.id,
td.mgdb_id,
td.name,
te.keywords,
te.create_time,
te.create_by
from wc_pendant_tab_detail td left join wc_pendant_tab_extend te on td.mgdb_id = te.mgdb_id
where te.mgdb_id like '300003%'
order by te.mgdb_id desc
</select>
<!--新增所有列-->
<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>
<insert id="insertBatch" 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
<foreach collection="entities" item="entity" separator=",">
(#{entity.mgdbId}, #{entity.name},#{entity.keywords},
#{entity.createTime}, #{entity.createBy}, #{entity.updateTime},#{entity.updateBy})
</foreach>
</insert>
</mapper>5. Dao层
a. WcPendantTabDetailDao
package com.common.instance.demo.dao;
import com.common.instance.demo.entity.WcPendantTabDetail;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* @author tcm
* @since 2021-01-14 15:02:07
*/
@Mapper
public interface WcPendantTabDetailDao {
/**
* 查询指定行数据
*
* @param offset 查询起始位置
* @param limit 查询条数
* @return 对象列表
*/
List<WcPendantTabDetail> queryAllByLimit(@Param("offset") int offset, @Param("limit") int limit);
List<WcPendantTabDetail> queryAllByShard(@Param("offset") int offset, @Param("limit") int limit);
List<WcPendantTabDetail> queryAllByShardAndPageHelper();
/**
* 新增数据
* @param wcPendantTab 实例对象
* @return 影响行数
*/
int insert(WcPendantTabDetail wcPendantTab);
/**
* 批量新增数据（MyBatis原生foreach方法）
* @param entities List<WcPendantTab> 实例对象列表
* @return 影响行数
*/
int insertBatch(@Param("entities") List<WcPendantTabDetail> entities);
}b. WcPendantTabExtendDao
package com.common.instance.demo.dao;
import com.common.instance.demo.entity.WcPendantTabExtend;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* @author tcm
* @since 2021-01-14 15:02:07
*/
@Mapper
public interface WcPendantTabExtendDao {
List<WcPendantTabExtend> queryAllByShardAndPageHelper();
/**
* 新增数据
* @param wcPendantTabExtend 实例对象
* @return 影响行数
*/
int insert(WcPendantTabExtend wcPendantTabExtend);
/**
* 批量新增数据（MyBatis原生foreach方法）
* @param entities List<WcPendantTabExtend> 实例对象列表
* @return 影响行数
*/
int insertBatch(@Param("entities") List<WcPendantTabExtend> entities);
}6. Service层
package com.common.instance.demo.service;
import com.common.instance.demo.entity.WcPendantTabDetail;
import com.common.instance.demo.entity.WcPendantTabExtend;
import com.github.pagehelper.PageInfo;
import java.util.List;
/**
* @description 分库分表
* @author TCM
* @version 1.0
* @date 2021/12/11 19:34
**/
public interface ShardService {
/**
* 获取分布式主键ID
* @return 主键ID
*/
String getRedisId();
/**
* 插入数据
* @return
*/
int insert();
/**
* 批量插入数据
* @return
*/
Long insertBatch();
/**
* 分库分表排序查询分页
* @param offset
* @param limit
* @return
*/
List<WcPendantTabDetail> queryAllByShard(int offset, int limit);
/**
* 分库分表排序查询分页
* @param page 页码
* @param pageSize 页面大小
* @return
*/
PageInfo<WcPendantTabDetail> queryAllByShardAndPageHelper(Integer page, Integer pageSize);
/**
* 分库分表JOIN排序查询分页
* @param page 页码
* @param pageSize 页面大小
* @return
*/
PageInfo<WcPendantTabExtend> queryAllByShardAndPageHelperAndJoin(Integer page, Integer pageSize);
}
package com.common.instance.demo.service.impl;
import com.common.instance.demo.dao.WcPendantTabDetailDao;
import com.common.instance.demo.dao.WcPendantTabExtendDao;
import com.common.instance.demo.entity.WcPendantTabDetail;
import com.common.instance.demo.entity.WcPendantTabExtend;
import com.common.instance.demo.service.ShardService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.google.common.collect.Lists;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.support.atomic.RedisAtomicLong;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.atomic.AtomicLong;
/**
* @description 分库分表
* @author TCM
* @version 1.0
* @date 2021/12/11 19:35
**/
@Service
public class ShardServiceImpl implements ShardService {
@Value("${redis.key:MG_KEY}")
private String MG_KEY;
@Resource
private WcPendantTabDetailDao wcPendantTabDetailDao;
@Resource
private WcPendantTabExtendDao wcPendantTabExtendDao;
@Resource
private RedisTemplate<String, String> redisTemplate;
@Override
public String getRedisId() {
// 获取redis主键值
RedisAtomicLong idCount = new RedisAtomicLong(MG_KEY, redisTemplate.getConnectionFactory());
Long redisId = idCount.incrementAndGet();
// 整型转格式字符串
return redisIdF(redisId);
}
// 整型转格式字符串，如：300000000001
private String redisIdF(Long redisId){
String redisIdStr = null;
if (Objects.nonNull(redisId)){
redisIdStr = String.format("3%011d", redisId);
}
return redisIdStr;
}
@Override
public int insert() {
// 获取分布式主键
String tabId = getRedisId();
// 组装对象
WcPendantTabDetail wcPendantTabDetail = getWcPendantTabDetail(tabId);
WcPendantTabExtend wcPendantTabExtend = getWcPendantTabExtend(wcPendantTabDetail);
// 保存DB
wcPendantTabDetailDao.insert(wcPendantTabDetail);
return wcPendantTabExtendDao.insert(wcPendantTabExtend);
}
// 组装对象
private WcPendantTabDetail getWcPendantTabDetail(String tabId){
WcPendantTabDetail tab = new WcPendantTabDetail();
Date date = new Date();
tab.setTabId(tabId);
tab.setMgdbId(tabId);
tab.setPeriod(0);
tab.setTabType("ENTERTAINMENT_R_DETAIL");
tab.setName("test" + tabId);
tab.setDescripton("Description" + tabId);
tab.setUrl("https://assets.alicdn.com/g/tm/login/3.0.2/img/logo.png");
tab.setIsShow(1);
tab.setAppId("203023");
tab.setCreateTime(date);
tab.setCreateBy("admin");
tab.setUpdateTime(date);
tab.setUpdateBy("admin");
tab.setDelFalg(0);
tab.setTerminalId("android");
return tab;
}
private WcPendantTabExtend getWcPendantTabExtend(WcPendantTabDetail wcPendantTabDetail){
WcPendantTabExtend tabExtend = new WcPendantTabExtend();
tabExtend.setMgdbId(wcPendantTabDetail.getMgdbId());
tabExtend.setName(wcPendantTabDetail.getName());
tabExtend.setKeywords("Keywords" + wcPendantTabDetail.getMgdbId());
tabExtend.setCreateTime(wcPendantTabDetail.getCreateTime());
tabExtend.setCreateBy(wcPendantTabDetail.getCreateBy());
tabExtend.setUpdateTime(wcPendantTabDetail.getUpdateTime());
tabExtend.setUpdateBy(wcPendantTabDetail.getUpdateBy());
return tabExtend;
}
@Override
public Long insertBatch() {
// 插入数据总量
AtomicLong totalNum = new AtomicLong(0);
for (int i = 0; i < 100000; i++) {
List<WcPendantTabDetail> tabDetails = Lists.newArrayList();
List<WcPendantTabExtend> tabExtends = Lists.newArrayList();
for (int j = 0; j < 100; j++) {
// 获取分布式主键
String tabId = getRedisId();
// 组装对象
WcPendantTabDetail tabDetail = getWcPendantTabDetail(tabId);
WcPendantTabExtend tabExtend = getWcPendantTabExtend(tabDetail);
tabDetails.add(tabDetail);
tabExtends.add(tabExtend);
}
// 批量插入数据量
wcPendantTabDetailDao.insertBatch(tabDetails);
int num = wcPendantTabExtendDao.insertBatch(tabExtends);
totalNum.addAndGet(num);
}
return totalNum.get();
}
@Override
public List<WcPendantTabDetail> queryAllByShard(int offset, int limit) {
return wcPendantTabDetailDao.queryAllByLimit(offset, limit);
}
@Override
public PageInfo<WcPendantTabDetail> queryAllByShardAndPageHelper(Integer page, Integer pageSize) {
// 1.设置分页
PageHelper.startPage(page, pageSize);
// 2.执行查询
List<WcPendantTabDetail> tabs = wcPendantTabDetailDao.queryAllByShardAndPageHelper();
// 3.获取详细分页信息
PageInfo<WcPendantTabDetail> pageInfo = new PageInfo<>(tabs);
return pageInfo;
}
@Override
public PageInfo<WcPendantTabExtend> queryAllByShardAndPageHelperAndJoin(Integer page, Integer pageSize) {
// 1.设置分页
PageHelper.startPage(page, pageSize);
// 2.执行查询
List<WcPendantTabExtend> tabs = wcPendantTabExtendDao.queryAllByShardAndPageHelper();
// 3.获取详细分页信息
PageInfo<WcPendantTabExtend> pageInfo = new PageInfo<>(tabs);
return pageInfo;
}
}
7. Controller层
package com.common.instance.demo.controller;
import com.common.instance.demo.core.Response;
import com.common.instance.demo.entity.WcPendantTabDetail;
import com.common.instance.demo.entity.WcPendantTabExtend;
import com.common.instance.demo.service.ShardService;
import com.github.pagehelper.PageInfo;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;
import java.util.Map;
/**
* @description 分库分表
* @author TCM
* @version 1.0
* @date 2021/12/11 19:33
**/
@RestController
@RequestMapping("/shard")
@Api(tags = "分库分表测试")
public class ShardController {
@Resource
private ShardService shardService;
@GetMapping("/getRedisId")
@ApiOperation("测试请求合并")
public Response<Map<String, String>> getRedisId(){
// 返回结果
Map<String, String> result = Maps.newHashMap();
try {
String redisId = shardService.getRedisId();
result.put("redisId", redisId);
} catch (Exception e) {
e.printStackTrace();
return Response.error();
}
return Response.success(result);
}
@GetMapping("/insert")
@ApiOperation("分库分表插入数据")
public Response insert(){
int result = 0;
try {
// 插入数据
result = shardService.insert();
} catch (Exception e) {
e.printStackTrace();
return Response.error();
}
return Response.success(result);
}
@GetMapping("/insertBatch")
@ApiOperation("分库分表批量插入数据")
public Response<Long> insertBatch(){
Long result = null;
try {
// 插入数据
result = shardService.insertBatch();
} catch (Exception e) {
e.printStackTrace();
return Response.error();
}
return Response.success(result);
}
@GetMapping("/queryAll")
@ApiOperation("排序分页查询数据")
public Response<List<WcPendantTabDetail>> queryAllByShard(int offset, int limit){
List<WcPendantTabDetail> result = Lists.newArrayList();
try {
// 分库分表排序查询分页
List<WcPendantTabDetail> list = shardService.queryAllByShard(offset, limit);
result.addAll(list);
} catch (Exception e) {
e.printStackTrace();
return Response.error();
}
return Response.success(result);
}
@GetMapping("/queryAllByShardAndPageHelper")
@ApiOperation("PageHelper排序分页查询数据")
public Response<PageInfo<WcPendantTabDetail>> queryAllByShardAndPageHelper(Integer page, Integer pageSize){
PageInfo<WcPendantTabDetail> pageInfo = null;
try {
// PageHelper排序分页查询数据
pageInfo = shardService.queryAllByShardAndPageHelper(page, pageSize);
} catch (Exception e) {
e.printStackTrace();
return Response.error();
}
return Response.success(pageInfo);
}
@GetMapping("/queryAllByShardAndPageHelperAndJoin")
@ApiOperation("PageHelper和JOIN排序分页查询数据")
public Response<PageInfo<WcPendantTabExtend>> queryAllByShardAndPageHelperAndJoin(Integer page, Integer pageSize){
PageInfo<WcPendantTabExtend> pageInfo = null;
try {
// PageHelper排序分页查询数据
pageInfo = shardService.queryAllByShardAndPageHelperAndJoin(page, pageSize);
} catch (Exception e) {
e.printStackTrace();
return Response.error();
}
return Response.success(pageInfo);
}
}
二、分库分表分页/排序/JOIN
1. 单分库分表查询 — 分页/排序
如下所示是查询语句，查询表是逻辑表，不是实际的物理表。
SELECT
tab_id,mgdb_id,period,tab_type,is_shoname,url,descripton,del_falg,
create_time,create_by,update_time,update_by,app_id,terminal_id
FROM
wc_pendant_tab_detail
WHERE
tab_id LIKE '3000006%'
ORDER BY
tab_id DESC
如下所示是查询日志。
2021-12-18 16:16:16.671 INFO [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] ShardingSphere-SQL : Logic SQL: SELECT count(0) FROM wc_pendant_tab_detail WHERE tab_id LIKE '3000006%'
2021-12-18 16:16:16.671 INFO [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@5618f4ae, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@64d533a1), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@64d533a1, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=14, distinctRow=false, projections=[AggregationProjection(type=COUNT, innerExpression=(0), alias=Optional.empty, derivedAggregationProjections=[], index=-1)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@6a12d14b, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@6e2d58d1, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@2b2fdb70, containsSubquery=false)
2021-12-18 16:16:16.671 INFO [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT count(0) FROM wc_pendant_tab_detail_1 WHERE tab_id LIKE '3000006%'
2021-12-18 16:16:16.671 INFO [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT count(0) FROM wc_pendant_tab_detail_2 WHERE tab_id LIKE '3000006%'
2021-12-18 16:16:16.671 INFO [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] ShardingSphere-SQL : Actual SQL: ds2 ::: SELECT count(0) FROM wc_pendant_tab_detail_1 WHERE tab_id LIKE '3000006%'
2021-12-18 16:16:16.671 INFO [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] ShardingSphere-SQL : Actual SQL: ds2 ::: SELECT count(0) FROM wc_pendant_tab_detail_2 WHERE tab_id LIKE '3000006%'
2021-12-18 16:16:16.706 WARN [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.1.55:3307/nomswc_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC, version : 1.2.8, lastPacketReceivedIdleMillis : 978675
2021-12-18 16:16:16.709 WARN [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.1.55:3307/nomswc_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC, version : 1.2.8, lastPacketReceivedIdleMillis : 1034720
2021-12-18 16:16:16.730 WARN [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.1.55:3307/nomswc_db_2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC, version : 1.2.8, lastPacketReceivedIdleMillis : 978704
2021-12-18 16:16:16.732 WARN [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.1.55:3307/nomswc_db_2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC, version : 1.2.8, lastPacketReceivedIdleMillis : 1034626
2021-12-18 16:16:16.798 INFO [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] 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 like '3000006%' order by tab_id desc
LIMIT ?
2021-12-18 16:16:16.798 INFO [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@44f0db37, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1a65f8d6), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@1a65f8d6, 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@e3f7d3, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@2652581, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@408e6f99, containsSubquery=false)
2021-12-18 16:16:16.798 INFO [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] 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 like '3000006%' order by tab_id desc
LIMIT ? ::: [2]
2021-12-18 16:16:16.798 INFO [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] 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 like '3000006%' order by tab_id desc
LIMIT ? ::: [2]
2021-12-18 16:16:16.798 INFO [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] 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 like '3000006%' order by tab_id desc
LIMIT ? ::: [2]
2021-12-18 16:16:16.798 INFO [,e9ca9c3c9a5e174d,e9ca9c3c9a5e174d,true] 18628 --- [io-9013-exec-13] 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 like '3000006%' order by tab_id desc
LIMIT ? ::: [2]逻辑SQL语句，是mapper中的查询语句，即：日志中的"Logic SQL"。根据分库分表路由情况，再分别查询真实表进行归并，即：日志中的"Actual SQL"。
对于单个表分页或深度分页、排序查询的时候，查询字段落到分片键上，同时索引有效，否则查询会很慢。
2. 两分库分表查询 — 分页/排序/JOIN
如下所示是查询语句，两个查询表都是逻辑表，不是实际的物理表。注意这两张表为绑定关系。
SELECT
te.id,td.mgdb_id,td.name,te.keywords,te.create_time,te.create_by
FROM
wc_pendant_tab_detail td
LEFT JOIN wc_pendant_tab_extend te ON td.mgdb_id = te.mgdb_id
WHERE
te.mgdb_id LIKE '300003%'
ORDER BY
te.mgdb_id DESC
如下所示是查询日志。
2021-12-18 19:58:37.037 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Logic SQL: SELECT count(0) FROM wc_pendant_tab_detail td LEFT JOIN wc_pendant_tab_extend te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '300003%'
2021-12-18 19:58:37.037 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@229e648, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5ddf5571), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5ddf5571, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=14, distinctRow=false, projections=[AggregationProjection(type=COUNT, innerExpression=(0), alias=Optional.empty, derivedAggregationProjections=[], index=-1)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@124e3b64, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@2e0b74fc, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@635dccd3, containsSubquery=false)
2021-12-18 19:58:37.038 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds2 ::: SELECT count(0) FROM wc_pendant_tab_detail_2 td LEFT JOIN wc_pendant_tab_extend_1 te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '300003%'
2021-12-18 19:58:37.038 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds2 ::: SELECT count(0) FROM wc_pendant_tab_detail_2 td LEFT JOIN wc_pendant_tab_extend_2 te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '300003%'
2021-12-18 19:58:37.038 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds2 ::: SELECT count(0) FROM wc_pendant_tab_detail_1 td LEFT JOIN wc_pendant_tab_extend_1 te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '300003%'
2021-12-18 19:58:37.038 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds2 ::: SELECT count(0) FROM wc_pendant_tab_detail_1 td LEFT JOIN wc_pendant_tab_extend_2 te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '300003%'
2021-12-18 19:58:37.038 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT count(0) FROM wc_pendant_tab_detail_2 td LEFT JOIN wc_pendant_tab_extend_1 te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '300003%'
2021-12-18 19:58:37.038 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT count(0) FROM wc_pendant_tab_detail_2 td LEFT JOIN wc_pendant_tab_extend_2 te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '300003%'
2021-12-18 19:58:37.038 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT count(0) FROM wc_pendant_tab_detail_1 td LEFT JOIN wc_pendant_tab_extend_1 te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '300003%'
2021-12-18 19:58:37.038 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT count(0) FROM wc_pendant_tab_detail_1 td LEFT JOIN wc_pendant_tab_extend_2 te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '300003%'
2021-12-18 19:58:37.984 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Logic SQL: select
te.id,td.mgdb_id,td.name,te.keywords,te.create_time,te.create_by
from wc_pendant_tab_detail td left join wc_pendant_tab_extend te on td.mgdb_id = te.mgdb_id
where te.mgdb_id like '300003%'
order by te.mgdb_id desc
LIMIT ?
2021-12-18 19:58:37.984 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@72e33a3d, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7330c93f), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@7330c93f, projectionsContext=ProjectionsContext(startIndex=17, stopIndex=80, distinctRow=false, projections=[ColumnProjection(owner=te, name=id, alias=Optional.empty), ColumnProjection(owner=td, name=mgdb_id, alias=Optional.empty), ColumnProjection(owner=td, name=name, alias=Optional.empty), ColumnProjection(owner=te, name=keywords, alias=Optional.empty), ColumnProjection(owner=te, name=create_time, alias=Optional.empty), ColumnProjection(owner=te, name=create_by, alias=Optional.empty), DerivedProjection(expression=te.mgdb_id, alias=Optional[ORDER_BY_DERIVED_0])]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@4625bf61, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@5fe9b797, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@34304c85, containsSubquery=false)
2021-12-18 19:58:37.984 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds2 ::: select
te.id,td.mgdb_id,td.name,te.keywords,te.create_time,te.create_by
, te.mgdb_id AS ORDER_BY_DERIVED_0 from wc_pendant_tab_detail_2 td left join wc_pendant_tab_extend_1 te on td.mgdb_id = te.mgdb_id
where te.mgdb_id like '300003%'
order by te.mgdb_id desc
LIMIT ? ::: [2]
2021-12-18 19:58:37.984 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds2 ::: select
te.id,td.mgdb_id,td.name,te.keywords,te.create_time,te.create_by
, te.mgdb_id AS ORDER_BY_DERIVED_0 from wc_pendant_tab_detail_2 td left join wc_pendant_tab_extend_2 te on td.mgdb_id = te.mgdb_id
where te.mgdb_id like '300003%'
order by te.mgdb_id desc
LIMIT ? ::: [2]
2021-12-18 19:58:37.984 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds2 ::: select
te.id,td.mgdb_id,td.name,te.keywords,te.create_time,te.create_by
, te.mgdb_id AS ORDER_BY_DERIVED_0 from wc_pendant_tab_detail_1 td left join wc_pendant_tab_extend_1 te on td.mgdb_id = te.mgdb_id
where te.mgdb_id like '300003%'
order by te.mgdb_id desc
LIMIT ? ::: [2]
2021-12-18 19:58:37.984 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds2 ::: select
te.id,td.mgdb_id,td.name,te.keywords,te.create_time,te.create_by
, te.mgdb_id AS ORDER_BY_DERIVED_0 from wc_pendant_tab_detail_1 td left join wc_pendant_tab_extend_2 te on td.mgdb_id = te.mgdb_id
where te.mgdb_id like '300003%'
order by te.mgdb_id desc
LIMIT ? ::: [2]
2021-12-18 19:58:37.984 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds1 ::: select
te.id,td.mgdb_id,td.name,te.keywords,te.create_time,te.create_by
, te.mgdb_id AS ORDER_BY_DERIVED_0 from wc_pendant_tab_detail_2 td left join wc_pendant_tab_extend_1 te on td.mgdb_id = te.mgdb_id
where te.mgdb_id like '300003%'
order by te.mgdb_id desc
LIMIT ? ::: [2]
2021-12-18 19:58:37.984 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds1 ::: select
te.id,td.mgdb_id,td.name,te.keywords,te.create_time,te.create_by
, te.mgdb_id AS ORDER_BY_DERIVED_0 from wc_pendant_tab_detail_2 td left join wc_pendant_tab_extend_2 te on td.mgdb_id = te.mgdb_id
where te.mgdb_id like '300003%'
order by te.mgdb_id desc
LIMIT ? ::: [2]
2021-12-18 19:58:37.984 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds1 ::: select
te.id,td.mgdb_id,td.name,te.keywords,te.create_time,te.create_by
, te.mgdb_id AS ORDER_BY_DERIVED_0 from wc_pendant_tab_detail_1 td left join wc_pendant_tab_extend_1 te on td.mgdb_id = te.mgdb_id
where te.mgdb_id like '300003%'
order by te.mgdb_id desc
LIMIT ? ::: [2]
2021-12-18 19:58:37.984 INFO [,9d217fa7dfbd1c3c,9d217fa7dfbd1c3c,true] 17016 --- [nio-9013-exec-1] ShardingSphere-SQL : Actual SQL: ds1 ::: select
te.id,td.mgdb_id,td.name,te.keywords,te.create_time,te.create_by
, te.mgdb_id AS ORDER_BY_DERIVED_0 from wc_pendant_tab_detail_1 td left join wc_pendant_tab_extend_2 te on td.mgdb_id = te.mgdb_id
where te.mgdb_id like '300003%'
order by te.mgdb_id desc
LIMIT ? ::: [2]两个分库分表JOIN查询，关联起来分别查询了8次（组合数8 = 2*4），即：出现8次"Actual SQL"。
虽然有8次查询，但是查询结果并没有出现笛卡尔积。原因是两表配置了绑定关系，即：spring.shardingsphere.sharding.binding-tables: wc_pendant_tab_detail,wc_pendant_tab_extend。
绑定关系的条件：多表之间有共同的字段（多为主子表关系），且分库、分表的分片键和规则都应相同，这样插入数据的时候，关联数据不会进入两个数据源中，只能插入到同一个数据源（归并4次查询即可），从而避免出现笛卡尔积。
注意若是分库分表和单表关联查询时，会报错：另一个数据源不存在该表，如下所示。可以看出，此时不能关联查询。要么单表配置为全局表（每个数据源都有该表，且数据相同），或者分两次查询，查询结果作相应处理。
2021-12-18 20:53:21.389 INFO [,6e240abc68e18c43,6e240abc68e18c43,true] 17476 --- [nio-9013-exec-6] ShardingSphere-SQL : Logic SQL: SELECT count(0) FROM wc_pendant_tab td LEFT JOIN wc_pendant_tab_extend te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '30000374425%'
2021-12-18 20:53:21.389 INFO [,6e240abc68e18c43,6e240abc68e18c43,true] 17476 --- [nio-9013-exec-6] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@6e2aeb67, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@76ca6cb), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@76ca6cb, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=14, distinctRow=false, projections=[AggregationProjection(type=COUNT, innerExpression=(0), alias=Optional.empty, derivedAggregationProjections=[], index=-1)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@9ab4856, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@d94cdbb, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@4896426d, containsSubquery=false)
2021-12-18 20:53:21.389 INFO [,6e240abc68e18c43,6e240abc68e18c43,true] 17476 --- [nio-9013-exec-6] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT count(0) FROM wc_pendant_tab td LEFT JOIN wc_pendant_tab_extend_1 te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '30000374425%'
2021-12-18 20:53:21.389 INFO [,6e240abc68e18c43,6e240abc68e18c43,true] 17476 --- [nio-9013-exec-6] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT count(0) FROM wc_pendant_tab td LEFT JOIN wc_pendant_tab_extend_2 te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '30000374425%'
2021-12-18 20:53:21.389 INFO [,6e240abc68e18c43,6e240abc68e18c43,true] 17476 --- [nio-9013-exec-6] ShardingSphere-SQL : Actual SQL: ds2 ::: SELECT count(0) FROM wc_pendant_tab td LEFT JOIN wc_pendant_tab_extend_1 te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '30000374425%'
2021-12-18 20:53:21.389 INFO [,6e240abc68e18c43,6e240abc68e18c43,true] 17476 --- [nio-9013-exec-6] ShardingSphere-SQL : Actual SQL: ds2 ::: SELECT count(0) FROM wc_pendant_tab td LEFT JOIN wc_pendant_tab_extend_2 te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '30000374425%'
2021-12-18 20:53:21.391 WARN [,6e240abc68e18c43,6e240abc68e18c43,true] 17476 --- [nio-9013-exec-6] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.1.55:3307/nomswc_db_1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC, version : 1.2.8, lastPacketReceivedIdleMillis : 72325
2021-12-18 20:53:21.413 WARN [,6e240abc68e18c43,6e240abc68e18c43,true] 17476 --- [nio-9013-exec-6] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://192.168.1.55:3307/nomswc_db_2?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC, version : 1.2.8, lastPacketReceivedIdleMillis : 80333
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: java.sql.SQLSyntaxErrorException: Table 'nomswc_db_2.wc_pendant_tab' doesn't exist
### The error may exist in file [E:\Idea Project\instance-demo\target\classes\mapper\WcPendantTabExtendDao.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT count(0) FROM wc_pendant_tab td LEFT JOIN wc_pendant_tab_extend te ON td.mgdb_id = te.mgdb_id WHERE te.mgdb_id LIKE '30000374425%'
### Cause: java.sql.SQLSyntaxErrorException: Table 'nomswc_db_2.wc_pendant_tab' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'nomswc_db_2.wc_pendant_tab' doesn't exist
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
at com.sun.proxy.$Proxy177.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
at com.sun.proxy.$Proxy194.queryAllByShardAndPageHelper(Unknown Source)
at com.common.instance.demo.service.impl.ShardServiceImpl.queryAllByShardAndPageHelperAndJoin(ShardServiceImpl.java:165)
at com.common.instance.demo.controller.ShardController.queryAllByShardAndPageHelperAndJoin(ShardController.java:117)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:626)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at brave.servlet.TracingFilter.doFilter(TracingFilter.java:68)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at brave.servlet.TracingFilter.doFilter(TracingFilter.java:87)
at org.springframework.cloud.sleuth.instrument.web.LazyTracingFilter.doFilter(TraceWebServletAutoConfiguration.java:139)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: Table 'nomswc_db_2.wc_pendant_tab' doesn't exist
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:62)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:58)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:82)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58)
at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.lambda$asyncExecute$0(ExecutorEngine.java:110)
at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)
at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:69)
at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
... 1 more三、参考资料
分库分表ShardingSphere＜一＞_爱我所爱0505-CSDN博客
sharding-jdbc分库分表规则(2)-多表查询_yanyan19880509的专栏-CSDN博客_shardingjdbc分表查询
更多推荐
0
0- 0
已为社区贡献3条内容
所有评论(0)