Database file recombination method and device and medium

Abstract

The embodiment of the invention discloses a file reorganization method and device for a database and a medium, relates to the technical field of data processing, and is used for solving the problems that an existing data file reorganization mode is low in efficiency and large in system influence. The method comprises the steps of obtaining structure information of a source database table to create an initial target database table corresponding to the source database table; copying data of the source database table to the initial target database table to obtain a target database table; updating the target database table based on the change content of the source database table to obtain an updated target database table; applying for an access exclusive lock of the source database table based on the current database session, and performing table exchange on the source database table and the updated target database table based on the access exclusive lock to obtain an available target database table; wherein the locking time for accessing the exclusive lock is shorter than a preset locking time period.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a method, device and medium for reorganizing files in a database. Background Art

[0002] After a database has been running for a long time, due to the continuous use of the database, especially the frequent addition, deletion and modification operations, the data structure in the database may become messy, resulting in performance degradation. In addition, due to data fragmentation, index failure, uneven storage space allocation, performance requirements, security and integrity considerations, and the promotion of technological development, the relevant data files and index files in the database are reorganized according to a better structure and layout to improve database performance, reduce fragmentation, and optimize storage space utilization. Database file reorganization has become an important means to improve database performance.

[0003] Among the file reorganization methods of traditional databases, one is to operate directly on the original table, freeing up space by reorganizing storage and deleting dead tuples. Although this method can optimize the storage and performance of the table, it requires a long-term exclusive lock on the entire table. Therefore, when executing it in a production environment, especially for large tables and high-concurrency systems, it is necessary to carefully consider its possible impact. The second method is to allow read and write operations on the table throughout the process, but triggers are required to monitor and record data changes to ensure that the data in the new table is consistent with the original table after the initial data copy is completed. The disadvantage of this method is that triggers are required to monitor and record data changes, which has a greater impact on the overall performance of the system. Summary of the invention

[0004] In order to solve the above technical problems, one or more embodiments of the present specification provide a method, device and medium for reorganizing files in a database.

[0005] One or more embodiments of this specification adopt the following technical solutions:

[0006] One or more embodiments of the present specification provide a method for reorganizing files in a database, the method comprising:

[0007] Establishing a database session to obtain structural information of a source database table, and creating an initial target database table corresponding to the source database table based on the structural information;

[0008] Copying the data of the source database table to the initial target database table to obtain a target database table;

[0009] Recording the changed content of the logical log of the source database table, so as to update the target database table based on the changed content and obtain an updated target database table;

[0010] Applying for an access exclusive lock for the source database table based on the current database session, so as to exchange the source database table with the updated target database table based on the access exclusive lock to obtain an available target database table; wherein the lock time of the access exclusive lock is less than a preset lock time period.

[0011] Optionally, in one or more embodiments of the present specification, establishing a database session to obtain structural information of a source database table, and creating an initial target database table corresponding to the source database table based on the structural information, specifically includes:

[0012] Acquire the permission information of the current user to establish a database session between the current user and the source database table based on the permission information and the connection parameters of the source database table;

[0013] Executing an SQL statement based on the database session to obtain structural information of the source database table based on the SQL statement; wherein the structural information at least includes: structural definition information, attribute information, and additional setting information;

[0014] Determine the current state of the source database table based on the cache table lock and memory mirror of the source database table;

[0015] If the current state is that there is no exclusive access lock, an initial target database table corresponding to the source database table is created based on the structure definition information, the attribute information and the additional setting information.

[0016] Optionally, in one or more embodiments of the present specification, before establishing a database session to obtain structural information of a source database table, the method further includes:

[0017] Based on the preset statistical process, basic data information of each database table in the current database is obtained; wherein the basic data information includes: data volume and dead tuple data;

[0018] Determine the proportion of dead tuple data in each database table according to the data volume and the dead tuple data;

[0019] If the data volume is greater than a preset data volume value or the dead tuple data ratio is greater than a preset dead tuple data ratio value, it is determined that the database table is a source database table.

[0020] Optionally, in one or more embodiments of the present specification, copying the data of the source database table to the initial target database table to obtain the target database table specifically includes:

[0021] Determine the primary key range of the source database table and the timestamp of each data, so as to group the data of the source database table based on the primary key range and the preset time range to obtain multiple groups of data;

[0022] Read in each group of data in batches to obtain the frequency of occurrence of each data in each group of data, take each data as a child node and sort it based on the frequency of occurrence to obtain a child node priority sequence;

[0023] Acquire the child node pair with the lowest frequency of occurrence to merge the child node pair into a parent node, and update the child node priority sequence based on the parent node to iteratively acquire the root node corresponding to each group of data;

[0024] Traversing each data based on the root node to obtain the Huffman code corresponding to each data, replacing the corresponding data in each group of data based on the Huffman code, obtaining multiple groups of data to be copied and recording the corresponding relationship between each group of data to be copied and the Huffman code;

[0025] Copying the plurality of groups of to-be-copied data to the initial target database table in batches, and transmitting the corresponding relationship between the to-be-copied data and the Huffman codes to the initial target database table;

[0026] The multiple groups of data in the source database table restored for the multiple groups of data to be copied are restored based on the corresponding relationship between the data to be copied and the Huffman code, and filled into the initial target database table to obtain a target database table.

[0027] Optionally, in one or more embodiments of the present specification, the change content of the logical log of the source database table is recorded, so as to update the target database table based on the change content to obtain an updated target database table.

[0028] Monitor the change events of the source data table, capture the data operation records of each change event, and obtain the change content of the logical log;

[0029] Based on the logical change sequence of each of the change events, the change contents are sorted to obtain a logical data update flow corresponding to the source data table;

[0030] Based on the logical data update flow, the changed contents are sequentially applied to the target database table to obtain an updated target database table, and a consistency check is performed between the updated target database table and the source database table.

[0031] Optionally, in one or more embodiments of the present specification, applying for an exclusive access lock for the source database table based on the current database session specifically includes:

[0032] Determine the server type corresponding to the current database session, and apply for an exclusive access lock to the source database table by calling a corresponding command statement based on the server type;

[0033] Determine whether the exclusive access lock of the source database table has a lock conflict based on the application, and if so, monitor and obtain the lock status of the source database table;

[0034] A lock release operation of the lock conflict event is determined based on the lock state, so as to obtain an exclusive access lock of the source database table in the shared memory of the source database table.

[0035] Optionally, in one or more embodiments of the present specification, performing a table exchange between the source database table and the updated target database table based on the access exclusive lock to obtain an available target database table specifically includes:

[0036] If it is determined that an access exclusive lock is set between the source database table and the target database table, determining a first file identifier corresponding to the source database table based on database table metadata in the source database table;

[0037] exchanging the first file identifier with the second file identifier of the updated target database table to obtain an available target database table;

[0038] After obtaining the available target database table, the method further includes:

[0039] The index and constraint of the available target database table are created based on the structural information, and the consistency of the index and constraint of the available target database table is verified.

[0040] Optionally, in one or more embodiments of the present specification, after performing a table exchange between the source database table and the updated target database table based on the access exclusive lock to obtain an available target database table, the method further includes:

[0041] Determine the data type of each file data in the source database table, and clean up the file data based on an operation command corresponding to the data type; wherein the file data includes: memory cache, data file, visibility file and free space file.

[0042] One or more embodiments of the present specification provide a database file reorganization device, the device comprising:

[0043] at least one processor; and,

[0044] a memory communicatively connected to the at least one processor; wherein,

[0045] The memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to:

[0046] Establishing a database session to obtain structural information of a source database table, and creating an initial target database table corresponding to the source database table based on the structural information;

[0047] Copying the data of the source database table to the initial target database table to obtain a target database table;

[0048] Recording the changed content of the logical log of the source database table, so as to update the target database table based on the changed content and obtain an updated target database table;

[0049] Applying for an access exclusive lock for the source database table based on the current database session, so as to exchange the source database table with the updated target database table based on the access exclusive lock to obtain an available target database table; wherein the lock time of the access exclusive lock is less than a preset lock time period.

[0050] One or more embodiments of this specification provide a non-volatile computer storage medium storing computer executable instructions, wherein the computer executable instructions are configured to:

[0051] Establishing a database session to obtain structural information of a source database table, and creating an initial target database table corresponding to the source database table based on the structural information;

[0052] Copying the data of the source database table to the initial target database table to obtain a target database table;

[0053] Recording the changed content of the logical log of the source database table, so as to update the target database table based on the changed content and obtain an updated target database table;

[0054] Applying for an access exclusive lock for the source database table based on the current database session, so as to exchange the source database table with the updated target database table based on the access exclusive lock to obtain an available target database table; wherein the lock time of the access exclusive lock is less than a preset lock time period.

[0055] At least one of the above technical solutions adopted in the embodiments of this specification can achieve the following beneficial effects:

[0056] By creating an initial target database table corresponding to the source database table based on the structural information, it can be ensured that the structure of the target database table is completely consistent with the source table, and then by copying all the data of the source database table to the initial target database to obtain the target database table, it can be ensured that the data between the two tables are consistent. The target database table is updated based on the changed content. This method based on logical replication does not invade the original table, and does not need to create triggers on the original table to capture data changes, so that the original table can continue to perform normal read and write operations, ensuring the high availability and stable performance of the system. Table exchange through short-term access to exclusive locks ensures that even in a high-concurrency environment, the problem of database performance degradation due to long-term locking will not occur, and access to exclusive locks ensures that no other sessions will modify the data of the source table during the table exchange process, thereby protecting the consistency and integrity of the data, and monitoring the lock status and applying for the lock immediately after the lock is released. This mechanism can minimize the impact of lock conflicts on database operations and improve the response speed and efficiency of the system. BRIEF DESCRIPTION OF THE DRAWINGS

[0057] In order to more clearly illustrate the technical solutions in the embodiments of this specification or the prior art, the following briefly introduces the drawings required for use in the embodiments or the prior art description. Obviously, the drawings described below are only some embodiments recorded in this specification. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative labor. In the drawings:

[0058] Figure 1 A schematic diagram of a database file reorganization method provided in an embodiment of this specification;

[0059] Figure 2 A schematic diagram of the structure of a file reorganization device for a database provided in an embodiment of this specification;

[0060] Figure 3 A schematic diagram of the structure of a non-volatile storage medium provided in an embodiment of this specification. DETAILED DESCRIPTION

[0061] The embodiments of the present specification provide a method, device and medium for reorganizing files in a database.

[0062] In order to enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below in conjunction with the drawings in the embodiments of this specification. Obviously, the described embodiments are only part of the embodiments of this specification, not all of the embodiments. Based on the embodiments of this specification, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of this specification.

[0063] like Figure 1 As shown, the present specification embodiment provides a schematic diagram of a method for reorganizing files in a database. Figure 1 It can be seen that in one or more embodiments of this specification, a method for reorganizing files in a database includes:

[0064] S101: Establishing a database session to obtain structural information of a source database table, and creating an initial target database table corresponding to the source database table based on the structural information.

[0065] In order to achieve database table file reorganization without locking the table for a long time, the problem of normal database business being blocked due to long-term exclusive locking is solved. In the embodiment of this specification, the structural information of the source database table is first obtained according to the established database session, and an initial target database table with the same structure as the source database table is created according to the structural information. By directly obtaining the structural information from the source database table, it can be ensured that the structure of the target database table is completely consistent with the source table, including field names, data types, constraints, etc. This helps to maintain the consistency and accuracy of data during migration or replication.

[0066] Specifically, in one or more embodiments of the present specification, establishing a database session to obtain the structural information of a source database table, and creating an initial target database table corresponding to the source database table based on the structural information, specifically includes the following process:

[0067] In order to ensure that only users with sufficient permissions can access the source database table, protect the security of the database, and prevent unauthorized access and operation. The embodiment of this specification first obtains the permission information of the current user, and then establishes a database session between the current user and the source database table according to the user's permission information and the connection parameters of the source database table. Among them, it should be noted that the connection parameters include: the address of the source database server, the port number monitored by the source database server, the user name used to connect to the database, the password corresponding to the user name, the name of the source database, etc. In this process, by clearly specifying the connection parameters such as the address, port number, user name, password and database name of the source database server, it can be ensured that the database session can be established accurately and correctly, avoiding the connection failure problem caused by incorrect connection information. Then, an SQL statement is executed based on the database session, so as to obtain the structural information of the source database table with the help of the SQL statement. Among them, the structural information at least includes: attribute information such as: table name, column name, data type, constraint and other information, structure definition information such as: column definition, index definition, foreign key definition, etc., additional setting information such as: trigger, stored procedure and function, partition information, etc. Then, the current state of the source database table is determined based on the cache table lock and memory mirror of the source database table. If the current state is that there is no exclusive access lock, then the initial target database table corresponding to the source database table can be created based on the structure definition information, attribute information and additional setting information. Before creating the target database table, by checking the current state of the source database table, it can be ensured that the table structure is copied when the source database table is not exclusively accessed by other users or processes. This helps to avoid data inconsistency and concurrent access conflicts.

[0068] Furthermore, in one or more embodiments of the present specification, before establishing a database session to obtain the structure information of the source database table, the method further includes the following process:

[0069] First, the basic data information of each database table in the current database is obtained according to the preset statistical process. It should be noted that the basic data information includes: data volume and dead tuple data. Then, according to the ratio of data volume to dead tuple data, the proportion of dead tuple data in each database table is determined. If the data volume is greater than the preset data volume value or the proportion of dead tuple data is greater than the preset dead tuple data proportion value, then the database table can be determined to be a source database table. Determine which database tables need to be focused on based on the data volume and the proportion of dead tuple data. If a table has a large amount of data or a high proportion of dead tuple data, it may be a source database table that needs to be migrated, optimized or cleaned up. This intelligent screening mechanism helps reduce unnecessary operations and improve overall efficiency. In this process, only those tables that really need to be migrated or optimized are selected as source database tables, which can avoid introducing unnecessary complexity and errors in the migration process. This helps to improve the efficiency and success rate of database file reorganization.

[0070] S102: Copy the data of the source database table to the initial target database table to obtain a target database table.

[0071] After the initial target database table with the same structure as the source database table is constructed based on the above step S101, the target database table is obtained by copying all the data of the source database table to the initial target database, which can ensure that the data between the two tables is completely consistent. This is crucial for application scenarios that need to maintain data integrity and accuracy. In addition, in a distributed database system, copying data to the target database table can also support a read-write separation architecture. This architecture reduces the burden on the main database by distributing read operations and write operations to different database instances, thereby improving the overall performance and scalability of the system.

[0072] Specifically, in one or more embodiments of the present specification, copying data from a source database table to an initial target database table to obtain a target database table specifically includes the following process:

[0073] First, determine the primary key range of the source database table and the timestamp of each data, so as to group the data of the source database table according to the primary key range and the preset time range, and obtain multiple groups of data. Then, in order to improve the rate of data replication and realize data compression, the embodiment of this specification reads each group of data in batches, so as to obtain the frequency of occurrence of each data in each group of data, and sorts each data as a child node according to the frequency of occurrence to obtain a child node priority sequence. Then, the two child nodes with the smallest frequency of occurrence are used as a child node pair, so as to merge the child node pair into a parent node, and update the child node priority sequence based on the parent node to iteratively obtain the root node corresponding to each group of data. Then, traverse each data through the path from the root node to the child node, obtain the Huffman code corresponding to each data, replace the corresponding data in each group of data based on the Huffman code, and obtain multiple groups of data to be copied. By calculating the frequency of occurrence of each data in each group of data and replacing the data based on the Huffman code, the storage space of the data can be effectively reduced. In order to realize the recovery of the data, it is also necessary to record the corresponding relationship between each data to be copied and the Huffman code. Then, multiple groups of data to be copied are copied to the initial target database table in batches, and the correspondence between the data to be copied and the Huffman code is transmitted to the initial target database table. After data compression, the size of the data to be copied is significantly reduced, thereby reducing the time required for data transmission and writing to the target database table. This is especially important for database tables with large amounts of data, and can significantly improve the overall rate of data replication. Multiple groups of data from the source database table restored for multiple groups of data to be copied based on the correspondence between the data to be copied and the Huffman code are filled into the initial target database table to obtain the target database table. In addition, by dividing multiple groups of data to be copied, multiple groups of data can be processed in parallel during the data replication process, thereby achieving a further improvement in data replication efficiency, which is particularly important for database file reorganization scenarios that require rapid migration of large amounts of data.

[0074] S103: Record the change content of the logic log of the source database table, so as to update the target database table based on the change content and obtain an updated target database table.

[0075] Since it takes a certain amount of time to copy the data of the source database table to the target database table, and during this time period, the source database table may have data changes due to the processing of transactions. Therefore, in order to ensure the consistency of the source database table and the target database table, and to avoid the problem that the trigger monitoring and recording of data changes in the existing method have a great impact on the overall performance of the database, the embodiment of this specification records the changes in the logical log of the source database table, so that the target database table is updated according to the changes, thereby obtaining the updated target database table. Compared with the traditional method of trigger monitoring and recording data changes, the method of recording the changes in the logical log and updating the target database table accordingly usually has a lower performance overhead. Because trigger monitoring will perform corresponding operations every time the data changes, this may increase the burden on the database; while recording the logical log is a relatively lightweight method, which can be processed asynchronously after the data changes, reducing the impact on the real-time performance of the database. Combining the batch copying of data in the above steps with the application change log of this step, it is possible to control resource usage in a more fine-grained manner and reduce the impact on system performance.

[0076] Specifically, in one or more embodiments of the present specification, recording the change content of the logical log of the source database table, thereby updating the target database table according to the change content, and obtaining the updated target database table, specifically includes the following process:

[0077] First, monitor the change events of the source data table, capture the data operation records of each change event, and obtain the change content of the logical log. Then, based on the logical change order of each change event, sort the change content to obtain the logical data update stream corresponding to the source data table. According to the obtained logical data update stream, apply the change content to the target database table in turn to obtain the updated target database table, and perform consistency check on the updated target database table and the source database table. For example, in a specific embodiment, first record the relevant data operations on the source database table after the initial data replication as a logical log change record. In this way, the data changes of the source database table are identified, and the data operation records of the source database table transactions are captured, combined into corresponding transaction operation logs, and converted into readable logical change records. The logical change records of the source database table are received, a logical data update stream is constructed, and the change log is applied to the target database table to ensure that the newly formed target database table is consistent with the data of the source database table. Using the logical replication method, there is no intrusion on the original table, and there is no need to create triggers on the original table to capture data changes. In this way, the original table can continue to perform normal read and write operations to ensure the high availability and stable performance of the system. Use the database's logical log to capture data changes and build a logical data update stream, and use internal mechanisms to decode the log and apply the changes. This ensures data consistency between the new table and the original table while minimizing the impact on the system.

[0078] In this process, by monitoring and recording the change events of the source data table, it is ensured that all changes are accurately captured and applied to the target database table, thereby ensuring the consistency of data between the two database tables. The synchronization method based on the change content avoids unnecessary data duplication and redundancy, and also reduces the possibility of data conflicts. The synchronization process is triggered only when the source data table actually changes, ensuring the accuracy and efficiency of the data. In addition, in this process, whether it is a simple data insertion, update or deletion operation, or a complex business logic change, it can be captured and processed by recording the logical log. This flexibility enables this method to adapt to various complex data synchronization requirements. And because the synchronization process is based on the change content of the logical log, it is not limited by the specific implementation or architecture of the source database and the target database. This enables this method to support synchronization operations between heterogeneous databases and improve the flexibility and scalability of the system.

[0079] S104: Applying for an access exclusive lock for the source database table based on the current database session, so as to exchange the source database table with the updated target database table based on the access exclusive lock to obtain an available target database table; wherein the lock time of the access exclusive lock is less than a preset lock time period.

[0080] In order to ensure that there will be no new data changes during the table exchange, in the embodiment of this specification, an access exclusive lock of the source database table is applied according to the current database session, so that after the original table access exclusive lock is briefly obtained in the shared memory, the source database table and the updated target database table are exchanged to obtain an available target database table. Through table exchange, that is, through the change of database table metadata information, the file identifier of the target database table file is exchanged to make the target database table an actual table. Traditional table structure changes may require complex downtime plans and data migration steps, and the access exclusive lock in this process ensures that no other session can modify the data of the source table during the entire process of table exchange. This avoids data inconsistencies during the table exchange process and ensures the integrity and accuracy of the data.

[0081] Specifically, in one or more embodiments of the present specification, applying for an exclusive access lock for a source database table based on a current database session specifically includes the following process:

[0082] First, determine the server type corresponding to the current database session, and then call the corresponding command statement according to the server type to apply for the access exclusive lock of the source database table. Then, determine whether the access exclusive lock of the source database table has a lock conflict based on the application. If there is a lock conflict, it is necessary to monitor the lock status of the source database table. Determine the lock release operation of the lock conflict event based on the lock status, so as to obtain the access exclusive lock of the source database table in the shared memory of the source database table after the lock is released; it should be noted that the access exclusive lock is a short lock, so the lock time of the access exclusive lock is less than the preset lock time period. In this process, the access exclusive lock ensures that no other session will modify the data of the source table during the table exchange process, thereby protecting the consistency and integrity of the data, and the lock status is monitored and the lock is applied immediately after the lock is released. This mechanism can minimize the impact of lock conflicts on database operations and improve the response speed and efficiency of the system. In addition, the short-term locking strategy ensures that even in a high-concurrency environment, the problem of database performance degradation due to long-term locking will not be guaranteed, which ensures the normal operation of various businesses that need the database.

[0083] Further, in one or more embodiments of the present specification, based on the access exclusive lock, the source database table and the updated target database table are exchanged to obtain the available target database table, which specifically includes the following process:

[0084] If it is determined that an access exclusive lock is set between the source database table and the target database table, then the first file identifier corresponding to the source database table is determined based on the database table metadata in the source database table. By using the access exclusive lock, it can be ensured that during the table exchange process, the source database table and the target database table will not be accessed or modified by other concurrent transactions, thereby effectively avoiding the problem of data inconsistency. The first file identifier is exchanged with the second file identifier of the updated target database table to obtain an available target database table. Directly exchanging the file identifiers of the two tables, for example, may be the physical path or identifier of the table file in some database systems. Compared with traditional data copying and deletion operations, this method can greatly reduce the time and resources required for data migration. Because the actual data content is not moved, only the structure and reference of the table are relocated, which greatly improves the efficiency of the operation. By using table exchange technology, data migration and table updates can be completed with almost no service interruption, thereby greatly reducing downtime and improving system availability and user experience.

[0085] In addition, in order to ensure data consistency, after obtaining the available target database table, the method further includes: creating indexes and constraints of the available target database table based on the structural information, and verifying the consistency of the indexes and constraints of the available target database table. After the table is exchanged, the indexes and constraints of the available target database table are created and verified based on the structural information, which can ensure that the new table is logically consistent with the source table. This step not only enhances the integrity of the data, but also facilitates possible data recovery operations. If any problems are found, it can be quickly rolled back to the state before the exchange, ensuring the reversibility of the operation and the recoverability of the data.

[0086] Furthermore, in one or more embodiments of the present specification, after performing a table exchange between the source database table and the updated target database table based on the access exclusive lock to obtain an available target database table, the method further includes:

[0087] Determine the data type of each file data in the source database table, and clean up the file data based on the operation command corresponding to the data type; it should be noted that the file data includes: memory cache, data file, visibility file and free space file. In this process, after creating a new table and copying the data, all the space of the original table can be completely reclaimed through the cleanup process to ensure maximum space utilization. In addition, operation logs can be recorded for auditing and tracking when necessary, and the performance of the new table can be optimized and statistical information can be updated. Recording detailed operation logs during the operation process facilitates auditing and tracking, and improves the transparency and traceability of operations.

[0088] like Figure 2 As shown, the present specification embodiment provides a schematic diagram of the structure of a database file reorganization device. Figure 2 It can be seen that in one or more embodiments of this specification, a file reorganization device for a database includes:

[0089] at least one processor; and,

[0090] a memory communicatively connected to the at least one processor; wherein,

[0091] The memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to:

[0092] Establishing a database session to obtain structural information of a source database table, and creating an initial target database table corresponding to the source database table based on the structural information;

[0093] Copying the data of the source database table to the initial target database table to obtain a target database table;

[0094] Recording the changed content of the logical log of the source database table, so as to update the target database table based on the changed content and obtain an updated target database table;

[0095] Applying for an access exclusive lock for the source database table based on the current database session, so as to exchange the source database table with the updated target database table based on the access exclusive lock to obtain an available target database table; wherein the lock time of the access exclusive lock is less than a preset lock time period.

[0096] like Figure 3 As shown, the present specification provides a schematic diagram of the structure of a non-volatile storage medium. Figure 3 It can be seen that in one or more embodiments of this specification, a non-volatile storage medium stores computer executable instructions 301, and the computer executable instructions 301 can:

[0097] Establishing a database session to obtain structural information of a source database table, and creating an initial target database table corresponding to the source database table based on the structural information;

[0098] Copying the data of the source database table to the initial target database table to obtain a target database table;

[0099] Recording the changed content of the logical log of the source database table, so as to update the target database table based on the changed content and obtain an updated target database table;

[0100] Applying for an access exclusive lock for the source database table based on the current database session, so as to exchange the source database table with the updated target database table based on the access exclusive lock to obtain an available target database table; wherein the lock time of the access exclusive lock is less than a preset lock time period.

[0101] Each embodiment in this specification is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the device, equipment, and non-volatile computer storage medium embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0102] The above is a description of a specific embodiment of the specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recorded in the claims can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the processes depicted in the drawings do not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0103] The above description is only one or more embodiments of this specification and is not intended to limit this specification. For those skilled in the art, one or more embodiments of this specification may have various changes and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of one or more embodiments of this specification shall be included in the scope of the claims of this specification.

Claims

1. A method for reorganizing files in a database, characterized in that: The method comprises: Establishing a database session to obtain structural information of a source database table, and creating an initial target database table corresponding to the source database table based on the structural information; Copying the data of the source database table to the initial target database table to obtain a target database table; Recording the changed content of the logical log of the source database table, so as to update the target database table based on the changed content and obtain an updated target database table; Applying for an access exclusive lock for the source database table based on the current database session, so as to exchange the source database table with the updated target database table based on the access exclusive lock to obtain an available target database table; wherein the lock time of the access exclusive lock is less than a preset lock time period.

2. A database file reorganization method according to claim 1, characterized in that: The step of establishing a database session to obtain structural information of a source database table and creating an initial target database table corresponding to the source database table based on the structural information specifically includes: Acquire the permission information of the current user to establish a database session between the current user and the source database table based on the permission information and the connection parameters of the source database table; Executing an SQL statement based on the database session to obtain structural information of the source database table based on the SQL statement; wherein the structural information at least includes: structural definition information, attribute information, and additional setting information; Determine the current state of the source database table based on the cache table lock and memory mirror of the source database table; If the current state is that there is no exclusive access lock, an initial target database table corresponding to the source database table is created based on the structure definition information, the attribute information and the additional setting information.

3. A database file reorganization method according to claim 1, characterized in that: Before establishing a database session to obtain the structural information of the source database table, the method further includes: Based on the preset statistical process, basic data information of each database table in the current database is obtained; wherein the basic data information includes: data volume and dead tuple data; Determine the proportion of dead tuple data in each database table according to the data volume and the dead tuple data; If the data volume is greater than a preset data volume value or the dead tuple data ratio is greater than a preset dead tuple data ratio value, it is determined that the database table is a source database table.

4. A database file reorganization method according to claim 1, characterized in that: The step of copying the data of the source database table to the initial target database table to obtain the target database table specifically includes: Determine the primary key range of the source database table and the timestamp of each data, so as to group the data of the source database table based on the primary key range and the preset time range to obtain multiple groups of data; Read in each group of data in batches to obtain the frequency of occurrence of each data in each group of data, take each data as a child node and sort it based on the frequency of occurrence to obtain a child node priority sequence; Acquire the child node pair with the lowest frequency of occurrence to merge the child node pair into a parent node, and update the child node priority sequence based on the parent node to iteratively acquire the root node corresponding to each group of data; Traversing each data based on the root node to obtain the Huffman code corresponding to each data, replacing the corresponding data in each group of data based on the Huffman code, obtaining multiple groups of data to be copied and recording the corresponding relationship between each group of data to be copied and the Huffman code; Copying the plurality of groups of to-be-copied data to the initial target database table in batches, and transmitting the corresponding relationship between the to-be-copied data and the Huffman codes to the initial target database table; The multiple groups of data in the source database table restored for the multiple groups of data to be copied are restored based on the corresponding relationship between the data to be copied and the Huffman code, and filled into the initial target database table to obtain a target database table.

5. The method for reorganizing a database file according to claim 1, characterized in that: The recording of the change content of the logical log of the source database table to update the target database table based on the change content to obtain the updated target database table specifically includes: Monitor the change events of the source data table, capture the data operation records of each change event, and obtain the change content of the logical log; Based on the logical change sequence of each of the change events, the change contents are sorted to obtain a logical data update flow corresponding to the source data table; Based on the logical data update flow, the changed contents are sequentially applied to the target database table to obtain an updated target database table, and a consistency check is performed between the updated target database table and the source database table.

6. A database file reorganization method according to claim 1, characterized in that: The applying for an exclusive access lock to the source database table based on the current database session specifically includes: Determine the server type corresponding to the current database session, and apply for an exclusive access lock to the source database table by calling a corresponding command statement based on the server type; Determine whether the exclusive access lock of the source database table has a lock conflict based on the application, and if so, monitor and obtain the lock status of the source database table; A lock release operation of the lock conflict event is determined based on the lock state, so as to obtain an exclusive access lock of the source database table in the shared memory of the source database table.

7. A database file reorganization method according to claim 1, characterized in that: The step of performing table exchange on the source database table and the updated target database table based on the access exclusive lock to obtain an available target database table specifically includes: If it is determined that an access exclusive lock is set between the source database table and the target database table, determining a first file identifier corresponding to the source database table based on database table metadata in the source database table; exchanging the first file identifier with the second file identifier of the updated target database table to obtain an available target database table; After obtaining the available target database table, the method further includes: The index and constraint of the available target database table are created based on the structural information, and the consistency of the index and constraint of the available target database table is verified.

8. The method for reorganizing a database file according to claim 1, characterized in that: After performing table exchange between the source database table and the updated target database table based on the access exclusive lock to obtain an available target database table, the method further includes: Determine the data type of each file data in the source database table, and clean up the file data based on an operation command corresponding to the data type; wherein the file data includes: memory cache, data file, visibility file and free space file.

9. A file reorganization device for a database, characterized in that: The device comprises: at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to: Establishing a database session to obtain structural information of a source database table, and creating an initial target database table corresponding to the source database table based on the structural information; Copying the data of the source database table to the initial target database table to obtain a target database table; Recording the changed content of the logical log of the source database table, so as to update the target database table based on the changed content and obtain an updated target database table; Applying for an access exclusive lock for the source database table based on the current database session, so as to exchange the source database table with the updated target database table based on the access exclusive lock to obtain an available target database table; wherein the lock time of the access exclusive lock is less than a preset lock time period.

10. A non-volatile storage medium storing computer executable instructions, characterized in that: The computer executable instructions are capable of: Establishing a database session to obtain structural information of a source database table, and creating an initial target database table corresponding to the source database table based on the structural information; Copying the data of the source database table to the initial target database table to obtain a target database table; Recording the changed content of the logical log of the source database table, so as to update the target database table based on the changed content and obtain an updated target database table; Applying for an access exclusive lock for the source database table based on the current database session, so as to exchange the source database table with the updated target database table based on the access exclusive lock to obtain an available target database table; wherein the lock time of the access exclusive lock is less than a preset lock time period.

Images