CN114610749A - Database execution statement optimization method, apparatus, device, medium, and program product - Google Patents

Database execution statement optimization method, apparatus, device, medium, and program product Download PDF

Info

Publication number
CN114610749A
CN114610749A CN202210288848.3A CN202210288848A CN114610749A CN 114610749 A CN114610749 A CN 114610749A CN 202210288848 A CN202210288848 A CN 202210288848A CN 114610749 A CN114610749 A CN 114610749A
Authority
CN
China
Prior art keywords
execution
database
processed
statement
statements
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
Application number
CN202210288848.3A
Other languages
Chinese (zh)
Inventor
王元章
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Ping An Commercial Factoring Co ltd
Original Assignee
Ping An Commercial Factoring Co ltd
Priority date (The priority date 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 date listed.)
Filing date
Publication date
Application filed by Ping An Commercial Factoring Co ltd filed Critical Ping An Commercial Factoring Co ltd
Priority to CN202210288848.3A priority Critical patent/CN114610749A/en
Publication of CN114610749A publication Critical patent/CN114610749A/en
Pending legal-status Critical Current

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

Abstract

The present application relates to a database execution statement optimization method, apparatus, device, medium, and program product. The method comprises the following steps: acquiring a database to be processed execution statement, wherein the database to be processed execution statement carries an execution state; acquiring a corresponding detection rule in a statement optimizer according to the execution state; detecting the database execution statements to be processed according to the detection rules to judge whether the database execution statements to be processed need to be optimized; and when the execution statement of the database to be processed needs to be optimized, optimizing the execution statement of the database to be processed through a pre-generated optimization rule. By adopting the method, the optimization efficiency of the database execution statement can be improved.

Description

Database execution statement optimization method, apparatus, device, medium, and program product
Technical Field
The present application relates to the field of artificial intelligence technologies, and in particular, to a method, an apparatus, a device, a medium, and a program product for optimizing database execution statements.
Background
SQL, a structured query language (structured query language), is a special-purpose programming language that is used primarily to access data and to query, update, and manage relational database systems. In the construction of the SQL system, because the system structure is complicated, the number of participating developers is large, and the syntax and performance problems of the SQL statement are inevitable.
Traditional SQL optimization requires a specific developer to check SQL sentences one by one, analyze whether the SQL sentences are reasonable, and analyze whether the performance design of a database reaches the standard, and has obvious disadvantages such as:
(1) the optimization efficiency is low: one project has thousands of SQL sentences and hundreds of database tables, and the efficiency is very low through manually analyzing and optimizing the SQL sentences one by one.
(2) The code maintenance is difficult: developers designing databases, writing SQL and optimizing SQL are different, and even if complete document data exists, new people are difficult to take over maintenance.
(3) The optimization is not flexible: any database framework modifications must be re-optimized, a process that tends to be lengthy.
(4) The stability is not high: human optimization is easily neglected to cause problems.
Disclosure of Invention
In view of the foregoing, it is desirable to provide a database execution statement optimization method, apparatus, device, medium, and program product that can improve optimization efficiency of database execution statements, in view of the above technical problems.
In a first aspect, the present application provides a method for performing statement optimization on a database, where the method includes:
acquiring a database to be processed execution statement, wherein the database to be processed execution statement carries an execution state;
acquiring a corresponding detection rule in a statement optimizer according to the execution state;
detecting the database execution statements to be processed according to the detection rules to judge whether the database execution statements to be processed need to be optimized;
and when the execution statement of the database to be processed needs to be optimized, optimizing the execution statement of the database to be processed through a pre-generated optimization rule.
In one embodiment, the detecting, according to the detection rule, the to-be-processed database execution statement to determine whether the to-be-processed database execution statement needs to be optimized includes:
when the to-be-processed database execution statement is not executed, judging whether the to-be-processed database execution statement needs to be optimized according to a grammar rule;
when the to-be-processed database execution statement is not executed, counting the complexity of the to-be-processed database execution statement, and judging whether the to-be-processed database execution statement needs to be optimized according to the complexity;
when the execution statements of the database to be processed are executed, counting the execution efficiency of the execution statements of the database to be processed according to an execution plan, and judging whether the execution statements of the database to be processed need to be optimized according to the execution efficiency.
In one embodiment, the optimizing the to-be-processed database execution statement by the pre-generated optimization rule includes at least one of:
putting the fixed value after the query condition where statement on the uppermost layer of the execution statement of the database to be processed; or
When the to-be-processed database execution statement has a sub-query, replacing the result of the sub-query by a temporary table or left association; or
When a specific sentence exists, replacing the specific sentence by the classification data of the corresponding target sentence; or
Acquiring an execution scene of the database execution statements to be processed, and optimizing the database execution statements to be processed according to the execution scene; or
And counting the characteristics of the execution object corresponding to the execution statement of the database to be processed, and performing table division and partition processing on the execution object according to the characteristics of the execution object.
In one embodiment, the obtaining of the to-be-processed database execution statement includes:
and calling a statement optimizer to generate a database execution statement to be processed.
In one embodiment, the method further comprises:
executing the optimized to-be-processed database execution statements to obtain an execution log, and extracting error information from the execution log;
and when the occurrence frequency of the error information reaches a preset frequency, generating a new optimization rule according to the error information.
In one embodiment, the method further comprises:
and outputting the execution statement of the database to be processed and the optimized execution statement of the database to be processed to a log.
In a second aspect, the present application further provides an apparatus for optimizing database execution statements, where the apparatus includes:
the system comprises a statement acquisition module, a statement acquisition module and a statement processing module, wherein the statement acquisition module is used for acquiring a database execution statement to be processed, and the database execution statement to be processed carries an execution state;
the rule obtaining module is used for obtaining a corresponding detection rule in the statement optimizer according to the execution state;
the detection module is used for detecting the database execution statements to be processed according to the detection rules so as to judge whether the database execution statements to be processed need to be optimized;
and the optimization module is used for optimizing the database execution statements to be processed according to a pre-generated optimization rule when the database execution statements to be processed need to be optimized.
In a third aspect, the present application further provides a computer device comprising a memory and a processor, wherein the memory stores a computer program, and the processor implements the steps of the above method when executing the computer program.
In a fourth aspect, the present application also provides a computer-readable storage medium having stored thereon a computer program which, when executed by a processor, performs the steps of the method described above.
In a fifth aspect, the present application also provides a computer program product comprising a computer program which, when executed by a processor, performs the steps of the method described above.
According to the database execution statement optimization method, the device, the equipment, the medium and the program product, the corresponding detection rule in the statement optimizer is called according to the execution state of the database execution statement to be processed, the detection rule is automatically obtained, manual query and detection are not needed, the efficiency is improved, the database execution statement to be processed is subsequently detected through the detection rule to determine whether the database execution statement to be processed needs to be optimized, if so, optimization is carried out according to the corresponding optimization rule, and the automatic detection and optimization method is improved compared with the original manual detection method and manual optimization method.
Drawings
FIG. 1 is a diagram of an application environment in which a database performs a statement optimization methodology in one embodiment;
FIG. 2 is a flowchart illustrating a method for performing statement optimization on a database in one embodiment;
FIG. 3 is a block diagram showing an exemplary apparatus for optimizing database execution statements;
FIG. 4 is a diagram illustrating an internal structure of a computer device according to an embodiment.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the present application and are not intended to limit the present application.
The database execution statement optimization method provided by the embodiment of the application can be applied to the application environment shown in fig. 1. Wherein the processing terminal 102 communicates with the development terminal 104 through a network. The data storage system may store data that the processing terminal 102 needs to process. The data storage system may be integrated on the processing terminal 102, or may be placed on the cloud or other network server.
The processing terminal 102 acquires a to-be-processed database execution statement, wherein the to-be-processed database execution statement carries an execution state; acquiring a corresponding detection rule in the statement optimizer according to the execution state; detecting the execution statements of the database to be processed according to the detection rules so as to judge whether the execution statements of the database to be processed need to be optimized; when the execution statement of the database to be processed needs to be optimized, the execution statement of the database to be processed is optimized through a pre-generated optimization rule, and the efficiency is improved through an automatic detection and optimization method.
The processing terminal 102 and the research and development terminal 104 may be, but are not limited to, various personal computers, notebook computers, smart phones, tablet computers, internet of things devices, and portable wearable devices, and the internet of things devices may be smart speakers, smart televisions, smart air conditioners, smart car-mounted devices, and the like. The portable wearable device can be a smart watch, a smart bracelet, a head-mounted device, and the like.
In one embodiment, as shown in fig. 2, a database execution statement optimization method is provided, which is described by taking the example that the method is applied to the processing terminal 102 in fig. 1, and includes the following steps:
s202: and acquiring an execution statement of the database to be processed, wherein the execution statement of the database to be processed carries an execution state.
Specifically, the pending database execution statement may be an sql statement, such as a query statement, and the like, and the execution state refers to whether the pending database execution statement has been executed, that is, includes an execution state and an unexecuted state.
The processing terminal firstly acquires an execution statement of a to-be-processed database which is completely researched and developed, judges whether debugging is carried out or not, and if so, is in an execution state, otherwise, is in an unexecuted state.
S204: and acquiring a corresponding detection rule in the statement optimizer according to the execution state.
Specifically, the statement optimizer is configured in advance, and includes a detection rule for executing the statement on the database to be processed, and an optimization rule for executing the statement on the database to be processed. Furthermore, an optimization function is configured in the statement optimizer, so that when the statement is executed in the writing of the database to be processed, the optimization function in the statement optimizer is called.
The detection rule corresponds to the execution state, and if the to-be-processed database execution statement is not executed, the grammar rule and the complexity rule in the to-be-processed database execution statement are directly called to detect the to-be-processed database execution statement. If the to-be-processed database execution statement is executed, the execution efficiency rule in the to-be-processed database execution statement can be called to detect the to-be-processed database execution statement.
S206: and detecting the execution statements of the database to be processed according to the detection rules so as to judge whether the execution statements of the database to be processed need to be optimized.
Specifically, the grammar rule is a judgment rule for judging the grammar in the execution statement of the database to be processed, for example, whether the sql breaks the index. The complexity rule includes a determination of at least one of a number of rows of execution statements of the database to be processed, a number of data tables involved, and a number of nesting conditions. The execution efficiency rule includes determining whether the execution efficiency of each step in the execution plan meets a requirement.
And the grammar optimizer calls the obtained detection rule to detect the database execution statement to be processed so as to judge whether the database execution statement to be processed needs to be optimized. Furthermore, when judging that the to-be-processed database execution statement is not executed, the grammar optimizer firstly detects the to-be-processed database execution statement through a grammar rule and a complexity rule, namely a detection rule corresponding to an unexecuted state, then executes the to-be-processed database execution statement, and then calls a detection rule corresponding to an execution state to detect the to-be-processed database execution statement. When the grammar optimizer judges that the execution statement of the database to be processed is executed, for example, the detection of the execution statement of the database to be processed is carried out by a plurality of threads respectively through the detection rule corresponding to the unexecuted state, and the detection of the execution statement of the database to be processed is carried out by the detection rule corresponding to the executed state, so as to ensure the completeness of the detection.
S208: and when the execution statement of the database to be processed needs to be optimized, optimizing the execution statement of the database to be processed through a pre-generated optimization rule.
Specifically, the optimization rules may include common rules and customized rules, where the common rules are preset rules, and the customized rules may be rules customized by the user and rules newly added later according to the execution result.
Optionally, the syntax optimizer may first determine whether the to-be-processed database execution statement needs to be optimized and an optimized type according to the detection rule, and select a corresponding optimization rule according to the optimized type to optimize the to-be-processed database execution statement. Therefore, the execution of optimization rules can be reduced, and the optimization efficiency is improved.
For example, when the to-be-processed database execution statement needs to be optimized, and the type of optimization is syntax optimization, the to-be-processed database execution statement is optimized through syntax optimization rules, for example, some functions are replaced, or the execution sequence of the functions is modified.
When the execution statement of the database to be processed needs to be optimized, and the optimized type is complexity optimization, the execution statement of the database to be processed is optimized through a complexity optimization rule, for example, partitioning and table-splitting execution are performed to reduce complexity.
When the execution statements of the database to be processed need to be optimized, and the optimization type is efficiency optimization, the processing terminal obtains the execution efficiency of each step in the execution plan, selects the execution statements corresponding to the steps in the execution plan with the execution efficiency meeting the requirement, and optimizes the execution statements, including but not limited to syntax optimization and complexity optimization, so that the optimization steps can be reduced, and the optimization efficiency is improved.
According to the database execution statement optimization method, the corresponding detection rule in the statement optimizer is called according to the execution state of the execution statement of the database to be processed, the execution statement of the database to be processed is detected according to the detection rule, whether the execution statement of the database to be processed needs to be optimized or not is judged, if the execution statement of the database to be processed needs to be optimized, the optimization is carried out, and the automatic detection and optimization method is adopted, so that the efficiency is improved.
In one embodiment, detecting the to-be-processed database execution statement according to a detection rule to determine whether the to-be-processed database execution statement needs to be optimized includes: when the execution statement of the database to be processed is not executed, judging whether the execution statement of the database to be processed needs to be optimized or not according to the grammar rule; when the execution statements of the database to be processed are not executed, the complexity of the execution statements of the database to be processed is counted, and whether the execution statements of the database to be processed need to be optimized or not is judged according to the complexity; when the execution statements of the database to be processed are executed, the execution efficiency of the execution statements of the database to be processed is counted according to the execution plan, and whether the execution statements of the database to be processed need to be optimized or not is judged according to the execution efficiency.
Specifically, when the to-be-processed database execution statement is not executed, the syntax optimizer may detect the to-be-processed database execution statement according to the syntax of the sql to obtain the location of the problem, for example, determine whether the to-be-processed database execution statement breaks the index, where the syntax rule is a rule of the sql itself, and no specific limitation is made herein.
Secondly, when the to-be-processed database execution statement is not executed, the grammar optimizer may obtain the complexity of the to-be-processed database execution statement, for example, the number of lines of the to-be-processed database execution statement, the number of involved data tables, and the number of nesting conditions, where a number threshold may be preset, so that the number of lines of the to-be-processed database execution statement, the number of involved data tables, and the number of nesting conditions are compared with a corresponding number threshold, and as long as one of the number thresholds is greater than the corresponding number threshold, it may be determined that the to-be-processed database execution statement is a complex statement, and optimization is required.
When the execution statement of the database to be processed is executed, the sql with low efficiency is inquired, the final sql statement is optimized and written on the log, and the sql segment with the broken index is printed out through the execution plan and output in a log mode. Because many data are searched from different systems and combined, the sql optimizer can judge in segments which system is slow in sql, and what the fixed point, the fixed position and the fixed module show the problem, for example, judging which step is low in efficiency according to an execution plan, and time for developers to search for the problem is saved. Specifically, the execution time of each step is related to the data volume, the efficiency can be obtained through calculation according to the data volume and the actual execution time, the efficiency is compared with an efficiency threshold value to obtain a step with the efficiency not meeting the requirement, and the execution statement of the database to be processed is optimized according to the step.
In one embodiment, the optimization of the execution statement of the database to be processed is performed through a pre-generated optimization rule, and the optimization rule includes at least one of the following items: putting the fixed value after the query condition where statement on the uppermost layer of the execution statement of the database to be processed; or when the to-be-processed database executes the statement and has the sub-query, replacing the result of the sub-query through the temporary table or the left association; or when a specific sentence exists, replacing the specific sentence by the classification data of the corresponding target sentence; or acquiring an execution scene of the execution statements of the database to be processed, and optimizing the execution statements of the database to be processed according to the execution scene; or counting the characteristics of the execution objects corresponding to the execution statements of the database to be processed, and performing table division and partition processing on the execution objects according to the characteristics of the execution objects.
Specifically, the optimization rules include at least one of:
after the where statement of the query condition, everything directly has a constant value such as: all put on top of query condition to improve processing efficiency
All sub-queries for which a field is always used may default to a prompt to be processed instead of a temporary table, or left-associated.
If the is null is judged, the designated data corresponding to the in can be used for replacing the is null; if Is not null, then, in (',') Is replaced with the corresponding specified data.
Applying a special function according to a special scene: the more the function is used, the better the function is, and the more the function is used, the actual scene is used, and the function is used. Such as a large table to small table round robin lookup, in. Small to large tables are circularly queried with exists.
All functions are changed according to specific query logic, and the functions are optimized.
In one embodiment, obtaining the database execution statements to be processed includes: and calling a statement optimizer to generate a database execution statement to be processed.
In particular, the generation of the to-be-processed database execution statements may be performed by a statement optimizer.
In one embodiment, the method for optimizing the database execution statement further includes: executing the optimized to-be-processed database execution statements to obtain an execution log, and extracting error information from the execution log; and when the occurrence frequency of the error information reaches the preset frequency, generating a new optimization rule according to the error information.
In this embodiment, the sql optimizer may categorize that if the same error occurs too many times during use, the sql optimizer will generate an optimized rule that is automatically compiled into optimized code when used by the developer. Therefore, an execution log is obtained from the execution statements of the optimized database to be processed, error information is extracted from the execution log and classified, the number of the error information of each classification is counted, and when the number of the error information reaches the preset number, a new optimization rule is generated according to the error information, so that when the execution statements of the database to be processed are optimized next time, the new optimization rule can be used for optimizing, errors are avoided from appearing again, and the efficiency is improved.
In one embodiment, the method for optimizing the database execution statement further includes: and outputting the to-be-processed database execution statement and the optimized to-be-processed database execution statement to a log.
Specifically, on the unexecuted sql statement, the problem is directly found out according to the syntax optimized by the sql, the problem is output in a log mode, and the sql possibly influencing the efficiency and the improved sql are output at the same time, so that developers can conveniently check the unexecuted problems and the unharmed silk. In the sql sentences which actually occur, searching the inefficient sql, optimizing the final sql sentences to be written on the log, printing out the sql fragments which break the index through the execution plan, and outputting the sql fragments in a log mode.
In other embodiments, the to-be-processed database execution statement and the optimized to-be-processed database execution statement are output to the log with different identifications, for example, different colors of highlights, so that the developer can conveniently check the error.
To facilitate understanding by those skilled in the art, the present application will be described as follows:
in actual work, some functional modules should exist, but a report query system is needed to be made on the basis, as the date types defined at the beginning are various, some are character strings, some are dates, and some are time minutes and seconds, so that the character strings can be intercepted according to the service requirements when time is screened, for example, the normal date type should be yyyy-mm-dd 24h: mi: ss, but the date type in the database is yyyy-mm-dd or directly the character strings yyyyyyymmdd.
Thus, such usage often occurs at the time of query: the query efficiency is very low for the left side of the function, and many reports cannot be found at all, for example, to _ char (sysdate, ' yyyy-mm-dd) ═ to _ char (sysdate, ' yyyyy-mm-dd ') and substr (a.date,1,10) ═ to _ char (sysdate, ' yyyy-mm-dd '), and so on.
There are many solutions, and for the service requirement, a constant, such as a.date ═ to _ char (sysdate, 'yyyyy-mm-dd'), can be changed under the condition that the service data is not changed
a.time>＝to_char(sysdate,’yyyy-mm-dd’)||’00:00:00’
a.time<＝to_char(sysdate,’yyyy-mm-dd’)||’23:59:59’
Thus, the efficiency is improved obviously.
In connection with the example, the purpose of the sql optimizer is to:
for the optimization of the function: and verifying an sql function before executing the query statement, and defaulting to highlight as long as the index is broken, so that a developer can conveniently check errors. After the where statement of the query condition, everything has directly a constant value such as: and a, 1' is placed on the top of the query. Whenever a sub-query is always used by the event field, the default prompt may be processed with a temporary table instead, or left-associated. If there is an is null decision, it can be replaced by what specific data in. Is not null shall be replaced by data, in (','). Applying a special function according to a special scene: the more the function is used, the better the function is, and the more the function is used, the actual scene is used, and the function is used. Such as a large table to small table round robin lookup, in. Small to large tables are circularly queried with exists. All functions are changed according to specific query logic, and the functions are optimized.
Optimization for large tables: at present, if the database is manually partitioned or is subjected to table partitioning, automatic partitioning is difficult to achieve, and when the data volume of one table is too large, the creation of an index and the creation of a partition are emphasized in the application. If the query is inefficient (the optimizer may suggest reworking a new table to handle such functionality alone, the database is burdened with this approach.
In the above embodiment, the Sql optimizer is a product, and actively goes to a code or a database to search for an Sql script to be executed: on the unexecuted sql statement, the problem is directly checked out according to the syntax optimized by sql, the problem is output in a log mode, and the sql possibly influencing the efficiency and the improved sql are output at the same time, so that developers can conveniently check the unexecuted problems without taking the time. In the sql sentences which actually occur, searching the inefficient sql, optimizing the final sql sentences to be written on the log, printing out the sql fragments which break the index through the execution plan, and outputting the sql fragments in a log mode. The system is associated and inquired, and as a lot of data are searched from different systems and combined, the sql optimizer can judge which system is slow in specific by segmenting, and points, positions and modules show problems, so that time for developers to search for the problems is saved. And meanwhile, the sql optimizer can classify, if the same errors occur for too many times in use, the sql optimizer can generate an optimized rule, and when the sql optimizer is used by developers, the sql optimizer can automatically compile the optimized code.
It should be understood that, although the steps in the flowcharts related to the embodiments are shown in sequence as indicated by the arrows, the steps are not necessarily executed in sequence as indicated by the arrows. The steps are not performed in the exact order shown and described, and may be performed in other orders, unless explicitly stated otherwise. Moreover, at least a part of the steps in the flowcharts related to the above embodiments may include multiple steps or multiple stages, which are not necessarily performed at the same time, but may be performed at different times, and the order of performing the steps or stages is not necessarily sequential, but may be performed alternately or alternately with other steps or at least a part of the steps or stages in other steps.
Based on the same inventive concept, the embodiment of the present application further provides a database execution statement optimization device for implementing the above-mentioned database execution statement optimization method. The implementation scheme for solving the problem provided by the device is similar to the implementation scheme described in the method, so that the specific limitations in one or more embodiments of the database execution statement optimization device provided below may refer to the limitations on the database execution statement optimization method in the foregoing, and are not described herein again.
In one embodiment, as shown in fig. 3, there is provided a database execution statement optimization apparatus, including: a statement obtaining module 301, a rule obtaining module 302, a detecting module 303 and an optimizing module 304, wherein:
a statement obtaining module 301, configured to obtain a to-be-processed database execution statement, where the to-be-processed database execution statement carries an execution state;
a rule obtaining module 302, configured to obtain a corresponding detection rule in the statement optimizer according to the execution state;
a detection module 303, configured to detect the to-be-processed database execution statement according to the detection rule, so as to determine whether the to-be-processed database execution statement needs to be optimized;
and the optimization module 304 is configured to optimize the to-be-processed database execution statement according to a pre-generated optimization rule when the to-be-processed database execution statement needs to be optimized.
In one embodiment, the detecting module 303 is further configured to determine whether the execution statement of the database to be processed needs to be optimized according to a syntax rule when the execution statement of the database to be processed is not executed; when the execution statements of the database to be processed are not executed, the complexity of the execution statements of the database to be processed is counted, and whether the execution statements of the database to be processed need to be optimized or not is judged according to the complexity; when the execution statements of the database to be processed are executed, the execution efficiency of the execution statements of the database to be processed is counted according to the execution plan, and whether the execution statements of the database to be processed need to be optimized or not is judged according to the execution efficiency.
In one embodiment, the optimization module 304 is further configured to place a fixed value after the query condition where statement in the top layer of the to-be-processed database execution statement; when the execution statement of the database to be processed has the sub-query, replacing the result of the sub-query through the temporary table or the left association; when a specific sentence exists, replacing the specific sentence by the classification data of the corresponding target sentence; acquiring an execution scene of an execution statement of a database to be processed, and optimizing the execution statement of the database to be processed according to the execution scene; and counting the characteristics of the execution objects corresponding to the execution statements of the database to be processed, and performing table division and partition processing on the execution objects according to the characteristics of the execution objects.
In one embodiment, the statement obtaining module 301 is further configured to invoke a statement optimizer to generate a to-be-processed database execution statement.
In one embodiment, the database execution statement optimization apparatus further includes:
the extraction module is used for executing the optimized execution statements of the database to be processed to obtain an execution log and extracting error information from the execution log;
and the generating module is used for generating a new optimization rule according to the error information when the occurrence frequency of the error information reaches a preset frequency.
In one embodiment, the apparatus for optimizing database execution statements further includes:
and the output module is used for outputting the execution statement of the database to be processed and the optimized execution statement of the database to be processed to a log.
Each block in the database execution statement optimization apparatus may be implemented in whole or in part by software, hardware, and a combination thereof. The modules can be embedded in a hardware form or independent from a processor in the computer device, and can also be stored in a memory in the computer device in a software form, so that the processor can call and execute operations corresponding to the modules.
In one embodiment, a computer device is provided, which may be a terminal, and its internal structure diagram may be as shown in fig. 4. 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 comprises a nonvolatile 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 an operating system and computer programs in the non-volatile storage medium. The communication interface of the computer device is used for carrying out wired or wireless communication with an external terminal, and the wireless communication can be realized through WIFI, a mobile cellular network, NFC (near field communication) or other technologies. The computer program is executed by a processor to implement a database execution 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, a key, a track ball or a touch pad arranged on the shell of the computer equipment, an external keyboard, a touch pad or a mouse and the like.
Those skilled in the art will appreciate that the architecture shown in fig. 4 is merely a block diagram of some of the structures associated with the disclosed aspects and is not intended to limit the computing devices to which the disclosed aspects apply, as particular computing devices may include more or less components than those shown, or may combine certain components, or have a different arrangement of components.
In one embodiment, a computer device is provided, comprising a memory and a processor, the memory having a computer program stored therein, the processor implementing the following steps when executing the computer program: acquiring a database to be processed execution statement, wherein the database to be processed execution statement carries an execution state; acquiring a corresponding detection rule in the statement optimizer according to the execution state; detecting the execution statements of the database to be processed according to the detection rule so as to judge whether the execution statements of the database to be processed need to be optimized; and when the execution statement of the database to be processed needs to be optimized, optimizing the execution statement of the database to be processed through a pre-generated optimization rule.
In one embodiment, the detecting, according to a detection rule, an execution statement of a database to be processed, implemented when a processor executes a computer program, to determine whether the execution statement of the database to be processed needs to be optimized includes: when the execution statement of the database to be processed is not executed, judging whether the execution statement of the database to be processed needs to be optimized or not according to the grammar rule; when the execution statements of the database to be processed are not executed, the complexity of the execution statements of the database to be processed is counted, and whether the execution statements of the database to be processed need to be optimized or not is judged according to the complexity; when the execution statements of the database to be processed are executed, counting the execution efficiency of the execution statements of the database to be processed according to the execution plan, and judging whether the execution statements of the database to be processed need to be optimized according to the execution efficiency.
In one embodiment, the optimization of the database execution statement to be processed by the pre-generated optimization rule implemented when the processor executes the computer program includes at least one of: putting the fixed value after the query condition where statement on the uppermost layer of the execution statement of the database to be processed; or when the to-be-processed database executes the statement and has the sub-query, replacing the result of the sub-query through the temporary table or the left association; or when a specific sentence exists, replacing the specific sentence by the classification data of the corresponding target sentence; or acquiring an execution scene of the execution statements of the database to be processed, and optimizing the execution statements of the database to be processed according to the execution scene; or counting the characteristics of the execution objects corresponding to the execution statements of the database to be processed, and performing table division and partition processing on the execution objects according to the characteristics of the execution objects.
In one embodiment, the obtaining of the pending database execution statement performed by the processor when executing the computer program comprises: and calling a statement optimizer to generate a database execution statement to be processed.
In one embodiment, the processor when executing the computer program further performs the steps of: executing the optimized to-be-processed database execution statements to obtain an execution log, and extracting error information from the execution log; and when the occurrence frequency of the error information reaches the preset frequency, generating a new optimization rule according to the error information.
In one embodiment, the processor, when executing the computer program, further performs the steps of: and outputting the execution statements of the database to be processed and the optimized execution statements of the database to be processed to a log.
In one embodiment, a computer-readable storage medium is provided, having a computer program stored thereon, which when executed by a processor, performs the steps of: acquiring a database to be processed execution statement, wherein the database to be processed execution statement carries an execution state; acquiring a corresponding detection rule in the statement optimizer according to the execution state; detecting the execution statements of the database to be processed according to the detection rules so as to judge whether the execution statements of the database to be processed need to be optimized; and when the execution statement of the database to be processed needs to be optimized, optimizing the execution statement of the database to be processed through a pre-generated optimization rule.
In one embodiment, the detecting, according to the detection rule, the to-be-processed database execution statement to determine whether the to-be-processed database execution statement needs to be optimized, implemented when the computer program is executed by the processor, includes: when the execution statements of the database to be processed are not executed, judging whether the execution statements of the database to be processed need to be optimized according to the grammar rules; when the execution statements of the database to be processed are not executed, the complexity of the execution statements of the database to be processed is counted, and whether the execution statements of the database to be processed need to be optimized or not is judged according to the complexity; when the execution statements of the database to be processed are executed, counting the execution efficiency of the execution statements of the database to be processed according to the execution plan, and judging whether the execution statements of the database to be processed need to be optimized according to the execution efficiency.
In one embodiment, the optimization of the database execution statements to be processed by the pre-generated optimization rules, implemented when the computer program is executed by the processor, comprises at least one of: placing the fixed value after the query condition where statement on the uppermost layer of the execution statement of the database to be processed; or when the to-be-processed database executes the statement and has the sub-query, replacing the result of the sub-query through the temporary table or the left association; or when a specific sentence exists, replacing the specific sentence by the classification data of the corresponding target sentence; or acquiring an execution scene of the execution statements of the database to be processed, and optimizing the execution statements of the database to be processed according to the execution scene; or counting the characteristics of the execution objects corresponding to the execution statements of the database to be processed, and performing table division and partition processing on the execution objects according to the characteristics of the execution objects.
In one embodiment, the obtaining of the pending database execution statement performed when the computer program is executed by the processor comprises: and calling a statement optimizer to generate a database execution statement to be processed.
In one embodiment, the computer program when executed by the processor further performs the steps of: executing the optimized to-be-processed database execution statements to obtain an execution log, and extracting error information from the execution log; and when the occurrence frequency of the error information reaches the preset frequency, generating a new optimization rule according to the error information.
In one embodiment, the computer program when executed by the processor further performs the steps of: and outputting the execution statements of the database to be processed and the optimized execution statements of the database to be processed to a log.
In one embodiment, a computer program product is provided, comprising a computer program which, when executed by a processor, performs the steps of: acquiring a database to be processed execution statement, wherein the database to be processed execution statement carries an execution state; acquiring a corresponding detection rule in the statement optimizer according to the execution state; detecting the execution statements of the database to be processed according to the detection rules so as to judge whether the execution statements of the database to be processed need to be optimized; and when the execution statements of the database to be processed need to be optimized, optimizing the execution statements of the database to be processed through a pre-generated optimization rule.
In one embodiment, the detecting, according to the detection rule, the to-be-processed database execution statement to determine whether the to-be-processed database execution statement needs to be optimized, implemented when the computer program is executed by the processor, includes: when the execution statement of the database to be processed is not executed, judging whether the execution statement of the database to be processed needs to be optimized or not according to the grammar rule; when the execution statements of the database to be processed are not executed, the complexity of the execution statements of the database to be processed is counted, and whether the execution statements of the database to be processed need to be optimized or not is judged according to the complexity; when the execution statements of the database to be processed are executed, the execution efficiency of the execution statements of the database to be processed is counted according to the execution plan, and whether the execution statements of the database to be processed need to be optimized or not is judged according to the execution efficiency.
In one embodiment, the optimization of the database execution statements to be processed by the pre-generated optimization rules, implemented when the computer program is executed by the processor, comprises at least one of: putting the fixed value after the query condition where statement on the uppermost layer of the execution statement of the database to be processed; or when the to-be-processed database executes the statement and has the sub-query, replacing the result of the sub-query through the temporary table or the left association; or when a specific sentence exists, replacing the specific sentence by the classification data of the corresponding target sentence; or acquiring an execution scene of the execution statements of the database to be processed, and optimizing the execution statements of the database to be processed according to the execution scene; or counting the characteristics of the execution objects corresponding to the execution statements of the database to be processed, and performing table division and partition processing on the execution objects according to the characteristics of the execution objects.
In one embodiment, the obtaining of the pending database execution statement when the computer program is executed by the processor comprises: and calling a statement optimizer to generate a database execution statement to be processed.
In one embodiment, the computer program when executed by the processor further performs the steps of: executing the optimized to-be-processed database execution statements to obtain an execution log, and extracting error information from the execution log; and when the occurrence frequency of the error information reaches the preset frequency, generating a new optimization rule according to the error information.
In one embodiment, the computer program when executed by the processor further performs the steps of: and outputting the execution statements of the database to be processed and the optimized execution statements of the database to be processed to a log.
It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by hardware instructions of a computer program, which can be stored in a non-volatile computer-readable storage medium, and when executed, can include the processes of the embodiments of the methods described above. Any reference to memory, database, or other medium used in the 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 (MRAM), Ferroelectric Random Access Memory (FRAM), Phase Change Memory (PCM), graphene Memory, and the like. Volatile Memory can include Random Access Memory (RAM), external cache Memory, and the like. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM), among others. The databases referred to in various embodiments provided herein may include at least one of relational and non-relational databases. The non-relational database may include, but is not limited to, a block chain 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 devices, quantum computing based data processing logic devices, etc., without limitation.
The technical features of the above embodiments can be arbitrarily combined, and for the sake of brevity, all possible combinations of the technical features in the above embodiments are not described, but should be considered as the scope of the present specification as long as there is no contradiction between the combinations of the technical features.
The above-mentioned embodiments only express several embodiments of the present application, and the description thereof is more specific and detailed, but not construed as limiting the scope of the present application. It should be noted that, for a person skilled in the art, several variations and modifications can be made without departing from the concept of the present application, which falls within the scope of protection of the present application. Therefore, the protection scope of the present application shall be subject to the appended claims.

Claims (10)

1. A method for database execution statement optimization, the method comprising:
acquiring a database to be processed execution statement, wherein the database to be processed execution statement carries an execution state;
acquiring a corresponding detection rule in a statement optimizer according to the execution state;
detecting the database execution statements to be processed according to the detection rules to judge whether the database execution statements to be processed need to be optimized;
and when the execution statement of the database to be processed needs to be optimized, optimizing the execution statement of the database to be processed through a pre-generated optimization rule.
2. The method according to claim 1, wherein the detecting the to-be-processed database execution statement according to the detection rule to determine whether the to-be-processed database execution statement needs to be optimized includes:
when the to-be-processed database execution statement is not executed, judging whether the to-be-processed database execution statement needs to be optimized or not according to a grammar rule;
when the to-be-processed database execution statement is not executed, counting the complexity of the to-be-processed database execution statement, and judging whether the to-be-processed database execution statement needs to be optimized according to the complexity;
when the execution statements of the database to be processed are executed, counting the execution efficiency of the execution statements of the database to be processed according to an execution plan, and judging whether the execution statements of the database to be processed need to be optimized according to the execution efficiency.
3. The method according to claim 1, wherein the optimizing the to-be-processed database execution statement by the pre-generated optimization rule includes at least one of:
putting the fixed value after the query condition where statement on the uppermost layer of the execution statement of the database to be processed; or
When the to-be-processed database execution statement has a sub-query, replacing the result of the sub-query by a temporary table or left association; or
When a specific sentence exists, replacing the specific sentence by the classification data of the corresponding target sentence; or
Acquiring an execution scene of the database execution statements to be processed, and optimizing the database execution statements to be processed according to the execution scene; or
And counting the characteristics of the execution object corresponding to the execution statement of the database to be processed, and performing table division and partition processing on the execution object according to the characteristics of the execution object.
4. The method of claim 1, wherein obtaining the pending database execution statement comprises:
and calling a statement optimizer to generate a database execution statement to be processed.
5. The method of any of claims 1 to 4, further comprising:
executing the optimized to-be-processed database execution statements to obtain an execution log, and extracting error information from the execution log;
and when the occurrence frequency of the error information reaches a preset frequency, generating a new optimization rule according to the error information.
6. The method of any one of claims 1 to 4, further comprising:
and outputting the execution statement of the database to be processed and the optimized execution statement of the database to be processed to a log.
7. An apparatus for optimizing database execution statements, the apparatus comprising:
the system comprises a statement acquisition module, a statement acquisition module and a statement processing module, wherein the statement acquisition module is used for acquiring a database execution statement to be processed, and the database execution statement to be processed carries an execution state;
the rule obtaining module is used for obtaining a corresponding detection rule in the statement optimizer according to the execution state;
the detection module is used for detecting the database execution statements to be processed according to the detection rules so as to judge whether the database execution statements to be processed need to be optimized;
and the optimization module is used for optimizing the database execution statements to be processed according to a pre-generated optimization rule when the database execution statements to be processed need to be optimized.
8. A computer device comprising a memory and a processor, the memory storing a computer program, characterized in that the processor, when executing the computer program, implements the steps of the method of any of claims 1 to 6.
9. A computer-readable storage medium, on which a computer program is stored, which, when being executed by a processor, carries out the steps of the method of any one of claims 1 to 6.
10. A computer program product comprising a computer program, characterized in that the computer program realizes the steps of the method of any one of claims 1 to 6 when executed by a processor.
CN202210288848.3A 2022-03-22 2022-03-22 Database execution statement optimization method, apparatus, device, medium, and program product Pending CN114610749A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202210288848.3A CN114610749A (en) 2022-03-22 2022-03-22 Database execution statement optimization method, apparatus, device, medium, and program product

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202210288848.3A CN114610749A (en) 2022-03-22 2022-03-22 Database execution statement optimization method, apparatus, device, medium, and program product

Publications (1)

Publication Number Publication Date
CN114610749A true CN114610749A (en) 2022-06-10

Family

ID=81865817

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202210288848.3A Pending CN114610749A (en) 2022-03-22 2022-03-22 Database execution statement optimization method, apparatus, device, medium, and program product

Country Status (1)

Country Link
CN (1) CN114610749A (en)

Similar Documents

Publication Publication Date Title
US11630762B2 (en) Method and apparatus for testing map service
WO2019085474A1 (en) Calculation engine implementing method, electronic device, and storage medium
US8949222B2 (en) Changing the compression level of query plans
US9569492B2 (en) Coalescing operation for query processing
CN110795455A (en) Dependency relationship analysis method, electronic device, computer device and readable storage medium
US10324710B2 (en) Indicating a trait of a continuous delivery pipeline
WO2021217846A1 (en) Interface data processing method and apparatus, and computer device and storage medium
US10191947B2 (en) Partitioning advisor for online transaction processing workloads
US9195730B2 (en) Verifying correctness of a database system via extended access paths
CN117033424A (en) Query optimization method and device for slow SQL (structured query language) statement and computer equipment
CN111221690B (en) Model determination method and device for integrated circuit design and terminal
CN115617594B (en) Method, apparatus, storage medium, and program product for generating incentive information
CN116860311A (en) Script analysis method, script analysis device, computer equipment and storage medium
US9201937B2 (en) Rapid provisioning of information for business analytics
CN114610749A (en) Database execution statement optimization method, apparatus, device, medium, and program product
CN115757172A (en) Test execution method and device, storage medium and computer equipment
CN116578583B (en) Abnormal statement identification method, device, equipment and storage medium
JP6870454B2 (en) Analytical equipment, analytical programs and analytical methods
CN110795451B (en) SQL fingerprint restoring method, SQL fingerprint restoring device, SQL fingerprint restoring computer equipment and storage medium
CN117827978A (en) Data conflict processing method, device, computer equipment and storage medium
CN116881289A (en) Method, device, computer equipment and storage medium for processing aggregate data
CN114385630A (en) Blood relationship combing method, apparatus, device, storage medium, and program product
CN115809304A (en) Method and device for analyzing field-level blood margin, computer equipment and storage medium
CN117453437A (en) Database statement processing method, device, computer equipment and storage medium
CN117785250A (en) System change quality control method, device, computer equipment and storage medium

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