Database index generation method and device, equipment, medium and program product

Abstract

The invention provides a database index generation method which can be applied to the technical field of big data. The database index generation method comprises the steps that in response to monitoring that a database query using a first index is a slow query, a query statement of the slow query is input into a large language model, semantic analysis is carried out according to the large language model to determine a first feature, and the slow query is a database query with query parameters abnormal; generating a feature vector according to the first feature, and inputting an index recommendation model to obtain a second index; and replacing the first index according to the second index so as to perform database query operation according to the first index. The invention further provides a database index generation device and equipment, a storage medium and a program product.

Description

Technical Field

[0001] The present application relates to the field of big data, and specifically to a database index generation method, apparatus, device, medium and program product. Background Art

[0002] In financial business scenarios, databases are increasingly used to store and query data. Indexes are crucial tools for database queries. If indexes are not well constructed, slow queries often occur when querying the database. Slow queries can be defined as queries whose response time exceeds a set time threshold or whose resource usage exceeds a predetermined threshold when executed in the database.

[0003] In related technologies, after identifying a slow query, database engineers typically analyze the execution plan to examine whether full table scans, inefficient joins, and the use of sorting and aggregate functions are involved. Subsequently, new indexes are set up and added. However, this approach requires data engineers to manually create and adjust indexes, which is time-consuming and error-prone. This approach, especially in large-scale and complex database environments, cannot meet database query and usage requirements.

[0004] Therefore, a more efficient and intelligent database index generation method is needed. Summary of the Invention

[0005] In view of the above problems, the present application provides an index generation method, apparatus, device, medium and program product.

[0006] According to a first aspect of the present application, a database index generation method is provided, comprising: in response to detecting that a database query using a first index is a slow query, inputting the query statement of the slow query into a large language model, performing semantic analysis based on the large language model to determine a first feature, wherein the slow query is a database query with abnormal query parameters; generating a feature vector based on the first feature, and inputting the feature vector into an index recommendation model to obtain a second index; and replacing the first index based on the second index so as to perform a database query operation based on the first index.

[0007] According to an embodiment of the present application, the query statement of the slow query is input into a large language model, and a semantic analysis is performed based on the large language model to determine a first feature, including: performing a semantic analysis on the query statement to determine at least one operator; determining at least one operation object based on the operator; and determining the operator and the operation object as the first feature.

[0008] According to an embodiment of the present application, a feature vector is generated based on the first feature, and an index recommendation model is input to obtain a second index, including: determining a column for generating an index based on the feature vector; determining a setting method of the index based on the column for generating the index; and generating a second index based on the column for generating the index and the setting method of the index.

[0009] According to an embodiment of the present application, the method further includes: obtaining a monitoring log including multiple slow queries, and performing statistics on information of multiple slow queries for the same data based on the monitoring log to determine aggregate information, wherein the aggregate information includes at least one of the frequency of the query, the average execution time, and the number of affected rows; determining a second feature based on the aggregate information, and generating a feature vector for inputting into an index recommendation model.

[0010] According to an embodiment of the present application, the method further includes: obtaining metadata of the table and column corresponding to the slow query, determining a third feature based on the metadata; and generating a feature vector for inputting into an index recommendation model based on the third feature.

[0011] According to an embodiment of the present application, the method further includes: obtaining index performance evaluation data of the first index of the slow query, generating a fourth feature based on the index performance evaluation data; and generating a feature vector for inputting an index recommendation model based on the fourth feature.

[0012] According to an embodiment of the present application, it also includes: determining the dependency relationship of the target data of the database query based on the large language model; determining the index problem that causes slow query during the query process based on the dependency relationship; generating a first index optimization suggestion based on the index problem; and generating a third index based on the first index optimization suggestion and the second index.

[0013] According to an embodiment of the present application, it also includes: determining index performance for a database query using the second index; generating a second index optimization suggestion based on the index performance; and generating a fourth index based on the second index optimization suggestion and the second index.

[0014] A second aspect of the present application provides a database index generation device, including: a feature engineering module, for, in response to detecting that a database query using a first index is a slow query, inputting the query statement of the slow query into a large language model, performing semantic analysis based on the large language model to determine a first feature, wherein the slow query is a database query with abnormal query parameters; an index recommendation module, for generating a feature vector based on the first feature, and inputting the feature vector into the index recommendation model to obtain a second index; and a system management module, for replacing the first index based on the second index, so as to perform a database query operation based on the first index.

[0015] The third aspect of the present application provides an electronic device, comprising: one or more processors; a memory for storing one or more computer programs, wherein the one or more processors execute the one or more computer programs to implement the steps of the above method.

[0016] The fourth aspect of the present application further provides a computer-readable storage medium having a computer program or instructions stored thereon, which implements the steps of the above method when the computer program or instructions are executed by a processor.

[0017] The fifth aspect of the present application further provides a computer program product, comprising a computer program or instructions, which implement the steps of the above method when executed by a processor. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] The above contents and other objects, features and advantages of the present application will become more apparent through the following description of the embodiments of the present application with reference to the accompanying drawings, in which:

[0019] Figure 1 A diagram schematically illustrates an application scenario of a database index generation method, apparatus, device, medium, and program product according to an embodiment of the present application;

[0020] Figure 2 A schematic diagram of a database query system according to an embodiment of the present application is schematically shown;

[0021] Figure 3 The following schematically shows a flow chart of a method for generating a database index according to an embodiment of the present application;

[0022] Figure 4 A schematic diagram of a structure of a database index generating device according to an embodiment of the present application is shown; and

[0023] Figure 5 A block diagram of an electronic device suitable for implementing a database index generation method according to an embodiment of the present application is schematically shown. DETAILED DESCRIPTION

[0024] Hereinafter, embodiments of the present application will be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of the present application. In the detailed description below, for ease of explanation, many specific details are set forth to provide a comprehensive understanding of the embodiments of the present application. However, it is apparent that one or more embodiments may also be implemented without these specific details. In addition, in the following description, descriptions of known structures and technologies are omitted to avoid unnecessarily confusing the concepts of the present application.

[0025] The terms used herein are only for describing specific embodiments and are not intended to limit this application. The terms "comprise," "include," etc. used herein indicate the presence of the features, steps, operations, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, or components.

[0026] All terms used herein (including technical and scientific terms) have the meanings commonly understood by those skilled in the art unless otherwise defined. It should be noted that the terms used herein should be interpreted as having a meaning consistent with the context of this specification and should not be interpreted in an idealized or overly rigid manner.

[0027] When expressions such as "at least one of A, B, and C, etc." are used, they should generally be interpreted in accordance with the meaning commonly understood by those skilled in the art (for example, "a system having at least one of A, B, and C" should include but is not limited to a system having A alone, B alone, C alone, A and B, A and C, B and C, and / or A, B, C, etc.).

[0028] It should be noted that the database index generation method, apparatus, device, medium, and program product identified in this disclosure can be used in the fields of database technology and financial technology, and can also be used in various fields other than the fields of database technology and financial technology. The application fields of the database index generation method, apparatus, device, medium, and program product provided in the embodiments of this disclosure are not limited.

[0029] In the technical solution of this application, the user information involved (including but not limited to user data, user personal information, user image information, user device information, such as location information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) are all information and data authorized by the user or fully authorized by all parties, and the collection, storage, use, processing, transmission, provision, disclosure and application of the relevant data comply with relevant laws, regulations and standards, take necessary confidentiality measures, do not violate public order and good morals, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0030] In the scenario of using personal information for automated decision-making, the methods, devices, and systems provided in the embodiments of the present application all provide users with corresponding operation portals for users to choose to agree or reject the automated decision-making results; if the user chooses to reject, the expert decision-making process will be entered. The expression "automated decision-making" here refers to the activity of automatically analyzing and evaluating an individual's behavioral habits, interests and hobbies, or economic, health, credit status, etc. through computer programs and making decisions. The expression "expert decision-making" here refers to the activity of making decisions by people who specialize in a certain field, have specialized experience, knowledge and skills, and have reached a certain level of professionalism.

[0031] The embodiments of the present application provide a method and apparatus for generating a database index.

[0032] Figure 1 The application scenario diagram of the database index generation method and device according to the embodiment of the present application is schematically shown.

[0033] like Figure 1 As shown, the application scenario 100 according to this embodiment may include a first terminal device 101, a second terminal device 102, a third terminal device 103, a network 104, and a server 105. The network 104 is used as a medium for providing a communication link between the first terminal device 101, the second terminal device 102, the third terminal device 103, and the server 105. The network 104 may include various connection types, such as wired or wireless communication links or optical fiber cables.

[0034] A user may use a first terminal device 101, a second terminal device 102, or a third terminal device 103 to interact with a server 105 via a network 104 to receive or send messages, etc. Various communication client applications may be installed on the first terminal device 101, the second terminal device 102, or the third terminal device 103, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, etc. (for example only).

[0035] The first terminal device 101 , the second terminal device 102 , and the third terminal device 103 may be various electronic devices having display screens and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, desktop computers, and the like.

[0036] The server 105 may be a server that provides various services, such as a background management server (for example only) that supports websites browsed by users using the first terminal device 101, the second terminal device 102, and the third terminal device 103. The background management server may analyze and process received data such as user requests, and feed back processing results (e.g., web pages, information, or data obtained or generated based on user requests) to the terminal devices.

[0037] It should be noted that the database index generation method provided in the embodiment of the present application can generally be executed by the server 105. Accordingly, the database index generation device provided in the embodiment of the present application can generally be set in the server 105. The database index generation method provided in the embodiment of the present application can also be executed by a server or server cluster that is different from the server 105 and can communicate with the first terminal device 101, the second terminal device 102, the third terminal device 103 and / or the server 105. Accordingly, the database index generation device provided in the embodiment of the present application can also be set in a server or server cluster that is different from the server 105 and can communicate with the first terminal device 101, the second terminal device 102, the third terminal device 103 and / or the server 105.

[0038] It should be understood that Figure 1 The number of terminal devices, networks and servers in the embodiment is merely illustrative. Any number of terminal devices, networks and servers may be provided as required.

[0039] The following will be based on Figure 1 The scene described by Figures 2 to 4 A method for generating a database index according to an embodiment of the present application is described in detail.

[0040] Figure 2 The following schematically shows a database query system according to an embodiment of the present application. Figure 2 As shown, the database query system 200 includes a database 210, a large language model 220, a data asset management platform 230, a big data development platform 240, a financial data analysis system 250 and an operation governance platform 260.

[0041] According to one embodiment, database 210 can be implemented as a distributed database cluster, which may include multiple distributed data servers. Database 210 can also be implemented as a massively parallel processing database (MPP). MPP is a distributed database system used to store and process large amounts of data, improving performance and scalability by executing complex query operations in parallel on multiple server nodes. A distributed database cluster refers to a database system in which data is physically distributed but logically centrally managed. Distributed databases are increasingly widely used in the field of financial technology. For example, analytical distributed databases are widely used in key business scenarios such as data warehouses, data marts, real-time analysis, real-time decision-making, and mixed loads that support massive amounts of data in the financial industry. Taking database 210 as an example, database 210 may specifically include multiple database clusters (Massively Parallel Processing Data Base Cluster, MPPDB Cluster), such as MPPDB Cluster A, MPPDB Cluster B, and MPPDB Cluster C.

[0042] Database 210 handles all data storage and query operations. Financial data analysis system 250 can store and query data from database 210, performing complex financial analysis based on the queried data to support business analysts' decision-making. Financial data analysis system 250 also provides query performance feedback to ensure optimized query performance during the analysis process. For example, query problems encountered during data querying are fed back to the operations governance platform 260. Query problems can specifically include slow queries when querying specific data.

[0043] Operations and governance platform 260 monitors database operations, assesses database query performance, identifies and reports performance issues, and, for example, receives query problem feedback from financial data analysis system 250 and sends database queries requiring analysis to large language model 220. Database 210 also provides real-time query execution data to operations and governance platform 260, such as sending database query execution data to operations and governance platform 260. Operations and governance platform 260 obtains query execution data from database 210 and also sends this query execution data to large language model 220.

[0044] The large language model 220 can be implemented as a deep learning model with hundreds of billions of parameters. This application does not limit the specific implementation of the large language model 220. A large language model (LLM) is a deep learning model trained using large-scale datasets that can understand and generate natural language. In the present invention, the LLM is used to deeply analyze database queries and provide insights for query optimization. After receiving database query and query execution data from the operations governance platform 260, the large language model 220 performs in-depth analysis and feeds the analysis results back to the data asset management platform 230 and the big data development platform 240.

[0045] The data asset management platform 230 manages and analyzes metadata in the database 210, optimizing the data structure and indexing. The data asset management platform 230 receives data from the database 210 and the operation and governance platform 260, and sends optimization suggestions to the database 210 based on the analysis results.

[0046] The big data development platform 240 provides data analysts and developers with data processing tools and environments for developing and testing data-driven applications. Its functions include developing new data processing methods and optimization strategies using the analysis results of the big language model 220 and implementing optimization recommendations from the data resource management platform 230.

[0047] The six modules in the database query system 220 form a closely coordinated system that optimizes the performance of the entire database and analysis platform through interaction and data sharing. Each module has a specific function, and together they improve the data processing capabilities and decision-making efficiency of the entire query system.

[0048] Figure 3 The flowchart of the method for generating a database index according to an embodiment of the present application is schematically shown. Figure 3 As shown, step 310 is first executed. In response to detecting that the database query using the first index is a slow query, the query statement of the slow query is input into the large language model, and a semantic analysis is performed based on the large language model to determine the first feature.

[0049] According to one embodiment of the present application, the database 210 is adapted to store data and provide services such as querying the stored data. The stored data may specifically be data in the financial field, and the present application does not impose any restrictions on the specific data type of the stored data.

[0050] The present application can automatically identify slow queries when the database 210 queries data. Query parameter anomalies refer to query execution time exceeding a predetermined threshold, abnormal resource usage during the query, etc. Abnormal resource usage includes resource usage exceeding a preset threshold, specifically the number of CPU cores used being greater than 8 or the usage ratio exceeding 30%, etc. The database 210 may include one or more database clusters, and a distributed monitoring agent may be set corresponding to the database, that is, a monitoring agent is set on each database cluster. For example, the database 210 may specifically include multiple database clusters (MPPDB Cluster), such as MPPDBClusterA, MPPDB ClusterB, and MPPDB ClusterC, etc., and monitoring agents may be set separately in MPPDB ClusterA, MPPDB ClusterB, and MPPDB ClusterC, etc.

[0051] The monitoring agent installed on the database cluster can collect monitoring logs in real time. The monitoring agent can be configured with monitoring scripts and database triggers. The database triggers can be used to identify database queries and trigger the execution of monitoring scripts to collect monitoring logs in real time. The monitoring log includes database queries for the database cluster; further, the recorded database queries include the full text of each query, the execution start and end timestamps, the CPU time consumed, the amount of data read and write operations, and other key performance indicators. This application does not limit the specific content of the database query.

[0052] Subsequently, the monitoring logs collected by the monitoring agent can be collected together and processed. One implementation method is that the monitoring agent transmits the monitoring log to the operation governance platform 260. The processing steps include: analyzing each database query in the monitoring log to determine whether it meets the criteria for slow queries, such as whether the query execution time exceeds a predetermined threshold, whether the resource usage during the query is abnormal, etc. These criteria can be customized, such as flexibly setting thresholds and abnormal value ranges to capture slow queries. For example, thresholds can be set according to different business requirements of database queries. Thresholds can also be set separately for each database cluster, such as based on the system performance historical data of the database cluster to identify slow queries that may affect system performance.

[0053] After identifying slow queries in all monitoring logs, all logs identified as slow queries can be further aggregated and organized. This means that multiple database queries for the same data item are counted to obtain aggregate information. The aggregate information includes statistical data such as query frequency, average execution time, and number of rows affected. This application does not limit the specific content of the aggregate information.

[0054] In the prior art, when identifying slow queries, database analysts need to monitor and analyze query logs through the database's built-in tools, and manually identify slow queries whose execution time exceeds a predetermined threshold. This method relies on manual experience, has a long processing time, and is inefficient. However, the technical problem solved by the present application by setting up a monitoring agent is how to improve the efficiency and accuracy of determining slow queries. The technical effect achieved is that the slow queries when the database 210 queries data can be automatically and accurately identified, so as to optimize the first index used for these slow queries, reduce manual intervention, and be more efficient. By accurately and quickly identifying slow queries, real-time and accurate data support is provided for subsequent steps, laying a solid foundation for further in-depth analysis and index optimization.

[0055] Big data platforms in the financial industry often use MPPDB as the core technology for their data warehouses to accommodate massive amounts of financial information. As business demands for processing various types of financial data continue to grow, slow queries have become increasingly prominent. Slow queries not only impact system performance but also reduce user experience and business processing efficiency. Traditional manual analysis and optimization methods are no longer sufficient to address the massive volume of slow queries, necessitating the introduction of automated, intelligent solutions. Slow queries can sometimes be caused by poor quality of the primary index used during querying. A database index is a data structure used within a database management system to accelerate data retrieval operations. While indexes can significantly improve query speed, they can also increase the overhead of writing, updating, and maintaining data.

[0056] This application addresses the common slow query issues in ultra-large-scale MPPDB distributed database clusters in the financial industry, built on a database kernel. It proposes an intelligent index recommendation solution that combines large language models with advanced machine learning algorithms. This solution aims to optimize query performance through automated index management, thereby improving the overall response speed and processing efficiency of the database system.

[0057] According to an embodiment of the present application, the present application uses a large language model 220 to extract features of the database query to help obtain a recommended second index. The large language model can be trained using methods such as supervised fine-tuning and reinforcement learning based on pre-trained data. This application does not limit the specific training method of the large language model.

[0058] Before inputting slow queries into the large language model, the database query statements are preprocessed, including standardizing the query statements to obtain standardized query statements. For example, redundant spaces and special characters are removed, and the SQL syntax is standardized to meet the input requirements of the large language model.

[0059] Inputting a query statement of a slow query into a large language model, and performing semantic analysis based on the large language model to determine a first feature, including: performing semantic analysis on the query statement to determine at least one operator; determining at least one operation object based on the operator; and determining the operator and the operation object as the first feature.

[0060] The large language model receives slow query information, including query statements (such as standardized query statements). It then performs semantic parsing and structural analysis on the query statements, using its advanced natural language processing capabilities to conduct in-depth semantic analysis of the query statements, identifying key operators such as SELECT, JOIN, WHERE, and ORDER BY. Based on the semantic analysis results, it then extracts the primary features relevant to retrieval performance optimization, such as the operation object corresponding to the operator, which can be a data table, data row, or data column in the database.

[0061] This application uses a large language model 220 to extract features of database queries to help obtain a recommended second index. The technical problem it solves is how to more efficiently and accurately determine the index used for database queries. The technical effect achieved is to optimize retrieval performance by mining features that affect query performance in slow queries, determining excellent indexes.

[0062] Then, step 320 is executed to generate a feature vector based on the first feature, and input the feature vector into the index recommendation model to obtain a second index.

[0063] The large language model mines the specific, deeper first features of each slow query, including but not limited to the access frequency, data size, execution cost, data distribution, and dirty page rate of the tables and columns involved in the query. After generating the first features, a feature vector can be generated based on the first features.

[0064] The present application also generates a second feature. To generate the second feature, monitoring logs containing multiple slow queries are obtained. Based on the monitoring logs, information about multiple slow queries targeting the same data item is collected to determine aggregate information. The aggregate information includes at least one of the following: query frequency, average execution time, and number of rows affected. The present application does not limit the specific content of the aggregate information. The second feature can then be determined based on the aggregate information to generate a feature vector for input into the index recommendation model.

[0065] The first feature is to perform statistics on multiple slow queries to obtain query features in the time dimension, which can represent historical execution information, such as the frequency of queries, average execution time, number of affected rows, etc. in multiple historical queries. These features (including the first feature and the second feature) are encoded into feature vectors and prepared for subsequent machine learning supervised learning model training and index recommendation. The trained supervised learning model can be used as an index recommendation model. By mining the features of different dimensions of slow queries, this application further solves the technical problem of how to recommend more accurate indexes. The technical effect achieved based on these features is to improve the accuracy of locating the root cause of slow queries and recommend more accurate indexes.

[0066] This application also includes: determining the dependencies of the target data of the database query based on the large language model; determining the index problem that causes slow query during the query process based on the dependencies; generating a first index optimization suggestion based on the index problem; generating a third index based on the first index optimization suggestion and the second index. The large language model also performs structural analysis on the query statement, further analyzes the dependencies and data flow in the query, and identifies index problems that cause performance bottlenecks in slow queries, such as unindexed columns participating in JOIN or WHERE conditions, or large amounts of data involved in data aggregation and sorting operations. The first index optimization suggestion is then generated based on the structural analysis results, including recommendations to create indexes on specific columns, adjust the query structure to reduce resource consumption, etc. These optimization suggestions are sent back to the operation governance platform along with the feature vector for further index recommendation and verification. This application uses a large language model to analyze the memory structure of query statements and obtain optimization suggestions. The technical problem it solves is how to improve the accuracy of index recommendations. The technical effect achieved is: the generated optimization suggestions can be verified with the second index generated by the index recommendation model, and can also assist in the training of the index recommendation model and fine-tuning after specific operation. For example, the index recommendation algorithm is adjusted according to the first index optimization suggestion, thereby improving the index recommendation generation model, or a third index is generated according to the first index optimization suggestion and the second index initially generated by the index recommendation model, thereby improving the accuracy of index recommendations. When generating the third index, the second index can be modified according to the index optimization suggestion, such as modifying the index method, changing the traversal index to a hash index, etc. This application does not limit the specific method of modifying the index.

[0067] The role of the large language model in this application is to receive slow query data from the operations governance platform, conduct in-depth analysis, and parse the complex structure and semantics of query statements to better understand the essential requirements and performance bottlenecks of the queries. In subsequent steps, the operations governance platform receives feature vectors and optimization suggestions from the large language model, combines them with data provided by other modules, and uses machine learning and supervised learning algorithms to generate specific index recommendations, ensuring the scientific nature and adaptability of the query optimization suggestions.

[0068] According to an embodiment of the present application, the operation governance platform further performs feature processing, including further processing data to extract features to construct feature vectors for machine learning supervised learning model training.

[0069] According to one embodiment, metadata for the tables and columns corresponding to the slow query is also obtained, and a third feature is determined based on the metadata. A feature vector is generated based on the third feature for input into the index recommendation model. Metadata for the tables and columns associated with the slow query, such as data type, column size, and historical query frequency, can be obtained from the data asset management platform 230. This application does not limit the specific method for obtaining metadata.

[0070] According to one embodiment, index performance evaluation data of the first index of the slow query is also obtained, and a fourth feature is generated based on the index performance evaluation data; a feature vector for inputting the index recommendation model is generated based on the fourth feature. The index performance evaluation data includes the query response time, execution efficiency, resource consumption, etc. corresponding to the index. When obtaining the index performance evaluation data, it can be obtained from the big data development platform 240. The present application does not limit the specific method of obtaining the index performance evaluation data. According to one embodiment, when generating a feature vector, a feature vector can be generated based on at least one of the first feature, the second feature, the third feature, and the fourth feature. The generated feature vector represents the selectivity of the column of the slow query, the frequency of the column involved in the query, the number of rows in the table, and the specific database operations used in the query.

[0071] This application optimizes the feature set by acquiring historical performance data for metadata and indexes. The technical problem it solves is how to increase the richness of feature mining for slow queries. The resulting technical effect is that by generating a third feature, features can be mined at the metadata level; by generating a fourth feature, features can be mined at the index performance level, increasing the richness of feature mining. By enriching the feature set with the first, second, third, and fourth features, the accuracy and applicability of the generated index are improved.

[0072] The process of generating and optimizing feature sets in this application is called feature engineering. Feature engineering refers to the process of using domain knowledge in machine learning to select, modify, or create features that maximize the performance of machine learning algorithms. Feature engineering involves extracting useful features from database operation and query logs to train index recommendation models. Feature vectors processed through feature engineering are used in subsequent steps to train and validate machine learning models and generate accurate index configuration recommendations. This process ensures the scientific and practical nature of feature data, providing a solid data foundation for generating efficient index recommendations.

[0073] According to an embodiment of the present application, an index recommendation model for recommending indexes is provided. The core goal of the index recommendation model is to determine the most suitable column for indexing and generate a recommended index based on the column to maximize the access performance of a table in the target query environment.

[0074] When training the index recommendation model, we first prepare the training model data for supervised learning. This training model data consists of processed feature vectors containing key information extracted from historical query and index performance data of the MPPDB cluster. The data is divided into training and test sets. Typically, 70% of the data is used as the training set to train the index recommendation model, and the remaining 30% is used as the test set to verify the accuracy and generalization ability of the index recommendation model.

[0075] When training the index recommendation model, a suitable machine learning algorithm can be selected for training. This application does not limit the specific training algorithm selected. According to one embodiment, this application selects the distributed gradient boosting library (eXtreme Gradient Boosting, XGBoost) as the main training algorithm based on the nature and data characteristics of the recommendation index to be implemented. The distributed gradient boosting library is a decision tree algorithm based on gradient boosting, which is particularly suitable for processing large-scale data sets and can effectively process various types of features (including numerical and categorical types, etc.).

[0076] The training set data is then used to perform preliminary training of the index recommendation model. During this process, various parameters of the index recommendation model (such as the learning rate, tree depth, subsample size, etc.) are adjusted to optimize the predictive performance of the index recommendation model and prevent overfitting. The parameter adjustment process can use methods such as grid search or random search, combined with cross-validation, to find the optimal model parameters. This application does not limit the specific training method used.

[0077] After training the index recommendation model, use the test set data to evaluate its performance. Key evaluation metrics include precision, recall, and F1 score to ensure the model maintains good performance even on unseen data. Additionally, perform model interpretability analysis, such as feature importance assessment, to understand which features play a decisive role in index recommendations. This allows adjustments to be made to increase the weight of these features during training to optimize the performance of the index recommendation model.

[0078] After the index recommendation model is trained, features such as at least one of the first feature, the second feature, the third feature, and the fourth feature can be directly input to obtain a second index. When the features are input into the index recommendation model to obtain the second index, the column used to generate the index can be determined based on the feature vector; the index setting method can be determined based on the column used to generate the index; and the second index can be generated based on the column used to generate the index and the index setting method. The column used to generate the index can be a column that has already been created in the data table, or one or more columns can be newly created in the data table as columns for setting the index. The index setting method can be set according to the retrieval needs, such as setting the retrieval method, hash retrieval or traversal retrieval; the retrieval step size can also be set, such as when searching in an ascending sorted column, searching every five data points each time to determine whether the current traversal value is greater than the retrieval target value. If it is greater than the retrieval target value, the retrieval target value is searched between the previous traversal value and the current traversal value to improve retrieval efficiency, etc. This application does not limit the specific setting content of the retrieval method. This application can quickly and accurately generate effective indexes by determining the columns that can be used to generate indexes and the specific method of generating indexes, thereby improving the operability of generating indexes.

[0079] The index recommendation model can then be further tested. Based on the test results and business needs, it may be necessary to readjust and optimize the model. Once the model's performance meets the expected standards, the model is considered ready for actual index recommendations. Once the index recommendation model has been verified and optimized, it can be deployed to a production environment to begin providing index configuration recommendations for actual queries. These recommendations are automatically generated based on the model's predictions and are then tested in the field to verify their effectiveness.

[0080] In the prior art, database analysts are required to analyze the execution plan of slow queries and examine whether there are full table scans, inefficient join operations, and the use of sorting and aggregate functions. Index optimization is then performed, including: adding new indexes, creating new indexes for columns frequently used in filtering, joining, or sorting; modifying existing indexes, adjusting or rebuilding existing indexes, such as changing the order or type of index fields; deleting redundant indexes, and removing indexes that are no longer valid or not beneficial to performance. This method of optimizing indexes is highly expert-dependent, which limits the universality and scalability of the optimization process; the degree of automation is low: each step requires manual operation; the implementation efficiency is poor: the process of manually creating and adjusting indexes is time-consuming and prone to errors, especially when dealing with large-scale and complex database environments. In order to solve the technical problems existing in the prior art, the present application proposes an index recommendation model for implementing index recommendations, which achieves real-time performance improvement and query efficiency improvement, can quickly respond to changes in database performance, and adjust index strategies in real time to adapt to changing query requirements and data patterns; ensure that queries are executed using indexes as much as possible, reduce execution time, and improve user satisfaction and business process efficiency. According to an embodiment of the present application, after completing model training and preliminary recommendations, the recommended index (e.g., the second index) is implemented in a controlled environment and its actual impact on query performance is tested. This step is intended to ensure that the newly recommended index can actually improve query efficiency and does not negatively impact the database system before actual deployment.

[0081] First, create the index: Based on the recommended columns and index settings, create the recommended indexes in an isolated test environment. This test environment is typically a mirror or subset of the production environment to ensure the reliability of test results and the security of the application. Creating an index involves executing the corresponding SQL commands to add the new index. These commands accurately reflect the recommendations of the index recommendation model.

[0082] After creating the recommended indexes, we use a series of predefined benchmark queries to evaluate their effectiveness. These queries represent common query patterns in production environments. The test involves executing each query and recording key performance metrics, such as execution time, CPU and memory usage, and I / O read and write volume.

[0083] We then compare performance data before and after the index creation to analyze the effectiveness of the new index. Key metrics to watch include reduced query response time and improved resource efficiency. If the index causes performance degradation in certain queries or an abnormal increase in resource usage, this information will be recorded and fed into the index recommendation model for further analysis and adjustment.

[0084] One implementation also performs index tuning. Based on test results, adjustments to the index structure may be recommended, such as modifying the columns included in the index or changing the index type (e.g., switching from a balanced multi-way search tree query to a hash index query) to optimize performance. These tuning measures are re-tested as necessary to ensure that each adjustment yields positive results.

[0085] Once indexes have undergone multiple rounds of testing to verify that they improve performance without negative impact, they are marked as validated and ready for deployment in production. Validated index configurations and their corresponding performance improvement reports are used to coordinate the actual deployment process.

[0086] After thorough verification and testing to ensure the effectiveness and security of the recommended indexes, we will prepare to apply them to actual production environments to significantly improve query performance. This rigorous and detailed process ensures the stability of the database system and maximizes the optimization effect.

[0087] In the prior art, each step of adjusting the index requires manual execution. Testing and verifying the adjusted index requires database analysts to manually verify the effectiveness of the new index in a test environment, comparing execution time and resource usage before and after the indexing, resulting in low efficiency. This application, by automatically setting up a test environment and then testing recommended indexes within the test environment, can improve index testing efficiency and reduce reliance on manual labor.

[0088] Finally, step 330 is executed to replace the first index with the second index so that database query operations can be performed based on the first index, thereby improving database query efficiency. According to embodiments of the present application, the performance of the deployed new index (e.g., the second index) is continuously tracked and evaluated to ensure that the implementation of the new index produces the expected improvement in query performance in the production environment and to provide continuous optimization feedback to the system. The present application also includes: determining index performance for database queries using the second index; generating optimization recommendations for the second index based on the index performance; and generating a fourth index based on the second index optimization recommendations and the second index.

[0089] Once new indexes (such as a secondary index) are deployed in production, begin real-time monitoring of query performance impacted by these indexes. Monitor metrics such as query execution time, resource consumption (CPU, memory, I / O), and query frequency. Leverage integrated monitoring tools, such as performance dashboards, to display real-time performance data, making it easy to quickly identify any indexes that are not performing as expected.

[0090] Regularly analyze the collected performance data and evaluate the overall impact of the index. The analysis results help determine which indexes have significantly improved performance and which may need further adjustment or removal. Based on the analysis results, generate detailed performance improvement reports and second index optimization suggestions, which will be submitted to the index recommendation model to improve the index. When generating the fourth index based on the second index optimization suggestion and the second index, the second index optimization suggestion may include recommendations to create indexes on specific columns, adjust the query structure to reduce resource consumption, etc. When modifying the second index to obtain the fourth index, the index method can be modified, such as changing the traversal index to a hash index, etc. This application does not limit the specific method of modifying the index.

[0091] Based on performance data and reports, the index recommendation algorithm can be adjusted to reflect actual query and index usage, resulting in new indexes for performance improvement. This continuous optimization feedback loop helps continuously improve the accuracy and effectiveness of index recommendations and provides database administrators with operational recommendations, such as index rebuilding or tuning, to maintain optimal database performance. These operations dynamically adapt to changing query requirements and data growth, continuously improving query efficiency.

[0092] In the existing technology, as data and query patterns change, database administrators need to regularly evaluate and update index strategies to maintain query performance. This approach is difficult to quickly adapt to changes in database and business needs, and usually responds only after problems arise. In the usage model of ultra-large-scale MPPDB distributed database clusters, especially in modern database systems with large data volumes and complex query requirements, manual maintenance methods are almost unsustainable, and more efficient and intelligent technical solutions are needed to overcome the limitations of existing technologies. To this end, this application continuously tracks and evaluates the performance of deployed indexes, ensuring that the implementation of new indexes will produce the expected improvement in query performance in the production environment, and providing continuous optimization feedback for the system.

[0093] According to embodiments of the present application, once the index is confirmed to be valid, the final implementation and deployment of the index in the production environment is immediately carried out. First, a "validated" index configuration and performance improvement report are received. Based on this information, a deployment plan for the index is prepared, including determining a deployment time window to minimize the impact on the production environment.

[0094] Guide database administrators in executing index creation scripts within the scheduled time window. These scripts precisely configure new indexes based on the recommendations of the index recommendation model, ensuring the accuracy of each operation. During the deployment process, monitor the status and performance indicators of deployment activities to ensure that index implementation does not cause system instability or other unexpected issues.

[0095] After deployment, continue to monitor the performance of the new index to verify its success. This includes comparing performance data before and after deployment to confirm the expected improvement. If necessary, based on the actual results, adjust or optimize the deployment strategy to ensure continued optimization of database performance.

[0096] By ensuring the correct deployment and maintenance of new indexes, we can achieve continuous performance optimization and system stability. The establishment and implementation of this process ensures that the new indexes can actually improve the processing capacity of the database and adapt to the unchanged business needs.

[0097] According to one implementation, a financial business built a data warehouse based on MPPDB to store and process massive amounts of data to meet the complex data support needs of daily operations. This involved complex SQL queries on large amounts of transaction data. These queries frequently led to performance bottlenecks, particularly when generating month-end reports, which took a long time. The following example illustrates how to solve this problem using the index generation method provided in this application.

[0098] The initial situation is as follows: a database table is set up with the following fields: a first data identification field (primary key), a second data identification field, a numeric field, a date field, and a type field. The data volume is approximately 200 million rows.

[0099] Typical database query statements include:

[0100] SELECT the second data identification field, SUM (numeric field) AS total_spent;

[0101] FROM database table;

[0102] WHERE date field BETWEEN '2023-01-01' AND '2023-01-31';

[0103] AND typefield = 'debit';

[0104] GROUP BY second data identification field;

[0105] In the above database query statement, FROM the database table refers to obtaining data from the database table, BETWEEN '2023-01-01' AND '2023-01-31' refers to setting the query time condition: from January 1, 2023 to January 31, 2023; GROUP BY refers to data grouping statistics.

[0106] The problem encountered during the search was that this query ran very slowly at the end of the month, especially when there were many concurrent users. First, we caught the slow query. The operations management platform monitored that the query took more than 10 minutes to execute in the database, far exceeding the required performance target.

[0107] Data Collection and Feature Engineering: By interacting with a large language model, we perform semantic analysis on slow queries to identify keywords and bottlenecks. The large language model semantically analyzes SQL structures and, combined with database metadata, identifies potential performance bottlenecks. This analysis indicates that the lack of composite indexes on date and type fields is the primary performance constraint. First, we construct a feature vector that includes the data range selectivity of the date field and the frequency of use of the type field. Table 1 shows the features and data sources for various feature categories.

[0108] Table 1

[0109]

[0110] The training method of the index recommendation model is supervised learning. The labels are integrated into the second index in the feature engineering step. Table 2 shows the features that improve the performance after creating the second index.

[0111] Table 2

[0112]

[0113] The following is an example of the XGBoost algorithm model structure used in the index recommendation model training process. The parameters are adjusted according to different environments: the number of trees can be 200, the maximum tree depth can be 7, the learning ratio can be 0.1, the sample sampling rate can be 0.8, the feature sampling rate can be 0.7, and the feature importance calculation method can be set to calculate the average gain of a feature on the prediction results in all trees.

[0114] Subsequently, the model is trained based on the feature vector and a second index is generated, that is, a composite index is created on the date field and the type field.

[0115] To verify the effectiveness and accuracy of the secondary index generated by this system, a recommended secondary index is created on a trimmed-down business table in an MPP distributed database in a test environment, simulating a production sandbox environment. Queries are then re-executed to test performance (monitoring metrics such as execution time and cluster performance). For example, the recommended index can be used to complete index creation for the same table in the test environment. After verification and trimming of the data in the test environment, queries are run to confirm successful index creation. The core input for this verification phase is the index recommendation generated by the large language model for a specific query, along with its contextual features (e.g., index type, structural complexity, apparent relevance to the core query concept, and confidence score during generation). However, this does not rely on external, real-world annotated data. After training, the verification model outputs a qualitative quality assessment signal or confidence deviation estimate (e.g., categories such as "high-risk invalid index," "potentially relevant but caution needed," "highly confident," or a deviation score indicating deviation from the expected quality range).

[0116] The core utility of the verification results lies in forming a closed-loop feedback mechanism, including: immediate output correction; database users can set quantitative indicators and thresholds; when the verification model determines that the index recommendation quality is "low" or the deviation is "high", the system can trigger a warning, require the regeneration of index recommendations, or automatically call alternative search strategies, directly intervening in the final output and reducing the risk of invalid or incorrect indexes being used; dynamic parameter adjustment; the signals generated by the verification model can be fed back to the index generation algorithm in real time as a basis for weight adjustment. For example, the weight of index sources or generation modes frequently marked as low-quality can be reduced, or the weight of features that have performed stably and reliably during verification can be increased, thereby achieving online, adaptive optimization of the index recommendation strategy and continuously improving the overall accuracy and reliability of subsequent recommendations.

[0117] This application can also monitor the performance of the new index in a production environment and confirm that the performance improvement is continuous; if there is a significant query performance improvement for the same amount of data and the same query SQL, for example, from more than 10 minutes to less than 1 minute, it indicates that the second index is effective.

[0118] When deploying the second index, we coordinated the deployment of the verified composite index in the production environment. After the second index was deployed, the speed of generating query reports involved was significantly improved.

[0119] The above case studies demonstrate the successful resolution of performance issues for critical business queries. The new composite index significantly improves query speed, significantly shortens report generation time, and thus improves overall business efficiency. This demonstrates the practicality and efficiency of large language models and index recommendation models in resolving real-world database performance issues.

[0120] Figure 4The following schematic diagram shows a database index generation device according to an embodiment of the present application. Figure 4 The device is described in detail. Figure 4 As shown, database index generation apparatus 400 includes a query analysis module 410, a feature engineering module 420, an index recommendation module 430, an index verification module 440, a performance monitoring module 450, and a system management module 460. The modules in database index generation apparatus 400 work together to automatically recommend and manage database indexes, thereby optimizing query performance.

[0121] According to one embodiment, the query analysis module 410 is responsible for monitoring and analyzing query activity in the database, automatically identifying slow queries with inefficient execution. It not only captures query execution data, including execution time and resource consumption, but also performs preliminary performance evaluations to provide a data foundation for further analysis.

[0122] Feature engineering module 420 is configured to, in response to detecting that a database query using the first index is a slow query, input the query statement of the slow query into the large language model, perform semantic analysis based on the large language model, and determine a first feature, indicating that the slow query is a database query with abnormal query parameters. In one embodiment, feature engineering module 420 can be configured to perform operation 310 described above, which will not be further described here.

[0123] The feature engineering module 420 includes: an operator determination module for performing semantic analysis on a query statement to determine at least one operator; an operation object determination module for determining at least one operation object based on the operator; and a first feature determination module for determining the operator and the operation object as a first feature.

[0124] The feature engineering module 420 also includes: a second feature determination module, which is used to obtain a monitoring log including multiple slow queries, and to collect statistics on information of multiple slow queries for the same data based on the monitoring log to determine aggregate information, where the aggregate information includes at least one of the frequency of the query, the average execution time, and the number of affected rows; determine the second feature based on the aggregate information, and generate a feature vector for input into the index recommendation model.

[0125] The feature engineering module 420 further includes: a third feature determination module, configured to obtain metadata of tables and columns corresponding to slow queries, determine a third feature based on the metadata, and generate a feature vector for input into an index recommendation model based on the third feature.

[0126] The feature engineering module 420 also includes: a fourth feature determination module, which is used to obtain index performance evaluation data of the first index of the slow query, generate a fourth feature based on the index performance evaluation data; and generate a feature vector for input into the index recommendation model based on the fourth feature.

[0127] The feature engineering module 420 also includes: a target data feature mining module, which is used to determine the dependency relationship of the target data of the database query based on the large language model; an index problem determination module, which is used to determine the index problem that causes slow query during the query process based on the dependency relationship; a first index optimization suggestion generation module, which is used to generate a first index optimization suggestion based on the index problem; and a third index generation module, which is used to generate a third index based on the first index optimization suggestion and the second index.

[0128] The index recommendation module 430 is used to generate a feature vector based on the first feature and input the feature vector into the index recommendation model to obtain a second index. In one embodiment, the index recommendation module 430 can be used to perform the operation 320 described above, which will not be described in detail here.

[0129] The index recommendation module 430 includes: a first determination module, used to determine the column used to generate the index based on the feature vector; a second determination module, used to determine the index setting method based on the column used to generate the index; and a generation module, used to generate a second index based on the column used to generate the index and the index setting method.

[0130] Index validation module 440 is used to create the recommended index in an isolated test environment based on the provided recommended columns and index configuration. After the recommended index is created, a series of predefined benchmark queries are used to evaluate the index's effectiveness. Performance data before and after index creation is then compared to analyze the actual effectiveness of the new index.

[0131] Performance monitoring module 450 monitors query performance in real time as newly created indexes impact query performance. It regularly analyzes collected performance data to assess the overall impact of the indexes. This analysis helps determine which indexes significantly improve performance and which may require further adjustment or removal.

[0132] The performance monitoring module 450 includes: an index performance determination module, used to determine the index performance of the database query using the second index; a second index optimization suggestion generation module, used to generate a second index optimization suggestion based on the index performance; and a fourth index generation module, used to generate a fourth index based on the second index optimization suggestion and the second index.

[0133] The system management module 460 is used to replace the first index with the second index so as to perform a database query operation according to the first index. In one embodiment, the system management module 460 can be used to perform the operation 330 described above, which will not be described in detail here.

[0134] According to an embodiment of the present application, any multiple modules in the database index recommendation device 400 can be combined into a single module, or any one of the modules can be split into multiple modules. Alternatively, at least part of the functionality of one or more of these modules can be combined with at least part of the functionality of other modules and implemented in a single module. According to an embodiment of the present application, at least one of the query analysis module 410, feature engineering module 420, index recommendation module 430, index verification module 440, performance monitoring module 450, and system management module 460 can be at least partially implemented as a hardware circuit, such as a field programmable gate array (FPGA), a programmable logic array (PLA), a system on a chip, a system on a substrate, a system on a package, an application-specific integrated circuit (ASIC), or can be implemented in hardware or firmware by any other reasonable means of integrating or packaging circuits, or can be implemented in any of the three implementation methods of software, hardware, and firmware, or in any appropriate combination of any of them. Alternatively, at least one of the query analysis module 410, the feature engineering module 420, the index recommendation module 430, the index verification module 440, the performance monitoring module 450 and the system management module 460 can be at least partially implemented as a computer program module, which can perform the corresponding function when it is executed.

[0135] Figure 5 A block diagram of an electronic device suitable for implementing a database index generation method according to an embodiment of the present application is schematically shown.

[0136] like Figure 5 As shown, an electronic device 500 according to an embodiment of the present application includes a processor 501, which can perform various appropriate actions and processes based on a program stored in a read-only memory (ROM) 502 or a program loaded from a storage unit 508 into a random access memory (RAM) 503. The processor 501 may include, for example, a general-purpose microprocessor (e.g., a CPU), an instruction set processor and / or a related chipset and / or a special-purpose microprocessor (e.g., an application-specific integrated circuit (ASIC)), etc. The processor 501 may also include onboard memory for caching purposes. The processor 501 may include a single processing unit or multiple processing units for performing different actions of the method flow according to the embodiment of the present application.

[0137] Various programs and data required for the operation of the electronic device 500 are stored in the RAM 503. The processor 501, ROM 502, and RAM 503 are connected to each other via a bus 504. The processor 501 performs various operations of the method flow according to the embodiment of the present application by executing the programs in the ROM 502 and / or RAM 503. It should be noted that the programs may also be stored in one or more memories other than the ROM 502 and the RAM 503. The processor 501 may also perform various operations of the method flow according to the embodiment of the present application by executing the programs stored in the one or more memories.

[0138] According to an embodiment of the present application, electronic device 500 may further include an input / output (I / O) interface 505, which is also connected to bus 504. Electronic device 500 may also include one or more of the following components connected to I / O interface 505: an input section 506 including a keyboard, mouse, etc.; an output section 507 including devices such as a cathode ray tube (CRT), liquid crystal display (LCD), and speakers; a storage section 508 including a hard disk; and a communication section 509 including a network interface card such as a LAN card or modem. Communication section 509 performs communication processing via a network such as the Internet. A drive 510 is also connected to I / O interface 505 as needed. Removable media 511, such as a magnetic disk, optical disk, magneto-optical disk, semiconductor memory, etc., is installed in drive 510 as needed, so that computer programs read from the removable media can be installed into storage section 508 as needed.

[0139] This application also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiments, or may exist independently and not be incorporated into the device / apparatus / system. The computer-readable storage medium carries one or more programs, and when the one or more programs are executed, the method according to the embodiments of this application is implemented.

[0140] According to an embodiment of the present application, a computer-readable storage medium may be a non-volatile computer-readable storage medium, such as, but not limited to, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present application, a computer-readable storage medium may be any tangible medium that contains or stores a program that can be used by or in conjunction with an instruction execution system, apparatus, or device. For example, according to an embodiment of the present application, a computer-readable storage medium may include the ROM 502 and / or RAM 503 described above and / or one or more memories other than ROM 502 and RAM 503.

[0141] The embodiments of the present application also include a computer program product, which includes a computer program containing program code for executing the method shown in the flowchart. When the computer program product is executed in a computer system, the program code is used to enable the computer system to implement the database index generation method provided in the embodiments of the present application.

[0142] The computer program executes the above functions defined in the system / device of the embodiment of the present application when the computer program is executed by the processor 501. According to the embodiment of the present application, the system, device, module, unit, etc. described above can be implemented by a computer program module.

[0143] In one embodiment, the computer program may be stored on a tangible storage medium such as an optical storage device or a magnetic storage device. In another embodiment, the computer program may be transmitted and distributed in the form of a signal on a network medium, downloaded and installed via the communication portion 509, and / or installed from a removable medium 511. The program code contained in the computer program may be transmitted using any appropriate network medium, including but not limited to wireless, wired, or any suitable combination thereof.

[0144] In such an embodiment, the computer program can be downloaded and installed from the network via the communication section 509, and / or installed from the removable medium 511. When the computer program is executed by the processor 501, the above-mentioned functions defined in the system of the embodiment of the present application are performed. According to the embodiment of the present application, the systems, devices, means, modules, units, etc. described above can be implemented by computer program modules.

[0145] According to an embodiment of the present application, the program code for executing the computer program provided by the embodiment of the present application can be written in any combination of one or more programming languages. Specifically, these computer programs can be implemented using high-level procedural and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages ​​include, but are not limited to, languages ​​such as Java, C++, Python, "C" or similar programming languages. The program code can be executed entirely on the user computing device, partially on the user device, partially on a remote computing device, or entirely on a remote computing device or server. In the case of a remote computing device, the remote computing device can be connected to the user computing device through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computing device (for example, using an Internet service provider to connect via the Internet).

[0146] The flowcharts and block diagrams in the accompanying drawings illustrate the possible implementation architecture, functions and operations of the systems, methods and computer program products according to various embodiments of the present application. In this regard, each box in the flowchart or block diagram can represent a module, program segment, or a part of code, and the above-mentioned module, program segment, or a part of code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in an order different from that marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flowchart, and the combination of the boxes in the block diagram or flowchart, can be implemented with a dedicated hardware-based system that performs the specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0147] Those skilled in the art will appreciate that the features described in the various embodiments of this application may be combined and / or coupled in various ways, even if such combinations or couplings are not explicitly described in this application. In particular, the features described in the various embodiments of this application may be combined and / or coupled in various ways without departing from the spirit and teachings of this application. All such combinations and / or couplings fall within the scope of this application.

Claims

1. A method for generating a database index, characterized in that: include: In response to detecting that a database query using the first index is a slow query, inputting a query statement of the slow query into a large language model, performing semantic analysis based on the large language model to determine a first feature, wherein the slow query is a database query with abnormal query parameters; Generate a feature vector based on the first feature, and input the feature vector into an index recommendation model to obtain a second index; The first index is replaced according to the second index so as to perform a database query operation according to the first index.

2. The method according to claim 1, characterized in that Inputting the query statement of the slow query into the large language model and performing semantic analysis according to the large language model to determine the first feature includes: Performing semantic analysis on the query statement to determine at least one operator; determining at least one operation object according to the operator; The operator and the operation object are determined as first features.

3. The method according to claim 1, characterized in that Generating a feature vector according to the first feature and inputting the feature vector into an index recommendation model to obtain a second index includes: Determining a column for generating an index according to the feature vector; Determine how the index is set up based on the columns used to create the index; A second index is generated according to the columns used to generate the index and the index setting method.

4. The method according to any one of claims 1 to 3, further comprising: Obtaining a monitoring log including multiple slow queries, and collecting statistics on multiple slow queries for the same data according to the monitoring log to determine aggregate information, where the aggregate information includes at least one of query frequency, average execution time, and number of affected rows; A second feature is determined based on the aggregated information, and a feature vector is generated for input into an index recommendation model.

5. The method according to any one of claims 1 to 3, further comprising: Obtain metadata of the table and column corresponding to the slow query, and determine a third feature based on the metadata; A feature vector for inputting into an index recommendation model is generated according to the third feature.

6. The method according to any one of claims 1 to 3, further comprising: Obtaining index performance evaluation data of the first index of the slow query, and generating a fourth feature based on the index performance evaluation data; A feature vector for inputting into an index recommendation model is generated according to the fourth feature.

7. The method according to any one of claims 1 to 3, further comprising: Determining the dependency relationship of target data for database query based on the large language model; Determine the index problem that causes slow query during the query process based on the dependency relationship; generating a first index optimization suggestion based on the index problem; A third index is generated based on the first index optimization suggestion and the second index.

8. The method according to any one of claims 1 to 3, further comprising: determining index performance for a database query using the second index; generating a second index optimization suggestion based on the index performance; A fourth index is generated according to the second index optimization suggestion and the second index.

9. A database index generation device, characterized in that: The device comprises: a feature engineering module configured to, in response to detecting that a database query using the first index is a slow query, input a query statement of the slow query into a large language model, and perform semantic analysis based on the large language model to determine a first feature, wherein the slow query is a database query with abnormal query parameters; an index recommendation module, configured to generate a feature vector based on the first feature and input the feature vector into an index recommendation model to obtain a second index; and The system management module is configured to replace the first index according to the second index so as to perform a database query operation according to the first index.

10. An electronic device comprising: one or more processors; a memory for storing one or more computer programs, It is characterized in that the one or more processors execute the one or more computer programs to implement the steps of the method according to any one of claims 1 to 8.

11. A computer-readable storage medium having a computer program or instruction stored thereon, characterized in that: When the computer program or instruction is executed by a processor, the steps of the method according to any one of claims 1 to 8 are implemented.

12. A computer program product comprising a computer program or instructions, characterized in that When the computer program or instruction is executed by a processor, the steps of the method according to any one of claims 1 to 8 are implemented.

Images