Sql precompilation method, apparatus, server, and storage medium
By creating reusable pre-compiled objects in global memory, the memory consumption problem caused by repeatedly creating pre-compiled objects in database systems is solved, thereby improving the database's concurrent access capabilities and performance.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- JINZHUAN INFORMATION TECHNOLOGY CO LTD
- Filing Date
- 2021-11-30
- Publication Date
- 2026-07-03
AI Technical Summary
In database systems, the massive memory consumption caused by the repeated creation of SQL precompiled objects on the server side becomes a bottleneck for concurrent access capabilities and may cause server lag or crashes.
A global precompiled object is created in global memory, making it reusable across different links and within links. This avoids creating a new precompiled object for each link. By detecting whether there is an object corresponding to the same precompiled statement in global memory, the reuse of precompiled objects between and within links is achieved.
It significantly reduces memory usage, avoids server performance issues caused by insufficient memory, and improves the database's concurrent access capabilities and overall performance.
Smart Images
Figure CN116204546B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of databases, and in particular to an SQL pre-compilation method, apparatus, server, and storage medium. Background Technology
[0002] After receiving an SQL statement, the database needs to perform lexical and semantic analysis, SQL statement optimization and execution plan formulation, execution, and return the results. However, in database applications, a single SQL statement may be executed repeatedly, or only a few values may differ each time. If lexical and semantic analysis, statement optimization, and execution plan formulation are required every time, the efficiency of SQL statement execution will be significantly reduced. SQL prepared statements replace values in the SQL statement with placeholders, which can be seen as templating or parameterizing the SQL statement. SQL prepared statements can be compiled once and run multiple times, avoiding repeated hard parsing work, saving parsing and optimization processes, and improving execution efficiency. Furthermore, it can effectively prevent SQL injection attacks.
[0003] The database system, acting as a server, can establish sessions with each client. The database system will create a new prepared object corresponding to the prepared statement for each session on the server side. The prepared object is used as the data structure on which the server relies when performing pre-compilation.
[0004] However, the server-side memory in the above method may contain a large number of identical Prepare objects, resulting in a huge memory footprint.
[0005] This huge memory consumption wastes physical resources, becoming a bottleneck that restricts the database's concurrent access capabilities, and may also cause insufficient physical memory, leading to server lag or crashes. Summary of the Invention
[0006] The main objective of this application is to provide an SQL pre-compilation method, apparatus, server, and storage medium to reduce server-side memory usage, avoid restricting database concurrent access capabilities due to excessive server-side memory usage, and reduce server lag or crashes caused by insufficient memory.
[0007] To achieve the above objectives, embodiments of this application provide an SQL pre-compilation method, comprising: obtaining an SQL pre-compilation request, wherein the SQL pre-compilation request carries a pre-compilation statement; detecting whether a global pre-compilation object corresponding to the pre-compilation statement exists in global memory; if a global pre-compilation object corresponding to the pre-compilation statement exists, returning a pre-compilation success instruction to the client; if a global pre-compilation object corresponding to the pre-compilation statement does not exist, creating a global pre-compilation object according to the pre-compilation statement, and using the created global pre-compilation object for pre-compilation.
[0008] To achieve the above objectives, embodiments of this application also provide an SQL pre-compilation apparatus, comprising: an acquisition module for acquiring an SQL pre-compilation request, wherein the pre-compilation request carries a pre-compilation statement; a detection module for detecting whether a global pre-compilation object corresponding to the pre-compilation statement exists in global memory; and a pre-compilation module for returning a pre-compilation success instruction to a client if a global pre-compilation object corresponding to the pre-compilation statement exists; and for creating a global pre-compilation object according to the pre-compilation statement and using the created global pre-compilation object for pre-compilation if no global pre-compilation object corresponding to the pre-compilation statement exists.
[0009] To achieve the above objectives, embodiments of this application also provide a server, including: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to execute the above-described SQL pre-compilation method.
[0010] To achieve the above objectives, embodiments of this application also provide a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the SQL pre-compilation method described above.
[0011] The embodiments of this application create pre-compiled objects globally in global memory, making these objects accessible across different links and within links. Furthermore, when a global compilation object corresponding to a pre-compiled statement exists, the embodiments of this application can return a pre-compilation success instruction to the client without needing to create a new pre-compiled object (Prepare object) based on the link to which the pre-compilation request belongs. This achieves the reuse of pre-compiled objects across and within links, overcoming the functional defects of database systems where Prepare objects cannot be reused or can only be reused to a limited extent. It significantly reduces memory space occupation caused by duplicate Prepare objects, saving physical memory resources. Therefore, it avoids the constraint of huge server-side memory usage on database concurrent access capabilities and reduces server lag or crashes caused by insufficient physical memory. Attached Figure Description
[0012] Figure 1 This is a flowchart of an SQL pre-compilation method according to an embodiment of this application;
[0013] Figure 2 This is a schematic diagram of a global memory data structure provided in one embodiment of this application. Figure 1 ;
[0014] Figure 3 This is a schematic diagram of a global memory data structure provided in one embodiment of this application. Figure 2 ;
[0015] Figure 4 This is a schematic diagram of a global memory data structure provided in one embodiment of this application. Figure 3 ;
[0016] Figure 5 This is a schematic diagram of a global memory data structure provided according to an embodiment of this application. Figure 4 ;
[0017] Figure 6 This is a flowchart illustrating an operation on a precompiled object according to one embodiment of this application;
[0018] Figure 7 This is a schematic diagram of an SQL pre-compilation apparatus according to another embodiment of this application;
[0019] Figure 8 This is a schematic diagram of the structure of an electronic device according to another embodiment of this application. Detailed Implementation
[0020] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the various embodiments of this application will be described in detail below with reference to the accompanying drawings. However, those skilled in the art will understand that many technical details have been provided in the various embodiments of this application to help readers better understand this application. However, the technical solutions claimed in this application can be implemented even without these technical details and various changes and modifications based on the following embodiments. The division of the various embodiments below is for the convenience of description and should not constitute any limitation on the specific implementation of this application. The various embodiments can be combined with and referenced by each other without contradiction.
[0021] The process of a database system processing Structured Query Language (SQL) statements includes the following stages: lexical and semantic analysis, SQL statement optimization and execution plan formulation, execution, and return of results. The "lexical and semantic analysis" stage is called hard parsing. A single SQL statement may be executed repeatedly, or each execution may only differ a few values, such as the values of the WHERE clause in a SELECT statement or the SET clause in an UPDATE statement. If the entire SQL statement processing process were performed every time, a lot of repetitive hard parsing work would be done, impacting execution efficiency.
[0022] SQL prepared statements replace values in SQL statements with placeholders, essentially templateing or parameterizing them. They are also known as prepared statements. SQL prepared statements allow for compilation once and execution multiple times, avoiding repeated hard parsing and optimization processes, thus improving execution efficiency. Furthermore, they effectively prevent SQL injection attacks. These advantages have led most database applications to use prepared statements to access the database. SQL prepared statements are widely used, and most common database systems support them, such as MySQL, PostgreSQL, and Oracle.
[0023] Currently, SQL prepared statements are session-level (link-level), meaning that prepared statements created in the current session can only be used by that session. In other words, most database systems create a new prepared object (Prepare object) for each prepared statement in each session on the server side, using this prepared object as the data structure for server-side processing. However, this leads to a situation where, if the same prepared statement exists in multiple links (e.g., links 1, 2, and 3 all contain prepared statement 1), the server still creates the same number of Prepare objects to correspond to each one. For example, three identical prepared objects are created, one for each link (link 1, link 2, and link 3). This means the server-side Prepare objects are not reused at all, resulting in a large number of identical Prepare objects in server memory, leading to huge memory consumption. This massive memory consumption wastes physical resources, becoming a bottleneck restricting database concurrency and potentially causing server lag or crashes due to insufficient physical memory.
[0024] Only a few database systems, such as Oracle, have made limited improvements: if there are multiple Prepared Statements with the same SQL statement within a session, only one Prepare object needs to be created on the server side. However, this only reuses the Prepare object within the session. If there are prepared statements with the same SQL statement between different sessions, they still cannot be reused on the server side. Therefore, the problem of a large number of identical Prepare objects occupying memory has not been effectively solved.
[0025] Therefore, one embodiment of this application relates to an SQL pre-compilation method, which can be applied to a server-side device, such as a server. The SQL pre-compilation method of this embodiment includes: obtaining an SQL pre-compilation request, wherein the pre-compilation request carries a pre-compilation statement; detecting whether a global pre-compilation object corresponding to the pre-compilation statement exists in global memory; if a global pre-compilation object corresponding to the pre-compilation statement exists, returning a pre-compilation success instruction to the client; if no global pre-compilation object corresponding to the pre-compilation statement exists, creating a global pre-compilation object according to the pre-compilation statement, and using the created global pre-compilation object for pre-compilation.
[0026] This embodiment creates a global pre-compiled object in global memory, making it accessible across different links and within links. Furthermore, when a global pre-compiled object corresponding to a pre-compiled statement exists, a pre-compilation success instruction is returned to the client, eliminating the need to create a new pre-compiled object (Prepare object) for the link to which the pre-compilation request belongs. This allows the link to use the existing global pre-compiled object for execution and related operations, achieving the reuse of pre-compiled objects between and within links. This overcomes the functional defects of database systems where Prepare objects cannot be reused or can only be reused to a limited extent. It significantly reduces memory space consumption caused by duplicate Prepare objects, saving physical memory resources. Therefore, it avoids the constraint of large server-side memory consumption on database concurrent access capabilities and reduces server lag or crashes caused by insufficient physical memory.
[0027] The following is a detailed description of the implementation details of the SQL pre-compilation method in this embodiment. The following content is only for the convenience of understanding the implementation details and is not necessary for implementing this solution.
[0028] The specific process of the pre-compilation method in this embodiment can be as follows: Figure 1 As shown, it includes:
[0029] Step 101: Obtain the SQL prepared request. The SQL prepared request (Prepare request) carries the prepared statements.
[0030] Prepared statements are templated or parameterized SQL statements. Placeholders, such as "?", can be used to represent parameters, separating them from the SQL statement. For example, `insert into user(id,password,name,email,address)values(?",?",?",?",?")` can be a prepared statement. Prepared statements can be identified by their MD5 hashes; comparing the MD5 hashes of prepared statements will determine if they are identical.
[0031] Step 102 checks whether a global precompiled object corresponding to the precompiled statement exists in global memory. In this embodiment, the precompiled object is designed to be global so that all clients that have established a session with the server can access the precompiled object.
[0032] For example, in a MySQL database, the precompiled object can be a PreparedStatement object. It's worth noting that the specific name of the precompiled object may differ across databases. The SQL precompilation method in this embodiment can be applied to various databases, and this embodiment does not limit the specific name of the precompiled object.
[0033] In some embodiments, after receiving a pre-compilation request, the global memory is locked before being checked. Once the global pre-compilation object corresponding to the pre-compilation statement is found, the lock on the global memory can be released. By locking the global memory, conflicts caused by concurrent operations on the Prepare object in the global memory are avoided.
[0034] In some embodiments, global memory 200 may be referenced. Figure 2 As shown, it checks whether a global precompiled object 1 corresponding to precompiled statement 1 exists in global memory.
[0035] In other embodiments, reference is made to Figure 3 As shown, the global memory 300 includes multiple memory pools. Based on a preset mapping relationship between links and memory pools, a first target memory pool is obtained. The first target memory pool is the memory pool mapped to the link to which the SQL pre-compilation request belongs. Creating a global pre-compilation object in the global memory specifically includes: creating a global pre-compilation object in the first target memory pool; locking the global memory, including locking the first target memory pool; and releasing the lock on the global memory, including releasing the lock on the first target memory pool. The number of memory pools can be designed as a fixed value or configurable. When the number of memory pools is set to 1, the Prepare object is globally reusable; if set to a value greater than 1, the Prepare object is reusable within the corresponding memory pool.
[0036] In this embodiment's method for reusing globally prepared objects, the unique global memory structure storing all Prepare objects is improved into a memory pool, and a database system can have multiple memory pools. The implementation method of the memory pool can be selected according to the specific characteristics of the database, such as using a hash map.
[0037] The specific memory pool that manages a particular Prepare object can be selected using a suitable method designed based on the actual situation. For example, it can be determined by taking the modulo of the number of memory pools based on the link ID of the Prepared Statement. Once the memory pool managing the Prepare object is determined, it is responsible for allocating and releasing the Prepare object, as well as determining whether the Prepare object is idle and can be accessed by the specific link currently requesting it. As long as a link is assigned to access a memory pool, Prepare objects in that memory pool can be reused both within the same link and between different links.
[0038] Compared to locking and releasing locks across the entire global memory, which can lead to numerous lock conflicts and degrade database performance in high-concurrency scenarios, this embodiment divides the global memory into multiple memory pools. This alleviates lock conflicts in the globally unique memory management structure of Prepare objects, distributing lock conflicts across multiple memory pools. Each time a Prepare object is accessed, only the lock needs to be acquired in that specific memory pool. The impact is reduced from the global scope to a single memory pool, thereby minimizing database performance loss. For example, if the number of memory pools is M, the average number of lock conflicts is reduced to 1 / M of the original number.
[0039] In other embodiments, reference may be made to Figure 4 As shown, a precompiled object unit 401 can be created in global memory 400. Each global precompiled object in the precompiled object unit corresponds to the same precompiled statement. The server can determine whether the number of precompiled objects corresponding to the precompiled statement, i.e., whether the number of precompiled objects in the current precompiled object unit has reached a preset value; if it has not reached the preset value, a global precompiled object is created according to the precompiled statement and precompiled using the created global precompiled object; if it has reached the preset value, no precompiled object is created and no precompilation operation is performed, and a precompilation success instruction is returned to the client. If an idle precompiled object is obtained, step 103 is executed. For example, the corresponding Prepared object unit is found based on the string of the Prepared statement or its MD5 or other characteristic values.
[0040] In this embodiment, within global memory, multiple Prepare objects are allowed to correspond to the same Prepare statement, and the number of identical Prepare objects cannot exceed the "maximum concurrency of Prepare objects," i.e., it cannot exceed a preset value. The maximum concurrency of Prepare objects means the maximum number of links that can simultaneously access a Prepare object within the same memory pool, which is also the maximum number of replicas of Prepare objects in the memory pool, or the number of repeatable Prepare objects.
[0041] In other words, this embodiment introduces a new memory structure to manage identical Prepare objects within global memory, namely, a "Prepare object unit". When a Prepare object is created, if the number of identical Prepare objects does not exceed the "maximum concurrency" (a preset value), the memory pool will actually allocate memory for the Prepare object and place it into the corresponding Prepare object unit. However, if the "maximum concurrency" is exceeded, the memory pool will not actually allocate memory for the Prepare object, but will reuse it, counting the number of references to the pre-compiled object in the unit to obtain a reference count. When a Prepare object needs to be accessed, the Prepare object unit is first located, then a free Prepare object within the unit is found, and then the corresponding operation is performed on that Prepare object. When a Prepare object needs to be released, the Prepare object unit is first located. If the reference count exceeds the maximum concurrency, no actual Prepare object memory release is performed; the reference count is simply decremented. If the reference count does not exceed the maximum concurrency, a free Prepare object within the unit is found, then the Prepare object memory is released, and the reference count is decremented.
[0042] That is, after finding the precompiled object unit, check the reference count in the Prepare object unit. If it is less than the maximum concurrency of the Prepare object, create a new Prepare object in the memory pool, put it into the Prepare object unit, increment the reference count by 1, and set the Prepare object to the idle state; if it is greater than the maximum concurrency of the Prepare object, only increment the reference count by 1.
[0043] The maximum concurrency of a Prepare object, i.e., the default value, can be designed as a fixed value or as configurable. When the maximum concurrency is set to 1, it has the same effect as not introducing a maximum concurrency; if it is set to a value greater than 1 (such as N), the Prepare object can be accessed by N links simultaneously in the memory pool.
[0044] Introducing a maximum concurrency limit for Prepare objects can alleviate access lock contention within the Prepare object itself. This method avoids the situation where other pending access paths can only wait for a lock while a Prepare object is being accessed. By introducing a maximum concurrency limit, for example, N, N paths can be allowed to access a Prepare object simultaneously, reducing database performance loss and reducing lock contention to an average of 1 / N of the original.
[0045] In other embodiments, the common fields of each global precompiled object in the precompiled object unit, i.e., each global precompiled object corresponding to a certain precompiled statement, are fields that will not change or cause concurrent conflicts.
[0046] Within a "Prepare object unit," multiple identical Prepare objects can exist. These Prepare objects contain fields that are guaranteed not to change or experience concurrency conflicts, such as the SQL statement string and the database name stored in each Prepare object. These common fields are extracted and stored entirely within the "Prepare object unit" structure, with only one copy. All subsequent Prepare objects then point to this structure. This further reduces the memory footprint of Prepare objects and achieves more refined Prepare object reuse. More importantly, the memory allocation and copying of these fields for user-provided data in each Prepare object is eliminated after field reuse. In high-concurrency scenarios, these operations are very frequent; eliminating them results in better database performance than the original session-level Prepare object.
[0047] For example, a common field of the precompiled object in the created precompiled object can be pointed to the corresponding precompiled field on the precompiled unit.
[0048] In other embodiments, reference may be made to Figure 5 As shown, in global memory 500, the memory pool structure and the memory structure of precompiled object units can be combined, and one memory pool can have multiple precompiled object units. That is, in this embodiment, when a global precompiled object corresponding to the precompiled statement exists, before detecting whether the corresponding global precompiled object is in an idle state, it is determined whether the number of global precompiled objects corresponding to the precompiled statement in the first target memory pool has reached a preset value; if the preset value has not been reached, a global precompiled object is created according to the precompiled statement and the created global precompiled object is used for precompilation; if the preset value has been reached, no precompiled object is created and no precompilation operation is performed, and a precompilation success instruction can be directly returned to the client, i.e., proceed to step 103.
[0049] In this embodiment, if the number of memory pools is M and the maximum concurrency of Prepare objects is N, the overall lock contention is reduced to 1 / (M*N) compared to the initial global reuse scheme of Prepare objects. Furthermore, adjusting the values of M and N for specific business logic and servers can significantly reduce memory usage in SQL pre-compilation mode without significantly impacting database performance.
[0050] Step 103: If a global precompiled object corresponding to the precompiled statement exists, return a precompilation success instruction to the client.
[0051] In some embodiments, if a global precompiled object corresponding to a precompiled statement exists, and the number of such global precompiled objects reaches a preset maximum concurrency level (i.e., a preset value), a precompilation success instruction is returned to the client. That is, when the number of corresponding global precompiled objects reaches the preset maximum concurrency level, it is not necessary to create a new global precompiled object for precompilation.
[0052] In some embodiments, when the precompilation of a precompiled object is completed, the precompiled object is set to an idle state and the reference count is incremented by 1 to indicate the number of links referencing the precompiled object unit. Before performing operations such as execution using the corresponding precompiled object, the global precompiled object is locked, that is, it is set from an idle state to a busy state, so as to prevent other links from using the corresponding global precompiled object and causing concurrent conflicts.
[0053] Step 104: If no global precompiled object exists corresponding to the precompiled statement, create a global precompiled object based on the precompiled statement, and use the created global precompiled object to perform precompilation. After precompilation is complete, a precompilation success command can be sent to the client.
[0054] In some embodiments, a precompiled object unit corresponding to a precompiled statement may be created first, and then a global precompiled object may be created in the precompiled object unit.
[0055] After the pre-compiled object is successfully compiled, a Prepare success message can be returned to the client.
[0056] It is worth mentioning that common SQL pre-compilation modes in databases generally include: prepare process, execute process, fetch process, close process (or deallocate process), and disconnect process.
[0057] The Prepare process has been briefly described above. The following is a brief description of the process after pre-compilation, which involves receiving operation instructions for pre-compiled objects, such as execution instructions, cursor fetch instructions, close instructions, and release instructions, and then entering the corresponding process flow.
[0058] In some embodiments, after precompiling using the global precompiled object, if a precompiled object operation instruction is received, the target global precompiled object is located in the global memory according to the precompiled object operation instruction; the operation instruction is then executed using the found target global precompiled object. The target global precompiled object must be in an idle state to execute the operation instruction.
[0059] In some embodiments, the global memory pool is locked before searching for the target global precompiled object in the global memory according to the precompiled object operation instructions; and the lock on the global memory pool is released after the target global precompiled object is found.
[0060] In some embodiments, the global memory includes multiple memory pools, and the step of searching for the target global precompiled object in the global memory includes: obtaining a second target memory pool according to a preset mapping relationship between links and memory pools; wherein, the second target memory pool is the memory pool mapped by the link to which the precompiled object operation instruction belongs; searching for the target global precompiled object in the second target memory pool; the step of locking the global memory pool includes: locking the second target memory pool; and the step of releasing the lock on the global memory pool includes: releasing the lock on the second target memory pool.
[0061] The following briefly describes the process of receiving precompiled object operation instructions and performing corresponding operations. (See also...) Figure 6 As shown.
[0062] Step 601: Receive precompiled object operation instructions.
[0063] For example, operation instructions may include an execute request, a fetch request, a close or deallocate request, or a disconnect request.
[0064] Step 602: Obtain the second target memory pool according to the preset mapping relationship between links and memory pools.
[0065] For example, the memory pool number can be calculated based on the ID of the link where the precompiled object operation instruction is located, and the corresponding memory pool can be found.
[0066] Step 603: Lock the second target memory pool.
[0067] Step 604: Based on the mapping relationship between precompiled statements and Prepare objects, locate the target precompiled object unit in the memory pool.
[0068] For example, within the second target memory pool, the corresponding Prepare object unit is found based on the Prepare statement string or the MD5 value of the Prepare statement, or other characteristic values that can identify the pre-compiled statement.
[0069] Step 605: Release the lock on the second target memory pool.
[0070] Step 606: Locate the target global precompiled object in the target precompiled object unit.
[0071] In some embodiments, when the precompiled object operation instruction is an execute request, if there is an idle Prepare object in the Prepare object unit, the Prepare object is obtained, the Prepare object is used as the target global precompiled object, and it is set to a busy state; if all Prepare objects in the Prepare object unit are in a busy state, the system waits for one of them to change to an idle state, then obtains the idle Prepare object, uses it as the target global precompiled object, and sets it to a busy state.
[0072] In some embodiments, when the precompiled object operation instruction is a fetch request, the Prepare object bound in the execute operation is used as the target global precompiled object. It's worth noting that the server typically receives the fetch request only after the execute operation instruction has been completed.
[0073] In some embodiments, when the precompiled object operation instruction is a close or deallocate request, the reference count in the Prepare object unit is checked. If it is less than or equal to the maximum concurrency of the Prepare object, i.e., the preset value, a linked or idle Prepare object is obtained and used as the target global precompiled object.
[0074] In some embodiments, when the precompiled object operation instruction is a chain break signal, all precompiled objects bound under the chain are used as the target global precompiled object.
[0075] Step 607: Execute operation instructions using the found target global precompiled object.
[0076] In some embodiments, when the precompiled object operation instruction is an execute request, the execute operation is performed, that is, the SQL statement is executed using the precompiled object, and it is determined whether the execute request carries a cursor fetch attribute. If the execute request does not have a fetch attribute, the Prepare object is set to an idle state, and the result set of the execute operation is returned in one go; if the execute operation includes a fetch attribute, the Prepare object is bound to the chain where the operation instruction is located, and an execution success instruction is returned to the client.
[0077] In some embodiments, when the precompiled object operation instruction is a fetch request, a fetch operation is performed on the precompiled object. If this is the last fetch operation, i.e. the last time an execute result is returned, the Prepare object is removed from the link binding and the Prepare object is set to idle state; a fetch success is returned, and the result set of this fetch is returned.
[0078] In some embodiments, when the precompiled object operation instruction is a close or deallocate request, if the reference count in the Prepare object unit is less than or equal to the maximum concurrency of the Prepare object, the original close operation is performed to release the target global precompiled object memory space and decrement the precompiled object reference count by 1; if the reference count in the Prepare object unit is greater than the maximum concurrency of the Prepare object, only the reference count is decremented by 1, and close or deallocate is returned as successful.
[0079] In some embodiments, when the precompiled object operation instruction is a chain break signal, the binding relationship of all bound Prepare objects under the chain, i.e. the target global precompiled object, is removed, the memory space of all referenced Prepare objects under the chain is released, and then the chain break operation is performed.
[0080] It's worth noting that in some databases, the SQL prepared statement mode may also include a `send_long_data` process that receives binary long fields (such as blob-related types) in batches, and possibly a `reset` process. The processing of these processes is modified to first search the memory pool, then search for the Prepare object unit, and finally obtain the idle Prepare object for processing. Additionally, the `send_long_data` process also involves linking to the Prepare object being processed. The specific processes will not be listed individually.
[0081] Additionally, if all Prepare objects in a Prepare object unit are bound, a new Prepare object is cloned and set to idle for use; when removing the binding relationship, if the number of Prepare objects exceeds the maximum concurrency, an idle Prepare object is deleted.
[0082] The SQL pre-compilation method in this embodiment enables the reuse of Prepare objects within and between database links, significantly reducing memory consumption caused by duplicate Prepare objects. Combined with the introduction of multiple memory pools and a maximum concurrency limit for Prepare objects, the side effects of database performance loss can be minimized. Furthermore, a method for reusing identical fields within the same Prepare object is proposed, resulting in improved database performance rather than decreased performance. This achieves performance enhancement while significantly reducing memory consumption.
[0083] Another embodiment of this application relates to an SQL pre-compilation device. The details of this SQL pre-compilation device are described below. The following content is merely for ease of understanding and is not essential for implementing this example. Figure 7 This is a schematic diagram of the SQL pre-compilation device described in this embodiment, including: an acquisition module 701, used to acquire an SQL pre-compilation request, wherein the pre-compilation request carries a pre-compilation statement; a detection module 702, used to detect whether a global pre-compilation object corresponding to the pre-compilation statement exists in global memory; and a pre-compilation module 703, used to return a pre-compilation success instruction to the client if a global pre-compilation object corresponding to the pre-compilation statement exists; and to create a global pre-compilation object according to the pre-compilation statement and use the created global pre-compilation object for pre-compilation if no global pre-compilation object corresponding to the pre-compilation statement exists.
[0084] In some embodiments, the SQL pre-compilation apparatus further includes: an unlocking module, which is used to lock the global memory or a memory pool after obtaining the SQL pre-compilation request; and to release the lock on the global memory or a memory pool after finding the global pre-compilation object that is in an idle state and corresponds to the pre-compilation statement, or after creating the global pre-compilation object.
[0085] In some embodiments, the SQL pre-compilation apparatus further includes a target memory pool acquisition module, which is used to acquire a first target memory pool according to a preset mapping relationship between links and memory pools after the SQL pre-compilation request is acquired; wherein, the first target memory pool is the memory pool mapped by the link to which the SQL pre-compilation request belongs; the detection module 702 is further used to detect whether there is a global pre-compilation object corresponding to the pre-compilation statement in the first target memory pool; the pre-compilation module 703 is further used to create a global pre-compilation object in the first target memory pool; the lock / unlock module is further used to lock the first target memory pool and release the lock on the first target memory pool.
[0086] In some embodiments, the detection module 702 is further configured to, if a global pre-compiled object corresponding to the pre-compiled statement exists, determine whether the number of global pre-compiled objects corresponding to the pre-compiled statement in the first target memory pool reaches a preset value; if the preset value is not reached, create a global pre-compiled object according to the pre-compiled statement and use the created global pre-compiled object for pre-compilation; if the preset value is reached, proceed to the step of returning a pre-compilation success instruction to the client.
[0087] In some embodiments, the global precompiled objects corresponding to the precompiled statements in the precompiled module 703 share common fields of the precompiled objects.
[0088] In some embodiments, the pre-compilation module 703 is further configured to, after pre-compiling using the global pre-compilation object, if a pre-compilation object operation instruction is received, search for a target global pre-compilation object in the global memory according to the pre-compilation object operation instruction; and execute the operation instruction using the found target global pre-compilation object.
[0089] In some embodiments, the locking / unlocking module is further configured to lock the global memory pool before searching for the target global precompiled object in the global memory according to the precompiled object operation instructions; and release the lock on the global memory pool after the target global precompiled object is found.
[0090] In some embodiments, the detection module 702 is further configured to obtain a second target memory pool according to a preset mapping relationship between links and memory pools; wherein, the second target memory pool is the memory pool mapped by the link to which the precompiled object operation instruction belongs; the lock / unlock module is further configured to lock the second target memory pool; and release the lock on the second target memory pool.
[0091] It is not difficult to see that this embodiment is a system embodiment corresponding to the above method embodiments, and this embodiment can be implemented in conjunction with the above method embodiments. The relevant technical details and technical effects mentioned in the above embodiments are still valid in this embodiment, and will not be repeated here to reduce repetition. Accordingly, the relevant technical details mentioned in this embodiment can also be applied to the above embodiments.
[0092] It is worth mentioning that all modules involved in this embodiment are logical modules. In practical applications, a logical unit can be a physical unit, a part of a physical unit, or a combination of multiple physical units. Furthermore, to highlight the innovative aspects of this application, this embodiment does not introduce units that are not closely related to solving the technical problems proposed in this application; however, this does not mean that other units are absent in this embodiment.
[0093] Another embodiment of this application relates to an electronic device, such as... Figure 8 As shown, it includes: at least one processor 801; and a memory 802 communicatively connected to the at least one processor 801; wherein the memory 802 stores instructions executable by the at least one processor 801, the instructions being executed by the at least one processor 801 to enable the at least one processor 801 to execute the SQL pre-compilation method in the above embodiments.
[0094] The memory and processor are connected via a bus, which can include any number of interconnecting buses and bridges, connecting various circuits of one or more processors and memories. The bus can also connect various other circuits, such as peripheral devices, voltage regulators, and power management circuits, which are well known in the art and will not be described further herein. The bus interface provides an interface between the bus and the transceiver. The transceiver can be a single element or multiple elements, such as multiple receivers and transmitters, providing a unit for communicating with various other devices over a transmission medium. Data processed by the processor is transmitted over the wireless medium via an antenna, which further receives data and transmits it to the processor.
[0095] The processor manages the bus and general processing, and also provides various functions, including timing, peripheral interfaces, voltage regulation, power management, and other control functions. Memory is used to store data used by the processor during operation.
[0096] Another embodiment of this application relates to a computer-readable storage medium storing a computer program. When executed by a processor, the computer program implements the method embodiments described above.
[0097] That is, those skilled in the art will understand that all or part of the steps in the methods of the above embodiments can be implemented by a program instructing related hardware. This program is stored in a storage medium and includes several instructions to cause a device (which may be a microcontroller, chip, etc.) or processor to execute all or part of the steps of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as a USB flash drive, a portable hard drive, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk.
[0098] Those skilled in the art will understand that the above embodiments are specific implementations of this application, and in practical applications, various changes can be made in form and detail without departing from the spirit and scope of this application.
Claims
1. A method of SQL precompilation, characterized by, include: Obtain an SQL pre-compilation request, wherein the SQL pre-compilation request carries pre-compilation statements; Check if a global precompiled object corresponding to the stated precompiled statement exists in global memory; If a global precompiled object corresponding to the precompiled statement exists, determine whether the number of global precompiled objects corresponding to the precompiled statement in the first target memory pool has reached the maximum concurrency; wherein, the maximum concurrency is the maximum number of links that are allowed to access a global precompiled object simultaneously in the same memory pool; If the maximum concurrency is not reached, a global pre-compilation object is created according to the pre-compilation statement and the created global pre-compilation object is used for pre-compilation. If the maximum concurrency is reached, proceed to the step of returning a pre-compilation success instruction to the client. If no global precompiled object exists corresponding to the precompiled statement, a global precompiled object is created based on the precompiled statement, and the created global precompiled object is used for precompilation.
2. The SQL precompilation method of claim 1, wherein, Following the process of obtaining the SQL pre-compilation request, the following is also included: Lock the global memory; Following the creation of the global precompiled object, the following is also included: Release the lock on the global memory.
3. The method of claim 2, wherein, The global memory includes multiple memory pools, and after obtaining the SQL pre-compilation request, it also includes: According to the preset mapping relationship between links and memory pools, the first target memory pool is obtained; wherein, the first target memory pool is the memory pool mapped by the link to which the SQL pre-compilation request belongs; The step of detecting whether a global precompiled object corresponding to the precompiled statement exists in global memory includes: Detect whether a global precompiled object corresponding to the precompiled statement exists in the first target memory pool; The creation of the global precompiled object includes: Create a global precompiled object in the first target memory pool; The locking of the global memory includes: Lock the first target memory pool; Releasing the lock on the global memory includes: Release the lock on the first target memory pool.
4. The method of claim 3, wherein, The global precompiled objects corresponding to the precompiled statements share common fields of the precompiled objects.
5. The SQL pre-compile method of claim 1, wherein, After precompiling using the global precompiled object, the process also includes: If a precompiled object operation instruction is received, the target global precompiled object is located in the global memory according to the precompiled object operation instruction; The operation instructions are executed using the found target global precompiled object.
6. The SQL precompilation method of claim 5, wherein, Before locating the target global precompiled object in the global memory according to the precompiled object operation instructions, the process includes: Lock the global memory pool; After locating the target global precompiled object, the process also includes: Release the lock on the global memory pool.
7. The SQL pre-compilation method according to claim 6, characterized in that, The global memory includes multiple memory pools, and the process of locating the target global precompiled object in the global memory includes: According to the preset mapping relationship between links and memory pools, a second target memory pool is obtained; wherein, the second target memory pool is the memory pool mapped by the link to which the precompiled object operation instruction belongs; Locate the target global precompiled object in the second target memory pool; The locking of the global memory pool includes: Lock the second target memory pool; Releasing the lock on the global memory pool includes: Release the lock on the second target memory pool.
8. An SQL precompiler apparatus, characterized by comprising: include: The acquisition module is used to acquire SQL pre-compilation requests, wherein the pre-compilation requests carry pre-compilation statements; The detection module is used to detect whether a global pre-compiled object corresponding to the pre-compiled statement exists in global memory; if a global pre-compiled object corresponding to the pre-compiled statement exists, it determines whether the number of global pre-compiled objects corresponding to the pre-compiled statement in the first target memory pool has reached the maximum concurrency; wherein, the maximum concurrency is the maximum number of links allowed to access a global pre-compiled object simultaneously in the same memory pool; if the maximum concurrency has not been reached, a global pre-compiled object is created according to the pre-compiled statement and pre-compiled using the created global pre-compiled object; if the maximum concurrency has been reached, the module proceeds to the step of returning a pre-compilation success instruction to the client; The pre-compilation module is used to return a pre-compilation success instruction to the client when a global pre-compilation object corresponding to the pre-compilation statement exists and the number of global pre-compilation objects corresponding to the pre-compilation statement reaches the maximum concurrency; and when no global pre-compilation object corresponding to the pre-compilation statement exists, it creates a global pre-compilation object according to the pre-compilation statement and uses the created global pre-compilation object to perform pre-compilation.
9. A server, characterized by include: At least one processor; as well as, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor to enable the at least one processor to perform the SQL pre-compilation method as described in any one of claims 1 to 7.
10. A computer readable storage medium storing a computer program, characterized in that, When the computer program is executed by a processor, it implements the SQL pre-compilation method according to any one of claims 1 to 7.