Query optimization method and system based on key truth value path table and set merging

Abstract

The invention belongs to the technical field of batch SQL query, and particularly discloses a query optimization method and system based on a key truth value path table and set merger, and the method comprises the following steps: constructing parameter data and a data dictionary required by SQL query conditions according to a value space; analyzing the SQL statement, and generating and analyzing an SQL syntax tree; executing set query to obtain a query hit result set Sh of all parameter combinations; traversing query hit records in the query hit result set Sh, and reversely deducing query parameters; and packaging a query mapping result according to a query number parameter combination corresponding to the reverse hit record. By adopting the technical scheme, on the basis of the key truth value path table and set merging, the large-scale data volume is optimized, and the combined Cartesian product batch SQL query tasks are generated.

Description

Technical Field

[0001] The invention belongs to the technical field of batch SQL query, and relates to a query optimization method and system based on key truth path table and set merging. Background Art

[0002] With the increase in data volume and the complexity of business requirements, SQL query (Structured Query Language, a standard language for managing relational databases) optimization is becoming increasingly important. In particular, when large-scale data is used as the value space of multi-condition query conditions in SQL, the data in these different value spaces will be filled into the SQL query condition placeholders as query parameters in the form of Cartesian product combination, so as to execute batch SQL query tasks.

[0003] In order to avoid the generation of batch SQL query tasks, we will consider replacing the single-value matching '=' in the query condition with a set query of multi-value matching 'in'. Although this can obtain the query results of all query parameter combinations in multiple value spaces at one time and will not generate batch SQL query tasks, it is impossible to distinguish the set query results and form a mapping result between the query hit records and the query parameters.

[0004] For this type of single-value matching query, as the data size of the value space dynamically filled with placeholders in the query conditions and the number of query conditions increase, the number of tasks to execute batch SQL queries will increase exponentially, causing serious time loss and performance bottlenecks, and will put tremendous pressure on the database server under high-concurrency queries.

[0005] Traditional SQL query optimization technologies include query plan optimization, index optimization, cache optimization, parallel query, etc. These traditional optimization methods do not fundamentally solve the optimization of batch SQL query tasks that generate combined Cartesian products of large-scale data volumes, which has limited optimization effects on the above scenarios. Summary of the invention

[0006] The purpose of the present invention is to provide a query optimization method and system based on key truth path table and set merging to solve the performance bottleneck problem caused by generating Cartesian product combinations and executing a large number of SQL when a large-scale value space is used as a dynamic filling parameter of a condition placeholder in a multi-condition SQL query statement.

[0007] In order to achieve the above object, the basic scheme of the present invention is: a query optimization method based on key truth path table and set merging, comprising the following steps:

[0008] According to the value space, construct the parameter data and data dictionary required for SQL query conditions;

[0009] Parse SQL statements, generate and analyze SQL syntax trees;

[0010] Execute the analyzed SQL statement, specifically execute the analyzed set query SQL statement through JDBC (Java Database Connectivity) to obtain the query hit result set S of all parameter combinations h ;

[0011] Traverse the query hit result set S h Query the hit records and infer the query parameters;

[0012] According to the query number parameter combination corresponding to the inferred hit record, the data dictionary corresponding to each parameter in the query parameter combination is queried to obtain the data label combination of all parameter combinations, and the query data label combination and hit record mapping result are generated. The mapping result is converted, with the label combination as the key and the hit record query field value as the value value, the hit record field values ​​corresponding to the label combination are merged, and the query mapping result is encapsulated.

[0013] The working principle and beneficial effect of this basic solution are as follows: This technical solution constructs a query parameter data dictionary for querying data labels corresponding to specific data values ​​during data inversion. The SQL statement is parsed by the Antlr4 tool to generate a syntax tree, and specific sub-steps are performed for different types of tree nodes in the syntax tree to implement syntax tree analysis.

[0014] Execute a collection query SQL to obtain query hit records for all parameter combinations at one time, then traverse each query hit record to reverse the query parameters. This solves the performance bottleneck problem caused by the Cartesian product combination and the execution of a large number of SQL when a large-scale value space is used as a dynamic filling parameter for condition placeholders in multi-condition SQL query statements. It also solves the problem that traditional collection query results cannot form a mapping result between query hit records and query parameters.

[0015] Furthermore, according to the value space, the method of constructing the parameter data and data dictionary required for the SQL query condition is:

[0016] The value of the data in the value space is used as the key of the data dictionary, and the label of the data is used as the value of the data dictionary. The data label corresponding to the data value is queried through the data dictionary. The data label in the value space is unique. When generating the dictionary, the labels corresponding to the data with the same value are merged, and the data dictionaries of the value spaces M1, M2, and M3 are obtained respectively:

[0017] D1:{'a0':['L 10 '],'a1':['L 11 '],…,'a9':['L19 ']}

[0018] D2:{'b0':['L 20 '],'b1':['L 21 '],…,'b9':['L 29 ']}

[0019] D3:{'c0':['L 30 '],'c1':['L 31 '],…,'c9':['L 39 ']}

[0020] Among them, L 1i , L 2i , L 3i Respectively represent the labels corresponding to different values ​​in the value space M1, M2, and M3, a i , b i 、c i (i=1,2,…,9) represent different values ​​in different value spaces, i=1,2,…,9.

[0021] The query parameter data dictionary is used to query the data labels corresponding to specific data values ​​during data inversion, and the operation is simple.

[0022] Further, the method of parsing SQL statements, generating and analyzing SQL syntax trees is as follows:

[0023] Use the Antlr4 framework to perform syntax analysis on the original SQL statement to obtain the syntax tree of the SQL statement. Define specific syntax actions for different types of tree nodes in the syntax tree to implement syntax tree analysis, specifically:

[0024] Generate a key truth path table and perform in-order traversal on the syntax tree of the WHERE clause of the SQL query statement;

[0025] According to the size of the value space corresponding to the placeholder, the placeholder-related conditions in the SQL query statement are reconstructed into in set query conditions. The reconstructed SQL query statement is:

[0026] select column4, column1, column2, column3 from table1 where(column1 in(?,?,…,?)and column1 in(?,?,…,?))or column2 in(?,?,…,?)

[0027] The reconstructed list matching conditions contain 10 placeholders '?' in brackets, the condition fields are column1, column2, column3, and the query field is column4.

[0028] Saves the atomic condition information of the WHERE clause in the original SQL statement query, including the atomic condition operation logic, the database table field name used in the atomic condition, and the operation value information.

[0029] The SQL statement is parsed by the Antlr4 tool to generate a syntax tree, and specific sub-steps are performed for different types of tree nodes in the syntax tree to implement syntax tree analysis.

[0030] Furthermore, the key truth path is defined as the atomic conditions that need to be true at least for the WHERE clause in the SQL statement to be true, that is, when the atomic conditions covered by the key truth path in the WHERE clause are all true, the WHERE clause is true regardless of whether the remaining atomic conditions are true or not;

[0031] The key truth value path is represented by a 01 string, where each item corresponds to an atomic condition in the WHERE clause, 1 represents a key truth value, and 0 represents a non-key truth value. The key truth value path table K stores all the key truth value paths of the WHERE clause, and the non-key truth value path table NK stores all the non-key truth value paths of the WHERE clause. The steps to generate the key truth value path table are:

[0032] When the WHERE clause is an atomic condition, its key truth path table is {1}, and its non-key truth path table is {0}, that is, K atomCond ={1},NK atomCond ={0};

[0033] When the WHERE clause is a combined condition, its key truth path table is related to the type of the sub-condition logical operator; if its sub-condition logical operator is AND, its key truth path table is {Cartesian product combination of sub-condition key truth path tables};

[0034] If its sub-condition logical connector is OR, then one of the sub-conditions is a critical truth value and the other is a non-critical truth value. Its critical truth value path table is {the Cartesian product combination of the critical truth value path table of sub-condition 1 and the non-critical truth value path table of sub-condition 2}∪{the Cartesian product combination of the non-critical truth value path table of sub-condition 1 and the critical truth value path table of sub-condition 2};

[0035] When the WHERE clause is a combination condition, its non-key truth path table is the Cartesian product of {the non-key truth path table of sub-condition 1 and the non-key truth path table of sub-condition 2}, regardless of the type of the sub-condition logical operator;

[0036] When the WHERE clause is a combined condition, the key truth path table and the non-key truth path table are:

[0037]

[0038] NK combineCond =(NK childCond1 ×NK childCond2 ),

[0039] Among them, combineCond is composed of two sub-conditions combined by logical operators. The sub-conditions can be atomic conditions or combined conditions. The logical operators include logical AND and logical OR. ChildCond1 and childCond2 are sub-conditions of combineCond.

[0040] Generate a key truth path table for subsequent use.

[0041] Furthermore, the syntax tree of the WHERE clause of the SQL query statement is traversed in order, and the traversal process is as follows:

[0042] Traversing to condition C1, we get its key truth path set as {1} and its non-key truth path set as {0}, that is, K atomCond ={1},NK atomCond ={0};

[0043] Traverse to condition C2 and obtain its key truth path set as {1} and its non-key truth path set as {0};

[0044] Traversing to condition C4, because its sub-conditions C1 and C2 are connected by AND, the key truth path set of C4 is the Cartesian product combination of the key truth path sets of the sub-conditions, that is, {11}, and the non-key truth path set is the Cartesian product combination of the non-key path set of C1 and the non-key path set of C2, that is, {00};

[0045] Traverse to condition C3 and obtain its key truth path set as {1} and its non-key truth path set as {0};

[0046] Traversing to condition C5, because its sub-conditions C4 and C3 are connected by OR, the key truth path set of C5 is {Cartesian product combination of C4's key truth path set and C3's non-key truth path set}∪{Cartesian product combination of C4's non-key truth path set and C3's key truth path set}, that is, {110, 001};

[0047] Because condition C5 is the top-level condition of the WHERE clause in the SQL statement, the key truth path set of condition C5 is the key truth path table of the SQL query statement in the embodiment, path1 represents the key truth path "110", path2 represents the key truth path "001", where each digit of the key truth path corresponds to conditions C1, C2, and C3 from left to right.

[0048] Perform an in-order traversal on the syntax tree of the WHERE clause of the SQL query statement to obtain a key truth value path set and a non-key truth value path set.

[0049] Further, traverse the query hit result set S h Query the hit records and infer the query parameters. The specific steps are as follows:

[0050] S1, determine whether each condition in the WHERE clause is true for each hit record, obtain their truth value strings, substitute the atomic condition field data in the hit record into the placeholder at the corresponding position according to the atomic condition information in the SQL query statement, and determine the atomic condition truth value of the record;

[0051] S2, compare and match the generated truth string with the key truth path in the key truth path table to obtain the effective key truth path of each hit record;

[0052] S3, traverses all effective key truth value paths of the query hit record, reversely infers the query parameter data, and obtains the query parameters of each hit record.

[0053] Iterate through each query hit record and reverse the query parameters. The operation is simple.

[0054] Further, the step of determining the true value of the atomic condition of the hit record in step S1 is:

[0055] When the atomic condition is a condition involving a placeholder data field, optimization is performed for the placeholder-related conditions with equal value matching. It is determined whether the condition field data in the corresponding hit record exists in the data dictionary corresponding to the condition. If it exists, it means that the condition may be true when the record is hit, which is recorded as 1; if it does not exist, it means that the condition must be false when the record is hit, which is recorded as 0;

[0056] When the atomic condition is a condition that does not involve placeholder data fields, after the logical semantics of the conditional expression is translated, the corresponding conditional field data in the hit record is substituted into the conditional expression operation. If it is true, it is recorded as 1; if it is false, it is recorded as 0;

[0057] When the atomic condition is a placeholder constant condition, the logical semantics of the conditional expression is translated and the data in the data dictionary corresponding to the condition is substituted into the conditional expression operation. If there is data in the dictionary that satisfies the logic of the expression and makes the condition true, it is recorded as 1; if not, it is recorded as 0.

[0058] When the atomic condition is a non-placeholder constant condition, the logical semantics of the conditional expression is translated and logical operations are performed. If it is true, it is recorded as 1; if it is false, it is recorded as 0.

[0059] Determine whether each hit record meets each condition in the WHERE clause and obtain their truth value strings.

[0060] Further, in step S2, the method for comparing and matching the generated truth value string with the key truth value path of the key truth value path table is:

[0061] If there is an atomic condition in the truth string, the corresponding position of the atomic condition in the key truth path is 1, and the truth value of the atomic condition is 0, that is, the atomic condition is the key truth value in the key truth path, but the truth value of the atomic condition is false, then the truth string does not match the key path;

[0062] If all key truth values ​​corresponding to the key truth path in the truth string are 1, the truth string matches the key truth path, and the key truth path is the effective key truth path of the hit record.

[0063] The hit record is compared and matched with the key truth value path in the key truth value path table through the generated truth value string to obtain the effective key truth value path of each hit record.

[0064] Furthermore, in step S3, for each effective key truth value path, the reverse deduction method is as follows:

[0065] S31, when the placeholder-related atomic condition is a key condition, the parameter of the condition takes the hit result data;

[0066] S32, when the placeholder-related atomic condition is a non-critical condition, the parameter of the condition is the entire data of the corresponding data space;

[0067] S33, performing Cartesian product combination on each parameter set involved in the inverse of the placeholder atomic condition, and the combination result is all query parameter combinations corresponding to the records under the key truth path;

[0068] S34, repeat steps S31-S33 for all matched key truth value paths to obtain all query parameter combinations corresponding to the hit record.

[0069] Traverse the effective key truth value path corresponding to the query hit record, reverse the query parameter data, and obtain the query parameters of each hit record.

[0070] The present invention also provides a query optimization system based on a key truth path table and set merging, comprising a processing unit, wherein the processing unit executes the method of the present invention to perform SQL optimization query.

[0071] This system uses processing modules to optimize SQL queries for ease of use. BRIEF DESCRIPTION OF THE DRAWINGS

[0072] Figure 1 It is a flow chart of a query optimization method based on a key truth path table and set merging of the present invention;

[0073] Figure 2 It is a structural diagram of a syntax tree of an SQL query statement of the query optimization method based on a key truth path table and set merging of the present invention. DETAILED DESCRIPTION

[0074] Embodiments of the present invention are described in detail below, examples of which are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present invention, and cannot be understood as limiting the present invention.

[0075] In the description of the present invention, it is necessary to understand that the terms "longitudinal", "lateral", "up", "down", "front", "back", "left", "right", "vertical", "horizontal", "top", "bottom", "inside", "outside", etc., indicating the orientation or position relationship, are based on the orientation or position relationship shown in the accompanying drawings, and are only for the convenience of describing the present invention and simplifying the description, rather than indicating or implying that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore cannot be understood as a limitation on the present invention.

[0076] In the description of the present invention, unless otherwise specified and limited, it should be noted that the terms "installed", "connected" and "connected" should be understood in a broad sense. For example, it can be a mechanical connection or an electrical connection, or it can be the internal connection between two components. It can be a direct connection or an indirect connection through an intermediate medium. For ordinary technicians in this field, the specific meanings of the above terms can be understood according to the specific circumstances.

[0077] The present invention discloses a query optimization method based on a key truth path table and set merging, so as to solve the performance bottleneck problem caused by generating Cartesian product combinations and executing a large number of SQLs when a large-scale value space is used as a dynamic filling parameter of a condition placeholder in a multi-condition SQL query statement, and at the same time solve the problem that the traditional set query result cannot form a mapping result between the query hit record and the query parameter.

[0078] like Figure 1 As shown, the query optimization method based on key truth path table and set merging includes the following steps:

[0079] According to the value space, construct the parameter data and data dictionary required for SQL query conditions;

[0080] Parse SQL statements, generate and analyze SQL syntax trees, such as Figure 2 As shown; the SQL query statement targeted by the present invention is in the following format:

[0081] selectStatement→SELECT columnName(','columnName)*FROM tableNameWHERE combineCOND

[0082] Among them, the SQL query statement (selectStatement) contains only one SELECT keyword and involves only one database table (tableName), the query field involves one or more database table fields (columnName), and the WHERE clause contains a combination of conditions.

[0083] Execute a collection query SQL statement through JDBC, execute the analyzed SQL statement, specifically execute the analyzed collection query SQL statement through JDBC (Java Database Connectivity, Java database connection), and obtain the query hit result set S of all parameter combinations at one time h ;

[0084] Traverse the query hit result set S h Query the hit records and infer the query parameters;

[0085] According to the query number parameter combination corresponding to the inferred hit record, the data dictionary corresponding to each parameter in the query parameter combination is queried to obtain the data label combination of all parameter combinations, and the query data label combination and hit record mapping result are generated. The mapping result is converted, with the label combination as the key and the hit record query field value as the value value, the hit record field values ​​corresponding to the label combination are merged, and the query mapping result is encapsulated.

[0086] In this embodiment, the value space data format is a Map structure, the Key value is a data tag, the Value value is a specific data, and the data tag is a unique identifier of the data in the value space. The atomic condition (atomCOND) is defined as the finest granularity condition, that is, it cannot be split into sub-conditions. The combined condition (combineCOND) is composed of two sub-conditions (childCOND) through the logical operator logicOp, where the sub-condition can be an atomic condition or a combined condition, and the logical operators include logical AND and logical OR. The formal definition of atomic conditions and combined conditions is as follows:

[0087] atomCOND→columnName'=''? '|columnName op literal|literal op'? '|literal op literal

[0088] literal→STRING|NUMBER

[0089] op→'>'|'<'|'>='|'<='|'='|'! ='

[0090] combineCOND→childCOND logicOp childCOND

[0091] childCOND→atomCOND|combineCOND

[0092] logicOp→'AND'|'OR'

[0093] Among them, columnName represents the field name of the database table, literal represents a constant, STRING represents a string constant, and NUMBER represents a numeric constant.

[0094] There are four types of atomic conditions: ① Atomic conditions in the form of "columnName'=''?'" are called placeholder data field-related conditions, such as "column1=?"; ② Atomic conditions in the form of "columnName op literal" are called non-placeholder data field-related conditions, such as "column1=1"; ③ Atomic conditions in the form of "literal op'?'" are called placeholder constant-related conditions, such as "?=1"; ④ Atomic conditions in the form of "literal op literal" are called non-placeholder constant-related conditions, such as "1=1".

[0095] The conditions involving placeholders include ①③, and the conditions not involving placeholders include ②④. The atomic conditions of types ②③④ have operation operators (op) including greater than, less than, greater than or equal to, less than or equal to, equal to, and not equal to.

[0096] In a preferred embodiment of the present invention, the method for constructing the parameter data and data dictionary required for the SQL query condition according to the value space is:

[0097] The value of the data in the value space is used as the key of the data dictionary, and the label of the data is used as the value of the data dictionary. The data label corresponding to the data value is queried through the data dictionary. The data label in the value space is unique. When generating the dictionary, the labels corresponding to the data with the same value are merged, and the data dictionaries of the value spaces M1, M2, and M3 are obtained respectively:

[0098] D1:{'a0':['L 10 '],'a1':['L 11 '],…,'a9':['L 19 ']}

[0099] D2:{'b0':['L 20 '],'b1':['L 21 '],…,'b9':['L 29 ']}

[0100] D3:{'c0':['L 30 '],'c1':['L 31 '],…,'c9':['L 39 ']}

[0101] Where L 1i , L 2i , L 3i Respectively represent the labels corresponding to different values ​​in the value space M1, M2, and M3, a i , b i 、c i (i=1,2,…,9) represent different values ​​in different value spaces respectively.

[0102] In a preferred embodiment of the present invention, the method for parsing SQL statements and generating and analyzing SQL syntax trees is as follows:

[0103] Use the Antlr4 framework to perform syntax analysis on the original SQL statement to obtain the syntax tree of the SQL statement. Define specific syntax actions for different types of tree nodes in the syntax tree to implement syntax tree analysis, specifically:

[0104] Generate a key truth path table and perform in-order traversal on the syntax tree of the WHERE clause of the SQL query statement;

[0105] The conditions of the placeholder data fields in the SQL are reconstructed into set query conditions according to the parameter pool size corresponding to the placeholder, that is, the number of parameters, and the conditional operator, and the conditional fields corresponding to all atomic conditions are added to the query fields of the SQL, so that the query parameters can be deduced based on the query fields in the hit results. According to the size of the value space corresponding to the placeholder, the placeholder conditions in the SQL query statement are reconstructed into in set query conditions. The rules for reconstructing set queries are as follows:

[0106] The equal value matching condition involving placeholder data fields in the SQL query statement such as "columnName'=''?'" is reconstructed into a set query of "columnName IN(?,?,...)", where the size of the placeholder parameter list after IN is consistent with the data space size of its corresponding parameter.

[0107] The reconstructed SQL query statement is:

[0108] select column4, column1, column2, column3 from table1 where(column1 in(?,?,…,?)and column1 in(?,?,…,?))or column2 in(?,?,…,?)

[0109] The reconstructed list matching conditions contain 10 placeholders '?' in brackets, the condition fields are column1, column2, column3, and the query field is column4.

[0110] Save the atomic condition information of the WHERE clause in the original SQL statement query, including the atomic condition operation logic, the database table field name used in the atomic condition, and the operation value information, so as to facilitate the subsequent query parameter inference of the set query hit result based on the saved atomic condition information. Save the atomic condition information and query field information in the original SQL query statement: involving three equal value matching placeholder data field conditions, whose condition fields are column1, column2, column3, and the query field is column4.

[0111] In a preferred embodiment of the present invention, the key truth path is defined as the atomic conditions that need to be true at least for the WHERE clause in the SQL statement to be true, that is, when the atomic conditions covered by the key truth path in the WHERE clause are all true, the WHERE clause is true regardless of whether the remaining atomic conditions are true or not;

[0112] The key truth value path is represented by a 01 string, where each item corresponds to an atomic condition in the WHERE clause, 1 represents a key truth value, and 0 represents a non-key truth value. The key truth value path table K stores all the key truth value paths of the WHERE clause, and the non-key truth value path table NK stores all the non-key truth value paths of the WHERE clause. The steps to generate the key truth value path table are:

[0113] When the WHERE clause is an atomic condition, its key truth path table is {1}, and its non-key truth path table is {0}, that is, K atomCond ={1},NK atomCond ={0};

[0114] When the WHERE clause is a combined condition, its key truth path table is related to the type of the sub-condition logical operator; if its sub-condition logical operator is AND, its key truth path table is {Cartesian product combination of sub-condition key truth path tables};

[0115] If its sub-condition logical connector is OR, then one of the sub-conditions is a critical truth value and the other is a non-critical truth value. Its critical truth value path table is {the Cartesian product combination of the critical truth value path table of sub-condition 1 and the non-critical truth value path table of sub-condition 2}∪{the Cartesian product combination of the non-critical truth value path table of sub-condition 1 and the critical truth value path table of sub-condition 2};

[0116] When the WHERE clause is a combination condition, its non-key truth path table is the Cartesian product of {the non-key truth path table of sub-condition 1 and the non-key truth path table of sub-condition 2}, regardless of the type of the sub-condition logical operator;

[0117] When the WHERE clause is a combined condition, the key truth path table and the non-key truth path table are:

[0118]

[0119] NK combineCond =(NK childCond1 ×NK childCond2 ),

[0120] Among them, combineCond is composed of two sub-conditions combined by logical operators. The sub-conditions can be atomic conditions or combined conditions. The logical operators include logical AND and logical OR. ChildCond1 and childCond2 are sub-conditions of combineCond.

[0121] In a preferred embodiment of the present invention, the syntax tree of the WHERE clause of the SQL query statement is traversed in order, and the traversal process is as follows:

[0122] Traversing to condition C1, we get its key truth path set as {1} and its non-key truth path set as {0}, that is, K atomCond ={1},NK atomCond ={0};

[0123] Traverse to condition C2 and obtain its key truth path set as {1} and its non-key truth path set as {0};

[0124] Traversing to condition C4, because its sub-conditions C1 and C2 are connected by AND, the key truth path set of C4 is the Cartesian product combination of the key truth path sets of the sub-conditions, that is, {11}, and the non-key truth path set is the Cartesian product combination of the non-key path set of C1 and the non-key path set of C2, that is, {00};

[0125] Traverse to condition C3 and obtain its key truth path set as {1} and its non-key truth path set as {0};

[0126] Traversing to condition C5, because its sub-conditions C4 and C3 are connected by OR, the key truth path set of C5 is {Cartesian product combination of C4's key truth path set and C3's non-key truth path set}∪{Cartesian product combination of C4's non-key truth path set and C3's key truth path set}, that is, {110, 001};

[0127] Because condition C5 is the top-level condition of the WHERE clause in the SQL statement, the key truth path set of condition C5 is the key truth path table of the SQL query statement in the embodiment, path1 represents the key truth path "110", path2 represents the key truth path "001", and each digit of the key truth path corresponds to conditions C1, C2, and C3 from left to right, as shown in Table 1.

[0128] Table 1 Key truth path table of SQL query statements

[0129]

[0130] In a preferred embodiment of the present invention, each hit record obtained by the set query corresponds to one or more query condition parameter combinations, that is, for each hit record, there must be one or more query condition parameter combinations that make the logical condition expression of the WHERE clause true, that is, each hit record must correspond to one or more key truth value paths, so that when the atomic conditions covered by these key truth value paths are true, the record is hit. Traversing the query hit result set S h Query the hit records and infer the query parameters. The specific steps are as follows:

[0131] S1, determine whether each hit record is true for each condition in the WHERE clause, obtain their truth strings, and substitute the atomic condition field data in the hit record into the placeholder at the corresponding position according to the atomic condition information in the SQL query statement to determine the atomic condition truth value of the record. i The value corresponding to column(i=1,2,3) in (i=1,2,3) is Traverse the contents of row1 record. Because the atomic condition C1 corresponding to the field column1 is a reference-related placeholder condition, and because the hit result value a0 exists in the key value set of the data dictionary D1, that is, a0∈D1, it is recorded as 1. Similarly, because b1∈D2, it is recorded as 1. So it is recorded as 0. In summary, the truth value string recorded in row1 is 110. Similarly, the truth value string corresponding to row2 is 101, and the truth value string corresponding to row3 is 111.

[0132] The truth value judgment of atomic sub-conditions is divided into the following four cases;

[0133] S2, compare and match the generated truth string with the key truth path in the key truth path table to obtain the effective key truth path of each hit record;

[0134] For the truth string 110 of row1, first compare the key truth path path1. The key truth values ​​in path1 are C1 and C2, and in the truth string of row1, they are both true, so the match succeeds; then compare path2, the key truth value in path2 is C3, but it is false in the truth string of row1, so the match fails. In summary, the effective key truth path of row1 is path1. Similarly, the effective key truth path of row2 is path2, and the effective key truth paths of row3 are path1 and path2.

[0135] S3, traverses all effective key truth value paths of the query hit record, reversely infers the query parameter data, and obtains the query parameters of each hit record.

[0136] For the hit record row1, its effective key truth value path is path1, and the query parameter combination corresponding to row1 for and the Cartesian product of the key set in D3, where So there is

[0137]

[0138] The query parameter combination corresponding to row2 is the key set in D1, the key set in D2 and The Cartesian product of So there is

[0139]

[0140] The query parameter combination P corresponding to row3 row3 for The Cartesian product of the key set in D3 and the key set in D1, the key set in D2 and The union of the Cartesian product combinations of

[0141]

[0142] Query hit result set S h As shown in Table 2:

[0143] Table 2 Collection query hit result set S of SQL query statement h

[0144]

[0145] Preferably, according to The parameter combination value in the query corresponds to the data dictionary D1, D2 and D3. For example, for the parameter combination a0b0c3, a0 queries the data dictionary D1 to obtain its corresponding data label L 10 , b0 queries the data dictionary D2 to obtain its corresponding data label L 20 , c3 queries the data dictionary D3 to obtain its corresponding data label L 33 , concatenate each data label with '-' to get the label combination corresponding to the parameter combination as L 10 -L 20 -L 33 Finally, we get the label combinations corresponding to all query hit records:

[0146]

[0147] The mapping between the hit record value and the label combination is converted, with the label combination as the key and the hit record value as the value, the hit record values ​​corresponding to the label combination are merged, and the final query mapping result of the SQL query statement of the embodiment is generated, as shown below:

[0148] {

[0149] L 10 -L 20 -L 32 :{d2},

[0150] L 10 -L 20 -L 33<h2 style=";text-align:left;direction:ltr">:{d3},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0151] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 10 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 21 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 30 <h2 style=";text-align:left;direction:ltr"> :{d1},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0152] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 10 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 21 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 31 <h2 style=";text-align:left;direction:ltr"> :{d1},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0153] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 10 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 21 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 32 <h2 style=";text-align:left;direction:ltr"> :{d1,d2},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0154] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 10 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 21 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 33 <h2 style=";text-align:left;direction:ltr"> :{d1,d3},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0155] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 10 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 21 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 34 <h2 style=";text-align:left;direction:ltr"> :{d1},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0156] <h2 style=";text-align:left;direction:ltr"> …<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0157] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 10 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 21 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 39 <h2 style=";text-align:left;direction:ltr"> :{d1},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0158] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 10 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 22 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 32 <h2 style=";text-align:left;direction:ltr"> :{d2},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0159] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 10 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 22 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 33 <h2 style=";text-align:left;direction:ltr"> :{d3},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0160] <h2 style=";text-align:left;direction:ltr"> …<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0161] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 10 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 29 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 32 <h2 style=";text-align:left;direction:ltr"> :{d2},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0162] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 10 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 29 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 33 <h2 style=";text-align:left;direction:ltr"> :{d3},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0163] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 20 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 32 <h2 style=";text-align:left;direction:ltr"> :{d2},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0164] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 20 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 33 <h2 style=";text-align:left;direction:ltr"> :{d3},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0165] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 21 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 32 <h2 style=";text-align:left;direction:ltr"> :{d2},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0166] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 21 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 33 <h2 style=";text-align:left;direction:ltr"> :{d3},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0167] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 22 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 30 <h2 style=";text-align:left;direction:ltr"> :{d3},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0168] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 22 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 31 <h2 style=";text-align:left;direction:ltr"> :{d3},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0169] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 22 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 32 <h2 style=";text-align:left;direction:ltr"> :{d2,d3},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0170] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 22 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 33 <h2 style=";text-align:left;direction:ltr"> :{d3},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0171] <h2 style=";text-align:left;direction:ltr"> …<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0172] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 22 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 39 <h2 style=";text-align:left;direction:ltr"> :{d3},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0173] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 23 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 32 <h2 style=";text-align:left;direction:ltr"> :{d2},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0174] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 23 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 33 <h2 style=";text-align:left;direction:ltr"> :{d3},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0175] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 24 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 32 <h2 style=";text-align:left;direction:ltr"> :{d2},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0176] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 24 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 33 <h2 style=";text-align:left;direction:ltr"> :{d3},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0177] <h2 style=";text-align:left;direction:ltr"> …<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0178] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 29 <h2 style=";text-align:left;direction:ltr"> -L<h2 style=";text-align:left;direction:ltr"> 32 <h2 style=";text-align:left;direction:ltr"> :{d2},<h2 style=";text-align:left;direction:ltr"> <h2 style=";text-align:left;direction:ltr">

[0179] <h2 style=";text-align:left;direction:ltr"> L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> -L29 -L 33 :{d3},

[0180] L 12 -L 20 -L 32 :{d2},

[0181] L 12 -L 20 -L 33 :{d3},

[0182] …

[0183] L 12 -L 29 -L 32 :{d2},

[0184] L 12 -L 29 -L 33 :{d3},

[0185] …

[0186] L 19 -L 20 -L 32 :{d2},

[0187] L 19 -L 20 -L 33 :{d3},

[0188] …

[0189] L 19 -L 29 -L 32 :{d2},

[0190] L 19 -L 29 -L 33 :{d3}

[0191] }

[0192] In a 2-core 16G Linux environment, the traditional algorithm and the optimization algorithm of the present invention are used to execute the SQL query statement of the embodiment. The experiment is repeated 5 times, and the execution time of the two algorithms is shown in Table 3. The experimental results show that the optimization algorithm of the present invention reduces the algorithm execution time by 97.8% compared with the traditional algorithm, and the optimization algorithm can effectively improve the query processing speed.

[0193] Table 3 Comparison of execution time of traditional algorithm and optimized algorithm

[0194]

[0195] In a preferred embodiment of the present invention, the step of determining the true value of the atomic condition of the hit record in step S1 is:

[0196] When the atomic condition is a condition involving a placeholder data field, optimization is performed for the placeholder-related conditions with equal value matching. It is determined whether the condition field data in the corresponding hit record exists in the data dictionary corresponding to the condition. If it exists, it means that the condition may be true when the record is hit, which is recorded as 1; if it does not exist, it means that the condition must be false when the record is hit, which is recorded as 0;

[0197] When the atomic condition is a condition that does not involve placeholder data fields, after the logical semantics of the conditional expression is translated, the corresponding conditional field data in the hit record is substituted into the conditional expression operation. If it is true, it is recorded as 1; if it is false, it is recorded as 0;

[0198] When the atomic condition is a placeholder constant condition, the logical semantics of the conditional expression is translated and the data in the data dictionary corresponding to the condition is substituted into the conditional expression operation. If there is data in the dictionary that satisfies the logic of the expression and makes the condition true, it is recorded as 1; if not, it is recorded as 0.

[0199] When the atomic condition is a non-placeholder constant condition, the logical semantics of the conditional expression is translated and logical operations are performed. If it is true, it is recorded as 1; if it is false, it is recorded as 0.

[0200] In a preferred embodiment of the present invention, the method for comparing and matching the generated truth value string with the key truth value path of the key truth value path table in step S2 is:

[0201] If there is an atomic condition in the truth string, the corresponding position of the atomic condition in the key truth path is 1, and the truth value of the atomic condition is 0, that is, the atomic condition is the key truth value in the key truth path, but the truth value of the atomic condition is false, then the truth string does not match the key path;

[0202] If all key truth values ​​corresponding to the key truth path in the truth string are 1, the truth string matches the key truth path, and the key truth path is the effective key truth path of the hit record.

[0203] In a preferred solution of the present invention, for each effective key truth value path in step S3, the reverse deduction method is as follows:

[0204] S31, when the placeholder-related atomic condition is a key condition, the parameter of the condition takes the hit result data;

[0205] S32, when the atomic condition involving the placeholder is a non-key condition, the parameter of the condition takes the entire data of the corresponding data space, because it is not the key truth value that causes the logical condition of the WHERE clause to be true;

[0206] S33, performing Cartesian product combination on each parameter set involved in the inverse of the placeholder atomic condition, and the combination result is all query parameter combinations corresponding to the records under the key truth path;

[0207] S34, repeat steps S31-S33 for all matched key truth value paths to obtain all query parameter combinations corresponding to the hit record.

[0208] The present invention also provides a query optimization system based on a key truth path table and set merging, comprising a processing unit, and the processing unit executes the method of the present invention to perform SQL optimization query.

[0209] In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "examples", "specific examples", or "some examples" means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representation of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner.

[0210] Although the embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the claims and their equivalents.

Claims

1. A query optimization method based on key truth path table and set merging, characterized in that: The steps include: According to the value space, construct the parameter data and data dictionary required for SQL query conditions; Parse SQL statements, generate and analyze SQL syntax trees; Execute the analyzed SQL statement to obtain the query hit result set S for all parameter combinations h ; Traverse the query hit result set S h Query the hit records and infer the query parameters; According to the query number parameter combination corresponding to the inferred hit record, the data dictionary corresponding to each parameter in the query parameter combination is queried to obtain the data label combination of all parameter combinations, and the query data label combination and hit record mapping result are generated. The mapping result is converted, with the label combination as the key and the hit record query field value as the value, the hit record field values ​​corresponding to the label combination are merged, and the query mapping result is encapsulated.

2. The query optimization method based on key truth path table and set merging as claimed in claim 1, characterized in that: According to the value space, the method of constructing the parameter data and data dictionary required for SQL query conditions is: The value of the data in the value space is used as the key of the data dictionary, and the label of the data is used as the value of the data dictionary. The data label corresponding to the data value is queried through the data dictionary. The data label in the value space is unique. When generating the dictionary, the labels corresponding to the data with the same value are merged, and the data dictionaries of the value spaces M1, M2, and M3 are obtained respectively: <h2 style=";text-align:left;direction:ltr">D1:{'a0':['L<h2 style=";text-align:left;direction:ltr"> 10 <h2 style=";text-align:left;direction:ltr"> '],'a1':['L<h2 style=";text-align:left;direction:ltr"> 11 <h2 style=";text-align:left;direction:ltr"> '],…,'a9':['L<h2 style=";text-align:left;direction:ltr"> 19 <h2 style=";text-align:left;direction:ltr"> ']} <h2 style=";text-align:left;direction:ltr">D2:{'b0':['L<h2 style=";text-align:left;direction:ltr"> 20 <h2 style=";text-align:left;direction:ltr"> '],'b1':['L<h2 style=";text-align:left;direction:ltr"> 21 <h2 style=";text-align:left;direction:ltr"> '],…,'b9':['L<h2 style=";text-align:left;direction:ltr"> 29 <h2 style=";text-align:left;direction:ltr"> ']} D3:{'c0':['L 30 '],'c1':['L 31 '],…,'c9':['L 39 ']} Among them, L 1i , L 2i , L 3i Respectively represent the labels corresponding to different values ​​in the value space M1, M2, and M3, a i , b i 、c i Respectively represent different values ​​in different value spaces, i = 1, 2,…, 9.

3. The query optimization method based on key truth path table and set merging as claimed in claim 1, characterized in that: The method to parse SQL statements, generate and analyze SQL syntax trees is as follows: Use the Antl r4 framework to perform syntax analysis on the original SQL statement to obtain the syntax tree of the SQL statement. Define specific syntax actions for different types of tree nodes in the syntax tree to implement syntax tree analysis, specifically: Generate a key truth path table and perform in-order traversal on the syntax tree of the WHERE clause of the SQL query statement; According to the size of the value space corresponding to the placeholder, the placeholder-related conditions in the SQL query statement are reconstructed into in set query conditions. The reconstructed SQL query statement is: select column4, column1, column2, column3 from table1 where(column1in(?,?,…,?)and column1 in(?,?,…,?))or column2 in(?,?,…,?) The reconstructed list matching conditions contain 10 placeholders '?' in brackets, the condition fields are column1, column2, column3, and the query field is column4. Saves the atomic condition information of the WHERE clause in the original SQL statement query, including the atomic condition operation logic, the database table field name used in the atomic condition, and the operation value information.

4. The query optimization method based on key truth path table and set merging as claimed in claim 3, characterized in that: The key truth path is defined as the atomic conditions that must be true at least for the WHERE clause in the SQL statement to be true. That is, when all the atomic conditions covered by the key truth path in the WHERE clause are true, the WHERE clause is true regardless of whether the remaining atomic conditions are true or not. The key truth value path is represented by a 01 string, where each item corresponds to an atomic condition in the WHERE clause, 1 represents a key truth value, and 0 represents a non-key truth value. The key truth value path table K stores all the key truth value paths of the WHERE clause, and the non-key truth value path table NK stores all the non-key truth value paths of the WHERE clause. The steps to generate the key truth value path table are: When the WHERE clause is an atomic condition, its key truth path table is {1}, and its non-key truth path table is {0}, that is, K atomCond ={1},NK atomCond ={0}; When the WHERE clause is a combined condition, its key truth path table is related to the type of the sub-condition logical operator; if its sub-condition logical operator is AND, its key truth path table is {Cartesian product combination of sub-condition key truth path tables}; If its sub-condition logical connector is OR, then one of the sub-conditions is a critical truth value and the other is a non-critical truth value. Its critical truth value path table is {the Cartesian product combination of the critical truth value path table of sub-condition 1 and the non-critical truth value path table of sub-condition 2}∪{the Cartesian product combination of the non-critical truth value path table of sub-condition 1 and the critical truth value path table of sub-condition 2}; When the WHERE clause is a combination condition, its non-key truth path table is the Cartesian product of {the non-key truth path table of sub-condition 1 and the non-key truth path table of sub-condition 2}, regardless of the type of the sub-condition logical operator; When the WHERE clause is a combined condition, the key truth path table and the non-key truth path table are: NK combineCond =(NK childCond1 ×NK childCond2 ), Among them, combineCond is composed of two sub-conditions combined by logical operators. The sub-conditions can be atomic conditions or combined conditions. The logical operators include logical AND and logical OR. ChildCond1 and childCond2 are sub-conditions of combineCond.

5. The query optimization method based on key truth path table and set merging as claimed in claim 4, characterized in that: Perform an in-order traversal of the syntax tree of the WHERE clause of the SQL query statement. The traversal process is: Traversing to condition C1, we get its key truth path set as {1} and its non-key truth path set as {0}, that is, K atomCond ={1},NK atomCond ={0}; Traverse to condition C2 and obtain its key truth path set as {1} and its non-key truth path set as {0}; Traversing to condition C4, because its sub-conditions C1 and C2 are connected by AND, the key truth path set of C4 is the Cartesian product combination of the key truth path sets of the sub-conditions, that is, {11}, and the non-key truth path set is the Cartesian product combination of the non-key path set of C1 and the non-key path set of C2, that is, {00}; Traverse to condition C3 and obtain its key truth path set as {1} and its non-key truth path set as {0}; Traversing to condition C5, because its sub-conditions C4 and C3 are connected by OR, the key truth path set of C5 is {Cartesian product combination of C4's key truth path set and C3's non-key truth path set}∪{Cartesian product combination of C4's non-key truth path set and C3's key truth path set}, that is, {110, 001}; Because condition C5 is the top-level condition of the WHERE clause in the SQL statement, the key truth path set of condition C5 is the key truth path table of the SQL query statement in the embodiment, path1 represents the key truth path "110", path2 represents the key truth path "001", where each digit of the key truth path corresponds to conditions C1, C2, and C3 from left to right.

6. The query optimization method based on key truth path table and set merging as claimed in claim 1, characterized in that: Traverse the query hit result set S h Query the hit records and infer the query parameters. The specific steps are as follows: S1, determine whether each condition in the WHERE clause is true for each hit record, obtain their truth value strings, substitute the atomic condition field data in the hit record into the placeholder at the corresponding position according to the atomic condition information in the SQL query statement, and determine the atomic condition truth value of the record; S2, compare and match the generated truth string with the key truth path in the key truth path table to obtain the effective key truth path of each hit record; S3, traverses all effective key truth value paths of the query hit record, reversely infers the query parameter data, and obtains the query parameters of each hit record.

7. The query optimization method based on key truth path table and set merging as claimed in claim 6, characterized in that: The steps of determining the true value of the atomic condition of the hit record in step S1 are: When the atomic condition is a condition involving a placeholder data field, optimization is performed for the placeholder-related conditions with equal value matching. It is determined whether the condition field data in the corresponding hit record exists in the data dictionary corresponding to the condition. If it exists, it means that the condition may be true when the record is hit, which is recorded as 1; if it does not exist, it means that the condition must be false when the record is hit, which is recorded as 0; When the atomic condition is a condition that does not involve placeholder data fields, after the logical semantics of the conditional expression is translated, the corresponding conditional field data in the hit record is substituted into the conditional expression operation. If it is true, it is recorded as 1; If it is false, it is recorded as 0; When the atomic condition is a placeholder constant condition, the logical semantics of the conditional expression is translated and the data in the data dictionary corresponding to the condition is substituted into the conditional expression operation. If there is data in the dictionary that satisfies the logic of the expression and makes the condition true, it is recorded as 1; If it does not exist, it is recorded as 0; When the atomic condition is a non-placeholder constant condition, the logical semantics of the conditional expression is translated and logical operations are performed. If it is true, it is recorded as 1; if it is false, it is recorded as 0.

8. The query optimization method based on key truth path table and set merging as claimed in claim 6, characterized in that: The method for comparing and matching the generated truth value string with the key truth value path in the key truth value path table in step S2 is: If there is an atomic condition in the truth string, the corresponding position of the atomic condition in the key truth path is 1, and the truth value of the atomic condition is 0, that is, the atomic condition is the key truth value in the key truth path, but the truth value of the atomic condition is false, then the truth string does not match the key path; If all key truth values ​​corresponding to the key truth path in the truth string are 1, the truth string matches the key truth path, and the key truth path is the effective key truth path of the hit record.

9. The query optimization method based on key truth path table and set merging as claimed in claim 6, characterized in that: In step S3, for each effective key truth value path, the reverse deduction method is as follows: S31, when the placeholder-related atomic condition is a key condition, the parameter of the condition takes the hit result data; S32, when the placeholder-related atomic condition is a non-critical condition, the parameter of the condition is the entire data of the corresponding data space; S33, performing Cartesian product combination on each parameter set involved in the inverse of the placeholder atomic condition, and the combination result is all query parameter combinations corresponding to the records under the key truth path; S34, repeat steps S31-S33 for all matched key truth value paths to obtain all query parameter combinations corresponding to the hit record.

10. A query optimization system based on key truth path table and set merging, characterized in that: It comprises a processing unit, which executes the method according to any one of claims 1 to 9 to perform SQL optimized query.

Images