Query instruction generation method, device, equipment, medium and program product

Abstract

The embodiment of the present application provides a query instruction generation method, device, equipment, medium and program product. The method is applied to an electronic device including an optimizer, and the method includes: obtaining an initial query instruction; when the initial query instruction includes at least two sub-queries, determining the sub-query operators and sub-query connectors of at least two sub-queries through the optimizer; when the sub-query operator is any one of exists, in, any and some, and the sub-query connector is or, determining whether the query table items of at least two sub-queries are the same, and generating a first target query instruction if they are the same; or, when the sub-query operators are not exists, and the sub-query connector is and, determining whether the query table items of at least two sub-queries are the same, and generating a second target query instruction if they are the same. The query instruction generated by this method can reduce the consumption of computing resources.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a query instruction generation method, device, equipment, medium and program product. Background Art

[0002] In a database management system, query instructions for managing the database can be compiled using structured query language (SQL). For example, query instructions can be compiled to perform management operations such as querying data in the database, inserting records, deleting records, updating records, and creating and modifying database structures.

[0003] If the compiled query instruction is relatively complex, it will be optimized by the optimizer. In the existing method, the optimizer usually rewrites the initial query instruction using a common table expression (CTE) to obtain a new query instruction containing the CTE.

[0004] However, the new query instruction generated based on the CTE method still has the problem of repeated scanning of query table items. Therefore, when executing the new query instruction containing the CTE, more computing resources will be consumed. Summary of the invention

[0005] The embodiments of the present application provide a query instruction generation method, apparatus, device, medium and program product for realizing the generation of query instructions that can avoid repeated scanning of query table items, so that when the generated query instructions are executed, the consumption of computing resources when scanning query table items can be reduced.

[0006] In a first aspect, an embodiment of the present application provides a query instruction generation method, which is applied to an electronic device, wherein the electronic device includes an optimizer, and the method includes:

[0007] Obtaining an initial query instruction, wherein the initial query instruction is an instruction compiled using a structured query language;

[0008] In a case where the initial query instruction includes at least two sub-queries, determining, by the optimizer, sub-query operators of the at least two sub-queries and a sub-query connector connecting the at least two sub-queries;

[0009] When the subquery operators of the at least two subqueries are any one of a preset operator set and the subquery connector is or, the optimizer determines whether the query table items of the at least two subqueries are the same, and when the query table items of the at least two subqueries are the same, generates a first target query instruction, wherein the first target query instruction includes merging the at least two subqueries into a first merged subquery connected by or, and the preset operator set includes operators exists, in, any, and some;

[0010] Alternatively, when the subquery operators of the at least two subqueries are both not exists and the subquery connector is and, the optimizer determines whether the query table items of the at least two subqueries are the same, and when the query table items of the at least two subqueries are the same, generates a second target query instruction, wherein the second target query instruction includes merging the at least two subqueries into a second merged subquery connected by or.

[0011] In a possible implementation, generating the first target query instruction includes: according to the initial query instruction, merging the sub-query operators of the at least two sub-queries into one exists to obtain the query operator of the first target query instruction, and generating the first target query instruction based on the query operator of the first target query instruction;

[0012] Alternatively, the generating of the second target query instruction includes: according to the initial query instruction, merging the sub-query operators of the at least two sub-queries into one not exists to obtain the query operator of the second target query instruction, and generating the second target query instruction based on the query operator of the second target query instruction.

[0013] In a possible implementation, the method further includes: when the optimizer supports semi-join, performing a semi-join equivalent conversion on the first target query instruction to generate a third target query instruction corresponding to the first target query instruction, and the execution result of the third target query instruction is the same as the execution result of the first target query instruction and the execution result of the initial query instruction.

[0014] In a possible implementation, the method further includes: when the optimizer supports anti-join, performing an anti-join equivalent conversion on the second target query instruction to generate a fourth target query instruction corresponding to the second target query instruction, and the execution result of the fourth target query instruction and the execution result of the second target query instruction are the same as the execution result of the initial query instruction.

[0015] In a possible implementation, after obtaining the initial query instruction, it also includes: detecting by the optimizer whether the initial query instruction includes a preset target item, the preset target item includes at least one of a sorting operation, a grouping operation or a target column expression; if it is determined that the initial query instruction includes a preset target item, eliminating the preset target item.

[0016] In a possible implementation, the method further includes: calculating, by the optimizer, the execution costs corresponding to the initial query instruction, the first target query instruction, and the third target query instruction, respectively, and determining the query instruction corresponding to the smallest execution cost among the execution costs as the target execution instruction for execution; or, calculating, by the optimizer, the execution costs corresponding to the initial query instruction, the second target query instruction, and the fourth target query instruction, respectively, and determining the query instruction corresponding to the smallest execution cost among the execution costs as the target execution instruction for execution.

[0017] In a second aspect, an embodiment of the present application provides a query instruction generation device, which is applied to an electronic device, wherein the electronic device includes an optimizer, and the device includes:

[0018] An acquisition module, used for acquiring an initial query instruction, wherein the initial query instruction is an instruction compiled using a structured query language;

[0019] an optimization module, configured to determine, by the optimizer, subquery operators of the at least two subqueries and a subquery connector connecting the at least two subqueries when the initial query instruction includes at least two subqueries;

[0020] The optimization module is further configured to, when the subquery operators of the at least two subqueries are any one of a preset operator set and the subquery connector is or, determine through the optimizer whether the query table items of the at least two subqueries are the same, and when the query table items of the at least two subqueries are the same, generate a first target query instruction, wherein the first target query instruction includes merging the at least two subqueries into a first merged subquery connected by or, and the preset operator set includes operators exists, in, any and some;

[0021] The optimization module is also used for, alternatively, when the subquery operators of the at least two subqueries are both notexes and the subquery connector is and, determining through the optimizer whether the query table items of the at least two subqueries are the same, and when the query table items of the at least two subqueries are the same, generating a second target query instruction, wherein the second target query instruction includes merging the at least two subqueries into a second merged subquery connected by or.

[0022] In a possible implementation, the optimization module is specifically used to: according to the initial query instruction, merge the subquery operators of the at least two subqueries into one exists to obtain the query operator of the first target query instruction, and generate the first target query instruction based on the query operator of the first target query instruction; or, the optimization module is specifically used to: according to the initial query instruction, merge the subquery operators of the at least two subqueries into one not exists to obtain the query operator of the second target query instruction, and generate the second target query instruction based on the query operator of the second target query instruction.

[0023] In a possible implementation, the optimization module is also used to: when the optimizer supports semi-join, perform a semi-join equivalent conversion on the first target query instruction to generate a third target query instruction corresponding to the first target query instruction, and the execution result of the third target query instruction is the same as the execution result of the first target query instruction and the execution result of the initial query instruction.

[0024] In a possible implementation, the optimization module is also used to: when the optimizer supports anti-join, perform an anti-join equivalent conversion on the second target query instruction to generate a fourth target query instruction corresponding to the second target query instruction, and the execution result of the fourth target query instruction and the execution result of the second target query instruction are the same as the execution result of the initial query instruction.

[0025] In a possible implementation, the optimization module is also used to: detect through the optimizer whether the initial query instruction includes a preset target item, and the preset target item includes at least one of a sorting operation, a grouping operation or a target column expression; if it is determined that the initial query instruction includes a preset target item, eliminate the preset target item.

[0026] In one possible implementation, the optimization module is also used to: calculate the execution costs corresponding to the initial query instruction, the first target query instruction, and the third target query instruction respectively through the optimizer, and determine the query instruction corresponding to the smallest execution cost among the execution costs as the target execution instruction for execution; or, calculate the execution costs corresponding to the initial query instruction, the second target query instruction, and the fourth target query instruction respectively through the optimizer, and determine the query instruction corresponding to the smallest execution cost among the execution costs as the target execution instruction for execution.

[0027] In a third aspect, an embodiment of the present application provides an electronic device, comprising: a memory and a processor; the memory stores computer-executable instructions; the processor executes the computer-executable instructions stored in the memory, so that the processor executes the first aspect above and / or various possible implementations of the first aspect.

[0028] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the first aspect above and / or various possible implementations of the first aspect.

[0029] In a fifth aspect, an embodiment of the present application provides a computer program product, including a computer program, which, when executed by a processor, implements the above first aspect and / or various possible implementation methods of the first aspect.

[0030] The query instruction generation method, device, equipment, medium and program product provided by the embodiment of the present application, the method determines the sub-query operator and sub-query connector of the initial query instruction, and when it is determined that the sub-query operator and the sub-query connector meet the corresponding conditions, and when it is determined that the query table items of each sub-query are the same, each sub-query is merged into a first merged sub-query or a second merged sub-query connected by or, and a first target query instruction or a second target query instruction can be generated. Based on this, when the sub-query operator is exists, in, any, some or not exists, and the connector is or or and, the query conditions of each sub-query connected by or or and are merged according to the execution logic of exists, in, any, some and not exists, and based on the first merged sub-query or the second merged sub-query obtained after the merger, the query can be completed by performing a data table scan on the query table item once, so that the number of times the query table items are scanned can be reduced, and then when the first target query instruction or the second target query instruction is executed, the consumption of computing resources when scanning the query table items can be reduced. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0032] Figure 1 A schematic diagram of the process of optimizing and executing query instructions in the relevant method;

[0033] Figure 2 Schematic diagram of the query instruction generation method provided in the embodiment of the present application Figure 1 ;

[0034] Figure 3 Schematic diagram of the query instruction generation method provided in the embodiment of the present application Figure 2 ;

[0035] Figure 4 Schematic diagram of the query instruction generation method provided in the embodiment of the present application Figure 3 ;

[0036] Figure 5 A schematic diagram of the structure of a query instruction generating device provided in an embodiment of the present application;

[0037] Figure 6 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application.

[0038] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0039] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0040] In the embodiments of the present application, words such as "first" and "second" are used to distinguish the same or similar items with substantially the same functions and effects. For example, the first electronic device and the second electronic device are only used to distinguish different electronic devices, and their order is not limited. Those skilled in the art can understand that words such as "first" and "second" do not limit the quantity and execution order, and words such as "first" and "second" do not necessarily limit them to be different.

[0041] In the embodiments of the present application, the words "exemplary" or "for example" are used to indicate examples, illustrations or descriptions. Any embodiment or design described as "exemplary" or "for example" in the present application should not be interpreted as being more preferred or more advantageous than other embodiments or designs. Specifically, the use of words such as "exemplary" or "for example" is intended to present related concepts in a specific way.

[0042] In the embodiments of the present application, "at least one" means one or more, and "more" means two or more. "And / or" describes the association relationship of the associated objects, indicating that there can be three relationships. For example, A and / or B can mean: A exists alone, A and B exist at the same time, and B exists alone, where A and B can be singular or plural. The character " / " generally indicates that the associated objects before and after are in an "or" relationship.

[0043] In the technical solutions of the embodiments of the present application, the collection, storage, use, processing, transmission, provision and disclosure of user personal information involved are in compliance with the provisions of relevant laws and regulations and do not violate public order and good morals.

[0044] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0045] Figure 1 The following is a schematic diagram of the process of optimizing and executing query instructions in the relevant method. In the software for operating or managing the database, such as the database system or database management system, there are functional components such as optimizer and executor. Figure 1 As shown, after the query instruction is input into the optimizer, if the query instruction is a complex query instruction including a complex SQL statement, then if the optimizer supports the CTE optimization method, the optimizer will convert the complex query instruction into a new query instruction including CTE, formulate an execution plan based on the new query instruction including CTE, and indicate the execution plan to the executor, so that the executor executes the query instruction according to the execution plan and outputs the execution result.

[0046] For example, the query command input is "Select*from t1 where a in(super complex SQL where a<10)or b in(super complex SQL where a<5)". Select means selection; from means from, and the value following from is the query table item, which can be understood as the object to be queried; t1 means data table t1, and the query table item in this query command is data table t1. "Select*from t1" means selecting the results that meet the filtering conditions from data table t1.

[0047] Where is a symbol for filtering conditions, and the statement after where indicates the specific filtering conditions. "a in (super complex SQL where a<10)" and "b in (super complex SQL where a<5)" are two filtering conditions, where a and b represent the query columns, respectively. These two filtering conditions can also be understood as two subqueries. A subquery can be understood as a query nested in other queries, or as a query nested in an inner layer; the nested outer query can be understood as the main query corresponding to the subquery. A subquery can also be called a sublink, a subconnection, or an inner query, etc., and the main query can also be called the parent query of the subquery or an outer query, etc.

[0048] in means in, and is a query operator that is usually used in subqueries to check whether a value is in a set of values. "Super complex SQL" is a shorthand for indicating that this is a long or complex SQL statement. "a<10" means that the value in column a of the queried data table item is less than 10, and "a<5" means that the value in column a of the queried data table item is less than 5. If the executor directly executes the complex query instruction, it will scan the data table corresponding to the query table item based on the subquery "a in (super complex SQL where a<10)", and then scan the data table corresponding to the query table item again based on the subquery "b in (super complex SQL where a<5)". There is a problem of repeatedly scanning the same query table item, resulting in more computing resources being consumed when scanning the data table.

[0049] After the optimizer converts the input query instruction "Select*from t1 where a in(super complex SQL where a<10)or b in(super complex SQL where a<5);", the converted query instruction is "WITH CTEas(super complex SQL)Select*from t1 where a in(select...from CTE where a<10)or bin(select...from CTE where a<5);". Compared with the query instruction before conversion, the main thing is to add a line of SQL statement "WITH CTE as(super complex SQL)". This SQL statement can be understood as generating a CTE public table based on (super complex SQL), where WITH means having and as means as. The CTE public table can be understood as an intermediate set, which can be used to improve the execution efficiency when executing new query instructions.

[0050] In addition to the newly added SQL statement, other statements are adaptively modified to adapt to CTE. When the executor executes the converted query instruction including CTE, it will still scan the data table corresponding to the query table item based on the subquery "a in (select...fromCTE where a<10)", and scan the data table corresponding to the query table item again based on the subquery "b in (select...from CTEwhere a<5)". Therefore, compared with the query instruction before the conversion, the new query instruction generated by executing this method does not reduce the number of scans for the same query table item, and the problem of repeated scanning still exists, which cannot reduce the consumption of computing resources.

[0051] In addition, for some query instructions with lower complexity, there is also a problem of performing repeated scanning on the same query table items in multiple sub-queries.

[0052] For example, when the input query instruction is: "Select*from t1 where a in(select a fromt2 where a<10)or b in(select b from t2 where a<5);", the data table t2 will be scanned once based on the query table item t2 in the subquery "a in(select a from t2where a<10)"; the data table t2 will also be scanned once based on the query table item t2 in the subquery "b in(select bfrom t2 where a<5)", and the data table t2 will be scanned repeatedly.

[0053] For another example, when the query instruction is "Select*from t1 where a not in(select afrom t2 where a<10)and b not in(select b from t2 where a<5);", the data table t2 will be scanned once based on the query table item t2 in the subquery "a not in(select afrom t2 where a<10)"; the data table t2 will also be scanned once based on the query table item t2 in the subquery "b not in(select b from t2 where a<5)", and the data table t2 may be scanned repeatedly. The "not" in the query instruction means "not", and "notin" can be understood as "not in".

[0054] It can be seen that in normal database execution, the query table items are referenced and executed multiple times according to the sub-queries. Although the query table items of each sub-query in a query instruction are consistent, there is no direct interaction between each execution round when executing each sub-query, which will lead to repetitive scanning actions and consume too much computing resources of the electronic device.

[0055] In practice, the inventors analyzed the execution logic of the subquery operators not exists, exists, in, any or some, and found that the subqueries corresponding to these subquery operators can be merged when connected with and or or, which can not only obtain the correct execution results, but also avoid repeated scanning of the same query table items, thereby reducing the consumption of computing resources. Among them, not exists means not exists, exists means exists, in means in, any means any, some means some, and means and, or means or.

[0056] In view of this, an embodiment of the present application provides a query instruction generation method, which can determine the subquery operator and subquery connector in the query instruction through an optimizer, and when the subquery operator is determined to be any one of exists, in, any or some and each subquery is connected by or; or when the subquery operator is determined to be not exists and each subquery is connected by and; further determine that when the query table items of each subquery are the same, each subquery can be merged to obtain a merged subquery connected by or, and then a new query instruction can be generated. Since the execution logic of exists, in, any, some and not exists each supports the equivalent merging of query instructions in the above cases, the new query instruction including the merged subquery can reduce the number of scans of the same query table item without changing the execution result, thereby reducing the consumption of computing resources when scanning the query table items.

[0057] The technical solution of the present application is described in detail with specific embodiments below. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.

[0058] Figure 2 Schematic diagram of the query instruction generation method provided in the embodiment of the present application Figure 1 The execution subject of the method may be an electronic device with corresponding data storage and computing capabilities, such as a computer, a server or a server cluster. The electronic device includes an optimizer, which can be understood as a component or module in a database system or database management system that can process query instructions, optimize query instructions and formulate corresponding execution plans. The optimizer can be a software module composed of a computer program. Figure 2 As shown, the method includes:

[0059] S201, obtaining an initial query instruction, where the initial query instruction is an instruction compiled using a structured query language.

[0060] Exemplarily, the structured query language is SQL, and the query instruction may also be referred to as an SQL instruction or an SQL query instruction, etc. The initial query instruction may be understood as the query instruction based on the original input when generating a new query instruction. The initial query instruction may be manually written by a user using SQL, or may be generated by automatically generating SQL instructions or software that transcribes SQL in other programming languages.

[0061] S202: When the initial query instruction includes at least two sub-queries, determine, by an optimizer, sub-query operators for the at least two sub-queries and a sub-query connector for connecting the at least two sub-queries.

[0062] Exemplarily, the initial query instruction may be detected by a code preset in the optimizer to determine whether it includes at least two sub-queries. In the case where the initial query instruction includes at least two sub-queries, the sub-query operators of the at least two sub-queries and the sub-query connector connecting the at least two sub-queries may be detected and determined by the optimizer.

[0063] The subquery operator can be understood as an operator used in a subquery, which is used to compare or process the results returned by the subquery, and the subquery operator is, for example, all, exists, in, any, or some, where all means all. The subquery connector that connects at least two subqueries can be understood as a symbol that connects any two adjacent subqueries, such as or or and.

[0064] S203, when the subquery operators of at least two subqueries are any one of a preset operator set and the subquery connector is or, determine through the optimizer whether the query table items of the at least two subqueries are the same, and when the query table items of the at least two subqueries are the same, generate a first target query instruction, the first target query instruction includes merging the at least two subqueries into a first merged subquery connected by or, and the preset operator set includes operators exists, in, any and some.

[0065] Exemplarily, the preset operator set may be a preset set including multiple subquery operators. In an embodiment of the present application, the preset operator set includes operators exists, in, any, and some. Among them, exists is used to check whether the subquery returns any rows; in is used to check whether a value is in a set of values; any and some are synonyms with the same function, which are used to compare a value with any one of a set of values ​​returned by the subquery. The query connector or can be understood as an or connection relationship.

[0066] The query table item can be any queried object, and the query table item of the subquery can be understood as the queried object in the subquery. It should be noted that for the subquery and the main query corresponding to the subquery, the query table item of the subquery can be understood as the query object of the inner query, and the query table item of the main query can be understood as the query object of the outer query. The query table item can be a data table, or the result obtained after executing the SQL related to the subquery, or it can also be other queried objects.

[0067] The query table items of at least two sub-queries represent the query table items of each sub-query included in the initial query instruction. When the optimizer determines whether the query table items of at least two sub-queries are the same, the optimizer can identify the query table items of each sub-query respectively, and compare the identified query table items for consistency, so as to determine whether the query table items are the same. For example, for each sub-query, the value after from in the sub-query is extracted, and the value after from of each sub-query is compared for consistency, so as to determine whether the query table items are the same. If it is determined that the query table items are consistent, it can also be understood that these sub-queries are sub-links of the same type.

[0068] The first target query instruction can be understood as a query instruction generated by merging the subqueries when the initial query instruction satisfies that the subquery operator of each subquery is any one of exists, in, any, or some, and the subqueries are connected by or. The first target query instruction includes merging the subqueries into a first merged subquery connected by or. The first merged subquery can be understood as a subquery generated by merging the subqueries when the initial query instruction satisfies that the subquery operator of each subquery is any one of exists, in, any, or some, and the subqueries are connected by the connector or.

[0069] In a possible implementation, when generating the first target query instruction, specifically: according to the initial query instruction, merge the sub-query operators of at least two sub-queries into one exists to obtain the query operator of the first target query instruction, and generate the first target query instruction based on the query operator of the first target query instruction.

[0070] For example, the subquery execution logic followed by the operator exists can be understood as only caring whether the query table item has data that meets the filtering condition. Therefore, the query instruction in the exists or exists format can be equivalent to the format of exists(...or...).

[0071] For the operators exists, in, any, and some, if the query table items of the inner query and the query table items of the outer query have data that meets the filtering conditions, the result true will be returned, and true means true. Therefore, the operators exists, in, any, and some can be combined with each other in each subquery. In addition, according to the writing rules of SQL, in, any, and some can be rewritten as exists. Therefore, when generating the first target query instruction, the subquery operators of each subquery can be merged into one exists, and the exists is determined as the query operator of the first target query instruction. After obtaining the query operator of the first target query instruction, the subqueries connected by or in the initial query instruction can be merged into a first merged subquery connected by or.

[0072] For example, assume that the initial query instruction obtained is "Select*from t1 where Exists(select1from t2 where t1.a=t2.a)or Exists(select 1from t2 where t1.b=t2.b);", where, according to from t1, it can be known that the query table item of the outer query is data table t1, and the two inner queries connected by or after the main query where are subqueries "Exists(select 1from t2 where t1.a=t2.a)" and "Exists(select 1from t2 where t1.b=t2.b)". It can be seen that the subquery operators of the two subqueries are both exists, and the two subqueries are connected by the subquery connector or; further, the values ​​after the from of the two subqueries are both t2, so it can be determined that the query table items of the two subqueries are both data table t2. Therefore, the subquery operators of the two subqueries can be merged into an exists as the query operator of the first target query instruction, and the two subqueries can be merged into a first merged subquery "select 1from t2 where t1.a=t2.a or t1.b=t2.b" connected by or. Based on this, the first target query instruction "Select*from t1 whereExists(select 1from t2 where t1.a=t2.a or t1.b=t2.b);" corresponding to the initial query instruction can be obtained.

[0073] It should be noted that, for example, in the subquery "Exists(select 1from t2 where t1.a=t2.a)", the 1 in select 1 is a simplified SQL statement. In actual application scenarios, the 1 can be any simple or complex SQL statement. The following embodiments are similar to this and will not be repeated here.

[0074] For another example, assume that the initial query instruction obtained is "Select*from t1 where A in(select afrom t2)or B in(select b from t2);" wherein the two inner queries connected by or after the main query where are the subqueries "Ain(select a from t2)" and "B in(select b from t2)". It can be seen that the subquery operators of the two subqueries are both in, and the two subqueries are connected by the subquery connector or; further, the values ​​after the from of the two subqueries are both t2, so it can be determined that the query table items of the two subqueries are both data table t2, therefore, the subquery operators of the two subqueries can be merged into an exists as the query operator of the first target query instruction, and the two subqueries can be merged into a first merged subquery "select 1from t2 wheret1.a=t2.a or t1.b=t2.b" connected by or. Based on this, the first target query instruction “Select*from t1 where Exists(select 1from t2 where t1.a=t2.a or t1.b=t2.b);” corresponding to the initial query instruction can be obtained.

[0075] For another example, assume that the initial query instruction obtained is "Select*from t1 where A<any(select afrom t2)or B>any(select b from t2);" wherein the two inner queries connected by or after the main query where are the subqueries "A<any(select a from t2)" and the subquery "B>any(select b from t2)". It can be seen that the subquery operators of the two subqueries are both any, and the two subqueries are connected by the subquery connector or; further, the values ​​after the from of the two subqueries are both t2, then it can be determined that the query table items of the two subqueries are both data table t2, therefore, the subquery operators of the two subqueries can be merged into an exists as the query operator of the first target query instruction, and the two subqueries can be merged into a first merged subquery "select 1fromt2 where t1.a<t2.a or t1.b>t2.b" connected by or. Based on this, the first target query instruction "Select*from t1 where Exists(select 1from t2 where t1.a<t2.a ort1.b>t2.b);" corresponding to the initial query instruction can be obtained. In addition, since some and any can be replaced with each other, the query instruction in the some or some format can also be equivalent to the exists(...or...) format.

[0076] For example, the operators exists, in, any, and some can be combined with each other and eventually converted into exists sub-links for unified processing, that is, the format of supports exists or any or some or in is equivalent to the format of exists(…or…or…or…).

[0077] From the above examples, it can be seen that the generated first target query instruction only includes a merged sub-query after merging. When querying based on the merged sub-query, the number of times the query table items are scanned can be reduced, thereby reducing the consumption of computing resources when scanning the query table items. In this embodiment, when generating the first target query instruction, the sub-query operators of all sub-queries are merged into one exists, and exists, in, any and some can be converted to a unified format, which is convenient for the optimizer to generate query instructions with unified standards, and is convenient for unifying the internal execution logic and code implementation of the optimizer, thereby improving the applicability of this method.

[0078] S204, or, when the subquery operators of at least two subqueries are both not exists and the subquery connector is and, determine through the optimizer whether the query table items of the at least two subqueries are the same, and when the query table items of the at least two subqueries are the same, generate a second target query instruction, the second target query instruction including merging the at least two subqueries into a second merged subquery connected by or.

[0079] For example, not exists means negating exists. If the query table items of the inner query and the query table items of the outer query do not contain data that meets the filtering conditions, the result true will be returned. The subquery connector and can be understood as an AND connection relationship.

[0080] In this step, the query table items of at least two sub-queries can refer to the description in the above step S203 embodiment. The optimizer can also determine whether the query table items of at least two sub-queries are the same in a similar manner to the above step S203 embodiment, which will not be repeated here.

[0081] The second target query instruction can be understood as a query instruction generated by merging the subqueries when the initial query instruction satisfies that the subquery operators of each subquery are not exists and the subqueries are connected by and. The second target query instruction includes merging the subqueries into a second merged subquery connected by or. The second merged subquery can be understood as a subquery generated by merging the subqueries when the initial query instruction satisfies that the subquery operators of each subquery are not exists and the subqueries are connected by and.

[0082] In one possible implementation, when generating the second target query instruction, specifically: according to the initial query instruction, merge the sub-query operators of at least two sub-queries into one not exists to obtain the query operator of the second target query instruction, and generate the second target query instruction based on the query operator of the second target query instruction.

[0083] Exemplarily, in the initial query instruction, if the subquery operator of each subquery is not exists, and each subquery is connected by the subquery connector and, then according to the execution logic of not exists, the query instruction in the format of not exists and not exists can be equivalent to the format of not (exists or exists), or to the format of not exists (…or…).

[0084] For example, assume that the initial query instruction obtained is "Select*from t1 where Not exists(select 1from t2 where t1.a=t2.a)and Not exists(select 1from t2 where t1.b=t2.b);", where, according to from t1, it can be known that the query table item of the outer query is data table t1, and the two inner queries connected by and after the main query where are subqueries "Not exists(select 1from t2 where t1.a=t2.a)" and subquery "Not exists(select1from t2 where t1.b=t2.b)". It can be seen that the subquery operators of the two subqueries are both not exists, and the two subqueries are connected by the subquery connector or. Furthermore, the values ​​after the from in the two subqueries are both t2, so it can be determined that the query table items of the two subqueries are both data table t2. Therefore, the subquery operators of the two subqueries can be merged into a not exists as the query operator of the second target query instruction, and the two subqueries can be merged into a second merged subquery "select 1from t2 wheret1.a=t2.a or t1.b=t2.b" connected by or. Based on this, the second target query instruction "Select*from t1 where Not exists(select 1from t2 where t1.a=t2.a or t1.b=t2.b);" corresponding to the initial query instruction can be obtained.

[0085] From the above example, it can be seen that the generated second target query instruction only includes a merged sub-query after merging. When querying based on the merged sub-query, the number of times the query table items are scanned can be reduced, thereby reducing the consumption of computing resources when scanning the query table items. In this embodiment, when generating the second target query instruction, the sub-query operators of all sub-queries are merged into one not exists, which can quickly merge all sub-queries, improve the optimization efficiency of the optimizer, facilitate the unification of the internal execution logic and code implementation of the optimizer, and improve the applicability of this method.

[0086] The query instruction generation method provided by the embodiment of the present application determines the subquery operator and the subquery connector of the initial query instruction. When it is determined that the subquery operator and the subquery connector meet the corresponding conditions and when it is determined that the query table items of each subquery are the same, each subquery is merged into a first merged subquery or a second merged subquery connected by or, and a first target query instruction or a second target query instruction can be generated. Based on this, when the subquery operator is exists, in, any, some or not exists and the connector is or or and, the query conditions of each subquery connected by or or and are merged according to the execution logic of exists, in, any, some and not exists respectively. Based on the first merged subquery or the second merged subquery obtained after the merger, the query can be completed by performing a data table scan on the query table item once, so that the number of times the query table items are scanned can be reduced, and then when the first target query instruction or the second target query instruction is executed, the consumption of computing resources when scanning the query table items can be reduced.

[0087] Exemplarily, in order to further generate new query instructions to improve the optimization performance of the optimizer, the first target query instruction or the second target query instruction may be equivalently converted by using a conversion connection.

[0088] In a possible implementation, the method further includes: when the optimizer supports semi-join, performing a semi-join equivalent conversion on the first target query instruction to generate a third target query instruction corresponding to the first target query instruction, and the execution result of the third target query instruction is the same as the execution result of the first target query instruction and the execution result of the initial query instruction.

[0089] For example, a semi join is a join operation that returns rows from one table that match at least one row in another table under the join condition, but only returns columns from the first table. Semi joins can be used to optimize existence checks, such as exists subqueries.

[0090] For an optimizer that can support transcribing query instructions in a semi-join manner, the optimizer can be used to perform an equivalent conversion on the first target query instruction, and the first target query instruction can be transcribed into a third target query instruction including a semi-join. Since the first target query instruction is an equivalent conversion of the initial query instruction, and the third target query instruction is an equivalent conversion of the first target query instruction, the execution result of the third target query instruction and the execution result of the first target query instruction are the same as the execution result of the initial query instruction.

[0091] For example, the first target query instruction "Select * from t1 where Exists (select1 from t2 where t1.a = t2.a or t1.b = t2.b);" can be equivalently converted into a third target query instruction "Select * from t1 semi join t2 on (t1.a = t2.a or t1.b = t2.b);" by the optimizer. Based on the third target query instruction, a semi-join operation can be performed on the query table items, which can reduce the number of scans on the query table items.

[0092] For another example, the first target query instruction "Select*from t1 where Exists(select 1fromt2 where t1.a<t2.a or t1.b>t2.b);" can be equivalently converted into a third target query instruction "Select*from t1 semi join t2 on(t1.a<t2.a or t1.b>t2.b);" by the optimizer. Based on the third target query instruction, a semi-join operation can be performed on the query table items, which can reduce the number of scans on the query table items.

[0093] In this embodiment, a semi-join is used to perform an equivalent conversion on the first target query instruction whose query operator is exists, so that a query instruction corresponding to a simplified join operation of the first target query instruction can be generated, and then a join operation can be performed on the query table items to reduce the number of scans of the query table items and reduce the consumption of computing resources. In particular, when the amount of data in the query table items is large, the efficiency of the query can be improved.

[0094] In a possible implementation, the method further includes: when the optimizer supports anti-join, performing an anti-join equivalent conversion on the second target query instruction to generate a fourth target query instruction corresponding to the second target query instruction, and the execution result of the fourth target query instruction is the same as the execution result of the second target query instruction and the execution result of the initial query instruction.

[0095] For example, an anti-join is a join operation opposite to a semi-join, and an anti-join returns rows in one data table that do not match any rows in another data table under the join condition. An anti-join can be used to optimize non-existence checks, such as a not exists subquery.

[0096] For an optimizer that can support transcribing query instructions in an anti-join manner, the optimizer can be used to perform an equivalent conversion on the second target query instruction, and the second target query instruction can be transcribed into a fourth target query instruction including an anti-join. Since the second target query instruction is an equivalent conversion of the initial query instruction, and the fourth target query instruction is an equivalent conversion of the second target query instruction, the execution result of the fourth target query instruction is the same as the execution result of the second target query instruction and the execution result of the initial query instruction.

[0097] For example, the second target query instruction "Select * from t1 where Not exists (select1 from t2 where t1.a = t2.a or t1.b = t2.b);" can be equivalently converted into a fourth target query instruction "Select * from t1 anti join t2 on (t1.a = t2.a or t1.b = t2.b);" through the optimizer. Based on the fourth target query instruction, an anti-join operation can be performed on the query table items, which can reduce the number of scans on the query table items.

[0098] In this embodiment, an anti-join is used to perform an equivalent conversion on the second target query instruction whose query operator is not exists, so that a query instruction corresponding to a simplified join operation of the second target query instruction can be generated, and then a join operation can be performed on the query table items to reduce the number of scans of the query table items and reduce the consumption of computing resources. In particular, when the amount of data in the query table items is large, the efficiency of the query can be improved.

[0099] Figure 3 Schematic diagram of the query instruction generation method provided in the embodiment of the present application Figure 2 Based on the above embodiments, the present application provides a method for generating a query instruction, such as Figure 3 As shown, after obtaining the initial query instruction, the method further includes the following steps:

[0100] S301, detecting, by an optimizer, whether an initial query instruction includes a preset target item, where the preset target item includes at least one of a sorting operation, a grouping operation, or a target column expression.

[0101] Exemplarily, the preset target item can be understood as a preset detection object item, and the preset target item includes at least one of a sorting operation, a grouping operation, or a target column expression.

[0102] Among them, the sorting operation may be an operation performed by the operator order by, where order means sorting and by means according to. order by can be used to sort the query results, for example, the sorting order can be specified according to one or more columns in the data table. The grouping operation may be an operation performed by the operator group by, where group means grouping. group by can be used to group rows with the same value. The target column expression may be a SQL statement in a preset format, which may be preset in advance according to the actual needs of the database application scenario, for example, it may be a complex target column expression.

[0103] By setting a code for detecting the operator order by, the operator group by or the SQL statement in a preset format in the optimizer's optimizer, it is possible to detect whether the initial query instruction includes the preset target item.

[0104] S302: When it is determined that the initial query instruction includes a preset target item, eliminate the preset target item.

[0105] Exemplarily, when it is determined that the initial query instruction includes a preset target item, the preset target item may be eliminated by an optimization program of the optimizer.

[0106] For example, the necessity of executing the preset target item is determined by the optimizer's optimization program. If it is determined that the preset target item is not necessary for execution and is a redundant target item, the preset target item in the initial query instruction can be deleted; or, based on the execution logic of the preset target item, the initial query instruction is equivalently converted to obtain an initial query instruction that eliminates the preset target item. The embodiment of the present application does not limit the implementation method of eliminating the preset target item.

[0107] In the embodiment of the present application, before generating the first target query instruction or the second target query instruction according to the initial query instruction, the preset target items in the initial query instruction are first eliminated, so that the initial query instruction can be simplified, for example, unnecessary sorting or grouping can be removed. In this way, a concise initial query instruction can be provided for generating the first target query instruction or the second target query instruction, reducing the complexity of the generated first target query instruction or the second target query instruction, and further reducing the computational overhead when executing the first target query instruction or the second target query instruction.

[0108] In one possible implementation, the method further includes: calculating the execution costs corresponding to the initial query instruction, the first target query instruction and the third target query instruction respectively through the optimizer, and determining the query instruction corresponding to the smallest execution cost among the execution costs as the target execution instruction for execution; or, calculating the execution costs corresponding to the initial query instruction, the second target query instruction and the fourth target query instruction respectively through the optimizer, and determining the query instruction corresponding to the smallest execution cost among the execution costs as the target execution instruction for execution.

[0109] Exemplarily, the optimizer can obtain the execution cost corresponding to the query instruction through cardinality estimation and cost estimation of the query instruction, wherein the cardinality estimation can be an estimation of the amount of data involved in executing the query instruction, the cost estimation can be an estimation of the computing overhead of the electronic device involved in executing the query instruction, and the obtained execution cost can be a value used to characterize the execution time required to execute the query instruction.

[0110] When generating the first target query instruction based on the initial query instruction, the initial query instruction and the first target query instruction can be retained, and the third target query instruction corresponding to the first target query instruction can continue to be generated. The optimizer calculates the execution costs corresponding to the initial query instruction, the first target query instruction, and the third target query instruction respectively. After that, the optimizer compares the execution costs corresponding to the initial query instruction, the first target query instruction, and the third target query instruction, and determines the query instruction corresponding to the smallest execution cost as the target execution instruction for execution. The target execution instruction can be understood as a query instruction used to generate an execution plan and instruct the executor to execute it to obtain the final execution result.

[0111] When generating the second target query instruction based on the initial query instruction, the initial query instruction and the second target query instruction can be retained, and the fourth target query instruction corresponding to the second target query instruction can continue to be generated. The optimizer calculates the execution costs corresponding to the initial query instruction, the second target query instruction, and the fourth target query instruction respectively. After that, the optimizer compares the execution costs corresponding to the initial query instruction, the second target query instruction, and the fourth target query instruction, and determines the query instruction corresponding to the smallest execution cost as the target execution instruction for execution. The target execution instruction can be understood as a query instruction used to generate an execution plan and instruct the executor to execute it to obtain the final execution result.

[0112] In an embodiment of the present application, by calculating the execution cost of each query instruction and determining the query instruction corresponding to the minimum execution cost as the target execution instruction, an execution plan with minimum computing overhead can be generated, so that when the executor executes the target execution instruction, the execution result can be obtained quickly, thereby improving the execution efficiency of the query instruction.

[0113] The following is an example of a specific implementation method to illustrate the query instruction generation method provided in the embodiment of the present application.

[0114] Figure 4 Schematic diagram of the query instruction generation method provided in the embodiment of the present application Figure 3 ,like Figure 4 As shown, after obtaining the initial query instruction, the initial query instruction is processed. You can first determine whether the initial query instruction is in the SQL statement format of exists(in / any / some) combined with or, or in the SQL statement format of not exists and and, or in the SQL statement format of exists(...or...), or in the SQL statement format of not exists(...or...). If it is determined to be no, you can exit this method and execute the initial query instruction according to other query instruction execution methods; if it is determined to be yes, the subqueries in the initial query instruction with the subquery operator of in / any / some can be uniformly converted to subqueries with the subquery operator of exists. Afterwards, the query instruction obtained after the conversion can eliminate redundant operations in the subquery of exists, for example, order by, groupby or complex target column expressions in the subquery of exists can be eliminated.

[0115] After the elimination is completed, determine whether the query table items of each sub-query are consistent, that is, whether the query table items are the same. If it is determined to be no, this method can be exited and the initial query instruction can be executed according to other query instruction execution methods; if it is determined to be yes, the execution cost corresponding to the first target query instruction, the second target query instruction, the third target query instruction and / or the fourth target query instruction can be calculated according to the query instruction under the condition of production conditions, and the query instruction corresponding to the minimum execution cost can be generated according to the execution cost, and the query instruction is determined as the target execution instruction for formulating the corresponding execution plan and indicating the subsequent execution steps, etc.

[0116] Exemplarily, when query instructions are generated based on execution costs, the generation of query instructions in a "backward" manner can also be implemented based on the above-mentioned equivalence principle. For example, if the database does not support semi-join or anti-join operations with or conditions, or the data distribution or cost estimation of the data table corresponding to the query table item is more inclined to split logic, it can be "broken into parts", for example, a subquery of exists can be split into two subqueries of exists, that is, a SQL statement in the format of exists(...or...) can be converted into a SQL statement in the format of exists or exists.

[0117] Based on the method provided in the embodiment of the present application, similar subqueries in the initial query instruction can be merged to reduce the performance loss of repeated subquery queries, and the merged sub-joins can also use the optimization of conversion joins to further improve performance, for example, condition simplification or conversion of semi-joins or anti-joins, etc. Alternatively, the sub-queries including the exists of or joins in the initial query instruction can also be split to improve the flexibility of applicable data distribution scenarios.

[0118] Figure 5 A schematic diagram of the structure of a query instruction generating device provided in an embodiment of the present application, such as Figure 5 As shown, an embodiment of the present application provides a query instruction generating device, which is applied to an electronic device, and the electronic device includes an optimizer, and the device includes:

[0119] The acquisition module 501 is used to acquire an initial query instruction, where the initial query instruction is an instruction compiled using a structured query language;

[0120] An optimization module 502, configured to determine, by an optimizer, subquery operators of the at least two subqueries and a subquery connector connecting the at least two subqueries when the initial query instruction includes at least two subqueries;

[0121] The optimization module 502 is further configured to determine, through an optimizer, whether query table items of at least two subqueries are the same when the subquery operators of at least two subqueries are any one of a preset operator set and the subquery connector is or, and to generate a first target query instruction when the query table items of at least two subqueries are the same, wherein the first target query instruction includes merging the at least two subqueries into a first merged subquery connected by or, and the preset operator set includes operators exists, in, any, and some;

[0122] The optimization module 502 is also used for, or, when the subquery operators of at least two subqueries are both not exists and the subquery connector is and, determining through the optimizer whether the query table items of at least two subqueries are the same, and when the query table items of at least two subqueries are the same, generating a second target query instruction, the second target query instruction including merging the at least two subqueries into a second merged subquery connected by or.

[0123] In one possible implementation, the optimization module 502 is specifically used to: according to the initial query instruction, merge the subquery operators of at least two subqueries into one exists to obtain the query operator of the first target query instruction, and generate the first target query instruction based on the query operator of the first target query instruction; or, the optimization module 502 is specifically used to: according to the initial query instruction, merge the subquery operators of at least two subqueries into one not exists to obtain the query operator of the second target query instruction, and generate the second target query instruction based on the query operator of the second target query instruction.

[0124] In a possible implementation, the optimization module 502 is also used to: when the optimizer supports semi-join, perform a semi-join equivalent conversion on the first target query instruction to generate a third target query instruction corresponding to the first target query instruction, and the execution result of the third target query instruction is the same as the execution result of the first target query instruction and the execution result of the initial query instruction.

[0125] In a possible implementation, the optimization module 502 is also used to: when the optimizer supports anti-join, perform an anti-join equivalent conversion on the second target query instruction to generate a fourth target query instruction corresponding to the second target query instruction, and the execution result of the fourth target query instruction is the same as the execution result of the second target query instruction and the execution result of the initial query instruction.

[0126] In a possible implementation, the optimization module 502 is also used to: detect through the optimizer whether the initial query instruction includes a preset target item, the preset target item includes at least one of a sorting operation, a grouping operation or a target column expression; if it is determined that the initial query instruction includes a preset target item, eliminate the preset target item.

[0127] In one possible implementation, the optimization module 502 is also used to: calculate the execution costs corresponding to the initial query instruction, the first target query instruction, and the third target query instruction respectively through the optimizer, and determine the query instruction corresponding to the smallest execution cost among the execution costs as the target execution instruction for execution; or, calculate the execution costs corresponding to the initial query instruction, the second target query instruction, and the fourth target query instruction respectively through the optimizer, and determine the query instruction corresponding to the smallest execution cost among the execution costs as the target execution instruction for execution.

[0128] The query instruction generation device provided in the embodiment of the present application can be used to execute the technical solution of the query instruction generation method in any of the above embodiments of the present application. Its implementation principle and technical effect are similar, and this embodiment will not be repeated here.

[0129] Figure 6A schematic diagram of the structure of an electronic device provided in an embodiment of the present application is shown in FIG. Figure 6 As shown, the electronic device of this embodiment may include: at least one processor 601; and a memory 602 communicatively connected to the at least one processor; wherein the memory 602 stores instructions executable by the at least one processor 601, and the instructions are executed by the at least one processor 601 so that the electronic device executes a method as in any of the above embodiments.

[0130] Optionally, the memory 602 may be independent or integrated with the processor 601 .

[0131] The implementation principle and technical effects of the electronic device provided in this embodiment can be found in the aforementioned embodiments and will not be described in detail here.

[0132] An embodiment of the present application further provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions. When a processor executes the computer-executable instructions, the method described in any of the above embodiments is implemented.

[0133] An embodiment of the present application further provides a computer program product, including a computer program, which implements the method described in any of the above embodiments when executed by a processor.

[0134] In the several embodiments provided in this application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic, for example, the division of the modules is only a logical function division, and there may be other division methods in actual implementation, such as multiple modules can be combined or integrated into another system, or some features can be ignored or not executed.

[0135] The above-mentioned integrated module implemented in the form of a software function module can be stored in a computer-readable storage medium. The above-mentioned software function module is stored in a storage medium, including a number of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) or a processor to perform some steps of the method described in each embodiment of the present application.

[0136] It should be understood that the above-mentioned processor can be a processing unit (Central Processing Unit, CPU), or other general-purpose processors, digital signal processors (Digital Signal Processor, DSP), application-specific integrated circuits (Application Specific Integrated Circuit, ASIC), etc. The general-purpose processor can be a microprocessor or the processor can also be any conventional processor, etc. The steps of the method disclosed in the application can be directly embodied as being executed by a hardware processor, or can be executed by a combination of hardware and software modules in the processor. The memory may include random access memory (Random Access Memory, RAM), and may also include non-volatile memory (Non-Volatile Memory, NVM), such as at least one disk storage, and can also be a USB flash drive, a mobile hard disk, a read-only memory, a disk or an optical disk, etc.

[0137] The above storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read only memory (EEPROM), erasable programmable read only memory (EPROM), programmable read only memory (PROM), read only memory (ROM), magnetic memory, flash memory, magnetic disk or optical disk. The storage medium can be any available medium that can be accessed by a general or special-purpose computer.

[0138] An exemplary storage medium is coupled to a processor so that the processor can read information from the storage medium and write information to the storage medium. Of course, the storage medium can also be a component of the processor. The processor and the storage medium can be located in a dedicated integrated circuit. Of course, the processor and the storage medium can also exist as discrete components in an electronic device or a main control device.

[0139] It should be noted that, in this article, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, an element defined by the sentence "comprises a ..." does not exclude the existence of other identical elements in the process, method, article or device including the element.

[0140] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0141] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for a terminal device (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in each embodiment of the present application.

[0142] The above are only preferred embodiments of the present application, and are not intended to limit the patent scope of the present application. Any equivalent structure or equivalent process transformation made using the contents of the present application specification and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present application.

[0143] Those skilled in the art will readily come up with other implementations of the embodiments of the present application after considering the specification and practicing the application disclosed herein. The present application is intended to cover any modifications, uses or adaptations of the embodiments of the present application, which follow the general principles of the embodiments of the present application and include common knowledge or customary technical means in the art that are not disclosed in the embodiments of the present application.

[0144] It should be understood that the embodiments of the present application are not limited to the precise structures described above and shown in the drawings, and various modifications and changes can be made without departing from the scope thereof. The scope of the embodiments of the present application is limited only by the appended claims.

Claims

1. A query instruction generation method, characterized in that: Applied to an electronic device, the electronic device includes an optimizer, and the method includes: Obtaining an initial query instruction, wherein the initial query instruction is an instruction compiled using a structured query language; In a case where the initial query instruction includes at least two sub-queries, determining, by the optimizer, sub-query operators of the at least two sub-queries and a sub-query connector connecting the at least two sub-queries; When the subquery operators of the at least two subqueries are any one of a preset operator set and the subquery connector is or, the optimizer determines whether the query table items of the at least two subqueries are the same, and when the query table items of the at least two subqueries are the same, generates a first target query instruction, wherein the first target query instruction includes merging the at least two subqueries into a first merged subquery connected by or, and the preset operator set includes operators exists, in, any, and some; Alternatively, when the subquery operators of the at least two subqueries are both not exists and the subquery connector is and, the optimizer determines whether the query table items of the at least two subqueries are the same, and when the query table items of the at least two subqueries are the same, generates a second target query instruction, wherein the second target query instruction includes merging the at least two subqueries into a second merged subquery connected by or.

2. The method according to claim 1, characterized in that The generating of the first target query instruction comprises: According to the initial query instruction, the sub-query operators of the at least two sub-queries are merged into one exists to obtain the query operator of the first target query instruction, and based on the query operator of the first target query instruction, the first target query instruction is generated; or, The generating of the second target query instruction comprises: According to the initial query instruction, the sub-query operators of the at least two sub-queries are merged into one notexes to obtain the query operator of the second target query instruction, and based on the query operator of the second target query instruction, the second target query instruction is generated.

3. The method according to claim 2, characterized in that The method further comprises: In the case where the optimizer supports semi-join, a semi-join equivalent conversion is performed on the first target query instruction to generate a third target query instruction corresponding to the first target query instruction, and an execution result of the third target query instruction is the same as the execution result of the first target query instruction and the execution result of the initial query instruction.

4. The method according to claim 2, characterized in that: The method further comprises: In the case where the optimizer supports anti-join, an anti-join equivalent conversion is performed on the second target query instruction to generate a fourth target query instruction corresponding to the second target query instruction, and the execution result of the fourth target query instruction and the execution result of the second target query instruction are the same as the execution result of the initial query instruction.

5. The method according to any one of claims 1 to 4, characterized in that: After obtaining the initial query instruction, the method further includes: Detecting, by the optimizer, whether the initial query instruction includes a preset target item, wherein the preset target item includes at least one of a sorting operation, a grouping operation, or a target column expression; In the case where it is determined that the initial query instruction includes a preset target item, the preset target item is eliminated.

6. The method according to claim 3 or 4, characterized in that: The method further comprises: The optimizer calculates the execution costs corresponding to the initial query instruction, the first target query instruction, and the third target query instruction respectively, and determines the query instruction corresponding to the smallest execution cost among the execution costs as the target execution instruction for execution; Alternatively, the optimizer calculates the execution costs corresponding to the initial query instruction, the second target query instruction and the fourth target query instruction respectively, and determines the query instruction corresponding to the smallest execution cost among the execution costs as the target execution instruction for execution.

7. A query instruction generating device, characterized in that: Applied to an electronic device, the electronic device includes an optimizer, and the device includes: An acquisition module, used for acquiring an initial query instruction, wherein the initial query instruction is an instruction compiled using a structured query language; an optimization module, configured to determine, by the optimizer, subquery operators of the at least two subqueries and a subquery connector connecting the at least two subqueries when the initial query instruction includes at least two subqueries; The optimization module is further configured to, when the subquery operators of the at least two subqueries are any one of a preset operator set and the subquery connector is or, determine through the optimizer whether the query table items of the at least two subqueries are the same, and when the query table items of the at least two subqueries are the same, generate a first target query instruction, wherein the first target query instruction includes merging the at least two subqueries into a first merged subquery connected by or, and the preset operator set includes operators exists, in, any and some; The optimization module is also used for, alternatively, when the subquery operators of the at least two subqueries are both not exists and the subquery connector is and, determining through the optimizer whether the query table items of the at least two subqueries are the same, and when the query table items of the at least two subqueries are the same, generating a second target query instruction, wherein the second target query instruction includes merging the at least two subqueries into a second merged subquery connected by or.

8. An electronic device, characterized in that: include: Memory and processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory, so that the processor performs the method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 6 when executed by a processor.

10. A computer program product, characterized in that The method comprises a computer program, which implements the method according to any one of claims 1 to 6 when being executed by a processor.

Images