Distribution strategy recommendation method, device and storage medium for cross-database migration

Abstract

This invention relates to the field of database technology, and discloses a method, device, and storage medium for recommending distribution strategies for cross-database migration. The method includes the following steps: constructing a table group association topology graph based on table relationships; assigning dynamically configurable weights to table evaluation metrics and calculating a table group score; and determining a table group distribution strategy based on the table group association topology graph and the table group score. This invention can significantly improve the storage and computing load balancing in a distributed environment, support dynamic adaptation to complex business scenarios, support real-time impact analysis of strategy changes and rollback in any dimension, and reduce manual intervention costs and migration risks.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method for recommending distribution strategies for cross-database migration. Background Technology

[0002] With the development of online businesses, the concurrency of business access is increasing exponentially, and the demand for massive data computing and analysis is becoming increasingly common. Traditional single-machine systems face enormous challenges in terms of business operations, cost, and openness. The emergence of distributed databases can better meet these challenges. Therefore, distributed databases are gaining popularity, data interaction with other databases is increasing, and data migration to distributed databases is becoming more and more common. To ensure data integrity, consistency, and standardized operations, migration evaluation systems play a crucial role. For distributed databases, how data is rationally distributed is of significant research importance, and providing reasonable distribution rules is essential for data migration to distributed databases.

[0003] Patent searches revealed very few inventions related to database distribution rules. Currently published patents include methods for generating distribution rule tables based on table information, but these methods rely on limited information, lack reliability, and do not consider how to select distribution methods and keys. There are also intelligent recommendation technologies, but they handle distribution rules for relational tables in a relatively simple way, requiring all relational tables to have common fields, which cannot handle complex relational scenarios. Summary of the Invention

[0004] This application aims to overcome the shortcomings of unbalanced data distribution in existing technologies during cross-database migration, and to provide a distribution strategy recommendation method for cross-database migration based on intelligent decision-making.

[0005] To achieve the objectives of this invention, embodiments of this application provide a method for recommending distribution strategies across database migrations, characterized by comprising: Based on the relationships between the tables, construct a table group relationship topology diagram; The evaluation metrics of the table are assigned weights that can be dynamically configured, and the table group score is calculated. Based on the table group association topology and table group scores, a table group distribution strategy recommendation is implemented.

[0006] To achieve the objectives of this invention, this application also provides an electronic device, including a processor and a memory, wherein the memory stores a program that can run on the processor, characterized in that, when the program is executed by the processor, it implements the steps of the above-described cross-database migration distribution strategy recommendation method.

[0007] To achieve the objectives of this invention, embodiments of this application also provide a computer-readable storage medium storing at least one program, characterized in that the at least one program can be executed by at least one processor to implement the steps of the above-described method for recommending distribution strategies for cross-database migration.

[0008] This application presents a cross-database migration distribution strategy recommendation method. By deeply analyzing the topological relationships between database objects, it constructs a multi-dimensional weighted decision model, automatically generates and optimizes data distribution strategies, effectively solving technical challenges such as the difficulty in determining distribution rules for related tables, data skew, and unbalanced business loads in complex relational scenarios. Compared to traditional manual configuration solutions, this invention can significantly reduce implementation costs and time consumption, and improve migration efficiency. Attached Figure Description

[0009] Figure 1 A schematic diagram illustrating the logic of cross-database data migration; Figure 2 This is a schematic diagram of the distribution strategy recommendation method for cross-database migration in Example 1; Figure 3 This is a flowchart illustrating step S101 of the method for recommending a distribution strategy for cross-database migration in Example 1. Figure 4 This is a flowchart illustrating step S102 of the method for recommending a distribution strategy for cross-database migration in Example 1. Figure 5 This is a flowchart illustrating step S103 of the method for recommending a distribution strategy for cross-database migration in Example 1. Figure 6 This is a flowchart illustrating step S104 of the method for recommending a distribution strategy for cross-database migration in Example 1. Detailed Implementation

[0010] To make the technical solution of the present invention easier to understand, the technical solution of the present invention will be further described in detail below with reference to specific embodiments and accompanying drawings. Obviously, the following embodiments are merely some embodiments of the present invention and do not constitute a limitation on the technical solution of the present invention. For those skilled in the art, other embodiments can be obtained based on the embodiments of this application without creative effort.

[0011] It should be noted that the order of steps described in the method embodiments below does not constitute a limitation on the technical solution of the present invention. For those skilled in the art, some steps can be reasonably adjusted according to the embodiments of this application without creative effort.

[0012] Example 1 This embodiment provides a distribution strategy recommendation method for cross-database migration, applicable to, for example... Figure 1 The database migration assessment system shown. Figure 1 The functions of each module are shown in Table 1: Table 1. Functions of the Database Migration Assessment System Modules

[0013] The data acquisition module collects basic information from the source database, primarily including the basic structure information of the source tables, their storage status, business models, and related relationships. The information conversion module categorizes the information collected by the data acquisition module. Due to the cross-database migration involved, lexical analysis is required. The information conversion module converts the source syntax and lexical data into syntax and lexical data executable on the target database, and then passes the converted statements to the data analysis module for processing. The data analysis module simulates execution of the statements and provides modification suggestions, filtering out supported and unsupported business logic. The distribution rule module recommends distribution rules for supported databases and tables based on the configured distribution rule strategy, providing pre-distribution details for each table and the target database load analysis results after pre-distribution. The data playback module then writes the above results into a structured format, completing the final process of the migration assessment system and supporting the preliminary work for cross-database migration assessment.

[0014] The cross-database migration distribution strategy recommendation method of this embodiment is applied to the distribution rule module of the migration evaluation system mentioned above. This method generates a load balancing distribution strategy based on multi-dimensional features and provides reasonable distribution rules according to the relationship between data tables, thus solving the problem of difficulty in selecting distribution rules for related tables when migrating data to a distributed database.

[0015] like Figure 2 As shown, the cross-database migration distribution strategy recommendation method in this embodiment includes the following steps: Step S101: Construction of Association Topology: Based on the association relationships of the tables, construct the association topology diagram of the table group.

[0016] Specifically, step S101, based on the foreign key relationships of the data tables, the join relationships obtained through SQL parsing, and the relationships between tables and views / objects / tables through intermediate structures, connects the various tables through edge relationships, using database tables as nodes and each relationship as an edge, to form a table group relationship topology graph. The smallest unit of this relationship topology graph is a table group. Tables within a table group are either directly related or indirectly related through other tables.

[0017] like Figure 3 As shown, step S101 includes: S1011, Graph-based structural modeling, including: Node definition: Database tables are used as nodes; Edge definition: Explicit association, including foreign key constraints (FK) and primary key-foreign key dependencies; Implicit association, including Join relationships in the SQL execution plan (extracted through SQL analysis); Composite association, including table-view call chains and stored procedure-table reference relationships.

[0018] S1012, Table Group Generation: Based on the connected subgraph partitioning method, a set of strongly related tables is defined as a table group; cross-table indirect association identification is supported.

[0019] S1013, Topology Optimization: Dynamically Pruning Weakly Related Edges.

[0020] S1014. Generate a weighted adjacency matrix.

[0021] Step S102, Dynamic Weight Decision: Establish a multi-dimensional feature quantification model, allocate weights to the evaluation indicators of the table in a way that supports dynamic configuration, and calculate the table group score.

[0022] Specifically, since there are many tables in the database, this step prioritizes processing large table groups when handling table distribution rules, and further prioritizes processing large tables within large table groups. This ensures the uniformity of the final results. There are many evaluation metrics for tables, and this step assigns weights to different evaluation metrics. The final score for a single table is calculated using these weights, and the score for a table group is obtained by summing the scores of all the tables it contains.

[0023] like Figure 4 As shown, step S102 includes: S1021. Extraction of feature data for evaluation indicators, including: Data characteristics include table size, number of SQL statements, update frequency, etc. Computational characteristics, including SQL execution cost, transaction complexity, etc.; Association features include foreign key relationships between tables, join query relationships, view / object association relationships, etc. S1022, Weight Allocation Matrix Generation: This function assigns weights to different evaluation metrics in the table, generating a weight allocation matrix. The weight allocation matrix supports dynamic configuration of weight vectors.

[0024] The configurable weighting consists of the sub-items within data characteristics, computational characteristics, and association characteristics. The total weight is 100%. By default, table data size, foreign key relationships, view / object associations, number of SQL queries, and SQL execution cost are given higher weights, while others are given lower weights. The specific weighting can be configured based on the characteristics of your business data.

[0025] S1023, Scoring Calculation and Priority Sorting: Dynamically adjust weights to adapt to OLTP / OLAP hybrid scenarios. After the weight adjustment is completed, calculate the table group score according to the weight, and use a greedy algorithm to sort the table group in descending order according to the score.

[0026] Step S103, Distribution Rule Recommendation: Based on the table group association topology and table group score, execute the table group distribution strategy recommendation process according to the configured strategy preference mode.

[0027] This invention configures three strategy preference modes: Hybrid Sharding Mode: The default mode in this embodiment is a combination of horizontal and vertical sharding. In hybrid sharding mode, a certain proportion of the header table groups are horizontally distributed, while the remaining table groups are vertically distributed.

[0028] Horizontal sharding distributes data evenly across multiple data nodes based on data volume and correlation. In horizontal sharding, data sharding is achieved using both consistent hashing and multi-node replication, maintaining shared sharding for strongly correlated tables.

[0029] In vertical sharding mode, table objects are allocated to the optimal data nodes based on data update frequency and access patterns, while also supporting a dynamic migration mechanism based on real-time load status. In vertical sharding mode, a first-fit algorithm is used to allocate single nodes, and hot node avoidance is supported.

[0030] like Figure 5 As shown, step S103 includes: S1031. Resource estimation: Estimate the number of data nodes and the storage capacity of data nodes, verify the storage capacity, and monitor the remaining space of nodes in real time.

[0031] S1032, Distribution Strategy Execution: Execute the table group distribution strategy recommendation process according to the configured strategy preference mode, including: Vertical distribution: All table groups are polled in descending order, and the entire table group is stored on a single data node; Horizontal distribution: All table groups are polled in descending order, and the tables in each table group are evenly distributed across all data nodes while ensuring that the relationships with larger weights remain open. Mixed distribution: All table groups are sorted from largest to smallest. A certain proportion of the head table groups (configurable, 20% in this example) are processed according to a horizontal distribution, and the remaining table groups are processed according to a vertical distribution.

[0032] During the execution of the above distribution strategy, the status of all data nodes is updated after each table group is processed, which facilitates the selection of data nodes for the next table group.

[0033] S1033, Status Update: Real-time update of node load status (memory / IO / storage), dynamically adjust the distribution strategy of subsequent table groups.

[0034] The cross-database migration distribution strategy recommendation method in this embodiment further includes: Step S104: Strategy iteration optimization and effect verification.

[0035] Specifically, such as Figure 6 As shown, if the table group distribution strategy does not require adjustment, a data distribution rule report is directly output; otherwise, the distribution strategy can be adjusted secondaryly through the configured visualization interface. Secondary adjustments support single-table strategy modification and cross-table group reorganization, enabling secondary optimization of the distribution strategy. After the secondary adjustments for the current batch are completed, a new table group score and distribution strategy are generated. Each adjustment result is recorded as a version, facilitating backtracking and comparison to select the version that meets the requirements.

[0036] This embodiment configures a dual-version comparison engine to automatically perform comparative evaluations of the distribution strategies before and after the secondary adjustment, using configured quantitative indicators to generate a comparison evaluation report to assist users in decision-making and optimization. The quantitative indicators in this embodiment include: storage load balancing, business load balancing, and cross-data node query probability.

[0037] Users choose whether to adopt the adjusted distribution strategy based on the evaluation results after the second adjustment. If adopted, a final data distribution rule report is output; otherwise, the result before adjustment is reverted or a second adjustment is performed. This embodiment supports unlimited strategy rollbacks and automatically generates evaluations of the changing trends of quantitative indicators before and after adjustment.

[0038] The cross-database migration distribution strategy recommendation method in this embodiment achieves intelligent data distribution among target nodes through a multi-dimensional weighted decision model and a multi-mode distribution strategy (horizontal distribution / vertical distribution / hybrid distribution). The measured data skew rate is reduced by more than 30%, the cross-node query overhead is reduced by 20%-40%, the storage space utilization and computing resource allocation efficiency are significantly optimized, and the storage and computing load balancing in a distributed environment is improved.

[0039] The cross-database migration distribution strategy recommendation method in this embodiment adopts a dynamic weight decision architecture. It utilizes a dynamically configurable weight allocation matrix to automatically generate differentiated distribution strategies based on data model features. This solves the deployment challenges of complex data models that traditional tools cannot handle, improves strategy recommendation accuracy, and has the intelligent decision-making capability to dynamically adapt to complex business scenarios. It can provide customized solutions for different business scenarios (such as OLTP / OLAP mixed loads) and effectively address the deployment challenges brought by complex data models.

[0040] The cross-database migration distribution strategy recommendation method in this embodiment constructs an interactive strategy optimization and strategy change full lifecycle management framework. It innovatively introduces a version control mechanism and a dual-version comparison engine, supports real-time impact analysis of strategy changes and rollback of historical distribution schemes in any dimension, shortens the strategy iteration cycle, and makes the decision-making process transparent and traceable, ensuring the best balance between data access locality and load balancing in a distributed environment.

[0041] The cross-database migration distribution strategy recommendation method in this embodiment transforms the traditional migration configuration process that relies on human experience into an intelligent recommendation + lightweight adjustment mode through automated topology analysis, dynamic scoring system and visual interface, thereby reducing the cost of manual intervention and migration risks.

[0042] Example 2 This embodiment provides an electronic device, including a processor and a memory communicatively connected to the processor. The memory stores a program executable on the processor, which, when executed by the processor, implements the steps of the cross-database migration distribution strategy recommendation method provided in the above embodiment.

[0043] The above programs can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, and C++, as well as conventional procedural programming languages ​​such as C or similar languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server.

[0044] Example 3 This embodiment provides a computer-readable storage medium storing at least one program that can be executed by at least one processor to implement the steps of the cross-database migration distribution strategy recommendation method provided in the above embodiment.

[0045] The aforementioned computer-readable storage media include: USB flash drives, portable hard drives, read-only memory, random access memory, magnetic disks, optical disks, and other media and combinations thereof capable of storing program code. With the development of science and technology, the meaning of storage media may become increasingly broad, extending beyond physical media.

[0046] Those skilled in the art will understand that the above embodiments are specific embodiments for implementing the present invention. In addition to the above embodiments, the present invention may have other implementation methods. All technical solutions formed by equivalent substitution or equivalent transformation fall within the protection scope claimed by the present invention.

Claims

1. A method for recommending distribution strategies across database migrations, characterized in that, include: Based on the relationships between the tables, construct a table group relationship topology diagram; The evaluation metrics of the table are assigned weights that can be dynamically configured, and the table group score is calculated. Based on the table group association topology and table group scores, a table group distribution strategy recommendation is implemented.

2. The distribution strategy recommendation method for cross-database migration as described in claim 1, characterized in that, The step of constructing a table group relationship topology graph based on the table relationships includes: The tables are used as nodes in the topology graph, and the relationships between the tables are used as edges. The tables are connected through the relationships between the edges. Based on the connected subgraph partitioning method, a set of strongly associated tables is defined as a table group, which supports the identification of indirect associations across tables; Dynamically trim weakly related edges; Generate a weighted adjacency matrix.

3. The method for recommending distribution strategies for cross-database migration as described in claim 1, characterized in that, The evaluation metrics for the tables are weighted with dynamic configuration to calculate the table group score, including: Extract the characteristic data of the table group, including data characteristics, calculation characteristics, and association characteristics; Generate a weight allocation matrix that supports dynamic configuration; Calculate the table group score based on the weights, and then sort the table groups in descending order based on the table group scores.

4. The method for recommending distribution strategies for cross-database migration as described in claim 1, characterized in that, The step of recommending table group distribution strategies based on table group association topology and table group scores includes: Estimate the number of data nodes and the storage capacity of data nodes, perform storage capacity verification, and monitor the remaining space of nodes in real time; The table group distribution strategy recommendation is executed according to the configured strategy preference mode, which includes vertical distribution mode, horizontal distribution mode and mixed distribution mode.

5. The method for recommending distribution strategies for cross-database migration as described in claim 1, characterized in that, Also includes: If the table group distribution strategy does not need to be adjusted, the data distribution rule report will be output directly; otherwise, it will be adjusted again through the interactive interface. The distribution strategy before and after the second adjustment is evaluated by quantitative indicators. Based on the evaluation results, a decision is made on whether to adopt the second-adjusted distribution strategy. If adopted, the final data distribution rule report is output; otherwise, the result before the second adjustment is reverted or the second adjustment is carried out again.

6. The method for recommending distribution strategies for cross-database migration as described in claim 5, characterized in that, The quantitative indicators include: storage load balancing, business load balancing, and cross-data node query probability.

7. The method for recommending distribution strategies for cross-database migration as described in claim 5, characterized in that, Each adjustment is recorded as a version, and the distribution strategy can be rolled back an unlimited number of times.

8. The method for recommending distribution strategies for cross-database migration as described in claim 1, characterized in that, The relationships include: foreign key relationships in tables, join query relationships obtained through SQL parsing, and relationships associated through intermediate structures.

9. An electronic device comprising a processor and a memory, the memory storing a program executable on the processor, characterized in that, When the program is executed by the processor, it implements the steps of the distribution strategy recommendation method for cross-database migration as described in any one of claims 1-8.

10. A computer-readable storage medium storing at least one program, characterized in that, The at least one program may be executed by at least one processor to implement the steps of the distribution strategy recommendation method for cross-database migration as described in any one of claims 1-8.

Images