Exist-related subquery optimization method based on MPP database and multiple correlation conditions

Abstract

The application provides an EXISTS related subquery optimization method based on a multi-association condition of an MPP database, relates to the technical field of database query optimization, and comprises the following steps: after obtaining a SQL query statement to be optimized, firstly, conditions in an EXISTS related subquery statement of the SQL query statement are extracted; then, the row repetition rate of each condition is calculated to filter out duplicate keys; and finally, the original SQL query statement is rewritten based on the duplicate keys to obtain an optimized SQL query statement. The method directly embeds the duplicate removal operation in the query rewriting process, so that the system can effectively eliminate duplicate data and avoid repeated calculation. Compared with the traditional global duplicate removal method, the method can significantly reduce the calculation complexity and resource consumption of the database system while ensuring the accuracy of the query result, thereby greatly improving the query efficiency.

Description

Technical Field

[0001] This invention relates to the field of database query optimization technology, and in particular to an optimization method for EXISTS correlated subqueries with multiple association conditions based on MPP databases. Background Technology

[0002] In the field of database query optimization, EXISTS correlated subqueries with multiple join conditions are a common semi-join operation, primarily used to check whether a record in the main query exists in the result set of the subquery. Taking an EXISTS correlated subquery with five join conditions as an example, its basic form is as follows:

[0003] SELECT * FROM a WHERE EXISTS

[0004] (SELECT 1 FROM b

[0005] WHERE a.a1 = b.b1

[0006] AND a.a2 = b.b2

[0007] AND a.a3 = b.b3

[0008] AND a.a4 = b.b4

[0009] AND a.a5 = b.b5)

[0010] When the target table b contains hundreds of millions or even billions of records, the condition columns b1, b2, b3, b4, and b5 may have a high repetition rate, especially when the values ​​of these columns are unevenly distributed. This can lead to duplicate calculations of multiple matching results. Since the query engine needs to perform multiple repeated subquery matches on each row, the number of duplicate matches increases exponentially with the amount of data and the repetition rate. This results in a sharp increase in memory and I / O pressure, which may ultimately lead to a significant increase in query response time, or even database service unavailability or system crashes.

[0011] Traditional optimization methods typically perform global deduplication on all condition columns b1 to b5 in table b. While this method theoretically guarantees semantic correctness, it ignores the differences in data distribution and correlations between different columns. When the repetition rate of some columns (such as b3 and b4) is extremely low, deduplication not only fails to reduce matching costs but also introduces additional sorting and hashing overhead. When highly repetitive columns (such as b1 and b2) dominate the matching cost, excessive grouping can mask the optimization potential, preventing the query plan from fully utilizing the statistical characteristics of the data. Therefore, traditional global deduplication strategies exhibit significant computational resource waste and scalability bottlenecks in multi-key related scenarios. To address this issue, there is an urgent need for a subquery scheme that can identify high-repetition-value features and adaptively optimize based on column correlations. This would allow the EXISTS semi-join to effectively reduce duplicate matching, lower resource consumption, and improve overall query performance while maintaining result correctness. Summary of the Invention

[0012] The purpose of this invention is to provide an optimization method for EXISTS correlated subqueries with multiple association conditions based on MPP databases, so as to alleviate the technical problem of low query efficiency in existing optimization methods for EXISTS correlated subqueries with multiple association conditions.

[0013] In a first aspect, the present invention provides a method for optimizing EXISTS related subqueries with multiple association conditions based on an MPP database, comprising: obtaining an SQL query statement to be optimized; wherein the SQL query statement contains EXISTS related subqueries, and the WHERE condition of the EXISTS related subqueries contains multiple association conditions, but no non-optimized conditions; the non-optimized conditions include: DISTINCT, GROUP BY, and OFFSET; extracting the condition columns from the EXISTS related subqueries; wherein the condition columns represent the data columns of the EXISTS related subquery table in the WHERE condition; calculating the row duplication rate of each condition column in the EXISTS related subqueries; filtering out duplicate keys from the condition columns based on the row duplication rate of all condition columns; and rewriting the SQL query statement based on the deduplication keys to obtain the optimized SQL query statement.

[0014] In an optional implementation, calculating the row repetition rate of each condition column in the EXISTS related subquery statement includes: obtaining the total number of rows of data contained in the target condition column; wherein, the target condition column represents any condition column among all condition columns; determining the initial sampling rate of the target condition column based on the total number of rows and a preset correspondence; wherein, the preset correspondence represents the correspondence between the data row number interval and the sampling rate; sampling the target condition column based on the initial sampling rate and obtaining the system's CPU utilization; if it is determined that the CPU utilization is greater than a preset threshold, reducing the initial sampling rate until the CPU utilization is less than or equal to the preset threshold to obtain the target sampling rate; if it is determined that the CPU utilization is less than or equal to the preset threshold, using the initial sampling rate as the target sampling rate; counting the number of duplicate rows in the target condition column based on the sampling results under the target sampling rate; and calculating the row repetition rate of the target condition column based on the number of duplicate rows, the target sampling rate, and the total number of rows.

[0015] In an optional implementation, deduplication keys are filtered from the condition columns based on the row repetition rate of all condition columns, including: calculating the repetition rate threshold of the target condition column based on the row repetition rate of the target condition column and the total number of rows of data it contains; determining the target condition column as a feature column if the row repetition rate of the target condition column is greater than its repetition rate threshold; calculating the row repetition rate and corresponding repetition rate threshold of each non-empty subset of the set composed of all feature columns; and selecting the non-empty subset with the highest dimension and the highest repetition rate among all non-empty subsets with row repetition rates greater than the corresponding repetition thresholds as the deduplication key.

[0016] In an optional implementation, the repetition rate threshold of the target condition column is calculated based on the row repetition rate of the target condition column and the total number of rows of data contained therein, including: determining the row repetition rate gain based on the row repetition rate of the target condition column; determining the data volume gain based on the total number of rows of the target condition column; and calculating the repetition rate threshold of the target condition column based on the preset row repetition rate base threshold, the row repetition rate gain, and the data volume gain.

[0017] In an optional implementation, the SQL query statement is rewritten based on the deduplication key to obtain an optimized SQL query statement, including: cloning the EXISTS correlated subquery statement from the SQL query statement; clearing the projection columns and join conditions in the EXISTS correlated subquery statement; injecting the deduplication key into the projection columns and constructing a GROUP BY statement based on the deduplication key to obtain a deduplicated statement; embedding the deduplicated statement into the EXISTS correlated subquery statement to obtain an optimized EXISTS correlated subquery statement; and updating the SQL query statement based on the optimized EXISTS correlated subquery statement to obtain an optimized SQL query statement.

[0018] In an optional implementation, reducing the initial sampling rate includes: processing the initial sampling rate according to a preset attenuation coefficient to obtain an updated sampling rate.

[0019] In an optional implementation, the formula for the repetition rate threshold of the target condition column is: ;in, This indicates the preset baseline threshold for row repetition rate. Indicates the row repetition rate gain. Indicates the data volume gain. This indicates the row repetition rate of the target criteria column. This indicates the total number of rows in the target criteria column. , All of these represent preset coefficients.

[0020] Secondly, the present invention provides an EXISTS related subquery optimization device based on multiple association conditions in an MPP database, comprising: an acquisition module for acquiring an SQL query statement to be optimized; wherein the SQL query statement contains an EXISTS related subquery statement, and the WHERE condition of the EXISTS related subquery statement contains multiple association conditions, and there are no non-optimized conditions; the non-optimized conditions include: DISTINCT, GROUP BY, and OFFSET; an extraction module for extracting condition columns from the EXISTS related subquery statement; wherein the condition columns represent the data columns of the EXISTS related subquery table in the WHERE condition; a calculation module for calculating the row repetition rate of each condition column in the EXISTS related subquery statement; a filtering module for filtering deduplicated keys from the condition columns based on the row repetition rate of all condition columns; and a rewriting module for rewriting the SQL query statement based on the deduplicated keys to obtain an optimized SQL query statement.

[0021] Thirdly, the present invention provides an electronic device, including a memory and a processor, wherein the memory stores a computer program that can run on the processor, and the processor executes the computer program to implement the EXISTS correlated subquery optimization method based on MPP database with multiple association conditions as described in any of the foregoing embodiments.

[0022] Fourthly, the present invention provides a computer-readable storage medium storing computer instructions, which, when executed by a processor, implement the EXISTS correlated subquery optimization method based on multiple association conditions of an MPP database as described in any of the foregoing embodiments.

[0023] This invention provides a method for optimizing EXISTS correlated subqueries with multiple association conditions based on MPP databases. After obtaining the SQL query statement to be optimized, the method first extracts the condition columns from its EXISTS correlated subqueries. Then, by calculating the row duplication rate of each condition column, duplicate keys are filtered out. Finally, the original SQL query statement is rewritten based on the deduplication keys to obtain the optimized SQL query statement. This method embeds the deduplication operation directly into the query rewriting process, enabling the system to effectively eliminate duplicate data and avoid redundant calculations. Compared with traditional global deduplication methods, this method significantly reduces the computational complexity and resource consumption of the database system while ensuring the accuracy of the query results, thereby greatly improving query efficiency. Attached Figure Description

[0024] To more clearly illustrate the specific embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the specific embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0025] Figure 1 A flowchart of an EXISTS correlated subquery optimization method based on multiple association conditions in an MPP database provided in an embodiment of the present invention;

[0026] Figure 2 This invention provides a method flow for filtering deduplicated keys from condition columns based on the row repetition rate of all condition columns;

[0027] Figure 3 This is a schematic diagram illustrating EXISTS related subqueries optimization of an example statement according to an embodiment of the present invention;

[0028] Figure 4 This is a functional block diagram of an EXISTS correlated subquery optimization device based on multiple association conditions in an MPP database, provided in an embodiment of the present invention. Detailed Implementation

[0029] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. The components of the embodiments of the present invention described and shown in the accompanying drawings can generally be arranged and designed in various different configurations.

[0030] Therefore, the following detailed description of the embodiments of the invention provided in the accompanying drawings is not intended to limit the scope of the claimed invention, but merely to illustrate selected embodiments of the invention. All other embodiments obtained by those skilled in the art based on the embodiments of the invention without inventive effort are within the scope of protection of the invention.

[0031] The following detailed description of some embodiments of the present invention is provided in conjunction with the accompanying drawings. Unless otherwise specified, the following embodiments and features can be combined with each other.

[0032] Example 1

[0033] This invention provides an EXISTS correlated subquery optimization method based on multiple association conditions in an MPP database. An MPP database refers to a distributed database that adopts a massively parallel processing architecture, such as GBase 8a MPP database. Figure 1 A flowchart illustrating an EXISTS correlated subquery optimization method based on multiple association conditions using an MPP database, as provided in this embodiment of the invention, is shown below. Figure 1 As shown, the method specifically includes the following steps:

[0034] Step S102: Obtain the SQL query statement to be optimized.

[0035] The method provided in this embodiment of the invention is applied to SQL query statements with the following characteristics: the SQL query statement contains EXISTS related subqueries, and the WHERE condition of the EXISTS related subqueries contains multiple related conditions and no non-optimized conditions; the non-optimized conditions include: DISTINCT, GROUP BY, and OFFSET.

[0036] To determine whether an SQL query possesses the aforementioned characteristics, after obtaining the SQL query, a syntax parser is used to parse it to construct a corresponding Abstract Syntax Tree (AST). This allows the system to identify whether an EXISTS-related subquery exists within the SQL query. If an EXISTS-related subquery is found, subsequent operations continue; otherwise, the process exits.

[0037] Since non-optimized conditions (DISTINCT, GROUP BY, and OFFSET) conflict with the optimization method provided in this embodiment of the invention, it is necessary to avoid applying the method of this embodiment to query statements with non-optimized conditions. Therefore, it is necessary to further analyze whether there are non-optimized conditions in the WHERE condition of the EXISTS related subquery. If non-optimized conditions exist, the method flow is exited; if no non-optimized conditions exist, and there are multiple related conditions in the WHERE condition, such as AND and OR, then it indicates that the SQL query statement can be optimized using the method provided in this embodiment of the invention.

[0038] Step S104: Extract the condition columns from the EXISTS related subquery statement.

[0039] The condition column represents the data columns of the EXISTS related subquery table in the WHERE condition.

[0040] Taking the query statement provided in the background art of this invention as an example, in the EXISTS related subquery statement, the EXISTS related subquery table is table b, and the condition columns are b1, b2, b3, b4 and b5. That is to say, the condition columns are the original data columns that have not been optimized and evaluated, and are taken from the related condition part of the EXISTS related subquery statement.

[0041] Step S106: Calculate the row repetition rate of each condition column in the EXISTS related subquery statement.

[0042] Step S108: Filter out duplicate keys from the condition columns based on the row repetition rate of all condition columns.

[0043] Existing technologies use global deduplication to optimize query statements. To avoid low-repetition-rate condition columns from participating in the deduplication operation and to reduce unnecessary waste of computing resources, this invention first calculates the row repetition rate of each condition column, and then further filters out deduplication keys based on the calculation results. The deduplication key represents the condition column or combination of condition columns used to identify and eliminate duplicate records. It determines which records will be judged as "duplicates" and thus processed in deduplication operations (such as GROUP BY).

[0044] Step S110: Rewrite the SQL query statement based on the deduplication key to obtain the optimized SQL query statement.

[0045] In this embodiment of the invention, by setting a repetition rate threshold, the deduplication operation determined based on the deduplication key is not a global deduplication process. Based on this, after rewriting the SQL query statement, the computational resources required for deduplication are significantly reduced compared to traditional global deduplication methods, and the larger the amount of data contained in the database table, the more significant the difference in computational resource usage.

[0046] This invention provides a method for optimizing EXISTS related subqueries with multiple association conditions based on MPP databases. After obtaining the SQL query statement to be optimized, the method first extracts the condition columns from its EXISTS related subqueries. Then, by calculating the row duplication rate of each condition column, duplicate keys are filtered out. Finally, the original SQL query statement is rewritten based on the deduplication keys to obtain the optimized SQL query statement. This method embeds the deduplication operation directly into the query rewriting process, enabling the system to effectively eliminate duplicate data and avoid redundant calculations. Compared with traditional global deduplication methods, this method significantly reduces the computational complexity and resource consumption of the database system while ensuring the accuracy of the query results, thereby greatly improving query efficiency.

[0047] In an optional implementation, step S106 above, calculating the row repetition rate of each condition column in the EXISTS related subquery statement, specifically includes the following steps:

[0048] Step S1061: Obtain the total number of rows of data contained in the target condition column; wherein, the target condition column represents any condition column among all condition columns.

[0049] Step S1062: Determine the initial sampling rate of the target condition column based on the total number of rows and the preset correspondence; wherein, the preset correspondence represents the correspondence between the data row number interval and the sampling rate.

[0050] This invention does not specifically limit the values ​​used in the preset correspondence. Users can make adaptive settings according to actual needs. Optionally, the preset correspondence is as follows: if the total number of rows N is less than or equal to 100,000 rows, the sampling rate is 5%; if the total number of rows N is greater than 100,000 rows and less than or equal to 1,000,000 rows, the sampling rate is 1%; if the total number of rows N is greater than 1,000,000 rows, the sampling rate is 0.1%. That is to say, the more rows of data contained in the target condition column, the lower the sampling rate, so as to ensure the accuracy of the row repetition rate calculation result while avoiding excessive calculation.

[0051] Assuming the total number of rows N in the target condition column is 500,000, then based on the preset correspondence in the example above, the initial sampling rate of the target condition column should be 1%.

[0052] Step S1063: Sample the target condition column based on the initial sampling rate and obtain the CPU utilization of the system.

[0053] Step S1064: If the CPU utilization rate is determined to be greater than the preset threshold, reduce the initial sampling rate until the CPU utilization rate is less than or equal to the preset threshold to obtain the target sampling rate.

[0054] Step S1065: If the CPU utilization rate is determined to be less than or equal to a preset threshold, the initial sampling rate is used as the target sampling rate.

[0055] After setting the initial sampling rate, this rate is used to sample the target condition column (i.e., extract multiple rows of data from the target condition column). If the system's CPU utilization during sampling is less than or equal to a preset threshold, the sampling rate is considered reasonable and is used as the target sampling rate, i.e., the final configured sampling rate. However, if the system's CPU utilization during sampling exceeds the preset threshold, the sampling rate is too high. In this case, the initial sampling rate should be reduced to obtain an updated sampling rate. After reconfiguring to the updated sampling rate, the CPU utilization should be measured again. If the CPU utilization is still greater than the preset threshold, the sampling rate should be reduced further until it is less than or equal to the preset threshold, and this current sampling rate is used as the target sampling rate. In other words, after setting the initial sampling rate, the system needs to dynamically adjust the sampling rate based on real-time CPU utilization.

[0056] The embodiments of the present invention do not specifically limit the value of the preset threshold. Users can set it adaptively according to the actual CPU configuration of the database query system. For example, the preset threshold can be configured as 90%.

[0057] Optionally, reducing the initial sampling rate includes processing the initial sampling rate according to a preset attenuation coefficient to obtain an updated sampling rate. In specific implementation, the initial sampling rate can be multiplied by the preset attenuation coefficient, and the product can be used as the updated sampling rate. The preset attenuation coefficient ranges from (0, 1). This embodiment of the invention does not specifically limit the value of the preset attenuation coefficient; users can configure it according to actual needs. For example, the preset attenuation coefficient can be 0.5.

[0058] The aforementioned dynamic sampling rate adjustment mechanism optimizes query efficiency while ensuring sufficient sampled data volume. This flexible mechanism allows the system to respond in real-time to fluctuations in computing resources, preventing excessive consumption of system performance under high load. It improves query efficiency, optimizes system resource utilization, and ensures data processing accuracy.

[0059] Step S1066: Count the number of duplicate rows in the target condition column based on the sampling results under the target sampling rate.

[0060] To give a simple example, if the sampling result of the target condition column under the target sampling rate is {1, 2, 3, 4, 1, 2, 1, 5}, then there is a duplicate row {1, 2, 1}, that is, the statistical result of the number of duplicate rows is 3.

[0061] Step S1067: Calculate the row repetition rate of the target condition column based on the number of repeated rows, the target sampling rate, and the total number of rows.

[0062] The formula for line repetition rate is expressed as: ,in, This indicates the row repetition rate of the target criteria column. This indicates the number of repeated rows in the target condition column under the target sampling rate. This indicates the total number of rows in the target criteria column. This represents the target sampling rate.

[0063] In one alternative implementation, such as Figure 2 As shown, step S108 above, which filters deduplicated keys from the condition columns based on the row repetition rate of all condition columns, specifically includes the following steps:

[0064] Step S1081: Calculate the repetition rate threshold of the target condition column based on the row repetition rate of the target condition column and the total number of rows of data it contains.

[0065] As described above, this embodiment of the invention filters out duplicate keys from the condition columns based on the setting of a repetition rate threshold. Therefore, in order to reasonably and accurately set the repetition rate threshold for each condition column, this embodiment adopts a dynamically guided threshold setting method.

[0066] Specifically, step S1081 above calculates the repetition rate threshold of the target condition column based on the row repetition rate of the target condition column and the total number of rows of data it contains, which includes the following steps:

[0067] Step S10811: Determine the row repetition rate gain based on the row repetition rate of the target condition column.

[0068] Step S10812: Determine the data volume gain based on the total number of rows in the target condition column.

[0069] Step S10813: Calculate the repetition rate threshold of the target condition column based on the preset row repetition rate base threshold, row repetition rate gain, and data volume gain.

[0070] In this embodiment of the invention, the formula for the repetition rate threshold of the target condition column is: ;in, This represents the preset baseline threshold for row repetition rate, based on common query patterns and data distribution. The value can be chosen as 55%, based on the above formula. Setting the upper limit of the repetition rate threshold to 55% can avoid the problem of not being able to obtain deduplication keys due to an excessively large repetition rate threshold. Indicates the row repetition rate gain. Indicates the data volume gain. This indicates the row repetition rate of the target criteria column. This indicates the total number of rows in the target criteria column. , All represent preset coefficients. Optionally, The value is 0.045. The value is 0.05.

[0071] According to the expression for line repetition rate gain It can be seen that with the increase in row repetition rate As the data volume increases, the system adaptively increases the optimization requirements to ensure that the repetition threshold gradually decreases during high-repetition queries. This is based on the expression for data volume gain. It can be seen that the data volume gain can be determined based on the data volume. Dynamic adjustment avoids premature optimization with small datasets while ensuring that optimization needs increase with large datasets. Based on the above analysis, it can be seen that the dynamically guided threshold setting method adopted in this embodiment of the invention can intelligently adjust the optimization strategy according to the specific distribution of the data.

[0072] Step S1082: If the row repetition rate of the target condition column is greater than its repetition rate threshold, the target condition column is determined as a feature column.

[0073] If, among the condition columns b1, b2, b3, b4, and b5, only the row repetition rate of b1, b2, and b3 is greater than the corresponding repetition rate threshold, then b1, b2, and b3 are determined to be feature columns.

[0074] Step S1083: Calculate the row repetition rate and the corresponding repetition rate threshold for each non-empty subset of the set consisting of all feature columns.

[0075] Following the example in step S1082, if b1, b2, and b3 are feature columns, then the set consisting of all feature columns is {b1, b2, b3}. Therefore, all its non-empty subsets include: {b1}, {b2}, {b3}, {b1, b2}, {b1, b3}, {b2, b3}, and {b1, b2, b3}. Referring to the method described above for calculating the row repetition rate and corresponding repetition rate threshold of a single column (i.e., a one-dimensional condition column), the row repetition rate and corresponding repetition rate threshold of the two-dimensional condition columns (i.e., {b1, b2}, {b1, b3}, and {b2, b3}) and the three-dimensional condition column {b1, b2, b3} within the non-empty subset can be calculated.

[0076] Step S1084: Among all non-empty subsets with a row repetition rate greater than the corresponding repetition rate threshold, the non-empty subset with the highest dimension and the highest repetition rate is used as the deduplication key.

[0077] In other words, after calculating the row repetition rate and corresponding repetition rate threshold of all non-empty subsets, the non-empty subsets with a repetition rate greater than the corresponding repetition rate threshold are first selected. Then, the non-empty subsets are sorted in descending order according to the size of the repetition rate. If there are non-empty subsets tied for first place, the non-empty subset with the highest dimension is selected as the deduplication key.

[0078] For ease of understanding, assume the non-empty subsets with a repetition rate greater than the corresponding repetition rate threshold are: {b1}, {b2}, {b3}, {b1, b2}, and {b1, b2, b3}. After sorting these non-empty subsets in descending order of their repetition rates, {b3} and {b1, b2} are tied for first place. Therefore, {b1, b2}, with its relatively higher dimensionality, will be selected as the deduplication key. This embodiment of the invention analyzes the repetition rate of feature columns of different dimensions layer by layer to accurately identify the combination with the most optimization value, thereby significantly improving query efficiency in complex high-dimensional scenarios.

[0079] In an optional implementation, step S110 above, which rewrites the SQL query statement based on the deduplication key to obtain an optimized SQL query statement, specifically includes the following steps:

[0080] Step S1101: Clone the EXISTS related subquery statement from the SQL query statement.

[0081] Specifically, the first step is to clone the EXISTS-related subqueries in the original SQL query. The purpose of this operation is to extract the subquery tree and ensure that subsequent modifications to the subquery structure will not affect the main query. This step primarily analyzes the original query to prepare for the subsequent optimization.

[0082] Step S1102: Clear the projection columns and association conditions in the EXISTS related subquery statement.

[0083] To ensure that irrelevant elements in the subquery structure no longer affect the rewritten query results, this embodiment of the invention, after cloning the EXISTS related subquery statement, clears the projection columns and all associated conditions in the EXISTS related subquery statement. Taking the SQL query statement in the background technology as an example, the projection columns and all associated conditions in the subquery, i.e., SELECT 1 and WHERE a.a1 = b.b1 and a.a2 = b.b2 and a.a3 = b.b3 and a.a4 = b.b4 and a.a5 = b.b5 in the original EXISTS related subquery statement.

[0084] Step S1103: Inject the deduplication key into the projection column and construct a GROUP BY statement based on the deduplication key to obtain the deduplication statement.

[0085] Following the example in step S1084 above, if the deduplication key is {b1, b2}, then the deduplication statement constructed in this step is: SELECT b1, b2, b3, b4, b5 FROM b GROUP BY b1, b2. Based on this, the system will perform deduplication processing based on the above deduplication key, thereby avoiding repeated calculations and reducing the processing of redundant data.

[0086] Step S1104: Embed the deduplication statement into the EXISTS related subquery statement to obtain the optimized EXISTS related subquery statement.

[0087] Based on the above example, the optimized EXISTS related subquery statement obtained in this step is:

[0088] SELECT 1 FROM

[0089] (SELECT b1, b2, b3, b4, b5 FROM b GROUP BY b1, b2) AS b

[0090] WHERE a.a1 = b.b1 and a.a2 = b.b2 and a.a3 = b.b3 and a.a4 = b.b4 anda.a5 = b.b5.

[0091] In other words, the original EXISTS correlated subquery structure was modified to match using the deduplicated table b, thereby reducing the number of redundant calculations and improving query efficiency.

[0092] Step S1105: Update the SQL query statement based on the optimized EXISTS related subquery statement to obtain the optimized SQL query statement.

[0093] Figure 3 This is a schematic diagram illustrating the EXISTS correlated subquery optimization of the example statement according to an embodiment of the present invention. Based on the above steps, the optimized SQL query statement is finally generated, as shown below:

[0094] SELECT * FROM a WHERE EXISTS

[0095] (SELECT 1 FROM

[0096] (SELECT b1, b2, b3, b4, b5 FROM b GROUP BY b1,b2) AS b

[0097] WHERE a.a1 = b.b1 and a.a2 = b.b2 and a.a3 = b.b3 and a.a4 = b.b4 anda.a5 = b.b5).

[0098] After the query tree is reconstructed, the optimized query structure can be embedded into the database's query execution plan to ensure the efficiency of query operations.

[0099] Based on the description of all the above methods and steps, it can be seen that the method provided by the embodiments of the present invention can effectively handle complex queries involving multiple condition columns. By calculating the row duplication rate of the condition columns, the system can identify and collaboratively optimize redundant data of multiple feature columns. The deduplicated subqueries effectively eliminate duplicate data, thereby improving query performance and reducing computing resource consumption.

[0100] To verify the method provided in the embodiments of the present invention, the improvement rate of query time is... For indicators: .in, This indicates the time consumed by the query without optimization. This indicates the time consumed by the query after optimization using the method of this invention.

[0101] By comparing optimized and non-optimized queries across different data volumes, the results show that optimization significantly improves query efficiency, especially in scenarios with high repetition rates. Actual testing demonstrates that on billion-level data tables with a repetition rate exceeding 50%, the optimized query execution time is reduced by an average of 40%-65%, with performance improvements exceeding 30% in scenarios where multiple key condition columns have a repetition rate exceeding 70%.

[0102] Example 2

[0103] This invention also provides an EXISTS related subquery optimization device based on multiple association conditions of an MPP database. This device is mainly used to execute the EXISTS related subquery optimization method based on multiple association conditions of an MPP database provided in Embodiment 1 above. The following is a detailed description of the EXISTS related subquery optimization device based on multiple association conditions of an MPP database provided in this invention.

[0104] Figure 4 This is a functional block diagram of an EXISTS correlated subquery optimization device based on multiple association conditions in an MPP database, provided in an embodiment of the present invention. Figure 4 As shown, the device mainly includes: an acquisition module 10, an extraction module 20, a calculation module 30, a filtering module 40, and a rewriting module 50, wherein:

[0105] The acquisition module 10 is used to acquire the SQL query statement to be optimized; wherein, the SQL query statement contains EXISTS related subqueries, and the WHERE condition of the EXISTS related subqueries contains multiple related conditions, and there are no non-optimized conditions; the non-optimized conditions include: DISTINCT, GROUP BY and OFFSET.

[0106] Extraction module 20 is used to extract the condition columns in the EXISTS related subquery statement; where the condition columns represent the data columns of the EXISTS related subquery table in the WHERE condition.

[0107] Calculation module 30 is used to calculate the row repetition rate of each condition column in the EXISTS related subquery statement.

[0108] The filtering module 40 is used to filter deduplicated keys from the condition columns based on the row repetition rate of all condition columns.

[0109] The rewrite module 50 is used to rewrite SQL query statements based on deduplication keys to obtain optimized SQL query statements.

[0110] This invention provides a device for optimizing EXISTS related subqueries based on multiple association conditions in an MPP database. After obtaining the SQL query statement to be optimized, the device first extracts the condition columns from its EXISTS related subqueries. Then, it calculates the row duplication rate of each condition column to filter out duplicate keys. Finally, it rewrites the original SQL query statement based on the deduplication keys to obtain the optimized SQL query statement. This device embeds the deduplication operation directly into the query rewriting process, enabling the system to effectively eliminate duplicate data and avoid redundant calculations. Compared with traditional global deduplication methods, this device significantly reduces the computational complexity and resource consumption of the database system while ensuring the accuracy of the query results, thereby greatly improving query efficiency.

[0111] Optionally, the computing module 30 is specifically used for:

[0112] Get the total number of rows of data contained in the target condition column; where the target condition column represents any one of the condition columns.

[0113] Based on the total number of rows and the preset correspondence, the initial sampling rate of the target condition column is determined; where the preset correspondence represents the correspondence between the data row number interval and the sampling rate.

[0114] The target condition column is sampled based on the initial sampling rate, and the CPU utilization of the system is obtained.

[0115] If the CPU utilization rate is determined to be greater than a preset threshold, the initial sampling rate is reduced until the CPU utilization rate is less than or equal to the preset threshold, thus obtaining the target sampling rate.

[0116] If the CPU utilization rate is determined to be less than or equal to a preset threshold, the initial sampling rate is used as the target sampling rate.

[0117] The number of duplicate rows in the target condition column is counted based on the sampling results under the target sampling rate.

[0118] Calculate the row repetition rate of the target condition column based on the number of repeated rows, the target sampling rate, and the total number of rows.

[0119] Optionally, the filtering module 40 includes:

[0120] The first calculation unit is used to calculate the repetition rate threshold of the target condition column based on the row repetition rate of the target condition column and the total number of rows of data contained therein.

[0121] The first determining unit is used to determine the target condition column as a feature column when the row repetition rate of the target condition column is greater than its repetition rate threshold.

[0122] The second calculation unit is used to calculate the row repetition rate and the corresponding repetition rate threshold for each non-empty subset of the set consisting of all feature columns.

[0123] The second determining unit is used to select the non-empty subset with the highest dimension and the highest repetition rate from all non-empty subsets with a row repetition rate greater than the corresponding repetition rate threshold as the deduplication key.

[0124] Optionally, the first computing unit is specifically used for:

[0125] The row repetition rate gain is determined based on the row repetition rate of the target condition column.

[0126] The data volume gain is determined based on the total number of rows in the target condition column.

[0127] The repetition rate threshold of the target condition column is calculated based on the preset row repetition rate base threshold, row repetition rate gain, and data volume gain.

[0128] Optionally, rewriting module 50 is specifically used for:

[0129] Clone the EXISTS related subquery from the SQL query statement.

[0130] Clear the projection columns and association conditions in the EXISTS correlated subquery statement.

[0131] Inject the deduplication key into the projected column, and construct the GROUP BY statement based on the deduplication key to obtain the deduplication statement.

[0132] Embedding the deduplication statement into the EXISTS correlated subquery statement yields the optimized EXISTS correlated subquery statement.

[0133] The optimized SQL query statement is obtained by updating the SQL query statement based on the optimized EXISTS related subquery statement.

[0134] Optionally, the computing module 30 is also used for:

[0135] The initial sampling rate is processed according to the preset attenuation coefficient to obtain the updated sampling rate.

[0136] Optionally, the formula for the repetition rate threshold of the target condition column is: ;in, This indicates the preset baseline threshold for row repetition rate. Indicates the row repetition rate gain. Indicates the data volume gain. This indicates the row repetition rate of the target criteria column. This indicates the total number of rows in the target criteria column. , All of these represent preset coefficients.

[0137] Example 3

[0138] In practical applications, the EXISTS correlated subquery optimization method based on multiple association conditions using an MPP database, as described in Embodiment 1 above, can be integrated into a statistical analysis system based on an MPP database. This system typically consists of one or more database servers, each equipped with MPP database software to store business data and execute user-submitted SQL queries. To reduce the performance overhead of EXISTS correlated subqueries with multiple association conditions, a piece of program code can be added to the database server to execute the method described in Embodiment 1.

[0139] In this implementation, when a user or upper-layer application submits a query request to the MPP database, the server first receives the SQL statement to be executed. Based on conventional syntax analysis of the SQL, the database calls the optimization program provided in this embodiment to check whether there are EXISTS related subqueries in the statement, and whether these related subqueries contain multiple join conditions. If a related subquery that meets the conditions is found, then according to the rules described in Embodiment 1 above, simple statistics or sampling are performed on the columns involved in the join to estimate the data duplication of these columns, thereby selecting columns suitable as "deduplication keys".

[0140] After selecting the deduplication key, the optimization program rewrites the original SQL statement without changing the query results. This might involve adding deduplication operations in appropriate places or reshaping the nested EXISTS structure into a more easily executable form. Once rewritten, the optimized SQL is submitted to the MPP database for normal execution. For the user, the query results remain unchanged, but in scenarios with large datasets and complex related subqueries, it significantly reduces the amount of duplicate data processed and shortens query time.

[0141] The above process can be performed using a computer device, which includes at least a processor and a memory. The memory stores a computer program that implements the method in Embodiment 1. When the processor runs the program, it performs the aforementioned steps regarding SQL checking, column duplication estimation, deduplication key selection, and query rewriting. Those skilled in the art can compile this program into a plug-in, extension module, or directly integrate it into the database kernel, depending on the specific database product; this embodiment of the invention does not limit this.

[0142] The computer program product of the EXISTS correlated subquery optimization method based on multiple association conditions of MPP database provided in this embodiment of the invention includes a computer-readable storage medium storing non-volatile program code executable by a processor. The instructions included in the program code can be used to execute the methods described in the preceding method embodiments. For specific implementation, please refer to the method embodiments, which will not be repeated here.

[0143] The above embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit it. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features therein. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. An EXISTS correlated subquery optimization method based on multi-association conditions of an MPP database, characterized in that, The method comprises the following steps: acquire a SQL query statement to be optimized; wherein, the SQL query statement contains an EXISTS related subquery statement, and the WHERE condition of the EXISTS related subquery statement contains multiple correlation conditions, and no non-optimized condition; the non-optimized condition includes DISTINCT, GROUP BY and OFFSET; extract condition columns in the EXISTS related subquery statement; wherein, the condition column represents a data column of an EXISTS related subquery table in the WHERE condition; calculate the row duplication rate of each condition column in the EXISTS related subquery statement; select a deduplication key from the condition columns based on the row duplication rates of all the condition columns; rewrite the SQL query statement based on the deduplication key to obtain an optimized SQL query statement; wherein, selecting a deduplication key from the condition columns based on the row duplication rates of all the condition columns comprises: based on the row duplication rate of a target condition column and the total number of data contained in the target condition column, calculate the duplication rate threshold of the target condition column; the target condition column represents any condition column in all the condition columns; in the case where it is determined that the row duplication rate of the target condition column is greater than its duplication rate threshold, determine that the target condition column is a feature column; calculate the row duplication rate of each non-empty subset of a set composed of all the feature columns and the corresponding duplication rate threshold; among all the non-empty subsets with a row duplication rate greater than the corresponding duplication rate threshold, the non-empty subset with the highest dimension and the highest duplication rate is taken as the deduplication key.

2. The method for optimizing the EXISTS related subquery of multi-association condition based on the MPP database according to claim 1, characterized in that, calculating the row duplication rate of each condition column in the EXISTS related subquery statement comprises: acquire the total number of data contained in a target condition column; based on the total number and a preset correspondence relationship, determine the initial sampling rate of the target condition column; wherein, the preset correspondence relationship represents the correspondence between the data row number interval and the sampling rate; based on the initial sampling rate, sample the target condition column, and acquire the CPU usage rate of the system; in the case where it is determined that the CPU usage rate is greater than a preset threshold, reduce the initial sampling rate until the CPU usage rate is less than or equal to the preset threshold to obtain a target sampling rate; in the case where it is determined that the CPU usage rate is less than or equal to the preset threshold, take the initial sampling rate as the target sampling rate; based on the sampling result under the target sampling rate, count the number of duplicate rows in the target condition column; based on the number of duplicate rows, the target sampling rate and the total number of rows, calculate the row duplication rate of the target condition column.

3. The method for optimizing the EXISTS related subquery of multi-association condition based on MPP database according to claim 1, characterized in that, based on the row duplication rate of the target condition column and the total number of data contained in the target condition column, calculating the duplication rate threshold of the target condition column comprises: determine the row duplication rate gain based on the row duplication rate of the target condition column; determine the data volume gain based on the total number of rows of the target condition column; based on a preset row duplication rate basic threshold, the row duplication rate gain and the data volume gain, calculate the duplication rate threshold of the target condition column.

4. The method for optimizing the EXISTS related subquery of multi-association condition based on MPP database according to claim 1, characterized in that, rewriting the SQL query statement based on the deduplication key to obtain an optimized SQL query statement comprises: clone an EXISTS related subquery statement from the SQL query statement; empty projection columns and association conditions in the EXISTS related subquery statement; inject the deduplication key into the projection columns and construct a GROUP BY statement based on the deduplication key to obtain a deduplication statement; embed the deduplication statement into the EXISTS related subquery statement to obtain an optimized EXISTS related subquery statement; update the SQL query statement based on the optimized EXISTS related subquery statement to obtain the optimized SQL query statement.

5. The method for optimizing the EXISTS related subquery of multi-association condition based on MPP database according to claim 2, characterized in that, decrease the initial sampling rate, comprising: processing the initial sampling rate according to a preset attenuation coefficient to obtain an updated sampling rate.

6. The method for optimizing the EXISTS related subquery of multi-association condition based on MPP database according to claim 3, characterized in that, An algorithm of the repetition rate threshold of the target condition column is: ; wherein, represents the preset row repetition rate basic threshold, represents the row repetition rate gain, represents the data volume gain, represents the row repetition rate of the target condition column, represents the total number of rows of the target condition column, , all represent preset coefficients.

7. A device for optimizing EXISTS correlated subqueries with multiple association conditions based on an MPP database, characterized in that, comprising: an acquisition module, configured to acquire a SQL query statement to be optimized; wherein the SQL query statement contains an EXISTS related subquery statement, and a WHERE condition of the EXISTS related subquery statement contains multiple association conditions and does not contain a non-optimized condition; the non-optimized condition includes DISTINCT, GROUP BY and OFFSET; an extraction module, configured to extract condition columns in the EXISTS related subquery statement; wherein the condition columns represent data columns of an EXISTS related subquery table in the WHERE condition; a calculation module, configured to calculate a row duplication rate of each condition column in the EXISTS related subquery statement; a screening module, configured to screen a deduplication key from the condition columns based on the row duplication rates of all the condition columns; a rewriting module, configured to rewrite the SQL query statement based on the deduplication key to obtain an optimized SQL query statement; wherein the screening module comprises: a first calculation unit, configured to calculate a duplication rate threshold of a target condition column based on a row duplication rate of the target condition column and a total number of data contained in the target condition column; the target condition column represents any condition column in all the condition columns; a first determination unit, configured to determine the target condition column as a feature column in a case where the row duplication rate of the target condition column is greater than the duplication rate threshold of the target condition column; a second calculation unit, configured to calculate a row duplication rate and a corresponding duplication rate threshold of each non-empty subset of a set composed of all the feature columns; a second determination unit, configured to determine, from all the non-empty subsets with the row duplication rate greater than the corresponding duplication rate threshold, a non-empty subset with the highest dimension and the highest duplication rate as the deduplication key.

8. An electronic device comprising a memory, a processor, the memory having stored thereon a computer program executable on the processor, characterized in that, The processor implements the MPP database-based EXISTS related subquery optimization method for multiple association conditions according to any one of claims 1 to 6 when executing the computer program.

9. A computer-readable storage medium, characterized in that, The computer readable storage medium stores computer instructions, and the computer instructions implement the MPP database-based EXISTS related subquery optimization method for multiple association conditions according to any one of claims 1 to 6 when executed by the processor.

Images