Database hole repairing method and device, electronic equipment and storage medium

Abstract

The embodiment of the invention provides a database hole repairing method and device, electronic equipment and a storage medium, and belongs to the technical field of financial science and technology. The method comprises the steps of regularly scanning a database, obtaining a data table generating holes in the database, obtaining a hole data table, obtaining a historical time period for executing data updating operation on the hole data table, determining a strategy execution time period according to the historical time period, obtaining a target repairing strategy of the hole data table in the strategy execution time period, and executing the target repairing strategy. And the void data table is repaired, so that the void of the database can be repaired, and the storage and query performance of the database is improved.

Description

Technical Field

[0001] The present application relates to the field of financial technology, and in particular to a database hole repair method and device, electronic equipment and storage medium. Background Art

[0002] In the database, due to operations such as data deletion and update, ineffectively utilized storage space will be generated. These storage spaces are not used, but exist in the physical storage structure of the database, forming database holes. Database holes increase the time overhead of data storage and query, seriously affecting the performance of the database. For example, when users conduct real-time transactions such as stock trading and payment transfers through financial trading systems, the financial trading system needs to quickly query the user's account balance. If there are a large number of holes in the database, the financial trading system needs to scan more data pages to find the required data, resulting in low efficiency of financial transactions. Therefore, how to repair database holes has become an urgent problem to be solved. Summary of the invention

[0003] The main purpose of the embodiments of the present application is to propose a database hole repair method and device, an electronic device and a storage medium, aiming to repair database holes and improve the storage and query performance of the database.

[0004] To achieve the above-mentioned purpose, a first aspect of an embodiment of the present application proposes a database hole repair method, the method comprising:

[0005] Scanning the database regularly to obtain a data table where a hole is generated in the database, thereby obtaining a hole data table;

[0006] Acquire a historical period during which data update operations are performed on the empty data table, and determine a policy execution period according to the historical period;

[0007] During the strategy execution period, obtaining a target repair strategy for the hole data table;

[0008] The target repair strategy is executed to repair the empty data table.

[0009] In some embodiments, the target repair strategy includes a first repair strategy, and executing the target repair strategy to repair the hole data table includes:

[0010] executing the first repair strategy and generating a space recovery instruction according to the first repair strategy;

[0011] The space of the hole data table is reclaimed according to the space reclaim instruction.

[0012] In some embodiments, the target repair strategy includes a second repair strategy, and executing the target repair strategy to repair the hole data table includes:

[0013] Execute the second repair strategy, and obtain the table structure and data records of the hole data table according to the second repair strategy;

[0014] Create a reference data table according to the table structure;

[0015] Migrating the data record to the reference data table;

[0016] The hole data table is overwritten according to the reference data table.

[0017] In some embodiments, migrating the data record to the reference data table includes:

[0018] Obtaining a unit migration data volume of the data record;

[0019] The data records are migrated to the reference data table one by one according to the unit migration data amount.

[0020] In some embodiments, covering the hole data table according to the reference data table includes:

[0021] Obtaining a data transmission log of the database;

[0022] According to the data transmission log, a data update operation performed on the empty data table during a migration period is acquired to obtain a target operation; the migration period is a period for migrating the data record to the reference data table;

[0023] Performing the target operation on the reference data table to obtain a candidate data table;

[0024] The empty data table is covered according to the candidate data table.

[0025] In some embodiments, obtaining a historical period of executing a data update operation on the empty data table, and determining a policy execution period according to the historical period includes:

[0026] Obtaining a data transmission log of the database;

[0027] Acquire the historical period of performing data update operation on the empty data table according to the data transmission log;

[0028] The historical time periods are screened to obtain the policy execution time periods.

[0029] In some embodiments, during the strategy execution period, obtaining a target repair strategy for the hole data table includes:

[0030] During the policy execution period, obtaining the number of data records in the empty data table and the storage space capacity of the database occupied by the empty data table;

[0031] A target repair strategy for the hole data table is determined according to the number of data records or the storage space capacity.

[0032] To achieve the above-mentioned purpose, a second aspect of an embodiment of the present application provides a database hole repairing device, the device comprising:

[0033] A scanning module, used for scanning the database at regular intervals, obtaining a data table where a hole is generated in the database, and obtaining a hole data table;

[0034] A time period acquisition module, used to acquire a historical time period for executing a data update operation on the empty data table, and determine a strategy execution time period according to the historical time period;

[0035] A strategy acquisition module, used for acquiring a target repair strategy for the hole data table during the strategy execution period;

[0036] An execution module is used to execute the target repair strategy to repair the empty data table.

[0037] To achieve the above objectives, a third aspect of an embodiment of the present application proposes an electronic device, which includes a memory and a processor, wherein the memory stores a computer program, and when the processor executes the computer program, the method described in the first aspect is implemented.

[0038] To achieve the above-mentioned purpose, the fourth aspect of an embodiment of the present application proposes a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method described in the first aspect is implemented.

[0039] The database hole repair method, database hole repair device, electronic device and computer-readable storage medium proposed in the embodiments of the present application obtain data tables with holes in the database by periodically scanning the database, so as to automatically identify tables with holes in the database and obtain empty data tables. The historical time periods for performing data update operations on empty data tables are obtained. In order to avoid the impact of database hole repair tasks on business processing, the policy execution period is determined according to the historical period to obtain a suitable time period for performing database hole repair. During the policy execution period, the target repair strategy for the empty data table is obtained to select the appropriate repair strategy in the appropriate time period to ensure the stable operation of the database. The target repair strategy is executed to repair the empty data table, thereby avoiding the impact of database holes on database query performance and storage performance, and improving the availability and stability of the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] Figure 1 is a flow chart of a database hole repair method provided by an embodiment of the present application;

[0041] Figure 2 yes Figure 1 Flow chart of step S120 in FIG.

[0042] Figure 3 yes Figure 1 Flow chart of step S130 in FIG.

[0043] Figure 4 yes Figure 1 Flow chart of step S140 in FIG.

[0044] Figure 5 yes Figure 1 Another flow chart of step S140 in FIG.

[0045] Figure 6 yes Figure 5 Flow chart of step S530 in FIG.

[0046] Figure 7 yes Figure 5 Flow chart of step S540 in FIG.

[0047] Figure 8 It is a structural schematic diagram of a database hole repairing device provided in an embodiment of the present application;

[0048] Fig. 9 It is a schematic diagram of the hardware structure of the electronic device provided in the embodiment of the present application. DETAILED DESCRIPTION

[0049] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0050] It should be noted that, although the functional modules are divided in the device schematic diagram and the logical order is shown in the flowchart, in some cases, the steps shown or described may be performed in a different order than the module division in the device or the order in the flowchart. The terms "first", "second", etc. in the specification, claims and the above drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence.

[0051] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as those commonly understood by those skilled in the art to which this application belongs. The terms used herein are only for the purpose of describing the embodiments of this application and are not intended to limit this application.

[0052] In the database, due to operations such as data deletion and update, ineffectively utilized storage space will be generated. These storage spaces are not used, but exist in the physical storage structure of the database, forming database holes. Database holes increase the time overhead of data storage and query, seriously affecting the performance of the database. For example, when users conduct real-time transactions such as stock trading and payment transfers through financial trading systems, the financial trading system needs to quickly query the user's account balance. If there are a large number of holes in the database, the financial trading system needs to scan more data pages to find the required data, resulting in low efficiency of financial transactions. Therefore, how to repair database holes has become an urgent problem to be solved.

[0053] Based on this, the embodiments of the present application provide a database hole repair method, a database hole repair device, an electronic device and a computer-readable storage medium, which aim to repair database holes and improve the storage and query performance of the database.

[0054] The database hole repair method, database hole repair device, electronic device and computer-readable storage medium provided in the embodiments of the present application are specifically illustrated through the following embodiments. First, the database hole repair method in the embodiments of the present application is described.

[0055] The database hole repair method provided in the embodiment of the present application relates to the field of financial technology technology. The database hole repair method provided in the embodiment of the present application can be applied to the terminal, can also be applied to the server side, and can also be software running in the terminal or the server side. In some embodiments, the terminal can be a smart phone, a tablet computer, a laptop computer, a desktop computer, etc.; the server side can be configured as an independent physical server, or it can be configured as a server cluster or distributed system composed of multiple physical servers, and can also be configured as a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN, and big data and artificial intelligence platforms; the software can be an application that implements the database hole repair method, etc., but is not limited to the above forms.

[0056] The present application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc. The present application can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present application can also be practiced in distributed computing environments, in which tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.

[0057] It should be noted that in each specific implementation of the present application, when it comes to the need to perform relevant processing based on data related to user identity or characteristics such as user information, user behavior data, user historical data, and user location information, the user's permission or consent will be obtained first, and the collection, use, and processing of these data will comply with relevant laws, regulations, and standards. In addition, when the embodiment of the present application needs to obtain the user's sensitive personal information, the user's separate permission or consent will be obtained through a pop-up window or by jumping to a confirmation page. After clearly obtaining the user's separate permission or consent, the necessary user-related data for the normal operation of the embodiment of the present application will be obtained.

[0058] Figure 1 is an optional flowchart of the database hole repair method provided in the embodiment of the present application. Figure 1 The method may include but is not limited to steps S110 to S140.

[0059] Step S110, regularly scanning the database to obtain a data table with holes in the database, thereby obtaining a hole data table;

[0060] Step S120, obtaining a historical period for executing a data update operation on the empty data table, and determining a policy execution period according to the historical period;

[0061] Step S130, during the strategy execution period, obtaining a target repair strategy for the hole data table;

[0062] Step S140, executing the target repair strategy to repair the empty data table.

[0063] Steps S110 to S140 shown in the embodiment of the present application can automatically identify tables with holes by periodically scanning the database, and match appropriate repair periods and repair strategies for tables with holes, thereby reducing the impact of hole repair on business processing and improving the availability and stability of the database.

[0064] In step S110 of some embodiments, when a large amount of data in the database is frequently deleted or updated, or the long-term accumulated expired data is not cleaned up, discontinuous free areas will appear in the database storage space, forming database holes. From the perspective of data integrity, database holes will lead to data discontinuity and loss; from the perspective of database performance optimization, database holes will waste storage space and increase the time cost of data query and storage; from the perspective of data management and maintenance, identifying database holes will help discover potential data errors or data anomalies, so as to timely correct and optimize the database structure and ensure the stability and reliability of the database; from the perspective of business operations, high-quality, hole-free data can provide enterprises with more accurate business decisions and ensure the smooth operation of business processes. Identifying and repairing database holes is of great significance for ensuring data quality, optimizing database performance, maintaining system stability, and promoting business development.

[0065] In financial transaction scenarios, the database will store different types of data tables such as transaction order tables, user account tables, and transaction history tables to support different business needs. Among them, the transaction order table is used to store order information, including order ID, user ID, item price, item quantity, and order status. The user account table is used to store user identity information, user account information, and account balance information. The transaction history table is used to record the details of completed transactions, including the transaction parties, transaction amount, transaction items, transaction time, etc. The following takes the transaction order table as an example to describe the specific process of database holes. When an order is canceled by the user or is automatically canceled due to timeout and non-payment, the order record of the order needs to be deleted from the transaction order table. The order record is marked as deleted in the database, but the actual order record still exists in the database storage space, resulting in holes in the transaction order table. As more order records are deleted, there will be more holes in the transaction order table, resulting in data fragmentation. When querying pending orders or new orders, the database needs to read more data pages, which reduces the efficiency of order queries and affects the speed of real-time transactions such as stock trading.

[0066] Database voids reduce the query and storage performance of the database, resulting in transaction delays in financial transactions. In order to avoid the impact of database voids on financial transactions, it is necessary to automatically identify and repair database voids to reorganize the database storage space. The database is scanned at preset intervals to obtain data tables with voids in the database, so as to automatically identify database voids and obtain void data tables. The preset duration can be set according to actual conditions, such as one day, three days, etc. In order to ensure the reliability and security of financial transactions, the database can use a large distributed database PostgreSQL.

[0067] The embodiment of the present application uses the pgstattuple extension module to identify database holes. Run the CREATE EXTENSION pgstattuple statement to install the pgstattuple extension module in the PostgreSQL database. By default, only super users can access the pgstattuple function. pg_stat_scan_tables is an extended view, which is a system table that comes with the database and provides statistical information about table scans. Non-super users can be granted access to pg_stat_scan_tables through the GRANT pg_stat_scan_tablesTO statement, so that non-super users can obtain query permissions for the pgstattuple function. After completing the installation and authorization, use the pgstattuple function to obtain tuple statistics for the data table. A tuple is a row of data in a data table. Tuples are divided into live tuples and dead tuples. Live tuples are row data that is currently valid and not deleted in the data table. Dead tuples are row data that has been deleted or updated but still occupies storage space. The pgstattuple function can return statistical information such as the physical length of the data table, the number of live tuples, the number of dead tuples, and free space.

[0068] Pass the table name of the data table to the pgstattuple function, call the pgstattuple function regularly to obtain the statistical information of the data table, and analyze the statistical information. According to the statistical information, determine whether there are holes in the data table. Holes can be found in time, and corresponding measures can be taken to clean up and optimize the database to improve the performance and space utilization of the database. If there are holes in the data table, the data table is used as a hole data table. The data table can be any table stored in the database. If the table name of the data table is expressed as table3, execute select*from pgstattuple('table3') to get the statistical information of the data table. The statistical information includes the total length of the table table_len, the number of tuples tuple_count, the tuple length tuple_len, the tuple percentage tuple_percent, the number of dead tuples dead_tuple_count, the dead tuple length dead_tuple_len, the dead tuple percentage dead_tuple_percent, the hole length free_space, and the free space percentage free_percent.

[0069] Among them, table_len indicates the size of the space occupied by the table in bytes; tuple_count is the total number of rows (tuples) in the table; tuple_len indicates the size of the space occupied by all rows in bytes; tuple_percent indicates the percentage of the space occupied by all rows to the space occupied by the table; dead_tuple_count indicates the total number of rows in the table that have been deleted or updated but not cleaned up; dead_tuple_len indicates the size of the space occupied by dead tuples; dead_tuple_percent indicates the percentage of the space occupied by dead tuples to the space occupied by the table; free_space indicates the size of the free space in the table; free_percent indicates the percentage of the free space to the space occupied by the table.

[0070] If the tuple_percent of the data table is less than or equal to the preset tuple ratio threshold, or the dead_tuple_count is greater than or equal to the preset dead tuple number threshold, then the data table has a hole. On the contrary, if the tuple_percent of the data table is greater than the preset tuple ratio threshold, or the dead_tuple_count is less than the preset dead tuple number threshold, then the data table does not have a hole. The preset tuple ratio threshold and the preset dead tuple number threshold can be set according to actual conditions. The preset tuple ratio threshold can be 70, and the preset dead tuple number threshold can be 10 million. If the tuple_percent of the data table is less than the preset tuple ratio threshold, it means that there is a lot of free space in the table. If the dead_tuple_count is greater than the preset dead tuple number threshold, it means that there are a large number of tuples that have been deleted but not cleaned up in the table.

[0071] The pgstattuple extension module also includes the pgstatindex function, which can return the statistical information of the index, such as the size, number of levels, number of leaf pages, etc. Get the name of the index in the data table, pass the name of the index to the pgstatindex function, and execute select*from pgstatindex('index_name') regularly to obtain the statistical information of the index. The statistical information of the index includes the number of dead tuples dead_tuples, the number of free pages empty_pages, and the percentage of free space free_percent. If the number of dead tuples dead_tuples is greater than the preset dead tuple number threshold, the index is highly fragmented, the data in the table is unevenly distributed, and the data table has holes. Alternatively, if the number of free pages or the percentage of free space is high, that is, greater than the preset threshold, it means that there is a lot of storage space that does not store valid data, and the data table has holes.

[0072] In the related art, when the database generates a capacity alarm, it is determined that there is a database hole in the data table, resulting in a certain lag in hole identification and failure to identify database holes in a timely manner. The embodiment of the present application automatically scans the database to intervene in the database hole identification and repair process before receiving an alarm. Compared with the manual hole checking method, it greatly improves the efficiency of hole identification and improves the availability and stability of the database.

[0073] See also Figure 2 In some embodiments, step S120 may include but is not limited to steps S210 to S230:

[0074] Step S210, obtaining the data transmission log of the database;

[0075] Step S220, obtaining a historical period of executing a data update operation on the empty data table according to the data transmission log;

[0076] Step S230, filtering the historical time periods to obtain the policy execution time period.

[0077] In step S210 of some embodiments, the data transfer service (DTS) can realize data migration, data synchronization, data backup and recovery between different PostgreSQL databases, and record detailed information of data transmission activities in the PostgreSQL database to obtain a data transmission log. The data transmission log includes the time when the data transmission is sent, the starting point and end point of the data transmission, the transmission rate, etc. The data transmission service has functions such as data migration, data synchronization, data backup and recovery, and data integration. Through the data migration function, data can be migrated from the current PostgreSQL database to another PostgreSQL database, and full data migration and incremental data synchronization can be supported. Through the data synchronization function, data consistency between multiple PostgreSQL databases can be maintained to achieve real-time data synchronization. Through the data backup and recovery function, data can be backed up to other locations and restored when needed. Through the data integration function, the PostgreSQL database can be integrated with other data sources to achieve data exchange and sharing. Through the data transmission service, it is convenient to manage and maintain the PostgreSQL database, and improve the availability, reliability and security of data.

[0078] In step S220 of some embodiments, the data transmission log is scanned to obtain a historical period in which a data update operation is performed on the empty data table. The data update operation includes at least one of a data insertion operation insert, a data deletion operation delete, and a data modification operation update. The historical period is a period in which a data update operation is performed on the empty data table before the current time. For example, the historical period may be a period in which a data update operation is performed on the empty data table in the last three days.

[0079] In step S230 of some embodiments, in order to avoid the impact of database hole repair on business processing such as financial transactions, the period with less data update operations is screened from the historical period to obtain the table cold period. The pre-set business low peak period is obtained, such as 0:00 to 6:00 in the morning. The table cold period and the business low peak period are used as the strategy execution period. The strategy execution period is the execution period of the hole repair strategy.

[0080] Through the above steps S210 to S230, the policy execution period can be obtained, so as to perform database hole repair in a suitable period to avoid the repair process blocking the business processing process.

[0081] In the related art, the VACUUM command or the VACUUM FULL command is used to repair database holes. The VACUUM command is a basic command used to reclaim space in the PostgreSQL database. It can clean up the space occupied by deleted or updated rows and update the statistics of the table. Compared with the VACUUM command, the VACUUM FULL command will perform a more thorough space recovery operation on the data table, but it will lock the table during space recovery, resulting in a long period of blocking, and the blocking time window is uncontrollable, affecting normal business processing. Therefore, it is necessary to obtain a repair strategy suitable for empty data tables.

[0082] See also Figure 3 In some embodiments, step S130 may include but is not limited to steps S310 to S320:

[0083] Step S310, during the policy execution period, obtaining the number of data records in the empty data table and the storage space capacity of the database occupied by the empty data table;

[0084] Step S320: determining a target repair strategy for the hole data table according to the number of data records or the storage space capacity.

[0085] In step S310 of some embodiments, the hole data table contains multiple data records. During the policy execution period, the number of data records in the hole data table is obtained to obtain the number of data records, and the storage space size of the database occupied by the hole data table is obtained to obtain the storage space capacity.

[0086] In step S320 of some embodiments, if the number of data records is less than or equal to the preset number threshold, or the storage space capacity is less than or equal to the preset storage space capacity, it means that the hole data table is a small table, and the target repair strategy is determined to be the first repair strategy. The first repair strategy is a hole repair strategy for small tables. The preset number threshold and the preset storage space capacity can be set according to actual conditions. The preset number threshold can be 30 million, and the preset storage space capacity can be 30G. If the number of data records is greater than the preset number threshold, or the storage space capacity is greater than the preset storage space capacity, it means that the hole data table is a large table, and the target repair strategy is determined to be the second repair strategy. The second repair strategy is a hole repair strategy for large tables.

[0087] Hole data tables of different sizes can be distinguished by the number of data records or the storage space capacity, so that different hole repair strategies can be adopted for hole data tables of different sizes, making the hole repair strategy more refined.

[0088] Through the above steps S310 to S320, a suitable hole repair strategy can be matched for the hole data table, making the hole repair strategy more refined.

[0089] Execute the target repair strategy to repair the database hole to improve the storage efficiency of the database, speed up data access and data processing, and thus improve the system performance of the database. The target repair strategy includes the first repair strategy or the second repair strategy.

[0090] See also Figure 4 In some embodiments, step S140 may include but is not limited to steps S410 to S420:

[0091] Step S410, executing a first repair strategy, and generating a space recovery instruction according to the first repair strategy;

[0092] Step S420: reclaim space on the empty data table according to the space reclaim instruction.

[0093] In step S410 of some embodiments, during the strategy execution period, if the target repair strategy is the first repair strategy, a space reclamation instruction is generated according to the operator of the space reclamation operation and the table name of the hole data table. The operator of the space reclamation operation is VACUUM FULL. If the table name of the hole data table is expressed as tablename, the space reclamation instruction is VACUUMFULL tablename.

[0094] In step S420 of some embodiments, a space recovery instruction is executed to recover the storage space that is not effectively utilized in the hole data table. It should be noted that when the space recovery instruction is executed, the hole data table will be locked, and the hole data table cannot be read or written. In order to avoid affecting the online business, it is necessary to execute the space recovery instruction during the policy execution period. Compared with the hole data table (large table) with a large amount of data, the VACUUM FULL instruction is used to recover the space of the hole data table (small table) with a small amount of data, which can reduce the time cost of hole repair. The blocking time of the VACUUMFULL instruction for small tables is shorter, and it is executed during the off-peak period of business or the cold period of the table, so its impact on business processing can be ignored.

[0095] Through the above steps S410 to S420, space is reclaimed from the small table during the policy execution period, which can avoid the business peak period, avoid long-term congestion of the business processing flow, and ensure high availability of the database.

[0096] See also Figure 5 In some embodiments, step S140 may include but is not limited to steps S510 to S540:

[0097] Step S510, executing the second repair strategy, and obtaining the table structure and data records of the hole data table according to the second repair strategy;

[0098] Step S520, creating a reference data table according to the table structure;

[0099] Step S530, migrating the data record to the reference data table;

[0100] Step S540: covering the empty data table according to the reference data table.

[0101] In step S510 of some embodiments, during the policy execution period, if the target repair strategy is the second repair strategy, the table structure and data records of the void data table are obtained. The table structure is used to describe the organization and definition of the table in the database, including the table name, field name, index, authority, and inheritance relationship between tables. Taking the transaction order table as an example, the table name of the transaction order table is transaction order, and the field name is used to identify the name of each column in the transaction order table, such as order ID, user ID, product name, order creation time, etc. The index is a data structure created on a key field such as an order ID to increase the query speed. The authority is used to control the user's access to the transaction order table, including read authority, insert authority, delete authority, and change authority. The transaction order table can be associated with the user table, and all historical orders of the user can be queried through the user ID field. The data record is the data stored in the void data table, and a row of the void data table is a data record.

[0102] In step S520 of some embodiments, the table structure of the empty data table is copied to create a new data table to obtain a reference data table.

[0103] In step S530 of some embodiments, for an empty data table with a large amount of data, the use of space recovery instructions will cause the business processing flow involving the empty data table to be blocked for a long time, seriously affecting the progress of business processing, especially for businesses with high timeliness requirements such as real-time financial transactions. The embodiment of the present application migrates the data records of the empty data table to the reference data table to eliminate database holes without affecting the business processing flow.

[0104] In step S540 of some embodiments, the reference data table after data migration is used as the repaired hole data table, and the original hole data table is deleted. If the hole data table is represented by T1 and the reference data table is represented by T2, after the data records of the hole data table T1 are migrated to the reference data table T2, the table names of the hole data table and the reference data table can be exchanged, that is, T1 becomes T2, T2 becomes T1, and the reference data table named T1 is used as the result of hole repair, and the hole data table named T2 is deleted.

[0105] In the above steps S510 to S540, during the policy execution period, an online data migration strategy is used to eliminate holes in empty data tables with large data volumes, so that the empty data tables can still be operated during the hole repair process, minimizing the impact of hole repair on business processing, thereby allowing the database to respond to business requests.

[0106] See also Figure 6 In some embodiments, step S530 may include but is not limited to steps S610 to S620:

[0107] Step S610, obtaining the unit migration data volume of the data record;

[0108] Step S620 , migrating data records to the reference data table one by one according to the unit migration data volume.

[0109] In step S610 of some embodiments, inserting a large amount of stored data into a data table at one time will take up a large amount of rollback space. When the data insertion fails and needs to be rolled back, the insufficient rollback space will cause the insertion operation to be unable to roll back, resulting in inconsistent data in the two data tables or blocking of the hole repair process. In order to ensure the data consistency of business data such as financial transaction data, improve the security and reliability of financial transactions, and complete the hole repair of the hole data table, the embodiment of the present application adopts a step-by-step insertion method to insert data records of the hole data table into the reference data table. Specifically, the number of data records in the hole data table and the preset number of migrations are obtained, and the ratio of the number of data records to the number of migrations is calculated. If the ratio is an integer, the ratio is used as the unit migration data amount. If the ratio is a decimal, the comparison value is rounded up or rounded down to obtain the unit migration data amount. The unit migration data amount is used to represent the number of data records that need to be migrated at a single time.

[0110] In step S620 of some embodiments, data records are migrated to the reference data table one by one according to the unit migration data volume until the migration of all data records in the hole data table is completed. For example, if the number of data records is 10,000 and the number of migrations is 10, then the unit migration data volume is 1000. The data records in the hole data table can be divided into 10 times and inserted into the reference data table in a manner of 1000 records each time.

[0111] Through the above steps S610 to S620, data migration of a hollow data table with a large amount of data can be completed. While realizing database hole repair, data inconsistency and data migration process blockage caused by insufficient rollback space can be avoided.

[0112] See also Figure 7 In some embodiments, step S540 may include but is not limited to steps S710 to S740:

[0113] Step S710, obtaining the data transmission log of the database;

[0114] Step S720, obtaining the data update operation performed on the empty data table during the migration period according to the data transmission log, and obtaining the target operation; the migration period is the period for migrating data records to the reference data table;

[0115] Step S730, performing a target operation on the reference data table to obtain a candidate data table;

[0116] Step S740: covering the empty data table according to the candidate data table.

[0117] In step S710 of some embodiments, detailed information of data transmission activities in a database recorded by a data transmission service is obtained to obtain a data transmission log.

[0118] In step S720 of some embodiments, the migration period is the period for migrating data records to the reference data table, and the migration period is the time range from starting the data migration operation to completing the data migration operation. The data update operation includes at least one of a data insertion operation, a data deletion operation, and a data modification operation. The data transmission log is scanned to obtain the data update operation performed on the empty data table during the migration period, and the data update operation within the migration period is used as the target operation.

[0119] In step S730 of some embodiments, the hole data table may change due to the data update operation during the migration period. In order to ensure the consistency between the hole data table and the reference data table, the target operation is performed on the reference data table according to the operation sequence of performing the data update operation on the hole data table during the migration period, and the reference data table after performing the target operation is used as the candidate data table.

[0120] In step S740 of some embodiments, referring to step S540 , the candidate data table is used as the repaired hole data table, and the original hole data table is deleted.

[0121] Through the above steps S710 to S740, database holes can be repaired and the availability of the database is improved.

[0122] The database hole repair method provided in the embodiment of the present application includes: regularly scanning the statistical information of the data table output by the pgstattuple extension module of the database, and treating the data table with a tuple percentage tuple_percent less than or equal to 70 or a dead tuple number greater than or equal to 10 million as a hole table. Scan the data transmission log of the database, obtain the historical period of data update operations on the hole table in the last three days, filter out the table cold period from the historical period, and determine the policy execution period according to the business off-peak period and the table cold period. During the policy execution period, obtain the number of data records in the hole table and the storage space capacity of the database occupied by the hole table. If the number of data records is greater than 30 million or the storage space capacity is greater than 30G, create a reference data table, and migrate the data records of the hole table to the reference data table by step-by-step insertion. If the number of data records is less than or equal to 30 million or the storage space capacity is less than or equal to 30G, use the VACUUM FULL instruction to reclaim space for the hole table.

[0123] See also Figure 8 The embodiment of the present application also provides a database hole repair device, which can implement the above database hole repair method, and the device includes:

[0124] The scanning module 810 is used to periodically scan the database to obtain a data table with holes in the database, thereby obtaining a data table with holes;

[0125] The time period acquisition module 820 is used to acquire the historical time period for executing the data update operation on the empty data table, and determine the policy execution time period according to the historical time period;

[0126] A strategy acquisition module 830 is used to acquire a target repair strategy for the hole data table during the strategy execution period;

[0127] The execution module 840 is used to execute the target repair strategy to repair the empty data table.

[0128] The specific implementation of the database hole repair device is basically the same as the specific implementation of the above-mentioned database hole repair method, and will not be repeated here.

[0129] In some embodiments, the database hole repairing device is further used to:

[0130] Executing a first repair strategy and generating a space recovery instruction according to the first repair strategy;

[0131] Perform space reclamation on empty data tables according to space reclamation instructions.

[0132] In some embodiments, the database hole repairing device is further used to:

[0133] Execute the second repair strategy, and obtain the table structure and data records of the empty data table according to the second repair strategy;

[0134] Create a reference data table based on the table structure;

[0135] Migrate data records to reference data tables;

[0136] Overwrite the void data table according to the reference data table.

[0137] In some embodiments, the database hole repairing device is further used to:

[0138] Get the unit migration data volume of the data record;

[0139] Migrate data records to the reference data table one by one according to the unit migration data volume.

[0140] In some embodiments, the database hole repairing device is further used to:

[0141] Get the data transfer log of the database;

[0142] According to the data transmission log, the data update operation performed on the empty data table during the migration period is obtained to obtain the target operation; the migration period is the period for migrating data records to the reference data table;

[0143] Perform the target operation on the reference data table to obtain the candidate data table;

[0144] Cover the empty data table according to the candidate data table.

[0145] In some embodiments, the database hole repairing device is further used to:

[0146] Get the data transfer log of the database;

[0147] Obtain the historical period during which data update operations are performed on the empty data table according to the data transmission log;

[0148] Filter the historical time periods to obtain the strategy execution period.

[0149] In some embodiments, the database hole repairing device is further used to:

[0150] During the policy execution period, the number of data records in the empty data table and the storage space capacity of the database occupied by the empty data table are obtained;

[0151] Determine the target repair strategy for the empty data table based on the number of data records or storage space capacity.

[0152] The embodiment of the present application also provides an electronic device, the electronic device includes a memory and a processor, the memory stores a computer program, and the processor implements the above database hole repair method when executing the computer program. The electronic device can be any smart terminal including a tablet computer, a car computer, etc.

[0153] See also Fig. 9 , Fig. 9 The hardware structure of an electronic device of another embodiment is illustrated, and the electronic device includes:

[0154] The processor 910 may be implemented by a general-purpose CPU (Central Processing Unit), a microprocessor, an application-specific integrated circuit (Application Specific Integrated Circuit, ASIC), or one or more integrated circuits, and is used to execute relevant programs to implement the technical solutions provided in the embodiments of the present application;

[0155] The memory 920 can be implemented in the form of a read-only memory (ROM), a static storage device, a dynamic storage device, or a random access memory (RAM). The memory 920 can store an operating system and other applications. When the technical solution provided in the embodiment of this specification is implemented by software or firmware, the relevant program code is stored in the memory 920, and the processor 910 calls and executes the database hole repair method of the embodiment of this application;

[0156] Input / output interface 930, used to implement information input and output;

[0157] Communication interface 940, used to realize communication interaction between the device and other devices, which can be realized through wired mode (such as USB, network cable, etc.) or wireless mode (such as mobile network, WIFI, Bluetooth, etc.);

[0158] bus 950 , which transmits information between the various components of the device (e.g., processor 910 , memory 920 , input / output interface 930 , and communication interface 940 );

[0159] The processor 910 , the memory 920 , the input / output interface 930 , and the communication interface 940 are connected to each other in communication within the device via a bus 950 .

[0160] An embodiment of the present application also provides a computer-readable storage medium, which stores a computer program. When the computer program is executed by a processor, the above-mentioned database hole repair method is implemented.

[0161] The memory, as a non-transient computer-readable storage medium, can be used to store non-transient software programs and non-transient computer executable programs. In addition, the memory may include a high-speed random access memory, and may also include a non-transient memory, such as at least one disk storage device, a flash memory device, or other non-transient solid-state storage device. In some embodiments, the memory may optionally include a memory remotely disposed relative to the processor, and these remote memories may be connected to the processor via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0162] The database hole repair method and device, electronic device and storage medium provided in the embodiments of the present application can automatically identify tables with holes by periodically scanning the database, and match appropriate repair time periods and repair strategies for tables with holes, thereby reducing the impact of hole repair on business processing and improving the availability and stability of the database.

[0163] The embodiments described in the embodiments of the present application are intended to more clearly illustrate the technical solutions of the embodiments of the present application and do not constitute a limitation on the technical solutions provided in the embodiments of the present application. Those skilled in the art will appreciate that with the evolution of technology and the emergence of new application scenarios, the technical solutions provided in the embodiments of the present application are also applicable to similar technical problems.

[0164] Those skilled in the art will appreciate that the technical solutions shown in the figures do not constitute a limitation on the embodiments of the present application, and may include more or fewer steps than shown in the figures, or a combination of certain steps, or different steps.

[0165] The device embodiments described above are merely illustrative, and the units described as separate components may or may not be physically separated, that is, they may be located in one place or distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0166] Those skilled in the art will appreciate that all or some of the steps in the methods disclosed above, and the functional modules / units in the systems and devices may be implemented as software, firmware, hardware, or a suitable combination thereof.

[0167] The terms "first", "second", "third", "fourth", etc. (if any) in the specification of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device comprising a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0168] It should be understood that in the present application, "at least one (item)" means one or more, and "plurality" means two or more. "And / or" is used to describe the association relationship of associated objects, indicating that three relationships may exist. For example, "A and / or B" can mean: only A exists, only B exists, and A and B exist at the same time, where A and B can be singular or plural. The character " / " generally indicates that the objects associated before and after are in an "or" relationship. "At least one of the following" or similar expressions refers to any combination of these items, including any combination of single or plural items. For example, at least one of a, b or c can mean: a, b, c, "a and b", "a and c", "b and c", or "a and b and c", where a, b, c can be single or multiple.

[0169] In the several embodiments provided in the present application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic. For example, the division of the above units is only a logical function division. There may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.

[0170] The units described above as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0171] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0172] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including multiple instructions to enable a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods of various embodiments of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (Read-Only Memory, referred to as ROM), random access memory (Random Access Memory, referred to as RAM), disk or optical disk and other media that can store programs.

[0173] The preferred embodiments of the present invention are described above with reference to the accompanying drawings, but the scope of the rights of the present invention is not limited thereto. Any modification, equivalent substitution and improvement made by a person skilled in the art without departing from the scope and essence of the present invention should be within the scope of the rights of the present invention.

Claims

1. A database hole repair method, characterized in that: The method comprises: Scanning the database regularly to obtain a data table where a hole is generated in the database, thereby obtaining a hole data table; Acquire a historical period during which data update operations are performed on the empty data table, and determine a policy execution period according to the historical period; During the strategy execution period, obtaining a target repair strategy for the hole data table; The target repair strategy is executed to repair the empty data table.

2. The method according to claim 1, characterized in that The target repair strategy includes a first repair strategy, and executing the target repair strategy to repair the hole data table includes: executing the first repair strategy and generating a space recovery instruction according to the first repair strategy; The space of the hole data table is reclaimed according to the space reclaim instruction.

3. The method according to claim 1, characterized in that The target repair strategy includes a second repair strategy, and executing the target repair strategy to repair the hole data table includes: Execute the second repair strategy, and obtain the table structure and data records of the hole data table according to the second repair strategy; Create a reference data table according to the table structure; Migrating the data record to the reference data table; The hole data table is overwritten according to the reference data table.

4. The method according to claim 3, characterized in that The step of migrating the data record to the reference data table includes: Obtaining a unit migration data volume of the data record; The data records are migrated to the reference data table one by one according to the unit migration data amount.

5. The method according to claim 3, characterized in that: The covering the empty data table according to the reference data table includes: Obtaining a data transmission log of the database; According to the data transmission log, a data update operation performed on the empty data table during a migration period is acquired to obtain a target operation; the migration period is a period for migrating the data record to the reference data table; Performing the target operation on the reference data table to obtain a candidate data table; The empty data table is covered according to the candidate data table.

6. The method according to any one of claims 1 to 5, characterized in that: The obtaining of a historical period of executing a data update operation on the empty data table, and determining a policy execution period according to the historical period, includes: Obtaining a data transmission log of the database; Acquire the historical period of performing data update operation on the empty data table according to the data transmission log; The historical time periods are screened to obtain the policy execution time periods.

7. The method according to any one of claims 1 to 5, characterized in that: The step of obtaining a target repair strategy for the hole data table during the strategy execution period includes: During the policy execution period, obtaining the number of data records in the empty data table and the storage space capacity of the database occupied by the empty data table; A target repair strategy for the hole data table is determined according to the number of data records or the storage space capacity.

8. A database hole repair device, characterized in that: The device comprises: A scanning module, used for scanning the database at regular intervals, obtaining a data table where a hole is generated in the database, and obtaining a hole data table; A time period acquisition module, used to acquire a historical time period for executing a data update operation on the empty data table, and determine a strategy execution time period according to the historical time period; A strategy acquisition module, used for acquiring a target repair strategy for the hole data table during the strategy execution period; An execution module is used to execute the target repair strategy to repair the empty data table.

9. An electronic device, characterized in that: The electronic device comprises a memory and a processor, the memory stores a computer program, and the processor implements the method according to any one of claims 1 to 7 when executing the computer program.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.

Images