CN115617830A - Data query optimization processing method and device based on machine learning - Google Patents

Abstract

The invention provides a data query optimization processing method and device based on machine learning. The method comprises the following steps: acquiring input structured query statement information; determining an execution plan corresponding to the structured query statement information, and converting execution environment parameter information corresponding to the execution plan into a corresponding expression vector; inputting the expression vector into a query optimizer model based on a long-short term memory network to obtain cost information output by the query optimizer model, and determining a corresponding target execution plan based on the cost information; the query optimizer model is obtained by training a historical execution plan serving as sample data and an actual cost corresponding to the historical execution plan serving as a target value. By adopting the method disclosed by the invention, more accurate cost estimation can be obtained under different execution environments based on dynamic execution environment parameter information, so that a better execution plan is selected, and the efficiency and the accuracy of query optimization processing are improved.

Description

A machine learning-based data query optimization processing method and device

technical field

The invention relates to the technical field of computer applications, in particular to a machine learning-based data query optimization processing method and device. In addition, it also relates to an electronic device and a processor-readable storage medium.

Background technique

Structured Query Language (Structured Query Language), referred to as SQL, is a database query and programming language for accessing data and querying, updating and managing data stored in relational database systems. Structured Query Language allows users to work on high-level data structures. It does not require the user to specify the data storage method, nor does it require the user to understand the specific data storage method, so different database systems with completely different underlying structures can use the same structured query language as the interface for data input and management. At the same time, structured query language statements can be nested, so that it has great flexibility and powerful functions.

In recent years, with the rapid development of Internet technology, data query with the help of structured query language has become more and more widely used. However, since the execution environment of the data query process is extremely complex, especially with the change of data volume and data distribution, in the prior art, static statistical data is used for optimization scheme matching, the accuracy of optimization quality is not high, and at the same time, it cannot cope with data Various execution scenarios of queries lead to low data query efficiency. Therefore, how to design a stable and efficient data query optimization processing scheme based on machine learning has become an important research topic for those skilled in the art.

Contents of the invention

For this reason, the present invention provides a data query optimization processing method and device based on machine learning to solve the problem that existing data query optimization processing schemes in the prior art have relatively high limitations, resulting in relatively low efficiency and accuracy of data query in complex execution environments. bad question.

In the first aspect, the present invention provides a data query optimization processing method based on machine learning, including: acquiring input structured query statement information;

Determining the execution plan corresponding to the structured query statement information, and converting the execution environment parameter information corresponding to the execution plan into a corresponding representation vector;

Inputting the representation vector into a query optimizer model based on a long short-term memory network, obtaining cost information output by the query optimizer model, and determining a corresponding target execution plan based on the cost information;

Wherein, the query optimizer model is obtained by training the historical execution plan as sample data and the actual cost corresponding to the historical execution plan as the target value.

In one embodiment, the execution environment parameter information includes query parsing tree, query load data for executing the structured query statement information, hardware feature information and software feature information of the execution environment.

In one embodiment, the query optimizer model includes: a cardinality estimation module, a cost estimation module, a connection order selection module, and a learning optimizer module with machine learning and reinforcement learning capabilities;

Wherein, the cardinality estimation module is configured to determine the total number of rows processed at each level of the execution plan, and determine the total number of rows as the cardinality information of the execution plan;

The cost estimation module is configured to determine the cost information of each physical path in the corresponding query optimizer based on the statistical information corresponding to the structured query statement information; wherein, the statistical information includes: high-frequency value statistical information, histogram statistics information, target coefficient statistics, type high frequency value information, array type histogram information, and range value type statistics;

The connection sequence selection module is used to select and optimize the connection sequence based on the reinforcement learning capability of the long-short-term memory network to obtain a corresponding execution plan;

The learning optimizer module is configured to determine a corresponding target execution plan from the execution plan by using machine learning and reinforcement learning based on the cardinality information and the cost information.

In one embodiment, the machine learning-based data query optimization processing method further includes: optimizing the predetermined structured query statement information based on preset rewriting rules and optimizing the structured query statement information. Optimizing and rewriting the target query statement; or, based on preset learning rules, using machine learning and reinforcement learning to optimize and select the information of the structured query statement to obtain a corresponding query rewriting result.

In one embodiment, the machine learning-based data query optimization processing method further includes: using the SQL parser in the query parsing tool of the corresponding database to parse the structured query statement information to obtain the expression containing the operator formula query parsing tree; using the query optimizer model to optimize the operator expression according to the preset plugging principle, and obtain corresponding query parsing results.

In one embodiment, the long-short-term memory network query optimizer model is used to analyze and process the structured query statement information based on the representation features obtained by feature extraction from the historical execution plan, and determine the corresponding cost information.

In the second aspect, the present invention also provides a data query optimization processing device based on machine learning, including: a structured query statement acquisition unit, configured to acquire input structured query statement information;

A vector representation unit, configured to determine the execution plan corresponding to the structured query statement information, and convert the execution environment parameter information corresponding to the execution plan into a corresponding representation vector;

A query optimization processing unit, configured to input the representation vector into a query optimizer model based on a long short-term memory network, obtain cost information output by the query optimizer model, and determine a corresponding target execution plan based on the cost information ;

Wherein, the query optimizer model is obtained by training the historical execution plan as sample data and the actual cost corresponding to the historical execution plan as the target value.

In one embodiment, the execution environment parameter information includes query parsing tree, query load data for executing the structured query statement information, hardware feature information and software feature information of the execution environment.

In one embodiment, the query optimizer model includes: a cardinality estimation module, a cost estimation module, a connection order selection module, and a learning optimizer module with machine learning and reinforcement learning capabilities;

Wherein, the cardinality estimation module is configured to determine the total number of rows processed at each level of the execution plan, and determine the total number of rows as the cardinality information of the execution plan;

The cost estimation module is configured to determine the cost information of each physical path in the corresponding query optimizer based on the statistical information corresponding to the structured query statement information; wherein, the statistical information includes: high-frequency value statistical information, histogram statistics information, target coefficient statistics, type high frequency value information, array type histogram information, and range value type statistics;

The connection sequence selection module is used to select and optimize the connection sequence based on the reinforcement learning capability of the long-short-term memory network to obtain a corresponding execution plan;

The learning optimizer module is configured to determine a corresponding target execution plan from the execution plan by using machine learning and reinforcement learning based on the cardinality information and the cost information.

In one embodiment, the machine learning-based data query optimization processing device further includes: a query rewriting unit, configured to optimize the structure of the predetermined structured query statement information based on preset rewriting rules Optimize and rewrite the target query statement in the structured query statement information; or, based on preset learning rules, use machine learning and reinforcement learning to optimize and select the structured query statement information to obtain corresponding query rewriting results.

In one embodiment, the machine learning-based data query optimization processing method further includes: a query parsing unit for parsing the structured query statement information by using the SQL parser in the query parsing tool of the corresponding database , to obtain a query parsing tree including an operator expression; using the query optimizer model, the operator expression is optimized according to a preset plugging principle, and a corresponding query parsing result is obtained.

In one embodiment, the long-short-term memory network query optimizer model is used to analyze and process the structured query statement information based on the representation features obtained by feature extraction from the historical execution plan, and determine the corresponding cost information.

In a third aspect, the present invention also provides an electronic device, including: a memory, a processor, and a computer program stored on the memory and operable on the processor, when the processor executes the program, any one of the above-mentioned The steps of the machine learning-based data query optimization processing method.

In a fourth aspect, the present invention also provides a processor-readable storage medium, where a computer program is stored on the processor-readable storage medium, and when the computer program is executed by a processor, the machine-based Learn the steps of the data query optimization processing method.

By adopting the data query optimization processing method based on machine learning described in the present invention, based on the dynamic execution environment parameter information, more accurate cost estimates can be obtained in different execution environments, so as to quickly and accurately select a better execution plan, The efficiency and accuracy of query optimization processing are improved.

Description of drawings

In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings that need to be used in the description of the embodiments or the prior art. Obviously, the accompanying drawings in the following description These are some embodiments of the present invention. For those skilled in the art, other drawings can also be obtained based on these drawings without creative effort.

FIG. 1 is a schematic flowchart of a data query optimization processing method based on machine learning provided by an embodiment of the present invention;

FIG. 2 is a schematic structural diagram for implementing a machine learning-based data query optimization processing method provided by an embodiment of the present invention;

3 is a schematic structural diagram of a query optimization module in a data query optimization processing method based on machine learning provided by an embodiment of the present invention;

4 is a schematic structural diagram of a SQL statement reinforcement learning model in a data query optimization processing method based on machine learning provided by an embodiment of the present invention;

FIG. 5 is a schematic diagram of a specific implementation process corresponding to a data query optimization processing method based on machine learning provided by an embodiment of the present invention;

6 is a schematic structural diagram of a data query optimization processing device based on machine learning provided by an embodiment of the present invention;

FIG. 7 is a schematic diagram of a physical structure of an electronic device provided by an embodiment of the present invention.

detailed description

In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments It is a part of embodiments of the present invention, but not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by persons of ordinary skill in the art without making creative efforts belong to the protection scope of the present invention.

Embodiments of the machine learning-based data query optimization processing method described in the present invention will be described in detail below. As shown in Figure 1, it is a schematic flow chart of a data query optimization processing method based on machine learning provided by an embodiment of the present invention, and the specific implementation process includes the following steps:

Step 101: Obtain the information of the input structured query statement.

In the embodiment of the present invention, before performing this step, model training needs to be performed in advance. Specifically, it is first necessary to include historically executed SQL (Structured Query Language) execution plans, attribute columns, tables, and query trees related to the execution plan, load data for executing SQL statements, hardware feature information, software feature information, and other execution environment information. Transform into the corresponding vector representation, that is, determine the corresponding representation vector, use the representation vector as the input of the original query optimizer model, take the actual cost of executing the execution plan as the target value, and compare the target value with the predicted value to calculate the corresponding loss , perform backpropagation to update the parameters, and realize the training of the model. The original query optimizer model refers to a cost model based on LSTM (Long Short-Term Memory; long short-term memory network). Further, in this step, the structured query statement information input during the SQL data query, that is, the SQL query statement is obtained.

Step 102: Determine the execution plan corresponding to the structured query statement information, and convert the execution environment parameter information corresponding to the execution plan into a corresponding representation vector. Wherein, the execution environment parameter information includes query parsing tree, query load data for executing the structured query statement information, hardware feature information and software feature information of the execution environment, and the like. The execution plan is an SQL execution plan.

In the embodiment of the present invention, first determine each execution plan corresponding to the SQL query statement information, and convert the execution environment information such as attribute columns, tables, query parsing trees, load data, hardware feature information, and software feature information related to the execution plan into The corresponding representation vector is input into the trained cost model, and the cost output by the cost model is obtained, so that the execution plan with the lowest cost can be selected for subsequent execution. The query parse tree is an abstract syntax tree (AbstractSyntax Tree, AST).

In the process of query feature representation, since the performance of machine learning methods largely depends on the form or characteristics of data represented by vectors. Therefore, this application extracts features such as execution environment parameter information and database metadata (tables, attributes, statistical information, etc.) when users perform data queries, and can use the One-Hot method for feature representation. Specifically, the features are expressed as: transforming the SQL execution plan and objects such as attribute columns and tables related to the execution plan, and the generated query parsing tree into corresponding representation vectors, which are used as input of the cost model. The vector representation is as follows: attribute columns, tables, query parsing trees, etc. are converted into corresponding representation vectors as the input of the cost model. Among them, the representation structure of the query parsing tree is Tree-LSTM, which is a kind of graph neural network (GNN, Graph Neural Networks). The parameters of the Tree-LSTM can be set according to preset rules, and will not be described in detail here.

In the specific implementation process, the expression of the SQL query statement may include: let n be the number of tables in the database, each table has a unique identifier from 0 to n-1; let m be n where each element is 0 or 1 *n matrix; Mi, j being 1 means that there is a connection relationship between the i-th table and the j-th table, and 0 if it does not exist; then, by adding all rows of the original matrix m (from 0 to n-1) put into a vector to flatten the matrix into a vector v, that is, Vi*n+j=mi, j; |v|=n*n (convert a matrix into a one-dimensional vector).

Representation of attribute columns: Based on preset SQL query statements, only the predicates in SQL need to be used to construct the representation of columns without scanning the database.

For a table with k columns, use the vector of its k columns to represent its table's representation vector. For some symbolic operators in the SQL query statement, it is planned to use the One-Hot form to encode. In addition, in the actual implementation process, related One-Hot vectorization is also carried out for Hash Join, Seq Scan, etc. in cardinality estimation, each table and the attribute value of each table, etc., which will not be repeated here Go into details.

In the process of model processing, the embedding layer: transforms the vectorized operators and the attributes and fields of various tables into low-dimensional dense representation vectors through a layer of Embedding. Input the final processed data into the cost model for calculation. The operator, metadata, cardinality estimation operation method and node graph are denoted by O _t , M _t , P _t , B _t respectively. After the activation layer processing, for example, it can be expressed as follows:

embed(O _t )＝ReLU(W _o O _t +b _o )

embed(M _t )＝ReLU(W _m M _t +b _m )

embed(B _t )＝ReLU(W _b B _t +b _b )

Among them, embed represents the embedding layer function; ReLU is the linear rectification function; W _o , W _m , W _b , b _o , b _m , and b _b are preset parameters.

The following node types, for example, can be expressed as follows:

为预设参数。Among them, P _t represents the selection type of the cardinality estimation operation method, specifically including AND, OR and other selection types of operations; embed represents the embedded layer function; expr represents the expression; W _m , b _p ,

is a preset parameter.

Presentation layer: The problem to be solved in the embodiment of the present invention is to avoid the loss between the global nodes and the root node, so it is proposed to use the LSTM-based cost model for model training. Inputting the return value of the embedding layer into the presentation layer can correspond to the following formal definition:

output＝LSTM(embed(O _t ),embed(M _t ),embed(B _t ),embed(P _t ))

Evaluation layer: use a two-layer fully connected network (Fully Connected, FC) to connect, and set a specific dropout to prevent overfitting of training. The activation function is performed using ReLU (Rectified Linear Unit, ReLU) linear rectification function or Sigmoid (S-type function). Finally, the target value is compared with the predicted value to calculate the corresponding loss, and the backpropagation is performed to update the parameters. The overall formalization can be defined as follows:

Final_output = sigmoid(Dropout(FC(output)))

The above proposed method can be adjusted according to the actual business to achieve the optimal effect, and finally a trained query optimizer model based on the long-term and short-term memory network can be obtained.

Step 103: Input the representation vector into the query optimizer model based on the long short-term memory network, obtain the cost information output by the query optimizer model, and determine the corresponding target execution plan based on the cost information. Wherein, the query optimizer model is obtained by training the historical execution plan as sample data and the actual cost corresponding to the historical execution plan as the target value. The long-short-term memory network query optimizer model is used to analyze and process the structured query statement information based on the representation features obtained by feature extraction from the historical execution plan, and determine corresponding cost information.

As shown in FIG. 2, in the embodiment of the present invention, the query processing system includes a query parser, query rewriter, query optimizer, query executor, etc.; the storage management system includes a buffer management module, etc.; and a machine learning model manager.

In the query parsing process, the SQL parser in the query parsing tool of the corresponding database can be used to parse the structured query statement information to obtain a query parsing tree containing operator expressions; using the query optimizer model, according to The preset insertion and removal principle optimizes the operator expression to obtain a corresponding query analysis result. Specifically, Calcite, a query analysis tool corresponding to the database, can be used to apply to various offline, search, and real-time query engines, such as Drill, Hive, Kylin, Solr, flink, Samza, etc., to analyze SQL query statements, SQL verification, etc. Features. The query parsing tree is parsed by Calcite's SQL parser, and the query parsing tree contains operator expressions. The query optimizer can optimize operator expressions according to the principle of plugging and unplugging, and can use the information provided by the metadata database for cost calculation and other operations.

During the query rewriting process, the target query statement in the structured query statement information may be optimized and rewritten based on the predetermined optimization type of structured query statement information based on preset rewriting rules; or, based on preset learning rules , using machine learning and reinforcement learning to optimize and select the structured query statement information to obtain a corresponding query rewriting result. Specifically, a preset SQL rewriting tool is provided, and the index recommendation is optimized. Based on the model, the advantages and costs of indexing can be learned in the case of a given query workload, and then the index is automatically established according to the learned information, for example : Foreign key index to improve query speed. Compared with the traditional query rewriting method, SQL statements are rewritten only based on the content in SQL, user click behavior, semantic information, and obtained Session information of different users. On this basis, the present invention adopts two ways to improve, that is, based on the preset rewriting rules, the predetermined SQL optimization type is optimized and rewritten to the target query statement in the structured query statement information; and based on the preset The learning method uses machine learning and reinforcement learning to optimize the selection of SQL query statements.

In the query optimization process, the query optimizer model includes: a cardinality estimation module, a cost estimation module, a connection order selection module, a rule-based optimizer and a cost-based optimizer, a learning type with machine learning and reinforcement learning capabilities Optimizer module. Wherein, the cardinality estimation module is used to determine the total number of rows processed at each level of the execution plan, and determine the total number of rows as the cardinality information of the execution plan; the cost estimation module is used to determine the number of rows based on the The statistical information corresponding to the structured query statement information determines the cost information of each physical path in the corresponding query optimizer; among them, the statistical information includes: high-frequency value statistical information, histogram statistical information, target coefficient statistical information, type high-frequency value Information, array type histogram information and range value type statistical information; the connection order selection module is used to optimize the connection order selection based on the reinforcement learning ability of the long short-term memory network, and obtain the corresponding execution plan; the learning type An optimizer module, configured to determine a corresponding target execution plan from the execution plan by using machine learning and reinforcement learning based on the cardinality information and the cost information.

In the query optimizer, machine learning is first used to classify SQL query statements using classification and regression in the Spark MLib library, and then extract corresponding optimization methods from the classified types. The overall structure of the query optimizer is shown in Figure 3. It mainly includes two parts: SQL optimization part and query optimizer part. When performing the SQL optimization part, the SQL query statement rewritten in the SQL optimization part can be optimized, and index recommendations, view recommendations, and partition recommendations can be formed through data feature extraction, dimensionality reduction, collaborative filtering, association analysis, and trend prediction. and other optimization functions. In the query optimizer part, it is planned to use combined cardinality estimation, cost estimation, and connection order selection (using Tree-LSTM model structure), while using rule-based optimizer (RBO, Rule-Based Optimizer), cost-based optimizer (CBO, Cost-Based Optimizer) and Learning Optimizer with Machine Learning and Reinforcement Learning.

The size estimate is the base estimate. First determine the total number of rows processed at each level of the execution plan, known as the cardinality of the execution plan; the overhead pattern of the algorithm dictated by the operators used in the query; the first factor (the cardinality) is used as the second factor ( cost mode); therefore, increasing the cardinality will reduce the estimated cost and thus speed up the execution plan.

The cost estimate is a cost model. Determining the query optimizer for physical optimization requires calculating the cost of various physical paths, and cost estimation relies heavily on statistics. Whether the statistical information can accurately describe the data distribution in the table is one of the important conditions for determining the accuracy of the cost. In the specific implementation process, through cost estimation, you can know how many pieces of data a table has, how many pages are used, the frequency of a certain value, etc., and then calculate how much data can be filtered out by a constraint based on this information. The ratio of the data filtered out by such constraints to the total amount of data is called the "selection rate".

Various forms of statistical information are provided according to SQL query statements, including single-column statistical information and multi-column statistical information. Single-column statistical information means that the attribute columns of each table generate a corresponding statistical tuple in the system table. This tuple is responsible for depicting the distribution of data in this attribute from multiple perspectives.

The statistical information includes: high-frequency value statistics, histogram statistics, correlation coefficient statistics, type high-frequency values, array type histogram, and finally range value type statistics. From the above statistical information to form a grasp of the overall data, the estimation of the selection rate needs the help of statistical information (including histogram, high frequency value, NULL value rate, etc.).

In the actual implementation process, the selection rate of the constraints is determined, that is, the proportion of the results to be scanned through the scanning path or the proportion of the tuples obtained through the connection operation is determined, and this proportion can be calculated The number of intermediate results and final results is calculated, and then these numbers are used to calculate the cost, so as to determine the corresponding price.

Said plan generation, ie connection order selection. Join optimization of queries using Tree-LSTM model and reinforcement learning. Specifically, it uses reinforcement learning with tree-structured long short-term memory (LSTM) for connection order selection. First, a graph neural network is used to capture the structure of the connection tree; in addition, it supports the modification of the database schema and the name of the multi-alias table. A large number of experiments based on JOB (Join Order Benchmark) and TPC-H show that RTOS (Real-time operating system) is superior to traditional optimizers and existing learning optimizers based on DRL (Deep Reinforcement Learning). Especially compared to dynamic programming, RTOS connection planning (estimated) costs 101% and execution time 67%.

Furthermore, in the process of selecting an efficient execution plan, the actual execution plan time can be used as the execution cost (Reward) for training by means of reinforcement learning, that is, the adjustment of the execution plan (ie, the scheduling strategy). The goal is to find the execution plan with the smallest execution plan time for the data query.

The main model structure of reinforcement learning is shown in Figure 4. Every time the SQL query statement is parsed, the query load data and the validity of the SQL query statement will be changed, so it can be seen that the optimization of the SQL query statement is a sequential optimization problem. Reinforcement learning can better solve the sequential decision-making process and reduce the dependence on training samples. Reinforcement learning interacts with the environment through trial and error through the scheduler (Agent) to find the optimal scheduling strategy (Action) to maximize the cumulative rewards obtained from interacting with the environment. Mathematically, it can be modeled as a Markov decision process (MDP, Markov Decision Process), which is used to simulate the random strategy and reward that the agent can achieve in an environment where the system state has Markov properties. The elements include state, action, strategy function and reward function. The present invention can adopt Q-Learning, DQN (Deep Q-Learing Network) asynchronous, off-line RL algorithm, and accelerate the efficiency of reinforcement learning through episodic meta-reinforcement learning. Reinforcement learning mainly obtains feedback from the interaction with the environment, and its interpretability is limited. When using deep learning to solve the goal, it will further lead to poor interpretability. In the scenario calculation applied to the database, it mainly relies on practical experience. For example, its Reward calculation mainly obtains benefits from the front and rear query times. Therefore, this project not only uses the Tree-LSTM method of the above cost model as the baseline method, but also intends to integrate or fuse with this reinforcement learning method to further improve efficiency and interpretability.

As shown in Figure 2, in an implementation process, the user inputs SQL query statement information through the user interface, and the machine learning library and reinforcement learning library extend it to convert it into a relational SQL query statement. After receiving the SQL query statement, the query processing system extracts the execution result from the index file or cache after parsing, rewriting, optimizing, and executing the SQL query statement. The query processing system relies on the collector, which can combine the load performance index of the machine learning library and the reinforcement learning library with the performance of query processing, and finally achieve an effective interaction with the environment. The storage management module (that is, the storage management system) adopts the heterogeneous data platform exchange technology provided by the hybrid data storage management system (Database Management System; DBMS) to realize data reading and complex correlation query of various databases, thereby simplifying data preprocessing process. The query optimization model can be detailed into scale estimation, cost estimation, and final plan generation.

In the embodiment of the present invention, the machine learning library mainly uses machine learning libraries such as Spark MLib, ML Algorithm, Stored Procedure, Spark UDF and related methods. In addition, the present invention can also develop custom algorithms for the provided machine learning library. The metabase shown is used to hold information such as database schemas, knowledge graphs, user query load logs, system performance logs, machine learning libraries, and reinforcement learning libraries.

As shown in Figure 5, in another practical implementation process, data set A can be used for model testing and training. Dataset A is a real dataset based on the traffic leaderboard, which can provide real load data. It contains 113 SQL queries from 33 templates, and contains 3.6GB of data (11GB when indexes are calculated) and 21 tables. The range of tables in each SQL query statement is 4 to 17. Specifically, the SQL query statements in the data set A must be extracted and loaded first, and the loaded SQL query statements are allocated according to the ratio of 9:1 between the training set and the test set. After the SQL query statements are loaded, the characteristics of the table (table name, column name, predicate, etc.) are encoded to obtain a representation vector, and the representation vector is used as the input of the query optimizer model based on deep reinforcement learning, and then the encoding extraction feature is trained. The intermediate state is a connected tree containing partial states. The intermediate state is passed to the query optimizer model to choose what action should be taken now, and it keeps updating its own state based on the chosen action. The terminal state of the system is to connect all tables together to form a connection tree containing all tables, which will be converted into the execution plan of the SQL query statement and passed to the hybrid data storage management system that manages the storage module for execution.

The present invention adopts methods based on machine learning, reinforcement learning, etc. to improve the computing efficiency of the query optimizer, continuously learn the load data of the user's query, and improve the self-adaptive optimization capability of the database SQL data query, that is, for the user's data scale and query efficiency. The system is optimized according to the characteristics of the load data, so as to promote the intelligent empowerment of data query analysis.

Using the machine learning-based data query optimization processing method described in the embodiment of the present invention can obtain more accurate cost estimates in different execution environments based on dynamic execution environment parameter information, so as to quickly and accurately select better execution plan, which improves the efficiency and accuracy of query optimization processing.

Corresponding to the machine learning-based data query optimization processing method provided above at the base station side, the present invention also provides a machine learning-based data query optimization processing device at the base station side. Since the embodiment of the device is similar to the above-mentioned method embodiment, the description is relatively simple. For relevant information, please refer to the description of the above-mentioned method embodiment. The embodiment of the data query optimization processing device based on machine learning described below is only is indicative. Please refer to FIG. 6 , which is a schematic structural diagram of a data query optimization processing device based on machine learning provided by an embodiment of the present invention.

A machine learning-based data query optimization processing device according to the present invention specifically includes the following parts:

A structured query statement acquisition unit 601, configured to acquire the input structured query statement information;

A vector representation unit 602, configured to determine the execution plan corresponding to the structured query statement information, and convert the execution environment parameter information corresponding to the execution plan into a corresponding representation vector;

The query optimization processing unit 603 is configured to input the representation vector into the query optimizer model based on the long short-term memory network, obtain the cost information output by the query optimizer model, and determine the corresponding target execution based on the cost information plan.

Wherein, the query optimizer model is obtained by training the historical execution plan as sample data and the actual cost corresponding to the historical execution plan as the target value.

Using the machine learning-based data query optimization processing device described in the embodiment of the present invention can obtain more accurate cost estimates in different execution environments based on dynamic execution environment parameter information, so as to quickly and accurately select better execution plan, which improves the efficiency and accuracy of query optimization processing.

Corresponding to the machine learning-based data query optimization processing method provided above, the present invention also provides an electronic device. Since the embodiment of the electronic device is similar to the above-mentioned method embodiment, the description is relatively simple. For related details, please refer to the description of the above-mentioned method embodiment. The electronic device described below is only illustrative. As shown in FIG. 7 , it is a schematic diagram of a physical structure of an electronic device disclosed in an embodiment of the present invention. The electronic device may include: a processor (processor) 701, a memory (memory) 702 and a communication bus 703, wherein the processor 701 and the memory 702 communicate with each other through the communication bus 703, and communicate with the outside through the communication interface 704. The processor 701 can invoke logic instructions in the memory 702 to execute a data query optimization processing method based on machine learning. The method includes: acquiring input structured query statement information; determining an execution plan corresponding to the structured query statement information, and converting the execution environment parameter information corresponding to the execution plan into a corresponding representation vector; and converting the representation vector Input into the query optimizer model based on the long short-term memory network, obtain the cost information output by the query optimizer model, and determine the corresponding target execution plan based on the cost information; wherein, the query optimizer model is the historical The execution plan is used as sample data, and the actual cost corresponding to the historical execution plan is used as a target value for training.

In addition, the above-mentioned logic instructions in the memory 702 may be implemented in the form of software functional units and may be stored in a computer-readable storage medium when sold or used as an independent product. Based on this understanding, the essence of the technical solution of the present invention or the part that contributes to the prior art or the part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including Several instructions are used to make a computer device (which may be a personal computer, a server, or a network device, etc.) execute all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage media include: memory chips, U disks, mobile hard disks, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), magnetic disks or optical disks, etc., which can store programs. The medium of the code.

On the other hand, an embodiment of the present invention also provides a computer program product, the computer program product includes a computer program stored on a processor-readable storage medium, the computer program includes program instructions, and when the program instructions are executed by the computer During execution, the computer can execute the machine learning-based data query optimization processing method provided by the above method embodiments. The method includes: acquiring input structured query statement information; determining an execution plan corresponding to the structured query statement information, and converting the execution environment parameter information corresponding to the execution plan into a corresponding representation vector; and converting the representation vector Input into the query optimizer model based on the long short-term memory network, obtain the cost information output by the query optimizer model, and determine the corresponding target execution plan based on the cost information; wherein, the query optimizer model is the historical The execution plan is used as sample data, and the actual cost corresponding to the historical execution plan is used as a target value for training.

In yet another aspect, an embodiment of the present invention further provides a processor-readable storage medium, where a computer program is stored on the processor-readable storage medium, and the computer program is implemented when executed by a processor to perform the functions provided by the above-mentioned embodiments. Data query optimization processing method based on machine learning. The method includes: acquiring input structured query statement information; determining an execution plan corresponding to the structured query statement information, and converting the execution environment parameter information corresponding to the execution plan into a corresponding representation vector; and converting the representation vector Input into the query optimizer model based on the long short-term memory network, obtain the cost information output by the query optimizer model, and determine the corresponding target execution plan based on the cost information; wherein, the query optimizer model is the historical The execution plan is used as sample data, and the actual cost corresponding to the historical execution plan is used as a target value for training.

The processor-readable storage medium can be any available medium or data storage device that can be accessed by a processor, including but not limited to magnetic storage (e.g., floppy disk, hard disk, magnetic tape, magneto-optical disk (MO), etc.), optical storage (e.g., CD, DVD, BD, HVD, etc.), and semiconductor memory (such as ROM, EPROM, EEPROM, non-volatile memory (NANDFLASH), solid-state disk (SSD)), etc.

The device embodiments described above are only illustrative, and the units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in One place, or it can be distributed to multiple network elements. Part or all of the modules can be selected according to actual needs to achieve the purpose of the solution of this embodiment. It can be understood and implemented by those skilled in the art without any creative efforts.

Through the above description of the implementations, those skilled in the art can clearly understand that each implementation can be implemented by means of software plus a necessary general-purpose hardware platform, and of course also by hardware. Based on this understanding, the essence of the above technical solution or the part that contributes to the prior art can be embodied in the form of software products, and the computer software products can be stored in computer-readable storage media, such as ROM/RAM, magnetic discs, optical discs, etc., including several instructions to make a computer device (which may be a personal computer, server, or network device, etc.) execute the methods described in various embodiments or some parts of the embodiments.

Finally, it should be noted that: the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that: it can still be Modifications are made to the technical solutions described in the foregoing embodiments, or equivalent replacements are made to some of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the various embodiments of the present invention.

Claims (10)

1. A data query optimization processing method based on machine learning, characterized in that, comprising: Obtain the input structured query statement information; Determining the execution plan corresponding to the structured query statement information, and converting the execution environment parameter information corresponding to the execution plan into a corresponding representation vector; Inputting the representation vector into a query optimizer model based on a long short-term memory network, obtaining cost information output by the query optimizer model, and determining a corresponding target execution plan based on the cost information; Wherein, the query optimizer model is obtained by training the historical execution plan as sample data and the actual cost corresponding to the historical execution plan as the target value. 2. The data query optimization processing method based on machine learning according to claim 1, wherein the execution environment parameter information includes query parsing tree, execution query load data of the structured query statement information, and execution environment parameter information. Hardware feature information and software feature information. 3. The data query optimization processing method based on machine learning according to claim 1, wherein the query optimizer model includes: a cardinality estimation module, a cost estimation module, a connection sequence selection module, a machine learning and strengthening learning optimizer module of learning ability; Wherein, the cardinality estimation module is configured to determine the total number of rows processed at each level of the execution plan, and determine the total number of rows as the cardinality information of the execution plan; The cost estimation module is configured to determine the cost information of each physical path in the corresponding query optimizer based on the statistical information corresponding to the structured query statement information; wherein, the statistical information includes: high-frequency value statistical information, histogram statistics information, target coefficient statistics, type high frequency value information, array type histogram information, and range value type statistics; The connection sequence selection module is used to select and optimize the connection sequence based on the reinforcement learning capability of the long-short-term memory network to obtain a corresponding execution plan; The learning optimizer module is configured to determine a corresponding target execution plan from the execution plan by using machine learning and reinforcement learning based on the cardinality information and the cost information. 4. the data query optimization processing method based on machine learning according to claim 1, is characterized in that, also comprises: Using the SQL parser in the query parsing tool of the corresponding database to parse the structured query statement information to obtain a query parsing tree containing operator expressions; The query optimizer model is used to optimize the operator expression according to the preset plugging principle, and obtain corresponding query analysis results. 5. the data query optimization processing method based on machine learning according to claim 1, is characterized in that, also comprises: Optimize and rewrite the target query statement in the structured query statement information based on the preset rewriting rule for the predetermined optimization type of the structured query statement information; or, Based on preset learning rules, machine learning and reinforcement learning are used to optimize and select the structured query statement information, and obtain corresponding query rewriting results. 6. The data query optimization processing method based on machine learning according to claim 1, wherein the long-short-term memory network query optimizer model is used to obtain representation features based on feature extraction from the historical execution plan , analyzing and processing the structured query statement information to determine corresponding cost information. 7. A data query optimization processing device based on machine learning, characterized in that, comprising: a structured query statement obtaining unit, configured to obtain input structured query statement information; A vector representation unit, configured to determine the execution plan corresponding to the structured query statement information, and convert the execution environment parameter information corresponding to the execution plan into a corresponding representation vector; A query optimization processing unit, configured to input the representation vector into a query optimizer model based on a long short-term memory network, obtain cost information output by the query optimizer model, and determine a corresponding target execution plan based on the cost information ; Wherein, the query optimizer model is obtained by training the historical execution plan as sample data and the actual cost corresponding to the historical execution plan as the target value. 8. The data query optimization processing device based on machine learning according to claim 7, wherein the execution environment parameter information includes query parsing tree, execution query load data of the structured query statement information, and execution environment parameter information. Hardware feature information and software feature information. 9. An electronic device, comprising a memory, a processor, and a computer program stored on the memory and operable on the processor, wherein the processor implements any one of claims 1-6 when executing the program. The steps of the data query optimization processing method based on machine learning described in the item. 10. A processor-readable storage medium, with a computer program stored on the processor-readable storage medium, characterized in that, when the computer program is executed by a processor, the computer program according to any one of claims 1-6 is implemented. The steps of the data query optimization processing method based on machine learning.

Images