Data query method and device, computer device, computer readable storage medium and program product

Abstract

The application relates to a data query method and device, computer equipment, a computer readable storage medium and a computer program product. The method comprises the following steps: analyzing a query statement to obtain an initial operation instruction sequence; identifying a data query range control parameter in the query statement and comparing initial execution sequences of a data scanning node and a data filtering node; if the initial execution sequence of the data scanning node is in front of the data filtering node and the query range control parameter is identified in the query statement, the data query node is configured according to the data query range control parameter, the data filtering node is configured to be executed after the data scanning node, and a target operation instruction sequence corresponding to a data query request is obtained; and the target operation instruction sequence is executed to obtain a query result corresponding to the data query request. The method can improve the data query efficiency.

Description

Technical Field

[0001] This application relates to the field of computer data processing technology, and in particular to a data query method, apparatus, computer equipment, computer-readable storage medium, and computer program product. Background Technology

[0002] Currently, in the field of database query processing, especially in paginated queries (such as LIMIT ...OFFSET ...) scenarios with large data volumes, significant performance bottlenecks exist in related technologies. For example, when the query statement contains computationally expensive projection expressions (such as JSON parsing, regular expression matching, complex functions, etc.), the database system usually needs to perform expression calculations on all records that meet the conditions at the execution layer. This leads to unnecessary CPU computation and memory resource consumption, resulting in a sharp increase in response time, a decrease in overall system throughput, and an impact on user experience and system efficiency. Summary of the Invention

[0003] Therefore, it is necessary to provide a data query method, apparatus, computer equipment, computer-readable storage medium, and computer program product that can improve the efficiency of data query in order to address the above-mentioned technical problems.

[0004] Firstly, this application provides a data query method, the method comprising:

[0005] Retrieve the query statement in response to a data query request;

[0006] The query statement is parsed to obtain an initial operation instruction sequence; the initial operation instruction sequence includes a data scanning node and a data filtering node;

[0007] The query statement is used to identify data query range control parameters and to compare the initial execution order of the data scanning nodes and data filtering nodes.

[0008] If the initial execution order of the data scanning node is before the data filtering node and the query range control parameter is identified in the query statement, the data filtering node is configured according to the data query range control parameter, and the data filtering node is configured to be executed on the data scanning node, thereby obtaining the target operation instruction sequence corresponding to the data query request;

[0009] Execute the target operation instruction sequence to obtain the query result corresponding to the data query request.

[0010] In some embodiments, the data query range control parameters include a query start offset and a query range length; configuring the data filtering node according to the data query range control parameters includes:

[0011] The data query range control parameter is pushed down to the data query node so that the data scanning node skips the data records of the query start offset and obtains the number of data records of the query range length when performing data scanning.

[0012] In some embodiments, the data filtering node includes a projection node; parsing the query statement to obtain an initial operation instruction sequence includes:

[0013] The query statement is parsed to obtain the fields to be returned corresponding to the data query request;

[0014] Construct a projection expression based on the field to be returned; perform a projection operation on the data to be returned based on the projection expression;

[0015] Configure the projection node according to the projection expression;

[0016] The step of configuring the data filtering node to execute on the data scanning node includes:

[0017] Configure the data filtering node as the data scanning node to obtain the number of data records within the query range and then execute it.

[0018] In some embodiments, configuring the data filtering node to be executed after the data scanning node obtains the number of data records of the query range length includes:

[0019] In the target operation instruction sequence, the data filtering node is set downstream of the data scanning node;

[0020] The execution of each projection operation of the projection node is configured to be triggered by each data record output by the data scanning node.

[0021] In some embodiments, before configuring the data filtering node to execute the data scanning node, the method further includes:

[0022] The projection computation cost of the data filtering node is evaluated based on the complexity of the data type involved in the projection expression, the computational logic complexity of the projection expression, and whether the projection expression is at least one of the user-defined functions.

[0023] If the projection calculation cost is higher than a preset threshold, the step of configuring the data filtering node to be executed on the data scanning node is performed.

[0024] In some embodiments, the data scanning node includes a full table scan operator or an index scan operator; the method further includes:

[0025] An internal counter is maintained in the data scanning node, which is used to count the data records that have been scanned by the data scanning node;

[0026] When the value of the internal counter reaches the amount of query data indicated by the data query range control parameter, the data scanning node is controlled to terminate the data scanning operation.

[0027] Secondly, this application also provides a data query device, the device comprising:

[0028] The response module is used to respond to data query requests and obtain the query statement;

[0029] The parsing module is used to parse the query statement to obtain an initial operation instruction sequence; the initial operation instruction sequence includes a data scanning node and a data filtering node;

[0030] The comparison module is used to identify data query range control parameters in the query statement and to compare the initial execution order of the data scanning nodes and data filtering nodes.

[0031] The configuration module is used to configure the data filtering node according to the data query range control parameter if the initial execution order of the data scanning node is before the data filtering node and the query range control parameter is identified in the query statement, and configure the data filtering node to be executed on the data scanning node, so as to obtain the target operation instruction sequence corresponding to the data query request;

[0032] The execution module is used to execute the target operation instruction sequence to obtain the query result corresponding to the data query request.

[0033] Thirdly, this application also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps included in any of the aforementioned data query method embodiments.

[0034] Fourthly, this application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps included in any of the aforementioned data query method embodiments.

[0035] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, implements the steps included in any of the aforementioned data query method embodiments.

[0036] The aforementioned data query method, apparatus, computer equipment, computer-readable storage medium, and computer program product, through the data query method provided in the embodiments of the present invention, identify range control parameters during the query optimization stage and intelligently push them down to the scanning layer, while configuring data filtering nodes for delayed execution, thereby avoiding redundant calculations from the source. This method ensures that the high-cost projection expression calculation only applies to the very small number of target records that ultimately need to be returned, thus greatly reducing the ineffective consumption of central processing unit (CPU) and memory resources. This not only significantly improves the response speed of large offset pagination queries but also effectively improves the overall throughput of the database system in concurrent scenarios by reducing the resource consumption of individual queries, ultimately providing users with a more efficient and stable data service experience. Attached Figure Description

[0037] To more clearly illustrate the technical solutions in the embodiments of this application or related technologies, the drawings used in the description of the embodiments of this application or related technologies will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0038] Figure 1 This is a diagram illustrating the application environment of a data query method in one embodiment.

[0039] Figure 2 This is a flowchart illustrating a data query method in one embodiment;

[0040] Figure 3 This is a structural block diagram of a data query device in one embodiment;

[0041] Figure 4 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation

[0042] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0043] It should be noted that the terms "first," "second," etc., used in this application can be used to describe various elements, but these elements are not limited by these terms. These terms are only used to distinguish the first element from the second element. The terms "comprising" and "having," and any variations thereof, used in this application, are intended to cover non-exclusive inclusion. The term "multiple" used in this application refers to two or more. The term "and / or" used in this application refers to one of the embodiments, or any combination of multiple embodiments.

[0044] Explanation of related terms:

[0045] The LIMIT / OFFSET clause is a pagination mechanism in SQL queries. LIMIT specifies the maximum number of rows to return, and OFFSET specifies the number of rows to skip. For example, LIMIT 10 OFFSET 100 means skipping the first 100 rows and returning only the next 10.

[0046] Operator: The basic unit of operation in a database execution plan, such as Scan, Join, Sort, Limit, etc. Each operator is responsible for completing a specific data processing task.

[0047] Operator Pushdown: This refers to merging or "pushing down" certain operations (such as filtering, aggregation, sorting, LIMIT, etc.) in the execution plan to a position closer to the data source (such as the storage engine) in advance, in order to reduce the amount of intermediate data and improve execution efficiency.

[0048] Scan operator: refers to the operator used to read data from the underlying storage, that is, the data scanning operator. Common forms include full table scan and index scan.

[0049] Projection Expression: This refers to the fields or expressions that a SELECT query needs to return. For example, in SELECT name, age*2, name and age*2 are projection expressions. Scan is typically responsible for reading these projection fields.

[0050] Storage Engine: One of the core modules of a database system, responsible for the physical storage and retrieval of data. Common storage engines include InnoDB and RocksDB.

[0051] Late Projection: An optimization strategy that refers to delaying projection operations as much as possible during query execution, prioritizing filtering, sorting, or pagination, thereby reducing unnecessary field readings and calculations.

[0052] Execution Plan: An operation instruction tree generated by the database query optimizer, describing how to access tables, use indexes, perform joins, etc. Each node typically corresponds to an operator.

[0053] Pagination optimization: Optimization methods for queries containing LIMIT / OFFSET, aiming to reduce unnecessary scans, sorting, and calculations, and improve pagination performance with large amounts of data.

[0054] Push-Up / Push-Down Strategy: A strategy for adjusting operator positions during query optimization. Push-Up means executing the operation closer to the root node, while Push-Down means executing the operation closer to the data source.

[0055] Before describing the embodiments of the present invention, the related technologies and their problems will be explained:

[0056] With the development of big data applications, tables containing millions or even billions of records in databases are now commonplace. In typical scenarios such as pagination, scrolling, and BI reports, SQL queries often include LIMIT and OFFSET clauses to control the returned result set window. The semantics of such queries are "skip the first OFFSET rows and return the LIMIT rows," for example:

[0057] SELECT id, compute_heavy_expr(col1, col2) FROM large_table ORDER BYts LIMIT 100 OFFSET 100000;

[0058] In traditional database systems, this type of query is typically handled by performing data scanning, projection expression calculation, and sorting at the execution layer. The first LIMIT + OFFSET rows are retained, the first OFFSET rows are discarded, and only the remaining data is returned as the result. While this approach is semantically correct, it suffers from significant performance bottlenecks, especially when: the OFFSET value is large; or the computational cost of the projection expression is high, such as when the query contains complex expressions or UDFs (user-defined functions).

[0059] To address the aforementioned issues, database systems in related technologies may employ optimizations. For instance, some databases (such as PostgreSQL, MySQL, and ClickHouse) support pushing down LIMIT to sub-operators (e.g., Index Scan or Table Scan) during the optimizer or executor construction phase. This allows the underlying operators to produce only the required number of rows, avoiding unnecessary data reads. However, this approach only pushes down LIMIT; when an OFFSET clause is present, it is not equivalently pushed down to the storage layer, especially in distributed scenarios. Because OFFSET essentially "skips the first n rows," and its storage does not support cursor-style jumps or offset reads, LIMIT / OFFSET cannot be pushed down as a whole. The projection expression still undergoes excessive redundant evaluation at the execution layer, failing to reduce CPU consumption.

[0060] Therefore, the key problem to be solved is how to identify the LIMIT / OFFSET pattern in the execution plan and combine it with expression analysis to selectively delay or skip the evaluation of the expression.

[0061] The data query method provided in this application embodiment can be applied to, for example... Figure 1 In the application environment shown, terminal 102 communicates with server 104 via a network. A data storage system can store the data that server 104 needs to process. The data storage system can be integrated onto server 104 or placed on a cloud or other network server. In response to a data query request received through terminal 102, server 104 obtains a query statement; server 104 parses the query statement to obtain an initial operation instruction sequence; the initial operation instruction sequence includes a data scanning node and a data filtering node; server 104 identifies data query range control parameters in the query statement and compares the initial execution order of the data scanning node and the data filtering node; if the initial execution order of the data scanning node is before the data filtering node and the query range control parameter is identified in the query statement, server 104 configures the data filtering node according to the data query range control parameter and configures the data filtering node to be executed on the data scanning node, obtaining the target operation instruction sequence corresponding to the data query request; server 104 executes the target operation instruction sequence, obtaining the query result corresponding to the data query request and returning it to terminal 102.

[0062] Terminal 102 can be, but is not limited to, various personal computers, laptops, smartphones, tablets, drones, low-altitude aircraft, IoT devices, and portable wearable devices. IoT devices can include smart speakers, smart TVs, smart air conditioners, smart in-vehicle devices, and projection equipment. Portable wearable devices can include smartwatches, smart bracelets, and head-mounted displays. Head-mounted displays can be virtual reality (VR) devices, augmented reality (AR) devices, and smart glasses. Server 104 can be a standalone physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing cloud computing services.

[0063] In one exemplary embodiment, such as Figure 2 As shown, a data query method is provided, which can be applied to... Figure 1 Taking the server in the example, the following steps are included:

[0064] Step 202: In response to the data query request, obtain the query statement.

[0065] The data query request can be sent by a pre-defined client application or user terminal. It requests data based on specific conditions, such as retrieving data for users whose "User ID" is between 10 and 100. The data query request includes a query statement conforming to a pre-defined syntax, such as a SELECT statement written in Structured Query Language (SQL). This query statement defines the conditions that the data to be retrieved from the database must meet, the fields to be returned, and the sorting and quantity limits of the results.

[0066] Step 204: Parse the query statement to obtain an initial operation instruction sequence; the initial operation instruction sequence includes a data scanning node and a data filtering node.

[0067] The process involves lexical and syntactic analysis of the acquired query statements to ensure their correctness. Semantic analysis is then performed on queries that pass syntactic analysis to clarify the meaning of tables, fields, etc., within the statement. Based on this, the database system's optimizer generates an initial sequence of operation instructions, also known as a logical query plan or initial physical execution plan. This initial sequence of instructions can be a directed acyclic graph (DAG) consisting of multiple operator nodes, each representing a specific data operation unit. These operator nodes are connected by data flow and collectively describe the complete computational process required to execute the query. The data scan node, corresponding to the data scan operator node, reads raw data rows from the database's underlying storage engine. Common implementations include the Full TableScan operator, which sequentially reads all data in a table, and the Index Scan operator, which uses an index structure to quickly locate and read data that meets the conditions. The data scan node is located at the data source of the execution plan and is responsible for providing the initial dataset to upstream nodes. Correspondingly, the data filtering node processes, calculates, or filters intermediate results from the data scan node or other nodes. Specifically, data filtering nodes can include projection operators, which are used to calculate and select the final output fields. The final output fields may consist of the original columns or may be calculated by complex projection expressions that include functions such as JSON_EXTRACT, string processing, arithmetic operations, etc.; and selection or filter operators, which are used to filter rows based on the conditions of the WHERE clause.

[0068] In this embodiment of the invention, the initial execution order can be preliminarily determined by the database optimizer based on query semantics and default optimization rules. For example, the optimizer can generate a plan that conforms to the logical calculation order: that is, first, data is retrieved from the disk through the data scan node, and then the results are passed to the data filtering node for subsequent processing. For example, for the query statement "SELECT id, JSON_EXTRACT(info, '$.name') FROM large_table LIMIT 10 OFFSET100000;", the initial operation instruction sequence initially generated by the optimizer may be: a Table Scan node (data scan node) for scanning all data in large_table, followed by a Projection node (data filtering node) for calculating the JSON_EXTRACT expression and outputting the id and name fields.

[0069] Step 206: Identify the data query range control parameters in the query statement and compare the initial execution order of the data scanning nodes and data filtering nodes.

[0070] In this embodiment of the invention, the optimizer performs deeper semantic analysis and plan structure analysis to identify opportunities for optimizing the query efficiency of the current query statement. Specifically, the optimizer can scan the query statement and identify parameters used to limit the range of the result set ultimately returned to the user. Data query range control parameters can be used to limit the range of the output query results through methods such as data pagination or window extraction. For example, in SQL, data query range control parameters can consist of a LIMIT clause (specifying the number of rows of data to return) and an OFFSET clause (specifying the number of rows of data to skip from the start of the scan). For example, LIMIT 10 OFFSET 100000 means that the first 100,000 rows of records should be skipped, and then the next 10 rows of records should be returned. These parameters that define the "target data window" of the query are collectively referred to as data query range control parameters.

[0071] Furthermore, the optimizer examines the structure of the initial operation instruction sequence generated above, comparing the relative positions and data flow relationships of the data scan nodes and data filter nodes (especially the projection nodes that perform high-cost computations) in the plan tree. The optimizer needs to determine whether there exists a structure where the data filter node is downstream of the data scan node, and its computational operations depend on the complete or intermediate result set output by the data scan node that has not been range-filtered.

[0072] Step 208: If the initial execution order of the data scanning node is before the data filtering node and the query range control parameter is identified in the query statement, configure the data filtering node according to the data query range control parameter, and configure the data filtering node to be executed on the data scanning node to obtain the target operation instruction sequence corresponding to the data query request.

[0073] Specifically, if the initial execution order of the data scanning node precedes that of the data filtering node and the query range control parameter is identified in the query statement, then it is determined that the current query has significant optimization potential, and intelligent reconstruction of the initial plan is initiated to generate an efficient sequence of target operation instructions. Specifically, the semantics of the range control parameter are "pushed down" to the data scanning layer. The optimizer assigns the values ​​of OFFSET (number of skipped rows) and LIMIT (number of returned rows) as control attributes to the original data scanning node (such as Table Scan or Index Scan). This enhances the node into a range-aware scan node capable of understanding and directly executing "skip" and "limit" instructions. This node can be configured to proactively skip the first OFFSET records during the scan, and then only scan the subsequent LIMIT records, thereby significantly reducing the amount of data to be processed from the data source.

[0074] To achieve deferred evaluation or selective execution of expression computation, the optimizer adjusts the plan structure to configure data filtering nodes to execute before data scanning nodes. This ensures that the computational logic of data filtering nodes (such as projection nodes) is strictly confined to the "target data window" output by range-aware scan nodes, which has already undergone skipping and quantification. This can be achieved by explicitly placing data filtering nodes directly downstream of range-aware scan nodes in the plan tree and establishing a record-by-record triggering mechanism. In this way, high-cost projection expression computations (such as JSON_EXTRACT) will not be performed on ineffective records that will ultimately be skipped by OFFSET or LIMIT. Continuing the example above, the optimizer optimizes the initial Table Scan -> Projection sequence to: Range-AwareTable Scan (OFFSET 100000, LIMIT 10) -> Projection. Here, Range-Aware TableScan skips 100,000 rows and reads only 10 rows, and the Projection node only performs JSON_EXTRACT computation on these final 10 rows.

[0075] Step 210: Execute the target operation instruction sequence to obtain the query result corresponding to the data query request.

[0076] The process involves loading and executing an optimized sequence of target operation instructions. Specifically, the process might include: the range-aware scan node interacting with the storage engine, skipping the number of rows specified by OFFSET, and reading only the target number of rows specified by LIMIT, after which the scan can be terminated early. These few precise target records are output one by one. Downstream data filtering nodes (such as projection nodes) then work, performing potentially costly projection expression calculations only on these surviving records. Finally, the calculated result set is returned to the client.

[0077] This invention, by pushing pagination operations down to the storage scan layer and combining this with delayed expression computation, fundamentally avoids the high-cost computational operations performed by the database system on massive amounts of intermediate invalid data (especially data skipped by offsets). In the example, the JSON_EXTRACT function is reduced from over 100,000 calculations to only 10, resulting in a performance improvement of several orders of magnitude. Reducing unnecessary data transmission and computation significantly shortens the time required to process large offset pagination queries, providing users with a faster response experience. The reduced resource consumption of a single query allows the database system to concurrently process more such queries under the same hardware conditions, improving overall system throughput, and is particularly suitable for high-concurrency, large-volume online analytical processing (OLAP) or data service scenarios.

[0078] In some embodiments, the data query range control parameters include a query start offset and a query range length; configuring the data filtering node according to the data query range control parameters includes:

[0079] The data query range control parameter is pushed down to the data query node so that the data scanning node skips the data records of the query start offset and obtains the number of data records of the query range length when performing data scanning.

[0080] In database queries, pagination or result set window control is a common requirement, and its core semantics are defined by the two key clauses OFFSET and LIMIT. To accurately describe this optimization process, the data query range control parameters need to be specified. In this embodiment, the data query range control parameters are explicitly defined as the query start offset and the query range length. The query start offset corresponds to the OFFSET clause in SQL, specifying the number of records to skip from the beginning of the result set; while the query range length corresponds to the LIMIT clause, limiting the maximum number of records expected to be returned. For example, in the query ... LIMIT 10 OFFSET 100000, the query start offset is 100000, and the query range length is 10.

[0081] After clarifying the specific composition of the parameters, the database optimizer pushes down the data query range control parameters to the data query node when generating the physical execution plan. This directly passes the identified query start offset (e.g., 100,000) and query range length (e.g., 10) to the data scan nodes (e.g., TableScan or Index Scan operators) at the bottom of the plan tree. By pushing down the data query range control parameters, the data scan node, which was originally only responsible for reading raw data, is given new control logic. This node is configured to: first, internally count based on the query start offset and skip a corresponding number of initial data records without returning them to the upper-level operator; then, it begins acquiring valid data and immediately stops scanning when the number of acquired records reaches the query range length.

[0082] By pushing down the specific semantics of range control parameters to the data scanning nodes, this embodiment of the invention achieves precise filtering from the data source. This avoids reading a large number of invalid intermediate records from the storage layer to the computation layer, which not only significantly reduces unnecessary data transmission overhead and alleviates the processing burden on all subsequent operators, but also lays a solid foundation for early termination of queries and immediate release of resources. It is a key step in optimizing from the I / O level.

[0083] In some embodiments, the data filtering node includes a projection node; parsing the query statement to obtain an initial operation instruction sequence includes:

[0084] The query statement is parsed to obtain the fields to be returned corresponding to the data query request;

[0085] Construct a projection expression based on the field to be returned; perform a projection operation on the data to be returned based on the projection expression;

[0086] Configure the projection node according to the projection expression;

[0087] The step of configuring the data filtering node to execute on the data scanning node includes:

[0088] Configure the data filtering node as the data scanning node to obtain the number of data records within the query range and then execute it.

[0089] To achieve accurate calculation and return of specific fields or complex expressions in the query results, the data filtering node needs to be specified and its configuration logic defined. In this embodiment of the invention, the data filtering node specifically includes a projection operator. This node plays a crucial role in defining the final result set pattern and content in the query execution plan.

[0090] Specifically, the query statement is parsed to obtain an initial operation instruction sequence that includes the construction of a projection node. During parsing, the database optimizer extracts a list of fields to be returned from the SELECT clause of the query statement. These fields may be raw columns from the data table (such as id, name), or expressions that require calculation, such as price * quantity, UPPER(username), or even complex data parsing like JSON_EXTRACT(info, '$.name'). Based on this information about the fields to be returned, the optimizer constructs the corresponding projection expression, which encapsulates the calculation logic from the raw data to the final output value. Subsequently, the optimizer configures a projection node in the initial operation instruction sequence. The core function of this node is to perform a projection operation on the data passed from the upstream node based on the constructed projection expression, thereby generating output rows that meet the query requirements.

[0091] Furthermore, this includes precise control over the timing of projection node calculations. The optimizer configures the projection node to a deferred execution state, meaning that the projection operation of this node must only be triggered after the data scanning node (which has now been enhanced into a range-aware scanning node) has successfully acquired the number of data records required for the query range length (i.e., the LIMIT value).

[0092] Through the above method, this embodiment of the invention precisely anchors the abstract data filtering operation to projection calculation, a typical scenario that consumes the most CPU resources. By explicitly instructing the projection node to work only on the final small number of target records, it ensures that high-cost expression calculation resources are used efficiently, thereby achieving maximum performance improvement and resource saving in complex expression queries.

[0093] In some embodiments, configuring the data filtering node to be executed after the data scanning node obtains the number of data records of the query range length includes:

[0094] In the target operation instruction sequence, the data filtering node is set downstream of the data scanning node;

[0095] The execution of each projection operation of the projection node is configured to be triggered by each data record output by the data scanning node.

[0096] Having clarified the delayed execution strategy for the projection node, to ensure that the optimization idea can be accurately understood and executed by the database execution engine, it is necessary to further elaborate on the specific implementation mechanism of this strategy at the physical execution plan level. In this embodiment of the invention, when generating the final target operation instruction sequence, the optimizer performs precise operator position arrangement, placing the projection node downstream of the data scan node. This positional relationship establishes a clear data flow direction in a tree-like or pipeline-like execution plan: that is, data must first be processed by the data scan node before its output can be used as the input of the projection node. This structural arrangement is the physical basis for achieving computational latency.

[0097] Furthermore, unlike the method of having the projection node wait for the data scanning node to process all LIMIT records before performing batch calculations, this embodiment of the invention employs a more efficient per-row triggering mechanism. Under this mechanism, whenever the data scanning node successfully skips the record specified by OFFSET and begins to output a valid target record, it immediately triggers the downstream projection node to perform a projection operation on that record.

[0098] In this embodiment of the invention, a downstream placement and row-by-row triggering mechanism achieves precise latency in projection calculations, ensuring no premature calculations occur. Furthermore, by maintaining the pipelined nature of the execution flow, the additional latency caused by waiting for all data to be ready is avoided, allowing the result set to begin returning as quickly as possible. Once the LIMIT threshold is reached, the entire data flow can be terminated immediately, including stopping the triggering of new projection calculations, thereby achieving on-demand allocation and rapid release of resources.

[0099] In some embodiments, before configuring the data filtering node to execute the data scanning node, the method further includes:

[0100] The projection computation cost of the data filtering node is evaluated based on the complexity of the data type involved in the projection expression, the computational logic complexity of the projection expression, and whether the projection expression is at least one of the user-defined functions.

[0101] If the projection calculation cost is higher than a preset threshold, the step of configuring the data filtering node to be executed on the data scanning node is performed.

[0102] In this invention, considering that not all queries are suitable for pushdown optimization when building an efficient data query system, and that the benefits of optimization for projection operations, which have very low computational cost, may not outweigh the increased complexity, this embodiment of the invention introduces a cost-aware intelligent decision-making mechanism. Before finally deciding to configure the projection node in deferred execution mode, the computational cost of the projection node is first prospectively evaluated.

[0103] Specifically, the optimizer analyzes the projection expressions carried by the projection nodes and evaluates their computational complexity from multiple dimensions: Data type complexity: The data type operated on by the expression is a crucial factor. The computational cost of processing basic integers or short strings differs by orders of magnitude from processing large JSON documents (BLOB / CLOB types) or complex spatial data. Computational logic complexity: The computational logic of the expression itself determines its CPU consumption. Simple arithmetic operations or field references are extremely inexpensive, while those involving regular expression matching, recursive calculations, or complex string processing functions (such as fuzzy matching) are costly. User-defined function (UDF) calls: If the projection expression contains user-defined functions, this is generally considered a strong signal of high computational cost. This is because the logic of a UDF can be very complex, and the optimizer often cannot perceive its internal consumption.

[0104] The optimizer will consider one or more of the above factors to assign a quantitative score to the computational cost of the projection node and compare it with a preset threshold. This threshold can be dynamically adjusted based on system load, hardware configuration, or operational experience.

[0105] The aforementioned optimization process will only be executed when the evaluation results explicitly indicate that the computational cost of the projection node is higher than a preset threshold. This involves pushing down the LIMIT / OFFSET parameters to the data scan node and configuring the projection node to deferred execution mode. Conversely, if the computational cost of the projection expression is determined to be low, the system will maintain the traditional execution plan to avoid unnecessary optimization overhead.

[0106] By introducing a cost-aware intelligent decision-making mechanism, this invention ensures that optimization methods are used effectively, avoiding unnecessary system overhead. This allows the optimization scheme to adapt to various complex query loads, maintaining stability and efficiency when facing massive simple queries, while accurately optimizing when encountering high-cost computational queries that truly consume resources, thereby achieving optimal overall system performance.

[0107] In some embodiments, the data scanning node includes a full table scan operator or an index scan operator; the method further includes:

[0108] An internal counter is maintained in the data scanning node, which is used to count the data records that have been scanned by the data scanning node;

[0109] When the value of the internal counter reaches the amount of query data indicated by the data query range control parameter, the data scanning node is controlled to terminate the data scanning operation.

[0110] In addition to implementing the pushdown of query range control parameters and delayed expression calculation, this embodiment of the invention further enhances the data scanning process itself to optimize resource utilization and improve query response speed. It introduces a dynamic termination mechanism so that data scanning nodes can not only skip and limit reading, but also stop working immediately after completing their tasks, thereby avoiding unnecessary subsequent operations.

[0111] Specifically, after the data scanning node (whether a full table scan operator or an index scan operator) is configured as a range-aware scan node and begins execution, it maintains an internal counter. This counter is initially set to zero, and its purpose is to precisely track the number of valid data records that have been successfully acquired and are ready to be output to upstream nodes. The records counted here are the target records read after skipping the number of records specified by the offset.

[0112] During the scan, the internal counter increments once for each data record that meets the criteria. Simultaneously, the data scanning node continuously compares the current counter value with the number of rows to be returned (the LIMIT value) specified in the query range control parameters. If the internal counter value has reached or exceeded the specified number of rows to return, the data scanning node immediately terminates its current data scanning operation. This termination is mandatory and immediate, meaning the scanner immediately stops reading new data pages or index entries from the storage engine and ends its workflow.

[0113] The dynamic termination mechanism in this embodiment of the invention can prevent resource waste. Considering that in traditional processing, even if the LIMIT condition is met, the scanning process may still read more data due to the inertia of the execution plan, this embodiment of the invention ensures that data scanning stops instantly after the demand is met through internal counting and immediate termination. This not only saves valuable I / O bandwidth and CPU cycles but also significantly shortens the overall query execution time, allowing system resources to be released more quickly to serve other concurrent requests, thereby improving the overall throughput of the system, especially under high load environments.

[0114] The following describes in detail another embodiment of the data query method provided by the present invention, using a specific application scenario. This embodiment takes the overall process of the method executed in a database server as an example.

[0115] Suppose a user behavior analysis system contains a table named `user_log` that records tens of millions of user action logs. Each log entry contains an `id`, a large JSON string field `info` storing detailed user information (such as name, device, and action content), and a timestamp. Now, the analyst needs to execute the following SQL query to retrieve specific user records after a certain point in time and display them in a paginated format:

[0116] SQL;

[0117] SELECT id, json_extract(info, '$.name') AS name;

[0118] FROM user_log;

[0119] ORDER BY timestamp DESC;

[0120] LIMIT 10 OFFSET 100000;

[0121] This query aims to skip the first 100,000 records and return the IDs and names (parsed from the JSON) of the next 10 records. `json_extract` is a computationally expensive function. The processing flow for this query is as follows:

[0122] Step 1: Query Semantic Analysis and Plan Generation: The database optimizer responds to the data query request by obtaining and parsing the query statement. During parsing, the optimizer identifies the data query range control parameters contained within, namely LIMIT 10 (query range length) and OFFSET 100000 (query starting offset). Simultaneously, the optimizer identifies that the fields to be returned defined in the query contain a complex projection expression json_extract(info, '$.name').

[0123] Based on this, the optimizer constructs an initial sequence of operation instructions. This sequence initially includes a data scan node (such as a Table Scan operator) for reading data from the user_log table, and a data filter node (i.e., a projection node) for calculating the json_extract expression and outputting id and name. The optimizer confirms through comparison that the execution order of the data scan node precedes that of the data filter node in this initial plan. Given that the range control parameters have been identified and the plan structure meets the optimization conditions, the optimizer records its pushdown requirement.

[0124] Step 2: Physical Plan Construction and Execution Strategy Labeling: When transforming the logical plan into a physical execution plan, the optimizer performs key optimization operations. It configures the data scanning node according to the data query range control parameters, specifically by pushing down the OFFSET 100000 and LIMIT 10 parameters and attaching them as control attributes to the TableScan operator, making it a range-aware scanning node. Simultaneously, the optimizer configures the data filtering node to execute on the data scanning node. Specifically, the projection node is explicitly set downstream of the range-aware scanning node and marked as deferred execution. This means that the calculation of the projection node is configured to be triggered by each data record output by the data scanning node, and only after the scanning node has acquired the specified number of data records for the query range length (i.e., 10 records). This yields the optimized target operation instruction sequence.

[0125] Step 3: Dynamic control during the execution phase: The query execution engine loads and runs the sequence of instructions for this target operation.

[0126] Specifically, this includes: Range-aware scanning and skipping: The range-aware scanning node starts working. Based on the received OFFSET value, it actively skips the first 100,000 records during the data scanning process. These skipped records are not further processed or passed up. Limited scanning and dynamic termination: Starting from the 100,001st record, the scanning node begins to acquire valid data. It maintains an internal counter to count the acquired records. After reading each line of data, it checks the counter value. When 10 records are successfully acquired, that is, when the value of the internal counter reaches the amount of query data (10) indicated by the data query range control parameter, the data scanning node is immediately controlled to terminate the data scanning operation, perfectly avoiding unnecessary subsequent I / O.

[0127] Delayed projection calculation: Throughout the process, the downstream projection node remains in a waiting state. The projection node will only execute the json_extract(info, '$.name') projection operation once for each valid record output by the range-aware scanning node (maximum 10 records in total).

[0128] Traditional method: 100,010 records need to be scanned first, and the costly `json_extract` function needs to be executed on each of these 100,010 records one by one, ultimately discarding most of the results. This results in extremely high computational resource waste and slow response. However, the optimized implementation in this invention executes the `json_extract` function only 10 times. By pushing OFFSET and LIMIT down to the storage layer for direct execution, and combining this with the latency calculation mechanism of the projection node, the high-cost computation is entirely focused on the final 10 required results.

[0129] This invention achieves a leap in performance through the complete "identification-pushdown-delayed execution" process described above: for high-cost expressions, the performance improvement can reach several orders of magnitude (such as tens of thousands of times in the example above), and it can also achieve a significant reduction in resource consumption: greatly reducing CPU computation cycles and memory usage, avoiding waste on invalid intermediate data, and significantly shortening query response time due to the reduction of unnecessary data processing and I / O. The reduction in resource usage per query enables the system to process more requests concurrently, improving overall efficiency.

[0130] It should be understood that although the steps in the flowcharts of the embodiments described above are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the embodiments described above may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages in other steps. It is understood that the steps in different embodiments can be freely combined as needed, and all non-contradictory solutions formed by such combinations are within the scope of protection of this application.

[0131] Based on the same inventive concept, this application also provides a data query apparatus for implementing the data query method described above. The solution provided by this apparatus is similar to the implementation scheme described in the above method; therefore, the specific limitations in one or more data query apparatus embodiments provided below can be found in the limitations of the data query method described above, and will not be repeated here.

[0132] In one exemplary embodiment, such as Figure 3 As shown, a data query device 300 is provided, comprising:

[0133] Response module 302 is used to respond to a data query request and obtain the query statement;

[0134] The parsing module 304 is used to parse the query statement to obtain an initial operation instruction sequence; the initial operation instruction sequence includes a data scanning node and a data filtering node;

[0135] The comparison module 306 is used to identify the data query range control parameters in the query statement and to perform an initial execution order comparison of the data scanning nodes and data filtering nodes.

[0136] The configuration module 308 is configured to configure the data filtering node according to the data query range control parameter if the initial execution order of the data scanning node is before the data filtering node and the query range control parameter is identified in the query statement, and configure the data filtering node to be executed on the data scanning node, so as to obtain the target operation instruction sequence corresponding to the data query request;

[0137] The execution module 310 is used to execute the target operation instruction sequence to obtain the query result corresponding to the data query request.

[0138] Each module in the aforementioned data query device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device, or stored in the memory of a computer device as software, so that the processor can call and execute the operations corresponding to each module.

[0139] In one exemplary embodiment, a computer device is provided, which may be a terminal, and its internal structure diagram may be as follows: Figure 4 As shown, the computer device includes a processor, memory, input / output interfaces, a communication interface, a display unit, and an input device. The processor, memory, and input / output interfaces are connected via a system bus, and the communication interface, display unit, and input device are also connected to the system bus via the input / output interfaces. The processor provides computing and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The input / output interfaces are used for exchanging information between the processor and external devices. The communication interface is used for wired or wireless communication with external terminals; wireless communication can be achieved through Wi-Fi, mobile cellular networks, Near Field Communication (NFC), or other technologies. When the computer program is executed by the processor, it implements a data query method. The display unit is used to form a visually visible image and can be a display screen, a projection device, or a virtual reality imaging device. The display screen can be an LCD screen or an e-ink screen. The input device of the computer device can be a touch layer covering the display screen, or buttons, trackballs, or touchpads set on the casing of the computer device, or external keyboards, touchpads, or mice, etc.

[0140] Those skilled in the art will understand that Figure 4 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0141] In one exemplary embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps included in any of the foregoing data query method embodiments.

[0142] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon, which, when executed by a processor, implements the steps included in any of the foregoing data query method embodiments.

[0143] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the steps included in any of the foregoing data query method embodiments.

[0144] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of the relevant data must comply with relevant regulations.

[0145] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, artificial intelligence (AI) processors, etc., and are not limited to these.

[0146] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.

[0147] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of this patent application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.

Claims

1. A data query method, characterized in that, The method includes: Retrieve the query statement in response to a data query request; The query statement is parsed to obtain an initial operation instruction sequence; the initial operation instruction sequence includes a data scanning node and a data filtering node; wherein, the data filtering node includes a projection node; the process of parsing the query statement to obtain the initial operation instruction sequence includes: The query statement is parsed to obtain the fields to be returned corresponding to the data query request; Construct a projection expression based on the field to be returned; perform a projection operation on the data to be returned based on the projection expression; Configure the projection node according to the projection expression; The query statement is used to identify data query range control parameters and to perform an initial execution order comparison of the data scanning nodes and data filtering nodes; the data query range control parameters include the query start offset and the query range length. If the initial execution order of the data scanning node is before the data filtering node and the query range control parameter is identified in the query statement, the data filtering node is configured according to the data query range control parameter, and the data filtering node is configured to be executed on the data scanning node to obtain the target operation instruction sequence corresponding to the data query request; wherein, configuring the data filtering node according to the data query range control parameter includes: pushing the data query range control parameter down to the data scanning node, so that the data scanning node skips the data records of the query start offset and obtains the number of data records of the query range length when performing data scanning; configuring the data filtering node to be executed on the data scanning node includes: Configure the data filtering node as the data scanning node to obtain the number of data records within the query range and execute it. Execute the target operation instruction sequence to obtain the query result corresponding to the data query request.

2. The method according to claim 1, characterized in that, The step of configuring the data filtering node as the data scanning node to obtain the number of data records within the query range includes: In the target operation instruction sequence, the data filtering node is set downstream of the data scanning node; The execution of each projection operation of the projection node is configured to be triggered by each data record output by the data scanning node.

3. The method according to claim 1, characterized in that, The method further includes configuring the data filtering node to execute before the data scanning node performs the operation: The projection computation cost of the data filtering node is evaluated based on the complexity of the data type involved in the projection expression, the computational logic complexity of the projection expression, and whether the projection expression is at least one of the user-defined functions. If the projection calculation cost is higher than a preset threshold, the step of configuring the data filtering node to be executed on the data scanning node is performed.

4. The method according to claim 1, characterized in that, The data scanning node includes a full table scan operator or an index scan operator; the method further includes: An internal counter is maintained in the data scanning node, which is used to count the data records that have been scanned by the data scanning node; When the value of the internal counter reaches the amount of query data indicated by the data query range control parameter, the data scanning node is controlled to terminate the data scanning operation.

5. A data query device, characterized in that, The device includes: The response module is used to respond to data query requests and obtain the query statement; The parsing module is used to parse the query statement to obtain an initial operation instruction sequence; the initial operation instruction sequence includes a data scanning node and a data filtering node; wherein, the data filtering node includes a projection node; the parsing of the query statement to obtain the initial operation instruction sequence includes: The query statement is parsed to obtain the fields to be returned corresponding to the data query request; Construct a projection expression based on the field to be returned; perform a projection operation on the data to be returned based on the projection expression; Configure the projection node according to the projection expression; The comparison module is used to identify data query range control parameters in the query statement and to perform an initial execution order comparison of the data scanning nodes and data filtering nodes; the data query range control parameters include the query start offset and the query range length. A configuration module is used to configure the data filtering node according to the data query range control parameter if the initial execution order of the data scanning node is before the data filtering node and the query range control parameter is identified in the query statement, and to configure the data filtering node to be executed on the data scanning node, thereby obtaining the target operation instruction sequence corresponding to the data query request; wherein, configuring the data filtering node according to the data query range control parameter includes: pushing the data query range control parameter down to the data scanning node, so that the data scanning node skips the data records of the query start offset and obtains the number of data records of the query range length when performing data scanning; configuring the data filtering node to be executed on the data scanning node includes: Configure the data filtering node as the data scanning node to obtain the number of data records within the query range and execute it. The execution module is used to execute the target operation instruction sequence to obtain the query result corresponding to the data query request.

6. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 4.

7. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 4.

8. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 4.

Images