CN109145004A - A kind of method and device creating database index - Google Patents
A kind of method and device creating database index Download PDFInfo
- Publication number
- CN109145004A CN109145004A CN201810994069.9A CN201810994069A CN109145004A CN 109145004 A CN109145004 A CN 109145004A CN 201810994069 A CN201810994069 A CN 201810994069A CN 109145004 A CN109145004 A CN 109145004A
- Authority
- CN
- China
- Prior art keywords
- index
- identification information
- database
- sql statement
- name
- 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.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 31
- 230000006870 function Effects 0.000 claims description 25
- 238000001514 detection method Methods 0.000 claims description 7
- 238000001914 filtration Methods 0.000 claims description 6
- 238000012545 processing Methods 0.000 abstract description 5
- 238000012986 modification Methods 0.000 description 6
- 230000004048 modification Effects 0.000 description 6
- 238000010586 diagram Methods 0.000 description 4
- 238000003860 storage Methods 0.000 description 4
- 230000002411 adverse Effects 0.000 description 3
- 230000009286 beneficial effect Effects 0.000 description 3
- 238000013461 design Methods 0.000 description 3
- 238000005516 engineering process Methods 0.000 description 3
- 230000006399 behavior Effects 0.000 description 1
- 210000004556 brain Anatomy 0.000 description 1
- 238000004590 computer program Methods 0.000 description 1
- 238000012790 confirmation Methods 0.000 description 1
- 238000012217 deletion Methods 0.000 description 1
- 230000037430 deletion Effects 0.000 description 1
- 238000000151 deposition Methods 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 230000005611 electricity Effects 0.000 description 1
- 239000000835 fiber Substances 0.000 description 1
- 238000007689 inspection Methods 0.000 description 1
- 230000014759 maintenance of location Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 238000002360 preparation method Methods 0.000 description 1
- 238000011160 research Methods 0.000 description 1
- 238000012827 research and development Methods 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 239000002699 waste material Substances 0.000 description 1
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The embodiment of the invention provides a kind of method and devices for creating database index, this method comprises: determining the query condition information and field information in the SQL statement when receiving the inquiry instruction of any structure query language SQL statement；According to the query condition information, the querying condition field in the field information is determined, and determine the match query mode of the querying condition field；According to the querying condition field and the match query mode, the first index name of the database index of the SQL statement to be created is determined；Based on first index name, the database index of the SQL statement is created.By means of the invention it is possible to quickly and accurately be adjusted correspondingly operation to each first index name, and the earth improves the search efficiency of database and the oncurrent processing ability of data；Avoid because of adjustment index name and structure, lead to have inquiry failure, cause data accident there is a situation where.
Description
Technical field
The present invention relates to field of computer technology more particularly to a kind of method and devices for creating database index.
Background technique
Index in database is like the directory index in dictionary, and user can be helped to find faster accordingly
Data information.With the rapid development of computer technology, existing application often possesses huge user volume, therefore database
In possess huge data volume, establish efficient index and be necessary to increase the speed of inquiry and modification.But database rope
Drawing Naming conventions is always that research and development of software personnel, database administrator are difficult to unified problem.In the prior art, with relationship type number
For base management system MySQL database, index naming method is usually " index prefix _ column name " form, on the one hand, when depositing
In the approximate index name of two contents, database administrator can not determine whether to delete one of index, therefore,
It usually will cause index redundancy, disk space caused to waste, and reduce the oncurrent processing ability of database under high load；It is another
Aspect, when executing with relevant operation is indexed, such as newly index, increase new column to have index, deletion is indexed and is operated, count
It needs that relevant operation could be executed after confirming repeatedly according to library manager, but still not can avoid because index association can not be traced back to
All SQL and the case where be unable to adjust index name and structure, database performance and disk size caused to adversely affect
Occur, while also not can avoid because of adjustment index name and structure, leads to have inquiry failure, the feelings for causing data accident to occur
Condition.
Summary of the invention
The embodiment of the present invention provide it is a kind of create database index method and device, with by maximize index information come
Realize the efficient inquiry of database.
On the one hand, the embodiment of the invention provides a kind of methods for creating database index, comprising:
When receiving the inquiry instruction of any structure query language SQL statement, the inquiry in the SQL statement is determined
Conditional information and field information；
According to the query condition information, the querying condition field in the field information is determined, and determine the inquiry
The match query mode of condition field；
According to the querying condition field and the match query mode, the data of the SQL statement to be created are determined
First index name of library index；
Based on first index name, the database index of the SQL statement is created.
On the other hand, the embodiment of the invention provides a kind of devices for creating database index, comprising:
First determining module, for determining institute when receiving the inquiry instruction of any structure query language SQL statement
State the query condition information and field information in SQL statement；
Second determining module, for determining the querying condition word in the field information according to the query condition information
Section, and determine the match query mode of the querying condition field；
Third determining module, for determining to be created according to the querying condition field and the match query mode
First index name of the database index of the SQL statement；
First creation module creates the database index of the SQL statement for being based on first index name.
Above-mentioned technical proposal has the following beneficial effects: by means of the invention it is possible to quickly and accurately determine SQL query language
The match query mode of querying condition field and querying condition field in sentence, for the database of the subsequent creation SQL statement
The index name of index provides necessary premise guarantee；It realizes according to querying condition field and match query mode, quickly,
The first index name of the database index of the SQL statement is accurately determined, and the SQL is created according to the first index name
The database index of sentence further quickly and accurately can be adjusted correspondingly behaviour to each first index name
Make, and the earth improves the search efficiency of database and the oncurrent processing ability of data；It avoids and is closed because index can not be traced back to
Connection all SQL and be unable to adjust index name and structure, the feelings for causing database performance and disk size to adversely affect
Condition occurs, and avoids because of adjustment index name and structure, leads to have inquiry failure, the feelings for causing data accident to occur
Condition.
Detailed description of the invention
In order to more clearly explain the embodiment of the invention or the technical proposal in the existing technology, to embodiment or will show below
There is attached drawing needed in technical description to be briefly described, it should be apparent that, the accompanying drawings in the following description is only this
Some embodiments of invention for those of ordinary skill in the art without creative efforts, can be with
It obtains other drawings based on these drawings.
Fig. 1 is a kind of method flow diagram for creating database index in one embodiment of the invention；
Fig. 2 is a kind of apparatus structure schematic diagram for creating database index in one embodiment of the invention.
Specific embodiment
Following will be combined with the drawings in the embodiments of the present invention, and technical solution in the embodiment of the present invention carries out clear, complete
Site preparation description, it is clear that described embodiments are only a part of the embodiments of the present invention, instead of all the embodiments.It is based on
Embodiment in the present invention, it is obtained by those of ordinary skill in the art without making creative efforts every other
Embodiment shall fall within the protection scope of the present invention.
As shown in Figure 1, for the method flow diagram for creating database index a kind of in the embodiment of the present invention, comprising:
101, it when receiving the inquiry instruction of any structure query language SQL statement, determines in the SQL statement
Query condition information and field information；
102, according to the query condition information, the querying condition field in the field information is determined, and described in determination
The match query mode of querying condition field；
103, according to the querying condition field and the match query mode, the SQL statement to be created is determined
First index name of database index；
104, it is based on first index name, creates the database index of the SQL statement.
Further, the query condition information and field information in the determination SQL statement, comprising:
Grammer and keyword based on sql like language, parse the SQL statement, determine the querying condition in the SQL statement
Information and field information.
Optionally, further includes:
Predetermined index naming rule；
Wherein, the index naming rule includes: that different index types corresponds to different type identification information and difference
Match query mode correspond to different matching identification information；
Wherein, the match query mode includes equivalent matching, commensurate in scope, sequence matching and filters in matching at least
Any matching way.
Further, described according to the querying condition field and the match query mode, it determines to be created described
First index name of the database index of SQL statement, comprising:
Determine the index type of the database index of the SQL statement to be created, it is true based on the index naming rule
Fixed type identification information corresponding with the index type；
According to the fixed match query mode, based on index naming rule determination and the match query side
The corresponding matching identification information of formula；
According to the querying condition field, the type identification information and the matching identification information, determine to be created
First index name of the database index of the SQL statement.
Optionally, further includes:
Connected by scheduled concatenation character type identification information in first index name, matching identification information and
Querying condition field.
Optionally, be based on first index name described, the step of creating the database index of the SQL statement it
Before, further includes:
Judge whether existing comprising the querying condition field, the type identification information and the matching in database
Second index name of identification information；
If it exists, when second index name it is corresponding index execute query function do not include it is to be created described in
When the query function of the database index of SQL statement, it is based on first index name, creates the database of the SQL statement
Index.
Optionally, further includes:
Based on the index name of each index in database, with each rope in scheduled detection frequency detecting database
Draw title；
When detect at least two index names there are identical querying condition field, identical type identification information and
When identical matching identification information, retain at least one index name at least two index name；
Wherein, described to detect that there are identical querying condition fields, identical type at least two index names
When identification information and identical matching identification information, further includes:
Modify the index name of at least one of described at least two index name.
As shown in Fig. 2, for a kind of apparatus structure schematic diagram for creating database index, comprising:
First determining module 21, for determining when receiving the inquiry instruction of any structure query language SQL statement
Query condition information and field information in the SQL statement；
Second determining module 22, for determining the querying condition in the field information according to the query condition information
Field, and determine the match query mode of the querying condition field；
Third determining module 23, for determining to be created according to the querying condition field and the match query mode
The SQL statement database index the first index name；
First creation module 24 creates the database index of the SQL statement for being based on first index name.
Further, first determining module, is specifically used for
Grammer and keyword based on sql like language, parse the SQL statement, determine the querying condition in the SQL statement
Information and field information.
Optionally, further includes:
Predetermined module is used for predetermined index naming rule；
Wherein, the index naming rule includes: that different index types corresponds to different type identification information and difference
Match query mode correspond to different matching identification information；
Wherein, the match query mode includes equivalent matching, commensurate in scope, sequence matching and filters in matching at least
Any matching way.
Further, the third determining module, is specifically used for
Determine the index type of the database index of the SQL statement to be created, it is true based on the index naming rule
Fixed type identification information corresponding with the index type；
According to the fixed match query mode, based on index naming rule determination and the match query side
The corresponding matching identification information of formula；
According to the querying condition field, the type identification information and the matching identification information, determine to be created
First index name of the database index of the SQL statement.
Optionally, further includes:
Link block, for connected by scheduled concatenation character the type identification information in first index name,
Matching identification information and querying condition field.
Optionally, further includes:
Judgment module, it is whether existing comprising the querying condition field, the type identification in database for judging
Second index name of information and the matching identification information；
Second creation module, for if it exists, indexing the query function of execution not when second index name is corresponding
When the query function of the database index comprising the SQL statement to be created, it is based on first index name, described in creation
The database index of SQL statement.
Optionally, further includes:
Detection module, for the index name based on each index in database, with scheduled detection frequency detecting number
According to each index name in library；
Reservation module, for when detecting that there are identical querying condition fields, identical at least two index names
When type identification information and identical matching identification information, retain at least one index name at least two index name
Claim；
Wherein, the reservation module is also used to
When detect at least two index names there are identical querying condition field, identical type identification information and
When identical matching identification information, the index name of at least one of described at least two index name is modified.
Above-mentioned technical proposal of the embodiment of the present invention has the following beneficial effects: by means of the invention it is possible to quickly and accurately really
The match query mode of the querying condition field and querying condition field in SQL query statement is determined, for the subsequent creation SQL language
The index name of the database index of sentence provides necessary premise guarantee；It realizes according to querying condition field and match query
Mode quickly and accurately determines the first index name of the database index of the SQL statement, and according to the first index name
The database index of the SQL statement is created, further, is greatly improved through first index name to inquiry language
The efficiency that sentence is inquired, meanwhile, it is capable to operation quickly and accurately is adjusted correspondingly to each first index name, and
The earth improves the search efficiency of database and the oncurrent processing ability of data；It avoids because the associated institute of index can not be traced back to
The case where having SQL and be unable to adjust index name and structure, database performance and disk size is caused to adversely affect,
And avoid because of adjustment index name and structure, lead to have inquiry failure, cause data accident there is a situation where.
Above-mentioned technical proposal of the embodiment of the present invention is described in detail below in conjunction with application example:
Application example of the present invention has aimed at maximization index information, to realize the efficient inquiry of database.
As shown in Figure 1, for example, when receiving the inquiry instruction of SQL statement, SQL statement is such as in MySQL database
" select name from user where age=10 ", determines the query condition information in the SQL statement, is " age=
10 " and field information, it is " name ", " age "；According to query condition information " age=10 ", the SQL statement to be created is determined
Database index match query mode and field information " name ", " age " in querying condition field be " age ", and root
According to field information and match query mode, the first index name of the database index of the SQL statement to be created is determined；With
Afterwards, according to querying condition field " age " and match query mode, the database index of the SQL statement is created.
In one possible implementation, determined described in step 101 query condition information in the SQL statement and
The step of field information, comprising: grammer and keyword based on sql like language parse the SQL statement, determine the SQL statement
In field information and query condition information.
For example, in MySQL database, when receiving the inquiry instruction of SQL statement, SQL statement such as " select
Name from user where age=10 ", grammer and keyword based on sql like language parse the SQL statement, it may be determined that
Query condition information in the SQL statement is " age=10 " and field information, is " name ", " age ".
In one possible implementation, this method further include: predetermined index naming rule.
Wherein, the index naming rule includes: that different index types corresponds to different type identification information and difference
Match query mode correspond to different matching identification information.
Wherein, the match query mode includes equivalent matching, commensurate in scope, sequence matching and filters in matching at least
Any matching way.
For example, in MySQL database, the naming rule of predetermined index are as follows: different index types corresponds to different classes
Type identification information and different match query modes correspond to different matching identification information；Wherein, index type includes common rope
Draw, unique index and the index that index name can not be modified, different index types corresponds to different type identification informations such as, general
Logical to index corresponding type identification information as " i ", the corresponding type identification information of unique index is " u ", can not modify index name
The corresponding type identification information of index be referred to as " f "；Wherein, match query mode includes equivalent matching, commensurate in scope, sequence
Match and filter at least any matching way in matching, if match query mode only equivalent can match, can also look into simultaneously
Matching way and commensurate in scope are ask, can also simultaneously be equivalent matching, commensurate in scope and sequence matching etc., different match queries
Mode corresponds to different matching identification information such as, and it is " 1 " that equivalence, which matches corresponding matching identification information, and commensurate in scope corresponding
With identification information be " 2 ", the corresponding matching identification information of sequence matching is " 3 ", and filtering matches corresponding matching identification information and is
“4”。
It should be noted that " i " is that " index " writes a Chinese character in simplified form in the embodiment of the present invention, simple index is indicated, it is also possible to
Other characters such as " idx ", " index " replace, it is not limited here；" u " is that " unique " writes a Chinese character in simplified form, and indicates unique index, can also
With " uidx ", " uni ", " unique " etc., other characters are replaced, it is not limited here；" f " is that " force index " writes a Chinese character in simplified form, table
Show that the index is forced or suggests in some or multiple queries to use, index name modification may cause inquiry failure, play police
It is shown as using, it is also possible to which other characters such as " force ", " fixed " replace, it is not limited here；Though can be in the embodiment of the present invention
Illustrate for specific matching identification information, the replacement of other characters also can be used, it is therefore an objective to which identity column plays work in the index
Type, it is not limited here.
Through this embodiment, it realizes according to the customized demand of data base administrator and determines the SQL to be created
First index name of the database index of sentence, meanwhile, by the naming rule of predetermined index, enable to the index of name
Comprising effective information needed for user in title, provided for operations such as the subsequent merging for realizing index, index column sequence adjustment
Necessary premise guarantee.
In one possible implementation, according to the querying condition field and the match query described in step 103
Mode determines the first index name of the database index of the SQL statement to be created, comprising: determines to be created described
The index type of the database index of SQL statement determines class corresponding with the index type based on the index naming rule
Type identification information；According to the fixed match query mode, based on index naming rule determination and the inquiry
With the corresponding matching identification information of mode；According to the querying condition field, the type identification information and the matching identification
Information determines the first index name of the database index of the SQL statement to be created.
Further, the type identification information in first index name is connected by scheduled concatenation character, is matched
Identification information and querying condition field.
For example, connecting example, in MySQL database, when receiving the inquiry instruction of SQL statement, SQL statement is such as
" select name from user where age=10 ", parses the SQL statement, it may be determined that the inquiry item in the SQL statement
Part information is " age=10 " and field information, is " name ", " age ", can determine according to query condition information " age=10 "
The querying condition field of the SQL statement is " age ", it is later determined that the match query mode of querying condition field " age " is equivalence
Matching；The index type for determining the database index of the SQL statement to be created is simple index, is named based on scheduled index
Rule determines that type identification information corresponding with " simple index " index type is " i "；According to fixed match query mode
" equivalence matching " is believed based on the determining matching identification corresponding with match query mode " equivalence matches " of scheduled index naming rule
Breath is " 1 "；Based on type identification information " i " and matching identification information " 1 ", and according to querying condition field " age ", by predetermined
Concatenation character, as " _ " connection the first index name in type identification information " i ", matching identification information " 1 " and querying condition
Field " age ", it may be determined that the first index name of the database index of the SQL statement to be created, for example " i_1_age
(age) " or " i_1age (age) ", wherein the mode of Connection operator can be made by oneself according to the use habit of data base administrator
Justice.
It should be noted that those skilled in the art are it can be appreciated that the database index of SQL statement to be created is looked into
Matching way is ask, can be determined according to the working experience of data base administrator or research staff, or according to looking into database
Ask function to determine, it is not limited here.
In one possible implementation, it is based on first index name described in the step 104, creates the SQL
Before the step of database index of sentence, further includes: judge in database whether it is existing comprising the querying condition field,
Second index name of the type identification information and the matching identification information；If it exists, when second index name pair
When the query function for the database index that the query function that the index answered executes does not include the SQL statement to be created, it is based on
First index name, creates the database index of the SQL statement.
Wherein, when the query function that the corresponding index of second index name executes includes the SQL language to be created
When the query function of the database index of sentence, using second index name as the name of the database index of the SQL statement
Claim.
For example, in MySQL database, however, it is determined that the first index name of the database index of the SQL statement to be created
Claim, for example " i_1_age (age) ", then, judge in MySQL database whether it is existing comprising querying condition field " age ",
Second index name of type identification information " i " and matching identification information " 1 "；If it exists, the second index name for example " i_1_
Name_1_sex_2_age (name, sex, age) " can determine whether that " i_1_name_1_sex_2_age (name, sex, age) " is right
The query function that the index answered executes does not include the query function of the database index of the SQL statement to be created, based on the
One index name " i_1_age (age) ", creates the database index of the SQL statement；If the second index name for example " i_1_
The query function that age_1_sex (age, sex) " can determine whether that the corresponding index of second index name executes includes institute to be created
The query function of the database index of SQL statement is stated, " i_1_age_1_sex (age, sex) " is then used as the SQL statement
Database index title.
In one possible implementation, this method further include: the index name based on each index in database,
With each index name in scheduled detection frequency detecting database；When detecting that there are identical at least two index names
Querying condition field, identical type identification information and when identical matching identification information, retain at least two index
At least one index name in title.
Wherein, described to detect that there are identical querying condition fields, identical type at least two index names
When identification information and identical matching identification information, further includes: at least one of described at least two index name of modification
Index name.
For example, in MySQL database, based on the index name of each index in MySQL database, with scheduled inspection
Measured frequency detects each index name in MySQL database；It is looked into when detecting at least two index names there are identical
When asking condition field, identical type identification information and identical matching identification information, such as index name A " i_1_age (age) "
With index name B " i_1_name_1_age (name, age) ", weight whether there is according to confirmation index name A and index name B
Check is ask, and determines at least one index name in reserve index title A and index name B；Or according to query function, modify rope
Draw at least one index name in title A and index name B, be as modified index name B " i_1_age_1_name (age,
name)”。
Through this embodiment, efficiently and accurately index name can be managed, avoids the feelings for causing index redundancy
Condition occurs, and has greatly saved the oncurrent processing ability of disk space and database under high load.
In a concrete application scene, in MySQL database, the naming rule of predetermined index are as follows: different index types
Corresponding different type identification information and different match query modes correspond to different matching identification information；Simple index is corresponding
Type identification information be " i ", the corresponding type identification information of unique index be " u ", the index pair of index name can not be modified
The type identification information answered is " f "；Wherein, match query mode includes equivalent matching, commensurate in scope, sequence matching and filtering
At least any matching way in matching, it is " 1 ", the corresponding matching identification of commensurate in scope that equivalence, which matches corresponding matching identification information,
Information is " 2 ", the corresponding matching identification information of sequence matching is " 3 ", and it is " 4 " that filtering, which matches corresponding matching identification information,；When
Receive SQL statement A, such as " age > 10 select name from user where ", inquiry instruction when, parse the SQL
Sentence, it may be determined that the query condition information in the SQL statement is " age > 0 " and field information, is " name ", " age ", inquiry
Condition field is " age ", it is later determined that the match query mode of querying condition field " age " is commensurate in scope；It determines to be created
The SQL statement database index index type be simple index, based on scheduled index naming rule determine and " commonly
Index " the corresponding type identification information of index type is " i "；Based on the determination of scheduled index naming rule and match query mode
" commensurate in scope " corresponding matching identification information is " 2 "；Pass through scheduled concatenation character, such as " _ " connection type identification information
" i ", matching identification information " 2 " and querying condition field " age ", it may be determined that the rope of the database index of SQL statement A to be created
Draw title, is " i_2_age (age) "；When receiving SQL statement B, such as " select name from user order by
Age desc ", inquiry instruction when, querying condition field is " age ", and the matching way of querying condition field " age " is sequence
Matching, then can determine the index name of the database index of SQL statement B to be created, be " i_3_age (age) ", when receiving
SQL statement C, such as " select name from user where name=' tom ' and sex=1and age > 10order
By birthday desc ", inquiry instruction when, it may be determined that querying condition field be " name ", " age ", " sex " and
" birthday ", however, it is determined that the match query mode of querying condition field " name " is equivalent matching, querying condition field " age "
Match query mode be commensurate in scope, the match query mode of querying condition field " sex " is filtering matching, querying condition word
The match query mode of section " birthday " is sequence matching, then can determine the rope of the database index of SQL statement C to be created
Draw title, is " i_1_name_2_age_3_birthday_4_sex (name, age, birthday, sex) "；For SQL statement
C, however, it is determined that querying condition field " name " and the match query mode of " sex " match to be equivalent, querying condition field " age "
Match query mode is commensurate in scope, and the match query mode of querying condition field " birthday " is sequence matching, then can be true
The index name of the database index of fixed SQL statement C to be created is " i_1_name_1_sex_2_age_3_birthday
(name, sex, age, birthday) ", it should be noted that the combination of querying condition field does not limit built-up sequence, because
This, the index name of the database index of SQL statement C to be created can also be " i_2_age_3_birthday_1_name_
1_sex (name, sex, age, birthday) "；Wherein, the method for determination of the index name of the database index of SQL statement B, C
With the method for determination of the index name of the database index of SQL statement A, details are not described herein.
The embodiment of the invention provides a kind of device for creating database index, the method that above-mentioned offer may be implemented is implemented
Example, concrete function realize the explanation referred in embodiment of the method, and details are not described herein.
It should be understood that the particular order or level of the step of during disclosed are the examples of illustrative methods.Based on setting
Count preference, it should be appreciated that in the process the step of particular order or level can be in the feelings for the protection scope for not departing from the disclosure
It is rearranged under condition.Appended claim to a method is not illustratively sequentially to give the element of various steps, and not
It is to be limited to the particular order or level.
In above-mentioned detailed description, various features are combined together in single embodiment, to simplify the disclosure.No
This published method should be construed to reflect such intention, that is, the embodiment of theme claimed needs to compare
The more features of the feature clearly stated in each claim.On the contrary, as appended claims is reflected
Like that, the present invention is in the state fewer than whole features of disclosed single embodiment.Therefore, appended claims
It is hereby expressly incorporated into detailed description, wherein each claim is used as alone the individual preferred embodiment of the present invention.
For can be realized any technical staff in the art or using the present invention, above to disclosed embodiment into
Description is gone.To those skilled in the art；The various modifications mode of these embodiments will be apparent from, and this
The General Principle of text definition can also be suitable for other embodiments on the basis of not departing from the spirit and scope of the disclosure.
Therefore, the disclosure is not limited to embodiments set forth herein, but most wide with principle disclosed in the present application and novel features
Range is consistent.
Description above includes the citing of one or more embodiments.Certainly, in order to describe above-described embodiment and description portion
The all possible combination of part or method is impossible, but it will be appreciated by one of ordinary skill in the art that each implementation
Example can do further combinations and permutations.Therefore, embodiment described herein is intended to cover fall into the appended claims
Protection scope in all such changes, modifications and variations.In addition, with regard to term used in specification or claims
The mode that covers of "comprising", the word is similar to term " includes ", just as " including " solved in the claims as transitional word
As releasing.In addition, the use of any one of specification in claims term "or" being to indicate " non-exclusionism
Or ".
Those skilled in the art will also be appreciated that the various illustrative components, blocks that the embodiment of the present invention is listed
(illustrative logical block), unit and step can by electronic hardware, computer software, or both knot
Conjunction is realized.For the replaceability (interchangeability) for clearly showing that hardware and software, above-mentioned various explanations
Property component (illustrative components), unit and step universally describe their function.Such function
It can be that the design requirement for depending on specific application and whole system is realized by hardware or software.Those skilled in the art
Can be can be used by various methods and realize the function, but this realization is understood not to for every kind of specific application
Range beyond protection of the embodiment of the present invention.
Various illustrative logical blocks or unit described in the embodiment of the present invention can by general processor,
Digital signal processor, specific integrated circuit (ASIC), field programmable gate array or other programmable logic devices, discrete gate
Or transistor logic, discrete hardware components or above-mentioned any combination of design carry out implementation or operation described function.General place
Managing device can be microprocessor, and optionally, which may be any traditional processor, controller, microcontroller
Device or state machine.Processor can also be realized by the combination of computing device, such as digital signal processor and microprocessor,
Multi-microprocessor, one or more microprocessors combine a digital signal processor core or any other like configuration
To realize.
The step of method described in the embodiment of the present invention or algorithm can be directly embedded into hardware, processor execute it is soft
The combination of part module or the two.Software module can store in RAM memory, flash memory, ROM memory, EPROM storage
Other any form of storaging mediums in device, eeprom memory, register, hard disk, moveable magnetic disc, CD-ROM or this field
In.Illustratively, storaging medium can be connect with processor, so that processor can read information from storaging medium, and
It can be to storaging medium stored and written information.Optionally, storaging medium can also be integrated into the processor.Processor and storaging medium can
To be set in asic, ASIC be can be set in user terminal.Optionally, processor and storaging medium also can be set in
In different components in the terminal of family.
In one or more exemplary designs, above-mentioned function described in the embodiment of the present invention can be in hardware, soft
Part, firmware or any combination of this three are realized.If realized in software, these functions be can store and computer-readable
On medium, or it is transferred on a computer readable medium in the form of one or more instructions or code forms.Computer readable medium includes electricity
Brain storaging medium and convenient for so that computer program is allowed to be transferred to from a place telecommunication media in other places.Storaging medium can be with
It is that any general or special computer can be with the useable medium of access.For example, such computer readable media may include but
It is not limited to RAM, ROM, EEPROM, CD-ROM or other optical disc storages, disk storage or other magnetic storage devices or other
What can be used for carry or store with instruct or data structure and it is other can be by general or special computer or general or specially treated
The medium of the program code of device reading form.In addition, any connection can be properly termed computer readable medium, example
Such as, if software is to pass through a coaxial cable, fiber optic cables, double from a web-site, server or other remote resources
Twisted wire, Digital Subscriber Line (DSL) are defined with being also contained in for the wireless way for transmitting such as example infrared, wireless and microwave
In computer readable medium.The disk (disk) and disk (disc) includes compress disk, radium-shine disk, CD, DVD, floppy disk
And Blu-ray Disc, disk is usually with magnetic replicate data, and disk usually carries out optically replicated data with laser.Combinations of the above
Also it may be embodied in computer readable medium.
Above-described specific embodiment has carried out further the purpose of the present invention, technical scheme and beneficial effects
It is described in detail, it should be understood that being not intended to limit the present invention the foregoing is merely a specific embodiment of the invention
Protection scope, all within the spirits and principles of the present invention, any modification, equivalent substitution, improvement and etc. done should all include
Within protection scope of the present invention.
Claims (14)
1. a kind of method for creating database index characterized by comprising
When receiving the inquiry instruction of any structure query language SQL statement, the querying condition in the SQL statement is determined
Information and field information；
According to the query condition information, the querying condition field in the field information is determined, and determine the querying condition
The match query mode of field；
According to the querying condition field and the match query mode, the database rope of the SQL statement to be created is determined
The first index name drawn；
Based on first index name, the database index of the SQL statement is created.
2. the method according to claim 1, wherein the query condition information in the determination SQL statement
And field information, comprising:
Based on the grammer and keyword of S QL language, the SQL statement is parsed, determines the querying condition letter in the SQL statement
Breath and field information.
3. the method according to claim 1, wherein further include:
Predetermined index naming rule；
Wherein, the index naming rule includes: that different index types corresponds to different type identification information and different looks into
It askes matching way and corresponds to different matching identification information；
Wherein, the match query mode includes at least any in equivalent matching, commensurate in scope, sequence matching and filtering matching
Matching way.
4. according to the method described in claim 3, it is characterized in that, it is described according to the querying condition field and it is described inquiry
With mode, the first index name of the database index of the SQL statement to be created is determined, comprising:
The index type for determining the database index of the SQL statement to be created, based on the index naming rule determine with
The corresponding type identification information of the index type；
According to the fixed match query mode, based on index naming rule determination and the match query mode pair
The matching identification information answered；
According to the querying condition field, the type identification information and the matching identification information, determine to be created described
First index name of the database index of SQL statement.
5. according to the method described in claim 4, it is characterized by further comprising:
Type identification information, matching identification information and the inquiry in first index name are connected by scheduled concatenation character
Condition field.
6. method according to claim 4 or 5, which is characterized in that be based on first index name described, create institute
Before the step of stating the database index of SQL statement, further includes:
Judge whether existing comprising the querying condition field, the type identification information and the matching identification in database
Second index name of information；
If it exists, when the query function that the corresponding index of second index name executes does not include the SQL language to be created
When the query function of the database index of sentence, it is based on first index name, creates the database index of the SQL statement.
7. the method according to claim 1, wherein further include:
Based on the index name of each index in database, with each index name in scheduled detection frequency detecting database
Claim；
When detecting that there are identical querying condition fields, identical type identification information and identical at least two index names
Matching identification information when, retain at least one index name at least two index name；
Wherein, described to detect that there are identical querying condition fields, identical type identification at least two index names
When information and identical matching identification information, further includes:
Modify the index name of at least one of described at least two index name.
8. a kind of device for creating database index characterized by comprising
First determining module, described in determining when receiving the inquiry instruction of any structure query language SQL statement
Query condition information and field information in SQL statement；
Second determining module, for determining the querying condition field in the field information according to the query condition information, and
Determine the match query mode of the querying condition field；
Third determining module, it is to be created described for determining according to the querying condition field and the match query mode
First index name of the database index of SQL statement；
First creation module creates the database index of the SQL statement for being based on first index name.
9. device according to claim 8, which is characterized in that first determining module is specifically used for
Based on the grammer and keyword of S QL language, the SQL statement is parsed, determines the querying condition letter in the SQL statement
Breath and field information.
10. device according to claim 8, which is characterized in that further include:
Predetermined module is used for predetermined index naming rule；
Wherein, the index naming rule includes: that different index types corresponds to different type identification information and different looks into
It askes matching way and corresponds to different matching identification information；
Wherein, the match query mode includes at least any in equivalent matching, commensurate in scope, sequence matching and filtering matching
Matching way.
11. device according to claim 10, which is characterized in that the third determining module is specifically used for
The index type for determining the database index of the SQL statement to be created, based on the index naming rule determine with
The corresponding type identification information of the index type；
According to the fixed match query mode, based on index naming rule determination and the match query mode pair
The matching identification information answered；
According to the querying condition field, the type identification information and the matching identification information, determine to be created described
First index name of the database index of SQL statement.
12. device according to claim 11, which is characterized in that further include:
Link block, for connecting the type identification information in first index name, matching by scheduled concatenation character
Identification information and querying condition field.
13. device according to claim 11 or 12, which is characterized in that further include:
Judgment module, it is whether existing comprising the querying condition field, the type identification information in database for judging
With the second index name of the matching identification information；
Second creation module is used for if it exists, when the query function that the corresponding index of second index name executes does not include
When the query function of the database index of the SQL statement to be created, it is based on first index name, creates the SQL
The database index of sentence.
14. device according to claim 8, which is characterized in that further include:
Detection module, for the index name based on each index in database, with scheduled detection frequency detecting database
In each index name；
Reservation module detects that there are identical querying condition fields, identical type at least two index names for working as
When identification information and identical matching identification information, retain at least one index name at least two index name；
Wherein, the reservation module is also used to
When detecting that there are identical querying condition fields, identical type identification information and identical at least two index names
Matching identification information when, modify the index name of at least one of described at least two index name.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810994069.9A CN109145004A (en) | 2018-08-29 | 2018-08-29 | A kind of method and device creating database index |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810994069.9A CN109145004A (en) | 2018-08-29 | 2018-08-29 | A kind of method and device creating database index |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN109145004A true CN109145004A (en) | 2019-01-04 |
Family
ID=64828907
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201810994069.9A Pending CN109145004A (en) | 2018-08-29 | 2018-08-29 | A kind of method and device creating database index |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109145004A (en) |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110245137A (en) * | 2019-05-07 | 2019-09-17 | 阿里巴巴集团控股有限公司 | A kind of processing method of index, device and equipment |
| CN110502603A (en) * | 2019-08-23 | 2019-11-26 | 南京信易达计算技术有限公司 | A kind of solid-state physics property computing system and method based on web |
| CN110750555A (en) * | 2019-10-22 | 2020-02-04 | 中国工商银行股份有限公司 | Method, apparatus, computing device, and medium for generating index |
| CN111782659A (en) * | 2020-07-10 | 2020-10-16 | 东北大学 | Database index creation method, apparatus, computer equipment and storage medium |
| CN111831659A (en) * | 2020-07-09 | 2020-10-27 | 天津车之家数据信息技术有限公司 | Index checking method and device and computing equipment |
| CN113760948A (en) * | 2021-01-29 | 2021-12-07 | 北京沃东天骏信息技术有限公司 | Method and device for querying data |
| WO2024000896A1 (en) * | 2022-06-27 | 2024-01-04 | 深圳前海微众银行股份有限公司 | Sql script optimization method and device |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20020010701A1 (en) * | 1999-07-20 | 2002-01-24 | Platinum Technology Ip, Inc. | Method and apparatus for viewing the effect of changes to an index for a database table on an optimization plan for a database query |
| CN103810212A (en) * | 2012-11-14 | 2014-05-21 | 阿里巴巴集团控股有限公司 | Automated database index creation method and system |
| CN103902698A (en) * | 2014-03-31 | 2014-07-02 | 北京车商汇软件有限公司 | Data storage system and data storage method |
| CN103902702A (en) * | 2014-03-31 | 2014-07-02 | 北京车商汇软件有限公司 | Data storage system and data storage method |
| CN105095255A (en) * | 2014-05-07 | 2015-11-25 | 中兴通讯股份有限公司 | Data index creating method and device |
| CN107016018A (en) * | 2015-10-23 | 2017-08-04 | 阿里巴巴集团控股有限公司 | Database index creation method and device |
| CN107239451A (en) * | 2016-03-28 | 2017-10-10 | 北京京东尚科信息技术有限公司 | Database index creation method and device |
-
2018
- 2018-08-29 CN CN201810994069.9A patent/CN109145004A/en active Pending
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20020010701A1 (en) * | 1999-07-20 | 2002-01-24 | Platinum Technology Ip, Inc. | Method and apparatus for viewing the effect of changes to an index for a database table on an optimization plan for a database query |
| CN103810212A (en) * | 2012-11-14 | 2014-05-21 | 阿里巴巴集团控股有限公司 | Automated database index creation method and system |
| CN103902698A (en) * | 2014-03-31 | 2014-07-02 | 北京车商汇软件有限公司 | Data storage system and data storage method |
| CN103902702A (en) * | 2014-03-31 | 2014-07-02 | 北京车商汇软件有限公司 | Data storage system and data storage method |
| CN105095255A (en) * | 2014-05-07 | 2015-11-25 | 中兴通讯股份有限公司 | Data index creating method and device |
| CN107016018A (en) * | 2015-10-23 | 2017-08-04 | 阿里巴巴集团控股有限公司 | Database index creation method and device |
| CN107239451A (en) * | 2016-03-28 | 2017-10-10 | 北京京东尚科信息技术有限公司 | Database index creation method and device |
Cited By (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110245137A (en) * | 2019-05-07 | 2019-09-17 | 阿里巴巴集团控股有限公司 | A kind of processing method of index, device and equipment |
| CN110245137B (en) * | 2019-05-07 | 2023-06-27 | 创新先进技术有限公司 | Index processing method, device and equipment |
| CN110502603A (en) * | 2019-08-23 | 2019-11-26 | 南京信易达计算技术有限公司 | A kind of solid-state physics property computing system and method based on web |
| CN110750555A (en) * | 2019-10-22 | 2020-02-04 | 中国工商银行股份有限公司 | Method, apparatus, computing device, and medium for generating index |
| CN111831659A (en) * | 2020-07-09 | 2020-10-27 | 天津车之家数据信息技术有限公司 | Index checking method and device and computing equipment |
| CN111831659B (en) * | 2020-07-09 | 2022-09-06 | 天津车之家数据信息技术有限公司 | Index checking method and device and computing equipment |
| CN111782659A (en) * | 2020-07-10 | 2020-10-16 | 东北大学 | Database index creation method, apparatus, computer equipment and storage medium |
| CN111782659B (en) * | 2020-07-10 | 2023-10-17 | 东北大学 | Database index creation method, device, computer equipment and storage medium |
| CN113760948A (en) * | 2021-01-29 | 2021-12-07 | 北京沃东天骏信息技术有限公司 | Method and device for querying data |
| WO2024000896A1 (en) * | 2022-06-27 | 2024-01-04 | 深圳前海微众银行股份有限公司 | Sql script optimization method and device |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109145004A (en) | A kind of method and device creating database index | |
| US10901956B2 (en) | Indexing of linked data | |
| EP2863310B1 (en) | Data processing method and apparatus, and shared storage device | |
| US8386498B2 (en) | Message descriptions | |
| US20180113771A1 (en) | Transaction consistency query support for replicated data from recovery log to external data stores | |
| US20110040762A1 (en) | Segmenting postings list reader | |
| EP2637111A1 (en) | Data management system and method using database middleware | |
| CN112162983B (en) | Database index suggestion processing method, device, medium and electronic device | |
| CN112463886B (en) | Data processing method and device, electronic equipment and storage medium | |
| US11615076B2 (en) | Monolith database to distributed database transformation | |
| US20160132542A1 (en) | Online index rebuilding method and apparatus | |
| CN111324604A (en) | Database table processing method and device, electronic equipment and storage medium | |
| CN102708203A (en) | A Dynamic Database Management Method Based on XML Metadata | |
| CA2932403A1 (en) | Systems and methods for hosting an in-memory database | |
| CN108319608A (en) | The method, apparatus and system of access log storage inquiry | |
| CN110489442A (en) | A kind of data query method and system | |
| CN113407514A (en) | Method, device and equipment for migrating database and readable medium | |
| CN109388614A (en) | A kind of method, system and the equipment of catalogue file number quota | |
| CN102651024A (en) | Method and device for data conversion | |
| US9594677B2 (en) | Computer system, data management method, and recording medium for storing program | |
| WO2024198875A1 (en) | Data query method and system, and device and storage medium | |
| CN104394518B (en) | A kind of note transmission method and SMS platform | |
| KR101588375B1 (en) | Method and system for managing database | |
| CN112162982B (en) | Data query method, device, equipment and medium | |
| CN114416771A (en) | Data processing method and device, electronic equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| CB02 | Change of applicant information | ||
| CB02 | Change of applicant information |
Address after: 075000 ten building, phase 1, airport economic and Technological Development Zone, Zhangjiakou, Hebei Applicant after: Smart intercommunication Technology Co.,Ltd. Address before: 075000 ten building, phase 1, airport economic and Technological Development Zone, Zhangjiakou, Hebei Applicant before: INTELLIGENT INTERCONNECTION TECHNOLOGIES Co.,Ltd. |
|
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20190104 |