数据库分库分表插件shardingsphere

数据库分库分表插件shardingsphere

shardingsphere简介

Apache ShardingSphere(Incubator) 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（规划中）这3款相互独立，却又能够混合部署配合使用的产品组成。它们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景。

Share-JDBC简介

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

• 适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC。
• 支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP 等。
• 支持任意实现 JDBC 规范的数据库，目前支持 MySQL，Oracle，SQLServer，PostgreSQL 以及任何遵循 SQL92 标准的数据库。

sharejdbc配置

<?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:tx="http://www.springframework.org/schema/tx"
       xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"
       xmlns:spi="http://shardingsphere.apache.org/schema/shardingsphere/spi"
       xmlns:bean="http://www.springframework.org/schema/util"
       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://shardingsphere.apache.org/schema/shardingsphere/sharding
                        http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
                        http://shardingsphere.apache.org/schema/shardingsphere/spi
                        http://shardingsphere.apache.org/schema/shardingsphere/spi/spi.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">

    <bean id="demo_ds_0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://192.168.50.65:3306/ds0?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>

    <bean id="demo_ds_1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://192.168.50.65:3306/ds1?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>

    <spi:sharding-algorithm id="databaseStrategyShardingAlgorithm" type="INLINE" props-ref="databaseStrategyShardingAlgorithmProp" />

    <spi:sharding-algorithm id="tableStrategyShardingAlgorithm" type="INLINE" props-ref="tableStrategyShardingAlgorithmProp" />

    <bean:properties id="databaseStrategyShardingAlgorithmProp">
        <prop key="algorithm.expression"><![CDATA[demo_ds_$->{user_id % 2}]]></prop>
    </bean:properties>

    <bean:properties id="tableStrategyShardingAlgorithmProp">
        <prop key="algorithm.expression"><![CDATA[t_user_$->{user_id % 3}]]></prop>
    </bean:properties>

    <bean:properties id="properties">
        <prop key="worker.id">123</prop>
    </bean:properties>

    <sharding:standard-strategy id="databaseStrategy" sharding-column="user_id" algorithm-ref="databaseStrategyShardingAlgorithm"/>
    <sharding:standard-strategy id="tableStrategy" sharding-column="user_id" algorithm-ref="tableStrategyShardingAlgorithm"/>

    <spi:key-generate-algorithm id="snowflakeAlgorithm" type="SNOWFLAKE" props-ref="properties"/>

    <sharding:key-generator id="orderKeyGenerator" column="user_id" algorithm-ref="snowflakeAlgorithm" />

    <sharding:data-source id="shardingDataSource">
        <sharding:sharding-rule data-source-names="demo_ds_0, demo_ds_1">
            <sharding:table-rules>
                <sharding:table-rule logic-table="t_user" actual-data-nodes="demo_ds_$->{0..1}.t_user_$->{0..2}" database-strategy-ref="databaseStrategy" table-strategy-ref="tableStrategy" key-generator-ref="orderKeyGenerator" />
            </sharding:table-rules>
            <sharding:binding-table-rules>
                <sharding:binding-table-rule logic-tables="t_user"/>
            </sharding:binding-table-rules>
        </sharding:sharding-rule>
    </sharding:data-source>

    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="shardingDataSource" />
    </bean>

    <tx:annotation-driven />

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="shardingDataSource"/>
        <property name="mapperLocations" value="classpath*:META-INF/mappers/*.xml"/>
    </bean>

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="cn.com.immortals.ds.share"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        <property name="annotationClass" value="cn.com.immortals.ds.share.RepositoryImpl" />
    </bean>


</beans>

配置解析

• 配置时需要配置物理数据源，物理数据源是与数据库直接链接，使用任意数据连接即可
• 配置分库算法，<spi:sharding-algorithm id="databaseStrategyShardingAlgorithm" type="INLINE" props-ref="databaseStrategyShardingAlgorithmProp" /> 制定分库条件，
• 配置分表算法<sharding:standard-strategy id="tableStrategy" sharding-column="user_id" algorithm-ref="tableStrategyShardingAlgorithm"/> 制定分表的条件表达式
• 配置逻辑数据源 shareDataSource
• 配置ORM框架，使用上没有区别

样例代码在样例代码