Database query processing method, storage medium and computer device

Abstract

The application provides a database query processing method, a storage medium and a computer device, and the processing method comprises the following steps: obtaining a query statement, and performing analysis on the query statement to obtain a query tree of the query statement; performing traversal on tables involved in the query tree to obtain a pre-generated aggregation index on the tables; replacing a matching sub-tree on the query tree, which is matched with a query sub-tree, with the query sub-tree to obtain an optimized query tree; and generating an execution plan of the query statement according to the optimized query tree. When a statistical query is processed, the query sub-tree is used to perform the query according to the scheme of the application, so that the data table does not need to be scanned and complex aggregation operations are not needed, thereby greatly shortening the query execution time of the query statement and reducing the occupation of hardware resources.

Description

Technical Field

[0001] This invention relates to database technology, and in particular to a database query processing method, storage medium, and computer device. Background Technology

[0002] Analytical database applications typically contain a large number of statistical queries. Processing these queries is time-consuming and requires significant hardware resources. These queries utilize numerous aggregation operations to generate statistical information, such as grouping, deduplication, summation, and counting.

[0003] To improve the processing efficiency of these statistical queries, existing database technologies have proposed several solutions, such as materialized views, columnar storage, and calculating approximate values ​​using sampled statistical information. However, these technologies all have certain limitations. For example, materialized views cannot reflect data updates in real time and require refreshing to reflect data changes, which also consumes a lot of resources. Columnar storage requires importing all data for a column to obtain statistics, which is inefficient. Sampling statistics can only provide approximate values ​​and are not precise enough.

[0004] To address the aforementioned issues, an ideal approach would be to store frequently used and time-consuming statistical data for direct use in future queries. However, in practice, due to the diversity and complexity of statistical methods, as well as limitations in update efficiency, existing solutions have certain limitations, and current technologies lack solutions that overcome these limitations. Summary of the Invention

[0005] One objective of this invention is to improve the efficiency of processing statistical query statements.

[0006] A further objective of this invention is to reduce the hardware resource consumption of processing statistical query statements.

[0007] Specifically, the present invention provides a database query processing method, comprising:

[0008] Obtain the query statement and parse the query result to obtain the query tree of the query statement;

[0009] The tables involved in the query tree are traversed to obtain the pre-generated aggregate indexes on the tables involved;

[0010] The query tree is optimized by replacing the matching subtree on the query tree with the query subtree of the aggregated index that matches the query subtree.

[0011] Execution plans for query statements are generated based on the optimized query tree.

[0012] Optionally, the steps of replacing the matching subtree on the query tree that matches the query subtree with the query subtree of the aggregated index include:

[0013] Compile all aggregated indexes obtained from the traversal into aggregated index subtrees;

[0014] Search the query tree for matching subtrees that match the aggregated index subtree;

[0015] Replace the matching subtree with the aggregated index subtree.

[0016] Optionally, the steps of querying the query tree to find the matching subtree that matches the aggregated index subtree include:

[0017] Traverse the query tree again to find subtrees that are identical to the query subtree, as well as subtrees whose subqueries contain query conditions of aggregated index subtrees, and use them as matching subtrees.

[0018] Optionally, the steps of generating an execution plan for a query statement based on the optimized query tree include:

[0019] The database optimizer evaluates the cost of optimizing the query tree;

[0020] An execution plan is selected from the optimized query tree based on the cost evaluation results.

[0021] Optionally, after the step of generating an execution plan for the query statement based on the optimized query tree, the method further includes:

[0022] The database executor executes the execution plan. During the execution process, while processing the aggregated index subtree, it retrieves the index key values ​​and their aggregated data that meet the conditions from the corresponding aggregated index.

[0023] Optionally, after the step of retrieving the index key values ​​and their aggregated data that meet the conditions from the corresponding aggregated index, the process may further include:

[0024] Determine whether the aggregated data is directly the desired result data;

[0025] If not, perform a second calculation on the aggregated data to obtain the final data.

[0026] Optionally, after traversing the tables involved in the query tree, if none of the tables involved have a clustered index, the database optimizer directly formulates an execution plan based on the query tree.

[0027] Optionally, the above method further includes:

[0028] Obtain the data operation that triggers the update of the aggregated index of the database, and modify the data in the target data table of the database according to the data operation;

[0029] Read the predefined statistical formula of the aggregated index;

[0030] The modified data is statistically calculated using the aforementioned statistical formula to obtain statistical information.

[0031] The aggregated data of the aggregated index is updated using the statistical information.

[0032] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor executes the machine-executable program to implement the processing method for database querying as described above.

[0033] The database query processing method of the present invention uses aggregated indexes for statistical queries, eliminating the need to scan the data table and perform complex aggregation operations. Instead, it simply performs a secondary operation on the statistical information stored in the index and returns it, thereby greatly shortening the query execution time and reducing the consumption of hardware resources.

[0034] Furthermore, the database query processing method of the present invention uses the data of the aggregated index to complete the query, and no longer scans the original data table. Therefore, the execution time is not significantly affected by the increase in data volume. The larger the amount of data in the database, the more query processing time can be saved.

[0035] Furthermore, in the database query processing method of the present invention, the aggregated index has the following characteristics: index values ​​are not duplicated, the index contains statistical information specified when the index is defined, the index is a sorted index, and the index can be updated in real time when data changes. The data returned by the aggregated index is accurate and efficient.

[0036] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0037] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings:

[0038] Figure 1 This is a schematic diagram of the data structure of the aggregated index for a database query processing method according to an embodiment of the present invention;

[0039] Figure 2 This is a schematic diagram of the data of a node in an aggregated index for a database query processing method according to an embodiment of the present invention;

[0040] Figure 3 This is a schematic diagram of the process of updating a clustered index in a database query processing method according to an embodiment of the present invention;

[0041] Figure 4 This is a schematic diagram of a database query processing method according to an embodiment of the present invention;

[0042] Figure 5 This is a schematic diagram illustrating the generation of an execution plan in a database query processing method according to an embodiment of the present invention;

[0043] Figure 6 This is a data instance that performs a query using a clustered index in a database query processing method according to an embodiment of the present invention;

[0044] Figure 7 This is a comparison chart of the execution time before and after applying a database query processing method according to an embodiment of the present invention;

[0045] Figure 8 This is a schematic diagram of a machine-readable storage medium according to an embodiment of the present invention; and

[0046] Figure 9 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0047] The database query processing method in this embodiment uses a novel index, named a clustered index. The purpose of a clustered index is to store statistical information of data in the form of an index, avoiding the problem of high processing time and resource consumption that occurs in existing technologies when processing statistical queries by generating statistical information using aggregation operations.

[0048] Clustered indexes can leverage the ordered nature of B-tree indexes or other indexing techniques in a database to speed up information retrieval, and ensure the real-time nature of statistical information by utilizing the characteristic that the index updates accordingly as the data is updated. Clustered indexes can also guarantee data visibility by only committing index data updates when transactions are committed, and can even utilize index partitioning to accelerate access to statistical information.

[0049] Figure 1 This is a schematic diagram of the data structure of the aggregated index for a database query processing method according to an embodiment of the present invention. Figure 2 This is a schematic diagram of the data of a node in a clustered index for a database query processing method according to an embodiment of the present invention.

[0050] Figure 1Using a B-tree structure as an example, the index portion 110 of the aggregated index is constructed according to the index key values, where 4, 32, 64, etc. are example index key values, which can be data IDs. Non-leaf nodes contain pointers to the next level nodes. The data portion 120 of the aggregated index stores the location of the data corresponding to the index key value, that is, a pointer to the data.

[0051] In addition to the data location, the data section 120 also includes statistical information values, such as... Figure 2 As shown, the clustered index has undergone deduplication, with the same index key value 44 corresponding to three data entries. It also stores statistical information about the data.

[0052] The data structure of a clustered index is an improvement on the database index structure (such as the B-tree structure). It retains the existing index structure while adding new features.

[0053] The method in this embodiment addresses aggregated indexes by adding a list field to the index's data structure to store statistical information. Each time data is updated, this statistical information is recalculated based on the updated data pointers specified in the index.

[0054] The length and content of the statistics field are defined when the index is created. For example, if the statistics for the id column are specified as `select id, sum(val) from t group by id`, then every time the data with id=1 is updated, the sum(val) value corresponding to id=1 will be recalculated and then updated in the index file.

[0055] The data stored in a clustered index can be configured to be either deduplicated values ​​or not. In other words, in a clustered index, each data row can have its own index node, or all data rows with the same index key value can share a single index node.

[0056] For aggregation operations that need to be saved, such as grouping by or deduplication by distinct, the aggregated index can retain only the data after removing duplicate values. For example, if two rows of data are inserted into the table table(id int) and both have the value 1 in the id column, the aggregated index can retain only one key value. The pointers to the data corresponding to the key value are an array, which contains pointers to the two rows of data. This is equivalent to the original BTREE data storage format having two indexes: (key=1, value=(1)) and (key=1, value=(1)). In the clustered index, the data storage format is (key=1, value=((1),(1)), aggval=()). The value in the parentheses of aggval=() is the statistical information value that needs to be calculated. In this way, the time for grouping and deduplication operations can be saved when querying data.

[0057] As can be seen from the above description, a clustered index, based on the index data structure, further adds the corresponding data positions (generally the data row positions corresponding to the index key value) and statistical information values. That is, two new linked lists are added: a linked list storing all data row positions corresponding to the index key value, and a linked list storing all statistical values ​​corresponding to the index key value.

[0058] The database query processing method in this embodiment targets the generation process of aggregated indexes, which includes: obtaining the database index creation statement; performing semantic analysis on the creation statement to obtain the indexed relationships and statistical formulas; creating an aggregated index; performing statistics on the indexed relationships according to the statistical formulas to obtain statistical information; and storing the statistical information as aggregated data in the aggregated index.

[0059] The create statement can specify the index type and index data structure (e.g., B-tree or hash structure). One optional syntax for the create statement is:

[0060] CREATE[UNIQUE]INDEX[CONCURRENTLY]name ON table[USING method]

[0061] In this context, CREATE is the creation command; [UNIQUE] indicates that each index key value corresponds to a unique data record and is an optional setting; [CONCURRENTLY] is also an optional setting, indicating that DML (delete, modify, insert) operations are not blocked during index creation; and [USING method] is the index type. The aggregated index generated in this embodiment can be defined as AGGREGATE.

[0062] For example, the creation statement 1: `CREATE INDEX idx1 ON t using AGGREGATE(select id, max(score) from t group by id)` creates a clustered index on the `id` column of table `t`, storing the `max(score)` value corresponding to that `id` in the clustered index.

[0063] For example, consider statement 2: `CREATE INDEX idx1 ON t using AGGREGATE(select country, province, sum(num), max(score) from t group by country, province)`. Its function is to create a clustered index on the `country` and `province` columns of table `t`, storing the `sum(num)` and `max(score)` values ​​corresponding to `country` and `province` in the clustered index.

[0064] The indexed relation obtained through semantic analysis of the creation statement can be a single table in the database, or it can be a subquery that joins multiple tables. In other words, a clustered index can be created on a single table in the database, or it can be created on a subquery that joins multiple tables. This is also an important difference from existing indexes.

[0065] For example, in the above creation statement 1, the indexed relationship is the id column of table t, and the statistical formula (statistical function) is max(score); in the above creation statement 2, the indexed relationship is the country and province columns of table t, and there are two statistical formulas (statistical functions): sum(num) and max(score).

[0066] When the database parser performs syntactic and semantic analysis on the creation statement, if it determines that the index type is aggregate, it invokes the aggregate index creation process to initiate the creation of the aggregate index. For example, the statement `CREATE INDEX idx1 ONt using AGGREGATE(select id, max(score) from t group by id)`, during syntax analysis, determines that an aggregate (AGGREGATE) index is created on the `id` column of table `t`, and the aggregate information stored in the index's key value is `max(score)`.

[0067] The steps for creating a clustered index may include: creating an index table, which can use an ordered index structure such as a B-tree; and recording statistical formulas and the correspondence between the index table and the indexed relation in the database's system tables. In other words, the process of creating a clustered index first involves creating a table named `t_index`, then recording the correspondence between the indexed relation `t` and the index table `t_index` in the database's system tables. The system tables also record other index information, such as the `aggregate` type, the corresponding key value `id`, and the corresponding statistical calculation formula.

[0068] The steps for recording statistical formulas in the system tables of a database can include: saving the statistical formula as a creation statement; or compiling the statistical formula to obtain executable code corresponding to the statistical formula, and then saving the executable code. That is, when saving a statistical formula, you can save the SQL form of the calculation formula, such as max(score) or sum(num), or you can use compilation techniques (such as JIT) to compile the statistical formula into executable code to speed up the compilation process when modifying indexes.

[0069] Before recording statistical formulas in the system tables of the database, it is possible to determine whether the statistical formulas have corresponding transformation rules; if so, the statistical formulas are transformed according to the corresponding transformation rules; and the steps of recording statistical formulas in the system tables of the database include: recording the transformed statistical formulas in the system tables of the database.

[0070] Different statistical formulas have different transformation requirements. Some statistical formulas do not require transformation, while others require transformation according to defined rules to avoid rescanning the entire table and recalculating every time data is updated. For example, for statement 1, when calculating max(score), only max(score) needs to be stored. This way, it only needs to be updated when the value inserted into table t is greater than the current value, and only needs to be recalculated when the deleted value is the maximum value. Another example is when calculating the average avg(score), because average avg = total / number = sum / count, the information for the sum(score) and count aggregate functions needs to be stored.

[0071] Since the values ​​of the statistics are a set of integers or floating-point numbers, the length and space occupied by the statistics list can be determined when defining a clustered index. Therefore, after creating the index table, the steps can include: determining the data length of the statistics; and allocating space for the index table according to the data length.

[0072] For example, when defining an index, the constraint is `select id, sum(val), max(score) from t group by id`. If `val` is of type `int` and `score` is of type `float`, then for the aggregate index on column `id`, the additional space required for each key value should be `sizeof(int) + sizeof(float)`, which is the size of the `int` type and the size of the `float` type.

[0073] The statistical process of performing statistics on the indexed relation according to the statistical formula can be carried out by reading the corresponding columns of the indexed relation (data table t) according to the statistical formula.

[0074] Statistical information is stored as aggregated data in the aggregated index, that is, it is stored in the linked list corresponding to the key value of the aggregated index for storing statistical information. The stored statistical information is called the aggregated data of the aggregated index.

[0075] The above method, after obtaining the database index creation statement, creates a clustered index and uses it to pre-calculate and store the results of frequently used statistical queries, facilitating quick and easy data provision during subsequent statistical queries. On one hand, the ordered nature of the index improves the processing efficiency of statistical queries; on the other hand, the clustered index updates accordingly as the data changes, ensuring data real-time performance. The aggregated data of the clustered index is derived by statistically analyzing all relevant data from the indexed relationship (which can be a single table or the result table of a subquery), ensuring the accuracy of the statistical data.

[0076] Statistical information is stored in the data structure of the clustered index. That is, for each index key value, additional space is allocated in the data structure of the clustered index to store statistical information.

[0077] Clustered indexes have the following characteristics: index key values ​​are unique, clustered indexes contain flexibly defined statistical information, the index is a sorted index, and it is updated in real time when data changes.

[0078] Alternatively, the statistical information can be stored in a separate file to form a new data file. The index file only stores the position of the corresponding statistical information within the file. Assume an index file has a key value A, and its corresponding data is located at position B in the data file. Traditional index files store the correspondence between A and B (A->B). The method in this embodiment can add a statistical file C. The aggregated index file stores (A->(B,C)). Changes in data file B will trigger modifications to index file A, and simultaneously trigger modifications to statistical file C. Apart from the above triggering conditions, statistical file C will not be proactively updated.

[0079] Figure 3This is a schematic diagram illustrating the process of updating a clustered index in a database query processing method according to an embodiment of the present invention. The update method includes:

[0080] Step S302: Obtain the request to update data; when a DML (INSERT, UPDATE, DELETE) operation occurs on the data table associated with the aggregated index, the aggregated index will be updated simultaneously.

[0081] Step S304: Perform a data update. This means modifying the data in the database table.

[0082] Step S306: Perform a clustered index update. For each row of data that has been modified, obtain all indexes corresponding to that table from the system table and modify them sequentially.

[0083] For a BTREE structured aggregated index, first, the index key value is queried according to the BTREE index method, and then the index is updated.

[0084] When processing the aggregated index in step S306, the index key value is first queried in the manner of a BTREE index.

[0085] For inserted data, locate the node position of the index key value in the BTREE. If the index key value already exists in the BTREE node and the aggregated index requires unique keys, allocate space in the linked list corresponding to the data position of the index key value to store the current record's position. Then calculate the statistics corresponding to that key value. If the index key value does not exist, allocate a new index key value, store the data corresponding to that index key value, and then recalculate the statistics for that index key value.

[0086] To delete data, first update the data location linked list and delete the data row corresponding to the index key value. If the linked list is empty after deletion, delete the key value of that index key value; otherwise, recalculate the statistical information value after deletion.

[0087] When modifying aggregated data, the specific process may include: reading the predefined statistical formulas of the aggregated index; using the statistical formulas to perform statistical calculations on the modified data to obtain statistical information; and using the statistical information to update the aggregated data of the aggregated index. First, all statistical formulas defined on the aggregated index are read from the database system tables. Then, the set of statistical formulas is traversed, and statistical calculations are performed on each value. During statistical calculations, it is necessary to determine whether the statistical data needs to be recalculated based on different statistical formulas. For some specific statistical formulas, such as max (maximum value), min (minimum value), and count (count), it is only necessary to perform the calculation between the current row's data value and the saved original aggregated data. For other statistical formulas, all data rows corresponding to the index key value can be traversed, and all data corresponding to the index value can be retrieved sequentially to recalculate its aggregated data.

[0088] In database mechanisms, when data is updated, the index information is also updated simultaneously. When the index information is updated, the statistical information within the index information is also updated.

[0089] For example, for a clustered index: `CREATE INDEX idx1 ON t using AGGREGATE(select id, max(score) from t group by id)`. When executing the data operation `update t set score=100 where id=1 and val<10`, the update operation of the clustered index is as follows: find all data rows that satisfy `id=1` through the `id=1` index; for each row, check if `val<10`, and update the data for records that meet the condition; calculate `max(score)` for each row; and update the aggregated data of the clustered index with the final `max(score)` result.

[0090] When executing a statistical query, the statistical query statement goes through three stages: parsing, optimization, and execution, and finally returns the query results. Figure 4 This is a schematic diagram of a database query processing method according to an embodiment of the present invention. The database query processing method includes:

[0091] Step S402: Obtain the query statement and parse the query statement to obtain the query tree of the query statement;

[0092] Step S404: Traverse the tables involved in the query tree to obtain the pre-generated aggregate indexes on the tables involved;

[0093] Step S406: Replace the matching subtree on the query tree that matches the query subtree with the query subtree of the aggregated index, thereby obtaining the optimized query tree;

[0094] Step S408: Generate an execution plan for the query statement based on the optimized query tree.

[0095] Step S406, the matching process, can specifically include: compiling all the aggregated indexes obtained from the traversal into aggregated index subtrees; querying the query tree for matching subtrees that match the aggregated index subtrees; and replacing the matching subtrees with the aggregated index subtrees. The process of querying the query tree for matching subtrees that match the aggregated index subtrees can include: traversing the query tree again to find subtrees identical to the query subtrees and subtrees whose subqueries contain the query conditions of the aggregated index subtrees, using these as matching subtrees.

[0096] If, after traversing the tables involved in the query tree, none of the tables have a clustered index, the database optimizer directly formulates an execution plan based on the query tree. In other words, if the clustered index cannot provide the data required for the statistical query, the optimizer processes the statistical query according to the existing processing procedure.

[0097] Step S408, generating the execution plan for the query statement, may include: the database optimizer evaluating the cost of the optimized query tree; and selecting an execution plan from the optimized query tree based on the cost evaluation results. After generating the execution plan, the database executor can execute it. During execution, while processing the aggregated index subtree, the database executor retrieves the index key values ​​and their aggregated data that meet the conditions from the corresponding aggregated index. Then, it is determined whether the aggregated data directly corresponds to the required result data. If the aggregated data directly corresponds to the required result data, it is provided directly. If the aggregated data does not correspond to the required result data, secondary calculations can be performed on the aggregated data to obtain the result data.

[0098] Figure 5 This is a schematic diagram illustrating the generation of an execution plan in a database query processing method according to an embodiment of the present invention. The steps for generating the execution plan include:

[0099] Step S502: Obtain and parse the statistical query;

[0100] Step S504: Determine whether a clustered index exists on the columns of the statistical query;

[0101] Step S506: The database optimizer directly formulates the execution plan.

[0102] Step S510: Generate an execution plan using the aggregated index;

[0103] Step S512: Perform a search on the aggregated index;

[0104] Step S514: Perform a secondary calculation on the search results.

[0105] When executing a statistical query using a clustered index, the SQL statement goes through three stages: parsing, optimization, and execution, ultimately returning the query result. Step S502 is the parsing process, the optimizer completes steps S504 and S610, and the executor completes steps S512 and S514.

[0106] During the optimizer phase, if some statistical queries meet the query conditions of the aggregated index, an execution plan for the aggregated index will be generated for that subquery.

[0107] The execution plan for the above query statement using existing technology is as follows:

[0108] NestLoop Join(a.id=b.id)

[0109] SeqScan on a

[0110] HashAggregate(b.id)

[0111] SeqScan on b

[0112] In other words, in the execution plan of the existing technology, for each record in table a, a scan and aggregation calculation must be performed on table b. When the data in table b is very large, this query operation will be very time-consuming.

[0113] When using the aggregated index implemented in this embodiment, the following statement is used to create the aggregated index: CREATE INDEX idx1 ON busing AGGREGATE(select id, sum(score) from b group by id).

[0114] When the optimizer generates an execution plan using a clustered index, it determines that the `id` column in table `b` has a clustered index. Furthermore, since all clustered query operations exist within the pre-formed aggregated data of the clustered index, it will generate a query plan for table `b` based on the clustered index. This execution plan might be as follows:

[0115] NestLoop Join(a.id=b.id)

[0116] SeqScan on a

[0117] AggIndexScan on b(b.id)

[0118] In other words, for each record in table a, an index scan is performed on table b. Furthermore, since the results of the statistical query are already stored in the aggregated data of the aggregated index, the results of the aggregated data can be returned directly.

[0119] As can be seen from the above description, applying the method of this embodiment can greatly improve the processing efficiency of aggregate queries.

[0120] When processing a statistical query request, the database optimizer determines whether the statistical query involves columns containing aggregate indexes. If the aggregate index meets the conditions for aggregate operations, it generates an execution plan that uses the aggregate index for the query.

[0121] When executing a clustered index, the executor queries the index to obtain statistics, then uses these statistics to perform secondary calculations and returns the final result. Using a clustered index eliminates the need to scan the entire table and perform complex aggregation operations; instead, it simply performs secondary calculations on the statistics stored in the index and returns the result. Therefore, SQL execution time and hardware resource consumption are significantly reduced.

[0122] The optimizer's functionality is divided into rule-based optimization and cost-based optimization. Since using clustered indexes always brings performance improvements, it falls under rule-based optimization. This rule applies to all databases; the following describes its technical implementation process using the KES database as an example:

[0123] The database optimizer takes as input a query tree (Query1) transformed from an SQL string (an internal tree-like data structure) and outputs the final selected execution plan. The main task of logical optimization is to transform the query tree according to rules. When applying aggregated index transformation rules, it first traverses all tables involved in the query tree corresponding to the subquery, determining whether each table has a aggregated index. All found aggregated indexes are then returned as a set.

[0124] Then iterate through the set of aggregated indexes obtained in the previous step. For each aggregated index in the set, compile its definition into a query tree.

[0125] For example, the following indexes are defined:

[0126] CREATE INDEX idx1 ON b using AGGREGATE(select id,sum(score)from bgroup by id)

[0127] The SQL query `select id, sum(score) from b group by id` is parsed again to form a new query tree `Query2`. The tree structure `Query1` is traversed to find if there is a subtree that is the same as `Query2`. If it exists, the subtree is turned into a node of the aggregated index `AggIndexScan`.

[0128] When matching child nodes of Query1 with Query2, a fuzzy matching algorithm is needed. The child nodes don't need to be exactly the same as Query2; only that the subquery's condition includes Query2. For example, the SQL statement `(select id, sum(score) from b group by id where id=2)` can match the aggregated index definition `(select id, sum(score) from b group by id)`. Another example is the SQL statement `(select id, sum(score) from b group by id where id=2)`, which also matches the aggregated index definition `(select id, name, sum(score) from b group by id, name)`. In these cases, the executor first needs to read the statistical values ​​from the aggregated index and then perform simple secondary calculations on the statistical values.

[0129] After rule-based optimization is completed, the optimizer runs cost-based optimization to select the final execution plan. At this point, for the AggIndexScan node, the optimizer directly generates the execution plan for AggIndexScan.

[0130] During the executor phase, when executing the AggIndexScan execution plan, the index file is read into shared memory. The B-tree index is searched according to the query conditions to find all index nodes that meet the full set of query conditions. Then, the index values ​​and corresponding statistics of these nodes are returned sequentially. For example, it retrieves and returns each id from table b that meets the filtering conditions and its corresponding sum(score) value.

[0131] In SQL queries, even if some query statements do not exactly match the definition of a clustered index, but can be recalculated based on the information stored in the clustered index, the clustered index can still be used.

[0132] Using the above clustered index as an example:

[0133] Example 1: The following query: Select id, sum(score)*10as n from b group by id having n<10 can perform a simple secondary calculation on the query return value of the aggregated index and then quickly return the result.

[0134] Example 2: The following query: select distinct id from b can directly return the id value using the aggregate index, saving the time of the distinct deduplication operation.

[0135] In the above process, when the database optimizer processes an aggregation query request, it determines whether the aggregation operation is performed on the column containing the aggregation index, and if the aggregation index meets the conditions for aggregation operation. If so, it generates an execution plan that uses the aggregation index for the query. When the executor executes the aggregation index, it queries the index to obtain statistical information, and then uses this statistical information to perform secondary calculations and return the final result.

[0136] Using a clustered index for queries eliminates the need to scan the data table and perform complex aggregation operations. Instead, it simply performs a secondary calculation on the statistical information stored in the index and returns the result. Therefore, the execution time of the SQL query and the consumption of hardware resources are greatly reduced.

[0137] Figure 6 This refers to a data instance used in a database query processing method according to an embodiment of the present invention to perform a query using a clustered index. Figure 6 The data table on the left is the original data table. Figure 6 In the data processing diagram on the right, the data within the dashed box 60 is the data accessed when using the aggregated index, and the data within the dashed box 61 is the data accessed when not using the aggregated index.

[0138] Figure 6 The statement for creating a clustered index is:

[0139] CREATE index test_idx on students(id)as(select id,sum(score)fromstudents group by id);

[0140] When processing the query `Select id, sum(score) from students group by id`, without using a clustered index, it's necessary to scan all the data in the table, group each row, and perform aggregation operations. Only after all data has been scanned can the results be returned to the client. On a table with tens of millions of records, this operation could take several minutes.

[0141] With the use of a clustered index, the executor can immediately locate the first record to be returned, "32(1)", and then return the result to the client. Then it reads the next record, "33(1)", and returns the results in sequence. The execution time of this operation is a few milliseconds.

[0142] For some SQL queries, execution time increases with the amount of data in the database or the data distribution. However, queries using clustered indexes are less affected by the increase in data volume because they no longer scan the original data table.

[0143] In the Figure 6 When using the query statement: Select count(distinct id) from students, the select count distinct operation is an example of how the execution time of a query is affected by the data distribution.

[0144] Without using a clustered index, each record needs to be read to remove duplicate values. Whether using sorting or hashing to remove duplicates, it takes a long time. And when the data in the column has a low degree of duplication, the execution time will increase.

[0145] In contrast, with a clustered index, because the index values ​​are unique, you only need to traverse the leaf nodes of the index and add up the counts of all the leaf nodes to get the result.

[0146] Figure 7 This is a comparison chart of the execution time before and after the application of a database query processing method according to an embodiment of the present invention. Figure 7 In the figure, curve L1 is the execution time curve without using a clustered index, and curve L2 is the execution time curve with a clustered index. It can be seen that the execution time is not significantly affected by the data when using a clustered index.

[0147] As can be seen from the above description, using the aggregated index in this embodiment can greatly shorten the query execution time of the query statement, reduce the occupation of hardware resources, and improve execution efficiency.

[0148] This embodiment also provides a machine-readable storage medium and a computer device. Figure 8 This is a schematic diagram of a machine-readable storage medium 70 according to an embodiment of the present invention. Figure 9 This is a schematic diagram of a computer device 80 according to an embodiment of the present invention.

[0149] The machine-readable storage medium 70 stores a machine-executable program 71 thereon, which, when executed by the processor 810, implements the database query processing method of any of the above embodiments.

[0150] The computer device 80 may include a memory 820, a processor 810, and a machine-executable program 71 stored on the memory 820 and running on the processor 810. When the processor 810 executes the machine-executable program 71, it implements the database query processing method of any of the above embodiments.

[0151] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any machine-readable storage medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-based system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0152] For the purposes of this embodiment, the machine-readable storage medium 70 can be any means capable of containing, storing, communicating, propagating, or transmitting a program for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of machine-readable storage media include: an electrical connection (electronic device) having one or more wires, a portable computer disk drive (magnetic device), random access memory (RAM), read-only memory (ROM), erasable and editable read-only memory (EPROM or flash memory), fiber optic devices, and portable optical disc read-only memory (CDROM). Furthermore, the machine-readable storage medium 70 can even be paper or other suitable media on which the program can be printed, since the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting, or otherwise processing as necessary, and then stored in a computer memory.

[0153] It should be understood that various parts of the present invention can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0154] Computer device 80 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 80 can be a cloud computing node. Computer device 80 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 80 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can reside on local or remote computing system storage media, including storage devices.

[0155] Computer device 80 may include a processor 810 adapted to execute stored instructions and a memory 820 that provides temporary storage space for the operation of said instructions during operation. The processor 810 may be a single-core processor, a multi-core processor, a computing cluster, or any other configuration. The memory 820 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0156] The processor 810 can be connected via a system interconnect (e.g., PCI, PCI-Express, etc.) to an I / O interface (input / output interface) suitable for connecting the computer device 80 to one or more I / O devices (input / output devices). I / O devices may include, for example, a keyboard and indicating devices, where indicating devices may include a touchpad or touchscreen, etc. I / O devices may be built into the computer device 80 or may be external devices connected to the computing device.

[0157] The processor 810 can also be linked via a system interconnect to a display interface suitable for connecting the computer device 80 to a display device. The display device may include a display screen that is a built-in component of the computer device 80. The display device may also include an external computer monitor, television, or projector connected to the computer device 80. Furthermore, a network interface controller (NIC) may be adapted to connect the computer device 80 to a network via a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an Internet Minicomputer System Interface) to transmit data. The network may be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. Remote devices can connect to the computing device via the network.

[0158] The flowchart provided in this embodiment is not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.

[0159] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A database query processing method, comprising: Obtain the query statement and parse the query statement to obtain the query tree of the query statement; The tables involved in the query tree are traversed to obtain the pre-generated aggregate indexes on the tables involved; All the aggregated indexes obtained by traversal are compiled into aggregated index subtrees, and a matching subtree that matches the aggregated index subtree is queried on the query tree; The matching subtree is replaced with the aggregated index subtree to obtain an optimized query tree; An execution plan for the query statement is generated based on the optimized query tree.

2. The database query processing method according to claim 1, wherein, The step of querying the matching subtree that matches the aggregated index subtree on the query tree includes: The query tree is traversed again to find subtrees that are identical to the query subtree and subtrees whose subqueries contain the query conditions of the aggregated index subtree, which are then used as the matching subtrees.

3. The database query processing method according to claim 1, wherein, The steps for generating an execution plan for the query statement based on the optimized query tree include: The database optimizer performs a cost evaluation on the optimized query tree; The execution plan is selected from the optimized query tree based on the cost evaluation results.

4. The database query processing method according to claim 1, wherein, The step of generating an execution plan for the query statement based on the optimized query tree further includes: The execution plan is executed by the executor of the database. During the execution process, the aggregated index subtree is processed, and the index key values ​​and their aggregated data that meet the conditions are retrieved from the corresponding aggregated index.

5. The database query processing method according to claim 4, wherein, After retrieving the index key values ​​and their aggregated data that meet the criteria from the corresponding aggregated index, the process also includes: Determine whether the aggregated data is directly the desired result data; If not, perform a second calculation on the aggregated data to obtain the result data.

6. The database query processing method according to claim 1, wherein, After traversing the tables involved in the query tree, if none of the tables involved have a clustered index, the database optimizer directly formulates an execution plan based on the query tree.

7. The database query processing method according to claim 1, wherein, Also includes: Obtain the data operation that triggers the update of the aggregated index of the database, and modify the data in the target data table of the database according to the data operation; Read the predefined statistical formula of the aggregated index; The modified data is statistically calculated using the aforementioned statistical formula to obtain statistical information. The aggregated data of the aggregated index is updated using the statistical information.

8. A machine-readable storage medium having a machine-executable program stored thereon, wherein the machine-executable program, when executed by a processor, implements the database query processing method according to any one of claims 1 to 7.

9. A computer device comprising a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor, when executing the machine-executable program, implements a database query processing method according to any one of claims 1 to 7.

Images