Database query optimization method and system based on metric learning and correlation correction

Abstract

The application belongs to the technical field of databases, and specifically discloses a database query optimization method and system based on metric learning and correlation correction. The method comprises the following steps: constructing a training set based on data in a database table, training a metric learning model based on the training set to learn correlation information between columns, and storing the correlation information as extended statistical metadata; responding to and analyzing a query statement, obtaining single-column statistical information of relevant columns and statistical information of multi-column correlation from the extended statistical metadata; calculating an initial selection rate based on the column independence assumption, correcting the initial selection rate based on the multi-column correlation to obtain a final multi-column selection rate after correction; and estimating query cost based on the final multi-column selection rate, and selecting an execution path according to the estimation result to generate a final execution plan. Through the application, the accuracy of multi-column selection rate estimation can be improved.

Description

Technical Field

[0001] This application belongs to the field of database technology, and more specifically, relates to a database query optimization method and system based on metric learning and relevance correction. Background Technology

[0002] Currently, in databases that implement query optimization based on the System-R framework, query optimization uses cost-based query optimization to calculate the cost of various physical paths and select the optimal execution path based on the cost. The cost estimation process relies on database statistics to calculate how much data a constraint can filter out. The proportion of data filtered out by this constraint relative to the total amount of data is called the selectivity.

[0003] These databases share a common characteristic when calculating selectivity: for single-column constraints, they use statistics based on that single column to estimate the selectivity; for constraints referencing multiple columns, they need to break the constraint down into multiple independent sub-constraints, estimate the selectivity for each sub-constraint, and assume that the selectivity of these sub-constraints is independent, using a probability-based method to estimate the total selectivity. This current estimation method is based on the premise that the multiple constraints are independent of each other, and the statistical information of multiple columns is incomplete, which may lead to excessive estimation errors.

[0004] Therefore, how to solve the problem of excessive error in traditional multi-column selection rate estimation is a technical problem that urgently needs to be solved. Summary of the Invention

[0005] In view of the shortcomings of the prior art, the purpose of this application is to reduce the estimation error of multi-column selection rate.

[0006] To achieve the above objectives, firstly, this application provides a database query optimization method based on metric learning and relevance correction, comprising: A training set is constructed based on data in the database table. A metric learning model is trained based on the training set to learn the correlation information between columns. The correlation information is stored as extended statistical metadata. Respond to and parse the query statement, and obtain single-column statistical information and multi-column correlation statistical information of the relevant columns from the extended statistical metadata; The initial selection rate is calculated based on the column independence assumption, and the initial selection rate is corrected based on the multi-column correlation to obtain the corrected final multi-column selection rate. The query cost is estimated based on the final multi-column select rate, and the execution path is selected based on the estimation result to generate the final execution plan.

[0007] Optionally, the method for constructing the training set includes: Determine the joint probability and independent probability of the first and second columns of data in the sampled data; Calculate the ratio of the joint probability to the independent probability, and set an upper limit and a lower limit for the ratio to limit the ratio. The correlation score is determined by combining the ratio with the upper and lower limits of the ratio, and the correlation score is used as the training set label to construct the training set.

[0008] Optionally, the process of acquiring relevance information during the training phase of the metric learning model includes: The first and second columns of data are vectorized to obtain the first data vector and the second data vector, respectively. Similarity is calculated based on the first data vector and the second data vector to obtain cosine similarity, and the cosine similarity is linearly scaled. The similarity value output by the metric learning model is determined as the relevance information based on the linearly scaled cosine similarity, the upper limit of the ratio, and the lower limit of the ratio.

[0009] Optionally, the step of responding to and parsing the query statement, and obtaining single-column statistical information and multi-column correlation statistical information of relevant columns from the extended statistical metadata, includes: The database parser receives Structured Query Language (SQL) statements. In response to the SQL statement, the SQL statement is parsed into an abstract syntax tree, which includes multiple columns of constraint information; The database optimizer reads single-column statistical information and multi-column correlation statistical information from the extended statistical metadata based on multi-column constraint information.

[0010] Optionally, the process for correcting the final multi-column selection rate includes: The correlation metric between columns is determined based on the multi-column correlation and the strength range; the strength range is determined based on a preset threshold parameter and its reciprocal. If the judgment result is that there is a correlation, then the correlation correction coefficient is calculated based on the single-column selection rate of the first column data and the single-column selection rate of the second column data. The initial selection rate is adjusted using the correlation correction coefficient to obtain the final multi-column selection rate after correction.

[0011] Optionally, the intensity range is ,like If the columns are independent, then the columns are determined to be independent; if If the columns are positively correlated, then the columns are determined to be positively correlated; if If the columns are negatively correlated, then the columns are determined to be negatively correlated; where, This represents a correlation metric. This indicates the preset threshold parameter.

[0012] Optionally, the query cost estimation based on the final multi-column select rate includes: The number of rows is determined by estimating the final multi-column selection rate. The CPU cost and I / O cost of different execution paths are calculated based on the number of rows. The total cost is obtained based on the CPU cost and I / O cost. The total cost corresponding to different execution paths is compared to select the optimal execution path.

[0013] Optionally, it also includes: The database executor receives and executes the final execution plan to complete data reading, sorting, and aggregation, obtains the execution result, and returns the execution result to the client.

[0014] Secondly, this application also provides a database query optimization system based on metric learning and relevance correction, including: The model training module is used to construct a training set based on data in the database table, train a metric learning model based on the training set to learn the correlation information between columns, and store the correlation information as extended statistical metadata. The statement response module is used to respond to and parse the query statement, and obtain single-column statistical information and multi-column correlation statistical information of the relevant columns from the extended statistical metadata. The selection rate correction module is used to calculate and determine the initial selection rate based on the column independence assumption, and correct the initial selection rate based on the multi-column correlation to obtain the corrected final multi-column selection rate. The cost estimation module is used to estimate the query cost based on the final multi-column selection rate, and select the execution path according to the estimation result to generate the final execution plan.

[0015] Thirdly, this application provides an electronic device, comprising: at least one memory for storing a program; and at least one processor for executing the program stored in the memory, wherein when the program stored in the memory is executed, the processor is configured to execute the method described in the first aspect or any possible implementation thereof.

[0016] Fourthly, this application provides a computer-readable storage medium storing a computer program that, when run on a processor, causes the processor to perform the method described in the first aspect or any possible implementation thereof.

[0017] Fifthly, this application provides a computer program product that, when run on a processor, causes the processor to perform the method described in the first aspect or any possible implementation thereof.

[0018] It is understood that the beneficial effects of the second to fifth aspects mentioned above can be found in the relevant descriptions in the first aspect mentioned above, and will not be repeated here.

[0019] Overall, the technical solutions conceived in this application have the following beneficial effects compared with the prior art: This application fundamentally reduces the estimation error of multi-column selectivity in database query optimization by introducing a relevance modeling and correction mechanism based on metric learning. During the statistical information collection phase, a metric learning model automatically learns continuous and quantified relevance measures between columns from the data distribution, overcoming the limitations of traditional binary or static relevance expressions. During the query optimization phase, the optimizer dynamically determines whether to adopt the independence assumption based on this relevance measure and intelligently corrects the initial selectivity under the independence assumption. This correction mechanism adaptively captures both positive and negative relevance, increasing the underestimated selectivity when columns are positively correlated and decreasing the overestimated selectivity when columns are negatively correlated. This prevents the optimizer from allocating too much memory or incorrectly abandoning parallel execution due to overoptimism, effectively avoiding memory overflow and insufficient parallelism. Therefore, this application significantly improves the accuracy of multi-column selectivity estimation, enabling the optimizer to generate better execution plans, reducing query latency while improving the overall utilization efficiency of system resources. Attached Figure Description

[0020] Figure 1 This is a flowchart illustrating the database query optimization method based on metric learning and relevance correction provided in the embodiments of this application; Figure 2 This is a schematic diagram illustrating the process of learning and training a model using the ANALYZE process in an embodiment of this application; Figure 3 This is a schematic diagram illustrating the selection rate calculation based on the metric model in an embodiment of this application; Figure 4 This is a schematic diagram of the structure of the database query optimization device based on metric learning and relevance correction provided in the embodiments of this application; Figure 5 This is a schematic diagram of the structure of the electronic device provided in the embodiments of this application. Detailed Implementation

[0021] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0022] In this article, the term "and / or" describes the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, or B existing alone. The symbol " / " in this article indicates that the related objects are in an "or" relationship; for example, A / B means A or B.

[0023] The terms "first" and "second," etc., used in the specification and claims herein are used to distinguish different objects, not to describe a specific order of objects. For example, "first response message" and "second response message," etc., are used to distinguish different response messages, not to describe a specific order of response messages.

[0024] In the embodiments of this application, the terms "exemplary" or "for example" are used to indicate that something is an example, illustration, or description. Any embodiment or design that is described as "exemplary" or "for example" in the embodiments of this application should not be construed as being more preferred or advantageous than other embodiments or design. Specifically, the use of the terms "exemplary" or "for example" is intended to present the relevant concepts in a specific manner.

[0025] In the description of the embodiments of this application, unless otherwise stated, "multiple" means two or more, for example, multiple processing units means two or more processing units, multiple elements means two or more elements, etc.

[0026] The embodiments of this application are described below with reference to the accompanying drawings.

[0027] Reference Figure 1 This application provides a database query optimization method based on metric learning and relevance correction, including: S101. Construct a training set based on data in the database table, train a metric learning model based on the training set to learn the correlation information between columns, and store the correlation information as extended statistical metadata; S102. Respond to and parse the query statement, and obtain single-column statistical information and multi-column correlation statistical information of relevant columns from the extended statistical metadata; S103. Calculate and determine the initial selection rate based on the column independence assumption, and correct the initial selection rate based on the multi-column correlation to obtain the corrected final multi-column selection rate; S104. Estimate the query cost based on the final multi-column select rate, and select the execution path based on the estimation results to generate the final execution plan.

[0028] Specifically, step S101 is the foundational preparation stage of this application, designed to create and store key knowledge for accurately estimating the selection rate. Specifically, a parallel machine learning task is embedded during the execution of routine statistical information collection commands by the database system. The target database table is sampled, and a dedicated training set is constructed using the sampled data. The core of constructing this training set lies in calculating a relevance label for each pair of column data samples. This label is determined by the ratio of the joint probability to the independent probability of the sample, and is constrained by specific upper and lower limits (such as 0.1 and 5) to prevent interference from extreme values.

[0029] Subsequently, this training set is used to train the metric learning model, enabling it to learn and internalize complex, continuous correlation patterns between columns, rather than simply relying on traditional "relevance" binary judgments. Once training is complete, the correlation knowledge acquired by the model (typically quantified as a correlation coefficient matrix or queryable key-value pairs) will be serialized and persistently stored as extended statistical metadata in the database's system tables.

[0030] In step S102, when a user submits a query request containing multiple constraints, this step initiates the optimization process and gathers all necessary decision information. First, the database parser performs lexical and syntactic analysis on the SQL statement, generating an Abstract Syntax Tree (AST), from which it precisely extracts the involved columns and their constraints. Subsequently, the optimizer performs information retrieval based on the multiple constraint information in the AST: reading single-column statistics of relevant columns from traditional statistical metadata, and querying and obtaining multi-column correlation statistics between these columns from extended statistical metadata.

[0031] In step S103, the optimizer first uses the traditional method, based on the single-column statistical information obtained in S102, assuming that the conditions in each column are independent, and calculates the initial selection rate using rules such as probability multiplication. Next, it calls the introduced correction logic: based on the multi-column correlation statistics obtained in S102, it first determines whether the columns involved are independent, positively correlated, or negatively correlated. If they are determined to be independent, the initial selection rate is directly used; if they are determined to be correlated, a correction coefficient is calculated based on the correlation metric.

[0032] For positively correlated columns, the correction coefficient is typically greater than 1, used to correct the underestimated initial selection rate upwards; for negatively correlated columns, the correction coefficient is less than 1, used to correct the overestimated initial selection rate downwards. Through dynamic correction based on data-driven relationships, a final multi-column selection rate that more closely reflects the true data distribution is obtained, significantly improving estimation accuracy.

[0033] It's worth noting that in databases implementing query optimization based on the System-R framework, query optimization uses cost-based query optimization to calculate the cost of various physical paths and select the optimal execution path based on the cost. The cost estimation process relies on database statistics to calculate how much data a constraint can filter out. The proportion of data filtered out by this constraint relative to the total data volume is called the selectivity, calculated using the following formula: ; When calculating selectivity in databases based on the System-R framework, there is a common characteristic: for single-column constraints, selectivity is estimated using statistics based on that single column; for constraints referencing multiple columns, the constraint needs to be broken down into multiple independent sub-constraints, and selectivity is estimated for each sub-constraint. Furthermore, it is assumed that the selectivity of these sub-constraints is independent, and the total selectivity is estimated using a probabilistic method.

[0034] Finally, after obtaining a high-precision final multi-column selectivity in step S104, this step completes the final decision for query optimization. The optimizer multiplies the final selectivity by the total number of rows in the table to obtain a more accurate estimated number of rows. Based on the row count estimate, the optimizer can more realistically calculate the I / O operation cost, CPU processing cost, and memory consumption required for different candidate execution paths.

[0035] Ultimately, the optimizer compares the total cost of all paths, selects the path with the lowest estimated cost, generates the optimal final execution plan, and delivers it to the executor, thereby fundamentally improving query execution efficiency and optimizing the utilization of system resources (CPU, memory, disk I / O).

[0036] Optionally, the method for constructing the training set includes: Determine the joint probability and independent probability of the first and second columns of data in the sampled data; Calculate the ratio of the joint probability to the independent probability, and set an upper limit and a lower limit for the ratio to limit the ratio. The correlation score is determined by combining the ratio with the upper and lower limits of the ratio, and the correlation score is used as the training set label to construct the training set.

[0037] The process of acquiring relevance information during the training phase of the metric learning model includes: The first and second columns of data are vectorized to obtain the first data vector and the second data vector, respectively. Similarity is calculated based on the first data vector and the second data vector to obtain cosine similarity, and the cosine similarity is linearly scaled. The similarity value output by the metric learning model is determined as the relevance information based on the linearly scaled cosine similarity, the upper limit of the ratio, and the lower limit of the ratio.

[0038] Specifically, in this embodiment, a dataset is constructed using the existing data in the table. The correlation score is calculated as a label by combining the joint distribution probability and the independent distribution probability. The column data is used as training samples and input into the metric model to train and learn the correlation between columns, thereby obtaining more accurate data distribution information between columns. The correlation is then saved in the metadata of the extended statistics.

[0039] The tag calculation method is as follows: ; in, Indicates the training set labels. This represents the data in the first column. This represents the data in the second column; Denotes the joint probability. This represents the independent probability. A ratio greater than 5 is truncated to prevent excessive statistical information from individual column combinations. The lower limit of the ratio is set to 0.1 to prevent information from returning to zero.

[0040] The model correlation output is calculated as follows: ; in, This represents the similarity value obtained from the model. It is the linearly scaled cosine similarity, with values ​​ranging from 1 to 10. , Represents the first data vector. This represents the second data vector.

[0041] Optionally, the step of responding to and parsing the query statement, and obtaining single-column statistical information and multi-column correlation statistical information of relevant columns from the extended statistical metadata, includes: The database parser receives Structured Query Language (SQL) statements. In response to the SQL statement, the SQL statement is parsed into an abstract syntax tree, which includes multiple columns of constraint information; The database optimizer reads single-column statistical information and multi-column correlation statistical information from the extended statistical metadata based on multi-column constraint information.

[0042] Specifically, in this embodiment, when a user submits a Structured Query Language (SQL) statement, the database system's parser first receives it and performs lexical and syntactic analysis on it, converting it into an intermediate data structure called an Abstract Syntax Tree (AST). The AST accurately represents the logical structure of the query in a tree-like form, containing key multi-column constraint information.

[0043] The database optimizer is triggered to traverse the Abstract Syntax Tree (AST) and extract these multi-column constraint information. Based on the extracted column names, the optimizer performs two parallel metadata queries: firstly, it reads single-column statistics of the relevant columns from the traditional system catalog; secondly, it retrieves multi-column correlation statistics corresponding to these column combinations, pre-calculated and stored by the metric learning model, from the extended statistical metadata area. This process clarifies a clear and automated technical path from the original SQL to all the statistical data required for optimization decisions.

[0044] Optionally, the process for correcting the final multi-column selection rate includes: The correlation metric between columns is determined based on the multi-column correlation and the strength range; the strength range is determined based on a preset threshold parameter and its reciprocal. If the judgment result is that there is a correlation, then the correlation correction coefficient is calculated based on the single-column selection rate of the first column data and the single-column selection rate of the second column data. The initial selection rate is adjusted using the correlation correction coefficient to obtain the final multi-column selection rate after correction.

[0045] Optionally, the intensity range is ,like If the columns are independent, then the columns are determined to be independent; if If the columns are positively correlated, then the columns are determined to be positively correlated; if If so, then the columns are determined to be negatively correlated.

[0046] Specifically, in this embodiment, based on the acquired multi-column correlation statistics (e.g., similarity values ​​output by a metric learning model), the data is compared with a dynamic intensity range. This intensity range is determined by a configurable preset threshold parameter. and its reciprocal By jointly defining it, a judgment interval is formed. .like If the values ​​fall within this interval, then the columns are considered statistically independent, and the traditional method is used; if... If the value exceeds this range, a correlation is determined to exist.

[0047] The judgment rule is as follows: when the metric learning model outputs relevance information, i.e., similarity value... satisfy When the system determines that the columns involved satisfy the independence assumption; when When the columns are positively correlated, it means that as the value in one column increases, the value in the other column also tends to increase; when When the columns are negatively correlated, it means that as the value in one column increases, the value in another column tends to decrease. This rule will determine the continuous values ​​output by the model. Discretization into three distinct semantic states—independent, positively correlated, and negatively correlated—provides a direct logical basis for subsequent differentiated correction strategies (such as increasing the selection rate for positively correlated individuals and decreasing the selection rate for negatively correlated individuals).

[0048] Once a correlation is determined, a correlation correction coefficient is calculated based on the single-column selection rate of each column (i.e., the probability that each column's constraints independently filter data).

[0049] ; in, This is the correlation correction coefficient. This is a correlation metric. The single-column selection rate of the first column. This represents the single-column selection rate for the second column.

[0050] Finally, the initial selection rate calculated based on the independence assumption is adjusted using this correction coefficient, thereby outputting a final multi-column selection rate that better reflects the joint distribution of the real data.

[0051] Optionally, the query cost estimation based on the final multi-column select rate includes: The number of rows is determined by estimating the final multi-column selection rate. The CPU cost and I / O cost of different execution paths are calculated based on the number of rows. The total cost is obtained based on the CPU cost and I / O cost. The total cost corresponding to different execution paths is compared to select the optimal execution path.

[0052] Specifically, in this embodiment, the optimizer multiplies the final multi-column selectivity by the total number of rows in the target table to obtain a highly accurate estimated number of rows. Based on this row count estimate, the optimizer calculates the cost for each possible physical execution path, primarily including the estimated number of CPU operation cycles (CPU cost) and the number of disk data blocks to be accessed (I / O cost). According to a predefined cost model, the CPU cost and I / O cost are weighted or summed to obtain the total execution cost for each path. Finally, the optimizer compares the total cost of all candidate paths and selects the path with the lowest estimated cost as the optimal execution path for this query.

[0053] Optionally, it also includes: The database executor receives and executes the final execution plan to complete data reading, sorting, and aggregation, obtains the execution result, and returns the execution result to the client.

[0054] After the optimizer generates the final execution plan, this plan is submitted to the database executor. The executor, as the physical execution engine for the query task, strictly follows the instructions in the execution plan. It reads data pages from disk or memory through the storage engine interface, filters the data, and performs computationally intensive operations such as sorting, grouping, aggregation, and joins according to the plan requirements. After all data processing operators are executed pipelined according to the plan, the final query result set is obtained. Finally, the executor or database front-end interface returns this result set to the client application that initiated the query, thus completing the entire query processing from SQL parsing and intelligent optimization to efficient execution.

[0055] Reference Figure 2 , Figure 2 This is a schematic diagram illustrating the process of learning and training a model using the ANALYZE process in an embodiment of this application, including: Analysis begins; Data sampling; Train the metric model; save the model weights Calculate single-column statistical information such as MCV and histogram; save statistical information. Analysis complete.

[0056] Furthermore, the specific process of database query optimization in this application embodiment includes: Step 1: The parser constructs an AST tree from the query SQL statement. The optimizer reads traditional single-column statistics and multi-column correlation statistics from the metadata in the extended statistics based on the multi-column constraint information in the query tree. Step 2: Determine independence based on the correlation of multiple columns. For positively correlated columns, calculate the correlation coefficient using the correlation statistics of multiple columns. Improve the initial selection rate by using the correlation coefficient to avoid underestimating the selection rate by the independence assumption, thereby obtaining the final selection rate.

[0057] Step 3: Estimate the number of rows based on the final selection rate, and calculate the CPU and I / O costs for each execution path. Use the CPU and I / O costs as the total cost to select the optimal execution path.

[0058] Step 4: Generate the optimal execution calculation based on the optimal execution path, hand it over to the executor to read the data, and perform CPU-intensive computational operations such as sorting and aggregation.

[0059] Step 5: Return the processed data to the client.

[0060] Furthermore, traditional database optimizers are prone to estimation biases caused by predicate laxity, leading to excessively high selectivity calculations, excessive memory budgets, or incorrect abandonment of parallelism. This embodiment improves and optimizes the traditional selectivity calculation for this scenario, and the execution flow is as follows: Step 1: The query compilation layer parses the query SQL statement through the parser to obtain the query tree. The optimizer reads traditional single-column statistical information and multi-column correlation statistical information from the metadata in the extended statistics based on the multi-column constraint information in the query tree. Step 2: Determine independence based on the correlation of multiple columns. For negatively correlated columns, calculate the correlation coefficient using the correlation statistics of multiple columns. Use the correlation coefficient to reduce the initial selection rate, thereby obtaining the final selection rate.

[0061] Step 3: Estimate the number of rows based on the final selection rate, improve the accuracy of memory usage prediction, improve memory utilization, and reduce the risk of memory overflow.

[0062] Step 4: Based on memory estimation and cost calculation, correctly select the parallel query and Join methods to obtain the optimal execution path and generate the final execution plan.

[0063] Step 5: The executor reads data according to the execution plan and performs CPU-intensive computational operations such as sorting and aggregation.

[0064] Step 6: Return the processed data to the client.

[0065] Reference Figure 3 , Figure 3 This is a schematic diagram illustrating the selection rate calculation based on the metric model in an embodiment of this application; SQL begins; Retrieve multi-column constraint information; Call the metric learning model; Relevance measurement judgment; If the columns are determined to be "mutually independent", then the traditional selectivity calculation is used.

[0066] If the column is determined to be "correlated", then the selection rate is adjusted.

[0067] Calculate the overall selection rate; Calculate the path cost and select the optimal path.

[0068] Reference Figure 4 This application also provides a database query optimization system based on metric learning and relevance correction, comprising: The model training module 410 is used to construct a training set based on data in a database table, train a metric learning model based on the training set to learn the correlation information between columns, and store the correlation information as extended statistical metadata. The statement response module 420 is used to respond to and parse the query statement, and obtain single-column statistical information and multi-column correlation statistical information of the relevant columns from the extended statistical metadata. Selection rate correction module 430 is used to calculate and determine the initial selection rate based on the column independence assumption, and correct the initial selection rate based on the multi-column correlation to obtain the corrected final multi-column selection rate; The cost estimation module 440 is used to estimate the query cost based on the final multi-column selection rate, and select the execution path according to the estimation result to generate the final execution plan.

[0069] Optionally, the method for constructing the training set includes: Determine the joint probability and independent probability of the first and second columns of data in the sampled data; Calculate the ratio of the joint probability to the independent probability, and set an upper limit and a lower limit for the ratio to limit the ratio. The correlation score is determined by combining the ratio with the upper and lower limits of the ratio, and the correlation score is used as the training set label to construct the training set.

[0070] Optionally, the process of acquiring relevance information during the training phase of the metric learning model includes: The first and second columns of data are vectorized to obtain the first data vector and the second data vector, respectively. Similarity is calculated based on the first data vector and the second data vector to obtain cosine similarity, and the cosine similarity is linearly scaled. The similarity value output by the metric learning model is determined as the relevance information based on the linearly scaled cosine similarity, the upper limit of the ratio, and the lower limit of the ratio.

[0071] Optionally, the step of responding to and parsing the query statement, and obtaining single-column statistical information and multi-column correlation statistical information of relevant columns from the extended statistical metadata, includes: The database parser receives Structured Query Language (SQL) statements. In response to the SQL statement, the SQL statement is parsed into an abstract syntax tree, which includes multiple columns of constraint information; The database optimizer reads single-column statistical information and multi-column correlation statistical information from the extended statistical metadata based on multi-column constraint information.

[0072] Optionally, the process for correcting the final multi-column selection rate includes: The correlation metric between columns is determined based on the multi-column correlation and the strength range; the strength range is determined based on a preset threshold parameter and its reciprocal. If the judgment result is that there is a correlation, then the correlation correction coefficient is calculated based on the single-column selection rate of the first column data and the single-column selection rate of the second column data. The initial selection rate is adjusted using the correlation correction coefficient to obtain the final multi-column selection rate after correction.

[0073] Optionally, the intensity range is ,like If the columns are independent, then the columns are determined to be independent; if If the columns are positively correlated, then the columns are determined to be positively correlated; if If so, then the columns are determined to be negatively correlated.

[0074] Optionally, the query cost estimation based on the final multi-column select rate includes: The number of rows is determined by estimating the final multi-column selection rate. The CPU cost and I / O cost of different execution paths are calculated based on the number of rows. The total cost is obtained based on the CPU cost and I / O cost. The total cost corresponding to different execution paths is compared to select the optimal execution path.

[0075] Optionally, it also includes an execution module for: The database executor receives and executes the final execution plan to complete data reading, sorting, and aggregation, obtains the execution result, and returns the execution result to the client.

[0076] Reference Figure 5 Based on the methods in the above embodiments, this application provides an electronic device that may include: a processor 510, a communications interface 520, a memory 530, and a communication bus 540. The processor 510, communications interface 520, and memory 530 communicate with each other via the communication bus 540. The processor 510 can call logical instructions stored in the memory 530 to execute the methods in the above embodiments.

[0077] Furthermore, the logical instructions in the aforementioned memory 530 can be implemented as software functional units and, when sold or used as independent products, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or a portion of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application.

[0078] Based on the methods in the above embodiments, this application provides a computer-readable storage medium storing a computer program that, when run on a processor, causes the processor to execute the methods in the above embodiments.

[0079] Based on the methods in the above embodiments, this application provides a computer program product that, when run on a processor, causes the processor to execute the methods in the above embodiments.

[0080] It is understood that the processor in the embodiments of this application can be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, transistor logic devices, hardware components, or any combination thereof. A general-purpose processor can be a microprocessor or any conventional processor.

[0081] The method steps in this application embodiment can be implemented in hardware or by a processor executing software instructions. The software instructions can consist of corresponding software modules, which can be stored in random access memory (RAM), flash memory, read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), registers, hard disks, portable hard disks, CD-ROMs, or any other form of storage medium known in the art. An exemplary storage medium is coupled to the processor, enabling the processor to read information from 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 reside in an ASIC.

[0082] In the above embodiments, implementation can be achieved entirely or partially through software, hardware, firmware, or any combination thereof. When implemented using software, it can be implemented entirely or partially as a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, all or part of the processes or functions described in the embodiments of this application are generated. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium or transmitted through the computer-readable storage medium. The computer instructions can be transmitted from one website, computer, server, or data center to another website, computer, server, or data center via wired (e.g., coaxial cable, fiber optic, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server or data center that integrates one or more available media. The available medium can be a magnetic medium (e.g., floppy disk, hard disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium (e.g., solid-state disk (SSD)).

[0083] It is understood that the various numerical designations used in the embodiments of this application are merely for the convenience of description and are not intended to limit the scope of the embodiments of this application.

[0084] Those skilled in the art will readily understand that the above description is merely a preferred embodiment of this application and is not intended to limit this application. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this application should be included within the scope of protection of this application.

Claims

1. A database query optimization method based on metric learning and relevance correction, characterized in that, include: A training set is constructed based on data in the database table. A metric learning model is trained based on the training set to learn the correlation information between columns. The correlation information is stored as extended statistical metadata. Respond to and parse the query statement, and obtain single-column statistical information and multi-column correlation statistical information of the relevant columns from the extended statistical metadata; The initial selection rate is calculated based on the column independence assumption, and the initial selection rate is corrected based on the multi-column correlation to obtain the corrected final multi-column selection rate. The query cost is estimated based on the final multi-column select rate, and the execution path is selected based on the estimation result to generate the final execution plan.

2. The database query optimization method based on metric learning and relevance correction according to claim 1, characterized in that, The methods for constructing the training set include: Determine the joint probability and independent probability of the first and second columns of data in the sampled data; Calculate the ratio of the joint probability to the independent probability, and set an upper limit and a lower limit for the ratio to limit the ratio. The correlation score is determined by combining the ratio with the upper and lower limits of the ratio, and the correlation score is used as the training set label to construct the training set.

3. The database query optimization method based on metric learning and relevance correction according to claim 1, characterized in that, The process of acquiring relevance information during the training phase of the metric learning model includes: The first and second columns of data are vectorized to obtain the first data vector and the second data vector, respectively. Similarity is calculated based on the first data vector and the second data vector to obtain cosine similarity, and the cosine similarity is linearly scaled. The similarity value output by the metric learning model is determined as the relevance information based on the linearly scaled cosine similarity, the upper limit of the ratio, and the lower limit of the ratio.

4. The database query optimization method based on metric learning and relevance correction according to claim 1, characterized in that, The response parses the query statement and obtains single-column statistical information and multi-column correlation statistical information of relevant columns from the extended statistical metadata, including: The database parser receives Structured Query Language (SQL) statements. In response to the SQL statement, the SQL statement is parsed into an abstract syntax tree, which includes multiple columns of constraint information; The database optimizer reads single-column statistical information and multi-column correlation statistical information from the extended statistical metadata based on multi-column constraint information.

5. The database query optimization method based on metric learning and relevance correction according to claim 3, characterized in that, The process for correcting the final multi-column selection rate includes: The correlation metric between columns is determined based on the multi-column correlation and the strength range; the strength range is determined based on a preset threshold parameter and its reciprocal. If the judgment result is that there is a correlation, then the correlation correction coefficient is calculated based on the single-column selection rate of the first column data and the single-column selection rate of the second column data. The initial selection rate is adjusted using the correlation correction coefficient to obtain the final multi-column selection rate after correction.

6. The database query optimization method based on metric learning and relevance correction according to claim 5, characterized in that, The intensity range is ,like If the columns are independent, then the columns are determined to be independent; if If the columns are positively correlated, then the columns are determined to be positively correlated; if If the columns are negatively correlated, then the columns are determined to be negatively correlated; where, This represents a correlation metric. This indicates the preset threshold parameter.

7. The database query optimization method based on metric learning and relevance correction according to claim 1, characterized in that, The query cost estimation based on the final multi-column selectivity includes: The number of rows is determined by estimating the final multi-column selection rate. The CPU cost and I / O cost of different execution paths are calculated based on the number of rows. The total cost is obtained based on the CPU cost and I / O cost. The total cost corresponding to different execution paths is compared to select the optimal execution path.

8. The database query optimization method based on metric learning and relevance correction according to claim 1, characterized in that, Also includes: The database executor receives and executes the final execution plan to complete data reading, sorting, and aggregation, obtains the execution result, and returns the execution result to the client.

9. A database query optimization system based on metric learning and relevance correction, characterized in that, include: The model training module is used to construct a training set based on data in the database table, train a metric learning model based on the training set to learn the correlation information between columns, and store the correlation information as extended statistical metadata. The statement response module is used to respond to and parse the query statement, and obtain single-column statistical information and multi-column correlation statistical information of the relevant columns from the extended statistical metadata. The selection rate correction module is used to calculate and determine the initial selection rate based on the column independence assumption, and correct the initial selection rate based on the multi-column correlation to obtain the corrected final multi-column selection rate. The cost estimation module is used to estimate the query cost based on the final multi-column selection rate, and select the execution path according to the estimation result to generate the final execution plan.

10. An electronic device, characterized in that, include: At least one memory for storing computer programs; At least one processor is configured to execute a program stored in the memory, wherein when the program stored in the memory is executed, the processor is configured to perform the method as described in any one of claims 1-8.

Images