CN112035513A - SQL statement performance optimization method, device, terminal and storage medium - Google Patents
SQL statement performance optimization method, device, terminal and storage medium Download PDFInfo
- Publication number
- CN112035513A CN112035513A CN202010910839.4A CN202010910839A CN112035513A CN 112035513 A CN112035513 A CN 112035513A CN 202010910839 A CN202010910839 A CN 202010910839A CN 112035513 A CN112035513 A CN 112035513A
- Authority
- CN
- China
- Prior art keywords
- sql statement
- performance
- sql
- optimized
- execution plan
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method, a device, a terminal and a storage medium for optimizing SQL statement performance, wherein the method comprises the following steps: acquiring 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 value does not reach a first preset performance threshold value, performing pressure test on the SQL statement to determine a point to be optimized of the SQL statement; and inquiring 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. According to the method and the device, the SQL statement is subjected to pressure test, so that potential defects of the SQL statement are exposed, targeted optimization is performed, and the performance of the SQL statement is improved.
Description
Technical Field
The present application relates to the field of computer technologies, and in particular, to a method, an apparatus, a terminal, and a storage medium for optimizing performance of an SQL statement.
Background
With the advent of the big data era, a database often needs to select data meeting specific requirements from massive data to complete query operation. Structured Query Language (SQL) is a database Query and programming Language for accessing data and querying, updating, and managing relational database systems. In the actual working process, developers often need to write SQL statements with relatively complex logic, and some developers may write SQL statements with poor performance due to unfamiliarity with the underlying principle of SQL, so it is very important to test the performance of the SQL statements.
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 can also view execution plans by means of command lines. However, the above method can only inform developers of the performance of the SQL statements, but cannot determine the specific defects in the SQL statements, and cannot optimize the SQL statements.
Disclosure of Invention
The application provides a method, a device, a terminal and a storage medium for optimizing SQL statement performance, which are used for solving the problem that the existing development tool cannot visually display defects in SQL statements and carry out targeted optimization.
In order to solve the technical problem, the application adopts a technical scheme that: the SQL statement performance optimization method comprises the following steps: acquiring 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 value does not reach a first preset performance threshold value, performing pressure test on the SQL statement to determine a point to be optimized of the SQL statement; and inquiring 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.
As a further improvement of the present application, the pressure test is performed on the SQL statement, and determining the point to be optimized of the SQL statement includes: acquiring table field DDL related information from an SQL statement to confirm a target table; generating simulation data of a preset number of target tables through a preset simulation data generator; and testing the SQL statement according to the simulation data, and outputting 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: and evaluating the SQL statement according to the consumed resources and the table access mode when the SQL statement is executed and the connection sequence and the connection mode of the table to obtain a 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 a second preset performance threshold value, acquiring a data structure principle and a principle explanation of an SQL statement input by a developer; and writing the data structure principle and the principle explanation into the SQL performance optimization parameter table after corresponding to the SQL statement.
As a further improvement of the present application, after the SQL statement is optimized, the method further includes: and recording the optimized record of the SQL statement, generating an execution plan of the optimized SQL statement, and storing the execution plan to a cache.
As a further improvement of the present application, after the SQL statement is optimized, the method further includes: carrying out pressure test on the optimized SQL statement again to detect whether the point to be optimized is optimized; if not, recording the SQL statement, and reporting the point to be optimized of the SQL statement.
As a further improvement of the present application, after the SQL statement is optimized, the method further includes: and uploading the optimized SQL statement to a block chain.
In order to solve the above technical problem, another technical solution adopted by the present application is: the SQL statement performance optimization device comprises: the acquisition module is used for acquiring an execution plan of the SQL statement input by the user; the evaluation module is used for evaluating the performance value of the SQL statement according to the execution plan; the test module is used for carrying out pressure test on the SQL statement and determining a point to be optimized of the SQL statement when the performance value does not reach a first preset performance threshold value; and the optimization module is used for inquiring 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.
In order to solve the above technical problem, the present application adopts another technical solution that: providing a terminal, wherein the terminal comprises a processor and a memory coupled with the processor, and the memory stores program instructions for implementing the SQL statement performance optimization method; the processor is configured to execute the program instructions stored in the memory to optimize the SQL statements.
In order to solve the above technical problem, the present application adopts another technical solution that: a storage medium is provided, which stores a program file capable of realizing the SQL sentence performance optimization method.
The beneficial effect of this application is: according to the SQL statement performance optimization method, whether the SQL statement has defects is preliminarily judged by utilizing the execution plan of the SQL statement, when the SQL statement is confirmed to have the defects through the execution plan, the SQL statement is subjected to pressure test, so that the defects of the SQL statement are exposed, the SQL statement is optimized according to the preset SQL performance optimization parameter table, developers are effectively helped to find hidden problems in the SQL in time, an optimization scheme is directly provided for the developers, and the optimization efficiency of the SQL statement is improved.
Drawings
FIG. 1 is a flowchart illustrating a method for optimizing the performance of an SQL statement according to a first embodiment of the present invention;
FIG. 2 is a flowchart illustrating a method for optimizing the performance of an SQL statement according to a second embodiment of the present invention;
FIG. 3 is a flowchart illustrating a method for optimizing the performance of an SQL statement according to a third embodiment of the present invention;
FIG. 4 is a functional module diagram of an SQL statement performance optimization device according to an embodiment of the present invention;
fig. 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 Description
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, and it is obvious that the described embodiments are only a part of the embodiments of the present application, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present application.
The terms "first", "second" and "third" in this application are used for descriptive purposes only and are not to be construed as indicating or implying relative importance or implying any indication of the number of technical features indicated. Thus, a feature defined as "first," "second," or "third" may explicitly or implicitly include at least one of the feature. In the description of the present application, "plurality" means at least two, e.g., two, three, etc., unless explicitly specifically limited otherwise. All directional indications (such as up, down, left, right, front, and rear … …) in the embodiments of the present application are only used to explain the relative positional relationship between the components, the movement, and the like in a specific posture (as shown in the drawings), and if the specific posture is changed, the directional indication is changed accordingly. Furthermore, the terms "include" and "have," as well as any variations thereof, are intended to cover non-exclusive inclusions. For example, a process, method, system, article, or apparatus that comprises a list of steps or elements is not limited to only those steps or elements listed, but may alternatively include other steps or elements not listed, or inherent to such process, method, article, or apparatus.
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 application. The appearances of the phrase in various places in the specification are not necessarily all referring to the same embodiment, nor are separate or alternative embodiments mutually exclusive of other embodiments. It is explicitly and implicitly understood by one skilled in the art that the embodiments described herein can be combined with other embodiments.
Fig. 1 is a schematic flow chart of an SQL statement performance optimization method according to a first embodiment of the present invention. It should be noted that the method of the present invention is not limited to the flow sequence shown in fig. 1 if the results are substantially the same. As shown in fig. 1, the method comprises the steps of:
step S101: and acquiring an execution plan of the SQL statement input by the user.
It should be noted that the performance of the SQL statement affects the performance of the entire database to a certain extent, in many cases, the performance of the database is basically low due to a bad SQL statement, and the execution plan of the SQL statement determines which way the SQL statement will adopt to extract data from the database and return the data to the client.
In step S101, the execution plan of the SQL statement input by the user is obtained by executing the computer instruction information, for example, the SQL statement may be manually input by the user first, and then the SQL statement input by the user is pre-executed by triggering the computer instruction information, so as to obtain the execution plan of the SQL statement. In this embodiment, six ways of obtaining an SQL statement execution plan are introduced:
1. generating an execution plan by using an explain for command and displaying the execution plan;
2. generating an execution plan by using a dbms storage process;
3. acquiring an execution plan by using an autotrace tool;
4. extracting an execution plan directly from shared _ pool;
5. acquiring an execution plan from an AWR report library;
6. and (3) generating a tracking file by using 10046 event tracking and starting the event tracking, and finally acquiring an execution plan by analyzing the tracking file.
The embodiment preferably adopts an explain plan for command to obtain the execution plan of the SQL statement.
Step S102: and evaluating the performance value of the SQL statement according to the execution plan.
In step S102, the performance of the SQL statement may be determined by the resources consumed when the SQL statement is executed, the way in which the SQL statement accesses the table, the connection order of the table, and the connection way, and therefore, evaluating the performance value of the SQL statement according to the execution plan specifically includes: and evaluating the SQL statement according to the consumed resources and the table access mode when the SQL statement is executed and the connection sequence and the connection mode of the table to obtain a performance value.
For example: the access modes of the table are mainly two types: a TABLE ACCESS FULL (TABLE ACCESS FULL) and an INDEX SCAN (INDEX SCAN), wherein if an INDEX with good selectivity exists on a TABLE, the adopted TABLE ACCESS mode is indicated to have defects; if the large table has no proper index and the scanning of the whole table is carried out, whether the index can be established or not or whether a more proper table connection mode and connection sequence can be selected or not needs to be analyzed so as to improve the efficiency. The connection order of tables refers to the access order in which a certain table is used as a drive table to connect other tables. The table connection mode refers to a connection process when two tables obtain data satisfying conditions, and at present, there are mainly three table connection modes, namely, NESTED loop (NESTED loop), HASH JOIN (HASH JOIN), and SORT MERGE JOIN (SORT MERGE JOIN). Nested loops and hash connections are commonly used. The nested loop is to process two cursors by two layers of loops, the outer layer of cursors is called a drive table, Oracle retrieves data of the drive table, one curser is substituted into the inner layer of cursors to search all data meeting WHERE conditions, so that the better the selectivity of available indexes in the inner layer of cursors table is, the higher the performance of nested loop connection is. Hash join refers to putting the data of the drive table into the memory in a hash mode according to the condition field, and then matching the rows meeting the condition in the memory. The hash join needs a proper memory and can be used only when the WHERE condition for joining two tables has an equal number in the CBO optimization mode. Hash join is more efficient than nested loops when the amount of data in the table is large and no suitable index is available in the table. The sorting and merging connection is to sort the associated columns of the associated table, extract data from the respective sorted table, and match the data to another sorted table, which results in more consumed resources because more sorting is required.
Step S103: and when the performance value does not reach the first preset performance threshold value, performing pressure test on the SQL statement to determine a point to be optimized of the SQL statement.
It should be noted that the points to be optimized in the SQL statement include too many resources occupied by executing the SQL statement, an inappropriate way for the SQL statement to access the table, an inappropriate way for the table to connect, and the like. The first preset performance threshold is preset, and when the performance value of the SQL statement is lower than the first preset performance threshold, the SQL statement has a defect to be optimized.
In step S103, the defect of the partial SQL statement cannot be perceived by the developer when the data amount is small, for example: when querying data of a table, if the data amount of the table is small, the time difference between the consumed time of the full-table scanning mode and the consumed time of the index scanning mode is very small and cannot be perceived by developers, and at this time, the developers may misunderstand that the effects of the two modes are the same, but when the data amount of the table is large, the time difference is enlarged, and the consumed time of the full-table scanning mode is obviously longer than that of the index scanning mode. Therefore, in the embodiment, when the performance of the SQL statement is judged to be poor through the execution plan, the SQL statement is subjected to a stress test to expose the defects, so that the point to be optimized in the SQL statement is confirmed.
Further, in some embodiments, the performing a stress test on the SQL statement, and determining the point to be optimized of the SQL statement specifically includes:
1. and acquiring the relevant information of the table field DDL from the SQL statement to confirm the target table.
2. And generating simulation data of a preset number of target tables through a preset simulation data generator.
Specifically, the simulation Data generator is composed of a lazy mock or a Data-processor, and after relevant information of a table field DDL is acquired from an SQL statement, actual Data in a target table is simulated by the simulation Data generator according to the relevant information of the table field DDL, so that simulation Data of a preset quantity is acquired. It should be noted that the preset number is preset and fully satisfies the data amount required by the SQL statement for the stress test.
3. And testing the SQL statement according to the simulation data, and outputting the point to be optimized of the SQL statement.
Specifically, the SQL statement is tested by using the simulation data, and the test result is recorded and analyzed, so that the defect in the SQL statement is confirmed and is output as the point to be optimized of the SQL statement.
Step S104: and inquiring 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, and it integrates all SQL statement optimization schemes known to those skilled in the art. For example, the related optimization rules in indexing, the underlying data structure principles, etc. can be written into the SQL performance optimization parameter table.
In step S104, after the point to be optimized of the SQL statement is confirmed, a suitable optimization scheme is queried 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: carrying out pressure test on the optimized SQL statement again to detect whether the point to be optimized is optimized; if not, recording the SQL statement, and reporting the point to be optimized of the SQL statement.
Specifically, when the pressure test is performed, it is not necessary to simulate again to generate simulation data, and the test may be performed by directly using the previously generated simulation data. In this embodiment, the second pressure test is performed to verify whether the optimization of the SQL statement is reasonable, and if the optimization is not reasonable, it indicates that the optimization suggestion of the SQL performance optimization parameter table cannot solve the defect of the SQL statement, at this time, the defect is reported to the developer, the developer actively solves the defect, and the solution and the principle description of the defect can be written into the SQL performance optimization parameter table, so as to enrich the content in the SQL performance optimization parameter table.
According to the SQL statement performance optimization method, whether the SQL statement has defects is preliminarily judged by using the execution plan of the SQL statement, when the SQL statement is confirmed to have the defects through the execution plan, the SQL statement is subjected to pressure test, so that the defects of the SQL statement are exposed, the SQL statement is optimized according to the preset SQL performance optimization parameter table, developers are effectively helped to find hidden problems in SQL in time, an optimization scheme is directly provided for the developers, and the optimization efficiency of the SQL statement is improved.
Further, in some embodiments, after the optimizing the SQL statement, the method further includes: and uploading the optimized SQL statement to a block chain.
Specifically, the corresponding digest information is obtained based on the optimized SQL statement, and specifically, the digest information is obtained by performing hash processing on the optimized SQL statement, for example, by using the sha256s algorithm. Uploading summary information to the blockchain can ensure the safety and the fair transparency of the user. The user equipment can download the summary information from the blockchain so as to verify whether the optimized SQL statement is tampered. The blockchain referred to in this example is a novel application mode of computer technologies such as distributed data storage, point-to-point transmission, consensus mechanism, encryption algorithm, and the like. A block chain (Blockchain), which is essentially a decentralized database, is a series of data blocks associated by using a cryptographic method, and each data block contains information of a batch of network transactions, so as to verify the validity (anti-counterfeiting) of the information and generate a next block. The blockchain may include a blockchain underlying platform, a platform product service layer, an application service layer, and the like.
Further, in some embodiments, after the optimizing the SQL statement, the method further includes: and acquiring a data structure principle and a related explanation adopted when the SQL statement is optimized from the SQL performance optimization parameter table, and outputting the optimization result of the SQL statement, the adopted data structure principle and the related explanation together.
It should be noted that, the data structure principle and the related explanation are both input into the SQL performance optimization parameter table in advance when the SQL performance optimization parameter table is constructed.
In the embodiment, the optimization result of the SQL statement, the data structure principle adopted when the SQL statement is optimized in the SQL performance optimization parameter table, and the related explanation are output together, so that the development staff can understand and learn, and the SQL knowledge storage of the development staff is improved.
Fig. 2 is a schematic flow chart of an SQL statement performance optimization method according to a second embodiment of the present invention. It should be noted that the method of the present invention is not limited to the flow sequence shown in fig. 2 if the results are substantially the same. As shown in fig. 2, the method comprises the steps of:
step S201: and acquiring an 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 brevity, is not described herein again.
Step S202: and evaluating the performance value of the SQL statement according to the execution plan. And executing the step S203 to the step S204 when the performance value does not reach the first preset performance threshold value. And when the performance value exceeds a second preset performance threshold value, executing the step S205 to the step S206.
In this embodiment, step S202 in fig. 2 is similar to step S102 in fig. 1, and for brevity, is not described herein again.
Step S203: and carrying out pressure test 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 brevity, is not described herein again.
Step S204: and inquiring 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.
In this embodiment, step S204 in fig. 2 is similar to step S104 in fig. 1, and for brevity, is not described herein again.
Step S205: and acquiring 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 is 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 is stated that the performance of the SQL statement is good, and a sample thereof may be used as a reference, so that a developer needs to input the data structure principle and principle explanation of the SQL statement, as a reference and suggestion for subsequently optimizing the SQL statement, and when other developers see the data structure principle and principle explanation, other developers can also help other developers understand the SQL statement.
Step S206: and writing the data structure principle and the principle explanation into the SQL performance optimization parameter table after corresponding to the SQL statement.
In step S206, the SQL statement, the corresponding data structure principle and the principle explanation are all written into the SQL performance optimization parameter table, so as to enrich the examples in the SQL performance optimization parameter table. It should be noted that, when the SQL performance optimization parameter table has the sample of the SQL statement, it does not need to be written again.
On the basis of the first embodiment, when the performance of the SQL statement is higher than the second preset performance threshold, the performance of the SQL statement can be considered to be excellent, and therefore, when the performance of the SQL statement exceeds the second preset performance threshold, a developer is prompted to input a data structure principle and a principle explanation of the SQL statement, and the SQL statement is written into the SQL performance optimization parameter table as a sample and used as a suggestion for subsequently optimizing similar SQL statements for reference and learning of other developers.
Fig. 3 is a flowchart illustrating a method for optimizing the performance of an SQL statement according to a third embodiment of the present invention. It should be noted that the method of the present invention is not limited to the flow sequence shown in fig. 3 if the results are substantially the same. As shown in fig. 3, the method comprises the steps of:
step S301: and acquiring an 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 brevity, is not described herein again.
Step S302: and evaluating 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 brevity, is not described herein again.
Step S303: and when the performance value does not reach the first preset performance threshold value, performing pressure test on the SQL statement to determine a 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 brevity, is not described herein again.
Step S304: and inquiring 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.
In this embodiment, step S304 in fig. 3 is similar to step S104 in fig. 1, and for brevity, is not described herein again.
Step S305: and recording the optimized record of the SQL statement, generating an execution plan of the optimized SQL statement, and storing the execution plan to a cache. It should be noted that the cache is preset and is used for storing an execution plan of the SQL statement. The optimization record may record whether the SQL statement was optimized.
In step S305, after the SQL statement is optimized, the optimized record of the SQL statement is recorded, and the execution plan of the optimized SQL statement needs to be regenerated, at this time, the optimized record of the SQL statement may be queried to determine whether the SQL statement is optimized for the first time, if the SQL statement is optimized for the first time, the execution plan of the optimized SQL statement is generated and stored in the cache, and if the SQL statement is not optimized for the first time, the corresponding execution plan may be directly queried from the cache without being regenerated.
Further, the optimized record can also be used for recording the optimized result of the SQL statement, after the developer inputs the SQL statement, whether the optimized record of the SQL statement exists is queried, if the optimized record of the SQL statement exists, the SQL statement is directly optimized according to the optimized record, then the execution plan corresponding to the SQL statement is queried from the cache, and the SQL statement is executed according to the execution plan, so that the SQL statement does not need to be optimized again, and the repeated optimization process is prevented from occupying too many resources. According to the SQL statement performance optimization method provided by the third embodiment of the invention, on the basis of the first embodiment, by storing the optimization records of the SQL statement and the execution plan of the optimized SQL statement, when the same SQL statement is executed subsequently, the SQL statement can be optimized directly according to the optimization records, the execution plan is notified to be directly called, the optimized SQL statement does not need to be analyzed, the query efficiency of the SQL statement is improved, and meanwhile, the repeated optimization process is prevented from occupying too many resources.
Fig. 4 is a functional module diagram of the SQL statement performance optimizing apparatus according to the 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.
An obtaining module 41, configured to obtain an execution plan of an SQL statement input by a user;
an evaluation module 42, configured to evaluate a performance value of the SQL statement according to the execution plan;
the test module 43 is configured to perform a pressure test on the SQL statement when the performance value does not reach the first preset performance threshold, and determine a point to be optimized of the SQL statement;
and the optimization module 44 is configured to query a preset SQL performance optimization parameter table based on the SQL statements and the points to be optimized, so as to optimize the SQL statements.
Optionally, the test module 43 performs a pressure test on the SQL statement, and the operation of determining the point to be optimized of the SQL statement may further be: acquiring table field DDL related information from an SQL statement to confirm a target table; generating simulation data of a preset number of target tables through a preset simulation data generator; and testing the SQL statement according to the simulation data, and outputting the point to be optimized of the SQL statement.
Optionally, the operation of the evaluation module 42 evaluating the performance value of the SQL statement according to the execution plan may also evaluate the SQL statement according to the resource consumption, the table accessing manner, and the table connection order and connection manner during execution of the SQL statement, so as to obtain the performance value.
Optionally, after the evaluation module 42 evaluates the operation of the performance value of the SQL statement according to the execution plan, when the performance value exceeds a second preset performance threshold, the evaluation module is further configured to obtain a data structure principle and a principle explanation of the SQL statement input by the developer; and writing the data structure principle and the principle explanation into the SQL performance optimization parameter table after corresponding to the SQL statement.
Optionally, after the optimization module 44 performs the optimization operation on the SQL statement, the optimization module is further configured to record the optimization record of the SQL statement, generate an execution plan of the optimized SQL statement, and store the execution plan in the cache.
Optionally, after the optimization module 44 performs optimization operation on the SQL statement, the optimization module is further configured to perform a pressure test on the optimized SQL statement again to detect whether the point to be optimized is optimized; if not, recording the SQL statement, and reporting the point to be optimized of the SQL statement.
Optionally, after the optimization operation is performed on the SQL statement, the optimization module 44 is further configured to upload the optimized SQL statement to the block chain.
Referring to fig. 5, fig. 5 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 according to any of the embodiments.
The processor 61 is configured to execute program instructions stored in the memory 62 to optimize the SQL statements.
The processor 61 may also be referred to as a CPU (Central Processing Unit). The processor 61 may be an integrated circuit chip having signal processing capabilities. The processor 61 may also be a general purpose processor, a Digital Signal Processor (DSP), an Application Specific Integrated Circuit (ASIC), a Field Programmable Gate Array (FPGA) or other programmable logic device, discrete gate or transistor logic, 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 invention. The storage medium of the embodiment of the present invention stores a program file 71 capable of implementing all the methods described above, wherein the program file 71 may be stored in the storage medium in the form of a software product, and includes several instructions to enable a computer device (which may be a personal computer, a server, or a network device) or a processor (processor) to execute all or part of the steps of the methods described in the embodiments of the present application. And the aforementioned storage medium includes: various media capable of storing program codes, such as a usb disk, a mobile hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk or an optical disk, or terminal devices, such as a computer, a server, a mobile phone, and a tablet.
In the several embodiments provided in the present application, it should be understood that the disclosed system, apparatus and method may be implemented in other manners. For example, the above-described apparatus embodiments are merely illustrative, and for example, a division of a unit is merely a logical division, and an actual implementation may have another division, for example, a plurality of units or components may be combined or integrated into another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual coupling or direct coupling or communication connection may be an indirect coupling or communication connection through some interfaces, devices or units, and may be in an electrical, mechanical or other form.
In addition, functional units in the embodiments of the present invention may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit. The integrated unit can be realized in a form of hardware, and can also be realized in a form of a software functional unit. The above embodiments are merely examples and are not intended to limit the scope of the present disclosure, and all modifications, equivalents, and flow charts using the contents of the specification and drawings of the present disclosure or those directly or indirectly applied to other related technical fields are intended to be included in the scope of the present disclosure.
Claims (10)
1. A method for optimizing performance of SQL statements is characterized by comprising the following steps:
acquiring 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 value does not reach a first preset performance threshold value, performing pressure test on the SQL statement to determine a point to be optimized of the SQL statement;
and inquiring 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.
2. The method for optimizing the performance of the SQL statement according to claim 1, wherein the step of performing a stress test on the SQL statement and determining a point to be optimized of the SQL statement comprises:
acquiring table field DDL related information from the SQL statement to confirm a target table;
generating a preset number of simulation data of the target table through a preset simulation data generator;
and testing the SQL statement according to the simulation data, and outputting the point to be optimized of the SQL statement.
3. The SQL statement performance optimization method of claim 1, wherein the evaluating the performance value of the SQL statement according to the execution plan comprises:
and evaluating the SQL statement according to the consumed resources, the table access mode, the table connection sequence and the table connection mode when the SQL statement is executed to obtain the performance value.
4. The SQL statement performance optimization method according to claim 1, further comprising, after evaluating the performance value of the SQL statement according to the execution plan:
when the performance value exceeds a second preset performance threshold value, acquiring a data structure principle and a principle explanation of the SQL statement input by a developer;
and writing the data structure principle and principle explanation into the SQL performance optimization parameter table after corresponding to the SQL statement.
5. The SQL statement performance optimization method according to claim 1, further comprising, after the optimizing the SQL statement:
and recording the optimized record of the SQL statement, generating an execution plan of the optimized SQL statement, and storing the execution plan to a cache.
6. The SQL statement performance optimization method according to claim 1, further comprising, after the optimizing the SQL statement:
performing pressure test on the optimized SQL statement again to detect whether the point to be optimized is optimized;
if not, recording the SQL statement, and reporting the SQL statement to the point to be optimized.
7. The SQL statement performance optimization method according to claim 1, further comprising, after the optimizing the SQL statement:
and uploading the optimized SQL statement to a block chain.
8. An apparatus for optimizing performance of SQL statements, comprising:
the acquisition module is used for acquiring an execution plan of the SQL statement input by the user;
the evaluation module is used for evaluating the performance value of the SQL statement according to the execution plan;
the test module is used for carrying out pressure test on the SQL statement and determining a point to be optimized of the SQL statement when the performance value does not reach a first preset performance threshold value;
and the optimization module is used for inquiring 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, comprising a processor, a memory coupled to the processor, wherein,
the memory stores program instructions for implementing the SQL statement performance optimization method according to any of claims 1-7;
the processor is configured to execute the program instructions stored by the memory to optimize SQL statements.
10. A storage medium storing a program file capable of implementing the SQL statement performance optimization method according to any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010910839.4A CN112035513A (en) | 2020-09-02 | 2020-09-02 | SQL statement performance optimization method, device, terminal and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010910839.4A CN112035513A (en) | 2020-09-02 | 2020-09-02 | SQL statement performance optimization method, device, terminal and storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112035513A true CN112035513A (en) | 2020-12-04 |
Family
ID=73591219
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010910839.4A Pending CN112035513A (en) | 2020-09-02 | 2020-09-02 | SQL statement performance optimization method, device, terminal and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112035513A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115544064A (en) * | 2022-11-24 | 2022-12-30 | 中国电子信息产业集团有限公司 | Data optimization method and device based on custom optimization rule |
| CN117290377A (en) * | 2023-09-28 | 2023-12-26 | 北京领雁科技股份有限公司 | Method and device for converting SQL sentences among relational databases |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20090077016A1 (en) * | 2007-09-14 | 2009-03-19 | Oracle International Corporation | Fully automated sql tuning |
| CN105224458A (en) * | 2015-10-09 | 2016-01-06 | 上海瀚银信息技术有限公司 | A kind of database method of testing and system |
| CN105989137A (en) * | 2015-02-27 | 2016-10-05 | 中国移动通信集团河北有限公司 | Structured query language performance optimization method and system |
| CN108197306A (en) * | 2018-01-30 | 2018-06-22 | 平安科技（深圳）有限公司 | SQL statement processing method, device, computer equipment and storage medium |
| CN108519949A (en) * | 2018-04-12 | 2018-09-11 | 郑州云海信息技术有限公司 | A pressure testing method, system and device for an ORACLE database |
| CN110287114A (en) * | 2019-06-26 | 2019-09-27 | 深圳前海微众银行股份有限公司 | Method and device for database script performance testing |
-
2020
- 2020-09-02 CN CN202010910839.4A patent/CN112035513A/en active Pending
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20090077016A1 (en) * | 2007-09-14 | 2009-03-19 | Oracle International Corporation | Fully automated sql tuning |
| CN105989137A (en) * | 2015-02-27 | 2016-10-05 | 中国移动通信集团河北有限公司 | Structured query language performance optimization method and system |
| CN105224458A (en) * | 2015-10-09 | 2016-01-06 | 上海瀚银信息技术有限公司 | A kind of database method of testing and system |
| CN108197306A (en) * | 2018-01-30 | 2018-06-22 | 平安科技（深圳）有限公司 | SQL statement processing method, device, computer equipment and storage medium |
| CN108519949A (en) * | 2018-04-12 | 2018-09-11 | 郑州云海信息技术有限公司 | A pressure testing method, system and device for an ORACLE database |
| CN110287114A (en) * | 2019-06-26 | 2019-09-27 | 深圳前海微众银行股份有限公司 | Method and device for database script performance testing |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115544064A (en) * | 2022-11-24 | 2022-12-30 | 中国电子信息产业集团有限公司 | Data optimization method and device based on custom optimization rule |
| CN117290377A (en) * | 2023-09-28 | 2023-12-26 | 北京领雁科技股份有限公司 | Method and device for converting SQL sentences among relational databases |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11163744B2 (en) | Test data generation and scale up for database testing using unique common factor sequencing | |
| US8799859B2 (en) | Augmented design structure matrix visualizations for software system analysis | |
| US20090254332A1 (en) | Efficient presentation of functional coverage results | |
| US9195730B2 (en) | Verifying correctness of a database system via extended access paths | |
| CN110990274A (en) | Data processing method, device and system for generating test case | |
| US10175958B2 (en) | Acquiring identification of an application lifecycle management entity associated with similar code | |
| CN112035513A (en) | SQL statement performance optimization method, device, terminal and storage medium | |
| EP3786810A1 (en) | Automatic generation of computing artifacts for data analysis | |
| US8781997B2 (en) | Reusing reporting components in customized cubes | |
| CN113900956A (en) | Test case generation method, device, computer equipment and storage medium | |
| US20180196738A1 (en) | Test input information search device and method | |
| CN116483476B (en) | Data source configuration method and device, electronic equipment and storage medium | |
| JP5310196B2 (en) | Classification system revision support program, classification system revision support device, and classification system revision support method | |
| CN112559331A (en) | Test method and device | |
| CN112380127A (en) | Test case regression method, device, equipment and storage medium | |
| Lin et al. | OREO: detection of cherry-picked generalizations | |
| CN118296001B (en) | Data query method, device, computer equipment, storage medium and program product | |
| CN110750569A (en) | Data extraction method, device, equipment and storage medium | |
| CN114020738B (en) | Method and system for quickly generating OPC test layout by result database and storage medium | |
| CN112380798B (en) | Parameter checking method, device, equipment and storage medium | |
| Payne et al. | University of kentucky tracelab component similarity matrix voting merge | |
| CN117453703A (en) | Test method, apparatus, device, medium and program product | |
| CN118969050A (en) | Automatic testing method and device for fault coverage of March algorithm | |
| WO2023187869A1 (en) | Test assistance device, test assistance method, and test assistance program | |
| CN119759729A (en) | Automatic detection method, system, device and storage medium for data development code |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |