Large object rewriting method of database, storage medium and equipment

Abstract

The invention provides a large object rewriting method of a database, a storage medium and equipment. The large object rewriting method of the database comprises the steps that an event for rewriting data in a large object column in the database is obtained, and the large object column is used for storing large object data items of a large object data type; a rewriting sub-task queue and a rewriting process group are established, the rewriting sub-task queue is used for storing a plurality of rewriting sub-tasks divided based on rewriting events, and the rewriting process group comprises a plurality of rewriting processes running in parallel; and allocating the rewriting sub-tasks in the rewriting sub-task queue to each rewriting process of the rewriting process group, and processing the allocated rewriting sub-tasks by each rewriting process. The method has the advantages that the rewriting speed can be remarkably increased, and the locking time is shortened. Therefore, the load of the database system is reduced, the concurrency performance of the system is improved, and the requirements of daily services are better met.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular to a method, storage medium and device for rewriting large objects of a database. Background Art

[0002] Large Object is a special type of data in the database, mainly including Binary Large Object (BLOB) and Character Large Object / National Character Large Object (CLOB / NCLOB). BLOB is used to store binary large object data, such as images, audio files, videos or other non-text format data; CLOB and NCLOB are used to store large amounts of character data, suitable for storing long texts such as articles, reports or XML documents. The upper limit of the volume of a single data item of this type of data is very high, up to GB or even TB, and the volume varies greatly. The small amount of data can be as small as a few bytes or even empty.

[0003] In existing database systems, since large objects may occupy a large amount of storage space, rewriting operations usually involve a large number of data reading, processing, and writing steps, which are often very time-consuming. When rewriting large objects, it is usually necessary to lock related data rows or tables to prevent data from being modified by other transactions during the rewriting process. However, long-term locking will block access to other transactions, resulting in a decrease in database concurrency performance and adverse effects on daily business. Summary of the invention

[0004] An object of the first aspect of the present invention is to increase the speed of rewriting large objects in a database.

[0005] Another object of the first aspect of the present invention is to achieve efficient and flexible rewriting of large objects.

[0006] Another object of the first aspect of the present invention is to reasonably set the number of rewriting processes to avoid wasting resources.

[0007] In particular, according to a first aspect of the present invention, the present invention provides a method for rewriting a large object of a database, comprising:

[0008] Acquire an event of rewriting data in a large object column in the database, wherein the large object column is used to store large object data items of a large object data type;

[0009] Establishing a rewriting subtask queue and a rewriting process group, wherein the rewriting subtask queue is used to store a plurality of rewriting subtasks divided based on the rewriting events, and the rewriting process group includes a plurality of rewriting processes running in parallel;

[0010] The rewriting subtasks in the rewriting subtask queue are allocated to the respective rewriting processes of the rewriting process group, and each of the rewriting processes processes the rewriting subtask allocated thereto.

[0011] Optionally, the object type of the large object data item includes an in-row object, metadata and data body of the in-row object are stored in a data table row of the database, and the steps of each rewriting process processing the rewriting subtask assigned thereto include:

[0012] Each of the rewriting processes rewrites the inline objects in the rewriting subtask.

[0013] Optionally, the object type of the large object data item includes an out-of-row object, part of the metadata of the out-of-row object is stored in the row of the data table of the database, and the remaining metadata and the data body are stored outside the row of the data table of the database, and the steps of each rewriting process processing the rewriting subtask assigned to it include:

[0014] Each of the rewriting processes writes the object identifier of the out-of-row object in the rewriting subtask into the out-of-row object temporary table pre-created by the database;

[0015] The temporary table of out-of-row objects is scanned, and the scanned out-of-row objects are rewritten.

[0016] Optionally, the steps of establishing the rewriting process group include:

[0017] Obtaining the queue length of the rewriting subtask queue and the parallelism parameter of the database rewriting task;

[0018] Determine whether the queue length of the rewriting subtask queue is greater than the parallelism parameter of the database rewriting task;

[0019] If so, create a rewriting process group whose number of processes is equal to the parallelism parameter value of the database rewriting task;

[0020] If not, create a rewriting process group whose number of processes is equal to the queue length value of the rewriting subtask queue.

[0021] Optionally, the steps of establishing a rewriting subtask queue include:

[0022] Initialize an empty queue in memory as the rewriting subtask queue;

[0023] Dividing the rewriting task of the large object column into a plurality of rewriting subtasks according to the partitioning condition of the large object column;

[0024] Adding a plurality of the rewriting subtasks to the rewriting subtask queue in sequence.

[0025] Optionally, the step of dividing the rewriting task of the large object column into a plurality of rewriting subtasks according to the partitioning condition of the large object column includes:

[0026] Allocating one rewriting subtask to each of the unpartitioned large object columns;

[0027] One rewriting subtask is respectively assigned to each partitioned column partition of the large object column.

[0028] Optionally, the step of obtaining an event of rewriting data in a large object column in the database includes:

[0029] monitoring modification instructions for the large object column;

[0030] When the modification instruction is detected, an event of rewriting the data in the large object column is triggered.

[0031] Optionally, after the step of each rewriting process independently processing the rewriting subtask assigned to it, the method further includes:

[0032] Obtaining the completion status of all said rewriting processes;

[0033] When all the rewriting processes are completed, it is reported that the rewriting task of the large object column is completed.

[0034] According to a second aspect of the present invention, the present invention provides a machine-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements any of the above-mentioned large object rewriting methods for a database.

[0035] According to a third aspect of the present invention, the present invention provides a computer device comprising a memory, a processor, and a computer program stored in the memory and running on the processor, and when the processor executes the computer program, any one of the above-mentioned large object rewriting methods for a database is implemented.

[0036] The large object rewriting method of the database of the present invention can divide the rewriting task into multiple rewriting subtasks according to the event requirements of the rewriting, and put them into the rewriting subtask queue. A rewriting process group is established, and the subtasks in the rewriting subtask queue are allocated to each rewriting process of the rewriting process group. The allocation process can be dynamically adjusted according to the size and complexity of the subtask and the load of the rewriting process to ensure that each process can handle the task in a balanced manner. Due to the use of parallel processing and task division methods, the large object rewriting method of the database of the present invention can significantly improve the rewriting speed and reduce the locking time. This helps to reduce the load of the database system and improve the concurrent performance of the system, thereby better meeting the needs of daily business.

[0037] Furthermore, in the method for rewriting large objects in a database of the present invention, since all information of objects in a row is centrally stored in the row of the data base table, the rewriting process can quickly locate and modify these objects. Therefore, for objects in a row, the rewriting process will directly rewrite them. For objects outside the row, their object identifiers will be first recorded in the temporary table of objects outside the row, and then the temporary table of objects outside the row will be scanned, and the corresponding objects outside the row will be located and rewritten according to the scanned object identifiers. This processing method allows the rewriting process to flexibly operate on objects outside the row, while avoiding traversal of the entire database, thereby improving the efficiency of rewriting.

[0038] Furthermore, the database large object rewriting method of the present invention adopts a dynamic adjustment strategy based on the rewriting subtask queue length and the database rewriting task parallelism parameter when establishing the rewriting process group, aiming to reasonably allocate and create the rewriting process according to the current task load and system resource conditions to achieve a more efficient rewriting operation. Through this dynamic adjustment strategy, the rewriting process can be flexibly allocated according to the actual situation, ensuring that the system resources can be fully utilized when there are more tasks, and avoiding unnecessary resource occupation when there are fewer tasks. This helps to improve the overall efficiency of rewriting and reduce waiting time and resource waste.

[0039] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] Various other advantages and benefits will become apparent to those of ordinary skill in the art by reading the detailed description of the preferred embodiments below. The accompanying drawings are only for the purpose of illustrating the preferred embodiments and are not to be considered as limiting the present invention. Moreover, the same reference symbols are used throughout the accompanying drawings to represent the same components. In the accompanying drawings:

[0041] Figure 1 is a schematic diagram of a table structure in a data table having a large object column according to an embodiment of the present invention;

[0042] Figure 2 is a schematic diagram of a system table in a data table having a large object column according to an embodiment of the present invention;

[0043] Figure 3 is a schematic diagram of an optional large object control block in a data table having a large object column according to an embodiment of the present invention;

[0044] Figure 4 is a schematic diagram of another optional large object control block in a data table having a large object column according to an embodiment of the present invention;

[0045] Figure 5 is a schematic diagram of an object and a storage slice table in a data table having a large object column according to an embodiment of the present invention;

[0046] Figure 6 is a schematic diagram of storing slices referencing each other in a data table with a large object column according to an embodiment of the present invention;

[0047] Figure 7 is a flowchart of a method for rewriting a large object of a database according to an embodiment of the present invention;

[0048] Figure 8 is a flowchart of establishing a rewriting subtask queue in a large object rewriting method for a database according to an embodiment of the present invention;

[0049] Fig. 9 A flowchart of establishing a rewriting process group in a method for rewriting a large object in a database according to an embodiment of the present invention;

[0050] Fig.10 A schematic diagram of a computer readable storage medium according to an embodiment of the present invention;

[0051] Fig.11 Schematic diagram of a computer device according to one embodiment of the present invention. DETAILED DESCRIPTION

[0052] It should be understood by those skilled in the art that the embodiments described below are only some embodiments of the present invention, rather than all embodiments of the present invention, and these embodiments are intended to explain the technical principles of the present invention, rather than to limit the protection scope of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should still fall within the protection scope of the present invention.

[0053] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by an instruction execution system, device or equipment (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or equipment and execute instructions), or used in combination with these instruction execution systems, devices or equipment.

[0054] This embodiment provides a large object rewriting method for a database. Before describing the embodiment of the present application in detail, the database in this embodiment is first described to better understand the database environment on which this embodiment relies. The database environment not only supports the storage and operation of large objects, but also provides sufficient flexibility to perform complex rewriting tasks.

[0055] The database of this embodiment is established with a data base table, which can be a user base table and is created according to a creation statement. The data base table has at least one large object column, and at least part of the large object data items in the large object column are divided into multiple storage slices for storage. The data items in the large object column can be BLOB, CLOB / NCLOB or other types of variable-length binary data, character data, external data, etc. The data base table can be a user base table and is created according to a creation statement. The number of large object columns can be set according to business requirements, for example, set to one column, two columns, or more columns.

[0056] In the large object mode preset by the database, each large object column or each partition of the large object column is provided with a storage management table space, each storage management table space stores the object and storage slice table corresponding to the large object column or the large object column partition, and the object and storage slice table are used to store the description information and data of the storage slice.

[0057] In one example, the step of establishing a data base table includes: obtaining a creation instruction and generating a data base table according to the creation instruction, generating a column object identifier for each large object column or each partition of the large object column in the data base table; and naming the object and storage slice table according to the column object identifier to obtain a storage table object identifier. The creation instruction may use, for example, SQL (Structured Query Language). Taking a simple case as an example, assuming that a user has established a business table user_biz_data, which contains two large object columns col_x and col_y (and other regular data columns col_a and col_b) and the user has not set partitions for the large object columns, the creation statement may be:

[0058]

[0059] KLOB can be a large object data type defined in the database of this embodiment, which includes three proprietary base types: KBLOB, KCLOB, and KNCLOB, which correspond to the traditional BLOB, CLOB, and NCLOB types respectively. In the database of this embodiment, the existing Domain type mechanism can be used to set KBLOB, KCLOB, and KNCLOB as one of the replaceable implementations behind BLOB, CLOB, and NCLOB. This means that when the user specifies to use BLOB, CLOB, or NCLOB type when creating a table or defining a column, the proprietary base type KBLOB, KCLOB, or KNCLOB of this embodiment can actually be selected as an alternative.

[0060] The user creates a business table (base table) called user_biz_data, which contains two large object columns col_x and col_y that are not partitioned. This solution will automatically generate UUIDs (Universally Unique Identifiers) for these two large object columns.

[0061] The large object mode preset by the database can be a dedicated mode (Schema) created in the database. In some embodiments, the large object mode can be named sys_klob_schema, which defines the organization and structure of the database. For each large object column and its column partition in the user base table, the database system can establish a set of tables and indexes in the above Schema to store and manage large objects. The object itself will be divided into small storage slices (Chunks) and stored in these tables. It should be noted that the large object column can be partitioned as needed, and the large object column partition can be considered as a part of the large object column. Each large object column partition has an object and storage slice table. In some embodiments, when partitioning is not required, each large object column has an object and storage slice table.

[0062] For example, the sys_klob_sch Schema can set up matching object and storage slice tables for two unpartitioned large object columns col_x and col_y, named tab_colx_uuid and tab_coly_uuid respectively. The object and storage slice tables tab_colx_uuid and tab_coly_uuid generate globally unique identifiers using randomly generated UUIDs respectively. The format of the globally unique identifier can be a combination of a prefix (for example, the tab_ prefix mentioned above) and a UUID to meet the naming requirements of the database object name. The object record in the object and storage slice table is the first storage slice record of the object. In this embodiment, UUID is used to identify a large object column or a large object column partition, and data tables, indexes, and sequences can generate their own names by combining their own prefixes with UUIDs.

[0063] The large object column metadata table preset in the database system table records the description information of the object and the storage slice table. To manage large objects, the database system establishes a set of dedicated system tables to store the correspondence between the large object columns in the user table and these database objects, as well as the unique storage parameters of the large object columns. The large object column metadata table can be named sys_klob_column, which is one of a set of system tables configured for large objects in the database system.

[0064] The large object column metadata table sys_klob_column can also record the object identifier of the base table (used to identify the data base table, which can be named rel_oid and used when searching for metadata of the data base table) and the identifier of the large object column (which can be the column number of the large object column in the base table, used to locate the position of the large object column, which can be named attr_no, and can be used with rel_oid to uniquely identify a table column of the database, and is used when searching for table column metadata). In addition, the description information of the object and storage slice table recorded in the large object column metadata table includes the storage table object identifier (which can be named tab_oid, i.e., the OID of the above object and storage slice table. In the case of no partitioning, each large object column has a corresponding tab_oid. If the large object column is partitioned, each large object column partition has a corresponding tab_oid).

[0065] In the data base table, a large object control block is used to replace the position of the corresponding large object data item. The large object control block records at least the description information of the large object data item and the data item object identifier. That is, in the final data base table, the position of the large object data item is a control block. The control block can be set to different data structures according to the size of the large object data, and at least includes the description information of the large object data item and the data item object identifier. The description information of the large object data item is used to describe the type, various flags, version, etc. of the large object data item. The data item object identifier is a globally unique fixed identifier of the object, which is a UUID type identifier that is randomly generated when the object is created and remains unchanged throughout the life cycle of the object.

[0066] A set of system tables specially configured for large objects in the database system may also include: storage parameter history table (which may be named sys_klob_store_params), deduplication task history table (which may be named sys_klob_dedup_tasks), and in addition, the system metadata table in the database system table (which may be named pg_attribute) also records other basic attributes of large object columns that are consistent with other regular data types.

[0067] In some embodiments, the storage parameter history table sys_klob_store_params preset in the database system table records the storage table object identifier tab_oid and storage parameters of the object and the storage slice table, and these storage parameters may include storage parameter version number, storage options, encryption parameters, task identifiers, etc. The data in the storage parameter history table sys_klob_store_params can be used when the storage options such as compression and encryption of large objects are modified or when existing data needs to be rewritten.

[0068] In some embodiments, the deduplication task history table sys_klob_dedup_tasks preset in the database system table records the storage table object identifier tab_oid of the object and the storage slice table and the deduplication task information, wherein the deduplication task information includes the deduplication task sequence number and the deduplication task identifier, which is used to determine the execution status of the deduplication task.

[0069] The original system tables of the database system tables also store the metadata of the tables and indexes used for large object storage, which is basically consistent with other common data type metadata records and will not be elaborated here.

[0070] Each storage management tablespace also stores the primary key index of the object and storage slice table. The primary key index is used to search for large object data items and / or storage slices in the object and storage slice table; and the large object column metadata table also records the information of the primary key index. The primary key index can generally be hash indexed. The search efficiency is improved by establishing an index.

[0071] A reference column is also provided in the object and storage slice table. The reference column is used to record the reference relationship between the storage slice and other storage slices and / or the reference relationship between the large object data item and other large object data items. By utilizing these reference relationships, repeated storage of the same large object data item and / or storage slice can be avoided, saving storage space resources. Furthermore, each storage management table space can also store a reference column index of the object and storage slice table. The reference column index is used to query the reference relationship in the object and storage slice table; and the large object column metadata table also records the reference column index information. The storage efficiency of finding reference relationships is improved by establishing a reference column index.

[0072] Since rewriting data and deduplication of large objects when modifying storage options requires a lot of execution time, in order to minimize the impact on the user's daily business, the above operations are generally performed gradually in the background. In order to ensure the correct execution of the above operations, in some embodiments, a data rewriting task sequence (store_seq_<col / part uuid> ) and the deduplication task sequence (dedup_seq_<col / part uuid> ). The data rewriting task sequence is used to record the rewriting process of objects and storage slice tables, and the deduplication task sequence is used to record the deduplication process of objects and storage slice tables, so that these two sequences can be used to track the tasks of rewriting data and deduplication of large objects when modifying storage options.

[0073] In this embodiment, in order to achieve efficient storage and management of large objects, especially when processing TB-level large objects, a storage environment design based on tables and indexes is adopted. This method avoids complex transformation of the underlying storage implementation mechanism of the database, and instead sets a storage management table space for each large object column or each large object column partition in the large object mode preset in the database.

[0074] The following is an example of creating a business table user_biz_data that contains two large object columns col_x and col_y. Figure 1 The diagram is a schematic diagram of a table structure in a data table with a large object column according to an embodiment of the present invention.

[0075] When the user creates the data base table (business table) user_biz_data, the database system generates UUIDs (colx_uuid and colly_uuid) for the col_x and col_y large object columns contained in it. When generating database objects, prefixes are added to the corresponding UUIDs. On the one hand, some UUID text forms have a number as the first character, which does not conform to the database object naming rules; on the other hand, a group of database objects, including tables, indexes, and sequences, are used to store a large object column or column partition. Sequences are indeed objects used to generate auto-increment sequence values ​​in the database, and are usually used to provide unique identifiers for records in a table. In this solution, sequence numbers are generated for rewrite tasks and deduplication tasks respectively, the main purpose of which is to identify the execution order of these tasks. Since these sequence numbers are generated within a specific column or column partition range, and considering that the number of executions of these two types of tasks is usually not too many, the possibility of sequence value wrapping (i.e., the sequence value restarts after reaching the upper limit) is indeed very small. This ensures that the sequence numbers are unique within these ranges. In addition, these sequence numbers are not globally unique identifiers like UUIDs. UUID is usually used to generate globally unique identifiers. No matter where it is generated, the probability of conflict of UUID is very low. Sequence-generated unique identifiers are only valid in a specific context or range, and may no longer be unique beyond this range. If col_x and col_y are partitioned, each partition has its own UUID. In addition, regular data type columns col_a and col_b are stored according to the original storage logic.

[0076] The structure of the entire database includes: system metadata schema pg_catalog schema, application schema schema, and large object schema sys_klob_schema. The system metadata schema pg_catalog schema is the database metadata, and the application schema schema and large object schema sys_klob_schema are mainly user business data.

[0077] In the system tables, the relational attribute table (or system metadata table, which can be named pg_attribute) is used to store the common basic attributes of the database's general data types and data tables (including the basic attributes of large object columns that are consistent with other regular data types), the large object column metadata table sys_klob_column, the storage parameter history table sys_klob_store_params, and the deduplication task history table sys_klob_dedup_tasks as the unique storage attributes of big data. The large object column metadata table sys_klob_column records the OID of the created table and index, as well as the unique storage attributes of the large object column. The base table OID (rel_oid) and large object column number (attr_no) fields in the table are used to associate with the original system tables in the database, indicating which column in which data base table this row of data records the attributes of.

[0078] In order to track the execution status of long-term tasks such as deduplication tasks and rewriting existing data when modifying large object column storage options, and to ensure recovery from interrupted tasks, the storage parameter history table sys_klob_store_params and the deduplication task history table sys_klob_dedup_tasks are used to record the history of storage parameters and deduplication task parameters.

[0079] The large object schema sys_klob_schema includes col_x data storage tablespace and col_x data storage tablespace. If the large object column has partitions, each partition has its own data storage tablespace. Taking col_x data storage tablespace as an example, in addition to data storage of col_x objects and storage slice tables, indexes (which can be hash indexes) are also created for the primary key and reference columns of the objects and storage slice tables.<colx_uuid> ,ref_<colx_uuid> .

[0080] col_x object and storage slice tables can be named with the prefix tab_ plus colx_uuid to store object records and storage slice records. Both are globally uniquely identified by a randomly generated UUID. The object record is essentially the first storage slice (Chunk) record (Chunk) of the object.<colx_uuid> Primary key index idx_<colx_uuid> It can be used to quickly find objects or store slices of Chunks. When object deduplication is supported, objects are allowed to reference other objects and Chunks to reference other Chunks.<colx_uuid> Reference column index ref_<colx_uuid> It can be used to speed up the query operation of reference relationship.

[0081] In the base table user_biz_data, the location of the large object data item will be replaced by the large object control block, which will contain the actual storage location of the large object data item. In some embodiments, if the data size of the large object data item is very small, in order to optimize performance, the data item with small data size can also be directly stored in the large object control block according to the user's choice.

[0082] Figure 2 This is a schematic diagram of a system table in a data table with a large object column according to an embodiment of the present invention. There are four main system tables related to large object columns: large object column metadata table sys_klob_column, relationship attribute table pg_attribute, storage parameter history table sys_klob_store_params, and large object column deduplication task history table sys_klob_dedup_tasks.

[0083] The relation attribute table pg_attribute is used to record table column metadata. Like other columns of regular data types, the basic attributes of large object columns are also stored in this table. The data base table OID rel_oid and the column number attr_no of the large object column in the base table can be used to locate the position of the large object column in the data base table.

[0084] The large object column metadata table sys_klob_column is used to store the unique attributes of the large object column and its column partitions. It is associated with the relational attribute table pg_attribute through the two fields: the data base table OID rel_oid and the column number attr_no of the large object column in the base table. If the large object column is not partitioned, each record in the relational attribute table pg_attribute has a corresponding record in the large object column metadata table sys_klob_column. If the large object column is partitioned, each partition will also have a corresponding record in the large object column metadata table sys_klob_column.

[0085] Specifically, the large object column metadata table sys_klob_column can include the following attributes:

[0086] The data base table OID rel_oid is used when searching for metadata related to the base table.

[0087] The column number attr_no of the large object column in the base table can be used with rel_oid to uniquely identify a table column in the database and is used when looking up table column metadata.

[0088] Object and storage slice table OID tab_oid, used to store the OID of the large object and its storage slice table. If the large object column is not partitioned, each large object column corresponds to such a table. If the large object column is partitioned, each partition also corresponds to such a table.

[0089] Primary key index OID idx_<colx_uuid> , the OID of the index (which can be a hash index) of the primary key column unit_id of the object and storage slice table. When the large object column is not partitioned, each large object column corresponds to a primary key index. When the large object column is partitioned, each partition also corresponds to such an index.

[0090] Reference column index OID ref_<colx_uuid> : The OID of the index (hash index) of the object / storage slice identification column ref_id used in the object and storage slice table. If the big object column is not partitioned, each big object column corresponds to such an index. If the big object column is partitioned, each partition also corresponds to such an index.

[0091] The tablespace OID tbs_oid is the OID of the tablespace where the object and the slice table and its index are located. Large object columns can be placed in different tablespaces from other data, or each column partition can be placed in a different tablespace. If needed, a separate tablespace can be specified for each large object column (unpartitioned) or large object column partition (partitioned).

[0092] The storage parameter version number sequence OID store_seq_oid is the storage parameter version number sequence store_seq_<col / part uuid> The OID of the store_seq_ sequence is used to generate integers that identify the versions of the large object storage parameters and their order. Each large object column (in the case of non-partitioning) or large object column partition (in the case of partitioning) corresponds to such a sequence object.

[0093] The deduplication task sequence OID dedup_seq_oid is the deduplication task sequence dedup_seq_oid for the large object column or large object column partition.<col / part uuid> The OID of the deduplication task sequence dedup_seq is used to generate an integer to identify the version of the large object deduplication task parameters and their order. Each large object column (in the case of no partition) or large object column partition (in the case of partition) corresponds to such a sequence object.

[0094] The physical size of the storage slice chunk, chunk_size, is used to record the size of the storage slice Chunk of the large object. The unit is bytes. The Chunk size is not limited by the size of the underlying storage page and can be selected between 2K and 1G. The Chunk size is set separately for each large object column (unpartitioned) or large object column partition (partitioned) to meet the requirements of different large data columns.

[0095] The current storage option lob flags is the current storage option, which can be a 32-bit bit string, which contains storage options such as storage location, compression, and encryption. The storage parameter history table sys_klob_store_params also fully records the storage options of each version. In order to speed up some operations, the current storage option lob flags retains a redundant field and is stored repeatedly;

[0096] The current storage parameter version number store ver is the latest serial number generated by the large object column / partition storage parameter version number sequence. It is used together with tab_oid to identify which record in the system table sys_klob_store_params the current storage parameter corresponds to.

[0097] dedup_ord: current deduplication task number, which is the latest serial number generated by the large object column or large object column partition deduplication task serial number sequence. It is used together with tab_oid to identify which record in the large object column deduplication task history table sys_klob_dedup_tasks the current deduplication task corresponds to.

[0098] Partition strategy partstrat, used to record the partition strategy. The partition strategy can include h hash partition, l list partition, r range partition table. This attribute has a value only in the row corresponding to the object and the storage slice partition table;

[0099] The number of columns in the partition key, partnatts; only has values ​​in the row corresponding to the object and the Chunk partition table;

[0100] Default partition partdefid: The OID of the pg_class entry for the default partition of this partitioned table, or zero if this partitioned table has no default partition. This value is present only in rows corresponding to objects in Chunk partitioned tables;

[0101] The partition key column number vector partattrs is an array of length partnatts values ​​indicating which table columns are part of the partition key. For example, the value 1 3 indicates that the first and third table columns form the partition key. A zero in this array indicates that the corresponding partition key column is an expression rather than a simple column reference, and is only present in the row corresponding to the object and the storage slice partition table;

[0102] Partition key operator class OID vector partclass. For each column in the partition key, this field contains the OID of the operator class to use. It has a value only in the row corresponding to the object and the storage slice partition table.

[0103] Partition key column collation OID vector partcollation. For each column in the partition key, this field contains the OID of the collation to be used for partitioning. If the column is not a sortable data type, the corresponding value is zero. It only has a value in the row corresponding to the object and the storage slice partition table.

[0104] Partition key column expression tree partexprs, expression tree of partition key columns that are not simple column references (expressed in nodeToString()), which is a list with one element for each zero item in partattrs. If all partition key columns are simple column references, this field is empty. There is a value only in the row corresponding to the object and the Chunk partition table;

[0105] The internal representation of a single partition boundary, relpartbound, is the internal representation of the partition boundary and has values ​​only in the rows corresponding to the column partition.

[0106] The large object column-specific storage parameter history table sys_klob_store_params is used to save all historical versions of each large object column-specific storage parameter in the database library, and is associated with the records in the sys_klob_column table through the tab_oid column. These historical data are used when modifying the compression, encryption and other storage options of large objects and need to rewrite existing data.

[0107] Specifically, the large object column-specific storage parameter history table sys_klob_store_params can include the following attributes:

[0108] Object and storage slice table OID tab_oid;

[0109] The storage parameter version number store_ver is defined by the aforementioned tab_oid as store_seq_<col / part uuid> The sequence generation;

[0110] The storage option lob flags can be used to identify the off-line storage method (including table storage method, file storage method, OSS (Object Storage Service) storage method, etc.), whether in-line storage is available (available or unavailable), whether encryption is available (encrypted or not), the compression level (not compressed, low-level compression, medium-level compression, high-level compression), and the encryption algorithm identifier (encryption algorithm type). The contents of the above identifiers occupy one or more identification bits of the storage option lob flags. In some embodiments, the storage option lob flags can be a 32-bit bit string, which is spliced ​​by the above identification bits.

[0111] Key expiration UTC time key_expr, the expiration time of the key used to encrypt the large object column, can be in UTC time (Coordinated Universal Time);

[0112] The key enc_key is the key for encrypting large object columns. It is stored in ciphertext and encrypted with the parent key.

[0113] The encryption & compression task identifier enc_task_id is the identifier of the large object column encryption and compression task. It can be an identifier in the form of a UUID, which is randomly generated when creating a large object column encryption and compression task and registered in a hash table in the memory. If the identifier recorded in the table cannot be found in the encryption and compression task registration table in the current memory, it means that the process executing this encryption and compression task has exited.

[0114] The tab_oid and store_ver fields in the large object column-specific storage parameter history table sys_klob_store_params can be combined to uniquely identify a storage parameter version of a large object column or large object column partition in a library.

[0115] The large object column deduplication task history table sys_klob_dedup_tasks saves the parameters of each deduplication task for each large object column in the database. It is associated with the records in the sys_klob_column table through the tab_oid column to determine the execution status of the deduplication task.

[0116] Specifically, the large object dedup task history table sys_klob_dedup_tasks can include the following attributes:

[0117] Object and storage slice table OID tab_oid, meaning is the same as the tab_oid attribute of other system tables above;

[0118] The deduplication task sequence number dedup_ord is composed of the object and storage slice table OID as the large object column or large object column partition deduplication task sequence dedup_seq_<col / part uuid> The sequence generation;

[0119] The unique identifier of the deduplication task process group, dedup_task_id, is a UUID format identifier, which is randomly generated when creating a large object column deduplication task and registered in a hash table in memory. If the identifier recorded in the table cannot be found in the deduplication task registration table in the current memory, it means that the process executing this deduplication task has exited.

[0120] The dedicated system tables established above record the description information of the object and the storage slice table. These dedicated system tables are used to store the correspondence between the large object columns in the user table and the database objects used to store large objects, as well as the unique storage parameters of the large object columns, making data management more efficient and in line with the storage management characteristics of large objects.

[0121] In the solution of this embodiment, a large object control block is used to replace the position of the corresponding large object data item in the data base table. The large object control block can include two types: an in-row large object storage structure and an out-of-row large object storage structure.

[0122] Figure 3 The diagram is a schematic diagram of an optional large object control block in a data table having a large object column according to an embodiment of the present invention. Figure 3 The out-of-row large object storage structure is shown. The large object control block varattrib_4b includes a file header varattrib_4b.va_header and data varattrib_4b.va_data. The data varattrib_4b.va_data includes the description information of the large object data item (LOB object control block description word) and the data item object identifier (LOB object fixed unique identifier).

[0123] The description information of the large object data item may describe the control block type (inline or outline), flags such as whether there is data in the line, control block format version number, etc. The above contents may respectively occupy one or more flags of the description information. In some embodiments, the description information of the large object data item may be a 32-bit bit string, which is formed by concatenating the flags corresponding to the above descriptions.

[0124] The data item object identifier is a UUID type identifier that is randomly generated when the object is created and remains unchanged throughout the life cycle of the object.

[0125] The out-of-row large object storage structure only contains the LOB object control block descriptor and the LOB object fixed unique identifier. These two fields remain unchanged throughout the life cycle of the object, avoiding the need to update the data base table when updating the large object content.

[0126] Figure 4 The diagram is a schematic diagram of another optional large object control block in a data table having a large object column according to an embodiment of the present invention. Figure 4 The in-line large object storage structure is shown.

[0127] The in-row large object storage structure is for large object data items with a small amount of data (e.g., within 32k). It stores large object data in the control block (the maximum size of the large object data is within a set range, e.g., a maximum of 32k). In addition to the LOB object control block descriptor and the LOB object fixed unique identifier, the data varattrib_4b.va_data further includes: LOB object storage options and LOB data. The LOB object storage option can be a bit string, which can use the same format as the lob_flags field in the system table sys_klob_column and sys_klob_store_params. LOB data contains the data of the large object.

[0128] By distinguishing between the in-row large object storage structure and the out-of-row large object storage structure, the storage requirements of large object data items of different data sizes are met, and only data exceeding a certain data size (for example, 32k) is stored out-of-row.

[0129] The object and storage slice table is a table that actually stores large object data. During database operation, each large object column or column partition is equipped with an object and storage slice table. This embodiment optimizes and improves the object and storage slice table, which is significantly different from the existing PostgreSQL and has obvious advantages.

[0130] In the PostgreSQL database solution, the large object storage table pg_largeobject is a system table that is only used to store slice data. It contains three fields: loid, pageno, and data. Among them, loid is the unique identifier of the large object, the type is OID (4-byte unsigned integer), and it is unique within the database. Pageno identifies the page number of the chunk data in the large object (starting from 0), and the type is a 4-byte integer. Data is the chunk data, and the volume does not exceed 1 / 4 of the database storage page size. For the common 8K storage page setting, its size does not exceed 2K.

[0131] In the PostgreSQL database, the total capacity of large objects in the entire database is limited by the upper limit of the data volume of a single table, and the total number of objects in the entire database is limited by the range of OID values. If the user's business table uses data types with a larger value range, such as int8, the number of large object identifiers may not be enough. Moreover, this type of OID identifier can only ensure uniqueness within a database. If you want to import a large object into another database, you must reallocate the identifier, which is not conducive to tracking large objects. Each row of a large data object stores the data of a page of a large object, starting from the byte offset (pageno*LOBLKSIZE) inside the object. Sparse storage is allowed, and pages may be lost and may be shorter than LOBLKSIZE bytes (even if it is not the last page). A lost area in a large object will be read as 0. In other words, this solution does not support insertion or deletion operations in the middle of a large object, but only supports content appending and replacement. Its behavior mode is different from the generally understood editing operation.

[0132] Figure 5 : This is a schematic diagram of an object and storage slice table in a data table with a large object column according to an embodiment of the present invention. In the scheme of this embodiment, there are two types of records stored in the object and storage slice table. The first type is an object record, which contains object metadata and data within the first 32K (for example, it can be set to other values), which is used to accelerate the operation of some functions. For objects larger than 32K (for example, it can be set to other values), it also contains a storage slice index block, which is used to find out which storage slices the data read and written by the user is located in. The second type is a storage slice record, which contains storage slice metadata and data above 32K. A large object (more than 32K) will consist of an object record and several storage slice records. For each large object column or column partition, the size of the storage slice can be freely selected within 1G. If the average size of the objects in the column is small, it is suitable to use a smaller storage slice data size; if the average size of the objects in the column is large, it is suitable to use a larger storage slice size. For example, for TB-level large objects, the largest storage slice size (1GB) can be used.

[0133] Specifically, the object and storage slice table may include the following attributes: the object's fixed unique identifier uint_id, the partition key part_key used internally by the system, the storage parameter version number store_ver, the reference object identifier ref_id, the reference object count ref_count, the object deduplication feature value dedup_feat, the data prefetch within the first 32k of the object, and the additional data field ext_data.

[0134] The object's fixed unique identifier, uint_id, is a UUID-type unique identifier. For object records, this is the object identifier, and for storage slices, this is the storage slice identifier. UUID-type identifiers can be considered unique in any case. Even if an object is imported into other databases, this identifier can remain unchanged and no conflict will occur.

[0135] part_key is the partition key used internally by the system and can be a signed integer. When the user chooses to use other columns in the base table as the partition key of the large object column, the original partition setting made by the user will be translated into an internal list partition format. This field contains the partition key used internally. When the user chooses to perform Hash partitioning based on the object / storage slice identifier, this field can be NULL.

[0136] store_ver is the storage parameter version number of the object, which is NULL in the storage slice record. This field can also be used to distinguish object records from storage slice records.

[0137] ref_id, for object records, if there is a value, it is the identifier (UUID) of the referenced object, indicating that the object is exactly the same as the referenced object; for storage slice records, if there is a value in this field, it is the identifier (UUID) of the referenced storage slice, indicating that the storage slice is exactly the same as another storage slice. When this field is not NULL, the prefetch and ext_data fields of the record are both NULL. In addition, only when one object is exactly the same as another object, can there be a reference relationship between the two. If only some storage slices are the same, there is only a reference relationship between the storage slices, and there is no reference relationship between the objects.

[0138] ref_count, in the object record, represents the number of times this object is referenced by other objects; in the storage slice record, represents the number of times this storage slice is referenced by other storage slices. If the object or storage slice is not referenced, the value of this field is 0.

[0139] dedup_feat is the deduplication feature value of the storage slice itself in the storage slice record, and the deduplication feature value of the entire object in the object record. This is a binary data block, and its length is determined by the deduplication feature value algorithm. If the deduplication function has never been enabled on the corresponding column or column partition, this field is NULL (initial state). For small objects stored out of row, if DML operations change the object content, then when the DML operation occurs: 1. If deduplication is not currently enabled, set this field to NULL; 2. If deduplication is currently enabled, generate a new object feature value and fill it in this field; for large objects stored out of row, if DML operations change the content of its subordinate slices, then when the DML operation occurs: 1. If deduplication is not currently enabled, set this field to NULL; 2. If deduplication is currently enabled, generate a new slice feature value and fill it in this field. When the deduplication task is executed, feature values ​​will be generated for small objects that do not have feature values ​​and slices of large objects, and filled in this field.

[0140] prefetch, in the object record, is the data within the first 32K of the object, which is stored according to the object storage parameter settings and may be compressed and encrypted; in the storage slice record, this field is always NULL.

[0141] ext_data, in the object record, this additional data field contains the storage slice index block, records the arrangement order of all storage slices of the object, the original data logical length and unique identifier. The logical length here refers to the length calculated in the unit of the original data. For BLOB, the unit is bytes, and for CLOB / NCLOB, the unit is characters. In the storage slice record, this field contains the data of the part above 32K of the large object. Both the storage slice index block and the storage slice data will be stored according to the setting of the object storage parameters, and may be compressed and encrypted. For example, in the record of the first storage slice of a large object, the additional data field can be used to store the index records of all storage slices corresponding to the large object, that is, the storage slice index block. In the storage slices other than the first storage slice of the large object, the additional data field is used to store the data of the corresponding storage slice. That is, only the first storage slice of the large object is used to store the storage slice index block in the additional data field, and its own data is stored in the prefetch field, and the remaining storage slices will store their own corresponding data content in the additional data field of ext_data. Whether storing slice index blocks or slice data, they will be stored according to the settings of the object storage parameters, and may be compressed and encrypted. Under the above storage structure, the storage slice content modification method of this solution is significantly different from the PostgreSQL database in the prior art. It can efficiently support insertion and deletion operations in the middle of large objects and adopt the same semantics as string insertion and deletion operations.

[0142] In the object record (i.e. the first data row of the object), the prefetch field is used to store the first slice of the object, i.e. the data of limited length in the object header. In some scenarios, this part of data can be directly read and written like a regular field without going through a file-based API; its length limit is also low, unlike subsequent slices which can be set to be very large; the ext_data field is used to store the slice index block. In the slice record (i.e. the data row where the second and subsequent slices are located), the prefetch field is not used and is set to NULL; the ext_data field is used to store the data content of the slice.

[0143] Through the above database design method, when a large object is split into multiple storage slices, first, the global unique identifier of the large object itself can be determined in the data base table (that is, the user_biz_data table), and the first storage slice where it is located can be found in the object and storage slice table according to the global unique identifier of the large object (the global unique identifier of the large object is the slice unique identifier of the first storage slice); then, the global unique identifier of the target storage slice where the target data is located is determined through the storage slice index block recorded in the first storage slice, and then the target storage slice is found in the object and storage slice table through the global unique identifier of the target storage slice, and finally the specific data of the target storage slice is obtained in its ext_data column.

[0144] In addition, if you want to modify the data of the target storage slice, you can also find the corresponding target storage slice and perform the operation according to the above method, and then synchronously update the content of the storage slice index block in the additional data field of the first storage slice.

[0145] The index block of the storage slice is a binary data block that contains the location information of all the storage slices of an object, which includes the following fields in sequence: storage slice index library format description word, storage slice number, storage slice length array, and storage slice identification array.

[0146] The storage slice index library format description word can be a signed integer, used as a bit string, some of its bits (such as the lower 8 bits) are the storage slice index block format version number, and the other bits can be reserved.

[0147] The total number of storage slices, that is, the length of the subsequent storage slice array, the type is a signed integer. If this field is 0, it means that no storage slice is included.

[0148] The length of the storage slice array is the total number of storage slices mentioned above. The array element type is a signed integer and is arranged in the order of the storage slices in the object.

[0149] Storage slice identifier array, the length is the total number of storage slices, the array element type is UUID number, and it is also arranged in the order of storage slices in the object.

[0150] from Figure 5 As can be seen in the figure, in the storage slice index block, the metadata (length and identifier) ​​of the storage slice is not stored by row, but by column, that is, all storage slice length values ​​are stored continuously first, and then all storage slice identifier values ​​are stored continuously. Since large objects are usually updated less frequently, there will be many repeated values ​​in the length value of their storage slices, which helps to improve the compression rate.

[0151] In the solution of this embodiment, the metadata (identification, length, etc.) of the storage slice is stored separately from the data, the actual length of the storage slice is variable, and the order of the storage slices in the large object is recorded by the storage slice index block. Therefore, when inserting or deleting data in the middle of the large object, only the storage slice involved in the inserted or deleted data needs to be rewritten, and other storage slices are not moved, which greatly reduces the amount of data that needs to be rewritten during the update and improves performance.

[0152] In comparison, the PostgreSQL database uses fixed-length storage slices, and the storage slice metadata and data are saved in one record. The position of the storage slice sequence number is used to indicate its position in the object. If data is inserted or deleted from the middle, all storage slices after the insertion / deletion point need to be rewritten, and the performance is far inferior to the solution of this embodiment.

[0153] By adding reference object / storage slice uint_id, ref_id, and ref_count, the large object storage of the solution of this embodiment supports deduplication operations, that is, simplifying duplicate objects or storage slices. Figure 6 It is a schematic diagram of storage slices referencing each other in a data table with a large object column according to an embodiment of the present invention. For example, some storage slices of a large object data item A are the same as storage slices in another large object data item B. These storage slices are relatively independent and have unique identifiers, but do not store actual data, but store the identifier of the storage slice of the object data item B. For example, when storage slice 102 is the same as storage slice 201, and storage slice 104 and storage slice 105 are the same as storage slice 204, the identifier of storage slice 201 is stored in storage slice 102, and storage slice 104 and storage slice 105 respectively store the identifier of storage slice 204. Storage slice 201 and storage slice 204 respectively record reference counts.

[0154] Similarly, if a large object data item D is the same as another large object data item C, then large object data item D can store the identifier of large object data item C without storing the data. Large object data item C records the reference count. Of course, this is only for large object data items with a volume of less than or equal to 32K. For larger large object data items, since the cost of determining whether they are duplicated as a whole is too high and the probability of a complete match is low, it is a better choice to only remove duplicates at the granularity of storage slices.

[0155] The reference count of the above records can avoid data errors when modifying data or executing DML.

[0156] Using the above storage structure, the storage forms of large objects can be divided into three types: small objects stored in-line, small objects stored out-of-line, and large objects stored out-of-line.

[0157] The metadata and data body of the small objects stored in the row are stored in the row of the data base table in the form of an in-row control block. The small objects stored in the row have a compact structure and occupy little space.

[0158] Part of the metadata of small objects stored out of line is stored in the data base table row, and the remaining metadata and data body are stored in the object and storage slice table in the form of an out-of-line object control block and an object record. In addition to the control block and the object data itself, small objects stored out of line also contain fields that support other features such as partitioning, deduplication, and progressive background tasks.

[0159] Part of the metadata of a large object stored out-of-line is stored in-line, the remaining metadata, the first 32K data, and the storage slice index block are stored in one object record, and the data above 32K is stored in a group of storage slice records. A large object stored out-of-line consists of one object record and several storage slice records. The object record and several storage slice records are stored in the same table, using the same primary key column. The object record serves as the entry point to access the large object data.

[0160] It is worth noting that although large objects stored out-of-row also have fields that support partitioning, deduplication, and progressive background tasks, as well as other features of the above-mentioned small objects, the differences are: 1. In the object records of large objects, ref_id, ref_count, and dedup_feat are not used, because large objects are not deduplicated as a whole, but only slice-level deduplication; 2. In the slice records of large objects: store_ver is not used, because all contents of a single object use unified storage parameters, which is more in line with the general expectations of users; prefetch is also not used, because when users read and write specified fragments in large objects, they do not need to quickly obtain a small piece of data at the head of the slice.

[0161] As mentioned above, in existing database systems, since large objects may occupy a large amount of storage space, rewriting operations usually involve a large number of data reading, processing, and writing steps, which are often very time-consuming. When rewriting large objects, it is usually necessary to lock the relevant data rows or tables to prevent the data from being modified by other transactions during the rewriting process. However, long-term locking will block the access of other transactions, resulting in a decrease in database concurrency performance and a negative impact on daily business.

[0162] To solve the above problems, the present invention provides a method for rewriting large objects in a database. Figure 7 FIG. 1 is a flowchart of a method for rewriting a large object of a database according to an embodiment of the present invention. Figure 7 As shown, the method for rewriting large objects in the database at least includes the following steps S701 to S703.

[0163] Step S701: obtaining an event of rewriting data in a large object column in a database, where the large object column is used to store large object data items of a large object data type.

[0164] Step S702, establishing a rewriting subtask queue and a rewriting process group, wherein the rewriting subtask queue is used to store a plurality of rewriting subtasks divided based on rewriting events, and the rewriting process group includes a plurality of rewriting processes running in parallel.

[0165] Step S703: assigning the rewriting subtasks in the rewriting subtask queue to the respective rewriting processes of the rewriting process group, and having each rewriting process process the assigned rewriting subtasks.

[0166] The large object rewriting method of the database of the present invention can reduce the locking time, reduce the database system load, and meet the optimization of daily business needs through task division and parallel processing, which significantly improves the performance and stability of the database system and provides an effective solution for database maintenance and management in actual business scenarios.

[0167] In some embodiments, in order to ensure that the rewrite task of the entire large object column can be executed smoothly and completely, and to give correct feedback after all related rewrite subtasks are completed, after each rewrite process independently handles the steps of the rewrite subtask assigned to it, the completion status of all rewrite processes can be obtained. When all rewrite processes are completed, the rewrite task of the large object column is reported as completed.

[0168] In a specific example, the session process that monitors the execution of the entire rewriting task can divide the rewriting task into several rewriting subtasks, and the divided rewriting subtasks will be executed in parallel by the rewriting process group under the monitoring of the session process. After each rewriting subtask is executed, the execution result will be reported to the session process responsible for monitoring through the rewriting subtask completion notification queue. When all rewriting subtasks are executed, the session process responsible for monitoring displays the execution result to the user.

[0169] In some embodiments, the step of obtaining an event of rewriting data in a large object column in a database may be to monitor modification instructions for the large object column. The modification instructions may come from user operations, application requests, or automated scripts, etc. The modification instructions include modifications to data types and storage parameters, etc. When a modification instruction is monitored, an event of rewriting data in the large object column is triggered.

[0170] Since different data types occupy different storage spaces in the database and have different ways of representing data, after converting data types, the database management system usually needs to modify the stored data to adapt to the new data type. When a user modifies the data type of a column, such as from int to long or double, the system detects this modification and triggers a rewrite event, requiring the data in the table to be rewritten.

[0171] In some embodiments, modifying storage parameters (eg, compression, encryption, table space, etc.) also means that the storage format or location of the data will change, which will also trigger the rewriting of the data.

[0172] Figure 8 FIG. 1 is a flowchart of establishing a rewriting subtask queue in a method for rewriting a large object in a database according to an embodiment of the present invention. Figure 8 As shown, establishing the rewriting subtask queue at least includes the following steps S801 to S803.

[0173] Step S801, initialize an empty queue in the memory as the rewriting subtask queue.

[0174] In this step, the system creates a new, empty queue in its memory space to store the multiple rewrite subtasks that are subsequently divided. Initializing the queue can be part of the preparation work, ensuring that the queue is clean and ready when adding tasks.

[0175] Step S802: divide the rewriting task of the large object column into a plurality of rewriting subtasks according to the partitioning condition of the large object column.

[0176] In this step, the system will split the overall rewriting task into multiple smaller, more manageable subtasks based on the partitioning of the large object column, so that these subtasks can be processed in parallel, thereby improving the overall processing speed and efficiency. Each subtask can correspond to the data rewriting work of one or more partitions of the large object column.

[0177] Step S803: adding multiple rewriting subtasks to the rewriting subtask queue in sequence.

[0178] In this step, the system will add the subtasks to the previously initialized rewrite subtask queue in a certain order (maybe partition order, data size order or other logical order). In this way, the queue forms a list of tasks to be processed, and the subsequent processing logic can take tasks from the queue for execution.

[0179] In this way, by establishing a queue containing multiple rewriting subtasks, a basis can be provided for subsequent task scheduling and execution. This divide-and-conquer approach is very effective when processing large amounts of data, and can improve the throughput and response speed of the system. At the same time, managing tasks through queues also facilitates the implementation of priority scheduling, concurrent processing and other functions of tasks, further improving the flexibility and scalability of the system.

[0180] In one example, the step of dividing the rewriting task of the large object column into multiple rewriting subtasks according to the partitioning status of the large object column may be to assign one rewriting subtask to each unpartitioned large object column and to assign one rewriting subtask to each column partition of the partitioned large object column.

[0181] Specifically, the system can check the metadata information of the database to understand the configuration and properties of each large object column, thereby determining whether the large object column is partitioned. For each non-partitioned large object column, the system can assign it a separate rewrite subtask, which will be responsible for handling the data rewrite work of the large object column. Since there is no partition, there is no need for further splitting. For each partitioned large object column, the system can further analyze its partition situation, including the number of partitions, the size of each partition, data distribution and other information. Based on the analysis of the partition situation, the system can assign a separate rewrite subtask to each partition, which can ensure that the data rewrite work of each partition can be carried out independently, thereby achieving parallel processing.

[0182] Of course, the above examples are only for illustration purposes, and are used to explain the concept of how to divide the rewrite subtasks according to the partitioning of the large object column. In actual applications, database administrators or developers can adjust and optimize the task division strategy according to specific circumstances to ensure the efficiency and reliability of the rewrite operation.

[0183] Fig. 9FIG. 1 is a flowchart of establishing a rewriting process group in a large object rewriting method for a database according to an embodiment of the present invention. Fig. 9 As shown, establishing a rewriting process group may include the following steps S901 to S904.

[0184] Step S901, obtain the queue length of the rewriting subtask queue and the parallelism parameter of the database rewriting task. The queue length value of the rewriting subtask queue represents the number of rewriting subtasks waiting to be processed in the current queue, and the parallelism parameter value of the database rewriting task represents the maximum number of rewriting processes allowed to run simultaneously by the database system.

[0185] Step S902, determine whether the queue length of the rewriting subtask queue is greater than the parallelism parameter of the database rewriting task, if so, execute step S903, if not, execute step S904.

[0186] Step S903, create a rewrite process group whose number of processes is equal to the parallelism parameter value of the database rewrite task. It can be understood that when the queue length of the rewrite subtask queue is greater than the parallelism parameter of the database rewrite task, it means that there are more rewrite subtasks to be processed, but the system resources (such as the number of CPU cores or the number of threads) may not be sufficient to process all rewrite subtasks at the same time. At this time, the system will create a number of rewrite process groups equal to the parallelism parameter value, which can ensure that the system does not try to create too many processes, thereby exhausting system resources.

[0187] Step S904, create a rewrite process group whose number of processes is equal to the queue length value of the rewrite subtask queue. It can be understood that when the queue length of the rewrite subtask queue is less than or equal to the database rewrite task parallelism parameter, it means that the system has enough resources to process all rewrite subtasks at the same time. At this time, the system will create a number of rewrite processes equal to the queue length value of the rewrite subtask queue, so as to ensure that all rewrite subtasks can be processed in time without causing task delays due to insufficient number of processes.

[0188] In summary, in this way, the system is able to dynamically adjust the number of rewriting processes to accommodate rewriting tasks and workloads of different sizes, which helps improve the system's responsiveness and overall performance.

[0189] In the scheme of this embodiment, the object types of large object data items can be divided according to the storage method, and objects stored in-line are defined as in-line objects, such as the small objects stored in-line above, and objects stored out-of-line are defined as out-of-line objects, such as the small objects stored out-of-line and the large objects stored out-of-line above.

[0190] The data size of small objects stored in the row and small objects stored outside the row are both smaller than the preset threshold, while the data size of large objects stored outside the row is larger than the preset threshold and is divided into multiple storage slices for storage. The preset threshold can be 32K, of course, it can also be other values, and this embodiment does not make specific restrictions on this.

[0191] The metadata and data body of the in-row object are stored in the row of the database table, part of the metadata of the out-of-row object is stored in the row of the database table, and the rest of the metadata and data body are stored outside the row of the database table.

[0192] For in-row objects, since all their information is stored in the data table row, the rewrite process can quickly locate and modify these objects. Therefore, when the rewrite process is processing the assigned rewrite subtask, if the rewrite subtask involves in-row objects, the rewrite process can directly rewrite the in-row objects in the data table row. Since no additional I / O operations are required to read or write data in other storage locations, this direct access and modification method can significantly improve the efficiency of rewriting.

[0193] When processing an in-row object, the rewriting process first obtains the object data of the in-row object, then restores the obtained object data to obtain the original object data, and finally rewrites the original object data according to the given storage parameters.

[0194] In the solution of this embodiment, refer to Figure 3 and Figure 4 , two forms of control blocks are designed to efficiently access large objects of different sizes. The control block is in the row of the data base table, replacing the large object itself. For small objects (≤32K), if the user allows in-row storage, they can be stored directly in the control block, thereby simplifying the read and write process and improving the read and write speed. For large objects, the control block only contains the control block version, attributes and UUID of the large object, which is used to guide the database to find the large object stored outside the row.

[0195] In a specific example, the rewriting process obtains object data from the control block of the in-row object, then decrypts and decompresses the obtained object data according to the current storage parameters to obtain the original object data, and finally compresses and encrypts the original object data according to the given storage parameters.

[0196] For out-of-row objects, since their data bodies are stored outside the rows of the data table, the rewriting process cannot directly modify these objects. Therefore, when the rewriting process processes the assigned rewriting subtasks, if the rewriting subtasks involve out-of-row objects, the rewriting process can write the object identifier of the out-of-row object into the out-of-row object temporary table pre-created by the database, and then scan the out-of-row object temporary table and rewrite the scanned out-of-row objects. This processing method allows the rewriting process to flexibly operate on out-of-row objects, while avoiding traversal of the entire database, improving the efficiency of rewriting.

[0197] In the case where the out-of-row object is a small object, the step of rewriting the scanned large object data item may be to obtain the object data in the object record of the large object data item, restore the obtained object data to obtain the original object data, rewrite the original object data according to given storage parameters, and update the storage parameters and object record of the large object data item.

[0198] In a specific example, the rewrite process can read the first 32K data of a small object, that is, the prefetch field in the object record, and then decrypt and decompress the object data in the prefetch field according to the current storage parameters to obtain the original object data, and then compress and encrypt the original object data according to the given storage parameters, and finally update the storage parameters and object record of the small object.

[0199] In the case where the out-of-row object is a large object, the step of rewriting the scanned large object data item may be to obtain the slice data in the slice records of all stored slices of the large object, and then restore the obtained slice data one by one to obtain the original slice data, and then rewrite the original slice data according to the given storage parameters, and finally update the slice records of the stored slices.

[0200] In a specific example, the rewrite process can read the ext_data field in the slice record of the storage slice, then decrypt and decompress the slice data in the ext_data field according to the current storage parameters to obtain the original slice data, then rewrite the original slice data according to the given storage parameters, and finally update the slice record of the storage slice.

[0201] After updating the slice record of the rewritten storage slice, the object data in the object record of the large object can be obtained, the obtained object data can be restored to obtain the original object data, the original object data can be rewritten according to the given storage parameters, and the storage parameters and object records of the large object can be updated.

[0202] In a specific example, after updating the slice record of the rewritten storage slice, the rewrite process reads the first 32K data of the large object, that is, the prefetch field in the object record, and then decrypts and decompresses the object data in the prefetch field according to the current storage parameters to obtain the original object data, and then compresses and encrypts the original object data according to the given storage parameters, and finally updates the storage parameters and object records of the large object.

[0203] In the scheme of this embodiment, the ext_data field of the object record contains a storage slice index block, which records the arrangement order of all storage slices of the large object data item, the original data logical length and the unique identifier. The logical length mentioned here refers to the length calculated in units of original data. For BLOB, the unit is bytes, and for CLOB / NCLOB, the unit is characters. In the slice record of the storage slice, the ext_data field contains the data of the part above 32K of the large object. Whether it is a storage slice index block or storage slice data, it will be stored according to the setting of the object storage parameters, and may be compressed and encrypted. That is, for a large object stored out of row, its index block, data in the object record, and data in the slice record are all compressed and encrypted according to the storage parameters of the large object.

[0204] In some embodiments, the step of obtaining slice data in slice records of all storage slices of a large object data item may be to obtain a storage slice index block of the large object data item, restore the obtained storage slice index block to obtain the original index block, and obtain the slice data in the slice records of all storage slices of the large object data item based on the original index block.

[0205] In the scheme of this embodiment, object records and storage slice records are placed in the same object and storage slice table, and UUID is uniformly used as the primary key. Therefore, only one primary key index is needed to quickly retrieve both objects and storage slices. The object record is like a special storage slice, which is the entrance to access large object data. When only the first 32K of data need to be accessed, it can be directly obtained from the prefetch field of the object record without further search; when data after 32K needs to be accessed, the UUID of the storage slice to be accessed can be found through the storage slice index block stored in the ext_data field of the object record, and then the primary key index on the table can be used to find these storage slice records. Since the entire process only requires equality queries and does not require range queries, a more efficient Hash index can be used than a B-Tree index.

[0206] In general, the process of rewriting large objects according to given storage parameters can be roughly divided into three stages: preparation stage, rewriting in-row objects stage, and rewriting out-of-row objects stage.

[0207] 1. Preparation phase: query and cache the storage parameters of all versions of the specified large object column or column partition, so that the parameters used in the actual storage of each object can be queried in subsequent operations.

[0208] 2. Rewrite in-row object stage: Scan the data base table according to the conditions of the given large object column or column partition, rewrite the in-row object, and record the unit_id of the out-of-row object in an out-of-row object temporary table; operations in this stage can be performed in a large transaction.

[0209] 3. Rewrite out-of-row objects phase: sequentially scan the out-of-row object temporary table and rewrite the object data one by one; start an independent transaction for each object, first rewrite all storage slice records, then rewrite the object record, and then commit the transaction. After processing all out-of-row objects, delete the out-of-row object temporary table.

[0210] New objects inserted by the user during the execution of the above tasks will be stored using the latest storage parameters, so there is no need to rewrite them.

[0211] The large object rewriting method of this embodiment supports rewriting existing data after modifying the storage parameters (including encryption and compression) of large object columns or column partitions, and also supports data deduplication in units of large object columns or column partitions. For each task on a large object column or column partition, it will be executed as an independent transaction for each object, and the transaction isolation level is Read Committed. In the object and storage slice table, each object record contains the current storage parameter field store_ver, which is used to indicate the storage parameters under which the data of the object is currently stored. In the storage parameter history table, the storage parameters of each large object column or column partition are stored, and include the values ​​set each time since its creation. Refer to Figure 2 There is a UUID type encryption & compression task identification field enc_task_id in the storage parameter history table, which is used to identify the data rewriting task corresponding to this version of the storage parameter. It is randomly generated when creating a large object column encryption and compression task and registered in a hash table in memory. If the identifier recorded in the table cannot be found in the encryption and compression task registration table in the current memory, it means that the process executing this task has exited. The tab_oid and store_ver fields in the table can be combined to uniquely identify a storage parameter version of a large object column or column partition in the library.

[0212] In the solution of this embodiment, the rewrite task will be executed as a background low-priority task and will be split into small transactions. Even if the database is shut down during execution or the user re-initiates the task with different parameter settings, the historical parameters of such tasks are recorded, so that the data can be processed according to the latest settings.

[0213] In addition, when the database supports concurrent access by multiple users, it is necessary to detect the execution status of background progressive tasks to avoid task conflicts. To this end, when a background progressive task is created, a UUID is randomly generated as its task identifier. The task identifiers of all executing background progressive tasks will be registered in a hash table in memory. The key of the hash table is the task identifier, and the value is the attribute information of the task. The task identifier is also recorded in the large object dedicated storage parameter history table (for data rewriting tasks) or the deduplication task history table (for deduplication tasks). After the task is executed normally or is actively canceled by the user, the task entry will be deleted from the hash table.

[0214] Take the data rewrite task as an example: when the system reads the object data, if its storage parameters are not the latest version, it can find the corresponding record in the large object-specific storage parameter history table based on the OID and storage parameter version number of the storage table where it is located, read the data rewrite task ID, and compare it with the data rewrite task hash table in the memory. If the task ID can be found in the hash table, it means that the relevant task process is still executing; otherwise, it means that the relevant task has stopped, which may be interrupted by an unexpected downtime or canceled midway because the user specified new storage parameters. If the rewrite task finds during execution that there are rewrite tasks using different versions of storage parameters on the same large object column or column partition, the storage parameter version numbers are compared. The rewrite process with a smaller storage parameter version number will exit actively, and the rewrite process with a larger storage parameter version number will continue to execute.

[0215] The above task management strategy combines the uniqueness of UUID, the fast query capability of Hash table and the logical judgment of version number comparison, providing an efficient and reliable task conflict avoidance mechanism for the multi-user concurrent access environment of the database. This helps to ensure the stability and performance of the database system and meet the needs of daily business.

[0216] The flow chart provided by the present embodiment is not intended to indicate that the operation of the method will be performed in any particular order, or that all operations of the method are included in all every case. In addition, the method may include additional operations. Within the scope of the technical thinking provided by the present embodiment method, additional changes may be made to the above method.

[0217] It should be understood that in some embodiments, each part can be implemented by hardware, software, firmware or a combination thereof. In the above embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0218] This embodiment also provides a computer-readable storage medium 20 and a computer device 30 . Fig.10 is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention, Fig.11 is a schematic diagram of a computer device 30 according to one embodiment of the present invention.

[0219] The computer readable storage medium 20 stores the computer program 21, which implements the steps of the method for rewriting a large object of a database in any of the above embodiments when executed by the processor 32. The computer device 30 may include a memory 31, a processor 32, and the computer program 21 stored in the memory 31 and running on the processor 32.

[0220] The computer program 21 for performing the operation of the present invention may be an assembly instruction, an instruction set architecture (ISA) instruction, a machine instruction, a machine-related instruction, a microcode, a firmware instruction, a state setting data, a configuration data of an integrated circuit, or a source code or an object code written in any combination of one or more programming languages ​​and process programming languages. The computer program 21 may be executed entirely on the user's computer, partially on the user's computer, as an independent software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network (including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., using an Internet service provider via the Internet). In some embodiments, in order to perform various aspects of the present invention, an electronic circuit including, for example, a programmable logic circuit, a field programmable gate array (FPGA) or a programmable logic array (PLA) may execute computer-readable program instructions by utilizing the state information of the computer-readable program instructions to personalize the electronic circuit.

[0221] For the purpose of the description of the present embodiment, the computer readable storage medium 20 is a tangible device capable of retaining and storing a computer program 21, which may be any device that can contain, store, communicate, propagate or transmit the program 21 for use with or in conjunction with an instruction execution system, device or apparatus. More specific examples (a non-exhaustive list) of the computer readable storage medium 20 include the following: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disk read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device, and any suitable combination of the foregoing.

[0222] The computer device 30 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smart phone. In some examples, the computer device 30 may be a cloud computing node. The computer device 30 may be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, a program module may include routines, programs, target programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. The computer device 30 may be implemented in a distributed cloud computing environment where remote processing devices linked via a communication network perform tasks. In a distributed cloud computing environment, program modules may be located on a local or remote computing system storage medium including a storage device.

[0223] The computer device 30 may include a processor 32 adapted to execute stored instructions, and a memory 31 providing temporary storage space for the operation of the instructions during operation. The processor 32 may be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory 31 may include a random access memory (RAM), a read-only memory, a flash memory, or any other suitable storage system.

[0224] The computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows data to be input and output with external devices that may be connected to the computer device. The network adapter / interface may provide communication between the computer device and a network, which is typically shown as a communication network.

[0225] At this point, those skilled in the art should recognize that, although multiple exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications that conform to the principles of the present invention can still be directly determined or derived based on the content disclosed in the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and recognized as covering all these other variations or modifications.

Claims

1. A method for rewriting a large object of a database, comprising: Acquire an event of rewriting data in a large object column in the database, wherein the large object column is used to store large object data items of a large object data type; Establishing a rewriting subtask queue and a rewriting process group, wherein the rewriting subtask queue is used to store a plurality of rewriting subtasks divided based on the rewriting events, and the rewriting process group includes a plurality of rewriting processes running in parallel; The rewriting subtasks in the rewriting subtask queue are allocated to the respective rewriting processes of the rewriting process group, and each of the rewriting processes processes the rewriting subtask allocated thereto.

2. The method for rewriting a large object of a database according to claim 1, wherein: The object type of the large object data item includes an in-row object, the metadata and data body of the in-row object are stored in a data table row of the database, and the steps of each rewriting process processing the rewriting subtask assigned to it include: Each of the rewriting processes rewrites the inline objects in the rewriting subtask.

3. The method for rewriting a large object of a database according to claim 1, wherein: The object type of the large object data item includes an out-of-row object, part of the metadata of the out-of-row object is stored in the row of the data table of the database, and the remaining metadata and the data body are stored outside the row of the data table of the database, and the steps of each rewriting process processing the rewriting subtask assigned to it include: Each of the rewriting processes writes the object identifier of the out-of-row object in the rewriting subtask into the out-of-row object temporary table pre-created by the database; The temporary table of out-of-row objects is scanned, and the scanned out-of-row objects are rewritten.

4. The method for rewriting a large object of a database according to claim 1, wherein: The steps to establish a rewriting process group include: Obtaining the queue length of the rewriting subtask queue and the parallelism parameter of the database rewriting task; Determine whether the queue length of the rewriting subtask queue is greater than the parallelism parameter of the database rewriting task; If so, create a rewriting process group whose number of processes is equal to the parallelism parameter value of the database rewriting task; If not, create a rewriting process group whose number of processes is equal to the queue length value of the rewriting subtask queue.

5. The method for rewriting a large object of a database according to claim 1, wherein: The steps to establish a rewrite subtask queue include: Initialize an empty queue in memory as the rewriting subtask queue; Dividing the rewriting task of the large object column into a plurality of rewriting subtasks according to the partitioning condition of the large object column; Adding a plurality of the rewriting subtasks to the rewriting subtask queue in sequence.

6. The method for rewriting a large object of a database according to claim 5, wherein: The step of dividing the rewriting task of the large object column into a plurality of rewriting subtasks according to the partitioning condition of the large object column comprises: Allocating one rewriting subtask to each of the unpartitioned large object columns; One rewriting subtask is respectively assigned to each partitioned column partition of the large object column.

7. The method for rewriting a large object of a database according to claim 1, wherein: The step of obtaining an event of rewriting data in a large object column in the database includes: monitoring modification instructions for the large object column; When the modification instruction is detected, an event of rewriting the data in the large object column is triggered.

8. The method for rewriting a large object of a database according to claim 1, wherein: After the step of each rewriting process independently processing the rewriting subtask assigned to it, the method further includes: Obtaining the completion status of all said rewriting processes; When all the rewriting processes are completed, it is reported that the rewriting task of the large object column is completed.

9. A machine-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the method for rewriting a large object of a database according to any one of claims 1 to 8.

10. A computer device comprising a memory, a processor, and a computer program stored in the memory and running on the processor, wherein the processor implements the method for rewriting a large object of a database according to any one of claims 1 to 8 when executing the computer program.

Images