CN116594984A - Database management method, device, equipment and storage medium - Google Patents
Database management method, device, equipment and storage medium Download PDFInfo
- Publication number
- CN116594984A CN116594984A CN202310593619.7A CN202310593619A CN116594984A CN 116594984 A CN116594984 A CN 116594984A CN 202310593619 A CN202310593619 A CN 202310593619A CN 116594984 A CN116594984 A CN 116594984A
- Authority
- CN
- China
- Prior art keywords
- target table
- time
- database
- data
- target
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000007726 management method Methods 0.000 title claims abstract description 59
- 230000010076 replication Effects 0.000 claims abstract description 78
- 238000000034 method Methods 0.000 claims abstract description 35
- 238000012544 monitoring process Methods 0.000 claims abstract description 20
- 230000003362 replicative effect Effects 0.000 claims abstract description 6
- 238000004590 computer program Methods 0.000 claims description 9
- 238000012217 deletion Methods 0.000 claims description 3
- 230000037430 deletion Effects 0.000 claims description 3
- 230000004044 response Effects 0.000 abstract description 8
- 239000012634 fragment Substances 0.000 abstract description 5
- 238000010586 diagram Methods 0.000 description 15
- 230000008569 process Effects 0.000 description 14
- 238000012545 processing Methods 0.000 description 6
- 238000012423 maintenance Methods 0.000 description 4
- 238000005457 optimization Methods 0.000 description 2
- 238000003491 array Methods 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 238000011217 control strategy Methods 0.000 description 1
- 238000013500 data storage Methods 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 238000013467 fragmentation Methods 0.000 description 1
- 238000006062 fragmentation reaction Methods 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 230000006872 improvement Effects 0.000 description 1
- 230000003993 interaction Effects 0.000 description 1
- 238000013508 migration Methods 0.000 description 1
- 230000005012 migration Effects 0.000 description 1
- 238000006467 substitution reaction 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/217—Database tuning
-
- 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/2282—Tablespace storage structures; Management thereof
-
- 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/24—Querying
- G06F16/242—Query formulation
-
- 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/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Computing Systems (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application discloses a database management method, a device, equipment and a storage medium, which aim to solve the problems that a large amount of fragments are generated in a table of a financial database due to frequent inquiry or input of data in the financial database by sales personnel in a financial system, so that a large amount of storage space is wasted, and the execution performance and response speed of the financial database are reduced, and the method comprises the following steps: determining a target table to be optimized; monitoring whether the target table is in an idle state, and if the target table is in the idle state, acquiring idle time corresponding to the idle state of the target table; obtaining the estimated replication time of the target table, and judging whether the idle time is larger than the estimated replication time; and if the idle time is greater than or equal to the estimated replication time, replicating the target table to an empty data table, and deleting the target table. The application can reduce the requirement on the storage space of the financial database and simplify the management work of the user on the financial database.
Description
Technical Field
The present application relates to the field of financial database management technologies, and in particular, to a database management method, apparatus, device, and storage medium.
Background
For the financial system, because the financial system needs to communicate with all kinds of users in the daily selling process and the intention products of different users are different, sales staff need to search the product information in the financial database and the personal asset information of the users more frequently in the working process, and the related data are continuously input. The above-mentioned query and entry process can frequently insert and delete data in the financial database, and a large amount of fragments are generated in the table where data operations are frequently performed, so that a large amount of storage space is wasted, and the performance of the structured execution language (Structured Query Language, SQL) in the running process of the financial database is reduced, and the response speed to the server request becomes extremely slow.
Disclosure of Invention
The embodiment of the application provides a database management method, a device, equipment and a storage medium, which can greatly shorten the requirement of the financial field on the storage space of a database by transferring the data of a target table to be optimized in the financial database, and further solve the problem of reduced response speed of the financial database caused by excessive occupation of the storage space of the financial database.
In a first aspect, the present application provides a database management method, the database management method including:
determining a target table to be optimized;
monitoring whether the target table is in an idle state, and if the target table is in the idle state, acquiring idle time corresponding to the idle state of the target table;
obtaining the estimated replication time of the target table, and judging whether the idle time is larger than the estimated replication time;
and if the idle time is greater than or equal to the estimated replication time, replicating the target table to an empty data table, and deleting the target table.
In a second aspect, the present application provides a database management apparatus comprising:
the target determining module is used for determining a target table to be optimized;
the idle monitoring module is used for monitoring whether the target table is in an idle state or not, and acquiring idle time corresponding to the idle state of the target table if the target table is in the idle state;
the acquisition judging module is used for acquiring the estimated replication time of the target table and judging whether the idle time is larger than the estimated replication time or not;
and the duplication deletion module copies the target table to an empty data table and deletes the target table if the idle time is greater than or equal to the estimated duplication time.
In a third aspect, the present application provides a computer device, including a memory and a processor, where the memory is connected to the processor and is used to store a program, and the processor is used to implement the database management method as described above by running the program stored in the memory.
In a fourth aspect, the present application also provides a computer readable storage medium storing a computer program which, when executed by a processor, causes the processor to implement a database management method as described above.
According to the database management method disclosed by the application, the target table to be optimized is obtained, when the target table is monitored to be in the idle state, the idle time corresponding to the idle state of the target table is compared with the estimated replication time, and when the idle time is greater than or equal to the estimated replication time, the target table is replicated to the idle data table, and the target table is deleted.
It is to be understood that both the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the application as claimed.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for the description of the embodiments will be briefly described below, and it is obvious that the drawings in the following description are some embodiments of the present application, and other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a schematic diagram of a database management system according to an embodiment of the present application;
FIG. 2 is a schematic flow chart of steps of a database management method according to an embodiment of the present application;
FIG. 3 is a schematic flow chart of steps of a target table determination method provided by an embodiment of the present application;
FIG. 4 is a schematic flow chart of steps of another method for determining a target table provided by an embodiment of the present application;
FIG. 5 is a schematic diagram of a method for file name replacement provided by an embodiment of the present application;
FIG. 6 is a schematic diagram of a control strategy selection interface provided by an embodiment of the present application;
FIG. 7 is a schematic diagram of a method for segment replication of a table provided by an embodiment of the present application;
FIG. 8 is a schematic diagram of a method for updating estimated time according to an embodiment of the present application;
fig. 9 is a schematic block diagram of a server management apparatus provided by an embodiment of the present application;
fig. 10 is a schematic block diagram of a computer device according to an embodiment of the present application.
It is to be understood that both the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the application as claimed.
Detailed Description
The following description of the embodiments of the present application 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 application. All other embodiments, which can be made by those skilled in the art based on the embodiments of the application without making any inventive effort, are intended to be within the scope of the application.
The flow diagrams depicted in the figures are merely illustrative and not necessarily all of the elements and operations/steps are included or performed in the order described. For example, some operations/steps may be further divided, combined, or partially combined, so that the order of actual execution may be changed according to actual situations.
It is to be understood that the terminology used in the description of the application herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the application. As used in this specification and the appended claims, the singular forms "a," "an," and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise.
It should also be understood that the term "and/or" as used in the present specification and the appended claims refers to any and all possible combinations of one or more of the associated listed items, and includes such combinations.
In order to facilitate understanding of the embodiments of the present application, some words related to the embodiments of the present application are briefly described below.
1. Database: in a simple way, the method can be regarded as a place for storing the electronic file, and a user can perform operations such as adding, intercepting, updating, deleting and the like on the data in the file.
Databases refer to data sets that are stored together in a manner, can be shared among multiple users, have as little redundancy as possible, and are independent of the application.
In the daily work of financial management, when sales personnel acquire data of customers and products, it is often necessary to put some relevant data into a financial database and perform corresponding processing according to the management needs. For example, the sales department of a financial institution often stores the basic conditions of the company's products (price, for customer characteristics, product characteristics, etc.) in tables, which can be considered a database, respectively.
Through invoking the database, the required information can be acquired by related personnel conveniently and rapidly to a great extent, and great convenience is brought to the improvement of the working efficiency.
2. Structured execution language: is a standard language for operating and retrieving relational databases, developed by IBM corporation in the 70 s of the 20 th century, and is currently applied to various relational databases.
The structured query language is a database query and programming language for accessing data and querying, updating and managing a relational database system; and is also an extension of the database script file.
The structured query language is a high-level, non-procedural programming language that allows users to work on high-level data structures. The method does not require the user to specify a storage method for data and does not require the user to know a specific data storage mode, so that different database systems with completely different substructures can use the same structured query language as an interface for data input and management. The structured query language statements can be nested, which gives him great flexibility and powerful functionality.
3. Fragments: when a database is created, it is divided into a plurality of logical segments called tablespaces (tablespaces), such as system tablespaces, temporary tablespaces, and the like. A tablespace may contain a plurality of data ranges and one or more free range blocks, i.e., free space.
When a segment is generated in the tablespace, space will be allocated for the initial range of that segment from the tablespace active free space. When these initial ranges are full of data, the segment may request an addition of another range. Such an expansion process will continue until the maximum range value is reached or there is no free space in the tablespace for the next range. The most desirable state is that one segment of data can be stored in a single range. In this way, all data is stored close to other data within the segment, and less pointers are available to find the data. But the case where a segment contains a plurality of ranges is in a large number, there is no measure to ensure that these ranges are stored adjacently.
When a space requirement is to be met, the database normally no longer merges adjacent free ranges, but instead finds the largest free range in the tablespace for use. This will gradually create more and more discrete, separate, smaller free spaces, i.e. fragments.
Over time, the widespread use of database-based applications, with the resulting increased fragmentation, will have the following impact on the database:
1) Leading to reduced system performance: as mentioned above, when a space requirement is to be met, the database will first find the current maximum free range, while as the "maximum" free range becomes smaller, it has become increasingly difficult to find a sufficiently large free range, resulting in speed obstacles in the tablespace, which increasingly move the spatial allocation of the database away from ideal;
2) Wasting a lot of table space: although some free ranges are periodically merged by the system monitoring background process, some free ranges cannot be automatically merged all the time, and a large amount of table space is wasted.
When sales personnel in the existing financial industry introduce different products in the process of talking with clients, the sales personnel need to frequently inquire product information and personal asset information of users in a financial database and continuously input perfect related information, and the process of inquiring and inputting frequently inserts and deletes data in the corresponding financial database, so that the data volume of the storage space of the database is continuously increased, the performance of the structured execution language of the database is reduced in the running process of the database, and the response speed to a server request is extremely slow.
The existing financial database management method is required to be developed under the condition of suspending the work of the related table, the accurate estimation of the maintenance time length is very difficult, and the front-end personnel can not avoid the omission of maintenance by manually checking the space to be cleaned in the financial database one by one, so the intelligent management method of the financial database is very urgent.
Some embodiments of the present application are described in detail below with reference to the accompanying drawings. The following embodiments and features of the embodiments may be combined with each other without conflict.
Referring to fig. 1, fig. 1 is a schematic structural diagram of a database management system according to an embodiment of the present application, in which a server corresponds to various databases, and a user can implement interaction between a terminal device and a database by using a structured execution language to query corresponding information of the database corresponding to the server at the terminal device.
Specifically, referring to fig. 2, fig. 2 is a schematic flowchart illustrating steps of a database management method according to an embodiment of the present application, where the method may be applied to the server in fig. 1.
As shown in fig. 2, the database management method includes steps S101 to S104.
S101, determining a target table to be optimized.
Specifically, the target table may be determined according to the number of data processing, or may be determined according to a table feature, or may be a table that is specified by a user to be optimized in the financial database, and by confirming the corresponding target table according to a requirement, a problem that occurs in the use process of the financial database may be solved in time, or in other manners, for example, the server may determine a plurality of data tables to be optimized according to the number of data processing, and then the user specifies one of the data tables to be optimized as the target table.
In some embodiments, referring to fig. 3, fig. 3 is a schematic flowchart illustrating steps of a target table determining method according to an embodiment of the present application, where the target table determining method includes steps S101a to S101b.
S101a, scanning in a database by adopting a structured execution language, obtaining the operation times of a data table in the database, and judging whether the operation times of the table are larger than a preset threshold value.
S101b, if the operation times of the data table are larger than a preset threshold, determining the data table as the target table to be optimized.
For example, when the financial database stores 50 preset threshold values, the structured execution language is adopted to scan the financial database to obtain the data operation times of the table in the financial database, and whether the data operation times of the table are greater than 50 times is judged, all the tables with the data processing times greater than 50 times in the financial database are determined as target tables, the 50 times are only illustrative, the preset threshold values in practical application may be far greater than 50 times, and the size of the preset threshold values is not limited herein.
In some embodiments, referring to fig. 4, fig. 4 is a flowchart illustrating steps of another method for determining an object table according to an embodiment of the present application, where the method for determining an object table includes steps S101c to S101d.
S101c, judging whether a data table in a database contains a specified identifier or not;
and S101d, if the data table contains the specified identification, determining the data table as a target table to be optimized.
For example, when the designated identifier of the data table stored in the financial database is client information, the structured execution language is adopted to scan in the database, so as to obtain the data identifier of the table in the financial database, determine whether the identifier of the table contains the client information, and determine that all the tables of the table containing the client information in the financial database are target tables.
In some embodiments, the user sends identification information of the target table to the server, the identification information including a file name or index information of the table. The determining the target table to be optimized may specifically include: scanning in a database by adopting a structured execution language, matching in the database according to the identification information of the target table, and determining the table successfully matched as the target table to be optimized.
For example, when a worker of a financial system finds that new information cannot be entered in customer information or the response speed to inquiring customer information in the table becomes extremely slow, the table in which the customer information is recorded is taken as a target table, and identification information of the table in which the customer information is recorded is transmitted to a server so as to resume normal use of the target table at the first time.
S102, monitoring whether the target table is in an idle state, and if so, acquiring idle time corresponding to the idle state of the target table.
Specifically, the monitoring of the target table may be that the server monitors according to a stored monitoring period, or may be that the server monitors the target table in real time, or may also monitor according to an instruction sent by a user through a terminal device, and an optimal database management scheme may be obtained as far as possible through the monitoring of the target table.
Illustratively, the server stores a monitoring period of 10 minutes, and monitors whether the current state of the target table is an idle state every 10 minutes.
The server stores a monitoring module, and the monitoring module can feed back the idle state of the target table to the server in real time.
For example, when the user calls the table in the financial database and has errors or slow response, the table with errors or slow response is determined as the target table, an instruction is sent to the server to monitor the target table, and when the target table is monitored to be in an idle state, the data of the target table is processed.
Specifically, whether the target table is in an idle state or not is judged by judging whether the idle time of the target table is longer than the preset time stored by the server, whether the target table is in the idle state or not is judged by judging whether the number of code segments which are not called to the target table in a structured execution statement which is operated next by the server is longer than the preset number of segments, and the optimal financial database management scheme is obtained in a non-stop state as far as possible through judging the idle state of the target table.
For example, the server stores a preset time of 1 minute, and when the idle time of the target table is monitored to be greater than or equal to 1 minute, the target table is considered to be in an idle state.
For example, the server stores a preset number of 200 segments, and when the target table is called after the code from the 200 th segment in the structured execution statement which is operated by the server next is monitored, the target table is considered to be in an idle state.
Specifically, the idle time can be obtained through monitoring by the server, and the idle time can also be estimated through the structured execution language received by the server, or the idle time of the target table can be calculated according to the past idle data.
The server stores a monitoring module, through which the idle time of the target table can be fed back to the server.
The server can, for example, estimate the idle time of the target table by estimating the time required for computing the code position for calling the target table after receiving the structured execution language sent by the terminal device.
The server stores the historical running time of each table, and can calculate the idle time distribution rule of the target table by extracting the historical idle time of the target table, so as to calculate the idle time of the target table.
S103, obtaining the estimated replication time of the target table, and judging whether the idle time is larger than the estimated replication time or not:
specifically, after determining the target table, the server determines the time required for copying the data of the target table according to the data size of the target table as the estimated copying time, compares the estimated copying time with the idle time, and comprehensively evaluates according to the time, the importance of the table and other dimensions to obtain the optimal database management scheme.
For example, when the data size of the target table is 1GB, it is monitored that the target table is in an idle state and the idle time is 3 minutes, the server determines that 2 minutes are required for copying 1GB of data, and at this time, the estimated copy time is less than the idle time, and the server may directly copy the data of the target table.
It should be noted that, the estimated replication time is not the time required for actually replicating the target table, the estimated replication time may be greater than, less than or equal to the target table time replication time, the estimated replication time is only the time required for the server to perform preliminary judgment on replication according to the data size of the target table and the speed of reciprocation, and the actual replication process may be comprehensively influenced by a series of factors such as the CPU utilization rate, the number of background running programs, external hardware facilities and the like in the replication process, but according to the estimated replication time as the basis of the database management method, a reasonable financial database management method can be provided in preliminary theory, and the server can provide an optimal management method based on the estimated replication time better as management records accumulate later.
S104, if the idle time is greater than or equal to the estimated replication time, replicating the target table to the empty data table, and deleting the target table.
Specifically, when the idle time is longer than the estimated replication time, the server may directly start the optimization operation on the target table, replicate the data of the target table into an idle data table which is not subjected to the data operation, delete the target table after the replication is completed, and at this time, fragments generated by frequent data operation on the target table can be cleared simultaneously, so that the situation that the data space of the target table is greatly wasted due to frequent operation on the target table can be effectively reduced by transferring the data of the target table, thereby improving the response speed of the table.
In some embodiments, after deleting the target table, further comprising: the file name of the empty data table is named as the file name of the target table, and the file name of the empty data table is replaced by the file name of the target table, so that the user can call the data content originally positioned in the target table without changing the sentence of the structured execution language at the terminal equipment, and the operation of the user is simplified.
For example, as shown in fig. 5, the file name of the target table is the client information table, and when the data of the target table is copied to the empty data table, the file name of the empty data table is modified to the client information table after the target table is deleted.
In some embodiments, a database management method includes: if the idle time is smaller than the estimated replication time, transmitting the information of the target table, the estimated replication time and the idle time to the terminal equipment; the terminal equipment sends a first control instruction or a second control instruction to the database according to the information of the target table and the estimated replication time and the idle time thereof, wherein the first control instruction is used for controlling the stopping work of the database to optimize the target table, and the second control instruction is used for controlling the continuous work of the database; when a second control instruction is received, the database judges whether the target table can be optimized or not when the target table is in an idle state again, or the data of the target table are respectively copied to an idle data table in multiple idle times of the target table; and deleting the target table when all the data of the target table are copied to the empty data table.
When the idle time is smaller than the estimated copy time, the server sends information including the data size, the function, the file name and the like of the target table, and the copy time and the idle time of the target table to the terminal device, as shown in fig. 6, fig. 6 is a schematic diagram of a control policy selection interface provided by the embodiment of the present application, at this time, after the terminal device receives the information of the target table, the copy time and the idle time, the user can comprehensively determine an optimal optimization scheme for the target table, and select a first control signal for stopping and maintaining the target table or a second control signal for prioritizing the target table to complete the work on the display interface, so that the user can give the most suitable management method after comprehensively considering the operation time and the urgent and heavy dimension of the target table work.
The server pauses the working task of the target table, performs shutdown maintenance, copies the data of the target table to the empty data table, deletes the target table, enables the empty data table to work, and controls the target table to perform shutdown maintenance by adopting the first control signal.
The server continues the subsequent work tasks of the target table after the user sends the second control signal to the database through the display interface, and on the one hand, when the target table has a certain data space, the server makes the target table continue to work by adopting the second control signal, and then judges the management method of the target table when the next time the target table is monitored to be in an idle state; on the other hand, when the follow-up task of the target table is important, the target table is managed after completing the work.
In some embodiments, a method of managing a database includes: if the idle time is smaller than the estimated copying time, copying the data of the target table to an empty data table in the idle time, and recording the final position of the copy in the target table; when the target table is detected to be in the idle state again, copying the data of the target table to an empty data table from the end position; and deleting the target table until all the data of the target table are copied into the empty data table.
As shown in fig. 7, fig. 7 is a schematic diagram of a table segment replication method according to an embodiment of the present application, when the idle time of the target table is 5 minutes and the data size of the target table is 1GB, the estimated replication time of the target table is 10 minutes, 500MB data of the target table is first replicated to the empty data table within 5 minutes, the data replication endpoint at this time is recorded as an a point, when the target table is monitored to be in the idle time again, the replication time required by remaining 500MB data of the target table from the a point is 5 minutes, the idle time of the target table is monitored to be 6 minutes, the server continues to begin to replicate the data of the target table from the a point, and after completing replication of all data of the target table, the target table is deleted.
It should be noted that, multiple copies of the data of the target table need to be copied to the same empty data table, for example, migration of the data of the target table to multiple empty data tables may cause complicated operation of calling the target table by a subsequent user, which is not beneficial to simplifying management of the database.
As shown in fig. 8, fig. 8 is a schematic diagram of a method for estimating copy time update according to an embodiment of the present application, and in some embodiments, after deleting a target table, the method further includes: acquiring the actual copying time of the target table, and storing the data size, the estimated copying time and the actual copying time of all the optimized target tables; when the database is determined to have a new target table to be optimized, determining the estimated replication time of the new target table to be optimized according to the data size, the estimated replication time, the actual replication time of all the optimized target tables and the data size and the estimated replication time of the new target table to be optimized.
For example, since the accuracy of the estimated replication time of the target table cannot be judged, in each case, the management data of the target table of the database is a data sample with reference significance, the management data includes the data size, the estimated replication time and the actual replication time of the target table, the server can obtain the relationship between the actual replication time and the data size, and the estimated replication time of the target table by storing enough pipeline data, and when a new target table to be optimized exists, the server can update the new estimated replication time of the target table to be optimized by obtaining the data size and the estimated replication time of the new target table to be optimized, thereby further improving the accuracy of the estimated replication time and enabling the adopted database management method to obtain better effects.
According to the database management method provided by the application, the target table to be optimized is obtained, when the target table is in the idle state, the idle time corresponding to the idle state of the target table is compared with the estimated replication time, and when the idle time is greater than or equal to the estimated replication time, the target table is replicated to the empty data table, and the target table is deleted.
Referring to fig. 9, fig. 9 is a schematic block diagram of a database management device according to an embodiment of the present application, where the database management device is configured to perform the foregoing database management method. Wherein, the database management device can be configured on a terminal or a server.
As shown in fig. 9, the database management apparatus 200 includes: the system comprises a target determining module 201, an idle monitoring module 202, an acquisition judging module 203 and a copy deleting module 204.
The objective determining module 201 is configured to determine an objective table to be optimized.
The idle monitoring module 202 is configured to monitor whether the target table is in an idle state, and if the target table is in the idle state, obtain an idle time corresponding to the target table being in the idle state.
And the acquiring and judging module 203 is configured to acquire the estimated replication time of the target table, and judge whether the idle time is greater than the estimated replication time.
And a copy deletion module 204, wherein the idle time is greater than or equal to the estimated copy time, copies the target table to an empty data table, and deletes the target table.
The update estimation module 205 is configured to obtain an actual replication time of the target table, and store the data sizes, the estimated replication time and the actual replication time of all the optimized target tables; when the fact that a new target table to be optimized exists in the database is determined, determining the estimated replication time of the new target table to be optimized according to the data size, the estimated replication time and the actual replication time of all the optimized target tables, and the data size and the estimated replication time of the new target table to be optimized.
A data renaming module 206, configured to name the file name of the empty data table as the file name of the target table.
It should be noted that, for convenience and brevity of description, the specific working process of the apparatus and each module described above may refer to the corresponding process in the foregoing method embodiment, which is not described herein again.
The apparatus described above may be implemented in the form of a computer program which is executable on a computer device as shown in fig. 10.
Referring to fig. 10, fig. 10 is a schematic block diagram of a computer device according to an embodiment of the present application. The computer device may be a server.
With reference to FIG. 10, the computer device includes a processor, memory, and a network interface connected by a system bus, where the memory may include a non-volatile storage medium and an internal memory.
The non-volatile storage medium may store an operating system and a computer program. The computer program comprises program instructions that, when executed, cause a processor to perform any of a number of database management methods.
The processor is used to provide computing and control capabilities to support the operation of the entire computer device.
The internal memory provides an environment for the execution of a computer program in a non-volatile storage medium that, when executed by a processor, causes the processor to perform any of a number of database management methods.
The network interface is used for network communication such as transmitting assigned tasks and the like. It will be appreciated by those skilled in the art that the structure shown in FIG. 10 is merely a block diagram of some of the structures associated with the present inventive arrangements and is not limiting of the computer device to which the present inventive arrangements may be applied, and that a particular computer device may include more or fewer components than shown, or may combine some of the components, or have a different arrangement of components.
It should be appreciated that the processor may be a central processing unit (Central Processing Unit, CPU), but may also be other general purpose processors, digital signal processors (Digital Signal Processor, DSP), application specific integrated circuits (Application Specific Integrated Circuit, ASIC), field-programmable gate arrays (Field-Programmable Gate Array, FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or the like. Wherein the general purpose processor may be a microprocessor or the processor may be any conventional processor or the like.
Wherein in one embodiment the processor is configured to run a computer program stored in the memory to implement the steps of: determining a target table to be optimized; monitoring whether the target table is in an idle state, and if the target table is in the idle state, acquiring idle time corresponding to the idle state of the target table; obtaining the estimated replication time of the target table, and judging whether the idle time is larger than the estimated replication time; and if the idle time is greater than or equal to the estimated replication time, replicating the target table to an empty data table, and deleting the target table.
In some embodiments, after the copying the target table to the empty data table, the processor further implements: and naming the file name of the empty data table as the file name of the target table.
In some embodiments, if the idle time is less than the estimated replication time, the processor sends the information of the target table, the estimated replication time and the idle time to a terminal device for display, so that the terminal device sends a first control instruction or a second control instruction to a server according to a user operation; the first control instruction is used for controlling the database to stop working so as to optimize the target table, and the second control instruction is used for controlling the database to continue working.
In some embodiments, if the idle time is less than the estimated replication time, the processor replicates the data of the target table to the empty data table in the idle time, and records the end position of the replication in the target table; copying the data of the target table to the empty data table from the end point position when the target table is detected to be in the idle state again; and deleting the target table until all the data of the target table are copied into the empty data table.
In some embodiments, after said deleting said target table, said processor further implements: acquiring the actual copying time of the target table, and storing the data size, the estimated copying time and the actual copying time of all the optimized target tables; when the fact that a new target table to be optimized exists in the database is determined, determining the estimated replication time of the new target table to be optimized according to the data size, the estimated replication time and the actual replication time of all the optimized target tables, and the data size and the estimated replication time of the new target table to be optimized.
In some embodiments, the determining the target table to be optimized includes the processor implementing scanning in the database by using a structured execution language, obtaining operation times of a data table in the database, and determining whether the operation times of the table are greater than a preset threshold; and if the operation times of the data table are larger than a preset threshold value, determining the data table as the target table to be optimized.
In some embodiments, the determining the target table to be optimized includes the processor implementation determining whether a data table in the database contains a specified identification; and if the data table contains the appointed identification, determining the data table as the target table to be optimized.
The embodiment of the application also provides a computer readable storage medium, wherein the computer readable storage medium stores a computer program, the computer program comprises program instructions, and the processor executes the program instructions to realize the database management method of any application program provided by the embodiment of the application.
The computer readable storage medium may be an internal storage unit of the computer device according to the foregoing embodiment, for example, a hard disk or a memory of the computer device. The computer readable storage medium may also be an external storage device of the computer device, such as a plug-in hard disk, a Smart Media Card (SMC), a Secure Digital (SD) Card, a Flash memory Card (Flash Card), or the like, which are provided on the computer device.
While the application has been described with reference to certain preferred embodiments, it will be understood by those skilled in the art that various changes and substitutions of equivalents may be made and equivalents will be apparent to those skilled in the art without departing from the scope of the application. Therefore, the protection scope of the application is subject to the protection scope of the claims.
Claims (10)
1. A database management method, characterized in that the database management method comprises:
determining a target table to be optimized;
monitoring whether the target table is in an idle state, and if the target table is in the idle state, acquiring idle time corresponding to the idle state of the target table;
obtaining the estimated replication time of the target table, and judging whether the idle time is larger than the estimated replication time;
and if the idle time is greater than or equal to the estimated replication time, replicating the target table to an empty data table, and deleting the target table.
2. The database management method according to claim 1, wherein the database management method comprises:
if the idle time is smaller than the estimated replication time, the information of the target table, the estimated replication time and the idle time are sent to a terminal device for display, so that the terminal device sends a first control instruction or a second control instruction to a server according to the operation of a user;
the first control instruction is used for controlling the database to stop working so as to optimize the target table, and the second control instruction is used for controlling the database to continue working.
3. The database management method according to claim 1, wherein the database management method comprises:
if the idle time is smaller than the estimated copying time, copying the data of the target table to the empty data table in the idle time, and recording the final position of the copying in the target table;
copying the data of the target table to the empty data table from the end point position when the target table is detected to be in the idle state again;
and deleting the target table until all the data of the target table are copied into the empty data table.
4. The database management method according to claim 1, further comprising, after said deleting said target table:
acquiring the actual copying time of the target table, and storing the data size, the estimated copying time and the actual copying time of all the optimized target tables;
when the fact that a new target table to be optimized exists in the database is determined, determining the estimated replication time of the new target table to be optimized according to the data size, the estimated replication time and the actual replication time of all the optimized target tables, and the data size and the estimated replication time of the new target table to be optimized.
5. The method for managing a database according to claim 1, wherein the determining a target table to be optimized includes:
scanning in the database by adopting a structured execution language, obtaining the operation times of a data table in the database, and judging whether the operation times of the table are greater than a preset threshold value or not;
and if the operation times of the data table are larger than a preset threshold value, determining the data table as the target table to be optimized.
6. The method for managing a database according to claim 1, wherein the determining a target table to be optimized includes:
judging whether a data table in the database contains a specified identifier or not;
and if the data table contains the appointed identification, determining the data table as the target table to be optimized.
7. The database management method according to claim 1, further comprising, after said copying the target table to a null data table:
and naming the file name of the empty data table as the file name of the target table.
8. A database management apparatus, characterized in that the database management apparatus comprises:
the target determining module is used for determining a target table to be optimized;
the idle monitoring module is used for monitoring whether the target table is in an idle state or not, and acquiring idle time corresponding to the idle state of the target table if the target table is in the idle state;
the acquisition judging module is used for acquiring the estimated replication time of the target table and judging whether the idle time is larger than the estimated replication time or not;
and the duplication deletion module copies the target table to an empty data table and deletes the target table if the idle time is greater than or equal to the estimated duplication time.
9. A computer device, the computer device comprising:
a memory and a processor;
the memory is connected with the processor and used for storing programs;
the processor is configured to implement the steps of the database management method according to any one of claims 1 to 7 by running a program stored in the memory.
10. A computer readable storage medium, characterized in that the computer readable storage medium stores a computer program which, when executed by a processor, causes the processor to implement the steps of the database management method according to any one of claims 1-7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310593619.7A CN116594984A (en) | 2023-05-23 | 2023-05-23 | Database management method, device, equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310593619.7A CN116594984A (en) | 2023-05-23 | 2023-05-23 | Database management method, device, equipment and storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN116594984A true CN116594984A (en) | 2023-08-15 |
Family
ID=87600481
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202310593619.7A Pending CN116594984A (en) | 2023-05-23 | 2023-05-23 | Database management method, device, equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN116594984A (en) |
-
2023
- 2023-05-23 CN CN202310593619.7A patent/CN116594984A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US20240020295A1 (en) | Incremental refresh of a materialized view | |
| CN100568240C (en) | Be used for setting up the method and system of database from backup data images | |
| JP5963845B2 (en) | Method and system for loading data into a temporary data warehouse | |
| JP4856541B2 (en) | Automatic and dynamic provisioning of databases | |
| CN101334797B (en) | Distributed file systems and its data block consistency managing method | |
| US8082273B2 (en) | Dynamic control and regulation of critical database resources using a virtual memory table interface | |
| JP4676199B2 (en) | Systems and methods for integrating, managing, and coordinating customer activities | |
| CN106598992B (en) | Database operation method and device | |
| US7698319B2 (en) | Database system management method, database system, database device, and backup program | |
| US8650224B2 (en) | Batching content management operations to facilitate efficient database interactions | |
| CN110019469B (en) | Distributed database data processing method and device, storage medium and electronic device | |
| US20050262158A1 (en) | System and method for externally providing database optimizer statistics | |
| CN104040481A (en) | Method Of And System For Merging, Storing And Retrieving Incremental Backup Data | |
| CN101263492A (en) | Transparent archiving | |
| CN109669929A (en) | Method for storing real-time data and system based on distributed parallel database | |
| JP2007241486A (en) | Memory system | |
| CN103177063A (en) | Time slider operator for temporal data aggregation | |
| CN110637292B (en) | System and method for querying a resource cache | |
| JP2003296171A (en) | Electronic business form management method and program | |
| US11537574B2 (en) | Autonomous database defragmentation | |
| EP2662783A1 (en) | Data archiving approach leveraging database layer functionality | |
| CN116594984A (en) | Database management method, device, equipment and storage medium | |
| US7213029B2 (en) | Quiescing work bounded by application transactions consisting of multiple relational database transactions | |
| CN113778975A (en) | Data processing method and device based on distributed database | |
| JP2000020374A (en) | Replication control 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 |