CN111400338A - SQ L optimization method, device, storage medium and computer equipment - Google Patents
SQ L optimization method, device, storage medium and computer equipment Download PDFInfo
- Publication number
- CN111400338A CN111400338A CN202010141851.3A CN202010141851A CN111400338A CN 111400338 A CN111400338 A CN 111400338A CN 202010141851 A CN202010141851 A CN 202010141851A CN 111400338 A CN111400338 A CN 111400338A
- Authority
- CN
- China
- Prior art keywords
- optimized
- statement
- optimization
- script
- index
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
- Debugging And Monitoring (AREA)
Abstract
The invention discloses an SQ L optimization method, an SQ L optimization device, a storage medium and computer equipment, and relates to the technical field of databases.
Description
Technical Field
The invention relates to the technical field of databases, in particular to a SQ L optimization method, a SQ L optimization device, a SQ L storage medium and computer equipment.
Background
SQ L (Structured Query L anguage), a Structured Query language, is a Database language with multiple functions of data manipulation and data definition, and SQ L (Structured Query L anguage) is dedicated to operating relational databases, which are currently the most widely used databases, and more typical relational databases include Oracle Database of Oracle corporation, microsoft L Server of corporation, DB2 of IBM corporation, and postgresgq L and MySQ.
In practical application, the SQ L tuning is crucial to the performance of a project, but the existing SQ L tuning is all based on the technical accumulation of technicians, the technical points are scattered, the regulators need professional knowledge in multiple fields such as SQ L optimization, access design, SQ L design and the like, different data volumes and different configuration tuning modes of different database scenes are different, a large number of repeated and trivial challenges are very time-consuming, and the tuning work is endless due to the continuous increase of SQ L load.
At present, no effective scheme specially aiming at the SQ L statement automatic optimization exists in the market.
Disclosure of Invention
In view of this, the present application provides a method, an apparatus, a storage medium, and a computer device for optimizing SQ L, and mainly aims to solve the technical problems of long time consumption, low optimization efficiency, and poor optimization effect of SQ L optimization.
According to a first aspect of the present invention, there is provided a SQ L optimization method, the method comprising:
collecting multiple groups of SQ L sample data, and creating a decision model according to SQ L sample data;
acquiring an SQ L statement to be optimized and application environment information corresponding to the SQ L statement to be optimized;
converting the SQ L statement to be optimized into an abstract syntax tree;
optimizing the abstract syntax tree by using a decision model according to application environment information corresponding to the SQ L statement to be optimized;
and converting the optimized abstract syntax tree into an SQ L statement to obtain an optimized SQ L statement.
According to a second aspect of the present invention there is provided a SQ L optimisation device comprising:
the model creating module is used for acquiring multiple groups of SQ L sample data and creating a decision model according to SQ L sample data;
the data acquisition module is used for acquiring an SQ L statement to be optimized and application environment information corresponding to the SQ L statement to be optimized;
the semantic analysis module is used for converting the SQ L statement to be optimized into an abstract syntax tree;
the optimization processing module is used for optimizing the abstract syntax tree by using a decision model according to the application environment information corresponding to the SQ L statement to be optimized;
and the statement conversion module is used for converting the optimized abstract syntax tree into an SQ L statement to obtain an optimized SQ L statement.
According to a third aspect of the present invention, there is provided a storage medium having stored thereon a computer program which, when executed by a processor, implements the SQ L optimization method described above.
According to a fourth aspect of the present invention there is provided a computer apparatus comprising a memory, a processor and a computer program stored on the memory and executable on the processor, the processor when executing the program implementing the SQ L optimization method described above.
According to the SQ L optimization method, the SQ L optimization device, the decision model is created by using collected SQ L sample data, the SQ L statement converted into the abstract syntax tree is optimized by using the decision model and combining with the application environment of the SQ L statement, and the optimized abstract syntax tree is converted into the SQ L statement to obtain the optimized SQ L statement.
The foregoing description is only an overview of the technical solutions of the present application, and the present application can be implemented according to the content of the description in order to make the technical means of the present application more clearly understood, and the following detailed description of the present application is given in order to make the above and other objects, features, and advantages of the present application more clearly understandable.
Drawings
The accompanying drawings, which are included to provide a further understanding of the invention and are incorporated in and constitute a part of this application, illustrate embodiment(s) of the invention and together with the description serve to explain the invention without limiting the invention. In the drawings:
fig. 1 is a schematic flow chart illustrating an SQ L optimization method according to an embodiment of the present invention;
FIG. 2 is a schematic flow chart diagram illustrating another SQ L optimization method provided by an embodiment of the present invention;
fig. 3 is a schematic structural diagram illustrating an SQ L optimization apparatus according to an embodiment of the present invention;
fig. 4 shows a schematic structural diagram of another SQ L optimization apparatus provided by an embodiment of the present invention.
Detailed Description
The invention will be described in detail hereinafter with reference to the accompanying drawings in conjunction with embodiments. It should be noted that the embodiments and features of the embodiments in the present application may be combined with each other without conflict.
In the development process of an actual project, SQ L optimization is a very important link, for example, in the early operation stage of some small projects, since the amount of service data is relatively small, the influence of the execution efficiency of SQ L on the program operation efficiency is not obvious, and developers and operation and maintenance personnel cannot judge the influence of SQ L on the program operation efficiency, no special optimization is performed on SQ L, but as time is accumulated, the amount of service data is increased, the influence of the execution efficiency of SQ L on the program operation efficiency is gradually increased, and at this time, a periodic optimization is performed on SQ L, and for example, in some medium and large projects, due to the large amount of data, a targeted SQ L optimization is performed on service scenes in the early operation stage of a project, so as not to influence the program operation efficiency in the operation process of the project.
In one embodiment, as shown in fig. 1, a SQ L optimization method is provided, which is described by taking the method as an example applied to a server, and includes the following steps:
101. multiple sets of SQ L sample data are collected and a decision model is created from SQ L sample data.
The decision model belongs to a reinforced learning model, and the model comprises two training processes of learning and reinforcement, wherein the learning and the reinforcement are two continuous and cyclic iterative processes until an optimal decision is reached.
Specifically, the multiple groups of SQ L sample data include multiple groups of SQ L scripts before optimization, pre-optimized SQ L scripts corresponding to the SQ L scripts before optimization, and corresponding application environment information, and before the decision model is trained, the multiple groups of SQ L sample data may be divided into a training set and a test set, where data in the training set may be used for training a learning process, and data in the test set may be used for training a reinforcement process.
Further, in the learning process of the decision model, a plurality of decision trajectory data can be created according to an SQ L script before optimization and an SQ L script which is pre-optimized in a training set, wherein each decision trajectory data comprises a plurality of SQ L grammars and an optimization operation sequence corresponding to the SQ L grammar, then mapping relations between the SQ L grammars and the optimization operations in all the decision trajectory data are extracted, a data set is constructed according to the extracted mapping relations between the SQ L grammars and the optimization operations, and finally, iterative computation is performed on the data set through a separation algorithm and/or a regression algorithm, and the decision model is obtained through continuous learning and training.
Further, in the process of strengthening the decision model, firstly, a performance test can be performed on the SQ L script before optimization and the SQ L script before pre-optimization in the test set, and a target performance index can be obtained according to the performance test result, wherein the target performance index comprises a preset SQ L execution time threshold, a memory occupancy threshold, a CPU occupancy threshold, a read-write database frequency threshold, a deadlock frequency threshold and the like, then a reward function is created according to the target performance index, and the SQ L script before optimization in the test set is optimized and subjected to the performance test by using the decision model so as to obtain the performance index of the SQ L script after optimization, wherein the performance index comprises an SQ L execution time, a memory occupancy, a CPU occupancy, a read-write database frequency, a deadlock frequency and the like, then the performance index of the SQ L script after optimization is compared with the target performance index, a cumulative reward is obtained through a greedy algorithm and/or a value function algorithm according to the comparison result, and finally the transition probability of the decision.
In this embodiment, the reward function is an important component in the decision model, and is a feedback signal, and it can identify whether the performance optimization of SQ L reaches a preset target, the goal of the decision model is to obtain a maximum cumulative reward (maximum cumulative reward), for the serialized decision problem, reinforcement learning is to select some optimization operations to maximize the cumulative reward, for the same SQ L script, the optimization path is not only one, and from the start state to the end state, there may be multiple optimization paths, the transition of which is related to the transition probability, and after continuous learning and reinforcement, the transition probability can be adjusted and optimized, thereby obtaining a relatively perfect decision model.
102. And acquiring the SQ L statement to be optimized and the application environment information corresponding to the SQ L statement to be optimized.
The application environment information of SQ L refers to a database environment in which SQ L statements are applied, and the SQ L syntax in each database environment differs, and the execution efficiency in different database environments also differs for the same SQ L statement, so that it is necessary to distinguish the application environments of SQ L statements during model training and model application to perform targeted training and application.
Specifically, the server may directly obtain an SQ L statement to be optimized and application environment information corresponding to an SQ L statement to be optimized, where the application environment information corresponding to the SQ L statement to be optimized includes information such as an application database and a database plug-in.
103. The SQ L statement to be optimized is converted into an abstract syntax tree.
The Abstract Syntax Tree (AST) is an Abstract representation of a Syntax structure of a source code, and represents the Syntax structure of a programming language in a Tree form, and each node on the Syntax Tree represents a structure in the source code.
Specifically, the server may first split the SQ L sentence to be optimized into a plurality of lemmas according to a predetermined lexical rule through a lexical analyzer, then perform syntax analysis on the plurality of lemmas according to the set SQ L syntax analysis rule through a syntax analyzer, and finally convert the plurality of lemmas conforming to the syntax rule into an abstract syntax tree.
104. And optimizing the abstract syntax tree by using a decision model according to the application environment information corresponding to the SQ L statement to be optimized.
Specifically, the query condition of the SQ L statement to be optimized can be obtained through the abstract syntax tree, then the decision model can determine the optimization strategy according to the application environment information corresponding to the SQ L statement to be optimized, then the redundant query condition in the SQ L statement is removed according to the optimization strategy, and finally the query condition from which the redundancy is removed is replaced with the query condition with the same semantics or the query condition is split into a plurality of query conditions.
In the embodiment, the optimization strategy of the decision model is different for different application environments, that is, the transition probability of the optimization operation is different under different application environments, and naturally, the output optimization result is also different, so that after the abstract syntax tree is optimized by using the decision model, a better SQ L statement for different application environments can be obtained.
105. And converting the optimized abstract syntax tree into an SQ L statement to obtain an optimized SQ L statement.
Specifically, the step is to convert the SQ L statement into the inverse operation of the abstract syntax tree, and after the abstract syntax tree is optimized by using the decision model, the abstract syntax tree can be subjected to semantic reverse analysis, so that the optimized SQ L statement can be obtained.
According to the SQ L optimization method provided by the embodiment, firstly, a decision model is created by using collected SQ L sample data, then, an SQ L statement converted into an abstract syntax tree is optimized by using the decision model in combination with an application environment of an SQ L statement, and finally, the optimized abstract syntax tree is converted into an SQ L statement to obtain an optimized SQ L statement.
Further, as a refinement and an extension of the specific implementation of the above embodiment, in order to fully illustrate the implementation process of the present embodiment, there is provided an SQ L optimization method, as shown in fig. 2, the method includes the following steps:
201. multiple sets of SQ L sample data are collected and a decision model is created from SQ L sample data.
Specifically, multiple groups of SQ L sample data include multiple groups of pre-optimization SQ L scripts, pre-optimization SQ L scripts corresponding to pre-optimization SQ L0 scripts, and corresponding application environment information, before a decision model is trained, multiple groups of SQ L sample data can be divided into a training set and a test set, during the learning process of the decision model, multiple decision trajectory data can be created according to the pre-optimization SQ L scripts and the pre-optimization SQ L scripts in the training set, wherein each decision trajectory data includes multiple SQ L grammars and an optimization operation sequence corresponding to the SQ L grammars, then mapping relations between SQ L grammars and optimization operations in all decision trajectory data are extracted, a data set is constructed according to the extracted mapping relations between the SQ L grammars and the optimization operations, and finally, iterative computation is performed on the data set through a separation algorithm and/or a regression algorithm, and the decision model is obtained through continuous learning and training.
In this embodiment, each decision trajectory data represents an optimization process of a set of SQ L sample data, that is, each decision trajectory data includes a set of corresponding relationships between SQ L syntax and optimization operations, so that multiple sets of SQ L sample data can create multiple pieces of decision trajectory data, and it can be understood that multiple optimization operations for the same SQ L syntax exist in the multiple pieces of decision trajectory data, a mapping relationship between each SQ L syntax and the optimization operations can be extracted, a data set can be constructed according to the mapping relationship therebetween, and by performing a certain manner on the data set, a corresponding relationship between each SQ L syntax and the optimization operations can be analyzed, so as to train and obtain a decision model.
Further, in the process of strengthening the decision model, firstly, a performance test can be performed on the SQ L script before optimization and the SQ L script before pre-optimization in the test set, and a target performance index can be obtained according to the performance test result, wherein the target performance index comprises a preset SQ L execution time threshold, a memory occupancy threshold, a CPU occupancy threshold, a read-write database frequency threshold, a deadlock frequency threshold and the like, then a reward function is created according to the target performance index, and the SQ L script before optimization in the test set is optimized and subjected to the performance test by using the decision model so as to obtain the performance index of the SQ L script after optimization, wherein the performance index comprises an SQ L execution time, a memory occupancy, a CPU occupancy, a read-write database frequency, a deadlock frequency and the like, then the performance index of the SQ L script after optimization is compared with the target performance index, a cumulative reward is obtained through a greedy algorithm and/or a value function algorithm according to the comparison result, and finally the transition probability of the decision.
In this embodiment, a performance test is performed on the SQ L script before optimization and the SQ L script before optimization in the test set, so that the pre-optimization effect can be quantified, and a target performance index can be established, which can be qualitative or quantitative, for example, the target performance index can be that the execution time of SQ L is shortened by 20%, the memory occupancy rate is reduced by 30%, or the deadlock frequency cannot exceed 1, and the like.
202. And acquiring the SQ L statement to be optimized and the application environment information corresponding to the SQ L statement to be optimized.
Specifically, the server may directly obtain an SQ L statement to be optimized and application environment information corresponding to an SQ L statement to be optimized, where the application environment information corresponding to the SQ L statement to be optimized includes information such as an application database and a database plug-in.
203. The SQ L statement to be optimized is converted into an abstract syntax tree.
Specifically, the server may first split the SQ L sentence to be optimized into a plurality of lemmas according to a predetermined lexical rule through a lexical analyzer, then perform syntax analysis on the plurality of lemmas according to the set SQ L syntax analysis rule through a syntax analyzer, and finally convert the plurality of lemmas conforming to the syntax rule into an abstract syntax tree.
For example, the server obtains a section of SQ L statement to be optimized AS SE L ECT [ personal user base attribute.user code ], [ personal user base attribute.inhabitation address ] FROM [ personal user base attribute ] WHERE [ personal user base attribute.inhabitation address ] IN (SE L ECT [ k.addr ] FROM (SE L ECTCOUNT ([ personal user base attribute.user code ]) AS [ C _ USERID ], [ personal user base attribute.inhabitation address ] AS [ ADDR ] FROM [ personal user base attribute ] GROUP BY [ personal user base attribute.inhabitation address ])) AS [ K ] WHERE [ K.C _ USERID ] <5), after obtaining this section of L statement, the server performs first lexical analysis, IN the lexical analysis stage, the program performs first lexical analysis according to a predefined SQ L lexical grammar, the character stream of SQ L word segmentation script, outputs a keyword, TOKEN, and so on-string syntax analysis, and converts the sequence of the tree syntax into a tree structure, and performs final parsing of a tree syntax according to a predefined syntax, the syntax of the tree structure, and converts the tree syntax of the tree structure into a syntax.
204. And optimizing the abstract syntax tree by using a decision model according to the application environment information corresponding to the SQ L statement to be optimized.
Specifically, the query condition of the SQ L statement to be optimized can be obtained through the abstract syntax tree, then the decision model can determine the optimization strategy according to the application environment information corresponding to the SQ L statement to be optimized, then, according to the optimization strategy, the redundant query condition in the SQ L statement is removed, and finally, the query condition from which the redundancy is removed is replaced with the query condition with the same semantics or the query condition is split into a plurality of query conditions.
For example, the decision model may remove some meaningless query conditions, such as the statement select col1 generating an empty table structure, and col2int # t from t where 1 ═ 0 may be replaced with create table # t (. once.), because the original statement will not return any result set but will consume system resources, or may replace the symbol ". times.a" list of fields "with a specific list of fields, such as replacing the". times.in the statement select from t with a specific list of fields to avoid returning redundant columns, or may replace the in statement with a between statement or an ex statement for consecutive values, such as replacing the statement select num from num in (select num b) with a between statements 6332, and may avoid applying different scenarios of the optimization of the tables 52.
205. And converting the optimized abstract syntax tree into an SQ L statement to obtain an optimized SQ L statement.
Specifically, after the abstract syntax tree is optimized by using the decision model, semantic reverse analysis can be performed on the abstract syntax tree, and an optimized SQ L statement can be obtained.
206. And performing performance test on the optimized SQ L statement to obtain the performance index of the optimized SQ L statement.
Specifically, after the decision model is used to obtain the optimized SQ L statement, a performance test can be performed on the SQ L statement to obtain a performance index of the optimized SQ L statement, wherein the performance index includes SQ L execution time, memory occupancy rate, CPU occupancy rate, times of reading and writing a database, times of deadlock and the like.
207. And comparing the performance index of the optimized SQ L statement with a preset performance index.
Specifically, after obtaining the performance index of the optimized SQ L statement, the performance index of the optimized SQ L statement may be compared with a preset performance index, where the preset performance index may also be qualitative or quantitative, for example, the preset performance index may be that the execution time of SQ L is shortened by 20%, the memory occupancy rate is reduced by 30%, or the number of times of deadlock cannot exceed 1, and the like.
208. And optimizing the SQ L sentences which do not reach the preset performance index again and/or outputting index optimization suggestions and memory optimization suggestions of the SQ L sentences to be optimized through a decision model.
Specifically, for an SQ L statement which does not reach the preset performance index, the decision model may be used to optimize the SQ L statement before optimization or the SQ L statement after optimization again to obtain different optimization results, or the decision model may be used to output an index optimization suggestion and a memory optimization suggestion to obtain a better SQ L statement, and it can be understood that, for an SQ L statement which reaches the preset performance index, the index optimization suggestion and the memory optimization suggestion may also be output through the decision model.
Further, when outputting the index optimization suggestion and the memory optimization suggestion of the SQ L sentence to be optimized, the index information, the table building script information and the execution plan of the SQ L sentence to be optimized may be first obtained, then the index execution information of the SQ L sentence to be optimized may be obtained through the execution plan, and a targeted index optimization suggestion may be generated according to the index execution information, the index information of the SQ L sentence and a preset index optimization policy, and a targeted memory optimization suggestion may be generated according to the table building script information of the SQ L sentence and a preset memory optimization policy.
In this embodiment, the index optimization suggestion and the memory optimization suggestion may include various contents, and the index optimization aspect may suggest to build an index on a column of a where statement or an order by statement in a certain row, or suggest to delete an index built on some columns that are not frequently used to improve execution efficiency. In the aspect of memory optimization, a digital field can be used for replacing a character field, and a varchar field can be used for replacing a char field, so that the query and connection performance is improved, and the storage space is saved; or when the temporary table is newly built, if the data volume inserted at one time is large, the select intos is used for replacing the creatable statement so as to avoid generating a large amount of logs and improve the speed, if the data volume inserted at one time is not large, the create table statement is firstly used, then the insert statement is used so as to alleviate the resources of the system table, in addition, frequent creation and deletion of the temporary table can be avoided so as to reduce the consumption of the resources of the system table, and the like.
The SQ L optimization method provided by this embodiment can perform targeted optimization on the SQ L statement according to different application environments, and can also perform performance test and re-optimization on the optimized SQ L statement, and perform targeted output index optimization suggestion and memory optimization suggestion, thereby further improving the efficiency of SQ L optimization, reducing the time consumption of SQ L optimization, and achieving a better optimization effect.
Further, as a specific implementation of the method shown in fig. 1 and fig. 2, this embodiment provides an SQ L optimization apparatus, as shown in fig. 3, the apparatus includes a model creation module 31, a data acquisition module 32, a semantic analysis module 33, an optimization processing module 34, and a statement conversion module 35, where:
the model creating module 31 can be used for collecting multiple groups of SQ L sample data and creating a decision model according to the SQ L sample data;
the data acquisition module 32 is used for acquiring an SQ L statement to be optimized and application environment information corresponding to the SQ L statement to be optimized;
the semantic analysis module 33 is used for converting the SQ L statement to be optimized into an abstract syntax tree;
the optimization processing module 34 is configured to perform optimization processing on the abstract syntax tree by using a decision model according to application environment information corresponding to an SQ L statement to be optimized;
and the statement conversion module 35 may be configured to convert the optimized abstract syntax tree into an SQ L statement, so as to obtain an optimized SQ L statement.
In a specific application scenario, multiple groups of SQ L sample data comprise multiple groups of first SQ L scripts before optimization, second pre-optimized SQ L scripts corresponding to the first SQ L0 scripts and corresponding application environment information, the model creation module 31 can be specifically used for dividing multiple groups of SQ L sample data into a training set and a testing set, multiple decision trajectory data are created according to the first SQ L scripts, the second SQ L scripts and the corresponding application environment information in the training set, each decision trajectory data comprises multiple SQ L grammars and an optimization operation sequence corresponding to the SQ L grammars, mapping relations between SQ L grammars and the optimization operations in all the decision trajectory data are extracted, a data set is constructed according to the extracted mapping relations between the SQ L grammars and the optimization operations, iterative calculation is carried out on the data set through a separation algorithm and/or a regression algorithm, and a decision model is obtained through training.
In a specific application scenario, the model creating module 31 may be further configured to perform performance tests on a first SQ L script and a second SQ L script in a test set respectively, obtain a target performance index according to a performance test result, the target performance index includes a preset SQ L execution time threshold, a memory occupancy threshold, a CPU occupancy threshold, a read-write database frequency threshold and a deadlock frequency threshold, create a reward function according to the target performance index, optimize the first SQ L script in the test set by using a decision model to obtain an optimized first SQ L script, perform the performance test on the optimized first SQ L script to obtain a performance index of the optimized first SQ L script, the performance index includes an SQ L execution time, a memory occupancy, a CPU occupancy, a read-write database frequency and a deadlock frequency, compare the performance index of the optimized first SQ L script with the target performance index, obtain reward accumulation through a greedy algorithm and/or a value function algorithm according to a comparison result, and optimize a transfer probability of the reward decision model according to accumulation.
In a specific application scenario, the semantic analysis module 33 is specifically configured to split the SQ L sentence to be optimized into a plurality of lemmas according to a predetermined lexical rule through a lexical analyzer, perform syntax analysis on the plurality of lemmas according to a set syntax rule through a syntax analyzer, and convert the plurality of lemmas conforming to the syntax rule into an abstract syntax tree.
In a specific application scenario, the optimization processing module 34 is specifically configured to obtain a query condition of an SQ L statement to be optimized through an abstract syntax tree, determine an optimization strategy according to application environment information corresponding to the SQ L statement to be optimized, remove a redundant query condition according to the optimization strategy, and replace the query condition from which the redundancy is removed with a query condition having the same semantic meaning or split the query condition into multiple query conditions according to the optimization strategy.
In a specific application scenario, as shown in fig. 4, the apparatus further includes: performance test module 36, index comparison module 37, strengthen optimizing module 38, wherein:
the performance test module 36 is configured to perform a performance test on the optimized SQ L statement to obtain a performance index of the optimized SQ L statement, where the performance index includes SQ L execution time, memory occupancy, CPU occupancy, database reading and writing times, and deadlock times;
the index comparison module 37 is configured to compare the performance index of the optimized SQ L statement with a preset performance index;
the reinforcement optimization module 38 may be configured to optimize the optimized SQ L statement again and/or output an index optimization suggestion and a memory optimization suggestion of the SQ L statement to be optimized through a decision model if the performance test index of the optimized SQ L statement does not reach the preset performance index.
In a specific application scenario, the reinforcement optimization module 38 may be specifically configured to obtain index information, table building script information, and an execution plan of an SQ L statement to be optimized, obtain index execution information of an SQ L statement to be optimized through the execution plan, generate an index optimization suggestion according to the index execution information, the index information, and a preset index optimization policy, and generate a memory optimization suggestion according to the table building script information and a preset memory optimization policy.
It should be noted that other corresponding descriptions of the functional units involved in the SQ L optimizing apparatus provided in this embodiment may refer to the corresponding descriptions in fig. 1 and fig. 2, and are not repeated herein.
Based on the methods shown in fig. 1 and fig. 2, the present embodiment also provides a storage medium, on which a computer program is stored, and the computer program, when executed by a processor, implements the SQ L optimization method shown in fig. 1 and fig. 2.
Based on such understanding, the technical solution of the present application may be embodied in the form of a software product, and the software product to be identified may be stored in a non-volatile storage medium (which may be a CD-ROM, a usb disk, a removable hard disk, or the like), and include several instructions for enabling a computer device (which may be a personal computer, a server, or a network device) to execute the method according to the implementation scenarios of the present application.
Based on the foregoing methods shown in fig. 1 and fig. 2 and the SQ L optimization apparatus embodiments shown in fig. 3 and fig. 4, in order to achieve the foregoing object, the present embodiment further provides an entity device optimized for SQ L, which may be specifically a personal computer, a server, a smartphone, a tablet computer, a smartwatch, or other network device, and the like, the entity device including a storage medium and a processor, the storage medium for storing a computer program, and the processor for executing the computer program to implement the foregoing methods shown in fig. 1 and fig. 2.
Optionally, the entity device may further include a user interface, a network interface, a camera, a Radio Frequency (RF) circuit, a sensor, an audio circuit, a WI-FI module, and the like. The user interface may include a Display screen (Display), an input unit such as a keypad (Keyboard), etc., and the optional user interface may also include a USB interface, a card reader interface, etc. The network interface may optionally include a standard wired interface, a wireless interface (e.g., WI-FI interface), etc.
It will be appreciated by those skilled in the art that the present embodiment provides an SQ L optimized physical device configuration that is not limiting of the physical device and may include more or fewer components, or some components in combination, or a different arrangement of components.
The storage medium may further include an operating system and a network communication module. The operating system is a program for managing the hardware of the above-mentioned entity device and the software resources to be identified, and supports the operation of the information processing program and other software and/or programs to be identified. The network communication module is used for realizing communication among components in the storage medium and communication with other hardware and software in the information processing entity device.
By applying the technical scheme of the invention, the invention can carry out targeted optimization on SQ L statements according to different application environments, and can improve the SQ L optimization efficiency by optimizing SQ L statements through a strategy model, thereby reducing the time consumption of SQ L optimization and achieving better optimization effect.
Those skilled in the art will appreciate that the figures are merely schematic representations of one preferred implementation scenario and that the blocks or flow diagrams in the figures are not necessarily required to practice the present application. Those skilled in the art will appreciate that the modules in the devices in the implementation scenario may be distributed in the devices in the implementation scenario according to the description of the implementation scenario, or may be located in one or more devices different from the present implementation scenario with corresponding changes. The modules of the implementation scenario may be combined into one module, or may be further split into a plurality of sub-modules.
The above application serial numbers are for description purposes only and do not represent the superiority or inferiority of the implementation scenarios. The above disclosure is only a few specific implementation scenarios of the present application, but the present application is not limited thereto, and any variations that can be made by those skilled in the art are intended to fall within the scope of the present application.
Claims (10)
1. A SQ L optimization method, comprising:
collecting multiple groups of SQ L sample data, and creating a decision model according to the SQ L sample data;
acquiring an SQ L statement to be optimized and application environment information corresponding to the SQ L statement to be optimized;
converting the SQ L statement to be optimized into an abstract syntax tree;
optimizing the abstract syntax tree by using the decision model according to the application environment information corresponding to the SQ L statement to be optimized;
and converting the optimized abstract syntax tree into an SQ L statement to obtain an optimized SQ L statement.
2. The method of claim 1, wherein the multiple sets of SQ L sample data comprise multiple sets of a first SQ L script before optimization, and a second SQ L script corresponding to the first SQ L script and corresponding application environment information;
creating a decision model according to the SQ L sample data, specifically including:
dividing the multiple groups of SQ L sample data into a training set and a test set;
creating a plurality of decision trajectory data according to a first SQ L script, a second SQ L script and corresponding application environment information in the training set, wherein each decision trajectory data comprises a plurality of SQ L grammars and an optimization operation sequence corresponding to the SQ L grammar;
extracting mapping relations of SQ L grammar and optimization operation in all decision trajectory data, and constructing a data set according to the extracted SQ L grammar and the mapping relations of the optimization operation;
and carrying out iterative computation on the data set through a separation algorithm and/or a regression algorithm, and training to obtain a decision model.
3. The method of claim 2, further comprising:
respectively performing performance tests on the first SQ L script and the second SQ L script in the test set, and obtaining target performance indexes according to the performance test results, wherein the target performance indexes comprise preset SQ L execution time threshold values, preset memory occupancy threshold values, preset CPU occupancy threshold values, preset read-write database times threshold values and preset deadlock times threshold values;
creating a reward function according to the target performance index;
optimizing the first SQ L script in the test set by using the decision model to obtain an optimized first SQ L script;
performing performance test on the optimized first SQ L script to obtain performance indexes of the optimized first SQ L script, wherein the performance indexes comprise SQ L execution time, memory occupancy rate, CPU occupancy rate, database reading and writing times and deadlock times;
comparing the performance index of the optimized first SQ L script with a target performance index, and obtaining accumulated rewards through a greedy algorithm and/or a value function algorithm according to a comparison result;
optimizing a transition probability of the decision model based on the cumulative reward.
4. The method as claimed in claim 3, wherein said converting the SQ L statement to be optimized into an abstract syntax tree specifically comprises:
splitting the SQ L sentence to be optimized into a plurality of lemmas through a lexical analyzer according to a preset lexical rule;
using a grammar analyzer to analyze the plurality of word elements according to a set grammar rule;
and converting the plurality of word elements which accord with the grammar rule into an abstract grammar tree.
5. The method as claimed in claim 4, wherein the optimizing the abstract syntax tree using the decision model according to the application environment information corresponding to the SQ L statement to be optimized includes:
obtaining the query condition of the SQ L statement to be optimized through an abstract syntax tree;
determining an optimization strategy according to the application environment information corresponding to the SQ L statement to be optimized;
removing redundant query conditions according to the optimization strategy;
and replacing the query condition with redundancy removed with a query condition with the same semantic meaning or splitting the query condition into a plurality of query conditions according to the optimization strategy.
6. The method of claim 5, further comprising:
performing performance test on the optimized SQ L statement to obtain the performance indexes of the optimized SQ L statement, wherein the performance indexes comprise SQ L execution time, memory occupancy rate, CPU occupancy rate, database reading and writing times and deadlock times;
comparing the performance index of the optimized SQ L statement with a preset performance index;
and if the performance test index of the optimized SQ L sentence does not reach the preset performance index, optimizing the optimized SQ L sentence again and/or outputting an index optimization suggestion and a memory optimization suggestion of the SQ L sentence to be optimized through the decision model.
7. The method according to claim 6, wherein the outputting of the index optimization suggestion and the memory optimization suggestion for the SQ L statement to be optimized through the decision model specifically comprises:
acquiring index information, table building script information and an execution plan of an SQ L statement to be optimized;
acquiring index execution information of the SQ L statement to be optimized through the execution plan;
generating an index optimization suggestion according to the index execution information, the index information and a preset index optimization strategy;
and generating a memory optimization suggestion according to the table building script information and a preset memory optimization strategy.
8. An apparatus for SQ L optimization, the apparatus comprising:
the model creating module is used for acquiring multiple groups of SQ L sample data and creating a decision model according to the SQ L sample data;
the data acquisition module is used for acquiring an SQ L statement to be optimized and application environment information corresponding to the SQ L statement to be optimized;
the semantic analysis module is used for converting the SQ L statement to be optimized into an abstract syntax tree;
the optimization processing module is used for optimizing the abstract syntax tree by using the decision model according to the application environment information corresponding to the SQ L statement to be optimized;
and the statement conversion module is used for converting the optimized abstract syntax tree into an SQ L statement to obtain an optimized SQ L statement.
9. A storage medium having a computer program stored thereon, the computer program, when being executed by a processor, realizing the steps of the method of any one of claims 1 to 7.
10. A computer arrangement comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the computer program realizes the steps of the method of any one of claims 1 to 7 when executed by the processor.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010141851.3A CN111400338B (en) | 2020-03-04 | 2020-03-04 | SQL optimization method, device, storage medium and computer equipment |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010141851.3A CN111400338B (en) | 2020-03-04 | 2020-03-04 | SQL optimization method, device, storage medium and computer equipment |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111400338A true CN111400338A (en) | 2020-07-10 |
| CN111400338B CN111400338B (en) | 2022-11-22 |
Family
ID=71435996
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010141851.3A Active CN111400338B (en) | 2020-03-04 | 2020-03-04 | SQL optimization method, device, storage medium and computer equipment |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111400338B (en) |
Cited By (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111897802A (en) * | 2020-08-10 | 2020-11-06 | 中国工商银行股份有限公司 | Database container fault positioning method and system |
| CN112347100A (en) * | 2020-10-27 | 2021-02-09 | 杭州安恒信息技术股份有限公司 | Database index optimization method and device, computer equipment and storage medium |
| CN112434003A (en) * | 2020-09-23 | 2021-03-02 | 苏宁云计算有限公司 | SQL optimization method and device, computer equipment and storage medium |
| CN112559554A (en) * | 2020-12-24 | 2021-03-26 | 北京百家科技集团有限公司 | Query statement optimization method and device |
| CN112667666A (en) * | 2020-12-31 | 2021-04-16 | 民生科技有限责任公司 | SQL operation time prediction method and system based on N-gram |
| CN113268494A (en) * | 2021-05-24 | 2021-08-17 | 中国联合网络通信集团有限公司 | Statement processing method and device for database to be optimized |
| CN113505141A (en) * | 2021-07-05 | 2021-10-15 | 浪潮云信息技术股份公司 | Automated method and system for realizing database SQL optimized execution based on artificial intelligence |
| CN114003231A (en) * | 2021-09-28 | 2022-02-01 | 厦门国际银行股份有限公司 | SQL syntax parse tree optimization method and system |
| CN114347039A (en) * | 2022-02-14 | 2022-04-15 | 北京航空航天大学杭州创新研究院 | Robot control method and related device |
Citations (11)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101021874A (en) * | 2007-03-21 | 2007-08-22 | 金蝶软件（中国）有限公司 | Method and apparatus for optimizing request to poll SQL |
| CN103761080A (en) * | 2013-12-25 | 2014-04-30 | 中国农业大学 | Structured query language (SQL) based MapReduce operation generating method and system |
| US20140181073A1 (en) * | 2012-12-20 | 2014-06-26 | Business Objects Software Ltd. | Method and system for generating optimal membership-check queries |
| CN107247811A (en) * | 2017-07-21 | 2017-10-13 | 中国联合网络通信集团有限公司 | SQL statement performance optimization method and device based on oracle database |
| CN107644073A (en) * | 2017-09-18 | 2018-01-30 | 广东中标数据科技股份有限公司 | A kind of field consanguinity analysis method, system and device based on depth-first traversal |
| CN109753490A (en) * | 2018-12-13 | 2019-05-14 | 深圳壹账通智能科技有限公司 | Database optimizing method, system, equipment and medium based on loophole reparation |
| CN109766354A (en) * | 2018-12-04 | 2019-05-17 | 北京辰森世纪科技股份有限公司 | Optimization method, device and the equipment of business datum inquiry |
| CN110134706A (en) * | 2019-04-01 | 2019-08-16 | 平安科技（深圳）有限公司 | SQL statement automatic optimization method, device, computer equipment and storage medium |
| CN110362597A (en) * | 2019-06-28 | 2019-10-22 | 华为技术有限公司 | A kind of structured query language SQL injection detection method and device |
| CN110555032A (en) * | 2019-09-09 | 2019-12-10 | 北京搜狐新媒体信息技术有限公司 | Data blood relationship analysis method and system based on metadata |
| CN110555035A (en) * | 2018-05-31 | 2019-12-10 | 阿里巴巴集团控股有限公司 | Method and device for optimizing query statement |
-
2020
- 2020-03-04 CN CN202010141851.3A patent/CN111400338B/en active Active
Patent Citations (11)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101021874A (en) * | 2007-03-21 | 2007-08-22 | 金蝶软件（中国）有限公司 | Method and apparatus for optimizing request to poll SQL |
| US20140181073A1 (en) * | 2012-12-20 | 2014-06-26 | Business Objects Software Ltd. | Method and system for generating optimal membership-check queries |
| CN103761080A (en) * | 2013-12-25 | 2014-04-30 | 中国农业大学 | Structured query language (SQL) based MapReduce operation generating method and system |
| CN107247811A (en) * | 2017-07-21 | 2017-10-13 | 中国联合网络通信集团有限公司 | SQL statement performance optimization method and device based on oracle database |
| CN107644073A (en) * | 2017-09-18 | 2018-01-30 | 广东中标数据科技股份有限公司 | A kind of field consanguinity analysis method, system and device based on depth-first traversal |
| CN110555035A (en) * | 2018-05-31 | 2019-12-10 | 阿里巴巴集团控股有限公司 | Method and device for optimizing query statement |
| CN109766354A (en) * | 2018-12-04 | 2019-05-17 | 北京辰森世纪科技股份有限公司 | Optimization method, device and the equipment of business datum inquiry |
| CN109753490A (en) * | 2018-12-13 | 2019-05-14 | 深圳壹账通智能科技有限公司 | Database optimizing method, system, equipment and medium based on loophole reparation |
| CN110134706A (en) * | 2019-04-01 | 2019-08-16 | 平安科技（深圳）有限公司 | SQL statement automatic optimization method, device, computer equipment and storage medium |
| CN110362597A (en) * | 2019-06-28 | 2019-10-22 | 华为技术有限公司 | A kind of structured query language SQL injection detection method and device |
| CN110555032A (en) * | 2019-09-09 | 2019-12-10 | 北京搜狐新媒体信息技术有限公司 | Data blood relationship analysis method and system based on metadata |
Cited By (15)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111897802A (en) * | 2020-08-10 | 2020-11-06 | 中国工商银行股份有限公司 | Database container fault positioning method and system |
| CN111897802B (en) * | 2020-08-10 | 2023-08-04 | 中国工商银行股份有限公司 | Database container fault positioning method and system |
| CN112434003B (en) * | 2020-09-23 | 2022-11-18 | 苏宁云计算有限公司 | SQL optimization method and device, computer equipment and storage medium |
| CN112434003A (en) * | 2020-09-23 | 2021-03-02 | 苏宁云计算有限公司 | SQL optimization method and device, computer equipment and storage medium |
| CN112347100A (en) * | 2020-10-27 | 2021-02-09 | 杭州安恒信息技术股份有限公司 | Database index optimization method and device, computer equipment and storage medium |
| CN112347100B (en) * | 2020-10-27 | 2024-03-26 | 杭州安恒信息技术股份有限公司 | Database index optimization method, device, computer equipment and storage medium |
| CN112559554A (en) * | 2020-12-24 | 2021-03-26 | 北京百家科技集团有限公司 | Query statement optimization method and device |
| CN112559554B (en) * | 2020-12-24 | 2024-01-26 | 北京百家科技集团有限公司 | Query statement optimization method and device |
| CN112667666A (en) * | 2020-12-31 | 2021-04-16 | 民生科技有限责任公司 | SQL operation time prediction method and system based on N-gram |
| CN113268494B (en) * | 2021-05-24 | 2023-06-02 | 中国联合网络通信集团有限公司 | Method and device for processing database statement to be optimized |
| CN113268494A (en) * | 2021-05-24 | 2021-08-17 | 中国联合网络通信集团有限公司 | Statement processing method and device for database to be optimized |
| CN113505141A (en) * | 2021-07-05 | 2021-10-15 | 浪潮云信息技术股份公司 | Automated method and system for realizing database SQL optimized execution based on artificial intelligence |
| CN114003231A (en) * | 2021-09-28 | 2022-02-01 | 厦门国际银行股份有限公司 | SQL syntax parse tree optimization method and system |
| CN114347039A (en) * | 2022-02-14 | 2022-04-15 | 北京航空航天大学杭州创新研究院 | Robot control method and related device |
| CN114347039B (en) * | 2022-02-14 | 2023-09-22 | 北京航空航天大学杭州创新研究院 | Robot look-ahead control method and related device |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111400338B (en) | 2022-11-22 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111400338B (en) | SQL optimization method, device, storage medium and computer equipment | |
| CN111061757B (en) | Language conversion method and device of database, electronic equipment and storage medium | |
| US9971967B2 (en) | Generating a superset of question/answer action paths based on dynamically generated type sets | |
| US10909319B2 (en) | Entity linking method, electronic device for performing entity linking, and non-transitory computer-readable recording medium | |
| US10430469B2 (en) | Enhanced document input parsing | |
| CN106611044B (en) | SQL optimization method and equipment | |
| US8364696B2 (en) | Efficient incremental parsing of context sensitive programming languages | |
| WO2022237253A1 (en) | Test case generation method, apparatus and device | |
| CN108491326B (en) | Test behavior a recombination process apparatus and storage medium | |
| US9213548B2 (en) | Code generation method and information processing apparatus | |
| CN112818181A (en) | Graph database retrieval method, system, computer device and storage medium | |
| CN112328621B (en) | SQL conversion method, SQL conversion device, SQL conversion computer equipment and SQL conversion computer readable storage medium | |
| WO2023193547A1 (en) | Method for generating and storing waveform data during circuit simulation, electronic device and storage medium | |
| CN116860583A (en) | Database performance optimization method and device, storage medium and electronic equipment | |
| CN115292347A (en) | Active SQL algorithm performance checking device and method based on rules | |
| CN110888876A (en) | Method and device for generating database script, storage medium and computer equipment | |
| CN111984625B (en) | Database load characteristic processing method and device, medium and electronic equipment | |
| CN115809294A (en) | Rapid ETL method based on Spark SQL temporary view | |
| CN114860872A (en) | Data processing method, device, equipment and storage medium | |
| CN112948419A (en) | Query statement processing method and device | |
| CN117041073B (en) | Network behavior prediction method, system, equipment and storage medium | |
| CN113392124B (en) | Structured language-based data query method and device | |
| CN117609075A (en) | Index use efficiency evaluation method and device, electronic equipment and storage medium | |
| CN116841962A (en) | File processing method and device and electronic equipment | |
| CN115543836A (en) | Script quality detection method and related equipment |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| TA01 | Transfer of patent application right |
Effective date of registration: 20220526 Address after: 518000 China Aviation Center 2901, No. 1018, Huafu Road, Huahang community, Huaqiang North Street, Futian District, Shenzhen, Guangdong Province Applicant after: Shenzhen Ping An medical and Health Technology Service Co.,Ltd. Address before: Room 12G, Area H, 666 Beijing East Road, Huangpu District, Shanghai 200001 Applicant before: PING AN MEDICAL AND HEALTHCARE MANAGEMENT Co.,Ltd. |
|
| TA01 | Transfer of patent application right | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |