CN107633097A - The dynamic capacity-expanding method and system of a kind of database table - Google Patents
The dynamic capacity-expanding method and system of a kind of database table Download PDFInfo
- Publication number
- CN107633097A CN107633097A CN201710958921.2A CN201710958921A CN107633097A CN 107633097 A CN107633097 A CN 107633097A CN 201710958921 A CN201710958921 A CN 201710958921A CN 107633097 A CN107633097 A CN 107633097A
- Authority
- CN
- China
- Prior art keywords
- record
- current
- subscript
- database
- level
- 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
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a kind of dynamic capacity-expanding method of database table and device, this method includes：If data capacity, which reaches single storehouse, to be carried, realize database purchase dilatation using uniformity hash algorithm combination dummy node mode and obtain multi-level table；Determine that the table is current table when needing and increasing record to other any level tables for removing afterbody off-balancesheet, it is determined that the maximum table of the subscript of table name is object table in the next stage table of current table, it is determined that whether the bar number of the record corresponding record of current table and object table is less than the threshold value for precalculating to obtain, if, then determine to need the corresponding object table of increased record and increased record write-in will be needed into current table, if not, the subscript for then creating table name adds the table of predetermined interval value equal to the subscript of object table, it is determined that need the corresponding table created of increased record and increased record write-in will be needed into current table；When needing to increase record to afterbody table directly by record write-in into the table.So as to effectively realize the dilatation of table.
Description
Technical field
The present invention relates to technical field of data administration, more specifically to a kind of dynamic capacity-expanding method of database table
And system.
Background technology
In general system is all ascending progressively to be developed.Data volume is also smaller when business is few, begins to use a number
According to storehouse, then as business development data volume gradually increases, single storehouse list table can not meet the performance of storage and on-line equiries
It require that, now the scheme with regard to needing to take point storehouse to divide table.It is commonly used during storehouse table is divided and specific key assignments is carried out
Hash method carries out a point storehouse and divides table.But table is divided to need to consider when needing and realizing the dilatation of table to carry out point storehouse using hash
To the dependence between the situation of table not at the same level and each table etc., cause the dilatation of table to be difficult to, therefore be badly in need of a kind of skill
Art scheme realizes the dilatation of effective table.
In summary, how a kind of technical scheme for the dilatation that can effectively realize table is provided, is current art technology
Personnel's urgent problem to be solved.
The content of the invention
It is an object of the invention to provide a kind of dynamic capacity-expanding method of database table and system, effectively to realize the expansion of table
Hold.
To achieve these goals, the present invention provides following technical scheme：
A kind of dynamic capacity-expanding method of database table, including：
When data capacity reaches the situation that single storehouse can not carry, uniformity hash algorithm combination dummy node mode is used
Realize database purchase dilatation and obtain multi-level table corresponding to database；
Determine that any level table is current when needing and increasing other any level tables of record extremely in addition to afterbody table
Table, it is determined that a maximum table of the subscript of table name be object table in the next stage table of current table, determine in the currently table with mesh
The bar number of record corresponding to record in mark table simultaneously judges whether this number is less than the threshold value for precalculating to obtain, if it is,
It is determined that need the corresponding object table of increased record and increased record write-in will be needed into current table, if it is not, then creating table name
The subscript that subscript is equal to object table adds the table of predetermined interval value, it is determined that needing the corresponding table created of increased record and will need increased
Record write-in is into current table；
Determine that the afterbody table is current table when needing and increasing record to afterbody table, and directly write record
Into current table.
Preferably, it is determined that a maximum table of the subscript of table name is object table in the next stage table of current table, including：
The first order table that obtaining needs to record in increased record corresponds to identification information, and does uniformity to the identification information
Hash operation obtains the identification information of correspondence database；
The subscript that real-time update is safeguarded in database is corresponded to by obtained identification information and counts acquisition record in dictionary table
Corresponding maximum subscript in the next stage table of current table, and determine that table corresponding to the subscript is object table, wherein the subscript meter
Number dictionary tables be the record being pre-created have under correspondence database in addition to first order table in other every grade table it is corresponding it is maximum under
Mark.
Preferably, after obtaining multi-level table corresponding to database, in addition to：
Corresponded to using Redis Hash structures as buffer memory carrier to record every grade of table between identification information and table name
Corresponding relation corresponds to inquiry to realize.
Preferably, precalculate to obtain threshold value, including：
It is determined that the maximum size of current table records for MS bars, the data between upper level table and current table based on current table are closed
System determines the record in the current table of the maximum corresponding M bars of record in the upper level table of a current table, it is determined that corresponding to current table
Threshold value is MS/M.
Preferably, after obtaining multi-level table corresponding to database, in addition to：
When need to inquire about in certain grade of table certain record to should level table which of next stage table table when, then obtaining needs
The table name of the next stage carried in certain record of inquiry, next stage table corresponding to certain record inquired about with determination needs.
A kind of dynamic capacity-expanding system of database table, including：
Dilatation module is stored, is used for：When data capacity reaches the situation that single storehouse can not carry, calculated using uniformity Hash
Method combination dummy node mode realizes database purchase dilatation and obtains multi-level table corresponding to database；
First table dilatation module, is used for：When needing to increase other any level tables of record extremely in addition to afterbody table
It is current table to determine any level table, it is determined that a maximum table of the subscript of table name is object table in the next stage table of current table,
Determine in the current table bar number recorded corresponding with the record in object table and judge this number whether less than precalculating
Obtained threshold value, if it is, determine to need the corresponding object table of increased record and increased record write-in will be needed into current table,
If it is not, then the subscript that the subscript for creating table name is equal to object table adds the table of predetermined interval value, it is determined that needing increased record corresponding
The table of establishment simultaneously will need increased record write-in into current table；
Second table dilatation module, is used for：The afterbody table is determined when needing and increasing record to afterbody table to work as
Preceding table, and directly by record write-in into current table.
Preferably, the first table dilatation module includes：
Object table determining module, is used for：The first order table that obtaining needs to record in increased record corresponds to identification information, and
Uniformity Hash operation is done to the identification information and obtains the identification information of correspondence database；Data are corresponded to by obtained identification information
Corresponding maximum subscript in the next stage table for the current table for obtaining record in the subscript counting dictionary table that real-time update is safeguarded in storehouse,
And determine that table corresponding to the subscript is object table, wherein it is that the record being pre-created has corresponding data that the subscript, which counts dictionary table,
Under storehouse in addition to first order table corresponding maximum subscript in other every grade table.
Preferably, in addition to：
Logging modle, it is used for：After obtaining multi-level table corresponding to database, deposited using Redis Hash structures as caching
Storage carrier corresponds to the corresponding relation between identification information and table name to realize corresponding inquiry to record every grade of table.
Preferably, in addition to：
Threshold calculation module, it is used for：It is determined that the maximum size of current table records for MS bars, the upper level table based on current table
Data relationship between current table determines the note in the maximum corresponding current table of M bars of record in the upper level table of a current table
Record, it is determined that threshold value corresponding to current table is MS/M.
Preferably, in addition to：
Enquiry module, it is used for：After obtaining multi-level table corresponding to database, when needing to inquire about in certain grade of table certain record pair
Should level table which of next stage table table when, then obtain the table name of the next stage carried in certain record for needing to inquire about,
Next stage table corresponding to certain record inquired about with determination needs.
The invention provides a kind of dynamic capacity-expanding method of database table and device, wherein this method includes：When data are held
When amount reaches the situation that single storehouse can not carry, realize that database purchase expands using uniformity hash algorithm combination dummy node mode
Hold and obtain multi-level table corresponding to database；It is true when needing to increase other any level tables of record extremely in addition to afterbody table
The fixed any level table is current table, it is determined that a maximum table of the subscript of table name is object table in the next stage table of current table, really
The bar number recorded corresponding with the record in object table and judge this number whether less than precalculating in the fixed current table
The threshold value arrived, if it is, determining to need the corresponding object table of increased record and increased record write-in will be needed into current table, such as
Fruit is no, then the subscript for creating table name adds the table of predetermined interval value equal to the subscript of object table, it is determined that needing the corresponding wound of increased record
The table built simultaneously will need increased record write-in into current table；Determine this last when needing and increasing record to afterbody table
Level table is current table, and directly by record write-in into current table.In technical scheme disclosed in the present application, when data capacity reaches
During the situation that single storehouse can not carry, realize database purchase dilatation using uniformity hash algorithm combination dummy node mode and obtain
To multi-level table corresponding to database；And when needing to increase other grade table of the new record extremely in addition to afterbody table, really
Whether the next stage table of fixed this grade of table can provide corresponding capacity for it, if it is realize the addition of new record, otherwise create
The addition of new record is realized after new next stage table, and is directly added into i.e. when needing and increasing new record to afterbody table
Can, so as to which Pass through above-mentioned technical proposal effectively realizes the dilatation of table.
Brief description of the drawings
In order to illustrate more clearly about the embodiment of the present invention or technical scheme of the prior art, below will be to embodiment or existing
There is the required accompanying drawing used in technology description to be briefly described, it should be apparent that, drawings in the following description are only this
The embodiment of invention, for those of ordinary skill in the art, on the premise of not paying creative work, can also basis
The accompanying drawing of offer obtains other accompanying drawings.
Fig. 1 is a kind of flow chart of the dynamic capacity-expanding method of database table provided in an embodiment of the present invention；
Fig. 2 is each after realization storage dilatation in a kind of dynamic capacity-expanding method of database table provided in an embodiment of the present invention
The schematic diagram of node and table；
Fig. 3 is specific example figure in a kind of dynamic capacity-expanding method of database table provided in an embodiment of the present invention；
Fig. 4 is a kind of structural representation of the dynamic capacity-expanding system of database table provided in an embodiment of the present invention.
Embodiment
Below in conjunction with the accompanying drawing in the embodiment of the present invention, the technical scheme in the embodiment of the present invention is carried out clear, complete
Site preparation describes, it is clear that described embodiment is only part of the embodiment of the present invention, rather than whole embodiments.It is based on
Embodiment in the present invention, those of ordinary skill in the art are obtained every other under the premise of creative work is not made
Embodiment, belong to the scope of protection of the invention.
Referring to Fig. 1, it illustrates a kind of flow of the dynamic capacity-expanding method of database table provided in an embodiment of the present invention
Figure, can include：
S11：When data capacity reaches the situation that single storehouse can not carry, uniformity hash algorithm combination dummy node is used
Mode realizes database purchase dilatation and obtains multi-level table corresponding to database.
Wherein database purchase dilatation is realized using uniformity hash algorithm combination dummy node mode and obtain database
The realization principle of corresponding multi-level table is identical with corresponding to technical scheme in the prior art；Illustrated by taking Fig. 2 as an example, for any
Database, first order table are table directly corresponding with the database, are stored with corresponding to database and record in first order table, such as
T_A；Second level table is table directly corresponding with first order table, and every is stored with first order table in the table of the second level and is recorded
Thinner record, such as T_B, by that analogy；It is one-to-one relationship that first order table and database, which can typically be set, also may be used certainly
So that according to progress is actually needed, other set, within protection scope of the present invention.Citing is to the data between table not at the same level
Relation illustrates, and as what one-level table was deposited is recorded as each order, then the record that the next stage table of this grade of table is deposited is then each order
It is detailed.
S12：Determine that any level table is when needing and increasing other any level tables of record extremely in addition to afterbody table
Current table, it is determined that a maximum table of the subscript of table name is object table in the next stage table of current table, determine in the current table
The bar number recorded corresponding with the record in object table simultaneously judges whether this number is less than the threshold value for precalculating to obtain, if
It is, it is determined that need the corresponding object table of increased record and increased record write-in will be needed into current table, if it is not, then creating table
The subscript that the subscript of name is equal to object table adds the table of predetermined interval value, it is determined that needing the corresponding table created of increased record and will need to increase
The record added is write into current table.
Each table has the table name of oneself, and the table name includes corresponding subscript, if the T_A_001 in Fig. 2 is table
T_A table name, wherein 001 is the subscript that the table name includes, the table name of different tables is different in addition, and for any level table
For, the subscript that is included in the table name of the next stage table of the table be typically all according to creation time by it is early to evening and selection by it is small to
Big subscript, the table that adjacent time creates correspond to subscript and can differed according to the predetermined interval value (such as 1) for being actually needed setting,
Can the table that creates of different adjacent times correspond to subscript and differs different value, within protection scope of the present invention.Wherein, with working as
Be actually needed according to threshold value corresponding to preceding table and precalculate what is obtained, the record for determining to include in object table with current table
The corresponding relation of record, if being less than corresponding threshold value with recording the bar number of corresponding record in object table in the current table, recognize
For the capacity of the record more refined can also be provided in object table for the record increased newly in current table, therefore, will directly need to increase
The record added is added into current table, and determines that the corresponding record more refined of the record is stored into object table, otherwise, then
Thinking can not be to provide the capacity of the record more refined for record newly-increased in current table in object table, therefore creates newly
The currently next stage table of table, and determine to need increased record and the table of the establishment that there is corresponding relation, increased record will be needed to add
Enter into current table.
S13：Determine that the afterbody table is current table when needing and increasing record to afterbody table, and directly will record
Write-in is into current table.
If necessary to increase new record in afterbody table, then it is directly added in afterbody table.Specifically
For, identification information corresponding to per one-level table on afterbody table that can be as needing to obtain its carrying in increased record,
First order table is corresponded to identification information do uniformity Hash obtain corresponding to database, and by first order table according to the first order pair
It should identify and inquire a first order token record, and value obtains it in the field of storage association table name from first order token record
The value of contingency table file-name field, the value are the subscript of second level table corresponding with the token record of this first order；It is corresponding by the subscript
Determine to record corresponding to mark corresponding with the second level table carried in the table of the second level, and obtain the value of its contingency table file-name field,
The value is the subscript of third level table corresponding with the token record of this second level；By that analogy, until determining that new record needs
The afterbody table increased to, and the new record is increased in corresponding table.
In technical scheme disclosed in the present application, when data capacity reaches the situation that single storehouse can not carry, uniformity is used
Hash algorithm combination dummy node mode realizes database purchase dilatation and obtains multi-level table corresponding to database；And needing
Whether when increasing other grade table of the new record extremely in addition to afterbody table, determining the next stage table of this grade of table can carry for it
For corresponding capacity, the addition of new record is if it is realized, otherwise creates the addition that new record is realized after new next stage table, and
It is directly added into when needing and increasing new record to afterbody table, so as to which Pass through above-mentioned technical proposal effectively realizes
The dilatation of table.
The dynamic capacity-expanding method of a kind of database table provided in an embodiment of the present invention, it is determined that table in the next stage table of current table
A maximum table of the subscript of name is object table, can be included：
The first order table that obtaining needs to record in increased record corresponds to identification information, and does uniformity to the identification information
Hash operation obtains the identification information of correspondence database；
The subscript that real-time update is safeguarded in database is corresponded to by obtained identification information and counts acquisition record in dictionary table
Corresponding maximum subscript in the next stage table of current table, and determine that table corresponding to the subscript is object table, wherein the subscript meter
Number dictionary tables be the record being pre-created have under correspondence database in addition to first order table in other every grade table it is corresponding it is maximum under
Mark.
A subscript can be designed in each database and count dictionary table (T_INDEX), stored in the dictionary table correspondingly
The maximum subscript that the table name of other every grade table includes in addition to first order table under database, and when any level table is corresponding maximum
When subscript changes, real-time update safeguards subscript to count under the corresponding maximum in dictionary table and is designated as new maximum subscript；Such as Fig. 2
It is designated as being designated as 058 under 010, T_C table maximums under middle T_B tables maximum；So as to count dictionary table quick obtaining by these subscripts
Corresponding maximum subscript.And above-mentioned described identification information can be the ID of corresponding record in table, specifically, can set following
Rule：It is upper to this grade of table that the record of every grade of table in addition to first order table needs to carry corresponding record in corresponding first order table
The identification information of corresponding record in one-level table, and the record in first order table can carry the identification information of itself, in Fig. 2
Need to carry the ID that energy Hash is routed to corresponding record in the T_A tables of certain database in the record such as Object_1, Object_2, i.e.,
T_A_ID；The record of write-in T_B tables needs to carry the T_A_ID of corresponding record in T_A tables；The record of write-in T_C tables needs to carry
In T_A tables in T_A_ID, T_B table of corresponding record corresponding record T_B_ID；By that analogy；Wherein each ID is this note
Record the location index information of the corresponding table in database.So as to by the identification information uniformity Hash operation carried in record
The identification information (can be ID) of correspondence database is obtained, facilitates the positioning for database and the inquiry of related data.
A kind of dynamic capacity-expanding method of database table provided in an embodiment of the present invention, obtain multi-level table corresponding to database it
Afterwards, can also include：
Corresponded to using Redis Hash structures as buffer memory carrier to record every grade of table between identification information and table name
Corresponding relation is to realize quick search.
In order to reach in actual applications better performance requirement, can use cache accelerate record identification information with it is right
Answer the lookup speed between table name, buffer memory carrier is used as with Redis Hash structures in the application, Key in the structure
It is that the ID that certain record carries (is specifically as follows this and records its corresponding institute for certain record correspondence database node name, Field
Belong to table upper level table in corresponding record ID), Value be certain record belonging to table table name.It is corresponding can be with using process
Including：Table when operating certain record belonging to the record is unknown, but can be by obtaining its mark carried in the record
Information, identification information uniformity Hash is then corresponded to by first order table and obtains the database of corresponding record storage, is then passed through
Redis Hash structure commands：hget DB:The identification information of database reference numeral first order table gets value, and the value is
Table belonging to this certain record, and then determine that the record that identification information corresponding with the table carried in record is corresponding in the table is
Need the record operated.And the operation to record specifically can include increasing, deleting, changing, looking into etc. operating.Redis is one and increased income
Write using ANSI C languages, support network, can based on internal memory also can the log type of persistence, in Key-Value structures
Deposit data storehouse, possess more rich data structure, and the API of multilingual is provided.So as to realize identification information and table name
Between quick search.Specifically, the KV structures of the above-mentioned buffer memory carrier of design can be as shown in table 1 below：
Table 1
It is corresponding to be included using process：
When operating the record of T_B_011 tables, data are obtained by T_B_ID father's business relations T_A_ID uniformity Hash
Storehouse N-1, then pass through Redis Hash structure commands：hget DB:N-1T_A_ID gets value T_B_011.
When operating the record of T_C_023 tables, data are obtained by T_B_ID father's business relations T_A_ID uniformity Hash
Storehouse N-1, then pass through Redis Hash structure commands：hget DB:N-1T_B_ID gets value T_C_023.
A kind of dynamic capacity-expanding method of database table provided in an embodiment of the present invention, precalculates obtained threshold value, can be with
Including：
It is determined that the maximum size of current table records for MS bars, the data between upper level table and current table based on current table are closed
System determines the record in the current table of the maximum corresponding M bars of record in the upper level table of a current table, it is determined that corresponding to current table
Threshold value is MS/M.
It should be noted that corresponding single database, can combine reserved disk space size, database server I/O
Performance height, low soft hardware performance index high to the database data read-write frequency, are closed further according to the data between table and table
System's (as assumed order table VS order detail lists, it is 30W bars that an order record, which correspond to order detail list and records the upper limit) determines
The data capacity upper limit of single table, specifically, soft hardware performance index typically corresponding with single database can be supplied to this
The space of database corresponding table has a maximum limit, and then can set the database according to being actually needed in the maximum limit
The space of corresponding table, and then the data capacity upper limit of each table is determined in the space based on table, can such as be set same business
Data correspond to N bars record storage in same tables of data, specifically can be by each first order table and multiple business pair
Data are answered to be arranged to one-to-one relationship, i.e., each first order table storage corresponds to business datum and inferior relation (specifically includes down
Level table name), it is corresponding, can be using the maximum data capacity that the business estimated can have as the data capacity for corresponding to first order table
The upper limit, the situation based on business datum estimate the record strip number of the maximum corresponding second level table of a record of first order table, then
The record strip number of the maximum corresponding second level table of one record of the data capacity upper limit of the first order table divided by first order table is obtained
What is arrived is the data capacity upper limit of the second level table, by that analogy, to ensure that N bars record is deposited corresponding to the data of same business
Storage is in same tables of data.Each table included with Fig. 2 is illustrated：
One database one of T_A tables is opened, maximum size MS setting 1000W bar records；
T_B_001--T_B_* tables, single table maximum size MS setting 1000W bar records, according to data cases, a T_A table
The maximum corresponding M bars T_B token records of record, i.e., the corresponding T_A token record numbers that a T_B table can be deposited mark for MS/M=N bars T_A
Record；
T_C_001--T_C_*, single table maximum size MS set 2000W, and according to data cases, a T_B token record is maximum
Corresponding M bars T_C token records, i.e., the corresponding T_B token record numbers that a T_C table can be deposited are MS/M=N bars T_B token records.
A kind of dynamic capacity-expanding method of database table provided in an embodiment of the present invention, obtain multi-level table corresponding to database it
Afterwards, can also include：
When need to inquire about in certain grade of table certain record to should level table which of next stage table table when, then obtaining needs
The table name of the next stage carried in certain record of inquiry, next stage table corresponding to certain record inquired about with determination needs.
It should be noted that it can carry its related data (data more refined) in each record of every grade of table
It is present in which table of next stage table, in Fig. 2, have recorded the presence of its associated traffic data in T_A tables in each record
Table name (such as T_B_002) in certain of T_B table, its associated traffic data is have recorded in each record in T_B_001 tables and is deposited
Table name (such as T_C_005) in T_C certain table, and so on.It is achieved thereby that the fast quick checking of watch where certain record
Ask.
Otherwise for above-mentioned technical proposal disclosed in the present application, it is specifically described by taking Fig. 2 as an example：
1st, routing rule：
The industry of the T_A tables of certain database is routed in the record such as Object_1, Object_2 firstly the need of carrying energy hash
Business id information T_A_ID, the wherein record such as Object_1, Object_2 are one and referred to, and can represent the record of any one table,
The record that can be understood as each table is both needed to carry T_A_ID；The record of write-in T_B tables needs to carry T_A_ID；Write T_C tables
Record need carry T_A_ID, T_B_ID；Other hierarchical relationship tables are by that analogy.
2nd, increasing a T_A tokens record newly (needs increase record in addition to afterbody table in corresponding above-described embodiment
During other any level tables)：
Uniformity Hash operation is done to the T_A_ID (T_A_ID is the major key of data) carried in the record that need to increase newly to obtain
Corresponding database；
Counting maximum subscript corresponding to inquiry T_B in dictionary table (T_INDEX) from the subscript of obtained database (is such as
010)；
Record in T_B_010 tables has the Record ID (T_A_ID) of the corresponding T_A tables of a field record, is based on
Statistics SQL (SELECT COUNT (DISTINCT T_A_ID) FROM T_B_010) obtains statistics Q；
If Q<N (N is threshold value corresponding to T_A tables), then illustrate that current T_B_010 is not up to the anticipated capability upper limit, then will
It is " T_B_010 " that the record adds contingency table file-name field content when writing T_A tables；
If Q>=N, then illustrate that current T_B_010 reaches the anticipated capability upper limit, then dynamic creation T_B_011 tables, and will
The record adds contingency table file-name field content when writing T_A tables be " T_B_011 " (paying attention to no record in now T_B_011 tables),
And update in subscript counting dictionary table (T_INDEX) and be designated as 011 under maximum corresponding to T_B.
3rd, increasing a T_B tokens record newly (needs increase record in addition to afterbody table in corresponding above-described embodiment
During other any level tables)：
Uniformity Hash operation is done to the T_A_ID carried in newly-increased record and obtains corresponding database；
A unique TA record, is obtained in being recorded by the TA corresponding to being inquired according to T_A_ID value from T_A tables
The value (such as T_B_011) of the contingency table file-name field of maintenance；
Maximum subscript corresponding to inquiry T_C in dictionary table (T_INDEX) is counted from the subscript of database (as being 058)；
According to counting record strip number Q after the associate field duplicate removal with T_B tables in T_C_058 tables；
If Q<N (T_B tables correspond to threshold value), then illustrate that current T_C_058 is not up to the anticipated capability upper limit, then will be newly-increased
It is " T_C_058 " that T_B records add contingency table file-name field content when being written in T_B_011 tables；
If Q>=N, then illustrate that current T_C_058 reaches the anticipated capability upper limit, then dynamic creation T_C_059 tables, and will
The record adds contingency table file-name field content when writing T_B_011 tables be that " T_C_059 " (is paid attention in now T_C_059 tables without note
Record), and update in subscript counting dictionary table (T_INDEX) and be designated as 059 under maximum corresponding to T_C.
4th, increase a T_C tokens record newly (when needing increase record to afterbody table)
By needing to obtain its T_A_ID, T_B_ID for carrying in newly-increased record；
Database corresponding to the acquisition of uniformity Hash is done to T_A_ID；
One T_A record is inquired according to T_A_ID from T_A tables, and storage associates the field of table name from T_A records
Middle value obtains the value T_B_XXX (determining T_B tables corresponding with T_A records) of its contingency table file-name field；
Record is inquired from T_B_XXX according to T_B_ID, and obtains the value T_C_XXX of its contingency table file-name field (i.e. really
Make T_C tables corresponding with T_B records)；
Newly-increased record is written in T_C_XXX tables.
5th, data query：The lower DBMS of inquiry needs to take the id information of all parents.
The record inquired about in first order table, it may be determined that identification information corresponding to the record and by being found in first order table
Record corresponding with the mark；Corresponding diagram 2, inquiry T_A tokens record are led, it is necessary to clear and definite T_A_ID according to T_A_ID as inquiry
Key finds the corresponding record in T_A tables；
Inquire about the record of other tables in addition to first order table, it may be determined that the record corresponds to the mark recorded in first order table
Information, it is then determined that corresponding record of the identification information in first order table, then the corresponding second level is determined from the record
Table, then determine that the record corresponds to corresponding record of the identification information recorded in the table of the second level in the table of the second level, by that analogy, directly
Untill the record is found.Corresponding diagram 2, inquiry T_B token records are, it is necessary to clear and definite T_A_ID, first (T_B_ID is optional), basis
T_A_ID finds the corresponding record (such as XX) in T_A tables as inquiry major key, then is taken out from record XX associate field
Value (such as YY), value YY is T_B a certain specific table (such as T_B_011), finally, it can according to T_B_ID from T_B_
T_B records corresponding to being inquired in 011 table；
T_C token records are inquired about, it is necessary to clear and definite T_A_ID, T_B_ID, (T_C_ID is optional), first according to T_A_ID from T_A
Table finds a T_A record, then corresponding T_B_XXX tables are obtained from T_A records, then is looked into T_B_ID from T_B_XXX tables
Recorded to a T_B, then corresponding T_C_XXX tables are obtained from T_B records, then one is found from T_C_XXX tables with T_C_ID
Bar T_C is recorded.
If any more levels, ibid scheme is by that analogy.It is further to note that had by taking Fig. 2 as an example
The specific implementation being related to during the above-mentioned label 1 to 5 that body illustrates is upper to first order table, second level table, the 3rd
T_A tables also within protection scope of the present invention, are also corresponded to first order table by level table etc., T_B tables correspond to second level table,
T_C tables correspond to third level table etc. and realize above-mentioned technical proposal also within protection scope of the present invention.
It should be noted that the dynamic capacity-expanding method of the database table set forth herein based on business major key can be applied
In the assets packing mortgage process of the consumer finance.Assets packing process is related to there may be N number of assets under an assets bag bright
Carefully (also referred to as contract), each assets are detailed to there may be N bars refund record again.The paging that can sort is needed for inquiry angle
All assets checked under an assets bag are detailed, all refund record that energy paging is checked under an assets detail.From renewal
The field data of a plurality of assets detail record in an affairs under one assets bag of batch updating is needed for angle.If
Using the assets that can be caused based on simple modulus hash algorithm or uniformity Hash+dummy node algorithm under an assets bag
Detail can be stored in multiple tables in multiple storehouses because of balance by scattered.Thus cause there arises a problem that：
Sequence paging checks difficulty data, it is necessary to travel through all assets detail lists in all storehouses, and polymerization is difficult and inquiry is imitated
Rate is low；
Batch updating data are not supported；
The scene of processing distributed transaction can also be faced；
The dilatation of assets detail list and refund record sheet also faces the migration between memory node.
And using set forth herein scheme after, can solve these problems.
Illustrated with reference to Fig. 3, in the assets bag business scenario of the consumer finance, fastest-rising two business of data volume
Table is exactly assets detail list and refund record sheet, and the corresponding about 20W assets of 1 assets bag are detailed (contract), and 1 contract is corresponding about
30 records of refunding, i.e., 1 assets bag ≈ 20W contract ≈ 600W bar, which is refunded, records.
Capacity planning：
Single table capacity delimit principle：A large amount of table about 1000W for inquiring about, updating on a small quantity are recorded/opened, and are subjected to the upper limit and are floated
20% i.e. 1200W/；The table of frequent updating about 500W records/, it is subjected to upper limit floating 20% i.e. 600W/；So refund
Detail list (apm_repay_detail) 1000W/, assets bag detail list (apm_detail) 500W/.
Limit the record that an assets bag detail list only deposits 25 assets bags.[25 assets bags of 500W/20W ≈]
Limit the record that 2 assets bags are only deposited in a refund record sheet.[1000W/ (20W contracts * 30 refunds detailed)
2 assets bags of ≈]
Storehouse table is split：1W assets bag can be reached after being expected 1 year according to practical business development, you can extrapolate needs
1W/25=400 assets bag detail lists, 1W/2=5000 refund record sheets.Further according to data library disk, network I/O processing
Ability initially sets virtual data base node 128, actual database node 16, limits single storehouse 400 and opens table or so.
Data are route and operation：
Operating personnel when system increases an assets bag newly, according to the cryptographic Hash of assets packet number with modulus 128 do by system
Modulo operation, which obtains it, should be stored in the assets bag table of some database；Then the assets bag is determined by counting, calculating
The table name specifically deposited of assets detail record, table is not present then system and actively creates, finally include providing by assets bag record
Production detail record table name is stored in assets bag table together.
Operating personnel need to specify an affiliated assets bag when system increases an assets detail item (contract) newly.Pass through
Assets packet number determines database and the table name of corresponding storage assets detail, then determines that the assets are detailed by counting, calculating
The specific table name that the refund record of item should be deposited, then system actively establishment is not present in table, finally by the assets detail record bag
The table name for including storage refund flowing water is stored in assets detail list together.
User determines that database and corresponding storage assets are bright after system amortizes a flowing water, by assets packet number
Thin table name, the table name of storage refund record is determined by assets detailed (contract) number, refund flowing water is then inserted into refund
In record sheet.
Implement set forth herein scheme after, following beneficial effect has been reached in the project：
The assets of one assets bag are detailed and record of refunding in a table, is being easy to quick paging and aggregate number respectively
According to；
Support to an assets detail list or the batch updating of refund record sheet data, deletion action；
Because all data are in a table, in the absence of the scene of distributed transaction；
Because the detailed refund record of assets will not change again after a certain time and frozen state is in, the table is just not
Can there are insertion and renewal operation again, the situation for being split because of business development and moving the table data is also not present.
The embodiment of the present invention additionally provides a kind of dynamic capacity-expanding system of database table, as shown in figure 4, can include：
Dilatation module 11 is stored, is used for：When data capacity reaches the situation that single storehouse can not carry, uniformity Hash is used
Algorithm combination dummy node mode realizes database purchase dilatation and obtains multi-level table corresponding to database；
First table dilatation module 12, is used for：Recorded when needing increase to other any level tables in addition to afterbody table
When determine that any level table is current table, it is determined that a maximum table of the subscript of table name is target in the next stage table of current table
Table, determine the bar number recorded corresponding with the record in object table in the current table and judge whether this number is less than meter in advance
Obtained threshold value, if it is, determining to need the corresponding object table of increased record and increased record write-in will be needed to current table
In, if it is not, then the subscript that the subscript for creating table name is equal to object table adds the table of predetermined interval value, it is determined that needing increased record pair
The table that should create simultaneously will need increased record write-in into current table；
Second table dilatation module 13, is used for：Determine that the afterbody table is when needing and increasing record to afterbody table
Current table, and directly by record write-in into current table.
A kind of dynamic capacity-expanding system of database table provided in an embodiment of the present invention, the first table dilatation module can include：
Object table determining module, is used for：The first order table that obtaining needs to record in increased record corresponds to identification information, and
Uniformity Hash operation is done to the identification information and obtains the identification information of correspondence database；Data are corresponded to by obtained identification information
Corresponding maximum subscript in the next stage table for the current table for obtaining record in the subscript counting dictionary table that real-time update is safeguarded in storehouse,
And determine that table corresponding to the subscript is object table, wherein it is that the record being pre-created has corresponding data that the subscript, which counts dictionary table,
Under storehouse in addition to first order table corresponding maximum subscript in other every grade table.
A kind of dynamic capacity-expanding system of database table provided in an embodiment of the present invention, can also include：
Logging modle, it is used for：After obtaining multi-level table corresponding to database, deposited using Redis Hash structures as caching
Storage carrier corresponds to the corresponding relation between identification information and table name to realize corresponding inquiry to record every grade of table.
A kind of dynamic capacity-expanding system of database table provided in an embodiment of the present invention, can also include：
Threshold calculation module, it is used for：It is determined that the maximum size of current table records for MS bars, the upper level table based on current table
Data relationship between current table determines the note in the maximum corresponding current table of M bars of record in the upper level table of a current table
Record, it is determined that threshold value corresponding to current table is MS/M.
A kind of dynamic capacity-expanding system of database table provided in an embodiment of the present invention, can also include：
Enquiry module, it is used for：After obtaining multi-level table corresponding to database, when needing to inquire about in certain grade of table certain record pair
Should level table which of next stage table table when, then obtain the table name of the next stage carried in certain record for needing to inquire about,
Next stage table corresponding to certain record inquired about with determination needs.
The explanation of relevant portion refers to this in a kind of dynamic capacity-expanding system of database table provided in an embodiment of the present invention
The detailed description of corresponding part, will not be repeated here in a kind of dynamic capacity-expanding method for database table that inventive embodiments provide.
The part consistent with realization principle in the prior art be not specifically in other above-mentioned technical proposal provided in an embodiment of the present invention
It is bright, in order to avoid excessively repeat.
The foregoing description of the disclosed embodiments, those skilled in the art are enable to realize or using the present invention.To this
A variety of modifications of a little embodiments will be apparent for a person skilled in the art, and generic principles defined herein can
Without departing from the spirit or scope of the present invention, to realize in other embodiments.Therefore, the present invention will not be limited
The embodiments shown herein is formed on, and is to fit to consistent with principles disclosed herein and features of novelty most wide
Scope.
Claims (10)
- A kind of 1. dynamic capacity-expanding method of database table, it is characterised in that including：When data capacity reaches the situation that single storehouse can not carry, realized using uniformity hash algorithm combination dummy node mode Database purchase dilatation simultaneously obtains multi-level table corresponding to database；Determine that any level table is current table when needing and increasing other any level tables of record extremely in addition to afterbody table, really A maximum table of the subscript of table name be object table in the next stage table of settled preceding table, determine in the currently table with object table Record corresponding to record bar number and judge whether this number is less than and precalculate obtained threshold value, if it is, determine need to Increased record corresponds to object table and will need increased record write-in into current table, if it is not, then create subscript of table name etc. Add the table of predetermined interval value in the subscript of object table, it is determined that needing the corresponding table created of increased record and increased need to recording to write Enter into current table；Determine that the afterbody table is current table when needs increase record to afterbody table, and directly extremely work as record write-in In preceding table.
- 2. according to the method for claim 1, it is characterised in that it is determined that the subscript of table name is maximum in the next stage table of current table A table be object table, including：The first order table that obtaining needs to record in increased record corresponds to identification information, and does uniformity Hash to the identification information Computing obtains the identification information of correspondence database；Corresponded to by obtained identification information in the subscript counting dictionary table that real-time update is safeguarded in database and obtain the current of record Corresponding maximum subscript in the next stage table of table, and determine that table corresponding to the subscript is object table, wherein the subscript counts word Allusion quotation table is that the record being pre-created has under correspondence database in addition to first order table corresponding maximum subscript in other every grade table.
- 3. according to the method for claim 2, it is characterised in that after obtaining multi-level table corresponding to database, in addition to：Recorded using Redis Hash structures as buffer memory carrier every grade of table correspond to it is corresponding between identification information and table name Relation corresponds to inquiry to realize.
- 4. according to the method for claim 1, it is characterised in that precalculate to obtain threshold value, including：It is determined that the maximum size of current table records for MS bars, the data relationship between upper level table and current table based on current table is true The record in the current table of the maximum corresponding M bars of record in the upper level table of a fixed current table, it is determined that threshold value corresponding to current table For MS/M.
- 5. according to the method for claim 4, it is characterised in that after obtaining multi-level table corresponding to database, in addition to：When need to inquire about in certain grade of table certain record to should level table which of next stage table table when, then obtaining needs to inquire about Certain record in the table name of next stage that carries, to determine to need next stage table corresponding to certain record inquired about.
- A kind of 6. dynamic capacity-expanding system of database table, it is characterised in that including：Dilatation module is stored, is used for：When data capacity reaches the situation that single storehouse can not carry, uniformity hash algorithm knot is used Dummy node mode is closed to realize database purchase dilatation and obtain multi-level table corresponding to database；First table dilatation module, is used for：Determined when needing and increasing other any level tables of record extremely in addition to afterbody table Any level table is current table, it is determined that a maximum table of the subscript of table name is object table in the next stage table of current table, it is determined that The bar number recorded corresponding with the record in object table and judge this number whether less than precalculating to obtain in the current table Threshold value, if it is, determine to need the corresponding object table of increased record and increased record write-in will be needed into current table, if No, then the subscript for creating table name adds the table of predetermined interval value equal to the subscript of object table, it is determined that needing increased record is corresponding to create Table and will need it is increased record write-in into current table；Second table dilatation module, is used for：Determine that the afterbody table is current table when needing and increasing record to afterbody table, And directly by record write-in into current table.
- 7. system according to claim 6, it is characterised in that the first table dilatation module includes：Object table determining module, is used for：The first order table that obtaining needs to record in increased record corresponds to identification information, and to this Identification information does uniformity Hash operation and obtains the identification information of correspondence database；Corresponded to by obtained identification information in database Corresponding maximum subscript in the next stage table for the current table for obtaining record in the subscript counting dictionary table that real-time update is safeguarded, and really Table corresponding to the fixed subscript is object table, wherein it is that the record being pre-created has under correspondence database that the subscript, which counts dictionary table, The corresponding maximum subscript in other every grade table in addition to first order table.
- 8. system according to claim 7, it is characterised in that also include：Logging modle, it is used for：After obtaining multi-level table corresponding to database, carried using Redis Hash structures as buffer memory Body corresponds to the corresponding relation between identification information and table name to realize corresponding inquiry to record every grade of table.
- 9. system according to claim 6, it is characterised in that also include：Threshold calculation module, it is used for：It is determined that the maximum size of current table records for MS bars, the upper level table based on current table is with working as Data relationship between preceding table determines the record in the current table of the maximum corresponding M bars of record in the upper level table of a current table, really Threshold value corresponding to settled preceding table is MS/M.
- 10. system according to claim 9, it is characterised in that also include：Enquiry module, it is used for：After obtaining multi-level table corresponding to database, when needing to inquire about in certain grade of table certain record to should During which of the next stage table of level table table, then the table name of the next stage carried in certain record for needing to inquire about is obtained, with true Need to inquire about calmly certain record corresponding to next stage table.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710958921.2A CN107633097B (en) | 2017-10-16 | 2017-10-16 | A kind of dynamic capacity-expanding method and system of database table |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710958921.2A CN107633097B (en) | 2017-10-16 | 2017-10-16 | A kind of dynamic capacity-expanding method and system of database table |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN107633097A true CN107633097A (en) | 2018-01-26 |
| CN107633097B CN107633097B (en) | 2018-09-25 |
Family
ID=61105360
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201710958921.2A Active CN107633097B (en) | 2017-10-16 | 2017-10-16 | A kind of dynamic capacity-expanding method and system of database table |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN107633097B (en) |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110059080A (en) * | 2019-04-29 | 2019-07-26 | 珠海天燕科技有限公司 | A kind of method and apparatus of data processing |
| CN110928900A (en) * | 2018-09-17 | 2020-03-27 | 马上消费金融股份有限公司 | Multi-table data query method, device, terminal and computer storage medium |
| CN111782628A (en) * | 2020-06-11 | 2020-10-16 | 国电南京自动化股份有限公司 | Method, configuration tool and system for dynamically expanding increment of real-time base of power system |
| CN112783860A (en) * | 2021-01-11 | 2021-05-11 | 网易（杭州）网络有限公司 | Method and device for constructing mirror database, storage medium and computer equipment |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP1736904A2 (en) * | 2005-06-15 | 2006-12-27 | Alcatel | Methods and data structure for indexed storage of hierarchically interrelated information in a relational database |
| CN102867071A (en) * | 2012-10-19 | 2013-01-09 | 烽火通信科技股份有限公司 | Management method for massive network management historical data |
| CN105989015A (en) * | 2015-01-28 | 2016-10-05 | 阿里巴巴集团控股有限公司 | Capacity expanding method of database and database accessing method and device |
-
2017
- 2017-10-16 CN CN201710958921.2A patent/CN107633097B/en active Active
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP1736904A2 (en) * | 2005-06-15 | 2006-12-27 | Alcatel | Methods and data structure for indexed storage of hierarchically interrelated information in a relational database |
| CN102867071A (en) * | 2012-10-19 | 2013-01-09 | 烽火通信科技股份有限公司 | Management method for massive network management historical data |
| CN105989015A (en) * | 2015-01-28 | 2016-10-05 | 阿里巴巴集团控股有限公司 | Capacity expanding method of database and database accessing method and device |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110928900A (en) * | 2018-09-17 | 2020-03-27 | 马上消费金融股份有限公司 | Multi-table data query method, device, terminal and computer storage medium |
| CN110928900B (en) * | 2018-09-17 | 2021-02-19 | 马上消费金融股份有限公司 | Multi-table data query method, device, terminal and computer storage medium |
| CN110059080A (en) * | 2019-04-29 | 2019-07-26 | 珠海天燕科技有限公司 | A kind of method and apparatus of data processing |
| CN111782628A (en) * | 2020-06-11 | 2020-10-16 | 国电南京自动化股份有限公司 | Method, configuration tool and system for dynamically expanding increment of real-time base of power system |
| CN112783860A (en) * | 2021-01-11 | 2021-05-11 | 网易（杭州）网络有限公司 | Method and device for constructing mirror database, storage medium and computer equipment |
| CN112783860B (en) * | 2021-01-11 | 2023-08-08 | 网易（杭州）网络有限公司 | Method, device, storage medium and computer equipment for constructing mirror image database |
Also Published As
| Publication number | Publication date |
|---|---|
| CN107633097B (en) | 2018-09-25 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107633097B (en) | A kind of dynamic capacity-expanding method and system of database table | |
| CN100498740C (en) | Data cache processing method, system and data cache device | |
| CN108009008A (en) | Data processing method and system, electronic equipment | |
| CN100566281C (en) | The method and apparatus of virtual private network routing search | |
| CN108228649A (en) | For the method and apparatus of data access | |
| US6415375B2 (en) | Information storage and retrieval system | |
| JP2004518225A (en) | Data structures for information systems | |
| CN104268099B (en) | A kind of method and device for managing reading and writing data | |
| CN103020255A (en) | Hierarchical storage method and hierarchical storage device | |
| CN107493327A (en) | Distributed caching management method, system and data management system | |
| CN106406759A (en) | Data storage method and device | |
| CN105630803B (en) | The method and apparatus that Document image analysis establishes index | |
| CN108287840A (en) | A kind of data storage and query method based on matrix Hash | |
| CN109657498B (en) | Differential privacy protection method for top-k symbiotic mode mining in multiple streams | |
| CN105389367A (en) | Power network graphic multi-tense and multi-level distributed storage method based on Mongo database | |
| CN104021223B (en) | The access method and device of a kind of Cluster Database measuring point | |
| CN108052541A (en) | The realization of file system based on multi-level page-table bibliographic structure, access method, terminal | |
| CN107426315A (en) | A kind of improved method of the distributed cache system Memcached based on BP neural network | |
| CN108093024A (en) | A kind of classification method for routing and device based on data frequency | |
| CN109408416B (en) | Address mapping table entry page management method and device | |
| CN106844491A (en) | A kind of write-in of ephemeral data, read method and write-in, reading device | |
| CN103036796B (en) | Route information update method and device | |
| CN102436479B (en) | Method and system for generating globally unique data identifier | |
| CN104133970A (en) | Data space management method and device | |
| CN106934033A (en) | A kind of bent plate robot data indexing means and device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |