An HTAP database construction method, device, equipment and medium

Abstract

The application relates to the technical field of data processing, and discloses an HTAP database construction method, device, equipment and medium, which can select multiple target column names and multiple target row records of interest in multiple column names and multiple row records saved in an initial row storage table. An agent key index column is created in the initial row storage table, and an increasing and continuous agent key index identifier is allocated to each target row record from a preset initial agent key index identifier in the agent key index column, so that a target row storage table is obtained. For any target column name, column data corresponding to the target column name is created according to the target column name and each agent key index identifier. A target column storage table is created according to each target column name and corresponding column data. A hybrid transaction processing and analysis processing (HTAP) database including the target row storage table and the target column storage table is created. The database created by the application can improve column data query efficiency based on the target column storage table.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a method, apparatus, device and medium for constructing an HTAP database. Background Technology

[0002] With the development of computer science and technology, database technology is constantly improving.

[0003] Databases can use row storage tables to store data generated by users or enterprises during business production and processing, optimizing the storage efficiency of add, delete, and modify operations at the row record level, supporting transaction processing and concurrent access control, and ensuring that users can access the freshest data.

[0004] However, databases that use row-based storage tables to store data have lower efficiency in querying column data. Summary of the Invention

[0005] This invention provides an HTAP database construction method, apparatus, device, and medium to address the shortcomings of related technologies that use row storage tables to store data, resulting in low column data query efficiency, and to improve column data query efficiency.

[0006] In a first aspect, the present invention provides an HTAP database construction method, comprising:

[0007] In response to a data selection command, 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;

[0008] Create a proxy key index column in the initial row storage table, and in the proxy key index column, assign incremental and consecutive proxy key index identifiers to each target row record starting from a preset initial proxy key index identifier, to obtain the target row storage table;

[0009] For any of the target column names, create column data corresponding to the target column name based on the target column name and each of the proxy key index identifiers;

[0010] Create a target column storage table based on each target column name and the corresponding column data;

[0011] 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 querying, respectively.

[0012] Optionally, creating column data corresponding to the target column name based on the target column name and each surrogate key index identifier includes:

[0013] In each row record corresponding to the surrogate key index identifier, determine the record value corresponding to the target column name;

[0014] If it is determined that the number of characters in at least N record values ​​is greater than a first preset threshold, and it is determined that the number of times a record value is repeated in all the record values ​​exceeds a second preset threshold, then all the record values ​​are grouped to obtain at least one corresponding record value category, and a unique dictionary table code is set for each of the at least one record value category; where N is an integer greater than 1.

[0015] For any of the record values, the target dictionary table code corresponding to the record value is determined according to the record value category corresponding to the record value, the target surrogate key index identifier corresponding to the record value is determined according to the target row record corresponding to the record value, the ascending order of the target surrogate key index identifier among all the surrogate key index identifiers is determined, and the ascending order is determined as the order to be arranged for the target dictionary table code;

[0016] According to the order in which each target dictionary table code is to be arranged, the target dictionary table codes corresponding to each record value are arranged to obtain the arrangement result, which is used as the column data corresponding to the target column name.

[0017] Optionally, after constructing the hybrid transaction and analytical processing HTAP database including the target row store table and the target column store table, the method further includes:

[0018] Determine the total number of proxy key index identifiers in the proxy 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;

[0019] In response to a row record operation instruction on the target row storage table, data operations are 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.

[0020] Optionally, the plurality of column names include an operation timestamp, and the initial surrogate key index identifier is 1; the step of 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 a row record operation instruction on the target row storage table includes:

[0021] In response to an update instruction for the record value corresponding to the target column name in the first row record, the record value corresponding to the target column name in the first row record is updated, and the first timestamp of the update operation and the first entry address of the first row record in the target row storage table are determined.

[0022] The first timestamp is saved to the cell corresponding to the operation timestamp in the first row record, and the first entry address and update operation identifier are stored in the operation type index table.

[0023] Determine the first surrogate key index identifier corresponding to the first row record. Among the multiple cells in the freshness vector index column, determine the first cell whose cell order is equal to the size of the first surrogate key index identifier, and save the first timestamp to the first cell.

[0024] Optionally, the step of 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 a row record operation instruction on the target row storage table further includes:

[0025] In response to the instruction to add a second row record in the target row storage table, the second row record is added in the target row storage table, a second timestamp of the addition operation is determined, and the second timestamp is stored in the second cell corresponding to the operation timestamp in the second row record;

[0026] If the second row is determined to be the row of interest, then an incrementing second surrogate key index identifier is assigned to the second row in the surrogate key index column, the second entry address of the second row 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.

[0027] Optionally, the step of 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 a row record operation instruction on the target row storage table further includes:

[0028] In response to a deletion instruction on the third row record in the target row storage table, the identifier value in the deletion identifier bit corresponding to the third row record is modified from a non-deletion identifier value to a deleted identifier value, a third timestamp of the modification operation is recorded, and the third timestamp is stored in the cell corresponding to the operation timestamp in the third row record;

[0029] If the third row of records is determined to include a third surrogate key index identifier, then among the multiple cells in the freshness vector index column, the third cell whose cell order is equal to the size of the third surrogate key index identifier is determined, and the third timestamp is stored in the third cell.

[0030] Optionally, 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 a row record operation instruction on the target row storage table, the method further includes:

[0031] Receive a query instruction for the target column data corresponding to the target column name in the target column storage table, and record the timestamp of receiving the query instruction;

[0032] Find each empty cell in the freshness vector index column, determine the first cell order of each empty cell in the freshness vector index column, and determine the record value in the target column data that is located in each first cell order as the record value that can be accessed;

[0033] Among the timestamps currently stored in the freshness vector index column, at least one target timestamp greater than the received timestamp is found, and the second cell order of each target timestamp in the freshness vector index column is determined.

[0034] For any second cell order, determine a fourth proxy key index identifier that is equal in size to the second cell order, determine the fourth row record corresponding to the fourth proxy key identifier index in the target row storage table, and determine the record value corresponding to the target column name in the fourth row record as the allowed access record value;

[0035] 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;

[0036] For any row record corresponding to the entry address, determine the timestamp corresponding to the operation timestamp in the row record. If the determined timestamp is less than the receiving timestamp, then determine the record value corresponding to the target column name in the row record as the allowed access record value.

[0037] All the allowed access record values ​​are treated as a whole as the query result corresponding to the query command, and the query result is output.

[0038] In a second aspect, the present invention provides an HTAP database construction apparatus, comprising:

[0039] The selection unit is used to 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.

[0040] The first creation unit is used to create a surrogate key index column in the initial row storage table;

[0041] The allocation unit is used to allocate incremental and consecutive proxy key index identifiers to each target row record in the proxy key index column, starting from a preset initial proxy key index identifier, to obtain a target row storage table;

[0042] The second creation unit is used to create column data corresponding to any target column name based on the target column name and each proxy key index identifier;

[0043] The third creation unit is used to create a target column storage table based on each target column name and the corresponding column data;

[0044] 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 querying, respectively.

[0045] Thirdly, the present invention provides a computer device, including: 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 computer instructions to perform the HTAP database construction method of the first aspect or any corresponding embodiment described above.

[0046] Fourthly, the present invention provides a computer-readable storage medium storing computer instructions for causing a computer to execute the HTAP database construction method of the first aspect or any corresponding embodiment thereof.

[0047] The HTAP database construction method, apparatus, device, and medium provided by this invention can, in response to a data selection instruction, select multiple target column names and multiple target row records of interest from multiple column names and multiple row records stored in an initial row storage table. A surrogate key index column is created in the initial row storage table, and in the surrogate key index column, incrementally increasing and consecutively assigning surrogate key index identifiers to each target row record, starting from a preset initial surrogate key index identifier, to obtain a target row storage table. For any target column name, column data corresponding to the target column name is created based on the target column name and each surrogate key index identifier. A target column storage table is created based on each target column name and its corresponding column data. A database including the target row storage table and the target column storage table is created; wherein, the target row storage table and the target column storage table in the database are used for transaction processing and column data querying, respectively. The hybrid transaction processing and analytical processing HTAP database created by this invention can improve column data query efficiency based on the target column storage table while ensuring transaction processing efficiency based on the target row storage table. Attached Figure Description

[0048] To more clearly illustrate the technical solutions in this invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this invention. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0049] Figure 1 A flowchart illustrating an HTAP database construction method provided in an embodiment of the present invention;

[0050] Figure 2 This is a schematic diagram illustrating the HTAP database construction and update process provided in an embodiment of the present invention;

[0051] Figure 3 This is a schematic diagram of an HTAP database update process provided in an embodiment of the present invention;

[0052] Figure 4 This is a schematic diagram of a hybrid storage model structure for HTAP queries provided in an embodiment of the present invention;

[0053] Figure 5 This is a schematic diagram of another HTAP database update process provided in an embodiment of the present invention;

[0054] Figure 6 This is a schematic diagram of the structure of an HTAP database construction device provided in an embodiment of the present invention;

[0055] Figure 7 This is a schematic diagram of the structure of a computer device provided in an embodiment of the present invention. Detailed Implementation

[0056] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of this invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without creative effort are within the scope of protection of this invention.

[0057] The following is combined Figures 1-5 The present invention describes the HTAP database construction method.

[0058] like Figure 1 As shown, this embodiment proposes a first method for constructing an HTAP database, which may include the following steps:

[0059] S101. In response to the data selection instruction, select multiple target column names and multiple target row records of interest from the multiple column names and multiple row records stored in the initial row storage table.

[0060] It should be noted that an initial row store table can include a header and multiple row records. The header can define multiple column names, and each row record can include the values ​​corresponding to all column names. Each row record can be a complete data entry. For example, the header of the initial row store table can include column names such as A3, A2, A1, Deleted, and SCN. The Deleted column indicates whether the row record has been deleted, and SCN is the timestamp of the most recent operation on the row record. The initial row store table can include multiple row records, and the value corresponding to column A3 can include AIR, RAIL, SHIP, and TRUCK. In related technologies, databases using row store tables to store data experience low query efficiency when faced with queries involving only a few columns, such as querying only one or two columns of data in the row store table, resulting in queries involving a large amount of data in the row store table.

[0061] Specifically, in this embodiment, users or technicians can select multiple column names and multiple row records of interest in the initial row storage table, and create corresponding column storage tables based on these selected column names and row records. This embodiment can construct a database that includes column storage tables and row storage tables. When facing column queries, data can be queried directly in the column storage tables, improving data query efficiency.

[0062] The data selection command can be entered by a user or technician to select the column names and row records of interest. The data selection command may include identifiers for the column names and row records of interest. In this embodiment, the corresponding column names and row records can be selected based on the column name and row record identifiers carried in the data selection command.

[0063] Specifically, the target column name and target row record can be the column name and row record that the user or technical personnel are interested in, respectively. Understandably, the target column name can be a column name that is frequently used and queried in practical applications.

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

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

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

[0067] S103. In the surrogate key index column, starting from the preset initial surrogate key index identifier, assign incremental and consecutive surrogate key index identifiers to each target row record to obtain the target row storage table.

[0068] Specifically, in this embodiment, in the surrogate key index column, the multiple target row records can be assigned consecutive and incremental surrogate key index identifiers according to their order of arrangement in the row storage table.

[0069] like Figure 2 As shown, in this embodiment, consecutive and incremental surrogate key index identifiers 1, 2, 3...13 can be assigned to the selected target row records in the SK column. Row records without assigned surrogate key index identifiers are those that are not selected.

[0070] Of course, this embodiment may also allocate surrogate key index identifiers in a manner that does not follow the order in which the multiple target row records are arranged in the row storage table. For example, in this embodiment, incremental and consecutive surrogate key index identifiers may be randomly assigned to the multiple target row records in the surrogate key index column.

[0071] S104. For any target column name, create the column data corresponding to the target column name based on the target column name and each surrogate key index identifier.

[0072] Specifically, in this embodiment, for any target column name, the record value corresponding to the target column name can be determined in the row record corresponding to each surrogate key index identifier in ascending order, and these record values ​​can be converted into column data corresponding to the target column name according to the order of these record values.

[0073] It should be noted that this embodiment does not require sorting of row and column records, thus avoiding excessive sorting costs. Even the surrogate key index value in the row record only needs to ensure that the connection is increasing, 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 surrogate key value. That is, the surrogate key in the row storage can be logically ordered but physically unordered, and the array position in the column storage is naturally ordered according to the surrogate key value mapping.

[0074] Optionally, step S104 may include:

[0075] In each row record corresponding to the surrogate key index identifier, determine the record value corresponding to the target column name;

[0076] If it is determined that the number of characters in at least N record values ​​is greater than a first preset threshold, and the number of times a record value is repeated in all record values ​​exceeds a second preset threshold, then all record values ​​are grouped to obtain at least one corresponding record value category, and a unique dictionary table code is set for each at least one record value category; where N is an integer greater than 1.

[0077] For any record value, determine the target dictionary table code corresponding to the record value according to the record value category, 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 order of the target surrogate key index identifier among all surrogate key index identifiers, and determine the ascending order as the order to be arranged for the target dictionary table code;

[0078] Based on the order in which each target dictionary table code is to be arranged, the target dictionary table codes corresponding to each record value are arranged, and the arrangement result is used as the column data corresponding to the target column name.

[0079] The first preset threshold and the second preset threshold can be set by technicians according to the actual situation, and this embodiment does not limit them.

[0080] Understandably, in this embodiment, if a column of data in the target row storage table contains a large number of records with many characters and many duplicate records, the records in that column can be grouped into multiple categories. A corresponding dictionary table code, such as 1, 2, 3, and 4, can be assigned to each category. Subsequently, when creating the corresponding column data, this embodiment can use the dictionary table code to replace the corresponding record value for storage, reducing data storage pressure.

[0081] like Figure 2As shown, in the target row storage table, A3 is a target column name. Taking target column name A3 as an example, this embodiment can group the record values ​​corresponding to target column name A3 to obtain the corresponding record value categories AIR, RAIL, SHIP, and TRUCK. Surrogate key index identifiers 1, 2, 3, and 4 are assigned to these record value categories respectively. Then, the row record values ​​are mapped to the surrogate key index values ​​in the grouped records and used as the column data corresponding to target column name A3. See [link to documentation]. Figure 2 The right side of the middle section contains the column data corresponding to column name A3.

[0082] S105. Create a target column storage table based on each target column name and its corresponding column data.

[0083] Specifically, in this embodiment, each target column name and its corresponding column data can be combined, and the combined result can be used as a target column storage table. (See also...) Figure 2 The right side of the text contains the column data corresponding to column names A1, A2, and A3.

[0084] Understandably, the target column storage table includes each target column name and its corresponding column data.

[0085] S106. Create a Hybrid Transactional / Analytical Processing (HTAP) database that includes a target row store table and a target column store table; wherein, the target row store table and the target column store table in the HTAP database are used for transaction processing and column data querying, respectively.

[0086] 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 can be created.

[0087] Optionally, the HTAP database can be a database used to store in-memory data or a database used to store other types of data. This embodiment does not limit the database type.

[0088] Specifically, in this embodiment, the target row storage table can be an Online Transaction Processing (OLTP) row storage table, and the target column storage table can be an Online Analytical Processing (OLAP) column storage table. Therefore, the database constructed in this embodiment can be an in-memory database that implements Hybrid Transactional / Analytical Processing (HTAP).

[0089] It should be noted that this embodiment can perform corresponding transaction processing based on the target row storage table in the HTAP database when a transaction processing instruction is received. In this embodiment, when a column data query instruction corresponding to a target column name is received, the corresponding column data query is performed based on the target column storage table in the HTAP database.

[0090] The HTAP database construction method proposed in this embodiment can, in response to a data selection command, 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. A surrogate key index column is created in the initial row storage table, and in the surrogate key index column, incrementally increasing and consecutively assigning surrogate key index identifiers to each target row record, starting from a preset initial surrogate key index identifier, to obtain the target row storage table. For any target column name, column data corresponding to the target column name is created based on the target column name and each surrogate key index identifier. A target column storage table is created based on each target column name and its corresponding column data. An HTAP database including the target row storage table and the target column storage table is created; wherein, the target row storage table and the target column storage table in the HTAP database are used for transaction processing and column data querying, respectively. The HTAP database of this embodiment can improve column data query efficiency based on the target column storage table while ensuring transaction processing efficiency based on the target row storage table.

[0091] It should be noted that, in order to ensure the transaction processing and data query efficiency of HTAP in-memory databases, it is necessary to simultaneously guarantee high performance of analytical queries and data freshness within a unified database system. Typically, a dual storage engine design is adopted, with the OLTP engine responsible for transaction processing and the OLAP engine responsible for analytical processing. The technical challenge lies in how to promptly propagate the updated data from the OLTP engine's transaction processing to the OLAP engine, ensuring the same data freshness and transaction consistency as the OLTP engine while maintaining the computational efficiency of the OLAP engine.

[0092] based on Figure 1 This embodiment proposes a second HTAP database construction method, which, after step S106, may further include:

[0093] Determine the total number of surrogate key index identifiers in the surrogate key index column, create a freshness vector index column where the total number of cells equals the total number of cells; and create an operation type index table.

[0094] In response to a row record operation command on a target row storage table, perform data operations on at least one of the target row storage table, target column storage table, freshness vector index column, and operation type index table in the HTAP database.

[0095] Specifically, the freshness vector index column can be found in [link to relevant documentation]. Figure 2The column data under the Freshness Vector Index (FVI) is used to store the timestamps of operations performed on the record values ​​in the column data corresponding to the target column name. See the operation type index table for details. Figure 2 The Delta Index in the table stores the entry address (RowID) and operation type (Type) of the row record. The data such as #1001 and #1002 under RowID on the right side of the target row storage table are the entry addresses of the corresponding row records. Figure 2 The meaning of "in-place update" is the in-place update mode.

[0096] Optionally, the above-mentioned data operation on at least one of the target row storage table, target column storage table, freshness vector index column, and operation type index table in the HTAP database in response to a row record operation instruction on the target row storage table may include:

[0097] In response to an update instruction for the record value corresponding to the target column name in the first row record, update the record value corresponding to the target column name in the first row record, and determine the first timestamp of the update operation and the first entry address of the first row record in the target row storage table;

[0098] Save the first timestamp to the cell corresponding to the first row of records, and store the first entry address and update operation identifier in the operation type index table.

[0099] Determine the first surrogate key index identifier corresponding to the first row record. Among the multiple cells in the freshness vector index column, identify the first cell whose cell order is equal to the size of the first surrogate key index identifier, and save the first timestamp to the first cell.

[0100] like Figure 2 As shown, when an update instruction is received for the record value under target column name A2 in the first row of the record with SK=4, the row record operation type is determined to be an update. In this embodiment, according to the update instruction, the record value corresponding to target column name A2 in the first row of the record is updated from 105 to 110. The first timestamp 97 of the update operation and the first entry address #1004 of the first row of the record are recorded. At this time, in this embodiment, 97 can be stored in the cell corresponding to the timestamp column in the first row of the record, and the first entry address #1004 and the update operation identifier U can be stored in the operation type index table. The first surrogate key index identifier 4 corresponding to the first row of the record is determined, and the fourth cell is determined among the multiple cells of the freshness vector index column. The timestamp 97 is stored in the fourth cell of the freshness vector index column, and the record value 105 stored in the target column storage table is not updated for the time being.

[0101] Optionally, the above-mentioned response to a row record operation instruction on a target row storage table, performing data operations on at least one of the target row storage table, target column storage table, freshness vector index column, and operation type index table in the HTAP database, further includes:

[0102] In response to the instruction to add a second record to the target row storage table, add a second record to the target row storage table, determine the second timestamp of the operation, and store the second timestamp in the second cell corresponding to the operation timestamp in the second row record;

[0103] If the second row is determined to be the row of interest, then an incrementing second surrogate key index identifier is assigned to the second row in the surrogate key index column. The second entry address of the second row in the target row storage table is obtained, and the second entry address and the new operation identifier are stored in the operation type index table accordingly.

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

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

[0106] Optionally, when an update operation occurs on a newly inserted row record in the target row storage, such as when an update operation occurs on record #1017 inserted at timestamp SCN=110, the row storage engine performs 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.

[0107] Optionally, the above-mentioned response to a row record operation instruction on a target row storage table, performing data operations on at least one of the target row storage table, target column storage table, freshness vector index column, and operation type index table in the HTAP database, further includes:

[0108] In response to a deletion instruction on the third row record in the target row storage table, the identifier value in the deletion identifier bit corresponding to the third row record is changed from a non-deletion identifier value to a deleted identifier value, the third timestamp of the modification operation is recorded, and the third timestamp is stored in the cell corresponding to the operation timestamp in the third row record;

[0109] If the third row of records is determined to include a third surrogate key index identifier, then among the multiple cells in the freshness vector index column, the third cell whose cell order is equal to the size of the third surrogate key index identifier is identified, and the third timestamp is stored in the third cell.

[0110] like Figure 2 As shown, when an instruction is received to delete the third row record with SK=10 in the target row storage table, this embodiment can modify the identifier value in the deletion identifier bit corresponding to the third row record from the non-deletion identifier value 0 to the deleted identifier value 1, and record the third timestamp 90 of the modification operation, storing the third timestamp 90 in the cell corresponding to the operation timestamp of the third row record. When it is determined that the third row record includes the third surrogate key index identifier 10, the third row record is determined as 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.

[0111] Optionally, after performing data operations on at least one of the target row storage table, target column storage table, freshness vector index column, and operation type index table in the HTAP database in response to a row record operation instruction on the target row storage table, the above method may further include:

[0112] Receive query instructions for the target column data corresponding to the target column name in the target column storage table, and record the timestamp of the received query instructions;

[0113] Find each empty cell in the freshness vector index column, determine the first cell order of each empty cell in the freshness vector index column, and determine the record values ​​in the target column data that are in the first cell order as allowed access record values;

[0114] Among the timestamps currently stored in the freshness vector index column, find at least one target timestamp that is greater than the received timestamp, and determine the second cell order of each target timestamp in the freshness vector index column.

[0115] For any second cell order, determine the fourth surrogate key index identifier with the same size as the second cell order, determine the fourth row record corresponding to the fourth surrogate key identifier index in the target row storage table, and determine the record value corresponding to the target column name in the fourth row record as the allowed access record value;

[0116] Retrieve 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;

[0117] For any row record corresponding to an entry address, determine the timestamp corresponding to the operation timestamp in the row record. If the determined timestamp is less than the receiving timestamp, then determine the record value corresponding to the target column name in the row record as the allowed access record value.

[0118] All allowed access record values ​​are treated as a whole as the query result corresponding to the query command, and the query result is output.

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

[0120] Next, this embodiment can identify cells in the freshness vector index column that store non-empty values, determine the target timestamp greater than the received timestamp from the timestamps recorded in these cells, and determine the second cell order corresponding to each cell recording the target timestamp. Then, this embodiment can determine the fourth surrogate key index identifier with the same size as the second cell order, determine the fourth row of records, and determine the record value corresponding to column A3 in the fourth row of records as the allowed access record value. For example, assuming the received 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 of records as the row record with RowID #1004, and determine the latest record value corresponding to column A3 in the fourth row of records as the allowed access record value.

[0121] Subsequently, 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 entry addresses #1017, #1004, and #1018 in the target row storage table. The record value corresponding to the SCN is determined in the row records corresponding to entry addresses #1017, #1004, and #1018 respectively. The SCN in the row records with RowIDs #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. An update operation occurred when SCN=110 after the query execution. The OLTP row storage table is accessed by the RowID index, and the old version record with SCN 85 is accessed through its own concurrency access control and multi-version mechanism and used as the allowed access record value.

[0122] Specifically, in this embodiment, all the determined allowed access record values ​​can be used as the column data query result corresponding to the target column name A3 and returned to the querying user.

[0123] 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 whole as the column data query result corresponding to the target column name A3, and returned to the query user.

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

[0125] 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, target column storage table, freshness vector index column, and 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:

[0126] When the number of non-null values ​​in the freshness vector index column reaches a preset threshold, the current timestamp is determined.

[0127] For any entry address stored in the operation type index table, search for the row record corresponding to the entry address in the target row storage table. When the operation identifier corresponding to the entry address is determined to be an update operation identifier, determine the cells to be updated in each column of 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 the entry address of each target column name, and store the corresponding column value of the row record into the cells to be updated in the corresponding column data. Set the record value corresponding to the operation timestamp in the row record to null. In the multiple cells of the freshness index column, determine the cell whose cell order is equal to the size of the surrogate key index identifier, and set the stored content in the determined cell to null. When the operation identifier corresponding to the entry address is determined to be 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 null.

[0128] Delete all entry addresses and operation identifiers stored in the operation type index table.

[0129] The preset threshold can be set by technicians according to the actual situation, and this embodiment does not limit it.

[0130] 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 a preset threshold, it can be determined that there will be a lot of data that needs to be queried from the target row storage table during data query, which will affect the efficiency of column data query and cause a significant reduction in the data query performance of the target column storage table. At this time, the target column storage table can be updated according to the operation type index table and the updated data in the target row storage table.

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

[0132] like Figure 2As shown, in this embodiment, for the corresponding stored entry address #1017 and new operation identifier I, and entry address #1018 and new operation identifier I in the operation type index table, the row records corresponding to entry addresses #1017 and #1018 can be determined in the target row storage table. Based on the row records corresponding to entry addresses #1017 and #1018, the corresponding data is inserted into the target column storage table, such as... Figure 3 As shown, the target column storage table A1, A2, and A3 have all had the 14th and 15th data entries added.

[0133] Specifically, in this embodiment, after updating the column data in the target column storage table based on the stored data in the operation type index table, the stored data in the operation type index table can be deleted, such as... Figure 3 The operation type index table Delta Index is shown.

[0134] It should be noted that this embodiment can temporarily retain row records marked for deletion in the target row storage table, or it can retain the deletion timestamp of the corresponding record in the freshness vector index, for example... Figure 3 90 under FVI in the middle.

[0135] Optionally, after performing data operations on at least one of the target row storage table, target column storage table, freshness vector index column, and operation type index table in the HTAP database in response to a row record operation instruction on the target row storage table, the method further includes:

[0136] In the target row store table, monitor the target number of row records marked for deletion and including surrogate key index identifiers. When it is determined that the target number has reached the deletion threshold, reconstruct a new target row store table and a new target column store table based on the target row store table.

[0137] Specifically, in this embodiment, when the number of row records deleted reaches a threshold, the target row storage table and the target column storage table can be reconstructed to release the storage space occupied by the deleted row records.

[0138] The HTAP database construction method proposed in this embodiment can provide an efficient and flexible OLAP column store index acceleration engine for OLTP databases. The freshness vector index and Delta index separate the different freshness of data in the query and map the query to the fresh dataset. Custom column store index and efficient update data merging are realized through surrogate key index. The design of column store engine is simplified by separating metadata and value data in OLAP dataset, thereby improving storage and computing efficiency.

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

[0140] The column-store indexing mechanism used in this embodiment supports user-defined subsets of row and column data on the original row-store table. It can be applied to OLAP acceleration technologies in scenarios with limited memory capacity, based on data popularity, and oriented towards GPU-NVM-DRAM heterogeneous storage. Dictionary data compression technology can be used to separate column-store index metadata from numerical storage, enabling numerical column-store indexes and simplifying the implementation of column-store indexing technology. An index access mechanism between read-only column-store data and updatable row-store data is established through the indexing mechanism created and maintained on the OLTP side and used on the OLAP side, stripping the column-store index of Delta data management functions and simplifying the implementation of column-store indexes. A surrogate key index is added to the OLTP row-store engine to locate the position of records in the column-store index; a freshness vector index is added to indicate column records in the column-store index that have lost freshness due to updates by the OLTP engine; and a Delta index is added to indicate the entry address for update and insert records after the column-store index is created, accelerating Delta data access. These three indexes are implemented on the OLTP storage side using basic data structures such as data columns, vectors, and tables, simplifying index design and maintenance.

[0141] During data synchronization, efficient index access and updates from the OLTP row storage engine to the column storage engine are achieved based on the surrogate key index. This implementation can be applied to in-memory data warehouse applications that support HTAP. It can adopt a hybrid row-column storage mode, where smaller dimension tables (primary key tables) are stored in the OLTP row storage engine, supporting common operations such as adding, deleting, and modifying data. Larger fact tables (foreign key tables) can have column storage indexes created to accelerate OLAP analytical processing performance. The query tree spans both the row storage engine and the column storage index engine. The dimension tables on the row storage engine are used to generate hash tables, join vectors, or Bloom filters for joins during query execution. The fact table column storage index provides multidimensional computation capabilities based on a push model, enabling HTAP query processing on both the hybrid storage and query processing engines.

[0142] To better illustrate the HTAP database construction method proposed in this embodiment, the following Example 1 is provided for explanation.

[0143] In Example 1, the initial row-store table is an OLTP table, the target column-store table is an OLAP table, and the database is an HTAP in-memory database. Example 1 can be used for the implementation of HTAP in-memory database technology with column-store indexes. It can be implemented through a hybrid storage and query processing engine technology that uses indexes to join OLTP row-store engines and OLAP column-store engines. This embodiment can implement HTAP query processing in-memory databases, such as... Figure 4 As shown.

[0144] exist Figure 4 In this embodiment, the database front-end can receive Structured Query Language (SQL) queries. 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 the target row storage table related to LINEORDER (line order) stored in DRAM or NVM, and the column storage index model can be the target column storage table related to LINEORDER (line order) stored in DRAM, NVM, or GPU.

[0145] In the OLTP row storage engine, an index structure is created to create a subset of data for the columnstore index. This structure records update operations after the columnstore index is created and separates the query's subset of records across the row storage engine and the columnstore index. The index structure also supports efficient data synchronization updates. For example... Figure 4 As shown, this embodiment can synchronize data between the row storage model and the column storage index model based on data updates to the row storage model.

[0146] S1. Add a surrogate key index column to the original OLTP table. This allows you to create custom columnstore indexes in the original OLTP rowstore table through selection and projection operations. The columnstore index record is used to identify the offset address of the record in the columnstore index by assigning consecutive surrogate key index values ​​of 1, 2, 3... This allows you to access the columnstore index record and perform data update operations on the OLTP rowstore record by the surrogate key index address when updating data of the OLTP rowstore record.

[0147] S2. Create columnstore indexes for OLAP data columns in OLTP rowstore tables. This mainly includes attributes used for filtering and grouping at the dimension level or dimension attributes, and attribute sets used for dimension joins and measure calculations in the fact data. When the selection and grouping attributes on the dimension table are low-potential attributes (i.e., the number of different data members in the attribute column is small), create corresponding dictionary tables on the OLTP storage side. The corresponding columns in the columnstore index only store the corresponding dictionary table codes, realizing the numerical storage of columnstore index data. Selection operations on the columnstore index are first converted into selection operations on the fact table on the OLTP storage side, and the selection results are mapped to the dictionary table bitmap index. Then, direct predicate processing on compressed data is achieved through the mapping of columnstore index dictionary table code values ​​to dictionary table bitmap index. Grouping operations on the columnstore are directly executed on the dictionary codes. By using dictionary table compression and direct processing on compressed data, the data type stored in the OLAP columnstore index is simplified to numeric, reducing the complexity of storage design and improving query processing performance and efficiency.

[0148] S3. When creating columnstore indexes for OLTP tables to accelerate OLAP queries, columnstore indexes are created based on available memory space, original table size, OLAP-accelerated data subsets, and corresponding column compression widths. The WHERE clause selects the subset of data in the original OLTP table used for OLAP computation acceleration, and assigns a unique, incrementing surrogate key index value designed in S1 to the selected subset records as the record address of the columnstore index, used for index update operations during update type updates. The SELECT clause selects the data columns in the original OLTP table used for OLAP computation acceleration. The attribute columns in the WHERE and GROUP-BY clauses of the query are compressed using the S2 method. The dictionary table is stored on the OLTP storage side, and the compressed encoded columns are stored in the columnstore index. This ensures that the columnstore index only stores three types of numeric data: select, grouped compressed encoded, foreign keys, and measures, eliminating data logical semantics, simplifying the columnstore index data type, and improving computational efficiency on numeric data.

[0149] S4. Create a columnstore index for the OLTP row store table based on a specific timestamp. The columnstore index is a compressed subset of the numerical data in the OLTP table, accelerating OLAP computation performance on user-defined data subsets. Create a freshness vector index of the same length as the columnstore index. When an update or delete operation occurs on a record in the original OLTP table, the corresponding cell in the freshness vector index is accessed through the surrogate key index value of the record, and the timestamp of the update operation is recorded atomically. This indicates that the record has lost its data freshness in the columnstore index due to the update operation on the OLTP table record. The timestamp determines the data visibility when querying on the columnstore index (the record is visible to the query if the query execution timestamp is earlier than the update timestamp; otherwise, it is not visible). Queries on the columnstore index exclude records that have undergone update operations.

[0150] S5. Create a Delta index on the OLTP storage side. This index stores the entry address and update type of records that have undergone insert or update operations after the columnstore index was created. The deletion flag of delete operations is stored directly in the record and is not included in the Delta index. The Delta index is used to indicate the address of records added and modified after the columnstore index was created. It is merged with the decremented data accessed by the columnstore index based on the freshness vector index into the current data set.

[0151] S6. When updating data, insert type updates insert a record into the OLTP row table and add the entry address and update type of the inserted record to the Delta index. delete type updates mark the OLTP row table as deleted and simultaneously write the deletion timestamp of the record to the offset position corresponding to the surrogate key index value of the record in the freshness vector index in an atomic manner. When update type updates are in-place updates, the data is updated directly at the record position and the update timestamp of the record is written to the offset position corresponding to the surrogate key index value of the record in the freshness vector index in an atomic manner. At the same time, the entry address and update type of the updated record are written to the Delta index.

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

[0153] S8. When executing a query on a columnstore index, scan the freshness vector index. If the current position is empty, the columnstore index record is a record that has not been updated since the columnstore index was created and can be accessed. If 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.

[0154] S9. After reducing access to columnstore index data through the freshness vector index, scan the Delta index to access the fresh data subset updated before the query was executed. Access the data inserted and updated before the columnstore index was executed in the OLTP row store table through the entry address recorded in the Delta index. This data, together with the data accessed by the columnstore index, constitutes a complete subset of the query data.

[0155] S10. When using a unified row-store query processing engine, column-store index scans based on the freshness vector index and row-store index scans based on the Delta index jointly provide query data. When using row-store and column-store query processing engines separately, the query task spans the column-store index and the OLTP row-store table. On the column-store index, column data is accessed based on the freshness vector index and query subtasks are executed through the column-store query processing engine. When the proportion of null values ​​in the freshness vector index is high and the selectivity of join or filtering operations on the column-store index is low, the delayed access to the freshness vector index method can be used to exclude data that has changed in the column-store index, thereby optimizing query performance. On the row-store engine, fresh data is accessed through the Delta index and query subtasks are executed through the row-store query processing engine. Finally, the results of the query subtasks from the two query processing engines are merged.

[0156] S11. After creation, the columnstore index executes a "reduction" query mode. When the number of updated records reaches a certain threshold (e.g., exceeding 20% ​​which significantly impacts query performance), the columnstore index synchronization mode is activated. This means that the updated data after the columnstore index is created is synchronized based on the current timestamp. Update type records in the OLTP rowstore table are updated in-situ. During synchronization, the modified data is directly updated in the corresponding record column of the columnstore index according to the record surrogate key index value, and the corresponding freshness vector index value is set to null. Insert type records are updated by adding surrogate key index values ​​to the OLTP row table according to the conditions for creating the columnstore index (specifying columns and satisfying selection conditions) and are inserted into the columnstore index in an append manner. Deletion of records adopts the same management strategy as the OLTP row table. When the deletion flag reaches a certain threshold, the table is cleaned up to release the storage space occupied by logically deleted records. At the same time, the columnstore index performs corresponding data cleanup to release the storage space occupied by logically deleted records in the columnstore index.

[0157] S12. When the OLTP row storage table adopts the off-site 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 update record, which is used to indicate the position of the corresponding record before the update 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 update record in the OLTP row table, so as to write the latest version update data to the specified update position in the column storage index.

[0158] To better illustrate the HTAP database construction method of this embodiment, Example 2 is presented here. This embodiment can be used to implement an in-memory database HTAP based on column-stored indexes. It can achieve hybrid storage and query processing engine technology by using indexes to join OLTP row storage engines and OLAP column storage engines.

[0159] In the OLTP row storage engine, an index structure is created to create a subset of data for the columnstore index. This structure records update operations after the columnstore index is created and separates the query's subset of records on the row storage engine and the columnstore index. The index structure also supports efficient data synchronization updates. Example 2 may include the following steps:

[0160] S1, such as Figure 2 As shown, a surrogate key index column SK is added to the row store table to assign a continuously incrementing ID to the selected record. This ID is used as the access address for the column store index record. SK can be non-contiguous and is used to distinguish between the original row store record and the record for which the column store index was created. When data synchronization is performed, the in-place update record on the row store table can be updated based on the SK value index to access the corresponding record position in the column store index.

[0161] S2, such as Figure 2As shown, according to application requirements, a columnstore index is created for a specified OLTP row store table. The columnstore index can be the entire data set or a selected or projected subset. For example, if A3 is a low-potential string type data, a dictionary table is created for this column in the OLTP row store engine when creating the columnstore index, and the columnstore index stores the corresponding dictionary compression code. Numeric low-potential value range columns can also use dictionary compression methods, such as lo_discount[0.01-0.1] and lo_quantity[1-50]. Numeric dictionary tables can also be created on the OLTP row store side, and the columnstore 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 columnstore index only stores the compression code of simple numeric types, which simplifies the data type of the columnstore index and eliminates the data semantics in the columnstore index. At the same time, queries on column A3, such as A3=AIR, are first executed on the dictionary table on the OLTP storage side to generate a bitmap index, such as... Figure 2 As shown, the columnstore index compresses and maps the value of column A3 to the corresponding position in the bitmap index (e.g., BitMap[A3[i]]). If the value is 1, then the record in column A3 satisfies the original A3=AIR predicate condition. This method delegates the handling of complex data types and processing methods in predicate operations to the OLTP row storage engine. Predicate operations on the columnstore index are unified into mapping access operations based on the dictionary table bitmap index, simplifying the functional design of complex predicate processing on the columnstore index side.

[0162] S3. In this embodiment, the S2 dictionary table compression method can be applied to compress the WHERE and GROUP-BY attributes of the low-potential set in the column store index and store the compressed code in the column store index.

[0163] S4. When a user creates a columnstore index, a copy of the columnstore index data based on a timestamp (e.g., SCN=70) is generated. At the same time, a freshness vector index of the same length as the columnstore index is created. The original OLTP rowstore table can directly access the entry address of the corresponding record in the freshness vector index and the columnstore index through the surrogate key index.

[0164] S5. A Delta index is created to record records updated (insert and update types) after the timestamp of the columnstore index creation. 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. Delete types are directly recorded in the original rowstore table's delete bitmap and are not recorded in the Delta index. While the freshness vector index excludes updated and deleted records during scanning, the Delta index scan supplements both added and updated records, forming a complete dataset.

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

[0166] The surrogate key index is a column added to the OLTP row storage table. When creating the 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, and the insertion of index entries is handled by the row storage engine through transaction, concurrency control, and multi-version access mechanisms.

[0167] S7. When executing a query, the columnstore index creation command determines whether the current query dataset is included in the columnstore index dataset. If it is, the query is supported through the columnstore index; otherwise, the query is redirected to the original rowstore data table. Columnstore indexes are used to accelerate user-defined datasets. Application scenarios include defining a subset of columnstore data based on available memory size (e.g., DRAM, NVM non-volatile memory, GPU memory size), defining a subset of columnstore data based on data access frequency (e.g., based on time frequency), and defining a subset of columnstore data based on a specified application theme (e.g., data mart). Together with a custom OLAP computing engine on the columnstore index, they provide high-performance OLAP computing capabilities. To simplify the use of columnstore indexes, this invention first supports accelerated OLAP query processing when the query dataset is a subset of columnstore data. When the query dataset is not entirely included in the columnstore index dataset, collaborative query processing between the rowstore engine and the columnstore index is implemented based on a cost model.

[0168] S8. When performing a query on a columnstore index, such as Figure 2As shown, assuming the SCN of the query on the columnstore index is 95, when scanning the freshness vector index, the vector cell storage SCN corresponding to record #1004 is 97, which is later than the query start time. Therefore, this updated record is accessible to the current query. When scanning the vector cell storage SCN corresponding to record #1010, which is 90, which is earlier than the query start time, this record is not accessible to the current query. The columnstore scan excludes a deleted record.

[0169] S9. Scanning the Delta index, record #1017 originally had an SCN of 85. An update operation occurred after the query execution when the SCN reached 110. The RowID index accesses the OLTP rowstore table, using its own concurrency control and multi-versioning mechanism to access the older version record with SCN 85. The record with RowID #1004 has an SCN of 97, which is later than the query execution time and has already been accessed in the columnstore index; therefore, this Delta record does not need to be accessed. The record with RowID #1018 has an SCN of 100, which is later than the query execution SCN and is not visible to the current query. The Delta index stores the entry addresses of records added and modified after the columnstore index was created. It determines the visibility of records in the current query by using the record's own SCN, and together with the columnstore index record access, it provides the dataset for the current query.

[0170] S10. When the database uses a unified row-store query processing engine, scans based on the freshness vector index provide on-demand access to a subset of data on the columnstore index, while Delta index scans provide the corresponding delta data subset on the rowstore engine. These are merged into a table scan dataset accessed by the query processing engine. When the columnstore index supports a customized OLAP query processing engine, the columnstore index and the OLTP rowstore Delta data can be viewed as two data shards on heterogeneous storage engines. The columnstore index is accessed based on the freshness vector index, and OLAP query tasks are executed. When the freshness vector index selectivity is high (high null value ratio, such as exceeding 90%), and the selectivity of selection or filtering operations on the columnstore index is much lower than the freshness vector index selectivity, the freshness vector index scan filtering operations can be delayed until after low-selectivity operations to improve query performance. This result is then merged with the query result of the rowstore update data subset accessed based on the Delta index into the final query result.

[0171] S11. When the number of non-null values ​​(updated records) in the freshness vector index reaches a certain threshold, the number of records accessed on the columnstore index decreases, while the number of rowstore records accessed based on the Delta index increases, leading to a decrease in overall query performance. When the data freshness reaches a preset threshold (e.g., the proportion of updated records), the columnstore index is rebuilt. For example, the REINDEXlo_csi command synchronizes the updated data to the columnstore index according to the current timestamp and columnstore index structure. Figure 3As shown, the current timestamp SCN=200. Accessing the row storage table via the Delta index, update (U) type records with SCNs earlier than 200, such as RowID #1004, are located in the columnstore index record using the surrogate key index value 4 for in-situ updates, and the corresponding cells in the freshness vector index are simultaneously set to null. Records with update type insert (I) in the Delta index and SCNs less than 200, such as RowIDs #1017 and #1018, are written to the columnstore index using the surrogate key index values ​​14 and 15, according to the columns selected in the columnstore index. The newly added records are then synchronized to the columnstore index. After processing, the corresponding index entries in the Delta index are deleted. Deleted records are not processed immediately; the freshness vector index still records the SCN at the time of deletion. When the number of deleted records in the table reaches a preset threshold, the original row storage table is reorganized, the record storage is reconstructed, the space for deleted records is released, and the columnstore index is rebuilt according to the reconstructed table.

[0172] S12. When a row-stored table uses an out-place update mechanism, the update operation is broken down into a strategy of deleting the original record and inserting the modified record into the table. For example... Figure 5 As shown, when record #1004 is updated, the record deletion flag is set to 1. The updated record, along with the surrogate key index value 4, is inserted as the new record at the position of record #1008 in the table, and an index entry is added to the Delta index. The update type remains U. Record #1017, when SCN=110, undergoes an update operation. The original record is marked as deleted, and the updated record and surrogate key index value are inserted as the new record #1020. The original index entry is replaced in the Delta index, and the update flag remains I. Under the cross-index update mechanism, the Delta index entry needs to be added (update) or replaced (update is performed on the inserted record) to the current record entry address to access the latest version of the data. During data synchronization, the record corresponding to the Delta index entry is updated according to the surrogate key index value mapped to the corresponding record in the columnstore index.

[0173] This embodiment can adopt a hybrid storage engine design of timestamp-based static column storage index and dynamic row storage. Only incremental queries based on freshness vector index 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 provides incremental queries after the column storage index is created. The Delta data management on the column storage index is pushed to the OLTP row storage engine, which simplifies the complexity of column storage index design.

[0174] This embodiment constructs an HTAP query processing engine that supports both OLTP row storage and OLAP column storage by using freshness vector indexes, surrogate key indexes, and Delta indexes. The indexes are maintained on the OLTP side and updated through mechanisms such as transaction processing and concurrency control. During a query, the column storage indexes access the indexes to execute the query task. The data management functions of the column storage indexes are simplified through the OLTP-side index maintenance and OLAP-side index access mechanisms.

[0175] 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. It supports users to select some rows and columns to build column storage indexes as needed, supports in-situ updates of data on the column storage index on the OLTP side, flexibly takes into account data popularity and space capacity, and improves column storage efficiency and data synchronization performance.

[0176] This embodiment improves the efficiency of columnstore access to the database through columnstore indexes and extends the high-performance OLAP analysis and processing capabilities. It accesses the read-only dataset on the columnstore index side and the update dataset on the rowstore side through surrogate key indexes, freshness vector indexes and Delta indexes to build a complete query dataset, and implements HTAP query processing on each dataset based on different query processing models.

[0177] like Figure 6 As shown in the figure, this embodiment proposes an HTAP database construction apparatus, which may include:

[0178] The selection unit 101 is used to 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.

[0179] The first creation unit 102 is used to create a surrogate key index column in the initial row storage table;

[0180] The allocation unit 103 is used to allocate incremental and consecutive proxy key index identifiers to each target row record in the proxy key index column, starting from a preset initial proxy key index identifier, to obtain the target row storage table;

[0181] The second creation unit 104 is used to create column data corresponding to any target column name based on the target column name and each proxy key index identifier;

[0182] The third creation unit 105 is used to create a target column storage table based on each target column name and the corresponding column data;

[0183] The construction unit 106 is used to construct a hybrid transaction processing and analytical processing HTAP database, which includes 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 querying, respectively.

[0184] It should be noted that the processing procedures and beneficial effects 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 can be referred to respectively. Figure 1 Steps S101 to S106 are not described in detail here.

[0185] Optionally, the second creation unit 104 is also used for:

[0186] In each row record corresponding to the surrogate key index identifier, determine the record value corresponding to the target column name;

[0187] If it is determined that the number of characters in at least N record values ​​is greater than a first preset threshold, and the number of times a record value is repeated in all record values ​​exceeds a second preset threshold, then all record values ​​are grouped to obtain at least one corresponding record value category, and a unique dictionary table code is set for each at least one record value category; where N is an integer greater than 1.

[0188] For any record value, determine the target dictionary table code corresponding to the record value according to the record value category, 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 order of the target surrogate key index identifier among all surrogate key index identifiers, and determine the ascending order as the order to be arranged for the target dictionary table code;

[0189] Based on the order in which each target dictionary table code is to be arranged, the target dictionary table codes corresponding to each record value are arranged, and the arrangement result is used as the column data corresponding to the target column name.

[0190] Optionally, the above-mentioned device further includes:

[0191] The operation unit is used to determine the total number of surrogate key index identifiers in the surrogate key index column after constructing a hybrid transaction processing and analytical processing HTAP database including a target row store table and a target column store table; create a freshness vector index column with the total number of cells equal to the total number; and create an operation type index table.

[0192] In response to a row record operation command on a target row storage table, perform data operations on at least one of the target row storage table, target column storage table, freshness vector index column, and operation type index table in the HTAP database.

[0193] Optionally, multiple column names may include an operation timestamp, with the initial surrogate key index set to 1;

[0194] The aforementioned operating unit is also used for:

[0195] In response to an update instruction for the record value corresponding to the target column name in the first row record, update the record value corresponding to the target column name in the first row record, and determine the first timestamp of the update operation and the first entry address of the first row record in the target row storage table;

[0196] Save the first timestamp to the cell corresponding to the first row of records, and store the first entry address and update operation identifier in the operation type index table.

[0197] Determine the first surrogate key index identifier corresponding to the first row record. Among the multiple cells in the freshness vector index column, identify the first cell whose cell order is equal to the size of the first surrogate key index identifier, and save the first timestamp to the first cell.

[0198] Optionally, the above-mentioned operating unit is also used for:

[0199] In response to the instruction to add a second record to the target row storage table, add a second record to the target row storage table, determine the second timestamp of the operation, and store the second timestamp in the second cell corresponding to the operation timestamp in the second row record;

[0200] If the second row is determined to be the row of interest, then an incrementing second surrogate key index identifier is assigned to the second row in the surrogate key index column. The second entry address of the second row in the target row storage table is obtained, and the second entry address and the new operation identifier are stored in the operation type index table accordingly.

[0201] Optionally, the above-mentioned operating unit is also used for:

[0202] In response to a deletion instruction on the third row record in the target row storage table, the identifier value in the deletion identifier bit corresponding to the third row record is changed from a non-deletion identifier value to a deleted identifier value, the third timestamp of the modification operation is recorded, and the third timestamp is stored in the cell corresponding to the operation timestamp in the third row record;

[0203] If the third row of records is determined to include a third surrogate key index identifier, then among the multiple cells in the freshness vector index column, the third cell whose cell order is equal to the size of the third surrogate key index identifier is identified, and the third timestamp is stored in the third cell.

[0204] Optionally, the above-mentioned device further includes: a query unit;

[0205] The query unit is used for:

[0206] After performing data operations on at least one of the target row storage table, target column storage table, freshness vector index column, and operation type index table in the HTAP database in response to a row record operation instruction on the target row storage table, the system receives a query instruction on the target column data corresponding to the target column name in the target column storage table and records the timestamp of the query instruction received.

[0207] Find each empty cell in the freshness vector index column, determine the first cell order of each empty cell in the freshness vector index column, and determine the record values ​​in the target column data that are in the first cell order as allowed access record values;

[0208] Among the timestamps currently stored in the freshness vector index column, find at least one target timestamp that is greater than the received timestamp, and determine the second cell order of each target timestamp in the freshness vector index column.

[0209] For any second cell order, determine the fourth surrogate key index identifier with the same size as the second cell order, determine the fourth row record corresponding to the fourth surrogate key identifier index in the target row storage table, and determine the record value corresponding to the target column name in the fourth row record as the allowed access record value;

[0210] Retrieve 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;

[0211] For any row record corresponding to an entry address, determine the timestamp corresponding to the operation timestamp in the row record. If the determined timestamp is less than the receiving timestamp, then determine the record value corresponding to the target column name in the row record as the allowed access record value.

[0212] All allowed access record values ​​are treated as a whole as the query result corresponding to the query command, and the query result is output.

[0213] The HTAP database construction apparatus proposed in this embodiment can, in response to a data selection command, 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. A surrogate key index column is created in the initial row storage table, and in the surrogate key index column, incrementally increasing and consecutively assigning surrogate key index identifiers to each target row record, starting from a preset initial surrogate key index identifier, to obtain the target row storage table. For any target column name, column data corresponding to the target column name is created based on the target column name and each surrogate key index identifier. A target column storage table is created based on each target column name and its corresponding column data. An HTAP database including the target row storage table and the target column storage table is created; wherein, the target row storage table and the target column storage table in the HTAP database are used for transaction processing and column data querying, respectively. The HTAP database of this embodiment can improve column data query efficiency based on the target column storage table while ensuring transaction processing efficiency based on the target row storage table.

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

[0215] This invention also provides a computer device having the above-described features. Figure 6 The HTAP database construction device shown.

[0216] Please see Figure 7 The present invention provides a schematic diagram of the structure of a computer device according to an optional embodiment. The computer device includes one or more processors 10, a memory 20, and interfaces for connecting the various components, including high-speed interfaces and low-speed interfaces. The various components are interconnected via different buses and can be mounted on a common motherboard or otherwise installed as needed. The processors can process instructions executed within the computer device, including instructions stored in or on memory to display graphical information of a GUI on an external input / output device (such as a display device coupled to the interface). In some optional embodiments, multiple processors and / or multiple buses can be used with multiple memories and multiple memory modules, if desired. Similarly, multiple computer devices can be connected, each providing some of the necessary operations (e.g., as a server array, a group of blade servers, or a multiprocessor system). Figure 7 Take a processor 10 as an example.

[0217] Processor 10 may be a central processing unit, a network processor, or a combination thereof. Processor 10 may further include a hardware chip. The hardware chip may be an application-specific integrated circuit (ASIC), a programmable logic device (PLD), or a combination thereof. The programmable logic device may be a complex programmable logic device (CAMP), a field-programmable gate array (FPGA), a general-purpose array logic (GPA), or any combination thereof.

[0218] The memory 20 stores instructions executable by at least one processor 10 to cause at least one processor 10 to perform the method shown in the above embodiments.

[0219] The memory 20 may include a program storage area and a data storage area. The program storage area may store the operating system and applications required for at least one function. The data storage area may store data created based on the use of the computer device. Furthermore, the memory 20 may include high-speed random access memory and may also include non-transitory memory, such as at least one disk storage device, flash memory device, or other non-transitory solid-state storage device. In some alternative embodiments, the memory 20 may optionally include memory remotely located relative to the processor 10, which can be connected to the computer device via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof.

[0220] Memory 20 may include volatile memory, such as random access memory. Memory may also include non-volatile memory, such as flash memory, hard disk, or solid-state drive. Memory 20 may also include combinations of the above types of memory.

[0221] The computer device also includes a communication interface 30 for communicating with other devices or communication networks.

[0222] This invention also provides a computer-readable storage medium. The methods described above according to embodiments of the invention can be implemented in hardware or firmware, or implemented as computer code that can be recorded on a storage medium, or implemented as computer code downloaded via a network and originally stored on a remote storage medium or a non-transitory machine-readable storage medium and then stored on a local storage medium. Thus, the methods described herein can be processed by software stored on a storage medium using a general-purpose computer, a dedicated processor, or programmable or dedicated hardware. The storage medium can be a magnetic disk, optical disk, read-only memory, random access memory, flash memory, hard disk, or solid-state drive, etc.; further, the storage medium can also include combinations of the above types of memory. It is understood that computers, processors, microprocessor controllers, or programmable hardware include storage components capable of storing or receiving software or computer code, which, when accessed and executed by the computer, processor, or hardware, implements the methods shown in the above embodiments.

[0223] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate 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, The method comprises: 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 an initial row storage table in response to a data selection instruction; creating a proxy key index column in the initial row storage table, and assigning an incremental and continuous proxy key index identifier to each of the target row records in the proxy key index column starting from a preset initial proxy 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 proxy key index identifiers; creating a target column storage table according to each of the target column names and the corresponding column data; constructing a hybrid transaction processing and analysis processing (HTAP) database comprising 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 respectively used for transaction processing and column data query; wherein the creating column data corresponding to the target column name according to the target column name and each of the proxy key index identifiers comprises: determining a record value corresponding to the target column name in each of the row records corresponding to the proxy key index identifier, respectively; 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 repetitions of a record value in all of the record values exceeds a second preset threshold, grouping all of the record values to obtain at least one record value category corresponding to the record values, and setting a unique dictionary table code corresponding to the at least one record value category, respectively; wherein N is an integer greater than 1; for any of the record values, determining a target dictionary table code corresponding to the record value according to the record value category corresponding to the record value, determining a target proxy key index identifier corresponding to the record value according to the target row record corresponding to the record value, determining an incremental arrangement order of the target proxy key index identifier in all of the proxy key index identifiers, and determining the incremental arrangement order as a to-be-arranged order of the target dictionary table code; arranging the target dictionary table code corresponding to each of the record values according to the to-be-arranged order of each of the target dictionary table codes, to obtain an arrangement result and use it as column data corresponding to the target column name.

2. The method of claim 1, wherein, After the construction of the hybrid transaction processing and analysis processing (HTAP) database comprising the target row storage table and the target column storage table, the method further comprises: determining the total number of the proxy key index identifiers in the proxy 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 for the target row storage table, 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.

3. The method of claim 2, wherein, The plurality of column names includes an operation timestamp, and the initial proxy key index is identified as 1; and in response to the row record operation instruction on the target row storage table, 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, comprising: In response to an update instruction on the record value corresponding to the target column name in the first row record, updating the record value corresponding to the target column name in the first row record, determining a first timestamp at which the update operation occurs and a first entry address of the first row record in the target row storage table; Saving the first timestamp to the operation timestamp in the cell corresponding to the first row record, and storing the first entry address and the update operation identifier in the operation type index table; Determining a first proxy key index corresponding to the first row record, and determining a first cell in which the cell order is equal to the size of the first proxy key index in the plurality of cells of the freshness vector index column, and saving the first timestamp to the first cell.

4. The method of claim 3, wherein, In response to the row record operation instruction on the target row storage table, 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, further comprising: In response to an instruction to add a second row record to the target row storage table, adding the second row record to the target row storage table, determining a second timestamp at which the addition operation occurs, and storing the second timestamp in a second cell corresponding to the second row record in the operation timestamp; If the second row record is a row record of interest, then continue to assign an increasing second proxy key index to the second row record in the proxy key index column, obtain a second entry address of the second row record in the target row storage table, and store the second entry address and the addition operation identifier in the operation type index table.

5. The method of claim 4, wherein, In response to the row record operation instruction on the target row storage table, 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, further comprising: In response to a deletion instruction on a third row record in the target row storage table, modifying an identification value in a deletion identifier bit corresponding to the third row record from a non-deletion identification value to a deleted identification value, recording a third timestamp at which the modification operation occurs, and storing the third timestamp in a cell corresponding to the third row record in the operation timestamp; If the third row record includes a third proxy key index, then in the plurality of cells of the freshness vector index column, a third cell is determined in which the cell order is equal to the size of the third proxy key index, and the third timestamp is stored in the third cell.

6. The method according to any one of claims 2 to 5, characterized in that, After the 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 comprises: receiving a query instruction for target column data corresponding to the target column name in the target column storage table, and recording a receiving timestamp of the query instruction; finding each null cell in the freshness vector index column, determining a first cell order where each null cell is located in the freshness vector index column, and determining a record value in the target column data at each first cell order as an allowed access record value; finding at least one target timestamp greater than the receiving timestamp among the various timestamps currently stored in the freshness vector index column, and determining a second cell order where each target timestamp is located in the freshness vector index column; for any second cell order, determining a fourth proxy key index identifier equal in size to the second cell order, determining a fourth row record corresponding to the fourth proxy key index identifier in the target row storage table, and determining a record value corresponding to the target column name in the fourth row record as an allowed access record value; obtaining each entry address currently stored in the operation type index table, and finding a row record corresponding to each entry address in the target row storage table; for any row record corresponding to each entry address, determining a timestamp corresponding to the operation timestamp in the row record, and if the determined timestamp is less than the receiving timestamp, determining a record value corresponding to the target column name in the row record as an allowed access record value; integrating all the allowed access record values as a query result corresponding to the query instruction, and outputting the query result. 7.A HTAP database construction apparatus, characterized in that, Comprise: a selection unit configured 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 saved in an initial row storage table in response to a data selection instruction; a first creation unit configured to create a proxy key index column in the initial row storage table; an allocation unit configured to allocate an incremental and continuous proxy key index identifier to each target row record in the proxy key index column starting from a preset initial proxy key index identifier, to obtain a target row storage table; a second creation unit configured to, for any target column name, create column data corresponding to the target column name according to the target column name and each proxy key index identifier; a third creation unit configured to create a target column storage table according to each target column name and corresponding column data; a construction unit configured to construct a hybrid transaction processing and analysis processing (HTAP) database comprising 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 respectively used for transaction processing and column data query; wherein the second creation unit is further configured to: Determine a record value corresponding to the target column name in each of the proxy key index identifiers; Determine that the number of characters of at least N record values is greater than a first preset threshold, and determine that the number of repetitions of a record value in all the record values exceeds a second preset threshold, then group all the record values to obtain at least one record value category corresponding thereto, and set a unique dictionary table code corresponding to the at least one record value category respectively; 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 proxy key index identifier corresponding to the record value according to the target row record corresponding to the record value, determine the incremental arrangement order of the target proxy key index identifier in all the proxy key index identifiers, and determine the incremental arrangement order as the arrangement order to be arranged of the target dictionary table code; According to the arrangement order to be arranged of each target dictionary table code, arrange the target dictionary table code corresponding to each record value to obtain an arrangement result as the column data corresponding to the target column name.

8. A computer device, comprising: Comprise: A memory and a processor, which are mutually connected in communication, the memory stores computer instructions, and the processor executes the computer instructions to perform the HTAP database construction method in any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that, The computer readable storage medium stores computer instructions, which are used to make the computer execute the HTAP database construction method in any one of claims 1 to 6.

Images