HTAP database construction method and device, equipment and medium

Abstract

The invention relates to the technical field of data processing, and discloses an HTAP database construction method and device, equipment and a medium, which can select a plurality of interested target column names and a plurality of target row records from a plurality of column names and a plurality of row records stored in an initial row storage table. And creating a proxy key index column in the initial row storage table, and in the proxy key index column, starting from a preset initial proxy key index identifier, allocating progressive increasing and continuous proxy key index identifiers to each target row record to obtain a target row storage table. And for any target column name, creating column data corresponding to the target column name according to the target column name and each proxy key index identifier. And creating a target column storage table according to each target column name and the corresponding column data. A hybrid transactional processing and analytic processing HTAP database is created that includes a target row store table and a target column store table. According to the database created by the method, the column data query efficiency can be improved based on the target column storage table.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a HTAP database construction method, device, equipment and medium. Background Art

[0002] With the development of computer science and technology, database technology continues to improve.

[0003] The database can use row storage tables to store data generated by users or enterprises during business production and processing, optimize the storage efficiency of add, delete, and modify operations in row units, support transaction processing and concurrent access control, and ensure that users can access the freshest data.

[0004] However, in databases that use row storage tables to store data, column data queries are less efficient. Summary of the invention

[0005] The present invention provides a HTAP database construction method, device, equipment and medium, which are used to solve the defect of low column data query efficiency in the database using row storage tables to store data in the related art, and improve the column data query efficiency.

[0006] In a first aspect, the present invention provides a method for constructing an HTAP database, comprising: In response to the data selection instruction, selecting a plurality of target column names and a plurality of target row records of interest from a plurality of column names and a plurality of row records stored in the initial row storage table; Creating a surrogate key index column in the initial row storage table, and assigning an incremental and continuous surrogate key index identifier to each target row record in the surrogate key index column starting from a preset initial surrogate key index identifier, to obtain a target row storage table; For any of the target column names, creating column data corresponding to the target column name according to the target column name and each of the surrogate key index identifiers; Creating a target column storage table according to each target column name and the corresponding column data; A hybrid transaction processing and analytical processing HTAP database including the target row storage table and the target column storage table is constructed; wherein the target row storage table and the target column storage table in the HTAP database are used for transaction processing and column data query, respectively.

[0007] Optionally, creating column data corresponding to the target column name according to the target column name and each of the surrogate key index identifiers includes: Determine the record value corresponding to the target column name in each row record corresponding to the surrogate key index identifier; Determining that the number of characters of at least N of the record values ​​is greater than a first preset threshold, and determining that the number of record value repetitions in all the record values ​​exceeds a second preset threshold, grouping all the record values ​​to obtain at least one corresponding record value category, and setting a unique corresponding dictionary table code for each of the at least one record value categories; wherein N is an integer greater than 1; For any of the record values, determine the target dictionary table code corresponding to the record value according to the record value category corresponding to the record value, determine the target surrogate key index identifier corresponding to the record value according to the target row record corresponding to the record value, determine the ascending arrangement order of the target surrogate key index identifier among all the surrogate key index identifiers, and determine the ascending arrangement order as the to-be-arranged order of the target dictionary table code; According to the order to be arranged of each target dictionary table code, the target dictionary table code corresponding to each record value is arranged to obtain an arrangement result as the column data corresponding to the target column name.

[0008] Optionally, after constructing the hybrid transaction processing and analytical processing HTAP database including the target row storage table and the target column storage table, the method further includes: Determine the total number of the surrogate key index identifiers in the surrogate key index column, create a freshness vector index column with a total number of cells equal to the total number; and create an operation type index table; In response to a row record operation instruction on the target row storage table, a data operation is performed on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database.

[0009] Optionally, the multiple column names include an operation timestamp, and the initial surrogate key index identifier is 1; the step of responding to the row record operation instruction on the target row storage table, performing data operation on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database includes: In response to an update instruction for a record value corresponding to the target column name in a first row of records, updating the record value corresponding to the target column name in the first row of records, and determining a first timestamp at which the update operation occurs and a first entry address of the first row of records in the target row storage table; Saving the first timestamp to the cell corresponding to the operation timestamp in the first row of records, and storing the first entry address and the update operation identifier in the operation type index table in correspondence; Determine a first surrogate key index identifier corresponding to the first row of records, determine a first cell whose cell order is equal to the first surrogate key index identifier among multiple cells of the freshness vector index column, and save the first timestamp in the first cell.

[0010] Optionally, in response to the row record operation instruction on the target row storage table, performing data operation on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database further includes: In response to an instruction to add a second row of records in the target row storage table, add the second row of records in the target row storage table, determine a second timestamp of when the adding operation occurs, and store the second timestamp in a second cell corresponding to the operation timestamp in the second row of records; Determine that the second row record is the row record of interest, then continue to assign an incremented second surrogate key index identifier to the second row record in the surrogate key index column, obtain the second entry address of the second row record in the target row storage table, and store the second entry address and the newly added operation identifier in the operation type index table accordingly.

[0011] Optionally, in response to the row record operation instruction on the target row storage table, performing data operation on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database further includes: In response to a delete instruction for the third row record in the target row storage table, modify the identification value in the deletion identification bit corresponding to the third row record from a non-deletion identification value to a deleted identification value, record a third timestamp of the modification operation, and store the third timestamp in the cell corresponding to the operation timestamp in the third row record; If it is determined that the third row of records includes a third surrogate key index identifier, a third cell having a cell order equal to the third surrogate key index identifier is determined from among the multiple cells of the freshness vector index column, and the third timestamp is stored in the third cell.

[0012] Optionally, after performing data operation on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database in response to the row record operation instruction on the target row storage table, the method further includes: receiving a query instruction for the target column data corresponding to the target column name in the target column storage table, and recording a receiving timestamp of the query instruction; Find each empty value cell in the freshness vector index column, determine the first cell order of each empty value cell in the freshness vector index column, and determine the record value located in each first cell order in the target column data as the record value allowed to be accessed; Find at least one target timestamp greater than the received timestamp from each timestamp currently stored in the freshness vector index column, and determine a second cell order of each target timestamp in the freshness vector index column; For any of the second cell sequences, determine a fourth surrogate key index identifier of the same size as the second cell sequence, determine a fourth row of records corresponding to the fourth surrogate key identifier index in the target row storage table, and determine a record value corresponding to the target column name in the fourth row of records as an access-permitted record value; Obtain each entry address currently stored in the operation type index table, and search the target row storage table for a row record corresponding to each entry address; For any row record corresponding to the entry address, determine the timestamp corresponding to the operation timestamp in the row record, and if the determined timestamp is less than the receiving timestamp, determine the record value corresponding to the target column name in the row record as the record value that allows access; All the access permission record values ​​are taken as a whole as the query result corresponding to the query instruction, and the query result is output.

[0013] In a second aspect, the present invention provides a HTAP database construction device, comprising: A selection unit, configured to select, in response to a data selection instruction, a plurality of target column names and a plurality of target row records of interest from a plurality of column names and a plurality of row records stored in an initial row storage table; A first creation unit, configured to create a surrogate key index column in the initial row storage table; an allocating unit, configured to allocate, in the surrogate key index column, an increasing and continuous surrogate key index identifier to each of the target row records starting from a preset initial surrogate key index identifier, to obtain a target row storage table; A second creation unit is used to create, for any of the target column names, column data corresponding to the target column name according to the target column name and each of the surrogate key index identifiers; A third creating unit, configured to create a target column storage table according to each target column name and the corresponding column data; A construction unit is used to construct a hybrid transaction processing and analytical processing HTAP database including the target row storage table and the target column storage table; wherein the target row storage table and the target column storage table in the HTAP database are used for transaction processing and column data query respectively.

[0014] In a third aspect, the present invention provides a computer device, comprising: a memory and a processor, the memory and the processor being communicatively connected to each other, the memory storing computer instructions, and the processor executing the HTAP database construction method of the first aspect or any corresponding embodiment thereof by executing the computer instructions.

[0015] In a fourth aspect, the present invention provides a computer-readable storage medium having computer instructions stored thereon, the computer instructions being used to enable a computer to execute the HTAP database construction method of the first aspect or any corresponding embodiment thereof.

[0016] The HTAP database construction method, device, equipment and medium provided by the present invention can select multiple target column names and multiple target row records of interest from multiple column names and multiple row records stored in the initial row storage table in response to a data selection instruction. Create a surrogate key index column in the initial row storage table, and in the surrogate key index column, assign an incremental and continuous surrogate key index identifier to each target row record starting from the preset initial surrogate key index identifier to obtain a target row storage table. For any target column name, create column data corresponding to the target column name according to the target column name and each surrogate key index identifier. Create a target column storage table according to each target column name and the corresponding column data. Create a database including a target row storage table and a target column storage table; wherein the target row storage table and the target column storage table in the database are used for transaction processing and column data query, respectively. The hybrid transaction processing and analytical processing HTAP database created by the present invention can improve the column data query efficiency based on the target column storage table while ensuring the transaction processing efficiency based on the target row storage table. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the technical solutions in the present invention or the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0018] Figure 1 A flowchart of a HTAP database construction method provided by an embodiment of the present invention; Figure 2 A schematic diagram of a HTAP database construction and update process provided by an embodiment of the present invention; Figure 3 A schematic diagram of an HTAP database update process provided by an embodiment of the present invention; Figure 4A schematic diagram of a hybrid storage model structure for HTAP queries provided by an embodiment of the present invention; Figure 5 A schematic diagram of another HTAP database update process provided by an embodiment of the present invention; Figure 6 A schematic diagram of the structure of a HTAP database construction device provided by an embodiment of the present invention; Figure 7 A schematic diagram of the structure of a computer device provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0019] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with the drawings of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0020] Combine the following Figure 1-Figure 5 The HTAP database construction method of the present invention is described.

[0021] like Figure 1 As shown, this embodiment proposes a first HTAP database construction method, which may include the following steps: S101 . In response to a data selection instruction, multiple target column names and multiple target row records of interest are selected from multiple column names and multiple row records stored in an initial row storage table.

[0022] It should be noted that the initial row storage table may include a table header and multiple row records, and multiple column names may be defined in the table header. Each row record may include record values ​​corresponding to all column names, and each row record may be a complete piece of data. For example, the table header of the initial row storage table may include column names such as A3, A2, A1, Deleted, and SCN, where the Deleted column is used to identify whether the row record is deleted, and SCN is used to identify the timestamp of the latest operation row record. The initial row storage table may include multiple row records, and the record values ​​corresponding to the A3 column may include AIR, RAIL, SHIP, and TRUCK. In the related art, a database that uses a row storage table to store data, when faced with a query of a small number of columns, such as only querying one or two columns of data in the row storage table, will result in a query of a large amount of data in the row storage table, and the query efficiency is low.

[0023] Specifically, in this embodiment, a user or technician can select multiple column names and multiple row records of interest in the initial row storage table, and create a corresponding column storage table based on the selected column names and row records. This embodiment can construct a database including a column storage table and a row storage table. When facing a column query, data query can be performed directly in the column storage table, thereby improving data query efficiency.

[0024] The data selection instruction may be input by a user or a technician to select the column name and row record that the user is interested in. The data selection instruction may carry the column name identifier and row record identifier of interest. In this embodiment, the corresponding column name and row record may be selected according to the column name identifier and row record identifier carried in the data selection instruction.

[0025] Specifically, the target column name and the target row record may be the column name and row record that the user or technician is interested in. It is understandable that the target column name may be a column name that is more popular in actual applications and has a higher number of queries.

[0026] S102: Create a surrogate key index column in the initial row storage table.

[0027] Specifically, in this embodiment, a surrogate key index column may be added to the initial row storage table.

[0028] like Figure 2 As shown, in this embodiment, a surrogate key index column SK may be added to the initial row storage table.

[0029] S103 . In the surrogate key index column, assigning an increasing and continuous surrogate key index identifier to each target row record starting from a preset initial surrogate key index identifier to obtain a target row storage table.

[0030] Specifically, in this embodiment, in the surrogate key index column, continuous and increasing surrogate key index identifiers may be allocated to the multiple target row records according to the order in which the multiple target row records are arranged in the row storage table.

[0031] like Figure 2 As shown, in this embodiment, continuous and increasing surrogate key index identifiers 1, 2, 3, ... 13 may be allocated to the selected multiple target row records in the SK column. The row records that are not allocated with surrogate key index identifiers are the unselected row records.

[0032] Of course, this embodiment may also allocate surrogate key index identifiers not according to the order in which the above-mentioned multiple target row records are arranged in the row storage table. For example, this embodiment may randomly allocate increasing and continuous surrogate key index identifiers to the above-mentioned multiple target row records in the surrogate key index column.

[0033] S104: For any target column name, create column data corresponding to the target column name according to the target column name and each surrogate key index identifier.

[0034] Specifically, this embodiment can determine, for any target column name, the record value corresponding to the target column name in the row record corresponding to each surrogate key index identifier in order from small to large, and determine the order of these record values ​​in sequence to convert these record values ​​into column data corresponding to the target column name.

[0035] It should be noted that, in this embodiment, there is no need to perform sorting operations on row records and column records, so as to avoid increasing excessive sorting costs. Even the proxy key index value in the row record only needs to ensure the connection increment, and does not need to be consistent with the physical order. It can be mapped to the corresponding column record position in the column storage according to the proxy key value. That is, the proxy key on the row storage side can be logically ordered but physically unordered, and the column storage is naturally ordered according to the array position mapped by the proxy key value.

[0036] Optionally, step S104 may include: Determine the record value corresponding to the target column name in each row record corresponding to the surrogate key index identifier; Determining that the number of characters of at least N record values ​​is greater than a first preset threshold, and determining that the number of repetitions of record values ​​in all record values ​​exceeds a second preset threshold, grouping all record values ​​to obtain at least one corresponding record value category, and setting a unique corresponding dictionary table code for each at least one record value category; wherein N is an integer greater than 1; For any record value, determine the target dictionary table code corresponding to the record value according to the record value category corresponding to the record value, determine the target surrogate key index identifier corresponding to the record value according to the target row record corresponding to the record value, determine the ascending sorting order of the target surrogate key index identifier among all surrogate key index identifiers, and determine the ascending sorting order as the to-be-sorted order of the target dictionary table code; According to the order to be arranged of each target dictionary table code, the target dictionary table code corresponding to each record value is arranged to obtain an arrangement result as the column data corresponding to the target column name.

[0037] The first preset threshold and the second preset threshold can be set by a technician according to actual conditions, and are not limited in this embodiment.

[0038] It can be understood that in this embodiment, when determining a column of data in the target row storage table, if there are more record values ​​with more characters and more repeated record values, the record values ​​on the column data can be grouped to divide into multiple record value categories, and corresponding dictionary table codes can be set for each record value category, such as 1, 2, 3, and 4. Afterwards, when creating the corresponding column data, this embodiment can use the dictionary table code to replace the corresponding record value for storage, thereby reducing data storage pressure.

[0039] like Figure 2 As shown, in the target row storage table, A3 is a target column name. Taking the target column name A3 as an example, this embodiment can group the record values ​​corresponding to the target column name A3 to obtain the corresponding record value categories AIR, RAIL, SHIP and TRUCK, and assign surrogate key index identifiers 1, 2, 3 and 4 to these record value categories respectively. Afterwards, the row record value is mapped to the surrogate key index value in the group record and used as the column data corresponding to the target column name A3. Figure 2 In the right part, the column data corresponding to column name A3.

[0040] S105: Create a target column storage table according to each target column name and corresponding column data.

[0041] Specifically, in this embodiment, each target column name and the corresponding column data can be combined, and the result of the combination can be used as the target column storage table. Figure 2 The column data corresponding to the column names A1, A2 and A3 in the right part.

[0042] It can be understood that the target column storage table includes each target column name and corresponding column data.

[0043] S106: Create a Hybrid Transactional / Analytical Processing (HTAP) database including a target row storage table and a target column storage table; wherein the target row storage table and the target column storage table in the HTAP database are used for transaction processing and column data query, respectively.

[0044] Specifically, in this embodiment, after constructing the target row storage table and the target column storage table, an HTAP database including the target row storage table and the target column storage table may be created.

[0045] Optionally, the HTAP database may be a database for storing memory data, or a database for storing other types of data. This embodiment does not limit the database type.

[0046] Specifically, in this embodiment, the target row storage table may be an online transaction processing (On-LineTransaction Processing, OLTP) row storage table, and the target column storage table may be an online analytical processing (On-lineAnalytical Processing, OLAP) column storage table. At this time, the database constructed in this embodiment may be an in-memory database that implements hybrid transactional / analytical processing (HTAP).

[0047] It should be noted that, when receiving a transaction processing instruction, this embodiment can perform corresponding transaction processing based on the target row storage table in the HTAP database. When receiving a column data query instruction corresponding to the target column name, this embodiment performs corresponding column data query based on the target column storage table in the HTAP database.

[0048] The HTAP database construction method proposed in this embodiment can select multiple target column names and multiple target row records of interest from multiple column names and multiple row records stored in the initial row storage table in response to a data selection instruction. Create a surrogate key index column in the initial row storage table, and in the surrogate key index column, assign an incremental and continuous surrogate key index identifier to each target row record starting from the preset initial surrogate key index identifier to obtain a target row storage table. For any target column name, create column data corresponding to the target column name according to the target column name and each surrogate key index identifier. Create a target column storage table according to each target column name and the corresponding column data. Create an HTAP database including a target row storage table and a target column storage table; wherein the target row storage table and the target column storage table in the HTAP database are used for transaction processing and column data query, respectively. The HTAP database in this embodiment can improve the efficiency of column data query based on the target column storage table while ensuring the efficiency of transaction processing based on the target row storage table.

[0049] It should be noted that in order to ensure the transaction processing and data query efficiency of the HTAP memory database, it is necessary to ensure the high performance of analytical queries and the freshness of data in a unified database system. Usually, a dual storage engine design is adopted, with the OLTP engine responsible for transaction processing and the OLAP engine responsible for analytical processing. The difficulty of the implementation technology lies in how to propagate the updated data in the OLTP engine transaction processing to the OLAP engine in a timely manner, ensuring the same data freshness and transaction consistency as the OLTP engine while ensuring the calculation efficiency of the OLAP engine.

[0050] based on Figure 1 This embodiment proposes a second HTAP database construction method. After step S106, the method may further include: determining a total number of surrogate key index identifiers in the surrogate key index column, creating a freshness vector index column with a total number of cells equal to the total number; and, creating an operation type index table; In response to a row record operation instruction on a target row storage table, a data operation is performed on at least one of a target row storage table, a target column storage table, a freshness vector index column, and an operation type index table in the HTAP database.

[0051] For details, see the freshness vector index column. Figure 2 The column data under the Freshness Vector Index (FVI) in the target column name is used to store the operation timestamp of the operation on the record value in the column data corresponding to the target column name. For the operation type index table, see Figure 2 The Delta Index in the table corresponds to the entry address RowID and the operation type Type that stores the row record. The data #1001 and #1002 under the RowID on the right side of the target row storage table are the entry addresses of the corresponding row records. Figure 2 In-placeupdate means the in-place update mode.

[0052] Optionally, in response to the row record operation instruction on the target row storage table, performing data operation on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database may include: In response to an update instruction for a record value corresponding to the target column name in the first row of records, the record value corresponding to the target column name in the first row of records is updated, and a first timestamp at which the update operation occurs and a first entry address of the first row of records in the target row storage table are determined; The first timestamp is saved in the cell corresponding to the first row of the operation timestamp, and the first entry address and the update operation identifier are stored in the operation type index table in correspondence; Determine a first surrogate key index identifier corresponding to a first row of records, determine a first cell having a cell order equal to the first surrogate key index identifier among multiple cells of the freshness vector index column, and save the first timestamp in the first cell.

[0053] like Figure 2As shown, when receiving an update instruction for the record value under the target column name A2 in the first row record with SK being 4, it is determined that the row record operation type is an update of the record value. According to the update instruction, this embodiment can update the record value corresponding to the target column name A2 in the first row record from 105 to 110, record the first timestamp 97 of the update operation and the first entry address #1004 of the first row record. At this time, this embodiment can store 97 in the cell corresponding to the timestamp column in the first row record, and store the first entry address #1004 and the update operation identifier U in the operation type index table. Determine the first surrogate key index identifier 4 corresponding to the first row record, determine the fourth cell from the multiple cells of the freshness vector index column, store the timestamp 97 in the fourth cell of the freshness vector index column, and the record value 105 stored in the target column storage table is temporarily not updated.

[0054] Optionally, in response to the row record operation instruction on the target row storage table, performing data operation on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database further includes: In response to an instruction to add a second row of records in the target row storage table, add a second row of records in the target row storage table, determine a second timestamp of when the adding operation occurs, and store the second timestamp in a second cell corresponding to the operation timestamp in the second row of records; If it is determined that the second row record is the row record of interest, an incremental second surrogate key index identifier is continuously assigned to the second row record in the surrogate key index column, the second entry address of the second row record in the target row storage table is obtained, and the second entry address and the newly added operation identifier are stored in the operation type index table accordingly.

[0055] like Figure 2 As shown, when receiving an instruction to add a second row record (such as the row record corresponding to entry address #1017) in the target row storage table, this embodiment can add the second row record in the target row storage table, determine that the second timestamp of the added operation is 85, and store 85 in the second cell corresponding to the operation timestamp of the second row record. The second row record can also be the row record corresponding to entry address #1018. In this case, this embodiment can add the second row record in the target row storage table, determine that the second timestamp of the added operation is 100, and store 100 in the second cell corresponding to the operation timestamp of the second row record.

[0056] Afterwards, if the embodiment determines that the second row record is the row record of interest, an incremental second surrogate key index identifier may be assigned to the second row record in the surrogate key index column, such as Figure 2The second surrogate key index identifiers 14 and 15 in the target row storage table are obtained, and the second entry address #1017 and #1018 of the second row record in the target row storage table are stored in the operation type index table correspondingly, and the second entry address #1018 and the new operation identifier I are stored in the operation type index table correspondingly.

[0057] Optionally, when an update operation occurs on a newly added row record inserted in the target row store, such as when an update operation occurs on inserted record #1017 when the timestamp is SCN=110, the row store engine implements an in-situ update and synchronously updates the SCN timestamp of the record. At this time, the RowID entry address in the Delta index remains unchanged.

[0058] Optionally, in response to the row record operation instruction on the target row storage table, performing data operation on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database further includes: In response to a delete instruction for the third row record in the target row storage table, modify the identification value in the deletion identification bit corresponding to the third row record from a non-deletion identification value to a deleted identification value, record a third timestamp of the modification operation, and store the third timestamp in a cell corresponding to the operation timestamp in the third row record; If it is determined that the third row of records includes a third surrogate key index identifier, a third cell having a cell order equal to the third surrogate key index identifier is determined from among multiple cells of the freshness vector index column, and the third timestamp is stored in the third cell.

[0059] like Figure 2 As shown, when receiving an instruction to delete the third row record with SK 10 in the target row storage table, this embodiment can modify the identification value in the deletion identification bit corresponding to the third row record from the non-deleted identification value 0 to the deleted identification value 1, and record the third timestamp 90 of the modification operation, and store the third timestamp 90 in the cell corresponding to the operation timestamp in the third row record. When it is determined that the third row record includes the third surrogate key index identification 10, the third row record is determined to be the row record of interest, and the 10th cell, i.e., the third cell, is determined in the freshness vector index, and the third timestamp 90 is stored in the third cell.

[0060] Optionally, after performing data operation on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database in response to the row record operation instruction on the target row storage table, the method may further include: Receive a query instruction for target column data corresponding to a target column name in a target column storage table, and record a receiving timestamp of the query instruction; Find each empty value cell in the freshness vector index column, determine the first cell order of each empty value cell in the freshness vector index column, and determine the record value located in each first cell order in the target column data as the record value allowed to be accessed; Find at least one target timestamp greater than the received timestamp among the timestamps currently stored in the freshness vector index column, and determine the second cell order of each target timestamp in the freshness vector index column; For any second cell sequence, determine a fourth surrogate key index identifier of the same size as the second cell sequence, determine a fourth row of records corresponding to the fourth surrogate key identifier index in the target row storage table, and determine a record value corresponding to the target column name in the fourth row of records as a record value that is allowed to be accessed; Obtain each entry address currently stored in the operation type index table, and search for the row record corresponding to each entry address in the target row storage table; For the row record corresponding to any entry address, determine the timestamp corresponding to the operation timestamp in the row record. If the determined timestamp is less than the receiving timestamp, determine the record value corresponding to the target column name in the row record as the record value that allows access; All the access-permitted record values ​​are taken as a whole as the query result corresponding to the query instruction, and the query result is output.

[0061] like Figure 2 As shown, when receiving a column data query instruction for the target column name A3, this embodiment can directly query the target column storage table. Specifically, this embodiment can scan in the freshness vector index column to find all empty value cells, and in the A3 column data of the target column storage table, the record values ​​in the same row as all the empty value cells are determined as record values ​​that are allowed to be accessed.

[0062] Afterwards, this embodiment can determine the cells storing non-null values ​​in the freshness vector index column, and determine the target timestamp greater than the receiving timestamp in the timestamps recorded in these cells, and determine the second cell order corresponding to each cell recording the target timestamp. Afterwards, this embodiment can determine the fourth surrogate key index identifier equal to the second cell order, determine the fourth row record, and determine the record value corresponding to the A3 column in the fourth row record as the record value that allows access. For example, assuming that the receiving timestamp SCN=95, this embodiment can determine the target timestamp 97 in the freshness vector index column, and determine the second cell order 4 corresponding to the target timestamp 97. At this time, this embodiment can determine the fourth surrogate key index identifier as 4, determine the fourth row record as the row record with RowID #1004, and determine the latest record value corresponding to the A3 column in the fourth row record as the record value that allows access.

[0063] Afterwards, this embodiment can obtain each entry address #1017, #1004, and #1018 currently stored in the operation type index table, and search for the row records corresponding to the entry addresses #1017, #1004, and #1018 in the target row storage table. Determine the record value corresponding to the SCN in the row records corresponding to the entry addresses #1017, #1004, and #1018, respectively. The SCN in the row records with RowID #1004 and #1018 is 97, which is later than the query execution time and does not need to be accessed. The SCN in the row record corresponding to #1017 is 85. After the query is executed, an update operation occurs when SCN=110. The OLTP row storage table is accessed by the RowID index, and the old version record with SCN 85 is accessed through its own concurrent access control and multi-version mechanism and used as the record value allowed to be accessed.

[0064] Specifically, in this embodiment, all determined access-permitted record values ​​may be used as a whole as the column data query result corresponding to the target column name A3 and returned to the query user.

[0065] Specifically, if the above-mentioned allowed access record value is a bitmap index identifier, it can be converted into the original record value first, and then combined with all the determined target record values ​​as a column data query result corresponding to the target column name A3, and returned to the query user.

[0066] like Figure 2As shown, when the user queries the specified record value in the column data corresponding to the target column name, the present embodiment can also query in the target column storage table. For example, when the user specifies to query the specified record value AIR in the column data corresponding to the target column name A3, the present embodiment can scan each record value in the column data dictionary corresponding to A3. When the record value is determined to be AIR, the scanning result is 1, and when the record value is determined to be non-AIR, the scanning result is 0. The scanning result is a bitmap of the same length as the column data dictionary. Then, the dictionary code value is scanned on the column storage data column A3 and mapped to the corresponding position of the bitmap. If it is 1, the original data corresponding to the dictionary code meets the condition of being AIR, and if it is 0, it does not meet the condition.

[0067] Optionally, in other HTAP database construction methods proposed in this embodiment, after performing data operations on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database in response to the row record operation instruction on the target row storage table, the following method may be further included: When the number of non-null values ​​in the freshness vector index column reaches a preset threshold, the current timestamp is determined; For any entry address stored in the operation type index table, search the row record corresponding to the entry address in the target row storage table; when it is determined that the operation identifier corresponding to the entry address is an update operation identifier, determine the to-be-updated cell of each column data in the column storage table according to the surrogate key index identifier corresponding to the row record, determine the corresponding surrogate key index value according to the row record value corresponding to each target column name in the row record entry address, and store the corresponding column values ​​of the row record in the to-be-updated cells of the corresponding column data, set the record value corresponding to the operation timestamp in the row record to a null value, determine the cell whose cell order is equal to the size of the surrogate key index identifier among multiple cells in the freshness index column, and set the storage content in the determined cell to a null value; when it is determined that the operation identifier corresponding to the entry address is a new operation identifier, add the record value corresponding to each target column name in the row record to the corresponding column data, and set the record value corresponding to the operation timestamp in the row record to a null value; Delete all entry addresses and operation identifiers stored in the operation type index table.

[0068] The preset threshold value may be set by a technician according to actual conditions and is not limited in this embodiment.

[0069] It should be noted that, in this embodiment, when it is determined that the number of non-null values ​​in the freshness vector index column is not less than the preset threshold, it can be determined that a lot of data will need to be queried in the target row storage table during data query, which affects the efficiency of column data query and causes a significant reduction in data query performance in the target column storage table. At this time, the target column storage table can be updated according to the updated data in the operation type index table and the target row storage table.

[0070] Specifically, this embodiment can update the data in the target column storage table based on any entry address stored in the operation type index table. Figure 2 As shown, in this embodiment, for the entry address #1004 and the newly added operation identifier U stored in the operation type index table, the row record corresponding to the entry address #1004 can be determined in the target row storage table, and the data stored in the target column storage table can be updated accordingly according to the current record value corresponding to each target column name in the row record. For example, Figure 3 As shown, 105 in the column data corresponding to A2 in the target column storage table is updated to 110, and then 97 stored in the freshness vector index column is set to a null value. At this time, 97 under the SCN column in the target row storage table can also be set to a null value, or 97 under the SCN column in the target row storage table can be retained.

[0071] like Figure 2 As shown, in this embodiment, for the entry address #1017 and the newly added operation identifier I, the entry address #1018 and the newly added operation identifier I corresponding to the stored entry address in the operation type index table, the row records corresponding to the entry addresses #1017 and #1018 can be determined in the target row storage table, and the corresponding data can be inserted into the target column storage table according to the row records corresponding to the entry addresses #1017 and #1018, such as Figure 3 As shown in the figure, the 14th and 15th data are added to the target column storage tables A1, A2 and A3.

[0072] Specifically, in this embodiment, after updating the column data in the target column storage table according to the storage data in the operation type index table, the storage data in the operation type index table may be deleted, such as Figure 3 The operation type index table Delta Index is shown.

[0073] It should be noted that this embodiment can temporarily retain the row records marked as deleted in the target row storage table, and can also retain the deletion timestamp corresponding to the record in the freshness vector index, such as Figure 3 90 under the FVI column.

[0074] Optionally, after performing data operation on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database in response to the row record operation instruction on the target row storage table, the method further includes: In the target row storage table, the target number of row records marked for deletion and including surrogate key index identifiers is monitored, and when it is determined that the target number reaches the deletion number threshold, a new target row storage table and a new target column storage table are reconstructed based on the target row storage table.

[0075] Specifically, in this embodiment, when it is determined that the number of deleted row records reaches a threshold, the target row storage table and the target column storage table may be reconstructed to release the storage space occupied by the deleted row records.

[0076] The HTAP database construction method proposed in this embodiment can provide an efficient and flexible OLAP column storage index acceleration engine for the OLTP database. The freshness vector index and the Delta index separate the different freshness of data in the query, map the query to the fresh data set, and implement the custom column storage index and efficient update data merging through the surrogate key index. By separating the metadata and value data in the OLAP data set, the column storage engine design is simplified, and the storage and computing efficiency is improved.

[0077] The HTAP database construction method proposed in this embodiment is particularly about how the OLTP row storage engine accelerates the OLAP query processing performance by creating a column storage index, and establishes an index access method between the updated data on the OLTP side and the read-only data on the OLAP side through a series of index mechanisms, thereby realizing real-time data analysis and processing on a row-column hybrid storage engine.

[0078] The column storage index mechanism adopted in this embodiment supports the customization of row and column data subsets according to user needs on the original row storage table, and can be applied to OLAP acceleration implementation technology in scenarios such as limited memory capacity, data heat, and GPU-NVM-DRAM heterogeneous storage. The separation of column storage index metadata and numerical storage can be achieved through dictionary data compression technology, and numerical column storage indexes can be realized to simplify column storage index implementation technology. Through the index mechanism created and maintained by the OLTP end and used by the OLAP end, an index access mechanism between read-only column storage data and updateable row storage data is established, and the management function of the column storage index for Delta data is stripped off, simplifying the column storage index implementation technology; a surrogate key index is added to the OLTP row storage engine to locate the position of the record in the column storage index, and a freshness vector index is added to indicate the column record in the column storage index that has lost freshness due to the update of the original record by the OLTP engine, and a Delta index is added to indicate the entry address of the update and insert records after the column storage index is created, accelerating Delta data access, and the three indexes are implemented on the OLTP storage end with basic data structures such as data columns, vectors, and tables, simplifying index design and maintenance.

[0079] During data synchronization, efficient index access and update from the OLTP row storage engine to the column storage engine is implemented based on the surrogate key index. This implementation can be applied to the in-memory data warehouse application scenario that supports HTAP, and a hybrid row-column storage mode can be adopted, that is, the smaller dimension table (primary key table) is stored in the OLTP row storage engine, supporting general operations such as add, delete, and modify. A column storage index can be created on the larger fact table (foreign key table) to accelerate OLAP analysis and processing performance. The query tree spans the row storage engine and the column storage index engine. The dimension table on the row storage engine is used to generate hash tables, connection vectors, or Bloom filters used for connections during query execution. The fact table column storage index provides multi-dimensional computing functions based on the push model, realizing HTAP query processing on the hybrid storage and query processing engine.

[0080] In order to better illustrate the HTAP database construction method proposed in this embodiment, this embodiment proposes the following Example 1 for illustration.

[0081] In Example 1, the initial row storage table is an OLTP table, the target column storage table is an OLAP table, and the database is an HTAP memory database. Example 1 can be used for the HTAP implementation technology of the memory database with column storage indexes. The hybrid storage and query processing engine implementation technology of the OLTP row storage engine and the OLAP column storage engine can be connected through the index. This embodiment can implement the HTAP query processing of the memory database, such as Figure 4 shown.

[0082] exist Figure 4 In this embodiment, the database front end can receive a structured query language SQL query. When the SQL query is determined to be an OLTP query, the query instruction is executed in the row storage model. When the SQL query is determined to be an OLAP query, the query can be executed in the column storage index model. The row storage model can be a target row storage table related to the production line order LINEORDER stored in DRAM or NVM, and the column storage index model can be a target column storage table related to the production line order LINEORDER stored in DRAM, NVM or GPU.

[0083] Create an index structure in the OLTP row storage engine to create a column storage index data subset, record the update operation after the column storage index is created, and separate the query record subsets on the row storage engine and column storage index. The index structure can also support efficient data synchronization updates. Figure 4 As shown, this embodiment can perform data synchronization in the row storage model and the column storage index model according to the data update of the row storage model.

[0084] S1. Add a surrogate key index column to the original OLTP table to support the creation of a custom column storage index through selection and projection operations in the original OLTP row storage table. The column storage index record is used to create a column storage index record by assigning consecutive 1, 2, 3... surrogate key index values ​​to identify the offset address of the record in the column storage index. The update type on the OLTP row storage record is supported to access the column storage index record by the surrogate key index address and perform data update operations.

[0085] S2. Create a column storage index for the data columns used for OLAP in the OLTP row storage table, mainly including the attributes used for filtering and grouping at the dimension level or dimension attributes and the attribute set used for dimension connection and measurement calculation on the fact data. When the selection and grouping attributes on the dimension table are low-potential set attributes (that is, the number of different data members in the attribute column is small), create a corresponding dictionary table on the OLTP storage side, and the corresponding column in the column storage index only stores the corresponding dictionary table code to realize the numerical storage of the column storage index data. The selection operation on the column storage index is first converted into the selection operation on the fact table on the OLTP storage side, and the selection result is mapped to the dictionary table bitmap index. Then, the direct predicate processing on the compressed data is realized by mapping the column storage index dictionary table code value to the dictionary table bitmap index. The grouping operation on the column storage is directly performed on the dictionary code. The data type stored in the OLAP column storage index is simplified to a numerical type by compressing the dictionary table and directly processing the compressed data, thereby reducing the complexity of the storage design and improving the query processing performance and efficiency.

[0086] S3. When creating a column storage index for an OLTP table to accelerate OLAP queries, a column storage index is created based on available memory space, original table size, OLAP acceleration data subset, and corresponding column compression width. The data subset used for OLAP calculation acceleration in the original OLTP table is selected through the WHERE clause, and the unique incrementing surrogate key index value designed in S1 is assigned to the selected subset record as the record address of the column storage index for index update operations during update type updates. The data column used for OLAP calculation acceleration in the original OLTP table is selected through the SELECT clause, and the attribute column of the WHERE and GROUP-BY clauses in the query is compressed using the S2 method for the dictionary table. The dictionary table is stored on the OLTP storage side, and the compression code column is stored in the column storage index. This ensures that only three types of numerical data, selection, grouping compression code, foreign key, and measurement, are stored in the column storage index, eliminating data logical semantics, simplifying the column storage index data type, and improving the computing efficiency of numerical data.

[0087] S4. Create a column storage index of the OLTP row storage table based on a certain timestamp. The column storage index is a subset of numeric data compressed and stored in the OLTP table. It accelerates the OLAP computing performance on the user-defined data subset. Create a freshness vector index of the same length as the column storage index. When an update or delete operation occurs on a record in the OLTP original table, access the corresponding unit of the freshness vector index through the surrogate key index value of the record and record the timestamp of the update operation through atomic operations, indicating that the record loses the data freshness in the column storage index due to the update operation of the OLTP table record. The data visibility during query on the column storage index is determined by the timestamp (the record is visible to the query when the query execution timestamp is earlier than the update timestamp, otherwise it is invisible). Queries on the column storage index exclude records where update operations occur.

[0088] S5. Create a Delta index on the OLTP storage side to store the entry address and update type of the records that have insert or update operations after the column storage index is created. The delete mark of the delete operation is directly stored in the record and does not enter the Delta index. The Delta index is used to indicate the addresses of records that are added and modified after the column storage index is created, and is merged with the reduced data accessed by the column storage index based on the freshness vector index to form the current data set.

[0089] S6. When data is updated, an insert type update inserts a record in the OLTP row table, and adds the entry address and update type of the inserted record to the Delta index. A delete type update is marked as deleted in the OLTP row table, and at the same time, the record deletion timestamp is written in the offset position corresponding to the record surrogate key index value in the freshness vector index in an atomic operation mode. When the update type update is an in-situ update, the data is updated directly at the record position, and the record update timestamp is written in the offset position corresponding to the record surrogate key index value in the freshness vector index in an atomic operation mode, and the entry address and update type of the updated record are written to the Delta index.

[0090] S7. When executing a query, first determine whether the query is included in the column store index based on the column subset and selection conditions defined by the column store index. If included, execute the query task in the column store index; otherwise, execute the query task on the OLTP row store table.

[0091] S8. When executing a query on a column store index, scan the freshness vector index. When the current position is empty, the column store index record is a record that has not been updated after the column store index was created and can be accessed. When the current position is not empty, compare the freshness vector index value with the query timestamp. If it is greater than the current query timestamp, it means that the record was updated after the query started and is visible in the current query. Otherwise, it means that the record was updated before the query started and is not visible to the current query.

[0092] S9. After accessing the column storage index data through the freshness vector index, scan the Delta index to access the fresh data subset updated before the query is executed. Access the data inserted and updated before the column storage index is executed in the OLTP row storage table through the entry address recorded in the Delta index, and form a complete query data subset together with the data accessed by the column storage index.

[0093] S10. When a unified row storage query processing engine is used, the column storage index scan based on the freshness vector index and the row storage index scan based on the Delta index jointly provide query data; when the row storage and column storage query processing engines are used separately, the query task spans the column storage index and the OLTP row storage table, accesses column data based on the freshness vector index on the column storage index and executes query subtasks through the column storage query processing engine. When the proportion of null values ​​in the freshness vector index is high and the selectivity of the connection or filtering operation on the column storage index is low, the delayed access to the freshness vector index method can be used to exclude the changed data in the column storage index to optimize the query performance; accesses fresh data through the Delta index on the row storage engine and executes query subtasks through the row storage query processing engine, and finally merges the query subtask results on the two query processing engines.

[0094] S11. After the column storage index is created, the "reduction" query mode is executed. When the update record reaches a certain threshold (such as when it exceeds 20%, which has a greater impact on query performance), the column storage index synchronization mode is started, that is, the update data after the column storage index is created is synchronized based on the current timestamp. The update type update record adopts the in-situ update method in the OLTP row storage table. During synchronization, the modified data is directly updated in the record column at the corresponding position of the column storage index according to the record surrogate key index value, and the corresponding freshness vector index value is set to a null value; the insert type update record adds the surrogate key index value in the OLTP row table according to the conditions for the creation of the column storage index (specified columns and the selection conditions that are met) and inserts it into the column storage index in an appended manner; the deletion record adopts a unified management strategy with the OLTP row table. When the deletion mark reaches a certain threshold, the table is sorted to release the storage space occupied by the logically deleted records. At the same time, the column storage index performs corresponding data sorting to release the storage space occupied by the logically deleted records in the column storage index.

[0095] S12. When the OLTP row storage table adopts the out-of-place update mode, the update operation deletes the original record and inserts the modified new record at the end of the table. The new record copies the surrogate key index value of the original updated record to indicate the position of the pre-update record corresponding to the newly inserted record in the column storage index. At the same time, the entry address of the updated new record and the data operation type update are recorded in the incremental index. During data synchronization, the surrogate key index value of the original record indicates the update position of the record in the column storage index, and the entry address after the update indicates the position of the latest version of the update record in the OLTP row table, so as to write the latest version of the updated data into the specified update position in the column storage index.

[0096] In order to better illustrate the HTAP database construction method of this embodiment, this embodiment introduces Example 2. This embodiment can be used for the implementation of HTAP in-memory database based on column storage index, and can realize the hybrid storage and query processing engine technology of connecting OLTP row storage engine and OLAP column storage engine through index.

[0097] Create an index structure in the OLTP row storage engine to create a column storage index data subset, record the update operation after creating the column storage index, and separate the query record subsets on the row storage engine and the column storage index. The index structure can also support efficient data synchronization updates. Example 2 may include the following steps: S1, such as Figure 2 As shown, a surrogate key index column SK is added to the row storage table to assign a continuously increasing ID to the selected record, which is used as the access address of the column storage index record. SK can be discontinuous and is used to distinguish the original row storage record from the record for creating the column storage index. When data synchronization is performed, the in-situ update record on the row storage table can be updated by accessing the corresponding record position in the column storage index according to the SK value index.

[0098] S2, such as Figure 2As shown in the figure, according to application requirements, a column storage index is created for the specified OLTP row storage table. The column storage index can be the entire data set or a selected or projected subset. For example, A3 is low-potential set string type data. When creating a column storage index, a dictionary table is created for the column in the OLTP row storage engine, and the column storage index stores the corresponding dictionary compression code. Dictionary compression methods can also be used for numerical low-potential set value range columns, such as lo_discount[0.01-0.1] and lo_quantity[1-50]. A numerical dictionary table can also be created on the OLTP row storage side, and the column storage index stores the dictionary compression code. This dictionary table compression method on the OLTP storage side maps the metadata of the data column to a dictionary table, and the column storage index only stores the compression code of simple numerical types, which simplifies the data type of the column storage index and eliminates the data semantics in the column storage index. At the same time, queries on the A3 column, such as A3=AIR, are first executed on the OLTP storage side dictionary table to generate a bitmap index, such as Figure 2 As shown in the figure, the column storage index compresses and stores the A3 column value and maps it to the corresponding position of the bitmap index (such as BitMap[A3[i]]). If it is 1, the record in the A3 column satisfies the original A3=AIR predicate condition. This method allows the OLTP row storage engine to handle the complex data types and processing methods in the predicate operation, and unifies the predicate operation on the column storage index into a mapping access operation based on the dictionary table bitmap index, simplifying the functional design of complex predicate processing on the column storage index side.

[0099] S3. In this embodiment, when creating a column storage index, the S2 dictionary table compression method can be applied to perform dictionary table compression on the WHERE and GROUP-BY attributes of the low potential set and store the compressed code in the column storage index.

[0100] S4. When the user creates a column storage index, a column storage index data copy based on a timestamp (such as SCN=70) is generated, and a freshness vector index of the same length as the column storage index is created. The original OLTP row storage table can directly access the entry address of the freshness vector index and the corresponding record of the column storage index through the surrogate key index.

[0101] S5. Create a Delta index to record records updated after the timestamp of creating the column storage index (insert and update types). The Delta index includes RowID and update type. The former records the entry address of the updated record, and the latter records the update type. The delete type is directly recorded in the delete bitmap of the original row storage table and is not recorded in the Delta index. When the freshness vector index is scanned, the updated and deleted records are excluded. When the Delta index is scanned, the newly added records and updated records are supplemented. The two constitute a complete data set.

[0102] S6, such as Figure 2As shown in the figure, newly added records (such as records with RowID #1017 and #1018) are directly inserted into the original OLTP row storage table. The records that meet the conditions for creating column storage indexes are assigned surrogate key index values, and the RowID and update type of the record are added in the Delta index. When an update operation occurs on the inserted record, such as an update operation occurs on the inserted record #1017 when the timestamp is SCN=110, the row storage engine implements in-situ update and synchronously updates the SCN timestamp of the record, and the RowID entry address in the Delta index remains unchanged. The record with RowID #1004 undergoes an in-situ update operation, and the update value is directly recorded in the A2 attribute. At the same time, the timestamp is written to the position mapped by the surrogate key index value 4 in the freshness vector index in an atomic operation mode, marking that the record has been changed and needs to be excluded from the column storage index access. The RowID and update type of the updated record are written in the Delta index, and the updated record in the original OLTP row storage table is accessed through the Delta index item. The record with RowID #1010 is deleted. The delete bitmap in the table is set to 1 and the timestamp is written to the freshness vector index in an atomic operation, excluding access to the record on the column storage index.

[0103] The surrogate key index is a column added to the OLTP row storage table. When creating a column storage index, records that meet the conditions are continuously assigned values ​​according to the selection clause. The freshness vector index ensures the consistency of write operations through atomic operations. The Delta index is a table structure. The insertion of index items is provided by the row storage engine with transactions, concurrency control, and multi-version access mechanisms.

[0104] S7. When executing a query, determine whether the current query data set is included in the column storage index data set according to the column storage index creation command. If it is included, the query processing is supported by the column storage index, otherwise the query is redirected to the original row storage data table. Column storage indexes are used to accelerate user-defined data sets. Application scenarios include defining column storage data subsets according to available memory size (such as DRAM, NVM non-volatile memory, GPU video memory size), defining column storage data subsets according to data access popularity (such as based on time popularity), defining column storage data subsets according to specified application topics (such as data marts), etc., and jointly provide high-performance OLAP computing capabilities with the customized OLAP computing engine on the column storage index. In order to simplify the use of column storage indexes, the present invention first supports the OLAP query acceleration processing capability when the query data set is a column storage data subset, and when the query data subset is not completely included in the column storage index data set, the collaborative query processing on the row storage engine and the column storage index is realized according to the cost model.

[0105] S8. When executing a query on a column store index, such as Figure 2As shown in the figure, assuming that the SCN of the query on the column storage index is 95, when scanning the freshness vector index, the vector unit storage SCN corresponding to the record #1004 is 97, which is later than the query start time, so the updated record is accessible to the current query. The vector unit storage SCN corresponding to the scanned record #1010 is 90, which is earlier than the query start time, so the record is not accessible to the current query, and the column storage scan excludes a deleted record.

[0106] S9, scan the Delta index. The original SCN of record #1017 is 85. After the query is executed, an update operation occurs when SCN=110. The OLTP row storage table is accessed by the RowID index. The old version record with SCN of 85 is accessed through its own concurrent access control and multi-version mechanism. The record with RowID #1004 is judged to have an SCN of 97, which is later than the query execution time. It has been accessed in the column storage index, so the Delta record does not need to be accessed. The SCN of the record with RowID #1018 is 100, which is later than the query execution SCN and is not visible to the current query. The Delta index stores the entry address of newly added and modified records after the column storage index is created. The visibility of the record in the current query is judged by the SCN of the record itself, and together with the column storage index record access, it provides the data set for the current query.

[0107] S10. When the database uses a unified row storage query processing engine, the scan based on the freshness vector index provides a data subset on the column storage index for on-demand access, and the Delta index scan provides the corresponding delta data subset on the row storage engine, which are merged into the table scan data set for access by the query processing engine. When the column storage index supports a customized OLAP query processing engine, the column storage index and the OLTP row storage Delta data can be regarded as two data shards on the heterogeneous storage engine. The column storage index is accessed based on the freshness vector index and the OLAP query task is executed. When the freshness vector index selectivity is high (the proportion of null values ​​is high, such as more than 90%) and the selectivity of the selection or filtering operation on the column storage index is much lower than the freshness vector index selectivity, the scan and filter operation of the freshness vector index can be delayed to execute after the low selectivity operation to improve query performance, and merged with the query result of the row storage update data subset accessed based on the Delta index into the final query result.

[0108] S11. When the non-null values ​​(updated records) in the freshness vector index reach a certain threshold, the records accessed on the column storage index decrease, the row storage records accessed based on the Delta index increase, and the overall query performance decreases. When the data freshness reaches the preset threshold (such as the proportion of updated records), the column storage index is rebuilt, such as the REINDEXlo_csi command to synchronize the updated data to the column storage index according to the current timestamp and column storage index structure. Figure 3As shown in the figure, the current timestamp SCN=200, the row storage table is accessed through the Delta index, and the update (U) type records with SCN earlier than 200, such as the record with RowID #1004, are located in the column storage index record according to the surrogate key index value 4 for in-situ update and the corresponding cell of the freshness vector index is set to null value synchronously; the records with SCN less than 200 in the update type of insert (I) records in the Delta index, such as the records with RowID #1017 and #1018, are written to the column storage index according to the surrogate key index values ​​14 and 15 according to the columns selected in the column storage index, and the newly added records are synchronized to the column storage index. After processing, the corresponding index items in the Delta index are deleted. The deleted records are not processed for the time being, and the SCN at the time of deletion is still recorded in the freshness vector index. When the number of deleted records in the table reaches the preset threshold, the original row storage table is sorted, the record storage is reconstructed, the deleted record space is released, and the column storage index is rebuilt according to the reconstructed table.

[0109] S12. When the row storage table adopts the out-of-place update mechanism, the update operation is decomposed into a strategy of deleting the original record and inserting the modified record into the table. Figure 5 As shown in the figure, when record #1004 is updated, the record deletion mark is set to 1, and the updated record together with the surrogate key index value 4 is inserted as a new record into the table at record position #1008, and an index item is added to the Delta index, and the update type is still U; the inserted record #1017 is updated at SCN=110, the original record is marked as deleted, the updated record and the surrogate key index value are inserted as a new record #1020, and the original index item is replaced in the Delta index, and the update mark is still I. Under the out-of-place update mechanism, the Delta index item needs to be added (updated) or replaced (update is performed on the insert record) as the current record entry address to access the latest version of the data. When synchronizing data, the record corresponding to the Delta index item is updated according to the surrogate key index value mapping to the corresponding record of the column storage index.

[0110] This embodiment can adopt a timestamp-based static column storage index and dynamic row storage hybrid storage engine design. Only decrement queries based on freshness vector indexes are executed on the column storage index. The transaction processing of record addition, deletion, and modification is executed by the OLTP row storage engine, and incremental queries after the column storage index is created are provided. Delta data management on the column storage index is pushed to the OLTP row storage engine end, simplifying the complexity of the column storage index design.

[0111] In this embodiment, a HTAP query processing engine that supports OLTP row storage and OLAP column storage can be constructed through freshness vector indexes, surrogate key indexes, and Delta indexes. The index is maintained by the OLTP side, and index updates are implemented through transaction processing, concurrency control, and other mechanisms. During query, the column storage index side accesses the index to execute query tasks. The data management function of the column storage index side is simplified through the OLTP side index maintenance and OLAP side index access mechanisms.

[0112] This embodiment can use a lightweight surrogate key index mechanism in the OLTP row storage table to map the physical location of records in the column storage index through logical values, support users to select some rows and some columns to establish column storage indexes as needed, support in-situ updates of OLTP-side update data on the column storage index, flexibly consider data popularity and space capacity, and improve column storage efficiency and data synchronization performance.

[0113] This embodiment improves the efficiency of column storage access for the database through column storage indexes and expands high-performance OLAP analysis and processing capabilities. It accesses the column storage index-side read-only data set and the row storage-side update data set through surrogate key indexes, freshness vector indexes, and Delta indexes to build a complete query data set, and implements HTAP query processing based on different query processing models on the respective data sets.

[0114] like Figure 6 As shown, this embodiment proposes a HTAP database construction device, which may include: A selection unit 101 is used to select a plurality of target column names and a plurality of target row records of interest from a plurality of column names and a plurality of row records stored in an initial row storage table in response to a data selection instruction; A first creation unit 102 is used to create a surrogate key index column in an initial row storage table; The allocating unit 103 is used to allocate an increasing and continuous surrogate key index identifier to each target row record starting from a preset initial surrogate key index identifier in the surrogate key index column to obtain a target row storage table; The second creation unit 104 is used to create column data corresponding to any target column name according to the target column name and each surrogate key index identifier; The third creation unit 105 is used to create a target column storage table according to each target column name and corresponding column data; The construction unit 106 is used to construct a hybrid transaction processing and analytical processing HTAP database including a target row storage table and a target column storage table; wherein the target row storage table and the target column storage table in the HTAP database are used for transaction processing and column data query respectively.

[0115] It should be noted that the processing of the selection unit 101, the first creation unit 102, the allocation unit 103, the second creation unit 104, the third creation unit 105 and the construction unit 106 and the beneficial effects thereof can be referred to respectively. Figure 1 Steps S101 to S106 in the above are not described in detail.

[0116] Optionally, the second creating unit 104 is further configured to: Determine the record value corresponding to the target column name in each row record corresponding to the surrogate key index identifier; Determining that the number of characters of at least N record values ​​is greater than a first preset threshold, and determining that the number of repetitions of record values ​​in all record values ​​exceeds a second preset threshold, grouping all record values ​​to obtain at least one corresponding record value category, and setting a unique corresponding dictionary table code for each at least one record value category; wherein N is an integer greater than 1; For any record value, determine the target dictionary table code corresponding to the record value according to the record value category corresponding to the record value, determine the target surrogate key index identifier corresponding to the record value according to the target row record corresponding to the record value, determine the ascending sorting order of the target surrogate key index identifier among all surrogate key index identifiers, and determine the ascending sorting order as the to-be-sorted order of the target dictionary table code; According to the order of each target dictionary table code to be arranged, the target dictionary table code corresponding to each record value is arranged to obtain an arrangement result as the column data corresponding to the target column name.

[0117] Optionally, the above device further includes: An operation unit, configured to determine the total number of surrogate key index identifiers in the surrogate key index column after building a hybrid transaction processing and analytical processing HTAP database including a target row storage table and a target column storage table, create a freshness vector index column with a total number of cells equal to the total number; and create an operation type index table; In response to a row record operation instruction on a target row storage table, a data operation is performed on at least one of a target row storage table, a target column storage table, a freshness vector index column, and an operation type index table in the HTAP database.

[0118] Optionally, multiple column names include the operation timestamp, and the initial surrogate key index is identified as 1; The above operating unit is also used for: In response to an update instruction for a record value corresponding to the target column name in the first row of records, the record value corresponding to the target column name in the first row of records is updated, and a first timestamp at which the update operation occurs and a first entry address of the first row of records in the target row storage table are determined; The first timestamp is saved in the cell corresponding to the first row of the operation timestamp, and the first entry address and the update operation identifier are stored in the operation type index table in correspondence; Determine a first surrogate key index identifier corresponding to a first row of records, determine a first cell having a cell order equal to the first surrogate key index identifier among multiple cells of the freshness vector index column, and save the first timestamp in the first cell.

[0119] Optionally, the above-mentioned operating unit is further used for: In response to an instruction to add a second row of records in the target row storage table, add a second row of records in the target row storage table, determine a second timestamp of when the adding operation occurs, and store the second timestamp in a second cell corresponding to the operation timestamp in the second row of records; If it is determined that the second row record is the row record of interest, an incremental second surrogate key index identifier is continuously assigned to the second row record in the surrogate key index column, the second entry address of the second row record in the target row storage table is obtained, and the second entry address and the newly added operation identifier are stored in the operation type index table accordingly.

[0120] Optionally, the above-mentioned operating unit is further used for: In response to a delete instruction for the third row record in the target row storage table, modify the identification value in the deletion identification bit corresponding to the third row record from a non-deletion identification value to a deleted identification value, record a third timestamp of the modification operation, and store the third timestamp in a cell corresponding to the operation timestamp in the third row record; If it is determined that the third row of records includes a third surrogate key index identifier, a third cell having a cell order equal to the third surrogate key index identifier is determined from among multiple cells of the freshness vector index column, and the third timestamp is stored in the third cell.

[0121] Optionally, the above device further includes: a query unit; Query unit, used to: After performing data operations on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database in response to the row record operation instruction on the target row storage table, receiving a query instruction on the target column data corresponding to the target column name in the target column storage table, and recording a receiving timestamp of the query instruction; Find each empty value cell in the freshness vector index column, determine the first cell order of each empty value cell in the freshness vector index column, and determine the record value located in each first cell order in the target column data as the record value allowed to be accessed; Find at least one target timestamp greater than the received timestamp among the timestamps currently stored in the freshness vector index column, and determine the second cell order of each target timestamp in the freshness vector index column; For any second cell sequence, determine a fourth surrogate key index identifier of the same size as the second cell sequence, determine a fourth row of records corresponding to the fourth surrogate key identifier index in the target row storage table, and determine a record value corresponding to the target column name in the fourth row of records as a record value that is allowed to be accessed; Obtain each entry address currently stored in the operation type index table, and search for the row record corresponding to each entry address in the target row storage table; For the row record corresponding to any entry address, determine the timestamp corresponding to the operation timestamp in the row record. If the determined timestamp is less than the receiving timestamp, determine the record value corresponding to the target column name in the row record as the record value that allows access; All the access-permitted record values ​​are taken as a whole as the query result corresponding to the query instruction, and the query result is output.

[0122] The HTAP database construction device proposed in this embodiment can select multiple target column names and multiple target row records of interest from multiple column names and multiple row records stored in the initial row storage table in response to a data selection instruction. Create a surrogate key index column in the initial row storage table, and in the surrogate key index column, assign an incremental and continuous surrogate key index identifier to each target row record starting from the preset initial surrogate key index identifier to obtain a target row storage table. For any target column name, create column data corresponding to the target column name according to the target column name and each surrogate key index identifier. Create a target column storage table according to each target column name and the corresponding column data. Create an HTAP database including a target row storage table and a target column storage table; wherein the target row storage table and the target column storage table in the HTAP database are used for transaction processing and column data query, respectively. The HTAP database in this embodiment can improve the efficiency of column data query based on the target column storage table while ensuring the efficiency of transaction processing based on the target row storage table.

[0123] The HTAP database construction device in this embodiment is presented in the form of a functional unit, where the unit refers to an ASIC (Application Specific Integrated Circuit) circuit, a processor and memory that executes one or more software or fixed programs, and / or other devices that can provide the above functions.

[0124] The embodiment of the present invention also provides a computer device having the above Figure 6 The HTAP database construction device shown.

[0125] See also Figure 7 , a schematic diagram of the structure of a computer device provided by an optional embodiment of the present invention, the computer device includes: one or more processors 10, a memory 20, and interfaces for connecting various components, including high-speed interfaces and low-speed interfaces. The various components are connected to each other using different buses for communication, and can be installed on a common motherboard or installed in other ways as needed. The processor can process instructions executed in the computer device, including instructions stored in or on the memory to display graphical information of the GUI on an external input / output device (such as a display device coupled to the interface). In some optional embodiments, if necessary, multiple processors and / or multiple buses can be used together with multiple memories and multiple memories. Similarly, multiple computer devices can be connected, and each device provides part of the necessary operations (for example, as a server array, a group of blade servers, or a multi-processor system). Figure 7 A processor 10 is taken as an example.

[0126] The processor 10 may be a central processing unit, a network processor or a combination thereof. The processor 10 may further include a hardware chip. The hardware chip may be a dedicated integrated circuit, a programmable logic device or a combination thereof. The programmable logic device may be a complex programmable logic device, a field programmable gate array, a general purpose array logic or any combination thereof.

[0127] The memory 20 stores instructions executable by at least one processor 10, so that at least one processor 10 executes the method shown in the above embodiment.

[0128] The memory 20 may include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application required for at least one function. The data storage area may store data created according to the use of the computer device, etc. In addition, the memory 20 may include a high-speed random access memory, and may also include a non-transient memory, such as at least one disk storage device, a flash memory device, or other non-transient solid-state storage devices. In some optional embodiments, the memory 20 may optionally include a memory remotely arranged relative to the processor 10, and these remote memories may be connected to the computer device via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0129] The memory 20 may include a volatile memory, such as a random access memory. The memory may also include a non-volatile memory, such as a flash memory, a hard disk or a solid state drive. The memory 20 may also include a combination of the above-mentioned types of memory.

[0130] The computer device further comprises a communication interface 30 for the computer device to communicate with other devices or a communication network.

[0131] The embodiment of the present invention also provides a computer-readable storage medium. The method according to the embodiment of the present invention can be implemented in hardware, firmware, or can be implemented as a computer code that can be recorded in a storage medium, or can be implemented as a computer code that is originally stored in a remote storage medium or a non-temporary machine-readable storage medium and will be stored in a local storage medium through a network download, so that the method described herein can be stored in such software processing on a storage medium using a general-purpose computer, a dedicated processor, or programmable or dedicated hardware. Among them, the storage medium can be a magnetic disk, an optical disk, a read-only storage memory, a random access memory, a flash memory, a hard disk or a solid-state hard disk, etc.; further, the storage medium can also include a combination of the above types of memories. It can be understood that a computer, a processor, a microprocessor controller, or programmable hardware includes a storage component that can store or receive software or computer code. When the software or computer code is accessed and executed by a computer, a processor, or hardware, the method shown in the above embodiment is implemented.

[0132] 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 make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for constructing an HTAP database, characterized in that: include: In response to the data selection instruction, selecting a plurality of target column names and a plurality of target row records of interest from a plurality of column names and a plurality of row records stored in the initial row storage table; Creating a surrogate key index column in the initial row storage table, and assigning an incremental and continuous surrogate key index identifier to each target row record in the surrogate key index column starting from a preset initial surrogate key index identifier, to obtain a target row storage table; For any of the target column names, creating column data corresponding to the target column name according to the target column name and each of the surrogate key index identifiers; Creating a target column storage table according to each target column name and the corresponding column data; A hybrid transaction processing and analytical processing HTAP database including the target row storage table and the target column storage table is constructed; wherein the target row storage table and the target column storage table in the HTAP database are used for transaction processing and column data query, respectively.

2. The method according to claim 1, characterized in that: The step of creating column data corresponding to the target column name according to the target column name and each of the surrogate key index identifiers includes: Determine the record value corresponding to the target column name in each row record corresponding to the surrogate key index identifier; Determining that the number of characters of at least N of the record values ​​is greater than a first preset threshold, and determining that the number of record value repetitions in all the record values ​​exceeds a second preset threshold, grouping all the record values ​​to obtain at least one corresponding record value category, and setting a unique corresponding dictionary table code for each of the at least one record value categories; wherein N is an integer greater than 1; For any of the record values, determine the target dictionary table code corresponding to the record value according to the record value category corresponding to the record value, determine the target surrogate key index identifier corresponding to the record value according to the target row record corresponding to the record value, determine the ascending arrangement order of the target surrogate key index identifier among all the surrogate key index identifiers, and determine the ascending arrangement order as the to-be-arranged order of the target dictionary table code; According to the order to be arranged of each target dictionary table code, the target dictionary table code corresponding to each record value is arranged to obtain an arrangement result as the column data corresponding to the target column name.

3. The method according to claim 1, characterized in that After constructing the hybrid transaction processing and analytical processing HTAP database including the target row storage table and the target column storage table, the method further includes: Determine the total number of the surrogate key index identifiers in the surrogate key index column, create a freshness vector index column with a total number of cells equal to the total number; and create an operation type index table; In response to a row record operation instruction on the target row storage table, a data operation is performed on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database.

4. The method according to claim 3, characterized in that The multiple column names include an operation timestamp, and the initial surrogate key index identifier is 1; in response to the row record operation instruction on the target row storage table, performing data operation on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database, including: In response to an update instruction for a record value corresponding to the target column name in a first row of records, updating the record value corresponding to the target column name in the first row of records, and determining a first timestamp at which the update operation occurs and a first entry address of the first row of records in the target row storage table; Saving the first timestamp to the cell corresponding to the operation timestamp in the first row of records, and storing the first entry address and the update operation identifier in the operation type index table in correspondence; Determine a first surrogate key index identifier corresponding to the first row of records, determine a first cell whose cell order is equal to the first surrogate key index identifier among multiple cells of the freshness vector index column, and save the first timestamp in the first cell.

5. The method according to claim 4, characterized in that The step of performing data operation on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database in response to the row record operation instruction on the target row storage table further includes: In response to an instruction to add a second row of records in the target row storage table, add the second row of records in the target row storage table, determine a second timestamp of when the adding operation occurs, and store the second timestamp in a second cell corresponding to the operation timestamp in the second row of records; Determine that the second row record is the row record of interest, then continue to assign an incremented second surrogate key index identifier to the second row record in the surrogate key index column, obtain the second entry address of the second row record in the target row storage table, and store the second entry address and the newly added operation identifier in the operation type index table accordingly.

6. The method according to claim 5, characterized in that The step of performing data operation on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database in response to the row record operation instruction on the target row storage table further includes: In response to a delete instruction for the third row record in the target row storage table, modify the identification value in the deletion identification bit corresponding to the third row record from a non-deletion identification value to a deleted identification value, record a third timestamp of the modification operation, and store the third timestamp in the cell corresponding to the operation timestamp in the third row record; If it is determined that the third row of records includes a third surrogate key index identifier, a third cell having a cell order equal to the third surrogate key index identifier is determined from among the multiple cells of the freshness vector index column, and the third timestamp is stored in the third cell.

7. The method according to any one of claims 3 to 6, characterized in that After performing data operation on at least one of the target row storage table, the target column storage table, the freshness vector index column, and the operation type index table in the HTAP database in response to the row record operation instruction on the target row storage table, the method further includes: receiving a query instruction for the target column data corresponding to the target column name in the target column storage table, and recording a receiving timestamp of the query instruction; Find each empty value cell in the freshness vector index column, determine the first cell order of each empty value cell in the freshness vector index column, and determine the record value located in each first cell order in the target column data as the record value allowed to be accessed; Find at least one target timestamp greater than the received timestamp from each timestamp currently stored in the freshness vector index column, and determine a second cell order of each target timestamp in the freshness vector index column; For any of the second cell sequences, determine a fourth surrogate key index identifier of the same size as the second cell sequence, determine a fourth row of records corresponding to the fourth surrogate key identifier index in the target row storage table, and determine a record value corresponding to the target column name in the fourth row of records as an access-permitted record value; Obtain each entry address currently stored in the operation type index table, and search the target row storage table for a row record corresponding to each entry address; For any row record corresponding to the entry address, determine the timestamp corresponding to the operation timestamp in the row record, and if the determined timestamp is less than the receiving timestamp, determine the record value corresponding to the target column name in the row record as the record value that allows access; All the access permission record values ​​are taken as a whole as the query result corresponding to the query instruction, and the query result is output.

8. A HTAP database construction device, characterized in that: include: A selection unit, configured to select, in response to a data selection instruction, a plurality of target column names and a plurality of target row records of interest from a plurality of column names and a plurality of row records stored in an initial row storage table; A first creation unit, configured to create a surrogate key index column in the initial row storage table; an allocating unit, configured to allocate, in the surrogate key index column, an increasing and continuous surrogate key index identifier to each of the target row records starting from a preset initial surrogate key index identifier, to obtain a target row storage table; A second creation unit is used to create, for any of the target column names, column data corresponding to the target column name according to the target column name and each of the surrogate key index identifiers; A third creating unit, configured to create a target column storage table according to each target column name and the corresponding column data; A construction unit is used to construct a hybrid transaction processing and analytical processing HTAP database including the target row storage table and the target column storage table; wherein the target row storage table and the target column storage table in the HTAP database are used for transaction processing and column data query respectively.

9. A computer device, characterized in that: include: A memory and a processor, wherein the memory and the processor are communicatively connected to each other, the memory stores computer instructions, and the processor executes the HTAP database construction method according to any one of claims 1 to 7 by executing the computer instructions.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a computer to execute the HTAP database construction method according to any one of claims 1 to 7.

Images