Method, device and electronic equipment for online change of a fragmentation scheme

Abstract

The application relates to the computer technical field and discloses a method, device and electronic equipment for changing a sharding scheme online. The method for changing the sharding scheme online comprises the following steps: creating a mirror table of an original table by using a new sharding scheme and creating a trigger from the mirror table to the original table; redistributing existing data of the original table to the mirror table; switching a service to the mirror table and updating original data information. The device for changing the sharding scheme online comprises the following modules: a metadata processing module for processing table metadata information, a metadata lock and metadata persistence; a data redistribution module for copying existing data of an original table to a mirror table; and a service switching module for switching a service to the mirror table and updating original data information so as to use a new sharding scheme. The technical scheme of the application can change the sharding scheme of a table without affecting a service, can greatly reduce operation and maintenance costs and distributed data use efficiency, can shorten a sharding change window, and has a better effect.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and more specifically to a method, apparatus, and electronic device for online modification of a sharding scheme. Background Technology

[0002] Currently, many distributed relational data systems lack online solutions for modifying sharding schemes. Specifically, when business operations change or the scale of operations expands or shrinks, adjustments to the sharding scheme are often necessary; existing technologies require downtime and offline manual processing, which can disrupt normal business operations. Therefore, this invention proposes an online modification solution that is seamless for the business, ensuring uninterrupted and unblocked operations. Summary of the Invention

[0003] This application provides a method, apparatus, and electronic device for online modification of a sharding scheme to solve the aforementioned technical problems in the prior art.

[0004] According to one aspect of this application, one embodiment provides a method for online modification of a fragmentation scheme, comprising:

[0005] A mirror table of the original table is created using the new sharding scheme, and a trigger is created to link the mirror table to the original table. The new sharding scheme is different from the sharding scheme used by the original database.

[0006] Redistribute the existing data of the original table to the mirror table;

[0007] Switch the service to the mirror table and update the original data information.

[0008] In one embodiment, the step of creating a mirror table of the original table using a new sharding scheme and creating a trigger to mirror the original table includes:

[0009] Use the new sharding scheme to create a mirror table identical to the original table;

[0010] The mirror table is added to the original data information as a new table.

[0011] The trigger is created on the original table, and the new row data write operation of the original table is replayed on the mirror table to keep the new data of the mirror table synchronized with the original table.

[0012] In one embodiment, the redistribution of existing data from the original table to the mirror table includes:

[0013] Select an appropriate primary key or unique key as the splitting field;

[0014] The data is split into N intervals according to the splitting field, where N is the same as the specified redistribution concurrency number and N is a positive integer;

[0015] N threads perform data redistribution processing simultaneously, where each redistribution thread corresponds to a data range.

[0016] In one embodiment, each of the redistribution threads includes:

[0017] Start a transaction;

[0018] Scan the original table data based on the selected split key, scanning one batch size at a time;

[0019] Each row of data is routed and inserted into the mirror table according to the new sharding scheme;

[0020] Commit transaction;

[0021] Begin scanning the next batch, and repeat this process until the end of the interval.

[0022] In one embodiment, the binlog of all the original tables during the data redistribution phase is replayed on the mirror table until the replication is caught up.

[0023] In one embodiment, switching services to the mirror table and updating the original data information includes:

[0024] Acquire the table's metadata lock;

[0025] Initiate table switching and delete the triggers for the mirrored table;

[0026] After the switch is complete, release the metadata lock.

[0027] In one embodiment, the method for online modification of the sharding scheme further includes pre-detection and parameter verification; and / or,

[0028] The method for online modification of the sharding scheme also includes performing data consistency checks after redistribution;

[0029] In the pre-detection and parameter verification, the pre-detection includes detecting whether the preconditions for online change are met, and the parameter verification includes detecting the rationality of the input parameters.

[0030] The data consistency detection includes:

[0031] The original table is divided into large intervals using the splitting field selected during the redistribution phase.

[0032] Calculate the sum of the CRC32 values ​​of all rows of data within the interval: SUMcrc32;

[0033] Compare the SUMcrc32 of the original table and the mirror table to determine if the data in a given interval is consistent on both sides. If all intervals are consistent, the data in the two tables is considered consistent. If there are inconsistencies, the inconsistent intervals are further split, and all sub-intervals are compared for consistency. This splitting is repeated until the interval is smaller than a preset size, and the specific inconsistent row is located. If there are inconsistent intervals, but they are consistent in sub-interval or row-level comparisons, they are still considered consistent. If an inconsistent row is located, the check is repeated. If the inconsistency persists after multiple checks, the data in the two tables is considered inconsistent. In this case, the process of table switching is prohibited due to data inconsistency, and the metadata information is updated, awaiting manual processing.

[0034] According to one aspect of this application, one embodiment provides an apparatus for online modification of a fragmentation scheme, comprising:

[0035] The metadata processing module is used to process table metadata information, metadata locks, and metadata persistence;

[0036] The data redistribution module is used to copy existing data from the original table to the mirror table; and

[0037] The service switching module is used to switch services to the mirror table and update the original data information to use the new sharding scheme.

[0038] According to one aspect of this application, one embodiment provides an electronic device, including:

[0039] Memory; and

[0040] processor;

[0041] The memory is used to store one or more computer instructions; the one or more computer instructions are executed by the processor to implement the method for online modification of the sharding scheme as described above.

[0042] According to one aspect of this application, one embodiment provides a readable storage medium storing computer instructions; wherein, when the computer instructions are executed by a processor, they implement the method for online modification of the sharding scheme as described in any of the above claims.

[0043] The embodiments described above in this application enable automatic changes to the sharding scheme, significantly reducing manual maintenance costs. Furthermore, the sharding scheme can be changed online without downtime, better meeting the needs of modern business characteristics. Online changes ensure uninterrupted, unblocked, and seamless business operations, without affecting normal business processes. In summary, the online sharding scheme change technology of this invention, which modifies the table's sharding scheme without impacting business operations, significantly reduces maintenance costs and improves distributed data utilization efficiency, shortens the sharding change window, and achieves superior results. Attached Figure Description

[0044] To more clearly illustrate the specific embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the specific embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0045] Figure 1 This is a flowchart illustrating a method for online modification of a sharding scheme in one embodiment;

[0046] Figure 2 This is a schematic diagram of the structure of a device for online modification of the segmentation scheme in one embodiment. Detailed Implementation

[0047] It should be noted that, unless otherwise specified, the embodiments and features described in this application can be combined with each other. This application will now be described in detail with reference to the accompanying drawings and embodiments.

[0048] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are merely some, not all, of the embodiments of the present application. All other embodiments obtained by those skilled in the art based on the embodiments of the present application without creative effort should fall within the scope of protection of the present application.

[0049] It should be noted that the terms "first," "second," etc., used in this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be used interchangeably where appropriate for the embodiments of this application described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0050] It should be understood that when an element (such as a layer, film, region, or substrate) is described as being "on" another element, the element may be directly on the other element, or there may be an intermediate element present. Moreover, in this application, when an element is described as being "connected" to another element, the element may be "directly connected" to the other element, or "connected" to the other element via a third element.

[0051] Example 1:

[0052] Please refer to Figure 1 One embodiment provides a method for online modification of a sharding scheme, comprising the following steps:

[0053] S1. Pre-detection and parameter verification, wherein the pre-detection includes detecting whether the preconditions for online changes are met, and the parameter verification includes detecting the rationality of the input parameters. Further, regarding the preconditions, the preconditions include one or more of foreign keys, triggers, and unique indexes. These can all be obtained by querying table metadata information. Regarding the input parameters, the input parameters include one or both of the data redistribution concurrency and the pause window. Higher concurrency results in faster redistribution, but also consumes more system resources; peak business periods can be set as pause windows to avoid the online change tasks of the sharding scheme preempting business resources.

[0054] S2. Create a mirror table of the original table using the new sharding scheme, and create triggers for the mirror table to synchronize the new data between the mirror table and the original table. Preferably, the triggers include one or more of INSERT triggers, UPDATE triggers, and DELETE triggers. The new sharding scheme is different from the sharding scheme used in the original database; it is an alternative sharding scheme adapted to the changed business requirements, and can be selected from existing sharding schemes.

[0055] S3. Redistribute the existing data of the original table to the mirror table. This mainly involves redistributing the existing data of the original table to the mirror table through replication, thereby migrating the old data.

[0056] S4. Data Consistency Check. For data security reasons, after redistribution, the data consistency between the original table and the mirror table is checked.

[0057] S5. Switch the service to the mirror table and update the original data information, that is, use the new sharding scheme.

[0058] The following details each step to facilitate a comprehensive and accurate understanding of the technical solution for online modification of this sharding scheme.

[0059] In one embodiment, step S2 includes the following sub-steps:

[0060] S21. Create a mirror table identical to the original table using the new sharding scheme, and add the mirror table as a new table to the original data information.

[0061] S22. The mirror table is added to the original data information as a new table.

[0062] S23. Create IUD triggers (i.e., INSERT triggers, UPDATE triggers, and DELETE triggers) on the original table. The new row data write operations of the original table are replayed on the mirror table to keep the new data of the mirror table synchronized with the original table.

[0063] In step S2, since the metadata update operation of the design database table needs to acquire a metadata mutex lock, the table DML and DDL operations will be blocked. Since the time is very short, the user will hardly notice it.

[0064] In one embodiment, step S3 includes the following sub-steps:

[0065] S31. Select a suitable primary key or unique key as the splitting field. Preferably, a selection priority can be specified, such as: primary key > unique key, numeric type > string type, low repetition rate > high repetition rate, etc.

[0066] S32. Split the data into N intervals according to the splitting field, where N is the same as the specified redistribution concurrency number and N is a positive integer.

[0067] S33. N threads simultaneously perform data redistribution processing, where one redistribution thread corresponds to one data interval. Operablely, each of the redistribution threads includes the following steps:

[0068] S331. Start a transaction.

[0069] S332. Scan the original table data according to the selected split key, scanning one batch size at a time. For example, this can be specified at the start of the task, such as 10000.

[0070] S333. Insert each row of data into the mirror table according to the new sharding scheme.

[0071] S334. Commit the transaction.

[0072] S335. Begin scanning the next batch, and repeat this process until the end of the interval.

[0073] In the above steps, the entire redistribution is completed when all redistribution threads have finished executing.

[0074] In one embodiment, step S4 includes the following steps:

[0075] S41. Use the splitting field selected during the redistribution phase to divide the original table into large intervals. For example, 500,000 rows per interval.

[0076] S42. Calculate the sum of the CRC32 values ​​(cyclic redundancy check codes) of all rows of data within the interval, SUMcrc32.

[0077] S43. Compare the SUMcrc32 of the original table and the mirror table to determine if the data in a given interval is consistent. If all intervals are consistent, the two tables are considered to have consistent data by default. If intervals are inconsistent, the inconsistent intervals need to be further split, and all sub-intervals need to be compared for consistency. This splitting is repeated until the interval is smaller than a preset size, and the specific inconsistent row is located. If an inconsistent interval exists but is consistent in sub-interval or row-level comparisons, it is still considered consistent. If an inconsistent row is located, it needs to be checked repeatedly to determine if it is not an intermediate state written by the user business. If the inconsistency is still found after multiple checks, the data in the two tables (original table and mirror table) is considered inconsistent. In this case, table switching is prohibited if there is data inconsistency. The metadata information is then updated, and manual processing is awaited. Regarding manual processing, for example, after manual repair, execution can continue or retry can be canceled directly.

[0078] In one embodiment, the trigger-based method for synchronizing incremental data can be replaced with a binlog synchronization method. Specifically, the binlogs of all original tables during the data redistribution phase are replayed on the mirror table until the replication is caught up, and then the table switch is performed.

[0079] In one embodiment, step S5 includes the following sub-steps:

[0080] S51. Acquire the table's metadata lock. This requires first requesting the table's metadata lock, while waiting for the current DDL and DML operations on the table in the distributed database to finish, blocking new DDL and DML requests on the table, until all old DDL and DML operations have been completed, at which point the metadata lock is acquired.

[0081] S52. Then, the table switching is performed, and the triggers of the mirrored table are deleted.

[0082] S53. After the switch is completed, release the metadata lock.

[0083] In step S5, since it is only a table-level lock and the switching time is on the order of seconds, the table switching can be controlled to take place during the off-peak period of business, so it has almost no impact on business.

[0084] The method for online modification of sharding schemes in this invention allows operations and maintenance personnel to change the sharding scheme of a table without downtime or impact on business operations using a single DDL statement or a single operations and maintenance command. This significantly reduces operations and maintenance costs, improves the efficiency of distributed data utilization, and shortens the sharding change window.

[0085] Example 2:

[0086] Please refer to Figure 2Based on the same inventive concept, one embodiment of this application provides an apparatus for online modification of a sharding scheme, comprising:

[0087] 1. Metadata Processing Module 201

[0088] The metadata processing module 201 is used to process table metadata information, metadata locks, and metadata persistence, among other related operations. Specifically, the metadata processing module 201 creates a mirror table of the original table using a new sharding scheme and creates triggers from the mirror table to the original table. For specific operation steps, please refer to Embodiment 1. Regarding the metadata processing module 201, in one embodiment, it includes a processing module used to create a mirror table of the original table using a new sharding scheme and create triggers from the mirror table to the original table; for specific operation steps, please refer to Embodiment 1.

[0089] 2. Data redistribution module 202

[0090] The data redistribution module 202 is used to copy existing data from the original table to the mirror table, that is, to copy old data for migration processing. In other words, the data redistribution module 202 can redistribute the existing data of the original table to the mirror table.

[0091] 3. Data Consistency Detection Module 203

[0092] After data redistribution, the data consistency detection module 203 is mainly responsible for detecting the consistency of the new table data, ensuring data consistency, and avoiding data loss errors.

[0093] 4. Service switching module 204 (not shown in the diagram)

[0094] The service switching module 204 is used to switch services to the mirror table and update the original data information to use the new sharding scheme.

[0095] In one embodiment, the apparatus for online modification of the sharding scheme further includes a preprocessing module 200, which is used for pre-detection and parameter verification. The pre-detection includes detecting whether the preconditions for online modification are met, and the parameter verification includes detecting the rationality of the input parameters. Optionally, the preconditions include one or more of foreign keys, triggers, and unique indexes. Optionally, the input parameters include one or both of the data redistribution concurrency and pause window.

[0096] The modules of the device for online modification of the sharding scheme correspond one-to-one with the steps of the method for online modification of the sharding scheme in the above embodiments, and will not be described again.

[0097] Example 3:

[0098] Based on the same inventive concept, one embodiment of this application provides an electronic device, including: a memory and a processor; wherein, the memory is used to store one or more computer instructions; the one or more computer instructions are executed by the processor using the method for online modification of the sharding scheme described in any of the above embodiments.

[0099] One or more of the aforementioned computer instructions can form a program.

[0100] Example 4:

[0101] Based on the same inventive concept, one embodiment of this application provides a readable storage medium storing computer instructions; wherein, when the computer instructions are executed by a processor, the method for online modification of the sharding scheme described in any of the above embodiments is implemented.

[0102] One or more of the aforementioned computer instructions can form a program.

[0103] The aforementioned program can run on a processor or be stored in memory (or computer-readable medium). Computer-readable medium includes both permanent and non-permanent, removable and non-removable media, and information storage can be achieved by any method or technology. Information can be computer-readable instructions, data structures, program modules, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable medium does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0104] These computer programs may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes can be implemented using different modules, and different steps can be implemented using different modules.

[0105] The specific embodiments of the present invention have been described in detail above, but they are merely examples, and the present invention is not limited to the specific embodiments described above. For those skilled in the art, any equivalent modifications and substitutions to the present invention are also within the scope of the present invention. Therefore, all equivalent changes and modifications made without departing from the spirit and scope of the present invention should be covered within the scope of the present invention.

Claims

1. A method for online modification of a sharding scheme, characterized in that, include: Create a mirror table of the original table using the new sharding scheme, and create a trigger for the mirror table to the original table, wherein the new sharding scheme is different from the sharding scheme used by the original database. Redistribute the existing data of the original table to the mirror table; Switch the service to the mirror table and update the metadata information; The redistribution of existing data from the original table to the mirror table includes: Select the primary key or unique key as the splitting field; The data is split into N intervals according to the splitting field, where N is the same as the specified redistribution concurrency number and N is a positive integer; N threads perform data redistribution processing simultaneously, where one redistribution thread corresponds to one interval; It also includes performing a data consistency check after the redistribution is completed; wherein the data consistency check includes: The original table is divided into large intervals using the splitting field selected during the redistribution phase. Calculate the sum of the CRC32 values ​​of all rows of data within a large interval, SUMcrc32; Compare the SUMcrc32 of the original table and the mirror table to determine if the data in a large interval is consistent on both sides. If all large intervals are consistent, the data in the two tables is consistent. If there are inconsistent large intervals, further split the inconsistent large intervals and compare whether all sub-intervals are consistent. Repeat the splitting until the sub-intervals are smaller than a preset size, and locate the specific inconsistent row. If there are inconsistent large intervals, but they are consistent in their sub-intervals or row-level comparisons, they are still considered consistent. If an inconsistent row is located, repeat the detection. If the inconsistency is still found after multiple detections, it is considered that the data in the two tables is inconsistent. In this case, the table switch is prohibited due to data inconsistency, and the metadata information is updated for manual processing.

2. The method for online modification of a sharding scheme according to claim 1, characterized in that, The trigger for creating a mirror table of the original table using the new sharding scheme and creating the mirror table to the original table includes: Use the new sharding scheme to create a mirror table identical to the original table; The mirror table is added as a new table to the metadata information; The trigger is created on the original table, and the new row data write operation of the original table is replayed on the mirror table to keep the new data of the mirror table synchronized with the original table.

3. The method for online modification of a sharding scheme according to claim 1, characterized in that, Each of the redistribution threads includes: Start a transaction; Scan the original table data based on the selected split field, scanning one batch size at a time; Each row of data is routed and inserted into the mirror table according to the new sharding scheme; Commit transaction; Begin scanning the next batch, and repeat this process until the end of the interval.

4. The method for online modification of a sharding scheme according to claim 1, characterized in that, The binlogs of all the original tables during the data redistribution phase are replayed on the mirror table until the replication is caught up.

5. The method for online modification of a sharding scheme according to claim 1, characterized in that, The process of switching services to the mirror table and updating metadata information includes: Acquire the table's metadata lock; Initiate table switching and delete the triggers for the mirrored table; After the switch is complete, release the metadata lock.

6. A method for online modification of a sharding scheme according to any one of claims 1-5, characterized in that, It also includes pre-detection and parameter verification; In the pre-detection and parameter verification, the pre-detection includes detecting whether the preconditions for online change are met, and the preconditions include one or more of foreign keys, triggers and unique indexes; The parameter validation includes checking the rationality of the input parameters, which include one or both of the data redistribution concurrency and the pause window.

7. A device for online modification of a segmentation scheme, characterized in that, include: The metadata processing module is used to process table metadata information, metadata locks, and metadata persistence; The metadata processing module includes a processing module, which is used to create a mirror table of the original table using a new sharding scheme, and to create a trigger for the mirror table to be mapped to the original table. The new sharding scheme is different from the sharding scheme used by the original database. The data redistribution module is used to copy existing data from the original table to the mirror table. It includes: selecting a primary key or unique key as the splitting field; splitting the data into N intervals according to the splitting field, where N is the same as the specified redistribution concurrency number and N is a positive integer; and having N threads simultaneously perform data redistribution processing, where one redistribution thread corresponds to one interval. The service switching module is used to switch services to the mirror table and update metadata information to use the new sharding scheme; and The data consistency detection module is used to detect the data consistency of the new table. This includes: dividing the original table into large intervals using the splitting field selected during the redistribution phase; calculating the sum of the CRC32 values ​​(SUMcrc32) of all rows within each large interval; comparing the SUMcrc32 values ​​of the original table and the mirror table to determine if the data in each large interval is consistent; if all large intervals are consistent, the two tables are considered consistent; if inconsistent large intervals exist, further splitting them and comparing the consistency of all sub-intervals is performed, repeating the splitting until the sub-intervals are smaller than a preset size, and locating the specific inconsistent row. If an inconsistent large interval exists but is consistent in its sub-intervals or row-level comparisons, it is still considered consistent. If an inconsistent row is located, the detection is repeated. If multiple detections still result in inconsistency, the two tables are considered inconsistent, and the table switch is prohibited due to data inconsistency. Metadata information is updated, and manual processing is required.

8. An electronic device, characterized in that, include: Memory; and processor; The memory is used to store one or more computer instructions; the one or more computer instructions are executed by the processor to implement the method for online modification of the fragmentation scheme according to any one of claims 1 to 6.

9. A readable storage medium, characterized in that, The readable storage medium stores computer instructions; wherein, when the computer instructions are executed by a processor, they implement the method for online modification of the sharding scheme according to any one of claims 1 to 6.

Images