Database query control method and system based on cardinality estimation

Abstract

The invention provides a database query control method and system based on cardinal number estimation, and the method comprises the steps: carrying out the data calibration of a database based on a change log of the database, collecting the description information of all calibration data, generating corresponding statistical information, carrying out the index collection and statistics of the database, and comprehensively mastering the data distribution condition of the database; generating a plurality of query execution plans based on the query statement and the statistical information of the user side, and selecting a part of the query execution plans as alternative query execution plans based on the execution cost of all the query execution plans; predicting the query result set scale of the alternative query execution plans so as to select one of the alternative query execution plans to perform data query, and predicting the computing power overhead, the result scale and the credibility possibly formed by all the query execution plans on the database from the software level; and the query efficiency and performance of the database can be improved without hardware improvement.

Description

Technical Field

[0001] This invention relates to the field of data querying, and more particularly to a database query control method and system based on cardinality estimation. Background Technology

[0002] When the amount of data in a database increases significantly, its operating efficiency may decrease. To ensure normal database operation and prevent it from impacting the operating system, optimization can be achieved by increasing memory. Increasing memory allows the database to hold more data and indexes in memory, reducing disk I / O operations and thus improving query and processing speed. However, considering the relatively high cost of memory, increasing memory to optimize database performance is not cost-effective in large-scale data storage scenarios. Furthermore, memory scalability is limited; when the physical limits of server memory are reached, increasing memory will not improve query performance. Therefore, simply increasing memory or other hardware changes cannot provide effective and in-depth database optimization. Summary of the Invention

[0003] The purpose of this invention is to provide a database query control method and system based on cardinality estimation. Based on the database change log, the method calibrates the database and collects the cataloging information of all calibrated data to generate corresponding statistical information. It then performs index collection and statistics on the database to comprehensively understand the data distribution. Based on the user's query statements and statistical information, it generates multiple query execution plans and selects a subset of these plans as alternatives based on their execution costs, ensuring that the alternative plans do not consume excessive computing power. The method predicts the size of the query result set of the alternative plans and selects one for data querying. From a software perspective, it predicts the potential computing power overhead, result size, and reliability of all query execution plans, providing multi-dimensional references for determining the final query execution plan. This improves database query efficiency and performance without requiring hardware upgrades.

[0004] This invention is achieved through the following technical solution:

[0005] Database query control methods based on cardinality estimation include:

[0006] Based on the database change log, the database is calibrated; the cataloging information of all calibrated data is collected to generate corresponding statistical information.

[0007] Based on the query statement from the user and the statistical information, multiple query execution plans are generated; based on the execution cost of all query execution plans, a subset of query execution plans are selected as alternative query execution plans.

[0008] The candidate query execution plans are predicted to obtain the size of the query result set of the candidate query execution plans; based on the size of the query result set, one of the candidate query execution plans is selected for data query.

[0009] Optionally, based on the database's change log, the database is calibrated; the cataloging information of all calibrated data is collected to generate corresponding statistical information, including:

[0010] Based on the database change log, the latest change time of each piece of data in the database is obtained; based on the latest change time and a preset data validity period, the data in the database that has changed within the preset data validity period is identified.

[0011] Based on the location of all calibration data within the database, index information and table information about all calibration data are collected, and semantic association recognition is performed on the index information and table information to generate statistical information about all calibration data at the semantic association level.

[0012] Optionally, based on the query statement from the user and the statistical information, multiple query execution plans are generated; based on the execution cost of all query execution plans, a subset of query execution plans are selected as alternative query execution plans, including:

[0013] The query statement from the user is parsed to obtain all keywords in the query statement; the keywords are matched with the semantic association information between them and all the labeled data contained in the statistical information to generate multiple query execution plans;

[0014] Estimate the disk I / O operation overhead and CPU computing overhead during the execution of each query execution plan to obtain the execution cost of each query execution plan; compare the execution cost of each query execution plan with the preset execution cost threshold, and select a portion of the query execution plans as alternative query execution plans.

[0015] Optionally, the alternative query execution plans are predicted to obtain the size of the query result set of the alternative query execution plans; based on the size of the query result set, one of the alternative query execution plans is selected for data querying, including:

[0016] Based on the distribution structure of all calibration data in the database, the execution plan of the alternative query is predicted to obtain the size of the query result set of the alternative query execution plan; wherein, the size of the query result set refers to the data volume and data structure of the query results;

[0017] Based on the size of the query result set, the reliability of the query results of each of the candidate query execution plans is determined, and one of the candidate query execution plans is selected for data query.

[0018] A database query control system based on cardinality estimation includes:

[0019] The data labeling module is used to label the database based on the database's change logs;

[0020] The statistical information generation module is used to collect the bibliographic information of all calibration data and generate corresponding statistical information.

[0021] The execution plan generation module is used to generate multiple query execution plans based on the query statements from the user and the statistical information.

[0022] The execution plan selection module is used to select a subset of query execution plans as alternative query execution plans based on the execution cost of all query execution plans;

[0023] The query result set size determination module is used to predict the alternative query execution plans and obtain the query result set size of the alternative query execution plans;

[0024] The execution plan determination module is used to select one of the alternative query execution plans for data querying based on the size of the query result set.

[0025] Optionally, the data labeling module is used to label the database based on the database's change log, including:

[0026] Based on the database change log, the latest change time of each piece of data in the database is obtained; based on the latest change time and a preset data validity period, the data in the database that has changed within the preset data validity period is identified.

[0027] The statistical information generation module is used to collect the bibliographic information of all calibration data, and thereby generate corresponding statistical information, including:

[0028] Based on the location of all calibration data within the database, index information and table information about all calibration data are collected, and semantic association recognition is performed on the index information and table information to generate statistical information about all calibration data at the semantic association level.

[0029] Optionally, the execution plan generation module is used to generate multiple query execution plans based on the query statement from the user and the statistical information, including:

[0030] The query statement from the user is parsed to obtain all keywords in the query statement; the keywords are matched with the semantic association information between them and all the labeled data contained in the statistical information to generate multiple query execution plans;

[0031] The execution plan selection module is used to select a subset of query execution plans as candidate query execution plans based on the execution cost of all query execution plans, including:

[0032] Estimate the disk I / O operation overhead and CPU computing overhead during the execution of each query execution plan to obtain the execution cost of each query execution plan; compare the execution cost of each query execution plan with the preset execution cost threshold, and select a portion of the query execution plans as alternative query execution plans.

[0033] Optionally, the query result set size determination module is used to predict the size of the query result set of the alternative query execution plans, including:

[0034] Based on the distribution structure of all calibration data in the database, the execution plan of the alternative query is predicted to obtain the size of the query result set of the alternative query execution plan; wherein, the size of the query result set refers to the data volume and data structure of the query results;

[0035] The execution plan determination module is used to select one of the candidate query execution plans for data querying based on the size of the query result set, including:

[0036] Based on the size of the query result set, the reliability of the query results of each of the candidate query execution plans is determined, and one of the candidate query execution plans is selected for data query.

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

[0038] The database query control method and system based on cardinality estimation provided in this application, based on the database change log, performs data labeling on the database and collects the cataloging information of all labeled data to generate corresponding statistical information. It then performs index collection and statistics on the database to comprehensively understand the data distribution. Based on the user's query statements and statistical information, it generates multiple query execution plans and selects a subset of these plans as alternatives based on the execution cost of all plans, ensuring that the alternative plans do not consume excessive computing power. It predicts the size of the query result set of the alternative plans and selects one for data querying. From a software perspective, it predicts the potential computing power overhead, result size, and reliability of all query execution plans, providing multi-dimensional references for determining the final query execution plan. This improves database query efficiency and performance without requiring hardware upgrades. Attached Figure Description

[0039] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. Wherein:

[0040] Figure 1 This is a flowchart illustrating the database query control method based on cardinality estimation provided by the present invention.

[0041] Figure 2 This is a schematic diagram of the database query control system based on cardinality estimation provided by the present invention. Detailed Implementation

[0042] To make the above-mentioned objectives, features, and advantages of this application more apparent and understandable, the specific embodiments of this application will be described in detail below with reference to the accompanying drawings. It should be understood that the specific embodiments described herein are for illustrative purposes only and are not intended to limit the scope of this application. Furthermore, it should be noted that, for ease of description, only the parts relevant to this application are shown in the accompanying drawings, not the entire structure. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without inventive effort are within the scope of protection of this application.

[0043] The terms “comprising” and “having”, and any variations thereof, used in this application are intended to cover non-exclusive inclusion. For example, a process, method, system, product, or apparatus that includes a series of steps or units is not limited to the steps or units listed, but may optionally include steps or units not listed, or may optionally include other steps or units inherent to such process, method, product, or apparatus.

[0044] In this document, the term "embodiment" means that a particular feature, structure, or characteristic described in connection with an embodiment may be included in at least one embodiment of this application. The appearance of this phrase in various places throughout the specification does not necessarily refer to the same embodiment, nor is it a separate or alternative embodiment mutually exclusive with other embodiments. It will be explicitly and implicitly understood by those skilled in the art that the embodiments described herein can be combined with other embodiments.

[0045] Please see Figure 1 As shown, an embodiment of this application provides a database query control method based on cardinality estimation. This database query control method based on cardinality estimation includes:

[0046] Based on the database change log, data is calibrated in the database; the cataloging information of all calibrated data is collected to generate corresponding statistical information.

[0047] Based on the query statements and statistical information from the user, multiple query execution plans are generated; based on the execution cost of all query execution plans, a subset of query execution plans are selected as alternative query execution plans.

[0048] Predict the execution plans of the alternative queries to obtain the size of the query result set of the alternative query execution plans; based on the size of the query result set, select one of the alternative query execution plans to perform data query.

[0049] The beneficial effects of the above embodiments are as follows: This database query control method based on cardinality estimation uses the database change log to label the database and collect the cataloging information of all labeled data to generate corresponding statistical information. It then performs index collection and statistics on the database to comprehensively understand the data distribution. Based on the user's query statements and statistical information, it generates multiple query execution plans and selects a subset of these plans as alternatives based on the execution cost of all plans, ensuring that the alternative plans do not consume excessive computing power. It predicts the size of the query result set of the alternative plans and selects one for data querying. From a software perspective, it predicts the computing power overhead, result size, and reliability that all query execution plans may impose on the database, providing multi-dimensional references for determining the final query execution plan. This improves database query efficiency and performance without requiring hardware upgrades.

[0050] In another embodiment, the database is calibrated based on its change log; the cataloging information of all calibrated data is collected to generate corresponding statistical information, including:

[0051] Based on the database change log, the latest change time of each piece of data in the database is obtained; based on the latest change time and the preset data validity period, the data that has changed within the preset data validity period in the database is identified.

[0052] Based on the location of all calibration data within the database, index and table information about all calibration data is collected. Semantic association recognition is performed on the index and table information to generate statistical information about all calibration data at the semantic association level.

[0053] The beneficial effects of the above embodiments are that the database stores a large amount of data, and the creation and modification times of this data are not the same. Some data was created and stored earlier, while others were created and stored later, resulting in different availability of different data in the database. Generally speaking, data created and modified earlier has lower availability, while data created and modified later has higher availability. In actual data queries on the database, directly traversing and querying all data in the database will inevitably lead to excessively long query times and wasted query computing resources. Based on the differences in availability between data created and modified at different times in the database, the latest change time of each piece of data is obtained based on the database's change log; the latest change time can be, but is not limited to, the time when the data was most recently created or modified. The latest change time of each piece of data is compared with a preset data validity time interval to determine whether the latest change time is within the preset data validity time interval, thereby identifying data that has changed within the preset data validity time interval in the database. This allows for the filtering and identification of data with a certain degree of usefulness in the database, effectively narrowing the scope of database query operations and reducing the workload of subsequent data queries. Furthermore, each piece of data in the database has corresponding index and table information. Index information refers to the sorting characteristics of the data within the database, while table information refers to the storage structure characteristics of the data within the database. Each piece of data in the database has corresponding index and table information. At the same time, there may be textual semantic relationships between different pieces of data in the database. Based on the position of all labeled data in the database, index and table information about all labeled data is collected. Semantic association identification is performed on the index and table information, which can associate the textual semantics of the data, index information, and table information to generate statistical information about the semantic association of all labeled data. This statistical information can characterize the association of labeled data with the same or similar textual semantics in the database in terms of index and table information, which is convenient for effectively and comprehensively covering the labeled data with corresponding semantic content when formulating query execution plans.

[0054] In another embodiment, multiple query execution plans are generated based on query statements and statistical information from the user; based on the execution cost of all query execution plans, a subset of query execution plans are selected as candidate query execution plans, including:

[0055] The query statement from the user is parsed to obtain all keywords in the query statement; the keywords are matched with the semantic association information between all the labeled data contained in the statistical information to generate multiple query execution plans;

[0056] Estimate the disk I / O operation overhead and CPU computing overhead during the execution of each query execution plan to obtain the execution cost of each query execution plan; compare the execution cost of each query execution plan with the preset execution cost threshold, and select a portion of the query execution plans as alternative query execution plans.

[0057] The beneficial effects of the above embodiments are that when a user initiates a query request to the database, the query statement initiated by the user is parsed and processed to obtain all keywords within the query statement. These keywords can be, but are not limited to, words within the query statement that have substantial semantic meaning related to the text. The number of keywords in the query statement and the semantic correlation between them directly affect the number of results that can be obtained from the database based on the query statement. By organizing the keywords in the query statement in different ways to form different query execution plans, different query execution plans can obtain different query results in the database. Therefore, the semantic correlation information between keywords and all labeled data contained in the statistical information is matched to generate multiple query execution plans. For example, the semantic similarity between all keywords in the query statement and all labeled data contained in the statistical information can be compared to obtain the semantic similarity between the keywords and the statistical information. Based on the semantic similarity, multiple query execution plans are generated, where each query execution plan can include a data query path to the database. Furthermore, based on the data query paths included in each query execution plan, the disk I / O operation overhead and CPU computing overhead during the execution of each query execution plan are estimated to obtain the execution cost of each query execution plan. The higher the disk I / O operation overhead and / or the CPU computing overhead, the higher the execution cost of the corresponding query execution plan. Then, the execution cost of each query execution plan is compared with a preset execution cost threshold. If the execution cost of a certain query execution plan is less than or equal to the preset execution cost threshold, the above query execution plan is used as a candidate query execution plan. This ensures that all selected candidate query execution plans have low execution costs and avoids the above candidate query execution plans from consuming too much system resources when performing data queries on the database, thus affecting the data query efficiency.

[0058] In another embodiment, the candidate query execution plans are predicted to obtain the size of the query result set of the candidate query execution plans; based on the size of the query result set, one of the candidate query execution plans is selected for data querying, including:

[0059] Based on the distribution structure of all calibrated data in the database, the execution plans of the alternative queries are predicted to obtain the size of the query result set of the alternative query execution plans; where the size of the query result set refers to the amount and structure of the query results.

[0060] Based on the size of the query result set, determine the reliability of the query results for each of the alternative query execution plans, and then select one of the alternative query execution plans to perform the data query.

[0061] The beneficial effects of the above embodiments are that all the data marked in the database are distributed in different locations within the database, thus forming a corresponding distribution structure. Each query execution plan contains a corresponding data query path and data query read frequency. When all the marked data in the database is processed according to different alternative query execution plans, corresponding query result sets are obtained. Furthermore, the size of the query result sets corresponding to different alternative query execution plans is not the same; that is, the data volume and data structure of the query results corresponding to different alternative query execution plans are not the same. Based on the size of the query result sets corresponding to all alternative query execution plans, the reliability of the query results for each alternative query execution plan is determined. The reliability of the query results can be, but is not limited to, the proportion of non-noise data results within the query results. The larger the proportion of non-noise data results, the greater the reliability of the query results. The alternative query execution plan with the highest reliability is then selected as the final query execution plan, thereby querying the database, reducing the computational overhead of database queries and improving the database's query efficiency and performance.

[0062] In one specific embodiment, the step of predicting the candidate query execution plan based on the distribution structure of all calibration data in the database to obtain the query result set size of the candidate query execution plan specifically includes the following step S1:

[0063] Step S1: Calculate the result set size of each alternative query execution plan using the cardinality estimation model. Specifically, calculate the weighted composite selection rate based on the selection rate and semantic association weight of each query predicate, multiply the weighted composite selection rate by the cardinality of the main driving table to obtain the basic row count estimate, calculate the cardinality adjustment term based on the join, aggregation, or subquery operations involved in the query, and add the basic row count estimate to the cardinality adjustment term to obtain the estimated cardinality.

[0064] As an example, the cardinality estimation model is expressed by the following formula:

[0065] Calculate the execution plan for each alternative query according to formula (1). The estimated base :

[0066] (1)

[0067] in, This indicates the estimated base number of the current alternative query execution plan, i.e., the estimated number of rows in the result set;

[0068] Indicates the first Selectivity of each query predicate The calculation can be performed based on the statistical information generated by the statistical information generation module. Specifically, it can be implemented as follows: (1) For equivalence predicates, the calculation is performed using the number of distinct values ​​(NDV) of the column, for example, it can be calculated as follows: (2) For range predicates (such as column BETWEEN a AND b or column > value), the selectivity is calculated based on the column histogram in the statistics. The column histogram divides the data range of the column into several consecutive buckets and records the proportion of data rows contained in each bucket to the total number of rows, i.e., the bucket percentage (or the proportion of data within a bucket). During calculation, first, the buckets covered by the range value range are determined. Then, for buckets that fall completely within the range value range, their bucket percentages are directly accumulated. For buckets that are partially covered by the range value range, their contribution percentage is estimated by linear interpolation based on the overlap ratio between the bucket's value range width and the range value range within that bucket. Finally, the contribution percentages of all relevant buckets are accumulated to obtain the estimated selectivity of the range predicate.

[0069] For other types of predicates, their selectivity can be estimated based on the actual situation.

[0070] in, Indicates the first The semantic relevance weight of each query predicate is a personalized parameter. The method for obtaining this value is as follows: it is obtained by analyzing historical query logs. The system records the actual filtering effect and importance of each predicate in historical queries, and dynamically updates it using a feedback-based weight learning algorithm, with the initial value set to 1.

[0071] This indicates the cardinality of the primary driving table involved in the query, and its value can be obtained directly from the row count statistics of that table in the database system catalog table.

[0072] The base adjustment term can be obtained through the following methods:

[0073] (1) For the join operation, the radix adjustment term is equal to ,in For join selectivity, this is used to estimate the percentage increase in the number of rows resulting from join operations. The join selectivity... For example, the estimation method can be: ,in and These represent the number of distinct values ​​in the two joined columns; where, Indicates rounding up;

[0074] (2) For aggregation operations, the cardinality adjustment term is equal to ,in Let be the grouping reduction factor. Let the grouping column corresponding to the aggregation operation be a set. The selection rates for each column are as follows: Then the group reduction factor The calculation formula is as follows (2):

[0075] (2)

[0076] The above selection rate Based on the number of different values ​​in the corresponding column Approximately, the calculation formula is as follows:

[0077] (3) For compound operations, the base adjustment term is the algebraic sum of the adjustment terms of each operation.

[0078] in, This indicates the number of predicates in the query statement, obtained by parsing the query statement.

[0079] Formula (1) is the core model for cardinality estimation. Its principle is to predict the amount of data in the query results by quantitatively analyzing the filtering effect of query conditions on the data and the structural impact of data operations. Its function is to transform the abstract size of the query result set into a computable mathematical expression, providing a precise quantitative basis for subsequent query optimization decisions.

[0080] Accordingly, based on the size of the query result set, the reliability of the query results of each of the candidate query execution plans is determined, and one of the candidate query execution plans is selected for data querying. Specifically, this can be implemented as follows:

[0081] For each candidate query execution plan: its estimated cardinality is normalized to obtain the relative size of the plan's result among all candidate plans, which serves as the size metric; using the Monte Carlo sampling method, based on the distribution structure of the calibrated data, the same query execution plan is simulated multiple times to obtain a set of cardinality estimation samples, and the coefficient of variation of this sample is calculated; one is divided by (one plus the coefficient of variation) to obtain the stability score; the size metric and the stability score are weighted and summed according to preset first and second weighting coefficients to obtain the query result confidence of the candidate query execution plan;

[0082] For each alternative query execution plan: normalize its estimated execution cost to obtain the relative size of the execution cost of the plan among all alternative plans, i.e., the cost normalization value; by subtracting the cost normalization value from one, the cost efficiency score is obtained.

[0083] For each alternative query execution plan: the confidence level of its query results and the cost efficiency score are weighted and summed according to a preset trade-off coefficient to obtain the corresponding comprehensive evaluation score;

[0084] Compare the overall evaluation scores of all alternative query execution plans, and select the query execution plan with the highest overall evaluation score as the final execution plan.

[0085] Accordingly, the specific embodiments described above are represented by the following algorithmic models S2-S5:

[0086] Step S2: Calculate the query result confidence of each alternative query execution plan according to the following formula (2). ;

[0087] (3)

[0088] Formula (3) is based on the two dimensions of result sufficiency and estimation stability to evaluate the reliability of query results. Its function is to transform the quality of cardinality estimation into a standardized confidence score, providing input for the quality dimension of comprehensive evaluation.

[0089] in, This indicates the confidence level of the query results from the current alternative query execution plan. A higher value indicates a higher level of reliability for the query results. This indicates the estimated base for the current alternative query execution plan. The normalized value is calculated using the following formula (4):

[0090] (4)

[0091] in , which is the estimated base set for all alternative plans;

[0092] The coefficient of variation, representing the estimated cardinality, is obtained by performing multiple (e.g., 1000) simulations on the same query plan using a Monte Carlo sampling method based on data distribution. This yields a set of estimated cardinality samples, which are then used to calculate the sample coefficient of variation. ,in The sample mean. This represents the sample standard deviation.

[0093] Let be the weighting coefficient, satisfying and The value is obtained by either pre-setting it by the system administrator based on the application scenario, or dynamically adjusting it based on historical query feedback through machine learning algorithms.

[0094] Step S3: Calculate the cost efficiency score of each alternative query execution plan according to the following formula (5). :

[0095] (5)

[0096] in, This indicates the cost-efficiency score of the current alternative query execution plan. ,number

[0097] A higher value indicates higher execution efficiency. Indicates the estimated execution cost The normalized value is calculated according to the following formula (6):

[0098] (6)

[0099] in, , which is the set of estimated execution costs for all alternative query execution plans.

[0100] This represents the estimated execution cost of the current alternative query execution plan. Its value is obtained by calculating the computational cost based on disk I / O operations and CPU computational costs. For example... The first value is obtained by multiplying the disk I / O operation computing power cost of the current candidate query execution plan by the corresponding weight coefficient; the second value is obtained by multiplying the CPU computing power cost of the current candidate query execution plan by the corresponding weight coefficient; the value obtained by adding the first value to the second value is the estimated execution cost of the current candidate query execution plan.

[0101] The principle behind this cost-efficiency scoring formula is to transform execution costs into efficiency scores through cost normalization and linear transformation. Its purpose is to provide input for the efficiency dimension of comprehensive evaluation, ensuring that plans with lower costs receive higher efficiency scores.

[0102] Step S4: Calculate the comprehensive evaluation score for each alternative query execution plan using the comprehensive evaluation model. The comprehensive evaluation model is expressed as the following formula (7).

[0103] (7)

[0104] The principle behind this formula is to balance and optimize the quality of query results (confidence) and execution efficiency (cost efficiency) through weighted summation. Its function is to generate a single comprehensive score, enabling the system to make the optimal choice among multi-dimensional optimization objectives.

[0105] in, This represents the overall evaluation score of the current alternative query execution plans. A higher value indicates that the plan performs better in terms of both result quality and execution efficiency. These are the weighting coefficients. This setting is used to adjust the relative importance of result confidence and cost efficiency in the overall score. Its value can be configured according to the query type and application requirements; interactive queries can be set to a higher value. The value emphasizes result quality; batch queries can be set to a lower value. The value focuses on execution efficiency.

[0106] The system maintains a feedback queue to record the confidence level, actual execution time, and user satisfaction of the final selected plan for each query. After every N queries, stochastic gradient descent is used to adjust α, β, and γ to minimize the loss function, which consists of the deviation between actual execution time and estimated cost, as well as the number of user dissatisfactions.

[0107] Step S5: Sort all alternative query execution plans by comprehensive evaluation scores from largest to smallest, obtain the top-ranked target alternative query execution plan, and use this target alternative query execution plan as the final query execution plan for data query.

[0108] The beneficial effects of the above embodiments are as follows:

[0109] Through the aforementioned cardinality estimation model and comprehensive evaluation system, this embodiment can optimize database query performance at the software level. Specific beneficial effects include:

[0110] By introducing semantic association weights and base adjustment item This significantly improves the accuracy of predicting the size of complex query results; and by comprehensively evaluating the model's result confidence... and execution efficiency It scientifically balances various factors, avoiding the limitations of traditional optimizers' single-dimensional decision-making and achieving multi-dimensional intelligent optimization; through personalized parameters... Its configurability enables the system to adapt to different application scenarios and performance requirements, supporting personalized adaptation; before query execution, cost and benefit analysis is used to select the optimal execution plan, avoiding high-cost and low-quality query execution and reducing computing power consumption; without increasing hardware resources, algorithm optimization significantly improves the database's query response time and throughput, improving query performance, which is particularly suitable for large-scale data environments; the entire optimization process is based on a transparent mathematical model, and each decision has a clear quantitative basis, enhancing system interpretability and facilitating system tuning and problem diagnosis.

[0111] Please see Figure 2 As shown, an embodiment of this application provides a database query control system based on cardinality estimation. This database query control system based on cardinality estimation includes:

[0112] The data labeling module is used to label data in the database based on the database's change logs.

[0113] The statistical information generation module is used to collect the bibliographic information of all calibration data and generate corresponding statistical information.

[0114] The execution plan generation module is used to generate multiple query execution plans based on query statements and statistical information from the user.

[0115] The execution plan selection module is used to select a subset of query execution plans as alternative query execution plans based on the execution cost of all query execution plans;

[0116] The query result set size determination module is used to predict the execution plans of alternative queries and obtain the size of the query result set of the alternative query execution plans;

[0117] The execution plan determination module is used to select one of the alternative query execution plans for data querying based on the size of the query result set.

[0118] The beneficial effects of the above embodiments are as follows: This database query control system based on cardinality estimation calibrates the database based on its change logs and collects the cataloging information of all calibrated data to generate corresponding statistical information. It then performs index collection and statistics on the database to comprehensively understand its data distribution. Based on user-side query statements and statistical information, it generates multiple query execution plans and selects a subset of these plans as alternatives based on their execution costs, ensuring that these alternatives do not consume excessive computing power. It predicts the size of the query result set of the alternative plans and selects one for data querying. From a software perspective, it predicts the potential computing power overhead, result size, and reliability of all query execution plans, providing multi-dimensional references for determining the final query execution plan. This improves database query efficiency and performance without requiring hardware upgrades.

[0119] In another embodiment, the data labeling module is used to label the database based on the database's change log, including:

[0120] Based on the database change log, the latest change time of each piece of data in the database is obtained; based on the latest change time and the preset data validity period, the data that has changed within the preset data validity period in the database is identified.

[0121] The statistical information generation module is used to collect the bibliographic information of all calibration data and generate corresponding statistical information, including:

[0122] Based on the location of all calibration data within the database, index and table information about all calibration data is collected. Semantic association recognition is performed on the index and table information to generate statistical information about all calibration data at the semantic association level.

[0123] The beneficial effects of the above embodiments are that the database stores a large amount of data, and the creation and modification times of this data are not the same. Some data was created and stored earlier, while others were created and stored later, resulting in different availability of different data in the database. Generally speaking, data created and modified earlier has lower availability, while data created and modified later has higher availability. In actual data queries on the database, directly traversing and querying all data in the database will inevitably lead to excessively long query times and wasted query computing resources. Based on the differences in availability between data created and modified at different times in the database, the latest change time of each piece of data is obtained based on the database's change log; the latest change time can be, but is not limited to, the time when the data was most recently created or modified. The latest change time of each piece of data is compared with a preset data validity time interval to determine whether the latest change time is within the preset data validity time interval, thereby identifying data that has changed within the preset data validity time interval in the database. This allows for the filtering and identification of data with a certain degree of usefulness in the database, effectively narrowing the scope of database query operations and reducing the workload of subsequent data queries. Furthermore, each piece of data in the database has corresponding index and table information. Index information refers to the sorting characteristics of the data within the database, while table information refers to the storage structure characteristics of the data within the database. Each piece of data in the database has corresponding index and table information. At the same time, there may be textual semantic relationships between different pieces of data in the database. Based on the position of all labeled data in the database, index and table information about all labeled data is collected. Semantic association identification is performed on the index and table information, which can associate the textual semantics of the data, index information, and table information to generate statistical information about the semantic association of all labeled data. This statistical information can characterize the association of labeled data with the same or similar textual semantics in the database in terms of index and table information, which is convenient for effectively and comprehensively covering the labeled data with corresponding semantic content when formulating query execution plans.

[0124] In another embodiment, the execution plan generation module is used to generate multiple query execution plans based on query statements and statistical information from the user terminal, including:

[0125] The query statement from the user is parsed to obtain all keywords in the query statement; the keywords are matched with the semantic association information between all the labeled data contained in the statistical information to generate multiple query execution plans;

[0126] The execution plan selection module is used to select a subset of query execution plans as alternatives based on the execution cost of all query execution plans. These include:

[0127] Estimate the disk I / O operation overhead and CPU computing overhead during the execution of each query execution plan to obtain the execution cost of each query execution plan; compare the execution cost of each query execution plan with the preset execution cost threshold, and select a portion of the query execution plans as alternative query execution plans.

[0128] The beneficial effects of the above embodiments are that when a user initiates a query request to the database, the query statement initiated by the user is parsed and processed to obtain all keywords within the query statement. These keywords can be, but are not limited to, words within the query statement that have substantial semantic meaning related to the text. The number of keywords in the query statement and the semantic correlation between them directly affect the number of results that can be obtained from the database based on the query statement. By organizing the keywords in the query statement in different ways to form different query execution plans, different query execution plans can obtain different query results in the database. Therefore, the semantic correlation information between keywords and all labeled data contained in the statistical information is matched to generate multiple query execution plans. For example, the semantic similarity between all keywords in the query statement and all labeled data contained in the statistical information can be compared to obtain the semantic similarity between the keywords and the statistical information. Based on the semantic similarity, multiple query execution plans are generated, where each query execution plan can include a data query path to the database. Furthermore, based on the data query paths included in each query execution plan, the disk I / O operation overhead and CPU computing overhead during the execution of each query execution plan are estimated to obtain the execution cost of each query execution plan. The higher the disk I / O operation overhead and / or the CPU computing overhead, the higher the execution cost of the corresponding query execution plan. Then, the execution cost of each query execution plan is compared with a preset execution cost threshold. If the execution cost of a certain query execution plan is less than or equal to the preset execution cost threshold, the above query execution plan is used as a candidate query execution plan. This ensures that all selected candidate query execution plans have low execution costs and avoids the above candidate query execution plans from consuming too much system resources when performing data queries on the database, thus affecting the data query efficiency.

[0129] In another embodiment, the query result set size determination module is used to predict the size of the query result set of the candidate query execution plans, including:

[0130] Based on the distribution structure of all calibrated data in the database, the execution plans of the alternative queries are predicted to obtain the size of the query result set of the alternative query execution plans; where the size of the query result set refers to the amount and structure of the query results.

[0131] The execution plan determination module is used to select one of the alternative query execution plans for data querying based on the size of the query result set, including:

[0132] Based on the size of the query result set, determine the reliability of the query results for each of the alternative query execution plans, and then select one of the alternative query execution plans to perform the data query.

[0133] The beneficial effects of the above embodiments are that all the data marked in the database are distributed in different locations within the database, thus forming a corresponding distribution structure. Each query execution plan contains a corresponding data query path and data query read frequency. When all the marked data in the database is processed according to different alternative query execution plans, corresponding query result sets are obtained. Furthermore, the size of the query result sets corresponding to different alternative query execution plans is not the same; that is, the data volume and data structure of the query results corresponding to different alternative query execution plans are not the same. Based on the size of the query result sets corresponding to all alternative query execution plans, the reliability of the query results for each alternative query execution plan is determined. The reliability of the query results can be, but is not limited to, the proportion of non-noise data results within the query results. The larger the proportion of non-noise data results, the greater the reliability of the query results. The alternative query execution plan with the highest reliability is then selected as the final query execution plan, thereby querying the database, reducing the computational overhead of database queries and improving the database's query efficiency and performance.

[0134] In summary, this database query control method and system based on cardinality estimation calibrates the database using its change logs and collects the cataloging information of all calibrated data to generate corresponding statistical information. It then performs index collection and statistics on the database to comprehensively understand its data distribution. Based on user-side query statements and statistical information, it generates multiple query execution plans and selects a subset as alternative plans based on the execution costs of all plans, ensuring that these alternative plans do not consume excessive computing power. It predicts the size of the query result set of the alternative plans and selects one for data querying. From a software perspective, it predicts the potential computing power overhead, result size, and reliability of all query execution plans, providing multi-dimensional references for determining the final query execution plan. This improves database query efficiency and performance without requiring hardware upgrades.

[0135] The above is only one specific embodiment of the present invention, and any improvements made based on the concept of the present invention shall be considered within the scope of protection of the present invention.

Claims

1. A database query control method based on cardinality estimation, characterized in that, include: Based on the database change log, the database is tagged with data. Collect the bibliographic information of all calibration data to generate corresponding statistical information; Based on the query statement from the user and the statistical information, multiple query execution plans are generated; Based on the execution cost of all query execution plans, a subset of query execution plans are selected as alternative query execution plans; The size of the query result set of the alternative query execution plan is obtained by predicting the alternative query execution plan. Based on the size of the query result set, select one of the alternative query execution plans to perform data query.

2. The database query control method based on cardinality estimation as described in claim 1, characterized in that: Based on the database change log, the database is tagged with data. Collect the bibliographic information of all calibration data to generate corresponding statistical information, including: Based on the database change log, the latest change time of each piece of data in the database is obtained; based on the latest change time and a preset data validity period, the data in the database that has changed within the preset data validity period is identified. Based on the location of all calibration data within the database, index information and table information about all calibration data are collected, and semantic association recognition is performed on the index information and table information to generate statistical information about all calibration data at the semantic association level.

3. The database query control method based on cardinality estimation as described in claim 1, characterized in that: Based on the query statement from the user and the statistical information, multiple query execution plans are generated; Based on the execution cost of all query execution plans, a subset of query execution plans are selected as alternative query execution plans, including: The query statement from the user is parsed to obtain all keywords in the query statement; the keywords are matched with the semantic association information between them and all the labeled data contained in the statistical information to generate multiple query execution plans; Estimate the disk I / O operation overhead and CPU computing overhead during the execution of each query execution plan to obtain the execution cost of each query execution plan; compare the execution cost of each query execution plan with the preset execution cost threshold, and select a portion of the query execution plans as alternative query execution plans.

4. The database query control method based on cardinality estimation as described in claim 1, characterized in that: The size of the query result set of the alternative query execution plan is obtained by predicting the alternative query execution plan. Based on the size of the query result set, one of the alternative query execution plans is selected for data querying, including: Based on the distribution structure of all calibration data in the database, the execution plan of the alternative query is predicted to obtain the size of the query result set of the alternative query execution plan; Based on the size of the query result set, the reliability of the query results of each of the candidate query execution plans is determined, and one of the candidate query execution plans is selected for data query.

5. A database query control system based on cardinality estimation, characterized in that, include: The data labeling module is used to label the database based on the database's change logs; The statistical information generation module is used to collect the bibliographic information of all calibration data and generate corresponding statistical information. The execution plan generation module is used to generate multiple query execution plans based on the query statements from the user and the statistical information. The execution plan selection module is used to select a subset of query execution plans as alternative query execution plans based on the execution cost of all query execution plans; The query result set size determination module is used to predict the alternative query execution plans and obtain the query result set size of the alternative query execution plans; The execution plan determination module is used to select one of the alternative query execution plans for data querying based on the size of the query result set.

6. The database query control system based on cardinality estimation as described in claim 5, characterized in that: The data labeling module is used to label the database based on the database's change log, including: Based on the database change log, the latest change time of each piece of data in the database is obtained; based on the latest change time and a preset data validity period, the data in the database that has changed within the preset data validity period is identified. The statistical information generation module is used to collect the bibliographic information of all calibration data, and thereby generate corresponding statistical information, including: Based on the location of all calibration data within the database, index information and table information about all calibration data are collected, and semantic association recognition is performed on the index information and table information to generate statistical information about all calibration data at the semantic association level.

7. The database query control system based on cardinality estimation as described in claim 5, characterized in that: The execution plan generation module is used to generate multiple query execution plans based on the query statement from the user and the statistical information, including: The query statement from the user is parsed to obtain all keywords in the query statement; the keywords are matched with the semantic association information between them and all the labeled data contained in the statistical information to generate multiple query execution plans; The execution plan selection module is used to select a subset of query execution plans as candidate query execution plans based on the execution cost of all query execution plans, including: Estimate the disk I / O operation overhead and CPU computing overhead during the execution of each query execution plan to obtain the execution cost of each query execution plan; compare the execution cost of each query execution plan with the preset execution cost threshold, and select a portion of the query execution plans as alternative query execution plans.

8. The database query control system based on cardinality estimation as described in claim 5, characterized in that: The query result set size determination module is used to predict the size of the query result set of the candidate query execution plans, including: Based on the distribution structure of all calibration data in the database, the execution plan of the alternative query is predicted to obtain the size of the query result set of the alternative query execution plan; The execution plan determination module is used to select one of the candidate query execution plans for data querying based on the size of the query result set, including: Based on the size of the query result set, the reliability of the query results of each of the candidate query execution plans is determined, and one of the candidate query execution plans is selected for data query.

Images