A method for detecting rationality of join query optimization

Abstract

The present application provides a method for detecting the rationality of connection query optimization, the method comprising: generating a database for query. Generating an original query statement, the original query statement comprising at least two connection statements, the connection statements being used to connect the query plan in the query statement. Querying the database based on the original query statement to obtain a first query result. Optimizing the connection statement in the original query statement to obtain a second query statement, the query plan of the second query statement being different from the query plan of the original query statement. Querying the database based on the second query statement to obtain a second query result. Generating a rationality detection result based on the difference between the first query result and the second query result. The method provided by the present application can efficiently detect the rationality of connection query optimization.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method for detecting rationality of connection query optimization. Background Art

[0002] With the continuous development of science and technology, the amount of data has increased explosively. In relational databases, SQL (Structured Query Language) is the standard language for managing and operating databases. As the amount of data grows, SQL query performance has become one of the key factors in system performance. Especially when dealing with associations between multiple tables, performance issues are particularly prominent. Join query operations can combine data rows from two or more tables, but improper use of join queries can lead to inefficient queries, consume a lot of resources, and even cause system crashes. Therefore, optimizing SQL join queries is an important means to improve database performance.

[0003] After optimizing SQL connection query operations, if the optimized query returns an incorrect result, the performance improvement will be meaningless and may even have a negative impact on the business. Therefore, it is necessary to perform rationality detection on SQL connection query optimization. Currently, most methods for rationality detection of SQL connection query optimization remain in the manual stage, and there is no efficient detection method to improve detection efficiency.

[0004] Therefore, how to improve the efficiency of rationality detection for optimizing SQL connection queries has become a technical problem that those skilled in the art need to solve urgently. Summary of the invention

[0005] Based on the above problems, the present application provides a method for detecting the rationality of connection query optimization, which determines the rationality of connection query optimization by comparing query results, thereby improving the efficiency of rationality detection of SQL connection query optimization.

[0006] The present application provides a method for detecting rationality of connection query optimization, the method comprising: Generate a database for querying; Generate an original query statement, wherein the original query statement includes at least two connection statements, and the connection statements are used to connect the query plans in the query statement; Performing a query in the database based on the original query statement to obtain a first query result; Optimizing the join statement in the original query statement to obtain a second query statement, wherein the query plan of the second query statement is different from the query plan of the original query statement; Performing a query in the database based on the second query statement to obtain a second query result; A rationality detection result is generated based on the difference between the first query result and the second query result.

[0007] In a possible implementation, generating a database for query includes: Generating a first type of result statement based on a random generation method, each of the first type of result statement includes at least one type of query result corresponding to the query question; Mutating the query results in the first type of result statements to obtain second type of result statements; A database for query is generated based on the first-category result statements and the second-category result statements.

[0008] In a possible implementation, generating a rationality detection result based on a difference between the first query result and the second query result includes: If the first query result and the second query result are the same, generating a detection result of reasonable connection query optimization; If the first query result and the second query result are different, a detection result of unreasonable connection query optimization is generated.

[0009] In a possible implementation, the method further includes: If a detection result that the connection query optimization is unreasonable is generated, the connection statement in the second query statement is optimized to obtain an optimized second query statement; Performing a query in the database based on the optimized second query statement to obtain an optimized second query result; A second rationality detection result is generated based on a difference between the first query result and the optimized second query result.

[0010] The present application also provides a device for detecting rationality of connection query optimization, the device comprising: A first generating module, used to generate a database for query; A second generating module is used to generate an original query statement, wherein the original query statement includes at least two connection statements, and the connection statements are used to connect the query plans in the query statement; A first query module, configured to query the database based on the original query statement to obtain a first query result; An optimization module, configured to optimize the join statement in the original query statement to obtain a second query statement, wherein the query plan of the second query statement is different from the query plan of the original query statement; A second query module, configured to query the database based on the second query statement to obtain a second query result; A rationality detection module is used to generate a rationality detection result based on the difference between the first query result and the second query result.

[0011] In a possible implementation manner, the first generating module is specifically configured to: Generating a first type of result statement based on a random generation method, each of the first type of result statement includes at least one type of query result corresponding to the query question; Mutating the query results in the first type of result statements to obtain second type of result statements; A database for query is generated based on the first-category result statements and the second-category result statements.

[0012] In a possible implementation, the rationality detection module is specifically used to: If the first query result and the second query result are the same, generating a detection result of reasonable connection query optimization; If the first query result and the second query result are different, a detection result of unreasonable connection query optimization is generated.

[0013] In a possible implementation, the device further includes a statement optimization module, and the statement optimization module is specifically used to: If a detection result that the connection query optimization is unreasonable is generated, the connection statement in the second query statement is optimized to obtain an optimized second query statement; Performing a query in the database based on the optimized second query statement to obtain an optimized second query result; A second rationality detection result is generated based on a difference between the first query result and the optimized second query result.

[0014] The present application also provides an electronic device, the electronic device comprising a processor and a memory: The memory is used to store a computer program and transmit the computer program to the processor; The processor is used to execute the steps of the above-mentioned method for detecting rationality of connection query optimization according to the instructions in the computer program.

[0015] The present application also provides a computer-readable storage medium, which is used to store a computer program. When the computer program is executed by an electronic device, the steps of the method for detecting the rationality of connection query optimization are implemented.

[0016] Compared with the prior art, this application has the following beneficial effects: The method provided by the present application generates an original query statement and executes the query based on the database to obtain a first query result, optimizes the connection statement in the original query statement, generates a second query statement for execution, obtains a second query result, and generates a rationality detection result based on the difference between the first query result and the second query result. The whole process is automated, avoiding the tediousness and inefficiency of manual comparison, and improving the efficiency of rational detection of the result set after query optimization. By comparing the difference between the query results before and after optimization, the present application can quickly identify errors or abnormal situations that may be introduced during the optimization process. If there is a difference between the optimized query result and the original query result, potential errors can be discovered in time to avoid the negative impact of erroneous data on the business, thereby ensuring that the correctness of the result set is fully guaranteed when the database system performs query optimization. By optimizing the connection statement in the SQL query, reducing resource consumption, and improving query efficiency, the present application can ensure the correctness of the optimized query result. This detection mechanism provides a reliable guarantee for database performance optimization, avoiding erroneous results and system crashes caused by improper query optimization. Traditional methods mainly rely on manual detection to compare the query results before and after optimization. This method not only wastes manpower and material resources, but also is prone to human omissions. The detection method of the present application avoids human errors through automated comparison, and can more efficiently and comprehensively detect result set problems that may arise after query optimization, ensuring that the optimized query can still provide correct and reliable results while improving performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0018] Figure 1 A flowchart of a method for detecting rationality of connection query optimization provided in an embodiment of the present application; Figure 2 A schematic diagram of the structure of a device for detecting the rationality of connection query optimization provided in an embodiment of the present application. DETAILED DESCRIPTION

[0019] As described above, in relational databases, SQL (Structured Query Language) is a standard language for managing and operating databases. With the growth of data volume, SQL query performance has become one of the key factors in system performance. In particular, when dealing with associations between multiple tables, performance issues are particularly prominent. Join query operations can combine data rows from two or more tables, but improper use of join queries can lead to low query efficiency, consume a lot of resources, and even cause system crashes. Therefore, optimizing SQL join queries is an important means to improve database performance. After optimizing SQL join query operations, if the optimized query returns an incorrect result, the performance improvement will lose its meaning and may even have a negative impact on the business. Therefore, it is necessary to detect the rationality of optimizing SQL join queries. At present, most methods for detecting the rationality of optimizing SQL join queries remain in the manual stage, and there is no efficient detection method to improve the detection efficiency. Therefore, how to improve the efficiency of detecting the rationality of optimizing SQL join queries has become a technical problem that those skilled in the art need to solve urgently.

[0020] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0021] It is understandable that the method provided in the present application can be applied to a processing device, which is a processing device that can generate a database for query, such as a terminal device or a server that can generate a database for query. The method provided in the present application can be executed independently by a terminal device or a server, or it can be applied to a network scenario in which a terminal device and a server communicate, and is executed by the cooperation of a terminal device and a server. Among them, the terminal device can be a computer, a mobile phone and other devices. The server can be understood as an application server or a Web server. In actual deployment, the server can be a stand-alone server or a cluster server.

[0022] Figure 1 A flow chart of a method for detecting rationality of connection query optimization provided by the present application, the method comprising the following steps: S101: Generate a database for query.

[0023] The processing device generates a database for querying.

[0024] In a possible implementation, in order to generate a database for query, the processing device may first generate a table in the database, and then generate data based on the table. Specifically, the processing device may randomly generate the database structure and data, such as using a grammar rule-based method to automatically generate the structure of the database table and necessary data, such as constructing the table and index in the database through statements such as CREATE TABLE and CREATE INDEX.

[0025] It should be noted that the database used for query stores non-query statements. The generation of non-query statements can be, for example, to automatically generate a database structure and data content that meets the requirements through SQL statements such as CREATE TABLE and INSERT INTO, ensuring that there is enough table data for testing when executing the query.

[0026] In a possible implementation, the processing device may generate a first-category result statement based on a random generation method, each of which includes at least one type of query result corresponding to the query question. The query result in the first-category result statement is mutated to obtain a second-category result statement. A database for query is generated based on the first-category result statement and the second-category result statement.

[0027] S102: Generate an original query statement.

[0028] The processing device generates an original query statement, which includes at least two connection statements, and the connection statements are used to connect the query plans in the query statement. This method simulates the actual SQL query operation by generating a query statement containing a connection operation. In order to generate these query statements, we use the automatic generation technology of grammar rules to randomly traverse the corresponding syntax tree to generate SQL queries that meet the requirements. For example, the processing device can automatically generate a query statement containing a connection query (such as JOIN) based on the generated database table and data. Here, the query statement includes at least two connection statements for connecting data from multiple tables. In order to ensure that the query syntax is correct, the processing device can encode the syntax of the SQL dialect of the database management system and generate a connection query based on the syntax tree traversal.

[0029] In order to ensure the correctness of the connection query, the processing device can randomly generate a legal query statement by traversing the query syntax tree to ensure that the query structure meets the SQL syntax rules and contains valid connection operations.

[0030] S103: Query the database based on the original query statement to obtain a first query result.

[0031] The processing device may query the database based on the original query statement to obtain a first query result.

[0032] The processing device executes the original query statement and obtains the query result. By executing the original query statement, its execution time, execution plan and query result can be obtained, providing a benchmark for subsequent optimization comparison.

[0033] S104: Optimizing the connection statement in the original query statement by using query hints and system variables that affect the query plan to obtain a second query statement.

[0034] The processing device optimizes the join statement in the original query statement using the query hint and the system variable affecting the query plan to obtain a second query statement, and the query plan of the second query statement is different from the query plan of the original query statement. The query hint refers to a hint that specifies the use of the indicated hint within the query scope, which affects almost all operators in the query statement.

[0035] The query plan is an important part of a complete query process. It is a detailed description of the execution process of a SQL statement in the database.

[0036] By optimizing the second query statement using query hints and system variables that affect the query plan, it can be ensured that the query purpose is the same as that of the original query statement but the query plan is different.

[0037] The processing device optimizes the join statement in the original query statement and generates a new query statement. The purpose of this optimization is to improve query performance, reduce execution time, or better utilize the index structure of the database. The processing device obtains the optimized query statement and prepares for subsequent comparison.

[0038] S105: Query the database based on the second query statement to obtain a second query result.

[0039] The processing device performs a query in the database based on the second query statement to obtain a second query result.

[0040] The optimized query statement is executed by the processing device, and its query result is obtained, which is the second query result.

[0041] S106: Generate a rationality detection result based on the difference between the first query result and the second query result.

[0042] The processing device may generate a rationality detection result based on a difference between the first query result and the second query result.

[0043] The processing device compares the result sets of the original query statement and the optimized query statement to verify whether the optimization is correct.

[0044] In a possible implementation, if the first query result and the second query result are the same, a detection result of reasonable connection query optimization is generated; If the first query result and the second query result are different, a detection result of unreasonable connection query optimization is generated.

[0045] In another possible implementation, if a detection result that the connection query optimization is unreasonable is generated, the connection statement in the second query statement is optimized to obtain an optimized second query statement; Performing a query in the database based on the optimized second query statement to obtain an optimized second query result; A second rationality detection result is generated based on a difference between the first query result and the optimized second query result.

[0046] The method provided by the present application generates an original query statement and executes the query based on the database to obtain a first query result, optimizes the connection statement in the original query statement, generates a second query statement for execution, obtains a second query result, and generates a rationality detection result based on the difference between the first query result and the second query result. The whole process is automated, avoiding the tediousness and inefficiency of manual comparison, and improving the efficiency of rational detection of the result set after query optimization. By comparing the difference between the query results before and after optimization, the present application can quickly identify errors or abnormal situations that may be introduced during the optimization process. If there is a difference between the optimized query result and the original query result, potential errors can be discovered in time to avoid the negative impact of erroneous data on the business, thereby ensuring that the correctness of the result set is fully guaranteed when the database system performs query optimization. By optimizing the connection statement in the SQL query, reducing resource consumption, and improving query efficiency, the present application can ensure the correctness of the optimized query result. This detection mechanism provides a reliable guarantee for database performance optimization, avoiding erroneous results and system crashes caused by improper query optimization. Traditional methods mainly rely on manual detection to compare the query results before and after optimization. This method not only wastes manpower and material resources, but also is prone to human omissions. The detection method of the present application avoids human errors through automated comparison, and can more efficiently and comprehensively detect result set problems that may arise after query optimization, ensuring that the optimized query can still provide correct and reliable results while improving performance.

[0047] The present application also provides a Figure 2 The structural schematic diagram of the detection device for connection query optimization rationality shown in FIG. 200 includes: A first generating module 201 is used to generate a database for query; A second generating module 202, configured to generate an original query statement, wherein the original query statement includes at least two connection statements, and the connection statements are used to connect the query plans in the query statement; A first query module 203, configured to query the database based on the original query statement to obtain a first query result; An optimization module 204, configured to optimize the join statement in the original query statement to obtain a second query statement, wherein the query plan of the second query statement is different from the query plan of the original query statement; A second query module 205, configured to query the database based on the second query statement to obtain a second query result; The rationality detection module 206 is used to generate a rationality detection result based on the difference between the first query result and the second query result.

[0048] In a possible implementation manner, the first generating module is specifically configured to: Generating a first type of result statement based on a random generation method, each of the first type of result statement includes at least one type of query result corresponding to the query question; Mutating the query results in the first type of result statements to obtain second type of result statements; A database for query is generated based on the first-category result statements and the second-category result statements.

[0049] In a possible implementation, the rationality detection module is specifically used to: If the first query result and the second query result are the same, generating a detection result of reasonable connection query optimization; If the first query result and the second query result are different, a detection result of unreasonable connection query optimization is generated.

[0050] In a possible implementation, the device further includes a statement optimization module, and the statement optimization module is specifically used to: If a detection result that the connection query optimization is unreasonable is generated, the connection statement in the second query statement is optimized to obtain an optimized second query statement; Performing a query in the database based on the optimized second query statement to obtain an optimized second query result; A second rationality detection result is generated based on a difference between the first query result and the optimized second query result.

[0051] The device provided by the present application generates an original query statement and executes the query based on the database to obtain a first query result, optimizes the connection statement in the original query statement, generates a second query statement for execution, obtains a second query result, and generates a rationality detection result based on the difference between the first query result and the second query result. The whole process is automated, avoiding the tediousness and inefficiency of manual comparison, and improving the efficiency of rational detection of the result set after query optimization. By comparing the difference between the query results before and after optimization, the present application can quickly identify errors or abnormal situations that may be introduced during the optimization process. If there is a difference between the optimized query result and the original query result, potential errors can be discovered in time to avoid the negative impact of erroneous data on the business, thereby ensuring that the correctness of the result set is fully guaranteed when the database system performs query optimization. By optimizing the connection statement in the SQL query, reducing resource consumption, and improving query efficiency, the present application can ensure the correctness of the optimized query result. This detection mechanism provides a reliable guarantee for database performance optimization, avoiding erroneous results and system crashes caused by improper query optimization. Traditional methods mainly rely on manual detection to compare the query results before and after optimization, which not only wastes manpower and material resources, but also is prone to human omissions. The detection method of the present application avoids human errors through automated comparison, and can more efficiently and comprehensively detect result set problems that may arise after query optimization, ensuring that the optimized query can still provide correct and reliable results while improving performance.

[0052] An embodiment of the present application also provides a device for detecting the rationality of connection query optimization, wherein the device includes a memory and a processor, the memory is used to store instructions or codes, and the processor is used to execute the instructions or codes so that the device performs the steps of the method for detecting the rationality of connection query optimization described in any embodiment of the present application.

[0053] In practical applications, the computer-readable storage medium may be any combination of one or more computer-readable media, which may be a computer-readable signal medium or a computer-readable storage medium.

[0054] The computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination thereof. More specific examples of computer-readable storage media (a non-exhaustive list) include: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present embodiment, the computer-readable storage medium may be any tangible medium containing or storing a program that may be used by or in conjunction with an instruction execution system, device, or device.

[0055] Computer-readable signal media may include data signals propagated in baseband or as part of a carrier wave, which carry computer-readable program code. Such propagated data signals may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. Computer-readable signal media may also be any computer-readable medium other than a computer-readable storage medium, which may send, propagate, or transmit a program for use by or in conjunction with an instruction execution system, apparatus, or device.

[0056] The program code embodied on the computer readable medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.

[0057] Computer program code for performing the operations of the present invention may be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, C++, and conventional procedural programming languages ​​such as "C" or similar programming languages. The program code may be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0058] It should be noted that each embodiment in this specification is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the device embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment. The device embodiment described above is merely schematic, in which the unit described as a separate component may or may not be physically separated, and the component prompted as a unit may or may not be a physical unit, that is, it may be located in one place, or it may be distributed on multiple network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the scheme of this embodiment. Ordinary technicians in this field can understand and implement it without paying creative work.

[0059] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any changes or substitutions that can be easily thought of by a person skilled in the art within the technical scope disclosed in the present application should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.

Claims

1. A method for detecting rationality of connection query optimization, characterized in that: include: Generate a database for querying; Generate an original query statement, wherein the original query statement includes at least two connection statements, and the connection statements are used to connect the query plans in the query statement; Performing a query in the database based on the original query statement to obtain a first query result; Optimizing the join statement in the original query statement using query hints and system variables that affect the query plan to obtain a second query statement, wherein the query plan of the second query statement is different from the query plan of the original query statement; Performing a query in the database based on the second query statement to obtain a second query result; A rationality detection result is generated based on the difference between the first query result and the second query result.

2. The method according to claim 1, characterized in that The generating of a database for querying comprises: Generating a first type of result statement based on a random generation method, each of the first type of result statement includes at least one type of query result corresponding to the query question; Mutating the query results in the first type of result statements to obtain second type of result statements; A database for query is generated based on the first-category result statements and the second-category result statements.

3. The method according to claim 1, characterized in that Generating a rationality detection result based on the difference between the first query result and the second query result includes: If the first query result and the second query result are the same, generating a detection result of reasonable connection query optimization; If the first query result and the second query result are different, a detection result of unreasonable connection query optimization is generated.

4. The method according to claim 3, characterized in that The method further comprises: If a detection result that the connection query optimization is unreasonable is generated, the connection statement in the second query statement is optimized to obtain an optimized second query statement; Performing a query in the database based on the optimized second query statement to obtain an optimized second query result; A second rationality detection result is generated based on a difference between the first query result and the optimized second query result.

5. A device for detecting rationality of connection query optimization, characterized in that: include: A first generating module, used to generate a database for query; A second generating module is used to generate an original query statement, wherein the original query statement includes at least two connection statements, and the connection statements are used to connect the query plans in the query statement; A first query module, configured to query the database based on the original query statement to obtain a first query result; An optimization module, configured to optimize the join statement in the original query statement to obtain a second query statement, wherein the query plan of the second query statement is different from the query plan of the original query statement; A second query module, configured to query the database based on the second query statement to obtain a second query result; A rationality detection module is used to generate a rationality detection result based on the difference between the first query result and the second query result.

6. The device according to claim 5, characterized in that The first generating module is specifically used for: Generating a first type of result statement based on a random generation method, each of the first type of result statement includes at least one type of query result corresponding to the query question; Mutating the query results in the first type of result statements to obtain second type of result statements; A database for query is generated based on the first-category result statements and the second-category result statements.

7. The device according to claim 5, characterized in that The rationality detection module is specifically used for: If the first query result and the second query result are the same, generating a detection result of reasonable connection query optimization; If the first query result and the second query result are different, a detection result of unreasonable connection query optimization is generated.

8. The device according to claim 7, characterized in that The device further includes a statement optimization module, which is specifically used to: If a detection result that the connection query optimization is unreasonable is generated, the connection statement in the second query statement is optimized to obtain an optimized second query statement; Performing a query in the database based on the optimized second query statement to obtain an optimized second query result; A second rationality detection result is generated based on a difference between the first query result and the optimized second query result.

9. An electronic device, characterized in that: comprising a memory and a processor, wherein: The memory is used to store the computer program; The processor is used to execute the computer program to implement the method for detecting rationality of connection query optimization according to any one of claims 1 to 4.

10. A computer-readable storage medium, characterized in that: Used to store a computer program, wherein when the computer program is executed by a processor, the method for detecting rationality of connection query optimization according to any one of claims 1 to 4 is implemented.

Images