A Database Slow Query Analysis and Optimization Method Based on a Large Model
By preprocessing and fusion of structured information from slow database queries, and using a large language model for causal reasoning, this approach solves the problems of low diagnostic accuracy and ineffective optimization suggestions caused by limited information in existing technologies, thus achieving efficient and reliable slow database query optimization.
Patent Information
- Application Number
- CN202611245602.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2026-08-17
- Publication Date
- 2026-11-17
AI Technical Summary
Existing slow query optimization methods based on large models directly use a single SQL text as input, failing to integrate multi-dimensional information such as table structure, execution plan, and business semantics. This results in low diagnostic accuracy and optimization suggestions that cannot be directly implemented, affecting database operation and maintenance efficiency and system stability.
Collect and preprocess slow query statements from the database, filter target statements through desensitization and parameter normalization, obtain structured information, build enhanced prompts and input them into a large language model for causal reasoning, output standardized results, display the optimized results on the front end, record user feedback and feed it back to the knowledge base.
By integrating multi-dimensional information for diagnosis, the model's comprehension bias is reduced. It provides directly executable index creation statements and high-performance query statements, lowering the barrier to entry and establishing an optimization feedback loop to gradually improve the accuracy and adaptability of optimization suggestions.
Smart Images

Figure CN123115182A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of intelligent data processing technology, and in particular to a method for slow query analysis and optimization of databases based on large models. Background Technology
[0002] Databases, as a core infrastructure in the financial industry, are widely used in core banking transactions and accounting processes. With the continuous growth of business data volume and concurrency, slow queries have become a key bottleneck affecting system response performance. Existing technologies utilize the collaborative application of rule engines, traditional machine learning, and large language models to construct a technical system from static SQL analysis to intelligent optimization. This system covers key aspects such as slow query log collection, execution plan parsing, and index recommendation. While the large model-based approach generates optimization suggestions through semantic understanding, its input dimensions are relatively limited.
[0003] However, existing slow query optimization methods based on large models directly use a single SQL text as input without integrating multi-dimensional information such as table structure (including existing indexes), execution plan, and business semantics. This may lead to low diagnostic accuracy or the output suggestions may not be directly implemented, thus affecting database operation and maintenance efficiency and system stability. Summary of the Invention
[0004] The main objective of this invention is to provide a method for slow query analysis and optimization of databases based on large models.
[0005] Another objective of this invention is to propose a database slow query analysis and optimization device based on a large model.
[0006] The third objective of this invention is to provide an electronic device.
[0007] The fourth objective of this invention is to provide a non-transitory computer-readable storage medium.
[0008] To achieve the above objectives, a first aspect of the present invention proposes a method for slow query analysis and optimization of databases based on a large model, comprising: S1. Collect and preprocess slow query statements in the database, perform desensitization and parameter normalization on the slow query statements, and filter out the target slow query statements to be analyzed. S2, for each target slow query statement, obtain the corresponding structured information; S3 constructs enhanced prompts from structured information according to a preset template, guides the large language model to perform causal reasoning, and outputs standardized results; S4 inputs the enhanced suggestions into the large language model and receives the standardized results from the large language model, including root cause analysis, directly executable index creation statements, and rewritten high-performance query statements. S5 receives the standardized output results, displays the optimization results on the front-end operation and maintenance platform, records the user's adoption of the optimization results and the actual performance change data after the go-live, and feeds it back to the knowledge base.
[0009] Optionally, slow query statements in the database are collected and preprocessed, and the slow query statements are anonymized and their parameters are normalized to filter out the target slow query statements to be analyzed, including: Slow query statements whose execution time exceeds a preset threshold can be captured in real time through a database agent or application performance monitoring platform; Sensitive literals in captured slow query statements are de-identified and replaced, and constant parameters in query conditions are normalized. Calculate the SQL fingerprint of the slow query statement after anonymization and normalization, and perform deduplication and aggregation on the slow query statement based on the SQL fingerprint to filter out the target slow query statement to be analyzed.
[0010] Optionally, for each target slow query, obtain the corresponding structured information, including: Obtain the text of the target slow query statement after desensitization and standardization processing, and obtain the table definition statements of the database tables involved in the target slow query statement, including field definitions, data types, primary keys, unique constraints, and all existing index information, by querying the database system tables; Execute the EXPLAIN command on the target slow query statement in the original database to obtain the key features of the execution plan, which includes access type, estimated number of rows scanned, and additional information. Extract the business semantic annotations of the database tables and fields involved in the target slow query statement.
[0011] Optionally, structured information can be used to build enhanced hints according to a preset template, including: The structured information is assembled according to a preset template into enhanced prompts that include role settings, task descriptions, slow SQL text, table structure DDL, business semantic descriptions, and key information of the execution plan. The enhanced prompts also include instructions to output root cause analysis, directly executable index creation statements, and rewritten high-performance query statements.
[0012] Optionally, it receives standardized results from the large language model output, including root cause analysis, directly executable index creation statements, and rewritten high-performance query statements, including: Receive the standardized JSON format results output by the large language model. The standardized JSON format results include root cause analysis fields, an array of index creation statements, a rewritten high-performance query statement string, a rewriting reason field, and a confidence score field.
[0013] To achieve the above objectives, a second aspect of the present invention provides a database slow query analysis and optimization device based on a large model, comprising: The preprocessing unit is used to collect and preprocess slow query statements in the database, perform desensitization and parameter normalization on the slow query statements, and filter out the target slow query statements to be analyzed. The data collection unit is used to obtain the corresponding structured information for each target slow query statement; The enhancement unit is used to construct enhanced prompts from structured information according to a preset template, guide the large language model to perform causal reasoning and output standardized results; The parsing unit is used to input enhanced hints into the large language model and receive standardized results from the large language model, including root cause analysis, directly executable index creation statements, and rewritten high-performance query statements. The feedback unit is used to receive standardized output results, display optimization results on the front-end operation and maintenance platform, record user adoption of optimization results and actual performance changes after deployment, and feed them back to the knowledge base.
[0014] Regarding the apparatus in the above embodiments, the specific manner in which each unit performs its operation has been described in detail in the embodiments related to the method, and will not be elaborated upon here.
[0015] To achieve the above objectives, a third aspect of this application provides an electronic device, including a processor and a memory; wherein the processor reads executable program code stored in the memory to run a program corresponding to the executable program code, for implementing the database slow query analysis and optimization method based on a large model as described in the first aspect embodiment.
[0016] To achieve the above objectives, a fourth aspect of this application provides a non-transitory computer-readable storage medium storing a computer program that, when executed by a processor, implements the database slow query analysis and optimization method based on a large model as described in the first aspect embodiment.
[0017] The embodiments of the present invention have the following beneficial effects: This invention goes beyond relying solely on SQL text analysis. It integrates execution plans, table structures, and field annotations with business semantics, and provides structured annotations for the execution plans to reduce model comprehension biases. It can also provide reasonable indexing schemes based on data distribution, avoiding ineffective optimization overhead. Ordinary users do not need to have professional database knowledge; they can directly execute the output DDL statements, reducing optimization time from hours to minutes, significantly lowering the barrier to entry. At the same time, it establishes an optimization feedback loop, continuously accumulating business tuning experience. The optimization suggestions will be continuously refined with use, gradually evolving into a dedicated database tuning assistant tailored to specific business needs. Attached Figure Description
[0018] The above and / or additional aspects and advantages of the present invention will become apparent and readily understood from the following description of the embodiments taken in conjunction with the accompanying drawings, wherein: Figure 1 is a flowchart of a large model-based database slow query analysis and optimization method provided by an embodiment of the present invention; Figure 2 is a structural diagram of a large model-based database slow query analysis and optimization apparatus provided by an embodiment of the present invention. Detailed Description of the Embodiments
[0019] It should be noted that the embodiments of the present invention and the features in the embodiments may be combined with each other on a non-conflicting basis. The present invention will be described in detail below with reference to the accompanying drawings in conjunction with the embodiments.
[0020] In order to enable those skilled in the art to better understand the solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all of them. All other embodiments obtained by a person of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.
[0021] The large model-based database slow query analysis and optimization method and apparatus proposed according to the embodiments of the present invention are described below with reference to the accompanying drawings.
[0022] Embodiment 1 An embodiment of the present invention provides a large model-based database slow query analysis and optimization method, as shown in Figure 1 , the method includes the following steps: S1: Collect and preprocess slow query statements in the database, perform desensitization processing and parameter normalization on the slow query statements, and filter out target slow query statements to be analyzed.
[0023] In order to solve the problems of poor real-time performance of traditional slow query collection, sensitive data leakage and high overhead of repeated analysis, the present application realizes automatic slow query collection, desensitization normalization and intelligent filtering, and accurately outputs the target slow query to be optimized.
[0024] In the embodiment of the present application, a dedicated database Agent collection component is deployed in a supporting manner on the database server side, and is simultaneously connected to the APM (Application Performance Monitoring) platform supporting the business system. The two collection links cooperate with each other to realize all-weather uninterrupted monitoring of the native Slow Query Log of the database, complete real-time and accurate capture of slow query statements, and no manual intervention in log export and filtering operations is required.
[0025] In this embodiment, the system supports operators to customize the execution time threshold for slow query judgment. In this embodiment, 1 second is selected as an example time threshold. The system continuously compares the actual running time of each completed SQL statement and automatically filters and captures all inefficient SQL statements whose execution time exceeds the preset threshold, fully covering all kinds of slow queries generated during the entire business chain operation.
[0026] In this application embodiment, the original SQL statements generated by the business side often carry sensitive plaintext constants such as mobile phone numbers, ID card numbers, order numbers, and customer privacy text. If these are directly transmitted externally or sent to large model analysis, compliance risks will arise. At the same time, the query statements of the same business logic only differ in the values of constants such as id, status, and create_time in the WHERE condition. The overall statement structure is exactly the same, and analyzing each one will generate a large amount of repetitive computing power consumption. Therefore, this application performs two standardized preprocessing operations, namely, de-identification processing and parameter normalization, on the captured original slow queries.
[0027] In this embodiment, the system first performs sensitive information desensitization processing, automatically identifies and uniformly replaces all plaintext sensitive literals in the SQL statement, completely shields business privacy data, and meets data security and compliance requirements; then it performs parameter normalization processing, uniformly standardizes and replaces various dynamic constants bound to the query condition fields, retains only the stable and unchanging SQL query logic structure, and eliminates statement differentiation interference caused by parameter differences.
[0028] After the standardized SQL statements have undergone anonymization and parameter normalization, the system calculates a unique SQL fingerprint identifier based on the complete statement text. SQL statements with the same logical structure will generate completely identical fingerprint values. In this embodiment, the system performs batch deduplication and aggregation operations based on SQL fingerprints, merging and statistically analyzing multiple slow queries with the same structure but different original query parameters. Simultaneously, it calculates the cumulative execution frequency, average execution time per query, and CPU and memory resource usage indicators for each fingerprint-corresponding slow query. Based on preset weights, it performs a comprehensive sorting, selecting the Top-N slow SQL statements with the highest priority and the most severe impact on database performance. These are then stored in an independent slow query analysis queue as target statements for subsequent multi-source information collection and intelligent optimization analysis, accurately identifying optimization targets and reducing resource consumption caused by ineffective analysis.
[0029] In this embodiment of the application, the Agent combined with the APM platform captures timeout slow SQL in real time, performs desensitization and parameter normalization on the statements, and uses SQL fingerprint deduplication and aggregation to screen the Top-N slow SQL for key optimization, thus completing the standardized preprocessing of slow query samples.
[0030] S2 retrieves the corresponding structured information for each target slow query statement.
[0031] To overcome the shortcomings of traditional solutions that rely solely on SQL text analysis and lack sufficient information dimensions, resulting in redundant optimization solutions that are detached from business needs, this application adopts a parallel multi-source acquisition mechanism to obtain four types of structured data, providing a complete context for large-scale model inference.
[0032] In this embodiment, the outdated method of traditional single-information collection is abandoned. Instead, a four-dimensional structured information parallel collection method is used as the core innovation to comprehensively supplement the various key information required for slow query analysis, thus completely solving the optimization deviation problem caused by information gaps. In this embodiment, the system first reads the target slow SQL text that has been anonymized and parameterized. This standardized SQL statement is used as the core material for the overall analysis, ensuring that the analysis object is unified, standardized, and free from sensitive data interference.
[0033] In this embodiment, the system performs a full collection of structure and index information for the business data table associated with the target slow SQL query. By executing the SHOW CREATE TABLE statement and traversing and querying the information_schema system database table, the system accurately obtains the complete field definitions, data storage types of each field, primary key constraints, unique constraints, and other basic table structure information of the data table.
[0034] In this embodiment, the system synchronously sorts out all the index resources currently deployed in the statistical data table, fully includes the specific constituent fields of single-column indexes and composite indexes, as well as the field arrangement order of composite indexes, comprehensively understands the existing index architecture, avoids the problem of generating duplicate and redundant indexes in subsequent large models from the source, and ensures that subsequent index optimization solutions are reasonable and feasible.
[0035] To accurately pinpoint the execution bottlenecks of slow SQL queries in a real database environment, this application directly performs EXPLAIN execution plan parsing operations on the target slow query statement in the original business database, completely preserving the original execution plan data in standardized JSON format to ensure the integrity and traceability of execution information. In this embodiment, the system performs intelligent feature extraction and automatic performance bottleneck labeling on the original execution plan, accurately identifying various typical database inefficiency execution characteristics such as full table scans with the type field set to ALL, file sorting marked with the Extra field, and temporary table generation. This enables precise capture of underlying performance defects in SQL execution, providing a reliable basis for root cause analysis.
[0036] This application synchronously collects COMMENT information from data tables and corresponding fields, automatically converting the database's internally encoded field meanings, uses, and business status definitions into easily understandable natural language business semantic descriptions. By introducing business semantic information, subsequent optimization analysis of large models is no longer limited to the database technology level, but can be closely integrated with actual business scenarios to conduct comprehensive judgments, effectively improving the accuracy of slow query root cause localization and the business adaptability of optimization solutions, achieving a deep match between technical optimization and business needs.
[0037] In this embodiment, four types of structured information—standardized SQL, table structure indexes, EXPLAIN execution plans, and table field comments—are collected in parallel to fully restore the SQL runtime environment and business meaning. This supports the accurate location of performance bottlenecks in slow queries within the large model and lays the foundation for subsequent output of standardized results.
[0038] S3 constructs enhanced prompts from structured information according to preset templates, guiding the large language model to perform causal reasoning and output standardized results.
[0039] To address the issues of incomplete input information, large inference bias, and chaotic output format in large models, this application utilizes preset templates to integrate multi-dimensional structured information to construct enhanced prompts, standardize model inference logic, and limit standardized output.
[0040] In this embodiment, the disordered and fragmented information input method is abandoned, and a dedicated hierarchical prompt template adapted to the slow query optimization scenario of the database is designed. It can orderly splice and assemble the standardized slow SQL text, data table structure and existing index information, business semantic annotations, key features of the execution plan with bottleneck annotations, and other multi-dimensional structured information collected in the previous steps according to fixed logic to form a large model input content with complete information, clear hierarchy and rigorous logic, which completely solves the problems of missing context and messy information in the traditional input method.
[0041] In this embodiment, the constructed enhanced prompting system features multi-layered standardized functional modules, comprehensively constraining the reasoning perspective and output content of the large model. First, a dedicated role positioning module is established, clearly defining the large language model's identity as a senior optimization expert for the corresponding database type, standardizing the model's professional reasoning perspective. Second, a core task description module is configured, clearly defining the current reasoning task as conducting performance bottleneck analysis, root cause assessment, and optimization scheme generation for the target slow SQL. Simultaneously, the standardized slow SQL text, complete table structure and existing index DDL information, business semantic descriptions of tables and fields, and key execution plan information highlighting bottlenecks such as full table scans and file sorting are fully loaded, providing comprehensive and reliable scenario support for model reasoning.
[0042] This application adds strict standardized output constraint instructions to the enhanced prompts, restricting the content generated by the model from multiple dimensions such as reasoning logic, output content, and output format. This application explicitly requires the model to deeply analyze the underlying root causes of slow SQL execution, generate index creation statements without redundancy that can be directly executed online based on the existing index situation, provide detailed basis for the field arrangement order of composite indexes, and output optimized and rewritten high-performance SQL statements with explanations of the rewriting and optimization principles for each statement.
[0043] At the same time, this application strictly limits the final output of the model to standard JSON format, unifies the output fields and data structure, and precisely constrains the inference logic and output specifications of the large model, fundamentally eliminating the problems of invalid model output, chaotic logic, and unimplementable solutions, and ensuring that the subsequent system can automatically complete result parsing and operation and maintenance implementation.
[0044] In this embodiment, enhanced prompts are built by integrating four types of information—SQL, table structure, execution plan, and business semantics—through a layered template, and strict output constraint instructions are attached to guide the large model to output standardized and practical slow query optimization conclusions, laying the foundation for the subsequent generation of standardized results.
[0045] S4 inputs enhanced suggestions into the large language model and receives standardized results from the large language model, including root cause analysis, directly executable index creation statements, and rewritten high-performance query statements.
[0046] To address the issues of poor adaptability to large models, disorganized and unstandardized output results, and the inability to directly implement optimization statements, this application will enhance the input prompts to be compatible with a general interface for multiple types of large models, and perform multi-layered validation on the returned standardized JSON optimization results.
[0047] In this embodiment, a generalized and adaptable large model calling interface is pre-configured. The interface architecture has good compatibility and can stably connect to various mainstream large language models such as GPT-4 and DeepSeek, without requiring separate modification of adaptation code for different models, greatly improving the system's versatility and scalability. In this embodiment, the system completely and losslessly transmits the constructed multi-source fusion standardized enhanced prompts to the large model calling interface, providing the large model with full-dimensional contextual information including table structure, existing indexes, execution bottlenecks, and business semantics. This drives the large model to perform accurate causal reasoning and database optimization simulation based on complete scenario information, avoiding optimization deviations caused by missing context.
[0048] In this embodiment, the large model follows the role settings, analysis tasks, and output specifications instructions within the enhanced prompts throughout the process, and strictly outputs structured JSON format results according to a unified standard, ensuring that the fields output in each inference are consistent, the content is complete, and the format is standardized.
[0049] In this embodiment, the model output includes several core key fields, each with a clear function and working together to form a complete slow query optimization solution. The `root_cause` field accurately records the underlying root cause of the current slow SQL execution inefficiency, enabling problem localization; the `index_ddl` field is an array format field that can hold one or more index creation statements that can be directly deployed and executed online, providing a deployable script for database index optimization; the `rewritten_sql` field outputs a new, high-performance query statement that has undergone logical refactoring and improved performance, achieving performance optimization of the SQL statement itself; the `rewrite_reason` field explains in detail the index creation logic, SQL rewriting approach, and performance improvement principles, ensuring the optimization solution is well-founded; and the `confidence` field outputs a floating-point confidence score in the range of 0 to 1, used to quantitatively evaluate the reliability and rationality of the optimization solution, facilitating system and operations personnel in selecting high-quality optimization results.
[0050] To avoid issues such as random output, formatting errors, missing fields, and invalid statements in large models, this application automatically executes a multi-layered validity verification mechanism after receiving the model's returned results. In this embodiment, the system sequentially performs JSON syntax format verification, core field integrity verification, and index DDL and rewritten SQL statement syntax validity verification, automatically identifying and filtering invalid output results with formatting errors, missing fields, unexecutable statements, and logical anomalies.
[0051] In this embodiment of the application, inference is completed by passing enhanced hints through the general large model interface. It receives standardized JSON data containing root causes, index statements, rewritten SQL, and confidence scores, and automatically verifies the syntax, field and code validity of the results to output available optimization solutions.
[0052] S5 receives the standardized output results, displays the optimization results on the front-end operation and maintenance platform, records the user's adoption of the optimization results and the actual performance change data after the go-live, and feeds it back to the knowledge base.
[0053] To address the issues of inconvenient viewing of optimization results and the lack of business feedback iteration links that prevent continuous improvement in model optimization accuracy, this application visualizes the optimization scheme and collects implementation performance data to feed back into the knowledge base, thus constructing a self-evolving optimization closed loop.
[0054] The system reads the standardized optimization results that have undergone multi-layer verification and are legally valid, and pushes the entire set of data to the front-end database operation and maintenance visualization platform for regional visualization display. In this embodiment, the front-end interface is divided into independent display blocks, which respectively display the basic information related to the original slow query, the performance bottlenecks identified by the execution plan, the complete root cause analysis output by the large model, the directly executable index DDL statements, the rewritten high-performance SQL text, and the accompanying optimization logic explanation. The page is configured with a one-click copy function, which allows operation and maintenance personnel to quickly copy the index creation statements and rewritten SQL for direct deployment, eliminating the need for manual copying and organization, significantly reducing the cost of manual operation and maintenance, and improving the efficiency of optimization solution implementation.
[0055] In this embodiment, a feedback loop collection mechanism is constructed to cover the entire process. The system captures the adoption of the index and SQL optimization solutions by operations and maintenance personnel in real time and automatically retains the adoption status record. After the operations and maintenance personnel deploy the optimized statements to the online database, the system continuously collects real-world performance indicators after deployment, including the percentage decrease in average query time before and after optimization, the reduction in the number of data rows scanned per database query, and changes in server database CPU load fluctuations, among other multi-dimensional quantitative performance data, to fully record the actual performance improvement effect brought about by the optimization solution.
[0056] In this embodiment, the system uniformly backflows and stores all operation and maintenance adoption behavior records and online real performance change indicators into a dedicated system knowledge base, realizing the accumulation and reuse of feedback data. In this embodiment, the historical optimization cases and real performance data backflowed into the knowledge base have two core application values. First, the data continuously expands the RAG retrieval enhancement generation resource library as retrieval material. When building enhancement prompts later, it can retrieve and match similar historical optimization cases, enrich the model inference context, and improve the accuracy of optimization conclusions in similar slow query scenarios. Second, the accumulated real feedback data can be used for online fine-tuning of large models and iterative updates of model weights, continuously correcting model inference biases.
[0057] In this embodiment, based on the complete link of result display, data collection, and knowledge base feedback, this application builds a self-evolving closed loop of slow query collection, preprocessing, multi-source information collection, enhanced prompt construction, large model reasoning and parsing, operation and maintenance implementation, and feedback iteration, enabling the entire intelligent optimization system to continuously improve its slow query diagnosis and optimization capabilities as business operations progress.
[0058] Example 2 This invention provides a database slow query analysis and optimization device 10 based on a large model, such as... Figure 2 As shown, the device includes: The preprocessing unit 100 is used to collect and preprocess slow query statements in the database, perform desensitization and parameter normalization on the slow query statements, and filter out the target slow query statements to be analyzed. The data acquisition unit 200 is used to obtain the corresponding structured information for each target slow query statement; The enhancement unit 300 is used to construct enhanced prompts from structured information according to a preset template, guide the large language model to perform causal reasoning and output standardized results; The parsing unit 400 is used to input enhanced hints into the large language model and receive standardized results from the large language model, including root cause analysis, directly executable index creation statements, and rewritten high-performance query statements. Feedback unit 500 is used to receive standardized output results, display optimization results on the front-end operation and maintenance platform, record user adoption of optimization results and actual performance change data after go-live, and feed them back to the knowledge base.
[0059] Regarding the apparatus in the above embodiments, the specific manner in which each unit performs its operation has been described in detail in the embodiments related to the method, and will not be elaborated upon here.
[0060] Example 3 To implement the methods of the above embodiments, the present invention also provides an electronic device, which includes a memory and a processor; wherein the processor reads executable program code stored in the memory to run a program corresponding to the executable program code, so as to implement the various steps of the methods described above.
[0061] Example 4 To implement the above embodiments, this application also proposes a non-transitory computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the method described in the foregoing embodiments.
[0062] The above description is merely a preferred embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.
[0063] In the description of this specification, the references to terms such as "one embodiment," "some embodiments," "example," "specific example," or "some examples," etc., refer to specific features, structures, materials, or characteristics described in connection with that embodiment or example, which are included in at least one embodiment or example of the present invention. 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 may be combined in any suitable manner in one or more embodiments or examples. Moreover, without contradiction, those skilled in the art can combine and integrate the different embodiments or examples described in this specification, as well as the features of different embodiments or examples.
[0064] Furthermore, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include at least one of that feature. In the description of this invention, "a plurality of" means at least two, such as two, three, etc., unless otherwise explicitly specified.
Claims
1. A method for slow query analysis and optimization of databases based on a large model, characterized in that, include: S1. Collect and preprocess slow query statements in the database, perform desensitization and parameter normalization on the slow query statements, and filter out the target slow query statements to be analyzed. S2, for each target slow query statement, obtain the corresponding structured information; S3 constructs enhanced prompts from structured information according to a preset template, guides the large language model to perform causal reasoning, and outputs standardized results; S4 inputs the enhanced suggestions into the large language model and receives the standardized results from the large language model, including root cause analysis, directly executable index creation statements, and rewritten high-performance query statements. S5 receives the standardized output results, displays the optimization results on the front-end operation and maintenance platform, records the user's adoption of the optimization results and the actual performance change data after the go-live, and feeds it back to the knowledge base.
2. The method according to claim 1, characterized in that, The process of collecting and preprocessing slow query statements from the database, de-identifying and normalizing the parameters of the slow query statements, and filtering out the target slow query statements to be analyzed includes: Slow query statements whose execution time exceeds a preset threshold can be captured in real time through a database agent or application performance monitoring platform; Sensitive literals in captured slow query statements are de-identified and replaced, and constant parameters in query conditions are normalized. Calculate the SQL fingerprint of the slow query statement after anonymization and normalization, and perform deduplication and aggregation on the slow query statement based on the SQL fingerprint to filter out the target slow query statement to be analyzed.
3. The method according to claim 1, characterized in that, For each target slow query statement, the corresponding structured information is obtained, including: Obtain the text of the target slow query statement after desensitization and standardization processing, and obtain the table definition statements of the database tables involved in the target slow query statement, including field definitions, data types, primary keys, unique constraints, and all existing index information, by querying the database system tables; Execute the EXPLAIN command on the target slow query statement in the original database to obtain the key features of the execution plan, which includes access type, estimated number of rows scanned, and additional information. Extract the business semantic annotations of the database tables and fields involved in the target slow query statement.
4. The method according to claim 1, characterized in that, The step of constructing enhanced prompts from structured information according to a preset template includes: The structured information is assembled according to a preset template into enhanced prompts that include role settings, task descriptions, slow SQL text, table structure DDL, business semantic descriptions, and key information of the execution plan. The enhanced prompts also include instructions to output root cause analysis, directly executable index creation statements, and rewritten high-performance query statements.
5. The method according to claim 1, characterized in that, The standardized results received from the large language model output, including root cause analysis, directly executable index creation statements, and rewritten high-performance query statements, include: Receive the standardized JSON format results output by the large language model. The standardized JSON format results include root cause analysis fields, an array of index creation statements, a rewritten high-performance query statement string, a rewriting reason field, and a confidence score field.
6. A database slow query analysis and optimization device based on a large model, characterized in that, include: The preprocessing unit is used to collect and preprocess slow query statements in the database, perform desensitization and parameter normalization on the slow query statements, and filter out the target slow query statements to be analyzed. The data collection unit is used to obtain the corresponding structured information for each target slow query statement; The enhancement unit is used to construct enhanced prompts from structured information according to a preset template, guide the large language model to perform causal reasoning and output standardized results; The parsing unit is used to input enhanced hints into the large language model and receive standardized results from the large language model, including root cause analysis, directly executable index creation statements, and rewritten high-performance query statements. The feedback unit is used to receive standardized output results, display optimization results on the front-end operation and maintenance platform, record user adoption of optimization results and actual performance changes after deployment, and feed them back to the knowledge base.
7. An electronic device, characterized in that, Including processor and memory; The processor reads executable program code stored in the memory to run a program corresponding to the executable program code, so as to implement the method as described in any one of claims 1-5.
8. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method as described in any one of claims 1-5.
