Update method and device of query statement and electronic equipment

Abstract

The application discloses a query statement updating method and device and electronic equipment, and relates to the technical field of databases. The method comprises the following steps: generating K candidate statements based on the logical features of a slow query statement; performing semantic analysis on the logical features to obtain semantic features of the slow query statement; determining the performance index features of each candidate statement based on the performance index data of each candidate statement; generating K comparison sample pairs corresponding to the K candidate statements based on the semantic features of the slow query statement and the performance index features of each candidate statement; and updating the slow query statement based on the updating strategy after the updating strategy of the slow query statement is generated based on the K comparison sample pairs. The application solves the technical problems of low efficiency and poor effect of performance optimization of slow query statements in a database based on the prior art.

Description

Technical Field

[0001] This application relates to the field of database technology, and more specifically, to a method, apparatus, and electronic device for updating a query statement. Background Technology

[0002] In optimization scenarios for slow SQL (Structured Query Language) statements in databases, there are multiple equivalent rewriting methods for slow SQL statements. Equivalent rewriting refers to changing the query structure of the slow SQL statement, adjusting the join order, using different subquery expansion methods, adding optimization hints, or indexes, etc., while keeping the logical execution result of the SQL statement unchanged, so that the SQL statement produces different performance indicators at the execution level. Due to the bias in the cost estimation of the database optimizer, coupled with the execution differences between different database execution engines, even two SQL statements that are completely equivalent at the logical execution level may have execution efficiency differences of orders of magnitude.

[0003] Existing database optimizers struggle to exhaustively explore all possible rewrite methods when optimizing slow query statements in complex scenarios, and they also lack an evaluation process to assess the actual performance differences brought about by different rewrite methods. This results in the technical problems of low efficiency and poor performance in optimizing slow query statements in databases in existing technologies.

[0004] There is currently no effective solution to the above problems. Summary of the Invention

[0005] This application provides a method, apparatus, and electronic device for updating query statements, to at least solve the technical problems of low efficiency and poor performance in optimizing slow query statements in databases based on existing technologies.

[0006] According to one aspect of this application, a method for updating a query statement is provided, comprising: generating K candidate statements based on logical features of a slow query statement, wherein K is a positive integer, the logical features are used to characterize the logical structure of the slow query statement, and each candidate statement has the same execution result as the slow query statement on a preset sample set; performing semantic analysis on the logical features to obtain semantic features of the slow query statement, wherein the semantic features are used to characterize the business objective achieved by the slow query statement; determining the performance indicator features of each candidate statement based on the performance indicator data of each candidate statement; generating K comparison sample pairs corresponding to the K candidate statements based on the semantic features of the slow query statement and the performance indicator features of each candidate statement, wherein each comparison sample pair includes at least a semantic feature and a performance indicator feature of a candidate statement; and updating the slow query statement based on the update strategy after generating an update strategy for the slow query statement based on the K comparison sample pairs.

[0007] Optionally, before generating K candidate statements based on the logical characteristics of the slow query statement, the query statement update method further includes: after receiving the update request of the slow query statement, checking whether there is an optimization task corresponding to the update request in the task scheduling library, wherein the optimization task is used to improve the performance indicators of the slow query statement during database operation; if there is no optimization task corresponding to the update request in the task scheduling library, creating an optimization task corresponding to the update request; and after creating the optimization task corresponding to the update request, collecting the execution plan of the slow query statement in the database.

[0008] Optionally, after collecting the execution plan of the slow query statement in the database, the query statement update method further includes: parsing the statement text of the slow query statement to obtain the abstract syntax tree of the slow query statement; generating the logical plan tree of the slow query statement based on the abstract syntax tree and the execution plan; and extracting features from the logical plan tree to obtain the logical features of the slow query statement, wherein the logical features include at least one of the following: table join order, predicate, grouping, and sorting.

[0009] Optionally, K candidate statements are generated based on the logical features of the slow query statement, including: generating L initial statements based on the logical features using a large language model, where L is a positive integer greater than or equal to K, and each initial statement is semantically equivalent to the slow query statement; performing syntax verification and result alignment tests on the L initial statements, where syntax verification is used to detect syntax errors in each initial statement, and result alignment tests are used to detect the similarity between the execution results obtained by executing each initial statement and the slow query statement on a preset sample set; and selecting the initial statements that pass the syntax verification and result alignment tests from the L initial statements as candidate statements to obtain K candidate statements.

[0010] Optionally, the performance metric features of each candidate statement are determined based on the performance metric data of each candidate statement, including: determining the metric values ​​of each candidate statement in P performance metric dimensions based on the performance metric data of each candidate statement, where P is a positive integer, and the P performance metric dimensions include at least execution time, read / write count, processor utilization, and memory consumption; generating initial metric features for each candidate statement based on the metric values ​​of each candidate statement in the P performance metric dimensions; normalizing the initial metric features of each candidate statement to obtain standard metric features for each candidate statement; and spatially mapping the standard metric features of each candidate statement to obtain the performance metric features of each candidate statement, wherein the spatial mapping is used to unify the vector dimensions of the initial metric features and the semantic features.

[0011] Optionally, the update strategy for generating slow query statements based on K comparison sample pairs includes: determining target comparison sample pairs based on the K comparison sample pairs using a target model, wherein the target comparison sample pair is the comparison sample pair to which the performance indicator feature with the lowest performance overhead belongs among the K comparison sample pairs, and the target model is a comparison discriminator trained based on update cases of Q historical slow query statements in a preset experience base, where Q is a positive integer; taking the candidate statements corresponding to the performance indicator features in the target comparison sample pairs as target statements, and using the target statements as the update strategy for slow query statements.

[0012] Optionally, the training steps of the target model include: parsing the update cases of each historical slow query statement in the preset experience base to obtain K historical candidate statements corresponding to each historical slow query statement; after weighted summation of the index values ​​of each historical candidate statement on P performance index dimensions to obtain the performance cost corresponding to each historical candidate statement, the historical candidate statement with the lowest performance cost among the K historical candidate statements is taken as the historical target statement; the historical target statement corresponding to each historical slow query statement is taken as the positive sample corresponding to each historical slow query statement, and the K-1 historical candidate statements other than the historical target statement among the K historical candidate statements are taken as the negative samples corresponding to each historical slow query statement; the initial comparison discriminator is iteratively trained based on the Q groups of positive samples and Q groups of negative samples corresponding to the Q historical slow query statements to obtain the target model, wherein the initial comparison discriminator is used to determine the mapping relationship between the semantic features of each historical slow query statement and the performance index features corresponding to the historical target statement.

[0013] Optionally, after taking the candidate statements corresponding to the performance index features in the target comparison sample pair as the target statement, the query statement update method further includes: taking the slow query statement, K candidate statements, target statement, and update feedback results of the slow query statement as update cases of the slow query statement; storing the update cases of the slow query statement in a preset experience base; and updating the target model based on the new update cases in the preset experience base.

[0014] According to another aspect of this application, a query statement updating apparatus is also provided, comprising: a candidate statement generation unit, configured to generate K candidate statements based on the logical features of a slow query statement, wherein K is a positive integer, the logical features are used to characterize the logical structure of the slow query statement, and each candidate statement has the same execution result as the slow query statement on a preset sample set; a semantic analysis unit, configured to perform semantic analysis on the logical features to obtain the semantic features of the slow query statement, wherein the semantic features are used to characterize the business objective achieved by the slow query statement; an indicator feature determination unit, configured to determine the performance indicator features of each candidate statement based on the performance indicator data of each candidate statement; a sample pair generation unit, configured to generate K comparison sample pairs corresponding to the K candidate statements based on the semantic features of the slow query statement and the performance indicator features of each candidate statement, wherein each comparison sample pair includes at least a semantic feature and a performance indicator feature of a candidate statement; and a statement updating unit, configured to update the slow query statement based on the update strategy after generating an update strategy for the slow query statement based on the K comparison sample pairs.

[0015] According to another aspect of this application, a computer program product is also provided, which stores a computer program, wherein an update method for controlling the computer program product to execute the query statement of any of the above-mentioned items is provided when the computer program is running.

[0016] According to another aspect of this application, an electronic device is also provided, wherein the electronic device includes one or more processors and a memory for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors implement the update method of the query statement of any of the above.

[0017] As described above, this application first generates K candidate statements that are semantically equivalent to the slow query statement based on its logical characteristics. Then, through further semantic analysis of the logical characteristics of the slow query statement, semantic features that can characterize the business objectives of the slow query statement are obtained. Next, this application combines the semantic features of the slow query statement with the performance indicator features of the candidate statements to construct a pair of comparison samples with the semantic features and performance indicator features. By generating an update strategy based on the comparison sample pairs, the purpose of automatically optimizing and rewriting the slow query statement is achieved. This technical solution does not rely on manual rules or static cost models. Through equivalence constraints and performance feedback loops, it realizes the updating of the slow query statement, thereby achieving the effect of adaptive, data-driven performance optimization of the slow query statement. This solves the technical problem of low efficiency and poor effect of performance optimization of slow query statements in the database based on existing technologies. Attached Figure Description

[0018] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings:

[0019] Figure 1 This is a flowchart of an optional query statement update method according to an embodiment of this application;

[0020] Figure 2 This is a flowchart of an optional method for optimizing slow SQL statements based on a contrastive learning model, according to an embodiment of this application.

[0021] Figure 3 This is an architecture diagram of an optional system for optimizing slow SQL statements based on a contrastive learning model, according to an embodiment of this application.

[0022] Figure 4 This is a schematic diagram of an optional query statement update device according to an embodiment of this application;

[0023] Figure 5 This is a structural block diagram of an electronic device according to an embodiment of this application. Detailed Implementation

[0024] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort should fall within the scope of protection of the present application.

[0025] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this application 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 the embodiments of this application 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 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.

[0026] It should also be noted that all information and data (including but not limited to information used for display and analysis) involved in this application are authorized by the user or fully authorized by all parties. For example, if there is an interface between this system and the relevant user or organization, before obtaining the relevant information, it is necessary to send a request to the aforementioned user or organization through the interface, and obtain the relevant information only after receiving consent from the aforementioned user or organization.

[0027] Furthermore, the collection, storage, use, processing, transmission, provision, disclosure, and application of relevant information and data involved in this application all comply with the relevant laws, regulations, and standards of the relevant regions, and necessary confidentiality measures have been taken. This application does not violate public order and good morals. In addition, this application provides a corresponding operation entry point for users to choose to agree to or refuse authorization. If the user chooses to refuse authorization, the corresponding expert decision-making process will be initiated.

[0028] In related technologies, optimizing slow SQL statements in databases mainly relies on the following methods:

[0029] (1) Database optimizers recommend indexes and select join strategies based on cost models, but due to cost estimation errors, they are not good at optimizing complex queries.

[0030] (2) DBA (Database Administrator) manually rewrites SQL by analyzing execution plans and making judgments based on experience, but this relies on expert experience, is inefficient and difficult to scale.

[0031] (3) Some traditional AIOps (Artificial Intelligence for IT Operations) systems can automatically detect slow SQL statements and provide optimization suggestions. However, traditional AIOps systems are based on fixed rules or shallow models and cannot adapt to complex and ever-changing SQL patterns.

[0032] In summary, optimizing slow SQL statements using the aforementioned technologies presents the following problems:

[0033] (1) The rule optimizer is limited by the cost estimation error in complex scenarios, resulting in poor optimization effect on slow SQL statements.

[0034] (2) Slow SQL diagnostics lack the ability to “generate rewrite” and often remain at the level of index suggestions.

[0035] (3) Existing methods cannot be continuously improved under real-world operational feedback and lack self-learning and closed-loop optimization mechanisms.

[0036] Compared to existing methods that rely on rule-based optimization or manual tuning, this application proposes a method for optimizing slow SQL statements based on a contrastive learning model (i.e., the target model). This method leverages the generative rewriting capabilities of a large language model to overcome the limitations of traditional rules and fixed templates, enabling diverse equivalent rewriting of complex queries. Furthermore, by combining a contrastive learning mechanism, this application can continuously optimize the quality of the generated target statements under real-world operational feedback, forming a self-learning and closed-loop improvement mechanism for the contrastive learning model. This achieves automatic diagnosis and self-optimization of slow SQL statements without expert intervention, contributing to improved intelligence and adaptability in slow SQL statement optimization.

[0037] The present invention will now be described in detail with reference to various embodiments.

[0038] Example 1

[0039] According to an embodiment of this application, an embodiment of a query statement update method is provided. It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions. Furthermore, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be executed in a different order than that shown here.

[0040] This application provides a query statement update system (hereinafter referred to as the update system) for executing the query statement update method in this application. Figure 1 This is a flowchart of an optional query statement update method according to an embodiment of this application, such as... Figure 1 As shown, the method includes the following steps:

[0041] Step S101: Generate K candidate statements based on the logical features of the slow query statement, where K is a positive integer. The logical features are used to characterize the logical structure of the slow query statement. Each candidate statement has the same execution result as the slow query statement on the preset sample set.

[0042] Optionally, a preset sample set is used to verify the consistency and semantic equivalence of the execution results, avoiding the high overhead caused by testing the full amount of data in the database.

[0043] Optionally, the update system collects slow query statements and their complete execution plans in the database from a real-time database monitoring system or performance view. Then, using an SQL parsing engine, the original SQL text is transformed into an abstract syntax tree that is easy for machines to process, and further constructed into a logical plan tree. Then, key logical information (such as table join order, predicates, grouping, sorting, etc.) is extracted from the logical plan tree, and the key logical information is uniformly serialized to obtain logical features.

[0044] Optionally, after receiving an update request for a slow SQL statement (i.e., a slow query statement), the update system converts the slow SQL statement into an abstract syntax tree through the SQL parsing engine, and further constructs it into a logical plan tree. From the logical plan tree, it extracts structured elements such as table names, join methods, filtering conditions, grouping fields, and sorting fields to obtain logical features in JSON (JavaScript Object Notation, a lightweight data interchange format). Then, based on these logical features, it calls a pre-trained large language model to generate L semantically equivalent initial statements. After that, it performs syntax verification on the L initial statements and compares the execution results of each candidate SQL statement with the original slow SQL statement using a preset sample set (e.g., 1000 order data records). Only the initial SQL statements that pass the syntax verification and whose execution results are consistent are retained, resulting in K candidate statements.

[0045] Optionally, the update system drives the large language model to generate diverse candidate SQL (i.e., K candidate statements) through structured logical features, which breaks through the limitations of human experience or fixed rules. By performing result alignment tests based on a small sample set, it helps to avoid abnormal business execution results due to rewriting errors, reduces the optimization risk of slow SQL statements, and helps to improve the optimization effect of slow SQL statements.

[0046] Step S102: Perform semantic analysis on the logical features to obtain the semantic features of the slow query statement, wherein the semantic features are used to characterize the business objectives achieved by the slow query statement.

[0047] Optionally, the update system inputs the logical features in JSON document format into a large language model, performs semantic analysis on the logical features through the large language model, thereby identifying the business objectives of slow query statements, and converts the business objectives obtained from the semantic analysis into semantic vectors of preset dimensions (e.g., 128 dimensions) to obtain semantic features.

[0048] Optionally, traditional methods in related technologies only process the syntactic structure of slow query statements and cannot understand the business purpose behind the query. For example, slow SQL statements with the same logical structure may require different performance optimization directions in different business scenarios (such as "statistics of active users" or "statistics of refunded users"). The update system obtains the business goal of the slow query statement through semantic analysis, enabling subsequent optimization to have the ability to perceive the intent of business execution, which helps to improve the scenario adaptability of the generated candidate statements.

[0049] Step S103: Determine the performance indicator characteristics of each candidate statement based on the performance indicator data of each candidate statement.

[0050] Optionally, after generating K candidate statements, the update system executes each candidate statement one by one. During the execution process, the system collects various performance indicators in real time, such as total execution time, I / O (Input / Output) counts, CPU (Central Processing Unit) utilization, memory consumption, and lock wait time. By processing the collected raw performance data, the performance characteristics of each candidate statement are obtained.

[0051] Optionally, the update system executes the K candidate SQL statements in the test database of the production environment to avoid interfering with the operation of online business. It also uses database performance monitoring tools to collect performance indicator data for each candidate statement, such as execution time, number of reads and writes, processor utilization, and memory consumption. Then, it performs vectorization, normalization, and spatial mapping on the indicator values ​​in the collected performance indicator data to obtain the performance indicator characteristics of each candidate statement.

[0052] Optionally, traditional methods in related technologies predict the execution cost (i.e., performance index data) of each candidate statement based on a cost model. However, the predicted value deviates from the actual performance. The update system directly uses the performance index data obtained from actual test execution to determine the performance index features. Furthermore, the obtained performance index features are normalized and spatially mapped, enabling direct comparison of performance index features of different dimensions. This avoids the problem of misjudgment of the cost model due to differences in units of measurement, and provides a more reliable and quantifiable data foundation for subsequent comparative learning models.

[0053] Step S104: Generate K comparison sample pairs corresponding to K candidate statements based on the semantic features of the slow query statement and the performance index features of each candidate statement. Each comparison sample pair includes at least semantic features and the performance index features of a candidate statement.

[0054] Optionally, the update system first aligns the performance metric features of each candidate statement with the semantic features of the slow query statement in terms of vector dimension, and then concatenates the aligned performance metric features with the semantic features to obtain K comparison sample pairs.

[0055] Optionally, the update system aligns semantic features and performance index features, unifying data from different modalities (semantic features and performance index features) into the same vector space, eliminating dimensional differences, facilitating correlation analysis between the two, and helping to improve the processing efficiency of comparison sample pairs in subsequent comparison learning models.

[0056] Optionally, traditional methods in related technologies use performance indicators as the scoring basis and do not establish a structured mapping relationship between semantic features and performance indicator features. The update system, by constructing semantically guided comparison samples, enables the model to learn the hidden correlation between semantic features and performance indicator features, which helps to improve the selection accuracy of target comparison sample pairs among K comparison sample pairs.

[0057] Step S105: After generating an update strategy for slow query statements based on K comparison sample pairs, update the slow query statements based on the update strategy.

[0058] Optionally, the update model uses a contrastive learning model (i.e., the target model) to distinguish which candidate SQL statements have better performance based on K contrastive sample pairs, thereby determining the target statement among the K candidate statements. Then, the target statement is used as the update strategy for slow query statements.

[0059] Optionally, traditional methods in related technologies can only output recommended SQL statements (i.e. target statements) and cannot identify the reasons for recommending the SQL statements. The update system automatically accumulates optimization experience through a comparative learning mechanism to form a reusable update strategy. Each optimization case of a slow SQL statement can feed back into the comparative learning model, which helps to improve the recommendation accuracy of subsequent similar slow SQL statements.

[0060] As described above, this application first generates K candidate statements that are semantically equivalent to the slow query statement based on its logical characteristics. Then, through further semantic analysis of the logical characteristics of the slow query statement, semantic features that can characterize the business objectives of the slow query statement are obtained. Next, this application combines the semantic features of the slow query statement with the performance indicator features of the candidate statements to construct a pair of comparison samples with the semantic features and performance indicator features. By generating an update strategy based on the comparison sample pairs, the purpose of automatically optimizing and rewriting the slow query statement is achieved. This technical solution does not rely on manual rules or static cost models. Through equivalence constraints and performance feedback loops, it realizes the updating of the slow query statement, thereby achieving the effect of adaptive, data-driven performance optimization of the slow query statement. This solves the technical problem of low efficiency and poor effect of performance optimization of slow query statements in the database based on existing technologies.

[0061] In one optional embodiment, before generating K candidate statements based on the logical characteristics of the slow query statement, after the update system receives the update request for the slow query statement, it checks whether there is an optimization task corresponding to the update request in the task scheduling library. The optimization task is used to improve the performance indicators of the slow query statement during database operation. If there is no optimization task corresponding to the update request in the task scheduling library, the update system creates the optimization task corresponding to the update request. After creating the optimization task corresponding to the update request, the update system collects the execution plan of the slow query statement in the database.

[0062] Optionally, the update system defines and submits update requests for slow query statements based on database monitoring information or business feedback information. Then, the update system first checks the task scheduling library to determine whether an optimization task already exists for the slow query statement. If the task already exists, the update system reports the current processing status of the optimization task (such as pending status, in progress status, completed status, recommended status, etc.). If the task does not exist, the update system creates a new optimization task, assigns a unique task identifier, and records the creation time, submitter, and initial task status, laying the foundation for subsequent closed-loop optimization management.

[0063] Optionally, the execution plan, which characterizes the logical execution path of the slow query statement, includes at least the order of table access, the indexes used, the join method, and the sorting information of the filtering conditions, and can be obtained through the EXPLAIN command.

[0064] Optionally, after receiving an update request for a slow query, the update system checks whether there is an optimization task corresponding to the update request in the task scheduling library. This can prevent the same slow query from being repeatedly started in the optimization process. In a production environment, slow queries may be triggered for optimization multiple times by multiple monitoring cycles or by different personnel. Without a task deduplication mechanism, the system will concurrently execute the same optimization task, wasting computing resources and increasing database load. The update system implements deduplication and status management through the task scheduling library, so that each slow query is processed only once per unit of time, improving the resource utilization of the update system.

[0065] Optionally, after creating the optimization task corresponding to the update request, the update system collects the execution plan of the slow query statement in the database. Collecting the complete execution plan helps ensure the accuracy and contextual consistency of subsequent logical feature extraction. If this step is skipped, the update system can only make fuzzy inferences based on the SQL text and cannot accurately identify the actual execution structure. Especially in scenarios such as index changes and outdated statistics, the execution plan can truly reflect the bottleneck in the current environment. This provides a reliable data foundation for generating high-quality candidate SQL statements.

[0066] In one optional embodiment, after collecting the execution plan of the slow query statement in the database, the update system first parses the statement text of the slow query statement to obtain the abstract syntax tree of the slow query statement. Then, the update system generates the logical plan tree of the slow query statement based on the abstract syntax tree and the execution plan. Then, the update system extracts features from the logical plan tree to obtain the logical features of the slow query statement. The logical features include at least one of the following: table join order, predicate, grouping, and sorting.

[0067] Optionally, the update system parses the statement text and execution plan of the slow query statement through the SQL parsing engine, constructs a logical plan tree, then extracts the feature information of the logical plan tree and performs serialization processing. The feature extractor extracts the feature information of the logical plan tree, including at least the join mode of the SQL statement, subquery complexity, table name and field name information, and stores the extracted logical feature information in JSON serialization format.

[0068] For example, a slow SQL statement might be as follows:

[0069] SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id

[0070] WHERE o.amount> 1000;

[0071] The extracted logical feature information in JSON format is as follows:

[0072] {

[0073] "tables": ["users", "orders"],

[0074] "join": "users.id = orders.user_id",

[0075] "filter": "o.amount> 1000",

[0076] "columns": ["u.name", "o.amount"]

[0077] }

[0078] Optionally, the update system uses a large language model to perform semantic analysis on the JSON document, identify key semantic units of the SQL statements, and then generates a SQL semantic vector representation based on the semantic understanding results. A vector generation model then converts the semantic analysis results into a 128-dimensional vector as the vector representation of the SQL semantics. This process is illustrated by the following formula:

[0079] ;

[0080] in, Let d be the semantic encoding function, and d be the vector dimension. The input SQL is: SELECT u.name,o.amount FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount> 1000. The output semantic features are: [0.12, 0.05, -0.33, ... , 0.41].

[0081] Optionally, the update system parses the text of slow query statements to obtain an abstract syntax tree of the slow query statements, transforming unstructured text into structured syntax objects, thereby achieving precise location and decomposition of the components of slow SQL statements.

[0082] Optionally, relying solely on the abstract syntax tree (AST) fails to reflect the actual execution bottleneck of slow query statements (such as whether an index was used or a full table scan occurred), while relying solely on the execution plan lacks semantic structure information. The update system generates a logical plan tree for slow query statements based on the AST and the execution plan. The AST provides the logical intent, and the execution plan provides the runtime context information. The logical plan tree constructed in this way not only preserves the semantic structure of the SQL but also embeds the real execution constraints, obtaining an intermediate representation that closely reflects the actual performance bottleneck. This provides a more complete and accurate analytical foundation for subsequent feature extraction.

[0083] Optionally, the update system performs feature extraction on the logical plan tree to obtain the logical features of the slow query statement. The logical features directly reflect the complexity and execution bottleneck of the original slow query statement, which solves the defect of traditional technical solutions based on text matching in feature extraction that cannot cope with structural changes.

[0084] In summary, the update system generates an abstract syntax tree by parsing the statement text, thus transforming strings into structured syntax; it generates a logical plan tree by integrating the abstract syntax tree with the execution plan, thus unifying the syntactic intent with the actual execution path; and it extracts logical features to compress complex execution structures into structured attributes that can be understood by the model. This allows the update system to move away from relying on "fuzzy text matching" or "manual rule judgment," and instead drive subsequent optimization based on structured, realistic, and computable logical features, which helps improve the accuracy, relevance, and interpretability of candidate SQL generation.

[0085] In one optional embodiment, the update system first generates L initial statements based on logical features using a large language model, where L is a positive integer greater than or equal to K. Each initial statement is semantically equivalent to a slow query statement. Then, the update system performs syntax verification and result alignment tests on the L initial statements. The syntax verification is used to detect syntax errors in each initial statement, and the result alignment test is used to detect the similarity between the execution results of each initial statement and the slow query statement on a preset sample set. Finally, the update system selects the initial statements that pass the syntax verification and result alignment tests from the L initial statements as candidate statements, resulting in K candidate statements.

[0086] Optionally, the update system generates L initial statements based on the large language model, resulting in an initial statement set. Then, the initial statement is subjected to syntax verification and result alignment test. That is, the semantic correctness of the generated initial statement is checked by using a semantic verification rule base, and the initial statement with grammatical errors is filtered out by using a predefined rule base. Then, after semantic verification, the result alignment test is performed by executing a small sample dataset to compare whether the execution results of the initial statement and the original slow query statement are consistent.

[0087] Alternatively, traditional optimization methods rely on fixed rule templates (such as "change DISTINCT to GROUP BY"), which limits their generation capabilities. Update systems, through large language models, generate diverse and non-template-based rewriting schemes based on structured logical features, breaking through the coverage of manual rules. For example, large language models can generate rewriting methods such as "use window functions to take the latest record of each group to replace DISTINCT", which is usually difficult to exhaustively enumerate in manual rule bases, thus helping to improve the breadth and freedom of generative optimization.

[0088] Optionally, large language models may generate initial statements with grammatical errors or semantic drift. Syntax validation can immediately eliminate structurally invalid statements, preventing subsequent invalid execution. Result alignment testing helps ensure logical correctness; even if the syntax is correct, if the results are inconsistent, the statement is not a truly equivalent rewrite. For example, the model might mistakenly write COUNT(DISTINCTuser_id) as COUNT( The initial statement is grammatically correct but semantically incorrect. The update system uses a dual filtering mechanism to select the initial statement that passes the syntax check and result alignment test from the L initial statements as candidate statements. This ensures the reliability of the generated candidate statements and provides a high-quality and reliable input set for subsequent performance evaluation.

[0089] In summary, the update system generates diverse initial statements based on logical features using a large language model, overcoming the limitations of rules. Through syntax verification and result alignment testing, it double-filters illegal and semantically offset statements, which helps improve security. By retaining only statements that pass the verification as candidate statements, it achieves high-quality, executable, and evaluable candidate set output.

[0090] In one optional embodiment, the update system first determines the index value of each candidate statement in P performance index dimensions based on the performance index data of each candidate statement, where P is a positive integer, and the P performance index dimensions include at least execution time, read / write count, processor utilization, and memory consumption. Then, the update system generates initial index features for each candidate statement based on the index values ​​of each candidate statement in the P performance index dimensions. Next, the update system normalizes the initial index features of each candidate statement to obtain standard index features for each candidate statement. Subsequently, the update system performs spatial mapping on the standard index features of each candidate statement to obtain the performance index features of each candidate statement, wherein the spatial mapping is used to unify the vector dimensions of the initial index features and semantic features.

[0091] Optionally, the update system applies candidate statements to the database for simulated execution. Using database explain (execution plan analysis) technology, while batch-executing each candidate SQL, it collects performance metrics across P dimensions, including execution time, I / O, CPU, and memory consumption. Then, based on the collected performance metrics across these P dimensions, it constructs initial metric features for each candidate statement. ,in, Indicates the execution time. Indicates the number of I / O operations. Indicates CPU usage. This indicates memory consumption.

[0092] Optionally, to ensure that indicators from different dimensions can be compared on the same scale, the update system normalizes the initial indicator features to obtain standard indicator features for each candidate statement. For example, the Z-score (standard score) standardization method can be used to process various performance indicators. The Z-score standardization method is shown in the following formula:

[0093] ;

[0094] in, Represented as the original value, This is represented as the mean of the feature. This is expressed as the standard deviation of the feature. Represented as the standardized value.

[0095] For example, executing each candidate SQL query yields P performance metrics across various dimensions, as shown in Table 1 below:

[0096] Table 1

[0097]

[0098] Normalization example:

[0099] t: (500-μ) / σ=1.2;

[0100] io: (200-μ) / σ=0.8.

[0101] Optionally, to integrate semantic and performance features across domains, the update system maps the standard indicator features of the obtained candidate sentences to the same 128 dimensions through a mapping function, unifying them into the same vector space to facilitate similarity calculation. The mapping function is shown in the following formula:

[0102] ;

[0103] in, As a standard indicator feature, The performance metrics characteristics obtained from the mapping.

[0104] Optionally, related technologies often rely solely on "expected costs" or a single metric (such as execution time) to evaluate SQL, which cannot fully reflect resource consumption. The update system avoids misjudgments caused by one-sided evaluation by explicitly collecting four key real performance indicators, covering four major resource dimensions: time, I / O, CPU, and memory.

[0105] Optionally, the update system generates initial indicator features for each candidate statement based on the indicator values ​​of each candidate statement across P performance indicator dimensions, thereby structuring the dispersed performance data into a unified numerical vector, providing a data foundation for subsequent normalization and spatial mapping operations.

[0106] Optionally, the units and numerical ranges of the original performance metrics vary greatly: execution time (milliseconds) and memory consumption (MB) are on different scales, and there is no unified scale for CPU utilization (0–100) and I / O counts (thousands). If directly input into the model, metrics with large dimensions will dominate the similarity calculation results, causing the system to ignore small but crucial metrics. The update system normalizes the initial metric features of each candidate statement to eliminate the influence of dimensions and avoid recommendation bias caused by data scale issues.

[0107] Optionally, semantic features are output by a large language model as 128-dimensional dense vectors, expressing the business intent of the SQL; while the original performance metric features are only 4-dimensional. The two dimensions do not match, making it impossible to directly calculate similarity or construct comparison samples. Through a spatial mapping mechanism, the update system compresses the low-dimensional performance information and projects it into the same space as the semantic features, making the two comparable. This mapping does not rely on manual rules, but is automatically optimized through comparative learning, achieving semantic alignment between the semantic dimension and the performance dimension, enabling subsequent optimization judgments to have cross-modal correlation capabilities.

[0108] In summary, the update system achieves multi-dimensional performance evaluation by collecting real P-dimensional performance data; it constructs initial indicator features from the raw performance data, completing the structured expression of performance indicator values; it eliminates dimensional interference through normalization, enabling fair comparison of data across dimensions; and it unifies the dimensions of performance features and semantic features through spatial mapping, achieving cross-modal alignment. Through these steps, it transforms "raw monitoring data" into "high-performance vectors that can be jointly modeled with semantics," solving the shortcomings of traditional methods such as "performance indicators cannot be associated with semantics" and "multi-dimensional data cannot be uniformly evaluated." This allows the update system to make accurate and interpretable optimization recommendations based on the joint features of semantic intent and real performance, providing comparative learning models with dimensionally consistent, dimensionally unified, and informationally complete input.

[0109] In one optional embodiment, the update system first determines target comparison sample pairs based on K comparison sample pairs using a target model. The target comparison sample pair is the comparison sample pair to which the performance indicator feature with the lowest performance overhead belongs among the K comparison sample pairs. The target model is a comparison discriminator trained based on update cases of Q historical slow query statements in a preset experience base, where Q is a positive integer. Then, the update system takes the candidate statements corresponding to the performance indicator features in the target comparison sample pairs as the target statements and uses the target statements as the update strategy for slow query statements.

[0110] Optionally, traditional optimization models directly select candidate statements with better performance as recommended statements based solely on weighted scores corresponding to performance metrics such as execution time. The update system introduces a target model that determines target comparison sample pairs based on K comparison sample pairs. This model is not a simple sorter, but rather learns from the update cases of Q historical slow query statements, gaining historical experience on "what kind of performance is suitable for the target statement in the current business scenario under a certain semantic context." Therefore, the target model can transcend the judgment of a single performance metric, comprehensively integrate semantic context information and historical experience, and identify candidate statements that are "better in the current business scenario," which helps improve the accuracy and scenario adaptability of the recommended target statements.

[0111] Optionally, the update system takes the candidate statements corresponding to the performance index features in the target comparison sample pair as the target statements, uses the target statements as the update strategy for slow query statements, and directly guides the DBA to modify the original slow SQL statements based on the target statements, forming a closed loop of "system suggestion - manual confirmation - feedback feedback". This ensures that each optimization record of a slow SQL statement can become a training sample for the target model, driving the continuous updating of the target model.

[0112] In one optional embodiment, the update system first parses the update cases of each historical slow query statement in the preset experience base to obtain K historical candidate statements corresponding to each historical slow query statement. Then, the update system performs a weighted summation of the index values ​​of each historical candidate statement on P performance index dimensions to obtain the performance cost corresponding to each historical candidate statement. The historical candidate statement with the lowest performance cost among the K historical candidate statements is selected as the historical target statement. Subsequently, the update system uses the historical target statement corresponding to each historical slow query statement as the positive sample corresponding to each historical slow query statement, and uses K-1 historical candidate statements other than the historical target statement as the negative sample corresponding to each historical slow query statement. Then, the update system iteratively trains the initial comparison discriminator based on the Q groups of positive samples and Q groups of negative samples corresponding to the Q historical slow query statements to obtain the target model. The initial comparison discriminator is used to determine the mapping relationship between the semantic features of each historical slow query statement and the performance index features corresponding to the historical target statement.

[0113] Optionally, the update system trains the contrast discriminator based on update cases of Q historical slow query statements in a preset experience base. The loss function of the contrast discriminator is as follows:

[0114] ;

[0115] in, This represents the semantic characteristics of slow query statements. This indicates the performance characteristics of the target statement. This represents the performance metric characteristic of the j-th candidate statement among K candidate statements. By representing similarity metrics (such as cosine similarity or inner product), the update system optimizes the SQL semantic representation and SQL statement execution performance by minimizing the loss function.

[0116] Optionally, the update system parses the update cases of each historical slow query statement in the preset experience base to obtain K historical candidate statements corresponding to each historical slow query statement, thereby transforming unstructured SQL text and performance data into computable semantic features and performance indicator features, so that the experience data has a machine-learnable format.

[0117] Optionally, the update system performs a weighted summation of the metric values ​​of each historical candidate statement across P performance metrics dimensions. The weights are set based on business experience (e.g., I / O costs are higher than CPU costs) rather than simply taking the minimum execution time, thus avoiding biased judgment. Through weighted summation, the system can identify candidate statements that truly balance resource consumption, making the "positive samples" in the training samples more representative.

[0118] Optionally, the update system uses the historical target statement corresponding to each historical slow query statement as the positive sample corresponding to each historical slow query statement, and uses the K-1 historical candidate statements other than the historical target statement as the negative sample corresponding to each historical slow query statement. This method of constructing training samples does not require manual labeling. Based on the performance overhead ranking automatically generated by the system, it achieves low-cost, high-scale, and high-quality supervision signal generation, providing sufficient and effective learning signals for model training.

[0119] Optionally, the update system iteratively trains the initial comparison discriminator based on Q groups of positive samples and Q groups of negative samples corresponding to Q historical slow query statements, which improves the generalization ability of the trained target model. Even when encountering new slow SQL statements that have never been seen before, it can determine which one performs better based on its semantic features, thus achieving end-to-end semantic-performance correlation modeling.

[0120] In summary, the above steps achieve a complete transformation from historical data to the target model, enabling the update system to no longer rely on manual experience or rule bases, but instead automatically extract enterprise-level SQL optimization knowledge through a data-driven approach, forming a reusable, scalable, and evolvable target model.

[0121] In one optional embodiment, after taking the candidate statements corresponding to the performance index features in the target comparison sample pair as the target statement, the update system first takes the slow query statement, K candidate statements, the target statement, and the update feedback results of the slow query statement as update cases for the slow query statement. Then, the update system stores the update cases of the slow query statement in a preset experience base. Subsequently, the update system updates the target model based on the new update cases in the preset experience base.

[0122] Optionally, the update system displays the update strategy through a user interface and the optimization results and related analysis data through a desktop application for DBA review. Afterward, the update system receives the user's confirmation or rejection of the update and records the user's response to the suggestion through the confirmation button provided on the interface.

[0123] It is important to note that user feedback may contain some noise. For example, a DBA's rejection of an optimization suggestion does not necessarily mean that the model is wrong. It may also be due to non-performance factors such as business needs, resource constraints, or operational strategies. Therefore, the update system adopts a weighted feedback mechanism to reduce the sensitivity to a single negative feedback when updating the target model, and to avoid the model overfitting to the subjective choices of individual DBAs.

[0124] Optionally, the update system uses the slow query statement, K candidate statements, the target statement, and the update feedback results of the slow query statement as update cases for the slow query statement. This means that a complete optimization process is structured into reusable data units. Then, the update cases of the slow query statement are stored in a preset experience base. This design enables the system to accumulate knowledge. As the running time increases, the size of the preset experience base continues to expand, thereby providing richer samples for the training of the target model and gradually covering more SQL patterns and business scenarios. Then, the update system performs lightweight retraining on the target model based on the new update cases in the preset experience base, adjusting the model parameters to adapt to the new case data distribution, thereby continuously improving the recommendation accuracy of the model. Through the above steps, the system moves away from the traditional mode of "one-time deployment and static rules" and transforms into an intelligent optimization system that continuously learns and dynamically adapts.

[0125] In one alternative embodiment, Figure 2 This is a flowchart of an optional method for optimizing slow SQL statements based on a contrastive learning model, according to an embodiment of this application. Figure 2 As shown in the flowchart, this process describes the complete execution flow of slow SQL optimization. The specific steps are as follows:

[0126] After receiving a slow SQL optimization request, the system first checks if an optimization task for the same slow SQL statement exists in the task scheduling database. If it does, the DBA is notified of the current task status; otherwise, a new task is created and a unique task identifier is assigned. Next, the slow SQL statement and its corresponding execution plan are collected from the database monitoring database. Then, the SQL parsing engine is used to parse the slow SQL statement and execution plan, constructing a logical plan tree and extracting logical features from it. Simultaneously, semantic vectors (i.e., semantic features) are generated based on these logical features. Finally, a large language model (i.e.,...) is used... Figure 2 The AI ​​model in the model generates candidate SQL based on logical features.

[0127] Then, semantic verification and rule filtering are performed on each candidate SQL. The verified and filtered candidate SQL is executed, and performance indicators are collected. Based on the collected performance indicator data, a performance indicator vector (i.e., performance indicator features) is constructed. Subsequently, the semantic vector of the original SQL and the performance indicator features of each candidate statement are combined to form a comparison sample pair, which is input into the comparison learning model. The degree of matching between each candidate and the original SQL in terms of performance and semantics is calculated through the loss function, and the candidate statement with the best performance (i.e., the target statement) is selected.

[0128] Afterwards, the system returns the target statement as an optimization suggestion to the user and stores the "original SQL - target statement - user feedback" as a triple in the experience database. The system receives feedback from the user on whether to accept or reject the suggestion, and fine-tunes the contrastive learning model based on the user feedback to update its judgment ability. The entire process runs in a closed loop. After each round of tasks is completed, the system automatically optimizes subsequent judgments based on the newly added experience.

[0129] In one alternative embodiment, Figure 3 This is an architecture diagram of an optional system for optimizing slow SQL statements based on a contrastive learning model, according to an embodiment of this application. Figure 3 As shown, the optimization system includes: a data acquisition layer, a parsing and feature layer, a semantic understanding layer, a candidate generation and verification layer, an execution and indicator acquisition layer, a learning and experience base layer, and a service and interaction layer.

[0130] Optionally, the data acquisition layer obtains slow SQL statements and execution plans from the database monitoring system, constructs a logical plan tree based on the slow SQL statements and execution plans through the SQL parsing engine in the parsing and feature layer, extracts features from the logical plan tree through the feature extractor to obtain features such as the join pattern, predicates and fields of the slow SQL statements, and performs serialization processing on the extracted features through the JSON serialization processor to obtain metadata in JSON format.

[0131] Optionally, after receiving the serialized slow SQL statement features (i.e., logical features), the semantic understanding layer inputs them as semantic units into the semantic editor of the large language model to obtain a high-dimensional SQL semantic vector (i.e., semantic features) that describes the semantic intent of the slow SQL statement.

[0132] Optionally, after receiving the metadata in JSON format, the candidate generation and validation layer calls the generation model to generate multiple candidate SQL statements that are semantically equivalent to the original slow SQL statements. Then, it performs semantic validation and rule filtering operations through the semantic validation rules in the semantic validation rule base. Afterward, it performs result alignment tests on the filtered candidate statements in the sandbox environment based on a small sample dataset, filters out candidate statements with syntax errors or semantic inconsistencies, and retains K valid candidate statements.

[0133] Optionally, the execution and metric collection layer performs performance metric tests on the K candidate statements through the execution engine, and collects raw performance metric data such as execution time, read / write count, CPU utilization, and memory consumption for each candidate statement through the EXPLAIN collector to form an initial performance metric vector, which is then normalized and mapped to performance metric features of the same dimension as the semantic vector.

[0134] Optionally, the learning and experience base layer pairs the semantic vector of the original slow SQL with the performance indicator features of each candidate statement to form a comparison sample pair, which is then input into the comparison learning model. The comparison learning model is trained to learn the correlation between the semantic vector and the performance of the candidate statements. Furthermore, the sample pairs used for training the comparison learning model are stored in a preset experience base.

[0135] Optionally, the recommendation service module in the service and communication layer is used to receive the candidate SQL list and the identifier of the optimal candidate statement (i.e., the target statement) output by the contrastive learning model. The contrastive learning model selects the candidate statement with the lowest performance overhead as the optimal candidate statement. Then, the recommendation service module displays the recommendation results to the user through the user interface and receives feedback results from the DBA user on whether to accept or reject the recommendation. After that, the user feedback results are transmitted to the preset experience base. The preset experience base stores the original slow SQL statement, candidate statement, target statement and user feedback results corresponding to each slow SQL optimization task as update cases for subsequent updates of the contrastive learning model.

[0136] As described above, this application first generates K candidate statements that are semantically equivalent to the slow query statement based on its logical characteristics. Then, through further semantic analysis of the logical characteristics of the slow query statement, semantic features that can characterize the business objectives of the slow query statement are obtained. Next, this application combines the semantic features of the slow query statement with the performance indicator features of the candidate statements to construct a pair of comparison samples with the semantic features and performance indicator features. By generating an update strategy based on the comparison sample pairs, the purpose of automatically optimizing and rewriting the slow query statement is achieved. This technical solution does not rely on manual rules or static cost models. Through equivalence constraints and performance feedback loops, it realizes the updating of the slow query statement, thereby achieving the effect of adaptive, data-driven performance optimization of the slow query statement. This solves the technical problem of low efficiency and poor effect of performance optimization of slow query statements in the database based on existing technologies.

[0137] Example 2

[0138] This application embodiment can also provide a query statement updating device. It should be noted that the query statement updating device of this application embodiment can be used to execute the query statement updating method provided in this application embodiment. The query statement updating device provided in this application embodiment will be described below.

[0139] According to an embodiment of this application, an apparatus for implementing the above-described query statement update method is also provided. Figure 4 This is a schematic diagram of an optional query statement update device according to an embodiment of this application, such as... Figure 4 As shown, the device includes: a candidate statement generation unit 401, a semantic analysis unit 402, an indicator feature determination unit 403, a sample pair generation unit 404, and a statement update unit 405.

[0140] Optionally, the candidate statement generation unit 401 is used to generate K candidate statements based on the logical features of the slow query statement, where K is a positive integer, the logical features are used to characterize the logical structure of the slow query statement, and the execution result of each candidate statement is consistent with that of the slow query statement on a preset sample set; the semantic analysis unit 402 is used to perform semantic analysis on the logical features to obtain the semantic features of the slow query statement, where the semantic features are used to characterize the business objectives achieved by the slow query statement; the indicator feature determination unit 403 is used to determine the performance indicator features of each candidate statement based on the performance indicator data of each candidate statement; the sample pair generation unit 404 is used to generate K comparison sample pairs corresponding to the K candidate statements based on the semantic features of the slow query statement and the performance indicator features of each candidate statement, where each comparison sample pair includes at least a semantic feature and a performance indicator feature of a candidate statement; and the statement update unit 405 is used to update the slow query statement based on the update strategy generated based on the K comparison sample pairs.

[0141] As described above, this device first generates K candidate statements that are semantically equivalent to the slow query statement based on its logical characteristics. Then, it performs further semantic analysis on the logical characteristics of the slow query statement to obtain semantic features that can characterize the business objectives of the slow query statement. Next, the device combines the semantic features of the slow query statement with the performance indicator features of the candidate statements to construct a pair of comparison samples with the semantic features and performance indicator features. By generating an update strategy based on the comparison sample pairs, the device achieves the goal of automatically optimizing and rewriting the slow query statement. This device does not rely on manual rules or static cost models. Through equivalence constraints and performance feedback loops, it realizes the updating of the slow query statement, thereby achieving the effect of adaptive, data-driven performance optimization technology for slow query statements. This solves the technical problems of low efficiency and poor effect of performance optimization of slow query statements in databases based on existing technologies.

[0142] In an optional embodiment, the query statement updating device further includes: a task detection unit, a task creation unit, and a task execution unit.

[0143] Optionally, the task detection unit is used to detect whether there is an optimization task corresponding to the update request in the task scheduling library after receiving the update request of the slow query statement, before generating K candidate statements based on the logical characteristics of the slow query statement. The optimization task is used to improve the performance indicators of the slow query statement during database operation. The task creation unit is used to create an optimization task corresponding to the update request if there is no optimization task corresponding to the update request in the task scheduling library. The task execution unit is used to collect the execution plan of the slow query statement in the database after creating the optimization task corresponding to the update request.

[0144] In an optional embodiment, the query statement updating device further includes: an abstract syntax tree generation unit, a logical plan tree generation unit, and a logical feature extraction unit.

[0145] Optionally, the abstract syntax tree generation unit is used to parse the statement text of the slow query statement after collecting the execution plan of the slow query statement in the database, and obtain the abstract syntax tree of the slow query statement; the logical plan tree generation unit is used to generate the logical plan tree of the slow query statement based on the abstract syntax tree and the execution plan; the logical feature extraction unit is used to extract features from the logical plan tree to obtain the logical features of the slow query statement, wherein the logical features include at least one of the following: table join order, predicate, grouping, and sorting.

[0146] In an optional embodiment, the candidate statement generation unit 401 includes: an initial statement generation subunit, a verification test subunit, and a candidate statement determination subunit.

[0147] Optionally, the initial statement generation subunit is used to generate L initial statements based on logical features using a large language model, where L is a positive integer greater than or equal to K, and each initial statement is semantically equivalent to a slow query statement; the verification and testing subunit is used to perform syntax verification and result alignment testing on the L initial statements, where syntax verification is used to detect syntax errors in each initial statement, and result alignment testing is used to detect the similarity between the execution results obtained by executing each initial statement and the slow query statement on a preset sample set; the candidate statement determination subunit is used to select the initial statements that pass the syntax verification and result alignment testing from the L initial statements as candidate statements to obtain K candidate statements.

[0148] In an optional embodiment, the indicator feature determination unit 403 includes: an indicator value determination subunit, an initial indicator feature generation subunit, a normalization processing subunit, and a spatial mapping subunit.

[0149] Optionally, the indicator value determination subunit is used to determine the indicator value of each candidate statement in P performance indicator dimensions based on the performance indicator data of each candidate statement, where P is a positive integer, and the P performance indicator dimensions include at least execution time, read / write count, processor utilization, and memory consumption; the initial indicator feature generation subunit is used to generate initial indicator features for each candidate statement based on the indicator values ​​of each candidate statement in P performance indicator dimensions; the normalization processing subunit is used to normalize the initial indicator features of each candidate statement to obtain the standard indicator features of each candidate statement; and the spatial mapping subunit is used to perform spatial mapping on the standard indicator features of each candidate statement to obtain the performance indicator features of each candidate statement, where the spatial mapping is used to unify the vector dimensions of the initial indicator features and semantic features.

[0150] In an optional embodiment, the statement update unit 405 includes a target sample pair determination subunit and a target statement determination subunit.

[0151] Optionally, the target sample pair determination subunit is used to determine the target comparison sample pair based on K comparison sample pairs using the target model. The target comparison sample pair is the comparison sample pair to which the performance indicator feature with the lowest performance overhead belongs among the K comparison sample pairs. The target model is a comparison discriminator trained based on the update cases of Q historical slow query statements in a preset experience base, where Q is a positive integer. The target statement determination subunit is used to take the candidate statements corresponding to the performance indicator features in the target comparison sample pair as the target statements and use the target statements as the update strategy for slow query statements.

[0152] In an optional embodiment, the query statement updating device further includes: a case parsing unit, a weighted summation unit, a sample determination unit, and a training unit.

[0153] Optionally, the case analysis unit is used to analyze the update cases of each historical slow query statement in the preset experience base to obtain K historical candidate statements corresponding to each historical slow query statement; the weighted summation unit is used to perform weighted summation of the index values ​​of each historical candidate statement on P performance index dimensions to obtain the performance cost corresponding to each historical candidate statement, and then select the historical candidate statement with the lowest performance cost among the K historical candidate statements as the historical target statement; the sample determination unit is used to select the historical target statement corresponding to each historical slow query statement as the positive sample corresponding to each historical slow query statement, and select K-1 historical candidate statements other than the historical target statement among the K historical candidate statements as the negative sample corresponding to each historical slow query statement; the training unit is used to iteratively train the initial comparison discriminator based on Q groups of positive samples and Q groups of negative samples corresponding to Q historical slow query statements to obtain the target model, wherein the initial comparison discriminator is used to determine the mapping relationship between the semantic features of each historical slow query statement and the performance index features corresponding to the historical target statement.

[0154] In an optional embodiment, the query statement updating device further includes: a case determination unit, a case storage unit, and a model updating unit.

[0155] Optionally, the case determination unit is used to take the slow query statement, K candidate statements, target statement, and update feedback results of the slow query statement as update cases of the slow query statement after taking the candidate statements corresponding to the performance index features in the target comparison sample pair as the target statement; the case storage unit is used to store the update cases of the slow query statement in a preset experience base; and the model update unit is used to update the target model based on the new update cases in the preset experience base.

[0156] It should be noted that the candidate statement generation unit 401, semantic analysis unit 402, indicator feature determination unit 403, sample pair generation unit 404, and statement update unit 405 mentioned above correspond to steps S101 to S105 in the method embodiment. The instances and application scenarios implemented by the above units and corresponding steps are the same, but are not limited to the content disclosed in the above embodiment.

[0157] Example 3

[0158] Embodiments of this application can also provide an electronic device. Figure 5 This is a structural block diagram of an electronic device according to an embodiment of this application, such as... Figure 5 As shown, the electronic device includes: one or more ( Figure 5 (Only one is shown) processor 502, memory 504, memory controller, and peripheral interface, wherein the peripheral interface is connected to the radio frequency module, audio module and display.

[0159] The memory can be used to store software programs and modules, such as the program instructions / modules corresponding to the methods and devices in the embodiments of this application. The processor executes various functional applications and data processing by running the software programs and modules stored in the memory, that is, to implement the above-mentioned query statement update method.

[0160] The memory may include high-speed random access memory (RAM), and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory may further include memory remotely located relative to the processor, which can be connected to the terminal via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks (LANs), mobile communication networks, and combinations thereof.

[0161] The processor can access information and applications stored in memory via a transmission device to perform the following steps: Generate K candidate statements based on the logical features of the slow query statement, where K is a positive integer. The logical features characterize the logical structure of the slow query statement, and each candidate statement's execution result is consistent with the slow query statement's result on a preset sample set. Perform semantic analysis on the logical features to obtain the semantic features of the slow query statement, where the semantic features characterize the business objective achieved by the slow query statement. Determine the performance indicator features of each candidate statement based on its performance indicator data. Generate K comparison sample pairs corresponding to the K candidate statements based on the semantic features of the slow query statement and the performance indicator features of each candidate statement, where each comparison sample pair includes at least a semantic feature and a performance indicator feature of a candidate statement. After generating an update strategy for the slow query statement based on the K comparison sample pairs, update the slow query statement based on the update strategy.

[0162] The processor can access information and applications stored in memory via a transmission device to perform the following steps: Before generating K candidate statements based on the logical characteristics of the slow query statement, after receiving an update request for the slow query statement, it checks whether there is an optimization task corresponding to the update request in the task scheduling library, wherein the optimization task is used to improve the performance indicators of the slow query statement during database operation; if there is no optimization task corresponding to the update request in the task scheduling library, it creates an optimization task corresponding to the update request; after creating the optimization task corresponding to the update request, it collects the execution plan of the slow query statement in the database.

[0163] The processor can invoke information and applications stored in memory via a transmission device to perform the following steps: after acquiring the execution plan of the slow query statement in the database, the statement text of the slow query statement is parsed to obtain the abstract syntax tree of the slow query statement; a logical plan tree of the slow query statement is generated based on the abstract syntax tree and the execution plan; features are extracted from the logical plan tree to obtain the logical features of the slow query statement, wherein the logical features include at least one of the following: table join order, predicate, grouping, and sorting.

[0164] The processor can access information and applications stored in memory via a transmission device to perform the following steps: Generate L initial statements based on logical features using a large language model, where L is a positive integer greater than or equal to K, and each initial statement is semantically equivalent to a slow query statement; perform syntax verification and result alignment tests on the L initial statements, where syntax verification detects syntax errors in each initial statement, and result alignment tests detect the similarity between the execution results of each initial statement and the slow query statement on a preset sample set; and select the initial statements that pass both syntax verification and result alignment tests from the L initial statements as candidate statements to obtain K candidate statements.

[0165] The processor can access information and applications stored in memory via a transmission device to perform the following steps: Based on the performance metric data of each candidate statement, determine the metric values ​​of each candidate statement across P performance metric dimensions, where P is a positive integer, and the P performance metric dimensions include at least execution time, read / write counts, processor utilization, and memory consumption; generate initial metric features for each candidate statement based on the metric values ​​of each candidate statement across the P performance metric dimensions; normalize the initial metric features of each candidate statement to obtain standard metric features for each candidate statement; and perform spatial mapping on the standard metric features of each candidate statement to obtain the performance metric features of each candidate statement, where spatial mapping is used to unify the vector dimensions of the initial metric features and semantic features.

[0166] The processor can access information and applications stored in memory via a transmission device to perform the following steps: First, determine target comparison sample pairs based on K comparison sample pairs using a target model. The target comparison sample pair is the pair of comparison sample pairs to which the performance indicator feature with the lowest performance overhead belongs. The target model is a discriminator trained based on update cases of Q historical slow query statements in a preset experience base, where Q is a positive integer. Then, the candidate statements corresponding to the performance indicator features in the target comparison sample pairs are taken as target statements, and the target statements are used as the update strategy for slow query statements.

[0167] The processor can access information and applications stored in memory via a transmission device to execute the following steps: parse update cases for each historical slow query statement in a preset experience base to obtain K historical candidate statements corresponding to each historical slow query statement; after weighted summation of the performance index values ​​of each historical candidate statement across P performance index dimensions to obtain the performance overhead of each historical candidate statement, select the historical candidate statement with the lowest performance overhead among the K historical candidate statements as the historical target statement; use the historical target statement corresponding to each historical slow query statement as the positive sample corresponding to each historical slow query statement, and use the K-1 historical candidate statements other than the historical target statement among the K historical candidate statements as the negative samples corresponding to each historical slow query statement; iteratively train the initial contrast discriminator based on the Q groups of positive samples and Q groups of negative samples corresponding to the Q historical slow query statements to obtain the target model, wherein the initial contrast discriminator is used to determine the mapping relationship between the semantic features of each historical slow query statement and the performance index features corresponding to the historical target statement.

[0168] The processor can call the information and application stored in the memory through the transmission device to perform the following steps: after taking the candidate statements corresponding to the performance index features in the target comparison sample pair as the target statement, the slow query statement, K candidate statements, the target statement, and the update feedback results of the slow query statement are taken as update cases of the slow query statement; the update cases of the slow query statement are stored in the preset experience base; the target model is updated based on the new update cases in the preset experience base.

[0169] This application provides a scheme for updating query statements. First, based on the logical characteristics of slow query statements, it generates K candidate statements semantically equivalent to the slow query statements. Then, through further semantic analysis of the logical characteristics of the slow query statements, it obtains semantic features that characterize the business objectives of the slow query statements. Next, it combines the semantic features of the slow query statements with the performance indicator features of the candidate statements to construct comparison sample pairs. By generating an update strategy based on these comparison sample pairs, it achieves the goal of automatically optimizing and rewriting slow query statements. This technical solution does not rely on manual rules or static cost models. Through equivalence constraints and a performance feedback loop, it realizes the updating of slow query statements, thereby achieving adaptive, data-driven performance optimization of slow query statements. This solves the technical problem of low efficiency and poor performance in optimizing slow query statements in databases using existing technologies.

[0170] Those skilled in the art will understand that Figure 5 The structure shown is for illustrative purposes only. Electronic devices can also be smartphones, tablets, PDAs, mobile internet devices, PADs, and other terminal devices. Figure 5 This does not limit the structure of the aforementioned electronic device. For example, electronic devices may also include components that are more... Figure 5 The more or fewer components shown (such as network interfaces, display devices, etc.), or having the same Figure 5 The different configurations shown.

[0171] Those skilled in the art will understand that all or part of the steps in the various methods of the above embodiments can be implemented by a program instructing the hardware related to the terminal device. The program can be stored in a computer-readable storage medium, which may include: flash drive, read-only memory (ROM), random access memory (RAM), disk or optical disk, etc.

[0172] Example 4

[0173] Embodiments of this application may also provide a storage medium.

[0174] Optionally, in this embodiment of the application, the storage medium can be used to store the program code executed by the query statement update method provided in the above method embodiment.

[0175] Optionally, in this embodiment, the storage medium may be located in any computer terminal in a group of computer terminals in a computer network, or in any mobile terminal in a group of mobile terminals.

[0176] This application also provides a computer program product, which, when executed on a data processing device, is adapted to perform update method steps of a query statement.

[0177] The sequence numbers of the embodiments in this application are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0178] In the above embodiments of this application, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0179] In the several embodiments provided in this application, it should be understood that the disclosed technical content can be implemented in other ways. The device embodiments described above are merely illustrative; for example, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the displayed or discussed mutual coupling, direct coupling, or communication connection may be through some interfaces; the indirect coupling or communication connection between units or modules may be electrical or other forms.

[0180] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0181] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0182] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part 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 application. The aforementioned storage medium includes various media capable of storing program code, such as a USB flash drive, read-only memory (ROM), random access memory (RAM), portable hard drive, magnetic disk, or optical disk.

[0183] The above description is only a preferred embodiment of this application. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of this application, and these improvements and modifications should also be considered within the scope of protection of this application.

Claims

1. A method for updating a query statement, characterized in that, include: K candidate statements are generated based on the logical features of the slow query statement, where K is a positive integer. The logical features are used to characterize the logical structure of the slow query statement. Each candidate statement is consistent with the execution result of the slow query statement on a preset sample set. Semantic analysis is performed on the logical features to obtain the semantic features of the slow query statement, wherein the semantic features are used to characterize the business objective achieved by the slow query statement; The performance index characteristics of each candidate statement are determined based on the performance index data of each candidate statement. Based on the semantic features of the slow query statement and the performance index features of each candidate statement, K comparison sample pairs are generated for K candidate statements, wherein each comparison sample pair includes at least the semantic features and the performance index features of a candidate statement. After generating an update strategy for the slow query statement based on the K comparison sample pairs, the slow query statement is updated based on the update strategy.

2. The query statement update method according to claim 1, characterized in that, Before generating K candidate statements based on the logical characteristics of slow query statements, the query statement update method further includes: After receiving the update request of the slow query statement, it checks whether there is an optimization task corresponding to the update request in the task scheduling library. The optimization task is used to improve the performance indicators of the slow query statement during the database operation. If no optimized task corresponding to the update request exists in the task scheduling library, an optimized task corresponding to the update request is created. After creating the optimization task corresponding to the update request, the execution plan of the slow query statement in the database is collected.

3. The query statement update method according to claim 2, characterized in that, After collecting the execution plan of the slow query statement in the database, the method for updating the query statement further includes: The text of the slow query statement is parsed to obtain the abstract syntax tree of the slow query statement; A logical plan tree for the slow query statement is generated based on the abstract syntax tree and the execution plan; Feature extraction is performed on the logical plan tree to obtain the logical features of the slow query statement, wherein the logical features include at least one of the following: Table join order, predicate, grouping, sorting.

4. The query statement update method according to claim 1, characterized in that, K candidate statements are generated based on the logical characteristics of slow query statements, including: L initial statements are generated based on the logical features using a large language model, where L is a positive integer greater than or equal to K, and each initial statement is semantically equivalent to the slow query statement. The L initial statements are subjected to syntax verification and result alignment test, wherein the syntax verification is used to detect syntax errors in each initial statement, and the result alignment test is used to detect the similarity between each initial statement and the execution result obtained by the slow query statement on the preset sample set; The initial statements that pass the syntax check and the result alignment test among the L initial statements are selected as candidate statements to obtain the K candidate statements.

5. The query statement update method according to claim 1, characterized in that, Based on the performance metric data of each candidate statement, the performance metric characteristics of each candidate statement are determined, including: Based on the performance index data of each candidate statement, determine the index value of each candidate statement in P performance index dimensions, where P is a positive integer, and the P performance index dimensions include at least execution time, read and write count, processor utilization, and memory consumption. The initial indicator features of each candidate statement are generated based on the indicator values ​​of each candidate statement in P performance indicator dimensions. The initial index features of each candidate statement are normalized to obtain the standard index features of each candidate statement. The standard indicator features of each candidate statement are spatially mapped to obtain the performance indicator features of each candidate statement, wherein the spatial mapping is used to unify the vector dimension of the initial indicator features and the semantic features.

6. The query statement update method according to claim 1, characterized in that, The update strategy for generating the slow query statement based on the K comparison samples includes: The target comparison sample pair is determined by the target model based on the K comparison sample pairs, wherein the target comparison sample pair is the comparison sample pair to which the performance index feature with the lowest performance overhead belongs among the K comparison sample pairs, and the target model is a comparison discriminator trained based on the update cases of Q historical slow query statements in the preset experience base, where Q is a positive integer; The candidate statements corresponding to the performance index features in the target comparison sample pair are taken as the target statements, and the target statements are used as the update strategy for the slow query statements.

7. The query statement update method according to claim 6, characterized in that, The training steps for the target model include: The update cases of each historical slow query statement in the preset experience base are analyzed to obtain K historical candidate statements corresponding to each historical slow query statement; After weighted summation of the index values ​​of each historical candidate statement across P performance index dimensions to obtain the performance overhead of each historical candidate statement, the historical candidate statement with the lowest performance overhead among the K historical candidate statements is selected as the historical target statement. The historical target statement corresponding to each historical slow query statement is taken as the positive sample corresponding to each historical slow query statement, and the K-1 historical candidate statements other than the historical target statement are taken as the negative samples corresponding to each historical slow query statement. The initial comparison discriminator is iteratively trained based on the Q groups of positive samples and Q groups of negative samples corresponding to the Q historical slow query statements to obtain the target model. The initial comparison discriminator is used to determine the mapping relationship between the semantic features of each historical slow query statement and the performance index features corresponding to the historical target statement.

8. The query statement update method according to claim 6, characterized in that, After selecting the candidate statements corresponding to the performance metric features in the target comparison sample pair as the target statement, the query statement update method further includes: The slow query statement, the K candidate statements, the target statement, and the update feedback result of the slow query statement are used as update cases for the slow query statement. The update cases of the slow query statements are stored in the preset experience base; The target model is updated based on new updated cases in the preset experience base.

9. A query statement update apparatus, characterized in that, include: The candidate statement generation unit is used to generate K candidate statements based on the logical features of the slow query statement, where K is a positive integer. The logical features are used to characterize the logical structure of the slow query statement, and each candidate statement is consistent with the execution result of the slow query statement on a preset sample set. A semantic analysis unit is used to perform semantic analysis on the logical features to obtain the semantic features of the slow query statement, wherein the semantic features are used to characterize the business objective achieved by the slow query statement; The indicator feature determination unit is used to determine the performance indicator features of each candidate statement based on the performance indicator data of each candidate statement. The sample pair generation unit is used to generate K comparison sample pairs corresponding to K candidate statements based on the semantic features of the slow query statement and the performance index features of each candidate statement, wherein each comparison sample pair includes at least the semantic features and the performance index features of a candidate statement. The statement update unit is used to update the slow query statement based on the update strategy after generating the update strategy for the slow query statement based on the K comparison sample pairs.

10. An electronic device, characterized in that, It includes one or more processors and a memory, the memory being used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors cause the one or more processors to implement the query statement update method according to any one of claims 1 to 8.

Images