Cross-platform visual database execution plan analysis method, equipment and medium

Abstract

The invention provides a cross-platform visual database execution plan analysis method and device and a medium, and belongs to the technical field of data storage. The database execution plan analysis method comprises the steps that a cross-platform support tool is used, standard SQL grammar is called, and an execution plan of each database in multiple databases is extracted; using a unified database execution plan conversion algorithm to convert the execution plan of each database into a unified intermediate representation format to obtain a standardized execution plan; displaying the standardized execution plan by using a graphical interface; analyzing potential performance problems of the execution plan according to the key analysis indexes of the execution plan, and generating optimization suggestions for the potential performance problems in combination with statistical information of the database table. The method can solve the problems that in the prior art, a unified interface and a display mode are adopted between tools, cross-platform use is complex, and display is complex.

Description

Technical Field

[0001] This application belongs to the field of data storage technology, specifically relating to a cross-platform visual database execution plan analysis method, device, and medium. Background Technology

[0002] With the rapid growth of data volume in the information age, the role of databases is becoming increasingly prominent; correspondingly, the query performance of databases is becoming increasingly significant in terms of the speed of business response and the value experience of users.

[0003] Traditional database query optimization relies on the SQL language, and SQL query optimization typically depends on the analysis of the database execution plan. The execution plan is a set of instructions generated by the database query optimizer to execute SQL statements. Specifically, when a database executes an SQL statement, it undergoes three steps: parsing, optimization, and execution. During the optimization phase, the query optimizer generates a plan called the execution plan. The execution plan tells the database the specific steps to execute the SQL statement, determining the operations the database will take, including table join methods, index selection, and access methods, thus affecting the execution efficiency of the query statement.

[0004] For execution plan analysis, existing tools are primarily standalone tools provided by database vendors, such as Oracle's SQL Developer and MySQL's Workbench. While these tools can generate execution plans, they lack a unified interface and display method, making cross-platform use and visualization complex. Because execution plans vary in format across different databases, and there is a lack of standardized and cross-platform solutions, current execution plan analysis often relies on manual operations. Manually analyzing execution plans is time-consuming and dependent on human experience, hindering efficient database performance tuning in complex environments.

[0005] Therefore, the ability to quickly and accurately analyze and optimize database execution plans has become a key task for database administrators and developers. Summary of the Invention

[0006] This application aims to provide a database execution plan analysis solution. It seeks to quickly and accurately analyze and optimize database execution plans, addressing the lack of a unified interface and display method among existing tools, and the complexity of cross-platform use and visualization. Because execution plans from different databases have varying formats and lack standardized and cross-platform solutions, the analysis of existing execution plans often relies on manual operations.

[0007] According to a first aspect of this application, this application provides a cross-platform visual database execution plan analysis method, comprising: Using cross-platform support tools, standard SQL syntax is invoked to extract the execution plan for each database in various databases. Using a unified database execution plan conversion algorithm, the execution plan of each database is converted into a unified intermediate representation format to obtain a standardized execution plan; Use a graphical interface to display standardized execution plans; Based on the key performance indicators of the execution plan, potential performance issues are analyzed, and optimization suggestions for these issues are generated by combining statistical information from database tables.

[0008] Preferably, in the above-described cross-platform visualized database execution plan analysis method, before the step of using cross-platform support tools to call standard SQL syntax and obtain the execution plan for each database in multiple databases, the method further includes: Use a cross-platform development framework to design cross-platform support tools; Design functional modules for cross-platform support tools using standardized interfaces; In cross-platform support tools, a unified database access interface is used to establish communication connections with each database across multiple databases.

[0009] Preferably, in the above-mentioned cross-platform visualized database execution plan analysis method, the steps of using cross-platform support tools and calling standard SQL syntax to extract the execution plan for each database in various databases include: Establish communication connections between cross-platform support tools and each database across multiple databases using a unified database access interface. Use the SQL syntax commands corresponding to each database to invoke the execution plan for each database; Parse the execution plan's content and generation time to obtain the parsing results, and then reconstruct the execution task flow corresponding to the execution plan based on the parsing results. The task correlation between different execution task flows is calculated using a vector similarity algorithm, and a task correlation map is generated using the execution task flows and task correlations. The execution plans are clustered based on the task relevance graph.

[0010] Preferably, in the above-mentioned cross-platform visualized database execution plan analysis method, the step of using a unified database execution plan conversion algorithm to convert the execution plan of each database into a unified intermediate representation format to obtain a standardized execution plan includes: Parse the format and type of the execution plan; Using a unified database execution plan conversion algorithm, the execution plan of each database is converted into a unified intermediate representation format according to the format and type of the execution plan, resulting in a standardized execution plan; Based on the task correlation graph, design the storage structure of the execution plan, and store the standardized execution plan in a unified system memory according to the storage structure.

[0011] Preferably, in the above-mentioned cross-platform visualized database execution plan analysis method, the step of using a unified database execution plan conversion algorithm to convert the execution plan of each database into a unified intermediate representation format to obtain a standardized execution plan includes: Parse the text or structured output of the execution plan and extract each operation node and its attributes from the text or structured output; According to the predefined intermediate representation model, the operation nodes and attributes are mapped to a unified node type and attribute field, respectively; Based on the hierarchy and dependencies of the execution plan, a complete intermediate representation format is constructed using node types and attribute fields; For different databases, the corresponding adapter or parser is selected according to the database type to convert the original execution plan into a unified intermediate representation format.

[0012] Preferably, in the above-mentioned cross-platform visualized database execution plan analysis method, the steps of using a graphical interface to display standardized execution plans include: Recall a standardized execution plan from system memory; Use a graphical interface to display the execution order and detailed information of each step in the execution plan in a tree structure; Use a graphical interface to display the operation process of the execution plan in the form of a flowchart; And / or, use a graphical interface to display the resource consumption information of the execution plan in chart form.

[0013] Preferably, in the above-mentioned cross-platform visualized database execution plan analysis method, the steps of analyzing potential performance issues of the execution plan based on key analysis indicators, and generating optimization suggestions for potential performance issues by combining database table statistics, include: Extract key analytical metrics corresponding to the execution plan, and use these metrics to detect potential performance issues in the database. These potential performance issues include indexing problems, query structure problems, and memory problems. Based on the database statistics, corresponding optimization suggestions are given for index issues, query structure issues, and memory issues, respectively.

[0014] Preferably, in the above-mentioned cross-platform visualized database execution plan analysis method, the step of generating optimization suggestions for potential performance problems by combining database table statistics includes: Based on the statistical information in the database, the characteristics of potential performance problems are statistically analyzed. Recall performance optimization cases from a predefined dynamic knowledge graph; Perform multi-dimensional matching of performance problem characteristics with performance optimization cases; Based on the degree of matching across multiple dimensions, the cosine similarity between the features of the performance problem and the performance optimization case is calculated. Performance optimization cases with cosine similarity above a predetermined similarity threshold are extracted and used as optimization reference cases; Based on fuzzy logic, an expert experience decision tree is constructed. The expert experience decision tree is used to process the uncertain conditions corresponding to the optimization reference case through fuzzy membership function to obtain the disposal conditions. Based on comprehensive optimization of reference cases and handling conditions, corresponding performance optimization suggestions are obtained; When database optimization is complete, the corresponding optimization records are extracted from the database logs. Potential performance issues, performance optimization suggestions, and corresponding optimization records are merged into a performance optimization case and stored in the dynamic knowledge graph.

[0015] According to a second aspect of this application, this application also provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the cross-platform visualized database execution plan analysis method provided by any of the above technical solutions.

[0016] According to a third aspect of this application, this application also provides a computer storage medium storing a computer program thereon, which, when executed, implements the cross-platform visualized database execution plan analysis method provided by any of the above technical solutions.

[0017] The technical solution of this application has at least the following technical effects: This application provides a cross-platform visualized database execution plan analysis solution. This solution uses cross-platform support tools to call standard SQL syntax, enabling the extraction of execution plans from multiple databases across operating platforms. Then, a unified database execution plan conversion algorithm is used to convert the execution plans of each database into a unified intermediate representation format, resulting in a standardized execution plan displayed through a graphical interface. This method can standardize the parsing and display of execution plans from different databases through a unified interface and platform compatibility mechanism, providing users with intuitive plan displays and optimization suggestions through a graphical interface. Finally, based on the key analysis indicators of the execution plan, potential performance problems are analyzed, and optimization suggestions for these potential performance problems are generated by combining database table statistics. This provides accurate references for database optimization and effectively improves the performance optimization efficiency of database queries. In summary, compared to existing execution plan analysis methods that typically rely on manual operations, this application's technical solution offers advantages such as ease of operation, cross-platform compatibility, and automated analysis. The process is less time-consuming and can efficiently optimize database performance in complex environments. Attached Figure Description

[0018] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings: Figure 1 A flowchart illustrating the first cross-platform visualized database execution plan analysis method provided in this application embodiment; Figure 2 for Figure 1 The illustrated embodiment provides a flowchart of a design method for a cross-platform support tool; Figure 3 for Figure 1 The illustrated embodiment provides a flowchart of an execution plan extraction method; Figure 4 for Figure 1 A flowchart illustrating the first intermediate representation format conversion method provided in the illustrated embodiment; Figure 5 for Figure 1 A flowchart illustrating the second intermediate representation format conversion method provided in the illustrated embodiment; Figure 6 for Figure 1 A flowchart illustrating the method for generating the first optimization suggestion provided in the illustrated embodiment; Figure 7 for Figure 1 A flowchart illustrating the second optimization suggestion generation method provided in the illustrated embodiment; Figure 8 A flowchart illustrating the second cross-platform visualized database execution plan analysis method provided in this application embodiment; Figure 9 A flowchart illustrating the first cross-platform visualized database execution plan analysis device provided in this application embodiment; Figure 10 for Figure 9 The illustrated embodiment provides a structural diagram of a standardized parsing module; Figure 11 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0019] To more clearly illustrate the overall concept of this application, a detailed explanation is provided below with reference to the accompanying drawings.

[0020] Many specific details are set forth in the following description to provide a thorough understanding of this application. However, this application may also be implemented in other ways different from those described herein. Therefore, the scope of protection of this application is not limited to the specific embodiments disclosed below. It should be noted that, unless otherwise specified, the embodiments of this application and the features thereof can be combined with each other.

[0021] In this application, unless otherwise expressly specified and limited, the terms "above" and "below" the second feature can refer to direct contact between the first and second features, or indirect contact between the first and second features through an intermediate medium. In the description of this specification, references to terms such as "one embodiment," "some embodiments," "example," "specific example," or "some examples," etc., indicate that a specific feature, structure, material, or characteristic described in connection with that embodiment or example is included in at least one embodiment or example of this application. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described can be combined in any suitable manner in one or more embodiments or examples.

[0022] The existing technology has the following drawbacks: For execution plan analysis, existing tools are primarily standalone tools provided by database vendors, such as Oracle's SQL Developer and MySQL's Workbench. While these tools can generate execution plans, they lack a unified interface and display method, making cross-platform use and visualization complex. Because execution plans vary in format across different databases, and there is a lack of standardized and cross-platform solutions, current execution plan analysis often relies on manual operations. Manually analyzing execution plans is time-consuming and dependent on human experience, hindering efficient database performance tuning in complex environments. Therefore, the ability to quickly and accurately analyze and optimize database execution plans has become a critical task for database administrators and developers.

[0023] To address the aforementioned issues, the following embodiments of this application provide a cross-platform, visualized database execution plan analysis solution. This solution supports multiple mainstream database management systems (such as MySQL, Oracle, SQL Server, and PostgreSQL) and offers users intuitive optimization functions, including execution path analysis, performance bottleneck detection, and index usage suggestions, through a graphical interface. Furthermore, it boasts advantages such as ease of operation, cross-platform compatibility, and automated analysis, effectively improving the performance optimization efficiency of database queries.

[0024] To achieve the above objectives, see [link to relevant documentation]. Figure 1 , Figure 1 This is a flowchart illustrating a cross-platform, visualized database execution plan analysis method provided in an embodiment of this application. Figure 1 As shown, this cross-platform visualized database execution plan analysis method includes: S110: Using cross-platform support tools, standard SQL syntax is invoked to extract the execution plan for each database in various databases.

[0025] This cross-screen support tool includes a unified interface and platform compatibility mechanism, enabling it to extract data from different operating platforms and perform standardized parsing of execution plans from different databases. By communicating with multiple database systems (such as MySQL, Oracle, SQL Server, and PostgreSQL) and invoking standard SQL syntax (such as EXPLAIN and EXPLAIN PLAN), this cross-platform support tool can obtain execution plan data from each database.

[0026] Specifically, as a preferred embodiment, such as Figure 2 As shown, the above cross-platform visualized database execution plan analysis method, before step S110: using cross-platform support tools to call standard SQL syntax to obtain the execution plan of each database in multiple databases, also includes: S101: Use a cross-platform development framework to design cross-platform support tools.

[0027] S102: Design functional modules for cross-platform support tools using standardized interfaces.

[0028] S103: In cross-platform support tools, a unified database access interface is used to establish communication connections with each database in a variety of databases.

[0029] The technical solution provided in this application embodiment utilizes a cross-platform support tool based on a cross-platform development framework (such as Java or Electron), ensuring smooth operation on operating systems such as Windows, Linux, and macOS. Users do not need to worry about platform compatibility issues and can use the tool seamlessly on different platforms. Furthermore, this cross-platform support tool provides a unified database interface, connecting to different database systems via JDBC or ODBC, and supporting the simultaneous management and analysis of execution plans from multiple database systems.

[0030] The key to this tool's excellent cross-platform compatibility lies in its architectural design and the chosen technical solutions. First, during development, the tool employed cross-platform development frameworks (such as Java, .NET Core, Electron, and Qt), which inherently support seamless operation on mainstream operating systems like Windows, Linux, and macOS. Second, each functional module (such as database connection, user interface, and file operations) is designed using standardized and abstracted interfaces, avoiding dependence on specific operating systems and ensuring consistency and stability across different platforms. Furthermore, the tool achieves seamless integration with various database systems through unified database access interfaces (such as JDBC, ODBC, or a custom database adaptation layer), further enhancing its cross-platform versatility.

[0031] In addition, as a preferred embodiment, such as Figure 3 As shown, in the above cross-platform visualized database execution plan analysis method, step S110: using cross-platform support tools, calling standard SQL syntax, and extracting the execution plan for each database in various databases, specifically including: S111: Use a unified database access interface to establish communication connections between cross-platform support tools and each database in a variety of databases.

[0032] The cross-platform support module was designed and implemented independently based on actual needs. This module not only coordinates and manages compatibility issues across different operating systems but also provides a unified interface, shielding the differences between underlying platforms. Through this self-developed cross-platform support module, the tool can flexibly adapt to different operating system environments and ensure a consistent user experience across all platforms. This design significantly improves the tool's portability and user reach, and is one of the key innovations of this invention.

[0033] S112: Use the SQL syntax commands corresponding to each database to invoke the execution plan for each database.

[0034] S113: Parse the execution plan's content and generation time to obtain the parsing results, and reconstruct the execution task flow corresponding to the execution plan based on the parsing results.

[0035] S114: Calculate the task correlation between different execution task flows according to the vector similarity algorithm, and generate a task correlation map using the execution task flows and task correlations.

[0036] S115: Cluster the execution plan according to the task relevance graph.

[0037] This application's embodiments focus on SQL syntax commands, extracting and standardizing the execution plan. Specifically, it obtains execution plan information by analyzing standard SQL statements from different databases, for example: For MySQL databases, use the EXPLAIN SELECT command to obtain the execution plan; For Oracle databases, use the EXPLAIN PLAN FOR command to obtain the execution plan; For SQL Server, use the SET SHOWPLAN_ALL statement to obtain the execution plan; For PostgreSQL, use the EXPLAIN (ANALYZE) command to obtain the execution plan.

[0038] The technical solution provided in this application embodiment encapsulates a unified database access interface in its cross-platform support tool. This allows for the use of different standard SQL statements to call corresponding execution plan information for different databases, thereby achieving cross-platform and cross-database execution plan calls. The parsing results include the execution plan content and the plan generation time. These parsing results are highly correlated with the execution task, even when multiple databases are executing the same task. Therefore, the execution task flow corresponding to the execution plan can be reconstructed from the above execution results. Then, based on the vector similarity in different execution task flows, the correlation between different execution tasks can be determined, identifying whether different execution tasks belong to the same requirement or project. This generates a task correlation graph, which is then used to classify, parse, and process different execution plans, thereby further optimizing database performance.

[0039] Figure 1 The technical solution provided in the illustrated embodiment, after the step of calling standard SQL syntax to extract the execution plan of each database in various databases, further includes: S120: Using a unified database execution plan conversion algorithm, the execution plan of each database is converted into a unified intermediate representation format to obtain a standardized execution plan.

[0040] The execution plan data obtained above is parsed by a standardized parsing module and converted into a unified intermediate representation format. Information parsed at each step, including operation type (such as full table scan, index scan, and join operation), estimated row count, and processing time, is parsed and stored in the client's memory for subsequent processing. The main purpose of converting the database execution plan into a unified intermediate representation format is to achieve compatibility and abstraction between different databases, facilitating subsequent automated analysis, performance diagnosis, optimization suggestion generation, and graphical interface display. Since the execution plan formats and contents output by various databases (such as Oracle, MySQL, SQL Server, PostgreSQL, etc.) differ significantly, directly processing the original execution plan leads to complex analysis and display logic and is difficult to maintain. This application designs a unified database execution plan conversion algorithm to convert it into a unified intermediate representation format, effectively solving this problem.

[0041] Specifically, as a preferred embodiment, such as Figure 4 As shown, step S120 above, which involves using a unified database execution plan conversion algorithm to convert the execution plan of each database into a unified intermediate representation format to obtain a standardized execution plan, includes: S121: Parse the format and type of the execution plan; S122: Using a unified database execution plan conversion algorithm, the execution plan of each database is converted into a unified intermediate representation format according to the format and type of the execution plan, resulting in a standardized execution plan; S123: Based on the task correlation graph, design the storage structure of the execution plan, and store the standardized execution plan in a unified system memory according to the storage structure.

[0042] The technical solution provided in this application uses a unified database execution plan conversion algorithm. This algorithm converts the execution plan into a unified intermediate representation format. Its main purpose is to achieve compatibility and abstraction between different databases, facilitating subsequent automated analysis, performance diagnosis, optimization suggestion generation, and graphical interface display. Because the execution plan formats and contents output by various databases (such as Oracle, MySQL, SQL Server, and PostgreSQL) differ significantly, directly processing the original execution plan would lead to complex analysis and display logic and make it difficult to maintain. It should be noted that the "format" mentioned here does not specifically refer to file formats such as txt, doc, or xls, but rather to a data format with certain rules, such as keywords, reserved words, and indentation rules.

[0043] Specifically, as a preferred embodiment, such as Figure 5 As shown, in the above cross-platform visualized database execution plan analysis method, step S120: using a unified database execution plan conversion algorithm to convert the execution plan of each database into a unified intermediate representation format to obtain a standardized execution plan, includes: S124: Parse the text or structured output of the execution plan and extract each operation node and its attributes from the text or structured output; S125: According to the predefined intermediate representation model, map the operation nodes and attributes to a unified node type and attribute field respectively; S126: Construct a complete intermediate representation format using node types and attribute fields according to the hierarchy and dependencies of the execution plan; For different databases, the corresponding adapter or parser is selected according to the database type to convert the original execution plan into a unified intermediate representation format.

[0044] The technical solution provided in this application provides that by parsing the text or structured output of the execution plan, each operation node and its attributes are extracted. Then, according to a predefined intermediate representation model, the operation nodes and attributes are mapped to a unified node type and attribute field, thus constructing a complete intermediate representation format. This method enables compatibility between different databases and different types of execution plans, as well as the structured and hierarchical display of the execution plan, facilitating a unified display from the graphical user interface.

[0045] Figure 1 The technical solution provided in the illustrated embodiment, after the step of converting the execution plan of each database into a unified intermediate representation format to obtain a standardized execution plan, further includes: S130: Use a graphical interface to display standardized execution plans.

[0046] Specifically, as a preferred embodiment, in the above-described cross-platform visualized database execution plan analysis method, step S130: displaying the standardized execution plan using a graphical interface, includes: Recall a standardized execution plan from system memory; Use a graphical interface to display the execution order and detailed information of each step in the execution plan in a tree structure; Use a graphical interface to display the operation process of the execution plan in the form of a flowchart; Use a graphical interface to display the resource consumption information of the execution plan in chart form.

[0047] The technical solution provided in this application embodiment allows for viewing the parsed execution plan in a graphical interface. The execution plan can be displayed in various formats, including tree structures, flowcharts, and charts. Tree structure: Displays the execution order of each step of an SQL query in the form of a tree, allowing users to expand layer by layer to view detailed information for each step; Flowchart: A flowchart illustrating the entire process of SQL execution, from data reading and index scanning to final result output. Charts show the resource consumption of the execution plan, such as CPU time and disk I / O, through bar charts, pie charts, etc.

[0048] Figure 1 The cross-platform visualized database execution plan analysis method provided in the illustrated embodiment, after the steps of displaying a standardized execution plan using a graphical interface, further includes: S140: Based on the key analytical metrics of the execution plan, analyze the potential performance issues of the execution plan, and generate optimization suggestions for potential performance issues by combining database table statistics.

[0049] The technical solution provided in this application, based on standardized execution plans and statistical information of database tables, can automatically analyze potential performance problems in the execution plan and provide specific optimization suggestions, such as index optimization, query reconstruction, and memory usage adjustment.

[0050] Specifically, as a preferred embodiment, such as Figure 6 As shown, in the above cross-platform visualized database execution plan analysis method, step S140: Based on the key analysis indicators of the execution plan, analyze the potential performance problems of the execution plan, and combine the database table statistics to generate optimization suggestions for potential performance problems, specifically including: S141: Extract the key analytical metrics corresponding to the execution plan, and use the key analytical metrics to detect potential performance problems in the database. These potential performance problems include index problems, query structure problems, and memory problems.

[0051] S142: Based on the database statistics, provide corresponding suggestions for index optimization, query structure optimization, and memory adjustment optimization for index problems, query structure problems, and memory problems, respectively.

[0052] Performance analysis specifically involves automatically identifying performance bottlenecks based on key metrics in the execution plan (such as full table scans, missed indexes, and numerous sorting operations); then, combined with database statistics, it generates targeted optimization suggestions. Specific optimization suggestions include: Index optimization: Detect cases where indexes are not used in WHERE or JOIN conditions, and recommend that users create appropriate indexes; Query structure optimization: Analyze subqueries, nested queries, or complex JOIN operations, and suggest that users refactor their SQL statements to improve execution efficiency; Memory tuning recommendations: When queries involve a large number of sorting or hashing operations, the system will assess whether it is necessary to increase the database memory allocation and provide corresponding configuration recommendations.

[0053] In addition, as a preferred embodiment, such as Figure 7 As shown, step S140: Combining database table statistics, generating optimization suggestions for potential performance issues, specifically including: S143: Based on the statistical information in the database, statistically analyze the performance problem characteristics corresponding to potential performance problems.

[0054] S144: Call performance optimization cases from a predefined dynamic knowledge graph.

[0055] S145: Perform multi-dimensional matching of performance problem characteristics with performance optimization cases.

[0056] S146: Calculate the cosine similarity between the performance problem features and the performance optimization cases according to the degree of matching in multiple dimensions.

[0057] S147: Extract performance optimization cases with cosine similarity above a predetermined similarity threshold as optimization reference cases.

[0058] S148: Construct an expert experience decision tree based on fuzzy logic, and use the expert experience decision tree to process the uncertain conditions corresponding to the optimization reference case through fuzzy membership function to obtain the disposal conditions.

[0059] S149: Based on comprehensive optimization of reference cases and handling conditions, corresponding performance optimization suggestions are obtained.

[0060] When database optimization is complete, the corresponding optimization records are extracted from the database logs. Potential performance issues, performance optimization suggestions, and corresponding optimization records are merged into a performance optimization case and stored in the dynamic knowledge graph.

[0061] Database table statistics primarily include the total number of rows, number of pages, number of unique values ​​(cardinality) in each column, column value distribution (e.g., histogram), minimum and maximum values, number of null values, average row length, and index-related information (e.g., index cardinality, covering columns, index depth). This statistical information provides the foundation for the database optimizer to generate efficient execution plans. Based on the execution plan and statistics, the system can automatically analyze potential performance issues. For example, if the execution plan includes a full table scan of a large table, and statistics indicate a large amount of data in the table, the system will suggest creating indexes for the relevant query conditions. If the cardinality of the indexed columns is found to be low, the system will indicate that the index's effectiveness is limited and suggest optimizing the query method. Furthermore, the system can automatically identify performance bottlenecks based on join operation types, sorting and grouping operation costs, data distribution uniformity, and statistical information, and provide specific optimization suggestions such as index optimization, SQL restructuring, table structure adjustment, parameter optimization, and statistical information maintenance, thereby helping users improve database query performance.

[0062] In summary, the cross-platform visualized database execution plan analysis method provided in this application, by using cross-platform support tools to call standard SQL syntax, can extract the execution plan of each database from multiple databases in a cross-operating platform manner. Then, a unified database execution plan conversion algorithm is used to convert the execution plans of each database into a unified intermediate representation format, thereby obtaining and displaying the standardized execution plan through a graphical interface. This method can standardize the parsing and display of execution plans from different databases through a unified interface and platform compatibility mechanism, and provide users with intuitive plan displays and optimization suggestions through a graphical interface. Finally, based on the key analysis indicators of the execution plan, potential performance problems are analyzed, and optimization suggestions for these potential performance problems are generated by combining database table statistics. This provides accurate references for database optimization and effectively improves the performance optimization efficiency of database queries. In conclusion, compared with existing execution plan analysis methods that typically rely on manual operations, the technical solution of this application offers the advantages of simple operation, cross-platform compatibility, and automated analysis. The process is less time-consuming and can efficiently optimize database performance in complex environments.

[0063] See also: Figure 8 , Figure 8 This is a flowchart illustrating the second cross-platform visualized database execution plan analysis method provided in this application embodiment. Figure 8 As shown, the database execution plan analysis method includes: S201: Execution Plan Parsing; includes SQL execution plan extraction, obtaining execution plan data from multiple databases using standard SQL syntax. It converts the execution plans generated by each database system into a unified intermediate representation format.

[0064] S202: Performance metrics extraction; including parsing execution cost, execution time, data transfer volume, and I / O operations, etc.

[0065] S203: Performance bottleneck detection; the detected performance bottlenecks include full table scans, excessive sorting costs, excessive join operation costs, and data skew.

[0066] S204: Generate optimization suggestions; including creating or adjusting indexes, optimizing query statements, and adjusting table structures.

[0067] S205: Output optimization report; including detailed analysis of the execution plan, specific description of performance bottlenecks, list of optimization suggestions, and visualization.

[0068] Furthermore, the beneficial effects of the product embodiments provided in the following embodiments of this application are the same as the beneficial effects of the cross-platform visualized database execution plan analysis method provided in the above embodiments, and other technical features in the product embodiments are the same as the features disclosed in the methods of the above embodiments, and will not be repeated here.

[0069] See Figure 9 , Figure 9 A cross-platform visualized database execution plan analysis device provided in this application embodiment includes: Execution plan extraction module 110: Used to obtain execution plan data by communicating with multiple database systems (such as MySQL, Oracle, SQL Server, PostgreSQL, etc.) and calling standard SQL syntax (such as EXPLAIN, EXPLAIN PLAN).

[0070] Standardized parsing module 120: Used to convert execution plans generated by various database systems into a unified intermediate representation format. This format includes information such as the type of execution operation (e.g., full table scan, index scan, join operation, etc.), estimated cost, expected number of rows processed, execution conditions, and I / O consumption.

[0071] Visualization module 130: Used to display the standardized execution plan through a graphical interface. Users can view the execution path of the query in various ways, such as tree structure, flowchart, and icons, to intuitively understand each step of the query, index usage, and potential performance bottlenecks.

[0072] Performance Analysis and Optimization Suggestion Module 140: Based on standardized execution plans and database table statistics, it automatically analyzes potential performance problems in the execution plan and provides specific optimization suggestions, such as index optimization, query reconstruction optimization, and memory allocation optimization.

[0073] Cross-platform support module 150: This tool has good cross-platform compatibility, supports running on operating systems such as Windows, Linux, and macOS, and can seamlessly interact with various database systems through a unified interface.

[0074] Among them, such as Figure 10 As shown, the standardization parsing module 120 is used to unify the execution plan format, converting the execution plan into a unified intermediate representation format. Specifically, this includes inputting execution plans in different formats, parsing the execution plan, and converting it into a unified format; specifically, it converts execution plans generated by various database systems into a unified intermediate representation format. For example... Figure 10 As shown, this intermediate representation format includes information such as the type of operation (e.g., full table scan, index scan, and join operation), estimated cost, expected number of rows processed, execution conditions, and I / O consumption.

[0075] In addition, see Figure 11 The electronic device provided in this application includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to perform the cross-platform visualized database execution plan analysis method of any of the above embodiments.

[0076] The following is for reference. Figure 11 The diagram illustrates a structural schematic of an electronic device suitable for implementing embodiments of this application. The electronic devices in the embodiments of this application may include, but are not limited to, mobile terminals and / or fixed terminals. Figure 11 The device shown is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments of this application.

[0077] The following is for reference. Figure 11 The diagram illustrates a structural schematic of an electronic device suitable for implementing embodiments of this application. The electronic devices in the embodiments of this application may include, but are not limited to, mobile terminals and / or fixed terminals. Figure 11 The device shown is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments of this application.

[0078] like Figure 11 As shown, the electronic device can include a processing unit 1001, such as a central processing unit and / or a graphics processing unit, which can perform various appropriate actions and processes according to a program stored in ROM 1002 or a program loaded from storage device 1003 into RAM 1004. RAM 1004 also stores various programs and data required for the operation of the electronic device. The processing unit 1001, ROM 1002, and RAM 1004 are interconnected via bus 1005. Input / output interface 1006 is also connected to bus 1005. Typically, the following systems can be connected to input / output interface 1006: input devices 1007, such as touchscreens, touchpads, keyboards, mice, image sensors, microphones, accelerometers, and / or gyroscopes; output devices 1008, such as liquid crystal displays (LCDs), speakers, and / or vibrators; storage devices 1003, such as magnetic tape and / or hard disks; and communication devices 1009. Communication device 1009 is capable of enabling the electronic device to exchange data with other devices wirelessly or via wired communication. Although the diagram shows a model building device with various systems, it should be understood that it is not required to implement or have all of the systems shown. It is possible to implement or have more or fewer systems alternatively.

[0079] Specifically, according to the embodiments disclosed in this application, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments disclosed in this application include a computer program product comprising a computer program carried on a computer-readable medium, the computer program containing program code for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via a communication device, or installed from storage device 1003, or installed from ROM 1002. When the computer program is executed by processing device 1001, it performs the functions defined in the cross-platform visualized database execution plan analysis method of the embodiments disclosed in this application.

[0080] This application provides a computer-readable storage medium having computer-readable program instructions stored thereon, namely the computer program described above, which is used to execute the cross-platform visualized database execution plan analysis method in the above embodiments.

[0081] The aforementioned computer-readable storage medium carries one or more programs that, when executed by the model-building device, can be written in one or more programming languages ​​or combinations thereof to perform the operations of this application. These programming languages ​​include object-oriented programming languages—such as Java, Smalltalk, or C++—and conventional procedural programming languages—such as the "C" language or similar programming 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. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a Local Area Network (LAN) or a Wide Area Network (WAN)—or can be connected to an external computer, for example, via the Internet using an Internet service provider.

[0082] The various embodiments in this specification are described in a progressive manner. The same or similar parts between the various embodiments can be referred to each other. Each embodiment focuses on describing the differences from other embodiments.

[0083] The above description is merely an embodiment of this application and is not intended to limit this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the protection scope of this application.

Claims

1. A cross-platform, visual database execution plan analysis method, characterized in that, include: Using cross-platform support tools, standard SQL syntax is invoked to extract the execution plan for each database in various databases. Using a unified database execution plan conversion algorithm, the execution plan of each database is converted into a unified intermediate representation format to obtain a standardized execution plan; The standardized execution plan is displayed using a graphical interface; Based on the key analytical metrics of the execution plan, potential performance issues of the execution plan are analyzed, and optimization suggestions for these potential performance issues are generated by combining statistical information from the database tables.

2. The method as described in claim 1, characterized in that, Before the step of using cross-platform support tools to invoke standard SQL syntax and obtain the execution plan for each database in various databases, the method further includes: Design the cross-platform support tool using a cross-platform development framework; The functional modules of the cross-platform support tool are designed using standardized interfaces; In the cross-platform support tool, a unified database access interface is used to establish a communication connection with each of the various databases.

3. The method as described in claim 1 or 2, characterized in that, The steps of using cross-platform support tools to call standard SQL syntax and extract the execution plan for each database in various databases include: Using the unified database access interface, establish a communication connection between the cross-platform support tool and each of the various databases; Use the SQL syntax commands corresponding to each database to invoke the execution plan for each database. The execution plan's content and generation time are analyzed to obtain the analysis results, and the execution task flow corresponding to the execution plan is reconstructed according to the analysis results. The task correlation between different execution task flows is calculated using a vector similarity algorithm, and a task correlation map is generated using the execution task flows and the task correlation. The execution plan is clustered according to the task relevance graph.

4. The method as described in claim 3, characterized in that, The step of using a unified database execution plan conversion algorithm to convert the execution plan of each database into a unified intermediate representation format to obtain a standardized execution plan includes: Parse the format and type of the execution plan; Using the unified database execution plan conversion algorithm, the execution plan of each database is converted into a unified intermediate representation format according to the format and type of the execution plan, resulting in a standardized execution plan; Based on the task correlation graph, design the storage structure of the execution plan, and store the standardized execution plan in a unified system memory according to the storage structure.

5. The method as described in claim 1, characterized in that, The step of using a unified database execution plan conversion algorithm to convert the execution plan of each database into a unified intermediate representation format to obtain a standardized execution plan includes: Parse the text or structured output of the execution plan, and extract each operation node and its attributes from the text or structured output; According to the predefined intermediate representation model, the operation nodes and attributes are mapped to unified node types and attribute fields respectively; Based on the hierarchy and dependencies of the execution plan, a complete intermediate representation format is constructed using the node type and attribute fields; For different databases, the corresponding adapter or parser is selected according to the database type to convert the original execution plan into a unified intermediate representation format.

6. The method as described in claim 1, characterized in that, The step of displaying the standardized execution plan using a graphical interface includes: The standardized execution plan is retrieved from system memory; The graphical interface is used to display the execution order and detailed information of each step in the execution plan in a tree structure. The graphical interface is used to display the operation flow of the execution plan in the form of a flowchart; And / or, using the graphical interface, display the resource consumption information of the execution plan in chart form.

7. The method as described in claim 1, characterized in that, The steps of analyzing potential performance issues of the execution plan based on its key analytical metrics, and generating optimization suggestions for these potential performance issues by combining database table statistics, include: Extract the key analysis indicators corresponding to the execution plan, and use the key analysis indicators to detect potential performance problems of the database, wherein the potential performance problems include index problems, query structure problems and memory problems; Based on the statistical information of the database, corresponding optimization suggestions are given for the indexing problem, query structure problem, and memory problem, respectively.

8. The method as described in claim 1 or 7, characterized in that, The step of generating optimization suggestions for the potential performance problems by combining database table statistics includes: Based on the statistical information in the database, the performance problem characteristics corresponding to the potential performance problems are statistically analyzed. Recall performance optimization cases from a predefined dynamic knowledge graph; The performance problem characteristics are matched with the performance optimization cases in multiple dimensions; Based on the degree of matching across multiple dimensions, calculate the cosine similarity between the performance problem features and the performance optimization cases, respectively. Extract performance optimization cases with cosine similarity above a predetermined similarity threshold and use them as optimization reference cases; An expert experience decision tree is constructed based on fuzzy logic. The expert experience decision tree is used to process the uncertain conditions corresponding to the optimization reference case through a fuzzy membership function to obtain the disposal conditions. Based on the aforementioned optimization reference cases and handling conditions, corresponding performance optimization suggestions are obtained; When the database optimization is completed, the corresponding optimization records are extracted from the database logs, and the potential performance problems, performance optimization suggestions and corresponding optimization records are merged into a performance optimization case and stored in the dynamic knowledge graph.

9. An electronic 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 program, it implements the cross-platform visualized database execution plan analysis method as described in any one of claims 1 to 8.

10. A computer storage medium having a computer program stored thereon, characterized in that, When the computer program is executed, it implements the cross-platform visualized database execution plan analysis method as described in any one of claims 1 to 8.

Images