CN107633097B - A kind of dynamic capacity-expanding method and system of database table - Google Patents
A kind of dynamic capacity-expanding method and system of database table Download PDFInfo
- Publication number
- CN107633097B CN107633097B CN201710958921.2A CN201710958921A CN107633097B CN 107633097 B CN107633097 B CN 107633097B CN 201710958921 A CN201710958921 A CN 201710958921A CN 107633097 B CN107633097 B CN 107633097B
- Authority
- CN
- China
- Prior art keywords
- record
- current
- subscript
- level
- database
- 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.)
- Active
Links
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 library, to be carried, realizes database purchase dilatation using consistency hash algorithm combination dummy node mode and obtain multi-level table；When needing the when of increasing record to other any level tables for removing afterbody off-balancesheet to determine, the table is current table, determine that the maximum table of subscript of table name in the next stage table of current table is object table, determine whether the item number of the record corresponding record of current table and object table is less than the threshold value precalculated, if it is, then determining needs the corresponding object table of increased record and will need in increased record write-in to 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, determining needs the corresponding table created of increased record and will need in increased record write-in to current table；It directly will be in record write-in to the table when needing to increase record to afterbody table.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
General system is all ascending gradually to be developed.Data volume is also smaller when business is few, begins to use a number
According to library, then as business development data volume gradually increases, single library list table cannot meet the performance of storage and on-line equiries
It requires, at this time the scheme with regard to needing that point library is taken to divide table.It is carried out commonly using to specific key assignments during dividing library table
The method of hash carries out a point library and divides table.But table is divided to need to consider when needing the dilatation of realization table to carry out point library using hash
To the dependence etc. the case where table not at the same level and between each table, the dilatation of table is caused to be difficult to realize, therefore is badly in need of a kind of skill
Art scheme realizes the dilatation of effective table.
It is current art technology in conclusion how to provide a kind of technical solution for capableing of the effective dilatation of realization table
Personnel's urgent problem to be solved.
Invention content
The object of the present invention is to provide a kind of dynamic capacity-expanding method of database table and systems, effectively to realize the expansion of table
Hold.
To achieve the goals above, the present invention provides the following technical solutions：
A kind of dynamic capacity-expanding method of database table, including：
When data capacity reaches the case where single library can not carry, consistency hash algorithm combination dummy node mode is used
It realizes database purchase dilatation and obtains the corresponding multi-level table of database；Wherein, it is deposited in every level-one table in addition to first order table
It contains and records corresponding record with every in the upper level table of every level-one table；
Determine that any level table is current when needing the when of increasing other any level tables of record extremely in addition to afterbody table
Table, determine the maximum table of the subscript of table name in the next stage table of current table be object table, determine in the current table with mesh
The item number for recording corresponding record in mark table simultaneously judges whether this number is less than the threshold value precalculated, if it is,
Determining needs the corresponding object table of increased record and will need in increased record write-in to current table, if it is not, then in current table
The subscript of next stage table establishment table name adds the table of predetermined interval value equal to the subscript of object table, and determining needs increased record is corresponding to create
The table built will simultaneously need in increased record write-in to current table；Wherein, each table all has different from other tables in multi-level table
Table name, table name include corresponding subscript, and the table name of the next stage table of other any level tables in addition to afterbody table according to
The priority creation time of table, which corresponds to, chooses ascending subscript；
When needing the when of increasing record to afterbody table to determine, the afterbody table is current table, and directly record is written
Into current table.
Preferably, determine that the maximum table of the subscript of table name in the next stage table of current table is object table, including：
It obtains and the first order table recorded in increased record is needed to correspond to identification information, and consistency is done 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 the corresponding table of 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 the corresponding multi-level table of database, further include：
It is corresponded between identification information and table name using the Hash structures of Redis as buffer memory carrier to record every grade of table
Correspondence is to realize corresponding inquiry.
Preferably, it precalculates to obtain threshold value, including：
Determine that the maximum size of current table records for MS items, 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 items of record in the upper level table of a current table, determines that current table is corresponding
Threshold value is MS/M.
Preferably, after obtaining the corresponding multi-level table of database, further include：
When needing to inquire which of next stage table of corresponding this grade of table of certain record table in certain grade of table, then needs are obtained
The table name of the next stage carried in certain record of inquiry needs certain inquired to record corresponding next stage table to determine.
A kind of dynamic capacity-expanding system of database table, including：
Dilatation module is stored, is used for：When data capacity reaches the case where single library can not carry, calculated using consistency Hash
Method combination dummy node mode realizes database purchase dilatation and obtains the corresponding multi-level table of database；Wherein, first order table is removed
Except every level-one table in be stored with and record corresponding record with every in the upper level table per level-one table；
First table dilatation module, is used for：When needing to increase other any level tables of record extremely in addition to afterbody table
It determines that any level table is current table, determines that the maximum table of the subscript of table name in the next stage table of current table is object table,
Determine in the current table with the item number for recording corresponding record in object table and judge this number whether be less than precalculate
Obtained threshold value, if it is, determining needs the corresponding object table of increased record and will need in increased record write-in to current table,
If it is not, then the next stage table in current table creates the subscript of table name and is equal to the subscript of object table and adds the table of predetermined interval value, really
Surely it needs the corresponding table created of increased record and will need in increased record write-in to current table；Wherein, each table in multi-level table
The table name different from other tables is all had, table name includes corresponding subscript, and other any level tables in addition to afterbody table
The table name of next stage table corresponded to according to the priority creation time of table and choose ascending subscript；
Second table dilatation module, is used for：When needing the when of increasing record to afterbody table to determine, the afterbody table is to work as
Preceding table, and directly will be in record write-in to current table.
Preferably, the first table dilatation module includes：
Object table determining module, is used for：It obtains and the first order table recorded in increased record is needed to correspond to identification information, and
Consistency 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 library,
And determine that the corresponding table of the subscript is object table, wherein subscript counting dictionary table, which is the record being pre-created, corresponding data
Under library in addition to first order table in other every grade table it is corresponding maximum subscript.
Preferably, further include：
Logging modle is used for：After obtaining the corresponding multi-level table of database, using the Hash structures of Redis as depositing
Storage carrier corresponds to the correspondence between identification information and table name to realize corresponding inquiry to record every grade of table.
Preferably, further include：
Threshold calculation module is used for：Determine that the maximum size of current table records for MS items, the upper level table based on current table
Data relationship between current table determines the note in the maximum corresponding current table of M items of the record in the upper level table of a current table
Record determines that the corresponding threshold value of current table is MS/M.
Preferably, further include：
Enquiry module is used for：After obtaining the corresponding multi-level table of database, when needing to inquire in certain grade of table certain record pair
Should grade table which of next stage table table when, then obtain the table name for the next stage for needing to be carried in certain inquired record,
Certain inquired is needed to record corresponding next stage table to determine.
The present 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 case where single library can not carry, realize that database purchase expands using consistency hash algorithm combination dummy node mode
Hold and obtains the corresponding multi-level table of 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, determines that the maximum table of the subscript of table name in the next stage table of current table is object table, really
In the fixed current table with the item number for recording corresponding record in object table and judge this number whether less than precalculating
The threshold value arrived, if it is, determining needs the corresponding object table of increased record and will need in increased record write-in to 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, and determining needs the corresponding wound of increased record
The table built will simultaneously need in increased record write-in to current table；Determine this last when needing the when of increasing record to afterbody table
Grade table is current table, and directly will be in record write-in to current table.In technical solution disclosed in the present application, when data capacity reaches
When the case where single library can not carry, realizes database purchase dilatation using consistency hash algorithm combination dummy node mode and obtain
To the corresponding multi-level table of 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 the when of increasing new record to afterbody table is being needed to be directly added into i.e.
Can, to which Pass through above-mentioned technical proposal effectively realizes the dilatation of table.
Description of the drawings
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 technology description to be briefly described, it should be apparent that, the accompanying drawings in the following description is only this
The embodiment of invention for those of ordinary skill in the art without creative efforts, can also basis
The attached drawing of offer obtains other attached 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 realizing 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 schematic diagram of the dynamic capacity-expanding system of database table provided in an embodiment of the present invention.
Specific implementation mode
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 describes, 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.
Referring to Fig. 1, it illustrates a kind of flows of the dynamic capacity-expanding method of database table provided in an embodiment of the present invention
Scheme, may include：
S11：When data capacity reaches the case where single library can not carry, consistency hash algorithm combination dummy node is used
Mode realizes database purchase dilatation and obtains the corresponding multi-level table of database.
Wherein database purchase dilatation is realized using consistency hash algorithm combination dummy node mode and obtain database
The realization principle of corresponding multi-level table is identical as technical solution is corresponded in the prior art；It is illustrated by taking Fig. 2 as an example, for any
Database, first order table are table directly corresponding with the database, are stored with the corresponding record of database in first order table, such as
T_A；Second level table is table directly corresponding with first order table, and every is stored in first order table in the table of the second level and is recorded
Thinner record, such as T_B, and so on；It is one-to-one relationship that first order table, which can generally be arranged, with database, also may be used certainly
To carry out other settings according to actual needs, within protection scope of the present invention.The data illustrated between table not at the same level
Relationship illustrates, and if what level-one 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 the when of increasing other any level tables of record extremely in addition to afterbody table
Current table determines that the maximum table of the subscript of table name in the next stage table of current table is object table, determines in the current table
With the item number for recording corresponding record in object table and judge whether this number is less than the threshold value that precalculates, if
It is, it is determined that need the corresponding object table of increased record and will need in increased record write-in to current table, if it is not, then creating table
The subscript of name adds the table of predetermined interval value equal to the subscript of object table, determines the table for needing the corresponding establishment of increased record and will need to increase
The record added is written into current table.
Each table has the table name of oneself, which includes corresponding subscript, if the T_A_001 in Fig. 2 is table
The table name of T_A, wherein 001 is the subscript that the table name includes, in addition the table name of different tables is different, and for any level table
For, the subscript that includes 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, which corresponds to subscript, can differ the predetermined interval value (such as 1) set according to actual needs,
Can the table that creates of different adjacent times corresponds to subscript and differs different value, within protection scope of the present invention.Wherein, with work as
The corresponding threshold value of preceding table precalculates to obtain according to actual needs, determine the record for including in object table in current table
The correspondence of record is recognized if being less than corresponding threshold value with the item number for recording corresponding record in object table in the current table
Therefore capacity for that can also provide the record more refined in object table for the record increased newly in current table will directly need to increase
The record added is added into current table, and determines that the record corresponds to the record more refined and stores 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 next stage table of current table, and determine increased to record that there is correspondence with the table of the establishment, increased record will be needed to add
Enter into current table.
S13：When needing the when of increasing record to afterbody table to determine, the afterbody table is current table, and directly will record
In write-in to current table.
If necessary to increase new record in afterbody table, then it is directly added in afterbody table.Specifically
For, per the corresponding identification information of level-one table on afterbody table that can be by needing to obtain its carrying in increased record,
Identification information corresponded to first order table do consistency Hash and obtain corresponding database, and by first order table according to the first order pair
It should identify and inquire a first order table record, and value obtains it from the field of storage association table name in first order table record
The value of contingency table file-name field, the value are that the subscript of corresponding second level table is recorded with this bar first order table；It is corresponded to by the subscript
The corresponding record of mark corresponding with the second level table carried is determined in the table of the second level, and obtains the value of its contingency table file-name field,
The value is that the subscript of corresponding third level table is recorded with this second level table；And so on, until determining that new record needs
The afterbody table increased to, and the new record is increased in corresponding table.
In technical solution disclosed in the present application, when data capacity reaches the case where single library can not carry, consistency is used
Hash algorithm combination dummy node mode realizes database purchase dilatation and obtains the corresponding multi-level table of database；And it is needing
When increasing other grade table of the new record extremely in addition to afterbody table, determine whether 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 create the addition of realization new record after new next stage table, and
The when of increasing new record to afterbody table is being needed to be directly added into, to which Pass through above-mentioned technical proposal effectively realizes
The dilatation of table.
A kind of dynamic capacity-expanding method of database table provided in an embodiment of the present invention determines table in the next stage table of current table
The maximum table of subscript of name is object table, may include：
It obtains and the first order table recorded in increased record is needed to correspond to identification information, and consistency is done 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 the corresponding table of 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 being designed in each database and counting dictionary table (T_INDEX), correspondence is stored in the dictionary table
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 corresponds to 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 the ID that above-mentioned described identification information can be corresponding record in table can be specifically arranged 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 level-one table, and the record in first order table can carry the identification information of itself, in Fig. 2
It needs to carry the ID that energy Hash is routed to corresponding record in the T_A tables of certain database in the records 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；And so on；Wherein each ID is this note
Record corresponds to the location index information of table in the database.So as to by the identification information consistency Hash operation carried in recording
The identification information (can be ID) of correspondence database is obtained, the positioning for database and the inquiry of related data are facilitated.
A kind of dynamic capacity-expanding method of database table provided in an embodiment of the present invention, obtain the corresponding multi-level table of database it
Afterwards, can also include：
It is corresponded between identification information and table name using the Hash structures of Redis as buffer memory carrier to record every grade of table
Correspondence is to realize quick search.
In order to reach in practical applications better performance requirement, can use cache accelerate record identification information with it is right
It answers the search speed between table name, buffer memory carrier is used as with the Hash structures of Redis 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
Then information corresponds to identification information consistency Hash by first order table and obtains the database of corresponding record storage, then passes through
The Hash structure commands of Redis：hget DB:The identification information of database reference numeral first order table gets value, which 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 operations such as increasing can specifically include to the operation of record, delete, change, look into.Redis is one and increases income
Write using ANSI C languages, support network, can based on memory also can the log type of persistence, in Key-Value structures
Deposit data library possesses more rich data structure, and provides the API of multilingual.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 may include using process：
When operating the record of T_B_011 tables, data are obtained by father's business relations T_A_ID consistency Hash of T_B_ID
Then library N-1 passes through the Hash structure commands of Redis：hget DB:N-1T_A_ID gets value T_B_011.
When operating the record of T_C_023 tables, data are obtained by father's business relations T_A_ID consistency Hash of T_B_ID
Then library N-1 passes through the Hash structure commands of Redis：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, the threshold value precalculated can be with
Including：
Determine that the maximum size of current table records for MS items, 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 items of record in the upper level table of a current table, determines that current table is corresponding
Threshold value is MS/M.
It should be noted that corresponding single database, it can be in conjunction with reserved disk space size, database server I/O
Performance height, to the high low soft hardware performance index of database data read-write frequency, closed further according to the data between table and table
System (order table VS order detail lists, an order record correspond to the order detail list record upper limit as 30W items as assumed) determines
The data capacity upper limit of single table, specifically, soft hardware performance index generally corresponding with single database are capable of providing to this
Database corresponds to the space tool of table there are one maximum limit, and then can set the database according to actual needs 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 be such as arranged same business
Data correspond to N record storage in same tables of data, specifically can be by each first order table and multiple business pair
Data are answered to be set as one-to-one relationship, i.e., each first order table storage corresponds to business datum and inferior relation (specifically includes down
Grade table name), corresponding, the maximum data capacity that can have the business estimated is as the data capacity of corresponding first order table
The upper limit estimates the record strip number of the maximum corresponding second level table of a record of first order table based on the case where business datum, 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, and so on, to ensure that the corresponding N items record of the data of same business is deposited
Storage is in same tables of data.It is illustrated with each table that Fig. 2 includes：
One database one of T_A tables is opened, maximum size MS setting 1000W items records；
T_B_001--T_B_* tables, single table maximum size MS settings 1000W items record, according to data cases, a T_A table
The maximum corresponding M T_B table record of record, i.e. the correspondence T_A tables record number that a T_B table can be stored is that MS/M=N T_A table is remembered
Record；
T_C_001--T_C_*, single table maximum size MS set 2000W, and according to data cases, a T_B tables record is maximum
Corresponding M T_C table record, i.e. the correspondence T_B tables record number that a T_C table can be stored is that MS/M=N T_B table records.
A kind of dynamic capacity-expanding method of database table provided in an embodiment of the present invention, obtain the corresponding multi-level table of database it
Afterwards, can also include：
When needing to inquire which of next stage table of corresponding this grade of table of certain record table in certain grade of table, then needs are obtained
The table name of the next stage carried in certain record of inquiry needs certain inquired to record corresponding next stage table to determine.
It should be noted that 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, has 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 has recorded its associated traffic data in each record in T_B_001 tables and deposits
Table name (such as T_C_005) in certain table of T_C, and so on.To realize the fast quick checking of certain record place watch
It askes.
Otherwise for above-mentioned technical proposal disclosed in the present application, it is specifically described by taking Fig. 2 as an example：
1, routing rule：
The industry of the T_A tables of certain database is routed in the records such as Object_1, Object_2 firstly the need of carrying energy hash
Business id information T_A_ID, the wherein records such as Object_1, Object_2 are one and refer 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；T_C tables are written
Record need carry T_A_ID, T_B_ID；Other hierarchical relationship tables and so on.
2, it increases a T_A tables record newly and (needs to increase record in corresponding above-described embodiment in addition to afterbody table
When other any level tables)：
Consistency 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 the corresponding maximum subscripts of inquiry T_B in dictionary table (T_INDEX) from the subscript of obtained database (is such as
010)；
Record in T_B_010 tables is based on there are one the field record Record ID (T_A_ID) of corresponding T_A tables
Statistics SQL (SELECT COUNT (DISTINCT T_A_ID) FROM T_B_010) obtains statistical data Q；
If Q<N (N is the corresponding threshold value of T_A tables), then illustrate that current T_B_010 is not up to the anticipated capability upper limit, then will
Addition contingency table file-name field content is " T_B_010 " when T_A tables are written in the record；
If Q>=N then illustrates that current T_B_010 reaches the anticipated capability upper limit, then dynamic creation T_B_011 tables, and will
Addition contingency table file-name field content is " T_B_011 " (paying attention to no record in T_B_011 tables at this time) when T_A tables are written in the record,
And it updates and is designated as 011 under the corresponding maximums of T_B in subscript counting dictionary table (T_INDEX).
3, it increases a T_B tables record newly and (needs to increase record in corresponding above-described embodiment in addition to afterbody table
When other any level tables)：
Consistency Hash operation is done to the T_A_ID carried in newly-increased record and obtains corresponding database；
Corresponding one unique TA record is inquired from T_A tables according to the value of T_A_ID, is obtained in being recorded by the TA
The value (such as T_B_011) of the contingency table file-name field of maintenance；
The corresponding maximum subscripts of inquiry T_C in dictionary table (T_INDEX) are 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
Addition contingency table file-name field content is " T_C_058 " when T_B records are written in T_B_011 tables；
If Q>=N then illustrates that current T_C_058 reaches the anticipated capability upper limit, then dynamic creation T_C_059 tables, and will
Addition contingency table file-name field content is that " T_C_059 " (pays attention in T_C_059 tables at this time without note when T_B_011 tables are written in the record
Record), and update and be designated as 059 under the corresponding maximums of T_C in subscript counting dictionary table (T_INDEX).
4, increase a T_C tables record newly (when needing to increase record to afterbody table)
By needing to obtain its T_A_ID, T_B_ID for carrying in newly-increased record；
Consistency Hash is done to T_A_ID and obtains corresponding database；
One T_A record is inquired according to T_A_ID from T_A tables, and storage is associated with the field of table name from T_A records
Middle value obtains the value T_B_XXX (determining to record corresponding T_B tables with T_A) 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
It makes and records corresponding T_C tables with T_B)；
Newly-increased record is written in T_C_XXX tables.
5, data query：The lower level data of inquiry needs to take the id information of all parents.
Inquire the record in first order table, it may be determined that this records corresponding identification information and by being found in first order table
Record corresponding with the mark；Corresponding diagram 2, inquiry T_A tables record, needs clear T_A_ID, is led as inquiry according to T_A_ID
Key finds the corresponding record in T_A tables；
Inquire 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
Then information determines corresponding record of the identification information in first order table, then determines the corresponding second level 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, and so on, directly
Until finding the record.Corresponding diagram 2, inquiry T_B tables record, needs clear T_A_ID, (T_B_ID is optional), first basis
T_A_ID finds the corresponding record (such as XX) in T_A tables as inquiry major key, then is taken out from the associate field of record XX
Value (such as YY), value YY is a certain specific table (such as T_B_011) of T_B, finally, it can according to T_B_ID from T_B_
Corresponding T_B records are inquired in 011 table；
T_C tables record is inquired, needs clear 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 obtains corresponding T_B_XXX tables from T_A records, then is looked into from T_B_XXX tables with T_B_ID
It is recorded to a T_B, then corresponding T_C_XXX tables is obtained from T_B records, then one is found from T_C_XXX tables with T_C_ID
T_C record.
If any more levels, ibid scheme.It is further to note that being 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, third
T_A tables also within protection scope of the present invention, are also corresponded to first order table by grade 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
It is packaged mortgage process in the assets of the consumer finance.Assets packing process is related under an assets packet that there may be N number of assets are bright
Carefully (also referred to as contract), each assets are detailed again, and there may be the refund of N items to record.The paging that can sort is needed for inquiry angle
Check that all assets under an assets packet are detailed, energy paging checks that all refund under an assets detail record.From update
The field data of a plurality of assets detail record in an affairs under one assets packet of batch updating is needed for angle.If
It can lead to the assets under an assets packet using based on simple modulus hash algorithm or consistency Hash+dummy node algorithm
Detail can be stored in by dispersion in multiple tables in multiple libraries due to balance.Thus cause there arises a problem that：
Difficulty data is checked in sequence paging, needs to be traversed for all assets detail lists in all libraries, 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 after using scheme proposed in this paper, it will solve these problems.
It is illustrated in conjunction with Fig. 3, in the assets packet business scenario of the consumer finance, fastest-rising two business of data volume
Table is exactly assets detail list and refund record sheet, and 1 assets packet corresponds to about 20W assets detail (contract), and 1 contract corresponds to about
30 records of refunding, i.e., 1 assets packet ≈ 20W contract ≈ 600W item, which is refunded, records.
Capacity planning：
Single table capacity delimit principle：A large amount of inquiries, a small amount of newer table about 1000W records/open, are subjected to the upper limit and float
20% i.e. 1200W/；The table of frequent updating about 500W records/, it is subjected to upper limit floating 20% i.e. 600W/；So refunding
Detail list (apm_repay_detail) 1000W/, assets packet detail list (apm_detail) 500W/.
Limit the record that an assets packet detail list only deposits 25 assets packets.[25 assets packets of 500W/20W ≈]
Limit the record that 2 assets packets are only deposited in a refund record sheet.[1000W/ (20W contracts * 30 refunds detailed)
2 assets packets of ≈]
Library table is split：1W assets packet can be reached after being expected 1 year according to practical business development, you can extrapolate needs
1W/25=400 assets packet detail lists, 1W/2=5000 refund record sheets.Further according to data library disk, network I/O processing
Ability initial setting virtual data base node 128, actual database node 16 limit single library 400 and open table or so.
Data are route and operation：
Operating personnel when system increases an assets packet newly, do according to the cryptographic Hash of assets packet number with modulus 128 by system
Modulo operation, which obtains it, should be stored in the assets packet table of some database；Then the assets packet is determined by counting, calculating
The table name specifically stored of assets detail record, there is no then systems actively to create for table, includes finally providing by assets packet record
Production detail record table name is stored in together in assets packet table.
Operating personnel need to specify an affiliated assets packet when system increases an assets detail item (contract) newly.Pass through
Assets packet number determines the table name of database and 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 stored, there is no the actively establishments of then system for table, finally by the assets detail record packet
The table name for including storage refund flowing water is stored in together in assets detail list.
User determines that database and corresponding storage assets are bright after system amortizes a flowing water, by assets packet number
Thin table name is determined the table name of storage refund record by assets detailed (contract) number, refund flowing water is then inserted into refund
In record sheet.
After implementing scheme proposed in this paper, following beneficial effect has been reached in the project：
The assets detail of one assets packet and record of refunding in a table, are being convenient for quick paging and aggregate number respectively
According to；
Support batch updating to an assets detail list or refund record sheet data, delete operation；
Because all data are in a table, the scene of distributed transaction is not present；
It is in frozen state due to the refund of assets detail record will not change again after a certain time, the table is just not
Can there are insertion and update operation again, also be not present and split the case where moving the table data because of business development.
The embodiment of the present invention additionally provides a kind of dynamic capacity-expanding system of database table, as shown in figure 4, may include：
Dilatation module 11 is stored, is used for：When data capacity reaches the case where single library can not carry, consistency Hash is used
Algorithm combination dummy node mode realizes database purchase dilatation and obtains the corresponding multi-level table of database；
First table dilatation module 12, is used for：It is recorded to other any level tables in addition to afterbody table when needing increase
When determine any level table be current table, determine the maximum table of the subscript of table name in the next stage table of current table be target
Table, determine in the current table with the item number for recording corresponding record in object table and judge this number whether be less than in advance meter
Obtained threshold value, if it is, determining needs 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 for creating table name adds the table of predetermined interval value equal to the subscript of object table, determining needs increased record pair
The table that should create will simultaneously need in increased record write-in to current table；
Second table dilatation module 13, is used for：Determine that the afterbody table is when needing the when of increasing record to afterbody table
Current table, and directly will be in record write-in to 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 may include：
Object table determining module, is used for：It obtains and the first order table recorded in increased record is needed to correspond to identification information, and
Consistency 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 library,
And determine that the corresponding table of the subscript is object table, wherein subscript counting dictionary table, which is the record being pre-created, corresponding data
Under library in addition to first order table in other every grade table it is corresponding maximum subscript.
A kind of dynamic capacity-expanding system of database table provided in an embodiment of the present invention can also include：
Logging modle is used for：After obtaining the corresponding multi-level table of database, using the Hash structures of Redis as depositing
Storage carrier corresponds to the correspondence 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 is used for：Determine that the maximum size of current table records for MS items, the upper level table based on current table
Data relationship between current table determines the note in the maximum corresponding current table of M items of the record in the upper level table of a current table
Record determines that the corresponding threshold value of 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 is used for：After obtaining the corresponding multi-level table of database, when needing to inquire in certain grade of table certain record pair
Should grade table which of next stage table table when, then obtain the table name for the next stage for needing to be carried in certain inquired record,
Certain inquired is needed to record corresponding next stage table to determine.
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 in a kind of dynamic capacity-expanding method for database table that inventive embodiments provide, details are not described herein.
In addition part consistent with realization principle in the prior art in above-mentioned technical proposal provided in an embodiment of the present invention is not specifically
It is bright, in order to avoid excessively repeat.
The foregoing description of the disclosed embodiments enables those skilled in the art to realize or use the present invention.To this
A variety of modifications of a little embodiments will be apparent for a person skilled in the art, and the general 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
It is formed on the embodiments shown herein, and is to fit to consistent with the principles and novel features disclosed in this article widest
Range.
Claims (10)
1. a kind of dynamic capacity-expanding method of database table, which is characterized in that including：
When data capacity reaches the case where single library can not carry, realized using consistency hash algorithm combination dummy node mode
Database purchase dilatation simultaneously obtains the corresponding multi-level table of database；Wherein, it is stored in every level-one table in addition to first order table
Corresponding record is recorded with every in the upper level table per level-one table；
When needing the when of increasing other any level tables of record extremely in addition to afterbody table to determine, any level table is current table, really
In the next stage table of settled preceding table the maximum table of the subscript of table name be object table, determine in the current table in object table
The item number for recording corresponding record and judge whether this number is less than the threshold value that precalculates, if it is, determining needs
The corresponding object table of increased record will simultaneously need in increased record write-in to current table, if it is not, then in the next stage of current table
The subscript of table establishment table name adds the table of predetermined interval value equal to the subscript of object table, determines the table for needing increased record to correspond to establishment
And it will need in increased record write-in to current table；Wherein, each table all has the table name different from other tables, table in multi-level table
Name includes corresponding subscript, and the table name of the next stage table of other any level tables in addition to afterbody table is according to the priority of table
Creation time, which corresponds to, chooses ascending subscript；
When needing the when of increasing record to afterbody table to determine, the afterbody table is current table, and directly will record write-in to working as
In preceding table.
2. according to the method described in claim 1, it is characterized in that, the subscript of table name is maximum in the next stage table of determining current table
A table be object table, including：
It obtains and the first order table recorded in increased record is needed to correspond to identification information, and consistency Hash is done to the identification information
Operation obtains the identification information of correspondence database；
It is corresponded in the subscript counting dictionary table that real-time update is safeguarded in database by obtained identification information and obtains the current of record
Corresponding maximum subscript in the next stage table of table, and determine that the corresponding table of the subscript is object table, wherein the subscript counts word
Allusion quotation table, which is the record being pre-created, under correspondence database in addition to first order table corresponding maximum subscript in other every grade table.
3. according to the method described in claim 2, it is characterized in that, after obtaining the corresponding multi-level table of database, further include：
Recorded using the Hash structures of Redis as buffer memory carrier every grade of table correspond to it is corresponding between identification information and table name
Relationship is to realize corresponding inquiry.
4. according to the method described in claim 1, it is characterized in that, precalculate to obtain threshold value, including：
Determine that the maximum size of current table records for MS items, 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 items of record in the upper level table of a fixed current table, determines the corresponding threshold value of current table
For MS/M.
5. according to the method described in claim 4, it is characterized in that, after obtaining the corresponding multi-level table of database, further include：
When needing to inquire which of next stage table of corresponding this grade of table of certain record table in certain grade of table, then obtains and need to inquire
Certain record in the table name of next stage that carries, record corresponding next stage table to determine certain that needs inquire.
6. a kind of dynamic capacity-expanding system of database table, which is characterized in that including：
Dilatation module is stored, is used for：When data capacity reaches the case where single library can not carry, consistency hash algorithm knot is used
Dummy node mode is closed to realize database purchase dilatation and obtain the corresponding multi-level table of database；Wherein, in addition to first order table
Every level-one table in be stored with and record corresponding record with every in the upper level table per level-one table；
First table dilatation module, is used for：It is determined when needing the when of increasing other any level tables of record extremely in addition to afterbody table
Any level table is current table, determines that the maximum table of the subscript of table name in the next stage table of current table is object table, determines
In the current table with the item number for recording corresponding record in object table and judge this number whether be less than precalculate to obtain
Threshold value, if it is, determining needs the corresponding object table of increased record and increased record will be needed to be written into current table, if
No, then the subscript that table name is created in the next stage table of current table adds the table of predetermined interval value equal to the subscript of object table, and determining needs
The table of the increased corresponding establishment of record will simultaneously need in increased record write-in to current table；Wherein, each table has in multi-level table
There is the table name different from other tables, table name includes corresponding subscript, and under other any level tables in addition to afterbody table
The table name of level-one table corresponds to according to the priority creation time of table and chooses ascending subscript；
Second table dilatation module, is used for：When needing the when of increasing record to afterbody table to determine, the afterbody table is current table,
And it directly will be in record write-in to current table.
7. system according to claim 6, which is characterized in that the first table dilatation module includes：
Object table determining module, is used for：It obtains and the first order table recorded in increased record is needed to correspond to identification information, and to this
Identification information does consistency Hash operation and obtains the identification information of correspondence database；It is corresponded in database 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, and really
The corresponding table of the fixed subscript is object table, is had under correspondence database wherein subscript counting dictionary table is the record being pre-created
The corresponding maximum subscript in other every grade table in addition to first order table.
8. system according to claim 7, which is characterized in that further include：
Logging modle is used for：After obtaining the corresponding multi-level table of database, carried using the Hash structures of Redis as buffer memory
Body corresponds to the correspondence between identification information and table name to realize corresponding inquiry to record every grade of table.
9. system according to claim 6, which is characterized in that further include：
Threshold calculation module is used for：Determine the maximum size of current table for MS items record, based on the upper level table of current table with work as
Data relationship between preceding table determines the record in the current table of the maximum corresponding M items of record in the upper level table of a current table, really
The corresponding threshold value of settled preceding table is MS/M.
10. system according to claim 9, which is characterized in that further include：
Enquiry module is used for：After obtaining the corresponding multi-level table of database, when needing to inquire in certain grade of table, certain record correspondence should
When which of the next stage table of grade table table, then the table name for the next stage for needing to be carried in certain inquired record is obtained, with true
Certain inquired is needed to record corresponding next stage table calmly.
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 CN107633097A (en) | 2018-01-26 |
| CN107633097B true 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) |
Families Citing this family (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110928900B (en) * | 2018-09-17 | 2021-02-19 | 马上消费金融股份有限公司 | Multi-table data query method, device, terminal and computer storage medium |
| CN110059080B (en) * | 2019-04-29 | 2021-12-14 | 珠海天燕科技有限公司 | Data processing method and device |
| CN111782628A (en) * | 2020-06-11 | 2020-10-16 | 国电南京自动化股份有限公司 | Method, configuration tool and system for dynamically expanding increment of real-time base of power system |
| CN112783860B (en) * | 2021-01-11 | 2023-08-08 | 网易（杭州）网络有限公司 | Method, device, storage medium and computer equipment for constructing mirror image database |
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 |
Also Published As
| Publication number | Publication date |
|---|---|
| CN107633097A (en) | 2018-01-26 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107633097B (en) | A kind of dynamic capacity-expanding method and system of database table | |
| CN110334154A (en) | Based on the classification storage method and device of block chain, electronic equipment | |
| US9177004B2 (en) | Balancing data across partitions of a table space during load processing | |
| CN107644071A (en) | Buffering updating method, device and data-storage system | |
| CN110347684A (en) | Based on the classification storage method and device of block chain, electronic equipment | |
| CN106407303A (en) | Data storage method and apparatus, and data query method and apparatus | |
| CN107066393A (en) | The method for improving map information density in address mapping table | |
| JP2004518225A (en) | Data structures for information systems | |
| CN108228649A (en) | For the method and apparatus of data access | |
| CN110347660A (en) | Based on the classification storage method and device of block chain, electronic equipment | |
| CN103577440A (en) | Data processing method and device in non-relational database | |
| CN108287840A (en) | A kind of data storage and query method based on matrix Hash | |
| CN103473298B (en) | Data archiving method and device and storage system | |
| CN106406759A (en) | Data storage method and device | |
| CN104021223B (en) | The access method and device of a kind of Cluster Database measuring point | |
| US7020782B2 (en) | Size-dependent hashing for credit card verification and other applications | |
| CN107426315A (en) | A kind of improved method of the distributed cache system Memcached based on BP neural network | |
| CN106383826A (en) | Database checking method and apparatus | |
| CN109408416B (en) | Address mapping table entry page management method and device | |
| CN110489418A (en) | A kind of data aggregation method and system | |
| CN106844491A (en) | A kind of write-in of ephemeral data, read method and write-in, reading device | |
| CN102436479B (en) | Method and system for generating globally unique data identifier | |
| CN107102900A (en) | A kind of management method of shared memory space | |
| EP3945694A1 (en) | Block data access method, block data storage method, and apparatuses thereof | |
| US7756854B2 (en) | Minimization of calculation retrieval in a multidimensional database |
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 |