Method and device for optimizing migration from Oracle database to TiDB database

Abstract

The invention discloses a migration optimization method and device from an Oracle database to a TiDB database. The method comprises the steps that related performance data of the Oracle database and related performance data of the TiDB database are collected; the execution plans of the Oracle database and the TiDB database are analyzed; the execution plans of the Oracle database and the TiDB database are compared through a preset mathematical model; the difference index obtained by comparison is determined, and when the difference index exceeds a predetermined threshold value, optimization corresponding to the difference index is performed. According to the method, automatic optimization, dynamic self-adaptive resource allocation and automatic capacity expansion based on the execution plan can be realized, manual intervention can be reduced, the migration efficiency and the database performance can be improved, the performance of the migrated database is ensured to be optimal, and dynamic resource expansion is ensured to be performed in the migration process so as to avoid the performance bottleneck.

Description

Technical Field

[0001] This invention relates to the field of database migration and performance optimization technology, and in particular to a method and apparatus for migrating and optimizing Oracle databases to TiDB databases. Background Technology

[0002] This section is intended to provide background or context for the embodiments of the invention set forth in the claims. The description herein is not an admission that it is prior art simply because it is included in this section.

[0003] With the deepening development of enterprise informatization, database migration has gradually become an inevitable task. TiDB, as a distributed database, has been increasingly adopted by enterprises due to its superior performance and scalability. However, during database migration, especially when migrating from traditional relational databases (such as Oracle) to TiDB, problems such as manual tuning, improper configuration, and excessive resource consumption arise. Existing migration solutions mostly rely on manual configuration and lack dynamic, real-time performance optimization mechanisms.

[0004] Current database migration solutions primarily rely on manually configuring database resources (such as CPU, memory, and storage) and adjusting query execution plans. While some tools support automated migration, they are often limited to data migration and do not provide automatic performance tuning or real-time resource optimization based on actual load. Existing solutions typically cannot achieve dynamic self-adjustment after database migration and lack flexible performance optimization methods, resulting in migrated databases often not being able to undergo adaptive syntax optimization in a short period of time.

[0005] Existing database migration solutions have the following problems:

[0006] 1. Lack of automatic tuning mechanism: Currently, most optimization during the migration process relies on manual configuration. The performance of the migrated database may not be able to adapt to load fluctuations, resulting in uneven utilization of database resources.

[0007] 2. High degree of manual intervention: Existing solutions require significant manual intervention in execution plan optimization and resource allocation. This is especially true during migrations between different databases (such as Oracle and TiDB), where manual adjustments are labor-intensive and prone to errors.

[0008] 3. Performance not meeting standards after migration: Existing migration solutions cannot automatically optimize according to the actual workload of the database, resulting in frequent performance bottlenecks in the migrated database, and even affecting the normal operation of business. Summary of the Invention

[0009] This invention provides a migration optimization method for Oracle databases to TiDB databases. This method enables automatic optimization based on execution plans, dynamic adaptive resource allocation, and automatic scaling. It reduces manual intervention, improves migration efficiency and database performance, and ensures optimal database performance after migration. It also ensures dynamic resource scaling during the migration process to avoid performance bottlenecks. The method includes:

[0010] Collect relevant performance data for Oracle and TiDB databases;

[0011] Analyze the execution plans for Oracle and TiDB databases;

[0012] The execution plans of Oracle database and TiDB database are compared using a pre-defined mathematical model;

[0013] The differences identified through comparison are judged, and when the difference exceeds the specified threshold, optimization is performed accordingly.

[0014] This invention also provides a migration optimization device for Oracle databases to TiDB databases, which can achieve automatic optimization based on execution plans, dynamic adaptive resource allocation, and automatic scaling. It can reduce manual intervention, improve migration efficiency and database performance, and ensure optimal database performance after migration. It also ensures dynamic resource expansion during the migration process to avoid performance bottlenecks. The device includes:

[0015] The data collection and performance monitoring module is used to collect relevant performance data for Oracle databases and TiDB databases.

[0016] The execution plan parsing module is used to parse the execution plans of Oracle databases and TiDB databases.

[0017] The execution plan comparison module is used to compare the execution plans of Oracle databases and TiDB databases using a pre-defined mathematical model.

[0018] The execution plan optimization module is used to determine the differences identified in the comparison. When the difference exceeds a specified threshold, optimization is performed accordingly.

[0019] This invention also provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the above-described migration optimization method from an Oracle database to a TiDB database.

[0020] This invention also provides a computer-readable storage medium storing a computer program that performs the above-described migration optimization method from an Oracle database to a TiDB database.

[0021] In this embodiment of the invention, relevant performance data of Oracle and TiDB databases are collected; the execution plans of Oracle and TiDB databases are analyzed; the execution plans of Oracle and TiDB databases are compared using a pre-defined mathematical model; the differences identified are judged, and when the difference exceeds a specified threshold, optimization corresponding to that difference is performed. Compared with existing technologies where database migration lacks automatic tuning mechanisms, involves significant manual intervention, and results in substandard performance after migration, this invention automatically identifies execution differences by comparing the execution plans of Oracle and TiDB databases, intelligently generates optimization strategies, and automatically tunes the query execution path of TiDB. Furthermore, during the migration process, this invention can dynamically adjust TiDB's resource allocation (such as memory, CPU, and storage) and database configuration based on real-time performance monitoring data to ensure optimal database performance after migration. Additionally, this invention can automatically initiate a scaling mechanism based on real-time performance feedback, ensuring that TiDB can dynamically expand its resources according to increased workload during the migration process, avoiding performance bottlenecks. Attached Figure Description

[0022] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings:

[0023] Figure 1 This is a flowchart of the migration optimization method from Oracle database to TiDB database in an embodiment of the present invention.

[0024] Figure 2 This is a flowchart illustrating the collection of relevant performance data for Oracle and TiDB databases in an embodiment of the present invention.

[0025] Figure 3 This is a flowchart illustrating the parsing of execution plans for Oracle and TiDB databases in an embodiment of the present invention.

[0026] Figure 4 This is a flowchart in an embodiment of the present invention that compares the execution plans of an Oracle database and a TiDB database using a pre-set mathematical model.

[0027] Figure 5 This is a flowchart in an embodiment of the present invention that determines the difference index after comparison, and performs optimization corresponding to the difference index when the difference index exceeds a specified threshold.

[0028] Figure 6 This is a flowchart illustrating how SQL rewriting is performed if the execution path similarity between the Oracle database and the TiDB database is less than a specified threshold, as described in this embodiment of the invention.

[0029] Figure 7 This is a flowchart illustrating how index optimization is performed if the difference in execution time or resource consumption between the Oracle database and the TiDB database exceeds a specified threshold, as per an embodiment of the present invention.

[0030] Figure 8 This is a schematic diagram of a migration optimization device from an Oracle database to a TiDB database in an embodiment of the present invention.

[0031] Figure 9 This is a schematic diagram of a computer device in an embodiment of the present invention. Detailed Implementation

[0032] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the embodiments of the present invention will be further described in detail below with reference to the accompanying drawings. Here, the illustrative embodiments of the present invention and their descriptions are used to explain the present invention, but are not intended to limit the present invention.

[0033] Those skilled in the art will recognize that embodiments of the present invention can be implemented as a system, apparatus, device, method, or computer program product. Therefore, this disclosure can be specifically implemented in the following forms: entirely hardware, entirely software (including firmware, resident software, microcode, etc.), or a combination of hardware and software.

[0034] The information collected in the technical solution of this application is information and data authorized by the user or fully authorized by all parties. The collection, storage, use, processing, transmission, provision, disclosure and application of the relevant data all comply with the relevant laws, regulations and standards of the relevant countries and regions, necessary confidentiality measures have been taken, and they do not violate public order and good morals. Corresponding operation portals are provided for users to choose to authorize or refuse.

[0035] The acquisition, transmission, storage, use, and processing of data in this application all comply with the relevant provisions of national laws and regulations.

[0036] It should be noted that in the embodiments of this application, certain software, components, models and other existing solutions in the industry may be mentioned. These should be regarded as exemplary and are only intended to illustrate the feasibility of implementing the technical solution of this application. However, it does not mean that the applicant has used or necessarily used the solution.

[0037] According to an embodiment of the present invention, a method and apparatus for optimizing the migration from an Oracle database to a TiDB database are proposed. The technical solution of the present invention achieves automated database tuning during the migration process from Oracle to TiDB through the collaborative work of a data collection and performance monitoring module and an execution plan comparison and analysis module. The data collection and performance monitoring module collects relevant performance data of the Oracle and TiDB databases during the migration process, including execution plans, query response times, and resource consumption. The execution plan comparison module is the core part of the present invention; its purpose is to identify performance bottlenecks of the migrated database by performing detailed analysis and comparison of the execution plans of the Oracle and TiDB databases, and to provide a basis for optimization. This module uses a series of mathematical formulas, function judgments, and threshold judgments for comparison and automatically generates optimization suggestions.

[0038] The principles and spirit of the present invention will be explained in detail below with reference to representative embodiments thereof.

[0039] Figure 1 This is a flowchart of the migration optimization method from Oracle database to TiDB database in an embodiment of the present invention. Figure 1 As shown, the method includes:

[0040] Step S101: Collect relevant performance data for Oracle database and TiDB database;

[0041] Step S102: Analyze the execution plans for the Oracle database and the TiDB database;

[0042] Step S103: Compare the execution plans of the Oracle database and the TiDB database using a pre-defined mathematical model;

[0043] Step S104: Determine the difference index after comparison. When the difference index exceeds the specified threshold, perform optimization corresponding to the difference index.

[0044] Compared to existing technologies that lack automatic optimization mechanisms, require significant manual intervention, and result in substandard performance after migration, this invention automatically identifies execution differences by comparing the execution plans of Oracle and TiDB databases, intelligently generates optimization strategies, and automatically optimizes the query execution path of TiDB. Furthermore, during the migration process, this invention can dynamically adjust TiDB's resource allocation (such as memory, CPU, and storage) and database configuration based on real-time performance monitoring data to ensure optimal database performance after migration. Additionally, this invention can automatically initiate a scaling mechanism based on real-time performance feedback, ensuring that TiDB can dynamically expand its resources according to increased workload during the migration process, thus avoiding performance bottlenecks.

[0045] Each step is described in detail below.

[0046] See Figure 2 The above step S101, which involves collecting relevant performance data for Oracle and TiDB databases, includes:

[0047] Step S201: Use the interpreted execution plan to obtain the execution plan of the Oracle database, and extract the query execution path, operation nodes, and estimated cost;

[0048] Step S202: Use the interpretation command to obtain the execution plan of the TiDB database, and extract the operation nodes, execution order, access method, and cost estimate;

[0049] Step S203: Collect real-time database operation data, including CPU utilization, memory usage, disk I / O, and query response time.

[0050] The operations in step S201 include, for example, full table scans and index scans, and the estimated costs include, for example, I / O operations and CPU time. Based on the above steps S201-203, execution plan data and real-time performance monitoring data from both the Oracle database and the TiDB database can be collected to provide a basis for subsequent optimization decisions.

[0051] Furthermore, see Figure 3 The above step S102, which parses the execution plans of the Oracle database and the TiDB database, includes:

[0052] Step S301: Extract the execution plan from the Oracle database and the TiDB database;

[0053] Step S302: Analyze each node of the execution plan by means of execution path, cost estimation, and estimated data volume.

[0054] The execution plan described above includes multiple nodes (e.g., scanning, sorting, joining, etc.), each of which can be analyzed using key indicators such as the execution path, cost estimation, and estimated data volume as described in step S302. Here, the execution path represents the order of database queries, typically a tree structure; the cost estimation (Cost) represents the estimated execution cost of each node, including CPU consumption, I / O consumption, etc.; and the estimated data volume (Rows) represents the number of rows each operation node is expected to process. Taking cost estimation as an example to analyze the nodes of the execution plan, we can calculate the total query cost (Total Cost) and the node execution cost (Node Cost). The total query cost (Total Cost) can be calculated, for example, using Equation 1 below.

[0055] [Formula 1]

[0056]

[0057] in, Let be the estimated cost of the i-th node, and n be the total number of nodes.

[0058] The cost of a node typically includes CPU cost and I / O cost. Let's assume the I / O cost of a node is... CPU cost is Then the node execution cost can be expressed as Equation 2 below.

[0059] [Equation 2]

[0060]

[0061] Furthermore, see Figure 4 The above step S103, which compares the execution plans of Oracle database and TiDB database using a pre-defined mathematical model, includes:

[0062] Step S401: Compare the execution paths of the Oracle database and the TiDB database, calculate the similarity of the execution paths of the two databases, and determine the differences in the execution paths;

[0063] Step S402: Compare the cost of each node in the execution plans of the Oracle database and TiDB database, and calculate and determine the differences in execution time and resource consumption.

[0064] Regarding the execution path similarity in step S401 It can be calculated using Equation 3 below.

[0065] [Formula 3]

[0066]

[0067] in, and These are the execution paths for Oracle database and TiDB database, respectively. The characteristics of an operation (e.g., I / O cost, CPU cost, execution time, etc.) when If the value is less than a certain set threshold (e.g., 0.8), it indicates that the paths of the two are significantly different and need to be optimized.

[0068] Regarding the execution time difference in step S402 It can be calculated using Equation 4 below.

[0069] [Formula 4]

[0070]

[0071] in, and The first and second databases, respectively, are Oracle database and TiDB database. The execution time of each operation node. If (in, The threshold value (e.g., 15%) indicates that the execution time of the TiDB database is 15% slower than that of the Oracle database, which may indicate a performance bottleneck.

[0072] Regarding the difference in resource consumption in step S402 It can be calculated using Equation 5 below.

[0073] [Formula 5]

[0074]

[0075] in, Indicates the differences in I / O operations, Indicates the difference in CPU time, when (in When the resource difference threshold is set to 15%, it means that the resource consumption of the TiDB database exceeds that of the Oracle database by 15%, and optimization may be required.

[0076] Furthermore, see Figure 5 The above step S104, which involves determining the difference index and performing optimization corresponding to the difference index when the difference index exceeds a specified threshold, includes:

[0077] Step S501: If the execution path similarity between the Oracle database and the TiDB database is less than a specified threshold, then SQL rewriting is performed.

[0078] Step S502: If the difference in execution time or resource consumption between the Oracle database and the TiDB database exceeds a specified threshold, then index optimization is performed.

[0079] In the technical solution of this invention, steps S301-302 standardize and analyze the original execution plan data of the Oracle database and the TiDB database and extract features; steps S401-402 quantitatively compare and diagnose the differences in the execution paths of the two databases; and steps S501-502 calculate the difference index (path similarity) from steps S401-402. Differences in execution time Differences in resource consumption The system will make a comprehensive judgment by comparing the execution plans of Oracle database and TiDB database, and combining the difference judgment results. The system will automatically quantify the time and performance differences after migration, and perform corresponding optimization actions as needed.

[0080] Regarding optimization actions, embodiments of the present invention exemplify SQL rewriting in step S501 and index optimization in step S502. For details on SQL rewriting in step S501, see [link to relevant documentation]. Figure 6 ,include:

[0081] Step S601 (Inefficient SQL Identification): After determining the execution path similarity through the above steps S401-402... When the value is too low (e.g., below the specified threshold) and a known inefficient operation pattern (e.g., unreasonable subquery execution method) is identified in the TiDB database's execution plan, the system triggers the query rewrite mechanism.

[0082] Step S602 (SQL rewriting): The system rewrites the SQL based on a pre-set optimization rule base (e.g., converting IN subqueries to JOIN, prompting the optimizer to use a better join order, avoiding the use of functions in the WHERE clause that cause index invalidation, etc.).

[0083] For information on index optimization in step S502, see [link / reference]. Figure 7 ,include:

[0084] Step S701 (Difference Node Identification): After determining the I / O cost difference of a certain query node through the above steps S401-402... When the value is significantly higher (e.g., exceeding the threshold δI), the system automatically locates the corresponding operation node in the execution plan of the TiDB database (e.g., the "TableFullScan" full table scan operation).

[0085] Step S702 (Index Missing Analysis) compares the data tables and query conditions (such as WHERE clauses and JOIN conditions) accessed by the operation node with the execution plan of the Oracle database source. If an efficient index scan (such as "IndexRangeScan") is used on the Oracle database side but the corresponding index is missing on the TiDB database side, an index optimization suggestion is triggered.

[0086] Step S703 (Index Recommendation Generation): Combining the distributed data distribution characteristics of the TiDB database, analyze the field combinations in the query conditions, and use the built-in index recommendation algorithm (e.g., based on field selectivity and query frequency) to automatically generate the most suitable index creation SQL statement for the TiDB database (e.g., suggesting the creation of a composite index or considering the partition key index).

[0087] Step S704 (Simulation Verification): In the sandbox environment, re-execute the query using the generated index, and run the execution plan comparison process of steps S401-402 above again to verify the cost difference of the new execution plan. and Has it reached a reasonable value (e.g., it is no higher than the prescribed threshold)?

[0088] In summary, the method proposed in this invention involves collecting relevant performance data from Oracle and TiDB databases; analyzing the execution plans of Oracle and TiDB databases; comparing the execution plans of Oracle and TiDB databases using a pre-defined mathematical model; determining the differences identified in the comparison, and performing optimization corresponding to the difference indicator when the difference indicator exceeds a specified threshold. Compared with existing technologies that lack automatic tuning mechanisms, require significant manual intervention, and result in substandard performance after migration, this invention has at least two advantages: 1. Precise execution plan comparison: By analyzing execution plan paths and performance and time consumption, precise comparisons of execution plans between different databases can be made, timely identifying performance differences and providing a scientific basis for further optimization; 2. Automated performance optimization: Through automated comparison and difference determination of execution plans, recommended optimization strategies are automatically generated, reducing manual intervention and improving migration efficiency and database performance. Furthermore, in the embodiments of the present invention, by comparing the execution plans of Oracle and TiDB databases, execution differences are automatically identified, optimization strategies are intelligently generated, and the query execution path of TiDB is automatically tuned. During the migration process, the present invention can dynamically adjust the resource allocation (such as memory, CPU, and storage) and database configuration of TiDB based on real-time performance monitoring data to ensure that the performance of the migrated database reaches the best. In addition, the present invention can automatically start the expansion mechanism based on real-time performance feedback to ensure that TiDB can dynamically expand its resources according to the increase in workload during the migration process, thus avoiding performance bottlenecks.

[0089] This invention also provides a migration optimization device for Oracle database to TiDB database, as described in the following embodiments. Since the principle by which this device solves the problem is similar to the migration optimization method for Oracle database to TiDB database, the implementation of this device can refer to the implementation of the migration optimization method for Oracle database to TiDB database; repeated details will not be elaborated further.

[0090] Figure 8 This is a schematic diagram of a migration optimization device from an Oracle database to a TiDB database according to an embodiment of the present invention, including:

[0091] The data collection and performance monitoring module 801 is used to collect relevant performance data for Oracle database and TiDB database.

[0092] The execution plan parsing module 802 is used to parse the execution plans of Oracle databases and TiDB databases.

[0093] The execution plan comparison module 803 is used to compare the execution plans of Oracle database and TiDB database using a pre-defined mathematical model.

[0094] The execution plan optimization module 804 is used to determine the difference indicators found in the comparison. When the difference indicator exceeds the specified threshold, optimization corresponding to the difference indicator is performed.

[0095] The execution plan parsing module 802, the execution plan comparison module 803, and the execution plan optimization module 804 can also be collectively referred to as the "execution plan comparison and analysis module".

[0096] In one embodiment, the data collection and performance monitoring module is specifically used for:

[0097] Use the interpreted execution plan to obtain the execution plan of the Oracle database, and extract the query execution path, operation nodes, and estimated cost;

[0098] Use the interpretation command to obtain the execution plan of the TiDB database, and extract the operation nodes, execution order, access method, and cost estimate;

[0099] Collect real-time database operation data, including CPU utilization, memory usage, disk I / O, and query response time.

[0100] In one embodiment, the execution plan parsing module is specifically used for:

[0101] Extract execution plans from Oracle and TiDB databases;

[0102] Each node of the execution plan is analyzed by considering the execution path, cost estimation, and estimated data volume.

[0103] In one embodiment, the execution plan comparison module is specifically used for:

[0104] Compare the execution paths of Oracle database and TiDB database, calculate the similarity of the execution paths of the two databases, and determine the differences in the execution paths;

[0105] The cost of each node in the execution plans for the Oracle database and TiDB database is compared, and the differences in execution time and resource consumption are calculated and determined.

[0106] In one embodiment, the execution plan optimization module is specifically used for:

[0107] If the similarity of the execution paths of the Oracle database and the TiDB database is less than a specified threshold, then SQL rewriting will be performed.

[0108] If the difference in execution time or resource consumption between the Oracle database and the TiDB database exceeds a specified threshold, index optimization will be performed.

[0109] In summary, the apparatus proposed in this invention collects relevant performance data of Oracle and TiDB databases; analyzes the execution plans of Oracle and TiDB databases; compares the execution plans of Oracle and TiDB databases using a pre-set mathematical model; determines the differences identified in the comparison, and performs optimization corresponding to the difference when the difference exceeds a specified threshold. Compared with existing technologies that lack automatic tuning mechanisms, require significant manual intervention, and result in substandard performance after migration, this invention has at least two advantages: 1. Accurate execution plan comparison: By analyzing execution plan paths and performance and time consumption, accurate comparisons of execution plans between different databases can be made, timely identifying performance differences and providing a scientific basis for further optimization; 2. Automated performance optimization: Through automated comparison and difference determination of execution plans, recommended optimization strategies are automatically generated, reducing manual intervention and improving migration efficiency and database performance. Furthermore, in the embodiments of the present invention, by comparing the execution plans of Oracle and TiDB databases, execution differences are automatically identified, optimization strategies are intelligently generated, and the query execution path of TiDB is automatically tuned. During the migration process, the present invention can dynamically adjust the resource allocation (such as memory, CPU, and storage) and database configuration of TiDB based on real-time performance monitoring data to ensure that the performance of the migrated database reaches the best. In addition, the present invention can automatically start the expansion mechanism based on real-time performance feedback to ensure that TiDB can dynamically expand its resources according to the increase in workload during the migration process, thus avoiding performance bottlenecks.

[0110] This invention also provides a computer device. Figure 9 This is a schematic diagram of a computer device in an embodiment of the present invention. The computer device 900 includes a memory 910, a processor 920, and a computer program 930 stored in the memory 910 and executable on the processor 920. When the processor 920 executes the computer program 930, it implements the above-described migration optimization method from Oracle database to TiDB database.

[0111] This invention also provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the above-described migration optimization method from an Oracle database to a TiDB database.

[0112] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0113] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0114] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0115] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0116] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of the present invention. It should be understood that the above descriptions are merely specific embodiments of the present invention and are not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.

Claims

1. A migration optimization method from Oracle database to TiDB database, characterized in that, include: Collect relevant performance data for Oracle and TiDB databases; Analyze the execution plans for Oracle and TiDB databases; The execution plans of Oracle database and TiDB database are compared using a pre-defined mathematical model; The differences identified through comparison are judged, and when the difference exceeds the specified threshold, optimization is performed accordingly.

2. The method according to claim 1, characterized in that, The collection of relevant performance data for Oracle and TiDB databases includes: Use the interpreted execution plan to obtain the execution plan of the Oracle database, and extract the query execution path, operation nodes, and estimated cost; Use the interpretation command to obtain the execution plan of the TiDB database, and extract the operation nodes, execution order, access method, and cost estimate; Collect real-time database operation data, including CPU utilization, memory usage, disk I / O, and query response time.

3. The method according to claim 1, characterized in that, The parsing of the execution plans for the Oracle and TiDB databases includes: Extract execution plans from Oracle and TiDB databases; Each node of the execution plan is analyzed by considering the execution path, cost estimation, and estimated data volume.

4. The method according to claim 1, characterized in that, The comparison of execution plans for Oracle and TiDB databases using a pre-defined mathematical model includes: Compare the execution paths of Oracle database and TiDB database, calculate the similarity of the execution paths of the two databases, and determine the differences in the execution paths; The cost of each node in the execution plans for the Oracle database and TiDB database is compared, and the differences in execution time and resource consumption are calculated and determined.

5. The method according to claim 1, characterized in that, The step of determining the difference index after comparison, and when the difference index exceeds a predetermined threshold, performing optimization corresponding to the difference index, includes: If the similarity of the execution paths of the Oracle database and the TiDB database is less than a specified threshold, then SQL rewriting will be performed. If the difference in execution time or resource consumption between the Oracle database and the TiDB database exceeds a specified threshold, index optimization will be performed.

6. The method according to claim 5, characterized in that, If the execution path similarity between the Oracle database and the TiDB database is less than a specified threshold, SQL rewriting will be performed, including: When the similarity of the execution path is less than the specified threshold and a known inefficient operation pattern is identified in the execution plan of the TiDB database, the query rewrite mechanism is triggered. The SQL is rewritten based on a pre-set optimization rule base.

7. The method according to claim 5, characterized in that, If the difference in execution time or resource consumption between the Oracle database and the TiDB database exceeds a specified threshold, index optimization will be performed, including: When it is determined that the I / O cost difference of a certain query node is significantly higher, the corresponding operation node in the execution plan of the TiDB database is automatically located. The data tables and query conditions accessed by the operation node are compared with the execution plan of the Oracle database source. If an efficient index scan is used on the Oracle database side but the corresponding index is missing on the TiDB database side, an index optimization suggestion is triggered. Based on the distributed data distribution characteristics of the TiDB database, the combination of fields in the query conditions is analyzed, and the built-in index recommendation algorithm is used to automatically generate the SQL statement that is most suitable for creating indexes for the TiDB database. In the sandbox environment, re-execute the query using the generated index and run the execution plan comparison between the Oracle database and the TiDB database again to verify that the cost difference of the new execution plan is no higher than the specified threshold.

8. A migration optimization device for Oracle database to TiDB database, characterized in that, include: The data collection and performance monitoring module is used to collect relevant performance data for Oracle databases and TiDB databases. The execution plan parsing module is used to parse the execution plans of Oracle databases and TiDB databases. The execution plan comparison module is used to compare the execution plans of Oracle databases and TiDB databases using a pre-defined mathematical model. The execution plan optimization module is used to determine the differences identified in the comparison. When the difference exceeds a specified threshold, optimization is performed accordingly.

9. A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method of any one of claims 1 to 8.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that performs the method of any one of claims 1 to 8.

Images