CN103761308A - Materialized view selection method based on self-adaption genetic algorithm - Google Patents

Abstract

The invention discloses a materialized view selection method based on the self-adaption genetic algorithm. The method includes the steps of inputting a candidate materialized view set with the view size and the access frequency attribute and on the basis of the same fact table and a dimensional table of the fact table, mapping the candidate materialized view set to nodes in a multi-dimensional data grid according to the mapping rule, setting up a multi-dimensional data grid model, defining an expense model of materialized views under the multi-dimensional data grid mode, utilizing binary system codes for converting the candidate materialized view set based on the multi-dimensional data model into a 0-1 integrated number set which can be processed through the genetic algorithm, finally, introducing a mechanism of the self-adaption adjustment crossover probability and the mutation probability to improve the genetic algorithm, and solving the materialized view selection problem through the improved genetic algorithm. Compared with a genetic algorithm which does not use the self-adaption mechanism and a traditional algorithm which uses the greedy strategy, the result obtained through the method is excellent, appropriate views can be selected for conducting materialization, and the overhead of the materialized views is minimized.

Description

A kind of Materialized View system of selection based on self-adapted genetic algorithm

Technical field

The invention belongs to data warehouse technology field, be specifically related to a kind of Materialized View system of selection based on self-adapted genetic algorithm.

Background technology

By data warehouse and online analysis process treatment technology, data are carried out comprehensive, scientifical management and multi-angle, deep analysis and excavate oneself becoming the necessary means that current business decision is analyzed.By OLAP inquire about can find fast data behind hiding information to be used for aid decision making, but OLAP inquiry often need in the process of implementation to a large amount of data select, connection and projection operation, this is a process very consuming time.In order to realize the operation of quick online analyzing and processing, can introduce Materialized View and solve this problem.

By Materialized View, the performance of data warehouse can significantly improve, and it has also formed the important means that realizes OLAP on-line analysis.But the introducing of Materialized View can bring again new problem, and preserving on the one hand Materialized View needs extra storage space, and the attended operation of Materialized View also becomes a problem demanding prompt solution on the other hand.Particularly to comprising the Materialized View of mass data, along with the variation of raw data, summary information in Materialized View can occur inconsistent with raw data, cause Materialized View to lose efficacy, now Materialized View just need to, according to the variation of raw data, refresh its data, keeps the consistance with raw data, for the Materialized View based on mass data, maintenance costs is very large.Materialized View selection problem is exactly that the view that How to choose is suitable carries out materialization, minimizes its overhead.

Materialized View selects problem to be proved to be as np complete problem, if the number of candidate's Materialized View is n, so always has 2 ⁿplant selection scheme.Employing enumeration obviously can be in the hope of optimum solution, but this algorithm expense is too large, adopt greedy algorithm to solve and often obtain locally optimal solution, and genetic algorithm is applicable to solve np complete problem, can obtain approximate optimal solution or even the optimum solution of problem through the iteration of limited number of times.

Summary of the invention

The present invention is directed to the deficiencies in the prior art, a kind of Materialized View system of selection based on self-adapted genetic algorithm is provided.

A kind of Materialized View system of selection based on self-adapted genetic algorithm of the present invention, specifically comprises the following steps:

Step (1). the candidate's Materialized View collection based on same fact table and dimension table thereof of input tape view size and access frequency attribute, according to the packet attributes that in candidate's Materialized View SQL definition statement, group-by clause comprises, according to mapping ruler, be mapped to the node in multidimensional data lattice;

Mapping ruler is defined as follows: in SQL definition statement, comprise view that packet attributes is maximum corresponding to the root node in multidimensional data lattice, any two views of concentrating for candidate's Materialized View, two views are made as respectively a and b, if the packet attributes comprising in a view SQL definition statement is the proper subclass of the packet attributes that comprises in b view SQL definition statement, the corresponding node of a view is the child node of b view institute corresponding node so, in multidimensional data lattice model, except root node does not have father node, other node has and only has a direct father node;

Step (2). calculate the overhead of the Materialized View under multidimensional data lattice model;

If Q is the alternate view collection of the materialization that needs in multidimensional data lattice model, q is a view in Q, f _q(q) represent the corresponding enquiry frequency of q, M is Materialized View collection, QueryCost (Q, M) be the query cost of Materialized View, the maintenance costs that MaintenanceCost (M) is Materialized View, the storage overhead that Storage (M) is Materialized View, TotalCost (Q, M) be the overhead of Materialized View, avg _{q ∈ Q}{ f _q(q) } be the average of view query frequency, α is scale-up factor, TotalCost (Q, M)=QueryCost (Q, M)+α × (MaintenanceCost (M)+Storage (M)), wherein α=0.5 × avg _{q ∈ Q}{ f _q(q) }.

Step (3). utilize binary coding to convert the candidate's Materialized View collection based on multidimensional data lattice model to genetic algorithm manageable 0-1 integer array, and using the size of inverse ideal adaptation degree in genetic algorithm of the Materialized View overhead of step (2) gained;

In multidimensional data lattice model, the numbering of node is corresponding to the index of array, the value of array element determines whether view is carried out to materialization, array element needs materialization for the view of this element index institute corresponding node of 1 expression, and array element does not need materialization for the view of this element index institute corresponding node of 0 expression.

Step (4). set initial population scale n, maximum iteration time max_number, according to the 0-1 integer array of step (3) gained, utilize random algorithm to produce n individuality as initial population;

Step (5). select operation, by screening, retain the high individuality of fitness in population, eliminate the low individuality of fitness in population; If Population is population, k is the individuality in population, and Fit (x) is the corresponding fitness of individual x, and individual x is selected and enters follow-on Probability p _scomputing function as follows:

$p_s=\frac{\mathrm{Fit}\left(x\right)}{\underset{k\∈\mathrm{population}}{\mathrm{\Σ}}\mathrm{Fit}\left(k\right)}$

Step (6). calculate crossover probability p _c, the decision content flag_c between generating 0 to 1 for the each individuality in population is random, if the value of flag_c is less than or equal to p _c, this individuality will carry out 2 interlace operations, otherwise this individuality need not carry out 2 interlace operations, p _ccomputing formula as follows:

$p_c=\left\{\begin{array}{cc}1-1.2\&times;{\mathrm{Fit}}_{\mathrm{avg}}/({\mathrm{Fit}}_{\max }+{\mathrm{Fit}}_{\min })& 0<{\mathrm{Fit}}_{\mathrm{avg}}/({\mathrm{Fit}}_{\max }+{\mathrm{Fit}}_{\min })<0.5\\ 0.4& \mathrm{else}\end{array}\right.$

Fit _maxfor maximum adaptation degree individual in population, Fit _minfor minimum fitness individual in population, Fit _avgfor average fitness individual in population;

The process of 2 interlace operations is as follows:

Be that two individualities that will carry out interlace operation arrange two different point of crossing at random, two point of crossing are made as respectively point1 and point2, and the gene code between two individual point1 and point2 is exchanged and produces new individuality.

Step (7). calculate crossover probability p _m, the decision content flag_m between generating 0 to 1 for the each individuality in population is random, if the value of flag_m is less than or equal to p _m, this individuality will carry out mutation operation, otherwise this individuality need not carry out mutation operation, p _mcomputing formula as follows:

$p_m=\left\{\begin{array}{cc}0.1& {\mathrm{Fit}}_{\mathrm{avg}}/({\mathrm{Fit}}_{\max }+{\mathrm{Fit}}_{\min })>0.5\\ 0.2\&times;{\mathrm{Fit}}_{\mathrm{avg}}/({\mathrm{Fit}}_{\max }+{\mathrm{Fit}}_{\min })& \mathrm{else}\end{array}\right.$

The process of mutation operation is as follows:

For the individuality that will carry out mutation operation, determine at random a gene position, if the value of element is 0 in gene position, become 1, if the value of element is 1 in gene position, become 0.

Step (8). repeating step (5), (6), (7), reach the maximum iteration time max_number that step (4) sets to iterations, final is exactly the optimum solution of requirement for the individuality of fitness maximum in population, the view collection that after being decoded, output will select to carry out materialization exactly.

The method that the present invention proposes builds multidimensional data lattice model according to candidate's Materialized View collection, consider inquiry, maintenance and the storage overhead of Materialized View, provide definite metric function and quantize overhead, and set it as the foundation that judges ideal adaptation degree size in genetic algorithm, determine that reasonable efficient adaptive is adjusted crossover probability and the mechanism of the probability that makes a variation, genetic algorithm is selected during problem solving Materialized View, and search speed soon and can Premature Convergence.When solving extensive Materialized View selection problem, compared with not adopting the genetic algorithm of adaptation mechanism and the traditional algorithm of employing Greedy strategy, adopt the result of method gained of the present invention more excellent, can select suitable view to carry out materialization, minimize the overhead of Materialized View.

Accompanying drawing explanation

The block diagram of the Materialized View system of selection of Fig. 1 based on self-adapted genetic algorithm;

Fig. 2 multidimensional data lattice model figure;

Fig. 3 self-adapted genetic algorithm solves process flow diagram.

Embodiment

The embodiment of a kind of Materialized View system of selection based on self-adapted genetic algorithm provided by the present invention is mainly divided 3 steps (as shown in Figure 1):

(1) the candidate's Materialized View collection based on same fact table and dimension table thereof of input tape view size and access frequency attribute, according to the packet attributes that in candidate's Materialized View SQL definition statement, group-by clause comprises, according to mapping ruler, be mapped to the node in multidimensional data lattice, build multidimensional data lattice model (as shown in Figure 2) and define its cost model of Materialized View down; (2) utilize binary coding to convert the candidate's Materialized View collection based on multidimensional data lattice model to genetic algorithm manageable 0-1 integer array, the numbering of node is corresponding to the index of array, and the value of array element determines whether view is carried out to materialization; (3) mechanism of utilizing self-adaptation to adjust crossover probability and variation probability is carried out improved genetic algorithms method, and utilizes improved genetic algorithm for solving Materialized View to select problem.

For sake of convenience, definition related symbol is as follows:

Q: the alternate view collection of the materialization that needs in multidimensional data lattice model.

M: Materialized View collection.

A view query in q:Q.

F _q(q): the corresponding enquiry frequency of q.

Qt (q, M): in the situation that Materialized View collection M has existed, q inquires about required query cost.

F _m(v): the frequency of maintenance of Materialized View v.

Mt (v, M): in the situation that Materialized View collection M exists, the maintenance costs that Materialized View v is required.

S (v): the corresponding storage overhead of Materialized View v.

QueryCost (Q, M): at Q and M definite in the situation that, the query cost of Materialized View.

MaintenanceCost (M): in the situation that M is definite, the maintenance costs of Materialized View.

StorageCost (M): in the situation that M is definite, the storage overhead of Materialized View.

TotalCost (Q, M): at Q and M definite in the situation that, the overhead of Materialized View.

α: scale-up factor, the proportion of the maintenance costs of determining Materialized View in overhead.

β: scale-up factor, the proportion of the storage overhead of determining Materialized View in overhead.

Fit (x): the fitness of individual x.

P _s: select probability.

P _c: crossover probability.

P _m: variation probability.

Fit _max: the maximal value of ideal adaptation degree in whole population.

Fit _min: the minimum value of ideal adaptation degree in whole population.

Fit _avg: the mean value of ideal adaptation degree in whole population.

(1) build multidimensional data lattice model and corresponding cost model

Candidate's Materialized View collection of input tape view size and access frequency attribute, according to the mapping ruler of node in packet attributes that in candidate's Materialized View SQL definition statement, group-by clause comprises and multidimensional data lattice model, build corresponding multidimensional data lattice model.Under the condition of having set up at multidimensional data lattice model, the overhead metric function of Materialized View is defined as follows:

TotalCost(Q,M)＝QueryCost(Q,M)+α×(MaintenanceCost(M)+StorageCost(M))

Wherein scale-up factor α is defined as follows:

α＝0.5×avg _q∈Q{f _q(q)}

Wherein the metric function of query cost QueryCost (Q, M) is defined as follows:

$\mathrm{QueryCost}(Q,M)=\underset{q\&Element;Q}{\mathrm{\&Sigma;}}{f}_q\left(q\right)\mathrm{qt}(q,M)$

Wherein the metric function of maintenance costs MaintenanceCost (M) is defined as follows:

$\mathrm{Maint\; enanceCost}\left(M\right)=\underset{v\&Element;M}{\mathrm{\&Sigma;}}{f}_m\left(v\right)\mathrm{mt}(v,M)$

Wherein the metric function of storage overhead StorageCost (M) is defined as follows:

$\mathrm{StorageCost}\left(M\right)=\underset{v\&Element;M}{\mathrm{\&Sigma;}}s\left(v\right)$

(2) problem conversion

Utilize binary coding to convert the candidate's Materialized View collection based on multidimensional data lattice model to genetic algorithm manageable 0-1 integer array, the numbering of node is corresponding to the index of array, the value of array element determines whether view is carried out to materialization, array element needs materialization for the view of this element index institute corresponding node of 1 expression, and array element does not need materialization for the view of this element index institute corresponding node of 0 expression.

In genetic algorithm for solving process, individual fitness is larger, and it is just larger that it enters follow-on chance, and when population loop iteration stops, now in population, the individuality of fitness maximum is exactly required optimum solution.In order to make genetic algorithm for solving Materialized View, select problem, here using the size of the inverse of Materialized View overhead ideal adaptation degree in genetic algorithm, Materialized View overhead is less, fitness is larger, when genetic algorithm iteration finishes like this, the overhead minimum of the corresponding Materialized View of individuality of fitness maximum.

(3) self-adapted genetic algorithm solves Materialized View and selects problem

Set initial population scale n, maximum iteration time max_number, according to the 0-1 integer array after the conversion of Materialized View collection, utilize random algorithm to produce n individuality as initial population;

Select operation, by screening, retain the high individuality of fitness in population, eliminate the low individuality of fitness in population; If Population is population, k is the individuality in population, and individual x is selected and enters follow-on Probability p _scomputing function as follows:

$p_s=\frac{\mathrm{Fit}\left(x\right)}{\underset{k\&Element;\mathrm{Population}}{\mathrm{\&Sigma;}}\mathrm{Fit}\left(k\right)}$

Calculate crossover probability p _c, the decision content flag_c between generating 0 to 1 for the each individuality in population is random, if the value of flag_c is less than or equal to p _c, this individuality will carry out 2 interlace operations, otherwise this individuality need not carry out 2 interlace operations, p _ccomputing formula as follows:

$p_c=\left\{\begin{array}{cc}1-1.2\&times;{\mathrm{Fit}}_{\mathrm{avg}}/({\mathrm{Fit}}_{\max }+{\mathrm{Fit}}_{\min })& 0<{\mathrm{Fit}}_{\mathrm{avg}}/({\mathrm{Fit}}_{\max }+{\mathrm{Fit}}_{\min })<0.5\\ 0.4& \mathrm{else}\end{array}\right.$

The process of 2 interlace operations is as follows:

Be that two individualities that will carry out interlace operation arrange two different point of crossing at random, two point of crossing are made as respectively point1 and point2, and the gene code between two individual point1 and point2 is exchanged and produces new individuality.

Calculate crossover probability p _m, the decision content flag_m between generating 0 to 1 for the each individuality in population is random, if the value of flag_m is less than or equal to p _m, this individuality will carry out mutation operation, otherwise this individuality need not carry out mutation operation, p _mcomputing formula as follows:

$p_m=\left\{\begin{array}{cc}0.1& {\mathrm{Fit}}_{\mathrm{avg}}/({\mathrm{Fit}}_{\max }+{\mathrm{Fit}}_{\min })>0.5\\ 0.2\&times;{\mathrm{Fit}}_{\mathrm{avg}}/({\mathrm{Fit}}_{\max }+{\mathrm{Fit}}_{\min })& \mathrm{else}\end{array}\right.$

The process of mutation operation is as follows:

For the individuality that will carry out mutation operation, determine at random a gene position, if the value of element is 0 in gene position, become 1, if the value of element is 1 in gene position, become 0.

Repeat to select operation, calculate crossover probability p _c, interlace operation, calculates variation Probability p m, and mutation operation reaches maximum iteration time max_number to iterations, for the individuality of fitness maximum in population, is exactly finally the optimum solution of requirement, the view collection that output will select to carry out materialization exactly afterwards of decode.

The specific implementation process of above algorithm as shown in Figure 3.

A kind of Materialized View system of selection based on self-adapted genetic algorithm provided by the present invention is comprised of four functional modules, and they are respectively to build multidimensional data lattice module, tolerance overhead module, problem modular converter and self-adapted genetic algorithm to solve module.

Build multidimensional data lattice module according to the packet attributes that in candidate's Materialized View SQL definition statement, group-by clause comprises, according to mapping ruler, the candidate's Materialized View collection with view size and access frequency attribute is mapped to the node in multidimensional data lattice.

Tolerance overhead module is according to the needs of actual conditions, and the proportion of query cost, maintenance costs and the storage overhead of determining Materialized View in overhead calculates the metric of overhead.

Problem modular converter utilizes binary coding to convert the candidate's Materialized View collection based on multidimensional data lattice model to genetic algorithm manageable 0-1 integer array, and using the size of inverse ideal adaptation degree in genetic algorithm of the Materialized View overhead of tolerance overhead module gained, the numbering of node is corresponding to the index of array, the value of array element determines whether view is carried out to materialization, array element needs materialization for the view of this element index institute corresponding node of 1 expression, and array element does not need materialization for the view of this element index institute corresponding node of 0 expression.

Self-adapted genetic algorithm solves the mechanism that module utilizes self-adaptation to adjust crossover probability and variation probability and carrys out improved genetic algorithms method, and utilizes the Materialized View after the genetic algorithm for solving problem conversion after improving to select problem.

The present invention can be used for the selection of Materialized Views in Data Warehouse, selects suitable view to carry out materialization, and the overhead of Materialized View is minimized.

Claims (1)

1. the Materialized View system of selection based on self-adapted genetic algorithm, is characterized in that, the method specifically comprises the following steps:

Step (1): the candidate's Materialized View collection based on same fact table and dimension table thereof of input tape view size and access frequency attribute, according to the packet attributes that in candidate's Materialized View SQL definition statement, group-by clause comprises, according to mapping ruler, be mapped to the node in multidimensional data lattice;

Mapping ruler is defined as follows: in SQL definition statement, comprise view that packet attributes is maximum corresponding to the root node in multidimensional data lattice, any two views of concentrating for candidate's Materialized View, two views are made as respectively a and b, if the packet attributes comprising in a view SQL definition statement is the proper subclass of the packet attributes that comprises in b view SQL definition statement, the corresponding node of a view is the child node of b view institute corresponding node so, in multidimensional data lattice model, except root node does not have father node, other node has and only has a direct father node;

Step (2): the overhead of calculating the Materialized View under multidimensional data lattice model;

If Q is the alternate view collection of the materialization that needs in multidimensional data lattice model, q is a view in Q, f _q(q) represent the corresponding enquiry frequency of q, M is Materialized View collection, QueryCost (Q, M) be the query cost of Materialized View, the maintenance costs that MaintenanceCost (M) is Materialized View, the storage overhead that Storage (M) is Materialized View, TotalCost (Q, M) be the overhead of Materialized View, avg _{q ∈ Q}{ f _q(q) } be the average of view query frequency, α is scale-up factor, TotalCost (Q, M)=QueryCost (Q, M)+α × (MaintenanceCost (M)+Storage (M)), wherein α=0.5 × avg _{q ∈ Q}{ f _q(q) };

Step (3): utilize binary coding to convert the candidate's Materialized View collection based on multidimensional data lattice model to genetic algorithm manageable 0-1 integer array, and using the size of inverse ideal adaptation degree in genetic algorithm of the Materialized View overhead of step (2) gained;

In multidimensional data lattice model, the numbering of node is corresponding to the index of array, the value of array element determines whether view is carried out to materialization, array element needs materialization for the view of this element index institute corresponding node of 1 expression, and array element does not need materialization for the view of this element index institute corresponding node of 0 expression;

Step (4): set initial population scale n, maximum iteration time max_number, according to the 0-1 integer array of step (3) gained, utilize random algorithm to produce n individuality as initial population;

Step (5): select operation, retain the high individuality of fitness in population by screening, eliminate the low individuality of fitness in population; If Population is population, k is the individuality in population, and Fit (x) is the corresponding fitness of individual x, and individual x is selected and enters follow-on Probability p _scomputing function as follows:

$p_s=\frac{\mathrm{Fit}\left(x\right)}{\underset{k\&Element;\mathrm{Population}}{\mathrm{\&Sigma;}}\mathrm{Fit}\left(k\right)}$

Step (6): calculate crossover probability p _c, the decision content flag_c between generating 0 to 1 for the each individuality in population is random, if the value of flag_c is less than or equal to p _c, this individuality will carry out 2 interlace operations, otherwise this individuality need not carry out 2 interlace operations, p _ccomputing formula as follows:

$p_c=\left\{\begin{array}{cc}1-1.2\&times;{\mathrm{Fit}}_{\mathrm{avg}}/({\mathrm{Fit}}_{\max }+{\mathrm{Fit}}_{\min })& 0<{\mathrm{Fit}}_{\mathrm{avg}}/({\mathrm{Fit}}_{\max }+{\mathrm{Fit}}_{\min })<0.5\\ 0.4& \mathrm{else}\end{array}\right.$

Fit _maxfor maximum adaptation degree individual in population, Fit _minfor minimum fitness individual in population, Fit _avgfor average fitness individual in population;

The process of 2 interlace operations is as follows:

Be that two individualities that will carry out interlace operation arrange two different point of crossing at random, two point of crossing are made as respectively point1 and point2, and the gene code between two individual point1 and point2 is exchanged and produces new individuality;

Step (7): calculate crossover probability p _m, the decision content flag_m between generating 0 to 1 for the each individuality in population is random, if the value of flag_m is less than or equal to p _m, this individuality will carry out mutation operation, otherwise this individuality need not carry out mutation operation, p _mcomputing formula as follows:

$p_m=\left\{\begin{array}{cc}0.1& {\mathrm{Fit}}_{\mathrm{avg}}/({\mathrm{Fit}}_{\max }+{\mathrm{Fit}}_{\min })>0.5\\ 0.2\&times;{\mathrm{Fit}}_{\mathrm{avg}}/({\mathrm{Fit}}_{\max }+{\mathrm{Fit}}_{\min })& \mathrm{else}\end{array}\right.$

The process of mutation operation is as follows:

For the individuality that will carry out mutation operation, determine at random a gene position, if the value of element is 0 in gene position, become 1, if the value of element is 1 in gene position, become 0;

Step (8): repeating step (5), (6), (7), reach the maximum iteration time max_number that step (4) sets to iterations, final is exactly the optimum solution of requirement for the individuality of fitness maximum in population, the view collection that after being decoded, output will select to carry out materialization exactly.

Images