Shulou(Shulou.com)06/02 Report--

This article shows you how to carry out the horizontal sub-table practice sharding-jdbc 4.0.0-RC3-SNAPSHOT, the content is concise and easy to understand, can definitely brighten your eyes, through the detailed introduction of this article, I hope you can get something.

Abstract

The example of this article is a monthly level table. There are two shortcomings:

The primary key of the sub-table is not well designed, and the self-growing id is used in this paper, and the time is not combined into the primary key, resulting in a scenario that only queries based on the primary key.

There is no redundant field in the table, which is specially used to divide the table, and the sub-table field is coupled with the business field, resulting in some details. For example, the create_time in this article is with milliseconds, and some time addition and subtraction operations will lose milliseconds so that the data cannot be found.

Limited to the size of the team, there is no separation of reading and writing.

Practical background

At present, our payment order center pipeline table has 2400w data (mysql single table), the query speed is very slow, and is growing at the rate of 20w + every day. Considering this amount of data (600w data per month), we plan to break down the table on a monthly basis, so that the amount of 600w + data in each table is more suitable for query.

Design ideas

Keeping all data up to November 2019 in the default table (imass_order_record) has the advantage of not having to migrate any historical data. After that, build the table on a monthly basis. For example, the data of November 11, 2019 is put into the imass_order_record_201911 table, and the data of December 11, 2019 is written into the imass_order_record_201912 table.

Here, pay a little attention to the problem of "monthly cut" when doing data query.

The split table policy jar depends on 4.0.0-RC3-SNAPSHOT org.apache.shardingsphere sharding-jdbc-core ${sharding-sphere.version} Org.apache.shardingsphere sharding-jdbc-spring-boot-starter ${sharding-sphere.version} Org.apache.shardingsphere sharding-jdbc-spring-namespace ${sharding-sphere.version} Org.apache.shardingsphere sharding-transaction-xa-core ${sharding-sphere.version}

The above jar are added as needed.

Accurate sub-table strategy package com.imassbank.unionpay.sharding;import java.text.ParseException;import java.time.LocalDate;import java.time.ZoneId;import java.time.format.DateTimeFormatter;import java.util.Collection;import java.util.Date;import java.util.Locale;import org.apache.commons.lang3.time.DateUtils;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;import lombok.extern.slf4j.Slf4j / * * @ author Michael Feng * @ date September 19, 2019 * @ description * / @ Slf4jpublic class DatePreciseShardingAlgorithm implements PreciseShardingAlgorithm {private static DateTimeFormatter sdf = DateTimeFormatter.ofPattern ("yyyyMM", Locale.CHINA); private static final String SEPERATOR = "_"; / / Table name separator private static Date lowwerDate = null Static {try {lowwerDate = DateUtils.parseDate ("201911", "yyyyMM");} catch (ParseException e) {log.error ("parsing actual date exception", e) } @ Override public String doSharding (Collection availableTargetNames, PreciseShardingValue shardingValue) {String loginTableName = shardingValue.getLogicTableName (); Date createTime = shardingValue.getValue () If (createTime = = null | | createTime.before (lowwerDate)) {log.info ("creation time is empty, or current time: {} < 2019-11, enter the default table", createTime); return loginTableName;} String yyyyMM = "" Try {yyyyMM = SEPERATOR+ createTime.toInstant (). AtZone (ZoneId.systemDefault ()). ToLocalDate (). Format (sdf); log.info ("enter Table: {}", loginTableName+yyyyMM); return loginTableName+yyyyMM } catch (Exception e) {log.error ("parsing creation time exception, sub-table failed, enter the default table", e);} return loginTableName;}} range query policy package com.imassbank.unionpay.sharding;import java.text.ParseException;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.Locale;import java.util.concurrent.atomic.AtomicInteger;import org.apache.commons.lang3.time.DateUtils;import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;import com.alibaba.fastjson.JSONObject;import com.google.common.collect.Range;import com.google.common.collect.Sets;import lombok.extern.slf4j.Slf4j / * * @ author Michael Feng * @ date September 19, 2019 * @ description * / @ Slf4jpublic class DateRangeShardingAlgorithm implements RangeShardingAlgorithm {private static DateTimeFormatter sdf = DateTimeFormatter.ofPattern ("yyyyMM", Locale.CHINA); private static final String SEPERATOR = "_"; / / Table name separator private static Date lowwerDate = null Static {try {lowwerDate = DateUtils.parseDate ("201911", "yyyyMM");} catch (ParseException e) {log.error ("parsing actual date exception", e) } @ Override public Collection doSharding (Collection availableTargetNames, RangeShardingValue shardingValue) {Collection tableSet = Sets.newConcurrentHashSet (); String logicTableName = shardingValue.getLogicTableName (); Range dates = shardingValue.getValueRange (); Date lowDate = DateUtils.truncate (dates.lowerEndpoint (), Calendar.MONTH) Date upperDate = DateUtils.truncate (dates.upperEndpoint (), Calendar.MONTH); / / to add the current month AtomicInteger I = new AtomicInteger (0); while (DateUtils.addMonths (lowDate, i.get ()) .compareto (upperDate) * and create_time

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.