A SQL statement operation method, device and equipment and storage medium

Abstract

The application discloses an operation method of an SQL statement, which comprises the following steps: generating a syntax tree according to an SQL statement received by a database, and creating an initial execution plan of the SQL statement according to the syntax tree; the statement elements in the SQL statement at least comprise a TopN element and a neighboring element located before the TopN element; if the neighboring element cannot be pushed down, updating the initial execution order between the TopN element and the neighboring element in the initial execution plan, updating the initial execution logic between the TopN element and the neighboring element, and determining a target execution plan; according to the target execution plan, performing operation on the TopN element to obtain an operation result of the TopN element, and determining whether to execute the neighboring element according to the operation result of the TopN element; if yes, executing the neighboring element, and after the target execution plan is executed, calculating and integrating the execution result of the target execution plan to determine the operation result of the SQL statement. The operation efficiency of the SQL statement can be ensured while solving the problem that the TopN element cannot be pushed down.

Description

Technical Field

[0001] The present invention relates to the field of computers, and more particularly to a method, apparatus, device and storage medium for calculating SQL statements. Background Technology

[0002] With the rapid development of the information society, traditional centralized databases are increasingly unable to meet the demands of massive data transactions. Distributed databases, with their high scalability, high availability, and strong consistency, can address the explosive growth in data storage capacity and high latency issues associated with massive data processing. However, when faced with highly complex SQL statements, the server's computational load becomes extremely high, and the SQL statement operations also suffer from high latency. When the actions preceding the Top N elements in an SQL statement cannot be pushed down, the Top N elements also cannot be pushed down. This necessitates retrieving all data from the database into memory before performing the Top N operation, which consumes significant I / O, computational, and memory resources, while also drastically reducing processing speed. Therefore, resolving the inability to push down Top N elements is an urgent issue. Thus, improving the efficiency of SQL statement operations and reducing resource consumption during SQL statement processing are problems that need to be addressed. Summary of the Invention

[0003] This invention provides a method, apparatus, device, and storage medium for calculating SQL statements, which can solve the problem of TopN elements not being able to be pushed down, while saving IO resources, computing resources, and memory resources, and ensuring the efficiency of SQL statement calculation.

[0004] According to one aspect of the present invention, a method for calculating SQL statements is provided, comprising:

[0005] A syntax tree is generated based on the SQL statement received from the database, and an initial execution plan for the SQL statement is created based on the syntax tree; the statement elements in the SQL statement include at least the Top N elements and the adjacent elements preceding the Top N elements;

[0006] If the adjacent elements cannot be pushed down, the initial execution order between the Top N elements and the adjacent elements in the initial execution plan is updated, and the initial execution logic between the Top N elements and the adjacent elements is updated to determine the target execution plan.

[0007] According to the target execution plan, the TopN elements are processed to obtain the calculation results of the TopN elements, and it is determined whether to execute the adjacent elements based on the calculation results of the TopN elements.

[0008] If so, then return to execute the adjacent elements, and after the execution of the target execution plan is completed, calculate and integrate the execution results of the target execution plan to determine the calculation result of the SQL statement.

[0009] According to another aspect of the present invention, a device for calculating SQL statements is provided, the device comprising:

[0010] The initial execution plan determination module is used to generate a syntax tree based on the SQL statement received from the database, and to create an initial execution plan for the SQL statement based on the syntax tree; the statement elements in the SQL statement include at least the Top N elements and the adjacent elements located before the Top N elements;

[0011] The target execution plan determination module is used to update the initial execution order between the TopN elements and the adjacent elements in the initial execution plan and update the initial execution logic between the TopN elements and the adjacent elements if the adjacent elements cannot be pushed down, thereby determining the target execution plan.

[0012] The target execution plan execution module is used to perform calculations on the TopN elements according to the target execution plan to obtain the calculation results of the TopN elements, and to determine whether to execute the adjacent elements based on the calculation results of the TopN elements;

[0013] The calculation result determination module is used to return to the execution of the adjacent elements if the result is yes, and to calculate and integrate the execution results of the target execution plan after the target execution plan is completed to determine the calculation result of the SQL statement.

[0014] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising:

[0015] At least one processor; and

[0016] A memory communicatively connected to the at least one processor; wherein,

[0017] The memory stores a computer program that can be executed by the at least one processor, which is executed by the at least one processor to enable the at least one processor to perform the SQL statement operation method described in any embodiment of the present invention.

[0018] According to another aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions for causing a processor to execute and implement the SQL statement operation method described in any embodiment of the present invention.

[0019] The technical solution of this invention generates a syntax tree based on the SQL statement received from the database, and creates an initial execution plan for the SQL statement based on the syntax tree. If adjacent elements cannot be pushed down, the initial execution order between the Top N elements and adjacent elements in the initial execution plan is updated, and the initial execution logic between the Top N elements and adjacent elements is updated to determine the target execution plan. Based on the target execution plan, the Top N elements are processed to obtain the results of the Top N elements, and it is determined whether to execute adjacent elements based on the results of the Top N elements. If so, the execution of adjacent elements is returned, and after the execution of the target execution plan is completed, the execution results of the target execution plan are calculated and integrated to determine the result of the SQL statement. This solves the problem that when the execution actions before the Top N elements cannot be pushed down, making it impossible for the Top N elements to be pushed down as well, in order to perform the operations corresponding to the Top N elements, it is necessary to directly extract all the data from the database into the computer memory to perform the operations corresponding to the Top N elements, resulting in a large consumption of IO resources, computing resources, and memory resources, and reducing the computational efficiency of the SQL statement. The above solution, after creating the initial execution plan for the SQL statement, updates the initial execution order and logic of the initial execution plan when it is determined that adjacent elements preceding the Top N elements cannot be pushed down, thus determining the target execution plan. Based on the target execution plan, the Top N elements are prioritized for computation, and the execution results of the Top N elements determine whether to execute adjacent elements; the execution result of the SQL statement is determined based on the execution result of the target execution plan. This achieves the goal of adjusting the initial execution plan of the SQL statement when the preceding adjacent element of the Top N elements cannot be pushed down, without needing to extract all data from the database into memory, thus executing the operations corresponding to the Top N elements. This solves the problem of Top N elements not being able to be pushed down, while saving IO, computational, and memory resources, ensuring the computational efficiency of the SQL statement.

[0020] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

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

[0022] Figure 1 A flowchart illustrating a method for calculating an SQL statement according to Embodiment 1 of the present invention;

[0023] Figure 2 A flowchart illustrating a method for calculating an SQL statement according to Embodiment 2 of the present invention;

[0024] Figure 3 This is a schematic diagram of the structure of an SQL statement processing device provided in Embodiment 3 of the present invention;

[0025] Figure 4 This is a schematic diagram of the structure of an electronic device provided in Embodiment 4 of the present invention. Detailed Implementation

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

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

[0028] Example 1

[0029] Figure 1 This is a flowchart illustrating a method for calculating an SQL statement, as provided in Embodiment 1 of the present invention. This embodiment is applicable to situations where SQL statements are calculated and their results are obtained. It is particularly suitable for situations where the Top N elements in an SQL statement cannot be pushed down, requiring updating the initial execution plan of the SQL statement, determining a target execution plan, and continuing to calculate the SQL statement according to the target execution plan to obtain the result. This method can be executed by an SQL statement calculation device, which can be implemented in hardware and / or software and can be configured in an electronic device. Figure 1 As shown, the method includes:

[0030] S110. Generate a syntax tree based on the SQL statement received from the database, and create an initial execution plan for the SQL statement based on the syntax tree.

[0031] The statement elements in an SQL statement must include at least the Top N elements and the adjacent elements preceding the Top N elements.

[0032] The database is a distributed database, such as an MPP database. Distributed databases store data in different databases (DBs, data storage nodes) using different distribution rules. When a distributed database receives an SQL request from a client, it generates a syntax tree, uses the syntax tree to generate an execution plan, and then distributes the execution plan to the DBs for execution. TopN elements refer to the Top N statements in the SQL statement received by the database. Adjacent elements preceding the TopN elements are the adjacent elements of the TopN statements. TopN statements are used to filter the top N extreme values ​​of a certain indicator in real-time data. A syntax tree is a graphical representation of sentence structure; it represents the derivation result of the sentence and is helpful for understanding the hierarchical structure of the sentence's syntax.

[0033] Specifically, when a database system receives an SQL statement, its parser first parses it into an abstract syntax tree. The Coordinator can then parse the received SQL statement and generate its corresponding syntax tree based on the parsing results. This syntax tree is then sent to the database system's overhead-based updater, which creates an initial execution plan for the SQL statement based on the syntax tree. The Coordinator is responsible for parsing the SQL statement, formulating the execution plan, and managing the worker nodes.

[0034] For example, the SQL statements received from the database can be standardized; the standardized SQL statements can be parsed, and a syntax tree corresponding to the SQL statements can be generated based on the parsing results.

[0035] It should be noted that since the SQL statements sent by different data senders may have different statement formats, the SQL statements received by the database need to be standardized and parsed before the SQL statements are processed. A syntax tree can be generated based on the parsing results, which can improve the flexibility of the database in processing SQL statements.

[0036] S120. If adjacent elements cannot be pushed down, update the initial execution order between TopN elements and adjacent elements in the initial execution plan, and update the initial execution logic between TopN elements and adjacent elements to determine the target execution plan.

[0037] In this context, "adjacent elements cannot be pushed down" means that adjacent elements cannot be directly sent to the database for execution. If adjacent elements cannot be pushed down, then the Top N elements also cannot be pushed down. For example, if there is an OR operation before the Top N elements, it will cause the Top N elements to fail to be pushed down.

[0038] Specifically, if the adjacent elements of the TopN elements cannot be pushed down, the initial execution order between the TopN elements and their adjacent elements in the initial execution plan is updated, and the initial execution logic between the TopN elements and their adjacent elements is updated to determine the target execution plan. This allows the TopN elements to be sorted first when executing the TopN elements and their adjacent elements based on the target execution plan. Then, a counting operation is performed based on the result of the TopN elements' sorting operation, and the result of the counting operation determines whether to return to executing the adjacent elements of the TopN elements.

[0039] For example, the target execution plan can be determined through the following sub-steps:

[0040] S1201. If adjacent elements cannot be pushed down, then the TopN elements cannot be pushed down, and the execution order between the TopN elements and adjacent elements in the initial execution plan is swapped to obtain the target execution order after the swap.

[0041] Specifically, if the neighboring elements of the Top N elements cannot be pushed down, then the Top N elements are determined to be unable to be pushed down. In this case, the method to update the initial execution order between the Top N elements and their neighboring elements in the initial execution plan is to swap the execution order of the Top N elements and their neighboring elements in the initial execution plan to obtain the swapped target execution order. The swapped target execution order means that the Top N elements are executed first, followed by the neighboring elements.

[0042] S1202. Adjust the execution logic between the TopN elements and adjacent elements in the initial execution plan to obtain the adjusted target execution logic, so that the execution result of the TopN elements determines whether to execute adjacent elements.

[0043] Specifically, the execution logic between the Top N elements and adjacent elements in the initial execution plan is adjusted to obtain the adjusted target execution logic, so that when executing the Top N elements and adjacent elements based on the target execution logic, it is determined whether to return to execute the previous element based on the execution result of the Top N elements.

[0044] S1203. Determine the target execution plan based on the target execution sequence and target execution logic.

[0045] Specifically, the target execution order is used as the execution order of the target execution plan, and the target execution logic is used as the execution logic of the target execution plan to determine the target execution plan.

[0046] Understandably, when adjacent elements cannot be pushed down, the initial execution logic and initial execution order in the initial execution plan should be adjusted in a timely manner to determine the target execution order and target execution logic. Based on the target execution order and target execution logic, the target execution plan should be determined. It is possible to determine whether the Top N elements can be pushed down in a timely manner based on the push-down status of adjacent elements. If not, the initial execution plan should be adjusted in a timely manner to ensure the smooth execution of SQL statements.

[0047] S130. Based on the target execution plan, perform calculations on the Top N elements to obtain the calculation results of the Top N elements, and determine whether to execute adjacent elements based on the calculation results of the Top N elements.

[0048] Specifically, the Coordinator schedules parallel worker threads to execute the target execution plan. Based on the target execution plan, it prioritizes sending the ORDER BY operation for the Top N elements to the database and obtains the sorting results from the database. Based on the sorting results, it determines the count of the Top N elements, uses this count as the result of the Top N element calculation, and determines whether to execute adjacent elements based on the calculation results of the Top N elements and the execution logic between the Top N elements and adjacent elements in the target execution plan. If the calculation results of the Top N elements meet the conditions for returning to execute adjacent elements as specified in the execution logic between the Top N elements and adjacent elements, then it is determined that execution of adjacent elements can be returned; otherwise, it is determined that execution of adjacent elements cannot be returned. The default sorting order for ORDER BY is ascending. If the ORDER BY clause does not explicitly specify ascending or descending order, then the database defaults to ascending sorting for ORDER BY.

[0049] S140. If yes, then return to execute adjacent elements, and after the target execution plan is completed, calculate and integrate the execution results of the target execution plan to determine the calculation result of the SQL statement.

[0050] Specifically, if the calculation results of the TopN elements determine that adjacent elements can be executed, then the execution of adjacent elements is returned. After all execution tasks in the target execution plan have been completed, the Coordinator schedules the Worker to extract the execution results of the target execution plan from the DB, and calculates and integrates the execution results according to the preset data processing rules to determine the calculation result of the SQL statement.

[0051] For example, the method to determine the operation result of the SQL statement can be as follows: if it is determined that adjacent elements can be executed based on the operation result of the Top N elements, then the execution of adjacent elements is returned, and after the execution of the target execution plan is completed, the execution result of the target execution plan is obtained from the database according to the parallel thread; the execution result is divided into regular result and result to be calculated, and the result to be calculated is processed, and the regular result and the result to be calculated after processing are integrated to determine the operation result of the SQL statement.

[0052] Here, "normal result" refers to the execution result that requires no further calculation. "Result to be calculated" refers to the execution result that requires further calculation.

[0053] It is understandable that after obtaining the execution result of the target execution plan, the calculation and processing of the results to be calculated in the execution result, and then the calculation result of the SQL statement is determined based on the calculated results and the regular results in the execution result, which can improve the accuracy of the obtained calculation results.

[0054] The technical solution provided in this embodiment generates a syntax tree based on the SQL statement received from the database, and creates an initial execution plan for the SQL statement based on the syntax tree. If adjacent elements cannot be pushed down, the initial execution order between the Top N elements and adjacent elements in the initial execution plan is updated, and the initial execution logic between the Top N elements and adjacent elements is updated to determine the target execution plan. Based on the target execution plan, the Top N elements are processed to obtain the results of the Top N elements, and it is determined whether to execute adjacent elements based on the results of the Top N elements. If so, the execution of adjacent elements is returned, and after the execution of the target execution plan is completed, the execution results of the target execution plan are calculated and integrated to determine the result of the SQL statement. This solves the problem that when the execution actions before the Top N elements cannot be pushed down, making it impossible for the Top N elements to be pushed down as well, in order to perform the operations corresponding to the Top N elements, it is necessary to directly extract all the data from the database into the computer memory to perform the operations corresponding to the Top N elements, resulting in a large consumption of IO resources, computing resources, and memory resources, and reducing the efficiency of SQL statement operations. The above solution, after creating the initial execution plan for the SQL statement, updates the initial execution order and logic of the initial execution plan when it is determined that adjacent elements preceding the Top N elements cannot be pushed down, thus determining the target execution plan. Based on the target execution plan, the Top N elements are prioritized for computation, and the execution results of the Top N elements determine whether to execute adjacent elements; the execution result of the SQL statement is determined based on the execution result of the target execution plan. This achieves the goal of adjusting the initial execution plan of the SQL statement when the preceding adjacent element of the Top N elements cannot be pushed down, without needing to extract all data from the database into memory, thus executing the operations corresponding to the Top N elements. This solves the problem of Top N elements not being able to be pushed down, while saving IO, computational, and memory resources, ensuring the computational efficiency of the SQL statement.

[0055] Example 2

[0056] Figure 2 This is a flowchart of an SQL statement operation method provided in Embodiment 2 of the present invention. This embodiment optimizes the above embodiment and provides a preferred implementation method that calculates the Top N elements according to the target execution plan to obtain the calculation results of the Top N elements, and determines whether to execute adjacent elements based on the calculation results of the Top N elements. Specifically, as shown... Figure 2 As shown, the method includes:

[0057] S210. Generate a syntax tree based on the SQL statement received from the database, and create an initial execution plan for the SQL statement based on the syntax tree.

[0058] The statement elements in an SQL statement must include at least the Top N elements and the adjacent elements preceding the Top N elements.

[0059] S220. If adjacent elements cannot be pushed down, update the initial execution order between TopN elements and adjacent elements in the initial execution plan, and update the initial execution logic between TopN elements and adjacent elements to determine the target execution plan.

[0060] S230. Based on the target execution order in the target execution plan, prioritize sending the Top N elements to the database, and sort the data in the database based on the Top N elements to determine the sorting result stream.

[0061] Specifically, based on the target execution order in the target execution plan, the ORDER BY sorting operation of the TopN elements is first sent to the DB in the distributed database, and the data in the DB is sorted based on the sorting operation. The sorting result stream is determined according to the sorting result.

[0062] For example, a method to determine the sorting result stream could be: sending the sorting operation of the Top N features to the database; calling the target updater corresponding to the adjacent features to sort the data in the database according to the sorting operation, and determining the sorting result stream returned by the database.

[0063] Specifically, the ORDER BY sorting operation of the TopN elements is sent to the DB in the distributed database. Based on the correspondence between candidate elements and candidate updaters, the target updater corresponding to the adjacent elements is called. The target updater sorts the data in the database according to the sorting operation. The sorting result stream fed back by the database is determined based on the sorting result.

[0064] It is understandable that by calling the target updater corresponding to adjacent features, and by sorting the data in the database through the target updater and sorting operations, the accuracy of the obtained sorting result stream can be improved by determining the sorting result stream returned by the database.

[0065] S240. Perform counting processing on the sorting result stream, determine the counting results of the Top N elements, and determine whether to return to execute adjacent elements based on the counting results and the target execution logic of the target execution plan.

[0066] Specifically, the sorted result stream is filtered by a WHERE condition, and then a LIMIT counting operation is performed on the filtered sorted result stream. The counting result obtained from the counting operation is used as the counting result of the Top N elements. It is determined whether the counting result of the Top N elements meets the counting condition specified by the target execution logic of the target execution plan. If it does, it is determined that the execution of adjacent elements can be returned; if it does not, it is determined that the execution of adjacent elements cannot be returned.

[0067] For example, the decision to return to executing adjacent features can be determined through the following sub-steps:

[0068] S2401. Call the target updater corresponding to the adjacent elements, filter the sorted result stream according to the query filtering conditions corresponding to the adjacent elements, and obtain the filtered result stream fed back by the target updater.

[0069] The WHERE clause allows filtering of the sorted result stream based on query conditions corresponding to adjacent elements. The WHERE block can specify query filtering conditions within a single-table query. The correspondence between query filtering conditions and adjacent elements can be pre-defined according to actual needs.

[0070] Specifically, the target updater corresponding to the adjacent elements is invoked, and the sorted result stream is filtered by WHERE conditions based on the query filtering conditions corresponding to the adjacent elements. The filtered result stream fed back by the target updater is determined based on the condition filtering results.

[0071] S2402. Perform counting processing on the filtered result stream to determine the counting results of the Top N elements.

[0072] Specifically, a LIMIT operation is performed on the filtered result stream to count the filtered result stream, and the technical result of the filtered result stream is used as the count result of the Top N elements.

[0073] S2403. Determine whether the counting result meets the counting constraints specified by the target execution logic of the target execution plan. If so, return the adjacent elements of the TopN elements.

[0074] Specifically, when the counting result meets the counting limit conditions specified by the target execution logic of the target execution plan, the early stop function is activated to end the operation on the Top N elements and return to the adjacent elements of the Top N elements.

[0075] It is understandable that filtering the sorted result stream to determine the filtered result stream, and then performing counting processing on the filtered result stream to determine the count results of the Top N elements, can improve the accuracy of the obtained Top N element count results.

[0076] S250. If yes, then return to execute adjacent elements, and after the target execution plan is completed, calculate and integrate the execution results of the target execution plan to determine the calculation result of the SQL statement.

[0077] The technical solution of this embodiment generates a syntax tree based on the SQL statement received from the database, and creates an initial execution plan for the SQL statement based on the syntax tree. If adjacent elements cannot be pushed down, the initial execution order between the Top N elements and adjacent elements in the initial execution plan is updated, and the initial execution logic between the Top N elements and adjacent elements is updated to determine the target execution plan. Based on the target execution order in the target execution plan, the Top N elements are preferentially sent to the database, and the Top N elements are sorted to determine the sorting result stream. The sorting result stream is counted to determine the count result of the Top N elements, and based on the count result and the target execution logic of the target execution plan, it is determined whether to return to execute adjacent elements. If so, the execution of adjacent elements is returned, and after the target execution plan is completed, the execution result of the target execution plan is calculated and integrated to determine the calculation result of the SQL statement. The above solution determines the sorting result stream of the Top N elements based on the target execution plan, and determines the counting result of the Top N elements based on the sorting result stream. Based on the calculation result, it determines whether to return to execute adjacent elements. This can determine the time node for returning to execute adjacent elements in a timely and accurate manner. While solving the problem that the Top N elements cannot be pushed down, it also improves the computational efficiency of SQL statements and the accuracy of computational results.

[0078] Example 3

[0079] Figure 3 This is a schematic diagram of a device for processing SQL statements according to Embodiment 3 of the present invention. This embodiment is applicable to situations where SQL statements are processed and the results of the SQL statement processing are obtained. Figure 3 As shown, the calculation device for the SQL statement includes: an initial execution plan determination module 310, a target execution plan determination module 320, a target execution plan execution module 330, and a calculation result determination module 340.

[0080] The initial execution plan determination module 310 is used to generate a syntax tree based on the SQL statement received from the database, and to create an initial execution plan for the SQL statement based on the syntax tree; the statement elements in the SQL statement include at least the Top N elements and the adjacent elements located before the Top N elements;

[0081] The target execution plan determination module 320 is used to update the initial execution order between the TopN elements and adjacent elements in the initial execution plan and update the initial execution logic between the TopN elements and adjacent elements if adjacent elements cannot be pushed down, thereby determining the target execution plan.

[0082] The target execution plan execution module 330 is used to perform calculations on the TopN elements according to the target execution plan to obtain the calculation results of the TopN elements, and to determine whether to execute adjacent elements based on the calculation results of the TopN elements.

[0083] The calculation result determination module 340 is used to return the execution of adjacent elements if the result is yes, and to calculate and integrate the execution results of the target execution plan after the target execution plan is completed, so as to determine the calculation result of the SQL statement.

[0084] The technical solution provided in this embodiment generates a syntax tree based on the SQL statement received from the database, and creates an initial execution plan for the SQL statement based on the syntax tree. If adjacent elements cannot be pushed down, the initial execution order between the Top N elements and adjacent elements in the initial execution plan is updated, and the initial execution logic between the Top N elements and adjacent elements is updated to determine the target execution plan. Based on the target execution plan, the Top N elements are processed to obtain the results of the Top N elements, and it is determined whether to execute adjacent elements based on the results of the Top N elements. If so, the execution of adjacent elements is returned, and after the execution of the target execution plan is completed, the execution results of the target execution plan are calculated and integrated to determine the result of the SQL statement. This solves the problem that when the execution actions before the Top N elements cannot be pushed down, making it impossible for the Top N elements to be pushed down as well, in order to perform the operations corresponding to the Top N elements, it is necessary to directly extract all the data from the database into the computer memory to perform the operations corresponding to the Top N elements, resulting in a large consumption of IO resources, computing resources, and memory resources, and reducing the efficiency of SQL statement operations. The above solution, after creating the initial execution plan for the SQL statement, updates the initial execution order and logic of the initial execution plan when it is determined that adjacent elements preceding the Top N elements cannot be pushed down, thus determining the target execution plan. Based on the target execution plan, the Top N elements are prioritized for computation, and the execution results of the Top N elements determine whether to execute adjacent elements; the execution result of the SQL statement is determined based on the execution result of the target execution plan. This achieves the goal of adjusting the initial execution plan of the SQL statement when the preceding adjacent element of the Top N elements cannot be pushed down, without needing to extract all data from the database into memory, thus executing the operations corresponding to the Top N elements. This solves the problem of Top N elements not being able to be pushed down, while saving IO, computational, and memory resources, ensuring the computational efficiency of the SQL statement.

[0085] For example, the target execution plan determination module 320 is specifically used for:

[0086] If adjacent elements cannot be pushed down, then the Top N elements cannot be pushed down, and the execution order between the Top N elements and adjacent elements in the initial execution plan is swapped to obtain the target execution order after the swap.

[0087] The execution logic between the Top N elements and adjacent elements in the initial execution plan is adjusted to obtain the adjusted target execution logic, which determines whether to execute adjacent elements based on the execution results of the Top N elements.

[0088] Determine the target execution plan based on the target execution order and target execution logic.

[0089] For example, the target execution plan execution module includes:

[0090] The sorting result stream determination unit is used to prioritize sending the Top N elements to the database based on the target execution order in the target execution plan, and to sort the data in the database based on the Top N elements to determine the sorting result stream;

[0091] The counting result determination unit is used to perform counting processing on the sorting result stream, determine the counting results of the Top N elements, and determine whether to return to execute adjacent elements based on the counting results and the target execution logic of the target execution plan.

[0092] For example, the sorting result stream determination unit is specifically used for:

[0093] Send the sorting operation of the Top N elements to the database;

[0094] Invoke the target updater corresponding to the adjacent features, sort the data in the database according to the sorting operation, and determine the sorting result stream returned by the database.

[0095] For example, the counting result determination unit is specifically used for:

[0096] Call the target updater corresponding to the adjacent elements, filter the data in the sorted result stream according to the query filtering conditions corresponding to the adjacent elements, and obtain the filtered result stream fed back by the target updater;

[0097] The filtered result stream is counted to determine the count results of the Top N elements;

[0098] Determine whether the counting result meets the counting constraints specified by the target execution logic of the target execution plan. If so, return the neighboring elements of the Top N elements.

[0099] For example, the calculation result determination module 340 is specifically used for:

[0100] Return to the adjacent elements for execution, and after the target execution plan has been executed, retrieve the execution results of the target execution plan from the database according to the parallel threads;

[0101] The execution results are divided into regular results and results to be calculated. The results to be calculated are then processed, and the regular results and the processed results to be calculated are integrated to determine the result of the SQL statement.

[0102] For example, the initial execution plan determination module 310 is specifically used for:

[0103] Standardize the SQL statements received from the database;

[0104] The standardized SQL statement is parsed, and a syntax tree corresponding to the SQL statement is generated based on the parsing results.

[0105] The SQL statement processing device provided in this embodiment can be applied to the SQL statement processing method provided in any of the above embodiments, and has corresponding functions and beneficial effects.

[0106] Example 4

[0107] Figure 4 A schematic diagram of an electronic device 10 that can be used to implement embodiments of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (e.g., helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

[0108] like Figure 4 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer program stored in the ROM 12 or loaded from storage unit 18 into the RAM 13. The RAM 13 may also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0109] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0110] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as the operation of SQL statements.

[0111] In some embodiments, the SQL statement operation method can be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program can be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the SQL statement operation method described above can be performed. Alternatively, in other embodiments, processor 11 can be configured to execute the SQL statement operation method by any other suitable means (e.g., by means of firmware).

[0112] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0113] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0114] In the context of this invention, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0115] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0116] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or computing systems that include middleware components (e.g., application servers), or computing systems that include frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0117] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.

[0118] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0119] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A method for calculating SQL statements, characterized in that, include: Generate a syntax tree based on the SQL statement received from the database, and create an initial execution plan for the SQL statement based on the syntax tree; The statement elements in the SQL statement include at least the Top N elements and the adjacent elements preceding the Top N elements; wherein, the Top N elements refer to the Top N statements in the SQL statement received by the database; the Top N statements are used to filter the top N extreme values ​​of a certain indicator in real-time data; the adjacent elements preceding the Top N elements refer to the adjacent elements of the Top N statements; If the adjacent elements cannot be pushed down, the initial execution order between the Top N elements and the adjacent elements in the initial execution plan is updated, and the initial execution logic between the Top N elements and the adjacent elements is updated to determine the target execution plan. According to the target execution plan, the TopN elements are processed to obtain the calculation results of the TopN elements, and it is determined whether to execute the adjacent elements based on the calculation results of the TopN elements. If so, then return to execute the adjacent elements, and after the execution of the target execution plan is completed, calculate and integrate the execution results of the target execution plan to determine the operation result of the SQL statement; The process includes performing calculations on the TopN elements according to the target execution plan to obtain the calculation results for the TopN elements, and determining whether to execute the adjacent elements based on the calculation results of the TopN elements, including: Based on the target execution order in the target execution plan, the TopN elements are preferentially sent to the database, and the data in the database is sorted based on the TopN elements to determine the sorting result stream; The sorting result stream is counted to determine the count result of the Top N elements, and based on the count result and the target execution logic of the target execution plan, it is determined whether to return to execute the adjacent elements.

2. The method according to claim 1, characterized in that, If the adjacent elements cannot be pushed down, the initial execution order between the Top N elements and the adjacent elements in the initial execution plan is updated, and the initial execution logic between the Top N elements and the adjacent elements is updated to determine the target execution plan, including: If the adjacent elements cannot be pushed down, then the TopN elements cannot be pushed down, and the execution order between the TopN elements and the adjacent elements in the initial execution plan is swapped to obtain the target execution order after the swap. The execution logic between the TopN elements and the adjacent elements in the initial execution plan is adjusted to obtain the adjusted target execution logic, so that the execution result of the TopN elements determines whether to execute the adjacent elements. Based on the target execution order and the target execution logic, determine the target execution plan.

3. The method according to claim 1, characterized in that, Based on the target execution order in the target execution plan, the Top N elements are preferentially distributed to the database, and the data in the database is sorted based on the Top N elements to determine the sorting result stream, including: The sorting operation of the TopN elements is sent to the database; The target updater corresponding to the adjacent feature is invoked, and the data in the database is sorted according to the sorting operation to determine the sorting result stream returned by the database.

4. The method according to claim 1, characterized in that, The sorting result stream is counted to determine the count results of the Top N elements. Based on the count results and the target execution logic of the target execution plan, it is determined whether to return to execute the adjacent elements, including: Invoke the target updater corresponding to the adjacent elements, filter the sorted result stream according to the query filtering conditions corresponding to the adjacent elements, and obtain the filtered result stream fed back by the target updater; The filtered result stream is counted to determine the count results of the Top N elements; Determine whether the counting result meets the counting constraints specified by the target execution logic of the target execution plan. If so, return to execute the neighboring elements of the TopN elements.

5. The method according to claim 1, characterized in that, Returning to the execution of the adjacent elements, and after the execution of the target execution plan is completed, calculating and integrating the execution results of the target execution plan to determine the operation result of the SQL statement, including: Return to the execution of the adjacent elements, and after the execution of the target execution plan is completed, retrieve the execution result of the target execution plan from the database according to the parallel thread; The execution results are divided into regular results and results to be calculated. The results to be calculated are then processed, and the regular results and the processed results to be calculated are integrated to determine the operation result of the SQL statement.

6. The method according to claim 1, generating a syntax tree based on the SQL statement received from the database, comprising: Standardize the SQL statements received from the database; The standardized SQL statement is parsed, and a syntax tree corresponding to the SQL statement is generated based on the parsing results.

7. A device for processing SQL statements, characterized in that, include: The initial execution plan determination module is used to generate a syntax tree based on the SQL statement received from the database, and to create an initial execution plan for the SQL statement based on the syntax tree. The statement elements in the SQL statement include at least the Top N elements and the adjacent elements preceding the Top N elements; wherein, the Top N elements refer to the Top N statements in the SQL statement received by the database; the Top N statements are used to filter the top N extreme values ​​of a certain indicator in real-time data; the adjacent elements preceding the Top N elements refer to the adjacent elements of the Top N statements; The target execution plan determination module is used to update the initial execution order between the TopN elements and the adjacent elements in the initial execution plan and update the initial execution logic between the TopN elements and the adjacent elements if the adjacent elements cannot be pushed down, thereby determining the target execution plan. The target execution plan execution module is used to perform calculations on the TopN elements according to the target execution plan to obtain the calculation results of the TopN elements, and to determine whether to execute the adjacent elements based on the calculation results of the TopN elements; The calculation result determination module is used to return to the execution of the adjacent elements if the result is yes, and to calculate and integrate the execution results of the target execution plan after the target execution plan is completed to determine the calculation result of the SQL statement; The target execution plan execution module includes: The sorting result stream determination unit is used to prioritize sending the TopN elements to the database based on the target execution order in the target execution plan, and to sort the data in the database based on the TopN elements to determine the sorting result stream; The counting result determination unit is used to perform counting processing on the sorting result stream, determine the counting result of the TopN elements, and determine whether to return to execute the adjacent elements based on the counting result and the target execution logic of the target execution plan.

8. An electronic device, characterized in that, The electronic device includes: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the operation method of the SQL statement according to any one of claims 1-6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that are used to cause a processor to execute the operation method of the SQL statement according to any one of claims 1-6.

Images