Row migration elimination method, device, computer equipment and storage medium

Abstract

The present application relates to the field of cloud data technology and provides a method, apparatus, computer device, storage medium, and computer program product for eliminating row migration. The method comprises: detecting a record row with row migration, determining a row identifier corresponding to the record row, and based on the row identifier, searching for the current data block where the record row is located after the row migration and the original data block where the record row is located before the row migration, determining the remaining free space in the original data block and the storage space required for the record row, and when the remaining free space is not less than the storage space, migrating the record row in the current data block to the original data block and deleting the pointer in the original data block pointing to the current data block, thereby reducing additional I / O operations of the database and improving the data processing performance of the database as a whole.

Description

Technical Field

[0001] The present application relates to the field of cloud data technology, and in particular to a method, apparatus, computer device, storage medium, and computer program product for eliminating row migration. Background Art

[0002] In Oracle (a relational database management system), when modifying a non-linked row, if the modified row length is greater than the original length and the data block cannot accommodate the modified row, the entire row's data is migrated to another data block, and a pointer to the row's new location is retained in the original data block. This is called a row migration. Queries involving migrated data can degrade database performance and generate additional I / O operations.

[0003] The traditional solution is to store the migrated data in a temporary table, delete the migrated data from the original table, retrieve the data from the temporary table, reinsert it into the original table, and then delete the temporary table. However, this approach doesn't consider foreign key constraints, making it impossible to delete rows with migrated data that have foreign key constraints, impacting database performance. Summary of the Invention

[0004] Based on this, it is necessary to provide a row migration elimination method, apparatus, computer equipment, computer-readable storage medium and computer program product that can improve database performance in order to address the above technical issues.

[0005] In a first aspect, the present application provides a method for eliminating row migration. The method comprises:

[0006] Detecting a record row with row migration, and determining a row identifier corresponding to the record row;

[0007] Based on the row identifier, searching for the current data block where the record row is located after row migration and the original data block where the record row is located before row migration;

[0008] Determining the remaining free space of the original data block and the storage space required to be occupied by the record row;

[0009] When the remaining free space is not less than the storage space, the record rows in the current data block are migrated to the original data block, and the pointer in the original data block pointing to the current data block is deleted.

[0010] In one embodiment, determining the remaining free space of the original data block includes:

[0011] Obtaining a data block identifier corresponding to the original data block;

[0012] Using the data block identifier as an index condition, locating the record information corresponding to the original data block in the data table;

[0013] The remaining free space of the original data block is obtained from the record information.

[0014] In one embodiment, obtaining the remaining free space of the original data block from the record information includes:

[0015] Obtaining the total storage space of the original data block and the size of the occupied storage space corresponding to each record row in the original data block from the record information;

[0016] Accumulating the size of the occupied storage space corresponding to the record row to obtain the cumulative occupied storage space corresponding to the original data block;

[0017] Based on the total storage space corresponding to the original data block and the accumulated occupied storage space, the remaining free space of the original data block is obtained.

[0018] In one embodiment, detecting a record row with row migration and determining a row identifier corresponding to the record row includes:

[0019] Based on the data storage method corresponding to the row migration behavior, the target information table representing the row migration behavior is filtered out from the information table;

[0020] Based on the target information table, record rows with row migration and row identifiers corresponding to the record rows are determined.

[0021] In one embodiment, determining the record row with row migration and the row identifier corresponding to the record row based on the target information table includes:

[0022] Based on the target information table, recording the row identifier corresponding to the record row generating the row migration behavior into the data dictionary table;

[0023] The data dictionary table is traversed to determine the record rows where row migration exists and the row identifiers corresponding to the record rows.

[0024] In one embodiment, the method further comprises:

[0025] Trigger the row migration and elimination task for the record rows according to the preset cycle;

[0026] When the remaining free space is smaller than the storage space of the current record row and there is no record row whose storage space is smaller than or equal to the remaining free space, the row migration elimination task of the current cycle is terminated.

[0027] In a second aspect, the present application further provides a row migration elimination device. The device comprises:

[0028] A detection module, configured to detect a record row with row migration and determine a row identifier corresponding to the record row;

[0029] a database search module configured to search, based on the row identifier, for the current data block where the record row is located after row migration and the original data block where the record row is located before row migration;

[0030] A space determination module, configured to determine the remaining free space of the original data block and the storage space required to be occupied by the record row;

[0031] The row migration elimination module is configured to migrate the record rows in the current data block to the original data block and delete the pointer in the original data block pointing to the current data block when the remaining free space is not less than the storage space.

[0032] In a third aspect, the present application further provides a computer device. The computer device includes a memory and a processor, wherein the memory stores a computer program, and when the processor executes the computer program, the following steps are performed:

[0033] Detecting a record row with row migration, and determining a row identifier corresponding to the record row;

[0034] Based on the row identifier, searching for the current data block where the record row is located after row migration and the original data block where the record row is located before row migration;

[0035] Determining the remaining free space of the original data block and the storage space required to be occupied by the record row;

[0036] When the remaining free space is not less than the storage space, the record rows in the current data block are migrated to the original data block, and the pointer in the original data block pointing to the current data block is deleted.

[0037] In a fourth aspect, the present application further provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the following steps:

[0038] Detecting a record row with row migration, and determining a row identifier corresponding to the record row;

[0039] Based on the row identifier, searching for the current data block where the record row is located after row migration and the original data block where the record row is located before row migration;

[0040] Determining the remaining free space of the original data block and the storage space required to be occupied by the record row;

[0041] When the remaining free space is not less than the storage space, the record rows in the current data block are migrated to the original data block, and the pointer in the original data block pointing to the current data block is deleted.

[0042] In a fifth aspect, the present application further provides a computer program product. The computer program product includes a computer program that, when executed by a processor, implements the following steps:

[0043] Detecting a record row with row migration, and determining a row identifier corresponding to the record row;

[0044] Based on the row identifier, searching for the current data block where the record row is located after row migration and the original data block where the record row is located before row migration;

[0045] Determining the remaining free space of the original data block and the storage space required to be occupied by the record row;

[0046] When the remaining free space is not less than the storage space, the record rows in the current data block are migrated to the original data block, and the pointer in the original data block pointing to the current data block is deleted.

[0047] The above-mentioned row migration elimination method, apparatus, computer equipment, storage medium and computer program product detect the record row with row migration and determine the row identifier corresponding to the record row, so as to facilitate the positioning of the record row. Based on the row identifier, the current data block where the record row is located after the row migration and the original data block where the record row is located before the row migration are found, and the positions of the record row before and after the migration are determined. The remaining free space of the original data block and the storage space required for the record row are determined so as to compare the space sizes. When the remaining free space is not less than the storage space, the record row in the current data block is migrated to the original data block, and the pointer in the original data block pointing to the current data block is deleted, thereby eliminating row migration, reducing additional I / O operations, and improving the data processing performance of the database as a whole. BRIEF DESCRIPTION OF THE DRAWINGS

[0048] Figure 1 A diagram illustrating an application environment of a method for eliminating bank migration according to an embodiment;

[0049] Figure 2 A schematic diagram of a flow chart of a method for eliminating migration of a bank line according to an embodiment;

[0050] Figure 3 FIG. 1 is a flow chart of another embodiment of a method for eliminating migration of a bank line;

[0051] Figure 4 1 is a flow chart of another embodiment of a method for eliminating migration;

[0052] Figure 5 is a structural block diagram of a row migration elimination device according to an embodiment;

[0053] Figure 6 FIG. 1 is a diagram showing the internal structure of a computer device in one embodiment. DETAILED DESCRIPTION

[0054] In order to make the purpose, technical solutions and advantages of this application more clear, the following further describes this application in detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain this application and are not intended to limit this application.

[0055] The row migration elimination method provided in the embodiment of the present application can be applied to Figure 1 In the application environment shown. Among them, the terminal 102 communicates with the server 104 through the network. The terminal 102 can be used to configure the triggering conditions of the row migration elimination event. When the server detects that the triggering conditions are met, it triggers the row migration elimination event and performs row migration elimination. The server 104 performs row migration elimination processing specifically including: detecting the record row with row migration, determining the row identifier corresponding to the record row, based on the row identifier, searching the current data block where the record row is located after row migration, and the original data block where the record row is located before row migration, determining the remaining free space of the original data block and the storage space required for the record row, when the remaining free space is not less than the storage space, migrating the record row in the current data block to the original data block, and deleting the pointer in the original data block pointing to the current data block, thereby eliminating the row migration of the record row.

[0056] Among them, the terminal 102 can be, but is not limited to, various personal computers, laptops, smart phones, tablet computers and portable wearable devices. Portable wearable devices can be smart watches, smart bracelets, head-mounted devices, etc. The server 104 can be implemented as an independent server or a server cluster consisting of multiple servers. The server can be an independent server or 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, content delivery networks (CDNs), and big data and artificial intelligence platforms.

[0057] In one embodiment, Figure 2 As shown, a row migration elimination method is provided, which is applied to Figure 1The following steps are used as an example to illustrate the server in the example:

[0058] Step 202: Detect the record row with row migration and determine the row identifier corresponding to the record row.

[0059] Row migration refers to the relocation of a data row to another block when it no longer fits in the current block, but a pointer to the original block remains, and the indexed row identifier still points to the original location. Specifically, when modifying a non-row-linked row in Oracle Database, if the modified row length is greater than the original length, and the free space in the data block is too small to accommodate the modified row, Oracle Database will migrate the entire row to another data block and retain a pointer to the row's new location in the original data block. The Oracle Database can be a cloud database stored in the cloud.

[0060] A row is a unit of data recorded in Oracle databases. Unlike row chaining, the data corresponding to a row is recorded in the same data block. Row chaining, on the other hand, involves using multiple data blocks to store data when a row is too large to fit in a single data block. For example, if Oracle uses a 4KB data block size and a row of 8KB is inserted, Oracle Database will use three data blocks to store the data.

[0061] A row identifier is a unique identifier for each row of data in an Oracle database table. It is also called a rowid and is commonly used within Oracle to access data. Specifically, a rowid requires 10 bytes of storage space and is represented by a string that indicates the physical location of the row in the Oracle database. This string consists of a data object number, a corresponding file number, a block number, and a row number. The data object number indicates the number of the database object to which the row belongs. Each data object is assigned a unique number when the database is created, and this number is unique. The corresponding file number indicates the number of the file containing the row. Each file number in a tablespace is unique. The block number indicates the block location of the file containing the row, and the row number indicates the row's specific location in the row directory. The row identifier represents the internal address of the row data in the physical table. It consists of two addresses: the address of the data file where the block containing the row is stored in the table, and the address of the row itself within the data block.

[0062] Specifically, when a row is migrated, Oracle Database migrates the entire row to a data block that can store the entire row. The original pointer of the migration points to the new data block storing the row data, and the rowid remains unchanged. When a row is migrated, accessing it will degrade I / O performance because Oracle must access more data blocks to retrieve the data for these rows. In response to the triggering of the row migration elimination event, the server initiates a detection process for rows with migrations. The server detects and locates the migrated rows and stores the rowids of these rows.

[0063] Step 204 : Based on the row identifier, the current data block where the record row is located after the row migration and the original data block where the record row is located before the row migration are searched.

[0064] The data blocks in which a row resides before and after row migration are different. The data block in which the row resides after row migration is called the current data block, while the data block in which the row resided before row migration is called the original data block. The original data block stores a pointer to the row's storage location in the current data block. This allows a data search for that row to determine the row's current data block based on the pointer stored in the original data block, thereby retrieving the data corresponding to that row.

[0065] Specifically, the original data block is the data block where the record row resides before the row migration occurs. After the row migration occurs, a pointer to the data block where the current record row resides is stored in the original data block. Based on the row identifier, the server determines the current data block where the record row resides and the pointer to the current data block corresponding to the record row. The server locates the data block where the corresponding pointer is stored to locate the original data block corresponding to the record row.

[0066] Step 206: Determine the remaining free space of the original data block and the storage space required by the record row.

[0067] The remaining free space refers to the sum of the free space of fragments in the data block that do not store data. It should be noted that the remaining free space does not require continuous storage space and can be composed of multiple fragmented storage spaces. The storage space required for a record row refers to the space required for storing the record row in the data block. The storage space required for a record row is related to the amount of data content corresponding to the record row. The more data content a record row has, the larger the storage space required for the record row, and the less data content a record row has, the smaller the storage space required for the record row.

[0068] Specifically, the server can query the table to obtain the storage information of the record row. The storage information refers to the amount of storage space occupied by the record row in the data block. By obtaining the storage information, the server can determine the storage space required by the record row. The server can use the data block identifier of the original data block to locate the original data block and view the remaining free space in the original data block.

[0069] Step 208: When the remaining free space is not less than the storage space, the record rows in the current data block are migrated to the original data block, and the pointer in the original data block pointing to the current data block is deleted.

[0070] After a row migration occurs, the row is stored in the current data block, and a pointer to the current data block is stored in the original data block. The server compares the storage space required by the row with the remaining free space in the original data block to obtain a comparison result, and performs further data processing operations based on the comparison result. The comparison results include two situations: the remaining free space in the original data block is greater than or equal to the storage space required by the row, and the remaining free space in the original data block is less than the storage space required by the row. Different comparison results correspond to different data processing operations, and the specific relationship between the data processing operation and the comparison result can be pre-set.

[0071] Specifically, if the remaining free space in the original data block is greater than or equal to the storage space required for the row, the row can be migrated from the current data block to the original data block, eliminating the row migration. To eliminate the row migration, two steps are required: first, migrating the row from the current data block to the original data block to free up space in the current data block, thus migrating the data from the current data block to the original data block; second, deleting the pointer in the original data block pointing to the current data block, thus disassociating the two data blocks.

[0072] The above-mentioned row migration elimination method detects rows with row migration and determines the row identifier corresponding to the row, facilitating the location of the row. Based on the row identifier, the current data block where the row is located after the row migration and the original data block where the row was located before the row migration are found. The location of the row before and after the migration is determined, and the remaining free space in the original data block and the storage space required for the row are determined to facilitate space size comparison. When the remaining free space is not less than the storage space, the row in the current data block is migrated to the original data block, and the pointer in the original data block pointing to the current data block is deleted. This not only solves the problem of traditional solutions that cannot delete rows with row migrations under foreign key constraints, but also eliminates the need to spend a lot of time maintaining the balance of the index tree. Since a large amount of table space fragmentation will occur during the long-term operation of the database, the above-mentioned method migrates the row to the original data block that can accommodate the size of the row, deletes the pointer to the original data block, and releases the space in the current data block. On the one hand, it eliminates row migration and reduces additional I / O operations. On the other hand, it fills the free fragment space in the table space, alleviates the phenomenon of table space fragmentation, and releases the space of the current data block, thereby improving the overall data processing performance of the database.

[0073] In one embodiment, determining the remaining free space of the original data block includes: obtaining a data block identifier corresponding to the original data block; locating record information corresponding to the original data block in a data table using the data block identifier as an index condition; and obtaining the remaining free space of the original data block from the record information.

[0074] The data block ID is a unique identifier for each data block in an Oracle database. Different data blocks have different data block IDs. A data table is a table in an Oracle database that records information about each data block. Recorded information refers to the attributes of the data block, including the total storage space of the data block, the amount of used storage space, and the amount of remaining free space.

[0075] Specifically, the server obtains the data block identifier corresponding to the original data block, uses the data block identifier of the original data block as an index condition, locates the record information corresponding to the original data block from the data table, and queries the storage space information of the original data block from the record information corresponding to the original data block according to the keyword corresponding to the storage space, thereby obtaining the remaining free space of the original data block.

[0076] In this embodiment, by using the data block identifier corresponding to the original data block as an index condition to locate the record information of the original data block, the remaining free space of the original data block is obtained, which can achieve fast and accurate acquisition of the remaining free space and improve data processing speed.

[0077] In one embodiment, obtaining the remaining free space of the original data block from the record information includes: obtaining the total storage space of the original data block and the space size of the occupied storage space corresponding to each record row in the original data block from the record information; accumulating the space size of the occupied storage space corresponding to the record row to obtain the cumulative occupied storage space corresponding to the original data block; and obtaining the remaining free space of the original data block based on the total storage space and the cumulative occupied storage space corresponding to the original data block.

[0078] The total storage space refers to the storage space a data block can provide before storing any record rows. The total storage space of different data blocks can be the same or different, and the total storage space of a data block can be configured according to the actual application scenario. The occupied storage space refers to the storage space occupied by each currently stored record row. The cumulative occupied storage space refers to the sum of the storage space occupied by each currently stored record row. The cumulative occupied storage space includes the space for storing the data corresponding to the record row and the space for storing the pointer to the record row.

[0079] Specifically, when the server obtains storage information from the record information in the data table, which is the used storage space of the data block, the server obtains the remaining storage space of the original data block by calculating the difference between the total storage space of the data block and the size of the used storage space. In a specific application, the storage information obtained by the server from the record information in the data table may be the occupied storage space corresponding to each stored record row. Since the occupied storage space corresponding to each record row may be non-contiguous, the server obtains the size of the occupied storage space corresponding to the data block by accumulating the size of the occupied storage space corresponding to each record row in the data block, and then obtains the remaining storage space of the original data block by calculating the difference between the total storage space of the data block and the size of the used storage space.

[0080] When the server obtains the remaining storage space from the record information in the data table, the remaining free space of the original data block can be directly obtained. The remaining free space of the original data block can be obtained by counting each time the data block completes data storage, so that the server can quickly obtain the remaining free space of the original data block.

[0081] In this embodiment, by obtaining the total storage space of the original data block from the record information and calculating the cumulative occupied storage space of each record row, it is possible to analyze the record rows with non-continuous occupied storage space, and based on the total storage space and the cumulative occupied storage space, the remaining free space of the original data block can be accurately obtained, thereby improving the accuracy of the results of subsequent space size comparison processing.

[0082] In one embodiment, Figure 3 As shown, detecting the record row with row migration and determining the row identifier corresponding to the record row includes:

[0083] Step 302 : Based on the data storage mode corresponding to the row migration behavior, a target information table representing the row migration behavior is filtered out from the information table.

[0084] Step 304: Based on the target information table, determine the record rows with row migration and the row identifiers corresponding to the record rows.

[0085] Data storage methods include storing data in the same data block, storing data in different data blocks through row chaining, and migrating data to another data block through row migration while retaining a pointer in the data block where the data was stored before the migration. An information table is a data table used to record storage information corresponding to rows. Based on the storage information recorded in the information table, the data storage method corresponding to each record row can be determined. The information table records the row identifier corresponding to the corresponding record row.

[0086] Specifically, based on the data storage method corresponding to the row migration behavior, the server filters out the target information table representing the row migration behavior from the information table to implement the screening of record rows with row migration behavior. Based on the row identifiers recorded in the target information table, the server determines the record rows with row migration and the row identifiers corresponding to the record rows.

[0087] In this embodiment, the server filters out a target information table representing the row migration behavior from the information table based on the data storage method specific to the row migration behavior. Based on the target information table, it can quickly and accurately locate the record rows with row migration and determine the row identifiers corresponding to the record rows, thereby achieving accurate identification of the record rows with row migration.

[0088] In one embodiment, determining the record row with row migration and the row identifier corresponding to the record row based on the target information table includes:

[0089] Based on the target information table, the row identifier corresponding to the record row that generates the row migration behavior is recorded in the data dictionary table; the data dictionary table is traversed to determine the record row with row migration and the row identifier corresponding to the record row.

[0090] The data dictionary table, created by the UTLCHAIN.SQL or UTLCHN1.SQL script, receives the data results from analyzing the information table. The server traverses the data dictionary table to query the results of the information table analysis, specifically whether there are any rows requiring row migration and, if so, the corresponding row identifier.

[0091] Specifically, the server obtains an information table including record row information, and based on the data storage method corresponding to the row migration behavior, filters the target information table with row migration. The server creates a data dictionary table, analyzes the table with row migration, and records the row identifiers corresponding to the record rows that generate row migration in the data dictionary table. The server can query the row identifiers corresponding to the row migration record rows through the data dictionary table.

[0092] In this embodiment, the server records the row identifiers corresponding to the record rows that generate row migration behavior by creating a data dictionary table, which can facilitate rapid acquisition of the row identifiers corresponding to the record rows with row migration, thereby improving data processing efficiency.

[0093] In one embodiment, the method further includes: triggering a row migration elimination task for the record row according to a preset period; when the remaining free space is less than the storage space of the current record row and there is no record row whose storage space is less than or equal to the remaining free space, ending the row migration elimination task of the current period.

[0094] The preset period refers to a triggering period for the row migration elimination task for the record row, and the preset period can be set according to actual scenario requirements.

[0095] Specifically, within a preset period, the server will attempt to perform row migration elimination on all record rows with row migration behavior. When the remaining free space is greater than or equal to the storage space of the current record row, the server will migrate the current record row to the original data block and delete the pointer to the current data amount in the original data block. When the remaining free space is less than the storage space of the current record row, the server will skip the record row and perform the same processing on the next unprocessed record row as the current record row until there is no record row with storage space less than or equal to the remaining free space, thereby ending the row migration elimination task of the current period and waiting for the triggering of the row migration elimination task for the record row in the next period.

[0096] In this embodiment, by setting a trigger period for the row migration elimination task for the record rows and executing the row migration elimination task for the record rows on a periodic basis, the row migration elimination can be automatically and adaptively performed according to the changes in the storage space in the data block, thereby improving the data processing performance of the database.

[0097] In a specific application, such as Figure 4 As shown, a method for eliminating row migration is provided, which specifically includes the following processing procedures:

[0098] When modifying a non-linked row in Oracle, if the modified row length is greater than the original length and the free space in the data block is too small to accommodate the modified row, Oracle will migrate the entire row to another data block and retain a pointer to the new location of the row in the original data block. This is called a row migration. Queries involving migrated data can degrade database performance because the rowid of the migrated row does not change. Therefore, accessing the migrated row requires accessing two data blocks, resulting in additional I / O operations.

[0099] The traditional solution is to store the migrated data in a temporary table (or intermediate table), delete the migrated data from the original table, retrieve the data from the temporary table, re-insert it into the original table, and then delete the temporary table. Because this approach doesn't consider foreign key constraints, it can't delete rows with migrated data that have foreign key constraints. Furthermore, this approach doesn't process indexes during insertions and deletions, which results in a significant waste of time maintaining the balance of the index tree.

[0100] Since traditional solutions have limited usage scenarios and tablespace fragmentation occurs after the database has run for a period of time, this method proposes to collect the record rows that have row migration, compare the remaining free space in the original data blocks of these record rows with the size of the record rows, and migrate the record rows to the original data blocks when the original data blocks can accommodate them. The pointer to the original data blocks is deleted and the space of the current data blocks is released.

[0101] The specific steps to eliminate row migration are as follows:

[0102] Step 1: Detect and collect rowids of rows that have been migrated;

[0103] The server locates rows where row migrations have occurred and collects the rowids of these rows. Specifically, the server retrieves the table containing row information, selects tables with row migrations based on the data storage method corresponding to the row migration behavior, creates a data dictionary table, analyzes the tables with row migrations, and records the rowid information of the row migrations in the data dictionary table. The data dictionary table can then be queried for rowids of row migrations.

[0104] Step 2: Query the size of the record row;

[0105] Specifically, the server obtains the storage information of the record row through table query. The storage information refers to the size of the storage space occupied by the record row in the data block. By obtaining the storage information, the space size of the record row is determined for comparison with the remaining free space size of the original data block.

[0106] Step 3: Find the original data block where the record row is located;

[0107] Specifically, based on the row identifier, the server determines the current data block where the record row is currently located and the pointer pointing to the current data block corresponding to the record row. The server locates the original data block corresponding to the record row by locating the data block storing the corresponding pointer.

[0108] Step 4: Query the remaining fragment free space of the original data block;

[0109] Specifically, the server obtains the data block identifier corresponding to the original data block, uses the data block identifier of the original data block as an index condition, locates the record information corresponding to the original data block from the data table, and based on the keyword corresponding to the storage space, when the server obtains the storage information from the record information in the data table as the used storage space of the data block, the server obtains the remaining storage space of the original data block by calculating the difference between the total storage space size of the data block and the size of the used storage space. In a specific application, the storage information obtained by the server from the record information in the data table may be the occupied storage space corresponding to each stored record row. Since the occupied storage space corresponding to each record row may be non-continuous, the server obtains the size of the occupied storage space corresponding to the data block by accumulating the size of the occupied storage space corresponding to each record row in the data block, and then obtains the remaining storage space of the original data block by calculating the difference between the total storage space size of the data block and the size of the used storage space.

[0110] Step 5: Determine whether the remaining free space of the original data block is greater than the size of the record row; if so, proceed to step 6; if not, proceed to step 8;

[0111] Step 6: Migrate the record rows in the current data block to the original data block;

[0112] Step 7: Skip the row and wait until the remaining free space of the original data block is greater than the row size before eliminating the row migration.

[0113] Step 8: Delete the pointer in the original data block pointing to the current data block and release the space in the current data block.

[0114] Specifically, if the remaining free space in the original data block is greater than or equal to the storage space required for the row, the row can be migrated from the current data block to the original data block, eliminating the row migration. To eliminate the row migration, the server needs to complete two steps: first, migrate the row in the current data block to the original data block to free up space in the current data block, thus migrating the data from the current data block to the original data block; second, delete the pointer in the original data block pointing to the current data block, thus disassociating the two data blocks.

[0115] The above-mentioned row migration elimination method solves the problem that traditional solutions cannot delete records with foreign key constraints that have row migration, and it also eliminates the need to spend a lot of time on maintaining the balance of the index tree. Since a large amount of table space fragmentation will appear during the long-term operation of the database, based on this point, this solution migrates the record rows to the original data blocks that can accommodate the size of the record rows, deletes the pointers to the original data blocks, and frees up the space of the current data blocks. On the one hand, it eliminates the row migration phenomenon and reduces additional I / O operations. On the other hand, it fills the free fragmented space of the table space, alleviates the phenomenon of table space fragmentation, and frees up data block space, thereby improving the database's data processing performance as a whole.

[0116] It should be understood that, although the steps in the flowcharts of the above embodiments are shown in sequence as indicated by the arrows, these steps are not necessarily performed in the order indicated by the arrows. Unless otherwise specified herein, there is no strict order restriction on the execution of these steps, and these steps can be performed in other orders. Moreover, at least a portion of the steps in the flowcharts of the above embodiments may include multiple steps or multiple stages, and these steps or stages are not necessarily performed at the same time, but can be performed at different times. The execution order of these steps or stages is not necessarily to be performed in sequence, but can be performed in turn or alternately with other steps or at least a portion of steps or stages in other steps.

[0117] Based on the same inventive concept, embodiments of the present application also provide a row migration elimination device for implementing the aforementioned row migration elimination method. The solution provided by this device is similar to the solution described in the aforementioned method. Therefore, the specific limitations of one or more embodiments of the row migration elimination device provided below can be found in the aforementioned limitations of the row migration elimination method and will not be further elaborated here.

[0118] In one embodiment, Figure 5 As shown, a row migration elimination device is provided, comprising: a detection module 502, a database search module 504, a space determination module 506 and a row migration elimination module 508, wherein:

[0119] Detection module 502, used to detect the record row with row migration and determine the row identifier corresponding to the record row;

[0120] A database search module 504 is configured to search, based on the row identifier, the current data block where the record row is located after row migration and the original data block where the record row is located before row migration;

[0121] A space determination module 506 is used to determine the remaining free space of the original data block and the storage space required for the record row;

[0122] The row migration elimination module 508 is configured to migrate the record rows in the current data block to the original data block and delete the pointer in the original data block pointing to the current data block when the remaining free space is not less than the storage space.

[0123] In one embodiment, the space determination module is further used to obtain a data block identifier corresponding to the original data block; locate the record information corresponding to the original data block in the data table using the data block identifier as an index condition; and obtain the remaining free space of the original data block from the record information.

[0124] In one embodiment, the space determination module is also used to obtain the total storage space of the original data block and the space size of the occupied storage space corresponding to each record row in the original data block from the record information; accumulate the space size of the occupied storage space corresponding to the record row to obtain the cumulative occupied storage space corresponding to the original data block; based on the total storage space and the cumulative occupied storage space corresponding to the original data block, obtain the remaining free space of the original data block.

[0125] In one embodiment, the detection module is further used to filter out a target information table representing the row migration behavior from the information table based on the data storage method corresponding to the row migration behavior; based on the target information table, determine the record rows with row migration and the row identifiers corresponding to the record rows.

[0126] In one embodiment, the detection module is further used to record the row identifier corresponding to the record row that generates row migration behavior into the data dictionary table based on the target information table; traverse the data dictionary table to determine the record row with row migration and the row identifier corresponding to the record row.

[0127] In one embodiment, the row migration elimination device also includes a task triggering module for triggering a row migration elimination task for a record row according to a preset period; when the remaining free space is less than the storage space of the current record row and there is no record row whose storage space is less than or equal to the remaining free space, the row migration elimination task of the current period is terminated.

[0128] The above-mentioned row migration elimination device detects the record row with row migration and determines the row identifier corresponding to the record row, so as to facilitate the positioning of the record row. Based on the row identifier, the current data block where the record row is located after row migration and the original data block where the record row is located before row migration are found, and the positions of the record row before and after migration are determined. The remaining free space of the original data block and the storage space required to be occupied by the record row are determined so as to compare the space sizes. When the remaining free space is not less than the storage space, the record row in the current data block is migrated to the original data block, and the pointer in the original data block pointing to the current data block is deleted, thereby eliminating row migration, reducing additional I / O operations, and improving the data processing performance of the database as a whole.

[0129] Each module in the aforementioned row migration elimination device can be implemented in whole or in part through software, hardware, or a combination thereof. Each module can be embedded in or independent of a processor in a computer device in the form of hardware, or can be stored in a memory in the computer device in the form of software, so that the processor can call and execute the corresponding operations of each module.

[0130] In one embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as follows: Figure 6 As shown. The computer device includes a processor, a memory, and a network interface connected via a system bus. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program, and a database. The internal memory provides an environment for the operation of the operating system and computer program in the non-volatile storage medium. The database of the computer device is used to store data. The network interface of the computer device is used to communicate with an external terminal via a network connection. When the computer program is executed by the processor, a row migration elimination method is implemented.

[0131] Those skilled in the art will understand that Figure 6 The structure shown in the figure is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the computer device to which the solution of the present application is applied. The specific computer device may include more or fewer components than shown in the figure, or combine certain components, or have a different component arrangement.

[0132] In one embodiment, a computer device is provided, including a memory and a processor, wherein a computer program is stored in the memory, and when the processor executes the computer program, the following steps are implemented:

[0133] Detect the record rows that have row migration and determine the row identifier corresponding to the record row; based on the row identifier, find the current data block where the record row is located after row migration and the original data block where the record row was located before row migration; determine the remaining free space in the original data block and the storage space required for the record row; when the remaining free space is not less than the storage space, migrate the record row in the current data block to the original data block and delete the pointer in the original data block pointing to the current data block.

[0134] In one embodiment, when the processor executes the computer program, the processor further implements the following steps:

[0135] Obtain a data block identifier corresponding to the original data block; use the data block identifier as an index condition to locate record information corresponding to the original data block in the data table; and obtain the remaining free space of the original data block from the record information.

[0136] In one embodiment, when the processor executes the computer program, the processor further implements the following steps:

[0137] The total storage space of the original data block and the size of the occupied storage space corresponding to each record row in the original data block are obtained from the record information; the size of the occupied storage space corresponding to the record rows is accumulated to obtain the cumulative occupied storage space corresponding to the original data block; based on the total storage space and the cumulative occupied storage space corresponding to the original data block, the remaining free space of the original data block is obtained.

[0138] In one embodiment, when the processor executes the computer program, the processor further implements the following steps:

[0139] Based on the data storage mode corresponding to the row migration behavior, a target information table representing the row migration behavior is filtered out from the information table; based on the target information table, a record row with row migration and a row identifier corresponding to the record row are determined.

[0140] In one embodiment, when the processor executes the computer program, the processor further implements the following steps:

[0141] Based on the target information table, the row identifier corresponding to the record row that generates the row migration behavior is recorded in the data dictionary table; the data dictionary table is traversed to determine the record row with row migration and the row identifier corresponding to the record row.

[0142] In one embodiment, when the processor executes the computer program, the processor further implements the following steps:

[0143] According to the preset cycle, the row migration elimination task for the record row is triggered; when the remaining free space is less than the storage space of the current record row and there is no record row with storage space less than or equal to the remaining free space, the row migration elimination task of the current cycle is ended.

[0144] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the following steps are implemented:

[0145] Detect the record rows that have row migration and determine the row identifier corresponding to the record row; based on the row identifier, find the current data block where the record row is located after row migration and the original data block where the record row was located before row migration; determine the remaining free space in the original data block and the storage space required for the record row; when the remaining free space is not less than the storage space, migrate the record row in the current data block to the original data block and delete the pointer in the original data block pointing to the current data block.

[0146] In one embodiment, when the computer program is executed by a processor, the following steps are further implemented:

[0147] Obtain a data block identifier corresponding to the original data block; use the data block identifier as an index condition to locate record information corresponding to the original data block in the data table; and obtain the remaining free space of the original data block from the record information.

[0148] In one embodiment, when the computer program is executed by a processor, the following steps are further implemented:

[0149] The total storage space of the original data block and the size of the occupied storage space corresponding to each record row in the original data block are obtained from the record information; the size of the occupied storage space corresponding to the record rows is accumulated to obtain the cumulative occupied storage space corresponding to the original data block; based on the total storage space and the cumulative occupied storage space corresponding to the original data block, the remaining free space of the original data block is obtained.

[0150] In one embodiment, when the computer program is executed by a processor, the following steps are further implemented:

[0151] Based on the data storage mode corresponding to the row migration behavior, a target information table representing the row migration behavior is filtered out from the information table; based on the target information table, a record row with row migration and a row identifier corresponding to the record row are determined.

[0152] In one embodiment, when the computer program is executed by a processor, the following steps are further implemented:

[0153] Based on the target information table, the row identifier corresponding to the record row that generates the row migration behavior is recorded in the data dictionary table; the data dictionary table is traversed to determine the record row with row migration and the row identifier corresponding to the record row.

[0154] In one embodiment, when the computer program is executed by a processor, the following steps are further implemented:

[0155] According to the preset cycle, the row migration elimination task for the record row is triggered; when the remaining free space is less than the storage space of the current record row and there is no record row with storage space less than or equal to the remaining free space, the row migration elimination task of the current cycle is ended.

[0156] In one embodiment, a computer program product is provided, comprising a computer program, which, when executed by a processor, implements the following steps:

[0157] Detect the record rows that have row migration and determine the row identifier corresponding to the record row; based on the row identifier, find the current data block where the record row is located after row migration and the original data block where the record row was located before row migration; determine the remaining free space in the original data block and the storage space required for the record row; when the remaining free space is not less than the storage space, migrate the record row in the current data block to the original data block and delete the pointer in the original data block pointing to the current data block.

[0158] In one embodiment, when the computer program is executed by a processor, the following steps are further implemented:

[0159] Obtain a data block identifier corresponding to the original data block; use the data block identifier as an index condition to locate record information corresponding to the original data block in the data table; and obtain the remaining free space of the original data block from the record information.

[0160] In one embodiment, when the computer program is executed by a processor, the following steps are further implemented:

[0161] The total storage space of the original data block and the size of the occupied storage space corresponding to each record row in the original data block are obtained from the record information; the size of the occupied storage space corresponding to the record rows is accumulated to obtain the cumulative occupied storage space corresponding to the original data block; based on the total storage space and the cumulative occupied storage space corresponding to the original data block, the remaining free space of the original data block is obtained.

[0162] In one embodiment, when the computer program is executed by a processor, the following steps are further implemented:

[0163] Based on the data storage mode corresponding to the row migration behavior, a target information table representing the row migration behavior is filtered out from the information table; based on the target information table, a record row with row migration and a row identifier corresponding to the record row are determined.

[0164] In one embodiment, when the computer program is executed by a processor, the following steps are further implemented:

[0165] Based on the target information table, the row identifier corresponding to the record row that generates the row migration behavior is recorded in the data dictionary table; the data dictionary table is traversed to determine the record row with row migration and the row identifier corresponding to the record row.

[0166] In one embodiment, when the computer program is executed by a processor, the following steps are further implemented:

[0167] According to the preset cycle, the row migration elimination task for the record row is triggered; when the remaining free space is less than the storage space of the current record row and there is no record row with storage space less than or equal to the remaining free space, the row migration elimination task of the current cycle is ended.

[0168] Those skilled in the art will appreciate that all or part of the processes in the above-mentioned embodiment methods can be implemented by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, database or other media used in the embodiments provided in this application may include at least one of non-volatile and volatile memory. Non-volatile memory may include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory may include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM). The database involved in the various embodiments provided herein may include at least one of a relational database and a non-relational database. Non-relational databases may include, but are not limited to, distributed databases based on blockchains. The processor involved in the various embodiments provided herein may be, but are not limited to, a general-purpose processor, a central processing unit, a graphics processing unit, a digital signal processor, a programmable logic unit, a data processing logic unit based on quantum computing, and the like.

[0169] The technical features of the above embodiments can be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0170] The above-described embodiments merely represent several implementation methods of the present application. While the descriptions are relatively specific and detailed, they should not be construed as limiting the scope of the present application. It should be noted that a person of ordinary skill in the art may make various modifications and improvements without departing from the spirit of the present application, and these modifications and improvements fall within the scope of protection of the present application. Therefore, the scope of protection of the present application shall be determined by the appended claims.

Claims

1. A method for eliminating row migration, characterized in that: The method comprises: Detecting a record row with row migration and determining a row identifier corresponding to the record row; wherein row migration refers to a data row that does not fit into the current block and is relocated to another block, with a pointer retained in the original block, and the indexed row identifier pointing to the original location; Based on the row identifier, searching for the current data block where the record row is located after row migration and the original data block where the record row is located before row migration; Determining the remaining free space of the original data block and the storage space required to be occupied by the record row; When the remaining free space is not less than the storage space, migrating the record rows in the current data block to the original data block, and deleting the pointer in the original data block pointing to the current data block; The detecting of the record row having row migration and determining the row identifier corresponding to the record row includes: Based on the data storage method corresponding to the row migration behavior, the target information table representing the row migration behavior is filtered out from the information table; Based on the target information table, determining a record row where row migration occurs and a row identifier corresponding to the record row; The determining of the remaining free space of the original data block includes: Obtaining a data block identifier corresponding to the original data block; Using the data block identifier as an index condition, locating the record information corresponding to the original data block in the data table; The remaining free space of the original data block is obtained from the record information.

2. The method according to claim 1, characterized in that The obtaining of the remaining free space of the original data block from the record information includes: Obtaining the total storage space of the original data block and the size of the occupied storage space corresponding to each record row in the original data block from the record information; Accumulating the size of the occupied storage space corresponding to the record row to obtain the cumulative occupied storage space corresponding to the original data block; Based on the total storage space corresponding to the original data block and the accumulated occupied storage space, the remaining free space of the original data block is obtained.

3. The method according to claim 1, characterized in that The determining, based on the target information table, the record row where row migration occurs and the row identifier corresponding to the record row includes: Based on the target information table, recording the row identifier corresponding to the record row generating the row migration behavior into the data dictionary table; The data dictionary table is traversed to determine the record rows where row migration exists and the row identifiers corresponding to the record rows.

4. The method according to claim 1, wherein The method further comprises: Trigger the row migration and elimination task for the record rows according to the preset cycle; When the remaining free space is smaller than the storage space of the current record row and there is no record row whose storage space is smaller than or equal to the remaining free space, the row migration elimination task of the current cycle is terminated.

5. A row migration elimination device, characterized in that: The device comprises: a detection module, configured to detect a row of records that has undergone row migration and determine a row identifier corresponding to the row of records; wherein row migration refers to a data row that is not suitable for being placed in the current block and is therefore relocated to another block, with a pointer retained in the original block, and the row identifier indexed to point to the original location; a database search module configured to search, based on the row identifier, for the current data block where the record row is located after row migration and the original data block where the record row is located before row migration; A space determination module, configured to determine the remaining free space of the original data block and the storage space required to be occupied by the record row; a row migration elimination module, configured to migrate the record rows in the current data block to the original data block and delete the pointer in the original data block pointing to the current data block when the remaining free space is not less than the storage space; The detection module is specifically configured to filter out a target information table representing the row migration behavior from the information table based on the data storage method corresponding to the row migration behavior; and determine, based on the target information table, the record rows where the row migration occurs and the row identifiers corresponding to the record rows; The space determination module is specifically used to obtain the data block identifier corresponding to the original data block; locate the record information corresponding to the original data block in the data table using the data block identifier as an index condition; and obtain the remaining free space of the original data block from the record information.

6. The device according to claim 5, characterized in that The space determination module is further configured to obtain, from the record information, the total storage space of the original data block and the size of the occupied storage space corresponding to each record row in the original data block; and to accumulate the sizes of the occupied storage space corresponding to the record rows to obtain the cumulative occupied storage space corresponding to the original data block; Based on the total storage space corresponding to the original data block and the accumulated occupied storage space, the remaining free space of the original data block is obtained.

7. The device according to claim 5, characterized in that The detection module is also used to record the row identifier corresponding to the record row that generates row migration behavior into the data dictionary table based on the target information table; traverse the data dictionary table to determine the record row with row migration and the row identifier corresponding to the record row.

8. The device according to claim 5, characterized in that The row migration elimination device also includes a task triggering module, which triggers the row migration elimination task for the record row according to a preset period; when the remaining free space is less than the storage space of the current record row and there is no record row with a storage space less than or equal to the remaining free space, the row migration elimination task of the current period is ended.

9. A computer device comprising a memory and a processor, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 4 are implemented.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 4 are implemented.

11. A computer program product comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 4 are implemented.

Images