Index recommendation method and apparatus

Abstract

The embodiment of the application provides an index recommendation method and device, which can be used in the field of artificial intelligence technology, and the method comprises the following steps: based on a preset database SQL feature index system, target data is extracted from database table information corresponding to a target distributed database, database SQL information and database SQL historical execution log content in a test environment; the target data is input into a preset index efficiency recommendation model, and index recommendation result data corresponding to the target distributed database is generated according to the data output by the index efficiency recommendation model. The application can effectively improve the automation degree, intelligent degree and efficiency of the index recommendation process, and can effectively improve the effectiveness, accuracy and reliability of the index recommendation result, thereby effectively improving the update and query efficiency of the distributed database according to the index recommendation result.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and more particularly to the field of artificial intelligence technology, specifically to index recommendation methods and apparatus. Background Technology

[0002] In recent years, with the rapid development of financial technology, more and more banks and other financial institutions have launched IT transformation to enhance their technology application capabilities and adhere to technology empowerment. For example, they have transformed their IT architecture system, which has always been based on IBM mainframes, Oracle databases and EMC storage devices, into a digital architecture system that adopts x86 service system, distributed database based on MySQL, and uses various emerging technologies to drive business model development.

[0003] With the development of banking business and the transformation of technology architecture, distributed system frameworks are gradually replacing system frameworks dominated by IOE (IBM, Oracle, and EMC). However, the transformation of IT architecture and the rapid growth of business data have made it difficult for traditional database index design to meet the new database specifications, and data processing efficiency to meet update and query requirements. This has led to a series of problems during the system transformation process, such as the inability to properly match the optimal index and the reduction of database index efficiency, which in turn has resulted in long data processing times during the IT architecture transformation process.

[0004] However, existing index selection methods require operations and maintenance personnel to make a final selection after multiple attempts, which consumes a lot of manpower and time. If the selection is based on preset selection criteria, although the time for human intervention can be reduced, the fixed selection criteria make it impossible to flexibly select indexes and thus cannot guarantee the validity of the index results. Therefore, no matter which method is mentioned above, it is impossible to simultaneously meet the requirements of validity, efficiency and automation in index selection. Summary of the Invention

[0005] To address the problems in the existing technology, this application provides an index recommendation method and apparatus, which can effectively improve the automation, intelligence and efficiency of the index recommendation process, and effectively improve the validity, accuracy and reliability of the index recommendation results. In turn, it can effectively improve the efficiency of updating and querying distributed databases based on the index recommendation results, and meet the data processing time requirements of distributed databases in the process of IT architecture transformation.

[0006] To solve the above-mentioned technical problems, this application provides the following technical solution:

[0007] Firstly, this application provides an index recommendation method, including:

[0008] Based on a pre-defined database SQL feature index system, target data is extracted from the database table information, database SQL information, and database SQL historical execution log content in the test environment corresponding to the target distributed database.

[0009] The target data is input into a preset index efficiency recommendation model, and the index recommendation result data corresponding to the target distributed database is generated based on the data output by the index efficiency recommendation model.

[0010] Furthermore, before extracting target data from the database table information, database SQL information, and historical SQL execution log content in the test environment corresponding to the target distributed database based on the preset database SQL feature indicator system, the following steps are also included:

[0011] Receive an index recommendation request, wherein the index recommendation request contains the identifier of a distributed database to be indexed;

[0012] The distributed database in the index recommendation request is identified as the current target distributed database;

[0013] Based on the identifier of the target distributed database, extract the database table information, database SQL information, and the database SQL historical execution log content in the test environment corresponding to the target distributed database.

[0014] Furthermore, before extracting target data from the database table information, database SQL information, and historical SQL execution log content in the test environment corresponding to the target distributed database based on the preset database SQL feature indicator system, the following steps are also included:

[0015] Obtain database table information, database SQL information, and historical SQL execution log content in the test environment corresponding to a distributed database;

[0016] Extract the data indicators of each SQL-related table from the database table information corresponding to the distributed database to form the corresponding SQL-related table data indicator system.

[0017] Extract each SQL keyword indicator from the database SQL information corresponding to the distributed database to form a corresponding SQL keyword indicator system;

[0018] In addition, extract each SQL execution result indicator from the database SQL historical execution log content of the distributed database in the test environment to form a corresponding SQL execution result indicator system;

[0019] A database SQL feature indicator system is generated based on the table data indicator system, SQL keyword indicator system, and SQL execution result indicator system involved in the SQL.

[0020] Furthermore, the target data, based on the preset database SQL feature index system, is extracted from the database table information, database SQL information, and historical execution log content of the database SQL in the test environment corresponding to the target distributed database, including:

[0021] Data preprocessing is performed on the database table information and database SQL information corresponding to the target distributed database to obtain the corresponding initial dataset. The data preprocessing includes: text feature extraction, data normalization processing, and data information vectorization.

[0022] Based on the preset database SQL feature index system, the corresponding first index dataset is extracted from the initial dataset using the SQL keyword index system and the SQL table data index system.

[0023] Based on the SQL execution result indicator system in the preset database SQL feature indicator system, the index efficiency of the database SQL historical execution log content corresponding to the target distributed database is evaluated to generate a second indicator dataset containing the corresponding index efficiency evaluation results.

[0024] Target data is generated based on the first indicator dataset and the second indicator dataset.

[0025] Furthermore, based on the SQL execution result indicator system within the preset database SQL feature indicator system, the indexing efficiency of the database SQL historical execution log content corresponding to the target distributed database is evaluated to generate a second indicator dataset containing the corresponding indexing efficiency evaluation results, including:

[0026] Based on the execution plan indicators and consumption value indicators in the SQL execution result indicator system, an initial index efficiency evaluation is performed on the SQL historical execution log content of the target distributed database to generate an evaluation result.

[0027] Obtain the historical execution log content of the database SQL corresponding to the target distributed database in the production environment;

[0028] The database SQL historical execution log content in the production environment is fitted with the database SQL historical execution log content in the test environment corresponding to the first evaluation result to generate a secondary fitting result, and a second indicator dataset is generated based on the secondary fitting result.

[0029] Furthermore, before inputting the target data into a preset index efficiency recommendation model and generating the index recommendation result data corresponding to the target distributed database based on the data output by the index efficiency recommendation model, the method further includes:

[0030] Based on a preset database SQL feature index system, historical target data is extracted from the database table information, database SQL information, and database SQL historical execution log content in the test environment corresponding to the distributed database.

[0031] Multiple candidate indexes are extracted from the database table information and database SQL information corresponding to the distributed database using a preset candidate index selection rule.

[0032] A preset random forest classifier is trained based on the historical target data and each of the candidate indices to obtain an index efficiency recommendation model for index efficiency recommendation.

[0033] Further, the step of inputting the target data into a preset indexing efficiency recommendation model, and generating indexing recommendation result data corresponding to the target distributed database based on the data output by the indexing efficiency recommendation model, includes:

[0034] The target data is input into a preset index efficiency recommendation model so that the index efficiency recommendation model outputs multiple index data.

[0035] A preset number of target indexes are selected from each of the index data based on a preset weight selection rule;

[0036] Obtain the execution plan results for each of the target indexes in the test environment;

[0037] Determine whether the execution efficiency of the execution plan results of each target index in the test environment is better than the execution plan results of the original index in the SQL historical execution log of the database. If so, determine each target index as the index recommendation result data corresponding to the target distributed database.

[0038] Output the index recommendation results data corresponding to the target distributed database.

[0039] Secondly, this application provides an index recommendation device, comprising:

[0040] The data selection module is used to extract target data from the database table information, database SQL information, and database SQL historical execution log content in the test environment of the target distributed database, based on a preset database SQL feature index system.

[0041] The model application module is used to input the target data into a preset index efficiency recommendation model, and generate index recommendation result data corresponding to the target distributed database based on the data output by the index efficiency recommendation model.

[0042] Thirdly, this application provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the index recommendation method.

[0043] Fourthly, this application provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the aforementioned index recommendation method.

[0044] As can be seen from the above technical solution, the index recommendation method and apparatus provided in this application include: extracting target data from database table information, database SQL information, and historical SQL execution log content in a test environment corresponding to a target distributed database based on a preset database SQL feature index system; inputting the target data into a preset index efficiency recommendation model, and generating index recommendation result data corresponding to the target distributed database based on the data output by the index efficiency recommendation model; by extracting target data from database table information, database SQL information, and historical SQL execution log content in a test environment corresponding to the target distributed database based on the database SQL feature index system of the target distributed database, the reliability and effectiveness of target data selection can be effectively improved. This provides an accurate and comprehensive data foundation for subsequent index efficiency recommendation models. By inputting the target data into a preset index efficiency recommendation model and generating index recommendation results for the target distributed database based on the output of the model, index recommendation results can be flexibly generated from the target data, saving manpower and time costs. This effectively improves the automation, intelligence, and efficiency of the index recommendation process, as well as the validity, accuracy, and reliability of the index recommendation results. Consequently, it effectively improves the efficiency of updating and querying the distributed database based on the index recommendation results, meeting the data processing time requirements of distributed databases during IT architecture transformation. This effectively improves the efficiency and reliability of IT architecture transformation and enhances the user experience for operations and maintenance personnel. Attached Figure Description

[0045] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0046] Figure 1 This is a schematic diagram illustrating the relationship between the index recommendation device and the client device in an embodiment of this application.

[0047] Figure 2 This is a schematic diagram of the first type of index recommendation method in the embodiments of this application.

[0048] Figure 3 This is a schematic diagram of the second type of index recommendation method in the embodiments of this application.

[0049] Figure 4 This is a schematic diagram of the third type of index recommendation method in the embodiments of this application.

[0050] Figure 5 This is a schematic diagram of the fourth type of index recommendation method in the embodiments of this application.

[0051] Figure 6 This is a schematic diagram of the fifth type of index recommendation method in the embodiments of this application.

[0052] Figure 7 This is a schematic diagram of the sixth type of index recommendation method in the embodiments of this application.

[0053] Figure 8 This is a schematic diagram of the seventh type of index recommendation method in the embodiments of this application.

[0054] Figure 9 This is a schematic diagram of the index recommendation device in the embodiments of this application.

[0055] Figure 10 This is an overall flowchart of the main steps of the index recommendation method provided in the application example of this application.

[0056] Figure 11 This is a flowchart of the database SQL text feature extraction process provided in the application example of this application.

[0057] Figure 12 This is an example structure diagram of the database SQL parsing provided in the application example of this application.

[0058] Figure 13 This is a flowchart of the overall model training and feedback process provided in the application example of this application.

[0059] Figure 14 This is a block diagram of the distributed database index efficiency detection and index recommendation device provided in the application example of this application.

[0060] Figure 15 This is a schematic diagram of the structure of the electronic device in the embodiments of this application. Detailed Implementation

[0061] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, 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.

[0062] It should be noted that the index recommendation method and apparatus disclosed in this application can be used in the field of artificial intelligence technology, or in any field other than artificial intelligence technology. The application field of the index recommendation method and apparatus disclosed in this application is not limited.

[0063] To address the issue that existing index optimization methods cannot simultaneously meet the requirements of effectiveness, efficiency, and automation, this application provides an index recommendation method. Based on a preset database SQL feature index system, target data is extracted from the database table information, database SQL information, and historical SQL execution logs in a test environment corresponding to the target distributed database. The target data is then input into a preset index efficiency recommendation model, and index recommendation result data corresponding to the target distributed database is generated based on the output of the model. By extracting target data from the database table information, database SQL information, and historical SQL execution logs in the test environment based on the database SQL feature index system of the target distributed database, the method can effectively improve the selection of target data. The reliability and effectiveness of the data obtained provide an accurate and comprehensive data foundation for subsequent index efficiency recommendation models. By inputting the target data into a preset index efficiency recommendation model and generating index recommendation results for the target distributed database based on the output of the model, index recommendation results can be flexibly generated based on the target data, saving manpower and time costs. This effectively improves the automation, intelligence, and efficiency of the index recommendation process, as well as the effectiveness, accuracy, and reliability of the index recommendation results. Consequently, it effectively improves the efficiency of updating and querying the distributed database based on the index recommendation results, meeting the data processing time requirements of distributed databases during IT architecture transformation. This effectively improves the efficiency and reliability of IT architecture transformation and enhances the user experience for operations and maintenance personnel.

[0064] In one or more embodiments of this application, SQL (Structured Query Language) refers to a structured query language, a special-purpose programming language, a database query and programming language used to access data and query, update and manage relational database systems.

[0065] Based on the foregoing, this application also provides an index recommendation apparatus for implementing the index recommendation method provided in one or more embodiments of this application. This index recommendation apparatus can be a server. (See also...) Figure 1The index recommendation device can communicate and connect with each client device sequentially, either independently or through a third-party server. It can receive index recommendation requests from client devices and, based on a preset database SQL feature index system, extract target data from the database table information, database SQL information, and historical SQL execution logs in the test environment corresponding to the target distributed database. The target data is then input into a preset index efficiency recommendation model, and index recommendation result data corresponding to the target distributed database is generated based on the output of the model. The index recommendation device can also send the index recommendation result data to the user's client device for display.

[0066] In another practical application scenario, the index recommendation portion of the aforementioned index recommendation device can be executed on a server as described above, or all operations can be completed on the user-end device. The choice can be made based on the processing power of the user-end device and the limitations of the user's usage scenario. This application does not impose any limitations on this. If all operations are completed on the user-end device, the user-end device may further include a processor for the specific processing of index recommendation.

[0067] It is understood that the mobile terminal may include any mobile device capable of running applications, such as smartphones, tablet computers, network set-top boxes, portable computers, personal digital assistants (PDAs), in-vehicle devices, and smart wearable devices. Among these, the smart wearable devices may include smart glasses, smartwatches, and smart bracelets.

[0068] The aforementioned mobile terminal may have a communication module (i.e., a communication unit) that can communicate with a remote server to achieve data transmission. The server may include a server on the task scheduling center side; in other implementation scenarios, it may also include a server on an intermediate platform, such as a server on a third-party server platform that has a communication link with the task scheduling center server. The server may include a single computer device, a server cluster consisting of multiple servers, or a distributed server structure.

[0069] The server and the mobile terminal can communicate using any suitable network protocol, including network protocols not yet developed as of the date of this application. Such network protocols may include, for example, TCP / IP, UDP / IP, HTTP, HTTPS, etc. Of course, such network protocols may also include, for example, RPC (Remote Procedure Call Protocol) and REST (Representational State Transfer) protocols used on top of the aforementioned protocols.

[0070] The following embodiments and application examples will be described in detail.

[0071] To address the issue that existing index selection methods cannot simultaneously meet the requirements of effectiveness, efficiency, and automation, this application provides an embodiment of an index recommendation method, see [link to embodiment]. Figure 2 The index recommendation method executed by the index recommendation device specifically includes the following:

[0072] Step 100: Based on the preset database SQL feature index system, extract target data from the database table information, database SQL information, and database SQL historical execution log content in the test environment corresponding to the target distributed database.

[0073] In step 100, the basic database data involves three main components: the database, database tables, and database SQL. A database can contain multiple database tables and multiple database SQL statements, and a database table can also correspond to multiple database SQL statements. This application treats a database as a whole, mainly including two dimensions of data: database table information Q1 and database SQL information Q2. Database table information Q1 may include, but is not limited to, table name, number of table fields, table field names, table data volume, and the SQL statements involved. Database SQL information Q3 may include, but is not limited to, specific SQL data, SQL call frequency, maximum SQL cost, and the corresponding data table. A database index refers to the specific index information of a specified table in the database. It is also possible to obtain a specified portion of the database SQL historical execution log content Q3 executed in the test environment. It can be understood that the database SQL historical execution log content Q3 represents the execution result data obtained from a portion of the database SQL executed in the test environment.

[0074] Step 200: Input the target data into a preset index efficiency recommendation model, and generate index recommendation result data corresponding to the target distributed database based on the data output by the index efficiency recommendation model.

[0075] In step 200, the index efficiency recommendation model can employ a random forest classifier, utilizing the random forest classification method in machine learning to construct the index efficiency recommendation model.

[0076] As described above, the index recommendation method provided in this application extracts target data from the database table information, database SQL information, and historical SQL execution logs in the test environment of the target distributed database based on the database SQL feature index system of the target distributed database. This effectively improves the reliability and effectiveness of target data selection and provides an accurate and comprehensive data foundation for subsequent index recommendation using an index efficiency recommendation model. By inputting the target data into a preset index efficiency recommendation model and generating index recommendation result data corresponding to the target distributed database based on the data output by the index efficiency recommendation model, index recommendation results can be flexibly generated based on the target data, saving manpower and time costs. This effectively improves the automation, intelligence, and efficiency of the index recommendation process and the effectiveness, accuracy, and reliability of the index recommendation results. Consequently, it effectively improves the efficiency of updating and querying the distributed database based on the index recommendation results, meets the data processing time requirements of distributed databases during IT architecture transformation, effectively improves the efficiency and reliability of IT architecture transformation, and enhances the user experience for maintenance personnel.

[0077] To improve the efficiency and reliability of acquiring basic data from the target distributed database, an embodiment of the index recommendation method provided in this application is described below. Figure 3 The index recommendation method, prior to step 100, specifically includes the following:

[0078] Step 010: Receive an index recommendation request, wherein the index recommendation request contains the identifier of a distributed database to be indexed;

[0079] Step 020: Determine the distributed database in the index recommendation request as the current target distributed database;

[0080] Step 030: Extract the database table information, database SQL information, and historical SQL execution log content in the test environment corresponding to the target distributed database based on the identifier of the target distributed database.

[0081] As can be seen from the above description, the index recommendation method provided in this application can effectively improve the efficiency and reliability of obtaining basic data of the target distributed database by retrieving the database table information, database SQL information and database SQL historical execution log content in the test environment corresponding to the target distributed database according to the index recommendation request. This can provide a comprehensive and effective data foundation for subsequent extraction of target data, thereby further improving the automation, intelligence and efficiency of the index recommendation process, and effectively improving the validity, accuracy and reliability of the index recommendation results.

[0082] To further improve the efficiency of index recommendation, in one embodiment of the index recommendation method provided in this application, see [link to embodiment]. Figure 4 The index recommendation method, prior to step 100, specifically includes the following:

[0083] Step 040: Obtain database table information, database SQL information, and historical SQL execution log content in the test environment corresponding to a distributed database.

[0084] Step 050: Extract the data indicators of each SQL-related table from the database table information corresponding to the distributed database to form the corresponding SQL-related table data indicator system;

[0085] Step 060: Extract each SQL keyword indicator from the database SQL information corresponding to the distributed database to form a corresponding SQL keyword indicator system;

[0086] Step 070: Extract various SQL execution result metrics from the historical SQL execution logs of the distributed database in the test environment to form a corresponding SQL execution result metric system;

[0087] Step 080: Generate a database SQL feature indicator system based on the SQL table data indicator system, SQL keyword indicator system, and SQL execution result indicator system.

[0088] Specifically, the database SQL feature indicator system mainly consists of three parts: the SQL keyword indicator system, the SQL-related table data indicator system, and the SQL execution result indicator system. The specific indicator system is shown in Table 1.

[0089] The SQL keyword indicator system refers to the following: SQL keywords mainly refer to the SQL_ID, SQL type (such as update, query, delete, etc.), target table name, relationships, predicates, conditions, etc. involved in a SQL data. To obtain SQL keyword information, natural language processing technology is needed to identify keywords in the SQL text (i.e., database SQL information Q2 in step S110), locate the position and content of keywords, and extract target data information.

[0090] The SQL table data indicator system refers to the basic characteristic information of the target table in the current SQL, such as the amount of data, number of fields, field names, primary key, table field filtering rate, and target index. The SQL table data indicator system can be obtained from the database table information Q1 in step S110.

[0091] The SQL execution result indicator system refers to the basic execution plan and efficiency information of the SQL. The efficiency information includes the maximum request time, average request time, amount of data called, whether there is a deadlock, deadlock duration, COST consumption value (SQL consumption value), etc. The SQL execution result indicator system can be obtained from the SQL historical execution log content Q3 in the database.

[0092] As can be seen from the above description, the index recommendation method provided in this application extracts a set of database SQL feature index systems in advance based on the basic information of a distributed database. This allows the database SQL feature index system to be directly extracted each time an index recommendation is performed, without the need to regenerate the database SQL feature index system. This further improves the efficiency of index recommendation and, consequently, the efficiency of updating and querying the distributed database based on the index recommendation results, thus meeting the data processing time requirements of distributed databases during IT architecture transformation.

[0093] To further improve the efficiency and reliability of index recommendation, an embodiment of the index recommendation method provided in this application is described below. Figure 5 Step 100 of the index recommendation method specifically includes the following:

[0094] Step 101: Perform data preprocessing on the database table information and database SQL information corresponding to the target distributed database to obtain the corresponding initial dataset. The data preprocessing includes: text feature extraction, data normalization, and data information vectorization.

[0095] Specifically, since the initial dataset may contain high-dimensional data and various types of data such as numbers and text, the initial dataset is first preprocessed. The data normalization process may include data discretization and data normalization.

[0096] Step 102: Extract the corresponding first indicator dataset from the initial dataset based on the SQL keyword indicator system and the SQL-related table data indicator system in the preset database SQL feature indicator system;

[0097] Step 103: Based on the SQL execution result indicator system in the preset database SQL feature indicator system, perform index efficiency evaluation on the database SQL historical execution log content corresponding to the target distributed database to generate a second indicator dataset containing the corresponding index efficiency evaluation results.

[0098] Step 104: Generate target data based on the first indicator dataset and the second indicator dataset.

[0099] As can be seen from the above description, the index recommendation method provided in this application, through data preprocessing and index efficiency evaluation, can accurately and quickly reduce the database table information, database SQL information, and database SQL historical execution log content in the test environment corresponding to the target distributed database into target data for input to the index efficiency recommendation model, thereby effectively improving the efficiency and reliability of index recommendation.

[0100] To further improve the effectiveness and reliability of the index efficiency evaluation results, an embodiment of the index recommendation method provided in this application is described below. Figure 6 Step 103 of the index recommendation method specifically includes the following:

[0101] Step 131: Based on the execution plan indicators and consumption value indicators in the SQL execution result indicator system, perform an initial index efficiency evaluation on the SQL historical execution log content of the target distributed database to generate an evaluation result.

[0102] Step 132: Obtain the historical execution log content of the database SQL corresponding to the target distributed database in the production environment.

[0103] Step 133: Fit the database SQL historical execution log content in the production environment with the database SQL historical execution log content in the test environment corresponding to the first evaluation result to generate a secondary fitting result, and generate a second index dataset based on the secondary fitting result.

[0104] Specifically, obtain the historical SQL execution log content Q3 from the database in the test environment, analyze the historical execution records of SQL in the database, mark SQL that may have efficiency problems as 1, and otherwise mark it as 0.

[0105] 1) Based on the execution plan TYPE efficiency monitoring standard, mark the basic performance of the currently used index type. The better the index type efficiency, the higher the value assigned. Evaluate SQL queries with poor efficiency in historical execution plans or those whose execution plans have been modified.

[0106] 2) At the same time, evaluate the frequency of SQL calls and the cost of the execution results.

[0107] 3) Finally, evaluate the SQL execution results for those with significant COST value changes during the data period.

[0108] Then, a second-order fitting determination of SQL efficiency based on production environment data was performed.

[0109] In addition to judging the efficiency of database SQL based on monitoring standards, the execution results of the corresponding SQL in the production environment are also obtained. By fitting the test result data with the production result data, a second judgment is made on the database SQL, and a "Whether test and production are consistent" field is added. If they are consistent, the value is assigned as 1; if they are inconsistent, the value is assigned as 0. For SQL in the test environment that meets the database SQL efficiency standards but whose runtime, execution plan, etc. are inconsistent with those in production, the judgment results are corrected.

[0110] As can be seen from the above description, the index recommendation method provided in this application embodiment can effectively improve the effectiveness and reliability of the index efficiency evaluation results by performing a secondary index efficiency evaluation on the database SQL historical execution log content corresponding to the target distributed database, thereby further improving the effectiveness, accuracy and reliability of the index recommendation results.

[0111] To reduce the number of indexes required for model training, one embodiment of the index recommendation method provided in this application is described below. Figure 7 The index recommendation method, prior to step 200, specifically includes the following:

[0112] Step 091: Based on the preset database SQL feature index system, extract historical target data from the database table information, database SQL information, and database SQL historical execution log content in the test environment corresponding to the distributed database.

[0113] Step 092: Apply the preset candidate index selection rules to extract multiple candidate indexes from the database table information and database SQL information corresponding to the distributed database.

[0114] Specifically, using the basic information of the acquired database tables, the fields involved in each table are initially screened to determine their suitability as candidate index fields. Factors such as field data type, field filtering rate, presence of NULL columns, and whether the field is a primary key are all key factors affecting index efficiency. Suitable fields are marked with Boolean values: 1 for suitable and 0 for unsuitable. The number of individual index fields also affects index efficiency. For high-dimensional database tables, methods such as principal component analysis, join relationships, and field content change frequency analysis are used to ultimately determine a list of candidate index fields for table Tn, denoted as F. Tn ={F1,F2,…,Fn}. Since the number of indexes in a single database table also affects database performance, the number of indexes built for the same database table needs to be controlled within a reasonable range. For database table Tn, let the optimal number be Mn. Based on the candidate index field list F selected for database table Tn in step (1) of step S150. Tn Generate a candidate index list, denoted as I. Tn ={I1,I2,…,Im}.

[0115] Step 093: Train a preset random forest classifier based on the historical target data and each candidate index to obtain an index efficiency recommendation model for index efficiency recommendation.

[0116] The obtained list of SQL queries from the database to be analyzed is used as the target SQL optimization dataset, denoted as S0. After vectorizing and preprocessing the dataset S0 based on the real-valued feature matrix T2 of the obtained test dataset, a database SQL feature index system TS={A1,A2,A3,…,B1,B2,…,C1,C2,…} with further reduced data volume is obtained. This system is used as the input to the machine learning model, i.e., the initial training set S1. The candidate index list I constructed from each database table is then used... Tn As an evaluation of the recommendation model, the final output is a ranking list of predicted performance effects of each candidate index, denoted as CH={(I1: weight(I1)),I2: weight(I2)),…,In: weight(In))}.

[0117] This paper describes the construction of a recommendation model for evaluating database indexing efficiency using the random forest classification method in machine learning. The training process of the random forest classifier mainly includes:

[0118] a. Using the bootstrap sampling method, generate a training subset S1' from the initial training set S1;

[0119] b. For the training subset S1', randomly select λ features without replacement from the data indicator system TS, and use them as the basis for whether each node on the current decision tree should be split, to generate the decision tree D1'.

[0120] c. Repeat steps 1 and 2 N times to obtain n training subsets S1', S2', ..., Sn', and generate decision trees D1', D2', ..., Dn', which together form a random forest D;

[0121] d. Based on the database table candidate index list I Tn As a classification selection, the target uses the random forest algorithm to ultimately determine the index selection and weight of the target SQL;

[0122] e. Repeat step d N times until all SQL indexes have been selected.

[0123] As can be seen from the above description, the index recommendation method provided in this application can effectively reduce the number of indexes that need to be trained by obtaining candidate indexes, effectively improve the efficiency of training a preset random forest classifier based on the historical target data and each candidate index, and further improve the automation, intelligence and efficiency of the index recommendation process, and effectively improve the effectiveness, accuracy and reliability of the index recommendation results.

[0124] To ensure the validity and reliability of the index recommendation results data corresponding to the target distributed database, see [link / reference]. Figure 8 Step 200 of the index recommendation method specifically includes the following:

[0125] Step 210: Input the target data into a preset index efficiency recommendation model so that the index efficiency recommendation model outputs multiple index data;

[0126] Step 220: Select a preset number of target indexes from each of the index data based on a preset weight selection rule;

[0127] Step 230: Obtain the execution plan results of each target index in the test environment;

[0128] Step 240: Determine whether the execution efficiency of the execution plan results of each target index in the test environment is better than the execution plan results of the original index in the SQL historical execution log of the database. If so, determine each target index as the index recommendation result data corresponding to the target distributed database.

[0129] Step 250: Output the index recommendation result data corresponding to the target distributed database.

[0130] Specifically, based on the efficiency evaluation of the constructed database SQL index, the selection weights of the candidate index list are sorted and denoted as CH={(I1: weight(I1)),I2: weight(I2)),…,In: weight(In))}, and the top 3 with the highest weight values ​​are selected as the initial recommendation index result set REC={I1,I2,I3}.

[0131] At the same time, the execution plan result of the recommended index corresponding to the database SQL is compared with the execution plan result of the original database SQL in the database SQL historical execution log content Q3. If the execution plan result of the recommended index is better than the execution plan result of the original database SQL, the current index will enter the final recommended index set REC'; otherwise, it will not enter the final recommended index set REC'. The specific method for comparing the execution plan results can be to compare the execution time or compare the COST consumption values ​​of the two.

[0132] If the final recommended index set REC' is not empty, then the index content in REC' will be modified to optimize the database SQL index; if the final recommended index set REC' is empty, then the original index content will be kept.

[0133] As described above, the index recommendation method provided in this application embodiment, by further efficiency testing of the recommendation results and determining whether the execution efficiency of the execution plan results of each target index in the test environment is better than the execution plan results of the original index in the database SQL historical execution log, can effectively ensure the validity and reliability of the output index recommendation result data corresponding to the target distributed database, thereby significantly improving the database SQL execution efficiency. It also provides better scalability for subsequent database index use and reduces the risk of manual intervention and modification operations to a certain extent. This not only improves database performance during IT architecture transformation and builds a candidate index list to optimize database SQL processing efficiency, but also iteratively optimizes the establishment and selection of subsequent indexes through model training results, continuously improving database performance.

[0134] From a software perspective, to address the issue that existing index optimization methods cannot simultaneously meet the requirements of effectiveness, efficiency, and automation, this application provides an embodiment of an index recommendation device for executing all or part of the aforementioned index recommendation method. See [link to embodiment]. Figure 9 The index recommendation device specifically includes the following components:

[0135] The data selection module 10 is used to extract target data from the database table information, database SQL information, and database SQL historical execution log content in the test environment of the target distributed database based on a preset database SQL feature index system.

[0136] The model application module 20 is used to input the target data into a preset index efficiency recommendation model, and generate index recommendation result data corresponding to the target distributed database based on the data output by the index efficiency recommendation model.

[0137] The embodiments of the index recommendation device provided in this application can be used to execute the processing flow of the index recommendation method embodiments described above. Its functions will not be repeated here, but can be referred to the detailed description of the above method embodiments.

[0138] As described above, the index recommendation device provided in this application, through a database SQL feature index system based on the target distributed database, extracts target data from the database table information, database SQL information, and historical execution logs of the database SQL in the test environment corresponding to the target distributed database. This effectively improves the reliability and effectiveness of target data selection and provides an accurate and comprehensive data foundation for subsequent index recommendation using an index efficiency recommendation model. By inputting the target data into a preset index efficiency recommendation model and generating index recommendation result data corresponding to the target distributed database based on the data output by the index efficiency recommendation model, index recommendation results can be flexibly generated based on the target data, saving manpower and time costs. This effectively improves the automation, intelligence, and efficiency of the index recommendation process, and also effectively improves the effectiveness, accuracy, and reliability of the index recommendation results. Consequently, it effectively improves the efficiency of updating and querying the distributed database based on the index recommendation results, meets the data processing time requirements of distributed databases during IT architecture transformation, effectively improves the efficiency and reliability of IT architecture transformation, and enhances the user experience for maintenance personnel.

[0139] To further illustrate this solution, this application provides an index recommendation method for efficiency detection and recommendation of distributed database indexes. This method involves technical fields such as machine learning, natural language processing, and database index construction. It overcomes the problem that existing technologies cannot adequately meet the database index construction and matching needs that arise during IT architecture transformation. It takes a multi-dimensional approach, considering existing indexes in the database, data distribution, and the actual execution efficiency of database SQL. Specifically, it is an index recommendation method that combines historical data and artificial intelligence technology for index efficiency detection and recommendation.

[0140] See Figure 10 The main steps of the index recommendation method are as follows:

[0141] S110: Retrieve basic data from the database.

[0142] This application involves three main components: a database, database tables, and database SQL. A database can contain multiple database tables and multiple database SQL statements, and a database table can also correspond to multiple database SQL statements. This application treats a database as a whole, mainly including two dimensions of data: database table information Q1 and database SQL information Q2. Database table information Q1 may include, but is not limited to, table name, number of table fields, table field names, table data volume, and the SQL statements involved. Database SQL information Q3 may include, but is not limited to, specific SQL data, SQL call frequency, maximum SQL cost, and the corresponding data table. A database index refers to the specific index information data of a specified table in the database.

[0143] In this step, you can also obtain the database SQL historical execution log content Q3, which is a specified part of the database basic data executed in the test environment. It can be understood that the database SQL historical execution log content Q3 is the execution result data obtained from a part of the database SQL running in the test environment.

[0144] S120: Construction of Database SQL Feature Index System.

[0145] The database SQL feature indicator system mainly consists of three parts: SQL keyword indicator system, SQL table data indicator system, and SQL execution result indicator system. The specific indicator system is shown in Table 1.

[0146] (1) SQL Keyword Index System

[0147] SQL keywords mainly refer to the SQL_ID, SQL type (such as update, query, delete, etc.), target table name, relationships, predicates, conditions, etc. involved in a SQL statement. To obtain SQL keyword information, natural language processing technology is needed to identify keywords in the SQL text (i.e., database SQL information Q2 in step S110), locate the position and content of keywords, and extract the target data information.

[0148] (2) SQL-related table data indicator system

[0149] The SQL table data indicator system mainly refers to the basic characteristic information of the target table in the current SQL, such as the amount of data, number of fields, field names, primary key, table field filtering rate, and target index. The SQL table data indicator system can be obtained from the database table information Q1 in step S110.

[0150] (3) SQL execution result indicator system

[0151] The SQL execution result indicator system mainly refers to the basic execution plan and efficiency information of the SQL. The efficiency information includes the maximum request time, average request time, amount of data called, whether there is a deadlock, deadlock duration, COST consumption value (SQL consumption value), etc. The SQL execution result indicator system can be obtained from the SQL historical execution log content Q3 in the database.

[0152] Table 1 Database SQL Feature Index System

[0153]

[0154]

[0155] S130: Data Preprocessing

[0156] Two initial datasets are obtained through steps S110 and S120: Specifically, a business transaction dataset D1, based on the database table information Q1 from step S110, is generated, with the database tables as the dimension; and a feature indicator system dataset D2, based on the specific content of the SQL keyword indicator system and the SQL-related table data indicator system from step S120, is generated, with the database SQL as the dimension. Because the initial datasets may contain high-dimensional data and various data types such as numbers and text, data preprocessing is performed on the obtained initial datasets, mainly including the following parts:

[0157] (1) Feature extraction of SQL text from database

[0158] Keyword recognition and key feature extraction are performed on the SQL database. The specific rules are as follows, and the corresponding flowchart is shown below. Figure 11 As shown:

[0159] a. Take all the SQL texts involved in the current database in the obtained business transaction dataset D1 as the initial dataset S01, which contains N SQL texts, i.e., S01 = {s1, s2, s3, ..., sn};

[0160] b. Perform data cleaning on the initial dataset S01, including removing SQL text sn and reducing the impact of invalid content such as SQL comments on the accuracy of SQL text feature extraction. The cleaned dataset is denoted as S1. The specific rules are as follows:

[0161] SQL.replace(re.match(' / *([*]|[\r\n]|(*+([ / ]|[\r\n])))*+ / '),",regex=True,inplace=True)

[0162] c. Use the python-sqlparse tool to parse, split, and format the SQL text in dataset S1 to obtain the SQL type, target table name, relationships, predicates, conditions, and other information involved in the SQL text data. Figure 12 This is an example structure diagram of the parsed SQL in a database. Traverse the SQL tree after parsing the SQL text to obtain the SQL feature vector Tsn = {SQL type, table name array list, predicate list, condition list, ...};

[0163] d. Considering that the depth of the SQL tree is inversely proportional to the number of nodes per level and the execution efficiency of the SQL index, information such as the depth of the SQL tree and the number of nodes per level is recorded to optimize the evaluation index system and incorporate it into the subsequent index efficiency evaluation model. At the same time, considering that conditions and predicates appearing at different levels are also key factors in SQL index efficiency, the deeper the level at which conditions and predicates appear, the greater the impact on efficiency. Therefore, the tree level of each keyword is recorded to optimize the feature vector.

[0164] e. Repeat steps c and d N times until the traversal is complete, forming the database SQL feature matrix T1, T1 = {Ts1, Ts2, ..., Tsn}. At the same time, generate the feature indicator system dataset D2 with the database SQL as the dimension based on the specific content of the SQL keyword indicator system and the SQL-related table data indicator system in step S120.

[0165] (2) Data normalization processing

[0166] 1) Data discretization: Discretize the fields with decision rules, such as SQL type and field filtering rate, in datasets D1 and D2. Fields with decision rules can refer to non-numerical fields, specifically including SQL type, field filtering rate, maximum request time, average request time, data volume called, and COST consumption value in Table 1.

[0167] For example, in a real-world database application system, the ratio of SQL update to query operations is generally around 1:10. Therefore, the application examples in this application mainly target query SQL as the object, and the determination rules are shown in Table 2:

[0168] Table 2

[0169]

[0170] For the filter rate field, since a higher filter rate results in better index performance during index building, a threshold is set to divide the field. The determination rules are shown in Table 3.

[0171] Table 3

[0172]

[0173] For the relevant field content, Boolean values ​​are used. If the requirements are met, a value of 1 is assigned; otherwise, a value of 0 is assigned. Specific discretization examples are shown in Table 4 below:

[0174] Table 4

[0175] SQL type Is it the target object? Field Filtering Rate Does it meet the requirements? SELECT 1 5000000 1 UPDATE 0 100000 1 DELETE 0 2 0 …… ……

[0176] 2) Data Normalization: To eliminate the influence of the magnitude of the field's attribute values, numerical data such as the basic execution time of SQL queries and deadlock duration are normalized by mapping data values ​​to [0,1] to eliminate the influence of units on subsequent model construction. The rules are as follows:

[0177] W * =(WW) min ) / (W max -W min )

[0178] Where W represents the numerical value of the numerical data.

[0179] (3) Data information vectorization

[0180] The content of the database SQL feature indicator system in the aforementioned generated feature indicator system dataset D2 with database SQL as the dimension is vectorized and normalized. That is, the real value matrix composed of each field N in the indicator dimension is vectorized to establish a real number feature matrix T2 that can be used for the test dataset in step S150.

[0181] S140: Efficiency evaluation of initial SQL indexes in the database.

[0182] (1) Initial efficiency judgment of database SQL based on monitoring standards

[0183] Q3: Obtain the historical SQL execution log content of the database in the test environment. Analyze the historical execution records of SQL in the database. Mark SQL that may have efficiency problems as 1, and otherwise mark it as 0.

[0184] 1) Based on the execution plan TYPE efficiency monitoring standard, the basic performance of the currently used index category is marked. The better the efficiency of the index category, the higher the value is assigned. The specific rules are shown in Table 5 below:

[0185] Table 5

[0186] TYPE (Execution Plan) Basic performance ALL (Full Table Scan) 1 INDEX (index scan) 2 RANGE (range index scan) 3 REF (index lookup + scan) 4 EQ_REF (index lookup) 5 CONST (constant) 6 NULL (Index not accessed) 7

[0187] The SQL queries with poor historical execution plans or whose execution plans have been modified are evaluated according to the rules shown in Table 6 below:

[0188] Table 6

[0189] if (TYPE <= 3 or 'TYPE value is not unique') —> Yes: assign a value of 1, no: assign a value of 0

[0190] See Table 7 for examples of the values ​​assigned to the execution plan TYPE:

[0191] Table 7

[0192] SQL_ID TYPE Has the TPYE value changed? Needs further analysis? SQL_1 0 no 1 SQL_2 1 no 1 SQL_3 1 yes 1 SQL_4 4 no 0 ……

[0193] 2) Simultaneously, the frequency of SQL calls and the cost of execution results are evaluated, according to the rules shown in Table 8 below:

[0194] Table 8

[0195]

[0196] See Table 9 for examples of COST assignment results:

[0197] Table 9

[0198] SQL_ID call frequency Maximum COST Needs further analysis? SQL_1 high 5000 1 SQL_2 high 300 0 SQL_3 Low 20000 1 SQL_4 Low 100 0 ……

[0199] 3) Finally, for SQL execution results with significant COST value changes during the data period, an evaluation is performed, according to the rules shown in Table 10 below:

[0200] Table 10

[0201] If (initial COST value <= 1 / 2(final COST value)) —> yes: assign 1; no: assign 0

[0202] For examples of evaluation results for SQL statements with large COST value changes, please refer to Table 11;

[0203] Table 11

[0204] SQL_ID Initial execution COST value Last execution COST value Needs further analysis? SQL_1 1000 2000 1 SQL_2 1000 1000 0 ……

[0205] (2) Quadratic fitting determination of SQL efficiency based on production environment data

[0206] In addition to judging the efficiency of database SQL according to monitoring standards, the execution results of the corresponding SQL in the production environment are also obtained. By fitting the test result data with the production result data, a second judgment is made on the database SQL, and a "Whether test and production are consistent" field is added. If they are consistent, the value is assigned 1; if they are inconsistent, the value is assigned 0. For SQL in the test environment that meets the database SQL efficiency standards but whose runtime, execution plan, etc. are inconsistent with those in production, the judgment results are corrected, and the rules are shown in Table 12 below:

[0207] Table 12

[0208] SQL_ID Initial analysis required? Test whether production is consistent Needs further analysis? SQL_1 0 1 0 SQL_2 0 0 1 SQL_3 1 1 1 SQL_4 1 0 1

[0209] S150: Database Table Candidate Index Construction

[0210] (1) Determining candidate fields for database tables

[0211] Using the basic information of the database tables obtained in step S110, the fields involved in each table in the database are initially screened to determine whether they are suitable as candidate index fields. Factors such as field data type, field filtering rate, field containing NULL columns, and whether the field is a primary key are all key factors affecting index efficiency. Boolean values ​​are used to mark whether a field is suitable as an index field; a value of 1 is assigned if it is suitable, and a value of 0 is assigned if it is not suitable. Examples of the rules are shown in Table 13 below:

[0212] Table 13

[0213]

[0214] Meanwhile, the number of individual index fields also affects index efficiency. For high-dimensional database tables, through methods such as principal component analysis, association analysis, and field content change frequency analysis, a list of candidate index fields, denoted as F, is finally determined for database table Tn. Tn ={F1,F2,…,Fn}.

[0215] (2) Database table candidate index construction

[0216] Since the number of indexes in a single database table also affects database performance, the number of indexes built for the same database table needs to be controlled within a reasonable range. For database table Tn, let the optimal number be Mn. Based on the candidate index field list F selected for database table Tn in step (1) of step S150. Tn Generate a candidate index list, denoted as I. Tn = {I1, I2, ..., Im}, examples are shown in Table 14 below:

[0217] Table 14

[0218] Candidate index number Index content Index_1(I1) {F1} Index_2(I2) {F1, F2} Index_3(I3) {F1, F2, F3} Index_4 (I4) {F1, F3, F2} …… ……

[0219] S160: Construction of a Recommendation Model for Database Index Efficiency Evaluation

[0220] (1) Model Input and Output

[0221] The list of SQL databases to be analyzed obtained in step S140 is used as the target SQL optimization dataset, denoted as S0. After vectorizing the dataset S0 according to the real feature matrix T2 of the test dataset obtained in step S130, the database SQL feature index system TS={A1,A2,A3,…,B1,B2,…,C1,C2,…} with further reduced data volume is obtained and used as the input of the machine learning model, i.e., the initial training set S1.

[0222] The candidate index list I constructed for each database table in step S150 Tn As an objective selection factor for evaluating the recommendation model, the final output is a ranking list of predicted performance effects for each candidate index, denoted as CH={(I1: weight(I1)),I2: weight(I2)),…,In: weight(In))}

[0223] (2) Efficiency evaluation recommendation model training

[0224] This patent utilizes the random forest classification method in machine learning to construct a recommendation model for evaluating database indexing efficiency. The training process of the random forest classifier mainly includes:

[0225] a. Using the bootstrap sampling method, generate a training subset S1' from the initial training set S1;

[0226] b. For the training subset S1', randomly select λ features without replacement from the data indicator system TS, and use them as the basis for whether each node on the current decision tree should be split, to generate the decision tree D1'.

[0227] c. Repeat steps 1 and 2 N times to obtain n training subsets S1', S2', ..., Sn', and generate decision trees D1', D2', ..., Dn', which together form a random forest D;

[0228] d. Based on the database table candidate index list I Tn As a classification selection, the target uses the random forest algorithm to ultimately determine the index selection and weight of the target SQL;

[0229] e. Repeat step d N times until all SQL indexes have been selected.

[0230] S170: Database SQL Index Efficiency Evaluation Recommendation Results Feedback

[0231] The overall model training and feedback flowchart is as follows: Figure 13 As shown. The final recommendation result feedback mainly consists of the following steps:

[0232] Based on the training results of the database SQL index efficiency evaluation recommendation model constructed in step S160, the selection weights of the candidate index list are sorted, and the weights from high to low are denoted as CH={(I1: weight(I1)),I2: weight(I2)),…,In: weight(In))}. The top 3 with the highest weight values ​​are selected as the initial recommendation index result set REC={I1,I2,I3}.

[0233] At the same time, the execution plan result of the recommended index corresponding to the database SQL is compared with the execution plan result of the original database SQL in the database SQL historical execution log content Q3. If the execution plan result of the recommended index is better than the execution plan result of the original database SQL, the current index will enter the final recommended index set REC'; otherwise, it will not enter the final recommended index set REC'. The specific method for comparing the execution plan results can be to compare the execution time or compare the COST consumption values ​​of the two.

[0234] If the final recommended index set REC' is not empty, then the index content in REC' will be modified to optimize the database SQL index; if the final recommended index set REC' is empty, then the original index content will be kept.

[0235] In addition, see the application examples in this application. Figure 14 The distributed database index efficiency detection and index recommendation device for implementing the index recommendation method further includes a data acquisition module, a data processing module, a model building module, an effect evaluation and recommendation module, and a result feedback and optimization module.

[0236] The index recommendation method provided in this application firstly assesses the performance of database SQL by fitting the test and production environments, generating a list of SQL queries to be optimized. Secondly, based on the accumulated massive historical data, it uses machine learning techniques to construct a candidate index list and mines potential optimal index matches through efficiency evaluation and recommendation models. This significantly improves the execution efficiency of database SQL and provides good scalability for subsequent database index use. It also reduces the risk of manual intervention and modification to a certain extent. This method not only improves database performance during IT architecture transformation and optimizes the processing efficiency of database SQL by constructing a candidate index list, but also iteratively optimizes the creation and selection of subsequent indexes based on model training results, continuously improving database performance.

[0237] From a hardware perspective, to address the issue that existing index optimization methods cannot simultaneously meet the requirements of effectiveness, efficiency, and automation, this application provides an embodiment of an electronic device for implementing all or part of the index recommendation method. The electronic device specifically includes the following components:

[0238] Figure 15 This is a schematic block diagram illustrating the system configuration of the electronic device 9600 according to an embodiment of this application. Figure 15 As shown, the electronic device 9600 may include a central processing unit 9100 and a memory 9140; the memory 9140 is coupled to the central processing unit 9100. It is worth noting that... Figure 15 This is an example; other types of structures can also be used to supplement or replace this structure to achieve telecommunications functions or other functions.

[0239] In one embodiment, the index recommendation function can be integrated into a central processing unit (CPU). The CPU can be configured to perform the following control:

[0240] Step 100: Based on the preset database SQL feature index system, extract target data from the database table information, database SQL information, and database SQL historical execution log content in the test environment corresponding to the target distributed database.

[0241] Step 200: Input the target data into a preset index efficiency recommendation model, and generate index recommendation result data corresponding to the target distributed database based on the data output by the index efficiency recommendation model.

[0242] As described above, the electronic device provided in this application embodiment extracts target data from the database table information, database SQL information, and historical SQL execution logs in the test environment of the target distributed database using a database SQL feature index system based on the target distributed database. This effectively improves the reliability and effectiveness of target data selection and provides an accurate and comprehensive data foundation for subsequent index recommendation using an index efficiency recommendation model. By inputting the target data into a preset index efficiency recommendation model and generating index recommendation result data corresponding to the target distributed database based on the data output by the index efficiency recommendation model, index recommendation results can be flexibly generated based on the target data, saving manpower and time costs. This effectively improves the automation, intelligence, and efficiency of the index recommendation process and enhances the effectiveness, accuracy, and reliability of the index recommendation results. Consequently, it effectively improves the efficiency of updating and querying the distributed database based on the index recommendation results, meets the data processing time requirements of distributed databases during IT architecture transformation, effectively improves the efficiency and reliability of IT architecture transformation, and enhances the user experience for maintenance personnel.

[0243] In another embodiment, the index recommendation device can be configured separately from the central processing unit 9100. For example, the index recommendation device can be configured as a chip connected to the central processing unit 9100, and the index recommendation function can be implemented through the control of the central processing unit.

[0244] like Figure 15 As shown, the electronic device 9600 may further include: a communication module 9110, an input unit 9120, an audio processor 9130, a display 9160, and a power supply 9170. It is worth noting that the electronic device 9600 does not necessarily need to include these components. Figure 15 All components shown; in addition, the electronic device 9600 may also include Figure 15 For components not shown, please refer to existing technologies.

[0245] like Figure 15 As shown, the central processing unit 9100, sometimes also referred to as a controller or operating control, may include a microprocessor or other processor device and / or logic device, which receives inputs and controls the operation of various components of the electronic device 9600.

[0246] The memory 9140 may be, for example, one or more of a cache, flash memory, hard drive, removable media, volatile memory, non-volatile memory, or other suitable devices. It may store the aforementioned failure-related information, and also store a program for executing that information. The central processing unit 9100 may execute the program stored in the memory 9140 to perform information storage or processing, etc.

[0247] Input unit 9120 provides input to central processing unit 9100. Input unit 9120 may be, for example, a keypad or touch input device. Power supply 9170 provides power to electronic device 9600. Display 9160 displays images and text. Display may be, for example, an LCD display, but is not limited thereto.

[0248] The memory 9140 can be a solid-state memory, such as a read-only memory (ROM), random access memory (RAM), SIM card, etc. It can also be a memory that retains information even when power is off, can be selectively erased, and contains more data; examples of this type of memory are sometimes referred to as EPROMs. The memory 9140 can also be some other type of device. The memory 9140 includes a buffer memory 9141 (sometimes referred to as a buffer). The memory 9140 may include an application / function storage unit 9142 for storing application programs and function programs or processes for executing the operation of the electronic device 9600 via the central processing unit 9100.

[0249] The memory 9140 may also include a data storage unit 9143 for storing data, such as contacts, digital data, pictures, sounds, and / or any other data used by the electronic device. The driver storage unit 9144 of the memory 9140 may include various drivers for the electronic device's communication functions and / or for performing other functions of the electronic device (such as messaging applications, address book applications, etc.).

[0250] The communication module 9110 is a transmitter / receiver 9110 that transmits and receives signals via the antenna 9111. The communication module (transmitter / receiver) 9110 is coupled to the central processing unit 9100 to provide input signals and receive output signals, which can be the same as in a conventional mobile communication terminal.

[0251] Based on different communication technologies, multiple communication modules 9110 can be configured in the same electronic device, such as cellular network modules, Bluetooth modules, and / or wireless LAN modules. The communication module (transmitter / receiver) 9110 is also coupled to a speaker 9131 and a microphone 9132 via an audio processor 9130 to provide audio output via the speaker 9131 and receive audio input from the microphone 9132, thereby realizing typical telecommunications functions. The audio processor 9130 may include any suitable buffer, decoder, amplifier, etc. Additionally, the audio processor 9130 is coupled to a central processing unit 9100, enabling on-device recording via the microphone 9132 and on-device playback of stored sound via the speaker 9131.

[0252] Embodiments of this application also provide a computer-readable storage medium capable of implementing all steps of the index recommendation method in the above embodiments. The computer-readable storage medium stores a computer program that, when executed by a processor, implements all steps of the index recommendation method in the above embodiments where the execution subject is a server or client. For example, when the processor executes the computer program, it implements the following steps:

[0253] Step 100: Based on the preset database SQL feature index system, extract target data from the database table information, database SQL information, and database SQL historical execution log content in the test environment corresponding to the target distributed database.

[0254] Step 200: Input the target data into a preset index efficiency recommendation model, and generate index recommendation result data corresponding to the target distributed database based on the data output by the index efficiency recommendation model.

[0255] As described above, the computer-readable storage medium provided in this application embodiment extracts target data from the database table information, database SQL information, and historical SQL execution logs in the test environment corresponding to the target distributed database through a database SQL feature index system based on the target distributed database. This effectively improves the reliability and effectiveness of target data selection and provides an accurate and comprehensive data foundation for subsequent index recommendation using an index efficiency recommendation model. By inputting the target data into a preset index efficiency recommendation model and generating index recommendation result data corresponding to the target distributed database based on the data output by the index efficiency recommendation model, index recommendation results can be flexibly generated based on the target data, saving manpower and time costs. This effectively improves the automation, intelligence, and efficiency of the index recommendation process and enhances the effectiveness, accuracy, and reliability of the index recommendation results. Consequently, it effectively improves the efficiency of updating and querying the distributed database based on the index recommendation results, meets the data processing time requirements of distributed databases during IT architecture transformation, effectively improves the efficiency and reliability of IT architecture transformation, and enhances the user experience for maintenance personnel.

[0256] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, apparatus, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0257] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (devices), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0258] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0259] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0260] Specific embodiments have been used to illustrate the principles and implementation methods of this invention. The descriptions of the embodiments above are only for the purpose of helping to understand the method and core ideas of this invention. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of this invention. Therefore, the content of this specification should not be construed as a limitation of this invention.

Claims

1. An index recommendation method, characterized in that, include: Based on a pre-defined database SQL feature indicator system, target data is extracted from the database table information, database SQL information, and database SQL historical execution log content in the test environment corresponding to the target distributed database. The database SQL feature indicator system includes: SQL keyword indicator system, SQL table data indicator system, and SQL execution result indicator system. The target data is input into a pre-trained index efficiency recommendation model, and index recommendation result data corresponding to the target distributed database is generated based on the data output by the index efficiency recommendation model, including: The target data is input into a pre-trained index-efficiency recommendation model so that the index-efficiency recommendation model outputs multiple index data. Based on the output of the index efficiency recommendation model, a performance effect prediction ranking list corresponding to the multiple index data is obtained; Based on the preset weight selection rules and the sorting list, a preset number of target indexes with the highest ranking are selected from each of the index data. Output the index recommendation results data corresponding to the target distributed database; The index efficiency recommendation model is generated based on a first indicator dataset and a second indicator dataset; the generation process of the first indicator dataset includes: Based on the preset database SQL feature index system, the corresponding first index dataset is extracted from the initial dataset using the SQL keyword index system and the SQL table data index system. The process of generating the second indicator dataset includes: Based on the execution plan indicators and consumption value indicators in the SQL execution result indicator system, an initial index efficiency evaluation is performed on the SQL historical execution log content of the target distributed database to generate an evaluation result; the execution plan indicators represent the index category efficiency of the execution plan, and the consumption value indicators represent the COST consumption of the execution plan; By comparing the historical SQL execution log content of the target distributed database in the production environment with the historical SQL execution log content of the database in the test environment corresponding to the first evaluation result, the execution plan indicators whose basic performance evaluation values ​​have changed in the first evaluation result, as well as the consumption value indicators whose maximum consumption value is greater than the preset threshold, are corrected to generate a second fitting result. The second index dataset is generated based on the results of the quadratic fitting.

2. The index recommendation method according to claim 1, characterized in that, Before extracting target data from the database table information, database SQL information, and historical SQL execution logs in the test environment corresponding to the target distributed database, based on the preset database SQL feature indicator system, the following steps are also included: Receive an index recommendation request, wherein the index recommendation request contains the identifier of a distributed database to be indexed; The distributed database in the index recommendation request is identified as the current target distributed database; Based on the identifier of the target distributed database, extract the database table information, database SQL information, and the database SQL historical execution log content in the test environment corresponding to the target distributed database.

3. The index recommendation method according to claim 1, characterized in that, Before extracting target data from the database table information, database SQL information, and historical SQL execution logs in the test environment corresponding to the target distributed database, based on the preset database SQL feature indicator system, the following steps are also included: Obtain database table information, database SQL information, and historical SQL execution log content in the test environment corresponding to a distributed database; Extract the data indicators of each SQL-related table from the database table information corresponding to the distributed database to form the corresponding SQL-related table data indicator system; Extract each SQL keyword indicator from the database SQL information corresponding to the distributed database to form a corresponding SQL keyword indicator system; In addition, extract each SQL execution result indicator from the database SQL historical execution log content of the distributed database in the test environment to form a corresponding SQL execution result indicator system; A database SQL feature indicator system is generated based on the table data indicator system, SQL keyword indicator system, and SQL execution result indicator system involved in the SQL.

4. The index recommendation method according to claim 3, characterized in that, The preset database SQL feature index system extracts target data from the database table information, database SQL information, and historical SQL execution logs in the test environment corresponding to the target distributed database, including: Data preprocessing is performed on the database table information and database SQL information corresponding to the target distributed database to obtain the corresponding initial dataset. The data preprocessing includes: text feature extraction, data normalization processing, and data information vectorization. Based on the SQL execution result indicator system in the preset database SQL feature indicator system, the index efficiency of the database SQL historical execution log content corresponding to the target distributed database is evaluated to generate a second indicator dataset containing the corresponding index efficiency evaluation results. Target data is generated based on the first indicator dataset and the second indicator dataset.

5. The index recommendation method according to claim 4, characterized in that, The SQL execution result indicator system, based on a preset database SQL feature indicator system, performs an index efficiency evaluation on the database SQL historical execution log content corresponding to the target distributed database to generate a second indicator dataset containing the corresponding index efficiency evaluation results, including: Based on the execution plan indicators and consumption value indicators in the SQL execution result indicator system, an initial index efficiency evaluation is performed on the SQL historical execution log content of the target distributed database to generate an evaluation result. Obtain the historical execution log content of the database SQL corresponding to the target distributed database in the production environment; The database SQL historical execution log content in the production environment is fitted with the database SQL historical execution log content in the test environment corresponding to the first evaluation result to generate a secondary fitting result, and a second indicator dataset is generated based on the secondary fitting result.

6. The index recommendation method according to claim 3, characterized in that, Before inputting the target data into a preset index efficiency recommendation model and generating the index recommendation result data corresponding to the target distributed database based on the data output by the index efficiency recommendation model, the method further includes: Based on a preset database SQL feature index system, historical target data is extracted from the database table information, database SQL information, and database SQL historical execution log content in the test environment corresponding to the distributed database. Multiple candidate indexes are extracted from the database table information and database SQL information corresponding to the distributed database using a preset candidate index selection rule. A preset random forest classifier is trained based on the historical target data and each of the candidate indices to obtain an index efficiency recommendation model for index efficiency recommendation.

7. The index recommendation method according to any one of claims 1 to 6, characterized in that, The step of inputting the target data into a preset index efficiency recommendation model and generating index recommendation result data corresponding to the target distributed database based on the data output by the index efficiency recommendation model further includes: Obtain the execution plan results for each of the target indexes in the test environment; Determine whether the execution efficiency of the execution plan results of each target index in the test environment is better than the execution plan results of the original index in the SQL historical execution log of the database. If so, then each target index is determined as the index recommendation result data corresponding to the target distributed database.

8. An index recommendation device, characterized in that, include: The data selection module is used to extract target data from the database table information, database SQL information, and database SQL historical execution log content in the test environment of the target distributed database based on a preset database SQL feature indicator system. The database SQL feature indicator system includes: SQL keyword indicator system, SQL table data indicator system, and SQL execution result indicator system. The model application module is used to input the target data into a pre-trained index-efficiency recommendation model, and generate index recommendation result data corresponding to the target distributed database based on the data output by the index-efficiency recommendation model, including: The target data is input into a pre-trained index-efficiency recommendation model so that the index-efficiency recommendation model outputs multiple index data. Based on the output of the index efficiency recommendation model, a performance effect prediction ranking list corresponding to the multiple index data is obtained; Based on the preset weight selection rules and the sorting list, a preset number of target indexes with the highest ranking are selected from each of the index data. Output the index recommendation results data corresponding to the target distributed database; The index efficiency recommendation model is generated based on the first indicator dataset and the second indicator dataset. The process of generating the first indicator dataset includes: Based on the preset database SQL feature index system, the corresponding first index dataset is extracted from the initial dataset using the SQL keyword index system and the SQL table data index system. The process of generating the second indicator dataset includes: Based on the execution plan indicators and consumption value indicators in the SQL execution result indicator system, an initial index efficiency evaluation is performed on the SQL historical execution log content of the target distributed database to generate an evaluation result; the execution plan indicators represent the index category efficiency of the execution plan, and the consumption value indicators represent the COST consumption of the execution plan; By comparing the historical SQL execution log content of the target distributed database in the production environment with the historical SQL execution log content of the database in the test environment corresponding to the first evaluation result, the execution plan indicators whose basic performance evaluation values ​​have changed in the first evaluation result, as well as the consumption value indicators whose maximum consumption value is greater than the preset threshold, are corrected to generate a second fitting result. The second index dataset is generated based on the results of the quadratic fitting.

9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the index recommendation method according to any one of claims 1 to 7.

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

Images