A method, device and equipment for optimizing database statements

Abstract

The embodiments of this specification provide a method, device, and equipment for optimizing database statements, which can be applied to the field of artificial intelligence technology. The method includes: receiving a target database statement; performing semantic parsing on the target database statement to obtain semantic information; the semantic information is used to represent the meaning and association relationship of the target database statement; determining the analysis features of the target database statement according to the semantic information; the analysis features are used to represent the features targeted by statement optimization; based on the analysis features, using preset optimization rules to determine the optimization strategy of the target database statement; combining the execution status of the database statement, and optimizing the target database statement through the optimization strategy. The above method ensures the execution effect of the database statement, reduces the possibility of corresponding problems occurring, and is beneficial to the execution of corresponding services in subsequent application processes.

Description

Technical Field

[0001] The embodiments of this specification relate to the field of artificial intelligence technology, and particularly to a method, device, and equipment for optimizing database statements. Background Art

[0002] With the development of computer and big data technologies, people can use databases to store and manage large amounts of data, and the invocation of data is becoming more and more frequent. Correspondingly, by writing corresponding database statements, it is possible to effectively achieve requirements such as querying, associating, and statistics for data, thereby facilitating the acquisition of data or corresponding information, facilitating the execution of corresponding services, and further improving the user experience.

[0003] However, when some data analysts write database statements to execute data extraction and analysis for databases, they do not pay attention to the writing specifications of the statements, resulting in poor performance of the statements, generating a large amount of inefficient load on the database, thereby blocking the operation of other processes, and even possibly causing serious problems such as database downtime. And in the case of a large number of database statements, it is also difficult to monitor each database statement one by one. Therefore, there is an urgent need for a method that can automatically optimize database statements to improve the execution effect. Summary of the Invention

[0004] The purpose of the embodiments of this specification is to provide a method, device, and equipment for optimizing database statements to solve the problem of how to optimize the execution effect of database statements.

[0005] To solve the above technical problems, the embodiments of this specification provide a method for optimizing database statements. The database statements are used to implement corresponding program instructions based on a database. The method includes: receiving a target database statement; performing semantic parsing on the target database statement to obtain semantic information. The semantic information is used to represent the meaning and association relationship of the target database statement; determining the analysis features of the target database statement according to the semantic information. The analysis features are used to represent the features targeted by statement optimization; based on the analysis features, using a preset optimization rule to determine an optimization strategy for the target database statement; combining the execution status of the database statement, and optimizing the target database statement through the optimization strategy.

[0006] An embodiment of this specification also proposes a database statement optimization device. The database statement is used to implement corresponding program instructions based on a database. The device includes: a target database statement receiving module, configured to receive a target database statement; a semantic parsing module, configured to perform semantic parsing on the target database statement to obtain semantic information, where the semantic information is used to represent the meaning and association relationship of the target database statement; an analysis feature determining module, configured to determine the analysis feature of the target database statement according to the semantic information, where the analysis feature is used to represent the feature targeted by statement optimization; an optimization strategy determining module, configured to determine the optimization strategy of the target database statement based on the analysis feature by using a preset optimization rule; and a target database statement optimization module, configured to optimize the target database statement through the optimization strategy in combination with the execution status of the database statement.

[0007] An embodiment of this specification also proposes a database statement optimization device, including a memory and a processor. The memory is configured to store computer program instructions. The processor is configured to execute the computer program instructions to implement the following steps: receive a target database statement; perform semantic parsing on the target database statement to obtain semantic information, where the semantic information is used to represent the meaning and association relationship of the target database statement; determine the analysis feature of the target database statement according to the semantic information, where the analysis feature is used to represent the feature targeted by statement optimization; determine the optimization strategy of the target database statement based on the analysis feature by using a preset optimization rule; and optimize the target database statement through the optimization strategy in combination with the execution status of the database statement.

[0008] As can be seen from the technical solutions provided by the embodiments of this specification above, after obtaining a database statement, the embodiments of this specification first perform semantic parsing on the database statement to obtain semantic information, so as to determine the meaning and association relationship of the statement, and then obtain the corresponding analysis feature in combination with the semantic information to determine the feature that needs to be optimized, so that the corresponding optimization strategy can be determined based on the analysis feature by using a preset optimization rule, and finally, the database statement can be optimized by using the optimization strategy in combination with the execution status of the database statement. Through the above method, the analysis of the database statement is realized to determine the feature that needs to be optimized, and the optimization of the database statement is realized by setting the corresponding strategy, which ensures the execution effect of the database statement, reduces the possibility of corresponding problems occurring, and is beneficial to the execution of the corresponding business in the subsequent application process. Description of the Drawings

[0009] To more clearly illustrate the technical solutions in the embodiments of this specification or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the drawings in the following description are only some embodiments recorded in this specification. For those of ordinary skill in the art, without creative efforts, other drawings can also be obtained based on these drawings.

[0010] Figure 1 It is a flowchart of a method for optimizing database statements in an embodiment of this specification;

[0011] Figure 2 It is a schematic flowchart of semantic parsing of database statements in an embodiment of this specification;

[0012] Figure 3 It is a schematic structural diagram of a performance analysis model in an embodiment of this specification;

[0013] Figure 4 It is a schematic flowchart of model-based performance analysis in an embodiment of this specification;

[0014] Figure 5 It is a schematic diagram of the construction of a loss function in an embodiment of this specification;

[0015] Figure 6 It is a schematic flowchart of optimizing an execution queue in an embodiment of this specification;

[0016] Figure 7 It is a schematic flowchart of optimizing database statements in an embodiment of this specification;

[0017] Figure 8 It is a module diagram of a device for optimizing database statements in an embodiment of this specification;

[0018] Figure 9 It is a structural diagram of a device for optimizing database statements in an embodiment of this specification. Detailed implementation manners

[0019] The following will clearly and completely describe the technical solutions in the embodiments of this specification in conjunction with the drawings in the embodiments of this specification. Obviously, the described embodiments are only some embodiments of this specification, rather than all embodiments. Based on the embodiments in this specification, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of this specification.

[0020] To solve the above technical problems, a method for optimizing database statements in an embodiment of this specification is introduced. The execution subject of the method for optimizing database statements is a device for optimizing database statements, and the device for optimizing database statements includes but is not limited to servers, industrial control computers, personal computers, etc. AsFigure 1 As shown in the figure, the database statement optimization method may include the following specific implementation steps.

[0021] S110: Receive a target database statement.

[0022] The target database statement is the database statement that needs to be optimized and is used to implement corresponding program instructions based on the database. For example, queries, statistics, associations, etc. of corresponding data are implemented based on the target database statement.

[0023] Specifically, the target database statement may be an SQL (Structured Query Language) statement, which is a database query and programming language used to access data and query, update, and manage relational database systems. In practical applications, the target database statement may also be other types of statements, and there is no limitation on this.

[0024] The target database statement may be a statement that needs to be prominently optimized manually input by the user, or it may be data obtained in large quantities when processing a large number of database statements. There is no limitation on this.

[0025] S120: Perform semantic parsing on the target database statement to obtain semantic information; the semantic information is used to represent the meaning and association relationship of the target database statement.

[0026] The semantic information is used to represent the meaning and association relationship of the target database statement. By obtaining the semantic information, the specific meaning of the target database statement can be better understood, so that the statement can be optimized while ensuring that the target database statement realizes its original function.

[0027] In some embodiments, when performing semantic parsing, semantic parsing may be performed on the target database statement to obtain a semantic tree; the semantic tree includes at least one of table objects, filtering conditions, and association levels, and then table association information and index information are constructed based on the semantic tree.

[0028] Specifically, the sqlparse method of the Python library can be used to perform semantic parsing on the SQL statement to form an SQL semantic tree, obtain table objects, filtering conditions, association levels, etc., and then extract the table objects involved in the SQL semantic tree, generate a table list in the association order and number it. By querying the database statistics table, obtain the data volume of the current statement of this table, and at the same time obtain the table structure and index situation of this table to form the index situation of this table and the data density index (discrimination degree) of each index field.

[0029] In some embodiments, to facilitate semantic parsing, the target database statement may be segmented first to obtain at least one sub-statement, and then format normalization processing may be performed on each sub-statement respectively to adjust each sub-statement to a more standard format. The format normalization processing includes at least one of: converting keywords to uppercase, deleting comments, and indentation processing.

[0030] Illustrated with a specific example, the sqlparse.split method of the Python library can be used to perform statement-level splitting on the SQL statement, and it can be sequentially split into sql context1, sql context2, and so on. For the split statements, the keyword_case and identifier_case methods of the sqlparse.format function can be used to convert the keywords in the SQL to uppercase, and / or the strip_comments=true method of the sqlparse.format function can be used to delete the comments in the SQL, and / or the reindent=Ture configuration of the sqlparse.format function can be used to optimize the sql statement to form a standardized indentation.

[0031] In some embodiments, after obtaining the semantic information, the execution plan and historical running records of the target database statement may also be obtained. The execution plan can be used to represent the corresponding execution plan currently utilized for the target database statement, and the historical running record is used to represent the corresponding record of previously running the target database statement.

[0032] Specifically, the execution plan generated after parsing the SQL can be extracted to obtain the expected execution plan of the statement generated based on the statistical information of the database, and then it is analyzed whether this SQL has been run historically. For example, the parsed sql_id of this SQL is obtained, and it is retrieved from the v$sql in the historical database or the relevant system tables of the big data platform, and relevant information such as the execution time consumption statistics of the historical sql is obtained.

[0033] Combined with the example of pre-parsing the SQL statement in the appendix Figure 2 The above process is further summarized and illustrated. First, based on step S210, after receiving the SQL input by the customer before the query, step S220 is executed to split the SQL statement, and then step S230 is executed to format the SQL statement. For the formatted SQL statement, step S240 is taken to extract the table and index objects and analysis reports, and then step S250 is executed for pre-parsing and extraction of the execution plan, and finally step S260 is executed to perform statistics on the historical execution time consumption to complete the pre-processing of the SQL statement.

[0034] S130: Determine the analysis features of the target database statement according to the semantic information; the analysis features are used to represent the features targeted by statement optimization.

[0035] After obtaining the semantic information, the analysis features can be determined in combination with the semantic information. The analysis features can be pre-set features that can optimize statements. By comparing with the semantic information, the analysis features that can optimize the target database statement can be determined.

[0036] In some embodiments, the analysis features include at least one of statement features, statistical information features, association features, execution plan cost features, and historical statement execution features.

[0037] Specifically, for statement features, corresponding feature variables are formed by analyzing the keywords at the statement level of sql_context; for statistical information features, feature variables at the table and field levels are obtained by querying the statistical information or the number of records of the involved tables; for association features, the association features between tables and the features of single-table scans are obtained through the query execution plan; for execution plan cost features, the cost data, returned rows, discrimination, etc. features of the execution plan are obtained through the query execution plan; for historical statement execution features, indicators such as the historical execution time and waiting events of the statement are obtained through the system department. For the specific introduction of the above analysis features, please refer to the description in Table 1 below.

[0038]

[0039]

[0040] Table 1

[0041] By obtaining the analysis features, corresponding optimization strategies can be effectively set for the analysis features in subsequent steps, ensuring the effective optimization of the target database statement.

[0042] S140: Based on the analysis features, use the preset optimization rules to determine the optimization strategy of the target database statement.

[0043] The preset optimization rules can be pre-set rules for optimizing statements, which can analyze the analysis features to determine the actual optimization method applied to the target database statement.

[0044] In some embodiments, the preset optimization rules can correspond to a statement performance analysis model. Correspondingly, when determining the optimization strategy, the analysis features can be input into a pre-trained statement performance analysis model to obtain the performance parameters of the target database statement, and then the optimization strategy corresponding to the target database can be determined according to the performance parameters.

[0045] When determining the optimization strategy using the statement performance analysis model, the reinforcement learning A3C model framework can be adopted. A random model is used to replace the traditional DQN (deterministic model) to construct a neural network, and methods such as parallel asynchronous training framework, network structure optimization, and Actor-Critic evaluation point optimization are used. The Actor is based on the policy function, responsible for generating Actions and continuously interacting with the environment to obtain samples for training. The Critic is based on the value function, responsible for evaluating the performance of the Actor, and combining the feedback of the actual operation, and guiding the Actor to perform the next action, and timely adapting to the accuracy of SQL performance recognition. This model can be closer to the human evaluation method for SQL performance optimization.

[0046] During implementation, this multi-classification decision problem needs to be converted into a reinforcement learning scenario. First, define the four elements of reinforcement learning (S, A, P, R), where they are State, Action, Reward, and Policy respectively. Among them, the State includes the analyzed features collected; the Action includes constructing a random neural network SQL performance evaluation model based on feature engineering, generating the initial mean and differential vectors using the Gaussian distribution, and completing the Monte Carlo simulation in the form of A3C multi-parallel simulation. In this state, identify which SQLs have efficiency problems, perform optimization analysis and adjustment instruction operations, and for those without efficiency problems, perform actual execution operations. The Reward includes the difference between the running cost calculated by the current SQL efficiency evaluation model and the running cost after actual submission, that is, the reward obtained after accurately evaluating the SQL performance. The Policy includes predicting the optimization time generated after each SQL tuning action as the reward value, and the SQL performance problem action corresponding to the highest reward value, and the corresponding SQL performance analysis model parameters are the estimated parameters used in this SQL performance analysis model.

[0047] To further explain the statement performance analysis model, the statement performance analysis model is obtained through the following method: inputting sample features into a pre-set common neural network model; the common neural network model includes at least two execution threads; the execution threads respectively use their own sample features to interact to obtain empirical data; using the execution threads to calculate the corresponding loss function gradients according to the empirical data; optimizing the statement performance analysis model based on the loss function gradients.

[0048] Specifically, combined with the appendix Figure 3For the structural schematic diagram of model implementation, an exemplary description of the model acquisition process is provided. These features are input into a common neural network model, which includes the functions of an Actor network and a Critic network. There are n worker queues below, and each thread has the same network structure as the common neural network. Each thread will independently interact with the environment to obtain experience data, and these threads do not interfere with each other and run independently.

[0049] After each thread interacts with the environment to a certain amount of data, it calculates the gradient of the neural network loss function in its own thread. However, these gradients do not update the neural network in its own thread, but update the common neural network. That is, n threads will independently use the accumulated gradients to update the neural network model parameters of the common part. Every once in a while, the thread will update the parameters of its own neural network to the parameters of the common neural network, thereby guiding subsequent environment interactions. Thus, the reward value under the current network parameters can be calculated.

[0050] Select the estimation result with the highest reward value predicted by the A3C neural network parameters of the common part as the current SQL performance optimization strategy (Policy) for execution. After execution, the actual reward value can be calculated according to the definition of the reward (Reward) in the historical SQL running situation record (S, A, P, R).

[0051] After each prediction, the current state State, the calculated Action (queuename), and the estimated reward value (Reward) are stored in the replay memory unit. And the state State is initialized, an action is selected based on the policy, and the action is executed to obtain the reward and a new state.

[0052] The structure of the loss function is shown in Figure 4 As shown, where the loss function = policy gradient loss + value residual + policy entropy, that is, the TD-error shown in the figure = Policy_loss + Value_loss + Entropy_Regularization, and then the parameters of the neural network are updated by using the gradient descent method through the backpropagation of the neural network to achieve the purpose of minimizing the loss function.

[0053] The following combines with the appendix Figure 5A specific exemplary description of the process of determining an optimization strategy based on analysis features is as follows. First, based on step S510, the establishment of feature engineering is completed to obtain corresponding analysis features. Then, step S520 is executed to train the neural network in a parallel asynchronous manner. Next, step S530 is executed to update the neural network parameters through multi-threading. After the parameter training is completed, step S540 is executed to estimate the reward value and select the optimal strategy. Then, step S550 is executed to minimize the loss function. Finally, step S560 is executed to complete the output of the optimization analysis result.

[0054] The optimization strategy is the determined strategy used to be implemented on the target database statement to improve the running state of the target database statement and reduce the redundancy degree. Among them, the optimization strategy includes an optimization strategy based on the statement and an optimization strategy based on the execution plan.

[0055] The optimization strategy based on the statement includes at least one of the following: the outer join rewrite strategy for subqueries, the rewrite strategy for repeated access to multiple tables, the driving table adjustment strategy, the table relationship rewrite strategy, the table data rewrite strategy, the index rewrite strategy, and the multi-table association relationship rewrite strategy; the optimization strategy based on the execution plan includes at least one of the following: the execution plan rewrite strategy based on historical execution time and the table access strategy based on the correspondence between statistical information and physical storage.

[0056] Specifically, for the implementation form of the above optimization strategy, for the optimization strategy based on the statement, it can be that the subquery is rewritten as an outer join; the repeated access to multiple tables is rewritten as Within; the selection and adjustment of the driving table; IN and Not IN are changed to Exists and Not exists; the table data is mapped and trimmed; the rewrite of the index and partition implementation caused by adding functions to the index key and partition key; the temporary table cache rewrite for multi-table association. For the optimization rule based on the execution plan, it can be the optimal execution plan based on historical execution time to generate hints and bind the optimal execution plan; when the statistical information is inconsistent with the physical storage, rewrite the table access strategy.

[0057] In practical applications, the implementation method of the corresponding optimization strategy can also be adjusted according to specific requirements to make it more in line with the application requirements, which is not limited to the above examples and will not be elaborated here.

[0058] S150: Combine the execution state of the database statement and optimize the target database statement through the optimization strategy.

[0059] After obtaining the optimization strategy, the execution state of the database statement can be combined, and the optimization strategy can be used to optimize the target database statement.

[0060] The execution status is used to describe the application situation of the current target database statement, so that the optimization of the statement can be realized in combination with specific application methods, and the optimization effect can be guaranteed.

[0061] In some embodiments, the execution status includes at least one of data volume related features, execution plan related features, system resource features, job running features, job waiting features, and historical features.

[0062] The specific optimization process can Figure 6 As shown, each step is executed in sequence based on the execution order shown in the figure. First, based on step S610, obtain the data volume related features of the SQL, including the statistical information and record count of the dependent tables, the estimated IO situation, the discrimination degree of the index, whether the data distribution is balanced, etc.

[0063] Execute step S620 to obtain the SQL execution plan related features, such as the table access method (full table or index), the Join method of the table (Hash join or Nestloops), and the number of nested levels of the table association.

[0064] Execute step S630 to obtain the system resource features. At the current predicted time point, how many jobs are running on the system, the total memory and CPU used, and how much memory and CPU are remaining.

[0065] Execute step S640 to view the features of the running time and remaining running time of other jobs.

[0066] Execute step S650 to view the features of the waiting time and estimated running time of other waiting jobs.

[0067] Execute step S660 to obtain the SQL historical running features, that is, the average running time, variance, waiting events, etc. of the historical running.

[0068] Execute step S670 to input these features into a neural network with a double-layer fully connected Relu activation function, where the number of neurons in the hidden layer is equal to 50. Thus, the reward value that is expected to be generated by preferentially executing the SQL under the current network parameters can be calculated. This reward value is the reward value estimated by the current value network. Select the SQL with the highest reward value predicted by the current neural network as the strategy (Policy) of the current SQL statement for execution. After execution and when the batch of this job group ends on the same day, the actual reward value can be calculated according to the definition of the reward (Reward) in the quadruple (S, A, P, R) of reinforcement learning defined in the previous section.

[0069] Execute step S680 to achieve execution of each prediction feedback. After each prediction, store the current state State, the calculated Action(queuename), and the estimated reward value (Reward) in the replay memory unit. After the daily batch is completed, randomly select a certain number of batches of training samples from the replay memory unit for learning to train the neural network. The reason for randomly selecting 30% instead of all here is that machine learning based on the maximum likelihood method has an assumption: the training samples must be independent and identically distributed. If the assumption does not hold, the training effect of the model will be greatly reduced. And the training samples in our replay memory unit here are sequences obtained by interacting with State and there is a certain correlation because the result of one statement is likely to be the input of another statement. Therefore, the solution of the present invention is to set up a replay memory unit to store a certain amount of data, including features, actions, and rewards. When using the data, we randomly select 30% of the hql statements in this replay memory unit for learning, which actually breaks the correlation of the sequences obtained by State interaction, that is, solves the problem of data correlation.

[0070] Finally, execute step S690 to achieve queue order scheduling. Define the loss function loss = (actual reward value - estimated reward value)^2, and then use the gradient descent method through the backpropagation of the neural network to update the parameters of the neural network to minimize the loss function, that is, to make the optimal queue arrangement trained by the neural network closer and closer to the actual queue arrangement rule.

[0071] The following combines the appendix Figure 7 Use a specific scenario example to illustrate the above database statement optimization method, such as Figure 7 As shown, first, based on step S710, pre-parse the SQL statement, and at the same time execute step S720 to call the information in the historical operation information library and fuse it with the pre-parsed SQL statement. Then execute step S730 to construct a feature engineering, that is, include corresponding analysis features. After that, execute step S740. Based on the above feature engineering, complete the training of the model through the construction of a reinforcement learning model and the automatic analysis of SQL performance. Use the trained model to execute step S750 to optimize the statement itself through performance analysis and statement self-tuning. Finally, execute step S760 to further optimize the execution process of the SQL statement through SQL execution queue optimization management, so as to ensure the execution effect of the optimized database statement.

[0072] Based on the introduction of the above embodiments and scenario examples, it can be seen that after obtaining the database statement, the above method first performs semantic parsing on the database statement to obtain semantic information, thereby determining the meaning and association relationship of the statement, and then obtains the corresponding analysis features in combination with the semantic information to determine the features that need to be optimized, so that the corresponding optimization strategy can be determined based on the analysis features using the preset optimization rules. Finally, in combination with the execution status of the database statement, the optimization strategy is used to optimize the database statement. Through the above method, the analysis of the database statement is realized to determine the features that need to be optimized, and the optimization of the database statement is realized by setting the corresponding strategy, which ensures the execution effect of the database statement and is beneficial to the execution of the corresponding business in the subsequent application process.

[0073] Based on Figure 1 the corresponding database statement optimization method, this specification embodiment introduces a database statement optimization device. The database statement optimization device is set in the database statement optimization device. As Figure 8 shown, the database statement optimization device includes the following modules.

[0074] The target database statement receiving module 810 is used to receive the target database statement.

[0075] The semantic parsing module 820 is used to perform semantic parsing on the target database statement to obtain semantic information; the semantic information is used to represent the meaning and association relationship of the target database statement.

[0076] The analysis feature determination module 830 is used to determine the analysis features of the target database statement according to the semantic information; the analysis features are used to represent the features targeted by the statement optimization.

[0077] The optimization strategy determination module 840 is used to determine the optimization strategy of the target database statement based on the analysis features using the preset optimization rules.

[0078] The target database statement optimization module 850 is used to optimize the target database statement through the optimization strategy in combination with the execution status of the database statement.

[0079] Based on Figure 1 the corresponding database statement optimization method, this specification embodiment provides a database statement optimization device. As Figure 9 shown, the database statement optimization device may include a memory and a processor.

[0080] In this embodiment, the memory may be implemented in any suitable manner. For example, the memory may be a read-only memory, a mechanical hard disk, a solid-state drive, or a USB flash drive, etc. The memory may be used to store computer program instructions.

[0081] In this embodiment, the processor may be implemented in any suitable manner. For example, the processor may take the form of, for example, a microprocessor or a processor, a computer-readable medium storing computer-readable program code (such as software or firmware) executable by the (micro)processor, logic gates, switches, an Application Specific Integrated Circuit (ASIC), a programmable logic controller, and an embedded microcontroller, and so on. The processor may execute the computer program instructions to implement the following steps: receiving a target database statement; performing semantic parsing on the target database statement to obtain semantic information; the semantic information is used to represent the meaning and association relationship of the target database statement; determining an analysis feature of the target database statement according to the semantic information; the analysis feature is used to represent the feature targeted by statement optimization; based on the analysis feature, determining an optimization strategy for the target database statement by using a preset optimization rule; and optimizing the target database statement through the optimization strategy in combination with the execution state of the database statement.

[0082] It should be noted that the above database statement optimization method, device, and equipment may be applied to the field of artificial intelligence technology, or may also be applied to other technical fields except the field of artificial intelligence technology, and no limitation is imposed thereon.

[0083] In the 1990s, it was obvious to distinguish whether an improvement to a technology was a hardware improvement (e.g., improvement to circuit structures such as diodes, transistors, switches, etc.) or a software improvement (improvement to method flows). However, with the development of technology, many method flow improvements today can be regarded as direct improvements to hardware circuit structures. Almost all designers obtain the corresponding hardware circuit structure by programming the improved method flow into the hardware circuit. Therefore, it cannot be said that an improvement to a method flow cannot be implemented with a hardware entity module. For example, a Programmable Logic Device (PLD) (such as a Field Programmable Gate Array (FPGA)) is such an integrated circuit whose logical function is determined by the user's programming of the device. Designers can program by themselves to "integrate" a digital system on a single PLD, without having to ask a chip manufacturer to design and fabricate a dedicated integrated circuit chip. Moreover, nowadays, instead of manually fabricating integrated circuit chips, this programming is mostly implemented using "logic compiler" software, which is similar to the software compiler used in program development and writing. The original code before compilation also has to be written in a specific programming language, which is called a Hardware Description Language (HDL). And there is not only one kind of HDL, but many kinds, such as ABEL (Advanced Boolean Expression Language), AHDL (Altera Hardware Description Language), Confluence, CUPL (Cornell University Programming Language), HDCal, JHDL (Java Hardware Description Language), Lava, Lola, MyHDL, PALASM, RHDL (Ruby Hardware Description Language), etc. The most commonly used ones currently are VHDL (Very-High-Speed Integrated Circuit Hardware Description Language) and Verilog. Those skilled in the art should also be aware that as long as the method flow is slightly logically programmed with the above-mentioned several hardware description languages and programmed into the integrated circuit, it is easy to obtain the hardware circuit that implements the logical method flow.

[0084] The systems, devices, modules or units illustrated in the above embodiments may be specifically implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer. Specifically, the computer may be, for example, a personal computer, a laptop computer, a cellular phone, a camera phone, a smart phone, a personal digital assistant, a media player, a navigation device, an email device, a game console, a tablet computer, a wearable device, or any combination of these devices.

[0085] From the description of the above embodiments, those skilled in the art can clearly understand that this specification can be implemented by means of software plus a necessary first hardware platform. Based on such an understanding, the technical solution of this specification, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions for causing a computer device (which may be a personal computer, a server, or a network device, etc.) to execute the methods described in various embodiments or some parts of the embodiments of this specification.

[0086] The various embodiments in this specification are described in a progressive manner. The same or similar parts among the various embodiments can be referred to each other, and the key point of each embodiment is to illustrate the differences from other embodiments. In particular, for the system embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0087] This specification can be used in many first or dedicated computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet-type devices, multi-processor systems, microprocessor-based systems, set-top boxes, programmable consumer electronic devices, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, and so on.

[0088] This specification can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. This specification can also be practiced in a distributed computing environment, where tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.

[0089] Although this specification is illustrated by way of examples, those of ordinary skill in the art will appreciate that the specification has many modifications and variations without departing from the spirit of the specification. It is intended that the appended claims cover these modifications and variations without departing from the spirit of the specification.

Claims

1. A method for optimizing database statements, characterized in that, the database statements are used to implement corresponding program instructions based on a database; the method includes: receiving a target database statement; the target database statement includes an SQL statement; performing semantic parsing on the target database statement to obtain semantic information; the semantic information is used to represent the meaning and association relationship of the target database statement; determining an analysis feature of the target database statement according to the semantic information; the analysis feature is used to represent the feature targeted by statement optimization; based on the analysis feature, using a preset optimization rule to determine an optimization strategy for the target database statement; combining the execution status of the database statement, and optimizing the target database statement through the optimization strategy; wherein, before determining the analysis feature of the target database statement according to the semantic information, it further includes: obtaining an execution plan and a historical operation record corresponding to the target database statement; correspondingly, determining the analysis feature of the target database statement according to the semantic information includes: combining the execution plan and the historical operation record, and determining the analysis feature of the target database statement according to the semantic information; wherein, the analysis feature includes at least one of a statement feature, a statistical information feature, an association feature, an execution plan cost feature, and a historical statement operation feature; wherein, the statement feature is to form corresponding feature variables by analyzing keywords at the statement level of sql_context; the statistical information feature is to obtain feature variables at the table and field levels by querying the statistical information or the number of records of the involved tables; the association feature is to obtain the association feature between tables and the feature of single-table scanning through the obtained execution plan; the execution plan cost feature is to obtain the cost data of the execution plan, return rows, and distinguishability features through the obtained execution plan; the historical statement operation feature is to obtain the historical operation time-consuming and waiting event indicators of the statement through the system department; wherein, based on the analysis feature, using a preset optimization rule to determine an optimization strategy for the target database statement includes: inputting the analysis feature into a pre-trained statement performance analysis model to obtain performance parameters of the target database statement; determining an optimization strategy corresponding to the target database according to the performance parameters.

2. The method according to claim 1, characterized in that, the semantic information includes table association information and index information; performing semantic parsing on the target database statement to obtain semantic information includes: performing semantic parsing on the target database statement to obtain a semantic tree; the semantic tree includes at least one piece of information such as table objects, filtering conditions, and association levels; constructing table association information and index information based on the semantic tree.

3. The method according to claim 1, characterized in that, before performing semantic parsing on the target database statement to obtain semantic information, it further includes: splitting the target database statement to obtain at least one sub-statement; performing format normalization processing on each sub-statement respectively; the format normalization processing includes at least one of keyword capitalization conversion, comment deletion, and indentation processing.

4. The method according to claim 1, wherein, the statement performance analysis model is obtained by the following method: Inputting sample features into a preset common neural network model; the common neural network model includes at least two execution threads; the execution threads respectively use their own sample features to interact to obtain empirical data; Using the execution threads to respectively calculate the corresponding loss function gradients according to the empirical data; Optimizing the statement performance analysis model based on the loss function gradients.

5. The method according to claim 1, wherein, the optimization strategy includes an optimization strategy based on statements and an optimization strategy based on execution plans; the optimization strategy based on statements includes at least one of an outer join rewrite strategy for subqueries, a rewrite strategy for repeated access to multiple tables, a driving table adjustment strategy, a table relationship rewrite strategy, a table data rewrite strategy, an index rewrite strategy, and a multi-table association relationship rewrite strategy; the optimization strategy based on execution plans includes at least one of an execution plan rewrite strategy based on historical execution time and a table access strategy based on the correspondence between statistical information and physical storage.

6. The method according to claim 1, wherein, the execution state includes at least one of data volume-related features, execution plan-related features, system resource features, job running features, job waiting features, and historical features.

7. The method according to claim 1, wherein, combining the execution state of the database statement and optimizing the target database statement through the optimization strategy includes: Using a neural network with a double-layer fully connected Relu activation function to optimize the target database statement in combination with the execution state and optimization strategy of the database statement.

8. A database statement optimization device, wherein, the database statement is used to implement corresponding program instructions based on a database; the device includes: A target database statement receiving module, configured to receive a target database statement; the target database statement includes an SQL statement; A semantic parsing module, configured to perform semantic parsing on the target database statement to obtain semantic information; the semantic information is used to represent the meaning and association relationship of the target database statement; An analysis feature determination module, configured to determine the analysis features of the target database statement according to the semantic information; the analysis features are used to represent the features targeted by statement optimization; An optimization strategy determination module, configured to determine the optimization strategy of the target database statement based on the analysis features by using a preset optimization rule; A target database statement optimization module, configured to optimize the target database statement in combination with the execution state of the database statement through the optimization strategy; wherein, before determining the analysis features of the target database statement according to the semantic information, it further includes: Obtaining the execution plan and historical operation records corresponding to the target database statement; Correspondingly, determining the analysis features of the target database statement according to the semantic information includes: Combining the execution plan and historical operation records, and determining the analysis features of the target database statement according to the semantic information; Among them, the analysis features include at least one of statement features, statistical information features, association features, execution plan cost features, and historical statement execution features; among them, the statement features are formed by analyzing keywords at the statement level of the sql_context to form corresponding feature variables; the statistical information features are obtained by querying the statistical information or the number of records of the involved tables to obtain feature variables at the table and field levels; the association features are obtained by the query execution plan to obtain the association features between tables and the features of single-table scans; the execution plan cost features are obtained by the query execution plan to obtain the cost data of the execution plan, return rows, and discrimination features; the historical statement execution features are obtained by the system department to obtain the historical execution time and waiting event indicators of the statement. Among them, based on the analysis features, using preset optimization rules to determine the optimization strategy for the target database statement includes: Inputting the analysis features into a pre-trained statement performance analysis model to obtain the performance parameters of the target database statement; Determining the optimization strategy corresponding to the target database according to the performance parameters.

9. A database statement optimization device, including a memory and a processor; The memory is used to store computer program instructions; The processor is used to execute the computer program instructions to implement the following steps: receiving a target database statement; the target database statement includes an SQL statement; performing semantic parsing on the target database statement to obtain semantic information; the semantic information is used to represent the meaning and association relationship of the target database statement; determining the analysis features of the target database statement according to the semantic information ； The analysis features are used to represent the features targeted by statement optimization; Based on the analysis features, using preset optimization rules to determine the optimization strategy for the target database statement; Optimize the target database statement through the optimization strategy in combination with the execution status of the database statement; wherein, before determining the analysis features of the target database statement according to the semantic information, it further includes: obtaining the execution plan and historical running records corresponding to the target database statement; correspondingly, determining the analysis features of the target database statement according to the semantic information includes: determining the analysis features of the target database statement in combination with the execution plan and historical running records according to the semantic information; wherein, the analysis features include at least one of statement features, statistical information features, association features, execution plan cost features, and historical statement running features; wherein, the statement features are formed by analyzing the keywords at the statement level of the sql_context to form corresponding feature variables; the statistical information features are obtained by querying the statistical information or the number of records of the involved tables to obtain the feature variables at the table and field levels; the association features are obtained by querying the execution plan to obtain the association features between tables and the features of single-table scans; the execution plan cost features are obtained by querying the execution plan to obtain the cost data of the execution plan, return rows, and discrimination features; the historical statement running features are obtained by the system department to obtain the historical running time and waiting event indicators of the statement; wherein, based on the analysis features, using a preset optimization rule to determine the optimization strategy of the target database statement includes: inputting the analysis features into a pre-trained statement performance analysis model to obtain the performance parameters of the target database statement; determining the optimization strategy corresponding to the target database according to the performance parameters.

Images