Method, system and equipment for storing large object data in database

Abstract

The invention relates to the technical field of large object data storage, and provides a method, system and equipment for storing large object data in a database, and the method comprises the steps: segmenting the large object data, and setting serial numbers for a plurality of data blocks obtained through segmentation; the data blocks with the serial numbers are stored in different data pages in a scattered mode; constructing a B tree index for each piece of large object data according to the serial number and the storage information; and performing incremental backup on the large object data, and performing positioning recovery on the large object data according to the constructed B-tree index. According to the method, the system and the equipment for storing the large object data in the database, the storage efficiency can be improved, the data access speed can be increased, the system stability can be enhanced, and the development, operation and maintenance efficiency can be improved.

Description

Technical Field

[0001] The present application relates to the technical field of large object data storage, and in particular to a method, system and device for storing large object data in a database. Background Art

[0002] With the rapid development of information technology, the amount of data has exploded, especially in the fields of big data, cloud computing, multimedia applications, etc., which puts higher requirements on the performance and capacity of database systems. As the core tool for data storage and management, database plays a vital role in various enterprises and organizations. As a powerful open source relational database management system, openGauss is widely used in data storage and management of various enterprises and organizations with its excellent performance and flexibility, providing users with reliable data storage solutions.

[0003] However, in actual applications, traditional database storage methods have many problems when dealing with large binary objects (LOBs). Large binary objects refer to unstructured or semi-structured data such as images, audio, video, and documents that take up a lot of storage space. These data are special in storage and management, and traditional storage methods are difficult to meet their needs for efficient storage and fast retrieval. For example, in the openGauss database, although the LOB function is provided to allow users to store and manage large object data, in actual applications, due to its inherent storage architecture, the database performance is significantly reduced in a high-concurrency, high-traffic environment, which is mainly reflected in the following aspects: 1. Low storage efficiency: Traditional LOB storage methods often store large object data in a separate table space, separate from ordinary data. Although this storage method achieves data isolation to a certain extent, it also increases the complexity of storage management. When storing large object data, additional storage space is required to store the metadata information of the object, such as the size and type of the object. In addition, since the storage method of large object data is relatively scattered, the utilization rate of storage space is low, and the storage resources of the database cannot be fully utilized, thereby reducing the overall storage efficiency.

[0004] 2. Slow retrieval speed: When retrieving large object data in the database, specific query statements are required to access the object data stored in the table space. Since the storage location of large object data is relatively scattered and the data volume is large, a large number of data scanning and transmission operations are required during the retrieval process, resulting in slow retrieval speed. Especially in a high-concurrency environment, when multiple users retrieve large object data at the same time, the database needs to process a large number of data requests at the same time, which further increases the delay time of the retrieval and affects the user experience.

[0005] 3. Complex management: The traditional LOB management mechanism also has certain limitations in data backup, recovery, and migration. When backing up data, large object data and ordinary data stored in different table spaces need to be backed up separately, which increases the complexity and time cost of backup. During the data recovery process, the backed-up large object data also needs to be restored one by one, and the consistency of the restored data is difficult to ensure. In addition, when migrating data, due to the special storage method of large object data, data loss or damage is prone to occur during the migration process, which brings great challenges to the maintenance and management of the database.

[0006] Therefore, how to provide an efficient and reliable solution to optimize the storage and management methods of large objects, solve the many shortcomings of the existing openGauss database in large object storage and management, meet the growing data storage needs, and improve the overall performance of the database and user experience has become a technical problem that needs to be solved urgently. Summary of the invention

[0007] In view of this, in order to overcome the deficiencies of the prior art, the present application aims to provide a method, system and device for storing large object data in a database.

[0008] According to a first aspect of the present application, a method for storing large object data in a database is provided, the method comprising: Segment the large object data, and set sequence numbers for several data blocks obtained by segmentation; The data blocks with sequence numbers are stored in different data pages; Build a B-tree index for each large object data based on the sequence number and storage information; Perform incremental backup of large object data and locate and restore large object data based on the constructed B-tree index.

[0009] Optionally, in the method for storing large object data in a database of the present application, segmenting the large object data includes: segmenting the large object data according to fixed sizes.

[0010] Optionally, in the method for storing large object data in a database of the present application, setting serial numbers for a number of data blocks obtained by segmentation includes: setting serial numbers in ascending order according to original positions of the data blocks in the large object data.

[0011] Optionally, in the method of storing large object data in a database of the present application, data blocks with serial numbers are dispersedly stored in different data pages, including: according to the size of the data block and the capacity of the data page, the corresponding data blocks are allocated and stored in different data pages according to the serial number.

[0012] Optionally, in the method for storing large object data in a database of the present application, a B-tree index is constructed for each large object data according to the serial number and storage information, including: the nodes of the constructed B-tree index include the serial number, storage location information and size information of the data block.

[0013] Optionally, in the method for storing large object data in a database of the present application, the storage location information of the data block includes the page number of the data page stored in the data block and the stored offset.

[0014] Optionally, in the method for storing large object data in a database of the present application, incremental backup of large object data includes: recording the timestamp of each backup and the range of data blocks backed up, and backing up only the data blocks that have changed since the last backup.

[0015] Optionally, in the method for storing large object data in a database of the present application, locating and restoring the large object data according to the constructed B-tree index includes: locating the storage location of the data block to be restored according to the B-tree index, and restoring the data block in sequence number order.

[0016] According to a second aspect of the present application, a system for storing large object data in a database is provided, the system comprising a large object data storage service client, the large object data storage service client comprising: A segmentation module is used to segment the large object data and set sequence numbers for several data blocks obtained by segmentation; A distributed storage module is used to distribute and store data blocks with sequence numbers in different data pages; An index building module is used to build a B-tree index for each large object data based on the sequence number and storage information; The backup and recovery module is used to perform incremental backup of large object data and locate and restore large object data based on the constructed B-tree index.

[0017] According to a third aspect of the present application, a computer device is provided, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the method described in the first aspect of the present application when executing the program.

[0018] The method, system and device for storing large object data in a database of the present application improves the performance and stability of the database when processing large objects by optimizing the storage structure and access mechanism of large object data. At the same time, it enhances the flexibility and maintainability of large object management and simplifies operations such as data backup, recovery and migration to meet the needs of modern applications for efficient data management in large data and high concurrency environments. Specifically, the method, system and device for storing large object data in a database of the present application have the following beneficial technical effects: 1. Improve storage efficiency. Segmented storage and optimized storage structure effectively reduce database page fragmentation and improve storage space utilization; 2. Accelerate data access speed. Efficient indexing mechanism and parallel processing make the reading and writing operations of large objects faster and shorten the response time. 3. Enhance system stability, ensure the reliability of data operations through transaction consistency guarantee mechanism, and avoid data inconsistency problems caused by concurrent access; 4. Improve development and operation efficiency. Easy-to-use management interfaces and tools reduce the workload of developers and administrators and improve the maintainability and scalability of the system. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0020] Figure 1 This is an example diagram of the architecture of a system for storing large object data in a database according to an embodiment of the present application; Figure 2 This is an example diagram of the architecture of a large object data storage service end of a system for storing large object data in a database according to an embodiment of the present application; Figure 3 A flowchart of a method for storing large object data in a database according to an embodiment of the present application; Figure 4 An example diagram of segmenting large object data according to a method for storing large object data in a database according to an embodiment of the present application; Figure 5 A schematic diagram of the structure of the device provided in this application. DETAILED DESCRIPTION

[0021] The embodiments of the present application are described in detail below with reference to the accompanying drawings.

[0022] It should be noted that the following embodiments and features in the embodiments may be combined with each other in the absence of conflict; and, based on the embodiments in the present disclosure, all other embodiments obtained by ordinary technicians in the field without making any creative work are within the scope of protection of the present disclosure.

[0023] It should be noted that various aspects of the embodiments within the scope of the appended claims are described below. It should be apparent that the aspects described herein may be embodied in a wide variety of forms, and any specific structure and / or function described herein is merely illustrative. Based on the present disclosure, it should be understood by those skilled in the art that an aspect described herein may be implemented independently of any other aspect, and two or more of these aspects may be combined in various ways. For example, any number of aspects described herein may be used to implement the device and / or practice the method. In addition, other structures and / or functionalities other than one or more of the aspects described herein may be used to implement this device and / or practice this method.

[0024] The technical principles of the present invention are as follows: The optimized large object storage structure sets up a segmented storage mechanism in the database to split large object data into several smaller segments and store them in different data pages to reduce the impact of a single large object on database performance. A dedicated B-tree index is established for large object segments, and an efficient indexing mechanism is established to make the retrieval and access of large objects faster, supporting the rapid location of specific data segments that need to be operated.

[0025] Figure 1 FIG. 1 is an example diagram of the architecture of a system for storing large object data in a database according to an embodiment of the present application. Figure 1 As shown, the system may include a large object data storage service client 101, a communication network 102 and / or one or more large object data storage clients 103. Figure 1 The example shown is a plurality of large object data storage clients 103 .

[0026] The large object data storage service end 101 can be any appropriate server for storing information, data, programs and / or any other suitable type of content. In some embodiments, the large object data storage service end 101 can perform appropriate functions. For example, in some embodiments, the large object data storage service end 101 can be used to implement database storage of large object data. As an optional example, in some embodiments, the large object data storage service end 101 can be used to implement database storage of large object data by segmenting the large object data. For example, the large object data storage service end 101 can be used to: segment the large object data, set serial numbers for several data blocks obtained by segmentation; store the data blocks with serial numbers in different data pages in a dispersed manner; construct a B-tree index for each large object data according to the serial number and storage information; perform incremental backup of the large object data, and locate and restore the large object data according to the constructed B-tree index.

[0027] Figure 2FIG. 1 is an example diagram of the architecture of a large object data storage service end of a system for storing large object data in a database according to an embodiment of the present application. Figure 2 As shown, in this embodiment, the large object data storage service client includes: A segmentation module is used to segment the large object data and set sequence numbers for several data blocks obtained by segmentation; A distributed storage module is used to distribute and store data blocks with sequence numbers in different data pages; An index building module is used to build a B-tree index for each large object data based on the sequence number and storage information; The backup and recovery module is used to perform incremental backup of large object data and locate and restore large object data based on the constructed B-tree index.

[0028] As another example, in some embodiments, the large object data storage service client 101 may send the method of storing large object data in the database to the large object data storage client 103 for the user to use according to the request of the large object data storage client 103 .

[0029] As an optional example, in some embodiments, the large object data storage client 103 is used to provide a visual storage interface, which is used to receive a user's selection input operation to implement database storage of large object data, and, in response to the selection input operation, obtain a storage interface corresponding to the option selected by the selection input operation from the large object data storage service client 101 and display the storage interface, wherein the storage interface at least displays information for implementing database storage of large object data and operation options for implementing the information for implementing database storage of large object data.

[0030] In some embodiments, the communication network 102 can be any suitable combination of one or more wired and / or wireless networks. For example, the communication network 102 can include any one or more of the following: the Internet, an intranet, a wide area network (WAN), a local area network (LAN), a wireless network, a digital subscriber line (DSL) network, a frame relay network, an asynchronous transfer mode (ATM) network, a virtual private network (VPN), and / or any other suitable communication network. The large object data storage client 103 can be connected to the communication network 102 via one or more communication links (e.g., communication link 104), and the communication network 102 can be linked to the large object data storage service client 101 via one or more communication links (e.g., communication link 105). The communication link can be any communication link suitable for transmitting data between the large object data storage client 103 and the large object data storage service client 101, such as a network link, a dial-up link, a wireless link, a hard-wired link, any other suitable communication link, or any suitable combination of such links.

[0031] The large object data storage client 103 may include any one or more clients that present an interface related to implementing the database storage of large object data in an appropriate form for use and operation by a user. In some embodiments, the large object data storage client 103 may include any suitable type of device. For example, in some embodiments, the large object data storage client 103 may include a mobile device, a tablet computer, a laptop computer, a desktop computer, and / or any other suitable type of client device.

[0032] Although the large object data storage service client 101 is illustrated as one device, in some embodiments, any suitable number of devices may be used to perform the functions performed by the large object data storage service client 101. For example, in some embodiments, multiple devices may be used to implement the functions performed by the large object data storage service client 101. Alternatively, the functions of the large object data storage service client 101 may be implemented using a cloud service.

[0033] Based on the above system, an embodiment of the present application provides a method for storing large object data in a database, which is described below through the following embodiments.

[0034] Figure 3 1 is a flowchart of the steps of a method for storing large object data in a database according to an embodiment of the present application. The method for storing large object data in a database according to this embodiment can be executed on a large object data storage service end, such as Figure 3 As shown, the method for storing large object data in the database includes the following steps: Step S201: Segment the large object data, and set sequence numbers for several data blocks obtained by segmentation.

[0035] As an optional example, in this embodiment, the large object data is segmented according to a fixed size. In actual applications, other methods can also be used to segment the large object data, such as segmenting the large object data according to multiple data block sizes, such as segmenting the large object data according to three specifications of 2kb, 3kb, and 4kb. Figure 4 This is an example diagram of segmenting large object data according to a method for storing large object data in a database according to an embodiment of the present application.

[0036] As an optional example, in this embodiment, the sequence number is set in ascending order according to the original position of the data block in the large object data. In actual applications, the sequence number can also be set for the segmented data block in other ways, such as setting the sequence number in descending order according to the original position of the data block in the large object data, which is not limited in this embodiment.

[0037] Step S202: dispersively storing data blocks with sequence numbers in different data pages.

[0038] As an optional example, in this embodiment, according to the size of the data block and the capacity of the data page, the corresponding data blocks are allocated and stored in different data pages according to the sequence number. This embodiment avoids a single large object data from occupying continuous storage space and reduces lock competition for database pages by dispersing and storing different data blocks in different data pages in the database. It should be noted that in this embodiment, the metadata of the large object data (the owner of the large object, access permission information, and storage of actual data content) is stored in the pg_largeobject_metadata table, and the actual data content of the large object data (corresponding to the OID of the large object data, the page number of the data block, and the actual binary data content) is stored in the pg_largeobject table.

[0039] Step S203: construct a B-tree index for each large object data according to the sequence number and storage information.

[0040] In this embodiment, the nodes of the constructed B-tree index include the serial number, storage location information and size information of the data block. The storage location information of the data block includes the page number of the data page stored in the data block and the stored offset. In this embodiment, by establishing a B-tree index for large object data and recording the location information of all data blocks, it is convenient to accurately and quickly locate and access the data blocks. It should be noted that in this embodiment, all leaf nodes of the B-tree index are on the same layer, non-leaf nodes are at least half full, the key values ​​in the nodes are ordered, and it is a balanced tree structure, thereby significantly improving the retrieval and access speed of the data block. This embodiment creates a B-tree index for all large object data, records the information of all large object data and data blocks, realizes global indexing, and supports searching for corresponding large object data through large object data ID.

[0041] Step S204: perform incremental backup of the large object data, and locate and restore the large object data according to the constructed B-tree index.

[0042] As an optional example, in this embodiment, the timestamp of each backup and the range of the backed-up data blocks are recorded, and only the data blocks that have changed since the last backup are backed up, thereby reducing the backup time and storage space and improving the backup efficiency.

[0043] In this embodiment, the storage location of the data block to be restored is located according to the B-tree index, and the data block is restored in sequence, thereby achieving efficient data recovery and shortening system downtime.

[0044] The method of storing large object data in a database of this embodiment improves the performance and stability of the database when processing large objects by optimizing the storage structure and access mechanism of large object data. At the same time, it enhances the flexibility and maintainability of large object management and simplifies operations such as data backup, recovery, and migration to meet the needs of modern applications for efficient data management in large data and high-concurrency environments. Specifically, the method and system of storing large object data in a database of this embodiment have the following beneficial technical effects: 1. Improve storage efficiency. Segmented storage and optimized storage structure effectively reduce database page fragmentation and improve storage space utilization; 2. Accelerate data access speed. Efficient indexing mechanism and parallel processing make the reading and writing operations of large objects faster and shorten the response time. 3. Enhance system stability, ensure the reliability of data operations through transaction consistency guarantee mechanism, and avoid data inconsistency problems caused by concurrent access; 4. Improve development and operation efficiency. Easy-to-use management interfaces and tools reduce the workload of developers and administrators and improve the maintainability and scalability of the system.

[0045] like Figure 5 As shown, the present application also provides a device, including a processor 310, a communication interface 320, a memory 330 for storing a processor executable computer program, and a communication bus 340. The processor 310, the communication interface 320, and the memory 330 communicate with each other through the communication bus 340. The processor 310 implements the above-mentioned method of storing large object data in a database by running an executable computer program.

[0046] Among them, the computer program in the memory 330 can be implemented in the form of a software functional unit and can be stored in a computer-readable storage medium when it is sold or used as an independent product. Based on this understanding, the technical solution of the present application can be essentially or partly embodied in the form of a software product that contributes to the prior art. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the various embodiments of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc. Various media that can store program codes.

[0047] The system embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, i.e., they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected based on actual needs to achieve the purpose of the solution of this embodiment. Those of ordinary skill in the art may understand and implement it without creative effort.

[0048] Through the description of the above implementation modes, those skilled in the art can clearly understand that each implementation mode can be implemented by means of software plus a necessary general hardware platform, or of course by hardware. Based on such an understanding, the above technical solution can essentially or in other words be embodied in the form of a software product that contributes to the prior art. The computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods of each embodiment or some parts of the embodiment.

[0049] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any changes or substitutions that can be easily thought of by a person skilled in the art within the technical scope disclosed in the present application should be included in the protection scope of the present application. Therefore, the protection scope of the present application shall be based on the protection scope of the claims.

Claims

1. A method for storing large object data in a database, characterized in that: The method comprises: Segment the large object data, and set sequence numbers for several data blocks obtained by segmentation; The data blocks with sequence numbers are stored in different data pages; Build a B-tree index for each large object data based on the sequence number and storage information; Perform incremental backup of large object data and locate and restore large object data based on the constructed B-tree index.

2. The method for storing large object data in a database according to claim 1, characterized in that: The large object data is segmented, including segmenting the large object data according to fixed sizes.

3. The method for storing large object data in a database according to claim 1, characterized in that: Setting sequence numbers for the plurality of data blocks obtained by segmentation includes: setting the sequence numbers in ascending order according to the original positions of the data blocks in the large object data.

4. The method for storing large object data in a database according to claim 1, characterized in that: The data blocks with serial numbers are dispersedly stored in different data pages, including: according to the size of the data blocks and the capacity of the data pages, the corresponding data blocks are allocated and stored in different data pages according to the serial numbers.

5. The method for storing large object data in a database according to claim 1, characterized in that: A B-tree index is constructed for each large object data according to the sequence number and the storage information, including: a node of the constructed B-tree index includes a sequence number, storage location information and size information of the data block.

6. The method for storing large object data in a database according to claim 5, characterized in that: The storage location information of the data block includes the page number of the data page stored in the data block and the stored offset.

7. The method for storing large object data in a database according to claim 1, characterized in that: Perform incremental backup of large object data, including recording the timestamp of each backup and the range of data blocks to be backed up, and backing up only the data blocks that have changed since the last backup.

8. The method for storing large object data in a database according to claim 1, characterized in that: The large object data is located and recovered according to the constructed B-tree index, including: locating the storage location of the data block to be recovered according to the B-tree index, and recovering the data block in sequence of the serial number.

9. A system for storing large object data in a database, characterized in that: The system includes a large object data storage service end, and the large object data storage service end includes: A segmentation module is used to segment the large object data and set sequence numbers for several data blocks obtained by segmentation; A distributed storage module is used to distribute and store data blocks with sequence numbers in different data pages; An index building module is used to build a B-tree index for each large object data based on the sequence number and storage information; The backup and recovery module is used to perform incremental backup of large object data and locate and restore large object data based on the constructed B-tree index.

10. A computer device, characterized in that: The computer device comprises a memory, a processor and a computer program stored in the memory and executable on the processor, and the processor implements the steps of the method according to any one of claims 1 to 8 when executing the program.

Images