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

This article will explain in detail how to use composite slicing in SpringBoot integration Sharding Jdbc. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

With the gradual increase of business, the amount of data originally stored in a single table is also increasing. Database data will continue to increase with the development of business, so the cost of data operations, such as additions, deletions, modifications and queries will become more and more expensive. Coupled with the limited resources of the physical server (CPU, disk, memory, IO, etc.). In the end, the amount of data and data processing capacity of the database will encounter bottlenecks. In other words, a reasonable database architecture is needed to store the growing data, which is the original intention of the design of sub-database and sub-table. The purpose is to alleviate the pressure on the database and improve the efficiency of data operation.

The database sub-database and sub-table middleware is apache sharding.

1. Introduction to Sharing JDBC

ShardingSphere is an ecological circle of open source distributed database middleware solutions, which consists of three independent products, Sharding-JDBC, Sharding-Proxy and Sharding-Sidecar (planned). They all provide standardized data sharding, distributed transaction and database governance functions, which can be applied to a variety of application scenarios, such as Java isomorphism, heterogeneous languages, cloud native, and so on.

ShardingSphere is positioned as relational database middleware, which aims to make full use of the computing and storage capacity of relational database in distributed scenarios, but not to achieve a new relational database. It is coexisting rather than mutually exclusive with NoSQL and NewSQL. As the forefront of new technology exploration, NoSQL and NewSQL are highly recommended for looking to the future and embracing change. On the contrary, we can also look at the problem in another way, look to the future, pay attention to the same things, and then grasp the essence of things. Relational database still occupies a huge market today, and it is the cornerstone of each company's core business, and it is difficult to shake in the future. at this stage, we pay more attention to the increment on the original basis rather than subversion.

ShardingSphere graduated from the Apache Incubator on April 16, 2020 and became a top-level Apache program.

2. System transformation

Because our company belongs to the third-party payment platform, this transformation point can be divided into two categories: the docking system provided to the merchant (such as the cashier), and the system called within the system (payment engine).

Cashier system: the core function is to provide a payment order for a merchant to submit a transaction order and pay for the transaction order.

Payment engine: receive the request of this payment product, call channel, bookkeeping, settlement and other functions

The data source uses sub-database and sub-table, and the whole table will be scanned if the sharding key is not included in the modification, deletion and query operation in Sharding JDBC. Therefore, during the business transformation, the original database operations are optimized, and all the operations after the basic transformation are based on sharding keys (except timing tasks).

2.1 system interfacing with external system

First of all, let's discuss the system provided to the merchant to call. When placing an order, the merchant must pass the merchant number and the external order number. The external order number can not be controlled by the third-party payment system, as long as the merchant does not repeat the historical external order number each time it is delivered. So here is a mapping table, the main functions of which are as follows:

Map the merchant's external order number to the internal order number

Idempotent processing is achieved through the combination of the merchant number and the merchant's external order number in the database.

Save the original data requested by the merchant as the request certificate

At this time, the transaction order depends on the external mapping table, and the request is mapped to the internal order number for fragmentation.

2.2 calls between internal systems

When a merchant places a transaction order and needs to make a payment, a payment order is generated. There is an one-to-many relationship between transaction orders and payment orders. When the user makes a payment, the payment engine will be called, and under normal circumstances, the payment order of the payment system will be generated. Then the payment engine will call the subsequent channels, settlement, accounting and other systems. The calls between the systems are shown below:

If the order number of the payment order of the payment system is used as the sharding key:

The internal system of the payment engine can use the sharding key to query, and it will be routed to the specific database table, no problem.

If the channel, settlement, accounting and other systems involve callback payment engine, the payment order number of the payment engine will be passed to the subsequent system when the payment engine is called. If the callback operation is carried out, the payment order number can be sent back. It will be routed to the specific database table, no problem

The cashier needs to query the payment order generated by the payment engine according to the payment order of the transaction. Because it is not queried according to the sharding key, it cannot be routed to a specific table in a specific library, and a full table scan will be performed. There will be a problem.

3. Solution

The first solution you can think of is to refer to the cashier system and call the cashier to the payment engine to see the external call. Then add a mapping table to associate the payment serial number generated by the cashier with the payment order number of the payment engine. When the cashier needs to query the payment engine, you can first query the specific payment order number through the mapping table, so that you can operate the data source with sharding keys. There is a problem with this scheme. There are several problems:

The associated table is introduced, and the complexity of the system is added to query the data twice, first query the mapping table, and then query the payment order.

So are there any other plans? The answer is yes.

Let's take a look at the cashier and the payment engine. In fact, these two systems are in the same latitude in the payment system. If the cashier's transaction order is paid, a payment order will be placed in the payment engine. We can split the transaction order and the payment order into the database at the same horizontal latitude.

What is a database split at the same latitude?

In fact, after the payment order of the cashier is divided into databases and tables, which database this data falls on, which table will be certain. At this time, the payment engine can obtain the specific database table information through this order number. In this way, you can take the order number generated by the payment engine with a specific database table information. Then, when defining the sub-library and sub-table algorithm, it is routed to the specific database table according to the information of the library table in the order number generated by the payment engine. The above problem will be solved without the need for a mapping table. At the same time, this scheme will also bring the following problems:

The subdatabase tables upstream and downstream of the data must be consistent.

There will be other problems when the data is being expanded again.

After discussion, it was decided to use plan two.

4. Code implementation

The following is achieved through the simple simulation code of Sharding jdbc's compound fragmentation. Database, table preparation:

Database:-order_0- order_1 the table of each database: tb_order_0tb_order_1tb_order_2tb_order_3tb_order_4tb_order_5tb_order_6tb_order_7# logical table create table tb_order (trade_master_no varchar (16), pay_order_no varchar (16),) # prepare data # the rule of sub-library and sub-table is the former representative database and the latter represents the table, so add the following data insert into tb_order_1 values ('11',' 11') to the order_1.tb_order_1, 4.1Sharding JDBC configuration

The following is the sublibrary and table configuration for the sharding jdbc of the order table, and the database connection pool uses Hikari. Slicing rules: the first representative library, the latter representative table. Use the transaction master order number (trade_master_no) and the payment order number (pay_order_no) as the composite shard. When the query condition contains only one query rule, it will be routed to the specific database table.

ComplexShardingJDBCConfig.java

@ Configurationpublic class ComplexShardingJDBCConfig {@ Bean public DataSource getShardingDataSource (HikariCommonConfig commonConfig) throws SQLException {ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration (); shardingRuleConfig.getTableRuleConfigs () .add (getShardingMessageTableRuleConfiguration ()); Map dataSourceMap = new HashMap (); dataSourceMap.put ("order_0", createDataSource (datasourceOne (commonConfig); dataSourceMap.put ("order_1", createDataSource (datasourceTwo (commonConfig) Properties properties = new Properties (); properties.setProperty (ShardingPropertiesConstant.SQL_SHOW.getKey (), "true"); return ShardingDataSourceFactory.createDataSource (dataSourceMap, shardingRuleConfig, properties);} private TableRuleConfiguration getShardingMessageTableRuleConfiguration () {TableRuleConfiguration shardingMessageConfiguration = new TableRuleConfiguration ("tb_order", "order_$ {0.1} .tb _ order_$ {0.7}") ShardingMessageConfiguration.setDatabaseShardingStrategyConfig (messageDatasourceShardingStrategyConfig ()); shardingMessageConfiguration.setTableShardingStrategyConfig (messageTableShardingStrategyConfig ()); return shardingMessageConfiguration;} private ComplexShardingStrategyConfiguration messageDatasourceShardingStrategyConfig () {return new ComplexShardingStrategyConfiguration ("trade_master_no,pay_order_no", new OrderDatasourceComplexKeysShardingAlgorithm ()) } private ShardingStrategyConfiguration messageTableShardingStrategyConfig () {return new ComplexShardingStrategyConfiguration ("trade_master_no,pay_order_no", new OrderTableComplexKeysShardingAlgorithm ());} @ Bean @ ConfigurationProperties (prefix = "spring.datasource.ds1") public HikariConfig datasourceOne (HikariCommonConfig commonConfig) {HikariConfig hikariConfig = new HikariConfig (); hikariConfig.setMinimumIdle (commonConfig.getMinimumIdle ()) HikariConfig.setIdleTimeout (commonConfig.getIdleTimeout ()); hikariConfig.setMaximumPoolSize (commonConfig.getMaximumPoolSize ()); hikariConfig.setPoolName (commonConfig.getPoolName ()); hikariConfig.setMaxLifetime (commonConfig.getMaxLifetime ()); hikariConfig.setConnectionTimeout (commonConfig.getConnectionTimeout ()); hikariConfig.setConnectionTestQuery (commonConfig.getConnectionTestQuery ()); return hikariConfig } @ Bean @ ConfigurationProperties (prefix = "spring.datasource.ds2") public HikariConfig datasourceTwo (HikariCommonConfig commonConfig) {HikariConfig hikariConfig = new HikariConfig (); hikariConfig.setMinimumIdle (commonConfig.getMinimumIdle ()); hikariConfig.setIdleTimeout (commonConfig.getIdleTimeout ()); hikariConfig.setMaximumPoolSize (commonConfig.getMaximumPoolSize ()); hikariConfig.setPoolName (commonConfig.getPoolName ()) HikariConfig.setMaxLifetime (commonConfig.getMaxLifetime ()); hikariConfig.setConnectionTimeout (commonConfig.getConnectionTimeout ()); hikariConfig.setConnectionTestQuery (commonConfig.getConnectionTestQuery ()); return hikariConfig;} private HikariDataSource createDataSource (HikariConfig hikariConfig) {HikariDataSource sharding = new HikariDataSource (); BeanUtils.copyProperties (hikariConfig, sharding); return sharding }}

Database slicing rules:

Public class OrderDatasourceComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm {@ Override public Collection doSharding (Collection availableTargetNames, ComplexKeysShardingValue shardingValue) {Map columnNameAndShardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap (); if (columnNameAndShardingValuesMap.containsKey ("trade_master_no")) {Collection tradeMasterNos = columnNameAndShardingValuesMap.get ("trade_master_no"); String tradeMasterNo = tradeMasterNos.iterator () .next () String datasourceSuffix = tradeMasterNo.substring (0,1); for (String availableTargetName: availableTargetNames) {if (availableTargetName.endsWith (datasourceSuffix)) {return Lists.newArrayList (availableTargetName) }} if (columnNameAndShardingValuesMap.containsKey ("pay_order_no")) {Collection payOrderNos = columnNameAndShardingValuesMap.get ("pay_order_no"); String payOrderNo = payOrderNos.iterator () .next () String datasourceSuffix = payOrderNo.substring (0,1); for (String availableTargetName: availableTargetNames) {if (availableTargetName.endsWith (datasourceSuffix)) {return Lists.newArrayList (availableTargetName) }} throw new UnsupportedOperationException ();}}

Table fragmentation rules in the database:

Public class OrderTableComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm {@ Override public Collection doSharding (Collection availableTargetNames, ComplexKeysShardingValue shardingValue) {Map columnNameAndShardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap (); if (columnNameAndShardingValuesMap.containsKey ("trade_master_no")) {Collection tradeMasterNos = columnNameAndShardingValuesMap.get ("trade_master_no"); String tradeMasterNo = tradeMasterNos.iterator () .next () String datasourceSuffix = tradeMasterNo.substring (1,2); for (String availableTargetName: availableTargetNames) {if (availableTargetName.endsWith (datasourceSuffix)) {return Lists.newArrayList (availableTargetName) }} if (columnNameAndShardingValuesMap.containsKey ("pay_order_no")) {Collection payOrderNos = columnNameAndShardingValuesMap.get ("pay_order_no"); String payOrderNo = payOrderNos.iterator () .next () String datasourceSuffix = payOrderNo.substring (1,2); for (String availableTargetName: availableTargetNames) {if (availableTargetName.endsWith (datasourceSuffix)) {return Lists.newArrayList (availableTargetName) } throw new UnsupportedOperationException ();}} 4.2 data source operation class

Mybatis is used here to manipulate the data source, but of course it is also supported to use other ORM frameworks to manipulate the data source sharding jdbc.

Public interface OrderMapper {int countByExample (OrderExample example); int deleteByExample (OrderExample example); int insert (Order record); int insertSelective (Order record); List selectByExample (OrderExample example); int updateByExampleSelective (@ Param ("record") Order record, @ Param ("example") OrderExample example); int updateByExample (@ Param ("record") Order record, @ Param ("example") OrderExample example);} 4.3 fragmentation test class

Define a Controller through Spring boot and query using the Order object. You can use either the transaction number or the payment number to query.

@ Getter@Setterpublic class Order {private String tradeMasterNo; private String payOrderNo;} @ RestController@RequestMapping ("order") public class OrderController {@ Resource private OrderDao orderDao; @ RequestMapping ("query") public Order query (@ RequestBody Order order) {Order orderInDB = orderDao.queryOrder (order); return orderInDB;}} 4.4 Test results

Since we configured the database query SQL in the sharing jdbc configuration, we only need to see if only one database operation statement has been printed to determine whether the previous conclusion is correct.

Use the transaction number to query through Postman:

Console print:

Then use the payment order number to query through Postman:

Console print:

Their queries are routed to specific database tables, indicating that our solution is OK.

This is the end of the operation of SpringBoot integration Sharding Jdbc using composite shards. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.