Relational database table synchronization method and terminal device

Abstract

The present application relates to a method for relational database table synchronization and a terminal device. A first database table to be synchronized is obtained, and the type of the first database table is any type of relational database table. If a first sharding strategy for the first database table exists in a pre-stored set of sharding strategies, the first sharding strategy is directly used for sharding synchronization. If the first sharding strategy does not exist, a candidate segmentation list is constructed for the first database table, and the candidate segmentation list includes valid segmentation columns for the first database table, and the multiple valid segmentation columns are sorted in descending order of priority according to the IO friendliness. The best segmentation column of the first database table is determined according to the candidate segmentation list and a pre-stored blacklist of segmentation columns. The target sampling points on the best segmentation column are determined. The present application can be applied to synchronization scenarios of various types of relational database tables including database tables without primary keys, without index tables, and without numeric columns.

Description

Technical Field

[0001] This application relates to the technical field of database table synchronization, and particularly to a method for synchronizing relational database tables and a terminal device. Background Art

[0002] With the booming development of technologies such as informatization, big data, and domestic databases, more and more scenarios require synchronizing one or more database tables (Tables) from one database (read end) to another database (write end). The read and write ends may be different database software, or may be deployed on different servers or even computer rooms. After synchronization, it is necessary to ensure that the corresponding data at the read and write ends is exactly the same.

[0003] In application practice, scenarios where a single table with a scale of hundreds of millions or even larger needs to be synchronized are often encountered. To improve efficiency, generally, according to a certain field of the table (i.e., the split column), the data of the entire table is divided into multiple logical shards (i.e., the sharding strategy), and then multi-threaded or distributed technologies are used to synchronize in parallel according to the shards to meet the performance requirements. In this process, the selection of the split column and the determination of the sharding strategy are particularly crucial and are decisive factors affecting the overall synchronization efficiency. Summary of the Invention

[0004] Based on this, in view of the above technical problems, this application provides a method for synchronizing relational database tables and a terminal device, which can be applied to synchronization scenarios of various types of relational database tables including database tables without primary keys, without index tables, and without numeric columns.

[0005] In a first aspect, this application provides a method for synchronizing relational database tables, and the method includes the following steps:

[0006] S1. Obtain a first database table to be synchronized, where the type of the first database table is any type of relational database table;

[0007] S2. Determine whether there is a first sharding strategy for the first database table in a pre-stored sharding strategy set. If the first sharding strategy exists, go to step S6; if the first sharding strategy does not exist, go to steps S3 - S5 to formulate a first sharding strategy for the first database table. The sharding strategy set includes target sampling points on the optimal split columns for each database table;

[0008] S6. Obtain target sampling points according to the first sharding strategy, and shard the first database table to obtain multiple sharded data tables;

[0009] S7. Synchronize the multiple sharded data tables until the synchronization of the first database table is completed;

[0010] Among them, the steps for formulating the first sharding strategy include:

[0011] S3. Construct a candidate sharding list for the first database table, where the candidate sharding list includes multiple valid sharding columns in the first database table, and the multiple valid sharding columns are sorted in the order of priority from the highest to the lowest IO friendliness;

[0012] S4. Determine the best sharding column of the first database table according to the candidate sharding list and the pre-stored blacklist of sharding columns;

[0013] S5. Determine the target sampling point of the first database table, and the target sampling point is located on the best sharding column of the first database table.

[0014] In this embodiment, the type of the first database table to be synchronized is any type of relational database table. That is to say, the first database table can be a table with a primary key, an index table or a numeric column, or a database table without a primary key, an index table or a numeric column. By constructing a candidate sharding list for the first database table, and the candidate sharding list includes valid sharding columns for the first database table, and the multiple valid sharding columns are sorted in the order of priority from the highest to the lowest IO friendliness. Since the valid sharding columns are from the first database table, that is to say, the valid sharding columns are certain fields of the first database table. Therefore, regardless of the type of the first database table, the IO friendliness of the best sharding column is the best in the first database table. Then, the target sampling point is determined to implement the sharding synchronization process of the first database table, and the IO friendliness of the synchronization result is the best implementation of the current first database table.

[0015] In another embodiment, the method further includes: obtaining a first sharding strategy for the first database table according to the target sampling point, and storing the first sharding strategy in the sharding strategy set to update the sharding strategy set.

[0016] In this embodiment, by storing the first sharding strategy formulated in the current synchronization process in the sharding strategy set, it is ensured that when the current first sharding strategy is a valid sharding strategy, when the first database table is synchronized again next time, the current first sharding strategy can be directly adopted without having to reconstruct a new first sharding strategy again, thereby improving the efficiency of sharding synchronization.

[0017] In another embodiment, the method further includes: calculating the data skew of the data volume of each sharding synchronization, and determining whether the data skew exceeds a preset threshold. If so, adding the best sharding column used in this sharding to the blacklist of sharding columns, and deleting the sharding strategy used in this sharding for the first database table in the sharding strategy set.

[0018] In this embodiment, after the synchronization of the first database table is completed, the data skew is calculated to determine whether there is a defect of serious data skew in the result of this sharding strategy. If so, this sharding strategy is added to the blacklist, and this sharding strategy is deleted from the sharding strategy set. When the first database table is sharded and synchronized again next time, this sharding strategy can be avoided to improve the quality of sharding synchronization and prevent some shards or nodes from taking on too much load during the synchronization process, which affects the performance and stability of the system.

[0019] In another embodiment, the method further includes: deleting the split columns that have been added to the split column blacklist and whose time of addition to the split column blacklist exceeds a preset time threshold according to the time of addition to the split column blacklist, so as to perform rolling update on the split column blacklist.

[0020] Since the data situation of the first database table changes over time, such as adding new row data or new column data, the split columns that are blacklisted during the current sharding synchronization may also be the best split columns during the next sharding synchronization. Therefore, in order to avoid misjudgment caused by data updates in the first database table, the blacklist is rolled updated, which further improves the efficiency and quality of sharding synchronization.

[0021] In another embodiment, the data skew is the ratio of the standard deviation to the average value of the data volume of the multiple sharding synchronizations; the preset threshold is 1.

[0022] In another embodiment, the construction of the candidate split list for the first database table is specifically as follows:

[0023] Scan the first database table to obtain a plurality of target data columns, where the target data columns include at least one of a partition column, the first column of the primary key, the first column of each index, and other data columns that can be used as effective split columns;

[0024] Sort the target data columns in descending order according to the IO friendliness from high to low and the sharding efficiency from high to low to obtain the candidate split list.

[0025] In another embodiment, the priority order of the target data columns from high to low is: partition column > first column of the primary key > first column of each index > other data columns;

[0026] For target data columns of different data types, the priority order from high to low is: numeric type column > string column > binary column > time type column;

[0027] For target data columns of the same data type, the priority order from high to low is: data column with non-null constraint > data column allowing null.

[0028] In another embodiment, step S4 is specifically as follows:

[0029] Scan the target data columns in the candidate segmentation list in descending order of priority, and determine whether the currently scanned target data column is in the segmentation column blacklist. If so, continue to scan the next target data column. If not, determine the currently scanned target data column as the optimal segmentation column.

[0030] The multiple target data columns in the candidate segmentation list include all columns of the first database table. In this application, all columns of the first database table are scanned, and the scanned valid segmentation columns are sorted in descending order of IO friendliness and descending order of sharding efficiency to obtain the candidate segmentation list. Thus, regardless of the type of the first database table, that is, whether the first database table is a numerical table, a string table, or other non-numerical table types, and regardless of whether the first database table contains partition columns, primary keys, indexes, etc., in combination with the target data columns of the candidate segmentation list and the segmentation column blacklist, the optimal segmentation column can be ensured to be screened, and both the IO friendliness and sharding efficiency of the optimal segmentation column are the best. That is, this application can achieve reasonable sharding synchronization for various types of first database tables.

[0031] In another embodiment, step S5 is specifically as follows: Using a preset sampling method, determine the target sampling points of the first database table according to the preset sampling point spacing; the preset sampling method includes at least one of the fast sampling system function, window function, and client sampling in the relational database; the client sampling is to transfer the data of the optimal segmentation column determined on the database side to the client, and determine the target sampling points on the client.

[0032] In another embodiment, the preset sampling methods are, in descending order of priority, the fast sampling system function, window function, and client sampling in the relational database; step S5 is specifically as follows:

[0033] According to the descending order of the sampling method priority, use the corresponding sampling method to sample the first database table;

[0034] If the current sampling method fails, continue to sample the first database table using the next sampling method;

[0035] If the current sampling method is successful, output the target sampling points of the first database table.

[0036] The determination method of the target sampling points in this application. The priority order of the sampling methods, from high to low, is to sort and execute according to the increasing generality of the sampling methods, so as to improve the sampling efficiency. The fast sampling system functions provided by some relational databases, although with low generality, have high sampling efficiency for such databases; in addition, most relational databases do not have built-in fast sampling system functions, but all support window functions. For such databases, using window functions for sampling has high sampling efficiency; for a small number of databases that do not support window functions, the client sampling method can be used for sampling, that is, data sorting is completed on the database side, and the selection of target sampling points is completed on the client side. The client sampling has high generality but sacrifices sampling efficiency, so its priority is low, but it also ensures that sampling can be achieved for various types of databases.

[0037] In a second aspect, this application also provides a terminal device, including a memory and a processor. The memory stores a computer program, and when the processor executes the computer program, it implements the steps of the method described in any one of the embodiments in the first aspect above.

[0038] In a third aspect, this application also provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, it implements the steps of the method described in any one of the embodiments in the first aspect above.

[0039] In a fourth aspect, this application also provides a computer program product, including a computer program. When the computer program is executed by a processor, it implements the steps of the method described in any one of the embodiments in the first aspect above.

[0040] Beneficial effects: In the embodiments of this application, by constructing a candidate segmentation list for the first database table, the candidate segmentation list includes multiple valid segmentation columns of the first database table, and all the multiple valid segmentation columns are from the first database table and are sorted in descending order of IO friendliness priority. Then, combined with the pre-stored blacklist of segmentation columns, the unsuitable valid segmentation columns in the candidate segmentation list are excluded. Thus, regardless of the type of the first database table, an optimal segmentation column with the best IO friendliness can be determined in the order of priority, that is, the embodiments of this application have no restrictions on the type of the first database table. That is to say, the first database table can be a table with a primary key, an index table or a numeric column, or a database table without a primary key, without an index table, and without a numeric column, and an optimal segmentation column with the best IO friendliness of the current first database table can be confirmed. After that, the target sampling points are determined, so as to realize the sharding synchronization process of the first database table, and the IO friendliness of the synchronization result is the best implementation of the current first database table. Description of the Drawings

[0041] To more clearly illustrate the technical solutions in the embodiments of the present application or related technologies, the following will briefly introduce the accompanying drawings required for the description of the embodiments of the present application or related technologies. Obviously, the accompanying drawings in the following description are only some embodiments of the present application. For those of ordinary skill in the art, without creative efforts, other related drawings can also be obtained based on these drawings.

[0042] Figure 1 It is an application environment diagram of the relational database table synchronization method in an embodiment;

[0043] Figure 2 It is a schematic flowchart of the relational database table synchronization method in an embodiment;

[0044] Figure 3 It is a schematic flowchart of the relational database table synchronization method in an embodiment;

[0045] Figure 4 It is a structural block diagram of the relational database table synchronization device in an embodiment;

[0046] Figure 5 It is an internal structure diagram of a terminal device in an embodiment. Specific implementation manners

[0047] In order to make the objectives, technical solutions and advantages of the present application clearer, the following further details the present application in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0048] In the synchronization scenario of database tables, the key steps of the sharding strategy are the selection of split columns and sampling points. For the selection of split columns and sampling points, conventional techniques include manual specification, range linear fitting, hash modulo-based methods, etc.

[0049] For example, open-source software such as DataX, Kettle, and SeaTunnel. These software do not provide good enough algorithms to automatically determine the split columns. In applications, the split columns and sampling points need to be manually specified, resulting in low efficiency. The method of manually specifying split columns is acceptable in the scenario of a small number of tables, but when hundreds or even hundreds of thousands of tables need to be processed, the problem of low implementation efficiency is surely unacceptable. On the other hand, selecting appropriate split columns requires operators to have a relatively deep understanding of the database, knowing the database implementation principle. Otherwise, if the wrong split columns are selected, not only will the data synchronization efficiency not be improved, but it may also cause a large number of random accesses to the read-end database, affecting the stability of normal business. That is to say, for the large-scale database table synchronization scenario, the high requirements for operators and low efficiency of the method of manually specifying split columns and sampling points are unacceptable.

[0050] In the method of linear fitting of the value range, the basic idea is to use the aggregation functions (usually MIN and MAX functions) in the SQL standard to find the minimum and maximum values of the split column, and then use (upper_bound - lower_bound) / N as the step size to generate sampling points by linear distribution fitting, where N is the expected number of shards; then, according to the sampling points obtained by fitting, sharding and data synchronization are performed in the way of interval filtering. However, the value range fitting method can only split and sample digital columns, and cannot be compatible with split columns of non-integer types, and cannot be used for types such as binary and strings (especially multi-byte character sets). Moreover, since its sampling points are obtained by linear distribution fitting, there may be a large deviation from the actual situation, so relatively serious data skew problems may occur.

[0051] Data skew refers to the situation in a sharding system where the data volume, query request volume, or computing load on some shards or nodes is much higher than that on other shards or nodes. The data volume in some shards far exceeds that in other shards, resulting in greater storage pressure on these shards. In some cases, the sizes of the shards may be inconsistent, resulting in the data volume of some shards being much larger than that of other shards, thereby causing some shards or nodes to bear too much load and affecting the performance and stability of the system.

[0052] In the method based on hash modulo, the hash value of the split column is used, and then the sharding and parallel replication are carried out simultaneously in the way of modulo N. However, during sharding queries, a large number of random I / Os often occur. This method not only fails to achieve the purpose of improving the synchronization efficiency, but also easily causes database jitter. Random I / O (Random I / O) refers to the situation where read and write operations on a disk or storage device occur at discontinuous physical locations. Compared with sequential I / O (Sequential I / O), the characteristic of random I / O is that the address of each read and write operation is not continuous, but scattered throughout the storage space. For a mechanical hard disk, random I / O requires frequent movement of the read / write head, and for a solid-state drive, it requires frequent searching for different blocks, usually resulting in higher latency and lower throughput. Therefore, the generation of random I / O should be avoided during the sharding process of a database table.

[0053] The relational database table synchronization method provided by the embodiments of this application can be applied to, for example Figure 1In the application environment shown. Among them, the client 102 communicates with the server 104 through the network. The data storage system can store the data that the server 104 needs to process, including the database cluster that needs to be synchronized. The data storage system can be integrated on the server 104, or placed on the cloud or other network servers. Among them, the client 102 can be, but is not limited to, various personal computers, laptops, smartphones, tablets, Internet of Things devices, and portable wearable devices. The Internet of Things devices can be smart speakers, smart TVs, smart air conditioners, smart in-vehicle devices, projection devices, etc. The portable wearable devices can be smart watches, smart bracelets, head-mounted devices, etc. The head-mounted device can be a virtual reality (VR) device, an augmented reality (AR) device, smart glasses, etc. The server 104 can be an independent physical server, a server cluster or a distributed system composed of multiple physical servers, or a cloud server providing cloud computing services.

[0054] The sharding operation and synchronization operation during the database synchronization process are mainly executed by each node in the database cluster. Each node is responsible for storing and managing partial data shards and ensuring data consistency between different shards. The client interacts with the database cluster through the API or SQL interface and generally does not directly participate in the sharding and synchronization operations. The main responsibilities of the client include initiating query and write requests, handling shard awareness and shard rebalancing, monitoring and logging, etc.

[0055] In an exemplary embodiment, as Figure 2 shown, Figure 2 FIG. is a schematic flow diagram of a method for synchronizing a relational database table in an embodiment. The method includes the following steps:

[0056] Step S1, obtain a first database table to be synchronized, where the type of the first database table is any type of relational database table.

[0057] The first database table can be a database table with partition columns, a primary key, an index, and / or numeric columns (hereinafter referred to as a common table), or a database table without partition columns, without a primary key, without an index, and without numeric columns (hereinafter referred to as an uncommon table). Moreover, the first database table can be a database table with any amount of data, such as a large table. In database design, tables without a primary key, without an index, and without numeric columns, although not best practices, may also occur in certain specific scenarios. Such tables usually lack the best features of structured data management, such as uniqueness constraints, fast query performance, and efficient sorting capabilities. Tables without partition columns, without a primary key, without an index, and without numeric columns include log tables, wide tables, temporary tables, intermediate tables in the ETL process, historical archive tables, non-normalized tables, Internet of Things data tables, text analysis tables, event tables in event-driven architectures, etc. In addition to processing common tables, this embodiment can also perform sharding synchronization on these uncommon tables.

[0058] Step S2: Determine whether there is a first sharding strategy for the first database table in the pre-stored set of sharding strategies. If the first sharding strategy exists, proceed to step S6; if the first sharding strategy does not exist, proceed to steps S3 - S5 to formulate a first sharding strategy for the first database table. The set of sharding strategies includes target sampling points on the best splitting columns for each database table.

[0059] Specifically, load the cached sharding strategies to obtain the pre-stored set of sharding strategies. The set of sharding strategies can be stored in the cache of the data storage system. These sets of sharding strategies include multiple sharding strategies, and each sharding strategy corresponds to a database table. It can be understood that as the system runs, the data content and data volume in the first database table are updated and changed, and there may also be situations where the first database table needs to be synchronized multiple times at different time periods. The best sharding strategy used in the previous synchronization process may not be the best sharding strategy in the next synchronization process. Therefore, before performing this sharding synchronization, it is necessary to first determine whether there is a first sharding strategy for the current first database table in the pre-stored set of sharding strategies.

[0060] Step S6: Obtain the target sampling points according to the first sharding strategy, and shard the first database table to obtain multiple sharded data tables.

[0061] Step S7: Synchronize the multiple sharded data tables until the synchronization of the first database table is completed.

[0062] If there is a first sharding strategy in the pre-stored set of sharding strategies that is adapted to the first database table, directly shard and synchronize the first database table according to the multiple target sampling points in the first strategy to improve the sharding synchronization efficiency.

[0063] During the shard synchronization process, in this embodiment, it is preferably to adopt a multi-threaded synchronous execution method or a distributed execution method to concurrently execute the synchronization processes of multiple shards to improve the synchronization efficiency.

[0064] Wherein, when the first sharding policy adapted to the current first database table is not included in the pre-stored sharding policy set, in this embodiment, the first sharding policy is directly reconstructed, as Figure 2 shown, the formulation steps of the first sharding policy include steps S3 - S5:

[0065] Step S3, construct a candidate sharding list for the first database table, where the candidate sharding list includes multiple valid sharding columns in the first database table, and the multiple valid sharding columns are sorted in the priority order from the highest to the lowest I / O friendliness;

[0066] The valid sharding columns are certain fields in the first database table that can be used as sharding columns to shard the first database table. The valid sharding columns can include some fields (i.e., some columns) of the first database table, or can include all fields (i.e., all columns) of the first database table.

[0067] In the embodiment of the present application, it is preferably to scan all columns of the first database table, and sort the scanned valid sharding columns in the order from the highest to the lowest I / O friendliness to obtain a candidate sharding list. That is to say, according to the priority order of the candidate sharding list from the highest to the lowest, the best sharding column determined must be the sharding column with the best I / O friendliness in the current first database table, thereby reducing the generation of random I / O during sharding queries.

[0068] Step S4, determine the best sharding column of the first database table according to the candidate sharding list and the pre-stored sharding column blacklist;

[0069] In the embodiment of the present application, a corresponding candidate sharding list and a sharding column blacklist are respectively set for each database table, that is, there is a one-to-one relationship between the database table and the candidate sharding list and the sharding column blacklist. The sharding column blacklist in the embodiment of the present application can be generated according to requirements of sharding quality such as data skew, data consistency, and synchronization latency after sharding. For example, the sharding columns with data skew exceeding a preset threshold are added to the sharding column blacklist, and / or the sharding columns with data consistency lower than a preset threshold, and / or the sharding columns with synchronization latency exceeding a preset threshold are added to the sharding column blacklist. The setting method of the sharding column blacklist in this embodiment is not limited.

[0070] By setting the sharding column blacklist, when determining the best sharding column, the sharding columns with low sharding quality are avoided, and the quality of the sharding synchronization result is improved to a certain extent.

[0071] Step S5: Determine the target sampling points of the first database table, where the target sampling points are located on the optimal splitting column of the first database table.

[0072] The sharding strategy is the target sampling points on the optimal splitting column of the database table, and the target sampling points are the sampling points on the optimal splitting column. The sharding execution process is to split the rows of the database table with the rows where multiple target sampling points are located as boundaries. For example, for a database table with 900,000 rows, there are two target sampling points A and B. Point A is the intersection of the 5th column and the 300,000th row, and point B is the intersection of the 5th column and the 600,000th row. When synchronizing, it can be divided into 3 shards, that is, the data with row numbers less than or equal to 300,000 rows is divided into shard 1, the data with row numbers greater than 300,000 rows and less than or equal to 600,000 rows is divided into shard 2, and the data with row numbers greater than 600,000 rows is divided into shard 3. When sharding, it is necessary to ensure that the data volume of each shard is as consistent as possible. Generally, the sharding is carried out in the way of average division according to the number of rows to avoid data skew.

[0073] The appropriate method can be selected to determine the target sampling points according to the characteristics of the database itself. For most relational databases, window functions are supported, so the window function can be used to determine the target sampling points. For example, considering the data volume of the first database table, the efficiency and quality of sharding synchronization, etc., one target sampling point can be sampled every 1 million rows. The window function code is as follows:

[0074] SELECT v FROM (

[0075] SELECT { field_name} AS v,

[0076] ROW_NUMBER()OVER (ORDER BY { field_name} ASC)AS rn

[0077] FROM { table_name}

[0078] )t

[0079] WHERE t.rn % 1000000 = 0

[0080] For some relational databases with built-in fast sampling system functions, the built-in system functions of the database can be preferentially used for sampling. For example, for oracle databases, oracle rowid can be used for sampling. Taking sampling one target sampling point every 1 million rows as an example, the code is as follows:

[0081] BEGIN

[0082] DBMS_PARALLEL_EXECUTE.create_task("task");

[0083] DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid("task", {schema}, {table}, true, 1000000);

[0084] END

[0085] For a small part of databases that do not support window functions, client-side sampling can be used for sampling, that is, determining the optimal split column and sorting the data on the database side, and selecting the target sampling points on the client side.

[0086] The relational database synchronization method disclosed in the embodiments of the present application is applicable to various types of relational database tables, fully compatible with database tables without primary keys, without indexes, and without numeric columns, has low requirements and constraints on the data types of each column in the database table, supports fields of types such as strings (including multi-byte character sets) and binaries as split columns for sharding, and can cover most relational database synchronization scenarios.

[0087] Moreover, by constructing a candidate split list for the first database table, and the candidate split list includes valid split columns for the first database table, and the multiple valid split columns are sorted in descending order of priority according to the IO friendliness, therefore, regardless of the type of the first database table, the IO friendliness of the optimal split column is the best in the first database table. Then, the target sampling points are determined, so as to implement the sharding synchronization process of the first database table, and the IO friendliness of the synchronization result is the best implementation of the current first database table. In addition, the embodiments of the present application determine the optimal split column in a completely automated manner, and require little or no manual intervention in few scenarios, realizing the automated synchronization of various types of relational database tables.

[0088] The flow chart of the relational database table synchronization method disclosed in other embodiments of the present application is as Figure 3 described, including the following steps:

[0089] S201. Obtain the first database table to be synchronized, where the type of the first database table is any type of relational database table;

[0090] S202. Determine whether there is a first sharding strategy for the first database table in the pre-stored sharding strategy set. If the first sharding strategy exists, go to step S212. If the first sharding strategy does not exist, formulate a first sharding strategy for the first database table. The sharding strategy set includes the target sampling points on the optimal split columns for each database table.

[0091] S212. Obtain target sampling points according to the first sharding strategy, and shard the first database table to obtain multiple sharded data tables;

[0092] S213. Synchronize the multiple sharded data tables until the synchronization of the first database table is completed.

[0093] The above several steps are similar to the same steps in the previous embodiment, and this embodiment will not elaborate on them in detail. The process of formulating the first sharding strategy in this embodiment includes steps S203 - S210. Among them, the method of constructing a candidate segmentation list for the first database table in this embodiment includes the following steps:

[0094] Step S203: Scan the first database table to obtain multiple target data columns, where the target data columns include at least one of a partition column, the first column of the primary key, the first column of each index, and other data columns that can be used as effective sharding columns.

[0095] Step S204: Sort the target data columns in descending order according to the IO friendliness from high to low and the sharding efficiency from high to low to obtain the candidate segmentation list.

[0096] Among them, the priority order of the target data columns from high to low is: partition column > the first column of the primary key > the first column of each index > other data columns (hereinafter referred to as sorting method 1).

[0097] In a sharded or partitioned database, data is scattered and stored in multiple nodes or files. When a query involves multiple shards or partitions, it may cause random IO across nodes. Therefore, for a relational database containing a partition column, such as a distributed relational database, considering the IO friendliness and sharding efficiency, the priority of the partition column is set to the highest in the embodiments of the present application.

[0098] Moreover, since the first column of the primary key is mostly clustered, in order to reduce random IO, that is, the access is in the form of sequential IO, for the first database table containing the primary key, the first column of the primary key is added to the candidate segmentation list. Further, the first column of the primary key is often a high-cardinality sharding column (such as user ID, order ID, etc.). When using a high-cardinality sharding column as the best sharding column for sharding, it can also ensure to a certain extent that the data can be evenly distributed to each shard. The priority of the first column of the primary key is relatively high, which can also avoid data skew to a certain extent.

[0099] In the index access scenario, when the database uses an index for querying, the index structure (such as a B+ tree) usually stores data dispersedly in different pages or blocks. Therefore, a large number of random I / Os may occur during the query process, especially in the case of a multi-level index, where the amount of random I / O is even greater. Therefore, for the first database table containing an index, in the embodiments of the present application, the first column of each index is added to the candidate split list.

[0100] It should be noted that there are various types of the first database table. In order to adapt to each type of the first database table, when constructing the candidate split list, the situations of each type of database table are considered.

[0101] Specifically, when actually executed, the method is as follows:

[0102] Scan all columns of the first database table. If the first database table contains a partition column (i.e., partition), then add the partition column to the candidate split list with the highest priority.

[0103] If the first database table contains a primary key, then add the first column in the primary key to the candidate split list.

[0104] If the first database table contains an index, then add the first column of each index to the candidate split list.

[0105] At the same time, scan all columns of the first database table and sort them according to the data type. For the target data columns of different data types, the priority order from high to low is: numeric type column > string column > binary column > time type column (hereinafter referred to as sorting method 2); for the target data columns of the same data type, the priority order from high to low is: data column with non-null constraint > data column allowing null (hereinafter referred to as sorting method 3).

[0106] It should be noted that all columns of the first database table will be sorted by combining sorting methods 1, 2, and 3. Preferably, the priority of the sorting methods from high to low is sorting method 1 > sorting method 2 > sorting method 3.

[0107] For example, when sorting the partition column, primary key column, and index column in the first database table, the partition column, the first primary key column, and the first index column will be preferentially arranged in the top 3 positions. If the first database table does not contain a partition column and only contains a primary key column and an index column, the first primary key column and the first index column will be arranged in the top 2 positions in sequence. If the first database table does not contain a partition column and a primary key column and only contains an index column, the first index column will be arranged in the first column, and then the other columns of the first database table will be sorted according to sorting methods 2 and 3. If the first database table does not contain a partition column, a primary key column, or an index column, all columns of the first database table only need to be sorted according to sorting methods 2 and 3 during sorting.

[0108] Specifically, the combined sorting methods 2 and 3 can be as follows. If the first database table contains multiple numeric type columns, multiple string columns, etc., the multiple numeric type columns will be preferentially sorted according to sorting method 3, and then the multiple string columns will be sorted according to sorting method 3, and so on.

[0109] After the construction of the candidate split list is completed, the selection of the best split column is performed. Specifically, in another embodiment of the present application, this step includes:

[0110] Step S205: Scan the target data columns in the candidate split list in descending order of priority, that is, scan all columns in the candidate split list in sequence.

[0111] Step S206: Determine whether the currently scanned target data column is in the split column blacklist. If so, return to step S205 and continue to scan the next target data column. If not, enter step S207, and determine the currently scanned target data column as the best split column.

[0112] In the embodiment of the present application, by scanning all columns of the first database table and sorting all columns in descending order of IO friendliness and descending order of sharding efficiency, a candidate split list is obtained. Therefore, regardless of the type of the first database table, that is, whether the first database table is a numeric table, a string table, or other non-numeric table types, and regardless of whether the first database table contains a partition column, a primary key, an index, etc., in combination with the target data columns and the split column blacklist of the candidate split list, the best split column can be ensured to be screened, and the IO friendliness and sharding efficiency of the best split column are both the best. That is, the embodiment of the present application can achieve reasonable sharding synchronization for various types of first database tables.

[0113] After determining the optimal splitting column, enter the sampling point selection process. In another embodiment of the present application, the sampling point selection process is to determine the target sampling points of the first database table according to a preset sampling method and a preset sampling point spacing. As described in Embodiment 1 above, the sampling method is related to the sampling methods supported by the database, that is, the preset sampling method includes at least one of the fast sampling system function, window function, and client sampling in the relational database. The client sampling is to transmit the data of the optimal splitting column determined on the database side to the client, and determine the target sampling points on the client.

[0114] The preset sampling methods are, from highest to lowest priority: the fast sampling system function, window function, and client sampling in the relational database. The priority sorting from highest to lowest is sorted according to the increasing generality of the sampling methods. Among these sampling methods, the method with lower generality has higher sampling efficiency, and the higher the generality, the lower the sampling efficiency.

[0115] In the embodiments of the present application, the sampling point selection process specifically includes the following steps:

[0116] Step S208: According to the order of sampling method priority from highest to lowest, use the corresponding sampling method to sample the first database table according to the preset sampling spacing. The sampling spacing can be any spacing, mainly set to avoid data skew. For database tables of different types and different data volumes, the preset sampling spacing can be the same or different, and this embodiment does not limit this.

[0117] Step S209: Determine the execution result of the current sampling method, that is, judge whether the current sampling method is executed successfully. If the current sampling method fails to sample, return to Step S208 and continue to sample the first database table using the next sampling method; if the current sampling method samples successfully, enter Step S210 and output the target sampling points of the first database table.

[0118] After outputting the target sampling points, Steps S212 and S213 can be entered. According to the target sampling points, perform the sharding and synchronization processes for the first database. In this embodiment, it is preferably to use multi-threading or distributed parallel execution for the sharding and synchronization processes.

[0119] That is to say, if the first database table supports the fast sampling system function, the fast sampling system function is directly used to determine the sampling points. If the first database table does not support the fast sampling system function, the window function is used to determine the sampling points. If the first database table supports neither the fast sampling system function nor the window function, the client sampling is used to determine the sampling points. The client sampling method has no additional dependency requirements. It only needs to read all the data of the complete optimal split column to the client, and directly determine the target sampling points on the client according to the sampling interval and the number of shards, etc.

[0120] The above methods for determining the optimal split column and the target sampling points take into account both the IO friendliness and the sharding efficiency.

[0121] After completing the process of determining the optimal split column and the target sampling points in another embodiment of the present application, it further includes step S211 of obtaining a first sharding strategy for the first database table according to the target sampling points, and storing the first sharding strategy in the sharding strategy set to update the sharding strategy set.

[0122] In this embodiment, by storing the first sharding strategy formulated in the current synchronization process in the sharding strategy set, it is ensured that when the current first sharding strategy is an effective sharding strategy, the current first sharding strategy can be directly adopted when synchronizing the first database table again next time, without having to reconstruct a new first sharding strategy again, thereby improving the sharding synchronization efficiency.

[0123] Another embodiment of the present application, after completing the sharding synchronization process for the first database this time, further includes an analysis process of the sharding synchronization quality, and this process includes the following steps:

[0124] Step S214: Calculate the data skewness for the data volume of each sharding synchronization.

[0125] There are many ways to calculate the data skewness. In this embodiment, the data skewness is the ratio of the standard deviation to the average value of the data volumes of the multiple sharding synchronizations. The calculation formula for the data skewness Skewness is as follows:

[0126]

[0127] Among them, the actually processed data volume of each shard is Di, n is the number of shards, and μ is the average value of the data volumes of the n shards.

[0128] Step S215: Determine whether the data skewness exceeds a preset threshold. If so, enter step S216, and list the optimal split column adopted in this sharding in the split column blacklist. In this embodiment, the preset threshold for the data skewness is 1.

[0129] Step S217: Delete the sharding policy adopted for the current sharding of the first database table from the set of sharding policies.

[0130] In this embodiment, after the synchronization of the first database table is completed, the data skew is calculated to determine whether there is a defect of serious data skew in the result of this sharding policy. If so, the split column adopted in this sharding is added to the split column blacklist, and this sharding policy is deleted from the set of sharding policies. When the first database table is sharded and synchronized again next time, this sharding policy can be avoided to improve the quality of sharding synchronization and prevent some shards or nodes from taking on too much load during the synchronization process, which affects the performance and stability of the system.

[0131] In the embodiment of the present application, for multiple database tables to be synchronized, a set of sharding policies can be maintained. In the set of sharding policies, there are sharding policies for each database table to be synchronized, and its storage method can be to associate the identifier of the database table to be synchronized with the corresponding sharding policy for storage. In other words, in this embodiment, a corresponding sharding policy is maintained for each database table.

[0132] Moreover, in the embodiment of the present application, a corresponding split column blacklist is also maintained for each database table to be synchronized. In other embodiments, the method further includes: deleting the split columns whose time of being added to the split column blacklist exceeds a preset time threshold according to the time of being added to the split column blacklist, so as to perform rolling update on the split column blacklist. This preset time threshold can be set according to the type, data volume, update rule, or sharding synchronization rule of the database table to be synchronized to adapt to the sharding synchronization requirements of different database tables in different scenarios.

[0133] Since the data situation of the first database table changes over time, such as adding new row data or new column data, the split columns blacklisted during the current sharding synchronization may also be the best split columns during the next sharding synchronization. Therefore, in order to avoid misjudgment caused by data updates of the first database table, the blacklist is updated in a rolling manner, which further improves the efficiency and quality of sharding synchronization.

[0134] Since the update logics and update rules of the sharding policy and the split column blacklist are different, in this embodiment, the sharding policy and the split column blacklist are stored separately and updated independently, which is convenient for independent maintenance of each, without interference with each other, and ensures the stable operation of the system.

[0135] Other embodiments of the present application also disclose a relational database table synchronization device for executing the relational database table synchronization method described in any of the above embodiments. The structural block diagram of the device is as Figure 4 shown and includes the following modules:

[0136] The storage module 10 is used to store the sharding policy set corresponding to multiple database tables, as well as the blacklist of split columns corresponding to each database table. Each database table corresponds to a sharding policy and a blacklist of split columns.

[0137] The database table acquisition module 11 is used to acquire a first database table to be synchronized. The type of the first database table is any type of relational database table.

[0138] The first search module 12 is used to determine whether there is a first sharding policy for the first database table from the pre-stored sharding policy set.

[0139] The first sharding policy construction module 20 is used to construct a matching first sharding policy for the first database table when the storage module 10 does not contain the first sharding policy corresponding to the first database table. The first sharding policy construction module 20 includes:

[0140] The candidate split list construction module 13 is used to construct a candidate split list for the first database table. The candidate split list includes multiple valid split columns in the first database table, and the multiple valid split columns are sorted in the priority order from high to low according to the IO friendliness.

[0141] The best split column determination module 14 is used to determine the best split column of the first database table according to the candidate split list and the blacklist of split columns pre-stored in the storage module 10.

[0142] The target sampling point determination module 15 is used to determine the target sampling point of the first database table, and the target sampling point is located on the best split column of the first database table.

[0143] In addition, the device further includes a sharding module 16, which is used to obtain the target sampling point according to the first sharding policy, shard the first database table to obtain multiple sharded data tables, and a synchronization module 17, which is used to synchronize the multiple sharded data tables until the synchronization of the first database table is completed.

[0144] In an exemplary embodiment, a terminal device is provided. The terminal device may be a server. There is a node in the database cluster inside the server responsible for sharding and synchronizing database tables, and its internal structure diagram may be as Figure 5As shown in the figure. The terminal device includes a processor, a memory, an input / output interface (Input / Output, abbreviated as I / O), and a communication interface. Among them, the processor, the memory, and the input / output interface are connected through a system bus, and the communication interface is connected to the system bus through the input / output interface. Among them, the processor of the terminal device is used to provide computing and control capabilities. The memory of the terminal device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program, and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the terminal device is used to store voice interaction-related data based on a dynamic tag library. The input / output interface of the terminal device is used to exchange information between the processor and external devices. The communication interface of the terminal device is used to interact with an external client through an API or an SQL interface. When the computer program is executed by the processor, it implements the relational database synchronization method described in the above embodiments.

[0145] Those skilled in the art can understand that Figure 5 the structure shown in the figure is only a block diagram of some structures related to the solution of this application, and does not constitute a limitation on the terminal device to which the solution of this application is applied. The specific terminal device may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0146] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, it implements the steps in the above method embodiments.

[0147] It should be noted that the information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data for analysis, stored data, displayed data, etc.) in the database tables involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use, and processing of relevant data need to comply with relevant regulations.

[0148] Those of ordinary skill in the art can understand that all or part of the processes in the methods of the above embodiments can be completed by instructing relevant hardware through a computer program. The computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above methods. Among them, any reference to a memory, database, or other medium used in the embodiments provided in the present application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetoresistive random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM), etc. The databases involved in the embodiments provided in the present application can include at least one of relational databases and non-relational databases. Non-relational databases can include distributed databases based on blockchain, etc., without limitation. The processors involved in the embodiments provided in the present application can be general-purpose processors, central processors, graphics processors, digital signal processors, programmable logic devices, data processing logics based on quantum computing, artificial intelligence (AI) processors, etc., without limitation.

[0149] The technical features of the above embodiments can be combined arbitrarily. For the sake of brevity of description, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, it should be considered as falling within the scope recorded in the present application.

[0150] The above-described embodiments merely represent several implementation manners of the present application. The description thereof is relatively specific and detailed, but it should not be construed as a limitation to the patent scope of the present application. It should be noted that for those of ordinary skill in the art, without departing from the concept of the present application, several modifications and improvements can still be made, and these all fall within the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the appended claims.

Claims

1. A method for synchronizing a relational database table, characterized in that: The method comprises: S1. Obtain a first database table to be synchronized, where the type of the first database table is any type of relational database table; S2. Determine whether there is a first sharding strategy for the first database table from a pre-stored sharding strategy set. If there is the first sharding strategy, proceed to step S6. If there is no first sharding strategy, proceed to steps S3-S5 to formulate a first sharding strategy for the first database table. The sharding strategy set includes target sampling points on the optimal splitting columns for each database table. S6. Obtain target sampling points according to the first sharding strategy, and shard the first database table to obtain multiple sharding data tables; S7, synchronizing the multiple shard data tables until the synchronization of the first database table is completed; The step of formulating the first sharding strategy includes: S3. Construct a candidate split list for the first database table, wherein the candidate split list includes multiple valid split columns in the first database table, and the multiple valid split columns are sorted in order of priority from high to low in terms of IO friendliness; S4. Determine the best segmentation column for the first database table according to the candidate segmentation list and the pre-stored segmentation column blacklist; S5. Determine a target sampling point of the first database table, where the target sampling point is located on the optimal split column of the first database table.

2. The method according to claim 1, characterized in that Also includes: A first sharding strategy for the first database table is obtained according to the target sampling point, and the first sharding strategy is stored in the sharding strategy set to update the sharding strategy set.

3. The method according to claim 2, characterized in that The method also includes: calculating the data skewness of the data volume synchronized by each shard, and determining whether the data skewness exceeds a preset threshold; if so, adding the optimal sharding column used in this sharding to the sharding column blacklist, and deleting the sharding strategy used in this sharding for the first database table from the sharding strategy set.

4. The method according to claim 3, characterized in that The method further includes: according to the time of joining the segmentation column blacklist, deleting the segmentation columns whose time of joining the segmentation column blacklist exceeds a preset time threshold, so as to perform rolling update on the segmentation column blacklist.

5. The method according to claim 1, characterized in that Step S3 is specifically: Scan the first database table to obtain multiple target data columns, where the target data columns include at least one of a partition column, a first column in a primary key, a first column in each index, and other data columns that can be used as valid partition columns; The target data columns are prioritized according to the order of IO friendliness from high to low and sharding efficiency from high to low to obtain the candidate sharding list.

6. The method according to claim 5, characterized in that The priority order of the target data columns is from high to low: partition column > first column of primary key > first column of each index > other data columns; For target data columns of different data types, the priority order from high to low is: numeric type column > string column > binary column > time type column; For target data columns of the same data type, the priority order from high to low is: data columns containing non-null constraints > data columns that are allowed to be null.

7. The method according to claim 6, characterized in that Step S4 is specifically as follows: The target data columns in the candidate segmentation list are scanned in descending order of priority, and it is determined whether the currently scanned target data column is in the segmentation column blacklist. If so, the next target data column is scanned continuously; if not, the currently scanned target data column is determined as the optimal segmentation column.

8. The method according to claim 1, characterized in that Step S5 specifically includes: using a preset sampling method, and determining the target sampling points of the first database table according to a preset sampling point spacing; the preset sampling method includes at least one of a fast sampling system function, a window function, and client sampling in the relational database; the client sampling is to transmit the optimal split column data determined on the database side to the client, and determine the target sampling points on the client.

9. The method according to claim 8, characterized in that The preset sampling methods are, in descending order of priority, fast sampling system functions, window functions and client sampling in the relational database; the step S5 is specifically as follows: In descending order of priority of the sampling methods, the first database table is sampled using the corresponding sampling method; If the current sampling method fails, continue to use the next sampling method to sample the first database table; If the current sampling method is successful, the target sampling point of the first database table is output.

10. A terminal device, comprising a memory and a processor, wherein the memory stores a computer program, characterized in that: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 9 are implemented.

11. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 9 are implemented.

12. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 9 are implemented.

Images