Preparation statement execution method and device, electronic equipment and readable storage medium

Abstract

Embodiments of the present application provide an execution method and device of a prepared statement, electronic equipment and a readable storage medium. The method is applied to a data node device, and includes the following steps: after a communication connection is established with a coordination node device, an execution plan corresponding to a preset statement sent by the coordination node device is received; if the execution plan is not detected in a shared memory slice area, the execution plan is stored in the shared memory slice area, and the execution plan is executed; the shared memory slice area is a memory slice area allocated for all processes in the data node device when a database is started, and the execution plan stored in the shared memory slice area is not deleted when a process of the data node device is closed; if the execution plan is detected in the shared memory slice area, the execution plan is acquired and executed. The processing speed of the prepared statement can be improved.

Description

Technical Field

[0001] This invention belongs to the field of database technology, and in particular relates to a method, apparatus, electronic device, and readable storage medium for executing preparatory statements. Background Technology

[0002] A preparatory statement is a special way of processing statements. Instead of executing the preparatory statement directly, it is first compiled to generate an execution plan. When the preparatory statement is executed later, the statement is executed by the Execute command with parameters, without the need for recompilation and optimization.

[0003] When executing the preparatory statements, the relevant technology caches the execution plan corresponding to the preparatory statements in the memory context of the background process of the data node device after the connection between the coordinating node device and the data node device is established. After the preparatory statements are executed, the cached execution plan is released.

[0004] The relevant technology requires releasing the cached execution plan after each execution of the preparatory statement, and then re-caching the execution plan when executing the preparatory statement again, which makes the processing speed of the preparatory statement relatively slow. Summary of the Invention

[0005] This invention provides a method, apparatus, electronic device, and readable storage medium for executing preparatory statements, in order to solve the technical problem in related technologies that after each execution of a preparatory statement, the cached execution plan needs to be released, and the execution plan needs to be re-cached when executing preparatory statements subsequently, resulting in slow processing speed of preparatory statements.

[0006] In a first aspect, the present invention provides a method for executing preparatory statements, applied to a data node device, the method comprising:

[0007] After establishing a communication connection with the coordinating node device, the system receives an execution plan corresponding to a preset statement sent by the coordinating node device. The execution plan is a plan generated by the coordinating node device after receiving the preset statement, compiling and optimizing the preset statement.

[0008] If the execution plan is not detected in the shared memory area, the execution plan is stored in the shared memory area and executed; the shared memory area is allocated to the data node device when the database starts, and is used by all processes in the data node device; the execution plan stored in the shared memory area is not deleted when the process of the data node device is closed;

[0009] If the execution plan is detected in the shared memory segment, the execution plan is obtained and executed.

[0010] Secondly, the present invention provides a method for executing preparatory statements, applied to a coordination node device, the method comprising:

[0011] After receiving the preparatory statement, the preparatory statement is compiled and optimized to generate the execution plan corresponding to the preparatory statement;

[0012] A connection to the data node device is obtained from the connection pool, and the execution plan corresponding to the preparatory statement is sent to the data node device. If the data node device does not detect the execution plan in the shared memory area, it stores the execution plan in the shared memory area and executes the execution plan; if the execution plan is detected in the shared memory area, it obtains the execution plan and executes the execution plan.

[0013] Thirdly, the present invention provides an execution apparatus for preparatory statements, applied to a data node device, the apparatus comprising:

[0014] The receiving module is used to receive an execution plan corresponding to a preset statement sent by the coordinating node device after establishing a communication connection with the coordinating node device; the execution plan is a plan corresponding to the preset statement generated by the coordinating node device after receiving the preset statement and compiling and optimizing the preset statement.

[0015] The first execution module is configured to, if the execution plan is not detected in the shared memory area, store the execution plan in the shared memory area and execute the execution plan; the shared memory area is allocated to the data node device when the database starts, and is used by all processes in the data node device; the execution plan stored in the shared memory area is not deleted when the process of the data node device is closed;

[0016] The second execution module is configured to acquire the execution plan and execute the execution plan if the execution plan is detected in the shared memory segment.

[0017] Fourthly, the present invention provides an execution device for preparatory statements, applied to a coordination node device, the device comprising:

[0018] The generation module is used to compile and optimize the preparatory statement after receiving it, and generate the execution plan corresponding to the preparatory statement.

[0019] The sending module is used to obtain a connection to the data node device from the connection pool and send the execution plan corresponding to the preparatory statement to the data node device, so that if the data node device does not detect the execution plan in the shared memory area, it stores the execution plan in the shared memory area and executes the execution plan; if the execution plan is detected in the shared memory area, it obtains the execution plan and executes the execution plan.

[0020] Fifthly, the present invention provides an electronic device comprising: a processor, a memory, and a computer program stored in the memory and executable on the processor, characterized in that the processor, when executing the program, implements the above-mentioned method for executing preparatory statements.

[0021] In a sixth aspect, the present invention provides a readable storage medium that, when the instructions in the storage medium are executed by a processor of an electronic device, enables the electronic device to execute the above-described method for executing preparatory statements.

[0022] In this embodiment of the invention, after the data node device and the coordinating node device establish a communication connection, the data node device receives the execution plan corresponding to the preset statement sent by the coordinating node device. If the execution plan is not detected in the shared memory area, it is stored in the shared memory area and executed. If the execution plan is detected in the shared memory area, it is retrieved and executed. This invention stores the execution plan in a shared memory area allocated to the data node device when the database starts, for all processes within the data node device to access. This execution plan is not deleted when the data node device's processes are closed, eliminating the need to cache the execution plan in the data node's process memory each time a preparatory statement is executed. Therefore, the processing speed of preparatory statements can be improved. Attached Figure Description

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

[0024] Figure 1 This is a flowchart of the steps of an execution method for a preparatory statement provided in an embodiment of the present invention;

[0025] Figure 2 This is a schematic diagram of a shared memory provided in an embodiment of the present invention;

[0026] Figure 3 This is a flowchart of another method for executing preparatory statements provided in an embodiment of the present invention;

[0027] Figure 4 This is a schematic diagram of a connection pool management method provided in an embodiment of the present invention;

[0028] Figure 5 This is a flowchart illustrating the interactive steps of an execution method for a preparatory statement provided in an embodiment of the present invention.

[0029] Figure 6 This is an execution device for preparatory statements provided in an embodiment of the present invention;

[0030] Figure 7 This is another execution device for preparatory statements provided in the embodiments of the present invention;

[0031] Figure 8 This is a structural diagram of an electronic device provided in an embodiment of the present invention. Detailed Implementation

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

[0033] A prepared statement is a server-side object used to optimize performance. It allows for the pre-compilation and optimization of Structured Query Language (SQL) queries, enabling the query to be executed multiple times in subsequent queries without re-parsing and optimization. A prepared statement can be subsequently executed after receiving parameters and binding values ​​using the BIND command. Prepared statements exist only during the current database session and disappear when the session ends. Furthermore, a prepared statement cannot be used by multiple database clients simultaneously.

[0034] Currently, most databases support prepared statements. When a prepared statement is executed, the specified statement is pre-compiled and optimized, and the generated execution plan is cached. Subsequent processing of the EXECUTE command can accept and bind parameters without recompiling and optimizing, and allows the execution plan to depend on specific parameter values ​​provided. For single-instance PostgreSQL databases, caching can be easily managed through a memory context; specifically, CachedPlanSource manages the pre-compiled and optimized execution plans.

[0035] In a distributed PostgreSQL database, a cluster typically consists of several coordinating nodes and data nodes. The execution of an SQL statement often requires both the coordinating and data nodes to obtain and execute the corresponding execution plan. Furthermore, the execution of SQL statements in a distributed PostgreSQL cluster typically involves extensive communication between the coordinating and data nodes. A client connection is no longer handled by a single background process but by multiple background processes on both the coordinating and data nodes. Therefore, unlike a single-machine PostgreSQL database, the processing of prepared statements cannot simply involve caching the execution plan within the context of a single background process.

[0036] The caching method for preparatory statements is closely related to the communication method between the coordination node devices and data node devices in a distributed database. Currently, distributed PostgreSQL databases mainly adopt two solutions:

[0037] The first type is a long-lived connection, where the connection between the coordinating node device and the data node device is a long-lived connection, and the lifespan of the connection is the same as the session. In this scheme, when the coordinating node device and the data node device communicate, there is a one-to-one correspondence between the background processes on the coordinating node device and the data node device. The execution plan corresponding to the prepared statement is cached in the memory context of the background process corresponding to the coordinating node device and the data node device until the process ends.

[0038] The second type is connection pooling. Communication between the coordinating node device and the data node device is managed using a connection pool, and the background processes of the coordinating node device and the data node device are no longer in a one-to-one correspondence. Because the connection pool manages and reuses connections, sometimes it needs to be returned to the pool. The next time a connection is reused, the data node device's background process holding that connection may be handling requests from a different session. Therefore, each time a connection is returned, the data node device's background process needs to release the session-related memory context. For preparatory statements, the cached execution plan also needs to be released. If the preparatory statement needs to be executed again later, the coordinating node device needs to request a connection from the data node device again from the connection pool and recreate memory and cache the execution plan in the corresponding data node device's background process.

[0039] For preparatory statements, both approaches and their corresponding caching methods have certain issues. The first approach, while simple and direct, generates a large number of connections and background processes in high-concurrency scenarios because the lifecycle of a long-lived connection is the same as the session, especially on data node devices, causing significant resource consumption and impacting system performance. The second approach, by introducing a connection pool, solves the problem of connection and process inflation, but the need to release associated memory when returning a connection necessitates re-caching when processing preparatory statements, reducing processing speed and performance.

[0040] In this embodiment of the invention, after the data node device and the coordinating node device establish a communication connection, the data node device receives the execution plan corresponding to the preset statement sent by the coordinating node device. If the execution plan is not detected in the shared memory area, it is stored in the shared memory area and executed. If the execution plan is detected in the shared memory area, it is retrieved and executed. This invention stores the execution plan in a shared memory area allocated to the data node device when the database starts, for all processes within the data node device to access. This execution plan is not deleted when the data node device's processes are closed, eliminating the need to re-cache the execution plan in the data node's process memory each time a preparatory statement is executed. Therefore, the processing speed of preparatory statements can be improved.

[0041] Figure 1 This is a flowchart illustrating the steps of an execution method for a preparatory statement provided in an embodiment of the present invention, as follows: Figure 1 As shown, the method may include:

[0042] Step 101: After establishing a communication connection with the coordinating node device, receive the execution plan corresponding to the preset statement sent by the coordinating node device; the execution plan is the plan corresponding to the preset statement generated by the coordinating node device after receiving the preset statement, compiling and optimizing the preset statement.

[0043] For example, an execution plan refers to a set of operation steps generated by a database system to execute an SQL statement, including accessing tables, indexes, and filtering data. The process of generating an execution plan needs to be optimized based on factors such as the structure of the SQL statement, table structure, and indexes to improve execution efficiency.

[0044] For example, a data node device is used to store data internally, similar to a data warehouse. When a client sends a prepared statement to the server, the request is routed to the data node device containing the necessary data, then the execution plan corresponding to the prepared statement is executed, and the result is delivered to the client.

[0045] For example, the coordinating node device is responsible for distributing preparatory statements. In other words, the coordinating node device handles the work delegation required for preparatory statements. Unlike data node devices, the coordinating node device does not search for data itself, but instead assigns that task to the data node devices.

[0046] For example, after establishing a communication connection with the coordinating node device, the system receives the execution plan corresponding to the prepared statement sent by the coordinating node device, so as to execute the execution plan corresponding to the prepared statement and return the execution result. This process is the basic flow of the database system executing prepared statements and is also the key to the database system achieving efficient data processing.

[0047] Step 102: If the execution plan is not detected in the shared memory area, the execution plan is stored in the shared memory area and executed; the shared memory area is allocated to the data node device when the database starts, and is used by all processes in the data node device; the execution plan stored in the shared memory area is not deleted when the process of the data node device is closed.

[0048] For example, the database uses a multi-process model and requires inter-process communication via shared memory. A shared memory segment is a memory segment shared by all processes on the data node device when the database starts up. The execution plan stored in this memory segment is not deleted when the data node device processes are shut down.

[0049] For example, in the scenario of preparing statements, the compiled and optimized execution plan is stored in a shared memory area, rather than in the process memory of the data node device. When the preparing statement is executed for the first time, the background process of the data node device allocates a memory area in shared memory to store the execution plan.

[0050] For example, on a data node device, in a scenario involving preparatory statement processing, when the background process of the data node device first receives the preparatory statement processing command sent by the coordinating node device, it allocates memory space in the shared memory area and caches information such as the execution plan required to process the preparatory statement. This avoids the overhead of repeatedly releasing and creating caches when processing preparatory statements multiple times due to connection reuse in the connection pool, thus improving system performance.

[0051] For example, see Figure 2Memory area 1 and Memory area 2 are shared memory areas. Memory area 1 stores execution plan 1 corresponding to preparatory statement 1 sent by client 1, and memory area 2 stores execution plan 2 corresponding to preparatory statement 1 sent by client 2. Execution plan 1 is the plan generated by process 1 in the coordinating node device after receiving preparatory statement 1 from the client. Execution plan 2 is the plan generated by process 2 in the coordinating node device after receiving preparatory statement 2 from the client.

[0052] For example, if no execution plan is detected in the shared memory segment, the execution plan is stored in the shared memory segment and executed, instead of storing the execution plan in the process memory. The execution plan is not deleted after the process is closed.

[0053] Step 103: If the execution plan is detected in the shared memory segment, the execution plan is obtained and executed.

[0054] For example, if an execution plan is detected in a shared memory segment, the execution plan is retrieved and executed. By finding the cached execution plan in the shared memory segment, the cache does not need to be recreated, improving the processing speed of prepared statements and enhancing system performance.

[0055] In this embodiment of the invention, after the data node device and the coordinating node device establish a communication connection, the data node device receives the execution plan corresponding to the preset statement sent by the coordinating node device. If the execution plan is not detected in the shared memory area, it is stored in the shared memory area and executed. If the execution plan is detected in the shared memory area, it is retrieved and executed. This invention stores the execution plan in a shared memory area allocated to the data node device when the database starts, for all processes within the data node device to access. This execution plan is not deleted when the data node device's processes are closed, eliminating the need to re-cache the execution plan in the data node's process memory each time a preparatory statement is executed. Therefore, the processing speed of preparatory statements can be improved.

[0056] Figure 3 This is a flowchart of another method for executing preparatory statements provided in an embodiment of the present invention, as shown below. Figure 3 As shown, the method may include:

[0057] Step 201: After receiving the preparatory statement, compile and optimize the preparatory statement to generate the execution plan corresponding to the preparatory statement.

[0058] For example, after receiving a preparatory statement, the preparatory statement is compiled and optimized to generate an execution plan corresponding to the preparatory statement. When the same preparatory statement is received later, it is not necessary to recompile and optimize it, which can improve the processing performance of preparatory statements.

[0059] Step 202: Obtain a connection to the data node device from the connection pool, and send the execution plan corresponding to the preparatory statement to the data node device, so that if the data node device does not detect the execution plan in the shared memory area, it stores the execution plan in the shared memory area and executes the execution plan; if the execution plan is detected in the shared memory area, it obtains the execution plan and executes the execution plan.

[0060] For example, the connection pool is used to manage the connections between the coordination node device and the data node device. When the coordination node device and the data node device communicate, they need to obtain a connection from the connection pool. After use, the connection needs to be released and returned to the connection pool.

[0061] For example, see Figure 4 When the database is started, the coordinating node device starts a connection pool process to store a certain number of connections to the data node devices, such as connections to processes 1 and 2 in data node device 1, connections to processes 3 and 4 in data node device 2, and connections to processes 5 and 6 in data node device 3.

[0062] In this embodiment of the invention, after the data node device and the coordinating node device establish a communication connection, the data node device receives the execution plan corresponding to the preset statement sent by the coordinating node device. If the execution plan is not detected in the shared memory area, it is stored in the shared memory area and executed. If the execution plan is detected in the shared memory area, it is retrieved and executed. This invention stores the execution plan in a shared memory area allocated to the data node device when the database starts, for all processes within the data node device to access. This execution plan is not deleted when the data node device's processes are closed, eliminating the need to re-cache the execution plan in the data node's process memory each time a preparatory statement is executed. Therefore, the processing speed of preparatory statements can be improved.

[0063] Figure 5 This is a flowchart illustrating the interactive steps of an execution method for a preparatory statement provided in an embodiment of the present invention, as shown below. Figure 5 As shown, the method may include:

[0064] Step 301: After receiving the preparatory statement, the coordination node device compiles and optimizes the preparatory statement to generate the execution plan corresponding to the preparatory statement.

[0065] This step can be referred to step 201 above, and will not be repeated here.

[0066] Step 302: The coordinating node device obtains a connection with the data node device from the connection pool and sends the execution plan corresponding to the preparatory statement to the data node device.

[0067] This step can be referred to as step 202 above, and will not be repeated here.

[0068] Optionally, the method further includes:

[0069] Step A1: After the database starts, create a preset number of connections from the coordinating node device to multiple data node devices for the connection pool, and cache the connections from the coordinating node device to multiple data node devices in the queue of the connection pool.

[0070] Step 302 may specifically include:

[0071] Sub-step 3021: If there is a connection to the data node device in the queue of the connection pool, then obtain the connection to the data node device from the queue of the connection pool.

[0072] Sub-step 3022: If there is no connection to the data node device in the queue of the connection pool, then establish a connection to the data node device and add it to the queue of the connection pool, and obtain the connection to the data node device from the queue of the connection pool.

[0073] For example, regarding step A1, see Figure 4 When the database starts, the coordinating node device starts a connection pool process to store a certain number of connections to the data node devices. For example, connections to processes 1 and 2 in data node device 1, processes 3 and 4 in data node device 2, and processes 5 and 6 in data node device 3. For sub-steps 3021-3022, if there are connections to data node devices in the connection pool queue (i.e., connections to processes within data node devices exist), then a connection to the data node device is retrieved from the connection pool queue. If there are no connections to data node devices in the connection pool queue (i.e., the connection pool queue is empty), then a connection to the data node device is created and added to the connection pool queue, and the connection to the data node device is retrieved from the connection pool queue.

[0074] For example, if a connection to the data node device cannot be established, an attempt is made to establish the connection to the data node device again. If a connection to the data node device still cannot be established after a certain time interval, a fault is reported.

[0075] For example, after the connection pool process starts normally with the coordinating node device service, it pre-creates a certain number of connections to the data node devices for communication with the same number of background processes on the data node devices. When the coordinating node device needs to communicate with the data node devices, it requests a connection from the connection pool process. If there are any available connections to the data node devices in the connection pool's local cache, it allocates them to the coordinating node device, and the coordinating node device then communicates normally with the data node devices through the allocated connections. If there are no available connections in the connection pool at this time, a new connection is created, added to the connection pool, and allocated to the coordinating node device.

[0076] Step 303: The data node device receives the execution plan corresponding to the preset statement sent by the coordination node device.

[0077] This step can be referred to step 101 above, and will not be repeated here.

[0078] Step 304: If the data node device does not detect the execution plan in the shared memory area, it obtains the session identifier and preparatory statement sent by the coordinating node device; the session identifier is used to indicate the client corresponding to the preparatory statement.

[0079] For example, if no execution plan is detected in the shared memory area, it means that the prepared statement corresponding to the execution plan is being executed for the first time. Therefore, the session identifier and prepared statement are obtained to establish the correspondence between the session identifier, prepared statement and execution plan, and the correspondence is stored in the shared memory area to ensure the isolation of prepared statements between different sessions.

[0080] Step 305: The data node device uses the session identifier and the prepared statement as indexes, and the execution plan corresponding to the prepared statement as a value to establish a correspondence between the session identifier, the prepared statement, and the execution plan corresponding to the prepared statement. The correspondence is then stored in the shared memory area, and the execution plan is executed.

[0081] For example, since prepared statements only exist during the current database session, and multiple clients may execute the same prepared statements, in order to ensure session-level isolation of prepared statements on a shared memory slice visible to background processes on all data node devices, the execution plan needs to be categorized and isolated according to the session identifier and prepared statements when caching. When using a hash algorithm, both the session identifier and prepared statements need to be hashed as keys, and the execution plan is stored as the value in the shared memory slice.

[0082] Optionally, step 305 may specifically include:

[0083] Sub-step 3051: In response to the execution command of the preparatory statement, obtain the parameters corresponding to the preparatory statement;

[0084] Sub-step 3052: Bind the parameters corresponding to the preparatory statement to the execution plan, and execute the execution plan with parameters.

[0085] For example, for sub-steps 3051-3052, the execution command for the preparatory statement can be the EXECUTE command. In response to the EXECUTE command, the parameters corresponding to the preparatory statement are obtained through the BIND command, and the parameters corresponding to the preparatory statement are bound to the execution plan, and the execution plan with parameters is executed.

[0086] Step 306: If the data node device detects the execution plan in the shared memory area, it obtains the execution plan and executes it.

[0087] This step can be referred to as step 103 above, and will not be repeated here.

[0088] Optionally, the shared memory segment stores the session identifier, the preparatory statement, and the execution plan corresponding to the preparatory statement; step 306 may specifically include:

[0089] Sub-step 3061: Obtain the target session identifier and target preparatory statement sent by the coordinating node device;

[0090] Sub-step 3062: If the target session identifier and the target preparation statement exist in the shared memory segment, then obtain the target execution plan that matches the target session identifier and the target preparation statement from the shared memory segment, and execute the target execution plan.

[0091] For sub-steps 3061-3062, the target session identifier and target preparation statement sent by the coordinating node device are obtained. If the target session identifier and target preparation statement exist in the shared memory segment, the target execution plan matching the target session identifier and target preparation statement is obtained from the shared memory segment and executed. This eliminates the need to re-cache the target execution plan in the process memory of the data node device, thus improving the processing speed of the preparation statement.

[0092] For example, based on the target session identifier and the target preparatory statement, check if there is a corresponding key on the shared memory segment. If the key exists, directly use the value corresponding to the key as the execution plan and execute the execution plan.

[0093] Optionally, the method further includes:

[0094] Step 307: After receiving the clear preparation statement instruction sent by the coordinating node device, the data node device deletes the execution plan in the shared memory area.

[0095] For example, the clearing instruction for prepared statements can be the DEALLOCATE command. The corresponding execution plan in shared memory is released and reclaimed until the client session is closed or the DEALLOCATE command is received. Even if different connections are used during communication between the coordinating node device and the data node device due to connection pooling, the execution plan cached in the shared memory area can still be found during subsequent execution of prepared statements without needing to recreate the cache, thus speeding up the processing of prepared statements and improving system performance.

[0096] Optionally, the method further includes:

[0097] Step 308: The coordination node device sends a message to the data node device according to a preset time period. If no feedback message is received within the preset time period, the connection with the data node device is returned to the connection pool.

[0098] For example, the message sent by the coordinating node device to the data node device can be a numerical identifier, such as 1. If no 1 is received from the data node device within a preset time period, the connection with the data node device is returned to the connection pool.

[0099] For example, a coordinating node device can proactively initiate a connection release request, returning the connection to the connection pool so that it can be used for communication with other coordinating node devices. Meanwhile, to avoid resource waste caused by holding connections for extended periods and leaving them idle, the connection pool monitors idle connections in real time. Connections that have been idle for a long time are released and returned to the connection pool, improving resource utilization and reducing resource consumption.

[0100] In this embodiment of the invention, after the data node device and the coordinating node device establish a communication connection, the data node device receives the execution plan corresponding to the preset statement sent by the coordinating node device. If the execution plan is not detected in the shared memory area, it is stored in the shared memory area and executed. If the execution plan is detected in the shared memory area, it is retrieved and executed. This invention stores the execution plan in a shared memory area allocated to the data node device when the database starts, for all processes within the data node device to access. This execution plan is not deleted when the data node device's processes are closed, eliminating the need to re-cache the execution plan in the data node's process memory each time a preparatory statement is executed. Therefore, the processing speed of preparatory statements can be improved.

[0101] For example, the process of preparing statement processing methods specifically includes the following steps:

[0102] Step S1: After the database cluster starts, the connection pool process on the coordinating node device creates a certain number of connections from the coordinating node device to different data node devices. Each connection is held by a different background process on a different data node device, and these connections are cached in the idle queue of the connection pool, waiting for requests. After the coordinating node device receives the pre-compiled statement command from the client, since the background process of the coordinating node device maintains a connection with the client until the session ends, the execution plan obtained after the statement is pre-compiled and optimized can be directly and normally cached in the memory context of the background process of the coordinating node device, and released normally when the process exits.

[0103] Step S2: The coordinating node device requests a connection from the connection pool process. The connection pool checks its local cached idle queue for available connections and allocates the idle connection to the coordinating node device's background process. The coordinating node device's background process can then communicate with the data node device's background process through the obtained connection, sending execution plans or SQL statements to the data node device for execution. If there are no available connections in the idle queue, the connection pool creates a new connection, adds it to the queue, and then allocates it to the coordinating node device's background process.

[0104] Step S3: After receiving the command from the coordinating node device, the background process of the data node device performs a hash calculation based on the session identifier and the content of the prepared statement to generate a hash key. It then allocates a memory space in the shared memory area of ​​the database to cache the execution plan and other information required for processing the prepared statement. Subsequently, when processing the prepared statement, the background process of the data node device checks whether a corresponding key exists in the shared memory based on the session identifier and the prepared statement. If the key exists, it directly generates an execution plan based on the cached information and executes it. If the key does not exist, it needs to allocate a new memory space and cache the information again, ensuring isolation of the prepared statement between different sessions.

[0105] Step S4: When the connection between the coordinating node device and the data node device is released due to prolonged idle time, or when the coordinating node device actively requests to release the connection, the background process of the data node device normally cleans up the corresponding memory context. However, the prepared statement-related information cached in shared memory is not cleared. When the coordinating node device subsequently receives prepared statement commands, it re-establishes the connection and sends commands to the data node device. At this time, the data node device directly looks up the corresponding cached information in shared memory and processes it, without needing to re-allocate memory and record the information. The cached prepared statement-related information in the shared memory of the data node device will only be cleared when the session ends or the client explicitly clears the prepared statements using the DEALLOCATE command.

[0106] In this embodiment of the invention, after the data node device and the coordinating node device establish a communication connection, the data node device receives the execution plan corresponding to the preset statement sent by the coordinating node device. If the execution plan is not detected in the shared memory area, it is stored in the shared memory area and executed. If the execution plan is detected in the shared memory area, it is retrieved and executed. This invention stores the execution plan in a shared memory area allocated to the data node device when the database starts, for all processes within the data node device to access. This execution plan is not deleted when the data node device's processes are closed, eliminating the need to re-cache the execution plan in the data node's process memory each time a preparatory statement is executed. Therefore, the processing speed of preparatory statements can be improved.

[0107] Figure 6 This invention provides an execution device 40 for preparatory statements. The device 40 is applied to a data node device and may include:

[0108] The receiving module 401 is used to receive an execution plan corresponding to a preset statement sent by the coordinating node device after establishing a communication connection with the coordinating node device; the execution plan is a plan corresponding to the preset statement generated by the coordinating node device after receiving the preset statement and compiling and optimizing the preset statement.

[0109] The first execution module 402 is configured to store the execution plan in the shared memory area and execute the execution plan if the execution plan is not detected in the shared memory area; the shared memory area is a memory area allocated to the data node device when the database starts, and is used by all processes in the data node device; the execution plan stored in the shared memory area is not deleted when the process of the data node device is closed;

[0110] The second execution module 403 is configured to acquire the execution plan and execute the execution plan if the execution plan is detected in the shared memory segment.

[0111] Optionally, the device further includes:

[0112] The deletion module is used to delete the execution plan in the shared memory area after receiving a clear preparation statement instruction sent by the coordinating node device.

[0113] Optionally, the first execution module includes:

[0114] The first acquisition submodule is used to acquire the session identifier and preparatory statement sent by the coordinating node device; the session identifier is used to indicate the client corresponding to the preparatory statement;

[0115] A submodule is established to use the session identifier and the prepared statement as indexes, the execution plan corresponding to the prepared statement as a value, to establish a correspondence between the session identifier, the prepared statement, and the execution plan corresponding to the prepared statement, and to store the correspondence in the shared memory area.

[0116] Optionally, the shared memory segment stores the session identifier, the preparatory statement, and the execution plan corresponding to the preparatory statement;

[0117] The second execution module includes:

[0118] The second acquisition submodule is used to acquire the target session identifier and target preparatory statement sent by the coordination node device;

[0119] The third acquisition submodule is used to acquire a target execution plan that matches the target session identifier and the target preparation statement from the shared memory segment if the shared memory segment contains the target session identifier and the target preparation statement, and then execute the target execution plan.

[0120] Optionally, the first execution module includes:

[0121] The fourth submodule is used to retrieve the parameters corresponding to the preparatory statement in response to the execution command of the preparatory statement;

[0122] The execution submodule is used to bind the parameters corresponding to the preparatory statement to the execution plan and execute the execution plan with parameters.

[0123] In this embodiment of the invention, after the data node device and the coordinating node device establish a communication connection, the data node device receives the execution plan corresponding to the preset statement sent by the coordinating node device. If the execution plan is not detected in the shared memory area, it is stored in the shared memory area and executed. If the execution plan is detected in the shared memory area, it is retrieved and executed. This invention stores the execution plan in a shared memory area allocated to the data node device when the database starts, for all processes within the data node device to access. This execution plan is not deleted when the data node device's processes are closed, eliminating the need to re-cache the execution plan in the data node's process memory each time a preparatory statement is executed. Therefore, the processing speed of preparatory statements can be improved.

[0124] Figure 7 This invention provides a preparatory statement execution device 50, which is applied to a coordination node device. The device 50 may include:

[0125] The receiving module 501 is used to compile and optimize the preparatory statement after receiving it, and generate an execution plan corresponding to the preparatory statement.

[0126] The sending module 502 is used to obtain a connection to the data node device from the connection pool and send the execution plan corresponding to the preparatory statement to the data node device, so that if the data node device does not detect the execution plan in the shared memory area, it stores the execution plan in the shared memory area and executes the execution plan; if the execution plan is detected in the shared memory area, it obtains the execution plan and executes the execution plan.

[0127] Optionally, the device further includes:

[0128] A creation module is used to create a preset number of connections from the coordinating node device to multiple data node devices for the connection pool after the database is started, and to cache the connections from the coordinating node device to multiple data node devices in the queue of the connection pool.

[0129] The sending module includes:

[0130] The acquisition submodule is used to acquire a connection to the data node device from the queue of the connection pool if a connection to the data node device exists in the queue of the connection pool.

[0131] A submodule is established to create a connection to a data node device and add it to the queue of the connection pool if there is no connection to the data node device in the queue of the connection pool, and to obtain the connection to the data node device from the queue of the connection pool.

[0132] Optionally, the device further includes:

[0133] The return module is used to send messages to the data node device according to a preset time period. If no feedback message is received within the preset time period, the connection with the data node device is returned to the connection pool.

[0134] In this embodiment of the invention, after the data node device and the coordinating node device establish a communication connection, the data node device receives the execution plan corresponding to the preset statement sent by the coordinating node device. If the execution plan is not detected in the shared memory area, it is stored in the shared memory area and executed. If the execution plan is detected in the shared memory area, it is retrieved and executed. This invention stores the execution plan in a shared memory area allocated to the data node device when the database starts, for all processes within the data node device to access. This execution plan is not deleted when the data node device's processes are closed, eliminating the need to re-cache the execution plan in the data node's process memory each time a preparatory statement is executed. Therefore, the processing speed of preparatory statements can be improved.

[0135] The present invention also provides an electronic device, see [link to relevant documentation]. Figure 8 It includes: a processor 801, a memory 802, and a computer program 8021 stored in the memory and executable on the processor. When the processor executes the program, it implements the method for executing the preparatory statements of the foregoing embodiments.

[0136] The present invention also provides a readable storage medium that, when the instructions in the storage medium are executed by the processor of an electronic device, enables the electronic device to execute the execution method of the preparatory statements of the foregoing embodiments.

[0137] As the device embodiment is basically similar to the method embodiment, the description is relatively simple, and relevant parts can be found in the description of the method embodiment.

[0138] It should be noted that all information and data obtained in the embodiments of the present invention were obtained with the authorization of the information / data holder.

[0139] The algorithms and displays provided herein are not inherently related to any particular computer, virtual system, or other device. Various general-purpose systems can also be used in conjunction with the teachings herein. The required structure for constructing such systems is apparent from the above description. Furthermore, this invention is not directed to any particular programming language. It should be understood that the contents of the invention described herein can be implemented using various programming languages, and the above description of specific languages ​​is for the purpose of disclosing the best mode of implementation of the invention.

[0140] Numerous specific details are set forth in the specification provided herein. However, it will be understood that embodiments of the invention may be practiced without these specific details. In some instances, well-known methods, structures, and techniques have not been shown in detail so as not to obscure the understanding of this specification.

[0141] Similarly, it should be understood that, in order to simplify the invention and aid in understanding one or more of the various inventive aspects, in the above description of exemplary embodiments of the invention, various features of the invention are sometimes grouped together in a single embodiment, figure, or description thereof. However, this disclosure should not be construed as reflecting an intention that the claimed invention requires more features than are expressly recited in each claim. Rather, as reflected in the following claims, inventive aspects lie in fewer than all features of a single foregoing disclosed embodiment. Therefore, the claims following the detailed description are hereby expressly incorporated into this detailed description, wherein each claim itself is a separate embodiment of the invention.

[0142] Those skilled in the art will understand that modules in the device of the embodiments can be adaptively changed and placed in one or more devices different from that embodiment. Modules, units, or components in the embodiments can be combined into a single module, unit, or component, and further, they can be divided into multiple sub-modules, sub-units, or sub-components. Except where at least some of such features and / or processes or units are mutually exclusive, any combination can be used to combine all features disclosed in this specification (including the accompanying claims, abstract, and drawings) and all processes or units of any method or device so disclosed. Unless expressly stated otherwise, each feature disclosed in this specification (including the accompanying claims, abstract, and drawings) may be replaced by an alternative feature that serves the same, equivalent, or similar purpose.

[0143] The various component embodiments of the present invention can be implemented in hardware, or as software modules running on one or more processors, or a combination thereof. Those skilled in the art will understand that microprocessors or digital signal processors (DSPs) can be used in practice to implement some or all of the functions of some or all of the components in the sorting device according to the present invention. The present invention can also be implemented as a device or apparatus program for performing part or all of the methods described herein. Such a program implementing the present invention can be stored on a computer-readable medium, or can be in the form of one or more signals. Such signals can be downloaded from an Internet website, provided on a carrier signal, or provided in any other form.

[0144] It should be noted that the above embodiments are illustrative of the invention and not restrictive, and that those skilled in the art can devise alternative embodiments without departing from the scope of the appended claims. In the claims, any reference signs placed between parentheses should not be construed as limiting the claims. The word "comprising" does not exclude the presence of elements or steps not listed in the claims. The word "a" or "an" preceding an element does not exclude the presence of a plurality of such elements. The invention can be implemented by means of hardware comprising several different elements and by means of a suitably programmed computer. In the unit claims enumerating several means, several of these means may be embodied by the same item of hardware. The use of the words first, second, and third, etc., does not indicate any order. These words can be interpreted as names.

[0145] The user information (including but not limited to user device information, user personal information, etc.) and related data involved in this invention are all information authorized by the user or by the parties.

[0146] Those skilled in the art will understand that, for the sake of convenience and brevity, the specific working processes of the systems, devices, and units described above can be referred to the corresponding processes in the foregoing method embodiments, and will not be repeated here.

[0147] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

[0148] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. A method for executing preparatory statements, characterized in that, Applied to data node devices, the method includes: After establishing a communication connection with the coordinating node device, the system receives an execution plan corresponding to a preset statement sent by the coordinating node device. The execution plan is a plan generated by the coordinating node device after receiving the preset statement, compiling and optimizing the preset statement. If the execution plan is not detected in the shared memory area, the execution plan is stored in the shared memory area and executed; the shared memory area is allocated to the data node device when the database starts, and is used by all processes in the data node device; the execution plan stored in the shared memory area is not deleted when the process of the data node device is closed; If the execution plan is detected in the shared memory segment, then the execution plan is obtained and executed. Storing the execution plan in the shared memory segment includes: Obtain the session identifier and preparatory statement sent by the coordinating node device; the session identifier is used to indicate the client corresponding to the preparatory statement; Using the session identifier and the prepared statement as indexes, and the execution plan corresponding to the prepared statement as the value, a correspondence is established between the session identifier, the prepared statement, and the execution plan corresponding to the prepared statement, and the correspondence is stored in the shared memory area.

2. The method according to claim 1, characterized in that, The method further includes: After receiving the clear preparation statement instruction sent by the coordinating node device, the execution plan in the shared memory segment is deleted.

3. The method according to claim 1, characterized in that, The shared memory segment stores the session identifier, the preparatory statement, and the execution plan corresponding to the preparatory statement; Obtaining the execution plan includes: Obtain the target session identifier and target preparatory statement sent by the coordinating node device; If the target session identifier and the target preparation statement exist in the shared memory segment, then the target execution plan matching the target session identifier and the target preparation statement is obtained from the shared memory segment, and the target execution plan is executed.

4. The method according to claim 1, characterized in that, The execution of the execution plan includes: In response to the execution command of the preparatory statement, retrieve the parameters corresponding to the preparatory statement; The parameters corresponding to the preparatory statement are bound to the execution plan, and the execution plan with parameters is executed.

5. A method for executing preparatory statements, characterized in that, Applied to a coordinating node device, the method includes: After receiving the preparatory statement, the preparatory statement is compiled and optimized to generate the execution plan corresponding to the preparatory statement; A connection to a data node device is obtained from the connection pool, and an execution plan corresponding to the preparatory statement is sent to the data node device. If the data node device does not detect the execution plan in the shared memory segment, it stores the execution plan in the shared memory segment and executes it; if the execution plan is detected in the shared memory segment, it obtains the execution plan and executes it. Storing the execution plan in the shared memory segment includes: Obtain the session identifier and preparatory statement sent by the coordinating node device; the session identifier is used to indicate the client corresponding to the preparatory statement; Using the session identifier and the prepared statement as indexes, and the execution plan corresponding to the prepared statement as the value, a correspondence is established between the session identifier, the prepared statement, and the execution plan corresponding to the prepared statement, and the correspondence is stored in the shared memory area.

6. The method according to claim 5, characterized in that, The method further includes: After the database starts, a preset number of connections from the coordinating node device to multiple data node devices are created for the connection pool, and the connections from the coordinating node device to multiple data node devices are cached in the queue of the connection pool. The step of obtaining a connection to the data node device from the connection pool includes: If a connection to a data node device exists in the queue of the connection pool, then the connection to the data node device is obtained from the queue of the connection pool. If there is no connection to the data node device in the queue of the connection pool, a connection to the data node device is established and added to the queue of the connection pool, and the connection to the data node device is obtained from the queue of the connection pool.

7. The method according to claim 5, characterized in that, The method further includes: According to a preset time period, a message is sent to the data node device. If no feedback message is received within the preset time period, the connection with the data node device is returned to the connection pool.

8. An electronic device, characterized in that, include: A processor, a memory, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the program, implements the method as described in any one of claims 1-7.

9. A readable storage medium, characterized in that, When the instructions in the storage medium are executed by the processor of the electronic device, the electronic device is able to perform the method described in any one of claims 1-7.

Images