vlambda博客
学习文章列表

数据库分库分表插件使用

数据库分库分表插件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框架,使用上没有区别