CN112035513A - SQL statement performance optimization method, device, terminal and storage medium - Google Patents

Abstract

The invention discloses a SQL statement performance optimization method, device, terminal and storage medium, wherein the method includes: obtaining an execution plan of an SQL statement input by a user; evaluating the performance value of the SQL statement according to the execution plan; When the performance threshold is preset, a stress test is performed on the SQL statement to determine the point to be optimized for the SQL statement; the preset SQL performance optimization parameter table is queried based on the SQL statement and the point to be optimized to optimize the SQL statement. The present invention exposes potential defects of the SQL statement by performing stress testing on the SQL statement, so as to carry out targeted optimization and improve the performance of the SQL statement.

Description

SQL statement performance optimization method, device, terminal and storage medium

technical field

The present application relates to the field of computer technology, and in particular, to a method, device, terminal and storage medium for optimizing the performance of SQL statements.

Background technique

With the advent of the era of big data, the database often needs to select the data that meets the specific needs from the massive data to complete the query operation. Structured Query Language (SQL) is a database query and programming language used for accessing data and querying, updating and managing relational database systems. In the actual work process, developers often need to write SQL statements with complex logic. Some developers may write SQL statements with poor performance because they are not familiar with the underlying principles of SQL. Therefore, it is particularly important to test the performance of SQL statements. important.

Many management and development tools now provide the function of viewing graphical execution plans, such as MySQL Workbench, Oracle SQL Developer, SQL Server Management Studio, DBeaver, etc., and execution plans can also be viewed through the command line. However, the above method can only inform the developer of the performance of the SQL statement, but cannot determine the specific defects in the SQL statement, nor can it be optimized.

SUMMARY OF THE INVENTION

The present application provides a method, device, terminal and storage medium for optimizing the performance of SQL statements, so as to solve the problem that existing development tools cannot visually display defects in SQL statements and perform targeted optimization.

In order to solve the above-mentioned technical problems, a technical solution adopted in the present application is to provide a method for optimizing the performance of an SQL statement, including: obtaining an execution plan of the SQL statement input by a user; evaluating the performance value of the SQL statement according to the execution plan; When the first preset performance threshold is reached, a stress test is performed on the SQL statement to determine the point to be optimized of the SQL statement; the preset SQL performance optimization parameter table is queried based on the SQL statement and the point to be optimized to optimize the SQL statement.

As a further improvement of the present application, performing a stress test on the SQL statement to determine the point to be optimized of the SQL statement includes: obtaining DDL-related information of the table field from the SQL statement to confirm the target table; generating a preset simulation data generator through a preset simulation data generator The simulation data of the target table; test the SQL statement according to the simulation data, and output the point to be optimized of the SQL statement.

As a further improvement of the present application, evaluating the performance value of the SQL statement according to the execution plan includes: evaluating the SQL statement according to the consumption resources when executing the SQL statement, the way of accessing the table, and the connection sequence and connection method of the table to obtain the performance value.

As a further improvement of the present application, after evaluating the performance value of the SQL statement according to the execution plan, the method further includes: when the performance value exceeds the second preset performance threshold, obtaining the data structure principle and principle explanation of the SQL statement input by the developer; After the data structure principle and principle explanation correspond to the SQL statement, write it into the SQL performance optimization parameter table.

As a further improvement of the present application, after the SQL statement is optimized, the method further includes: recording an optimization record of the SQL statement, generating an execution plan of the optimized SQL statement, and storing it in a cache.

As a further improvement of the present application, after optimizing the SQL statement, the method further includes: performing a stress test on the optimized SQL statement again to detect whether the point to be optimized has been optimized; if not, recording the SQL statement, and putting the SQL statement Report to be optimized.

As a further improvement of the present application, after optimizing the SQL statement, the method further includes: uploading the optimized SQL statement to the blockchain.

In order to solve the above-mentioned technical problem, another technical solution adopted in the present application is to provide a SQL statement performance optimization device, comprising: an acquisition module for acquiring an execution plan of the SQL statement input by a user; an evaluation module for obtaining an execution plan according to the execution plan Evaluate the performance value of the SQL statement; the test module is used to perform stress testing on the SQL statement when the performance value does not reach the first preset performance threshold to determine the point to be optimized of the SQL statement; the optimization module is used to perform a stress test on the SQL statement based on the The optimization point queries the preset SQL performance optimization parameter table to optimize the SQL statement.

In order to solve the above-mentioned technical problem, another technical solution adopted in the present application is to provide a terminal, the terminal includes a processor and a memory coupled to the processor, wherein the memory stores a program for implementing the above-mentioned SQL statement performance optimization method Instructions; used by the processor to execute program instructions stored in memory to optimize SQL statements.

In order to solve the above technical problem, another technical solution adopted in the present application is to provide a storage medium storing a program file capable of implementing the above-mentioned method for optimizing the performance of SQL statements.

The beneficial effects of the present application are as follows: the SQL statement performance optimization method of the present application preliminarily determines whether the SQL statement has defects by utilizing the execution plan of the SQL statement, and when it is confirmed that the SQL statement has defects through the execution plan, the SQL statement is subjected to a stress test, Thus, the defects of the SQL statement are exposed, and the SQL statement is optimized according to the pre-set SQL performance optimization parameter table, which effectively helps developers discover hidden problems in SQL in time, and directly provides optimization for developers. The solution improves the optimization efficiency of SQL statements.

Description of drawings

1 is a schematic flowchart of a method for optimizing SQL statement performance according to a first embodiment of the present invention;

Fig. 2 is the schematic flow chart of the SQL statement performance optimization method of the second embodiment of the present invention;

Fig. 3 is the schematic flow chart of the SQL statement performance optimization method of the third embodiment of the present invention;

Fig. 4 is the functional module schematic diagram of the SQL statement performance optimization device of the embodiment of the present invention;

5 is a schematic structural diagram of a terminal according to an embodiment of the present invention;

FIG. 6 is a schematic structural diagram of a storage medium according to an embodiment of the present invention.

Detailed ways

The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, but not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present application.

The terms "first", "second" and "third" in this application are only used for descriptive purposes, and should not be construed as indicating or implying relative importance or implying the number of indicated technical features. Thus, a feature defined as "first", "second", "third" may expressly or implicitly include at least one of that feature. In the description of the present application, "a plurality of" means at least two, such as two, three, etc., unless otherwise expressly and specifically defined. All directional indications (such as up, down, left, right, front, rear...) in the embodiments of the present application are only used to explain the relative positional relationship between components under a certain posture (as shown in the accompanying drawings). , motion situation, etc., if the specific posture changes, the directional indication also changes accordingly. Furthermore, the terms "comprising" and "having" and any variations thereof are intended to cover non-exclusive inclusion. For example, a process, method, system, product or device comprising a series of steps or units is not limited to the listed steps or units, but optionally also includes unlisted steps or units, or optionally also includes For other steps or units inherent to these processes, methods, products or devices.

Reference herein to an "embodiment" means that a particular feature, structure, or characteristic described in connection with the embodiment can be included in at least one embodiment of the present application. The appearances of the phrase in various places in the specification are not necessarily all referring to the same embodiment, nor a separate or alternative embodiment that is mutually exclusive of other embodiments. It is explicitly and implicitly understood by those skilled in the art that the embodiments described herein may be combined with other embodiments.

FIG. 1 is a schematic flowchart of a method for optimizing SQL statement performance according to the first embodiment of the present invention. It should be noted that, if there is substantially the same result, the method of the present invention is not limited to the sequence of the processes shown in FIG. 1 . As shown in Figure 1, the method includes the steps:

Step S101: Obtain the execution plan of the SQL statement input by the user.

It should be noted that the performance of SQL statements affects the performance of the entire database to a certain extent. In many cases, the low performance of the database is basically caused by bad SQL statements, and the execution plan of the SQL statement determines that the SQL statement will How will the data be extracted from the database and returned to the client.

In step S101, the execution plan of the SQL statement input by the user can be obtained by executing the computer instruction information. For example, the user can manually input the SQL statement first, and then pre-execute the SQL statement input by the user by triggering the computer instruction information. , so as to obtain the execution plan of the SQL statement. In this embodiment, six ways to obtain the execution plan of an SQL statement are introduced:

1. Use the explain plan for command to generate an execution plan and display it;

2. Use the dbms_system stored procedure to generate an execution plan;

3. Use the autotrace tool to obtain the execution plan;

4. Extract the execution plan directly from the shared_pool;

5. Get the execution plan from the AWR report library;

6. Using 10046 event tracking, by enabling event tracking, a tracking file is generated, and finally the execution plan is obtained by analyzing the tracking file.

In this embodiment, the explain plan for command is preferably used to obtain the execution plan of the SQL statement.

Step S102: Evaluate the performance value of the SQL statement according to the execution plan.

In step S102, the performance of the SQL statement can be determined by the resources consumed when executing the SQL statement, the way the SQL statement accesses the table, the connection order and connection method of the table, etc. Therefore, the performance value of the SQL statement is evaluated according to the execution plan. Including: evaluating the SQL statement according to the consumption of resources when executing the SQL statement, the way of accessing the table, and the connection sequence and connection method of the table to obtain the performance value.

For example, there are two main ways to access a table: full table scan (TABLE ACCESS FULL) and index scan (INDEX SCAN). There may be defects in the method; if there is no suitable index on the large table and a full table scan is performed, it is necessary to analyze whether an index can be established, or whether a more suitable table connection method and connection order can be selected to improve efficiency. The connection order of tables refers to the sequential access order in which a table is used as a driving table to connect other tables. The connection method of tables refers to the connection process when two tables obtain data that meets the conditions. At present, there are three main table connection methods, nested loop (NESTED LOOPS), hash join (HASH JOIN) and sort merge join (SORT MERGE JOIN) ). Among them, nested loops and hash joins are more commonly used. Nested loop refers to processing two cursors with two layers of loops. The outer cursor is called the driving table. Oracle retrieves the data of the driving table and substitutes it into the inner cursor one by one to find all the data that meets the WHERE condition. Therefore, the inner cursor table The better the selectivity of the indexes available in , the higher the performance of the nested loop join. Hash join means that the data of the driving table is hashed into the memory according to the condition field, and then the rows that meet the condition are matched in the memory. Hash joins require appropriate memory, and can only be used when the WHERE condition of joining two tables has an equal sign in CBO optimization mode. Hash joins are more efficient than nested loops when the amount of data in the table is large and there is no suitable index available in the table. Sort merge join is to first sort the associated columns of the associated table, and then extract data from the respective sorting table to match in another sorting table. Because more sorting is required, more resources are consumed.

Step S103: When the performance value does not reach the first preset performance threshold, perform a stress test on the SQL statement to determine the point to be optimized of the SQL statement.

It should be noted that the points to be optimized in the SQL statement include excessive resource consumption for executing the SQL statement, inappropriate way of accessing the table by the SQL statement, and inappropriate connection method of the table. The first preset performance threshold is preset, and when the performance value of the SQL statement is lower than the first preset performance threshold, it indicates that the SQL statement has a defect to be optimized.

In step S103, the defects existing in some SQL statements cannot be perceived by developers when the amount of data is small. For example, when querying data of a table, if the amount of data in the table is small, two full table scans and index scans are used. In this way, the time difference between the two methods is very small and cannot be perceived by developers. At this time, developers may mistakenly believe that the effects of the two methods are the same. However, when the amount of data in the table is large, the time difference will be Enlarged, the consumption time of the full table scan method is significantly higher than that of the index scan method. Therefore, in this embodiment, when it is judged by the execution plan that the performance of the SQL statement is poor, a stress test is performed on the SQL statement to expose its existing defects, so as to confirm the existing point to be optimized of the SQL statement.

Further, in some embodiments, performing a stress test on the SQL statement, and determining the point to be optimized of the SQL statement specifically includes:

1. Obtain the DDL-related information of the table field from the SQL statement to confirm the target table.

2. Generate simulation data of a preset number of target tables through a preset simulation data generator.

Specifically, the simulated data generator is specifically composed of a lazy mock or a Data-Processer. After obtaining the table field DDL related information from the SQL statement, the simulated data generator simulates the actual data in the target table according to the table field DDL related information. to get a preset amount of simulation data. It should be noted that the preset number is preset, and fully meets the data volume required for the stress test of the SQL statement.

3. Test the SQL statement according to the simulated data, and output the point to be optimized of the SQL statement.

Specifically, the SQL statement is tested with simulated data, and the test result is recorded and analyzed, thereby confirming the defect in the SQL statement, and outputting the defect as the point to be optimized of the SQL statement.

Step S104 : query a preset SQL performance optimization parameter table based on the SQL statement and the point to be optimized, so as to optimize the SQL statement.

It should be noted that the SQL performance optimization parameter table is preset, which integrates all SQL statement optimization solutions known to those skilled in the art. For example, optimization rules related to indexes and underlying data structure principles can be written into the SQL performance optimization parameter table.

In step S104, after confirming the to-be-optimized point of the SQL statement, query an appropriate optimization scheme from the SQL performance optimization parameter table, so as to optimize the SQL statement.

Further, in some embodiments, after step S104, the method further includes: performing a stress test on the optimized SQL statement again to detect whether the point to be optimized has been optimized; if not, recording the SQL statement, and storing the SQL statement to be Optimizing point reporting.

Specifically, when performing a stress test, it is not necessary to simulate again to generate simulated data, and the test can be performed directly with the previously generated simulated data. In this embodiment, the stress test is performed again to verify whether the optimization of the SQL statement is reasonable. If the optimization is unreasonable, it means that the optimization suggestion of the SQL performance optimization parameter table cannot solve the defect existing in the SQL statement. In this case, the defect is Report it to the developer, and the developer will take the initiative to solve the defect, and the solution method and principle description of the defect can also be written into the SQL performance optimization parameter table to enrich the content in the SQL performance optimization parameter table.

The SQL statement performance optimization method according to the first embodiment of the present invention preliminarily determines whether the SQL statement has defects by using the execution plan of the SQL statement. There are defects in the SQL statement, and the SQL statement is optimized according to the pre-set SQL performance optimization parameter table, which effectively helps developers discover hidden problems in SQL in time, and directly gives developers an optimization plan to improve The optimization efficiency of SQL statements.

Further, in some embodiments, after optimizing the SQL statement, the method further includes: uploading the optimized SQL statement to the blockchain.

Specifically, the corresponding summary information is obtained based on the optimized SQL statement. Specifically, the summary information is obtained by hashing the optimized SQL statement, for example, by using the sha256s algorithm. Uploading summary information to the blockchain ensures its security and fairness and transparency to users. The user equipment can download the summary information from the blockchain to verify whether the optimized SQL statement has been tampered with. The blockchain referred to in this example is a new application mode of computer technology such as distributed data storage, point-to-point transmission, consensus mechanism, and encryption algorithm. Blockchain, essentially a decentralized database, is a series of data blocks associated with cryptographic methods. Each data block contains a batch of network transaction information to verify its Validity of information (anti-counterfeiting) and generation of the next block. The blockchain can include the underlying platform of the blockchain, the platform product service layer, and the application service layer.

Further, in some embodiments, after optimizing the SQL statement, the method further includes: obtaining the data structure principle and related explanations used when optimizing the SQL statement from the SQL performance optimization parameter table, and then comparing the optimization result, The adopted data structure principle and related explanations are output together.

It should be noted that the data structure principle and related explanations are input into the SQL performance optimization parameter table in advance when constructing the SQL performance optimization parameter table.

In this embodiment, the optimization result of the SQL statement, the data structure principle and related explanations used in optimizing the SQL statement in the SQL performance optimization parameter table are output together, so as to help developers understand and learn, and improve the SQL knowledge reserves of developers. .

FIG. 2 is a schematic flowchart of a method for optimizing SQL statement performance according to a second embodiment of the present invention. It should be noted that, if there is substantially the same result, the method of the present invention is not limited to the sequence of the processes shown in FIG. 2 . As shown in Figure 2, the method includes the steps:

Step S201: Obtain the execution plan of the SQL statement input by the user.

In this embodiment, step S201 in FIG. 2 is similar to step S101 in FIG. 1 , and for the sake of brevity, details are not repeated here.

Step S202: Evaluate the performance value of the SQL statement according to the execution plan. When the performance value does not reach the first preset performance threshold, steps S203 to S204 are performed. When the performance value exceeds the second preset performance threshold, steps S205 to S206 are performed.

In this embodiment, step S202 in FIG. 2 is similar to step S102 in FIG. 1 , and for the sake of brevity, details are not repeated here.

Step S203: Perform stress testing on the SQL statement to determine the point to be optimized of the SQL statement.

In this embodiment, step S203 in FIG. 2 is similar to step S103 in FIG. 1 , and for the sake of brevity, details are not repeated here.

Step S204 : query the preset SQL performance optimization parameter table based on the SQL statement and the point to be optimized, so as to optimize the SQL statement.

In this embodiment, step S204 in FIG. 2 is similar to step S104 in FIG. 1 , and for the sake of brevity, details are not repeated here.

Step S205: Obtain the data structure principle and principle explanation of the SQL statement input by the developer.

It should be noted that the second preset performance threshold is preset and greater than the first preset performance threshold.

In step S205, when the performance value of the SQL statement is higher than the second preset performance threshold, it indicates that the performance of the SQL statement is excellent, and its sample can be used for reference. Therefore, the developer needs to input the data structure of the SQL statement The principle and principle explanation, as a reference and suggestion for subsequent optimization of the SQL statement, can also help other developers understand the SQL statement when they see the data structure principle and principle explanation.

Step S206: After the data structure principle and principle explanation are corresponding to the SQL statement, write the SQL performance optimization parameter table.

In step S206, the SQL statement, the corresponding data structure principle and principle explanation are all written into the SQL performance optimization parameter table, thereby enriching the samples in the SQL performance optimization parameter table. It should be noted that when there is a sample of the SQL statement in the SQL performance optimization parameter table, it does not need to be written again.

On the basis of the first embodiment, the SQL statement performance optimization method according to the second embodiment of the present invention, when the performance of the SQL statement is higher than the second preset performance threshold, it can be considered that the performance of the SQL statement is excellent. Therefore, when the performance of the SQL statement is higher than the second preset performance threshold When the performance of the SQL statement exceeds the second preset performance threshold, the developer is reminded to input the data structure principle and principle explanation of the SQL statement, and the SQL statement is written as a sample into the SQL performance optimization parameter table as a follow-up optimization Suggestions of similar SQL statements for reference and learning by other developers.

FIG. 3 is a schematic flowchart of a method for optimizing SQL statement performance according to a third embodiment of the present invention. It should be noted that, if there is substantially the same result, the method of the present invention is not limited to the flow sequence shown in FIG. 3 . As shown in Figure 3, the method includes the steps:

Step S301: Obtain the execution plan of the SQL statement input by the user.

In this embodiment, step S301 in FIG. 3 is similar to step S101 in FIG. 1 , and for the sake of brevity, details are not repeated here.

Step S302: Evaluate the performance value of the SQL statement according to the execution plan.

In this embodiment, step S302 in FIG. 3 is similar to step S102 in FIG. 1 , and for the sake of brevity, details are not repeated here.

Step S303: When the performance value does not reach the first preset performance threshold, perform a stress test on the SQL statement to determine the point to be optimized of the SQL statement.

In this embodiment, step S303 in FIG. 3 is similar to step S103 in FIG. 1 , and for the sake of brevity, details are not repeated here.

Step S304 : query the preset SQL performance optimization parameter table based on the SQL statement and the point to be optimized, so as to optimize the SQL statement.

In this embodiment, step S304 in FIG. 3 is similar to step S104 in FIG. 1 , and for the sake of brevity, details are not repeated here.

Step S305: Record the optimization record of the SQL statement, generate an execution plan of the optimized SQL statement, and store it in the cache. It should be noted that the cache is preset to store the execution plan of the SQL statement. The optimization record can record whether the SQL statement has been optimized.

In step S305, after the SQL statement is optimized, the optimization record of the SQL statement is recorded, and the execution plan of the optimized SQL statement needs to be regenerated. At this time, the optimization record of the SQL statement can be queried to confirm whether the SQL statement is It is the first optimization. If it is the first optimization, the execution plan of the optimized SQL statement will be generated, and the execution plan will be saved in the cache. If it is not the first optimization, the corresponding query can be directly obtained from the cache. The execution plan does not need to be regenerated.

Further, the optimization record can also be used to record the optimization result of the SQL statement. After the developer enters the SQL statement, it is queried whether there is an optimization record of the SQL statement. The execution plan corresponding to the SQL statement is queried in the cache, and the SQL statement is executed according to the execution plan, and the SQL statement does not need to be optimized again, so as to avoid the repeated optimization process occupying too many resources. On the basis of the first embodiment, the SQL statement performance optimization method according to the third embodiment of the present invention stores the optimization record of the SQL statement and the execution plan of the optimized SQL statement. The SQL statement is optimized directly according to the optimization record, and the execution plan is notified to be called directly, and the optimized SQL statement does not need to be parsed, which improves the query efficiency of the SQL statement and avoids the repeated optimization process occupying too many resources.

FIG. 4 is a schematic diagram of functional modules of an apparatus for optimizing SQL statement performance according to an embodiment of the present invention. As shown in FIG. 4 , the apparatus 40 includes an acquisition module 41 , an evaluation module 42 , a testing module 43 and an optimization module 44 .

Obtaining module 41, for obtaining the execution plan of the SQL statement input by the user;

The evaluation module 42 is used for evaluating the performance value of the SQL statement according to the execution plan;

A test module 43, configured to perform a stress test on the SQL statement when the performance value does not reach the first preset performance threshold, and determine the point to be optimized of the SQL statement;

The optimization module 44 is configured to query a preset SQL performance optimization parameter table based on the SQL statement and the point to be optimized, so as to optimize the SQL statement.

Optionally, the test module 43 performs a stress test on the SQL statement, and the operation of determining the point to be optimized of the SQL statement can also be: obtaining the table field DDL related information from the SQL statement to confirm the target table; The generator generates a preset number of simulated data of the target table; tests the SQL statement according to the simulated data, and outputs the to-be-optimized point of the SQL statement.

Optionally, the operation of evaluating the performance value of the SQL statement by the evaluation module 42 according to the execution plan may also be to evaluate the SQL statement according to the consumption of resources during the execution of the SQL statement, the way of accessing the table, and the connection sequence and connection method of the table to obtain the performance value.

Optionally, after the evaluation module 42 evaluates the performance value of the SQL statement according to the execution plan, when the performance value exceeds the second preset performance threshold, it is also used to obtain the data structure principle and principle explanation of the SQL statement input by the developer. ; After corresponding the data structure principle and principle explanation with the SQL statement, write it into the SQL performance optimization parameter table.

Optionally, after the optimization module 44 performs the optimization operation on the SQL statement, it is further configured to record the optimization record of the SQL statement, generate an execution plan of the optimized SQL statement, and store it in the cache.

Optionally, after the optimization module 44 performs the optimization operation on the SQL statement, it is also used to perform a stress test on the optimized SQL statement again to detect whether the point to be optimized has been optimized; The statement is reported to the optimization point.

Optionally, after optimizing the SQL statement, the optimization module 44 is further configured to upload the optimized SQL statement to the blockchain.

Please refer to FIG. 5 , which is a schematic structural diagram of a terminal according to an embodiment of the present invention. As shown in FIG. 5 , the terminal 60 includes a processor 61 and a memory 62 coupled to the processor 61 .

The memory 62 stores program instructions for implementing the SQL statement performance optimization method described in any of the foregoing embodiments.

The processor 61 is configured to execute program instructions stored in the memory 62 to optimize the SQL statement.

The processor 61 may also be referred to as a CPU (Central Processing Unit, central processing unit). The processor 61 may be an integrated circuit chip with signal processing capability. The processor 61 may also be a general purpose processor, digital signal processor (DSP), application specific integrated circuit (ASIC), field programmable gate array (FPGA) or other programmable logic device, discrete gate or transistor logic device, discrete hardware components . A general purpose processor may be a microprocessor or the processor may be any conventional processor or the like.

Referring to FIG. 6, FIG. 6 is a schematic structural diagram of a storage medium according to an embodiment of the present invention. The storage medium of the embodiment of the present invention stores a program file 71 capable of implementing all the above methods, wherein the program file 71 may be stored in the above-mentioned storage medium in the form of a software product, and includes several instructions to enable a computer device (which can be A personal computer, a server, or a network device, etc.) or a processor (processor) executes all or part of the steps of the methods described in the various embodiments of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, Read-Only Memory (ROM, Read-Only Memory), Random Access Memory (RAM, Random Access Memory), magnetic disk or optical disk and other media that can store program codes , or terminal devices such as computers, servers, mobile phones, and tablets.

In the several embodiments provided in this application, it should be understood that the disclosed system, apparatus and method may be implemented in other manners. For example, the apparatus embodiments described above are only illustrative. For example, the division of units is only a logical function division. In actual implementation, there may be other division methods, for example, multiple units or components may be combined or integrated. to another system, or some features can be ignored, or not implemented. On the other hand, the shown or discussed mutual coupling or direct coupling or communication connection may be through some interfaces, indirect coupling or communication connection of devices or units, and may be in electrical, mechanical or other forms.

In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically alone, or two or more units may be integrated into one unit. The above-mentioned integrated units may be implemented in the form of hardware, or may be implemented in the form of software functional units. The above are only the embodiments of the present application, and are not intended to limit the scope of the patent of the present application. Any equivalent structure or equivalent process transformation made by using the contents of the description and drawings of the present application, or directly or indirectly applied in other related technical fields, All are similarly included in the scope of patent protection of the present application.

Claims (10)

1. a SQL statement performance optimization method, is characterized in that, comprises: Get the execution plan of the SQL statement entered by the user; Evaluate the performance value of the SQL statement according to the execution plan; When the performance value does not reach the first preset performance threshold, perform a stress test on the SQL statement to determine the point to be optimized of the SQL statement; The preset SQL performance optimization parameter table is queried based on the SQL statement and the point to be optimized, so as to optimize the SQL statement. 2. The SQL statement performance optimization method according to claim 1, wherein the stress test is performed on the SQL statement, and determining the point to be optimized of the SQL statement comprises: Obtain table field DDL related information from the SQL statement to confirm the target table; Generate a preset number of simulation data of the target table through a preset simulation data generator; The SQL statement is tested according to the simulated data, and the to-be-optimized point of the SQL statement is output. 3. The SQL statement performance optimization method according to claim 1, wherein the evaluating the performance value of the SQL statement according to the execution plan comprises: The performance value is obtained by evaluating the SQL statement according to the resource consumption when the SQL statement is executed, the way of accessing the table, and the connection sequence and connection mode of the table. 4. The SQL statement performance optimization method according to claim 1, wherein after evaluating the performance value of the SQL statement according to the execution plan, the method further comprises: When the performance value exceeds the second preset performance threshold, obtain the data structure principle and principle explanation of the SQL statement input by the developer; After the data structure principle and principle explanation are corresponding to the SQL statement, write the SQL performance optimization parameter table. 5. The SQL statement performance optimization method according to claim 1, characterized in that, after the described SQL statement is optimized, further comprising: An optimization record of the SQL statement is recorded, an execution plan of the optimized SQL statement is generated, and stored in a cache. 6. The SQL statement performance optimization method according to claim 1, characterized in that, after the described SQL statement is optimized, further comprising: Perform a stress test again on the optimized SQL statement to detect whether the to-be-optimized point has been optimized; If not, the SQL statement is recorded, and the to-be-optimized point of the SQL statement is reported. 7. The SQL statement performance optimization method according to claim 1, characterized in that, after the described SQL statement is optimized, further comprising: Upload the optimized SQL statement to the blockchain. 8. A device for optimizing SQL statement performance, comprising: The acquisition module is used to acquire the execution plan of the SQL statement input by the user; an evaluation module, configured to evaluate the performance value of the SQL statement according to the execution plan; a test module, configured to perform a stress test on the SQL statement when the performance value does not reach the first preset performance threshold, and determine the point to be optimized of the SQL statement; An optimization module, configured to query a preset SQL performance optimization parameter table based on the SQL statement and the point to be optimized, so as to optimize the SQL statement. 9. A terminal, characterized in that the terminal comprises a processor and a memory coupled to the processor, wherein, The memory stores program instructions for implementing the method for optimizing SQL statement performance according to any one of claims 1-7; The processor is configured to execute the program instructions stored in the memory to optimize SQL statements. 10 . A storage medium, characterized in that a program file capable of implementing the method for optimizing the performance of an SQL statement according to any one of claims 1 to 7 is stored. 11 .

Images