Query optimization method, statistical information prediction model training method and equipment
Through the pre-trained statistical information prediction model, the database statistics information at the current moment is predicted using historical statistical information, which solves the problem of inaccurate evaluation of database query optimizer, improves the accuracy of selection of query execution plans, and avoids the occurrence of system performance problems. .
Patent Information
- Application Number
- CN202410955045.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-16
- Publication Date
- 2024-12-03
AI Technical Summary
Due to the untimely update of statistical information by the existing database query optimizer, the execution cost evaluation of the query execution plan is inaccurate, and it is difficult to select a plan with the minimum execution cost, causing system performance problems.
Through the pre-trained statistical information prediction model, the statistical information at the current moment is predicted using historical statistical information, thereby evaluating the execution cost of query execution plans and selecting the optimal execution plan.
It improves the evaluation accuracy of query execution plan, reduces the occurrence of system performance problems, and saves resource consumption for statistical information updates.
Smart Images

Figure CN119066088A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of database technology, and in particular to a query optimization method, a statistical information prediction model training method and a device. Background Art
[0002] Database systems usually use query optimizers to evaluate the execution costs corresponding to different query execution plans, and ensure efficient database query operations by selecting the query execution plan with the minimum execution cost.
[0003] The query optimizer generally uses the statistical information in the database system to evaluate the execution cost of each query execution plan. However, since the update of the above statistical information requires physical reading and sorting of the information in the database table, the entire operation consumes a lot of hardware resources. Therefore, in actual applications, it is difficult to update the statistical information in the database system in real time. As a result, the execution cost of each query execution plan evaluated by the query optimizer using the statistical information is not accurate, and it is difficult to accurately select the query execution plan with the minimum execution cost, which can easily cause system performance problems. Summary of the invention
[0004] The present application provides a query optimization method, a statistical information prediction model training method and a device, which can solve the technical problem that the execution cost of each query execution plan evaluated by the query optimizer in the prior art is not accurate, resulting in difficulty in accurately selecting the query execution plan with the minimum execution cost.
[0005] In a first aspect, the present application provides a query optimization method, the method comprising:
[0006] Upon receiving a query request, determining an update time of the last update of the statistical information of the database, and actual statistical information at the update time;
[0007] According to the actual statistical information at the updating time, using the pre-trained statistical information prediction model, predicting the statistical information at the current time;
[0008] Determine the execution cost of each query execution plan corresponding to the query request according to the predicted statistical information at the current moment;
[0009] A target query execution plan corresponding to the query request is determined according to the execution costs of the query execution plans.
[0010] In some embodiments, the method further comprises:
[0011] According to the preset statistical information collection rules, the statistical information of the database is collected at multiple different times;
[0012] Preprocessing the collected statistical information; the preprocessing includes adding a timestamp and an identification bit to the collected statistical information, the identification bit is used to identify the collected statistical information as historical statistical information of the database;
[0013] Store preprocessed statistics in time series.
[0014] In some implementations, the statistical information collection rule includes: collecting statistical information of the database when the database completes updating;
[0015] The statistical information of the database is collected at multiple different collection times, including:
[0016] When it is detected that the database is updated each time, statistical information of the database is collected at the time of completion of the update.
[0017] In a second aspect, the present application provides a statistical information prediction model training method, the method comprising:
[0018] Acquire a training data set, wherein the training data set includes historical statistical information stored in a database at multiple historical moments;
[0019] The training data set is used to train the model to be trained to obtain a statistical information prediction model; the statistical information prediction model is used to predict the statistical information of the database at a second moment based on the statistical information of the database at a first moment, and the first moment is before the second moment.
[0020] In some embodiments, the method further comprises:
[0021] When it is detected that the statistical information in the database has been updated, the update time of this update and the actual statistical information after this update are obtained;
[0022] Get the update time of the last update and the actual statistics after the last update;
[0023] According to the update time of the last update and the actual statistical information after the last update, using the statistical information prediction model, predict the predicted statistical information after this update;
[0024] The statistical information prediction model is adjusted according to the difference between the actual statistical information after this update and the predicted statistical information after this update.
[0025] In a third aspect, the present application provides a query optimization device, the device comprising:
[0026] A determination module, configured to determine, upon receiving a query request, an update time of the last update of the statistical information of the database and actual statistical information at the update time;
[0027] A prediction module, configured to predict the statistical information at the current moment according to the actual statistical information at the updating moment using a pre-trained statistical information prediction model;
[0028] An evaluation module, used to determine the execution cost of each query execution plan corresponding to the query request according to the predicted statistical information at the current moment;
[0029] The selection module is used to determine a target query execution plan corresponding to the query request according to the execution costs of the various query execution plans.
[0030] In a fourth aspect, the present application provides a statistical information prediction model training device, the device comprising:
[0031] An acquisition module, used to acquire a training data set, wherein the training data set includes historical statistical information stored in a database at multiple historical moments;
[0032] A model training module is used to train a model to be trained using the training data set to obtain a statistical information prediction model; the statistical information prediction model is used to predict the statistical information of the database at a second moment based on the statistical information of the database at a first moment, and the first moment is before the second moment.
[0033] In a fifth aspect, an embodiment of the present application provides an electronic device, comprising: at least one processor and a memory; the memory stores computer-executable instructions; the at least one processor executes the computer-executable instructions stored in the memory, so that the at least one processor executes the query optimization method described in the first aspect and various possible designs of the first aspect above;
[0034] Alternatively, the at least one processor executes the computer-executable instructions stored in the memory, so that the at least one processor executes the statistical information prediction model training method described in the second aspect and various possible designs of the second aspect.
[0035] In a sixth aspect, an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and when a processor executes the computer-executable instructions, the query optimization method described in the first aspect and various possible designs of the first aspect is implemented;
[0036] Alternatively, when the processor executes the computer-executable instructions, the statistical information prediction model training method described in the second aspect and various possible designs of the second aspect is implemented.
[0037] In a seventh aspect, an embodiment of the present application provides a computer program product, including a computer program, wherein when the computer program is executed by a processor, the query optimization method described in the first aspect and various possible designs of the first aspect is implemented;
[0038] Alternatively, when the computer program is executed by a processor, it implements the statistical information prediction model training method described in the second aspect and various possible designs of the second aspect.
[0039] The query optimization method, statistical information prediction model training method and device provided in the embodiments of the present application can use the historical statistical information stored in the database at multiple historical moments in advance to train the training model and obtain the statistical information prediction model; when a query request is received, the statistical information of the database can be predicted based on the statistical information prediction model to predict the statistical information of the database at the current moment, and the execution cost of each query execution plan corresponding to the above query request can be evaluated based on the predicted statistical information, which can effectively improve the accuracy of the evaluation result, and then more accurately select the query execution plan with the minimum execution cost to prevent system performance problems from being caused. BRIEF DESCRIPTION OF THE DRAWINGS
[0040] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.
[0041] Figure 1 A flowchart of a statistical information prediction model training method provided in an embodiment of the present application;
[0042] Figure 2 A flowchart of a query optimization method provided in an embodiment of the present application;
[0043] Figure 3 A schematic diagram of a program module of a query optimization device provided in an embodiment of the present application;
[0044] Figure 4 A schematic diagram of a program module of a statistical information prediction model training device provided in an embodiment of the present application;
[0045] Figure 5 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application.
[0046] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION
[0047] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.
[0048] In the embodiments of the present application, words such as "first" and "second" are used to distinguish between identical or similar items with substantially identical functions and effects. For example, the first indication information and the second indication information are only used to distinguish between different indication information, and do not limit their order. Those skilled in the art can understand that words such as "first" and "second" do not limit the quantity and execution order, and words such as "first" and "second" do not necessarily limit them to be different.
[0049] In the embodiments of the present application, "at least one" refers to one or more, and "more than one" refers to two or more. "And / or" describes the association relationship of associated objects, indicating that three relationships may exist. For example, A and / or B can represent: A exists alone, A and B exist at the same time, and B exists alone, where A and B can be singular or plural. The character " / " generally indicates that the previous and next associated objects are in an "or" relationship. "At least one of the following" or similar expressions refers to any combination of these items, including any combination of single or plural items. For example, at least one of a, b, or c can represent: a, b, c, ab, a--c, bc, or abc, where a, b, c can be single or multiple.
[0050] It should be noted that in the embodiments of the present application, words such as "exemplary" or "for example" are used to indicate examples, illustrations or descriptions. Any embodiment or design described as "exemplary" or "for example" in the present application should not be interpreted as being more preferred or more advantageous than other embodiments or designs. Specifically, the use of words such as "exemplary" or "for example" is intended to present related concepts in a specific way.
[0051] The following is an explanation of some of the terms involved in the embodiments of the present application:
[0052] 1. Query optimizer: It mainly performs internal optimization on user query requests. It can decide which indexes and association algorithms to use for specific query requests to generate (or reuse) efficient query execution plans, transmit them to the storage engine to operate data, and finally return the results to the user.
[0053] 2 Statistical information: Database statistical information mainly describes the size, scale, data distribution status, etc. of tables and indexes in the database. This includes but is not limited to the number of rows and blocks in the table, the average size of each row, the leafblocks of the index, the number of rows in the index field, the size of different values, etc.
[0054] Statistical information can include macro data in many aspects, such as the number of data pages occupied by the table, the number of data pages actually used, the height of the B-tree (for clustered index tables), etc. In addition, statistical information can also be classified as needed, such as table statistics, index statistics, column statistics, system statistics, data dictionary statistics, and internal object statistics.
[0055] 3. Query execution plan: A description of how the database executes SQL statements, including how to execute the query, whether to query through indexes, and index information.
[0056] 4. Query path: It is the specific implementation method in the query execution plan, that is, the path by which data is accessed and processed. Different query paths may lead to different performance. For example, a query may obtain data through a full table scan, or it may quickly locate data through an index scan.
[0057] The query optimizer plays a vital role in the database system and has a significant impact on the performance of the database. The same query statement may have execution time differences of hours due to different query execution plans. Therefore, the optimizer's task is to select the optimal query execution plan to improve query efficiency.
[0058] Database statistics play a key role in database performance optimization and query execution plan selection. It enables the database to respond to query requests more effectively and improve query efficiency.
[0059] In some solutions, the main function of statistical information is to provide data for the query optimizer, enabling it to calculate the execution costs of various query execution plans under different access paths and different join modes, and select the query execution plan with the lowest execution cost to execute the query operation.
[0060] Database statistics updates are the basis for database performance tuning and the correct operation of the query optimizer. The database optimizer relies on these statistics to evaluate the execution costs of different query execution plans and select the best query execution plan based on the execution costs of each query execution plan.
[0061] In order to make the right choice among many query execution plans, the query optimizer of the database system must estimate the execution cost of each query execution plan. However, before executing a query according to a query execution plan, it is impossible to accurately calculate the execution cost of the query execution plan. Therefore, the optimizer must use the statistical information in the database system to estimate the execution cost of the query execution plan. Among them, the statistical information of the database system is stored in the system table of the database.
[0062] In actual applications, if the database wants to maintain accurate statistical information, then whenever the table data is modified, the corresponding statistical values must also be updated. This update will bring a great cost, so the database system does not update the statistical information every time it is modified. For example, when the relevant data of a table is modified or deleted, the system's statistical information is not automatically updated. For example: if you use the delete command to delete a record in a database table, after the deletion is completed, when you search for the record information about the table in the system table, you will find that the number of record rows about the database table in the system table has not changed. Therefore, the statistical data used to select the query execution plan may not be completely correct.
[0063] In some solutions, database products record statistics through the following or similar system tables:
[0064] Field table: describes each field in the database. The "field maximum value" and "field minimum value" store the second smallest and second largest values of the fields in each table of the database. These values are effective only after the field is indexed and the statistical information update is run. For example, for field values 1, 2, 3, 4, 5, 4 is the second largest value and 2 is the second smallest value.
[0065] Index information table: describes the index information in the database. Each index in the database corresponds to a record. After modifying the index, only the statistical information can make its changes reflected in this table.
[0066] Data distribution table: stores data distribution information and is the core of statistical update processing. This table provides detailed table field information for the optimizer to optimize the execution of SQL Select statements.
[0067] The data distribution process is to sample the actual data in one or more columns and then organize them into a series of so-called bulkets. There is a fixed number of boxes used for the sampled data. The number of boxes and the exact range of data contained in each box depend on the value entered for the accuracy resolution. Usually the data resolution option of the product determines the number of distribution boxes generated and the exact range of data in the box. If the resolution value is reduced, the number of records in each box will be smaller, the number of boxes will increase, and the query optimizer will be more efficient when using them.
[0068] As shown in Table 1, Table 1 is a data record table 1 provided in an embodiment of the present application.
[0069] Table 1: Data Record Table 1
[0070]
[0071] Assuming that the SQL query condition is any date greater than 2017 / 3 / 2 and less than 2017 / 5 / 18, there are 77 calendar days between these two boundary values, and the number of records is fixed at N, so the expected number of returned records is approximately N / 77 records. This shows that statistical information is very critical for the database to evaluate the amount of data returned by the query path. The more accurate the statistical information, the more accurate the execution cost of different query execution plans evaluated by the query optimizer based on these statistical information, which can make the final selected query execution plan have higher execution efficiency.
[0072] However, updating statistical information is an operation that consumes CPU, memory, and I / O interface resources, and requires physical reading and sorting of database table information, which makes it impossible to update statistical information in the database system in real time. In addition, in order to ensure system stability and continuity, the update of statistical information also needs to avoid business peaks and other complex operations during actual operation and maintenance, which significantly reduces the accuracy of statistical information.
[0073] Secondly, in actual situations, there may be situations where the full statistical information update cannot be performed due to the large amount of data, and incremental statistical information updates have to be performed. For example, assuming that a table has 1 billion records, and the trigger condition for incremental statistical updates is 1%, it means that the statistical information update will be triggered only after the number of records in the table changes to 10 million records. In fact, this is a very demanding condition that requires a long period of data change accumulation to achieve. Before the incremental condition meets the trigger condition operation, the statistical information update is completely missing. During this period, the statistical information will be distorted. If the trigger condition threshold is lowered, although more timely statistical information updates can be obtained, more frequent statistical information updates mean higher resource consumption and more frequent impacts on the business.
[0074] The distortion of statistical information will cause the above query optimizer to be unable to accurately estimate the execution cost of each query execution plan, and thus unable to accurately select the query execution plan with the minimum execution cost, which can easily cause query path errors in SQL statements and cause system performance problems.
[0075] In response to the above technical problems, the embodiments of the present application provide a query optimization method, a statistical information prediction model training method and a device, which can pre-train a model to be trained by using historical statistical information stored in a database at multiple historical moments to obtain a statistical information prediction model; when a query request is received, the statistical information of the database can be predicted based on the statistical information prediction model to predict the statistical information of the database at the current moment, and the execution cost of each query execution plan corresponding to the above query request can be evaluated based on the predicted statistical information, which can effectively improve the accuracy of the evaluation result, and further more accurately select the query execution plan with the minimum execution cost to prevent system performance problems from being caused.
[0076] The technical solution of the present application and how the technical solution of the present application solves the above-mentioned technical problems are described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.
[0077] Reference Figure 1 , Figure 1 This is a flow chart of a statistical information prediction model training method provided in an embodiment of the present application. In some embodiments, the statistical information prediction model training method includes:
[0078] S101. Obtain a training data set, where the training data set includes historical statistical information stored in a database at multiple historical moments.
[0079] In some implementations, the training data set may include multiple training data, which are statistical information corresponding to multiple different moments in the past and stored in a database.
[0080] Optionally, the above statistical information may include information used to describe the size, scale, data distribution status, etc. of tables and indexes in the database, including but not limited to the number of rows and blocks in the table, the average size of each row, the leaf blocks of the index, the number of rows of the index field, the size of different values, etc.
[0081] Optionally, the above statistical information may also include macro data in multiple aspects, such as the number of data pages occupied by the table, the number of data pages actually used, the height of the B-tree (for clustered index tables), etc. In addition, the statistical information may also be classified as needed, such as table statistics, index statistics, column statistics, system statistics, data dictionary statistics, and internal object statistics.
[0082] In some implementations, obtaining the training data set may include the following steps:
[0083] Determine the data source: Determine from which database or databases to obtain historical statistics.
[0084] Select historical time nodes: Select a series of historical time nodes based on your needs. These time nodes should cover enough data changes so that the model can learn the changing trends and patterns of statistical information.
[0085] In some implementations, the first day of each month in the past few years, or the last day of each quarter, etc. may be selected.
[0086] In some implementations, several update moments may be selected from the update moments of multiple database updates in the past period of time as historical time nodes.
[0087] Extract historical statistical information: At each selected historical time node, extract relevant statistical information from the database.
[0088] Data cleaning and preprocessing: The extracted historical statistical information is cleaned and preprocessed to remove outliers, missing values or duplicate data. At the same time, the data can also be standardized or normalized so that the model can better handle data of different dimensions.
[0089] Constructing a training data set: Organize the cleaned and preprocessed historical statistical information into a training data set in a suitable format. This data set usually includes input features (i.e. historical time nodes and related statistical information) and target variables (i.e. predicted statistical information).
[0090] S102. Use the training data set to train the model to be trained to obtain a statistical information prediction model; the statistical information prediction model is used to predict the statistical information of the database at the second moment based on the statistical information of the database at the first moment.
[0091] The first moment is before the second moment.
[0092] In some implementations, the training of the model to be trained using the training data set may include the following steps:
[0093] Select model type: Optionally, you can choose to use a supervised learning algorithm to build the model to be trained.
[0094] Exemplarily, the above-mentioned supervised learning algorithm may select a time series prediction algorithm based on a support vector machine (Support Vector Machine, SVM).
[0095] Split training set and validation set: In order to evaluate the performance of the model and prevent overfitting, the training data set can be divided into training set and validation set. Optionally, most of the data (such as 80%) can be used as a training set and the remaining part (such as 20%) can be used as a validation set.
[0096] Model training: The selected model is trained using the training set. During the training process, the model adjusts its internal parameters based on the relationship between the input features and the target variable to minimize the prediction error.
[0097] Model evaluation and optimization: Use the validation set to evaluate the trained model and calculate its prediction accuracy, recall rate, F1 score and other indicators. Based on the evaluation results, the model can be optimized, such as adjusting the model parameters, changing the model structure or trying to use different model types.
[0098] Obtaining a statistical information prediction model: After training and optimization, a model is obtained that can predict future statistical information based on historical statistical information.
[0099] In some embodiments, the statistical information prediction model can also use the backpropagation (BP) algorithm. Among them, the learning process of the BP neural network is to gradually improve the accuracy of the network by continuously adjusting the weights and bias values. The training process of the BP neural network can be divided into two stages: forward propagation and backpropagation. In forward propagation, the input signal passes through the neurons of each layer and finally produces an output result; in backpropagation, the error between the output result and the expected result is backpropagated back to the network, and the weights and bias values of the neurons of each layer are adjusted according to the error size, so that the error is gradually reduced, thereby improving the accuracy of the network.
[0100] In some embodiments, subsequent algorithm optimization may use a convolutional neural network (CNN) time series prediction algorithm.
[0101] It is understandable that in the embodiments of the present application, by using a supervised learning algorithm to predict database statistical information, the existing statistical information collection method that completely relies on actual calculations (data sorting, grouping statistics) can be replaced. By training the supervised learning algorithm to grasp the law of statistical information changes, statistical information can be predicted simply and quickly, greatly saving the resources and time required for data reading, sorting and statistics in the actual calculation process.
[0102] Optionally, the training data and loss function validation data required for supervised learning can use historical data from database statistics or can be re-collected as needed.
[0103] The statistical information prediction model training method provided in the embodiment of the present application can pre-train the model to be trained by using the historical statistical information stored in the database at multiple historical moments to obtain a statistical information prediction model. The statistical information prediction model can predict the statistical information of the database at any moment. By predicting the statistical information of the database at any moment, it can help the query optimizer to more accurately evaluate the execution cost of each query execution plan.
[0104] Reference Figure 2 , Figure 2 This is a flowchart of a query optimization method provided in an embodiment of the present application. In some embodiments, the query optimization method includes:
[0105] S201. When a query request is received, determine the update time of the most recent update of the statistical information of the database and the actual statistical information at the update time.
[0106] In some implementations, when a database receives a query request, the query optimizer first needs to determine the time when the database's statistical information was last updated. This information is usually stored in the metadata of the database. At the same time, the optimizer also needs to obtain the actual statistical information at the update time, which is the statistical information after the database was last updated.
[0107] S202: Based on the actual statistical information at the above-mentioned updating moment, the statistical information at the current moment is predicted using a pre-trained statistical information prediction model.
[0108] In some implementations, after obtaining the most recently updated statistical information, the query optimizer may use a pre-trained statistical information prediction model to predict the statistical information at the current moment.
[0109] Among them, the above statistical information prediction model is based on Figure 1 The statistical information prediction model is trained by the statistical information prediction model training method shown.
[0110] It can be understood that the statistical information prediction model used for the above-mentioned statistical information can learn the data change pattern from historical statistical information and predict future statistical information based on it. Therefore, in some embodiments, after determining the actual statistical information at the above-mentioned update moment, the statistical information prediction model can be used to predict the statistical information of the database at the current moment.
[0111] S203: Determine the execution cost of each query execution plan corresponding to the query request according to the predicted statistical information at the current moment.
[0112] In some implementations, after the statistical information at the current moment is predicted, a query optimizer may be used to evaluate the execution costs of each possible query execution plan corresponding to the query request.
[0113] Optionally, the above execution cost may include the number of I / O operations, CPU time, network transmission volume, etc., which can be calculated through the predicted statistical information and the characteristics of the query execution plan.
[0114] S204: Determine a target query execution plan corresponding to the query request according to the execution costs of each query execution plan.
[0115] In some implementations, the query optimizer may select an optimal query execution plan as a target query execution plan according to the execution costs of various query execution plans.
[0116] Optionally, the above selection process may be based on a cost model, that is, a query execution plan with the minimum execution cost is selected as the target query execution plan.
[0117] In some implementations, the query optimizer may generate an execution plan tree, and prune and optimize the tree according to the estimated result of the execution cost, and finally obtain an optimal execution plan.
[0118] It is understandable that in the embodiments of the present application, it is possible to predict statistical information without changing the basic architecture of existing database products.
[0119] The query optimization method provided in the embodiment of the present application predicts the statistical information of the database at the current moment by using the statistical information prediction model, so as to find a relatively optimal query execution plan for the query request when the database cannot update the statistical information in real time. This can not only reduce the overhead of updating the statistical information, but also improve the efficiency and accuracy of query optimization.
[0120] Based on the contents described in the above embodiments, in some embodiments of the present application, the above query optimization method further includes:
[0121] According to the preset statistical information collection rules, the statistical information of the database is collected at multiple different times, the collected statistical information is preprocessed, and the preprocessed statistical information is stored in a time series. Optionally, the preprocessing includes adding a timestamp and a flag to the collected statistical information, and the flag is used to identify the collected statistical information as historical statistical information of the database.
[0122] In some implementations, the statistical information collection rule includes: collecting statistical information of the database when the database is updated.
[0123] In some implementations, each time it is detected that the database has been updated, statistical information of the database is collected at the time of update completion.
[0124] As shown in Table 2, Table 2 is a data record table 2 provided in an embodiment of the present application.
[0125] Table 2: Data record table 2
[0126]
[0127]
[0128] In an embodiment of the present application, storage of historical statistical information can be added to the database, wherein the specific collection time point can be used only for illustration and does not need to be actually stored. The collection time point of the historical statistical information can be recorded by counting the timestamp in the historical statistical information.
[0129] In addition, by adding an identification bit, the data type of the statistical information can be distinguished. Among them, the training data can be used as the historical statistical information of the subsequent training statistical information prediction model; the predicted data is the statistical information of a certain time node predicted by the trained statistical information prediction model, and the BP data is the actual statistical information of the time node.
[0130] In some embodiments, the statistical information prediction model training method further includes:
[0131] When it is detected that the statistical information in the database has been updated, the update time of this update and the actual statistical information after this update (i.e. the above-mentioned BP data) are obtained; the update time of the last update and the actual statistical information after the last update are obtained; based on the update time of the last update and the actual statistical information after the last update, the statistical information prediction model is used to predict the predicted statistical information after this update (i.e. the above-mentioned predicted data); based on the difference between the actual statistical information after this update and the predicted statistical information after this update, the above-mentioned statistical information prediction model is adjusted.
[0132] In the implementation of this application, without changing the basic architecture of existing database products, the database can get rid of the method of actually reading statistical information that is completely dependent on high resource consumption, increase the use of supervised learning algorithms to predict the changing trends of statistical information through historical statistical information, and have the ability to adjust and optimize the supervised learning model parameters through the calculation of actual statistical information, so that the accuracy of the prediction results is continuously improved to ensure the continued accuracy of the database query path.
[0133] The statistical information prediction model training method provided in the embodiment of the present application uses a back-propagation adjustment algorithm and utilizes the collected actual statistical information and the predicted statistical information to continuously and automatically adjust the parameters of the statistical information prediction model, so that the accuracy of the prediction results is continuously improved, thereby ensuring the accuracy of the statistical information prediction.
[0134] Reference Figure 3 , Figure 3 A program module diagram of a query optimization device provided in an embodiment of the present application. In some embodiments, the query optimization device includes: a determination module 301, a prediction module 302, an evaluation module 303 and a selection module 304. Among them:
[0135] The determination module 301 is used to determine the update time of the most recent update of the statistical information of the database and the actual statistical information at the above update time when a query request is received.
[0136] The prediction module 302 is used to predict the statistical information at the current moment according to the actual statistical information at the above-mentioned update moment using a pre-trained statistical information prediction model.
[0137] The evaluation module 303 is used to determine the execution cost of each query execution plan corresponding to the query request according to the predicted statistical information at the current moment.
[0138] The selection module 304 is used to determine a target query execution plan corresponding to the query request according to the execution costs of each query execution plan.
[0139] In some embodiments, the above device further comprises a collection module, which is used to:
[0140] According to the preset statistical information collection rules, the statistical information of the database is collected at multiple different times; the collected statistical information is preprocessed; the preprocessing includes adding a timestamp and an identification bit to the collected statistical information, and the identification bit is used to identify the collected statistical information as historical statistical information of the database; the preprocessed statistical information is stored in a time series.
[0141] In some implementations, the statistical information collection rule includes: collecting statistical information of the database when the database is updated; the apparatus further includes a collection module for:
[0142] When it is detected that each time the database is updated, the statistical information of the database is collected at the time of completion of the update.
[0143] The query optimization device provided in the embodiment of the present application can be used to execute the technical solution of the query optimization method described in the above embodiment. Its implementation principle and technical effect are similar and will not be repeated here.
[0144] Reference Figure 4 , Figure 4 A schematic diagram of a program module of a statistical information prediction model training device provided in an embodiment of the present application. In some embodiments, the statistical information prediction model training device includes: an acquisition module 401 and a model training module 402. Among them:
[0145] The acquisition module 401 is used to acquire a training data set, where the training data set includes historical statistical information stored in a database at multiple historical moments.
[0146] The model training module 402 is used to train the model to be trained using the training data set to obtain a statistical information prediction model; the statistical information prediction model is used to predict the statistical information of the database at a second moment based on the statistical information of the database at a first moment; wherein the first moment is before the second moment.
[0147] In some embodiments, the statistical information prediction model training device further includes a back propagation adjustment module for:
[0148] When it is detected that the statistical information in the database has been updated, the update time of this update and the actual statistical information after this update are obtained; the update time of the last update and the actual statistical information after the last update are obtained; based on the update time of the last update and the actual statistical information after the last update, the statistical information prediction model is used to predict the predicted statistical information after this update; based on the difference between the actual statistical information after this update and the predicted statistical information after this update, the statistical information prediction model is adjusted.
[0149] The statistical information prediction model training device provided in the embodiment of the present application can be used to execute the technical solution of the statistical information prediction model training method described in the above embodiment. Its implementation principle and technical effect are similar and will not be repeated here.
[0150] It should be noted that it should be understood that the division of the various modules of the above device is only a division of logical functions. In actual implementation, they can be fully or partially integrated into one physical entity, or they can be physically separated. And these modules can all be implemented in the form of software called by processing elements; they can also be all implemented in the form of hardware; some modules can also be implemented in the form of software called by processing elements, and some modules can be implemented in the form of hardware. Each module can be a separately established processing element, or it can be integrated in a chip of the above device. In addition, it can also be stored in the memory of the above device in the form of program code, and called and executed by a processing element of the above device. The functions of each module. In addition, all or part of these modules can be integrated together, or they can be implemented independently. The processing element here can be an integrated circuit with signal processing capabilities. In the implementation process, each step of the above method or each module above can be completed by an integrated logic circuit of hardware in the processor element or instructions in the form of software.
[0151] Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present application. Figure 5 As shown, the electronic device may include: a transceiver 501 , a processor 502 , and a memory 503 .
[0152] The processor 502 can implement the technical solutions in the query optimization method or the statistical information prediction model training method provided in the above embodiments by executing the computer execution instructions stored in the memory.
[0153] Processor 502 can be a general-purpose processor, including a central processing unit CPU, a network processor (NP), etc.; it can also be a digital signal processor DSP, an application-specific integrated circuit ASIC, a field programmable gate array FPGA or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.
[0154] The memory 503 is connected to the processor 502 via a system bus and communicates with each other. The memory 503 is used to store computer-executable instructions.
[0155] The transceiver 501 may be used to obtain tasks to be executed and configuration information of the tasks to be executed.
[0156] The system bus can be a peripheral component interconnect (PCI) bus or an extended industry standard architecture (EISA) bus, etc. The system bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, only one thick line is used in the figure, but it does not mean that there is only one bus or one type of bus. The transceiver is used to realize the communication between the database access device and other computers (such as clients, read-write libraries, and read-only libraries). The memory may include random access memory (RAM) and may also include non-volatile memory.
[0157] An embodiment of the present application also provides a chip for running instructions, which is used to execute the technical solutions in the query optimization method or statistical information prediction model training method provided in the above embodiments.
[0158] An embodiment of the present application also provides a computer-readable storage medium, which stores computer instructions. When the computer instructions are executed on a computer, the computer executes the technical solutions in the query optimization method or statistical information prediction model training method provided in the above embodiments.
[0159] An embodiment of the present application also provides a computer program product, which includes a computer program stored in a computer-readable storage medium. At least one processor can read the computer program from the computer-readable storage medium. When at least one processor executes the computer program, it can implement the technical solutions in the query optimization method or statistical information prediction model training method provided in the above embodiments.
[0160] In the several embodiments provided in the present application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic, for example, the division of modules is only a logical function division, and there may be other division methods in actual implementation, such as multiple modules can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be an indirect coupling or communication connection through some interfaces, devices or modules, which can be electrical, mechanical or other forms.
[0161] The modules described as separate components may or may not be physically separated, and the components shown as modules may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the modules may be selected according to actual needs to implement the solution of this embodiment.
[0162] In addition, each functional module in each embodiment of the present application can be integrated into one processing unit, or each module can exist physically separately, or two or more modules can be integrated into one unit. The above-mentioned module-composed unit can be implemented in the form of hardware or in the form of hardware plus software functional units.
[0163] The above-mentioned integrated module implemented in the form of a software function module can be stored in a computer-readable storage medium. The above-mentioned software function module is stored in a storage medium, including a number of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) or a processor to perform some steps of the methods of various embodiments of the present application.
[0164] It should be understood that the processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. A general-purpose processor may be a microprocessor or the processor may be any conventional processor, etc. The steps of the method disclosed in the invention may be directly embodied as being executed by a hardware processor, or may be executed by a combination of hardware and software modules in the processor.
[0165] The memory may include a high-speed RAM memory, and may also include a non-volatile storage NVM, such as at least one disk memory, and may also be a USB flash drive, a mobile hard disk, a read-only memory, a magnetic disk or an optical disk, etc.
[0166] The bus may be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. The bus may be divided into an address bus, a data bus, a control bus, etc. For ease of representation, the bus in the drawings of the present application is not limited to only one bus or one type of bus.
[0167] The above storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic memory, flash memory, magnetic disk or optical disk. The storage medium can be any available medium that can be accessed by a general or special purpose computer.
[0168] An exemplary storage medium is coupled to a processor so that the processor can read information from the storage medium and write information to the storage medium. Of course, the storage medium can also be a component of the processor. The processor and the storage medium can be located in an application specific integrated circuit (ASIC). Of course, the processor and the storage medium can also exist as discrete components in an electronic control unit or a main control device.
[0169] Those skilled in the art can understand that all or part of the steps of implementing the above-mentioned method embodiments can be completed by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When the program is executed, the steps of the above-mentioned method embodiments are executed; and the aforementioned storage medium includes: ROM, RAM, magnetic disk or optical disk and other media that can store program codes.
[0170] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit it. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements 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 application.
Claims
1. A query optimization method, characterized in that: The method comprises: Upon receiving a query request, determining an update time of the last update of the statistical information of the database, and actual statistical information at the update time; According to the actual statistical information at the updating time, using a pre-trained statistical information prediction model, predicting the statistical information at the current time; Determine the execution cost of each query execution plan corresponding to the query request according to the predicted statistical information at the current moment; A target query execution plan corresponding to the query request is determined according to the execution costs of the query execution plans.
2. The method according to claim 1, characterized in that The method further comprises: According to the preset statistical information collection rules, the statistical information of the database is collected at multiple different times; Preprocessing the collected statistical information; the preprocessing includes adding a timestamp and an identification bit to the collected statistical information, the identification bit is used to identify the collected statistical information as historical statistical information of the database; Store preprocessed statistics in time series.
3. The method according to claim 2, characterized in that The statistical information collection rule includes: collecting statistical information of the database when the database is updated; The statistical information of the database is collected at multiple different collection times, including: When it is detected that the database is updated each time, statistical information of the database is collected at the time of completion of the update.
4. A statistical information prediction model training method, characterized in that: The method comprises: Acquire a training data set, wherein the training data set includes historical statistical information stored in a database at multiple historical moments; The training data set is used to train the model to be trained to obtain a statistical information prediction model; the statistical information prediction model is used to predict the statistical information of the database at a second moment based on the statistical information of the database at a first moment, and the first moment is before the second moment.
5. The method according to claim 4, characterized in that The method further comprises: When it is detected that the statistical information in the database has been updated, the update time of this update and the actual statistical information after this update are obtained; Get the update time of the last update and the actual statistics after the last update; According to the update time of the last update and the actual statistical information after the last update, using the statistical information prediction model, predict the predicted statistical information after this update; The statistical information prediction model is adjusted according to the difference between the actual statistical information after this update and the predicted statistical information after this update.
6. A query optimization device, characterized in that: The device comprises: A determination module, configured to determine, upon receiving a query request, an update time of the last update of the statistical information of the database and actual statistical information at the update time; A prediction module, configured to predict the statistical information at the current moment according to the actual statistical information at the updating moment using a pre-trained statistical information prediction model; An evaluation module, used to determine the execution cost of each query execution plan corresponding to the query request according to the predicted statistical information at the current moment; The selection module is used to determine a target query execution plan corresponding to the query request according to the execution costs of the various query execution plans.
7. A statistical information prediction model training device, characterized in that: The device comprises: An acquisition module, used to acquire a training data set, wherein the training data set includes historical statistical information stored in a database at multiple historical moments; A model training module is used to train a model to be trained using the training data set to obtain a statistical information prediction model; the statistical information prediction model is used to predict the statistical information of the database at a second moment based on the statistical information of the database at a first moment, and the first moment is before the second moment.
8. An electronic device, characterized in that: include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the query optimization method according to any one of claims 1 to 3; Alternatively, the processor executes the computer-executable instructions stored in the memory to implement the statistical information prediction model training method as described in any one of claims 4 to 5.
9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the query optimization method according to any one of claims 1 to 3 when executed by a processor; Alternatively, when the computer execution instruction is executed by a processor, it is used to implement the statistical information prediction model training method as described in any one of claims 4 to 5.
10. A computer program product, characterized in that The method comprises a computer program, wherein when the computer program is executed by a processor, the query optimization method according to any one of claims 1 to 3 is implemented; Or when the computer program is executed by a processor, it implements the statistical information prediction model training method as described in any one of claims 4 to 5.
Citation Information
Patent Citations
Techniques for maintaining statistics in a database system
CN112740195A
Database query performance prediction method and system based on cost factor calibration
CN116244333A
Database statistical histogram forecasting
US20160210329A1
Database optimization based on forecasting hardware statistics using data mining techniques
US20180218038A1
Cited By
Query plan result data set rapid generation method
CN121188092A
A query plan result dataset fast generation method
CN121188092B
