如何分表
1、垂直分表
将冷热数据分离,将常用数据和非常用数据进行分开存储。将不常用的大字段进行分表存储。
2、水平分表
将表中不同的数据行按照一定规律分布到不同的数据库表中。
可以使用求余的方式,也可以使用hash的方式。
使用sharing-jdbc进行分表后的查询
首先引入依赖
<dependency>
<groupId>com.dangdang</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>1.4.2</version>
</dependency>
<dependency>
<groupId>com.dangdang</groupId>
<artifactId>sharding-jdbc-config-spring</artifactId>
<version>1.4.0</version>
</dependency>
新建一个sharding-jdbc.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:rdb="http://www.dangdang.com/schema/ddframe/rdb"
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://www.dangdang.com/schema/ddframe/rdb
http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd">
<rdb:strategy id="tableShardingStrategy" sharding-columns="user_id" algorithm-class="com.meiren.member.common.sharding.MemberSingleKeyTableShardingAlgorithm"/>配置分表规则器 sharding-columns:分表规则依赖的名(根据user_id取模分表),algorithm-class:分表规则的实现类
<rdb:data-source id="shardingDataSource">
<rdb:sharding-rule data-sources="dataSource">这里填写关联数据源(多个数据源用逗号隔开),
<rdb:table-rules>
<rdb:table-rule logic-table="member_index" actual-tables="member_index_tbl_${[0,1,2,3,4,5,6,7,8,9]}${0..9}" table-strategy="tableShardingStrategy"/>
<rdb:table-rule logic-table="member_details" actual-tables="member_details_tbl_${[0,1,2,3,4,5,6,7,8,9]}${0..9}" table-strategy="tableShardingStrategy"/>
logic-table:逻辑表名(mybatis中代替的表名)actual-tables:数据库实际的表名,这里支持inline表达式,比如:member_index_tbl_${0..2}会解析成member_index_tbl_0,member_index_tbl_1,member_index_tbl_2;member_index_tbl_${[a,b,c]}会被解析成member_index_tbl_a,member_index_tbl_b和member_index_tbl_c,两种表达式一起使用的时候,会采取笛卡尔积的方式:member_index_tbl_${[a,b]}${0..2}解析为member_index_tbl_a0,member_index_tbl_a1,member_index_tbl_a2,member_index_tbl_b0,member_index_tbl_b1,member_index_tbl_b2;table-strategy:前面定义的分表规则器;
</rdb:table-rules>
</rdb:sharding-rule>
</rdb:data-source>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="shardingDataSource" />
</bean>
</beans>
配置好改文件后,需要修改之前我们的spring-dataSource的几个地方,把sqlSessionFactory和transactionManager原来关联的dataSource统一修改为shardingDataSource(这一步作用就是把数据源全部托管给sharding去管理)
实现分表(分库)逻辑,我们的分表逻辑类需要实现SingleKeyTableShardingAlgorithm接口的三个方法doBetweenSharding、doEqualSharding、doInSharding
/**
* 分表逻辑
* @author zhangwentao
*
*/
public class MemberSingleKeyTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {
/**
* sql between 规则
*/
public Collection<String> doBetweenSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<String>(tableNames.size());
Range<Long> range = (Range<Long>) shardingValue.getValueRange();
for (long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
Long modValue = i % 100;
String modStr = modValue < 10 ? "0" + modValue : modValue.toString();
for (String each : tableNames) {
if (each.endsWith(modStr)) {
result.add(each);
}
}
}
return result;
}
/**
* sql == 规则
*/
public String doEqualSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
Long modValue = shardingValue.getValue() % 100;
String modStr = modValue < 10 ? "0" + modValue : modValue.toString();
for (String each : tableNames) {
if (each.endsWith(modStr)) {
return each;
}
}
throw new IllegalArgumentException();
}
/**
* sql in 规则
*/
public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<String>(tableNames.size());
for (long value : shardingValue.getValues()) {
Long modValue = value % 100;
String modStr = modValue < 10 ? "0" + modValue : modValue.toString();
for (String tableName : tableNames) {
if (tableName.endsWith(modStr)) {
result.add(tableName);
}
}
}
return result;
}
}
以上四步,我们就完成了sharding-jdbc的搭建,我们可以写一个测试demo来检查我们的成果
<select id="getDetailsById" resultType="com.meiren.member.dataobject.MemberDetailsDO"
parameterType="java.lang.Long">
select user_id userId ,qq,email from member_details where user_id =#{userId} limit 1
</select>