Relational database table synchronization method and terminal equipment

Abstract

The invention relates to a relational database table synchronization method and terminal equipment, and the method comprises the steps: obtaining a to-be-synchronized first database table, the type of the first database table being any type of relational database table; if a first fragmentation strategy of the first database table exists in a pre-stored fragmentation strategy set, directly adopting the first fragmentation strategy to perform fragmentation synchronization, and if the first fragmentation strategy does not exist, constructing a candidate segmentation list for the first database table, and the candidate segmentation list comprises an effective segmentation column for the first database table, the plurality of effective segmentation columns are sequenced according to the priority of IO friendliness from high to low; determining an optimal segmentation column of the first database table according to the candidate segmentation list and a pre-stored segmentation column blacklist; and determining a target sampling point on the optimal segmentation column. The method and the device can be applied to synchronization scenes of various types of relational database tables including database tables without primary keys, index tables and digital columns.

Description

Technical Field

[0001] The present application relates to the technical field of database table synchronization, and in particular to a relational database table synchronization method and a terminal device. Background Art

[0002] With the rapid development of informatization, big data, domestic database and other technologies, more and more scenarios require synchronizing one or more database tables from one database (read side) to another database (write side). The read and write ends may be different database software, or they may be deployed in different servers or even computer rooms. After the synchronization is completed, it is necessary to ensure that the corresponding data on both ends are completely consistent.

[0003] In practical applications, we often encounter scenarios where a single table of billions or even larger data needs to be synchronized. To improve efficiency, we generally need to divide the data of the entire table into multiple logical shards (i.e., sharding strategy) based on a certain field of the table (i.e., sharding column), and then use multi-threading or distributed technology to synchronize in parallel according to the shards to meet performance requirements. In this process, the selection of sharding columns and the determination of sharding strategies are particularly critical and are the decisive factors affecting the overall synchronization efficiency. Summary of the invention

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

[0005] In a first aspect, the present application provides a method for synchronizing a relational database table, the method comprising the following steps: 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.

[0006] In this embodiment, the type of the first database table to be synchronized is any type of relational database table, that is, 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. A candidate split list is constructed for the first database table, and the candidate split list includes valid split columns for the first database table, and multiple valid split columns are prioritized from high to low according to IO friendliness. Since the valid split column comes from the first database table, that is, the valid split column is some field of the first database table, therefore, no matter what type of table the first database table is, the IO friendliness of the best split column is the best in the first database table, and then the target sampling point is determined, thereby realizing the shard 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.

[0007] 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.

[0008] This embodiment stores the first sharding strategy formulated in the current synchronization process into the sharding strategy set to ensure that when the current first sharding strategy is a valid sharding strategy, the next time the first database table is synchronized again, the current first sharding strategy can be directly adopted without rebuilding a new first sharding strategy again, thereby improving the efficiency of sharding synchronization.

[0009] In another embodiment, the method further includes: calculating the data skewness of the amount of data synchronized by each shard, and determining whether the data skewness exceeds a preset threshold; if so, adding the optimal sharding column used for this sharding to the sharding column blacklist, and deleting the sharding strategy used for this sharding of the first database table from the sharding strategy set.

[0010] In this embodiment, after the synchronization of the first database table is completed, the data skewness is calculated to determine whether the result of the sharding strategy has the defect of severe data skew. If so, the sharding strategy is added to the blacklist and deleted from the sharding strategy set. When the sharding of the first database table is synchronized next time, the sharding strategy can be avoided to improve the quality of sharding synchronization and avoid some shards or nodes from bearing too much load during the synchronization process, which affects the performance and stability of the system.

[0011] In another embodiment, the method further includes: according to the time of joining the segmentation column blacklist, deleting the segmentation column whose joining time exceeds a preset time threshold, so as to perform rolling update on the segmentation column blacklist.

[0012] Since the data of the first database table changes over time, such as new row data or new column data, the sharding column that is blacklisted during the current shard synchronization may also be the best sharding column during the next shard synchronization. Therefore, in order to avoid misjudgment caused by the update of the first database table data, the blacklist is rolled over to further improve the efficiency and quality of shard synchronization.

[0013] In another embodiment, the data skewness is the ratio of the standard deviation of the amount of data synchronized by the multiple shards to the average value; and the preset threshold is 1.

[0014] In another embodiment, the constructing of the candidate segmentation list for the first database table 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.

[0015] In another embodiment, 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.

[0016] In another embodiment, step S4 is specifically: 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.

[0017] The multiple target data columns in the candidate split list include all the columns of the first database table. The present application obtains the candidate split list by scanning all the columns of the first database table and sorting the scanned valid split columns in the order of IO friendliness from high to low and sharding efficiency from high to low. Therefore, 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 conjunction with the target data columns of the candidate split list and the split column blacklist, it can be ensured that the best split column is screened, and the IO friendliness and sharding efficiency of the best split column are both optimal, that is, the present application can achieve reasonable sharding synchronization for various types of first database tables.

[0018] In another embodiment, step S5 specifically includes: using a preset sampling method, according to a preset sampling point spacing, determining the target sampling point of the first database table; the preset sampling method includes at least one of a fast sampling system function, a window function and a 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 point on the client.

[0019] In another embodiment, the preset sampling methods are, in descending order of priority, fast sampling system functions, window functions, and client sampling in the relational database; and 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.

[0020] The method for determining the target sampling points of the present application and the ordering of the sampling methods from high to low priority are to order and execute them from low to high according to the universality of the sampling methods, so as to improve the sampling efficiency. Although the fast sampling system functions that come with some relational databases have low universality, they have high sampling efficiency for such databases. In addition, although most relational databases do not come with fast sampling system functions, they all support window functions. For such databases, sampling using window functions has high sampling efficiency. For a small number of databases that do not support window functions, sampling can be performed using client sampling, that is, data sorting is completed on the database side, and the selection of target sampling points is completed on the client side. Client sampling has high universality and sacrifices sampling efficiency, so it has low priority, but it also ensures that sampling can be achieved for all types of databases.

[0021] In a second aspect, the present application further provides a terminal device, including a memory and a processor, wherein the memory stores a computer program, and when the processor executes the computer program, the steps of the method described in any one of the embodiments of the first aspect are implemented.

[0022] In a third aspect, the present application further provides a computer-readable storage medium having a computer program stored thereon, and when the computer program is executed by a processor, the steps of the method described in any one of the embodiments of the first aspect above are implemented.

[0023] In a fourth aspect, the present application further provides a computer program product, including a computer program, which, when executed by a processor, implements the steps of the method described in any one of the embodiments of the first aspect above.

[0024] Beneficial effect: The embodiment of the present application constructs a candidate split list for the first database table, the candidate split list includes multiple valid split columns of the first database table, and the multiple valid split columns are all from the first database table and are sorted in order of IO friendliness from high to low priority, and then combined with a pre-stored split column blacklist, the unsuitable valid split columns in the candidate split list are excluded, so that regardless of the type of the first database table, an optimal split column with the best IO friendliness can be determined in order of priority, that is, the embodiment of the present application has no restriction on the type of the first database table, that is, the first database table can be a table with a primary key, an index table or a digital column, or a database table without a primary key, an index table or a digital column, and the optimal split column with the best IO friendliness of the current first database table can be confirmed, and then the target sampling point is determined, thereby realizing the shard 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. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] In order to more clearly illustrate the technical solutions in the embodiments of the present application or related technologies, the drawings required for use in the embodiments of the present application or related technical descriptions will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without paying creative work.

[0026] Figure 1 An application environment diagram of a relational database table synchronization method in one embodiment; Figure 2 A schematic diagram of a flow chart of a method for synchronizing a relational database table in one embodiment; Figure 3 A schematic diagram of a flow chart of a method for synchronizing a relational database table in one embodiment; Figure 4 is a structural block diagram of a relational database table synchronization device in one embodiment; Figure 5 FIG. 4 is a diagram showing the internal structure of a terminal device in an embodiment. DETAILED DESCRIPTION

[0027] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below 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.

[0028] In the scenario of database table synchronization, the key step of the sharding strategy is the selection of sharding columns and sampling points. For the selection of sharding columns and sampling points, conventional techniques include manual specification, linear fitting of value range, and hash-based modulus.

[0029] For example, open source software such as DataX, Kettle, and SeaTunnel do not provide good enough algorithms to automatically determine the split columns. In the application, the split columns and sampling points need to be manually specified, which is inefficient. The method of manually specifying split columns is acceptable in scenarios with a small number of tables, but when hundreds or even hundreds of thousands of tables need to be processed, the low efficiency of its implementation is bound to be unacceptable. On the other hand, choosing the right split column requires operators to have a deep database foundation and understand the principles of database implementation. Otherwise, if the wrong split column is selected, not only will the efficiency of data synchronization fail to be improved, but it may cause a large number of random accesses to the database on the read side, affecting the stability of normal business. In other words, the method of manually specifying split columns and sampling points is unacceptable for large-scale database table synchronization scenarios, as it places high demands on operators and is inefficient.

[0030] The basic idea of ​​the range linear fitting method is to use the aggregation functions in the SQL standard (usually MIN and MAX functions) to find the minimum and maximum values ​​of the split columns, and then use (upper_bound- lower_bound) / N as the step size to generate sampling points according to the linear distribution fitting, where N is the expected number of shards; then, based on the sampling points obtained by the fitting, sharding and data synchronization are performed in the manner of interval filtering. However, the range fitting method can only be used for splitting and sampling of numeric columns, and is not compatible with non-integer type split columns. It cannot be used in binary, string (especially multi-byte character set) and other types. In addition, since its sampling points are based on linear distribution fitting, there may be a large discrepancy with the actual situation, so there may be a serious data skew problem.

[0031] Data skew refers to a situation in which the amount of data, query requests, or computing load on some shards or nodes in a sharded system is much higher than that on other shards or nodes. The amount of data in some shards far exceeds that in other shards, resulting in greater storage pressure on these shards. In some cases, the size of the shards may be inconsistent, resulting in the amount of data in some shards being much larger than that in other shards, causing some shards or nodes to bear too much load, affecting the performance and stability of the system.

[0032] Based on the hash modulus method, the hash value of the split column is used, and then modulo N is used to perform sharding and parallel replication simultaneously. However, when querying shards, a large number of random IOs are often generated. This method not only fails to achieve the purpose of improving synchronization efficiency, but is also prone to database jitter. Random IO (Random I / O) refers to the read and write operations on the disk or storage device occurring at discontinuous physical locations. Compared with sequential IO (Sequential I / O), the characteristic of random IO is that the address of each read and write operation is not continuous, but is scattered throughout the storage space. For mechanical hard disks, random IO requires frequent movement of the magnetic head, and for solid-state drives, it is necessary to frequently search for different blocks, which usually leads to higher latency and lower throughput. Therefore, random IO should be avoided during the sharding of database tables.

[0033] The relational database table synchronization method provided in the embodiment of the present application can be applied to 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 it can be placed on the cloud or other network servers. Among them, the client 102 can be, but is not limited to, various personal computers, laptops, smart phones, tablets, Internet of Things devices and portable wearable devices. The Internet of Things devices can be smart speakers, smart TVs, smart air conditioners, smart car devices, projection devices, etc. 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, or a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides cloud computing services.

[0034] The sharding and synchronization operations in the database synchronization process are mainly performed by each node in the database cluster. Each node is responsible for storing and managing part of the 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 sharding and synchronization operations. The main responsibilities of the client include initiating query and write requests, processing shard perception and shard rebalancing, monitoring and logging, etc.

[0035] In an exemplary embodiment, Figure 2 As shown, Figure 2 The following is a flow chart of a method for synchronizing a relational database table in an embodiment, wherein the method comprises the following steps: 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.

[0036] The first database table may be a database table with a partition column, a primary key, an index, and / or a numeric column (hereinafter referred to as a commonly used table), or may be a database table without a partition column, a primary key, an index, or a numeric column (hereinafter referred to as an uncommon table), and the first database table may be a database table of any amount of data, such as a large table. In database design, although tables without primary keys, indexes, and numeric columns are not best practices, they may also appear in certain specific scenarios. Such tables generally lack the best features of structured data management, such as unique constraints, fast query performance, and efficient sorting capabilities. Tables without partition columns, primary keys, indexes, and 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, and the like. In addition to processing commonly used tables, this embodiment can also process these uncommon tables for shard synchronization.

[0037] Step S2: Determine from a pre-stored sharding strategy set whether there is a first sharding strategy for the first database table; if there is the first sharding strategy, proceed to step S6; if there is no the 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.

[0038] Specifically, the cached sharding strategy is loaded to obtain a pre-stored sharding strategy set. The sharding strategy set can be stored in the cache of the data storage system. These sharding strategy sets include multiple sharding strategies, and each sharding strategy corresponds to a database table. It is understandable that as the system runs, the data content and data volume in the first database table are updated and changed, and the first database table also needs to be synchronized multiple times in 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 executing this sharding synchronization, it is necessary to determine whether there is a first sharding strategy for the current first database table in the pre-stored sharding strategy set.

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

[0040] Step S7: synchronize the multiple shard data tables until the synchronization of the first database table is completed.

[0041] If the pre-stored sharding strategy set includes a first sharding strategy adapted to the first database table, the first database table is directly sharded and synchronized according to multiple target sampling points in the first strategy to improve sharding synchronization efficiency.

[0042] During the shard synchronization process, in this embodiment, a multi-threaded synchronous execution method or a distributed execution method is preferably adopted to execute the synchronization process of multiple shards in parallel to improve the synchronization efficiency.

[0043] Wherein, if the pre-stored sharding strategy set does not include the first sharding strategy adapted to the current first database table, in this embodiment, the first sharding strategy is directly reconstructed, such as Figure 2 As shown, the steps of formulating the first sharding strategy include steps S3 to S5: Step 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; The valid split columns are certain fields in the first database table that can be used as split columns to split the first database table. The valid split columns may include some fields (i.e., some columns) of the first database table, or may include all fields (i.e., all columns) of the first database table.

[0044] The embodiment of the present application preferably scans all columns of the first database table, and sorts the scanned valid split columns in descending order of IO friendliness to obtain a candidate split list. That is, according to the order of priority of the candidate split list from high to low, the best split column determined must be the split column with the best IO friendliness in the current first database table, thereby reducing the generation of random IO during shard query.

[0045] Step S4: determining the best segmentation column for the first database table according to the candidate segmentation list and the pre-stored segmentation column blacklist; In the embodiment of the present application, a corresponding candidate segmentation list and a segmentation column blacklist are respectively set for each database table, that is, the database table and the candidate segmentation list and the segmentation column blacklist are in a one-to-one relationship. The segmentation column blacklist in the embodiment of the present application can be generated according to the requirements of the sharding quality such as the data inclination, data consistency, and synchronization delay after sharding, for example, the segmentation column whose data inclination exceeds the preset threshold is added to the segmentation column blacklist, and / or, the segmentation column whose data consistency is lower than the preset threshold, and / or the segmentation column whose synchronization delay exceeds the preset threshold is added to the segmentation column blacklist. This embodiment does not limit the setting method of the segmentation column blacklist.

[0046] By setting a shard column blacklist, shard columns with low shard quality are avoided when determining the best shard column, which improves the quality of shard synchronization results to a certain extent.

[0047] Step 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.

[0048] The sharding strategy is the target sampling point on the optimal split column of the database table, and the target sampling point is the sampling point on the optimal split column. The sharding execution process is to split the rows of the database table with the rows where the multiple target sampling points are located as the boundary. 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 300,000th row of the 5th column, and point B is the intersection of the 600,000th row of the 5th column. When synchronized, it can be divided into 3 shards, that is, data with a row number less than or equal to 300,000 rows is divided into shard 1, data with a row number greater than 300,000 rows and less than or equal to 600,000 rows is divided into shard 2, and data with a row number greater than 600,000 rows is divided into shard 3. When sharding, it is necessary to ensure that the amount of data in each shard is as consistent as possible. Generally, sharding is performed by evenly dividing according to the number of rows to avoid data skew.

[0049] You can choose a suitable method to determine the target sampling point based on the characteristics of the database itself. For most relational databases, window functions are supported, so you can use window functions to determine the target sampling point. For example, based on the data volume of the first database table and the efficiency and quality of shard synchronization, you can sample a target sampling point every 1 million rows. The window function code is as follows: SELECT v FROM ( SELECT { field_name} AS v, ROW_NUMBER() OVER (ORDER BY { field_name} ASC) AS rn FROM { table_name} )t WHERE t.rn % 1000000 = 0 For some relational databases that have built-in fast sampling system functions, you can use the database's built-in system functions for sampling. For example, for Oracle databases, you can use Oracle rowid for sampling. Taking sampling one target sampling point for every 1 million rows as an example, the code is as follows: BEGIN DBMS_PARALLEL_EXECUTE.create_task("task"); DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid("task", {schema},{table}, true, 1000000); END For a small number of databases that do not support window functions, client sampling can be used for sampling, that is, the best split column is determined and data is sorted on the database side, and the target sampling point is selected on the client side.

[0050] The relational database synchronization method disclosed in the embodiments of the present application is applicable to various types of relational database tables, is fully compatible with database tables without primary keys, index tables, or numeric columns, has low constraints on the data types of columns in the database table, supports sharding using strings (including multi-byte character sets), binary, and other types of fields as segmentation columns, and can cover most relational database synchronization scenarios.

[0051] Furthermore, 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 prioritized from high to low in IO friendliness, therefore, no matter what type of table the first database table is, the IO friendliness of the best split column is the best in the first database table, and then the target sampling point is determined, thereby realizing the shard 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 embodiment of the present application determines the best split column in a fully automated manner, without or with very few scenarios requiring manual intervention, and realizes the automatic synchronization of various types of relational database tables.

[0052] The flowchart of the relational database table synchronization method disclosed in other embodiments of the present application is as follows: Figure 3 Said, comprising the following steps: S201: Acquire a first database table to be synchronized, where the type of the first database table is any type of relational database table; S202: Determine whether there is a first sharding strategy for the first database table from a pre-stored sharding strategy set; if there is, proceed to step S212; if there is no first sharding strategy, formulate a first sharding strategy for the first database table, wherein the sharding strategy set includes target sampling points on the optimal splitting columns for each database table; S212: Acquire target sampling points according to the first sharding strategy, and shard the first database table to obtain multiple sharding data tables; S213: Synchronize multiple shard data tables until the synchronization of the first database table is completed.

[0053] The above steps are similar to the steps in the previous embodiment, and this embodiment will not be described in detail. The process of formulating the first sharding strategy in this embodiment includes steps S203 to S210. Among them, the method of constructing a candidate sharding list for the first database table in this embodiment includes the following steps: Step S203: Scan the first database table to obtain multiple target data columns, where the target data columns include a partition column, the first column in the primary key, the first column of each index, and at least one of other data columns that can be used as valid partition columns.

[0054] Step S204: Prioritize the target data columns according to the order of IO friendliness from high to low and sharding efficiency from high to low to obtain the candidate sharding list.

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

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

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

[0058] In the index access scenario, when the database uses the index for query, the index structure (such as B+ tree) usually stores the data in different pages or blocks. Therefore, a large amount of random IO may be generated during the query process, especially in the case of multi-level indexes, the amount of random IO is even greater. Therefore, for the first database table containing the index, the first column of each index is added to the candidate segmentation list in the embodiment of the present application.

[0059] It should be noted that there are multiple types of first database tables. In order to adapt to various types of first database tables, this application takes into account the situations of various types of database tables when constructing the candidate segmentation list.

[0060] Specifically, in actual execution, the method is as follows: Scan all columns of the first database table. If the first database table contains a partition column (partition), add the partition column to the candidate split list with the highest priority. If the first database table contains a primary key, the first column of the primary key is added to the candidate split list; If the first database table contains indexes, then the first column in each index is added to the candidate split list; At the same time, all columns in the first database table are scanned and sorted according to data type. 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 (hereinafter referred to as sorting method 2); for target data columns of the same data type, the priority order from high to low is: data column containing non-null constraints > data column allowed to be null (hereinafter referred to as sorting method 3).

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

[0062] For example, if the first database table contains partition columns, primary key columns, and index columns, the partition columns, the first column of the primary key, and the first column of the index will be prioritized in the top three positions in sequence when sorting. If the first database table does not contain partition columns but only contains primary key columns and index columns, the first column of the primary key and the first column of the index will be prioritized in the top two positions in sequence. If the first database table does not contain partition columns and primary key columns but only contains index columns, the first column of the index will be prioritized in sequence, 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 partition columns, primary key columns, or index columns, all columns of the first database table only need to be sorted according to sorting methods 2 and 3 when sorting.

[0063] Specifically, the comprehensive sorting methods 2 and 3 may be that, if the first database table contains multiple numeric type columns, multiple string columns, etc., the multiple numeric type columns are first sorted according to sorting method 3, and then the multiple string columns are sorted according to sorting method 3, and so on.

[0064] After the candidate segmentation list is constructed, the best segmentation column is selected. Specifically, in another embodiment of the present application, this step includes: Step S205 : Scan the target data columns in the candidate segmentation list in order of priority from high to low, that is, scan all the columns in the candidate segmentation list in sequence.

[0065] Step S206, determining whether the currently scanned target data column is in the segmentation column blacklist, if so, returning to step S205, continuing to scan the next target data column, if not, entering step S207, determining the currently scanned target data column as the optimal segmentation column.

[0066] The embodiment of the present application obtains a candidate sharding list by scanning all columns of the first database table and sorting all columns in order of IO friendliness from high to low and sharding efficiency from high to low. Therefore, 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 conjunction with the target data columns of the candidate sharding list and the sharding column blacklist, it can be ensured that the best sharding column is screened, and the IO friendliness and sharding efficiency of the best sharding column are both optimal, that is, the embodiment of the present application can achieve reasonable sharding synchronization of various types of first database tables.

[0067] After determining the best split column, the sampling point selection process is entered. In another embodiment of the present application, the sampling point selection process is to use a preset sampling method and determine the target sampling points of the first database table according to the preset sampling point spacing. As described in the above embodiment 1, the sampling method is related to the sampling method 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 best split column data determined by the database side to the client, and determine the target sampling point at the client.

[0068] The preset sampling methods are arranged in order of priority from high to low, and the fast sampling system functions, window functions and client sampling in the relational database are arranged in order of priority from high to low. The sampling methods are arranged in order of universality from low to high. Among these sampling methods, the method with low universality has high sampling efficiency, and the higher the universality, the lower the sampling efficiency.

[0069] In the embodiment of the present application, the sampling point selection process specifically includes the following steps: Step S208: In descending order of sampling mode priority, the first database table is sampled using a corresponding sampling mode and a preset sampling interval. The sampling interval may be any interval, which is mainly set to avoid data skew. For database tables of different types and different data volumes, the preset sampling intervals may be the same or different, which is not limited in this embodiment.

[0070] Step S209, determine the execution result of the current sampling method, that is, determine whether the current sampling method is executed successfully. If the current sampling method fails, return to step S208 and continue to use the next sampling method to sample the first database table; if the current sampling method is successful, enter step S210 and output the target sampling point of the first database table.

[0071] After the target sampling point is output, step S212 and step S213 may be entered to perform sharding and synchronization processes on the first database according to the target sampling point. In this embodiment, multi-threading or distributed parallel execution of the sharding and synchronization processes is preferably adopted.

[0072] That is, if the first database table supports the fast sampling system function, the fast sampling system function is used directly to determine the sampling point; if the first database table does not support the fast sampling system function, the window function is used to determine the sampling point; 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 point. The client sampling method does not have any 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 point on the client according to the sampling interval and the number of shards.

[0073] The above method of determining the optimal split column and target sampling point takes into account both IO friendliness and sharding efficiency.

[0074] Another embodiment of the present application further includes step S211, after completing the process of determining the optimal split column and the target sampling point, 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.

[0075] This embodiment stores the first sharding strategy formulated in the current synchronization process into the sharding strategy set to ensure that when the current first sharding strategy is a valid sharding strategy, the next time the first database table is synchronized again, the current first sharding strategy can be directly adopted without rebuilding a new first sharding strategy again, thereby improving the efficiency of sharding synchronization.

[0076] In another embodiment of the present application, after the shard synchronization process for the first database is completed, a shard synchronization quality analysis process is further included, and the process includes the following steps: Step S214: Calculate the data skewness of the data volume synchronized by each shard.

[0077] There are many ways to calculate data skewness. In this embodiment, the data skewness is the ratio of the standard deviation of the data volume synchronized by the multiple shards to the average value. The calculation formula of data skewness is as follows: The actual amount of data processed by each shard is Di, n is the number of shards, and μ is the average amount of data in n shards.

[0078] Step S215: determine whether the data inclination exceeds a preset threshold, if so, proceed to step S216, add the optimal split column used in this sharding to the split column blacklist. In this embodiment, the preset threshold of the data inclination is 1.

[0079] Step S217: deleting the sharding strategy used for the current sharding of the first database table from the sharding strategy set.

[0080] In this embodiment, after the synchronization of the first database table is completed, the data skewness is calculated to determine whether the result of the sharding strategy has the defect of serious data skew. If so, the sharding column used in this sharding is added to the sharding column blacklist, and the sharding strategy is deleted from the sharding strategy set. When the sharding of the first database table is synchronized next time, the sharding strategy can be avoided to improve the quality of sharding synchronization and avoid some shards or nodes from bearing too much load during the synchronization process, which affects the performance and stability of the system.

[0081] In the embodiment of the present application, a sharding strategy set may be maintained for multiple database tables to be synchronized, wherein the sharding strategy set includes a sharding strategy for each database table to be synchronized, and the storage method may be to associate and store the identifier of the database table to be synchronized with the corresponding sharding strategy. In other words, in the present embodiment, a corresponding sharding strategy is maintained for each database table.

[0082] In addition, the embodiment of the present application also maintains a corresponding sharding column blacklist for each database table to be synchronized. In other embodiments, the method further includes: according to the time of joining the sharding column blacklist, deleting the sharding columns whose time of joining the sharding column blacklist exceeds the preset time threshold, so as to perform rolling updates on the sharding column blacklist. The preset time threshold can be set according to the type of database table to be synchronized, the amount of data, the update rule, or the sharding synchronization rule of the database table, so as to adapt to the sharding synchronization requirements of different database tables in different scenarios.

[0083] Since the data of the first database table changes over time, such as new row data or new column data, the sharding column that is blacklisted during the current shard synchronization may also be the best sharding column during the next shard synchronization. Therefore, in order to avoid misjudgment caused by the update of the first database table data, the blacklist is rolled over to further improve the efficiency and quality of shard synchronization.

[0084] Since the update logic and update rules of the sharding strategy and the sharding column blacklist are different, the sharding strategy and the sharding column blacklist in this embodiment are stored separately and updated independently, which is convenient for independent maintenance without interfering with each other, thereby ensuring the stable operation of the system.

[0085] Other embodiments of the present application also disclose a relational database table synchronization device, which is used to execute the relational database table synchronization method described in any of the above embodiments. The structure diagram of the device is as follows: Figure 4 As shown, it includes the following modules: The storage module 10 is used to store a set of sharding strategies corresponding to multiple database tables and a sharding column blacklist corresponding to each database table. Each database table corresponds to a sharding strategy and a sharding column blacklist.

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

[0087] The first search module 12 is used to determine whether there is a first sharding strategy for the first database table from a pre-stored sharding strategy set. The first sharding strategy building module 20 is used to build a matching first sharding strategy for the first database table when the storage module 10 does not contain the first sharding strategy corresponding to the first database table. The first sharding strategy building module 20 includes: The candidate segmentation list construction module 13 is used to construct a candidate segmentation list for the first database table, wherein the candidate segmentation list includes multiple valid segmentation columns in the first database table, and the multiple valid segmentation columns are sorted in order of priority from high to low IO friendliness.

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

[0089] The target sampling point determination module 15 is used to determine the target sampling point of the first database table, where the target sampling point is located on the optimal segmentation column of the first database table.

[0090] In addition, the device also includes a sharding module 16, which is used to obtain target sampling points according to the first sharding strategy, shard the first database table to obtain multiple sharding data tables. A synchronization module 17 is used to synchronize the multiple sharding data tables until the synchronization of the first database table is completed.

[0091] In an exemplary embodiment, a terminal device is provided. The terminal device may be a server. A database cluster inside the server has a node responsible for synchronizing database table shards. The internal structure diagram of the server may be as follows: Figure 5 As shown. The terminal device includes a processor, a memory, an input / output interface (Input / Output, referred to 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 an external device. The communication interface of the terminal device is used to interact with an external client through an API or SQL interface. When the computer program is executed by the processor, the relational database synchronization method described in the above embodiment is implemented.

[0092] Those skilled in the art will understand that Figure 5 The structure shown in the figure is only a block diagram of a partial structure related to the scheme of the present application, and does not constitute a limitation on the terminal device to which the scheme of the present 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 a different arrangement of components.

[0093] 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, the steps in the above-mentioned method embodiments are implemented.

[0094] 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 used 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 must comply with relevant regulations.

[0095] A person of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment method can be completed by instructing the relevant hardware through a computer program, and 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-mentioned methods. Among them, any reference to the 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), magnetic 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. As an illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM). The database involved in each embodiment provided in this application may include at least one of a relational database and a non-relational database. Non-relational databases may include distributed databases based on blockchains, etc., but are not limited to this. The processor involved in each embodiment provided in this application may be a general-purpose processor, a central processing unit, a graphics processor, a digital signal processor, a programmable logic device, a data processing logic device based on quantum computing, an artificial intelligence (AI) processor, etc., but are not limited to this.

[0096] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, 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, they should be considered to be within the scope of this application.

[0097] The above-described embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the present application. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the attached 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