Migration method for system data horizontal expansion

Abstract

The invention belongs to the technical field of data migration, and discloses a migration method for horizontal expansion of system data, which comprises the following steps: determining a data table to be migrated, configuring migration parameters, and setting a routing algorithm based on the migration parameters; synchronously updating the incremental data into the to-be-migrated data table and the corresponding sub-data table based on a routing algorithm; based on a routing algorithm, migrating stock data of the data table to be migrated to the corresponding sub-data table; switching the read request to read the sub-data table, and performing read flow verification; after verification is correct, flow reading of the to-be-migrated data table is closed, and migration is completed. According to the method, data horizontal migration can be realized without modifying service codes, rapid migration of incremental data and stock data is realized through a routing algorithm, and particularly synchronous updating of the incremental data in the to-be-migrated data table and the migrated sub-data table is realized, so that the research and development efficiency and the data horizontal expansion efficiency are improved, and the data accuracy and the system stability are ensured.

Description

Technical Field

[0001] The present invention relates to the technical field of data migration, and particularly relates to a migration method for horizontal expansion of system data. Background Art

[0002] In the process of the rapid development of the Internet, as the company scale continues to expand and the business system continues to be upgraded, business data will rapidly expand. For example, as the number of orders increases, the order data volume will rapidly grow from tens of thousands to millions or even tens of millions. At this time, the database performance will decline rapidly, seriously affecting user order placement and system stability. Therefore, it is necessary to horizontally split the data of large tables for expansion. However, after horizontal splitting, data sharding routing and data migration will inevitably occur.

[0003] In order to provide a better experience for users, non-stop migration becomes very crucial. Traditional solutions usually require modifying business code and adding additional data synchronization logic. Existing technologies are difficult to ensure transaction consistency during dual writing, lack a flexible configuration mechanism and error handling strategy, and have a relatively large development investment cost. Summary of the Invention

[0004] The present invention provides a migration method for horizontal expansion of system data to at least solve the above technical problems.

[0005] To achieve the above object, the technical solution adopted by the present invention is as follows: A migration method for horizontal expansion of system data includes the following steps: S1. Determine the data table to be migrated, configure migration parameters, and set a routing algorithm based on the migration parameters. The migration parameters include the routing key of the data table to be migrated and the number of corresponding sub-data tables after migration of the data table to be migrated; S2. Based on the routing algorithm, synchronize and update the incremental data to the data table to be migrated and the corresponding sub-data tables; S3. Based on the routing algorithm, migrate the stock data of the data table to be migrated to the corresponding sub-data tables; S4. Switch the read request to the read sub-data table for read traffic verification; S5. After verification, close the traffic reading of the data table to be migrated to complete the migration.

[0006] Further, the routing key is the business primary key of the data table to be migrated.

[0007] Further, the routing algorithm includes: S11. Set the number of sub-data tables to 2 n , and the serial numbers of the sub-data tables are 0 to (2 nAn integer of -1); S12. Obtain the business primary key of each row of data in the data table to be migrated, and extract the string except the business prefix of the business primary key as a substring; S13. Convert the substring into a hash value; S14. Perform an unsigned right shift of 16 bits on the hash value to obtain a right shift value; S15. Perform a bitwise AND operation on the right shift value and 2 n -1 to obtain the serial number of the sub-data table after the corresponding row of data is migrated.

[0008] Further, the S2 includes: S21. Intercept the original sql of the incremental data; S22. Dynamically generate a new table sql according to the original sql; S23. Execute the original sql to update the incremental data into the data table to be migrated, execute the new table sql, obtain the serial number of the sub-data table after the incremental data is migrated according to the routing algorithm, and update the incremental data into the corresponding sub-data table.

[0009] Further, in S23, the original sql and the new table sql are executed in the same transaction.

[0010] Further, the S3 includes: S31. Read the stock data of the data table to be migrated in batches; S32. Obtain the serial number of the sub-data table after each batch of stock data is migrated according to the routing algorithm; S33. Migrate each batch of stock data into the corresponding sub-data table.

[0011] Further, in S33, if the migration of each batch of stock data fails, change it to migrate each piece of stock data in each batch. If the migration of one piece of stock data fails, skip it and migrate the next piece of stock data.

[0012] Further, in S3, multiple threads are used to perform parallel migration on multiple batches of stock data.

[0013] Further, the S4 includes: S41. Switch the read request to the read sub-data table; S42. Verify whether the data reading is normal in the new system storing the sub-data table; S43. If the data reading is normal, verify whether the key indicators in the new system are normal.

[0014] Further, the verification of the normal key indicators includes: the request response time of 99% of the requests in the new system is less than the request response time of the original system storing the data table to be migrated; the timeout rate of the new system is less than the timeout rate of the original system; the throughput of the new system is less than the timeout rate of the throughput.

[0015] Compared with the prior art, the present invention has the following beneficial effects: The present invention can achieve horizontal data migration without modifying business code. Specifically, it realizes the rapid migration of incremental data and stock data through a routing algorithm. In particular, the synchronous update of incremental data in the data table to be migrated and the sub-data table after migration greatly improves the R & D efficiency and the horizontal expansion efficiency of data, while ensuring data accuracy and system stability. Brief Description of the Drawings

[0016] Figure 1 It is a flowchart of the method of the present invention. Detailed Embodiments

[0017] In order to make the objectives, technical solutions and advantages of the present invention clearer, the present invention will be further described in detail below with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all of the embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the scope of protection of the present invention.

[0018] As Figure 1 shown, a migration method for horizontal expansion of system data provided by the present invention includes the following steps: S1. Determine the data table to be migrated in the original system (i.e., the original database), configure migration parameters, and set a routing algorithm based on the migration parameters. The migration parameters include the routing key of the data table to be migrated and the number of corresponding sub-data tables after migration of the data table to be migrated; S2. Based on the routing algorithm, synchronously update the incremental data to the data table to be migrated and the corresponding sub-data tables; S3. Based on the routing algorithm, migrate the stock data of the data table to be migrated to the corresponding sub-data tables; S4. Switch the read request to the read sub-data table for read traffic verification; S5. After verification is correct, close the traffic reading of the data table to be migrated to complete the migration.

[0019] The present invention first configures migration parameters through the determined data table to be migrated, then sets a routing algorithm based on the migration parameters, and subsequently uses the routing algorithm to migrate incremental data and stock data to the corresponding sub-data tables. Finally, read traffic verification is performed on the new system after data migration. The present invention can greatly improve the R & D efficiency and the horizontal expansion efficiency of data without modifying business code, while ensuring data accuracy and system stability.

[0020] In S1 of the present invention, the migration parameters include the routing key of the data table to be migrated and the number of corresponding sub-data tables after migration of the data table to be migrated. The routing key is the business primary key of the data table to be migrated, and it is avoided to use an incremental id to prevent data hotspots and uneven distribution. And according to the business characteristics and the data growth trend, the number of sub-data tables after migration is set, preferably 2n pieces, which is convenient for subsequent routing algorithms. Then, set the routing algorithm according to the migration parameters. The routing algorithm is used to route the data in the data table to be migrated to the corresponding sub-data tables, specifically including: S11. Set the number of sub-data tables to 2 n , and the serial numbers of the sub-data tables are integers from 0 to (2 n -1); S12. Obtain the business primary key of each row of data in the data table to be migrated, and extract the string except the business prefix of the business primary key as the substring; S13. Convert the substring into a hash value to scatter the data; S14. Perform an unsigned right shift of 16 bits on the hash value to obtain the right shift value, further scatter the data, remove the high-order information, and retain the low-order information; S15. Perform a bitwise AND operation on the right shift value and 2 n -1, the purpose is to further scatter the bit distribution of the data, increase randomness, and obtain the serial number of the sub-data table after the corresponding row of data is migrated. The serial number of the sub-data table is limited within the range of 0-63, thus forming a sharding route, which is equivalent to the modulo % operation, but with higher performance. Compared with the traditional algorithm code%32, the routing algorithm of the present invention has a higher data dispersion, avoiding data skew caused by too much single-piece data; the sharding calculation is faster, and the bit operation is faster than the remainder-taking method.

[0021] The following is a specific embodiment of the routing algorithm.

[0022] First, set the number of sub-data tables to 2 6 = 64, the serial numbers of the sub-data tables range from 0 to 63, and the table names of the sub-data tables are sku_0, sku_1,..., sku_63. Obtain the business primary key of each row of data in the data table to be migrated. The business primary key of one row of data is SKU_20250123111912345123456. Since the first 4 bits are the same business prefix and have no discrimination, the middle 17 bits are the timestamp, and the last 6 bits are the self-increment sequence. Therefore, remove the non-discriminatory business prefix and extract the substring 20250123111912345123456. Then convert the substring 20250123111912345123456 into a hash value; then perform an unsigned right shift of 16 bits on the converted hash value to obtain the right shift value; finally, perform a bitwise AND operation on the right shift value and 63 to obtain the serial number 25 of the sub-data table after the corresponding row of data is migrated, that is, the data with the business primary key SKU_20250123111912345123456 should be migrated to the sub-data table sku_25.

[0023] In the present invention, S2 is to synchronously update the incremental data to the data table to be migrated and the corresponding sub - data tables based on the routing algorithm, that is, to complete the incremental dual - writing. Since the table structures are exactly the same when a single table is horizontally extended and no data model conversion is involved, it only needs to intercept the sql of the incremental data, generate an identical sql and execute it to update the incremental data to the corresponding sub - data tables. Specifically, it includes: S21. Enable the interceptor at the database object - relational mapping layer to intercept the original sql of the incremental data; S22. Dynamically generate the sql of the new table according to the original sql; S23. Execute the original sql to update the incremental data to the data table to be migrated, execute the new table sql, obtain the serial number of the sub - data table after the incremental data migration according to the routing algorithm, and update the incremental data to the corresponding sub - data tables.

[0024] Preferably, in S23, the original sql and the new table sql are executed in the same transaction. Executing the original sql and the new table sql in the same transaction ensures that they either succeed together or fail together, so as to guarantee the accuracy of the incremental data. For the scenario of new data addition, both the old table of the data table to be migrated and the new table of the sub - data table are newly added simultaneously; for the scenario of data update or deletion, if the old table is updated or deleted, and if there is data in the new table, it is also updated or deleted correspondingly. If not, the update or deletion of the new table is an empty execution, which is also considered successful. In particular, since the original sql and the new table sql of the old table and the new table are executed in the same transaction, during the execution process, as long as one sql throws an exception, the other sql will also roll back.

[0025] In the present invention, S3 is to migrate the stock data of the data table to be migrated to the corresponding sub - data tables based on the routing algorithm. Specifically, it includes: S31. Read the stock data of the data table to be migrated in batches; S32. Obtain the serial number of the sub - data table after the migration of each piece of stock data in each batch according to the routing algorithm; S33. Migrate each batch of stock data to the corresponding sub - data tables.

[0026] Preferably, in S33, if the migration of each batch of stock data fails, it is changed to migrate each piece of stock data in each batch. If the migration of a certain piece of stock data fails, it is skipped and the next piece of stock data is migrated. When a certain piece of data has been written into the new system (i.e., the new database) through the incremental dual - writing method, it cannot be migrated at this time, and it is changed to single - piece data migration. The data that fails to migrate is skipped. The present invention uses the business primary key to ensure that data is not regenerated repeatedly.

[0027] Preferably, in S3, multiple threads are used to migrate the stock data of multiple batches in parallel to improve the migration efficiency.

[0028] In the present invention, S4 is to switch the read request to the read sub-data table for read traffic verification to achieve a smooth switch between the old and new systems and ensure system stability. Specifically, it includes: S41, switching the read request to the read sub-data table; S42, verifying whether data reading is normal in the new system storing the sub-data table; S43, if the data reading is normal, verifying whether the key indicators in the new system are normal. The verification of the normal key indicators includes: the request response time of 99% of the requests in the new system is less than the request response time of the original system storing the data table to be migrated; the timeout rate of the new system is less than the timeout rate of the original system; the throughput of the new system is less than the timeout rate of the throughput. When both the data reading and the key indicators are normal, close the traffic reading of the migrated data table in the original system to complete the migration.

[0029] Finally, it should be noted that: the above embodiments are only preferred embodiments of the present invention to illustrate the technical solutions of the present invention, rather than limiting it, and certainly not limiting the patent scope of the present invention; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that: they can still modify the technical solutions recorded in the foregoing embodiments, or perform equivalent replacements on some or all of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the scope of the technical solutions of the embodiments of the present invention; that is to say, any meaningless changes or polish made in the main design idea and spirit of the present invention, as long as the technical problems solved are still the same as those of the present invention, should be included in the protection scope of the present invention; in addition, directly or indirectly applying the technical solutions of the present invention to other related technical fields shall also be included in the patent protection scope of the present invention by the same token.

Claims

1. A migration method for horizontal expansion of system data, characterized in that It includes the following steps: S1. Determine the data table to be migrated, configure the migration parameters, and set the routing algorithm based on the migration parameters. The migration parameters include the routing key of the data table to be migrated and the number of corresponding sub-data tables after the migration of the data table to be migrated; S2. Based on the routing algorithm, synchronously update the incremental data to the data table to be migrated and the corresponding sub-data tables; S3. Based on the routing algorithm, migrate the stock data of the data table to be migrated to the corresponding sub-data tables; S4. Switch the read request to the read sub-data table for read traffic verification; S5. After verification is correct, close the traffic reading of the data table to be migrated to complete the migration.

2. The migration method for horizontal expansion of system data according to claim 1, characterized in that The routing key is the business primary key of the data table to be migrated.

3. The migration method for horizontal expansion of system data according to claim 2, characterized in that, The routing algorithm includes: S11. Set the number of sub-data tables to 2 n , and the serial numbers of the sub-data tables are integers from 0 to (2 n -1); S12. Obtain the business primary key of each row of data in the data table to be migrated, and extract the string except the business prefix of the business primary key as the substring; S13. Convert the substring into a hash value; S14. Perform an unsigned right shift of 16 bits on the hash value to obtain the right shift value; S15. Perform a bitwise AND operation on the right shift value and 2 n -1 to obtain the serial number of the sub-data table after the corresponding row of data is migrated.

4. A migration method for horizontal expansion of system data according to claim 3, characterized in that, S2 includes: S21. Intercept the original sql of the incremental data; S22. Dynamically generate a new table sql according to the original sql; S23. Execute the original sql to update the incremental data to the data table to be migrated, execute the new table sql, obtain the serial number of the sub-data table after the migration of the incremental data according to the routing algorithm, and update the incremental data to the corresponding sub-data table.

5. A migration method for horizontal expansion of system data according to claim 4, characterized in that In S23, the original sql and the new table sql are executed in the same transaction.

6. A migration method for horizontal expansion of system data according to claim 3, characterized in that S3 includes: S31. Read the stock data of the data table to be migrated in batches; S32. Obtain the serial number of the sub-data table after the migration of each batch of stock data according to the routing algorithm; S33. Migrate each batch of stock data to the corresponding sub-data table.

7. A migration method for horizontal expansion of system data according to claim 6, characterized in that In S33, if the migration of each batch of stock data fails, change it to migrate each piece of stock data in each batch. If the migration of one piece of stock data fails, skip it and migrate the next piece of stock data.

8. A migration method for horizontal expansion of system data according to claim 6, characterized in that, In S3, multiple threads are used to perform parallel migration of multiple batches of stock data.

9. A migration method for horizontal expansion of system data according to claim 1, characterized in that S4 includes: S41. Switch the read request to the read sub-data table; S42. Verify whether the data reading is normal in the new system storing the sub-data table; S43. If the data reading is normal, verify whether the key indicators in the new system are normal.

10. A migration method for horizontal expansion of system data according to claim 9, characterized in that, The verification of the normal key indicators includes: the request response time of 99% of the requests in the new system is less than the request response time of the original system storing the data table to be migrated; the timeout rate of the new system is less than the timeout rate of the original system; the throughput of the new system is less than the timeout rate of the throughput.

Images