调用代码
public void createOrder(Order order) { order.setId(Long.valueOf(keyGenerator.generateKey().toString())); order.setCreateTime(new Date()); orderMapper.createOrder(order); }
定时任务（每月执行一次）
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_create_order`() BEGIN declare str_date varchar(16); SET str_date = date_format(date_add(NOW(), interval 1 MONTH),"%Y%m"); SET @sqlcmd1 = CONCAT('CREATE TABLE t_order_',str_date ," ( `id` bigint(32) NOT NULL, `order_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;"); PREPARE p1 FROM @sqlcmd1; EXECUTE p1; DEALLOCATE PREPARE p1; END
效果图
插入数据时基于雪花算法生成唯一主键
import com.google.common.base.Preconditions; import lombok.Getter; import lombok.Setter; import lombok.SneakyThrows; import org.apache.shardingsphere.core.strategy.keygen.TimeService; import org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.core.env.Environment; import org.springframework.stereotype.Component; import org.springframework.util.unit.DataUnit; import java.time.*; import java.time.format.DateTimeFormatter; import java.time.temporal.TemporalAdjusters; import java.util.Calendar; import java.util.Date; import java.util.Properties; import static java.time.temporal.TemporalAdjusters.firstDayOfMonth; @Component public final class KeyGenerator implements ShardingKeyGenerator { public KeyGenerator() { } public static final long EPOCH; private static final long SEQUENCE_BITS = 12L; private static final long WORKER_ID_BITS = 10L; private static final long SEQUENCE_MASK = (1 << SEQUENCE_BITS) - 1; private static final long WORKER_ID_LEFT_SHIFT_BITS = SEQUENCE_BITS; private static final long TIMESTAMP_LEFT_SHIFT_BITS = WORKER_ID_LEFT_SHIFT_BITS + WORKER_ID_BITS; private static final long WORKER_ID_MAX_VALUE = 1L << WORKER_ID_BITS; private static final long WORKER_ID = 0; private static final int MAX_TOLERATE_TIME_DIFFERENCE_MILLISECONDS = 1; @Setter private static TimeService timeService = new TimeService(); @Getter @Setter private Properties properties = new Properties(); private byte sequenceOffset; private long sequence; private long lastMilliseconds; @Value("${keyGenerator.snowflake.worker.id}") private String workerId; @Value("${max.tolerate.time.difference.milliseconds}}") private String maxTolerateTime; static { Calendar calendar = Calendar.getInstance(); calendar.set(2016, Calendar.NOVEMBER, 1); calendar.set(Calendar.HOUR_OF_DAY, 0); calendar.set(Calendar.MINUTE, 0); calendar.set(Calendar.SECOND, 0); calendar.set(Calendar.MILLISECOND, 0); EPOCH = calendar.getTimeInMillis(); } @Override public String getType() { return "THISSNOWFLAKEY"; } @Override public synchronized Comparable<?> generateKey() { long currentMilliseconds = timeService.getCurrentMillis(); if (waitTolerateTimeDifferenceIfNeed(currentMilliseconds)) { currentMilliseconds = timeService.getCurrentMillis(); } if (lastMilliseconds == currentMilliseconds) { if (0L == (sequence = (sequence + 1) & SEQUENCE_MASK)) { currentMilliseconds = waitUntilNextTime(currentMilliseconds); } } else { vibrateSequenceOffset(); sequence = sequenceOffset; } lastMilliseconds = currentMilliseconds; return ((currentMilliseconds - EPOCH) << TIMESTAMP_LEFT_SHIFT_BITS) | (getWorkerId() << WORKER_ID_LEFT_SHIFT_BITS) | sequence; } public synchronized long generatorKey() { return this.generateKeyByMillis(timeService.getCurrentMillis()).longValue(); } public synchronized Number generateKeyByMillis(long currentMilliseconds) { if (lastMilliseconds == currentMilliseconds) { if (0L == (sequence = (sequence + 1) & SEQUENCE_MASK)) { currentMilliseconds = waitUntilNextTime(currentMilliseconds); } } else { vibrateSequenceOffset(); sequence = sequenceOffset; } lastMilliseconds = currentMilliseconds; return ((currentMilliseconds - EPOCH) << TIMESTAMP_LEFT_SHIFT_BITS) | (this.getWorkerId() << WORKER_ID_LEFT_SHIFT_BITS) | sequence; } @SneakyThrows private boolean waitTolerateTimeDifferenceIfNeed(final long currentMilliseconds) { if (lastMilliseconds <= currentMilliseconds) { return false; } long timeDifferenceMilliseconds = lastMilliseconds - currentMilliseconds; Preconditions.checkState(timeDifferenceMilliseconds < getMaxTolerateTimeDifferenceMilliseconds(), "Clock is moving backwards, last time is %d milliseconds, current time is %d milliseconds", lastMilliseconds, currentMilliseconds); Thread.sleep(timeDifferenceMilliseconds); return true; } private long getWorkerId() { // long result = Long.valueOf(properties.getProperty("worker.id", String.valueOf(GenericTool.getWorkId()))); long result = Long.valueOf(workerId); Preconditions.checkArgument(result >= 0L && result < WORKER_ID_MAX_VALUE); return result; } private int getMaxTolerateTimeDifferenceMilliseconds() { // return Integer.valueOf(properties.getProperty("max.tolerate.time.difference.milliseconds", // String.valueOf(MAX_TOLERATE_TIME_DIFFERENCE_MILLISECONDS))); return Integer.valueOf(maxTolerateTime); } private long waitUntilNextTime(final long lastTime) { long result = timeService.getCurrentMillis(); while (result <= lastTime) { result = timeService.getCurrentMillis(); } return result; } private void vibrateSequenceOffset() { sequenceOffset = (byte) (~sequenceOffset & 1); } }
如何查询
1.询时能指定分片键的要尽量指定分片键，sharding-jdbc会自动帮你定位到对应的分表。
2.单分表查询，要指定分片键的，自己手动指定具体的分表先过滤出符合条件的数据，再union all其他分表符合条件的数据。union all的分表最好不要超过2张，即查询时间跨度在两个月内。
<select id="selectListByProductSelective" parameterType="hashmap" resultMap="ResultMapWithBLOBs"> select <include refid="Base_Column_List"/> , <include refid="Blob_Column_List"/> from ${startTable} <include refid="selectWhere"/> <if test="endTable != null "> union all select <include refid="Base_Column_List"/> , <include refid="Blob_Column_List"/> from ${endTable} <include refid="selectWhere"/> </if> <if test="pageStart != null and pageEnd!=null "> limit #{pageStart},#{pageEnd} </if> </select>
3.多分表关联查询时一定要单独指定每张分表的分片键的值（避免扫描多余的表）。
spring.shardingsphere.datasource.names=test spring.shardingsphere.datasource.test.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.test.filters=stat,wall,log4j spring.shardingsphere.datasource.test.stat-view-servlet.enabled=true spring.shardingsphere.datasource.test.stat-view-servlet.reset-enable=false spring.shardingsphere.datasource.test.stat-view-servlet.url-pattern=/druid/* spring.shardingsphere.datasource.test.testOnBorrow=false spring.shardingsphere.datasource.test.testOnReturn=false spring.shardingsphere.datasource.testn.testWhileIdle=true spring.shardingsphere.datasource.test.maxActive=20 spring.shardingsphere.datasource.test.maxWait=20000 spring.shardingsphere.datasource.testn.initial-size=1 spring.shardingsphere.datasource.test.min-idle=2 spring.shardingsphere.datasource.test.timeBetweenEvictionRunsMillis=60000 spring.shardingsphere.datasource.test.minEvictableIdleTimeMillis=300000 spring.shardingsphere.datasource.test.removeAbandoned=true spring.shardingsphere.datasource.test.removeAbandonedTimeout=180 spring.shardingsphere.datasource.test.maxPoolPreparedStatementPerConnectionSize=50 spring.shardingsphere.datasource.test.poolPreparedStatements=true spring.shardingsphere.datasource.test.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.test.url=jdbc:mysql://xxx:3306/database?useSSL=false&characterEncoding=utf-8&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true spring.shardingsphere.datasource.test.username=xxx spring.shardingsphere.datasource.test.password=xxx spring.shardingsphere.datasource.test.validationQuery=SELECT 1 FROM DUAL spring.shardingsphere.datasource.test.web-stat-filter.enabled=true spring.shardingsphere.datasource.test.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/* spring.shardingsphere.props.sql.show=false spring.shardingsphere.sharding.tables.defendant.actual-data-nodes = test.defendant_$->{1..5} spring.shardingsphere.sharding.tables.defendant.table-strategy.inline.sharding-column = case_id spring.shardingsphere.sharding.tables.defendant.table-strategy.inline.algorithm-expression=defendant_$->{case_id%5 + 1} spring.shardingsphere.sharding.tables.defendant_business.actual-data-nodes = test.defendant_business_$->{1..5} spring.shardingsphere.sharding.tables.defendant_business.table-strategy.inline.sharding-column = case_id spring.shardingsphere.sharding.tables.defendant_business.table-strategy.inline.algorithm-expression=defendant_business_$->{case_id%5 + 1}
case_id是defendant和defendant_business的分片键查询语句如下：
SELECT a.*, b.* FROM defendant a LEFT JOIN defendant_business b ON a.case_id = b.case_id AND a.case_id = 1 AND b.case_id = 1
