Database query optimization method, device, storage medium and electronic device

Abstract

The present disclosure provides a database query optimization method, device, storage medium and electronic device, which include: obtaining the real cardinality and query content of executed nodes in a query plan, fusing the real cardinality and query content to obtain fusion information, and updating the initial estimated cardinality of unexecuted nodes in the query plan according to the fusion information to obtain an updated estimated cardinality. In this embodiment, the technical feature of obtaining the real cardinality and query content of executed nodes and adjusting the initial estimated cardinality of unexecuted nodes based on the real cardinality and query content is equivalent to correcting the initial estimated cardinality of unexecuted nodes by drawing on the relevant content of executed nodes, so that the corrected updated estimated cardinality has higher accuracy and reliability.

Description

Technical Field

[0001] The present disclosure relates to the fields of data processing and deep learning technology, and in particular to a database query optimization method, device, storage medium, and electronic device. Background Art

[0002] The query optimizer in a database system consists of three main components: the cardinality estimator (also known as the estimator), the cost model, and the plan space search. The cardinality estimator estimates the number of tuples in a given query's intermediate result table, i.e., the subquery's cardinality. The cost model estimates the cost of executing a given execution plan (also known as a query plan). The plan space search searches for possible execution plans.

[0003] Machine learning-based cardinality estimation methods can be divided into three categories: query-driven estimators, data-driven estimators, and hybrid-driven estimators.

[0004] In the prior art, the execution of a query plan corresponding to any node in a query plan depends on an initial estimated cardinality obtained by pre-estimated cardinality.

[0005] The content of the background technology section is merely information known to the inventor personally, and does not mean that the above information has entered the public domain before the application date of this disclosure, nor does it mean that it can become the prior art of the present disclosure. Summary of the Invention

[0006] The present disclosure provides a database query optimization method, device, storage medium and electronic device to improve the effectiveness and reliability of database query optimization.

[0007] In a first aspect, the present disclosure provides a database query optimization method, the method comprising:

[0008] Get the actual cardinality of executed nodes and query content in the query plan;

[0009] Fusing the real cardinality and the query content to obtain fusion information;

[0010] An initial estimated cardinality of unexecuted nodes in the query plan is updated according to the fusion information to obtain an updated estimated cardinality.

[0011] Optionally, the true cardinality is extracted from the executed node based on a cardinality module in a pre-trained progressive cardinality estimation model;

[0012] The query content is extracted from the executed node based on the content module in the progressive cardinality estimation model;

[0013] The fusion information is obtained by fusing the true cardinality and the query content based on the connection layer in the progressive cardinality estimation model;

[0014] The updated estimated cardinality is obtained by updating the initial estimated cardinality of the unexecuted node according to the fusion information based on the improvement module in the progressive cardinality estimation model;

[0015] The progressive cardinality estimation model is obtained by training based on the first sample data.

[0016] Optionally, the progressive cardinality estimation model is obtained by pre-training a pre-trained model based on the first data set, and adjusting an improvement module in the pre-trained model based on the second data set;

[0017] The first sample data includes the first data set and the second data set.

[0018] Optionally, each node in the query plan has an initial estimated cardinality, and the initial estimated cardinality of each node is determined according to characteristics of the node and characteristics of its child nodes.

[0019] Optionally, the initial estimated cardinality of each node is determined based on a preset cardinality estimation model;

[0020] The cardinality estimation model is obtained by training based on the second sample data, and the cardinality estimation model includes an embedding module, a simple cycle unit module, and an output module;

[0021] The embedding module is used to adjust the encoding feature vector of a first length corresponding to each node to an encoding feature vector of a second length, wherein the first length is greater than the second length;

[0022] The simple cycle unit module is used to determine the cardinality estimation feature of each node based on the encoded feature vector of the second length of each node and the features of the child nodes of the node;

[0023] The output module is used to determine the initial estimated cardinality of each node according to the cardinality estimation feature of the node.

[0024] Optionally, the cardinality estimation model is trained based on knowledge distillation.

[0025] Optionally, the method further includes:

[0026] Based on the updated estimated cardinality, the query plan corresponding to the unexecuted node is updated.

[0027] In a second aspect, the present disclosure provides a database query optimization device, comprising:

[0028] The acquisition unit is used to obtain the actual cardinality of executed nodes and query content in the query plan;

[0029] a fusion unit, configured to fuse the real cardinality and the query content to obtain fusion information;

[0030] The first updating unit is configured to update an initial estimated cardinality of unexecuted nodes in the query plan according to the fusion information to obtain an updated estimated cardinality.

[0031] Optionally, the true cardinality is extracted from the executed node based on a cardinality module in a pre-trained progressive cardinality estimation model;

[0032] The query content is extracted from the executed node based on the content module in the progressive cardinality estimation model;

[0033] The fusion information is obtained by fusing the true cardinality and the query content based on the connection layer in the progressive cardinality estimation model;

[0034] The updated estimated cardinality is obtained by updating the initial estimated cardinality of the unexecuted node according to the fusion information based on the improvement module in the progressive cardinality estimation model;

[0035] The progressive cardinality estimation model is obtained by training based on the first sample data.

[0036] Optionally, the progressive cardinality estimation model is obtained by pre-training a pre-trained model based on the first data set, and adjusting an improvement module in the pre-trained model based on the second data set;

[0037] The first sample data includes the first data set and the second data set.

[0038] Optionally, each node in the query plan has an initial estimated cardinality, and the initial estimated cardinality of each node is determined according to characteristics of the node and characteristics of its child nodes.

[0039] Optionally, the initial estimated cardinality of each node is determined based on a preset cardinality estimation model;

[0040] The cardinality estimation model is obtained by training based on the second sample data, and the cardinality estimation model includes an embedding module, a simple cycle unit module, and an output module;

[0041] The embedding module is used to adjust the encoding feature vector of a first length corresponding to each node to an encoding feature vector of a second length, wherein the first length is greater than the second length;

[0042] The simple cycle unit module is used to determine the cardinality estimation feature of each node based on the encoded feature vector of the second length of each node and the features of the child nodes of the node;

[0043] The output module is used to determine the initial estimated cardinality of each node according to the cardinality estimation feature of the node.

[0044] Optionally, the cardinality estimation model is trained based on knowledge distillation.

[0045] Optionally, the method further includes:

[0046] The second updating unit is configured to update the query plan corresponding to the unexecuted node based on the updated estimated cardinality.

[0047] In a third aspect, the present disclosure provides a processor-readable storage medium, wherein the processor-readable storage medium stores a computer program, and the computer program is used to enable the processor to execute the method as described in any one of the first aspects above.

[0048] In a fourth aspect, the present disclosure provides an electronic device, comprising: a processor, and a memory communicatively connected to the processor;

[0049] The memory stores computer-executable instructions;

[0050] The processor executes the computer-executable instructions stored in the memory to implement the method as described in any one of the first aspects.

[0051] In a fifth aspect, the present disclosure provides a computer program product, comprising a computer program, which implements the method described in any one of the first aspects when executed by a processor.

[0052] The present disclosure provides a database query optimization method, device, storage medium and electronic device, which includes: obtaining the real cardinality and query content of executed nodes in a query plan, fusing the real cardinality and query content to obtain fusion information, and updating the initial estimated cardinality of unexecuted nodes in the query plan according to the fusion information to obtain an updated estimated cardinality. In this embodiment, the technical feature of obtaining the real cardinality and query content of executed nodes and adjusting the initial estimated cardinality of unexecuted nodes based on the real cardinality and query content is equivalent to correcting the initial estimated cardinality of unexecuted nodes by drawing on the relevant content of executed nodes, so that the corrected updated estimated cardinality has higher accuracy and reliability. BRIEF DESCRIPTION OF THE DRAWINGS

[0053] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present disclosure and, together with the description, serve to explain the principles of the present disclosure.

[0054] Figure 1 Schematic diagram for performance comparison of learning-based estimators;

[0055] Figure 2 Schematic diagram of the estimated error of the estimator for queries with different numbers of joins;

[0056] Figure 3 A schematic diagram of a database query optimization method according to an embodiment of the present disclosure;

[0057] Figure 4 A schematic diagram of a database query optimization method according to another embodiment of the present disclosure;

[0058] Figure 5 Schematic diagram of the framework of the progressive cardinality estimation model according to an embodiment of the present disclosure;

[0059] Figure 6 Schematic diagram of the training principle of the progressive cardinality estimation model according to an embodiment of the present disclosure;

[0060] Figure 7 Schematic diagram of the structure of the cardinality estimation model according to an embodiment of the present disclosure;

[0061] Figure 8 Schematic diagram of model compression based on knowledge distillation according to an embodiment of the present disclosure;

[0062] Figure 9 Schematic breakdown of end-to-end execution time for different cardinality estimators;

[0063] Figure 10 This is a schematic diagram of implementing adding a checkpoint in an embodiment of the present disclosure;

[0064] Figure 11 A schematic diagram of changes in the estimation error of the progressive cardinality estimation model during the query plan execution process disclosed herein;

[0065] Figure 12 A diagram showing the change in end-to-end execution time of PostgreSQL using the estimator;

[0066] Figure 13 A schematic diagram of a database query optimization device according to an embodiment of the present disclosure;

[0067] Figure 14 It is a schematic block diagram of an electronic device for implementing an embodiment of the present disclosure.

[0068] The above drawings illustrate specific embodiments of the present disclosure, which will be described in more detail below. These drawings and textual descriptions are not intended to limit the scope of the present disclosure in any way, but rather to illustrate the concepts of the present disclosure to those skilled in the art by reference to specific embodiments. DETAILED DESCRIPTION

[0069] Exemplary embodiments will be described in detail herein, with examples illustrated in the accompanying drawings. In the following description, when referring to the drawings, identical numerals in different figures represent identical or similar elements, unless otherwise indicated. The embodiments described in the following exemplary embodiments are not intended to represent all possible embodiments consistent with the present disclosure. Rather, they are merely examples of apparatus and methods consistent with certain aspects of the present disclosure, as detailed in the appended claims.

[0070] It should be understood that the terms "including" and "having" and any variations thereof in the embodiments of the present disclosure are intended to cover but not exclude inclusion. For example, a product or device that includes a series of components is not necessarily limited to those components explicitly listed, but may include other components not explicitly listed or inherent to these products or devices.

[0071] In the embodiments of the present disclosure, the term "and / or" describes the association relationship between associated objects, indicating that three relationships can exist. For example, A and / or B can represent three situations: A exists alone, A and B exist simultaneously, and B exists alone. The character " / " generally indicates that the associated objects are in an "or" relationship.

[0072] In the embodiments of the present disclosure, the term "plurality" refers to two or more than two, and other quantifiers are similar thereto.

[0073] The terms "first," "second," "third," and the like in this disclosure are used to distinguish between similar or similar objects or entities and are not necessarily intended to limit a particular order or precedence, unless otherwise indicated. It should be understood that the terms used in this manner are interchangeable where appropriate, e.g., capable of being implemented in an order other than that given in the illustrations or descriptions of the embodiments of this disclosure.

[0074] The term "unit / module" as used in this disclosure refers to any known or later developed hardware, software, firmware, artificial intelligence, fuzzy logic or combination of hardware and / or software code that is capable of performing the functions associated with that element.

[0075] To facilitate readers' understanding of the present disclosure, at least some of the terms involved in the present disclosure are explained as follows:

[0076] Artificial Intelligence (AI) technology refers to the research and development of theories, methods, techniques and application systems for simulating, extending and expanding human intelligence.

[0077] Deep learning (DL) is a subfield of machine learning (ML). It is about learning the inherent laws and representation levels of sample data. The information obtained in this learning process is of great help in interpreting data such as text, images, and sounds.

[0078] Knowledge distillation, also known as dark knowledge extraction, refers to the introduction of soft-targets related to the teacher network (complex but with excellent prediction accuracy) as part of the total loss to induce the training of the student network (simplified, low-complexity, more suitable for inference deployment) and achieve knowledge transfer.

[0079] A database system is a system consisting of a database and its management software. A database system is an ideal data processing system developed to meet the needs of data processing. It is also a software system that provides data for practical storage, maintenance, and application systems. It is a combination of storage media, processing objects, and management systems.

[0080] A database refers to a "warehouse that organizes, stores, and manages data according to data structures." It is a collection of large amounts of data that is stored in a computer for a long time, organized, shareable, and uniformly managed.

[0081] Cardinality estimation in a database refers to estimating the size of a query result based on a given data table and query information.

[0082] Structured Query Language (SQL) is a database language with multiple functions such as data manipulation and data definition.

[0083] PostgreSQL is a free software object-relational database management system (ORDBMS) with a very complete set of features.

[0084] MySQL is a relational database management system. A relational database stores data in different tables instead of putting all data in one large warehouse, which increases speed and flexibility.

[0085] The lightweight database (SQLite) is integrated into the program as an integral part of the application. Therefore, the primary communication protocol is direct Application Programming Interface (API) calls within the programming language. This has a positive impact on throughput, latency, and overall simplicity. The entire database (definitions, tables, indexes, and the data itself) is stored in a single file on the host computer. Its simple design is achieved by locking the entire data file when starting a transaction.

[0086] The query optimizer in a database system is the component that performs internal optimizations on SQL servers based on user queries, generates (or reuses) execution plans, transmits them to the storage engine for data manipulation, and ultimately returns the results to the user. The query optimizer is a core component of a relational database management system, determining which indexes and join algorithms to use for a specific query to ensure efficient execution. It is one of the most important components in the optimizer.

[0087] The query optimizer in a database system consists of three main components: the cardinality estimator (also known as the estimator), the cost model, and the plan space search. The cardinality estimator estimates the number of tuples in a given query's intermediate result table, i.e., the subquery's cardinality. The cost model estimates the cost of executing a given execution plan (also known as a query plan). The plan space search searches for possible execution plans.

[0088] The specific process of query optimization by the query optimizer is as follows: the optimizer first uses the plan space search to determine possible execution plans; then uses the cardinality estimator to estimate the cardinality of all subqueries in each execution plan; finally, estimates the cost of each execution plan through the cost model, and selects the execution plan with the lowest cost as the final execution plan.

[0089] Improving the accuracy of cardinality estimation is crucial for improving database system performance. Traditional cardinality estimation methods often rely on simplifying assumptions (such as uniform and independent data) and use table statistics (such as histograms and sampling) to estimate cardinality. While these methods are easy to implement, in real-world scenarios, these assumptions often fail to hold, and sampled data rarely represents the entire database's data distribution. Consequently, these traditional methods often suffer from significant errors, often reaching several orders of magnitude.

[0090] In order to solve the problems existing in traditional cardinality estimation methods, the academic community has proposed many cardinality estimators based on machine learning in recent years, and compared with traditional methods, they have significantly improved the accuracy of cardinality estimation. Figure 1This paper lists some current machine learning-based cardinality estimators and shows their performance on a query set with eight join tables generated from the in-memory database (IMDB) dataset.

[0091] like Figure 1 ( Figure 1 As shown in Figure 1 (a schematic diagram showing the performance comparison of learning-based estimators), cardinality estimation methods based on machine learning can be divided into three categories: query-driven estimators, data-driven estimators, and hybrid-driven estimators.

[0092] The query-driven estimator transforms cardinality estimation into a supervised learning-based regression problem. Given a query, the query-driven estimator first extracts the query information, including table names, column names, filter conditions, and join relationships used in the query. It then encodes this information into a feature vector that can be input into the neural network model. The neural network model processes the feature vector and outputs an estimated cardinality.

[0093] Existing work on this topic involves: Multi-Scale Dense Convolutional Networks (MSDN) first convert queries into feature vectors, then use Multi-Set Convolution Networks (MSCN) to map the feature vectors to the resulting cardinality. Tree Long Short-Term Memory (TLSTM) networks use LSTMs to recursively process query execution plans according to their tree structure. While other work learns the mapping from queries to cardinality, the flow-loss training model learns the mapping from execution plans to costs.

[0094] Data-driven estimators treat cardinality estimation as an unsupervised learning problem. For example, given data, they model the joint distribution of tables to capture the correlation and distribution of data between tables, and then estimate the cardinality based on the query conditions.

[0095] In terms of existing work: DeepDB uses the Relational Sum Product Network (RSPN) to model the joint probability distribution of tables and estimates cardinality by calculating probabilities and expectations based on RSPN. Neurocard uses samples collected from the results of full left outer joins of all tables to train a single deep autoregressive model that can estimate the cardinality of queries connecting any subset of relations. The lightweight and efficient cardinality estimation model (Fast, Lightweight and Accurate Method for Cardinality Estimation - FLAT, FLAT) uses the factorization-split-sum-product network (FSPN) based on a graph model to model the joint probability density function of attributes.

[0096] The hybrid-driven estimator learns from both table data and query samples.

[0097] In terms of existing work: the unified deep autoregressive estimator (UAE) adopts a unified deep autoregressive model to learn the joint distribution of tables in an unsupervised manner, and is trained in a supervised manner using query content as auxiliary information.

[0098] from Figure 1 It can be seen that compared with the traditional cardinality estimation method, which has an error of several orders of magnitude (q-error), the learning-based estimator greatly improves the accuracy of cardinality estimation. However, these methods still have the following disadvantages: (1) It is difficult to balance the estimation accuracy and inference complexity. Figure 1 It can be seen that among the existing estimators, the q-error of data-driven and hybrid-driven estimators is an order of magnitude smaller than that of query-driven estimators, but their inference time is much longer than that of query-driven estimators. At present, all estimators are difficult to achieve high accuracy and fast inference at the same time. This is mainly because data-driven estimators need to load a large amount of data, which inevitably increases the inference time while improving the estimation accuracy; query-driven estimators do not need to load data, so they can achieve fast inference, but because the training samples are difficult to cover all possible queries, their estimation errors are large. (2) It is difficult to deal with complex queries. From Figure 2 ( Figure 2As shown in the diagram (Figure 2) showing the estimation errors of the estimators for queries with different numbers of joins, the accuracy of all cardinality estimators decreases as the number of joins in a query increases. When a query includes eight joins, the average q-error of all estimators is greater than 102. Predictably, the estimation errors of these estimators will become even larger for queries with more joins. This problem is primarily due to the propagation and amplification of estimation errors between execution plans. The estimator's cardinality estimation for each join is inaccurate, and this error is propagated to subsequent joins, causing the error to increase with increasing query complexity. Because estimation errors are inevitable, no matter how accurate the estimator, it is difficult to accurately predict complex queries with many joins.

[0099] To address the problems existing in existing methods, this disclosure proposes a query-driven adaptive cardinality estimation algorithm (Learned Progressive Cardinality Estimation, LPCE). Specifically, this disclosure proposes a lightweight and high-precision cardinality estimation model (Initial Cardinality Estimation Model, LPCE-I) to address the problem that current methods have difficulty balancing estimation accuracy and inference complexity; this disclosure also proposes a progressive cardinality estimation model LPCE-R (Progressive Cardinality Refinement Model, LPCE-R) to handle complex queries that existing methods have difficulty handling.

[0100] The following will be combined with the accompanying drawings in the embodiments of the present disclosure to clearly and completely describe the technical solutions in the embodiments of the present disclosure. Obviously, the embodiments described are only part of the embodiments of the present disclosure and not all of the embodiments. Based on the embodiments of the present disclosure, all other embodiments obtained by ordinary technicians in this field without making any creative efforts shall fall within the scope of protection of the present disclosure.

[0101] See also Figure 3 , Figure 3 A schematic diagram of a database query optimization method according to an embodiment of the present disclosure is shown in FIG. Figure 3 As shown, the method includes:

[0102] S301: Obtain the actual cardinality of executed nodes and query content in the query plan.

[0103] Illustratively, the execution subject of this embodiment is a database query optimization device (hereinafter referred to as the optimization device), which may be the query optimizer described above.

[0104] The query plan can be understood as the execution plan described above, and specifically can be the execution plan with the lowest cost, that is, the final execution plan.

[0105] A query plan includes multiple nodes (also known as subqueries). Query plan execution is performed at the node level, for example, by sequentially executing the portion of the query plan corresponding to each node. Nodes in a query plan that have already executed their corresponding portions are referred to as executed nodes, while nodes whose corresponding query plans have not yet been executed are referred to as unexecuted nodes.

[0106] In this step, the query optimizer can obtain the actual cardinality and query content of the executed node. The actual cardinality is the actual cardinality obtained by the query optimizer when executing the query plan corresponding to the executed node.

[0107] S302: Fusing the real cardinality and the query content to obtain fused information.

[0108] This embodiment does not limit the query optimizer's fusion processing method. For example, the fusion processing may be a splicing processing.

[0109] S303: updating the initial estimated cardinality of the unexecuted nodes in the query plan according to the fusion information to obtain an updated estimated cardinality.

[0110] The initial estimated cardinality and the updated estimated cardinality are relative concepts. Combined with the above analysis, the initial estimated cardinality can be understood as being determined based on the cardinality estimator in the query optimizer, and the updated estimated cardinality refers to the adjustment of the initial estimated cardinality.

[0111] Combined with the above analysis, the fusion information is determined based on the real cardinality of the executed nodes and the query content. Therefore, this step can be understood as adjusting the relevant content of the unexecuted nodes (specifically the initial estimated cardinality) based on the relevant content of the executed nodes (specifically the real cardinality and the query content) to obtain the updated estimated cardinality.

[0112] That is, in this embodiment, the query optimizer can adjust the initial estimated cardinality of the unexecuted nodes based on the executed nodes, so as to correct the initial estimated cardinality of the unexecuted nodes based on the actual execution status of the query plan, so that the updated estimated cardinality has higher accuracy and reliability.

[0113] Based on the above analysis, the present disclosure provides a database query optimization method, which includes: obtaining the real cardinality and query content of executed nodes in a query plan, fusing the real cardinality and query content to obtain fusion information, and updating the initial estimated cardinality of unexecuted nodes in the query plan according to the fusion information to obtain an updated estimated cardinality. In this embodiment, the technical feature of obtaining the real cardinality and query content of executed nodes and adjusting the initial estimated cardinality of unexecuted nodes based on the real cardinality and query content is equivalent to correcting the initial estimated cardinality of unexecuted nodes by drawing on the relevant content of executed nodes, so that the corrected updated estimated cardinality has higher accuracy and reliability.

[0114] Based on the above analysis, it can be seen that in some embodiments, a model can be used to solve the problem of difficulty in balancing accuracy and reasoning complexity, as well as to solve the problem of complex queries. In order to enable readers to have a deeper understanding of the implementation principle of the present disclosure, Figure 4 The database query optimization method disclosed in the present invention is described in more detail. Figure 4 This is a schematic diagram of a database query optimization method according to another embodiment of the present disclosure. Figure 4 As shown, the method includes:

[0115] S401: extracting true cardinality from executed nodes in a query plan based on a cardinality module in a pre-trained progressive cardinality estimation model, where the progressive cardinality estimation model is trained based on first sample data.

[0116] S402: Extracting query content from executed nodes based on the content module in the progressive cardinality estimation model.

[0117] S403: Fusing the actual cardinality and the query content based on the connection layer in the progressive cardinality estimation model to obtain fused information.

[0118] S404: Based on the improvement module in the progressive cardinality estimation model, the initial estimated cardinality of the unexecuted nodes is updated according to the fusion information to obtain an updated estimated cardinality.

[0119] It should be understood that in order to avoid tedious description, the technical features of this embodiment that are the same as those in the above embodiments will not be repeated in this embodiment.

[0120] For example, regarding the execution subject of this embodiment, please refer to the above example, which will not be repeated here; for another example, regarding the explanation of the query plan, executed nodes, and actual cardinality, please refer to the above example, which will not be repeated here.

[0121] S401-S404 can be understood as: the query optimizer or other devices (such as training devices) can use the first sample data to train and obtain a progressive cardinality estimation model, such as Figure 5 ( Figure 5 As shown in the framework diagram of the progressive cardinality estimation model of the embodiment of the present disclosure, the progressive cardinality estimation model includes four modules, namely the cardinality module, the content module, the connection layer, and the refinement module. The four modules cooperate with each other to obtain the updated estimated cardinality.

[0122] Among them, if the progressive cardinality estimation model is obtained by training the training device using the first sample data, then when the training device trains the progressive cardinality estimation model, the training device can transmit the progressive cardinality estimation model to the query optimizer so that the query optimizer determines the updated estimated cardinality based on the progressive cardinality estimation model.

[0123] In this embodiment, the query optimizer may extract two types of information from the executed nodes based on the progressive cardinality estimation model: actual cardinality and query content.

[0124] For example, the initial estimated cardinality of an executed node is 10, but the true cardinality is 1000. Since the true cardinality of unexecuted nodes depends on the executed nodes, identifying this 100-fold underestimation is crucial for improving the initial estimated cardinality of unexecuted nodes. Furthermore, the semantics of executed nodes (i.e., query content), such as operator types, join columns, and filter predicates, are also important for determining the true cardinality of unexecuted nodes.

[0125] Therefore, the cardinality module extracts the true cardinality c of the executed node B ; The content module extracts the query content c of the executed node A For an executed node, the query optimizer can append its actual cardinality to the content feature encoding of its query content to obtain a feature vector, and use the feature vector as the input of the next module (i.e., the connection layer).

[0126] Among them, the real cardinality c B It can be called the embedding generated by the cardinality module, and the query content c A The embeddings produced by the content module can be called as content modules, which encode the information of executed nodes.

[0127] like Figure 5 As shown in Figure 2, the connection layer is used to merge the two embeddings, and the output of the connection layer is the input of the improvement module. The connection layer can use a single-layer neural network with a sigmoid activation function to learn the query content c separately. Aand the true cardinality c B The combined weight of query content c A and the true cardinality c B The result of the weighted combination is processed by a single-layer neural network using the ReLU activation function.

[0128] For example, Figure 5 As shown, the connection layer includes a linear layer, a sigmoid activation function layer, and a ReLU activation function layer. The input of one linear layer is the output of the base module, the input of one linear layer is the output of the content module, the input of two sigmoid activation functions is the input of another linear layer, and the output of the linear layer is the input of the ReLU activation function, and the output of the ReLU activation function is the input of the improvement module.

[0129] In some embodiments, the output c of the connection layer AB It can be expressed by formula 1:

[0130] c AB =ReLU(W AB (w A ⊙c A +w B ⊙c B )+b AB )

[0131] Among them, w A =ρ(W A c A +b A ), w B =ρ(W B c B +b B ), ρ(·) is the sigmoid function, ⊙ represents element-wise multiplication, W A 、W B and W AB Preset parameter matrix, w A Is the query content c A The corresponding weight coefficient, w B is the real cardinality c B The corresponding weight coefficient, b AB is a preset constant.

[0132] In some embodiments, a query plan is a tree structure, including parent nodes and child nodes. A parent node may include one or two child nodes. The query plan is executed sequentially from leaf to root along the tree structure, that is, from child nodes to parent nodes. At some point in time, some nodes in the query plan have been completed, while others remain to be executed.

[0133] When the query optimizer performs cardinality improvement based on the improvement module, the improvement module can directly select the encoding feature vector of the unexecuted node (such as the encoding feature vector corresponding to each child node of the unexecuted parent node) as input, or it can use the input c of the connection module according to the execution situation. AB Replace at least one of the encoding feature vectors corresponding to each child node.

[0134] After the query plan for a node (if it's a parent node) completes execution, the cardinality and content modules update their embeddings based on the parent node's executed children and the recently completed parent node. The improvement module then uses these updated embeddings to improve the estimates of all other nodes. Embeddings are updated incrementally as each node completes, so the cardinality and content modules don't need to process the entire completed node from scratch, making incremental improvement highly efficient.

[0135] In some embodiments, the progressive cardinality estimation model is obtained by pre-training a pre-trained model based on a first data set, and adjusting an improvement module in the pre-trained model based on a second data set.

[0136] The first sample data includes a first data set and a second data set. For example, the first data set and the second data set are respectively partial data in the first sample data, or the first sample data is the first data set, or the first sample data is the second data set.

[0137] This embodiment does not limit the content of the first dataset and the second dataset. For example, the first dataset and the second dataset may be the same dataset; for example, the first dataset and the second dataset may include some of the same data; or for example, the first dataset and the second dataset may not have any of the same data.

[0138] In this embodiment, the training of the progressive cardinality estimation model can be divided into two stages: one stage is a pre-training stage, and the other stage is an adjustment stage.

[0139] like Figure 6 ( Figure 6 As shown in the diagram of the training principle of the progressive cardinality estimation model of an embodiment of the present disclosure, in the pre-training stage, the improvement module and the content module share the same parameters, such as the improvement module and the content module share input parameters, and the input parameters can be the feature vector obtained by encoding the sample query content in the first data set.

[0140] For example, Figure 6As shown in Figure 1, the pre-training phase can include two phases: feature vector encoding and training. In the feature vector encoding phase, the input is the sample query content, and the output is the feature vector processed by feature vector encoding. In the training phase, the feature vector is used as input to train the basic network model, resulting in the improvement module and content module.

[0141] This embodiment does not limit the framework and parameters of the basic network model, which can be determined based on requirements, historical records, and experiments.

[0142] like Figure 6 As shown, in the pre-training stage, the input of the training cardinality module is the concatenation result of the sample query content of the parent node (specifically, the sample feature vector of the sample query content) and the sample true cardinality of the child nodes belonging to the parent node.

[0143] For example, combining the above example and Figure 6 In the feature vector encoding stage, the input is the sample query content and the sample true cardinality, and the output is the feature vector obtained by encoding the concatenation result of the sample query content and the sample true cardinality. In the training stage, the feature vector is used as input to train the basic network model and obtain the cardinality module.

[0144] In some embodiments, when training a cardinality module, the true cardinality of pre-labeled nodes may be used as a learning value, and the basic network model may be trained so that the cardinality module satisfies the minimum node loss function.

[0145] like Figure 6 As shown in Figure 2, during the adjustment phase, the parameters of the cardinality module and the content module can be frozen, and the parameters of the improvement module can be fine-tuned. That is, during this phase, the parameters of the cardinality module and the content module remain unchanged, while the parameters of the improvement module are adjusted to obtain the final progressive cardinality estimation model.

[0146] A sample query plan with m nodes can provide (m-1) training samples for the improvement module. Specifically, when a node is executed, the content module and cardinality module can be used to obtain the embedding of the child nodes with that node as the parent. This embedding is used as the input of the model obtained in the pre-training phase. The true cardinality of the remaining unexecuted nodes is used as the learning value. The node loss function is used to train the improvement module to learn the mapping ability from the embedding of the sample query plan to the true cardinality.

[0147] like Figure 6As shown, in the adjustment stage, the feature coding of the unexecuted nodes is used as the input of the improvement module, the feature coding of the executed nodes (specifically, the feature coding corresponding to the true cardinality of the sample) is used as the input of the cardinality module, and the feature coding of the executed nodes (specifically, the feature coding corresponding to the sample query content) is used as the input of the content module. The connection layer fuses the output of the cardinality module and the output of the content module. The output of the connection layer is also used as the input of the improvement module to fine-tune the parameters of the improvement module, thereby obtaining the adjusted improvement module and the trained progressive cardinality estimation model.

[0148] It is worth noting that, in some embodiments, the query optimizer may determine whether to use the progressive technology estimation model to update the initial estimated cardinality based on the execution status of the query plan.

[0149] For example, if the difference between the actual cardinality of the executed node and the initial estimated cardinality of the executed node is relatively large, the query optimizer uses the progressive technique estimation model to update the initial estimated cardinality; conversely, if the difference between the actual cardinality of the executed node and the initial estimated cardinality of the executed node is relatively small, the query optimizer does not use the progressive technique estimation model to update the initial estimated cardinality.

[0150] Based on the above analysis, the final execution plan is determined by first determining the possible execution plans, then determining the cardinality corresponding to each possible execution plan, and finally determining it through cost estimation.

[0151] Therefore, in some embodiments, each node in the query plan has an initial estimated cardinality, and the initial estimated cardinality of each node is determined based on the characteristics of the node and the characteristics of the node's child nodes.

[0152] In the above example, a query plan includes multiple nodes, each of which can be referred to as a parent node. A parent node may include child nodes, specifically one or two child nodes. Each node has an initial estimated cardinality. This means that unexecuted nodes and executed nodes also have an initial estimated cardinality. The initial estimated cardinality of a parent node is determined based on the characteristics of the parent node and its child nodes.

[0153] In some embodiments, the initial estimated cardinality of each node is determined based on a preset cardinality estimation model.

[0154] The cardinality estimation model is obtained by training based on the second sample data. The cardinality estimation model includes an embedding module, a simple cyclic unit module, and an output module.

[0155] The embedding module is used to adjust the encoding feature vector of a first length corresponding to each node to an encoding feature vector of a second length, where the first length is greater than the second length.

[0156] The simple cycle unit module is used to determine the cardinality estimation feature of the node according to the encoded feature vector of the second length of each node and the features of the child nodes of the node.

[0157] The output module is used to determine the initial estimated cardinality of each node according to the cardinality estimation characteristics of the node.

[0158] Combined with the above analysis, it can be seen that the present disclosure proposes a query-driven adaptive cardinality estimation algorithm, which can be understood as a cardinality estimation model, that is, the cardinality estimation model can be pre-trained to determine the initial estimated cardinality of each node based on the cardinality estimation model.

[0159] Similarly, this embodiment does not limit the relationship between the first sample data and the second sample data. For example, the first sample data may be at least partially identical to the second sample data, or the first sample data may be completely different from the second sample data.

[0160] For example, Figure 7 ( Figure 7 As shown in the structural diagram of the cardinality estimation model of an embodiment of the present disclosure, the cardinality estimation model includes an embedding module (Embed module), a simple recurrent unit module (Simple Recurrent Unit module, SRU module) and an output module (Output module).

[0161] The embedding module is used to map the sparse feature embedding of a node to a dense feature embedding x, which consists of a two-layer fully connected neural network using ReLU as the activation function.

[0162] For example, embedding modules (such as Figure 7 The “Embed module” shown in the figure includes: feature vector layer (such as Figure 7 "Feature vector" shown), linear layer (such as Figure 7 "Liner" shown), activation function layer (such as Figure 7 "ReLU" shown).

[0163] Exemplarily, the input of the embedding module is a sparse feature embedding, which is a feature vector obtained by encoding the query information of the node, wherein the feature vector can be encoded by a one-hot encoding method.

[0164] Simple recurrent unit modules (such as Figure 7 The function of the "SRU module" shown in the figure is to embed the features of a node (such as Figure 7 The node code (such as "x" shown in Figure 7 The “c l ” and “c r ”) as input, generate the node code of the node (such as Figure 7 "c" shown) and node representation (as Figure 7 The generated node code c will be passed to the parent node of the node, and the simple recurrent unit module will repeat the same operation on its parent node.

[0165] In some embodiments, the node code c can be expressed by Formula 2:

[0166]

[0167] The node representation h can be expressed by Equation 3:

[0168] h=r⊙tanh(c)+(1-r)⊙x

[0169] Where, f=ρ(W f x+b f ), r=ρ(W r x+b r ), W x 、W f and W r is the parameter matrix, b f and b r is the bias vector, ⊙ represents element multiplication, ρ is the activation function sigmoid, and f is the forget gate. When generating node code c, f controls the sub-node code c l and c r , projection of node feature embedding , r is the reset gate, which uses the node feature embedding x and node encoding c to generate the node representation h for cardinality estimation.

[0170] The output module is used to map the node representation h to the initial estimated cardinality. The output module also consists of two layers of fully connected neural networks, but the last layer uses a sigmoid activation function to generate a floating point number from 0 to 1. The floating point number represents the difference between the initial estimated cardinality and the training set (such as Figure 7 The ratio of the maximum cardinality in the "Cardinality" shown.

[0171] For example, output modules (such as Figure 7 The "Output module" shown in the figure includes: a linear layer (such as Figure 7 "Liner" shown), activation function layer (such as Figure 7 "ReLU" and "Sigmoid" shown).

[0172] When processing an input vector, LSTM needs to perform 8 matrix multiplications, while the simple recurrent unit module only needs 3. Therefore, the simple recurrent unit module has higher computational efficiency than the LSTM model. In addition, the 3 matrix multiplications in the simple recurrent unit module (used to calculate f and r) can be parallelized, while the matrix multiplication in LSTM is data-dependent and cannot be parallelized. Therefore, the cardinality estimation model consumes less memory and improves inference speed.

[0173] In related technologies, both MSCN and TLSTM choose average q-error as the loss function. Since this loss function only considers the estimation error of the final cardinality of each query, we call it the query loss function.

[0174] In this embodiment, the loss function is a node loss function, which can be expressed by Formula 4:

[0175]

[0176] in, c ij is the true cardinality of the jth node in query plan i, is the initial estimated cardinality of the jth node in query plan i, m i is the number of nodes in query plan i. Unlike the query loss function, the node loss function considers the q-error of all nodes in the query plan.

[0177] In this embodiment, on the one hand, the node loss function is a data enhancement method that expands the second sample data. For example, for a query plan in the second sample data The node loss function actually utilizes the initial estimation errors of the three query plans, namely Because complex queries contain many intermediate nodes, this data augmentation works very well. On the other hand, it allows supervision of each node in the query plan. The query loss function only provides supervision of the root node, which means that the gradient needs to be back-propagated in time to reach the internal nodes. By providing direct supervision signals for the internal nodes, the node loss function generates more information embedding for the internal nodes, making the cardinality estimation model produce a more accurate representation of the entire query, thereby producing a more accurate cardinality estimate. Among them, Indicates Join.

[0178] In some embodiments, the cardinality estimation model is trained based on knowledge distillation.

[0179] Exemplarily, the model parameters of the cardinality estimation model, such as the size of the embedding vector and the number of hidden units in the neural network, control the complexity and accuracy of the cardinality estimation model. Although the use of a small model allows for fast inference, the accuracy of directly training the small model is poor due to the weak learning and generalization capabilities of the small model. In order to train a high-precision small model, in this embodiment, a knowledge distillation method is adopted - using a large model (such as a teacher model or a teacher network) to guide the training of a small student model or a small student network (i.e., a cardinality estimation model).

[0180] For example, the student model can be matched Figure 8 ( Figure 8 The output of the teacher model shown in FIG1 is a schematic diagram of a model compression based on knowledge distillation in an embodiment of the present disclosure to learn useful knowledge, which is easier to fit than the training data because it is generated by the teacher model with the same structure as the student model. In order to perform knowledge distillation, a teacher model with high complexity and accuracy can be trained first (such as Figure 8 The “Large (teacher) model” shown in , can then be solved by the hint loss (such as Figure 8 The “hint loss” function shown in the figure trains the student model (such as Figure 8 The "Small(student)model" shown in the figure can finally be predicted by the loss function (such as Figure 8 The “Prediction loss” shown in the figure is used to further calibrate the student model to obtain the trained cardinality estimation model.

[0181] In some embodiments, the hint loss function It can be expressed by formula 5:

[0182]

[0183] in, is the output of the embedding module of the teacher model, is the output of the embedding module of the student model, is the node representation produced by the simple recurrent unit module of the teacher model, is the node representation produced by the simple recurrent unit module of the student model, p e () and p s () are two single-layer neural networks used to resize the output of the student model to the same size as the teacher model.

[0184] In some embodiments, the prediction loss function can be expressed by Equation 6:

[0185]

[0186] in, is the q-error of the cardinality estimate of the Student model, is the logit before the sigmoid activation function in the teacher model output module, It is the logit before the sigmoid activation function in the output module of the student model. α is the weight used to balance the two loss terms. It can be set based on demand, historical records, and experiments. For example, α is 0.5.

[0187] In this embodiment, the cardinality estimation model is trained by knowledge distillation, which can greatly compress the cardinality estimation model without reducing the accuracy of the cardinality estimation model and improve the inference speed of the cardinality estimation model.

[0188] It is worth noting that the framework structure of the progressive cardinality estimation model can be the framework structure of the cardinality estimation model.

[0189] S405: Based on the updated estimated cardinality, update the query plan corresponding to the unexecuted node.

[0190] Combined with the above analysis, it can be seen that the updated estimated cardinality has higher accuracy and reliability. Therefore, in this embodiment, by updating the query plan corresponding to the unexecuted node in combination with the updated estimated cardinality, the updated query plan corresponding to the unexecuted node can have higher validity and reliability.

[0191] The database query optimization method of the above embodiment can be applied to other database engines such as PostgreSQL, MySQL, and SQLite. To help readers more deeply understand the application of the database query optimization method of the present disclosure and the technical effects generated based on the application, the database query optimization method of the present disclosure is applied to PostgreSQL as an example for exemplary explanation.

[0192] PostgreSQL's query optimizer uses a dynamic programming algorithm to enumerate possible query plans: for a query joining n tables, the query optimizer begins enumerating possible query plans from the base tables at level 1. At level i, the enumerated query plan needs to join i tables, and the query optimizer also calls the cardinality estimator to perform cardinality estimation on all nodes of the enumerated query plan. At level n, the query optimizer can enumerate query plans that include all tables, but these query plans may have different structures, and the query plan space search ends.

[0193] In the application scenario of the cardinality estimation model, the histogram-based cardinality estimator in PostgreSQL can be replaced with the cardinality estimation model of the disclosed embodiment to obtain more accurate cardinality estimation. Because all query plans at the same level connect to the same number of relational tables and the query plan's feature vector is small, the cardinality estimation model performs estimates in batches. To avoid repeated calculations, the cardinality estimation results of the query cardinality estimation model are stored in a memory pool for subsequent reuse.

[0194] PostgreSQL query execution time can be decomposed into planning time T P1 and execution time T E1 , the overhead of its histogram-based cardinality estimator is negligible. Using the cardinality estimation model, the end-to-end query execution time of PostgreSQL is T end1 It can be expressed by formula 7:

[0195] T end1 =T P1 +T I1 +T E1

[0196] Among them, T I1 is the model inference time of cardinality estimation. For execution time T E1 For shorter queries, when the cardinality estimation model is more complex, T I1 will dominate.

[0197] According to the method in the related art, we randomly generated query sets with 6 joins and 8 joins, and decomposed the end-to-end time of all cardinality estimates on these two query sets, and obtained the following Figure 9 ( Figure 9 The experimental results are shown in the figure (decomposition diagram of the end-to-end execution time of different cardinality estimators).

[0198] like Figure 9 As shown in Figure 2, although the data-driven estimator can find a better execution plan due to its higher estimation accuracy, the cardinality estimation model has a simple model structure and a shorter estimation time, which makes it have a shorter end-to-end execution time T than the existing data-driven estimator in most cases. end .

[0199] In the scenario where the progressive cardinality estimation model is applied, when a node finishes execution, q-error is used to measure the difference between the actual cardinality of its result table and the initial estimated cardinality. If the difference is greater than a threshold (which can be set to 50 based on experience), query re-optimization is triggered.

[0200] When performing query reoptimization, the query optimizer can call the progressive cardinality estimation model to improve the initial estimated cardinality of unexecuted nodes in the query plan. It then explores the optimal plan for the currently unexecuted node and the optimal plan for all nodes (the query optimizer obtains a portion of the true cardinality) and selects the optimal plan to continue execution.

[0201] To support query reoptimization, checkpoints can be used to record the difference between the actual cardinality and the initial estimate. A checkpoint is a simple counter that counts the tuples output by the query plan. Checkpoints can be placed after query plans that interrupt pipeline processing to cache tuples (thus materializing intermediate results). Checkpoints can also be placed at the node level.

[0202] PostgreSQL has three join implementations, namely hash join (such as Figure 10 "Hash join" as shown), merge join (as shown Figure 10 "Merge join" as shown) and nested loop joins (as shown Figure 10 "Nested loop join" as shown). Figure 10 As shown in (a) and (b), checkpoint (such as Figure 10 CHECK (shown in the example) is placed on the inner side (smaller table) of a hash join after the hash table is built, and after sorting on both sides of a merge join. Since both hash joins and merge joins materialize tuples in PostgreSQL (e.g. Figure 10 MV), so no additional materialization overhead is incurred. However, for nested loop joins, both ends are pipelined. Figure 10 As shown in (c), we interrupt pipeline processing and materialize tuples after scanning the outer table (the smaller table). To test the overhead of nested loop joins, we ran 500 queries from the IMDB benchmark and achieved 1.2% and 5.8% improvement in query execution time and peak memory consumption, respectively. This indicates that our modification imposes little overhead because the nested loop join is only used when the cardinality of the outer layer is small.

[0203] For queries that trigger re-optimization, that is, when using the progressive cardinality estimation model, the end-to-end query execution time T in PostgreSQL is end2 It can be expressed by formula 8:

[0204] T end2 =T P2 +T I2 +T E2 +T R

[0205] Among them, T I2 is the asymptotic cardinality estimation model inference time, T R is the reoptimization time, T E2 is the execution time, T P2 Planning time. Reoptimization may be triggered multiple times for a query plan, and you can set a maximum number of reoptimizations, such as 3, to control the reoptimization overhead.

[0206] In order to experimentally verify the superiority of the database query optimization method of the embodiment of the present disclosure, two test data sets containing 500 queries with 6 joins and 8 joins respectively, and a training set containing 10,000 queries can be randomly generated based on the method in the related art. The selected comparison objects may include: (1) query-driven estimators: MSCN, TLSTM, Flow-Los; (2) data-driven estimators: DeepDB, Neurocard, FLAT; (3) hybrid-driven estimator: UAE; (4) single cardinality estimation model, cardinality estimation model + progressive cardinality estimation model.

[0207] from Figure 1 It can be seen that compared with the previous query-driven estimator, the method of the present disclosure (such as Figure 1 The inference time of the LPCE (shown as "LPCE") is less than the average inference time of other models, while the estimation error (or error) is reduced by at least 2x. Compared with the data-driven estimator, the LPCE inference time is less than 1% of the other models, while the estimation error is no more than doubled. LPCE achieves both high accuracy and fast inference.

[0208] from Figure 11 ( Figure 11 (This is a diagram showing the change in estimation error of the progressive cardinality estimation model during query plan execution disclosed herein.) As can be seen, as the number of executed nodes increases during query plan execution, the cardinality estimation error of the progressive cardinality estimation model decreases, demonstrating that the progressive cardinality estimation model effectively addresses the problem of large cardinality estimation for complex queries. In addition to the advantages of high accuracy and fast inference, LPCE has the shortest end-to-end execution time compared to other models. In some embodiments, the performance gain R of the model on PostgreSQL can be represented by Equation 9:

[0209]

[0210] Among them, T Postgres is the PostgreSQL time, T Learn It’s reasoning time.

[0211] For example, the performance gains of all compared models for PostgreSQL are as follows: Figure 12 ( Figure 12 (The figure shows the change in the end-to-end execution time of PostgreSQL using the estimator.) Figure 12 As shown, using LPCE has the largest end-to-end time reduction compared to other cardinality estimators.

[0212] in, Figure 12 "Join-six" means six joins, "Join-eight" means eight joins, "5th" means the fifth node, "25th" means the 25th node, and so on. We will not list them all here.

[0213] See also Figure 13 , Figure 13 Schematic diagram of the database query optimization device according to the embodiment of the present disclosure. Figure 13 As shown, the database query optimization device 1300 includes:

[0214] The acquisition unit 1301 is used to acquire the actual cardinality of the executed nodes and the query content in the query plan.

[0215] The fusion unit 1302 is configured to fuse the real cardinality and the query content to obtain fusion information.

[0216] The first updating unit 1303 is configured to update the initial estimated cardinality of the unexecuted nodes in the query plan according to the fusion information to obtain an updated estimated cardinality.

[0217] In some embodiments, the true cardinality is extracted from the executed node based on a cardinality module in a pre-trained progressive cardinality estimation model.

[0218] The query content is extracted from the executed node based on a content module in the progressive cardinality estimation model.

[0219] The fusion information is obtained by fusing the true cardinality and the query content based on a connection layer in the progressive cardinality estimation model.

[0220] The updated estimated cardinality is obtained by updating the initial estimated cardinality of the unexecuted node according to the fusion information by an improvement module in the progressive cardinality estimation model.

[0221] The progressive cardinality estimation model is obtained by training based on the first sample data.

[0222] In some embodiments, the progressive cardinality estimation model is obtained by pre-training a pre-trained model based on a first data set, and adjusting an improvement module in the pre-trained model based on a second data set.

[0223] The first sample data includes the first data set and the second data set.

[0224] In some embodiments, each node in the query plan has an initial estimated cardinality, and the initial estimated cardinality of each node is determined according to characteristics of the node and characteristics of its child nodes.

[0225] In some embodiments, the initial estimated cardinality of each node is determined based on a preset cardinality estimation model.

[0226] The cardinality estimation model is obtained by training based on the second sample data, and the cardinality estimation model includes an embedding module, a simple cyclic unit module, and an output module.

[0227] The embedding module is used to adjust the encoding feature vector of a first length corresponding to each node to an encoding feature vector of a second length, where the first length is greater than the second length.

[0228] The simple cycle unit module is used to determine the cardinality estimation feature of each node based on the encoded feature vector of the second length of each node and the features of the child nodes of the node.

[0229] The output module is used to determine the initial estimated cardinality of each node according to the cardinality estimation feature of the node.

[0230] In some embodiments, the cardinality estimation model is trained based on knowledge distillation.

[0231] The second updating unit 1304 is configured to update the query plan corresponding to the unexecuted node based on the updated estimated cardinality.

[0232] According to an embodiment of the present disclosure, the present disclosure also provides an electronic device, a readable storage medium, and a computer program product.

[0233] According to an embodiment of the present disclosure, the present disclosure also provides a computer program product, which includes: a computer program, the computer program is stored in a readable storage medium, at least one processor of an electronic device can read the computer program from the readable storage medium, and at least one processor executes the computer program so that the electronic device executes the solution provided by any of the above embodiments.

[0234] Figure 14A schematic block diagram of an example electronic device 1400 that can be used to implement embodiments of the present disclosure is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital assistants, cellular phones, smartphones, wearable devices, and other similar computing devices. The components shown herein, their connections and relationships, and their functions are provided as examples only and are not intended to limit the implementation of the present disclosure described and / or claimed herein.

[0235] like Figure 14 As shown, device 1400 includes a computing unit 1401, which can perform various appropriate actions and processes according to a computer program stored in a read-only memory (ROM) 1402 or a computer program loaded from a storage unit 1408 into a random access memory (RAM) 1403. Various programs and data required for the operation of device 1400 can also be stored in RAM 1403. Computing unit 1401, ROM 1402, and RAM 1403 are connected to each other via a bus 1404. An input / output (I / O) interface 1405 is also connected to bus 1404.

[0236] Various components in device 1400 are connected to I / O interface 1405, including: an input unit 1406, such as a keyboard, mouse, etc.; an output unit 1407, such as various types of displays, speakers, etc.; a storage unit 1408, such as a magnetic disk, optical disk, etc.; and a communication unit 1409, such as a network card, modem, wireless communication transceiver, etc. Communication unit 1409 allows device 1400 to exchange information / data with other devices via a computer network such as the Internet and / or various telecommunication networks.

[0237] The computing unit 1401 can be a variety of general-purpose and / or specialized processing components with processing and computing capabilities. Some examples of the computing unit 1401 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various dedicated artificial intelligence (AI) computing chips, various computing units that run machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The computing unit 1401 performs the various methods and processes described above, such as the database query optimization method. For example, in some embodiments, the database query optimization method can be implemented as a computer software program that is tangibly contained in a machine-readable medium, such as the storage unit 1408. In some embodiments, part or all of the computer program can be loaded and / or installed on the device 1400 via the ROM 1402 and / or the communication unit 1409. When the computer program is loaded into the RAM 1403 and executed by the computing unit 1401, one or more steps of the database query optimization method described above can be performed. Alternatively, in other embodiments, the computing unit 1401 may be configured to execute the database query optimization method in any other appropriate manner (eg, by means of firmware).

[0238] Various embodiments of the systems and techniques described above can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), system-on-chip systems (SOCs), complex programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments can include being implemented in one or more computer programs that are executable and / or interpreted on a programmable system that includes at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0239] The program code for implementing the method of the present disclosure can be written in any combination of one or more programming languages. These program codes can be provided to a processor or controller of a general-purpose computer, a special-purpose computer, or other programmable data processing device so that when the program code is executed by the processor or controller, the functions / operations specified in the flow chart and / or block diagram are implemented. The program code can be executed entirely on the machine, partially on the machine, as a stand-alone software package, partially on the machine and partially on a remote machine, or entirely on a remote machine or server.

[0240] In the context of the present disclosure, a machine-readable medium can be a tangible medium that can contain or store a program for use by or in conjunction with an instruction execution system, device or equipment. A machine-readable medium can be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium can include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or equipment, or any suitable combination of the foregoing. A more specific example of a machine-readable storage medium can include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0241] To provide interaction with a user, the systems and techniques described herein can be implemented on a computer having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user can provide input to the computer. Other types of devices can also be used to provide interaction with the user; for example, the feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic input, voice input, or tactile input).

[0242] The systems and techniques described herein can be implemented in a computing system that includes back-end components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes front-end components (e.g., a user computer having a graphical user interface or a web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such back-end components, middleware components, or front-end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include a local area network (LAN), a wide area network (WAN), and the Internet.

[0243] A computer system may include a client and a server. The client and server are generally remote from each other and typically interact via a communication network. This client-server relationship is established by computer programs running on the respective computers, establishing a client-server relationship. The server may be a cloud server, also known as a cloud computing server or cloud host, a host product within the cloud computing service ecosystem that addresses the management difficulties and limited scalability of traditional physical hosts and VPS services ("Virtual Private Servers" or simply "VPS"). The server may also be a server in a distributed system or a server integrated with blockchain.

[0244] Those skilled in the art will appreciate that the embodiments of the present disclosure may be provided as methods, systems, or computer program products. Therefore, the present disclosure may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Furthermore, the present disclosure may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage and optical storage, etc.) containing computer-usable program code.

[0245] The present disclosure is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present disclosure. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer-executable instructions. These computer-executable instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 a process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0246] These processor-executable instructions may also be stored in a processor-readable memory that can direct a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the processor-readable memory produce an article of manufacture comprising an instruction device that implements the process Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0247] These processor-executable instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are performed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for executing on the computer or other programmable device to implement the process. Figure 1 a process or multiple processes and / or boxes Figure 1 The steps for the function specified in one or more boxes.

[0248] Obviously, those skilled in the art may make various changes and modifications to the present disclosure without departing from the spirit and scope of the present disclosure. Thus, if these modifications and variations of the present disclosure fall within the scope of the claims of the present disclosure and their equivalents, the present disclosure is intended to include these modifications and variations.

Claims

1. A database query optimization method, characterized in that: The method comprises: Get the actual cardinality of executed nodes and query content in the query plan; Fusing the real cardinality and the query content to obtain fusion information; updating the initial estimated cardinality of the unexecuted nodes in the query plan according to the fusion information to obtain an updated estimated cardinality; Each node in the query plan has an initial estimated cardinality, the initial estimated cardinality of each node is determined based on characteristics of the node and characteristics of its child nodes, and the initial estimated cardinality of each node is determined based on a preset cardinality estimation model; The cardinality estimation model is obtained by training based on the second sample data. The cardinality estimation model includes an embedding module, a simple cyclic unit module, and an output module. The embedding module is used to adjust the encoding feature vector of the first length corresponding to each node to the encoding feature vector of the second length, and the first length is greater than the second length. The simple cyclic unit module is used to determine the cardinality estimation feature of the node based on the encoding feature vector of the second length of each node and the features of the child nodes of the node. The output module is used to determine the initial estimated cardinality of the node based on the cardinality estimation feature of each node.

2. The method according to claim 1, characterized in that The true cardinality is extracted from the executed node based on a cardinality module in a pre-trained progressive cardinality estimation model; The query content is extracted from the executed node based on the content module in the progressive cardinality estimation model; The fusion information is obtained by fusing the true cardinality and the query content based on the connection layer in the progressive cardinality estimation model; The updated estimated cardinality is obtained by updating the initial estimated cardinality of the unexecuted node according to the fusion information based on the improvement module in the progressive cardinality estimation model; The progressive cardinality estimation model is obtained by training based on the first sample data.

3. The method according to claim 2, characterized in that The progressive cardinality estimation model is obtained by pre-training a pre-trained model based on a first data set, and adjusting an improvement module in the pre-trained model based on a second data set; The first sample data includes the first data set and the second data set.

4. The method according to claim 1, wherein The cardinality estimation model is trained based on knowledge distillation.

5. The method according to any one of claims 1 to 3, characterized in that The method further comprises: Based on the updated estimated cardinality, the query plan corresponding to the unexecuted node is updated.

6. A database query optimization device, characterized in that: include: The acquisition unit is used to obtain the actual cardinality of executed nodes and query content in the query plan; a fusion unit, configured to fuse the real cardinality and the query content to obtain fusion information; an updating unit, configured to update an initial estimated cardinality of unexecuted nodes in the query plan according to the fusion information to obtain an updated estimated cardinality; Each node in the query plan has an initial estimated cardinality, the initial estimated cardinality of each node is determined based on characteristics of the node and characteristics of its child nodes, and the initial estimated cardinality of each node is determined based on a preset cardinality estimation model; The cardinality estimation model is obtained by training based on the second sample data. The cardinality estimation model includes an embedding module, a simple cyclic unit module, and an output module. The embedding module is used to adjust the encoding feature vector of the first length corresponding to each node to the encoding feature vector of the second length, and the first length is greater than the second length. The simple cyclic unit module is used to determine the cardinality estimation feature of the node based on the encoding feature vector of the second length of each node and the features of the child nodes of the node. The output module is used to determine the initial estimated cardinality of the node based on the cardinality estimation feature of each node.

7. The database query optimization device according to claim 6, characterized in that: The true cardinality is extracted from the executed node based on a cardinality module in a pre-trained progressive cardinality estimation model; The query content is extracted from the executed node based on the content module in the progressive cardinality estimation model; The fusion information is obtained by fusing the true cardinality and the query content based on the connection layer in the progressive cardinality estimation model; The updated estimated cardinality is obtained by updating the initial estimated cardinality of the unexecuted node according to the fusion information based on the improvement module in the progressive cardinality estimation model; The progressive cardinality estimation model is obtained by training based on the first sample data.

8. The database query optimization device according to claim 7, characterized in that: The progressive cardinality estimation model is obtained by pre-training a pre-trained model based on a first data set, and adjusting an improvement module in the pre-trained model based on a second data set; The first sample data includes the first data set and the second data set.

9. A processor-readable storage medium, characterized in that: The processor-readable storage medium stores a computer program, and the computer program is configured to cause the processor to execute the method according to any one of claims 1 to 5.

10. 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 method according to any one of claims 1 to 5.

Images