Data processing method and device for database query request

Abstract

The invention relates to a data processing method and device for a database query request. The method comprises the steps of obtaining a plurality of database query requests; according to the execution logic of the database query requests, the database query requests are clustered to obtain clusters, and each cluster comprises at least one database query request; and determining a request behavior pattern of the database query request of each cluster, the request behavior pattern being an execution logic sequence of the database query requests in the corresponding cluster. The execution scheme can also be optimized according to the dependency graph of the database query request based on the determined request behavior mode. The method and the device are used for solving the problems of low accuracy, low efficiency and long consumed time caused by identifying request behavior modes of query requests one by one when the number of database query requests is large in the related technology.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method and device for processing data of a database query request. Background Art

[0002] With the development of cloud technology, deploying database services on cloud systems has become a common practice in the industry. The ever-increasing amount of data in actual production scenarios will generate a large number of SQL (Structured Query Language) database query requests. In the massive data scenario, the related technology analyzes the pattern request behavior mode of database query requests one by one, which not only has the problems of high computational cost and long time consumption, but is also prone to errors, resulting in low accuracy.

[0003] This section is intended to provide a background or context to the embodiments of the invention recited in the claims. No admission is made that the description herein is prior art by its inclusion in this section. Summary of the invention

[0004] A method and device for processing database query requests provided by an embodiment of the present invention at least solves the problem in the related art that when the number of database query requests is large, identifying the request behavior patterns of the query requests one by one will result in low accuracy, low efficiency and long time consumption.

[0005] An embodiment of the present invention provides a method for processing data of a database query request, comprising: obtaining a plurality of database query requests; clustering the database query requests according to the execution logic of the database query requests to obtain clusters, wherein each cluster includes at least one database query request; and determining a request behavior pattern of the database query requests of each cluster, wherein the request behavior pattern is the execution logic order of the database query requests in the corresponding cluster.

[0006] The beneficial effects of the embodiments of the present invention are as follows: by determining the execution logic of massive database query requests, and clustering them based on the execution logic to obtain cluster clusters, the request behavior patterns of the database query requests in each cluster cluster obtained by clustering are similar, and then determining the request behavior pattern for the database query request sequence in the cluster cluster as the request behavior pattern of the database query request in the cluster cluster, there is no need to provide a label for each database query request, nor is there a need to determine the request behavior model for the database query request one by one according to the label. Compared with the related art of identifying the request behavior pattern of the database query request one by one, not only is the accuracy of determining the request behavior pattern higher, but also it has higher efficiency and speed for massive database query requests, and solves the problem in the related art that when the number of database query requests is large, identifying the request behavior pattern of the query request one by one will cause low accuracy, low efficiency and long time consumption.

[0007] As an optional embodiment, obtaining multiple database query requests includes: collecting original data of database query requests within a preset time range according to multiple collectors deployed in the database; obtaining a query log of the collected database query requests based on the original data, wherein the query log includes the original query text and execution data; and determining the multiple database query requests based on the query log.

[0008] The original data collected by the collector is used to obtain the query log, and then the execution data of the database query request is obtained, so as to determine the request behavior pattern based on the execution data.

[0009] As an optional embodiment, clustering the database query requests according to the execution logic of the database query requests to obtain cluster clusters includes: determining the execution logic graph of the corresponding database query request according to the execution data of each database query request; and clustering multiple database query requests according to the execution logic graph using graph clustering to obtain cluster clusters.

[0010] Through the execution logic graph of database query requests, clustering is performed using graph clustering to achieve clustering of database query requests based on the similarity of the execution logic of the execution data, and multiple clusters are obtained, so that the execution logic of the database query requests in each cluster is similar, so that the subsequent request behavior pattern can be determined.

[0011] As an optional embodiment, determining the multiple database query requests according to the query log includes: receiving an input target query request, and according to the target query request, searching the query log for the database query request that includes the target query request to determine the multiple database query requests.

[0012] According to the input target query request, a database query request containing the target query request can be searched, thereby obtaining multiple database query requests corresponding to the target query request, so as to facilitate searching for database query requests according to needs, and determining and optimizing request behavior patterns.

[0013] As an optional embodiment, determining the request behavior pattern of the database query request of each clustering cluster includes: determining the state transition probability between the database query requests in the clustering cluster using a related probability model, wherein the related probability model is a mathematical model for determining the state transition probability between any two database query requests; determining the request behavior pattern of the clustering cluster based on the state transition probability and a preset probability threshold.

[0014] The relevant probability model is used to calculate the state transition probability of the database query request, and then the request behavior pattern of the database query request in the cluster is determined according to the preset probability threshold.

[0015] As an optional embodiment, according to the database query requests in the clustering cluster, using the relevant probability model, determining the state transition probability between the database query requests includes: creating prefix trees of multiple orders, wherein the multiple orders are multiple orders within a preset order range of the relevant probability model; according to the prefix trees of multiple orders, respectively calculating the model losses of the relevant probability models of multiple orders; selecting the order with the smallest model loss as the target order of the relevant probability model; and determining the relevant probability matrix of the database query requests in the clustering cluster according to the relevant probability model of the target order, wherein the relevant probability matrix is ​​used to determine the state transition probability between the database query requests.

[0016] The order of the relevant probability model is determined by the prefix tree, and then the relevant probability model is determined. The relevant probability matrix of the database query request is determined according to the relevant probability model to characterize the state transition probability between database query requests and prepare for determining the request behavior pattern.

[0017] As an optional embodiment, determining the request behavior pattern of the cluster cluster according to the state transition probability and the preset probability threshold includes: taking the first database query request as the first working behavior model, wherein the multiple database query requests in the cluster cluster form a request sequence according to a default execution order, and the first database query request is the first in the request sequence; when the state transition probability of executing the next database query request after the first database query request is greater than or equal to the preset probability threshold, adding the next database query request to the first behavior model; when the state transition probability of executing the next database query request after the first database query request is less than the preset probability threshold, taking the next database query request as the next behavior model; traversing the database query requests of the request sequence according to the default execution order to obtain the request behavior pattern, wherein the request behavior pattern includes at least two behavior models.

[0018] The working behavior model is determined by comparing the state transition probability of adjacent database query requests in the default execution order with a preset probability threshold, and then the request behavior pattern is determined.

[0019] As an optional embodiment, after determining the request behavior pattern of the database query request of each cluster, the method also includes: constructing a dependency graph based on the request behavior pattern and the dependency relationship of the corresponding database query request; optimizing the execution plan of the request behavior pattern according to the dependency graph to obtain an optimized execution plan.

[0020] The request behavior pattern is optimized by constructing a dependency graph of database query requests in the request behavior pattern.

[0021] As an optional embodiment, constructing a dependency graph according to the request behavior pattern and the dependency relationship of the corresponding database query request includes: determining the dependency relationship according to the execution data of the query log corresponding to each database query request in the request behavior pattern; creating a dependency graph according to the dependency relationship and the blocking range of the target data block of each database query request, wherein the dependency graph includes dependency subgraphs of multiple blocking ranges, and the dependency subgraphs are dependency graphs of database query requests under the same blocking range.

[0022] A dependency graph is constructed based on the dependency relationship of the database query request and the blocking range of the target data block of the database query request.

[0023] As an optional embodiment, creating a dependency graph according to the dependency relationship and the blocking range of the target data block of each database query request includes: selecting the database query request as a node in the dependency graph according to the default execution order of each database query request; if a dependency subgraph already exists in the blocking range of the selected database query request, adding the node of the selected database query request to the dependency subgraph, and generating a dependency edge, wherein the dependency edge points from the earlier database query request in the default order to the later database query request; if a dependency subgraph does not exist in the blocking range of the selected database query request, generating a new dependency subgraph, and using the node of the selected database query request as the first node of the dependency subgraph; and traversing the database query requests of the cluster cluster according to the default execution order of the database query requests in the cluster cluster to obtain the dependency graph.

[0024] According to the default execution order and dependency relationships, dependency subgraphs with different blocking scopes are generated, and then a dependency graph is generated.

[0025] As an optional embodiment, the execution plan of the request behavior pattern is optimized according to the dependency graph, and the optimized execution plan includes: setting the database query requests in the dependency subgraph of the same blocking range in the dependency graph to serial execution in the execution plan of the request behavior pattern; setting the database query requests in the dependency subgraphs of different blocking ranges in the dependency graph to parallel execution in the execution plan of the request behavior pattern; obtaining the optimized execution plan.

[0026] By parallelizing independently executed database query requests, the execution plan of the request behavior pattern is optimized, thereby improving the execution speed and efficiency of the request behavior pattern.

[0027] As an optional embodiment, the method also includes: displaying the execution plan of the request behavior pattern before optimization in a first window; in response to the optimization operation, performing the step of optimizing the execution plan of the request behavior pattern according to the dependency graph to obtain the execution plan of the optimized request behavior pattern; displaying the optimized execution plan in a second window, wherein, in the second window, parallel database query requests are displayed side by side, and serial database query requests are displayed column by column.

[0028] The determined request behavior pattern is displayed according to the needs, and the execution plan of the optimized request behavior pattern is displayed in response to user operations. Parallel database query requests are displayed side by side, and serial database query requests are displayed column by column, so as to intuitively show the optimization effect and the optimized execution plan.

[0029] This embodiment also provides a data processing device for database query requests, including: an acquisition module, used to acquire multiple database query requests; a clustering module, used to cluster the database query requests according to the execution logic of the database query requests to obtain multiple cluster clusters, wherein each cluster cluster includes at least one database query request; a determination module, used to determine the request behavior pattern of the database query requests of each cluster cluster, wherein the request behavior pattern is the execution logic order of the database query requests in the corresponding cluster cluster.

[0030] The beneficial effects of the embodiments of the present invention are as follows: by determining the execution logic of massive database query requests, and clustering based on the execution logic to obtain cluster clusters, the request behavior patterns of the database query requests in each cluster cluster obtained by clustering are similar, and then determining the request behavior pattern for the database query request sequence in the cluster cluster as the request behavior pattern of the database query request in the cluster cluster, there is no need to provide a label for each database query request, nor is there a need to determine the request behavior model for the database query request one by one according to the label. Compared with the related art of identifying the request behavior pattern of the database query request one by one, not only is the accuracy of determining the request behavior pattern higher, but also it has higher efficiency and speed for massive database query requests, and solves the problem of low accuracy and long time consumption when identifying the request behavior pattern of the query request one by one in the related art when the number of database query requests is large.

[0031] This embodiment further provides an electronic device, including: a processor, and a memory storing a program, wherein the program includes instructions, and when the instructions are executed by the processor, the processor executes any one of the methods described above.

[0032] This embodiment also provides a non-transitory machine-readable medium storing computer instructions, wherein the computer instructions are used to enable the computer to execute any one of the above methods.

[0033] The details of one or more embodiments of the invention are set forth in the following drawings and description so that other features, objects, and advantages of the invention are more readily apparent. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the prior art descriptions. Obviously, the drawings described below are only some embodiments of the present invention, and for ordinary technicians in this field, other embodiments can be obtained based on these drawings without creative work.

[0035] Figure 1The present invention is a flowchart of a method for processing data of a database query request according to an embodiment of the present invention.

[0036] Figure 2 It is a schematic diagram of a request behavior pattern mining and execution optimization architecture of a SQL request according to an embodiment of the present invention.

[0037] Figure 3 It is a flowchart of another method for processing data of a database query request according to an embodiment of the present invention.

[0038] Figure 4 It is a flowchart of another method for processing data of a database query request according to an embodiment of the present invention.

[0039] Figure 5 It is a schematic diagram of calculating a state transition probability matrix based on a prefix tree according to an embodiment of the present invention.

[0040] Figure 6 It is a schematic diagram of determining a behavior model based on calculating state transition probability according to an embodiment of the present invention.

[0041] Figure 7 It is a flowchart of another method for processing data of a database query request according to an embodiment of the present invention.

[0042] Figure 8 It is a schematic diagram of the execution optimization process of an embodiment of the present invention.

[0043] Fig. 9 It is a flowchart of another method for processing data of a database query request according to an embodiment of the present invention.

[0044] Fig.10 A schematic diagram of a process for creating a dependency graph according to an embodiment of the invention.

[0045] Fig.11 A schematic diagram of a request behavior pattern display page according to an embodiment of the invention.

[0046] Fig.12 It is a schematic diagram of a data processing device for a database query request according to an embodiment of the present invention.

[0047] Fig.13 Schematic diagram of the structure of the electronic device of this embodiment. DETAILED DESCRIPTION

[0048] Embodiments of the present embodiment will be described in more detail below with reference to the accompanying drawings. Although certain embodiments of the present embodiment are shown in the accompanying drawings, it should be understood that the present embodiment can be implemented in various forms and should not be construed as being limited to the embodiments set forth herein, which are instead provided for a more thorough and complete understanding of the present embodiment. It should be understood that the drawings and embodiments of the present embodiment are only for exemplary purposes and are not intended to limit the scope of protection of the present embodiment.

[0049] DAS, Database Autonomy Service, is a cloud service that realizes database self-perception, self-repair, self-optimization, self-operation and self-security based on machine learning and expert experience. It can help users eliminate the complexity of database operation and maintenance and service failures caused by manual operations, and effectively ensure the stability, security and efficiency of database services.

[0050] With the development of cloud technology, deploying database autonomous services on cloud systems has become a common practice in the industry. For example, some cloud service systems in related technologies can select problematic SQL statements that exceed the threshold for index optimization after discovering SQL statements that take a long time or have a long lock wait time. They can also analyze a single SQL statement and give optimization suggestions, and can also help users deeply understand the reasons and details of the optimization through a visual execution plan. However, the above optimization is performed on a single problematic SQL statement.

[0051] In the related technology, some databases can also provide faster analytical queries for current data. Pushing query processing down to the storage layer allows queries to obtain a large amount of computing power and significantly reduces the data that needs to be transmitted over the network. This optimization solution is not an optimization of the SQL mode at the user service level. The engine side of some databases can provide the parallel execution capability of a single SQL and perform full table scans in parallel. Given the execution plan of an SQL query, the parallel execution coordinator breaks down the entire SQL query into parallel parts and runs them in parallel in the order specified in the query. Some database engines provide the parallel execution capability of a single SQL. Specifically, first determine whether the current system workload and related parallel configuration information allow parallel execution. If parallel execution is required, the database engine will determine the number of worker threads and distribute the execution of the parallel plan to these worker threads. This parallel plan is for the SQL that currently needs to be executed, and is not a reusable optimization suggestion for user services.

[0052] In summary, there are two main optimization solutions for SQL execution in related technologies:

[0053] 1. Optimization suggestions for the execution of a single SQL statement, but it is difficult to analyze each statement one by one when dealing with massive SQL statements:

[0054] The process framework of automatic index recommendation in related technologies includes: the control plane is responsible for controlling the life cycle of the entire automatic optimization process and coordinating different components; the index recommender is used to analyze the workload and identify which list indexes need to be created or abandoned; the validator is used to analyze the impact of the list index on the workload and check whether the new list index causes SQL performance to deteriorate.

[0055] The SQL optimization of the control end of some cloud service systems can provide users with a one-click optimization function for SQL statements, and provide analysis and optimization suggestions for the corresponding execution plan, which is suitable for scenarios such as slow SQL execution optimization. You can also manually enter the SQL statement, execute the analysis to obtain the performance evaluation results and optimization suggestions for the statement. However, this type of SQL optimization is only for the user's full request or input statement, and does not optimize the request behavior pattern.

[0056] 2. The parallel optimization solution for a single SQL query at the engine level cannot cope with the SQL execution logic optimization at the user service level:

[0057] The parallel execution solution provided by cloud database vendors in the related art mainly optimizes the query plan of a given SQL query, and decomposes each query in the SQL query into parallel parts through a parallel execution coordinator, and runs them in the correct order specified in the query. When parallel execution is not used, a single server process executes all the processing required for the sequential execution of SQL statements. For example, to perform a full table scan (such as SELECT * FROM employees), one process will perform the entire operation.

[0058] Parallel execution uses multiple parallel processes to execute these operations in parallel. A process called the parallel execution coordinator dispatches the execution of a statement to multiple parallel execution servers and coordinates the results of all server processes to send the results back to the user, thereby optimizing the full table scan statement. This solution performs kernel-level parallel optimization for a single SQL statement, rather than optimizing the execution mode at the user service level.

[0059] Moreover, in actual production scenarios, as usage time increases, the amount of data usually increases. The growing amount of data will generate a large number of SQL requests. The massive number of requests will inevitably cause some database performance problems, such as long execution time and longer lock waiting time.

[0060] To this end, this embodiment proposes a parallel optimization solution based on database request behavior pattern recognition, which obtains multiple clusters by clustering massive database query requests, and the request behavior patterns of the database query requests in each cluster are similar, so as to determine the request behavior pattern for the database query request sequence in the cluster. Compared with the related art of identifying the request behavior pattern of database query requests one by one, it has higher efficiency and speed, and improves the accuracy, thereby solving the problems of low accuracy, low efficiency and long time consumption in the related art.

[0061] Specifically, it deals with unsupervised request behavior pattern mining in massive data scenarios: actual industrial data is complex and diverse. The user's complex request behavior is split through unsupervised clustering, and the SQL mining request behavior patterns under similar services are used to maximize the protection of user privacy and maintain data security.

[0062] You can also analyze the conflict relationship of each SQL request by building a dependency graph, and provide a parallel execution solution for SQL statements at the user service level based on this. Dependency graph construction and reusable parallel optimization: Build a SQL dependency graph based on the mined request behavior pattern (SQL Pattern), analyze the conflict relationship of each SQL request, and provide users with reusable SQL parallel execution optimization suggestions at the user service level.

[0063] Figure 1 is a flow chart of a method for processing data of a database query request according to an embodiment of the present invention. Figure 1 To solve the above technical problems, an embodiment of the present invention provides a method for processing data of a database query request, comprising the following steps:

[0064] Step S101, obtaining multiple database query requests;

[0065] Step S102, clustering the database query requests according to the execution logic of the database query requests to obtain clusters, wherein each cluster includes at least one database query request;

[0066] Step S103: determining a request behavior pattern of a database query request of each cluster, wherein the request behavior pattern is a logical order of execution of the database query request in the corresponding cluster.

[0067] The data processing method for the above-mentioned database query request provided by the embodiment of the present invention determines the execution logic of massive database query requests, and clusters them based on the execution logic to obtain cluster clusters, so that the request behavior patterns of the database query requests in each cluster cluster obtained by clustering are similar, and then determines the request behavior pattern for the database query request sequence in the cluster cluster. As the request behavior pattern of the database query request in the cluster cluster, there is no need to provide a label for each database query request, nor is it necessary to determine the request behavior model for the database query request one by one according to the label.

[0068] Compared with the related art of identifying the request behavior patterns of database query requests one by one, not only is the accuracy of determining the request behavior patterns higher, but it also has higher efficiency and speed for massive database query requests. It solves the problem of low accuracy and long time consumption in the related art when the number of database query requests is large and the request behavior patterns of query requests are identified one by one.

[0069] During use, there are SQLs with the same task that have similar execution trends within a certain time range. Therefore, this embodiment clusters database query requests according to the similarity of their execution logics to obtain one or more clusters. The execution logics of database query requests in the same cluster are highly similar and can be put together to determine the request behavior pattern.

[0070] In the above, the database query requests are clustered according to the execution logic of the database query requests, and the similarity between the execution logics can be determined by the similarity algorithm of the execution logic. Specifically, the execution logic is represented by fields or graphics, and then the similarity can be determined by the similarity algorithm, and then clustering is performed by the clustering algorithm.

[0071] It is also possible to directly use a clustering algorithm based on the similarity association principle of the execution logic to cluster database query requests directly using the execution logic.

[0072] Then, for the database query request in each cluster, its request behavior pattern is determined to characterize the execution logic order of the database query request.

[0073] It should be noted that the request behavior pattern of the database query request of the clustering cluster can be determined by using the Markov model and the related probability transfer matrix, and the specific details are described below.

[0074] In addition, the final request behavior pattern may include multiple behavior models. The database query requests in the request behavior pattern are usually executed in a serial order, which will result in slow execution. After determining the request behavior pattern, this embodiment can also optimize its execution order and execute database query requests that can be executed in parallel in parallel to improve execution efficiency.

[0075] Figure 2 is a schematic diagram of a request behavior pattern mining and execution optimization architecture of an SQL request according to an embodiment of the present invention, such as Figure 2 As shown, the execution trends of SQL requests under the same service are similar within a certain time range. This implementation aims to split the SQL request based on the clustering algorithm of the graph model, and then use the Markov model to mine the request behavior pattern SQL pattern, and then propose reusable parallel optimization suggestions for the request behavior pattern.

[0076] First, some technical terms involved are explained as follows:

[0077] Markov Chain Model: Markov Chain is a type of random process. The Markov Chain Model believes that the future state depends only on the present state and has nothing to do with the past state.

[0078] Request behavior pattern SQL pattern: An interpretable SQL context sequence in database request behavior, which represents the characteristic pattern of the workload and can reveal the query pattern corresponding to the application.

[0079] Dependency Graph: In a program, a dependency graph refers to a graphical representation of a program, which is a directed multigraph with labels. A program dependency graph can represent the control dependency and data dependency of a program. The dependency graph in this embodiment refers to a more general dependency graph, which is used to represent the dependency relationship between SQL database query requests.

[0080] Directed acyclic graph: A directed acyclic graph (DAG) is a directed graph without loops (cycles). If a directed graph cannot start from a vertex and return to that vertex through several edges, then the graph is a directed acyclic graph.

[0081] This implementation method analyzes and mines the request behavior patterns of the user's SQL requests, and optimizes the performance of the database by analyzing these patterns.

[0082] On the one hand, it can cope with massive data scenarios: In massive data scenarios, the computational cost and time cost of analyzing and optimizing SQL requests one by one are often very high, and the SQL request execution trends of related service scenarios are often similar. Therefore, unsupervised methods can be used to analyze the correlation of massive SQL request execution trends, split the massive interspersed services, and mine request behavior patterns for subsequent analysis, while maximizing the protection of user data security.

[0083] On the other hand, reusable optimization suggestions: Based on the analyzed request behavior patterns, more efficient data access request parallel optimization services can be provided. Parallel execution optimization analysis is performed for some commonly occurring fixed SQL request behavior patterns, and parallel optimization suggestions are provided. These optimization suggestions are reusable for similar request behavior patterns.

[0084] The main steps include: Graph-based graph clustering of SQL requests, including collecting SQL requests and performing graph clustering according to the graph clustering method.

[0085] To mine the behavior patterns of SQL requests, we first build a prefix tree based on the data access request sequence of the workload to prepare for the Markov model. We calculate and select a suitable Markov model. Then we identify the SQL Pattern based on the Markov model.

[0086] Pattern-based parallel execution optimization analysis based on request behavior patterns, including dependency analysis of database query requests and construction of dependency graphs.

[0087] The optimization suggestion recommendation system based on the request behavior pattern executes the database query requests that can be executed in parallel in parallel according to the execution order of database query requests with different blocking ranges in the dependency graph to optimize the execution efficiency.

[0088] Figure 3 FIG. 1 is a flowchart of another method for processing data of a database query request according to an embodiment of the present invention. Figure 3 As shown, the embodiment of the present invention also provides another method for processing data of database query requests. Optionally, the method for processing data of database query requests provided by the embodiment of the present invention can be used in step S101 provided in the above embodiment. In the above step S101, obtaining multiple database query requests includes:

[0089] Step S301, collecting original data of database query requests within a preset time range according to multiple collectors deployed in the database;

[0090] Step S302, obtaining a query log of the collected database query request according to the original data, wherein the query log includes the original query text and execution data;

[0091] Step S303: determining multiple database query requests according to the query log.

[0092] The data processing method for database query requests provided in this embodiment obtains query logs through the original data collected by the collector, and then obtains the execution data of the database query request, so as to determine the request behavior pattern according to the execution data.

[0093] The source of database query requests may be a large number of database instances. In order to accurately and effectively collect database query requests, the collector can be first deployed to the massive database instances, and the streaming raw data of the database query requests can be collected based on the collector.

[0094] Based on the original data, the query log of the database query request is obtained, and all query information of each database query request is collected. Specifically, the query information consists of basic information (such as detailed SQL statements) and execution feature measurement data (such as query response time rt and number of rows examined rows_examined). As shown in Table 1, Table 1 is a specific description.

[0095] Table 1 Feature fields and their descriptions in query information

[0096]

[0097] When database query requests are collected, they are asynchronously stored in cloud storage in real time, so that the collection and storage processes of database query requests are independent and do not interfere with each other, minimizing interference with the operation of the database instance.

[0098] Since the data is stored asynchronously, there is little impact on the database instance. The collected database query request data can be deleted after a period of time to keep the size of the stored data within a certain limit. Specifically, the data will expire after three days (or another user-defined validity period).

[0099] Each executed query log consists of two parts of information: (i) the original query text; (ii) the recorded execution data. The original query text is the text content of the database query statement, and the execution data can be the execution-related data of the database query statement, which can include the reference, call and other dependency information of the database query statement. It can be used as a basis for optimizing the execution plan of the request behavior pattern in the future.

[0100] As an optional embodiment, clustering the database query requests according to the execution logic of the database query requests to obtain cluster clusters includes: determining the execution logic graph of the corresponding database query request according to the execution data of each database query request; and clustering multiple database query requests according to the execution logic graph using graph clustering to obtain cluster clusters.

[0101] Through the execution logic graph of database query requests, we use graph clustering to cluster database query requests based on the execution logic similarity of the execution data.

[0102] Clustering clusters makes the execution logic of database query requests in each cluster similar so that subsequent request behavior patterns can be determined.

[0103] The full database query requests in real service scenarios are relatively complicated, which will cause database query requests in different service scenarios to always be interspersed together, destroying the original request behavior pattern. Although classifiers can be used to automatically collect labels, this method is time-consuming and, more importantly, cannot guarantee user privacy.

[0104] Since the execution trends of database query requests under the same service are similar within a certain time range, a graph-based clustering method based on the similarity of database query request execution trends is adopted to solve the problem of database query requests interspersed with different services. No labeling process is required, thus protecting user privacy.

[0105] It should be noted that before performing graph clustering on database query requests, an execution logic graph can be determined based on the dependency relationship of the database query requests to characterize the execution logic of the database query requests. The execution logic graph may include nodes that characterize the execution state or execution subject, and arrows between nodes that characterize the execution order. This allows graph clustering to more accurately cluster clusters with similar execution logic.

[0106] As an optional embodiment, determining multiple database query requests according to the query log includes: receiving an input target query request, searching the query log for a database query request including the target query request according to the target query request, and determining multiple database query requests.

[0107] According to the input target query request, the database query request containing the target query request can be searched, thereby obtaining multiple database query requests corresponding to the target query request, so as to facilitate the search of database query requests according to needs, and determine and optimize the request behavior pattern.

[0108] In some embodiments, Fig.11As shown, a database query request including the target query request can be obtained according to the target query request input by the user to determine multiple database query requests, which can be provided to the user through an interface so that the user can mine the request behavior pattern and optimize the execution plan according to the needs.

[0109] Fig.11 In the input box, the current request to be analyzed is entered. Based on the current request, the execution plan of the request behavior pattern including the current request can be obtained by "analysis", which is displayed on the left. Then, the optimization strategy for the execution plan of the request behavior pattern on the left can be obtained by "optimization", which is displayed in the table on the right. In the recommended strategy displayed on the right, the statements in parallel horizontally can be executed in parallel, and the statements in parallel vertically can be executed in series.

[0110] Figure 4 FIG. 1 is a flowchart of another method for processing data of a database query request according to an embodiment of the present invention. Figure 4 As shown, the embodiment of the present invention also provides another method for processing data of database query requests. Optionally, the method for processing data of database query requests provided by the embodiment of the present invention can be used in step S103 provided in the above embodiment. In the above step S103, determining the request behavior pattern of the database query request of each clustering cluster includes:

[0111] Step S401, according to the database query requests in the cluster, using the relevant probability model, determining the state transition probability between the database query requests, wherein the relevant probability model is a mathematical model for determining the state transition probability between any two database query requests;

[0112] Step S402: determining the request behavior pattern of the cluster according to the state transition probability and a preset probability threshold.

[0113] The data processing method for database query requests provided in this embodiment calculates the state transition probability of the database query request through a relevant probability model, and then determines the request behavior pattern of the database query request in the cluster according to a preset probability threshold.

[0114] The above-mentioned related probability model can be a Markov model, and other types of related probability methods of request behavior pattern mining are also within the protection scope of this application. For example, the association probability of a directed graph or undirected graph of a probabilistic graph model can also be used to mine request behavior patterns.

[0115] The state transition probability between database query requests may include the state transition probability between any two database query requests in the cluster. The above state transition probability is the probability of executing the subsequent database query request after the previous database query request is executed. It can characterize the probability of sequential execution between two database query requests.

[0116] According to the size of the state transition probability and the preset probability threshold, it can be determined whether the state transition probability meets the requirements of the request behavior pattern. If the state transition probability is greater than or equal to the preset probability threshold, it means that the order of executing the subsequent database query request after the current previous database query request can be used as a request behavior pattern. If the state transition probability is less than the preset probability threshold, it means that the order of executing the subsequent database query request after the current previous database query request cannot be used as a request behavior pattern.

[0117] As an optional embodiment, according to the database query requests in the clustering cluster, using the correlation probability model, determining the state transition probability between the database query requests includes: creating prefix trees of multiple orders, wherein the multiple orders are multiple orders within a preset order range of the correlation probability model; according to the prefix trees of the multiple orders, respectively calculating the model losses of the correlation probability models of the multiple orders; selecting the order with the smallest model loss as the target order of the correlation probability model; and determining the correlation probability matrix of the database query requests in the clustering cluster according to the correlation probability model of the target order, wherein the correlation probability matrix is ​​used to determine the state transition probability between the database query requests.

[0118] The order of the relevant probability model is determined by the prefix tree, and then the relevant probability model is determined. The relevant probability matrix of the database query request is determined according to the relevant probability model to characterize the state transition probability between database query requests and prepare for determining the request behavior pattern.

[0119] In this embodiment, the above-mentioned related probability model is a Markov model as an example for description.

[0120] Use the Markov model to model a database query request sequence, which is the above request sequence, and mine the important request behavior patterns and the loop structure in the sequence. First, build a prefix tree based on the request sequence to prepare for the Markov model. Calculate and select the appropriate Markov model.

[0121] The state transition probability is calculated according to the MDL (minimum description length) principle of the pruning model. When calculating, it is necessary to determine the method of calculating the state transition probability based on the threshold, calculate the loss of Markov models of different orders according to the MDL principle of the pruning model, select the order of the Markov model with the smallest loss, and calculate the state transition matrix based on this selected Markov model. The details are as follows:

[0122] Figure 5 is a schematic diagram of calculating a state transition probability matrix according to a prefix tree in an embodiment of the present invention, such as Figure 5As shown, assuming that the database query request sequence T = abababadac is a prefix tree with a height of 2 established for the first-order Markov model, Figure 5 It can be seen that the prefix tree with a height of 2 has 2 layers of nodes, node n0 is the root node, node n1 indicates that a appears 5 times in the sequence, node n5 indicates that ab appears 3 times in the sequence, and so on.

[0123] Specifically, in order to reduce calculations and improve the generalization ability of the Markov model, the state transition probability matrix can be calculated according to the MDL (Minimum Description Length) principle of the pruning model, that is, the size of the state transition probability matrix is ​​first determined according to the number of request types contained in the target database query request sequence and the order of the Markov model, and then the state transition probability of each element in the matrix is ​​calculated using the established prefix tree.

[0124] Among them, the size of the state transfer matrix is ​​|T|order×|T|, and |T| is the number of request types in the target database query request sequence.

[0125] Furthermore, in the process of calculating the state transition probability using the MDL principle, a threshold τ = 1 / |T| is first determined, and then the method of using the threshold τ to judge the state transition probability is: (a) if the state transition probability is greater than or equal to the threshold τ, then the state transition probability is equal to the calculated value; (b) if the remaining state transition probabilities do not meet condition (a), then the remaining state transition probabilities are considered to be uniformly distributed.

[0126] Continue to see above Figure 5 As shown, the number of request types in the target database query request sequence |T|=4, namely, including four types: SQLa, SQL b, SQL c, and SQL d. Therefore, the size of the state transition probability matrix is ​​|T|order×|T|=41×4. Threshold τ=1 / |T|=1 / 4. Taking the first column of the state transition probability matrix corresponding to the leftmost branch of the prefix tree as an example, Prob(b|a)=val(n5) / val(n1)=3 / 5>1 / 4, Prob(c|a)=val(n6) / val(n1)=1 / 5<1 / 4, Prob(d|a)=val(n7) / val(n1)=1 / 5<1 / 4, Prob(a|a)=0 / val(n1)=0<1 / 4. Since the three state transition probabilities Prob(c|a), Prob(d|a), and Prob(a|a) are all less than the threshold τ, the three state transition probabilities are uniformly distributed, and the specific probability values ​​should be: (1-Prob(b|a)) / 3=2 / 15. The state transition probabilities of other columns are also calculated according to the above principle, and finally obtained as follows Figure 5The state transition probability matrix shown.

[0127] Furthermore, the model loss cost is calculated according to the MDL principle of the pruning model:

[0128] cost(x,M θ(k) )=2logk+1+2logm+1+m((k+1)log|T|+2logn)-logProb(x|M θ(k) )

[0129] Where k is the order of the Markov model; m is the number of state transition probabilities greater than the threshold τ in the state transition probability matrix; |T| is the number of request types in the sequence; n is the length of the sequence; Prob(x|M θ(k) ) is the probability value obtained by multiplying the state transition probabilities of the target database query request sequence. Taking the first-order Markov model as an example, as mentioned above Figure 5 As shown in the figure, the state transition probabilities that form the target database query request sequence are multiplied to obtain Prob(x|M θ(1) ):

[0130] Prob(x|M θ(1) )=Prob(a)×Prob(b|a)×Prob(a|b)×Prob(b|a)×Prob(a|b)×Prob(b|a)×Prob(a|b)×Prob(d|a)×Prob(a|d)×Prob(c|a)

[0131] As an optional embodiment, determining the request behavior pattern of the clustering cluster according to the state transition probability and the preset probability threshold includes: taking the first database query request as the first working behavior model, wherein the multiple database query requests in the clustering cluster form a request sequence according to a default execution order, and the first database query request is the first in the request sequence; when the state transition probability of executing the next database query request after the first database query request is greater than or equal to the preset probability threshold, adding the next database query request to the first behavior model; when the state transition probability of executing the next database query request after the first database query request is less than the preset probability threshold, taking the next database query request as the next behavior model; traversing the database query requests of the request sequence according to the default execution order to obtain the request behavior pattern, wherein the request behavior pattern includes at least two behavior models.

[0132] The working behavior model is determined by comparing the state transition probability of adjacent database query requests in the default execution order with a preset probability threshold, and then the request behavior pattern is determined.

[0133] The above-mentioned request behavior pattern includes at least two behavior models, and one behavior model includes at least one database query request sequence. The principle of analyzing it as a database query request sequence is: taking the k-order Markov model as an example, a threshold θ for selecting a database query request sequence can be pre-set. If the state transition probability Prob(s|c1...ck)≥θ, then c1...cks is considered to be a database query request sequence, and if the state transition probability Prob(s|c1...ck)<θ, then c1...ck is considered to be a database query request sequence.

[0134] Figure 6 is a schematic diagram of determining a behavior model according to calculating state transition probability according to an embodiment of the present invention, such as Figure 6 As shown, the threshold θ=0.7 is preset. Since the state transition probability of the q1q2 sequence is greater than or equal to the threshold θ, and the probability of the q1q2q3 sequence is less than the threshold θ, the q1q2 sequence is a request behavior pattern. The state transition probability of the q1q4 sequence is less than the threshold θ, and therefore, it is not a request behavior pattern.

[0135] In the above example of the k=1 order Markov model, the threshold θ=0.7 is preset, and a database query request sequence is defined as a behavior model. In the target database query request sequence T=abababadac, the behavior model is first initialized to the first one in the sequence T: a, and then Prob(T[2]|T[1])=Prob(b|a)=3 / 5<0.7 is calculated, so the first behavior model is a; then the next behavior model is initialized to b, and Prob(T[3]|T[2])=Prob(a|b)=1>0.7 is calculated, and then the behavior model is updated to ba, and Prob(T[ 4]|T[3])=Prob(b|a)=3 / 5<0.7, so ba is the second behavior model; initialize the next behavior model to b, calculate Prob(T[5]|T[4])=Prob(b|a)=3 / 5<0.7, Prob(T[6]|T[5])=Prob(b|a)=3 / 5<0.7, then update the behavior model to ba, calculate Prob(T[7]|T[6])=Prob(a|b)=1>0.7, so ba is the third behavior model; continue to calculate and get the request behavior pattern of sequence T as {a, ba, ba, ba, da, c}.

[0136] It should be noted that a long and complex sequence of target database query requests may have many repeated behavior models. For such cases, loop detection can be performed and behavior models of the same loop can be merged. For example, the three behavior models "ba" in the request behavior pattern pattern {a, ba, ba, ba, da, c} of sequence T can be merged into one behavior model, so the final request behavior pattern pattern of sequence T is {a, ba, da, c}.

[0137] Figure 7 FIG. 1 is a flowchart of another method for processing data of a database query request according to an embodiment of the present invention. Figure 7 As shown, the embodiment of the present invention also provides another method for processing data of database query requests. Optionally, the method for processing data of database query requests provided by the embodiment of the present invention can be used for the method for processing data of database query requests provided by the above embodiment. After the above step S103, determining the request behavior pattern of the database query request of each cluster, the method further includes:

[0138] Step S701, constructing a dependency graph according to the request behavior pattern and the dependency relationship of the corresponding database query request;

[0139] Step S702, optimizing the execution plan of the request behavior pattern according to the dependency graph to obtain an optimized execution plan.

[0140] The data processing method for database query requests provided in this embodiment optimizes the execution scheme of the request behavior pattern by constructing a dependency graph of the database query requests in the request behavior pattern.

[0141] There are usually two sources for request behavior patterns. The first is a pattern in a piece of code, for example, a function first executes a "SELECT" command on the "user" table to select the requested user ID, and then iteratively executes "SELECT" and "UPDATE". For example, the following example is a representative code pattern, where the user executes the "SELECT" command and then executes the "UPDATE" and "INSERT" operations on table 1 table1 and table 1 state table1_state respectively:

[0142] SQL1:SELECT id,gmt_create,gmt_modified,name,type,max_hold_time,max_concurrency,active_concurrency,comment,version FROM table1 WHERE 1=1AND name=?

[0143] SQL2:SELECT id,gmt_create,gmt_modified,name,holder,heartbeat FROMtable1_state WHERE 1=1AND name=?

[0144] SQL3:UPDATE table1 SET max_hold_time=? ,max_concurrency=? ,active_concurrency=? ,comment=? ,gmt_modified=now(),version=version+? WHERE id=? AND version=?

[0145] SQL4:INSERT INTO table1_state(id,gmt_create,gmt_modified,name,holder,heartbeat)VALUES(?,now(),now(),?,?,?)

[0146] SQL5:SELECT LAST_INSERT_ID()

[0147] Another pattern is remote procedure calls from different microservices. Therefore, multiple microservices will call each other in the user's request, forming a directed acyclic graph (DAG) with a calling relationship.

[0148] In both cases, there are intentional or unintentional dependencies in the code that queries the cloud database. In addition, the dependencies are hidden in the patterns mined from the SQL query log corpus, and the corresponding effective optimization strategies are also included. However, some dependencies may be incorrect or unnecessary.

[0149] To this end, in the process of optimizing the execution plan of the request behavior pattern, a dependency graph is first created according to the dependency relationship of the database query request, and the execution of the database query request is optimized based on the dependency graph.

[0150] Figure 8 Schematic diagram of the execution optimization process of an embodiment of the present invention. Figure 8 As shown in the figure, with the help of the output request behavior patterns mined from the SQL query log corpus, a parallel optimization strategy is designed to proactively provide cloud database users with suggestions on how to optimize their logical codes for better performance.

[0151] The reason for proposing a parallel optimization strategy based on the mined request behavior pattern is that directly optimizing the execution plan based on the entire request behavior pattern extracted from the log requires specific analysis of each SQL, which is difficult to cope with massive SQL data scenarios. Optimizing queries based on mined request behavior patterns can be fundamentally optimized. Because request behavior patterns are more frequently occurring sequences and are closer to the essence of user request behavior, the optimization solutions proposed based on this are reusable under similar request behavior patterns.

[0152] As an optional embodiment, constructing a dependency graph according to the request behavior pattern and the dependency relationship of the corresponding database query request includes: determining the dependency relationship according to the execution data of the query log corresponding to each database query request in the request behavior pattern; creating a dependency graph according to the dependency relationship and the blocking range of the target data block of each database query request, wherein the dependency graph includes dependency subgraphs of multiple blocking ranges, and the dependency subgraph is a dependency graph of the database query request under the same blocking range.

[0153] A dependency graph is constructed based on the dependency relationship of the database query request and the blocking range of the target data block of the database query request.

[0154] The execution data of the query log may include dependency information of the database query request. The dependency relationship of the database query request may be determined based on the execution data of the query log.

[0155] For example, in the example shown in Table 2, the execution of SQL1 and SQL3 is dependent, and the execution of SQL2 and SQL4 is also dependent, but the execution order of SQL1 and SQL2, SQL3 and SQL4 implies unnecessary dependencies. Therefore, executing related SQL in parallel can improve database performance.

[0156] Table 2 Example dependency table

[0157]

[0158]

[0159] Then, the dependencies of the request behavior patterns are analyzed, and strategies are found that allow users to execute database query requests in parallel as much as possible. A dependency graph is constructed based on the dependencies of database query requests, and then corresponding optimization suggestions are analyzed based on the constructed dependency graph.

[0160] Fig. 9 FIG. 1 is a flowchart of another method for processing data of a database query request according to an embodiment of the present invention. Fig. 9As shown, the embodiment of the present invention also provides another method for processing data of database query requests. Optionally, the method for processing data of database query requests provided by the embodiment of the present invention can be used for the method for processing data of database query requests provided by the above embodiment. In the above steps, creating a dependency graph according to the dependency relationship and the blocking range of the target data blocks of each database query request includes:

[0161] Step S901, selecting a database query request as a node in a dependency graph according to the default execution order of each database query request;

[0162] Step S902: if a dependency subgraph already exists in the blocking range of the selected database query request, add the node of the selected database query request to the dependency subgraph and generate a dependency edge, wherein the dependency edge points from the earlier database query request in the default order to the later database query request;

[0163] Step S903: if there is no dependency subgraph in the blocking range of the selected database query request, a new dependency subgraph is generated, and the node of the selected database query request is used as the first node of the dependency subgraph;

[0164] Step S904: traverse the database query requests of the clusters according to the default execution order of the database query requests in the clusters to obtain a dependency graph.

[0165] The data processing method for database query requests provided in this embodiment generates dependency subgraphs of different blocking ranges according to the default execution order and dependency relationships, and then generates a dependency graph.

[0166] Since the dependency relationship usually depends on the target data block of the database query request, when the target data block is called by the database query request, it cannot be called by other database query requests, which also creates a dependency relationship. Therefore, when analyzing the dependency relationship, the blocking range of the database query request is mainly considered.

[0167] Fig.10 A schematic diagram of a process for creating a dependency graph according to an embodiment of the invention is shown in FIG. Fig.10 As shown, when considering the dependencies of database query requests, it is necessary to detect blocking queries of the target data block, that is, queries that may prevent the execution of subsequent queries. For example, the "UPDATE" query is a blocking query that blocks queries on the same table or the same row. Different blocking queries have different blocking scopes, including rows, tables, databases, etc. For example, DDL (data definition language, such as "CREATE", "ALTER") queries usually have a larger scope of blocking impact than DML (data manipulation language, such as "INSERT", "UPDATE") queries.

[0168] Therefore, in the process of building the dependency graph:

[0169] a. First, you need to maintain the mapping relationship between each query and its corresponding blocking scope (database / table). Here, "query" refers to the 5 lines of statements in the code example with a sequence, corresponding to Fig.10 SQL1, SQL2, SQL3, SQL4, and SQL5 in the table maintain the blocking range corresponding to each query, including table1, table1_state, and datebase, where table1 and table1_state represent part of the database, and datebase represents the entire database.

[0170] b. If the query is within a blocking range, add a dependency edge between the blocking query in the range and the current query to represent the dependency relationship. Fig.10 The "Dependencies" in the table show the dependency edges established based on blocking scope and execution order.

[0171] c. Continue to analyze. If the query to be analyzed is a blocking query, update Fig.10 The current blocking range in the figure below. Finally, the dependency graph from SQL1 to SQL5 is obtained, and the blocking range is marked.

[0172] As an optional embodiment, the execution plan of the request behavior pattern is optimized according to the dependency graph, and the optimized execution plan includes: setting the database query requests in the dependency subgraph of the same blocking range in the dependency graph to serial execution in the execution plan of the request behavior pattern; setting the database query requests in the dependency subgraphs of different blocking ranges in the dependency graph to parallel execution in the execution plan of the request behavior pattern; obtaining the optimized execution plan.

[0173] By parallelizing independently executed database query requests, the request behavior pattern is optimized and the execution speed and efficiency of the request behavior pattern are improved.

[0174] In the above example, we can build a dependency graph according to the above process in the order of statements. The simple dependency graph is as follows: Fig.10 In the figure below, the left and right subgraphs correspond to different blocking ranges (the left side corresponds to table1, and the right side corresponds to table1_state), and the edges between nodes are dependency edges. Statements that do not have true dependency relationships can be executed in parallel, as shown in Table 3, which is an execution optimization table.

[0175] Table 3 Execution optimization table

[0176]

[0177] At the same time, user-defined dependencies can be introduced into the parallel optimization strategy to supplement the constraint dependencies of the statement itself.

[0178] As an optional embodiment, the method also includes: displaying the execution plan before optimization in a first window; in response to the optimization operation, performing the step of optimizing the execution plan of the request behavior pattern according to the dependency graph to obtain an optimized execution plan; displaying the optimized execution plan in a second window, wherein, in the second window, parallel database query requests are displayed side by side, and serial database query requests are displayed column by column.

[0179] The determined request behavior pattern is displayed according to the needs, and the execution plan of the optimized request behavior pattern is displayed in response to user operations. Parallel database query requests are displayed side by side, and serial database query requests are displayed column by column, so as to intuitively show the optimization effect and the optimized execution plan.

[0180] This embodiment is designed to obtain the correct request behavior pattern mined and provide the user with a mode display interface required for operation and maintenance, such as Fig.11 As shown, Fig.11 Schematic diagram of a request behavior mode display page according to an embodiment of the invention. The above-mentioned mode display page is also the interface of the request behavior mode and the optimization suggestion system for parallel execution of the request behavior mode.

[0181] For the identified request behavior patterns, parallel execution optimization solutions are recommended based on the statement types. With the help of request behavior patterns mined from the query log corpus, a parallel optimization strategy is designed to proactively provide cloud database users with suggestions on how to optimize their logic code for better performance.

[0182] Fig.11 In the input box, the current request to be analyzed is entered. Based on the current request, the execution plan containing the request behavior pattern of the current request can be obtained by "analysis", which is displayed on the left. After that, the optimization strategy for the execution plan of the request behavior pattern on the left can be obtained by "optimization", that is, the optimized execution plan, which is displayed in the table on the right. In the recommended strategy displayed on the right, the statements in parallel horizontally can be executed in parallel, and the statements in parallel vertically can be executed serially.

[0183] It should be noted that, in order to reflect the technical effect of this embodiment, this embodiment also provides a test case, which is as follows: Case design: In order to evaluate the effectiveness of the optimization module based on SQL Pattern, 10 user cases were collected. Each user case is a code snippet containing a query. And these 10 cases are divided into two categories:

[0184] (i) There are cyclic situations;

[0185] (ii) There is no cycle.

[0186] For example, Case 1 and Case 2 are cases of a pattern with and without a cycle, respectively.

[0187] Case 1 contains loop:

[0188] for column in column_list:

[0189] if'SELECT instance_id FROM table2 WHERE instance_id=? ORDER BY{0}

[0190] LIMIT? '.format(column)

[0191] 'UPDATE table3 SET{0}=? WHERE instance_id=? '.format(column)

[0192] Case 2 without loop:

[0193] SELECT * FROM table4 LIMIT 1000

[0194] UPDATE table4 SET anomaly_feature='iii'WHERE instance_id=?

[0195] UPDATE table5 SET anomaly_feature='iii'WHERE instance_id=?

[0196] SELECT * FROM table4 LIMIT 1000

[0197] for column in column_list:

[0198] if'SELECT instance_id FROM table2 WHERE instance_id=? ORDER BY{0}

[0199] LIMIT? '.format(column)

[0200] 'UPDATE table3 SET{0}=? WHERE instance_id=? '.format(column)

[0201] SELECT * FROM table4 LIMIT 1000

[0202] UPDATE table4 SET anomaly_feature='iii'WHERE instance_id=?

[0203] UPDATE table5 SET anomaly_feature='iii'WHERE instance_id=?

[0204] SELECT * FROM table4 LIMIT 1000

[0205] First, multiple cases are applied to identify request behavior patterns (SQL Patterns). Then, for these request behavior patterns, the dependency graphs are analyzed accordingly and optimization strategies for parallel execution are generated.

[0206] Optimization test: Next, use the optimization strategy to improve the code. Run each original code and improved code 1,000 times, record two metrics under two execution scenarios: the average execution time of the request behavior pattern and the variance of the execution time, and test the performance improvement after parallel optimization.

[0207] Experimental results: As shown in Table 4, Table 4 is the experimental results data table.

[0208] Table 4 Experimental results data table

[0209]

[0210]

[0211] According to the results, the pattern-based optimization strategy significantly improves query efficiency, reducing the average execution time of each request behavior pattern (SQLPattern) to 1 / 2.7 of the original time. At the same time, it greatly reduces the variance of time and improves the stability and predictability of query execution. It can be seen that the optimization scheme has a more significant improvement effect on situations containing loops. The reason is that the more loops there are in the pattern, the greater the opportunity and space for parallel optimization.

[0212] The technical effect that can be achieved by this implementation is: it can cope with the SQL request scenario of massive interspersed services, cluster and split similar services through SQL execution trend and mine request behavior patterns under the premise of protecting user data security, and then analyze the dependency graph built based on the request behavior pattern to obtain a reusable parallel optimization solution at the service level.

[0213] A solution is proposed to cluster and split similar services based on SQL execution trends and mine request behavior patterns. There is no need to provide corresponding labels for services. This improves the accuracy of request behavior pattern mining while protecting user privacy and maintaining data security, while also improving time efficiency.

[0214] A solution for reusable parallel optimization analysis based on request behavior pattern-based dependency graph is also proposed. By building a dependency graph, the conflict relationship of each SQL request in the request behavior pattern is analyzed, and based on this, parallel execution optimization analysis is performed at the service SQL statement level rather than the engine level, providing users with reusable optimization suggestions based on request behavior pattern.

[0215] Fig.12 Schematic diagram of a data processing device for a database query request according to an embodiment of the present invention. Fig.12 As shown, based on the data processing method for the above-mentioned database query request provided by an embodiment of the present invention, this embodiment also provides a data processing device for database query request, including: an acquisition module 1201, a clustering module 1202, and a determination module 1203. The device is described in detail below.

[0216] An acquisition module 1201 is used to acquire multiple database query requests; a clustering module 1202 is connected to the acquisition module 1201 and is used to cluster the database query requests according to the execution logic similarity of the database query requests to obtain multiple clustering clusters, wherein each clustering cluster includes at least one database query request; a determination module 1203 is connected to the clustering module 1202 and is used to determine the request behavior pattern of the database query requests of each clustering cluster, wherein the request behavior pattern is the execution logic order of the database query requests in the corresponding clustering cluster.

[0217] The beneficial effects of the embodiments of the present invention are as follows: by determining the execution logic of massive database query requests, and clustering based on the execution logic to obtain cluster clusters, the request behavior patterns of the database query requests in each cluster cluster obtained by clustering are similar, and then determining the request behavior pattern for the database query request sequence in the cluster cluster as the request behavior pattern of the database query request in the cluster cluster, there is no need to provide a label for each database query request, nor is there a need to determine the request behavior model for the database query request one by one according to the label. Compared with the related art of identifying the request behavior pattern of the database query request one by one, not only is the accuracy of determining the request behavior pattern higher, but also it has higher efficiency and speed for massive database query requests, and solves the problem of low accuracy and long time consumption when identifying the request behavior pattern of the query request one by one in the related art when the number of database query requests is large.

[0218] As an optional embodiment, the acquisition module 1201 includes: multiple collectors deployed in the database, the collectors are used to collect the original data of database query requests within a preset time range; an acquisition unit, used to obtain the query log of the collected database query requests based on the original data, wherein the query log includes the original query text and execution data; a determination unit, used to determine multiple database query requests based on the query log.

[0219] As an optional embodiment, the clustering module 1202 includes: a determination unit, used to determine the execution logic graph of the corresponding database query request based on the execution data of each database query request; a clustering unit, used to cluster multiple database query requests according to the execution logic graph and use graph clustering to obtain cluster clusters.

[0220] As an optional embodiment, the determination unit includes: a receiving subunit, used to receive an input target query request; a query subunit, used to search for a database query request containing the target query request in a query log according to the target query request, and determine multiple database query requests.

[0221] As an optional embodiment, the determination module 1203 includes: a probability unit, which is used to determine the state transition probability between database query requests based on the database query requests in the cluster cluster using a relevant probability model, wherein the relevant probability model is a mathematical model for determining the state transition probability between any two database query requests; a pattern unit, which is used to determine the request behavior pattern of the cluster cluster based on the state transition probability and a preset probability threshold.

[0222] As an optional embodiment, the probability unit includes: a creation subunit, used to create prefix trees of multiple orders, wherein the multiple orders are multiple orders within a preset order range of the relevant probability model; a loss subunit, used to calculate the model losses of the relevant probability models of multiple orders respectively according to the prefix trees of multiple orders; a selection subunit, used to select the order with the smallest model loss as the target order of the relevant probability model; a model subunit, used to determine the relevant probability matrix of the database query request in the cluster cluster according to the relevant probability model of the target order, wherein the relevant probability matrix is ​​used to determine the state transition probability between the database query requests.

[0223] As an optional embodiment, the pattern unit includes: a behavior model subunit, used to take the first database query request as the first working behavior model, wherein the multiple database query requests in the cluster cluster form a request sequence according to a default execution order, and the first database query request is the first in the request sequence; the first query subunit adds the next database query request to the first behavior model when the state transition probability of executing the next database query request after the first database query request is greater than or equal to a preset probability threshold; the second query subunit is used to take the next database query request as the next behavior model when the state transition probability of executing the next database query request after the first database query request is less than a preset probability threshold; the first traversal subunit is used to traverse the database query requests of the request sequence according to the default execution order to obtain a request behavior pattern, wherein the request behavior pattern includes at least two behavior models.

[0224] As an optional embodiment, the device also includes: a construction module, which is used to construct a dependency graph based on the request behavior pattern and the dependency relationship of the corresponding database query request; an optimization module, which is used to optimize the execution plan of the request behavior pattern according to the dependency graph to obtain an optimized execution plan.

[0225] As an optional embodiment, the construction module includes: a dependency unit, which is used to determine the dependency relationship based on the execution data of the query log corresponding to each database query request in the request behavior pattern; a creation unit, which is used to create a dependency graph based on the dependency relationship and the blocking range of the target data block of each database query request, wherein the dependency graph includes dependency subgraphs of multiple blocking ranges, and the dependency subgraph is a dependency graph of database query requests under the same blocking range.

[0226] As an optional embodiment, the creation unit includes: a node subunit, which is used to select database query requests as nodes in a dependency graph according to the default execution order of each database query request; a first blocking subunit, which is used to add the node of the selected database query request to the dependency subgraph when a dependency subgraph already exists in the blocking range of the selected database query request, and generate a dependency edge, wherein the dependency edge points from the earlier database query request in the default order to the later database query request; a second blocking subunit, which is used to generate a new dependency subgraph when a dependency subgraph does not exist in the blocking range of the selected database query request, and use the node of the selected database query request as the first node of the dependency subgraph; and a second traversal subunit, which is used to traverse the database query requests of the cluster cluster according to the default execution order of the database query requests in the cluster cluster to obtain a dependency graph.

[0227] As an optional embodiment, the optimization module includes: a serial unit, which is used to set the database query requests in the dependency subgraph of the same blocking range in the dependency graph to serial execution in the execution plan of the request behavior pattern; a parallel unit, which is used to set the database query requests in the dependency subgraphs of different blocking ranges in the dependency graph to parallel execution in the execution plan of the request behavior pattern; and obtain an optimized execution plan.

[0228] As an optional embodiment, the device also includes: a first display module, used to display the execution plan before optimization in a first window; an operation module, used to respond to the optimization operation, execute the step of optimizing the execution plan of the request behavior pattern according to the dependency graph, and obtain the optimized execution plan; a second display module, used to display the optimized execution plan in a second window, wherein, in the second window, parallel database query requests are displayed in parallel, and serial database query requests are displayed column by column.

[0229] An embodiment of the present invention further provides an electronic device, comprising: at least one processor; and a memory in communication with the at least one processor. The memory stores a computer program executable by the at least one processor, and when the computer program is executed by the at least one processor, the electronic device executes the method of the embodiment of the present invention.

[0230] An embodiment of the present invention further provides a non-transitory machine-readable medium storing a computer program, wherein the computer program, when executed by a processor of a computer, is used to cause the computer to execute the method of the embodiment of the present invention.

[0231] An embodiment of the present invention further provides a computer program product, including a computer program, wherein the computer program, when executed by a processor of a computer, is used to enable the computer to execute the method of the embodiment of the present invention.

[0232] refer to Fig.13 , a block diagram of an electronic device that can be used as a server or client of an embodiment of the present invention will now be described, which is an example of a hardware device that can be applied to various aspects of the present invention. Electronic devices are intended to represent various forms of digital electronic computer devices, such as laptop computers, desktop computers, workbenches, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. Electronic devices can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices, and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or required herein.

[0233] like Fig.13As shown, the electronic device includes a computing unit 1301, which can store data stored in a read-only memory (ROM)

[0234] The computer program in the computer program 1302 or the computer program loaded from the storage unit 1308 into the random access memory (RAM) 1303 is used to perform various appropriate actions and processes. In the RAM 1303, various programs and data required for the operation of the electronic device can also be stored. The computing unit 1301, the ROM 1302 and the RAM 1303 are connected to each other via a bus 1304. An input / output (I / O) interface 1305 is also connected to the bus 1304.

[0235] Multiple components in the electronic device are connected to the I / O interface 1305, including: an input unit 1306, an output unit 1307, a storage unit 1308, and a communication unit 1309. The input unit 1306 can be any type of device that can input information to the electronic device, and the input unit 1306 can receive input digital or character information, and generate key signal input related to user settings and / or function control of the electronic device. The output unit 1307 can be any type of device that can present information, and can include but is not limited to a display, a speaker, a video / audio output terminal, a vibrator, and / or a printer. The storage unit 1308 can include but is not limited to a disk, an optical disk. The communication unit 1309 allows the electronic device to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks, and can include but is not limited to a modem, a network card, an infrared communication device, a wireless communication transceiver, and / or a chipset, such as a Bluetooth device, a WiFi device, a WiMax device, a cellular communication device, and / or the like.

[0236] The computing unit 1301 may be a variety of general and / or special processing components with processing and computing capabilities. Some examples of the computing unit 1301 include, but are not limited to, a CPU, a graphics processing unit (GPU), various dedicated artificial intelligence (AI) computing chips, various computing units running machine learning model algorithms, digital signal processors (DSPs), and any appropriate processors, controllers, microcontrollers, etc. The computing unit 1301 performs the various methods and processes described above. For example, in some embodiments, the method embodiments of the present invention may be implemented as a computer program, which is tangibly contained in a machine-readable medium, such as a storage unit 1308. In some embodiments, part or all of the computer program may be loaded and / or installed on an electronic device via a ROM 1302 and / or a communication unit 1309. In some embodiments, the computing unit 1301 may be configured to perform the above-described method in any other appropriate manner (e.g., by means of firmware).

[0237] The computer programs for implementing the methods of the embodiments of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor or controller of a general-purpose computer, a special-purpose computer, or other programmable data processing device, so that when the computer programs are executed by the processor or controller, the functions / operations specified in the flow chart and / or block diagram are implemented. The computer programs may be executed entirely on the machine, partially on the machine, partially on the machine as a stand-alone software package and partially on a remote machine, or entirely on a remote machine or server.

[0238] In the context of an embodiment of the present invention, a machine-readable medium may be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, device, or equipment. A machine-readable medium may be a machine-readable signal medium or a machine-readable storage medium. A machine-readable signal medium may include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, device, or equipment, or any suitable combination of the foregoing. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0239] It should be noted that the term "including" and its variations used in the embodiments of the present invention are open inclusions, that is, "including but not limited to". The term "based on" means "based at least in part on". The term "one embodiment" means "at least one embodiment"; the term "another embodiment" means "at least one other embodiment"; the term "some embodiments" means "at least some embodiments". The modifications of "one" and "multiple" mentioned in the embodiments of the present invention are illustrative rather than restrictive. Those skilled in the art should understand that unless the context clearly indicates otherwise, it should be understood as "one or more".

[0240] 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 the embodiments of the present invention 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 laws, regulations and standards of relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0241] The various steps described in the method implementation methods provided in the embodiments of the present invention may be performed in different orders and / or in parallel. In addition, the method implementation methods may include additional steps and / or omit the steps shown. The scope of protection of the present invention is not limited in this respect.

[0242] The term "embodiment" in this specification refers to specific features, structures or characteristics described in conjunction with the embodiment that can be included in at least one embodiment of the present invention. The appearance of this phrase in various places in the specification does not necessarily mean the same embodiment, nor does it mean that it is mutually exclusive with other embodiments and is independent or optional. The various embodiments in this specification are described in a related manner, and the same or similar parts between the various embodiments refer to each other. In particular, for the device, equipment, and system embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts refer to the partial description of the method embodiment.

[0243] The above-mentioned embodiments only express several implementation methods of the present invention, and the description thereof is relatively specific and detailed, but it cannot be understood as limiting the scope of patent protection. 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 invention, and these all belong to the scope of protection of the present invention. Therefore, the scope of protection of the present invention shall be subject to the attached claims.

Claims

1. A method for processing data of a database query request, comprising: Get multiple database query requests; Clustering the database query requests according to the execution logic of the database query requests to obtain clusters, wherein each cluster includes at least one database query request; A request behavior pattern of a database query request of each cluster is determined, wherein the request behavior pattern is a logical order of execution of the database query request in the corresponding cluster.

2. The method according to claim 1, wherein: Get multiple database query requests including: Collecting raw data of database query requests within a preset time range according to multiple collectors deployed in the database; Acquire a query log of the collected database query request according to the original data, wherein the query log includes the original query text and execution data; The multiple database query requests are determined according to the query log.

3. The method according to claim 2, wherein: According to the execution logic of the database query request, the database query request is clustered to obtain clusters including: Determine the execution logic diagram of the corresponding database query request according to the execution data of each database query request; According to the execution logic graph, a plurality of database query requests are clustered by using a graph clustering method to obtain cluster clusters.

4. The method according to claim 2, wherein: Determining, according to the query log, the plurality of database query requests comprises: Receiving an input target query request; According to the target query request, the database query request including the target query request is searched in the query log to determine the multiple database query requests.

5. The method according to claim 1, wherein: The request behavior patterns of database query requests that determine each cluster include: Determine the state transition probability between the database query requests according to the database query requests in the cluster using a related probability model, wherein the related probability model is a mathematical model for determining the state transition probability between any two database query requests; The request behavior pattern of the cluster is determined according to the state transition probability and a preset probability threshold.

6. The method according to claim 5, wherein: Determining the state transition probability between database query requests according to the database query requests in the cluster using a related probability model includes: Creating prefix trees of multiple orders, wherein the multiple orders are multiple orders within a preset order range of the relevant probability model; According to the prefix trees of multiple orders, respectively calculating the model losses of the relevant probability models of multiple orders; Select the order with the smallest model loss as the target order of the relevant probability model; A correlation probability matrix of database query requests in the cluster is determined according to the correlation probability model of the target order, wherein the correlation probability matrix is ​​used to determine the state transition probability between database query requests.

7. The method according to claim 5, wherein: Determining the request behavior pattern of the cluster according to the state transition probability and the preset probability threshold includes: Taking the first database query request as the first working behavior model, wherein the multiple database query requests in the cluster form a request sequence according to a default execution order, and the first database query request is the first one in the request sequence; When the state transition probability of executing the next database query request after the first database query request is greater than or equal to the preset probability threshold, adding the next database query request to the first behavior model; When the state transition probability of executing the next database query request after the first database query request is less than the preset probability threshold, taking the next database query request as the next behavior model; The database query requests of the request sequence are traversed according to the default execution order to obtain the request behavior pattern, wherein the request behavior pattern includes at least two behavior models.

8. The method according to claim 1, wherein: After determining the request behavior pattern of the database query request of each cluster, the method further includes: According to the request behavior pattern and the dependency relationship of the corresponding database query request, a dependency graph is constructed. The execution scheme of the request behavior pattern is optimized according to the dependency graph to obtain an optimized execution scheme.

9. The method according to claim 8, wherein: According to the request behavior pattern and the dependency relationship of the corresponding database query request, constructing a dependency graph includes: Determine the dependency relationship according to the execution data of the query log corresponding to each database query request in the request behavior pattern; A dependency graph is created based on the dependency relationship and the blocking range of the target data block of each database query request, wherein the dependency graph includes dependency subgraphs of multiple blocking ranges, and the dependency subgraphs are dependency graphs of database query requests under the same blocking range.

10. The method according to claim 9, wherein: According to the dependency relationship and the blocking range of the target data block of each database query request, creating a dependency graph includes: According to the default execution order of each database query request, select the database query request as a node in the dependency graph; If a dependency subgraph already exists in the blocking range of the selected database query request, add the node of the selected database query request to the dependency subgraph and generate a dependency edge, wherein the dependency edge points from the earlier database query request in the default order to the later database query request; In the case where there is no dependency subgraph in the blocking range of the selected database query request, a new dependency subgraph is generated, and the node of the selected database query request is used as the first node of the dependency subgraph; According to the default execution order of the database query requests in the clusters, the database query requests of the clusters are traversed to obtain the dependency graph.

11. The method according to claim 8, wherein: The execution scheme of the request behavior mode is optimized according to the dependency graph, and the optimized execution scheme includes: Setting the database query requests in the dependency subgraph of the same blocking range in the dependency graph to be executed serially in the execution scheme of the request behavior mode; Setting the database query requests in the dependency subgraphs of different blocking ranges in the dependency graph to be executed in parallel in the execution scheme of the request behavior mode; Get the optimized execution plan.

12. The method according to claim 11, wherein: The method further comprises: Displaying the execution plan of the request behavior mode before optimization in the first window; In response to the optimization operation, the step of optimizing the execution scheme of the request behavior pattern according to the dependency graph to obtain an optimized execution scheme is performed; The optimized execution plan is displayed in a second window, wherein in the second window, parallel database query requests are displayed in parallel, and serial database query requests are displayed column by column.

13. A data processing device for a database query request, comprising: An acquisition module, used to acquire multiple database query requests; A clustering module, configured to cluster the database query requests according to the execution logic of the database query requests to obtain clusters, wherein each cluster includes at least one database query request; The determination module is used to determine the request behavior pattern of the database query request of each cluster, wherein the request behavior pattern is the execution logic order of the database query request in the corresponding cluster.

14. An electronic device comprising: A processor, and a memory storing a program, wherein the program comprises instructions which, when executed by the processor, cause the processor to perform the method according to any one of claims 1 to 12.

15. A non-transitory machine-readable medium storing computer instructions, wherein: The computer instructions are used to cause the computer to execute the method according to any one of claims 1 to 12.

Images