Index determination method and device for database query scenario, equipment and medium

Abstract

The application provides an index determination method and device for a database query scenario, equipment and a medium, relating to the technical field of computer databases. The method comprises: obtaining a target query statement and at least two target data tables corresponding to the target query statement, performing feature extraction on the target query statement and the target data tables respectively to obtain target query features and target table structure features; performing aggregation processing on each target data table to obtain target aggregation features representing the association between at least two target data tables; inputting the target query features, target table structure features and target aggregation features into an index determination model to obtain a target index scheme, and performing database query according to the target index scheme to obtain a target query result. The above method can improve the accuracy of the index scheme.

Description

Technical Field

[0001] This application relates to the field of computer database technology, and in particular to an index determination method, apparatus, device and medium for database query scenarios. Background Technology

[0002] With the increasing complexity of database application scenarios and the rapid growth of data scale, database query performance optimization has become a crucial issue. In relational databases, indexes are one of the key tools for improving query performance.

[0003] Traditional index optimizers select the optimal index scheme through fixed optimization rules and query performance cost estimations. However, as query complexity increases, this approach lacks flexibility and struggles to adapt to frequently changing database workloads. Consequently, traditional optimizers fail to accurately determine the optimal index scheme in complex scenarios, leading to a decline in database query performance. Summary of the Invention

[0004] This application provides an index determination method, apparatus, device, and medium for database query scenarios, which can solve the problem of decreased accuracy of the determined index scheme when facing dynamic and complex multi-table join queries using traditional methods.

[0005] To solve the above-mentioned technical problems, this application is implemented as follows: In a first aspect, embodiments of this application provide an index determination method for database query scenarios, the method comprising: Obtain the target query statement and at least two target data tables corresponding to the target query statement, and perform feature extraction on the target query statement and the target data tables respectively to obtain the target query features and the target table structure features; The target data tables are aggregated to obtain target aggregation features that characterize the relationship between at least two of the target data tables. The target query features, target table structure features, and target aggregation features are input into the index determination model to obtain the target index scheme, and the database query is performed according to the target index scheme to obtain the target query results.

[0006] Optionally, the iterative training of the initial neural network model to obtain the index determination model includes: Obtain an initial neural network model, and iteratively train the initial neural network model to obtain the index determination model, wherein the iterative training includes: The loss value is calculated based on a preset loss function, and the initial neural network model is updated according to the loss value until the preset iteration stopping condition is met, at which point the iteration stops. The preset loss function includes a first weighted term corresponding to query time loss and a second weighted term corresponding to index resource loss. Furthermore, the index resource loss is obtained by weighting storage space loss and update loss.

[0007] Optionally, the iterative training of the initial neural network model to obtain the index determination model includes: Based on the historical query statements and each of the historical data tables, a query structure diagram is constructed. The initial neural network model is obtained by combining a multilayer perceptron and a pre-defined graph neural network; The query structure graph and the multidimensional feature vector are input into the initial neural network model, and the initial neural network model is iteratively trained to obtain the index determination model.

[0008] Optionally, the step of constructing a query structure graph based on the historical query statements and each of the data tables includes: Using each of the data tables and fields in each of the data tables as nodes, edges are determined based on the connection conditions, filtering conditions, or grouping conditions in the target query statement, and the query structure graph is constructed.

[0009] Optionally, the historical query statements are SQL query statements, and feature extraction is performed on each historical query statement, its corresponding historical data table, and execution plan information to obtain a multi-dimensional feature vector, including: The SQL query statement is parsed into a syntax tree, multiple types of syntax units are extracted, and historical query features are constructed based on each syntax unit. Feature extraction is performed on the historical data table to obtain historical table structure features and historical business features, and feature extraction is performed on the execution plan information to obtain historical execution features; The multidimensional feature vector is obtained by integrating the historical query features, historical table structure features, historical business features, and historical execution features.

[0010] Optionally, the aggregation process performed on each of the target data tables to obtain target aggregation features characterizing the relationship between at least two of the target data tables includes: Each target data table is feature-encoded to obtain the corresponding node feature vector; Using each of the target data tables as nodes and the business relationships between the target data tables as edges, and combining the node feature vectors corresponding to each data table, a data table association graph is constructed. The data table association graph is aggregated to obtain the aggregated features.

[0011] Optionally, the aggregation process of the data table association graph to obtain the aggregation feature includes: The edge weight is calculated based on the historical association query frequency or business impact factor between the data tables corresponding to each edge. The edge weights and the feature vectors of each node are input into a preset graph attention neural network to calculate attention coefficients based on the edge weights and node features. The attention coefficients are then used to aggregate the feature vectors of each node to obtain the aggregated features.

[0012] Optionally, the aggregation process of the data table association graph to obtain the aggregation feature includes: The target query features are fused with the feature vectors of each node to obtain the node fused features; The attention coefficients between the fused features of each node are dynamically calculated using a graph attention neural network, and the features are aggregated to obtain the aggregated features.

[0013] Secondly, embodiments of this application provide an index determination device for database query scenarios, the device comprising: The feature module is used to obtain the target query statement and at least two target data tables corresponding to the target query statement, and to extract features from the target query statement and the target data tables respectively to obtain target query features and target table structure features; An aggregation module is used to perform aggregation processing on each of the target data tables to obtain target aggregation features that characterize the relationship between at least two of the target data tables. The indexing module is used to input the target query features, target table structure features, and target aggregation features into the index determination model to obtain a target indexing scheme, and to perform database queries based on the target indexing scheme to obtain target query results.

[0014] Thirdly, embodiments of this application provide an electronic device, including a processor and a memory, wherein the memory stores a program or instructions that can run on the processor, and when the program or instructions are executed by the processor, they implement the steps of the index determination method for database query scenarios as described in the first aspect.

[0015] Fourthly, embodiments of this application provide a readable storage medium storing a program or instructions, which, when executed by a processor, implement the steps of the index determination method for database query scenarios as described in the first aspect.

[0016] In this embodiment, by integrating target query features, target table structure features, and target aggregation features of multi-table associations, the target index scheme is generated by inputting the index to determine the model. This achieves accurate adaptation to multi-table association query scenarios, solves the problem that traditional optimizers cannot take into account both query statement features and inter-table associations under complex queries, improves the matching degree between the index scheme and actual query requirements, enhances the accuracy of the index scheme, and thus improves database query performance. Attached Figure Description

[0017] Figure 1 A flowchart illustrating the index determination method for database query scenarios provided in this application embodiment; Figure 2 A schematic diagram illustrating the parsing process of a query statement provided in an embodiment of this application; Figure 3 This is a schematic diagram comparing the index results provided in the embodiments of this application; Figure 4 A schematic diagram of the structure of an index determination device for database query scenarios provided in an embodiment of this application; Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0018] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0019] This application provides an index determination method, apparatus, device, and medium for database query scenarios. The embodiments of this application will be described in detail below with reference to the accompanying drawings and specific embodiments and application scenarios.

[0020] Please see Figure 1 , Figure 1 A flowchart illustrating an index determination method for database query scenarios provided in this application embodiment is shown in the figure. The method includes: Step 110: Obtain the target query statement and at least two target data tables corresponding to the target query statement, and extract features from the target query statement and the target data tables respectively to obtain target query features and target table structure features.

[0021] In this step, the target query statement, such as an SQL statement, is obtained from the real-time query request. Figure 2As shown, the query statement is parsed to identify one or at least two data tables involved. For example, in the financial field, key business data (such as users, orders, transactions, policies, claims, and credit behavior) are usually distributed across databases with at least two logical shards or physical nodes. Business queries often involve cross-table multi-field joins. For instance, in a payment scenario, it may involve cross-table queries between the user table, transaction record table, and payment channel table; in an insurance scenario, it may involve cross-table queries between the policyholder information table, claims history table, and insured person table; and in a credit purchase scenario, it may involve cross-table queries between the user credit score table, order table, overdue record table, and payment plan table. The target query statement undergoes structured or semantic feature extraction. For example, it is parsed into a syntax tree and keywords, operators, or conditional expressions are extracted; or a natural language processing model is used to convert it into a vector representation to capture the query intent and structural features, extracting target query features including table names, column names, filtering conditions, and sorting conditions. Based on the table name, the corresponding data table is associated, and metadata and structural features of the involved tables and column structures are extracted to obtain target table structure features, including the number of rows in the table, the data types of the columns, and the existing index types in the table. The index types can be B-Tree indexes, hash indexes, unique indexes, and composite indexes. Optionally, business semantic features of key fields in the table (such as whether they are primary keys, foreign keys, or frequently queried fields) can be extracted in conjunction with the business context.

[0022] Step 120: Aggregate each of the target data tables to obtain target aggregation features that characterize the relationship between at least two of the target data tables.

[0023] In this step, based on the business logic relationships or historical query patterns between the target data tables, a relationship graph structure is constructed between the data tables, where each node represents a table and edges represent the relationships between tables (such as join relationships, query dependencies, etc.). Information is aggregated from the relationship graph using graph structure learning methods, and feature vectors that reflect the global relationships between at least two tables are extracted as target aggregation features.

[0024] Step 130: Input the target query features, target table structure features, and target aggregation features into the index determination model to obtain the target index scheme, and perform database queries according to the target index scheme to obtain the target query results.

[0025] Indexes can improve database performance by accelerating data queries and reducing full table scans, but inappropriate index selection may lead to increased maintenance overhead (such as the need to maintain the index structure when updating and deleting).

[0026] In this step, the target query features, target table structure features, and target aggregation features extracted in steps 101 and 102 are fused to form a unified feature representation. This fused feature is then input into a pre-trained index determination model, which is a neural network-based prediction model capable of outputting a recommended indexing scheme based on the input features, including at least one of recommended index column combinations and index types. Based on the target indexing scheme output by the model, the corresponding index structure is created or adjusted in the database. The target query statement is then used to execute a query operation on the database configured with the target indexing scheme to obtain the query results.

[0027] It should be noted that this indexing scheme can be applied dynamically during query execution, or it can be deployed after the administrator verifies the index. This embodiment improves efficiency and accuracy through a real-time index recommendation and verification mechanism.

[0028] Optionally, the method further includes: obtaining an initial neural network model, iteratively training the initial neural network model to obtain the index determination model, wherein the iterative training includes: inputting the multidimensional feature vector into the initial neural network model to obtain a predicted index scheme, calculating a loss value based on a preset loss function, updating the initial neural network model according to the loss value, and stopping the iteration when a preset iteration stopping condition is met; the preset loss function includes a first weighted term corresponding to query time loss and a second weighted term corresponding to index resource loss, and the index resource loss is obtained by weighting storage space loss and update loss.

[0029] In this embodiment, to train the neural network model, a loss function is defined based on query performance (including execution time and resource consumption). Therefore, the preset loss function includes a first weighted term corresponding to query time consumption and a second weighted term corresponding to index resource consumption. Specifically, the loss function design for the first weighted term aims to train the model by minimizing the query execution time. This loss function can be expressed as: ; in, It is the execution time predicted by the model for the i-th query. This is the actual execution time of the query.

[0030] The loss function for the second weighted term is designed by minimizing disk space, CPU utilization, and index maintenance overhead. Furthermore, a total resource consumption loss function can be defined. Each part of the overhead has a corresponding weighting factor. Specifically, indexes occupy additional disk space, and storage space overhead can be estimated using the index size. For example, for an index... Storage space consumption It can be represented as: ; Here, DateSize(i) represents the data size of the fields involved in the index; log(RowCount(i)) is the logarithm of the number of rows in the table, used to approximate the storage complexity of the index structure.

[0031] Whenever a database write operation (insert, delete, or update) occurs, the relevant indexes need to be maintained. Maintenance overhead can be represented by the index update frequency and the amount of data. It can be represented as: ; in, This indicates the frequency of table updates involved in the index.

[0032] Finally, combining the above two parts, we form the total loss function of resource depletion, expressed as: ; Among them, α and β are adjustable hyperparameters used to balance storage space, maintenance overhead, and query optimization costs.

[0033] Integrating the first and second weighting terms above, the total loss function is designed as follows: Overall optimization goals: ; Here, α and β are the weighting coefficients of each loss term, used to control their impact on the total loss.

[0034] Load training data and perform data preprocessing. For example, after preprocessing the training data, a multi-dimensional feature vector X is obtained, including SQL query features, table structure features, and execution plan features. The feature vector X is input into the initial neural network model for forward propagation calculation. The forward propagation process passes through a series of non-linear layers to calculate the index recommendation score or execution time prediction. The error between the predicted value and the true value is calculated using the loss function L. Backpropagation is performed using gradient descent algorithms (such as Adam and SGD) to update the model's weight parameters and minimize the loss function. The model training process involves multiple iterations until the loss function converges or the predetermined number of training rounds is reached. The iteration is then stopped, and the index determination model is obtained.

[0035] This embodiment introduces a multi-objective joint optimization loss function design at the model training level. By weighting and combining query time consumption (performance objective) and index resource consumption (cost objective), and further decomposing resource consumption into storage space consumption and update and maintenance consumption, the trained index determination model no longer solely pursues query speed, but can intelligently balance query performance and system resource overhead. This ensures that the recommended indexing scheme is high-performance and sustainable in actual deployment, improving system resource utilization.

[0036] In some optional implementations, the iterative training of the initial neural network model to obtain the index determination model includes: acquiring at least two historical query statements, and acquiring the historical data table and execution plan information corresponding to each historical query statement; extracting features from each historical query statement and the corresponding historical data table and execution plan information to obtain a multi-dimensional feature vector; and inputting the multi-dimensional feature vector into the initial neural network model to perform the iterative training of the initial neural network model based on the multi-dimensional feature vector to obtain the index determination model.

[0037] Optionally, the historical query statement is an SQL query statement. The step of extracting features from each historical query statement, its corresponding historical data table, and execution plan information to obtain a multi-dimensional feature vector includes: parsing the SQL query statement into a syntax tree, extracting multiple types of syntax units, and constructing historical query features based on each syntax unit; extracting features from the historical data table to obtain historical table structure features and historical business features, and extracting features from the execution plan information to obtain historical execution features; and fusing the historical query features, historical table structure features, historical business features, and historical execution features to obtain the multi-dimensional feature vector.

[0038] For example, by using the query log files of the target database system, such as the MySQL query log, SQL statements executed within a historical time period are collected in batches as historical query statements. For each historical query statement, the basic structure information of all data tables involved in the query (i.e., historical data table information) is obtained through the MySQL metadata query interface, including table names, column names, column data types, and existing index definitions. For the same historical query statement, the database execution plan interpretation command, such as MySQL's EXPLAIN, is used to execute the statement to obtain detailed execution plan information, including access type, possible indexes used, actual indexes used, number of rows scanned, filtering ratio, and join type. At the same time, the actual performance indicators during the execution of the historical query statement are extracted from the database performance view or monitoring logs, including query time and resource consumption. Each SQL statement is parsed into an Abstract Syntax Tree (AST) using an SQL parser (such as one generated by ANTLR or a built-in database parsing library). Various syntactic units (such as table names, column names, filter conditions, and join conditions) are extracted from the AST. These syntactic units are then converted into fixed-length numerical vectors using word embedding techniques (such as Word2Vec or FastText) or one-hot encoding to obtain the query semantic feature vector. The obtained table structure information (number of columns, column type distribution, number of rows, data size) and existing index information (index type, included columns, uniqueness) are structured and encoded into numerical vectors to obtain the table structure feature vector. Extracting detailed features of the execution plan from the MySQL EXPLAIN results, such as the indexes used, the number of rows scanned, and the join type, and vectorizing the structured information output by EXPLAIN, yields historical execution characteristics. Furthermore, the business data in the data table includes payment channels, payment methods, payment status, geographical location, transaction time, account balance and transaction type, order ID, and user ID. The payment channels, payment methods, payment status, order ID, and user ID are extracted from the data table and then processed into structure vectors to obtain historical business features. The query feature vector, table structure feature vector, and execution plan feature vector obtained above are concatenated to obtain a unified, high-dimensional multidimensional feature vector, which serves as the input to the neural network model. This multidimensional feature vector is represented as follows: .

[0039] To accommodate the complexity of SQL query and database index optimization, the core of the model design is a multilayer perceptron (MLP) based on neural networks, used to learn the mapping relationship between SQL query features and index recommendations. The model consists of an input layer, multiple hidden layers, and an output layer. The input layer receives SQL query features and table structure features, forming an input vector X. The hidden layers use a multilayer fully connected network; each hidden layer can be viewed as a linear transformation plus a non-linear activation function. The calculation formula for the hidden layer is expressed as: ; in, It is the weight matrix of the Lth layer. σ is the bias coefficient, and σ is the activation function.

[0040] It should be noted that the first hidden layer receives the input vector X from the input layer, and the calculation formula is expressed as: ; After processing by each hidden layer, the output layer generates the prediction index vector. ,in, It includes at least two dimensions, such as the combination of indexed columns and the index type, and is represented as: ); The output format of the predicted index vector can be a combination of index columns, i.e., an output vector. For combinations of corresponding fields, use the softmax function to output a probability distribution and select the optimal column combination; alternatively, recommend index types, i.e., output a vector. For the corresponding index type (such as B-Tree, Hash), the classification results are generated using softmax.

[0041] The loss value between the predicted index vector and the executed feature vector is calculated according to a preset loss function, and the initial neural network model is iteratively trained based on the loss value to determine the model by iterating the index.

[0042] This embodiment constructs a high-quality, multi-dimensional foundation for model training data. By comprehensively utilizing historical query statements (semantics), historical data tables (structure), and historical execution plan information (performance feedback), multi-dimensional feature vectors of training samples are generated, enabling the model to learn the complete mapping relationship from query intent and data status to the final execution effect. This solves the problem of traditional methods having single features and difficulty in modeling complex causal relationships, providing sufficient and necessary supervision signals for the model to learn accurate index recommendation strategies.

[0043] Optionally, iteratively training the initial neural network model to obtain the index determination model includes: constructing a query structure graph based on the historical query statements and each of the historical data tables; constructing the initial neural network model by combining the multilayer perceptron and a preset graph neural network; inputting the query structure graph and multidimensional feature vectors into the initial neural network model to perform the iterative training on the initial neural network model according to the query structure graph and the multidimensional feature vectors to obtain the index determination model.

[0044] In this embodiment, GNN is a deep learning model for graph structure data, which is particularly suitable for modeling the relationship between tables and columns. In order to further improve the model performance, a graph neural network (GNN) can be combined with a multilayer perceptron to handle complex relationships between database tables (such as join relationships), especially for the association relationship between orders and users and payment tables in high-concurrency scenarios such as financial transactions.

[0045] Specifically, the process iterates through all historical query statements and their parsed results. For each query, based on the parsed table names, column names, join conditions, and filter conditions, a global or scenario-specific graph structure is dynamically updated: Data tables in the database and data columns involved in the query are defined as nodes in the graph structure, constructing a heterogeneous information network. For example, two types of nodes are established: table nodes and column nodes. The relationships between tables and columns (e.g., a column belongs to a certain table), the join relationships between tables (e.g., tables linked by foreign keys or frequently JOINed in historical queries), and the conditional relationships between columns (e.g., column pairs appearing in the same WHERE or JOIN ON clause) are defined as edges in the graph structure. After processing all historical query logs, a weighted and heterogeneous query structure graph reflecting historical query patterns and data pattern relationships is ultimately formed.

[0046] An initial neural network model is constructed by combining a multilayer perceptron (MLP) and a graph neural network (GNN). The MLP-based module is responsible for processing the non-graph structured multidimensional feature vectors extracted from a single query. This is used to capture the specific semantics of the query, the detailed structure of the table, and the detailed features of the execution plan. The GNN-based module is responsible for processing the query structure graph constructed in the previous step. This is used to learn the complex global topological relationships and business semantics between data tables and columns, and to capture implicit relationship patterns that are difficult to express by traditional features.

[0047] Specifically, the GNN module adopts a classic GNN architecture, such as Graph Convolutional Network (GCN), Graph Attention Network (GAT), or Message Passing Neural Network (MPNN). In each layer (or each iteration) t, each node i aggregates the feature information of its neighboring nodes j ∈ N(i). The aggregation function can be weighted summation (using an attention mechanism to dynamically calculate weights based on edge weights), mean pooling, or max pooling. The aggregated neighbor information is combined with the features of the node itself from the previous layer and updated through a learnable transformation matrix and activation function to generate a new feature representation of node i in the current layer. After propagation through several layers of GNN, the final vector representation of all nodes in the graph is obtained. In the feature fusion layer, for nodes directly related to the current training sample (a specific historical query statement) (e.g., all table nodes and column nodes involved in the query), their final node embedding vectors are extracted. These node embedding vectors are then pooled (e.g., summation, averaging, or attention-weighted summation) to generate a fixed-length global representation vector of the graph structure. The global representation vector of this graph structure is concatenated or weighted and fused with the feature vector obtained after MLP stream processing of multi-dimensional feature vectors to obtain a joint feature representation that integrates specific query details and global structural knowledge. This joint feature representation is then input into one or at least two fully connected layers (which can be considered an extension of MLP) and finally mapped to the output space to obtain the prediction indexing scheme.

[0048] The index determination model is obtained by combining the query structure graph and multidimensional feature vectors in the above iterative training. Specifically, each training sample is a quadruple (multidimensional feature vector, query structure graph, true index scheme label, true performance metric). The query structure graph can be a subgraph dynamically extracted based on the database scope involved in the query, or it can be a pre-constructed global graph. The multidimensional feature vector is input into the model's MLP stream to obtain feature vectors, and the query structure graph (or its subgraphs) is input into the model's GNN stream to obtain a global graph structure representation vector. Following the feature fusion layer method described above, the outputs of the two streams are fused, and the prediction result is obtained through subsequent networks. Using the same composite loss function as in the previous embodiment (including a weighted term for query time loss and index resource loss), the total loss Ltotal between the prediction result and the true label / performance metric is calculated. The gradient of Ltotal with respect to all trainable parameters in the model (including the weights of the MLP, the weights of the GNN, the weights of the fusion layer, and the output layer) is calculated using the backpropagation algorithm. All these parameters are updated based on the gradient using an optimizer (such as Adam). Repeat the above process, iterating multiple times over all batches of data in the training set. Stop training when the model's performance on the validation set no longer improves (or the total loss no longer decreases), or when the preset maximum number of iterations is reached. Save the model's parameters at this point; this is the final trained index determination model, capable of simultaneously understanding query details and global data relationships.

[0049] This embodiment proposes a hybrid neural network architecture for index recommendation models. By combining a multilayer perceptron, which excels at handling Euclidean structured data, and a graph neural network, which excels at handling non-Euclidean relational data, this architecture can simultaneously learn the local features of the query itself and the complex global topological features of relationships between data tables. This design enables the model to understand both specific query details and global data patterns, thus allowing it to make more collaborative and globally optimal decisions for index recommendation in complex scenarios involving multi-table joins and nested queries.

[0050] Optionally, the step of constructing a query structure graph based on the historical query statements and each of the data tables includes: using each of the data tables and the fields in each of the data tables as nodes, determining edges based on the connection conditions, filtering conditions, or grouping conditions in the target query statement, and constructing the query structure graph.

[0051] In this embodiment, a graph structure can be constructed for SQL queries and table structures. Nodes represent elements such as tables and columns, and edges represent relationships between them, such as joins, filters, and groupings. Each node carries table-related feature information, such as field types, index usage frequency, and query patterns. For example, for a join relationship between the order and user tables, the order and user tables can be considered as two nodes in the graph. By modeling the join between the order and payment tables, it's possible to predict which index combinations can optimize such join operations. Furthermore, the join weight between the order and user tables can be determined by their join query frequency or query cost.

[0052] GNN learns relationships between tables and predicts the optimal index recommendation scheme through information passing and aggregation between nodes. The state vector Hi of each node is updated based on information from its neighboring nodes, as shown in the following formula: ; in, It is the representation of node i at level t; represents the set of neighbors of node i; AGG represents the aggregation function of neighbor information (such as summation, averaging, etc.); W is a trainable weight matrix; It is an activation function (e.g., ReLU).

[0053] This embodiment provides a fine-grained, semantic graph structure construction method. By treating data tables and their internal fields as nodes, and defining edges based on specific semantic conditions such as joins, filters, and groupings in the query, the constructed query structure graph can accurately depict the data entities touched by a single query and the specific operational relationships between them. This fine-grained graph representation provides extremely rich and accurate input for graph neural networks, enabling them to learn micro-association patterns at the field and condition levels, potentially leading to more accurate composite or covering indexes.

[0054] In some optional implementations, the step of aggregating each of the target data tables to obtain target aggregation features that characterize the relationship between at least two of the target data tables includes: performing feature encoding on each target data table to obtain a corresponding node feature vector; constructing a data table association graph by using each target data table as a node and the business relationship between each target data table as an edge, combined with the node feature vectors corresponding to each data table; and aggregating the data table association graph to obtain the aggregation features.

[0055] In this embodiment, since data tables are often sharded and deployed across different nodes, in order to achieve index recommendation for cross-table join queries (such as order tables, user tables, and payment tables), it is necessary to effectively aggregate the features between tables distributed across different nodes. The cross-node feature aggregation mechanism is as follows: Within each shard node, the predefined lightweight neural network module Transformer Encoder is used to encode features such as columns, indexes, access frequency, and query history in the local table to obtain the initial vector representation of the table within the node: ; Among them, X V It can be the table structure (column type), access frequency, historical index coverage, or related fields, and can specifically encode business-critical fields such as userid, policyid, creditscore, and channelid.

[0056] Based on graph theory representation, a cross-node graph structure is abstracted to obtain a data table association graph used to represent business semantic relationships. The formula for the graph attention network is as follows: ; Where V is the set of nodes, E is the set of edges, { (v i ,v j The "|Business JOIN or Logical Dependency}" statement describes the rules for generating the edge set E.

[0057] Specifically, the node feature vectors of each target data table obtained in the previous step are associated with a graph node object, where each node uniquely represents a data table. Business relationships are determined based on predefined business rules or data models. For example, in a payment scenario, edges could be users <-> transactions <-> payment channels; in an insurance scenario, edges could be policies <-> claims <-> insured person; and in a credit purchase scenario, edges could be creditscore <-> orders <-> repayment plan. Alternatively, business relationships can be mined based on historical query patterns. For example, by analyzing historical query logs, the frequency of co-occurrence between pairs of data tables in JOIN operations is counted; if the frequency exceeds a certain threshold, a business relationship is considered to exist. A feature vector is attached to each edge Eij to quantify the strength or attribute of the relationship. After adding all nodes and edges, an attribute graph, namely the data table relationship graph, is constructed.

[0058] Feature aggregation is performed using distributed graph neural networks, such as graph convolutional networks or graph attention networks. Specifically, for a target data table node, the association strength coefficient between it and each associated neighbor node is calculated to obtain attention weights. Based on these weights, the feature vectors of all neighbor nodes are weighted and summed to obtain an intermediate vector that aggregates neighbor information. This weighted and summed vector is then subjected to a learnable linear transformation and processed by a non-linear activation function to generate the updated feature vector of the target node. After multiple rounds of the above steps, the features of each node ultimately encode the semantic information of its local graph structure, resulting in the aggregated features.

[0059] This embodiment defines a standard process for generating cross-table aggregated features during the inference phase. By encoding the target data table as nodes and constructing a graph based on predefined or mined business relationships, and then aggregating the graph, this method can dynamically synthesize a compact feature vector representing the complex relationships between multiple tables involved in the query without executing a query. This aggregated feature, as one of the key inputs to the index determination model, compensates for the inability of single-table features and query features to describe the interactions between tables, and is the core of handling complex relational queries.

[0060] Optionally, the aggregation process of the data table association graph to obtain the aggregation feature includes: calculating the edge weight based on the historical association query frequency or business impact factor between the data tables corresponding to each edge; inputting the edge weight and each node feature vector into a preset graph attention neural network to calculate the attention coefficient based on the edge weight and node features through the graph attention neural network, and aggregating each node feature vector according to the attention coefficient to obtain the aggregation feature.

[0061] In this embodiment, a preset weighted fusion processor is obtained, whose inputs are normalized historical connection frequencies and business impact factors, and whose output is the weight of the fused edges. Specifically, the weighted fusion processor is used to weight each edge E. ij The following attribute can be added as a weight: frequency of historical join queries f join (i,j), and the weights that influence the outcome in the business scenario (such as compensation amount, payment risk level). The calculated edge weights are used as the core components of the edge features, and together with the feature vectors of each node, they are input into the graph attention neural network mentioned in the previous embodiment to obtain aggregated features. The calculation formula of the graph attention neural network is expressed as: ; Where v is the node to be updated, u is the neighbor node of node v, and N(v) is the set of neighbors of node v. W is the feature vector of node v at layer l. (l) It is the trainable weight matrix of the l-th layer, used to perform linear transformation on the aggregated information. α uv σ is the attention coefficient, and σ is a non-linear activation function, such as ReLU or ELU.

[0062] This embodiment implements business knowledge-guided graph attention aggregation. By using edge weights calculated based on historical statistics (query frequency) and domain knowledge (business influencing factors) as prior information, and inputting them into the graph attention neural network, the network is "biased" or "guided" by these prior weights when calculating attention coefficients. This makes the feature aggregation process not treat all associations "equally," but rather selectively strengthen those association paths that are historically important or business-critical. As a result, the generated aggregated features are more business-oriented and historically experiential, improving the practicality and reliability of the recommendation results.

[0063] In some other optional implementations, the aggregation process of the data table association graph to obtain the aggregated features includes: fusing the target query features with the feature vectors of each node to obtain node fusion features; dynamically calculating the attention coefficients between the node fusion features through a graph attention neural network and performing feature aggregation to obtain the aggregated features.

[0064] In this embodiment, the feature vectors of each data table node and the target query feature vector are fused through cross-attention, concatenation, or transformation to obtain fused features. Based on the fused features, the attention scores of the edges are calculated using the graph attention neural network mentioned in the previous embodiment, normalized to obtain attention coefficients, and weighted aggregation is performed. Through multi-layer stacking, information propagates along the path most relevant to the query semantics, and the target query features are introduced into the final layer node features for attention pooling to obtain aggregated features.

[0065] This embodiment implements query semantic awareness-based dynamic graph aggregation. By pre-fusing the target query features with the features of each node, each node's representation "carries" the intent information of the current query. The graph attention aggregation based on this foundation calculates attention coefficients that are dynamically determined by the relevance between nodes in the current query context. This achieves "on-demand aggregation," meaning that for different queries, even on the same graph, different feature representations are aggregated, making the aggregated features highly adaptable to the immediate needs of the current query, achieving highly personalized and contextualized recommendations.

[0066] like Figure 3 As shown, a comparison chart of indexing results is provided to characterize different indexing schemes obtained under the application of existing technology and the present invention for the same query statement. The left side of the chart represents the existing technology, and the right side represents the present invention. Furthermore, the query performance comparison results of the existing technology and the present invention in this scenario are shown in Tables 1-3 below. As can be seen from the tables, the average query time before optimization using this scheme is 500ms, and the average query time after optimization is 300ms, which means it can process 40% more query requests per second (500-300) / 500*100%. By recommending a more accurate and compact index structure, unnecessary index storage space is reduced, lowering storage costs. The current database index occupies 100TB of storage space; after optimization, the index storage space is reduced by 20%. This saves 20TB of storage space annually; calculated at 1000 yuan per TB per year, the estimated storage cost saving is 2000 yuan. The index recommendation method can reduce the workload of database administrators in manually optimizing indexes, lowering maintenance costs. By reducing the creation and maintenance of invalid or redundant indexes, database storage space and I / O load are reduced, saving resources. This solution can reduce the workload of database administrators in index optimization and maintenance by 30% annually. Based on an annual cost of 500,000 yuan per person, it is estimated that the labor cost will be reduced by 150,000 yuan.

[0067] Table 1 Query Performance Comparison Table

[0068] Table 2. Comparison of Index Maintenance Costs

[0069] Table 3. Efficiency Comparison of Cross-Shard Joins

[0070] Please see Figure 4 , Figure 4 This is a schematic diagram of the structure of an index determination device 400 for database query scenarios provided in an embodiment of this application. As shown in the figure, the index determination device 400 for database query scenarios includes: Feature module 401 is used to obtain a target query statement and at least two target data tables corresponding to the target query statement, and to extract features from the target query statement and the target data tables respectively to obtain target query features and target table structure features; Aggregation module 402 is used to perform aggregation processing on each of the target data tables to obtain target aggregation features that characterize the relationship between at least two of the target data tables; The index module 403 is used to input the target query features, target table structure features and target aggregation features into the index determination model to obtain the target index scheme, so as to perform database queries according to the target index scheme to obtain the target query results.

[0071] The index determination device 300 for database query scenarios provided in this application embodiment can perform the above-described... Figure 1 The method embodiments shown are similar in principle and technical effect, and will not be described again here.

[0072] This application also provides an electronic device. Since the principle by which this electronic device solves the problem is similar to the index determination method for database query scenarios in this application, the implementation of this electronic device can be found in [reference needed]. Figure 1 The implementation of the method shown will not be repeated here. Figure 5 As shown, the electronic device 500 of this application embodiment includes: a processor 510, configured to read a program from a memory 520 and execute the following processes: Among them, Figure 5 In this context, the bus architecture can include any number of interconnected buses and bridges, specifically linking various circuits of one or more processors represented by processor 510 and memory represented by memory 520 together. The bus architecture can also link various other circuits such as peripheral devices, voltage regulators, and power management circuits, which are well known in the art and therefore will not be described further herein. The bus interface provides the interface.

[0073] The electronic device provided in this application embodiment can execute all the above-described method embodiments, and its implementation principle and technical effect are similar, so it will not be described again here.

[0074] This application also provides a computer-readable storage medium storing a computer program, which, when executed by a processor, performs the above-described... Figure 1 The various processes in the implementation of the index determination method for database query scenarios are all described above, and since they achieve the same technical effect, they will not be repeated here to avoid repetition. The computer-readable storage medium mentioned above includes, for example, read-only memory (ROM), random access memory (RAM), magnetic disk, or optical disk.

[0075] This application embodiment also provides a computer program / program product, which is stored in a storage medium and executed by at least one processor to implement the above. Figure 1 The various processes of the index determination method embodiment for database query scenarios described herein can achieve the same technical effect, and will not be repeated here to avoid duplication.

[0076] In the several embodiments provided in this application, it should be understood that the disclosed methods and apparatus can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between devices or units may be electrical, mechanical, or other forms.

[0077] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can be physically included separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or in the form of hardware plus software functional units.

[0078] The integrated units implemented as software functional units described above can be stored in a computer-readable storage medium. These software functional units, stored in a storage medium, include several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute some steps of the transmission and reception methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0079] The above description is the preferred embodiment of this application. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principles described in this application, and these improvements and modifications should also be considered within the scope of protection of this application.

Claims

1. A method for determining an index for database query scenarios, characterized in that, include: Obtain the target query statement and at least two target data tables corresponding to the target query statement, and perform feature extraction on the target query statement and the target data tables respectively to obtain the target query features and the target table structure features; The target data tables are aggregated to obtain target aggregation features that characterize the relationship between at least two of the target data tables. The target query features, target table structure features, and target aggregation features are input into the index determination model to obtain the target index scheme, and the database query is performed according to the target index scheme to obtain the target query results.

2. The method according to claim 1, characterized in that, The method further includes: Obtain an initial neural network model, and iteratively train the initial neural network model to obtain the index determination model, wherein the iterative training includes: The loss value is calculated based on a preset loss function, and the initial neural network model is updated according to the loss value until the preset iteration stopping condition is met, at which point the iteration stops. The preset loss function includes a first weighted term corresponding to query time loss and a second weighted term corresponding to index resource loss. Furthermore, the index resource loss is obtained by weighting storage space loss and update loss.

3. The method according to claim 2, characterized in that, The iterative training of the initial neural network model to obtain the index determination model includes: Obtain at least two historical query statements, and obtain the historical data table and execution plan information corresponding to each historical query statement respectively; For each historical query statement and its corresponding historical data table and execution plan information, feature extraction is performed to obtain a multi-dimensional feature vector; The multidimensional feature vector is input into the initial neural network model to perform iterative training on the initial neural network model based on the multidimensional feature vector, thereby obtaining the index determination model.

4. The method according to claim 2, characterized in that, The iterative training of the initial neural network model to obtain the index determination model includes: Based on the historical query statements and each of the historical data tables, a query structure diagram is constructed. The initial neural network model is obtained by combining a multilayer perceptron and a pre-defined graph neural network; The query structure graph and the multidimensional feature vector are input into the initial neural network model to perform iterative training on the initial neural network model based on the query structure graph and the multidimensional feature vector, thereby obtaining the index determination model.

5. The method according to claim 4, characterized in that, The process of constructing a query structure diagram based on the historical query statements and each of the data tables includes: Using each of the data tables and fields in each of the data tables as nodes, edges are determined based on the connection conditions, filtering conditions, or grouping conditions in the target query statement, and the query structure graph is constructed.

6. The method according to any one of claims 3-5, characterized in that, The historical query statements are SQL query statements. Feature extraction is performed on each historical query statement, along with its corresponding historical data table and execution plan information, to obtain a multi-dimensional feature vector, including: The SQL query statement is parsed into a syntax tree, multiple types of syntax units are extracted, and historical query features are constructed based on each syntax unit. Feature extraction is performed on the historical data table to obtain historical table structure features and historical business features, and feature extraction is performed on the execution plan information to obtain historical execution features; The multidimensional feature vector is obtained by integrating the historical query features, historical table structure features, historical business features, and historical execution features.

7. The method according to claim 1, characterized in that, The aggregation process of each of the target data tables to obtain target aggregation features used to characterize the relationship between at least two of the target data tables includes: Each target data table is feature-encoded to obtain the corresponding node feature vector; Using each of the target data tables as nodes and the business relationships between the target data tables as edges, and combining the node feature vectors corresponding to each data table, a data table association graph is constructed. The data table association graph is aggregated to obtain the aggregated features.

8. The method according to claim 7, characterized in that, The aggregation process of the data table association graph to obtain the aggregation features includes: The edge weight is calculated based on the historical association query frequency or business impact factor between the data tables corresponding to each edge. The edge weights and the feature vectors of each node are input into a preset graph attention neural network to calculate attention coefficients based on the edge weights and node features. The attention coefficients are then used to aggregate the feature vectors of each node to obtain the aggregated features.

9. The method according to claim 7, characterized in that, The aggregation process of the data table association graph to obtain the aggregation features includes: The target query features are fused with the feature vectors of each node to obtain the node fused features; The attention coefficients between the fused features of each node are dynamically calculated using a graph attention neural network, and the features are aggregated to obtain the aggregated features.

10. An index determination device for database query scenarios, characterized in that, include: The feature module is used to obtain the target query statement and at least two target data tables corresponding to the target query statement, and to extract features from the target query statement and the target data tables respectively to obtain target query features and target table structure features; An aggregation module is used to perform aggregation processing on each of the target data tables to obtain target aggregation features that characterize the relationship between at least two of the target data tables. The indexing module is used to input the target query features, target table structure features, and target aggregation features into the index determination model to obtain a target indexing scheme, and to perform database queries based on the target indexing scheme to obtain target query results.

11. An electronic device, characterized in that, include: A processor, a memory, and a program stored in the memory and executable on the processor, wherein the program, when executed by the processor, implements the steps of the index determination method for a database query scenario as described in any one of claims 1 to 9.

12. A computer-readable storage medium, characterized in that, When the computer program is executed by the processor, it implements the steps in the index determination method for database query scenarios as described in any one of claims 1 to 9.

13. A computer program product, characterized in that, It includes computer instructions that, when executed by a processor, implement the steps in the index determination method for database query scenarios as described in any one of claims 1 to 9.

Images