Vector-relational data hybrid query optimization method based on dynamic search range

Abstract

The invention discloses a vector-relational data hybrid query optimization method based on a dynamic search range. According to the algorithm, the query efficiency is improved through a two-stage optimization strategy; the method comprises the following steps of: firstly, in an optimization condition checking stage, analyzing semantics of a query by a query engine, evaluating optimizability of a physical operator, and ensuring semantics conforming to K-nearest neighbor (KNN) query; and secondly, in a physical operator optimization stage, the algorithm tracks a query state by using a record table, executes approximate nearest neighbor search, and performs query decision according to similarity, so that redundant calculation is reduced. According to the algorithm, the calculation overhead is effectively reduced while the query accuracy and diversity are ensured, and the search is terminated in time after all types of KNN queries are completed. The invention aims to improve the processing efficiency of mixed query and optimize the user experience in a recommendation system, and provides an efficient query optimization solution.

Description

Technical Field

[0001] The present invention relates to the technical field of database query optimization, and in particular to a vector-relational data hybrid query optimization method based on dynamic search range. Background Art

[0002] In a recommendation system, relying solely on the most accurate recommendation method does not always bring the best results. Although high-precision recommendations can provide highly relevant items based on the user's historical behavior, such recommendations often lead to a monotonous user experience because the recommended items are often highly similar and lack diversity. Therefore, in a recommendation system, the diversity of recommendations is as important as accuracy.

[0003] To address this problem, the query scope is usually first limited to data that exceeds a certain similarity threshold with the query object, and then the window function is used to group the data by category and calculate the K nearest neighbors in each group. Existing query engines can quickly retrieve records within the query scope by optimizing index scanning, but lack the ability to further narrow the scope. Figure 1 As shown in the figure, within a larger query range R1, if the data is evenly distributed, the number of data points in each category is K' (K' ≥ K), even if the query range is narrowed to R2 (R2 ≤ R1), at least K neighbors can still be found in each category, and the accuracy and diversity of recommendations will not be significantly affected. The query engine should further optimize the window function operator to ensure that the query can be terminated as soon as possible when the query range exceeds R2, avoiding unnecessary redundant calculations in the difference area between R1 and R2. Summary of the invention

[0004] In view of the deficiencies of the above-mentioned prior art, the present invention aims to provide a vector-relational data hybrid query optimization method based on dynamic search range to improve the processing efficiency of hybrid queries and optimize the user experience in the recommendation system; the method is used to optimize hybrid query processing, and it improves query efficiency through a two-stage optimization strategy to solve the limitations faced by existing query engines when processing large-scale data. While ensuring the accuracy and diversity of queries, the method effectively reduces the computational overhead and terminates the search in time after all categories of KNN queries are completed, providing an efficient query optimization solution.

[0005] The technical solution of the present invention is specifically introduced.

[0006] A vector-relational data hybrid query optimization method based on dynamic search range includes two stages:

[0007] Phase 1: Analyze the semantics of the query and decide whether the physical operators in the query can be optimized based on the semantic check;

[0008] Phase 2: Specific optimization of optimizable physical operators

[0009] If the query meets specific semantic conditions, the query range is dynamically adjusted to optimize the K nearest neighbor (KNN) query operation based on category partitioning. During the optimization process, the record table T is used to track the categories involved in each query process, and the record table T is updated according to the similarity of the query results and the number of tuples that meet the conditions. When all categories in the record table T have completed their respective KNN queries, the query operation is terminated early to reduce unnecessary computational overhead.

[0010] In the present invention, the specific steps of the first stage include:

[0011] 1) Evaluate the query plan tree and check whether the operators in the query meet the optimization conditions;

[0012] 2) Perform semantic checks on the window operation to ensure compliance with KNN query semantics, and check whether the partition field is a non-primary key column and whether the window size covers the entire partition;

[0013] 3) After passing the semantic check, the downstream K value of the window operation is extracted for subsequent index scan operations.

[0014] In the present invention, the specific steps of the second stage include:

[0015] 1) Use record table T to store query status information of each category;

[0016] 2) Check whether there are unprocessed categories in the record table;

[0017] 3) Perform an approximate nearest neighbor ANN search to obtain the result tuple res and its similarity sim with the query;

[0018] 4) Decide whether to continue the query based on the value of the similarity sim. If sim is greater than or equal to the predetermined threshold R, update the record table T.

[0019] In the present invention, when a category in the record table T is found to be a new category during the query process, the system will increase the count of the category and update the search queue of the corresponding category based on the similarity of the result tuples.

[0020] Compared with the prior art, the present invention has the following beneficial effects:

[0021] The present invention relates to an algorithm for optimizing hybrid query processing, and is particularly directed to the problem of calculating redundant physical operators in the process of processing hybrid queries. The query engine includes a two-stage optimization strategy, the first stage of which is to determine whether there are physical operators that can be optimized by checking the query conditions, and the second stage is to optimize these physical operators in detail to reduce redundant calculations and improve query efficiency. While ensuring query accuracy and diversity, the present invention effectively reduces calculation overhead and improves the processing efficiency of hybrid queries. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] Figure 1 This is a schematic diagram for dynamically narrowing the query scope.

[0023] Figure 2 It is a diagram of the query performance of Query1 in the embodiment.

[0024] Figure 3 Query2 query performance diagram in the embodiment. DETAILED DESCRIPTION

[0025] The technical solution of the present invention is described in detail below in conjunction with specific embodiments.

[0026] In an embodiment, a vector-relational data hybrid query optimization method based on dynamic retrieval range is provided, and the method is divided into the following two stages:

[0027] 1. Optimize condition checks

[0028] At this stage, the query engine first evaluates the query plan tree to check whether the operators in the query meet the optimization conditions. For classification-based partition queries, the physical operators in the query plan (such as basic table scans and index scans) are marked according to the specific conditions of the query (such as the presence of comparison operators). Next, the system performs a semantic check on the window operation to ensure compliance with the KNN query semantics, and checks whether the partition field is a non-primary key column and whether the window size covers the entire partition. If the semantic check passes, the system extracts the downstream K value of the window operation and uses it for subsequent index scan operations.

[0029] 2. Physical Operator Optimization

[0030] After passing the semantic check, the algorithm will enter the optimization phase of the physical operator. The query optimization process based on classification partitioning is divided into the following steps:

[0031] First, a record table T is used to store the query status information of each category. The algorithm checks whether there are any unprocessed categories. If all categories in the record table have been processed, it means that the KNN query of all categories has been completed; otherwise, the system will perform an approximate nearest neighbor (ANN) search to obtain the result tuple res and its similarity sim with the query.

[0032] If the similarity sim is less than the predetermined threshold R, the algorithm will determine whether to continue the query. If the similarity is greater than or equal to R, it indicates that the query has entered the specified range, and the record table T is updated immediately. Each key in the record table represents a category, and the corresponding value is the search queue of the category and the number of tuples that meet the query conditions.

[0033] When the algorithm updates the record table, it first checks the category and similarity of the result tuple. If the category does not exist in the record table, it indicates that a new category was found during the query, and the system will increase the count of the category. Subsequently, based on the similarity of the result tuple, the search queue of the corresponding category is updated, and the monotonicity of the queue is checked. If the number of tuples that meet the conditions for a category reaches the K value and the search queue increases monotonically, it indicates that the KNN query for the category is completed, and the algorithm will reduce the count of the unprocessed category in the record table.

[0034] The termination condition of the optimization algorithm is that when the KNN query of all categories in the record table is completed, the system will stop further search operations, thereby reducing the computational overhead.

[0035] The following are specific embodiments.

[0036] The publicly available dataset LAION-400-MILLION OPEN DATASET is used for system evaluation. The dataset is divided into two subsets, laion1m and laion100. The laion1m subset contains 1 million data entries, while the laion100 subset contains 100 data entries. The data in the two tables are non-overlapping.

[0037] The Query1 process is shown in Table 1:

[0038]

[0039]

[0040] The Query2 process is shown in Table 2:

[0041]

[0042] In order to increase the query complexity, in the embodiment, three different selectivity levels are designed for Q1 and Q2, specifically: 1, 0.5 and 0.03. Selectivity is defined as: selectivity = the number of tuples satisfying the predicate / the total number of tuples / the total number of tuples. It is worth noting that when the selectivity is 1, it means that no relational data is involved in the query.

[0043] Recall is used as a measure of accuracy, which is defined as: recall = |X∩G| / |G|. Among them, G represents the set of actual neighboring samples, and X represents the set of samples obtained by approximate nearest neighbor search. A higher recall rate indicates that the system can effectively capture more real samples, thereby improving its reliability in practical applications. In addition, execution time is introduced as a key indicator for performance evaluation. The measurement of execution time excludes planning time, data loading time, and compilation time, ensuring that only the actual computing time during input data processing is recorded; the results are as follows: Figure 2 and Figure 3 As shown, the results show that the present invention can effectively reduce computing overhead and improve the processing efficiency of hybrid queries while ensuring query accuracy and diversity.

Claims

1. A vector-relational data hybrid query optimization method based on dynamic search range, characterized in that: It consists of two stages: Phase 1: Analyze the semantics of the query and decide whether the physical operators in the query can be optimized based on the semantic check; Phase 2: Specific optimization of optimizable physical operators If the query meets specific semantic conditions, the query range is dynamically adjusted to optimize the K nearest neighbor KNN query operation based on category partitioning; during the optimization process, the record table T is used to track the categories involved in each query process, and the record table T is updated according to the similarity of the query results and the number of tuples that meet the conditions; when all categories in the record table T have completed their respective KNN queries, the query operation is terminated early to reduce unnecessary computational overhead.

2. The vector-relational data hybrid query optimization method based on dynamic search range according to claim 1 is characterized in that: The specific steps of the first phase include: 1) Evaluate the query plan tree and check whether the operators in the query meet the optimization conditions; 2) Perform semantic checks on the window operation to ensure compliance with KNN query semantics, and check whether the partition field is a non-primary key column and whether the window size covers the entire partition; 3) After passing the semantic check, the downstream K value of the window operation is extracted for subsequent index scan operations.

3. The vector-relational data hybrid query optimization method based on dynamic search range according to claim 1 is characterized in that: The specific steps of the second phase include: 1) Use record table T to store query status information of each category; 2) Check whether there are unprocessed categories in the record table; 3) Perform an approximate nearest neighbor ANN search to obtain the result tuple res and its similarity sim to the query; 4) Decide whether to continue the query based on the value of the similarity sim. If sim is greater than or equal to the predetermined threshold R, update the record table T.

4. The vector-relational data hybrid query optimization method based on dynamic search range according to claim 3 is characterized in that: When a category in the record table T is found to be a new category during the query process, the system will increase the count of the category and update the search queue of the corresponding category based on the similarity of the result tuples.

Images