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

Abstract

The embodiment of the invention provides a preliminary statement execution method and device, electronic equipment and a readable storage medium. The method is applied to data node equipment, and comprises the following steps: after establishing communication connection with coordination node equipment, receiving an execution plan corresponding to a preset statement sent by the coordination node equipment; if the execution plan is not detected in the shared memory area, storing the execution plan in the shared memory area, and executing the execution plan; the shared memory district is a memory district which is distributed for the data node equipment and is called by all processes in the data node equipment when the database is started; the execution plan stored in the shared memory area is not deleted when the process of the data node equipment is closed; and if the execution plan is detected in the shared memory area, obtaining the execution plan, and executing the execution plan. The processing speed of the preparatory statement can be improved.

Description

Technical Field

[0001] The present invention belongs to the field of database technology, and in particular relates to a method, device, electronic device and readable storage medium for executing a prepared statement. Background Art

[0002] A prepared statement is a special statement processing method. The prepared statement is not executed directly. Instead, the prepared statement is compiled to generate an execution plan. When the prepared statement is subsequently executed, the statement is executed with parameters through the Execute command without the need for recompilation and optimization.

[0003] When executing a prepared statement, after the coordination node device and the data node device establish a connection, the execution plan corresponding to the prepared statement will be cached in the memory context of the background process of the data node device. After the prepared statement is executed, the cached execution plan will be released.

[0004] The related technology needs to release the cached execution plan after each prepared statement is executed. When the prepared statement is subsequently executed, the execution plan needs to be cached again, and the processing speed of the prepared statement is slow. Summary of the invention

[0005] The present invention provides a method, device, electronic device and readable storage medium for executing prepared statements, so as to solve the technical problem in the related art that after each prepared statement is executed, a cached execution plan needs to be released, and when the prepared statement is subsequently executed, the execution plan needs to be re-cached, and the processing speed of the prepared statement is slow.

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

[0007] After establishing a communication connection with the coordination node device, receiving an execution plan corresponding to a preset statement sent by the coordination node device; the execution plan is a plan corresponding to the prepared statement generated by the coordination node device after compiling and optimizing the prepared statement after receiving the prepared 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 a memory area allocated to the data node device when the database is started and is called 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 slice, the execution plan is obtained and executed.

[0010] In a second aspect, the present invention provides a method for executing a prepared statement, applied to a coordination node device, the method comprising:

[0011] After receiving the prepared statement, compile and optimize the prepared statement to generate an execution plan corresponding to the prepared statement;

[0012] A connection with a data node device is obtained from a connection pool, and an execution plan corresponding to the prepared statement is sent to the data node device, so that if the data node device does not detect the execution plan in the shared memory area, the execution plan is stored in the shared memory area and executed; if the execution plan is detected in the shared memory area, the execution plan is obtained and executed.

[0013] In a third aspect, the present invention provides an execution device for a prepared statement, applied to a data node device, the device comprising:

[0014] A receiving module, configured 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 prepared statement generated by the coordinating node device after compiling and optimizing the prepared statement after receiving the prepared statement;

[0015] The first execution module is used 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 is started and is called 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 used to obtain the execution plan and execute the execution plan if the execution plan is detected in the shared memory slice.

[0017] In a fourth aspect, the present invention provides an execution device for a prepared statement, applied to a coordination node device, the device comprising:

[0018] A generation module, configured to compile and optimize the prepared statement after receiving the prepared statement, and generate an execution plan corresponding to the prepared statement;

[0019] A sending module is used to obtain a connection with a data node device from a connection pool, and to send an execution plan corresponding to the prepared statement to the data node device, so that if the data node device does not detect the execution plan in a shared memory area, the execution plan is stored in the shared memory area and executed; if the execution plan is detected in the shared memory area, the execution plan is obtained and executed.

[0020] In a fifth aspect, 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, wherein the processor implements the above-mentioned method for executing prepared statements when executing the program.

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

[0022] In an embodiment of the present invention, after the data node device establishes a communication connection with the coordination node device, the execution plan corresponding to the preset statement sent by the coordination node device is received. If the execution plan is not detected in the shared memory area, the execution plan is stored in the shared memory area and the execution plan is executed. If the execution plan is detected in the shared memory area, the execution plan is obtained and the execution plan is executed. The present invention stores the execution plan in a shared memory area allocated to the data node device when the database is started and called by all processes in the data node device. The execution plan will not be deleted when the process of the data node device is closed. There is no need to cache the execution plan in the process memory of the data node each time the prepared statement is executed. Therefore, the processing speed of the prepared statement can be improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0024] Figure 1 It is a flowchart of a method for executing a prepared statement provided by an embodiment of the present invention;

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

[0026] Figure 3 is a flowchart of another method for executing a prepared statement provided by an embodiment of the present invention;

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

[0028] Figure 5 It is a flow chart of interactive steps of a method for executing a prepared statement provided by an embodiment of the present invention;

[0029] Figure 6 An execution device for a prepared statement provided by an embodiment of the present invention;

[0030] Figure 7 Another prepared statement execution device provided by an embodiment of the present invention;

[0031] Figure 8 It is a structural diagram of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0032] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0033] A prepared statement is a server-side object used to optimize performance, allowing you to precompile and optimize Structured Query Language (SQL) queries, and then execute them multiple times in subsequent queries without having to reparse and optimize them. A prepared statement can be executed later after receiving parameters and binding values ​​through the BIND command. A prepared statement only exists during the current database session. When the session ends, the prepared statement disappears, and a prepared statement cannot be used by multiple database clients at the same time.

[0034] Currently, most databases already support prepared statements. When a prepared statement is executed, the specified statement is precompiled and optimized, and the generated execution plan is cached. When the EXECUTE command is subsequently processed, parameters can be received and bound, but there is no need to compile and optimize again, and the execution plan is allowed to depend on the specific parameter values ​​provided. For a stand-alone PostgreSQL database, the cache can be easily managed through the memory context. Specifically, CachedPlanSource is used to manage the precompiled and optimized execution plan.

[0035] For a distributed PostgreSQL database, a distributed PostgreSQL database cluster is usually composed of several coordination node devices and data node devices. The execution of an SQL statement often requires obtaining the execution plan corresponding to the statement and executing it on the coordination node device and the data node device. The execution of SQL statements by a distributed PostgreSQL database cluster usually involves the communication of a large number of coordination node devices and data node devices. A client connection is no longer served by only one background process, but involves multiple background processes on the coordination node device and the data node device. In this case, for the processing of prepared statements, the execution plan cannot be simply cached in the context of a background process like a stand-alone PostgreSQL database.

[0036] The caching method for prepared statements is closely related to the communication method between the coordination node devices and the data node devices in the distributed database. There are two main solutions currently used in the distributed PostgreSQL database:

[0037] The first is a long connection, that is, the connection from the coordination node device to the data node device for communication adopts a long connection mode, and the life cycle of the connection is the same as the session. Under this scheme, when the coordination node device and the data node device communicate, the background processes on the coordination node device and the data node device correspond one to one, and the execution plan corresponding to the prepared statement is always cached in the memory context of the background process corresponding to the coordination node device and the data node device until the process ends.

[0038] The second type is the connection pool. The communication between the coordination node device and the data node device is managed by the connection pool. The background processes of the coordination node device and the data node device are no longer one-to-one. Since the connection pool will perform connection management and connection reuse, sometimes it is necessary to return the connection to the connection pool. The next time the connection is reused, the background process of the data node device holding the connection may process requests in a different session than before. Therefore, each time the connection is returned, the background process of the data node device needs to release the session-related memory context. For prepared statements, of course, the cached execution plan also needs to be released. If the prepared statement needs to be executed later, the coordination node device needs to request a connection to the data node device from the connection pool again, and re-create the memory and cache the execution plan in the background process of the data node device corresponding to the connection.

[0039] For prepared statements, both solutions and their corresponding caching methods have certain problems. Although the first solution is simple and direct, since the life cycle of a long connection is the same as that of a session, a large number of connections and background processes will be generated in high-concurrency scenarios, especially on data node devices, causing a large amount of resource consumption and affecting system performance. The second solution introduces a connection pool, which solves the problem of expanding the number of connections and processes. However, since the relevant memory needs to be released when the connection is returned, it is necessary to re-cache the prepared statement when processing it later, which will reduce the processing speed and performance of the prepared statement.

[0040] In an embodiment of the present invention, after the data node device establishes a communication connection with the coordination node device, the execution plan corresponding to the preset statement sent by the coordination node device is received. If the execution plan is not detected in the shared memory area, the execution plan is stored in the shared memory area and the execution plan is executed. If the execution plan is detected in the shared memory area, the execution plan is obtained and the execution plan is executed. The present invention stores the execution plan in a shared memory area allocated to the data node device when the database is started and called by all processes in the data node device. The execution plan will not be deleted when the process of the data node device is closed. There is no need to re-cache the execution plan in the process memory of the data node each time the prepared statement is executed. Therefore, the processing speed of the prepared statement can be improved.

[0041] Figure 1 is a flowchart of a method for executing a prepared statement provided by an embodiment of the present invention. Figure 1 As shown, the method may include:

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

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

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

[0045] For example, the coordinator node device is responsible for distributing prepared statements. In other words, the coordinator node device handles the delegation of work required for prepared statements. Unlike the data node devices, the coordinator node device does not search for data on its own, but instead delegates that task to the data node devices.

[0046] For example, after establishing a communication connection with the coordination node device, the execution plan corresponding to the prepared statement sent by the coordination node device is received, 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 to execute prepared statements and is also the key to the database system to achieve 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 a memory area allocated to the data node device when the database is started and is called 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 adopts a multi-process model and needs to communicate between processes through shared memory. The shared memory area is a memory area used by all processes of the data node device when the database is started. The execution plan stored in this memory area will not be deleted when the data node device process is closed.

[0049] For example, for the prepared statement processing scenario, the compiled and optimized execution plan is stored in the shared memory slice instead of the process memory of the data node device. When the prepared statement is executed for the first time, the background process of the data node device will apply for a memory slice in the shared memory to store the execution plan.

[0050] For example, on the data node device, for the prepared statement processing scenario, when the background process of the data node device receives the prepared statement processing command sent by the coordination node device for the first time, it applies for memory space in the shared memory area and caches the execution plan and other information required to process the prepared statement. This avoids the overhead of repeatedly releasing and creating caches when processing prepared statements multiple times due to connection reuse in the connection pool, thereby improving system performance.

[0051] For example, see Figure 2, the slices where memory 1 and memory 2 are located are shared memory slices. Memory slice 1 stores execution plan 1 corresponding to prepared statement 1 sent by client 1, and memory slice 2 stores execution plan 2 corresponding to prepared statement 1 sent by client 2. Among them, execution plan 1 is a plan generated by process 1 in the coordination node device after receiving prepared statement 1 sent by the client. Execution plan 2 is a plan generated by process 2 in the coordination node device after receiving prepared statement 2 sent by the client.

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

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

[0054] For example, if an execution plan is detected in the shared memory slice, the execution plan is obtained and executed. By searching the cached execution plan in the shared memory slice, there is no need to recreate the cache, which improves the processing speed of the prepared statement and improves the system performance.

[0055] In an embodiment of the present invention, after the data node device establishes a communication connection with the coordination node device, the execution plan corresponding to the preset statement sent by the coordination node device is received. If the execution plan is not detected in the shared memory area, the execution plan is stored in the shared memory area and the execution plan is executed. If the execution plan is detected in the shared memory area, the execution plan is obtained and the execution plan is executed. The present invention stores the execution plan in a shared memory area allocated to the data node device when the database is started and called by all processes in the data node device. The execution plan will not be deleted when the process of the data node device is closed. There is no need to re-cache the execution plan in the process memory of the data node each time the prepared statement is executed. Therefore, the processing speed of the prepared statement can be improved.

[0056] Figure 3 is a flowchart of another method for executing a prepared statement provided by an embodiment of the present invention. Figure 3 As shown, the method may include:

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

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

[0059] Step 202: obtain a connection with the data node device from the connection pool, and send the execution plan corresponding to the prepared statement to the data node device, so that if the data node device does not detect the execution plan in the shared memory area, the execution plan is stored in the shared memory area and executed; if the execution plan is detected in the shared memory area, the execution plan is obtained and executed.

[0060] For example, the connection pool is used to manage the connection 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 the connection from the connection pool. After use, they need to release and return the connection to the connection pool.

[0061] For example, see Figure 4 When the database is started, the coordination node device will start a connection pool process to store a certain number of connections to the data node devices, for example, the connection with process 1 and process 2 in data node device 1, the connection with process 3 and process 4 in data node device 2, and the connection with process 5 and process 6 in data node device 3.

[0062] In an embodiment of the present invention, after the data node device establishes a communication connection with the coordination node device, the execution plan corresponding to the preset statement sent by the coordination node device is received. If the execution plan is not detected in the shared memory area, the execution plan is stored in the shared memory area and the execution plan is executed. If the execution plan is detected in the shared memory area, the execution plan is obtained and the execution plan is executed. The present invention stores the execution plan in a shared memory area allocated to the data node device when the database is started and called by all processes in the data node device. The execution plan will not be deleted when the process of the data node device is closed. There is no need to re-cache the execution plan in the process memory of the data node each time the prepared statement is executed. Therefore, the processing speed of the prepared statement can be improved.

[0063] Figure 5 is a flowchart of the interactive steps of a method for executing a prepared statement provided by an embodiment of the present invention, such as Figure 5 As shown, the method may include:

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

[0065] This step may refer to the above step 201 and will not be described in detail here.

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

[0067] This step may refer to the above step 202 and will not be described in detail here.

[0068] Optionally, the method further includes:

[0069] Step A1: After the database is started, a preset number of connections from the coordination node device to the multiple data node devices are created for the connection pool, and the connections from the coordination node device to the multiple data node devices are cached in a queue of the connection pool.

[0070] Step 302 may specifically include:

[0071] Sub-step 3021: if there is a connection with a data node device in the queue of the connection pool, obtain the connection with the data node device from the queue of the connection pool;

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

[0073] For example, for step A1, see Figure 4 When the database is started, the coordination node device will start a connection pool process to store a certain number of connections to the data node devices, for example, connections to process 1 and process 2 in data node device 1, connections to process 3 and process 4 in data node device 2, and connections to process 5 and process 6 in data node device 3. For sub-steps 3021-3022, if there is a connection to the data node device in the queue of the connection pool, that is, there is a connection to the process in the data node device, 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, that is, the queue of the connection pool is empty, then 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.

[0074] For example, if a connection with a data node device cannot be established, an attempt is made to establish a connection with the data node device again. If a connection with 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 coordination node device service, a certain number of connections to the data node devices are pre-created for communication with the same number of background processes on the data node devices. When the coordination node device needs to communicate with the data node device, it applies for a connection from the connection pool process. If there is an idle connection to the data node device available in the local cache of the connection pool, it will be allocated to the coordination node device. After that, the coordination node device communicates normally with the data node device through the connection applied for. If there is no idle connection in the connection pool at this time, a new connection will be created to join the connection pool and allocated to the coordination 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 may refer to the above step 101 and will not be described in detail 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 the prepared statement sent by the coordination node device; the session identifier is used to indicate the client corresponding to the prepared statement.

[0079] For example, if the execution plan is not detected in the shared memory slice, it means that the prepared statement corresponding to the execution plan is executed for the first time. Therefore, the session identifier and the prepared statement are obtained to establish a correspondence between the session identifier, the prepared statement and the execution plan, and the correspondence is stored in the shared memory slice, thereby ensuring 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, establishes a correspondence between the session identifier, the prepared statement, and the execution plan corresponding to the prepared statement, stores the correspondence in the shared memory slice, and executes the execution plan.

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

[0082] Optionally, step 305 may specifically include:

[0083] Sub-step 3051: in response to the execution command of the prepared statement, obtaining parameters corresponding to the prepared statement;

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

[0085] For example, for sub-steps 3051-3052, the execution command of the prepared statement may be an EXECUTE command. In response to the EXECUTE command, the parameters corresponding to the prepared statement are obtained through the BIND command, and the parameters corresponding to the prepared 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 slice, it obtains the execution plan and executes the execution plan.

[0087] This step may refer to the above step 103 and will not be described in detail here.

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

[0089] Sub-step 3061, obtaining the target session identifier and the target prepared statement sent by the coordination node device;

[0090] Sub-step 3062: If the target session identifier and the target prepared statement exist in the shared memory slice, a target execution plan matching the target session identifier and the target prepared statement is obtained from the shared memory slice, and the target execution plan is executed.

[0091] For sub-steps 3061-3062, the target session identifier and the target prepared statement sent by the coordination node device are obtained. If the target session identifier and the target prepared statement exist in the shared memory slice, the target execution plan matching the target session identifier and the target prepared statement is obtained from the shared memory slice, and the target execution plan is executed. There is no need to cache the target execution plan in the process memory of the data node device again, which can improve the processing speed of the prepared statement.

[0092] For example, according to the target session identifier and the target prepared statement, check whether there is a corresponding key in the shared memory area. If the key exists, the value corresponding to the key is directly used as the execution plan and the execution plan is executed.

[0093] Optionally, the method further includes:

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

[0095] For example, the instruction to clear the prepared statement can be a DEALLOCATE command. Until the client session is disconnected or the DEALLOCATE command is received, the corresponding execution plan in the shared memory is released and recycled. Even if different connections are used in the communication process between the coordination node device and the data node device due to the connection management of the connection pool, the execution plan cached in the shared memory slice can still be found when the prepared statement is subsequently executed without re-creating the cache, which speeds up the processing of the prepared statement and improves 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 of the 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 coordination node device to the data node device may be a digital identifier, such as 1. If the message 1 sent by the data node device is not received within a preset time period, the connection with the data node device is returned to the connection pool.

[0099] For example, the coordination node device can actively initiate a connection release request and return the connection to the connection pool so that it can be used for communication with other coordination node devices. At the same time, in order to avoid wasting resources by holding the connection for a long time and being idle, the connection pool will detect the idle connection in real time. The connection that has been idle for a long time will be released and returned to the connection pool, which improves resource utilization and reduces resource consumption.

[0100] In an embodiment of the present invention, after the data node device establishes a communication connection with the coordination node device, the execution plan corresponding to the preset statement sent by the coordination node device is received. If the execution plan is not detected in the shared memory area, the execution plan is stored in the shared memory area and the execution plan is executed. If the execution plan is detected in the shared memory area, the execution plan is obtained and the execution plan is executed. The present invention stores the execution plan in a shared memory area allocated to the data node device when the database is started and called by all processes in the data node device. The execution plan will not be deleted when the process of the data node device is closed. There is no need to re-cache the execution plan in the process memory of the data node each time the prepared statement is executed. Therefore, the processing speed of the prepared statement can be improved.

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

[0102] Step S1, after the database cluster is started, the connection pool process on the coordination node device will create a certain number of connections from the coordination node device to different data node devices, each connection is held by different background processes on different data node devices, and these connections are cached in the idle queue of the connection pool waiting for requests. After the coordination node device receives the prepared statement command from the client, since the background process of the coordination node device has been connected to the client before the end of the session, the execution plan obtained after the statement is precompiled and optimized can be directly cached in the memory context of the background process of the coordination node device, and released normally when the process exits.

[0103] Step S2, the coordination node device requests a connection from the connection pool process, the connection pool checks whether there is an available connection in the idle queue of the local cache, and allocates the idle connection to the background process of the coordination node device, after which the background process of the coordination node device can communicate with the background process of the data node device at the opposite end through the obtained connection, send the execution plan or SQL statement to the data node device, and let the data node device perform the corresponding processing operation. If there is no available connection in the idle queue temporarily, the connection pool recreates a new connection, adds the new connection to the queue, and then allocates it to the background process of the coordination node device.

[0104] Step S3, after the background process of the data node device receives the command sent by the coordination node device, it performs hash calculation based on the session identifier and the content of the prepared statement to generate a hash key, and applies for a memory space in the shared memory area of ​​the database to cache the execution plan and other information required to process the prepared statement. When processing the prepared statement thereafter, the background process of the data node device checks whether there is a corresponding key in the shared memory based on the session identifier and the prepared statement. If the key exists, the execution plan is directly generated and executed based on the cached information. If the key does not exist, it is necessary to reapply for a memory space and cache the information to ensure the isolation of prepared statements between different sessions.

[0105] Step S4, when the connection from the coordination node device to the data node device is released because it has been idle for a long time, or the coordination node device actively requests to release the connection, the background process of the data node device normally cleans up the corresponding memory context in the process, and the prepared statement related information cached in the shared memory will not be cleaned up. When the subsequent coordination node device continues to receive the prepared statement command, it re-acquires the connection and sends the command to the data node device. At this time, the data node device directly searches for the corresponding cache information in the shared memory and processes it, without having to re-apply for memory and record the information. The cached prepared statement related information will not be cleared in the shared memory of the data node device until the session ends or the client explicitly clears the prepared statement through the DEALLOCATE command.

[0106] In an embodiment of the present invention, after the data node device establishes a communication connection with the coordination node device, the execution plan corresponding to the preset statement sent by the coordination node device is received. If the execution plan is not detected in the shared memory area, the execution plan is stored in the shared memory area and the execution plan is executed. If the execution plan is detected in the shared memory area, the execution plan is obtained and the execution plan is executed. The present invention stores the execution plan in a shared memory area allocated to the data node device when the database is started and called by all processes in the data node device. The execution plan will not be deleted when the process of the data node device is closed. There is no need to re-cache the execution plan in the process memory of the data node each time the prepared statement is executed. Therefore, the processing speed of the prepared statement can be improved.

[0107] Figure 6 An execution device 40 of a prepared statement provided in an embodiment of the present invention is applied to a data node device, and the device 40 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 prepared statement generated by the coordinating node device after compiling and optimizing the prepared statement after receiving the prepared statement;

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

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

[0111] Optionally, the device further comprises:

[0112] The deleting module is used to delete the execution plan in the shared memory slice after receiving the clear prepared statement instruction sent by the coordinating node device.

[0113] Optionally, the first execution module includes:

[0114] A first acquisition submodule is used to acquire a session identifier and a prepared statement sent by the coordination node device; the session identifier is used to indicate a client corresponding to the prepared statement;

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

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

[0117] The second execution module includes:

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

[0119] The third acquisition submodule is used to acquire a target execution plan matching the target session identifier and the target prepared statement from the shared memory slice if the target session identifier and the target prepared statement exist in the shared memory slice, and execute the target execution plan.

[0120] Optionally, the first execution module includes:

[0121] A fourth acquisition submodule, configured to acquire parameters corresponding to the prepared statement in response to an execution command of the prepared statement;

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

[0123] In an embodiment of the present invention, after the data node device establishes a communication connection with the coordination node device, the execution plan corresponding to the preset statement sent by the coordination node device is received. If the execution plan is not detected in the shared memory area, the execution plan is stored in the shared memory area and the execution plan is executed. If the execution plan is detected in the shared memory area, the execution plan is obtained and the execution plan is executed. The present invention stores the execution plan in a shared memory area allocated to the data node device when the database is started and called by all processes in the data node device. The execution plan will not be deleted when the process of the data node device is closed. There is no need to re-cache the execution plan in the process memory of the data node each time the prepared statement is executed. Therefore, the processing speed of the prepared statement can be improved.

[0124] Figure 7 An execution device 50 of a prepared statement provided in an embodiment of the present invention is applied to a coordination node device, and the device 50 may include:

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

[0126] The sending module 502 is used to obtain a connection with a data node device from a connection pool, and to send an execution plan corresponding to the prepared statement to the data node device, so that if the data node device does not detect the execution plan in the shared memory area, the execution plan is stored in the shared memory area and executed; if the execution plan is detected in the shared memory area, the execution plan is obtained and executed.

[0127] Optionally, the device further comprises:

[0128] A creation module, used for creating a preset number of connections from the coordination node device to the multiple data node devices for the connection pool after the database is started, and caching the connections from the coordination node device to the multiple data node devices in a queue of the connection pool;

[0129] The sending module comprises:

[0130] An acquisition submodule, configured to acquire a connection with a data node device from the queue of the connection pool if a connection with the data node device exists in the queue of the connection pool;

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

[0132] Optionally, the device further comprises:

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

[0134] In an embodiment of the present invention, after the data node device establishes a communication connection with the coordination node device, the execution plan corresponding to the preset statement sent by the coordination node device is received. If the execution plan is not detected in the shared memory area, the execution plan is stored in the shared memory area and the execution plan is executed. If the execution plan is detected in the shared memory area, the execution plan is obtained and the execution plan is executed. The present invention stores the execution plan in a shared memory area allocated to the data node device when the database is started and called by all processes in the data node device. The execution plan will not be deleted when the process of the data node device is closed. There is no need to re-cache the execution plan in the process memory of the data node each time the prepared statement is executed. Therefore, the processing speed of the prepared statement can be improved.

[0135] The present invention also provides an electronic device, see Figure 8 , including: a processor 801, a memory 802, and a computer program 8021 stored in the memory and executable on the processor, wherein when the processor executes the program, the method for executing the prepared statement of the aforementioned embodiment is implemented.

[0136] The present invention also provides a readable storage medium. When the instructions in the storage medium are executed by a processor of an electronic device, the electronic device can execute the method for executing the prepared statement of the aforementioned embodiment.

[0137] As for the device embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

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

[0139] The algorithm and display provided herein are not inherently related to any particular computer, virtual system or other device. Various general purpose systems can also be used together with the teachings based on this. According to the above description, it is obvious that the structure required for constructing such systems. In addition, the present invention is not directed to any specific programming voice either. It should be understood that various programming voices can be utilized to realize the content of the present invention described herein, and the description of the above specific voices is for the purpose of disclosing the best mode of the present invention.

[0140] In the description provided herein, a large number of specific details are described. However, it is understood that embodiments of the present invention can be practiced without these specific details. In some instances, well-known methods, structures and techniques are not shown in detail so as not to obscure the understanding of this description.

[0141] Similarly, it should be understood that in order to streamline the present invention and aid in understanding one or more of the various inventive aspects, in the above description of exemplary embodiments of the present invention, the various features of the present invention are sometimes grouped together into a single embodiment, figure, or description thereof. However, this disclosed method should not be interpreted as reflecting the following intention: that the claimed invention requires more features than the features explicitly recited in each claim. More specifically, as reflected in the claims below, inventive aspects lie in less than all the features of the individual embodiments disclosed above. Therefore, the claims that follow the specific embodiment are hereby expressly incorporated into the specific embodiment, with each claim itself serving as a separate embodiment of the present invention.

[0142] Those skilled in the art will appreciate that the modules in the devices in the embodiments may be adaptively changed and arranged in one or more devices different from the embodiments. The modules or units or components in the embodiments may be combined into one module or unit or component, and in addition they may be divided into a plurality of submodules or subunits or subcomponents. Except that at least some of such features and / or processes or units are mutually exclusive, all features disclosed in this specification (including the accompanying claims, abstracts and drawings) and all processes or units of any method or device disclosed in this manner may be combined in any combination. Unless otherwise expressly stated, each feature disclosed in this specification (including the accompanying claims, abstracts and drawings) may be replaced by an alternative feature providing the same, equivalent or similar purpose.

[0143] The various component embodiments of the present invention may be implemented in hardware, or in software modules running on one or more processors, or in a combination thereof. It should be understood by those skilled in the art that a microprocessor or digital signal processor (DSP) may 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 may also be implemented as a device or apparatus program for executing part or all of the methods described herein. Such a program for implementing the present invention may be stored on a computer-readable medium, or may be in the form of one or more signals. Such a signal may be downloaded from an Internet website, or provided on a carrier signal, or provided in any other form.

[0144] It should be noted that the above embodiments illustrate the present invention rather than limit it, and that those skilled in the art may devise alternative embodiments without departing from the scope of the appended claims. In the claims, any reference symbol between brackets shall not be construed as a limitation on the claims. The word "comprising" does not exclude the presence of elements or steps not listed in the claims. The word "one" or "an" preceding an element does not exclude the presence of a plurality of such elements. The present invention may be implemented by means of hardware comprising a number of different elements and by means of a suitably programmed computer. In a unit claim enumerating a number of devices, several of these devices may be embodied by the same hardware item. The use of the words first, second, and third, etc., does not indicate any order. These words may be interpreted as names.

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

[0146] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working processes of the systems, devices and units described above can refer to the corresponding processes in the aforementioned 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 principle of the present invention should be included in the protection scope of the present invention.

[0148] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any person skilled in the art can easily think of changes or substitutions within the technical scope disclosed by the present invention, which should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention should be based on the protection scope of the claims.

Claims

1. A method for executing a prepared statement, characterized in that: Applied to a data node device, the method comprises: After establishing a communication connection with the coordination node device, receiving an execution plan corresponding to a preset statement sent by the coordination node device; the execution plan is a plan corresponding to the prepared statement generated by the coordination node device after compiling and optimizing the prepared statement after receiving the prepared 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 a memory area allocated to the data node device when the database is started and is called 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 slice, the execution plan is obtained and executed.

2. The method according to claim 1, characterized in that The method further comprises: After receiving the prepared statement clearing instruction sent by the coordination node device, the execution plan in the shared memory slice is deleted.

3. The method according to claim 1, characterized in that Storing the execution plan in the shared memory area includes: Acquire a session identifier and a prepared statement sent by the coordination node device; the session identifier is used to indicate a client corresponding to the prepared statement; The session identifier and the prepared statement are used as indexes, and the execution plan corresponding to the prepared statement is used as a value to establish a correspondence 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 slice.

4. The method according to claim 1, characterized in that The shared memory area stores session identifiers, prepared statements, and execution plans corresponding to the prepared statements; The obtaining of the execution plan includes: Acquire a target session identifier and a target prepared statement sent by the coordination node device; If the target session identifier and the target prepared statement exist in the shared memory slice, a target execution plan matching the target session identifier and the target prepared statement is obtained from the shared memory slice, and the target execution plan is executed.

5. The method according to claim 1, characterized in that The executing the execution plan includes: In response to an execution command of the prepared statement, obtaining parameters corresponding to the prepared statement; Bind the parameters corresponding to the prepared statement to the execution plan, and execute the execution plan with the parameters.

6. A method for executing a prepared statement, characterized in that: Applied to a coordination node device, the method comprises: After receiving the prepared statement, compile and optimize the prepared statement to generate an execution plan corresponding to the prepared statement; A connection with a data node device is obtained from a connection pool, and an execution plan corresponding to the prepared statement is sent to the data node device, so that if the data node device does not detect the execution plan in the shared memory area, the execution plan is stored in the shared memory area and executed; if the execution plan is detected in the shared memory area, the execution plan is obtained and executed.

7. The method according to claim 6, characterized in that The method further comprises: After the database is started, a preset number of connections from the coordination node device to the plurality of data node devices are created for the connection pool, and the connections from the coordination node device to the plurality of data node devices are cached in a queue of the connection pool; The step of obtaining a connection with a data node device from a connection pool includes: If there is a connection with a data node device in the queue of the connection pool, obtaining a connection with the data node device from the queue of the connection pool; If there is no connection with the data node device in the queue of the connection pool, a connection with the data node device is established and added to the queue of the connection pool, and the connection with the data node device is obtained from the queue of the connection pool.

8. The method according to claim 6, characterized in that The method further comprises: A message is sent to the data node device according to a preset time period. If no feedback message of the message is received within the preset time period, the connection with the data node device is returned to the connection pool.

9. 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 implements the method according to any one of claims 1 to 8 when executing the program.

10. A readable storage medium, characterized in that: When the instructions in the storage medium are executed by a processor of an electronic device, the electronic device is enabled to execute any one of the methods described in claims 1-8.

Images