Complex query optimization method, device and medium

Abstract

This application discloses a complex query optimization method, apparatus, and medium, relating to the field of data query technology. It addresses the problem that current large table query optimization relies excessively on manual labor, and the optimization effect is severely affected by human factors. This application provides a complex query optimization method that pre-queries the database based on the WHERE condition of the SQL query statement to obtain the main table ID set and the total number of rows retrieved. Corresponding optimization identifiers are then generated and stored in redundant tables across various databases along with the aforementioned information, thereby optimizing the SQL query statement. The optimization requires no manual intervention. Compared to current optimization methods where the optimization effect is affected by the technical level of maintenance personnel, this method simplifies complex queries into simpler ones, ensuring optimization results, is unaffected by human factors, and eliminates the need for subsequent repeated debugging and verification, making it better suited to various complex business environments.

Description

Technical Field

[0001] This application relates to the field of data query technology, and in particular to a method, apparatus and medium for optimizing complex queries. Background Technology

[0002] In complex systems, it's common to encounter scenarios where the main table contains a large amount of data and numerous related business tables. In such scenarios, querying the main table for data and related business information inevitably leads to high query frequency and long query times, severely impacting user experience. This problem is prevalent in every business system because each query involves executing two complex queries on the large table: the first to check the total number of rows, and the second to check the paginated data. Each of these complex queries is time-consuming, resulting in a long overall query time.

[0003] Currently, the typical solution is as follows: when users report slow response times on pages displaying business lists, the Structured Query Language (SQL) statements used in the query execution process are analyzed, and execution plans are analyzed to identify optimization points. Optimization methods such as adding relevant indexes or data caching are then employed. Finally, the SQL is debugged to verify whether the query time has improved. Whether query optimization is necessary depends on user feedback, and technical personnel are required to optimize, debug, and verify the SQL statements. This entire process is time-consuming and labor-intensive, and the effectiveness of SQL optimization is heavily influenced by the experience and skills of technical personnel, making it unsuitable for various complex business scenarios.

[0004] Therefore, those skilled in the art urgently need a complex query optimization method to solve the problem that current large table query optimization relies too heavily on human intervention, and the optimization effect is severely affected by human factors, making it unsuitable for various complex business scenarios. Summary of the Invention

[0005] The purpose of this application is to provide a method, apparatus and medium for optimizing complex queries, which solves the problem that current large table query optimization relies too much on human labor and the optimization effect is seriously affected by human factors, and cannot be well applied to various complex business scenarios.

[0006] To address the aforementioned technical problems, this application provides a method for optimizing complex queries, including:

[0007] When an SQL query statement is received, it is determined whether there is an optimization flag in the SQL query statement. The optimization flag is generated in advance by querying the database based on the WHERE condition in the SQL query statement to determine the corresponding set of main table IDs and the total number of queries. The optimization flag in the redundant table has a one-to-one correspondence with the set of main table IDs and the total number of queries.

[0008] If an optimization flag exists, the corresponding set of main table IDs and the total number of records to be queried are obtained from the redundant table based on the optimization flag, and the database is queried based on the main table IDs to determine the pagination data;

[0009] The query results will be returned as the total number of records and the paginated data.

[0010] Preferably, the set of main table IDs and the total number of queries are stored in pairs in the redundant table. Then, based on the optimization identifier, the corresponding set of main table IDs and the total number of queries are obtained from the redundant table, and the database is queried based on the main table IDs to determine the paginated data, including:

[0011] The redundant table is queried based on the optimization identifier to obtain the corresponding set of main table IDs and the total number of rows queried;

[0012] The SQL query statement is broken down to obtain the query pagination SQL statement;

[0013] Replace the WHERE and pagination conditions in the SQL query for pagination with the ID set from the main table;

[0014] The database is queried using a pagination SQL statement to obtain the paginated data.

[0015] Preferably, if the main table ID set and the total number of queries are stored in different locations in the redundant table, then the corresponding main table ID set and total number of queries are obtained from the redundant table based on the optimization identifier, and the database is queried based on the main table ID to determine the paginated data, including:

[0016] The redundant table is queried based on the optimization identifier to obtain the corresponding set of main table IDs;

[0017] The SQL query statement is broken down into the SQL statement for querying the total number of records and the SQL statement for querying pagination;

[0018] The statement that optimizes the query to find the total number of records stored in the redundant table will be used as the new SQL statement for finding the total number of records. The WHERE and pagination conditions in the SQL statement for finding pagination will be replaced with the set of IDs from the main table.

[0019] The system uses SQL statements to query the total number of records and SQL statements to query the pagination, respectively, to query different databases in parallel to obtain the total number of records and pagination data.

[0020] Preferred options also include:

[0021] If no optimization flag is found, the SQL query statement will be decomposed into a total query SQL statement and a pagination query SQL statement.

[0022] The system uses SQL statements to query the total number of records and SQL statements to query the pagination, respectively, to query different databases in parallel to obtain the total number of records and pagination data.

[0023] Preferably, the two databases used to execute different queries in parallel are the first two databases in the database list generated according to preset rules.

[0024] Preferably, the database list is as follows:

[0025] The priority value of each database is determined based on the pre-configured weights and the ranking of the number of database connections; the ranking of the number of database connections is sorted from largest to smallest; the priority value is positively correlated with the pre-configured weights and the ranking of the number of database connections.

[0026] The databases are sorted from highest to lowest priority value to determine the database list.

[0027] Preferably, after returning the total number of queries and paginated data as query results, the method also includes:

[0028] Record the optimization flags, SQL query statements, query parameters, and query time for this query process.

[0029] Preferred options also include:

[0030] Based on the preset optimization cycle, periodically query the SQL query statements and query time of all query processes within the optimization cycle;

[0031] Identify the query processes whose query time exceeds a preset threshold, and call the execution plan analysis function to optimize the query process and obtain optimization points;

[0032] Send the optimization points to the operations and maintenance platform.

[0033] Preferably, after obtaining the optimization point, the following steps are also included:

[0034] Based on query time and optimization points, determine the optimization priority of the corresponding SQL query statement.

[0035] To address the aforementioned technical problems, this application also provides a complex query optimization apparatus, comprising:

[0036] The identifier judgment module is used to determine whether there is an optimization identifier in the SQL query statement when an SQL query statement is received. The optimization identifier is generated in advance by querying the database according to the WHERE condition in the SQL query statement to determine the corresponding main table ID set and the total number of queries. The optimization identifier in the redundant table has a one-to-one correspondence with the main table ID set and the total number of queries.

[0037] The data query module is used to obtain the corresponding set of main table IDs and the total number of records to be queried from the redundant table if an optimization identifier exists, and then query the database based on the main table IDs to determine the pagination data.

[0038] The results return module is used to return the total number of queries and the paginated data as query results.

[0039] Preferred options also include:

[0040] The basic optimization module is used to decompose the SQL query statement if no optimization flag exists, resulting in a query statement for the total number of rows and a query statement for pagination. The total number of rows and pagination SQL statements are then used to query different databases in parallel to obtain the total number of rows and pagination data.

[0041] The database list acquisition module is used to determine the priority value of each database based on the pre-configured weights and the ranking of the number of database connections. The ranking of the number of database connections is sorted from largest to smallest. The priority value is positively correlated with the pre-configured weights and the ranking of the number of database connections. The databases are sorted from largest to smallest according to their priority values ​​to determine the database list.

[0042] The query record module is used to record the optimization flags, SQL query statements, query parameters, and query time of this query process.

[0043] The periodic optimization module is used to periodically query the SQL query statements and query times of all query processes within a preset optimization period; identify the query processes whose query times exceed a preset threshold, and call the execution plan analysis function to perform optimization analysis on the query process to obtain optimization points; and send the optimization points to the operation and maintenance platform.

[0044] The optimization priority determination module is used to determine the optimization priority of the corresponding SQL query statement based on the query time and optimization points.

[0045] To address the aforementioned technical problems, this application also provides a complex query optimization apparatus, comprising:

[0046] Memory, used to store computer programs;

[0047] A processor is used to implement complex query optimization methods as described above when executing computer programs.

[0048] To address the aforementioned technical problems, this application also provides a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the steps of the complex query optimization method described above.

[0049] This application provides a complex query optimization method that pre-queries the database based on the WHERE condition of the SQL query statement to obtain the main table ID set and the total number of rows retrieved. A corresponding optimization identifier is then generated and stored in redundant tables across various databases along with this information, thereby optimizing the SQL query statement. When an SQL query statement carrying an optimization identifier is received, it indicates that the SQL query statement has been optimized. The optimization identifier can be used to query any redundant table in the database to obtain the corresponding main table ID set and the total number of rows retrieved. The total number of rows retrieved is also one of the results of two complex queries on the large table. Regarding the paginated data of the other complex query result, the main table ID set is used to replace the WHERE condition in the original SQL query statement to retrieve the data, thus simplifying the complex query with a WHERE condition into a simple query without a WHERE condition, resulting in higher query efficiency and shorter query time. Furthermore, the above-described query optimization method eliminates the need for manual optimization of SQL queries by adding relevant indexes or data caching, saving significant manpower costs. Furthermore, current optimization methods, such as manually adding relevant indexes or data caching, are subject to the technical skill level of operations and maintenance personnel, and their effectiveness is uncertain. They also require repeated debugging and verification of the optimized SQL queries. This method, however, optimizes by simplifying both the complex queries for obtaining the total number of rows and paginated data into simpler queries. The total number of rows can be directly obtained from the redundant table based on the optimization identifier, and the paginated data query uses the main table's ID set instead of the WHERE clause in the original SQL query. This ensures effective optimization, is unaffected by human factors, eliminates the need for repeated debugging and verification, and better adapts to various complex business environments.

[0050] The complex query optimization apparatus and computer-readable storage medium provided in this application correspond to the above-described method and have the same effect. Attached Figure Description

[0051] To more clearly illustrate the embodiments of this application, the accompanying drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0052] Figure 1 A flowchart of a complex query optimization method provided by the present invention;

[0053] Figure 2 A flowchart of a database list priority sorting method provided by the present invention;

[0054] Figure 3 A flowchart of a planning analysis and optimization method provided by the present invention;

[0055] Figure 4 A structural diagram of a complex query optimization device provided by the present invention;

[0056] Figure 5 This is a structural diagram of another complex query optimization device provided by the present invention. Detailed Implementation

[0057] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the protection scope of this application.

[0058] The core of this application is to provide a method, apparatus, and medium for optimizing complex queries.

[0059] To enable those skilled in the art to better understand the present application, the present application will be further described in detail below with reference to the accompanying drawings and specific embodiments.

[0060] In practical applications of business systems, it's common for the main table used for data storage to contain excessively large amounts of data due to the complexity of the system's business logic, and for this main table to be associated with numerous other business tables. For such data tables with a certain data volume (the specific value is determined by the hardware), query speeds become extremely slow. Furthermore, when the business system queries large tables, it typically requires two complex SQL queries: the first to retrieve the total number of rows, and the second to retrieve the paginated data. Each of these complex queries is time-consuming, contributing to the overall long query time.

[0061] To address this issue, the current approach typically involves optimizing the SQL statement. When user feedback indicates slow response times for querying the business list page, execution plan analysis is performed on the SQL statement corresponding to the user's reported query to identify optimization points. This execution plan analysis is usually performed using a function tool. Once the technical staff has identified the optimization points, they can optimize the SQL statement for each point by adding relevant indexes or improving data caching, and then debug the SQL to verify whether the query time has been improved.

[0062] The existing optimization process described above first requires user feedback on which query needs optimization, i.e., which SQL statement requires optimization. Then, technical personnel are needed to analyze, optimize, debug, and verify this SQL statement. The entire process is manpower-intensive, time-consuming, and the optimization effect is overly dependent on the expertise of the technical personnel, making it difficult to guarantee. This negatively impacts both the control of business system maintenance costs and the system's ability to be applied in more complex environments.

[0063] Based on the above problems, this embodiment provides a method for optimizing complex queries, such as... Figure 1 Shown, including:

[0064] S11: When an SQL query statement is received, determine whether there is an optimization flag in the SQL query statement.

[0065] The optimization identifier is generated in advance by querying the database based on the WHERE condition in the SQL query statement to determine the corresponding set of identity document (ID) numbers in the main table and the total number of queries. It is generated when the set of ID numbers in the main table and the total number of queries are saved to the redundant table. There is a one-to-one correspondence between the optimization identifier in the redundant table and the set of ID numbers in the main table and the total number of queries.

[0066] The set of main table IDs and the total number of rows retrieved, pre-stored in the redundancy table, constitute the optimization strategy for this SQL query. The optimization strategy is obtained by querying the database using the WHERE clause of the SQL query to determine the corresponding set of main table IDs and the total number of rows retrieved, thus obtaining the optimization strategy for that SQL query and storing it in the redundancy table. To distinguish which SQL query corresponds to which optimization strategy in the redundancy table, an optimization identifier is used in conjunction with storing the set of main table IDs and the total number of rows retrieved. The optimization identifier is a unique representation generated corresponding to a single SQL query, and it is stored in the redundancy table along with the set of main table IDs and the total number of rows retrieved.

[0067] It should be noted that business system databases are typically deployed in a cluster, meaning there is more than one database. The redundant table mentioned above is a type of data table whose data is redundant across various databases; hence, it is called a redundant table, meaning the redundant table exists in every database.

[0068] Additionally, it should be noted that while there is a one-to-one correspondence between the main table ID set and the total number of queries, this does not mean that there must be a direct correspondence between them (i.e., one set of data must be able to directly find the other). Both the main table ID set and the total number of queries are retrieved from the redundant table based on the optimization identifier, thereby reducing the complexity of the redundant table's data structure. Furthermore, the main table ID set and the total number of queries can be stored in groups in the redundant table, meaning that finding one set of data through the optimization identifier will find the other; alternatively, the main table ID set and the total number of queries can be stored in different locations within the redundant table, meaning that finding all data requires two queries using the optimization identifier. This application does not impose any restrictions on this, and a suitable storage method can be chosen according to actual needs.

[0069] S12: If an optimization flag exists, obtain the corresponding set of main table IDs and the total number of records to be queried from the redundant table based on the optimization flag, and query the database based on the main table IDs to determine the pagination data.

[0070] As shown above, redundant tables can be queried from any database. Therefore, by optimizing the identifier to query the set of main table IDs and the total number of rows in the redundant table, any database can be selected for the query.

[0071] In short, a complex query is one that includes a WHERE clause in its SQL statement, while a simple query is one that does not. Therefore, querying the database using the main table ID obtained through the above steps can replace the WHERE clause in the original SQL query, enabling the retrieval of paginated data and simplifying a complex query into a simple one, thus improving query efficiency. Similarly, directly querying the total number of rows and the main table ID set from the redundant table based on the optimization flag is also a simple query and will not incur excessive time consumption.

[0072] S13: Return the total number of records and paginated data as the query results.

[0073] In other words, the total number of queries and pagination data obtained above are summarized and returned to the querying party (user) as the total query result.

[0074] Furthermore, in practical applications, there may be situations where query optimization using the complex query optimization method provided in this application is unnecessary, such as when data is lost in redundant tables or when performing self-checks on the business system. In such cases, this embodiment provides an optional implementation scheme as follows:

[0075] The aforementioned complex query optimization methods are triggered by specific annotations.

[0076] In other words, when an SQL query contains a predefined specific annotation, the aforementioned complex query optimization method is used to optimize the SQL query.

[0077] This application provides a complex query optimization method to specifically address the problem of excessively long data query times caused by the large amount of data in the main table of a business system. By pre-querying the database based on the WHERE condition of the SQL query to be optimized, a set of corresponding main table IDs and the total number of rows retrieved are obtained, and these are stored in a redundant table along with an optimization identifier. When the query process corresponding to the SQL query is triggered again, the corresponding set of main table IDs and the total number of rows retrieved can be obtained from the redundant table through the optimization identifier. The total number of rows retrieved is directly the result of one of the two complex queries, and the other complex query can also be performed using the set of main table IDs, thus simplifying two complex queries into a simple query and optimizing query time. The complex query optimization method provided in this application requires no manual intervention, and compared to current optimization methods that rely on manually adding relevant indexes or data caching, the optimization effect is not dependent on the professional skills of technical personnel, ensuring a more reliable optimization result. It also eliminates the need for repeated debugging and verification, saving significant manpower and resources and reducing operational costs.

[0078] As can be seen from the above embodiments, this application does not impose any restrictions on the storage method of the main table ID set and the total number of query records in the redundant table. However, it should be noted that the subsequent queries corresponding to different storage methods are slightly different. Therefore, in order to more clearly illustrate the complex query optimization method provided by this application, the subsequent query steps corresponding to the two different storage methods are further explained below:

[0079] 1) The main table ID set and the total number of rows retrieved are stored in pairs in a redundant table:

[0080] In this scenario, step S12, which involves retrieving the main table ID set and querying the total number of rows from the redundant table using optimized identifiers, can be completed in one step. Specifically, step S12 is as follows:

[0081] S121-a: Query the redundant table based on the optimization identifier to obtain the corresponding set of main table IDs and the total number of rows queried.

[0082] S122-a: Decompose the SQL query statement to obtain the query pagination SQL statement.

[0083] Since step S121-a directly obtained the total number of queries through the optimization identifier, one of the original two complex queries has already been completed. That is, there is still a complex query for paginated data that has not been completed. Therefore, the SQL statement concerning the paginated data part of the SQL query statement can be decomposed.

[0084] S123-a: Replace the WHERE and pagination conditions in the SQL statement for querying pagination with the main table ID set.

[0085] This step aims to simplify complex queries containing WHERE conditions into simpler queries.

[0086] S124-a: Query the database using a pagination SQL statement to obtain paginated data.

[0087] It is easy to understand that, similar to the above embodiments, when querying the database, there is no restriction on querying a specific one or several databases; any database can be selected for querying to obtain paginated data.

[0088] 2) The main table's ID set and the total number of rows retrieved are stored in different locations in the redundant table:

[0089] In this scenario, optimizing the identifier requires two queries from the redundant table to obtain the main table ID set and the total number of rows retrieved, respectively. Therefore, step S12 specifically involves:

[0090] S121-b: Query the redundant table based on the optimization identifier to obtain the corresponding set of main table IDs.

[0091] S122-b: Decompose the SQL query statement to obtain the SQL statement for querying the total number of rows and the SQL statement for querying pagination.

[0092] Since step S121-b only obtains the main table ID set through querying this redundant database, the total number of rows and pagination data required for the query results are not obtained, and two more queries are needed. Therefore, the SQL query statement is decomposed into a query statement for the total number of rows and a query statement for pagination.

[0093] S123-b: The statement that queries the total number of rows stored in the redundant table by optimizing the identifier is used as the new SQL statement for querying the total number of rows, and the WHERE condition and pagination condition in the SQL statement for querying pagination are replaced with the set of IDs in the main table.

[0094] In other words, the original SQL statement for querying the total number of rows will be replaced by a statement that retrieves the corresponding total number of rows from the redundant table based on the optimization identifier. For the SQL statement for querying pagination, the same as in the above embodiment will be used, replacing the original WHERE condition and pagination condition with the ID set of the main table.

[0095] S124-b: The SQL statements for querying the total number of records and pagination are executed in parallel on different databases to obtain the total number of records and pagination data.

[0096] Since step S123-b decomposes a complete query process into two SQL query statements, that is, into two subqueries, and the two subqueries are independent of each other and do not affect each other, the query can be performed in parallel to improve the query efficiency by nearly double.

[0097] It's important to note that the idea behind parallel queries is to break down a large table query into two subqueries: one to retrieve the total number of rows and the other to retrieve the paginated data. Specifically, retrieving the total number of rows involves only one query process: a) obtaining the total number of rows by querying the redundant table using optimized identifiers; while retrieving the paginated data involves two query processes: b) obtaining the set of main table IDs by querying the redundant table using optimized identifiers, and c) querying the database using the set of main table IDs to retrieve the paginated data.

[0098] In the scenario corresponding to Example 1) above, a) obtaining the total number of queries by querying the redundant table through optimized identifier and b) obtaining the set of main table IDs by querying the redundant table through optimized identifier are completed in one step. Without obtaining the set of main table IDs, the subsequent step of obtaining paginated data cannot be performed. Therefore, there is a strong correlation between the steps, and efficiency cannot be optimized through parallel processing. However, simplifying the two queries of the redundant table into one query is also an optimization of query efficiency.

[0099] In the scenario corresponding to Example 1), the steps of obtaining the total number of queries and obtaining pagination data are completely independent and have no mandatory order. They can be completed through parallel processing. The parallel processing between a) and c) in the above embodiment is only one possible implementation. It can also be parallel processing between a) and b), or parallel processing between a) and b) and c). This application does not limit this.

[0100] This embodiment provides a possible implementation method for subsequent steps, considering different storage methods of the main table ID set and the total number of rows queried in the redundant table. Furthermore, parallel processing is used to further improve the query efficiency of subsequent steps, enabling the business system to perform large table queries more quickly and providing a better user experience.

[0101] Apart from the cases where optimization flags exist as mentioned in the above embodiments, for cases where optimization flags do not exist, i.e., the current SQL query statement is not optimized, this embodiment provides a preferred implementation scheme, such as... Figure 1 As shown, the above method also includes:

[0102] S14: If no optimization flag is found, the SQL query statement will be decomposed to obtain the SQL statement for querying the total number of rows and the SQL statement for querying pagination.

[0103] S15: Perform parallel queries on different databases using SQL statements for total number of records and SQL statements for pagination to obtain the total number of records and pagination data.

[0104] Similar to the above embodiments, this embodiment provides a basic optimization process when there is no optimization flag, that is, the original two complex queries are separated into SQL query statements and queried separately in parallel to obtain nearly double the query efficiency.

[0105] Similarly, this embodiment does not restrict the selection of databases used for two different queries; any database can be selected. However, to further improve query efficiency, a preferred implementation method is also provided:

[0106] The two databases used to execute different queries in parallel are the first two databases in the database list generated according to preset rules.

[0107] It's easy to see that different databases, with varying performance and stored data volumes, may result in different execution times for the same query. Therefore, operations and maintenance personnel can sort the databases according to preset rules based on actual conditions to select the two optimal databases for the aforementioned parallel queries, thereby maximizing query efficiency.

[0108] It should be noted that this embodiment does not impose any restrictions on the specific correspondence between the selected two databases and the two parallel queries mentioned above. The process of determining which of the two parallel queries takes longer and thus allocating the optimal database to it is too difficult to implement, and the resulting improvement in query efficiency is too insignificant, even less than the time spent on the determination process. Therefore, this embodiment does not require a further correspondence between the two databases and the two parallel queries.

[0109] Furthermore, based on the above embodiments, this embodiment also provides a preferred scheme for preset rules, such as... Figure 2 As shown, the specific process of obtaining the database list according to the preset rules is as follows:

[0110] S21: Determine the priority value of each database based on the pre-configured weights of the databases and the ranking of the number of database connections.

[0111] The database connection count is ranked from largest to smallest; the priority value is positively correlated with the pre-configured weight and the database connection count ranking.

[0112] It should be noted that a preferred implementation method is to determine the priority value through multiplication.

[0113] S22: Sort the databases according to their priority values ​​from largest to smallest to determine the database list.

[0114] Generally, database performance cannot be directly obtained; it requires manual configuration by operations and maintenance personnel, i.e., setting corresponding weights. Databases with better performance have higher weights. Regarding the number of database connections, generally, more connections mean longer query times. Therefore, when the priority value is positively correlated with the ranking of database connection counts, the database connection counts should be sorted from largest to smallest. In other words, databases with shorter query times rank lower, and higher ranking values ​​correspond to higher priority values.

[0115] The preferred solution provided in this embodiment prioritizes each database using preset rules, ensuring that databases with shorter query times are selected first, thereby further improving the efficiency of large table queries. Furthermore, a preferred solution based on preset rules is provided, addressing the performance and number of database connections that affect database query efficiency, and offering a quantifiable method for determining priority values, further guaranteeing the optimization of query efficiency.

[0116] Furthermore, this embodiment also provides a preferred implementation scheme, in which, after returning the total number of queries and pagination data as query results, such as... Figure 1 As shown, the above-mentioned complex query optimization methods also include:

[0117] S16: Record the optimization flags, SQL query statements, query parameters, and query time for this query process.

[0118] The information recorded in step S16 can be archived to facilitate subsequent analysis and query of optimization effects by maintenance personnel.

[0119] Meanwhile, as can be seen from the above embodiments, the optimization of SQL query statements is performed in advance. That is to say, without human intervention, an SQL query statement can only be optimized after it has been queried once. Moreover, such optimization must be based on the premise of recording and archiving the query process (which must be SQL query statements) as provided in the above embodiments.

[0120] However, simply recording and archiving the query process is insufficient for automatic query optimization. Therefore, this embodiment provides a preferred implementation scheme, such as... Figure 3 As shown, the above method also includes:

[0121] S31: Based on the preset optimization cycle, periodically query the SQL query statements and query times of all query processes within the optimization cycle.

[0122] It is easy to understand that this embodiment is not limited to a specific value of the optimization period, and can be 1 day, 1 week, 1 month or other time periods.

[0123] S32: Identify the query process whose query time exceeds a preset threshold, and call the execution plan analysis function to optimize the query process and obtain optimization points.

[0124] S33: Send the optimization points to the operation and maintenance platform.

[0125] As clearly stated in the above embodiments, the execution plan analysis of SQL query statements is implemented using existing function tools, so it will not be elaborated here. After the execution plan analysis is completed, optimization points such as the table index to be optimized and the WHERE conditions of the SQL to be optimized can be obtained. These points can be sent to the operation and maintenance platform via email, push, SMS, etc., so that operation and maintenance personnel can optimize the database and table of the SQL query statements accordingly.

[0126] It is easy to understand that after determining the query process whose query time exceeds the preset threshold in step S32, the trigger condition for optimizing the corresponding SQL query statement is also the complex query optimization method provided in the above embodiment. That is:

[0127] Identify the query processes whose query time exceeds a preset threshold, obtain the WHERE condition of the corresponding SQL query statement, further obtain the total number of queries and the set of main table IDs, and store them in a redundant table along with optimization flags.

[0128] This embodiment archives the optimization effects of each query process by recording the optimization identifier, SQL query statement, query parameters, and query time, facilitating subsequent analysis by maintenance personnel. Simultaneously, some archived data can be used for execution plan analysis. After identifying optimization points, these are sent to maintenance personnel for further database and table optimization, improving query efficiency from different perspectives. Furthermore, the archived query time can serve as a trigger condition for optimization strategies in the aforementioned optimization method. When the query time exceeds a preset threshold, the corresponding SQL query statement is processed using the method described above to obtain the total number of rows retrieved and the main table ID set, which, along with the optimization identifier, are stored in a redundant table. When the next query process arrives, the aforementioned complex query optimization method can be used to optimize the query and reduce query time.

[0129] It is easy to understand that the above-mentioned optimization method may identify multiple query processes to be optimized in each cycle. However, the energy of the operation and maintenance personnel is limited, and it is impossible to optimize all query processes at the same time. Therefore, there is a need to further determine the priority of query process optimization.

[0130] In this regard, this embodiment provides a preferred solution, such as... Figure 3 As shown, after obtaining the optimization point, the following steps are also included:

[0131] S34: Determine the optimization priority of the corresponding SQL query statement based on the query time and optimization points.

[0132] It should be noted that this preferred solution determines the optimization priority based on both the optimization requirements and the degree of optimization in the query process.

[0133] The time taken to query reflects the urgency of optimizing the query process; the longer the query takes, the more optimization is needed.

[0134] The optimization points reflect the degree of optimization of the SQL query statement corresponding to the query process. The more optimization points there are, the lower the degree of optimization of the SQL query statement. The higher the query efficiency can be improved by the operation and maintenance personnel through database and table optimization, and the more inclined they are to optimize the query process.

[0135] Therefore, this embodiment comprehensively determines the optimization priority of each query process through these two aspects, providing guidance for operation and maintenance personnel, and making their optimization of the query process more in line with actual engineering needs.

[0136] Furthermore, by statistically analyzing the number of archived records for a query process within the current period, we can obtain data on its execution count, which also reflects the optimization needs of the query process to some extent. A query process with a higher execution count indicates a more frequently used query by the user, making its optimization more important. Therefore, it can also serve as a factor in determining the optimization priority mentioned above. Similarly, depending on the actual needs, other data can be used as the basis for determining the optimization priority of query processes; this embodiment does not impose any restrictions on this.

[0137] The preferred solution provided in this embodiment determines the optimization priority of the query process by comprehensively considering both the optimization requirements and the degree of optimization of the query process through query time and optimization points. This provides guidance for operation and maintenance personnel when performing subsequent database and table optimization, making the optimization process more in line with actual engineering needs and better ensuring the user experience.

[0138] The above embodiments have described a complex query optimization method in detail. This application also provides an embodiment of a complex query optimization device. It should be noted that this application describes the device embodiment from two perspectives: one based on functional modules and the other based on hardware.

[0139] From the perspective of functional modules, such as Figure 4 As shown, this embodiment provides a complex query optimization device, including:

[0140] The identifier judgment module 41 is used to determine whether there is an optimization identifier in the SQL query statement when the SQL query statement is received. The optimization identifier is generated in advance by querying the database according to the WHERE condition in the SQL query statement to determine the corresponding main table ID set and the total number of queries. The optimization identifier in the redundant table has a one-to-one correspondence with the main table ID set and the total number of queries.

[0141] The data query module 42 is used to obtain the corresponding set of main table IDs and the total number of records to be queried from the redundant table if there is an optimization identifier, and to query the database based on the main table IDs to determine the pagination data.

[0142] The result return module 43 is used to return the total number of queries and the paginated data as query results.

[0143] Preferred options also include:

[0144] The basic optimization module is used to decompose the SQL query statement if no optimization flag exists, resulting in a query statement for the total number of rows and a query statement for pagination. The total number of rows and pagination SQL statements are then used to query different databases in parallel to obtain the total number of rows and pagination data.

[0145] The database list acquisition module is used to determine the priority value of each database based on the pre-configured weights and the ranking of the number of database connections. The ranking of the number of database connections is sorted from largest to smallest. The priority value is positively correlated with the pre-configured weights and the ranking of the number of database connections. The databases are sorted from largest to smallest according to their priority values ​​to determine the database list.

[0146] The query record module is used to record the optimization flags, SQL query statements, query parameters, and query time of this query process.

[0147] The periodic optimization module is used to periodically query the SQL query statements and query times of all query processes within a preset optimization period; identify the query processes whose query times exceed a preset threshold, and call the execution plan analysis function to perform optimization analysis on the query process to obtain optimization points; and send the optimization points to the operation and maintenance platform.

[0148] The optimization priority determination module is used to determine the optimization priority of the corresponding SQL query statement based on the query time and optimization points.

[0149] Since the embodiments of the apparatus and the embodiments of the method correspond to each other, please refer to the description of the embodiments of the method for the embodiments of the apparatus, which will not be repeated here.

[0150] This embodiment provides a complex query optimization device that pre-queries the database based on the WHERE condition of the SQL query to be optimized, obtaining the corresponding set of main table IDs and the total number of rows retrieved. These are stored in a redundant table along with an optimization identifier. When the query process corresponding to the SQL query is triggered again, the data query module retrieves the corresponding set of main table IDs and the total number of rows retrieved from the redundant table based on the optimization identifier. The total number of rows retrieved is directly the result of one of the two complex queries, and the other complex query can also be performed using the set of main table IDs. This simplifies the two complex queries into a simple query, thereby optimizing the query process time. This complex query optimization device requires no manual intervention, and compared to current optimization methods that rely on manually adding indexes or data caching, the optimization effect is not dependent on the expertise of technical personnel, ensuring a more reliable optimization result. It also eliminates the need for repeated debugging and verification, saving significant manpower and resources and reducing maintenance costs.

[0151] Figure 5 A structural diagram of a complex query optimization device provided in another embodiment of this application is shown below. Figure 5 As shown, a complex query optimization device includes: a memory 50 for storing computer programs;

[0152] The processor 51 is used to implement the steps of a complex query optimization method as described in the above embodiments when executing a computer program.

[0153] The complex query optimization device provided in this embodiment may include, but is not limited to, smartphones, tablets, laptops, or desktop computers.

[0154] The processor 51 may include one or more processing cores, such as a quad-core processor or an octa-core processor. The processor 51 may be implemented using at least one of the following hardware forms: Digital Signal Processor (DSP), Field-Programmable Gate Array (FPGA), or Programmable Logic Array (PLA). The processor 51 may also include a main processor and a coprocessor. The main processor, also known as the Central Processing Unit (CPU), is used to process data in the wake-up state; the coprocessor is a low-power processor used to process data in the standby state. In some embodiments, the processor 51 may integrate a Graphics Processing Unit (GPU), which is responsible for rendering and drawing the content to be displayed on the screen. In some embodiments, the processor 51 may also include an Artificial Intelligence (AI) processor, which is used to handle computational operations related to machine learning.

[0155] The memory 50 may include one or more computer-readable storage media, which may be non-transitory. The memory 50 may also include high-speed random access memory and non-volatile memory, such as one or more disk storage devices or flash memory devices. In this embodiment, the memory 50 is used to store at least the following computer program 501, which, after being loaded and executed by the processor 51, is capable of implementing the relevant steps of a complex query optimization method disclosed in any of the foregoing embodiments. In addition, the resources stored in the memory 50 may also include an operating system 502 and data 503, and the storage method may be temporary or permanent storage. The operating system 502 may include Windows, Unix, Linux, etc. The data 503 may include, but is not limited to, a complex query optimization method.

[0156] In some embodiments, a complex query optimization device may further include a display screen 52, an input / output interface 53, a communication interface 54, a power supply 55, and a communication bus 56.

[0157] Those skilled in the art will understand that Figure 5 The structure shown does not constitute a limitation on a complex query optimization apparatus and may include more or fewer components than illustrated.

[0158] This application provides a complex query optimization apparatus, including a memory and a processor. When the processor executes a program stored in the memory, it can implement the following method: a complex query optimization method.

[0159] This embodiment provides a complex query optimization device that uses a processor to execute a computer program stored in memory. This program pre-queries the database based on the WHERE conditions of the SQL query to be optimized, obtaining a set of corresponding main table IDs and the total number of rows retrieved. These are then stored in a redundant table along with an optimization identifier. When the query process corresponding to the SQL query is triggered again, the data query module retrieves the corresponding set of main table IDs and the total number of rows retrieved from the redundant table based on the optimization identifier. The total number of rows retrieved is directly the result of one of the two complex queries, and the other complex query can also be performed using the set of main table IDs. This simplifies the two complex queries into a simple query, thereby optimizing the query process time. This complex query optimization device requires no manual intervention. Compared to current optimization methods that rely on manually adding indexes or data caching, the optimization effect is not dependent on the expertise of technical personnel, ensuring a more reliable optimization result. It also eliminates the need for repeated debugging and verification, saving significant manpower and resources and reducing maintenance costs.

[0160] Finally, this application also provides an embodiment corresponding to a computer-readable storage medium. The computer-readable storage medium stores a computer program, which, when executed by a processor, implements the steps described in the above method embodiments.

[0161] It is understood that if the methods in the above embodiments are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and executes all or part of the steps of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0162] This embodiment provides a computer-readable storage medium. When the computer program stored therein is executed, it can pre-query the database based on the WHERE condition of the SQL query statement to be optimized, obtaining the corresponding set of main table IDs and the total number of rows retrieved. This set, along with an optimization identifier, is stored in a redundant table. When the query process corresponding to the SQL query statement is triggered again, the data query module can retrieve the corresponding set of main table IDs and the total number of rows retrieved from the redundant table based on the optimization identifier. The total number of rows retrieved is directly the result of one of the two complex queries, and the other complex query can also be performed using the set of main table IDs. This simplifies the two complex queries into a simple query, thereby optimizing the query process time. This method requires no manual intervention, and compared to current optimization methods that rely on manually adding relevant indexes or data caching, the optimization effect is not dependent on the professional skills of technical personnel, ensuring a more reliable optimization result. It also eliminates the need for repeated debugging and verification, saving significant manpower and resources and reducing maintenance costs.

[0163] The foregoing has provided a detailed description of a complex query optimization method, apparatus, and medium provided in this application. The various embodiments in the specification are described in a progressive manner, with each embodiment focusing on its differences from other embodiments. Similar or identical parts between embodiments can be referred to interchangeably. For the apparatus disclosed in the embodiments, since it corresponds to the method disclosed in the embodiments, the description is relatively simple; relevant parts can be referred to in the method section. It should be noted that those skilled in the art can make several improvements and modifications to this application without departing from the principles of this application, and these improvements and modifications also fall within the protection scope of the claims of this application.

[0164] It should also be noted that, in this specification, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

Claims

1. A method for optimizing complex queries, characterized in that, include: When an SQL query statement is received, it is determined whether there is an optimization identifier in the SQL query statement. The optimization identifier is generated in advance by querying the database according to the WHERE condition in the SQL query statement to determine the corresponding set of main table IDs and the total number of queries. The optimization identifier in the redundancy table has a one-to-one correspondence with the set of main table IDs and the total number of queries. If the optimization identifier exists, the corresponding set of main table IDs and the total number of queries are obtained from the redundancy table based on the optimization identifier, and the database is queried based on the main table IDs to determine the paginated data. Wherein, when the set of main table IDs and the total number of queries are stored in pairs in the redundancy table, obtaining the corresponding set of main table IDs and the total number of queries from the redundancy table based on the optimization identifier, and querying the database based on the main table IDs to determine the paginated data, includes: querying the redundancy table based on the optimization identifier to obtain the corresponding set of main table IDs and the total number of queries; decomposing the SQL query statement to obtain a paginated query SQL statement; replacing the WHERE condition and pagination condition in the paginated query SQL statement with the set of main table IDs; and querying the database using the paginated query SQL statement to obtain the paginated data. The total number of queries and the paginated data are returned as the query results; Record the optimization flags, SQL query statements, query parameters, and query time for this query process; This method also includes: According to a preset optimization period, periodically query the SQL query statements and query times of all query processes within the optimization period; The query process that takes longer than a preset threshold is identified, and the execution plan analysis function is invoked to optimize the query process and obtain optimization points. The optimization points are sent to the operation and maintenance platform.

2. The complex query optimization method according to claim 1, characterized in that, When the main table ID set and the total number of queries are stored in different locations in the redundant table, the process of obtaining the corresponding main table ID set and the total number of queries from the redundant table based on the optimization identifier, and querying the database based on the main table ID to determine the pagination data includes: The redundant table is queried according to the optimization identifier to obtain the corresponding set of main table IDs; The SQL query statement is decomposed into a query statement for the total number of records and a query statement for pagination. The statement that queries the total number of records stored in the redundant table using the optimized identifier is used as the new SQL statement for querying the total number of records, and the WHERE condition and pagination condition in the query pagination SQL statement are replaced with the main table ID set; The total number of records and the pagination data are obtained by querying different databases in parallel using the SQL statement for querying the total number of records and the SQL statement for querying pagination, respectively.

3. The complex query optimization method according to claim 1, characterized in that, Also includes: If the optimization identifier does not exist, the SQL query statement is decomposed to obtain the SQL statement for querying the total number of records and the SQL statement for querying pagination. The total number of records and the pagination data are obtained by querying different databases in parallel using the SQL statement for querying the total number of records and the SQL statement for querying pagination, respectively.

4. The complex query optimization method according to claim 2 or 3, characterized in that, The two databases used to execute different queries in parallel are the first two databases in the database list generated according to preset rules.

5. The complex query optimization method according to claim 4, characterized in that, The database list is as follows: The priority value of each database is determined based on the pre-configured weights and the ranking of the number of database connections; wherein the ranking of the number of database connections is sorted from largest to smallest; the priority value is positively correlated with the pre-configured weights and the ranking of the number of database connections. The databases are sorted from highest to lowest priority value to determine the database list.

6. The complex query optimization method according to claim 1, characterized in that, After obtaining the optimized point, the following is also included: Based on the query time and the optimization points, the optimization priority of the corresponding SQL query statement is determined.

7. A complex query optimization device, characterized in that, include: The identifier determination module is used to determine whether there is an optimization identifier in the SQL query statement when an SQL query statement is received. The optimization identifier is generated in advance by querying the database according to the WHERE condition in the SQL query statement to determine the corresponding set of main table IDs and the total number of queries, and is generated when the set of main table IDs and the total number of queries are saved to the redundancy table. There is a one-to-one correspondence between the optimization identifier in the redundancy table and the set of main table IDs and the total number of queries. The data query module is used to, if the optimization identifier exists, obtain the corresponding set of main table IDs and the total number of queries from the redundant table based on the optimization identifier, and query the database based on the main table IDs to determine the paginated data; wherein, when the set of main table IDs and the total number of queries are stored in pairs in the redundant table, obtaining the corresponding set of main table IDs and the total number of queries from the redundant table based on the optimization identifier, and querying the database based on the main table IDs to determine the paginated data includes: querying the redundant table based on the optimization identifier to obtain the corresponding set of main table IDs and the total number of queries; decomposing the SQL query statement to obtain a paginated query SQL statement; replacing the WHERE condition and pagination condition in the paginated query SQL statement with the set of main table IDs; and querying the database using the paginated query SQL statement to obtain the paginated data; The result return module is used to return the total number of query results and the pagination data as query results; The query record module is used to record the optimization identifier, the SQL query statement, query parameters, and query time of this query process; The periodic optimization module is used to periodically query the SQL query statements and query times of all query processes within the preset optimization period; identify the query processes whose query times exceed a preset threshold, and call the execution plan analysis function to perform optimization analysis on the query processes to obtain optimization points; and send the optimization points to the operation and maintenance platform.

8. A complex query optimization device, characterized in that, include: memory for storing computer programs; A processor for executing the computer program to implement the steps of the complex query optimization method as described in any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, implements the steps of the complex query optimization method as described in any one of claims 1 to 6.

Images