CN106294380A - The querying method of data base and device - Google Patents
The querying method of data base and device Download PDFInfo
- Publication number
- CN106294380A CN106294380A CN201510254514.4A CN201510254514A CN106294380A CN 106294380 A CN106294380 A CN 106294380A CN 201510254514 A CN201510254514 A CN 201510254514A CN 106294380 A CN106294380 A CN 106294380A
- Authority
- CN
- China
- Prior art keywords
- inquiry
- parameter
- data base
- comparative result
- threshold
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2425—Iterative querying; Query formulation based on the results of a preceding query
Landscapes
- Engineering & Computer Science (AREA)
- Physics & Mathematics (AREA)
- Theoretical Computer Science (AREA)
- Mathematical Physics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides querying method and the device of a kind of data base, wherein, the method includes: obtain for indicating multiple parameters of the inquiry scene of inquiry data base: carry out integrated decision-making according to multiple parameters, and perform the inquiry to data base according to the query type corresponding with the result of decision.By the present invention, solve problem more single for the inquiry mode of data base in correlation technique, improve the search efficiency of data base.
Description
Technical field
The present invention relates to the communications field, in particular to querying method and the device of a kind of data base.
Background technology
For relational database query, the efficiency optimization way in correlation technique is to use suitable index, necessary
Time use SQL (Structured Query Language, referred to as SQL) annotate (such as Oracle
Hint) specified database query optimization mode is carried out.For some scenes, due to uneven, the Hint of data distribution
It not optimum way, cause the optimum state being unable to reach actual efficiency.
For in correlation technique for the problem that the inquiry mode of data base is more single, the most not yet propose have
The solution of effect.
Summary of the invention
Present invention is primarily targeted at querying method and the device that a kind of data base is provided, at least to solve phase
For problem that the inquiry mode of data base is more single in the technology of pass.
According to an aspect of the invention, it is provided the querying method of a kind of data base, including: obtain and be used for referring to
Show multiple parameters of the inquiry scene of inquiry data base: carry out integrated decision-making according to the plurality of parameter, and press
The inquiry to described data base is performed according to the query type corresponding with the result of decision.
Further, obtain for indicating multiple parameters of inquiry scene of inquiry data base to include: acquisition the
One presets that to limit the data volume of return in timing statistics in querying condition total with the data from described data base querying
First parameter of the ratio of amount；Obtain the data needing inquiry in second presets timing statistics in described data base
Index range and the second parameter of the ratio of the index range of all data in described data base；Obtain pre-the 3rd
If the 3rd parameter of the partitions of database in timing statistics in querying condition and the ratio of total subregion.
Further, carry out integrated decision-making according to the plurality of parameter, and look into according to corresponding with the result of decision
Ask type to perform the inquiry of described data base is included: obtain described first parameter respectively with the first default threshold
Value and Second Threshold compare after the first comparative result, and determine whether according to described first comparative result
Described data base querying SQL is split as many sub-SQL；After determining whether to be split as many sub-SQL,
Obtain parameter less between described second parameter and described 3rd parameter, and by described less parameter difference
Compare with the 3rd default threshold value and the 4th threshold value, obtain the second comparative result, and according to described second
Comparative result randomly chooses assigned indexes inquiry mode to carry out inquiring about or full table scan inquiry mode is inquired about.
Further, determine whether to be split as described data base querying SQL according to described first comparative result
Many sub-SQL include: when described first comparative result is less than or equal to described first threshold, be split as multiple
Sub-SQL；Or, when described first comparative result is more than or equal to described Second Threshold, it is not split as many height
SQL；Or, when described first comparative result is more than described first threshold and is less than described Second Threshold, with
Machine chooses whether to be split as many sub-SQL；Employing assigned indexes inquiry is determined according to described second comparative result
Or table scan inquiry includes entirely: when described second comparative result is less than or equal to described three threshold value, employing refers to
Determine search index；Or, when described second comparative result is more than or equal to four threshold values, use full table scan to look into
Ask；Or, when described second comparative result is more than described 3rd threshold value and is less than described four threshold value, at random
Select the inquiry of described assigned indexes or the inquiry of described full table scan.
Further, performing described data base according to the comparative result of described appointment parameter with predetermined threshold
Inquiry after, described method also includes: preserves and described inquires about the described of described database data for indicating
Multiple parameters, and use current query type to inquire about the time-consuming of described data base.
Further, described for indicating the plurality of parameter inquiring about described database data preserving, with
And use current query type inquire about described data base time-consuming after, described method also includes: obtain this
The inquiry corresponding with query type that ground preserves is time-consuming, and wherein, described query type includes: described full table is swept
Retouch inquiry and described assigned indexes is inquired about；Relatively multiple described inquiries are time-consuming, and adjust institute according to comparative result
State first threshold, described Second Threshold, described 3rd threshold value and the value of described 4th threshold value.
According to another aspect of the present invention, it is provided that the inquiry unit of a kind of data base, including: first obtain
Module, for obtaining multiple parameters of the inquiry scene for indicating inquiry data base: enquiry module, is used for
Carry out integrated decision-making according to the plurality of parameter, and perform institute according to the query type corresponding with the result of decision
State the inquiry of data base.
Further, described first acquisition module includes: the first acquiring unit, presets system for obtaining first
In timing, querying condition limits the data volume of return and the ratio of the data total amount from described data base querying
First parameter；Second acquisition unit, needs in described data base in second presets timing statistics for obtaining
The data directory scope of inquiry and the second parameter of the ratio of the index range of all data in described data base；The
Three acquiring units, for obtaining in the 3rd partitions of database preset in timing statistics in querying condition and total score
3rd parameter of the ratio in district.
Further, described enquiry module comprises determining that unit, be used for obtaining described first parameter respectively with
The first threshold preset and Second Threshold compare after the first comparative result, and compare according to described first
Result determines whether described data base querying SQL is split as many sub-SQL；Query unit, for really
After determining whether to be split as many sub-SQL, obtain between described second parameter and described 3rd parameter less
Parameter, and described less parameter is compared with the 3rd default threshold value and the 4th threshold value respectively, obtain
Second comparative result, and randomly choose assigned indexes inquiry mode according to described second comparative result and inquire about
Or table scan inquiry mode is inquired about entirely.
Further, described determine unit, be additionally operable at described first comparative result less than or equal to described first
During threshold value, it is split as many sub-SQL；Or, at described first comparative result more than or equal to described Second Threshold
Time, it is not split as many sub-SQL；Or, and it is less than more than described first threshold at described first comparative result
During described Second Threshold, randomly choose and whether be split as many sub-SQL；Described query unit, is additionally operable to
When described second comparative result is less than or equal to described three threshold value, use assigned indexes inquiry；Or, described
When second comparative result is more than or equal to four threshold values, full table scan is used to inquire about；Or, compare described second
Result more than described 3rd threshold value and less than described four threshold value time, randomly choose described assigned indexes inquiry or
Described full table scan is inquired about.
By the present invention, use the multiple parameters obtaining the inquiry scene for indicating inquiry data base, and then
The plurality of parameter is carried out integrated decision-making, and specifies data base's according to the query type that the result of decision is corresponding
Inquiry；Visible, in this embodiment by obtaining the multiple parameters inquiring about scene, it is not merely to use
Hint carrys out specified database to be inquired about, and can preferably adapt to scene by comprehensive decision of the plurality of parameter
Use the mode of inquiry optimized, solve in correlation technique the inquiry mode for data base more single
Problem, improves the search efficiency of data base.
Accompanying drawing explanation
Accompanying drawing described herein is used for providing a further understanding of the present invention, constitutes the part of the application,
The schematic description and description of the present invention is used for explaining the present invention, is not intended that the improper limit to the present invention
Fixed.In the accompanying drawings:
Fig. 1 is the flow chart of the querying method of data base according to embodiments of the present invention；
Fig. 2 is the inquiry unit structured flowchart of data base according to embodiments of the present invention；
Fig. 3 is the optional structured flowchart of inquiry unit one of the data base according to alternative embodiment of the present invention；
Fig. 4 is the optional structured flowchart of inquiry unit two of the data base according to alternative embodiment of the present invention；
Fig. 5 is the flow chart adapting to use the data query algorithm of scene according to alternative embodiment of the present invention.
Detailed description of the invention
It should be noted that in the case of not conflicting, the embodiment in the application and the feature in embodiment
Can be mutually combined.Describe the present invention below with reference to the accompanying drawings and in conjunction with the embodiments in detail.
Can be in the department of computer science of such as one group of computer executable instructions in the step shown in the flow chart of accompanying drawing
System performs, and, although show logical order in flow charts, but in some cases, permissible
To be different from the step shown or described by order execution herein.
Present embodiments providing the querying method of a kind of data base, Fig. 1 is data according to embodiments of the present invention
The flow chart of the querying method in storehouse, as it is shown in figure 1, the step of the method includes:
Step S102: acquisition is for indicating multiple parameters of the inquiry scene of inquiry data base:
Step S104: carry out integrated decision-making according to multiple parameters, and according to the inquiry class corresponding with the result of decision
Type performs the inquiry to data base.
By above-mentioned steps S102 in the present embodiment and step S104, acquisition is used to be used for indicating inquiry data
Multiple parameters of the inquiry scene in storehouse, and then the plurality of parameter is carried out integrated decision-making, and according to the result of decision pair
The query type answered specifies the inquiry to data base；Visible, in this embodiment by obtaining inquiry scene
Multiple parameters, be not merely to carry out specified database with hint to inquire about, comprehensive by the plurality of parameter
Decision can preferably adapt to scene and use the mode of the inquiry optimized, and solves in correlation technique for data base
The more single problem of inquiry mode, improve the search efficiency of data base.
It should be noted that the plurality of parameter related in the present embodiment can preset the corresponding value of acquisition, enter
And can in use can dynamically adjust.
And for the multiple ginsengs obtained for indicating the inquiry scene inquiring about data base related in the present embodiment
The mode of number, the optional embodiment at the present embodiment can be achieved in that
Step S11: obtain first preset the data volume limiting return in timing statistics in querying condition with from data
First parameter of the ratio of the data total amount of library inquiry；
Step S12: obtain second preset the data directory scope needing inquiry in timing statistics in data base with
Second parameter of the ratio of the index range of all data in data base；
Step S13: obtain the 3rd partitions of database preset in timing statistics in querying condition and total subregion it
3rd parameter of ratio.
Visible, three different parameters in inquiry scene can be obtained by above-mentioned steps S11 to step S13,
But these three parameters are intended merely to be illustrated, and are not intended that limitation of the invention；It is to say,
More parameter can be obtained, additionally, above-mentioned first presets timing statistics, the second default system according to practical situation
Between timing and the 3rd preset timing statistics in this alternative embodiment can equal can also be unequal.
In another optional embodiment of the present embodiment, for the present embodiment relate to according to multiple parameters
Carry out integrated decision-making, and perform the mode to the inquiry of data base according to the query type corresponding with the result of decision can
To be achieved in that
Step S21: obtain after the first parameter compares respectively with default first threshold and Second Threshold
One comparative result, and determine whether data base querying SQL is split as many height according to the first comparative result
SQL；
Step S22: after determining whether to split many sub-SQL, obtain the second parameter and the 3rd parameter it
Between less parameter, and less parameter is compared with the 3rd default threshold value and the 4th threshold value respectively,
To the second comparative result, and randomly choose assigned indexes inquiry mode according to the second comparative result and carry out inquiring about or entirely
Table scan inquiry mode is inquired about.
By above-mentioned steps S21 and S22, it is known that, by the first parameter, the second parameter and the 3rd parameter with
After predetermined threshold compares, it is determined whether data base be split, so determine for split or not
The data base split carries out assigned indexes inquiry or full table scan inquiry；Refined further right by which
The factor considered when data base inquires about, after considering multiple parameters of inquiry scene, it is possible to make
Must inquire about and more optimize.And for how above-mentioned steps S21 and S22 specifically perform the inquiry to data base,
Can be realized by following optional mode；
Foundation the first comparative result in above-mentioned steps S21 is determined whether to be split as data base many height
SQL can be achieved in that when the first comparative result is less than or equal to first threshold, is split as
Many sub-SQL；Or, when the first comparative result is more than or equal to Second Threshold, it is not split as many sub-SQL；
Or, the first comparative result more than first threshold and less than Second Threshold time, randomly choose whether be split as multiple
Sub-SQL；
And assigned indexes inquiry or full table are used for determining according to the second comparative result in above-mentioned steps S22
The mode of scan for inquiries can be achieved in that when the second comparative result is less than or equal to three threshold values,
Employing assigned indexes is inquired about；Or, when the second comparative result is more than or equal to four threshold values, use full table scan to look into
Ask；Or, when the second comparative result is more than the 3rd threshold value and is less than four threshold values, randomly choose assigned indexes and look into
Ask or full table scan inquiry.
Visible, in above-mentioned steps S21 and S22, give how one is optionally considering inquiry
After multiple parameters of scene, choose inquiry mode.
And after above-mentioned steps S22, i.e. according to specifying the parameter comparative result with predetermined threshold to perform logarithm
After the inquiry in storehouse, the method for the present embodiment can also include: preserves for indicating inquiry database data
Multiple parameters, and use current query type to inquire about the time-consuming of data base.It is to say, perform to look into every time
Parameter, predetermined threshold and inquiry time-consuming that inquiry uses all can be preserved after inquiry.
And for the data of above-mentioned preservation, preserving the multiple parameters for indicating inquiry database data, and
Use current query type inquiry data base time-consuming after, the method for the present embodiment can also include:
The inquiry corresponding with query type obtaining this locality preservation is time-consuming, and wherein, query type includes: full table is swept
Retouch inquiry and assigned indexes is inquired about；
Relatively multiple queries is time-consuming, and according to comparative result adjust first threshold, Second Threshold, the 3rd threshold value with
And the 4th value of threshold value.
It is to say, by the data preserved, compare after showing that optimum or most suitable inquiry mode is
Inquiry provide reliable foundation.
Additionally providing the inquiry unit of a kind of data base in the present embodiment, this device is used for realizing above-described embodiment
And optional embodiment, carried out repeating no more of explanation.As used below, term " module "
" unit " can realize the software of predetermined function and/or the combination of hardware.Although described by following example
Device preferably realizes with software, but hardware, or the realization of the combination of software and hardware is also also possible
It is contemplated.
Fig. 2 is the inquiry unit structured flowchart of data base according to embodiments of the present invention, as in figure 2 it is shown, this dress
Put and include: the first acquisition module 22, for obtaining multiple ginsengs of the inquiry scene for indicating inquiry data base
Number: enquiry module 24, is of coupled connections with acquisition module 22, for carrying out integrated decision-making according to multiple parameters,
And perform the inquiry to data base according to the query type corresponding with the result of decision.
Fig. 3 is the optional structured flowchart of inquiry unit one of the data base according to alternative embodiment of the present invention, such as Fig. 3
Shown in, this first acquisition module 22 includes: the first acquiring unit 32, presets timing statistics for obtaining first
Interior querying condition limits the data volume of return and the first parameter of the ratio of the data total amount from data base querying；The
Two acquiring units 34, are of coupled connections with the first acquiring unit 32, preset in timing statistics second for obtaining
Need in data base inquiry data directory scope index range of all data with data base ratio second
Parameter；3rd acquiring unit 36, is of coupled connections with second acquisition unit 34, presets system for obtaining the 3rd
3rd parameter of the partitions of database in querying condition and the ratio of total subregion in timing.
Fig. 4 is the optional structured flowchart of inquiry unit two of the data base according to alternative embodiment of the present invention, such as Fig. 4
Shown in, enquiry module 24 comprises determining that unit 42, for obtain the first parameter respectively with the first default threshold
Value and Second Threshold compare after the first comparative result, and determine whether data according to the first comparative result
Library inquiry SQL is split as many sub-SQL；Query unit 44, and determines that unit 42 is of coupled connections, and is used for
After determining whether to split many sub-SQL, obtain parameter less between the second parameter and the 3rd parameter,
And less parameter is compared with the 3rd default threshold value and the 4th threshold value respectively, obtain the second comparative result,
And foundation the second comparative result randomly chooses assigned indexes inquiry mode and carries out inquiring about or full table scan inquiry mode
Inquire about.
Alternatively, this determines unit 42, is additionally operable to, when the first comparative result is less than or equal to first threshold, tear open
It is divided into many sub-SQL；Or, when the first comparative result is more than or equal to Second Threshold, it is not split as many height
SQL；Or, when the first comparative result is more than first threshold and is less than Second Threshold, randomly choose and whether split
For many sub-SQL；
Alternatively, query unit 44, it is additionally operable to, when the second comparative result is less than or equal to three threshold values, use
Assigned indexes is inquired about；Or, when the second comparative result is more than or equal to four threshold values, use full table scan to inquire about；
Or, the second comparative result more than the 3rd threshold value and less than four threshold values time, randomly choose assigned indexes inquiry or
Full table scan inquiry.
Alternatively, the device that the present embodiment relates to can also include: according to specifying parameter and predetermined threshold
After comparative result performs the inquiry of data base, this device also includes: preserve module, with enquiry module 22
It is of coupled connections, preserves the multiple parameters for indicating inquiry database data, and use current query type
Inquiry data base's is time-consuming.
Alternatively, preserving the multiple parameters for indicating inquiry database data, and using current inquiry
Type queries data base time-consuming after, this device can also include: the second acquisition module, with preserve module coupling
Closing and connect, time-consuming for obtaining the inquiry corresponding with query type of local preservation, wherein, query type includes:
Full table scan inquiry and assigned indexes are inquired about；
Comparison module, is of coupled connections with the second acquisition module, is used for comparing multiple queries time-consuming, and according to comparing
Result adjusts first threshold, Second Threshold, the 3rd threshold value and the value of the 4th threshold value.
Below by the alternative embodiment of the present invention, the present invention is illustrated；
This alternative embodiment provides a kind of self adaptation and uses the data query algorithm of scene, by this optional enforcement
Example can allow data query automatically choose the mode of optimization with the change using scene.
This alternative embodiment have employed the type according to inquiry, the index range of inquiry, the tables of data subregion of inquiry
Size, the data volume estimation of inquiry return, with inquiry, the parameter that bar number limits, and after Comprehensive Assessment, selecting structure is looked into
Asking the optimal way of language (Structured Query Language, referred to as SQL), SQL optimal way includes
Press subregion index and split sub-SQL statement, hint selection etc., and come according to the time statistics that inquiry consumes every time
As the foundation choosing optimal way next time.
Below for the type of the inquiry related in this alternative embodiment, the index range of inquiry, the number of inquiry
Estimate that the parameter limited with inquiry return bar number illustrates according to the data volume of table partition size, inquiry；
Type for the middle inquiry of this alternative embodiment typically has real-time query and statistical query, real-time query pair
Time requirement is high and restricted to the data volume returned；Statistical query is low to time requirement, but often requires that system
Count whole data.Therefore, for both query types: real-time query is more laid particular stress on by index scanning, and unites
The full table scan that meter inquiry is more biased towards in subregion.
The selection of hint is affected bigger by the index range of the inquiry related to for this alternative embodiment, if
In the index range of inquiry has reached subregion, this indexes more than the 1/2 of total data, and needs to return completely
Foot condition total data time, be suitable for full table scan；If the index range of inquiry is less than this rope in subregion
When drawing the 1/5 of total data, it is suitable for by index scanning.Index range is accounted for the 1/5 to 1/2 of four corner
Scene, its efficiency is affected bigger by database server, storage device hardware & software configuration, then use
Which kind of mode, can further use the time statistics that inquiry consumed in the past to be used as choosing optimization next time
The foundation of mode.
Furthermore, it is necessary to explanation is to be difficult to the scene that judges, the data under such as time index for index range
Change in location under disappearance, location index, it is adaptable to the time statistics using historical query to consume is used as next
The secondary foundation choosing optimal way.
For splitting the scene of sub-SQL in this alternative embodiment, if there being return bar number to limit, then can
To be split as sub-SQL according to data volume estimation, concrete condition such as inquires about the data of 1 month, and inquiry is returned
Receipt number is limited to 100,000, and the data estimation of 1 month has 30,000,000, then can be split as 30
Inquire about over individual 1 day, so only just can meet requirement by the inquiry data of 1 day.
And for the inquiry mass data of 1 week in the present embodiment, opsition dependent collects, tables of data subregion is 1
My god, 7 subqueries of 1 day can be split as, it is to avoid the operating lag that 1 week big data summarization brings.
Fig. 5 is the flow chart adapting to use the data query algorithm of scene according to alternative embodiment of the present invention, as
Shown in Fig. 5, based on described above, the data query algorithm of the self adaptation use scene of this alternative embodiment is permissible
It is implemented by:
Step S502: obtaining query type, determine parameter A, wherein, parameter A=1 is real-time query, A=0
Inquire about for report form statistics；
Step S504: judge the index range of inquiry, determine parameter B, wherein, parameter B=search index model
Enclose/all scope of data, as tables of data preserves 1 month data, temporally index, inquire about 1 day data, then
B=1/30；
Step S506: judge whether subregion, and partition size, determine parameter C；Wherein, such as this tables of data
There is no subregion, then C=1, if having subregion and temporally subregion, partition size be 1 week, time in querying condition
Between scope be 1 day, then C=1/7；
Step S508: judge whether inquiry has return bar number to limit, and in querying condition in time range
Data volume is estimated, determines parameter D.The tables of data 1 day data amount such as inquired about is estimated as 1,000,000, and looks into
Ask return bar number and be limited to 100,000, then D=1/10；
Step S510: according to parameter D, splits sub-SQL, as D as D≤t1 >=t2 time do not split sub-SQL,
T1 < D < randomly chooses during t2 and whether splits sub-SQL；And then determine according to the value of value=min (B, C) and to be
The mode of no employing assigned indexes: assigned indexes inquiry during value≤k1；Full table scan inquiry during value >=k2；
K1 < value < during k2, uses whether assigned indexes at random；
It should be noted that value=min (B, C) this formula represents and take value less in B and C as value
Value；
Step S512: preserve inquiry uses every time parameter, t1, t2, k1, k2 and inquiry time-consuming；
Step S514: the difference according to the time-consuming data preserved, according to parameter A is separately added up, it is judged that t1,
The value of t2, k1, k2 is the need of adjustment.
By the way of this alternative embodiment, for the data statistics of big tables of data, fixing SQL need not be relied on
Meet efficiency optimization purpose, but take suitable optimal way according to actually used scene flexibly, meet industry
It is greatly improved search efficiency while business statistics.
These are only the alternative embodiment of the present invention, be not limited to the present invention, for this area
For technical staff, the present invention can have various modifications and variations.All within the spirit and principles in the present invention,
Any modification, equivalent substitution and improvement etc. made, should be included within the scope of the present invention.
Claims (10)
1. the querying method of a data base, it is characterised in that including:
Acquisition is for indicating multiple parameters of the inquiry scene of inquiry data base:
Carry out integrated decision-making according to the plurality of parameter, and hold according to the query type corresponding with the result of decision
The row inquiry to described data base.
Method the most according to claim 1, it is characterised in that obtain the inquiry for indicating inquiry data base
Multiple parameters of scene include:
Obtain first preset the data volume limiting return in timing statistics in querying condition with from described data
First parameter of the ratio of the data total amount of library inquiry；
Obtain need in described data base in second presets timing statistics inquiry data directory scope with
Second parameter of the ratio of the index range of all data in described data base；
Obtain the ratio at the 3rd partitions of database preset in timing statistics in querying condition and total subregion
3rd parameter.
Method the most according to claim 2, it is characterised in that carry out integrated decision-making according to the plurality of parameter,
And perform the inquiry of described data base is included according to the query type corresponding with the result of decision:
Obtain after described first parameter compares respectively with default first threshold and Second Threshold
One comparative result, and determine whether to split described data base querying SQL according to described first comparative result
For many sub-SQL；
After determining whether to be split as many sub-SQL, obtain described second parameter and described 3rd parameter
Between less parameter, and described less parameter is entered with the 3rd default threshold value and the 4th threshold value respectively
Row compares, and obtains the second comparative result, and randomly chooses assigned indexes according to described second comparative result and look into
Inquiry mode carries out inquiring about or full table scan inquiry mode is inquired about.
Method the most according to claim 3, it is characterised in that
Determine whether described data base querying SQL is split as many height according to described first comparative result
SQL includes: when described first comparative result is less than or equal to described first threshold, is split as many sub-SQL；
Or, when described first comparative result is more than or equal to described Second Threshold, it is not split as many sub-SQL；
Or, when described first comparative result is more than described first threshold and is less than described Second Threshold, select at random
Select and whether be split as many sub-SQL；
Determine that employing assigned indexes inquiry or full table scan inquiry include according to described second comparative result:
When described second comparative result is less than or equal to described three threshold value, use assigned indexes inquiry；Or, institute
When stating the second comparative result more than or equal to four threshold values, full table scan is used to inquire about；Or, described second
When comparative result is more than described 3rd threshold value and less than described four threshold value, randomly choose described assigned indexes
Inquiry or the inquiry of described full table scan.
Method the most according to claim 4, it is characterised in that according to specifying parameter and the ratio of predetermined threshold
After relatively result performs the inquiry to described data base, described method also includes:
Preserve described for indicating the plurality of parameter inquiring about described database data, and use current
Query type inquire about described data base time-consuming.
Method the most according to claim 5, it is characterised in that be used for indicating the described number of inquiry described in preserving
According to the plurality of parameter of database data, and current query type is used to inquire about the time-consuming of described data base
Afterwards, described method also includes:
The inquiry corresponding with query type obtaining this locality preservation is time-consuming, and wherein, described query type includes:
The inquiry of described full table scan and described assigned indexes are inquired about；
Relatively multiple described inquiries are time-consuming, and according to comparative result adjust described first threshold, described second
Threshold value, described 3rd threshold value and the value of described 4th threshold value.
7. the inquiry unit of a data base, it is characterised in that including:
First acquisition module, for obtaining multiple parameters of the inquiry scene for indicating inquiry data base:
Enquiry module, for carrying out integrated decision-making according to the plurality of parameter, and according to the result of decision pair
The query type answered performs the inquiry to described data base.
Device the most according to claim 7, it is characterised in that described first acquisition module includes:
First acquiring unit, presets the number limiting return in timing statistics in querying condition for acquisition first
The first parameter according to amount with the ratio of the data total amount from described data base querying；
Second acquisition unit, needs inquiry for obtaining in second presets timing statistics in described data base
Data directory scope and the second parameter of the ratio of the index range of all data in described data base；
3rd acquiring unit, divides the 3rd data base preset in timing statistics in querying condition for obtaining
3rd parameter of district and the ratio of total subregion.
Device the most according to claim 8, it is characterised in that described enquiry module includes:
Determine unit, enter with default first threshold and Second Threshold respectively for obtaining described first parameter
The first comparative result after going relatively, and determine whether described data base according to described first comparative result
Query SQL is split as many sub-SQL；
Query unit, for, after determining whether to be split as many sub-SQL, obtaining described second parameter
And parameter less between described 3rd parameter, and by described less parameter respectively with the 3rd default threshold
Value and the 4th threshold value compare, and obtain the second comparative result, and random according to described second comparative result
Select assigned indexes inquiry mode to carry out inquiring about or full table scan inquiry mode is inquired about.
Device the most according to claim 9, it is characterised in that
Described determine unit, be additionally operable to when described first comparative result is less than or equal to described first threshold,
It is split as many sub-SQL；Or, when described first comparative result is more than or equal to described Second Threshold, no
It is split as many sub-SQL；Or, at described first comparative result more than described first threshold and less than described
During Second Threshold, randomly choose and whether be split as many sub-SQL；
Described query unit, is additionally operable to when described second comparative result is less than or equal to described three threshold value,
Employing assigned indexes is inquired about；Or, when described second comparative result is more than or equal to four threshold values, use complete
Table scan is inquired about；Or, at described second comparative result more than described 3rd threshold value and less than described 4th threshold
During value, randomly choose the inquiry of described assigned indexes or the inquiry of described full table scan.
Priority Applications (2)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510254514.4A CN106294380B (en) | 2015-05-18 | 2015-05-18 | Database query method and device |
| PCT/CN2015/089194 WO2016183964A1 (en) | 2015-05-18 | 2015-09-08 | Database query method and apparatus |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510254514.4A CN106294380B (en) | 2015-05-18 | 2015-05-18 | Database query method and device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN106294380A true CN106294380A (en) | 2017-01-04 |
| CN106294380B CN106294380B (en) | 2021-02-12 |
Family
ID=57319249
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201510254514.4A Active CN106294380B (en) | 2015-05-18 | 2015-05-18 | Database query method and device |
Country Status (2)
| Country | Link |
|---|---|
| CN (1) | CN106294380B (en) |
| WO (1) | WO2016183964A1 (en) |
Citations (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6732085B1 (en) * | 2001-05-31 | 2004-05-04 | Oracle International Corporation | Method and system for sample size determination for database optimizers |
| US7058622B1 (en) * | 2001-12-26 | 2006-06-06 | Tedesco Michael A | Method, apparatus and system for screening database queries prior to submission to a database |
| CN101088082A (en) * | 2004-10-25 | 2007-12-12 | 英孚威尔公司 | Full text query and search systems and methods of use |
| CN102118264A (en) * | 2010-12-20 | 2011-07-06 | 大唐移动通信设备有限公司 | Method and device for generating performance report |
| CN102279849A (en) * | 2010-06-09 | 2011-12-14 | 中兴通讯股份有限公司 | Method and system for big data query |
| US20120005189A1 (en) * | 2010-06-30 | 2012-01-05 | Oracle International Corporation | Techniques for recommending alternative sql execution plans |
| CN103942289A (en) * | 2014-04-12 | 2014-07-23 | 广西师范大学 | Memory caching method oriented to range querying on Hadoop |
| CN103984695A (en) * | 2014-03-21 | 2014-08-13 | 华为技术有限公司 | Method and device for inquiring data in database |
| CN104021145A (en) * | 2014-05-16 | 2014-09-03 | 华为技术有限公司 | Mixed service concurrent access method and device |
| CN104077530A (en) * | 2013-03-27 | 2014-10-01 | 国际商业机器公司 | Method and device used for evaluating safety of data access sentence |
| CN104462434A (en) * | 2014-12-15 | 2015-03-25 | 北京国双科技有限公司 | Data inquiring method and device |
| CN104516981A (en) * | 2014-12-31 | 2015-04-15 | 上海格尔软件股份有限公司 | LDAP anonymous inquiry method not limited by quantity |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8352459B2 (en) * | 2009-07-23 | 2013-01-08 | International Business Machines Corporation | SQL queries for simple network management protocol management information base tables |
-
2015
- 2015-05-18 CN CN201510254514.4A patent/CN106294380B/en active Active
- 2015-09-08 WO PCT/CN2015/089194 patent/WO2016183964A1/en active Application Filing
Patent Citations (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6732085B1 (en) * | 2001-05-31 | 2004-05-04 | Oracle International Corporation | Method and system for sample size determination for database optimizers |
| US7058622B1 (en) * | 2001-12-26 | 2006-06-06 | Tedesco Michael A | Method, apparatus and system for screening database queries prior to submission to a database |
| CN101088082A (en) * | 2004-10-25 | 2007-12-12 | 英孚威尔公司 | Full text query and search systems and methods of use |
| CN102279849A (en) * | 2010-06-09 | 2011-12-14 | 中兴通讯股份有限公司 | Method and system for big data query |
| US20120005189A1 (en) * | 2010-06-30 | 2012-01-05 | Oracle International Corporation | Techniques for recommending alternative sql execution plans |
| CN102118264A (en) * | 2010-12-20 | 2011-07-06 | 大唐移动通信设备有限公司 | Method and device for generating performance report |
| CN104077530A (en) * | 2013-03-27 | 2014-10-01 | 国际商业机器公司 | Method and device used for evaluating safety of data access sentence |
| CN103984695A (en) * | 2014-03-21 | 2014-08-13 | 华为技术有限公司 | Method and device for inquiring data in database |
| CN103942289A (en) * | 2014-04-12 | 2014-07-23 | 广西师范大学 | Memory caching method oriented to range querying on Hadoop |
| CN104021145A (en) * | 2014-05-16 | 2014-09-03 | 华为技术有限公司 | Mixed service concurrent access method and device |
| CN104462434A (en) * | 2014-12-15 | 2015-03-25 | 北京国双科技有限公司 | Data inquiring method and device |
| CN104516981A (en) * | 2014-12-31 | 2015-04-15 | 上海格尔软件股份有限公司 | LDAP anonymous inquiry method not limited by quantity |
Non-Patent Citations (2)
| Title |
|---|
| CHENG ZENG 等: "Efficient Web Service Composition and Intelligent Search Based on Relational Database", 《2010 INTERNATIONAL CONFERENCE ON INFORMATION SCIENCE AND APPLICATIONS》 * |
| 史英杰 等: "云数据管理系统中查询技术研究综述", 《计算机学报》 * |
Also Published As
| Publication number | Publication date |
|---|---|
| WO2016183964A1 (en) | 2016-11-24 |
| CN106294380B (en) | 2021-02-12 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US7054852B1 (en) | Performance of join operations in parallel database systems | |
| US9158812B2 (en) | Enhancing parallelism in evaluation ranking/cumulative window functions | |
| US20180060389A1 (en) | Query optimization over distributed heterogeneous execution engines | |
| US20120166423A1 (en) | Collection of statistics for spatial columns or r-tree indexes | |
| US8150836B2 (en) | System, method, and computer-readable medium for reducing row redistribution costs for parallel join operations | |
| EP3373158B1 (en) | Data storage method and coordinator node | |
| US9390129B2 (en) | Scalable and adaptive evaluation of reporting window functions | |
| CN106407830A (en) | Detection method and device of cloud-based database | |
| CN104462434A (en) | Data inquiring method and device | |
| US20120197866A1 (en) | Optimizing processing of group-by queries featuring maximum or minimum equality conditions in a parellel processing system | |
| US9235621B2 (en) | Data-aware scalable parallel execution of rollup operations | |
| CN103714098B (en) | Method and system for carrying out subregion to database | |
| CN108182192A (en) | A kind of half-connection inquiry plan selection algorithm based on distributed data base | |
| US9378243B1 (en) | Predicate-based range set generation | |
| CN102385629B (en) | Application method of extending father and son model to network model in data warehouse | |
| WO2018107942A1 (en) | System and method of adaptively partitioning data to speed up join queries on distributed and parallel database systems | |
| WO2004095168A3 (en) | System and method for providing a territory management tool | |
| GB2482210A (en) | Configuration management system | |
| CN106294380A (en) | The querying method of data base and device | |
| CN107656943A (en) | Job enquiry method and server | |
| US20200004720A1 (en) | Data center management system and method | |
| CN103729419A (en) | Method for managing dynamic spectra on basis of query on data of database | |
| CN106162671B (en) | A kind of base station deployment location determining method and device | |
| CN103207835A (en) | Mass data storage method through self-adaptive Range partitions | |
| CN106126706A (en) | A kind of scope of resource control method of based role |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |