Partition storage method for large objects in database and related products
By adopting partition storage methods in the database, partitioning operations on large object data is solved, and the problem of low positioning efficiency of large object data in traditional databases is achieved, rapid search and efficient storage are achieved, and processing efficiency is improved.
Patent Information
- Application Number
- CN202411046741.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-31
- Publication Date
- 2024-11-15
AI Technical Summary
When traditional databases process large object type data, the off-line storage method leads to complex reading and writing processes and inefficient efficiency, and lacks reasonable large object partitioning methods to quickly locate data.
A partition storage method for large objects in a database is proposed. By obtaining partition instructions, the original partitioning method is determined, and the object table and storage slice table are partitioned according to different types, including list partitions and hash partitions, and partitioning information is recorded to the element. Data table, optimize the execution of data manipulation statements.
It realizes the rapid search and efficient storage of large object data, improves the database's processing efficiency of large object data, and simplifies the data manipulation process.
Smart Images

Figure CN118964355A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of databases, and in particular to a partition storage method for large objects in a database and related products. 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] Traditional database management systems often use out-of-row storage when processing large object type data. However, this storage method has complex reading and writing processes and low efficiency when reading and writing large objects. Therefore, a reasonable large object partitioning method is needed for fast positioning. Summary of the invention
[0004] In view of the above problems, the present invention proposes a storage method for temporary large objects in a database and related products that overcome the above problems or at least partially solve the above problems.
[0005] An object of the present invention is to optimize the partitioning of large objects.
[0006] A further object of the present invention is to facilitate rapid search for corresponding large object data.
[0007] In particular, the present invention provides a partition storage method for large objects in a database, comprising:
[0008] Get the partition instruction, which is used to partition the data of large objects in the database;
[0009] According to the large object, an object table and a storage slice table corresponding to the large object are obtained. The object table is used to store description information of the large object, and the storage slice table is used to store storage slice data corresponding to the large object.
[0010] Determine the original partitioning method according to the partitioning instruction;
[0011] The partition type of the original partition method is determined, and different partition operations are performed on the object table and the storage slice table according to different types of the original partition methods.
[0012] Optionally, the original partitioning method includes partitioning the object table and the storage slice table by regular data columns of a data base table in the database, the data base table including at least one large object column and at least one regular data column;
[0013] The steps of performing different partitioning operations on the object table and the storage slice table according to different types of original partitioning methods include: when the original partitioning method is to partition the object table and the storage slice table according to regular data columns, calling a preset hook function to convert the original partitioning method into a preset partitioning method, the preset partitioning method is to perform list partitioning through internal partitioning keys recorded in the object table and the storage slice table; partitioning the object table and the storage slice table according to the preset partitioning method.
[0014] Optionally, the step of calling a preset hook function to convert the original partition mode into the preset partition mode includes:
[0015] Use the regular data column as the partition key to partition the large object column in the data base table to obtain the partition result;
[0016] Determine the partition sequence number corresponding to each large object in the large object column based on the partition result;
[0017] According to the fixed unique identifier of the large object, the partition number corresponding to the large object is recorded in the object table and storage slice table corresponding to the large object as the internal partition key.
[0018] Optionally, the step of partitioning the object table and the storage slice table according to a preset partitioning method includes:
[0019] List partitioning is performed based on the internal partition key of the records in the object table and the storage slice table respectively.
[0020] Optionally, after the step of partitioning the object table and the storage slice table according to a preset partitioning method, the following steps are further included:
[0021] The parameter information of the original partitioning method is recorded in the large object column metadata table of the database. The large object column metadata table is used to record the description information of the object table and the storage slice table.
[0022] Optionally, after the step of recording the parameter information of the original partitioning mode into the large object column metadata table of the database, the step further includes:
[0023] Obtaining data manipulation statements, where the data manipulation statements are used to query and / or modify data in a database;
[0024] Obtain parameter information of the original partitioning method from the large object column metadata table;
[0025] Determine the partition number where the manipulation target of the data manipulation statement is located according to the parameter information of the original partition mode;
[0026] Search the operation target in the partition corresponding to the partition number and execute the data operation statement.
[0027] Optionally, the partitioning method further includes performing hash partitioning according to a fixed unique identifier of an object in the object table and the storage slice table;
[0028] The steps of performing different partitioning operations on the object table and the storage slice table according to different types of partitioning methods include: when the partitioning method is hash partitioning based on the object fixed unique identifier in the object table and the storage slice table, determining the partition setting according to the partition instruction; calling a preset hash algorithm to calculate the object fixed unique identifier to obtain a calculation result; determining the target partition corresponding to the object fixed unique identifier according to the calculation result and the partition setting; and transferring the data of the object table and the storage slice table to the corresponding target partitions according to the object fixed unique identifier.
[0029] According to another aspect of the present invention, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the steps of any of the above-mentioned partition storage methods for large objects in a database are implemented.
[0030] According to another aspect of the present invention, a computer program product is provided, which includes a computer program. When the computer program is executed by a processor, the steps of any of the above-mentioned partition storage methods for large objects in a database are implemented.
[0031] According to another aspect of the present invention, a computer device is provided, comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, and the processor implements the steps of any of the above-mentioned partitioned storage methods for large objects in a database when executing the machine executable program.
[0032] The partition storage method of a large object in a database of the present invention obtains a partition instruction, the partition instruction is used to perform a partition operation on the data of the large object in the database; obtains an object table and a storage slice table corresponding to the large object according to the large object, the object table is used to store the description information of the large object, and the storage slice table is used to store the storage slice data corresponding to the large object; determines the original partition mode according to the partition instruction; determines the partition type of the original partition mode, and performs different partition operations on the object table and the storage slice table according to different types of the original partition mode. Through this method, the original partition mode can be converted into a partition mode suitable for this database according to different original partition modes, so as to facilitate the rapid search of large object data.
[0033] 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
[0034] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:
[0035] 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;
[0036] 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;
[0037] 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;
[0038] 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;
[0039] Figure 5 is a schematic diagram of an object table in a data table having a large object column according to an embodiment of the present invention;
[0040] Figure 6 is a schematic diagram of storing a slice table in a data table with a large object column according to an embodiment of the present invention;
[0041] Figure 7 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;
[0042] Figure 8 It is a flowchart of a partition storage method for large objects in a database according to an embodiment of the present invention;
[0043] Fig. 9 is a schematic diagram of a computer program product according to an embodiment of the present invention;
[0044] Fig.10 is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and
[0045] Fig.11 is a schematic diagram of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION
[0046] 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.
[0047] 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.
[0048] This embodiment provides a partition storage method for large objects in a database. Before describing the embodiment of the present application in detail, the database in this embodiment is first described to better understand the database environment on which this embodiment relies. The database environment not only supports the storage and operation of large objects, but also provides sufficient flexibility to perform complex rewriting tasks.
[0049] 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.
[0050] 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, and each storage management table space stores an object table corresponding to the large object column and a storage slice table corresponding to the storage slice. The object table is used to store the description information of the large object, and the storage slice table is used to store the storage slice data corresponding to the large object.
[0051] 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 table and the storage slice table according to the object fixed unique identifier of the column object to obtain the object identifier of the object table and the storage slice table. The creation instruction can use SQL (Structured Query Language), for example. Taking a simple case as an example, assuming that the 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 can be:
[0052] CREATE TABLE user_biz_data(
[0053] col_a VARCHAR(255),
[0054] col_b VARCHAR(255),
[0055] col_x KLOB,
[0056] col_y KLOB );
[0058] 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.
[0059] 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.
[0060] 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 tables and indexes in the above Schema to store and manage large objects. The object itself will be divided into small storage slices (Chunk) and stored in these storage slice 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 table and a storage slice table. In some embodiments, when partitioning is not required, each large object column has an object table and a storage slice table.
[0061] For example, the sys_klob_sch Schema can set up matching object tables and storage slice tables for two unpartitioned large object columns col_x and col_y, where the object tables are named tab_colx_uuid and tab_coly_uuid, and the storage slice tables are named tabc_colx_uuid and tabc_coly_uuid. The object table and the storage slice table generate globally unique identifiers using randomly generated UUIDs, respectively. The format of the globally unique identifier can be a combination of a prefix (for example, tab_ as the prefix mentioned above) and a UUID to meet the naming requirements of the names of database objects. 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.
[0062] The large object column metadata table preset in the database system table records the description information of the object table and the storage slice table. To manage large objects, the database system establishes a set of dedicated system tables to store the correspondence between large object columns in user tables and these database objects, as well as the unique storage parameters of 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.
[0063] 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 table and the storage slice table recorded in the large object column metadata table includes the storage table object identifier (which can be named tab_oid and tabc_oid, i.e., the OIDs of the above-mentioned object and storage slice table. In the case of no partitioning, each large object column has a corresponding tab_oid and tabc_oid. If the large object column is partitioned, each large object column partition has a corresponding tab_oid and tabc_oid).
[0064] 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.
[0065] 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 relation attribute table in the database system table (or system metadata table, which may be named pg_attribute) also records other basic attributes of large object columns that are consistent with other regular data types.
[0066] 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 of the object table (also the object identifier of the storage slice table) and the storage parameters, which 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.
[0067] In some embodiments, the storage table object identifier tab_oid of the object table and the deduplication task information are recorded in the deduplication task history table sys_klob_dedup_tasks preset in the database system table, wherein the deduplication task information includes the deduplication task sequence number and the deduplication task identifier, which are used to determine the execution status of the deduplication task.
[0068] 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.
[0069] Each storage management table space also stores the primary key index of the object table and the primary key index of the storage slice table. The primary key index is used to search for large object data items and / or storage slices in the object table and / or storage slice table; and the large object column metadata table also records the information of the primary key index. The primary key index of the object table can generally use the hash index method. The search efficiency is improved by establishing an index. The primary key index of the storage slice table can generally use a Btree index, which has two key value columns (the fixed unique identifier of the object and the position of the storage slice starting point in the object) and an inclusion column (storage slice length). The starting position and length of the storage slice table in the object are measured in logical units, such as bytes for BLOBs and characters for CLOBs / NCLOBs; when searching for storage slices involved in the read-write range, a range query is performed on the three columns in the index to filter out the storage slices that need to be accessed.
[0070] Reference column indexes are also set in the object table and storage slice table respectively. The object reference column index is used to quickly query the reference relationship between different objects in the object table, and the storage slice reference column index is used to query the reference relationship between different storage slices in the storage slice table. The reference column index is established to improve the storage efficiency of finding reference relationships.
[0071] 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 the object table and the storage slice table, and the deduplication task sequence is used to record the deduplication process of the object table and the storage slice table, so that these two sequences can be used to track the tasks of rewriting data and deduplicating large objects when modifying storage options.
[0072] 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.
[0073] 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.
[0074] 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 columns col_a and col_b are stored according to the original storage logic.
[0075] 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.
[0076] In the system tables, the relational attribute table (or system metadata table) 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.
[0077] 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.
[0078] 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 the col_x object table and storage slice table for data storage, indexes are also created for the primary key and reference columns of the object table and storage slice table. For example, indexes are created for the primary key and reference columns of the object table (which can be hash indexes).<colx_uuid> ,ref_<colx_uuid> , create indexes idxc_ for the primary key and reference columns of the slice table<colx_uuid> (can be a btree index), refc_<colx_uuid> (Can be a hash index).
[0079] The col_x object table can be named with the prefix tab_ plus colx_uuid to store object records. The col_x storage slice table can be named with the prefix tabc_ plus colx_uuid to store storage slice records. Both use the UUID corresponding to the object as the globally unique identifier. The record of the object table is essentially the first storage slice (Chunk) record (Chunk) of the object.<colx_uuid> Primary key index idx_<colx_uuid> Can be used to quickly find objects, tabc_<colx_uuid> Primary key index idxc_<colx_uuid> It can be used to quickly find storage slices. When object deduplication is supported, objects are allowed to reference other objects and chunks are allowed to reference other chunks.<colx_uuid> Reference column index ref_<colx_uuid> Can be used to speed up the query operation of reference relationship between objects, tabc_<colx_uuid> Reference column index refc_<colx_uuid> It can be used to speed up query operations that store reference relationships between slices.
[0080] 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.
[0081] 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 (or system metadata table) pg_attribute, storage parameter history table sys_klob_store_params, and large object column deduplication task history table sys_klob_dedup_tasks.
[0082] The relation attribute table (or system metadata 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 OIDrel_oid and the column number attr_no of the large object column in the base table can be used to locate the large object column in the data base table.
[0083] 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 (or system metadata 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. When the large object column is not partitioned, each record in the relational attribute table (or system metadata table) pg_attribute has a corresponding record in the large object column metadata table sys_klob_column. When the large object column is partitioned, each partition will also have a corresponding record in the large object column metadata table sys_klob_column.
[0084] Specifically, the large object column metadata table sys_klob_column can include the following attributes:
[0085] The data base table OID rel_oid is used when searching for metadata related to the base table.
[0086] 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.
[0087] The object table OID tab_oid is used to store the OID of the large object. 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.
[0088] Object primary key index OID idx_<colx_uuid> , the OID of the index (which can be a hash index) of the primary key column of the object table. If the large object column is not partitioned, each large object column corresponds to a primary key index. If the large object column is partitioned, each partition also corresponds to such an index.
[0089] Object reference column index OID ref_<colx_uuid> : The OID of the index (Hash index) of the object table application object. 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.
[0090] Storage slice table OID tabc_oid is used to store the OID of the storage slice. When the large object column is not partitioned, each large object column corresponds to such a table. When the large object column is partitioned, each partition also corresponds to such a table.
[0091] Storage slice primary key index OID pkc_<colx_uuid> , the OID of the index (which can be a btree index) of the primary key column of the storage slice table, which has two key value columns (the fixed unique identifier of the object and the position of the storage slice starting point in the object) and one inclusion column (the storage slice length). The starting point position and length of the storage slice table in the object are measured in logical units, such as bytes for BLOB and characters for CLOB / NCLOB. When searching for storage slices involved in the read-write range, a range query is performed on the three columns in the index to filter out the storage slices that need to be accessed;
[0092] Storage slice reference column index OID refc_<colx_uuid> : The OID of the index (hash index) of the storage slice table application object / storage slice identification column ref_id, a hash index made on the storage slice reference column, including the UUID of the object to which the referenced storage slice belongs and the starting position of the storage slice in the object to which it belongs;
[0093] The tablespace OID tbs_oid is the OID of the tablespace where the object table, the slice table, and its indexes 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).
[0094] The storage parameter version number sequence OIDstore_seq_oid is the storage parameter version number sequence store_seq_ for large object columns or large object column partitions.<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.
[0095] 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.
[0096] 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.
[0097] 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;
[0098] 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.
[0099] 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.
[0100] Partition strategy partstrat, used to record partition strategy. Partition strategy can include h hash partition, l list partition, r range partition table. This attribute has values only in the rows corresponding to the object partition table and the storage slice partition table.
[0101] The number of columns in the partition key, partnatts; only has values in the corresponding rows of the object partition table and the storage slice partition table;
[0102] 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 the corresponding rows of the object partition table and the storage slice partition table;
[0103] The partition key column number vector partattrs is an array of length partnatts values, indicating which table columns are components 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, which is only present in the corresponding rows of the object table and storage slice table;
[0104] Partition key operator class OID vector partclass. For each column in the partition key, this field contains the OID of the operator class to be used. It has a value only in the corresponding rows of the object partition table and the storage slice partition table.
[0105] 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 corresponding rows of the object partition table and the storage slice partition table.
[0106] 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 are values only in the rows corresponding to the object partition table and the storage slice partition table;
[0107] 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.
[0108] 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.
[0109] Specifically, the large object column-specific storage parameter history table sys_klob_store_params can include the following attributes:
[0110] The object table OID tab_oid (which can also be the OID of the storage slice table);
[0111] The storage parameter version number store_ver is defined by the aforementioned tab_oid as store_seq_<col / part uuid> The sequence generation;
[0112] 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.
[0113] 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);
[0114] The key enc_key is the key for encrypting large object columns. It is stored in ciphertext and encrypted with the parent key.
[0115] 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.
[0116] 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.
[0117] 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.
[0118] Specifically, the large object dedup task history table sys_klob_dedup_tasks can include the following attributes:
[0119] Object table OID tab_oid, meaning is the same as the tab_oid attribute of other system tables above;
[0120] The deduplication task sequence number dedup_ord is the sequence number of the deduplication task sequence dedup_seq_ by the object table OID for the large object column or the large object column partition.<col / part uuid> The sequence generation;
[0121] 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.
[0122] 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.
[0123] 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.
[0124] 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 globally unique fixed identifier).
[0125] 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.
[0126] 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.
[0127] The out-of-row large object storage structure only contains the LOB object control block descriptor and the LOB object globally unique fixed 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.
[0128] 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.
[0129] 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 globally unique fixed 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 tables sys_klob_column and sys_klob_store_params. LOB data contains the data of the large object.
[0130] 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.
[0131] The object table and storage slice table are tables that actually store large object data. During database operation, each large object column or column partition is equipped with an object table and a storage slice table. This embodiment optimizes and improves the object table and the storage slice table, which is significantly different from the existing PostgreSQL and has obvious advantages.
[0132] 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.
[0133] 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.
[0134] Figure 5 This is a schematic diagram of an object table in a data table with a large object column according to an embodiment of the present invention. In the scheme of this embodiment, the object table contains object metadata and data within the first 32K, which is used to accelerate the operation of some functions. The portion of the object that exceeds 32K in size will be stored in the storage slice table. A large-volume (over 32K) object 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, a smaller storage slice size is suitable; if the average size of the objects in the column is large, a larger storage slice size is suitable. For TB-level large objects, it is recommended to use the largest storage slice size (1GB).
[0135] Specifically, the object table in this solution contains the following fields:
[0136] lob_id: A UUID-type object unique identifier. UUID-type identifiers can be considered unique in any case. Even if an object is imported into another database, this identifier can remain unchanged and no conflict will occur.
[0137] part_key: The partition key used internally by the system, which is generally a 4-byte 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 identifier, this field is NULL.
[0138] store_ver: The storage parameter version number of the object.
[0139] ref_id: If this field has a value, it is the identifier (UUID) of the referenced object, indicating that the object is exactly the same as the referenced object. If this field is not NULL, the prefetch field of the record is NULL. It should be noted that only when one object is exactly the same as another object can there be a reference relationship between the two. If only some chunks are the same, there is only a reference relationship between the chunks, and no reference relationship between the objects.
[0140] ref_count: represents the number of times this object is referenced by other objects. If the object is not referenced, the value of this field is 0.
[0141] dedup_feat: The deduplication feature value of the entire object. It is a binary data block whose length is determined by the deduplication feature value algorithm. If the deduplication function is not enabled, this field is NULL. If the storage slices under the object are inserted, deleted, or updated, making the original deduplication feature value of the object invalid, this field should also be set to NULL so that the new feature value can be identified and filled in during the deduplication operation.
[0142] prefetch: The data within the first 32KB of the object in the object record is stored according to the object storage parameter settings and may be compressed and encrypted.
[0143] Figure 6 Schematic diagram of storing a slice table in a data table with a large object column according to an embodiment of the present invention. The stored slice table in the solution of this embodiment includes the following fields:
[0144] lob_id: A UUID-type object unique 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. Both the object table and the storage slice table of this database store the object fixed unique identifier, so you can choose to associate the object fixed unique identifier as a foreign key. This foreign key can indicate the relationship between the storage slice table and the object table in DML operations, and can automatically perform operations such as reference integrity checks and cascade deletions. This can simplify the implementation of large object DML operations to a certain extent.
[0145] start_pos: stores the position of the slice start point in the object. The unit for BLOB type is byte, and the unit for CLOB / NCLOB type is character.
[0146] chunk_len: storage slice length, the unit is byte for BLOB type and character for CLOB / NCLOB type.
[0147] part_key: The partition key used internally by the system, of type 4-byte signed integer. When the user chooses to use other columns in the base table as the partition key for 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 identifier, this field is NULL.
[0148] ref_lob_id: If this field has a value, it is the identifier (UUID) of the object to which the referenced storage slice belongs. If this field is not NULL, the ext_data field of the record is NULL. It should be noted that this field and the ref_start field together constitute the unique identifier of the referenced storage slice, so both must have values or be NULL, and only when one storage slice is exactly the same as another storage slice can the two have a reference relationship.
[0149] ref_start: If this field has a value, it is the starting position of the referenced storage slice in the object to which it belongs. It should be noted that this field and the ref_start field together constitute the unique identifier of the referenced storage slice, so both must have values or be NULL.
[0150] ref_count: represents the number of times this storage slice is referenced by other storage slices. If the storage slice is not referenced, the value of this field is 0.
[0151] dedup_feat: stores the deduplication feature value of the slice itself. It is a binary data block whose length is determined by the deduplication feature value algorithm. If deduplication is not enabled, this field is NULL.
[0152] ext_data: This field contains the data of the large object above 32KB. The data in this field will be stored according to the object storage parameter settings, and may also be compressed and encrypted.
[0153] 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.
[0154] 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 can be quickly found based on the btree index. 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 changed, which greatly reduces the amount of data that needs to be rewritten during the update and improves performance.
[0155] 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.
[0156] 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 7 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.
[0157] Similarly, if a certain large object data item D is the same as another large object data item C, then large object data item D may store the identifier of large object data item C instead of storing the data. Large object data item C records a reference count.
[0158] The reference count of the above records can avoid data errors when modifying data or executing DML.
[0159] 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.
[0160] Among them, for small objects stored in-line, their metadata and data body are stored in-line in the form of an in-line control block;
[0161] Small objects stored out-of-line have some metadata stored in-line, and the rest of the metadata and data body stored in the object table in the form of an out-of-line object control block and an object record;
[0162] For large objects stored out-of-line, some of their metadata is stored in-line, the rest of the metadata and the first 32K of data are stored in the object table, and data above 32K is stored in a set of records in the storage slice table;
[0163] 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.
[0164] Traditional database management systems often use out-of-row storage when processing large object type data. However, this storage method has complex reading and writing processes and low efficiency when reading and writing large objects. Therefore, a reasonable large object partitioning method is needed for fast positioning.
[0165] To solve the above problems, the present invention provides a partition storage method for large objects in a database. Figure 8 FIG. 1 is a flow chart of a partition storage method for a large object in a database according to an embodiment of the present invention. Figure 8 As shown, the partition storage method for large objects in the database at least includes the following steps S801 to S804.
[0166] Step S801, obtaining a partition instruction. The partition instruction is used to perform a partition operation on the data of a large object in a database. In some optional embodiments, the partition instruction may be obtained by inputting a data definition statement, which is used to define a database object in a database; and obtaining the partition instruction according to the data definition statement. For example, a DDL (Data Definition Language) statement is mainly used to define or change the structure of a table (TABLE), data types, links and constraints between tables, and other initialization work. Those skilled in the art may select a specific implementation method according to actual conditions.
[0167] Step S802, according to the large object, obtain the object table and storage slice table corresponding to the large object. Figure 5 It is generally used to store the description information of large objects. Figure 6 The storage slice data corresponding to the large object is generally used to store the storage slice data. In some optional embodiments, such as Figure 2 The large object column metadata table sys_klob_column shown records the object table OID tab_oid and the storage slice table OID tabc_oid. Therefore, the Oids of the object table and the corresponding slice table can be directly obtained from the large object column metadata table sys_klob_column.
[0168] In some other optional embodiments, since the object table and the storage slice table are associated with each other through the object fixed unique identifier as a foreign key, those skilled in the art can also obtain the corresponding storage slice table through the foreign key after obtaining the object table corresponding to the large object. Those skilled in the art can select the method of obtaining the object table and the storage slice table according to actual conditions.
[0169] Step S803, determining the original partitioning method according to the partitioning instruction.
[0170] Step S804, determining the partition type of the original partition mode, and performing different partition operations on the object table and the storage slice table according to different types of the original partition mode.
[0171] This method can be used to convert the original partitioning method into a partitioning method suitable for this database, thereby facilitating fast search of large object data.
[0172] In some optional embodiments, the original partitioning method may generally include partitioning the object table and the storage slice table by the regular data column of the data base table in the database, and the data base table includes at least one large object column and at least one regular data column. The steps of performing different partitioning operations on the object table and the storage slice table according to different types of the original partitioning method may generally include: in the case where the original partitioning method is to partition the object table and the storage slice table according to the regular data column, calling the preset hook function to convert the original partitioning method into the preset partitioning method, the preset partitioning method is to perform list partitioning by the internal partitioning key recorded in the object table and the storage slice table; partitioning the object table and the storage slice table according to the preset partitioning method. Since the partitioning mechanism of the database itself is to partition the table based on the partitioning key column of a table itself, and in the database of this embodiment, the data of the large object column is actually stored in the object table and the storage slice table, it is necessary to process it accordingly during the partitioning process of the user, so as to partition the object table and the storage slice table synchronously. An optional method is to operate by the preset hook function during the execution of the partitioning instruction, so as to partition the data in the object table and the storage slice table by the regular data column. By operating the preset hook function, when the original partitioning method is to partition the object table and the storage slice table according to the regular data columns, it can be converted into a list partitioning method, thereby realizing the partitioning operation of the object table and the storage slice table.
[0173] In some optional embodiments, the step of calling a preset hook function to convert the original partitioning mode into the preset partitioning mode may generally include: using the regular data column as the partition key to partition the large object column in the data base table to obtain the partitioning result; determining the partition serial number corresponding to each large object in the large object column according to the partitioning result; recording the partition serial number corresponding to the large object in the object table and the storage slice table corresponding to the large object according to the fixed unique identifier of the large object as the internal partition key. Figure 5 and Figure 6 As shown in the figure, the object table and the storage slice table are both set with the partition key part_key used internally by the system. After the large object is initially partitioned according to the original partitioning method, the partition number corresponding to each large object will be displayed in the partition result. Then the partition number corresponding to the large object can be recorded in the part_key column in the object table and storage slice table corresponding to the large object as the internal partition key of the object table and storage slice table.
[0174] Optionally, the step of partitioning the object table and the storage slice table according to a preset partitioning method may generally include: performing list partitioning according to internal partition keys recorded in the object table and the storage slice table, respectively.
[0175] Through this conversion method, no matter what the original partitioning method of the data base table is, it can always be mapped to the list partitioning method. For example, if the original partitioning method is range partitioning, after the data base table divides the large object column into multiple partitions through range partitioning, each partition has its corresponding large object. Then, according to the identifier of the large object, the partition number of the large object is recorded in the object table and the storage slice table as the internal partition key for list partitioning, thereby completing the conversion of the partitioning method.
[0176] In some optional embodiments, after the step of partitioning the object table and the storage slice table according to the preset partitioning method, the following step may be generally performed: recording the parameter information of the original partitioning method into the large object column metadata table of the database, where the large object column metadata table is used to record the description information of the object table and the storage slice table. A specific example of the parameter information is as follows: Figure 2 The original partition settings of the large object column in the large object column metadata shown in the figure are described in the following sections. Figure 2 It is specifically written in the introduction and will not be repeated here.
[0177] After the step of recording the parameter information of the original partitioning method into the metadata table of the large object column of the database, the following steps can generally be included: obtaining data manipulation statements, which are used to query and / or modify data in the database; obtaining the parameter information of the original partitioning method from the metadata table of the large object column; determining the partition number where the manipulation target of the data manipulation statement is located according to the parameter information of the original partitioning method; searching for the manipulation target in the partition corresponding to the partition number, and executing the data manipulation statement. The data manipulation statement can generally be a DML (Data Manipulation Language) statement. After the partitioning is completed, if the data of the large object is manipulated by the DML statement, the parameter information of the original partitioning method can be obtained in the metadata table of the large object column, and then the partitioning is performed according to the original partitioning method. In this way, the partition number where the manipulation target is located can be determined by the original partitioning method, thereby realizing the rapid search of the manipulation target, and then completing the query or modification of the data.
[0178] In some other optional embodiments, the partitioning method also includes hash partitioning according to the fixed unique identifier of the object in the object table and the storage slice table. The steps of performing different partitioning operations on the object table and the storage slice table according to different types of partitioning methods may generally include: in the case where the partitioning method is hash partitioning according to the fixed unique identifier of the object in the object table and the storage slice table, determining the partition setting according to the partition instruction; calling a preset hash algorithm to calculate the fixed unique identifier of the object to obtain a calculation result; determining the target partition corresponding to the fixed unique identifier of the object according to the calculation result and the partition setting; transferring the data of the object table and the storage slice table to the corresponding target partition according to the fixed unique identifier of the object. In the actual partitioning process, in order to realize hash partitioning, it is usually necessary to determine the partition key and the number of partitions, and the number of partitions needs to be comprehensively considered based on factors such as hardware resources, expected query mode and data growth rate. Therefore, after receiving the partition instruction, it is necessary to obtain the partition setting therein to determine the specific conditions of this hash partitioning, so as to facilitate subsequent partitioning operations.
[0179] In addition, an optional example of a preset hash algorithm is as follows: the hash value of the fixed unique identifier of the object is calculated and the remainder is taken, and the remainder value is used as the calculation result. Through the remainder operation, all the fixed unique identifiers of the object are mapped to an integer range, and different integer ranges correspond to different partitions. In this way, hash partitioning can be achieved. Those skilled in the art can select a specific hash algorithm to complete hash partitioning according to actual conditions.
[0180] 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.
[0181] 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.
[0182] This embodiment also provides a computer program product 10 , a computer readable storage medium 20 , and a computer device 30 . Fig. 9 is a schematic diagram of a computer program product 10 according to an embodiment of the present invention, Fig.10 is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention, Fig.11is a schematic diagram of a computer device 30 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which implements the steps of any of the above-mentioned partition storage methods for large objects in a database when the computer program 11 is executed by a processor 32. The computer-readable storage medium 20 stores the above-mentioned computer program 11, which implements the steps of any of the above-mentioned partition storage methods for large objects in a database when the computer program 11 is executed by the processor 32. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored in the memory 31 and running on the processor 32.
[0183] The computer program 11 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 11 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.
[0184] In the description of this embodiment, the computer program product 10 is a related product including the computer program 11 .
[0185] For the purposes of the description of this embodiment, the computer-readable storage medium 20 is a tangible device capable of retaining and storing the computer program 11, which may be any device that can contain, store, communicate, propagate or transmit the program 11 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.
[0186] 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.
[0187] 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.
[0188] 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.
[0189] 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 partition storage method for large objects in a database, include: Obtaining a partition instruction, where the partition instruction is used to perform a partition operation on the data of the large object in the database; According to the large object, an object table and a storage slice table corresponding to the large object are obtained, wherein the object table is used to store description information of the large object, and the storage slice table is used to store storage slice data corresponding to the large object; Determine the original partitioning mode according to the partitioning instruction; The partition type of the original partition mode is determined, and different partition operations are performed on the object table and the storage slice table according to different types of the original partition mode.
2. The partition storage method for large objects in a database according to claim 1, in, The original partitioning method includes partitioning the object table and the storage slice table according to the regular data columns of the data base table in the database, and the data base table includes at least one column of the large object column and at least one column of the regular data column; The step of performing different partitioning operations on the object table and the storage slice table according to different types of the original partitioning method includes: when the original partitioning method is to partition the object table and the storage slice table according to the conventional data column, calling a preset hook function to convert the original partitioning method into a preset partitioning method, wherein the preset partitioning method is to perform list partitioning through the internal partitioning keys recorded in the object table and the storage slice table; and partitioning the object table and the storage slice table according to the preset partitioning method.
3. The partition storage method for large objects in a database according to claim 2, in, The step of calling the preset hook function to convert the original partition mode into the preset partition mode comprises: Partitioning the large object column in the data base table using the regular data column as a partition key to obtain a partition result; Determine the partition sequence number corresponding to each large object in the large object column according to the partition result; The partition serial number corresponding to the large object is recorded in the object table and the storage slice table corresponding to the large object according to the object fixed unique identifier of the large object as the internal partition key.
4. The partition storage method for large objects in a database according to claim 3, in, The step of partitioning the object table and the storage slice table according to the preset partitioning method comprises: List partitioning is performed according to the internal partition keys recorded in the object table and the storage slice table respectively.
5. The partition storage method for large objects in a database according to claim 2, in, After the step of partitioning the object table and the storage slice table according to the preset partitioning method, the following step is further included: The parameter information of the original partitioning method is recorded in the large object column metadata table of the database, and the large object column metadata table is used to record the description information of the object table and the storage slice table.
6. The partition storage method for large objects in a database according to claim 5, in, After the step of recording the parameter information of the original partitioning mode into the large object column metadata table of the database, the method further includes: Obtaining a data manipulation statement, wherein the data manipulation statement is used to query and / or modify the data in the database; Acquire parameter information of the original partitioning mode from the large object column metadata table; Determine the partition number where the manipulation target of the data manipulation statement is located according to the parameter information of the original partition mode; The manipulation target is searched in the partition corresponding to the partition sequence number, and the data manipulation statement is executed.
7. The partition storage method for large objects in a database according to claim 1, in, The partitioning method also includes performing hash partitioning according to the fixed unique identifiers of the objects in the object table and the storage slice table; The step of performing different partitioning operations on the object table and the storage slice table according to different types of the partitioning method includes: when the partitioning method is hash partitioning based on the fixed unique identifier of the object in the object table and the storage slice table, determining the partition setting according to the partitioning instruction; calling a preset hash algorithm to calculate the fixed unique identifier of the object to obtain a calculation result; determining the target partition corresponding to the fixed unique identifier of the object according to the calculation result and the partition setting; and transferring the data of the object table and the storage slice table to the corresponding target partitions according to the fixed unique identifier of the object.
8. A computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, the steps of the partition storage method for large objects in a database according to any one of claims 1 to 7 are implemented.
9. A computer program product, comprising a computer program, which, when executed by a processor, implements the steps of the partition storage method for large objects in a database according to any one of claims 1 to 7.
10. A computer device comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, wherein the processor implements the steps of the partition storage method for large objects in a database according to any one of claims 1 to 7 when executing the machine executable program.
