Query processing method and apparatus based on multi-table join

Abstract

The present application provides a query processing method and apparatus based on multi-table join. The method relates to a user end, an optimization end, an execution end, and a database. The method comprises: when the optimization end receives a query request sent by the user end, the optimization end invokes corresponding metadata from the database on the basis of the query request; the optimization end generates a first access constraint on the basis of a data table set and preset cross-table semantics; the optimization end generates a query plan on the basis of the first access constraint and preset query rewriting types, wherein the query rewriting types comprise projection matching rewriting and Join rewriting; the optimization end sends the query plan to the execution end; the execution end is used for feeding back a query result, which is generated by executing the query plan, to the user end. The present application improves a cross-table data dependency capability, reduces the read-write overhead and calculation amount of scanning during query, reduces the maintenance cost, and supports real-time updating of data.

Description

A query processing method and device based on multi-table connection Technical Field

[0001] The present application relates to the field of data query, and in particular to a query processing method and device based on multi-table connection. Background Art

[0002] Query performance is a key metric for query analysis in big data scenarios. To improve query performance, the industry has explored a variety of methods, including indexes, materialized views, cubes, and bounded-computing-based access constraints.

[0003] Real-time synchronization of updates between materialized views and cubes is difficult. Data refreshes are often required, which can take a long time when the data volume is large, and the materialized view is unavailable during the refresh. These limitations result in limited flexibility for materialized views and cubes, limiting their use to relatively fixed query statements.

[0004] In bounded computation, access constraints require finding the degree N of duplication of x->y in a specific table and storing the duplicates as {x, y, count}. In practical applications, queries often involve multiple relational tables. To ensure that the query uses the AC for each table, the access constraints of all involved relational tables Ti must be considered. This makes it difficult to quickly determine the optimal AC for a specific relational table Ti during optimization. Furthermore, scanning each AC requires I / O overhead.

[0005] Summary of the Invention

[0006] In view of the above problems, the present application is proposed to provide a query processing method based on multi-table join and its device to overcome the above problems or at least partially solve the above problems, including:

[0007] A query processing method based on multi-table joins is used to query data in a data table set scenario having multiple data tables and relationships between the multiple data tables. The method involves a user end, an optimization end, an execution end, and a database. The user end is used to generate a query request based on user needs and send the query request to the optimization end. The method includes:

[0008] When the optimization end receives the query request sent by the user end, the optimization end retrieves corresponding metadata from the database according to the query request, wherein the metadata includes a data table set and an access constraint set;

[0009] The optimization end generates a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields, and logical inclusion relationships;

[0010] The optimization end generates a query plan according to the first access constraint and a preset query rewriting type, wherein the query rewriting type includes projection matching rewriting and join rewriting;

[0011] The optimization end sends the query plan to the execution end; the execution end is used to feed back the query results generated after the query plan is executed to the user end.

[0012] Furthermore, the step of generating a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields, and logical inclusion relationships, includes:

[0013] When the cross-table semantics is the foreign key, the optimization end obtains a first target data table in the data table set;

[0014] The optimization end determines a first target value of a preset target field in the first target data table;

[0015] The optimization end obtains, in the data table set according to the first target value, a second target value having the same value as the first target value;

[0016] The optimization end determines a second target data table in the data table set according to the second target value;

[0017] The optimization end generates the first access constraint according to the preset target field, the first target data table, the first target value, the second target data table, and the second target value.

[0018] Furthermore, the step of generating a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields, and logical inclusion relationships, includes:

[0019] When the cross-table semantics is the same semantic field, the optimization end obtains at least two data tables in the data table set, wherein the two data tables are a third target data table and a fourth target data table;

[0020] The optimization end obtains the third target field in the third target data table and the fourth target field in the fourth target data table respectively;

[0021] When the third target field and the fourth target field have the same semantics, the optimization end generates the first access constraint according to the third target data table, the fourth target data table, the third target field, and the fourth target field.

[0022] Furthermore, the step of generating a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields, and logical inclusion relationships, includes:

[0023] When the cross-table semantics is the logical inclusion relationship, the optimization end obtains at least two data tables in the data table set, wherein the two data tables are a fifth target data table and a sixth target data table;

[0024] The optimization end obtains the fifth target field in the fifth target data table and the sixth target field in the sixth target data table respectively;

[0025] When the semantics of the fifth target field includes the semantics of the sixth target field, or the semantics of the sixth target field includes the semantics of the fifth target field, the optimization end generates the first access constraint based on the fifth target data table, the sixth target data table, the fifth target field and the sixth target field.

[0026] Furthermore, generating a query plan based on the first access constraint and the query rewriting type, wherein the query rewriting type includes projection matching rewriting and Join rewriting steps, includes:

[0027] When the query rewriting type is the projection matching rewriting, the optimization end determines whether the projection expression in the analysis query statement is obtained by operating the expression in the first access constraint, and if so, generates the query plan according to the first access constraint; or

[0028] When the query rewrite type is the Join rewrite, the optimizer determines whether the data of the left and right nodes of the join operator exist in the first access constraint, and if so, generates the query plan according to the first access constraint.

[0029] The present application also discloses a query processing method based on multi-table joins, which is used to query data in a data table set scenario having multiple data tables and relationships between the multiple data tables. The method involves a user end, an optimization end, an execution end, and a database, and includes:

[0030] The execution end receives the query plan sent by the optimization end, and the optimization end is used to send the query plan to the execution end;

[0031] The execution end determines the target access constraint according to the query plan;

[0032] The execution end determines the target data in the data table set according to the target access constraint;

[0033] The execution end generates a query result based on the target data;

[0034] The execution end sends the query result to the user end, and the user end is used to receive the query result fed back by the execution end.

[0035] The present application also discloses a query processing method based on multi-table joins, which is used to query data in a data table set scenario having multiple data tables and relationships between the multiple data tables. The method involves a user end, an optimization end, an execution end, and a database, and includes:

[0036] The user terminal generates a query request in response to a user's demand and sends the query request to the optimization terminal, and the optimization terminal is used to receive the query request sent by the user terminal;

[0037] The user end receives the query result sent by the execution end and returns the query result to the corresponding target user. The execution end is used to feed back the query result to the user end.

[0038] The present application also discloses a query processing device based on multi-table joins, which is used to query data in a data table set scenario having multiple data tables and relationships between the multiple data tables. The device involves a user end, an optimization end, an execution end, and a database, and includes:

[0039] a first calling module, configured to retrieve corresponding metadata from the database according to the query request when the optimization end receives the query request sent by the user end, wherein the metadata includes a data table set and an access constraint set;

[0040] A first generation module is configured to generate, by the optimization end, a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, fields with the same semantics, and logical inclusion relationships;

[0041] A second generation module is configured to cause the optimization end to generate a query plan according to the first access constraint and a preset query rewriting type, wherein the query rewriting type includes projection matching rewriting and join rewriting;

[0042] The first sending module is used for the optimization end to send the query plan to the execution end; the execution end is used to feed back the query result generated after the query plan is executed to the user end.

[0043] The present application also discloses a query processing device based on multi-table joins, which is used to query data in a data table set scenario having multiple data tables and relationships between the multiple data tables. The device involves a user end, an optimization end, an execution end, and a database, and includes:

[0044] a second sending module, configured for the execution end to receive the query plan sent by the optimization end, and the optimization end to send the query plan to the execution end;

[0045] A first determination module, configured for the execution end to determine a target access constraint according to the query plan;

[0046] A second determination module, configured for the execution end to determine target data in the data table set according to the target access constraint;

[0047] A third generating module, configured for the execution end to generate a query result based on the target data;

[0048] The third sending module is used for the execution end to send the query result to the user end, and the user end is used to receive the query result fed back by the execution end.

[0049] The present application also discloses a query processing device based on multi-table joins, which is used to query data in a data table set scenario having multiple data tables and relationships between the multiple data tables. The device involves a user end, an optimization end, an execution end, and a database, and includes:

[0050] A fourth sending module is configured for the user end to generate a query request in response to a user's demand and send the query request to the optimization end, wherein the optimization end is configured to receive the query request sent by the user end;

[0051] The return module is used for the user end to receive the query result sent by the execution end and return the query result to the corresponding target user. The execution end is used to feed back the query result to the user end.

[0052] This application has the following advantages:

[0053] In an embodiment of the present application, relative to the shortcomings of the prior art in that "it is impossible to quickly determine which AC in a specific relational table Ti is optimal during optimization; and each AC requires IO overhead during scanning", the present application provides a solution of "a query processing method and device based on multi-table connection", specifically: "When the optimization end receives a query request sent by the user end, the optimization end retrieves corresponding metadata from the database according to the query request, wherein the metadata includes a data table set and an access constraint set; the optimization end generates a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields and logical inclusion relationships; the optimization end generates a query plan based on the first access constraint and a preset query rewrite type, wherein the query rewrite type includes projection matching rewrite and Join rewrite; the optimization end sends the query plan to the execution end; the execution end is used to feed back the query results generated after the query plan is executed to the user end." By "generating a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields and logical inclusion relationships; and the optimization end generating a query plan based on the first access constraint and the query rewrite type", the problem of "being unable to quickly determine which AC in a specific relational table Ti is optimal during optimization; and each AC requires IO overhead during scanning" is solved; and the effect of "increasing cross-table data dependency capabilities for SAC, reducing the IO overhead and Join calculation amount of scanning multiple table ACs during query; based on data dependency, reducing SAC maintenance costs and supporting real-time data updates" is achieved. BRIEF DESCRIPTION OF THE DRAWINGS

[0054] In order to more clearly illustrate the technical solution of the present application, the following is a brief introduction to the drawings required for the description of the present application. 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 any creative work.

[0055] FIG1 is a flowchart of a query processing method based on multi-table join provided by an embodiment of the present application;

[0056] FIG2 is a flowchart of a query processing method based on multi-table joins provided by an embodiment of the present application;

[0057] FIG3 is a flowchart of a query processing method based on multi-table joins provided by an embodiment of the present application;

[0058] FIG4 is a structural block diagram of a query processing device based on multi-table join provided by an embodiment of the present application;

[0059] FIG5 is a structural block diagram of a query processing device based on multi-table join provided by an embodiment of the present application;

[0060] FIG6 is a structural block diagram of a query processing device based on multi-table joins provided by an embodiment of the present application;

[0061] FIG7 is a schematic structural diagram of a computer device provided by an embodiment of the present invention;

[0062] FIG8 is a flow chart of an embodiment of a multi-table join query processing method provided by an embodiment of the present invention;

[0063] FIG9 is a flow chart of another embodiment of a multi-table join query processing method provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0064] To make the objectives, features, and advantages of this application more readily apparent, the present application is further described below in conjunction with the accompanying drawings and specific embodiments. It is apparent that the embodiments described are only a portion of the embodiments of this application, not all of them. All other embodiments derived by persons of ordinary skill in the art based on the embodiments in this application without inventive effort are also within the scope of protection of this application.

[0065] The inventors found through analysis of the prior art that:

[0066] Query performance is a key metric for query analysis in big data scenarios. To improve query performance, the industry has explored a variety of methods, including indexes, materialized views, cubes, and bounded-computing-based access constraints.

[0067] Materialized Views in Oracle

[0068] The biggest advantage of materialized views is that they can improve performance: Oracle's materialized views provide powerful functionality that can be used to pre-calculate and save the results of time-consuming operations such as table joins or aggregations. This allows queries to avoid these time-consuming operations and obtain results quickly. The main constraints are as follows:

[0069] 1. Materialized views work best in read-only or "intensive reading" environments and should not be used in online transaction processing (OLTP) environments. When updating fact tables, etc., materialized view row locks will be caused, thus affecting system concurrency.

[0070] 2. Materialized views cannot be refreshed quickly, resulting in inaccurate query data.

[0071] 3. Rowid materialized views (materialized views created typically have a primary key, rowid, and subquery view) have only a single primary table and cannot include any of the following:

[0072] A. Distinct or aggregate function;

[0073] B. Group by, subquery, join and SET operations.

[0074] 4. Materialized views increase the demand for disk resources, that is, hard disk space needs to be permanently allocated to materialized views to store data.

[0075] 5. The working principle of materialized views is subject to some possible constraints, such as primary keys, foreign keys, etc.

[0076] Apache Kylin's pre-calculation

[0077] Apache Kylin works by pre-calculating the cube of the data model and using the calculation results to accelerate queries. The specific working process is as follows:

[0078] 1) Specify the data model and define dimensions and measures.

[0079] 2) Precompute the Cube, calculate all Cuboids and save them as materialized views.

[0080] 3) When executing a query, read the Cuboid, perform operations, and generate query results.

[0081] Because Kylin's query process doesn't scan raw records, but instead precomputes complex operations like table joins and aggregations, and then uses the precomputed results to execute queries, it's generally one to two orders of magnitude faster than non-precomputed query techniques, and this advantage is even more pronounced on very large datasets. However, precomputation itself must be completed in advance and doesn't support dynamic data changes.

[0082] Bounded Computation

[0083] Bounded computing can significantly reduce the access cost of a single relational table through access constraints. However, in multi-table joins, the single-table access constraints AC of different tables must be accessed before the join operation. Therefore, the I / O and computing overhead are higher than those of specific multi-table access constraints SAC.

[0084] Real-time synchronization of updates between materialized views and cubes is difficult. Data refreshes are often required, which can take a long time when the data volume is large, and the materialized view is unavailable during the refresh. These limitations result in limited flexibility for materialized views and cubes, limiting their use to relatively fixed query statements.

[0085] In bounded computation, access constraints require finding the degree N of duplication of x->y in a specific table and storing the duplicates as {x, y, count}. In real-world applications, queries often involve multiple relational tables. To ensure that the query uses the AC for each table, the access constraints for all involved relational tables Ti must be considered. This makes it difficult to quickly determine the optimal AC for a specific relational table Ti during optimization. Furthermore, scanning each AC requires I / O overhead.

[0086] It should be noted that the single-table query access constraint AC is organized to compress and deduplicate duplicate data in the specified column according to the model definition {x->y,N}. x and y are both sets of expressions, each of which contains one or more fields from the table. Therefore, AC contains x, y, and the number of duplicate records (called the duplication degree, denoted as count) for each x value. In other words, AC is the set {x, y, count}. Generally speaking, the more fields x and y involve, the less likely x->y will have a high duplication degree, resulting in a smaller count value.

[0087] 1 , 8 and 9 , a flowchart of a query processing method based on multi-table join and a schematic flow diagram of an embodiment of the present application are shown;

[0088] A query processing method based on multi-table joins is used to query data in a data table set scenario having multiple data tables and relationships between the multiple data tables. The method involves a user end, an optimization end, an execution end, and a database, and includes:

[0089] S110: When the optimization end receives a query request sent by the user end, the optimization end retrieves corresponding metadata from the database according to the query request, wherein the metadata includes a data table set and an access constraint set;

[0090] S120: The optimization end generates a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, fields with the same semantics, and logical inclusion relationships;

[0091] S130: The optimizer generates a query plan according to the first access constraint and a preset query rewrite type, wherein the query rewrite type includes projection matching rewrite and join rewrite;

[0092] S140. The optimization end sends the query plan to the execution end; the execution end is used to feed back the query results generated after the query plan is executed to the user end.

[0093] In an embodiment of the present application, relative to the shortcomings of the prior art in that "it is impossible to quickly determine which AC in a specific relational table Ti is optimal during optimization; and each AC requires IO overhead during scanning", the present application provides a solution of "a query processing method and device based on multi-table connection", specifically: "When the optimization end receives a query request sent by the user end, the optimization end retrieves corresponding metadata from the database according to the query request, wherein the metadata includes a data table set and an access constraint set; the optimization end generates a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields and logical inclusion relationships; the optimization end generates a query plan based on the first access constraint and a preset query rewrite type, wherein the query rewrite type includes projection matching rewrite and Join rewrite; the optimization end sends the query plan to the execution end; the execution end is used to feed back the query results generated after the query plan is executed to the user end." By "generating a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields and logical inclusion relationships; and the optimization end generating a query plan based on the first access constraint and the query rewrite type", the problem of "being unable to quickly determine which AC in a specific relational table Ti is optimal during optimization; and each AC requires IO overhead during scanning" is solved; and the effect of "increasing cross-table data dependency capabilities for SAC, reducing the IO overhead and Join calculation amount of scanning multiple table ACs during query; based on data dependency, reducing SAC maintenance costs and supporting real-time data updates" is achieved.

[0094] Next, a query processing method based on multi-table connection in this exemplary embodiment will be further described.

[0095] In one embodiment of the present invention, when creating a SAC, the user must provide a complete SAC definition, including the relational tables involved, the specific definitions of the x and y fields, and the join conditions to be met. Based on the SAC definition, the database filters invalid data from the original table according to the specified join conditions and generates {T1.x, T2.y, count} data, where x and y belong to different relational tables.

[0096] In one embodiment of the present invention, when querying data in a table, the optimizer will determine whether the query can be rewritten as an access to the SAC during the query optimization phase.

[0097] In one embodiment of the present invention, in FIG8 :

[0098] SAC: An access constraint created based on access patterns on multiple related tables, saving the values ​​of the X and corresponding Y fields.

[0099] Metadata: stores description information of data tables and SAC.

[0100] The optimizer rewrites the query plan to leverage the capabilities of SAC, as shown in the following figure, and finds a matching SAC for a specific join operation.

[0101] Executor: Executes queries according to the query plan generated by the optimizer and generates query results.

[0102] Query: For a user query, the optimizer generates a query plan based on the query and sends it to the executor for execution, and finally returns the query results to the user.

[0103] In one embodiment of the present invention, in FIG9 :

[0104] Table fact: fact table;

[0105] Table dim1: dimension table 1;

[0106] Table dim2: dimension table 2;

[0107] SAC: Extracts access constraints for multi-table join queries based on the relationship between the fact table and two dimension tables.

[0108] Analytic Query: When the optimizer determines that an analytical query cannot use SAC access constraints, it must query data in each table and perform join calculations. If it determines that SAC access constraints can be used, the results can be obtained by querying the SAC data, saving I / O and computing overhead.

[0109] In one embodiment of the present invention, the step of generating the first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields, and logical inclusion relationships, includes:

[0110] When the cross-table semantics is the foreign key, the optimization end obtains a first target data table in the data table set;

[0111] The optimization end determines a first target value of a preset target field in the first target data table;

[0112] The optimization end obtains, in the data table set according to the first target value, a second target value having the same value as the first target value;

[0113] The optimization end determines a second target data table in the data table set according to the second target value;

[0114] The optimization end generates the first access constraint according to the preset target field, the first target data table, the first target value, the second target data table, and the second target value.

[0115] It should be noted that there is a foreign key relationship between two fields in different tables, that is, the value of a field in a table must exist in the value of the corresponding field in another table.

[0116] In one embodiment of the present invention, the step of generating the first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields, and logical inclusion relationships, includes:

[0117] When the cross-table semantics is the same semantic field, the optimization end obtains at least two data tables in the data table set, wherein the two data tables are a third target data table and a fourth target data table;

[0118] The optimization end obtains the third target field in the third target data table and the fourth target field in the fourth target data table respectively;

[0119] When the third target field and the fourth target field have the same semantics, the optimization end generates the first access constraint according to the third target data table, the fourth target data table, the third target field, and the fourth target field.

[0120] It should be noted that two fields in different tables represent the same semantics. In a specific implementation, for example, the relationship between "district" and "administrative district".

[0121] In one embodiment of the present invention, the step of generating the first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields, and logical inclusion relationships, includes:

[0122] When the cross-table semantics is the logical inclusion relationship, the optimization end obtains at least two data tables in the data table set, wherein the two data tables are a fifth target data table and a sixth target data table;

[0123] The optimization end obtains the fifth target field in the fifth target data table and the sixth target field in the sixth target data table respectively;

[0124] When the semantics of the fifth target field includes the semantics of the sixth target field, or the semantics of the sixth target field includes the semantics of the fifth target field, the optimization end generates the first access constraint based on the fifth target data table, the sixth target data table, the fifth target field and the sixth target field.

[0125] It should be noted that the semantics of a field in a table includes the semantics of the corresponding field in another table. In a specific implementation, for example, the relationship between "province" and "city".

[0126] In another embodiment of the present invention, the multi-table query access constraint SAC may further include the following attributes to further enhance the acceleration effect:

[0127] 1. Conditional filtering: Add filtering conditions to SAC. Only data that meets the conditions will be inserted into SAC.

[0128] 2. Aggregation function: In addition to x and y, you can add aggregation functions for specified fields or expressions to implement pre-calculation of aggregation operations;

[0129] 3. Expression calculation: The fields in x and y can be not only columns in the table, but also expressions calculated on table columns, enabling pre-calculation of expression calculations. Expressions can also appear in filter conditions.

[0130] In one embodiment of the present invention, generating a query plan based on the first access constraint and the query rewriting type, wherein the query rewriting type includes projection matching rewriting and join rewriting steps, includes:

[0131] When the query rewriting type is the projection matching rewriting, the optimization end determines whether the projection expression in the analysis query statement is obtained by operating the expression in the first access constraint, and if so, generates the query plan according to the first access constraint; or

[0132] When the query rewrite type is the Join rewrite, the optimizer determines whether the data of the left and right nodes of the join operator exist in the first access constraint, and if so, generates the query plan according to the first access constraint.

[0133] It should be noted that, by analyzing the query statement, the expressions corresponding to the projection matching rewrite and the Join rewrite are determined respectively, and the degree of overlap and / or logical relationship with the target expression defined by the access constraint in the access constraint set is determined based on the expression, and then the degree of overlap and / or the logical relationship can be calculated and rewritten in the query statement using the expression of the access constraint to directly query the specific column in the access constraint.

[0134] It's important to note that the purpose of projection matching rewriting is to ensure that all projection expressions required by a query can be provided by the data in the SAC. Therefore, the optimizer calculates the expressions required for upward projection in the underlying scan based on the logical operator tree generated by the query. It then matches the calculated projections with the expressions in the SAC definition, ensuring that all projection expressions can be calculated using the expressions in the SAC definition.

[0135] It should be noted that the Join rewrite will identify whether the data of the left and right nodes of the join operator contain the count column from SAC. If both nodes do, the product of the count columns of the left and right nodes will be added to the Join output column.

[0136] It should be noted that after SAC includes cross-table mapping, query calculations can be moved forward to the time when SAC data is generated, reducing the amount of calculation during query execution. This helps improve complexity and reduce the amount of SAC data, thereby accelerating query performance.

[0137] 2 , an embodiment of the present invention further discloses a flowchart of a query processing method based on multi-table joins provided by an embodiment of the present application;

[0138] A query processing method based on multi-table joins is used to query data in a data table set scenario having multiple data tables and relationships between the multiple data tables. The method involves a user end, an optimization end, an execution end, and a database, and includes:

[0139] S210: The execution end receives the query plan sent by the optimization end, and the optimization end is configured to send the query plan to the execution end;

[0140] S220, the execution end determines a target access constraint according to the query plan;

[0141] S230, the execution end determines target data in the data table set according to the target access constraint;

[0142] S240, the execution end generates a query result based on the target data;

[0143] S250: The execution end sends the query result to the user end, and the user end is used to receive the query result fed back by the execution end.

[0144] 3 , an embodiment of the present invention further discloses a flowchart of a query processing method based on multi-table joins provided by an embodiment of the present application;

[0145] A query processing method based on multi-table joins is used to query data in a data table set scenario having multiple data tables and relationships between the multiple data tables. The method involves a user end, an optimization end, an execution end, and a database, and includes:

[0146] S310: The user terminal generates a query request in response to a user's demand, and sends the query request to the optimization terminal, which is configured to receive the query request sent by the user terminal.

[0147] S320: The user end receives the query result sent by the execution end, and returns the query result to the corresponding target user. The execution end is used to feed back the query result to the user end.

[0148] Example 1

[0149] on_time table structure:

[0150] The on_time table records flight delay information. It contains over 100 million records, and the FlightNo, DestAirportID, and DelayTime fields contain a large number of duplicate values.

[0151] Airport table structure:

[0152] The airport table records airport information. The airport table has 1000 records.

[0153] There are queries like:

[0154] selectFlightNo,avg(DelayTime)from on_time,airportwhere DestAirportID=AirportID and Location in(alldomesticairports)group by FlightNo;

[0155] Assume there are 1,000 flights and 1 million records satisfying the DestAirportID = AirportID condition. Each DestAirportID corresponds to a maximum of 1,000 FlightNos, meaning the duplication of DestAirportID->FlightNo is 1,000. Furthermore, cross-table fields also have a correlation: DestAirportID->AirportID->Location. For each AirportID, the address Location is also unique.

[0156] After filtering and joining the on_time and airport tables, the preceding query statement is executed using a grouping and aggregation method. First, the 100 million records and the 1,000 records are scanned and joined to generate a larger intermediate result set. The 1 million records that were filtered out are then grouped and aggregated. There are 1,000 groups, and DelayTime is accumulated 1,000 times in each group, for a total of 1 million times.

[0157] To use a single-table query with an access constraint AC, create an AC for the on_time table: {DestAirportID, FlightNo, sum(DelayTime) as SumDelayTime, count}. Create an AC for the airport table: {AirportID, Location, count}. Based on the previous assumption about the data volume, the on_time AC will contain a maximum of 1 million records. Join the records in the airport table to obtain the addresses of all airports, then perform the filtering. The aggregation operation directly uses the SumDelayTime value. Although the computational complexity is reduced compared to the original table, the intermediate result set after the join is still large.

[0158] Access constraints are defined using the multi-table query access constraint (SAC) method: Based on the on_time table and the airport table, the X and Y data sets {DestAirportID, AirportID, Location, FlightNo, sum(DelayTime) as SumDelayTime, count} that meet the condition "DestAirportID = AirportID and Location in (all domestic airports)" can accurately locate the SAC records that meet the condition, avoiding scanning and joining different tables.

[0159] Advantages of the technical solution of this application:

[0160] 1. Added cross-table data dependency capabilities to SAC, reducing the I / O overhead and join calculations required to scan multiple AC tables during queries.

[0161] 2. Based on data dependency, reduce SAC maintenance costs and support real-time data updates.

[0162] As for the device embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0163] 4 , there is shown a structural block diagram of a query processing device based on multi-table joins provided by an embodiment of the present application;

[0164] A query processing device based on multi-table joins, the device is used to query data in a data table set scenario having multiple data tables and relationships between the multiple data tables. The device involves a user end, an optimization end, an execution end, and a database. The user end is used to generate a query request based on user needs and send the query request to the optimization end. The device includes:

[0165] A first calling module 410 is configured to retrieve corresponding metadata from the database according to the query request when the optimization end receives the query request sent by the user end, wherein the metadata includes a data table set and an access constraint set;

[0166] A first generating module 420 is configured to generate, by the optimization end, a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, fields with the same semantics, and logical inclusion relationships;

[0167] A second generation module 430 is configured for the optimization end to generate a query plan according to the first access constraint and a preset query rewriting type, wherein the query rewriting type includes projection matching rewriting and join rewriting;

[0168] The first sending module 440 is used for the optimization end to send the query plan to the execution end; the execution end is used to feed back the query result generated after the query plan is executed to the user end.

[0169] In one embodiment of the present invention, the first generating module 420 includes:

[0170] A first acquisition submodule, configured to, when the cross-table semantics is the foreign key, enable the optimization end to acquire a first target data table in the data table set;

[0171] A first determination submodule, configured for the optimization end to determine a first target value of a preset target field in the first target data table;

[0172] A second acquisition submodule, configured for the optimization end to acquire, in the data table set according to the first target value, a second target value having the same value as the first target value;

[0173] A second determination submodule, configured for the optimization end to determine a second target data table in the data table set according to the second target value;

[0174] The first generating submodule is used by the optimization end to generate the first access constraint according to the preset target field, the first target data table, the first target value, the second target data table and the second target value.

[0175] In one embodiment of the present invention, the first generating module 420 includes:

[0176] A third acquisition submodule is configured to, when the cross-table semantics is the same semantic field, cause the optimization end to acquire at least two of the data tables in the data table set, wherein the two data tables are a third target data table and a fourth target data table;

[0177] A fourth acquisition submodule, configured for the optimization end to respectively acquire a third target field from the third target data table and a fourth target field from the fourth target data table;

[0178] The second generation submodule is used to generate the first access constraint by the optimization end according to the third target data table, the fourth target data table, the third target field and the fourth target field when the third target field and the fourth target field have the same semantics.

[0179] In one embodiment of the present invention, the first generating module 420 includes:

[0180] a fifth acquisition submodule, configured to, when the cross-table semantics is the logical inclusion relationship, cause the optimization end to acquire at least two of the data tables in the data table set, wherein the two data tables are a fifth target data table and a sixth target data table;

[0181] A sixth acquisition submodule, configured for the optimization end to respectively acquire the fifth target field from the fifth target data table and the sixth target field from the sixth target data table;

[0182] The third generation submodule is used to generate the first access constraint by the optimization end based on the fifth target data table, the sixth target data table, the fifth target field and the sixth target field when the semantics of the fifth target field includes the semantics of the sixth target field, or the semantics of the sixth target field includes the semantics of the fifth target field.

[0183] 5 , there is shown a structural block diagram of a query processing device based on multi-table joins provided by an embodiment of the present application;

[0184] A query processing device based on multi-table joins, the device is used to query data in a data table set scenario having multiple data tables and relationships between the multiple data tables, the device involves a user end, an optimization end, an execution end, and a database, and the device includes:

[0185] A second sending module 510 is configured for the execution end to receive the query plan sent by the optimization end, and the optimization end is configured to send the query plan to the execution end;

[0186] A first determination module 520 is configured for the execution end to determine a target access constraint according to the query plan;

[0187] A second determination module 530 is configured for the execution end to determine target data in the data table set according to the target access constraint;

[0188] A third generating module 540 is configured for the execution end to generate a query result based on the target data;

[0189] The third sending module 550 is used for the execution end to send the query result to the user end, and the user end is used to receive the query result fed back by the execution end.

[0190] 6 , a block diagram of a query processing device based on multi-table joins according to an embodiment of the present application is shown;

[0191] A query processing device based on multi-table joins, the device is used to query data in a data table set scenario having multiple data tables and relationships between the multiple data tables, the device involves a user end, an optimization end, an execution end, and a database, and the device includes:

[0192] A fourth sending module 610 is configured for the user end to generate a query request in response to a user's demand and send the query request to the optimization end, and the optimization end is configured to receive the query request sent by the user end;

[0193] The return module 620 is used for the user end to receive the query result sent by the execution end and return the query result to the corresponding target user. The execution end is used to feed back the query result to the user end.

[0194] 7 , a computer device of a query processing method based on multi-table join of the present invention is shown, which may specifically include the following:

[0195] The computer device 12 is a general-purpose computing device. The components of the computer device 12 may include but are not limited to: one or more processors or processing units 16, a system memory 28, and a bus 18 connecting different system components (including the system memory 28 and the processing unit 16).

[0196] The bus 18 represents one or more of several types of bus 18 structures, including a memory bus 18 or memory controller, a peripheral bus 18, an accelerated graphics port, a processor, or a local bus 18 that utilizes any of a variety of bus 18 architectures. Examples of such architectures include, but are not limited to, an Industry Standard Architecture (ISA) bus 18, a Micro Channel Architecture (MAC) bus 18, an Enhanced ISA bus 18, an Audio Video Electronics Standards Association (VESA) local bus 18, and a Peripheral Component Interconnect (PCI) bus 18.

[0197] The computer device 12 typically includes a variety of computer system readable media. These media can be any available media that can be accessed by the computer device 12, including volatile and non-volatile media, removable and non-removable media.

[0198] System memory 28 may include computer system readable media in the form of volatile memory, such as random access memory (RAM) 30 and / or cache memory 32. Computer device 12 may further include other removable / non-removable, volatile / non-volatile computer system storage media. By way of example only, storage system 34 may be configured to read and write to non-removable, non-volatile magnetic media (commonly referred to as a "hard drive"). Although not shown in FIG. 7 , a magnetic disk drive may be provided for reading and writing to a removable non-volatile magnetic disk (e.g., a "floppy disk"), as well as an optical disk drive for reading and writing to a removable non-volatile optical disk (e.g., a CD-ROM, DVD-ROM, or other optical media). In these cases, each drive may be connected to bus 18 via one or more data media interfaces. The memory may include at least one program product having a set (e.g., at least one) of program modules 42 configured to perform the functions of various embodiments of the present invention.

[0199] A program / utility 40 having a set (at least one) of program modules 42 may be stored, for example, in a memory. Such program modules 42 include, but are not limited to, an operating system, one or more application programs, other program modules 42, and program data, each of which, or some combination thereof, may include an implementation of a network environment. The program modules 42 generally perform the functions and / or methods of the embodiments described herein.

[0200] The computer device 12 may also communicate with one or more external devices 14 (e.g., a keyboard, pointing device, display 24, camera, etc.), one or more devices that enable a user to interact with the computer device 12, and / or any device that enables the computer device 12 to communicate with one or more other computing devices (e.g., a network card, a modem, etc.). Such communication may occur via an input / output (I / O) interface 22. Furthermore, the computer device 12 may also communicate with one or more networks (e.g., a local area network (LAN)), a wide area network (WAN), and / or a public network (e.g., the Internet) via a network adapter 20. As shown, the network adapter 20 communicates with the other modules of the computer device 12 via a bus 18. It should be understood that, although not shown in FIG. 7 , other hardware and / or software modules may be used in conjunction with the computer device 12, including, but not limited to, microcode, device drivers, redundant processing units 16, external disk drive arrays, RAID systems, tape drives, and data backup storage systems 34.

[0201] The processing unit 16 executes various functional applications and data processing by running programs stored in the system memory 28, such as implementing the query processing method based on multi-table connection provided by the embodiment of the present invention.

[0202] That is, when the processing unit 16 executes the above program, it is achieved that: when the optimization end receives the query request sent by the user end, the optimization end retrieves the corresponding metadata from the database according to the query request, wherein the metadata includes a data table set and an access constraint set; the optimization end generates a first access constraint based on the data table set and the preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields and logical inclusion relationships; the optimization end generates a query plan based on the first access constraint and the preset query rewriting type, wherein the query rewriting type includes projection matching rewriting and Join rewriting; the optimization end sends the query plan to the execution end; the execution end is used to feed back the query results generated after the query plan is executed to the user end.

[0203] In an embodiment of the present invention, the present invention further provides a computer-readable storage medium having a computer program stored thereon. When the program is executed by a processor, the query processing method based on multi-table join provided in all embodiments of the present application is implemented:

[0204] That is, when the program is executed by the processor, it is implemented as follows: when the optimization end receives a query request sent by the user end, the optimization end retrieves corresponding metadata from the database according to the query request, wherein the metadata includes a data table set and an access constraint set; the optimization end generates a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields and logical inclusion relationships; the optimization end generates a query plan based on the first access constraint and a preset query rewrite type, wherein the query rewrite type includes projection matching rewrite and Join rewrite; the optimization end sends the query plan to the execution end; the execution end is used to feed back the query results generated after the query plan is executed to the user end.

[0205] Any combination of one or more computer-readable media may be used. A computer-readable medium may be a computer signal medium or a computer-readable storage medium. A computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples (a non-exhaustive list) of computer-readable storage media include: an electrical connection having one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In this document, a computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device.

[0206] A computer-readable signal medium may include a data signal propagated in baseband or as part of a carrier wave, which carries computer-readable program code. Such a propagated data signal may take a variety of forms, including, but not limited to, electromagnetic signals, optical signals, or any suitable combination thereof. A computer-readable signal medium may also be any computer-readable medium other than a computer-readable storage medium that can transmit, propagate, or transport a program for use by or in conjunction with an instruction execution system, apparatus, or device.

[0207] The computer program code for performing the operations of the present invention can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, C++, and conventional procedural programming languages ​​such as "C" or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or it can be connected to an external computer (for example, using an Internet service provider to connect through the Internet). The various embodiments in this specification are described in a progressive manner, and each embodiment focuses on the differences from other embodiments. The same and similar parts between the various embodiments can be referenced to each other.

[0208] Although preferred embodiments of the present invention have been described, those skilled in the art may make additional changes and modifications to these embodiments once they become aware of the basic inventive concepts. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments and all changes and modifications that fall within the scope of the embodiments of the present invention.

[0209] Finally, it should be noted that, in this document, relational terms such as first and second, etc., are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. Moreover, the terms "comprises," "comprising," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or terminal device that includes a series of elements includes not only those elements, but also other elements not explicitly listed, or elements inherent to such process, method, article, or terminal device. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of additional identical elements in the process, method, article, or terminal device that includes the element.

[0210] The above is a detailed introduction to a query processing method and device based on multi-table connection provided by the present application. Specific examples are used in this article to illustrate the principles and implementation methods of the present application. The description of the above embodiments is only used to help understand the method of the present application and its core idea. At the same time, for general technical personnel in this field, according to the ideas of the present application, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as a limitation on the present application.

Claims

1. A query processing method based on multi-table connection, characterized in that: The method is used to query data in a data table set scenario with multiple data tables and associations between the multiple data tables. The method involves a user end, an optimization end, an execution end and a database. The user end is used to generate a query request according to user needs and send the query request to the optimization end; The method comprises: When the optimization end receives the query request sent by the user end, the optimization end retrieves corresponding metadata from the database according to the query request, wherein the metadata includes a data table set and an access constraint set; The optimization end generates a first access constraint according to the data table set and preset cross-table semantics, wherein the cross-table semantics includes foreign keys, same-semantic fields, and logical inclusion relationships; The optimization end generates a query plan according to the first access constraint and a preset query rewriting type, wherein the query rewriting type includes projection matching rewriting and Join rewriting; The optimization end sends the query plan to the execution end; the execution end is used to feed back the query results generated after the query plan is executed to the user end.

2. The method according to claim 1, characterized in that The step of generating a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics includes foreign keys, same-semantic fields, and logical inclusion relationships, comprises: When the cross-table semantics is the foreign key, the optimization end obtains a first target data table in the data table set; The optimization end determines a first target value of a preset target field in the first target data table; The optimization end obtains, in the data table set according to the first target value, a second target value having the same value as the first target value; The optimization end determines a second target data table in the data table set according to the second target value; The optimization end generates the first access constraint according to the preset target field, the first target data table, the first target value, the second target data table and the second target value.

3. The method according to claim 1, characterized in that The step of generating a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics includes foreign keys, same-semantic fields, and logical inclusion relationships, comprises: When the cross-table semantics is the same semantic field, the optimization end obtains at least two of the data tables in the data table set, wherein the two data tables are a third target data table and a fourth target data table; The optimization end obtains the third target field in the third target data table and obtains the fourth target field in the fourth target data table respectively; When the third target field and the fourth target field have the same semantics, the optimization end generates the first access constraint according to the third target data table, the fourth target data table, the third target field, and the fourth target field.

4. The method according to claim 1, characterized in that: The step of generating a first access constraint based on the data table set and preset cross-table semantics, wherein the cross-table semantics includes foreign keys, same-semantic fields, and logical inclusion relationships, comprises: When the cross-table semantics is the logical inclusion relationship, the optimization end obtains at least two of the data tables in the data table set, wherein the two data tables are a fifth target data table and a sixth target data table; The optimization end obtains the fifth target field in the fifth target data table and the sixth target field in the sixth target data table respectively; When the semantics of the fifth target field includes the semantics of the sixth target field, or the semantics of the sixth target field includes the semantics of the fifth target field, the optimization end generates the first access constraint based on the fifth target data table, the sixth target data table, the fifth target field and the sixth target field.

5. The method according to claim 1, characterized in that The step of generating a query plan according to the first access constraint and the query rewriting type, wherein the query rewriting type includes the steps of projection matching rewriting and Join rewriting, comprises: When the query rewriting type is the projection matching rewriting, the optimization end determines whether the projection expression in the analysis query statement is obtained by operating the expression in the first access constraint, and if so, generates the query plan according to the first access constraint; or When the query rewrite type is the Join rewrite, the optimization end determines whether the data of the left and right nodes of the join operator exist in the first access constraint, and if so, generates the query plan according to the first access constraint.

6. A query processing method based on multi-table connection, characterized in that: The method is used to query data in a data table set scenario where there are multiple data tables and there are associations between the multiple data tables. The method involves a user end, an optimization end, an execution end, and a database. The method includes: The execution end receives the query plan sent by the optimization end, and the optimization end is used to send the query plan to the execution end; The execution end determines the target access constraint according to the query plan; The execution end determines the target data in the data table set according to the target access constraint; The execution end generates a query result according to the target data; The execution end sends the query result to the user end, and the user end is used to receive the query result fed back by the execution end.

7. A query processing method based on multi-table connection, characterized in that: The method is used to query data in a data table set scenario where there are multiple data tables and there are associations between the multiple data tables. The method involves a user end, an optimization end, an execution end, and a database. The method includes: The user terminal generates a query request in response to a user's demand, and sends the query request to the optimization terminal, and the optimization terminal is used to receive the query request sent by the user terminal; The user end receives the query result sent by the execution end, and returns the query result to the corresponding target user. The execution end is used to feed back the query result to the user end.

8. A query processing device based on multi-table connection, characterized in that: The device is used to query data in a data table set scenario with multiple data tables and multiple data tables associated with each other, and the device involves a user end, an optimization end, an execution end and a database, and the user end is used to generate a query request according to user needs and send the query request to the optimization end; the device includes: A first calling module, configured to retrieve corresponding metadata from the database according to the query request when the optimization end receives the query request sent by the user end, wherein the metadata includes a data table set and an access constraint set; A first generating module, configured for the optimization end to generate a first access constraint according to the data table set and preset cross-table semantics, wherein the cross-table semantics include foreign keys, same-semantic fields, and logical inclusion relationships; A second generation module, configured for the optimization end to generate a query plan according to the first access constraint and a preset query rewriting type, wherein the query rewriting type includes projection matching rewriting and Join rewriting; The first sending module is used for the optimization end to send the query plan to the execution end; the execution end is used to feed back the query result generated after the query plan is executed to the user end.

9. A query processing device based on multi-table connection, characterized in that: The device is used to query data in a data table set scenario where there are multiple data tables and there are associations between the multiple data tables. The device involves a user end, an optimization end, an execution end, and a database. The device includes: A second sending module, configured for the execution end to receive the query plan sent by the optimization end, and the optimization end to send the query plan to the execution end; A first determination module, used by the execution end to determine a target access constraint according to the query plan; A second determination module, configured for the execution end to determine target data in the data table set according to the target access constraint; A third generating module, used for the execution end to generate a query result according to the target data; The third sending module is used for the execution end to send the query result to the user end, and the user end is used to receive the query result fed back by the execution end.

10. A query processing device based on multi-table connection, characterized in that: The device is used to query data in a data table set scenario where there are multiple data tables and there are associations between the multiple data tables. The device involves a user end, an optimization end, an execution end, and a database. The device includes: A fourth sending module, configured for the user end to generate a query request in response to a user's demand, and send the query request to the optimization end, wherein the optimization end is configured to receive the query request sent by the user end; The return module is used for the user end to receive the query result sent by the execution end and return the query result to the corresponding target user. The execution end is used to feed back the query result to the user end.

Images