CN110287183A - Processing method, device, computer equipment and the storage medium of database table water level - Google Patents
Processing method, device, computer equipment and the storage medium of database table water level Download PDFInfo
- Publication number
- CN110287183A CN110287183A CN201910432704.9A CN201910432704A CN110287183A CN 110287183 A CN110287183 A CN 110287183A CN 201910432704 A CN201910432704 A CN 201910432704A CN 110287183 A CN110287183 A CN 110287183A
- Authority
- CN
- China
- Prior art keywords
- database
- object table
- statistical information
- water level
- data
- 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
- XLYOFNOQVPJJNP-UHFFFAOYSA-N water Substances O XLYOFNOQVPJJNP-UHFFFAOYSA-N 0.000 title claims abstract description 59
- 238000003860 storage Methods 0.000 title claims abstract description 31
- 238000003672 processing method Methods 0.000 title claims abstract description 25
- 238000004140 cleaning Methods 0.000 claims abstract description 23
- 238000000034 method Methods 0.000 claims abstract description 23
- 238000005192 partition Methods 0.000 claims description 45
- 238000012545 processing Methods 0.000 claims description 19
- 238000004590 computer program Methods 0.000 claims description 18
- 238000007599 discharging Methods 0.000 claims description 9
- 238000005201 scrubbing Methods 0.000 claims description 4
- 230000006870 function Effects 0.000 description 8
- 230000008569 process Effects 0.000 description 8
- 238000010586 diagram Methods 0.000 description 7
- 239000012634 fragment Substances 0.000 description 7
- 230000008859 change Effects 0.000 description 3
- 230000001965 increasing effect Effects 0.000 description 3
- 241001269238 Data Species 0.000 description 2
- 238000013500 data storage Methods 0.000 description 2
- 238000012217 deletion Methods 0.000 description 2
- 230000037430 deletion Effects 0.000 description 2
- 230000000694 effects Effects 0.000 description 2
- 238000009825 accumulation Methods 0.000 description 1
- 230000009471 action Effects 0.000 description 1
- 210000004556 brain Anatomy 0.000 description 1
- 238000004364 calculation method Methods 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 230000010485 coping Effects 0.000 description 1
- 238000007418 data mining Methods 0.000 description 1
- 230000007423 decrease Effects 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 238000009826 distribution Methods 0.000 description 1
- 230000005611 electricity Effects 0.000 description 1
- 230000002708 enhancing effect Effects 0.000 description 1
- 238000003780 insertion Methods 0.000 description 1
- 230000037431 insertion Effects 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 238000002360 preparation method Methods 0.000 description 1
- 230000008439 repair process Effects 0.000 description 1
- 230000003068 static effect Effects 0.000 description 1
- 230000001360 synchronised effect Effects 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/215—Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
- G06F16/2272—Management thereof
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Quality & Reliability (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses processing method, device, computer equipment and the storage mediums of a kind of database table water level, are applied to database technical field, for solving the problem of that the existing method inefficiency for reducing database table water level is easy to happen omission.The method include that determining target database for clearance;The each table for meeting default cleaning condition is searched from the target database, as each object table for clearance;Clear up the data on each object table;Lock the statistical information of each object table;The index of each object table is rebuild respectively;Discharge the statistical information of each object table.
Description
Technical field
The present invention relates to database technical fields more particularly to the processing method of database table water level, device, computer to set
Standby and storage medium.
Background technique
Under the background of big data era, big data storage is due to being data mining and the premise utilized, to enterprise
Development it is more important.Meanwhile with the accumulation of mass data, big data storage also to enterprise bring increasing cost and
Government pressure.Therefore, useless data are cleared up in time with regard to most important.
Currently, the way of use is deleted by sql command timing when clearing up useless or fail data.However, this
Although mode can simply, directly realize the deletion of data, as the operation to table in database increases, the water level of table
It can increase therewith, and then lead to the performance decline of table, database.Although existing way can arrange special data base administrator
The water level of table is reduced manually, but efficiency is lower, is easy to happen omission, while also increasing human cost burden to enterprise.
Summary of the invention
The embodiment of the present invention provides processing method, device, computer equipment and the storage medium of a kind of database table water level,
To solve the problem of that the existing method inefficiency for reducing database table water level is easy to happen omission.
A kind of processing method of database table water level, comprising:
Determine target database for clearance;
The each table for meeting default cleaning condition is searched from the target database, as each target for clearance
Table;
Clear up the data on each object table;
Lock the statistical information of each object table;
The index of each object table is rebuild respectively;
Discharge the statistical information of each object table;
Wherein, the target database includes more than two partition holdings, described to search from the target database
Meet each table for presetting cleaning condition, includes: as each object table for clearance
The last time for reading each partition holding in the target database uses the time;
The last storage for meeting using the time and presetting using Timeout conditions is determined from each partition holding
Subregion;
All tables in determining partition holding are determined as to each object table for clearance.
A kind of processing unit of database table water level, comprising:
Database determining module, for determining target database for clearance;
Object table searching module is made for searching each table for meeting default cleaning condition from the target database
For each object table for clearance;
Table data scrubbing module, the data for clearing up on each object table;
Statistical information locks module, for lockking the statistical information of each object table;
Table index rebuilds module, for rebuilding the index of each object table respectively;
Statistical information release module, for discharging the statistical information of each object table;
Wherein, the target database includes more than two partition holdings, and the object table searching module includes:
Using time reading unit, when for reading the last use of each partition holding in the target database
Between;
Subregion determination unit, for determining that the last preset using time satisfaction is made from each partition holding
With the partition holding of Timeout conditions;
First object table determination unit, for all tables in the partition holding determined to be determined as to each mesh for clearance
Mark table.
A kind of computer equipment, including memory, processor and storage are in the memory and can be in the processing
The computer program run on device, the processor realize the processing of above-mentioned database table water level when executing the computer program
The step of method.
A kind of computer readable storage medium, the computer-readable recording medium storage have computer program, the meter
Calculation machine program realizes the step of processing method of above-mentioned database table water level when being executed by processor.
Processing method, device, computer equipment and the storage medium of above-mentioned database table water level, firstly, determination is for clearance
Target database;Then, each table for meeting default cleaning condition is searched from the target database, as clearance
Each object table;Then, the data on each object table are cleared up;Lock the statistical information of each object table;In addition,
The index of each object table is rebuild respectively;Finally, the statistical information of release each object table.As it can be seen that the present invention is not
It is only able to achieve the automatic cleaning to the table for meeting default cleaning condition in data library, and the index of table can be rebuild simultaneously, is cleared up
Fall because indexing fragment caused by delete operation, reduces the water level of table, participate in without artificial, subtract while avoiding omitting
The human cost of Qing Liao enterprise is born;In addition, the present invention has also locked the statistical information of table, has kept away before rebuilding index to table
Exempting to rebuild to index causes the statistical information of table to change.
Detailed description of the invention
In order to illustrate the technical solution of the embodiments of the present invention more clearly, below by institute in the description to the embodiment of the present invention
Attached drawing to be used is needed to be briefly described, it should be apparent that, the accompanying drawings in the following description is only some implementations of the invention
Example, for those of ordinary skill in the art, without any creative labor, can also be according to these attached drawings
Obtain other attached drawings.
Fig. 1 is an application environment schematic diagram of the processing method of database table water level in one embodiment of the invention;
Fig. 2 is a flow chart of the processing method of database table water level in one embodiment of the invention;
Fig. 3 is the process method step 101 of database table water level in one embodiment of the invention under an application scenarios
Flow diagram;
Fig. 4 is the process method step 102 of database table water level in one embodiment of the invention under an application scenarios
Flow diagram;
Fig. 5 is the process method step 102 of database table water level in one embodiment of the invention under another application scenarios
Flow diagram;
Fig. 6 is that the processing method of database table water level in one embodiment of the invention verifies statistics letter under an application scenarios
The flow diagram of breath;
Fig. 7 is structural representation of the processing unit of database table water level in one embodiment of the invention under an application scenarios
Figure;
Fig. 8 is the structural schematic diagram of object table searching module in one embodiment of the invention;
Fig. 9 is that structure of the processing unit of database table water level in one embodiment of the invention under another application scenarios is shown
It is intended to;
Figure 10 is a schematic diagram of computer equipment in one embodiment of the invention.
Specific embodiment
Following will be combined with the drawings in the embodiments of the present invention, and technical solution in the embodiment of the present invention carries out clear, complete
Site preparation description, it is clear that described embodiments are some of the embodiments of the present invention, instead of all the embodiments.Based on this hair
Embodiment in bright, every other implementation obtained by those of ordinary skill in the art without making creative efforts
Example, shall fall within the protection scope of the present invention.
The processing method of database table water level provided by the present application, can be applicable in the application environment such as Fig. 1, wherein visitor
Family end is communicated by network with server.Wherein, which can be, but not limited to various personal computers, notebook electricity
Brain, smart phone, tablet computer and portable wearable device.Server can use the either multiple services of independent server
The server cluster of device composition is realized.
In one embodiment, it as shown in Fig. 2, providing a kind of processing method of database table water level, applies in this way
It is illustrated, includes the following steps: for server in Fig. 1
101, target database for clearance is determined;
In the present embodiment, for server firstly the need of target database for clearance is determined, server can be by automatic
Mode determine these target databases, specified data library can also be selected as clearance by client by user
Target database.For example, client is connect with the server communication, user can be on the interface of the client from multiple data
The database for selecting more than one database to need to clear up as this operation in library, so that server can select user
Database be determined as target database.
In addition, server can also determine target database automatically by preset configuration information, for ease of understanding, such as
Shown in Fig. 3, further, step 101 be can specifically include:
201, preconfigured failure configuration information is read;
202, search that there are the databases of fail data from each database according to the failure configuration information;
203, the database found is determined as to target database for clearance.
For above-mentioned steps 201, it is to be understood that failure configuration information can be provided on server in advance, the mistake
Effect configuration information is mainly used for judging whether the data in database are no longer valid or out-of-date, meets the data of failure configuration information
Useless data can be identified as, thus these useless data can be cleared up in the next steps.Specifically, which matches
Confidence breath is configurable to time conditions, and the storage time of data is once more than the duration limited in the time conditions, then can be with
Think that these data are fail data.
For above-mentioned steps 202, server can configure after reading the failure configuration information according to the failure
Information searches that there are the databases of fail data from each database.It is found that server need to only judge to deposit in which database
Corresponding database can be found out from these databases in fail data.Such as, it is assumed that the failure configuration information is set as
" storage time is more than that 1 month data is fail data ", according to the statistical information of database it is found that storing number in database A
It is on 2 1st, 2018 at the beginning of, is on March 1st, 2018, database C at the beginning of storing data in database B
At the beginning of middle storing data be on April 1st, 2018, present system time be on April 3rd, 2018, it is known that, database A and
There are fail datas in database B.
For above-mentioned steps 203, easily, finding out there are after the database of fail data, server can incite somebody to action
The database found is determined as target database for clearance.
102, each table for meeting default cleaning condition is searched from the target database, as each mesh for clearance
Mark table;
Server is after determining target database, it is also necessary to each mesh for clearance is found out from target database
Mark table.To be easy to implement this point, default cleaning condition can be preset on server, which defines which
A little tables are object table for clearance.Wherein, presetting cleaning condition can specifically be set according to actual use situation, such as can
To be set as " all data are the table of fail data for object table for clearance in table ".
Under certain application scenarios, it is contemplated that the problem of facilitating storage, database are provided with partition holding, each storage point
The data of different periods are pointedly stored between area.For this situation, the server of the present embodiment can be with partition holding
Object table is determined for unit, is applicable not only to the database of partitioned storage form, and improve the treatment effeciency of this method.
As shown in figure 4, further, the target database includes more than two partition holdings, step 102 be can specifically include:
301, the last time for reading each partition holding in the target database uses the time;
302, determine that the last preset using time satisfaction uses Timeout conditions from each partition holding
Partition holding;
303, all tables in determining partition holding are determined as to each object table for clearance.
For above-mentioned steps 301, it is to be understood that target database is in routine use, due to having divided storage point
Area, therefore different data are generally stored between different partition holdings, usually, when different partition holdings respectively store different
Between section data, for example, under an application scenarios, 3 partition holdings of target database be respectively subregion a, subregion b and point
The data in 2016 year of area c, subregion a storage, the data in 2017 year of subregion b storage, the data in 2018 year of subregion c storage.
Certainly, the use of partition holding can determines according to actual conditions, for example, the division of partition holding can also be directed to different numbers
Carried out according to type, for example, the business datum stored in target database can be divided into partition holding 1, employee's data are divided into
Partition holding 2, system data are divided into partition holding 3, etc..
For above-mentioned steps 302, when the configuration of server each partition holding in reading the target database uses
Between after, can determine that configuration meets the default storage using Timeout conditions point using the time from each partition holding
Area.Such as, it is assumed that the target database includes three partition holdings, respectively subregion 1, subregion 2 and subregion 3, the default use
Timeout conditions are " time of storing data was more than 1 month ", wherein the time for the latest data that subregion 1 stores is in March, 2018
2, the time for the latest data that subregion 2 stores was on March 15th, 2018, and the time for the latest data that subregion 3 stores is 2018
On March 22, in, present system time are on April 3rd, 2018, are easy to learn, all data that subregion 1 stores use the time equal
More than 1 month, thus may determine that all tables and all data have failed in the subregion 1.
For above-mentioned steps 303, it is to be understood that server is determining that the last time meets default make using the time
After the partition holding of Timeout conditions, all tables in determining partition holding can be determined as to each target for clearance
Table.
In the present embodiment, the object for clearing up data is table, therefore it is required that all data in the object table are failure
Data.As shown in figure 5, further, step 102 can specifically include:
401, preconfigured failure configuration information is read;
402, according to the failure configuration information from data are fail data in look-up table in the target database
Each table;
403, each table found is determined as to each object table for clearance.
For above-mentioned steps 401, it is to be understood that failure configuration information can be provided on server in advance, the mistake
Effect configuration information is mainly used for judging whether the data in table are no longer valid or out-of-date, and the data for meeting failure configuration information can be with
Useless data are identified as, thus these useless data can be cleared up in the next steps.Specifically, which matches confidence
Breath is configurable to time conditions, and the storage time of data is once more than the duration limited in the time conditions, it may be considered that
These data are fail data.
For above-mentioned steps 402, server can configure after reading the failure configuration information according to the failure
Information is from data are each table of fail data in look-up table in the target database.It is found that server is needed from the mesh
In all tables for marking database, each table that data in table are fail data is found out, therefore, server is needed to the target
Each table in database is checked.Such as, it is assumed that the failure configuration information is set as that " storage time is more than 1 month number
According to for fail data ", totally 3 tables, respectively table 1, table 2 and table 3 in the target database, the latest data that table 1 stores when
Between be on 2 1st, 2018, table 2 store latest data time be on March 1st, 2018, table 3 store latest data when
Between be on April 1st, 2018, present system time be on April 3rd, 2018, it is known that, all data in Tables 1 and 2 store surpasses
1 month is spent, is fail data, therefore server can find out Tables 1 and 2.
For above-mentioned steps 403, easily, server find out data in table be fail data each table it
Afterwards, each table found can be determined as to each object table for clearance, to carry out subsequent cleaning data manipulation.
103, the data on each object table are cleared up;
Server can clear up the data on these object tables after determining each object table.Specifically, server
Structured query language sql command can be used to realize the cleaning of data on table.
For ease of understanding, further, step 103 can specifically include:
501, respectively according to the structural generation of each object table be used for clear up data each SQL (structured query language,
Structured Query Language) order;
502, each sql command is executed respectively to clear up the data on each object table.
For above-mentioned steps 501, it is to be understood that be directed to each object table, server can be according to the object table
Structural generation is used to clear up the sql command of data.It illustrates, it is assumed that defrag subregion is PT_CCH_CREDT_
The total data that table on 201105 is TMRLIFEDATA.NTL_ASSIGNED_TASK, then can be generated sql command are as follows:
alter table TMRLIFEDATA.NTL_ASSIGNED_TASK truncate partition PT_CCH_CREDT_
201105drop storage.By the sql command, can clear up on TMRLIFEDATA.NTL_ASSIGNED_TASK table
All data.
For above-mentioned steps 502, after generation obtains these sql commands, server can execute each SQL life
Order respectively clears up the data on each object table.Specifically, server can execute these sql commands one by one,
Can also by the way of multithreading these sql commands of parallel processing, to finally realize to data on each object table
Cleaning.
It should be noted that object table is clean after the data in these object tables are cleared up, but the operation cleared up
So that there is a large amount of index fragment in the database, these index fragments can not be used by a user, and can make database for meeting
In water level increase, therefore the present embodiment also need subsequent step automatically by target database table water level reduce.
Wherein, about the water level of table, according to the definition of oracle, all oracle sections have one to accommodate number in section
According to the upper limit, industry this upper limit be known as " high water mark " or HWM.In oracle database, if a table
When making the operation such as frequent insert (insertion), delete (deletion), uodate (modification), this table can generate higher water
Position.The water level of table is high, and making table, performance declines when in use, and the time for operating watch also can be slow.
104, the statistical information of each object table is lockked;
It is understood that due in subsequent step server will clear up index fragment by rebuilding the operation of index,
The statistical information of object table can be destroyed when rebuilding index, this point, server can first be locked before step 105 in order to prevent
The statistical information of each object table is stated in residence, after the statistical information of object table is locked, even if carrying out reconstruction index to object table
Deng operation, these statistical informations will not change.
Specifically, server can execute sql command to lock the statistical information of each object table.For example, to
The object table that table name is TMRLIFEDATA.NTL_ASSIGNED_TASK is lockked, server can execute sql command: exec
dbms_stats.lock_table_stats('TMRLIFEDATA','NTL_ASSIGNED_TASK')。
105, the index of each object table is rebuild respectively;
After the statistical information for locking each object table, server can rebuild each object table respectively
Index.Specifically, server can execute sql command to realize the reconstruction index to object table.Such as, it is assumed that be to table name
The object table of NTL_ASSIGNED_TASK carries out reconstruction index, and server can execute sql command alter index
TMRLIFEDATA.PK_ASSIGNED_TASK_ID rebuild online parallel 8;alter index
TMRLIFEDATA.PK_ASSIGNED_TASK_ID parallel 1。
It is understood that table water level locating for these object tables is after the completion of each object table rebuilds index
It can reduce, index fragment has been removed.
Furthermore it is preferred that server can star multi-process parallel scan index, institute as exemplified above when rebuilding index
Show that sql command enables 8 processes altogether, the speed for rebuilding index can be accelerated to a certain extent.
106, the statistical information of each object table is discharged.
Server is after the index for rebuilding each object table, it is known that, the index fragment in target database is clear
It removes, at this point, server can discharge the statistical information of each object table.Specifically, server can execute sql command
The statistical information of each object table is discharged, for example, being counted for the object table that table name is NTL_ASSIGNED_TASK
Information release, server can execute sql command: exec dbms_stats.unlock_table_stats ('
TMRLIFEDATA', ' NTL_ASSIGNED_TASK'), after statistical information release, which can continue to use these
The statistical information of object table.
In view of failing or rebuilding index it is possible that rebuilding and indexing when server, which rebuilds each object table, to be indexed
There are problems that deviation, mistake occurs so as to cause data in each object table.For this purpose, verifying can also be increased in the present embodiment
Link verifies the statistical information of each object table after discharging statistical information, to learn that this rebuilds rope
Draw with the presence or absence of mistake or flaw, will not influence the normal operation of database to ensure this method after cleaning operation.Such as Fig. 6
Shown, further, this method can also include:
601, when lockking the statistical information of each object table, the current statistical information of each object table is recorded
As the first statistical information;
602, after the statistical information for discharging each object table, the current statistics letter of each object table is recorded
Breath is used as the second statistical information;
603, second statistical information and first statistical information are compared;
If 604, second statistical information and first statistical information are inconsistent, issue to designated person about mesh
It marks table and rebuilds index failure news.
For above-mentioned steps 601, when lockking the statistical information of each object table, server can recorde described each
The current statistical information of a object table is as the first statistical information, it is known that, which is before rebuilding index operation
The statistical information of object table.
For above-mentioned steps 602, after the statistical information for discharging each object table, server can recorde described
The current statistical information of each object table is as the second statistical information, it is known that, which is to rebuild index operation
The statistical information of object table afterwards.
For above-mentioned steps 603, it is to be understood that through the above steps 601 and step 602, server record
Rebuild the statistical information of each object table of index front and back, it is known that, if reconstruction index operation is normal, it is each to rebuild index front and back
The statistical information of object table should consistent namely the first statistical information it is consistent with the second statistical information, this is because first statistics
Information record is statistical information of the object table before rebuilding index, and what the second statistical information then recorded is that object table is being rebuild
Statistical information after index, the two unanimously show to rebuild effective in the typing statistical information in the same object table in index front and back
Information is consistent, so as to reflect that it is normally errorless that this rebuilds index operation.Conversely, if reconstruction index operation is wrong,
First statistical information also can be inconsistent with the second statistical information.Therefore, server can be by comparing second statistical information
Judged with first statistical information.
For above-mentioned steps 604, as can be seen above, if server comparison after find second statistical information with it is described
First statistical information is inconsistent, then shows that this is rebuild index operation and there is mistake or deviation, this wrong or inclined in order to mitigate
It is influenced caused by difference, server can issue to designated person and rebuild index failure news about object table, thus nominator
Member can take appropriate measures after receiving that message to be remedied, for example designated person can be with the system of manual queries server
The Backup Data of log and target database according to this repairs the target database and each object table.
In the embodiment of the present invention, firstly, determining target database for clearance;Then, it is looked into from the target database
The each table for meeting default cleaning condition is looked for, as each object table for clearance;Then, it clears up on each object table
Data;Lock the statistical information of each object table;In addition, the index of each object table is rebuild respectively;Finally, release
The statistical information of each object table.As it can be seen that the present invention is not only able to achieve to the table for meeting default cleaning condition in data library
Automatic cleaning, and the index of table can be rebuild simultaneously, clean up because indexing fragment caused by delete operation, reduce table
Water level is participated in without artificial, and the human cost burden of enterprise is alleviated while avoiding omitting;In addition, the present invention is right
Table is rebuild before index, and the statistical information of table has also been locked, and avoiding rebuilding indexing causes the statistical information of table to change.
It should be understood that the size of the serial number of each step is not meant that the order of the execution order in above-described embodiment, each process
Execution sequence should be determined by its function and internal logic, the implementation process without coping with the embodiment of the present invention constitutes any limit
It is fixed.
In one embodiment, a kind of processing unit of database table water level, the processing unit of the database table water level are provided
It is corresponded with the processing method of database table water level in above-described embodiment.As shown in fig. 7, the processing of the database table water level fills
It sets and locks module including database determining module 701, object table searching module 702, table data scrubbing module 703, statistical information
704, table index rebuilds module 705 and statistical information release module 706.Detailed description are as follows for each functional module:
Database determining module 701, for determining target database for clearance;
Object table searching module 702, for searching each table for meeting default cleaning condition from the target database,
As each object table for clearance;
Table data scrubbing module 703, the data for clearing up on each object table;
Statistical information locks module 704, for lockking the statistical information of each object table;
Table index rebuilds module 705, for rebuilding the index of each object table respectively;
Statistical information release module 706, for discharging the statistical information of each object table.
As shown in figure 8, further, the target database may include more than two partition holdings, the target
Table searching module 702 may include:
Using time reading unit 7021, the last time for reading each partition holding in the target database makes
Use the time;
Subregion determination unit 7022, it is the last pre- using time satisfaction for being determined from each partition holding
If using the partition holding of Timeout conditions;
First object table determination unit 7023, it is for clearance each for being determined as all tables in the partition holding determined
A object table.
As shown in figure 9, further, the processing unit of the database table water level can also include:
First information logging modle 707, for recording described each when lockking the statistical information of each object table
The current statistical information of object table is as the first statistical information;
Second information logging modle 708, it is described each for recording after the statistical information for discharging each object table
The current statistical information of a object table is as the second statistical information;
Information contrast module 709, for comparing second statistical information and first statistical information;
Information sending module 710, if for the information contrast module comparing result be it is inconsistent, to designated person
It issues and rebuilds index failure news about object table.
Further, the object table searching module may include:
Failure configuration reading unit, for reading preconfigured failure configuration information;
Fail data searching unit, for according to the failure configuration information from the target database in look-up table it is several
According to each table for being fail data;
Second object table determination unit, for each table found to be determined as to each object table for clearance.
Further, the database determining module may include:
Preparatory reading unit, for reading preconfigured failure configuration information;
Database lookup unit, for being searched from each database according to the failure configuration information, there are fail datas
Database;
Object library determination unit, for the database found to be determined as to target database for clearance.
The specific restriction of processing unit about database table water level may refer to above for database table water level
The restriction of processing method, details are not described herein.Modules in the processing unit of above-mentioned database table water level can whole or portion
Divide and is realized by software, hardware and combinations thereof.Above-mentioned each module can be embedded in the form of hardware or independently of computer equipment
In processor in, can also be stored in a software form in the memory in computer equipment, in order to processor calling hold
The corresponding operation of the above modules of row.
In one embodiment, a kind of computer equipment is provided, which can be server, internal junction
Composition can be as shown in Figure 10.The computer equipment include by system bus connect processor, memory, network interface and
Database.Wherein, the processor of the computer equipment is for providing calculating and control ability.The memory packet of the computer equipment
Include non-volatile memory medium, built-in storage.The non-volatile memory medium is stored with operating system, computer program and data
Library.The built-in storage provides environment for the operation of operating system and computer program in non-volatile memory medium.The calculating
The data that the database of machine equipment is related in the processing method of library table water level for storing data.The network of the computer equipment
Interface is used to communicate with external terminal by network connection.To realize a kind of data when the computer program is executed by processor
The processing method of library table water level.
In one embodiment, a kind of computer equipment is provided, including memory, processor and storage are on a memory
And the computer program that can be run on a processor, processor realize database table in above-described embodiment when executing computer program
The step of processing method of water level, such as step 101 shown in Fig. 2 is to step 106.Alternatively, processor executes computer program
The function of each module/unit of the processing unit of database table water level in Shi Shixian above-described embodiment, such as module shown in Fig. 7
701 to module 706 function.To avoid repeating, which is not described herein again.
In one embodiment, a kind of computer readable storage medium is provided, computer program is stored thereon with, is calculated
Machine program realizes the step of processing method of database table water level in above-described embodiment when being executed by processor, such as shown in Fig. 2
Step 101 to step 106.Alternatively, realizing database table water level in above-described embodiment when computer program is executed by processor
Processing unit each module/unit function, such as module 701 shown in Fig. 7 is to the function of module 706.To avoid repeating, this
In repeat no more.
Those of ordinary skill in the art will appreciate that realizing all or part of the process in above-described embodiment method, being can be with
Relevant hardware is instructed to complete by computer program, the computer program can be stored in a non-volatile computer
In read/write memory medium, the computer program is when being executed, it may include such as the process of the embodiment of above-mentioned each method.Wherein,
To any reference of memory, storage, database or other media used in each embodiment provided herein,
Including non-volatile and/or volatile memory.Nonvolatile memory may include read-only memory (ROM), programming ROM
(PROM), electrically programmable ROM (EPROM), electrically erasable ROM (EEPROM) or flash memory.Volatile memory may include
Random access memory (RAM) or external cache.By way of illustration and not limitation, RAM is available in many forms,
Such as static state RAM (SRAM), dynamic ram (DRAM), synchronous dram (SDRAM), double data rate sdram (DDRSDRAM), enhancing
Type SDRAM (ESDRAM), synchronization link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM
(RDRAM), direct memory bus dynamic ram (DRDRAM) and memory bus dynamic ram (RDRAM) etc..
It is apparent to those skilled in the art that for convenience of description and succinctly, only with above-mentioned each function
Can unit, module division progress for example, in practical application, can according to need and by above-mentioned function distribution by different
Functional unit, module are completed, i.e., the internal structure of described device is divided into different functional unit or module, more than completing
The all or part of function of description.
Embodiment described above is merely illustrative of the technical solution of the present invention, rather than its limitations;Although referring to aforementioned reality
Applying example, invention is explained in detail, those skilled in the art should understand that: it still can be to aforementioned each
Technical solution documented by embodiment is modified or equivalent replacement of some of the technical features;And these are modified
Or replacement, the spirit and scope for technical solution of various embodiments of the present invention that it does not separate the essence of the corresponding technical solution should all
It is included within protection scope of the present invention.
Claims (10)
1. a kind of processing method of database table water level characterized by comprising
Determine target database for clearance;
The each table for meeting default cleaning condition is searched from the target database, as each object table for clearance;
Clear up the data on each object table;
Lock the statistical information of each object table;
The index of each object table is rebuild respectively;
Discharge the statistical information of each object table;
Wherein, the target database includes more than two partition holdings, and described search from the target database meets
Each table of default cleaning condition includes: as each object table for clearance
The last time for reading each partition holding in the target database uses the time;
The last partition holding for meeting using the time and presetting using Timeout conditions is determined from each partition holding;
All tables in determining partition holding are determined as to each object table for clearance.
2. the processing method of database table water level according to claim 1, which is characterized in that the database table water level
Processing method further include:
When lockking the statistical information of each object table, the current statistical information of each object table is recorded as first
Statistical information;
After the statistical information for discharging each object table, the current statistical information of each object table is recorded as the
Two statistical informations;
Compare second statistical information and first statistical information;
If second statistical information and first statistical information are inconsistent, issue to designated person and rebuild about object table
Index failure news.
3. the processing method of database table water level according to claim 1, which is characterized in that described from the target data
The each table for meeting default cleaning condition is searched in library, includes: as each object table for clearance
Read preconfigured failure configuration information;
According to the failure configuration information from data are each table of fail data in look-up table in the target database;
The each table found is determined as to each object table for clearance.
4. the processing method of database table water level according to any one of claim 1 to 3, which is characterized in that described true
Determining target database for clearance includes:
Read preconfigured failure configuration information;
Search that there are the databases of fail data from each database according to the failure configuration information;
The database found is determined as to target database for clearance.
5. a kind of processing unit of database table water level characterized by comprising
Database determining module, for determining target database for clearance;
Object table searching module, for searching each table for meeting default cleaning condition from the target database, as to
Each object table of cleaning;
Table data scrubbing module, the data for clearing up on each object table;
Statistical information locks module, for lockking the statistical information of each object table;
Table index rebuilds module, for rebuilding the index of each object table respectively;
Statistical information release module, for discharging the statistical information of each object table;
Wherein, the target database includes more than two partition holdings, and the object table searching module includes:
Using time reading unit, the last time for reading each partition holding in the target database uses the time;
Subregion determination unit, it is the last default using super using time satisfaction for being determined from each partition holding
When condition partition holding;
First object table determination unit, for all tables in the partition holding determined to be determined as to each target for clearance
Table.
6. the processing unit of database table water level according to claim 5, which is characterized in that the database table water level
Processing unit further include:
First information logging modle, for recording each object table when lockking the statistical information of each object table
Current statistical information is as the first statistical information;
Second information logging modle, for recording each target after the statistical information for discharging each object table
The current statistical information of table is as the second statistical information;
Information contrast module, for comparing second statistical information and first statistical information;
Information sending module, if for the information contrast module comparing result be it is inconsistent, to designated person issue close
Index failure news is rebuild in object table.
7. the processing unit of database table water level according to claim 5, which is characterized in that the object table searching module
Include:
Failure configuration reading unit, for reading preconfigured failure configuration information;
Fail data searching unit is used for according to the failure configuration information from data are equal in look-up table in the target database
For each table of fail data;
Second object table determination unit, for each table found to be determined as to each object table for clearance.
8. the processing unit of database table water level according to any one of claims 5 to 7, which is characterized in that the number
Include: according to library determining module
Preparatory reading unit, for reading preconfigured failure configuration information;
Database lookup unit, for being searched from each database according to the failure configuration information, there are the numbers of fail data
According to library;
Object library determination unit, for the database found to be determined as to target database for clearance.
9. a kind of computer equipment, including memory, processor and storage are in the memory and can be in the processor
The computer program of upper operation, which is characterized in that the processor realized when executing the computer program as claim 1 to
The processing method of database table water level described in any one of 5.
10. a kind of computer readable storage medium, the computer-readable recording medium storage has computer program, and feature exists
In the place of realization database table water level as described in any one of claims 1 to 5 when the computer program is executed by processor
Reason method.
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN201910432704.9A CN110287183B (en) | 2019-05-23 | 2019-05-23 | Processing method and device for database table water level, computer equipment and storage medium |
Applications Claiming Priority (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN201910432704.9A CN110287183B (en) | 2019-05-23 | 2019-05-23 | Processing method and device for database table water level, computer equipment and storage medium |
Publications (2)
Publication Number | Publication Date |
---|---|
CN110287183A true CN110287183A (en) | 2019-09-27 |
CN110287183B CN110287183B (en) | 2024-02-02 |
Family
ID=68002303
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
CN201910432704.9A Active CN110287183B (en) | 2019-05-23 | 2019-05-23 | Processing method and device for database table water level, computer equipment and storage medium |
Country Status (1)
Country | Link |
---|---|
CN (1) | CN110287183B (en) |
Cited By (2)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US11023448B2 (en) * | 2017-01-09 | 2021-06-01 | Tencent Technology (Shenzhen) Company Limited | Data scrubbing method and apparatus, and computer readable storage medium |
CN113468167A (en) * | 2020-03-31 | 2021-10-01 | 中国移动通信集团湖南有限公司 | Database high water level recovery method and device and electronic equipment |
Citations (6)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CA2167790A1 (en) * | 1995-01-23 | 1996-07-24 | Donald S. Maier | Relational database system and method with high data availability during table data restructuring |
US20120136869A1 (en) * | 2010-11-30 | 2012-05-31 | Sap Ag | System and Method of Processing Information Stored in Databases |
CN105893531A (en) * | 2016-03-31 | 2016-08-24 | 武汉虹信技术服务有限责任公司 | PostgreSQL database mass data management method and system |
CN107506356A (en) * | 2016-06-14 | 2017-12-22 | 北京京东尚科信息技术有限公司 | Data processing method and its system |
US10007695B1 (en) * | 2017-05-22 | 2018-06-26 | Dropbox, Inc. | Replication lag-constrained deletion of data in a large-scale distributed data storage system |
CN108829782A (en) * | 2018-05-31 | 2018-11-16 | 平安科技(深圳)有限公司 | data table cleaning method, server and computer readable storage medium |
-
2019
- 2019-05-23 CN CN201910432704.9A patent/CN110287183B/en active Active
Patent Citations (6)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CA2167790A1 (en) * | 1995-01-23 | 1996-07-24 | Donald S. Maier | Relational database system and method with high data availability during table data restructuring |
US20120136869A1 (en) * | 2010-11-30 | 2012-05-31 | Sap Ag | System and Method of Processing Information Stored in Databases |
CN105893531A (en) * | 2016-03-31 | 2016-08-24 | 武汉虹信技术服务有限责任公司 | PostgreSQL database mass data management method and system |
CN107506356A (en) * | 2016-06-14 | 2017-12-22 | 北京京东尚科信息技术有限公司 | Data processing method and its system |
US10007695B1 (en) * | 2017-05-22 | 2018-06-26 | Dropbox, Inc. | Replication lag-constrained deletion of data in a large-scale distributed data storage system |
CN108829782A (en) * | 2018-05-31 | 2018-11-16 | 平安科技(深圳)有限公司 | data table cleaning method, server and computer readable storage medium |
Non-Patent Citations (1)
Title |
---|
王佳等: ""利用表分区的大数据库优化方法"", 《大连工业大学学报》 * |
Cited By (3)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US11023448B2 (en) * | 2017-01-09 | 2021-06-01 | Tencent Technology (Shenzhen) Company Limited | Data scrubbing method and apparatus, and computer readable storage medium |
CN113468167A (en) * | 2020-03-31 | 2021-10-01 | 中国移动通信集团湖南有限公司 | Database high water level recovery method and device and electronic equipment |
CN113468167B (en) * | 2020-03-31 | 2023-04-11 | 中国移动通信集团湖南有限公司 | Database high water level recovery method and device and electronic equipment |
Also Published As
Publication number | Publication date |
---|---|
CN110287183B (en) | 2024-02-02 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
US11429641B2 (en) | Copying data changes to a target database | |
US8838531B2 (en) | Database synchronization and validation | |
CN109086295B (en) | Data synchronization method, device, computer equipment and storage medium | |
CN108509462B (en) | Method and device for synchronizing activity transaction table | |
US20170011082A1 (en) | Mechanisms for merging index structures in molap while preserving query consistency | |
CN110008129B (en) | Reliability test method, device and equipment for storage timing snapshot | |
CN107665219B (en) | Log management method and device | |
CN110727724B (en) | Data extraction method and device, computer equipment and storage medium | |
CN111444027B (en) | Transaction processing method and device, computer equipment and storage medium | |
CN111475517B (en) | Data updating method, device, computer equipment and storage medium | |
US20080077591A1 (en) | Computer program product for conducting a lock free read | |
CN113238924B (en) | Chaotic engineering realization method and system in distributed graph database system | |
US7225206B2 (en) | System and method for reorganizing stored data | |
CN110287183A (en) | Processing method, device, computer equipment and the storage medium of database table water level | |
WO2021174817A1 (en) | Database automated auditing method and system, device, and storage medium | |
US10268776B1 (en) | Graph store built on a distributed hash table | |
CN107111534A (en) | A kind of method and apparatus of data processing | |
CN110147354B (en) | Batch data editing method, device, computer equipment and storage medium | |
CN107644041B (en) | Policy settlement processing method and device | |
US8473464B2 (en) | Method and device for data recovery using bit logging | |
CN113312309B (en) | Snapshot chain management method, device and storage medium | |
CN109739687A (en) | A kind of snapshot management system and method based on Elasticsearch | |
US11275666B1 (en) | Method and apparatus for identifying high importance devices of a consistency group | |
CN112988708A (en) | Version updating method and device, computer readable storage medium and processor | |
RU2526753C1 (en) | Method for data recovery in database management system |
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 |