Storage method and device based on hot and cold data separation and multi-mode database engine

Abstract

The present application belongs to the field of database technology, and specifically discloses a storage method and device based on hot and cold data separation and a multi-mode database engine, the method comprising: monitoring a row storage table and monitoring a column storage table; when cold data exists in the row storage table, transferring the cold data on the row storage table to the column storage table; when hot data exists in the column storage table, transferring the hot data on the column storage table to the row storage table; the row storage table is used to store hot data, the column storage table is used to store cold data, a copy of the stored data is stored in a row storage table or a column storage table in a single copy, the access frequency of hot data is higher than the access frequency of cold data, the row storage table is carried by a row storage engine, and the column storage table is carried by a column storage engine. The present application carries a row storage table for storing hot data by a row storage engine, and carries a column storage table for storing cold data by a column storage engine, so that the storage system can support both massive data query and analysis and efficient DML operations.

Description

Technical Field

[0001] The present application belongs to the field of database technology, and more specifically, relates to a storage method and device based on hot and cold data separation and a multi-mode database engine. Background Art

[0002] In the traditional database architecture, OLTP (Online Transactional Processing) type operations are implemented using relational databases, and the access scenarios of massive devices are handled by sharding databases and tables. In terms of OLAP (Online Analytical Processing), the construction of the perception data warehouse is completed based on NoSQL or analytical databases, and the data of the relational database is synchronized to the data warehouse in a scheduled or quasi-real-time manner through ETL. Traditional row-stored tables and traditional column-stored tables meet the two opposing application scenarios of OLTP and OLAP respectively. OLTP operations are high-concurrency, short transactions, and mainly write-based, while OLAP operations are high-latency, long transactions, and mainly read-based. The query performance of traditional row-stored tables drops significantly when the number of records reaches billions, while the concurrent deletion and update performance of traditional column-stored tables is low. How to implement a storage system that can support massive data query and analysis and perform DML operations efficiently is a technical problem that needs to be solved in this field. Summary of the invention

[0003] In view of the defects of the prior art, the purpose of this application is to realize a storage system that can support massive data query and analysis and perform DML operations efficiently.

[0004] To achieve the above objectives, in a first aspect, the present application provides a storage method based on hot and cold data separation and a multi-mode database engine, comprising:

[0005] Monitor row storage table and monitor column storage table;

[0006] When cold data exists in the row storage table, the cold data on the row storage table is transferred and stored in the column storage table; when hot data exists in the column storage table, the hot data on the column storage table is transferred and stored in the row storage table;

[0007] Among them, the row storage table is used to store hot data, and the column storage table is used to store cold data. A piece of stored data is stored in a row storage table or a column storage table in a single copy. The access frequency of hot data is higher than the access frequency of cold data. The row storage table is carried by the row storage engine, and the column storage table is carried by the column storage engine.

[0008] In a possible implementation, monitoring the row storage table includes:

[0009] Determine whether the time interval between the current time and the time when the row data was last updated exceeds the time interval threshold;

[0010] If it is determined that the time interval between the current time and the time when the row-stored data was last updated exceeds the time interval threshold, the row-stored data is determined to be cold data.

[0011] In a possible implementation, monitoring the row storage table includes:

[0012] Determine whether the data volume of the row storage table exceeds the data volume threshold;

[0013] If it is determined that the data volume of the row storage table exceeds the data volume threshold, the cache elimination algorithm is used to filter out the eliminated row storage data in the row storage table as cold data.

[0014] In a possible implementation, monitoring the column storage table includes:

[0015] Determine whether an event of updating column data occurs in the column storage table;

[0016] If it is determined that a column data update event occurs in the column storage table, the updated column storage data is treated as hot data.

[0017] In a possible implementation, the method further includes:

[0018] Periodically merge the records of the column storage data change log into the column storage bitmap and clear the column storage data change log.

[0019] In a possible implementation, the method further includes:

[0020] When receiving a SQL request, multiple execution plans are generated by parsing the SQL request;

[0021] Analyze the total computational cost corresponding to each execution plan, and determine the one with the smallest total computational cost among multiple execution plans as the target execution plan.

[0022] In a possible implementation, the method further includes:

[0023] When the amount of data in a batch insert operation exceeds the data insertion amount threshold, the data is inserted into the column storage table in column storage mode.

[0024] When the amount of data in the batch update operation exceeds the update data amount threshold, the new data is stored in the column storage table in a column storage manner.

[0025] In a second aspect, the present application provides a storage device based on cold and hot data separation and a multi-mode database engine, comprising:

[0026] A monitoring module, used for monitoring the row storage table and the column storage table;

[0027] The cold and hot data transfer module is used to transfer the cold data on the row storage table to the column storage table when there is cold data in the row storage table; and to transfer the hot data on the column storage table to the row storage table when there is hot data in the column storage table;

[0028] Among them, the row storage table is used to store hot data, and the column storage table is used to store cold data. A piece of stored data is stored in a row storage table or a column storage table in a single copy. The access frequency of hot data is higher than the access frequency of cold data. The row storage table is carried by the row storage engine, and the column storage table is carried by the column storage engine.

[0029] In a third aspect, the present application provides an electronic device comprising: at least one memory for storing programs; and at least one processor for executing the programs stored in the memory. When the program stored in the memory is executed, the processor is used to execute the method described in the first aspect or any possible implementation of the first aspect.

[0030] In a fourth aspect, the present application provides a computer-readable storage medium, which stores a computer program. When the computer program runs on a processor, the processor executes the method described in the first aspect or any possible implementation of the first aspect.

[0031] In general, the above technical solutions conceived by this application have the following beneficial effects compared with the prior art:

[0032] By using the row storage engine to carry row storage tables for storing hot data and the column storage engine to carry column storage tables for storing cold data, hot and cold data can be adaptively stored in dynamic multi-mode, achieving one-stop deployment to meet both OLTP and OLAP business requirements. Without reducing business performance, the database architecture can be simplified, deployment costs and storage costs can be reduced, and real-time data analysis capabilities can be provided, enabling the storage system to support both massive data query and analysis and efficient DML operations. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] Figure 1 It is a flowchart of a storage method based on hot and cold data separation and a multi-mode database engine provided in an embodiment of the present application;

[0034] Figure 2 It is a schematic diagram of the HTAP storage architecture based on hot and cold data separation and a multi-mode database engine provided in an embodiment of the present application;

[0035] Figure 3 It is a structural schematic diagram of a storage device based on cold and hot data separation and a multi-mode database engine provided in an embodiment of the present application;

[0036] Figure 4 It is a schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0037] In order to facilitate a clearer understanding of the embodiments of the present application, some relevant background knowledge is first introduced as follows.

[0038] In the traditional database architecture, OLTP operations are implemented using relational databases, which are used to handle the access scenarios of massive devices by sharding databases and tables. In terms of OLAP, the construction of a perception data warehouse is completed based on NoSQL or analytical databases, and the data of the relational database is synchronized to the data warehouse in a scheduled or quasi-real-time manner through ETL. This architecture has two problems: (1) It requires the deployment of multiple database systems and components, which has high deployment costs, high system operation and maintenance difficulties, and poor reliability; (2) ETL regularly imports data from the relational transaction database to the data warehouse, which has a relatively large delay of tens of minutes to several hours, and the data analysis is not fresh, which makes it difficult to meet business needs.

[0039] HTAP technology is a technology that processes both transaction requests and query analysis requests based on a one-stop architecture. It eliminates the ETL process from relational transaction databases to data warehouses, and supports real-time analysis of the latest transaction data, balancing system performance and data analysis freshness.

[0040] HTAP system architecture implementation is usually divided into two categories: one is the improved loose coupling model (One size doesn't fit all), which combines the OLTP and OLAP systems through the CDC method, with good scalability and load isolation, but relatively large data latency, usually over 100ms; the other is the reformed tight coupling model (One size fit all), which uses one system to meet the needs of both OLTP and OLAP, with high data visibility but low load isolation. The tight coupling model can also be divided into single-copy system and dual-copy system according to the data storage format.

[0041] Improved loosely coupled models include systems based on distributed row storage and column storage replicas (representative systems include F1Lightning and TiDB), and systems based on single-machine disk row storage and distributed memory column storage (representative systems include MySQLHeatwave). Tightly coupled models include systems based on primary row storage and memory column storage (representative systems include Oracle, SQLServer, and DB2 BLU), and systems based on primary column storage and incremental row storage (representative systems include SAP HANA).

[0042] Based on main column storage and incremental row storage, HTAP mainly uses column storage. The main column storage engine mainly handles OLAP query loads, and the incremental row storage engine is responsible for OLTP transaction processing, and directly merges updated data into the main column storage periodically. Taking SAP HANA as an example, it uses the three-level cache mechanism of the CPU to optimize transaction processing. Among them, L1-delta caches hot data and uncompressed row storage, which is saved in row storage format, and the data is also written to the redo log for persistence; L2-delta caches lightly compressed column storage, such as dictionary compression, and batch imported data, which is saved in column storage format; when the data in the L2 cache reaches a certain amount, it is merged into the L3-main store column storage and saved in heavily compressed column storage. Updates occur in L1-delta, and the L3-main store column storage is used for query acceleration and data persistence. Due to the small amount of row storage cache data, the OLTP processing performance is medium and the scalability is low.

[0043] Some databases support multiple storage engines at the same time, supporting both row storage engines and column storage engines. The two engines meet the two opposing application scenarios of OLTP and OLAP respectively. The row storage engine is mainly used to meet high-concurrency, low-latency OLTP requests, and the column storage engine is used to meet bandwidth-intensive, high-latency OLAP requests. The query performance of row storage tables drops significantly when the number of records reaches billions, while column storage tables are suitable for insert operations but not for concurrent deletion and update.

[0044] Traditional row-based tables and traditional column-based tables satisfy two opposing application scenarios, OLTP and OLAP, respectively. The query performance of traditional row-based tables drops significantly when the number of records reaches billions, while the concurrent deletion and update performance of traditional column-based tables is low.

[0045] In order to overcome the above-mentioned defects, the present application provides a storage method and device based on hot and cold data separation and a multi-mode database engine. By carrying a row storage table for storing hot data through a row storage engine and carrying a column storage table for storing cold data through a column storage engine, the storage system can support both massive data query and analysis and efficient DML operations.

[0046] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0047] In the embodiments of the present application, words such as "exemplary" or "for example" are used to indicate examples, illustrations or descriptions. Any embodiment or design described as "exemplary" or "for example" in the embodiments of the present application should not be interpreted as being more preferred or more advantageous than other embodiments or designs. Specifically, the use of words such as "exemplary" or "for example" is intended to present related concepts in a specific way.

[0048] In the description of the embodiments of the present application, unless otherwise specified, "multiple" means two or more than two. For example, multiple processing units refer to two or more processing units, etc.; multiple elements refer to two or more elements, etc.

[0049] First, the technical terms involved in the embodiments of the present application are introduced.

[0050] (1) HTAP (Hybrid transaction / analytical processing): Hybrid transaction and analytical processing is a technology that processes both transaction requests and query analysis requests based on a one-stop architecture.

[0051] (2) Loosely coupled model (One size doesn't fit all): This model combines OLTP and OLAP systems through the Change Data Capture (CDC) technology. Its main features include: (a) Scalability. Since OLTP and OLAP systems are relatively independent, their respective systems can be expanded separately as needed to meet different load requirements; (b) Load isolation. Due to the loose coupling between the two, the loads of OLTP and OLAP will not affect each other, and the resource usage of transaction processing and analytical processing can be effectively isolated; (c) Data latency. Since data needs to be synchronized through CDC, it usually leads to large data latency, which means that the latest transaction data may not be available when performing real-time analysis. This model is suitable for scenarios that do not require high real-time performance but require high scalability and load isolation.

[0052] (3) Tightly coupled model (One size fit all): One system meets the needs of both OLTP and OLAP. Its main features include: (a) High data visibility. Since OLTP and OLAP are in the same system, the real-time and consistency of data are guaranteed, and users can instantly access the latest data for analysis; (b) Low load isolation. Since OLTP and OLAP share the same resources, it may cause resource competition between transaction processing and analytical processing under high load conditions, thereby affecting the overall performance of the system. The tightly coupled model can be further divided into the following two categories according to the different data storage formats: (a) Single copy system. In this system, OLTP and OLAP share the same copy of data. The advantages of this method are high data consistency and good real-time performance; (b) Dual copy system. In this system, data is stored in independent copies of OLTP and OLAP respectively. This can improve performance and load isolation to a certain extent, but it will also bring problems with data synchronization and consistency.

[0053] (4) ETL (Extract, Transform, Load): It is used to describe the process of extracting (Extract), transforming (Transform), and loading (Load) data from the source to the destination. It is the lifeline of the data warehouse.

[0054] (5) CDC (Change Data Capture): Change data capture records the change activities on the source database tables and rows, and applies these change data to the target environment.

[0055] (6) Freshness of data analysis: refers to the timeliness or update level of data relative to the current time, and measures the data synchronization delay from OLTP to OLAP.

[0056] (7) Multi-Model database engine: refers to the same database supporting multiple storage engines, including both row storage engines and column storage engines, which can simultaneously meet the application's unified management requirements for structured, semi-structured, and unstructured data.

[0057] (8) Row storage: Also known as row storage or horizontal storage, it is a traditional database storage method. In this method, data is organized according to row-based logical storage units. Each row contains several fields or columns, which are arranged in a certain order.

[0058] (9) Row storage engine: A functional module that stores data in the form of rows. It is suitable for processing frequent transactional operations (OLTP) and can quickly access and update entire rows of data.

[0059] (10) Column storage: Also known as column storage or vertical storage, it is an emerging database storage method. In this method, data is organized based on columns as logical storage units. Each column contains several fields or values, which are compressed and stored in CUs. A column can have multiple CUs.

[0060] (11) Column storage engine: A functional module that stores data in the form of columns. It is suitable for large-scale analytical queries (OLAP) and can efficiently read and aggregate data in specific columns.

[0061] (12) CU (Compression Unit): Compression unit is the smallest storage unit of column-stored data. Some column-stored databases call it a segment.

[0062] (13) Vectorized engine: Vector storage engine is an emerging data storage technology that stores and accesses data in vector units. It is efficient, parallel, and flexible and is usually used for column storage operations.

[0063] (14) IMC (In-Memory Change): Saves the change log of the column storage CU, which is used to determine the visibility of the column storage data and improve the column storage update / deletion performance.

[0064] (15) Cold Data: refers to data with low access frequency. This data may exist in the system for a long time but is rarely accessed. Despite the low access frequency, cold data also occupies a certain amount of resources in data storage and management.

[0065] (16) Hot Data: refers to data with a very high access frequency. This data is requested or updated in large quantities in a short period of time and is often the core data of the system. Due to frequent access, hot data has a great impact on system performance and response speed.

[0066] (17) DML (Data Manipulation Language) operations: mainly include insert (INSERT), update (UPDATE), delete (DELETE) and query (SELECT).

[0067] (18) TBLRU (Two-Bit Least Recently Used) is a cache eviction algorithm that aims to improve cache hit rate and optimize memory usage. It is an improvement on the traditional LRU (Least Recently Used) algorithm, combining a two-bit counter mechanism to reduce the management overhead of each cache item.

[0068] The embodiments of the present application are described below in conjunction with the drawings in the embodiments of the present application.

[0069] Figure 1 is a flow chart of a storage method based on cold and hot data separation and a multi-mode database engine provided in an embodiment of the present application, such as Figure 1 As shown, the method includes the following steps S101 and S102.

[0070] Step S101, monitoring the row storage table and monitoring the column storage table;

[0071] Step S102: if there is cold data in the row storage table, the cold data in the row storage table is transferred and stored in the column storage table; if there is hot data in the column storage table, the hot data in the column storage table is transferred and stored in the row storage table;

[0072] The row storage table is used to store hot data, and the column storage table is used to store cold data. A piece of stored data is stored in a row storage table or a column storage table in a single copy. The access frequency of hot data is higher than the access frequency of cold data. The row storage table is carried by the row storage engine, and the column storage table is carried by the column storage engine.

[0073] It is understandable that for tables (large tables) storing a large amount of data (for example, billions of records) in the prior art, by analyzing the data and load of multiple business systems (such as customer orders, bank accounts, transaction risk control, IoT monitoring, etc.), it can be found that not all data in the large table needs to be involved in calculations at any time. Only a small amount of data will be frequently operated and is in a hot data state. Most of the data rarely participates in calculations and is in a cold data state. That is, the proportion of hot data in the large table is generally less than the proportion of cold data in the large table. The hot data with a smaller proportion of data undertakes most of the business operations. For example, hot data accounts for 20% of the large table, and cold data accounts for 80% of the large table.

[0074] The present application monitors the row storage table and the column storage table, transfers the cold data on the row storage table to the column storage table, and transfers the hot data on the column storage table to the row storage table. Hot data with a smaller data ratio can be stored in the row storage table, while cold data with a larger data ratio can be stored in the column storage table. A copy of the stored data is stored in the row storage table or the column storage table in a single copy, thereby realizing the separation of hot and cold data.

[0075] Row storage tables are used to store hot data to avoid storing large amounts of data in row storage tables. Row storage engines are suitable for processing frequent transactional operations (OLTP). Row storage engines carry row storage tables (storing hot data with a relatively small proportion of data) and can quickly insert (INSERT) data, update (UPDATE) data, and delete (DELETE) data to achieve efficient DML operations.

[0076] Column-based tables are used to store cold data, and column-based engines are suitable for large-scale analytical queries (OLAP). Column-based engines carry column-based tables (which store a relatively large proportion of cold data) and can efficiently read and aggregate data in specific columns, effectively supporting massive data queries and analysis.

[0077] The row storage engine and column storage engine constitute a multi-mode database engine.

[0078] Therefore, by using the row storage engine to carry row storage tables for storing hot data and the column storage engine to carry column storage tables for storing cold data, it is possible to perform adaptive dynamic multi-mode storage of hot and cold data, achieve one-stop deployment and meet both OLTP and OLAP business requirements, simplify the database architecture, reduce deployment and storage costs without reducing business performance, and provide real-time data analysis capabilities, so that the storage system can support both massive data query and analysis and efficient DML operations, without users being aware of the underlying storage.

[0079] In a possible implementation, the monitoring row storage table includes:

[0080] Determine whether the time interval between the current time and the time when the row data was last updated exceeds the time interval threshold;

[0081] If it is determined that the time interval between the current time and the time when the row-stored data was last updated exceeds the time interval threshold, the row-stored data is determined to be cold data.

[0082] In a possible implementation, the monitoring row storage table includes:

[0083] Determine whether the data volume of the row storage table exceeds the data volume threshold;

[0084] If it is determined that the data volume of the row storage table exceeds the data volume threshold, the cache elimination algorithm is used to filter out the eliminated row storage data in the row storage table as cold data.

[0085] In a possible implementation, the monitoring column storage table includes:

[0086] Determine whether an event of updating column data occurs in the column storage table;

[0087] If it is determined that a column data update event occurs in the column storage table, the updated column storage data is treated as hot data.

[0088] In a possible implementation, the method further includes:

[0089] Periodically merge the records of the column storage data change log (IMC log) into the column storage bitmap and clear the column storage data change log.

[0090] In a possible implementation, the method further includes:

[0091] When receiving a SQL request, multiple execution plans are generated by parsing the SQL request;

[0092] Analyze the total computational cost corresponding to each execution plan, and determine the one with the smallest total computational cost among multiple execution plans as the target execution plan.

[0093] In a possible implementation, the method further includes:

[0094] When the amount of data in the batch insert operation exceeds the data insertion amount threshold, the data is inserted into the column storage table in column storage mode.

[0095] When the amount of data in a batch update operation exceeds the update data amount threshold, the new data is stored in a column storage table in a column storage manner.

[0096] For example, Figure 2 is a schematic diagram of the HTAP storage architecture based on hot and cold data separation and a multi-mode database engine provided in an embodiment of the present application, such as Figure 2 As shown, it can be a single-copy tightly coupled HTAP architecture, using hot and cold data separation technology. An HTAP table consists of two sub-tables: a row storage table for hot data and a column storage table for cold data. Hot data has a high access frequency and a small data volume, and can be stored on high-performance storage devices, such as SSDs (solid-state drives) or storage devices that support NVME (non-volatile memory high-speed interface); cold data has a low access frequency and a large data volume, and can be stored on low-cost storage devices, such as disks. Figure 2 WAL (Write-Ahead Log) means write-ahead log, and IMC (In-Memory Change) means column storage CU change log.

[0097] HTAP tables use a single-copy data storage method. Records are either stored in a row-store table or a column-store table. There is only one copy of the data and no redundant data, ensuring data consistency and real-time data access.

[0098] The principle of determining hot and cold data is based on the last update time of the data and the amount of data in the row storage table. When the data update time exceeds the threshold, or the amount of data in the row storage table exceeds the threshold, the TBLRU elimination algorithm is used to convert the specified row storage data (eliminated row storage data) into column storage format for compression and storage. Exemplarily, the access frequency can be marked by the TBLRU elimination algorithm: in the row storage table, the TBLRU algorithm is used to mark the access frequency of each row of data. Through two bits (use bit and modification bit), the system can determine which row data is "hot" (frequently accessed) and "cold" (infrequently accessed). Select rows for transfer through the TBLRU elimination algorithm: when the amount of data exceeds the threshold, the row data with the use bit of 0 can be selected for batch transfer. These row data have not been accessed recently and are suitable for compression and transfer. Among them, if the use bit is 0, it means that the item has not been accessed recently and can be eliminated. If the use bit is 1, it means that the item has been accessed recently, but may be accessed again in the future.

[0099] The system background dedicated thread periodically scans the row storage table, and according to the hot and cold data judgment principle (data update time exceeds the threshold or the amount of data in the row storage table exceeds the threshold), automatically converts the cold data in the row storage table into column storage through the Merge operation, and clears the cold data in the row storage table. This operation can also be triggered manually.

[0100] The DML operations on the row-store table are consistent with those of the traditional row-store engine. The record deletion on the column-store table is converted into the IMC log insert operation. The record update on the column-store table is split into the IMC log insert operation and the row-store table data insert operation.

[0101] The system automatically merges the records of the IMC log into the column storage CU bitmap and clears the IMC log. The visibility of the column storage table data is determined by the records of the column storage CU bitmap and the IMC log.

[0102] It is understandable that by merging the records in the IMC log into the column storage CU bitmap, the system can directly use the updated column storage CU bitmap when querying, thereby reducing the reliance on the IMC log and reducing query latency. In addition, regularly clearing the IMC log can free up this space and avoid the log file being too large, which affects system performance. Therefore, regularly merging the records of the IMC log into the column storage CU bitmap and clearing the IMC log can effectively improve query performance and manage storage space.

[0103] The data statistics of row-stored and column-stored tables are stored independently. Row-stored statistics include: number of rows, average value, maximum value, minimum value, null value ratio, etc. of each column. Column-stored statistics include: column cardinality, distribution, compression ratio, index usage, etc.

[0104] The database receives the SQL request from the business component and uses the optimizer (such as the adaptive optimizer algorithm) to calculate the cost based on the row storage and column storage statistics, select different execution plans, and then evaluate the total cost corresponding to each execution plan. The execution plan with the minimum total cost is determined as the target execution plan. The row storage / column storage operators may be the same or different (for example, column storage uses indexes, while row storage uses full table scans). If the request only involves row storage or column storage data of the HTAP table, pruning is performed when generating the plan, and only the row storage engine or column storage engine is used. Among them, the execution plan is a detailed step description generated when processing the SQL request, which describes how to retrieve and process data from the database. The execution plan is usually represented in a tree structure or a graph structure, where each node represents an operation (operator) and the edges represent the dependencies between operations.

[0105] The optimizer may specifically be a cost-based optimization (CBO) algorithm, which uses statistical information (such as the number of rows in a table, the cardinality of columns, data distribution, etc.) to estimate the costs of different execution plans.

[0106] The process of determining the execution plan with the lowest total cost may specifically include: parsing the SQL request, checking the correctness of syntax and semantics, and generating an abstract syntax tree (AST); generating a logical execution plan based on the AST to describe the logical structure of the SQL request; and then the optimizer generates multiple execution plans based on the logical execution plan; and then the optimizer will evaluate the cost of each plan, and the optimizer will use statistical information (such as the number of rows in the table, the cardinality of the columns, data distribution, etc.) to estimate the cost of each execution plan; after the cost evaluation, the optimizer will select the execution plan with the lowest total cost. This plan will be submitted to the database engine for execution.

[0107] Cost evaluation is usually based on the following factors: (1) I / O cost, which is the number of disk accesses required to read data; (2) CPU cost, which is the computing resources required to process data; (3) memory usage, which is the space occupied by the execution plan in memory; and (4) network cost, which is the network latency and bandwidth usage for data transmission in a distributed database.

[0108] For insert operations, if it is a single insert operation, the data is directly inserted into the row storage table as hot data. If it is a batch insert, and the data volume exceeds a certain threshold, the data is converted to column storage mode and a new CU is directly generated and inserted into the column storage table to reduce the amount of data in the row storage table, because the batch insert data is most likely not hot data.

[0109] For the delete operation, if a row-stored table data is deleted, it is deleted directly on the row-stored table, which is consistent with the traditional row-stored engine. If a column-stored table data is deleted, the delete operation is converted into inserting a delete log in the IMC log.

[0110] For update operations, if a row-stored table data is updated, it is updated directly on the row-stored table, consistent with the traditional row-stored engine. If a column-stored table data is updated, the update operation is converted to inserting a deletion log in the IMC log and inserting a new data in the row-stored table. If the batch update data volume exceeds a certain threshold, the data is converted to column storage mode to directly generate a new CU and insert it into the column storage table to reduce the amount of row-stored table data, because the batch update data is most likely not hot data.

[0111] For query (Select) operations, the optimizer chooses to use the row storage engine or column storage engine based on whether the request involves only row storage or column storage data of the HTAP table. If both row storage and column storage data need to be accessed at the same time, the vectorized engine is used to return the column storage table data, and then the row storage engine is used to query the row storage table. The vectorized engine can efficiently process column storage data and take advantage of vectorized execution to improve query performance.

[0112] Supports mixed query of row-stored tables and column-stored tables in HTAP tables in the same SQL statement.

[0113] Therefore, there is only one copy of data in row storage and column storage at the same time. Data can be automatically converted between row storage and column storage. Column storage data is converted to row storage hot data through Update operation, and row storage cold data is automatically merged to column storage data at regular intervals. Therefore, the freshness of data can be guaranteed (for example, the freshness of data is controlled to 0s).

[0114] The initial data distribution is 20% hot data and 80% cold data. In the performance stress test scenario, the load is 80% cold data and 20% hot data. Compared with the traditional column storage engine, the performance of the HTAP engine is improved by more than 100 times, reaching more than 80% of the row storage engine, and the data storage space occupied is 30% less than that of the row storage engine.

[0115] The test results and storage space usage of each test operation TPS (Transactions Per Second) of row storage, column storage and HTAP engine on two-way 36-core server Sysbench are shown in Table 1. The test data has 10 tables, each with 100 million records. Sysbench is a widely used open source benchmark tool, mainly used to evaluate system performance, especially in database and server environments.

[0116] Table 1 Test results and storage space usage data

[0117]

[0118] The storage device based on hot and cold data separation and a multi-mode database engine provided by the present application is described below. The storage device based on hot and cold data separation and a multi-mode database engine described below and the storage method based on hot and cold data separation and a multi-mode database engine described above can be referenced to each other.

[0119] Figure 3 is a schematic diagram of the structure of a storage device based on cold and hot data separation and a multi-mode database engine provided in an embodiment of the present application, such as Figure 3 As shown, the device includes: a monitoring module 10 and a cold and hot data transfer module 20. Among them:

[0120] A monitoring module 10, used for monitoring the row storage table and the column storage table;

[0121] The cold and hot data transfer module 20 is used to transfer the cold data on the row storage table to the column storage table when there is cold data in the row storage table; and to transfer the hot data on the column storage table to the row storage table when there is hot data in the column storage table;

[0122] Among them, the row storage table is used to store hot data, and the column storage table is used to store cold data. A piece of stored data is stored in a row storage table or a column storage table in a single copy. The access frequency of hot data is higher than the access frequency of cold data. The row storage table is carried by the row storage engine, and the column storage table is carried by the column storage engine.

[0123] It can be understood that the detailed functional implementation of each of the above-mentioned units / modules can be found in the introduction of the aforementioned method embodiment, and will not be repeated here.

[0124] It should be understood that the above-mentioned device is used to execute the method in the above-mentioned embodiment. The implementation principle and technical effect of the corresponding program module in the device are similar to those described in the above-mentioned method. The working process of the device can refer to the corresponding process in the above-mentioned method, which will not be repeated here.

[0125] Based on the method in the above embodiment, an embodiment of the present application provides an electronic device, Figure 4 is a schematic diagram of the structure of an electronic device provided in an embodiment of the present application, such as Figure 4 As shown, the electronic device may include: a processor (Processor) 810, a communication interface (Communications Interface) 820, a memory (Memory) 830 and a communication bus 840, wherein the processor 810, the communication interface 820, and the memory 830 communicate with each other through the communication bus 840. The processor 810 can call the logic instructions in the memory 830 to execute the method in the above embodiment.

[0126] In addition, the logic instructions in the above-mentioned memory 830 can be implemented in the form of software functional units and can be stored in a computer-readable storage medium when sold or used as an independent product. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, or the part of the technical solution, can be embodied in the form of a software product, which is stored in a storage medium and includes a number of 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 method described in each embodiment of the present application.

[0127] Based on the method in the above embodiment, an embodiment of the present application provides a computer-readable storage medium, which stores a computer program. When the computer program runs on a processor, the processor executes the method in the above embodiment.

[0128] Based on the method in the above embodiment, an embodiment of the present application provides a computer program product. When the computer program product runs on a processor, the processor executes the method in the above embodiment.

[0129] It is understandable that the processor in the embodiment of the present application may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field programmable gate arrays (FPGA) or other programmable logic devices, transistor logic devices, hardware components or any combination thereof. The general-purpose processor may be a microprocessor or any conventional processor.

[0130] The method steps in the embodiments of the present application can be implemented by hardware or by a processor executing software instructions. The software instructions can be composed of corresponding software modules, and the software modules can be stored in random access memory (RAM), flash memory, read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), registers, hard disks, mobile hard disks, CD-ROMs, or any other form of storage medium known in the art. An exemplary storage medium is coupled to the processor so that the processor can read information from the storage medium and write information to the storage medium. Of course, the storage medium can also be a component of the processor. The processor and the storage medium can be located in an ASIC.

[0131] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented by software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present application is generated in whole or in part. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions may be stored in a computer-readable storage medium or transmitted through the computer-readable storage medium. The computer instructions may be transmitted from a website site, computer, server or data center to another website site, computer, server or data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium may be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more available media integrated. The available medium may be a magnetic medium (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid-state drive (SSD)), etc.

[0132] It should be understood that the various numerical numbers involved in the embodiments of the present application are only used for the convenience of description and are not used to limit the scope of the embodiments of the present application.

[0133] It will be easily understood by those skilled in the art that the above description is only a preferred embodiment of the present application and is not intended to limit the present application. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the present application shall be included in the scope of protection of the present application.

Claims

1. A storage method based on cold and hot data separation and multi-mode database engine, characterized in that: include: Monitor row storage table and monitor column storage table; When cold data exists in the row storage table, the cold data on the row storage table is transferred and stored in the column storage table; when hot data exists in the column storage table, the hot data on the column storage table is transferred and stored in the row storage table; The row storage table is used to store hot data, and the column storage table is used to store cold data. A piece of stored data is stored in a row storage table or a column storage table in a single copy. The access frequency of hot data is higher than that of cold data. The row storage table is carried by the row storage engine, and the column storage table is carried by the column storage engine. The monitoring column storage table includes: Determine whether an event of updating column data occurs in the column storage table; If it is determined that a column data update event occurs in the column storage table, the updated column storage data is treated as hot data; Also includes: Record updates on column-stored tables are divided into insert operations for column-stored data change logs and insert operations for row-stored table data. If a row of column-stored table data is deleted, the delete operation is converted into inserting a delete log into the column-stored data change log; Periodically merge the records of the column storage data change log into the column storage bitmap and clear the column storage data change log.

2. The storage method based on cold and hot data separation and multi-mode database engine according to claim 1 is characterized in that: The monitoring row storage table includes: Determine whether the time interval between the current time and the time when the row data was last updated exceeds the time interval threshold; If it is determined that the time interval between the current time and the time when the row-stored data was last updated exceeds the time interval threshold, the row-stored data is determined to be cold data.

3. The storage method based on cold and hot data separation and multi-mode database engine according to claim 1 is characterized in that: The monitoring row storage table includes: Determine whether the data volume of the row storage table exceeds the data volume threshold; If it is determined that the data volume of the row storage table exceeds the data volume threshold, the cache elimination algorithm is used to filter out the eliminated row storage data in the row storage table as cold data.

4. The storage method based on cold and hot data separation and multi-mode database engine according to claim 1 is characterized in that: Also includes: When receiving a SQL request, multiple execution plans are generated by parsing the SQL request; Analyze the total computational cost corresponding to each execution plan, and determine the one with the smallest total computational cost among multiple execution plans as the target execution plan.

5. The storage method based on cold and hot data separation and multi-mode database engine according to any one of claims 1 to 4, characterized in that: Also includes: When the amount of data in a batch insert operation exceeds the data insertion amount threshold, the data is inserted into the column storage table in column storage mode. When the amount of data in the batch update operation exceeds the update data amount threshold, the new data is stored in the column storage table in a column storage manner.

6. A storage device based on cold and hot data separation and multi-mode database engine, characterized in that: include: A monitoring module, used for monitoring the row storage table and the column storage table; The cold and hot data transfer module is used to transfer the cold data on the row storage table to the column storage table when there is cold data in the row storage table; and to transfer the hot data on the column storage table to the row storage table when there is hot data in the column storage table; The row storage table is used to store hot data, and the column storage table is used to store cold data. A piece of stored data is stored in a row storage table or a column storage table in a single copy. The access frequency of hot data is higher than that of cold data. The row storage table is carried by the row storage engine, and the column storage table is carried by the column storage engine. The monitoring column storage table includes: Determine whether an event of updating column data occurs in the column storage table; If it is determined that a column data update event occurs in the column storage table, the updated column storage data is treated as hot data. Also includes: Record updates on column-stored tables are divided into insert operations for column-stored data change logs and insert operations for row-stored table data. If a row of column-stored table data is deleted, the delete operation is converted into inserting a delete log into the column-stored data change log; Periodically merge the records of the column storage data change log into the column storage bitmap and clear the column storage data change log.

7. An electronic device, characterized in that: include: at least one memory for storing a computer program; At least one processor is used to execute the program stored in the memory. When the program stored in the memory is executed, the processor is used to execute the method according to any one of claims 1 to 5.

8. A computer-readable storage medium storing a computer program, characterized in that: When the computer program runs on a processor, the processor is caused to execute the method according to any one of claims 1 to 5.

Images