Query plan construction method and apparatus, electronic device and storage medium
By decomposing and extending the subexpressions of data query expressions, appropriate construction subexpressions are selected, which solves the conflict problem during the construction of query plan in the database management system, and improves query efficiency and consistency.
Patent Information
- Application Number
- PCT/CN2024/083246
- Authority / Receiving Office
- WO · WO
- Patent Type
- Applications
- Current Assignee / Owner
- Priority Date
- 2023-05-19
- Filing Date
- 2024-03-22
- Publication Date
- 2024-11-28
AI Technical Summary
When building a query plan, the operation steps of the existing database management system may conflict with each other, resulting in inconsistent query results with expectations and affecting query efficiency.
By obtaining the keywords in the data query expression, decompose them into basic subexpressions and determine their association relationships, expanding them into equivalent subexpressions, filtering out the subexpressions to be constructed, and using these subexpressions and association relationships to build data queries Plan to ensure that multiple subexpressions do not conflict when constructed.
It effectively improves the efficiency of the construction of query plans, ensures the consistency and performance of query results, and avoids uncertainty and error rates during the query process.
Smart Images

Figure CN2024083246_28112024_PF_FP_ABST
Abstract
Description
Query plan construction method, device, electronic device and storage medium
[0001] This application claims priority to the Chinese patent application filed with the China Patent Office on May 19, 2023, application number 202310575987.9, and application name “Query plan construction method, device, electronic device and storage medium”, all contents of which are incorporated by reference into this application. Technical Field
[0002] The present application relates to the field of computers, and in particular to a query plan construction method, device, electronic device and storage medium. Background Art
[0003] A database management system (DBMS) is a software system used to manage and organize data. It helps users create, manage, store, and retrieve data. When retrieving data, a DBMS can use query optimization techniques to expand data queries and construct query plans based on these expanded data queries. The query plans are then used to retrieve data.
[0004] However, the operation steps in the current query plan may conflict with each other, resulting in the data obtained by the constructed query plan being inconsistent with expectations, affecting query efficiency.
[0005] Summary of the Invention
[0006] The embodiments of the present application provide a query plan construction method, device, electronic device and storage medium, which can facilitate the construction of data query plans and improve query efficiency.
[0007] An embodiment of the present application provides a query plan construction method, which is executed by an electronic device and includes:
[0008] Get data query expressions, which include keywords;
[0009] Based on the keywords, obtain the basic sub-expressions in the data query expression and the association relationship between the basic sub-expressions;
[0010] Expanding the basic sub-expression to obtain an equivalent sub-expression of the basic sub-expression, wherein the basic sub-expression and the equivalent sub-expression of the basic sub-expression have equivalent expression meanings;
[0011] Determine the sub-expression to be constructed from the basic sub-expression and the equivalent sub-expression of the basic sub-expression;
[0012] Build a data query plan based on the sub-expressions to be built and the associated relationships.
[0013] The embodiment of the present application further provides a query plan construction device, which is deployed on an electronic device and includes:
[0014] An acquisition unit, used to acquire a data query expression, where the data query expression includes keywords;
[0015] The key unit is used to obtain the basic sub-expressions in the data query expression and the association relationship between the basic sub-expressions based on the keywords;
[0016] The equivalent unit is used to expand the basic sub-expression to obtain an equivalent sub-expression of the basic sub-expression. The basic sub-expression and the equivalent sub-expression of the basic sub-expression have equivalent expression meanings.
[0017] a determination unit, configured to determine a sub-expression to be constructed from a basic sub-expression and an equivalent sub-expression of the basic sub-expression;
[0018] A construction unit is used to construct a data query plan through sub-expressions to be constructed and associated relationships.
[0019] An embodiment of the present application also provides an electronic device, including a memory storing multiple instructions; the processor loads instructions from the memory to execute the steps in any query plan construction method provided in the embodiment of the present application.
[0020] An embodiment of the present application also provides a computer-readable storage medium, which stores multiple instructions, and the instructions are suitable for a processor to load to execute the steps in any query plan construction method provided in the embodiment of the present application.
[0021] An embodiment of the present application also provides a computer program product, including a computer program / instruction, which, when executed by a processor, implements the steps in any query plan construction method provided in the embodiment of the present application.
[0022] The embodiment of the present application can obtain a data query expression, which includes keywords. At the same time, the data query expression can have multiple basic sub-expressions, and there is an association relationship between each basic sub-expression. Therefore, based on the keywords, the basic sub-expressions in the data query expression and the association relationship between the basic sub-expressions can be obtained. The basic sub-expression is then expanded to obtain an equivalent sub-expression of the basic sub-expression. Since the equivalent sub-expression and the basic sub-expression have equivalent expression meanings, the equivalent sub-expression has an association relationship of the basic sub-expression. The sub-expression to be constructed can be screened out from the basic sub-expression and the equivalent sub-expression. The sub-expression to be constructed can be a basic sub-expression or an equivalent sub-expression. Then, a data query plan is constructed through the sub-expression to be constructed and the association relationship. Multiple sub-expressions to be constructed can construct a data query plan based on the association relationship, so that multiple sub-expressions to be constructed do not conflict with each other when constructing the data query plan. In this way, it is easy to construct a data query plan and improve query performance. BRIEF DESCRIPTION OF THE DRAWINGS
[0023] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present application. For those skilled in the art, other drawings can be obtained based on these drawings without creative work.
[0024] FIG1a is a schematic diagram of a scenario of a query plan construction method provided in an embodiment of the present application;
[0025] FIG1b is a flow chart of a query plan construction method provided in an embodiment of the present application;
[0026] FIG2a is a schematic diagram of the structure of a query system provided in an embodiment of the present application;
[0027] FIG2 b is a schematic structural diagram of a query optimizer provided in an embodiment of the present application;
[0028] FIG2c is a schematic diagram of creating a sub-expression provided in an embodiment of the present application;
[0029] FIG2d is a schematic diagram of obtaining metadata information provided by an embodiment of the present application;
[0030] FIG2e is a schematic diagram of the structure of constructing a data query plan provided in an embodiment of the present application
[0031] FIG2f is a schematic diagram of a process for constructing a data query plan according to an embodiment of the present application;
[0032] FIG3 is a schematic diagram of the structure of a query plan construction device provided in an embodiment of the present application;
[0033] FIG4 is a schematic diagram of the structure of the server provided in an embodiment of the present application. DETAILED DESCRIPTION
[0034] The following will be combined with the drawings in the embodiments of this application to clearly and completely describe the technical solutions in the embodiments of this application. Obviously, the embodiments described are only part of the embodiments of this application, not all of the embodiments. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without making creative efforts are within the scope of protection of this application.
[0035] Embodiments of the present application provide a query plan construction method, device, electronic device, and storage medium.
[0036] The query plan construction device may be integrated into an electronic device, such as a terminal, a server, or the like. The terminal may be a mobile phone, a tablet computer, a smart Bluetooth device, a laptop computer, or a personal computer (PC); the server may be a single server or a server cluster consisting of multiple servers.
[0037] In some embodiments, the query plan construction device can also be integrated into multiple electronic devices. For example, the query plan construction device can be integrated into multiple servers, and the query plan construction method of the present application can be implemented by multiple servers.
[0038] In some embodiments, the server may also be implemented in the form of a terminal.
[0039] For example, referring to Figure 1a, the electronic device can obtain a data query expression, which includes keywords; based on the keywords, obtain basic sub-expressions in the data query expression, and the association relationship between the basic sub-expressions; expand the basic sub-expressions to obtain equivalent sub-expressions of the basic sub-expressions, and the equivalent sub-expressions and the basic sub-expressions have equivalent expression meanings; determine the sub-expressions to be constructed from the basic sub-expressions and the equivalent sub-expressions of the basic sub-expressions; and construct a data query plan through the sub-expressions to be constructed and the association relationship.
[0040] Among them, the data query expression can have multiple basic sub-expressions, and there is an association relationship between each basic sub-expression. The basic sub-expression can be extended through the equivalent sub-expression. Since the equivalent sub-expression and the basic sub-expression have equivalent expression meanings, the equivalent sub-expression has an association relationship with the basic sub-expression. The sub-expression to be constructed can be screened out from the basic sub-expression and the equivalent sub-expression. The sub-expression to be constructed can be a basic sub-expression or an equivalent sub-expression. Multiple sub-expressions to be constructed can construct a data query plan based on the association relationship, so that multiple sub-expressions to be constructed do not conflict with each other when constructing the data query plan. In this way, it is convenient to construct the data query plan and improve the query performance.
[0041] It should be noted that the serial numbers of the following embodiments are not intended to limit the preferred order of the embodiments.
[0042] In this embodiment, a query plan construction method is provided, as shown in FIG1b . The specific process of the query plan construction method may be as follows:
[0043] 110. Obtain a data query expression, where the data query expression includes keywords.
[0044] A data query expression is a grammatical structure used to query and filter data from multiple database management systems. For example, the language of a data query expression can be a simple expression language (EL), a query domain specific language (Query DSL), a Java Persistence API query language (JPQL), an expression language in the Spring framework (Spring Expression Language, SpEL), and so on.
[0045] Keywords are special words or characters used to specify query conditions in a data query expression. A data query expression can contain multiple query conditions. For example, keywords can include "SELECT" for selecting columns from the database, "FROM" for specifying the table to be queried, "WHERE" for specifying filter conditions for query results, "ORDER BY" for sorting query results by a specified column, and so on.
[0046] It's understandable that EL data query expressions can interact with multiple database management systems because they don't access databases directly. Instead, they access databases through Java Database Connectivity (JDBC), a Java interface used to execute SQL statements within Java applications and interact with multiple database management systems. This makes it easy to connect applications to a variety of database management systems (DBMSs) types and versions through EL data query expressions, achieving broader functionality and interoperability. Furthermore, EL data query expressions are portable and cross-platform, running on multiple platforms and application servers.
[0047] The data query expression can be obtained by:
[0048] (1) If the query optimizer runs outside the database management system, multiple database management systems can use one query optimizer. After the user enters a data query command into any database management system, any database management system can convert the data query command into a data query expression and then send the data query expression to the query optimizer.
[0049] (2) If the query optimizer is running in the database management system, after the user inputs a data query command into the database management system, the database management system can convert the data query command into a data query expression, and then transmit the data query expression to the query optimizer;
[0050] Among them, the query optimizer is a component that provides query optimization technology.
[0051] In some embodiments, considering that the types of database management systems may include relational database management systems, non-relational database management systems, in-memory database management systems, distributed database management systems, and data warehouses, in order to enable the query optimizer to be deployed in multiple different database management systems, obtaining a data query expression includes:
[0052] Get data query command, which includes keywords;
[0053] According to the keywords, the data query command is parsed and processed to obtain the syntax tree of the data query command;
[0054] The preset data exchange language is used to convert the syntax tree into a format to obtain a data query expression.
[0055] The data query command is a command entered by a user when querying data from a database management system. For example, the data query command can be a query field entered by the user, a voice command, or a command output by a user-written data query script when it is executed.
[0056] Keywords can correspond to query conditions in data query commands.
[0057] For example, the data query command is to filter out the same data from column a of table T1 and column b of table T2. In this way, the query conditions in the data query command may include querying column a of table T1, querying column b of table T2, and filtering out the same data from column a of table T1 and column b of table T2. The keyword can be used to indicate querying column a of table T1, to indicate querying column b of table T2, and to indicate filtering out the same data from column a of table T1 and column b of table T2.
[0058] The syntax tree represents the query logic of the data query command, wherein the syntax tree includes multiple nodes representing different operations.
[0059] For example, in the syntax tree, the SELECT node represents the operation of querying data, the column1 and column2 nodes represent the columns to be queried, the FROM node indicates which tables to query from, table1 and table2 are the names of these tables, the WHERE node represents the conditions to limit the query results, and so on.
[0060] The preset data interaction language can enable the query optimizer to interact with different types of database management systems. For example, the preset data interaction language can be EL language, Query DSL language, JPQL language, etc.
[0061] 120. Based on the keywords, obtain the basic sub-expressions in the data query expression and the association relationship between the basic sub-expressions.
[0062] Basic subexpressions are used to implement the query conditions indicated by keywords in data query expressions. For example, basic subexpressions can be used to select columns of tables in a database management system for querying data, specify filter conditions for query results, and sort query results by specified columns.
[0063] Relationships indicate the logical order in which basic sub-expressions are executed. For example, they can include parallel, progressive, and AND relationships between basic sub-expressions. The AND relationship is used to restrict multiple query conditions to the same query. Only when all conditions are met will the required data be returned.
[0064] For example, if the data query expression is "SELECT T1.a FROM T1,T2 WHERE T1.a=T2.b ORDER BY T1.a", this data query expression indicates that table join is used to join table T1 and table T2. The join condition is T1.a=T2.b, which means that only the value of column a in table T1 is equal to the value of column b in table T2 will be returned. That is, the join condition T1.a=T2.b filters out the data that meets the query conditions and sorts them according to the value of column T1.a.
[0065] Through "SELECT T1.a", "FROM T1, T2", "WHERE T1.a=T2.b", and "ORDER BY T1.a" in the data query expression, basic sub-expression 1, basic sub-expression 2, and basic sub-expression 3 can be obtained. Basic sub-expression 1 is used to retrieve the data in the a-th column of table T1, basic sub-expression 2 is used to retrieve the data in the b-th column of table T2, and basic sub-expression 3 is used to indicate the inner join (Inner Join (T1.a=T2.b)) between table T1 and table T2, and is used to filter the query results of basic sub-expression 1 and the query results of basic sub-expression 2.
[0066] Among them, there is a parallel relationship between basic sub-expression 1 and basic sub-expression 2, and there is an AND relationship between basic sub-expression 1 and basic sub-expression 2 and basic sub-expression 3 respectively, that is, after basic sub-expression 1 and basic sub-expression 2 obtain query results respectively, the query results of basic sub-expression 1 and basic sub-expression 2 can be filtered by basic sub-expression 3.
[0067] 130. Expand the basic sub-expression to obtain an equivalent sub-expression of the basic sub-expression. The equivalent sub-expression and the basic sub-expression have equivalent expression meanings.
[0068] An equivalent sub-expression can be used to replace a basic sub-expression and has the same query result as the basic sub-expression, but with different query logic. A basic sub-expression corresponds to one or more equivalent sub-expressions.
[0069] The equivalent expression meaning is used to limit the equivalent sub-expression and the basic sub-expression to have the same semantics, so that the query result of the equivalent sub-expression is the same as the query result of the basic sub-expression.
[0070] For example, if the basic sub-expression is a sub-expression that reads (Get) the data in column a of table T1, then the equivalent sub-expression can be a sub-expression that scans (Scan) the data in column a of table T1, or a sub-expression that first scans the data in table T1 and then redistributes (Redistribute) the data in column a of table T1, and so on. In this way, both the basic sub-expression and the equivalent sub-expression can query the data in column a of table T1.
[0071] In some embodiments, in order to facilitate obtaining an equivalent sub-expression, the basic sub-expression is expanded to obtain an equivalent sub-expression of the basic sub-expression, including:
[0072] Obtaining a preset expression set, the preset expression set including multiple preset expressions, and the multiple preset expressions have equivalent expression meanings;
[0073] Based on the base subexpression, an equivalent subexpression is determined from a preset set of expressions.
[0074] The preset expression set is a set of multiple preset expressions with equivalent expression meanings.
[0075] For example, the preset expression set records multiple preset expressions with different query logics, but each preset expression in the preset expression set has the same expression meaning and can obtain the same query result. The preset expression set makes it easy to determine the equivalent sub-expression of the basic sub-expression.
[0076] 140. Determine a sub-expression to be constructed from a basic sub-expression and an equivalent sub-expression of the basic sub-expression.
[0077] The sub-expression to be constructed is a sub-expression whose query cost satisfies a preset condition among the base sub-expression and its equivalent sub-expressions. For example, the sub-expression to be constructed can be the sub-expression with the lowest query cost among the base sub-expression and its equivalent sub-expressions, or any sub-expression whose query cost satisfies the preset condition, and so on.
[0078] In some embodiments, considering that a sub-expression may indicate a table join, which is used to combine data between two or more tables to query information across multiple tables, in order to obtain a sub-expression with a low query cost from a basic sub-expression and equivalent sub-expressions, a sub-expression to be constructed is determined from the basic sub-expression and the equivalent sub-expression of the basic sub-expression, including:
[0079] Get the first condition number and the second condition number, the first condition number is the condition number of the data connection condition associated with the base sub-expression, and the second condition number is the condition number of the data connection condition associated with the equivalent sub-expression of the base sub-expression;
[0080] According to the first conditional quantity and the second conditional quantity, a sub-expression to be constructed is determined from a basic sub-expression and an equivalent sub-expression of the basic sub-expression.
[0081] The data connection condition is used to specify the connection relationship between at least two tables so as to combine the data in at least two tables. The data connection condition can be combined using comparison operators (=, <, >, <=, >=, etc.) and logical operators (AND, OR, etc.).
[0082] It's understandable that there can be multiple join conditions. For example, suppose there are two tables, employee and department, containing employee and department information, respectively. An inner join is used to join the employee and department tables, with two join conditions: employee.department_id = department.id, and employee.city = department.city. This ensures that only employee and department records in the same city and department are returned, and the required employee name, department name, and address information are obtained.
[0083] The first conditional number represents conditional data of a connection relationship between at least two tables as a base sub-expression.
[0084] The second condition number is condition data of the equivalent sub-expression representing the connection relationship between at least two tables.
[0085] For example, Inner Join usually refers to an operation that connects two tables based on a comparison operator (such as the equal sign "="), that is, whether the corresponding rows need to be connected is determined based on whether the corresponding column values in the two tables are equal. Therefore, in the Inner Join operation, there is usually only one or a few data connection conditions.
[0086] An inner hash join uses hash tables to join tables, offering excellent performance when processing large amounts of data. The main steps are to store the data from the two tables to be joined in separate in-memory hash tables and map them to corresponding hash buckets using a hash function. The join is then performed by comparing the data in each hash bucket with the join conditions.
[0087] Since Inner Hash Join requires the establishment of additional data structures such as hash tables, it usually requires more memory and computing resources in actual situations.
[0088] To determine the sub-expression to be constructed from the basic sub-expression and the equivalent sub-expression of the basic sub-expression according to the first conditional quantity and the second conditional quantity:
[0089] When the amount of data in the table is small, the smaller number of conditions can be filtered out from the first number of conditions and the second number of conditions. If the smaller number of conditions is the first number of conditions, the basic sub-expression is used as the sub-expression to be constructed. If the smaller number of conditions is the second number of conditions, the equivalent sub-expression corresponding to the second number of conditions is used as the sub-expression to be constructed.
[0090] When a large amount of data is recorded in a table, the number of conditions with a larger number can be filtered out from the first number of conditions and the second number of conditions. If the number of conditions with a larger number is the first number of conditions, the basic sub-expression is used as the sub-expression to be constructed. If the number of conditions with a larger number is the second number of conditions, the equivalent sub-expression corresponding to the second number of conditions is used as the sub-expression to be constructed. In this way, data queries can be executed efficiently, so that better performance can be provided when processing large amounts of data.
[0091] In some embodiments, considering that multiple expressions may be used to query the same data set, each expression has different query logic, and thus each expression has a different query cost, in order to obtain a sub-expression with a low query cost from a basic sub-expression and an equivalent sub-expression of the basic sub-expression, determining a sub-expression to be constructed from the basic sub-expression and the equivalent sub-expression of the basic sub-expression includes:
[0092] Get metadata information of the data to be queried associated with the basic sub-expression;
[0093] Obtain the data volume of the data to be queried from the metadata information;
[0094] According to the amount of data, the sub-expression to be constructed is determined from the basic sub-expression and the equivalent sub-expression of the basic sub-expression.
[0095] The data to be queried is the data in the table that the basic sub-expression needs to query. For example, if the basic sub-expression is used to query Table A, the data to be queried is the data in Table A.
[0096] Metadata information is data information that describes the data to be queried. For example, metadata information is used to describe the data to be queried, including information such as the data format, size, encoding method, and access protocol.
[0097] Data volume refers to the size of the data to be queried, usually expressed in terms of number of records or file size. For example, the size of the data to be queried can be obtained through metadata information.
[0098] For example, the basic sub-expression is Get(T1), and the equivalent sub-expression of the basic sub-expression is Scan(T1). Get(T1) and Scan(T1) are used to query the data to be queried in table T1.
[0099] Scenarios where the base sub-expression Get(T1) is used include:
[0100] 1. You need to get a single record with a specified row key.
[0101] 2. Only a small amount of data records need to be queried, such as obtaining a user's basic information;
[0102] 3. You need to query multiple versions of a row.
[0103] Scenarios where the equivalent subexpression Scan(T1) is used include:
[0104] 1. Need to query a large number of data records, such as batch statistical analysis data;
[0105] 2. Need to filter or sort according to specific conditions, such as time, value, etc.;
[0106] 3. It is necessary to traverse the entire table or multiple rows of data in a specified range, such as data backup and archiving.
[0107] If the amount of data is small, the basic sub-expression Get(T1) can be used as the sub-expression to be constructed. If the amount of data is large, the equivalent sub-expression Scan(T1) can be used as the sub-expression to be constructed.
[0108] 150. Build a data query plan through the sub-expressions to be built and the associated relationships.
[0109] The data query plan is an executable data query solution constructed based on the association relationship of the sub-expressions to be constructed.
[0110] For example, if there are three basic sub-expressions in the data query expression, basic sub-expression 1, basic sub-expression 2 and basic sub-expression 3, the association relationship between basic sub-expression 1 and basic sub-expression 2 can be a parallel relationship, and the association relationship between basic sub-expression 1 and basic sub-expression 2 and basic sub-expression 3 can be an AND relationship.
[0111] Among them, the sub-expression to be constructed can be a basic sub-expression or an equivalent sub-expression of a basic sub-expression, that is, there is a sub-expression 1 to be constructed determined from basic sub-expression 1 and the equivalent sub-expression 1 of basic sub-expression 1, a sub-expression 2 to be constructed determined from basic sub-expression 2 and the equivalent sub-expression 2 of basic sub-expression 2, and a sub-expression 3 to be constructed determined from basic sub-expression 3 and the equivalent sub-expression 3 of basic sub-expression 3, and there is an association relationship between each sub-expression to be constructed and other sub-expressions to be constructed.
[0112] Since the equivalent sub-expression and the basic sub-expression have equivalent expression meanings, the association relationship between the equivalent sub-expression 1 and the basic sub-expression 2 or the equivalent sub-expression 2 is a parallel relationship; the association relationship between the equivalent sub-expression 1 and the basic sub-expression 3 or the equivalent sub-expression 3 can be an AND relationship; the association relationship between the equivalent sub-expression 2 and the basic sub-expression 3 or the equivalent sub-expression 3 can be an AND relationship.
[0113] If the association relationship between sub-expression 1 to be constructed and sub-expression 2 to be constructed is a parallel relationship, and the association relationship between sub-expression 1 to be constructed and sub-expression 2 to be constructed and sub-expression 3 to be constructed is an AND relationship respectively, then based on the association relationship between sub-expression 1 to be constructed and sub-expression 2 to be constructed being a parallel relationship, control sub-expression 1 to be constructed and sub-expression 2 to be constructed to construct query methods independently, and then based on the association relationship between sub-expression 1 to be constructed and sub-expression 2 to be constructed and sub-expression 3 to be constructed being an AND relationship respectively, construct a query plan for sub-expression 3 to be constructed to filter the query results of sub-expression 1 to be constructed and sub-expression 2 to be constructed.
[0114] In some embodiments, considering that a sub-expression can construct multiple data query plans with the same query function, in order to screen a data query plan with a low query cost from multiple data query plans, a data query plan is constructed using the sub-expressions to be constructed and the association relationship, including:
[0115] Obtain the current query optimization request associated with the sub-expression to be constructed from the data query expression;
[0116] Build the initial subquery plan through the sub-expression to be built;
[0117] Determine a subquery plan to be processed from an initial subquery plan based on a current query optimization request and an association relationship associated with the subexpression to be constructed;
[0118] The sub-query plans to be processed are merged to obtain the data query plan.
[0119] Among them, the current query optimization request is a query optimization request composed of query conditions associated with the sub-expression to be constructed in the data query expression. The query conditions can be the data distribution, index type, etc. associated with the sub-expression to be constructed in the data query expression. The current query optimization request can be used to optimize the data query plan constructed by the sub-expression to be constructed.
[0120] For example, the current query optimization request may be a request for quickly obtaining the data to be queried, a request for reducing the query cost, a request for satisfying a query speed and query cost within a certain range, and so on.
[0121] The initial subquery plan may include multiple unoptimized subquery plans constructed for the sub-expression to be constructed. For example, the multiple unoptimized subquery plans include subquery plans with high query costs, subquery plans with low query costs, subquery plans with slow query speeds, subquery plans with fast query speeds, and so on.
[0122] The subquery plan to be processed may be an initial subquery plan obtained from the initial subquery plan based on the association relationship and meeting the current query optimization request.
[0123] For example, based on a data query expression, sub-expression 1 to be constructed, sub-expression 2 to be constructed, and sub-expression 3 to be constructed are determined. Sub-expression 1 to be constructed is used to query table T1, sub-expression 2 to be constructed is used to query the data in column b of table T2, and sub-expression 3 to be constructed is used to filter data obtained from sub-expression 1 to be constructed and sub-expression 2 to be constructed. The data query expression also includes sorting according to column a of table T1. The association relationship between sub-expression 1 to be constructed and sub-expression 2 to be constructed is a parallel relationship between the basic sub-expressions, and the association relationship between sub-expression 1 to be constructed and sub-expression 2 and sub-expression 3 to be constructed is an AND relationship.
[0124] The current query optimization request associated with sub-expression 1 to be constructed can be used to enable sub-expression 1 to quickly obtain query table T1 and sort the data queried from table T1 according to column a of the table. The current query optimization request associated with sub-expression 2 to be constructed can be used to enable sub-expression 2 to quickly obtain data in column b of query table T2. The current query optimization request associated with sub-expression 3 to be constructed can be used to merge the sorted (sorted according to column a of table T1) filtered data collected from all database nodes for sub-expression 3 to be constructed.
[0125] The initial subquery plan constructed by the sub-expression 1 to be constructed may include Get(T1), Scan(T1), Scan(T1)+Sort(T1.a), and the initial subquery plan constructed by the sub-expression 2 to be constructed may include Scan(T2)+Redistribute(T2.b), Get(T2.b).
[0126] According to the current query optimization request and the association relationship associated with the sub-expression to be constructed, the sub-query plan to be processed is determined from the initial sub-query plan, which can be:
[0127] Due to the parallel relationship, the initial subquery plans constructed by sub-expression 1 to be constructed and sub-expression 2 to be constructed can be independently filtered. Through the current query optimization request associated with sub-expression 1 to be constructed, Scan(T1) can be filtered out from the initial subquery plan Get(T1), Scan(T1), Scan(T1)+Sort(T1.a). And through the current query optimization request associated with sub-expression 2 to be constructed, Scan(T2)+Redistribute(T2.b) can be filtered out from the initial subquery plan Scan(T2)+Redistribute(T2.b), Get(T2.b).
[0128] According to the AND relationship between sub-expression 1 to be constructed and sub-expression 2 to be constructed and sub-expression 3 to be constructed, since sub-expression 1 to be constructed does not sort the data in table T1 according to the a-th column of the table, the initial sub-plan for constructing sub-expression 3 to be constructed can be to sort the data after Inner Hash Join (T1.a=T1.b) according to the a-th column in table T1 to realize the data distribution in the data query expression.
[0129] That is, the initial subquery plan constructed by the subexpression 3 to be constructed can be Inner Hash Join(T1.a=T1.b)+Sort(T1.a)+GatherMerge(T1.a), Inner Hash Join(T1.a=T1.b)+Gather(T1.a)+Sort(T1.a), and so on, where the GatherMerge operator is used to collect sorted data from all segments of the database to the master node, and Gather is used to collect data from all segments to the master node. Through the current query optimization request associated with the subexpression 3 to be constructed, one can be selected from Inner Hash Join(T1.a=T1.b)+Sort(T1.a)+GatherMerge(T1.a), Inner Hash Join(T1.a=T1.b)+Gather(T1.a)+Sort(T1.a), etc. as the subquery plan to be processed.
[0130] Merge the pending subquery plan Scan(T1) constructed by the pending subexpression 1, the pending subquery plan Scan(T2)+Redistribute(T2.b) constructed by the pending subexpression 2, and the pending subquery plan Inner Hash Join(T1.a=T1.b)+Sort(T1.a)+GatherMerge(T1.a) constructed by the pending subexpression 3 to obtain a data query plan.
[0131] In some embodiments, in order to enable an expression to construct a data query plan, an initial sub-query plan is constructed using the sub-expression to be constructed, including:
[0132] Get the query operator associated with the sub-expression to be constructed;
[0133] Build the connection relationship between query operators based on the sub-expressions to be built;
[0134] Control query operators to arrange according to the join relationship to obtain the initial subquery plan.
[0135] The query operators may support obtaining the required data from the database, for example, query operators may include Get, Scan, Sort, GatherMerge, Redistribute, Replicate, and the like.
[0136] Joins are used to restrict the order in which query operators are executed. For example, if sub-expression 2 is used to retrieve the data in column b of query table T2, then sub-expression 2 is associated with multiple query operators (Get, Scan, and Redistribute). The joins between the query operators constructed by sub-expression 2 can be Scan followed by Redistribute, or just Get. Arranging the query operators according to the joins yields the initial sub-query plan Scan(T2)+Redistribute(T2.b), Get(T2.b), or Get(T2.b).
[0137] In some embodiments, to calculate the query cost of a data query plan constructed by an expression, a sub-query plan to be processed is determined from an initial sub-query plan according to a current query optimization request and an association relationship associated with the sub-expression to be constructed, including:
[0138] According to the current query optimization request, obtain metadata information of the query data associated with the sub-expression to be constructed;
[0139] Analyze the computational effort required to execute the initial subquery plan using metadata information about the data to be queried.
[0140] The subquery plan to be processed is determined from the initial subquery plan based on the computational effort and the association relationship.
[0141] Metadata information describes the data being queried. For example, metadata records various attributes and characteristics of the data, such as data type, data source, data storage method, and data access permissions. Data attributes include those that describe tables, such as table name, column name, column data type, column constraints, index information, and data size.
[0142] The computational effort is the computational operations and resource consumption required to execute the initial subquery plan, which can be understood as the query cost. For example, the computational effort can include the size or time of scanning data files, the size or time of reading indexes, and so on.
[0143] The association relationship between sub-expression 1 to be constructed and sub-expression 2 to be constructed is a parallel relationship between sub-expressions. The association relationship between sub-expression 1 to be constructed and sub-expression 2 and sub-expression 3 to be constructed is an AND relationship. Based on the computational complexity and the association relationship, the sub-query plan determined from the initial sub-query plan can be:
[0144] Since the association relationship between the sub-expression 1 to be constructed and the sub-expression 2 to be constructed is a parallel relationship, the sub-query plan to be processed can be directly filtered from the initial sub-query plan constructed by the sub-expression 1 to be constructed and the sub-expression 2 to be constructed based on the calculation amount.
[0145] Since the association relationship between sub-expression 1 to be constructed and sub-expression 2 to be constructed and sub-expression 3 to be constructed is an AND relationship, when constructing the initial subquery plan by filtering sub-expression 3 to be constructed through calculation amount, it is also necessary to consider sub-query plan 1 to be processed constructed by sub-expression 1 to be constructed and sub-query plan 2 to be processed constructed by sub-expression 2 to be constructed, so as to avoid overlapping plans between sub-query plan 3 to be processed and sub-query plan 1 to be processed and sub-query plan 2 to be processed, or to avoid incomplete execution of all query conditions by sub-query plan 1 to be processed, sub-query plan 2 to be processed and sub-query plan 3 to be processed.
[0146] In some embodiments, to ensure that the data query plan constructed by each sub-expression does not have conflicting or duplicate logic, the sub-expression to be constructed includes a first expression and a second expression, the first expression is used to query the data to be queried, and the second expression is used to filter the data to be queried. Based on the current query optimization request and the association relationship associated with the sub-expression to be constructed, the sub-query plan to be processed is determined from the initial sub-query plan, including:
[0147] Determine, according to the current query optimization request associated with the first expression, a first subquery plan from the first initial subquery plan, where the first initial subquery plan is an initial subquery plan constructed using the first expression;
[0148] Based on the current query optimization requests satisfied by the first subquery plan, determining unsatisfied requests from the current query optimization requests associated with the first expression;
[0149] Determine a final optimization request from the current query optimization requests associated with the second expression according to the unsatisfied requests and the association relationship, where the final optimization request includes the unsatisfied requests;
[0150] According to the final optimization request, a second subquery plan is determined from the second initial subquery plan, where the second initial subquery plan is an initial subquery plan constructed using the second expression, and the subquery plans to be processed include the first subquery plan and the second subquery plan.
[0151] The first expression is used to query the data to be queried. For example, the first expression can be the above-mentioned sub-expression 1 to be constructed and sub-expression 2 to be constructed, where sub-expression 1 to be constructed is used to query table T1, and sub-expression 2 to be constructed is used to query the data in column b of table T2.
[0152] The current query optimization request associated with the first expression is used to filter an initial subquery plan required for retrieving data to be queried.
[0153] The first initial subquery plan is an initial subquery plan constructed by the first expression and is used to query the data to be queried. The first initial subquery plan constructed by the first expression may be one or more.
[0154] The first sub-query plan is a first initial sub-query that satisfies a query cost condition based on a current query optimization request screening associated with the first expression.
[0155] The current query optimization request satisfied by the first subquery plan is a query condition that can be satisfied when the first subquery plan is executed.
[0156] An unsatisfied request is a query optimization request for which the first subquery plan in the current query optimization request associated with the first expression is not fulfilled.
[0157] For example, when the first expression is the sub-expression 1 to be constructed, it can be seen from the above example that the current query optimization request associated with the sub-expression 1 to be constructed can be used to enable the sub-expression 1 to be constructed to quickly obtain the query table T1, and sort the data queried from the table T1 according to the a-th column of the table. If the first sub-query plan constructed by the first expression based on the current query optimization request associated with the first expression is Scan(T1), then the current query optimization request satisfied by the first sub-query plan only includes obtaining the query table T1, and the unsatisfied request is to sort the data queried from the table T1 according to the a-th column of the table.
[0158] The final optimization request is a current query optimization request associated with the second expression obtained based on the unsatisfied request, so that all query conditions in the data query expression can be realized through the query optimization request satisfied by the final optimization request and the first subquery plan.
[0159] For example, as can be seen from the above example, when the second expression is the sub-expression 3 to be constructed, the current query optimization request associated with the sub-expression 3 to be constructed can be used to merge the sorted and filtered data collected by the sub-expression 3 to be constructed from all database nodes (sorted according to the ath column of table T1). Since the first sub-query plan constructed by the sub-expression 1 to be constructed can only obtain the query table T1, the data queried from table T1 is not sorted according to the ath column of the table (the request is not satisfied), so the current query optimization request associated with the second expression needs to include the unsatisfied request to realize the query condition in the data query expression.
[0160] The second initial subquery plan is an initial subquery plan constructed by using the second expression.
[0161] The second subquery plan is a second initial subquery that meets the query cost conditions selected according to the final optimization request.
[0162] In some embodiments, in order to shorten the time of constructing a data query plan, the method provided in the embodiment of the present application further includes:
[0163] Obtain the historical query optimization request associated with the sub-expression to be constructed, and the historical query plan corresponding to the historical query optimization request;
[0164] Determine, from the historical query optimization requests, a pending historical request corresponding to the current query optimization request;
[0165] If there are pending historical requests, the historical query plan corresponding to the pending historical request is used as the pending sub-query plan;
[0166] If there is no pending historical request, the step of constructing an initial subquery plan using the sub-expression to be constructed is triggered.
[0167] The historical query optimization requests are query optimization requests that have been used by the sub-expression to be constructed in a historical time period.
[0168] The historical query plan is a query plan constructed based on historical query optimization requests for the sub-expression to be constructed.
[0169] The pending historical request is the same historical query optimization request as the current query optimization request.
[0170] For example, when constructing a pending subquery plan for the same sub-expression to be constructed, it may face multiple different query optimization requests in different query time periods. If the current query optimization request is the same as the historical query optimization request, that is, there is a pending historical request, then the historical query plan corresponding to the pending historical request will be used as the pending subquery plan. If there is no pending historical request, the initial subquery plan will be directly constructed through the sub-expression to be constructed, and then the initial subquery plan will be filtered through the current query optimization request to obtain the pending subquery plan.
[0171] In some embodiments, in order to facilitate obtaining historical query optimization requests associated with the sub-expression to be constructed, and historical query plans corresponding to the historical query optimization requests, the following steps are included:
[0172] Obtain a hash table associated with the sub-expression to be constructed. The hash table includes historical query optimization requests, as well as first tags and plan retrieval links associated with the historical query optimization requests. The first tag is a hash value obtained by calculating the historical query optimization request using a hash function.
[0173] Determine the pending historical requests corresponding to the current query optimization request from the historical query optimization requests, including:
[0174] Determine a second tag, where the second tag is a hash value obtained by calculating the current query optimization request through a hash function;
[0175] Determining, from the historical query optimization requests, a pending historical request corresponding to the current query optimization request based on the first tag and the second tag;
[0176] If there are pending historical requests, the historical query plan corresponding to the pending historical request is used as the pending subquery plan, including:
[0177] If there is a pending historical request, the plan retrieval link associated with the pending historical request is used to retrieve the historical query plan corresponding to the historical query optimization request as the pending sub-query plan.
[0178] Among them, the hash table can map the hash value with the historical query optimization request, and the historical query optimization request carries the historical query plan, so as to quickly filter the pending historical requests that are the same as the current query optimization request from the historical query optimization request. In this way, the historical query plan corresponding to the pending historical request can be obtained.
[0179] The first tag is the hash value obtained after the historical query optimization request is calculated using a hash function.
[0180] The plan retrieval link is a link for retrieving the query optimization plan used by the pending historical request.
[0181] The second tag is the hash value obtained by calculating the current query optimization request using the same hash function.
[0182] For example, since the first label is the hash value obtained after the historical query optimization request is calculated by the hash function, and the second label is the hash value obtained after the current query optimization request is calculated by the hash function, it is convenient to quickly find the label with the same hash value as the second label from the first label through the second label, so as to quickly treat the historical query optimization request of the label as the same historical request to be processed as the current query optimization request, avoiding traversing the historical query optimization requests.
[0183] In some embodiments, considering that data may be distributed and stored in multiple nodes, in order to quickly obtain query data, after constructing a data query plan using the sub-expressions to be constructed and the association relationships, the following steps are further included:
[0184] Determine the query instructions corresponding to the data query plan, as well as the copy instructions of the query instructions;
[0185] The replica instruction is sent to multiple data storage nodes of the database, so that the data storage nodes return the query data according to the replica instruction.
[0186] The query instruction is a representation of a data query plan so that the database can return query data through the query instruction. The copy instruction is an instruction obtained by copying the query instruction.
[0187] The data storage node is a node that stores data in the database, and each data storage node returns data according to the received replica instruction.
[0188] For example, as can be seen from the above, sub-expression 3 to be constructed is used to filter data from the data obtained from sub-expression 1 to be constructed and sub-expression 2 to be constructed. In this way, after receiving the data returned by each data storage node according to the copy instruction, the returned data is filtered according to sub-expression 3 to be constructed.
[0189] It is understandable that the data queried through the data query plan can be applied to fields such as data analysis, such as advertising content query, financial risk control analysis, information recommendation, etc.
[0190] In some embodiments, when querying for advertising content, advertisements can be quickly queried through a data query plan and displayed in the content played by the application.
[0191] For example, it is necessary to display a specific type of advertisement in the content played by an application. Considering that the query optimizer provided by the relevant technology adopts multi-stage query optimization, each optimization stage is optimized independently, resulting in possible conflicts in the optimization strategies of each stage, leading to high uncertainty and error rate in the query process. Therefore, when querying for the target type of advertisement through the query optimizer provided by the relevant technology, its query efficiency is low.
[0192] In order to improve the efficiency of querying specific types of advertisements, the current sub-expression in the data query expression can be expanded through equivalent sub-expressions, and sub-expressions to be constructed with high query efficiency can be filtered out from the equivalent sub-expressions and the current sub-expressions, and multiple sub-expressions to be constructed can be used to construct a data query plan based on the association relationship between the current sub-expressions, avoiding conflicting or overlapping steps in the data query plan, further speeding up the query efficiency of specific types of advertisements. In this way, specific types of advertisements can be quickly displayed in the content played by the application.
[0193] In some embodiments, data query plan construction can also be applied to the field of financial risk control analysis.
[0194] For example, when a user analyzes a financial product, financial risk control analysis can quickly query data related to the financial product through the data query plan constructed by this application, and recommend the data related to the financial product to the user.
[0195] In some embodiments, data query plan construction can also be applied to the field of information recommendation.
[0196] For example, when a user searches for information, the data query plan constructed by this application can quickly search for information and recommend the search information to the user.
[0197] As can be seen from the above, the embodiment of the present application can obtain a data query expression, which includes keywords. At the same time, the data query expression can have multiple basic sub-expressions, and there is an association relationship between each basic sub-expression. Therefore, based on the keywords, the basic sub-expressions in the data query expression and the association relationship between the basic sub-expressions can be obtained. The basic sub-expression is then expanded to obtain an equivalent sub-expression of the basic sub-expression. Since the equivalent sub-expression and the basic sub-expression have equivalent expression meanings, the equivalent sub-expression has an association relationship of the basic sub-expression. The sub-expression to be constructed can be screened out from the basic sub-expression and the equivalent sub-expression. The sub-expression to be constructed can be a basic sub-expression or an equivalent sub-expression. Then, a data query plan is constructed through the sub-expression to be constructed and the association relationship. Multiple sub-expressions to be constructed can construct a data query plan based on the association relationship, so that multiple sub-expressions to be constructed do not conflict with each other when constructing the data query plan. In this way, it is easy to construct a data query plan and improve query performance.
[0198] The method described in the above embodiment will be further described below.
[0199] In this embodiment, the method of the embodiment of the present application will be described in detail by taking the query system as an example.
[0200] As shown in FIG2a , a query system includes a database management system and a query optimizer. The query optimizer is located outside the database management system and is used to construct a data query plan. The database management system specifically includes:
[0201] A parser, configured to perform syntax analysis on a received data query command to obtain a syntax tree of the data query command;
[0202] The query converter is used to receive the syntax tree sent by the parser and convert the syntax tree into a format using a preset data exchange language to obtain a data query expression so that the query optimizer can construct a data query plan based on the data query expression.
[0203] The metadata provider is used to receive a request for obtaining metadata information sent by the query optimizer when the query optimizer constructs a data query plan;
[0204] The metadata provider is the interface for exchanging metadata information between the query optimizer and the database management system. The metadata provider component is a plug-in specific to the database management system and is used to retrieve metadata information from the database management system.
[0205] The directory is used to receive the acquisition request sent by the metadata provider and send the acquisition request to the metadata exchanger through the metadata provider, so that the acquisition request can obtain metadata information from the directory through the metadata exchanger and send the metadata information to the parser;
[0206] The directory is a database in a database management system that stores metadata information, and the metadata information is data information that describes the data in the database.
[0207] Metadata Exchanger: Exchanges metadata using a pre-defined data exchange language (EL). EL is an XML-formatted data exchange language that contains metadata information from database management systems, such as table definitions and operator definitions. Using EL, you can interact with different back-end database management systems and retrieve the required metadata. The Metadata Exchanger can also extract metadata from database systems and convert it into EL, decoupling the query optimizer from the database management system.
[0208] Explicit Data Exchange Language (EL): This refers to a framework for exchanging information between the query optimizer and the database management system. Separating the query optimizer from the database management system requires building a communication mechanism for processing queries. This framework uses an XML-based language to encode the necessary information required for communication, such as input queries, output plans, and metadata. The EL framework uses a simple communication protocol for obtaining metadata information and sending data query plans.
[0209] Plan converter, used to convert data query plans into query instructions;
[0210] The executor is used to receive the query instructions sent by the plan converter and return the query data according to the query instructions.
[0211] The executor performs the following: dispatching a copy of the final query instruction (replica instruction) to each segment (data storage node). During distributed query execution, the assigned executor on each segment acts as both a sender and receiver of data. For example, a Redistribute(T2.b) instance running on segment S sends tuples on S to other segments based on the hash value of T2.b, and also receives tuples from other Redistribute(T2.b) instances on other segments.
[0212] Here, Redistribute(T2.b) indicates the b-th column of table T2.
[0213] The input to the query optimizer in Figure 2a is a data query expression (EL query). The output of the query optimizer is a data query plan (EL plan). When the query optimizer constructs the data query plan, it can query the database management system to obtain metadata information (such as table definitions). The query optimizer abstracts metadata access details by allowing the database management system to register a metadata provider, which is responsible for serializing the metadata information into an EL query and sending it to the query optimizer. Metadata information can also be queried from a file containing metadata objects serialized in the EL language format. The database management system needs to include a converter that consumes / emit data in the EL format. The query converter converts the syntax tree into a data query expression, while the plan converter converts the data query plan into an executable plan. The implementation of this plan converter is completed entirely outside the optimizer, which allows multiple database management systems to use the query optimizer by providing appropriate plan converters.
[0214] By locating the query optimizer outside the database management system, the query optimizer architecture can be highly extensible, and all components in the query optimizer and the database management system can be replaced and configured separately.
[0215] As shown in Figure 2b, the query optimizer specifically includes:
[0216] Memo, used to encode the data query plan constructed by the query optimizer in a compact memory data structure and store it in the memo;
[0217] The structure of the memo consists of a set of containers called expression groups, each of which contains logically equivalent expressions. A memo expression group captures different subquery expressions of a data query expression (such as a filter on a table or a table join between two tables). The group members of an expression group (base subexpressions and equivalent subexpressions of base subexpressions) implement the same data query in different logical ways (such as different join orders). The base subexpressions or equivalent subexpressions of base subexpressions within each expression group are constructed using query operators. The recursive structure of the memo allows for compact encoding of large amounts of data, allowing for the construction of data query plans.
[0218] A searcher is used to use a search mechanism to construct an initial subquery plan through the sub-expressions to be constructed in the expression group, and estimate a subquery plan to be processed with the minimum query cost from the initial subquery plan;
[0219] A task scheduler is used to start the search mechanism. The task scheduler creates parallel work units to perform the three main steps of query optimization: exploration (generating equivalent logical expressions), realization (building initial subquery plans), and optimization (optimizing initial subquery plans), where the required physical properties (such as sort order) are enforced.
[0220] Optimization tools are used to provide search mechanisms for search engines. Optimization tools include:
[0221] A transformation component, used for determining equivalent sub-expressions, where the equivalent sub-expressions and the basic sub-expressions have equivalent expression meanings;
[0222] For example, plan alternatives are generated by applying transformation rules that can produce equivalent logical expressions (e.g., Inner Join(T1, T2) → Inner Join(T2, T1)) or physical implementations of existing sub-expressions (e.g., Join(T1, T2) → Hash Join(T2, T1)). The results of applying transformation rules are copied to the memo, which may result in the creation of new expression groups and / or the addition of new expressions to existing expression groups. Each transformation rule is a separate component that can be explicitly activated and deactivated in the system configuration.
[0223] A join is used to combine rows from two or more tables based on specific conditions, generating a larger new table that contains all the information from the source tables. A hash join uses a hash function to map the two tables to several buckets, then performs a join operation on each bucket and finally merges the join results.
[0224] Enforcement attributes, used to insert the physical attributes required for enforcement into the initial subquery plan;
[0225] The query optimizer includes an extensible framework for describing query requirements and plan characteristics based on formal property specifications. Properties come in various types, including logical properties (e.g., output columns), physical properties (e.g., sort order and data distribution), and scalar properties (e.g., columns used in input conditions). When the query optimizer constructs a data query plan, each query operator may request specific properties from the sub-expression being constructed. The optimized initial sub-query plan (sub-query plan) may automatically satisfy the current query optimization request associated with the sub-expression being constructed (required properties, such as an index scan plan providing sorted data). However, in many cases, it is necessary to insert certain properties into the initial sub-query plan through enforcement properties (e.g., if the initial sub-query plan contains a Sort operation, a Sort operator needs to be inserted into the plan). The framework allows each query operator to control the enforcement settings based on the sub-query plan properties and the query operator's local behavior, ensuring that the query data meets specific requirements, such as the order of output columns and data distribution.
[0226] Metadata cache, used to cache metadata information of data to be queried;
[0227] Query cost estimation is used to estimate the query cost of the initial subquery plan constructed by the searcher through the metadata information in the metadata cache to obtain the query cost of the initial subquery plan.
[0228] For example, since metadata (such as table definitions) rarely changes, sending metadata information for each query would incur a large overhead. Therefore, the query system caches metadata information on the query optimizer side and only queries metadata information from the catalog when the metadata information cannot be found in the cache or has changed.
[0229] For example, the query language input by the user is converted into Structured Query Language (SQL) (i.e., a data query command), where SQL is a standard computer language for processing relational databases. The data query command may be (SELECT T1.a FROM T1, T2 WHERE T1.a=T2.b ORDER BY T1.a), where the distribution of table T1 is grouped according to the hash value of column a in table T1, which can be expressed as Hashed(T1.a), and the distribution of table T2 is grouped according to the hash value of column a in table T1, which can be expressed as Hashed(T2.a). The following example illustrates how the query optimizer constructs a data query plan:
[0230] (1) Using a preset data exchange language, the data query command is converted into a data query expression in EL format.
[0231] For example, the data query command is converted into EL format, which contains the required output columns, sorting columns, data distribution, and query logic in XML format. Metadata information (such as table and operator definitions) is decorated as a metadata identifier (ID) to allow the query optimizer to request more information when building a data query plan. The metadata ID is a unique identifier consisting of a database system identifier, an object identifier, and a version number. The data query expression in EL format is sent to the query optimizer, parsed and converted into an in-memory logical expression tree, and then copied to the memo.
[0232] (2) According to the keywords, basic sub-expressions in the data query expression and the association relationship between the basic sub-expressions are obtained.
[0233] For example, as shown in Figure 2c, through the data query command (SELECT T1.a FROM T1,T2 WHERE T1.a=T2.b ORDER BY T1.a), the keyword retrieval table T1, retrieval table T2 and inner join (T1.a=T2.b)) in the data query expression can be obtained, and three basic sub-expressions are created through the retrieval table T1, retrieval table T2 and inner join (T1.a=T2.b).
[0234] For simplicity, the join conditions are omitted. Expression group 0 (Group0) is called the root group because it corresponds to the root of the logical plan. Dependencies between operators in the logical plan are captured as references between expression groups. That is, the basic sub-expressions corresponding to the Inner Join belong to the root group, and the basic sub-expressions corresponding to tables T1 and T2 belong to sub-groups. For example, Inner Join [T1, T2] means that expression group 1 (Group1) and expression group 2 (Group2) are joined as sub-groups. Query optimization is performed as follows:
[0235] (1) Exploration: triggering the transformation rules that generate other equivalent logical plan expressions (i.e., determining equivalent sub-expressions, which have equivalent expression meanings to the corresponding basic sub-expressions).
[0236] For example, Inner Join[T1,T2] is generated as Inner Join[T2,T1]. The newly generated equivalent expression is added to the existing root group and may create a new expression group. The memo structure has a built-in duplicate detection mechanism based on the expression topology to detect and eliminate any duplicate expressions created by different transformations.
[0237] (2) Derived Statistics: At the end of the exploration, the memo maintains the complete logical space of the given query. The query optimizer's derived statistics mechanism is then triggered to calculate the statistical objects of the memo group. The statistical objects are mainly used to calculate the column histogram of cardinality and data skewness (i.e., metadata information of the data to be queried). Derived statistics are performed on a compact memo structure to avoid expanding the search space. The system selects the expression with the highest reliable statistics to calculate the derived statistics for the sub-expression group to be constructed. The statistical calculation is based on the sub-expression to be constructed.
[0238] For example, an Inner Join expression (basic sub-expression) with fewer join conditions (the first number of conditions) has a lower query cost than another equivalent Inner Join expression (equivalent sub-expression) with more join conditions (the second number of conditions) (this situation may occur when generating multiple join orders). Because the greater the number of join conditions, the higher the propagation and amplification errors may be. Since confidence scores need to be aggregated on all nodes of a given sub-expression to be constructed, calculating the confidence score of the cardinality is very challenging. After selecting the sub-expression to be constructed with the lowest query cost in the root group, the system recursively triggers derived statistics on the sub-groups of the sub-expression to be constructed. Finally, by combining the statistical objects of the sub-groups, a statistical object of the root group (metadata information of the data to be queried) is constructed.
[0239] Figure 2d shows the derived statistics mechanism for the running example. A top-down traversal begins, where the sub-expression of the root group requests statistics (metadata information about column a in table T1 (T1.a) and column b in table T2 (T2.b)) from the sub-expressions of its child groups (Group1 and Group2). For example, an inner join (T1.a = T2.b) requests histograms for T1.a and T2.b. The requested histograms are loaded on demand from the catalog via the metadata provider, parsed into EL queries (data query expressions), and stored in the metadata cache to service future requests. Next, a bottom-up traversal is performed, combining the child statistics objects into the statistics object for the root group (metadata information about the data being queried). Because the join condition may affect the column histograms, this combines the (possibly modified) histograms of T1.a and T2.b. The constructed statistics objects are attached to a single group and can be incrementally updated during optimization (for example, by adding new histograms). This is crucial to keeping the cost of derived statistics manageable.
[0240] (3) Implementation: Triggering transformation rules that create the physical implementation of a logical expression. For example, triggering a scan rule to generate a logical (Get) into a physical table scan (Scan).
[0241] (4) Optimization: In this step, attribute enforcement and query cost estimation are performed. Optimization begins by submitting a query optimization request to the root expression group of the memo, specifying query requirements such as result distribution and sort order. Submitting a request to the root expression group is equivalent to requesting the minimum query cost plan that satisfies the request among the physical operators in the root expression group.
[0242] For each query optimization request, the sub-expressions in the root expression group pass the corresponding request to the sub-expressions in the sub-group based on the incoming query optimization request and the local requirements of the operator. During optimization, the same group may submit multiple identical query optimization requests. The system caches query optimization requests in a hash table. Incoming requests are only evaluated if the query optimization request does not exist in the hash table (to build a sub-query plan based on the query optimization request). In addition, each sub-expression maintains a local hash table that maps query optimization requests to corresponding historical query requests. The local hash table provides plan retrieval links used when extracting historical query plans from the memo.
[0243] As shown in Figure 2e, the query optimization request in the running example is shown. The current query optimization request associated with the first expression specifies that the query results need to be collected on the master node based on the order of column a of table T1. Figure 2e also includes the group hash table corresponding to the best group expression, as well as the Sort, Redistribute, and Replicate operators inserted in the memo. The Gather operator collects data from all data storage nodes to the master node. The GatherMerge operator collects sorted data from all data storage nodes to the master node and maintains the sort order. The Redistribute operator distributes tuples to segments based on the hash value of the given parameter.
[0244] Figure 2e includes the query operators in Group0 (Nested Loop Join [Group1, Group2] (Inner NestLoopJoin [Group1, Group2]), Inner NestLoopJoin [Group2, Group1], Inner Hash Join [Group1, Group2], and Inner Hash Join [Group2, Group1, (Sort(T1.a), Gather, GatherMerge(T1.a))), the query operators in Group1 (Scan(T1), Sort(T1.a), Replicate), and the query operators in Group2 (Scan(T2), Replicate, Redistribute(T2.b)). According to the query optimization request, the data query plan on the right is constructed by the query operators in Group1, Group2, and Group0. As shown in Figure 2f, the Inner Hash Join is shown. Optimization of Join[T1, T2]. For this query optimization request, one of the alternatives is to align the child node distributions according to the join condition so that the data to be joined can be co-located. This is achieved by requesting the Hashed(T1.a) distribution from the first expression 1 and the Hashed(T2.b) distribution from the first expression 2. Both first expressions require any type of sort order. After finding the best plan for the subgroups (Group1 and Group2) (the first subquery plan), the Inner Hash Join combines sub-attributes to determine the provided distribution and sort order (final optimization request). Here, the best plan for the first expression 2 requires hash distribution of T2 on T2.b because T2 was originally hash distributed on T2.a, while the best plan for the first expression 1 is a simple scan because T1 is already hash distributed on T1.a. When it is determined that the provided attributes cannot meet the original requirements (unsatisfied request), the unsatisfied attributes must be enforced (final optimization request). The attribute execution in the system adopts a flexible framework that allows each operator to define the behavior of enforcing the required attributes based on the sub-plan and operator local behavior. For example, the Nested Loops Join (NL Join) operator that maintains order may not need to enforce the sort order on the join if the outer child node already provides a sort order.
[0245] Figure 2f shows two possible initial subquery plans for Group0 that satisfy a query optimization request through attribute execution. The data query plan on the left sorts the query data on the segments and then gathers and merges the sorted results on the master node. The data query plan on the right gathers the query data from the segments to the master node and then sorts it. These different alternatives are encoded in a memo, and the cost model calculates the query cost (computational effort) of the initial subquery plan based on the metadata of the query data. Finally, based on the query structure provided by the query optimizer, the data query plan is extracted from the memo. Figure 2f illustrates plan extraction for a running example. Each related expression has a corresponding local hash table. Each local hash table maps an incoming optimization request to its corresponding sub-optimization request. First, the optimal grouping expression for the sub-expressions to be constructed in the root expression group is searched for using the GatherMerge operator. In the GatherMerge local hash table, the optimal grouping expression is Sort. Therefore, the GatherMerge operator is chained to Sort. In the Sort local hash table, the corresponding optimal grouping expression is Inner Hash Join [T1, T2]. Therefore, Sort is chained to Inner Hash Join. The same process is followed to complete the plan extraction and the final data query plan, as shown in Figure 2f. The extracted optimal data query plan is serialized in EL format and sent to the database management system for execution.
[0246] This application is mainly aimed at important business scenarios such as data analysis, such as query systems for large-scale real-time data and offline data. The Internet generates massive amounts of data every day, which poses a huge challenge to big data query systems. Data management and query systems in businesses have made great progress in scalability, availability and processing performance, making it possible to analyze and query large data sets of hundreds of trillions of bytes (TB) or even petabytes (PB) through Structured Query Language (SQL) or SQL-like interfaces. Internet data is usually stored in a distributed computing framework (Hadoop). The query engine compiles the original SQL query into a Spark or MapReduce job, where Spark is a distributed computing framework that uses in-memory computing and MapReduce is a distributed computing framework that uses disk read and write IO. Previous query optimizers usually use multi-stage query optimization, and the performance of the compiled Spark or MapReduce jobs is poor. For this reason, this application designs a query plan construction method based on a waterfall tree model to accelerate the coding of complex queries. After actual data testing, it can bring more than several times the query acceleration compared to previous query optimizers.
[0247] For SQL queries on big data systems, a common solution in the industry is to use Hive to convert queries into MapReduce tasks, where Hive is a data warehouse tool based on Hadoop, and Hadoop is a distributed computing framework. However, this method may lead to poor interactive analysis performance. To solve this problem, the industry has developed a number of professional query engines. However, these methods are often only applicable to specific host systems and are targeted at optimization, and cannot support the query requirements of multiple distributed systems at the same time. Since the query optimizer is the main factor affecting the performance of query processing analysis, and there are a large number of complex query processing requirements for big data in the business, this application designs a new type of query optimizer for a distributed query architecture for big data, and the business can quickly develop new optimization technologies and advanced query functions based on this optimizer. This application accelerates queries by designing metadata cache components, memos, mandatory attribute optimization and other technologies, and uses an efficient multi-core scheduler to improve the optimization speed.
[0248] This application designs a new query optimizer. A query optimizer based on the waterfall model optimization framework. Previously, the query optimizer of the data query system was tightly coupled with the entire database management system, but a unique feature of this application is that the designed optimizer can run as a separate component outside the database system. This capability makes it possible for products with different computing architectures to use the same optimizer. In addition, the optimizer can be deployed and run as an independent product, and can be tested and optimized separately in detail without being coupled to the structure of the database system. This reduces the difficulty of deployment and testing.
[0249] From the above, it can be seen that this application facilitates the construction of data query plans and improves query efficiency.
[0250] To better implement the above method, the present application also provides a query plan construction device. The query plan construction device can be integrated into an electronic device, such as a terminal, a server, or the like. The terminal can be a mobile phone, a tablet computer, a smart Bluetooth device, a laptop computer, a personal computer, or the like; the server can be a single server or a server cluster consisting of multiple servers.
[0251] For example, in this embodiment, the method of the embodiment of the present application will be described in detail by taking the query plan construction device specifically integrated into an electronic device as an example.
[0252] For example, as shown in FIG3 , the query plan construction apparatus may include an acquisition unit 310 , a key unit 320 , an equivalent unit 330 , a determination unit 340 , and a construction unit 350 , as follows:
[0253] (1) Acquisition unit 310.
[0254] The acquisition unit 310 is configured to acquire a data query expression, where the data query expression includes keywords.
[0255] In some embodiments, obtaining a data query expression includes:
[0256] Get data query command, which includes keywords;
[0257] According to the keywords, the data query command is parsed and processed to obtain the syntax tree of the data query command;
[0258] The preset data exchange language is used to convert the syntax tree into a format to obtain a data query expression.
[0259] (2) Key unit 320.
[0260] The key unit 320 is used to obtain basic sub-expressions in the data query expression and the association relationship between the basic sub-expressions according to the keywords.
[0261] (3) Equivalent unit 330.
[0262] The equivalent unit 330 is used to expand the basic sub-expression to obtain an equivalent sub-expression of the basic sub-expression. The basic sub-expression and the equivalent sub-expression of the basic sub-expression have equivalent expression meanings.
[0263] (4) Determine unit 340.
[0264] The determining unit 340 is configured to determine a sub-expression to be constructed from the basic sub-expression and the equivalent sub-expression of the basic sub-expression.
[0265] In some embodiments, determining a sub-expression to be constructed from a basic sub-expression and an equivalent sub-expression of the basic sub-expression includes:
[0266] Get the first condition number and the second condition number, the first condition number is the condition number of the data connection condition associated with the base sub-expression, and the second condition number is the condition number of the data connection condition associated with the equivalent sub-expression of the base sub-expression;
[0267] According to the first conditional quantity and the second conditional quantity, a sub-expression to be constructed is determined from a basic sub-expression and an equivalent sub-expression of the basic sub-expression.
[0268] In some embodiments, determining a sub-expression to be constructed from a basic sub-expression and an equivalent sub-expression of the basic sub-expression includes:
[0269] Get metadata information of the data to be queried associated with the basic sub-expression;
[0270] Obtain the data volume of the data to be queried from the metadata information;
[0271] According to the amount of data, the sub-expression to be constructed is determined from the basic sub-expression and the equivalent sub-expression of the basic sub-expression.
[0272] (5) Construction unit 350.
[0273] The construction unit 350 is used to construct a data query plan through the sub-expressions to be constructed and the association relationship.
[0274] In some embodiments, constructing a data query plan using the sub-expressions to be constructed and the associated relationships includes:
[0275] Obtain the current query optimization request associated with the sub-expression to be constructed from the data query expression;
[0276] Build the initial subquery plan through the sub-expression to be built;
[0277] Determine a subquery plan to be processed from an initial subquery plan based on a current query optimization request and an association relationship associated with the subexpression to be constructed;
[0278] The sub-query plans to be processed are merged to obtain the data query plan.
[0279] In some embodiments, constructing an initial subquery plan using the sub-expression to be constructed includes:
[0280] Get the query operator associated with the sub-expression to be constructed;
[0281] Build the connection relationship between query operators based on the sub-expressions to be built;
[0282] Control query operators to arrange according to the join relationship to obtain the initial subquery plan.
[0283] In some embodiments, determining a to-be-processed sub-query plan from an initial sub-query plan according to a current query optimization request and an association relationship includes:
[0284] Obtain metadata information of the to-be-queried data associated with the to-be-constructed sub-expression according to the current query optimization request associated with the to-be-constructed sub-expression;
[0285] Analyze the computational effort required to execute the initial subquery plan using metadata information about the data to be queried.
[0286] The subquery plan to be processed is determined from the initial subquery plan based on the computational effort and the association relationship.
[0287] In some embodiments, the sub-expression to be constructed includes a first expression and a second expression, the first expression is used to query the data to be queried, and the second expression is used to filter the data to be queried. According to the current query optimization request and the association relationship, the sub-query plan to be processed is determined from the initial sub-query plan, including:
[0288] Determine, according to the current query optimization request associated with the first expression, a first subquery plan from the first initial subquery plan, where the first initial subquery plan is an initial subquery plan constructed using the first expression;
[0289] Based on the current query optimization requests satisfied by the first subquery plan, determining unsatisfied requests from the current query optimization requests associated with the first expression;
[0290] Determine a final optimization request from the current query optimization requests associated with the second expression according to the unsatisfied requests and the association relationship, where the final optimization request includes the unsatisfied requests;
[0291] According to the final optimization request, a second subquery plan is determined from the second initial subquery plan, where the second initial subquery plan is an initial subquery plan constructed using the second expression, and the subquery plans to be processed include the first subquery plan and the second subquery plan.
[0292] In some embodiments, further comprising:
[0293] Obtain the historical query optimization request associated with the sub-expression to be constructed, and the historical query plan corresponding to the historical query optimization request;
[0294] Determine, from the historical query optimization requests, a pending historical request corresponding to the current query optimization request;
[0295] If there are pending historical requests, the historical query plan corresponding to the pending historical request is used as the pending sub-query plan;
[0296] If there is no pending historical request, the step of constructing an initial subquery plan using the sub-expression to be constructed is triggered.
[0297] In some embodiments, obtaining a historical query optimization request associated with a sub-expression to be constructed and a historical query plan corresponding to the historical query optimization request includes:
[0298] Obtain a hash table associated with the sub-expression to be constructed. The hash table includes historical query optimization requests, as well as first tags and plan retrieval links associated with the historical query optimization requests. The first tag is a hash value obtained by calculating the historical query optimization request using a hash function.
[0299] Determine the pending historical requests corresponding to the current query optimization request from the historical query optimization requests, including:
[0300] Determine a second tag, where the second tag is a hash value obtained by calculating the current query optimization request through a hash function;
[0301] Determining, from the historical query optimization requests, a pending historical request corresponding to the current query optimization request based on the first tag and the second tag;
[0302] If there are pending historical requests, the historical query plan corresponding to the pending historical request is used as the pending subquery plan, including:
[0303] If there is a pending historical request, the plan retrieval link associated with the pending historical request is used to retrieve the historical query plan corresponding to the historical query optimization request as the pending sub-query plan.
[0304] In some embodiments, after constructing a data query plan using the sub-expression to be constructed, the method further includes:
[0305] Determine the query instructions corresponding to the data query plan, as well as the copy instructions of the query instructions;
[0306] The replica instruction is sent to multiple data storage nodes of the database, so that the data storage nodes return the query data according to the replica instruction.
[0307] In specific implementation, the above units can be implemented as independent entities, or can be arbitrarily combined to be implemented as the same or several entities. The specific implementation of the above units can be found in the previous method embodiments and will not be repeated here.
[0308] As can be seen from the above, the query plan construction device of this embodiment obtains a data query expression by an acquisition unit, and the data query expression includes keywords; the key unit obtains the current sub-expression in the data query expression and the association relationship between the current sub-expressions based on the keywords; the equivalent unit expands the basic sub-expression to obtain the equivalent sub-expression of the basic sub-expression, and the basic sub-expression and the equivalent sub-expression of the basic sub-expression have equivalent expression meanings; the determination unit determines the sub-expression to be constructed from the basic sub-expression and the equivalent sub-expression of the basic sub-expression; the construction unit constructs a data query plan through the sub-expression to be constructed and the association relationship. Therefore, the embodiment of the present application can facilitate the construction of a data query plan and improve query efficiency.
[0309] The present application also provides an electronic device, which may be a terminal, a server, or the like. The terminal may be a mobile phone, a tablet computer, a smart Bluetooth device, a laptop computer, a personal computer, or the like; the server may be a single server or a server cluster consisting of multiple servers, or the like.
[0310] In some embodiments, the query plan construction device can also be integrated into multiple electronic devices. For example, the query plan construction device can be integrated into multiple servers, and the query plan construction method of the present application can be implemented by multiple servers.
[0311] In this embodiment, the electronic device of this embodiment is a server as an example for detailed description. For example, as shown in FIG4 , it shows a schematic diagram of the structure of the server involved in the embodiment of the present application. Specifically:
[0312] The server may include one or more processing core processors 410, one or more computer-readable storage media memories 420, a power supply 430, an input module 440, and a communication module 450. Those skilled in the art will appreciate that the server structure shown in FIG4 does not limit the server and may include more or fewer components than shown, or combine certain components, or arrange the components differently.
[0313] Processor 410 is the server's control center, connecting various server components via various interfaces and circuits. It executes software programs and / or modules stored in memory 420 and accesses data stored in memory 420 to perform various server functions and process data. In some embodiments, processor 410 may include one or more processing cores. In some embodiments, processor 410 may integrate an application processor and a modem processor. The application processor primarily handles the operating system, user interface, and application programs, while the modem processor primarily handles wireless communications. It is understood that the modem processor may not be integrated into processor 410.
[0314] The memory 420 can be used to store software programs and modules. The processor 410 executes various functional applications and data processing by running the software programs and modules stored in the memory 420. The memory 420 may mainly include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application required for at least one function (such as a sound playback function, an image playback function, etc.), etc.; the data storage area may store data created according to the use of the server, etc. In addition, the memory 420 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other volatile solid-state storage device. Accordingly, the memory 420 may also include a memory controller to provide the processor 410 with access to the memory 420.
[0315] The server also includes a power supply 430 that supplies power to various components. In some embodiments, the power supply 430 can be logically connected to the processor 410 via a power management system, thereby enabling the power management system to manage charging, discharging, and power consumption. The power supply 430 can also include one or more DC or AC power supplies, a recharging system, a power failure detection circuit, a power converter or inverter, a power status indicator, and other arbitrary components.
[0316] The server may further include an input module 440, which may be configured to receive input digital or character information and generate keyboard, mouse, joystick, optical or trackball signal inputs related to user settings and function controls.
[0317] The server may also include a communication module 450. In some embodiments, the communication module 450 may include a wireless module. The server may use the wireless module of the communication module 450 to perform short-range wireless transmission, thereby providing users with wireless broadband Internet access. For example, the communication module 450 may be used to help users send and receive emails, browse web pages, and access streaming media.
[0318] Although not shown, the server may further include a display unit, etc., which will not be described in detail here. Specifically, in this embodiment, the processor 410 in the server loads the executable files corresponding to one or more application processes into the memory 420 according to the following instructions, and the processor 410 runs the application stored in the memory 420, thereby implementing any of the methods provided in the aforementioned embodiments.
[0319] The specific implementation of the above methods can be found in the previous embodiments and will not be described again here.
[0320] From the above, we can see that multiple sub-expressions to be constructed can construct a query plan based on the association relationship, so that the multiple sub-expressions to be constructed do not conflict with each other when constructing the data query plan. This makes it easier to construct the data query plan and improves the query performance.
[0321] Those skilled in the art will appreciate that all or part of the steps in the various methods of the above embodiments may be accomplished by instructions, or by controlling related hardware through instructions. The instructions may be stored in a computer-readable storage medium and loaded and executed by a processor.
[0322] To this end, an embodiment of the present application provides a computer-readable storage medium storing a plurality of instructions that can be loaded by a processor to execute the steps of any query plan construction method provided in an embodiment of the present application. For example, the instructions can execute any of the methods provided in the aforementioned embodiments.
[0323] The storage medium may include a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk, etc.
[0324] According to one aspect of the present application, a computer program product is provided, comprising a computer program / instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer program / instructions from the computer-readable storage medium and executes the computer program / instructions, causing the computer device to perform the methods provided in various optional implementations of query plan construction provided in the above-described embodiments.
[0325] Since the instructions stored in the storage medium can execute the steps in any query plan construction method provided in the embodiments of the present application, the beneficial effects that can be achieved by any query plan construction method provided in the embodiments of the present application can be achieved. Please see the previous embodiments for details and will not be repeated here.
[0326] The above is a detailed introduction to a query plan construction method, device, electronic device and storage medium provided in the embodiments of 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 technical personnel in this field, based on 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 plan construction method, the method being executed by an electronic device, comprising: Obtaining a data query expression, wherein the data query expression includes keywords; According to the keywords, basic sub-expressions in the data query expression and the association relationship between the basic sub-expressions are obtained; Expanding the basic sub-expression to obtain an equivalent sub-expression of the basic sub-expression, wherein the basic sub-expression and the equivalent sub-expression of the basic sub-expression have equivalent expression meanings; Determine a sub-expression to be constructed from the basic sub-expression and the equivalent sub-expression of the basic sub-expression; A data query plan is constructed using the sub-expressions to be constructed and the association relationships.
2. The query plan construction method according to claim 1, wherein determining the sub-expression to be constructed from the basic sub-expression and the equivalent sub-expression of the basic sub-expression comprises: Acquire a first condition quantity and a second condition quantity, wherein the first condition quantity is the condition quantity of the data connection condition associated with the basic sub-expression, and the second condition quantity is the condition quantity of the data connection condition associated with the equivalent sub-expression of the basic sub-expression; The sub-expression to be constructed is determined from the basic sub-expression and the equivalent sub-expression of the basic sub-expression according to the first conditional quantity and the second conditional quantity.
3. The query plan construction method according to claim 1, wherein determining the sub-expression to be constructed from the basic sub-expression and the equivalent sub-expression of the basic sub-expression comprises: Obtaining metadata information of the to-be-queried data associated with the basic sub-expression; Acquire the data volume of the to-be-queried data from the metadata information; According to the data amount, a sub-expression to be constructed is determined from the basic sub-expression and the equivalent sub-expression of the basic sub-expression.
4. The query plan construction method according to claim 1, wherein the step of constructing a data query plan by using the sub-expression to be constructed and the association relationship comprises: Acquire a current query optimization request associated with the sub-expression to be constructed from the data query expression; Constructing an initial subquery plan through the sub-expression to be constructed; Determining a to-be-processed sub-query plan from the initial sub-query plan according to the current query optimization request and the association relationship; The sub-query plans to be processed are merged to obtain the data query plan.
5. The query plan construction method according to claim 4, wherein the step of constructing an initial sub-query plan using the sub-expression to be constructed comprises: Obtaining the query operator associated with the sub-expression to be constructed; Constructing a connection relationship between the query operators according to the sub-expressions to be constructed; The query operators are controlled to be arranged according to the connection relationship to obtain the initial sub-query plan.
6. The query plan construction method according to claim 4, wherein the step of determining the sub-query plan to be processed from the initial sub-query plan according to the current query optimization request and the association relationship comprises: According to the current query optimization request, metadata information of the to-be-queried data associated with the to-be-constructed sub-expression is obtained; Analyzing the amount of computation required when executing the initial subquery plan based on the metadata information of the data to be queried; The to-be-processed sub-query plan is determined from the initial sub-query plan according to the calculation amount and the association relationship.
7. The query plan construction method according to claim 4, wherein the sub-expression to be constructed comprises a first expression and a second expression, the first expression is used to query the data to be queried, and the second expression is used to filter the data to be queried, and the determining the sub-query plan to be processed from the initial sub-query plan according to the current query optimization request and the association relationship comprises: Determine, according to the current query optimization request associated with the first expression, a first sub-query plan from a first initial sub-query plan, wherein the first initial sub-query plan is the initial sub-query plan constructed by using the first expression; Based on the current query optimization request satisfied by the first subquery plan, determining unsatisfied requests from the current query optimization request associated with the first expression; Determine, according to the unsatisfied request and the association relationship, a final optimization request from the current query optimization requests associated with the second expression, wherein the final optimization request includes the unsatisfied request; According to the final optimization request, a second sub-query plan is determined from a second initial sub-query plan, wherein the second initial sub-query plan is the initial sub-query plan constructed by the second expression, and the sub-query plan to be processed includes the first sub-query plan and the second sub-query plan.
8. The query plan construction method according to claim 4, further comprising: Obtaining a historical query optimization request associated with the sub-expression to be constructed, and a historical query plan corresponding to the historical query optimization request; Determine, from the historical query optimization requests, a to-be-processed historical request corresponding to the current query optimization request; If the pending historical request exists, the historical query plan corresponding to the pending historical request is used as the pending sub-query plan; If the to-be-processed historical request does not exist, the step of constructing the initial subquery plan through the to-be-constructed sub-expression is triggered to execute.
9. The query plan construction method according to claim 8, wherein the step of obtaining the historical query optimization request associated with the sub-expression to be constructed and the historical query plan corresponding to the historical query optimization request comprises: Obtaining a hash table associated with the sub-expression to be constructed, wherein the hash table includes a historical query optimization request, and a first label and a plan retrieval link associated with the historical query optimization request, wherein the first label is a hash value obtained after the historical query optimization request is calculated by a hash function; The determining, from the historical query optimization requests, the to-be-processed historical request corresponding to the current query optimization request includes: Determine a second tag, where the second tag is a hash value of the current query optimization request obtained after the hash function is used to calculate the current query optimization request; Determine, from the historical query optimization requests, a to-be-processed historical request corresponding to the current query optimization request according to the first tag and the second tag; If the pending historical request exists, taking the historical query plan corresponding to the pending historical request as the pending sub-query plan includes: If the pending historical request exists, the plan retrieval link associated with the pending historical request is used to retrieve the historical query plan corresponding to the historical query optimization request as the pending sub-query plan.
10. The query plan construction method according to any one of claims 1 to 9, wherein obtaining the data query expression comprises: Acquire a data query command, wherein the data query command includes the keyword; According to the keywords, the data query command is subjected to syntax analysis to obtain a syntax tree of the data query command; The syntax tree is format converted using a preset data exchange language to obtain the data query expression.
11. The query plan construction method according to any one of claims 1 to 9, after constructing the data query plan by using the sub-expressions to be constructed and the association relationship, further comprising: Determine a query instruction corresponding to the data query plan, and a copy instruction of the query instruction; The replica instruction is sent to multiple data storage nodes of the database, so that the data storage nodes return the query data according to the replica instruction.
12. A query plan construction device, the device being deployed on an electronic device, comprising: An acquisition unit, used to acquire a data query expression, wherein the data query expression includes a keyword; A key unit, used to obtain basic sub-expressions in the data query expression and the association relationship between the basic sub-expressions according to the keywords; An equivalent unit, used for expanding the basic sub-expression to obtain an equivalent sub-expression of the basic sub-expression, wherein the basic sub-expression and the equivalent sub-expression of the basic sub-expression have equivalent expression meanings; A determination unit, configured to determine a sub-expression to be constructed from the basic sub-expression and an equivalent sub-expression of the basic sub-expression; A construction unit is used to construct a data query plan through the sub-expression to be constructed and the association relationship.
13. An electronic device comprising a processor and a memory, wherein the memory stores a plurality of instructions; the processor loads instructions from the memory to execute the steps in the query plan construction method as described in any one of claims 1 to 11.
14. A computer-readable storage medium storing a plurality of instructions, wherein the instructions are suitable for being loaded by a processor to execute the steps in the query plan construction method according to any one of claims 1 to 11.
15. A computer program product, comprising a computer program / instruction, wherein when the computer program / instruction is executed by a processor, the steps in the query plan construction method according to any one of claims 1 to 11 are implemented.
Citation Information
Patent Citations
Method for realizing optimization of data source extensive makeup language (XML) query system based on sub-queries
CN101984439A
Query execution method and device, electronic equipment and computer readable medium
CN112395303A
Query plan construction method and device, electronic equipment and storage medium
CN116975098A
System and method for optimizing database queries
US5822747A
System and method for optimizing database queries with improved performance enhancements
US6021405A
Cited By
Database query method based on tree structure conversion and HIVE multi-table combination
CN119597797A
Method and device for verifying effectiveness of plan tree in database and electronic equipment
CN121326969A
Method, device and electronic equipment for verifying validity of a plan tree in a database
CN121326969B
