Database query processing methods and related products

Abstract

This invention provides a method for processing database query statements and related products, relating to the field of database optimization. The method includes: obtaining the query statement to be processed; searching for the execution state corresponding to the query statement from a pre-established execution state cache record; if found, the database executor directly loads the execution state to process the query statement; if not found, obtaining the execution plan of the query statement, executing the execution plan by the executor, and saving the execution state of the executor to the execution state cache record. The solution of this invention allows the database executor to directly load the execution state to process the query statement, directly reusing the already initialized execution state, avoiding the time-consuming process of repeated initialization by the executor; it shortens the overall processing flow of the query statement, and is particularly suitable for scenarios where the same query is executed repeatedly.

Description

Technical Field

[0001] This invention relates to database optimization, and in particular to a method for processing database query statements and related methods. Background Technology

[0002] The process of a database executing Structured Query Language (SQL) can be broken down into five stages: syntax parsing, semantic analysis, query optimization, execution plan generation, and execution plan execution.

[0003] In scenarios with large datasets and high query complexity, the performance bottleneck typically occurs during the execution phase. Databases generally address this by using an optimizer to generate an efficient execution plan. However, when repeatedly executing the same simple query (such as within a loop in a stored procedure or user-defined function), this approach still encounters performance bottlenecks. This is because while the actual execution time of a single simple query is extremely short, repeated executions still require the database to initialize the optimizer and executor each time. Consequently, the performance bottleneck shifts to the initialization process of the optimizer and executor.

[0004] In existing technologies, the initialization efficiency problem of the optimizer mentioned above is addressed by using execution plan caching, but the efficiency problem of the executor remains unresolved. Summary of the Invention

[0005] One objective of this invention is to improve the efficiency of actuators.

[0006] This invention provides a method for processing database query statements, including: Retrieve the query statement to be processed; Search for the execution status corresponding to the query statement from the pre-established execution status cache record; If found, the database executor directly loads the execution status and processes the query statement; If not found, obtain the execution plan of the query statement, execute the execution plan by the executor, and save the execution status of the executor to the execution status cache record.

[0007] Optionally, the execution state cache record is a state hash table stored in the cache. The key of the state hash table is the hash value of the original syntax tree of the query statement, and the value corresponding to the key is the execution state of the query statement. The steps for retrieving the execution status corresponding to the query statement from the pre-established execution status cache record include: The query statement to be processed is parsed to generate the original syntax tree of the query statement; Calculate the hash value of the original syntax tree; The execution status corresponding to the hash value is found in the status hash table, thus obtaining the execution status corresponding to the query statement.

[0008] Optionally, the steps to obtain the execution plan of the query statement include: Retrieve the execution plan corresponding to the query statement from the pre-established execution plan cache records; If found, extract the execution plan. If not found, generate the execution plan corresponding to the query statement and save the execution plan to the execution plan cache record.

[0009] Optionally, the execution plan cache record is a plan hash table stored in the cache. The key of the plan hash table is the hash value of the original syntax tree of the query statement, and the value corresponding to the key is the execution plan of the query statement. The steps for finding the execution plan corresponding to the query statement from the pre-established execution plan cache record include: finding the execution plan corresponding to the hash value of the original syntax tree of the query statement in the plan hash table.

[0010] Optionally, the steps for generating the execution plan corresponding to the query statement include: Semantic analysis is performed on the original syntax tree of the query statement to obtain an abstract syntax tree; The abstract syntax tree is traversed, and an execution plan is formulated based on the traversal results.

[0011] Optionally, the method for processing the query statement of the above database further includes: obtaining the element change event of the original syntax tree in the state hash table; and deleting the data item of the original syntax tree that has undergone element change from the state hash table.

[0012] Optionally, the execution state is used to record the state after the executor is initialized, and includes: memory allocation, established data source connection, execution context, and bound query parameters.

[0013] According to another aspect of the present invention, a computer program product is also provided, comprising a computer program that, when executed by a processor, implements the steps of the processing method for database query statements of any of the above-described types.

[0014] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a computer program stored in the memory, characterized in that the processor executes the computer program to implement the steps of the processing method for database query statements of any of the above-described embodiments.

[0015] The database query statement processing method of the present invention searches for the execution state corresponding to the query statement from the execution state cache record, and the database executor directly loads the execution state to process the query statement, directly reusing the already initialized execution state, avoiding the time-consuming process of repeated initialization by the executor; it shortens the overall processing flow of the query statement, improves the efficiency of the database in processing repeated queries, and is especially suitable for scenarios where the same query is executed repeatedly.

[0016] Furthermore, the database query statement processing method of the present invention uses a state hash table and a plan hash table to establish cache records, uses the hash value of the original syntax tree as the key value, and generates the original syntax tree through syntax parsing before calculating hash value matching during the search. The hash value matching method has the characteristic of fast retrieval, which can shorten the cache search time; the original syntax tree can filter the format differences of the query statement, which not only ensures the accuracy of execution state and execution plan reuse, but also improves the efficiency of cache matching.

[0017] Furthermore, the database query processing method of the present invention can promptly clear the corresponding execution state and execution plan caches when the elements of the original syntax tree in the state hash table change, thus preventing the executor from loading cache resources that do not match the current query semantics and ensuring the accuracy of query processing. At the same time, timely deletion of invalid caches can prevent redundant caches from occupying system resources and ensure the effective utilization of cache space. This ensures that when query semantics change, the corresponding execution status and execution plan caches are promptly cleared, preventing the executor from loading cache resources that do not match the current query semantics and guaranteeing the accuracy of query processing. At the same time, timely deletion of invalid caches can prevent redundant caches from occupying system resources, ensure the effective utilization of cache space, and ensure that the matching efficiency of subsequent caches is not affected by invalid data, thus maintaining the stability of the caching system and the reliability of query processing.

[0018] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0019] The following sections will describe some specific embodiments of the invention in a detailed manner by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings: Figure 1 This is a schematic diagram of a database query statement processing method according to an embodiment of the present invention; Figure 2This is a flowchart illustrating the steps of searching for the execution status in a database query statement processing method according to an embodiment of the present invention. Figure 3 This is a flowchart illustrating the steps of obtaining the execution plan of a query statement in a database query statement processing method according to an embodiment of the present invention. Figure 4 This is a schematic diagram of the query statement processing flow in conventional technology; Figure 5 This is a schematic diagram of the query statement processing flow in a database query statement processing method according to an embodiment of the present invention; Figure 6 This is a schematic diagram of a computer program product according to an embodiment of the present invention; Figure 7 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; Figure 8 This is a schematic block diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0020] Figure 1 This is a schematic diagram illustrating a method for processing database query statements according to an embodiment of the present invention. The method for processing database query statements generally includes: Step S101: Obtain the query statement to be processed. The query statement is generally an SQL statement. In some embodiments, the query statement to be processed by the processing method of the present invention can be a specific type of SQL statement received by the database. The specific type can be set according to the database cache size and usage scenario. For example, simple query statements (such as those in the loop processing of stored procedures or user-defined functions) can be used as the query statement to be processed.

[0021] Step S102: Search for the execution status corresponding to the query statement from the pre-established execution status cache record.

[0022] The execution state cache record can be a state hash table stored in the cache. The key of the state hash table is the hash value of the original syntax tree of the query statement, and the value corresponding to the key is the execution state of the query statement. The execution state is used to record the state after the executor is initialized, and includes: memory allocation, established data source connection, execution context, and bound query parameters. The execution state cache record uses a key-value pair storage structure of a hash table to achieve efficient lookup.

[0023] The execution state is the state of the executor after initialization, specifically including: memory allocation, established data source connection, execution context, and bound query parameters. Memory allocation refers to the memory space allocated by the executor for this execution, used to store temporary data and result data during the query process; this is generally allocated during executor initialization. The data source connection state refers to the connection channel established between the executor and the target data table or index. The execution context records the rule information for this execution: filtering conditions, sorting methods, etc. Bound query parameters can include dynamic parameters of the query. After loading the execution state, the executor can directly enter the statement processing flow without performing the initialization process again.

[0024] In step S103, if the execution status is found, the database executor directly loads the execution status to process the query statement. This allows for the direct reuse of the already initialized execution status, avoiding the time-consuming process of repeated initialization by the executor.

[0025] Step S104: If no execution status is found, obtain the execution plan of the query statement, execute the execution plan by the executor, and save the execution status of the executor to the execution status cache record.

[0026] The processing method in this embodiment searches for the execution state corresponding to the query statement in the execution state cache record, and the database executor directly loads the execution state to process the query statement, which shortens the overall processing flow of the query statement and improves the efficiency of the database in processing repeated queries, especially suitable for scenarios where the same query is executed repeatedly.

[0027] Figure 2 This is a flowchart illustrating the steps of searching for the execution status in a database query statement processing method according to an embodiment of the present invention. The step of searching for the execution status corresponding to the query statement from a pre-established execution status cache record may include: Step S201 involves parsing the query statement to generate its original syntax tree (CST). The CST strictly corresponds to the literal syntax structure of SQL, preserving all syntactic details. In existing technologies, the CST only functions during the syntax analysis phase, performing syntactic validity checks. The execution state is strongly correlated with the syntactic details of the query statement, and the CST can be obtained simply by parsing the query statement. Without further processing, the execution state is directly obtained, eliminating the need for subsequent semantic analysis, optimization, and executor initialization.

[0028] After processing, the query statement generates a raw syntax tree (AST) and an abstract syntax tree (AST). The AST is obtained by semantic analysis of the raw syntax tree and is a simplification of the raw syntax tree. In related technologies, executor caching generally uses the AST for caching and traversal optimization. However, the method in this embodiment uses the raw syntax tree, which further reduces the processing of the query statement and is one of the improvements of this method.

[0029] Step S202: Calculate the hash value of the original syntax tree.

[0030] Step S203: Search for the execution status corresponding to the hash value in the status hash table to obtain the execution status corresponding to the query statement.

[0031] Hash value matching offers fast retrieval capabilities, reducing cache lookup time; the original syntax tree filters query statement format differences, ensuring that only semantically identical queries match the corresponding execution state.

[0032] Figure 3 This is a flowchart illustrating the steps of obtaining the execution plan of a query statement in a database query statement processing method according to an embodiment of the present invention. When the execution status of the query statement is not found in the status hash table, the above-mentioned step of obtaining the execution plan of the query statement may include: Step S301: Search for the execution plan corresponding to the query statement from the pre-established execution plan cache record.

[0033] The execution plan cache record can be a plan hash table stored in the cache. The key of the plan hash table is the hash value of the original syntax tree of the query statement, and the value corresponding to that key is the execution plan of the query statement. Therefore, the plan hash table and the state hash table can use the same key, further improving query efficiency.

[0034] The process of querying the execution plan cache record can be equivalent to looking up the execution plan in the plan hash table that corresponds to the hash value of the original syntax tree of the query statement.

[0035] In step S302, if an execution plan is found in the execution plan cache, the found execution plan is retrieved. The executor initializes based on the found execution plan and stores the initialized execution state and the hash value of the original syntax tree as a key-value pair in the state hash table.

[0036] Step S303: If no execution plan is found in the execution plan cache, generate the execution plan corresponding to the query statement and save the execution plan to the execution plan cache. Store the generated execution plan and the hash value of the original syntax tree as a key-value pair in the plan hash table.

[0037] In step S304, the executor initializes based on the found or generated execution plan and stores the completed execution state and the hash value of the original syntax tree as a key-value pair in the state hash table.

[0038] The process of generating an execution plan for a query statement may include: performing semantic analysis on the original syntax tree of the query statement to obtain an abstract syntax tree; traversing the abstract syntax tree and formulating an execution plan based on the traversal results. If the cache fails to match, the process of generating an execution plan for the statement is essentially the same as the query processing process in existing solutions.

[0039] The method in this embodiment may further include: obtaining element change events of the original syntax tree in the state hash table; and deleting data items of the original syntax tree that have undergone element changes from the state hash table. That is, the method in this embodiment also provides an invalidation mechanism: if a change in an element of the original syntax tree causes an execution state invalidation, the execution state will be deleted. The execution state will be reinitialized upon the next entry into the executor. The invalidation conditions of the execution plan and the invalidation conditions of the execution state are the same. The above steps can also monitor element change events of the original syntax tree in the cache; if an element change occurs in an original syntax tree, all data items corresponding to that original syntax tree will be deleted simultaneously from both the state hash table and the plan hash table.

[0040] In some embodiments, the database may trigger the above-mentioned failure mechanism in cases of cache clearing, database object information being changed, or insufficient cache space.

[0041] Figure 4 This is a schematic diagram of the query statement processing flow in conventional techniques. The query statement undergoes the following processing steps: Step S401: Syntax parsing, obtaining the original syntax tree; Step S402, syntax analysis, the original syntax tree is updated to an abstract syntax tree; Step S403, optimization: obtain the execution plan with the minimum execution cost by traversing the abstract syntax tree; Step S404: Executor initialization, obtaining the execution status based on the execution plan; Step S405, statement execution: The executor processes the query statement based on the execution status and obtains the execution result.

[0042] Figure 5 This is a schematic diagram of the query statement processing flow in a database query statement processing method according to an embodiment of the present invention. The query statement processing flow includes: Step S501: Syntax parsing, obtaining the original syntax tree; Step S502: Calculate the hash value of the original syntax tree; Step S503: Match the hash value in the state hash table. If a match is found, proceed directly to step S510; otherwise, proceed to step S504. Step S504: Match the hash value in the planned hash table. If a match is found, proceed directly to step S508; otherwise, proceed to step S505. Step S505, syntax analysis, the original syntax tree is updated to an abstract syntax tree; Step S506, optimization: obtain the execution plan with the minimum execution cost by traversing the abstract syntax tree; Step S507: Store the execution plan in the plan hash table; Step S508: Executor initialization, obtaining the execution status based on the execution plan; Step S509: Store the execution status in the status hash table; Step S510, statement execution: The executor processes the query statement based on the execution status and obtains the execution result.

[0043] By comparison Figure 4 and Figure 5 In the process of using the method of this embodiment, after calculating the hash value of the original syntax tree, the state hash table can be used to directly omit a series of steps such as plan generation and executor initialization.

[0044] When the execution state is not found in the cache, the execution plan is further checked for cache, and existing execution plans are used as much as possible. If the execution plan is still not found in the cache, the key-value pair of the original syntax tree hash value and the execution plan is saved to the plan hash table, and the key-value pair of the original syntax tree hash value and the execution state is saved to the state hash table.

[0045] For simple queries that are repeated multiple times, skipping executor initialization by directly using the cached execution state significantly improves query performance based on the execution plan cache. For complex queries that are repeated multiple times, skipping executor initialization can effectively improve query performance. For identical queries with a large number of joins, query performance can be improved by copying the execution state. The ultimate goal of the method in this embodiment is to shorten the execution flow of query statements, reduce execution time, and improve execution efficiency. The direct objective is to skip the executor initialization step and directly obtain the execution state, thereby reducing the executor's runtime.

[0046] This embodiment also provides a computer program product 400, a computer-readable storage medium 500, and a computer device 600. Figure 6 This is a schematic diagram of a computer program product 400 according to an embodiment of the present invention. Figure 7This is a schematic diagram of a computer-readable storage medium 500 according to an embodiment of the present invention. Figure 8 This is a schematic block diagram of a computer device 600 according to an embodiment of the present invention.

[0047] Computer program product 400 includes computer program 410, which, when executed by processor 610, implements the steps of the processing method for database query statements described above. Computer-readable storage medium 500 stores the aforementioned computer program 410, which, when executed by processor 610, implements the steps of the processing method for database query statements described above. Computer device 600 may include memory 620, processor 610, and computer program 410 stored in memory 620 and running on processor 610.

[0048] The computer program 410 used to perform the operations of the present invention may be assembly instructions, instruction set architecture (ISA) instructions, machine instructions, machine-related instructions, microcode, firmware instructions, status setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages ​​and procedural programming languages.

[0049] Computer program 410 may execute entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network, including a Local Area Network (LAN) or a Wide Area Network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, to perform aspects of the invention, electronic circuits including, for example, programmable logic circuits, field-programmable gate arrays (FPGAs), or programmable logic arrays (PLAs) may execute computer-readable program instructions to personalize the electronic circuits by utilizing state information of computer-readable program instructions.

[0050] For the purposes of this embodiment, computer program product 400 is a related product that includes computer program 410.

[0051] For the purposes of this embodiment, a computer-readable storage medium 500 is a tangible device capable of holding and storing a computer program 410. It can be any device capable of containing, storing, communicating, propagating, or transmitting the computer program 410 for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of a computer-readable storage medium 500 include: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable optical disc read-only memory (CD-ROM), digital versatile disc (DVD), memory stick, floppy disk, mechanical encoding device, and any suitable combination thereof.

[0052] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A method for processing database query statements, comprising: Retrieve the query statement to be processed; Search for the execution status corresponding to the query statement from the pre-established execution status cache record; If found, the database executor directly loads the execution status to process the query statement; If not found, the execution plan of the query statement is obtained, and the executor executes the execution plan, and the execution status of the executor is saved to the execution status cache record.

2. The database query statement processing method according to claim 1, wherein, The execution state cache record is a state hash table stored in the cache. The key of the state hash table is the hash value of the original syntax tree of the query statement, and the value corresponding to the key is the execution state of the query statement. The step of searching for the execution status corresponding to the query statement from the pre-established execution status cache record includes: The query statement to be processed is parsed to generate the original syntax tree of the query statement; Calculate the hash value of the original syntax tree; The execution status corresponding to the hash value is found in the status hash table, thereby obtaining the execution status corresponding to the query statement.

3. The database query statement processing method according to claim 2, wherein, The step of obtaining the execution plan of the query statement includes: Search for the execution plan corresponding to the query statement from the pre-established execution plan cache record; If found, extract the execution plan. If not found, generate the execution plan corresponding to the query statement and save the execution plan to the execution plan cache record.

4. The database query statement processing method according to claim 3, wherein, The execution plan cache record is a plan hash table stored in the cache. The key of the plan hash table is the hash value of the original syntax tree of the query statement, and the value corresponding to the key is the execution plan corresponding to the query statement. The step of searching for the execution plan corresponding to the query statement from the pre-established execution plan cache record includes: searching for the execution plan corresponding to the hash value of the original syntax tree of the query statement in the plan hash table.

5. The method for processing database query statements according to claim 3 or 4, wherein, The steps for generating the execution plan corresponding to the query statement include: Semantic analysis is performed on the original syntax tree of the query statement to obtain an abstract syntax tree; The abstract syntax tree is traversed, and the execution plan is formulated based on the traversal results.

6. The database query statement processing method according to claim 2 further includes: Obtain element change events of the original syntax tree in the state hash table; Remove the data item from the original syntax tree that has undergone element changes from the state hash table.

7. The method for processing database query statements according to any one of claims 1 to 6, wherein, The execution state is used to record the state of the executor after initialization, and includes: memory allocation, established data source connection, execution context, and bound query parameters.

8. A computer-readable storage medium having a machine-executable program stored thereon, the machine-executable program, when executed by a processor, implementing a method for processing database query statements according to any one of claims 1 to 7.

9. A computer program product, comprising a computer program, characterized in that, When executed by a processor, the computer program implements the steps of the method for processing database query statements according to any one of claims 1 to 7.

10. A computer device, comprising a memory, a processor, and a computer program stored in the memory, characterized in that, The processor executes the computer program to implement the steps of the method for processing database query statements according to any one of claims 1 to 7.

Images