WO2018228322A1 - 一种非相关子查询的优化方法、装置和存储介质 - Google Patents

一种非相关子查询的优化方法、装置和存储介质 Download PDF

Info

Publication number
WO2018228322A1
WO2018228322A1 PCT/CN2018/090610 CN2018090610W WO2018228322A1 WO 2018228322 A1 WO2018228322 A1 WO 2018228322A1 CN 2018090610 W CN2018090610 W CN 2018090610W WO 2018228322 A1 WO2018228322 A1 WO 2018228322A1
Authority
WO
WIPO (PCT)
Prior art keywords
rows
target clause
query statement
correlated
execution result
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Ceased
Application number
PCT/CN2018/090610
Other languages
English (en)
French (fr)
Inventor
李海翔
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Tencent Technology Shenzhen Co Ltd
Original Assignee
Tencent Technology Shenzhen Co Ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Tencent Technology Shenzhen Co Ltd filed Critical Tencent Technology Shenzhen Co Ltd
Publication of WO2018228322A1 publication Critical patent/WO2018228322A1/zh
Priority to US16/521,895 priority Critical patent/US11269878B2/en
Anticipated expiration legal-status Critical
Ceased legal-status Critical Current

Links

Images

Classifications

    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2455—Query execution
    • G06F16/24553—Query execution of query operations
    • G06F16/24558—Binary matching operations
    • G06F16/2456—Join operations
    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2453—Query optimisation
    • G06F16/24534—Query rewriting; Transformation
    • G06F16/24535—Query rewriting; Transformation of sub-queries or views
    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2453—Query optimisation
    • G06F16/24534—Query rewriting; Transformation
    • G06F16/24537—Query rewriting; Transformation of operators
    • G—PHYSICS
    • G06—COMPUTING OR CALCULATING; COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2453—Query optimisation
    • G06F16/24534—Query rewriting; Transformation
    • G06F16/24549—Run-time optimisation

Definitions

  • the present application relates to the field of computer technologies, and in particular, to a method, an apparatus, and a storage medium for optimizing a non-correlated sub-query.
  • Data processing systems such as database systems and big data processing systems, generally support the SQL (Structured Query Language) function as the main implementation goal, so as to provide users with convenient data operations.
  • SQL Structured Query Language
  • the SQL engine needs to perform lexical parsing, syntax parsing, semantic analysis, logic optimization and physical optimization on the SQL statement, and then hand the obtained execution plan to the actuator for execution. This process is divided into three stages: Analysis phase, optimization phase, and execution phase.
  • subqueries occurs in the logic optimization phase, which is the optimization subphase in the logic optimization phase (commonly called query rewriting).
  • logic optimization phase which is the optimization subphase in the logic optimization phase (commonly called query rewriting).
  • subqueries can be divided into two types: related subqueries and non-correlated subqueries.
  • the embodiment of the present application provides a method, an apparatus, and a storage medium for optimizing a non-correlated subquery, which can improve the execution efficiency of a database query statement having a non-correlated subquery.
  • the embodiment of the present application provides an optimization method for a non-correlated sub-query, which is applied to a computing device, and includes:
  • the embodiment of the present application further provides an apparatus for optimizing a non-correlated subquery, including:
  • processor coupled to the processor, the memory having machine readable instructions executable by the processor, the processor executing the machine readable instructions to:
  • Embodiments of the present application provide a non-transitory computer readable storage medium in which machine readable instructions are stored, the machine readable instructions being executable by a processor to perform the following operations:
  • 1a is a schematic diagram of an implementation environment of an optimization method for a non-correlated subquery according to an embodiment of the present application
  • FIG. 1b is a schematic flowchart of a method for optimizing a non-correlated subquery according to an embodiment of the present application
  • FIG. 1c is a schematic diagram of a semi-join optimization manner provided by an embodiment of the present application.
  • FIG. 1d is a schematic diagram of a physical and chemical optimization manner provided by an embodiment of the present application.
  • 2a is another schematic flowchart of a method for optimizing a non-correlated subquery according to an embodiment of the present application
  • 2b is a schematic flowchart of a SQL statement processing provided by an embodiment of the present application.
  • FIG. 3 is a schematic diagram of a first structure of an apparatus for optimizing a non-correlated sub-query according to an embodiment of the present application
  • FIG. 3b is a second schematic structural diagram of an apparatus for optimizing a non-correlated subquery according to an embodiment of the present application
  • FIG. 3 is a third structural diagram of an apparatus for optimizing a non-correlated sub-query according to an embodiment of the present application
  • FIG. 4 is a schematic structural diagram of a server provided by an embodiment of the present application.
  • the conventional optimization methods of non-correlated sub-queries mainly include semi-join optimization and "materialization" optimization.
  • semi-join optimization which belongs to sub-query pull-up technology, is a kind of semi-joining (seemi join) semantic elimination sub-query.
  • the optimization form belongs to the logical optimization sub-phase; the materialized optimization identifies the non-correlated sub-query clause as “materializable” and executes it once during the execution phase, and then buffers the result for subsequent use.
  • the above-mentioned non-correlated subquery optimization method is used to optimize the database query statement (such as the SQL statement) with the non-correlated subquery.
  • the database query statement such as the SQL statement
  • the table specified by the subquery needs to be accessed multiple times. (such as semi-join optimization) or multiple access to the temporary table corresponding to the sub-query specified table (such as using materialized optimization)
  • the time cost is relatively large, but also consume a lot of computing resources, reducing the database query statement with non-correlated sub-query
  • the performance of the implementation, especially in the face of large data queries, is more obvious.
  • the embodiment of the present application provides an optimization method and apparatus for a non-correlated sub-query, which can improve the execution efficiency of a database query statement with a non-correlated sub-query. The details will be described separately below.
  • FIG. 1 is a schematic diagram of an implementation environment of a method for optimizing a non-correlated subquery according to an embodiment of the present application.
  • the computing device 10 is integrated with the non-correlated sub-query optimization device 11 provided by any embodiment of the present application, and is used to implement the optimization method of the non-correlated sub-query provided by any embodiment of the present application.
  • the computing device 10 and the user terminal 20 are connected by a network 30, which may be a wired network or a wireless network.
  • This embodiment will be described from the perspective of an optimization device for a non-correlated sub-query, which may be integrated in a computing device, such as a database server.
  • An embodiment of the present application provides an optimization method for a non-correlated subquery, including: determining whether a target clause in a database query statement has a non-correlated subquery statement, and if so, obtaining an estimation of an execution result set corresponding to the target clause. The number of rows, when the estimated number of rows is less than the preset threshold, executing the non-correlated sub-query statement, obtaining an execution result set, and rewriting the target clause according to the execution result set to eliminate the non-correlated sub-query statement.
  • the specific process of the optimization method of the non-correlated subquery can be as follows:
  • Step 101 Determine whether there is a non-correlated sub-query statement in the target clause in the database query statement. If yes, execute step 102. If not, end the optimization process of the non-correlated sub-query.
  • whether the target clause in the database query statement has a non-correlated sub-query statement may be determined.
  • the database query statement can be implemented in multiple languages, for example, can be implemented by SQL (Structured Query Language) or other types of languages.
  • SQL can include languages such as DQL (Data Query Language) and DML (Data Manipulation Language).
  • DQL Data Query Language
  • DML Data Manipulation Language
  • the database query statement is the SQL statement.
  • DQL or DML the database query statement is the DQL statement and the DML statement.
  • the subquery is a program module of a common computer language such as a lower layer of a nested query in a database query language.
  • a query is a sub-portion of another query, it can be called a sub-query; the sub-query is a query nested within a query, so it can also be called a nested query.
  • the sub-query can be divided into two types: a related sub-query and a non-correlated sub-query.
  • the correlated subquery is a subquery that performs some property values that depend on the outer parent query.
  • the related subquery depends on the parameters of the parent query. When the parameters of the parent query change, the related subquery needs to be re-executed according to the new parameter value (the query optimizer has certain significance for optimizing the related subquery).
  • a non-correlated subquery is a subquery that does not depend on any attribute value of the outer parent query, so that the subquery has independence and can be solved by itself to form a subquery plan that precedes the outer query.
  • the non-correlated sub-query statement is generally nested in the target clause of the database query statement, and the target clause may be a clause in which a sub-query may appear, such as a conditional clause.
  • the target clause can include:
  • Target column position If the subquery is in the target column, it can only be a scalar subquery. Otherwise, the database may return a prompt like "Error: subquery must return only one field".
  • conditional clauses such as (WHERE clause):
  • Subqueries appearing in the WHERE clause are part of a conditional expression, and expressions can be decomposed into operators and operands; depending on the data type involved in the operation The operators are not the same.
  • This has certain requirements for subqueries (such as the INT type equivalent operation, and the subquery must be a scalar quantum. Inquire).
  • the subquery appears in the format of the WHERE clause, and also uses some operations specified by the predicate, such as IN, BETWEEN, EXISTS, and so on.
  • JOIN/ON clause can be split into two parts. One is that the JOIN block is similar to the FROM clause, and the other is that the ON clause block is similar to the WHERE clause. Both parts can appear subqueries. . Subqueries are handled in the same way as the FROM clause and the WHERE clause.
  • GROUPBY GROUPBY clause: The target column must be associated with GROUPBY.
  • the subquery can be written at the GROUPBY location, but the subquery has no practical meaning for use at GROUPBY.
  • ORDER BY clause The subquery can be written at the ORDERBY position. But the ORDERBY operation is applied to the entire SQL statement, and the subquery has no practical meaning at ORDERBY.
  • Step 102 Obtain an estimated number of rows of execution result sets corresponding to the target clause.
  • the execution result set corresponding to the target clause is a result set obtained by executing the target clause.
  • the estimated number of rows is the estimated cardinality of the execution plan corresponding to the target clause, and may be the estimated number of rows returned from the data table result set or index.
  • the estimated number of rows of the target clause such as the execution result set corresponding to the conditional clause, can be obtained based on the historical estimated row number and the selection rate of the table. That is, the step "acquiring the estimated number of rows of the execution result set corresponding to the target clause" may include:
  • the estimated number of rows of the table to be queried corresponding to the target clause is used as the estimated number of rows of the execution result set corresponding to the target clause.
  • the historical estimated number of rows of the to-be-queried table is the estimated number of rows of the to-be-queried table before the moment.
  • a data statistics module is set to statistically record the estimated number of rows of the to-be-queried table to form historical statistics. data.
  • the estimated number of rows of the to-be-queried table corresponding to the target clause may be obtained from the historical statistical data.
  • this embodiment can calculate the estimated number of rows of the execution result set corresponding to the WHERE condition clause, which is the same as the conventional process of calculating the actual number of rows. For example, if the non-correlated subquery clause does not have a WHERE condition, the selection rate is 1, which is equivalent to w_rows equal to rows.
  • Step 103 When the estimated number of rows is less than a preset threshold, executing the non-correlated sub-query statement.
  • the optimization method provided by the embodiment of the present application may be omitted, and the conventional optimization mode may be adopted.
  • the preset threshold (denoted as Kmax) is a sentinel value, which is a positive integer, indicating the size of the result set of the non-correlated subquery supported by the optimization method provided by the embodiment of the present application, and the preset threshold is a system. /session parameters, which can be set according to actual needs. In addition, the preset threshold may also be dynamically adjusted. For example, the default value of the preset threshold is 2000, the maximum value is 20000 (user definable), and the like.
  • Step 104 Rewrite the target clause according to the execution result set of the non-correlated sub-query statement to eliminate the non-correlated sub-query statement.
  • the target clause may be rewritten according to the execution result set of the non-correlated sub-query statement.
  • the manner in which the target clause is rewritten according to the execution result set in this embodiment includes: directly replacing the non-correlated sub-query statement in the expression of the target clause with the execution result to eliminate the non-correlated sub-query.
  • the expression of the target clause such as the conditional clause
  • the expression of the target clause may be further simplified, and the execution efficiency of the database query statement is improved.
  • the replaced expression can be simplified according to the operator in the target clause. That is, the step "rewriting the target clause according to the execution result set" may include:
  • the corresponding deformation optimization is performed on the replaced expression.
  • the WHERE clause can be transformed into:
  • the step "execute the non-correlated sub-query statement" may include:
  • the step “rewriting the target clause according to the execution result set of the non-correlated sub-query statement” may include: when the non-correlated sub-query statement is executed, according to the execution result set of the non-correlated sub-query statement The target clause is rewritten.
  • the preset threshold is the foregoing sentinel value Kmax.
  • the obtained tuple may be counted (that is, the number of rows of the current execution result set is obtained), if the number of tuples obtained is obtained. If the sentinel value Kmax is exceeded, the execution of the subquery is stopped, and the optimization process of the non-correlated subquery is ended.
  • the optimization method provided by the embodiment may further include: performing corresponding logic optimization on the rewritten target clause.
  • the expression of the rewritten target clause can be optimized according to the constant equation or inequality contained in the rewritten target clause. That is, the step "corresponding to the logical optimization of the rewritten target clause" may include:
  • the expression of the rewritten target clause is optimized according to the constant equation or inequality contained in the rewritten target clause.
  • the WHERE clause optimized by the non-correlated subquery contains a constant equation
  • the WHERE clause can be further logically optimized according to the constant equation, and the optimized SQL statement is:
  • connection operation of the tables t1 and t3 becomes an index scan on the two tables first, and then each tuple participates in the connection operation, so the time complexity of the connection becomes O(1), which is greatly Improve the efficiency of the connection and the efficiency of statement execution.
  • the optimization scheme of the non-correlated subquery may pre-execute the non-correlated subquery statement before the execution phase of the statement, such as in the logic optimization phase, and the non-correlated subquery statement is located according to the execution result.
  • the target clause is rewritten to eliminate non-correlated subqueries, so that no query and access related to non-correlated subquery statements are needed in the subsequent statement execution phase, which saves computing resources, saves execution time of database query statements, and improves Execution efficiency of database query statements with non-correlated subqueries.
  • the semi-join optimization method When the semi-join optimization method is used to optimize the non-correlated subquery of the database query statement, from the statement execution result, although the subquery is eliminated, the connection operation exists, and the subquery is specified during the execution of the statement by the actuator.
  • the table still needs to be accessed multiple times, so that the execution efficiency of the database query statement is not high, and the connection time is more expensive. If it is a large data query, multiple accesses to the table can cause significant performance loss.
  • the connection operation exists.
  • the id value is 1 means that the same level, that is, the subquery does not exist, and the display result of the "SHOW WARNINGS" command indicates that the non-correlated subquery is optimized for semi join.
  • the table t2 specified for the subquery still needs to be accessed multiple times, so that the execution efficiency of the SQL statement is not high, and the connection time is O(n*m). If it is a large data query, multiple access to the table t2 will consume a lot of computing resources, resulting in a large performance loss.
  • the optimization manner of the non-correlated sub-query may pre-execute the non-correlated sub-query statement, and rewrite the target clause in which the non-correlated sub-query statement is located according to the execution result, so as to eliminate the non-correlated sub-query.
  • it can avoid multiple accesses to the table specified by the sub-query during the statement execution phase, saving computing resources and statement execution time (specifically saving the time for the sub-query and the parent query to do the connection operation), and improving the statement. effectiveness.
  • the materialized optimization method When the materialized optimization method is used to optimize the non-correlated subquery of the database query statement, from the statement execution result, the subquery is not eliminated, but is materialized into a temporary table, and the connection operation exists during the execution of the actuator.
  • the materialized temporary table of the table specified by the subquery it still needs multiple accesses, and the connection time is expensive, so the execution efficiency of the statement is not high. If it is a large data query, the temporary table needs multiple accesses and consumes a lot of computing resources, resulting in a large performance loss.
  • FIG. 1d taking MySQL as an example to illustrate the materialization optimization mode, referring to FIG. 1d, from the execution result in FIG.
  • the query is not eliminated, but is materialized into a temporary table (MATERIALIZED). Then, the materialized temporary table ⁇ subquery2> and the table t1 do a semi join operation. During the execution of the executor, the materialized temporary table of the table t2 specified by the subquery still needs multiple accesses, and the connection time is O(n*m), so that the execution efficiency of the SQL statement is not high. If it is a large data query, the temporary table needs multiple accesses, which will cause a large performance loss.
  • the optimization manner of the non-correlated sub-query may pre-execute the non-correlated sub-query statement, and rewrite the target clause in which the non-correlated sub-query statement is located according to the execution result, so as to eliminate the non-correlated sub-query.
  • it can avoid accessing the temporary table after the sub-query specified table materialization in the statement execution stage, saving the computing resources and the statement execution time (specifically, saving the time between the sub-query and the parent query to perform the connection operation). Improve the efficiency of statement execution.
  • the optimization method of the non-correlated sub-query is integrated in a computing device, such as a server, and the database query statement is an SQL statement as an example to introduce an optimization method of the non-correlated sub-query provided by the embodiment of the present application.
  • an optimization method for non-correlated subqueries can be as follows:
  • Step 201 The server determines, in the logic optimization stage of the SQL statement, whether the target clause in the SQL statement has a non-correlated sub-query statement; if yes, executing step 202, and if not, ending the optimization process of the non-correlated sub-query.
  • the target clause may be a clause in which a subquery may appear, such as a WHERE clause, a FROM clause, a JOIN/ON clause, and the like.
  • Step 202 The server obtains the historical estimated row rows of the to-be-queried table corresponding to the target clause from the statistical data.
  • Step 203 The server obtains the estimated number of rows w_rows of the to-be-queried table corresponding to the target clause according to the historical estimated row number rows and the selection rate s.
  • the estimated number of rows of the execution result set corresponding to the WHERE condition clause can be calculated, which is the same as the conventional process of calculating the actual number of rows. If the non-correlated subquery clause does not have a WHERE condition, the selection rate is 1, which is equivalent to w_rows equal to rows.
  • Step 204 The server determines whether the estimated number of rows is less than a preset threshold Kmax. If yes, step 205 is performed, and if not, the optimization process of the non-correlated subquery is ended.
  • the preset threshold Kmax represents the size of the result set of the non-correlated subquery supported by the optimization method of the embodiment of the present application.
  • the Kmax is a system/session parameter, which can be set according to actual requirements.
  • the Kmax is further Can be adjusted dynamically, for example, the default is 2000, the maximum is 20000 (user definable), and so on.
  • Step 205 The server executes the non-correlated sub-query statement, and obtains the number of rows of the execution result set in real time during the execution process.
  • Step 206 The server determines whether the number of rows of the current execution result set is greater than a preset threshold Kmax. If not, returns to step 205 to continue executing the non-correlated sub-query statement, and if so, stops executing the non-correlated sub-query statement, and ends the non-correlated sub-query. Optimization process.
  • the obtained tuple is counted. If the number of tuples obtained exceeds the sentinel value Kmax, the execution of the subquery is stopped, and the process ends.
  • the conventional optimization method does not call the executor in the optimization phase to complete the acquisition of the query result.
  • the embodiment of the present application proposes that the executor can be called in the optimization phase, and the execution result of a non-correlated subquery has been obtained in advance.
  • w_rows>Kmax means that the number of tuples of the non-correlated subquery exceeds the sentinel value Kmax (ie, the non-correlated subquery is a large table), and the optimization method of the embodiment of the present application is no longer used, and the optimization method may be exited. Regular optimization.
  • Step 207 When the non-correlated sub-query statement is executed, the server rewrites the target clause according to the current execution result set to eliminate the non-correlated sub-query statement.
  • the replaced expression may be simplified according to the operator in the target clause.
  • the non-correlated sub-query statement in the expression of the target clause is replaced with the execution result set; according to the operator in the target clause, the corresponding expression is optimized for the deformation.
  • SQL statement is as follows:
  • the WHERE statement can be transformed into:
  • the initial SQL statement can be transformed into (this optimization, suitable for using range scans on the index to speed up the query):
  • corresponding logical optimization may be performed on the SQL statement optimized by the non-correlated sub-query. For example, when a non-correlated subquery is changed to a constant equality or inequality, other expressions can be further optimized. After the non-correlated subquery is executed in the optimization phase, it can be used to simplify the expression in the WHERE clause, which provides the possibility of further optimization for logic optimization.
  • query statement is as follows:
  • connection operation of the tables t1 and t3 becomes an index scan on the two tables first, and then each tuple participates in the connection operation, so the time complexity of the connection becomes O. (1), greatly improving the efficiency of the connection.
  • the optimization method of the non-correlated sub-query provided by this embodiment may be implemented in the logic optimization stage.
  • the logic optimization stage of the statement may be entered, and the logic optimization stage is adopted.
  • Applying the optimization method provided by the embodiment to optimize the non-correlated sub-query after the optimization of the non-correlated sub-query such as exiting the optimization of the non-correlated sub-query, ending the optimization process of the non-correlated sub-query, or eliminating the non-correlated sub-query After that, continue to optimize the logic of the statement.
  • the statement can be physically optimized.
  • the executor can be called to execute the optimized statement, and finally the result set is returned.
  • the scheme may be in the statement execution stage In the logic optimization stage, the non-correlated sub-query statement is pre-executed, and the target clause of the non-correlated sub-query statement is rewritten according to the execution result to eliminate the non-correlated sub-query, so that it is not required in the subsequent statement execution stage.
  • the query and access related to any non-correlated subquery statement saves the computing resources and the execution time of the database query statement, and improves the execution efficiency of the database query statement with non-correlated subqueries
  • the execution result when used as a constant clause in the SQL statement, it can participate in other types of logic optimization sub-phases, which can bring more optimization possibilities for other types of optimization of SQL statements, and further improve statement execution efficiency.
  • the execution result as a clause constant in DQL/DML, can participate in the optimization process of the WHERE/JOIN-ON clause, which further provides a new optimization possibility for the optimization of the WHERE clause.
  • the embodiment of the present application further provides an apparatus for optimizing a non-correlated sub-query.
  • the apparatus for optimizing the non-correlated sub-query includes: a determining unit 301, an obtaining unit 302, and an executing unit 303. And rewriting unit 304.
  • the determining unit 301 is configured to determine whether a target clause in the database query statement has a non-correlated sub-query statement.
  • the determining unit 301 may determine whether a target clause in the database query statement has a non-correlated sub-query statement in a logic optimization stage of the database query statement.
  • the database query statement can be implemented in multiple languages, for example, can be implemented by SQL or other types of languages.
  • SQL can include languages such as DQL and DML.
  • the subquery is a program module of a common computer language such as a lower layer of a nested query in a database query language.
  • a query is a sub-portion of another query, it can be called a sub-query; the sub-query is a query nested within a query, so it can also be called a nested query.
  • the obtaining unit 302 is configured to obtain the estimated number of rows of the execution result set corresponding to the target clause when the determining unit 301 determines that the non-correlated sub-query statement exists.
  • the execution result set corresponding to the target clause is a result set obtained by executing the target clause.
  • the estimated number of rows is the estimated cardinality of the execution plan corresponding to the target clause, and may be the estimated number of rows returned from the data table result set or index.
  • the estimated number of rows of the execution result set corresponding to the target clause may be based on the historical estimated row number and the selection rate of the table.
  • the obtaining unit 302 may be specifically configured to: obtain a historical estimated line number of the to-be-queried table corresponding to the target clause; obtain a corresponding selection rate according to the expression of the target clause; and estimate the number of rows and the selection rate according to the historical Obtaining the estimated number of rows of the to-be-queried table corresponding to the target clause; the estimated number of rows of the to-be-queried table corresponding to the target clause is used as the estimated number of rows of the execution result set corresponding to the target clause.
  • the executing unit 303 is configured to execute the non-correlated sub-query statement when the estimated number of rows is less than a preset threshold.
  • the preset threshold (Kmax) is a sentinel value, which is a positive integer, indicating the size of the result set of the non-correlated subquery supported by the optimization mode provided by the embodiment of the present application.
  • the preset threshold is a system/session. Parameters, which can be set according to actual needs. In addition, the preset threshold can also be dynamically adjusted, for example, the default value is 2000, the maximum value is 20000 (user definable), and the like.
  • the execution unit 303 in the process of executing the non-correlated sub-query statement, if the number of rows of the obtained execution result set (ie, the number of tuples) is greater than a preset threshold, the execution of the non-correlation sub-function may be stopped.
  • the query statement ends the optimization process of the non-correlated subquery, otherwise it continues to execute the non-correlated subquery statement. That is, the execution unit 303 can be specifically configured to:
  • the non-correlated sub-query statement is executed, and the step of obtaining the number of rows of the current execution result set in real time is performed until the non-correlated sub-query statement is executed.
  • the rewriting unit 304 is configured to rewrite the target clause according to the execution result set of the non-correlated sub-query statement to eliminate the non-correlated sub-query statement.
  • the rewriting unit 304 can be used to directly replace the non-correlated sub-query statement in the expression of the target clause with the execution result to eliminate the non-correlated sub-query.
  • the replaced expression can be simplified according to the operator in the target clause to achieve further optimization.
  • the rewriting unit 304 can include:
  • a replacement subunit 3041 configured to replace the non-correlated subquery statement in the expression of the target clause with an execution result set of the non-correlated subquery statement
  • the subunit 3042 is rewritten to perform corresponding deformation optimization on the replaced expression according to the operator in the target clause.
  • the optimization apparatus of this embodiment may further include: a logic optimization unit 305;
  • the logic optimization unit 305 can be configured to perform corresponding logic optimization on the rewritten target clause after the rewriting unit 304 eliminates the non-correlated sub-query statement.
  • the logic optimization unit 305 may be configured to: after the rewriting unit 304 eliminates the non-correlated sub-query statement, when the rewritten target clause includes a constant equation or inequality, the constant equation included according to the rewritten target clause Or inequality, the expression of the rewritten target clause is optimized.
  • the foregoing units may be implemented as a separate entity, or may be implemented in any combination, and may be implemented as the same or a plurality of entities.
  • the foregoing method embodiments and details are not described herein.
  • the optimization device of the non-correlated sub-query may be integrated in a computing device such as a server, and the server may be a database server.
  • the determining unit 301 determines whether the target clause in the database query statement has a non-correlated sub-query statement, and if so, the obtaining unit 302 obtains the execution result set corresponding to the target clause.
  • the estimated number of rows when the estimated number of rows is less than the preset threshold, the non-correlated subquery statement is executed by the executing unit 303 to obtain an execution result set, and the rewriting unit 304 performs the target clause according to the execution result set.
  • the scheme can pre-execute the non-correlated sub-query statement before the statement execution stage, as in the logic optimization stage, and perform the target clause of the non-correlated sub-query statement according to the execution result.
  • Rewrite to eliminate non-correlated subqueries so that no query and access related to non-correlated subquery statements are needed in the subsequent execution phase of the statement, which saves computation resources and execution time of database query statements, and improves non-correlated subqueries. The efficiency of the execution of database query statements.
  • the embodiment of the present application further provides a computing device, such as a server, as shown in FIG. 4, which shows a schematic structural diagram of a server involved in the embodiment of the present application, specifically:
  • the server 400 can include a processor 401 of one or more processing cores, a memory 402 of one or more computer readable storage media, a radio frequency (RF) circuit 403, a power source 404, an input unit 405, and a display unit 406. And other components. It will be understood by those skilled in the art that the server structure illustrated in FIG. 4 does not constitute a limitation to the server, and may include more or less components than those illustrated, or some components may be combined, or different component arrangements. among them:
  • the processor 401 is the control center of the server, connecting various portions of the entire server using various interfaces and lines, by running or executing software programs and/or modules stored in the memory 402, and recalling data stored in the memory 402, Execute the server's various functions and process data to monitor the server as a whole.
  • the processor 401 may include one or more processing cores; the processor 401 may integrate an application processor and a modem processor, wherein the application processor mainly processes an operating system, a user interface, and an application. Etc.
  • the modem processor primarily handles wireless communications. It can be understood that the above modem processor may not be integrated into the processor 401.
  • Memory 402 can be used to store software programs as well as modules, ie, machine readable instructions.
  • the processor 401 executes various functional applications and data processing by running software programs and modules stored in the memory 402. For example, processor 401 can run an optimization program of non-correlated sub-queries stored in memory 402 to implement the aforementioned optimization method of non-correlated sub-queries.
  • the RF circuit 403 can be used for receiving and transmitting signals during the transmission and reception of information, and in particular, after receiving the downlink information of the base station, it is processed by one or more processors 401. In addition, data related to the uplink is transmitted to the base station.
  • the server also includes a power source 404 (such as a battery) that powers the various components.
  • the power source can be logically coupled to the processor 401 through the power management system to implement functions such as managing charging, discharging, and power management through the power management system.
  • the power supply 404 may also include any one or more of a DC or AC power source, a recharging system, a power failure detection circuit, a power converter or inverter, a power status indicator, and the like.
  • the server can also include an input unit 405 that can be used to receive input numeric or character information and to generate keyboard, mouse, joystick, optical or trackball signal inputs related to user settings and function controls.
  • an input unit 405 can be used to receive input numeric or character information and to generate keyboard, mouse, joystick, optical or trackball signal inputs related to user settings and function controls.
  • the server can also include a display unit 406 that can be used to display information entered by the user or information provided to the user, as well as various graphical user interfaces of the server, which can be composed of graphics, text, icons, video, and It is composed of any combination.
  • the display unit 406 may include a display panel.
  • the display panel may be configured in the form of a Liquid Crystal Display (LCD), an Organic Light-Emitting Diode (OLED), or the like.
  • LCD Liquid Crystal Display
  • OLED Organic Light-Emitting Diode
  • the processor 401 in the server loads the executable file corresponding to the process of one or more applications into the memory 402 according to the following instruction, and is stored in the memory by the processor 401.
  • the application in 402 thus implementing various functions, as follows:
  • the non-correlated sub-query statement rewrites the target clause according to the execution result set of the non-correlated sub-query statement to eliminate the non-correlated sub-query statement.
  • the target clause is rewritten according to the execution result set of the non-correlated sub-query statement, including:
  • the corresponding expression is optimized for the deformation.
  • the estimated number of rows of the execution result set corresponding to the target clause is obtained, including:
  • the estimated number of rows of the to-be-queried table corresponding to the target clause is used as the estimated number of rows of the execution result set corresponding to the target clause.
  • the storage medium may include a read only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk, and the like.

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Computational Linguistics (AREA)
  • Data Mining & Analysis (AREA)
  • Databases & Information Systems (AREA)
  • Physics & Mathematics (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

本申请实施例公开了一种非相关子查询的优化方法、装置和存储介质。该方法包括：确定数据库查询语句中目标子句是否存在非相关子查询语句；若存在，则获取所述目标子句对应的执行结果集的预估行数；当所述预估行数小于预设阈值时，执行所述非相关子查询语句；根据所述非相关子查询语句的执行结果集对所述目标子句进行改写，以消除所述非相关子查询语句。

Description

一种非相关子查询的优化方法、装置和存储介质
本申请要求于2017年6月14日提交中国专利局、申请号为201710447344.0，申请名称为“一种非相关子查询的优化方法和装置”的中国专利申请的优先权，其全部内容通过引用结合在本申请中。
技术领域
本申请涉及计算机技术领域，具体涉及一种非相关子查询的优化方法、装置和存储介质。
背景技术
数据处理系统如数据库系统、大数据处理系统等通常都以支持SQL(Structured Query Language，结构化查询语言)功能为主要实现目标，以为用户提供数据操作便利。
在SQL处理过程中，需要SQL引擎对SQL语句进行词法解析、语法解析、语义分析、逻辑优化和物理优化，然后把得到的执行计划交给执行器去执行，这个过程被划分为三个阶段：分析阶段、优化阶段、执行阶段。
其中，子查询的优化发生在逻辑优化阶段，是逻辑优化阶段中的优化子阶段(通常称为查询重写)。一般来说，子查询可以分为相关子查询和非相关子查询两种类型。
发明内容
本申请实施例提供一种非相关子查询的优化方法、装置和存储介质，可以提高具有非相关子查询的数据库查询语句的执行效率。
本申请实施例提供一种非相关子查询的优化方法，应用于计算设备，包括：
确定数据库查询语句中目标子句是否存在非相关子查询语句；
若存在，则获取所述目标子句对应的执行结果集的预估行数；
当所述预估行数小于预设阈值时，执行所述非相关子查询语句；
根据所述非相关子查询语句的执行结果集对所述目标子句进行改写，以消除所述非相关子查询语句。
本申请实施例还提供一种非相关子查询的优化装置，包括：
处理器以及与所述处理器相连接的存储器，所述存储器中存储有可由所述处理器执行的机器可读指令，所述处理器执行所述机器可读指令完成以下操作：
确定数据库查询语句中目标子句是否存在非相关子查询语句；
当确定存在非相关子查询语句时，获取目标子句对应的执行结果集的预估行数；
当所述预估行数小于预设阈值时，执行所述非相关子查询语句；
根据所述非相关子查询语句的执行结果集对所述目标子句进行改写，以消除所述非相关子查询语句。
本申请实施例提供一种非易失性计算机可读存储介质，所述存储介质中存储有机器可读指令，所述机器可读指令可以由处理器执行以完成以下操作：
确定数据库查询语句中目标子句是否存在非相关子查询语句；
若存在，则获取所述目标子句对应的执行结果集的预估行数；
当所述预估行数小于预设阈值时，执行所述非相关子查询语句；
根据所述非相关子查询语句的执行结果集对所述目标子句进行改写，以消除所述非相关子查询语句。
附图说明
为了更清楚地说明本申请实施例中的技术方案，下面将对实施例描述中所需要使用的附图作简单地介绍，显而易见地，下面描述中的附图仅仅是本申请的一些实施例，对于本领域技术人员来讲，在不付出创造 性劳动的前提下，还可以根据这些附图获得其他的附图。
图1a是本申请实施例提供的非相关子查询的优化方法的实施环境示意图；
图1b是本申请实施例提供的非相关子查询的优化方法的流程示意图；
图1c是本申请实施例提供的半连接优化方式的示意图；
图1d是本申请实施例提供的物化优化方式的示意图；
图2a是本申请实施例提供的非相关子查询的优化方法的另一流程示意图；
图2b是本申请实施例提供的SQL语句处理的流程示意图；
图3a是本申请实施例提供的非相关子查询的优化装置的第一种结构示意图；
图3b是本申请实施例提供的非相关子查询的优化装置的第二种结构示意图；
图3c是本申请实施例提供的非相关子查询的优化装置的第三种结构示意图；
图4是本申请实施例提供的服务器的结构示意图。
具体实施方式
下面将结合本申请实施例中的附图，对本申请实施例中的技术方案进行清楚、完整地描述，显然，所描述的实施例仅仅是本申请一部分实施例，而不是全部的实施例。基于本申请中的实施例，本领域技术人员在没有作出创造性劳动前提下所获得的所有其他实施例，都属于本申请保护的范围。
目前，非相关子查询的常规优化方式主要包括半连接优化和“物化”优化，其中，半连接优化，属于子查询上拉技术，是用半连接(semi join)的语义消除子查询的一种优化形式，属于逻辑优化子阶段；物化优化是把非相关子查询子句标识为“可物化”，并在执行阶段执行一次，然后 把结果缓冲，供后续使用。
然而，采用上述非相关子查询的优化方式对具有非相关子查询的数据库查询语句(如SQL语句)进行优化，在执行阶段执行优化后的数据库查询语句时，需要多次访问子查询指定的表(如采用半连接优化)或者多次访问子查询指定表对应的临时表(如采用物化优化)，时间花费比较大，同时也消耗大量的计算资源，降低了具有非相关子查询的数据库查询语句的执行效率，尤其是在面临大数据量的查询时表现的更为明显。
有鉴于此，本申请实施例提供了一种非相关子查询的优化方法和装置，可以提高具有非相关子查询的数据库查询语句的执行效率。以下将分别进行详细说明。
图1a是本申请实施例提供的一种非相关子查询的优化方法的实施环境示意图。其中，计算设备10集成有本申请任一实施例提供的非相关子查询的优化装置11，用于实现本申请任一实施例提供的非相关子查询的优化方法。该计算设备10与用户终端20之间通过网络30连接，所述网络30可以是有线网络，也可以是无线网络。
本实施例将从非相关子查询的优化装置的角度进行描述，该非相关子查询的优化装置具体可以集成在计算设备中，如集成在服务器中，该服务器可以是数据库服务器等。
本申请实施例提供了一种非相关子查询的优化方法，包括：确定数据库查询语句中目标子句是否存在非相关子查询语句，若存在，则获取目标子句对应的执行结果集的预估行数，当该预估行数小于预设阈值时，执行该非相关子查询语句，得到执行结果集，根据该执行结果集对该目标子句进行改写，以消除该非相关子查询语句。
如图1b所示，该非相关子查询的优化方法的具体流程可以如下：
步骤101、确定数据库查询语句中目标子句是否存在非相关子查询语句，若是，则执行步骤102，若否，则结束非相关子查询的优化流程。
具体地，可以在数据库查询语句的逻辑优化阶段，确定数据库查询 语句中目标子句是否存在非相关子查询语句。
其中，数据库查询语句可以由多种语言实现，比如，可以由SQL(Structured Query Language，结构化查询语言)或者其他类型的语言来实现。其中，SQL可以包括DQL(Data Query Language，数据查询语言)、DML(Data Manipulation Language，数据操作语言)等语言。当数据库查询语句由SQL实现时，数据库查询语句即为SQL语句，当由DQL、DML实现时，数据库查询语句即为DQL语句、DML语句。
本实施例中，子查询是一种常用计算机语言如数据库查询语言中嵌套查询下层的程序模块。当一个查询是另一个查询的子部分时，可以称之为子查询；该子查询是嵌套在某个查询内的查询，因此，也可以称之为嵌套查询。
一般地，根据子查询中涉及的关系对象与外层关系对象间的关系，子查询可以分为相关子查询和非相关子查询两种类型。
相关子查询为：执行依赖于外层父查询的一些属性值的子查询。相关子查询因依赖于父查询的参数，当父查询的参数改变时，相关子查询需要根据新参数值重新执行(查询优化器对相关子查询进行优化有一定意义)。
非相关子查询为：不依赖于外层父查询的任何属性值的子查询，这样子查询具有独立性，可独自求解，形成一个子查询计划先于外层的查询求解。
本实施例中，非相关子查询语句一般嵌套在数据库查询语句的目标子句当中，该目标子句可以为可能出现子查询的子句，比如条件子句。以数据库查询语句为SQL语句时，目标子句可以包括：
a)目标列位置：子查询如果位于目标列，则只能是标量子查询，否则数据库可能返回类似“错误:子查询必须只能返回一个字段”的提示。
b)FROM子句：相关子查询出现在FROM子句中，数据库可能返回类似“在FROM子句中的子查询无法参考相同查询级别中的关系”的提示，所以相关子查询不能出现在FROM子句中；非相关子查询出现在 FROM子句中，可上拉子查询到父层，在多表连接时统一考虑连接代价然后择优。
c)条件子句如(WHERE子句)：出现在WHERE子句中的子查询，是一个条件表达式的一部分，而表达式可以分解为操作符和操作数；根据参与运算的不同的数据类型，操作符也不尽相同，如INT型有“>、<、＝、<>”等操作，这对子查询均有一定的要求(如INT型的等值操作，要求子查询必须是标量子查询)。另外，子查询出现在WHERE子句中的格式，也有用谓词指定的一些操作，如IN、BETWEEN、EXISTS等。
d)JOIN/ON子句：JOIN/ON子句可以拆分为两部分，一是JOIN块类似于FROM子句，二是ON子句块类似于WHERE子句，这两部分都可以出现子查询。子查询的处理方式同FROM子句和WHERE子句。
e)GROUPBY子句：目标列必须和GROUPBY关联。可将子查询写在GROUPBY位置处，但子查询用在GROUPBY处没有实用意义。
f)ORDERBY子句：可将子查询写在ORDERBY位置处。但ORDERBY操作是作用在整条SQL语句上的，子查询用在ORDERBY处没有实用意义。
步骤102、获取目标子句对应的执行结果集的预估行数。
其中，目标子句对应的执行结果集为执行该目标子句得到的结果集。该预估行数即为目标子句对应的执行计划的估计基数，可以为从数据表结果集或索引返回的估计行数。
以SQL语句为例，假设SQL语句如下(uk表示唯一键)：
SELECT*FROM t1
WHERE uk IN(SELECT k2_2 FROM t2 WHERE k2_2>2)；
此时，便可以执行WHERE条件子句对应的结果集的预估行数，也即从t2表中查询到的结果集。
其中，目标子句如条件子句对应的执行结果集的预估行数可以基于表的历史估计行数和选择率得到。也即步骤“获取目标子句对应的执行结果集的预估行数”可以包括：
获取目标子句对应的待查询表的历史估计行数(记为rows)；
根据该目标子句的表达式获取相应的选择率(记为s)；
根据该历史估计行数和该选择率，获取目标子句对应的待查询表的预估行数(记为w_rows)；
将该目标子句对应的待查询表的预估行数作为目标子句对应的执行结果集的预估行数。
比如，通过公式w_rows＝rows*s便可以计算出目标子句对应的执行结果集的预估行数。
其中，待查询表的历史估计行数为此刻之前该待查询表的估计行数，一般可以在查询语句处理过程中，设置一数据统计模块来统计记录待查询表的估计行数，形成历史统计数据。
因此，本实施例中，可以从历史统计数据中获取目标子句对应的待查询表的估计行数，即历史估计行数。
例如，通过上述介绍的方式本实施例可以计算WHERE条件子句对应的执行结果集的预估行数，这个过程与常规的计算实际行数的过程一样。譬如，如果非相关子查询子句没有WHERE条件，则选择率为1，即相当于w_rows等于rows。
步骤103、当该预估行数小于预设阈值时，执行该非相关子查询语句。
在本申请一些实施例中，如果预估行数比较大时，如超过预设阈值时，可以不采用本申请实施例提供的优化方法，而采用常规优化方式。
其中，预设阈值(记为Kmax)是一个哨兵值，为正整数，表示的是本申请实施例提供的优化方法所支持的非相关子查询的结果集的大小，该预设阈值是一个系统/会话参数，其可以根据实际需求设定。另外，该预设阈值还可以动态调节，比如，该预设阈值的默认值为2000，最大值为20000(用户可定义)等。
步骤104、根据非相关子查询语句的执行结果集对该目标子句进行改写，以消除该非相关子查询语句。
具体地，当执行完非相关子查询语句时，可以根据非相关子查询语句的执行结果集对目标子句进行改写。
本实施例中根据执行结果集对目标子句改写的方式包括：直接将目标子句的表达式中的非相关子查询语句替换为执行结果以消除非相关子查询。
例如，以SQL语句为例，假设SQL语句如下(uk表示唯一键)：
SELECT*FROM t1
WHERE uk IN(SELECT k2_2 FROM t2 WHERE k2_2>2)；
非相关子查询“SELECT k2_2 FROM t2 WHERE k2_2>2”被执行后，得到的执行结果为：{3,4}。
那么，可以将原先的SQL语句的WHERE子句变为：
WHERE uk IN{3,4}。
在本申请一些实施例中，还可以进一步简化目标子句如条件子句的表达式，提升数据库查询语句的执行效率。比如，在将目标子句的表达式中的非相关子查询语句替换为执行结果之后，还可以根据目标子句中的操作符对替换后的表达式进行相应的简化变形。也即步骤“根据该执行结果集对该目标子句进行改写”可以包括：
将该目标子句的表达式中的非相关子查询语句替换为非相关子查询语句的执行结果集；
根据该目标子句中的操作符，对替换后的表达式进行相应的变形优化。
例如，以SQL语句为例，假设SQL语句如下(uk表示唯一键)：
SELECT*FROM t1
WHERE uk IN(SELECT k2_2 FROM t2 WHERE k2_2>2)；
非相关子查询“SELECT k2_2 FROM t2 WHERE k2_2>2”被执行后，得到的执行结果为：{3,4}。
那么，可以将原先的SQL语句的WHERE子句变为：
WHERE uk IN{3,4}；
由于操作符为“IN”，因此，可以将原先的SQL语句的WHERE子句变形为：
WHERE uk＝3 OR uk＝4；
这样，原先的SQL语句就可以变形为：
SELECT*FROM t1
WHERE uk＝3 OR uk＝4。
观察上面的这条SQL语句，由原先的带有子查询的查询变为了单表扫描，且可以利用uk上的唯一索引，进行2次索引扫描就能得到查询结果，所以整个SQL语句的时间复杂度变为了O(1)，提升了该条SQL语句的执行效率。
另外，其他的非IN子查询的子句，也可以做类似变换(本文不再举例，同样适用于：<op>SOME、<op>ANY、<op>ALL、LIKE、BETWEEN…AND等操作符。也适用于：＝、>、>＝、<、<＝等操作符。另外，也适用于IN、NOT IN、EXISTS、NOT EXISTS等操作符)，比如，假设SQL语句如下(uk表示唯一键)：
SELECT*FROM t1
WHERE uk>SOME(SELECT k2_2 FROM t2 WHERE k2_2>2)；
WHERE子句可以变换为：
uk>Min(3,4)；
然后进一步变形为：
uk>3；
即初始的SQL语句变形为(这个优化，适合在索引上使用范围扫描加速查询速度)：
SELECT*FROM t1
WHERE uk>3。
为了节省资源，在本申请一些实施例中，可以在执行非相关子查询语句的过程中，如果获得的执行结果集的行数(即元组个数)大于预设阈值时停止执行非相关子查询语句，结束非相关子查询的优化流程，否 则继续执行非相关子查询语句。也即，步骤“执行该非相关子查询语句”可以包括：
执行非相关子查询语句，并在执行的过程中实时获取当前执行结果集的行数；
判断当前执行结果集的行数是否大于预设阈值；
若当前执行结果集的行数大于预设阈值，则停止执行非相关子查询语句，结束非相关子查询的优化流程；
若当前执行结果集的行数不大于预设阈值，则继续执行非相关子查询语句，返回执行步骤“在执行的过程中实时获取当前执行结果集的行数”，直到执行完非相关子查询语句为止。
此时，步骤“根据该非相关子查询语句的执行结果集对该目标子句进行改写”可以包括：当执行完非相关子查询语句时，根据该非相关子查询语句的执行结果集对该目标子句进行改写。
其中，预设阈值为前述的哨兵值Kmax，在执行非相关子查询的过程中，可以对获得的元组进行计数操作(即获取当前执行结果集的行数)，如果获得的元组个数超过哨兵值Kmax，则停止子查询的执行，结束非相关子查询的优化流程。
在消除非相关子查询语句之后，本实施例提供的优化方法还可以包括：对改写后的目标子句进行相应的逻辑优化。比如，如果改写后的目标子句包含常量等式或不等式时，可以根据改写后的目标子句包含的常量等式或不等式，对改写后的目标子句的表达式进行优化。也即，步骤“对改写后的目标子句进行相应的逻辑优化”可以包括：
当改写后的目标子句包含常量等式或不等式时，根据改写后的目标子句包含的常量等式或不等式，对改写后的目标子句的表达式进行优化。
例如，以SQL语句为例，假设SQL语句如下(uk表示唯一键)：
SELECT*FROM t1,t3
WHERE t1.uk IN(SELECT k2_2 FROM t2 WHERE k2_2>3)
AND t1.uk＝t3.uk；
根据非相关子查询语句“SELECT k2_2 FROM t2 WHERE k2_2>3”的执行结果可以将WHERE子句变为：
WHERE t1.uk＝4
AND t1.uk＝t3.uk；
这样，原先的SQL语句就变为：
SELECT*FROM t1,t3
WHERE t1.uk＝4
AND t1.uk＝t3.uk；
由于经过非相关子查询优化后的WHERE子句包含常量等式，因此，可以根据常量等式对WHERE子句进行进一步逻辑优化，得到优化后的SQL语句为：
SELECT*FROM t1,t3
WHERE t1.uk＝4
AND t3.uk＝4；
这样，对表t1和t3的连接操作，变为在这两个表上先分别作一次索引扫描，然后各自有一条元组参与连接操作，所以连接的时间复杂度变为O(1)，大大地提高了连接的效率和语句执行效率。
由上可知，本申请实施例提供的非相关子查询的优化方案可以在语句执行阶段之前如在逻辑优化阶段，预先执行非相关子查询语句，并根据执行结果来对非相关子查询语句所在的目标子句进行改写，以消除非相关子查询，这样在后续语句执行阶段中无需进行任何非相关子查询语句相关的查询和访问，节约了计算资源，节省了数据库查询语句的执行时间，提高了具有非相关子查询的数据库查询语句的执行效率。
当采用半连接优化方式对数据库查询语句进行非相关子查询的优化时，从语句执行结果来看，尽管子查询被消除，但是，连接操作存在，在执行器执行语句期间，对于子查询指定的表还是需要多次访问的，这样数据库查询语句的执行效率不会高，连接的时间花费较多。如果是大数据量的查询，多次访问表还会造成很大性能损失。
例如，以MySQL为例，说明半连接优化方式，参考图1c，从图1c中的执行结果来看，尽管子查询被消除，但是，连接操作存在。其中，id值都为1表示是同一个层次即子查询不存在了，“SHOW WARNINGS”命令的显示结果表明非相关子查询被优化为了半连接(semi join)。在执行器执行期间，对于子查询指定的表t2还是需要多次访问的，这样SQL语句的执行效率不会高，连接的时间花费为O(n*m)。如果是大数据量的查询，多次访问表t2会耗费大量计算资源，造成很大性能损失。
然而，本申请实施例提供的非相关子查询的优化方式可以预先执行非相关子查询语句，并根据执行结果来对非相关子查询语句所在的目标子句进行改写，以消除非相关子查询，相比半连接优化方式，可以避免在语句执行阶段多次访问子查询指定的表，节省了计算资源以及语句执行时间(具体地节省了子查询和父查询做连接操作的时间)，提升了语句执行效率。
当采用物化优化方式对数据库查询语句进行非相关子查询的优化时，从语句执行结果来看，子查询没有被消除，只是被物化到一个临时表中，而且连接操作存在，在执行器执行期间，对于子查询指定的表的物化后的临时表还是需要多次访问的，连接的时间花费较大，这样语句的执行效率不会高。如果是大数据量的查询，临时表需要多次访问也会耗费大量计算资源，造成很大性能损失。比如，参考图1d，以MySQL为例，说明物化优化方式，参考图1d，从图1d中的执行结果来看，查询没有被消除，只是被物化到一个临时表中(MATERIALIZED)。然后，物化的临时表<subquery2>和表t1做了半连接操作(semi join)。在执行器执行期间，对于子查询指定的表t2的物化后的临时表还是需要多次访问的，连接的时间花费为O(n*m)，这样SQL语句的执行效率不会高。如果是大数据量的查询，临时表需要多次访问会造成很大性能损失。
然而，本申请实施例提供的非相关子查询的优化方式可以预先执行非相关子查询语句，并根据执行结果来对非相关子查询语句所在的目标子句进行改写，以消除非相关子查询，相比物化优化方式，可以避免在 语句执行阶段多次访问子查询指定表物化后的临时表，节省了计算资源以及语句执行时间(具体地节省了子查询和父查询做连接操作的时间)，提升了语句执行效率。
根据上述实施例所描述的方法，以下将举例作进一步详细说明。
本申请实施例以非相关子查询的优化装置集成在计算设备，如服务器中，且数据库查询语句为SQL语句为例，来介绍本申请实施例提供的非相关子查询的优化方法。
如图2a所示，一种非相关子查询的优化方法，具体流程可以如下：
步骤201、服务器在SQL语句的逻辑优化阶段，确定SQL语句中目标子句是否存在非相关子查询语句；若是，则执行步骤202，若否，结束非相关子查询的优化流程。
其中，目标子句可以为可能出现子查询的子句，比如可以为WHERE子句、FROM子句、JOIN/ON子句等等。
步骤202、服务器从统计数据中获取目标子句对应的待查询表的历史估计行数rows。
步骤203、服务器根据历史估计行数rows和选择率s，获取目标子句对应的待查询表的预估行数w_rows。
比如，通过公式w_rows＝rows*s便可以计算出目标子句对应的执行结果集的预估行数。
例如，可以计算WHERE条件子句对应的执行结果集的预估行数，这个过程，与常规的计算实际行数的过程一样。如果非相关子查询子句没有WHERE条件，则选择率为1，即相当于w_rows等于rows。
步骤204、服务器判断预估行数是否小于预设阈值Kmax，若是，则执行步骤205，若否，则结束非相关子查询的优化流程。
其中，预设阈值Kmax表示的是本申请实施例优化方法所支持的非相关子查询的结果集的大小，该Kmax是一个系统/会话参数，其可以根据实际需求设定，另外，该Kmax还可以动态调节，比如，默认值为2000，最大值为20000(用户可定义)等。
步骤205、服务器执行非相关子查询语句，并在执行的过程中实时获取执行结果集的行数。
步骤206、服务器判断当前执行结果集的行数是否大于预设阈值Kmax，若否，则返回步骤205继续执行非相关子查询语句，若是，则停止执行非相关子查询语句，结束非相关子查询的优化流程。
比如，在执行非相关子查询语句的过程中，对获得的元组进行计数操作，如果获得的元组个数超过哨兵值Kmax，则停止子查询的执行，结束流程。
常规的优化方式不会在优化阶段调用执行器来完成查询结果的获取，本申请实施例则提出在优化阶段即可以调用执行器，已提前获得一个非相关子查询的执行结果。
其中，w_rows>Kmax意味着非相关子查询的元组个数超过哨兵值Kmax(即非相关子查询是大表)，则不再采用本申请实施例的优化方法，退出本优化方法，可以采用常规的优化方式。
步骤207、当执行完非相关子查询语句时，服务器根据当前执行结果集对该目标子句进行改写，以消除该非相关子查询语句。
比如，可以直接将目标子句的表达式中非相关子查询语句替换为执行结果以消除非相关子查询。
又比如，在将目标子句的表达式中非相关子查询语句替换为执行结果之后，还可以根据目标子句中的操作符对替换后的表达式进行相应的简化变形。具体地，将该目标子句的表达式中非相关子查询语句替换为该执行结果集；根据该目标子句中的操作符，对替换后的表达式进行相应的变形优化。
例如SQL语句如下：
SELECT*FROM t1
WHERE uk>SOME(SELECT k2_2 FROM t2 WHERE k2_2>2)；
其中的WHERE语句可以变换为：
uk>Min(5,6)；
然后进一步变形为：
uk>5；
即初始的SQL语句可变形为(这个优化，适合在索引上使用范围扫描加速查询速度)：
SELECT*FROM t1
WHERE uk>5。
在本申请一些实施例中，在消除非相关子查询语句之后，可以对经过非相关子查询优化后的SQL语句进行相应的逻辑优化。比如，当非相关子查询被变为常量等式或不等式，可以进一步优化其他表达式。非相关子查询在优化阶段执行后，即可用于化简WHERE子句中的表达式，这一点，为逻辑优化提供了进一步优化的可能性。
例如查询语句如下：
SELECT*FROM t1,t3
WHERE t1.uk IN(SELECT k2_2 FROM t2 WHERE k2_2>3)
AND t1.uk＝t3.uk；
结果非相关子查询的优化，上述语句可以变形为
SELECT*FROM t1,t3
WHERE t1.uk＝5
AND t1.uk＝t3.uk；
然后，在后续的逻辑优化中，可以进一步变形为：
SELECT*FROM t1,t3
WHERE t1.uk＝5
AND t3.uk＝5。
从上述语句可以看出：对表t1和t3的连接操作，变为在这两个表上先分别作一次索引扫描，然后各自有一条元组参与连接操作，所以连接的时间复杂度变为O(1)，大大地提高了连接的效率。
本实施例提供的非相关子查询的优化方式可以在逻辑优化阶段实施，参考图2b并结合图2a，在对语句进行语法分析之后，可以进入语句 的逻辑优化阶段，在逻辑优化阶段中采用本申请实施例提供的优化方式进行非相关子查询的优化，在进行非相关子查询的优化之后(比如退出非相关子查询的优化即结束非相关子查询的优化流程之后，或者消除非相关子查询之后)，继续对语句做相应的逻辑优化，在逻辑优化完成后，可以对语句进行物理优化，在物理优化完成后，可以调用执行器执行优化后的语句，最后返回结果集。
由上可知，在本申请实施例中，确定数据库查询语句中目标子句是否存在非相关子查询语句，若存在，则获取目标子句对应的执行结果集的预估行数，当该预估行数小于预设阈值时，执行该非相关子查询语句，得到执行结果集，根据该执行结果集对该目标子句进行改写，以消除该非相关子查询语句；该方案可以在语句执行阶段之前如在逻辑优化阶段，预先执行非相关子查询语句，并根据执行结果来对非相关子查询语句所在的目标子句进行改写，以消除非相关子查询，这样在后续语句执行阶段中无需进行任何非相关子查询语句相关的查询和访问，节省了计算资源以及数据库查询语句的执行时间，提高了具有非相关子查询的数据库查询语句的执行效率。
此外，当执行结果作为SQL语句中的常量子句时，可以参与到其他类型的逻辑优化子阶段，能为SQL语句开展其他类型的优化带来更多优化的可能，进一步提升语句执行效率。比如，执行结果作为DQL/DML中的子句常量，可以参与到WHERE/JOIN-ON子句的优化过程中，即进一步为WHERE子句的优化提供了新的优化可能。
为了更好地实施以上方法，本申请实施例还提供了非相关子查询的优化装置，如图3a所示，该非相关子查询的优化装置包括：确定单元301、获取单元302、执行单元303和改写单元304。
确定单元301，用于确定数据库查询语句中目标子句是否存在非相关子查询语句。
具体地，确定单元301可以在数据库查询语句的逻辑优化阶段，确定数据库查询语句中目标子句是否存在非相关子查询语句。
其中，数据库查询语句可以由多种语言实现，比如，可以由SQL或者其他类型的语言来实现。其中，SQL可以包括DQL、DML等语言。
本实施例中，子查询是一种常用计算机语言如数据库查询语言中嵌套查询下层的程序模块。当一个查询是另一个查询的子部分时，可以称之为子查询；该子查询是嵌套在某个查询内的查询，因此，也可以称之为嵌套查询。
获取单元302，用于当确定单元301确定存在非相关子查询语句时，获取目标子句对应的执行结果集的预估行数。
其中，目标子句对应的执行结果集为执行该目标子句得到的结果集。该预估行数即为目标子句对应的执行计划的估计基数，可以为从数据表结果集或索引返回的估计行数。
本实施例中，目标子句如条件子句对应的执行结果集的预估行数可以基于表的历史估计行数和选择率得。比如，获取单元302，可以具体用于：获取目标子句对应的待查询表的历史估计行数；根据该目标子句的表达式获取相应的选择率；根据该历史估计行数和该选择率，获取目标子句对应的待查询表的预估行数；将该目标子句对应的待查询表的预估行数作为目标子句对应的执行结果集的预估行数。
比如，通过公式w_rows＝rows*s便可以计算出目标子句对应的执行结果集的预估行数。
执行单元303，用于当该预估行数小于预设阈值时，执行该非相关子查询语句。
其中，预设阈值(Kmax)是一个哨兵值，为正整数，表示的是本申请实施例提供的优化方式所支持的非相关子查询的结果集的大小，该预设阈值是一个系统/会话参数，其可以根据实际需求设定。另外，该预设阈值还可以动态调节，比如，默认值为2000，最大值为20000(用户可定义)等。
为了节省资源，在本申请一些实施例中，可以在执行非相关子查询语句的过程中，如果获得的执行结果集的行数(即元组个数)大于预设阈值时停止执行非相关子查询语句，结束非相关子查询的优化流程，否则继续执行非相关子查询语句。也即，执行单元303可以具体用于：
执行非相关子查询语句，并在执行的过程中实时获取当前执行结果集的行数；
判断当前执行结果集的行数是否大于该预设阈值；
若当前执行结果集的行数不大于预设阈值，则继续执行该非相关子查询语句，并返回实时获取当前执行结果集的行数的步骤，直到执行完非相关子查询语句为止。
若当前执行结果集的行数大于预设阈值，则停止执行非相关子查询语句，结束非相关子查询的优化。
改写单元304，用于根据该非相关子查询语句的执行结果集对该目标子句进行改写，以消除该非相关子查询语句。
比如，改写单元304，可以用于直接将目标子句的表达式中的非相关子查询语句替换为执行结果以消除非相关子查询。
又比如，在将目标子句的表达式中非相关子查询语句替换为执行结果之后，还可以根据目标子句中的操作符对替换后的表达式进行相应的简化变形，达到进一步优化目的。参考图3b，该改写单元304可以包括：
替换子单元3041，用于将该目标子句的表达式中的非相关子查询语句替换为非相关子查询语句的执行结果集；
改写子单元3042，用于根据该目标子句中的操作符，对替换后的表达式进行相应的变形优化。
参考图3c，本实施例的优化装置还可以包括：逻辑优化单元305；
逻辑优化单元305可以用于在改写单元304消除该非相关子查询语句之后对改写后的目标子句进行相应的逻辑优化。
比如，逻辑优化单元305，可以用于在改写单元304消除该非相关子查询语句之后，当改写后的目标子句包含常量等式或不等式时，根据改 写后的目标子句包含的常量等式或不等式，对改写后的目标子句的表达式进行优化。
具体实施时，以上各个单元可以作为独立的实体来实现，也可以进行任意组合，作为同一或若干个实体来实现，以上各个单元的具体实施可参见前面的方法实施例，在此不再赘述。
该非相关子查询的优化装置具体可以集成在计算设备如服务器中，该服务器可以为数据库服务器。
由上可知，在本申请实施例中，优化装置通过确定单元301确定数据库查询语句中目标子句是否存在非相关子查询语句，若存在，则由获取单元302获取目标子句对应的执行结果集的预估行数，当该预估行数小于预设阈值时，由执行单元303执行该非相关子查询语句，得到执行结果集，由改写单元304根据该执行结果集对该目标子句进行改写，以消除该非相关子查询语句；该方案可以在语句执行阶段之前如在逻辑优化阶段，预先执行非相关子查询语句，并根据执行结果来对非相关子查询语句所在的目标子句进行改写，以消除非相关子查询，这样在后续语句执行阶段中无需进行任何非相关子查询语句相关的查询和访问，节省了计算资源以及数据库查询语句的执行时间，提高了具有非相关子查询的数据库查询语句的执行效率。
相应的，本申请实施例还提供了一种计算设备，如服务器，如图4所示，其示出了本申请实施例所涉及的服务器的结构示意图，具体来讲：
该服务器400可以包括一个或者一个以上处理核心的处理器401、一个或一个以上计算机可读存储介质的存储器402、射频(Radio Frequency，RF)电路403、电源404、输入单元405、以及显示单元406等部件。本领域技术人员可以理解，图4中示出的服务器结构并不构成对服务器的限定，可以包括比图示更多或更少的部件，或者组合某些部件，或者不同的部件布置。其中：
处理器401是该服务器的控制中心，利用各种接口和线路连接整个服务器的各个部分，通过运行或执行存储在存储器402内的软件程序和/ 或模块，以及调用存储在存储器402内的数据，执行服务器的各种功能和处理数据，从而对服务器进行整体监控。在本申请一些实施例中，处理器401可包括一个或多个处理核心；处理器401可集成应用处理器和调制解调处理器，其中，应用处理器主要处理操作系统、用户界面和应用程序等，调制解调处理器主要处理无线通信。可以理解的是，上述调制解调处理器也可以不集成到处理器401中。
存储器402可用于存储软件程序以及模块，即机器可读指令。处理器401通过运行存储在存储器402的软件程序以及模块，从而执行各种功能应用以及数据处理。例如，处理器401可运行存储在存储器402中的非相关子查询的优化程序以实现前述的非相关子查询的优化方法。
RF电路403可用于收发信息过程中，信号的接收和发送，特别地，将基站的下行信息接收后，交由一个或者一个以上处理器401处理。另外，将涉及上行的数据发送给基站。
服务器还包括给各个部件供电的电源404(比如电池)。在本申请一些实施例中，电源可以通过电源管理系统与处理器401逻辑相连，从而通过电源管理系统实现管理充电、放电、以及功耗管理等功能。电源404还可以包括一个或一个以上的直流或交流电源、再充电系统、电源故障检测电路、电源转换器或者逆变器、电源状态指示器等任意组件。
该服务器还可包括输入单元405，该输入单元405可用于接收输入的数字或字符信息，以及产生与用户设置以及功能控制有关的键盘、鼠标、操作杆、光学或者轨迹球信号输入。
该服务器还可包括显示单元406，该显示单元406可用于显示由用户输入的信息或提供给用户的信息以及服务器的各种图形用户接口，这些图形用户接口可以由图形、文本、图标、视频和其任意组合来构成。显示单元406可包括显示面板，可选的，可以采用液晶显示器(LCD，Liquid Crystal Display)、有机发光二极管(OLED，Organic Light-Emitting Diode)等形式来配置显示面板。
具体在本实施例中，服务器中的处理器401会按照如下的指令，将 一个或一个以上的应用程序的进程对应的可执行文件加载到存储器402中，并由处理器401来运行存储在存储器402中的应用程序，从而实现各种功能，如下：
确定数据库查询语句中目标子句是否存在非相关子查询语句，若存在，则获取目标子句对应的执行结果集的预估行数，当所述预估行数小于预设阈值时，执行所述非相关子查询语句，根据所述非相关子查询语句的执行结果集对所述目标子句进行改写，以消除所述非相关子查询语句。
其中，根据所述非相关子查询语句的执行结果集对所述目标子句进行改写，包括：
将所述目标子句的表达式中的非相关子查询语句替换为非相关子查询语句的执行结果集；
根据所述目标子句中的操作符，对替换后的表达式进行相应的变形优化。
其中，获取目标子句对应的执行结果集的预估行数，包括：
获取目标子句对应的待查询表的历史估计行数；
根据所述目标子句的表达式获取相应的选择率；
根据所述历史估计行数和所述选择率，获取目标子句对应的待查询表的预估行数；
将所述目标子句对应的待查询表的预估行数作为所述目标子句对应的执行结果集的预估行数。
具体的操作步骤或过程，可以参考前面非相关子查询的优化方法实施例的详细描述，在此不再赘述。
本领域普通技术人员可以理解上述实施例的各种方法中的全部或部分步骤是可以通过程序来指令相关的硬件来完成的，该程序可以存储于一非易失性计算机可读存储介质中，该存储介质可以包括：只读存储器(ROM，Read Only Memory)、随机存取记忆体(RAM，Random Access Memory)、磁盘或光盘等。
以上对本申请实施例所提供的一种非相关子查询的优化方法和装置进行了详细介绍，本文中应用了具体个例对本申请的原理及实施方式进行了阐述，以上实施例的说明只是用于帮助理解本申请的方法及其核心思想；同时，对于本领域的技术人员，依据本申请的思想，在具体实施方式及应用范围上均会有改变之处，综上所述，本说明书内容不应理解为对本申请的限制。

Claims (15)

  1. 一种非相关子查询的优化方法，应用于计算设备，包括：
    确定数据库查询语句中目标子句是否存在非相关子查询语句；
    若存在，则获取所述目标子句对应的执行结果集的预估行数；
    当所述预估行数小于预设阈值时，执行所述非相关子查询语句；
    根据所述非相关子查询语句的执行结果集对所述目标子句进行改写，以消除所述非相关子查询语句。
  2. 如权利要求1所述的优化方法，所述根据所述非相关子查询语句的执行结果集对所述目标子句进行改写，包括：
    将所述目标子句的表达式中的非相关子查询语句替换为非相关子查询语句的执行结果集；
    根据所述目标子句中的操作符，对替换后的表达式进行相应的变形优化。
  3. 如权利要求1所述的优化方法，所述获取所述目标子句对应的执行结果集的预估行数，包括：
    获取所述目标子句对应的待查询表的历史估计行数；
    根据所述目标子句的表达式获取相应的选择率；
    根据所述历史估计行数和所述选择率，获取所述目标子句对应的待查询表的预估行数；
    将所述目标子句对应的待查询表的预估行数作为所述目标子句对应的执行结果集的预估行数。
  4. 如权利要求1所述的优化方法，所述执行所述非相关子查询语句，包括：
    执行所述非相关子查询语句，并在执行的过程中实时获取当前执行结果集的行数；
    判断当前执行结果集的行数是否大于所述预设阈值；
    若否，则继续执行所述非相关子查询语句，并返回实时获取当前执行结果集的行数的步骤，直到执行完所述非相关子查询语句为止。
  5. 如权利要求1所述的优化方法，在根据所述非相关子查询语句的执行结果集对所述目标子句进行改写，以消除所述非相关子查询语句之后，所述优化方法还包括：
    当改写后的目标子句包含常量等式或不等式时，根据改写后的目标子句包含的常量等式或不等式，对改写后的目标子句的表达式进行优化。
  6. 一种非相关子查询的优化装置，包括：
    处理器以及与所述处理器相连接的存储器，所述存储器中存储有可由所述处理器执行的机器可读指令，所述处理器执行所述机器可读指令完成以下操作：
    确定数据库查询语句中目标子句是否存在非相关子查询语句；
    当确定存在非相关子查询语句时，获取目标子句对应的执行结果集的预估行数；
    当所述预估行数小于预设阈值时，执行所述非相关子查询语句；
    根据所述非相关子查询语句的执行结果集对所述目标子句进行改写，以消除所述非相关子查询语句。
  7. 如权利要求6所述的优化装置，所述处理器执行所述机器可读指令完成以下操作：
    将所述目标子句的表达式中的非相关子查询语句替换为非相关子查询语句的执行结果集；
    根据所述目标子句中的操作符，对替换后的表达式进行相应的变形优化。
  8. 如权利要求6所述的优化装置，所述处理器执行所述机器可读指令完成以下操作：
    获取所述目标子句对应的待查询表的历史估计行数；
    根据所述目标子句的表达式获取相应的选择率；
    根据所述历史估计行数和所述选择率，获取所述目标子句对应的待查询表的预估行数；
    将所述目标子句对应的待查询表的预估行数作为所述目标子句对 应的执行结果集的预估行数。
  9. 如权利要求6所述的优化装置，所述处理器执行所述机器可读指令完成以下操作：
    执行所述非相关子查询语句，并在执行的过程中实时获取当前执行结果集的行数；
    判断当前执行结果集的行数是否大于所述预设阈值；
    若否，则继续执行所述非相关子查询语句，并返回实时获取当前执行结果集的行数的步骤，直到执行完所述非相关子查询语句为止。
  10. 如权利要求6所述的优化装置，所述处理器执行所述机器可读指令完成以下操作；
    当改写后的目标子句包含常量等式或不等式时，根据改写后的目标子句包含的常量等式或不等式，对改写后的目标子句的表达式进行优化。
  11. 一种非易失性计算机可读存储介质，所述存储介质中存储有机器可读指令，所述机器可读指令可以由处理器执行以完成以下操作：
    确定数据库查询语句中目标子句是否存在非相关子查询语句；
    若存在，则获取所述目标子句对应的执行结果集的预估行数；
    当所述预估行数小于预设阈值时，执行所述非相关子查询语句；
    根据所述非相关子查询语句的执行结果集对所述目标子句进行改写，以消除所述非相关子查询语句。
  12. 如权利要求11所述的非易失性计算机可读存储介质，所述根据所述非相关子查询语句的执行结果集对所述目标子句进行改写，包括：
    将所述目标子句的表达式中的非相关子查询语句替换为非相关子查询语句的执行结果集；
    根据所述目标子句中的操作符，对替换后的表达式进行相应的变形优化。
  13. 如权利要求11所述的非易失性计算机可读存储介质，所述获取所述目标子句对应的执行结果集的预估行数，包括：
    获取所述目标子句对应的待查询表的历史估计行数；
    根据所述目标子句的表达式获取相应的选择率；
    根据所述历史估计行数和所述选择率，获取所述目标子句对应的待查询表的预估行数；
    将所述目标子句对应的待查询表的预估行数作为所述目标子句对应的执行结果集的预估行数。
  14. 如权利要求11所述的非易失性计算机可读存储介质，所述执行所述非相关子查询语句，包括：
    执行所述非相关子查询语句，并在执行的过程中实时获取当前执行结果集的行数；
    判断当前执行结果集的行数是否大于所述预设阈值；
    若否，则继续执行所述非相关子查询语句，并返回实时获取当前执行结果集的行数的步骤，直到执行完所述非相关子查询语句为止。
  15. 如权利要求11所述的非易失性计算机可读存储介质，所述机器可读指令可以由处理器执行以完成以下操作：
    当改写后的目标子句包含常量等式或不等式时，根据改写后的目标子句包含的常量等式或不等式，对改写后的目标子句的表达式进行优化。
PCT/CN2018/090610 2017-06-14 2018-06-11 一种非相关子查询的优化方法、装置和存储介质 Ceased WO2018228322A1 (zh)

Priority Applications (1)

Application Number Priority Date Filing Date Title
US16/521,895 US11269878B2 (en) 2017-06-14 2019-07-25 Uncorrelated subquery optimization method and apparatus, and storage medium

Applications Claiming Priority (2)

Application Number Priority Date Filing Date Title
CN201710447344.0A CN107315790B (zh) 2017-06-14 2017-06-14 一种非相关子查询的优化方法和装置
CN201710447344.0 2017-06-14

Related Child Applications (1)

Application Number Title Priority Date Filing Date
US16/521,895 Continuation US11269878B2 (en) 2017-06-14 2019-07-25 Uncorrelated subquery optimization method and apparatus, and storage medium

Publications (1)

Publication Number Publication Date
WO2018228322A1 true WO2018228322A1 (zh) 2018-12-20

Family

ID=60184127

Family Applications (1)

Application Number Title Priority Date Filing Date
PCT/CN2018/090610 Ceased WO2018228322A1 (zh) 2017-06-14 2018-06-11 一种非相关子查询的优化方法、装置和存储介质

Country Status (3)

Country Link
US (1) US11269878B2 (zh)
CN (1) CN107315790B (zh)
WO (1) WO2018228322A1 (zh)

Cited By (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN111159218A (zh) * 2019-12-31 2020-05-15 中科曙光国际信息产业有限公司 数据处理方法、装置及可读存储介质
CN117171197A (zh) * 2023-10-16 2023-12-05 北京人大金仓信息技术股份有限公司 数据库操作语句的分析方法、存储介质与计算机设备

Families Citing this family (25)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN107315790B (zh) 2017-06-14 2021-07-06 腾讯科技（深圳）有限公司 一种非相关子查询的优化方法和装置
CN110555030B (zh) * 2018-03-28 2024-06-18 北京京东尚科信息技术有限公司 一种sql语句的处理方法和装置
CN110908993B (zh) * 2018-09-14 2025-02-21 北京京东尚科信息技术有限公司 分析数据库索引合理性的方法和装置
CN109656946B (zh) * 2018-09-29 2022-12-16 创新先进技术有限公司 一种多表关联查询方法、装置及设备
CN109508339B (zh) * 2018-11-12 2020-10-09 上海达梦数据库有限公司 一种数据查询方法、装置、终端设备和存储介质
CN110019349B (zh) * 2019-04-02 2025-01-28 深圳前海微众银行股份有限公司 语句预警方法、装置、设备及计算机可读存储介质
CN110659327A (zh) * 2019-08-16 2020-01-07 平安科技（深圳）有限公司 实现异构数据库之间数据交互式查询的方法和相关装置
CN110489446B (zh) * 2019-09-10 2022-05-24 北京东方国信科技股份有限公司 基于分布式数据库的查询方法及装置
CN110674177B (zh) * 2019-09-30 2023-04-11 奇安信科技集团股份有限公司 数据查询方法、装置、电子设备和存储介质
CN110968593B (zh) * 2019-12-10 2023-10-03 上海达梦数据库有限公司 数据库sql语句优化方法、装置、设备和存储介质
CN111309753B (zh) * 2020-01-21 2024-02-06 上海达梦数据库有限公司 结构化查询语句的优化方法、装置、设备及存储介质
CN113946839B (zh) * 2020-07-16 2025-12-19 南京中兴软件有限责任公司 数据访问方法、装置、存储介质及电子装置
CN113204568A (zh) * 2021-05-26 2021-08-03 上海达梦数据库有限公司 一种结构化查询语句优化方法、装置、设备及存储介质
CN113282607A (zh) * 2021-06-02 2021-08-20 山东健康医疗大数据有限公司 一种数据库的优化查询方法
CN113377808B (zh) * 2021-06-03 2025-09-16 北京沃东天骏信息技术有限公司 Sql优化方法和装置
CN113535756B (zh) * 2021-07-30 2023-05-30 上海达梦数据库有限公司 数据查询方法、装置、设备及存储介质
CN113886420B (zh) * 2021-09-29 2024-06-25 平安国际智慧城市科技股份有限公司 Sql语句的生成方法、装置、电子设备及存储介质
CN114185919B (zh) * 2021-11-05 2024-05-28 深圳前海微众银行股份有限公司 慢查询告警方法、电子设备及存储介质
CN114840536B (zh) * 2022-04-18 2025-03-21 深圳市汉云科技有限公司 分布式数据库的数据删除方法、装置及电子设备
US12222939B2 (en) * 2022-06-06 2025-02-11 Microsoft Technology Licensing, Llc Systems and methods for accelerating and optimizing groupwise comparison in relational databases
CN114969101B (zh) * 2022-07-13 2022-10-21 北京奥星贝斯科技有限公司 Sql语句的处理方法及装置
CN115168408B (zh) * 2022-08-16 2024-05-28 北京永洪商智科技有限公司 基于强化学习的查询优化方法、装置、设备及存储介质
CN116126830B (zh) * 2023-02-16 2025-11-28 厦门大学 数据库管理系统的逻辑缺陷的检测方法、装置及可读介质
CN117290377A (zh) * 2023-09-28 2023-12-26 北京领雁科技股份有限公司 一种关系型数据库间sql语句的转换方法以及装置
US20250156418A1 (en) * 2023-11-14 2025-05-15 Workday, Inc. Techniques to improve query performance using dynamic filtering based on left-join aggregation pattern

Citations (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN103678621A (zh) * 2013-12-18 2014-03-26 上海达梦数据库有限公司 基于常量替换的sql语句优化方法
US20140379690A1 (en) * 2013-06-20 2014-12-25 Oracle International Corporation Transforming a query by eliminating a subquery
CN104765731A (zh) * 2014-01-02 2015-07-08 国际商业机器公司 数据库查询优化方法和设备
US9183254B1 (en) * 2012-05-04 2015-11-10 Paraccel Llc Optimizing database queries using subquery composition
CN107315790A (zh) * 2017-06-14 2017-11-03 腾讯科技（深圳）有限公司 一种非相关子查询的优化方法和装置

Family Cites Families (8)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US6341281B1 (en) * 1998-04-14 2002-01-22 Sybase, Inc. Database system with methods for optimizing performance of correlated subqueries by reusing invariant results of operator tree
US7376646B2 (en) * 2005-06-17 2008-05-20 International Business Machines Corporation Cost-based subquery correlation and decorrelation
CN103714058B (zh) * 2012-09-28 2017-05-17 Sap欧洲公司 用于数据库查询优化的方法以及使用该方法的系统
KR101432700B1 (ko) * 2012-10-10 2014-08-25 (주)티베로 쿼리의 최적화를 위한 방법
CN104123288B (zh) * 2013-04-24 2018-06-01 阿里巴巴集团控股有限公司 一种数据查询方法及装置
CN104714973A (zh) * 2013-12-17 2015-06-17 航天信息股份有限公司 一种处理查询语句的方法和装置
US10007700B2 (en) * 2015-06-29 2018-06-26 Oracle International Corporation Query optimization for group-by extensions and distinct aggregate functions
US10642832B1 (en) * 2016-11-06 2020-05-05 Tableau Software, Inc. Reducing the domain of a subquery by retrieving constraints from the outer query

Patent Citations (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US9183254B1 (en) * 2012-05-04 2015-11-10 Paraccel Llc Optimizing database queries using subquery composition
US20140379690A1 (en) * 2013-06-20 2014-12-25 Oracle International Corporation Transforming a query by eliminating a subquery
CN103678621A (zh) * 2013-12-18 2014-03-26 上海达梦数据库有限公司 基于常量替换的sql语句优化方法
CN104765731A (zh) * 2014-01-02 2015-07-08 国际商业机器公司 数据库查询优化方法和设备
CN107315790A (zh) * 2017-06-14 2017-11-03 腾讯科技（深圳）有限公司 一种非相关子查询的优化方法和装置

Cited By (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN111159218A (zh) * 2019-12-31 2020-05-15 中科曙光国际信息产业有限公司 数据处理方法、装置及可读存储介质
CN111159218B (zh) * 2019-12-31 2023-10-31 中科曙光国际信息产业有限公司 数据处理方法、装置及可读存储介质
CN117171197A (zh) * 2023-10-16 2023-12-05 北京人大金仓信息技术股份有限公司 数据库操作语句的分析方法、存储介质与计算机设备

Also Published As

Publication number Publication date
US20190347260A1 (en) 2019-11-14
CN107315790A (zh) 2017-11-03
US11269878B2 (en) 2022-03-08
CN107315790B (zh) 2021-07-06

Similar Documents

Publication Publication Date Title
WO2018228322A1 (zh) 一种非相关子查询的优化方法、装置和存储介质
US10372707B2 (en) Query execution pipelining with pump operators
US10885032B2 (en) Query execution pipelining with shared states for query operators
US10733184B2 (en) Query planning and execution with source and sink operators
US8965918B2 (en) Decomposed query conditions
US10860579B2 (en) Query planning and execution with reusable memory stack
US11132366B2 (en) Transforming directed acyclic graph shaped sub plans to enable late materialization
KR20180035035A (ko) 데이터 엔진에서의 질의 최적화 방법 및 장치
US11704317B2 (en) Partial group by for eager group by placement query plans
US10423619B2 (en) Query plan generation for precompiled and code generating query operations
US9342545B2 (en) Using a partially built index in a computer database system
US20230214393A1 (en) Query execution including pause and detach operations after first data fetch
WO2024060956A1 (zh) 一种混合数据库管理方法、装置、混合数据库及电子设备
US10733185B2 (en) Access pattern based optimization of memory access
US11789741B2 (en) Determining an optimum quantity of interleaved instruction streams of defined coroutines
US12339840B2 (en) Result streaming for distributed query processing
US12118003B2 (en) On-demand access of database table partitions
US20260079935A1 (en) Table-valued parameter for call statement
US12572539B2 (en) Parameterized structured query language view sharing
US12164519B2 (en) Result streaming by reordering pipelines
US20250200041A1 (en) Jit code compilation during runtime based on runtime statistics
CN113836175B (zh) 数据访问方法、装置、设备及存储介质
US20260017262A1 (en) Table semijoin dynamic pruning
US20250238378A1 (en) Spill-to-disk in projection operations
CN116991884A (zh) 数据查询方法及装置

Legal Events

Date Code Title Description
121 Ep: the epo has been informed by wipo that ep was designated in this application

Ref document number: 18817004

Country of ref document: EP

Kind code of ref document: A1

NENP Non-entry into the national phase

Ref country code: DE

122 Ep: pct application non-entry in european phase

Ref document number: 18817004

Country of ref document: EP

Kind code of ref document: A1