Data distribution strategy generation method, device and system based on association cluster and medium

Abstract

The invention discloses a data distribution strategy generation method, device and system based on an association cluster and a medium, and the method comprises the steps: collecting historical SQL statements of a source centralized database, and extracting corresponding inter-table association information according to the historical SQL statements; constructing a corresponding association graph according to the association information between the tables, and performing graph clustering processing on the association graph to obtain a plurality of strong association table clusters; performing fragmentation key compatibility analysis and fragmentation cost analysis on each strong association table cluster, and generating a joint distribution strategy of each strong association table cluster according to an analysis result; according to the reference features of the remaining non-cluster table, a corresponding supplementary distribution strategy is confirmed; and outputting a table establishment script of the target distributed database according to the joint distribution strategy and the supplementary distribution strategy. The corresponding distribution strategy is automatically generated based on association cluster joint optimization, so that the cross-fragment operation of the distributed database during association is reduced, and the association performance of the distributed database and the service operation efficiency are improved.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, apparatus, system, and medium for generating data distribution strategies based on association clusters. Background Technology

[0002] The most obvious feature of distributed databases compared to centralized databases is the distributed storage of data. When migrating business from a centralized database to a distributed database, the design of the data distribution strategy is crucial. Existing distribution methods include four types: hash, list, range, and replication. Among them, hash and replication are more widely used.

[0003] When using hash distribution, a data distribution strategy must be specified. Existing distributed databases typically use primary keys or unique keys, which are fields with distinctiveness, directly when designing hash distribution strategies. This distribution strategy can lead to a situation where, after migrating from a centralized database to a distributed database, join operations need to pull data across shards to the computing nodes of the distributed database for computation, resulting in a large amount of network overhead and computational latency, which severely restricts the performance advantages of distributed databases. Summary of the Invention

[0004] In view of the shortcomings of the prior art, the purpose of this invention is to provide a method, apparatus, system and medium for generating data distribution strategies based on association clusters that can be applied to financial technology or other related fields. The aim is to automatically generate optimized data distribution strategies based on association clusters, solve the problem of performance degradation of cross-shard associations caused by unreasonable data sharding during the migration of centralized databases to distributed architectures, and improve the performance of distributed databases.

[0005] The technical solution of the present invention is as follows: A method for generating a data distribution strategy based on associated clusters includes: Collect historical SQL statements from the source centralized database, and extract the corresponding table relationship information based on the historical SQL statements; Based on the inter-table association information, a corresponding association graph is constructed, and graph clustering is performed on the association graph to obtain several strongly associated table clusters; For each strongly associated table cluster, perform sharding key compatibility analysis and sharding cost analysis, and generate a joint distribution strategy for each strongly associated table cluster based on the analysis results; Based on the reference characteristics of the remaining non-clustered internal tables, determine the corresponding supplementary distribution strategy; Based on the joint distribution strategy and the supplementary distribution strategy, output the table creation script for the target distributed database.

[0006] In one embodiment, the step of collecting historical SQL statements from a centralized database and extracting corresponding table relationship information based on the historical SQL statements includes: Collect historical SQL logs from the source centralized database to obtain historical SQL statements; Identify the JOIN operations in the historical SQL statements, and extract the corresponding related table pairs, related field pairs, related frequency, and average execution time based on the JOIN operations as the inter-table relationship information.

[0007] In one embodiment, the step of constructing a corresponding association graph based on the inter-table association information, and performing graph clustering processing on the association graph to obtain several strongly associated table clusters includes: A corresponding association graph is constructed using the associated tables in the inter-table association information as nodes and the associated field pairs as weighted edges. The edge weights of the weighted edges are calculated based on the association frequency and the average execution time. The association graph is clustered and divided according to a preset graph clustering algorithm to obtain several strongly associated table clusters.

[0008] In one embodiment, the step of performing sharding key compatibility analysis and sharding cost analysis on each strongly correlated table cluster, and generating a joint distribution strategy for each strongly correlated table cluster based on the analysis results, includes: For each strongly associated table cluster, all associated field pairs are traversed, the association strength score of each associated field is calculated, and fields with association strength scores greater than a preset threshold are selected to form a corresponding candidate distribution key set. For each strongly associated table cluster, perform a sharding key compatibility analysis on the candidate distribution key set to obtain a global sharding key or a candidate sharding key combination. Based on the data distribution statistics of each field, the candidate sharding key combinations are simulated and evaluated in a preset dimension to obtain the overall sharding cost of each candidate sharding key combination. A joint distribution strategy is generated for each strongly associated table cluster based on the global sharding key or the candidate sharding key combination with the lowest sharding comprehensive cost.

[0009] In one embodiment, the step of performing sharding key compatibility analysis on the candidate distribution key set for each strongly associated table cluster, and filtering to obtain global sharding keys or candidate sharding key combinations, includes: Confirm whether there exists a target field in the candidate distribution key set of each strongly related table cluster that can connect any two tables through a foreign key path. If so, identify the target field as the global sharding key. If it does not exist, count the frequency of occurrence of each associated g field within the cluster in the inter-table association path and the number of tables covered; Based on the frequency of occurrence of each associated field and the number of tables covered, a greedy strategy is used to select candidate sharding key combinations containing several fields. The proportion of associated edges jointly covered by all associated fields in the candidate sharding key combinations is greater than or equal to a preset proportion threshold.

[0010] In one embodiment, the step of performing a preset-dimensional simulation evaluation of the candidate sharding key combinations based on the data distribution statistics of each field to obtain the overall sharding cost of each candidate sharding key combination includes: Based on the statistical analysis of the data distribution of each field, simulations were conducted to evaluate and obtain the cross-shard JOIN ratio, data skew coefficient, and storage redundancy increment of the candidate shard key combinations. The cross-shard JOIN ratio, data skew coefficient, and storage redundancy increment are weighted and summed according to budget weights to obtain the overall sharding cost for each candidate sharding key combination.

[0011] In one embodiment, determining the corresponding supplementary distribution strategy based on the reference characteristics of the remaining non-clustered internal tables specifically includes: Confirm whether the table size of the remaining non-clustered tables is less than a preset value and whether there are references to them more than a specified number of times. If so, use a full table replication strategy as the supplementary distribution strategy; otherwise, use a primary key hash distribution or a range distribution as the supplementary distribution strategy.

[0012] A data distribution strategy generation device based on association clusters, comprising: The data acquisition and extraction module is used to acquire historical SQL statements from the source centralized database and extract corresponding inter-table relationship information based on the historical SQL statements. The graph clustering module is used to construct a corresponding association graph based on the inter-table association information, and to perform graph clustering processing on the association graph to obtain several strongly associated table clusters. The first strategy generation module is used to perform sharding key compatibility analysis and sharding cost analysis on each strongly associated table cluster, and generate a joint distribution strategy for each strongly associated table cluster based on the analysis results. The second strategy generation module determines the corresponding supplementary distribution strategy based on the reference characteristics of the remaining non-cluster internal tables. The output module is used to output the table creation script of the target distributed database according to the joint distribution strategy and the supplementary distribution strategy.

[0013] A data distribution strategy generation system based on association clusters, the system comprising at least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, which enables the at least one processor to perform the above-described data distribution strategy generation method based on associated clusters.

[0014] A non-volatile computer-readable storage medium stores computer-executable instructions that, when executed by one or more processors, cause the one or more processors to perform the aforementioned data distribution strategy generation method based on associative clusters.

[0015] Beneficial Effects: This invention discloses a method, apparatus, system, and medium for generating data distribution strategies based on relational clusters. Compared to existing technologies, this invention collects historical SQL statements from a source centralized database and extracts corresponding inter-table relationship information based on these statements. It then constructs a relational graph based on this information and performs graph clustering to obtain several strongly related table clusters. For each strongly related table cluster, it performs sharding key compatibility analysis and sharding cost analysis, generating a joint distribution strategy for each cluster based on the analysis results. Based on the reference characteristics of the remaining non-cluster tables, it confirms corresponding supplementary distribution strategies. Finally, based on the joint and supplementary distribution strategies, it outputs the table creation script for the target distributed database. By automatically generating corresponding distribution strategies based on relational cluster joint optimization, it reduces cross-shard operations during distributed database associations, improving the association performance and business operation efficiency of the distributed database. Attached Figure Description

[0016] The present invention will be further described below with reference to the accompanying drawings and embodiments. In the accompanying drawings: Figure 1 A flowchart of a data distribution strategy generation method based on associated clusters provided in an embodiment of the present invention; Figure 2 This is a flowchart of step S101 in the data distribution strategy generation method based on associated clusters provided in the embodiments of the present invention; Figure 3 This is a flowchart of step S102 in the data distribution strategy generation method based on associated clusters provided in an embodiment of the present invention; Figure 4 This is a flowchart of step S303 in the data distribution strategy generation method based on associated clusters provided in an embodiment of the present invention; Figure 5 This is a flowchart of step S402 in the data distribution strategy generation method based on associated clusters provided in an embodiment of the present invention; Figure 6This is a flowchart of step S403 in the data distribution strategy generation method based on associated clusters provided in the embodiments of the present invention; Figure 7 This is a schematic diagram of the functional modules of the data distribution strategy generation device based on associated clusters provided in an embodiment of the present invention; Figure 8 This is a schematic diagram of the hardware structure of a data distribution strategy generation system based on associated clusters provided in an embodiment of the present invention. Detailed Implementation

[0017] To make the objectives, technical solutions, and effects of this invention clearer and more explicit, the invention is further described in detail below. It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention. The embodiments of the invention are described below in conjunction with the accompanying drawings.

[0018] Currently, when using hash distribution, a data distribution strategy needs to be specified. Existing distributed databases typically use primary keys or unique keys, which are fields with distinctiveness, directly when designing hash distribution strategies. This distribution strategy leads to the need for cross-shard data retrieval to the distributed database's computing nodes for computation after migrating business from a centralized database to a distributed database. This results in significant network overhead and computational latency, severely limiting the performance advantages of distributed databases.

[0019] To address the aforementioned problems, this invention proposes a data distribution strategy generation method based on associated clusters, such as... Figure 1 As shown, the data distribution strategy generation method based on associated clusters provided in this embodiment includes the following steps: S101. Collect historical SQL statements from the source centralized database and extract the corresponding table relationship information based on the historical SQL statements.

[0020] In this embodiment, the source centralized database is the centralized database currently running in the business system. By adapting the log collection method of this database, complete historical SQL statements are obtained, ensuring coverage of SQL execution records across various business scenarios. Based on the collected historical SQL statements, the inter-table join operations are identified and extracted to form inter-table join information. This information must include the relationships between tables, the fields involved in the join, the frequency of join operations, and the execution time of the join, providing foundational data support for subsequent join graph construction. By accurately obtaining core business join characteristics through inter-table join information, data support is provided for subsequent table cluster partitioning and strategy generation, avoiding blind sharding.

[0021] S102. Construct a corresponding association graph based on the inter-table association information, and perform graph clustering on the association graph to obtain several strongly associated table clusters.

[0022] In this embodiment, tables in the inter-table relationship information are used as nodes in the relationship graph, and the relationships between tables are used as edges. Each edge is assigned a weight based on the frequency of association and execution time, constructing a table-field level relationship graph that visually represents the strength of inter-table relationships. The relationship graph is then subjected to graph clustering processing. For example, a preset graph clustering algorithm is used to divide the constructed relationship graph into communities. Through iterative optimization, tables with close relationships and strong business relevance are aggregated together to form several strongly related table clusters. Inter-table relationships within each strongly related table cluster are frequent and close, while inter-cluster table relationships are relatively sparse, providing a logical unit partitioning basis for subsequent targeted distribution strategies. By constructing the relationship graph and clustering to obtain strongly related table clusters, the limitations of single-table optimization are broken, grouping closely related tables into one unit, laying the foundation for multi-table collaborative sharding, and reducing cross-shard relationships at the source.

[0023] S103. Perform sharding key compatibility analysis and sharding cost analysis on each strongly associated table cluster, and generate a joint distribution strategy for each strongly associated table cluster based on the analysis results.

[0024] In this embodiment, for each strongly related table cluster, the compatibility of the fields within the cluster as a unified sharding key is first analyzed. That is, it is determined whether there are fields (or combinations of fields) that can adapt to the join requirements of most tables within the cluster, thus clarifying the candidate range of sharding keys. At the same time, a sharding cost analysis is performed on the candidate sharding keys or combinations. The sharding cost is evaluated from different dimensions such as the rationality of data distribution, the efficiency of join operations, and resource consumption, comprehensively considering the advantages and disadvantages of different candidate schemes.

[0025] Based on the sharding key compatibility analysis results and sharding cost assessment conclusions, the optimal sharding method and sharding key are selected to generate a joint distribution strategy for each strongly related table cluster. This ensures that the distribution of table data within the cluster can maximize the localization of join operations, ensures that the sharding key adapts to the join requirements of the table cluster, and balances evaluation indicators such as cross-shard JOIN, data skew, and storage redundancy to maximize the localization rate of joins within the cluster.

[0026] S104. Based on the reference characteristics of the remaining non-clustered internal tables, confirm the corresponding supplementary distribution strategy.

[0027] In this embodiment, the remaining non-clustered tables refer to isolated tables that are not assigned to any strongly related table clusters. By analyzing the reference characteristics of these tables, including the frequency of references by other tables or business SQL, the size of the table's data volume, and other key information, corresponding supplementary distribution standards are determined. Specifically, corresponding feature judgment standards can be preset to classify and adapt each non-clustered table. For tables that meet specific reference characteristics (such as frequent references and small data volume), a distribution strategy adapted to their access requirements is adopted; for other types of non-clustered tables, an independent distribution strategy adapted to their storage and access characteristics is adopted, forming a complete supplementary distribution strategy system to ensure that all tables have an adapted distribution scheme. Through differentiated design strategies for isolated tables, small table replication improves query efficiency, and large table independent sharding saves storage, balancing performance and resource utilization.

[0028] S105. Based on the joint distribution strategy and the supplementary distribution strategy, output the table creation script for the target distributed database.

[0029] In this embodiment, the target distributed database can be a mainstream distributed database such as TiDB or OceanBase. Based on the joint distribution strategy of the cluster to which each table belongs, and the supplementary distribution strategy of each remaining non-cluster table, a table creation script for the target distributed database is generated. This table creation script includes key information such as the shard key definition and table structure definition. For example, for the customers table in cluster A, the generated table creation script specifies the shard key as customer_id and the distribution method as hash distribution; for the region_dict table with full table replication, the script indicates the replication strategy; for the system_log table, the shard key is specified as log_id and the distribution method is hash distribution. After the table creation script is output, it can be directly executed in the target distributed database to complete the creation of data tables and the configuration of data distribution, realizing a smooth migration from a centralized database to a distributed database, automating the migration process, eliminating the need for manual design of sharding rules, and lowering the threshold for distributed database migration.

[0030] The data distribution strategy generation method based on association clusters provided in this embodiment uses a multi-table collaborative sharding design to automatically generate corresponding distribution strategies based on the joint optimization of association clusters. This reduces cross-sharding operations when the distributed database performs associations, improves the association performance and business operation efficiency of the distributed database, significantly reduces cross-node communication overhead, and enhances the performance of complex queries. It is suitable for high-concurrency and strongly associated business scenarios such as finance and e-commerce, and provides key technical support for the smooth migration and efficient operation of distributed databases.

[0031] In one embodiment, such as Figure 2 As shown, step S101 includes: S201. Collect historical SQL logs from the source centralized database to obtain historical SQL statements; S202. Identify the JOIN operation in the historical SQL statement, and extract the corresponding related table pairs, related field pairs, related frequency, and average execution time based on the JOIN operation as the inter-table association information.

[0032] In this embodiment, the source centralized database can be a centralized database such as Oracle or MySQL used in business scenarios such as finance and e-commerce. Historical SQL logs of the source centralized database are collected to ensure coverage of all business-related scenarios and obtain the corresponding historical SQL statements. Specifically, this can be achieved through the database's built-in log export tool, third-party log collection components, etc. The collected content can include key information such as SQLID, SQL details, execution timestamp, execution frequency, average execution time, number of returned rows, and number of scanned rows, providing data support for subsequent extraction of related information.

[0033] The collected historical SQL statements are then parsed line by line by a syntax parser to identify statements containing the JOIN keyword. For each SQL statement containing a JOIN operation, the related table pairs (e.g., table1 and table2) and related field pairs (e.g., table1.col1 and table2.col2) are extracted, and the execution timestamp and execution time of the SQL statement are recorded. The parsing results are statistically analyzed to calculate the corresponding join frequency and average execution time. The join frequency is the total number of times the same combination of related table pairs and related field pairs appears in the historical SQL statements; the average execution time (avg_cost) is the sum of the execution times of all execution records for this combination divided by the join frequency. For example, if the parsed table pair (table_A, table_B) and the associated field pair (column_A, column_B) appear 8000 times, and the total execution time is 400000ms, then the association frequency is 8000 times and the average execution time is 50ms. This information is used to construct a structured association record (table_A, column_A, table_B, column_B, frequency, avg_cost), which is then stored as the corresponding inter-table association information.

[0034] This embodiment ensures the completeness, accuracy, and timeliness of inter-table relationship information through a standardized historical SQL collection process and precise extraction of relationship information. This addresses the problem of unreasonable strategy design caused by missing relationship data and provides high-quality input data for subsequent relationship graph construction and cluster analysis.

[0035] In one embodiment, such as Figure 3 As shown, step S102 includes: S301. Construct a corresponding association graph using the associated tables in the inter-table association information as nodes and the associated field pairs as weighted edges. The edge weights of the weighted edges are calculated based on the association frequency and the average execution time. S302. The association graph is clustered and divided according to a preset graph clustering algorithm to obtain several strongly associated table clusters.

[0036] In this embodiment, when constructing the association graph, the associated tables in the inter-table association information are used as nodes, and the associated field pairs are used as weighted edges. The edge weight is calculated as weight = association frequency. The higher the average execution time and weight value, the higher the business value and the greater the performance impact of the relationship between the tables. The relationship graph constructed in this way can intuitively reflect the strength of the relationship between the tables.

[0037] The association graph is then clustered according to a preset graph clustering algorithm, specifically the Louvain algorithm, which can efficiently identify highly cohesive and loosely coupled community structures, adapting to the partitioning requirements of strongly associated table clusters. In the graph clustering process, each table is first initialized as an independent community. Then, the modularity (an indicator of community partitioning quality) is iteratively calculated, and communities that can improve the modularity are continuously merged until the modularity reaches its maximum value or no longer increases. Each resulting community is a strongly associated table cluster. For example, cluster A contains the tables customers, orders, order_items, and products, and cluster B contains the tables employees and departments. At this point, the tables within a cluster are closely associated, while the associations between clusters are sparse.

[0038] This embodiment achieves efficient and accurate partitioning of strongly associated table clusters by employing weighted association graphs and Louvain clustering algorithm, ensuring that the associations within clusters are tight and the associations between clusters are sparse, thereby reflecting the strength of the associations between tables and providing a reliable logical partitioning basis for the subsequent generation of joint distribution strategies.

[0039] In one embodiment, such as Figure 4 As shown, step S103 includes: S401. Traverse all pairs of related fields in each strongly related table cluster, calculate the association strength score of each related field, filter out fields with association strength scores greater than a preset threshold, and form a corresponding candidate distribution key set. S402. Perform sharding key compatibility analysis on the candidate distribution key set for each strongly associated table cluster, and filter to obtain global sharding keys or candidate sharding key combinations. S403. Based on the data distribution statistics of each field, perform a simulation evaluation of the candidate sharding key combinations in a preset dimension to obtain the sharding comprehensive cost of each candidate sharding key combination. S404. Generate a joint distribution strategy for each strongly associated table cluster based on the global sharding key or the candidate sharding key combination with the lowest sharding comprehensive cost.

[0040] In this embodiment, for each strongly correlated table cluster, the fields participating in high-frequency associations are first screened to form a candidate distribution key set. Specifically, all tables and their associated records in the current strongly correlated table cluster are traversed, and the association strength score of each associated field is calculated. The association strength score is the sum of the products of frequency and avg_cost of the associated field in all associated records. If the association strength score of a certain associated field is greater than a preset threshold, the associated field is added to the candidate distribution key set until the traversal is completed and a complete candidate distribution key set is obtained.

[0041] Next, a sharding key compatibility analysis is performed on each strongly related table cluster. Specifically, this involves analyzing whether candidate fields can be used across all tables within the cluster. If such fields exist, they are directly used as the global sharding key; otherwise, multiple fields are combined to form candidate sharding key combinations. Through compatibility analysis, a global sharding key that can be used across all tables within the cluster is quickly identified, ensuring that the sharding key can directly achieve localized joins across all tables within the cluster, thus avoiding cross-shard joins at the source. If no global sharding key exists, candidate sharding key combinations are filtered based on dimensions such as field frequency and the number of tables covered, ensuring that the combined key can maximize the coverage of join scenarios within the cluster. This provides a highly adaptable core candidate set for subsequent cost optimization, avoiding a disconnect between traditional sharding key selection and inter-table join requirements.

[0042] Then, based on the data distribution statistics of each field (such as value range, value frequency distribution, etc.), a simulated evaluation of the candidate sharding key combinations is performed in preset dimensions. That is, the cluster table is virtually sharded according to the candidate sharding key combinations, and the corresponding cost indicators are calculated from preset dimensions by combining the data distribution statistics of each field, and then the overall sharding cost is obtained by summarizing. Through multi-dimensional sharding simulation evaluation, the abstract sharding effect is transformed into quantifiable indicator data, thereby replacing the traditional subjective judgment that relies on experience, ensuring that the evaluation of candidate sharding key combinations is more objective and accurate.

[0043] Based on the sharding key compatibility analysis results and the overall sharding cost, if a global sharding key exists, it is directly used as the sharding key. The distribution method (such as Hash distribution or Range distribution) is selected based on the supported types of the distributed database, generating a joint distribution strategy. For example, if the global sharding key for cluster A is `customer_id`, and the target distributed database supports Hash distribution, the joint distribution strategy is "Hash distribution with `customer_id` as the sharding key." If no global sharding key exists, all candidate sharding key combinations are traversed, and the combination with the lowest overall sharding cost is selected as the optimal sharding key combination. This, combined with the distribution method, generates a joint distribution strategy. By generating a joint distribution strategy using the global sharding key or the candidate sharding key combination with the lowest overall cost as the core, cluster-wide association localization or a balance between performance and cost can be directly achieved. This ensures that most association operations within the cluster can be completed within shards, significantly reducing cross-node communication overhead and significantly improving the response speed of complex association queries.

[0044] In one embodiment, such as Figure 5 As shown, step S402 includes: S501. Confirm whether there is a target field in the candidate distribution key set of each strongly related table cluster that can connect any two tables through a foreign key path. If so, identify the target field as the global sharding key. S502. If it does not exist, count the frequency of occurrence of each related field in the inter-table association path and the number of tables covered. S503. Based on the frequency of occurrence and the number of tables covered by each associated field, a candidate sharding key combination containing several fields is selected using a greedy strategy. The proportion of associated edges jointly covered by all associated fields in the candidate sharding key combination is greater than or equal to a preset proportion threshold.

[0045] In this embodiment, when evaluating the compatibility of sharding keys, it is first confirmed whether there is a target field in the candidate distribution key set of each strongly related table cluster that can connect any two tables through a foreign key path, that is, whether there is a field that allows any two tables in the cluster to be connected through the foreign key reference path of this field. If so, the field is determined as the global sharding key.

[0046] If no target field exists in the candidate distribution key set that can span all tables, the frequency of occurrence and the number of tables covered by each associated field in the inter-table association path are counted. The specific inter-table association path is generated based on the edges and nodes in the association graph. All inter-table association paths within the cluster (such as table 1-table 2, table 1-table 3, table 2-table 4, table 3-table 4, etc.) are traversed, and the number of occurrences of each associated field in these paths (i.e., occurrence frequency) and the number of different tables involved in the association edge of the field (i.e., the number of covered tables) are counted. Based on a greedy strategy, several fields are selected such that the proportion of association edges jointly covered is greater than or equal to a preset proportion threshold. The selected fields are combined to form a candidate sharding key combination. The specific execution logic of the greedy strategy is as follows: first, the field with the highest occurrence frequency and the largest number of covered tables is selected as the first field of the combination. Then, the field that can maximize the proportion of association edge coverage is added to the combination. This process is repeated until the proportion of association edges jointly covered reaches the preset proportion threshold.

[0047] This embodiment identifies global sharding keys that can be used across all tables within a cluster and uses a greedy strategy to select candidate sharding key combinations for compatibility analysis. This filters out fields that are incompatible with the table cluster association logic in advance, narrowing down the range of candidate sharding keys, avoiding cost assessments for invalid fields, and reducing unnecessary computational overhead. This ensures that the subsequent joint distribution strategy generated based on this can guarantee the coordination of table data distribution within the cluster from the bottom layer, ensuring that most association operations can be completed within the shards.

[0048] In one embodiment, such as Figure 6 As shown, step S403 includes: S601. Based on the data distribution statistics of each field, simulate and evaluate to obtain the cross-shard JOIN ratio, data skew coefficient and storage redundancy increment of the candidate sharding key combination; S602. The cross-shard JOIN ratio, data skew coefficient, and storage redundancy increment are weighted and summed according to budget weights to obtain the overall sharding cost of each candidate sharding key combination.

[0049] In this embodiment, when evaluating the sharding cost for each candidate sharding key combination, the cross-sharding JOIN ratio, data skew coefficient, and storage redundancy increment are simulated and evaluated based on the data distribution statistics of each field, transforming the abstract sharding effect into quantifiable metrics. Specifically, when evaluating the cross-sharding JOIN ratio, the cluster table is virtually sharded based on the candidate sharding key combination (e.g., a preset sharding number of shards of 16). Combining the data value range and distribution range of each field, the proportion of cross-sharding executions in all join operations within the cluster is statistically analyzed. For example, after virtual sharding of the candidate sharding key combination (col1, col3), 8 out of 100 join operations are executed across shards, so the cross-sharding JOIN ratio is 8%. When evaluating the data skew coefficient, the value frequency histogram of each candidate field (i.e., the data volume distribution corresponding to different values ​​of the field) is obtained to simulate data distribution under 16 shards. Calculate the data volume of each shard, using "maximum shard load / average load" as the data skew coefficient. For example, if the maximum shard data volume after simulation is 80GB and the average shard data volume is 50GB, then the data skew coefficient = 80 / 50 = 1.6. When evaluating the storage redundancy increment, if the candidate shard key combination needs to achieve join localization through table replication or field redundancy, then the increment is calculated based on the table size and redundancy range. For example, if a table is 10GB in size and needs to be replicated to 8 shards, then the storage redundancy increment = 10GB × (8-1) = 70GB. If no replication or redundancy is required, then the storage redundancy increment is 0.

[0050] Then, the quantified values ​​of the three dimensions are summed according to preset weights to obtain the comprehensive cost of the sharding that integrates the factors of different evaluation dimensions and their importance. The preset weights are set according to business needs. For example, for high-concurrency and low-latency businesses, the weight of the cross-sharding JOIN ratio can be increased, and for scenarios with tight storage resources, the weight of storage redundancy increment can be increased, etc. The specific weights can be set according to actual needs, and this embodiment does not limit them.

[0051] This embodiment takes into account the performance of related queries, the rationality of data distribution, and the cost of resource consumption through multiple evaluation dimensions, and realizes the quantitative decision-making of sharding strategy. It avoids the bias of subjective experience judgment and the defects caused by single-dimensional optimization, such as only pursuing a low cross-shard ratio while ignoring storage waste, and ensures that the generated sharding strategy achieves the optimal balance between performance and resource cost.

[0052] In one embodiment, step S104 specifically includes: Confirm whether the table size of the remaining non-clustered tables is less than a preset value and whether there are references to them more than a specified number of times. If so, use a full table replication strategy as the supplementary distribution strategy; otherwise, use a primary key hash distribution or a range distribution as the supplementary distribution strategy.

[0053] In this embodiment, for isolated tables not assigned to any strongly related table clusters (i.e., remaining non-clustered tables), their reference characteristics, including table size (i.e., data storage volume) and reference count (i.e., the number of times they are referenced by other tables or SQL statements), can be obtained by querying database metadata and runtime logs. The reference characteristics of each remaining non-clustered table are assessed. If the table size is less than a preset value and there are more than a specified number of references, a full table replication strategy is adopted to replicate the table's data to all shard nodes of the distributed database. This ensures that other tables do not need to fetch data across shards during join queries, improving query efficiency. If the table size is greater than or equal to the preset value, or there are no more than a specified number of references, a primary key hash distribution or range distribution is used. By using a dual criterion based on table size and reference count, non-clustered tables are differentiated for adaptation. This ensures that small tables with high-frequency references are fully replicated, reducing cross-shard access, while large tables with low-frequency references are independently sharded, avoiding storage waste. This approach guarantees the query performance of isolated tables while making reasonable use of storage resources.

[0054] It should be noted that there is no necessary order between the above steps. Those skilled in the art will understand from the description of the embodiments of the present invention that the above steps may have different execution orders in different embodiments, that is, they may be executed in parallel or in turn, etc.

[0055] Another embodiment of the present invention provides a data distribution strategy generation device based on associated clusters, such as Figure 7 As shown, device 1 includes: A data distribution strategy generation device based on association clusters, comprising: The data acquisition and extraction module 11 is used to acquire historical SQL statements from the source centralized database and extract corresponding table relationship information based on the historical SQL statements. Graph clustering module 12 is used to construct a corresponding association graph based on the inter-table association information, and perform graph clustering processing on the association graph to obtain several strongly associated table clusters; The first strategy generation module 13 is used to perform sharding key compatibility analysis and sharding cost analysis on each strongly associated table cluster, and generate a joint distribution strategy for each strongly associated table cluster based on the analysis results. The second strategy generation module 14 determines the corresponding supplementary distribution strategy based on the reference characteristics of the remaining non-cluster internal tables. Output module 15 is used to output the table creation script of the target distributed database according to the joint distribution strategy and the supplementary distribution strategy.

[0056] The module referred to in this invention is a series of computer program instruction segments that can perform specific functions. It is more suitable than a program for describing the execution process of generating a data distribution strategy based on association clusters. For specific implementation methods of each module, please refer to the corresponding method embodiments above, which will not be repeated here.

[0057] Another embodiment of the present invention provides a data distribution strategy generation system based on associated clusters, such as Figure 8 As shown, system 10 includes: One or more processors 110 and memory 120, Figure 8 The following description uses a processor 110 as an example. The processor 110 and the memory 120 can be connected via a bus or other means. Figure 8 Taking the example of a connection between China and Israel via a bus.

[0058] Processor 110 is used to perform various control logics of system 10, and can be a general-purpose processor, digital signal processor (DSP), application-specific integrated circuit (ASIC), field-programmable gate array (FPGA), microcontroller, ARM (Acorn RISC Machine) or other programmable logic device, discrete gate or transistor logic, discrete hardware components, or any combination of these components. Furthermore, processor 110 can also be any conventional processor, microprocessor, or state machine. Processor 110 can also be implemented as a combination of computing devices, such as a combination of DSP and microprocessor, multiple microprocessors, one or more microprocessors combined with DSP and / or any other such configuration.

[0059] The memory 120, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer-executable programs, and modules, such as the program instructions corresponding to the data distribution strategy generation method based on association clusters in this embodiment of the invention. The processor 110 executes various functional applications and data processing of the system 10 by running the non-volatile software programs, instructions, and units stored in the memory 120, thereby implementing the data distribution strategy generation method based on association clusters in the above method embodiment.

[0060] The memory 120 may include a program storage area and a data storage area. The program storage area may store the operating system and applications required for at least one function; the data storage area may store data created according to the use of the system 10. Furthermore, the memory 120 may include high-speed random access memory and may also include non-volatile memory, such as at least one disk storage device, flash memory device, or other non-volatile solid-state storage device. In some embodiments, the memory 120 may optionally include memory remotely located relative to the processor 110, and these remote memories may be connected to the system 10 via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0061] One or more units are stored in memory 120, and when executed by one or more processors 110, perform the following steps: Collect historical SQL statements from the source centralized database, and extract the corresponding table relationship information based on the historical SQL statements; Based on the inter-table association information, a corresponding association graph is constructed, and graph clustering is performed on the association graph to obtain several strongly associated table clusters; For each strongly associated table cluster, perform sharding key compatibility analysis and sharding cost analysis, and generate a joint distribution strategy for each strongly associated table cluster based on the analysis results; Based on the reference characteristics of the remaining non-clustered internal tables, determine the corresponding supplementary distribution strategy; Based on the joint distribution strategy and the supplementary distribution strategy, output the table creation script for the target distributed database.

[0062] In one embodiment, the step of collecting historical SQL statements from a centralized database and extracting corresponding table relationship information based on the historical SQL statements includes: Collect historical SQL logs from the source centralized database to obtain historical SQL statements; Identify the JOIN operations in the historical SQL statements, and extract the corresponding related table pairs, related field pairs, related frequency, and average execution time based on the JOIN operations as the inter-table relationship information.

[0063] In one embodiment, the step of constructing a corresponding association graph based on the inter-table association information, and performing graph clustering processing on the association graph to obtain several strongly associated table clusters includes: A corresponding association graph is constructed using the associated tables in the inter-table association information as nodes and the associated field pairs as weighted edges. The edge weights of the weighted edges are calculated based on the association frequency and the average execution time. The association graph is clustered and divided according to a preset graph clustering algorithm to obtain several strongly associated table clusters.

[0064] In one embodiment, the step of performing sharding key compatibility analysis and sharding cost analysis on each strongly correlated table cluster, and generating a joint distribution strategy for each strongly correlated table cluster based on the analysis results, includes: For each strongly associated table cluster, all associated field pairs are traversed, the association strength score of each associated field is calculated, and fields with association strength scores greater than a preset threshold are selected to form a corresponding candidate distribution key set. For each strongly associated table cluster, perform a sharding key compatibility analysis on the candidate distribution key set to obtain a global sharding key or a candidate sharding key combination. Based on the data distribution statistics of each field, the candidate sharding key combinations are simulated and evaluated in a preset dimension to obtain the overall sharding cost of each candidate sharding key combination. A joint distribution strategy is generated for each strongly associated table cluster based on the global sharding key or the candidate sharding key combination with the lowest sharding comprehensive cost.

[0065] In one embodiment, the step of performing sharding key compatibility analysis on the candidate distribution key set for each strongly associated table cluster, and filtering to obtain global sharding keys or candidate sharding key combinations, includes: Confirm whether there exists a target field in the candidate distribution key set of each strongly related table cluster that can connect any two tables through a foreign key path. If so, identify the target field as the global sharding key. If it does not exist, count the frequency of occurrence of each associated g field within the cluster in the inter-table association path and the number of tables covered; Based on the frequency of occurrence of each associated field and the number of tables covered, a greedy strategy is used to select candidate sharding key combinations containing several fields. The proportion of associated edges jointly covered by all associated fields in the candidate sharding key combinations is greater than or equal to a preset proportion threshold.

[0066] In one embodiment, the step of performing a preset-dimensional simulation evaluation of the candidate sharding key combinations based on the data distribution statistics of each field to obtain the overall sharding cost of each candidate sharding key combination includes: Based on the statistical analysis of the data distribution of each field, simulations were conducted to evaluate and obtain the cross-shard JOIN ratio, data skew coefficient, and storage redundancy increment of the candidate shard key combinations. The cross-shard JOIN ratio, data skew coefficient, and storage redundancy increment are weighted and summed according to budget weights to obtain the overall sharding cost for each candidate sharding key combination.

[0067] In one embodiment, determining the corresponding supplementary distribution strategy based on the reference characteristics of the remaining non-clustered internal tables specifically includes: Confirm whether the table size of the remaining non-clustered tables is less than a preset value and whether there are references to them more than a specified number of times. If so, use a full table replication strategy as the supplementary distribution strategy; otherwise, use a primary key hash distribution or a range distribution as the supplementary distribution strategy.

[0068] This invention provides a non-volatile computer-readable storage medium storing computer-executable instructions that are executed by one or more processors, for example, to perform the operations described above. Figure 1 The method steps S101 to S105.

[0069] As examples, non-volatile storage media can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) as external cache memory. By way of illustration and not limitation, RAM can be obtained in many forms such as synchronous RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), dual data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), Synchlink DRAM (SLDRAM), and direct Rambus RAM (DRRAM). The memory components or memories disclosed in the operating environment described herein are intended to include one or more of these and / or any other suitable types of memory.

[0070] In summary, the data distribution strategy generation method, apparatus, system, and medium based on association clusters disclosed in this invention involve: collecting historical SQL statements from a source centralized database and extracting corresponding inter-table relationship information based on these statements; constructing a corresponding association graph based on the association information; performing graph clustering on the association graph to obtain several strongly associated table clusters; performing sharding key compatibility analysis and sharding cost analysis on each strongly associated table cluster; generating a joint distribution strategy for each strongly associated table cluster based on the analysis results; confirming corresponding supplementary distribution strategies based on the reference characteristics of the remaining non-cluster tables; and outputting the table creation script for the target distributed database based on the joint distribution strategy and the supplementary distribution strategies. By automatically generating corresponding distribution strategies based on association cluster joint optimization, the cross-shard operations during distributed database association are reduced, improving the association performance and business operation efficiency of the distributed database.

[0071] Of course, those skilled in the art will understand that all or part of the processes in the above embodiments can be implemented by a computer program instructing related hardware (such as a processor, controller, etc.). The computer program can be stored in a non-volatile, computer-readable storage medium, and when executed, it can include the processes described in the above method embodiments. The storage medium can be a memory, magnetic disk, floppy disk, flash memory, optical storage, etc.

[0072] It should be understood that the application of the present invention is not limited to the examples above. Those skilled in the art can make improvements or modifications based on the above description, and all such improvements and modifications should fall within the protection scope of the appended claims.

Claims

1. A method for generating a data distribution strategy based on associated clusters, characterized in that, include: Collect historical SQL statements from the source centralized database, and extract the corresponding table relationship information based on the historical SQL statements; Based on the inter-table association information, a corresponding association graph is constructed, and graph clustering is performed on the association graph to obtain several strongly associated table clusters; For each strongly associated table cluster, perform sharding key compatibility analysis and sharding cost analysis, and generate a joint distribution strategy for each strongly associated table cluster based on the analysis results; Based on the reference characteristics of the remaining non-clustered internal tables, determine the corresponding supplementary distribution strategy; Based on the joint distribution strategy and the supplementary distribution strategy, output the table creation script for the target distributed database.

2. The data distribution strategy generation method based on associated clusters according to claim 1, characterized in that, The collection source is a centralized database containing historical SQL statements, and the corresponding table relationship information is extracted based on these historical SQL statements, including: Collect historical SQL logs from the source centralized database to obtain historical SQL statements; Identify the JOIN operations in the historical SQL statements, and extract the corresponding related table pairs, related field pairs, related frequency, and average execution time based on the JOIN operations as the inter-table relationship information.

3. The data distribution strategy generation method based on associated clusters according to claim 2, characterized in that, The step involves constructing a corresponding association graph based on the inter-table association information, and then performing graph clustering processing on the association graph to obtain several strongly associated table clusters, including: A corresponding association graph is constructed using the associated tables in the inter-table association information as nodes and the associated field pairs as weighted edges. The edge weights of the weighted edges are calculated based on the association frequency and the average execution time. The association graph is clustered and divided according to a preset graph clustering algorithm to obtain several strongly associated table clusters.

4. The data distribution strategy generation method based on associated clusters according to claim 2, characterized in that, The step involves performing sharding key compatibility analysis and sharding cost analysis on each strongly correlated table cluster, and generating a joint distribution strategy for each strongly correlated table cluster based on the analysis results, including: For each strongly associated table cluster, all associated field pairs are traversed, the association strength score of each associated field is calculated, and fields with association strength scores greater than a preset threshold are selected to form a corresponding candidate distribution key set. For each strongly associated table cluster, perform a sharding key compatibility analysis on the candidate distribution key set to obtain a global sharding key or a candidate sharding key combination. Based on the statistical distribution of data in each field, a simulation evaluation of the candidate sharding key combinations is performed in a preset dimension to obtain the overall sharding cost of each candidate sharding key combination. A joint distribution strategy is generated for each strongly associated table cluster based on the global sharding key or the candidate sharding key combination with the lowest sharding comprehensive cost.

5. The data distribution strategy generation method based on associated clusters according to claim 4, characterized in that, The step of performing sharding key compatibility analysis on the candidate distribution key set for each strongly associated table cluster, and filtering to obtain global sharding keys or candidate sharding key combinations, includes: Confirm whether there exists a target field in the candidate distribution key set of each strongly related table cluster that can connect any two tables through a foreign key path. If so, identify the target field as the global sharding key. If it does not exist, count the frequency of occurrence of each associated g field within the cluster in the inter-table association path and the number of tables covered; Based on the frequency of occurrence of each associated field and the number of tables covered, a greedy strategy is used to select candidate sharding key combinations containing several fields. The proportion of associated edges jointly covered by all associated fields in the candidate sharding key combinations is greater than or equal to a preset proportion threshold.

6. The data distribution strategy generation method based on associated clusters according to claim 4, characterized in that, The step of performing a preset-dimensional simulation evaluation of the candidate sharding key combinations based on the data distribution statistics of each field to obtain the comprehensive sharding cost of each candidate sharding key combination includes: Based on the statistical analysis of the data distribution of each field, simulations were conducted to evaluate and obtain the cross-shard JOIN ratio, data skew coefficient, and storage redundancy increment of the candidate shard key combinations. The cross-shard JOIN ratio, data skew coefficient, and storage redundancy increment are weighted and summed according to budget weights to obtain the overall sharding cost for each candidate sharding key combination.

7. The data distribution strategy generation method based on associated clusters according to claim 1, characterized in that, The step of determining the corresponding supplementary distribution strategy based on the reference characteristics of the remaining non-clustered internal tables specifically includes: Confirm whether the table size of the remaining non-clustered tables is less than a preset value and whether there are references to them more than a specified number of times. If so, use a full table replication strategy as the supplementary distribution strategy; otherwise, use a primary key hash distribution or a range distribution as the supplementary distribution strategy.

8. A data distribution strategy generation device based on association clusters, characterized in that, include: The data acquisition and extraction module is used to acquire historical SQL statements from the source centralized database and extract corresponding inter-table relationship information based on the historical SQL statements. The graph clustering module is used to construct a corresponding association graph based on the inter-table association information, and to perform graph clustering processing on the association graph to obtain several strongly associated table clusters. The first strategy generation module is used to perform sharding key compatibility analysis and sharding cost analysis on each strongly associated table cluster, and generate a joint distribution strategy for each strongly associated table cluster based on the analysis results. The second strategy generation module determines the corresponding supplementary distribution strategy based on the reference characteristics of the remaining non-cluster internal tables. The output module is used to output the table creation script of the target distributed database according to the joint distribution strategy and the supplementary distribution strategy.

9. A data distribution strategy generation system based on association clusters, characterized in that, The system includes at least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor to enable the at least one processor to perform the data distribution strategy generation method based on association clusters as described in any one of claims 1-7.

10. A non-volatile computer-readable storage medium, characterized in that, The non-volatile computer-readable storage medium stores computer-executable instructions that, when executed by one or more processors, cause the one or more processors to perform the data distribution strategy generation method based on any one of claims 1-7.

Images