Online recombination method, device and equipment of database, medium and product

Abstract

The invention discloses an online database recombination method, device and equipment, a medium and a product. The method comprises the following steps: determining a current positioning page located in a leaf layer in a B + tree of a database; exclusively locking all data pages on a path from a root node of the B + tree to the current positioning page and brother pages of the current positioning page; acquiring a right brother page of the current positioning page; and copying the records of the right brother page to the last part of the current positioning page in sequence until the current positioning page is full or the records of the right brother page are completely copied to the current positioning page, adjusting the structure of the B + tree and releasing all exclusive locks of the B + tree. The fragmentation problem of the current positioning page is solved, and the data orderliness of the B + tree is maintained; and when the index is recombined, exclusive blocking is not carried out on the whole data table, so that the influence on service logic is reduced, the recombined index and concurrent DML operation performance is better, the concurrency and availability of the database system are improved, and the overall performance of the database system is improved.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, apparatus, device, medium, and product for online database reorganization. Background Technology

[0002] In database systems, B+ trees are typically used as the index structure. When deleting data records from a table, to maintain database transaction characteristics and improve the performance of deletion operations, pages are not immediately reorganized or the index structure is adjusted. Only when all transactions involving deleted data can be cleaned up uniformly will the deleted records on the pages be cleaned up and page reorganization performed. If the table contains a large amount of data and performs a large number of Data Manipulation Language (DML) operations, such as inserts, updates, and deletes, records in the index may be frequently deleted, making record storage sparse and leading to index fragmentation.

[0003] Currently, index fragmentation is typically addressed by rebuilding the index, which involves re-inserting all records from the index into a newly created B+ tree, replacing the original, severely fragmented B+ tree. There are two methods for index rebuilding: offline rebuilding and online rebuilding. Offline rebuilding results in prolonged service downtime, impacting business logic, reducing database system concurrency and availability, and affecting overall performance. While online rebuilding solves the problem of prolonged table blocking during offline rebuilding, it still involves exclusive locking of the table at the start and end of the rebuild, leading to DML operation performance degradation. Even after rebuilding, the most compact data storage effect is not achieved, and changes to the root page address can invalidate all planned caches on the table.

[0004] It is evident that current index reconstruction methods, whether offline or online, have certain drawbacks and limitations, and cannot achieve true full online operation. Summary of the Invention

[0005] This invention provides a method, apparatus, device, medium, and product for online database reorganization, overcoming the shortcomings and limitations of current offline or online index reconstruction methods.

[0006] In a first aspect, embodiments of the present invention provide an online database reorganization method, comprising:

[0007] Determine the current page located at the leaf level in the B+ tree of the database;

[0008] Exclusive locks are applied to all data pages along the path from the root node of the B+ tree to the currently located page, as well as to the sibling pages of the currently located page.

[0009] Get the right sibling page of the currently located page;

[0010] The records of the right sibling page are copied sequentially to the end of the current positioning page until the current positioning page is full or all the records of the right sibling page are copied to the current positioning page. The structure of the B+ tree is then adjusted and all exclusive locks of the B+ tree are released.

[0011] Secondly, embodiments of the present invention provide an online database reorganization apparatus, comprising:

[0012] The page location determination module is used to determine the current page located in the leaf layer of the B+ tree in the database;

[0013] The locking module is used to apply exclusive locks to all data pages on the path from the root node of the B+ tree to the currently located page, as well as to the sibling pages of the currently located page.

[0014] The right sibling page acquisition module is used to acquire the right sibling page of the currently positioned page;

[0015] The reorganization module is used to copy the records of the right sibling page sequentially to the end of the current positioning page until the current positioning page is full or all the records of the right sibling page are copied to the current positioning page, adjust the structure of the B+ tree, and release all exclusive locks of the B+ tree.

[0016] Thirdly, embodiments of the present invention provide an electronic device, the electronic device comprising:

[0017] At least one processor;

[0018] and a memory communicatively connected to the at least one processor;

[0019] The memory stores a computer program that can be executed by the at least one processor, which enables the at least one processor to perform the online database reorganization method according to any embodiment of the present invention.

[0020] Fourthly, embodiments of the present invention provide a computer-readable storage medium storing computer instructions, which are used to cause a processor to execute and implement the online database reorganization method described in any embodiment of the present invention.

[0021] Fifthly, embodiments of the present invention provide a computer program product including a computer program, which, when executed by a processor, implements the online database reorganization method described in any embodiment of the present invention.

[0022] The technical solution of this invention involves: determining the current location page in the leaf layer of the B+ tree of the database; applying exclusive locks to all data pages on the path from the root node of the B+ tree to the current location page, as well as to the sibling pages of the current location page; acquiring the right sibling page of the current location page; copying the records of the right sibling page sequentially to the end of the current location page until the current location page is full or all records of the right sibling page have been copied to the current location page; adjusting the structure of the B+ tree and releasing all exclusive locks in the B+ tree. By copying as many records as possible from the right sibling page to the current location page, the free space of the current location page is reduced, solving the fragmentation problem of the current location page and maintaining the data ordering of the B+ tree. Moreover, during index reorganization, exclusive locks are not applied to the entire data table, but only to the data pages between the root node and the current location page, as well as the sibling pages of the current location page. This allows concurrent transactions to query and perform DML operations on index pages that are not subject to exclusive locks, reducing the impact on business logic. This overcomes the shortcomings and limitations of current offline or online index rebuilding methods, resulting in better performance for index reorganization and concurrent DML operations, improving the concurrency and availability of the database system, and enhancing the overall performance of the database system.

[0023] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

[0024] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0025] Figure 1 A flowchart illustrating an online database reorganization method provided in Embodiment 1 of the present invention;

[0026] Figure 2 This is a flowchart of an online database reorganization method provided in Embodiment 2 of the present invention;

[0027] Figure 3 This is a schematic diagram of a B+ tree structure provided in Embodiment 2 of the present invention;

[0028] Figure 4 This is a schematic diagram of the structure of an online database reorganization device provided in Embodiment 3 of the present invention;

[0029] Figure 5 A schematic diagram of the structure of an electronic device for implementing the online database reorganization method of this embodiment of the invention. Detailed Implementation

[0030] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0031] It should be noted that the terms "current," "previous," and "next," etc., used in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0032] In database systems, B+ trees are typically used as the index structure, with each data page being a B+ tree node. Inserting a large amount of data into a table results in more data pages in the index, occupying more space. When deleting data records from a table, because rebuilding or adjusting the B+ tree is time-consuming, to maintain database transaction characteristics and improve deletion performance, pages are not immediately rebuilt or the index structure is adjusted. Instead, a deletion mark is placed on the records in the index data pages, and the transaction number stored in the records is modified to the current transaction number, indicating that the row of data has been deleted by the current transaction. Only when all transactions that deleted data can be purged uniformly will the purge thread clean up the deleted records on the pages and perform page rebuilding. If the time for uniformly deleting all records in the pages is not reached, the system will not adjust the index structure. Therefore, although data is deleted from the table, the number of index data pages and the storage space occupied may not decrease for a considerable period of time.

[0033] If a table contains a large amount of data and undergoes numerous DML operations (insert, update, delete), records in the index may be frequently deleted, resulting in a lot of wasted space on data pages. This leads to sparse record storage and an increase in the amount of storage space occupied, a situation known as index fragmentation. When index fragmentation is severe, operations such as sequential scans and range queries require accessing more pages, degrading table read / write performance and impacting the overall performance of the database system. In such cases, defragmentation is typically necessary to reorganize and clean up severely wasted data pages, resolving the fragmentation problem and making the index storage more compact for better read / write performance.

[0034] Currently, index fragmentation is typically addressed by rebuilding the index. This involves re-inserting all records from the original, severely fragmented B+ tree index into a newly created one. Since the new B+ tree only performs insertion operations, records are stored in the most compact manner, eliminating redundant records and wasted space, thus preventing fragmentation. This improves read and write efficiency for the table and indexes, enhancing system performance. However, after a period of DML operations, severe fragmentation may reappear in the table and indexes. If DML operations on the table are frequent, it becomes necessary to frequently rebuild the table or indexes to reduce data fragmentation.

[0035] There are two ways to rebuild an index: offline rebuilding and online rebuilding. The main steps of offline rebuilding are as follows: (1) Lock the index to be operated on and its table to prevent concurrent access or modification; (2) Create a new B+ tree index with the same structure as the old B+ tree index that is severely fragmented; (3) Traverse the old B+ tree index and insert all records into the new B+ tree index; (4) Modify the dictionary so that the index uses the newly created B+ tree and delete the old B+ tree that is severely fragmented; (5) Release the exclusive lock on the index and the index fragmentation is completed. The disadvantage of this method is that an exclusive lock is placed on the index and its table during rebuilding. The table is unavailable during the rebuilding period, and queries and DML operations on the table cannot be performed normally. When the amount of data in the table is large, the table will be unable to provide services for a long time, affecting business logic, reducing the concurrency and availability of the database system, and affecting the overall performance.

[0036] The main steps for online index rebuilding are as follows: (1) Start a DDL transaction and lock the table object to be operated on at a specific level to prevent concurrent modification; (2) Create a temporary table with the same structure as the original table. Enable the logical log for the original table to capture the insert, update and delete operations on the original table and record the corresponding logical logs; (3) Downgrade the locking of the table in step (1) so that concurrent transactions can query and perform DML operations on the table, but are not allowed to perform DDL operations on the table; (4) The current DDL transaction performs query insertion at the serializable isolation level and inserts all data in the table into the newly created temporary table; (5) If it is necessary to rebuild the secondary index (such as if the user specifies to rebuild the secondary index), then all the secondary indexes of the original table are created on the temporary table; (6) Redo all the DML operations recorded in the logical log of the original table after the DDL transaction started on the temporary table; (7) Lock the table object again, swap the index B+ trees of the original table and the temporary table, and delete the temporary table; (8) Commit the DDL transaction, release the locking of the table object, and the table data page fragmentation is completed. This approach addresses the drawbacks of offline table rebuilding that leads to prolonged blocking, allowing concurrent queries and DML operations during the data transfer phase, thus improving database concurrency and availability to some extent. However, it has the following disadvantages:

[0037] 1. The table will still be exclusively locked at the beginning and end of the reconstruction.

[0038] 2. During the rebuild, enable logical logging to capture DML operations on the table. This will increase the log volume and degrade DML performance.

[0039] 3. All DML operations recorded in the logical log need to be redone on the temporary table. If the amount of concurrent DML is large, the redo will take a long time and affect performance.

[0040] 4. If the amount of concurrent DML is large, redoing DML may cause temporary table indexes to become fragmented, and even after rebuilding, the most compact data storage may still not be achieved.

[0041] 5. After the reconstruction is completed, the index B+ trees of the original table and the temporary table need to be swapped. The root page address changes, causing all planned caches on the table to become invalid.

[0042] It is evident that current index reconstruction methods, whether offline or online, have certain drawbacks and limitations, and cannot achieve true full online operation.

[0043] To address the aforementioned issues, this invention provides an online database reorganization method. During index reorganization, no exclusive locks are applied to the entire table, allowing concurrent transactions to query and perform DML operations on the table and indexes throughout the process. This results in improved performance for index reorganization and concurrent DML operations, prevents changes to the root page address of the B+ tree, and avoids invalidating the join plan. It eliminates the performance bottlenecks and limitations of current index reorganization methods, improves the concurrency and availability of the database system, and enhances the overall performance of the database system.

[0044] Example 1

[0045] Figure 1 This is a flowchart of an online database reorganization method provided in Embodiment 1 of the present invention. This embodiment is applicable to the online index reorganization of data pages in a database. The method can be executed by an online database reorganization device, which can be implemented in hardware and / or software and can be configured in an electronic device. Figure 1 As shown, the method includes:

[0046] S110. Determine the current page located at the leaf level in the B+ tree of the database.

[0047] B+ trees are a type of tree data structure commonly used in file systems of databases and operating systems. A B+ tree is a variation of the B-tree where non-leaf nodes store only indexes, while all actual data is stored in the leaf nodes, which are organized into a linked list.

[0048] The currently located page can be understood as the data page currently being viewed within the database. The currently located page can be the data page corresponding to a leaf node in the leaf level of a B+ tree.

[0049] Specifically, a current location page is selected from the leaf level of the B+ tree in the database. The current location page can be selected according to requirements (user instructions or system instructions), or it can be determined sequentially from left to right according to the leaf nodes in the leaf level.

[0050] For example, the leftmost data page in the leaf layer can be used as the first current positioning page. After each reorganization of the current positioning page, the data page to the right can be determined as the next positioning page in order from left to right. Alternatively, the data page corresponding to one of the leaf nodes can be selected as the current positioning page based on the degree of fragmentation among the data pages.

[0051] S120. Apply exclusive locks to all data pages on the path from the root node of the B+ tree to the currently located page, as well as to the sibling pages of the currently located page.

[0052] Exclusive locks (X locks) can be understood as a fundamental concurrency control mechanism in database systems, used to ensure the exclusivity and consistency of data operations. Once an exclusive lock is placed on a data page, other threads are not allowed to concurrently access the node containing that data page.

[0053] The sibling pages of the currently located page can include the left sibling page and / or the right sibling page. The left sibling page refers to the data page corresponding to the leaf node to the left of the currently located page in the leaf node list, and the right sibling page refers to the data page corresponding to the leaf node to the right of the currently located page in the leaf node list.

[0054] Specifically, the path from the root node of the B+ tree to the currently located page is determined, and exclusive locks are applied to all data pages along the path, as well as the sibling pages of the currently located page. X locks are applied to all data pages along the path, as well as the left and right sibling pages of the located page, because subsequent record migrations may release B+ tree pages, requiring a bottom-up adjustment of the B+ tree structure. X locks prevent other threads from concurrently accessing nodes along the path, but read / write operations on other paths are unaffected, and query, insert, update, and delete operations on the index are allowed.

[0055] It's understandable that if the leftmost page of the leaf layer doesn't have a left sibling page, the upper exclusive lock process for the left sibling page can be skipped. Similarly, if the rightmost page of the leaf layer doesn't have a left sibling page, the upper exclusive lock process for the right sibling page can be skipped.

[0056] In this embodiment, during index reorganization, the entire data table is not exclusively locked. Exclusive locks are only placed on the data pages between the root node and the current location page, as well as the sibling pages of the current location page. Concurrent transactions are allowed to query and perform DML operations on index pages that are not subject to exclusive locks, reducing the impact on business logic and improving the performance of index reorganization and concurrent DML operations.

[0057] S130. Get the right sibling page of the currently located page.

[0058] Specifically, retrieve the right sibling page of the currently located page from the leaf node linked list.

[0059] Understandably, if the currently located page does not have a right sibling page, it means that the currently located page is already the rightmost page of the leaf layer (i.e., the leaf node linked list), and the online reorganization can be terminated.

[0060] S140. Copy the records of the right sibling page in order to the end of the current positioning page until the current positioning page is full or all the records of the right sibling page are copied to the current positioning page. Adjust the structure of the B+ tree and release all exclusive locks on the B+ tree.

[0061] Specifically, if the right sibling page of the currently located page exists, the records from the right sibling page are copied sequentially to the end of the current located page according to their order. The records copied to the current located page maintain the same order as in the right sibling page. This continues until the current located page is full or all records from the right sibling page have been copied to the current located page. This indicates that the current page can no longer hold the records from the right sibling page, representing the most compact data storage state. The current located page has been processed, and the fragmentation problem has been resolved. The structure of the B+ tree is adjusted, and all exclusive locks on the B+ tree are released to prevent prolonged impact on concurrent tasks.

[0062] We can return to step S110 to determine the next currently located page in the leaf layer of the B+ tree of the database, and continue to execute steps S120 to S140 until the reorganization of the rightmost located page in the leaf layer is completed.

[0063] In this embodiment, as many records as possible from the right sibling page are copied to the current location page, reducing the free space on the current location page and solving the fragmentation problem. Since the order of records in the right sibling page remains unchanged during the copying process, the key-value relationship of the B+ tree's leaf layers is not affected, thus maintaining the data order of the B+ tree. Furthermore, after the reorganization is complete, the B+ tree root page address is not changed, the dictionary cache is not cleared, and the index dictionary is not modified.

[0064] The technical solution of this invention determines the current location page in the leaf layer of the B+ tree of the database; applies exclusive locks to all data pages on the path from the root node of the B+ tree to the current location page, as well as the sibling pages of the current location page; acquires the right sibling page of the current location page; and copies the records of the right sibling page sequentially to the end of the current location page until the current location page is full or all records of the right sibling page have been copied to the current location page. The structure of the B+ tree is then adjusted, and all exclusive locks on the B+ tree are released. By copying as many records as possible from the right sibling page to the current location page, the free space of the current location page is reduced, solving the fragmentation problem of the current location page and maintaining the data order of the B+ tree. Moreover, during index reorganization, exclusive locks are not applied to the entire data table; only the data pages between the root node and the current location page, as well as the sibling pages of the current location page, are subject to exclusive locks. This allows concurrent transactions to query and perform DML operations on index pages without exclusive locks, reducing the impact on business logic. The performance of index reorganization and concurrent DML operations is improved, enhancing the concurrency and availability of the database system and improving the overall performance of the database system.

[0065] Example 2

[0066] Figure 2This is a flowchart of an online database reorganization method provided in Embodiment 2 of the present invention. Based on the above embodiments, this embodiment further refines the step of sequentially copying the records of the right sibling page to the end of the current positioning page until the current positioning page is full or all records of the right sibling page have been copied to the current positioning page, adjusting the structure of the B+ tree, and releasing all exclusive locks on the B+ tree. This includes: cyclically copying the first record of the right sibling page to the end of the current positioning page until the current positioning page is full or all records of the right sibling page have been copied to the current positioning page, and saving the record copy count; if the record copy count is less than the number of records in the right sibling page but not zero, then deleting the copied records from the right sibling page and adjusting the structure of the B+ tree; if the record copy count is equal to the number of records in the right sibling page, then releasing the right sibling page and adjusting the structure of the B+ tree.

[0067] like Figure 2 As shown, the method includes:

[0068] S210. Determine the current page located at the leaf level in the B+ tree of the database.

[0069] In an optional embodiment, S210, determining the current location page in the leaf level of the database's B+ tree includes:

[0070] Create a cursor for the B+ tree, and based on the cursor, traverse from the root node of the B+ tree to the leftmost page of the leaf layer of the B+ tree, and determine the leftmost page as the first currently positioned page;

[0071] The next currently located page in the B+ tree is located based on the saved key value, where the saved key value is the key value of the last record in the right sibling page of the previous located page.

[0072] The cursor is a core component in a database system used for traversing and manipulating B+ tree indexes, providing precise positioning and movement capabilities within the index structure.

[0073] Specifically, Figure 3 This is a schematic diagram of a B+ tree structure provided in Embodiment 2 of the present invention. Figure 3 As shown, based on the cursor, the leftmost page (D node) of the leaf layer of the B+ tree is determined as the first currently located page by traversing from the root node (A node) of the B+ tree to the leftmost page (D node) of the leaf layer of the B+ tree.

[0074] To ensure that while reorganizing the index of the first currently positioned page, the next currently positioned page can be determined for index reorganization, avoiding the performance waste caused by scanning the B+ tree from the leftmost page of the leaf level each time, the key value of the last record in the right sibling page of the previous positioned page can be saved when the first currently positioned page is reorganized. Therefore, when determining a page that is not the first currently positioned page, the next currently positioned page in the B+ tree can be located based on the saved key value, that is, the right sibling page of the previous positioned page is used as the current currently positioned page. This ensures that records with key values ​​smaller than the current key value are always on the left side of the cursor and have already been reorganized. After repositioning, reorganization only needs to continue to the right until the index reorganization of all leaf nodes in the leaf level of the B+ tree is completed.

[0075] S220. Apply exclusive locks to all data pages on the path from the root node of the B+ tree to the currently located page, as well as to the sibling pages of the currently located page.

[0076] For example, such as Figure 3 As shown, for the currently located page (node ​​D), exclusive locks are applied to all data pages on the path from the root node of the B+ tree to the currently located page, as well as to the sibling pages of the currently located page. Specifically, X locks are applied to nodes A, B, C, D, and E. Other nodes in the B+ tree are not locked. Similarly, for the currently located page (node ​​E), exclusive locks are applied to all data pages on the path from the root node of the B+ tree to the currently located page, as well as to the sibling pages of the currently located page. Specifically, X locks are applied to nodes A, B, C, D, E, and G. Other nodes in the B+ tree are not locked.

[0077] S230. Get the right sibling page of the currently located page.

[0078] Specifically, the cursor is positioned at the last record in the currently positioned page. The data page containing the record with the next key value (i.e., the right sibling page of the currently positioned page) is then retrieved. Due to the ordered nature of the leaf levels of a B+ tree, the last record in a data page is the record with the largest key value, and the key values ​​of records in the right sibling page are always greater than all key values ​​in the current data page. If there are records with the same key value, the system typically generates a ROWID for each record and sorts them to ensure the strict size relationship within the B+ tree.

[0079] For example, such as Figure 3 As shown, for the currently located page (node ​​D), retrieve the right sibling page of the currently located page, which is the data page corresponding to node E. For the currently located page (node ​​E), retrieve the right sibling page of the currently located page, which is the data page corresponding to node G.

[0080] S240. Repeatedly copy the first record of the right sibling page to the end of the current page until the current page is full or all records of the right sibling page are copied to the current page. Save the number of records copied.

[0081] The record copy count can be understood as the number of records copied to the currently located page.

[0082] For example, if a right sibling page exists, each time the first record of the right sibling page is copied to the end of the currently positioned page, the record copy count is incremented by 1. For example... Figure 3 As shown, for the current location page (D node), the first record in the data page of E node is copied to the end of the current location page each time, until the current location page (D node) is full or all records of the right sibling page (E node) are copied to the current location page, and the current number of records copied is saved.

[0083] Copying the first record of the right sibling page to the end of the current page can result in three scenarios: Scenario 1: The record copy count is zero, indicating the current page is full and cannot copy the right sibling page's record. Therefore, neither the current page nor its right sibling page changes, and no adjustment to the B+ tree structure is needed. Scenario 2: The record copy count is less than the number of records in the right sibling page but not zero, indicating the current page has copied a portion of the right sibling page's records, requiring adjustment to the B+ tree structure. Scenario 3: The record copy count is equal to the number of records in the right sibling page and not zero, indicating the current page has copied all the records in the right sibling page, also requiring adjustment to the B+ tree structure. In scenarios 1 and 2, the right sibling page is not fully processed; in scenario 3, the right sibling page is fully processed. In all three scenarios, the current page is fully processed.

[0084] S250. If the number of records copied is less than the number of records in the right sibling page and is not zero, then delete the copied records from the right sibling page and adjust the structure of the B+ tree.

[0085] Specifically, if the number of records copied is less than the number of records in the right sibling page and is not zero, it means that the currently positioned page has copied part of the records in the right sibling page. Therefore, the copied records are deleted from the right sibling page, the structure of the B+ tree is adjusted, and step S270 is continued.

[0086] In an optional embodiment, when the number of record copies is less than the number of records in the right sibling page and is not zero, adjusting the structure of the B+ tree includes:

[0087] Modify the key value of the index tuple in the parent page of the right sibling page to the key value of the first record of the right sibling page after the record is deleted.

[0088] Specifically, since the key value of the B+ tree inner page index tuple is the key value of the first record of the data page it points to, if some records in the right sibling page are migrated to the current location page, the first record of the right sibling page will change. It is necessary to modify the index tuple in the parent page of the right sibling page so that its key value is equal to the key value of the migrated first record in the right sibling page.

[0089] For example, such as Figure 3 As shown, for the current location page (node ​​D), some records from the right sibling page (node ​​E) are migrated to the current location page, the copied records are deleted from the right sibling page, and the index tuple in the parent page (node ​​C) of the right sibling page is modified to the key value of the first record in the right sibling page (node ​​E) after the record is deleted.

[0090] S260. If the number of record copies is equal to the number of records in the right sibling page, then release the right sibling page and adjust the structure of the B+ tree.

[0091] Specifically, if the number of records copied is equal to the number of records in the right sibling page, it means that the currently positioned page has copied all the records of the right sibling page. Therefore, the right sibling page is released and the structure of the B+ tree is adjusted, and step S270 is continued.

[0092] In an optional embodiment, when the number of record copies is equal to the number of records in the right sibling page, adjusting the structure of the B+ tree includes:

[0093] Delete the index tuple that points to the right sibling page from the parent page of the right sibling page.

[0094] Specifically, if all records in the right sibling page are migrated to the current location page, the right sibling page is released, and therefore the index tuple pointing to the right sibling page in the parent page of the right sibling page is deleted.

[0095] For example, such as Figure 3 As shown, for the current location page (node ​​D), all records of the right sibling page (node ​​E) are migrated to the current location page, the right sibling page is released, and the index tuple pointing to the right sibling page in the parent page (node ​​C) of the right sibling page is deleted.

[0096] S270. Move the cursor of the B+ tree to the right sibling page of the currently located page in the leaf node linked list of the B+ tree, and save the key value of the last record in the right sibling page currently pointed to by the cursor. The key value is used to locate the next page.

[0097] Specifically, after the record migration, the current positioned page can no longer accommodate the first record in the right sibling page, representing the most compact data storage state, thus resolving the fragmentation problem. Then, the cursor is moved right to the right sibling page of the current positioned page in the leaf node list, continuing to eliminate free space in the right sibling page, ultimately resolving the fragmentation problem of all leaf pages. Each time a page is reorganized, the changes are flushed to disk, and all exclusive locks on the B+ tree are released, allowing concurrent queries and DML operations on that path in the B+ tree to execute normally. To avoid performance waste caused by scanning the B+ tree starting from the leftmost page of the leaf level each time, the key value of the current cursor is saved before releasing the locks. Records with a key value smaller than the current key value are always on the left side of the cursor and have already been reorganized. After repositioning, it is only necessary to locate the next positioned page based on the saved key value and continue reorganizing the index to the right.

[0098] It should be noted that, as Figure 3 As shown, for the currently positioned page (node ​​D), if some records of the right sibling page (node ​​E) of the currently positioned page are migrated to the currently positioned page, the cursor moves to the right sibling page (node ​​E) of the currently positioned page and records the key value of the last record in the current right sibling page (node ​​E). For the currently positioned page (node ​​D), if all records of the right sibling page (node ​​E) of the currently positioned page are migrated to the currently positioned page, the right sibling page (node ​​E) is released, the cursor moves to the current right sibling page (node ​​G) of the currently positioned page and records the key value of the last record in the current right sibling page (node ​​G).

[0099] S280, Release all exclusive locks on the B+ tree.

[0100] The technical solution of this invention involves: determining the current location page in the leaf layer of the B+ tree of the database; acquiring exclusive locks on all data pages along the path from the root node of the B+ tree to the current location page, as well as on the sibling pages of the current location page; acquiring the right sibling page of the current location page; repeatedly copying the first record of the right sibling page to the end of the current location page until the current location page is full or all records of the right sibling page are copied to the current location page, and saving the number of records copied; if the number of records copied is less than the number of records in the right sibling page but not zero, deleting the copied records from the right sibling page and adjusting the structure of the B+ tree; if the number of records copied is equal to the number of records in the right sibling page, releasing the right sibling page and adjusting the structure of the B+ tree; moving the cursor of the B+ tree to the right sibling page of the current location page in the leaf node linked list of the B+ tree, and saving the key value of the last record in the right sibling page currently pointed to by the cursor, the key value being used to locate the next location page; and releasing all exclusive locks on the B+ tree. By copying as many records as possible from the right sibling page to the current location page, index data page fragmentation is reorganized, eliminating wasted space in the index B+ tree, making records stored compactly, solving the fragmentation problem of the current location page, maintaining the data order of the B+ tree, and improving index read and write efficiency. Furthermore, during index reorganization, exclusive locks are not applied to the entire data table; only the data pages from the root node to the current location page and the sibling pages of the current location page are locked. This allows concurrent transactions to query and perform DML operations on index pages without exclusive locks, reducing the impact on business logic. The performance of index reorganization and concurrent DML operations is superior. Compared to existing offline or online reorganization methods, this approach has significant advantages, improving the high concurrency and high availability capabilities of the database system and enhancing overall system performance.

[0101] Example 3

[0102] Figure 4 This is a schematic diagram of an online database reorganization device provided in Embodiment 3 of the present invention. Figure 4 As shown, the device includes: a page positioning module 310, a locking module 320, a right sibling page acquisition module 330, and a reassembly module 340; wherein:

[0103] The page location determination module 310 is used to determine the current page located in the leaf layer of the B+ tree of the database;

[0104] The locking module 320 is used to apply exclusive locks to all data pages on the path from the root node of the B+ tree to the current location page, as well as to the sibling pages of the current location page.

[0105] The right sibling page acquisition module 330 is used to acquire the right sibling page of the currently positioned page;

[0106] The reorganization module 340 is used to copy the records of the right sibling page sequentially to the end of the current positioning page until the current positioning page is full or all the records of the right sibling page are copied to the current positioning page, adjust the structure of the B+ tree and release all exclusive locks of the B+ tree.

[0107] Optionally, the positioning page determination module 310 is specifically used for:

[0108] Create a cursor for the B+ tree, traverse the B+ tree from the root node to the leftmost page of the leaf layer based on the cursor, and determine the leftmost page as the first currently positioned page;

[0109] Locate the non-first currently located page in the B+ tree based on the saved key value, where the saved key value is the key value of the last record in the right sibling page of the previous located page.

[0110] Optionally, the device may also include:

[0111] The cursor movement module is used to move the cursor of the B+ tree to the right sibling page of the currently located page in the leaf node linked list of the B+ tree after adjusting the structure of the B+ tree and before releasing all exclusive locks of the B+ tree.

[0112] The key value storage module is used to store the key value of the last record in the right sibling page currently pointed to by the cursor, and the key value is used to locate the next positioning page.

[0113] Optionally, the recombination module 340 includes:

[0114] The record copying unit is used to repeatedly copy the first record of the right sibling page to the end of the current positioning page until the current positioning page is full or all records of the right sibling page are copied to the current positioning page, and to save the record copy count;

[0115] The first adjustment unit is used to delete the copied records from the right sibling page and adjust the structure of the B+ tree when the number of copied records is less than the number of records in the right sibling page and is not zero.

[0116] The second adjustment unit is used to release the right sibling page and adjust the structure of the B+ tree when the number of record copies is equal to the number of records in the right sibling page and is not zero.

[0117] Optionally, the first adjustment unit is specifically used for:

[0118] Modify the key value of the index tuple in the parent page of the right sibling page to the key value of the first record of the right sibling page after the record is deleted.

[0119] Optional, the second adjustment unit is specifically used for:

[0120] Delete the index tuple that points to the right sibling page from the parent page of the right sibling page.

[0121] The online database reorganization apparatus provided in this embodiment of the invention can execute the online database reorganization method provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the method.

[0122] Example 4

[0123] Figure 5 A schematic diagram of an electronic device 10 that can be used to implement embodiments of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (e.g., helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

[0124] like Figure 5 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer program stored in the ROM 12 or loaded from storage unit 18 into the RAM 13. The RAM 13 may also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0125] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0126] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as online database reorganization methods.

[0127] In some embodiments, the online database reorganization method may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the online database reorganization method described above may be performed. Alternatively, in other embodiments, processor 11 may be configured to perform the online database reorganization method by any other suitable means (e.g., by means of firmware).

[0128] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0129] In some embodiments, the online database reorganization method can be implemented as a computer program, which is implicitly included in a computer program product. When executed by a processor, the computer program implements the online database reorganization method of the present invention. The computer program product can be understood as a software product that primarily implements its solution through a computer program. The computer program used to implement the method of the present invention can be written in any combination of one or more programming languages. These computer programs can be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer program causes the functions / operations specified in the flowcharts and / or block diagrams to be implemented. The computer program can be executed entirely on a machine, partially on a machine, as a standalone software package partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0130] In the context of this invention, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0131] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0132] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or computing systems that include middleware components (e.g., application servers), or computing systems that include frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0133] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.

[0134] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0135] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A method for online database reorganization, characterized in that, include: Determine the current page located at the leaf level in the B+ tree of the database; Exclusive locks are applied to all data pages along the path from the root node of the B+ tree to the currently located page, as well as to the sibling pages of the currently located page. Get the right sibling page of the currently located page; The records of the right sibling page are copied sequentially to the end of the current positioning page until the current positioning page is full or all the records of the right sibling page are copied to the current positioning page. The structure of the B+ tree is then adjusted and all exclusive locks of the B+ tree are released.

2. The method according to claim 1, characterized in that, Determining the current location page in the leaf level of the B+ tree of the database includes: Create a cursor for the B+ tree, and based on the cursor, traverse from the root node of the B+ tree to the leftmost page of the leaf layer of the B+ tree, and determine the leftmost page as the first currently positioned page; Locate the non-first currently located page in the B+ tree based on the saved key value, where the saved key value is the key value of the last record in the right sibling page of the previous located page.

3. The method according to claim 2, characterized in that, After adjusting the structure of the B+ tree and before releasing all exclusive locks on the B+ tree, the process also includes: Move the cursor of the B+ tree to the right sibling page of the currently located page in the leaf node linked list of the B+ tree; Save the key value of the last record in the right sibling page that the cursor is currently pointing to. The key value is used to locate the next page.

4. The method according to claim 1, characterized in that, The step of copying the records of the right sibling page sequentially to the end of the current positioning page until the current positioning page is full or all the records of the right sibling page have been copied to the current positioning page, adjusting the structure of the B+ tree, and releasing all exclusive locks on the B+ tree includes: The first record of the right sibling page is copied to the end of the current positioning page in a loop until the current positioning page is full or all records of the right sibling page are copied to the current positioning page. The number of records copied is then saved. If the number of records copied is less than the number of records in the right sibling page and is not zero, then the copied records are deleted from the right sibling page and the structure of the B+ tree is adjusted. If the number of record copies is equal to the number of records in the right sibling page and is not zero, then the right sibling page is released and the structure of the B+ tree is adjusted.

5. The method according to claim 4, characterized in that, When the number of record copies is less than the number of records in the right sibling page and is not zero, adjusting the structure of the B+ tree includes: Modify the key value of the index tuple in the parent page of the right sibling page to the key value of the first record of the right sibling page after the record is deleted.

6. The method according to claim 4, characterized in that, When the number of record copies equals the number of records in the right sibling page, adjusting the structure of the B+ tree includes: Delete the index tuple that points to the right sibling page from the parent page of the right sibling page.

7. An online database reorganization device, characterized in that, include: The page location determination module is used to determine the current page located in the leaf layer of the B+ tree in the database; The locking module is used to apply exclusive locks to all data pages on the path from the root node of the B+ tree to the currently located page, as well as to the sibling pages of the currently located page. The right sibling page acquisition module is used to acquire the right sibling page of the currently positioned page; The reorganization module is used to copy the records of the right sibling page sequentially to the end of the current positioning page until the current positioning page is full or all the records of the right sibling page are copied to the current positioning page, adjust the structure of the B+ tree, and release all exclusive locks of the B+ tree.

8. An electronic device, characterized in that, The electronic device includes: At least one processor; and a memory communicatively connected to the at least one processor; The memory stores a computer program executable by the at least one processor, which is then executed by the at least one processor to enable the at least one processor to perform the online reorganization method of the database according to any one of claims 1-6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that, when executed by a processor, implement the online reorganization method of the database according to any one of claims 1-6.

10. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the online reorganization method of the database according to any one of claims 1-6.

Images