System and query method

Abstract

The embodiment of the present specification provides a system and a query method, wherein the coordination node of the system comprises a coordination service unit and a connection unit, the data node of the system comprises a data service unit, the connection unit comprises a connection capable of multiplexing, and the query method comprises the following steps: when receiving a preparation request carrying a sub-query statement, the data service unit generates query optimization data by using the sub-query statement, and writes the query optimization data into a cache area in correspondence with the information of the sub-query statement; the coordination service unit establishes a connection with the data service unit based on the connection in the connection unit, and when receiving a target query statement, determines a target sub-query statement of the target query statement, finds out a target data service unit which has cached the query optimization data of the target sub-query statement by using the target information of the target sub-query statement, and sends a use request to the target data service unit; and the data service unit multiplexes the query optimization data in response to receiving the use request.

Description

Technical Field

[0001] The embodiments in this specification relate to the field of computer technology, and in particular to a query method. Background Technology

[0002] In database systems, query optimization is a crucial, interconnected process. Query optimization data encompasses various data generated during the optimization process. For example, in database queries, optimization includes generating a query tree through syntax analysis, performing logical equivalence transformations, filtering physical execution paths, calculating the cost of each path based on statistical information and data distribution, and selecting the optimal execution path. In other words, query optimization transforms the query tree into a query execution plan for the target query statement. In multi-machine database systems, the optimization process is more complex and computationally intensive than in single-machine databases. Furthermore, if a query statement is executed repeatedly, the optimization process is also repeated multiple times, leading to unnecessary performance degradation. Therefore, there is a pressing need for query methods that can effectively improve query performance. Summary of the Invention

[0003] In view of this, embodiments of this specification provide a query method. One or more embodiments of this specification also relate to systems, query devices, computing devices, computer-readable storage media, and computer programs to address technical deficiencies in the prior art.

[0004] According to a first aspect of the embodiments of this specification, a query method is provided, applied to a system including a coordination node and a data node. The coordination node includes a coordination service unit and a connection unit, the data node includes a data service unit, and the connection unit includes a connection that can be reused between the coordination service units. The method includes: when the data service unit receives a preparation request carrying a subquery statement, it generates query optimization data using the subquery statement, and writes the query optimization data and the information of the subquery statement into a cache, wherein the subquery statement is generated based on the query optimization result of the query statement; the coordination service unit establishes a connection with the data service unit based on the connection in the connection unit, and when it receives a target query statement, it determines the target subquery statement of the target query statement, uses the target information of the target subquery statement to find a target data service unit that has cached the query optimization data of the target subquery statement, and sends a usage request to the target data service unit; in response to receiving the usage request, the data service unit reuses the query optimization data corresponding to the target information in the cache to execute the query of the target subquery statement.

[0005] According to a second aspect of the embodiments of this specification, a query method is provided, applied to a coordination service unit configured in a coordination node of a system, the system further including a data node, the coordination node further including a connection unit, the data node including a data service unit, the connection unit including a connection reusable between coordination service units, one connection corresponding to one data service unit in the data node, the method comprising: establishing a connection with a data service unit based on the connection in the connection unit, wherein the data service unit caches the correspondence between information of a subquery statement and query optimization data, the query optimization data being generated based on the subquery statement, the subquery statement being generated based on the query optimization result of the query statement; upon receiving a target query statement, determining a target subquery statement of the target query statement; using the target information of the target subquery statement, finding a target data service unit that has cached the query optimization data of the target subquery statement; sending a usage request to the target data service unit, causing the target data service unit to respond to receiving the usage request by reusing the query optimization data corresponding to the target information in the cache to execute the query of the target subquery statement.

[0006] According to a third aspect of the embodiments of this specification, a query method is provided, applied to a data service unit, the data service unit being configured in a data node of a system, the system further including a coordination node, the coordination node further including a connection unit, the connection unit including a connection reusable among coordination service units, one connection corresponding to one data service unit in the data node, the method comprising: upon receiving a preparation request carrying a subquery statement, generating query optimization data using the subquery statement, writing the query optimization data and the information of the subquery statement into a cache, the subquery statement being generated based on the query optimization result of the query statement; receiving a usage request sent by the coordination service unit, the usage request being issued by the coordination service unit to the target data service unit when receiving a target query statement, determining the target subquery statement of the target query statement, and using the target information of the target subquery statement to find the target data service unit that has cached the query optimization data of the target subquery statement; and, according to the usage request, reusing the query optimization data corresponding to the target information in the cache to execute the query of the target subquery statement.

[0007] According to a fourth aspect of the embodiments of this specification, a query device configured in a coordination service unit is provided, comprising: a connection module configured to establish a connection with a data service unit based on a connection in the connection unit, wherein the data service unit caches the correspondence between information of a subquery statement and query optimization data, the query optimization data being generated based on the subquery statement, and the subquery statement being generated based on the query optimization result of the query statement; a statement determination module configured to determine a target subquery statement of the target query statement upon receiving a target query statement; a lookup cache module configured to use the target information of the target subquery statement to find a target data service unit that has cached the query optimization data of the target subquery statement; and a query reuse module configured to send a usage request to the target data service unit, causing the target data service unit to respond to receiving the usage request by reusing the query optimization data corresponding to the target information in the cache to execute the query of the target subquery statement.

[0008] According to a fifth aspect of the embodiments of this specification, a query device configured in a data service unit is provided, comprising: a preparation module configured to, upon receiving a preparation request carrying a subquery statement, generate query optimization data using the subquery statement, and write the query optimization data and information of the subquery statement into a cache, wherein the subquery statement is generated based on the query optimization result of the query statement; a request receiving module 904 configured to receive a usage request sent by the coordination service unit, wherein the usage request is issued by the coordination service unit to the target data service unit when it receives a target query statement, determines the target subquery statement of the target query statement, and uses the target information of the target subquery statement to find the target data service unit that has cached the query optimization data of the target subquery statement; and a reuse execution module configured to, according to the usage request, reuse the query optimization data corresponding to the target information in the cache to execute the query of the target subquery statement.

[0009] According to a sixth aspect of the embodiments of this specification, a computing device is provided, including: a memory and a processor; the memory is used to store computer-executable instructions, and the processor is used to execute the computer-executable instructions, wherein the computer-executable instructions, when executed by the processor, implement the steps of the above-described query method.

[0010] According to a seventh aspect of the embodiments of this specification, a computer-readable storage medium is provided that stores computer-executable instructions, which, when executed by a processor, implement the steps of the query method described above.

[0011] According to an eighth aspect of the embodiments of this specification, a computer program is provided, wherein when the computer program is executed in a computer, it causes the computer to perform the steps of the above-described query method.

[0012] According to a ninth aspect of the embodiments of this specification, a system is provided for implementing the query method as described in any of the foregoing, comprising: a coordinating node and a data node.

[0013] One embodiment of this specification implements a query method. In this method, a connection unit includes a connection reusable between coordination service units. The connection has a mapping relationship with the data service units of the data nodes. When a data service unit receives a preparation request carrying a subquery statement, it generates query optimization data using the subquery statement and writes the query optimization data and the information of the subquery statement into a cache. This allows the coordination service unit of the coordination node to establish a connection with the data service unit based on the connection in the connection unit. Upon receiving a target query statement, the method determines the target subquery statement, uses the target information of the target subquery statement to locate the target data service unit that has cached the query optimization data of the target subquery statement, and sends a usage request to the target data service unit. The data service unit, in response to receiving the usage request, reuses the query optimization data corresponding to the target information in the cache according to the usage request to execute the query of the target subquery statement.

[0014] As can be seen, since this method provides a global cache based on connection units, the data service unit caches the correspondence between the information of the subquery statements and the query optimization data based on the subquery statements issued by the coordination service unit. The subquery statements are generated based on the query optimization results of the query statements. Therefore, the generated subquery statements are standardized. Although the query statements are different, as long as the subplan functions in the query plan of the query statement are the same, the subplan corresponding to the subquery statement can be reused in queries of different query plans. This decouples the query optimization data from the coordination service unit. Furthermore, the coordination node provides a globally shared connection unit. Thus, based on the global reuse of the connection at the coordination node, and using the information of the subquery statements as the search clue, the query optimization data cached by the data service unit can be reused among the coordination service units to execute optimized queries, thereby reducing redundancy, saving resources, and improving query efficiency. Attached Figure Description

[0015] Figure 1 This is a schematic diagram of the cluster architecture of a system provided in one embodiment of this specification;

[0016] Figure 2 This is a block diagram of a system provided in one embodiment of this specification;

[0017] Figure 3a This is a block diagram of a system provided in another embodiment of this specification;

[0018] Figure 3b This is a flowchart of a query method provided in one embodiment of this specification;

[0019] Figure 4 This is a flowchart of a query method applied to a coordination service unit, provided in one embodiment of this specification;

[0020] Figure 5a This is a schematic diagram of the signaling interaction of a query method provided in one embodiment of this specification;

[0021] Figure 5b This is a schematic diagram of a synchronous cache provided in one embodiment of this specification;

[0022] Figure 5c This is a schematic diagram of the signaling interaction of a query method provided in another embodiment of this specification;

[0023] Figure 6 This is a schematic diagram of the query execution plan for the target query statement provided in one embodiment of this specification;

[0024] Figure 7 This is a schematic diagram of the structure of a query device provided in one embodiment of this specification;

[0025] Figure 8 This is a flowchart of a query method applied to a data service unit according to an embodiment of this specification;

[0026] Figure 9 This is a schematic diagram of the structure of a query device provided in another embodiment of this specification;

[0027] Figure 10 This is a schematic diagram of the system structure provided in another embodiment of this specification;

[0028] Figure 11 This is a structural block diagram of a computing device provided in one embodiment of this specification. Detailed Implementation

[0029] Many specific details are set forth in the following description to provide a full understanding of this specification. However, this specification can be implemented in many other ways than those described herein, and those skilled in the art can make similar extensions without departing from the spirit of this specification. Therefore, this specification is not limited to the specific implementations disclosed below.

[0030] The terminology used in one or more embodiments of this specification is for the purpose of describing particular embodiments only and is not intended to be limiting of the one or more embodiments of this specification. The singular forms “a,” “described,” and “the” as used in one or more embodiments of this specification and the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term “and / or” as used in one or more embodiments of this specification refers to and includes any or all possible combinations of one or more associated listed items.

[0031] It should be understood that although the terms first, second, etc., may be used to describe various information in one or more embodiments of this specification, such information should not be limited to these terms. These terms are only used to distinguish information of the same type from one another. For example, first may also be referred to as second without departing from the scope of one or more embodiments of this specification, and similarly, second may also be referred to as first. Depending on the context, the word "if" as used herein may be interpreted as "when," "when," or "in response to a determination."

[0032] First, the terms and concepts used in one or more embodiments of this specification will be explained.

[0033] A coordinating node is a server in the system used to run coordination service units.

[0034] A data node is a server in the system used to run data service units.

[0035] A coordination service unit is the program execution entity running within a coordination node. It is responsible for processing query statements sent by clients and other querying parties, generating and executing query execution plans, and distributing subquery statements to data service units for execution. A coordination node can include one or more coordination service units. In practical applications, a coordination service unit can manifest as any form of program execution entity, such as a service process, and one coordination service unit corresponds to one session of communication with the querying party.

[0036] The data service unit is the main program execution entity running in the data node, responsible for processing the sub-query statements issued by the coordination service unit.

[0037] A connection unit can be understood as a buffer process for creating and managing connections.

[0038] A query statement is a program statement used to describe query conditions, enabling the entity executing the query to return data that meets those conditions. The specific query language used in a query statement is determined by the environment of the query system in the actual application scenario. For example, in SQL database queries, the query statement is expressed as an SQL query statement.

[0039] The information in a subquery statement is determined based on the characteristics of the subquery statement and is used to access the subquery statement. For example, the information in a subquery statement may be a hash value obtained by hashing the subquery statement.

[0040] Query optimization data is generated during the query optimization process to help with queries and improve query speed. For example, in database queries, query optimization includes the query tree generated by syntax analysis and the query execution plan.

[0041] A query tree is generated by parsing a query statement and is used to describe the internal representation of the query statement.

[0042] The query execution plan is an optimized execution path obtained by performing logical equivalent transformations on the query tree, filtering physical execution paths, and calculating the cost of the paths based on statistical information, data distribution, and other information.

[0043] In database systems, nodes are often categorized into coordinating nodes and data nodes based on their roles. Coordinating nodes are responsible for generating query execution plans, while data nodes are responsible for executing those plans. Specifically, coordinating nodes generate the query execution plan for the target query statement based on data metadata, statistics, and distribution information through query optimization. The coordinating node then pushes down the calculation of sub-plans by distributing the query execution plan, bringing the calculations closer to the data and reducing inefficient data transmission. Traditional query methods currently suffer from the following problems:

[0044] Data nodes do not cache query execution plans. The coordinating node generates the query execution plan, distributes it to each data node for execution, and finally aggregates the results at the coordinating node. However, distributing the query execution plan requires serialization and deserialization operations, and the distributed execution plan is controlled by the parent execution plan on the coordinating node. This means the sub-plan pushed down to the data nodes is a part of the target query's execution plan in the coordinating node, resulting in strong coupling and making it impossible to reuse on the data nodes. Therefore, traditionally, the pushed-down sub-plan can only serve the execution plan of the target query statement being distributed. Logically, the execution plans for some repeated queries are usually reusable, and this inability to reuse repeated operations leads to performance loss. Furthermore, in query systems such as databases, query optimization data is typically divided into two parts: one part generates a query tree from the query statement through parse, analyze, and rewrite operations; the other part generates an executable query execution plan through logical and physical optimization. Traditional solutions do not cache and reuse the query tree, causing the query tree generation operation to be repeated every time the same query is executed, resulting in unnecessary performance loss. In some traditional solutions, the data service unit typically provides services exclusive to the coordination service unit, making it impossible to share cached information from the data node service unit with other coordination service units. This leads to more processes caching redundant query optimization data, resulting in excessive data node resource consumption and ultimately impacting the processing capacity of the data cluster.

[0045] In view of this, a system and a query method are provided in this specification. This specification also relates to a query device, a computing device, and a computer-readable storage medium, which will be described in detail in the following embodiments.

[0046] See Figure 1 , Figure 1 A schematic diagram of a cluster architecture of a system provided according to an embodiment of this specification is shown. Figure 1 As shown, in this cluster architecture, the node types are divided into coordination nodes (CN), data nodes (DN), and central time synchronization nodes.

[0047] A coordinating node is a server in the system that runs the coordination service unit. A data node is a server in the system that runs the data service unit. A central time synchronization node is used for time synchronization among all nodes in the cluster. Clients send query statements to the CN, and the CN performs the query according to the query method provided in the embodiments of this specification.

[0048] See Figure 2 , Figure 2A block diagram of a system provided according to an embodiment of this specification is shown, specifically including: a coordination node 210 and a data node 220.

[0049] The coordination node 210 includes a coordination service unit 2102 and a connection unit 2104. The data node 220 includes a data service unit 2202. The connection unit 2104 includes a connection that can be reused among the coordination service units, and the connection has a mapping relationship with the data service unit 2202. For example, the connection and the data service unit can be a one-to-one mapping relationship, or it can be another mapping relationship set according to the actual application scenario. This specification does not limit this.

[0050] The data service unit 2202 is configured to, upon receiving a preparation request carrying a subquery statement, generate query optimization data using the subquery statement, and write the query optimization data and the information of the subquery statement into a cache area accordingly.

[0051] The coordination service unit 2102 is configured to establish a connection with the data service unit 2202 based on the connection in the connection unit 2104. When a target query statement is received, the target sub-query statement of the target query statement is determined. The target information of the target sub-query statement is used to find the target data service unit that has cached the query optimization data of the target sub-query statement, and a usage request is sent to the target data service unit.

[0052] The data service unit 2202 is further configured to, in response to receiving the usage request, reuse the query optimization data corresponding to the target information in the cache to execute the query of the target sub-query statement.

[0053] Because the system provides a global cache based on connection units, the data service unit caches the correspondence between the information of the subquery statements and the query optimization data based on the subquery statements issued by the coordination service unit. The subquery statements are generated based on the query optimization results of the query statements. Therefore, the generated subquery statements are standardized. Although the query statements are different, as long as the subplan functions in the query plan of the query statement are the same, the subplan corresponding to the subquery statement can be reused in queries of different query plans. This decouples the query optimization data from the coordination service unit. Furthermore, the coordination node provides a globally shared connection unit. Thus, based on the global reuse of connections at the coordination node, and using the information of the subquery statements as the search clue, the query optimization data cached by the data service unit can be reused among the coordination service units to execute optimized queries, thereby reducing redundancy, saving resources, and improving query efficiency.

[0054] See Figure 3a , Figure 3a A block diagram of a system according to another embodiment of this specification is shown. In this embodiment, the query optimization data includes a query tree and a query execution plan, and the information of the subquery statement is a hash value obtained by hashing the subquery statement.

[0055] The coordination node ( Figure 3a (CN represents the coordinating node) 210 includes one or more coordination service units ( Figure 3a In the text, CN Backend is used to represent the coordination service unit 2102, and the data node ( Figure 3a In Chinese, data nodes are represented by DN. 220 includes one or more data service units (DNs). Figure 3a (The data service unit is represented by DN Backend in Chinese) 2202.

[0056] The coordination service unit 2102 has a mapping relationship with the first cache area. The first cache area includes a first statement information cache area and a first plan cache area. The first statement information cache area ( Figure 3a The cache area (represented by "prepared stmtshash") is used to cache the correspondence between a first hash value and a first plan primary key. The first hash value includes the hash values ​​of sub-query statements in the caches of all data service units currently connected to the corresponding coordination service unit. The first plan cache area is used to cache the correspondence between a second plan primary key and a first plan value. The first plan primary key is the plan name determined based on the hash value of the query statement. The second plan primary key is the hash value determined based on the query tree generated by the query statement. The first plan value is the query execution plan of the target query statement generated on the corresponding coordination service unit.

[0057] The connection's cache area ( Figure 3a The connection unit (represented by 'c' and the cache area for the prepared stmt hash) is used to record the hash values ​​of the subquery statements corresponding to the cached query optimization data in the corresponding data service unit. Figure 3a The pooler is used to represent the connection unit. The coordination service unit 2102 and the agent ( Figure 3a The proxy pool (represented by "agent") has a mapping relationship with the proxy cache. The proxy cache (…) Figure 3aThe proxy cache (represented by "prepared stmts hash") is used to cache the hash values ​​of subquery statements cached by all data service units currently connected to the corresponding coordination service unit. It can be understood that before the coordination service unit requests a new connection, the proxy cache caches the hash values ​​of subquery statements cached in the previous connection state of the coordination service unit. Thus, when the coordination service unit requests a new connection, the proxy cache can be used as a reference to compare with the cache of the new connection, updating the coordination service unit's cache only for the parts that need to be synchronized.

[0058] The data service unit 2202 has a mapping relationship with the second cache area. The second cache area includes a second statement information cache area ( Figure 3a The second statement cache and the second plan cache are represented by the prepared stmt hash. Figure 3a (This cache area is illustrated as Plan Cache). The second statement information cache area is used to cache the correspondence between the hash value of the subquery statement and the query tree of the subquery statement. The second plan cache area is used to cache the correspondence between the third plan primary key, the fourth plan primary key, and the second plan value. The third plan primary key is the plan name determined based on the hash value of the subquery statement. The fourth plan primary key is the hash value determined based on the query tree of the subquery statement. The second plan value is the query execution plan generated based on the query tree of the subquery statement.

[0059] Next, let's take the example of a query statement being an SQL statement and a service unit being a service process. Figure 3a The architecture shown will be explained in more detail. For example... Figure 3a As shown, the overall architecture is divided into CN nodes and DN nodes, where:

[0060] CN Node: The coordination service unit is represented by a Backend service process, responsible for processing queries sent by clients and generating and executing the query execution plan for those queries. Each Session corresponds to one Backend service process. The first statement information cache is represented by preparedstmtshash, responsible for recording sub-SQL queries that have already been prepared for the request in the DN. The first plan cache is represented by PlanCache, responsible for caching the query execution plan for the target query generated on the CN. The connection unit is represented by a Pooler process, responsible for managing all connections between the CN and the DN.

[0061] Each Agent is a proxy for CNBackend in the connection unit, responsible for interacting with CN Backend.

[0062] Slots (extension slots) are used to record the connections currently established by the corresponding CNBackend. Each element slot[] ( Figure 3a (The slot for an element is indicated by 'c') corresponds to a connection, and each connection corresponds to a Backend on the DN. Each element records the connection information of the corresponding data service unit.

[0063] The connection buffer (as shown) Figure 3a The preparedstmthash (pointed to by c) is responsible for recording the sub-SQL information of the prepared request completed in the DNBackend corresponding to the connection.

[0064] The connpool is used to record connections released by an Agent, which can then be used by other Agents.

[0065] Agent's proxy cache ( Figure 3a The preparedstmtshash below the Agent indicates the agent cache area. It is used to synchronously record the preparedstmtshash information of the corresponding CNBackend, so that the Agent's cache can be used as the reference for updates. Only the parts of CNBackend are synchronized and updated, reducing the synchronization overhead between CNBackend and pooler and speeding up the synchronization.

[0066] DN Node: The data service unit is represented by the Backend service process, responsible for processing sub-SQL statements issued by CNBackend. The second statement information cache, preparedstmtcache, caches the prepared sub-SQL statements issued by CNBackend. The second plan cache, plancache, caches the local query execution plans generated by the sub-SQL statements issued by CNBackend.

[0067] It should be noted that in SQL databases, query optimization involves generating an optimized query execution plan for each SQL statement. To effectively reduce the performance overhead caused by query optimization, the system provided in the above embodiment caches reusable intermediate results, such as query trees and query execution plans. By employing a global cache and decoupling the cached query execution plans in the coordination service unit from the data service unit, a two-layer cache is implemented at the data node: the first layer caches the query tree, and the second layer caches the query execution plan. This decouples each layer of cache from the coordination service unit, enabling reuse among multiple service processes in the coordination node, improving reuse efficiency, reducing repetitive operations during SQL execution, and ultimately improving SQL execution efficiency.

[0068] based on Figure 3aThe system architecture shown, to further conserve resources and improve query efficiency, utilizes a doubly linked list for the CN / DN cache. For example, a global variable `PlanCacheHead` can be set to represent the head of the doubly linked list used to store query execution plans; and a global variable `max_plan_cache` can be set to represent the maximum number of cached plans. When the number of cached plans exceeds `max_plan_cache`, an LRU eviction algorithm is executed. Specifically, plans at the tail of the `PlanCacheHead` list are removed, and the corresponding plans in `plan_cache_hash` are cleared. When a new record is inserted into `plan_cache_hash` by the coordination service unit, the inserted record is inserted at the head of the `PlanCacheHead` doubly linked list. During execution plan access, if the required plan is found, the corresponding record is moved to the head of `PlanCacheHead`. Thus, in this embodiment, LRU control is used in the CN / DN to control memory resources, improving the hit rate of frequently used execution plans.

[0069] See Figure 3b , Figure 3b A flowchart of a query method according to an embodiment of this specification is shown. The method is applied to a system including a coordination node and data nodes. The coordination node includes a coordination service unit and a connection unit, the data node includes a data service unit, and the connection unit includes a connection that can be reused between the coordination service units. The method specifically includes:

[0070] Step 302: When the data service unit receives a preparation request carrying a subquery statement, it uses the subquery statement to generate query optimization data, and writes the query optimization data and the information of the subquery statement into the cache area in correspondence. The subquery statement is generated based on the query optimization result of the query statement.

[0071] Step 304: The coordination service unit establishes a connection with the data service unit based on the connection in the connection unit. When the target query statement is received, the coordination service unit determines the target subquery statement of the target query statement, uses the target information of the target subquery statement to find the target data service unit that has cached the query optimization data of the target subquery statement, and sends a usage request to the target data service unit.

[0072] Step 306: In response to receiving the usage request, the data service unit reuses the query optimization data corresponding to the target information in the cache to execute the query of the target subquery statement.

[0073] As can be seen, since this method provides a global cache based on connection units, the data service unit caches the correspondence between the information of the subquery statements and the query optimization data based on the subquery statements issued by the coordination service unit. The subquery statements are generated based on the query optimization results of the query statements. Therefore, the generated subquery statements are standardized. Although the query statements are different, as long as the subplan functions in the query plan of the query statement are the same, the subplan corresponding to the subquery statement can be reused in queries of different query plans. This decouples the query optimization data from the coordination service unit. Furthermore, the coordination node provides a globally shared connection unit. Thus, based on the global reuse of the connection at the coordination node, and using the information of the subquery statements as the search clue, the query optimization data cached by the data service unit can be reused among the coordination service units to execute optimized queries, thereby reducing redundancy, saving resources, and improving query efficiency.

[0074] See Figure 4 , Figure 4 A flowchart illustrating a query method applied to a coordination service unit according to an embodiment of this specification is shown. The coordination service unit is configured in a coordination node of a system, which also includes data nodes. The coordination node further includes connection units, and the data nodes include data service units. Each connection unit includes a connection that can be reused among the coordination service units, with one connection corresponding to one data service unit in the data node. The method specifically includes the following steps.

[0075] Step 402: Establish a connection with the data service unit based on the connection unit.

[0076] The data service unit caches the correspondence between information from subquery statements and query optimization data. The query optimization data is generated based on the subquery statements, and the subquery statements are generated based on the query optimization results of the query statements.

[0077] Taking SQL as an example, the method provided in this specification can abstract sub-plans in the SQL query execution plan into sub-SQL. The hash value of the sub-SQL and the correspondence between the query execution plan (i.e., the sub-plan) are cached on the data service node, decoupling the sub-plan from the coordination node. This allows for faster querying by pushing down sub-SQL instead of issuing sub-plans. Specifically, for example, the abstraction of sub-SQL includes: inputting SQL into a unified pre-defined interface to query the results of the optimization phase and generating sub-SQL. Since the generated sub-SQL is standardized, it enables the reuse of sub-SQL in the query execution plans of different SQL queries. For instance, if a sub-SQL is a full table scan of a specific table, and multiple SQL query execution plans include a sub-plan for a full table scan of that table, then this sub-plan can be reused by issuing the sub-SQL. It is understood that the pushed-down sub-SQL statement is completely independent and can be independently expanded in the DN. Any SQL query execution plan, as long as the generated sub-SQL is the same, can reference this sub-SQL, realizing the ability to reuse sub-plans in the query execution plans of SQL between coordination nodes on the coordination node. Furthermore, through the abstraction of SQL, subplans can be optimized individually.

[0078] Step 404: Upon receiving the target query statement, determine the target subquery statement of the target query statement.

[0079] It should be noted that the method provided in the embodiments of this specification is not limited in how the data service node obtains the correspondence between the information of the subquery statement and the query optimization data. For example, the correspondence between some known information of the subquery statement and the query optimization data can be imported in advance. Or, when the coordination service node receives a query statement for the first time, it can send the subquery statements abstracted from several sub-plans in the query execution plan of the query statement and their corresponding query optimization data to the data service node for caching, so as to facilitate subsequent global reuse.

[0080] Specifically, for example, the method further includes: when the coordination service unit receives the target query statement for the first time, determining the target subquery statement of the target query statement; sending a preparation request for the target subquery statement to the target data service unit, the preparation request carrying the target subquery statement, so that the target data service unit generates query optimization data using the target subquery statement according to the preparation request, and recording the first correspondence between the query optimization data and the target information in the cache area of ​​the target data service unit.

[0081] Taking the information of the subquery statement as the hash value, and the query optimization data including the query tree and query execution plan as an example: when the target query statement is received for the first time, determining the target subquery statement of the target query statement may include: when the target query statement is received for the first time, generating a query tree and a query execution plan using the target query statement; serializing the query tree of the target query statement and performing hash calculation to obtain the hash value of the query tree; converting the sub-plans in the query execution plan of the target query statement into the target subquery statement; using the plan name determined by the hash value of the target query statement and the hash value of the query tree as the primary key, using the query execution plan of the target query statement as the value corresponding to the primary key, and writing the correspondence between the primary key and the corresponding value into the cache area.

[0082] Accordingly, determining the subquery statement of the target query statement upon receiving the target query statement includes: upon receiving the target query statement again, using the plan name determined by the hash value of the target query statement and the hash value of the query tree of the target query statement as the primary key, querying the cache to see if there is a correspondence between the primary key and the corresponding value; if there is, obtaining the value corresponding to the primary key as the query execution plan of the target query statement; and determining the target subquery statement of the target query statement by calling the plan executor to execute the query execution plan of the target query statement.

[0083] For example, such as Figure 5a The diagram illustrates the signaling interaction process of the query method. Taking SQL as an example, when the coordination service unit receives an SQL query for the first time, it sends a preparation request to the data service unit, instructing the data service unit to write the query optimization data into the cache (i.e., the preparation phase). This process may include:

[0084] S5002: The coordination service unit sends the sub-SQL abstracted from the query execution plan of the SQL to the data service unit through the extended protocol.

[0085] S5004: The data service unit generates a query tree and sub-plan based on the sub-SQL, and records the primary key composed of the hash value and version number of the sub-SQL and the corresponding query tree in the local cache, that is... Figure 3a The preparedstmtcache example shows that the hash value and version number of the sub-SQL, as well as the hash value of the query tree, are recorded in the local cache as primary keys corresponding to the sub-plan. Figure 3a The plancache shown.

[0086] S5006: After the coordination service unit receives the message that the data service unit has successfully completed the cached process, it records the sub-SQL hash value and version number in the local first statement information cache area prepared stmts hash for subsequent execution of the reuse process.

[0087] Furthermore, after the coordination service unit commits its transaction, it releases the corresponding connection and returns it to the connection unit for management, making it available to other coordination service units that require it. The aforementioned process of preparing the newly inserted hash value and version number will be synchronized to the corresponding connection unit for management. Specifically, this includes:

[0088] S5008: The coordination service unit sends the updated information of the locally prepared_stmts_hash to the connection unit.

[0089] S5010: The connection unit updates the prepared_stmts_hash in the local proxy cache according to the update information.

[0090] S5012: Based on the update information, the connection unit generates a new record and inserts it into the connection's cache area by taking the hash value and version number of the sub-SQL contained in the update information, as well as the unique identifier of the coordination service unit corresponding to the agent.

[0091] The method provided in the embodiments of this specification is not limited in the way various requests, such as preparation requests, are sent. For example, the data service unit can process various messages in the extended protocol, such as P messages indicating preparation requests, B messages indicating binding information, E messages indicating query execution plans, and so on. Through these messages, the cached query tree and query execution plan in the data service unit can be reused. For example, in step S5002 above, the coordination service unit can record the query tree and query execution plan of the sub-SQL of the SQL into the cache of the data service unit by sending P messages.

[0092] Step 406: Using the target information of the target subquery statement, locate the target data service unit that has cached the query optimization data of the target subquery statement.

[0093] The search method is not limited. For example, since the coordination service unit's cache records information about the subquery statements of the query optimization data cached by the data service units it is connected to, and records the corresponding data service units, such as DN numbers, the target data service unit that has cached the query optimization data of the target subquery statement can be determined directly based on the information recorded in the DN cache. Alternatively, the coordination service unit can send a query request to the connection unit, which, based on the target information, searches for the target data service unit that has cached the query optimization data of the target subquery statement, and then returns the search result to the coordination service unit.

[0094] Step 408: Send a usage request to the target data service unit, so that the target data service unit responds to receiving the usage request by reusing the query optimization data corresponding to the target information in the cache to execute the query of the target subquery statement.

[0095] It should be noted that, since subquery statements may contain constants, subquery statements of the same pattern with different constant values ​​will generate different query execution plans, making it difficult to reuse the generated query execution plans in the cache. Therefore, the method provided in this specification, before the execution of the query execution plan of the target query statement by calling the plan executor, further includes: binding the parameters of the target query statement to the query plan of the target query statement. Correspondingly, sending a usage request to the target data service unit includes: sending a usage request to the target data service unit, the usage request carrying the target information and parameter binding information of the target subquery statement, enabling the target data service unit to, based on the usage request, use the target information to find the query tree from the cache, use the parameter binding information to parameterize the query tree to obtain a parameterized query tree, use the parameterized query tree to query the corresponding parameterized query execution plan, and execute the parameterized query execution plan.

[0096] In the above embodiments, subqueries containing constants are parameterized within the parsed query tree, and the parameterized query tree is then passed to the query optimizer for optimization, resulting in a parameterized query execution plan which is cached. This way, when a subquery containing constants with the same pattern accesses the database again, the query tree is parameterized again, the cached parameterized query execution plan is retrieved, and the parameterized execution plan is executed. This avoids the overhead of repeatedly performing query optimization on subqueries containing constants, thus improving execution efficiency.

[0097] For example, such as Figure 5aThe diagram illustrates the signaling interaction of the query method. The process of coordinating the service unit to execute the query execution plan of the target query statement and reusing the processing of cached sub-plans in the data service unit (i.e., the execution phase) may include:

[0098] S5014: When the same SQL statement is received to access the coordination service unit, the coordination service unit queries the query execution plan of the SQL statement cached by the coordination service unit based on the plan name provided by the client.

[0099] S5016: The coordination service unit binds parameters to the query execution plan of this SQL.

[0100] S5018: The coordination service unit calls the executor to execute the query execution plan of this SQL.

[0101] Since the subplans in the SQL query execution plan have been converted into subSQLs, and their query trees and execution plans are cached in the corresponding data service unit, the coordination service unit only needs to retrieve the subplan of each subSQL based on the cached plan name when executing it, thus omitting the query tree generation stage. Through the executor's execution, B information and E messages are sent to the data service unit.

[0102] S5020: Upon receiving message B, the data service unit queries the cache to find the cached query execution plan based on the plan name of the passed sub-SQL, and initializes the query execution plan using parameters.

[0103] S5022: Upon receiving message E, the data service unit executes the query execution plan and returns the query results to the coordination service unit.

[0104] Traditional solutions lack inter-process shared connections, and connections can only be reused for a single session. This method addresses the issue that traditional solutions lack such connections, as connections can only be shared within a single session. Each data service unit can be decoupled and reused among coordination service units. Based on a global cache implemented through connection units, the data service unit caches the correspondence between the sub-query statements issued by the coordination service unit and the query optimization data. The sub-query statements are generated based on the query optimization results of the given query statements; therefore, the generated sub-query statements are standardized. Although the query statements differ, as long as the sub-plans in the query plan of the query statement have the same function, the corresponding sub-plans can be reused in queries across different query plans. This decouples the query optimization data from the coordination service unit. Furthermore, the coordination node provides globally shared connection units. Based on the global reuse of connections at the coordination node, and using the information from the sub-query statements as a search clue, the query optimization data cached by the data service unit can be reused among coordination service units to execute optimized queries. This allows fewer data service units to serve the connections of more coordination service units. Moreover, through cache sharing, the problem of repeated resource allocation and release in connection reuse is solved, achieving the goals of reducing redundancy, saving resources, improving reuse efficiency, and improving query efficiency.

[0105] To further improve query efficiency, in one or more embodiments of this specification, each connection may be configured with a corresponding cache. The connection cache is used to record information about the subquery statements corresponding to the cached query-optimized data in the corresponding data service unit. Since the connection cache records information about the subquery statements corresponding to the cached query-optimized data in the corresponding data service unit, on the one hand, the coordination service unit can more quickly find the target data service unit that has cached the query-optimized data of the target subquery statement; on the other hand, the connection unit can accelerate the synchronization of the coordination service unit's cache with the cache of the data service units it connects to, reducing the synchronization overhead of the coordination service unit.

[0106] The step of using the target information of the target subquery to locate the target data service unit that has cached the query optimization data of the target subquery includes: using the target information of the target subquery and the information of the subquery recorded in the connected cache area to locate the target data service unit that has cached the query optimization data of the target subquery. Through this step, the coordination service unit can locate the target data service unit that has cached the query optimization data of the target subquery more quickly, thus improving query efficiency.

[0107] Accordingly, the first correspondence may further include first version information, which is used to distinguish sub-query statements with the same information. The method may further include: in response to the target data service unit completing the preparation request, recording the target information and the first version information as the plan name of the target sub-query statement in the cache of the coordination service unit; sending a first update request to the connection unit, the first update request carrying the target information and the first version information, so that the connection unit, according to the first update request, records the second correspondence between the unique identifier of the coordination service unit, the target information, and the first version information in the cache of the connection corresponding to the target data service unit. Receiving a second update request from the connection unit. Through this step, when the cache of the coordination service unit is updated, the cache of the connection can be updated in a timely manner, so that the information updated in the cache of the connection can be directly synchronized with the cache of the data service unit based on the records in the cache of the connection when the connection is reused by other coordination service units, reducing synchronization overhead.

[0108] Accordingly, the second update request carries second update information, which includes information about the sub-query statements that need to be updated in the cache of all connections of the coordination service unit, as well as second version information corresponding to this key information. When the unique identifier of the coordination service unit exists in the second correspondence, the second version information is taken according to the first version information; otherwise, it is taken as the version information to be verified. The second update information carried in the second update request is then updated in the cache of the coordination service unit. Through this step, when the connection is reused by another coordination service unit, the information in the connection's cache can be directly synchronized with the cache of the data service unit based on the records in the connection's cache, reducing synchronization overhead.

[0109] In addition, in the above embodiments, during the synchronization process of the cache of the coordination service unit and the cache of the connection, based on the differences between the two, a unique identifier of the coordination service unit and the corresponding version information are left for the record of the difference to indicate whether the subquery statement needs to be checked for consistency in the subsequent process. Thus, on the basis of achieving cache synchronization, it is also possible to distinguish between reusable subquery statements and subquery statements that need to be checked for consistency.

[0110] To further improve synchronization efficiency, in one or more embodiments of this specification, the connection unit includes a proxy corresponding to the coordination service unit. The proxy's cache is used to record information about the sub-query statements and corresponding version information recorded in the coordination service unit's cache. The second update information is determined by the connection unit by comparing the information in the cache of the proxy corresponding to the coordination service unit with the information in the caches of all connections to the coordination service unit, and based on the comparison result, the information that needs to be updated in the coordination service unit's cache.

[0111] In the above embodiments, the records of the coordination service unit's cache are recorded based on the cache of the agent in the connection unit, so that the agent's cache can be directly used as the reference for updates, and only the updated part is synchronized, reducing the synchronization overhead between the coordination service unit and the connection unit.

[0112] Below, in conjunction with Figure 5b The schematic diagram of the synchronous cache shown above, using the information of the subquery statement as the hash value and the query statement as SQL, serves as an example to illustrate the above embodiment:

[0113] like Figure 5b As shown, the prepared_stmt cache of connection c records the hash values ​​of the sub-SQL corresponding to all query trees cached in the data service node of the current connection. Specifically, in each record, the primary key includes a number of bits, such as a 32-bit hash value, generated after hash calculation of the sub-SQL, the unique identifier of the coordination service unit of the current connection, and the version number. The version number starts from 1.

[0114] The agent's cache is used to record the hash values ​​of the subquery statements cached by the corresponding coordination service unit. For example... Figure 5b The `prepared_stmts_hash` attribute below the Agent indicates the proxy cache. The proxy cache assists in synchronizing the coordination service unit's cache with the connection's cache. Specifically, in the records of the proxy cache, the primary key is a hash value (e.g., a 32-bit hash value) generated after hashing the sub-SQL, along with a version number. The version number starts from 0, where 0 indicates that the sub-SQL has been issued in another coordination service unit but has not yet been validated in the process of this coordination service unit. Validation refers to verifying whether the sub-SQL to be executed by this process is consistent with the sub-SQL in the query tree cached by the data service unit. Additionally, each record in the proxy cache also has a validation flag. Figure 5bThe check flag is identified by `dn_list`, and `max_dn_num` represents the maximum data service unit (DSU) number. Each bit indicates whether the check flag has been cached in the corresponding coordination service unit. This check flag is a boolean value; a true value indicates that the check flag has been cached, and a false value indicates that the check flag has not been cached.

[0115] The first statement information cache of the coordination service unit, such as Figure 5b The `prepared_stmts_hash` field below the CN Backend, as shown, records the hash values ​​of cached subquery statements in all data service units currently connected to by the coordination service unit. The first statement information cache is consistent with the agent cache and is used for synchronization with the agent.

[0116] The second statement information cache area of ​​the data service unit, such as Figure 5b The prepared_stmt_hash section below the DN Backend is used to record the hash value of the subquery statement that completes the preparation request. Specifically, each record in the second statement information cache includes: primary key (taken as the hash value of the subSQL), max_version (taken as the current maximum version number), version (version number starts from 1), and sql (query tree of the subSQL corresponding to this version). The version and sql are stored one-to-one.

[0117] It should be noted that when the coordination service unit reuses a sub-SQL statement for the first time on a connection, it needs to verify whether the corresponding sub-SQL in the query execution plan is consistent with the query tree cached in the data service unit by checking the primary key of the sub-SQL. Since the primary key is generated by hashing the sub-SQL, it cannot be guaranteed that the same hash value represents the same sub-SQL statement. Therefore, the coordination service unit needs to verify consistency during the first execution. To solve this problem, based on the above embodiment, the coordination service unit and the connection unit synchronize caching through the following process, specifically, as follows: Figure 5c The signaling interaction diagram of the query method shown includes the following synchronous caching process:

[0118] S5024: The connection unit traverses the buffer of each connection currently connected to the coordination service unit.

[0119] Of course, before the connection unit traverses the buffer of each connection currently connected to the coordination service unit, the process also includes the step of the coordination service unit requesting a connection from the connection unit. Only after the connection unit obtains the required connection from the coordination service unit can it proceed to S5024 to begin the process of synchronizing the buffer.

[0120] S5026: For all records whose names contain the unique identifier of the coordination service unit, take the hash value and version number of the sub-SQL as the primary key and insert it into the agent cache of the agent of the coordination service unit, and mark dn_list[slot] as true.

[0121] S5028: For all records whose names do not contain the unique identifier of the coordination service unit, take the hash value of the sub-SQL and the version number "0" as the primary key and insert it into the agent cache of the agent of the coordination service unit, and mark dn_list[slot] as true.

[0122] After all the above proxy caches have been updated, the obtained connection is returned to the coordination service unit, enabling the coordination service unit to use the corresponding connection and synchronize the local cache according to the following S5030-S5032.

[0123] S5030: The connection unit carries the hash value and version number of the sub-SQL in the updated record in the proxy cache in the update request and sends it to the corresponding coordination service unit.

[0124] S5032: The coordination service unit receives an update request and synchronizes the update information to the local cache, that is, updates the local preparedstmtshash.

[0125] Based on the above-described synchronous caching method, this embodiment of the specification also provides a process for verifying the consistency of subquery statements. Specifically, the step of using the target information of the target subquery statement to locate the target data service unit that has cached the query optimization data of the target subquery statement includes:

[0126] If the plan name of the target subquery statement exists in the cache of the coordination service unit, find the target data service unit corresponding to any connection that has cached the target information;

[0127] If the plan name of the target subquery does not exist in the cache of the coordination service unit, determine whether the version information corresponding to the target information is the version information to be verified.

[0128] If so, a verification request is sent to the target data service unit corresponding to any connection that has cached the target information. The verification request carries the target subquery statement and the target information, so that the target data service unit can use the query optimization data corresponding to the target information to verify whether it is the same statement as the target subquery statement.

[0129] Receive the verification result returned by the target data service unit;

[0130] If the verification results indicate that the statements are identical, then the target data service unit has cached the query optimization data of the target subquery statement.

[0131] In the above embodiment, the version information to be verified left during the synchronization caching process is used to send a verification request to the target data service unit, so that the data service unit can verify whether the sub-stored query statement of the query execution plan to be reused is consistent with the locally cached sub-query statement.

[0132] Accordingly, based on the verification results returned by the above embodiments, in the method provided by the embodiments of this specification, the step of determining that the target data service unit has cached the query optimization data of the target subquery statement when it is determined that the statements are the same based on the verification results includes:

[0133] The version information carried in the verification result is compared with the latest first version information of the target sub-query statement recorded in the cache of the coordination service unit. Wherein, if the target data service unit is determined to be the same statement, the version information carried in the verification result is the first version information of the same statement recorded in the target data service unit. If the target data service unit is determined to be a different statement, the version information carried in the verification result is the first version information updated based on the first version information.

[0134] If the latest first version information is not updated compared to the version information carried by the verification result, it is determined that the target data service unit has cached the query optimization data of the target subquery statement, and the target information and the version information carried by the verification result are recorded as the plan name of the target subquery statement in the cache area of ​​the coordination service unit.

[0135] Otherwise, proceed to the step of sending a preparation request for the target subquery statement to the target data service unit, so that the target data service unit caches the query optimization data of the target subquery statement according to the preparation request.

[0136] In the above embodiments, it is further determined whether the query statement for reusing the query optimization data has added a new statement based on the latest version information, so as to update the cache accordingly, so that the data service unit can cache more query optimization data for reuse.

[0137] In one or more embodiments of this specification, based on the verification results returned by the above embodiments, a cached flag is also used to determine whether the data can be reused. Specifically, the second update information also includes a cached flag corresponding to the information of the subquery statement, the cached flag indicating that the data service node has cached the corresponding query optimization data. Accordingly, when the plan name of the target subquery statement exists in the cache area of ​​the coordination service unit, finding the target data service unit corresponding to any connection that has cached the target information includes:

[0138] If the plan name of the target subquery exists in the cache of the coordination service unit, determine whether the target information has a corresponding cached tag;

[0139] If so, then find the target data service unit corresponding to any connection that caches the target information;

[0140] The method further includes:

[0141] If the target information does not have a corresponding cached tag, then proceed to the step of sending a preparation request for the target subquery statement to the data service unit, so that the target data service unit caches the query optimization data of the target subquery statement according to the preparation request;

[0142] Accordingly, sending a verification request to the target data service unit corresponding to any connection that has cached the target information includes:

[0143] Determine whether the target information has a corresponding cached tag;

[0144] If so, a verification request is sent to the target data service unit corresponding to any connection that has cached the target information;

[0145] The method further includes:

[0146] If the target information does not have a corresponding cached flag, then proceed to the step of sending a preparation request for the target subquery statement to the data service unit, so that the target data service unit caches the query optimization data of the target subquery statement according to the preparation request.

[0147] In the above embodiments, for subqueries with cached flags, the corresponding query optimization data can be reused correctly, while for subqueries without cached flags, a preparation request is issued, and the cache of the data service unit is updated accordingly, so that more query optimization data is added to the data service unit for reuse.

[0148] It is understandable that for subquery statements that have cached query optimization data in the data service node, there is a cached mark in the cache of the coordination service unit. For cases where there is no cached mark (such as the value of dn_list being false or the absence of dn_list information), it means that the data service node has not cached the corresponding query optimization data, and a preparation request needs to be sent to enable the data service unit to cache it.

[0149] Below, using the information from the subquery statement as the hash value and the query statement as SQL as an example, we will combine... Figure 5c The signaling interaction diagram of the query method shown illustrates the verification process of the above embodiments. Specifically, it includes:

[0150] S5034: The coordination service unit checks whether the corresponding sub-SQL in the query execution plan has been named (i.e., whether the record corresponding to the hash value of the sub-SQL contains the plan name). If it is not named, then execute S5038.

[0151] S5036: If named, query the first statement information cache using this plan name. If the plan name exists and dn_list[current_dn] (current_dn represents the data service unit currently connected) is true, it means that the corresponding data service unit has named the sub-SQL and verified it, and the reuse process can be executed directly. If the connection bit corresponding to dn_list[current_dn] is false, it means that the coordination service unit has not yet completed the verification work, and it is necessary to proceed to S5038.

[0152] S5038: The coordination service unit uses the hash value obtained by hashing the sub-SQL, initially version 0, as the primary key to query the local first statement information cache prepared_stmts_hash.

[0153] S5040: If it exists, check if dn_list[current_dn] is true. If it is true, it means that the query execution plan of the sub-SQL has been cached in the corresponding data service unit. Proceed to S5046 to determine whether the two are the same statement.

[0154] S5042: If it does not exist, execute the steps of the query execution plan for the newly added sub-SQL with the hash value of the sub-SQL and version number 1 as the primary key.

[0155] S5044: The coordination service unit sends a check_valid(hashvalue) message to the corresponding data service unit.

[0156] S5046: The data service unit queries the local second statement information cache based on the received hash value to verify whether this sub-SQL is consistent with a certain version in the cache.

[0157] S5048: If consistent, return the verification result carrying the corresponding version number.

[0158] S5050: If inconsistent, return the verification result with the current maximum version number +1.

[0159] S5052: After receiving the verification result, if the version number carried in the verification result is less than the current maximum version number recorded by the coordination service unit, the version number in the verification result is recorded. The hash value of the sub-SQL and the recorded version number are combined to form the primary key and recorded in the cache. Since the data access unit has already cached the query tree and query execution plan, the stage of generating the query tree is omitted when the sub-SQL is executed.

[0160] S5054: If the verification result is greater than the maximum version number, the new version number returned by the verification result and the hash value of the sub-SQL are used to form the primary key, and the steps of the query execution plan of the newly added sub-SQL are cached.

[0161] The steps for adding a sub-SQL query execution plan are as follows: Figure 5a The preparation phase shown is briefly summarized below:

[0162] The coordination service unit sends a preparation request for the above sub-SQL to the data service unit;

[0163] The data service unit receives the sub-SQL, generates a query tree, and uses the hash value and version number of the sub-SQL as the primary key, and caches the query tree as the value.

[0164] After the coordination service unit receives the successful completion of the preparation request, it records the sub-SQL hash value and version number in the local first statement information cache for subsequent execution reuse process.

[0165] After the transaction of the coordination service unit is committed, the connection to the data service unit must be released and the connection returned to the connection unit for management so that it can be provided to another coordination service unit that needs it. The newly inserted sub-SQL hash value and version number should also be synchronized to the cache area of ​​the corresponding connection in the connection unit.

[0166] For example, each connection corresponds to a data service unit. By reusing connections, the query tree cached in the data service unit can be reused among the coordination service units.

[0167] Understandably, if the query execution plan in the data service unit was prepared some time ago, the meaning of the current query may differ. For example, when a queried table is deleted and another table with the same name is created, and the table may have different column names and types, or when columns used in the queried table are deleted, or other operations affecting the metadata of the queried table occur, the coordinating node will re-execute the query statement preparation work, performing parsing, analysis, and verification operations. Accordingly, a new query tree and a new query execution plan will be generated in the coordinating node. This ensures that the abstract subquery statements in the query execution plan are generated with the latest queried table information, and the issued subquery statements will be different from the original. Consequently, the query tree prepared in the data node will not be selected, and a new query tree and a new execution plan will be generated in the data node to ensure the correctness of the query.

[0168] The following is in conjunction with the appendix Figure 6 Taking the query execution plans of two SQL queries as an example, the effectiveness of the plan reuse method provided in the embodiments of this specification will be further explained. For example: Figure 6 As shown, there are two SQL query execution plans in the coordination service unit. The query execution plan on the left is referred to as execution plan A, and the one on the right is referred to as execution plan F. The part of execution plan A that accesses C is the same as the part of execution plan F that accesses C. According to the method provided in the embodiments of this specification, C, after abstraction, becomes a subquery statement and is sent to the data nodes. When A is executed for the first time, the query tree and execution plan are cached. When F executes C again, due to the use of two-layer caching technology, all the information used in the execution phase is cached, so no further query optimization is needed, and it can be executed directly, thus realizing the ability to reuse sub-plans in the execution plan.

[0169] Corresponding to the above method embodiments, this specification also provides embodiments of a query device configured in a coordination service unit. Figure 7 A schematic diagram of a query device configured in a coordination service unit according to an embodiment of this specification is shown. Figure 7 As shown, the device includes:

[0170] The connection module 702 is configured to establish a connection based on the connection and data service unit in the connection unit.

[0171] The data service unit caches the correspondence between information of subquery statements and query optimization data. The query optimization data is generated based on the subquery statements, and the subquery statements are generated based on the query optimization results of the query statements.

[0172] The statement determination module 704 is configured to determine the target subquery statement of the target query statement when the target query statement is received;

[0173] The lookup cache module 706 is configured to use the target information of the target subquery statement to find the target data service unit that has cached the query optimization data of the target subquery statement;

[0174] The reuse query module 708 is configured to send a usage request to the target data service unit, so that the target data service unit, upon receiving the usage request, reuses the query optimization data corresponding to the target information in the cache to execute the query of the target sub-query statement.

[0175] The above is an illustrative scheme of a query device configured in a coordination service unit according to this embodiment. It should be noted that the technical solution of the query device configured in the coordination service unit and the technical solution of the query method applied to the coordination service unit described above belong to the same concept. For details not described in detail in the technical solution of the query device configured in the coordination service unit, please refer to the description of the technical solution of the query method applied to the coordination service unit described above.

[0176] See Figure 8 , Figure 8 A flowchart illustrating a query method applied to a data service unit according to an embodiment of this specification is shown. The data service unit is configured on a data node of a system, which also includes a coordination node. The coordination node further includes connection units, each comprising a connection reusable between coordinated service units. One connection corresponds to one data service unit in the data node. The method specifically includes the following steps.

[0177] Step 802: When a preparation request carrying a subquery statement is received, query optimization data is generated using the subquery statement, and the query optimization data is written into the cache corresponding to the information of the subquery statement.

[0178] The subquery statement is generated based on the query optimization results of the query statement.

[0179] Step 804: Receive the usage request sent by the coordination service unit.

[0180] The usage request is issued by the coordination service unit to the target data service unit when it receives the target query statement, determines the target subquery statement of the target query statement, and uses the target information of the target subquery statement to find the target data service unit that has cached the query optimization data of the target subquery statement.

[0181] Step 806: Based on the usage request, reuse the query optimization data corresponding to the target information in the cache to execute the query of the target subquery statement.

[0182] Because the system of this method provides a global cache based on connection units, the data service unit caches the correspondence between the information of the subquery statements and the query optimization data based on the subquery statements issued by the coordination service unit. The subquery statements are generated based on the query optimization results of the query statements. Therefore, the generated subquery statements are standardized. Although the query statements are different, as long as the subplan functions in the query plan of the query statement are the same, the subplan corresponding to the subquery statement can be reused in queries of different query plans. This decouples the query optimization data from the coordination service unit. Furthermore, the coordination node provides a globally shared connection unit. Thus, based on the global reuse of the connection at the coordination node, and using the information of the subquery statements as the search clue, the query optimization data cached by the data service unit can be reused among the coordination service units to execute optimized queries, thereby reducing redundancy, saving resources, and improving query efficiency.

[0183] The above is an illustrative scheme of a query method applied to a data service unit according to this embodiment. It should be noted that the technical solution of this query method applied to a data service unit belongs to the same concept as the technical solution of the query method applied to a coordination service unit described above. For details not described in detail in the technical solution of the query method applied to a data service unit, please refer to the description of the technical solution of the query method applied to a coordination service unit described above.

[0184] Corresponding to the above method embodiments, this specification also provides embodiments of a query device configured in a data service unit. Figure 9 This specification illustrates a schematic diagram of a query device configured in a data service unit according to one embodiment. Figure 9 As shown, the device includes:

[0185] The preparation module 902 is configured to, upon receiving a preparation request carrying a subquery statement, generate query optimization data using the subquery statement, and write the query optimization data and the information of the subquery statement into a cache, wherein the subquery statement is generated based on the query optimization result of the query statement;

[0186] The request receiving module 904 is configured to receive a usage request sent by the coordination service unit. The usage request is sent by the coordination service unit to the target data service unit when it receives the target query statement, determines the target subquery statement of the target query statement, and uses the target information of the target subquery statement to find the target data service unit that has cached the query optimization data of the target subquery statement.

[0187] The reuse execution module 906 is configured to reuse the query optimization data corresponding to the target information in the cache according to the usage request, in order to execute the query of the target subquery statement.

[0188] The above is an illustrative scheme of a query device configured in a data service unit according to this embodiment. It should be noted that the technical solution of the query device configured in the data service unit and the technical solution of the query method configured in the data service unit described above belong to the same concept. For details not described in detail in the technical solution of the query device configured in the data service unit, please refer to the description of the technical solution of the query method configured in the data service unit described above.

[0189] Corresponding to the above-described query method embodiments, this specification also provides system embodiments for implementing the query methods as described in any of the above embodiments. Figure 10 A schematic diagram of the structure of a system provided in one embodiment of this specification is shown. Figure 10 As shown, the system includes: a coordination node 1010 and a data node 1020.

[0190] The above is an illustrative scheme of a system according to this embodiment. It should be noted that the technical solution of this system and the technical solution of the above query method belong to the same concept. For details not described in detail in the technical solution of the system, please refer to the description of the technical solution of the above query method.

[0191] Figure 11 A structural block diagram of a computing device 1100 according to one embodiment of this specification is shown. The components of the computing device 1100 include, but are not limited to, a memory 1110 and a processor 1120. The processor 1120 is connected to the memory 1110 via a bus 1130, and a database 1150 is used to store data.

[0192] The computing device 1100 also includes an access device 1140, which enables the computing device 1100 to communicate via one or more networks 1160. Examples of these networks include a Public Switched Telephone Network (PSTN), a Local Area Network (LAN), a Wide Area Network (WAN), a Personal Area Network (PAN), or a combination of communication networks such as the Internet. The access device 1140 may include one or more of any type of wired or wireless network interface (e.g., a Network Interface Card (NIC)), such as an IEEE 802.11 Wireless Local Area Network (WLAN) interface, a Wi-MAX interface, an Ethernet interface, a Universal Serial Bus (USB) interface, a cellular network interface, a Bluetooth interface, a Near Field Communication (NFC) interface, and so on.

[0193] In one embodiment of this specification, the aforementioned components of the computing device 1100 and Figure 11 Other components, not shown, can also be connected to each other, for example, via a bus. It should be understood that... Figure 11 The block diagram of the computing device shown is for illustrative purposes only and is not intended to limit the scope of this specification. Those skilled in the art can add or replace other components as needed.

[0194] The computing device 1100 can be any type of stationary or mobile computing device, including mobile computers or mobile computing devices (e.g., tablet computers, personal digital assistants, laptop computers, notebook computers, netbooks, etc.), mobile phones (e.g., smartphones), wearable computing devices (e.g., smartwatches, smart glasses, etc.) or other types of mobile devices, or stationary computing devices such as desktop computers or PCs. The computing device 1100 can also be a mobile or stationary server.

[0195] The processor 1120 is configured to execute the following computer-executable instructions, which, when executed by the processor, implement the steps of the above-described query method.

[0196] The above is an illustrative scheme of a computing device according to this embodiment. It should be noted that the technical solution of this computing device and the technical solution of the above-described query method belong to the same concept. For details not described in detail in the technical solution of the computing device, please refer to the description of the technical solution of the above-described query method.

[0197] An embodiment of this specification also provides a computer-readable storage medium storing computer-executable instructions that, when executed by a processor, implement the steps of the above-described query method.

[0198] The above is an illustrative scheme of a computer-readable storage medium according to this embodiment. It should be noted that the technical solution of this storage medium and the technical solution of the above-described query method belong to the same concept, and all details not described in detail in the technical solution of the storage medium can be found in the description of the technical solution of the above-described query method.

[0199] An embodiment of this specification also provides a computer program, wherein when the computer program is executed in a computer, it causes the computer to perform the steps of the above-described query method.

[0200] The above is an illustrative scheme of a computer program according to this embodiment. It should be noted that the technical solution of this computer program and the technical solution of the above-described query method belong to the same concept. For details not described in detail in the technical solution of the computer program, please refer to the description of the technical solution of the above-described query method.

[0201] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0202] The computer instructions include computer program code, which may be in the form of source code, object code, executable file, or certain intermediate forms. The computer-readable medium may include any entity or device capable of carrying the computer program code, recording media, USB flash drives, portable hard drives, magnetic disks, optical disks, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc. It should be noted that the content included in the computer-readable medium may be appropriately added to or subtracted according to the requirements of legislation and patent practice in the jurisdiction. For example, in some jurisdictions, according to legislation and patent practice, computer-readable media may not include electrical carrier signals and telecommunication signals.

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

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

[0205] The preferred embodiments disclosed above are merely illustrative of this specification. The optional embodiments do not exhaustively describe all details, nor do they limit the invention to the specific implementations described. Clearly, many modifications and variations can be made based on the embodiments described herein. These embodiments are selected and specifically described in this specification to better explain the principles and practical applications of the embodiments, thereby enabling those skilled in the art to better understand and utilize this specification. This specification is limited only by the claims and their full scope and equivalents.

Claims

1. A query method applied to a system including a coordination node and data nodes, wherein the coordination node includes a coordination service unit and a connection unit, the data node includes a data service unit, and the connection unit includes a connection reusable among the coordination service units, the method comprising: When the data service unit receives a preparation request carrying a subquery statement, it uses the subquery statement to generate query optimization data, and writes the query optimization data and the information of the subquery statement into the cache area. The subquery statement is generated based on the query optimization result of the query statement. The coordination service unit establishes a connection with the data service unit based on the connection in the connection unit. When it receives the target query statement, it determines the target subquery statement of the target query statement, uses the target information of the target subquery statement to find the target data service unit that has cached the query optimization data of the target subquery statement, and sends a usage request to the target data service unit. In response to receiving the usage request, the target data service unit reuses the query optimization data corresponding to the target information in the cache to execute the query of the target sub-query statement.

2. The method according to claim 1, wherein the query optimization data includes a query tree and a query execution plan, and the information of the subquery statement is the hash value obtained by hash calculation of the subquery statement; The coordination node includes one or more coordination service units, and the data node includes one or more data service units; The coordination service unit has a mapping relationship with the first cache area. The first cache area includes a first statement information cache area and a first plan cache area. The first statement information cache area is used to cache the correspondence between a first hash value and a first plan primary key. The first hash value includes the hash values ​​of sub-query statements in the caches of all data service units currently connected to the corresponding coordination service unit. The first plan cache area is used to cache the correspondence between a second plan primary key and a first plan value. The first plan primary key is the plan name determined based on the hash value of the query statement. The second plan primary key is the hash value determined based on the query tree generated by the query statement. The first plan value is the query execution plan of the target query statement generated on the corresponding coordination service unit. The connection cache is used to record the hash values ​​of the subquery statements corresponding to the cached query optimization data in the corresponding data service unit. The coordination service unit has a mapping relationship with the agent, and the agent has a mapping relationship with the agent cache. The agent cache is used to cache the hash values ​​of the subquery statements cached by all data service units currently connected to the corresponding coordination service unit. The data service unit has a mapping relationship with the second cache area, which includes a second statement information cache area and a second plan cache area. The second statement information cache area is used to cache the correspondence between the hash value of the subquery statement and the query tree of the subquery statement. The second plan cache area is used to cache the correspondence between the third plan primary key, the fourth plan primary key and the second plan value. The third plan primary key is the plan name determined based on the hash value of the subquery statement, the fourth plan primary key is the hash value determined based on the query tree of the subquery statement, and the second plan value is the query execution plan generated based on the query tree of the subquery statement.

3. The method according to claim 1, further comprising: Upon first receiving the target query statement, the coordination service unit determines the target subquery statement of the target query statement; A preparation request for the target sub-query statement is sent to the target data service unit. The preparation request carries the target sub-query statement, so that the target data service unit generates query optimization data using the target sub-query statement according to the preparation request, and records the first correspondence between the query optimization data and the target information in the cache area of ​​the target data service unit.

4. The method according to claim 3, wherein when the coordination service unit receives the target query statement for the first time, it determines the target subquery statement of the target query statement, including: When the coordination service unit receives the target query statement for the first time, it uses the target query statement to generate a query tree and a query execution plan for the target query statement. The query tree of the target query statement is serialized and then hashed to obtain the hash value of the query tree; Convert the sub-plan in the query execution plan of the target query statement into the target sub-query statement; The plan name determined by the hash value of the target query statement and the hash value of the query tree are used as the primary key, the query execution plan of the target query statement is used as the value corresponding to the primary key, and the correspondence between the primary key and the corresponding value is written into the cache area. Accordingly, when the coordination service unit receives the target query statement, it determines the subquery statements of the target query statement, including: When the coordination service unit receives the target query statement again, it uses the plan name determined by the hash value of the target query statement and the hash value of the query tree of the target query statement as the primary key, and queries the cache to see if there is a correspondence between the primary key and the corresponding value. If it exists, obtain the value corresponding to the primary key as the query execution plan for the target query statement; The target subquery statement is determined by executing the query execution plan of the target query statement through the plan executor.

5. The method according to claim 4, further comprising, before invoking the plan executor to execute the query execution plan of the target query statement, the method includes: The coordination service unit binds the parameters of the target query statement to the query plan of the target query statement; Accordingly, sending a usage request to the target data service unit includes: The coordination service unit sends a usage request to the target data service unit. The usage request carries the target information and parameter binding information of the target sub-query statement. The target data service unit then uses the target information to find the query tree from the cache according to the usage request, parameterizes the query tree based on the parameter binding information to obtain a parameterized query tree, uses the parameterized query tree to find the corresponding parameterized query execution plan, and executes the parameterized query execution plan.

6. The method according to claim 3, wherein the connected cache area is used to record information of the subquery statements corresponding to the cached query optimization data in the corresponding data service unit; The step of using the target information of the target subquery to find the target data service unit that has cached the query optimization data of the target subquery includes: The coordination service unit uses the target information of the target subquery statement and the information of the subquery statement recorded in the connected cache area to find the target data service unit that has cached the query optimization data of the target subquery statement. The first correspondence also includes first version information, which is used to distinguish subquery statements with the same information. The method further includes: In response to the target data service unit completing the preparation request, the coordination service unit records the target information and the first version information as the plan name of the target sub-query statement in the cache area of ​​the coordination service unit. A first update request is sent to the connection unit. The first update request carries the target information and the first version information. The connection unit records the second correspondence between the unique identifier of the coordination service unit, the target information and the first version information in the cache area of ​​the connection corresponding to the target data service unit according to the first update request. Receive a second update request from the connection unit; The second update request carries second update information, which includes information about the subquery statement that needs to be updated in the cache of the coordination service unit in the cache of all connections of the coordination service unit, as well as the second version information corresponding to the information of the subquery statement. When the unique identifier of the coordination service unit exists in the second correspondence, the second version information is taken according to the first version information; otherwise, it is taken as the version information to be checked. The second update information carried in the second update request is updated in the cache of the coordination service unit.

7. The method according to claim 6, wherein the connection unit includes a proxy corresponding to the coordination service unit, and the cache of the proxy is used to record information of the sub-query statements recorded in the cache of the coordination service unit and the corresponding version information; The second update information is determined by the connection unit by comparing the information in the cache of the agent corresponding to the coordination service unit with the information in the cache of all connections of the coordination service unit, and based on the comparison result, the information that the cache of the coordination service unit needs to be updated.

8. The method according to claim 6, wherein the step of using the target information of the target subquery statement to find the target data service unit that has cached the query optimization data of the target subquery statement includes: If the plan name of the target sub-query statement exists in the cache of the coordination service unit, the coordination service unit finds the target data service unit corresponding to any connection that has cached the target information. If the plan name of the target subquery does not exist in the cache of the coordination service unit, determine whether the version information corresponding to the target information is the version information to be verified. If so, a verification request is sent to the target data service unit corresponding to any connection that has cached the target information. The verification request carries the target subquery statement and the target information, so that the target data service unit can use the query optimization data corresponding to the target information to verify whether it is the same statement as the target subquery statement. Receive the verification result returned by the target data service unit; If the verification results indicate that the statements are identical, then the target data service unit has cached the query optimization data of the target subquery statement.

9. The method according to claim 8, wherein determining that the target data service unit has cached the query optimization data of the target subquery statement when it is determined that the statements are the same based on the verification result includes: The coordination service unit compares the version information carried in the verification result with the latest first version information of the target sub-query statement recorded in the cache of the coordination service unit. Wherein, if the target data service unit determines that the statement is the same, the version information carried in the verification result is the first version information of the same statement recorded in the target data service unit. If the target data service unit determines that the statement is different, the version information carried in the verification result is the first version information updated based on the first version information. If the latest first version information is not updated compared to the version information carried by the verification result, it is determined that the target data service unit has cached the query optimization data of the target subquery statement, and the target information and the version information carried by the verification result are recorded as the plan name of the target subquery statement in the cache area of ​​the coordination service unit. Otherwise, proceed to the step of sending a preparation request for the target subquery statement to the target data service unit, so that the target data service unit caches the query optimization data of the target subquery statement according to the preparation request.

10. The method according to claim 8, wherein the second update information further includes a cached flag corresponding to the information of the subquery statement, the cached flag being used to indicate that the data service node has cached the corresponding query optimization data; When the plan name of the target sub-query statement exists in the cache of the coordination service unit, finding the target data service unit corresponding to any connection that caches the target information includes: If the plan name of the target subquery exists in the cache of the coordination service unit, the coordination service unit determines whether the target information has a corresponding cached tag. If so, then find the target data service unit corresponding to any connection that caches the target information; The method further includes: If the target information does not have a corresponding cached tag, then proceed to the step of sending a preparation request for the target subquery statement to the data service unit, so that the target data service unit caches the query optimization data of the target subquery statement according to the preparation request; Accordingly, sending a verification request to the target data service unit corresponding to any connection that has cached the target information includes: The coordination service unit determines whether the target information has a corresponding cached tag; If so, a verification request is sent to the target data service unit corresponding to any connection that has cached the target information; The method further includes: If the target information does not have a corresponding cached flag, then proceed to the step of sending a preparation request for the target subquery statement to the data service unit, so that the target data service unit caches the query optimization data of the target subquery statement according to the preparation request.

11. A query method applied to a coordination service unit, the coordination service unit being configured in a coordination node of a system, the system further comprising a data node, the coordination node further comprising a connection unit, the data node comprising a data service unit, the connection unit comprising a connection reusable between coordination service units, one connection corresponding to one data service unit in the data node, the method comprising: A connection is established between the connection unit and the data service unit, wherein the data service unit caches the correspondence between the information of the subquery statement and the query optimization data, the query optimization data is generated based on the subquery statement, and the subquery statement is generated based on the query optimization result of the query statement; Upon receiving the target query statement, determine the target subquery statement of the target query statement; Using the target information of the target subquery statement, locate the target data service unit that has cached the query optimization data of the target subquery statement; A usage request is sent to the target data service unit, causing the target data service unit to respond to the received usage request by reusing the query optimization data corresponding to the target information in the cache to execute the query of the target sub-query statement.

12. A query method applied to a data service unit, the data service unit being configured in a data node of a system, the system further comprising a coordination node, the coordination node further comprising a connection unit, the connection unit comprising a connection reusable between coordination service units, one connection corresponding to one data service unit in the data node, the method comprising: When a preparation request carrying a subquery statement is received, query optimization data is generated using the subquery statement, and the query optimization data and the information of the subquery statement are written into the cache. The subquery statement is generated based on the query optimization result of the query statement. The system receives a usage request sent by the coordination service unit. The usage request is sent by the coordination service unit to the target data service unit when it receives the target query statement, determines the target subquery statement of the target query statement, and uses the target information of the target subquery statement to find the target data service unit that has cached the query optimization data of the target subquery statement. Based on the usage request, the query optimization data corresponding to the target information in the cache is reused to execute the query of the target subquery statement.

13. A system for implementing the query method as described in any one of claims 1-10, comprising: Coordination nodes and data nodes.

14. A computing device, comprising: Memory and processor; The memory is used to store computer-executable instructions, and the processor is used to execute the computer-executable instructions, which, when executed by the processor, implement the steps of the query method according to any one of claims 1 to 12.

15. A computer-readable storage medium storing computer-executable instructions that, when executed by a processor, implement the steps of the query method according to any one of claims 1 to 12.

Images