Connection conversion method and device of query statement, equipment and storage medium
By confirming whether the query statement is a duplicate value is insensitive and rewriting its internal connection, the problem of excessive data volume caused by internal connection operations is solved and data processing efficiency is improved.
Patent Information
- Application Number
- CN202411959741.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-12-27
- Publication Date
- 2025-05-02
AI Technical Summary
The query statements including intra-connection operations in the prior art cause excessive amount of data in the result set, thereby reducing the efficiency of subsequent data processing.
By obtaining the query statement of the database, confirm whether it is a query statement with insensitive duplicate values. If so, adjust the connection method between tables, rewritten from the inner join to a semi-join or an inverse join, generate a new query statement, and execute the statement to reduce the number of duplicate values.
The number of duplicate values in the query result is reduced through the rewritten connection method, thereby reducing the amount of data in the result set and improving the efficiency of subsequent data processing.
Smart Images
Figure CN119917541A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of database technology, and in particular to a query statement connection conversion method, device, equipment and storage medium. Background Art
[0002] The query statement of the database includes various query conditions. When the query device executes the query statement, it can judge whether the data in the query table meets the query conditions based on specific matching rules for the data in other tables, and finally query to obtain the data that meets the conditions.
[0003] In the prior art, query conditions in some query statements need to associate data in the table to be queried with the query. The connection relationship between the table to be queried and other tables established in such a query statement is called an inner join operation.
[0004] With the prior art, when a query device executes a query statement including an inner join operation, a large amount of data will be included in the result set, resulting in low processing efficiency when subsequent data processing is performed based on the result set. Summary of the invention
[0005] The present application provides a query statement connection conversion method, device, equipment and storage medium to solve the technical problem of a large amount of data in a result set including an inner join operation in the prior art.
[0006] A first aspect of the present application provides a query statement connection conversion method, comprising: obtaining a first query statement from a database; when a first table and a second table using an inner join exist in the first query statement, and a target column of the first query statement is a column in the first table, confirming whether the first query statement is a query statement sensitive to duplicate values; if the first query statement is a query statement insensitive to duplicate values, adjusting the connection method between the first table and the second table to obtain a second query statement; executing the second query statement, and the number of duplicate values in the query result of the second query statement is less than the number of duplicate values in the query result of the first query statement.
[0007] In an embodiment of the first aspect of the present application, the confirming whether the first query statement is a query statement sensitive to duplicate values includes: when the type of the first query statement is a first type including a deduplication operation or a grouping operation, or a second type of an aggregate function including a deduplication property, or a third type in a branch of a set operation belonging to deduplication, confirming that the first query statement is a query statement insensitive to duplicate values; the adjusting the connection method of the first table and the second table to obtain the second query statement includes: rewriting the inner join into a semi-join to obtain the second query statement.
[0008] In an embodiment of the first aspect of the present application, the confirming whether the first query statement is a query statement sensitive to duplicate values includes: when the type of the first query statement is a fourth type in which a subquery of the first query statement includes an operator for conditional judgment or associated data, confirming that the first query statement is a query statement insensitive to duplicate values; adjusting the connection method of the first table and the second table to obtain the second query statement includes: rewriting the inner join into a semi-join or an anti-join to obtain the second query statement.
[0009] In an embodiment of the first aspect of the present application, the confirming whether the first query statement is a query statement sensitive to duplicate values includes: when the type of the first query statement is the fifth type of operator including inequality conditions in the subquery of the first query statement, confirming that the first query statement is a query statement insensitive to duplicate values; adjusting the connection method of the first table and the second table to obtain the second query statement includes: rewriting the inner join into an anti-join to obtain the second query statement.
[0010] In an embodiment of the first aspect of the present application, the inner join is rewritten into a semi-join to obtain the second query statement, including: according to the first query statement, searching for the inner join included in the first query statement; generating a subquery that implements the semi-join according to the inner join; deleting the inner join in the first query statement and adding the subquery that implements the semi-join to obtain the second query statement.
[0011] In an embodiment of the first aspect of the present application, the inner join is rewritten into an anti-join to obtain the second query statement, including: according to the first query statement, searching for the inner join included in the first query statement; generating a subquery that implements the anti-join according to the inner join; deleting the inner join in the first query statement and adding the subquery that implements the anti-join to obtain the second query statement.
[0012] A second aspect of the present application provides a query statement connection conversion device, including: an acquisition module, used to acquire a first query statement of a database; a processing module, used to confirm whether the first query statement is a query statement sensitive to duplicate values when there are a first table and a second table using an inner connection in the first query statement, and the target column of the first query statement is a column in the first table; a control module, used to adjust the connection method between the first table and the second table to obtain a second query statement if the first query statement is a query statement insensitive to duplicate values; and an execution module, used to execute the second query statement, the number of duplicate values in the query result of the second query statement being less than the number of duplicate values in the query result of the first query statement.
[0013] The third aspect of the present application provides a query statement connection conversion 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 data query device executes the query statement connection conversion method as described in any one of the first aspects of the present application.
[0014] A fourth aspect of the present application provides a computer-readable storage medium storing computer-executable instructions, which, when executed, implement the query statement connection conversion method as described in any one of the first aspects of the present application.
[0015] A fifth aspect of the present application provides a computer program product, including a computer program, which, when executed, implements the query statement connection conversion method as described in any one of the first aspects of the present application.
[0016] In summary, the connection conversion method, device, equipment and storage medium of the query statement provided in the present application obtain the first query statement of the database, and when the first query statement contains the first table and the second table using inner connection, and the target column of the first query statement is the column in the first table, confirm whether the first query statement is a query statement sensitive to duplicate values. If the first query statement is a query statement insensitive to duplicate values, the connection method of the first table and the second table is adjusted to obtain the second query statement, and the second query statement is executed so that the number of duplicate values in the query result of the second query statement is less than the number of duplicate values in the query result of the first query statement. Since the inner connection is rewritten in the second query statement, the number of duplicate values in the query result is reduced by the rewritten connection method, thereby reducing the amount of data in the result set, thereby improving the processing efficiency of subsequent data processing based on the result set. BRIEF DESCRIPTION OF THE DRAWINGS
[0017] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.
[0018] Figure 1 A schematic diagram of the application scenario of this application;
[0019] Figure 2 A flowchart of a query statement connection conversion method provided in an embodiment of the present application;
[0020] Figure 3A flowchart of another query statement connection conversion method provided in an embodiment of the present application;
[0021] Figure 4 A flowchart of another query statement connection conversion method provided in an embodiment of the present application;
[0022] Figure 5 A flowchart of a connection conversion method for another query statement provided in an embodiment of the present application;
[0023] Figure 6 A flowchart of a connection conversion method for another query statement provided in an embodiment of the present application;
[0024] Figure 7 A flowchart of a connection conversion method for another query statement provided in an embodiment of the present application;
[0025] Figure 8 A schematic diagram of a connection conversion device for a query statement provided by the present application;
[0026] Fig. 9 A structural diagram of a connection conversion device for a query statement provided in this application. DETAILED DESCRIPTION
[0027] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.
[0028] The terms "first", "second", "third", "fourth", etc. (if any) in the specification and claims of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein, for example. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device comprising a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.
[0029] Figure 1 This is a schematic diagram of the application scenario of this application, such as Figure 1As shown, the present application is applied in the technical field of databases. The database 20 may be a device that can be used to store data, such as an SQL database, an Oracle database, a Kingbase database, etc., and the present application embodiment does not limit the type of the database 20. The electronic device 10 may be a computer, a server, a workstation, etc., and the electronic device 10 may communicate with the database 20.
[0030] In one application scenario, a user may send a data query statement to the electronic device 10, and the data query statement is used to query the database 20 for data that meets the conditions. The electronic device 10 then obtains data from the database 20 according to the data query statement and returns the data to the user who needs to query the data.
[0031] In recent years, with the development of new database application scenarios such as mobile Internet, big data, artificial intelligence, and new database hardware technologies, the scale and complexity of databases have increased significantly, the amount of data that needs to be processed has become larger and larger, and the efficiency of devices in database management systems when processing query statements has become more and more serious.
[0032] The inventor of the present application found that when the electronic device 10 executes some query statements including inner join operations (when the query is not sensitive to duplicate values, such as in queries with deduplication operations or sub-links), and the query statements are not sensitive to duplicate values (such as in queries with deduplication operations or sub-links), it is necessary to process the data between two tables (such as the first table t1 and the second table t2) stored in the database 20. For example, when the electronic device 10 executes the query statement select * from t1 inner join t2 on t1.pk = t2.pk, each row of data in t1 will be matched with the data in t2 based on the join condition (t1.pk = t2.pk).
[0033] Due to the characteristics of inner join operations, any row combination that meets the join conditions will be included in the result set, which may result in a large amount of data in the result set. For example, table t1 has m rows of data and table t2 has n rows of data. If there are many matches for the join conditions, the amount of data in the result set may be close to m*n.
[0034] When deduplication or other operations are required after the query, the amount of data in the result set after the inner join is large, and the amount of data to be processed is also correspondingly large. Compared with the processing of data within a single table before the join operation, the amount of calculation for deduplication or other operations on a larger amount of data after the join is larger and more complex, resulting in lower data processing efficiency and greater consumption of computing resources.
[0035] Exemplarily, when filtering null values on the nullable end, the outer join in the query statement is rewritten as an inner join through connection conversion to filter the null values on the nullable end. For example, for the query statement Select distinctt2.*from t1 left join t2 on t1.id=t2.id where t2.id is not null; the outer join left join is currently rewritten as an inner join to filter the null values on the nullable end. The rewritten query statement is Select distinct t2.*from t1 inner join t2on t1.id=t2.id where t2.id is notnull. The rewritten query statement means that after obtaining the result set of the inner join, the result set of the inner join needs to be deduplicated through the deduplication operation distinct. However, the join operation (inner join or outer join) will result in a large amount of data in its result set, resulting in a large amount of calculation and complexity of the deduplication operation, which reduces the efficiency of the deduplication operation.
[0036] Based on this, in response to the problems existing in the above-mentioned application scenarios, the present application provides a query statement connection conversion method, which can rewrite the inner connection in the query statement when processing the query statement of the database, especially for different types of operations included in the query statement, the inner connection in the query statement is rewritten separately to reduce the amount of data in the result set of the query statement, thereby improving the processing efficiency of subsequent deduplication operations or other operations based on the result set.
[0037] 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 described in detail in some embodiments. Figure 2 A flowchart of a query statement connection conversion method provided in an embodiment of the present application is shown in FIG. Figure 2 The method shown can be applied to Figure 1 In the scenario shown, it is executed by an electronic device.
[0038] Specifically, Figure 2 The connection conversion methods of the query statement shown include:
[0039] S201: Obtain a first query statement from a database. The electronic device may receive a query statement indicated by a user, or the electronic device may receive a query statement sent from another device. In this embodiment, the query statement received by the electronic device is recorded as the first query statement. In one embodiment, the query statement may be a Structured Query Language (SQL) statement.
[0040] S202: When the first query statement includes a first table and a second table that are inner-joined, and the target column of the first query statement is a column in the first table, confirm whether the first query statement is a query statement that is sensitive to duplicate values.
[0041] In one embodiment, the first query statement may be Select distinct t1.id from t1 innerjoin t2 on t1.id=t2.id, in which the inner join operation t1 inner join t2 between the first table t1 and the second table t2 is included, and the inner join operation includes an equijoin relationship of t1.id=t2.id. In addition, the target column of the first query statement is t1.id, that is, the target column is a column in the first table t1. For the first query statement, it can be determined whether the first query statement is a query statement sensitive to duplicate values based on the content in the first query statement.
[0042] Specifically, whether the first query statement is a query statement sensitive to duplicate values can be determined based on the operations and functions included in the first query statement, the type of set operation corresponding to the branch to which the first query statement belongs, the operators used for conditional judgment or associated data included in the subquery to which the first query statement belongs, and other contents.
[0043] If it is confirmed that the first query statement is a query statement sensitive to duplicate values, there is no need to adjust the connection method between the first table and the second table in the first query statement, and step S205 is directly executed to execute the first query statement; if it is confirmed that the first query statement is a query statement insensitive to duplicate values, step S203 is executed to adjust the connection method between the first table and the second table in the first query statement to obtain a second query statement, and by executing the second query statement, the number of duplicate values in the connection result set is reduced.
[0044] In one embodiment, the equivalent connection relationship may be after a preset character. For example, in the first query statement, the preset character is on, and the equivalent connection relationship included in the inner join operation is t1.id=t2.id after the character on. In other embodiments, the preset character may also be where, etc., and the present application does not limit the specific implementation of such preset characters.
[0045] S203: Adjust the connection mode between the first table and the second table to obtain a second query statement.
[0046] In one embodiment, after obtaining the first query statement, the processing of the first query statement may include at least the following stages: first, the first query statement is parsed, then the first query statement is logically and physically optimized by the optimizer to obtain the second query statement, and finally the optimized second query statement is executed by the executor. Then the above step S203 of the present application corresponds to the stage in which the optimizer optimizes the first query statement to obtain the second query statement after obtaining the first query statement and before the executor executes the second query statement.
[0047] Specifically, in S203, the connection mode between the first table and the second table may be adjusted specifically according to the content in the first query statement. That is, the inner connection between the first table and the second table may be adjusted to another connection mode specifically according to the content in the first query statement, so as to reduce the number of duplicate values in the result set of the connection between the first table and the second table.
[0048] Among them, the other connection methods may include, for example, semi join and anti join. Since the inner join returns all row combinations that meet the join conditions in the two tables, a large number of duplicate rows will appear in the result set. For example, when a row in the first table meets the join conditions with multiple rows in the second table, this row will be combined with these multiple rows to form multiple result rows, thereby increasing the duplicate values in the result set. Semi join will only return rows in the first table that have an association relationship with the second table, rather than returning all row combinations that meet the join conditions like inner join, that is, semi join focuses on rows in the first table that have a specific association with the second table, avoiding redundant combinations caused by multiple matches in inner join, thereby reducing duplicate values; anti join returns rows in the first table that do not have a certain relationship with the second table, and its implementation principle is similar to that of semi join, so it can also avoid redundant combinations caused by multiple matches in inner join, thereby reducing duplicate values.
[0049] S204: Execute the second query statement.
[0050] Since the second query statement is a query statement that adjusts the connection mode between the first table and the second table, that is, the second query statement adjusts the inner connection between the first table and the second table to other connection modes such as semi-connection and anti-connection that can reduce the number of repeated values in the result set of the connection between the first table and the second table. Therefore, the number of repeated values in the result set of the connection of the second query statement is less than the number of repeated values in the result set of the inner connection of the first query statement.
[0051] Specifically, after the optimizer completes processing of the first query statement to obtain the second query statement in S203, the executor may execute the second query statement optimized by the optimizer.
[0052] In summary, the connection conversion method of the query statement provided in this embodiment obtains the first query statement of the database, and when the first query statement contains the first table and the second table using the inner connection, and the target column of the first query statement is the column in the first table, confirms whether the first query statement is a query statement sensitive to duplicate values. If the first query statement is a query statement insensitive to duplicate values, the connection method of the first table and the second table is adjusted to obtain the second query statement, and the second query statement is executed so that the number of duplicate values in the query result of the second query statement is less than the number of duplicate values in the query result of the first query statement. Since the inner connection is rewritten in the second query statement, the number of duplicate values in the query result is reduced by the rewritten connection method, thereby reducing the amount of data in the result set, thereby improving the processing efficiency of subsequent data processing based on the result set.
[0053] Next, combine Figure 3-Figure 5 The embodiments of the present application provide a detailed description of how to confirm whether the first query statement is a query statement sensitive to duplicate values, and when the first query statement is a query statement insensitive to duplicate values, how to adjust the connection method between the first table and the second table to obtain the second query statement.
[0054] Figure 3 A flow chart of another query statement connection conversion method provided in an embodiment of the present application, such as Figure 3 As shown, the method may specifically include:
[0055] S301: Obtain content in a first query statement.
[0056] The content of the first query statement includes operations, functions, types of set operations corresponding to branches to which the first query statement belongs, operators for conditional judgment or associated data included in subqueries to which the first query statement belongs, etc. The content of the first query statement may be obtained by parsing the first query statement, and the present application does not limit the specific implementation method of how to parse and obtain the content of the first query statement.
[0057] Specifically, the operations in the first query statement include, but are not limited to, connection operations (such as inner connection, outer connection, etc.), screening operations (such as using a where clause to screen conditions), sorting operations (such as order by), deduplication operations, grouping operations, etc. For example, in the query statement Select distinct*from table1 where condition1 order bycolumn1, "where condition screening" and "order by sorting" are operations.
[0058] Function refers to various functions used in the first query statement, such as aggregate functions (sum, avg, count, min, max, etc.), date functions (date_add, date_diff, etc.), string functions, etc. For example, count(*) in the query statement Select count(*) from table1 where column1 = 'value' is a function.
[0059] The set operation can be, for example, a union operation, a difference operation, or an intersection operation. The set operation type refers to whether the set operation is a set operation that contains the keyword all. When the set operation contains the keyword all, the result set of the set operation is not deduplicated; when the set operation does not contain the keyword all, the result set of the set operation is deduplicated. For example, assuming that the union operation is
[0060] Select t1.id from t1 join t2 on t1.id! =t2.id
[0061] Union
[0062] Select t1.id from t1;
[0063] If the first query statement is a query statement in a union operation branch, namely, Select t1.id from t1 joint2on t1.id!=t2.id, the type of the set operation (union operation Union) corresponding to the branch to which the first query statement belongs is a type that does not contain the keyword all.
[0064] Operators in subqueries include in, exists, all, any, =, ! =, etc.
[0065] S302. When the type of the first query statement is the first type including a deduplication operation or a grouping operation, or is the second type including an aggregation function with a deduplication property, or is the third type in a branch of a set operation belonging to deduplication, confirm that the first query statement is a query statement that is insensitive to duplicate values.
[0066] Among them, the deduplication operations mentioned in the first type include distinct, unique, distinct on, etc., and the grouping operations include group by. The aggregation functions with deduplication properties mentioned in the second type include the minimum value min, the maximum value max, and the aggregation function agg that first performs deduplication operations on the data. The deduplication set operations mentioned in the third type include the union operation Union, the difference operation Minus or Except, and the intersection operation Intersect, and these set operations do not contain all (that is, these set operations are deduplication set operations).
[0067] If the type of the first query statement belongs to at least one of the first type, the second type, or the third type, it can be confirmed that the first query statement is a query statement that is insensitive to duplicate values.
[0068] S303: Rewrite the inner join into a semi-join to obtain a second query statement.
[0069] Optionally, the semi-join may be implemented by a statement that implements the semi-join function through an exists sub-link or an in sub-link, or by rewriting an inner join or inner join into a semi join.
[0070] Exemplarily, when the type of the first query statement is the first type, taking the first query statement including a deduplication operation as an example, the first query statement including an inner join may be, for example, Select distinct t1.*, t3.* from t1, t2, t3 where t1.id = t2.id and t2.age = t3.age. The second query statement obtained by rewriting the inner join into a semi-join may be Select distinct t1.*, t3.* from t1, t3 where exists (select 1 from t2 where t1.id = t2.id and t2.age = t3.age).
[0071] Exemplarily, when the type of the first query statement is the second type, taking the count aggregation function included in the first query statement as an example, the first query statement including the inner join may be, for example, Select count (distinct t2.id) from t1 join t2 on t1.id = t2.id. The second query statement obtained by rewriting the inner join into a semi join may be Select count (distinct t2.id) from t2 semi join t1 on t1.id = t2.id.
[0072] Exemplarily, when the type of the first query statement is the third type, the first query statement belongs to a branch of a union operation Union, and the union operation Union is as follows:
[0073] Select t1.id from t1 join t2 on t1.id! =t2.id
[0074] Union
[0075] Select t1.id from t1;
[0076] The first query statement is Select t1.id from t1 join t2 on t1.id! = t2.id. The second query statement obtained by rewriting the inner join into a semi-join is Select t1.id from t1 semi join t2ont1.id! = t2.id. The rewritten union operation Union is as follows:
[0077] Select t1.id from t1 semi join t2 on t1.id! =t2.id
[0078] Union
[0079] Select t1.id from t1;
[0080] In the rewriting process of step S303, the inner join is rewritten into a semi-join to obtain a second query statement, thereby changing the connection mode of the first table and the second table, reducing the duplicate rows generated in the inner join, and achieving the purpose of reducing the number of duplicate values in the result set generated by the connection between the first table and the second table.
[0081] Figure 4 A flowchart of another query statement connection conversion method provided in an embodiment of the present application is shown as follows: Figure 4 As shown, the method may specifically include:
[0082] S401: Obtain content in a first query statement.
[0083] In this step, the method of obtaining the content in the first query statement can refer to the aforementioned step S301, which will not be described in detail here.
[0084] S402: When the type of the first query statement is a fourth type in which a subquery of the first query statement includes an operator for condition judgment or data association, confirm that the first query statement is a duplicate value insensitive query statement.
[0085] The operators used for conditional judgment or data association in the fourth type include in, exists, all, any, =, ! =, etc.
[0086] The main functions of the above operators are to make conditional judgments or associate data. Their core logic is to judge whether a certain relationship (such as existence, comparison relationship, etc.) is established. For example, exists only focuses on whether the subquery returns rows, that is, the verification of existence, and has nothing to do with whether the data is repeated. In is to judge whether a value is in a set. The focus is on the judgment of membership. Whether the elements in the set are repeated does not affect the core logic of this judgment. All and any are comparison operators. They focus on the comparison relationship with a group of values. This comparison relationship is based on each independent value. The repeatability of data will not change the comparison logic. = and ! = are basic equality and inequality judgment operators. They judge the relationship between single values. When judging, they are not affected by whether the data is repeated or not. They only focus on whether the two values involved in the comparison are equal or unequal.
[0087] Therefore, if the type of the first query statement belongs to the fourth type, it can be confirmed that the first query statement is a query statement that is insensitive to duplicate values.
[0088] S403: Rewrite the inner join into a semi-join or an anti-join to obtain a second query statement.
[0089] Optionally, the anti-join may be implemented by a statement that implements the anti-join function through a not exists sub-link or a not in sub-link, or by rewriting an inner join (join or inner join) into an anti-join.
[0090] When the subquery of the first query statement includes in, exists, = operators, the inner join can be rewritten as a semi-join. When the subquery of the first query statement includes not in, != operators, the inner join can be rewritten as an anti-join.
[0091] Figure 5 A flowchart of a connection conversion method for another query statement provided in an embodiment of the present application is shown as follows: Figure 5 As shown, the method may specifically include:
[0092] S501: Obtain content in a first query statement.
[0093] In this step, the method of obtaining the content in the first query statement can refer to the aforementioned step S301, which will not be described in detail here.
[0094] S502: When the type of the first query statement is the fifth type of an operator including an inequality condition in a subquery of the first query statement, confirm that the first query statement is a query statement that is insensitive to duplicate values;
[0095] The fifth type of inequality condition operator is !=. != is a basic inequality judgment operator, which judges the relationship between single values. The judgment is not affected by whether the data is repeated or not, and only focuses on whether the two values involved in the comparison are unequal.
[0096] Therefore, if the type of the first query statement belongs to the fifth type, it can be confirmed that the first query statement is a query statement that is insensitive to duplicate values.
[0097] S503: Rewrite the inner join into an anti-join or a semi-join to obtain a second query statement.
[0098] In this case, the inner join can be rewritten as an anti-join, or the inner join can be rewritten as a semi-join, and the second query statement can be obtained.
[0099] For example, the first query statement belongs to the branch of the union operation Union, and the union operation Union is as follows:
[0100] Select t1.id from t1 join t2 on t1.id! =t2.id
[0101] Union
[0102] Select t1.id from t1;
[0103] The first query statement is Select t1.id from t1 join t2 on t1.id! = t2.id. The second query statement obtained by rewriting the inner join into a semi-join is Select t1.id from t1 semi join t2ont1.id! = t2.id. The rewritten union operation Union is as follows:
[0104] Select t1.id from t1 semi join t2 on t1.id! =t2.id
[0105] Union
[0106] Select t1.id from t1;
[0107] Alternatively, the second query statement obtained by rewriting the inner join into an anti-join can be Select t1.id from t1 anti join t2 on t1.id=t2.id and t2.id is not null. The rewritten union operation Union is as follows:
[0108] Select t1.id from t1 anti join t2 on t1.id=t2.id
[0109] Union
[0110] Select t1.id from t1;
[0111] Next, taking the statement that implements the semi-join function through the exists sub-link to rewrite the inner join into a semi-join as an example, the rewriting of the inner join into a semi-join to obtain the second query statement in the aforementioned embodiment is introduced in detail. Figure 6 A flowchart of a connection conversion method for another query statement provided in an embodiment of the present application is shown as follows: Figure 6 As shown, the method may specifically include:
[0112] S601: According to a first query statement, search and obtain an inner connection included in the first query statement.
[0113] The inner connection included in the first query statement may be obtained by parsing the first query statement, and the present application does not limit the specific implementation method of how to parse and obtain the content in the first query statement.
[0114] By searching, all inner connections included in the first query statement can be obtained, and the present application does not limit the number of inner connections in the first query statement. If the first query statement includes only one inner connection and the inner connection needs to be rewritten, only the inner connection is rewritten; if the first query statement includes multiple inner connections and multiple inner connections need to be rewritten, the multiple inner connections can be rewritten in sequence by loop processing.
[0115] Exemplarily, taking the first query statement Select distinct t2.*from t1 inner join t2 on t1.id=t2.id where t2.id is not null as an example, it can be determined by parsing the first query statement that it includes only one inner join for connecting the first table t1 and the second table t2.
[0116] S602: Generate a subquery to implement a semi-join based on the inner join.
[0117] A subquery for implementing a semi-join is generated according to the target column of the first query statement, the first table and the second table connected by the inner join, and the equijoin condition of the inner join.
[0118] Continuing with the example of the first query statement Select distinct t1.*from t1 inner join t2 on t1.id=t2.id where t2.id is not null, the subquery of the semi-join may be exists(Select1from t1 where t1.id=t2.id).
[0119] S603: Delete the inner join in the first query statement and add a subquery for implementing a semi-join to obtain a second query statement.
[0120] After generating the semi-join subquery corresponding to the inner join, the inner join is deleted from the first query statement, that is, the inner join, the first table connected by the inner join, the second table, and the equal join condition of the inner join are deleted. After the deletion is completed, the generated semi-join subquery is added to the processed first query statement to obtain the second query statement.
[0121] Continuing with the example of the first query statement Select distinct t1.*from t1 inner join t2 on t1.id=t2.id where t2.id is not null, the second query statement may be Select distinct t1.*from t1 where exists(Select 1from t1 where t1.id=t2.id).
[0122] Next, taking the statement that realizes the anti-join function through the not exists sub-link to rewrite the inner join into an anti-join as an example, the rewriting of the inner join into an anti-join to obtain the second query statement in the above embodiment is described in detail. Figure 7 A flowchart of a connection conversion method for another query statement provided in an embodiment of the present application is shown as follows: Figure 7 As shown, the method may specifically include:
[0123] S701: According to a first query statement, search and obtain an inner connection included in the first query statement.
[0124] This step is the same as the aforementioned step S601 and will not be repeated here.
[0125] S702. Generate a subquery to implement an anti-join based on the inner join.
[0126] A subquery for implementing anti-join is generated based on the target column of the first query statement, the first table and the second table connected by the inner join, and the equijoin condition of the inner join, wherein the equijoin condition in the subquery for implementing anti-join is opposite to the equijoin condition of the inner join.
[0127] For example, taking the first query statement as Select t1.id from t1 join t2 on t1.id!=t2.id as an example, the anti-join subquery may be not exists (Select 1 from t2 where t1.id=t2.id).
[0128] S703: Delete the inner join in the first query statement and add a subquery for implementing an anti-join to obtain a second query statement.
[0129] After generating the anti-join subquery corresponding to the inner join, the inner join is deleted from the first query statement, that is, the inner join, the first table connected by the inner join, the second table, and the equal join condition of the inner join are deleted. After the deletion is completed, the generated anti-join subquery is added to the processed first query statement to obtain the second query statement.
[0130] Continuing with the example of the first query statement Select t1.id from t1 join t2 on t1.id!=t2.id, the second query statement may be Select t1.id from t1 where not exists (Select 1fromt2where t1.id=t2.id).
[0131] In the aforementioned embodiments of the present application, the query statement processing method provided by the embodiments of the present application is introduced, and in order to realize the functions in the methods provided by the above embodiments of the present application, the electronic device as the execution subject may include a hardware structure and / or a software module, and the above functions are realized in the form of a hardware structure, a software module, or a hardware structure plus a software module. Whether one of the above functions is executed in the form of a hardware structure, a software module, or a hardware structure plus a software module depends on the specific application and design constraints of the technical solution.
[0132] Figure 8 A schematic diagram of a connection conversion device for a query statement provided in this application, such as Figure 8 The device shown can be used to perform Figure 2-Figure 7 A connection conversion method for query statements provided in any embodiment. In one embodiment, Figure 8The device 1000 shown includes: an acquisition module 1001, a processing module 1002, a control module 1003 and an execution module 1004. Among them, the acquisition module 1001 is used to obtain a first query statement of the database. The processing module 1002 is used to confirm whether the first query statement is a query statement sensitive to duplicate values when there are a first table and a second table using an inner connection in the first query statement, and the target column of the first query statement is a column in the first table. The control module 1003 is used to adjust the connection method of the first table and the second table to obtain a second query statement if the first query statement is a query statement insensitive to duplicate values. The execution module 1004 is used to execute the second query statement, and the number of duplicate values in the query result of the second query statement is less than the number of duplicate values in the query result of the first query statement.
[0133] In an embodiment of the present application, the processing module 1002 is specifically used to confirm that the first query statement is a query statement that is insensitive to duplicate values when the type of the first query statement is a first type that includes a deduplication operation or a grouping operation, or a second type that includes an aggregate function with a deduplication property, or a third type in a branch of a deduplication set operation. The control module 1003 is specifically used to rewrite the inner join into a semi-join to obtain a second query statement.
[0134] In one embodiment of the present application, the processing module 1002 is specifically used to confirm that the first query statement is a query statement that is insensitive to duplicate values when the type of the first query statement is a fourth type of operator for conditional judgment or association data included in the subquery of the first query statement. The control module 1003 is specifically used to rewrite the inner join into a semi-join or an anti-join to obtain a second query statement.
[0135] In one embodiment of the present application, the processing module 1002 is specifically used to confirm that the first query statement is a query statement that is insensitive to duplicate values when the type of the first query statement is the fifth type of operator including an inequality condition in the subquery of the first query statement. The control module 1003 is specifically used to rewrite the inner join into an anti-join or a semi-join to obtain a second query statement.
[0136] In one embodiment of the present application, the control module 1003 is specifically used to search and obtain the inner connection included in the first query statement according to the first query statement, generate a subquery to implement a semi-connection according to the inner connection, delete the inner connection in the first query statement and add a subquery to implement a semi-connection to obtain a second query statement.
[0137] In one embodiment of the present application, the control module 1003 is specifically used to search and obtain the inner connection included in the first query statement according to the first query statement, generate a subquery to implement the anti-connection according to the inner connection, delete the inner connection in the first query statement and add a subquery to implement the anti-connection to obtain a second query statement.
[0138] The specific implementation method and principle of the query statement connection conversion device of the query statement mentioned above refer to the description of the query statement connection conversion method of the query statement mentioned above, which will not be repeated here.
[0139] It should be noted that it should be understood that the division of the various modules of the above device is only a division of logical functions. In actual implementation, they can be fully or partially integrated into one physical entity, or they can be physically separated. And these modules can all be implemented in the form of software called by processing elements; they can also be all implemented in the form of hardware; some modules can also be implemented in the form of software called by processing elements, and some modules can be implemented in the form of hardware. For example, the processing module can be a separately established processing element, or it can be integrated in a chip of the above device. In addition, it can also be stored in the memory of the above device in the form of program code, and called and executed by a processing element of the above device. The function of the above-mentioned module is determined. The implementation of other modules is similar. In addition, these modules can be fully or partially integrated together, or they can be implemented independently. The processing element described here can be an integrated circuit with signal processing capabilities. In the implementation process, each step of the above method or each module above can be completed by an integrated logic circuit of hardware in the processor element or instructions in the form of software.
[0140] For example, the above modules may be one or more integrated circuits configured to implement the above methods, such as one or more application specific integrated circuits (ASIC), or one or more microprocessors (digital signal processors, DSP), or one or more field programmable gate arrays (FPGA), etc. For another example, when a module above is implemented in the form of a processing element scheduling program code, the processing element may be a general-purpose processor, such as a central processing unit (CPU) or other processor that can call program code. For another example, these modules may be integrated together and implemented in the form of a system-on-a-chip (SOC).
[0141] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented using software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present application is generated in whole or in part. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions may be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium, for example, the computer instructions may be transmitted from a website site, computer, server or data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) mode to another website site, computer, server or data center. The computer-readable storage medium may be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more available media integrated. The available medium may be a magnetic medium, (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid state disk (SSD)), etc.
[0142] For example, Fig. 9 A structural diagram of a connection conversion device for a query statement provided in this application, such as Fig. 9 The device shown can be used to perform Figure 2-Figure 7 A connection conversion method for a query statement shown in any one of the items. In one embodiment, Fig. 9 The query statement processing device 2000 shown includes one or more processors 2001 and a memory 2002; wherein the memory 2002 is used to store computer executable instructions, and the processor 2001 can execute the computer executable instructions stored in the memory 2002. When the computer executable instructions are executed by the processor 2001, the processor 2001 implements the connection conversion method of any query statement in the aforementioned embodiments of the present application.
[0143] In one embodiment, if Fig. 9 The query statement connection conversion device 2000 shown also includes a communication interface 2003 , wherein the processor 2001 can communicate with other devices through the communication interface 2003 , for example, the processor 2001 obtains the first query statement through the communication interface 2003 .
[0144] The present application also provides a computer-readable storage medium, which stores computer-executable instructions. When the computer-executable instructions are executed, they can be used to implement a connection conversion method for any query statement in the aforementioned embodiments of the present application.
[0145] An embodiment of the present application also provides a chip for executing instructions, wherein the chip is used to execute a connection conversion method as described in any of the query statements described above in the present application.
[0146] An embodiment of the present application also provides a computer program product, including a computer program, which, when executed, implements the connection conversion method of any query statement described above in the present application.
[0147] Those skilled in the art can understand that all or part of the steps of implementing the above-mentioned method embodiments can be completed by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When the program is executed, the steps of the above-mentioned method embodiments are executed; and the aforementioned storage medium includes: ROM, RAM, disk or optical disk and other media that can store program codes.
[0148] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit it. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present application.
Claims
1. A query statement connection conversion method, characterized in that: include: Get the first query statement of the database; When the first query statement includes a first table and a second table that are inner-joined, and a target column of the first query statement is a column in the first table, confirming whether the first query statement is a query statement that is sensitive to duplicate values; If the first query statement is a query statement that is insensitive to duplicate values, adjusting the connection method of the first table and the second table to obtain a second query statement; The second query statement is executed, and the number of repeated values in the result set of the connection of the second query statement is less than the number of repeated values in the result set of the inner connection of the first query statement.
2. The method according to claim 1, characterized in that The confirming whether the first query statement is a query statement sensitive to repeated values includes: When the type of the first query statement is a first type including a deduplication operation or a grouping operation, or a second type including an aggregation function with a deduplication property, or a third type in a branch of a set operation belonging to deduplication, confirming that the first query statement is a query statement that is insensitive to duplicate values; The adjusting the connection mode between the first table and the second table to obtain a second query statement includes: The inner join is rewritten into a semi-join to obtain the second query statement.
3. The method according to claim 1, characterized in that The confirming whether the first query statement is a query statement sensitive to repeated values includes: When the type of the first query statement is a fourth type in which a subquery of the first query statement includes an operator for condition judgment or association data, confirming that the first query statement is a query statement that is insensitive to duplicate values; The adjusting the connection mode between the first table and the second table to obtain a second query statement includes: The inner join is rewritten into a semi-join or an anti-join to obtain the second query statement.
4. The method according to claim 1, characterized in that: The confirming whether the first query statement is a query statement sensitive to repeated values includes: When the type of the first query statement is the fifth type in which the subquery of the first query statement includes an operator of an inequality condition, confirming that the first query statement is a query statement that is insensitive to duplicate values; The adjusting the connection mode between the first table and the second table to obtain a second query statement includes: The inner join is rewritten into an anti-join or a semi-join to obtain the second query statement.
5. The method according to any one of claims 1 to 4, characterized in that: The step of rewriting the inner join into a semi-join to obtain the second query statement includes: According to the first query statement, searching for an inner connection included in the first query statement; Generate a subquery to implement the semi-join according to the inner join; The inner join in the first query statement is deleted and the subquery for implementing the semi-join is added to obtain the second query statement.
6. The method according to any one of claims 1 to 4, characterized in that: The step of rewriting the inner join into an anti-join to obtain the second query statement includes: According to the first query statement, searching for an inner connection included in the first query statement; Generate a subquery to implement the anti-join according to the inner join; The inner join in the first query statement is deleted and the subquery for implementing the anti-join is added to obtain the second query statement.
7. A query statement connection conversion device, characterized in that: include: An acquisition module, used to acquire a first query statement from a database; a processing module, configured to, when the first query statement includes a first table and a second table that are inner-joined, and a target column of the first query statement is a column in the first table, determine whether the first query statement is a query statement that is sensitive to duplicate values; a control module, configured to adjust a connection mode between the first table and the second table to obtain a second query statement if the first query statement is a query statement that is insensitive to duplicate values; An execution module is used to execute the second query statement, wherein the number of repeated values in the query result of the second query statement is less than the number of repeated values in the query result of the first query statement.
8. A query statement connection conversion 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 data query device executes the connection conversion method of the query statement according to any one of claims 1 to 6.
9. A computer-readable storage medium, characterized in that: Computer executable instructions are stored, and when the computer executable instructions are executed, the connection conversion method of the query statement according to any one of claims 1 to 6 is implemented.
10. A computer program product, characterized in that It comprises a computer program, which, when executed, implements the connection conversion method of the query statement according to any one of claims 1 to 6.