Method and apparatus for optimizing pruning queries for distributed view columns

By parsing the outer query and identifying redundant columns in the distributed database system, and filtering out redundant data, the problem of redundant transmission between data nodes and computing nodes caused by distributed view expansion is solved, thus improving query performance.

CN123115204APending Publication Date: 2026-11-17JINZHUAN INFORMATION TECHNOLOGY CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202611337424.6
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2026-08-31
Publication Date
2026-11-17

AI Technical Summary

Technical Problem

In distributed database systems, when a distributed view is expanded, there is a problem of redundant data transmission between data nodes and computing nodes, which leads to wasted network bandwidth and decreased query performance, and existing technologies cannot effectively solve this problem.

Method used

By parsing the outer query statement, identifying the distributed view and performing column requirement analysis, we obtain the first set of columns actually needed by the outer query and the second set of columns after the view is expanded. Redundant columns are identified and filtered out when the subquery is executed, returning only the required column data.

Benefits of technology

It effectively reduces redundant data transmission between computing nodes and data nodes, lowers network bandwidth consumption, and improves query performance, especially in scenarios with a large number of columns.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN123115204A_ABST
    Figure CN123115204A_ABST
Patent Text Reader

Abstract

This application discloses a query optimization method and apparatus for pruning columns in a distributed view. The method includes: parsing an outer query statement to identify each clause and the referenced distributed view; performing column requirement analysis on the outer query to obtain a first set of columns actually needed by the outer query, and obtaining a second set of all output columns of the distributed view referenced in the outer query after expansion into a subquery; wherein the column requirement analysis includes identifying columns directly referenced by each clause and columns dependent on function calls; comparing the first and second set of columns, and determining columns present in the second set but not in the first set as redundant columns; and filtering out redundant columns during subquery execution, returning only the column data corresponding to the first set to the compute node. This application solves the technical problem of redundant transmission between data nodes and compute nodes in related technologies.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] This application relates to the field of database technology, and more specifically, to a method and apparatus for optimizing pruning queries for distributed view columns. Background Technology

[0002] This section is intended to provide background or context for the content set forth in the claims or specification, and the content described herein is not acknowledged as prior art simply because it is included in this section.

[0003] With the advent of the big data era, enterprise data scale has exploded, and traditional centralized databases can no longer meet the demands of massive data storage and high-concurrency queries. Distributed database systems, by distributing data across multiple data nodes (DNs) and coordinating query processing through compute nodes (CNs), achieve horizontal scaling and high availability, and are therefore widely used in finance, e-commerce, the Internet of Things, and other fields. In distributed database systems, views, as an important database object, can encapsulate complex query logic into virtual tables, providing a unified data access interface for upper-layer applications, greatly improving the convenience and security of data access. Distributed views are built on top of distributed tables, with their underlying data distributed across multiple data nodes. When an outer query references a distributed view, the compute nodes need to expand the view definition into a subquery and push the subquery down to each data node for execution. The data nodes then return the execution results to the compute nodes for aggregation. Since the system defaults to returning all columns in the view definition, redundant transmission occurs between data nodes and compute nodes.

[0004] There is currently no effective solution to the above problems. Summary of the Invention

[0005] This application provides a method and apparatus for optimizing pruning queries for distributed view columns, so as to at least solve the technical problem of redundant transmission between data nodes and computing nodes in related technologies.

[0006] According to one aspect of the embodiments of this application, a query optimization method for pruning columns of a distributed view is provided, comprising: parsing an outer query statement and identifying each clause in the outer query and the distributed view referenced therein; performing column requirement analysis on the outer query to obtain a first set of columns actually needed by the outer query and a second set of all output columns of the distributed view referenced in the outer query after being expanded into a subquery, wherein the column requirement analysis includes identifying columns directly referenced by each clause and identifying columns dependent on function calls; comparing the first set of columns with the second set of columns and determining columns that exist in the second set of columns but not in the first set of columns as redundant columns; and filtering out redundant columns when executing the subquery and returning only the column data corresponding to the first set of columns to the computing node.

[0007] According to another aspect of the embodiments of this application, a query optimization apparatus for pruning distributed view columns is also provided, comprising: a parsing unit, configured to parse an outer query statement and identify each clause in the outer query and the referenced distributed view; an acquisition unit, configured to perform column requirement analysis on the outer query, acquire a first set of columns actually needed by the outer query, and acquire a second set of all output columns of the distributed view referenced in the outer query after being expanded into a subquery, wherein the column requirement analysis includes the identification of columns directly referenced by each clause and the identification of columns dependent on function calls; a comparison unit, configured to compare the first set of columns with the second set of columns, and determine columns that exist in the second set of columns but not in the first set of columns as redundant columns; and an execution unit, configured to filter out redundant columns when executing the subquery, and only return column data corresponding to the first set of columns to the computing node.

[0008] According to another aspect of the embodiments of this application, a computer-readable storage medium is also provided, the storage medium including a stored program that executes the above-described method when the program is run.

[0009] According to another aspect of the embodiments of this application, an electronic device is also provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor performs the above-described method through the computer program.

[0010] According to one aspect of this application, a computer program product or computer program is provided, comprising computer instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium and executes the computer instructions, causing the computer device to perform the steps of any of the embodiments of the methods described above.

[0011] In this embodiment, the outer query statement is parsed to identify each clause and the referenced distributed view, laying the foundation for subsequent analysis. Then, column requirement analysis is performed on the outer query to obtain the first set of columns actually needed by the outer query, and the second set of all output columns after the distributed view is expanded into a subquery is obtained. The column requirement analysis includes identifying columns directly referenced by each clause and columns dependent on function calls. The first and second set of columns are then compared, and columns that exist in the second set but not in the first set are identified as redundant columns, accurately locating unnecessary columns that do not need to be transmitted. Finally, when executing the subquery, redundant columns are filtered out, and only the column data corresponding to the first set is returned to the computing node. This physically blocks the transmission of redundant columns between the data node and the computing node, thereby solving the technical problem of redundant transmission between data nodes and computing nodes in related technologies. This completely solves the problem of wasted network bandwidth and increased transmission load caused by default outputting all columns after the distributed view is expanded. Attached Figure Description

[0012] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings: Figure 1 This is a flowchart of an optional query optimization method for pruning columns in a distributed view according to an embodiment of this application; Figure 2 This is a schematic diagram of an optional query optimization device for distributed view columns according to an embodiment of this application; Figure 3 This is a structural block diagram of a terminal according to an embodiment of this application. Detailed Implementation

[0013] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort should fall within the scope of protection of the present application.

[0014] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this application described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0015] After analyzing relevant technologies, the inventors recognized a serious performance issue with distributed views in distributed database systems: when an outer query references a distributed view, the system by default transmits all output columns from the data nodes to the compute nodes, even though the outer query often only needs a subset of the columns. For example, in a distributed view with twenty output columns, the outer query might only need three to five columns, leaving the remaining columns redundant. Since data nodes and compute nodes in a distributed database are typically deployed on different physical machines and transmit data over a network, the transmission of numerous redundant columns leads to severe data redundancy between the data nodes (CN) and compute nodes (DN). This not only wastes valuable network bandwidth but also increases I / O overhead on data nodes and data processing burden on compute nodes, ultimately resulting in decreased query performance and prolonged response times. This performance degradation is particularly pronounced in scenarios with a large number of view columns and large data volumes, becoming a significant bottleneck restricting the query performance of distributed databases. In addition, existing technologies still have many shortcomings in the implementation of column pruning. For example, they cannot handle implicit column dependencies in custom function bodies, pruning at the logic optimization level is not effectively implemented at the execution level, and traditional column pruning only covers simple clauses without considering complex function call scenarios. These problems further limit the potential for improving the performance of distributed view queries.

[0016] To address some or all of the above-mentioned problems, according to one aspect of the embodiments of this application, an embodiment of a query optimization scheme for column pruning in distributed views is provided. The technical solution of this application can be widely applied in multiple fields such as databases, distributed databases, database middleware, distributed middleware, and distributed storage. In the field of distributed databases, the column pruning technology of this application can serve as one of the core optimization methods of the query optimizer, significantly improving the performance of distributed view queries. In the field of database middleware, the technical solution of this application can be integrated into the middleware, providing transparent column pruning optimization services to upper-layer applications without modifying the underlying database. In the field of distributed storage, the idea of ​​this application can be extended to the data query interface layer, reducing the amount of data transmission between storage nodes and computing nodes. Furthermore, the technical solution of this application can also be applied to scenarios such as cloud databases, data warehouses, and big data query engines, demonstrating good versatility and broad application prospects.

[0017] The system architecture described in this application includes a management node (PM), compute nodes (CN), and multiple data nodes (DN1, DN2, DN3). The management node is responsible for cluster metadata management and node status monitoring. The compute nodes are the core of query processing, containing an SQL parsing module, a query optimization module (including a column requirement analyzer, view definition parser, and redundant column determiner), and an execution engine module. The data nodes are responsible for actual data storage and subquery execution. Data is distributed and stored across the data nodes according to sharding rules. The overall process is as follows: The user submits an SQL query request to the compute node; the SQL parsing module of the compute node parses the query, identifies each clause of the outer query and the referenced distributed view; the view definition parser in the query optimization module obtains the definition of the distributed view and expands it into a subquery, extracting all output columns to form a second column set; the column requirement analyzer performs column requirement analysis on the outer query, including identification of directly referenced columns and function call dependency columns, to obtain the actual first column set; the redundant column determiner compares the two column sets and determines redundant columns; the execution engine module generates an execution plan, constructs a simplified column projection, and pushes the subquery and column projection down to each data node; after each data node executes the subquery locally, it filters redundant columns according to the column projection and returns only the data of the required columns; the compute node summarizes the data returned by each data node, completes subsequent processing, and returns the results to the user.

[0018] Before detailing the technical solution of this application, the core terms involved in this application will be explained. The distributed database system involved in this application adopts a compute-storage separation architecture, mainly including the following types of nodes: First, compute nodes (CN), which are responsible for receiving SQL query requests submitted by users, performing SQL parsing, query optimization, execution plan generation, and query result aggregation processing. They are the query scheduling and coordination nodes of the entire distributed database system; Second, data nodes (DN), which are responsible for actually storing data fragments and executing subquery tasks pushed down by compute nodes. Data is horizontally distributed across multiple data nodes according to certain sharding rules; Third, management nodes (ProxyManager, PM), which are responsible for metadata management, node status monitoring, load balancing, and cluster configuration management of the entire cluster. When a user submits an SQL query containing a distributed view to a compute node, the compute node needs to expand the view definition into a subquery, and then push the subquery down to each data node for execution. The data nodes return the execution results to the compute node through the network, and the compute node aggregates the results before returning them to the user. The core objective of this application is to reduce the amount of redundant data transmitted from data nodes to computing nodes through precise column pruning during this process.

[0019] Figure 1 This is a flowchart of an optional query optimization method for pruning columns in a distributed view according to an embodiment of this application, such as... Figure 1 As shown, the method may include the following steps: Step S102: Parse the outer query statement and identify each clause in the outer query and the distributed view it references.

[0020] Step S104: Perform column requirement analysis on the outer query to obtain the first set of columns actually needed by the outer query, and obtain the second set of all output columns after the distributed view referenced in the outer query is expanded into a subquery. The column requirement analysis includes the identification of columns directly referenced by each clause and the identification of columns that function calls depend on.

[0021] Step S106: Compare the first column set with the second column set, and determine the columns that exist in the second column set but not in the first column set as redundant columns.

[0022] In step S108, when executing the subquery, redundant columns are filtered out, and only the column data corresponding to the first column set is returned to the compute node.

[0023] In practical implementation, the outer query statement refers to the SQL query statement directly submitted by the user that references the distributed view. The parsing of the outer query statement is performed by the SQL parser on the compute node. The SQL parser converts the SQL text into an Abstract Syntax Tree (AST) and identifies the various clauses in the query statement based on the AST, including but not limited to SELECT, WHERE, GROUP BY, ORDER BY, and HAVING clauses. Simultaneously, the parser also identifies the database objects referenced in the query, including base tables, views, and functions. When a referenced object is identified as a distributed view, it is marked as an object requiring expansion. A distributed view is a view created based on distributed tables, whose underlying data is physically distributed and stored across multiple data nodes. Column requirement analysis involves extracting all referenced columns from the various clauses of the outer query and summarizing these columns into a set of columns, i.e., the first column set. The first column set represents all the columns actually needed by the outer query during execution. It's important to note that column requirement analysis includes not only identifying columns directly referenced in clauses but also deeply identifying columns depended upon by function calls. That is, if a column, though not directly present in a clause, is used as a parameter in a function call or referenced within the function's internal logic, it should also be included in the first column set. The second column set refers to all output columns contained in the SELECT list of a subquery after the distributed view is expanded. By comparing the first and second column sets column by column, we can identify which columns exist only in the second set and not in the first. These are redundant columns that the outer query does not need. During the actual execution of the subquery, these redundant columns are filtered out, and the data nodes only return data corresponding to the columns in the first column set to the outer query, thus achieving column pruning optimization.

[0024] By adopting the above technical solution, redundant columns after the distributed view is expanded can be accurately identified, and these redundant columns can be dynamically filtered during the execution phase. This fundamentally solves the problem of redundant data transmission between CN and DN caused by the full column output of the distributed view, effectively reducing network bandwidth consumption and improving query execution efficiency.

[0025] In some optional implementations of this application, column requirement analysis is performed on the outer-layer query to obtain the first set of columns actually needed by the outer-layer query, including: performing column requirement analysis on the outer-layer query, identifying the columns that built-in function calls, custom function calls, aggregate function calls and window function calls depend on, and saving them to the first set of columns.

[0026] In practical implementation, built-in functions refer to standard functions provided by the database system itself, such as string processing functions (e.g., CONCAT, SUBSTRING, UPPER, LOWER), numerical calculation functions (e.g., ABS, ROUND, FLOOR), date and time functions (e.g., DATE_ADD, DATEDIFF), and conditional judgment functions (e.g., CASE WHEN, COALESCE). The columns that a built-in function call depends on are the columns that appear as function parameters; for example, the CONCAT(a, b) function depends on columns a and b. User-defined functions (UDFs) are functions that users define and register in the database system according to their own business needs. UDFs can contain complex business logic; their function bodies may directly reference table fields or contain nested subqueries. Aggregate functions are functions that calculate a set of values ​​and return a single summary value, such as SUM, AVG, COUNT, MAX, MIN, etc. The columns that an aggregate function call depends on are its input parameter columns. Window functions are functions that perform calculations on a set of rows related to the current row, such as ROW_NUMBER, RANK, LAG, LEAD, and window aggregate functions. The columns that a window function call depends on include its parameter columns and the columns referenced by PARTITIONBY and ORDER BY in the OVER clause. During column requirement analysis, it is necessary to traverse all function call nodes in the abstract syntax tree, process each type of function call separately, extract all its dependent columns, and add these columns to the first column set.

[0027] By adopting the above technical solution, the column requirement analysis covers a variety of complex function call scenarios, including built-in functions, user-defined functions, aggregate functions, and window functions. This ensures the comprehensiveness and accuracy of the column requirement analysis, avoids the problem of incorrect pruning caused by missing function dependency columns, and enables the technical solution of this application to be applicable to more complex query scenarios.

[0028] In some optional implementations of this application, performing column requirement analysis on the outer-layer query, identifying the columns that the custom function call depends on, and saving them to the first column set includes: parsing the function body of the custom function and saving the columns directly or indirectly referenced in the function body to the first column set.

[0029] In practical implementation, column dependency analysis of custom functions has its own unique characteristics because the function body of a custom function may contain complex logic, even nested SQL queries. Simply analyzing the parameter list of the custom function is insufficient, as the function body may directly reference table fields in the database, or reference table fields through nested queries. Although these referenced columns do not appear in the syntax tree of the outer query, they are actually the columns that the outer query depends on. Therefore, when encountering a custom function call, this application further obtains the function definition of the custom function and parses the function body. The function body parsing process includes: First, if the function body is a stored procedure or function composed of SQL statements, the SQL statements within the function body are parsed to extract the directly referenced table columns; Second, if the function body contains nested queries (such as subqueries, cursor queries, etc.), the column references in the nested queries are recursively parsed; Third, if the function body calls other custom functions, the function bodies of the called functions are recursively parsed until column dependencies at all nesting levels are captured. This deep analysis method allows you to fully obtain all columns that a custom function call depends on, including explicit parameter columns and implicit function body reference columns.

[0030] By adopting the above technical solution, the problem that traditional column pruning technology cannot handle implicit column dependencies within the body of custom functions is solved, and the linkage column pruning analysis between custom functions and distributed views is realized, ensuring the accuracy and completeness of column pruning in complex query scenarios that include custom function calls.

[0031] In some optional implementations of this application, column requirement analysis is performed on the outer query to obtain the first set of columns actually needed by the outer query, including: obtaining the columns involved in the outer query results, the columns involved in the filtering conditions, the columns required for grouping operations, the columns required for sorting operations, the columns involved in the DISTINCT deduplication operation, and the columns involved in the HAVING condition filtering operation, and saving the identified columns to the first set of columns.

[0032] In the specific implementation process, column requirements analysis needs to cover all clauses of the outer query, specifically including the following aspects: First, columns involved in the outer query results, that is, all columns directly or indirectly referenced in the output column list of the SELECT clause, including ordinary column references, column references in expressions, column references in function calls, etc.; Second, columns involved in filtering conditions, that is, all columns referenced by the filtering condition expression in the WHERE clause; Third, columns required for grouping operations, that is, the grouping columns specified in the GROUP BY clause; Fourth, columns required for sorting operations, that is, the sorting columns specified in the ORDER BY clause; Fifth, columns involved in the DISTINCT deduplication operation, that is, all output columns in the SELECT DISTINCT statement need to participate in the deduplication calculation, so these columns should all be included in the column requirements; Sixth, columns involved in the HAVING condition filtering operation, that is, columns referenced by the condition expression in the HAVING clause, including columns referenced by aggregate functions. In practical implementation, a depth-first traversal can be used to iterate through each node of the abstract syntax tree. For each node, its type is checked; if the node references a column, the corresponding column is added to the first column set. To avoid duplication, a Set data structure can be used to store the columns in the first column set, ensuring that the same column is recorded only once.

[0033] By adopting the above technical solution, the column requirement analysis comprehensively covers column references in all major SQL clauses such as SELECT, WHERE, GROUP BY, ORDER BY, DISTINCT, and HAVING, ensuring that all columns required for the execution of the outer query are accurately identified, providing a reliable foundation for subsequent redundant column determination.

[0034] In some optional implementations of this application, obtaining the second set of all output columns of the distributed view referenced in the outer query after it has been expanded into a subquery includes: obtaining the view definition of the distributed view; expanding the view definition into a subquery; and extracting all output columns of the subquery as the second set of columns.

[0035] In practice, a view definition refers to the SELECT statement specified when creating a view, stored in the database's metadata. When an outer query references a distributed view, the compute node first retrieves the view's definition information from the metadata storage, including the view name, schema, and creation statement. Then, the compute node uses the SELECT statement from the view definition as a subquery, replacing the reference to the view in the outer query—a process called view expansion. After view expansion, the original view reference is replaced by a subquery with its own SELECT list, whose columns are the view's output columns. By traversing the subquery's SELECT list, the column names, data types, and other information of all output columns can be extracted; these columns constitute the second set of columns. It should be noted that if the view definition itself references other views (i.e., view nesting), view expansion needs to be performed recursively until the innermost base table is reached, ultimately obtaining all output columns corresponding to the outermost view.

[0036] By adopting the above technical solution, through view definition parsing and view expansion operations, all output columns of the distributed view are accurately obtained, providing an accurate comparison benchmark for subsequent column set comparison and redundant column determination.

[0037] In some alternative embodiments of this application, before comparing the first set of columns with the second set of columns, the method further includes: establishing a mapping relationship between each column in the first set of columns and each column in the second set of columns, and passing the column requirements of the outer query to the corresponding columns of the subquery based on the mapping relationship.

[0038] In practical implementation, since the columns referenced by the outer query are referenced through the column names of the view, and the columns in the subqueries after the view is expanded may have different internal identifiers, it is necessary to establish a mapping relationship between the outer query columns and the subquery columns. The process of establishing this mapping relationship is as follows: First, for view columns directly referenced in the outer query, a one-to-one mapping relationship is established by matching the view column names with the column names of the subquery output columns. Second, for columns referenced through expressions or functions, it is necessary to trace the view columns corresponding to each column parameter in the expression or function, and then map these column parameters to the corresponding output columns of the subquery. Third, for columns referenced through aliases, it is necessary to first parse the original column corresponding to the alias before establishing the mapping relationship. After establishing the mapping relationship, the column requirements of the outer query can be passed to the subquery level through the mapping relationship. In other words, the mapping relationship determines which columns the outer query needs and which columns in the subquery correspond to, thus identifying which columns in the subquery are necessary and which are redundant. This cross-level column requirement propagation mechanism ensures that column pruning is performed on the correct columns, avoiding pruning errors caused by mismatched column names or aliases.

[0039] By adopting the above technical solution, a mapping relationship is established between the outer query columns and the subquery columns, which realizes the accurate transmission of column requirements across levels, ensures the accuracy of redundant column determination, and enables the column pruning operation to be correctly applied to the corresponding columns of the subquery.

[0040] In some optional embodiments of this application, filtering redundant columns includes: before the compute node sends a data retrieval request to the data node, constructing a simplified column projection based on the first column set, and sending the simplified column projection to the data node to instruct the data node to return column data only according to the simplified column projection after executing the subquery.

[0041] In practice, traditional column pruning often only stays at the logical optimization stage, that is, marking the required columns when generating the logical execution plan. However, in the actual execution stage, data nodes may still return data for all columns, leading to a disconnect between logical optimization and execution optimization. This application implements column pruning at the execution level. Specifically, before the compute node generates the physical execution plan and prepares to send a data retrieval request to the data nodes, a simplified column projection list is constructed based on the determined first set of columns (i.e., the columns actually needed). The column projection list is a list of column names or column identifiers that explicitly specifies which columns the data nodes need to return. The compute node sends this simplified column projection list along with the subquery statement to each data node. After receiving the request, the data node executes the subquery to obtain the result data, then filters the result data according to the received simplified column projection list, retaining only the columns specified in the projection list and discarding the data of the remaining columns. Finally, only the data of the retained columns is returned to the compute node via the network. In this way, the amount of data transmitted is substantially reduced during the data transmission stage. It should be noted that the construction of simplified column projection must ensure that the column order and data type are consistent with the expectations of the outer query, so as to ensure that the compute nodes can correctly parse the returned data.

[0042] By adopting the above technical solution, column pruning is advanced from the logical optimization level to the execution level, ensuring that data nodes truly return only the data of the required columns, thus achieving a substantial reduction in data transmission volume. Furthermore, by attaching column projection information when the compute node sends requests, the implementation is simple and efficient, requiring no modification to the execution logic of the data nodes, and possesses good feasibility.

[0043] As an optional embodiment, the technical solution of this application will be described in more detail with reference to specific SQL examples.

[0044] Suppose the following distributed view definition exists: CREATE VIEW v_tbl AS SELECT user_id, a, b, x, y, v1, v2, v3, age FROMusers; The view v_tbl is created based on the distributed table users, and the output columns include nine columns: user_id, a, b, x, y, v1, v2, v3, and age. The users table is a distributed table where data is hash-sharded according to user_id and stored across multiple data nodes.

[0045] The user-submitted outer query statement is as follows: SELECT m, CONCAT(a, b), my_func(x, y) FROM v_tbl WHERE age>18; This query retrieves data from the view v_tbl, and the output columns include m, CONCAT(a, b), and my_func(x, y), with the filter condition being age>18. CONCAT is a built-in string concatenation function, and my_func is a user-defined function.

[0046] Suppose the definition of the user-defined function my_func is as follows: CREATE FUNCTION my_func(p_x INT, p_y VARCHAR(100)) RETURNS VARCHAR(200) BEGIN DECLARE result VARCHAR(200); SELECT CONCAT(name, ':', p_x, '-', p_y) INTO result FROM users_infoWHERE id = p_x; RETURN result? END; This custom function takes two parameters, p_x and p_y. Inside the function body, it performs a nested query to retrieve the name field from the users_info table and then concatenates it with the parameters before returning the result.

[0047] The complete execution steps of the method in this application for this example are described in detail below: Step 1: Query Parsing. After receiving the outer query statement submitted by the user, the compute node first performs lexical analysis, syntactic analysis, and semantic analysis on the SQL statement to generate an abstract syntax tree. During the parsing process, it is identified that: the SELECT clause contains three output items, namely column m, the built-in function CONCAT(a, b) call, and the user-defined function my_func(x, y) call; the WHERE clause contains the filtering condition age>18; and the data source referenced by the query is the distributed view v_tbl. The parsing unit then passes the identification results to the acquisition unit.

[0048] Step Two: View Definition Parsing. After receiving the parsing result, the acquisition unit recognizes that the query references the distributed view v_tbl, and then retrieves the definition information of view v_tbl from the metadata storage. After obtaining the view definition, the view is expanded into a subquery, that is, FROM v_tbl in the outer query is replaced with FROM (SELECT user_id, a, b, x,y, v1, v2, v3, age FROM users) AS v_tbl. Then, all output columns of the subquery are extracted, including user_id, a, b, x, y, v1, v2, v3, and age, a total of nine columns, forming the second column set.

[0049] Step 3: Function Column Dependency Analysis. The unit performs column requirement analysis on the outer-layer query. First, it analyzes directly referenced columns: the SELECT clause directly references columns m, a, b, x, and y; the WHERE clause references column age. Then, it analyzes columns dependent on function calls: for the built-in function CONCAT(a, b), its parameters are columns a and b, which have already been identified; for the user-defined function my_func(x, y), in addition to the parameters x and y, the function body needs further analysis. After obtaining the definition of function my_func, the function body is analyzed, revealing a SELECT query that references the name and id fields of the users_info table. Therefore, in addition to explicitly depending on the parameter columns x and y, function my_func also implicitly depends on the name and id columns of the users_info table. It should be noted that if the implicitly dependent columns come from the table on which the view v_tbl is based, these columns also need to be considered; if they come from other tables, they need to be handled separately. In this example, `name` and `id` come from the `users_info` table, not the `users` table, so they do not affect column pruning in the view `v_tbl`. However, they do affect the overall query's column requirements analysis. After function column dependency analysis, the columns actually required by the outer query relative to the view `v_tbl` are: `m`, `a`, `b`, `x`, `y`, and `age`. These columns constitute the first column set.

[0050] Step 4: Cross-level column requirement propagation. The acquisition unit establishes a mapping relationship between columns in the first column set and columns in the second column set. Specifically, the mapping relationship is as follows: outer column m maps to subquery column m, outer column a maps to subquery column a, outer column b maps to subquery column b, outer column x maps to subquery column x, outer column y maps to subquery column y, and outer column age maps to subquery column age. Through this mapping relationship, the column requirements of the outer query are propagated to the subquery level.

[0051] Step 5: Redundant Column Identification and Pruning. The comparison unit compares the first column set {m, a, b, x, y, age} with the second column set {user_id, a, b, x, y, v1, v2, v3, age}. The comparison results show that the following columns exist in the second column set but not in the first column set: user_id, v1, v2, and v3. These four columns are identified as redundant columns.

[0052] Step Six: Perform Pruning. Before the compute nodes send data retrieval requests to the data nodes, the execution unit constructs a simplified column projection based on the first column set, i.e., a projection list containing only columns m, a, b, x, y, and age. The compute nodes send the subquery statement and the simplified column projection list to each data node storing the data in the users table. After receiving the request, each data node executes the subquery locally, then filters the result data according to the simplified column projection list, discarding the data in the four redundant columns user_id, v1, v2, and v3, and returning only the data in the six columns m, a, b, x, y, and age. After receiving the data returned by each data node, the compute nodes perform subsequent processing, including executing the CONCAT function, calling the my_func function, applying filtering conditions, etc., and finally return the query results to the user.

[0053] As can be seen from the complete execution process described above, the original view had nine output columns after expansion. After the column pruning optimization in this application, the data node only needs to return data from six columns, reducing the amount of network data transmission by one-third. In actual production environments, the number of columns in a view may be as high as dozens or even hundreds, while the outer query often only uses a few of them. In this case, the reduction in data transmission caused by column pruning will be even more significant.

[0054] This application provides a method and apparatus for optimizing queries by pruning columns in distributed views. It identifies the distributed view by parsing the outer query, performs column requirement analysis to obtain the first set of columns actually needed by the outer view, and simultaneously obtains all output columns after the view is expanded as the second set of columns. The two sets of columns are compared to identify redundant columns, which are then filtered during subquery execution, returning only the required column data. This application effectively solves the problem of redundant data transmission between the CN and DN caused by the full column expansion of distributed views, reducing network bandwidth consumption and improving query performance. Furthermore, this application supports column dependency analysis in complex scenarios such as built-in functions, user-defined functions, aggregate functions, and window functions. In particular, by parsing user-defined function bodies to capture implicit column dependencies, it solves complex scenario problems that traditional techniques cannot handle. In addition, this application implements column pruning at the execution level, ensuring the authenticity of the pruning effect, and the entire optimization process is transparent to the user, requiring no modification to the SQL statement, resulting in a good user experience and promotional value. In summary, this application has the following beneficial effects: 1) Effectively solves the problem of redundant data transmission in distributed views. This application analyzes the column requirements of the outer query to accurately identify the columns actually needed by the outer query and compares them with all the output columns after the distributed view is expanded, thereby determining the redundant columns in the expanded subquery. During the subquery execution phase, redundant columns are dynamically filtered, and only the column data actually needed by the outer query is returned. This fundamentally reduces the amount of redundant data transmission between computing nodes and data nodes, significantly reducing network bandwidth consumption. The performance improvement is particularly significant for wide table views with a large number of columns.

[0055] 2) Comprehensive column dependency analysis covering complex query scenarios. This application's column requirement analysis not only supports the identification of columns directly referenced in traditional clauses such as SELECT, WHERE, GROUP BY, and ORDER BY, but also deeply supports the analysis of columns depended upon by built-in function calls, custom function calls (UDFs), aggregate function calls, and window function calls. Especially for custom functions, this application captures implicit column dependencies within the function body by parsing nested queries and direct references, solving the problem that traditional column pruning techniques cannot handle implicit column dependencies in custom functions. This enables linked analysis between custom functions and distributed views, ensuring accurate determination of redundant columns even in complex function call scenarios.

[0056] 3) Significantly improves the overall performance of distributed queries. This application implements column pruning from the logical optimization level to the actual execution level through a column pruning mechanism at the execution layer. This ensures that data nodes return only the data of the required columns when returning results, avoiding the problem of disconnect between logical optimization and execution optimization. Reducing the data transmission of redundant columns not only reduces network bandwidth consumption but also alleviates the disk I / O pressure on data nodes and the data receiving, parsing, and processing burden on computing nodes, resulting in a significant increase in the overall query execution speed. Especially in complex query scenarios involving multiple function calls and nested views, the performance improvement is even more significant due to the layered pruning of redundant columns.

[0057] 4) Simplified query optimization logic, achieving automated and precise pruning. This application eliminates the need for users to manually modify SQL statements or adjust view definitions. The system automatically identifies references to distributed views and expands them into subqueries, automatically analyzes the column requirements of the outer query, automatically identifies redundant columns and performs pruning, all completely transparent to the user. By establishing a mapping relationship between outer query columns and subquery columns, precise cross-level transmission of column requirements is achieved, ensuring the accuracy of redundant column identification and avoiding query errors caused by erroneous pruning. Furthermore, the technical solution of this application can be widely applied in multiple fields such as databases, distributed databases, database middleware, distributed middleware, and distributed storage, demonstrating good versatility and promotional value.

[0058] It should be noted that, for the sake of simplicity, the foregoing method embodiments are all described as a series of actions. However, those skilled in the art should understand that this application is not limited to the described order of actions, as some steps may be performed in other orders or simultaneously according to this application. Furthermore, those skilled in the art should also understand that the embodiments described in the specification are preferred embodiments, and the actions and modules involved are not necessarily essential to this application.

[0059] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods according to the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes several instructions to cause a terminal device (which may be a mobile phone, computer, server, or network device, etc.) to execute the methods described in the various embodiments of this application.

[0060] According to another aspect of the embodiments of this application, a pruning query optimization apparatus for distributed view columns is also provided for implementing the above-described pruning query optimization method for distributed view columns. Figure 2 This is a schematic diagram of an optional query optimization apparatus for pruning distributed view columns according to an embodiment of this application, such as... Figure 2 As shown, the device may include: Parsing unit 21 is used to parse the outer query statement and identify each clause in the outer query and the distributed view referenced. The acquisition unit 22 is used to perform column requirement analysis on the outer layer query, acquire the first set of columns actually needed by the outer layer query, and acquire the second set of all output columns of the distributed view referenced in the outer layer query after it is expanded into a subquery. The column requirement analysis includes the identification of columns directly referenced by each clause and the identification of columns that function calls depend on. The comparison unit 23 is used to compare the first column set with the second column set, and to determine the columns that exist in the second column set but not in the first column set as redundant columns; Execution unit 24 is used to filter out redundant columns when executing subqueries and only return the column data corresponding to the first column set to the compute node.

[0061] The above modules parse the outer query statement, identify each clause and the referenced distributed view, laying the foundation for subsequent analysis. Then, column requirement analysis is performed on the outer query to obtain the first set of columns actually needed by the outer query, and the second set of all output columns after the distributed view is expanded into subqueries. Column requirement analysis includes identifying columns directly referenced by each clause and columns dependent on function calls. The first and second set of columns are then compared, and columns present in the second set but not in the first set are identified as redundant columns, accurately locating unnecessary columns that do not need to be transmitted. Finally, when executing the subquery, redundant columns are filtered out, and only the column data corresponding to the first set is returned to the compute node. This physically blocks the transmission of redundant columns between data nodes and compute nodes, thus solving the technical problem of redundant transmission between data nodes and compute nodes in related technologies. This completely resolves the network bandwidth waste and increased transmission load caused by default outputting all columns after the distributed view is expanded.

[0062] It should be noted that the examples and application scenarios implemented by the above modules and corresponding steps are the same, but are not limited to the content disclosed in the above embodiments. It should also be noted that the above modules, as part of the device, can run in a corresponding hardware environment, and can be implemented through software or hardware, wherein the hardware environment includes a network environment.

[0063] According to another aspect of the embodiments of this application, a server or terminal for implementing the above-described pruning query optimization method for distributed view columns is also provided.

[0064] Figure 3 This is a structural block diagram of a terminal according to an embodiment of this application, such as... Figure 3 As shown, the terminal may include: one or more (only one is shown in the figure) processors 301, memory 303, and transmission devices 305, such as... Figure 3 As shown, the terminal may also include input / output devices 307.

[0065] The memory 303 can be used to store software programs and modules, such as the program instructions / modules corresponding to the pruning query optimization method for distributed view columns in this embodiment. The processor 301 executes various functional applications and data processing by running the software programs and modules stored in the memory 303, thereby implementing the aforementioned pruning query optimization method for distributed view columns. The memory 303 may include high-speed random access memory, and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory 303 may further include memory remotely located relative to the processor 301, and these remote memories can be connected to the terminal via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0066] The aforementioned transmission device 305 is used to receive or send data via a network, and can also be used for data transfer between the processor and memory. Specific examples of the network described above may include wired networks and wireless networks. In one example, the transmission device 305 includes a Network Interface Controller (NIC), which can be connected to other network devices and routers via a network cable to communicate with the Internet or a local area network. In another example, the transmission device 305 is a radio frequency (RF) module used for wireless communication with the Internet.

[0067] Specifically, memory 303 is used to store application programs.

[0068] The processor 301 can invoke the application program stored in the memory 303 via the transmission device 305 to perform the following steps: Parse the outer query statement, identifying each clause and the referenced distributed view; perform column requirement analysis on the outer query to obtain the first set of columns actually needed by the outer query, and obtain the second set of all output columns of the distributed view referenced in the outer query after being expanded into subqueries. The column requirement analysis includes identifying columns directly referenced by each clause and columns depended on by function calls; compare the first and second set of columns, and determine the columns that exist in the second set but not in the first set as redundant columns; when executing the subquery, filter out the redundant columns and only return the column data corresponding to the first set to the compute node.

[0069] Optionally, specific examples in this embodiment can refer to the examples described in the above embodiments, and will not be repeated here.

[0070] Those skilled in the art will understand that Figure 3 The structure shown is for illustrative purposes only. The terminal can be a smartphone (such as an Android phone, an iOS phone, etc.), a tablet computer, a PDA, a mobile internet device (MID), a PAD, or other terminal devices. Figure 3 This does not limit the structure of the aforementioned electronic device. For example, the terminal may also include components that are more... Figure 3 The more or fewer components shown (such as network interfaces, display devices, etc.), or having the same Figure 3 The different configurations shown.

[0071] Those skilled in the art will understand that all or part of the steps in the various methods of the above embodiments can be implemented by a program instructing the hardware related to the terminal device. The program can be stored in a computer-readable storage medium, which may include: flash drive, read-only memory (ROM), random access memory (RAM), disk or optical disk, etc.

[0072] Embodiments of this application also provide a storage medium. Optionally, in this embodiment, the storage medium can be used to execute program code for a pruning query optimization method for distributed view columns.

[0073] Optionally, in this embodiment, the storage medium may be located on at least one of the network devices in the network shown in the above embodiment.

[0074] Optionally, in this embodiment, the storage medium is configured to store program code for performing the following steps: Parse the outer query statement, identifying each clause and the referenced distributed view; perform column requirement analysis on the outer query to obtain the first set of columns actually needed by the outer query, and obtain the second set of all output columns of the distributed view referenced in the outer query after being expanded into subqueries. The column requirement analysis includes identifying columns directly referenced by each clause and columns depended on by function calls; compare the first and second set of columns, and determine the columns that exist in the second set but not in the first set as redundant columns; when executing the subquery, filter out the redundant columns and only return the column data corresponding to the first set to the compute node.

[0075] Optionally, specific examples in this embodiment can refer to the examples described in the above embodiments, and will not be repeated here.

[0076] Optionally, in this embodiment, the storage medium may include, but is not limited to, various media capable of storing program code, such as USB flash drives, read-only memory (ROM), random access memory (RAM), portable hard drives, magnetic disks, or optical disks.

[0077] The sequence numbers of the embodiments in this application are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0078] If the integrated units in the above embodiments are implemented as software functional units and sold or used as independent products, they can be stored in the aforementioned computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause one or more computer devices (which may be personal computers, servers, or network devices, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application.

[0079] In the above embodiments of this application, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0080] In the several embodiments provided in this application, it should be understood that the disclosed client can be implemented in other ways. The device embodiments described above are merely illustrative; for example, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces, indirect coupling or communication connection between units or modules, and may be electrical or other forms.

[0081] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0082] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0083] The above description is only a preferred embodiment of this application. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of this application, and these improvements and modifications should also be considered within the scope of protection of this application.

Claims

1. A method for optimizing pruning queries for distributed view columns, characterized in that, include: Parse the outer query statement and identify the clauses and referenced distributed views within it; Perform column requirement analysis on the outer query to obtain the first set of columns actually needed by the outer query, and obtain the second set of all output columns of the distributed view referenced in the outer query after it is expanded into a subquery. The column requirement analysis includes the identification of columns directly referenced by each clause and the identification of columns that function calls depend on. The first set of columns is compared with the second set of columns, and columns that exist in the second set of columns but not in the first set of columns are determined to be redundant columns; When executing the subquery, the redundant columns are filtered out, and only the column data corresponding to the first column set is returned to the computing node.

2. The method according to claim 1, characterized in that, Perform column requirement analysis on the outer query to obtain the first set of columns actually needed by the outer query, including: Perform column requirement analysis on the outer query to identify the columns that built-in function calls, custom function calls, aggregate function calls, and window function calls depend on, and save them to the first column set.

3. The method according to claim 2, characterized in that, Perform column requirement analysis on the outer query to identify the columns that the custom function call depends on, and save them to the first column set, including: Parse the function body of the custom function and save the columns directly or indirectly referenced within the function body to the first column set.

4. The method according to claim 1, characterized in that, Perform column requirement analysis on the outer query to obtain the first set of columns actually needed by the outer query, including: Obtain the columns involved in the outer query results, the columns involved in the filtering conditions, the columns required for grouping operations, the columns required for sorting operations, the columns involved in the DISTINCT deduplication operation, and the columns involved in the HAVING condition filtering operation, and save the identified columns to the first column set.

5. The method according to claim 1, characterized in that, Obtain the second column set of all output columns of the distributed view referenced in the outer query after it has been expanded into a subquery, including: Obtain the view definition of the distributed view; Expand the view definition into a subquery; Extract all output columns of the subquery and use them as the second column set.

6. The method according to claim 1, characterized in that, Before comparing the first set of columns with the second set of columns, the method further includes: Establish a mapping relationship between each column in the first column set and each column in the second column set, and pass the column requirements of the outer query to the corresponding columns of the subquery based on the mapping relationship.

7. The method according to any one of claims 1 to 6, characterized in that, Filtering the redundant columns includes: Before the computing node sends a data retrieval request to the data node, a simplified column projection is constructed based on the first column set, and the simplified column projection is sent to the data node to instruct the data node to return column data only according to the simplified column projection after executing the subquery.

8. A pruning query optimization device for distributed view columns, characterized in that, include: The parsing unit is used to parse the outer query statement and identify the clauses in the outer query as well as the distributed views referenced. The acquisition unit is used to perform column requirement analysis on the outer query, acquire the first set of columns actually needed by the outer query, and acquire the second set of all output columns of the distributed view referenced in the outer query after it is expanded into a subquery. The column requirement analysis includes the identification of columns directly referenced by each clause and the identification of columns that function calls depend on. The comparison unit is used to compare the first column set with the second column set, and to determine the columns that exist in the second column set but do not exist in the first column set as redundant columns; The execution unit is used to filter out the redundant columns when executing the subquery and only return the column data corresponding to the first column set to the computing node.

9. A computer-readable storage medium, characterized in that, The storage medium includes a stored program, wherein the program executes the method described in any one of claims 1 to 7 when it is run.

10. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, The processor executes the method described in any one of claims 1 to 7 through the computer program.