Query statement optimization method and device, computer equipment, readable storage medium and program product

Abstract

The invention relates to a query statement optimization method and device, computer equipment, a computer readable storage medium and a computer program product, relates to the technical field of test tools and instruments, and can improve the query capability of a system. The method comprises the following steps: determining a query statement aimed at a slow query alarm, and carrying out corresponding query time threshold judgment; if the query statement is a first query statement with the query time smaller than a preset threshold value, analyzing the first query statement through an observation tool to obtain a first optimization scheme, and optimizing the first query statement according to the first optimization scheme; if the query statement is a second query statement of which the query time is greater than a preset threshold value, inputting the second query statement into the query statement optimization model to obtain a second optimization scheme; and associating the second optimization scheme with the query statement category of the second query statement, so as to optimize other query statements according to the second optimization scheme associated with the query statement category when the other query statements in the same category are received.

Description

Technical Field

[0001] The present application relates to the technical field of testing tools and instruments, and in particular to a query statement optimization method, apparatus, computer equipment, computer-readable storage medium, and computer program product. Background Art

[0002] With the development of information technology and digitalization, the field of test tools and instrumentation technology has entered the era of big data technology. The characteristics of big data technology are that the data types have changed from single to diverse, the data structure has changed from single structure to multiple structures, the data granularity has changed from coarse granularity to fine granularity, and the amount of data has increased exponentially. This technology requires the database system to have higher real-time response capabilities when dealing with complex queries and high-concurrency requests. However, when faced with massive amounts of data, traditional database systems are unable to adapt to modern complex query scenarios due to their limited static optimization capabilities, especially when slow query problems occur frequently. Summary of the invention

[0003] Based on this, it is necessary to provide a query statement optimization method, device, computer equipment, computer readable storage medium and computer program product to address the above technical problems.

[0004] In a first aspect, the present application provides a query statement optimization method, comprising:

[0005] Determine the query statement for which the slow query alarm is issued, and perform a threshold determination on the query time corresponding to the query statement;

[0006] If the query statement is a first query statement whose query time is less than a preset threshold, each of the first query statements is parsed by an observation tool to obtain a first optimization solution corresponding to the first query statement, and the first query statement is optimized according to the first optimization solution;

[0007] If the query statement is a second query statement whose query time is greater than the preset threshold, inputting the second query statement into a query statement optimization model to obtain a second optimization solution corresponding to the second query statement output by the model;

[0008] The second optimization scheme is associated with the query statement category of the second query statement, so that when other query statements under the query statement category are received, the other query statements are optimized according to the second optimization scheme associated with the query statement category.

[0009] In one embodiment, before determining the query statement for which the slow query alarm is issued, the method further includes:

[0010] Setting corresponding query request tags for multiple query requests in the query statement, and determining a query path of the query statement through the query request tags;

[0011] According to the query path, the occurrence condition of the slow query event is obtained;

[0012] The query path is monitored, and if an event satisfying the occurrence condition is detected to occur under the query path, it is determined that a slow query event occurs, and a slow query alarm is issued for the query statement.

[0013] In one of the embodiments, before associating the second optimization solution with the query statement category of the second query statement, the method further includes:

[0014] Obtaining a query alarm window corresponding to the slow query alarm, and determining a plurality of second query statements corresponding to the slow query alarm in the query alarm window;

[0015] Extracting a statement template from the second query statement to obtain a query template corresponding to the second query statement, and classifying the second query statement according to the query template; and

[0016] When the number of the second query statements of the same type is greater than a preset number, an optimization cache is set to cache the second optimization solutions corresponding to the second query statements of the same type.

[0017] In one embodiment, when the number of second query statements of the same type is greater than a preset number, after setting the optimized cache, the method further includes:

[0018] Assigning an identification tag to the query template, and determining an optimized cache location corresponding to a second query statement classified by the query template according to the identification tag;

[0019] The second optimization solution is extracted from the optimization cache location, and the second query statement is optimized.

[0020] In one embodiment, the query optimization model is trained by the following steps, including:

[0021] Obtain a slow query log, perform feature encoding on a query statement corresponding to the slow query log, obtain query statement features, determine a training state of the slow query log and the query statement features, and set an optimization action to be performed on the query statement;

[0022] The slow query log and the query statement features are input into a query statement optimization model to be trained; the query statement optimization model executes the optimization action, determines the training state corresponding to the optimization action, and if under the training state, the query statement optimization model satisfies a preset condition for convergence of a training reward function, outputs a query statement optimization model that has completed training.

[0023] In one embodiment, after inputting the query statement into the query statement optimization model to obtain the second optimization solution output by the model, the method further includes:

[0024] According to the plurality of second optimization schemes output by the query statement optimization model, a plurality of performance indicators characterizing the query efficiency of the query statement are determined, and corresponding preset weights are set for the performance indicators:

[0025] Based on the preset weights, the second optimization schemes are scored, and the second optimization scheme corresponding to the highest scoring result is determined as the target optimization scheme;

[0026] The target optimization solution is matched with the category of the query statement, and the target optimization solution is saved in the optimization cache.

[0027] In a second aspect, the present application also provides a query statement optimization device, comprising:

[0028] The alarm statement query analysis module is used to determine the query statement for which the slow query alarm is issued and to perform a threshold judgment on the query time corresponding to the query statement;

[0029] A first query statement analysis and optimization module, configured to, if the query statement is a first query statement whose query time is less than a preset threshold, parse each of the first query statements through an observation tool to obtain a first optimization solution corresponding to the first query statement, and optimize the first query statement according to the first optimization solution;

[0030] A second query statement optimization model module, for inputting the second query statement into the query statement optimization model if the query statement is a second query statement whose query time is greater than the preset threshold, and obtaining a second optimization solution corresponding to the second query statement output by the model;

[0031] The query statement category association optimization module is used to associate the second optimization scheme with the query statement category of the second query statement, so that when other query statements under the query statement category are received, the other query statements are optimized according to the second optimization scheme associated with the query statement category.

[0032] In a third aspect, the present application further provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and when the processor executes the computer program, the following steps are implemented:

[0033] Determine the query statement for which the slow query alarm is issued, and perform a threshold determination on the query time corresponding to the query statement;

[0034] If the query statement is a first query statement whose query time is less than a preset threshold, each of the first query statements is parsed by an observation tool to obtain a first optimization solution corresponding to the first query statement, and the first query statement is optimized according to the first optimization solution;

[0035] If the query statement is a second query statement whose query time is greater than the preset threshold, inputting the second query statement into a query statement optimization model to obtain a second optimization solution corresponding to the second query statement output by the model;

[0036] The second optimization scheme is associated with the query statement category of the second query statement, so that when other query statements under the query statement category are received, the other query statements are optimized according to the second optimization scheme associated with the query statement category.

[0037] In a fourth aspect, the present application further provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, the following steps are implemented:

[0038] Determine the query statement for which the slow query alarm is issued, and perform a threshold determination on the query time corresponding to the query statement;

[0039] If the query statement is a first query statement whose query time is less than a preset threshold, each of the first query statements is parsed by an observation tool to obtain a first optimization solution corresponding to the first query statement, and the first query statement is optimized according to the first optimization solution;

[0040] If the query statement is a second query statement whose query time is greater than the preset threshold, inputting the second query statement into a query statement optimization model to obtain a second optimization solution corresponding to the second query statement output by the model;

[0041] The second optimization scheme is associated with the query statement category of the second query statement, so that when other query statements under the query statement category are received, the other query statements are optimized according to the second optimization scheme associated with the query statement category.

[0042] In a fifth aspect, the present application further provides a computer program product, including a computer program, which implements the following steps when executed by a processor:

[0043] Determine the query statement for which the slow query alarm is issued, and perform a threshold determination on the query time corresponding to the query statement;

[0044] If the query statement is a first query statement whose query time is less than a preset threshold, each of the first query statements is parsed by an observation tool to obtain a first optimization solution corresponding to the first query statement, and the first query statement is optimized according to the first optimization solution;

[0045] If the query statement is a second query statement whose query time is greater than the preset threshold, inputting the second query statement into a query statement optimization model to obtain a second optimization solution corresponding to the second query statement output by the model;

[0046] The second optimization scheme is associated with the query statement category of the second query statement, so that when other query statements under the query statement category are received, the other query statements are optimized according to the second optimization scheme associated with the query statement category.

[0047] The query statement optimization method, device, computer equipment, computer-readable storage medium and computer program product described above determine the query statement targeted by the slow query alarm and perform a threshold judgment on the query time corresponding to the query statement; if the query statement is a first query statement whose query time is less than a preset threshold, each first query statement is parsed through an observation tool to obtain a first optimization scheme corresponding to the first query statement, and the first query statement is optimized according to the first optimization scheme; if the query statement is a second query statement whose query time is greater than the preset threshold, the second query statement is input into the query statement optimization model to obtain a second optimization scheme corresponding to the second query statement output by the model; the second optimization scheme is associated with the query statement category of the second query statement, so that when other query statements under the query statement category are received, the other query statements are optimized according to the second optimization scheme associated with the query statement category. In the present application, the query time of the query statement is determined by the slow query alarm, and it is graded and judged according to the preset threshold (the first query statement and the second query statement), which can reasonably allocate optimization resources according to the actual situation of query complexity and resource consumption; for the first query statement, the observation tool is used to generate the first optimization plan and perform the optimization, so that the optimized first query statement can directly verify the optimization effect and quickly feedback the improvement of query efficiency. For the second query statement, the query statement optimization model is input to generate the second optimization plan, and the optimization model is used to dynamically analyze the query characteristics, execution path and database operation status, provide accurate optimization suggestions, and establish an optimization association mechanism. For high-frequency or structurally similar queries, the associated optimization plan reduces the overhead of repeated analysis, thereby improving the query efficiency of the query statement. BRIEF DESCRIPTION OF THE DRAWINGS

[0048] In order to more clearly illustrate the technical solutions in the embodiments of the present application or related technologies, the drawings required for use in the embodiments of the present application or related technical descriptions will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without paying creative work.

[0049] Figure 1 A flowchart of a query statement optimization method in one embodiment;

[0050] Figure 2 A flowchart of a query statement optimization method according to another embodiment;

[0051] Figure 3 A schematic diagram of a process for determining a query statement optimization solution in one embodiment;

[0052] Figure 4 A structural block diagram of a query statement optimization method device in one embodiment;

[0053] Figure 5 FIG. 4 is a diagram showing the internal structure of a computer device in one embodiment. DETAILED DESCRIPTION

[0054] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0055] In one embodiment, Figure 1 As shown, a query statement optimization method is provided. This embodiment uses the method applied to a server as an example for illustration. It can be understood that the method can also be applied to a terminal, and can also be applied to a system including a terminal and a server, and is implemented through the interaction between the terminal and the server. In this embodiment, the method includes the following steps:

[0056] Step S102: determine the query statement for which the slow query alarm is issued, and perform a threshold determination on the query time corresponding to the query statement.

[0057] Among them, the slow query alarm is a warning mechanism triggered by a slow query event during the execution of a statement query. Among them, a slow query refers to a query operation in the database that takes a long time to query. In some embodiments, it may be caused by complex query logic, large data volume, unreasonable index design, or too many multi-table association operations.

[0058] Query time is the total time from when the database receives a query request to when the query is executed and the results are returned, including but not limited to the time for multiple stages such as query parsing, index retrieval, and data transmission.

[0059] Exemplarily, after receiving a slow query alarm, the server will extract the query statement information involved in the alarm, including query time, path, and results. The query log is scanned in real time to collect query statements with long execution time or frequent requests. The query statements with slow queries are preliminarily screened out based on factors such as query frequency, query table size, and query complexity. When a slow query alarm is triggered, the query statement corresponding to the slow query is further determined, and the server performs a threshold judgment on the execution time of each slow query.

[0060] Step S104: if the query statement is a first query statement whose query time is less than a preset threshold, each first query statement is parsed by an observation tool to obtain a first optimization solution corresponding to the first query statement, and the first query statement is optimized according to the first optimization solution.

[0061] Among them, the first query statement refers to those query statements whose query time exceeds the basic slow query alarm threshold of the system but is still lower than the preset optimization threshold. The optimization of the first query statement can be completed by a fast static analysis tool without too much computing resource investment. The observation tool is a tool for parsing query statements (such as the EXPLAIN tool), which can reveal the execution path, index usage and potential optimization space of the query statement by parsing the query execution plan or other auxiliary analysis methods. The optimization plan is a strategy or suggestion for improving the performance of the query statement, which can improve the query efficiency through different means based on the query execution plan and analysis results. The optimization plan includes but is not limited to measures such as creating indexes, rewriting query statements, and modifying database configuration. In this application, in order to distinguish the optimization schemes used after different threshold judgment results, the optimization scheme obtained by parsing the first query statement through the observation tool is determined as the first optimization scheme.

[0062] Exemplarily, the server performs real-time detection on all queries in the database, and the execution time of each query statement is recorded in real time and compared with a preset threshold. If the execution time of the query statement is less than the preset threshold, these query statements will be marked as "first query statements" and further analyzed. The first query statement can be parsed by using the Explain tool to parse its execution plan, which provides insights into the query execution plan, including detailed information about the steps that the database engine will take to retrieve and process the requested data. The execution plan reveals the execution steps of the query statement, including table scans, index usage, sorting operations, etc. Based on this, the server can identify potential performance bottlenecks. After parsing the query execution plan, the server generates one or more first optimization plans based on the identified bottlenecks.

[0063] Optionally, the first optimization solution is automatically applied to the query statement, the optimized query statement is executed again, and the optimization effect is verified by a monitoring tool. If the query time is shortened after the optimization, it proves that the first optimization solution is effective.

[0064] Step S106: If the query statement is a second query statement whose query time is greater than a preset threshold, the second query statement is input into a query statement optimization model to obtain a second optimization solution corresponding to the second query statement output by the model.

[0065] Among them, the second query statement refers to a slow query statement whose execution time exceeds the system preset threshold. Compared with the first query statement, the second query statement has a more complex execution path or a larger performance bottleneck, and occupies more system resources, requiring a more advanced optimization method. The query statement optimization model is an optimization tool built on an intelligent algorithm, which can be combined with deep learning or reinforcement learning technology to generate efficient optimization solutions for complex query statements. The query statement optimization model can be a rule-based static analysis tool or a dynamically adaptive intelligent algorithm, such as a machine learning model or other automatic optimization framework.

[0066] Exemplarily, when a query statement is identified as a second query statement whose query time exceeds a preset threshold of the system, the server inputs the query statement into the query statement optimization model for processing to generate a corresponding optimization solution. The query statement optimization model can output an optimization strategy for query performance bottlenecks based on the characteristics of the input query and the database operation status. The server calls the query statement optimization model to match the characteristics of the query statement with the internal optimization rules of the model, thereby obtaining a more efficient execution solution.

[0067] Optionally, the design of the query optimization model is not limited to a specific algorithm or implementation method, and its core goal is to generate an optimization solution based on the characteristics of the query. In one embodiment, the optimization model can determine the specific source of the query bottleneck based on the structural analysis and execution information of the query, and provide optimization suggestions in combination with the operating parameters of the database environment.

[0068] Step S108 : Associating the second optimization scheme with the query statement category of the second query statement, so that when other query statements under the query statement category are received, the other query statements are optimized according to the second optimization scheme associated with the query statement category.

[0069] Among them, the second optimization scheme is also an optimization scheme, a strategy or suggestion for improving the performance of the query statement, which can improve the query efficiency through different means based on the query execution plan and analysis results. In this application, in order to distinguish the optimization schemes used after different threshold judgment results, the optimization scheme obtained by parsing the second query statement is determined as the second optimization scheme.

[0070] Exemplarily, after generating the optimization scheme for the second query statement, the server further associates the second optimization scheme with the query statement category to which the second query statement belongs. The query statement category is a classified expression of query statement features, and is used to identify common features of query statements in logical structure, execution path, or query target. By establishing an association relationship between the optimization scheme and the query statement category, the server can quickly reuse the generated optimization scheme for other query statements of the same category, thereby avoiding repeated optimization processes.

[0071] The classification of query statements is not limited to a specific method. The server can automatically classify query statements by analyzing the key features of the query statements, such as the basic template of the SQL statement, the tables or indexes involved, the structure of the conditional expression, etc. After the association of the optimization plan is established, the server can directly search for the stored optimization plan and apply it when receiving other query statements under this category, reducing the resource consumption of recalculation.

[0072] In the above-mentioned query statement optimization method, by determining the query statement targeted by the slow query alarm, a threshold judgment is performed on the query time corresponding to the query statement; if the query statement is a first query statement whose query time is less than a preset threshold, each first query statement is parsed through an observation tool to obtain a first optimization scheme corresponding to the first query statement, and the first query statement is optimized according to the first optimization scheme; if the query statement is a second query statement whose query time is greater than the preset threshold, the second query statement is input into the query statement optimization model to obtain a second optimization scheme corresponding to the second query statement output by the model; the second optimization scheme is associated with the query statement category of the second query statement, so that when other query statements under the query statement category are received, the other query statements are optimized according to the second optimization scheme associated with the query statement category. In this embodiment, the execution time of the query statement is determined by the slow query alarm, and it is graded and judged according to the preset threshold (the first query statement and the second query statement), so that the optimization resources can be reasonably allocated according to the actual situation of query complexity and resource consumption; for the first query statement, the observation tool is used to generate the first optimization plan and perform the optimization, so that the optimized first query statement can directly verify the optimization effect and quickly feedback the improvement of query efficiency; for the second query statement, the query statement optimization model is input to generate the second optimization plan, and the optimization model is used to realize dynamic analysis of query characteristics, execution paths and database operation status, provide accurate optimization suggestions, and establish an optimization association mechanism. For high-frequency or structurally similar queries, the associated optimization plan reduces the overhead of repeated analysis, thereby improving the query efficiency of the query statement.

[0073] In an exemplary embodiment, before determining the query statement for which the slow query alarm is directed, it also includes: setting corresponding query request tags for multiple query requests in the query statement, and determining the query path of the query statement through the query request tags; obtaining the occurrence conditions of the slow query event according to the query path; monitoring the query path, and if an event that meets the occurrence conditions is detected under the query path, determining that a slow query event has occurred, and issuing a slow query alarm for the query statement.

[0074] Among them, the slow query alarm is the server's response to monitor query statements and identify potential slow query problems through labeling and pathing mechanisms. The alarm information not only includes the content of the query statement, but also records the detailed information of the query path, such as the service module name, calling method, SQL query time, etc., to provide support for subsequent optimization analysis.

[0075] The query request tag is a unique tag that identifies the query request and is used to track the execution process of the query request in the system. The query path refers to the complete call link of the query request in the database execution, including the service modules involved, the calling function, the database accessed and other information. By monitoring the query path, the system can perceive the risk of slow query events in advance and trigger alarms in time.

[0076] Specifically, the server first sets query request tags for multiple query requests in the query statement. These tags are usually generated based on the TraceID or other unique identifier of the request. Each query request corresponds to an independent tag, which is convenient for quickly tracking specific queries in subsequent analysis. The generation and allocation of tags are completed by call chain monitoring tools (such as Jaeger or OpenTelemetry). The information indicated by the tag includes the basic characteristics of the query, such as service name, file name, function name, database executing SQL, time consuming SQL execution, SQL query statement, and the content returned by the query SQL.

[0077] The server determines the query path of the query statement through the label. The query path consists of the nodes that the query passes through in the system and their interaction information, such as service call stack, database access log, SQL execution plan, etc. The server generates the conditions for the occurrence of slow query events based on the complete information of the query path. These conditions may include: whether the query execution time exceeds the basic alarm threshold (such as 500ms); whether the number of query calls is abnormal (such as more than 20 times in a short period of time); whether the query has a high frequency of access to certain tables or fields.

[0078] The server monitors the query path in real time to detect whether the path meets the set slow query conditions. When the query path meets these conditions, the system will determine that a slow query event has occurred and generate a corresponding slow query alarm.

[0079] In a specific embodiment, the code is connected to Jaeger, the request ID is used as TraceID, a complete call chain is generated, and Grafana is connected. For the connected Jaeger data source, a monitoring panel is configured in Grafana, and alarm thresholds or alarm rules are configured. During the off-peak period of business use, a copy of the online data is made in the test environment for later data analysis and optimization.

[0080] In this embodiment, through the dynamic monitoring method based on query tags and paths, automatic monitoring and early warning of slow queries are achieved, thereby improving the response efficiency of slow query problems.

[0081] In an exemplary embodiment, before associating the second optimization scheme with the query statement category of the second query statement, it also includes: obtaining a query alarm window corresponding to the slow query alarm, and determining multiple second query statements corresponding to the slow query alarm in the query alarm window; extracting statement templates for the second query statements to obtain query templates corresponding to the second query statements, and classifying the second query statements according to the query templates; and, when the number of second query statements of the same type is greater than a preset number, setting an optimization cache to cache second optimization schemes corresponding to second query statements of the same type.

[0082] The query alarm window refers to the range of query execution information captured by the server within a period of time, such as the query range within 10 minutes traced back from the alarm occurrence time. The determination of the query alarm window ensures that the analysis process focuses on queries related to the alarm event and avoids interference from irrelevant information.

[0083] A query template is a standardized query representation that converts a query statement into a template that is easy to classify and compare by extracting structural features (such as query type, number of fields, table name, conditional logic, etc.) from the query statement. In a simple embodiment, the queries SELECT a FROM b WHERE c=1 and SELECT a FROM b WHERE c=2 are extracted into the same template SELECT a FROM b WHERE c=?, where a is a query field and b is a table.

[0084] The optimization cache is a storage mechanism for optimization results of high-frequency query categories in slow queries. For classified query templates, the server stores the associated second optimization solution in the optimization cache.

[0085] Specifically, after detecting a slow query alarm, the server first determines the query alarm window corresponding to the alarm. Within the query alarm window, the server extracts all second query statements associated with the alarm. The second query statement is regarded as the focus of optimization. The server preliminarily screens out candidate statements that require further analysis by parsing its execution path and time-consuming characteristics. The server performs query template extraction on the screened second query statements. According to the generated query templates, the server classifies the second query statements and aggregates and analyzes the query statements with the same query templates. When the number of second query statements of the same type exceeds a preset number, the server sets an optimization cache and stores the classified query templates and their corresponding second optimization solutions in the optimization cache. When a query statement of the same category is detected again, the system directly calls the optimization solution in the cache for processing to avoid repeated execution of the optimization process.

[0086] In this embodiment, by limiting the query alarm window, the waste of resources for full analysis is avoided, and the optimization process is concentrated on the query statement range related to the alarm. By abstracting the second query statement as a template, aggregate analysis is performed, a cache mechanism for quickly identifying common problems and generating optimization solutions is provided, which reduces the cost of repeated optimization of query statements and ensures the continuity of query performance optimization.

[0087] In an exemplary embodiment, when the number of similar second query statements is greater than a preset number, after setting the optimization cache, it also includes: assigning an identification tag to the query template, and determining the optimization cache position corresponding to the second query statement classified by the target template based on the identification tag; extracting the second optimization scheme from the optimization cache position to optimize the second query statement.

[0088] Among them, the identification tag is a tag that identifies the query template characteristics. It can be generated based on the template characteristics (such as the SQL structure, the table names, field names and conditional logic involved) through a hash algorithm or other tag generation mechanism, and is used to quickly locate the optimization cache position and extract the corresponding optimization plan, thereby reducing the computational overhead in the optimization process.

[0089] Specifically, after generating the query template, the server assigns a unique identification tag to each query template. In one embodiment, a hash calculation is performed on the query template "SELECT a FROM b WHERE c=?" to obtain an identification tag corresponding to the query template. The server locates the optimization cache location associated with the target template based on the identification tag. The optimization cache stores optimization solutions for high-frequency query templates, and each cache location is uniquely identified by a tag. After extracting the optimization solution from the optimization cache location, the server directly applies the solution to the second query statement classified by the query template. The server quickly verifies the optimization results to ensure that the optimized query can run normally and achieve the goal of performance improvement.

[0090] In this embodiment, through the label association mechanism, the existing optimization solutions can be efficiently reused to avoid repeated calculations and significantly reduce system resource consumption. At the same time, the direct application of the cache solution reduces the delay in the generation of the optimization solution and provides a higher response speed for frequently occurring queries.

[0091] In an exemplary embodiment, a query statement optimization model is trained through the following steps, including: obtaining a slow query log, and performing feature encoding on the query statement corresponding to the slow query log to obtain query statement features, determining the training status of the slow query log and the query statement features, and setting an optimization action to be performed on the query statement; inputting the slow query log and the query statement features into the query statement optimization model to be trained; the query statement optimization model executes the optimization action, determines the training status corresponding to the optimization action, and outputs the trained query statement optimization model if the query statement optimization model meets the conditions for convergence of a preset training reward function under the training status.

[0092] Among them, the slow query log is a data set that records query statements and their execution performance during database operation. The slow query log can contain query statement content, execution time, CPU usage, I / O and other information, providing basic data for the training of query optimization models and used to analyze query performance issues.

[0093] Query statement features are a multi-dimensional representation of query logic and execution status, which are used for optimization model analysis and processing. The query statement features may include the query's grammatical structure (such as a syntax tree), the tables and fields involved, the usage of indexes, and the current operating status of the system (such as load, cache hit rate, etc.).

[0094] Optimization actions are the specific manifestations of solutions generated by the query optimization model for query problems, such as adding indexes, adjusting query conditions, rewriting query statements, or modifying database configuration.

[0095] Specifically, the server first extracts the data set required for training from the slow query log of the database. The server preprocesses the extracted log data, including cleaning invalid data, standardizing the format, and feature encoding the query statements contained in the log. Feature encoding converts the structure and state of the query into a numerical representation that can be recognized by the model, such as obtaining query statement features through syntax tree encoding, or extracting features through natural language processing technology.

[0096] The server combines query statement features with performance data in the slow query log to generate training samples. Each sample contains the current query features, execution status, and the optimization requirements of the system when executing the query. Based on this, the server provides initial settings for the selection of optimization actions, such as suggesting to create indexes, adjust query order, or rewrite statement logic.

[0097] The server inputs the training samples into the query optimization model to be trained. The model tries to perform different optimization actions under a given state, evaluates their effects, and records the actual impact of the actions on query performance. This process can be done by using contrastive learning to extract positive and negative sample pairs from the experience replay buffer during training. Positive samples are different states of performing the same action, and negative samples are states of performing different actions. The contrastive loss function is used to narrow the distance between positive samples and push the distance between negative samples. This process optimizes the effectiveness of the action through the calculation feedback of the reward function. In multiple training iterations, the model continuously updates its strategy through reinforcement learning, continuously reduces the value of the contrastive loss function, and selects a better optimization action. Optionally, when the query optimization model falls into a local optimum, simulated annealing can be used to jump out of the local optimum. When the training state of the model meets the preset reward function convergence condition, the server outputs the query optimization model that has been trained.

[0098] In this embodiment, through the combination of slow query logs and feature encoding, the optimization model can more accurately locate the performance bottleneck of the query, and in the iterative training of reinforcement learning, continuously use new slow query data to update the optimization strategy, adapt to the dynamic changes of the database, and avoid the inefficiency of manual analysis of slow queries.

[0099] In an exemplary embodiment, after a query statement is input into a query statement optimization model and a second optimization scheme is obtained as output by the model, the method further includes: determining multiple performance indicators characterizing the query efficiency of the query statement based on multiple second optimization schemes output by the query statement optimization model, and setting corresponding preset weights for the performance indicators: scoring the second optimization schemes based on the preset weights, and determining the second optimization scheme corresponding to the highest scoring result as the target optimization scheme; matching the target optimization scheme with the category of the query statement, and saving the target optimization scheme to the optimization cache.

[0100] Among them, performance indicators are used to quantify query efficiency and to evaluate the quality of query optimization solutions, including but not limited to query time, CPU usage, I / O waiting time, multi-table association efficiency, etc. Preset weights are parameters set for the importance of performance indicators, which are used to give different priorities to different indicators during the optimization solution scoring process.

[0101] Specifically, after the server generates multiple second optimization schemes through the query statement optimization model, it starts to perform performance evaluation on the optimization schemes. The server first determines multiple performance indicators for measuring query efficiency, such as query time, CPU occupancy, I / O waiting time, memory consumption, execution plan efficiency, etc. The server sets a corresponding preset weight for each performance indicator. The setting of weights is determined based on system requirements and optimization goals. For example, in high-concurrency scenarios, the server may pay more attention to CPU and I / O utilization, while in scenarios with higher real-time requirements, query time is given priority. The weight setting method can be based on empirical values, or it can be dynamically adjusted to adapt to changes in the operating status of the database.

[0102] Based on the performance indicators and preset weights, the server scores each second optimization solution. The scoring process multiplies each performance indicator by the weight and then performs weighted accumulation to obtain the comprehensive score of the optimization solution. The server selects the optimization solution with the highest comprehensive score as the target optimization solution. The target optimization solution not only performs best in a single optimization, but also takes into account the long-term improvement goal of the overall system performance. Finally, the server associates the target optimization solution with the category of the query statement and stores it in the optimization cache.

[0103] In this embodiment, a plurality of performance indicators are comprehensively considered through a scoring mechanism to ensure that the selected target optimization solution not only optimizes the current query but also maintains the balance of resource utilization and the sustainability of performance on a global scale.

[0104] In order to enable those skilled in the art to better understand the above steps, the embodiment of the present application is illustrated below by using an example, but it should be understood that the embodiment of the present application is not limited to this.

[0105] In an exemplary embodiment, Figure 2 As shown in the figure, the server assigns a query request tag (TraceID) to each query request by calling a chain tracing tool (such as Jaeger). The query request tag uniquely identifies the execution process of the query statement, including information such as the service name, function name, and target database. The server generates a query path through these tags and captures performance data such as query duration and execution times in the path. Configure the monitoring panel in Grafana, connect to the Jaeger data source, and configure the alarm threshold and alarm rules. If it is detected that the execution time in the query path exceeds the set slow query alarm threshold (such as 500 milliseconds) and the number of calls meets the alarm condition (such as more than 20 times), the slow query alarm is triggered and related information is recorded, including the query statement, query time, and path details.

[0106] The server uses query templates and query paths to determine whether the queries in the window belong to the same type of queries. If the query statements have the same syntax structure, number of fields, and table association mode, they are clustered as similar queries. If they belong to the same type of queries, the server performs cluster analysis and extracts information, including the number of query statement calls, time consumption distribution, and other features, to provide data support for subsequent batch optimization.

[0107] The server further analyzes the query time of the query statements involved in the slow query alarm and divides the query statements into two categories: the first query statements and the second query statements. Figure 3 As shown in the figure, after the Redis database parses the query path, the server uses the observation tool (EXPLAIN) to parse the execution plan of the first query statement with a slow query time lower than the preset threshold (2000ms) to quickly identify performance bottlenecks. Based on the parsing results, the server generates. After the optimization is completed, the server immediately verifies the optimization effect to ensure query performance improvement. For complex queries with slow query time exceeding the preset threshold (2000ms), the server inputs these queries into the query statement optimization model for intelligent analysis and generates the second optimization plan. During the off-peak period of business use, a copy of the test environment (the first optimization plan or the second optimization plan) is made for online data for later data analysis and optimization.

[0108] After receiving the second query statement, the query statement optimization model first extracts its features, including syntax structure, query conditions, fields involved in the table, and index status. Combined with the current operation status of the database, the model analyzes query performance bottlenecks and evaluates optimization potential. The query statement optimization model generates multiple second optimization solutions from historical query optimization experience based on the Deep Q-Network (DQN) algorithm or other intelligent algorithms. The server performs performance evaluation based on these optimization solutions to ensure that the solutions are optimal in terms of query time reduction and system resource usage.

[0109] The server comprehensively scores the multiple second optimization schemes output by the model based on performance indicators. Performance indicators include query time, CPU usage, I / O waiting time, memory usage, etc. The server sets preset weights for these indicators. Through weighted calculation, the server selects the optimization scheme with the highest score as the target optimization scheme. The target optimization scheme takes into account both performance improvement and resource consumption balance to ensure minimal impact on the system as a whole. For the target optimization scheme, the target optimization scheme is adjusted in combination with the query path determined by Jaeger to reduce the unnecessary computing loss caused by the query order. In some embodiments, the optimization model may fall into a local optimal solution. The server introduces a simulated annealing algorithm to try random perturbations during the optimization process to explore a better optimization path.

[0110] The server associates the target optimization solution with the query category to which the second query statement belongs. The server binds the query category with the optimization solution through the identification tag and stores it in the optimization cache. When receiving similar queries in the future, the server directly extracts the corresponding solution from the optimization cache and quickly applies it, avoiding the resource consumption of repeatedly generating optimization solutions.

[0111] After the optimization is completed, the server records the performance changes before and after the optimization, and uses this data as new training samples for continuous learning and updating of the query statement optimization model.

[0112] In this embodiment, the analysis and optimization of data query statements are triggered by alarms. Backup in the spare time of the test environment ensures online stability and data security. At the same time, alarms and analysis and automatic repair after alarms are configured to ensure the timeliness of solving online slow query problems. Through specific analysis of slow queries in different situations, the problem of long time consumption of a single Explain tool is solved. At the same time, query solution analysis based on multi-angle evaluation such as load, time consumption, and combined query is provided, providing a query method with strong timeliness and good optimization effect.

[0113] It should be understood that, although the various steps in the flowcharts involved in the above-mentioned embodiments are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence according to the order indicated by the arrows. Unless there is a clear explanation in this article, the execution of these steps does not have a strict order restriction, and these steps can be executed in other orders. Moreover, at least a part of the steps in the flowcharts involved in the above-mentioned embodiments can include multiple steps or multiple stages, and these steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these steps or stages is not necessarily to be carried out in sequence, but can be executed in turn or alternately with other steps or at least a part of the steps or stages in other steps.

[0114] Based on the same inventive concept, the embodiment of the present application also provides a query statement optimization device for implementing the query statement optimization method involved above. The implementation scheme for solving the problem provided by the device is similar to the implementation scheme recorded in the above method, so the specific limitations in the one or more query statement optimization device embodiments provided below can refer to the limitations of the query statement optimization method above, and will not be repeated here.

[0115] In an exemplary embodiment, Figure 4 As shown, a query statement optimization device is provided, including: an alarm statement query analysis module 410, a first query statement analysis and optimization module 420, a second query statement optimization model module 430 and a query statement category association optimization module 440, wherein:

[0116] The alarm statement query analysis module 410 is used to determine the query statement for which the slow query alarm is issued, and to perform a threshold judgment on the query time corresponding to the query statement;

[0117] A first query statement analysis and optimization module 420 is used to parse each of the first query statements through an observation tool to obtain a first optimization solution corresponding to the first query statement if the query statement is a first query statement whose query time is less than a preset threshold, and optimize the first query statement according to the first optimization solution;

[0118] A second query statement optimization model module 430 is used to input the second query statement into a query statement optimization model if the query statement is a second query statement whose query time is greater than the preset threshold, and obtain a second optimization solution corresponding to the second query statement output by the model;

[0119] The query statement category association optimization module 440 is used to associate the second optimization scheme with the query statement category of the second query statement, so that when other query statements under the query statement category are received, the other query statements are optimized according to the second optimization scheme associated with the query statement category.

[0120] In one embodiment, the alarm statement query analysis module 410 is also used before determining the query statement for which the slow query alarm is directed, and also includes: setting corresponding query request tags for multiple query requests in the query statement, and determining the query path of the query statement through the query request tags; obtaining the occurrence conditions of a slow query event according to the query path; monitoring the query path, and if an event that meets the occurrence conditions is detected under the query path, determining that a slow query event has occurred, and issuing a slow query alarm for the query statement.

[0121] In one embodiment, the second query statement optimization model module 430 is also used for associating the second optimization scheme with the query statement category of the second query statement, and further includes: obtaining a query alarm window corresponding to the slow query alarm, and determining multiple second query statements corresponding to the slow query alarm in the query alarm window; extracting a statement template for the second query statement to obtain a query template corresponding to the second query statement, and classifying the second query statement according to the query template; and, when the number of second query statements of the same type is greater than a preset number, setting an optimization cache to cache the second optimization scheme corresponding to the second query statement of the same type.

[0122] In one embodiment, the query statement category association optimization module 440 is also used for setting the optimization cache when the number of second query statements of the same type is greater than a preset number, and further includes: assigning an identification label to the query template, and determining the optimization cache position corresponding to the second query statement classified by the query template based on the identification label; extracting the second optimization solution from the optimization cache position to optimize the second query statement.

[0123] In one embodiment, the second query statement optimization model module 430 is also used for the query statement optimization model to be trained through the following steps, including: obtaining a slow query log, and performing feature encoding on the query statement corresponding to the slow query log to obtain query statement features, determining the training status of the slow query log and the query statement features, and setting an optimization action to be performed on the query statement; inputting the slow query log and the query statement features into the query statement optimization model to be trained; the query statement optimization model executes the optimization action, determines the training status corresponding to the optimization action, and if under the training status, the query statement optimization model satisfies the preset training reward function convergence condition, then outputs the query statement optimization model after training.

[0124] In one embodiment, the second query statement optimization model module 430 is also used for inputting the query statement into the query statement optimization model, and after obtaining the second optimization scheme output by the model, it also includes: determining multiple performance indicators characterizing the query efficiency of the query statement based on multiple second optimization schemes output by the query statement optimization model, and setting corresponding preset weights for the performance indicators: scoring the second optimization schemes based on the preset weights, and determining the second optimization scheme corresponding to the highest scoring result as the target optimization scheme; matching the target optimization scheme with the category of the query statement, and saving the target optimization scheme to the optimization cache.

[0125] Each module in the above query optimization device can be implemented in whole or in part by software, hardware, or a combination thereof. Each module can be embedded in or independent of a processor in a computer device in the form of hardware, or can be stored in a memory in a computer device in the form of software, so that the processor can call and execute operations corresponding to each module.

[0126] In an exemplary embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as shown in FIG. Figure 5As shown. The computer device includes a processor, a memory, an input / output interface (Input / Output, referred to as I / O) and a communication interface. The processor, the memory and the input / output interface are connected through a system bus, and the communication interface is connected to the system bus through the input / output interface. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store optimization solution data. The input / output interface of the computer device is used to exchange information between the processor and an external device. The communication interface of the computer device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, a query statement optimization method is implemented.

[0127] Those skilled in the art will understand that Figure 5 The structure shown in the figure is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the computer device to which the solution of the present application is applied. The specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0128] In one embodiment, a computer device is provided, including a memory and a processor, wherein a computer program is stored in the memory, and the processor implements the steps in the above-mentioned method embodiments when executing the computer program.

[0129] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the steps in the above-mentioned method embodiments are implemented.

[0130] In one embodiment, a computer program product is provided, including a computer program, which implements the steps in the above method embodiments when executed by a processor.

[0131] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with relevant regulations.

[0132] A person of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment method can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to the memory, database or other medium used in the embodiments provided in the present application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. As an illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM). The database involved in each embodiment provided in this application may include at least one of a relational database and a non-relational database. Non-relational databases may include distributed databases based on blockchains, etc., but are not limited to this. The processor involved in each embodiment provided in this application may be a general-purpose processor, a central processing unit, a graphics processor, a digital signal processor, a programmable logic device, a data processing logic device based on quantum computing, an artificial intelligence (AI) processor, etc., but are not limited to this.

[0133] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.

[0134] The above-described embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the present application. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the attached claims.

Claims

1. A query statement optimization method, characterized in that: The method comprises: Determine the query statement for which the slow query alarm is issued, and perform a threshold determination on the query time corresponding to the query statement; If the query statement is a first query statement whose query time is less than a preset threshold, each of the first query statements is parsed by an observation tool to obtain a first optimization solution corresponding to the first query statement, and the first query statement is optimized according to the first optimization solution; If the query statement is a second query statement whose query time is greater than the preset threshold, inputting the second query statement into a query statement optimization model to obtain a second optimization solution corresponding to the second query statement output by the model; The second optimization scheme is associated with the query statement category of the second query statement, so that when other query statements under the query statement category are received, the other query statements are optimized according to the second optimization scheme associated with the query statement category.

2. The method according to claim 1, characterized in that Before determining the query statement for which the slow query alarm is issued, the method further includes: Setting corresponding query request tags for multiple query requests in the query statement, and determining a query path of the query statement through the query request tags; According to the query path, the occurrence condition of the slow query event is obtained; The query path is monitored, and if an event satisfying the occurrence condition is detected to occur under the query path, it is determined that a slow query event occurs, and a slow query alarm is issued for the query statement.

3. The method according to claim 1, characterized in that Before associating the second optimization solution with the query statement category of the second query statement, the method further includes: Obtaining a query alarm window corresponding to the slow query alarm, and determining a plurality of second query statements corresponding to the slow query alarm in the query alarm window; Extracting a statement template from the second query statement to obtain a query template corresponding to the second query statement, and classifying the second query statement according to the query template; and When the number of the second query statements of the same type is greater than a preset number, an optimization cache is set to cache the second optimization solutions corresponding to the second query statements of the same type.

4. The method according to claim 3, characterized in that After setting the optimized cache when the number of second query statements of the same type is greater than a preset number, the method further includes: Assigning an identification tag to the query template, and determining an optimized cache location corresponding to a second query statement classified by the query template according to the identification tag; The second optimization solution is extracted from the optimization cache location, and the second query statement is optimized.

5. The method according to claim 1, characterized in that The query statement optimization model is trained through the following steps, including: Obtain a slow query log, perform feature encoding on a query statement corresponding to the slow query log, obtain query statement features, determine a training state of the slow query log and the query statement features, and set an optimization action to be performed on the query statement; The slow query log and the query statement features are input into a query statement optimization model to be trained; the query statement optimization model executes the optimization action, determines the training state corresponding to the optimization action, and if under the training state, the query statement optimization model satisfies a preset condition for convergence of a training reward function, outputs a query statement optimization model that has completed training.

6. The method according to claim 1, characterized in that After inputting the query statement into the query statement optimization model to obtain the second optimization solution output by the model, the method further includes: According to the plurality of second optimization schemes output by the query statement optimization model, a plurality of performance indicators characterizing the query efficiency of the query statement are determined, and corresponding preset weights are set for the performance indicators: Based on the preset weights, the second optimization schemes are scored, and the second optimization scheme corresponding to the highest scoring result is determined as the target optimization scheme; The target optimization solution is matched with the category of the query statement, and the target optimization solution is saved in the optimization cache.

7. A query statement optimization device, characterized in that: The device comprises: The alarm statement query analysis module is used to determine the query statement for which the slow query alarm is issued and to perform a threshold judgment on the query time corresponding to the query statement; A first query statement analysis and optimization module, configured to, if the query statement is a first query statement whose query time is less than a preset threshold, parse each of the first query statements through an observation tool to obtain a first optimization solution corresponding to the first query statement, and optimize the first query statement according to the first optimization solution; A second query statement optimization model module, for inputting the second query statement into the query statement optimization model if the query statement is a second query statement whose query time is greater than the preset threshold, and obtaining a second optimization solution corresponding to the second query statement output by the model; The query statement category association optimization module is used to associate the second optimization scheme with the query statement category of the second query statement, so that when other query statements under the query statement category are received, the other query statements are optimized according to the second optimization scheme associated with the query statement category.

8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 6 are implemented.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

Images