In-memory database storage engine data optimization method, electronic device and storage medium

Abstract

The invention discloses a data optimization method for a memory database storage engine, an electronic device and a storage medium, and belongs to the technical field of database management. The technical problem to be solved by the invention is how to effectively reduce the amount of data stored in the memory and improve the performance of the database. The technical scheme adopted is as follows: The method is specifically as follows: Insert increment: an ART tree-based memory storage engine changes the original SQL Update to a direct insert increment operation; KV data insertion: the storage engine queries the row data to be modified, and modifies the corresponding column according to the incremental operation, and reinserts it after merging; if there is no snapshot, the old KV data is deleted; for the Update Returning syntax, the storage engine returns the full Value; it is determined whether the Put syntax insertion is a write-intent deletion: if so, the write intent of the Key corresponding to the linked list head node is directly deleted; if not, the bidirectional linked list storing the Key is searched, and other versions in the linked list are deleted to complete the insertion.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a method for optimizing data of a memory database storage engine, an electronic device and a storage medium. Background Art

[0002] In recent years, with the increase in the capacity of dynamic random access memory (DRAM) and the decrease in unit price, it has become possible to store and process large amounts of data in memory. Compared with disks, the data reading and writing speed of memory is several orders of magnitude higher. Storing data in memory can greatly improve application performance compared to accessing it from disk.

[0003] The amount of data in memory is an important factor affecting the performance of the storage engine. The ART tree has the best query performance among all index structures. However, there is a common problem with index structures: as the amount of indexed data increases, query performance decreases. When the amount of data increases from 64K to 16M, the query performance of the ART tree decreases by at least 4 times, which seriously affects the performance of the storage engine. At the same time, since the amount of data stored in memory is limited, effective data optimization methods can store more data in the same size of memory.

[0004] During the execution of the Update statement, all data in the storage engine needs to be read to the SQL layer for processing and then put into the storage engine. This process not only makes the link too long, but also occupies too many resources for searching and inserting the full data, resulting in performance degradation. Summary of the invention

[0005] The technical task of the present invention is to provide a memory database storage engine data optimization method, electronic device and storage medium to solve the problem of how to effectively reduce the amount of data stored in the memory and improve the performance of the database.

[0006] The technical task of the present invention is achieved in the following manner: a method for optimizing data of a memory database storage engine, the method being specifically as follows:

[0007] Insert incrementally: Based on the memory storage engine of ART tree, the original SQL Update is changed to a direct insert incremental operation;

[0008] KV data insertion: The storage engine queries the row data to be modified, modifies the corresponding columns according to the incremental operation, merges and reinserts. If there is no snapshot, the old KV data is deleted. For the Update Returning syntax, the storage engine returns the full value without the need for merging by the upper layer.

[0009] Determine whether the insertion of the Put syntax is a deletion of the write intention:

[0010] If yes, there is no need to insert the node into the ART tree, and directly delete the write intention of the key corresponding to the head node of the linked list to reduce the amount of stored data;

[0011] If not, find the doubly linked list that stores the key, delete other versions in the linked list, and complete the insertion.

[0012] Among them, ART (Adaptive Radix Tree) is a trie tree with binary bit strings as keywords. It is a multi-branch tree structure and is similar to a multi-layer index table. Each intermediate node contains an array of pointers to multiple child nodes, and the leaf node contains a pointer to the actual object. The intermediate nodes are divided into several different types according to their lengths, and they adjust themselves as the data changes. Although the KV memory storage engine built on the ART tree will have great performance advantages, the height of the tree depends on the length of the key, and the time complexity depends on the length of the key. Therefore, when the length of the key increases, the performance will decrease, and there will be a performance bottleneck. There is a common problem with the index structure. As the amount of indexed data increases, the query performance will decrease.

[0013] As a preferred solution, when there are multiple versions of the Key inserted by the Put syntax (with different SequenceNumbers), the bidirectional linked list storing multiple versions of the Key is traversed, and other versions with the same HLC but different SequenceNumbers are deleted. Only the latest version and the version before the snapshot are retained, and the remaining versions are stored in a deletion linked list waiting to be deleted by the background thread.

[0014] Preferably, the Update incremental encoding method is as follows:

[0015] When a column of value tags (ValueTag) is added to the header of Value, the Update type is stored to distinguish whether it is an Update operation;

[0016] Change the subsequent column type (ColumnType) to the incremental operation type (OperationType, such as Replace, Sum, Sub, etc.), and the final column value is the incremental value (UpdateValue);

[0017] At the same time, the Update incremental syntax does not process system table data, adds a judgment, and skips tables with table IDs less than 52; when encoding, encodes the incremental operation type of the corresponding column.

[0018] Preferably, the storage engine encodes the incremental Value before inserting the data to determine whether the type of the first column of the Value is Update:

[0019] If so, the Value is incrementally updated, and the last inserted Value is the updated value.

[0020] Preferably, the incremental Value is encoded as follows:

[0021] Decode the incremental Value to obtain the column ID (ColumnID), incremental operation type (OperationType), and incremental value (UpdateValue) that need to be incrementally updated;

[0022] Query the latest Value in the storage engine through the Key;

[0023] Decode the latest Value to obtain the corresponding column ID (ColumnID), column type (ColumnType) and column value (ColumnValue);

[0024] According to the one-to-one correspondence between the incremental update ID and the column ID, the column to be updated is obtained, and the column value (ColumnValue) is updated according to the incremental operation type (OperationType) and incremental value (UpdateValue);

[0025] The updated Value is re-encoded into a whole Value according to the Value encoding method and inserted into the storage engine; the old KV data is stored in the deletion list and waits for deletion by the background thread;

[0026] Preferably, the encoding of Value is as follows:

[0027] A row of Value consists of multiple columns (Column), each column consists of a value tag (ValueTag) and a column value (ColumnValue), and the value tag includes a column ID (ColumnID) and a column type (ColumnType);

[0028] The original Update statement is pushed to the storage layer for execution flow of reading data - decoding - encoding - inserting, and the encoding method of the upper-layer Value is modified.

[0029] As a preferred method, when inserting KV data, decode UserKey to obtain table ID (TableID); when table ID is greater than 0, the timestamp of Key is empty, and the value type is deletion (kTypeDeletion), it is determined that this insertion is a write intention deletion, and the write intention KV data at the head of the linked list is directly deleted;

[0030] In the ART-based storage engine, a bidirectional linked list is used to store different versions of the Key. Since the write-intent Key has no timestamp, the Key must be stored in the head node of the linked list. When the KV data deleted by the write intent is inserted, there is no need to insert the write-intent KV data. Instead, the write-intent KV data at the head node of the linked list needs to be deleted.

[0031] Preferably, when writing to delete, first find the bidirectional linked list storing the UserKey, obtain the Key at the head of the linked list and the next NextKey through the iterator, and point the head of the linked list to NextKey. This operation uses atomic operations; then delete the previous head node.

[0032] An electronic device comprising: a memory and at least one processor;

[0033] Wherein, the memory stores a computer program;

[0034] The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the above-mentioned in-memory database storage engine data optimization method.

[0035] A computer-readable storage medium stores a computer program, which can be executed by a processor to implement the above-mentioned in-memory database storage engine data optimization method.

[0036] The in-memory database storage engine data optimization method, electronic device, and storage medium of the present invention have the following advantages:

[0037] (1) The present invention optimizes the Update logic and converts Update into Insert; optimizes the data insertion logic and deletes redundant data in a timely manner, effectively reducing the amount of data stored in the memory and improving the performance of the database storage engine;

[0038] (ii) The present invention reduces the amount of data indexed by ART by optimizing the insertion logic and changing the Update to the Insert logic, thereby improving the efficiency and performance of the memory storage engine in storing data;

[0039] (III) The present invention changes the original SQL Update operation that requires querying before inserting to a direct insert incremental operation. The storage layer only returns the full value, which does not need to be merged by the upper layer. Instead, it is merged during insertion and the original KV data is deleted, which improves the execution speed of Update and reduces the amount of data.

[0040] (iv) When the Put syntax inserts write-intent deletion data, there is no need to insert a node. The write-intent data of the corresponding node in the linked list is directly deleted. When inserting new KV data, other versions with different SequenceNumbers in the linked list are deleted, and only the latest version and the version before the snapshot (if a snapshot is set) are retained to reduce the amount of stored data. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] The present invention is further described below in conjunction with the accompanying drawings.

[0042] Attached Figure 1 A flowchart of a method for optimizing data in a memory database storage engine;

[0043] Attached Figure 2 This is the original Value encoding diagram;

[0044] Attached Figure 3 This is a diagram of Value encoding after Update optimization. DETAILED DESCRIPTION

[0045] The in-memory database storage engine data optimization method, electronic device and storage medium of the present invention are described in detail below with reference to the accompanying drawings and specific embodiments of the specification.

[0046] Embodiment 1:

[0047] As attached Figure 1 As shown, the memory database storage engine data optimization method of this embodiment is as follows:

[0048] S1. Insert increment: Based on the memory storage engine of ART tree, the original SQL Update is changed to direct insert increment operation;

[0049] S2, KV data insertion: The storage engine queries the row data to be modified, modifies the corresponding columns according to the incremental operation, merges and reinserts; if there is no snapshot, the old KV data is deleted; for the Update Returning syntax, the storage engine returns the full value, without the need for merging by the upper layer;

[0050] S3. Determine whether the insertion of the Put syntax is a deletion of the write intention:

[0051] ①. If yes, there is no need to insert the node into the ART tree. Just delete the write intention of the key corresponding to the head node of the linked list to reduce the amount of stored data.

[0052] ②If not, find the doubly linked list that stores the key, delete other versions in the linked list, and complete the insertion.

[0053] When there are multiple versions of the key inserted by the Put syntax (with different SequenceNumbers), the doubly linked list storing multiple versions of the key is traversed, and other versions with the same HLC but different SequenceNumbers are deleted. Only the latest version and the version before the snapshot are retained, and the remaining versions are stored in a deletion linked list waiting to be deleted by the background thread.

[0054] As attached Figure 3 As shown, the Update incremental encoding method in this embodiment is as follows:

[0055] (1) When the Value header is added with a column of value tags (ValueTag), the Update type is stored to distinguish whether it is an Update operation;

[0056] (2) Change the subsequent column type (ColumnType) to the incremental operation type (OperationType, such as Replace, Sum, Sub, etc.), and the final column value is the incremental value (UpdateValue);

[0057] At the same time, the Update incremental syntax does not process system table data, adds a judgment, and skips tables with table IDs less than 52; when encoding, encodes the incremental operation type of the corresponding column.

[0058] The storage engine in this embodiment encodes the incremental Value before inserting data and determines whether the type of the first column of Value is Update:

[0059] If so, the Value is incrementally updated, and the last inserted Value is the updated value.

[0060] Preferably, the incremental Value is encoded as follows:

[0061] (1) Decode the incremental Value to obtain the column ID (ColumnID), incremental operation type (OperationType), and incremental value (UpdateValue) that need to be incrementally updated;

[0062] (2) Query the latest value in the storage engine through the key;

[0063] (3) Decode the latest Value to obtain the corresponding column ID (ColumnID), column type (ColumnType) and column value (ColumnValue);

[0064] (4) According to the one-to-one correspondence between the incremental update ID and the column ID, the column to be updated is obtained, and the column value (ColumnValue) is updated according to the incremental operation type (OperationType) and the incremental value (UpdateValue);

[0065] (5) Re-encode the updated Value into a whole Value according to the Value encoding method and insert it into the storage engine; the old KV data is stored in the deletion list and waits for deletion by the background thread;

[0066] As attached Figure 2 As shown, the encoding method of Value in this embodiment is as follows:

[0067] (1) A row of Value consists of multiple columns (Column), each column consists of a value tag (ValueTag) and a column value (ColumnValue), and the value tag includes a column ID (ColumnID) and a column type (ColumnType);

[0068] (2) Push the original Update statement's execution process of reading data - decoding - encoding - inserting to the storage layer, and modify the encoding method of the upper-layer Value.

[0069] When KV data is inserted in this embodiment, UserKey is decoded to obtain the table ID (TableID); when the table ID is greater than 0, the timestamp of the Key is empty, and the value type is deletion (kTypeDeletion), it is determined that this insertion is a write-intent deletion, and the write-intent KV data at the head of the linked list is directly deleted.

[0070] In the ART-based storage engine, a bidirectional linked list is used to store different versions of the Key. Since the write-intent Key has no timestamp, the Key must be stored in the head node of the linked list. When the KV data deleted by the write intent is inserted, there is no need to insert the write-intent KV data. Instead, the write-intent KV data at the head node of the linked list needs to be deleted.

[0071] When the write intention is to delete in this embodiment, first find the bidirectional linked list storing the UserKey, obtain the Key at the head of the linked list and the next NextKey through the iterator, and point the head of the linked list to NextKey. This operation uses an atomic operation; then delete the previous head node.

[0072] Embodiment 2:

[0073] This embodiment also provides an electronic device, including: a memory and a processor;

[0074] Wherein, the memory stores computer-executable instructions;

[0075] The processor executes the computer-executable instructions stored in the memory, so that the processor executes the data optimization method for the memory database storage engine in any embodiment of the present invention.

[0076] Embodiment 3:

[0077] This embodiment also provides a computer-readable storage medium, in which a plurality of instructions are stored, and the instructions are loaded by a processor, so that the processor executes the data optimization method of the memory database storage engine in any embodiment of the present invention. Specifically, a system or device equipped with a storage medium can be provided, on which a software program code that implements the functions of any of the above embodiments is stored, and a computer (or CPU or MPU) of the system or device reads and executes the program code stored in the storage medium.

[0078] In this case, the program code itself read from the storage medium can realize the function of any one of the above-mentioned embodiments, and thus the program code and the storage medium storing the program code constitute a part of the present invention.

[0079] The storage medium embodiments for providing the program code include a floppy disk, a hard disk, a magneto-optical disk, an optical disk (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RYM, DVD-RW, DVD+RW), a magnetic tape, a non-volatile memory card, and a ROM. Alternatively, the program code can be downloaded from a server computer via a communication network.

[0080] In addition, it should be clear that the functions of any of the above embodiments can be implemented not only by executing the program code read by the computer, but also by enabling an operating system operating on the computer to complete part or all of the actual operations based on instructions from the program code.

[0081] In addition, it can be understood that the program code read from the storage medium is written to a memory provided in an expansion board inserted into the computer or written to a memory provided in an expansion unit connected to the computer, and then based on the instructions of the program code, a CPU installed on the expansion board or the expansion unit is enabled to perform part or all of the actual operations, thereby realizing the functions of any of the above-mentioned embodiments.

[0082] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for optimizing data in a memory database storage engine, characterized in that: The method is as follows: Insert incrementally: Based on the memory storage engine of ART tree, the original SQL Update is changed to a direct insert incremental operation; KV data insertion: The storage engine queries the row data to be modified, modifies the corresponding columns according to the incremental operation, merges and reinserts them; if there is no snapshot, the old KV data is deleted; For Update Returning syntax, the storage engine returns the full value. After the KV data is inserted, determine whether the data inserted by the Put syntax is a deletion with write intent: If so, directly delete the write intention of the key corresponding to the head node of the linked list; If not, then search the doubly linked list storing the key, delete other versions in the list, and complete the insertion; specifically: when there are multiple versions of the key inserted by the Put syntax, traverse the doubly linked list storing multiple versions of the key, delete other versions with the same HLC and different SequenceNumber, and only keep the latest version and the version before the snapshot. The remaining versions are stored in a deletion linked list waiting to be deleted by the background thread.

2. The method for optimizing the data of the memory database storage engine according to claim 1, characterized in that: Update incremental encoding is as follows: Add a column of value labels to the header of Value to store the Update type, which is used to distinguish whether it is an Update operation; Change the subsequent column types to incremental operation types, and the final column value to incremental value; At the same time, the Update incremental syntax adds a judgment to skip tables with table IDs less than 52; when encoding, the incremental operation type of the corresponding column is encoded.

3. The method for optimizing the data of the memory database storage engine according to claim 2, characterized in that: The storage engine encodes the incremental Value before inserting data to determine whether the type of the first column of Value is Update: If so, the Value is incrementally updated, and the last inserted Value is the updated value.

4. The method for optimizing data in a memory database storage engine according to claim 3, characterized in that: Decode the incremental Value to obtain the column ID, incremental operation type, and incremental value that need to be incrementally updated; Query the latest value in the storage engine through the key; Decode the latest Value to obtain the corresponding column ID, column type, and column value; According to the one-to-one correspondence between the incremental update ID and the column ID, the column to be updated is obtained, and the column value is updated according to the incremental operation type and incremental value; Re-encode the updated Value into a complete Value according to the Value encoding method and insert it into the storage engine; The old KV data is stored in the deletion list, waiting to be deleted by the background thread.

5. The method for optimizing data in a memory database storage engine according to claim 4, characterized in that: The encoding method of Value is as follows: A row of Value consists of multiple columns, each column consists of a value label and a column value. The value label includes the column ID and column type. The original Update statement is pushed to the storage layer for execution flow of reading data - decoding - encoding - inserting, and the encoding method of the upper-layer Value is modified.

6. The method for optimizing data in a memory database storage engine according to claim 1, characterized in that: When KV data is inserted, the UserKey is decoded to obtain the table ID. When the table ID is greater than 0, the Key timestamp is empty, and the value type is delete, the insertion is considered to be a write-intent delete, and the write-intent KV data at the head of the linked list is directly deleted.

7. The method for optimizing data in a memory database storage engine according to claim 6, characterized in that: When writing to delete, first find the bidirectional linked list storing the UserKey, obtain the Key at the head of the linked list and the next NextKey through the iterator, and point the head of the linked list to NextKey. This operation uses atomic operations; then delete the previous head node.

8. An electronic device, characterized in that: include: memory and at least one processor; Wherein, the memory stores a computer program; The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the in-memory database storage engine data optimization method as described in any one of claims 1 to 7.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, which can be executed by a processor to implement the in-memory database storage engine data optimization method according to any one of claims 1 to 7.

Images