sharding-jdbc实现按年分库按月分表_sharding以年分库 以月分表-程序员宅基地

技术标签： Sharding-JDBC  

原文地址

sharding-jdbc官方文档:https://shardingsphere.apache.org/document/current/cn/overview/
本文采用当当的shardingjdbc实现按年分库，按月分表

最终数据库结果如下
image.png

例如有如下sql语句

select * from ips where flowtime = '20181202';

我们规定flowtime是我们的分片键,通过值20181202确定年份为2018，月份为12，所以需要定位到库sharding_2018中的表ips_12查询，
所以实际发出的查询语句是

select * from `sharding_2018`.ips_12 where flowtime = '20181202';
具体实现
maven导出具体sharding需要的包
<dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>3.0.0.M3</version>
</dependency>
<dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-orchestration-spring-namespace</artifactId>
            <version>3.0.0.M3</version>
</dependency>
配置数据库连接

由于我们需要一年一库，所以我们取2017~2020年来建库，这步骤需要手工建立。查了资料好像shardingjdbc不支持自动建库，例如我们如果按照上面一年一库的规则，我们就需要自己手动建立对应的库。一年一库感觉还好，正常来说一个产品最多用10年已经很久了，所以手动预先建立好库没什么太大的工作量。所以我们先在数据库建立好表后，然后配置对应的数据源

applicationContext-database.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-4.1.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
    <bean id="dataSource_default" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/sharding_default?useUnicode=true&amp;characterEncoding=utf-8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        <!-- 初始化连接大小 -->
        <property name="initialSize" value="0"></property>
        <!-- 连接池最大数量 -->
        <property name="maxActive" value="200"></property>
        <!-- 连接池最大空闲 -->
        <property name="maxIdle" value="20"></property>
        <!-- 连接池最小空闲 -->
        <property name="minIdle" value="1"></property>
        <!-- 获取连接最大等待时间 -->
        <property name="maxWait" value="60000"></property>
    </bean>
    <bean id="dataSource_2017" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/sharding_2017?useUnicode=true&amp;characterEncoding=utf-8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        <!-- 初始化连接大小 -->
        <property name="initialSize" value="0"></property>
        <!-- 连接池最大数量 -->
        <property name="maxActive" value="200"></property>
        <!-- 连接池最大空闲 -->
        <property name="maxIdle" value="20"></property>
        <!-- 连接池最小空闲 -->
        <property name="minIdle" value="1"></property>
        <!-- 获取连接最大等待时间 -->
        <property name="maxWait" value="60000"></property>
    </bean>
    <bean id="dataSource_2018" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/sharding_2018?useUnicode=true&amp;characterEncoding=utf-8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        <!-- 初始化连接大小 -->
        <property name="initialSize" value="0"></property>
        <!-- 连接池最大数量 -->
        <property name="maxActive" value="200"></property>
        <!-- 连接池最大空闲 -->
        <property name="maxIdle" value="20"></property>
        <!-- 连接池最小空闲 -->
        <property name="minIdle" value="1"></property>
        <!-- 获取连接最大等待时间 -->
        <property name="maxWait" value="60000"></property>
    </bean>
    <bean id="dataSource_2019" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/sharding_2019?useUnicode=true&amp;characterEncoding=utf-8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        <!-- 初始化连接大小 -->
        <property name="initialSize" value="0"></property>
        <!-- 连接池最大数量 -->
        <property name="maxActive" value="200"></property>
        <!-- 连接池最大空闲 -->
        <property name="maxIdle" value="20"></property>
        <!-- 连接池最小空闲 -->
        <property name="minIdle" value="1"></property>
        <!-- 获取连接最大等待时间 -->
        <property name="maxWait" value="60000"></property>
    </bean>
    <bean id="dataSource_2020" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/sharding_2020?useUnicode=true&amp;characterEncoding=utf-8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        <!-- 初始化连接大小 -->
        <property name="initialSize" value="0"></property>
        <!-- 连接池最大数量 -->
        <property name="maxActive" value="200"></property>
        <!-- 连接池最大空闲 -->
        <property name="maxIdle" value="20"></property>
        <!-- 连接池最小空闲 -->
        <property name="minIdle" value="1"></property>
        <!-- 获取连接最大等待时间 -->
        <property name="maxWait" value="60000"></property>
    </bean>
</beans>

这里解释下dataSource_default这个数据库用来干嘛的。由于在正常项目中并不是所有的数据都需要进行分库分表，例如用户表和用户记录表，一般用户记录表一般是千万级的，需要分库分表，但是用户表不需要。我们这里配置一个默认的数据源，对于不分库分表的数据就存放在这个默认的数据库中

applicationContext-sharding.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:sharding="http://shardingsphere.io/schema/shardingsphere/sharding"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans.xsd
                        http://www.springframework.org/schema/tx
                        http://www.springframework.org/schema/tx/spring-tx.xsd
                        http://www.springframework.org/schema/context
                        http://www.springframework.org/schema/context/spring-context.xsd
                        http://shardingsphere.io/schema/shardingsphere/sharding
                        http://shardingsphere.io/schema/shardingsphere/sharding/sharding.xsd">

    <!--规则定义:一年一库,一月一表-->
    <!--分片算法(根据flowtime一年一库，一月一表)-->
    <bean id="databaseShardingAlgorithm" class="com.shardingAlgorithm.PreciseModuloDatabaseShardingAlgorithm"></bean>
    <bean id="tableShardingAlgorithm" class="com.shardingAlgorithm.PreciseModuloTableShardingAlgorithm"></bean>


    <bean id="complexModuloDatabaseShardingAlgorithm" class="com.shardingAlgorithm.ComplexModuloDatabaseShardingAlgorithm"></bean>
    <bean id="complexModuloTableShardingAlgorithm" class="com.shardingAlgorithm.ComplexModuloTableShardingAlgorithm"></bean>


    <!--数据库分片-标准-按照time字段分片【坑:不能使用mysql关键词作为分片，不然会不能路由,time就是一个 所以换成了flowtime】-->
    <sharding:standard-strategy id="databaseShardingStrategy" sharding-column="flowtime" precise-algorithm-ref="databaseShardingAlgorithm"/>
    <!--表分片-标准-按照time字段分片-->
    <sharding:standard-strategy id="tableShardingStrategy" sharding-column="flowtime" precise-algorithm-ref="tableShardingAlgorithm"/>

    <!--复合分片-->
    <sharding:complex-strategy id="complexdatabaseShardingStrategy" sharding-columns="flowtime,dataType" algorithm-ref="complexModuloDatabaseShardingAlgorithm"/>
    <sharding:complex-strategy id="complextableShardingStrategy" sharding-columns="flowtime,dataType" algorithm-ref="complexModuloTableShardingAlgorithm"/>


    <sharding:none-strategy id="noShardingStrategy"/>

    <sharding:data-source id="shardingDataSource">
        <sharding:sharding-rule data-source-names="dataSource_2017,dataSource_2018,dataSource_2019,dataSource_2020,dataSource_default" default-data-source-name="dataSource_default">
            <sharding:table-rules>
                <!--flow表的分片规则-->
                <sharding:table-rule logic-table="flow" actual-data-nodes="dataSource_${2017..2020}.flow_0${1..9},dataSource_${2017..2020}.flow_1${0..2}" database-strategy-ref="databaseShardingStrategy" table-strategy-ref="tableShardingStrategy" />
                <sharding:table-rule logic-table="ips" actual-data-nodes="dataSource_${2017..2020}.ips_0${1..9},dataSource_${2017..2020}.ips_1${0..2}" database-strategy-ref="databaseShardingStrategy" table-strategy-ref="tableShardingStrategy" />
                <sharding:table-rule logic-table="acca" actual-data-nodes="dataSource_${2017..2020}.acca_0${1..9},dataSource_${2017..2020}.acca_1${0..2}" database-strategy-ref="complexdatabaseShardingStrategy" table-strategy-ref="complextableShardingStrategy" />
                <!--
                    不路由的数据可以不配置，因为上面指定了默认的dataSource_default
                    <sharding:table-rule logic-table="websocket" actual-data-nodes="dataSource_default.websocket"/>
                -->
            </sharding:table-rules>
        </sharding:sharding-rule>
        <sharding:props>
            <prop key="sql.show">true</prop>
        </sharding:props>
    </sharding:data-source>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="shardingDataSource"></property>
    </bean>

    <!--普通的数据库连接，不走sharding-->
    <bean id="jdbcTemplate_default" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource_default"></property>
    </bean>
</beans>

解释下如上配置的意思:

    <bean id="databaseShardingAlgorithm" class="com.shardingAlgorithm.PreciseModuloDatabaseShardingAlgorithm"></bean>
    <bean id="tableShardingAlgorithm" class="com.shardingAlgorithm.PreciseModuloTableShardingAlgorithm"></bean>

表示对应的库跟表的分片算法，shardingjdbc支持多种的分片算法，具体可以参考# sharding-jdbc—分片策略里的介绍。举个例子，我们需要进行分库分表，肯定需要定义一些规则，例如select * from ips where flowtime = '20181212'查询语句,我是通过flowtime分片，且SQL语句是in或者=的查询，我就需要实现shardingjdbc提供的特定的分片算法接口，在里面通过计算出20181212具体是哪年哪月，shardingjdbc才能帮我们定位到对应的数据库

<sharding:sharding-rule data-source-names="dataSource_2017,dataSource_2018,dataSource_2019,dataSource_2020,dataSource_default" default-data-source-name="dataSource_default">
......
</sharding:sharding-rule>

data-source-names 主要列举所有的数据源。default-data-source-name为默认的数据源，通过这两项配置告诉sharding我的数据源列表和默认的数据源

<sharding:table-rule logic-table="flow" actual-data-nodes="dataSource_${2017..2020}.flow_0${1..9},dataSource_${2017..2020}.flow_1${0..2}" database-strategy-ref="databaseShardingStrategy" table-strategy-ref="tableShardingStrategy" />
<sharding:table-rule logic-table="ips" actual-data-nodes="dataSource_${2017..2020}.ips_0${1..9},dataSource_${2017..2020}.ips_1${0..2}" database-strategy-ref="databaseShardingStrategy" table-strategy-ref="tableShardingStrategy" />
<sharding:table-rule logic-table="acca" actual-data-nodes="dataSource_${2017..2020}.acca_0${1..9},dataSource_${2017..2020}.acca_1${0..2}" database-strategy-ref="complexdatabaseShardingStrategy" table-strategy-ref="complextableShardingStrategy" />

这里我对三个表都配置了分片规则，其实是一样的，我们取其中一个来看。

actual-data-nodes="dataSource_${2017..2020}.flow_0${1..9},dataSource_${2017..2020}.flow_1${0..2}"

主要配置实际的库表，格式为 数据库.表 。支持使用inline表达式。上面的配置shardingjdbc将为解析成dataSource_2017.flow_01 ~ dataSource_2020.flow_12。具体参考行表达式
logic-table 表示实际表
database-strategy-ref 表示对应的库分片算法
table-strategy-ref 表示对应的表分片算法

分片算法
库分片算法 PreciseModuloDatabaseShardingAlgorithm
public class PreciseModuloDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        //对于库的分片collection存放的是所有的库的列表，这里代表dataSource_2017~dataSource_2020
        //配置的分片的sharding-column对应的值
        String timeValue = preciseShardingValue.getValue();
        //分库时配置的sharding-column
        String time = preciseShardingValue.getColumnName();
        //需要分库的逻辑表
        String table = preciseShardingValue.getLogicTableName();
        if(StringUtils.isBlank(timeValue)){
            throw new UnsupportedOperationException("preciseShardingValue is null");
        }
        //按年路由
        for (String each : collection) {
            String value = StringUtils.substring(timeValue,0,4); //获取到年份
            if(each.endsWith(value)){
               // //这里返回回去的就是最终需要查询的库名
                return each;
            }
        }
        throw new UnsupportedOperationException();
    }
表分片算法 PreciseModuloTableShardingAlgorithm
/**
 * @author xuzhiyong
 * @createDate 2019-01-28-22:30
 * 按表
 */
public class PreciseModuloTableShardingAlgorithm implements PreciseShardingAlgorithm<String> {
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        //对于库的分片collection存放的是所有的库的列表，这里代表flow_01~flow_12
        //配置的分片的sharding-column对应的值
        String timeValue = preciseShardingValue.getValue();
        //分库时配置的sharding-column
        String time = preciseShardingValue.getColumnName();
        //需要分库的逻辑表
        String table = preciseShardingValue.getLogicTableName();
        if(StringUtils.isBlank(timeValue)){
            throw new UnsupportedOperationException("preciseShardingValue is null");
        }
        //按月路由
        for (String each : collection) {
            String value = StringUtils.substring(timeValue,4,6); //获取到月份
            if(each.endsWith(value)){
                //这里返回回去的就是最终需要查询的表名
                return each;
            }
        }
        return null;
    }
}
测试
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:applicationContext.xml", "classpath:applicationContext-database.xml", "classpath:applicationContext-sharding.xml"})
public class ShardingTest {
    @Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testCreateTable() {
        jdbcTemplate.update("CREATE TABLE IF NOT EXISTS ips (flowtime VARCHAR(50) NOT NULL, value INT NOT NULL)");
  }
}

查看控制台，已经帮我们创建了对应的表


image.png

测试插入数据

@Test
    public void testInsertOne(){
        //测试一条记录多条插入
        jdbcTemplate.update("INSERT IGNORE INTO flow(flowtime,value) VALUES ('20190525',1),('20190526',2),('20190527',2)");
    }

根据对应的规则插入到不同的表


image.png 
 @Test
    public void query(){
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from flow where flowtime = '20170818'");
    }
image.png
进行分库分表的思考

待续
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u013738122/article/details/105427429

智能推荐

oracle 12c 集群安装后的检查_12c查看crs状态-程序员宅基地

文章浏览阅读1.6k次。安装配置gi、安装数据库软件、dbca建库见下：http://blog.csdn.net/kadwf123/article/details/784299611、检查集群节点及状态：[root@rac2 ~]# olsnodes -srac1 Activerac2 Activerac3 Activerac4 Active[root@rac2 ~]_12c查看crs状态

解决jupyter notebook无法找到虚拟环境的问题_jupyter没有pytorch环境-程序员宅基地

文章浏览阅读1.3w次，点赞45次，收藏99次。我个人用的是anaconda3的一个python集成环境，自带jupyter notebook，但在我打开jupyter notebook界面后，却找不到对应的虚拟环境，原来是jupyter notebook只是通用于下载anaconda时自带的环境，其他环境要想使用必须手动下载一些库：1.首先进入到自己创建的虚拟环境(pytorch是虚拟环境的名字)activate pytorch2.在该环境下下载这个库conda install ipykernelconda install nb__jupyter没有pytorch环境

国内安装scoop的保姆教程_scoop-cn-程序员宅基地

文章浏览阅读5.2k次，点赞19次，收藏28次。选择scoop纯属意外，也是无奈，因为电脑用户被锁了管理员权限，所有exe安装程序都无法安装，只可以用绿色软件，最后被我发现scoop，省去了到处下载XXX绿色版的烦恼，当然scoop里需要管理员权限的软件也跟我无缘了（譬如everything）。推荐添加dorado这个bucket镜像，里面很多中文软件，但是部分国外的软件下载地址在github，可能无法下载。以上两个是官方bucket的国内镜像，所有软件建议优先从这里下载。上面可以看到很多bucket以及软件数。如果官网登陆不了可以试一下以下方式。_scoop-cn

Element ui colorpicker在Vue中的使用_vue el-color-picker-程序员宅基地

文章浏览阅读4.5k次，点赞2次，收藏3次。首先要有一个color-picker组件 <el-color-picker v-model="headcolor"></el-color-picker>在data里面data() { return {headcolor: ’ #278add ’ //这里可以选择一个默认的颜色} }然后在你想要改变颜色的地方用v-bind绑定就好了，例如：这里的:sty..._vue el-color-picker

迅为iTOP-4412精英版之烧写内核移植后的镜像_exynos 4412 刷机-程序员宅基地

文章浏览阅读640次。基于芯片日益增长的问题，所以内核开发者们引入了新的方法，就是在内核中只保留函数，而数据则不包含，由用户（应用程序员）自己把数据按照规定的格式编写，并放在约定的地方，为了不占用过多的内存，还要求数据以根精简的方式编写。boot启动时，传参给内核，告诉内核设备树文件和kernel的位置，内核启动时根据地址去找到设备树文件，再利用专用的编译器去反编译dtb文件，将dtb还原成数据结构，以供驱动的函数去调用。firmware是三星的一个固件的设备信息，因为找不到固件，所以内核启动不成功。_exynos 4412 刷机

Linux系统配置jdk_linux配置jdk-程序员宅基地

文章浏览阅读2w次，点赞24次，收藏42次。Linux系统配置jdkLinux学习教程，Linux入门教程（超详细）_linux配置jdk

随便推点

matlab(4)：特殊符号的输入_matlab微米怎么输入-程序员宅基地

文章浏览阅读3.3k次，点赞5次，收藏19次。xlabel('\delta');ylabel('AUC');具体符号的对照表参照下图：_matlab微米怎么输入

C语言程序设计-文件(打开与关闭、顺序、二进制读写)-程序员宅基地

文章浏览阅读119次。顺序读写指的是按照文件中数据的顺序进行读取或写入。对于文本文件，可以使用fgets、fputs、fscanf、fprintf等函数进行顺序读写。在C语言中，对文件的操作通常涉及文件的打开、读写以及关闭。文件的打开使用fopen函数，而关闭则使用fclose函数。在C语言中，可以使用fread和fwrite函数进行二进制读写。‍ Biaoge 于2024-03-09 23:51发布 阅读量：7 ️文章类型：【 C语言程序设计 】在C语言中，用于打开文件的函数是____，用于关闭文件的函数是____。

Touchdesigner自学笔记之三_touchdesigner怎么让一个模型跟着鼠标移动-程序员宅基地

文章浏览阅读3.4k次，点赞2次，收藏13次。跟随鼠标移动的粒子以grid（SOP）为partical（SOP）的资源模板，调整后连接【Geo组合+point spirit（MAT)】，在连接【feedback组合】适当调整。影响粒子动态的节点【metaball(SOP)+force(SOP)】添加mouse in（CHOP)鼠标位置到metaball的坐标，实现鼠标影响。..._touchdesigner怎么让一个模型跟着鼠标移动

【附源码】基于java的校园停车场管理系统的设计与实现61m0e9计算机毕设SSM_基于java技术的停车场管理系统实现与设计-程序员宅基地

文章浏览阅读178次。项目运行环境配置：Jdk1.8 + Tomcat7.0 + Mysql + HBuilderX（Webstorm也行）+ Eclispe（IntelliJ IDEA,Eclispe,MyEclispe,Sts都支持）。项目技术：Springboot + mybatis + Maven +mysql5.7或8.0+html+css+js等等组成，B/S模式 + Maven管理等等。环境需要1.运行环境：最好是java jdk 1.8，我们在这个平台上运行的。其他版本理论上也可以。_基于java技术的停车场管理系统实现与设计

Android系统播放器MediaPlayer源码分析_android多媒体播放源码分析 时序图-程序员宅基地

文章浏览阅读3.5k次。前言对于MediaPlayer播放器的源码分析内容相对来说比较多，会从Java-&amp;amp;gt;Jni-&amp;amp;gt;C/C++慢慢分析，后面会慢慢更新。另外，博客只作为自己学习记录的一种方式，对于其他的不过多的评论。MediaPlayerDemopublic class MainActivity extends AppCompatActivity implements SurfaceHolder.Cal..._android多媒体播放源码分析 时序图

java 数据结构与算法 ——快速排序法-程序员宅基地

文章浏览阅读2.4k次，点赞41次，收藏13次。java 数据结构与算法 ——快速排序法_快速排序法

推荐文章

热门文章

相关标签