A method and device for platform library and transaction primary key table splitting, and a storage medium

Abstract

The present application relates to the field of internet distributed micro-service architecture, and provides a method and device for platform database and transaction primary key table splitting and a storage medium.The main purpose is to solve the problem that when data reaches the limit, the transaction creation and query performance will be significantly reduced.The main scheme includes receiving a consumption transfer transaction message sent by the platform, performing basic verification, and storing transaction information table payments_xx and transaction information matching table payment_mappings_xx through the shardingjdc configured database and table splitting strategy, wherein_xx is a numerical variable serial number, according to the transaction information table payments_xx database splitting strategy configuration, the transaction information falls into the table payments_xx, the transaction matching information falls into the table payment_mappings_xx, the transaction is sent to the account module for accounting, and the accounting result is updated to the status field of the transaction information table payments_xx after the accounting success or failure.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of Internet distributed micro-service architecture, and provides a platform database splitting and transaction primary key table splitting method and device and storage medium. BACKGROUND

[0002] The bank account system is divided into an application service module, a transaction service module, an account service module and an account reconciliation service module. The services communicate with each other using the dubbo rpc protocol.

[0003] The transaction module receives and verifies records of various transactions, and then sends them to the account module for bookkeeping.

[0004] The transaction module has a transaction consumption transfer (payments). The system target in the next 1-2 years is to predict that the daily data volume will be more than 200w in the future. At present, a single table is still used, which cannot support the existing business.

[0005] The present application is a new technical solution for platform (channel) database splitting and transaction primary key table splitting based on the shardingjdbc third-party open source jar extension development. The existing non-table splitting solution cannot support the existing business. Therefore, a database and table splitting technology system capable of supporting large transaction volume is urgently needed. SUMMARY

[0006] The daily data volume of the consumption transfer transaction is 200w. According to the mysql database specification, the general business table limit is 500w data. If no table splitting is performed, 3 days of data will reach the limit, which will cause the transaction creation and query performance to decrease significantly. Therefore, database and table splitting must be performed, otherwise the business operation cannot be supported.

[0007] In order to solve the above technical problems, the present application adopts the following technical means:

[0008] A platform database splitting and transaction primary key table splitting method comprises the following steps:

[0009] Step 1: After receiving the consumption transfer transaction message sent by the platform and performing basic verification, the transaction information table payments_xx and the transaction information pairing table payment_mappings_xx are stored through the shardingjdc configured database and table splitting strategy, wherein_xx is a numerical variable number, and the platform id information root_mch_id in the consumption transfer transaction message is used to determine whether the platform is a large transaction volume platform according to the database splitting strategy configuration of the transaction information table payments_xx;

[0010] Step 2: As in step 1, if the platform is a large transaction volume platform, the large transaction volume platform uses m databases as a large platform for independent use according to the configuration specified by the large transaction volume platform;

[0011] Step 3: Continue from step 1, if the platform is not a large transaction volume platform, according to the platform root_mch_id / n modulo operation to get the corresponding sub-database, n sub-database for all not large data volume transaction platform sharing use;

[0012] Step 4: Continue from step 2, step 3 get the transaction sub-database, according to the transaction information table payments_xx sub-table strategy configuration using the transaction key payment_id / n1 modulo operation to get the corresponding sub-table, each database n1 sub-table payments_0, payments_1,..., payments_xx,..., payments_n1-1;

[0013] Step 5: Continue from step 4, write the transaction information in the consumption transfer transaction report into the corresponding payments_xx;

[0014] Step 6: Continue from step 3, in the same database determined in step 3, another transaction pairing table payment_mappings_xx is stored based on the transaction order number, which only stores the primary key out_order_no transaction order number, payment_id transaction primary key id, root_mch_id platform ID, according to the transaction pairing table payment_mappings_xx sub-table strategy configuration using the transaction order number out_order_no.hashcode / n2 modulo operation to get the corresponding sub-table, each database n2 sub-table payment_mappings_0,..., payment_mappings_xx,..., payment_mappings_n2-1, wherein out_order_no.hashcode is a string operation to get a numerical value;

[0015] Step 7: Continue to write the transaction pairing information in the consumption transfer transaction message into the table payment_mappings_xx;

[0016] Step 8: Continue from step 7, by now, the transaction information and transaction pairing information have been configured to fall into the corresponding sub-database and sub-table, the transaction information falls into the table payments_xx, and the transaction pairing information falls into the table payment_mappings_xx. The transaction is sent to the account module for accounting. After the accounting is successful or failed, the accounting result is updated to the status field of the transaction information table payments_xx. When updating, according to steps 1, 2, 3, 4, find the corresponding sub-database and sub-table, and then update;

[0017] Step 9: According to step 1, 2 and 3, the platform can query the transaction state through the transaction order number, and according to step 1, 2 and 3, the corresponding sub-table is obtained by using the transaction order number out_order_no.hashcode / n2 modulo operation, the payment_mappings data is obtained to obtain the payment_id corresponding to out_order_no, and then the transaction information table is obtained according to step 4 to obtain the transaction information table, and then the transaction information table is returned to the calling party.

[0018] In the technical solution, m is 4, n is 16, n1 is 16, and n2 is 16.

[0019] The application also provides a device for platform database splitting and transaction primary key sub-table, comprising the following modules:

[0020] Platform type discrimination module: after receiving the consumption transfer transaction message sent by the platform, performing basic verification, and storing the transaction information table payments_xx and the transaction information pairing table payment_mappings_xx through the shardingjdc configured database and sub-table strategy, wherein_xx is a digital variable number, according to the transaction information table payments_xx database splitting strategy configuration, the platform id information root_mch_id in the consumption transfer transaction message is judged to determine whether the platform is a large transaction platform configured;

[0021] Large transaction platform database splitting module: if the platform is a large transaction platform, the large transaction platform uses m databases as independent large platform databases according to the configuration;

[0022] Normal platform database splitting module: if the platform is not a large transaction platform, the corresponding database is obtained according to the platform root_mch_id / n modulo operation, and n databases are shared by all platforms that are not large data transaction platforms;

[0023] Transaction information table sub-table module: after the transaction database is split, the corresponding sub-table is obtained by using the transaction primary key payment_id / n1 modulo operation according to the transaction information table payments_xx sub-table strategy configuration, and each database has n1 sub-tables payments_0, payments_1, …, payments_xx, …, payments_n1-l;

[0024] Transaction information input module: write the transaction information in the consumption transfer transaction message into the corresponding payments_xx;

[0025] The transaction pairing table sub-table module: in the same database determined by the common platform sub-database module, a transaction pairing table paymentmappings_xx based on transaction order number is stored, the pairing table only stores the primary key out_order_no transaction order number, payment_id transaction primary key id, root_mch_id platform ID, and according to the transaction pairing table payment_mappings_xx sub-table strategy configuration, the corresponding sub-table is obtained by using the transaction order number out_order_no.hashcode / n2 modulo operation, each database has n2 sub-tables payment_mappings_0,..., payment_mappings_xx,..., payment_mappings_n2-1, wherein out_order_no.hashcode is an operation on a string to obtain a numerical value;

[0026] The transaction pairing information falling table module: the transaction pairing information in the consumption transfer transaction message is fallen into the table payment_mappings_xx;

[0027] The accounting state updating module: the transaction information and the transaction pairing information have been fallen into the corresponding sub-database and sub-table according to the configuration, the transaction information is fallen into the table payments_xx, the transaction pairing information is fallen into the table payment_mappings_xx, the transaction is sent to the account module for accounting, and after the accounting succeeds or fails, the accounting result is updated to the status field of the transaction information table payments_xx, and when updating, the corresponding sub-database and sub-table are found according to steps 1, 2, 3 and 4, and then the updating is performed;

[0028] The calling module: the platform party can query the transaction final state through the transaction order number, the corresponding sub-table is obtained by using the transaction order number out_order_no.hashcode / n2 modulo operation after the sub-database is obtained according to steps 1, 2 and 3, the payment_mappings data is obtained to obtain the payment_id corresponding to out_order_no, the transaction information table sub-table is obtained according to step 4, and then the consumption transfer transaction information is obtained, and then the calling party is returned.

[0029] In the above technical solution, m is 4, n is 16, n1 is 16, and n2 is 16.

[0030] The application also provides a storage medium, and when a processor executes a program, a platform sub-database and transaction primary key sub-table method according to any one of claims 1-2 is realized.

[0031] Because the application adopts the above technical solution, the following beneficial effects are obtained:

[0032] 1. This invention is based on sharding JDBC database partitioning and table partitioning, which can support 3 billion data volumes, and can support 2 million data volumes per day for 4 years.

[0033] 2. This invention is based on platform-based database sharding, and can obtain a shared database or a designated dedicated database by taking the modulo operation based on the platform ID. Dedicated databases can prevent uneven resource distribution across different databases caused by platforms with large data volumes, and can individually designate one or more platforms with large data volumes to occupy a specific dedicated database.

[0034] 3. This invention is based on platform database sharding, and then on transaction table sharding. Furthermore, it supports query operations based on two dimensions: transaction primary key and transaction order number.

[0035] 4. This proposal is based on a sharding JDBC database partitioning strategy that can be configured according to the volume of business on different platforms (channels), and a table partitioning strategy based on two business dimensions: transaction primary key and transaction order number. Through this configurable approach, real-time storage and querying of large volumes of transactions can be easily achieved. Attached Figure Description

[0036] Figure 1 Flowchart for creating database sharding and table partitioning for transactions;

[0037] Figure 2 Flowchart for querying transaction order numbers. Detailed Implementation

[0038] The embodiments of the present invention will be described in detail below. Although the present invention will be described and illustrated in conjunction with some specific embodiments, it should be noted that the present invention is not limited to these embodiments. On the contrary, any modifications or equivalent substitutions made to the present invention should be covered within the scope of the claims of the present invention.

[0039] Furthermore, to better illustrate the present invention, numerous specific details are set forth in the following detailed embodiments. Those skilled in the art will understand that the present invention can be practiced without these specific details.

[0040] A method for platform database sharding and transaction primary key table partitioning, characterized by the following steps:

[0041] Step 1: After receiving the consumption transfer transaction message sent by the platform, basic verification is performed, and the transaction information table payments_xx and the transaction information pairing table payment_mappings_xx are stored through the shardingjdc configured database and table splitting strategy, wherein_xx is a numerical variable number. According to the database splitting strategy configuration of the transaction information table payments_xx, the platform id information root_mchid in the consumption transfer transaction message is used to determine whether the platform is a large transaction volume platform configured.

[0042] Step 2: According to step 1, if the platform is a large transaction volume platform, the large transaction volume platform uses the specified exclusive database trade61-trade65, m databases as independent use of the large platform.

[0043] Step 3: According to step 1, if the platform is not a large transaction volume platform, the corresponding database trade01-trade16 is obtained by performing a modulo operation on the platform root_mch_id / 16, and the 16 databases are shared by all platforms that are not large data volume transactions.

[0044] Step 4: According to steps 2 and 3, after obtaining the database of the transaction, the corresponding sub-table is obtained by performing a modulo operation on the transaction key payment_id / 16 in the consumption transfer transaction message according to the table splitting strategy configuration of the transaction information table payments_xx, and each database has 16 sub-tables payments_0, payments_1, …, payments_xx, … payments_15.

[0045] Step 5: According to step 4, the transaction information in the consumption transfer transaction message is written into the corresponding payments_xx.

[0046] Step 6: According to step 3, in the same database determined in step 3, a transaction pairing table payment_mappings_xx is stored based on the transaction order number, which only stores the primary key out_order_no transaction order number, payment_id transaction primary key id, and root_mch_id platform ID. According to the table splitting strategy configuration of the transaction pairing table payment_mappings_xx, the corresponding sub-table is obtained by performing a modulo operation on the transaction order number out_order_no.hashcode / 16, and each database has 16 sub-tables payment_mappings_0, …, payment_mappings_xx, … payment_mappings_15, wherein out_order_no.hashcode is a numerical value obtained by operating on a string.

[0047] Step 7: The transaction pairing information in the consumer transfer transaction message is written into the table payment_mappings_xx.

[0048] Step 8: After step 7, the transaction information and the transaction pairing information have been written into the corresponding sub-database and sub-table according to the configuration. The transaction information is written into the table payments_xx, and the transaction pairing information is written into the table payment_mappings_xx. The transaction is then sent to the account module for accounting. After the accounting is successful or fails, the accounting result is updated to the status field in the transaction information table payments_xx. When updating, the corresponding sub-database and sub-table are found according to steps 1, 2, 3, 4, and then the update is performed.

[0049] Step 9: After step 8, the platform can query the transaction final state by the transaction order number. After obtaining the sub-database according to steps 1, 2, 3, the corresponding sub-table is obtained using the transaction order number out_order_no.hashcode / 16 modulo operation (16 sub-tables per database payment_mappings_0…payment_mappings_15). The payment_id corresponding to out_order_no is obtained by obtaining the payment_mappings data. Then, the payments sub-table is obtained according to step 4, and the consumer transfer transaction information is obtained, and then returned to the calling party.

Claims

1. A method for platform database sharding and transaction primary key table partitioning, characterized in that, Includes the following steps: Step 1: After receiving the consumption transfer transaction message from the platform and performing basic verification, the transaction information table payments_xx and the transaction information matching table payment_mappings_xx are stored through the database sharding and table partitioning strategy configured in shardingjdc. Here, _xx is a numeric variable sequence number. Based on the database sharding strategy configuration of the payment information table payment_xx, the platform ID information root_mch_id in the consumption transfer transaction message is checked to determine whether the platform is a configured high-transaction-volume platform. Step 2: Following Step 1, if the platform is configured as a high-volume trading platform, the high-volume trading platform will be assigned a dedicated database according to the configuration, and m databases will be used independently by the high-volume platform. Step 3: Following Step 1, if the platform is not configured as a large transaction volume platform, the corresponding database shards are obtained by taking the modulo operation of the platform's root_mch_id / n. The n database shards are shared by all platforms that do not have large transaction volumes. Step 4: Following Step 2 and Step 3, after obtaining the transaction database partitions, use the transaction primary key payment_id / n1 in the consumption transfer transaction message to perform a modulo operation to obtain the corresponding partitions based on the partitioning strategy configuration of the payment_xx transaction information table. Each database has n1 partitions: payment_0, payments_1, ..., payments_xx, ..., payments_n1-1. Step 5: Following step 4, write the transaction information from the consumption transfer transaction report to the corresponding payments_xx; Step 6: Following Step 3, in the same database determined in Step 3, another transaction pairing table, payment_mappings_xx, should be stored, with the transaction order number as the basis for table partitioning. This pairing table only stores the primary key out_order_no (transaction order number), payment_id (transaction primary key ID), and root_mch_id (platform ID). According to the table partitioning strategy configuration of the transaction pairing table payment_mappings_xx, the corresponding partition is obtained by performing a modulo operation on the transaction order number out_order_no.hashcode / n2. Each database has n2 partitioned tables payment_mappings_0, ..., payment_mappings_xx, ..., payment_mappings_n2-1, where out_order_no.hashcode is a value obtained by performing an operation on a string. Step 7: Next, store the transaction pairing information from the consumption transfer transaction message in the payment_mappings_xx table; Step 8: Following Step 7, the transaction information and transaction pairing information have now been assigned to the corresponding database shards and tables according to the configuration. The transaction information is assigned to the table payments_xx, and the transaction pairing information is assigned to the table payments_mappings_xx. The transaction is then sent to the account module for accounting. After the accounting is successful or unsuccessful, the accounting result is updated to the status field in the transaction information table payments_xx. During the update, the corresponding database shard and table are found according to Steps 1, 2, 3, and 4, and then updated. Step 9: Following Step 8, the platform can query the final status of a transaction through the transaction order number. After obtaining the database shards according to Steps 1, 2, and 3, the platform uses the transaction order number out_order_no.hashcode / n2 modulo operation to obtain the corresponding sharded table. It then obtains the payment_mappings data to get the payment_id corresponding to out_order_no. Based on the transaction information table shards obtained in Step 4, it obtains the consumption transfer transaction information and returns it to the caller.

2. The method for platform database sharding and transaction primary key table partitioning according to claim 1, characterized in that, m takes the value 4, n takes the value 16, n1 takes the value 16, and n2 takes the value 16.

3. An apparatus for platform database sharding and transaction primary key table partitioning, characterized in that... Includes the following modules: Platform type identification module: After receiving the consumption transfer transaction message from the platform, it performs basic verification and stores the transaction information table payments_xx and the transaction information matching table payment_mappings_xx through the database sharding and table partitioning strategy configured in shardingjdc. Here, _xx is a numeric variable sequence number. Based on the database sharding strategy configuration of the transaction information table payments_xx, it judges whether the platform is the configured high-transaction-volume platform by checking the platform ID information root_mch_id in the consumption transfer transaction message. Large transaction volume platform database sharding module: If the platform is configured as a large transaction volume platform, the large transaction volume platform will be assigned a dedicated database according to the configuration, and m databases will be used independently by the large platform; Normal platform database sharding module: If the platform is not configured as a high-volume trading platform, the corresponding database shards are obtained by taking the modulo operation of the platform's root_mch_id / n, and the n database shards are shared by all platforms that do not have large-volume trading. Transaction information table sharding module: After the transaction is sharded, the corresponding sharding is obtained by taking the transaction primary key payment_id / n1 in the consumption transfer transaction message according to the sharding strategy configuration of the payment_xx Transaction Information Table. Each database has n1 sharding tables payment_0, payments_1, ..., payments_xx, ... payments_n1-1; Transaction information entry module: Writes the transaction information from the consumption transfer transaction report to the corresponding payments_xx; Transaction pairing table sharding module: In the same database determined by the ordinary platform database sharding module, a transaction pairing table payment_mappings_xx is stored, which is based on the transaction order number. This pairing table only stores the primary key out_order_no (transaction order number), payment_id (transaction primary key ID), and root_mch_id (platform ID). According to the sharding strategy configuration of the transaction pairing table payment_mappings_xx, the corresponding sharding table is obtained by taking the modulo operation of the transaction order number out_order_no.hashcode / n2. Each database has n2 sharding tables payment_mappings_0, ..., payment_mappings_xx, ..., payment_mappings_n2-1, where out_order_no.hashcode is a value obtained by performing an operation on a string. The transaction matching information table module: This module stores the transaction matching information from the consumption transfer transaction message in the table payment_mappings_xx. Accounting Status Update Module: Transaction information and transaction pairing information have been assigned to the corresponding database shards and tables according to the configuration. Transaction information is assigned to the table payments_xx, and transaction pairing information is assigned to the table payments_mappings_xx. The transaction is then sent to the account module for accounting. After successful or failed accounting, the accounting result is updated to the status field in the transaction information table payments_xx. During the update, the corresponding database shard and table are found based on the platform type discrimination module, the database sharding module for high-volume platforms, the database sharding module for ordinary platforms, and the transaction information table sharding module, and then updated. Calling Module: The platform can query the final status of a transaction through the transaction order number. After obtaining the database shards based on the platform type discrimination module, the large transaction volume platform sharding module, and the ordinary platform sharding module, the corresponding sharded table is obtained by performing a modulo operation on the transaction order number out_order_no.hashcode / n2. The payment_mappings data is obtained to get the payment_id corresponding to out_order_no. Then, the sharded table of the transaction information table is obtained based on the transaction information table sharding module, and finally the consumption transfer transaction information is obtained and returned to the caller.

4. The apparatus for platform database sharding and transaction primary key table partitioning according to claim 3, characterized in that, m takes the value 4, n takes the value 16, n1 takes the value 16, and n2 takes the value 16.

5. A storage medium, characterized in that, When the processor executes the program, it implements a method for platform database sharding and transaction primary key table partitioning as described in any one of claims 1-2.

Images