Method, device, storage medium and system for processing query statements

Abstract

The present invention discloses a method, device, storage medium and system for processing query statements. The method comprises: obtaining a query statement to be processed; determining a target execution plan corresponding to the query statement to be processed according to a target strategy, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine multiple states corresponding to the query statement to be processed and the execution actions in each of the multiple states; executing the query statement to be processed according to the target execution plan to obtain a query result. The present invention solves the technical problem that the query optimization method provided in the related art has a high execution plan delay and low query efficiency.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a method, device, storage medium and system for processing query statements. Background Art

[0002] With the advent of the big data era, data query optimization has become an important and challenging issue in the database field. Data query optimization refers to the process of using a query optimizer in a database management system to determine the optimal execution plan for a query statement. Traditional query optimizers rely on artificially pre-made cost models. In recent related technologies, query optimizers based on reinforcement learning have been applied and have demonstrated their advantages.

[0003] For query optimizers based on reinforcement learning, reinforcement learning strategies include: strategies based on cost signals (referring to the cost model's estimate of the execution plan) and strategies based on delay signals (referring to the actual time of the execution plan). The strategy based on cost signals refers to using the cost as a guidance signal when training the corresponding reinforcement learning model. The defect of the query optimizer using this strategy is that the delay performance during the learning process is poor. The strategy based on delay signals refers to using delay as a guidance signal when training the corresponding reinforcement learning model. The defect of the query optimizer using this strategy is that the execution plan takes a long time during the query process. In addition, there is a gap between cost signals and delay signals. The optimal strategies based on cost signals and delay signals may be different, which in turn affects the query process.

[0004] Therefore, how to improve the query optimizer strategy and generate an optimized execution plan for query statements and improve query efficiency has become a key issue in the data query optimization process. Currently, no effective solution has been proposed for the above problems. Summary of the invention

[0005] The embodiments of the present invention provide a method, device, storage medium and system for processing query statements, so as to at least solve the technical problems of high execution plan delay and low query efficiency of the query optimization method provided in the related art.

[0006] According to one aspect of an embodiment of the present invention, a method for processing query statements is provided, including: obtaining a query statement to be processed; determining a target execution plan corresponding to the query statement to be processed according to a target strategy, wherein the target strategy is obtained by training a plurality of query statements, and the target execution plan is used to determine a plurality of states corresponding to the query statement to be processed and an execution action in each of the plurality of states; executing the query statement to be processed according to the target execution plan to obtain a query result.

[0007] According to another aspect of an embodiment of the present invention, a device for processing query statements is also provided, including: an acquisition module, used to acquire the query statement to be processed; a determination module, used to determine the target execution plan corresponding to the query statement to be processed according to the target strategy, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine multiple states corresponding to the query statement to be processed and the execution actions in each of the multiple states; a processing module, used to execute the query statement to be processed according to the target execution plan to obtain a query result.

[0008] According to another aspect of an embodiment of the present invention, a storage medium is further provided. The storage medium includes a stored program, wherein when the program is executed, the device where the storage medium is located is controlled to execute any one of the methods for processing query statements.

[0009] According to another aspect of an embodiment of the present invention, a system for processing query statements is also provided, including: a processor; and a memory, connected to the above processor, for providing the above processor with instructions for processing the following processing steps: obtaining a query statement to be processed; determining a target execution plan corresponding to the query statement to be processed according to a target strategy, wherein the target strategy is obtained by training a plurality of query statements, and the target execution plan is used to determine a plurality of states corresponding to the query statement to be processed and an execution action in each of the plurality of states; executing the query statement to be processed according to the target execution plan to obtain a query result.

[0010] In an embodiment of the present invention, a query statement to be processed is first obtained, and a target execution plan corresponding to the query statement to be processed is determined according to a target strategy. The query result is obtained by executing the query statement to be processed according to the target execution plan, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine multiple states corresponding to the query statement to be processed and the execution action in each of the multiple states, thereby achieving the purpose of using a strategy based on query statement training to generate an execution plan for the query statement to optimize the query process, thereby achieving the technical effect of improving the query optimizer's strategy to obtain an optimized execution plan for the query statement to improve the query efficiency, and further solving the technical problem of high execution plan delay and low query efficiency of the query optimization method provided in the related art. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] The drawings described herein are used to provide a further understanding of the present invention and constitute a part of the present invention. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute an improper limitation of the present invention. In the drawings:

[0012] Figure 1 A hardware structure block diagram of a computer terminal (or mobile device) for implementing a method for processing a query statement is shown;

[0013] Figure 2 is a flow chart of a method for processing a query statement according to an embodiment of the present invention;

[0014] Figure 3 is a schematic diagram of an example of an optional Markov decision process according to an embodiment of the present invention;

[0015] Figure 4 is a schematic diagram of an optional query optimization process based on reinforcement learning according to an embodiment of the present invention;

[0016] Figure 5 is a schematic diagram of an optional strategy conversion process according to an embodiment of the present invention;

[0017] Figure 6 is a flowchart of another method for processing a query statement according to an embodiment of the present invention;

[0018] Figure 7 is a schematic diagram of a method for executing and processing query statements on a cloud server according to an embodiment of the present invention;

[0019] Figure 8 is a structural schematic diagram of a device for processing query statements according to an embodiment of the present invention;

[0020] Fig. 9 is a schematic diagram of the structure of another device for processing query statements according to an embodiment of the present invention;

[0021] Fig.10 is a structural block diagram of another computer terminal according to an embodiment of the present invention. DETAILED DESCRIPTION

[0022] In order to enable those skilled in the art to better understand the scheme of the present invention, the technical scheme 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 are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.

[0023] It should be noted that the terms "first", "second", etc. in the specification and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units that are clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0024] First, some nouns or terms that appear in the process of describing the embodiments of the present invention are subject to the following explanations:

[0025] Reinforcement Learning (RL): Also known as reinforcement learning, evaluation learning or enhanced learning, it is one of the paradigms and methodologies of machine learning. RL is used to describe and solve the problem of how an intelligent agent can maximize its rewards or achieve specific goals through learning strategies during its interaction with the environment. RL can be divided into model-based reinforcement learning (model-based RL) and model-free reinforcement learning (model-free RL).

[0026] Inverse Reinforcement Learning (IRL): A variant of reinforcement learning. IRL refers to reversing the state-action sequence produced by the optimal strategy to solve the reward function of the environment.

[0027] Query Optimizer: refers to the component of SQL Server that performs internal optimization based on user requests, generates execution plans, transmits them to the storage engine to operate data, and finally returns results to users.

[0028] Database Management System (DBMS): refers to a large-scale software that manipulates and manages databases. DBMS can manage and control databases in a unified manner to ensure the security and integrity of the database.

[0029] Markov Decision Process (MDP): A common model of reinforcement learning. This model may include: the process of assuming the Markov property of state transformation in reinforcement learning, the process of making Markov assumptions on reinforcement learning strategies, etc.

[0030] Example 1

[0031] According to an embodiment of the present invention, a method embodiment for processing query statements is also provided. It should be noted that the steps shown in the flowchart of the accompanying drawings can be executed in a computer system such as a set of computer executable instructions, and although a logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0032] The method embodiment provided in the first embodiment of the present invention may be executed in a mobile terminal, a computer terminal or a similar computing device. Figure 1 FIG. 1 shows a hardware structure block diagram of a computer terminal (or mobile device) for implementing a method for processing a query statement. Figure 1 As shown, the computer terminal 10 (or mobile device 10) may include one or more (102a, 102b, ..., 102n are used to illustrate) processors 102 (the processor 102 may include but is not limited to a processing device such as a microprocessor MCU or a programmable logic device FPGA), a memory 104 for storing data, and a transmission device 106 for communication functions. In addition, it may also include: a display, a keyboard, a cursor control device (such as a mouse), an input / output interface (I / O interface), a universal serial bus (USB) port (which may be included as one of the ports of the BUS bus), a network interface, a power supply and / or a camera. It can be understood by those skilled in the art that Figure 1 The structure shown is only for illustration and does not limit the structure of the above electronic device. Figure 1 More or fewer components as shown, or with Figure 1 Different configurations shown.

[0033] It should be noted that the one or more processors 102 and / or other data processing circuits described above may generally be referred to herein as "data processing circuits". The data processing circuits may be embodied in whole or in part as software, hardware, firmware, or any other combination thereof. In addition, the data processing circuit may be a single independent processing module, or may be incorporated in whole or in part into any of the other components in the computer terminal 10 (or mobile device). As involved in the embodiments of the present invention, the data processing circuit acts as a processor control (e.g., selection of a variable resistor terminal path connected to an interface).

[0034] The memory 104 can be used to store software programs and modules of application software, such as program instructions corresponding to the method for processing query statements in the embodiment of the present invention. The processor 102 executes various functional applications and data processing by running the software programs and modules stored in the memory 104, that is, the method for processing query statements described above is realized. The memory 104 can be used to store query data (such as query candidate data, query statements, query strategies, query strategy training data, query results, etc.) involved in executing the above-mentioned method for processing query statements. The memory 104 may include a high-speed random access memory, and may also include a non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some examples, the memory 104 may further include a memory remotely arranged relative to the processor 102, and these remote memories may be connected to the computer terminal 10 via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0035] The transmission device 106 is used to receive or send data via a network. The specific example of the above network may include a wireless network provided by a communication provider of the computer terminal 10. In one example, the transmission device 106 includes a network adapter (Network Interface Controller, NIC), which can be connected to other network devices through a base station so as to communicate with the Internet. In one example, the transmission device 106 can be a radio frequency (RF) module, which is used to communicate with the Internet wirelessly.

[0036] The display may be, for example, a touch screen liquid crystal display (LCD) that enables a user to interact with a user interface of the computer terminal 10 (or mobile device).

[0037] It should be noted that, in some optional embodiments, the above Figure 1 The computer device (or mobile device) shown may include hardware elements (including circuits), software elements (including computer code stored on a computer-readable medium), or a combination of hardware elements and software elements. It should be noted that Figure 1 This is merely one example of a particular embodiment and is intended to illustrate the types of components that may be present in the above-described computer device (or mobile device).

[0038] Still like Figure 1As shown, in the cloud scenario, the computer terminal 10 can interact with the cloud query interface through the transmission device 106: receive the query request transmitted by the cloud query interface, obtain the query result corresponding to the query request through the method embodiment provided by the first embodiment of the present invention, and return the query result to the cloud query interface. The interaction mode between the transmission device 106 of the computer terminal 10 and the cloud query interface can be: wired network connection, wireless network connection, etc.

[0039] Specifically, the computer terminal 10 for implementing the method for processing query statements can be used in a drug query application scenario in the health field. In the above-mentioned drug query application scenario, the query statement to be processed can be a drug query statement (for example: What is the usage and dosage of Isatis Root Granules) input by the user into the computer terminal 10 through the input / output interface in the form of natural language. The drug query statement input by the user can be transmitted to the processor 102 (which can be one or more of 102a, 102b, ..., 102n) through the BUS bus in the computer terminal 10.

[0040] In the above-mentioned drug query application scenario, the memory 104 can be used to store program instructions, which may include but are not limited to: program instructions for obtaining a drug query strategy by training multiple training statements, program instructions for determining a drug query execution plan corresponding to the drug query statement according to the drug query strategy, program instructions for determining multiple states corresponding to the drug query statement and query execution actions in each of the multiple states through the drug query execution plan, program instructions for sending a query request to a cloud query interface, and program instructions for obtaining query results from the cloud query interface.

[0041] In the above-mentioned drug query application scenario, the memory 104 can also be used to store drug query data involved in executing the above-mentioned method of processing query statements (such as drug cache data, drug query logs, drug query strategies, training statements of drug query strategies, drug query results, etc.).

[0042] In the above-mentioned drug query application scenario, the processor 102 in the computer terminal 10 can obtain the program instructions and drug query data stored in the memory 104 through the BUS bus, and execute the following method steps based on the program instructions and drug query data:

[0043] Step 1: training a plurality of preset training sentences (for example, drug query sentences pre-entered by a technician for training, or drug query sentences collected in real time by the computer terminal 10 during processing of drug query sentences and used for training) to obtain a drug query strategy;

[0044] Step 2: Determine the drug query execution plan corresponding to the drug query statement through the drug query strategy;

[0045] Step 3: Obtain drug query results according to the drug query execution plan.

[0046] Optionally, during the process of the processor 102 in the computer terminal 10 executing program instructions, the drug query data required to execute the program instructions can be obtained from the memory 104 in real time, and the drug query data, drug query log, etc. generated by executing the program instructions can be stored in the corresponding storage area in the memory 104 in real time.

[0047] Optionally, during the process of the processor 102 in the computer terminal 10 performing the second step, the multiple query states corresponding to the drug query statement input by the user can be determined by the pre-trained drug query strategy, and the query action corresponding to each query state in the multiple query states can also be determined by the pre-trained drug query strategy. Furthermore, a corresponding drug query execution plan can be generated based on the multiple query states and the query action corresponding to each query state in the multiple query states.

[0048] Optionally, during the process of the processor 102 in the computer terminal 10 executing the above-mentioned third step operation, (for example: when the drug query execution plan corresponding to the drug query statement is relatively simple, or when executing the drug query statement to query the drug cache data in the local memory 104 is sufficient to obtain the drug query result), the drug query statement can be directly executed according to the drug query execution plan to obtain the corresponding drug query result; (for example: when the drug query execution plan corresponding to the drug query statement is relatively complex, or when executing the drug query statement to query the drug cache data in the local memory 104 cannot obtain the drug query result) a drug query request can also be generated based on the drug query execution plan, and the drug query request can be uploaded to the cloud query interface through the transmission device 106, and the cloud server can perform drug query, and receive the drug query result corresponding to the drug query request through the transmission device 106.

[0049] The processor 102 of the computer terminal 10 can provide the obtained drug query result to the user through the input / output interface. The drug query result corresponds to the drug query sentence input by the user in the form of natural language. The input / output interface can display the drug query result to the user in the form of a graphical user interface of a display.

[0050] It should be noted here that, in some optional embodiments, the computer terminal 10 for implementing the method for processing query statements can also be applied to any application scenarios involving database access, management, and query, such as: drug management, patient database, medical staff file management and other application scenarios in the health field; intelligent multilingual translation, text-to-speech conversion, electronic dictionary, language database and other application scenarios in the language field; resident database, intelligent customer service, security tracking, news management, search engine, mobile Internet application software and other application scenarios in the social field; paper database, scientific research duplicate checking, digital library and other application scenarios in the scientific field; digital art exhibition, music database, audio and video plagiarism retrieval and other application scenarios in the art field.

[0051] Under the above operating environment, the present invention provides Figure 2 A method for processing a query statement is shown. Figure 2 is a flow chart of a method for processing a query statement according to an embodiment of the present invention. Figure 2 As shown, the method for processing a query statement includes:

[0052] Step S202, obtaining a query statement to be processed;

[0053] Step S204, determining a target execution plan corresponding to the query statement to be processed according to the target strategy, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine multiple states corresponding to the query statement to be processed and an execution action in each of the multiple states;

[0054] Step S206: execute the query statement to be processed according to the target execution plan to obtain the query result.

[0055] Optionally, the query statements to be processed may be statements in the form of multiple structured query languages ​​(SQL). SQL is a database query and programming language. The query statements may be used to store data and query, update and manage relational database systems.

[0056] Optionally, the target execution plan may be an execution plan corresponding to the query statement to be processed determined according to the target strategy. The execution plan may also be referred to as a plan tree, which is composed of a series of internal operators, which constitute the execution plan of the query according to a certain operation relationship. In this optional embodiment, the DBMS connects the data tables in a left-deep tree manner.

[0057] It should be noted that for SQL query q, Rel(q) can be used to represent the set of all basic relations therein. In the plan tree p corresponding to the SQL query q, each leaf node can represent a basic relation bi in Rel(q), and non-leaf nodes can represent connection operators ji (ji∈J, J represents the set of all connection operators). In particular, the relation b of a specific leaf node can also be used to specify an index type e (e∈E, E represents the set of all index types). The plan tree p will be executed in order from bottom to top.

[0058] Optionally, the generation process of the above target execution plan can be described by Markov Decision Process (MDP). The execution plan can be expressed as M = (S, A, T, r, γ), where S represents the state, A represents the action, T represents the transition probability distribution, r represents the reward function, and γ∈[0,1) represents the loss factor.

[0059] Optionally, the target execution plan may be used to determine multiple states corresponding to the query statement to be processed. The state corresponds to a time step in the execution process, and the state St corresponding to each time step t may include a first component Lt and a second component Ht, wherein the first component Lt is used to represent multiple actions that have been executed, and the second component Ht is used to represent multiple unselected relationships.

[0060] It should be noted that for any execution plan corresponding to any query statement, there is always: the first component Lt of the initial state is empty, the second component Ht of the initial state is the set Rel(q) of all possible basic relations in the query statement; the second component Ht of the terminal state is empty.

[0061] Optionally, the target execution plan can also be used to determine an action to be performed in each of the multiple states. The action can be defined by a triple (bt, et, jt), where bt represents a basic relation, et represents an index type, and jt represents a join operator. The action to be performed in each of the multiple states according to the target execution plan can be to select a corresponding basic relation, index type, and join operator in the state.

[0062] Figure 3 is a schematic diagram of an example of an optional Markov decision process according to an embodiment of the present invention, such as Figure 3 As shown in the figure, during the execution plan, for the query example Select*, the corresponding query statement is as follows:

[0063] "From T1,T2,T3,T4 Where T1.a=T2.a and T3.b=T4.b and T1.c=T3.c"

[0064] For this query statement, the plan exhaustive and state transition process is performed.

[0065] Specifically, in the process of exhaustive planning, Figure 3 As shown, multiple plan trees are enumerated. In the multiple plan trees, each leaf node can represent a basic relationship (such as Seq(T3)) or an index type (such as Index(T1), Index(T2), Index(T4)); each non-leaf node can represent a connection operator (such as JN, JH).

[0066] Specifically, in the state transition process, the initial state S0 corresponds to the query statement Select*, the first component L0 is empty, and the second component H0 is all the basic relations {T1, T2, T3, T4} corresponding to the query statement. Figure 3 As shown, each intermediate state (such as intermediate state S1, intermediate state S2, intermediate state S4) can correspond to a plan in the plan exhaustive process. In multiple intermediate states, according to the state transition sequence, the executed actions in the first component increase, and the unselected basic relations in the second component decrease accordingly. The terminal state S4 can correspond to a plan containing all basic relations in the plan exhaustive process. There are no unselected basic relations in the second component of the terminal state S4.

[0067] In particular, it is still Figure 3 As shown, in the first component of each state in the multiple states, the executed actions are represented by triples. The first element of the triple represents the basic relationship (such as T1, T2, T3, T4), the second element of the triple represents the index type (such as Index, Seq), and the third element of the triple represents the connection operator (such as NextLoop, NS, Hash).

[0068] Optionally, the strategy can be denoted as π(S), which represents a set of appropriate actions that should be performed in each possible state S. The above target strategy can be obtained by training the above multiple query statements. In particular, the target strategy can also be obtained by adjusting the pre-trained initial strategy. In particular, the multiple query statements can be a plurality of pre-specified query statements for training the initial strategy, which can also be referred to as a training workload (Training workload), denoted as Q.

[0069] Optionally, according to the target execution plan, the query statement to be processed may be executed to obtain the query result, which may be part of the data in the database corresponding to the query statement to be processed.

[0070] In an embodiment of the present invention, a query statement to be processed is first obtained, and a target execution plan corresponding to the query statement to be processed is determined according to a target strategy. The query result is obtained by executing the query statement to be processed according to the target execution plan, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine multiple states corresponding to the query statement to be processed and the execution action in each of the multiple states, thereby achieving the purpose of using a strategy based on query statement training to generate an execution plan for the query statement to optimize the query process, thereby achieving the technical effect of improving the query optimizer's strategy to obtain an optimized execution plan for the query statement to improve the query efficiency, and further solving the technical problem of high execution plan delay and low query efficiency of the query optimization method provided in the related art.

[0071] Optionally, the method for processing query statements provided by the present invention may be applied, but not limited to, to any application scenario involving database access, management, and query in the fields of health, language, society, science, and art.

[0072] In an optional embodiment, the method for processing a query statement further includes the following method steps:

[0073] Step S208, sampling multiple query statements to obtain sampled query statements, and sampling execution plans of the sampled query statements based on the initial strategy to obtain a sampled execution plan;

[0074] Step S210, using the sampling execution plan to determine the storage result of the first experience pool, the first statistical result, and the second statistical result;

[0075] Step S212, updating the initial reward function based on the storage result of the first experience pool, the first statistical result, and the second statistical result to obtain a target reward function;

[0076] Step S214, fine-tuning the initial strategy using the target reward function to obtain the target strategy.

[0077] Optionally, the above-mentioned multiple query statements may be multiple pre-specified query statements for training the initial strategy, which may also be referred to as a training workload (Training workload), denoted as Q. Based on the initial strategy, it may be a pre-trained strategy, denoted as πθ. Based on the initial strategy πθ, the multiple query statements Q are actively sampled to obtain the above-mentioned sampled query statements. The sampled query statement may include some or all of the query statements in the multiple query statements Q, and the sampled query statement may be denoted as SQL Query.

[0078] It should be noted that the above active sampling process can be based on M(C) and M(L), wherein M(C) is a cost-based MDP and M(L) is a delay-based MDP.

[0079] Optionally, in an MDP, when an action is performed at a certain state, a reward will be received as feedback, and a reward function is usually used to represent the value of the "state-action pair". For example: the reward function R(St, At) represents the reward value received as feedback when the action At is performed at the state St. One of the tasks of the MDP may be to minimize the expected reward value accumulated by the control execution process. The control process may be determined according to the corresponding strategy π. The above initial reward function rθ may be a predefined reward function corresponding to the initial strategy πθ.

[0080] Optionally, based on the above-mentioned sample query statement SQL Query, a corresponding execution plan can be obtained. Based on the above-mentioned initial strategy πθ, the execution plan corresponding to the sample query statement SQL Query can be actively sampled. Then, the above-mentioned sample execution plan is obtained, and the sample execution plan can be used to execute the above-mentioned sample query statement SQL Query and obtain experience data.

[0081] Optionally, the above experience data may include result data of executing the above sample query statement SQL Query, and may also include data obtained by analyzing and processing the result data. Specifically, the experience data may include: storage results of the first experience pool, first statistical results, and second statistical results.

[0082] Optionally, based on the storage results of the first experience pool, the first statistical results, and the second statistical results, the initial reward function can be updated to obtain the target reward function. The target reward function can be used to fine-tune the initial strategy to obtain the target strategy. In particular, updating the initial reward function can be to calibrate the initial reward function using experience data collected by active sampling; fine-tuning the initial strategy can be to convert the initial strategy.

[0083] It is easy to notice that, compared with the method used in the related art, the method provided by this embodiment can realize a mutual improvement process between the initial reward function and the initial strategy, thereby more efficiently obtaining the corresponding reinforcement learning optimal strategy to optimize the query process.

[0084] In an optional embodiment, in step S208, sampling multiple query statements to obtain sample query statements includes the following method steps:

[0085] Step S2081, performing cluster analysis on multiple query statements to obtain multiple clusters;

[0086] Step S2082: Select a query statement closest to the cluster center from each of the multiple clusters to obtain a sample query statement.

[0087] Optionally, the above-mentioned multiple query statements may be multiple pre-specified query statements for training the initial strategy, which may also be referred to as a training workload (Training workload), denoted as Q. Cluster analysis is performed on the multiple query statements Q to obtain the above-mentioned multiple clusters. Each cluster of the multiple clusters may include at least one query statement, and the at least one query statement may be a candidate query statement for obtaining a sample query statement.

[0088] Specifically, the method for obtaining the sample query statement may be: selecting, from each of the multiple clusters, a query statement closest to the center of the cluster as the sample query statement corresponding to the cluster.

[0089] Optionally, the method for clustering multiple query statements may be to use a weighted K-means algorithm. Specifically, the clustering analysis may be: clustering multiple query statements Q into k%×|Q| clusters, then selecting a query statement closest to the center of each of the k%×|Q| clusters, and then, the query statements selected from the k%×|Q| clusters may be used as sampled query statements.

[0090] It should be noted that when sampling multiple query statements and sampling execution plans, the sampling criteria may include: information volume and diversity. The information volume can be used to help the strategy determine the above-mentioned first statistical result and the above-mentioned second statistical result when executing the sampled execution plan. Diversity can improve the stability of the learning process corresponding to the reward function. The above-mentioned clustering analysis process can ensure diversity. The method of ensuring the amount of information will be described in detail below.

[0091] In an optional embodiment, in step S208, sampling the execution plan of the sample query statement based on the initial strategy to obtain the sample execution plan includes the following method steps:

[0092] Step S2083, generating multiple candidate execution plans for the sample query statement based on the initial strategy;

[0093] Step S2084, according to the weight of each candidate execution plan from the multiple candidate execution plans, select the candidate execution plan with the largest weight to obtain a sample execution plan.

[0094] Optionally, the initial strategy may be a pre-trained strategy, denoted as πθ. The sampled query statement may be a query statement obtained by actively sampling the multiple query statements based on the initial strategy πθ. Based on the initial strategy πθ, multiple candidate execution plans may be generated for the sampled query statement.

[0095] Optionally, the weight of each candidate execution plan among the multiple candidate execution plans is calculated, and according to the calculation result, the candidate execution plan with the largest weight is selected from the multiple candidate execution plans as the sampled execution plan.

[0096] For example, sampling the execution plan of the sample query statement to obtain the sample execution plan may include the following method steps:

[0097] Step S2801, extracting multiple queries from multiple query statements;

[0098] Step S2802, for each query in the multiple query statements, generate multiple execution plans according to the initial strategy πθ;

[0099] Step S2803, calculate the weight Y of each execution plan among the multiple execution plans, and select the execution plan with the largest weight Y as the sample execution plan.

[0100] In the above step S2803, the calculation method of the weight Y is shown in the following formula (1):

[0101]

[0102] In the above formula (1), τ represents the trajectory corresponding to the execution plan; St represents the state at time step t; A(s t ) represents the action corresponding to state St.

[0103] In particular, a∈A(s) π θ (a|s)log(π θ (a|s)) is the strategy π θ The entropy of is used to represent the certainty of selecting action a in state s. In addition, since τ is determined by the policy π θ The maximum probability of is determined, so the weight It can be said that the current query is in strategy π θ uncertainty in .

[0104] It should be noted that, in the above process of sampling the execution plans of the sampled query statements, as many execution plans as possible can be generated for the sampled query statements according to the initial strategy πθ to ensure the amount of information; the dropout mechanism can also be used to sample the execution plans to ensure diversity.

[0105] In an optional embodiment, in step S210, the storage result, the first statistical result and the second statistical result of the first experience pool are determined by using the sampling execution plan, including the following method steps:

[0106] Step S2101, by executing the sampling execution plan, a sampling track corresponding to the sampling query statement is obtained, wherein the sampling track includes: a plurality of transition processes, each of the plurality of transition processes is used to record a process of state transition from a current state to a next state at adjacent moments;

[0107] Step S2102: determining a storage result, a first statistical result, and a second statistical result of a first experience pool based on the sampling trajectory.

[0108] Optionally, the sampled query statement may be a query statement obtained by actively sampling the multiple query statements based on an initial strategy. The sampled execution plan may be an execution plan obtained by actively sampling the execution plan of the sampled query statement based on an initial strategy. By executing the sampled execution plan, a sampling trajectory corresponding to the sampled query statement may be obtained.

[0109] Optionally, the sampling track corresponding to the sampling query statement may include multiple transition processes. Each of the multiple transition processes may be used to record the process of state transition from the current state to the next state at adjacent moments.

[0110] It should be noted that in MDP, the state St corresponding to each time step t may include a first component Lt and a second component Ht, wherein the first component Lt is used to represent multiple actions that have been executed, and the second component Ht is used to represent multiple unselected relationships. The transition process T(S(t+1)|St,At) may be a process in which the state St is converted to S(t+1) from time step t to time step (t+1). In this transition process, once the action At is executed, the action At will be added to the first component Lt of the state St, and the selected relationship bt corresponding to the action At will be deleted from the second component Ht of the state St. At this time, the two components corresponding to the state S(t+1) will be obtained, completing the state transition process.

[0111] Optionally, based on the sampling track corresponding to the sampling query statement, the storage result of the first experience pool, the first statistical result and the second statistical result may be determined.

[0112] In an optional embodiment, in step S2102, determining the storage result, the first statistical result, and the second statistical result of the first experience pool based on the sampling trajectory includes the following method steps:

[0113] Step S32, storing the first triplet corresponding to each state in the sampling trajectory into the first experience pool, and obtaining a storage result of the first experience pool, wherein the first triplet includes: state information of each state, cost information of each state, and delay information of each state;

[0114] Step S34, storing the second triplet corresponding to the sampling trajectory into the second experience pool, and obtaining the storage result of the second experience pool, wherein the second triplet includes: trajectory information of the sampling trajectory, the cumulative cost of the sampling trajectory, and the cumulative delay of the sampling trajectory, the cumulative cost is determined by the cost information of the final state in the sampling trajectory, and the cumulative delay is determined by the delay information of the final state in the sampling trajectory;

[0115] Step S36, determining a first statistical result and a second statistical result based on whether the cumulative cost difference and the cumulative delay difference of the sampling trajectory pair meet a preset condition, wherein the sampling trajectory pair includes: any two sampling trajectories in the second experience pool.

[0116] Optionally, the sampling trajectory may be a sampling trajectory corresponding to a sampling query statement obtained by executing a sampling execution plan. The sampling trajectory may include multiple transition processes, each of which is associated with a pair of adjacent states. The first triple corresponding to each state in the sampling trajectory is stored in the first experience pool, and the storage result of the first experience pool may be determined.

[0117] Optionally, the first experience pool may be a cache area BC, and the storage result of the first experience pool may be a plurality of first triplets (St, C(St), L(St)) corresponding to a plurality of sampling trajectories τ = {S0, a0, ..., a(T-1), ST} obtained by executing the sampling execution plan, wherein St represents the state corresponding to time t in the sampling trajectory, C(St) represents the cost corresponding to state St, and L(St) represents the delay corresponding to state St.

[0118] Optionally, the second experience pool may be a cache area BT. The second triplet corresponding to the sampling trajectory is stored in the second experience pool, and the storage result of the second experience pool may be determined. The second triplet may be (τ, R(C)(τ), R(L)(τ)), where τ represents the sampling trajectory, R(C)(τ) represents the cumulative cost of the sampling trajectory τ, and R(L)(τ) represents the cumulative delay of the sampling trajectory τ. The cumulative cost R(C)(τ) is determined by the cost of the final state in the sampling trajectory τ; the cumulative delay R(L)(τ) is determined by the delay of the final state in the sampling trajectory τ.

[0119] Optionally, the sampling trajectory pair may be any two sampling trajectories in the second experience pool, which may be recorded as (τ, τ′). For the two sampling trajectories of the sampling trajectory pair, it may be determined whether the cumulative cost difference and the cumulative delay difference meet the preset conditions, and the first statistical result and the second statistical result may be determined accordingly.

[0120] In an optional embodiment, in step S36, based on the cumulative cost difference and the cumulative delay difference of the sampling trajectory pair satisfying a preset condition, determining a first statistical result includes the following method steps:

[0121] Step S361, when the accumulated cost difference and the accumulated delay difference meet the preset conditions, it is determined that there is a difference between the sampling trajectory pairs, and a first determination result is obtained;

[0122] Step S362: Count the sampled trajectory pairs with differences in the second experience pool using the first determination result to obtain a first statistical result.

[0123] Optionally, the sampling trajectory pair may be any two sampling trajectories in the second experience pool, which may be recorded as (τ, τ′). For the two sampling trajectories of the sampling trajectory pair, the above-mentioned cumulative cost difference may be the difference between the cumulative cost R(C)(τ) of trajectory τ and the cumulative cost R(C)(τ′) of trajectory τ′; the above-mentioned cumulative delay difference may be the difference between the cumulative delay R(L)(τ) of trajectory τ and the cumulative delay R(L)(τ′) of trajectory τ′.

[0124] Optionally, when the sampling trajectory pair is determined to have a difference and the above-mentioned first determination result can be obtained, the preset condition satisfied by the cumulative cost difference and the cumulative delay difference of the sampling trajectory pair can be shown as the following formula (2):

[0125] [(R(L)(τ)-R(L)(τ′))(R(C)(τ)-R(C)(τ′))]<0 Formula (2)

[0126] Optionally, the first judgment result may be: when the cumulative cost difference and the cumulative delay difference of any two sampling trajectories meet a preset condition, there is a difference between the two sampling trajectories. Using the first judgment result, statistics can be performed on multiple sampling trajectories in the second experience pool to obtain a first statistical result. The first statistical result may be multiple first quadruplets (τ, τ′, R(C)(τ), R(C)(τ′)) corresponding to multiple pairs of sampling trajectories with differences.

[0127] Optionally, the first statistical result may be stored in a cache area Cd.

[0128] In an alternative embodiment, in step S36, when the cumulative cost difference and the cumulative delay difference of the sampled trajectory pairs do not meet the preset conditions, a second statistical result is determined, including the following method steps:

[0129] Step S363, when the cumulative cost difference and the cumulative delay difference do not meet the preset conditions, it is determined that there is no difference in the sampled trajectory pair, and a second determination result is obtained;

[0130] Step S364, using the second determination result, the sampled trajectory pairs without difference in the second experience pool are statistically analyzed to obtain a second statistical result.

[0131] Optionally, the sampled trajectory pair can be any two sampled trajectories in the second experience pool, which can be denoted as (τ, τ′). For the two sampled trajectories of this sampled trajectory pair, the above cumulative cost difference can be the difference between the cumulative cost R(C)(τ) of trajectory τ and the cumulative cost R(C)(τ′) of trajectory τ′; the above cumulative delay difference can be the difference between the cumulative delay R(L)(τ) of trajectory τ and the cumulative delay R(L)(τ′) of trajectory τ′.

[0132] Optionally, when the cumulative cost difference and the cumulative delay difference of the sampled trajectory pair do not meet the preset conditions as described in the above formula (2), this sampled trajectory pair is determined to have no difference, and the above second determination result can be obtained. The second determination result can be: when the cumulative cost difference and the cumulative delay difference of any two sampled trajectories do not meet the preset conditions, then there is no difference between these two sampled trajectories.

[0133] Optionally, using this second determination result, multiple sampled trajectories in the above second experience pool can be statistically analyzed to obtain a second statistical result. The second statistical result can be multiple second quadruples (τ, τ′, R(C)(τ), R(C)(τ′)) corresponding to multiple sampled trajectory pairs without difference.

[0134] Optionally, the above second statistical result can be stored in the cache area Cp.

[0135] It should be noted that in the actual application scenario, for the sake of convenience of discussion, the following assumption can be made: for each sampled trajectory pair (τ, τ′) in the cache area Cd or the cache area Cp, there is R(L)(τ) < R(L)(τ′). Through this assumption, the repeated consideration of the sampled trajectory pairs (τ, τ′) and (τ′, τ) can be avoided.

[0136] In an alternative embodiment, in step S212, the initial reward function is updated based on the storage result of the first experience pool, the first statistical result, and the second statistical result to obtain the target reward function, including the following method steps:

[0137] Step S2121, determining a first loss function based on the first statistical result and the second statistical result, and determining a second loss function based on the storage result of the first experience pool;

[0138] Step S2122, determining a target loss function using the first loss function and the second loss function;

[0139] Step S2123, the initial reward function is updated by the target loss function to obtain the target reward function.

[0140] Optionally, the sampling trajectory pair (τ, τ′) may be any two sampling trajectories in the second experience pool. The first statistical result stored in the cache area Cd may be a plurality of first quadruplets (τ, τ′, R(C)(τ), R(C)(τ′)) corresponding to a plurality of sampling trajectory pairs with differences. The second statistical result stored in the cache area Cp may be a plurality of second quadruplets (τ, τ′, R(C)(τ), R(C)(τ′)) corresponding to a plurality of sampling trajectory pairs without differences.

[0141] Optionally, based on the first statistical result and the second statistical result, a first loss function can be determined: The first loss function As shown in the following formula (3):

[0142]

[0143] In the above formula (3), δ represents a pre-specified boundary parameter, and δ>0.

[0144] In particular, in actual application scenarios, when using the above-mentioned first loss function, all elements in the cache area Cd are considered, and the most recently added elements in the cache area Cp are sampled with a higher priority.

[0145] Optionally, the first experience pool may be a cache area BC, and the storage result of the first experience pool may be a plurality of first triples (St, C(St), L(St)) corresponding to the plurality of sampling trajectories τ={S0, a0, ..., a(T-1), ST} obtained by executing the sampling execution plan, wherein St represents the state corresponding to time t in the sampling trajectory, C(St) represents the cost corresponding to state St, and L(St) represents the delay corresponding to state St. Based on the storage result of the first experience pool, the second loss function may be determined: The second loss function As shown in the following formula (4):

[0146]

[0147] In the above formula (4), CorrCoef(,) represents the Pearson product-moment correlation coefficient, which can be used to measure The linear relationship between and L(s). In practical application scenarios, the value of CorrCoef(,) should be as close to 1 as possible.

[0148] Optionally, using the first loss function And the second loss function mentioned above Determine the target loss function The objective loss function As shown in the following formula (5):

[0149]

[0150] In the above formula (5), λ represents a sample penalty factor for controlling violation of constraints. In particular, during the training iteration, the value of λ can be gradually increased.

[0151] Optionally, through the objective loss function Update the initial reward function Rθ to get the target reward function Specifically, when training the calibration function, it is usually possible to control the target loss function Minimize and optimize Use optimized Update the initial reward function Rθ to obtain the target reward function

[0152] Optionally, a target reward function can be used Fine-tune the initial strategy πθ to obtain the target strategy In particular, fine-tuning the initial policy πθ may be transforming the initial policy.

[0153] Figure 4 is a schematic diagram of an optional query optimization process based on reinforcement learning according to an embodiment of the present invention, such as Figure 4 As shown, the query optimization process based on reinforcement learning can include two stages: in stage 1, the QO is trained using the cost, and the QO generates a plan tree for the query q and returns it to the DBMS; in stage 2, the reward function is determined using the cost and delay, and the QO is trained using the calibrated cost, and the QO actively samples the plan tree for the actively sampled query q, and returns the sampled plan tree to the DBMS.

[0154] In particular, it is still Figure 4As shown, in stage 2, the QO generates a plan tree based on the strategy transformed from the QO strategy in stage 1. While the QO actively samples the generated plan tree, it uses the plan tree to correct the reward function, thereby obtaining a calibrated cost. In addition, based on the QO in stage 2, a calibrated QO can also be obtained.

[0155] Still like Figure 4 As shown in the figure, in both the QO of stage 1 and stage 2, the action, state, vectorization and network are cyclically acted upon.

[0156] Specifically, Figure 5 is a schematic diagram of an optional strategy conversion process according to an embodiment of the present invention, such as Figure 5 As shown in the query optimization process based on reinforcement learning, the process of converting the QO strategy in stage 1 to the QO strategy in stage 2 can be: by monitoring the cost-based reward function r(C) to the delay-based reward function r(L) and its calibration intermediate quantity Based on the initial policy πθ, the conversion process from the cost-based policy π(C)* to the delay-based policy π(L)* can be determined. The above process can realize the transfer of rewards and policies from the cost domain to the delay domain. The policy π(C)* can be the policy used in the QO of stage 1, and the policy π(L)* can be the policy used in the QO of stage 2.

[0157] One embodiment of the present invention further provides a method for processing a query statement, wherein the method for processing a query statement is executed on a cloud server. Figure 6 is a flowchart of another method for processing a query statement according to an embodiment of the present invention. Figure 6 As shown, the method for processing a query statement includes:

[0158] Step S61, receiving a query statement to be processed from a client;

[0159] Step S62, determining a target execution plan corresponding to the query statement to be processed according to the target strategy, executing the query statement to be processed according to the target execution plan, and obtaining a query result, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine multiple states corresponding to the query statement to be processed and an execution action in each of the multiple states;

[0160] Step S63: Feedback the query result to the client.

[0161] Optionally, Figure 7 is a schematic diagram of a method for executing and processing query statements on a cloud server according to an embodiment of the present invention. Figure 7As shown, the client uploads the query statement to be processed to the cloud server; the cloud server determines the target execution plan corresponding to the query statement to be processed according to the target strategy, executes the query statement to be processed according to the target execution plan, and obtains the query result, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine the multiple states corresponding to the query statement to be processed and the execution action in each of the multiple states. Then, the cloud server will feedback the query result to the above client, and the final query result will be provided to the user through the client's graphical user interface.

[0162] It should be noted that the above-mentioned method for processing query statements provided in the embodiment of the present invention can be applicable to, but not limited to, any application scenario involving database access, management, and query in the fields of health, language, society, science, and art. In the above-mentioned practical application scenario, the SaaS server and the client are used to interact, and an execution plan is generated for the query statement to be processed input by the client, and then the query result is obtained and returned to the client, and the returned query result is provided to the user through the client. It should be noted that for the aforementioned method embodiments, for the sake of simple description, they are all expressed as a series of action combinations, but those skilled in the art should know that the present invention is not limited to the described order of actions, because according to the present invention, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also know that the embodiments described in the specification are all preferred embodiments, and the actions and modules involved are not necessarily required by the present invention.

[0163] Through the description of the above implementation methods, those skilled in the art can clearly understand that the method according to the above embodiment can be implemented by means of software plus a necessary general hardware platform, and of course can also be implemented by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present invention, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, a magnetic disk, or an optical disk), and includes a number of instructions for enabling a terminal device (which can be a mobile phone, a computer, a server, or a network device, etc.) to execute the methods described in each embodiment of the present invention.

[0164] Example 2

[0165] According to an embodiment of the present invention, a device for implementing the above-mentioned method for processing query statements is also provided. Figure 8 is a schematic diagram of the structure of a device for processing query statements according to an embodiment of the present invention. Figure 8 As shown, the device includes: an acquisition module 801, a determination module 802, and a processing module 803, wherein:

[0166] The acquisition module 801 is used to obtain the query statement to be processed; the determination module 802 is used to determine the target execution plan corresponding to the query statement to be processed according to the target strategy, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine multiple states corresponding to the query statement to be processed and the execution actions in each of the multiple states; the processing module 803 is used to execute the query statement to be processed according to the target execution plan to obtain the query result.

[0167] Optionally, Fig. 9 is a schematic diagram of the structure of another device for processing query statements according to an embodiment of the present invention. Fig. 9 As shown, the device includes Figure 8 In addition to all the modules shown, it also includes: a sampling module 804, which is used to sample multiple query statements to obtain sampled query statements, and to sample the execution plans of the sampled query statements to obtain sampled execution plans; a result module 805, which is used to determine the storage results, first statistical results and second statistical results of the first experience pool using the sampled execution plan; an update module 806, which is used to update the initial reward function based on the storage results, first statistical results and second statistical results of the first experience pool to obtain a target reward function; an adjustment module 807, which is used to fine-tune the initial strategy using the target reward function to obtain a target strategy.

[0168] Optionally, the sampling module 804 is further used to: perform cluster analysis on multiple query statements to obtain multiple clusters; and select a query statement closest to the center of the cluster from each of the multiple clusters to obtain a sample query statement.

[0169] Optionally, the sampling module 804 is further used to: generate multiple candidate execution plans for the sampled query statement based on the initial strategy; select the candidate execution plan with the largest weight according to the weight of each candidate execution plan in the multiple candidate execution plans to obtain the sampled execution plan.

[0170] Optionally, the above-mentioned result module 805 is also used to: obtain a sampling trajectory corresponding to the sampling query statement by executing a sampling execution plan, wherein the sampling trajectory includes: multiple transfer processes, each transfer process in the multiple transfer processes is used to record the process of state transition from a current state to a next state at adjacent moments; determine the storage result, the first statistical result and the second statistical result of the first experience pool based on the sampling trajectory.

[0171] Optionally, the above-mentioned result module 805 is also used to: store the first triplet corresponding to each state in the sampling trajectory into the first experience pool, and obtain the storage result of determining the first experience pool, wherein the first triplet includes: state information of each state, cost information of each state, and delay information of each state; store the second triplet corresponding to the sampling trajectory into the second experience pool, and obtain the storage result of determining the second experience pool, wherein the second triplet includes: trajectory information of the sampling trajectory, cumulative cost of the sampling trajectory, and cumulative delay of the sampling trajectory, the cumulative cost is determined by the cost information of the final state in the sampling trajectory, and the cumulative delay is determined by the delay information of the final state in the sampling trajectory; determine the first statistical result and the second statistical result based on whether the cumulative cost difference and the cumulative delay difference of the sampling trajectory pair meet the preset conditions, wherein the sampling trajectory pair includes: any two sampling trajectories in the second experience pool.

[0172] Optionally, the above-mentioned result module 805 is also used to: when the cumulative cost difference and the cumulative delay difference meet the preset conditions, determine that there are differences in the sampling trajectory pair to obtain a first determination result; use the first determination result to count the sampling trajectory pairs with differences in the second experience pool to obtain a first statistical result.

[0173] Optionally, the above-mentioned result module 805 is also used to: when the cumulative cost difference and the cumulative delay difference do not meet the preset conditions, determine that there is no difference in the sampling trajectory pair, and obtain a second judgment result; use the second judgment result to count the sampling trajectory pairs that do not have differences in the second experience pool to obtain a second statistical result.

[0174] Optionally, the above-mentioned update module 806 is also used to: determine a first loss function based on the first statistical result and the second statistical result, and determine a second loss function based on the storage result of the first experience pool; determine a target loss function using the first loss function and the second loss function; and update the initial reward function through the target loss function to obtain a target reward function.

[0175] It should be noted that the acquisition module 801, the determination module 802, and the processing module 803 correspond to steps S202 to S206 in Example 1, and the three modules and the corresponding steps implement the same examples and application scenarios, but are not limited to the contents disclosed in the above-mentioned Example 1. It should be noted that the above-mentioned modules, as part of the device, can be run in the computer terminal 10 provided in Example 1.

[0176] In an embodiment of the present invention, an acquisition module is first used to acquire a query statement to be processed, and then a determination module is used to determine a target execution plan corresponding to the query statement to be processed according to a target strategy. A processing module is used to execute the query statement to be processed according to the target execution plan to obtain a query result, wherein the target strategy is obtained by training a plurality of query statements, and the target execution plan is used to determine a plurality of states corresponding to the query statement to be processed and an execution action in each of the plurality of states, thereby achieving the purpose of using a strategy based on query statement training to generate an execution plan for the query statement to optimize the query process, thereby achieving the technical effect of improving the query optimizer strategy to obtain an optimized execution plan for the query statement to improve the query efficiency, thereby solving the technical problem of high execution plan delay and low query efficiency of the query optimization method provided in the related art.

[0177] It should be noted that the preferred implementation of this embodiment can refer to the relevant description in Example 1, which will not be repeated here.

[0178] Example 3

[0179] According to an embodiment of the present invention, an embodiment of an electronic device is also provided, and the electronic device may be any computing device in a computing device group. The electronic device includes: a processor and a memory, wherein:

[0180] A memory is connected to the above-mentioned processor and is used to provide the above-mentioned processor with instructions for processing the following processing steps: obtaining a query statement to be processed; determining a target execution plan corresponding to the query statement to be processed according to a target strategy, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine multiple states corresponding to the query statement to be processed and an execution action in each of the multiple states; executing the query statement to be processed according to the target execution plan to obtain a query result.

[0181] In an embodiment of the present invention, a query statement to be processed is first obtained, and a target execution plan corresponding to the query statement to be processed is determined according to a target strategy. The query result is obtained by executing the query statement to be processed according to the target execution plan, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine multiple states corresponding to the query statement to be processed and the execution action in each of the multiple states, thereby achieving the purpose of using a strategy based on query statement training to generate an execution plan for the query statement to optimize the query process, thereby achieving the technical effect of improving the query optimizer's strategy to obtain an optimized execution plan for the query statement to improve the query efficiency, and further solving the technical problem of high execution plan delay and low query efficiency of the query optimization method provided in the related art.

[0182] It should be noted that the preferred implementation of this embodiment can refer to the relevant description in Example 1, which will not be repeated here.

[0183] Example 4

[0184] The embodiment of the present invention can provide a computer terminal, which can be any computer terminal device in a computer terminal group. Optionally, in this embodiment, the computer terminal can also be replaced by a terminal device such as a mobile terminal.

[0185] Optionally, in this embodiment, the computer terminal may be located in at least one network device among a plurality of network devices of the computer network.

[0186] In this embodiment, the above-mentioned computer terminal can execute the program code of the following steps in the method for processing query statements: obtaining the query statement to be processed; determining the target execution plan corresponding to the query statement to be processed according to the target strategy, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine multiple states corresponding to the query statement to be processed and the execution actions in each of the multiple states; executing the query statement to be processed according to the target execution plan to obtain the query result.

[0187] Optionally, Fig.10 is a structural block diagram of another computer terminal according to an embodiment of the present invention. Fig.10 As shown, the computer terminal may include: one or more (only one is shown in the figure) processors 122 , a memory 124 , and a peripheral interface 126 .

[0188] Among them, the memory can be used to store software programs and modules, such as the program instructions / modules corresponding to the method and device for processing query statements in the embodiments of the present invention. The processor executes various functional applications and data processing by running the software programs and modules stored in the memory, that is, realizing the above-mentioned method for processing query statements. The memory may include a high-speed random access memory, and may also include a non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory may further include a memory remotely arranged relative to the processor, and these remote memories may be connected to the computer terminal via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0189] The processor can call the information and application stored in the memory through the transmission device to execute the following steps: obtain the query statement to be processed; determine the target execution plan corresponding to the query statement to be processed according to the target strategy, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine multiple states corresponding to the query statement to be processed and the execution actions in each of the multiple states; execute the query statement to be processed according to the target execution plan to obtain the query result.

[0190] Optionally, the processor may also execute the program code of the following steps: sampling multiple query statements to obtain sampled query statements, and sampling the execution plans of the sampled query statements to obtain sampled execution plans; using the sampled execution plan to determine the storage results, first statistical results, and second statistical results of the first experience pool; updating the initial reward function based on the storage results, first statistical results, and second statistical results of the first experience pool to obtain a target reward function; and using the target reward function to fine-tune the initial strategy to obtain a target strategy.

[0191] Optionally, the processor may further execute program code of the following steps: performing cluster analysis on multiple query statements to obtain multiple clusters; and selecting a query statement closest to the center of the cluster from each of the multiple clusters to obtain a sample query statement.

[0192] Optionally, the processor may also execute program code of the following steps: generating multiple candidate execution plans for the sampled query statement based on the initial strategy; selecting the candidate execution plan with the largest weight according to the weight of each candidate execution plan in the multiple candidate execution plans to obtain a sampled execution plan.

[0193] Optionally, the processor may also execute the program code of the following steps: obtaining a sampling trajectory corresponding to a sampling query statement by executing a sampling execution plan, wherein the sampling trajectory includes: a plurality of transfer processes, each of the plurality of transfer processes being used to record a process of state transition from a current state to a next state at adjacent moments; and determining a storage result, a first statistical result, and a second statistical result of a first experience pool based on the sampling trajectory.

[0194] Optionally, the processor may also execute the following steps of program code: storing a first triplet corresponding to each state in the sampling trajectory into a first experience pool, and obtaining a storage result for determining the first experience pool, wherein the first triplet includes: state information of each state, cost information of each state, and delay information of each state; storing a second triplet corresponding to the sampling trajectory into a second experience pool, and obtaining a storage result for determining the second experience pool, wherein the second triplet includes: trajectory information of the sampling trajectory, cumulative cost of the sampling trajectory, and cumulative delay of the sampling trajectory, wherein the cumulative cost is determined by the cost information of the final state in the sampling trajectory, and the cumulative delay is determined by the delay information of the final state in the sampling trajectory; determining a first statistical result and a second statistical result based on whether the cumulative cost difference and the cumulative delay difference of the sampling trajectory pair meet preset conditions, wherein the sampling trajectory pair includes: any two sampling trajectories in the second experience pool.

[0195] Optionally, the processor may also execute the program code of the following steps: when the cumulative cost difference and the cumulative delay difference meet preset conditions, determine that there are differences in the sampling trajectory pair and obtain a first determination result; use the first determination result to perform statistics on the sampling trajectory pairs with differences in the second experience pool to obtain a first statistical result.

[0196] Optionally, the processor may also execute the program code of the following steps: when the cumulative cost difference and the cumulative delay difference do not meet the preset conditions, determining that there is no difference in the sampling trajectory pair, and obtaining a second judgment result; using the second judgment result to perform statistics on the sampling trajectory pairs that do not have differences in the second experience pool, and obtaining a second statistical result.

[0197] Optionally, the processor may also execute the following program code: determining a first loss function based on the first statistical result and the second statistical result, and determining a second loss function based on the storage result of the first experience pool; determining a target loss function using the first loss function and the second loss function; and updating the initial reward function through the target loss function to obtain a target reward function.

[0198] In an embodiment of the present invention, a query statement to be processed is first obtained, and a target execution plan corresponding to the query statement to be processed is determined according to a target strategy. The query result is obtained by executing the query statement to be processed according to the target execution plan, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine multiple states corresponding to the query statement to be processed and the execution action in each of the multiple states, thereby achieving the purpose of using a strategy based on query statement training to generate an execution plan for the query statement to optimize the query process, thereby achieving the technical effect of improving the query optimizer's strategy to obtain an optimized execution plan for the query statement to improve the query efficiency, and further solving the technical problem of high execution plan delay and low query efficiency of the query optimization method provided in the related art.

[0199] It can be understood by those skilled in the art that Fig.10 The structure shown is for illustration only, and the computer terminal may also be a smart phone (such as an Android phone, an iOS phone, etc.), a tablet computer, a PDA, a mobile Internet device (Mobile Internet Devices, MID), a PAD, or other terminal devices. Fig.10 The structure of the electronic device is not limited. For example, the computer terminal may also include Fig.10 More or fewer components (such as network interfaces, display devices, etc.) shown in, or having Fig.10 Different configurations shown.

[0200] A person of ordinary skill in the art can understand that all or part of the steps in the various methods of the above embodiments can be completed by instructing the hardware related to the terminal device through a program, and the program can be stored in a computer-readable storage medium, and the storage medium may include: a flash drive, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk, etc.

[0201] According to an embodiment of the present invention, an embodiment of a storage medium is also provided. Optionally, in this embodiment, the storage medium can be used to store the program code executed by the method for processing query statements provided in the above embodiment 1.

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

[0203] Optionally, in this embodiment, the storage medium is configured to store program code for executing the following steps: obtaining a query statement to be processed; determining a target execution plan corresponding to the query statement to be processed according to a target strategy, wherein the target strategy is obtained by training multiple query statements, and the target execution plan is used to determine multiple states corresponding to the query statement to be processed and an execution action in each of the multiple states; executing the query statement to be processed according to the target execution plan to obtain a query result.

[0204] Optionally, in this embodiment, the storage medium is configured to store program codes for executing the following steps: sampling multiple query statements to obtain sampled query statements, and sampling execution plans of the sampled query statements to obtain sampled execution plans; using the sampled execution plan, determining the storage results, first statistical results, and second statistical results of the first experience pool; updating the initial reward function based on the storage results, first statistical results, and second statistical results of the first experience pool to obtain a target reward function; and using the target reward function to fine-tune the initial strategy to obtain a target strategy.

[0205] Optionally, in this embodiment, the storage medium is configured to store program codes for executing the following steps: performing cluster analysis on multiple query statements to obtain multiple clusters; and selecting a query statement closest to the cluster center from each of the multiple clusters to obtain a sample query statement.

[0206] Optionally, in this embodiment, the storage medium is configured to store program code for executing the following steps: generating multiple candidate execution plans for the sampled query statement based on the initial strategy; selecting the candidate execution plan with the largest weight according to the weight of each candidate execution plan in the multiple candidate execution plans to obtain a sampled execution plan.

[0207] Optionally, in this embodiment, the storage medium is configured to store program code for executing the following steps: obtaining a sampling trajectory corresponding to the sampling query statement by executing a sampling execution plan, wherein the sampling trajectory includes: multiple transfer processes, each of the multiple transfer processes is used to record the process of state transition from a current state to a next state at adjacent moments; determining the storage result, the first statistical result, and the second statistical result of the first experience pool based on the sampling trajectory.

[0208] Optionally, in this embodiment, the storage medium is configured to store program codes for executing the following steps: storing a first triplet corresponding to each state in the sampling trajectory into a first experience pool to obtain a storage result for determining the first experience pool, wherein the first triplet includes: state information of each state, cost information of each state, and delay information of each state; storing a second triplet corresponding to the sampling trajectory into a second experience pool to obtain a storage result for determining the second experience pool, wherein the second triplet includes: trajectory information of the sampling trajectory, cumulative cost of the sampling trajectory, and cumulative delay of the sampling trajectory, wherein the cumulative cost is determined by the cost information of the final state in the sampling trajectory, and the cumulative delay is determined by the delay information of the final state in the sampling trajectory; determining a first statistical result and a second statistical result based on whether the cumulative cost difference and the cumulative delay difference of the sampling trajectory pair meet preset conditions, wherein the sampling trajectory pair includes: any two sampling trajectories in the second experience pool.

[0209] Optionally, in this embodiment, the storage medium is configured to store program code for executing the following steps: when the cumulative cost difference and the cumulative delay difference meet preset conditions, determine that there are differences in the sampling trajectory pairs, and obtain a first determination result; use the first determination result to count the sampling trajectory pairs with differences in the second experience pool to obtain a first statistical result.

[0210] Optionally, in this embodiment, the storage medium is configured to store program code for executing the following steps: when the cumulative cost difference and the cumulative delay difference do not meet the preset conditions, determine that there is no difference in the sampling trajectory pair, and obtain a second judgment result; use the second judgment result to count the sampling trajectory pairs that do not have differences in the second experience pool to obtain a second statistical result.

[0211] Optionally, in this embodiment, the storage medium is configured to store program codes for performing the following steps: determining a first loss function based on a first statistical result and a second statistical result, and determining a second loss function based on the storage results of the first experience pool; determining a target loss function using the first loss function and the second loss function; and updating the initial reward function by the target loss function to obtain a target reward function.

[0212] The serial numbers of the above embodiments of the present invention are only for description and do not represent the advantages or disadvantages of the embodiments.

[0213] In the above embodiments of the present invention, the description of each embodiment has its own emphasis. For parts that are not described in detail in a certain embodiment, reference can be made to the relevant descriptions of other embodiments.

[0214] In the several embodiments provided by the present invention, it should be understood that the disclosed technical content can be implemented in other ways. Among them, the device embodiments described above are only schematic, for example, the division of the units is only a logical function division, and there may be other division methods in actual implementation, for example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point, the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of units or modules, which can be electrical or other forms.

[0215] 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 distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0216] In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0217] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a number of instructions for a computer device (which can be a personal computer, a server or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), mobile hard disk, magnetic disk or optical disk and other media that can store program codes.

[0218] The above is only a preferred embodiment of the present invention. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principle of the present invention. These improvements and modifications should also be regarded as the scope of protection of the present invention.

Claims

1. A method for processing a query statement, characterized in that: include: Get the query statement to be processed; Sampling multiple query statements to obtain sampled query statements, and sampling execution plans of the sampled query statements based on the initial strategy to obtain a sampled execution plan; Using the sampling execution plan to update the initial reward function to obtain a target reward function; Using the target reward function to fine-tune the initial strategy to obtain a target strategy; Determine a target execution plan corresponding to the query statement to be processed according to the target strategy, wherein the target execution plan is used to determine multiple states corresponding to the query statement to be processed and an execution action in each of the multiple states; The query statement to be processed is executed according to the target execution plan to obtain a query result.

2. The method according to claim 1, characterized in that The initial reward function is updated using the sampling execution plan to obtain the target reward function including: Determine the storage result, the first statistical result and the second statistical result of the first experience pool by using the sampling execution plan; The initial reward function is updated based on the storage result of the first experience pool, the first statistical result, and the second statistical result to obtain a target reward function.

3. The method according to claim 2, characterized in that Sampling the multiple query statements to obtain the sampled query statements includes: Performing cluster analysis on the multiple query statements to obtain multiple clusters; A query statement closest to the center of each cluster is selected from the plurality of clusters to obtain the sample query statement.

4. The method according to claim 2, characterized in that: Sampling the execution plan of the sampled query statement based on the initial strategy to obtain the sampled execution plan includes: generating a plurality of candidate execution plans for the sample query statement based on the initial strategy; According to the weight of each candidate execution plan among the multiple candidate execution plans, a candidate execution plan with the largest weight is selected to obtain the sample execution plan.

5. The method according to claim 2, characterized in that: Determining the storage result of the first experience pool, the first statistical result, and the second statistical result by using the sampling execution plan includes: By executing the sampling execution plan, a sampling track corresponding to the sampling query statement is obtained, wherein the sampling track includes: a plurality of transition processes, each of the plurality of transition processes is used to record a process of state transition from a current state to a next state at adjacent moments; A storage result of the first experience pool, the first statistical result, and the second statistical result are determined based on the sampling trajectory.

6. The method according to claim 5, characterized in that Determining the storage result of the first experience pool, the first statistical result, and the second statistical result based on the sampling trajectory includes: The first triplet corresponding to each state in the sampling trajectory is stored in the first experience pool to obtain a storage result of the first experience pool, wherein the first triplet includes: state information of each state, cost information of each state, and delay information of each state; The second triplet corresponding to the sampling trajectory is stored in the second experience pool to obtain a storage result of the second experience pool, wherein the second triplet includes: trajectory information of the sampling trajectory, the cumulative cost of the sampling trajectory, and the cumulative delay of the sampling trajectory, the cumulative cost is determined by the cost information of the final state in the sampling trajectory, and the cumulative delay is determined by the delay information of the final state in the sampling trajectory; The first statistical result and the second statistical result are determined based on whether the accumulated cost difference and the accumulated delay difference of the sampling trajectory pair meet preset conditions, wherein the sampling trajectory pair includes: any two sampling trajectories in the second experience pool.

7. The method according to claim 6, characterized in that Based on the cumulative cost difference and the cumulative delay difference of the sampling trajectory pair satisfying the preset condition, determining the first statistical result includes: When the accumulated cost difference and the accumulated delay difference meet the preset condition, determining that there is a difference between the sampling trajectory pairs, and obtaining a first determination result; The first determination result is used to perform statistics on the sampling trajectory pairs with differences in the second experience pool to obtain the first statistical result.

8. The method according to claim 6, characterized in that Based on the fact that the accumulated cost difference and the accumulated delay difference of the sampling trajectory pair do not meet the preset condition, determining the second statistical result includes: When the accumulated cost difference and the accumulated delay difference do not meet the preset condition, determining that there is no difference between the sampling trajectory pairs, and obtaining a second determination result; The second determination result is used to perform statistics on the sampling trajectory pairs that do not have differences in the second experience pool to obtain the second statistical result.

9. The method according to claim 2, characterized in that: The initial reward function is updated based on the storage result of the first experience pool, the first statistical result, and the second statistical result to obtain the target reward function, including: Determine a first loss function based on the first statistical result and the second statistical result, and determine a second loss function based on the storage result of the first experience pool; Determine a target loss function using the first loss function and the second loss function; The initial reward function is updated by using the target loss function to obtain the target reward function.

10. A device for processing a query statement, characterized in that: include: An acquisition module is used to obtain query statements to be processed; A determination module is used to sample multiple query statements to obtain sampled query statements, and to sample execution plans of the sampled query statements based on an initial strategy to obtain a sampled execution plan; Using the sampling execution plan to update the initial reward function to obtain a target reward function; Using the target reward function to fine-tune the initial strategy to obtain a target strategy; Determine a target execution plan corresponding to the query statement to be processed according to the target strategy, wherein the target execution plan is used to determine multiple states corresponding to the query statement to be processed and an execution action in each of the multiple states; The processing module is used to execute the query statement to be processed according to the target execution plan to obtain the query result.

11. A storage medium, characterized in that: The storage medium includes a stored program, wherein when the program is executed, the device where the storage medium is located is controlled to execute the method for processing query statements according to any one of claims 1 to 9.

12. A system for processing a test task, characterized in that: include: processor; as well as A memory, connected to the processor, configured to provide the processor with instructions for processing the following processing steps: Step 1, obtain the query statement to be processed; Step 2: sampling multiple query statements to obtain sampled query statements, and sampling execution plans of the sampled query statements based on the initial strategy to obtain a sampled execution plan; Using the sampling execution plan to update the initial reward function to obtain a target reward function; Using the target reward function to fine-tune the initial strategy to obtain a target strategy; Determine a target execution plan corresponding to the query statement to be processed according to the target strategy, wherein the target execution plan is used to determine multiple states corresponding to the query statement to be processed and an execution action in each of the multiple states; Step 3: execute the query statement to be processed according to the target execution plan to obtain the query result.

Images