Database operation statement optimization method and device, electronic equipment, medium and product
By judging and converting it into target operation statements containing grouped statements in the database operation statements, the problem of limited application scenarios in the prior art is solved, and a wider deduplication operation scenarios and higher query optimization performance are achieved.
Patent Information
- Application Number
- CN202411312150.6
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-09-19
- Publication Date
- 2025-02-07
AI Technical Summary
The prior art only supports substitution of statements containing distinct in the aggregation function. The application scenarios are limited and it is impossible to effectively process database operation statements that do not include aggregation function and window function.
By obtaining the database operation statement and determining whether the preset conditions are met (there is a keyword statement for deduplication, does not contain aggregation function and a window function, and does not have grouping statements for deduplication), if it is satisfied, the statement is converted to Target operation statements, including grouped statements for deduplication.
Deduplication operations are implemented in a wider range of scenarios, improving the performance of query optimization, reducing redundant columns and unnecessary key-value grouping, and improving efficiency with parallel processing capabilities.
Smart Images
Figure CN119396865A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of database technology, and in particular to a method, device, electronic equipment, medium and product for optimizing database operation statements. Background Art
[0002] In database queries, the distinct operation is used to remove duplicate rows from the result set. For example, when the distinct operation is used in an aggregate function, it ensures that the aggregate function only processes unique values.
[0003] In the prior art, when distinct is used in an aggregate function to perform deduplication processing, group by can be used instead of distinct to achieve the deduplication effect.
[0004] However, the above method has certain limitations, that is, it only supports replacement of statements containing distinct in aggregate functions, and its application scenarios are limited. Summary of the invention
[0005] The present application provides a database operation statement optimization method, device, electronic device, medium and product, which are used to solve the problem that the existing application scenarios are limited because only statements containing distinct in aggregate functions are supported for replacement.
[0006] In a first aspect, the present application provides a method for optimizing database operation statements, the method comprising:
[0007] Obtaining a database operation statement, and determining whether the database operation statement satisfies a preset condition; the preset condition includes the existence of a keyword statement for deduplication, not containing an aggregation function and a window function, and, if executable, the absence of a grouping statement for deduplication;
[0008] If so, the database operation statement is converted into a target operation statement; the target operation statement includes a grouping statement for deduplication.
[0009] Optionally, converting the database operation statement into a target operation statement includes:
[0010] Determining a deduplication range of the keyword sentence;
[0011] If the keyword statement is used to deduplicate the specified column and retain the values of other columns except the specified column, then execute the database operation statement;
[0012] If the keyword statement is used to remove duplicates from all target columns, the database operation statement is converted into a target operation statement.
[0013] Optionally, the keyword statement is used to remove duplicate rows in the target column; the group statement is used to group the data in the table according to the specified column, and each group contains rows with the same value.
[0014] Optionally, after obtaining the database operation statement, the method further includes:
[0015] The database operation statements are filtered based on a filtering condition to obtain a database operation statement to be processed; the filtering condition includes filtering out a constant value and statements related to the constant value.
[0016] Optionally, the database operation statements include query statements, add statements, delete statements, and update statements.
[0017] Optionally, the method further includes:
[0018] Executing the target operation statement and the database operation statement respectively, and obtaining a first result set and a second result set after the execution;
[0019] Verifying the first result set and the second result set to obtain a verification result;
[0020] Determine whether there is a problem with the target operation statement based on the verification result;
[0021] When there is a problem with the target operation statement, the target operation statement is restored.
[0022] In a second aspect, the present application provides a database operation statement optimization device, the device comprising:
[0023] An acquisition module, used to acquire a database operation statement and determine whether the database operation statement meets a preset condition; the preset condition includes the existence of a keyword statement for deduplication, no aggregation function and window function, and no grouping statement for deduplication if executable;
[0024] The conversion module is used to convert the database operation statement into a target operation statement when the database operation statement meets a preset condition; the target operation statement includes a grouping statement for deduplication.
[0025] In a third aspect, the present application provides an electronic device, comprising: a processor, and a memory communicatively connected to the processor;
[0026] The memory stores computer-executable instructions;
[0027] The processor executes the computer-executable instructions stored in the memory to implement the method as described in any one of the first aspects.
[0028] In a fourth aspect, the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and when the computer-executable instructions are executed by a processor, they are used to implement the method as described in any one of the first aspects.
[0029] In a fifth aspect, the present application provides a computer program product, comprising a computer program, which, when executed by a processor, implements the method as described in any one of the first aspects.
[0030] In summary, the present application provides a method, device, electronic device, medium and product for optimizing database operation statements, which can be used for the current scenario of using distinct for deduplication, and is suitable for converting the group by statement in the case where the table is fully scanned according to all key values and then the result set is deduplicated. Specifically, when it is determined that the database operation statement includes a keyword statement for deduplication, does not contain aggregation functions and window functions, and does not contain a grouping statement for deduplication when it is executable, the group by statement is used to replace the distinct statement, and then the target operation statement is converted and generated, so that the group by key value can be used to eliminate redundant columns, reduce unnecessary key value grouping, and utilize the capability of parallel processing to improve the performance of query optimization; wherein, the group by key value refers to the column or expression used for grouping. BRIEF DESCRIPTION OF THE DRAWINGS
[0031] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.
[0032] Figure 1 A schematic diagram of an application scenario provided for an embodiment of the present application;
[0033] Figure 2 A flowchart of a method for optimizing database operation statements provided in an embodiment of the present application;
[0034] Figure 3 A flowchart of an optional database operation statement optimization method provided in an embodiment of the present application;
[0035] Figure 4 A schematic diagram of the structure of a database operation statement optimization device provided in an embodiment of the present application;
[0036] Figure 5 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application.
[0037] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION
[0038] In order to facilitate the clear description of the technical solutions of the embodiments of the present application, in the embodiments of the present application, the words "first", "second" and the like are used to distinguish the same items or similar items with substantially the same functions and effects. For example, the first device and the second device are only used to distinguish different devices, and their order is not limited. Those skilled in the art can understand that the words "first", "second" and the like do not limit the quantity and execution order, and the words "first", "second" and the like do not necessarily limit them to be different.
[0039] It should be noted that, in this application, words such as "exemplary" or "for example" are used to indicate examples, illustrations or descriptions. Any embodiment or design described as "exemplary" or "for example" in this application should not be interpreted as being more preferred or more advantageous than other embodiments or designs. Specifically, the use of words such as "exemplary" or "for example" is intended to present related concepts in a specific way.
[0040] In this application, "at least one" means one or more, and "plurality" means two or more. "And / or" describes the association relationship of associated objects, indicating that three relationships may exist. For example, A and / or B can mean: A exists alone, A and B exist at the same time, and B exists alone, where A and B can be singular or plural. The character " / " generally indicates that the previous and next associated objects are in an "or" relationship. "At least one of the following" or similar expressions refers to any combination of these items, including any combination of single or plural items. For example, at least one of a, b, or c can mean: a, b, c, ab, ac, bc, or abc, where a, b, c can be single or multiple.
[0041] The existing distinct deduplication operation only supports at least one first column to be queried in the query table, and distinct is in an aggregation function. In a possible implementation, a structured query statement (SQL) to be processed is obtained, wherein the SQL statement to be processed includes the table to be queried and an aggregation function with distinct for each first column to be aggregated in the table to be queried, and the table to be queried includes multiple first columns to be aggregated; further, a target query table is generated according to the table to be queried and the sequence number table, and a subquery is generated for the target query table, wherein the sequence number table includes a table composed of sequence numbers of each first column to be aggregated, and the subquery uses group by to deduplicate each first column to be aggregated; and then the SQL statement to be processed can be rewritten according to the subquery to obtain a target SQL statement, wherein the target SQL statement deduplicates and aggregates each first column to be aggregated.
[0042] It should be noted that the above process of rewriting the SQL statement to be processed uses group by instead of distinct, but the above statement only processes the distinct in the aggregate function, which has certain limitations and is limited in application scenarios.
[0043] In response to the above problems, the present application provides a database operation statement optimization method, which can be used for the current scenario of using distinct for deduplication, and is suitable for converting the group by statement in the case where the table is fully scanned according to all key values and then the result set is deduplicated. Specifically, when it is determined that the database operation statement includes a keyword statement for deduplication, does not contain aggregate functions and window functions, and does not contain a grouping statement for deduplication when it is executable, the group by statement is used to replace the distinct statement, and then the target operation statement is converted and generated, so that the group by key value can be used to eliminate redundant columns, reduce unnecessary key value grouping, and utilize the capability of parallel processing to improve the performance of query optimization; wherein, the group by key value refers to the column or expression used for grouping.
[0044] For example, Figure 1 A schematic diagram of an application scenario provided in an embodiment of the present application, such as Figure 1 As shown, the application scenario can be applied to the scenario of rewriting SQL statements containing distinct, and the application scenario includes: a server 102 and a display device 101 corresponding to the server 102, and the display device 101 is used for users to view the data in the table and perform a series of table operations, such as adding, deleting, modifying, and querying the table.
[0045] Taking the deduplication operation on the data in a certain table as an example, the server 102 can obtain the relevant database operation statements from the database in advance. Further, the server 102 can determine whether the database operation statement meets the preset conditions. After determining that the database operation statement meets the preset conditions, the group by statement is used to replace the distinct statement, and then converted to generate a target operation statement, so that the server 102 can perform deduplication operations on the data in the table based on the target operation statement.
[0046] Among them, the preset condition includes the existence of keyword statements for deduplication, no aggregation functions and window functions, and no grouping statements for deduplication when executable. Taking the database operation statement as a query statement as an example, the preset condition can be: the query statement contains a distinct statement, but does not contain aggregation functions and window functions. When the setsql_mode to switch is turned on, the query statement itself does not contain a group by statement.
[0047] Furthermore, after the server 102 performs a deduplication operation on the data in the table based on the target operation statement, the deduplication result set may be displayed on the display device 101 for the user to view.
[0048] In this way, when converting the distinct statement into a group by statement and generating a target operation statement for deduplication, the existing functions of group by can be utilized. For example, group by can be used to classify data according to certain columns, which is convenient for separate processing and analysis of data of different categories. When group by is used for deduplication, the amount of data processing can be reduced, and query performance can be improved. The existing functions of group by will not be described one by one in the embodiments of the present application. For details, please refer to the existing description.
[0049] It should be noted that the embodiment of the present application does not specifically limit the form of the database operation statement, which can be a query statement, an add statement, a delete statement, etc.
[0050] The technical solution of the present application and how the technical solution of the present application solves the above-mentioned technical problems are described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.
[0051] Figure 2 A flowchart of a method for optimizing database operation statements provided in an embodiment of the present application is shown, taking the execution subject as a server as an example. Figure 2 As shown, the database operation statement optimization method includes the following steps:
[0052] S201. Obtain a database operation statement, and determine whether the database operation statement meets preset conditions; the preset conditions include the existence of a keyword statement for deduplication, no aggregation function and window function, and no grouping statement for deduplication if executable.
[0053] Among them, the aggregation function is used to calculate a group of values and return a single value, such as "SUM", "COUNT", "AVG", "MAX", "MIN" and other functions; the window function is used to perform calculations within a certain "window" of the query results without grouping the data, such as "RANK", "LAG", "LEAD" and other functions. The embodiments of the present application do not limit the specific function types corresponding to the aggregation functions and window functions, which can be determined according to the specific circumstances.
[0054] In an embodiment of the present application, a database operation statement may refer to a command or instruction used to interact with a database, which is usually written using SQL. The database operation statement may be used for various operations, such as querying data, inserting data, updating data, and deleting data, etc., and may include query statements, add statements, delete statements, and update statements.
[0055] Among them, query statements are used to retrieve data from the database, add statements are used to insert new records into the database table, delete statements are used to delete records from the database table, and update statements are used to modify existing records in the database table.
[0056] Since database operation statements may include query statements, add statements, delete statements, and update statements, the present application can perform statement optimization on query statements, add statements, delete statements, and update statements. By optimizing query statements, query efficiency can be improved and data accuracy and consistency can be ensured. By optimizing add statements, insertion of duplicate data can be avoided and data uniqueness can be ensured. By optimizing delete statements, deletion efficiency can be improved and resource consumption can be reduced. By optimizing update statements, data consistency and accuracy can be ensured.
[0057] It should be noted that the present application can intelligently process and optimize all types of database operation statements through the judgment of preset conditions, reduce manual intervention and improve work efficiency.
[0058] In an embodiment of the present application, the keyword statement may be a distinct statement, which is used to remove duplicate rows in the target column; the grouping statement may be a group by statement, which is used to group the data in the table according to the specified column, and each group contains rows with the same value. For example, the database operation statement may be select distinct a,b from test, which is used to retrieve a unique combination of "a" and "b" columns from a table named "test", where "a" and "b" are columns in the "test" table, and the statement is used to extract data from these two columns. Distinct (keyword) is used to ensure that there are no duplicate rows in the result set, that is, if there are multiple rows in the "test" table with the same combination of "a" and "b" values, they only appear once in the result set. The embodiment of the present application does not limit the specific content corresponding to the database operation statement, and the above is only an example.
[0059] In view of the above situation, it can be determined that the database operation statement meets the preset conditions, that is, the database operation statement contains a group by statement, does not contain aggregate functions and window functions, and does not contain a grouping statement for deduplication if it is executable.
[0060] It should be noted that since the principles of deduplication of the DISTINCT statement and the GROUP BY statement are similar, when the database operation statement meets the preset conditions, GROUP BY can be used instead of DISTINCT to achieve deduplication, which is conducive to further utilizing the existing functions of GROUP BY to achieve fast deduplication and utilize the parallel function of GROUP BY.
[0061] Among them, when using the distinct statement for deduplication, the entire table needs to be scanned. After filtering out all the results that meet the conditions, the results are deduplicated to output the deduplicated results. When using the group by statement for deduplication, although the entire table also needs to be scanned, for the primary key table, the group by statement only needs to use the existing key value elimination function to remove non-primary key columns, that is, group according to the primary key, and then deduplicate according to the grouping situation. In addition, if the amount of data is large, the parallel function of group by can also be used for deduplication. Further, the deduplication results are output. Therefore, compared with the distinct statement, the group by statement greatly improves the deduplication efficiency.
[0062] It is understandable that in some cases, the use of the group by statement can make the intent of the query statement clearer, especially when data needs to be grouped, and some database systems or query engines may not support the distinct statement as well as the group by statement, especially in complex queries. Therefore, the use of the group by statement can improve the compatibility and portability of the query.
[0063] It should be noted that the group by statement has the following advantages: for primary key tables, grouping can be performed based only on the primary key, and data corresponding to useless non-primary key columns can be filtered out. In addition, for tables with large amounts of data, parallel processing can be implemented to improve the efficiency of processing data.
[0064] S202: If yes, convert the database operation statement into a target operation statement; the target operation statement includes a grouping statement for deduplication.
[0065] In some embodiments, taking the database operation statement as a query statement as an example, the query statement is select distinct a,b from test. When the query statement is executed, the server will deduplicate based on the fact that a and b are not at the same time, and then output the deduplicated result. However, for the above query statement, it can be determined that it meets the preset conditions, and further, it is converted into a target operation statement, that is, select a,b from test group by a,b. In this way, the target operation statement will be grouped according to the key values a, b, and then deduplicated after grouping, and then the deduplicated result will be output.
[0066] It should be noted that the target operation statement is a query statement including a group by statement, and thus the target operation statement can utilize the group by parallel function to perform deduplication, thereby greatly improving deduplication efficiency.
[0067] Therefore, by rewriting the query to use group by instead of distinct to achieve deduplication, we can utilize the existing functions of group by and improve query efficiency.
[0068] For example, Figure 3 A flowchart of an optional database operation statement optimization method provided in an embodiment of the present application is shown as follows: Figure 3 As shown, taking the database operation statement as a query block as an example, the database operation statement optimization method includes the following steps:
[0069] Step A: Determine whether the query block meets the conversion condition (preset condition). If so, execute step B. If not, do not optimize the query block, but use the original query block for deduplication. The preset condition is that the query block contains a distinct statement, does not contain aggregate functions and window functions, and does not contain a group by statement if it is executable. The executable condition refers to the case where the set sql_mode to switch can be turned on.
[0070] Step B: For the query block, convert the distinct statement in it into a group by statement to obtain the target operation statement.
[0071] Therefore, when it is determined that the database operation statement meets the preset conditions, the embodiment of the present application can convert the database operation statement containing the deduplication keyword statement into a target operation statement containing the grouping statement, and can utilize the index and other optimization techniques of the grouping statement to reduce computing overhead and improve the parallel processing speed. Moreover, the use of the grouping statement can improve the compatibility and portability of the query. While expanding the application scenarios of distinct statement replacement, the present application can also effectively utilize the performance of the grouping statement.
[0072] Optionally, converting the database operation statement into a target operation statement includes:
[0073] Determining a deduplication range of the keyword sentence;
[0074] If the keyword statement is used to deduplicate the specified column and retain the values of other columns except the specified column, then execute the database operation statement;
[0075] If the keyword statement is used to remove duplicates from all target columns, the database operation statement is converted into a target operation statement.
[0076] In an embodiment of the present application, the deduplication range may refer to the range for removing duplicate content in a list, and the deduplication range is determined based on the type of keyword statement. If the keyword statement is a distinct statement, the distinct statement is used to remove all duplicate rows from the query results to ensure that each row is unique. It is applicable to all database systems that support the SQL standard and to all combinations that appear in the target column. For example, distinct can output results based on the situation where a and b are different. If the keyword statement is a distinct on statement, the distinct on statement is used to remove duplicate rows based on the specified column from the query results, and can control which row to retain, that is, the distinct on statement is used to deduplicate on the specified column and retain the values of other columns. For example, distinct on can deduplicate based on the attribute column connected by on and output a unique column.
[0077] It should be noted that the distinct statement is applicable to simple queries that need to remove duplicate rows in all columns, while the distinct on statement is applicable to complex queries that need to deduplicate rows based on specific columns and control the retained rows. Therefore, although both the distinct statement and the distinct on statement are used for deduplication, their usage scenarios and functions are different. Therefore, the preset condition should also include: the keyword statement is used to deduplicate all target columns, that is, the keyword statement is limited to a distinct statement.
[0078] Therefore, the embodiment of the present application adopts different operation strategies for different deduplication ranges, which can enhance the flexibility of the query and meet different business needs, thereby optimizing the query execution plan and improving the query efficiency, so that when targeting the distinct statement, the database operation statement can be optimized to accurately control the deduplication behavior and ensure the accuracy of the conversion process.
[0079] Optionally, after obtaining the database operation statement, the method further includes:
[0080] The database operation statements are filtered based on a filtering condition to obtain a database operation statement to be processed; the filtering condition includes filtering out a constant value and statements related to the constant value.
[0081] In an embodiment of the present application, a constant value may refer to a fixed value, such as a number, a string, or a date, and a constant value-related statement may refer to a part of a query that uses a constant value for filtering or comparison. For example, "30" in where age=30 is a constant value filtering condition.
[0082] It should be noted that the embodiments of the present application do not limit the specific content corresponding to the constants and statements related to the constants, which can be determined according to the specific circumstances.
[0083] In the present application, filtering out constant values and related statements can reduce the computational overhead of the query, because the database does not need to process these fixed conditions, and filtering out constant value-related statements can reduce redundant data in the query results and improve the quality of the data.
[0084] Exemplarily, constant values and statements related to the constant values are identified in the SQL query, and then these constant values and related statements are removed from the original query to obtain a simplified database operation statement to be processed.
[0085] Therefore, the embodiment of the present application can simplify the database operation statements by filtering out constant values and related statements, and then greatly improve the processing rate when judging whether the database operation statements meet the preset conditions.
[0086] Optionally, the method further includes:
[0087] Executing the target operation statement and the database operation statement respectively, and obtaining a first result set and a second result set after the execution;
[0088] Verifying the first result set and the second result set to obtain a verification result;
[0089] Determine whether there is a problem with the target operation statement based on the verification result;
[0090] When there is a problem with the target operation statement, the target operation statement is restored.
[0091] Optionally, when there is a problem with the target operation statement, the target operation statement can be directly deleted, the database operation statement can be retained, and then a deduplication operation can be performed based on the original database operation statement.
[0092] Optionally, if there is a problem with the target operation statement, the target operation statement may be overwritten with the original database operation statement, and then a deduplication operation may be performed based on the original database operation statement.
[0093] Optionally, when there is a problem with the target operation statement, a prompt message may be generated to prompt the R&D personnel to make corrections and the like. The prompt message may be sent to the R&D personnel's terminal device or a display device corresponding to the server to prompt the R&D personnel to make corrections.
[0094] In the embodiment of the present application, since the target operation statement may not be executed normally or may be executed abnormally due to grammatical problems or statement omissions, the target operation statement and the database operation statement may be executed separately to verify whether the execution results of the two are consistent, so as to determine whether the optimized database operation statement is correct and reasonable.
[0095] Therefore, the embodiments of the present application can respectively execute the target operation statement and the database operation statement and verify the results. By verifying the first result set and the second result set, it is possible to effectively detect problems in the target operation statement, and automatically restore the target operation statement when problems are detected. This automated error recovery mechanism can reduce the need for human intervention, improve overall reliability and stability, and improve user satisfaction by ensuring the correctness and stability of data operations.
[0096] In the above embodiments, the database operation statement optimization method provided by the embodiment of the present application is introduced, and in order to realize the various functions in the method provided by the embodiment of the present application, the electronic device as the execution subject may include a hardware structure and / or a software module, and the above functions are realized in the form of a hardware structure, a software module, or a hardware structure plus a software module. Whether a certain function of the above functions is executed in the form of a hardware structure, a software module, or a hardware structure plus a software module depends on the specific application and design constraints of the technical solution.
[0097] For example, Figure 4 A schematic diagram of the structure of a database operation statement optimization device provided in an embodiment of the present application is shown in FIG. Figure 4 As shown, the device 400 includes:
[0098] The acquisition module 401 is used to acquire a database operation statement and determine whether the database operation statement meets a preset condition; the preset condition includes the existence of a keyword statement for deduplication, no aggregation function and window function, and no grouping statement for deduplication if executable;
[0099] The conversion module 402 is used to convert the database operation statement into a target operation statement when the database operation statement meets a preset condition; the target operation statement includes a grouping statement for deduplication.
[0100] Optionally, the conversion module 402 is specifically configured to:
[0101] Determining a deduplication range of the keyword sentence;
[0102] If the keyword statement is used to deduplicate the specified column and retain the values of other columns except the specified column, then execute the database operation statement;
[0103] If the keyword statement is used to remove duplicates from all target columns, the database operation statement is converted into a target operation statement.
[0104] Optionally, the keyword statement is used to remove duplicate rows in the target column; the group statement is used to group the data in the table according to the specified column, and each group contains rows with the same value.
[0105] Optionally, after obtaining the database operation statement, the device 400 further includes a filtering module, the filtering module being used to:
[0106] The database operation statements are filtered based on a filtering condition to obtain a database operation statement to be processed; the filtering condition includes filtering out a constant value and statements related to the constant value.
[0107] Optionally, the database operation statements include query statements, add statements, delete statements, and update statements.
[0108] Optionally, the device 400 further includes a verification module, the verification module being configured to:
[0109] Executing the target operation statement and the database operation statement respectively, and obtaining a first result set and a second result set after the execution;
[0110] Verifying the first result set and the second result set to obtain a verification result;
[0111] Determine whether there is a problem with the target operation statement based on the verification result;
[0112] When there is a problem with the target operation statement, the target operation statement is restored.
[0113] It should be noted that the specific implementation principle and effects of the above-mentioned database operation statement optimization device can be found in the relevant descriptions and effects corresponding to the above-mentioned embodiments, and will not be elaborated here.
[0114] The present application also provides a schematic diagram of the structure of an electronic device. Figure 5 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application is shown in FIG. Figure 5 As shown, the electronic device may include: a processor 501 and a memory 502 communicatively connected to the processor; the memory 502 stores a computer program; the processor 501 executes the computer program stored in the memory 502, so that the processor 501 executes the method described in any of the above embodiments.
[0115] The memory 502 and the processor 501 may be connected via a bus 503 .
[0116] An embodiment of the present application further provides a computer-readable storage medium, which stores computer-executable instructions. When the computer-executable instructions are executed by a processor, they are used to implement the method described in any of the aforementioned embodiments of the present application.
[0117] An embodiment of the present application further provides a chip for executing instructions, wherein the chip is used to execute the method described in any of the aforementioned embodiments as executed by an electronic device in any of the aforementioned embodiments of the present application.
[0118] An embodiment of the present application further provides a computer program product, which includes a computer program. When the computer program is executed by a processor, the method described in any of the aforementioned embodiments of the present application executed by an electronic device can be implemented.
[0119] In the several embodiments provided in the present application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic. For example, the division of modules is only a logical function division. There may be other division methods in actual implementation, such as multiple modules or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or modules, which can be electrical, mechanical or other forms.
[0120] The modules described as separate components may or may not be physically separated, and the components shown as modules may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the modules may be selected according to actual needs to implement the solution of this embodiment.
[0121] In addition, each functional module in each embodiment of the present application can be integrated into one processing unit, or each module can exist physically separately, or two or more modules can be integrated into one unit. The above-mentioned module-composed unit can be implemented in the form of hardware or in the form of hardware plus software functional units.
[0122] The above-mentioned integrated module implemented in the form of a software function module can be stored in a computer-readable storage medium. The above-mentioned software function module is stored in a storage medium, including a number of instructions for enabling a computer device (which can be a personal computer, a server, or a network device, etc.) or a processor to perform some steps of the method described in each embodiment of the present application.
[0123] It should be understood that the above processor can be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. The general-purpose processor can be a microprocessor or the processor can be any conventional processor, etc. The steps of the method disclosed in the application can be directly embodied as being executed by a hardware processor, or can be executed by a combination of hardware and software modules in the processor.
[0124] The memory may include high-speed random access memory (RAM), and may also include non-volatile memory (NVM), such as at least one disk storage, and may also be a USB flash drive, a mobile hard disk, a read-only memory, a disk or an optical disk, etc.
[0125] The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. The bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, the bus in the drawings of this application is not limited to only one bus or one type of bus.
[0126] The above storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic memory, flash memory, magnetic disk or optical disk. The storage medium can be any available medium that can be accessed by a general or special computer.
[0127] An exemplary storage medium is coupled to a processor so that the processor can read information from the storage medium and write information to the storage medium. Of course, the storage medium can also be a component of the processor. The processor and the storage medium can be located in an application specific integrated circuit (ASIC). Of course, the processor and the storage medium can also exist as discrete components in an electronic device or a main control device.
[0128] It should be noted that, for the aforementioned method embodiments, for the sake of simplicity, they are all expressed as a series of action combinations, but those skilled in the art should be aware that the present application is not limited by the described order of actions, because according to the present application, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all optional embodiments, and the actions and modules involved are not necessarily required by the present application.
[0129] It should be further noted that, although the various steps in the flowchart are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence in the order indicated by the arrows. Unless there is a clear description in this article, the execution of these steps is not strictly limited in order, and these steps can be executed in other orders. Moreover, at least a portion of the steps in the flowchart may include multiple sub-steps or multiple stages, and these sub-steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these sub-steps or stages is not necessarily to be carried out in sequence, but can be executed in turn or alternately with other steps or at least a portion of the sub-steps or stages of other steps.
[0130] In the above embodiments, the description of each embodiment has its own emphasis. For the part not described in detail in a certain embodiment, please refer to the relevant description of other embodiments. The technical features of the above embodiments can be combined arbitrarily. In order to make the description concise, all possible combinations of the technical features in the above embodiments are not described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.
[0131] Those skilled in the art will readily appreciate other embodiments of the present application after considering the specification and practicing the invention disclosed herein. The present application is intended to cover any modification, use or adaptation of the present application, which follows the general principles of the present application and includes common knowledge or customary techniques in the art that are not disclosed in the present application. The specification and examples are intended to be exemplary only, and the true scope and spirit of the present application are indicated by the claims.
[0132] The above is only a specific implementation of the embodiment of the present application, but the protection scope of the embodiment of the present application is not limited thereto, and any changes or replacements within the technical scope disclosed in the embodiment of the present application should be included in the protection scope of the embodiment of the present application. Therefore, the protection scope of the embodiment of the present application should be based on the protection scope of the claims.
Claims
1. A method for optimizing database operation statements, characterized in that: The method comprises: Obtaining a database operation statement, and determining whether the database operation statement satisfies a preset condition; the preset condition includes the existence of a keyword statement for deduplication, not containing an aggregation function and a window function, and, if executable, the absence of a grouping statement for deduplication; If so, the database operation statement is converted into a target operation statement; the target operation statement includes a grouping statement for deduplication.
2. The method according to claim 1, characterized in that: Converting the database operation statement into a target operation statement includes: Determining a deduplication range of the keyword sentence; If the keyword statement is used to deduplicate the specified column and retain the values of other columns except the specified column, then execute the database operation statement; If the keyword statement is used to remove duplicates from all target columns, the database operation statement is converted into a target operation statement.
3. The method according to claim 1, characterized in that The keyword statement is used to remove duplicate rows in the target column; the group statement is used to group the data in the table according to the specified column, and each group contains rows with the same value.
4. The method according to claim 1, characterized in that After obtaining the database operation statement, the method further includes: The database operation statements are filtered based on a filtering condition to obtain a database operation statement to be processed; the filtering condition includes filtering out a constant value and statements related to the constant value.
5. The method according to claim 1, characterized in that The database operation statements include query statements, add statements, delete statements, and update statements.
6. The method according to any one of claims 1 to 5, characterized in that: The method further comprises: Executing the target operation statement and the database operation statement respectively, and obtaining a first result set and a second result set after the execution; Verifying the first result set and the second result set to obtain a verification result; Determining whether there is a problem with the target operation statement based on the verification result; In the case where there is a problem with the target operation statement, the target operation statement is restored.
7. A database operation statement optimization device, characterized in that: The device comprises: An acquisition module, used to acquire a database operation statement and determine whether the database operation statement meets a preset condition; the preset condition includes the existence of a keyword statement for deduplication, no aggregation function and window function, and no grouping statement for deduplication if executable; The conversion module is used to convert the database operation statement into a target operation statement when the database operation statement meets a preset condition; the target operation statement includes a grouping statement for deduplication.
8. An electronic device, characterized in that: include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 6.
9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, and when the computer-executable instructions are executed by a processor, they are used to implement the method according to any one of claims 1 to 6.
10. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 6 when being executed by a processor.