更新：按月分表查詢更新
更新內(nèi)容：添加范圍搜索比如201901~201605五個月的數(shù)據(jù)粹舵，之前是只能查詢單月的數(shù)據(jù)
<!--sharding-jdbc -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<!--sharding-jdbc結(jié)束-->
sharding-jdbc 按月份分表需要自己實現(xiàn)失驶。需要實現(xiàn)兩個接口PreciseShardingAlgorithm，RangeShardingAlgorithm博烂。并在配置文件里添加實現(xiàn)路徑
如下：com.simianBook.conf.TimeShardingTableAlgorithm
TimeShardingTableAlgorithm路徑
那么yml 里的配置路徑如下
sharding:
jdbc:
datasource:
names: user-0,user-1
user-0: #springboot 在yml 配置里key不支持 '_' 推薦使用'-'
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/t_user_0?characterEncoding=UTF-8&serverTimezone=GMT
username: root
password: hou1147646079
user-1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/t_user_1?characterEncoding=UTF-8&serverTimezone=GMT
username: root
password: hou1147646079
config:
sharding: #分片
default-database-strategy:#數(shù)據(jù)庫默認(rèn)分庫
inline:
sharding-column: user_id
algorithm-expression: user ->${user_id % 2}
props:
sql.show: true
tables:
user:
key-generator-column-name: user_id #user 表的主鍵
database-strategy:
inline:
shardingColumn: user_id #數(shù)據(jù)庫分片測略
algorithm-expression: user-${user_id % 2} #
# actual-data-nodes: user-${0..1}.user_${0..1} #設(shè)置的datasource 的名字 如user-0,user-1,user_${0..1} 數(shù)據(jù)庫中的
table-strategy:
standard: # 單列sharidng算法,需要配合對應(yīng)的preciseShardingAlgorithm,rangeShardingAlgorithm接口的實現(xiàn)使用,目前無生產(chǎn)可用實現(xiàn)
shardingColumn: user_id # 列名,允許單列
precise-algorithm-class-name: com.simianBook.config.TimeShardingTableAlgorithm # preciseShardingAlgorithm接口的實現(xiàn)類
# rangeShardingAlgorithm: # rangeShardingAlgorithm接口的實現(xiàn)類
# inline:
# sharding-column: user_id
# algorithm-expression: user_${user_id % 2}
defaultTableStrategy:
none:
defaultKeyGenerator:
type: SNOWFLAKE
按月分表查詢更新
因為SimpleDateFormat 不是線程安全的需要修改為DateTimeFormatter
import com.google.common.collect.Range;
import com.simianBook.tool.GenericTool;
import com.simianBook.tool.ParaseShardingKeyTool;
import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.RangeShardingAlgorithm;
import io.shardingsphere.core.keygen.DefaultKeyGenerator;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.LinkedHashSet;
import java.util.stream.Stream;
/**
* 搜查多表
* 范圍搜索時（跨表）應(yīng)傳遞時間戳并左移22位
*/
public class TimeRangeShardingAlgorithm implements RangeShardingAlgorithm<Long> {
private DateTimeFormatter dateformat = DateTimeFormatter.ofPattern("yyyyMM");
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<String>();
Range<Long> shardingKey = shardingValue.getValueRange();
long startShardingKey = shardingKey.lowerEndpoint();
long endShardingKey = shardingKey.upperEndpoint();
//獲取到開始時間戳
String startTimeString = ParaseShardingKeyTool.getYearAndMonth(startShardingKey);
//獲取結(jié)束時間戳
String endTimeString = ParaseShardingKeyTool.getYearAndMonth(endShardingKey);
Calendar cal = Calendar.getInstance();
//獲取開始的年月
//時間戳
LocalDateTime startLocalDate = GenericTool.getLocalDate(startTimeString);
//獲取結(jié)束的年月
LocalDateTime endLocalDate = GenericTool.getLocalDate(endTimeString);
//進(jìn)行判斷 獲取跨月份的表 如201901,201902,201903 三個月的表
//目前只支持同一年內(nèi)的查詢，跨年不支持
int end = Integer.valueOf(dateformat.format(endLocalDate));
int start = Integer.valueOf(dateformat.format(startLocalDate));
while(start <= end){
StringBuffer tableName = new StringBuffer();
tableName.append(shardingValue.getLogicTableName())
.append("_").append(start);
result.add(tableName.toString());
start++；
}
return result;
}
}
使用到的方法
import io.shardingsphere.core.keygen.DefaultKeyGenerator;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
public class ParaseShardingKeyTool {
private static DateTimeFormatter yearAndMonth = DateTimeFormatter.ofPattern("yyyyMM");
private static DateTimeFormatter year = DateTimeFormatter.ofPattern("yyyy");
public static String getYearAndMonth(long shardingKey){
Instant instant = Instant.ofEpochMilli(DefaultKeyGenerator.EPOCH+(Long.valueOf(shardingKey+"")>>22));
LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
return yearAndMonth.format(localDateTime);
}
public static String getYear(long shardingKey){
Instant instant = Instant.ofEpochMilli(DefaultKeyGenerator.EPOCH+(Long.valueOf(shardingKey+"")>>22));
LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
return year.format(localDateTime);
}
public static void main(String[] args) {
DefaultKeyGenerator defaultKeyGenerator = new DefaultKeyGenerator();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMM");
System.out.println(simpleDateFormat.format(System.currentTimeMillis()));
System.out.println(ParaseShardingKeyTool.getYearAndMonth(Long.valueOf(defaultKeyGenerator.generateKey()+"")));
System.out.println(ParaseShardingKeyTool.getYearAndMonth(Long.valueOf(defaultKeyGenerator.generateKey()+"")));
}
}
下面需要來編寫按單月分表的方法
package com.simianBook.config;
import com.simianBook.tool.ParaseShardingKeyTool;
import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import java.text.SimpleDateFormat;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
public class TimeShardingTableAlgorithm implements PreciseShardingAlgorithm<Long> {
private DateTimeFormatter dateformat = DateTimeFormatter.ofPattern("yyyyMM");
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
StringBuffer tableName = new StringBuffer();
tableName.append(shardingValue.getLogicTableName())
.append("_").append(ParaseShardingKeyTool.getYearAndMonth(shardingValue.getValue()));
return tableName.toString();
}
}
我是使用sharding-jdbc 自帶的雪花算法 來生成主鍵的篙贸，雪花算法的實現(xiàn)邏輯
因此我在得到分片鍵時對分片鍵進(jìn)行逆推可以推出分片鍵的時間戳。再根據(jù)時間戳得到創(chuàng)建此條數(shù)據(jù)創(chuàng)建的年月進(jìn)而定位到那個表位置（或者說表名）
DefaultKeyGenerator.EPOCH+(Long.valueOf(shardingKey+"")>>22)
DefaultKeyGenerator.EPOCH 表示起始時間枫疆。在雪花算法當(dāng)中生成的時間戳需要減去起始時間在進(jìn)行左移22位在進(jìn)行或運(yùn)算
sharding-jdbc 的雪花實現(xiàn)方法如下 版本3.0 該版本有bug 并發(fā)量低的時候生成的分片鍵始終為偶數(shù)
public synchronized Number generateKey() {
long currentMillis = timeService.getCurrentMillis();
Preconditions.checkState(this.lastTime <= currentMillis, "Clock is moving backwards, last time is %d milliseconds, current time is %d milliseconds", new Object[]{this.lastTime, currentMillis});
if (this.lastTime == currentMillis) { //最新時間與當(dāng)前時間相同（發(fā)生并發(fā)）
// //sequence+1 如果suquence&4095L==0時條件成立時
if (0L == (this.sequence = this.sequence + 1L & 4095L)) {
currentMillis = this.waitUntilNextTime(currentMillis);//獲取最新時間
}
} else {
this.sequence = 0L; //此處有bug 并發(fā)量低的時候this.sequence始終為0L的
}
this.lastTime = currentMillis;
return currentMillis - EPOCH << 22 | workerId << 12 | this.sequence;
}
3.1.0 版本解決了 自己可以看一下有什么不同
public synchronized Number generateKey() {
long currentMilliseconds = timeService.getCurrentMillis();
if (this.waitTolerateTimeDifferenceIfNeed(currentMilliseconds)) {
currentMilliseconds = timeService.getCurrentMillis();
}
if (this.lastMilliseconds == currentMilliseconds) {
if (0L == (this.sequence = this.sequence + 1L & 4095L)) {
currentMilliseconds = this.waitUntilNextTime(currentMilliseconds);
}
} else {
this.vibrateSequenceOffset();
this.sequence = (long)this.sequenceOffset;
}
this.lastMilliseconds = currentMilliseconds;
return currentMilliseconds - EPOCH << 22 | workerId << 12 | this.sequence;
}
工具類
package com.simianBook.tool;
import io.shardingsphere.core.keygen.DefaultKeyGenerator;
import org.apache.commons.lang.StringUtils;
import java.lang.management.ManagementFactory;
import java.math.BigDecimal;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.regex.Pattern;
/**
* @program: simianBook1
* @description: 通用工具類
* @author: houzi
* @create: 2019-03-14 10:54
*/
public class GenericTool {
private static final String EMAIL_FORMAT =" /^\\w+([\\.-]?\\w+)*@\\w+([\\.-]?\\w+)*(\\.\\w{2,3})+$/";
public static long getWorkId(){
String name = ManagementFactory.getRuntimeMXBean().getName();
long workId =Long.valueOf(name.split("@")[0]);
return workId;
}
/**
*
* @return
*/
public static long getSahrdingKey(){
long workId = GenericTool.getWorkId();
DefaultKeyGenerator.setWorkerId(workId);
DefaultKeyGenerator defaultKeyGenerator = new DefaultKeyGenerator();
GenericTool.getWorkId();
return Long.valueOf(defaultKeyGenerator.generateKey()+"");
}
/**
* 通過時間戳獲取時間
* @param timestamp
* @return
*/
public static LocalDateTime getLocalDate(String timestamp){
Instant startInstant = Instant.ofEpochMilli(Long.valueOf(timestamp));
LocalDateTime localDate = LocalDateTime.ofInstant(startInstant, ZoneId.systemDefault());
return localDate;
}
public static boolean isEmail(String email){
if(StringUtils.isBlank(email)){
return false;
}
return Pattern.matches(EMAIL_FORMAT,email);
}
}
表的命名
user_201907
image.png
