SQL (Structured Query Language) statement optimization method and equipment

Abstract

The embodiment of the invention provides an SQL statement optimization method and device, and relates to the technical field of software engineering. The method comprises the steps that a target SQL statement and environment information are obtained, the target SQL statement is an SQL statement with the execution efficiency lower than expected efficiency, the environment information is used for describing the environment involved when the target SQL statement is executed, and the environment comprises a software environment and / or a hardware environment; according to the target SQL statement and the environment information, the reason that the execution efficiency of the target SQL statement is lower than the expected efficiency is determined, and the reason at least comprises defects existing in the target SQL statement and / or defects existing in the environment; according to the reason, a target strategy is determined, and the target strategy is used for indicating optimization of defects existing in the target SQL statement and / or defects existing in the environment.

Description

Technical Field

[0001] The present application relates to the field of software engineering technology, and in particular to a method and device for optimizing SQL statements. Background Art

[0002] In software development, data is not only a fundamental resource but also the key link between business logic and real-world applications, occupying a central position. Structured Query Language (SQL) is a key tool for executing data queries. In real-world applications, inefficient database SQL statements can cause user lag during database use and slow down the responsiveness of the database management system. To address this, developers and operations personnel need to identify and optimize inefficient SQL statements.

[0003] Currently, related technologies often identify inefficient SQL statements based on their execution time. Development / operations personnel then use their experience to determine the causes of these inefficient SQL statements and modify them. However, there are many causes of inefficient SQL statements, and SQL statement writing logic is often complex. Different database operators also have specific business specifications for SQL statement writing. Therefore, identifying the causes of inefficient SQL statements and modifying them to meet business specifications significantly challenges personnel's professional skills and familiarity with the business, making SQL statement performance optimization difficult and inefficient. Summary of the Invention

[0004] The embodiments of the present application provide a SQL statement optimization method, apparatus, computing device, computer storage medium, and computer program product, which can assist development / operation and maintenance personnel in optimizing SQL statements, reduce the difficulty of SQL statement optimization, and improve optimization efficiency.

[0005] In a first aspect, an embodiment of the present application provides a SQL statement optimization method, the method comprising: obtaining a target SQL statement and environment information, the target SQL statement being an SQL statement whose execution efficiency is lower than an expected efficiency, the environment information being used to describe the environment involved when executing the target SQL statement, the environment including the software environment and / or the hardware environment; determining, based on the target SQL statement and the environment information, the reason why the execution efficiency of the target SQL statement is lower than the expected efficiency, the reason including at least defects in the target SQL statement and / or defects in the environment; determining, based on the reason, a target strategy, the target strategy being used to indicate optimization of defects in the target SQL statement and / or defects in the environment.

[0006] In this embodiment, when a user terminal requests the execution of an SQL statement against a database, the computing device can obtain the target SQL statement being executed on the database—that is, the SQL statement whose execution efficiency is lower than the expected efficiency—as well as the environmental information associated with the target SQL statement. This allows the computing device to determine the cause of the target SQL statement's inefficiency by analyzing the target SQL statement itself and the environmental information. This allows the computing device to accurately pinpoint the cause, both at the level of defects in the target SQL statement itself and in the hardware and software environment. This allows the computing device to determine the appropriate target strategy and guide the user in optimizing SQL statement performance, reducing the difficulty of SQL statement optimization and improving its efficiency.

[0007] In some possible implementations, obtaining a target SQL statement includes: intercepting, through an interceptor, the SQL statement executed in the database and the execution information of the SQL statement, where the execution information includes at least the execution time and / or the resource consumption; and determining, based on the execution information, a target SQL statement whose execution efficiency is lower than the expected efficiency from the SQL statement, where the execution efficiency is lower than the expected efficiency as the execution time exceeds a preset time or the resource consumption exceeds a preset resource amount.

[0008] In this way, by intercepting the target SQL statement and execution information through the interceptor to determine the target SQL statement, compared with directly obtaining the target SQL statement and execution information from the database log, it can avoid increasing the database load and improve the real-time performance of analysis and optimization.

[0009] In some possible implementations, the defects in the target SQL statement include one or more of execution logic defects, index usage defects, query expansion, and lock contention; based on the cause, a target strategy is determined, including: if the cause includes an execution logic defect in the target SQL statement, a first target strategy is determined, the first target strategy is used to instruct to reduce complex nesting and inefficient operators in the target SQL statement to simplify the execution logic of the target SQL statement, and an inefficient operator refers to an SQL operator that reduces execution efficiency; if the cause includes an index usage defect in the target SQL statement, a second target strategy is determined, the second target strategy is used to instruct to correct necessary indexes in the target SQL statement and make the composite index comply with the left-most prefix principle; if the cause includes a query expansion defect in the target SQL statement, a third target strategy is determined, the third target strategy is used to instruct to use paging queries and optimize query fields in the target SQL statement to reduce the returned data; if the cause includes a lock contention defect in the target SQL statement, a fourth target strategy is determined, the fourth target strategy is used to instruct to shorten the holding time of the lock, which is the lock triggered by the target SQL statement.

[0010] In this way, through fine-grained optimization strategies, corresponding target strategies can be matched to various defects in the target SQL statements to guide personnel to optimize SQL statements. It has a high degree of intelligence and is conducive to improving optimization efficiency.

[0011] In some possible implementations, the software environment includes database tables involved in executing the target SQL statement, and the environment information includes at least statistical information of the database table, which is used to describe one or more of the data volume, number of indexes, table structure, data type, and number of add, delete, modify, and query operations of the database table.

[0012] That is to say, in addition to analyzing the defects of the SQL statement itself, this embodiment can also analyze from the database table level, expand the sub-dimensions, and optimize the SQL statement performance more accurately and effectively.

[0013] In some possible implementations, a target strategy is determined based on the cause, including: if the cause includes an excessive amount of data in the database table, a fifth target strategy is determined, which is used to instruct archiving historical data from the database table or using partitioned tables, summary tables, or materialized views to optimize the database table; if the cause includes an unreasonable number of indexes in the database table, a sixth target strategy is determined, which is used to instruct adding necessary indexes to the database table or deleting redundant indexes; if the cause includes structural defects in the database table, a seventh target strategy is determined, which is used to instruct adjusting the table structure or data type of the database table; if the cause includes an excessive number of add, delete, modify, and query operations on the database table, an eighth target strategy is determined, which is used to instruct adjusting the execution logic in the target SQL statement to merge or batch execute operations of the same type, or optimizing the database table indexes to improve the execution efficiency of the target SQL statement.

[0014] In this way, through fine-grained optimization strategies, corresponding target strategies can be matched to various defects in database tables to guide personnel to optimize SQL statements. This is highly intelligent and helps improve optimization efficiency.

[0015] In some possible implementations, the software environment includes a database involved in executing a target SQL statement, and the environmental information includes at least performance information of the database; based on the cause, a target strategy is determined, including: if the cause includes that the database is in an overloaded state, then a target strategy is determined, wherein the overloaded state indicates that the database processes an excessive number of concurrent requests, and the target strategy is used to indicate increasing the database connection pool size to accommodate the connections of concurrent requests, or limiting the frequency or number of concurrent requests through a current limiting strategy.

[0016] In this way, the causes of the target SQL statements are analyzed from the perspective of database defects, and corresponding target strategies are given to guide personnel to optimize SQL statements. This is highly intelligent and helps improve optimization efficiency.

[0017] In some possible implementations, the hardware environment includes the storage devices involved in executing the target SQL statement, and the environmental information includes at least the resource configuration information of the storage device; based on the cause, the target strategy is determined, including: if the cause includes that the resource configuration of the storage device has reached a bottleneck, then the target strategy is determined, and the target strategy is used to instruct to upgrade the resource configuration of the storage device or enable a caching mechanism.

[0018] In some possible implementations, the hardware environment includes the network environment involved when executing the target SQL statement, and the environmental information includes at least delay information of the network environment; determining the target strategy based on the cause, including: if the cause includes the delay of the network environment reaching a delay threshold, then determining the target strategy, the target strategy is used to indicate the optimization of the network environment or the optimization of the query result set of the target SQL statement.

[0019] In this way, analyzing the hardware and network environment will help optimize SQL statement performance more accurately and effectively.

[0020] In some possible implementations, after determining the target strategy based on the reason, the method includes: if multiple target strategies are matched corresponding to multiple target SQL statements, then prioritizing the matched target strategies according to the optimization urgency of the multiple target SQL statements, wherein a higher optimization urgency is manifested as a greater execution frequency, a longer response time, or more resource consumption; if multiple target strategies are matched for a single target SQL statement, then prioritizing the target strategies of the single target SQL statement according to the degree of effectiveness, wherein a higher degree of effectiveness is manifested as a greater performance improvement, higher maintainability, less resource consumption, or greater suitability to business needs.

[0021] In this way, the multiple target strategies are prioritized according to the preset priority rules, so that personnel can more intuitively understand the priorities of the strategies and thus select the best (eg, most effective, easiest to implement, etc.) strategy for statement performance optimization.

[0022] In a second aspect, an embodiment of the present application provides a SQL statement optimization device, the device comprising:

[0023] An acquisition module is used to obtain a target SQL statement and environmental information, where the target SQL statement is an SQL statement whose execution efficiency is lower than the expected efficiency. The environmental information is used to describe the environment involved in executing the target SQL statement, which includes the software environment and / or the hardware environment. A processing module is used to determine, based on the target SQL statement and the environmental information, the reason why the execution efficiency of the target SQL statement is lower than the expected efficiency, where the reason at least includes defects in the target SQL statement and / or defects in the environment. The processing module is also used to determine a target strategy based on the reason, where the target strategy is used to indicate optimization of defects in the target SQL statement and / or defects in the environment.

[0024] In some possible implementations, the acquisition module is specifically used to: intercept SQL statements executed in the database and execution information of the SQL statements through an interceptor, where the execution information includes at least execution time and / or resource consumption; based on the execution information, determine target SQL statements whose execution efficiency is lower than the expected efficiency from the SQL statements, where the execution efficiency is lower than the expected efficiency as the execution time exceeds the preset time or the resource consumption exceeds the preset resource amount.

[0025] In some possible implementations, the defects in the target SQL statement include one or more of execution logic defects, index usage defects, query expansion, and lock contention; the processing module is specifically used to: if the cause includes the existence of an execution logic defect in the target SQL statement, determine a first target strategy, the first target strategy is used to indicate the reduction of complex nesting and inefficient operators in the target SQL statement to simplify the execution logic of the target SQL statement, and the inefficient operator refers to an SQL operator that reduces execution efficiency; if the cause includes the existence of an index usage defect in the target SQL statement, determine a second target strategy, the second target strategy is used to indicate the correction of necessary indexes in the target SQL statement and make the composite index comply with the left-most prefix principle; if the cause includes the existence of a query expansion defect in the target SQL statement, determine a third target strategy, the third target strategy is used to indicate the use of paging queries and optimization of query fields in the target SQL statement to reduce the returned data; if the cause includes the existence of a lock contention defect in the target SQL statement, determine a fourth target strategy, the fourth target strategy is used to indicate the shortening of the holding time of the lock, which is the lock triggered by the target SQL statement.

[0026] In some possible implementations, the software environment includes database tables involved in executing the target SQL statement, and the environment information includes at least statistical information of the database table, which is used to describe one or more of the data volume, number of indexes, table structure, data type, and number of add, delete, modify, and query operations of the database table.

[0027] In some possible implementations, the processing module is specifically configured to: if the cause includes an excessive amount of data in the database table, determine a fifth target strategy, which is used to instruct archiving historical data from the database table or optimizing the database table using a partitioned table, summary table, or materialized view; if the cause includes an unreasonable number of indexes in the database table, determine a sixth target strategy, which is used to instruct adding necessary indexes to the database table or deleting redundant indexes; if the cause includes structural defects in the database table, determine a seventh target strategy, which is used to instruct adjusting the table structure or data type of the database table; if the cause includes an excessive number of add, delete, modify, and query operations on the database table, determine an eighth target strategy, which is used to instruct adjusting the execution logic in the target SQL statement to merge or batch execute operations of the same type, or to optimize the indexes of the database table to improve the execution efficiency of the target SQL statement.

[0028] In some possible implementations, the software environment includes a database involved in executing a target SQL statement, and the environmental information includes at least performance information of the database; the processing module is specifically used to: if the cause includes the database being in an overloaded state, determine a target strategy, wherein the overloaded state indicates that the database processes an excessive number of concurrent requests, and the target strategy is used to indicate increasing the database connection pool size to accommodate the connections of concurrent requests, or limiting the frequency or number of concurrent requests through a current limiting strategy.

[0029] In some possible implementations, the hardware environment includes the storage devices involved when executing the target SQL statement, and the environmental information includes at least the resource configuration information of the storage device; the processing module is specifically used to: if the reason includes that the resource configuration of the storage device reaches a bottleneck, then determine the target strategy, and the target strategy is used to indicate upgrading the resource configuration of the storage device or enabling the caching mechanism.

[0030] In some possible implementations, the hardware environment includes the network environment involved when executing the target SQL statement, and the environmental information includes at least delay information of the network environment; the processing module is specifically used to: if the cause includes the delay of the network environment reaching a delay threshold, then determine the target strategy, and the target strategy is used to indicate the optimization of the network environment or the optimization of the query result set of the target SQL statement.

[0031] In some possible implementations, the processing module is further used to: if multiple target strategies are matched corresponding to multiple target SQL statements, then the matched target strategies are prioritized according to the optimization urgency of the multiple target SQL statements, wherein a higher optimization urgency is manifested as a greater execution frequency, a longer response time, or more resource consumption; if multiple target strategies are matched for a single target SQL statement, then the target strategies of the single target SQL statement are prioritized according to the degree of effectiveness, wherein a higher degree of effectiveness is manifested as a greater performance improvement, higher maintainability, less resource consumption, or more suitable for business needs.

[0032] In a third aspect, an embodiment of the present application provides an electronic device, comprising: at least one memory for storing programs; and at least one processor for executing the programs stored in the memory; wherein, when the program stored in the memory is executed, the processor is used to execute the method described in the first aspect or any possible implementation of the first aspect.

[0033] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, which stores a computer program. When the computer program runs on a processor, the processor executes the method described in the first aspect or any possible implementation of the first aspect.

[0034] In a fifth aspect, an embodiment of the present application provides a computer program product, characterized in that when the computer program product runs on a processor, the processor executes the method described in the first aspect or any possible implementation of the first aspect.

[0035] In the sixth aspect, an embodiment of the present application provides a chip, characterized in that it includes at least one processor and an interface; at least one processor obtains program instructions or data through the interface; and at least one processor is used to execute program line instructions to implement the method described in the first aspect or any possible implementation of the first aspect.

[0036] It can be understood that the beneficial effects of the second to sixth aspects mentioned above can be found in the relevant description of the first aspect mentioned above, and will not be repeated here. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] Figure 1 This is a structural diagram of a centralized SQL statement optimization device provided in an embodiment of the present application;

[0038] Figure 2 This is a structural diagram of a distributed SQL statement optimization device provided in an embodiment of the present application;

[0039] Figure 3This is a structural diagram of a SQL statement optimization device provided in an embodiment of the present application;

[0040] Figure 4 This is a flow chart of a SQL statement optimization method provided in an embodiment of the present application;

[0041] Figure 5 This is a flow chart of a SQL statement optimization method provided in an embodiment of the present application;

[0042] Figure 6 This is a schematic diagram of the SQL statement optimization strategy in the embodiment of this application.

[0043] Figure 7 This is a schematic diagram of an interface for analyzing inefficient SQL statements and their database table statistics provided by an embodiment of the present application;

[0044] Figure 8 This is a schematic diagram of an interface for analyzing inefficient SQL statements and their database table statistics provided by an embodiment of the present application;

[0045] Figure 9 This is a flow chart of a SQL statement optimization method provided in an embodiment of the present application;

[0046] Figure 10 This is a flow chart of a SQL statement optimization method provided in an embodiment of the present application;

[0047] Figure 11 This is a schematic diagram of the structure of a chip provided in an embodiment of the present application. DETAILED DESCRIPTION

[0048] The term "and / or" as used herein describes an association between related objects, indicating that three possible relationships exist. For example, "A and / or B" can represent: A exists alone, A and B exist simultaneously, or B exists alone. The symbol " / " as used herein indicates that the related objects are in an "or" relationship, for example, A / B means either A or B.

[0049] In the description of the embodiments of the present application, unless otherwise specified, "multiple" means two or more, for example, multiple processing units means two or more processing units, etc.; multiple elements means two or more elements, etc.

[0050] To reduce the difficulty of optimizing the performance of database SQL statements and improve optimization efficiency, an embodiment of the present application provides a method for optimizing SQL statements. This method primarily automatically obtains SQL statement execution logs, analyzes them from multiple perspectives, including the SQL statements themselves and the database, quickly locates the cause of slow SQL execution, and outputs corresponding optimization tips based on a preset optimization strategy. This multi-dimensional analysis assists personnel in optimizing the performance of SQL statements, improves optimization efficiency, and reduces optimization difficulty.

[0051] The following describes the architecture of an SQL statement optimization device provided in an embodiment of the present application, wherein the SQL statement optimization device can be deployed in a centralized or distributed manner.

[0052] For example, Figure 1 The diagram in FIG. 1 is a schematic diagram of the architecture of a centralized SQL statement optimization device provided by an embodiment of the present application. Figure 1 As shown, the SQL statement optimization apparatus 100 can be deployed on a computing device 10 , which can be a physical server or a virtualized device such as a virtual machine or a container.

[0053] In this example, the computing device 10 may include a processor 110 and a memory 120 and a network interface 130 that are communicatively connected to the processor 110, wherein the processor 110 is the control center of the computing device 10. The processor 110 may be a central processing unit (CPU), a graphics processing unit (GPU), a data processing unit (DPU), a neural processing unit (NPU), etc., but is not limited thereto. The memory 120 may be a memory, such as a random access memory (RAM), for storing instructions or data that the processor 110 may access multiple times. The memory 120 may also be a persistent storage disk, such as a magnetic disk, a hard disk, an optical disk, a register, a read-only memory (ROM), a programmable ROM (PROM), an erasable PROM (EPROM), an electrically erasable PROM (EEPROM), a non-volatile RAM (NVRAM), etc. The memory 120 may also be used to store programs related to this embodiment. The network interface 130 may optionally include a standard wired interface, a wireless interface (including but not limited to WI-FI, a mobile communication interface, etc.), etc., for receiving and transmitting data under the control of the processor 110.

[0054] Figure 1 The device structure shown in the figure does not constitute a limitation on the computing device 10, and may include more or fewer components than shown in the figure, or combine certain components, or arrange the components differently.

[0055] In this embodiment, reference Figure 1 The user terminal (specifically, an application deployed on the terminal) 20 can interact with a database management system (DBMS) 30. In step 1, SQL statements are sent to DBMS 30 to request operations such as create, delete, modify, and query (CRUD) on database 300. CRUD operations refer to the four data operations: Create, Read, Update, and Delete. Create adds a new record to database 300, Read queries data from database 300, Update updates an existing record, and Delete removes a record from database 300.

[0056] Next, continue to refer to Figure 1 , DBMS30 can execute step 2, that is, perform add, delete, modify and query operations on the data in the database 300 according to the SQL statement, and generate a corresponding execution log. The information recorded in the execution log includes but is not limited to: the SQL statement itself, the execution time of the SQL statement (i.e., timestamp, including start time and end time), the execution time of the SQL statement, the execution result, the execution status (such as success, failure or error), user information (including user name, IP address, etc.), database and table information (such as name, etc.), resource consumption information (such as CPU time, memory usage, disk I / O, etc.), and execution plan (such as index usage, scanning method, etc.). Then, DBMS30 can execute step 3 and return the execution results of these SQL statements to the user terminal 20, for example, for query statements, the number of rows queried is returned, for update / delete statements, the number of affected rows is returned, and so on. As an example and not limitation, the database 300 can be a relational database or a non-relational database, etc., and the database 300 can be a cloud database or a local database.

[0057] In this embodiment, the SQL statement optimization device 100 deployed on the computing device 10 can be linked to the DBMS 30, so that in the process of the above-mentioned DBMS 30 receiving SQL statements and executing SQL statements to the database 300, the performance of these SQL statements can be monitored and analyzed to provide optimization suggestions for inefficient SQL statements (i.e., target SQL statements).

[0058] Exemplarily, the SQL statement optimization device 100 includes:

[0059] The acquisition module 101 can be used to obtain the target SQL statements executed on the database 300 and the environment information involved in these SQL statements (such as Figure 1 Step 4 shown). Wherein, the target SQL statement is an SQL statement whose execution efficiency is lower than the expected efficiency. As an example, the target SQL statement can be determined based on the execution log (for example, the execution time exceeds the preset time). Exemplarily, the execution information of the SQL statement may include execution time, execution time, execution result, and execution status, but is not limited thereto. The environmental information is used to describe the environment involved in executing the target SQL statement, and the environment includes a software environment (for example, a database, a table) and / or a hardware environment (for example, a storage device or a network environment). Exemplarily, the environmental information may include, but is not limited to, one or more of the load information of the database, the statistical information of the database table, the resource configuration information of the storage device, and the service quality information of the network environment. In this way, the acquisition module 101 obtains more comprehensive information for subsequent SQL statement performance analysis.

[0060] The analysis module 102 can be used to analyze the target SQL statement and environment information obtained above, so as to determine the reason why the execution efficiency of the target SQL statement is lower than the expected efficiency (that is, inefficiency). Among them, the target SQL statement includes a statement that executes slowly, consumes a large amount of system resources, or causes database performance to degrade. Exemplarily, the reasons for the inefficiency of the target SQL statement may include defects in the target SQL statement itself and / or defects in the environment involved. For example, if the subquery logic of the target SQL statement is unreasonable and causes inefficiency, it is a defect in the target SQL statement itself. If the amount of database table data involved in the SQL statement is large, resulting in a long scanning process, it is a defect in the database table (software environment) involved. Similarly, if the number of indexes of the database table involved is too large, the execution efficiency of the target SQL statement will be reduced due to the excessive burden on the database 300 or its disk and memory when adding / deleting / modifying data; or if there are too few indexes, the SQL query performance will be poor, resulting in increased execution time; or if the target SQL statement has more CRUD times (for example, more analysis queries or more additions, deletions, and modifications), the resource consumption, index maintenance, and other overheads will also increase, thereby reducing the execution efficiency of the target SQL statement. These are all inefficiencies caused by defects in the database tables involved in the SQL statement.

[0061] Therefore, illustratively, the analysis module 102 can analyze the cause of the target SQL statement based on the preset analysis rules and the acquired target SQL statement and environmental information, that is, analyze whether it is caused by its own logical problems or performance or load problems of the environment involved, so as to achieve the purpose of accurately locating the cause of the inefficiency of the target SQL statement.

[0062] The strategy matching module 103 can be used to match the corresponding target strategy for the reason from the pre-configured optimization strategies 1, 2, ... and output (such as Figure 1 5), thereby guiding the development / operation and maintenance personnel to optimize the performance of the target SQL statement according to the target strategy to improve the execution speed of the target SQL statement. Exemplarily, optimization strategies 1, 2, ... respectively configure corresponding optimization suggestions for different reasons for the inefficiency of SQL statements. By way of example and not limitation, optimization strategy 1 describes that if the current cause of inefficiency is that the target SQL statement involves too many indexes of the database table (for example, more than 10), then the configured optimization suggestion may be "delete infrequently used indexes or merge redundant indexes", and optimization strategy 2 describes that if the current cause of inefficiency is that there are nested subquery performance issues in the target SQL statement, then the configured optimization suggestion may be "optimize subqueries", and so on, which are not enumerated one by one.

[0063] In this way, this embodiment analyzes multiple dimensions from the SQL statement itself and the software environment and hardware environment related to running the SQL statement, which is conducive to more comprehensive and accurate positioning of the cause of the inefficiency of the target SQL statement and providing matching suggestions, thereby helping to improve the intelligence of SQL performance optimization operations, reduce the degree of human participation, and improve optimization efficiency.

[0064] Illustratively, the computing device 10, the user terminal 20 and the DBMS 30 may be implemented on the same physical server or on multiple physical servers, which is not limited herein.

[0065] It should be noted that in actual application, the above Figure 1 The SQL statement optimization device 100 shown can be implemented by software, or can also be implemented by hardware or a combination of software and hardware.

[0066] As an example of a software functional unit, the SQL statement optimization device 100 may include code running on a computing instance. The computing instance may include at least one of a host, a virtual machine, and a container. Furthermore, the computing instance may be one or more. For example, the data processing system 100 may include code running on multiple hosts / virtual machines / containers. It should be noted that the multiple hosts / virtual machines / containers used to run the code may be distributed in the same region or in different regions. Furthermore, the multiple hosts / virtual machines / containers used to run the code may be distributed in the same availability zone (AZ) or in different AZs, each AZ including one data center or multiple geographically close data centers. Typically, a region may include multiple AZs.

[0067] Similarly, multiple hosts / virtual machines / containers running the code can be distributed within the same virtual private cloud (VPC) or across multiple VPCs. Typically, a VPC is set up within a region. Cross-region communication between two VPCs within the same region, or between VPCs in different regions, requires a communication gateway within each VPC to interconnect the VPCs.

[0068] In some possible implementations, the SQL statement optimization device 100 may also be as follows: Figure 2 As shown, it is distributedly deployed on multiple computing devices 10 in the device cluster 00, so as to achieve the positioning of the target SQL statement on the database 300 and the corresponding target policy output as mentioned above, which will not be repeated.

[0069] Next, the working principle of the SQL statement optimization device 100 provided in the embodiment of the present application is introduced with reference to the accompanying drawings.

[0070] In some possible implementations, the acquisition module 101 of the SQL statement optimization device 100 may directly request the execution log of the SQL statement from the DBMS 30 to obtain the executed SQL statements and their execution information from the execution log.

[0071] In some other possible implementations, in order to avoid the query operation requesting to obtain the execution log increasing the load of the database 300 and to improve the real-time performance of the device 100, such as Figure 3 As shown, the acquisition module 101 of the SQL statement optimization device 100 may include an interface 1011 and an interceptor 1012, which intercepts SQL statements executed on the database 300 and their execution information. The interface 1011 is used to define the basic behavior of the interceptor 1012, and the interceptor 1012 is used to implement these basic behaviors to intercept the required information, such as the aforementioned SQL statements and their execution logs.

[0072] Exemplarily, in the implementation, a MyBatis interceptor 1012 can be created by implementing the Interceptor interface 1011. Specifically, the Interceptor interface 1011 is used to define a set of method (function) signatures (method name, parameters, return type), such as defining the capture and processing of specified information in the intercept method. This information may include but is not limited to SQL statements of one or more operation types, SQL statement execution time (or execution log) and other execution information, as well as database table statistics such as the table content, data volume, number of indexes, and CRUD operation records involved. Moreover, in this example, the corresponding interception logic is defined in the MyBatis interceptor 1012 to execute Figure 3 To capture these execution information and database table statistics, follow the instructions in step 11.

[0073] The following Interceptor interface code snippet is used as an example to illustrate the implementation logic of Interceptor interface 1011.

[0074]

[0075] This Interceptor interface code snippet is a partial definition of a MyBatis interceptor. The @Intercepts annotation is used to declare which methods this MyBatis interceptor will intercept. The @Intercepts annotation includes two @Signature annotations, each of which specifies a method to be intercepted (interception point). The first @Signature annotation defines the interception point as follows: intercepting the query method (i.e., the method name to be intercepted defined by the method attribute) of Executor (the interface / class to be intercepted defined by the type attribute). The method parameters (defined by the args attribute) are (MappedStatement, Object, RowBounds, ResultHandler), which are used to obtain the execution log and query result set of the query SQL. The second @Signature annotation defines the interception point as follows: intercepting the update method (i.e., the method name to be intercepted defined by the method attribute) of Executor (the interface / class to be intercepted defined by the type attribute). The method parameters (defined by the args attribute) are (MappedStatement, Object), which are used to obtain the execution log and modification parameters of the modified SQL. In addition, the MyBatisSQLExecutionInterceptor class is used to implement the Interceptor interface of MyBatis, so that the above-mentioned interception logic of the MyBatis interceptor is executed when the above-mentioned method is called. In other words, when executing SQL queries (queries) or updates (updates) on the database 300, the corresponding boundSQL (an intermediate state during the execution of SQL statements) will be intercepted by this MyBatis interceptor, thereby adding custom logic before and after execution, such as recording SQL execution logs, modifying SQL parameters, measuring SQL execution time, etc.

[0076] Similarly, MyBatis interceptors can be used to parse SQL statements to obtain relevant database table statistics. For example, database table statistics may include but are not limited to the table data volume, the number of indexes, the table structure and data type, and the number of CRUD operations involved in the SQL statement. For example, to obtain the number of indexes in a database table, refer to the following code snippet:

[0077] / / 3. Traverse the result set to count the number of indexes

[0078] }

[0079] In this way, the number of indexes of the database table involved in the SQL statement is extracted. Similarly, the number of CRUD operations involved in the SQL statement can also be extracted and recorded, but the present invention is not limited thereto.

[0080] In addition, the acquisition module 101 can also obtain database logs (including database load information), service quality information of the network environment (including network delay time, which can be obtained from the gateway of DBMS30), and resource configuration information of the underlying storage device of DBMS30 (that is, the physical entity that actually stores the database 300 data, such as a hard disk) from the DBMS30 side.

[0081] Then, refer to Figure 3 , the acquisition module 101 passes the acquired information to the analysis module 102 for analysis, and the analysis module 102 can determine the target SQL statement whose execution efficiency is lower than the expected efficiency from these SQL statements by executing step 12. Exemplarily, the analysis module 102 can compare the execution time of each SQL statement with the preset duration, and determine the target SQL statement that reaches the preset duration (for example, 1 second), that is, the inefficient SQL statement. In some possible examples, the target SQL statement that executes slowly can also be determined through some log functions of DBMS30 (such as the slow query log function), and then the determined result is passed to the analysis module 102 via the acquisition module 101.

[0082] In this embodiment, after the analysis module 102 determines the target SQL statement, it can further analyze the target SQL statement and environmental information using preset analysis rules to determine whether the target SQL statement is caused by its own reasons or the reasons of the environment involved.

[0083] For example, there are various inherent defects that lead to the inefficiency of the target SQL statement. For example, the query conditions of the target SQL statement lack indexes or the indexes are not used correctly, the amount of data returned by the query is too large (such as using SELECT *), complex JOIN operations such as multi-table connections (especially connections between large tables), and nested subquery performance issues, too many to list one by one. There are also various environmental defects that lead to the inefficiency of the target SQL statement. For example, there are too many or too few indexes in the database tables involved, the amount of data in the tables is large, the table structure design or data type selection is improper, and there are too many CRUD times, the database 300 is overloaded, the storage device resources are improperly configured, and there are network delays.

[0084] For example, the analysis module 120 may be pre-set with several statement analysis rules, which are used to determine whether the target SQL statement complies with general SQL rules. Specifically, the target SQL statement is matched against each of these statement analysis rules. If the target SQL statement matches one or more of these rules, the target SQL statement itself is considered irrational. Conversely, if the target SQL statement does not match any of these rules, the target SQL statement is considered irrational.

[0085] For example, the several statement analysis rules preset in the analysis module 120 may include, but are not limited to, one or more of the following rules:

[0086] Whether the query conditions of the SQL statement do not correctly use indexes, and / or whether the failure to use indexes causes a full table scan. If so, it is unreasonable; otherwise, it is reasonable;

[0087] Whether the consumption of CPU, memory, disk I / O and other resources during the execution of SQL statements reaches the resource threshold. If so, it is unreasonable; otherwise, it is reasonable.

[0088] Whether the SQL statement uses the "where" condition and performs mathematical operations on the "where" condition. If yes, it is unreasonable; otherwise, it is reasonable.

[0089] The SQL statement uses an "in" or "not in" clause, and whether it uses complex expressions or functions or returns a result set that exceeds the preset value. If so, it is unreasonable; otherwise, it is reasonable;

[0090] Whether the SQL statement uses "select *"; if yes, it is unreasonable; otherwise, it is reasonable;

[0091] When an SQL statement uses multiple tables, check whether the join order is such that the larger table drives the smaller table. If so, it is not reasonable; otherwise, it is reasonable.

[0092] In this example, the statement analysis rules are not enumerated one by one. It should be understood that in addition to the above statement analysis rules, other rules may also be included to determine whether an SQL statement complies with SQL general rules.

[0093] Thus, the statement analysis rule (which may be one or more) hit by the target SQL statement is the reason why the target SQL statement is unreasonable, that is, the reason why the execution efficiency of the target SQL statement is lower than the expected efficiency.

[0094] Furthermore, illustratively, the analysis module 120 may preset several database table analysis rules, which are used to determine whether the database statistical information involved in the target SQL statement affects the execution efficiency of the target SQL statement. Thus, the database table statistical information is matched against these database table analysis rules one by one. The database table analysis rules (which may be one or more) that the database table statistical information matches are the reasons why the execution efficiency of the target SQL statement is lower than the expected efficiency. Conversely, if no rule is matched, it is determined that the database table does not cause the inefficiency of the target SQL statement.

[0095] Exemplarily, database table analysis rules may include but are not limited to:

[0096] The amount of database table data involved in the SQL statement exceeds a threshold (also referred to herein as a first threshold), for example, the threshold is 1 million;

[0097] The number of indexes of the table involved in the SQL statement exceeds a threshold (also referred to herein as the second threshold) or falls below a threshold (also referred to herein as the third threshold). For example, the second threshold may be, but is not limited to, 6, and the third threshold may be, but is not limited to, 0.

[0098] The number of CRUD operations of SQL statements exceeds a threshold (also referred to as the fourth threshold in this article), for example, the number of insert operations (additions) exceeds 8,000, or the number of modifications exceeds 8,000, or the number of deletions exceeds 4,000.

[0099] In this example, the database table analysis rules are not enumerated one by one. It should be understood that in addition to the above database table analysis rules, other rules may also be included to determine whether the database table affects the efficiency of the SQL statement.

[0100] In this way, this embodiment accurately monitors the amount of database table data, the number of indexes, and the number of CRUD operations involved in SQL statements in all aspects, gathers more detailed records, and provides data support for SQL tuning. It then provides multi-dimensional analysis from the SQL statements themselves and the database, and gives more accurate and comprehensive optimization suggestions, assisting personnel in more effectively optimizing SQL performance.

[0101] In addition, illustratively, database analysis rules can be preset in the analysis module 120 to determine whether the database load status involved in the target SQL statement affects the execution efficiency of the target SQL statement. Database analysis rules may include but are not limited to analyzing whether the concurrent requests of the database 300 have reached a threshold, whether the database configuration is reasonable, etc. Similarly, storage device analysis rules can be preset in the analysis module 120 to determine whether the resource configuration of the storage device involved in the target SQL statement is reasonable, such as analyzing whether the resources allocated by the storage device to the user terminal 20 (including but not limited to storage space, bandwidth, I / O operation capabilities) have reached a bottleneck. Similarly, network environment analysis rules can be preset in the analysis module 120 to determine whether the network environment involved in the target SQL statement is stable, such as analyzing whether the quality of service (QoS) of the network environment is lower than a threshold.

[0102] In this embodiment, continue to refer to Figure 3 After the analysis module 102 determines the reason for the inefficiency of the target SQL statement, the strategy matching module 103 can be triggered to execute step 13, and the corresponding target strategy can be automatically matched from the optimization strategies 1, 2, ... according to the determined reason and output.

[0103] Exemplarily, there may be one or more reasons for the inefficiency of a target SQL statement. Then, the strategy matching module 103 may match one or more target optimization strategies for one reason of a target SQL statement. That is, for one cause of the inefficiency of a target SQL statement, the strategy matching module 103 may provide one or more optimization suggestions for personnel to choose from. For example, if the reason for the inefficiency of a target SQL statement is that there are too many indexes in the database table involved, the strategy matching module 103 may match target strategies 1, 3, and 15 from all optimization strategies 1, 2, ..., and target strategies 1, 3, and 15 are respectively "merge redundant indexes or delete unnecessary indexes", "modify the query conditions to avoid using functions or expressions on index columns to ensure that the index can be hit", and "if the table data volume is very large, consider using a partitioned table to reduce the size of the index". Alternatively, for multiple causes of the inefficiency of a target SQL statement, the strategy matching module 103 may provide an optimization suggestion to solve all problems caused by these causes, or may provide multiple optimization suggestions to solve the problems caused by each cause separately.

[0104] Exemplarily, the optimization strategy matching is implemented by at least one of the following methods: a lookup table based on predefined rules, a decision tree based on weights, a neural network model, a fuzzy logic system, or a dynamically generated strategy mapping table. The strategy matching module 103 can also be configured to support other unlisted strategy matching methods to adapt to different application scenarios and technological evolution.

[0105] For example, when the policy matching module 103 outputs multiple target policies for target SQL data, these target policies may be prioritized. Specifically, when multiple target policies are output for multiple target SQL statements, the corresponding target policies may be prioritized based on the importance of the target SQL statements. For example, the target policy for data manipulation language SQL (i.e., SQL for adding, deleting, modifying, and querying) has the highest priority, followed by the target policy for SQL used to define and modify database structures, which has a slightly lower priority, and then the target policy for SQL used to ensure system security and consistency, which has an even lower priority.

[0106] Alternatively, when multiple target strategies are output for multiple target SQL statements, the target strategies can be ranked based on their optimization urgency. Optimization urgency can be expressed as: SQL statements with higher execution frequency, longer response times, or greater resource consumption have higher optimization urgency, and their corresponding target strategies have higher priority.

[0107] Of course, the priority sorting basis of the target policy can also be applied to application scenarios or customized requirements, and this example does not make specific limitations.

[0108] In this way, this embodiment can analyze the causes of inefficiency based on the SQL statement itself and the associated software / hardware environment and provide corresponding optimization tips, helping developers / operators optimize SQL performance more targetedly at a higher level. Furthermore, developers / operators can intuitively understand the SQL statement issues that need urgent rectification and optimization solutions based on the priority list, thereby improving the speed and effectiveness of SQL optimization.

[0109] For example, in this embodiment, intercepted SQL statements, execution information, database table statistics, analyzed causes of inefficient SQL statements, and matching target strategies can be associated and stored in a remote dictionary server (REDIS) for future use. For example, this data can be used as optimization history data for human query, or when the same SQL statements and database table statistics are subsequently intercepted, the same optimization strategy can be directly used, reducing the computational complexity of the optimization operation.

[0110] Next, based on the above description, a SQL statement optimization method provided by an embodiment of the present application is introduced. It is understandable that this method is proposed based on the above description, and part or all of the content of this method can be referred to the above description.

[0111] See also Figure 4 , Figure 4 This is a flow chart of a SQL statement optimization method provided by an embodiment of the present application. It is understood that the method can be executed by any device, equipment, platform, or equipment cluster with computing and processing capabilities, such as Figure 1 The example of executing on the computing device 10 shown is used for illustration, but is not limited thereto. Figure 4 As shown, the method may include:

[0112] In S401, the target SQL statement and environment information are obtained.

[0113] In this embodiment, when the user terminal interacts with the database management system DBMS to execute the corresponding SQL statement on the database, the computing device can communicate with the database management system DBMS to obtain the target SQL statement executed on the database, that is, the SQL statement whose execution efficiency is lower than the expected efficiency. Among them, the execution efficiency of the SQL statement is lower than the expected efficiency, which can be manifested as slow execution speed, consumption of a large amount of system resources (such as CPU time, memory usage, disk I / O, etc.) or causing database performance to degrade. In addition, the computing device also obtains environmental information, which is used to describe the environment involved in the target SQL statement, and the environment includes the software environment and the hardware environment. For example, the software environment can include a database and a database table, and the hardware environment can include a storage device and a network environment.

[0114] For example, the environmental information may include database load information and database table statistics related to the target SQL statement. This database table statistics may include, but is not limited to, the amount of data in the database table, the number of indexes, the table structure and data type, and the number of CRUD operations involved in the SQL statement. Environmental information may also include storage device resource configuration information and network environment quality of service (QoS) information. Resource configuration information may describe the resources allocated to the user terminal (including, but not limited to, storage space, bandwidth, and I / O operation capabilities).

[0115] For example, the computing device may communicate with the database management system (DBMS) and request the DBMS to obtain the target SQL statement and environment information. Alternatively, the computing device may deploy an interceptor or other means to intercept the target SQL statement during the interaction between the user terminal and the DBMS, and then extract the corresponding environment information from the DBMS based on the target SQL statement.

[0116] Illustratively, the number of target SQL statements may be one or more.

[0117] S402: Determine, based on the target SQL statement and the environment information, why the execution efficiency of the target SQL statement is lower than the expected efficiency.

[0118] In this embodiment, the lower-than-expected execution efficiency of the target SQL statement may be due to defects in the SQL statement and / or defects in the environment. In this embodiment, preset analysis rules can be used to analyze the target SQL statement's language logic, as well as the performance and load of the environment involved, to determine the cause of the target SQL statement's inefficiency. This allows for a more comprehensive and accurate identification of the cause, facilitating subsequent targeted statement performance optimization.

[0119] Illustratively, the defects in the target SQL statement itself may be various, including but not limited to one or more of execution logic defects, index usage defects, query expansion, and lock contention, but not limited thereto.

[0120] For example, the target SQL statement may involve a variety of environmental defects, including but not limited to database table defects, database defects, storage device defects, and network environment defects. Database defects include but are not limited to database overload, database table defects include but are not limited to excessive data volume, an unreasonable number of indexes, structural defects in database tables, and an excessive number of add, delete, modify, and query operations on database tables. Storage device defects include but are not limited to resource allocation bottlenecks for user terminals, and network environment defects include but are not limited to service quality falling below a quality threshold.

[0121] For example, for each target SQL statement, there may be multiple reasons causing its inefficiency.

[0122] S403: Determine a target strategy based on the cause, where the target strategy is used to instruct optimization of defects in the target SQL statement and / or defects in the environment.

[0123] In this embodiment, the computing device can pre-configure several optimization strategies, each of which describes the corresponding optimization solutions (or optimization suggestions or prompts) required for different reasons. In this way, after determining the various causes of the target SQL statement, for each cause of the target SQL statement, at least one target strategy can be matched from these optimization strategies to indicate optimization of defects in the target SQL statement and / or defects in the environment.

[0124] In this embodiment, the computing device can provide a visual interface to output the target strategy, allowing personnel to optimize the target SQL statement according to the target strategy. Based on the SQL statement itself and the amount of data in the database tables involved, the number of indexes, and the number of CRUD operations, corresponding optimization tips are provided. This helps development and maintenance personnel optimize SQL performance more targeted and accurately at a higher level, reducing the input of personnel during the optimization and analysis process, thereby reducing the requirements on personnel, improving the operability of optimization, and facilitating its implementation.

[0125] The following is combined with Figure 5 , the execution process of the SQL statement optimization method provided in the embodiment of the present application is elaborated in detail.

[0126] For example, Figure 5 The flow chart of the SQL statement optimization method provided by the embodiment of the present application is shown. Figure 4 , Figure 5 The processes shown are for Figure 5 A further refinement of the process shown. In this example, Figure 5 As shown, when using this method to monitor and analyze the performance of SQL statements executed on the database, the acquisition of target SQL statements and database table statistics can be achieved through an interceptor or by directly requesting the database management system DBMS. Figure 5 In some possible implementations, the method may include:

[0127] S501A, obtaining the execution log of the SQL statement executed in the database, as well as the database log, storage device log and gateway log to obtain the target SQL statement and environment information.

[0128] In this example, when the user terminal requests the database management system DBMS to execute an SQL statement on the database, the database management system DBMS will generate a corresponding execution log. The execution log may include the SQL statement itself, the execution time of the SQL statement, the execution time, the execution result, the execution status, the database and table information, the resource consumption information, and the slow query log, etc. Among them, the slow query log describes the target SQL statement whose execution time exceeds the preset time.

[0129] In addition, the database management system DBMS will also record database logs, storage device logs, gateway (such as API gateway, load balancer or proxy) logs, etc. For example, the database log can record the database load information and database table statistics. The database load information may include the number of concurrent requests currently received by the database and the size of the database connection pool. The database table statistics may include the table's data volume, number of indexes, table structure and data type, as well as the number of add, delete, modify and query operations. The storage device log may include resource (storage space, bandwidth and I / O operation capability) configuration information. The gateway log may include the network environment's quality of service information QoS.

[0130] In this way, the computing device can request the execution log of SQL statements, as well as database logs, storage device logs, and gateway logs from the DBMS to obtain the required environmental information.

[0131] For example, when extracting database table statistics, the computing device may parse the target SQL statement to identify the database table identifier (e.g., table ID, table name, etc.) contained in the statement. Based on the parsed database table identifier, the computing device may request the database management system (DBMS) to obtain database table statistics such as the amount of data, number of indexes, table structure, and data type of the table to which the identifier belongs, as well as the number of additions, deletions, modifications, and queries performed by the SQL statement (including the target SQL statement).

[0132] In this way, the computing device can subsequently perform multi-dimensional performance analysis on the statement logic and environment based on the determined target SQL statement and environmental information to accurately locate the cause of the statement inefficiency.

[0133] In some other possible implementations, such as Figure 5 As shown in the figure, obtaining target SQL statements and database table statistics can be achieved based on interceptors. Compared with obtaining information from database execution logs, there is no need to change the original logic, and it is more real-time, highly operational, simple and easy to implement. It also avoids the burden of querying execution logs that increases the database. Figure 5 As shown, the method may include:

[0134] S501B, intercepting the SQL statements executed in the database and the execution information of the SQL statements through the interceptor.

[0135] In this example, the computing device can deploy an interceptor (such as a MyBatis interceptor). During the interaction between the user terminal and the database management system DBMS, the triggered MyBatis interceptor can intercept the SQL statements sent by the user to the DBMS according to its own interception logic, as well as the execution information related to the SQL statements on the DBMS. As an example, the interceptor can be the MyBatis interceptor created by implementing the Interceptor interface above to capture the required execution information. The execution information may include the execution time, execution duration (execution time) and / or resource consumption information of the SQL statement, but is not limited to this.

[0136] S502B: Determine a target SQL statement from the SQL statements based on the execution information.

[0137] In this example, based on the execution information, the computing device can identify, from among the intercepted SQL statements, statements whose execution time exceeds a preset duration or whose resource consumption exceeds a preset amount of resources as target SQL statements. In this way, the computing device can parse the target SQL statements to obtain the database table identifiers included therein. In some examples, the target SQL statements may also include database identifiers (such as database names or IDs) and other information related to the database tables.

[0138] S503B, obtain the database log, storage device log and gateway log involved in the target SQL statement to obtain environmental information.

[0139] In this example, the computing device can extract these database logs, as well as the logs of the database's underlying storage device and the database's gateway log from the DBMS based on the database table identifier and database identifier (from the target SQL statement or the connection information between the computing device and the database).

[0140] In this embodiment, after obtaining the target SQL statement and environment information through the above-mentioned S501A, or S501B to S503B, the method may further include:

[0141] S504: Determine the reason why the execution efficiency of the target SQL statement is lower than the expected efficiency based on the target SQL statement and the environment information.

[0142] In this example, the computing device may preset several statement analysis rules and several database table analysis rules, and use these rules to analyze the cause of the target SQL statement from the perspectives of language logic and software and hardware environment. Specifically, S504 may include:

[0143] S5041, traverse a plurality of preset analysis rules, and match the target SQL statement and the environment information with the plurality of analysis rules.

[0144] For example, several statement analysis rules, database analysis rules, database table analysis rules, storage device analysis rules, and network environment analysis rules in the computing device are used to determine whether an SQL statement complies with general SQL rules, whether there are issues with the database load, whether there are performance or load defects in the database table, whether the resource configuration of the storage device has reached a bottleneck, whether the network environment is stable, and so on. In this example, these analysis rules can be the statement analysis rules, database analysis rules, database table analysis rules, storage device analysis rules, and network environment analysis rules described above in the operating principle section of the SQL statement optimization device 100, but are not limited thereto and will not be further described here.

[0145] The computing device traverses the statement analysis rules, matching the target SQL statement against each one, and determining whether the target SQL statement matches these rules. For any target SQL statement, hitting any statement analysis rule can indicate that the target SQL statement itself has defects and needs to be optimized. In other words, the rules (which can be one or more) that the target SQL statement hits are the cause of its inefficiency. Conversely, if the target SQL statement does not hit any of these rules, it is determined that the target SQL statement's logic has no detected defects.

[0146] Similarly, the database table analysis rules are traversed and the database table statistics are matched against these database table analysis rules one by one. If the database table statistics match one or more database table analysis rules, it means that the database table has defects in characteristics (state) or load (or operation) that need to be optimized, which is the cause of the target SQL statement inefficiency. Conversely, if no rule is matched, it is determined that the database table does not cause the target SQL statement inefficiency.

[0147] Similarly, the database load information is matched against these database analysis rules, the storage device resource configuration information is matched against these storage device analysis rules, and the network environment's Quality of Service (QoS) information is matched against these network environment analysis rules. This allows us to determine if there are flaws in the database load, storage device resource configuration, and network environment that could be contributing to the inefficiency of the target SQL statement.

[0148] S5042: According to the analysis rules of the target SQL statement and its environment information, the reason why the execution efficiency of the target SQL statement is lower than the expected efficiency is obtained.

[0149] In this example, the analysis rules matching the target SQL statement and its environment information are summarized to identify all causes of inefficiency. This allows us to analyze the causes of SQL statements that are slow or consume excessive resources despite being confirmed flawless through language logic analysis. This eliminates bottlenecks in SQL statement performance analysis and allows us to quickly pinpoint the causes of inefficiency. Furthermore, the analysis process requires no human intervention, reducing staffing requirements.

[0150] S505: Match a target strategy from a plurality of pre-configured optimization strategies according to the cause.

[0151] In this example, several optimization strategies can be configured in the computing device, and each optimization strategy defines the optimization solutions (suggestions or prompts) required for different reasons. In this way, through matching methods such as lookup tables based on predefined rules, decision trees based on weights, neural network models, fuzzy logic systems, or dynamically generated strategy mappings, the required target strategy can be matched according to the cause of the target SQL statement, that is, the optimization solution in the strategy can be obtained.

[0152] Exemplary, reference Figure 6 The defects in the target SQL statement may include one or more of execution logic defects, index usage defects, query expansion, and lock contention. S505 may include S5051 to S5054:

[0153] S5051: If the reason includes that the target SQL statement has an execution logic defect, a first target strategy is determined, where the first target strategy is used to instruct to reduce complex nesting and inefficient operators in the target SQL statement to simplify the execution logic of the target SQL statement.

[0154] In this example, if the target SQL statement has execution logic flaws—that is, improperly written statements—these flaws can include complex nesting and the use of inefficient operators. Complex nesting can include high query overhead due to multiple layers of nested subqueries, high query overhead due to recalculation of subqueries for each row in the main query, or the use of complex JOIN operations. These examples are not exhaustive. Inefficient operators are SQL operators that reduce execution efficiency. Examples include operators like "NOT IN" that require a full table scan, the use of the "OR" operator in a WHERE condition that prevents index usage, and the use of functions on indexed columns that render the index invalid.

[0155] To address these execution logic flaws, a target strategy (the first target strategy) can be used to reduce or avoid complex nested and inefficient operators. For example, the optimization strategy might include "changing the inefficient "NOT IN" operator to the efficient "NOT EXISTS" operator to avoid full table scans" or "avoiding nested multi-layer subqueries and recommending query flattening and reconstruction." This simplifies the execution logic of the target SQL statement and improves its execution efficiency.

[0156] S5052: If the reason includes that the target SQL statement has an index usage defect, a second target strategy is determined, where the second target strategy is used to instruct to correct necessary indexes in the target SQL statement and make the composite index comply with the leftmost prefix principle.

[0157] In this example, index usage defects include lack of indexes or invalid indexes. Therefore, the matching target strategy (second target strategy) can be: add necessary indexes to the target SQL statement and ensure that the composite index complies with the leftmost prefix principle. That is, the query of the composite index (multi-column index) must start from the leftmost column of the index and cannot skip the middle columns. Only in this way can the index be fully utilized and the execution efficiency of the SQL statement be improved.

[0158] S5053: If the reason includes the defect of query expansion in the target SQL statement, a third target strategy is determined, where the third target strategy is used to instruct to use paging query and optimize query fields in the target SQL statement to reduce the returned data.

[0159] In this example, query expansion means that the query result set of the target SQL statement is too large. Therefore, the matching target strategy (third target strategy) can be: use paging queries in the target SQL statement (that is, only obtain a subset of the data set (data on a specific page) in the SQL query, rather than all data) and clear fields to reduce the return of redundant data. For example, if "SELECT *" is used in the target SQL statement, resulting in a query result set that is too large, the optimization suggestion given in the target strategy can be "Change "SELECT *" to use the clear fields "SELECT user_id, username, email" to obtain the corresponding query results." This reduces the returned data and improves the execution efficiency of the SQL statement.

[0160] S5054: If the reason includes a lock contention defect in the target SQL statement, a fourth target strategy is determined. The fourth target strategy is used to instruct to shorten the holding time of the lock, which is triggered by the target SQL statement.

[0161] In this example, lock contention causes the execution of the target SQL statement to take too long, so the target strategy (fourth target strategy) can indicate to shorten the lock holding time of the lock triggered by the target SQL statement, reduce the severity of lock contention, and thus improve the efficiency of SQL statement execution.

[0162] In this example, the defect in the target SQL statement may be a defect in the database table, and S505 may include S5055 to S5058:

[0163] S5055: If the reason includes excessive data volume in the database table, a fifth target strategy is determined. The fifth target strategy is used to instruct archiving historical data of the database table, or optimizing the database table using a partition table, a summary table, or a materialized view.

[0164] In this example, if the amount of data in the database table involved in the target SQL statement is too large, the fifth target strategy is matched to indicate archiving historical data for the database table, or using partitioned tables, summary tables, or materialized views to optimize the database table. In this way, archiving historical data can reduce the amount of data in the table, and using partitioned tables to optimize the database table can divide the data into multiple independent parts according to specific rules (such as time, geographic location, etc.). During the query, only the partitions related to the conditions can be scanned to avoid a full table scan. The summary table can pre-calculate commonly used statistical values (such as counts, sums, averages, etc.), so that complex aggregate queries can obtain results directly from the summary table instead of real-time calculations, thereby improving the execution efficiency of the target SQL statement.

[0165] S5056: If the reason includes that the number of indexes in the database table is unreasonable, a sixth target strategy is determined, where the sixth target strategy is used to instruct to add necessary indexes to the database table or to delete redundant indexes.

[0166] In this example, the number of indexes in the database table is unreasonable, that is, the indexes are insufficient or redundant. Therefore, the sixth target strategy matched thereto is used to instruct to add necessary indexes to the database table or delete redundant indexes to improve the execution efficiency of the target SQL statement.

[0167] S5057: If the reason includes that there are structural defects in the database table, determine a seventh target strategy, which is used to instruct to adjust the table structure or data type of the database table.

[0168] In this example, the database table has structural flaws, namely, inappropriate table structures or data types. For example, the table contains excessive duplicate data, or the data type is too large to match the SQL query conditions. Therefore, the seventh target strategy identified can instruct adjustments to the database table structure or data type to improve the execution efficiency of the target SQL statement. For example, the optimization suggestions provided by the seventh target strategy might include "split duplicate data into separate tables" or "change the integer type SMALLINT to the integer type TINYINT."

[0169] S5058. If the reason includes an excessive number of add, delete, modify, and query operations on the database table, an eighth target strategy is determined. The eighth target strategy is used to instruct to adjust the execution logic in the target SQL statement to merge or batch execute operations of the same type, or to optimize the index of the database table to improve the execution efficiency of the target SQL statement.

[0170] In this example, if too many add, delete, modify, or query operations cause slow execution of the target SQL statement, the matching eighth target strategy can be determined based on the operation type. For example, if there are too many query operations, you can optimize the database table index to improve the execution efficiency of the target SQL statement, or combine multiple query operations. If there are too many add, modify, or delete operations, you can use batch operations or separate operations to reduce the impact of these operations on database performance and improve SQL execution efficiency.

[0171] In this example, the defect in the target SQL statement may be a defect in the database, and S505 may include:

[0172] S5059. If the reason includes that the database is in an overloaded state, a target strategy is determined, wherein the overloaded state indicates that the database processes an excessive number of concurrent requests, and the target strategy is used to indicate whether to increase the database connection pool size to accommodate the connections of concurrent requests, or to limit the frequency or number of concurrent requests through a current limiting strategy.

[0173] In this example, the database is overloaded, meaning it's processing too many concurrent requests and the database connection pool is exhausted (all available database connections are occupied). The matching target policy can instruct it to increase the database connection pool size to accommodate concurrent requests, or limit the frequency or number of concurrent requests through a rate limiting policy, or optimize slow queries. This improves the database's load capacity and SQL execution efficiency.

[0174] In addition, to prevent inaccurate database table statistics, you can regularly update statistics and manually intervene in optimizer behavior to ensure the reliability of target strategy matching.

[0175] In this example, the defect in the target SQL statement may be a defect in the storage device or a defect in the network environment, and S505 may include:

[0176] S5050: If the reason includes that the resource configuration of the storage device has reached a bottleneck, determine a target policy, the target policy being used to instruct to upgrade the resource configuration of the storage device or enable a cache mechanism;

[0177] If the reason includes that the service quality of the network environment is lower than the quality threshold, a target strategy is determined, where the target strategy is used to indicate optimization of the network environment or optimization of a query result set of a target SQL statement.

[0178] Thus, if the resource allocation of the storage device reaches a bottleneck, the target strategy may be to upgrade the hardware configuration, enable the caching mechanism, or regularly organize the tablespace and rebuild the index to reduce storage space fragmentation, ensuring that the storage device allocates sufficient resource support to the user terminal, thereby improving SQL execution efficiency. If the service quality of the network environment is lower than the quality threshold (for example, the latency exceeds the threshold), the target strategy may indicate to optimize the network environment to reduce network latency or optimize the query result set of the target SQL statement to reduce the amount of data returned, thereby improving SQL execution efficiency.

[0179] For example, since there are many causes of SQL statement inefficiency, and each cause has one or more optimization solutions, there are many optimization strategies that can be configured on a computing device. This example does not list them all. However, for ease of understanding, the following will illustrate two specific examples.

[0180] For example, if there is an SQL statement containing the following logic:

[0181]

[0182]

[0183] In this SQL statement, the "from" clause connects three database tables, namely the user table, the dept table, and the trouble table, through two "join" operations, and uses the "select *" clause to query all field information in the table. The "where" condition defines the specified data to be filtered from these fields. Therefore, after analysis, Figure 7(7a) and (7b) show the execution information of the target SQL statement and the statistical information details of the three tables involved, respectively. After analysis, the data volume of the trouble table in this example is 55321, the number of queries is the largest, and there is currently no index. Therefore, "select *" is used in the target SQL statement to query all fields, and the use of the IN clause is prone to triggering a full table scan, resulting in poor query performance of the current target SQL statement. In other words, there are at least two reasons for analyzing the current target SQL statement. One is the lack of indexes on the tables involved, and the other is that the current target SQL statement contains queries such as "select *" and IN clauses that return a large amount of data. In response to these reasons, the target strategy for matching in this example may include: replacing the IN clause with an EXISTS clause to avoid using * to query all fields, and recommending creating an index on the connection field of the trouble table. If the amount of trouble data continues to increase, partitioning can be considered to improve query performance.

[0184] Let's take another example. If there is an SQL statement that includes the following logic:

[0185]

[0186] In this SQL statement, the "from" clause connects two database tables, namely large_tabl and related_tabl, through the "join" operation. The "SELECT" clause defines the fields "id", "status", "created_at", and "type" to be queried in these two tables. The "where" condition defines the specified data to be filtered from these fields. Therefore, after analysis, Figure 8(8a) and (8b) show the execution information of the target SQL statement and the statistical information details of the two tables involved, respectively. After analysis, the target SQL statement itself has no logical defects, but the execution time is long. Therefore, from the perspective of table dimension analysis: the data volume of the large_tabl table is 20,000 (lower than the threshold of 1 million), the number of new additions is relatively high, and the number of indexes is 12 (exceeding the preset threshold of 6). Therefore, the cause of the target SQL statement is determined to be: too many indexes. In response to this cause, the matching target strategy is: 1. Streamlining indexes, specifically, deleting infrequently used indexes. Since large_table is frequently updated, the number of indexes can be appropriately reduced to reduce the cost of index maintenance; or merging redundant indexes. If there are multiple indexes covering the same column, you can consider merging them; 2. Optimizing insert operations: Since the number of new additions is large, the insert operation can be changed to batch insert and the index can be disabled. That is, when inserting a large amount of data, the index can be temporarily disabled and re-enabled after the insert is completed; 3. Using partitioned tables: If the data volume of the table is very large and the index cannot be optimized, you can consider partitioning the table to improve query performance.

[0187] Understandably, Figure 7 and Figure 8 The database table statistics shown can also include more detailed sub-items for recording CRUD (Create, Delete, Modify, and Lookup) operations, facilitating in-depth and detailed cause analysis and location. Furthermore, the generated target strategy can provide more in-depth and specific suggestions, such as directing replacements for specific fields in a statement, adding indexes to specific fields in a statement, providing optimized SQL statements, and providing SQL for sharding databases and tables, allowing personnel to directly implement these suggestions.

[0188] S506: Output the target policy.

[0189] In this example, the matching target strategy can be presented to personnel through a visual interface to guide them to understand and optimize statement performance accordingly.

[0190] In this way, by quickly locating the causes of inefficiency from the dimensions of the SQL statement logic and the database tables involved, various suitable optimization strategies are automatically matched for personnel's reference, thereby improving the intelligence and optimization effect of SQL performance optimization.

[0191] In some examples, if multiple target strategies are matched, these multiple target strategies can also be prioritized according to preset priority rules so that personnel can more intuitively understand the priority of the strategies and thus select the optimal (e.g., most effective, easiest to implement, etc.) strategy for statement performance optimization.

[0192] For this purpose, for example, Figure 9 As shown, S506 may specifically include:

[0193] S5061: If multiple target strategies are matched corresponding to the multiple target SQL statements, the multiple target strategies are prioritized according to the optimization urgency of the multiple target SQL statements;

[0194] If a plurality of target strategies are matched to a single target SQL statement, the target strategies of the single target SQL statement are prioritized according to their effectiveness.

[0195] In this example, the higher the optimization urgency, the greater the execution frequency of the target SQL statement, the longer the response time, or the more resources consumed. Therefore, when there are multiple target SQL statements, the target SQL statement with higher optimization urgency has a higher priority for its corresponding target strategy. Moreover, for a single target SQL statement, when it has multiple target strategies, the target strategies of the single target SQL statement are prioritized according to the degree of optimization benefit. The higher the degree of benefit, the greater the performance improvement, the higher the maintainability, the less resources consumed, or the more suitable for business needs, etc. The target strategy with higher optimization benefit has a higher priority. For example, for Figure 8 In the example shown, the three target strategies are given for a SQL statement. The priorities for these strategies are: 1. Simplify indexes, 2. Optimize insert operations, and 3. Use partitioned tables. This is because simplifying indexes provides the highest optimization performance.

[0196] S5062: Output the multiple target policies according to the result of the priority sorting.

[0197] In this way, personnel can select the most urgent or optimal strategy to optimize statement performance based on the output strategy priority, thereby improving the optimization effect.

[0198] Some possible examples include Figure 10 As shown, the method may further include:

[0199] S1001, associate and store the target SQL statement, environment information, reason and target strategy in the remote dictionary server REDIS.

[0200] In this example, after obtaining the optimization strategy, the computing device may also associate and store the obtained target SQL statement, environment information, reason, and target strategy in a remote dictionary server (REDIS) for future use.

[0201] S1002, when a first SQL statement and first environment statistical information are obtained, and the first SQL statement and the first environment information are associated in a remote dictionary server REDIS, a first target strategy associated with the first SQL statement and the first environment information record is determined from the remote dictionary server REDIS.

[0202] In this example, if, during the subsequent statement monitoring and analysis process, it is detected that the captured target SQL statement (the first SQL statement) and its environment information (the first environment statistical information) are recorded in REDIS, the target strategy recorded in REDIS can be used directly without subsequent analysis and strategy matching, thereby reducing the computational complexity of the optimization operation.

[0203] S1003: Output a first target strategy to guide optimization of the first SQL statement.

[0204] In this example, in addition to this function, the records stored in the remote dictionary server REDIS can also be used as optimization history data for human query.

[0205] Based on the methods in the above embodiments, embodiments of the present application provide a computing device. The computing device may include: at least one memory for storing programs; and at least one processor for executing the programs stored in the memory. When the programs stored in the memory are executed, the processor is configured to execute the methods in the above embodiments.

[0206] Based on the method in the above embodiment, an embodiment of the present application provides a computer-readable storage medium, which stores a computer program. When the computer program runs on a processor, the processor executes the method in the above embodiment.

[0207] Based on the method in the above embodiment, an embodiment of the present application provides a computer program product, characterized in that when the computer program product runs on a processor, the processor executes the method in the above embodiment.

[0208] Based on the method in the above embodiment, the present application embodiment also provides a chip. Figure 11 , Figure 11 This is a schematic diagram of the structure of a chip provided in an embodiment of the present application. Figure 11 As shown, the chip 900 includes one or more processors 901 and an interface circuit 902. Optionally, the chip 900 may also include a bus 903.

[0209] The processor 901 may be an integrated circuit chip with signal processing capabilities. During implementation, each step of the above method can be completed by an integrated logic circuit of hardware in the processor 901 or instructions in the form of software. The above-mentioned processor 901 can be a general-purpose processor, a digital communicator (DSP), an application-specific integrated circuit (ASIC), a field programmable gate array (FPGA) or other programmable logic device, a discrete gate or transistor logic device, or a discrete hardware component. The various methods and steps disclosed in the embodiments of the present application can be implemented or executed. The general-purpose processor can be a microprocessor or the processor can also be any conventional processor, etc.

[0210] The interface circuit 902 can be used to send or receive data, instructions or information. The processor 901 can use the data, instructions or other information received by the interface circuit 902 to process it, and can send the processing completion information through the interface circuit 902.

[0211] Optionally, the chip 900 further includes a memory, which may include a read-only memory and a random access memory, and provides operation instructions and data to the processor. Part of the memory may also include a non-volatile random access memory (NVRAM).

[0212] Optionally, the memory stores an executable software module or a data structure, and the processor can perform corresponding operations by calling an operation instruction stored in the memory (the operation instruction may be stored in an operating system).

[0213] Optionally, the interface circuit 902 may be configured to output the execution result of the processor 901 .

[0214] It should be noted that the corresponding functions of the processor 901 and the interface circuit 902 can be implemented through hardware design, software design, or a combination of hardware and software, and there is no limitation here.

[0215] It should be understood that each step of the above method embodiment can be completed by a hardware-based logic circuit or a software-based instruction in a processor.

[0216] It is understood that the order of execution of the steps in the above embodiments does not necessarily imply a specific order of execution. The order of execution of each process should be determined by its function and inherent logic, and should not constitute any limitation on the implementation process of the embodiments of the present application. In addition, in some possible implementations, the steps in the above embodiments can be selectively executed according to actual circumstances, and can be executed partially or completely, which is not limited here.

[0217] It is understood that the processor in the embodiments of the present application may be a central processing unit (CPU), or may be other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field programmable gate arrays (FPGA), or other programmable logic devices, transistor logic devices, hardware components, or any combination thereof. The general-purpose processor may be a microprocessor or any conventional processor.

[0218] The method steps in the embodiments of the present application can be implemented by hardware or by a processor executing software instructions. The software instructions can be composed of corresponding software modules, which can be stored in random access memory (RAM), flash memory, read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), registers, hard disks, mobile hard disks, CD-ROMs or any other form of storage medium known in the art. An exemplary storage medium is coupled to the processor so that the processor can read information from the storage medium and write information to the storage medium. Of course, the storage medium can also be a component of the processor. The processor and the storage medium can be located in an ASIC.

[0219] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented using software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present application is generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium or transmitted via the computer-readable storage medium. The computer instructions can be transmitted from one website, computer, server or data center to another website, computer, server or data center via a wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) method. The computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more available media integrated. The available medium can be a magnetic medium (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid state drive (SSD)).

[0220] It will be understood that the various numerical numbers involved in the embodiments of the present application are merely distinctions for the convenience of description and are not intended to limit the scope of the embodiments of the present application.

Claims

1. A SQL statement optimization method, characterized in that: The method comprises: Obtaining a target SQL statement and environment information, wherein the target SQL statement is a SQL statement whose execution efficiency is lower than an expected efficiency, and the environment information is used to describe the environment involved in executing the target SQL statement, the environment including the software environment and / or the hardware environment; determining, based on the target SQL statement and the environment information, a reason why the execution efficiency of the target SQL statement is lower than the expected efficiency, the reason including at least a defect in the target SQL statement and / or a defect in the environment; A target strategy is determined according to the cause, where the target strategy is used to instruct to optimize defects in the target SQL statement and / or defects in the environment.

2. The method according to claim 1, characterized in that The obtaining of the target SQL statement includes: Intercepting SQL statements executed in the database and execution information of the SQL statements through an interceptor, wherein the execution information includes at least execution time and / or resource consumption; According to the execution information, the target SQL statement having an execution efficiency lower than an expected efficiency is determined from the SQL statements, wherein the execution efficiency lower than the expected efficiency is manifested as the execution duration exceeding a preset duration or the resource consumption exceeding a preset resource amount.

3. The method according to claim 1 or 2, characterized in that The defects in the target SQL statement include one or more of execution logic defects, index usage defects, query expansion, and lock contention; Determining a target strategy based on the cause includes: If the reason includes that the target SQL statement has an execution logic defect, determining a first target strategy, wherein the first target strategy is used to instruct to reduce complex nesting and inefficient operators in the target SQL statement to simplify the execution logic of the target SQL statement, wherein the inefficient operators are SQL operators that reduce the execution efficiency; If the reason includes that the target SQL statement has an index usage defect, determining a second target strategy, wherein the second target strategy is used to instruct to correct necessary indexes in the target SQL statement and make the composite index comply with the leftmost prefix principle; If the reason includes that the target SQL statement has a query expansion defect, determining a third target strategy, wherein the third target strategy is used to instruct to use paging query and optimize query fields in the target SQL statement to reduce returned data; If the reason includes a defect of lock contention in the target SQL statement, a fourth target strategy is determined, where the fourth target strategy is used to instruct to shorten the holding time of the lock, where the lock is a lock triggered by the target SQL statement.

4. The method according to any one of claims 1 to 3, characterized in that The software environment includes the database tables involved when executing the target SQL statement, and the environment information includes at least statistical information of the database tables, and the statistical information is used to describe one or more of the data volume, number of indexes, table structure, data type, and number of add, delete, modify, and query operations of the database tables.

5. The method according to claim 4, characterized in that Determining a target strategy based on the cause includes: If the reason includes an excessive amount of data in the database table, determining a fifth target strategy, wherein the fifth target strategy is used to instruct archiving historical data of the database table, or optimizing the database table using a partition table, a summary table, or a materialized view; If the reason includes that the number of indexes of the database table is unreasonable, a sixth target strategy is determined, where the sixth target strategy is used to instruct to add necessary indexes to the database table or delete redundant indexes. If the reason includes that the database table has a structural defect, determining a seventh target strategy, wherein the seventh target strategy is used to instruct adjustment of the table structure or data type of the database table; If the reason includes an excessive number of add, delete, modify and query operations on the database table, an eighth target strategy is determined, where the eighth target strategy is used to instruct adjustment of the execution logic in the target SQL statement to merge or batch execute operations of the same type, or to optimize the index of the database table to improve the execution efficiency of the target SQL statement.

6. The method according to claim 1 or 2, characterized in that The software environment includes a database involved in executing the target SQL statement, and the environment information includes at least load information of the database; Determining a target strategy based on the cause includes: If the cause includes that the database is in an overloaded state, the target strategy is determined, wherein the overloaded state indicates that the database processes an excessive number of concurrent requests, and the target strategy is used to instruct to increase the connection pool size of the database to accommodate the connections of the concurrent requests, or to limit the frequency or number of concurrent requests through a current limiting strategy.

7. The method according to claim 1 or 2, characterized in that The hardware environment includes storage devices involved in executing the target SQL statement, and the environment information includes at least resource configuration information of the storage devices; Determining a target strategy based on the cause includes: If the reason includes that the resource configuration of the storage device reaches a bottleneck, the target policy is determined, where the target policy is used to instruct to upgrade the resource configuration of the storage device or enable a cache mechanism.

8. The method according to claim 1 or 2, characterized in that The hardware environment includes the network environment involved in executing the target SQL statement, and the environment information includes at least service quality information of the network environment; Determining a target strategy based on the cause includes: If the reason includes that the service quality of the network environment is lower than a quality threshold, the target strategy is determined, where the target strategy is used to instruct optimization of the network environment or optimization of a query result set of the target SQL statement.

9. The method according to any one of claims 1 to 8, characterized in that After determining the target strategy based on the cause, the method includes: If multiple target strategies are matched corresponding to multiple target SQL statements, the matched target strategies are prioritized according to the optimization urgency of the multiple target SQL statements, wherein a higher optimization urgency is manifested as a greater execution frequency, a longer response time, or more resource consumption; If multiple target strategies are matched for a single target SQL statement, the target strategies of the single target SQL statement are prioritized according to the degree of effectiveness, wherein a higher degree of effectiveness indicates a greater performance improvement, higher maintainability, less resource consumption, or greater suitability for business needs.

10. A computing device, characterized in that include: at least one memory for storing a program; at least one processor, configured to execute the program stored in the memory; When the program stored in the memory is executed, the processor is configured to execute the method according to any one of claims 1 to 9.

Images