CN110287183B - Processing method and device for database table water level, computer equipment and storage medium - Google Patents
Processing method and device for database table water level, computer equipment and storage medium Download PDFInfo
- Publication number
- CN110287183B CN110287183B CN201910432704.9A CN201910432704A CN110287183B CN 110287183 B CN110287183 B CN 110287183B CN 201910432704 A CN201910432704 A CN 201910432704A CN 110287183 B CN110287183 B CN 110287183B
- Authority
- CN
- China
- Prior art keywords
- target
- database
- statistical information
- cleaned
- 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.)
- Active
Links
- XLYOFNOQVPJJNP-UHFFFAOYSA-N water Substances O XLYOFNOQVPJJNP-UHFFFAOYSA-N 0.000 title claims abstract description 23
- 238000003672 processing method Methods 0.000 title claims abstract description 11
- 238000004140 cleaning Methods 0.000 claims abstract description 40
- 238000012545 processing Methods 0.000 claims abstract description 35
- 238000000034 method Methods 0.000 claims abstract description 33
- 238000005192 partition Methods 0.000 claims description 66
- 238000004590 computer program Methods 0.000 claims description 16
- 239000012634 fragment Substances 0.000 description 6
- 230000006870 function Effects 0.000 description 6
- 238000010586 diagram Methods 0.000 description 4
- 230000008569 process Effects 0.000 description 4
- 230000008859 change Effects 0.000 description 3
- 238000013500 data storage Methods 0.000 description 2
- 238000007726 management method Methods 0.000 description 2
- 230000001360 synchronised effect Effects 0.000 description 2
- 238000009825 accumulation Methods 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 238000007418 data mining Methods 0.000 description 1
- 230000007423 decrease Effects 0.000 description 1
- 238000012217 deletion Methods 0.000 description 1
- 230000037430 deletion Effects 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 230000008439 repair process Effects 0.000 description 1
- 230000003068 static effect Effects 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 238000012795 verification Methods 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 a processing method, a processing device, computer equipment and a storage medium for a database table water level, which are applied to the technical field of databases and are used for solving the problems that the existing method for reducing the database table water level is low in efficiency and easy to miss. The method provided by the invention comprises the following steps: determining a target database to be cleaned; searching each table meeting preset cleaning conditions from the target database to serve as each target table to be cleaned; cleaning the data on each target table; locking the statistical information of each target table; reconstructing indexes of the target tables respectively; and releasing the statistical information of each target table.
Description
Technical Field
The present invention relates to the field of database technologies, and in particular, to a method and apparatus for processing a table level of a database, a computer device, and a storage medium.
Background
In the context of the big data age, big data storage is a precondition for data mining and utilization, and thus, the development of enterprises is becoming more important. Meanwhile, along with the accumulation of mass data, large data storage brings more and more cost and management pressure to enterprises. Therefore, timely cleaning of useless data is critical.
Currently, when cleaning useless or invalid data, regular deletion is performed through SQL commands. However, although this method can simply and directly delete data, as the number of operations on the table in the database increases, the table level increases, and the performance of the table and the database decreases. Although the existing practice can arrange special database management personnel to manually lower the water level of the table, the efficiency is low, omission easily occurs, and meanwhile, the labor cost burden is increased for enterprises.
Disclosure of Invention
The embodiment of the invention provides a processing method, a processing device, computer equipment and a storage medium for a database table water level, which are used for solving the problems that the existing method for reducing the database table water level is low in efficiency and easy to miss.
A method for processing a database table water level, comprising:
determining a target database to be cleaned;
searching each table meeting preset cleaning conditions from the target database to serve as each target table to be cleaned;
cleaning the data on each target table;
locking the statistical information of each target table;
reconstructing indexes of the target tables respectively;
releasing the statistical information of each target table;
the target database comprises more than two storage partitions, each table meeting the preset cleaning condition is searched from the target database, and each target table to be cleaned comprises:
reading the last use time of each storage partition in the target database;
determining a memory partition with the last use time meeting a preset use timeout condition from the memory partitions;
all tables in the determined storage partition are determined as respective target tables to be cleaned.
A database table level processing apparatus, comprising:
the database determining module is used for determining a target database to be cleaned;
the target table searching module is used for searching each table meeting the preset cleaning conditions from the target database and taking the table as each target table to be cleaned;
the table data cleaning module is used for cleaning the data on each target table;
the statistical information locking module is used for locking the statistical information of each target table;
the table index rebuilding module is used for rebuilding the indexes of the target tables respectively;
the statistical information releasing module is used for releasing the statistical information of each target table;
wherein the target database comprises more than two memory partitions, and the target table lookup module comprises:
the using time reading unit is used for reading the last using time of each storage partition in the target database;
the partition determining unit is used for determining a memory partition with the last use time meeting a preset use timeout condition from the memory partitions;
and the first target table determining unit is used for determining all tables in the determined storage partition as each target table to be cleaned.
A computer device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, the processor implementing the steps of the method of processing a database table level as described above when the computer program is executed.
A computer readable storage medium storing a computer program which, when executed by a processor, implements the steps of the database table level processing method described above.
The processing method, the processing device, the computer equipment and the storage medium of the database table water level firstly determine a target database to be cleaned; then, searching each table meeting preset cleaning conditions from the target database to serve as each target table to be cleaned; then, cleaning the data on each target table; locking the statistical information of each target table; respectively reconstructing indexes of the target tables; and finally, releasing the statistical information of each target table. Therefore, the method and the device not only can realize automatic cleaning of the table meeting the preset cleaning conditions in the database, but also can reconstruct the index of the table, clean index fragments generated by deleting operation, reduce the water level of the table, do not need to be participated manually, avoid omission and simultaneously reduce the labor cost burden of enterprises; in addition, the invention locks the statistical information of the table before reconstructing the index of the table, and avoids the change of the statistical information of the table caused by reconstructing the index.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings that are needed in the description of the embodiments of the present invention will be briefly described below, it being obvious that the drawings in the following description are only some embodiments of the present invention, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a schematic view of an application environment of a method for processing database table levels according to an embodiment of the present invention;
FIG. 2 is a flow chart of a method of processing database table levels in an embodiment of the invention;
FIG. 3 is a flowchart of step 101 of the method for processing database table levels in an application scenario according to an embodiment of the present invention;
FIG. 4 is a flowchart of step 102 of the method for processing database table levels in an application scenario according to an embodiment of the present invention;
FIG. 5 is a flowchart of step 102 of the method for processing database table levels in another application scenario according to an embodiment of the present invention;
FIG. 6 is a schematic flow chart of a method for processing database table levels according to an embodiment of the present invention for checking statistical information in an application scenario;
FIG. 7 is a schematic diagram of a structure of a database table water level processing device in an application scenario according to an embodiment of the present invention;
FIG. 8 is a schematic diagram of a target table lookup module according to an embodiment of the invention;
FIG. 9 is a schematic diagram of a structure of a database table water level processing device in another application scenario according to an embodiment of the present invention;
FIG. 10 is a schematic diagram of a computer device in accordance with an embodiment of the invention.
Detailed Description
The following description of the embodiments of the present invention will be made clearly and fully with reference to the accompanying drawings, in which it is evident that the embodiments described are some, but not all embodiments of the invention. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
The processing method of the database table level can be applied to an application environment as shown in fig. 1, wherein a client communicates with a server through a network. The client may be, but is not limited to, various personal computers, notebook computers, smart phones, tablet computers, and portable wearable devices. The server may be implemented as a stand-alone server or as a server cluster composed of a plurality of servers.
In one embodiment, as shown in fig. 2, a method for processing a table level of a database is provided, and the method is applied to the server in fig. 1 for illustration, and includes the following steps:
101. determining a target database to be cleaned;
in this embodiment, the server first needs to determine the target databases to be cleaned, and the server may determine the target databases in an automatic manner, or may select a specified database as the target database to be cleaned by the user through the client. For example, the client is in communication connection with the server, and a user can select more than one database from the multiple databases on the interface of the client as the database to be cleaned in the operation, so that the server can determine the database selected by the user as the target database.
In addition, the server may also automatically determine the target database through preset configuration information, for easy understanding, as shown in fig. 3, further, step 101 may specifically include:
201. reading pre-configured failure configuration information;
202. searching databases with failure data from the databases according to the failure configuration information;
203. and determining the searched database as a target database to be cleaned.
With respect to the above step 201, it may be understood that the server may be preconfigured with invalidation configuration information, where the invalidation configuration information is mainly used to determine whether the data in the database has been invalidated or outdated, and the data satisfying the invalidation configuration information may be regarded as useless data, so that the useless data may be cleaned up in a subsequent step. In particular, the invalidation configuration information may be configured as a time condition, and the data may be considered as invalidation data once the storage time of the data exceeds a period defined in the time condition.
For the above step 202, after the server reads the failure configuration information, the server may search the databases with failure data from the databases according to the failure configuration information. It can be known that the server only needs to determine which databases have invalid data, and can find out the corresponding databases from the databases. For example, assuming that the failure arrangement information is set to "data whose storage time exceeds 1 month is failure data", it is known from statistical information of the database that the start time of data stored in the database a is 2018, 2 months, 1 day, the start time of data stored in the database B is 2018, 3 months, 1 day, the start time of data stored in the database C is 2018, 4 months, 1 day, and the current system time is 2018, 4 months, 3 days, and it is known that failure data exists in the database a and the database B.
For the above step 203, it is easy that after searching for the database in which the failure data exists, the server may determine the searched database as the target database to be cleaned.
102. Searching each table meeting preset cleaning conditions from the target database to serve as each target table to be cleaned;
after determining the target database, the server also needs to find out each target table to be cleaned from the target database. To facilitate this, a preset cleaning condition may be preset on the server, where the preset cleaning condition specifies which tables are target tables to be cleaned. The preset cleaning condition may be specifically set according to an actual use condition, for example, a table in which all data in the table are failure data may be set as a target table to be cleaned.
In some application scenarios, in consideration of the problem of convenient storage, the database is provided with storage partitions, and data of different time periods are stored in a targeted manner among the storage partitions. In this case, the server of the present embodiment may determine the target table in units of storage partitions, which is not only applicable to the database in the form of partition storage, but also improves the processing efficiency of the present method. As shown in fig. 4, further, the target database includes more than two storage partitions, and step 102 may specifically include:
301. reading the last use time of each storage partition in the target database;
302. determining a memory partition with the last use time meeting a preset use timeout condition from the memory partitions;
303. all tables in the determined storage partition are determined as respective target tables to be cleaned.
With respect to step 301, it may be understood that, in daily use, the target database is divided into storage partitions, so different storage partitions generally store different data, and in general, each storage partition stores data in different time periods, for example, in an application scenario, 3 storage partitions of the target database are partition a, partition b and partition c, partition a stores data in 2016, partition b stores data in 2017, and partition c stores data in 2018. Of course, the use of the storage partition may be determined according to the actual situation, for example, the division of the storage partition may be performed for different data types, for example, the business data stored in the target database may be divided into the storage partition 1, the employee data into the storage partition 2, the system data into the storage partition 3, and so on.
For the above step 302, after the server reads the configuration usage time of each storage partition in the target database, it may determine, from the storage partitions, the storage partition whose configuration usage time satisfies the preset usage timeout condition. For example, assuming that the target database includes three storage partitions, partition 1, partition 2 and partition 3, respectively, the preset usage timeout condition is "the time to store data exceeds 1 month", where the time of the latest data stored in partition 1 is 2018, 3, 2, 15, and 22, the current system time is 2018, 4, 3, and it is easy to know that the usage time of all data stored in partition 1 exceeds 1 month, and therefore it can be determined that all tables and all data in partition 1 have failed.
With respect to step 303 above, it may be appreciated that after determining that the last time the memory partition has been used for a time that meets the preset usage timeout condition, the server may determine all tables in the determined memory partition as respective target tables to be cleaned.
In this embodiment, the object of the cleaning data is a table, so all the data in the target table is required to be invalid data. As shown in fig. 5, further, step 102 may specifically include:
401. reading pre-configured failure configuration information;
402. searching tables in which data in a table are failure data from the target database according to the failure configuration information;
403. and determining each searched table as each target table to be cleaned.
With respect to step 401 described above, it will be appreciated that the server may be pre-configured with failure configuration information that is primarily used to determine whether the data in the table has failed or is outdated, and that data that satisfies the failure configuration information may be identified as unusable data, and thus may be cleaned up in subsequent steps. In particular, the invalidation configuration information may be configured as a time condition, and the data may be considered as invalidation data once the storage time of the data exceeds a period defined in the time condition.
For the step 402, after the server reads the failure configuration information, the server may look up each table in the target database according to the failure configuration information, where the data is failure data. It is known that the server needs to find out each table whose data is failure data from all the tables in the target database, and therefore, the server needs to check each table in the target database. For example, assuming that the failure arrangement information is set to "data stored for more than 1 month is failure data", 3 tables in the target database are respectively table 1, table 2 and table 3, the time of the latest data stored in table 1 is 2018, 2 months, 1 day, the time of the latest data stored in table 2 is 2018, 3 months, 1 day, 4 months, 1 day, and 3 days, and the current system time is 2018, 4 months, 3 days, and all the data in table 1 and table 2 are stored for more than 1 month and are failure data, so the server can find out table 1 and table 2.
For the above step 403, it is easy for the server to determine each of the tables to be cleaned as each of the target tables to perform the subsequent data cleaning operation after searching each of the tables in which the data is failure data.
103. Cleaning the data on each target table;
after determining each target table, the server can clean up the data on the target tables. In particular, the server may use structured query language SQL commands to effect cleansing of the data on the table.
To facilitate understanding, further, step 103 may specifically include:
501. generating respective SQL (structured query language ) commands for cleaning data according to the structures of the respective target tables, respectively;
502. and executing each SQL command to clean the data on each target table.
For the above step 501, it is understood that for each target table, the server may generate an SQL command for cleaning up data according to the structure of the target table. For example, assuming that the disk partition is to be cleaned up to the full data of tmrlifada.ntl_associned_task of the table on pt_cch_credt_201105, an SQL command may be generated as: the alter table tmrlifedata.ntl_associatedj TASK truncate partition PT _cch_credt_201105drop storage. Through the SQL command, all data on the TMRLIFEDATA.NTL_ASSINED_TASK table can be cleaned.
For step 502, after generating the SQL commands, the server may execute each of the SQL commands to clean up the data on each of the target tables. Specifically, the server may execute the SQL commands one by one, or may process the SQL commands in parallel in a multithreading manner, so as to finally implement cleaning of data on each target table.
It should be noted that, after the data in the target tables are cleaned, but the cleaning operation may cause a large amount of index fragments in the database, which cannot be used by the user, and may cause the water level in the database to increase, so that in this embodiment, the water level in the target database is automatically reduced in the following steps.
Wherein, regarding the water level of the table, all oracle segments have an upper limit for the data contained in the segment, which is referred to as "high water mark" or HWM in the industry, according to the oracle definition. In the oracle database, if a table is frequently inserted, deleted, modified, etc., the table will generate a higher water level. The high water level of the watch can cause the watch to degrade in performance when in use, and the time to operate the watch can be slow.
104. Locking the statistical information of each target table;
it will be appreciated that, since the server will clear the index fragment by the operation of reconstructing the index in the subsequent step, the statistics of the target tables will be destroyed when reconstructing the index, and in order to prevent this, the server may lock the statistics of the target tables before step 105, and after the statistics of the target tables are locked, even if the operations of reconstructing the index are performed on the target tables, these statistics will not change.
Specifically, the server may execute SQL commands to lock statistics of the various target tables. For example, to lock a target table with a table name of tmrlifedata.ntl_associated_task, the server may execute the SQL command: exec dbms_stats.lock_table_stats ('TMRLIFEDATA', 'ntl_associated_task').
105. Reconstructing indexes of the target tables respectively;
after locking the statistics of the respective target table, the server may reconstruct the index of the respective target table, respectively. In particular, the server may execute SQL commands to implement the rebuild index of the target table. For example, assuming that a target table named NTL_ASSINED_TASK is to be re-indexed, the server may execute the SQL command alter index TMRLIFEDTAK. PK_ASSINED_TASK_ ID rebuild online parallel 8; the alter index TMRLIFEDATA. PK_ASSINED_TASK_ID parallel 1.
It will be appreciated that when the reconstruction of the index from the respective target table is completed, the table level at which the target tables are located will be reduced and the index fragments will be cleared.
In addition, preferably, when reconstructing the index, the server may start the multi-process parallel scan index, and the SQL command as illustrated by the above example enables 8 processes in total, so that the speed of reconstructing the index can be increased to a certain extent.
106. And releasing the statistical information of each target table.
After reconstructing the index of each target table, the server can know that the index fragments in the target database are cleared, and at this time, the server can release the statistical information of each target table. Specifically, the server may execute the SQL command to release the statistics of the respective target tables, for example, the server may execute the SQL command to release the statistics for the target table named ntl_associated_task: after releasing the statistics, the target database can continue to use the statistics of the target tables.
When the server rebuilds the index for each target table, the problem that the index is rebuilt in failure or deviation exists in the rebuilding may occur, so that errors occur in the data in each target table. Therefore, in this embodiment, a verification link may be further added, and after the statistical information is released, the statistical information of each target table is verified, so as to obtain whether the reconstructed index has an error or a flaw, so as to ensure that the normal operation of the database is not affected after the cleaning operation. As shown in fig. 6, the method may further include:
601. when the statistical information of each target table is locked, recording the current statistical information of each target table as first statistical information;
602. after releasing the statistical information of each target table, recording the current statistical information of each target table as second statistical information;
603. comparing the second statistical information with the first statistical information;
604. and if the second statistical information is inconsistent with the first statistical information, sending a message about failure of reconstructing the index of the target table to a designated person.
For the step 601, when the statistics of each target table are locked, the server may record the current statistics of each target table as the first statistics, where the first statistics is the statistics of the target table before the index is reconstructed.
For the step 602, after releasing the statistics of each target table, the server may record the current statistics of each target table as second statistics, which is known as statistics of the target table after the index reconstruction operation.
As to the above step 603, it can be understood that, through the above step 601 and step 602, the server records the statistics of each target table before and after the reconstruction index, and it can be known that if the reconstruction index is normal, the statistics of each target table before and after the reconstruction index should be consistent, that is, the first statistics are consistent with the second statistics, because the first statistics record the statistics of the target table before the reconstruction index, the second statistics record the statistics of the target table after the reconstruction index, and the consistency of the statistics indicates that the valid information in the entered statistics in the same target table before and after the reconstruction index is consistent, so that the normal operation of the reconstruction index can be reflected. Otherwise, if the rebuilding index operation is wrong, the first statistical information and the second statistical information are inconsistent. Thus, the server can make a judgment by comparing the second statistical information with the first statistical information.
For the above step 604, it can be known that, if the server compares the second statistical information with the first statistical information, it indicates that there is an error or deviation in the index rebuilding operation, so as to reduce the influence caused by the error or deviation, the server may send a message about the failure of index rebuilding of the target table to the appointed person, so that the appointed person may take appropriate measures to remedy after receiving the message, for example, the appointed person may manually query the system log of the server and the backup data of the target database, and repair the target database and each target table accordingly.
In the embodiment of the invention, firstly, a target database to be cleaned is determined; then, searching each table meeting preset cleaning conditions from the target database to serve as each target table to be cleaned; then, cleaning the data on each target table; locking the statistical information of each target table; respectively reconstructing indexes of the target tables; and finally, releasing the statistical information of each target table. Therefore, the method and the device not only can realize automatic cleaning of the table meeting the preset cleaning conditions in the database, but also can reconstruct the index of the table, clean index fragments generated by deleting operation, reduce the water level of the table, do not need to be participated manually, avoid omission and simultaneously reduce the labor cost burden of enterprises; in addition, the invention locks the statistical information of the table before reconstructing the index of the table, and avoids the change of the statistical information of the table caused by reconstructing the index.
It should be understood that the sequence number of each step in the foregoing embodiment does not mean that the execution sequence of each process should be determined by the function and the internal logic, and should not limit the implementation process of the embodiment of the present invention.
In an embodiment, a database table level processing device is provided, where the database table level processing device corresponds to the database table level processing method in the above embodiment one by one. As shown in fig. 7, the processing device for a database table level includes a database determining module 701, a target table searching module 702, a table data cleaning module 703, a statistical information locking module 704, a table index reconstructing module 705 and a statistical information releasing module 706. The functional modules are described in detail as follows:
a database determining module 701, configured to determine a target database to be cleaned;
a target table searching module 702, configured to search, from the target database, each table that satisfies a preset cleaning condition, as each target table to be cleaned;
a table data cleaning module 703, configured to clean up data on the target tables;
a statistics locking module 704, configured to lock statistics of the target tables;
a table index rebuilding module 705, configured to rebuild indexes of the respective target tables respectively;
and the statistical information releasing module 706 is configured to release the statistical information of the respective target tables.
As shown in fig. 8, further, the target database may include more than two memory partitions, and the target table lookup module 702 may include:
a usage time reading unit 7021, configured to read a last usage time of each storage partition in the target database;
a partition determining unit 7022, configured to determine, from the storage partitions, a storage partition whose last usage time satisfies a preset usage timeout condition;
a first target table determining unit 7023 is configured to determine all tables in the determined storage partition as respective target tables to be cleaned.
As shown in fig. 9, further, the processing device for database table water level may further include:
a first information recording module 707, configured to record, when the statistics of each target table are locked, current statistics of each target table as first statistics;
a second information recording module 708, configured to record, as second statistical information, current statistical information of each target table after releasing the statistical information of each target table;
an information comparing module 709 for comparing the second statistical information with the first statistical information;
and the information sending module 710 is configured to send a message about failure in reconstructing the index of the target table to the designated person if the comparison result of the information comparison module is inconsistent.
Further, the target table lookup module may include:
a failure configuration reading unit for reading the pre-configured failure configuration information;
the failure data searching unit is used for searching each table of which the data in the table is failure data from the target database according to the failure configuration information;
and the second target table determining unit is used for determining each searched table as each target table to be cleaned.
Further, the database determination module may include:
a pre-reading unit for reading pre-configured failure configuration information;
the database searching unit is used for searching databases with failure data from the databases according to the failure configuration information;
and the target database determining unit is used for determining the searched database as a target database to be cleaned.
For specific limitations of the processing means for the database table level, reference may be made to the above limitation of the processing method for the database table level, and no further description is given here. The above-mentioned various modules in the processing device of the database table level may be implemented in whole or in part by software, hardware, and combinations thereof. The above modules may be embedded in hardware or may be independent of a processor in the computer device, or may be stored in software in a memory in the computer device, so that the processor may call and execute operations corresponding to the above modules.
In one embodiment, a computer device is provided, which may be a server, and the internal structure of which may be as shown in fig. 10. The computer device includes a processor, a memory, a network interface, and a database connected by a system bus. Wherein the processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, computer programs, and a database. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage media. The database of the computer device is used for storing data involved in the processing method of the database table level. The network interface of the computer device is used for communicating with an external terminal through a network connection. The computer program, when executed by a processor, implements a method of processing database table levels.
In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored on the memory and executable on the processor, where the processor executes the computer program to implement the steps of the method for processing database table levels in the above embodiment, such as steps 101 to 106 shown in fig. 2. Alternatively, the processor may implement the functions of the modules/units of the processing device for database table levels in the above embodiment when executing the computer program, for example, the functions of the modules 701 to 706 shown in fig. 7. In order to avoid repetition, a description thereof is omitted.
In one embodiment, a computer readable storage medium is provided, on which a computer program is stored, which when executed by a processor, implements the steps of the method for processing database table levels in the above embodiment, such as steps 101 to 106 shown in fig. 2. Alternatively, the computer program when executed by the processor implements the functions of the modules/units of the processing apparatus for database table levels in the above embodiment, such as the functions of the modules 701 to 706 shown in fig. 7. In order to avoid repetition, a description thereof is omitted.
Those skilled in the art will appreciate that implementing all or part of the above described methods may be accomplished by way of a computer program stored on a non-transitory computer readable storage medium, which when executed, may comprise the steps of the embodiments of the methods described above. Any reference to memory, storage, database, or other medium used in the various embodiments provided herein may include non-volatile and/or volatile memory. The nonvolatile memory can include Read Only Memory (ROM), programmable ROM (PROM), electrically Programmable ROM (EPROM), electrically Erasable Programmable ROM (EEPROM), or flash memory. Volatile memory can include Random Access Memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in a variety of forms such as Static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double Data Rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous Link DRAM (SLDRAM), memory bus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), among others.
It will be apparent to those skilled in the art that, for convenience and brevity of description, only the above-described division of the functional units and modules is illustrated, and in practical application, the above-described functional distribution may be performed by different functional units and modules according to needs, i.e. the internal structure of the apparatus is divided into different functional units or modules to perform all or part of the above-described functions.
The above embodiments are only for illustrating the technical solution of the present invention, and not for limiting the same; although the invention has been described in detail with reference to the foregoing embodiments, it will be understood by those of ordinary skill in the art that: the technical scheme described in the foregoing embodiments can be modified or some technical features thereof can be replaced by equivalents; such modifications and substitutions do not depart from the spirit and scope of the technical solutions of the embodiments of the present invention, and are intended to be included in the scope of the present invention.
Claims (6)
1. A method for processing a database table water level, comprising:
determining a target database to be cleaned;
searching each table meeting preset cleaning conditions from the target database to serve as each target table to be cleaned;
cleaning the data on each target table;
locking the statistical information of each target table;
reconstructing indexes of the target tables respectively;
releasing the statistical information of each target table;
the target database comprises more than two storage partitions, each table meeting the preset cleaning condition is searched from the target database, and each target table to be cleaned comprises:
reading the last use time of each storage partition in the target database;
determining a memory partition with the last use time meeting a preset use timeout condition from the memory partitions;
all tables in the determined storage partition are determined to be all target tables to be cleaned; the processing method of the database table water level further comprises the following steps:
when the statistical information of each target table is locked, recording the current statistical information of each target table as first statistical information;
after releasing the statistical information of each target table, recording the current statistical information of each target table as second statistical information;
comparing the second statistical information with the first statistical information;
if the second statistical information is inconsistent with the first statistical information, sending a message about failure of reconstructing the index of the target table to a designated person; searching each table meeting preset cleaning conditions from the target database, wherein each target table to be cleaned comprises:
reading pre-configured failure configuration information;
searching tables in which data in a table are failure data from the target database according to the failure configuration information;
and determining each searched table as each target table to be cleaned.
2. The method for processing the table level of the database according to claim 1, wherein the determining the target database to be cleaned comprises:
reading pre-configured failure configuration information;
searching databases with failure data from the databases according to the failure configuration information;
and determining the searched database as a target database to be cleaned.
3. A database table level processing device, comprising:
the database determining module is used for determining a target database to be cleaned;
the target table searching module is used for searching each table meeting the preset cleaning conditions from the target database and taking the table as each target table to be cleaned;
the table data cleaning module is used for cleaning the data on each target table;
the statistical information locking module is used for locking the statistical information of each target table;
the table index rebuilding module is used for rebuilding the indexes of the target tables respectively;
the statistical information releasing module is used for releasing the statistical information of each target table;
wherein the target database comprises more than two memory partitions, and the target table lookup module comprises:
the using time reading unit is used for reading the last using time of each storage partition in the target database;
the partition determining unit is used for determining a memory partition with the last use time meeting a preset use timeout condition from the memory partitions;
a first target table determining unit, configured to determine all tables in the determined storage partition as respective target tables to be cleaned; the processing device of the database table water level further comprises:
the first information recording module is used for recording the current statistical information of each target table as first statistical information when the statistical information of each target table is locked;
the second information recording module is used for recording the current statistical information of each target table as second statistical information after releasing the statistical information of each target table;
the information comparison module is used for comparing the second statistical information with the first statistical information;
the information sending module is used for sending a message about failure in reconstructing the index of the target table to a designated person if the comparison result of the information comparison module is inconsistent; the target table lookup module includes:
a failure configuration reading unit for reading the pre-configured failure configuration information;
the failure data searching unit is used for searching each table of which the data in the table is failure data from the target database according to the failure configuration information;
and the second target table determining unit is used for determining each searched table as each target table to be cleaned.
4. A processing apparatus for database table levels as recited in claim 3, wherein said database determining module comprises:
a pre-reading unit for reading pre-configured failure configuration information;
the database searching unit is used for searching databases with failure data from the databases according to the failure configuration information;
and the target database determining unit is used for determining the searched database as a target database to be cleaned.
5. A computer device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, characterized in that the processor implements a method of processing a database table level according to any one of claims 1 to 2 when executing the computer program.
6. A computer-readable storage medium storing a computer program, characterized in that the computer program, when executed by a processor, implements a method of processing a database table level according to any one of claims 1 to 2.
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 CN110287183A (en) | 2019-09-27 |
| CN110287183B true 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) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108287835B (en) * | 2017-01-09 | 2022-06-21 | 腾讯科技（深圳）有限公司 | Data cleaning method and device |
| CN113468167B (en) * | 2020-03-31 | 2023-04-11 | ***通信集团湖南有限公司 | Database high water level recovery method and device and electronic equipment |
Citations (5)
| 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 |
| 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 | 平安科技（深圳）有限公司 | Tables of data method for cleaning, server and computer readable storage medium |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20120136869A1 (en) * | 2010-11-30 | 2012-05-31 | Sap Ag | System and Method of Processing Information Stored in Databases |
-
2019
- 2019-05-23 CN CN201910432704.9A patent/CN110287183B/en active Active
Patent Citations (5)
| 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 |
| 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 | 平安科技（深圳）有限公司 | Tables of data method for cleaning, server and computer readable storage medium |
Non-Patent Citations (1)
| Title |
|---|
| "利用表分区的大数据库优化方法";王佳等;《大连工业大学学报》;全文 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110287183A (en) | 2019-09-27 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11429641B2 (en) | Copying data changes to a target database | |
| CN112181945B (en) | Data archiving processing method, device, computer equipment and storage medium | |
| CN110209521B (en) | Data verification method and device, computer readable storage medium and computer equipment | |
| EP3125120A1 (en) | System and method for consistency verification of replicated data in a recovery system | |
| CN109086295B (en) | Data synchronization method, device, computer equipment and storage medium | |
| US10481988B2 (en) | System and method for consistency verification of replicated data in a recovery system | |
| CN108509462B (en) | Method and device for synchronizing activity transaction table | |
| CN102231168B (en) | Method for quickly retrieving resume from resume database | |
| CN105824846B (en) | Data migration method and device | |
| CN112969996A (en) | Tracking intermediate changes in database data | |
| CN107665219B (en) | Log management method and device | |
| US11210274B2 (en) | Prediction and repair of database fragmentation | |
| CN110287183B (en) | Processing method and device for database table water level, computer equipment and storage medium | |
| US20230137119A1 (en) | Method for replaying log on data node, data node, and system | |
| US20180032567A1 (en) | Method and device for processing data blocks in a distributed database | |
| CN106776795B (en) | Data writing method and device based on Hbase database | |
| RU2711348C1 (en) | Method and system for processing requests in a distributed database | |
| US10268776B1 (en) | Graph store built on a distributed hash table | |
| JP6470391B2 (en) | Method and apparatus for rebuilding standby node database | |
| CN107644041B (en) | Policy settlement processing method and device | |
| US20130080395A1 (en) | Data archiving methods and data archiving apparatus | |
| CN117131014A (en) | Database migration method, device, equipment and storage medium | |
| US20230195582A1 (en) | Rolling back a database transaction | |
| CN116225627A (en) | Transaction recording method and system | |
| CN111045869B (en) | Data backup method and device and readable storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |