Method and equipment for extracting and loading data fragments from source end database

Abstract

The invention relates to a method and equipment for extracting and loading data fragments from a source end database. The method comprises the following steps: electing an index first column of to-be-extracted data in the source end database as a fragment key; determining a fragmentation threshold according to a performance test result of the source end database and a historical extraction data record; querying fragmentation keys based on a preset first SQL query statement, performing ascending order arrangement on fragmentation key query results, and determining fragmentation intervals according to the arranged fragmentation key query results and fragmentation threshold values; constructing a second SQL query statement according to the fragment interval; the query condition of the second SQL query statement is a hit index; executing the second SQL query statement, and performing range query on the to-be-extracted data in the source end database; extracting the queried data to be extracted; and loading the extracted data to a standby end database. According to the technical scheme, the index is reasonably utilized, so that the fragment key does not need to be inquired in a table, the establishment of the fragment interval is very rapid, and the efficiency is greatly improved.

Description

Technical Field

[0001] The present application relates to the technical field of data backup, and in particular to a method and device for extracting and loading data slices from a source database. Background Art

[0002] In the scenario of data disaster recovery and backup, full table query cannot fully utilize the advantages of indexes, resulting in reduced query efficiency, which is especially obvious for large data field tables and large data volume tables. Data shard query can solve this problem well by selecting shard keys and establishing shard intervals.

[0003] Data sharding refers to horizontal data sharding, which means splitting a data table into multiple copies for querying. The table structure of each copy is the same, but the number of records may be different. When the amount of data in a table is too large and significantly affects the query performance, data sharding can be used to improve the query performance.

[0004] However, when the prior art uses data sharding to extract and load data from a source database to a backup database, there is still a problem of reduced query efficiency due to table back-up query. Summary of the invention

[0005] In order to at least to some extent overcome the problem in the related art of using data sharding to extract and load data from a source database to a backup database, in which there is a problem of reduced query efficiency due to table back query, the present application provides a method and device for extracting and loading data shards from a source database.

[0006] The scheme of this application is as follows:

[0007] According to a first aspect of an embodiment of the present application, a method for extracting and loading data fragments from a source database is provided, comprising:

[0008] Select the first column of the index of the data to be extracted in the source database as the shard key;

[0009] Determine the sharding threshold according to the performance test results and historical extracted data records of the source database;

[0010] Query the shard key based on a preset first SQL query statement, and sort the shard key query results in ascending order, and establish a shard interval based on the sorted shard key query results and the shard threshold;

[0011] Constructing a second SQL query statement according to the sharding interval; the query condition of the second SQL query statement is a hit index;

[0012] Execute the second SQL query statement to perform a range query on the data to be extracted in the source database;

[0013] Extracting the queried data to be extracted;

[0014] Load the extracted data into the standby database.

[0015] Preferably, the first column of the index of the data to be extracted in the source database is selected as the shard key, including:

[0016] Determine whether the data to be extracted has a primary key index;

[0017] If the data to be extracted has a primary key index, the first column of the primary key index of the data to be extracted is selected as the shard key;

[0018] If the data to be extracted does not have a primary key index, determine whether the data to be extracted has a unique index;

[0019] If there is a unique index for the data to be extracted, the first column of the unique index of the data to be extracted is selected as the shard key;

[0020] If the data to be extracted does not have a unique index, determine whether the data to be extracted has a common index;

[0021] If there is a common index for the data to be extracted, the first column of the common index for the data to be extracted will be selected as the shard key.

[0022] Preferably, the method further comprises:

[0023] If there is no common index for the data to be extracted, a full table query is performed on the data to be extracted in the source database.

[0024] Preferably, after determining the sharding threshold according to the performance test results of the source database and the historical extracted data records, the method further includes:

[0025] Determine whether the table data volume of the data to be extracted is less than the sharding threshold;

[0026] If the amount of table data of the data to be extracted is less than the sharding threshold, a full table query is performed on the data to be extracted in the source database.

[0027] Preferably, performing a range query on the data to be extracted in the source database includes:

[0028] Based on multi-threading, a range query is performed on the data to be extracted in each shard interval in the source database.

[0029] Preferably, when the first SQL query statement hits an index, no back-table query is performed.

[0030] Preferably, the first SQL query statement performs a single-column query;

[0031] The second SQL query statement performs a full-column query.

[0032] According to a second aspect of an embodiment of the present application, a device for extracting and loading data fragments from a source database is provided, including:

[0033] Processor and memory;

[0034] The processor and the memory are connected via a communication bus:

[0035] Wherein, the processor is used to call and execute the program stored in the memory;

[0036] The memory is used to store a program, and the program is used to execute at least a method for extracting and loading data slices from a source database as described in any one of the above items.

[0037] The technical solution provided by the present application may include the following beneficial effects: the method for extracting and loading data shards from a source database in the present application includes: selecting the first column of the index of the data to be extracted in the source database as the shard key; determining the shard threshold according to the performance test results and historical extracted data records of the source database; querying the shard key based on a preset first SQL query statement, and arranging the shard key query results in ascending order, and establishing the shard interval with the arranged shard key query results and the shard threshold; constructing a second SQL query statement according to the shard interval; the query condition of the second SQL query statement is a hit index; executing the second SQL query statement, performing a range query on the data to be extracted in the source database; extracting the queried data to be extracted; and loading the extracted data into the standby database.

[0038] The technical solution in the present application selects the first column of the index of the data to be extracted in the source database as the sharding key. By rationally utilizing the index, when the sharding key is queried based on the preset first SQL query statement and the index is hit, there is no need to query the sharding key back to the table, so the establishment of the sharding interval is very rapid. The rapid establishment of the sharding interval facilitates subsequent range query, data extraction and data loading. The technical solution in the present application greatly improves the efficiency of data extraction and loading from the source database to the backup database.

[0039] It should be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present application. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0041] Figure 1 It is a flowchart of a method for extracting and loading data slices from a source database provided by an embodiment of the present application;

[0042] Figure 2 It is a structural diagram of a device for extracting and loading data slices from a source database provided by an embodiment of the present application.

[0043] Reference numerals: processor-21; memory-22. DETAILED DESCRIPTION

[0044] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0045] Figure 1 This is a flow chart of a method for extracting and loading data from a source database in a fragmented manner provided by an embodiment of the present application. Figure 1 , a method for extracting and loading data slices from a source database, comprising:

[0046] S11: Select the first column of the index of the data to be extracted in the source database as the sharding key;

[0047] The source database refers to the database that stores the data to be extracted.

[0048] Generally, the data to be extracted is stored in the source database in the form of a data table.

[0049] Select the first column of the index of the data to be extracted in the source database as the shard key, including:

[0050] Determine whether the data to be extracted has a primary key index;

[0051] If the data to be extracted has a primary key index, the first column of the primary key index of the data to be extracted is selected as the shard key;

[0052] If the data to be extracted does not have a primary key index, determine whether the data to be extracted has a unique index;

[0053] If there is a unique index for the data to be extracted, the first column of the unique index of the data to be extracted is selected as the shard key;

[0054] If the data to be extracted does not have a unique index, determine whether the data to be extracted has a common index;

[0055] If there is a common index for the data to be extracted, the first column of the common index for the data to be extracted will be selected as the shard key.

[0056] That is, the shard key is selected according to the priority of the first column of the primary key index, the first column of the unique index, and the first column of the common index.

[0057] Among them, the primary key index is a type of index. Defining a primary key for a table in a database relationship diagram will automatically create a primary key index, which is a specific type of unique index. This index requires that each value in the primary key is unique. When the primary key index is used in a query, it also allows fast access to data.

[0058] The difference between a primary key index and a unique index is:

[0059] 1. The primary key is a constraint, and the unique index is an index;

[0060] 2. After the primary key is created, it must contain a unique index, but the unique index is not necessarily the primary key;

[0061] 3. Unique index columns allow null values, but primary keys do not;

[0062] 4. The primary key can be referenced as a foreign key by other tables, but the unique index cannot;

[0063] 5. A table can only create one primary key, but can create multiple unique indexes.

[0064] Therefore, in this embodiment, the shard key is selected according to the priority of the first column of the primary key index, the first column of the unique index, and the first column of the common index.

[0065] It should be noted that there may be a situation where the data to be extracted does not have a primary key index, a unique index, or a common index. Therefore, when the data to be extracted does not have the above three indexes, a full table query is performed directly on the data to be extracted in the source database, and then subsequent data extraction and loading are performed.

[0066] It should be noted that data sharding is to split a distributed table horizontally into multiple data slices according to the specified sharding key and sharding mode, and distribute them in multiple data storage nodes. For a sharded table, a sharding key must be selected. A distributed table can only have one sharding key, which is a column used to divide and locate the table and cannot be modified.

[0067] S12: Determine the sharding threshold based on the performance test results of the source database and the historical extracted data records;

[0068] The sharding threshold is the amount of data that needs to be queried at the source database at one time with the least time overhead. The optimal sharding threshold needs to be determined based on the performance test results of the source database and historical data extraction records.

[0069] For example:

[0070] Assuming that the determined sharding threshold is 1 million, and four shards are divided, 4 million data are extracted at one time.

[0071] It should be noted that after determining the sharding threshold according to the performance test results of the source database and the historical extracted data records, the method further includes:

[0072] Determine whether the amount of data in the table to be extracted is less than the sharding threshold;

[0073] If the amount of data in the table to be extracted is less than the sharding threshold, a full table query is performed on the data to be extracted in the source database.

[0074] It can be understood that if the amount of table data to be extracted is less than the sharding threshold, it means that the amount of table data to be extracted is small, and it can be directly extracted at one time based on the current sharding threshold. Therefore, a full table query can be performed directly on the data to be extracted in the source database, and then subsequent data extraction and loading can be performed.

[0075] S13: querying the sharding key based on the preset first SQL query statement, and arranging the sharding key query results in ascending order, and establishing the sharding interval based on the arranged sharding key query results and the sharding threshold;

[0076] It should be noted that the first SQL query statement is used for single-column query, and when the first SQL query statement hits the index, no table return query is performed.

[0077] In specific practice, the first SQL query statement may be: select columnName from torder bycolumnName asc.

[0078] The above SQL query statement indicates to select the columnName from table t and sort in ascending order according to the value of columnName. ColumnName is the sharding key. The sharding key is queried according to the first SQL query statement above, and the sharding key is sorted in ascending order. The sharding interval is established based on the sorted sharding key query result and the sharding threshold.

[0079] S14: construct a second SQL query statement according to the shard interval; the query condition of the second SQL query statement is a hit index;

[0080] S15: Execute the second SQL query statement to perform a range query on the data to be extracted in the source database;

[0081] It should be noted that the second SQL query statement performs a full-column query and performs a range query on the data to be extracted in the source database. The query condition is to hit the index, so the query efficiency is very high.

[0082] It should be noted that the range query for the data to be extracted in the source database includes:

[0083] Based on multi-threading, a range query is performed on the data to be extracted in each shard interval in the source database.

[0084] For example:

[0085] Assume that the determined shard threshold is 1 million, and 4 tables are synchronized at the same time, and each table extracts data at the same time with 4 shards, then 16 million data will be extracted at one time.

[0086] The multithreading used in this technical solution is single-table multithreading.

[0087] Multithreading is divided into multi-table multithreading and single-table multithreading. Multi-table multithreading, as the name implies, synchronizes multiple tables together, and the tables are independent, so multi-table multithreading can be easily achieved. Single-table multithreading cannot be achieved with traditional synchronization methods, but in this technical solution, the data of each shard is independent of each other, so multiple shards can be queried at the same time, thereby achieving single-table multithreading. Example: At this time, there is a synchronization task, and only a table with a data volume of 1 billion is required to be synchronized. The traditional synchronization method can only run slowly with a single thread. However, using this technical solution, the 1 billion data volume can be split into 1,000 copies, each with 1 million data, and 10 threads can query 10 million data at the same time.

[0088] S16: extracting the queried data to be extracted;

[0089] S17: Load the extracted data into the standby database.

[0090] The subsequent extraction of the queried data to be extracted and loading the extracted data into the standby database are conventional technical means in the prior art and will not be elaborated here.

[0091] This technical solution selects the first column of the index of the data to be extracted in the source database as the sharding key. By making reasonable use of the index, when the sharding key is queried based on the preset first SQL query statement and the index is hit, there is no need to query the sharding key back to the table, so the sharding interval is established very quickly. The rapid establishment of the sharding interval facilitates subsequent range query, data extraction and data loading. This technical solution greatly improves the efficiency of data extraction and loading from the source database to the backup database.

[0092] Embodiment 2

[0093] A device that extracts and loads data from source databases. Figure 2 ,include:

[0094] Processor 21 and memory 22;

[0095] The processor 21 and the memory 22 are connected via a communication bus:

[0096] The processor 21 is used to call and execute the program stored in the memory 22;

[0097] The memory 22 is used to store a program, and the program is used to execute at least one of the methods for extracting and loading data slices from a source database in the above embodiment.

[0098] It can be understood that the same or similar parts of the above embodiments can be referenced to each other, and the contents not described in detail in some embodiments can refer to the same or similar contents in other embodiments.

[0099] It should be noted that, in the description of this application, the terms "first", "second", etc. are only used for descriptive purposes and cannot be understood as indicating or implying relative importance. In addition, in the description of this application, unless otherwise specified, the meaning of "plurality" refers to at least two.

[0100] Any process or method description in a flowchart or otherwise described herein may be understood to represent a module, segment or portion of code that includes one or more executable instructions for implementing the steps of a specific logical function or process, and the scope of the preferred embodiments of the present application includes alternative implementations in which functions may not be performed in the order shown or discussed, including performing functions in a substantially simultaneous manner or in the reverse order depending on the functions involved, which should be understood by technicians in the technical field to which the embodiments of the present application belong.

[0101] It should be understood that the various parts of the present application can be implemented by hardware, software, firmware or a combination thereof. In the above-mentioned embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system. For example, if implemented by hardware, as in another embodiment, it can be implemented by any one of the following technologies known in the art or their combination: a discrete logic circuit having a logic gate circuit for implementing a logic function for a data signal, a dedicated integrated circuit having a suitable combination of logic gate circuits, a programmable gate array (PGA), a field programmable gate array (FPGA), etc.

[0102] A person skilled in the art may understand that all or part of the steps in the method for implementing the above-mentioned embodiment may be completed by instructing related hardware through a program, and the program may be stored in a computer-readable storage medium, which, when executed, includes one or a combination of the steps of the method embodiment.

[0103] In addition, each functional unit in each embodiment of the present application may be integrated into a processing module, or each unit may exist physically separately, or two or more units may be integrated into one module. The above-mentioned integrated module may be implemented in the form of hardware or in the form of a software functional module. If the integrated module is implemented in the form of a software functional module and sold or used as an independent product, it may also be stored in a computer-readable storage medium.

[0104] The storage medium mentioned above can be a read-only memory, a magnetic disk or an optical disk, etc.

[0105] In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "example", "specific example", or "some examples" means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present application. In this specification, the schematic representation of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner.

[0106] Although the embodiments of the present application have been shown and described above, it can be understood that the above embodiments are exemplary and cannot be understood as limitations on the present application. Ordinary technicians in this field can change, modify, replace and modify the above embodiments within the scope of the present application.

Claims

1. A method for extracting and loading data from a source database, characterized in that: include: Select the first column of the index of the data to be extracted in the source database as the shard key; Determine the sharding threshold according to the performance test results and historical extracted data records of the source database; Query the shard key based on a preset first SQL query statement, and sort the shard key query results in ascending order, and establish a shard interval based on the sorted shard key query results and the shard threshold; Constructing a second SQL query statement according to the sharding interval; the query condition of the second SQL query statement is a hit index; Execute the second SQL query statement to perform a range query on the data to be extracted in the source database; Extracting the queried data to be extracted; Load the extracted data into the standby database.

2. The method according to claim 1, characterized in that Select the first column of the index of the data to be extracted in the source database as the shard key, including: Determine whether the data to be extracted has a primary key index; If the data to be extracted has a primary key index, the first column of the primary key index of the data to be extracted is selected as the shard key; If the data to be extracted does not have a primary key index, determine whether the data to be extracted has a unique index; If there is a unique index for the data to be extracted, the first column of the unique index of the data to be extracted is selected as the shard key; If the data to be extracted does not have a unique index, determine whether the data to be extracted has a common index; If there is a common index for the data to be extracted, the first column of the common index for the data to be extracted will be selected as the shard key.

3. The method according to claim 2, characterized in that The method further comprises: If there is no common index for the data to be extracted, a full table query is performed on the data to be extracted in the source database.

4. The method according to claim 1, characterized in that After determining the sharding threshold according to the performance test results of the source database and the historical extracted data records, the method further includes: Determine whether the table data volume of the data to be extracted is less than the sharding threshold; If the amount of table data of the data to be extracted is less than the sharding threshold, a full table query is performed on the data to be extracted in the source database.

5. The method according to claim 1, characterized in that Performing a range query on the data to be extracted in the source database includes: Based on multi-threading, a range query is performed on the data to be extracted in each shard interval in the source database.

6. The method according to claim 1, characterized in that When the first SQL query statement hits the index, no back-table query is performed.

7. The method according to claim 1, characterized in that The first SQL query statement performs a single-column query; The second SQL query statement performs a full-column query.

8. A device for extracting and loading data from a source database, characterized in that: include: Processor and memory; The processor and the memory are connected via a communication bus: Wherein, the processor is used to call and execute the program stored in the memory; The memory is used to store a program, and the program is used at least to execute a method for extracting and loading data slices from a source database as described in any one of claims 1-7.

Images