SQL (Structured Query Language) slow query optimization method and device, electronic equipment and storage medium

Abstract

The invention provides an SQL (Structured Query Language) slow query optimization method and device, electronic equipment and a storage medium, the method comprises the following steps: obtaining a to-be-optimized SQL query statement, and generating an optimization suggestion by using a pre-trained target graph convolutional neural network, the network being pre-trained by the following steps: obtaining a target training data set comprising SQL graph structure data corresponding to historical SQL query statements, and inputting the SQL graph structure data into the graph neural network for training. The graph convolutional neural network is trained, whether the SQL query statement needs to be optimized or not is detected through the graph convolutional neural network, the optimized SQL query statement is output when the SQL query statement needs to be optimized, automatic optimization of SQL slow query is achieved, the SQL slow query optimization efficiency is improved, and meanwhile, the SQL slow query optimization efficiency is improved. In the training process, the graph convolutional neural network can fully learn the incidence relation between the SQL query statement and the query time, and therefore the optimized SQL query statement can be output more accurately.

Description

Technical Field

[0001] The present invention relates to the technical field of big data processing, and in particular, to a method, device, electronic device, and storage medium for optimizing slow SQL queries. Background Art

[0002] With the rapid development of information technology, database systems have become the core tools for enterprise data storage and retrieval. However, in practical applications, due to the continuous increase in data volume and the improvement of query complexity, the problem of slow SQL queries has become increasingly prominent. Among them, slow queries refer to SQL statements in MYSQL that record all executions exceeding the time threshold set by the long_query_time parameter.

[0003] Existing methods for optimizing slow SQL queries often rely on static rules and manual experience analysis, which are inefficient and error-prone. In addition, with the expansion of database scale and the diversification of query requirements, traditional optimization methods are difficult to adapt to these changes, resulting in ineffective improvement of query performance. Summary of the Invention

[0004] In view of this, embodiments of the present invention provide a method, device, electronic device, and storage medium for optimizing slow SQL queries to improve the efficiency and accuracy of slow SQL query optimization.

[0005] According to one aspect of the present invention, there is provided a method for optimizing slow SQL queries, the method comprising:

[0006] Obtaining a SQL query statement to be optimized:

[0007] Generating an optimization suggestion based on the SQL query statement to be optimized by using a pre-trained target graph convolutional neural network, wherein the optimization suggestion includes an optimized target SQL query statement; the target graph convolutional neural network is pre-trained through the following steps:

[0008] Obtaining a target training data set based on database query logs, where the target training data set includes SQL graph structure data corresponding to each historical SQL query statement; the SQL graph structure data includes element nodes and edges between the element nodes;

[0009] Inputting each piece of the SQL graph structure data into an initial graph neural network, so that the initial graph neural network outputs a corresponding optimized SQL query statement based on each piece of the SQL graph structure data;

[0010] Train the graph neural network based on the difference between the optimized query statement and the optimization label corresponding to the SQL graph structure data until the difference converges to obtain a target graph neural network, where the optimization label is the actually optimized SQL query statement corresponding to the SQL graph structure data.

[0011] In a possible embodiment, the obtaining of the target training data set based on the database query log includes:

[0012] Obtain an original data set through the database query log, where the original data set includes multiple historical SQL query statements;

[0013] Generate SQL graph structure data corresponding to each of the historical SQL query statements based on each element included in each of the historical SQL query statements and the association relationship between the elements. The elements at least include keywords, table names, and segment names included in the SQL query statements. The SQL graph structure data includes multiple nodes, each node corresponding to one of the elements, and the edges between the nodes are used to represent the index relationship between the nodes;

[0014] Correspondingly store the SQL graph structure data and the optimization label corresponding to the historical SQL query statement to obtain a target training data set.

[0015] In a possible embodiment, the obtaining of the original data set through the database query log includes:

[0016] Obtain historical SQL query data generated within a preset duration from the database query log at preset time intervals;

[0017] Filter the historical SQL query data according to a preset filtering rule to obtain filtered historical SQL query data, which constitutes the original data set, where the preset filtering rule includes eliminating historical SQL query data whose format does not conform to the preset SQL format.

[0018] In a possible embodiment, the graph convolutional neural network includes a classification sub-network and an optimization sub-network. The classification sub-network is used to determine whether a SQL query statement to be optimized needs to be optimized, and the optimization sub-network is used to output an optimized SQL query statement; the method further includes:

[0019] Perform positive and negative sample annotation on the SQL graph structure data in the target training data set;

[0020] Train the classification sub-network using a positive and negative sample training method based on the target training data set after positive and negative sample annotation;

[0021] Input the SQL graph structure data to be optimized into the optimization sub-network, so that the optimization sub-network extracts the features of the SQL graph structure data and outputs an optimized SQL query statement based on the features;

[0022] Train the optimization sub-network based on the difference between the optimized SQL query statement and the optimization label corresponding to the SQL graph structure data.

[0023] In a possible embodiment, the method further includes:

[0024] When obtaining the target SQL query statement output by the target graph convolutional neural network, run the target SQL query statement in the database;

[0025] Obtain the target execution time and target resource usage data of the target SQL query statement;

[0026] When both the target execution time and the target resource usage data are less than those of the SQL query statement to be optimized, determine the target SQL query statement as the optimization result of the SQL query statement to be optimized.

[0027] In a possible embodiment, the method further includes:

[0028] Send the target SQL query statement to the target client;

[0029] Based on the acceptance result of the target SQL query statement by the target client, add a positive feedback sample data label or a negative feedback sample data label to the target SQL query statement;

[0030] Add the marked target SQL query statement to the target training dataset to train the target graph convolutional neural network.

[0031] According to another aspect of the present invention, there is provided an SQL slow query optimization device, the device includes:

[0032] An acquisition module, configured to acquire an SQL query statement to be optimized:

[0033] An input module, configured to generate an optimization suggestion based on the SQL query statement to be optimized by using a pre-trained target graph convolutional neural network, where the optimization suggestion includes an optimized target SQL query statement;

[0034] A training module, configured to pre-train the target graph convolutional neural network through the following steps:

[0035] Obtain a target training dataset based on database query logs, where the target training dataset includes SQL graph structure data corresponding to each historical SQL query statement; the SQL graph structure data includes element nodes and edges between each of the element nodes;

[0036] Input each piece of the SQL graph structure data into an initial graph neural network, so that the initial graph neural network outputs a corresponding optimized SQL query statement based on each piece of the SQL graph structure data;

[0037] Train the graph neural network based on the difference between the optimized query statement and the optimized label corresponding to the SQL graph structure data until the difference converges to obtain a target graph neural network, where the optimized label is the actually optimized SQL query statement corresponding to the SQL graph structure data.

[0038] In a possible embodiment, the obtaining the target training dataset based on database query logs includes:

[0039] Obtain an original dataset through the database query logs, where the original dataset includes multiple historical SQL query statements;

[0040] Generate SQL graph structure data corresponding to each of the historical SQL query statements based on each element included in each of the historical SQL query statements and the association relationship between each of the elements. The elements at least include keywords, table names, and segment names included in the SQL query statements. The SQL graph structure data includes multiple nodes, each node corresponding to one of the elements, and the edges between each of the nodes are used to represent the index relationship between each of the nodes;

[0041] Correspondingly store the SQL graph structure data and the optimized label corresponding to the historical SQL query statement to obtain the target training dataset;

[0042] The obtaining the original dataset through the database query logs includes:

[0043] Obtain historical SQL query data generated within a preset duration from the database query logs at preset time intervals;

[0044] Filter the historical SQL query data according to a preset filtering rule to obtain filtered historical SQL query data, which constitutes the original dataset, where the preset filtering rule includes eliminating historical SQL query data whose format does not conform to the preset SQL format;

[0045] The graph convolutional neural network includes a classification sub-network and an optimization sub-network. The classification sub-network is used to determine whether the SQL query statement to be optimized needs to be optimized, and the optimization sub-network is used to output the optimized SQL query statement. The training module is used to perform positive and negative sample annotation on the SQL graph structure data in the target training dataset.

[0046] Based on the target training dataset after positive and negative sample annotation, use the positive and negative sample training method to train the classification sub-network.

[0047] Input the SQL graph structure data that needs to be optimized into the optimization sub-network, so that the optimization sub-network extracts the features of the SQL graph structure data and outputs the optimized SQL query statement based on this feature.

[0048] Train the optimization sub-network based on the difference between the optimized SQL query statement and the optimization label corresponding to the SQL graph structure data.

[0049] The device further includes a verification module, which is used to run the target SQL query statement in the database when the target SQL query statement output by the target graph convolutional neural network is obtained.

[0050] Obtain the target execution time and target resource usage data of the target SQL query statement.

[0051] When both the target execution time and the target resource usage data are less than those of the SQL query statement to be optimized, determine that the target SQL query statement is the optimization result of the SQL query statement to be optimized.

[0052] Send the target SQL query statement to the target client.

[0053] Based on the acceptance result of the target client for the target SQL query statement, add a positive feedback sample data label or a negative feedback sample data label to the target SQL query statement.

[0054] Add the marked target SQL query statement to the target training dataset for training the target graph convolutional neural network.

[0055] According to another aspect of the present invention, there is provided an electronic device, including:

[0056] A processor; and

[0057] A memory storing a program,

[0058] wherein the program includes instructions that, when executed by the processor, cause the processor to execute any one of the above-mentioned SQL slow query optimization methods.

[0059] According to another aspect of the present invention, there is provided a non-transitory computer-readable storage medium storing computer instructions, wherein the computer instructions are used to cause a computer to execute the SQL slow query optimization method described in any one of the above.

[0060] One or more technical solutions provided in the embodiments of the present invention pre-train a graph convolutional neural network, detect whether an SQL query statement needs to be optimized through the graph convolutional neural network, and output an optimized SQL query statement when the SQL query statement needs to be optimized, realizing the automatic optimization of SQL slow queries, improving the SQL slow query optimization efficiency. At the same time, during the training process, the graph convolutional neural network can fully learn the correlation between the SQL query statement and the query time, so as to more accurately output the optimized SQL query statement. BRIEF DESCRIPTION OF THE DRAWINGS

[0061] In the following description of exemplary embodiments with reference to the accompanying drawings, more details, features, and advantages of the present invention are disclosed. In the drawings:

[0062] Figure 1 is a schematic flowchart of a method for optimizing SQL slow queries provided by an embodiment of the present invention;

[0063] Figure 2 is a schematic flowchart of training a target graph convolutional neural network in the method for optimizing SQL slow queries provided by an embodiment of the present invention;

[0064] Figure 3 is a schematic diagram of graph structure data in the method for optimizing SQL slow queries provided by an embodiment of the present invention;

[0065] Figure 4 is another schematic flowchart of the method for optimizing SQL slow queries provided by an embodiment of the present invention;

[0066] Figure 5 is a schematic structural diagram of an SQL slow query optimization device provided by an embodiment of the present invention;

[0067] Figure 6 shows a structural block diagram of an exemplary electronic device capable of implementing the embodiments of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0068] Embodiments of the present invention will be described in more detail below with reference to the accompanying drawings. Although some embodiments of the present invention are shown in the drawings, it should be understood that the present invention can be implemented in various forms and should not be construed as limited to the embodiments set forth herein. On the contrary, these embodiments are provided to more thoroughly and completely understand the present invention. It should be understood that the drawings and embodiments of the present invention are only for exemplary purposes and are not used to limit the protection scope of the present invention.

[0069] It should be understood that the various steps recited in the method embodiments of the present invention can be executed in a different order and / or in parallel. In addition, the method embodiments may include additional steps and / or omit the steps shown. The scope of the present invention is not limited in this regard.

[0070] As used herein, the term "comprising" and its variations are open-ended, i.e., "including but not limited to". The term "based on" means "at least partially based on". The term "one embodiment" means "at least one embodiment"; the term "another embodiment" means "at least one additional embodiment"; the term "some embodiments" means "at least some embodiments". The relevant definitions of other terms will be given in the following description. It should be noted that the concepts such as "first", "second", etc. mentioned in the present invention are only used to distinguish different devices, modules or units, and are not used to limit the order of functions performed by these devices, modules or units or their interdependent relationships.

[0071] It should be noted that the modifications of "one" and "multiple" mentioned in the present invention are illustrative rather than restrictive. Those skilled in the art should understand that unless otherwise clearly specified in the context, it should be understood as "one or more".

[0072] The names of the messages or information exchanged between multiple devices in the embodiments of the present invention are only for illustrative purposes and are not used to limit the scope of these messages or information.

[0073] In the existing methods, there are the following main deficiencies in the optimization of SQL slow queries:

[0074] Strong manual dependence: Traditional SQL optimization methods usually rely on the experience of database administrators or developers, and they need to manually analyze and adjust the query statements. This method is inefficient and prone to unstable optimization effects due to human factors.

[0075] Lack of a global perspective: Existing methods often only focus on the local optimization of query statements and lack an understanding of the global structure of the query graph. Therefore, it is difficult for them to discover the performance bottlenecks hidden in complex query graphs.

[0076] Limited pattern recognition ability: Traditional optimization methods usually perform pattern recognition based on fixed rules and heuristic algorithms. This method has limited pattern recognition ability and is difficult to handle complex and changing query scenarios.

[0077] Unable to adapt to data changes: As the amount of data in the database grows and the structure changes, the influencing factors of query performance will also change. Existing methods often cannot adapt to these changes in real time, resulting in a gradual weakening of the optimization effect.

[0078] Lack of automation and intelligence: Existing methods lack an automated and intelligent optimization mechanism and cannot automatically monitor, diagnose, and optimize slow SQL queries. This increases the maintenance cost and reduces the reliability and efficiency of the system.

[0079] Based on this, the embodiments of the present invention provide a method, device, electronic device, and storage medium for optimizing slow SQL queries. The method for optimizing slow SQL queries provided by the embodiments of the present invention can be applied to any electronic device with the function of optimizing slow SQL queries. The electronic device can be a computer, a server, or a terminal device, etc. The solution of the present invention is described below with reference to the accompanying drawings:

[0080] Figure 1 FIG. is a schematic flowchart of a method for optimizing slow SQL queries provided by an embodiment of the present invention, which may include the following steps:

[0081] S101. Obtain the SQL query statement to be optimized:

[0082] S102. Use the pre-trained target graph convolutional neural network to generate optimization suggestions based on the SQL query statement to be optimized, where the optimization suggestions include the optimized target SQL query statement.

[0083] As Figure 2 shown, the above-mentioned target graph convolutional neural network can be pre-trained through the following steps:

[0084] S201. Obtain a target training data set based on the database query log, where the target training data set includes SQL graph structure data corresponding to each historical SQL query statement; the SQL graph structure data includes element nodes and edges between the element nodes;

[0085] S202. Input each piece of the SQL graph structure data into the initial graph neural network, so that the initial graph neural network outputs the corresponding optimized SQL query statement based on each piece of the SQL graph structure data;

[0086] S203. Train the graph neural network based on the difference between the optimized query statement and the optimized label corresponding to the SQL graph structure data until the difference converges, so as to obtain a target graph neural network, where the optimized label is the actually optimized SQL query statement corresponding to the SQL graph structure data.

[0087] Applying the embodiments of the present invention, by pre-training a graph convolutional neural network, detecting whether an SQL query statement needs to be optimized through the graph convolutional neural network, and outputting an optimized SQL query statement when the SQL query statement needs to be optimized, the automatic optimization of SQL slow queries is realized, the optimization efficiency of SQL slow queries is improved. At the same time, during the training process, the graph convolutional neural network can fully learn the correlation between the SQL query statement and the query time, so as to more accurately output the optimized SQL query statement.

[0088] The following is an exemplary description of the above S101-S102:

[0089] In S101, the SQL query statement to be optimized can be each SQL query statement received by the database. As a possible implementation manner, each SQL query statement received by the database within a preset time period can be obtained from the database query log according to the preset time period as the SQL query statement to be optimized. Exemplarily, the database query log data can be extracted every five minutes, and the SQL query statement can be extracted from the database query log data as the SQL query statement to be optimized.

[0090] In practical applications, Kafka is used for data processing. Kafka is a high-throughput distributed publish-subscribe messaging system that can process all action flow data of consumers on the website. As a possible implementation manner, the data of the database query log collection module can be subscribed through Kafka, and the database query log data can be obtained through Kafka according to the preset time period. The database query log data can include the content of each SQL query data, the corresponding query result, query time, etc.

[0091] The above data query log prints the query records for the database as text and stores the query records through the text. However, during the printing process of the data query log, the record printing may be incomplete. For example, the SQL query statement may be incompletely printed. In a possible embodiment, the obtained database query log data can be filtered according to a preset filtering rule, and then the SQL query statement to be optimized can be obtained. The filtering rule can include incomplete statement filtering and irregular statement filtering, etc. Among them, the incomplete statement filtering is used to filter the database query log data with incomplete printing, and the irregular statement filtering is used to filter the SQL query statements whose formats do not conform to the preset format.

[0092] Exemplarily, the preset data types included in the database query log data can be preset in advance, and the obtained database query log data is matched with the preset data types. If all the preset data types are successfully matched, it indicates that the database query log data of this record is complete. For another example, the regular matching formula corresponding to the standard format can be preset in advance, and the SQL query statements included in each database query log are matched through the regular matching formula. If the matching is successful, it indicates that the format of the SQL query statement conforms to the standard format. Therefore, this SQL query statement can be retained.

[0093] After obtaining the SQL query statement to be optimized, the SQL query statement to be optimized can be input into a pre-trained graph convolutional neural network, and the graph convolutional neural network further analyzes and judges the SQL query statement to be optimized. The graph convolutional neural network can be pre-trained through the above steps S201-S203. The following is an exemplary description of the above S201-S203:

[0094] The target training data set described in S201 may include multiple SQL query data. The SQL query data can be historical SQL query data obtained from the database query log or SQL query data obtained through an open-source data set. As a possible way, all records can be pulled from the database query log and all SQL query data can be extracted to form the target training data set. In a possible embodiment, the database query log within a preset time period can be pulled at a preset time interval for training the graph convolutional neural network. The above time interval can be set according to the actual application scenario, such as 30 days, 7 days, etc. Exemplarily, the query records generated within 7 days can be pulled every 7 days to obtain the corresponding historical SQL query statements, forming the target training data set.

[0095] In a possible embodiment, after obtaining the above historical SQL query statement, dirty data filtering can be performed on the historical SQL query statement according to a preset filtering rule. The dirty data filtering process is the same as the process of filtering to obtain the SQL query statement to be optimized, which will not be elaborated here.

[0096] In a possible embodiment, each historical SQL query statement can be converted into SQL graph structure data according to a preset conversion rule. Exemplarily, information such as preset keywords, table names, and field names in the SQL query statement can be extracted, and based on the extracted information and the association relationships between the tables and fields corresponding to each table name and field name, the SQL graph structure data corresponding to the historical SQL query statement can be constructed.

[0097] As described above, after obtaining the historical SQL query data, dirty data filtering will be performed on the historical SQL query data. Therefore, the filtered historical SQL query data are all data that conform to the preset format. Therefore, information at the corresponding positions in the historical SQL query statement can be extracted based on the positions of the keywords, table names, and field names in the preset format, and the keywords, table names, and field names included in the historical SQL query statement can be obtained. In a possible embodiment, a keyword table can be preset, and the historical SQL query statement can be matched based on the keyword table to obtain the keywords included in the historical SQL query statement. The above keywords are reserved words in the SQL language, such as SELECT, FROM, WHERE, JOIN, etc. These keywords can indicate information such as the query type, the source of the data to be queried, and sorting in the SQL query statement. Table names usually appear after keywords such as FROM, JOIN, UPDATE, DELETE, etc., and field names appear in the SELECT list or in clauses such as WHERE, GROUP BY, ORDER BY, etc. Correspondingly, as a possible implementation method, the text that appears after the corresponding keyword can be extracted to obtain the above table name and field name information.

[0098] In a possible embodiment, the extracted elements can be used as nodes, and edges can be added between the nodes based on the index relationships, query relationships, etc. between the elements. The above index relationship refers to the index relationship between different tables and fields, and the above query relationship refers to the query relationship between keywords and the corresponding tables and fields.

[0099] In a possible embodiment, corresponding node attributes and edge attributes can be set for the nodes and edges. The node attributes can include a node identifier, which can be a name, a code, etc. The edge attributes can include the type of association relationship between the nodes.

[0100] Correspondingly store the SQL graph structure data and the optimization labels corresponding to the historical SQL query statements to obtain a target training data set.

[0101] In a possible embodiment, positive and negative sample labeling can be performed on each piece of training data in the target training data set. Among them, positive samples can be SQL query statements that do not require optimization, and negative samples are SQL query data that requires optimization. It is also possible to use SQL query statements that require optimization as positive samples and SQL query statements that do not require optimization as negative samples. The present invention does not make specific limitations in this regard.

[0102] After that, an initial graph convolutional neural network can be used to perform model training based on the SQL graph structure data corresponding to each historical SQL query statement. In a possible embodiment, the above-mentioned SQL graph structure data can correspond to query time tags, which are used to identify the query time corresponding to the historical SQL query statement. Input the above-mentioned SQL graph structure data into the initial graph convolutional neural network, and the graph convolutional neural network learns the features of each element and the relationships between elements, and then outputs an optimized SQL query statement based on this feature. After that, the graph convolutional neural network can be trained based on the difference between the optimized SQL query statement and the query time of the training data. Exemplarily, the parameters of the graph convolutional neural network can be adjusted by methods such as gradient descent method and particle swarm algorithm based on this difference until the difference converges.

[0103] In a possible embodiment, the label corresponding to the above-mentioned training data can also be the actually optimized SQL query statement. Correspondingly, the graph convolutional neural network can be trained based on the difference between the optimized SQL query statement output by the graph convolutional neural network and the actually optimized SQL query statement until a target graph convolutional neural network is obtained.

[0104] In a possible embodiment, the graph convolutional neural network includes a classification sub-network and an optimization sub-network. Among them, the classification sub-network is used to determine whether a SQL query statement to be optimized needs to be optimized, and the optimization sub-network is used to output an optimized SQL query statement. The above-mentioned classification sub-network can be trained through a positive and negative sample training method. The optimization sub-network can input the SQL graph structure data that needs to be optimized into the optimization sub-network, so that the optimization sub-network extracts the features of the SQL graph structure data and outputs an optimized SQL query statement based on this feature;

[0105] Train the optimization sub-network based on the difference between the optimized SQL query statement and the optimization label corresponding to the SQL graph structure data.

[0106] In a possible embodiment, such as Figure 3As shown, a graph can be constructed based on each SQL query statement. Each node is a set of elements included in each training data, and the edges between nodes are the set of edges in each training data. Figure 3 Among them, X1 - X6 are each node in the graph convolutional neural network, representing the field names of each data table; X(i,j) is the edge feature between nodes Xi and Xj, representing the feature relationship between the corresponding two fields in the data table in the query SQL. Hi is the learning objective of the graph convolutional neural network, representing the hidden state of the graph perception of the corresponding node, that is, the information value containing information from neighboring nodes. The f() function is the state update function of the hidden state, which is the function fitted during the learning process of this graph convolutional neural network model. The above hidden state can be obtained based on the nodes, the edges related to the nodes, and the hidden function of the nodes. Exemplarily, the hidden state h5 of node X5 = f(x5, x(3,5), X(5,6), h3, h6, x3, x6).

[0107] After obtaining the target graph convolutional neural network, the target graph convolutional neural network can be stored in the database. Exemplarily, parameters such as nodes, edges, and hidden states in the graph convolutional neural network can be stored. In an actual application scenario, the target graph convolutional neural network can be loaded, and based on the output to - be - optimized SQL query statement, it can be determined whether the to - be - optimized SQL query statement needs to be optimized, and the corresponding optimized SQL query statement can be output.

[0108] In a possible embodiment, after obtaining the optimized SQL query statement, the optimized SQL query statement can be executed in the database, and data such as the response time and resource consumption of the optimized SQL query statement can be obtained. The resource consumption data can be the CPU resources, memory resources, etc. consumed by executing the optimized SQL query statement. If both the response time and the resource consumption data are less than those of the to - be - optimized SQL query statement, it can be determined that the optimized SQL query statement is the target optimized SQL query statement.

[0109] In a possible embodiment, the optimized SQL query statement output by the target graph convolutional neural network can be sent to the target client. The target client can be preset for the to - be - optimized SQL query statement. Relevant personnel can determine whether to use the optimized SQL query statement as the optimization result of the to - be - optimized SQL query statement based on the optimized SQL query statement displayed by the target client, that is, whether to use the optimized SQL query statement as the target optimized SQL query statement.

[0110] In a possible embodiment, the output results can be classified into positive feedback sample data and negative feedback sample data according to the acceptance result of the optimized SQL query statement output by the target graph convolutional neural network. Exemplarily, the accepted optimized SQL query statement can be used as positive feedback sample data, and the unaccepted optimized SQL query statement can be used as negative feedback sample data. The positive feedback sample data and the negative feedback sample data can be added to the target training dataset to update the GCN model in a timely manner based on the target training dataset, and feedback to the model for adjustment according to the actual execution result, so as to achieve adaptive optimization of SQL queries.

[0111] As Figure 4 shown, Figure 4 is another flowchart of the SQL slow query optimization method provided by the embodiment of the present invention, which can include the following stages:

[0112] 1. SQL log data collection stage: Subscribe to the data of the database query log collection module through kafka to obtain query log data, which includes SQL query statements and corresponding response times, query results, etc.

[0113] 2. Regular offline training stage, which specifically includes the following steps:

[0114] Regularly pull query log data as training data.

[0115] Data preprocessing. Specifically, the training data can be filtered to remove dirty data, so as to obtain filtered training data.

[0116] Training data annotation. Specifically, each element included in the training data can be extracted as a graph node, and edges can be added between the corresponding graph nodes according to the index relationship, query relationship, etc. between the elements, so as to obtain the graph structure data corresponding to the training data. At the same time, positive and negative samples can be labeled for each training data in this step. Specifically, the SQL query statement that needs to be optimized is labeled as a negative sample, and the SQL query statement that does not need to be optimized is labeled as a positive sample.

[0117] Graph convolutional neural network model training: The graph convolutional neural network model includes a classification sub-network and an optimization sub-network. The above classification sub-network can be trained by the positive and negative sample training method through the above positive and negative samples. The above optimization sub-network can input the graph structure data corresponding to the training data into the initial graph convolutional neural network model, extract the features of each graph structure data through the graph convolutional neural network model, and determine the optimized SQL query statement corresponding to the training data based on the features. The optimization sub-network is trained based on the difference between the query time of the optimized SQL query statement and the query time label corresponding to the training data.

[0118] After the above-mentioned graph convolutional neural network model is trained, the target graph convolutional neural network is obtained.

[0119] 3. Real-time slow query optimization plan recommendation stage: It includes using the target graph convolutional neural network to determine whether the real-time SQL query statement is a potential slow query SQL. If so, query optimization suggestions are generated. The query optimization suggestions can include the optimized SQL query statement.

[0120] 4. Adaptive dynamic optimization stage: Execute the optimized SQL query statement in the database. By comparing indicators such as execution time and resource consumption, verify the optimization effect. If the verification passes, accept the optimization result. According to whether the optimized SQL statement is accepted, the data is divided into positive feedback and negative feedback sample data, and the data is preprocessed. Inject the positive feedback sample data and the negative feedback sample data into the labeled samples newly, update the GCN model in time, and feedback to the model according to the actual execution results for adjustment to achieve adaptive optimization of SQL queries.

[0121] Applying the embodiments of the present invention, converting the SQL query statement into a graph representation and using the graph convolutional neural network for feature extraction and pattern recognition not only applies the deep learning method to the field of SQL query optimization, but also better captures the complex structures and correlation relationships in the query statement through the graph representation method.

[0122] Furthermore, by training the graph convolutional neural network model, it can learn the performance bottlenecks and pattern features in the SQL query statement, and can automatically identify potential slow query statements, generate optimization suggestions or optimized SQL query statements according to the features and pattern information extracted by the model, and perform positive and negative feedback annotation on the optimized SQL query statement, dynamically increase the training data, and achieve adaptive SQL query optimization.

[0123] Compared with the traditional manual analysis and empirical judgment methods, the present invention utilizes the powerful learning ability of the graph convolutional neural network to be able to identify and optimize slow query statements faster and more accurately, improving the efficiency and accuracy of database query performance optimization.

[0124] Based on the same inventive concept, the embodiments of the present invention also provide an SQL slow query optimization device, as Figure 5 shown. The device 500 may include:

[0125] An acquisition module 501 for acquiring the SQL query statement to be optimized:

[0126] An input module 502 for generating optimization suggestions based on the SQL query statement to be optimized by using a pre-trained target graph convolutional neural network, where the optimization suggestions include the optimized target SQL query statement;

[0127] A training module 503 for pre-training the target graph convolutional neural network through the following steps:

[0128] Obtain a target training dataset based on the database query log, where the target training dataset includes SQL graph structure data corresponding to each historical SQL query statement; the SQL graph structure data includes element nodes and edges between each of the element nodes;

[0129] Input each piece of the SQL graph structure data into an initial graph neural network, so that the initial graph neural network outputs a corresponding optimized SQL query statement based on each piece of the SQL graph structure data;

[0130] Train the graph neural network based on the difference between the optimized query statement and the optimized label corresponding to the SQL graph structure data until the difference converges to obtain the target graph neural network, where the optimized label is the actually optimized SQL query statement corresponding to the SQL graph structure data.

[0131] In a possible embodiment, the obtaining the target training dataset based on the database query log includes:

[0132] Obtain an original dataset through the database query log, where the original dataset includes multiple historical SQL query statements;

[0133] Generate SQL graph structure data corresponding to each historical SQL query statement based on each element included in each historical SQL query statement and the association relationship between each of the elements, where the elements at least include keywords, table names, and segment names included in the SQL query statement, and the SQL graph structure data includes multiple nodes, each node corresponding to one of the elements, and the edges between each of the nodes are used to represent the index relationship between each of the nodes;

[0134] Correspondingly store the SQL graph structure data and the optimized label corresponding to the historical SQL query statement to obtain the target training dataset;

[0135] The obtaining the original dataset through the database query log includes:

[0136] Obtain historical SQL query data generated within a preset duration from the database query log at preset time intervals;

[0137] Filter the historical SQL query data according to a preset filtering rule to obtain the filtered historical SQL query data, which constitutes the original dataset, where the preset filtering rule includes eliminating historical SQL query data with a format that does not conform to the preset SQL format;

[0138] The graph convolutional neural network includes a classification sub-network and an optimization sub-network. The classification sub-network is used to determine whether the SQL query statement to be optimized needs to be optimized, and the optimization sub-network is used to output the optimized SQL query statement. The training module is used to perform positive and negative sample annotation on the SQL graph structure data in the target training dataset.

[0139] Based on the target training dataset after positive and negative sample annotation, use the positive and negative sample training method to train the classification sub-network.

[0140] Input the SQL graph structure data that needs to be optimized into the optimization sub-network, so that the optimization sub-network extracts the features of the SQL graph structure data and outputs the optimized SQL query statement based on the features.

[0141] Train the optimization sub-network based on the difference between the optimized SQL query statement and the optimization label corresponding to the SQL graph structure data.

[0142] The device further includes a verification module, which is used to run the target SQL query statement in the database when the target SQL query statement output by the target graph convolutional neural network is obtained.

[0143] Obtain the target execution time and target resource usage data of the target SQL query statement.

[0144] When both the target execution time and the target resource usage data are less than those of the SQL query statement to be optimized, determine that the target SQL query statement is the optimization result of the SQL query statement to be optimized.

[0145] Send the target SQL query statement to the target client.

[0146] Based on the acceptance result of the target SQL query statement by the target client, add a positive feedback sample data label or a negative feedback sample data label to the target SQL query statement.

[0147] Add the marked target SQL query statement to the target training dataset for training the target graph convolutional neural network.

[0148] Among them, the collection, storage, use, processing, transmission, provision, and disclosure of user personal information involved in the present invention all comply with the provisions of relevant laws and regulations and do not violate public order and good customs.

[0149] An exemplary embodiment of the present invention further provides an electronic device, including: at least one processor; and a memory communicatively connected to 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, it is configured to cause the electronic device to execute the method according to the embodiment of the present invention.

[0150] An exemplary embodiment of the present invention further provides a non-transitory computer-readable storage medium storing a computer program, wherein when the computer program is executed by a processor of a computer, it is configured to cause the computer to execute the method according to the embodiment of the present invention.

[0151] An exemplary embodiment of the present invention further provides a computer program product, including a computer program, wherein when the computer program is executed by a processor of a computer, it is configured to cause the computer to execute the method according to the embodiment of the present invention.

[0152] Referring Figure 6 , a block diagram of an electronic device 600 that can be a server or a client of the present invention will now be described. It is an example of a hardware device applicable to various aspects of the present invention. The electronic device is intended to represent various forms of digital electronic computer devices, such as, a laptop computer, a desktop computer, a workbench, a personal digital assistant, a server, a blade server, a mainframe computer, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as, a personal digital processor, a cellular phone, a smart phone, a wearable device, 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 claimed herein.

[0153] As Figure 6 shown, the electronic device 600 includes a computing unit 601, which can execute various appropriate actions and processes according to a computer program stored in a read-only memory (ROM) 602 or a computer program loaded from a storage unit 608 into a random access memory (RAM) 603. In the RAM 603, various programs and data required for the operation of the electronic device 600 can also be stored. The computing unit 601, the ROM 602, and the RAM 603 are connected to each other through a bus 604. An input / output (I / O) interface 605 is also connected to the bus 604.

[0154] Multiple components in the electronic device 600 are connected to the I / O interface 605, including: an input unit 606, an output unit 607, a storage unit 608, and a communication unit 609. The input unit 606 can be any type of device capable of inputting information into the electronic device 600. The input unit 606 can receive input digital or character information and generate key signal inputs related to the user settings and / or function controls of the electronic device. The output unit 607 can be any type of device capable of presenting 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 608 can include, but is not limited to, magnetic disks and optical discs. The communication unit 609 allows the electronic device 600 to exchange information / data with other devices via 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 BluetoothTM device, a WiFi device, a WiMax device, a cellular communication device, and / or the like.

[0155] The computing unit 601 can be various general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of the computing unit 601 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various dedicated artificial intelligence (AI) computing chips, various computing units running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. The computing unit 601 executes the various methods and processes described above. For example, in some embodiments, any of the SQL slow query optimization methods described above can be implemented as a computer software program tangibly embodied in a machine-readable medium, such as the storage unit 608. In some embodiments, part or all of the computer program can be loaded and / or installed onto the electronic device 600 via the ROM 602 and / or the communication unit 609. In some embodiments, the computing unit 601 can be configured to execute any of the SQL slow query optimization methods described above in any other suitable manner (e.g., by means of firmware).

[0156] The program code for implementing the method of the present invention can be written in any combination of one or more programming languages. These program codes can be provided to a processor or controller of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when the program codes are executed by the processor or controller, the functions / operations specified in the flowchart and / or block diagram are implemented. The program code can be executed entirely on the machine, partially on the machine, executed partially on the machine as an independent software package and partially on a remote machine, or executed entirely on a remote machine or server.

[0157] In the context of the present invention, a machine-readable medium can be a tangible medium that can contain or store a program for use by or in connection with an instruction execution system, apparatus, or device. A machine-readable medium can be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium can include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. More specific examples of a machine-readable storage medium would include an electrical connection based on one or more wires, a portable computer diskette, 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 disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0158] As used in the present invention, the terms "machine-readable medium" and "computer-readable medium" refer to any computer program product, apparatus, and / or device (e.g., a disk, optical disk, memory, programmable logic device (PLD)) used to provide machine instructions and / or data to a programmable processor, including a machine-readable medium that receives machine instructions as a machine-readable signal. The term "machine-readable signal" refers to any signal used to provide machine instructions and / or data to a programmable processor.

[0159] In order to provide interaction with a user, the systems and techniques described herein can be implemented on a computer having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and a pointing device (e.g., a mouse or a trackball) by which the user can provide input to the computer. Other kinds of devices can also be used to provide interaction with the user; for example, the feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic input, voice input, or tactile input).

[0160] The systems and techniques described herein can be implemented in a computing system that includes back-end components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes front-end components (e.g., a user computer having a graphical user interface or a web browser through which a user can interact with an implementation of the systems and techniques described herein), or a computing system that includes any combination of such back-end, middleware, or front-end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: local area network (LAN), wide area network (WAN), and the Internet.

[0161] A computer system can include clients and servers. Clients and servers are generally remote from each other and typically interact through a communication network. The client-server relationship is created by computer programs that run on the respective computers and have a client-server relationship with each other.

Claims

1. A method for optimizing SQL slow queries, characterized in that: The method comprises: Get the SQL query statement to be optimized: Generate optimization suggestions based on the SQL query statement to be optimized using a pre-trained target graph convolutional neural network, wherein the optimization suggestions include the optimized target SQL query statement; the target graph convolutional neural network is pre-trained by the following steps: Acquire a target training data set based on a database query log, wherein the target training data set includes SQL graph structure data corresponding to each historical SQL query statement; the SQL graph structure data includes element nodes and edges between the element nodes; Input each piece of the SQL graph structure data into the initial graph neural network, so that the initial graph neural network outputs a corresponding optimized SQL query statement based on each piece of the SQL graph structure data; The graph neural network is trained based on the difference between the optimized query statement and the optimization label corresponding to the SQL graph structure data until the difference converges to obtain a target graph neural network, wherein the optimization label is the actual optimized SQL query statement corresponding to the SQL graph structure data.

2. The method according to claim 1, characterized in that The step of obtaining a target training data set based on a database query log includes: Acquire an original data set through the database query log, wherein the original data set includes a plurality of historical SQL query statements; Based on the elements contained in each of the historical SQL query statements and the association relationship between the elements, generate SQL graph structure data corresponding to each of the historical SQL query statements, wherein the elements at least include keywords, table names, and segment names contained in the SQL query statements, and the SQL graph structure data includes a plurality of nodes, each of which corresponds to one of the elements, and the edges between the nodes are used to represent the index relationship between the nodes; The SQL graph structure data and the optimization labels corresponding to the historical SQL query statements are stored correspondingly to obtain a target training data set.

3. The method according to claim 2, characterized in that The obtaining of the original data set by querying the database log includes: Obtaining historical SQL query data generated within a preset time period from the database query log at a preset time interval; The historical SQL query data is filtered according to a preset filtering rule to obtain filtered historical SQL query data to constitute the original data set, wherein the preset filtering rule includes eliminating historical SQL query data whose format does not conform to a preset SQL format.

4. The method according to claim 1, characterized in that: The graph convolutional neural network includes a classification subnetwork and an optimization subnetwork, wherein the classification subnetwork is used to determine whether the SQL query statement to be optimized needs to be optimized, and the optimization subnetwork is used to output the optimized SQL query statement; the method further includes: Annotating the SQL graph structure data in the target training data set with positive and negative samples; Based on the target training data set after positive and negative sample annotation, the classification sub-network is trained using a positive and negative sample training method; Inputting the SQL graph structure data to be optimized into the optimization sub-network, so that the optimization sub-network extracts the features of the SQL graph structure data and outputs the optimized SQL query statement based on the features; The optimization sub-network is trained based on the difference between the optimized SQL query statement and the optimization label corresponding to the SQL graph structure data.

5. The method according to claim 1, characterized in that The method further comprises: When a target SQL query statement output by the target graph convolutional neural network is obtained, running the target SQL query statement in a database; Obtaining the target execution time and target resource usage data of the target SQL query statement; When the target execution time and the target resource usage data are both smaller than the SQL query statement to be optimized, it is determined that the target SQL query statement is the optimization result of the SQL query statement to be optimized.

6. The method according to claim 5, characterized in that The method further comprises: Sending the target SQL query statement to the target client; Based on the acceptance result of the target SQL query statement by the target client, adding a positive feedback sample data label or a negative feedback sample data label to the target SQL query statement; The marked target SQL query statement is added to the target training data set to train the target graph convolutional neural network.

7. A SQL slow query optimization device, characterized in that: The device comprises: Acquisition module, used to obtain the SQL query statement to be optimized: An input module, configured to generate optimization suggestions based on the SQL query statement to be optimized using a pre-trained target graph convolutional neural network, wherein the optimization suggestions include the optimized target SQL query statement; The training module is used to pre-train the target graph convolutional neural network through the following steps: Acquire a target training data set based on a database query log, wherein the target training data set includes SQL graph structure data corresponding to each historical SQL query statement; the SQL graph structure data includes element nodes and edges between the element nodes; Input each piece of the SQL graph structure data into the initial graph neural network, so that the initial graph neural network outputs a corresponding optimized SQL query statement based on each piece of the SQL graph structure data; The graph neural network is trained based on the difference between the optimized query statement and the optimization label corresponding to the SQL graph structure data until the difference converges to obtain a target graph neural network, wherein the optimization label is the actual optimized SQL query statement corresponding to the SQL graph structure data.

8. The device according to claim 7, characterized in that The step of obtaining a target training data set based on a database query log includes: Acquire an original data set through the database query log, wherein the original data set includes a plurality of historical SQL query statements; Based on the elements contained in each of the historical SQL query statements and the association relationship between the elements, generate SQL graph structure data corresponding to each of the historical SQL query statements, wherein the elements at least include keywords, table names, and segment names contained in the SQL query statements, and the SQL graph structure data includes a plurality of nodes, each of which corresponds to one of the elements, and the edges between the nodes are used to represent the index relationship between the nodes; The SQL graph structure data and the optimization labels corresponding to the historical SQL query statements are stored in correspondence to obtain a target training data set; The obtaining of the original data set by querying the database log includes: Obtaining historical SQL query data generated within a preset time period from the database query log at a preset time interval; Filter the historical SQL query data according to a preset filtering rule to obtain filtered historical SQL query data to form the original data set, wherein the preset filtering rule includes eliminating historical SQL query data whose format does not conform to a preset SQL format; The graph convolutional neural network includes a classification subnetwork and an optimization subnetwork, wherein the classification subnetwork is used to determine whether the SQL query statement to be optimized needs to be optimized, and the optimization subnetwork is used to output the optimized SQL query statement; the training module is used to annotate the SQL graph structure data in the target training data set with positive and negative samples; Based on the target training data set after positive and negative sample annotation, the classification sub-network is trained using a positive and negative sample training method; Inputting the SQL graph structure data to be optimized into the optimization sub-network, so that the optimization sub-network extracts the features of the SQL graph structure data and outputs the optimized SQL query statement based on the features; Training the optimization subnetwork based on the difference between the optimized SQL query statement and the optimization label corresponding to the SQL graph structure data; The device also includes a verification module, which is used to run the target SQL query statement in the database when the target SQL query statement output by the target graph convolutional neural network is obtained; Obtaining the target execution time and target resource usage data of the target SQL query statement; When the target execution time and the target resource usage data are both less than the SQL query statement to be optimized, determining that the target SQL query statement is the optimization result of the SQL query statement to be optimized; Sending the target SQL query statement to the target client; Based on the acceptance result of the target SQL query statement by the target client, adding a positive feedback sample data label or a negative feedback sample data label to the target SQL query statement; The marked target SQL query statement is added to the target training data set to train the target graph convolutional neural network.

9. An electronic device, comprising: processor; as well as Memory for storing programs, The program includes instructions, which, when executed by the processor, cause the processor to perform the method according to any one of claims 1 to 6.

10. A non-transitory computer-readable storage medium storing computer instructions, wherein: The computer instructions are used to make a computer execute the method according to any one of claims 1-6.

Images