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

This article is to share with you about how to carry out Java ShardingJDBC actual combat, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

I. background

Recently, the single project table on hand in the company has reached a scale of 50 million, and the daily growth rate is about 10w every day, and there are about 300w of data in a month, so it is easy to have hundreds of millions of data in the table after a few months, which is not conducive to management and in the case of a large table, the DDL efficiency of the table will also be relatively reduced, and several colleagues discussed it, so they began to optimize the technology of sub-table.

II. Optimization matters

(1) first, determine the usage scenario. The current table usage scenario is mostly queried according to a specific identification value, and the scene frequency of range query is relatively low. In this case, consider using the identification value as a sharding key to split the table. The specific algorithm is: through the identification value through the algorithm to calculate the specific time quarter, split according to the season, that is, one year

Record_delivery_log

4 tables record_order_log_202101,record_order_log_202102,record_order_log_202103,record_order_log_202104

The amount of data in a single table before splitting is 5000w.

After the split, the amount of data of the single table becomes 1200w, which can tolerate the future growth of 4 ~ 5 times, which is in line with the expected range.

(2) the corresponding database and table middleware is investigated. At present, Sharing-jdbc is the most mainstream middleware, and the community and documents are perfect, so Sharing-jdbc is used as the middleware of table.

III. Specific actual combat

Here, because the company project is not easy to reuse, a simulation project is used to simulate this transformation.

(1) reconstruct the project with reference to sharing-jdbc documents

Introduce the pom corresponding to sharing-jdbc.

Org.apache.shardingsphere shardingsphere-jdbc-core-spring-boot-starter 5.0.0-beta

Corresponding configuration file

# Port server.port=8080 # data source ds0spring.shardingsphere.datasource.name=ds0# data source ds0 configuration spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.ds0.driverClassName=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/world1?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2b8spring.shardingsphere.datasource.ds0.username=rootspring.shardingsphere.datasource.ds0.password=123456 # sharding rules, here only sub-table So only specify the sharding rule of the table spring.shardingsphere.rules.sharding.tables.record_order_log.actual-data-nodes=ds0.record_order_log_$- > {2021. 2031} 0 $- > {1.. 4} # specify the sharding key of the database There is only one library, so use the sharding key spring.shardingsphere.rules.sharding.tables.record_order_log.database-strategy.standard.sharding-column=order_delivery_idspring.shardingsphere.rules.sharding.tables.record_order_log.database-strategy.standard.sharding-algorithm-name=database-inline # to specify the sharding key spring.shardingsphere.rules.sharding.tables.record_order_log.table-strategy.standard.sharding-column=order_delivery_idspring.shardingsphere.rules.sharding.tables of the subtable. .record _ order_log.table-strategy.standard.sharding-algorithm-name=table-inline # Omit t_order_item table rule configuration. #... # sharding rule (default mode) spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.type=INLINEspring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.algorithm-expression=ds0spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.type=CLASS_BASEDspring.shardingsphere.rules.sharding.sharding-algorithms.table-inline .props.strategy = STANDARDspring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.algorithmClassName=com.cus.shd.sharingjdbc.config.OrderDeliveryIdShardingAlgorithmspring.shardingsphere.props.sql.show=true # mybatis-plus??mybatis-plus.mapper-locations=classpath:mappers/*.xmlmybatis-plus.type-aliases-package=com.cus.shd.sharingjdbc.modelmybatis-plus.configuration.map-underscore-to-camel-case=true# sql??mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl# local database link Ignore the failure of spring.datasource.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.driverClassName=com.mysql.cj.jdbc.Driverspring.datasource.url=jdbc:mysql://localhost:3306/world1?characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2b8spring.datasource.username=rootspring.datasource.password=123456 after springboot automatic loading

Pay attention to the table name when the subtable key is set.

(2) Custom sharding key policy, which is stored quarterly according to order_delivery_id

Package com.cus.shd.sharingjdbc.config; import org.apache.commons.lang.StringUtils;import org.apache.shardingsphere.sharding.api.sharding.ShardingAutoTableAlgorithm;import org.apache.shardingsphere.sharding.api.sharding.standard.PreciseShardingValue;import org.apache.shardingsphere.sharding.api.sharding.standard.RangeShardingValue;import org.apache.shardingsphere.sharding.api.sharding.standard.StandardShardingAlgorithm; import java.time.LocalDateTime;import java.time.ZoneOffset;import java.util.Collection / * * @ author ASUS * @ Description Custom sharding Policy * @ Date 22:20 on 2021-11-6 * * / public class OrderDeliveryIdShardingAlgorithm implements StandardShardingAlgorithm {@ Override public String doSharding (Collection availableTargetNames, PreciseShardingValue shardingValue) {String orderDeliveryId = shardingValue.getValue (). ToString (); orderDeliveryId = orderDeliveryId.substring (0deliveryId.length ()-4) / / convert the timestamp to the current time LocalDateTime localDateTime = LocalDateTime.ofEpochSecond (Long.valueOf (orderDeliveryId) / 1000, 0, ZoneOffset.ofHours (8)); String availableTargetName; int month = localDateTime.getMonthValue (); LocalDateTime nowTime = LocalDateTime.now (); int year = nowTime.getYear (); if (month > = 1 & & month

< 3){ availableTargetName = "01"; }else if(month >

= 3 & & month

< 6){ availableTargetName = "02"; }else if(month >

= 6 & & month

< 9){ availableTargetName = "03"; }else { availableTargetName = "04"; } if(StringUtils.isEmpty(availableTargetName)){ return null; } return String.format("%s_%s%s",shardingValue.getLogicTableName(),year,availableTargetName); } @Override public Collection doSharding(Collection availableTargetNames, RangeShardingValue shardingValue) { return availableTargetNames; } @Override public void init() { } @Override public String getType() { return "ORDER_DELIVERY_ID"; }} （3）模拟提供两个接口，一个按id查询，一个插入接口。（修改的场景暂时没有，所以不考虑）

When it is added, simulated insertion is made, and the data can be stored in the corresponding table according to the slicing algorithm to achieve the effect.

The same goes for inquiries.

(4) sharing-jdbc does not automatically create tables, so you need to maintain a scheduled task in the background and create tables at a certain quarterly point. (you need to ensure that the database account corresponding to the application in the production environment has the permission to create tables)

CREATE TABLE ${tableName} SELECT * FROM record_order_log WHERE 1 # 2 IV. Problems encountered.

1. An error was reported when introducing the sharing-jdbc package. Here, debug to the automatic initiator (MybatisPlusAutoConfiguration) of mybatisPlus is found to be a single data source class (data sources in spring cannot have multiple implementation classes). Because the introduction of sharing results in multiple data sources (multi-datasource), this will not be started, resulting in an error when instantiating mapper. The solution is to add to the comments of the startup class of SpringBoot

@ SpringBootApplication (exclude = {DataSourceAutoConfiguration.class,DruidDataSourceAutoConfigure.class})

Ignore the automatic assembly of SpringBoot data sources and Druid data sources, and instantiate all data sources to sharing-jdbc

2. Some projects have problems left over from history. In the case of mybatis or hibernate, if you do not want to completely introduce sharding-jdbc data sources, I think you can use the form of multiple data sources to transform, to expand some database operations that need to use sub-tables, and switch the corresponding sharding data sources for database operations. For more information, you can refer to some code in the switchDataSource directory for switching data sources.

3. Questions for yourself

How does sharing-jdbc integrate mybatis-plus after ignoring DataSourceAutoConfiguration.class?

A: in fact, it is not difficult. The data source object was originally injected by the automatic injection of the data source included in SpringBoot, but now it has been injected by the ShardingSphereAutoConfiguration of Sharding, which is equivalent to changing a whole set of things of the entire data source, using the whole set of things of sharding.

Therefore, it is necessary to check whether there is any impact on the old project during the transformation.

The above is how to carry out Java ShardingJDBC actual combat, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.