All-dimensional sql automatic diagnosis method and device, electronic equipment and program product

Abstract

The application discloses a full-dimension SQL automatic diagnosis method and device, electronic equipment and program product, and comprises the following steps: reading a database configuration file, performing identity authentication according to a database connection parameter and establishing a PostgreSQL database session; performing syntax verification, formatting and redundant node filtering on a to-be-diagnosed SQL file to obtain a target SQL file; performing full-dimension data collection on the target SQL file to obtain an SQL execution plan, metadata and operation and maintenance records; performing recursive aggregation on each sub-partition according to an associated partition table of the target SQL file to obtain a global execution plan, global metadata and global operation and maintenance records of a parent table dimension; and performing data cleaning and fusion on the global execution plan, global metadata and global operation and maintenance records to generate a visual diagnosis report. The application reduces a diagnosis threshold, greatly improves database operation and maintenance efficiency, and can be widely applied to the technical field of databases.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, apparatus, electronic device and program product for full-dimensional automated SQL diagnosis. Background Technology

[0002] Currently, PostgreSQL database SQL performance diagnosis relies entirely on manual operations, including: For the SQL to be optimized, operations personnel must manually execute EXPLAIN step by step to view the execution plan; query system views such as pg_class, pg_attribute, and pg_index one by one to obtain table fields, data types, constraints, and index information; separately call pg_stat_user_tables and pg_statio_user_indexes to count table data volume, storage size, and index space usage; manually count the frequency of DML insert, delete, and update operations, and manually calculate the number of live and dead tuples under the MVCC architecture; and additionally query system logs separately to trace the execution time and status of the most recent ANALYZE statistical update, VACUUM garbage collection, and AUTOVACUUM automated operations.

[0003] For PostgreSQL partitioned table scenarios, existing operations can only query data from a single sub-partition, requiring manual calculation of the overall storage and index usage of the parent table. Conventional diagnostic tools only support analysis of simple SELECT query statements and are not compatible with INSERT / UPDATE / DELETE business write statements. They lack native SQL formatting capabilities and cannot adapt to complex business databases with case-sensitive table and field names. Mainstream tools require manual configuration of database connection parameters, lack independent configuration decoupling design, and cannot identify and filter CTE expressions, resulting in redundant diagnostic data.

[0004] In summary, existing SQL performance diagnostic solutions have the following problems: 1) The diagnostic process is fragmented and extremely inefficient: the entire process relies on manual execution of instructions, verification of system views, and data summarization. A single complete diagnosis takes a long time, is cumbersome, and is prone to missing key core data such as index status, dead tuples, and operation and maintenance records. 2) Blind spots exist in partition table diagnosis: It is impossible to automatically recursively collect sub-partition storage and index data, the overall performance and space expansion risks of the parent table cannot be identified globally, and partition-level performance defects are difficult to locate; 3) Poor statement compatibility and weak adaptability: It only supports SELECT query diagnosis, which cannot cover the frequently used CRUD DML statements in business, and cannot adapt to case-sensitive fields and complex SQL scenarios with CTE. 4) High diagnostic threshold and reliance on human experience: There is no standardized automated output, and maintenance personnel need to have a deep understanding of the PG kernel, system view, and MVCC mechanism. Novices cannot independently complete fault diagnosis and performance analysis. 5) Lack of unified archiving and review capabilities: Manual diagnosis lacks a fixed report format, and analysis results cannot be retained for traceability, which is not conducive to team collaboration and long-term database operation and maintenance optimization.

[0005] The above problems urgently need to be addressed. Summary of the Invention

[0006] The purpose of this invention is to at least partially solve one of the technical problems existing in the prior art.

[0007] Therefore, one objective of this invention is to provide a full-dimensional automated SQL diagnostic method. This method can achieve one-click automated diagnostics of all types of SQL, solve the problem of global statistics of partitioned tables, lower the diagnostic threshold, significantly improve database operation and maintenance efficiency, achieve standardized archiving of diagnostic results, and adapt to PostgreSQL database operation and maintenance scenarios in multiple fields.

[0008] Another objective of this invention is to provide a full-dimensional automated SQL diagnostic device.

[0009] To achieve the above-mentioned technical objectives, the technical solutions adopted in the embodiments of the present invention include: On one hand, embodiments of the present invention provide a comprehensive SQL automated diagnostic method, including the following steps: Read the database configuration file, load the database connection parameters, perform identity authentication based on the database connection parameters, and establish a PostgreSQL database session. Read the SQL file to be diagnosed, perform syntax verification, formatting, and redundant node filtering on the SQL file to be diagnosed, and obtain the target SQL file; The target SQL file is subjected to full-dimensional data collection to obtain the SQL execution plan, metadata, and operation and maintenance records; Based on the associated partition table of the target SQL file, the SQL execution plan, metadata, and operation and maintenance records of each sub-partition are recursively aggregated to obtain the global execution plan, global metadata, and global operation and maintenance records at the parent table level. The global execution plan, global metadata, and global operation and maintenance records are cleaned and integrated to generate a visual diagnostic report.

[0010] Furthermore, in one embodiment of the present invention, the step of reading the database configuration file, loading the database connection parameters, performing identity authentication based on the database connection parameters, and establishing a PostgreSQL database session specifically includes: When SQL diagnostics is started, the configuration parsing module reads the database configuration file and parses the database configuration file to obtain the database connection parameters, which include the database IP, port, username, password, and retrieval path. Authentication is performed based on the database connection parameters, and a PostgreSQL database session is established after successful authentication.

[0011] Furthermore, in one embodiment of the present invention, the step of reading the SQL file to be diagnosed, performing syntax verification, formatting, and redundant node filtering on the SQL file to be diagnosed to obtain the target SQL file specifically includes: The SQL file to be diagnosed is read based on the PostgreSQL database session using the SQL preprocessing module; The SQL file to be diagnosed is subjected to syntax compliance verification to filter invalid characters, and is formatted and redundant nodes are identified and filtered to obtain the target SQL file.

[0012] Furthermore, in one embodiment of the present invention, the step of performing full-dimensional data collection on the target SQL file to obtain the SQL execution plan, metadata, and operation and maintenance records specifically includes: The execution plan analysis module calls the EXPLAIN instruction to capture the SQL execution plan of the target SQL file; The metadata is obtained by retrieving the PostgreSQL system view through the metadata acquisition module and collecting the field structure, data type, primary key / unique constraint and index information of the associated tables; The database operation and maintenance logs are captured by the operation and maintenance data statistics module. The frequency of DML operations and the number of live tuples / dead tuples are counted based on the database operation and maintenance logs to obtain the operation and maintenance record.

[0013] Furthermore, in one embodiment of the present invention, the recursive aggregation of the SQL execution plan, metadata, and operation and maintenance records of each sub-partition based on the associated partition table of the target SQL file to obtain the global execution plan, global metadata, and global operation and maintenance records at the parent table level specifically includes: The associated partition table of the target SQL file is obtained through the partition data aggregation module, and the association relationship between each sub-partition and the parent table is determined based on the associated partition table. Traverse the sub-partitions and, based on the association, aggregate the SQL execution plan, metadata, and operation and maintenance records of each sub-partition to the corresponding parent table to obtain the global execution plan, global metadata, and global operation and maintenance records.

[0014] Furthermore, in one embodiment of the present invention, the step of cleaning and fusing the global execution plan, the global metadata, and the global operation and maintenance records to generate a visual diagnostic report specifically includes: Data cleaning is performed on the global execution plan, the global metadata, and the global operation and maintenance records to remove redundant data; A unified statistical standard is applied to the global execution plan, global metadata, and global operation and maintenance records after data cleaning. The visualized diagnostic report is generated based on the global execution plan, global metadata, and global operation and maintenance records after the preset diagnostic rules and unified indicator statistical standards are applied.

[0015] Furthermore, in one embodiment of the present invention, the full-dimensional SQL automated diagnostic method further includes the following steps: The risk level of the SQL file to be diagnosed is determined based on the visual diagnostic report, and an early warning is issued for the SQL file to be diagnosed based on the risk level.

[0016] On the other hand, embodiments of the present invention provide a full-dimensional automated SQL diagnostic device, including: The configuration parsing module is used to read the database configuration file, load the database connection parameters, perform identity authentication based on the database connection parameters, and establish a PostgreSQL database session. The SQL preprocessing module is used to read the SQL file to be diagnosed, perform syntax verification, formatting, and redundant node filtering on the SQL file to be diagnosed, and obtain the target SQL file. The full-dimensional data acquisition module is used to perform full-dimensional data acquisition on the target SQL file to obtain the SQL execution plan, metadata, and operation and maintenance records. The partition data aggregation module is used to recursively aggregate the SQL execution plan, metadata, and operation and maintenance records of each sub-partition based on the associated partition table of the target SQL file, so as to obtain the global execution plan, global metadata, and global operation and maintenance records of the parent table dimension. The report generation module is used to clean and integrate the global execution plan, the global metadata, and the global operation and maintenance records to generate a visual diagnostic report.

[0017] On the other hand, embodiments of the present invention provide an electronic device, the electronic device including a memory, a processor, a computer program stored in the memory and executable on the processor, and a data bus for implementing communication between the processor and the memory, wherein the computer program, when executed by the processor, implements the full-dimensional SQL automated diagnostic method as described above.

[0018] On the other hand, embodiments of the present invention also provide a storage medium, which is a computer-readable storage medium for computer-readable storage. The storage medium stores one or more computer programs, which can be executed by one or more processors to implement the full-dimensional SQL automated diagnostic method as described above.

[0019] On the other hand, embodiments of the present invention also provide a computer program product, including a computer program that, when executed by a processor, implements the full-dimensional SQL automated diagnostic method as described above.

[0020] The advantages and beneficial effects of the present invention will be set forth in part in the description which follows, and in part will be obvious from the description, or may be learned by practice of the invention: This invention reads the database configuration file, loads the database connection parameters, performs identity authentication and establishes a PostgreSQL database session based on the database connection parameters, reads the SQL file to be diagnosed, performs syntax verification, formatting, and redundant node filtering on the SQL file to be diagnosed to obtain the target SQL file, performs full-dimensional data collection on the target SQL file to obtain the SQL execution plan, metadata, and operation and maintenance records, recursively aggregates the SQL execution plan, metadata, and operation and maintenance records of each sub-partition based on the associated partition table of the target SQL file to obtain the global execution plan, global metadata, and global operation and maintenance records of the parent table, and performs data cleaning and fusion on the global execution plan, global metadata, and global operation and maintenance records to generate a visual diagnostic report. This invention establishes a database session through configuration parsing, completes statement formatting and CTE redundancy filtering through SQL preprocessing, and collects full-dimensional data such as execution plans, metadata, and operation and maintenance records in parallel. Based on the associated partition table, it summarizes the data of each sub-partition to obtain the global execution plan, global metadata, and global operation and maintenance records. Finally, it automatically generates a standardized HTML diagnostic report, which can realize one-click automated diagnosis of all types of SQL, solve the problem of global statistics of partition tables, lower the diagnostic threshold, greatly improve the efficiency of database operation and maintenance, realize standardized archiving of diagnostic results, and adapt to PostgreSQL database operation and maintenance scenarios in multiple fields. Attached Figure Description

[0021] To more clearly illustrate the technical solutions in the embodiments of the present invention, the drawings used in the embodiments of the present invention are described below. It should be understood that the drawings described below are only for the convenience of clearly describing some embodiments of the technical solutions of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0022] Figure 1 A flowchart illustrating the steps of a comprehensive SQL automated diagnostic method provided in this embodiment of the invention; Figure 2 A flowchart of step S101 provided in an embodiment of the present invention; Figure 3 A flowchart of step S102 provided in an embodiment of the present invention; Figure 4 A flowchart of step S103 provided in an embodiment of the present invention; Figure 5 A flowchart of step S104 provided in an embodiment of the present invention; Figure 6 A flowchart of step S105 provided in an embodiment of the present invention; Figure 7 Another flowchart of the full-dimensional SQL automated diagnosis method provided in this embodiment of the invention; Figure 8 This is a schematic diagram of the structure of the full-dimensional SQL automated diagnostic device provided in an embodiment of the present invention; Figure 9 A schematic diagram of the hardware structure of an electronic device provided in an embodiment of the present invention; Figure 10 This is a schematic diagram of the structure of the storage medium provided in an embodiment of the present invention. Detailed Implementation

[0023] The embodiments of the present invention are described in detail below. Examples of these embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals denote the same or similar elements or elements having the same or similar functions throughout. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain this application, and should not be construed as limiting this application. It should be noted that although functional modules are divided in the system schematic diagram and a logical order is shown in the flowchart, in some cases, the steps shown or described may be performed in a different order than the module division in the system schematic diagram or the order in the flowchart. The step numbers in the following embodiments are only set for ease of explanation and do not limit the order between steps. The execution order of each step in the embodiments can be adaptively adjusted according to the understanding of those skilled in the art.

[0024] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs. The terminology used herein is for the purpose of describing embodiments of this application only and is not intended to limit this application.

[0025] To address the technical problems of existing PostgreSQL database SQL diagnosis, such as reliance on manual processes, cumbersome procedures, blind spots in partitioned table diagnosis, poor scenario adaptability, and high diagnostic threshold, this invention provides a comprehensive automated SQL diagnosis method. This method enables one-click automated diagnosis of all types of SQL, overcomes the challenge of partitioned table data collection, lowers the diagnostic threshold, and establishes a standardized and archiveable diagnostic system.

[0026] This invention constructs a closed-loop automated process of "configuration loading—SQL preprocessing—multi-dimensional data collection—partition aggregation—report generation," completely solving the technical pain points of existing manual diagnosis, such as tediousness and omissions, incomplete partition data statistics, poor statement compatibility, and reliance on expert experience. It can realize one-click execution diagnosis of any SQL file, automatically generate structured HTML visual reports, covering all dimensions of information such as SQL specifications, execution plans, table structures, indexes, storage, tuples, and operation and maintenance records, and significantly improve the efficiency and accuracy of SQL performance troubleshooting.

[0027] The following explains some of the terms used in the embodiments of the present invention.

[0028] SQL (Structured Query Language): The standard operating language for relational databases, including DML statements such as SELECT, INSERT, UPDATE, and DELETE, as well as operation and maintenance management statements.

[0029] PG (PostgreSQL, an open-source object-relational database): an enterprise-grade open-source high-performance relational database that supports partitioned tables, high availability of transactions, and multi-version concurrency control (MVCC) architecture.

[0030] MVCC (Multi-Version Concurrency Control): The core concurrency mechanism of PostgreSQL, which produces living tuples and dead tuples, affecting storage space and query performance.

[0031] EXPLAIN: A built-in PostgreSQL execution plan parsing command used to restore SQL index hits, scan methods, execution costs, and join logic.

[0032] CTE (Common Table Expression): The syntax structure of temporary SQL result sets, which can easily cause redundant parsing interference in routine diagnosis.

[0033] VACUUM: A PostgreSQL garbage collection command used to clean up dead tuples and free up storage space; AUTOVACUUM is a garbage collection mechanism that is automatically triggered by the database.

[0034] ANALYZE: A PostgreSQL statistics collection command that updates table and index data distribution and optimizes the accuracy of execution plan cost estimation.

[0035] Partitioned tables: PostgreSQL uses a storage architecture that splits large tables into a parent table and multiple sub-partitions according to rules. Traditional tools cannot automatically summarize the partitioned storage and index data.

[0036] The full-dimensional SQL automated diagnostic method provided in this application can be applied to terminals, servers, or software running on either a terminal or a server. In some embodiments, the terminal can be a smartphone, tablet, laptop, desktop computer, smart speaker, smartwatch, or in-vehicle terminal, but is not limited to these. The server can be configured as an independent physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, CDN, and big data and artificial intelligence platforms. The server can also be a node server in a blockchain network. The software can be an application implementing a machine vision-based driver status monitoring method, but is not limited to the above forms.

[0037] This application can be used in a wide variety of general-purpose or special-purpose computer system environments or configurations. Examples include: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics, network PCs, minicomputers, mainframe computers, and distributed computing environments including any of the above systems or devices. This application can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc., that perform specific tasks or implement specific abstract data types. This application can also be practiced in distributed computing environments where tasks are performed by remote processing devices connected via a communication network. In distributed computing environments, program modules can reside in local and remote computer storage media, including storage devices.

[0038] It should be noted that in all specific embodiments of this application, when processing data related to user identity or characteristics, such as user information, user behavior data, user historical data, and user location information, user permission or consent is obtained first. Furthermore, the collection, use, and processing of this data comply with relevant laws, regulations, and standards of the relevant countries and regions. In addition, when embodiments of this application require access to sensitive personal information of users, separate permission or consent from the user is obtained through pop-ups or redirects to confirmation pages. Only after obtaining the user's separate permission or consent is the necessary user-related data for the proper functioning of the embodiments of this application obtained.

[0039] like Figure 1 The diagram shown is a flowchart of one step of the full-dimensional SQL automated diagnostic method provided by an embodiment of the present invention. (Refer to...) Figure 1 This invention provides a comprehensive automated SQL diagnostic method, which includes the following steps: S101. Read the database configuration file, load the database connection parameters, perform identity authentication based on the database connection parameters, and establish a PostgreSQL database session. S102. Read the SQL file to be diagnosed, perform syntax verification, formatting and redundant node filtering on the SQL file to be diagnosed, and obtain the target SQL file. S103. Perform full-dimensional data collection on the target SQL file to obtain the SQL execution plan, metadata, and operation and maintenance records; S104. Based on the associated partition table of the target SQL file, recursively aggregate the SQL execution plan, metadata, and operation and maintenance records of each sub-partition to obtain the global execution plan, global metadata, and global operation and maintenance records of the parent table dimension. S105. Perform data cleaning and fusion on the global execution plan, global metadata, and global operation and maintenance records to generate a visual diagnostic report.

[0040] This invention is compatible with all enterprise operation and maintenance scenarios deploying PostgreSQL databases, supporting standalone, clustered, and partitioned table business databases. The system features a lightweight software architecture, including a configuration parsing module, an SQL preprocessing module, a multi-dimensional data acquisition module, a partitioned data aggregation module, and a visualization report generation module. The multi-dimensional data acquisition module further includes an execution plan analysis module, a metadata acquisition module, and an operation and maintenance data statistics module. Each module is independent and decoupled, establishing a database session by locally reading SQL files and loading independent configuration files, requiring no manual intervention throughout the process, and ultimately outputting a diagnostic report locally. Details are as follows: Configuration parsing module: Reads the db_config.json configuration file, loads database connection parameters (IP, port, account, etc.), completes identity authentication and establishes a PostgreSQL database session, providing basic support for subsequent diagnostics; SQL preprocessing module: Reads the local SQL file to be diagnosed, performs syntax verification and automatic SQL formatting (keywords uppercase, indentation), and intelligently identifies and filters redundant nodes in CTE common expressions, while being compatible with case-sensitive table / field names. Execution plan analysis module: Automatically calls the EXPLAIN command to parse the SQL execution plan, capture core information such as query path, index hit logic, and execution cost estimation, and identify SQL execution efficiency bottlenecks; Metadata collection module: Retrieves PostgreSQL system views and automatically collects metadata such as field structure, data type, primary key / unique constraint, and index information (index columns, type, size) of related tables; Operation and maintenance data statistics module: capture database operation and maintenance logs, count the frequency of DML (add / delete / update) operations, the number of live / dead tuples, and trace the execution time and status of ANALYZE, VACUUM, and AUTOVACUUM; Partitioned data aggregation module: It adopts a unique recursive aggregation algorithm to automatically traverse all sub-partitions of the partitioned table, collect the storage, index, and tuple data of each sub-partition, and summarize them to the parent table dimension to realize global statistics of the partitioned table. Report generation module: Integrates all collected and aggregated data, cleans up redundant information, unifies statistical standards, and automatically generates standardized HTML visual diagnostic reports, which are then output locally for viewing and archiving.

[0041] The modules work together to complete the entire process of "configuration loading - SQL preprocessing - data collection - partition aggregation - report generation", achieving one-click automated diagnosis without manual intervention.

[0042] It can be recognized that the embodiments of the present invention establish a database session through configuration parsing, complete statement formatting and CTE redundancy filtering through SQL preprocessing, and collect full-dimensional data such as execution plans, metadata, and operation and maintenance records in parallel. Based on the associated partition table, the data of each sub-partition is summarized to obtain the global execution plan, global metadata, and global operation and maintenance records. Finally, a standardized HTML diagnostic report is automatically generated, which can realize one-click automated diagnosis of all types of SQL, solve the problem of global statistics of partition tables, lower the diagnostic threshold, greatly improve the efficiency of database operation and maintenance, realize standardized archiving of diagnostic results, and adapt to PostgreSQL database operation and maintenance scenarios in multiple fields.

[0043] like Figure 2The diagram shown is a flowchart of step S101 provided in an embodiment of the present invention. (Refer to...) Figure 2 As an optional implementation, the database configuration file is read, database connection parameters are loaded, authentication is performed based on the database connection parameters, and a PostgreSQL database session is established. Specifically, this includes: S1011. When SQL diagnostics is started, the configuration parsing module reads the database configuration file and parses the database configuration file to obtain the database connection parameters, which include the database IP, port, account, password and retrieval path. S1012. Perform authentication based on database connection parameters, and establish a PostgreSQL database session after successful authentication.

[0044] Specifically, the first step is to load the configuration and establish the session. The main execution unit is the configuration parsing module, and the trigger condition is to start the diagnostic program. The operation is as follows: read the independent db_config.json configuration file, automatically load the database IP, port, account, password, search path search_path and SQL-level custom running parameters, complete identity authentication, and establish a secure and stable dedicated PostgreSQL database session.

[0045] like Figure 3 The diagram shown is a flowchart of step S102 provided in an embodiment of the present invention. (Refer to...) Figure 3 As an optional implementation, the SQL file to be diagnosed is read, and its syntax is checked, formatted, and redundant nodes are filtered to obtain the target SQL file, specifically including: S1021. Read the SQL file to be diagnosed based on the PostgreSQL database session using the SQL preprocessing module; S1022. Perform syntax compliance checks on the SQL file to be diagnosed to filter invalid characters, and perform formatting and redundant node identification and filtering to obtain the target SQL file.

[0046] Specifically, SQL file preprocessing and intelligent optimization are then performed. The main execution body is the SQL preprocessing module, triggered by the successful establishment of a database session. The operation is as follows: read the local diagnostic file with the .sql extension and complete three processing steps: ① syntax compliance verification, filtering invalid characters; ② automatic SQL formatting, unifying keyword capitalization and standardizing indentation; ③ built-in CTE intelligent recognition algorithm, automatically skipping redundant nodes in common table expressions to avoid invalid data collection interfering with diagnostic accuracy; and natively compatible with case-sensitive table and field names.

[0047] like Figure 4The diagram shown is a flowchart of step S103 provided in an embodiment of the present invention. (Refer to...) Figure 4 As an optional implementation, the target SQL file undergoes comprehensive data collection to obtain the SQL execution plan, metadata, and operational records, specifically including: S1031. Capture the SQL execution plan of the target SQL file by calling the EXPLAIN command through the execution plan analysis module; S1032. The metadata collection module retrieves the PostgreSQL system view and collects the field structure, data type, primary key / unique constraint, and index information of the associated tables to obtain metadata. S1033. Capture database operation and maintenance logs through the operation and maintenance data statistics module, and obtain operation and maintenance records based on the database operation and maintenance logs to count the frequency of DML operations and the number of live tuples / dead tuples.

[0048] Specifically, then full-dimensional parallel automatic data collection is performed. The main execution modules are the execution plan analysis module, the metadata collection module, and the operation and maintenance data statistics module. The trigger condition is the completion of SQL preprocessing, and the operation is to synchronously collect six types of core data, as follows: ① Automatically invoke the EXPLAIN command to capture the complete SQL execution plan, scan method, index hit logic, and full-chain execution cost estimation; ② Retrieve the system view and collect all fields, data types, primary key / unique constraints, field default values, and remarks information of the associated tables; ③ Traverse the index metadata and collect the index name, index fields, index type, and index storage space occupied; ④ Real-time statistics on table physical storage space, total index size, and estimated number of rows in the database; ⑤ Capture system statistics, calculate historical INSERT / UPDATE / DELETE frequencies, and accurately count the number of live tuples and dead tuples under the MVCC architecture; ⑥ Trace the operation and maintenance logs to pinpoint the execution time and running status of the most recent ANALYZE statistics update, manual VACUUM, and AUTOVACUUM automatic garbage collection.

[0049] like Figure 5 The diagram shown is a flowchart of step S104 provided in an embodiment of the present invention. (Refer to...) Figure 5 As an optional implementation, the SQL execution plan, metadata, and operation and maintenance records of each sub-partition are recursively aggregated based on the associated partition table of the target SQL file to obtain the global execution plan, global metadata, and global operation and maintenance records at the parent table level, specifically including: S1041. Obtain the associated partition table of the target SQL file through the partition data aggregation module, and determine the association relationship between each sub-partition and the parent table based on the associated partition table. S1042. Traverse the sub-partitions and summarize the SQL execution plan, metadata, and operation and maintenance records of each sub-partition into the corresponding parent table according to the relationship to obtain the global execution plan, global metadata, and global operation and maintenance records.

[0050] Specifically, then a recursive aggregation of data specific to the partition table is performed. The execution body is the partition data aggregation module, and the trigger condition is to identify the associated partition table of the SQL to be diagnosed. The operation is as follows: automatically recursively traverse all child nodes of the partition table, accumulate and summarize the storage capacity, index size, tuple data, etc. of all child partitions, and uniformly collect and display them on the parent table dimension to obtain the global execution plan, global metadata, and global operation and maintenance records, thereby realizing the visualization of the global performance and space status of the partition table.

[0051] like Figure 6 The diagram shown is a flowchart of step S105 provided in an embodiment of the present invention. (Refer to...) Figure 6 As an optional implementation, the global execution plan, global metadata, and global operation and maintenance records are further cleaned and integrated to generate a visual diagnostic report, specifically including: S1051. Perform data cleaning on the global execution plan, global metadata, and global operation and maintenance records to remove redundant data; S1052. Standardize the statistical criteria for global execution plans, global metadata, and global operation and maintenance records after data cleaning; S1053. Generate a visual diagnostic report based on the preset diagnostic rules and unified statistical standards of the global execution plan, global metadata, and global operation and maintenance records.

[0052] Specifically, the final step involves data fusion and standardized report generation. The main body responsible for this is the visualization report generation module. The trigger condition is that all collected and aggregated data is available. The operation involves cleaning redundant data, unifying the statistical standards of all indicators, and associating and integrating information from all dimensions, including SQL formatted text, execution plan, table structure, indexes, storage, tuples, and operation and maintenance records, to automatically generate an HTML-formatted visualization diagnostic report, which is then saved to the local directory.

[0053] After completing the above steps, the program terminates, and the operations and maintenance personnel only need to view the local HTML report to complete the entire SQL performance diagnosis process.

[0054] like Figure 7 The diagram shown illustrates another step flowchart of the full-dimensional SQL automated diagnostic method provided in this embodiment of the invention. Further, as an optional implementation, the full-dimensional SQL automated diagnostic method also includes the following steps: S106. Determine the risk level of the SQL file to be diagnosed based on the visual diagnostic report, and issue an early warning for the SQL file to be diagnosed based on the risk level.

[0055] Specifically, in some optional embodiments, a batch diagnostic extension capability can be added to support reading multiple SQL files in a folder at once, executing the above process in a loop, and generating independent diagnostic reports for each SQL file in batches; at the same time, a risk marking mechanism is added to automatically identify high-risk SQL such as excessive dead tuples, index failure, partition space expansion, and long-term non-execution of ANALYZE / VACUUM, and highlight them in the report to achieve automated risk screening.

[0056] This invention can reduce hours of manual diagnosis to seconds with a single click, fully covering multiple diagnostic dimensions without omissions, accurately solving blind spots in partitioned table data statistics, and enabling even those with no prior experience to understand the report and complete the investigation; it supports batch operation and maintenance screening, automatically marks high-risk performance vulnerabilities, and is suitable for enterprise scenarios such as large-scale slow SQL inspections and daily database health self-checks, further reducing the workload of operation and maintenance.

[0057] The method flow of the present invention has been described above. It is understood that the embodiments of the present invention establish a database session through configuration parsing, complete statement formatting and CTE redundancy filtering through SQL preprocessing, and collect full-dimensional data such as execution plans, metadata, and operation and maintenance records in parallel. Based on the associated partition table, the data of each sub-partition is summarized to obtain the global execution plan, global metadata, and global operation and maintenance records. Finally, a standardized HTML diagnostic report is automatically generated, enabling one-click automated diagnosis of all types of SQL, solving the problem of global statistics for partition tables, lowering the diagnostic threshold, significantly improving database operation and maintenance efficiency, achieving standardized archiving of diagnostic results, and adapting to PostgreSQL database operation and maintenance scenarios in multiple fields.

[0058] Compared with the prior art, the embodiments of the present invention have the following advantages: 1) SQL preprocessing and full statement compatibility architecture with integrated CTE intelligent filtering: Unlike traditional tools that only support SELECT statements, this invention is compatible with all DML statements (CRUD operations), automatically eliminates redundant CTE parsing interference, adapts to case-sensitive complex databases, and greatly improves diagnostic accuracy and scenario adaptability.

[0059] 2) PostgreSQL partition table recursive aggregation and parent-child table data unified aggregation algorithm: The original partition data aggregation logic solves the industry blind spot that existing technologies cannot globally count partition storage and index space, and accurately locates partition space expansion and index redundancy hidden faults.

[0060] 3) Single-trigger linkage collection of execution plan + metadata + full-chain operation and maintenance data: No need for manual querying of multiple system views, automatically collects all core information such as SQL specifications, execution logic, table structure, indexes, storage, tuples, and operation and maintenance records, eliminating the possibility of manual omission of key diagnostic evidence.

[0061] 4) Configuration file decoupling + lightweight one-click execution + standardized HTML report output: simple deployment, convenient operation, no reliance on expert experience, diagnostic results can be archived, traceable, and can be collaborated across teams, adaptable to the long-term operation and maintenance of enterprise standardized databases.

[0062] 5) Integrating MVCC tuple statistics with full lifecycle operation and maintenance record traceability: accurately identifying hidden performance problems such as dead tuple accumulation, untimely garbage collection, and outdated statistical information, and locating the core reasons for SQL lag and excessive space consumption from the root.

[0063] like Figure 8 The diagram shown is a structural schematic of the full-dimensional SQL automated diagnostic device provided in an embodiment of the present invention. (Refer to...) Figure 8 This invention provides a comprehensive SQL automated diagnostic device, comprising: The configuration parsing module is used to read the database configuration file, load the database connection parameters, perform identity authentication based on the database connection parameters, and establish a PostgreSQL database session. The SQL preprocessing module is used to read the SQL file to be diagnosed, perform syntax verification, formatting, and filtering of redundant nodes in the SQL file to be diagnosed, and obtain the target SQL file. The full-dimensional data acquisition module is used to perform full-dimensional data acquisition on the target SQL file to obtain the SQL execution plan, metadata, and operation and maintenance records. The partition data aggregation module is used to recursively aggregate the SQL execution plans, metadata, and operation and maintenance records of each sub-partition based on the associated partition table of the target SQL file, so as to obtain the global execution plan, global metadata, and global operation and maintenance records of the parent table dimension. The report generation module is used to clean and integrate global execution plans, global metadata, and global operation and maintenance records to generate visual diagnostic reports.

[0064] It is understood that the content of the above method embodiments is applicable to the present device embodiments. The specific functions implemented by the present device embodiments are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.

[0065] This invention also provides an electronic device, comprising: a memory, a processor, a program stored in the memory and executable on the processor, and a data bus for communication between the processor and the memory. When the program is executed by the processor, it implements the aforementioned full-dimensional SQL automated diagnostic method. This electronic device can be any smart terminal, including tablet computers, in-vehicle computers, etc.

[0066] like Figure 9 The diagram shown is a hardware structure schematic of an electronic device provided in an embodiment of the present invention. (Refer to...) Figure 9 This invention provides an electronic device, comprising: The processor 901 can be implemented using a general-purpose CPU (Central Processing Unit), microprocessor, application-specific integrated circuit (ASIC), or one or more integrated circuits, and is used to execute relevant programs to implement the technical solutions provided in the embodiments of the present invention. The memory 902 can be implemented as a read-only memory (ROM), static storage device, dynamic storage device, or random access memory (RAM). The memory 902 can store the operating system and other applications. When the technical solutions provided in the embodiments of this specification are implemented through software or firmware, the relevant program code is stored in the memory 902 and called and executed by the processor 901 using the full-dimensional SQL automated diagnostic method of the embodiments of this invention. The input / output interface 903 is used to implement information input and output; The communication interface 904 is used to enable communication and interaction between this device and other devices. Communication can be achieved through wired means (such as USB, Ethernet cable, etc.) or wireless means (such as mobile network, WIFI, Bluetooth, etc.). Bus 905 transmits information between various components of the device (e.g., processor 901, memory 902, input / output interface 903, and communication interface 904); The processor 901, memory 902, input / output interface 903, and communication interface 904 are connected to each other within the device via bus 905.

[0067] It is understood that the content of the above method embodiments is applicable to this device embodiment. The specific functions implemented by this device embodiment are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.

[0068] like Figure 10 The diagram shown is a structural schematic of the storage medium provided in an embodiment of the present invention. (Refer to...) Figure 10 The present invention also provides a storage medium, which is a computer-readable storage medium for computer-readable storage. The storage medium stores one or more programs 1001, which can be executed by one or more processors to realize the above-mentioned full-dimensional SQL automated diagnosis method.

[0069] It is understood that the content of the above method embodiments is applicable to this storage medium embodiment. The specific functions implemented in this storage medium embodiment are the same as those in the above method embodiments, and the beneficial effects achieved are also the same as those achieved in the above method embodiments.

[0070] This invention also discloses a computer program product, including a computer program that, when executed by a processor, implements the aforementioned full-dimensional SQL automated diagnostic method.

[0071] It is understood that the content of the above method embodiments is applicable to the embodiments of this program product. The specific functions implemented by the embodiments of this program product are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.

[0072] Memory, as a non-transitory computer-readable storage medium, can be used to store non-transitory software programs and non-transitory computer-executable programs. Furthermore, memory may include high-speed random access memory, and may also include non-transitory memory, such as at least one disk storage device, flash memory device, or other non-transitory solid-state storage device. In some embodiments, memory may optionally include memory remotely located relative to the processor, and these remote memories can be connected to the processor via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof.

[0073] The embodiments described in this invention are for the purpose of more clearly illustrating the technical solutions of the embodiments of this invention, and do not constitute a limitation on the technical solutions provided by the embodiments of this invention. As those skilled in the art will know, with the evolution of technology and the emergence of new application scenarios, the technical solutions provided by the embodiments of this invention are also applicable to similar technical problems.

[0074] The terms "first," "second," "third," "fourth," etc. (if present) in the specification and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0075] In some alternative embodiments, the functions / operations mentioned in the block diagrams may not occur in the order shown in the operation diagrams. For example, depending on the functions / operations involved, two consecutively shown blocks may actually be executed substantially simultaneously, or the aforementioned blocks may sometimes be executed in reverse order. Furthermore, the embodiments presented and described in the flowcharts of this invention are provided by way of example to provide a more comprehensive understanding of the technology. The disclosed methods are not limited to the operations and logic flows presented herein. Alternative embodiments are contemplated in which the order of various operations is changed and sub-operations described as part of a larger operation are executed independently.

[0076] Furthermore, although the invention has been described in the context of functional modules, it should be understood that, unless otherwise stated, one or more of the aforementioned functions and / or features may be integrated into a single physical device and / or software module, or one or more functions and / or features may be implemented in a separate physical device or software module. It is also understood that a detailed discussion of the actual implementation of each module is unnecessary for understanding the invention. Rather, given the properties, functions, and internal relationships of the various functional modules in the apparatus disclosed herein, the actual implementation of the module will be understood within the scope of conventional skill of an engineer. Therefore, those skilled in the art can implement the invention as set forth in the claims using ordinary techniques without excessive experimentation. It is also understood that the specific concepts disclosed are merely illustrative and not intended to limit the scope of the invention, which is determined by the full scope of the appended claims and their equivalents.

[0077] If the aforementioned functions are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this invention, or the part that contributes to the prior art, or a portion of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0078] The logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be embodied in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus, or device (such as a computer-based system, a processor-including system, or other system that can fetch and execute instructions from, an instruction execution system, apparatus, or device). For the purposes of this specification, "computer-readable medium" can be any means that can contain, store, communicate, propagate, or transmit programs for use by, or in conjunction with, an instruction execution system, apparatus, or device.

[0079] More specific examples (a non-exhaustive list) of computer-readable media include: electrical connections (electronic devices) having one or more wires, portable computer disk drives (magnetic devices), random access memory (RAM), read-only memory (ROM), erasable and editable read-only memory (EPROM or flash memory), fiber optic devices, and portable optical disc read-only memory (CDROM). Furthermore, computer-readable media can even be paper or other suitable media on which the aforementioned program can be printed, because the aforementioned program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting, or otherwise processing as necessary, and then stored in computer memory.

[0080] It should be understood that various parts of the present invention can be implemented in hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented in software or firmware stored in memory and executed by a suitable instruction execution system. For example, if implemented in hardware, as in another embodiment, it can be implemented using any one or a combination of the following techniques known in the art: discrete logic circuits having logic gates for implementing logical functions on data signals, application-specific integrated circuits (ASICs) having suitable combinational logic gates, programmable gate arrays (PGAs), field-programmable gate arrays (FPGAs), etc.

[0081] In the foregoing description of this specification, references to terms such as "one embodiment," "another embodiment," or "some embodiments" indicate that a specific feature, structure, material, or characteristic described in connection with an embodiment or example is included in at least one embodiment or example of the present invention. In this specification, 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 may be combined in any suitable manner in one or more embodiments or examples.

[0082] Although embodiments of the invention have been shown and described, those skilled in the art will understand that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the claims and their equivalents.

[0083] The above is a detailed description of the preferred embodiments of the present invention. However, the present invention is not limited to the above embodiments. Those skilled in the art can make various equivalent modifications or substitutions without departing from the spirit of the present invention. All such equivalent modifications or substitutions are included within the scope defined by the claims of this application.

Claims

1. A comprehensive SQL automated diagnostic method, characterized in that, Includes the following steps: Read the database configuration file, load the database connection parameters, perform identity authentication based on the database connection parameters, and establish a PostgreSQL database session. Read the SQL file to be diagnosed, perform syntax verification, formatting, and redundant node filtering on the SQL file to be diagnosed, and obtain the target SQL file; The target SQL file is subjected to full-dimensional data collection to obtain the SQL execution plan, metadata, and operation and maintenance records; Based on the associated partition table of the target SQL file, the SQL execution plan, metadata, and operation and maintenance records of each sub-partition are recursively aggregated to obtain the global execution plan, global metadata, and global operation and maintenance records at the parent table level. The global execution plan, global metadata, and global operation and maintenance records are cleaned and integrated to generate a visual diagnostic report.

2. The full-dimensional automated SQL diagnostic method according to claim 1, characterized in that, The process of reading the database configuration file, loading the database connection parameters, performing authentication based on the database connection parameters, and establishing a PostgreSQL database session specifically includes: When SQL diagnostics is started, the configuration parsing module reads the database configuration file and parses the database configuration file to obtain the database connection parameters, which include the database IP, port, username, password, and retrieval path. Authentication is performed based on the database connection parameters, and a PostgreSQL database session is established after successful authentication.

3. The full-dimensional automated SQL diagnostic method according to claim 1, characterized in that, The process of reading the SQL file to be diagnosed, performing syntax validation, formatting, and filtering redundant nodes on the SQL file to obtain the target SQL file specifically includes: The SQL file to be diagnosed is read based on the PostgreSQL database session using the SQL preprocessing module; The SQL file to be diagnosed is subjected to syntax compliance verification to filter invalid characters, and is formatted and redundant nodes are identified and filtered to obtain the target SQL file.

4. The full-dimensional automated SQL diagnostic method according to claim 1, characterized in that, The process of collecting comprehensive data from the target SQL file to obtain the SQL execution plan, metadata, and operation and maintenance records specifically includes: The execution plan analysis module calls the EXPLAIN instruction to capture the SQL execution plan of the target SQL file; The metadata is obtained by retrieving the PostgreSQL system view through the metadata acquisition module and collecting the field structure, data type, primary key / unique constraint and index information of the associated tables; The database operation and maintenance logs are captured by the operation and maintenance data statistics module. The frequency of DML operations and the number of live tuples / dead tuples are counted based on the database operation and maintenance logs to obtain the operation and maintenance record.

5. The full-dimensional automated SQL diagnostic method according to claim 1, characterized in that, The recursive aggregation of the SQL execution plan, metadata, and operation and maintenance records of each sub-partition based on the associated partition table of the target SQL file to obtain the global execution plan, global metadata, and global operation and maintenance records at the parent table level specifically includes: The associated partition table of the target SQL file is obtained through the partition data aggregation module, and the association relationship between each sub-partition and the parent table is determined based on the associated partition table. Traverse the sub-partitions and, based on the association, aggregate the SQL execution plan, metadata, and operation and maintenance records of each sub-partition to the corresponding parent table to obtain the global execution plan, global metadata, and global operation and maintenance records.

6. The full-dimensional automated SQL diagnostic method according to claim 1, characterized in that, The step of cleaning and fusing the global execution plan, global metadata, and global operation and maintenance records to generate a visual diagnostic report specifically includes: Data cleaning is performed on the global execution plan, the global metadata, and the global operation and maintenance records to remove redundant data; A unified statistical standard is applied to the global execution plan, global metadata, and global operation and maintenance records after data cleaning. The visualized diagnostic report is generated based on the global execution plan, global metadata, and global operation and maintenance records after the preset diagnostic rules and unified indicator statistical standards are applied.

7. A comprehensive SQL automated diagnostic method according to any one of claims 1 to 6, characterized in that, The comprehensive SQL automated diagnostic method also includes the following steps: The risk level of the SQL file to be diagnosed is determined based on the visual diagnostic report, and an early warning is issued for the SQL file to be diagnosed based on the risk level.

8. A full-dimensional automated SQL diagnostic device, characterized in that, include: The configuration parsing module is used to read the database configuration file, load the database connection parameters, perform identity authentication based on the database connection parameters, and establish a PostgreSQL database session. The SQL preprocessing module is used to read the SQL file to be diagnosed, perform syntax verification, formatting, and redundant node filtering on the SQL file to be diagnosed, and obtain the target SQL file. The full-dimensional data acquisition module is used to perform full-dimensional data acquisition on the target SQL file to obtain the SQL execution plan, metadata, and operation and maintenance records. The partition data aggregation module is used to recursively aggregate the SQL execution plan, metadata, and operation and maintenance records of each sub-partition based on the associated partition table of the target SQL file, so as to obtain the global execution plan, global metadata, and global operation and maintenance records of the parent table dimension. The report generation module is used to clean and integrate the global execution plan, the global metadata, and the global operation and maintenance records to generate a visual diagnostic report.

9. An electronic device, characterized in that, The electronic device includes a memory, a processor, a computer program stored in the memory and executable on the processor, and a data bus for enabling communication between the processor and the memory. When the computer program is executed by the processor, it implements the full-dimensional SQL automated diagnostic method as described in any one of claims 1 to 7.

10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by the processor, it implements the full-dimensional SQL automated diagnostic method as described in any one of claims 1 to 7.

Images