Database operation statement optimization method and related product

Abstract

The invention provides a database operation statement optimization method and a related product. The method comprises the following steps: acquiring a database operation statement; all original constraint conditions in the database operation statement are traversed, and to-be-optimized constraint conditions meeting preset requirements are obtained through screening in the original constraint conditions; and deducing according to the constraint condition to be optimized to obtain a newly added constraint condition, and adding the newly added constraint condition into the optimization process of the database operation statement. Through the method, the newly added constraint condition can be obtained through reasonable derivation according to the database operation statement, and the tuple is filtered in advance through the newly added constraint condition, so that the data volume needing to be scanned by the database is reduced, the resource consumption of the database is reduced, and the query efficiency of the database is improved.

Description

Technical Field

[0001] The present invention relates to the field of database technologies, and particularly to an optimization method for database operation statements and related products. Background Art

[0002] In current general database technologies, when an optimizer processes database operation statements such as: SELECT * FROM T1, T2 WHERE T1.a < T2.a and T2.a < 3, most optimizers first push the constraint condition T2.a < 3 of table T2 down to the base table and filter tuples when scanning table T2. Then, a non-equivalent join of the join condition T1.a < T2.a between table T1 and table T2 is performed. Among them, since table T1 lacks constraint conditions, a full table scan is used for table T1. If the data scale of table T1 is very large, it will lead to an extended query time, thereby increasing unnecessary consumption of database resources. Summary of the Invention

[0003] In view of the above problems, the present invention provides an optimization method for database operation statements and related products that overcome the above problems or at least partially solve the above problems.

[0004] One objective of the present invention is to reduce the consumption of database resources.

[0005] A further objective of the present invention is to improve the database query efficiency.

[0006] Another further objective of the present invention is to reasonably derive new constraint conditions based on database operation statements, thereby filtering tuples in advance.

[0007] Specifically, the present invention provides an optimization method for database operation statements, including:

[0008] Obtain a database operation statement;

[0009] Traverse all original constraint conditions in the database operation statement, and screen out the constraint conditions to be optimized that meet the preset requirements from the original constraint conditions;

[0010] Derive new constraint conditions based on the constraint conditions to be optimized, and add the new constraint conditions to the optimization process of the database operation statement.

[0011] Optionally, the step of deriving new constraint conditions based on the constraint conditions to be optimized includes:

[0012] Construct an adjacency matrix of a directed graph according to the constraint conditions to be optimized;

[0013] Derive new constraint conditions based on the adjacency matrix of the directed graph.

[0014] Optionally, the step of deriving a new constraint condition according to the directed graph adjacency matrix includes:

[0015] Performing transitive closure on the adjacency matrix of the directed graph through a preset algorithm to obtain a transitive closure matrix, wherein the preset algorithm is a dynamic programming algorithm for calculating the transitive closure of the directed graph;

[0016] The logical relationship between new vertices is derived based on the transitive closure matrix;

[0017] Convert logical relationships into new constraints corresponding to new vertices.

[0018] Optionally, the step of converting the logical relationship into a newly added constraint condition corresponding to the new vertex includes:

[0019] Operators involved in obtaining logical relationships between new vertices in the constraints to be optimized;

[0020] According to the logical relationship and operators combined with the preset transformation rules, the new constraints corresponding to the new vertices are obtained.

[0021] Optionally, the step of constructing a directed graph adjacency matrix according to the constraints to be optimized includes:

[0022] The left value or right value of the constraint to be optimized is used as a vertex in the adjacency matrix of the directed graph;

[0023] The operators of the constraints to be optimized are treated as directed edges in the adjacency matrix of the directed graph.

[0024] Optionally, the step of using the operator of the constraint to be optimized as a directed edge in the adjacency matrix of the directed graph includes:

[0025] When the constraint to be optimized is that the left value is greater than or equal to the right value, set the left value to point to the right value;

[0026] When the constraint to be optimized is that the left value is less than or equal to the right value, set the right value to point to the left value;

[0027] When the constraint to be optimized is that the left value is equal to the right value, set the left value to point to the right value and the right value to point to the left value.

[0028] Optionally, the preset conversion rules include:

[0029] When the logical relationship shows that the first new vertex points to the second new vertex, and the second new vertex also points to the first new vertex, determining that the newly added constraint condition is that the first new vertex is equal to the second new vertex;

[0030] In the case where the logical relationship shows that the first new vertex points to the second new vertex, but the second new vertex does not point to the first new vertex, if at least one of the operators involved in the logical relationship does not belong to any of greater than or equal to, equal to, and less than or equal to, then the newly added constraint condition is determined to be that the first new vertex is greater than the second new vertex; if all the operators involved in the logical relationship belong to greater than or equal to, equal to, and less than or equal to, then the newly added constraint condition is determined to be that the first new vertex is greater than or equal to the second new vertex;

[0031] When the logical relationship shows that the second new vertex points to the first new vertex, but the first new vertex does not point to the second new vertex, if at least one of the operators involved in the logical relationship does not belong to any of greater than or equal to, equal to, and less than or equal to, then the new constraint condition is determined to be that the first new vertex is less than the second new vertex; if all the operators involved in the logical relationship belong to greater than or equal to, equal to, and less than or equal to, then the new constraint condition is determined to be that the first new vertex is less than or equal to the second new vertex.

[0032] Optionally, the step of selecting the constraints to be optimized that meet preset requirements from the original constraints includes:

[0033] Determine whether the original constraint is a binary expression;

[0034] In the case where the original constraint is a binary expression, determine whether the original constraint contains a volatile function;

[0035] If the original constraint does not contain a volatile function, determine whether the original constraint contains a subquery;

[0036] When the original constraint condition does not contain a subquery, determine whether the operator in the original constraint condition is any one of the preset comparison operators, where the preset comparison operators include: equal to, greater than, greater than or equal to, less than, and less than or equal to;

[0037] When the operator in the original constraint condition is any one of the preset comparison operators, the original constraint condition is used as the constraint condition to be optimized.

[0038] According to another aspect of the present invention, a computer program product is provided, which includes a computer program, and when the computer program is executed by a processor, the steps of any one of the above-mentioned methods for optimizing database operation statements are implemented.

[0039] According to another aspect of the present invention, a computer device is provided, comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, and the processor implements the steps of any of the above-mentioned database operation statement optimization methods when executing the machine executable program.

[0040] The optimization method of the database operation statement of the present invention first obtains the database operation statement; then traverses all the original constraints in the database operation statement, and screens the original constraints to obtain the constraints to be optimized that meet the preset requirements; then derives the new constraints according to the constraints to be optimized, and adds the new constraints to the optimization process of the database operation statement. Through this method, the new constraints can be reasonably derived according to the database operation statement, and the tuples can be filtered in advance through the new constraints, thereby reducing the amount of data that needs to be scanned by the database, reducing the consumption of database resources, and thus improving the efficiency of database query.

[0041] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:

[0043] Figure 1 is a flowchart of a method for optimizing database operation statements according to an embodiment of the present invention;

[0044] Figure 2 is a flow chart of a method for optimizing database operation statements according to another embodiment of the present invention;

[0045] Figure 3 is a schematic diagram of a computer program product according to an embodiment of the present invention;

[0046] Figure 4 is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and

[0047] Figure 5 is a schematic diagram of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0048] It should be understood by those skilled in the art that the embodiments described below are only some embodiments of the present invention, rather than all embodiments of the present invention, and these embodiments are intended to explain the technical principles of the present invention, rather than to limit the protection scope of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should still fall within the protection scope of the present invention.

[0049] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein can be considered as a sequenced list of executable instructions for implementing logical functions, which can be specifically implemented in any computer-readable medium for use by an instruction execution system, device or equipment (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or equipment and execute instructions), or used in combination with these instruction execution systems, devices or equipment.

[0050] The present invention provides a method for optimizing database operation statements. Figure 1 FIG. 1 is a flow chart of a method for optimizing database operation statements according to an embodiment of the present invention. Figure 1 As shown, the method for optimizing database operation statements at least includes the following steps S101 to S104.

[0051] Step S101, obtaining database operation statements. An optional example is SQL (Structured Query Language). SQL statements have multiple functions such as data manipulation and data definition. This language has interactive characteristics and can provide great convenience for users. In this step, the way to receive database operation statements can generally be active input by the user, or automatically generated by the database program during operation due to business needs. Those skilled in the art can decide the way to receive database operation statements according to actual conditions.

[0052] Step S102, traverse all original constraint conditions in the database operation statement, wherein the constraint condition generally refers to a conditional statement in an SQL statement used to screen and filter data.

[0053] Step S103, selecting the constraints to be optimized that meet the preset requirements from the original constraints.

[0054] In some optional embodiments, the screening step of step S103 may generally include: determining whether the original constraint is a binary expression; if the original constraint is a binary expression, determining whether the original constraint contains a volatile function; if the original constraint does not contain a volatile function, determining whether the original constraint contains a subquery; if the original constraint does not contain a subquery, determining whether the operator in the original constraint is any one of the preset comparison operators, and the preset comparison operators may generally include: equal to, greater than, greater than or equal to, less than, less than or equal to; if the operator in the original constraint is any one of the preset comparison operators, the original constraint is used as the constraint to be optimized. In a database, a binary expression generally refers to an expression containing two operands and an operator, such as A>B. Volatile functions generally refer to functions whose results may change each time they are recalculated or refreshed, such as random functions and current date and time functions.

[0055] Since the scheme of the present invention needs to derive new constraints in combination with the transitivity of comparison operators, in order to ensure that the derivation results will not affect the original execution results, it is necessary to ensure that the constraints involved in the derivation meet the following preset requirements: the original constraints are binary expressions, do not contain volatile functions, do not contain subqueries, and the operators in the original constraints are any of the preset comparison operators. The constraints to be optimized obtained by screening can be derived in combination with the transitivity of comparison operators, and the derivation results will not affect the original execution results, but will help the database to further filter data, thereby reducing the number of data scans.

[0056] It should be noted that the original constraints must all meet the above four preset requirements to be used as constraints to be optimized. There is no limit on the order of judging the four preset requirements, and technical personnel in this field can decide the order of judging the preset requirements according to actual conditions.

[0057] Step S104, deriving new constraints based on the constraints to be optimized, and adding the new constraints to the optimization process of the database operation statement.

[0058] Through this method, new constraints can be reasonably derived according to database operation statements, and tuples can be filtered in advance through the new constraints, thereby reducing the amount of data that the database needs to scan, reducing database resource consumption, and thus improving database query efficiency.

[0059] In some alternative embodiments, an alternative derivation scheme for step S104 may include: constructing an adjacency matrix of a directed graph according to the constraint conditions to be optimized; deriving new constraint conditions based on the adjacency matrix of the directed graph. The adjacency matrix of a directed graph is a matrix form used to represent the relationships between vertices in a directed graph. Through this matrix, new constraint conditions can be derived based on multiple constraint conditions to be optimized.

[0060] Optionally, the step of deriving new constraint conditions based on the adjacency matrix of the directed graph generally may include: obtaining a transitive closure matrix by performing a transitive closure on the adjacency matrix of the directed graph through a preset algorithm, where the preset algorithm is a dynamic programming algorithm for calculating the transitive closure of a directed graph; deriving the logical relationships between new vertices based on the transitive closure matrix; converting the logical relationships into new constraint conditions corresponding to the new vertices. An alternative example of the dynamic programming algorithm is the WarShall algorithm, which is also known as the Warshall algorithm or the transitive closure algorithm. This is a classic algorithm for calculating the transitive closure of a directed graph. This algorithm can, through the idea of dynamic programming, utilize the existing path information to gradually derive all possible paths, thereby obtaining the transitive closure of the graph. Those skilled in the art can select the specific algorithm of the dynamic programming algorithm according to the actual situation.

[0061] Optionally, the step of converting the logical relationships into new constraint conditions corresponding to the new vertices generally may include: obtaining the operators involved in the logical relationships between new vertices in the constraint conditions to be optimized; performing a conversion based on the logical relationships and the operators in combination with preset conversion rules to obtain new constraint conditions corresponding to the new vertices.

[0062] Among them, the step of constructing an adjacency matrix of a directed graph according to the constraint conditions to be optimized generally may include: using the left value or the right value of the constraint conditions to be optimized as vertices in the adjacency matrix of the directed graph; using the operator of the constraint conditions to be optimized as a directed edge in the adjacency matrix of the directed graph.

[0063] The step of using the operator of the constraint conditions to be optimized as a directed edge in the adjacency matrix of the directed graph generally may include: in the case where the constraint conditions to be optimized are that the left value is greater than or greater than or equal to the right value, setting the left value to point to the right value; in the case where the constraint conditions to be optimized are that the left value is less than or less than or equal to the right value, setting the right value to point to the left value; in the case where the constraint conditions to be optimized are that the left value is equal to the right value, setting the left value to point to the right value and at the same time the right value to point to the left value. For example: in the case where the constraint conditions to be optimized are A > B or A >= B, setting it as A → B; in the case where the constraint conditions to be optimized are A < B or A <= B, setting it as A ← B; in the case where the constraint conditions to be optimized are A = B, setting it as A ←→ B.

[0064] The preset conversion rule generally includes: when the logical relationship shows that the first new vertex points to the second new vertex, and the second new vertex also points to the first new vertex, determining that the new constraint condition is that the first new vertex is equal to the second new vertex.

[0065] When the logical relationship shows that the first new vertex points to the second new vertex, but the second new vertex does not point to the first new vertex, if at least one of the operators involved in the logical relationship does not belong to any of greater than or equal to, equal to, and less than or equal to, then the new constraint condition is determined to be that the first new vertex is greater than the second new vertex; if all the operators involved in the logical relationship belong to greater than or equal to, equal to, and less than or equal to, then the new constraint condition is determined to be that the first new vertex is greater than or equal to the second new vertex.

[0066] When the logical relationship shows that the second new vertex points to the first new vertex, but the first new vertex does not point to the second new vertex, if at least one of the operators involved in the logical relationship does not belong to any of greater than or equal to, equal to, and less than or equal to, then the new constraint condition is determined to be that the first new vertex is less than the second new vertex; if all the operators involved in the logical relationship belong to greater than or equal to, equal to, and less than or equal to, then the new constraint condition is determined to be that the first new vertex is less than or equal to the second new vertex.

[0067] In order to clearly illustrate the above logic conversion rules, some optional embodiments are provided for illustration:

[0068] Example 1: Among the constraints to be optimized, there are A=B, B=C, and C=D, and there is no constraint between A and D. After derivation, the transitive closure matrix shows that there are new vertices A and D, indicating that there is a logical relationship between the two, and the logical relationship between the two is:

[0069] A←→B←→C←→D

[0070] Based on the above logical relationship and the preset conversion rules, when the first new vertex points to the second new vertex and the second new vertex also points to the first new vertex, the newly added constraint condition is determined to be that the first new vertex is equal to the second new vertex, that is, A=D.

[0071] Embodiment 2: Among the constraints to be optimized, there are A>=B, B=C, C>=D, and there is no constraint between A and D. After derivation, the transitive closure matrix shows that there are new vertices A and D, indicating that there is a logical relationship between the two, and the logical relationship between the two is:

[0072] A→B←→C→D

[0073] Based on the above logical relationship and combined with the preset conversion rules, when the first new vertex points to the second new vertex, but the second new vertex does not point to the first new vertex, since the operators involved in the logical relationship are all greater than or equal to, equal to, and less than or equal to, the new constraint condition is determined to be that the first new vertex is greater than or equal to the second new vertex, that is, A>=D.

[0074] Example 3: Among the constraints to be optimized, there are A>B, B=C, C>=D, and there is no constraint between A and D. After derivation, the transitive closure matrix shows that there are new vertices A and D, indicating that there is a logical relationship between the two, and the logical relationship between the two is also:

[0075] A→B←→C→D

[0076] Based on the above logical relationship and combined with the preset conversion rules, when the first new vertex points to the second new vertex, but the second new vertex does not point to the first new vertex, since at least one of the operators involved in the logical relationship does not belong to any of greater than or equal to, equal to, and less than or equal to, the new constraint condition is determined to be that the first new vertex is greater than or equal to the second new vertex, that is, A>D.

[0077] Example 4: Among the constraints to be optimized, there are A<=B, B=C, and D>=C, and there is no constraint between A and D. After derivation, the transitive closure matrix shows that there are new vertices A and D, indicating that there is a logical relationship between the two, and the logical relationship between the two is:

[0078] A←B←→C←D

[0079] Based on the above logical relationship and combined with the preset conversion rules, when the second new vertex points to the first new vertex, but the first new vertex does not point to the second new vertex, since the operators involved in the logical relationship are all greater than or equal to, equal to, and less than or equal to, the new constraint condition is determined to be that the first new vertex is less than or equal to the second new vertex, that is, A<=D.

[0080] Example 5: Among the constraints to be optimized, there are A<=B, B=C, and D>C, and there is no constraint between A and D. After derivation, the transitive closure matrix shows that there are new vertices A and D, indicating that there is a logical relationship between the two, and the logical relationship between the two is also:

[0081] A←B←→C←D

[0082] Based on the above logical relationship and the preset conversion rules, when the second new vertex points to the first new vertex, but the first new vertex does not point to the second new vertex, since at least one of the operators involved in the logical relationship does not belong to any of greater than or equal to, equal to, and less than or equal to, the new constraint condition is determined to be that the first new vertex is less than or equal to the second new vertex, that is, A <D。

[0083] Based on the method of the present invention, the optimizer in the database can perform non-equivalent predicate deduction based on the transitivity between predicates under certain conditions, thereby obtaining new constraints, and filtering tuples in advance through the new constraints, thereby improving query efficiency.

[0084] Figure 2 FIG. 1 is a flow chart of a method for optimizing database operation statements according to another embodiment of the present invention. Figure 2 As shown, the method for optimizing the database operation statement at least includes the following steps S201 to S213.

[0085] Step S201, obtain database operation statements and enter the optimization phase.

[0086] Step S202, traverse all original constraint conditions in the database operation statement.

[0087] Step S203, determine whether the original constraint condition is a binary expression. If the original constraint condition is not a binary expression, it means that the original constraint condition cannot be used as a constraint condition to be optimized to participate in logic deduction, so step S208 is directly executed.

[0088] Step S204: if the result of step S203 is yes, determine whether the original constraint condition contains a volatile function. If the original constraint condition contains a volatile function, it means that the original constraint condition cannot be used as a constraint condition to be optimized to participate in logic deduction, so step S208 is directly executed.

[0089] Step S205: If the result of step S204 is negative, determine whether the original constraint condition contains a subquery. If the original constraint condition contains a subquery, it means that the original constraint condition cannot be used as a constraint condition to be optimized to participate in logic deduction, so step S208 is directly executed.

[0090] Step S206, if the judgment in step S205 is no, it is determined whether the operator in the original constraint condition is any of the preset comparison operators. If the original constraint condition is not any of the preset comparison operators, it means that the original constraint condition cannot be used as the constraint condition to be optimized to participate in the logic deduction, so step S208 is directly executed.

[0091] Step S207, when the judgment in step S206 is yes, use the original constraint condition as the constraint condition to be optimized.

[0092] Step S208, check whether the traversal of the database operation statement is ended. If not, continue to execute step S202 to traverse the next original constraint condition.

[0093] Step S209, when the judgment in step S208 is yes, construct an adjacency matrix of a directed graph according to the constraint condition to be optimized.

[0094] Step S210, perform a transitive closure on the adjacency matrix of the directed graph through a preset algorithm to obtain a transitive closure matrix.

[0095] Step S211, deduce the logical relationship between new vertices according to the transitive closure matrix.

[0096] Step S212, convert the logical relationship into a new added constraint condition corresponding to the new vertex.

[0097] Step S213, continue with the remaining optimization work of the optimizer.

[0098] Through this method, new added constraint conditions can be reasonably deduced according to the database operation statement, and tuples can be filtered in advance through the new added constraint conditions, thereby reducing the amount of data that the database needs to scan, reducing the consumption of database resources, and further improving the database query efficiency.

[0099] To clearly illustrate the processing flow of this method, some optional embodiments are proposed as follows:

[0100] Embodiment 1:

[0101] SELECT * FROM t2 WHERE col_2 < col_1 AND col_1 < 1000 Formula 1

[0102] When the database receives the SQL statement shown in Formula 1 for optimization, it first executes step S202: traverse all the original constraint conditions col_2 < col_1 and col_1 < 1000 in the database operation statement.

[0103] Then execute step S203 with col_2 < col_1: Since col_2 < col_1 belongs to a binary expression, it is determined to be yes;

[0104] When the judgment in step S203 is yes, execute step S204: Since col_2 < col_1 does not contain volatile functions, it is determined to be no.

[0105] In the case where the judgment in step S204 is negative, step S205 is executed: Since only two data columns are involved in col_2 < col_1 and it does not contain a subquery, the judgment is negative.

[0106] In the case where the judgment in step S205 is negative, step S206 is executed: Since the operator in col_2 < col_1 is less than, which belongs to the preset comparison operator, the judgment in step S206 is positive.

[0107] In the case where the judgment in step S206 is positive, step S207 is executed: col_2 < col_1 is used as the constraint condition to be optimized.

[0108] Step S208 is executed: At this time, col_1 < 1000 remains in the original constraint conditions. Therefore, step S203 needs to be executed with col_1 < 1000. The execution logic is the same as that of col_2 < col_1 and will not be elaborated here. Finally, the constraint conditions to be optimized are: col_2 < col_1 and col_1 < 1000.

[0109] After the traversal in step S208 is completed, step S209 is executed: Construct a directed graph adjacency matrix:

[0110] Among them, col_2 < col_1 can be constructed as col_2 ← col_1;

[0111] col_1 < 1000 can be constructed as col_1 ← 1000.

[0112] Step S210 is executed: The transitive closure of the directed graph adjacency matrix is obtained through a preset algorithm (such as the Warshall algorithm) to get the transitive closure matrix. It can be expressed as: col_2 ← col_1 ← 1000.

[0113] Step S211 is executed: Among them, there are new vertices col_2 and 1000, and the logical relationship between the two is: col_2 ← col_1 ← 1000.

[0114] Step S212 is executed: The logical relationship between col_2 and 1000 is combined with the preset conversion rules for conversion. Since it belongs to the case where the second new vertex points to the first new vertex, but the first new vertex does not point to the second new vertex, and the operators involved in the logical relationship are all < and do not belong to any of greater than or equal to, equal to, and less than or equal to, the new constraint condition is determined to be that the first new vertex is less than the second new vertex, that is, col_2 < 1000.

[0115] Step S213 is executed: The remaining optimization work of the optimizer is continued according to the newly added constraint condition col_2 < 1000.

[0116] Finally, when the database processes the SQL statement shown in Formula 1, it originally needs to scan all the data in the col_2 column of the t2 table and compare them with the col_1 column. After applying the method of the present invention, when scanning data, it only needs to scan the data in the col_2 column of the t2 table that is less than 1000. When the amount of data in the t2 table is large, the consumption of database resources can be greatly reduced, and the processing efficiency can be improved.

[0117] In actual applications, when facing the SQL statement shown in Formula 1, the execution time of the database is 1250ms before the method of the present invention is applied, and the execution time is 0.523ms after the method of the present invention is applied, and the processing performance is improved by several orders of magnitude.

[0118] The flow chart provided by the present embodiment is not intended to indicate that the operation of the method will be performed in any particular order, or that all operations of the method are included in all every case. In addition, the method may include additional operations. Within the scope of the technical thinking provided by the present embodiment method, additional changes may be made to the above method.

[0119] It should be understood that in some embodiments, each part can be implemented by hardware, software, firmware or a combination thereof. In the above embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0120] This embodiment also provides a computer program product 10 , a computer readable storage medium 20 , and a computer device 30 . Figure 3 is a schematic diagram of a computer program product 10 according to an embodiment of the present invention, Figure 4 is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention, Figure 5 is a schematic diagram of a computer device 30 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which implements the steps of any of the above-mentioned methods for optimizing database operation statements when executed by a processor 32. The computer-readable storage medium 20 stores the above-mentioned computer program 11, which implements the steps of any of the above-mentioned methods for optimizing database operation statements when executed by a processor 32. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored in the memory 31 and running on the processor 32.

[0121] The computer program 11 for performing the operation of the present invention may be an assembly instruction, an instruction set architecture (ISA) instruction, a machine instruction, a machine-related instruction, a microcode, a firmware instruction, a state setting data, a configuration data of an integrated circuit, or a source code or an object code written in any combination of one or more programming languages ​​and process programming languages. The computer program 11 may be executed entirely on the user's computer, partially on the user's computer, as an independent software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network (including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., using an Internet service provider via the Internet). In some embodiments, in order to perform various aspects of the present invention, an electronic circuit including, for example, a programmable logic circuit, a field programmable gate array (FPGA) or a programmable logic array (PLA) may execute computer-readable program instructions by utilizing the state information of the computer-readable program instructions to personalize the electronic circuit.

[0122] For the purposes of the description of the present embodiment, the computer program product 10 is a related product that includes the computer program 11. For the purposes of the description of the present embodiment, the computer readable storage medium 20 is a tangible device capable of retaining and storing the computer program 11, which may be any device that can contain, store, communicate, propagate or transmit the program 11 for use with or in conjunction with an instruction execution system, device or apparatus. More specific examples (a non-exhaustive list) of the computer readable storage medium 20 include the following: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disk read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device, and any suitable combination of the foregoing.

[0123] The computer device 30 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smart phone. In some examples, the computer device 30 may be a cloud computing node. The computer device 30 may be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, a program module may include routines, programs, target programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. The computer device 30 may be implemented in a distributed cloud computing environment where remote processing devices linked via a communication network perform tasks. In a distributed cloud computing environment, program modules may be located on a local or remote computing system storage medium including a storage device.

[0124] The computer device 30 may include a processor 32 adapted to execute stored instructions, and a memory 31 providing temporary storage space for the operation of the instructions during operation. The processor 32 may be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory 31 may include a random access memory (RAM), a read-only memory, a flash memory, or any other suitable storage system.

[0125] The computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows data to be input and output with external devices that may be connected to the computer device. The network adapter / interface may provide communication between the computer device and a network, which is typically shown as a communication network.

[0126] At this point, those skilled in the art should recognize that, although multiple exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications that conform to the principles of the present invention can still be directly determined or derived based on the content disclosed in the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and recognized as covering all these other variations or modifications.

Claims

1. A method for optimizing database operation statements, comprising: Obtaining the database operation statement; Traversing all original constraint conditions in the database operation statement, and screening out constraint conditions to be optimized that meet preset requirements from the original constraint conditions; New constraints are derived based on the constraints to be optimized, and the new constraints are added to the optimization process of the database operation statement.

2. The method for optimizing database operation statements according to claim 1, wherein: The step of deriving the newly added constraint conditions according to the constraint conditions to be optimized comprises: Constructing a directed graph adjacency matrix according to the constraints to be optimized; The newly added constraint condition is derived according to the directed graph adjacency matrix.

3. The method for optimizing database operation statements according to claim 2, wherein: The step of deriving the newly added constraint condition according to the directed graph adjacency matrix comprises: Performing transitive closure on the adjacency matrix of the directed graph by a preset algorithm to obtain a transitive closure matrix, wherein the preset algorithm is a dynamic programming algorithm for calculating the transitive closure of the directed graph; Derivation of the logical relationship between new vertices according to the transitive closure matrix; The logical relationship is converted into the newly added constraint condition corresponding to the new vertex.

4. The method for optimizing database operation statements according to claim 3, wherein: The step of converting the logical relationship into the newly added constraint condition corresponding to the new vertex comprises: Acquire operators involved in the logical relationship between the new vertices in the constraint conditions to be optimized; The newly added constraint condition corresponding to the new vertex is obtained by performing conversion according to the logical relationship and the operator in combination with a preset conversion rule.

5. The method for optimizing database operation statements according to claim 4, wherein: The step of constructing a directed graph adjacency matrix according to the constraint conditions to be optimized comprises: Using the left value or the right value of the constraint condition to be optimized as a vertex in the adjacency matrix of the directed graph; The operators of the constraint conditions to be optimized are used as directed edges in the adjacency matrix of the directed graph.

6. The method for optimizing database operation statements according to claim 5, wherein: The step of using the operator of the constraint to be optimized as a directed edge in the adjacency matrix of the directed graph comprises: When the constraint to be optimized is that the left value is greater than or equal to the right value, the left value is set to point to the right value; When the constraint to be optimized is that the left value is less than or equal to the right value, the right value is set to point to the left value; When the constraint to be optimized is that the left value is equal to the right value, the left value is set to point to the right value and the right value is set to point to the left value.

7. The method for optimizing database operation statements according to claim 6, wherein: The preset conversion rules include: When the logical relationship shows that the first new vertex points to the second new vertex, and the second new vertex also points to the first new vertex, determining that the newly added constraint condition is that the first new vertex is equal to the second new vertex; In the case where the logical relationship shows that the first new vertex points to the second new vertex, but the second new vertex does not point to the first new vertex, if at least one of the operators involved in the logical relationship does not belong to any of greater than or equal to, equal to, and less than or equal to, then the newly added constraint condition is determined to be that the first new vertex is greater than the second new vertex; if the operators involved in the logical relationship all belong to greater than or equal to, equal to, and less than or equal to, then the newly added constraint condition is determined to be that the first new vertex is greater than or equal to the second new vertex; When the logical relationship shows that the second new vertex points to the first new vertex, but the first new vertex does not point to the second new vertex, if at least one of the operators involved in the logical relationship does not belong to any of greater than or equal to, equal to, and less than or equal to, then the newly added constraint condition is determined to be that the first new vertex is less than or equal to the second new vertex; if the operators involved in the logical relationship all belong to greater than or equal to, equal to, and less than or equal to, then the newly added constraint condition is determined to be that the first new vertex is less than or equal to the second new vertex.

8. The method for optimizing database operation statements according to claim 1, wherein: The step of selecting the constraints to be optimized that meet the preset requirements from the original constraints includes: Determining whether the original constraint condition is a binary expression; In the case where the original constraint condition is the binary expression, determining whether the original constraint condition contains a volatile function; In the case that the original constraint condition does not include the volatile function, determining whether the original constraint condition includes a subquery; In the case that the original constraint condition does not include the subquery, determining whether the operator in the original constraint condition is any one of the preset comparison operators, the preset comparison operators including: equal to, greater than, greater than or equal to, less than, less than or equal to; In the case where the operator in the original constraint condition is any one of the preset comparison operators, the original constraint condition is used as the constraint condition to be optimized.

9. A computer program product, comprising a computer program, which, when executed by a processor, implements the steps of the method for optimizing database operation statements according to any one of claims 1 to 8.

10. A computer device comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, wherein the processor implements the steps of the method for optimizing database operation statements according to any one of claims 1 to 8 when executing the machine executable program.

Images