Database query method, device and system and medium

Abstract

The invention discloses a database query method, device and system and a medium, and the method comprises the steps: receiving a database query statement, analyzing the query statement to generate an execution plan of a tree structure, segmenting the execution plan, and obtaining a plurality of execution plan subtrees; matching the plurality of execution plan sub-trees with the intermediate result set in the cache, and determining whether intermediate result set sub-trees equal to the execution plan sub-trees exist in the intermediate result set or not; if an intermediate result set sub-tree equal to the execution plan sub-tree exists in the cache, replacing the successfully matched execution plan sub-tree with the equal intermediate result set sub-tree, and generating a replacement execution plan; and performing scheduling execution according to the replacement execution plan to obtain a corresponding database query result. The subtrees in the execution plan are matched and replaced through the intermediate result set in the cache, so that the intermediate result set in the cache can be directly used in subsequent execution operators, resource consumption and execution time are reduced, and database query performance is improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a database query method, device, system and medium. Background Art

[0002] A distributed database is a database system that can support concurrent access and data processing of different scales. SQL queries in distributed databases are generally divided into the following steps: statement parsing, execution plan generation, execution plan optimization, execution plan segmentation, and scheduling execution. The execution plan is to represent the operations to be executed by the SQL statement in the executor in a tree. Each node in the tree represents the operator type of each operation, such as table scanning, filtering, table joining, and projection. These operators involve reading a large amount of data into memory in the actual calculation process. Therefore, the query process will occupy a large amount of system resources. In particular, complex SQL statements will consume a longer execution time, thereby reducing the database query performance. Summary of the invention

[0003] In view of the above-mentioned deficiencies in the prior art, the purpose of the present invention is to provide a database query method, device, system and medium that can be applied to financial technology or other related fields, aiming to reduce resource consumption and execution time in the database query process, thereby improving the performance of database query.

[0004] The technical solution of the present invention is as follows:

[0005] A database query method, comprising:

[0006] Receive a database query statement and parse it to generate an execution plan with a tree structure, segment the execution plan, and obtain a plurality of execution plan subtrees;

[0007] Matching the plurality of execution plan subtrees with the intermediate result sets in the cache, confirming whether there is an intermediate result set subtree equal to the execution plan subtree in the intermediate result set, wherein the intermediate result set stores a plurality of intermediate result set subtrees and corresponding intermediate results of execution;

[0008] If there is an intermediate result set subtree equal to the execution plan subtree in the cache, the successfully matched execution plan subtree is replaced with the equal intermediate result set subtree to generate a replacement execution plan;

[0009] The execution is scheduled according to the replacement execution plan to obtain corresponding database query results.

[0010] In one embodiment, matching the plurality of execution plan subtrees with the intermediate result sets in the cache and confirming whether there is an intermediate result set subtree in the intermediate result set that is equal to the execution plan subtree includes:

[0011] Traversing the plurality of execution plan subtrees, and calculating the hash value of each execution plan subtree in turn;

[0012] The hash value of each execution plan subtree is matched with the intermediate result set in the cache to confirm whether there is an intermediate result set subtree with the same hash value.

[0013] In one embodiment, before receiving a database query statement and parsing to generate an execution plan in a tree structure, segmenting the execution plan, and obtaining a plurality of execution plan subtrees, the method further includes:

[0014] Get the intermediate result of the execution of the specified substatement;

[0015] The subtree of the specified substatement and the corresponding intermediate results of the execution are cached to obtain the intermediate result set.

[0016] In one embodiment, the method further comprises:

[0017] Monitor changes in table data in the data node in real time, and update the intermediate results in the intermediate result set according to the changed table data;

[0018] The update time of the table data and the most recent cache update time of the intermediate result set are recorded respectively.

[0019] In one embodiment, before monitoring the changes of table data in the data node in real time and updating the intermediate results in the intermediate result set according to the changed table data, the method further includes:

[0020] The table information to be monitored is obtained, and the library table to be monitored that is associated with the intermediate result set is screened out from all library tables of the data node according to the table information.

[0021] In one embodiment, if there is an intermediate result set subtree equal to the execution plan subtree in the cache, replacing the successfully matched execution plan subtree with the equal intermediate result set subtree to generate a replacement execution plan includes:

[0022] If there is an intermediate result set subtree in the cache that is equal to the execution plan subtree, confirm whether the most recent cache update time of the intermediate result set is after the update time of the table data;

[0023] If so, the successfully matched execution plan subtree is replaced with an equal intermediate result set subtree to generate a replacement execution plan.

[0024] In one embodiment, the scheduling execution according to the replacement execution plan to obtain the corresponding database query result specifically includes:

[0025] The execution is scheduled according to the replacement execution plan. When the intermediate result set subtree in the replacement execution plan is executed, the corresponding intermediate result is retrieved and applied from the intermediate result set to obtain the corresponding database query result.

[0026] A database query device, comprising:

[0027] A parsing and segmenting module is used to receive a database query statement and parse and generate an execution plan with a tree structure, segment the execution plan, and obtain a plurality of execution plan subtrees;

[0028] A subtree matching module is used to match the several execution plan subtrees with the intermediate result sets in the cache, and confirm whether there is an intermediate result set subtree equal to the execution plan subtree in the intermediate result set, wherein the intermediate result set stores several intermediate result set subtrees and corresponding intermediate results of execution;

[0029] A subtree replacement module, used to replace the successfully matched execution plan subtree with an equal intermediate result set subtree if there is an intermediate result set subtree equal to the execution plan subtree in the cache, and generate a replacement execution plan;

[0030] The execution module is used to schedule execution according to the replacement execution plan to obtain corresponding database query results.

[0031] A database query system, the system comprising at least one processor; and

[0032] a memory communicatively connected to the at least one processor; wherein,

[0033] The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the above-mentioned database query method.

[0034] A non-volatile computer-readable storage medium stores computer-executable instructions. When the computer-executable instructions are executed by one or more processors, the one or more processors can execute the above-mentioned database query method.

[0035] Beneficial effects: The present invention discloses a database query method, device, system and medium. Compared with the prior art, the embodiments of the present invention match and replace the subtree in the execution plan through the intermediate result set in the cache, so that in the subsequent execution operator, the intermediate result set in the cache can be directly used based on the replaced execution plan, which effectively reduces resource consumption and execution time and improves the performance of database query. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] The present invention will be further described below with reference to the accompanying drawings and embodiments, in which:

[0037] Figure 1 A flow chart of a database query method provided by an embodiment of the present invention;

[0038] Figure 2 A schematic diagram of the functional modules of a database query device provided by an embodiment of the present invention;

[0039] Figure 3 A schematic diagram of the hardware structure of a database query system provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0040] In order to make the purpose, technical solution and effect of the present invention clearer and more specific, the present invention is further described in detail below. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention. The embodiments of the present invention are described below in conjunction with the accompanying drawings.

[0041] like Figure 1 As shown, the database query method provided in this embodiment includes the following steps:

[0042] S101, receiving a database query statement and parsing to generate an execution plan with a tree structure, segmenting the execution plan to obtain a plurality of execution plan subtrees.

[0043] In this embodiment, in the database system, when a SQL query statement is received, it is first parsed, including understanding the intent of the SQL statement, determining tables and fields, checking permissions, etc. After the parsing is completed, a tree-structured execution plan is generated. This execution plan describes in detail how to execute the SQL statement. Each node in the execution plan represents the operator type of each step of the operation, such as table scanning, filtering, table joining, projection, etc., and each step of the data operation is implemented through operator calculation. Further, the generated execution is segmented in the future. For example, the execution plan can be divided into multiple stages according to the query logic according to the logical segmentation strategy, such as data extraction, data filtering, data aggregation, etc., or the execution plan can be segmented according to the physical storage and access method according to the physical segmentation strategy, such as index access, table scanning, etc., so as to obtain several execution plan subtrees, so as to more flexibly manage and optimize the query execution process.

[0044] S102, matching the plurality of execution plan subtrees with the intermediate result sets in the cache, confirming whether there is an intermediate result set subtree in the intermediate result set that is equal to the execution plan subtree, wherein the intermediate result set stores a plurality of intermediate result set subtrees and corresponding intermediate results of execution.

[0045] In this embodiment, the intermediate results of the execution of some sub-statements are pre-cached, that is, an intermediate result set is stored in the cache, and the intermediate result set stores several intermediate result set subtrees corresponding to the sub-statements and the corresponding intermediate results of the execution. Through the intermediate result set in the cache, reusable intermediate results can be provided when performing database queries. Specifically, after obtaining several execution plan subtrees in segments, it is checked whether the cache has stored intermediate result set subtrees equal to these subtrees, that is, the structure and content of the execution plan subtree and the subtree in the intermediate result set are compared to determine whether there is a cached intermediate result set subtree that can be used for replacement. Therefore, there is no need to perform repeated calculations based on the same execution plan subtree every time, which can avoid repeated calculations and occupying system resources, and achieve the purpose of performance optimization.

[0046] S103: If there is an intermediate result set subtree equal to the execution plan subtree in the cache, the successfully matched execution plan subtree is replaced with the equal intermediate result set subtree to generate a replacement execution plan;

[0047] In this embodiment, if there are equal subtrees in the cache, it means that the same query part has been calculated before and its results can be reused. Therefore, if the match finds that there are equal intermediate result set subtrees in the cache, the original execution plan subtree will replace the equivalent intermediate result set subtree in the cache. Through the replacement, a new execution plan is generated, that is, the replacement execution plan. This plan contains the part of the intermediate results that can be directly used in the cache, thereby avoiding repeated calculations, which is conducive to improving the query response speed and optimizing the use of system resources. If there are no equal subtrees in the cache, the current execution plan subtree may be the subtree executed for the first time, and its execution results can be cached for subsequent reuse. The specific cache size limit can use the LRU algorithm to retain hot data.

[0048] S104: Perform scheduling execution according to the replacement execution plan to obtain corresponding database query results.

[0049] In this embodiment, after the replacement execution plan is generated, it is scheduled for execution according to the new execution plan, that is, the query is executed according to the instructions in the new execution plan. Since the replacement execution plan contains a cached intermediate result set subtree, the cached intermediate result set can be used to avoid repeated calculation processes, and the corresponding database query results can be obtained while saving computing resources and time as much as possible, thereby achieving fast and accurate database queries and improving the response speed of database queries.

[0050] Specifically, in the execution phase, the execution is scheduled based on the replacement execution plan that contains the matching intermediate result set subtree. When the part replaced by the intermediate result set subtree is executed, the corresponding intermediate result is directly retrieved from the cache instead of re-executing the calculation. For example, according to the identifier of the intermediate result set subtree, the corresponding intermediate result is retrieved from the cache until the replacement execution plan is executed. The results of all execution steps are summarized and the complete query result is finally generated. In this way, the retrieved intermediate results are directly applied to the query execution process, replacing the calculation steps that originally need to be executed, avoiding repeated calculations and occupying system resources, and achieving the purpose of performance optimization.

[0051] In one embodiment, step S102 includes:

[0052] Traversing the plurality of execution plan subtrees, and calculating the hash value of each execution plan subtree in turn;

[0053] The hash value of each execution plan subtree is matched with the intermediate result set in the cache to confirm whether there is an intermediate result set subtree with the same hash value.

[0054] In this embodiment, the hash value of each subtree is calculated to determine whether there is an execution plan subtree that is the same as the one in the cache. After the execution plan is generated and segmented, all execution plan subtrees are traversed. The hash value is calculated for each execution plan subtree. The hash value is a string of fixed length that represents the structure and content of the subtree. Since the calculation of the hash value only depends on the structure and content of the subtree, and does not require the actual execution of the subtree, by calculating the hash value as the basis for determining equivalent matching, faster and more efficient matching can be achieved, further improving the query response speed.

[0055] Once the hash value of each execution plan subtree is calculated, these hash values ​​are compared with the hash values ​​of the intermediate result set subtrees in the cache. That is, each intermediate result set subtree in the cache has a hash value associated with it. The comparison results are used to confirm whether there is an intermediate result set subtree in the intermediate result set whose hash value is equal to the hash value of the execution plan subtree. The intermediate result set subtrees with equal hash values ​​are regarded as subtrees equivalent to the execution plan subtrees, so as to facilitate the direct use of the cached intermediate result sets in the subsequent execution process and improve query efficiency.

[0056] Preferably, to ensure the reliability of the matched equivalent subtrees, the execution plan subtree and the intermediate result set subtree with equal hash values ​​can be further compared, for example, the structure, content, semantics, etc. between the two subtrees are compared to ensure that the two subtrees are completely equivalent in operation logic and operation content, and that they can be safely replaced without affecting the correctness of the query results.

[0057] In one embodiment, before step S101, the method further includes:

[0058] Get the intermediate result of the execution of the specified substatement;

[0059] The subtree of the specified substatement and the corresponding intermediate results of the execution are cached to obtain the intermediate result set.

[0060] In this embodiment, in a database query, some sub-statements may appear multiple times in a complex SQL query, or may be frequently executed due to business logic, or may be executed for the first time as a sub-statement, etc. The intermediate results of the execution of these sub-statements are cached to build an intermediate result set for reuse in subsequent queries. That is, the specific specified sub-statement may be a repeated statement, or a high-frequency sub-statement with an execution frequency higher than a preset frequency, or a sub-statement executed for the first time, etc. Among them, repeated statements refer to the fact that in a complex SQL query, there may be multiple places where the same sub-query or expression is used. For example, a query may need to calculate the same date range or the same aggregate function multiple times; high-frequency sub-statements refer to certain sub-statements that may be frequently executed due to business logic, such as counting the total sales in a certain time period. By obtaining the intermediate results that these sub-statements should execute, storing these intermediate results in a cache, and associating them with the corresponding subtree structure, an intermediate result set is constructed, so that when the same sub-statement appears in a subsequent query, the result can be directly obtained from the cache without re-executing the sub-statement, thereby improving the performance and efficiency of database queries.

[0061] In one embodiment, the method further comprises:

[0062] Monitor changes in table data in the data node in real time, and update the intermediate results in the intermediate result set according to the changed table data;

[0063] The update time of the table data and the most recent cache update time of the intermediate result set are recorded respectively.

[0064] In this embodiment, since the table data of the data node may change in real time, not all intermediate result sets can be reused. Therefore, it is necessary to monitor the data changes of the data node in real time, record the latest time point when the data volume of the table involved in the cache is updated, and provide a basis for judging the cache availability for subsequent subtree replacement and execution process.

[0065] Specifically, when the storage node is MySQL, real-time monitoring is performed using binlog. Binlog is a binary log file that records all statements that update the database (such as DDL and DML statements) and is saved in the disk in binary form. That is, binlog records logs of all database modification operations, including insert, update, and delete operations. By parsing these logs, changes in table data in the database can be captured in real time. During real-time monitoring, parsing tools such as Canal and Debezium can be used to parse the contents in binlog and identify data change events. When log data changes, it will be monitored, so that the changed data can be obtained in real time. Data changes correspond to changes in the intermediate results of execution. Therefore, once changes in table data are detected, the corresponding intermediate result set needs to be updated to maintain data consistency, that is, the intermediate results of the affected sub-statements are recalculated according to the changed table data, and the cache is updated to ensure that the intermediate result set is synchronized with the data in the database, thereby improving the accuracy of the query results.

[0066] Every time the table data changes, the time point of this change is recorded, for example, by parsing the binlog to obtain the timestamp of the data operation to record the update time of the table data. At the same time, since it takes time to calculate the intermediate results and update the cache after the data changes, each time the intermediate result set is updated, this time point is also recorded, that is, the time when the intermediate result set was most recently cached. This helps to determine whether the cached intermediate results are still valid in the future, avoid using outdated cached data, and ensure the real-time and accuracy of the data.

[0067] In one embodiment, before monitoring the changes of table data in the data node in real time and updating the intermediate results in the intermediate result set according to the changed table data, the method further includes:

[0068] The table information to be monitored is obtained, and the library table to be monitored that is associated with the intermediate result set is screened out from all library tables of the data node according to the table information.

[0069] In one embodiment, in order to reduce the amount of data monitored and reduce unnecessary resource consumption, all library tables of the data node can be screened based on the table information to be monitored, and the library tables to be monitored that are associated with the intermediate result set are screened out. The specific table information can be, for example, the table name, architecture, and their location in the database, etc., so that only the relevant data of the library tables associated with the data in the cache can be updated, and the data updates of other library tables can be filtered out. By accurately determining the tables that need to be monitored, the allocation of monitoring resources can be optimized and the system efficiency can be improved.

[0070] In one embodiment, step S103 includes:

[0071] If there is an intermediate result set subtree in the cache that is equal to the execution plan subtree, confirm whether the most recent cache update time of the intermediate result set is after the update time of the table data;

[0072] If so, the successfully matched execution plan subtree is replaced with an equal intermediate result set subtree to generate a replacement execution plan.

[0073] In this embodiment, when replacing an equal execution plan subtree with an intermediate result set subtree, it is necessary to compare the most recent cache update time of the execution plan subtree in the cache and the update time of the table, specifically by comparing two timestamps, the cache update time of the intermediate result set (expressed as the cache time) and the update time of the table data (obtained from binlog or other data change logs). If the cache time is later than the update time of the table data, that is, the cache time of the intermediate result set is relatively new, which means that the cache data reflects the latest status of the table, then the successfully matched execution plan subtree can be replaced with an equal intermediate result set subtree to generate a replacement execution plan, which will be used for subsequent query executions; if the update time of the table data is after the cache time, it indicates that the cached intermediate result may be inconsistent with the latest table data. At this time, no replacement operation is performed to prevent the use of an outdated intermediate result set, thereby avoiding the risk of obtaining an erroneous query result based on outdated data and enhancing the accuracy of the query result.

[0074] Preferably, after obtaining the cache update time of the intermediate result set and the update time of the table data, these two time points are also used as the basis for judgment in the subsequent execution stage. That is, in the execution stage of the statement, it is also first determined whether the latest cache time is after the update time of the table. If it is, it can be determined that the cached intermediate result set is the latest data, and the cached intermediate result set can be directly applied during execution. If it is not, it can be determined that the cached intermediate result set is expired data, that is, the relevant data may have changed between the generation of the replacement execution plan and the actual execution, so the original execution plan node is still calculated to execute the query to ensure the accuracy and reliability of the query execution.

[0075] It should be noted that there is not necessarily a certain order between the above-mentioned steps. A person skilled in the art can understand, based on the description of the embodiments of the present invention, that in different embodiments, the above-mentioned steps may have different execution orders, that is, they may be executed in parallel, may be executed interchangeably, and so on.

[0076] Another embodiment of the present invention provides a database query device, such as Figure 2 As shown, the device 1 comprises:

[0077] A database query device, comprising:

[0078] The parsing and segmenting module 11 is used to receive a database query statement and parse and generate an execution plan with a tree structure, segment the execution plan, and obtain a plurality of execution plan subtrees;

[0079] The subtree matching module 12 is used to match the several execution plan subtrees with the intermediate result sets in the cache, and confirm whether there is an intermediate result set subtree equal to the execution plan subtree in the intermediate result set, and the intermediate result set stores several intermediate result set subtrees and corresponding intermediate results of execution;

[0080] A subtree replacement module 13 is used to replace the successfully matched execution plan subtree with an equal intermediate result set subtree if there is an intermediate result set subtree equal to the execution plan subtree in the cache, and generate a replacement execution plan;

[0081] The execution module 14 is used to schedule execution according to the replacement execution plan to obtain corresponding database query results.

[0082] The module referred to in the present invention refers to a series of computer program instruction segments that can perform specific functions, which are more suitable for describing the execution process of database queries than programs. For the specific implementation methods of each module, please refer to the corresponding method embodiments above, which will not be repeated here.

[0083] Another embodiment of the present invention provides a database query system, such as Figure 3 As shown, the system 10 includes:

[0084] One or more processors 110 and memory 120, Figure 3 A processor 110 is used as an example for description. The processor 110 and the memory 120 may be connected via a bus or other means. Figure 3 The example of connecting through bus is taken in the following.

[0085] The processor 110 is used to complete various control logics of the system 10, and it can be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field programmable gate array (FPGA), a single-chip microcomputer, an ARM (Acorn RISC Machine) or other programmable logic device, a discrete gate or transistor logic, a discrete hardware component, or any combination of these components. In addition, the processor 110 can also be any traditional processor, microprocessor or state machine. The processor 110 can also be implemented as a combination of computing devices, for example, a combination of a DSP and a microprocessor, a plurality of microprocessors, one or more microprocessors combined with a DSP and / or any other such configuration.

[0086] The memory 120 is a non-volatile computer-readable storage medium that can be used to store non-volatile software programs, non-volatile computer executable programs and modules, such as program instructions corresponding to the database query method in the embodiment of the present invention. The processor 110 executes various functional applications and data processing of the system 10 by running the non-volatile software programs, instructions and units stored in the memory 120, that is, implementing the database query method in the above method embodiment.

[0087] The memory 120 may include a program storage area and a data storage area, wherein the program storage area may store an operating system and an application required by at least one function; the data storage area may store data created according to the use of the system 10, etc. In addition, the memory 120 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some embodiments, the memory 120 may optionally include a memory remotely arranged relative to the processor 110, and these remote memories may be connected to the system 10 via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0088] One or more units are stored in the memory 120, and when executed by one or more processors 110, the following steps are implemented:

[0089] Receive a database query statement and parse it to generate an execution plan with a tree structure, segment the execution plan, and obtain a plurality of execution plan subtrees;

[0090] Matching the plurality of execution plan subtrees with the intermediate result sets in the cache, confirming whether there is an intermediate result set subtree equal to the execution plan subtree in the intermediate result set, wherein the intermediate result set stores a plurality of intermediate result set subtrees and corresponding intermediate results of execution;

[0091] If there is an intermediate result set subtree equal to the execution plan subtree in the cache, the successfully matched execution plan subtree is replaced with the equal intermediate result set subtree to generate a replacement execution plan;

[0092] The execution is scheduled according to the replacement execution plan to obtain corresponding database query results.

[0093] In one embodiment, matching the plurality of execution plan subtrees with the intermediate result sets in the cache and confirming whether there is an intermediate result set subtree in the intermediate result set that is equal to the execution plan subtree includes:

[0094] Traversing the plurality of execution plan subtrees, and calculating the hash value of each execution plan subtree in turn;

[0095] The hash value of each execution plan subtree is matched with the intermediate result set in the cache to confirm whether there is an intermediate result set subtree with the same hash value.

[0096] In one embodiment, before receiving a database query statement and parsing to generate an execution plan in a tree structure, segmenting the execution plan, and obtaining a plurality of execution plan subtrees, the method further includes:

[0097] Get the intermediate result of the execution of the specified substatement;

[0098] The subtree of the specified substatement and the corresponding intermediate results of the execution are cached to obtain the intermediate result set.

[0099] In one embodiment, the method further comprises:

[0100] Monitor changes in table data in the data node in real time, and update the intermediate results in the intermediate result set according to the changed table data;

[0101] The update time of the table data and the most recent cache update time of the intermediate result set are recorded respectively.

[0102] In one embodiment, before monitoring the changes of table data in the data node in real time and updating the intermediate results in the intermediate result set according to the changed table data, the method further includes:

[0103] The table information to be monitored is obtained, and the library table to be monitored that is associated with the intermediate result set is screened out from all library tables of the data node according to the table information.

[0104] In one embodiment, if there is an intermediate result set subtree equal to the execution plan subtree in the cache, replacing the successfully matched execution plan subtree with the equal intermediate result set subtree to generate a replacement execution plan includes:

[0105] If there is an intermediate result set subtree in the cache that is equal to the execution plan subtree, confirm whether the most recent cache update time of the intermediate result set is after the update time of the table data;

[0106] If so, the successfully matched execution plan subtree is replaced with an equal intermediate result set subtree to generate a replacement execution plan.

[0107] In one embodiment, the scheduling execution according to the replacement execution plan to obtain the corresponding database query result specifically includes:

[0108] The execution is scheduled according to the replacement execution plan. When the intermediate result set subtree in the replacement execution plan is executed, the corresponding intermediate result is retrieved and applied from the intermediate result set to obtain the corresponding database query result.

[0109] An embodiment of the present invention provides a non-volatile computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are executed by one or more processors, for example, to execute the above-described Figure 1 The method comprises steps S101 to S104.

[0110] As an example, non-volatile storage media can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) as external cache memory. By way of illustration and not limitation, RAM can be obtained in many forms such as synchronous RAM (SRAM), dynamic RAM, (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), Synchlink DRAM (SLDRAM), and direct Rambus RAM (DRRAM). The disclosed memory components or memories of the operating environment described herein are intended to include one or more of these and / or any other suitable types of memory.

[0111] In summary, the present invention discloses a method, device, system and medium for querying a database. The method receives a database query statement and parses to generate an execution plan with a tree structure, segments the execution plan, and obtains several execution plan subtrees; matches several execution plan subtrees with intermediate result sets in a cache, confirms whether there is an intermediate result set subtree equal to the execution plan subtree in the intermediate result set, and stores several intermediate result set subtrees and corresponding intermediate results of execution in the intermediate result set; if there is an intermediate result set subtree equal to the execution plan subtree in the cache, replace the successfully matched execution plan subtree with an equal intermediate result set subtree, and generate a replacement execution plan; schedule and execute according to the replacement execution plan to obtain the corresponding database query result. The subtree in the execution plan is matched and replaced by the intermediate result set in the cache, so that the intermediate result set in the cache can be directly used in the subsequent execution operator based on the replaced execution plan, which effectively reduces resource consumption and execution time and improves the performance of database query.

[0112] Of course, those skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be implemented by instructing related hardware (such as a processor, a controller, etc.) through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium, and the computer program can include the processes of the above-mentioned method embodiments when executed. The storage medium can be a memory, a disk, a floppy disk, a flash memory, an optical storage device, etc.

[0113] It should be understood that the application of the present invention is not limited to the above examples. For ordinary technicians in this field, improvements or changes can be made based on the above description. All these improvements and changes should fall within the scope of protection of the claims attached to the present invention.

Claims

1. A method for querying a database, characterized in that: include: Receive a database query statement and parse it to generate an execution plan with a tree structure, segment the execution plan, and obtain a plurality of execution plan subtrees; Matching the plurality of execution plan subtrees with the intermediate result sets in the cache, confirming whether there is an intermediate result set subtree equal to the execution plan subtree in the intermediate result set, wherein the intermediate result set stores a plurality of intermediate result set subtrees and corresponding intermediate results of execution; If there is an intermediate result set subtree equal to the execution plan subtree in the cache, the successfully matched execution plan subtree is replaced with the equal intermediate result set subtree to generate a replacement execution plan; The execution is scheduled according to the replacement execution plan to obtain corresponding database query results.

2. The method for querying a database according to claim 1, characterized in that: The matching of the plurality of execution plan subtrees with the intermediate result sets in the cache, and confirming whether there is an intermediate result set subtree in the intermediate result set that is equal to the execution plan subtree, includes: Traversing the plurality of execution plan subtrees, and calculating the hash value of each execution plan subtree in turn; The hash value of each execution plan subtree is matched with the intermediate result set in the cache to confirm whether there is an intermediate result set subtree with the same hash value.

3. The method for querying a database according to claim 1, characterized in that: Before receiving the database query statement and parsing to generate an execution plan with a tree structure, segmenting the execution plan, and obtaining a plurality of execution plan subtrees, the method further includes: Get the intermediate result of the execution of the specified substatement; The subtree of the specified substatement and the corresponding intermediate results of the execution are cached to obtain the intermediate result set.

4. The method for querying a database according to claim 1, characterized in that: The method further comprises: Monitor changes in table data in the data node in real time, and update the intermediate results in the intermediate result set according to the changed table data; The update time of the table data and the most recent cache update time of the intermediate result set are recorded respectively.

5. The method for querying a database according to claim 4, characterized in that: Before monitoring the changes of table data in the data node in real time and updating the intermediate results in the intermediate result set according to the changed table data, the method further includes: The table information to be monitored is obtained, and the library table to be monitored that is associated with the intermediate result set is screened out from all library tables of the data node according to the table information.

6. The method for querying a database according to claim 4, characterized in that: If there is an intermediate result set subtree equal to the execution plan subtree in the cache, the successfully matched execution plan subtree is replaced with the equal intermediate result set subtree to generate a replacement execution plan, including: If there is an intermediate result set subtree in the cache that is equal to the execution plan subtree, confirm whether the most recent cache update time of the intermediate result set is after the update time of the table data; If so, the successfully matched execution plan subtree is replaced with an equal intermediate result set subtree to generate a replacement execution plan.

7. The method for querying a database according to any one of claims 1 to 6, characterized in that: The scheduling execution is performed according to the replacement execution plan to obtain the corresponding database query result, specifically including: The execution is scheduled according to the replacement execution plan. When the intermediate result set subtree in the replacement execution plan is executed, the corresponding intermediate result is retrieved and applied from the intermediate result set to obtain the corresponding database query result.

8. A database query device, characterized in that: include: A parsing and segmenting module is used to receive a database query statement and parse and generate an execution plan with a tree structure, segment the execution plan, and obtain a plurality of execution plan subtrees; A subtree matching module is used to match the several execution plan subtrees with the intermediate result sets in the cache, and confirm whether there is an intermediate result set subtree equal to the execution plan subtree in the intermediate result set, wherein the intermediate result set stores several intermediate result set subtrees and corresponding intermediate results of execution; A subtree replacement module, used to replace the successfully matched execution plan subtree with an equal intermediate result set subtree if there is an intermediate result set subtree equal to the execution plan subtree in the cache, and generate a replacement execution plan; The execution module is used to schedule execution according to the replacement execution plan to obtain corresponding database query results.

9. A database query system, characterized in that: The system includes at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the database query method according to any one of claims 1 to 7.

10. A non-volatile computer-readable storage medium, characterized in that: The non-volatile computer-readable storage medium stores computer-executable instructions, which, when executed by one or more processors, enable the one or more processors to execute the database query method described in any one of claims 1-7.

Images