Large object updating method of database, storage medium and equipment
By identifying and processing references to small objects stored outside the row, the problem of chaotic reference relationships and data loss during the updating of large objects in the database is solved, realizing an efficient and consistent update method and improving the data integrity and performance of the database.
Patent Information
- Application Number
- CN202411048065.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-31
- Publication Date
- 2024-11-22
AI Technical Summary
In databases, large objects stored outside the row may be lost during updates due to confused reference relationships. Existing technologies struggle to handle these update operations efficiently and consistently.
By identifying small objects in off-row storage, examining their reference relationships, and updating data fields in the memory buffer, the integrity and consistency of reference relationships are ensured during the update process, including reading, updating, and writing back data fields, and selecting new referenced objects to replace the target object when necessary.
Efficiently updating small objects in off-row storage avoids confusion in reference relationships and data loss, improves database data integrity and consistency, and enhances update performance.
Smart Images

Figure CN119003547A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of database counting, and in particular to a method, storage medium and device for updating large objects in 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, large objects generally include in-row objects and out-of-row objects. Out-of-row objects may take up a lot of storage space. If the same large object data is stored in multiple records, then this data will appear repeatedly in the database, resulting in a waste of storage space and performance degradation.
[0004] To solve the above problems, the improvements that may be made by those skilled in the art are: according to the size of the large object, the out-of-row objects are divided into small objects stored out-of-row and large objects stored out-of-row, and the large objects stored out-of-row are divided into multiple storage slices, and a deduplication feature value is calculated for each small object stored out-of-row and storage slice of the large object. After the deduplication feature is enabled, the same large object data will usually only retain one copy or only a small amount of duplicate data in the physical storage of the database, and multiple records that reference the data will point to this single storage location. In this way, not only storage space is saved, but also the speed of data access is improved.
[0005] However, after enabling deduplication for the first time, regardless of whether deduplication is currently enabled, objects or slices with reference relationships may be encountered, and the update process needs to be adjusted accordingly. Specifically, when updating a large object, the database cannot simply update the data of the large object because it may reference other objects or be referenced by other objects. Direct updates will lead to confusion in reference relationships and data loss. Summary of the invention
[0006] An object of the first aspect of the present invention is to update small objects stored outside of rows in a targeted manner and properly handle reference relationships of small objects.
[0007] Another object of the first aspect of the present invention is to update small objects stored out-of-line in an efficient and consistent manner.
[0008] Another object of the first aspect of the present invention is to select a new referenced object to replace a small object stored out of row when the small object has been referenced by other objects.
[0009] In particular, according to a first aspect of the present invention, the present invention provides a method for updating a large object of a database, comprising:
[0010] Acquire a data base table of the database, wherein the data base table has at least one large object column, and each of the large object columns includes a plurality of large object data items;
[0011] Acquire a target large object data item to be updated from the large object column;
[0012] Identify the object type of the target large object data item, where the object type includes a small object stored out-of-row, and the data size of the small object stored out-of-row is less than or equal to a preset threshold;
[0013] If it is identified that the target large object data item is the small object stored outside the row, checking the reference relationship of the target large object data item;
[0014] The target large object data item is updated according to the reference relationship.
[0015] Optionally, a reference identifier and a reference count are set in the object record of the large object data item, the reference identifier is the identifier of the object referenced by the large object data item, and the reference count is the number of times the large object data item is referenced by other objects.
[0016] Optionally, the step of checking the reference relationship of the target large object data item includes: checking whether the target large object data item references other objects;
[0017] If other objects are referenced, the step of updating the target large object data item according to the reference relationship includes:
[0018] Reading the pre-fetched data field of the object referenced by the target large object data item into a memory buffer;
[0019] updating the prefetch data field in the memory buffer;
[0020] The updated pre-fetched data field is written back to the target large object data item.
[0021] Optionally, after the step of reading the pre-fetched data field of the object referenced by the target large object data item into the memory buffer, the method further includes:
[0022] Decrement the reference count of the object referenced by the target large object data item by 1;
[0023] The reference identifier of the target large object data item is set to null.
[0024] Optionally, if no other objects are referenced, the step of updating the target large object data item according to the reference relationship includes:
[0025] Reading the pre-fetched data field of the target large object data item into a memory buffer;
[0026] updating the prefetch data field in the memory buffer;
[0027] The updated pre-fetched data field is written back to the target large object data item.
[0028] Optionally, the step of checking the reference relationship of the target large object data item further includes: checking whether the target large object data item is referenced by other objects;
[0029] If it has been referenced by other objects, an object is selected to replace the target large object data item as a new referenced object, and the reference count of the target large object is cleared to zero.
[0030] If it is not referenced by other objects, the step of checking whether the target large object data item references other objects is executed.
[0031] Optionally, the step of selecting an object to replace the target large object data item as a new referenced object includes:
[0032] Select an object from the set of objects that reference the target large object data item as a new referenced object;
[0033] Subtract 1 from the reference count of the target large object data item as the reference count of the new referenced object;
[0034] Copying the data of the target large object data item to the new referenced object;
[0035] The reference identifiers of other objects in the object set that reference the target large object data item are modified to the identifier of the new referenced object.
[0036] Optionally, the step of checking whether the target large object data item is referenced by other objects includes:
[0037] Check whether the reference count of the target large object data item is 0;
[0038] If yes, determining that the target large object data item is not referenced by other objects;
[0039] If not, it is determined that the target large object data item has been referenced by other objects.
[0040] 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 when the computer program is executed by a processor, the computer program implements any one of the above-mentioned large object update methods for a database.
[0041] 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 update methods for a database is implemented.
[0042] The large object update method of the database of the present invention first obtains the target large object data item to be updated from the data base table, then identifies whether the target large object data item is a small object stored outside the row, and if the target large object data item is identified as a small object stored outside the row, then checks the reference relationship of the target large object data item, and finally updates the target large object data item according to the reference relationship. In this way, the small objects stored outside the row can be updated in a targeted manner, and the reference relationship of the small objects can be properly handled, so as to avoid the problem of reference relationship confusion or data loss after the small objects are updated, which is conducive to maintaining the data integrity and consistency of the database.
[0043] Furthermore, the large object update method of the database of the present invention, when the target large object data item is a small object stored outside the row and references other objects, reads the pre-fetched data field of the object referenced by the target large object data item into the memory buffer, updates the pre-fetched data field in the memory buffer and writes back to the target large object data item. When the target large object data item is a small object stored outside the row and does not reference other objects, reads the pre-fetched data field of the target large object data item into the memory buffer, updates the pre-fetched data field in the memory buffer and writes back to the target large object data item. In this way, regardless of whether the target large object data item references other objects, it can be updated in an efficient and consistent manner. This not only improves the performance of database updates, but also ensures the integrity and consistency of the data.
[0044] Furthermore, the large object updating method of the database of the present invention, when it is found that the target large object data item has been referenced by other objects, can select an object from the object set that references the target large object data item as the new referenced object, then reduce the reference count of the target large object data item by 1 as the reference count of the new referenced object, and then copy the data of the target large object to the new referenced object, ensuring that the new referenced object can have the same data content as the target large object, and finally modify the reference identifiers of other objects in the object set that references the target large object data item to the identifier of the new referenced object, so that the object that originally referenced the target large object will now reference the new referenced object, thereby achieving a smooth transition of the reference, which can significantly improve the performance and stability of the database.
[0045] 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
[0046] 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:
[0047] 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;
[0048] 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;
[0049] 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;
[0050] 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;
[0051] 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;
[0052] 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;
[0053] Figure 7is a flowchart of a method for updating a large object in a database according to an embodiment of the present invention;
[0054] Figure 8 is a flow chart of selecting an object to replace a target large object data item according to an embodiment of the present invention;
[0055] Fig. 9 is a flowchart of update processing when a target large object data item references other objects according to an embodiment of the present invention;
[0056] Fig.10 is a flowchart of update processing when a target large object data item does not reference other objects according to one embodiment of the present invention;
[0057] Fig.11 A schematic diagram of a computer readable storage medium according to an embodiment of the present invention;
[0058] Fig.12 Schematic diagram of a computer device according to one embodiment of the present invention. DETAILED DESCRIPTION
[0059] Reference will now be made in detail to embodiments of the present invention, one or more examples of which are illustrated in the accompanying drawings. The various embodiments provided are intended to explain the present invention, not to limit the present invention. In fact, it will be apparent to those skilled in the art that various modifications and variations may be made to the present invention without departing from the scope or spirit of the present invention. For example, a feature illustrated or described as a part of one embodiment may be used together with another embodiment to produce yet another embodiment. Therefore, the present invention is intended to cover such modifications and variations within the scope of the appended claims and their equivalents.
[0060] 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.
[0061] 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.
[0062] The present embodiment provides a method for updating large objects in a database. Before describing the embodiment of the present application in detail, the database in the present embodiment is first described in order to better understand the database environment on which the present embodiment relies. The database environment not only supports the storage and operation of large objects, but also provides sufficient flexibility to perform complex deduplication processing tasks.
[0063] 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.
[0064] 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.
[0065] 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:
[0066]
[0067] 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.
[0068] 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.
[0069] 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.
[0070] 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. 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.
[0071] 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.
[0072] 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).
[0073] 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.
[0074] 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.
[0075] 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 recreated.
[0076] 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.
[0077] 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 the metadata records of other common data types and will not be elaborated here.
[0078] 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.
[0079] 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.
[0080] 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.
[0081] 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.
[0082] 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.
[0083] 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.
[0084] 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.
[0085] 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.
[0086] 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.
[0087] 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> .
[0088] 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.
[0089] 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.
[0090] 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.
[0091] 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.
[0092] 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.
[0093] Specifically, the large object column metadata table sys_klob_column can include the following attributes:
[0094] The data base table OID rel_oid is used when searching for metadata related to the base table.
[0095] 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.
[0096] 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.
[0097] 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.
[0098] 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.
[0099] 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).
[0100] 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.
[0101] 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.
[0102] 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.
[0103] The current storage option lob flags is the current storage option. This 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;
[0104] 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.
[0105] 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.
[0106] 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;
[0107] The number of columns in the partition key, partnatts; only has values in the row corresponding to the object and the Chunk partition table;
[0108] 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;
[0109] 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;
[0110] 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.
[0111] 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.
[0112] 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;
[0113] 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.
[0114] 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.
[0115] Specifically, the large object column-specific storage parameter history table sys_klob_store_params can include the following attributes:
[0116] Object and storage slice table OID tab_oid;
[0117] The storage parameter version number store_ver is defined by the aforementioned tab_oid as store_seq_<col / part uuid> The sequence generation;
[0118] 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.
[0119] 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);
[0120] The key enc_key is the key for encrypting large object columns. It is stored in ciphertext and encrypted with the parent key.
[0121] 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.
[0122] 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.
[0123] 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.
[0124] Specifically, the large object dedup task history table sys_klob_dedup_tasks can include the following attributes:
[0125] Object and storage slice table OID tab_oid, meaning is the same as the tab_oid attribute of other system tables above;
[0126] 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;
[0127] 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.
[0128] 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.
[0129] 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.
[0130] 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).
[0131] 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.
[0132] 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.
[0133] 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.
[0134] 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.
[0135] 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.
[0136] 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.
[0137] 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.
[0138] 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.
[0139] 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.
[0140] 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.
[0141] 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.
[0142] 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 within the first 32k of the object prefetch, and the data above 32k of the object ext_data.
[0143] 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.
[0144] 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.
[0145] store_ver is the storage parameter version number of the object. It is NULL in the storage slice record. This field can also be used to distinguish object records from storage slice records.
[0146] 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.
[0147] 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.
[0148] 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.
[0149] 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.
[0150] 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.
[0151] 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.
[0152] 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.
[0153] 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.
[0154] 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.
[0155] 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.
[0156] 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.
[0157] 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.
[0158] 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.
[0159] 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.
[0160] 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.
[0161] 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.
[0162] 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.
[0163] 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.
[0164] The reference count of the above records can avoid data errors when modifying data or executing DML.
[0165] 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.
[0166] 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 a small space.
[0167] 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.
[0168] 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.
[0169] 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.
[0170] It is understandable that duplicate storage of large objects will lead to a waste of database storage space and performance degradation. When multiple records store the same large object data, the data will be stored repeatedly in the database, which is obviously unnecessary. Therefore, database management systems usually provide large object deduplication features to eliminate data redundancy, optimize storage performance, and improve query and management efficiency.
[0171] As mentioned above, after the deduplication feature is enabled, the same large object data will usually only retain one copy or a small amount of duplicate data in the physical storage of the database, and multiple records that reference the data will point to this single storage location. This not only saves storage space, but also improves the speed of data access.
[0172] However, after enabling deduplication for the first time, regardless of whether deduplication is currently enabled, objects or slices with reference relationships may be encountered, and the update process needs to be adjusted accordingly. Specifically, when updating a large object, the database cannot simply update the data of the large object because it may reference other objects or be referenced by other objects. Direct updates will lead to confusion in reference relationships and data loss.
[0173] Figure 7 is a flowchart of a method for updating a large object in a database according to an embodiment of the present invention. The method for updating a large object in this embodiment is intended to reasonably update small objects stored outside of a row, such as Figure 7 As shown, the method at least includes the following steps S701 to S705.
[0174] Step S701 : obtaining a data base table of a database, wherein the data base table has at least one large object column, and each large object column includes a plurality of large object data items.
[0175] Step S702: Obtain the target large object data item to be updated from the large object column.
[0176] Step S703: Identify the object type of the target large object data item, where the object type includes a small object stored out-of-row, and the data size of the small object stored out-of-row is less than or equal to a preset threshold.
[0177] Step S704: if it is identified that the target large object data item is a small object stored out-of-row, check the reference relationship of the target large object data item.
[0178] Step S705: update the target large object data item according to the reference relationship.
[0179] By using the large object update method of the above embodiment, small objects stored outside the row can be updated in a targeted manner, and the reference relationship of the small objects can be properly handled to avoid confusion in the reference relationship or data loss after the small objects are updated, which is beneficial to maintaining the data integrity and consistency of the database.
[0180] The preset threshold in this embodiment may be 32K, that is, an out-of-row object with a data size less than or equal to 32K is regarded as a small object stored out-of-row, and an out-of-row object with a data size greater than 32K is regarded as a large object stored out-of-row. Of course, the preset threshold is not fixed and can be adjusted according to actual needs, which can better manage large object data in the database.
[0181] In this embodiment, a reference identifier and a reference count are set in the object record of each large object data item, the reference identifier is the identifier of the object referenced by the large object data item, and the reference count is the number of times the large object data item is referenced by other objects. For small objects stored out of row, their reference identifier and reference count are respectively stored in the reference identifier ref_id field and the reference count ref_count field of their object record.
[0182] In some embodiments, the step of checking the reference relationship of the target large object data item includes: checking whether the target large object data item is referenced by other objects, and checking whether the target large object data item references other objects. In this way, it is possible to clearly understand which other objects are involved in updating the target large object data item, so as to fully consider and process various reference relationships, thereby maintaining the integrity and consistency of the data.
[0183] Specifically, the step of checking whether the target large object data item is referenced by other objects may be: checking whether the reference count of the target large object data item is 0. If the reference count is 0, it is determined that the target large object data item is not referenced by other objects, which means that it does not need to be considered as a referenced object when updating. If the reference count is not 0, it is determined that the target large object data item has been referenced by other objects. For example, when the reference count is 3, it means that three objects simultaneously reference the target large object data item, which means that it is necessary to carefully handle the case where it is a referenced object when updating to ensure the integrity of the reference.
[0184] Further, the step of checking whether the target large object data item references other objects may be: checking whether the reference identifier of the target large object data item is empty, if the reference identifier is empty, determining that the target large object data item does not reference other objects, if the reference identifier is not empty, determining that the target large object data item references other objects. By checking the reference identifier, it is possible to more accurately determine whether the target large object data item references other objects, thereby providing necessary information and basis for subsequent update operations, which also ensures the accuracy and security of the update operation.
[0185] In some embodiments, after checking whether the target large object data item is referenced by other objects, if it is found that the target large object data item has been referenced by other objects, then before updating the target large object, it is necessary to select an object to replace the target large object data item as the new referenced object, and clear the reference count of the target large object, and then execute the step of checking whether the target large object data item references other objects. If it is found that the target large object data item is not referenced by other objects, the step of checking whether the target large object data item references other objects can be directly executed.
[0186] Figure 8 FIG. 1 is a flowchart of selecting an object to replace a target large object data item according to an embodiment of the present invention. Figure 8 As shown, selecting an object to replace the target large object data item as a new referenced object may include the following steps S801 to S804.
[0187] Step S801, select an object from the object set that references the target large object data item as a new referenced object. If only one object references the target large object data item, then the object directly replaces the target large object data item. If multiple objects reference the target large object data item at the same time, then the first object among them can replace the target large object data item.
[0188] Step S802: subtract 1 from the reference count of the target large object data item as the reference count of the new referenced object. This is because the target large object data item is about to be updated, and the reference relationship originally maintained by it will be transferred to the new referenced object, so the reference count of the new referenced object needs to be increased accordingly.
[0189] Step S803, copy the data of the target large object to the new referenced object. This is equivalent to creating a data backup, ensuring that after the target large object data item is updated, the original data can still be accessed and used through the new referenced object, thereby avoiding data loss.
[0190] Step S804, modify the reference identifiers of other objects in the object set that reference the target large object data item to the identifier of the new referenced object. In this way, all objects that originally referenced the target large object data item will now reference the new referenced object, maintaining the reference integrity and consistency of the database.
[0191] At this point, no other object references the target large object data item, so the reference count of the target large object data item can be cleared to zero, which helps to release resources and simplify database state management to a certain extent.
[0192] To facilitate understanding of the following text, the prefetch data field (i.e., prefetch field) of small objects is explained again here. As mentioned earlier, the prefetch field is the first 32K of data of the object in the object record. Since the data of small objects is less than or equal to 32K, all the data of small objects will be stored in the prefetch field.
[0193] Fig. 9 is a flowchart of update processing when a target large object data item references other objects according to an embodiment of the present invention. Fig. 9 As shown, in the case where the target large object data item references other objects, updating the target large object data item according to the reference relationship may include the following steps S901 to S905.
[0194] Step S901, read the pre-fetched data field of the object referenced by the target large object data item into the memory buffer. Since the target large object data item references other objects, the target large object data item only retains the identifier of the object it references, and the actual data is stored in the object it references. Therefore, when updating the target large object data item, it is necessary to read data from the pre-fetched data field of the object it references.
[0195] Step S902, updating the pre-fetched data field in the memory buffer. This step is performed in the memory, has a relatively high speed, and does not directly affect the performance of the database.
[0196] Step S903, the reference count of the object referenced by the target large object data item is reduced by 1. Since the target large object data item will be updated, the reference count of the object it originally referenced needs to be reduced accordingly. This is because after the update, the target large object may no longer need to reference the object. By reducing the reference count, the system can track and manage the reference status of the object to ensure the accuracy of the data.
[0197] Step S904, the reference identifier of the target large object data item is set to null. This is to ensure that during the update process, the target large object no longer references any object, avoiding confusion or errors in the reference relationship. At the same time, this also provides a basis for re-establishing the correct reference relationship later.
[0198] Step S905: write the updated pre-fetched data field back to the target large object data item, which ensures that the target large object data item contains the updated pre-fetched data, so that related resources can be quickly loaded and used during subsequent access.
[0199] Fig.10 is a flowchart of update processing when a target large object data item does not reference other objects according to an embodiment of the present invention. Fig.10 As shown, in the case where the target large object data item does not reference other objects, updating the target large object data item according to the reference relationship may include the following steps S1001 to S1003.
[0200] Step S1001, read the pre-fetched data field of the target large object data item into the memory buffer. Since the target large object data item does not reference other objects, the actual data of the target large object data item is stored in its own pre-fetched data field, and only data needs to be read from its own pre-fetched data field when updating.
[0201] Step S1002, updating the pre-fetched data field in the memory buffer. This step is performed in the memory, has a relatively high speed, and does not directly affect the performance of the database.
[0202] Step S1003: write the updated pre-fetched data field back to the target large object data item, which ensures that the target large object data item contains the updated data, so that the relevant resources can be quickly loaded and used during subsequent access.
[0203] It is worth noting that when executing step S803 above, if it is found that the target large object data item references other objects, copying the data of the target large object to the new referenced object actually means copying the data of the object referenced by the target large object to the new referenced object. If it is found that the target large object data item does not reference other objects, the data of the target large object itself will be directly copied to the new referenced object.
[0204] In addition, it should be noted that in some special cases, after the small object is updated, its data size may exceed 32K. At this time, when the updated pre-fetched data field is written back to the small object, the first 32K of data is still written back to the pre-fetched data field, and the data exceeding 32K will be written to the additional data field in the form of storage slices, that is, the ext_data field. In other words, after this update, the small object becomes a large object, and when the object is updated later, it will be processed in the same way as a large object.
[0205] Since the database system provided by this embodiment has a deduplication function, even if the deduplication function is currently disabled, it may have been enabled before, and there may be reference relationships between objects and slices. Therefore, regardless of whether the deduplication feature is currently enabled, various DML operations including deletion may encounter objects or slices with reference relationships. The difference is: 1) If deduplication is currently enabled, a new deduplication feature value dedup_feat needs to be calculated and filled in when inserting and updating objects or slices; 2) If deduplication is not currently enabled, the deduplication feature value dedup_feat is set to NULL when inserting and updating objects or slices.
[0206] 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.
[0207] 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.
[0208] This embodiment also provides a computer-readable storage medium 20 and a computer device 30 . Fig.11 is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention, Fig.12 is a schematic diagram of a computer device 30 according to one embodiment of the present invention.
[0209] The computer readable storage medium 20 stores the computer program 21, which implements the steps of the large object update method of the database in any of the above embodiments when the computer program 21 is 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.
[0210] 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.
[0211] 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.
[0212] 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.
[0213] 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.
[0214] 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.
[0215] 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 updating large objects in a database. include: Acquire a data base table of the database, wherein the data base table has at least one large object column, and each of the large object columns includes a plurality of large object data items; Acquire a target large object data item to be updated from the large object column; Identify the object type of the target large object data item, where the object type includes a small object stored out-of-row, and the data size of the small object stored out-of-row is less than or equal to a preset threshold; If it is identified that the target large object data item is the small object stored outside the row, checking the reference relationship of the target large object data item; The target large object data item is updated according to the reference relationship.
2. The method for updating a large object in a database according to claim 1, in, A reference identifier and a reference count are set in the object record of the large object data item. The reference identifier is the identifier of the object referenced by the large object data item, and the reference count is the number of times the large object data item is referenced by other objects.
3. The method for updating a large object in a database according to claim 2, in, The step of checking the reference relationship of the target large object data item includes: checking whether the target large object data item references other objects; If other objects are referenced, the step of updating the target large object data item according to the reference relationship includes: Reading the pre-fetched data field of the object referenced by the target large object data item into a memory buffer; updating the prefetch data field in the memory buffer; The updated pre-fetched data field is written back to the target large object data item.
4. The method for updating a large object in a database according to claim 3, in, After the step of reading the pre-fetched data field of the object referenced by the target large object data item into the memory buffer, the method further includes: Decrement the reference count of the object referenced by the target large object data item by 1; The reference identifier of the target large object data item is set to null.
5. The method for updating a large object in a database according to claim 3, in, If no other object is referenced, the step of updating the target large object data item according to the reference relationship includes: Reading the pre-fetched data field of the target large object data item into a memory buffer; updating the prefetch data field in the memory buffer; The updated pre-fetched data field is written back to the target large object data item.
6. The method for updating a large object in a database according to claim 3, in, The step of checking the reference relationship of the target large object data item further includes: checking whether the target large object data item is referenced by other objects; If it has been referenced by other objects, an object is selected to replace the target large object data item as a new referenced object, and the reference count of the target large object is cleared to zero. If it is not referenced by other objects, the step of checking whether the target large object data item references other objects is executed.
7. The method for updating a large object in a database according to claim 6, in, The step of selecting an object to replace the target large object data item as a new referenced object includes: Select an object from the set of objects that reference the target large object data item as a new referenced object; Subtract 1 from the reference count of the target large object data item as the reference count of the new referenced object; Copying the data of the target large object data item to the new referenced object; The reference identifiers of other objects in the object set that reference the target large object data item are modified to the identifier of the new referenced object.
8. The method for updating a large object in a database according to claim 6, in, The step of checking whether the target large object data item is referenced by other objects includes: Check whether the reference count of the target large object data item is 0; If yes, determining that the target large object data item is not referenced by other objects; If not, it is determined that the target large object data item has been referenced by other objects.
9. A machine-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the large object update method for 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 large object update method for a database according to any one of claims 1 to 8 when executing the computer program.
