Data processing method and device, electronic equipment and storage medium

Abstract

Embodiments of the present application provide a data processing method and device, electronic equipment and storage medium, and relate to the technical field of databases. The method comprises: receiving a data query request and determining a target data table corresponding to the data query request; determining a target index corresponding to the target data table from a plurality of redistribution indexes; wherein the data in the redistribution indexes is stored in a distributed database based on the distribution of index columns of the redistribution indexes; when the operation for the data query request is directed to the index column of the target index, the original execution plan is optimized according to the target index to generate a target execution plan; the operation for the data query request comprises a single-table aggregation operation and / or a multi-table association operation; and the target execution plan is run to obtain a query result corresponding to the data query request. The embodiments of the present application realize query optimization, reduce cross-node data exchange operations, improve the efficiency of data processing, and improve the performance of the entire distributed database.

Description

Technical Field

[0001] This application relates to the field of database technology, and more specifically, to a data processing method, apparatus, electronic device, and storage medium. Background Technology

[0002] With the development of information technology, databases have been widely used, the amount of data stored is increasing daily, and users' query needs for data in databases are becoming more and more complex. This necessitates the optimization of database queries to ensure the efficiency and quality of the database when processing a large number of complex query requests.

[0003] A distributed HTAP (Hybrid Transactional Analytical Processing) database refers to a distributed database that simultaneously possesses OLTP (On-Line Transactional Processing) and OLAP (On-Line Analytical Processing) capabilities.

[0004] Currently, in distributed HTAP databases, data can only be distributed according to the primary key of the table, which makes it impossible to optimize OLAP queries. During the data query process, a large amount of cross-node data exchange is required, resulting in low data processing efficiency and a decline in database performance. Summary of the Invention

[0005] This application provides a data processing method, apparatus, electronic device, and storage medium, which can solve the problems of existing distributed HTAP databases being unable to optimize OLAP queries and having low data processing efficiency.

[0006] The technical solution is as follows:

[0007] According to one aspect of the embodiments of this application, a data processing method is provided, the method comprising:

[0008] Receive a data query request and determine the target data table corresponding to the data query request;

[0009] The target index corresponding to the target data table is determined from multiple redistributed indexes; wherein the data in the redistributed index is stored in a distributed database based on the index columns of the redistributed index.

[0010] When the operation for the data query request points to the index column of the target index, the original execution plan is optimized based on the target index to generate the target execution plan;

[0011] Operations for the data query request include single-table aggregation operations and / or multi-table join operations;

[0012] Run the target execution plan to obtain the query results corresponding to the data query request.

[0013] Optionally, the method further includes:

[0014] Use at least one column from the data table to be queried as the index column;

[0015] Based on the index column, the redistributed index is established; wherein, the redistributed index contains all rows and all columns of the corresponding data table to be queried, the redistributed index includes multiple data buckets, and all data rows included in the data buckets have the same index value;

[0016] Determine the same distribution group to which the redistributed index belongs; the same distribution group includes multiple redistributed indexes with the same data distribution in their index columns.

[0017] Optionally, data buckets with the same index value in the same distribution group are stored on the same database node; the data buckets with the same index value in the same distribution group are migrated as a whole when data scheduling occurs.

[0018] Optionally, the operations for the data query request include single-table aggregation operations;

[0019] The optimization of the original execution plan based on the redistribution index includes:

[0020] Determine the aggregated data table that participates in the single-table aggregation operation, and determine the first index corresponding to the aggregated data table from the plurality of redistributed indexes;

[0021] If the grouping column specified by the single-table aggregation operation contains the index column of the first index, then the first index is scanned and the cross-node data exchange operator in the original execution plan is deleted.

[0022] Optionally, the operations for the data query request include multi-table join operations;

[0023] The optimization of the original execution plan based on the redistribution index includes:

[0024] Identify at least two related data tables that participate in the multi-table join operation, and determine a second index corresponding to the related data tables from the plurality of redistributed indexes;

[0025] If the join column specified by the multi-table join operation contains the index column of each second index, and each second index belongs to the same distribution group, then scan each second index and delete the cross-node data exchange operator in the original execution plan.

[0026] Optionally, the method further includes:

[0027] If there is at least one first associated data table that meets the preset conditions, and at least one second associated data table that does not meet the preset conditions, then the redistribution index corresponding to the first associated data table is scanned, the cross-node data exchange operator corresponding to the first associated data table is eliminated, and the second associated data table is scanned, while the cross-node data exchange operator corresponding to the second associated data table is retained.

[0028] The preset condition is that the associated data table has a corresponding redistributed index, and the associated column specified by the multi-table association operation includes the index column of the redistributed index corresponding to the associated data table.

[0029] Optionally, the method further includes:

[0030] When data scheduling occurs in the distributed database, the multiple redistributed indexes remain unchanged;

[0031] When data scheduling in the distributed database ends, the multiple redistributed indexes are updated.

[0032] According to another aspect of the embodiments of this application, a data processing apparatus is provided, the apparatus comprising:

[0033] The target data table determination module is used to receive a data query request and determine the target data table corresponding to the data query request.

[0034] The target index determination module is used to determine the target index corresponding to the target data table from multiple redistributed indexes; wherein the data in the redistributed index is stored in a distributed database based on the index columns of the redistributed index;

[0035] The optimization module is used to optimize the original execution plan and generate a target execution plan based on the target index when the operation for the data query request points to the index column of the target index; the operation for the data query request includes single-table aggregation operation and / or multi-table join operation;

[0036] The execution module is used to run the target execution plan and obtain the query results corresponding to the data query request.

[0037] According to another aspect of the embodiments of this application, an electronic device is provided, the electronic device including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of any of the above data processing methods.

[0038] According to another aspect of the embodiments of this application, a computer-readable storage medium is provided, on which a computer program is stored, which, when executed by a processor, implements the steps of any of the data processing methods described above.

[0039] The beneficial effects of the technical solutions provided in this application are:

[0040] By pre-creating a target index for the target data table and distributing the data in the target index across the distributed database according to its indexed columns, query optimization is achieved when single-table aggregation operations and / or multi-table join operations for data query requests point to the indexed columns of the target index. By optimizing the original execution plan based on the target index, a target execution plan is obtained, reducing cross-node data exchange operations in the target execution plan, improving data processing efficiency, and enhancing the overall performance of the distributed database. Attached Figure Description

[0041] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the description of the embodiments of this application will be briefly introduced below.

[0042] Figure 1 A flowchart illustrating a data processing method provided in an embodiment of this application;

[0043] Figure 2 This is a schematic diagram of the structure of a query optimization system provided in an embodiment of this application;

[0044] Figure 3 A system architecture diagram of a distributed HTAP database provided in this application embodiment;

[0045] Figure 4 This is a schematic diagram of the structure of a data processing device provided in an embodiment of this application;

[0046] Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0047] The embodiments of this application are described below with reference to the accompanying drawings. It should be understood that the embodiments described below with reference to the accompanying drawings are exemplary descriptions for explaining the technical solutions of the embodiments of this application, and do not constitute a limitation on the technical solutions of the embodiments of this application.

[0048] Those skilled in the art will understand that, unless otherwise stated, the singular forms “a,” “an,” and “the” used herein may also include the plural forms. It should be further understood that the terms “comprising” and “including” as used in embodiments of this application mean that the corresponding feature can be implemented as the presented feature, information, data, step, operation, element, and / or component, but do not exclude implementation as other features, information, data, step, operation, element, component, and / or combinations thereof supported by the art. It should be understood that when we say that an element is “connected” or “coupled” to another element, the one element can be directly connected or coupled to the other element, or it can mean that the one element and the other element establish a connection relationship through an intermediate element. Furthermore, “connected” or “coupled” as used herein can include wireless connection or wireless coupling. The term “and / or” as used herein indicates at least one of the items defined by the term; for example, “A and / or B” can be implemented as “A,” or as “B,” or as “A and B.”

[0049] To make the objectives, technical solutions, and advantages of this application clearer, the embodiments of this application will be described in further detail below with reference to the accompanying drawings.

[0050] In a distributed HTAP database, data exists in two formats: row-based and column-based, corresponding to OLTP and OLAP scenarios respectively. The data in row-based and column-based databases are synchronized in some way. In a high-real-time HTAP database, this synchronization method requires that the data in both row-based and column-based databases have the same distribution to ensure synchronization efficiency, i.e., real-time performance. This necessitates that the data in the HTAP database should conform to the distribution format used in OLTP.

[0051] In distributed HTAP databases, to meet OLTP requirements, data can only be distributed according to the table's primary key, making query optimization impossible for OLAP queries. If an OLAP query includes single-table aggregations where the grouping column is not a primary key column, or multi-table joins where the join column is not a primary key column, inefficient cross-node data exchange is possible, significantly reducing the performance of the distributed HTAP database.

[0052] The data processing method, apparatus, electronic device, and storage medium provided in this application are intended to solve the above-mentioned technical problems of the prior art.

[0053] The technical solutions of this application and their effects are described below through several exemplary embodiments. It should be noted that the following embodiments can be referenced, borrowed from, or combined with each other. Identical terms, similar features, and similar implementation steps in different embodiments will not be repeated.

[0054] Figure 1 This is a flowchart illustrating a data processing method provided in an embodiment of this application, as shown below. Figure 1 As shown, the method includes:

[0055] Step S101: Receive a data query request and determine the target data table corresponding to the data query request.

[0056] Specifically, a data query request can be a request issued by a user to query data. The data query request can include information about the object of the query, which can specify which data tables need to be queried. The data query request can also include query result information, that is, what information needs to be filtered out from these data tables.

[0057] The data table to be queried as indicated by the data query request is taken as the corresponding target data table. The data query request may correspond to one target data table or multiple target data tables. This application embodiment does not limit this.

[0058] Step S102: Determine the target index corresponding to the target data table from multiple redistributed indexes; wherein, the data in the redistributed index is stored in the distributed database based on the index column distribution of the redistributed index.

[0059] Specifically, before performing data queries, multiple redistributed indexes can be pre-built, and the data distributed in the redistributed indexes can be stored in a distributed database based on the index columns of the redistributed indexes. The distributed database can include multiple database nodes; through the redistributed indexes, the database node storing the corresponding data can be quickly and accurately located, and the index columns can be used to characterize the distribution location of the corresponding data within the distributed database.

[0060] In distributed databases, data in tables is typically stored in a distributed manner based on primary keys. In this embodiment, by establishing a redistributed index, the data in the table can be redistributed to meet the conditions for subsequent query optimization.

[0061] The target index can be a redistributed index corresponding to the target data table. The target data table can correspond to one target index or multiple target indexes. This application embodiment does not limit this.

[0062] Step S103: When the operation for the data query request points to the index column of the target index, the original execution plan is optimized based on the target index to generate the target execution plan;

[0063] Operations for data query requests include single-table aggregation operations and / or multi-table join operations.

[0064] Specifically, to obtain the results from a data query request, a series of operations need to be performed on the target data table. These operations can include single-table aggregation operations and / or multi-table join operations. Single-table aggregation operations can be used to aggregate or merge data from multiple rows or columns in a single table, while multi-table join operations can be used to link multiple tables together.

[0065] The following uses Table A and Table B as examples for illustration. Table A is the sales details table, which contains a sales detail number (primary key) that is different for each sales record, the goods sold, the supplier of the goods, and the sales price; Table B is the purchase details table, which contains a purchase detail number (primary key) that is different for each purchase record, the goods purchased, the supplier of the goods, and the purchase price.

[0066] Table A: Sales Details

[0067] Sales details number commodity supplier Selling price 1 A First 10 2 A First 10 3 A Second 15 4 B First 20 5 B Second 25 6 B Second 25 7 C First 35 8 C Second 30 9 C First 35 10 C Second 30

[0068] Table B: Procurement Details

[0069] Purchase details number commodity supplier Purchase price Purchase volume 1 A First 8 100 2 A Second 10 100 3 B First 18 100 4 B Second 20 100 5 C First 30 100 6 C Second 28 100

[0070] For example, data query request 1 is "query the sales quantity of all products of each supplier". It is necessary to group all sales records by supplier in table A and count the number of records in each supplier group. This operation is a single table aggregation operation. In other words, the operation for data query request 1 includes single table aggregation operation.

[0071] For example, data query request 2 is "query the gross profit of all products of each supplier". It requires retrieving the sales records in table A and the purchase records in table B, and matching the records on both sides using the supplier column. This operation is a multi-table join operation. In other words, the operation for data query request 2 includes multi-table join operations.

[0072] In existing technologies, to meet OLTP requirements, data tables are stored in a distributed HTAP database based on primary keys. Taking table A as an example, the primary key of table A is the sales detail number. Table A contains 10 rows of data. Rows with detail numbers 1-5 can be stored in database node DB1, and rows with detail numbers 6-10 can be stored in database node DB2. To satisfy data query request 1, distributed computing is required. DB1 calculates the sales data for supplier A, and DB2 calculates the sales data for supplier B. Since the sales records for supplier A and supplier B are scattered across DB1 and DB2, cross-node data exchange is required between DB1 and DB2. DB1 transmits the local data rows for supplier B to DB2, and DB2 transmits the local data rows for supplier A to DB1. Only then can DB1 and DB2 calculate the total sales volume for supplier A and supplier B respectively.

[0073] In this embodiment of the application, a redistribution index table A' can be generated based on the supplier column in table A as the index column, as shown in the table below:

[0074] Table A'

[0075] Sales details number commodity supplier Selling price 1 A First 10 2 A First 10 4 B First 20 7 C First 35 9 C First 35 3 A Second 15 5 B Second 25 6 B Second 25 8 C Second 30 10 C Second 30

[0076] The supplier column in table A' is an index column. Five rows of data for supplier A in table A' can be stored in database node DB1, and five rows of data for supplier B in table A' can be stored in database node DB2.

[0077] With table A' stored in the distributed database according to the above distribution method, when data query request 1 is received, the target data table is table A, the index column is table A', and the target index is the supplier column. When the supplier column is specified as the grouping column (i.e., the index column of the target index) for the single-table aggregation operation in data query request 1, in order to obtain the query result corresponding to data query request 1, that is, the total sales quantity of goods purchased by supplier A and supplier B, the query calculation can be performed independently on DB1 and DB2 respectively, without the need for cross-node data exchange between DB1 and DB2, thus achieving the same distribution optimization.

[0078] One type of optimization is called "uniform distribution optimization." In distributed databases, users can typically choose any column in a table as an index column, allowing the data in that table to be distributed across different database nodes according to that index column. When performing single-table aggregations or multi-table joins, if the aggregation grouping column or the join column is the aforementioned index column, each database node can perform the aggregation or join calculation locally, avoiding data exchange across nodes.

[0079] Similarly, a redistributed index table B' can be generated based on the supplier column in table B as the index column.

[0080] Table B':

[0081] Purchase details number commodity supplier Purchase price Purchase volume 1 A First 8 100 3 B First 18 100 5 C First 30 100 2 A Second 10 100 4 B Second 20 100 6 C Second 28 100

[0082] The supplier column in Table B' is an index column. The three rows of data for supplier A in Table B' can be stored in database node DB1, and the three rows of data for supplier B in Table B' can be stored in database node DB2.

[0083] With table B' stored in the distributed database according to the above distribution method, when data query request 2 is received, the target data tables include tables A and B, and the target indexes include tables A' and B'. The index columns of tables A' and B' are both supplier columns. When the supplier column is specified as the join column (i.e., the index column of the target index) for the multi-table join operation in data query request 2, in order to obtain the query result corresponding to data query request 2, i.e., the gross profit of goods purchased by supplier A and supplier B, since the five rows of supplier A' in table A' are stored in DB1, and the three rows of supplier A' in table B' are stored in DB1, the join calculation can be directly performed on DB1 to obtain the gross profit of supplier A's goods, without the need for cross-node data exchange with DB2; similarly, the join calculation can be directly performed on DB2 to obtain the gross profit of supplier B's goods, without the need for cross-node data exchange with DB1; that is, the same distribution optimization can be achieved.

[0084] It should be noted that subsequent embodiments are described based on the above examples, but the above examples do not limit the data processing methods of this application.

[0085] Based on the above principles, it can be seen that when single-table aggregation operations and / or multi-table join operations for a data query request point to the index columns of the target index, query optimization can be achieved. Furthermore, the original execution plan can be optimized based on the target index, and the optimized execution plan can be used as the target execution plan. The execution plan can be a set of operations that implement a specific query request, and the original execution plan can be an execution plan generated based on the primary key column distribution of the target data table.

[0086] By optimizing the original execution plan based on the target index, a target execution plan can be obtained. This can minimize cross-node data exchange operations in the target execution plan, improve data processing efficiency, and thus enhance the performance of the entire distributed database.

[0087] Furthermore, the query optimization in this embodiment is based on indexes. Distributed databases can maintain consistency by processing the target data and its corresponding target index simultaneously in a transaction.

[0088] Step S104: Run the target execution plan to obtain the query results corresponding to the data query request.

[0089] Specifically, after obtaining the target execution plan, the target execution plan can be run to output the query results corresponding to the data query request.

[0090] In this embodiment, a target index for the target data table is pre-established, and the data in the target index is distributed across the distributed database according to its index columns. This enables query optimization when single-table aggregation operations and / or multi-table join operations for data query requests point to the index columns of the target index. By optimizing the original execution plan based on the target index, a target execution plan is obtained, reducing cross-node data exchange operations in the target execution plan, improving data processing efficiency, and enhancing the overall performance of the distributed database.

[0091] As an optional embodiment, the method further includes:

[0092] Use at least one column from the data table to be queried as the index column;

[0093] A redistributed index is created based on the indexed column. The redistributed index contains all rows and columns of the corresponding data table to be queried. The redistributed index includes multiple data buckets, and all data rows in the data buckets have the same index value.

[0094] Determine the same distribution group to which the redistributed index belongs; the same distribution group includes redistributed indexes with multiple index columns having the same data distribution.

[0095] Specifically, the data table to be queried can be obtained in advance. The data table to be queried can be any data table that may be involved in the data query, and the data table to be queried may include the target data table.

[0096] Select at least one column from the data table to be queried as the index column. The index column can be any column from the data table to be queried, or it can be a combination of at least two columns from the data table to be queried. The at least two columns can be adjacent or not adjacent. This application embodiment does not limit this.

[0097] An index column can include multiple index values. Different index values ​​can be arranged in a certain order, and identical index values ​​can be arranged consecutively. Based on the distribution of each index value in the index column, the rows of data in the data to be queried can be reordered according to their corresponding index values. The sorted data table is then used as the redistributed index of the data table to be queried. Examples of the data tables to be queried and their redistributed indexes can be found in Tables A and A', as well as Tables B and B', in the above embodiments.

[0098] An index column can include multiple index values, which can be arranged in any order, and identical index values ​​can be arranged consecutively. Based on the distribution of the index values ​​in the index column, the rows of data in the data to be queried can be rearranged according to their corresponding index values, placing them in the positions of the corresponding index values. The rearranged data table serves as the redistributed index of the data table to be queried. Examples of the data tables to be queried and their redistributed indexes can be found in Tables A and A', as well as Tables B and B', in the above embodiments.

[0099] A data bucket is formed by grouping all rows with the same index value. The redistributed index, created using the above steps, comprises multiple data buckets and includes all rows and columns corresponding to the data being queried.

[0100] The redistributed index contains the same data as the corresponding table to be queried, and the redistributed index contains all rows and columns of the table to be queried. By creating a redistributed index, the data in the table to be queried can be redistributed in the distributed database to meet the conditions for subsequent query optimization.

[0101] In this embodiment, the redistributed index is created by rearranging the data table to be queried according to the index columns, without changing the composition of the rows in the data table to be queried. This ensures that the redistributed index contains all rows and columns of the data table to be queried, meaning it has the properties of a clustered index. Simultaneously, the redistributed index and the data table to be queried are stored independently in the distributed database, meaning the redistributed index has the properties of a secondary index. Furthermore, the distributed database can maintain consistency by processing both the data to be queried and its corresponding redistributed index simultaneously within a transaction.

[0102] After creating a redistributed index, the corresponding homodistributed group can be determined. A homodistributed group includes multiple redistributed indexes with the same data distribution in their index columns. In the example above, the index columns of tables A' and B' have the same data distribution and belong to the same homodistributed group.

[0103] As an optional embodiment, in this method,

[0104] Data buckets with the same index value in the same distribution group are stored on the same database node; data buckets with the same index value in the same distribution group are migrated as a whole when data scheduling occurs.

[0105] Specifically, a homogeneous distribution group includes multiple redistributed indexes, and a redistributed index includes multiple data buckets. Data buckets with the same index value in a homogeneous distribution group are stored in the same database node. For example, in table A', rows 1-5 are in data bucket A1, rows 6-10 are in data bucket A2, and in table B', rows 1-3 are in data bucket B1, rows 4-6 are in data bucket B2. Table A' and table B' belong to the same homogeneous distribution group. In this homogeneous distribution group, both data bucket A1 and data bucket B1 are from supplier A, meaning they have the same index value. Data bucket A1 and data bucket B1 are stored in DB1. Similarly, data bucket A2 and data bucket B2 are stored in DB2.

[0106] Furthermore, data buckets with the same index value in the same distribution group are migrated as a whole during data scheduling. This ensures that data buckets with the same index value are always stored on the same database node before and after data scheduling, so that the distribution of data buckets with the same index value is not affected before and after data scheduling. This ensures that the conditions for same distribution optimization can be met before and after data scheduling.

[0107] As an optional embodiment, the operation for the data query request includes a single-table aggregation operation;

[0108] The optimization of the original execution plan based on the redistribution index includes:

[0109] Determine the aggregated data table to participate in the single-table aggregation operation, and determine the first index corresponding to the aggregated data table from multiple redistributed indexes;

[0110] If the grouping column specified by the single-table aggregation operation contains the index column of the first index corresponding to the aggregated data table, then the first index is scanned and the cross-node data exchange operator in the original execution plan is deleted.

[0111] Specifically, when the operation for the data query request includes a single-table aggregation operation, the optimization process of the original execution plan may include: First, the data table that needs to be aggregated is designated as the aggregation table; then, the redistribution index of the aggregation table is determined from multiple redistribution indexes and designated as the first index; when the grouping column specified by the single-table aggregation operation contains the index column of the first index corresponding to the aggregation table, the scanning of the aggregation table is replaced by scanning the first index corresponding to the aggregation table, and the cross-node data exchange operator in the original execution plan is deleted; when the above conditions are not met, the aggregation table is scanned.

[0112] Among them, the grouping column specified in the single table aggregation operation can be the column in the aggregated data table that needs to be aggregated / merged in the single table aggregation operation, and the cross-node data exchange operator can be the operation unit that performs data exchange between different database nodes.

[0113] For data query request 1, which is "query the sales quantity of all products of each supplier", data query request 1 includes a single table aggregation operation. The aggregated data table is table A, the grouping column is the supplier column, the first index is table A', and the index column is the supplier column. At this time, the index column of the first index is the grouping column specified by the single table aggregation operation, which satisfies the optimization conditions.

[0114] Therefore, table A' can be scanned directly. Since table A' has been pre-distributed based on the supplier column, all sales records of supplier A are stored in DB1, and all sales records of supplier B are stored in DB2. In response to data query request 1, the query can be performed locally in DB1 and DB2 without exchanging data with other database nodes. Therefore, the cross-node data exchange operator in the original execution plan can be eliminated, achieving uniform distribution optimization.

[0115] As an optional implementation, the operations for data query requests include multi-table join operations;

[0116] The optimization of the original execution plan based on the redistribution index includes:

[0117] Identify at least two related data tables involved in the multi-table join operation, and determine the second index corresponding to the related data tables from multiple redistributed indexes;

[0118] If the join column specified by the multi-table join operation contains the index column of each second index, and each second index belongs to the same distribution group, then scan each second index and delete the cross-node data exchange operator in the original execution plan.

[0119] Specifically, when the operation for the data query request includes multi-table join operations, the optimization process of the original execution plan may include: First, the data tables that need to be joined by multiple tables are designated as join data tables, thus obtaining at least two join data tables; then, the redistributed index corresponding to each join data table is determined from multiple redistributed indexes and used as a second index, thus obtaining a second index with the same number as the number of join data tables.

[0120] If the join column specified by the multi-table join operation contains the index column of each second index, and each second index belongs to the same distribution group, then scanning each join table can be replaced by scanning its corresponding second index, and the cross-node data exchange operator in the original execution plan can be deleted; if the above conditions are not met, then each join table is scanned.

[0121] In multi-table join operations, the column to be joined can be specified in the join table. Since multi-table join operations involve multiple join tables, the column to be joined can be multiple.

[0122] For data query request 2, "Query the gross profit of all products of each supplier", data query request 2 includes a multi-table join operation. The joined data tables include table A and table B, the joined column is the supplier, and the second index includes table A' and table B'. At this time, the index columns of the second index are the same as the joined columns, and table A' and table B' belong to the same identical distribution group, which satisfies the optimization conditions.

[0123] Therefore, tables A' and B' can be scanned directly. Since tables A' and B' have been pre-distributed based on the supplier column, and data corresponding to the same index value is stored on the same database node, that is, all sales records and all purchase records of supplier A are stored in DB1, and all sales records and all purchase records of supplier B are stored in DB2. In response to data query request 2, the query can be performed locally in DB1 and DB2 without exchanging data with other database nodes. Therefore, the cross-node data exchange operator in the original execution plan can be eliminated, achieving the same distribution optimization.

[0124] As an optional embodiment, the method further includes:

[0125] If there is at least one first associated data table that meets the preset conditions, and at least one second associated data table that does not meet the preset conditions, then the redistribution index corresponding to the first associated data table is scanned, the cross-node data exchange operator corresponding to the first associated data table is eliminated, and the second associated data table is scanned, while the cross-node data exchange operator corresponding to the second associated data table is retained.

[0126] The preset conditions are that the associated data table has a corresponding redistributed index, and the associated column specified by the multi-table join operation contains the index column of the redistributed index corresponding to the associated data table.

[0127] Specifically, multi-table join operations involve multiple related data tables. Not every related data table has a corresponding redistributed index. Therefore, these multiple related data tables can be divided into a first related data table and a second related data table. If the preset condition is the existence of a redistributed index, and the join column specified in the multi-table join operation contains the index column of the redistributed index corresponding to the related data table, then the related data table that meets the above preset condition is designated as the first related data table; the related data table that does not meet the above preset condition is designated as the second related data table.

[0128] For the first associated data table, the redistributed index corresponding to the first associated data table can be scanned directly to eliminate the cross-node data exchange operator corresponding to the first associated data table; for the second associated data table, the second associated data table can be scanned and the cross-node data exchange operator corresponding to the second associated data table can be retained, thereby changing the distributed association in the original execution plan where multiple tables need to exchange data to a distributed association where only tables that do not meet the preset conditions need to exchange data.

[0129] As an optional embodiment, the method further includes:

[0130] When data scheduling occurs in a distributed database, multiple redistributed indexes should be maintained unchanged.

[0131] When data scheduling in the distributed database ends, multiple redistributed indexes are updated.

[0132] Specifically, when a distributed database is scaled up or down or hotspots are scheduled, data migration will occur in the distributed database. At the same time, the distribution of data in the distributed database across various database nodes will change, and the distribution positions of multiple redistributed indexes in the distributed database will also change.

[0133] For data query requests received during data scheduling, multiple redistributed indexes can be maintained unchanged, and query optimization can be performed based on the existing redistributed indexes. Once all data migration in the distributed database is complete, the distribution of the redistributed indexes in the distributed database has changed. Therefore, it is necessary to update the redistributed indexes and use the new redistributed indexes for subsequent query optimization. After the redistributed indexes have been updated, the previous redistributed indexes can be deleted.

[0134] By retaining the original redistribution index during data scheduling and using the new redistribution index after data scheduling is complete, the same-distribution optimization can still be effective during data scheduling.

[0135] Since the redistributed index and the corresponding data table are stored independently, the data distribution of the redistributed index and the corresponding data table are also independent of each other, and therefore their scheduling is also independent of each other and will not affect each other.

[0136] As an optional embodiment, Figure 2 This is a schematic diagram of the structure of a query optimization system provided in an embodiment of this application, as shown below. Figure 2 As shown, the system includes:

[0137] The metadata module 201 provides users with methods for creating and deleting redistributed indexes. Users can create or delete redistributed indexes for a table using a table creation statement or an alter table statement (a type of SQL statement).

[0138] The transaction module 202 is used to synchronously perform corresponding operations on the redistributed index data as table data is inserted, updated, or deleted within a transaction. This ensures that the redistributed index data has the same ACID semantics as the table data. ACID refers to the four properties that a database management system must possess to ensure that transactions are correct and reliable during the writing or updating of data: atomicity, consistency, isolation, and durability.

[0139] The distributed storage computing module 203 includes a storage submodule 213 and a computing submodule 223. The storage submodule 213 is used for distributed storage of redistributed index data and schedules the redistributed index data to designated nodes according to the instructions of the data scheduling module, thereby satisfying the condition of homogeneous distribution optimization. The computing submodule 223 is used for distributed execution of a query plan. Each node on the distributed cluster can perform calculations on the data stored on that node according to the execution plan.

[0140] The query optimizer module 204 is used to perform homo-distribution optimization on single-table aggregation and multi-table join queries that meet the conditions based on the redistribution index information, thereby eliminating cross-node data exchange operations in the query plan.

[0141] The data scheduling module 205 is used to schedule data when the distributed cluster is scaling up or down or when hotspot scheduling is required.

[0142] As an optional embodiment, Figure 3 A system architecture diagram of a distributed HTAP database provided for embodiments of this application is shown below. Figure 3 As shown, this distributed HTAP database consists of three parts:

[0143] SQL layer: Responsible for receiving user SQL (Structured Query Language) query requests, generating and optimizing SQL execution plans, and performing some simple calculations;

[0144] Scheduling layer: Responsible for deciding how to distribute data storage, such as how the data in a table is divided into multiple parts and stored on multiple nodes;

[0145] Storage / Computation Layer: Responsible for storing data and related querying and computation.

[0146] At the SQL layer, redistributed indexes can be created, and query optimization can be performed. Query optimization specifically includes:

[0147] For single-table aggregation operations, the query optimizer checks whether the aggregation column contains an index column of a redistributed index in the table. If it does, the query optimizer converts the table scan into a scan of the corresponding redistributed index and eliminates the data exchange operator in the original execution plan, thus changing the execution plan to require only single-machine aggregation within the storage / compute nodes of the storage / compute layer. If it does not contain the index column, the query optimizer generates the relevant distributed aggregation execution plan according to the traditional algorithm.

[0148] When a multi-table join operation involves two joined data tables, if both tables contain a redistributed index such that the joined column contains the index column of that redistributed index, and the two redistributed indexes belong to the same distribution group, then the scans of both tables will be changed to scans of the corresponding redistributed indexes, and the data exchange operator in the original execution plan will be eliminated. This changes the execution plan to require only the storage / compute nodes in the storage / compute layer to perform single-machine joins within themselves. All redistributed indexes in the same distribution group have the same data distribution, so that data with the same index column resides in the same database node in the distributed database.

[0149] If one of the tables involved in the join contains a redistributed index such that the join column contains the index column of the redistributed index, then the scan of this table will be changed to a scan of the redistributed index, and the cross-node data exchange operator corresponding to this table in the original execution plan will be eliminated. Thus, the execution plan will be changed from a distributed join that requires data exchange between two tables to a distributed join that only requires data exchange between one table.

[0150] If neither of the two tables involved in the join has a related redistributed index, the execution plan for the distributed join is generated according to the traditional algorithm.

[0151] At the scheduling layer, since the redistributed index value is a hash value with an integer range, this range can be divided into multiple adjacent ranges and evenly distributed across all database nodes. During distributed database scaling or hotspot scheduling, the range can be redefined according to appropriate mechanisms, and the same range division should be used for different redistributed indexes within the same distribution group.

[0152] In this embodiment of the application, the redistribution of data in the distributed HTAP database is implemented as an index. While maintaining the distribution of data tables by primary key to efficiently serve OLTP requests, support for data redistribution is achieved, enabling the redistributed index to remain consistent with the data tables and to use the same distribution optimization for OLAP requests, greatly reducing inefficient cross-node data exchange, thereby significantly improving the processing performance of OLAP queries.

[0153] Figure 4 This is a schematic diagram of the structure of a data processing device provided in an embodiment of this application, as shown below. Figure 4 As shown, the device includes:

[0154] The target data table determination module 301 is used to receive a data query request and determine the target data table corresponding to the data query request;

[0155] The target index determination module 302 is used to determine the target index corresponding to the target data table from multiple redistributed indexes; wherein, the data in the redistributed index is stored in a distributed database based on the index columns of the redistributed index;

[0156] Optimization module 303 is used to optimize the original execution plan and generate a target execution plan based on the target index when the operation for the data query request points to the index column of the target index; the operation for the data query request includes single-table aggregation operation and / or multi-table join operation;

[0157] The execution module 304 is used to run the target execution plan and obtain the query results corresponding to the data query request.

[0158] As an optional embodiment, the device further includes a redistribution index building module for...

[0159] Use at least one column from the data table to be queried as the index column;

[0160] Based on the index column, the redistributed index is established; wherein, the redistributed index contains all rows and all columns of the corresponding data table to be queried, the redistributed index includes multiple data buckets, and all data rows included in the data buckets have the same index value;

[0161] Determine the same distribution group to which the redistributed index belongs; the same distribution group includes multiple redistributed indexes with the same data distribution in their index columns.

[0162] As an optional embodiment, in this device, data buckets with the same index value in the same distribution group are stored in the same database node; the data buckets with the same index value in the same distribution group are migrated as a whole when data scheduling occurs.

[0163] As an optional embodiment, the operation for the data query request includes a single-table aggregation operation;

[0164] The optimization module is used to determine the aggregated data table participating in the single-table aggregation operation, and to determine the first index corresponding to the aggregated data table from the plurality of redistributed indexes;

[0165] If the grouping column specified by the single-table aggregation operation contains the index column of the first index, then the first index is scanned and the cross-node data exchange operator in the original execution plan is deleted.

[0166] As an optional embodiment, the operation for the data query request includes multi-table join operations;

[0167] The optimization module is used to determine at least two related data tables participating in the multi-table join operation, and to determine a second index corresponding to the related data table from the plurality of redistributed indexes;

[0168] If the join column specified by the multi-table join operation contains the index column of each second index, and each second index belongs to the same distribution group, then scan each second index and delete the cross-node data exchange operator in the original execution plan.

[0169] As an optional embodiment, the optimization module is also used for:

[0170] If there is at least one first associated data table that meets the preset conditions, and at least one second associated data table that does not meet the preset conditions, then the redistribution index corresponding to the first associated data table is scanned, the cross-node data exchange operator corresponding to the first associated data table is eliminated, and the second associated data table is scanned, while the cross-node data exchange operator corresponding to the second associated data table is retained.

[0171] The preset condition is that the associated data table has a corresponding redistributed index, and the associated column specified by the multi-table association operation includes the index column of the redistributed index corresponding to the associated data table.

[0172] As an optional embodiment, the device further includes:

[0173] The update module is used to maintain the multiple redistributed indexes unchanged when data scheduling occurs in the distributed database;

[0174] When data scheduling in the distributed database ends, the multiple redistributed indexes are updated.

[0175] The apparatus in this application embodiment can execute the method provided in this application embodiment, and the implementation principle is similar. The actions performed by each module in the apparatus of each embodiment of this application correspond to the steps in the method of each embodiment of this application. For detailed functional descriptions of each module of the apparatus, please refer to the descriptions in the corresponding methods shown above, which will not be repeated here.

[0176] This application provides an electronic device including a memory, a processor, and a computer program stored in the memory. The processor executes the computer program to implement the steps of the data processing method described above. Compared with related technologies, it can achieve the following: by pre-establishing a target index for a target data table and distributing the data in the target index according to its index columns in a distributed database, query optimization can be achieved when single-table aggregation operations and / or multi-table join operations for data query requests point to the index columns of the target index. By optimizing the original execution plan based on the target index, a target execution plan is obtained, reducing cross-node data exchange operations in the target execution plan, improving data processing efficiency, and enhancing the performance of the entire distributed database.

[0177] In one alternative embodiment, an electronic device is provided, such as Figure 5 As shown, Figure 5 The illustrated electronic device 4000 includes a processor 4001 and a memory 4003. The processor 4001 and the memory 4003 are connected, for example, via a bus 4002. Optionally, the electronic device 4000 may further include a transceiver 4004, which can be used for data interaction between the electronic device and other electronic devices, such as sending and / or receiving data. It should be noted that in practical applications, the transceiver 4004 is not limited to one type, and the structure of the electronic device 4000 does not constitute a limitation on the embodiments of this application.

[0178] Processor 4001 may be a CPU (Central Processing Unit), a general-purpose processor, a DSP (Digital Signal Processor), an ASIC (Application Specific Integrated Circuit), an FPGA (Field Programmable Gate Array), or other programmable logic devices, transistor logic devices, hardware components, or any combination thereof. It can implement or execute the various exemplary logic blocks, modules, and circuits described in conjunction with the disclosure of this application. Processor 4001 may also be a combination that implements computational functions, such as including one or more microprocessor combinations, a combination of a DSP and a microprocessor, etc.

[0179] Bus 4002 may include a pathway for transmitting information between the aforementioned components. Bus 4002 may be a PCI (Peripheral Component Interconnect) bus or an EISA (Extended Industry Standard Architecture) bus, etc. Bus 4002 can be divided into address bus, data bus, control bus, etc. For ease of representation, Figure 5 The bus is represented by a single thick line, but this does not mean that there is only one bus or one type of bus.

[0180] The memory 4003 may be ROM (Read Only Memory) or other types of static storage devices capable of storing static information and instructions, RAM (Random Access Memory) or other types of dynamic storage devices capable of storing information and instructions, or EEPROM (Electrically Erasable Programmable Read Only Memory), CD-ROM (Compact Disc Read Only Memory) or other optical disc storage, optical disc storage (including compressed optical discs, laser discs, optical discs, digital universal optical discs, Blu-ray discs, etc.), magnetic disk storage media, other magnetic storage devices, or any other medium capable of carrying or storing computer programs and capable of being read by a computer, without limitation herein.

[0181] The memory 4003 stores computer programs that execute embodiments of this application, and its execution is controlled by the processor 4001. The processor 4001 executes the computer programs stored in the memory 4003 to implement the steps shown in the foregoing method embodiments.

[0182] This application provides a computer-readable storage medium storing a computer program. When the computer program is executed by a processor, it can implement the steps and corresponding content of the aforementioned method embodiments.

[0183] The terms "first," "second," "third," "fourth," "1," "2," etc. (if present) in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this application described herein can be implemented in a sequence other than that shown in the figures or text.

[0184] It should be understood that although arrows indicate various operation steps in the flowcharts of this application's embodiments, the order in which these steps are implemented is not limited to the order indicated by the arrows. Unless explicitly stated herein, in some implementation scenarios of this application's embodiments, the implementation steps in each flowchart can be executed in other orders as required. Furthermore, some or all steps in each flowchart may include multiple sub-steps or multiple stages based on the actual implementation scenario. Some or all of these sub-steps or stages can be executed at the same time, and each sub-step or stage can also be executed at different times. In scenarios where execution times differ, the execution order of these sub-steps or stages can be flexibly configured according to requirements, and this application's embodiments do not limit this.

[0185] The above description is only an optional implementation method for some implementation scenarios of this application. It should be noted that for those skilled in the art, other similar implementation methods based on the technical concept of this application without departing from the technical concept of this application also fall within the protection scope of the embodiments of this application.

Claims

1. A data processing method, characterized in that, include: Receive a data query request and determine the target data table corresponding to the data query request; The target index corresponding to the target data table is determined from multiple redistributed indexes; wherein the data in the redistributed index is stored in a distributed database based on the index columns of the redistributed index. When the operation for the data query request points to the index column of the target index, the original execution plan is optimized based on the target index to generate the target execution plan; Operations for the data query request include single-table aggregation operations and / or multi-table join operations; Run the target execution plan to obtain the query results corresponding to the data query request; The redistribution index is established based on the following method: Use at least one column from the data table to be queried as the index column; the data table to be queried includes the target data table. Based on the distribution of each index value in the index column, the data of each row in the data to be queried is re-sorted according to its corresponding index value, and the sorted data table is used as the redistribution index of the data table to be queried. The redistributed index contains all rows and columns of the corresponding data table to be queried. The redistributed index includes multiple data buckets, and all data rows in each data bucket have the same index value.

2. The data processing method according to claim 1, characterized in that, The method further includes: Determine the same distribution group to which the redistributed index belongs; the same distribution group includes multiple redistributed indexes with the same data distribution in their index columns.

3. The data processing method according to claim 2, characterized in that, Data buckets with the same index value in the same distribution group are stored on the same database node; data buckets with the same index value in the same distribution group are migrated as a whole when data scheduling occurs.

4. The data processing method according to claim 1, characterized in that, The operations for the data query request include single-table aggregation operations; the target data table is the aggregated data table participating in the single-table aggregation operation, and the target index is a first index determined from the plurality of redistributed indexes that corresponds to the aggregated data table; The optimization of the original execution plan based on the target index includes: If the grouping column specified by the single-table aggregation operation contains the index column of the first index, then the first index is scanned and the cross-node data exchange operator in the original execution plan is deleted.

5. The data processing method according to claim 1, characterized in that, The operations for the data query request include multi-table join operations; the target data tables are at least two join data tables participating in the multi-table join operation; the target index is a second index determined from the plurality of redistributed indexes that corresponds to the join data tables; The optimization of the original execution plan based on the target index includes: If the join column specified by the multi-table join operation contains the index column of each second index, and each second index belongs to the same distribution group, then scan each second index and delete the cross-node data exchange operator in the original execution plan.

6. The data processing method according to claim 5, characterized in that, The method further includes: If there is at least one first associated data table that meets the preset conditions, and at least one second associated data table that does not meet the preset conditions, then the redistribution index corresponding to the first associated data table is scanned, the cross-node data exchange operator corresponding to the first associated data table is eliminated, and the second associated data table is scanned, while the cross-node data exchange operator corresponding to the second associated data table is retained. The preset condition is that the associated data table has a corresponding redistributed index, and the associated column specified by the multi-table association operation includes the index column of the redistributed index corresponding to the associated data table.

7. The data processing method according to any one of claims 1 to 6, characterized in that, The method further includes: When data scheduling occurs in the distributed database, the multiple redistributed indexes remain unchanged; When data scheduling in the distributed database ends, the multiple redistributed indexes are updated.

8. A data processing apparatus, characterized in that, include: The target data table determination module is used to receive a data query request and determine the target data table corresponding to the data query request. The target index determination module is used to determine the target index corresponding to the target data table from multiple redistributed indexes; wherein the data in the redistributed index is stored in a distributed database based on the index columns of the redistributed index; The optimization module is used to optimize the original execution plan and generate a target execution plan based on the target index when the operation for the data query request points to the index column of the target index; the operation for the data query request includes single-table aggregation operation and / or multi-table join operation; The execution module is used to run the target execution plan and obtain the query results corresponding to the data query request; The redistribution index is established based on the following method: Use at least one column from the data table to be queried as the index column; the data table to be queried includes the target data table. Based on the distribution of each index value in the index column, the data of each row in the data to be queried is re-sorted according to its corresponding index value, and the sorted data table is used as the redistribution index of the data table to be queried. The redistributed index contains all rows and columns of the corresponding data table to be queried. The redistributed index includes multiple data buckets, and all data rows in each data bucket have the same index value.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory, characterized in that, The processor executes the computer program to implement the steps of the method according to any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 7.

Images