CN110119403A

CN110119403A - SQL optimization method, device, electronic equipment and readable storage medium storing program for executing

Info

Publication number: CN110119403A
Application number: CN201910266901.8A
Authority: CN
Inventors: 杨轩; 魏建钟; 刘强
Original assignee: Beijing Sankuai Online Technology Co Ltd
Current assignee: Beijing Sankuai Online Technology Co Ltd
Priority date: 2019-04-03
Filing date: 2019-04-03
Publication date: 2019-08-13
Anticipated expiration: 2039-04-03
Also published as: CN110119403B

Abstract

The invention discloses a kind of SQL optimization method, device, electronic equipment and readable storage medium storing program for executing.The method, comprising: acquisition has executed the execution duration of SQL and the second indicator atom data of the first indicator atom data and pending target SQL；Based on the execution duration for having executed SQL and the first indicator atom data, the principle of optimality parameter of the target SQL is obtained；According to the principle of optimality parameter and the second indicator atom data, the optimisation strategy of the target SQL is generated.

Description

SQL optimization method, device, electronic equipment and readable storage medium storing program for executing

Technical field

The present invention relates to database management technology fields, and in particular to a kind of SQL optimization method, device, electronic equipment and Readable storage medium storing program for executing.

Background technique

In database environment, especially in relevant database, SQL (Structured Query will use Language, structured query language) sentence.Moreover, database application is more and more with the arrival of big data era, place The data volume of reason is also increasing, processing logic also become increasingly complex, it is common application oneself be no longer several simple SQL languages The operation of sentence, large size application usually contain thousands of a SQL operations, how quickly to handle mass data and have become industry One problem.

SQL optimization in the prior art can only carry out artificial optimization for the situation of time-out, need after the completion of SQL is executed SQL is repeated to submit to execute；And the SQL being each performed may include some logics and model in terms of optimization Direction, but since the personnel for executing SQL are generally not modelling personnel, so related optimization direction can generally be ignored, and It cannot be referred to directly as the optimization of SQL.It can be seen that SQL optimization in the prior art be easy to cause the wasting of resources, and cause Global optimization is ineffective.

Summary of the invention

The present invention provides a kind of SQL optimization method, device, electronic equipment and readable storage medium storing program for executing, partly or entirely to solve The certainly relevant above problem of SQL optimization process in the prior art.

According to the present invention in a first aspect, providing a kind of SQL optimization method, comprising:

Obtain the execution duration for having executed SQL and the first indicator atom data and pending target SQL second is former Sub- achievement data；

Based on the execution duration for having executed SQL and the first indicator atom data, the excellent of the target SQL is obtained Change parameter of regularity；

According to the principle of optimality parameter and the second indicator atom data, the optimization plan of the target SQL is generated Slightly.

According to the second aspect of the invention, a kind of SQL optimization device is provided, comprising:

Indicator atom data acquisition module, for obtaining the execution duration and the first indicator atom data that have executed SQL, with And the second indicator atom data of pending target SQL；

Principle of optimality parameter acquisition module, for being referred to based on the execution duration for having executed SQL and first atom Data are marked, the principle of optimality parameter of the target SQL is obtained；

Optimisation strategy generation module, for according to the principle of optimality parameter and the second indicator atom data, life At the optimisation strategy of the target SQL.

According to the third aspect of the invention we, a kind of electronic equipment is provided, comprising:

Processor, memory and it is stored in the computer journey that can be run on the memory and on the processor Sequence, which is characterized in that the processor realizes SQL optimization method above-mentioned when executing described program.

According to the fourth aspect of the invention, provide a kind of readable storage medium storing program for executing, when the instruction in the storage medium by When the processor of electronic equipment executes, so that electronic equipment is able to carry out SQL optimization method above-mentioned.

SQL optimization method according to the present invention, the available execution duration for having executed SQL and the first indicator atom number According to and pending target SQL the second indicator atom data；Based on the execution duration for having executed SQL and described One indicator atom data obtain the principle of optimality parameter of the target SQL；According to the principle of optimality parameter and described second Indicator atom data generate the optimisation strategy of the target SQL.Thus it solves existing SQL optimization method and be easy to cause money Source waste, and the technical problem for causing global optimization ineffective.Achieve effectively avoid the wasting of resources while improve it is excellent Change the beneficial effect of effect.

The above description is only an overview of the technical scheme of the present invention, in order to better understand the technical means of the present invention, And it can be implemented in accordance with the contents of the specification, and in order to allow above and other objects of the present invention, feature and advantage can It is clearer and more comprehensible, the followings are specific embodiments of the present invention.

Detailed description of the invention

By reading the following detailed description of the preferred embodiment, various other advantages and benefits are common for this field Technical staff will become clear.The drawings are only for the purpose of illustrating a preferred embodiment, and is not considered as to the present invention Limitation.And throughout the drawings, the same reference numbers will be used to refer to the same parts.In the accompanying drawings:

Fig. 1 shows a kind of step flow chart of SQL optimization method according to an embodiment of the invention；

Fig. 2 shows a kind of step flow charts of SQL optimization method according to an embodiment of the invention；

Fig. 3 shows a kind of structural schematic diagram of SQL optimization device according to an embodiment of the invention；And

Fig. 4 shows a kind of structural schematic diagram of SQL optimization device according to an embodiment of the invention.

Specific embodiment

Exemplary embodiments of the present disclosure are described in more detail below with reference to accompanying drawings.Although showing the disclosure in attached drawing Exemplary embodiment, it being understood, however, that may be realized in various forms the disclosure without should be by embodiments set forth here It is limited.On the contrary, these embodiments are provided to facilitate a more thoroughly understanding of the present invention, and can be by the scope of the present disclosure It is fully disclosed to those skilled in the art.

Embodiment one

A kind of SQL optimization method provided in an embodiment of the present invention is discussed in detail.

Referring to Fig.1, a kind of step flow chart of SQL optimization method in the embodiment of the present invention is shown.

Step 110, the execution duration for having executed SQL and the first indicator atom data and pending target SQL are obtained The second indicator atom data.

It in embodiments of the present invention, can be pending in order to avoid SQL can must be optimized after execution Before target SQL is executed, the execution duration and the first indicator atom data and target of the SQL that acquisition history has executed respectively The second indicator atom data of SQL.

The SQL therein that executed may include the SQL that any available obtained history has executed, or can also be SQL of execution before current time in preset time period, etc..It can specifically be preset according to demand, to this The embodiment of the present invention is not limited.

Indicator atom data may include the key element information that can not be split for including, such as corresponding SQL in corresponding SQL In include tables of data, each tables of data be queried number, each tables of data is queried subregion, in SQL tables of data association time Whether each field is cited and quotes number, etc. in the data volume that includes in several, each tables of data, tables of data.Specifically Can be preset according to demand, this embodiment of the present invention is not limited.

In embodiments of the present invention, the execution duration and the first original for having executed SQL can be obtained by any available means Second indicator atom data of sub- achievement data and pending target SQL, are not limited this embodiment of the present invention. For example, each SQL is made of tables of data, field, association etc., table level, field can be carried out to SQL based on these characteristics Grade, association and the parsing of statistics aspect.

Therefore in embodiments of the present invention, it can be pre-configured with resolution rules according to demand, and then according to resolution rules pair It has executed SQL and target SQL is parsed.Wherein, main generation SQL tables of data, number used in is parsed by table level According to the access times of table, the subregion amount of tables of data, single partition amount of tables of data etc.；Parsing main generate in SQL by field makes Table, field and whether it is cited；Tables of data, each tables of data used in main generation SQL are parsed by association Between incidence relation, degree of incidence etc.；The main degree of incidence statistics generated in SQL, data volume statistics are parsed by statistics Deng.In embodiments of the present invention, the analysis mode that the resolution rules of concrete configuration are included be include above-mentioned any at least one Kind, this embodiment of the present invention is not limited.

It is available to obtain at least one and executed SQL moreover, in embodiments of the present invention, then then can accordingly obtain Each execution duration for having executed SQL is taken, also the sum of the execution duration of available whole SQL, it specifically can be according to demand It is preset, this embodiment of the present invention is not limited.

The execution duration for having executed SQL can be understood as accordingly having executed SQL from start to go at the end of executing when Between length.

Step 120, based on the execution duration for having executed SQL and the first indicator atom data, the mesh is obtained Mark the principle of optimality parameter of SQL.

It has been observed that in practical applications, each SQL is made of tables of data, field, association etc..In so difference SQL There may be identical parts for the tables of data for including, therefore based on each execution duration for having executed SQL and the first indicator atom Data can be estimated to obtain the executive condition of target SQL.Therefore, in embodiments of the present invention, can have been executed based on each The execution duration of SQL and the first indicator atom data, obtain the principle of optimality parameter of the target SQL.

The content that principle of optimality parameter therein specifically includes can be preset according to demand, real to this present invention Example is applied to be not limited.For example, it includes estimating to estimate the duration of target SQL execution duration that principle of optimality parameter, which can be set, Parameter, and interior optimization and the principle of optimality parameter of model optimization, etc. are carried out for target SQL.

Step 130, according to the principle of optimality parameter and the second indicator atom data, the target SQL is generated Optimisation strategy.

It has been executed after SQL obtains current principle of optimality parameter based on each, then it can be according to the principle of optimality Second indicator atom data of parameter and target SQL generate the optimisation strategy of target SQL.

Wherein, the particular content that optimisation strategy is included can also be preset according to demand, real to this present invention Example is applied also to be not limited.And the corresponding relationship between optimisation strategy and optimisation strategy can also be set in advance according to demand It sets, this embodiment of the present invention is also not limited.

For example, optimisation strategy may include fining optimization, the optimization of number of partitions for target SQL Field Inquiry, Expansion optimization in target SQL comprising tables of data, etc..Moreover, if principle of optimality parameter includes having been obtained based on having executed SQL The history subregion critical value obtained, if obtaining target then the second indicator atom data according to target SQL can be set Total inquiry subregion of SQL is greater than the history subregion critical value, then the optimization for reducing inquiry subregion amount can be generated for target SQL Strategy, etc..

SQL optimization method according to the present invention, the available execution duration for having executed SQL and the first indicator atom number According to and pending target SQL the second indicator atom data；Based on the execution duration for having executed SQL and described One indicator atom data obtain the principle of optimality parameter of the target SQL；According to the principle of optimality parameter and described second Indicator atom data generate the optimisation strategy of the target SQL.Thus raising while effectively avoiding the wasting of resources is achieved The beneficial effect of effect of optimization.

Embodiment two

Referring to Fig. 2, a kind of step flow chart of SQL optimization method in the embodiment of the present invention is shown.

Step 210, the execution duration for having executed SQL and the first indicator atom data and pending target SQL are obtained The second indicator atom data.

Step 220, according to the first indicator atom data and the execution duration for having executed SQL, described in acquisition The duration precompensation parameter of target SQL.

It has been observed that in practical applications, the same tables of data of target SQL may be contained in by having executed in SQL, in order to estimate The execution duration of target SQL can then refer to each execution duration for having executed SQL, obtain each data in target SQL respectively The execution duration of table, the association duration of tables of data, etc. duration precompensation parameter, so as to estimate to obtain the execution of target SQL Duration.It so then needs first according to each first indicator atom data for having executed SQL and executes duration, obtain institute State the duration precompensation parameter of target SQL.

Wherein, the content that duration estimation parameter specifically includes can be preset according to demand, real to this present invention Example is applied to be not limited.And the corresponding pass between each duration precompensation parameter and execution duration and the first indicator atom data System can also be preset according to demand, and the embodiment of the present invention is not limited.For example, can be set target SQL when Long wiping parameter of estimating includes the association between the execution duration for having executed each tables of data for including, every two tables of data in SQL Reference duration, etc. between duration, each table subregion.

Optionally, in embodiments of the present invention, the step 220 can further include:

Sub-step 221, according to the data volume for each tables of data for including in the first indicator atom data and total inquiry Duration is inquired in the number of partitions and the execution duration for having executed SQL, the single partition for obtaining each tables of data；

It has been observed that in embodiments of the present invention, each the first indicator atom number for having executed SQL can be obtained by parsing According to, and may include accordingly having executed the tables of data for including and each data in the first indicator atom data in SQL The data volume of table and total inquiry number of partitions.It so then can be according to each tables of data for including in the first indicator atom data Data volume and total inquiry number of partitions and the execution duration for having executed SQL, obtain the single partition of each tables of data Inquire duration.

Wherein, between single partition inquiry duration and the data volume of each tables of data and total inquiry number of partitions and corresponding SQL Execution duration between corresponding relationship can according to demand or experience etc. is preset, this embodiment of the present invention is not added To limit.

In practical applications, the inquiry duration of each table subregion of same tables of data is generally very nearly the same, because theoretically Each partition data magnitude of tables of data is not much different.And because the data volume gap of different data table is larger, different data The single partition inquiry duration of table is not generally identical.

Therefore, in embodiments of the present invention, a length of corresponding data table makes when the single partition inquiry of tables of data can be set With the ratio between the total inquiry number of partitions for including in duration and corresponding data table, wherein the use duration of tables of data can be phase The execution duration of SQL should have been executed to the data volume of corresponding data table in the corresponding product for having executed proportion in SQL, namely Tij=Ti* (dij/Di)/Nij.

Wherein, tij is that duration is inquired in the single partition of i-th of j-th of tables of data executed in SQL, and T has held for i-th The execution duration of row SQL, dij are the data volume of i-th of j-th of tables of data executed in SQL, and Di indicates to have executed for i-th The total amount of data for the total data table for including in SQL, Nij indicate total inquiry of i-th of j-th of tables of data executed in SQL The number of partitions.

Wherein, total inquiry number of partitions of each tables of data can be understood as corresponding data table when its affiliated SQL is executed, In the quantity of table subregion that is queried.For example, it is assumed that including 10 table subregions in tables of data tab1, but held in corresponding SQL When row, the table subregion being queried is 8, then always inquiring the number of partitions is 8 for tables of data tab1.

Sub-step 222, according to total in the execution duration for having executed SQL and the first indicator atom data Degree of incidence and total amount of data obtain the complexity duration for having executed SQL.

Furthermore, it is contemplated that there may be the tables of data for not executed SQL covering in pending target SQL.Such as It is inquired for newly-increased tables of data, and has executed SQL according to each then and can not obtain the single partition of the newly-increased tables of data Duration is inquired, so being based purely on the execution duration that single partition inquiry duration estimates target SQL, is easy to cause estimation results accurate The lower problem of property.

So in embodiments of the present invention, in order to improve the accuracy of estimation results, SQL complexity duration can be increased Calculating.Wherein, it has each executed the complexity duration of SQL and has accordingly executed the execution duration of SQL, and accordingly executed Corresponding relationship between the total correlation number and total amount of data of SQL can be preset according to demand, real to this present invention Example is applied to be not limited.

Optionally, in embodiments of the present invention, the sub-step 222 can further include:

Sub-step 2221, according to the total correlation in the execution duration for having executed SQL, the first indicator atom data Number and preset associated weights obtain the single association duration for having executed SQL；

Sub-step 2222, according to the total data in the execution duration for having executed SQL, the first indicator atom data Amount and preset inquiry weight obtain the ten thousand datas inquiry duration for having executed SQL；

Sub-step 2223 is associated with duration according to the single and ten thousand data inquires duration, obtains described executed The complexity duration of SQL.

Preferably, in embodiments of the present invention, can be set and executed the complexity duration of SQL includes single association duration Duration two parts are inquired with ten thousand datas, then then can be respectively according to the execution duration for having executed SQL, first original Total correlation number and preset associated weights in sub- achievement data obtain the single association duration for having executed SQL, And it according to the total amount of data in the execution duration for having executed SQL, the first indicator atom data and preset looks into Weight is ask, obtains the ten thousand datas inquiry duration for having executed SQL, and then duration and described ten thousand are associated with according to the single Data query duration obtains the complexity duration for having executed SQL.

Associated weights therein and inquiry weight can according to demand or experience etc. is preset, to this present invention Embodiment is not limited.In general, can be set associated weights and inquiry weight be greater than 0 and less than 1, and be associated with power Weight and inquiry weight may be the same or different.For example, it is 0.5, etc. that associated weights and inquiry weight, which can be set,.

Moreover, executed the single association duration of SQL and accordingly executed the execution duration and total correlation number of SQL, with And the corresponding relationship between preset associated weights, the ten thousand datas inquiry duration for having executed SQL have executed holding for SQL with corresponding Row duration and total amount of data and it is preset inquiry weight between corresponding relationship, complexity duration be associated with single duration and Corresponding relationship between ten thousand datas inquiry duration also can according to demand or experience etc. is preset, to this present invention Embodiment is not limited.

For example, the single association duration=corresponding execution duration * association power for having executed SQL for having executed SQL can be set Weight/corresponding total correlation the number for having executed SQL；The ten thousand datas inquiry duration=corresponding execution duration * inquiry power for having executed SQL Weight * 10000/ has accordingly executed the total amount of data of SQL；Complexity duration=single association+ten thousand data of duration inquires duration.

Step 230, according to the first indicator atom data, the Optimized Matching parameter of the target SQL is determined.

In practical applications, the executive condition referring to SQL is needed to determine its optimisation strategy, then then needing to be implemented corresponding SQL, and need to know the condition that SQL is optimized, and in embodiments of the present invention, it is corresponding in order to avoid having to carry out SQL is referred to the Optimized Matching parameter for the SQL estimation target SQL that history has executed.Wherein, Optimized Matching parameter is specifically wrapped The content contained can be preset according to demand, be not limited to this embodiment of the present invention.

For example, it includes being acquired based on each reference field accounting for having executed SQL that Optimized Matching parameter, which can be set, History reference field accounting threshold value, and the historical query partition threshold that the table number of partitions that has executed SQL based on each obtains, And model optimizations parameters such as the aiming field of model extension, etc. can be carried out for target SQL.

Optionally, in embodiments of the present invention, the step 230 can further include:

Sub-step 231 obtains the history reference field for having executed SQL according to the first indicator atom data The reference number of the degree of incidence of accounting, historical query subregion and tables of data and the field that is cited；

Wherein, history reference field accounting, can refer to it is any executed inquiry field final in SQL and accounted for accordingly executed SQL always inquires the ratio of field.For example, it is assumed that one has executed a total of 20 fields of SQL, but 5 are only used in query result A field, then the history reference field accounting for having executed SQL is 1/4.

Historical query subregion can refer to it is each executed the number of partitions that each table was queried in SQL, for example, it is a certain It executes comprising comprising 2 tables, wherein including 10 table subregions in table 1 in SQL, and the table subregion being queried is 8, is wrapped in table 2 Containing 10 table subregions, and the table subregion being queried is 5, but in the SQL implementation procedure, the table number of partitions that was queried It is 13, then the historical query subregion for having executed SQL can be then 13.

The degree of incidence of tables of data can be understood as each degree of incidence executed in SQL between tables of data；It is cited The reference number of field may include each reference number for having executed the field that is each cited in SQL.

Sub-step 232 obtains institute according to the history reference field accounting for having executed SQL and historical query subregion The SQL Optimal Parameters of target SQL are stated, the SQL Optimal Parameters include history reference field accounting threshold value and/or historical query Partition threshold；

It, then can root after acquiring each history reference field accounting for having executed SQL and historical query subregion According to the history reference field accounting for having executed SQL and historical query subregion, the SQL optimization ginseng of the target SQL is obtained Number.Wherein, pair between SQL Optimal Parameters and each history reference field accounting for having executed SQL and historical query subregion Should be related to be preset according to demand, be not limited to this embodiment of the present invention.

For example, all history reference field accountings for having executed SQL can be ranked up according to sequence from small to large, And then taking history reference field accounting of the history reference field accounting sequence at the first preset ratio position after sequence is to face Dividing value, it is less than the critical value that history reference field accounting threshold value, which is then arranged,.First preset ratio therein can be according to need It asks and is preset, this embodiment of the present invention is not limited.

For example, it is 80% that the first preset ratio, which can be set, if by each history reference field accounting for having executed SQL The history reference field accounting sequence obtained after being ranked up according to sequence from small to large are as follows: a2, a4, a10, a5, a7, a9, A3, a6, a8, a1, wherein ai indicates the 1st history reference field accounting for having executed SQL.Target so then can be set at this time The history reference field accounting threshold value of SQL is less than a6.

Correspondingly, all historical query subregions for having executed SQL can be ranked up according to sequence from big to small, into And taking historical query subregion of the historical query partition sequence at the second preset ratio position after sequence is critical value, is then set Setting historical query partition threshold is greater than the critical value.Second preset ratio therein can be preset according to demand, And second preset ratio can be identical as the first preset ratio above-mentioned, can also be different, this embodiment of the present invention is not added To limit.

For example, can be set the second preset ratio be 80%, if by each historical query subregion for having executed SQL according to The historical query partition sequence that sequence from big to small obtains after being ranked up are as follows: b3, b4, b7, b5, b9, b8, b6, b2, b10, B1, wherein bi indicates the 1st historical query subregion for having executed SQL.The history reference of target SQL so then can be set at this time Field accounting threshold value is greater than b2.

Sub-step 233, according to the degree of incidence of the tables of data and the reference number of field and preset excellent of being cited Change ratio obtains the model optimization parameter of the target SQL.

In practical applications, the degree of incidence for two associated data Table As and B in same SQL, between A and B It is more, and part the field C1 and C2 in tables of data B may be quoted by tables of data A more times, then in order to which that improves SQL executes effect Rate can then suggest being extended in tables of data A in tables of data B by field C1 and C2 that tables of data A more times are quoted.In the present invention It can include the above-mentioned target matrix for needing to carry out data extending, mesh to be expanded with Definition Model Optimal Parameters in embodiment Marking-up section, the source data table for obtaining aiming field, etc..

Wherein preset optimization ratio may include the optimization ratio based on degree of incidence and/or reference number, specifically It can be preset according to demand, this embodiment of the present invention is not limited.

For example, it is that the degree of incidence between two tables of data is more than or equal to n1, and the word that is cited that optimization ratio, which can be set, The reference number of section is more than or equal to n2, etc..So at this time if the degree of incidence between above-mentioned tables of data A and B is greater than etc. In n1, and tables of data A is more than or equal to n2 to the reference number of the field C1 in tables of data B, and small to the reference number of field C2 In n2, then it is tables of data B that then the model optimization parameter of available target SQL, which includes source data table, target matrix is number According to Table A, aiming field is field C1.Alternatively, also can be set optimization ratio be degree of incidence and/or reference number it is maximum before N3 field, etc..

Need to illustrate when, in embodiments of the present invention, step 230 can be performed simultaneously with step 220, can also be in step It is executed before rapid 220, but also a step in step 220 and 230 can be only carried out, can specifically carried out according to demand It presets, this embodiment of the present invention is not limited.

Step 240, according to the duration precompensation parameter and the second indicator atom data, the target SQL is obtained Estimate execution duration, and show in the target SQL implementing precondition and described estimate execution duration.

In practical applications, it is understood that there may be the execution duration of some SQL is too long, and without result and leads to the wasting of resources.That In embodiments of the present invention, in order to avoid above situation, can before target SQL execution, estimate its execute duration and It is prompted before execution, so as to avoid the SQL of time-out from repeating.So then can according to the duration precompensation parameter, And the second indicator atom data, obtain the target SQL estimates execution duration, and before target SQL execution Execution duration is estimated described in prompt.

It has been observed that duration precompensation parameter may include the execution duration for having executed each tables of data for including, every two in SQL The reference duration, etc. between association duration, each table subregion between a tables of data.And it is based on the second indicator atom data The incidence relation, etc. between the tables of data and tables of data for including in target SQL can also be acquired.It so then can be with The association between the execution duration of each tables of data for including in target SQL, tables of data is acquired based on duration precompensation parameter Duration, etc..And then can then estimate to obtain target SQL estimates execution duration.

Optionally, in embodiments of the present invention, the step 240 can further include:

Sub-step 241 obtains the target according to the first indicator atom data and the second indicator atom data By the of first quantity for having executed the tables of data that SQL is covered and the tables of data for being included in the target SQL in SQL Two quantity；

Sub-step 242, according to first quantity and second quantity, the duration for obtaining the target SQL, which is estimated, is Number；

Sub-step 243 inquires duration, single association duration and the duration according to the single partition and estimates and be Number, obtain the target SQL estimates execution duration, and shows in the target SQL implementing precondition and described estimate execution duration.

It has been observed that in practical applications, there may be not by each tables of data for having executed SQL and having covered in target SQL, It so then possibly can not accurately estimate to obtain the execution duration of corresponding data table based on duration precompensation parameter, to influence final obtain To estimate execute duration accuracy.Therefore, in embodiments of the present invention, also obtained while obtaining single partition inquiry duration Single association duration is obtained, to improve the finally obtained accuracy for estimating execution duration.

So be associated with single duration and be directed to balance single partition inquiry duration and estimates the significance level of execution duration, it can Coefficient is estimated so that a duration is arranged.It specifically can be according to the first indicator atom data and the second indicator atom number According to, obtain in the target SQL by it is described executed SQL cover tables of data the first quantity and the target SQL in wrapped Second quantity of the tables of data contained.

For example, it is assumed that including tables of data A and B in target SQL, and execute in SQL comprising tables of data A, D, F, then then The tables of data that SQL is covered has been executed as tables of data A by described in available target SQL, and the first quantity is 1, and the second number Amount is 2.

And then then can be according to first quantity and second quantity, the duration for obtaining the target SQL, which is estimated, is Number.Wherein, duration is estimated the corresponding relationship between coefficient and the first quantity and the second quantity and can either be passed through according to demand It tests etc. and to be preset, this embodiment of the present invention is not limited.For example, it is the first number that duration, which can be set, to estimate coefficient The ratio, etc. of amount and the second quantity.

It is last then can according to the single partition inquire duration, the single association duration and the duration estimate and be Number, obtain the target SQL estimates execution duration, and shows in the target SQL implementing precondition and described estimate execution duration.Its In, it estimates and executes duration and single partition inquiry duration, the single is associated with duration and the duration estimates pair between coefficient Should be related to can also according to demand or experience etc. is preset, and is also not limited to this embodiment of the present invention.

For example, if the execution duration * duration that all tables of data in execution duration=target SQL are estimated in setting estimates coefficient + complexity duration * (1- duration estimates coefficient), wherein the execution duration of each tables of data can be the table that corresponding data table includes The product of the single partition inquiry duration of number of partitions and corresponding data table, and complexity duration can be then data in target SQL The degree of incidence of table is associated with the product of duration with single.

It for example, it is assumed that having been executed SQL through parsing includes SQL1, and include tab1 and tab2 in SQL1, SQL1's holds A length of 50 minutes (m, minute) when row, total inquiry number of partitions of tab1 is total inquiry of 1, tab2 in the implementation procedure of SQL1 The data volume that the data volume that the number of partitions is 1, tab1 is 400000, tab2 is 100000；In target SQL comprising tab1, tab2 and Tab3, and the table number of partitions of tab1 is 2, data volume 400000, the table number of partitions of tab2 is 2, data volume 100000, The table number of partitions of tab3 is 1, data volume 100000, and preset associated weights are 0.5, and preset inquiry weight is 0.5, and And since SQL1 includes two tables of data, so the degree of incidence between two tables of data is 1, and in target SQL include three Degree of incidence between tables of data is 2.

So based on executed SQL1 then the single partition inquiry of available tab1 when a length of 400000/ (400000+ 100000) a length of 100000/ (400000+100000) * 50/1=10m, single when the single partition inquiry of * 50/1=40m, tab2 A length of 50*0.5/1=25m when association, ten thousand datas a length of 50*0.5*10000/ (400000+100000)=0.5m when inquiring.

And then then available tab1 estimates a length of 40*2=80m when execution for target SQL, tab2's estimate execution Shi Changwei 10*2=20m, it is 2/3 that duration, which estimates coefficient, and the association of target SQL a length of 25*2=50m when generating, data volume generates Shi Changwei (400000+100000+100000)/10000*0.5=25m, and then then when estimating execution of available target SQL A length of (80+20) * 2/3+ (50+25) * (1-2/3)=91.67m.

Step 250, according to the Optimized Matching parameter and the second indicator atom data, the target SQL is generated Optimizing Suggestions and display.

And after obtaining Optimized Matching parameter, then it can be according to according to the Optimized Matching parameter and described second Indicator atom data generate the Optimizing Suggestions of the target SQL and display.

It should be noted that this step can be performed simultaneously with aforementioned step 240, can also be held before step 240 Row can only execute step 240 either step 250, can specifically be preset according to demand, to this Inventive embodiments are not limited.

Wherein, Optimized Matching parameter and the second indicator atom data, the corresponding relationship between Optimizing Suggestions can With either experience etc. is preset according to demand, this embodiment of the present invention is not limited.

Optionally, in embodiments of the present invention, the step 250 can further include:

Sub-step 251, according to the second indicator atom data, obtain the target SQL reference field accounting and Inquire subregion；

Sub-step 252 generates institute according to the reference field accounting and inquiry subregion and the SQL Optimal Parameters State the SQL Optimizing Suggestions of target SQL；

Sub-step 253 generates the model optimization suggestion of the target SQL according to the model optimization parameter.

It has been observed that in embodiments of the present invention, Optimized Matching parameter may include SQL Optimal Parameters and model optimization ginseng Number.So then it can obtain the target SQL reference field accounting according to the second indicator atom data at this time and look into Subregion is ask, and then according to the reference field accounting and inquiry subregion and the SQL Optimal Parameters, generates the target The SQL Optimizing Suggestions of SQL.

It has been observed that SQL Optimal Parameters may include history reference field accounting threshold value and/or historical query partition threshold, It, can be raw for target SQL so at this time if the reference field accounting of target SQL meets history reference field accounting threshold value At " fining Field Inquiry " relevant SQL Optimizing Suggestions, and if the inquiry subregion of target SQL meets historical query subregion threshold Value can then generate " reducing inquiry subregion amount " relevant SQL Optimizing Suggestions for target SQL.In specific SQL Optimizing Suggestions Appearance can be preset according to demand, be not limited to this embodiment of the present invention.

And according to the model optimization parameter, generate the model optimization suggestion of the target SQL.

For example, if model optimization parameter include the target matrix above-mentioned for needing to carry out data extending, it is to be expanded Aiming field, the source data table for obtaining aiming field, etc., and source data table at this time is tables of data tab2, target data Table is tables of data tab1, and aiming field is field C1.It so then can be generated for target SQL and " extend tab2 table in tab1 In C1 field " model optimization suggestion.

Moreover, in embodiments of the present invention, according to the first indicator atom data and described SQL can also be executed Execution duration, obtain the duration precompensation parameter of the target SQL；And/or it according to the first indicator atom data, determines The Optimized Matching parameter of the target SQL.And the number according to each tables of data for including in the first indicator atom data According to amount and total inquiry number of partitions and the execution duration for having executed SQL, the single partition inquiry of each tables of data is obtained Duration；According to the total correlation number and sum in the execution duration for having executed SQL and the first indicator atom data According to amount, the complexity duration for having executed SQL is obtained.Referred to according to the execution duration for having executed SQL, first atom The total correlation number in data and preset associated weights are marked, the single association duration for having executed SQL is obtained；According to The execution duration for having executed SQL, the total amount of data in the first indicator atom data and preset inquiry weight, Obtain the ten thousand datas inquiry duration for having executed SQL；When being associated with duration and ten thousand data inquiry according to the single It is long, obtain the complexity duration for having executed SQL.Also, according to the first indicator atom data, obtain described executed The reference time of the history reference field accounting of SQL, the degree of incidence of historical query subregion and tables of data and the field that is cited Number；According to the history reference field accounting for having executed SQL and historical query subregion, the SQL for obtaining the target SQL is excellent Change parameter, the SQL Optimal Parameters include history reference field accounting threshold value and/or historical query partition threshold；According to described The reference number and preset optimization ratio of the degree of incidence of tables of data and the field that is cited, obtain the mould of the target SQL Type Optimal Parameters.So as to further increase effect of optimization.

In addition, in embodiments of the present invention, it can also be according to the duration precompensation parameter and second indicator atom Data, obtain the target SQL estimates execution duration, and shows in the target SQL implementing precondition and described estimate execution duration； And/or according to the Optimized Matching parameter and the second indicator atom data, generate the Optimizing Suggestions of the target SQL And it shows.Also, it according to the first indicator atom data and the second indicator atom data, obtains in the target SQL By the second number of first quantity for having executed the tables of data that SQL is covered and the tables of data for including in the target SQL Amount；According to first quantity and second quantity, the duration for obtaining the target SQL estimates coefficient；According to described single point Area's inquiry duration, single association duration and the duration estimate coefficient, obtain when estimating execution of the target SQL It is long, and show in the target SQL implementing precondition and described estimate execution duration.And it according to the second indicator atom data, obtains Take the reference field accounting and inquiry subregion of the target SQL；According to the reference field accounting and inquiry subregion, and The SQL Optimal Parameters generate the SQL Optimizing Suggestions of the target SQL；According to the model optimization parameter, the mesh is generated Mark the model optimization suggestion of SQL.It equally can be further improved SQL effect of optimization.

For embodiment of the method, for simple description, therefore, it is stated as a series of action combinations, but this field Technical staff should be aware of, and embodiment of that present invention are not limited by the describe sequence of actions, because implementing according to the present invention Example, some steps may be performed in other sequences or simultaneously.Secondly, those skilled in the art should also know that, specification Described in embodiment belong to preferred embodiment, the actions involved are not necessarily necessary for embodiments of the present invention.

Embodiment three

A kind of SQL optimization device provided in an embodiment of the present invention is discussed in detail.

Referring to Fig. 3, a kind of structural schematic diagram of SQL optimization device in the embodiment of the present invention is shown.

Indicator atom data acquisition module 310, for obtaining the execution duration and the first indicator atom number that have executed SQL According to and pending target SQL the second indicator atom data.

Principle of optimality parameter acquisition module 320, for based on the execution duration for having executed SQL and first atom Achievement data obtains the principle of optimality parameter of the target SQL.

Optimisation strategy generation module 330 is used for according to the principle of optimality parameter and the second indicator atom data, Generate the optimisation strategy of the target SQL.

Example IV

Referring to Fig. 4, a kind of structural schematic diagram of SQL optimization device in the embodiment of the present invention is shown.

Indicator atom data acquisition module 410, for obtaining the execution duration and the first indicator atom number that have executed SQL According to and pending target SQL the second indicator atom data.

Principle of optimality parameter acquisition module 420, for based on the execution duration for having executed SQL and first atom Achievement data obtains the principle of optimality parameter of the target SQL；

Wherein, in embodiments of the present invention, the principle of optimality parameter acquisition module 420, comprising:

Duration precompensation parameter acquisition submodule 421, for according to the first indicator atom data and described having executed The execution duration of SQL obtains the duration precompensation parameter of the target SQL；

Optionally, in embodiments of the present invention, the duration precompensation parameter acquisition submodule 421, comprising:

Duration acquiring unit is inquired in single partition, for according to each tables of data for including in the first indicator atom data Data volume and total inquiry number of partitions and the execution duration for having executed SQL, obtain the single partition of each tables of data Inquire duration；

Complexity duration acquiring unit, for having executed the execution duration of SQL and first atom refers to according to described The total correlation number and total amount of data in data are marked, the complexity duration for having executed SQL is obtained.

Optionally, in embodiments of the present invention, the complexity duration acquiring unit, can further include:

Single is associated with duration and obtains subelement, and for having executed the execution duration of SQL according to, first atom refers to The total correlation number in data and preset associated weights are marked, the single association duration for having executed SQL is obtained；

Ten thousand datas inquire duration and obtain subelement, for according to the execution duration for having executed SQL, first original Total amount of data and preset inquiry weight in sub- achievement data, when obtaining the ten thousand datas inquiry for having executed SQL It is long；

Complexity duration obtains subelement, for being associated with duration and ten thousand data inquiry duration according to the single, Obtain the complexity duration for having executed SQL.

And/or Optimized Matching parameter acquisition submodule 422, described in determining according to the first indicator atom data The Optimized Matching parameter of target SQL.

Optionally, in embodiments of the present invention, the Optimized Matching parameter acquisition submodule 422, can further include:

First foundation data capture unit, for according to the first indicator atom data, acquisition is described to have executed SQL's History reference field accounting, the degree of incidence of historical query subregion and tables of data and the reference number for the field that is cited；

SQL Optimal Parameters acquiring unit, for having executed the history reference field accounting of SQL and history is looked into according to described Subregion to be ask, the SQL Optimal Parameters of the target SQL are obtained, the SQL Optimal Parameters include history reference field accounting threshold value, And/or historical query partition threshold；

Model optimization parameter acquiring unit, for according to the degree of incidence of the tables of data and the reference time for the field that is cited Several and preset optimization ratio, obtains the model optimization parameter of the target SQL.

Optimisation strategy generation module 430 is used for according to the principle of optimality parameter and the second indicator atom data, Generate the optimisation strategy of the target SQL.

Optionally, in embodiments of the present invention, the optimisation strategy generation module 430, can further include:

It estimates and executes duration acquisition submodule 431, for being referred to according to the duration precompensation parameter and second atom Mark data, obtain the target SQL estimates execution duration, and the target SQL implementing precondition show it is described estimate execution when It is long；

Optionally, in embodiments of the present invention, described estimate executes duration acquisition submodule 431, can further include:

Second basic data acquiring unit, for according to the first indicator atom data and the second indicator atom number According to, obtain in the target SQL by it is described executed SQL cover tables of data the first quantity and the target SQL in wrapped Second quantity of the tables of data contained；

Duration estimates coefficient acquiring unit, for obtaining the target according to first quantity and second quantity The duration of SQL estimates coefficient；

Estimate execute duration acquiring unit, for according to the single partition inquire duration, the single association duration and The duration estimates coefficient, and obtain the target SQL estimates execution duration, and shows in the target SQL implementing precondition described Estimate execution duration.

And/or Optimizing Suggestions generate submodule 432, for according to the Optimized Matching parameter and second atom Achievement data generates the Optimizing Suggestions of the target SQL and display.

Optionally, in embodiments of the present invention, the Optimizing Suggestions generate submodule 432, can further include:

Third basic data acquiring unit, for obtaining drawing for the target SQL according to the second indicator atom data With field accounting and inquiry subregion；

SQL Optimizing Suggestions generation unit, for according to the reference field accounting and inquiry subregion and the SQL Optimal Parameters generate the SQL Optimizing Suggestions of the target SQL；

Model optimization suggests generation unit, for according to the model optimization parameter, the model for generating the target SQL to be excellent Change and suggests.

A kind of electronic equipment is also disclosed in the embodiment of the present invention, comprising:

A kind of readable storage medium storing program for executing is also disclosed in the embodiment of the present invention, when the instruction in the storage medium is set by electronics When standby processor executes, so that electronic equipment is able to carry out SQL optimization method above-mentioned.

For device embodiment, since it is basically similar to the method embodiment, related so being described relatively simple Place illustrates referring to the part of embodiment of the method.

Algorithm and display are not inherently related to any particular computer, virtual system, or other device provided herein. Various general-purpose systems can also be used together with teachings based herein.As described above, it constructs required by this kind of system Structure be obvious.In addition, the present invention is also not directed to any particular programming language.It should be understood that can use various Programming language realizes summary of the invention described herein, and the description done above to language-specific is to disclose this hair Bright preferred forms.

In the instructions provided here, numerous specific details are set forth.It is to be appreciated, however, that implementation of the invention Example can be practiced without these specific details.In some instances, well known method, structure is not been shown in detail And technology, so as not to obscure the understanding of this specification.

Similarly, it should be understood that in order to simplify the disclosure and help to understand one or more of the various inventive aspects, Above in the description of exemplary embodiment of the present invention, each feature of the invention is grouped together into single implementation sometimes In example, figure or descriptions thereof.However, the disclosed method should not be interpreted as reflecting the following intention: i.e. required to protect Shield the present invention claims features more more than feature expressly recited in each claim.More precisely, as following Claims reflect as, inventive aspect is all features less than single embodiment disclosed above.Therefore, Thus the claims for following specific embodiment are expressly incorporated in the specific embodiment, wherein each claim itself All as a separate embodiment of the present invention.

Those skilled in the art will understand that can be carried out adaptively to the module in the equipment in embodiment Change and they are arranged in one or more devices different from this embodiment.It can be the module or list in embodiment Member or component are combined into a module or unit or component, and furthermore they can be divided into multiple submodule or subelement or Sub-component.Other than such feature and/or at least some of process or unit exclude each other, it can use any Combination is to all features disclosed in this specification (including adjoint claim, abstract and attached drawing) and so disclosed All process or units of what method or apparatus are combined.Unless expressly stated otherwise, this specification is (including adjoint power Benefit require, abstract and attached drawing) disclosed in each feature can carry out generation with an alternative feature that provides the same, equivalent, or similar purpose It replaces.

In addition, it will be appreciated by those of skill in the art that although some embodiments described herein include other embodiments In included certain features rather than other feature, but the combination of the feature of different embodiments mean it is of the invention Within the scope of and form different embodiments.For example, in the following claims, embodiment claimed is appointed Meaning one of can in any combination mode come using.

Various component embodiments of the invention can be implemented in hardware, or to run on one or more processors Software module realize, or be implemented in a combination thereof.It will be understood by those of skill in the art that can be used in practice Microprocessor or digital signal processor (DSP) come realize some in SQL optimization equipment according to an embodiment of the present invention or The some or all functions of person's whole component.The present invention is also implemented as one for executing method as described herein Point or whole device or device programs (for example, computer program and computer program product).Such this hair of realization Bright program can store on a computer-readable medium, or may be in the form of one or more signals.It is such Signal can be downloaded from an internet website to obtain, and is perhaps provided on the carrier signal or is provided in any other form.

It should be noted that the above-mentioned embodiments illustrate rather than limit the invention, and ability Field technique personnel can be designed alternative embodiment without departing from the scope of the appended claims.In the claims, Any reference symbol between parentheses should not be configured to limitations on claims.Word "comprising" does not exclude the presence of not Element or step listed in the claims.Word "a" or "an" located in front of the element does not exclude the presence of multiple such Element.The present invention can be by means of including the hardware of several different elements and being come by means of properly programmed computer real It is existing.In the unit claims listing several devices, several in these devices can be through the same hardware branch To embody.The use of word first, second, and third does not indicate any sequence.These words can be explained and be run after fame Claim.

Claims

1. a kind of SQL optimization method characterized by comprising

The second atom for obtaining the execution duration for having executed SQL and the first indicator atom data and pending target SQL refers to Mark data；

Based on the execution duration for having executed SQL and the first indicator atom data, the optimization rule of the target SQL are obtained Then parameter；

According to the principle of optimality parameter and the second indicator atom data, the optimisation strategy of the target SQL is generated.

2. the method according to claim 1, wherein described based on the execution duration for having executed SQL and institute The step of stating the first indicator atom data, obtaining the principle of optimality parameter of the target SQL, comprising:

According to the first indicator atom data and the execution duration for having executed SQL, the duration of the target SQL is obtained Precompensation parameter；

And/or according to the first indicator atom data, determine the Optimized Matching parameter of the target SQL.

3. according to the method for claim 2 it is characterized in that, described according to the first indicator atom data and described The execution duration for having executed SQL, the step of obtaining the duration precompensation parameter of the target SQL, comprising:

According to the data volume for each tables of data for including in the first indicator atom data and total inquiry number of partitions and described The execution duration of SQL is executed, duration is inquired in the single partition for obtaining each tables of data；

According to the total correlation number and sum in the execution duration for having executed SQL and the first indicator atom data According to amount, the complexity duration for having executed SQL is obtained.

4. according to the method described in claim 3, it is characterized in that, the execution duration for having executed SQL according to, and Total correlation number and total amount of data in the first indicator atom data obtain the complexity duration for having executed SQL Step, comprising:

According to total correlation number in the execution duration for having executed SQL, the first indicator atom data and preset Associated weights obtain the single association duration for having executed SQL；

According to the total amount of data in the execution duration for having executed SQL, the first indicator atom data and preset look into Weight is ask, the ten thousand datas inquiry duration for having executed SQL is obtained；

Duration is associated with according to the single and ten thousand data inquires duration, obtains the complexity duration for having executed SQL.

5. according to the method described in claim 2, determining institute it is characterized in that, described according to the first indicator atom data The step of stating the Optimized Matching parameter of target SQL, comprising:

According to the first indicator atom data, the history reference field accounting for having executed SQL, historical query point are obtained The reference number of the degree of incidence of area and tables of data and the field that is cited；

According to the history reference field accounting for having executed SQL and historical query subregion, the SQL of the target SQL is obtained Optimal Parameters, the SQL Optimal Parameters include history reference field accounting threshold value and/or historical query partition threshold；

According to the reference number and preset optimization ratio of the degree of incidence of the tables of data and the field that is cited, institute is obtained State the model optimization parameter of target SQL.

6. according to the method described in claim 2, it is characterized in that, described according to the principle of optimality parameter and described second Indicator atom data, the step of generating the optimisation strategy of the target SQL, comprising:

According to the duration precompensation parameter and the second indicator atom data, when estimating execution of the target SQL is obtained It is long, and show in the target SQL implementing precondition and described estimate execution duration；

And/or according to the Optimized Matching parameter and the second indicator atom data, generate the optimization of the target SQL It is recommended that and showing.

7. according to the method described in claim 3, it is characterized in that, described according to the duration precompensation parameter and described Two indicator atom data, obtain the target SQL estimates execution duration, and shows in the target SQL implementing precondition described pre- Estimate the step of executing duration, comprising:

According to the first indicator atom data and the second indicator atom data, obtain in the target SQL by it is described Execute the second quantity of the tables of data for including in the first quantity and the target SQL of the tables of data of SQL covering；

According to first quantity and second quantity, the duration for obtaining the target SQL estimates coefficient；

Duration, single association duration and the duration are inquired according to the single partition and estimates coefficient, obtain the target SQL's estimates execution duration, and shows in the target SQL implementing precondition and described estimate execution duration.

8. according to the method described in claim 4, it is characterized in that, described according to the Optimized Matching parameter and described Two indicator atom data, the step of generating the Optimizing Suggestions of the target SQL and display, comprising:

According to the second indicator atom data, the reference field accounting and inquiry subregion of the target SQL are obtained；

According to the reference field accounting and inquiry subregion and the SQL Optimal Parameters, the SQL of the target SQL is generated Optimizing Suggestions；

According to the model optimization parameter, the model optimization suggestion of the target SQL is generated.

9. a kind of SQL optimizes device characterized by comprising

Indicator atom data acquisition module, for obtaining the execution duration and the first indicator atom data that have executed SQL, and to The second indicator atom data of the target SQL of execution；

Principle of optimality parameter acquisition module, for based on the execution duration for having executed SQL and the first indicator atom number According to obtaining the principle of optimality parameter of the target SQL；

Optimisation strategy generation module, for generating institute according to the principle of optimality parameter and the second indicator atom data State the optimisation strategy of target SQL.

10. a kind of electronic equipment characterized by comprising

Processor, memory and it is stored in the computer program that can be run on the memory and on the processor, It is characterized in that, the processor realizes that the SQL as described in any one of claim 1-8 is excellent when executing the computer program Change method.

11. a kind of readable storage medium storing program for executing, which is characterized in that when the instruction in the storage medium is held by the processor of electronic equipment When row, so that electronic equipment is able to carry out the SQL optimization method as described in any one of claim 1-8.