SQL statement execution method and device, system, electronic equipment and storage medium
By adding a random number seed to the SQL statement to differentiate it, multiple SQL conditional statements with the same execution result are generated, which solves the mutex lock contention problem in high-concurrency scenarios and ensures the stability of the application system and the normal operation of the database.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- CHINA TELECOM CORP LTD
- Filing Date
- 2022-12-27
- Publication Date
- 2026-05-19
AI Technical Summary
In high-traffic, high-concurrency application systems, multiple sessions executing the same SQL statement in parallel can lead to mutex lock contention, causing access waits and system lag, and even abnormal CPU drops in the database server.
By generating random number seeds to add distinguishing conditions to the target SQL statement, multiple SQL conditional statements with the same execution result as the target SQL statement are generated and executed separately to achieve concurrent execution and avoid mutex lock contention.
It solves the problem of mutex lock contention in high-concurrency scenarios, ensuring the stability of the application system and the normal operation of the database server, avoiding computational burden, and is easy to implement.
Smart Images
Figure CN115982198B_ABST
Abstract
Description
Technical Field
[0001] This disclosure relates to the field of computer technology, and more specifically, to a method, apparatus, system, electronic device, and storage medium for executing SQL statements. Background Technology
[0002] With the continuous development of information technology, more and more business scenarios in application systems are running in high-traffic, high-concurrency environments. In such scenarios, multiple sessions may execute the same SQL (Structured Query Language) statement in parallel, causing mutex lock contention for that SQL statement, resulting in a large amount of access waiting, which can lead to application system lag and even abnormal CPU drops in the database server.
[0003] It should be noted that the information disclosed in the background section above is only used to enhance the understanding of the background of this disclosure, and therefore may include information that does not constitute prior art known to those skilled in the art. Summary of the Invention
[0004] The purpose of this disclosure is to provide an SQL statement execution method and apparatus, an SQL statement execution device and system, thereby solving to some extent the problem of mutex lock contention caused by multiple sessions executing the same SQL statement in parallel, resulting in access problems and system lag.
[0005] According to a first aspect of this disclosure, a method for executing SQL statements is provided. The method includes: obtaining a target Structured Query Language (SQL) statement to be executed for a target business scenario, wherein the target SQL statement is a concurrent SQL statement under the target business scenario and is a SQL statement with bind variables; generating a random number seed and adding distinguishing conditions to the target SQL statement according to the random number seed to obtain multiple corresponding SQL condition statements; the execution result of each SQL condition statement is the same as the execution result of the target SQL statement; and executing multiple SQL condition statements respectively to achieve concurrent execution of the target SQL statement.
[0006] Optionally, generating the random number seed includes: generating the random number seed based on the current time of the operating system, wherein the random number seed is determined based on microsecond-level time.
[0007] Optionally, adding distinguishing conditions to the target SQL statement based on the random number seed includes: generating a random number sequence based on the random number seed and the concurrency of the target SQL statement; adding distinguishing conditions to the target SQL statement based on the random number sequence to form multiple SQL condition statements, wherein one random number in the random number sequence corresponds to one SQL condition statement.
[0008] Optionally, adding distinguishing conditions to the target SQL statement based on the random number sequence includes: performing a modulo operation on each random number in the random number sequence with a preset first value; summing the result of each modulo operation with a preset second value to obtain a condition value; generating a corresponding distinguishing condition based on the condition value, with one condition value corresponding to one distinguishing condition; and adding the distinguishing condition to the target SQL statement to obtain a corresponding SQL condition statement.
[0009] Optionally, the second value is determined based on the number of physical machines corresponding to the application system.
[0010] Optionally, obtaining the target SQL statement includes: in response to receiving a data request message, parsing the data request message to obtain a first SQL statement; in response to the first SQL statement being included in a second SQL statement set, determining the first SQL statement as the target SQL statement, wherein the second SQL statement set is a set of SQL statements configured for the target business scenario.
[0011] According to a second aspect of this disclosure, an SQL statement execution apparatus is provided, the apparatus comprising: an acquisition module, a condition addition module, and an execution module, wherein: the acquisition module is configured to acquire a target Structured Query Language (SQL) statement to be executed for a target business scenario, the target SQL statement being a concurrent SQL statement under the target business scenario, and the target SQL statement being an SQL statement with bind variables; the condition addition module is configured to generate a random number seed and, based on the random number seed, add distinguishing conditions to the target SQL statement to obtain multiple corresponding SQL condition statements; the execution result of each SQL condition statement is the same as the execution result of the target SQL statement; the execution module is configured to execute multiple SQL condition statements respectively to achieve concurrent execution of the target SQL statement.
[0012] According to a third aspect of this disclosure, a computer-readable storage medium is provided that stores a computer program thereon, which, when executed by a processor, implements the methods of any of the above embodiments.
[0013] According to a fourth aspect of this disclosure, an SQL statement execution apparatus is provided, comprising: a processor; and a memory for storing executable instructions of the processor; wherein the processor is configured to perform the method of any of the above embodiments by executing the executable instructions.
[0014] According to a fifth aspect of this disclosure, a data acquisition system is provided, the system comprising an application terminal and a database server, the application terminal comprising the apparatus described in any of the above embodiments, the application terminal acquiring corresponding data from the database server by executing multiple SQL conditional statements.
[0015] The exemplary embodiments disclosed herein may have some or all of the following beneficial effects:
[0016] In the SQL statement execution method provided in the exemplary embodiments of this disclosure, on the one hand, for target SQL statements in high-concurrency, high-traffic target business scenarios, a distinguishing condition is added to it using a random number seed, generating multiple SQL condition statements with the same execution result. By executing multiple SQL condition statements separately, the problem of mutex lock contention caused by parallel execution of the target SQL statement and the application system lag caused by a large number of access waits are avoided. This solves the problem of abnormal CPU drop in the database server of the application system and ensures the stability of the application system. On the other hand, adding distinguishing conditions based on a random number seed can ensure the divergence of data, thereby ensuring the validity of the distinguishing conditions and solving the problem of database instability caused by mutex lock contention in high-frequency SQL execution. In addition, this disclosure does not impose a computational burden on the application system and is easy to implement.
[0017] It should be understood that the above general description and the following detailed description are exemplary and explanatory only, and are not intended to limit this disclosure. Attached Figure Description
[0018] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this disclosure and, together with the description, serve to explain the principles of this disclosure. It is obvious that the drawings described below are merely some embodiments of this disclosure, and those skilled in the art can obtain other drawings based on these drawings without any inventive effort.
[0019] Figure 1 The schematic diagram illustrates a system architecture of an SQL statement execution method and apparatus according to an embodiment of the present disclosure.
[0020] Figure 2 One of the schematic diagrams illustrates a flowchart of an SQL statement execution method according to an embodiment of the present disclosure.
[0021] Figure 3The illustration shows a schematic diagram of a process for forming multiple SQL conditional statements according to an embodiment of the present disclosure.
[0022] Figure 4 The second schematic diagram illustrates a flowchart of an SQL statement execution method according to an embodiment of the present disclosure.
[0023] Figure 5 A schematic block diagram of an SQL statement execution apparatus according to an embodiment of the present disclosure is shown.
[0024] Figure 6 An exemplary electronic device block diagram according to one embodiment of the present disclosure is illustrated schematically. Detailed Implementation
[0025] Exemplary embodiments will now be described more fully with reference to the accompanying drawings. However, these exemplary embodiments can be implemented in many forms and should not be construed as limited to the examples set forth herein; rather, these embodiments are provided to make this disclosure more comprehensive and complete, and to fully convey the concept of the exemplary embodiments to those skilled in the art. The described features, structures, or characteristics can be combined in any suitable manner in one or more embodiments. In the following description, numerous specific details are provided to give a full understanding of embodiments of this disclosure. However, those skilled in the art will recognize that the technical solutions of this disclosure can be practiced with one or more specific details omitted, or other methods, components, apparatus, steps, etc., can be employed. In other instances, well-known technical solutions are not shown or described in detail to avoid obscuring various aspects of this disclosure.
[0026] Furthermore, the accompanying drawings are merely illustrative of this disclosure and are not necessarily drawn to scale. The same reference numerals in the drawings denote the same or similar parts, and therefore repeated descriptions of them will be omitted. Some block diagrams shown in the drawings are functional entities and do not necessarily correspond to physically or logically independent entities. These functional entities may be implemented in software, in one or more hardware modules or integrated circuits, or in different network and / or processor devices and / or microcontroller devices.
[0027] Figure 1 A schematic diagram of an exemplary system architecture 100 to which embodiments of the present disclosure of an SQL statement execution method and apparatus can be applied is shown. Figure 1 As shown, system architecture 100 may include a terminal 110 and a database server 120. Applications can be installed on the terminal 110, and the database server 120 can be a database server; the terminal may also have a backend server.
[0028] Terminal 110 can obtain the target Structured Query Language (SQL) statement to be executed for the target business scenario. The target SQL statement is a concurrent SQL statement under the target business scenario and is a SQL statement with bound variables. It generates a random number seed and adds distinguishing conditions to the target SQL statement based on the random number seed to obtain multiple corresponding SQL condition statements. The execution result of each SQL condition statement is the same as the execution result of the target SQL statement. Multiple SQL condition statements are executed separately to achieve concurrent execution of the target SQL statement, thereby retrieving corresponding data from the database server 120. Terminal 110 can be any terminal device capable of installing applications, such as a mobile phone, computer, smart voice interaction device, smart home appliance, in-vehicle terminal, test terminal, or computer; this example does not limit this.
[0029] Database server 120 can be a standalone physical server, or a server cluster or distributed system consisting of multiple physical servers. Multiple servers can form a blockchain, and the server is a node on the blockchain. Database server 120 can also be a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, CDN (Content Delivery Network), and big data and artificial intelligence platforms.
[0030] The SQL statement execution method provided in this embodiment can be executed in terminal 110. Accordingly, the SQL statement execution device is generally set in terminal 110.
[0031] To facilitate understanding of this disclosure, the following terms will be introduced.
[0032] SQL is a language used to manipulate databases. It can be used to access, query, update, and manage relational database systems.
[0033] Oracle Database: A database management system developed by Oracle Corporation. Oracle Database offers complete data management capabilities, a full suite of relational products, and distributed processing capabilities. It is currently the most widely used database management system. As a general-purpose database system, it possesses comprehensive data management functions; as a relational database, it offers a complete relational product; and as a distributed database, it implements distributed processing capabilities.
[0034] The technical solutions of the embodiments of this disclosure are described in detail below:
[0035] refer to Figure 2As shown, an example implementation of the SQL statement execution method provided in this disclosure may include the following steps S210 to S230.
[0036] Step S210: Obtain the target structured query language SQL statement to be executed for the target business scenario. The target SQL statement is a concurrent SQL statement under the target business scenario and is an SQL statement with bound variables.
[0037] Step S220: Generate a random number seed, and add distinguishing conditions to the target SQL statement according to the random number seed to obtain multiple corresponding SQL condition statements; the execution result of each SQL condition statement is the same as the execution result of the target SQL statement.
[0038] Step S230: Execute multiple SQL conditional statements to achieve concurrent execution of the target SQL statement.
[0039] The SQL statement execution method provided in this disclosure, on the one hand, for target SQL statements in high-concurrency, high-traffic target business scenarios, adds distinguishing conditions using a random number seed, generating multiple SQL condition statements with the same execution result. By executing multiple SQL condition statements separately, the problem of mutex lock contention caused by parallel execution of the target SQL statement and the application system lag caused by a large number of access waits are avoided. This solves the problem of abnormal CPU drop in the database server of the application system and ensures the stability of the application system. On the other hand, adding distinguishing conditions based on a random number seed can ensure the divergence of data, thereby ensuring the validity of the distinguishing conditions and solving the problem of database instability caused by mutex lock contention in high-frequency SQL execution. In addition, this disclosure does not impose a computational burden on the application system and is easy to implement.
[0040] In step S210, the target structured query language (SQL) statement to be executed for the target business scenario is obtained.
[0041] In this example implementation, the target SQL statement refers to an SQL statement executed frequently in the target business scenario (e.g., an SQL statement whose execution frequency exceeds a preset threshold within a preset time period), and which is an SQL statement with bound variables. The target business scenario refers to a business scenario involving high-frequency SQL execution under high-traffic, high-concurrency conditions, such as broadband authentication, user authentication, or user login to a broadband account. For example, the SQL statement execution flow for the target business scenario can be pre-configured, and the corresponding SQL statement (target SQL statement) can be executed according to the steps and flow of this disclosure within the target business scenario.
[0042] In this example implementation, a bind variable refers to storing different values of the conditional predicates in an SQL statement in an intermediate variable. When the system performs a hash operation on each SQL statement request, it generates the same hash value, thus using the same execution plan to execute as a single SQL statement.
[0043] In this example implementation, the target SQL statement can be entered by the user / system administrator or generated in response to the corresponding operation of the user / system administrator. In this case, a correspondence can be established between the front-end operation and the SQL statement in advance, or the target SQL statement can be received by the user through an application that can execute SQL statements. This example does not limit this.
[0044] For example, in response to receiving a data request message, the data request message can be parsed to obtain a first SQL statement.
[0045] In this example implementation, the data request message may be generated by the user through application input or based on the user's actions in the application; this example does not limit this.
[0046] In response to the first SQL statement being included in the second SQL statement set, the first SQL statement is determined to be the target SQL statement, and the second SQL statement set is the SQL statements configured for the target business scenario.
[0047] In this example implementation, the second SQL statement set is a pre-configured set of SQL statements for high-traffic, high-concurrency SQL statement business scenarios. When a data request message contains statements from this set, the first SQL statement of the current business is determined to be the target SQL statement.
[0048] In step S220, a random number seed is generated, and based on the random number seed, a distinguishing condition is added to the target SQL statement to obtain multiple corresponding SQL condition statements; the execution result of each SQL condition statement is the same as the execution result of the target SQL statement.
[0049] In this example implementation, to ensure distinct distinguishing conditions, these conditions are generated based on a random number seed. The diversity of the distinguishing conditions is guaranteed by the divergence of the random number seed. By adding distinguishing conditions, the target SQL statement, which is executed concurrently at high frequency, is transformed into multiple SQL conditional statements. The execution results of these multiple SQL conditional statements are the same as the execution result of the target SQL statement. However, for a database system (such as Oracle), these multiple SQL conditional statements are different SQL statements. Therefore, waiting during the execution of this statement can be avoided, thereby resolving the resulting system lag problem.
[0050] The execution of an SQL statement typically includes stages such as parsing, execution, and extraction. Among these, parsing is the most resource-intensive. During parsing, syntax, semantics, and permissions checks are performed. If all checks pass, the SQL statement is executed, and its execution plan is then stored in a shared pool. This ensures that if a similar SQL statement needs to be executed later, it can be executed according to the plan stored in the shared pool without further parsing.
[0051] For example, in an Oracle database system, if the same SQL statement is executed too frequently within a certain period, it can lead to mutex contention on the child cursor. The child cursor stores different execution plans generated based on the optimizer mode, input values, indexes, etc. A mutex is a lightweight memory structure that ensures only one thread uses a shared resource at a time when two or more threads need to access it simultaneously. When multiple sessions request the parallel execution of the same SQL statement, mutex contention (soft parse) occurs on the child cursor of that SQL statement, resulting in a large amount of access waiting and causing application system lag. By adding differentiating conditions, the frequently executed identical SQL statement can be transformed into different SQL conditional statements, thus avoiding mutex contention and system lag.
[0052] For example, refer to Figure 3 Multiple SQL conditional statements can be generated through the following steps.
[0053] Step S310: Generate a random number seed based on the current time of the operating system.
[0054] In this example implementation, during application runtime, in order to ensure that the random numbers are sufficiently diverse in a high-traffic, high-concurrency operating environment, a random number seed can be generated based on the current time of the operating system. For example, a random number seed X can be generated based on the current microsecond-level time of the operating system, X = 1000000 * T1 + T2, where T1 is the second-level data and T2 is the microsecond-level data.
[0055] Step S320: Generate a random number sequence based on the random number seed and the concurrency of the target SQL statement.
[0056] In this example implementation, a random number generation algorithm is used to generate a random number sequence based on a random number seed, so that the number of random numbers in the random number sequence is the same as the number of concurrent requests in the target SQL statement, thereby enabling one random number in the random number sequence to generate a distinguishing condition (corresponding to one SQL condition statement).
[0057] Step S330: Perform a modulo operation between each random number in the random number sequence and a preset first value.
[0058] In this example implementation, the first value can be set according to the actual situation. The first value is used to control the value within the range of the modulus. For example, if the first value is 20, it is used to control the calculation result to be within 20.
[0059] Step S340: Sum the result of each modulo operation with a preset second value to obtain the conditional value.
[0060] In this example implementation, the second value can be determined based on the number of physical machines corresponding to the application. For example, different second values can be set for different physical machines. The number of second values can be equal to or greater than the number of physical machines to ensure that the condition values determined by different physical machines for different applications are different, so that the condition values are sufficiently divergent.
[0061] Step S350: Based on the condition values, generate corresponding distinguishing conditions, with one distinguishing condition generated for each condition value.
[0062] In this example implementation, the identity principle can be used to generate distinguishing conditions based on conditional values. For example, the distinguishing condition can be N = N, where N is a conditional value, which can be an integer. N = rand()%N1+N2, where rand is a random number generator, % represents a modulo operation, N1 is the first value, and N2 is the second value.
[0063] Step S360: Add the distinguishing conditions to the target SQL statement to obtain the corresponding SQL condition statement.
[0064] In this example implementation, the distinguishing condition can be added after the target SQL statement to form an SQL conditional statement. This way, for SQL statements with bound variables, the original SQL condition result can be kept unchanged.
[0065] By following the steps above, we can add distinguishing conditions to the target SQL statement based on the random number sequence, forming multiple SQL condition statements. One random number in the random number sequence corresponds to one SQL condition statement.
[0066] In step S230, multiple SQL conditional statements are executed to achieve concurrent execution of the target SQL statement.
[0067] In this example implementation, the application system treats different SQL conditional statements as different SQL statements, thus avoiding mutex lock contention issues that arise when accessing the shared pool during the execution of the same SQL statement. Since the execution results of multiple SQL conditional statements are the same, concurrent execution of the target SQL statement can be achieved by executing multiple SQL conditional statements.
[0068] In some embodiments, reference Figure 4 The SQL statement execution method may include the following steps S401-S409.
[0069] Step S401: Locally identify the business scenario with frequently executed SQL statements in a high-traffic, high-concurrency business environment as the target business scenario.
[0070] In step S402, the user sends a data request message for the target business scenario through the application client.
[0071] Step S403: Parse the data request message locally to obtain the target SQL statement.
[0072] Step S404: Generate a random number seed locally based on the current time of the operating system.
[0073] Step S405: Generate a random number sequence locally based on the random number seed and the concurrency of the target SQL statement.
[0074] Step S406: Locally, based on the random number sequence, add distinguishing conditions to the target SQL statement to form multiple SQL conditional statements.
[0075] In step S407, the local execution engine executes multiple SQL conditional statements respectively.
[0076] Step S408: The database server searches for the corresponding requested data.
[0077] In step S409, the database server returns the search results to the client.
[0078] The order of the steps in the above embodiments is merely exemplary and can be adjusted as needed. Detailed descriptions of each step in the above embodiments can be found in the corresponding descriptions in the foregoing embodiments, and will not be repeated here.
[0079] In the daily operation and maintenance of core operating systems of telecommunications operators, under high-traffic, high-concurrency service environments, even without hardware failures or abnormal traffic, Oracle database servers frequently experience sudden and abnormal CPU drops, sometimes even leading to database system failures. This is highly likely due to certain SQL statements being executed too frequently within a certain period, causing contention for mutex operations on the child current register. When multiple sessions need to execute the same SQL statement in parallel, it triggers mutex contention on the child current register, also known as COUSOR:PIN S wait. A large number of waits can cause application system lag.
[0080] This disclosure addresses the issue of database instability caused by high-frequency SQL execution contention by utilizing the identity principle and a microsecond-level random number seed to control the random number within a certain range. This transforms a single SQL statement into multiple SQL statements and ensures sufficient dispersion of SQL execution within a given timeframe. It effectively improves the stability of Oracle database application systems and possesses good practicality and versatility. This disclosure can be used for maintaining the stability of Oracle databases accessed frequently by telecom operators.
[0081] See Figure 5 This example embodiment also provides an SQL statement execution device 500; the device 500 may include an acquisition module 510, a condition addition module 520, and an execution module 530, wherein: the acquisition module 510 is used to acquire a target Structured Query Language (SQL) statement to be executed for a target business scenario, the target SQL statement being a concurrent SQL statement in the target business scenario, and the target SQL statement being an SQL statement with bound variables; the condition addition module 520 is used to generate a random number seed and add distinguishing conditions to the target SQL statement according to the random number seed to obtain multiple corresponding SQL condition statements; the execution result of each SQL condition statement is the same as the execution result of the target SQL statement; the execution module 530 is used to execute multiple SQL condition statements respectively to achieve concurrent execution of the target SQL statement.
[0082] In one embodiment of this disclosure, the condition addition module 520 is further configured to generate a random number seed based on the current time of the operating system, wherein the random number seed is determined based on microsecond-level time.
[0083] In one embodiment of this disclosure, the condition addition module 520 is further configured to: generate a random number sequence based on the random number seed and the concurrency of the target SQL statement; add distinguishing conditions to the target SQL statement based on the random number sequence to form multiple SQL condition statements, wherein one random number in the random number sequence corresponds to one SQL condition statement.
[0084] In one embodiment of this disclosure, the condition addition module 520 is further configured to: perform a modulo operation on each random number in the random number sequence with a preset first value; sum the result of each modulo operation with a preset second value to obtain a condition value; generate a corresponding distinguishing condition based on the condition value, with one condition value corresponding to one distinguishing condition; and add the distinguishing condition to the target SQL statement to obtain the corresponding SQL condition statement.
[0085] In one embodiment of this disclosure, the second value is determined based on the number of physical machines corresponding to the application system.
[0086] In one embodiment of this disclosure, the acquisition module 510 is further configured to: in response to receiving a data request message, parse the data request message to obtain a first SQL statement; in response to the first SQL statement being included in a second SQL statement set, determine the first SQL statement as a target SQL statement, and the second SQL statement set being SQL statements configured for a target business scenario.
[0087] The specific details of each module / unit involved in the SQL statement execution device in the above embodiments have been described in detail in the corresponding SQL statement execution methods, so they will not be repeated here.
[0088] In another aspect, this application also provides a computer-readable medium, which may be included in the device described in the above embodiments; or it may exist independently and not assembled into the device. The computer-readable medium carries one or more programs that, when executed by the device, cause the device to implement the methods as described in the following embodiments. For example, the device may implement... Figures 2-4 The various steps shown are as follows.
[0089] It should be noted that the computer-readable medium disclosed herein may be a computer-readable signal medium or a computer-readable storage medium, or any combination thereof. A computer-readable storage medium may be, for example,—but not limited to—an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of a computer-readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage device, magnetic storage device, or any suitable combination thereof. In this disclosure, a computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device. In this disclosure, a computer-readable signal medium may include a data signal propagated in baseband or as part of a carrier wave, carrying computer-readable program code. Such propagated data signals may take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. Computer-readable signal media can also be any computer-readable medium other than computer-readable storage media, which can send, propagate, or transmit a program for use by or in connection with an instruction execution system, apparatus, or device. The program code contained on the computer-readable medium can be transmitted using any suitable medium, including but not limited to: wireless, wire, optical fiber, RF, etc., or any suitable combination thereof.
[0090] Furthermore, in exemplary embodiments of this disclosure, an apparatus capable of implementing the above-described methods is also provided. Those skilled in the art will understand that various aspects of this disclosure can be implemented as systems, methods, or program products. Therefore, various aspects of this disclosure can be specifically implemented as entirely hardware embodiments, entirely software embodiments (including firmware, microcode, etc.), or embodiments combining hardware and software aspects, collectively referred to herein as "circuit," "module," or "system."
[0091] See Figure 6 , Figure 6 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. For example... Figure 6 As shown, the electronic device 600 includes a processor 610, a memory 620, an input / output interface 630, and a communication bus 640. The processor 610 is connected to the memory 620 and the input / output interface 630; for example, the processor 610 can be connected to the memory 620 and the input / output interface 630 via the communication bus 640. The processor 610 is configured to support the electronic device in performing... Figures 2-4The processor 610 can be a Central Processing Unit (CPU), a Network Processor (NP), a hardware chip, or any combination thereof. The aforementioned hardware chip can be an Application-Specific Integrated Circuit (ASIC), a Programmable Logic Device (PLD), or a combination thereof. The aforementioned PLD can be a Complex Programmable Logic Device (CPLD), a Field-Programmable Gate Array (FPGA), a Generic Array Logic (GAL), or any combination thereof. The memory 620 is used to store program code, etc. Memory 620 may include volatile memory (VM), such as random access memory (RAM); memory 620 may also include non-volatile memory (NVM), such as read-only memory (ROM), flash memory, hard disk drive (HDD), or solid-state drive (SSD); memory 620 may also include combinations of the above types of memory.
[0092] The input / output interface 630 is used for inputting or outputting data.
[0093] Processor 610 can call the above program code to perform the following operations:
[0094] Obtain the target Structured Query Language (SQL) statement to be executed for the target business scenario. The target SQL statement is a concurrent SQL statement under the target business scenario and is a SQL statement with bound variables. Generate a random number seed and add distinguishing conditions to the target SQL statement based on the random number seed to obtain multiple corresponding SQL condition statements. The execution result of each SQL condition statement is the same as the execution result of the target SQL statement. Execute multiple SQL condition statements to achieve concurrent execution of the target SQL statement.
[0095] Optionally, the processor 610 described above can also generate a random number seed and perform the following operations: generate a random number seed based on the current time of the operating system, wherein the random number seed is determined based on microsecond-level time.
[0096] Optionally, the processor 610 can also add distinguishing conditions to the target SQL statement based on the random number seed and perform the following operations: generate a random number sequence based on the random number seed and the concurrency of the target SQL statement; add distinguishing conditions to the target SQL statement based on the random number sequence to form multiple SQL condition statements, with one random number in the random number sequence corresponding to one SQL condition statement.
[0097] Optionally, the processor 610 can also add distinguishing conditions to the target SQL statement based on the random number sequence and perform the following operations: perform a modulo operation on each random number in the random number sequence with a preset first value; sum the result of each modulo operation with a preset second value to obtain a condition value; generate corresponding distinguishing conditions based on the condition values, with one condition value corresponding to one distinguishing condition; add the distinguishing conditions to the target SQL statement to obtain the corresponding SQL condition statement.
[0098] Optionally, the second value is determined based on the number of physical machines corresponding to the application system.
[0099] Optionally, the processor 610 may also acquire the target SQL statement and perform the following operations: in response to receiving a data request message, parse the data request message to acquire the first SQL statement; in response to the first SQL statement being included in the second SQL statement set, determine that the first SQL statement is the target SQL statement, and the second SQL statement set is the SQL statement configured for the target business scenario.
[0100] It should be noted that the implementation of each operation can also be referenced accordingly. Figures 2-4 The corresponding description of the method embodiment shown; the processor 610 described above can also cooperate with the input / output interface 630 to perform other operations in the above method embodiment.
[0101] In some embodiments, this application also provides a data acquisition system, which may include an application terminal and a database server. The application terminal may include any of the SQL statement execution devices in the above embodiments. The application terminal obtains corresponding data from the database server by executing multiple SQL conditional statements.
[0102] This disclosure only requires business logic modifications on the application side, making it easy to implement and deploy.
[0103] From the above description of the embodiments, those skilled in the art will readily understand that the exemplary embodiments described herein can be implemented by software or by combining software with necessary hardware. Therefore, the technical solutions according to the embodiments of this disclosure can be embodied in the form of a software product, which can be stored in a non-volatile storage medium (such as a CD-ROM, USB flash drive, external hard drive, etc.) or on a network, and includes several instructions to cause a device to execute the method according to the embodiments of this disclosure.
[0104] Furthermore, the above figures are merely illustrative of the processes included in the method according to exemplary embodiments of this disclosure and are not intended to be limiting. It is readily understood that the processes shown in the above figures do not indicate or limit the temporal order of these processes. Additionally, it is readily understood that these processes may be executed synchronously or asynchronously, for example, in multiple modules.
[0105] It should be noted that although the steps of the method in this disclosure are described in a specific order in the accompanying drawings, this does not require or imply that the steps must be performed in that specific order, or that all the steps shown must be performed to achieve the desired result. Additional or alternative steps, such as omitting certain steps, combining multiple steps into one step, and / or breaking down one step into multiple steps, should all be considered part of this disclosure.
[0106] It should be understood that this disclosure, as disclosed and defined herein, extends to all alternative combinations of two or more individual features mentioned or apparent in the text and / or figures. All these different combinations constitute multiple alternative aspects of this disclosure. The embodiments described in this specification illustrate the best known mode for implementing this disclosure and will enable those skilled in the art to utilize it.
Claims
1. A method for executing SQL statements, characterized in that, The method includes: Obtain the target Structured Query Language (SQL) statement to be executed for the target business scenario. The target SQL statement is a concurrent SQL statement under the target business scenario, and the target SQL statement is an SQL statement that binds variables. A random number seed is generated, and a distinguishing condition is added to the target SQL statement based on the random number seed to obtain multiple corresponding SQL condition statements; the execution result of each SQL condition statement is the same as the execution result of the target SQL statement. Execute multiple of the aforementioned SQL conditional statements to achieve concurrent execution of the target SQL statement; The corresponding multiple SQL condition statements are determined as follows: a random number sequence is generated based on the random number seed and the concurrency of the target SQL statement; each random number in the random number sequence is moduloed with a preset first value; the result of each modulo operation is summed with a preset second value to obtain a condition value; a corresponding distinguishing condition is generated based on the condition value; and the distinguishing condition is added to the target SQL statement to obtain the corresponding SQL condition statement.
2. The method according to claim 1, characterized in that, The random number seed generation includes: The random number seed is generated based on the current time of the operating system, and the random number seed is determined based on microsecond-level time.
3. The method according to claim 1, characterized in that, One random number in the random number sequence corresponds to one SQL condition statement.
4. The method according to claim 1, characterized in that, One conditional value corresponds to one distinguishing condition.
5. The method according to claim 4, characterized in that, The second value is determined based on the number of physical machines corresponding to the application system.
6. The method according to claim 1, characterized in that, Retrieve the target SQL statement, including: In response to receiving a data request message, the data request message is parsed to obtain a first SQL statement; In response to the fact that the first SQL statement is included in the second SQL statement set, the first SQL statement is determined to be the target SQL statement, and the second SQL statement set is the SQL statement configured for the target business scenario.
7. An SQL statement execution device, characterized in that, The device includes: The acquisition module is used to acquire the target structured query language SQL statement to be executed for the target business scenario. The target SQL statement is a concurrent SQL statement under the target business scenario, and the target SQL statement is an SQL statement that binds variables. The condition addition module is used to generate a random number seed and add distinguishing conditions to the target SQL statement according to the random number seed to obtain multiple corresponding SQL condition statements; the execution result of each SQL condition statement is the same as the execution result of the target SQL statement. An execution module is used to execute multiple SQL conditional statements respectively, so as to achieve concurrent execution of the target SQL statement; The corresponding multiple SQL condition statements are determined as follows: a random number sequence is generated based on the random number seed and the concurrency of the target SQL statement; each random number in the random number sequence is moduloed with a preset first value; the result of each modulo operation is summed with a preset second value to obtain a condition value; a corresponding distinguishing condition is generated based on the condition value; and the distinguishing condition is added to the target SQL statement to obtain the corresponding SQL condition statement.
8. A data acquisition system, characterized in that, The system includes an application terminal and a database server terminal. The application terminal includes the apparatus as described in claim 7. The application terminal obtains corresponding data from the database server terminal by executing multiple SQL conditional statements.
9. An electronic device, characterized in that, include: processor; as well as A memory for storing executable instructions of the processor; wherein the processor is configured to perform the method of any one of claims 1-6 by executing the executable instructions.
10. A computer storage medium, characterized in that, It stores a computer program, which, when executed by a processor, implements the method described in any one of claims 1-6.