JP7358685B1 - Intelligent index tuning methods and systems for relational databases - Google Patents

Abstract

The present invention provides an intelligent index tuning method and system that dynamically adjusts indexes of a database instance to effectively optimize the indexes and improve query performance. [Solution] The method analyzes each query sentence in a query log to generate a query sentence parsing tree, obtains a set of query sentence parsing trees, and generates a candidate index set based on the set of query sentence parsing trees. , determine the index optimization value of each candidate index in the set of candidate indexes, and determine the index optimization value of each candidate index in the set of candidate indexes, and the sum of the index sizes of each candidate index in at least one candidate index from among the set of candidate indexes is less than or equal to a preset threshold; , and at least one candidate index that satisfies the preset condition that the sum of the index optimization values of each candidate index is maximum is selected as the tuning index set, and the tuning index set is stored in the target database. [Selection diagram] Figure 1

Description

TECHNICAL FIELD Embodiments of the present disclosure relate to the field of computer technology, and specifically to intelligent index tuning methods and systems for relational databases.

Intelligent index tuning is a technology for adjusting indexes in a database instance to maintain efficient query performance of the database. Currently, a common method for index tuning is to obtain index recommendations from query logs, use manually designed models to select indexes, and tune the indexes.
However, when performing index tuning as described above, the following technical problems often exist.
First, the indexes of the database instance cannot be adjusted dynamically, so the indexes cannot be adjusted effectively and the efficiency of data queries cannot be improved in real time.
Second, if the recommended index generated from the query log does not exist in the database instance, it is impossible to accurately generate the optimal index for the query optimization effect of the query based on such non-existent index. Can not.
Third, it is not possible to quickly generate an optimal index from a large number of candidate indexes, and the speed of index generation becomes slow.

Some embodiments of the present disclosure propose an intelligent index tuning method and system for relational databases to solve one or more of the technical problems mentioned in the background section above. .
In a first aspect, some embodiments of the present disclosure generate a query sentence parse tree and parse each query sentence in a query log to obtain a set of query sentence parse trees. A query sentence parsing tree is generated to obtain a set of query sentence parsing trees. A candidate index set is generated based on the above query sentence analysis tree set. Determine an index optimization value for each candidate index in the candidate index set described above. From the candidate index set, the sum of index sizes of each candidate index of the at least one candidate index is less than or equal to a preset threshold, and the sum of index optimization values of each candidate index is the largest. At least one candidate index that satisfies the condition is selected as a preset index set. Save the above tuning index set to the target database.
In a second aspect, some embodiments of the present disclosure provide a parsing unit configured to generate a query sentence parse tree and parse each query sentence in a query log to obtain a set of query sentence parse trees. a generation unit configured to parse a tree set based on the query statement and generate a candidate index set; and a generation unit configured to determine an index optimization value for each candidate index in the candidate index set. a selection unit that selects at least one candidate index that satisfies a preset condition as an adjustment index set from among the candidate index set;
a storage unit configured to store the index set in the target database.
In a third aspect, some embodiments of the present disclosure provide for one or more processors, one or more programs being executed by the one or more processors, and one or more processors according to the first aspect above. A storage device in which one or more programs for implementing any of the methods described above are stored.
In a fourth aspect, some embodiments of the present disclosure are a computer readable medium storing a computer program that, when executed by a processor, implements a method according to any of the first aspects above.

Each of the above-described embodiments of the present disclosure includes an intelligent index tuning method for relational databases of some embodiments of the present disclosure to effectively tune indexes and improve data query rates in real time. It has the beneficial effect of being able to In particular, the reason indexes cannot be adjusted effectively is that indexes for a database instance cannot be adjusted dynamically. Based on this, the intelligent index tuning method for relational databases of some embodiments of the present disclosure first parses each query statement in the query log to generate a query statement parse tree, Including obtaining a set of sentence parsing trees. Therefore, by analyzing the applied query logs, the indexes of a database instance can be adjusted dynamically. Then, a candidate index set is generated based on the query sentence analysis tree set. Therefore, it is possible to select the most effective set of indexes for query optimization. Next, an index optimization value for each candidate index in the candidate index set is determined. Therefore, each candidate index can be quantized with respect to the query optimization effect of the query, and the query optimization effect of each candidate index can be visually expressed. Next, from among the candidate index sets, at least one candidate index that satisfies preset conditions is selected as a tuning index set. The preset condition is that the sum of index sizes of each candidate index in the at least one candidate index is less than or equal to a preset threshold, and the sum of index optimization values of each candidate index is maximum. An index set that does not exceed a preset threshold and has the highest query optimization effect is obtained. Finally, store the above tuning index set in the target database. This allows the index query data after tuning to be used in the target database. Through the analysis of different query statements under different application scenes, it is possible to dynamically adjust the index of the database instance, effectively optimizing the index, and improving the query performance of the database system. can do.

1 is a flowchart of several embodiments of an intelligent index tuning method for relational databases according to the present disclosure. 1 is a block diagram of several embodiments of an intelligent index tuning system for relational databases according to the present disclosure. FIG. FIG. 3 is a structural schematic diagram of an electronic device suitable for implementing some embodiments of the present disclosure.

Hereinafter, the present disclosure will be described in detail in connection with embodiments with reference to the drawings.
FIG. 1 shows a flow 100 of some embodiments of an intelligent index tuning method for relational databases according to the present disclosure. relational·
The intelligent index tuning methodology for databases follows these steps:

In step 101, each query sentence in the query log is analyzed to generate a query sentence parsing tree, and a set of query sentence parsing trees is obtained.
In some embodiments, an entity (e.g., a computing device) performing an intelligent index tuning method for relational databases generates a query statement parse tree by parsing each query statement in a query log. A set of parse trees can be obtained. Here, the query sentence parsing tree may be a query sentence expressed as a parsing tree.
In practice, the above-mentioned execution entity can analyze each query sentence in the query log through an open source parser and a syntax analyzer, generate a query sentence parsing tree, and obtain a set of query sentence parsing trees.

In step 102, a candidate index set is generated based on the query sentence analysis tree set.
In some embodiments, the execution entity may generate a candidate index set based on the query sentence parse tree set.
In some alternative embodiments of some embodiments, the execution entity may generate a set of candidate indexes based on the set of query sentence parse trees, and may include the following steps.
Perform the following index generation steps for each query statement in the above query log.
First, a set of fields included in the query statement is determined based on a query statement analysis tree corresponding to the query statement.
Actually, the execution entity can determine each field included in the query statement parsing tree corresponding to the query statement as a field set. For example, the above query statement is “S
ELECT CNO, FNAME FROM CUST WHERE LNAME=:L
NAME AND CNO>:CNO ORDER BY FNAME", and the field set included in the query statement can be determined as {CNO, FNAME, LNAME} based on the query statement parsing tree corresponding to the query statement.
In step 2, the fields of the above field set are combined to generate at least one standard field combination that satisfies preset evaluation conditions.
In reality, the execution entity can combine the fields in the field set and set the combination of fields that satisfy preset evaluation conditions as the combination of standard fields. Here, the preset evaluation condition may be a combination of fields that satisfy at least one of the IndexSamsung evaluation criteria. Combinations of standard fields that satisfy preset evaluation conditions are (LNAME, CNO), (LNAME,
CNO, FNAME), (LNAME, FNAME, CNO).
In step 3, a combination of each field subset of the selection field set included in the selection condition statement of the query statement and the at least one standard field is determined as a candidate index.
Here, the above selection condition statement may be a where condition statement. The selection field set may be each field set included in the selection condition statement. In fact, the execution entity can determine a combination of each field subset of the selection field set included in the selection condition statement of the query statement and the at least one standard field as a candidate index. For example, the selection field set included in the selection condition statement of the above query statement is
It may be {LNAME, CNO}. The above candidate indexes are (CNO), (LN
AME), (LNAME, CNO), (LNAME, CNO, FNAME), (LNAM
E, FNAME, CNO).

In step 103, an index optimization value for each candidate index in the candidate index set is determined.
In some embodiments, the execution entity described above may determine an index optimization value for each candidate index in the candidate index set. Here, the above-mentioned index optimization value can characterize the degree of query optimization of the corresponding candidate index for the database system.
In some alternative implementations of some embodiments, for each candidate index in the candidate index set described above, the execution entity described above may perform the following steps.
In step 1, a target value prediction model trained in advance is used to determine a target value of the candidate index for each query sentence in the query log, and a target value set is obtained.
The target value prediction model is a model whose inputs are the candidate index and the feature attributes of the query sentence, and whose output is a target value. In fact, the execution entity may determine the output result obtained from the pre-trained target value prediction model as the target value of the candidate index for each query statement in the query log. The target value in the target value set is the execution time required to execute the query statement when the candidate index exists, and the execution time required to execute the query statement when the candidate index does not exist. It is the difference from the time and characterizes the query optimization effect that the candidate index can obtain for the query statement corresponding to the target value.
Alternatively, the target value prediction model can be obtained by the following model generation steps.
In the first step, a query sentence sample set and an index sample set are obtained.
In reality, the above execution entity obtains a query statement sample set and an index sample set by using the first target number of query statements and the second target number of indexes in the query log as query statement samples and index samples, respectively. I can do it. For example, the first target number may be 2,000. The second target number may be 100.
In step 2, a feature attribute set is generated based on the query sentence sample set and the index sample set.
Here, the feature attributes in the above feature attribute set include, but are not limited to, the category of the query sentence sample (for example, it may be select), the number of records in a table related to the query sentence sample, and the number of records included in the query sentence sample. the number of operators (for example, the above operator may be plus, minus, or assignment); the encoding of the field after the selection of the conditional statement in the query statement sample (for example, the above encoding is UTF-8; utf-8 is a variable-length character encoding), the number of records in the table in the database in which the index sample resides, the number of fields contained in the index sample,
These include encoding of fields included in the index sample, identification of whether the query sentence sample satisfies the prefix call principle for the index sample, and identification information of whether the query sentence sample causes an update of the index sample. The above prefix calling principle may be applied to a prefix field in which an index sample is included in a selection condition statement of a query statement sample.
In reality, the execution entity can obtain a feature attribute set by using the attribute features of the query sentence samples in the query sentence sample set and the attribute features of the index samples in the index sample set as feature attributes. The feature attributes of the above feature attribute set are attribute values that can be obtained by the query statement sample at the compilation stage. Here, the category of the query statement sample, the number of records in the table related to the query statement sample, the number of operators included in the query statement sample, the number of records in the table in the database in which the index sample exists, and the index The number of fields included in the sample is an attribute value obtained at the analysis stage. If all the fields in the tables in the database instance are arranged in a certain order, and the fields used in the query statement sample and index sample are heat-encoded in this order, the related fields after the conditional statement selection in the query statement sample above are The encoding of the field contained in the index sample and the encoding of the field contained in the index sample are obtained. During the analysis phase, you can compare whether the query statement sample satisfies the prefix call principle of the index sample, and whether the fields updated in the query statement sample include fields from the index sample, so the index sample It is possible to identify whether the query statement sample causes an update of the index sample or not, if the query statement satisfies the prefix call principle.
In the third step, each of the at least one initial target value prediction submodel is model-trained based on the feature attribute set to obtain at least one target value prediction submodel.
In reality, the execution entity combines each query statement sample in the query statement sample set and each index sample in the index sample set, and sets at least one feature attribute of the query statement sample and index sample in each combination. The model can be trained by inputting the input into two initial target value prediction submodels, and the trained model can be used as the at least one target value prediction submodel. Here, the at least one initial target value prediction sub-model is a linear prediction model, a gradient-enhanced decision tree, a DNN (Deep Neural
network, depth neural network).
In the fourth step, the weight of each target value prediction submodel in the at least one target value prediction submodel is determined.
In practice, the execution entity may determine the weight of each target value prediction sub-model in the at least one target value prediction sub-model using a combined model method.
In step 5, a target value prediction model is generated based on the determined weight and the at least one target value prediction sub-model.
In reality, the execution entity can obtain a target value prediction model by performing weighted addition based on the weight of each target value prediction submodel.
This makes it possible to give weights to different submodels, maximize the advantages of each submodel, and increase the accuracy of the output results of the obtained target value prediction model. Furthermore, the query optimization effect of candidate indexes on queries can be predicted more accurately.
The above-mentioned model generation step and its related content solve the technical problem 2 mentioned in the background art, "the possibility that the recommended index generated from the query log does not actually exist in the database instance," as one of the invention points of the embodiment of the present disclosure. ``It is not possible to accurately generate an optimal index for the query optimization effect of a query based on such an index that does not actually exist.'' The reason why the optimal index cannot be generated accurately is
This is because no index actually exists in the database instance, and the target value of the index cannot be determined based on the difference in execution time of a query statement when an index exists and when it does not exist. In other words, this is the effect of index query optimization. By solving these factors, it is possible to identify the query optimization effect of an index and generate an optimal index even if an index does not actually exist. To achieve this effect, we first built a target value prediction model and trained it using historical data. Next, the trained target value prediction model is used to determine the query optimization effect of the real or virtual index on the query statement. Furthermore, the query optimization effect of the index on the database system can be determined and an optimal index can be generated.
In the second step, the sum of each target value in the target value set is determined as the total sum of target values.
In step 3, the time value of the candidate index is determined. Here, the time value characterizes the time required to build the candidate index.
In some alternative embodiments of some embodiments, the execution entity may determine the time value of the candidate index by the following steps:
In step 1, in response to determining that the candidate index is the true index, a time value is determined to be zero. Here, the above-mentioned real index is an index that actually exists in the database instance.
In step 2, in response to determining the candidate index as a virtual index, the product of the index size of the candidate index and the time required to construct an index of a preset unit size is determined as a time value. . Here, the above virtual index is an index that is generated by the query log and does not actually exist in the database instance. The above preset unit size may be 1 MB. The above index size may be the size in bytes that the index occupies.
In step 4, the difference between the product of the sum of the first numerical value and the target value and the product of the second numerical value and the time value is determined as the index optimization value of the candidate index. Here, the first value and the second value are preset and preset in order to adjust the impact of the cost for constructing the candidate index on the practicality of the candidate index. It may be a value within a range of values. The above preset value range may be [0, 1]. The product of the first value and the sum of the target values may characterize the query optimization effect of the candidate index. The product of the second value and the time value may characterize the time cost for constructing the candidate index. The larger the ratio between the first number and the second number is characterized by the optimization effect brought about by the candidate index rather than the cost required to construct the candidate index.

In step 104, at least one candidate index that satisfies preset conditions is selected as a tuning index set from among the candidate index sets.
In some embodiments, the execution entity may select at least one candidate index that satisfies preset conditions from the candidate index set as the tuning index set. The preset condition is that the sum of index sizes of each candidate index in the at least one candidate index is less than or equal to a preset threshold, and the sum of index optimization values of each candidate index is maximum. The preset threshold may be a memory size threshold given by the user.
In some alternative embodiments of some embodiments, the execution entity selects at least one candidate index that satisfies a preset condition as a tuning index set from among the candidate index set. Steps can be performed:
First, in response to determining that the candidate index/set is not empty, the next selection step is performed based on the candidate index/set and a preset threshold.
In the first sub-step, the hash value of the candidate index set is determined as the current hash value under a preset threshold restriction.
Here, the hash value characterizes the maximum index optimization value of the candidate index set and the optimal candidate index set under a preset threshold restriction. For example, the candidate index set is {index 1, index 2, index 3, index 4
}. The preset threshold is 100MB. The hash value is used to determine the maximum index optimization value and the optimal candidate index set for the candidate index set {index 1, index 2, index 3, index 4} under the preset threshold of 100 MB. be.
The second sub-step includes determining the maximum index optimization value and the optimal candidate index set in response to the presence of the maximum index optimization value and the optimal candidate index set corresponding to the current hash value in the hash table to be determined. An index set is determined as a maximum index optimization value corresponding to the current hash value and an optimal candidate index set.
Note that the target hash table may be a hash table that stores the hash values.
a third substep, in response to determining that there is no maximum index optimization value and optimal candidate index set corresponding to the current hash value in the target hash table; The index is determined to be the lowest candidate index, the lowest candidate index is removed, the removed candidate index set is linked as the candidate index set, and the selection step is executed again.
In the second step, in response to determining that the candidate index set is empty,
0 and an empty set are determined as a first maximum index optimization value and a first optimal candidate index set corresponding to the current hash value, respectively.
In some alternative embodiments of some embodiments, the selection step described above further includes the following pushback step.
First, in response to determining that the preset threshold is greater than or equal to the index size of the final candidate index removed, the following steps are performed.
a first substep of determining the difference between the index size of the preset threshold and the deleted final candidate index as the updated preset threshold; The second selection step of determining the maximum index optimization value of 2 and the second optimal candidate index set is performed again.
In the second sub-step, the sum of the first maximum index optimization value and the index optimization value of the removed final candidate index is determined as the updated second maximum index optimization value, and the sum of the index optimization value of the removed final candidate index is determined as the updated second maximum index optimization value; The candidate index is added to the first optimal candidate index set, and the added first optimal candidate index set is determined as the updated second optimal candidate index set.
A third substep includes determining the first maximum index optimization value and the first maximum index optimization value in response to determining that the first maximum index optimization value is greater than or equal to the second maximum index optimization value.
The optimal candidate index set of is added to the target hash table, and the first maximum index optimization value and the first optimal candidate index set are determined as the maximum index optimization value and the optimal candidate index set of the current hash value.
In response to determining that the first maximum index optimization value is less than the second maximum index optimization value, the fourth substep includes determining the second maximum index optimization value and the second maximum index optimization value. The candidate index set is added to the target hash table, and a second maximum index optimization value and a second optimal candidate index set are determined as the maximum index optimization value and the optimal candidate index set of the current hash value.
Next, add the last removed candidate index to the candidate index set and follow the steps below.
In the first sub-step, the hash value of the candidate index set is determined as the current hash value under a preset threshold restriction.
For example, the candidate index set is {candidate index 1, candidate index 2},
If it is a set from which candidate index 3 has been removed, the final candidate index that has been removed is candidate index 3. The last candidate index deleted is candidate index 4
It is. The previously deleted candidate index 4 is added to the candidate index set to obtain an updated candidate index set {candidate index 1, candidate index 2, candidate index 3, candidate index 4}. Determine the hash value of the candidate index set as the current hash value under a preset threshold restriction.
In the second sub-step, the maximum index optimization value and the optimal candidate index set are determined as the first maximum index optimization value and the first optimal candidate index set corresponding to the current hash value, and the above-mentioned backing process is performed again. Execute a push step.
In step 3, the obtained optimal candidate index set is determined as the above tuning index set.
The above-mentioned selection step, the above-mentioned pushback step, and their related content are one of the invention points of the embodiments of the present disclosure, and the technical problem 3 mentioned in the background art is that it is difficult to quickly generate an optimal index from a large number of candidate indexes. "The index generation speed is slow."
solved. The reason why the index generation speed is slow is that when the database instance is large, the preset threshold is also large, and when selecting an index, it is necessary to enumerate local solutions under spatial constraints. The overhead is large and has little to do with the final optimal index. If these factors are resolved, index generation speed can be improved. To achieve this effect, a hash table is used to store each hash value during the selection process, and for hash values for which there is no result in the hash table, one index is discarded in the candidate index set, and the index The case where one is selected will be further compared. Based on the magnitude of the index optimization value that can be obtained in these two cases, the result corresponding to the large index optimization value is recorded in the hash table as the current hash value result. This avoids duplication of problems and increases the speed of generating optimal indexes.

At step 105, the tuning index set is stored in the target database.
In some embodiments, the execution entity may store the tuning index set in a target database. Here, the target database may be a database corresponding to the query log, and in the target database, the tuning and
Tuning index query data in index sets can be used.
Intelligent software for relational databases of some embodiments of the present disclosure
Index tuning methods can effectively tune indexes and improve data query rates in real time. In particular, the reason indexes cannot be adjusted effectively is that indexes for a database instance cannot be adjusted dynamically. Based on this, the intelligent index tuning method for relational databases of some embodiments of the present disclosure first parses each query statement in the query log to generate a query statement parse tree, Including obtaining a set of sentence parsing trees. Therefore, by analyzing the applied query logs, the indexes of a database instance can be adjusted dynamically. Then, a candidate index set is generated based on the query sentence analysis tree set. Therefore, it is possible to select the most effective set of indexes for query optimization. Next, an index optimization value for each candidate index in the candidate index set is determined. Therefore, each candidate index can be quantized with respect to the query optimization effect of the query, and the query optimization effect of each candidate index can be visually expressed. Next, from among the candidate index set, the sum of the index sizes of each candidate index among the at least one candidate index is less than or equal to a preset threshold, and the index optimization value of each candidate index is At least one candidate index that satisfies a preset condition whose sum is maximum,
Select as a preset index set. Therefore, an index set that does not exceed a preset threshold and has the highest query optimization effect can be obtained. Finally, store the above tuning index set in the target database. This allows the index query data after tuning to be used in the target database. Through the analysis of different query statements under different application scenes, it is possible to dynamically adjust the index of the database instance, effectively optimizing the index, and improving the query performance of the database system. can do.
With further reference to FIG. 2, the present disclosure describes several intelligent index tuning systems for relational databases that correspond to embodiments of the method shown in FIG. This system can be specifically applied to various electronic devices.
As shown in FIG. 2, an intelligent index tuning system 200 for relational databases according to some embodiments includes an analysis unit 201, a generation unit 202,
It includes a determination section 203, a selection section 204, and a storage section 205. Here, the analysis unit 201 is configured to analyze each query sentence in the query log, generate a query sentence analysis tree, and obtain a set of query sentence analysis trees. The generation unit 202 is configured to analyze a tree set based on the above query statement and generate a candidate index set. The determining unit 203 determines an index optimization value for each candidate index in the candidate index set. The selection unit 204 is configured to select at least one candidate index that satisfies preset conditions as an adjustment index set from the candidate index set. The storage unit 205 is configured to store the above-mentioned tuning index set in the target database.
It will be understood that the units described in this system 200 correspond to the steps of the method described with reference to FIG. Accordingly, the operations, features, and beneficial effects described for the above-described method apply equally to system 100 and the units included therein, and will not be further described here.
Referring now to FIG. 3, an electronic device 3 suitable for implementing some embodiments of the present disclosure
A structural schematic diagram of 00 is shown. The electronic device shown in FIG. 3 is merely an example, and does not impose any limitations on the functions and scope of use of the embodiments of the present disclosure.
As shown in FIG. 3, electronic device 300 performs various appropriate operations and processes in accordance with programs stored in read-only memory (ROM) 302 or programs loaded into random access memory (RAM) 303 from storage device 308. A processing device (eg, a central processor, a graphics processor, etc.) 301 can be included.
The RAM 303 also stores various programs and data necessary for the operation of the electronic device 300. The processing device 301, ROM 302, and RAM 303 are connected to each other via a bus 304. An input/output (I/O) interface 305 is also connected to the bus 304 .
Generally, the following devices include input devices 306, including touch screens, touch pads, keyboards, mice, cameras, microphones, accelerometers, gyroscopes, etc.;
An output device 307 including an LCD), a speaker, a vibrator, etc., a storage device 308 including a magnetic tape, a hard disk, etc., and a communication device 309. The communication device 309 is
Electronic equipment 300 may enable wireless or wired communication with other devices to exchange data. Although FIG. 3 shows an electronic device 300 that includes various devices,
It should be understood that not all of the illustrated devices need be implemented or included. Alternatively, more or fewer devices may be implemented or included. Each block shown in FIG. 3 may represent one device, or may represent multiple devices as necessary.

Claims (1)

a parsing unit configured to parse each query sentence in the query log, generate a query sentence parsing tree, and obtain a set of query sentence parsing trees;
a generation unit configured to generate a candidate index set and parsing the tree set based on a query statement;
a determining unit configured to determine an index optimization value for each candidate index in the candidate index set;
The decision unit is
The index optimization value is the value for the database system of the corresponding candidate index.
The degree of query optimization can be characterized,
Using a pre-trained target value prediction model, candidate images are generated for each query sentence in the query log.
Determine the target value of the index, obtain the target value set,
The target value prediction model takes the candidate index and the feature attributes of the query statement as input, and calculates the target value.
This is a model that outputs the output, and the main body of execution is the output obtained from the target value prediction model that has been trained in advance.
determine the target value of the candidate index for each query statement in the query log.
The target value in the target value set can be queried if a candidate index exists.
The execution time required to execute the statement and the query statement when no candidate index exists.
The difference between the execution time required to execute the query whose candidate index corresponds to the target value
Characterize the query optimization effect that can be obtained for resource statements,
The execution entity stores the first target number of query statements and the second target number of indexes in the query log.
A query statement sample set and an index sample are provided as query statement samples and index samples, respectively.
Obtain the dex sample set,
The execution entity executes the attribute characteristics and index sample of the query statement samples in the query statement sample set.
Obtain a feature attribute set using the attribute feature of the index sample in the sample set as the feature attribute.
Therefore, the feature attributes of the feature attribute set are the attribute values that the query statement sample can obtain at the compilation stage.
Here, the category of the query statement sample and the record of the table related to the query statement sample are
number of nodes, number of operators included in query statement samples, and whether index samples exist.
The number of records for a table in a given database, and the number of records included in the index sample.
The number of fields included are all attribute values obtained during the parsing stage and are dependent on the database installation.
Arrange all the fields in the tables in the database in a certain order and use the query statement sample and import.
If we wasteheat encode the fields used in the dex sample in this order, the above query
After selecting the conditional sentence in the sentence sample, encode the related fields and encode the above index sample.
The encoding of the fields included in the pull is obtained, and in the parsing phase, the sample query statement is
Whether the query statement satisfies the prefix call principle of the index sample
Do the fields updated in the sample include fields in the index sample?
Compare whether the index sample satisfies the prefix call principle.
and whether a sample query statement causes an update to a sample index.
Separately,
The main body of execution is each query statement sample in the query statement sample collection and the index sample collection.
Combine each index sample in the combination, and query statement sample and index in each combination.
inputting feature attributes of the index sample into at least one initial target value prediction submodel;
Perform model training and use the trained model as at least one target value prediction submodel.
, where the above at least one initial target value prediction submodel is a linear prediction model, a gradient
Improved decision tree, DNN (Deep Neural Network)
network),
The execution entity uses the combined model method to evaluate at least one target value prediction submodel.
Determine the weight of each target value prediction submodel for
The execution entity performs weighted addition based on the weight of each target value prediction submodel to predict the target value.
Obtain the measurement model,
Determine the sum of each target value in the target value set as the total sum of target values,
Determine the time value of the candidate index above, where the time value builds the candidate index
Characterize the time required to
The difference between the product of the total sum of the first numerical value and the target value and the product of the second numerical value and the time value is calculated as a candidate index.
where the first value and the second value are the index optimization values of the candidate index.
to adjust the impact of the cost of building the index on the usefulness of the candidate index.
A value that is preset to and within the range of the preset value, and the above preset value
The range of is [0,1], and the product of the first value and the sum of the target values is the query for the candidate index.
To characterize the optimization effect, the product of the second value and the time value is used to construct a candidate index.
The larger the ratio between the first number and the second number, the more likely the candidate index is
focus on the optimization benefits of candidate indexes, rather than the cost required to build indexes.
Sign,
a selection unit that selects at least one candidate index that satisfies preset conditions as a tuning index set from the candidate index set;
a storage unit configured to store the tuning index set in the target database;
Intelligent index tuning system for relational databases, including: