CN-113568888-B - Index recommendation method and device

CN113568888BCN 113568888 BCN113568888 BCN 113568888BCN-113568888-B

Abstract

The embodiment of the application provides an index recommendation method and device, which can be used in the technical field of artificial intelligence, and the method comprises the steps of extracting target data from database table information, database SQL information and database SQL historical execution log content corresponding to a target distributed database based on a preset database SQL characteristic 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 according to data output by the index efficiency recommendation model. The method and the device can effectively improve the automation degree, the intelligent degree and the efficiency of the index recommendation process, and can effectively improve the effectiveness, the accuracy and the reliability of the index recommendation result, and further can effectively improve the efficiency of updating and inquiring the distributed database according to the index recommendation result.

Inventors

ZHANG JIAO
ZHANG LINLIN

Assignees

中国工商银行股份有限公司

Dates

Publication Date: 20260508
Application Date: 20210730

Claims (10)

1. An index recommendation method, comprising: Extracting target data from database table information, database SQL information corresponding to a target distributed database and database SQL historical execution log content in a test environment based on a preset database SQL characteristic index system, wherein the database SQL characteristic index system comprises an SQL keyword index system, an SQL related table data index system and an SQL execution result index system; inputting the target data into a pre-trained index efficiency recommendation model, and generating index recommendation result data corresponding to the target distributed database according to the data output by the index efficiency recommendation model, wherein the method comprises the following steps: inputting the target data into a pre-trained index efficiency recommendation model so that the index efficiency recommendation model outputs a plurality of index data; Obtaining a performance effect estimated ordering list corresponding to the plurality of index data according to the output result of the index efficiency recommendation model; selecting target indexes with the top preset number from the index data based on preset weight selection rules and the sorting list; outputting index recommendation result data corresponding to the target distributed database; The index efficiency recommendation model is generated based on a first index data set and a second index data set, and the generation process of the first index data set comprises the following steps: Extracting a corresponding first index data set from the initial data set based on the SQL keyword index system and the SQL related table data index system in a preset database SQL characteristic index system; the generating process of the second index data set comprises the following steps: performing initial index efficiency evaluation on database SQL historical execution log content corresponding to the target distributed database based on an execution plan index and a consumption value index in the SQL execution result index system to generate a primary evaluation result, wherein the execution plan index represents index class efficiency of an execution plan, and the consumption value index represents COST consumption of the execution plan; Comparing the corresponding database SQL history execution log content of the target distributed database in the production environment with the corresponding database SQL history execution log content of the test environment in the primary evaluation result, correcting an execution plan index of the change of the basic performance evaluation value in the primary evaluation result, and a consumption value index of which the consumption maximum value is larger than a preset threshold value, so as to generate a secondary fitting result; and generating a second index data set according to the secondary fitting result.
2. The index recommendation method according to claim 1, wherein before extracting the target data from the database table information, the database SQL information, and the database SQL history execution log content corresponding to the target distributed database based on the preset database SQL feature index system, the method further comprises: Receiving an index recommendation request, wherein the index recommendation request comprises an identifier of a distributed database to be subjected to index recommendation; Determining the distributed database in the index recommendation request as a current target distributed database; and extracting database table information, database SQL information and database SQL historical execution log content corresponding to the target distributed database according to the identification of the target distributed database.
3. The index recommendation method according to claim 1, wherein before extracting the target data from the database table information, the database SQL information, and the database SQL history execution log content corresponding to the target distributed database based on the preset database SQL feature index system, the method further comprises: acquiring database table information, database SQL information and database SQL historical execution log content corresponding to a distributed database; Extracting each SQL related table data index from database table information corresponding to the distributed database to form a corresponding SQL related table data index system; Extracting each SQL keyword index from the database SQL information corresponding to the distributed database to form a corresponding SQL keyword index system; extracting each SQL execution result index from database SQL historical execution log content of the distributed database in a test environment to form a corresponding SQL execution result index system; And generating a database SQL characteristic index system according to the SQL related table data index system, the SQL keyword index system and the SQL execution result index system.
4. The index recommendation method according to claim 3, wherein extracting target data from database table information, database SQL information, and database SQL history execution log content in a test environment corresponding to a target distributed database based on a preset database SQL feature index system comprises: performing data preprocessing on database table information and database SQL information corresponding to a target distributed database to obtain a corresponding initial data set, wherein the data preprocessing comprises text feature extraction, data normalization processing and data information vectorization; based on the SQL execution result index system in a preset database SQL characteristic index system, index efficiency evaluation is carried out on database SQL historical execution log content corresponding to the target distributed database so as to generate a second index data set containing corresponding index efficiency evaluation results; Target data is generated from the first index data set and the second index data set.
5. The method of claim 4, wherein the performing index efficiency evaluation on the database SQL history execution log content corresponding to the target distributed database based on the SQL execution result index system in the preset database SQL feature index system to generate a second index data set including a corresponding index efficiency evaluation result includes: based on the execution plan index and the consumption value index in the SQL execution result index system, carrying out initial index efficiency evaluation on the database SQL historical execution log content corresponding to the target distributed database so as to generate a primary evaluation result; Acquiring database SQL historical execution log content corresponding to the database SQL of the target distributed database in a production environment; Fitting the corresponding database SQL history execution log content in the production environment with the corresponding database SQL history execution log content in the test environment in the primary evaluation result to generate a secondary fitting result, and generating a second index data set according to the secondary fitting result.
6. The index recommendation method according to claim 3, further comprising, before 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 data output by the index efficiency recommendation model: Extracting historical target data from database table information, database SQL information corresponding to the distributed database and database SQL historical execution log content in a test environment based on a preset database SQL characteristic index system; Extracting a plurality of candidate indexes from database table information and database SQL information corresponding to the distributed database by applying a preset candidate index selection rule; and training a preset random forest classifier based on the historical target data and each candidate index to obtain an index efficiency recommendation model for recommending the index efficiency.
7. The index recommendation method according to any one of claims 1 to 6, wherein the inputting the target data into a preset index efficiency recommendation model, and generating index recommendation result data corresponding to the target distributed database according to data output by the index efficiency recommendation model, further comprises: obtaining an execution plan result of each target index in the test environment; And judging whether the execution efficiency of the execution plan result of each target index in the test environment is better than that of the original index in the SQL historical execution log content of the database, if so, determining each target index as index recommendation result data corresponding to the target distributed database.
8. An index recommendation device, comprising: the data selection module is used for extracting target data from database table information, database SQL information and database SQL historical execution log content corresponding to a target distributed database based on a preset database SQL characteristic index system, wherein the database SQL characteristic index system comprises an SQL keyword index system, an SQL related table data index system and an SQL execution result index system; the model application module is used for inputting the target data into a pre-trained index efficiency recommendation model, generating index recommendation result data corresponding to the target distributed database according to the data output by the index efficiency recommendation model, and comprises the following steps: inputting the target data into a pre-trained index efficiency recommendation model so that the index efficiency recommendation model outputs a plurality of index data; Obtaining a performance effect estimated ordering list corresponding to the plurality of index data according to the output result of the index efficiency recommendation model; selecting target indexes with the top preset number from the index data based on preset weight selection rules and the sorting list; outputting index recommendation result data corresponding to the target distributed database; wherein the index efficiency recommendation model is generated based on a first index data set and a second index data set; The generating process of the first index data set comprises the following steps: Extracting a corresponding first index data set from the initial data set based on the SQL keyword index system and the SQL related table data index system in a preset database SQL characteristic index system; the generating process of the second index data set comprises the following steps: performing initial index efficiency evaluation on database SQL historical execution log content corresponding to the target distributed database based on an execution plan index and a consumption value index in the SQL execution result index system to generate a primary evaluation result, wherein the execution plan index represents index class efficiency of an execution plan, and the consumption value index represents COST consumption of the execution plan; Comparing the corresponding database SQL history execution log content of the target distributed database in the production environment with the corresponding database SQL history execution log content of the test environment in the primary evaluation result, correcting an execution plan index of the change of the basic performance evaluation value in the primary evaluation result, and a consumption value index of which the consumption maximum value is larger than a preset threshold value, so as to generate a secondary fitting result; and generating a second index data set according to the secondary fitting result.
9. An electronic device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the processor implements the index recommendation method of any one of claims 1 to 7 when executing the computer program.
10. A computer readable storage medium having stored thereon a computer program, characterized in that the computer program, when executed by a processor, implements the index recommendation method of any one of claims 1 to 7.

Description

Index recommendation method and device Technical Field The application relates to the technical field of data processing, in particular to the technical field of artificial intelligence, and particularly relates to an index recommendation method and device. Background In recent years, with the rapid development of financial technology, more and more financial institutions such as banks are enabled to enhance the application capability of technology and adhere to technology, and start IT transformation, for example, the conventionally adopted IT architecture system based on IBM mainframe, oracle database and EMC storage device is transformed into a digital architecture system adopting an X86 service system, a MySQL-based distributed database and a business model development promotion by using various emerging technologies. Along with development of banking and transformation of technical architecture, a distributed system framework gradually replaces a system framework mainly comprising IOE (IBM, oracle and EMC), but transformation of IT architecture and rapid growth of business data also make traditional database index design difficult to meet new database specifications, data processing efficiency difficult to meet updating and inquiring requirements, so that a series of problems, such as incapability of normally matching optimal indexes and reduction of database index efficiency, are gradually caused in a system transformation advancing process, and further the problems of long data processing time and the like in an IT architecture transformation process are caused. However, the existing index optimizing method requires operation and maintenance personnel to select after multiple attempts, which consumes a great deal of labor and time cost, and if the selection is performed according to preset selection criteria, the time of human participation can be shortened, but the index optimizing cannot be flexibly performed due to the fixed selection criteria, so that the effectiveness of an index result cannot be ensured, and therefore, the problem that the effectiveness, the efficiency and the automation requirements of the index optimizing cannot be simultaneously met exists in any mode. Disclosure of Invention Aiming at the problems in the prior art, the application provides the index recommendation method and the index recommendation device, which can effectively improve the automation degree, the intelligent degree and the efficiency of the index recommendation process, and can effectively improve the effectiveness, the accuracy and the reliability of the index recommendation result, further can effectively improve the updating and inquiring efficiency of the distributed database according to the index recommendation result, and can meet the data processing time requirement of the distributed database in the IT architecture transformation process. In order to solve the technical problems, the application provides the following technical scheme: in a first aspect, the present application provides an index recommendation method, including: Extracting target data from database table information, database SQL information corresponding to a target distributed database and database SQL historical execution log content in a test environment based on a preset database SQL characteristic index system; And inputting the target data into a preset index efficiency recommendation model, and generating index recommendation result data corresponding to the target distributed database according to the data output by the index efficiency recommendation model. Further, before extracting the target data from the database table information, the database SQL information and the database SQL history execution log content corresponding to the target distributed database based on the preset database SQL feature index system, the method further includes: Receiving an index recommendation request, wherein the index recommendation request comprises an identifier of a distributed database to be subjected to index recommendation; Determining the distributed database in the index recommendation request as a current target distributed database; and extracting database table information, database SQL information and database SQL historical execution log content corresponding to the target distributed database according to the identification of the target distributed database. Further, before extracting the target data from the database table information, the database SQL information and the database SQL history execution log content corresponding to the target distributed database based on the preset database SQL feature index system, the method further includes: acquiring database table information, database SQL information and database SQL historical execution log content corresponding to a distributed database; Extracting each SQL related table data index from database table information corresponding to the distributed database to form