CN117762937A - SQL sentence optimization method, SQL sentence optimization device, SQL sentence optimization storage medium and SQL sentence optimization computer device - Google Patents
SQL sentence optimization method, SQL sentence optimization device, SQL sentence optimization storage medium and SQL sentence optimization computer device Download PDFInfo
- Publication number
- CN117762937A CN117762937A CN202311790097.6A CN202311790097A CN117762937A CN 117762937 A CN117762937 A CN 117762937A CN 202311790097 A CN202311790097 A CN 202311790097A CN 117762937 A CN117762937 A CN 117762937A
- Authority
- CN
- China
- Prior art keywords
- sql statement
- optimization
- execution
- data table
- sql
- 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
- 238000005457 optimization Methods 0.000 title claims abstract description 87
- 238000000034 method Methods 0.000 title claims abstract description 47
- 230000002159 abnormal effect Effects 0.000 claims abstract description 65
- 239000012634 fragment Substances 0.000 claims abstract description 27
- 238000005070 sampling Methods 0.000 claims abstract description 12
- 238000012360 testing method Methods 0.000 claims description 14
- 238000012795 verification Methods 0.000 claims description 10
- 230000005856 abnormality Effects 0.000 claims description 7
- 238000011161 development Methods 0.000 abstract description 6
- 238000004590 computer program Methods 0.000 description 7
- 238000010586 diagram Methods 0.000 description 5
- 238000004891 communication Methods 0.000 description 4
- 230000008569 process Effects 0.000 description 4
- 230000009471 action Effects 0.000 description 3
- 238000012545 processing Methods 0.000 description 3
- 230000008859 change Effects 0.000 description 2
- 238000001514 detection method Methods 0.000 description 2
- 230000000694 effects Effects 0.000 description 2
- 238000005516 engineering process Methods 0.000 description 2
- 230000003068 static effect Effects 0.000 description 2
- OKTJSMMVPCPJKN-UHFFFAOYSA-N Carbon Chemical compound [C] OKTJSMMVPCPJKN-UHFFFAOYSA-N 0.000 description 1
- 230000001413 cellular effect Effects 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 229910021389 graphene Inorganic materials 0.000 description 1
- 238000011835 investigation Methods 0.000 description 1
- 239000004973 liquid crystal related substance Substances 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 230000000750 progressive effect Effects 0.000 description 1
- 238000012216 screening Methods 0.000 description 1
Abstract
The application provides an SQL sentence optimization method, an SQL sentence optimization device, a storage medium and computer equipment. The method comprises the following steps: acquiring an execution plan analysis result of the SQL sentence; determining a target data table based on the execution plan analysis result; performing data sampling on the target data table to generate a temporary data table; executing SQL sentences on the temporary data table, and counting corresponding slicing execution time according to a preset slicing stage; determining a target rule base based on a database corresponding to the SQL statement; performing rule matching on the analysis result of the execution plan based on the target rule base, and identifying abnormal information; and generating an optimization suggestion according to the identified abnormal information and the fragment execution time of the SQL sentence. The method and the device can provide more comprehensive optimization suggestions, help optimize SQL sentence performance and improve development efficiency.
Description
Technical Field
The present disclosure relates to the field of computer technologies, and in particular, to an SQL statement optimization method, device, storage medium, and computer apparatus.
Background
In a database environment, SQL (Structured Query Language ) statements are often used. Moreover, as database applications increase, so does the amount of data processed, so does the processing logic. The developer is unavoidably wrong in the development process or the written SQL sentence has poor performance, the data query efficiency is affected, and the manual investigation efficiency is lower when the subsequent abnormality occurs.
Disclosure of Invention
The embodiment of the application provides an SQL sentence optimization method, an SQL sentence optimization device, a storage medium and computer equipment, which can provide more comprehensive optimization suggestions, help optimize SQL sentence performance and improve development efficiency.
In a first aspect, the present application provides an SQL statement optimization method, the method including:
acquiring an execution plan analysis result of the SQL sentence;
determining a target data table based on the execution plan analysis result;
performing data sampling on the target data table to generate a temporary data table;
executing the SQL statement on the temporary data table, and counting corresponding slicing execution time according to a preset slicing stage;
determining a target rule base based on a database corresponding to the SQL statement;
performing rule matching on the analysis result of the execution plan based on the target rule base, and identifying abnormal information;
and generating optimization suggestions according to the identified abnormal information and the fragment execution time of the SQL statement.
In one embodiment, the method further comprises:
acquiring an execution result of executing the SQL statement on the temporary data table;
acquiring index hit data based on the execution result;
verifying index optimization suggestions in the execution plan analysis result according to the index hit data;
updating the index optimization suggestion based on the verification result.
In one embodiment, when the target rule base includes a plurality of rule bases, executing the rule matching on the execution plan analysis result based on the target rule base, and identifying the abnormal information includes:
acquiring the priority of each rule base;
and carrying out rule matching on the analysis results of the execution plan in sequence according to the order of the priority from high to low, and identifying the abnormal information.
In one embodiment, the rule matching is performed on the analysis results of the execution plan sequentially from high to low according to the priority, and identifying the abnormal information includes:
identifying abnormal results based on traversing each rule base in the order of priority from high to low;
identifying whether conflict exists for abnormal results corresponding to rule bases with different priorities;
if the conflict exists, comparing the priority of the rule base corresponding to the abnormal result of the conflict;
the abnormal results corresponding to the rule base with higher priority are reserved, and the abnormal results corresponding to the rule base with lower priority are deleted;
and summarizing the rest abnormal results to generate the abnormal information.
In one embodiment, the method further comprises:
acquiring a preset time threshold value of each slicing stage according to the SQL statement;
identifying whether the execution time of each fragment of the SQL statement exceeds a time threshold of a corresponding fragment stage;
and if any fragment execution time exceeds the time threshold of the corresponding fragment stage, generating an alarm prompt according to the SQL sentence and the fragment stage of which the fragment execution time exceeds the time threshold.
In one embodiment, before executing the execution plan analysis result of the SQL statement, the method further includes:
carrying out grammar analysis on the SQL sentence, and identifying whether grammar errors exist;
if so, grammar optimization suggestions are generated.
In a second aspect, the present application provides an SQL statement optimization apparatus, including:
the first acquisition module is used for acquiring an execution plan analysis result of the SQL sentence;
a data table determining module, configured to determine a target data table based on the execution plan analysis result;
the data table generating module is used for carrying out data sampling on the target data table to generate a temporary data table;
the execution test module is used for executing the SQL statement on the temporary data table and counting corresponding slicing execution time according to a preset slicing stage;
the rule base determining module is used for determining a target rule base based on the database corresponding to the SQL statement;
the rule matching module is used for carrying out rule matching on the analysis result of the execution plan based on the target rule base and identifying abnormal information;
and the first suggestion generation module is used for generating an optimization suggestion according to the identified abnormal information and the slicing execution time of the SQL sentence.
In one embodiment, the apparatus further comprises:
the second acquisition module is used for acquiring an execution result of executing the SQL sentence on the temporary data table;
a third obtaining module, configured to obtain index hit data based on the execution result;
the verification module is used for verifying the index optimization suggestion in the execution plan analysis result according to the index hit data;
and the suggestion updating module is used for updating the index optimization suggestion based on the verification result.
In a third aspect, the present application provides a storage medium having stored therein computer readable instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of the SQL statement optimization method as described in any of the above.
In a fourth aspect, the present application provides a computer device comprising: one or more processors, and memory;
the memory has stored therein computer readable instructions which, when executed by the one or more processors, perform the steps of the SQL statement optimization method as described in any of the above.
From the above technical solutions, the embodiments of the present application have the following advantages:
according to the SQL sentence optimization method, device, storage medium and computer equipment, the analysis result of the execution plan of the SQL sentence is analyzed, the pointed target data table is determined, the temporary data table is generated by sampling based on the target data table, the temporary data table is utilized for executing the test and counting the slicing execution time of the slicing stage, the target rule base is determined according to the database applied by the SQL sentence, rule matching is carried out on the analysis result of the execution plan, abnormal information is identified, the optimization suggestion is generated based on the abnormal information and the slicing execution time, the sampling data is utilized for executing the test, the slicing execution time is rapidly tested, the optimization suggestion is jointly generated by combining the actual test execution time with the abnormality identified by matching the analysis result of the execution plan by utilizing the target rule base, the optimization suggestion is jointly carried out from the two aspects of plan analysis and the test result, the developer is helped to quickly locate the problem of the SQL sentence, and the performance is optimized.
Drawings
In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described below, it being obvious that the drawings in the following description are only some embodiments of the present application, and that other drawings may be obtained according to these drawings without inventive faculty for a person skilled in the art.
FIG. 1 is a flow diagram of an SQL statement optimization method, in one embodiment;
FIG. 2 is a flow diagram of an SQL statement optimization method according to one embodiment;
FIG. 3 is a block diagram of a SQL statement optimizing device, in one embodiment;
FIG. 4 is an internal block diagram of a computer device, in one embodiment;
fig. 5 is an internal structural view of a computer device in another embodiment.
Detailed Description
The following description of the embodiments of the present application will be made clearly and fully with reference to the accompanying drawings, in which it is evident that the embodiments described are only some, but not all, of the embodiments of the present application. All other embodiments, which can be made by one of ordinary skill in the art without undue burden from the present disclosure, are within the scope of the present disclosure.
As shown in fig. 1, in one embodiment, the present application provides an SQL statement optimization method, which includes:
step S101, obtaining an execution plan analysis result of the SQL sentence.
The execution plan analysis result (explatin) is a plan to be executed, which is obtained by carrying out query analysis on the SQL statement to be optimized, and can be obtained from the execution plan analysis result, which steps are undergone by each step of the SQL statement, whether indexes are used, which fields are used, what indexes are used, whether there are some optimizable places, and the like, and the execution plan analysis result can be generated by the existing SQL query optimizer. The embodiment of the application generates more comprehensive optimization suggestions by further analysis based on the analysis result of the execution plan.
Step S102, determining a target data table based on the analysis result of the execution plan.
The target data table is a data source of the SQL statement and can be determined from the analysis result of the execution plan.
Step S103, data sampling is carried out on the target data table to generate a temporary data table.
Because the target data table may contain huge data, if the target data table is directly executed with the SQL statement, more resources are required to be consumed, and time is possibly long, the optimization proposal is obtained for the pre-estimated analysis statement in the existing optimization mode. In this embodiment, by sampling data from the target data table, a temporary data table is created for performing a test by acquiring a small portion of data.
Step S104, executing SQL sentences on the temporary data table, and counting corresponding slicing execution time according to a preset slicing stage.
The preset sharding stage is a stage which is configured in advance and can be included in executing various SQL sentences. And when executing the SQL statement on the temporary data table, counting the execution time of each corresponding slicing stage respectively to obtain the slicing execution time corresponding to each slicing stage. The execution time of the slice is 0 for the slice stage that is not executed. Since different SQL statements do not necessarily execute all the sharding phases, and there may be different rules for the time of execution in the different sharding phases, an exception can be found from the execution test to make an optimization suggestion based on the sharding execution time.
Step S105, determining a target rule base based on the database corresponding to the SQL statement.
When the SQL statement is applied to different databases, the rules to be satisfied may be different, so that different target rule bases may be associated according to the databases respectively. One database may be associated with one or more target rule bases.
And step S106, performing rule matching on the analysis result of the execution plan based on the target rule base, and identifying abnormal information.
The abnormal information is the place which is not matched with the rule and is identified by matching according to the target rule base.
And step S107, generating an optimization suggestion according to the identified abnormal information and the fragment execution time of the SQL sentence.
For the content which is required to be optimized and is found by prediction analysis from the plan level, the abnormal information which is identified by the execution plan analysis result is the content which is required to be optimized and is found from the actual execution level according to the slicing execution time, the optimization suggestion can be comprehensively considered from different levels, and the comprehensive SQL sentence optimization suggestion can be obtained without consuming a large amount of resources, so that development efficiency and development effect can be effectively improved for developers.
According to the SQL sentence optimization method, the analysis result of the execution plan of the SQL sentence is analyzed, the pointed target data table is determined, sampling is conducted based on the target data table to generate the temporary data table, the temporary data table is utilized to conduct execution test and control the slicing execution time of the slicing stage, rule matching is conducted on the execution plan analysis result according to the database applied by the SQL sentence, abnormal information is identified, optimization suggestions are generated based on the abnormal information and the slicing execution time, test execution is conducted by utilizing the sampling data, the slicing execution time is tested quickly, and further the actual test execution time is combined with the abnormality identified by matching the execution plan analysis result by utilizing the target rule base to jointly generate the optimization suggestions.
As shown in fig. 2, in one embodiment, the SQL statement optimization method further includes:
step S201, an execution result of executing the SQL statement on the temporary data table is obtained.
Step S202, obtaining index hit data based on the execution result.
Step S203, verifying the index optimization suggestion in the analysis result of the execution plan according to the index hit data.
Step S204, updating the index optimization suggestion based on the verification result.
In this embodiment, index hit data obtained from an execution result obtained by executing a test is used to verify an index optimization suggestion in an execution plan analysis result, verify whether an index problem obtained by prediction analysis and judgment in the execution plan analysis result is correct, correct the problem, and update the index optimization suggestion based on the execution result if the verification is incorrect.
In one embodiment, when the target rule base includes a plurality of rule bases, the step of performing rule matching on the result of analysis of the execution plan based on the target rule base, and identifying the abnormal information includes:
acquiring the priority of each rule base;
and carrying out rule matching on the analysis results of the execution plan in sequence according to the order of the priority from high to low, and identifying the abnormal information.
In this embodiment, when a plurality of rule bases exist, in order to avoid the problem of optimization suggestion abnormality caused by rule conflict, priorities are set in advance for a plurality of target rule bases associated with different databases, and rule matching is sequentially performed according to the priority order when rule matching is performed, so that abnormality information is identified.
In one embodiment, the rule matching is performed on the analysis results of the execution plan sequentially from high to low according to the priority, and identifying the abnormal information includes:
identifying abnormal results based on traversing each rule base in the order of priority from high to low;
identifying whether conflict exists for abnormal results corresponding to rule bases with different priorities;
if the conflict exists, comparing the priority of the rule base corresponding to the abnormal result of the conflict;
the abnormal results corresponding to the rule base with higher priority are reserved, and the abnormal results corresponding to the rule base with lower priority are deleted;
and summarizing the rest abnormal results to generate abnormal information.
In this embodiment, the abnormal result refers to an identification result that is not matched with the rule base, firstly, sequentially traversing each rule base according to the order of priority from high to low to obtain an abnormal result identified by each rule base, then performing conflict detection on the abnormal results corresponding to different rule bases, if there is a conflict, screening the abnormal results according to the priority of the rule base, retaining the abnormal result corresponding to the rule base with higher priority, deleting the abnormal result corresponding to the rule base with higher priority, and finally collecting the obtained residual abnormal results to obtain abnormal information, and generating an optimization suggestion according to the rule corresponding to the abnormal information.
In one embodiment, the SQL statement optimization method further comprises:
acquiring a preset time threshold value of each slicing stage according to the SQL statement;
identifying whether the execution time of each fragment of the SQL sentence exceeds the time threshold of the corresponding fragment stage;
if any fragment execution time exceeds the time threshold of the corresponding fragment stage, generating an alarm prompt according to the SQL sentence and the fragment stage of which the fragment execution time exceeds the time threshold.
In this embodiment, for the possible difference of time of different slicing phases of different SQL statements during execution, a corresponding preset time threshold is obtained to perform alarm detection on the slicing execution time obtained by test execution, if the time threshold of the corresponding slicing phase is exceeded, the slicing phase is indicated to be abnormal, and the generation of an alarm prompt can help a developer to quickly locate the abnormality.
In one embodiment, before executing the execution plan analysis result of the SQL statement, the method further includes:
carrying out grammar analysis on the SQL sentence, and identifying whether grammar errors exist;
if so, grammar optimization suggestions are generated.
In this embodiment, in order to further optimize the SQL statement, before executing the execution plan analysis result of the SQL statement, the SQL statement is parsed first, syntax errors are identified, and optimization suggestions are performed, so that the optimization effect is improved, and development efficiency is improved.
It should be understood that, although the steps in the flowcharts related to the embodiments described above are sequentially shown as indicated by arrows, these steps are not necessarily sequentially performed in the order indicated by the arrows. The steps are not strictly limited to the order of execution unless explicitly recited herein, and the steps may be executed in other orders. Moreover, at least some of the steps in the flowcharts described in the above embodiments may include a plurality of steps or a plurality of stages, which are not necessarily performed at the same time, but may be performed at different times, and the order of the steps or stages is not necessarily performed sequentially, but may be performed alternately or alternately with at least some of the other steps or stages.
The following describes the SQL statement optimization device provided in the embodiments of the present application, and the SQL statement optimization device described below and the SQL statement optimization method described above may be referred to correspondingly.
As shown in fig. 3, the present application provides an SQL statement optimization apparatus 300, including:
a first obtaining module 301, configured to obtain an analysis result of an execution plan of an SQL statement;
a data table determining module 302, configured to determine a target data table based on the execution plan analysis result;
a data table generating module 303, configured to perform data sampling on the target data table to generate a temporary data table;
the execution test module 304 is configured to execute the SQL statement on the temporary data table, and count corresponding shard execution time according to a preset shard stage;
a rule base determining module 305, configured to determine a target rule base based on the database corresponding to the SQL statement;
the rule matching module 306 is configured to perform rule matching on the execution plan analysis result based on the target rule base, and identify abnormal information;
the first suggestion generating module 307 is configured to generate an optimization suggestion according to the identified exception information and the execution time of the slice of the SQL statement.
In one embodiment, the apparatus further comprises:
the second acquisition module is used for acquiring an execution result of executing the SQL sentence on the temporary data table;
a third obtaining module, configured to obtain index hit data based on the execution result;
the verification module is used for verifying the index optimization suggestion in the execution plan analysis result according to the index hit data;
and the suggestion updating module is used for updating the index optimization suggestion based on the verification result.
In one embodiment, the rule matching module is configured to perform the following steps when the target rule base comprises a plurality of rule bases:
acquiring the priority of each rule base;
and carrying out rule matching on the analysis results of the execution plan in sequence according to the order of the priority from high to low, and identifying the abnormal information.
In one embodiment, the rule matching module is configured to perform the following steps when the target rule base comprises a plurality of rule bases:
identifying abnormal results based on traversing each rule base in the order of priority from high to low;
identifying whether conflict exists for abnormal results corresponding to rule bases with different priorities;
if the conflict exists, comparing the priority of the rule base corresponding to the abnormal result of the conflict;
the abnormal results corresponding to the rule base with higher priority are reserved, and the abnormal results corresponding to the rule base with lower priority are deleted;
and summarizing the rest abnormal results to generate the abnormal information.
In one embodiment, the SQL statement optimization apparatus further comprises:
the time threshold acquisition module is used for acquiring a preset time threshold of each slicing stage according to the SQL statement;
the time judging module is used for identifying whether the execution time of each fragment of the SQL sentence exceeds the time threshold of the corresponding fragment stage;
and the prompt generation module is used for generating an alarm prompt according to the SQL statement and the fragments with the execution time exceeding the time threshold value when the execution time of any fragments exceeds the time threshold value of the corresponding fragments.
In one embodiment, the SQL statement optimizing device further comprises
The grammar analysis module is used for carrying out grammar analysis on the SQL sentence and identifying whether grammar errors exist;
and the second suggestion generation module is used for generating a grammar optimization suggestion when recognizing that the SQL sentence has grammar errors.
The division of the modules in the above SQL statement optimization device is merely for illustration, and in other embodiments, the SQL statement optimization device may be divided into different modules as needed to complete all or part of the functions of the above SQL statement optimization device. The modules in the above-described SQL statement optimizing device may be implemented in whole or in part by software, hardware, or a combination thereof. The above modules may be embedded in hardware or may be independent of a processor in the computer device, or may be stored in software in a memory in the computer device, so that the processor may call and execute operations corresponding to the above modules.
In one embodiment, the present application also provides a storage medium having stored therein computer readable instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of the SQL statement optimization method as described in any of the embodiments above.
In one embodiment, the present application further provides a computer device, where computer readable instructions are stored, and when the one or more processors execute the computer readable instructions, the steps of the SQL statement optimization method described in any of the above embodiments are executed.
Illustratively, in one embodiment, a computer device is provided, which may be a server, the internal structure of which may be as shown in FIG. 4. The computer device includes a processor, a memory, and a network interface connected by a system bus. Wherein the processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, computer programs, and a database. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage media. The database of the computer device is for storing data. The network interface of the computer device is used for communicating with an external terminal through a network connection. The computer program when executed by a processor implements an SQL statement optimization method.
In one embodiment, a computer device is provided, which may be a terminal, and the internal structure of which may be as shown in fig. 5. The computer device includes a processor, a memory, a communication interface, a display screen, and an input device connected by a system bus. Wherein the processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage media. The communication interface of the computer device is used for carrying out wired or wireless communication with an external terminal, and the wireless mode can be realized through WIFI, a mobile cellular network, NFC (near field communication) or other technologies. The computer program when executed by a processor implements an SQL statement optimization method. The display screen of the computer equipment can be a liquid crystal display screen or an electronic ink display screen, and the input device of the computer equipment can be a touch layer covered on the display screen, can also be keys, a track ball or a touch pad arranged on the shell of the computer equipment, and can also be an external keyboard, a touch pad or a mouse and the like.
It will be appreciated by those skilled in the art that the structures shown in fig. 4 and 5 are block diagrams of only some of the structures associated with the aspects of the present application and are not intended to limit the computer device to which the aspects of the present application may be applied, and that a particular computer device may include more or less components than those shown, or may combine some of the components, or may have a different arrangement of components.
Those skilled in the art will appreciate that implementing all or part of the above described methods may be accomplished by way of a computer program stored on a non-transitory computer readable storage medium, which when executed, may comprise the steps of the embodiments of the methods described above. Any reference to memory, database, or other medium used in the various embodiments provided herein may include at least one of non-volatile and volatile memory. The nonvolatile Memory may include Read-Only Memory (ROM), magnetic tape, floppy disk, flash Memory, optical Memory, high density embedded nonvolatile Memory, resistive random access Memory (ReRAM), magnetic random access Memory (Magnetoresistive Random Access Memory, MRAM), ferroelectric Memory (Ferroelectric Random Access Memory, FRAM), phase change Memory (Phase Change Memory, PCM), graphene Memory, and the like. Volatile memory can include random access memory (Random Access Memory, RAM) or external cache memory, and the like. By way of illustration, and not limitation, RAM can be in the form of a variety of forms, such as static random access memory (Static Random Access Memory, SRAM) or dynamic random access memory (Dynamic Random Access Memory, DRAM), and the like. The databases referred to in the various embodiments provided herein may include at least one of relational databases and non-relational databases. The non-relational database may include, but is not limited to, a blockchain-based distributed database, and the like. The processors referred to in the embodiments provided herein may be general purpose processors, central processing units, graphics processors, digital signal processors, programmable logic units, quantum computing-based data processing logic units, etc., without being limited thereto.
Finally, it is further noted that relational terms such as first and second, and the like are used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Moreover, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises the element.
Furthermore, the terms "first," "second," and the like, are used for descriptive purposes only and are not to be construed as indicating or implying a relative importance or implicitly indicating the number of technical features indicated. Thus, a feature defining "a first" or "a second" may explicitly or implicitly include at least one such feature. In the description of the present application, the meaning of "plurality" is at least two, such as two, three, etc., unless explicitly defined otherwise.
In the present specification, each embodiment is described in a progressive manner, and each embodiment focuses on the difference from other embodiments, and may be combined according to needs, and the same similar parts may be referred to each other.
The previous description of the disclosed embodiments is provided to enable any person skilled in the art to make or use the present application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the application. Thus, the present application is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.
Claims (10)
1. A method for optimizing an SQL statement, the method comprising:
acquiring an execution plan analysis result of the SQL sentence;
determining a target data table based on the execution plan analysis result;
performing data sampling on the target data table to generate a temporary data table;
executing the SQL statement on the temporary data table, and counting corresponding slicing execution time according to a preset slicing stage;
determining a target rule base based on a database corresponding to the SQL statement;
performing rule matching on the analysis result of the execution plan based on the target rule base, and identifying abnormal information;
and generating optimization suggestions according to the identified abnormal information and the fragment execution time of the SQL statement.
2. The SQL statement optimization method of claim 1, wherein the method further comprises:
acquiring an execution result of executing the SQL statement on the temporary data table;
acquiring index hit data based on the execution result;
verifying index optimization suggestions in the execution plan analysis result according to the index hit data;
updating the index optimization suggestion based on the verification result.
3. The SQL statement optimization method according to claim 1, wherein when the target rule base includes a plurality of rule bases, the step of executing rule matching on the execution plan analysis result based on the target rule base, and identifying abnormality information includes:
acquiring the priority of each rule base;
and carrying out rule matching on the analysis results of the execution plan in sequence according to the order of the priority from high to low, and identifying the abnormal information.
4. The SQL statement optimization method according to claim 3, wherein the rule matching of the execution plan analysis results is performed sequentially in order of priority from high to low, and identifying the exception information comprises:
identifying abnormal results based on traversing each rule base in the order of priority from high to low;
identifying whether conflict exists for abnormal results corresponding to rule bases with different priorities;
if the conflict exists, comparing the priority of the rule base corresponding to the abnormal result of the conflict;
the abnormal results corresponding to the rule base with higher priority are reserved, and the abnormal results corresponding to the rule base with lower priority are deleted;
and summarizing the rest abnormal results to generate the abnormal information.
5. The SQL statement optimization method of claim 1, wherein the method further comprises:
acquiring a preset time threshold value of each slicing stage according to the SQL statement;
identifying whether the execution time of each fragment of the SQL statement exceeds a time threshold of a corresponding fragment stage;
and if any fragment execution time exceeds the time threshold of the corresponding fragment stage, generating an alarm prompt according to the SQL sentence and the fragment stage of which the fragment execution time exceeds the time threshold.
6. The SQL statement optimization method of claim 1, wherein prior to executing the execution plan analysis result of the get SQL statement, further comprising:
carrying out grammar analysis on the SQL sentence, and identifying whether grammar errors exist;
if so, grammar optimization suggestions are generated.
7. An SQL statement optimization device, comprising:
the first acquisition module is used for acquiring an execution plan analysis result of the SQL sentence;
a data table determining module, configured to determine a target data table based on the execution plan analysis result;
the data table generating module is used for carrying out data sampling on the target data table to generate a temporary data table;
the execution test module is used for executing the SQL statement on the temporary data table and counting corresponding slicing execution time according to a preset slicing stage;
the rule base determining module is used for determining a target rule base based on the database corresponding to the SQL statement;
the rule matching module is used for carrying out rule matching on the analysis result of the execution plan based on the target rule base and identifying abnormal information;
and the first suggestion generation module is used for generating an optimization suggestion according to the identified abnormal information and the slicing execution time of the SQL sentence.
8. The SQL statement optimization device of claim 7, wherein the device further comprises:
the second acquisition module is used for acquiring an execution result of executing the SQL sentence on the temporary data table;
a third obtaining module, configured to obtain index hit data based on the execution result;
the verification module is used for verifying the index optimization suggestion in the execution plan analysis result according to the index hit data;
and the suggestion updating module is used for updating the index optimization suggestion based on the verification result.
9. A storage medium, characterized by: the storage medium having stored therein computer readable instructions which, when executed by one or more processors, cause the one or more processors to perform the steps of the SQL statement optimization method of any one of claims 1 to 6.
10. A computer device, comprising: one or more processors, and memory;
stored in the memory are computer readable instructions which, when executed by the one or more processors, perform the steps of the SQL statement optimization method of any one of claims 1 to 6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202311790097.6A CN117762937A (en) | 2023-12-22 | 2023-12-22 | SQL sentence optimization method, SQL sentence optimization device, SQL sentence optimization storage medium and SQL sentence optimization computer device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202311790097.6A CN117762937A (en) | 2023-12-22 | 2023-12-22 | SQL sentence optimization method, SQL sentence optimization device, SQL sentence optimization storage medium and SQL sentence optimization computer device |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN117762937A true CN117762937A (en) | 2024-03-26 |
Family
ID=90323466
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202311790097.6A Pending CN117762937A (en) | 2023-12-22 | 2023-12-22 | SQL sentence optimization method, SQL sentence optimization device, SQL sentence optimization storage medium and SQL sentence optimization computer device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN117762937A (en) |
-
2023
- 2023-12-22 CN CN202311790097.6A patent/CN117762937A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US20240126735A1 (en) | Generating rules for data processing values of data fields from semantic labels of the data fields | |
| Juddoo | Overview of data quality challenges in the context of Big Data | |
| US20140007056A1 (en) | Metadata-based Test Data Generation | |
| CN109062794B (en) | Method and device for determining software evaluation result and electronic equipment | |
| US20190251072A1 (en) | Techniques for automated data analysis | |
| US9886369B2 (en) | Dynamic data fabrication for database applications | |
| CN107168995B (en) | Data processing method and server | |
| US9195730B2 (en) | Verifying correctness of a database system via extended access paths | |
| US10339035B2 (en) | Test DB data generation apparatus | |
| CN113535773B (en) | Database optimization method, database optimization device, electronic device and storage medium | |
| CN112783867A (en) | Database optimization method for meeting real-time big data service requirements and cloud server | |
| CN112328499A (en) | Test data generation method, device, equipment and medium | |
| CN113468204A (en) | Data query method, device, equipment and medium | |
| CN111159482A (en) | Data verification method and system | |
| CN111767213B (en) | Database check point testing method and device, electronic equipment and storage medium | |
| CN116955341A (en) | Database integrity evaluation method, system and application thereof | |
| CN117762937A (en) | SQL sentence optimization method, SQL sentence optimization device, SQL sentence optimization storage medium and SQL sentence optimization computer device | |
| CN113626558B (en) | Intelligent recommendation-based field standardization method and system | |
| CN116401177B (en) | DDL correctness detection method, device and medium | |
| CN116662191A (en) | Data testing method, device, computer equipment and storage medium | |
| KR101862614B1 (en) | Apparatus for validating program effectiveness and computer readable recording medium to member deterioration | |
| CN117555955A (en) | Data conversion method, data conversion device, computer device, and storage medium | |
| CN116610700A (en) | Query statement detection method and device and storage medium | |
| CN117435478A (en) | Program logic function detection method, device, computer equipment and storage medium | |
| CN117474008A (en) | Data correction method and device |
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 |