Distributed database query optimization method and system based on hybrid index structure

Abstract

This invention discloses a distributed database query optimization method and system based on a hybrid index structure, relating to the field of distributed database technology. The method includes: receiving user query requests and performing syntax parsing and semantic preprocessing to generate a standard query syntax tree; matching heterogeneous candidate index combinations containing different types of indexes from the hybrid index structure; calculating the total execution cost of each combination using a three-layer hybrid cost model adapted to heterogeneous indexes; selecting the optimal execution plan with the minimum total cost; performing precise shard pruning and node routing during cross-shard joins; splitting and distributing shard sub-queries; performing multi-index collaborative filtering and pre-aggregation locally; and returning the globally merged results. The system consists of corresponding functional modules, realizing the full-process execution of the above query optimization method. Its advantages include: effectively improving the performance of complex multi-condition queries and cross-shard join queries; significantly reducing network transmission volume; balancing node load; and adapting to diverse business query scenarios.

Description

Technical Field

[0001] This invention relates to the field of distributed database technology, and specifically to a distributed database query optimization method and system based on a hybrid index structure. Background Technology

[0002] With the rapid development of big data technology, distributed databases have become the fundamental support for core businesses such as e-commerce transactions, financial risk control, and IoT data processing. The exponential growth in business data volume and the surge in demand for complex multi-condition queries and cross-shard join queries have placed higher demands on database query performance. Hybrid index structures, which integrate the advantages of multiple index types, have become a key technological path to improve the query efficiency of distributed databases.

[0003] Existing distributed database query optimization methods based on hybrid index structures do not fully consider the collaborative overhead and conflict costs of heterogeneous index combinations in their cost models. Cross-shard join queries lack precise shard pruning mechanisms, replica selection does not take into account real-time node load and network topology, and local pre-filtering and pre-aggregation capabilities are insufficient, resulting in high overall query latency and making it difficult to support high-concurrency business scenarios. Summary of the Invention

[0004] To address the aforementioned technical issues, this paper provides a distributed database query optimization method and system based on a hybrid index structure. This technical solution resolves the problems of existing distributed database query optimization methods based on hybrid index structures mentioned in the background section, which suffer from imperfect cost models, inaccurate sharding and pruning, unreasonable replica selection, and insufficient local processing capabilities.

[0005] To achieve the above objectives, the technical solution adopted by the present invention is as follows: The distributed database query optimization method based on hybrid index structure includes the following steps: Step 1: Receive user query requests, perform syntax parsing and semantic preprocessing on the query requests, and generate a standard query syntax tree; Step 2: Obtain the filtering conditions, multi-table join conditions, and sorting and grouping conditions from the standard query syntax tree, and match the heterogeneous candidate index combination set containing different types of indexes from the hybrid index structure; Step 3: By using a pre-defined hybrid index cost model that adapts to heterogeneous index combinations, obtain the total execution plan cost corresponding to each heterogeneous index combination, and select the heterogeneous index combination with the minimum total cost and its corresponding execution plan as the optimal execution plan. Step 4: When the query involves multi-table cross-shard joins, the global metadata index is used to complete the join join shard pruning and precise routing of the join nodes, determine the set of data shards to be accessed, and split the optimal execution plan into multiple shard subqueries, which are then sent to the corresponding data nodes for parallel execution. Step 5: Each data node uses its local sharded multi-type heterogeneous index to execute sharded subqueries, complete data filtering, association pre-filtering and pre-aggregation within the shard, generate local query results and return them; Step 6: Receive the local query results returned by each data node, perform global association aggregation, sorting, pagination and deduplication fusion processing, generate the final query results and return them to the user.

[0006] Preferably, the step of performing syntax parsing and semantic preprocessing on the query request to generate a standard query syntax tree specifically includes: The system obtains the raw SQL character stream from the received user query request and performs lexical recognition to obtain a token identifier sequence containing keywords, identifiers, and operators. Perform syntactic structure matching on the token identifier sequence to obtain an initial abstract syntax tree; Based on the initial abstract syntax tree, the database global metadata index is called to obtain the table name, field name, and corresponding data dictionary information; Based on the data field information, the initial abstract syntax tree is semantically validated to obtain a validated logical operator tree; According to the logical equivalence transformation rules, predicate rewriting and operator merging are performed on the logical operator tree to obtain the optimized logical expression flow; Further, the dependency relationships and output mode definitions of each operator are obtained, and the dependency relationships and output mode definitions are standardized and encapsulated to obtain a standard query syntax tree that can be recursively traversed.

[0007] Preferably, the process of matching a set of heterogeneous candidate index combinations containing different types of indexes includes the following steps: Obtain the root node of the standard query syntax tree and perform a depth-first traversal to obtain a predicate feature set covering all filtering predicates, join predicates, and ordering predicates; Based on the field identifiers in the predicate feature set, obtain the local shard index distribution table of the corresponding field from the global metadata index; Obtain a list of index types supported by each field from the local sharded index distribution table. The list of index types includes B+ tree index, bitmap index, and inverted index. Based on the filtering type in the predicate feature set, the list of index types is filtered for validity to obtain a set of available indexes that match the query semantics; Obtain the covering column information and physical storage location corresponding to each index, and calculate the coverage weight of each individual index for the query predicate based on the covering column information; The Cartesian product of multiple indices is combined using the coverage weights to obtain a preliminary index path combination sequence; Redundant paths are eliminated according to the preset heterogeneous hybrid rules, and a set of heterogeneous candidate index combinations composed of indexes with different physical structures is obtained.

[0008] Preferably, the process of obtaining the total execution plan cost corresponding to each heterogeneous index combination includes the following steps: Obtain a histogram of statistical information of the fields involved in each heterogeneous candidate index combination, and then obtain the selectivity of each predicate; Get the total number of rows in the table, and use the product of the total number of rows in the table and the selectivity as the estimated number of records to be returned by the query; Based on preset hardware resource parameters, the unit I / O cost coefficient and unit CPU processing cost coefficient are obtained by combining the estimated number of returned records. The index scan overhead is determined based on the unit I / O cost coefficient. The predicate filtering overhead is determined based on the estimated number of returned records and the unit CPU processing cost coefficient. Based on the index scan overhead and predicate filtering overhead, the single-point sharding execution cost is obtained; The data transmission cost of each node is determined based on the distributed network communication parameters, and then added to the single-point sharding execution cost to obtain the total execution plan cost. The formula for calculating the selectivity of the predicate is as follows: In the formula, For field values The probability density distribution function, , To filter the range of values ​​for the query predicate, , For field values The range of values ​​within the entire dataset; The formula for calculating the index scan overhead is as follows: In the formula, The fan-out degree of the index node. The total number of records stored in the index. The height of the index tree represents the number of node levels required for an index lookup. The cost factor per unit I / O; The formula for calculating the predicate filtering overhead is as follows: In the formula, To estimate the number of records to be returned, The processing cost per unit of CPU is the coefficient.

[0009] Preferably, the process of completing the Join association shard pruning through the global metadata index includes the following steps: Based on the multi-table join conditions in the standard query syntax tree, the join key fields of the left and right tables are obtained, and then the physical sharding distribution logical mapping of each table is obtained from the global metadata index. The constant boundary value of the filter operator based on the Join condition is obtained, and the corresponding hash slot interval is obtained according to the constant boundary value; Perform an intersection operation between the hash slot interval and the sharded storage graph to obtain a set of potential active shard identifiers; Retrieve the global index snapshot, obtain the shard mask vector containing non-empty result sets, and further obtain the overlap density of the association keys of each table under the cross-node distribution; By using the overlap density to remove logically isolated fragments that do not meet the association constraints, candidate pruning results are obtained, and a simplified execution path is further generated to complete the Join association fragment pruning.

[0010] Preferably, the process of determining the set of data shards to be accessed includes the following steps: Obtain the shard pruning results to obtain a list of physical node IP addresses for all surviving shards; The real-time load factor of each data node is obtained through a heartbeat monitoring protocol, and the estimated response latency of each node is calculated according to the following formula. : ,in The length of the queue of tasks to be processed. For node processing capacity, This is the delay factor; Based on the estimated response delay, the shortest transmission weight matrix in the network topology is obtained, and the available status bits of the primary and secondary replicas are further obtained. By using the available status bits and the estimated response latency, the access priority score of each data shard is determined, and the optimal physical location is selected to obtain a list of target nodes for precise routing. Based on the target node list and its corresponding shard range, obtain the set of data shards that need to be accessed.

[0011] Preferably, the process of performing a sharded subquery includes the following steps: Obtain the subquery plan sent to the data nodes, determine the sequence of operators to be executed locally on the shards, parse the local shard index, and obtain the bit vector of the bitmap index; Logical bitwise operations are performed using the bit vector to obtain the set of row offsets that satisfy the filtering predicate, and the leaf node pointers of the B+ tree index are obtained simultaneously. Based on the leaf node pointer, the physical starting address of the data block is obtained, and asynchronous parallel I / O reading is further performed to obtain multiple rows of original data records; The local inverted index is used to perform full-text fuzzy condition matching and filtering, and then the effective record line flow after the local data filtering is completed is obtained.

[0012] Preferably, the process of generating local query results includes the following steps: Based on the effective record row flow, obtain the list of dimension fields defined by the local pre-aggregation operator, and build a temporary hash mapping table in local memory; Obtain the metric value of the valid record row stream, determine the cumulative sum and count value of each hash slot, and calculate the local average value according to the following formula: ,in This is the cumulative sum across all hash slots. This represents the count value for each hash slot; Obtain the results from the hash mapping table to get a local aggregated record set that has been associated and pre-filtered; The local aggregated record set is optimized by heap sort according to the sorting and grouping conditions to obtain ordered intermediate data blocks, and then encapsulated by binary protocol to obtain a local query response packet containing fragment metadata. The local query response packet is compressed according to the network transmission caching strategy to generate local query results.

[0013] Preferably, the process of generating the final query result includes the following steps: Receive the local query results from all data nodes and obtain the global result merging buffer; Based on the global association aggregation rules, the mean data from different shards in the buffer are merged, and then a preliminary aggregated global data view is obtained based on the merged full metrics. Based on the user-defined uniqueness constraint field, the deduplicated record sequence is obtained, and the external sorting and merging of the deduplicated record sequence is performed based on the sorting and grouping conditions to obtain a candidate result set; Retrieve the pagination parameters specified by the user and determine the starting pointer of the target result page; The candidate result set is sliced ​​using the starting pointer to obtain the final record set. The final record set is then formatted and mapped to generate the final query result.

[0014] Furthermore, a distributed database query optimization system based on a hybrid index structure is proposed, which is applied to the query optimization method described in any of the preceding items, specifically including: The query parsing preprocessing module is used to receive user query requests, perform lexical analysis, syntax parsing and semantic verification on the raw SQL character stream, perform predicate rewriting and operator merging, and generate a standard query syntax tree; The heterogeneous index matching module is used to traverse the standard query syntax tree to extract the predicate feature set, match the available index types corresponding to each field, and generate and filter a set of heterogeneous candidate index combinations. The cost calculation and plan generation module is used to calculate the total cost of the execution plan for each heterogeneous index combination based on the hybrid index-specific cost model, and select the optimal execution plan with the minimum total cost. The distributed execution scheduling module is used to complete Join shard pruning and precise routing of associated nodes through global metadata index, determine the set of data shards to be accessed, split the optimal execution plan into multiple shard subqueries and distribute them to the corresponding data nodes for parallel execution; The local index execution module is used to call local sharded heterogeneous indexes of various types to execute subqueries, complete data filtering, pre-filtering and pre-aggregation within the shards, and generate local query results. The global result fusion module is used to receive the local query results returned by each node, perform global association aggregation, sorting, pagination and deduplication processing, generate the final query results and return them to the user.

[0015] Compared with the prior art, the beneficial effects of the present invention are: 1. The distributed database query optimization method based on hybrid index structure proposed in this solution constructs a three-layer collaborative cost model that adapts to combinations of heterogeneous indexes of various types, such as B+ tree, hash, bitmap, and inverted index. Combined with index matching with coverage weight and redundant path elimination mechanism, it achieves accurate selection of the best index combination, solves the problem of underestimating the collaborative overhead of the combination by the single index cost model, and significantly improves the execution efficiency of complex queries with multiple conditions. 2. The distributed database query optimization method based on the hybrid index structure proposed in this scheme achieves the early removal of invalid shards and the dynamic selection of the optimal replica by using a Join association sharding pruning mechanism based on the overlap density of hash slot intervals and association keys, combined with a precise routing algorithm based on real-time node load and network topology. This significantly reduces the amount of cross-node data transmission and effectively balances the computation and network load of each node in the cluster. 3. The distributed database query optimization method based on hybrid index structure proposed in this solution achieves the sinking of data processing logic and the early simplification of intermediate results through the collaborative execution process of local sharded multi-type heterogeneous indexes, combined with the pre-aggregation and association pre-filtering mechanism within the shards. This significantly reduces the global computing pressure of the coordination node and further shortens the overall query response time in high-concurrency scenarios. Attached Figure Description

[0016] Figure 1 This is an overall flowchart of the distributed database query optimization method based on a hybrid index structure proposed in this invention; Figure 2 This is a system framework diagram of the distributed database query optimization system based on a hybrid index structure proposed in this invention. Detailed Implementation

[0017] The following description is intended to disclose the invention and enable those skilled in the art to implement it. The preferred embodiments described below are merely examples, and other obvious variations will occur to those skilled in the art.

[0018] The hybrid index structure in this invention adopts a two-layer distributed architecture, and the specific construction method is as follows: The global metadata index is implemented using a distributed consistent key-value store (such as etcd). The stored content includes: the sharding keys for each data table, the mapping relationship between shard ranges and physical nodes, a list of index types for each shard, a histogram of field statistics, node load status, and index version number. The global metadata index uses a 500ms batch synchronization mechanism, with updates uniformly handled by the coordinating node, and a copy cached locally on all data nodes.

[0019] Each data shard in the local sharded index independently maintains a collection of heterogeneous indexes of multiple types, including: The primary index is a B+ tree index built on the shard key to ensure that the data within the shard is ordered. The secondary indexes are dynamically built according to the query pattern. For equality queries, high-frequency fields are built with hash indexes, high-frequency fields for range queries are built with B+ tree indexes, low-cardinality numeric fields (such as status and gender) are built with bitmap indexes, and text fields are built with inverted indexes. Each shard also has a corresponding Bloom filter to quickly determine whether the data exists in the shard.

[0020] The index initialization process involves each data node generating local primary and secondary indexes in parallel after data sharding is completed, and the coordinating node collecting index metadata from each shard, building a global metadata index, and synchronizing it to all nodes.

[0021] Reference Figure 1 A distributed database query optimization method based on a hybrid index structure includes the following steps: Step 1: Receive user query requests, perform syntax parsing and semantic preprocessing on the query requests, and generate a standard query syntax tree; Furthermore, the query request undergoes syntax parsing and semantic preprocessing to generate a standard query syntax tree, specifically including: The system obtains the raw SQL character stream from the received user query request and performs lexical recognition to obtain a token identifier sequence containing keywords, identifiers, and operators. Perform syntactic structure matching on the token identifier sequence to obtain the initial abstract syntax tree; Based on the initial abstract syntax tree, the database global metadata index is called to obtain the table name, field name, and corresponding data dictionary information; Based on the data field information, the initial abstract syntax tree is semantically validated to obtain a validated logical operator tree; Based on the logical equivalence transformation rules, predicate rewriting and operator merging are performed on the logical operator tree to obtain the optimized logical expression flow; Further, the dependencies and output pattern definitions of each operator are obtained, and the dependencies and output pattern definitions are standardized and encapsulated to obtain a standard query syntax tree that can be recursively traversed.

[0022] Specifically, the syntax structure matching of the token identifier sequence to obtain the initial abstract syntax tree is implemented using an improved LR(1) parser. Based on the context-free grammar rules extended from the SQL-92 standard, the linear token sequence is subjected to bottom-up syntax deduction and structure matching. The specific process is as follows: Load the predefined SQL context-free grammar rule set, which includes three core grammar production rules: data definition statement (DDL), data manipulation statement (DML), and data query statement (DQL). For distributed databases, special grammar rules such as sharded query and cross-node association are extended. The token identifier sequence is taken as input and pushed into the syntax analysis stack. The shift and reduction operations are performed in sequence: when the symbol sequence at the top of the stack matches the right-hand side of a grammar production rule, it is reduced to the left-hand non-terminal symbol of the production rule. During the reduction process, the syntax structure matching is performed simultaneously with the token identifier sequence. Constructing the initial abstract syntax tree (AST): Each reduction operation corresponds to an AST node, with node types corresponding one-to-one with grammar non-terminal symbols, including the root node (query statement node) and child nodes (selection nodes, projection nodes, filter nodes, association nodes, grouping nodes, sorting nodes, etc.); Performing syntax error detection and recovery: When a token sequence cannot match any grammar production, locate the syntax error, record the error type (such as missing keywords, mismatched parentheses, illegal operators), and attempt to perform local recovery by skipping illegal tokens, inserting missing symbols, etc., to generate a complete AST as much as possible; Outputting the initial abstract syntax tree, which is a rooted tree with a hierarchical structure. Each node contains a node type, node attributes (such as field names, operators, constant values), and child node pointers, fully reflecting the syntax structure of the SQL statement.

[0023] For example, for the SQL statement SELECT user_id, SUM(amount) FROM orders WHERE create_time > '2026-01-01' GROUP BY user_id, the initial AST root node is the query statement node, and its child nodes are, in order, the projection node (containing user_id and SUM(amount)), the table reference node (orders), the filter node (create_time>'2026-01-01'), and the grouping node (user_id).

[0024] Semantic validity verification is performed on the initial abstract syntax tree (AST) based on data field information to obtain a verified logical operator tree. Specifically, the initial AST is used as input, combined with data dictionary information from the global metadata index, to perform multi-level semantic validity verification, and the syntactic AST is converted into a logical operator tree. The specific process is as follows: All table reference nodes in the initial AST are traversed, and the existence, access permissions, and sharding information of the corresponding tables are queried from the global metadata index. If the table does not exist or has no access permissions, a semantic error is thrown. All field reference nodes are traversed, verifying whether the fields exist in the corresponding tables, and verifying the compatibility of field data types and operators: for example, arithmetic operations on string fields are prohibited, and comparisons between date fields and numeric constants are prohibited. The validity of aggregate functions and grouping clauses is verified: ensuring that all non-aggregate fields in the SELECT clause appear in the GROUP clause. In the BY clause, aggregate functions are prohibited in the WHERE clause, and the validity of the parameter types of aggregate functions is verified. The validity of join conditions is verified: ensuring data type compatibility of join fields and prohibiting Cartesian product operations without join conditions across tables (unless explicitly specified by the user). Type inference and implicit conversion are performed: for operations with type mismatches but supporting implicit conversion (such as adding integers and floating-point numbers), type conversion nodes are automatically inserted to unify operand types. The initial abstract syntax tree is converted into a logical operator tree: each AST node is mapped to a corresponding logical operator, for example, projection nodes are mapped to projection operators, filter nodes to selection operators, join nodes to join operators, and grouping nodes to aggregation operators. Operators are connected by data flow edges, forming a directed acyclic graph (DAG) structure of the logical operator tree. The verified logical operator tree is output, with each operator containing information such as operator type, input / output mode, and operator attributes, fully reflecting the logical execution semantics of the SQL statement.

[0025] Based on the logical equivalence transformation rules, predicate rewriting and operator merging are performed on the logical operator tree to obtain an optimized logical expression flow. Specifically, based on the principle of relational algebraic equivalence transformation, a series of optimization transformations are performed on the logical operator tree to maintain semantic equivalence, eliminate redundant operations, and improve the efficiency of subsequent physical execution plans. The specific process is as follows: ① Predicate rewriting optimization, specifically including: constant folding, which replaces all computable constant expressions with calculation results, for example, rewriting WHERE price > 100*2 as WHERE price > 200, and rewriting SELECT 1+2 AS num as SELECT 3 ASnum; predicate pushdown, which pushes the selection operator as far as possible to the leaf nodes (table scan nodes) of the logical operator tree, filtering irrelevant data in advance and reducing the processing load of subsequent operators. For example, rewriting `SELECT * FROM orders JOIN users ON orders.user_id=users.id WHERE orders.status=1` first filters the `orders` table by setting `status=1`, then joins it with the `users` table. Converting OR conditions to UNION: When an OR condition involves different fields and each field has a corresponding index, rewriting `WHERE a=1 OR b=2` to `SELECT * FROM table WHERE a=1 UNION SELECT * FROM table WHERE b=2` allows for merging results after retrieving data from multiple indexes. Eliminating redundant predicates involves deleting predicates that are logically always true or always false, such as deleting `WHERE 1=1` and `WHERE 0=1`, merging duplicate predicate conditions. Rewriting join conditions involves converting outer joins to inner joins (when the right table of the outer join has a non-null filter condition), and converting unequal joins to equal joins with filtering, improving the efficiency of join operations. ② Operator merging optimization specifically includes: Merging consecutive selection operators, which combines multiple consecutive selection operators into one selection operator, merging their filtering conditions into logical AND expressions. For example, σ(a>1)(σ(b<2)(R)) is merged into σ(a>1∧b<2)(R); Merging consecutive projection operators, which combines multiple consecutive projection operators into one projection operator, retaining the final required field set and eliminating redundant intermediate projection operations; Merging selection and projection operators, which merges two operators into one projection operator with filtering conditions when the filtering conditions of the selection operator only involve the fields retained by the projection operator; and Merging aggregation operators, which combines multiple consecutive aggregation operators into one aggregation operator, merging their aggregation function lists.③ Logical expression stream generation, specifically including: performing topological sorting on the optimized logical operator tree to obtain a linear operator execution sequence; assigning a unique execution identifier to each operator and recording the input-output dependencies between operators; attaching global metadata information, including table sharding information, field index information, data statistics information, etc., to provide a basis for subsequent heterogeneous index matching and cost calculation; and outputting the optimized logical expression stream, which is a standardized linear sequence that can be directly parsed and processed by subsequent modules.

[0026] Specifically, the query parsing preprocessing module of this invention adopts a modular design, with the parser, semantic validator, and logic optimizer operating independently and capable of being expanded and upgraded separately. The logical equivalence transformation rule set is stored in a configurable manner, supporting the dynamic addition or deletion of transformation rules based on different database engines and business scenarios, thus exhibiting good versatility and scalability.

[0027] Step 2: Obtain the filtering conditions, multi-table join conditions, and sorting and grouping conditions from the standard query syntax tree, and match the heterogeneous candidate index combination set containing different types of indexes from the hybrid index structure; Furthermore, the process of matching a set of heterogeneous candidate index combinations containing different types of indexes includes the following steps: Obtain the root node of the standard query syntax tree and perform a depth-first traversal to obtain a predicate feature set covering all filtering predicates, join predicates, and ordering predicates; Based on the field identifiers in the predicate feature set, retrieve the local shard index distribution table of the corresponding field from the global metadata index; Obtain a list of index types supported by each field from the local sharded index distribution table. The list of index types includes B+ tree index, bitmap index, and inverted index. Based on the filtering type in the predicate feature set, the list of index types is filtered for validity to obtain a set of available indexes that match the query semantics; Obtain the covering column information and physical storage location corresponding to each index, and calculate the coverage weight of each individual index for the query predicate based on the covering column information; By combining multiple indices using the coverage weights through a Cartesian product, a preliminary sequence of index path combinations is obtained. Redundant paths are eliminated according to the preset heterogeneous hybrid rules, and a set of heterogeneous candidate index combinations composed of indexes with different physical structures is obtained.

[0028] Specifically, the formula for calculating the coverage weight of each individual index on the query predicate is: ω i= (Number of covered predicates / Total number of predicates) × 0.7 + (Number of covered projected fields / Total number of projected fields) × 0.3. Heterogeneous hybrid rules include: selecting at most one index type for the same field; prioritizing index combinations with high coverage weights; eliminating combinations containing low-selectivity indexes (selectivity > 0.8); for multi-condition combinations, prioritizing the combination of "hash index + B+tree index", followed by the combination of "B+tree index + bitmap index".

[0029] Step 3: By using a pre-defined hybrid index cost model that adapts to heterogeneous index combinations, obtain the total execution plan cost corresponding to each heterogeneous index combination, and select the heterogeneous index combination with the minimum total cost and its corresponding execution plan as the optimal execution plan. Furthermore, the process of obtaining the total execution plan cost corresponding to each heterogeneous index combination includes the following steps: Obtain a histogram of statistical information of the fields involved in each heterogeneous candidate index combination, and then obtain the selectivity of each predicate; Get the total number of rows in the table, and use the product of the total number of rows in the table and the selectivity as the estimated number of records to be returned by the query; Based on preset hardware resource parameters, the unit I / O cost coefficient and unit CPU processing cost coefficient are obtained by combining the estimated number of returned records. Determine the index scan cost based on the unit I / O cost coefficient; The predicate filtering overhead is determined based on the estimated number of returned records and the unit CPU processing cost coefficient. Based on the index scan overhead and predicate filtering overhead, the single-point sharding execution cost is obtained; The data transmission cost of each node is determined based on the distributed network communication parameters, and then added to the single-point sharding execution cost to obtain the total execution plan cost. The formula for calculating the selectivity of the predicate is as follows: In the formula, For field values The probability density distribution function, , To filter the range of values ​​for the query predicate, , For field values The range of values ​​within the entire dataset; The formula for calculating index scan overhead is: In the formula, The fan-out degree of the index node. The total number of records stored in the index. The height of the index tree represents the number of node levels required for an index lookup. The cost factor per unit I / O; The formula for calculating the predicate filtering overhead is: In the formula, This is to estimate the number of records to be returned (the estimated number of rows of records to be processed after index filtering). The CPU processing cost coefficient (the CPU overhead required to process the predicate conditions of a single record).

[0030] Specifically, the statistical information histogram in this invention uses an equal-height histogram, where each bucket contains the same number of records. The histogram is generated as follows: after sorting the field values, the data is divided into 20 buckets based on the number of records, and the minimum, maximum, and number of records in each bucket are recorded. Probability density function. This is obtained by linear interpolation fitting to the histogram. For equality query predicates (e.g., col=val). The selectivity rate is the probability of that value appearing within the corresponding bucket; for range query predicates (such as col BETWEEN a AND b). , The probability within the interval is calculated by integration.

[0031] The inode fan-out, *m*, is determined by the database page size and the index key size, calculated as *m* = page size / (index key size + pointer size). For a default 8KB page size, 8-byte index key, and 8-byte pointer, *m* is approximately 512. The unit I / O cost coefficient, calibrated through benchmarks, shows the average time to read an 8KB database page is approximately 10ms for a standard hard drive and approximately 0.1ms for a solid-state drive. The unit CPU processing cost coefficient, calibrated through benchmarks, shows the average time to process a simple integer comparison predicate is approximately 0.01μs. For complex predicates (such as string matching and function computation), multiply by the corresponding complexity coefficient (5 for string matching and 10 for function computation).

[0032] In a preferred embodiment of the present invention, the above steps are implemented using a hybrid index-specific cost model that adapts to heterogeneous index combinations. This model is a three-layer collaborative architecture (single index basic cost layer, heterogeneous combination collaborative cost layer, and distributed execution global cost layer), as detailed below: The single-index basic cost layer is used to define exclusive basic cost formulas for different types of indexes (B+ tree, hash, bitmap, inverted index); the heterogeneous combination collaborative cost layer is used to calculate the collaborative overhead (index switching, result merging, conflict handling) when multiple heterogeneous indexes are used in combination; the distributed execution global cost layer is used to superimpose the costs of cross-node network transmission, sharding coordination, and replica selection. The total cost formula is: In the formula, This represents the number of indexes included in the heterogeneous index combination. For the first The basic cost of an index. The cost of heterogeneous combination synergy The global cost of distributed execution; The basic cost layer for a single index includes the basic cost of a B+ tree index, a hash index, a bitmap index, and an inverted index. Among these, the basic cost of a B+ tree index is suitable for range queries and sorted queries, and its cost consists of three parts: index tree traversal, leaf node scanning, and table lookup. In the formula, the cost of index tree traversal is... Leaf node scanning cost , Given the total number of leaf nodes in B+, the cost of a table lookup is... If the index is a non-covering index, then If it is a covering index, .

[0033] The basic cost of a hash index is applicable to equality query scenarios, and the cost consists of three parts: hash calculation, bucket location, and in-bucket scan. In the formula, the hash calculation cost is... , where is the CPU cost of a single hash function computation and is the bucket location cost. The I / O cost for reading the hash bucket directory, and the cost of scanning within the bucket. , The total number of hash buckets, This represents the average number of pages within a bucket that need to be scanned.

[0034] The basic cost of bitmap indexing is suitable for low cardinality multi-value filtering scenarios. The cost consists of three parts: bit vector loading, logical bit operations, and row offset conversion. In the formula, bit loading cost , For the size of the bit vector, For database page size, the cost of logical bit operations , The row offset conversion cost is the number of bit vectors involved in the bit operations. This is the CPU cost of converting a bit vector into a set of row offsets.

[0035] The basic cost of an inverted index is applicable to full-text fuzzy query scenarios. The cost consists of three parts: term location, inverted table scan, and result merging. In the formula, the term location cost , Fan-out degree of the dictionary node. The cost of scanning the inverted index is the total number of entries. , The total number of pages in the inverted index, and the resulting merge cost. , which is the merge sort cost of multiple inverted index results.

[0036] The core formula for the heterogeneous combination collaborative cost layer is: Among them, the cost of index switching This refers to the overhead of switching index contexts when a query needs to use multiple indexes of different types sequentially. The calculation formula is as follows: In the formula, This represents the number of indexes in the heterogeneous index combination. Fixed overhead for a single index context switch (calibrated by benchmarks, default value is 0.5ms). Result merging cost. This is the cost of merging the result set after filtering from multiple indexes separately. The calculation formula is: In the formula For the first Predicate selectivity of each index. Conflict handling cost. This refers to the overhead of performing a secondary validation when there are logical conflicts in the filtering results of multiple indexes (e.g., one index considers a record to meet a condition, while another considers it not to). The calculation formula is as follows: In the formula The conflict rate (based on historical data, with a default value of 0.05).

[0037] The global cost layer for distributed execution is the sum of cross-node overhead in a distributed scenario, and its calculation formula is as follows: Among them, network transmission cost The overhead of transmitting data across nodes is calculated using the following formula: In the formula To transmit data volume, For network bandwidth, This represents the network latency factor (default value is 1.2). Fragmentation coordination cost. The overhead of the coordinating node in scheduling data nodes is calculated using the following formula: In the formula The number of data nodes involved in the query. This represents the processing overhead for a single coordination message (default value is 0.1ms). The replica selection cost is the overhead of choosing the optimal replica (primary / slave). If a slave replica is selected, additional data consistency verification overhead is required. In the formula The overhead for data consistency verification (default value is 0.2ms).

[0038] Understandably, the single-point shard execution cost is the total local cost of a query sub-plan on a single data shard without considering cross-node network transmission. Its calculation focuses on index scan overhead and predicate filtering overhead. The complete process is as follows: First, calculate the total index scan overhead based on the type and number of heterogeneous index combinations. For single-index scenarios, directly use the basic cost formula of the corresponding type. For multi-heterogeneous index combination scenarios, add the local collaborative cost of index context switching and result merging on the sum of the basic costs of all single indexes. Then, calculate the total predicate filtering overhead, only counting the remaining predicate filtering costs that are not covered by the index and need to be validated again after reading the data rows. The predicate costs that have been filtered by the index are not calculated again. Next, selectively add the overhead of other necessary local operations such as sorting, pre-aggregation, and temporary table construction within the shard according to the actual needs of the query sub-plan. Finally, add the above three costs to obtain the final single-point shard execution cost.

[0039] All parameters in this embodiment were calibrated using a combination of benchmark testing and historical statistics to ensure the accuracy of cost calculations, including hardware-related parameters. , And so on, pre-calibrated using standard benchmarking tools such as sysbench and TPC-H, and recalibrated quarterly), data distribution-related parameters ( , The values ​​are obtained through statistical analysis of historical query execution logs and are updated monthly. The initial values ​​are based on industry-standard empirical values ​​and empirical coefficients. , The values ​​are obtained through statistical analysis of historical query execution logs and are updated monthly. The initial values ​​are based on industry-standard empirical values.

[0040] Step 4: When the query involves multi-table cross-shard joins, the global metadata index is used to complete the join join shard pruning and precise routing of the join nodes, determine the set of data shards to be accessed, and split the optimal execution plan into multiple shard subqueries, which are then sent to the corresponding data nodes for parallel execution. Furthermore, the process of completing Join-related shard pruning through the global metadata index includes the following steps: Based on the multi-table join conditions in the standard query syntax tree, the join key fields of the left and right tables are obtained, and then the physical sharding distribution logical mapping of each table is obtained from the global metadata index. The constant boundary value of the filter operator based on the join condition is obtained, and the corresponding hash slot interval is obtained according to the constant boundary value; Perform an intersection operation between the hash slot interval and the sharded storage graph to obtain a set of potential active shard identifiers; Retrieve the global index snapshot, obtain the shard mask vector containing non-empty result sets, and further obtain the overlap density of the association keys of each table under the cross-node distribution; By using overlap density to eliminate logically isolated fragments that do not meet the association constraints, candidate pruning results are obtained, and a simplified execution path is further generated to complete the Join association fragment pruning.

[0041] Specifically, the purpose of obtaining the constant boundary value of the filter operator based on the Join condition and obtaining the corresponding hash slot range based on the constant boundary value is to extract constant constraints from the filter operator of the Join condition, convert them into the corresponding hash slot range, thereby quickly locating the "shards that may contain matching data", eliminating the "shards that are absolutely impossible to have data", and significantly reducing the number of shards that need to be accessed and the amount of cross-node data transmission. For example, assuming the orders table is sharded by user_id hash, the total number of hash slots is 16384; the hash function is: hash(user_id)=crc32(user_id)%16384; the constant boundary value is user_id=12345, and the calculated hash value is crc32(12345)%16384=5678; then the corresponding hash slot range is

[5678] (a single hash slot). If the constant boundary value is user_idBETWEEN10000AND20000, the calculated hash value range is [3456,7890], and the corresponding hash slot interval is 3456,3457,...,7890 (4435 consecutive hash slots).

[0042] The overlap density is calculated using the formula: d = (number of candidate shards in the left table ∩ number of candidate shards in the right table) / (number of candidate shards in the left table ∪ number of candidate shards in the right table). When the overlap density d < 0.1, the corresponding shard pair is removed. When the data volume of a table involved in the association is less than 10MB, the small table broadcast strategy is triggered: all data of the table is broadcast to all nodes involved in the association, and the association operation is completed locally.

[0043] Furthermore, the process of determining the set of data shards to be accessed includes the following steps: Obtain the shard pruning results to obtain a list of physical node IP addresses for all surviving shards; The real-time load factor of each data node is obtained through a heartbeat monitoring protocol, and the estimated response latency of each node is calculated according to the following formula. : ,in The length of the queue of tasks to be processed. For node processing capacity, This is the delay factor; Based on the estimated response delay, obtain the shortest transmission weight matrix in the network topology, and further obtain the available status bits of the primary and secondary replicas; By using the available status bits and the estimated response latency, the access priority score of each data shard is determined, and the optimal physical location is selected to obtain a list of target nodes for precise routing. Based on the target node list and its corresponding shard range, obtain the set of data shards that need to be accessed.

[0044] Specifically, the latency coefficient is obtained through historical data statistics, with a value range of 1.2 to 2.0 and a default value of 1.5. The node processing capacity is the number of query requests that the node can process per second, pre-calibrated through benchmark testing. Based on the response latency estimate, the shortest transmission weight matrix in the network topology is obtained. This is a business-aware shortest transmission weight matrix constructed based on the real-time cluster status. First, the coordinating node obtains the physical location information of all data nodes (including their racks and switches) through the cluster metadata service, automatically constructing an initial network topology map. Then, combining the response latency estimates of each node, and according to the hierarchical relationships of the same rack, different racks, and different switches, a differentiated initial transmission weight is assigned to each edge in the topology map. Subsequently, the Floyd-Warshall algorithm is used to calculate the shortest transmission path between any two nodes, updating the global shortest transmission weight matrix. This matrix is ​​automatically updated every 10 seconds, and also triggered by instant updates when nodes join, leave, or experience network anomalies, ensuring that it always reflects the true network transmission cost of the cluster. The access priority score for each data shard is determined by using availability status bits and response latency estimates. Optimal replica selection is achieved through a multi-dimensional weighted scoring system. First, faulty nodes and nodes currently synchronizing data are filtered out based on the replica's availability status bits, retaining only healthy and available primary and secondary replicas. Then, a three-dimensional weighted formula considering status, latency, and load is used to calculate the access priority score for each available replica. In the status dimension, the primary replica has a higher weight than the secondary replica; in the latency dimension, lower response latency results in a higher score; and in the load dimension, lower real-time node load results in a higher score. Finally, all available replicas are sorted from highest to lowest score, and the replica with the highest score is selected as the access target for that shard. If multiple replicas have the same score, the primary replica is prioritized. This approach minimizes query latency and balances node load while ensuring data consistency.

[0045] Step 5: Each data node uses its local sharded multi-type heterogeneous index to execute sharded subqueries, complete data filtering, association pre-filtering and pre-aggregation within the shard, generate local query results and return them; Furthermore, the process of performing a sharded subquery includes the following steps: Obtain the subquery plan sent to the data nodes, determine the sequence of operators to be executed locally on the shards, parse the local shard index, and obtain the bit vector of the bitmap index; Use bit vectors to perform logical bit operations to obtain the set of row offsets that satisfy the filtering predicate, and simultaneously obtain the leaf node pointers of the B+ tree index; The physical starting address of the data block is obtained based on the leaf node pointer, and then asynchronous parallel I / O reading is performed to obtain multiple rows of raw data records; The local inverted index is used to perform full-text fuzzy condition matching and filtering, and then the effective record line flow after the local data filtering is completed is obtained.

[0046] Specifically, the collaborative execution process for multi-type heterogeneous indexes is as follows: First, bitmap indexes are used to perform bitwise operations to quickly filter out records that do not meet the low cardinality condition, resulting in a set of row offsets; based on the set of row offsets, B+ tree indexes are used to locate the corresponding data rows; for full-text fuzzy conditions, inverted indexes are used for matching and filtering; all filtering results are merged to obtain a valid record row flow.

[0047] Furthermore, the process of generating local query results includes the following steps: Based on the effective record row flow, obtain the list of dimension fields defined by the local pre-aggregation operator, and build a temporary hash mapping table in local memory; Obtain the metric value of the valid record row stream, determine the cumulative sum and count value of each hash slot, and calculate the local average value according to the following formula: ,in This is the cumulative sum across all hash slots. This represents the count value for each hash slot; Retrieve the results from the hash mapping table to obtain the local aggregated record set that has been pre-filtered and associated; Based on the sorting and grouping conditions, the local aggregated record set is optimized by heap sort to obtain ordered intermediate data blocks, which are then encapsulated using a binary protocol to obtain a local query response packet containing fragment metadata. The local query response packet is compressed according to the network transmission caching strategy to generate local query results.

[0048] Step 6: Receive the local query results returned by each data node, perform global association aggregation, sorting, pagination and deduplication fusion processing, generate the final query results and return them to the user.

[0049] Specifically, the purpose of obtaining the results from the hash map table and getting the local aggregated record set after association pre-filtering is to extract the pre-aggregation results from the temporary hash map table in memory and synchronously apply the association pre-filtering conditions within the shard, further reducing the amount of data that needs to be transmitted to the coordinating node, and laying a streamlined data foundation for subsequent global association aggregation and sorting.

[0050] Furthermore, the process of generating the final query results includes the following steps: Receive the local query results from all data nodes and obtain the global result merging buffer; Based on the global association aggregation rules, the mean data from different shards in the buffer are merged, and then a preliminary aggregated global data view is obtained based on the merged full metrics. Based on the user-defined uniqueness constraint field, the deduplicated record sequence is obtained, and the external sorting and merging of the deduplicated record sequence is performed based on the sorting and grouping conditions to obtain a candidate result set; Retrieve the pagination parameters specified by the user and determine the starting pointer of the target result page; The candidate result set is sliced ​​using the starting pointer to obtain the final record set. The final record set is then formatted and mapped to generate the final query result.

[0051] Furthermore, the hybrid index of this invention adopts a consistency guarantee mechanism of local synchronous update + global asynchronous batch update: When performing Insert / Update / Delete operations, all indexes on the local shard are updated synchronously, and the index update log is recorded simultaneously. The coordinating node collects the index update logs of each node every 500ms, updates the global metadata index in batches, and synchronizes it to all nodes; An optimistic locking + version number mechanism is adopted. When an index version conflict is detected, the latest version shall prevail. The system calculates the hit rate and usage frequency of each index every hour. Indexes with a hit rate of less than 0.1 are automatically deleted, and corresponding index types are automatically created for frequently queried fields.

[0052] Reference Figure 2 This invention also proposes a distributed database query optimization system based on a hybrid index structure, applicable to any of the query optimization methods described above, specifically including: The query parsing preprocessing module is used to receive user query requests, perform lexical analysis, syntax parsing and semantic verification on the raw SQL character stream, perform predicate rewriting and operator merging, and generate a standard query syntax tree; The heterogeneous index matching module is used to traverse the standard query syntax tree to extract the predicate feature set, match the available index types corresponding to each field, and generate and filter to obtain a set of heterogeneous candidate index combinations; The cost calculation and plan generation module is used to calculate the total cost of the execution plan for each heterogeneous index combination based on the hybrid index exclusive cost model, and select the optimal execution plan with the minimum total cost; The distributed execution scheduling module is used to complete the Join association shard pruning and association node precise routing through the global metadata index, determine the set of data shards to be accessed, split the optimal execution plan into multiple subqueries for sharding and send them to the corresponding data nodes for parallel execution; The local index execution module is used to call the local shard multi-type heterogeneous index to execute subqueries, complete data filtering, association pre-filtering and pre-aggregation within the shard, and generate local query results; The global result fusion module is used to receive the local query results returned by each node, perform global association aggregation, sorting, paging and deduplication processing, generate the final query result and return it to the user.

[0053] The following further elaborates on the present invention in conjunction with specific embodiments: Embodiment 1: E-commerce order query scenario The test environment selects a 3-node distributed database cluster, and each node is configured with 8 cores, 16GB of memory, 1TB of solid-state drive, and a network bandwidth of 1Gbps. The order table used in the test data contains 100 million records, which are hash-sharded to 3 nodes according to the order ID. The fields include order ID (primary key), user ID, creation time, order status, and product name. The index configuration is order ID (B+ tree main index), user ID (hash index), creation time (B+ tree index), order status (bitmap index), and product name (inverted index). The test query is SELECT * FROM orders WHERE user_id=12345 AND create_time BETWEEN '2026-01-01' AND '2'026-01-31' AND status=1 AND goods_name LIKE '%mobile phone%'.

[0054] The specific execution process is as follows: First, the query parsing preprocessing module generates a standard query syntax tree. Then, the heterogeneous index matching module generates candidate index combinations: {user ID hash index, creation time B+ tree index, order status bitmap index, product name inverted index}. Next, the cost calculation module calculates the execution cost of this combination to be 120ms and selects the optimal execution plan. The distributed execution scheduling module locates the shard containing user ID=12345 through the global metadata index and issues a subquery. The local index execution module filters the data using the bitmap index, hash index, B+ tree index, and inverted index in sequence, obtaining 128 records. Finally, the global result fusion module merges the results and returns them.

[0055] The advantages of this invention are as follows: By constructing a three-layer collaborative cost model that adapts to combinations of heterogeneous indexes such as B+ trees, hash tables, bitmaps, and inverted indexes, the optimal index combination can be accurately selected. Combined with a Join sharding pruning mechanism based on hash slot intervals and association key overlap density, and a precise routing algorithm that is aware of real-time node load and network topology, the amount of cross-node data transmission is significantly reduced, effectively balancing the load on each node in the cluster. Simultaneously, through local multi-index collaborative execution and in-shard pre-aggregation and pre-filtering, data processing logic is decentralized, reducing the computational pressure on coordinating nodes and significantly improving the overall performance of complex multi-condition queries and cross-shard join queries, thus adapting to high-concurrency business scenarios.

[0056] The foregoing has shown and described the basic principles, main features, and advantages of the present invention. Those skilled in the art should understand that the present invention is not limited to the above embodiments. The embodiments and descriptions in the specification are merely principles of the invention. Various changes and modifications can be made to the invention without departing from its spirit and scope, and all such changes and modifications fall within the scope of the claimed invention. The scope of protection claimed by the appended claims and their equivalents is defined.

Claims

1. A distributed database query optimization method based on a hybrid index structure, characterized in that, Includes the following steps: Step 1: Receive user query requests, perform syntax parsing and semantic preprocessing on the query requests, and generate a standard query syntax tree; Step 2: Obtain the filtering conditions, multi-table join conditions, and sorting and grouping conditions from the standard query syntax tree, and match the heterogeneous candidate index combination set containing different types of indexes from the hybrid index structure; Step 3: By using a pre-defined hybrid index cost model that adapts to heterogeneous index combinations, obtain the total execution plan cost corresponding to each heterogeneous index combination, and select the heterogeneous index combination with the minimum total cost and its corresponding execution plan as the optimal execution plan. Step 4: When the query involves multi-table cross-shard joins, the global metadata index is used to complete the join join shard pruning and precise routing of the join nodes, determine the set of data shards to be accessed, and split the optimal execution plan into multiple shard subqueries, which are then sent to the corresponding data nodes for parallel execution. Step 5: Each data node uses its local sharded multi-type heterogeneous index to execute sharded subqueries, complete data filtering, association pre-filtering and pre-aggregation within the shard, generate local query results and return them; Step 6: Receive the local query results returned by each data node, perform global association aggregation, sorting, pagination and deduplication fusion processing, generate the final query results and return them to the user.

2. The distributed database query optimization method based on a hybrid index structure according to claim 1, characterized in that, The step of performing syntax parsing and semantic preprocessing on the query request to generate a standard query syntax tree specifically includes: The system obtains the raw SQL character stream from the received user query request and performs lexical recognition to obtain a token identifier sequence containing keywords, identifiers, and operators. Perform syntactic structure matching on the token identifier sequence to obtain an initial abstract syntax tree; Based on the initial abstract syntax tree, the database global metadata index is called to obtain the table name, field name, and corresponding data dictionary information; Based on the data field information, the initial abstract syntax tree is semantically validated to obtain a validated logical operator tree; According to the logical equivalence transformation rules, predicate rewriting and operator merging are performed on the logical operator tree to obtain the optimized logical expression flow; Further, the dependency relationships and output mode definitions of each operator are obtained, and the dependency relationships and output mode definitions are standardized and encapsulated to obtain a standard query syntax tree that can be recursively traversed.

3. The distributed database query optimization method based on a hybrid index structure according to claim 1, characterized in that, The process of matching a set of heterogeneous candidate index combinations containing different types of indexes includes the following steps: Obtain the root node of the standard query syntax tree and perform a depth-first traversal to obtain a predicate feature set covering all filtering predicates, join predicates, and ordering predicates; Based on the field identifiers in the predicate feature set, obtain the local shard index distribution table of the corresponding field from the global metadata index; Obtain a list of index types supported by each field from the local sharded index distribution table. The list of index types includes B+ tree index, bitmap index, and inverted index. Based on the filtering type in the predicate feature set, the index type list is filtered for validity to obtain a set of available indexes that match the query semantics; Obtain the covering column information and physical storage location corresponding to each index, and calculate the coverage weight of each individual index for the query predicate based on the covering column information; The Cartesian product of multiple indices is combined using the coverage weights to obtain a preliminary index path combination sequence; Redundant paths are eliminated according to the preset heterogeneous hybrid rules, and a set of heterogeneous candidate index combinations composed of indexes with different physical structures is obtained.

4. The distributed database query optimization method based on a hybrid index structure according to claim 1, characterized in that, The process of obtaining the total execution plan cost corresponding to each heterogeneous index combination includes the following steps: Obtain a histogram of statistical information of the fields involved in each heterogeneous candidate index combination, and then obtain the selectivity of each predicate; Get the total number of rows in the table, and use the product of the total number of rows in the table and the selectivity as the estimated number of records to be returned by the query; Based on preset hardware resource parameters, the unit I / O cost coefficient and unit CPU processing cost coefficient are obtained by combining the estimated number of returned records. The index scan overhead is determined based on the unit I / O cost coefficient. The predicate filtering overhead is determined based on the estimated number of returned records and the unit CPU processing cost coefficient. Based on the index scan overhead and predicate filtering overhead, the single-point sharding execution cost is obtained; The data transmission cost of each node is determined based on the distributed network communication parameters, and then added to the single-point sharding execution cost to obtain the total execution plan cost. The formula for calculating the selectivity of the predicate is as follows: In the formula, For field values The probability density distribution function, , To filter the range of values ​​for the query predicate, , For field values The range of values ​​within the entire dataset; The formula for calculating the index scan overhead is as follows: In the formula, The fan-out degree of the index node. The total number of records stored in the index. The height of the index tree represents the number of node levels required for an index lookup. The cost factor per unit I / O; The formula for calculating the predicate filtering overhead is as follows: In the formula, To estimate the number of records to be returned, The CPU processing cost coefficient.

5. The distributed database query optimization method based on a hybrid index structure according to claim 1, characterized in that, The process of completing Join-related shard pruning through global metadata indexing includes the following steps: Based on the multi-table join conditions in the standard query syntax tree, the join key fields of the left and right tables are obtained, and then the physical sharding distribution logical mapping of each table is obtained from the global metadata index. The constant boundary value of the filter operator based on the Join condition is obtained, and the corresponding hash slot interval is obtained according to the constant boundary value; Perform an intersection operation between the hash slot interval and the sharded storage graph to obtain a set of potential active shard identifiers; Retrieve the global index snapshot, obtain the shard mask vector containing non-empty result sets, and further obtain the overlap density of the association keys of each table under the cross-node distribution; By using the overlap density to remove logically isolated fragments that do not meet the association constraints, candidate pruning results are obtained, and a simplified execution path is further generated to complete the Join association fragment pruning.

6. The distributed database query optimization method based on a hybrid index structure according to claim 1, characterized in that, The process of determining the set of data shards to be accessed includes the following steps: Obtain the shard pruning results to obtain a list of physical node IP addresses for all surviving shards; The real-time load factor of each data node is obtained through a heartbeat monitoring protocol, and the estimated response latency of each node is calculated according to the following formula. : ,in The length of the queue of tasks to be processed. For node processing capacity, This is the delay factor; Based on the estimated response delay, the shortest transmission weight matrix in the network topology is obtained, and the available status bits of the primary and secondary replicas are further obtained. By using the available status bits and the estimated response latency, the access priority score of each data shard is determined, and the optimal physical location is selected to obtain a list of target nodes for precise routing. Based on the target node list and its corresponding shard range, obtain the set of data shards that need to be accessed.

7. The distributed database query optimization method based on a hybrid index structure according to claim 1, characterized in that, The process of performing a sharded subquery includes the following steps: Obtain the subquery plan sent to the data nodes, determine the sequence of operators to be executed locally on the shards, parse the local shard index, and obtain the bit vector of the bitmap index; Logical bitwise operations are performed using the bit vector to obtain the set of row offsets that satisfy the filtering predicate, and the leaf node pointers of the B+ tree index are obtained simultaneously. Based on the leaf node pointer, the physical starting address of the data block is obtained, and asynchronous parallel I / O reading is further performed to obtain multiple rows of original data records; The local inverted index is used to perform full-text fuzzy condition matching and filtering, and then the effective record line flow after the local data filtering is completed is obtained.

8. The distributed database query optimization method based on a hybrid index structure according to claim 7, characterized in that, The process of generating local query results includes the following steps: Based on the effective record row flow, obtain the list of dimension fields defined by the local pre-aggregation operator, and build a temporary hash mapping table in local memory; Obtain the metric value of the valid record row stream, determine the cumulative sum and count value of each hash slot, and calculate the local average value according to the following formula: ,in This is the cumulative sum across all hash slots. This represents the count value for each hash slot; Obtain the results from the hash mapping table to get a local aggregated record set that has been associated and pre-filtered; The local aggregated record set is optimized by heap sort according to the sorting and grouping conditions to obtain ordered intermediate data blocks, and then encapsulated by binary protocol to obtain a local query response packet containing fragment metadata. The local query response packet is compressed according to the network transmission caching strategy to generate local query results.

9. The distributed database query optimization method based on a hybrid index structure according to claim 1, characterized in that, The process of generating the final query results includes the following steps: Receive the local query results from all data nodes and obtain the global result merging buffer; Based on the global association aggregation rules, the mean data from different shards in the buffer are merged, and then a preliminary aggregated global data view is obtained based on the merged full metrics. Based on the user-defined uniqueness constraint field, the deduplicated record sequence is obtained, and the external sorting and merging of the deduplicated record sequence is performed based on the sorting and grouping conditions to obtain a candidate result set; Retrieve the pagination parameters specified by the user and determine the starting pointer of the target result page; The candidate result set is sliced ​​using the starting pointer to obtain the final record set. The final record set is then formatted and mapped to generate the final query result.

10. A distributed database query optimization system based on a hybrid index structure, applied to the query optimization method as described in any one of claims 1-9, characterized in that, Specifically, it includes: The query parsing preprocessing module is used to receive user query requests, perform lexical analysis, syntax parsing and semantic verification on the raw SQL character stream, perform predicate rewriting and operator merging, and generate a standard query syntax tree; The heterogeneous index matching module is used to traverse the standard query syntax tree to extract the predicate feature set, match the available index types corresponding to each field, and generate and filter a set of heterogeneous candidate index combinations. The cost calculation and plan generation module is used to calculate the total cost of the execution plan for each heterogeneous index combination based on the hybrid index-specific cost model, and select the optimal execution plan with the minimum total cost. The distributed execution scheduling module is used to complete Join shard pruning and precise routing of associated nodes through global metadata index, determine the set of data shards to be accessed, split the optimal execution plan into multiple shard subqueries and distribute them to the corresponding data nodes for parallel execution; The local index execution module is used to call local sharded heterogeneous indexes of various types to execute subqueries, complete data filtering, pre-filtering and pre-aggregation within the shards, and generate local query results. The global result fusion module is used to receive the local query results returned by each node, perform global association aggregation, sorting, pagination and deduplication processing, generate the final query results and return them to the user.

Images