A materialized view selection method and system based on large model

Abstract

The present invention provides a materialized view selection method and system based on a large model, which includes an execution plan parsing strategy, a query clustering algorithm, and a retrieval enhancement strategy. By using the prior knowledge of the large model, the same type of queries are asked to the large model with the help of prompt words to obtain materialized views and rewritten queries, and the correctness of the parsing results is analyzed by an error correction module, and finally the final result is obtained by a materialized view selection module. On the one hand, the present invention uses the materialized view related fields to fine-tune the large model, capture the relationship between queries and views, and adapt to the generation of materialized views in various scenarios; on the other hand, the present invention uses a retrieval enhancement mechanism to search for prompt word examples that meet the current scenario from a vector database, promote model performance in different scenarios, thereby improving the response of the large model and obtaining better results.

Description

Technical Field

[0001] The present invention belongs to the technical field of materialized views, and in particular relates to a materialized view selection method and system based on a large model. Background Art

[0002] Materialized views are views that store query results on disk. They can speed up query execution, but they also take up storage space and bring additional maintenance costs. Therefore, choosing which queries should create materialized views and when to update materialized views is a technical challenge. Materialized views improve query performance by avoiding repeated calculations of expensive query operations (such as joins, sorts, etc.). They save the query results obtained as a database object, which may be a local copy of remote data, an entity table, or a subset of rows and columns of a join result. Through the pre-calculation of materialized views, repeated subqueries in batch queries and complex queries containing multiple joins can be simplified to queries on materialized views or simpler calculations to obtain results, eliminating the overhead caused by expensive joins and aggregations, reducing system load, greatly improving query processing time, significantly improving database query performance, and reducing query costs.

[0003] In recent years, with the development of machine learning, deep learning, and reinforcement learning, materialized views have gradually begun to use methods based on deep learning or reinforcement learning. Methods based on deep learning can estimate the benefits of using materialized views for queries by learning the characteristics of execution plans, and thus recommend materialized views. On the one hand, materialized views based on greedy algorithms, such as the literature [Zhang Baili, Sun Zhihui, Sun Xiang. Preprocessing algorithm for materialized view selection [J]. Computer Research and Development, 2004, 41(10):1645-1651], select heuristic exploration based on database rules, which has good interpretability and accuracy, but it can only obtain local optimal solutions and performs poorly on large-scale data. On the other hand, methods based on deep learning (such as the Chinese patent application technology with publication number CN111597209A) can mine code defects based on historical training data and can be used for materialized view selection of large-scale data, but they lack interpretability and require a lot of time and resources to generate training data. The generalization ability in actual industrial scenarios remains to be verified.

[0004] In summary, we can find that there are at least the following problems in the existing technology: First, the existing research methods require a large amount of training data and spend a lot of training time, cannot adapt to scenarios that require real-time processing, and the actual effect is not ideal; second, most of the existing methods are limited to a single data set and a single database, cannot adapt to multi-source database scenarios, and cannot implement a unified system; finally, the materialized views generated by these methods mostly rely on general rules without considering prior knowledge. Summary of the invention

[0005] In view of the above, the present invention provides a materialized view selection method and system based on a big model, which can improve the quality of generated materialized views and enhance the management of multi-source databases by utilizing the prior knowledge, thought chain and other characteristics of the big model.

[0006] A materialized view selection method based on a large model includes the following steps:

[0007] (1) Obtain query SQL and parse its query plan;

[0008] (2) All query SQLs are divided into several clusters through clustering based on the information obtained from the analysis;

[0009] (3) Generate prompt words for each cluster;

[0010] (4) Generate a materialized view for each cluster using a large language model based on the prompt words and the query SQL rewritten by the materialized view;

[0011] (5) Verify and correct errors in the materialized views generated by the large language model and the rewritten query SQL;

[0012] (6) Collect the materialized views of all clusters and the rewritten query SQL, consider the benefits and costs of the materialized views, and select several better materialized views and the query SQL they cover as output.

[0013] Furthermore, the specific implementation method of step (1) is as follows: first, the query SQL and its corresponding query plan, the execution time, execution cost and number of data records generated by each operator in the query plan are extracted from the query execution log; then, the query plan is parsed to obtain the type of each operator in the query plan, the conditional predicates involved, and the column information of the table included in the conditional predicates.

[0014] Furthermore, the specific implementation method of step (2) is as follows: first, based on the execution time, execution cost, number of data records, type of each operator, conditional predicates involved, and column information of the table contained in the conditional predicates, an embedding vector of the query SQL is generated; then, by calculating the similarity between the embedding vectors, query SQLs of the same structure and similar type are clustered together, thereby generating a number of clusters.

[0015] Furthermore, the specific implementation method of step (3) is as follows: first, some sample information of query SQL-materialized view is collected from the Internet, and the sample information is converted into vector form and stored in the vector database; for any cluster, a query SQL is randomly selected from it, and the similarity between the embedded vector of the query SQL and each vector in the vector database is calculated, and several vectors are selected from the vector database through similarity sorting and greedy matching, and the sample information corresponding to these vectors is used as the prompt word of the materialized view, and then some rules in the materialized view field are selected and used as part of the prompt word, and finally these two parts of the prompt word are combined together as the prompt word of the entire cluster.

[0016] Furthermore, the specific implementation method of step (4) is as follows: first, a large language model is obtained and fine-tuned to adapt it to the materialized view extraction application; then, for any cluster, the prompt word of the cluster is input into the fine-tuned large language model, and after model processing, the materialized view of the cluster and the query SQL rewritten by the materialized view are obtained.

[0017] Furthermore, the specific implementation of step (5) is as follows:

[0018] 5.1 Analyze the syntax and semantics of materialized views and check the correctness of materialized views by connecting to the underlying database;

[0019] 5.2 Analyze the cost of the materialized view and compare it with the original query storage size to detect the rationality of the materialized view;

[0020] 5.3 Analyze the syntax and semantics of the rewritten query SQL and check the correctness of the query SQL by connecting to the underlying database;

[0021] 5.4 Check the adaptability of the rewritten query SQL by comparing it with the original query results to see if the results are consistent;

[0022] 5.5 If an error is detected in the above steps, the error information is fed back to the large language model and step (4) is re-executed until the generated materialized view and query SQL are correct.

[0023] Furthermore, the specific implementation of step (6) is as follows: first, for any cluster, select the first several materialized views whose single estimated storage size meets the constraint condition from the materialized views of the cluster to form MV-Unions; then, for any MV-Unions, match all query SQLs covered by them, calculate the time difference before and after the rewriting of these query SQLs and accumulate them as the estimated benefit of the MV-Unions, and divide the estimated benefit by the estimated storage size of the MV-Unions, and then sort all MV-Unions according to the ratio, and select the first several MV-Unions that meet the total storage space constraint condition; finally, the materialized views of these MV-Unions and the query SQLs covered by them are used as the final output.

[0024] A materialized view selection system based on a large model, comprising:

[0025] The query plan parsing module is used to obtain the query SQL and parse its query plan;

[0026] The clustering module divides all query SQLs into several clusters based on the information obtained from the analysis;

[0027] The embedding module is used to generate the embedding vector of the query SQL based on the information obtained from the analysis;

[0028] The retrieval enhancement module is used to generate prompt words for each cluster;

[0029] The large language model uses the prompt word as input to generate a materialized view for each cluster and the query SQL rewritten by the materialized view;

[0030] The analysis and error correction module is used to verify and correct the materialized views generated by the large language model and the rewritten query SQL;

[0031] The materialized view selection module is used to collect the materialized views of all clusters and the rewritten query SQL, consider the benefits and costs of the materialized views, and select several better materialized views and the query SQL they cover as output.

[0032] A computer device includes a memory and a processor, wherein the memory stores a computer program, and the processor is used to execute the computer program to implement the above-mentioned materialized view selection method based on a large model.

[0033] A computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method for selecting a materialized view based on a large model is implemented.

[0034] The present invention includes an execution plan parsing strategy, a query clustering algorithm, and a retrieval enhancement strategy. By using the prior knowledge of the big model, the same type of queries are asked to the big model with the help of prompt words, and the materialized view and the rewritten query are obtained. The correctness of the parsing result is analyzed by the error correction module, and finally the final result is obtained by the materialized view selection module. On the one hand, the present invention uses the materialized view related fields to fine-tune the big model, capture the relationship between the query and the view, so as to adapt to the generation of materialized views of various scenarios; on the other hand, the present invention searches for prompt word examples that meet the current scenario from the vector database through the retrieval enhancement mechanism, promotes the model performance in different scenarios, thereby improving the response of the big model and obtaining better results. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] Figure 1 The figure is a flow chart of a materialized view selection method in an embodiment of the present invention.

[0036] Figure 2 It is a structural block diagram of a materialized view selection method system in an embodiment of the present invention.

[0037] Figure 3 The present invention is a schematic diagram of the structure of an electronic device implemented according to the method of the present invention. DETAILED DESCRIPTION

[0038] In order to describe the present invention more specifically, the technical solution of the present invention is described in detail below in conjunction with the accompanying drawings and specific implementation methods.

[0039] like Figure 1 As shown, the materialized view selection method based on the large model in this embodiment includes the following steps:

[0040] Step S1: Obtain a query statement and a corresponding query plan, parse the query plan, obtain the semantic information and structural information involved in each operator and the number of data records generated by the operator, and thus obtain a parsed query plan.

[0041] This implementation method extracts the query plan from the query execution log, parses the query plan, and obtains the semantic information and structural information of each operator as well as the execution time and execution cost generated by the operator. The specific process is as follows:

[0042] S11: extracting query execution logs to obtain query plans of each query statement (i.e., query SQL, hereinafter referred to as query), the execution time and execution cost generated by each operator in the query plan, and the number of data records generated by each operator.

[0043] S12: Parse the query plan, extract the types of all operators in the query plan, the conditional predicates involved in the operators, and the column information of the table included in the conditional predicates involved, so as to construct a training sample set.

[0044] Step S2: The parsed query plan is divided into several clusters through a clustering-based classification module, and each cluster is then used as a set.

[0045] This implementation uses a clustering algorithm to cluster queries with the same structure and similar operator types according to the similarity between the original queries. The specific process is as follows:

[0046] S21: Generate an embedding vector for each query statement based on the semantic information and structural information of each operator and the execution time and execution cost generated by the operator.

[0047] S22: Each embedded vector is input into the clustering module, and queries of the same type are clustered together according to the similarity between the vectors to generate several clusters.

[0048] Step S3: Take each cluster as a group, parse the text features of each group and input them into the embedding model. The embedding model maps the features of each group into a high-dimensional space and generates several embedding vectors.

[0049] Step S4: The embedded vector is input into the retrieval enhancement module, which matches the embedded vector with all vectors in the vector database, efficiently selects the top k vectors that best meet the conditions, and selects some inherent rules to convert the top k vectors into text and rule connections to generate prompt words.

[0050] This implementation uses a search enhancement module to match appropriate examples for each set of queries and retrieve vectors that meet the conditions from the vector database. The specific process is as follows:

[0051] S41: Collect query-materialized view cases on the network, use the embedding module to turn the examples into vectors, and store all vectors in the vector database.

[0052] S42: For each group of clustering results, use the embedding module to convert it into a vector, retrieve the vectors that meet the conditions from the vector database, calculate the similarity between the vectors, and sort them according to the similarity relationship.

[0053] S43: Select the first k vectors that meet the conditions from the similarity sorting results and convert them into text information; further check the relevance, obtain the semantic information, structural information of each operator and the execution time and execution cost generated by the operator through the parsing module, and find m vectors that meet the conditions through greedy matching.

[0054] S44: Select the rule of the materialized view field, use it as part of the prompt word, and combine it with the materialized view selection prompt words corresponding to the m vectors in the previous step to generate a complete prompt word.

[0055] Step S5: Input the prompt words into the fine-tuned large model. The large model generates the first round of results according to the predetermined output format, parses the generated content, extracts valid information, and obtains the materialized view and the rewritten query.

[0056] This implementation method inputs the prompt word into the fine-tuned large model to generate the first round of results. The specific process is as follows:

[0057] S51: Freeze part of the network layer of the large model and use the generated samples (SQL and natural language descriptions of some queries and materialized views) to fine-tune the large model.

[0058] S52: The prompt word is input into the large model, and the output result is obtained after the model processing. The parsing module matches the output result and extracts the materialized view and the rewritten query.

[0059] Step S6: The materialized view and the rewritten query are verified on the underlying database through the analysis and correction module, and the semantic errors and syntactic errors are fed back to the big model for correction, so as to achieve the correct result through continuous interaction with the big model.

[0060] This implementation method analyzes the correctness of the analysis results of the error correction module and continuously interacts with the large model. The specific process is as follows:

[0061] S61: Analyze the syntax and semantics of the materialized view and check the correctness of the materialized view by connecting to the underlying database.

[0062] S62: Analyze the rationality of the materialized view and detect its rationality by comparing the cost of the materialized view with the original query storage size.

[0063] S63: Analyze the syntax and semantics of the rewritten query, and detect the correctness of the rewritten query by connecting to the underlying database.

[0064] S64: Check the adaptability of the rewritten query by comparing it with the original query result to compare the consistency of the result.

[0065] S65: If an error is detected, the process shown in step S5 is repeated until the materialized view is successfully created and rewritten or it is determined that the materialized view cannot be rewritten.

[0066] Step S7: Collect all generated materialized views and rewritten queries, use the materialized view selection module, consider the benefits and costs of the materialized views, and obtain the top k optimal views and several rewritten queries.

[0067] This implementation uses a materialized view selection module to obtain the optimal top k views and several rewritten queries. The specific process is as follows:

[0068] S71: For all queries in each query block, select the first K materialized views that meet the conditions (the single estimated storage size does not exceed max) and generate MV-Unions.

[0069] S72: For each MV-Union, match all query SQLs that it can cover, and accumulate all benefits by subtracting the time after rewriting from the time before rewriting.

[0070] S73: By calculating the cumulative estimated benefit / estimated storage size, each MV-Union is sorted in descending order, and the first N MV-Unions that meet the space constraint M are selected.

[0071] S74: Get all the materialized views of the first N MV-Unions and the queries rewritten by the materialized views.

[0072] Corresponding to the aforementioned embodiment of the materialized view selection method based on a large model, the present invention also provides an embodiment of a system of the materialized view selection method based on a large model.

[0073] like Figure 2 As shown, the materialized view selection system based on the large model in this embodiment includes:

[0074] A query plan parsing module is used to obtain a query statement and a corresponding query plan, parse the query plan, and obtain a parsed query plan;

[0075] A clustering module, used to classify the parsed query plan into several clusters through a clustering-based classification model;

[0076] The embedding module is used to input each cluster as a group into the embedding model to generate several embedding vectors;

[0077] The retrieval enhancement module is used to input the embedded vector into the retrieval enhancement module, match the first k suitable vectors from the vector database, convert them into text and generate prompt words;

[0078] The large language model is used to input the prompt words into the fine-tuned large model to generate the first round of results, parse the content to obtain the materialized view and the rewritten query;

[0079] The error analysis module is used to analyze the materialized view and the rewritten query through the error analysis module to check the correctness of the results and continuously interact with the big model to achieve the correct results.

[0080] The materialized view selection module is used to collect and generate all materialized views and rewritten queries, and obtain the optimal top k views and several rewritten queries by using the materialized view selection module.

[0081] Regarding the system in the above embodiment, the specific manner in which each module performs operations has been described in detail in the embodiment of the method, and will not be elaborated here.

[0082] Accordingly, the present invention also provides an electronic device, including a processor and a memory, wherein the memory is used to store a computer program, and the processor is used to execute the computer program to implement the above-mentioned materialized view selection method based on a large model. Figure 3 The following is a hardware structure of any electronic device with data processing capability where the materialized view selection system based on a large model provided by an embodiment of the present invention is located, except that Figure 3 In addition to the processor, memory and network interface shown, the electronic device with data processing capability in which the system in the embodiment is located may also include other hardware according to the actual function of the electronic device, which will not be described in detail.

[0083] Accordingly, the present invention also provides a computer-readable storage medium, on which computer instructions are stored, and when the instructions are executed by a processor, the materialized view selection method based on a large model as described above is implemented. The computer-readable storage medium can be an internal storage unit of the aforementioned electronic device with data processing capability, such as a hard disk or a memory; or it can be other external storage devices, such as a plug-in hard disk, a smart memory card (Smart Media Card, SMC), an SD card, a flash card (Flash Card), etc. equipped on the device. The computer-readable storage medium can also include both an internal storage unit of the electronic device with data processing capability and an external storage device, which is used to store the computer program and other programs and data required by the electronic device with data processing capability, and can also be used to temporarily store data that has been output or is to be output.

[0084] The above description of the embodiments is to facilitate the understanding and application of the present invention by those skilled in the art. It is obvious that those skilled in the art can easily make various modifications to the above embodiments and apply the general principles described herein to other embodiments without creative work. Therefore, the present invention is not limited to the above embodiments. Improvements and modifications made by those skilled in the art to the present invention based on the disclosure of the present invention should be within the protection scope of the present invention.

Claims

1. A materialized view selection method based on a large model comprises the following steps: (1) Obtain query SQL and parse its query plan; (2) All query SQLs are divided into several clusters through clustering based on the information obtained from the analysis; (3) Generate prompt words for each cluster. Specifically: first, collect some sample information of query SQL-materialized view from the Internet, convert these sample information into vectors and store them in the vector database; for any cluster, randomly select a query SQL from it, calculate the similarity between the embedding vector of the query SQL and each vector in the vector database, select several vectors from the vector database through similarity sorting and greedy matching, and use the sample information corresponding to these vectors as the prompt words of the materialized view, then select some rules in the materialized view field and use them as part of the prompt words, and finally combine these two parts of the prompt words as the prompt words of the entire cluster; (4) Generate a materialized view for each cluster and query SQL rewritten by the materialized view using the large language model according to the prompt word. Specifically, first obtain the large language model and fine-tune it to adapt it to the materialized view extraction application; then, for any cluster, input the prompt word of the cluster into the fine-tuned large language model, and obtain the materialized view of the cluster and query SQL rewritten by the materialized view after model processing; (5) Verify and correct errors in the materialized views generated by the large language model and the rewritten query SQL; (6) Collect the materialized views of all clusters and the rewritten query SQL, consider the benefits and costs of the materialized views, and select several better materialized views and the query SQL they cover as output.

2. The method for selecting a materialized view based on a large model according to claim 1, characterized in that: The specific implementation method of step (1) is as follows: first, the query SQL and its corresponding query plan, the execution time, execution cost and number of data records generated by each operator in the query plan are extracted from the query execution log; then, the query plan is parsed to obtain the type of each operator in the query plan, the conditional predicates involved, and the column information of the table contained in the conditional predicates.

3. The materialized view selection method based on a large model according to claim 1, characterized in that: The specific implementation method of step (2) is as follows: first, based on the execution time, execution cost, number of data records, type of each operator, conditional predicates involved, and column information of the table contained in the conditional predicates, an embedding vector of the query SQL is generated; then, by calculating the similarity between the embedding vectors, query SQLs of the same structure and similar type are clustered together, thereby generating a number of clusters.

4. The method for selecting a materialized view based on a large model according to claim 1, characterized in that: The specific implementation of step (5) is as follows: 5.1 Analyze the syntax and semantics of the materialized view and check the correctness of the materialized view by connecting to the underlying database; 5.2 Analyze the cost of the materialized view and compare it with the original query storage size to detect the rationality of the materialized view; 5.3 Analyze the syntax and semantics of the rewritten query SQL and check the correctness of the query SQL by connecting to the underlying database; 5.4 Check the adaptability of the rewritten query SQL by comparing it with the original query results to see if the results are consistent; 5.5 If an error is detected in the above steps, the error information is fed back to the large language model and step (4) is re-executed until the generated materialized view and query SQL are correct.

5. The method for selecting a materialized view based on a large model according to claim 1, characterized in that: The specific implementation method of step (6) is as follows: first, for any cluster, select the first several materialized views whose single estimated storage size meets the constraint condition from the materialized views of the cluster to form MV-Unions; then, for any MV-Unions, match all query SQLs covered by them, calculate the time difference before and after the rewriting of these query SQLs and accumulate them as the estimated benefit of the MV-Unions, and divide the estimated benefit by the estimated storage size of the MV-Unions, and then sort all MV-Unions according to the ratio, and select the first several MV-Unions that meet the total storage space constraint condition; finally, the materialized views of these MV-Unions and the query SQLs covered by them are used as the final output.

6. A materialized view selection system based on a large model, the system executing a materialized view selection method based on a large model as claimed in any one of claims 1 to 5, characterized in that: The system includes: The query plan parsing module is used to obtain the query SQL and parse its query plan; The clustering module divides all query SQLs into several clusters based on the information obtained from the analysis; The embedding module is used to generate the embedding vector of the query SQL based on the information obtained from the analysis; The retrieval enhancement module is used to generate prompt words for each cluster; The large language model uses the prompt word as input to generate a materialized view for each cluster and the query SQL rewritten by the materialized view; The analysis and error correction module is used to verify and correct the materialized views generated by the large language model and the rewritten query SQL; The materialized view selection module is used to collect the materialized views of all clusters and the rewritten query SQL, consider the benefits and costs of the materialized views, and select several better materialized views and the query SQL they cover as output.

7. A computer device comprising a memory and a processor, characterized in that: The memory stores a computer program, and the processor is used to execute the computer program to implement a materialized view selection method based on a large model as claimed in any one of claims 1 to 5.

8. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, it implements a materialized view selection method based on a large model as claimed in any one of claims 1 to 5.

Images