CN116775666B - Method for automatically adjusting data index on line - Google Patents
Method for automatically adjusting data index on line Download PDFInfo
- Publication number
- CN116775666B CN116775666B CN202311072852.7A CN202311072852A CN116775666B CN 116775666 B CN116775666 B CN 116775666B CN 202311072852 A CN202311072852 A CN 202311072852A CN 116775666 B CN116775666 B CN 116775666B
- Authority
- CN
- China
- Prior art keywords
- arm
- index
- query
- super
- slot machine
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 66
- 230000008569 process Effects 0.000 claims description 20
- 238000009825 accumulation Methods 0.000 claims description 16
- 239000013598 vector Substances 0.000 claims description 13
- 238000001914 filtration Methods 0.000 claims description 6
- 238000005457 optimization Methods 0.000 abstract description 9
- 230000000694 effects Effects 0.000 abstract description 5
- 208000001613 Gambling Diseases 0.000 description 11
- 230000008901 benefit Effects 0.000 description 7
- 230000001419 dependent effect Effects 0.000 description 6
- 230000009471 action Effects 0.000 description 5
- 238000013459 approach Methods 0.000 description 3
- 230000006872 improvement Effects 0.000 description 3
- 238000010801 machine learning Methods 0.000 description 3
- 230000001186 cumulative effect Effects 0.000 description 2
- 238000013461 design Methods 0.000 description 2
- 238000011161 development Methods 0.000 description 2
- 230000018109 developmental process Effects 0.000 description 2
- 230000006870 function Effects 0.000 description 2
- 239000011159 matrix material Substances 0.000 description 2
- 238000012545 processing Methods 0.000 description 2
- 238000012216 screening Methods 0.000 description 2
- 238000012549 training Methods 0.000 description 2
- QEIQEORTEYHSJH-UHFFFAOYSA-N Armin Natural products C1=CC(=O)OC2=C(O)C(OCC(CCO)C)=CC=C21 QEIQEORTEYHSJH-UHFFFAOYSA-N 0.000 description 1
- 230000015556 catabolic process Effects 0.000 description 1
- 230000008859 change Effects 0.000 description 1
- 238000006731 degradation reaction Methods 0.000 description 1
- 238000012217 deletion Methods 0.000 description 1
- 230000037430 deletion Effects 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 230000003467 diminishing effect Effects 0.000 description 1
- 239000006185 dispersion Substances 0.000 description 1
- 239000000284 extract Substances 0.000 description 1
- 230000007246 mechanism Effects 0.000 description 1
- 230000002028 premature Effects 0.000 description 1
- 238000002360 preparation method Methods 0.000 description 1
- 238000011160 research Methods 0.000 description 1
- 230000004044 response Effects 0.000 description 1
- 230000000717 retained effect Effects 0.000 description 1
- 238000010187 selection method Methods 0.000 description 1
- 230000000153 supplemental effect Effects 0.000 description 1
- 230000001052 transient effect Effects 0.000 description 1
- 230000007704 transition Effects 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
A method for automatically adjusting and optimizing data indexes on line comprises the following steps: establishing a multi-arm slot machine model based on the index selection flow, wherein each arm in the multi-arm slot machine model represents a query data flow from a query statement to a query result; generating a new index arm based on the current workload, and adding the new index arm into the original index arm set to form a new index arm set; and determining an execution super arm in the new index arm set through a greedy prediction model in the multi-arm slot machine model, wherein the execution super arm is used for executing database query work of the current workload. The index optimization problem is modeled as a sequential decision problem of the slot machine, each possible index configuration is represented as one arm, and then the relevant index is dynamically generated as the arm according to the query characteristics, so that the search space is reduced, and the learning complexity is reduced. The method independent of an inaccurate cost estimation model can enable learned knowledge to be closer to actual effects.
Description
Technical Field
The invention relates to the technical field of database indexes, in particular to a method for automatically adjusting and optimizing a data index on line.
Background
Database query using database indexes has long been a concern in database research, and applying appropriate index support to queries with different conditions will lead to significant performance improvements.
While most of today's index selection solutions are still highly dependent on manual operations, requiring the database administrator to make calls in an offline mode, which means that the database administrator needs to identify and provide a representative training workload, the traditional approach of pre-indexing and then performing work with a uniform index for different types of queries has gradually failed to meet the current database query performance requirements. Even the latest developments, such as query stores, have limited support for dynamic environments.
Therefore, a method for online automatic tuning of data index is needed.
Disclosure of Invention
The invention provides a method for automatically adjusting and optimizing data indexes on line, which is used for solving the problems that the existing index method cannot be updated in real time according to an on-line index instruction and is difficult to adapt to a dynamic environment. The specific technical scheme of the invention is as follows:
in a first aspect, the present invention provides a method for online automatic tuning of a data index, the method comprising:
establishing a multi-arm slot machine model based on the index selection flow, wherein each arm in the multi-arm slot machine model represents a query data flow from a query statement to a query result;
generating a new index arm based on the current workload, and adding the new index arm into the original index arm set to form a new index arm set;
and determining an execution super arm in the new index arm set through a greedy prediction model in the multi-arm slot machine model, wherein the execution super arm is used for executing database query work of the current workload.
Further, after the multi-arm slot machine model is built based on the index selection process, the method further comprises:
determining an accumulation of the multi-arm slot machine model based on the index selected rewardsUnfortunately, the accumulation is unfortunately used to measure the algorithmic performance of the multi-arm slot machine.
Further, the rewards selected based on the index determine an accumulation of the multi-arm slot machine modelUnfortunately, include:
determining the expected score of the actually executed super arm in each round of database query work, wherein the expected score of the actually executed super arm is the reward selected by the index;
determining an expected score of an ideal optimal super arm in each round of database query work;
the expected score of the super arm actually executed by each round is differenced with the expected score of the corresponding optimal super arm, and the difference values of each round are added to determine accumulationUnfortunately, the following is true.
Further, the process of establishing the multi-arm slot machine model based on the index selection includes:
acquiring an index column prefix and derived statistical information of the index selection process, and taking the index column prefix and the derived statistical information as context information of arms in the multi-arm slot machine model;
generating a plurality of original super arms based on known multi-group workloads, wherein a set of the plurality of original super arms is an original index arm set;
determining rewards for super arms of the multi-arm slot machine model based on index creation time and query execution time.
Further, the generating a new index arm based on the current workload includes:
extracting query features based on the structure of the query statement in the workload;
generating information of all relevant indexes contributing to the query based on the query characteristics, wherein the information of the relevant indexes comprises an index state, an index type and a table relation;
and generating index arms based on the information of the related indexes, wherein a set of index arms corresponding to the information of all the related indexes is an index arm candidate set.
Further, after generating all relevant index information contributing to the query based on the query features, the method further includes:
and filtering the information of the related indexes according to the existing index states, index types and table relations.
Further, before adding the new index arm to the original index arm set, the method further includes: the new index arm is represented as a vector representation acceptable to the multi-arm slot machine model.
Further, the determining, by the greedy prediction model in the multi-arm slot machine model, the execution super arm in the new index arm set includes:
determining a score for each arm in the new index arm set based on the greedy predictive model;
and determining the approximate score of the super arm based on the score of each arm, wherein the super arm with the highest approximate score is the executing super arm.
Further, after determining the execution super arm in the new index arm set through the greedy prediction model in the multi-arm slot machine model, the method further includes:
deleting arms which are no longer executable under the residual memory budget based on the residual memory;
deleting the arm covered by the prefix matching of the execution super arm based on the prefix matching;
and deleting the arm with the greedy predictive model score of negative based on the greedy predictive model.
In a second aspect, the present invention also provides an online automatic tuning system for data indexes, where the system includes: the system comprises a model building unit, an index arm unit and a super arm unit;
the model building unit is used for building a multi-arm slot machine model based on the index selection process, wherein each arm in the multi-arm slot machine model represents a query data process from a query statement to a query result;
the index arm unit is used for generating a new index arm based on the current workload, and adding the new index arm into the original index arm set to form a new index arm set;
the super arm unit is used for determining an execution super arm in the new index arm set through a greedy prediction model in the multi-arm slot machine model, and the execution super arm is used for executing database query work of the current workload.
Effects of the invention
And (5) carrying out automatic index generation and selection based on the multi-arm slot machine model. According to the method, index selection is modeled as a multi-arm slot machine, an index optimization problem is modeled as a sequential decision problem of the slot machine, each possible index configuration is expressed as one arm, and then related indexes are dynamically generated according to query characteristics to serve as arms instead of pre-enumerating and storing all possible indexes, so that the search space is reduced, and the learning complexity is reduced; meanwhile, rich context features are designed for each arm, index attributes, statistical information, query features and the like are reflected, so that index selection is more dependent on the context information, and the model is ensured to adapt to a subsequent algorithm; the method which does not depend on an inaccurate cost estimation model can enable the learned knowledge to be closer to the actual effect by directly using the running time of the index on the real query as feedback.
Drawings
FIG. 1 is an overall flow chart of a method for online automatic tuning of a data index;
FIG. 2 is an overall architecture diagram of a method for online automatic tuning of data indexes.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention. It will be apparent that the described embodiments are only some, but not all, embodiments of the invention. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
Specific example 1:
the invention provides a method for automatically adjusting and optimizing data indexes on line, which is used for solving the problems that the existing index method cannot be updated in real time according to an on-line index instruction and is difficult to adapt to a dynamic environment. The specific technical scheme of the invention is as follows:
in a first aspect, the present invention provides a method for online automatic tuning of a data index, the method comprising:
establishing a multi-arm slot machine model based on the index selection flow, wherein each arm in the multi-arm slot machine model represents a query data flow from a query statement to a query result; generating a new index arm based on the current workload, and adding the new index arm into the original index arm set to form a new index arm set; and determining an execution super arm in the new index arm set through a greedy prediction model in the multi-arm slot machine model, wherein the execution super arm is used for executing database query work of the current workload.
In this embodiment, the multi-arm slot machine model includes a plurality of arms, each of which is an index, representing a query data flow from a query statement to a query result, also referred to as an index arm. A particular plurality of index arms form a set of arms, either super arms or super arms. One super-arm is the index sequence that the round is to apply to the workload. After the multi-arm slot machine model is established, the model can be applied to online automatic tuning and optimizing application of data index. The process is as follows: according to the current workload, a plurality of index arms are generated, and the index arms form a super arm. This super arm is added to the index arm set. The newly added index arm set will remain in a certain round, avoiding premature deletion in subsequent updates. After the index arm set is determined, the optimal super arm is found through a greedy prediction model and is used for executing database query work.
Further, after the multi-arm slot machine model is built based on the index selection process, the method further comprises: determining an accumulation of the multi-arm slot machine model based on the index selected rewardsUnfortunately, the accumulation is unfortunately used to measure the algorithmic performance of the multi-arm slot machine.
In this embodiment, performance of the gambling algorithm is evaluatedThe estimation is measured by accumulating regrets. The accumulation is defined as the total expected difference between the rewards of the selected superarm in the T-wheel and the optimal superarm, accumulation of the present inventionUnfortunately, further improvements have been made on this basis. Accumulation->Unfortunately, is the sum of expected transient regrets. I.e. accumulate +.>Unfortunately, this is achieved by summing the difference between the expected score of the superarm for each round and the expected score of the optimal superarm. This metric is used to measure +.>Performance of the algorithm by->The performance of the approximation prediction algorithm is guaranteed as a measure.
Further, the rewards selected based on the index determine an accumulation of the multi-arm slot machine modelUnfortunately, include: determining the expected score of the actually executed super arm in each round of database query work, wherein the expected score of the actually executed super arm is the reward selected by the index; determining an expected score of an ideal optimal super arm in each round of database query work; the expected score of the super arm actually executed by each round is differenced with the expected score of the corresponding optimal super arm, and the difference values of each round are added to determine the accumulated +.>Unfortunately, the following is true.
Further, the process of establishing the multi-arm slot machine model based on the index selection includes: acquiring an index column prefix and derived statistical information of the index selection process, and taking the index column prefix and the derived statistical information as context information of arms in the multi-arm slot machine model; generating a plurality of original super arms based on known multi-group workloads, wherein a set of the plurality of original super arms is an original index arm set; determining rewards for super arms of the multi-arm slot machine model based on index creation time and query execution time.
In this embodiment, the arm context information, i.e., the definition of the arm, determines the specific components of the arm. The context information of the arm includes a context part 1 and a context part 2. Context part 1: index column prefix. Each column encodes a context component, the similarity of the arms depending on whether there is a similar column prefix; it is not sufficient to judge only the common index column, which also contains the order problem of the combined index. Context part 2: deriving statistical information. Refers to data that is additionally acquired when a query is executed. These information are derived from the raw statistics and can provide valuable information about the arms and work sequences involved in the query, with details about the derived statistics provided by the optimizer. These statistics include: a boolean value indicating whether an overlay index exists; if the size of the index has not been actually calculated, it will be estimated as the index size divided by the database size (otherwise 0); and index usage from previous query runs.
Further, the generating a new index arm based on the current workload includes: extracting query features based on the structure of the query statement in the workload; generating information of all relevant indexes contributing to the query based on the query characteristics, wherein the information of the relevant indexes comprises an index state, an index type and a table relation; and generating index arms based on the information of the related indexes, wherein a set of index arms corresponding to the information of all the related indexes is an index arm candidate set.
Further, after generating all relevant index information contributing to the query based on the query features, the method further includes: and filtering the information of the related indexes according to the existing index states, index types and table relations.
Further, before adding the new index arm to the original index arm set, the method further includes: the new index arm is represented as a vector representation acceptable to the multi-arm slot machine model.
Further, the determining, by the greedy prediction model in the multi-arm slot machine model, the execution super arm in the new index arm set includes: determining a score for each arm in the new index arm set based on the greedy predictive model; and determining the approximate score of the super arm based on the score of each arm, wherein the super arm with the highest approximate score is the executing super arm.
Further, after determining the execution super arm in the new index arm set through the greedy prediction model in the multi-arm slot machine model, the method further includes: deleting arms which are no longer executable under the residual memory budget based on the residual memory; deleting the arm covered by the prefix matching of the execution super arm based on the prefix matching; and deleting the arm with the greedy predictive model score of negative based on the greedy predictive model.
Specific example 2:
the invention provides an automatic online index selection method, which is different from the traditional index selection and optimization scheme, abandons a database manager and a query optimizer, and learns the advantages of a feasible structure through strategic exploration and direct performance observation. This problem is considered in the present method as the problem of making sequential decisions under uncertainty, i.e. constantly optimizing iterations in a gambling learning environment. The multi-arm gambling machine model used by the invention balances exploration and utilization, and can prove that the average performance converges to an optimal strategy, namely an optimal strategy with perfect prejudice.
Technical problems faced by the present invention
1. Relying on cost models that may be inaccurate
Traditional automatic index optimization methods rely on a cost model of a query optimizer to evaluate the quality of different indexes. However, these model-dependent parameter assumptions are not always accurate, resulting in poor index selection. The invention can capture index performance in the actual execution environment by directly utilizing the run-time feedback, and does not need to rely on a cost model which is possibly inaccurate.
2. Failure to cope with dynamically changing query loads
Many real world query loads are unstable, varying temporarily. The traditional method relies on a preset representative load to perform offline optimization, and cannot cope with dynamic changes. The multi-arm slot machine learning method can dynamically learn to continuously adapt to the change of the query load under the online condition through multi-round exploration, and does not need a representative load.
3. Difficulty in optimizing index selection combinations
The traditional method needs to solve the complex combination optimization problem through traversal and the like. The present invention provides a provable approximation solution, i.e. all the alternative index combinations are treated as arms of a multi-arm slot machine model, followed by a learning selection.
In general, the invention realizes a more stable, parameter-free and dynamically-changing index automatic optimization solution by introducing multi-arm slot machine learning.
The technical proposal of the invention
First, an online index selection process is established as a combined multi-arm slot machine model.
The goal of the online database index selection problem is to select a set of indices (hereinafter simply referred to as configurations) to minimize the total run time of the work load sequence given memory permissions. Neither the workload sequence nor the system runtime is known in advance. Assume that the workload isWherein->Representing a single query. Let I be the set of secondary indexes, +.>Representing implementation configuration->Required memory space, whileThen the index configuration set feasible within the total memory enable M is represented. Our aim is to propose a configuration sequence +.>Wherein->Is the configuration of the t-th wheel, and +.>To initiate configuration, make->The total work load time is the smallest:
wherein,indicating the recommended time in round t (i.e. the running time of the recommendation tool or the query optimization tool), while +.>Representing the slave configuration->Transition to configuration->Time of creation of the delta index. Finally, the step of obtaining the product,is shown in the configuration->Down execution Mini workload->I.e. the sum of the response times of the individual queries. At round t, the system will perform the following steps:
(1) in preparation for processing an upcoming workloadWhen a group of indexes is selected->But cannot be directly accessed +>。/>Dependent on historical workload only>Observing the corresponding selected index set and the result performance;
(2) to be configured withThe index instantiation which does not exist in (i.e.)>And->All indexes in the difference set;
(3) receiving a workloadAll queries therein are executed and the time consumption of each individual query and each operation in the corresponding query plan is measured.
In the method, the online index selection problem is solved by a multi-arm gambling Machine (MAB) in statistical machine learning, wherein different "arms" correspond to the selected index. First define the context of "arm":
context part 1: index column prefix. Each column codeA context component, the similarity of the arms depends on whether they have similar column prefixes; it is not sufficient to judge only the common index column, which also contains the order problem of the combined index. When a column is included in the index and is a workload predicate column (combination of predicate verbs), the corresponding context component may be assigned a value ofWherein->Representing the position of the column in the index. If the column is not contained in the index or is not a workload predicate column, then the value of the context component will be set to 0, including the case where only the payload portion is covered. In short, only the eligible columns will have an effect on the value of the context component, otherwise it will have a value of 0.
Context part 2: deriving statistical information. Refers to data that is additionally acquired when a query is executed. These information are derived from the raw statistics and can provide valuable information about the arms and work sequences involved in the query, with details about the derived statistics provided by the optimizer. These statistics include: a boolean value indicating whether an overlay index exists; if the size of the index has not been actually calculated, it will be estimated as the index size divided by the database size (otherwise 0); and index usage from previous query runs.
A multi-arm gambling machine model is then defined:
the following notations are used: non-scalar values are indicated in bold: lowercase letters represent vectors and uppercase letters represent matrices. [k] Denoted as the set { k e N }, the transpose of the representation matrix or vector is apostrophed.
In the context of combined gambling under semi-intensive feedback, repeated selection from k possible actions is involved, the number of rounds being t=1, 2. In this multi-arm gambling problem (MAB):
(1) observe each action or arm i e k]Context feature vector of (c)(which may be random or selected by an adversary), recorded asAnd accompanying its cost->，/>Real space
(2) Selecting or pulling a group of arms (commonly referred to as super arms)Limiting the possible superarm sets toOne super arm is the index sequence to be applied to the workload of the round;
(3) for each ofObserve the random score extracted from a fixed but unknown arm distribution +.>This distribution is dependent only on the arm +.>And its context->The true expected value of which is contained in the unknown variableIs a kind of medium.
Reward modeling since the goal of the physical design adjustment tool is to minimize end-to-end workload time, the index creation time and query execution time are then included in the rewards of the workload, omitting the index recommendation time, as it is independent of the arm selection.
Implementation of the bonus signal for one arm includes, for each armIn the arrangement->Lower workload->Is taken as profit->. By definition->For query optimizers in a given configuration +.>The following is a list of indexes used by query q, for which the benefits of index i are defined as follows:
wherein the method comprises the steps ofIs index->The list of>Representation watch->And queriesqIs a full table scan time of (c). />The empty set does not have the scan time using index i subtracted from the scan time using index i.
The benefits of the workload are related to the benefits of a single query:
according to this definition, if the optimizer does not use i in the current round t, then revenueWill be 0, the benefit may be negative if the creation of an index results in performance degradation. The creation time of index i is considered a negative prize, which is only when i is instantiated in round t, otherwise 0:
maximizing end-to-end workload time gain, or, in colloquial terms, minimizing end-to-end workload time, is the goal of gambling. As defined previously, the total workload time Ctot is the sum of the execution time, the recommended time, and the creation time accumulated in the round. Thus, minimizing the sum term for each round is an equivalent problem. Modifying the execution time into time gain, and simultaneously neglecting the recommended time to obtain the super arm rewards of each round:
second, the pairs of rewards selected in combination with the indexThe algorithm is used for improving the chaise analysis of the multi-arm slot machine.
UsingThe algorithm solves the index selection problem based on the multi-arm slot machine. The scores of the individual arms are modeled as a linear relationship with respect to their context: />Wherein->Is an unknown zero-mean (sub-Gaussian) random variable,>is an unknown, but fixed parameter, belonging to the real vector space,and->Is known as context information. This means that all learned knowledge is contained in +.>In the estimate of (2), and->Is a parameter shared among all arms, so that it is not necessary to explore each arm. For->Can be achieved by ridge regression, for each round t +.>New data point->Further speeding up the estimationThis is a more efficient method than if only one sample is observed.
By usingTo perform a point estimation of the desired score, wherein +.>Is the coefficient of the ridge regression training between the observation reward and the context of arm i. However, this estimateThe score estimates are ignored by the meter. Intuitively, to balance the exploration and development of a multi-arm gambling machine, an exploration addition should be added to those actions whose score estimates are less deterministic (i.e., arms whose estimates are more variance). This introduces an upper bound of confidence (UCB) in place of the expected value and can be calculated by:
wherein,is the exploration gain factor, +.>Is a positive d x d dispersion matrix of the chosen arm's context before round t-1. />The first term of (a) corresponds to the immediate prize of arm i and the second term corresponds to its exploration gain because the value of this arm is larger when it is sensitive to less defined context elements (i.e. underexplored context Wen Weidu). Thus, by using +.>Replace->Arms whose context lies in an area where the context space is not fully explored are more likely to be selected.
Ideally, a super arm is selectedShould be made +.>Reaching a maximum. In practice, a solution is obtained by some approximation algorithm such that +.>Approaching a maximum. With this criterion, the method defines a +.>An approximation prediction algorithm.
Definition 1: one or more ofThe approximate predictive algorithm outputs a super arm +.>Satisfies the following conditionsWherein->And the inputs are r and X.
While solving this sub-modularity with knapsack constraint (sub-modularity means that when adding an action to a selected set of actions its marginal gain is decremented) problem in this approach can be solved efficiently by greedy algorithms (iteratively selecting the arms with the highest score for which the remaining cost is viable), when。/>The algorithm is specifically as follows:
algorithm 1:algorithm
Input:
Initialize:
for t = 1, . . . , T do
Observe
Hv/Violin regression estimation
fordo
Observe context
end for
Approximation prediction using alpha
Play and observe/>for all/>
Regression update
Regression update
end for
The performance of a gambling algorithm is assessed by accumulating regrets. Cumulative regrettably is defined as the reward of the selected superarm in the T-roundAnd optimal super arm->The total expected difference between them, and such a measure is not applicable +.>An algorithm because the performance of the algorithm depends on the performance of the predictive algorithm. Therefore, a new metric should be used to measure +.>Using the performance guarantee of the predictive algorithm as a measure, defined as follows:
definition 2: accumulation ofUnfortunately, is the sum of expected instantaneous regrets, expressed as:
wherein,is composed of->Approximation prediction algorithm as part of gambling algorithm return super arm, but +.>Is a vector containing the true expected score for each arm.
In other words, accumulateUnfortunately, this is achieved by summing the difference between the expected score of the superarm for each round and the expected score of the optimal superarm. This metric is used to measure +.>Performance of the algorithmBy->The performance of the approximation prediction algorithm is guaranteed as a measure.
And thirdly, generating an index according to the workload and automatically selecting.
For online index adjustment, it is desirable to select those arms that cover important operations while not exceeding the required number so that rewards are observable and follow meaningful regrettable limits. Furthermore, the context and predictive model need to be computed efficiently and predict rewards. Each workload query is monitored for its characteristics, such as run-time, query predicates, payloads, etc. These observations are used to generate the relevant arms and their context. The learner then selects a desired index configuration and, after the actual query is executed, the system analyzes the benefits of the implemented index and translates those benefits into a reward signal for learning.
Dynamic arms based on workload predicates. In contrast to enumeration of column combinations, a relevant arm (index) may be generated based on a query: including combinations and permutations of query predicates (including join predicates) while considering whether the query contains the payload attributes in the query selection clause. Such workload-based arm generation greatly reduces arm space while also leveraging the features of real-world workloads, as real workloads are typically concentrated on a small portion of the attributes of the table. Such workload-based indexing scheme generation is possible because the system can determine the round of possible indexing schemes at the beginning of each round. The index arm generation steps are as follows:
analyzing the structure of the query statement: and performing lexical grammar analysis on the query statement, analyzing clauses such as SELECT, FROM, WHERE, GROUP BY, ORDER BY and the like, and understanding the intention and access mode of the query.
Extracting the characteristics of the query: columns, predicates, constant values, etc. in the WHERE and JOIN conditions are extracted as features. And extracts the GROUP BY, ORDER BY, and SELECT columns as supplemental features.
Generating index candidates: based on the above features, all information of relevant indexes that help to accelerate the query is generated. Such as creating a b-tree index for the combined numeric condition on the WHERE/JOIN condition column.
Filtering and processing: and filtering according to the existing index state, index type, table relation and the like to obtain a final index arm candidate set.
Expressed as vectors: the index arm is represented as a vector representation acceptable to the model using the one-hot vector approach.
Storing and updating: the new index arm vector is added to the total index arm set and the arm set for a certain historical turn is retained in memory.
In terms of super arm selection, use andthe algorithm selects the superarm based on a greedy (i.e., near optimal) predictive model that is closely related, i.e., the superarm is selected based on the scores of the individual arms. Because the superarm rewards are the sum of the individual arm rewards, the superarm rewards have an objective function that is (sub) modular and that exhibits monotonicity and Lipschitz continuity. To maximize the sub-modulo (diminishing returns) objective function, an efficient and near-optimal greedy predictive model may be used to derive the approximate solution. First, those arms that score negative are pruned because they do not contribute to the decision process. The arm selection and screening steps are then alternated until the memory reaches the budget threshold.
In the arm selecting step, one of the arms having the highest score is selected from the center based on the score of each arm. In the screening step, those arms that are no longer viable under the remaining memory budget are filtered out, and arms that have been selected are covered by prefix matching. If an overlay index is selected for a query, then the other arms generated by the query are filtered out.
Innovation point of the invention
1. And (5) carrying out automatic index generation and selection based on the multi-arm slot machine model. According to the method, index selection is modeled as a multi-arm slot machine, an index optimization problem is modeled as a sequential decision problem of the slot machine, each possible index configuration is expressed as one arm, and then related indexes are dynamically generated according to query characteristics to serve as arms instead of pre-enumerating and storing all possible indexes, so that the search space is reduced, and the learning complexity is reduced; meanwhile, rich context features are designed for each arm, index attributes, statistical information, query features and the like are reflected, so that index selection is more dependent on the context information, and the model is ensured to adapt to a subsequent algorithm; the method which does not depend on an inaccurate cost estimation model can enable the learned knowledge to be closer to the actual effect by directly using the running time of the index on the real query as feedback.
2. Improvement of the designThe algorithm adjusts the chaise and selects the optimal rewards for the model. The method improves->The algorithm adds the chasing mechanism in the algorithm, and carries out balance by adjusting the alpha parameter value, thereby avoiding repeated exploration of the same non-optimal scheme; the aggregate rewards are designed, and the rewards integrate the index execution speed increasing and the creation cost, so that the overall workload time is directly optimized, and the performance of a real running environment can be reflected; improved->Provide accumulation->The chaise on the border ensures the stable convergence of the selection result, and the selection result can not deviate from the optimum forever. />
Specific example 3:
an on-line automatic tuning system for data index, the system comprising: the system comprises a model building unit, an index arm unit and a super arm unit;
the model building unit is used for building a multi-arm slot machine model based on the index selection process, wherein each arm in the multi-arm slot machine model represents a query data process from a query statement to a query result;
the index arm unit is used for generating a new index arm based on the current workload, and adding the new index arm into the original index arm set to form a new index arm set;
the super arm unit is used for determining an execution super arm in the new index arm set through a greedy prediction model in the multi-arm slot machine model, and the execution super arm is used for executing database query work of the current workload.
Preferably, the system further comprises a cumulative regrettable unit for:
determining an accumulation of the multi-arm slot machine model based on the index selected rewardsUnfortunately, the accumulation is unfortunately used to measure the algorithmic performance of the multi-arm slot machine.
Preferably, the accumulating regrettable unit is further configured to:
determining the expected score of the actually executed super arm in each round of database query work, wherein the expected score of the actually executed super arm is the reward selected by the index;
determining an expected score of an ideal optimal super arm in each round of database query work;
the expected score of the super arm actually executed by each round is differenced with the expected score of the corresponding optimal super arm, and the difference values of each round are added to determine accumulationUnfortunately, the following is true.
Preferably, the model building unit is further configured to:
acquiring an index column prefix and derived statistical information of the index selection process, and taking the index column prefix and the derived statistical information as context information of arms in the multi-arm slot machine model;
generating a plurality of original super arms based on known multi-group workloads, wherein a set of the plurality of original super arms is an original index arm set;
determining rewards for super arms of the multi-arm slot machine model based on index creation time and query execution time.
Preferably, the index arm unit is further configured to:
extracting query features based on the structure of the query statement in the workload;
generating information of all relevant indexes contributing to the query based on the query characteristics, wherein the information of the relevant indexes comprises an index state, an index type and a table relation;
and generating index arms based on the information of the related indexes, wherein a set of index arms corresponding to the information of all the related indexes is an index arm candidate set.
Preferably, the index arm unit is further configured to:
and filtering the information of the related indexes according to the existing index states, index types and table relations.
Preferably, the index arm unit is further configured to:
the new index arm is represented as a vector representation acceptable to the multi-arm slot machine model.
Preferably, the super arm unit is configured to:
determining a score for each arm in the new index arm set based on the greedy predictive model;
and determining the approximate score of the super arm based on the score of each arm, wherein the super arm with the highest approximate score is the executing super arm.
Preferably, the super arm unit is further configured to:
deleting arms which are no longer executable under the residual memory budget based on the residual memory;
deleting the arm covered by the prefix matching of the execution super arm based on the prefix matching;
and deleting the arm with the greedy predictive model score of negative based on the greedy predictive model.
Claims (8)
1. A method for online automatic tuning of a data index, the method comprising:
establishing a multi-arm slot machine model based on the index selection flow, wherein each arm in the multi-arm slot machine model represents a query data flow from a query statement to a query result;
generating a new index arm based on the current workload, and adding the new index arm into the original index arm set to form a new index arm set;
determining an execution super arm in the new index arm set through a greedy prediction model in the multi-arm slot machine model, wherein the execution super arm is used for executing database query work of the current workload;
the process based on index selection establishes a multi-arm slot machine model, which comprises the following steps:
acquiring an index column prefix and derived statistical information of the index selection process, and taking the index column prefix and the derived statistical information as context information of arms in the multi-arm slot machine model;
generating a plurality of original super arms based on known multi-group workloads, wherein a set of the plurality of original super arms is an original index arm set;
determining rewards of super arms of the multi-arm slot machine model based on index creation time and query execution time;
the generating a new index arm based on the current workload includes:
extracting query features based on the structure of the query statement in the workload;
generating information of all relevant indexes contributing to the query based on the query characteristics, wherein the information of the relevant indexes comprises an index state, an index type and a table relation;
and generating index arms based on the information of the related indexes, wherein a set of index arms corresponding to the information of all the related indexes is an index arm candidate set.
2. The method for online automatic tuning of a data index according to claim 1, further comprising, after the multi-arm slot machine model is built based on the index selection process:
determining an accumulation of the multi-arm slot machine model based on the index selected rewardsUnfortunately, said accumulation->Unfortunately, the algorithm performance of the multi-arm slot machine is measured.
3. The method for online automatic tuning of a data index according to claim 2, wherein said determining said accumulation of said multi-arm slot machine model is based on rewards selected by said indexUnfortunately, include:
determining the expected score of the actually executed super arm in each round of database query work, wherein the expected score of the actually executed super arm is the reward selected by the index;
determining an expected score of an ideal optimal super arm in each round of database query work;
the expected score of the super arm actually executed by each round is differenced with the expected score of the corresponding optimal super arm, and the difference values of each round are added to determine accumulationUnfortunately, the following is true.
4. The method for online automatic tuning of a data index according to claim 1, wherein after generating all relevant index information for facilitating the query based on the query features, further comprising:
and filtering the information of the related indexes according to the existing index states, index types and table relations.
5. The method for online automatic tuning of a data index according to claim 1, wherein before adding the new index arm to the original index arm set, further comprising: the new index arm is represented as a vector representation acceptable to the multi-arm slot machine model.
6. The method for online automatic tuning of a data index according to claim 1, wherein said determining the execution super arm in the new index arm set by a greedy prediction model in the multi-arm slot machine model comprises:
determining a score for each arm in the new index arm set based on the greedy predictive model;
and determining the approximate score of the super arm based on the score of each arm, wherein the super arm with the highest approximate score is the executing super arm.
7. The method for online automatic tuning of a data index according to claim 1, wherein after determining the execution super arm in the new index arm set by using a greedy prediction model in the multi-arm slot machine model, the method further comprises:
deleting arms which are no longer executable under the residual memory budget based on the residual memory;
deleting the arm covered by the prefix matching of the execution super arm based on the prefix matching;
and deleting the arm with the greedy predictive model score of negative based on the greedy predictive model.
8. An on-line automatic tuning system for data indexes, the system comprising: the system comprises a model building unit, an index arm unit and a super arm unit;
the model building unit is used for building a multi-arm slot machine model based on the index selection process, wherein each arm in the multi-arm slot machine model represents a query data process from a query statement to a query result;
the index arm unit is used for generating a new index arm based on the current workload, and adding the new index arm into the original index arm set to form a new index arm set;
the super arm unit is used for determining an execution super arm in the new index arm set through a greedy prediction model in the multi-arm slot machine model, and the execution super arm is used for executing database query work of the current workload;
the model building unit is further configured to include:
acquiring an index column prefix and derived statistical information of the index selection process, and taking the index column prefix and the derived statistical information as context information of arms in the multi-arm slot machine model;
generating a plurality of original super arms based on known multi-group workloads, wherein a set of the plurality of original super arms is an original index arm set;
determining rewards of super arms of the multi-arm slot machine model based on index creation time and query execution time;
the index arm unit is also configured to include:
extracting query features based on the structure of the query statement in the workload;
generating information of all relevant indexes contributing to the query based on the query characteristics, wherein the information of the relevant indexes comprises an index state, an index type and a table relation;
and generating index arms based on the information of the related indexes, wherein a set of index arms corresponding to the information of all the related indexes is an index arm candidate set.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202311072852.7A CN116775666B (en) | 2023-08-24 | 2023-08-24 | Method for automatically adjusting data index on line |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202311072852.7A CN116775666B (en) | 2023-08-24 | 2023-08-24 | Method for automatically adjusting data index on line |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN116775666A CN116775666A (en) | 2023-09-19 |
| CN116775666B true CN116775666B (en) | 2023-11-14 |
Family
ID=87993520
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202311072852.7A Active CN116775666B (en) | 2023-08-24 | 2023-08-24 | Method for automatically adjusting data index on line |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN116775666B (en) |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110569245A (en) * | 2019-09-10 | 2019-12-13 | 天津理工大学 | Fingerprint index prefetching method based on reinforcement learning in data de-duplication system |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20200219028A1 (en) * | 2017-09-05 | 2020-07-09 | Brandeis University | Systems, methods, and media for distributing database queries across a metered virtual network |
-
2023
- 2023-08-24 CN CN202311072852.7A patent/CN116775666B/en active Active
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110569245A (en) * | 2019-09-10 | 2019-12-13 | 天津理工大学 | Fingerprint index prefetching method based on reinforcement learning in data de-duplication system |
Non-Patent Citations (2)
| Title |
|---|
| 非稳态雾赋能网络中的在线任务卸载方法;朱兆伟;刘婷;钱骅;罗喜良;;中国科学院大学学报(第05期);全文 * |
| 面向关系数据库的智能索引调优方法;邱涛;王斌;舒昭维;赵智博;宋子文;钟延辉;;软件学报(第03期);全文 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN116775666A (en) | 2023-09-19 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| Cope | Regret and convergence bounds for a class of continuum-armed bandit problems | |
| CN101339562A (en) | Portal personalized recommendation service system introducing into interest model feedback and update mechanism | |
| CN104573062A (en) | Intelligent learning method based on description logic and case-based reasoning | |
| CN113031983B (en) | Intelligent software upgrading method and device based on deep reinforcement learning | |
| CN114911844B (en) | Approximate query optimization system based on machine learning | |
| CN113407185A (en) | Compiler optimization option recommendation method based on Bayesian optimization | |
| CN115617830A (en) | Data query optimization processing method and device based on machine learning | |
| CN112348571A (en) | Combined model sales prediction method based on sales prediction system | |
| CN116028528A (en) | Database query optimization method, system, electronic equipment and storage medium | |
| CN116775666B (en) | Method for automatically adjusting data index on line | |
| KR20200125029A (en) | Method and apparatus for regression analysis | |
| CN112270058B (en) | Optical network multichannel transmission quality prediction method based on echo state network | |
| CN111723076A (en) | Method and device for generating database index | |
| Song et al. | Spark-based cloud data analytics using multi-objective optimization | |
| Ferrarotti et al. | Synthesis of optimal feedback controllers from data via stochastic gradient descent | |
| CN116091776A (en) | Semantic segmentation method based on field increment learning | |
| CN110069347A (en) | A kind of thread dividing method of Kernel-based methods different degree | |
| Halford et al. | Selectivity correction with online machine learning | |
| Šuc et al. | Induction of qualitative trees | |
| JP6193428B1 (en) | Feature selection device, feature selection method, and program | |
| Kossmann et al. | A framework for self-managing database systems | |
| CN115035304A (en) | Image description generation method and system based on course learning | |
| KR20220049709A (en) | System and Method of Adaptive Bach Selection for Accelerating Deep Neural Network Learning based on Data Uncertainty | |
| CN115099606B (en) | Training method and terminal of power grid dispatching model | |
| Kadhim et al. | Employing boosting algorithms to predict the growth of the Iraqi GDP |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |