Load-Oriented Data Index Recommendation Method, Its Device, and Storage Medium

Abstract

An embodiment of the present application provides a method, apparatus, and storage medium for data index recommendation oriented to a load, belonging to the technical field of data processing. The method includes: performing a cost test on an SQL statement to obtain a first execution cost of the SQL statement; generating a virtual index set according to a predefined field set; selecting a virtual index from the virtual index set as a target virtual index; obtaining a benefit cost of the SQL statement according to the first execution cost and the minimum execution cost; obtaining a recommendation evaluation value of the target virtual index according to the benefit cost; and selecting several target virtual indexes whose recommendation evaluation values meet a preset recommendation condition from all the target virtual indexes corresponding to the load as a recommended index set. The embodiment of the present application can intelligently provide a suitable recommended index set for the load by comprehensively analyzing each SQL statement in the load, can provide a good interaction experience for users, and does not require much cost, and has good application prospects.

Description

Technical Field

[0001] The embodiments of the present application relate to, but are not limited to, the technical field of data processing, and in particular, to a method and device for data index recommendation for a workload, an electronic device, and a computer-readable storage medium. Background Art

[0002] In a database, indexes are very important for the query performance of Structured Query Language (SQL). An appropriate index can change the execution mode of SQL from full table scan to index query, which may reduce the query time by an order of magnitude, thus obtaining a relatively significant performance improvement. Currently, the query mode of the workload in a given application scenario is generally implemented with the cooperation of human resources, which is a relatively fixed way and cannot achieve intelligent query. Therefore, it cannot provide a good interaction experience for users, and there is also the problem of high cost. Therefore, how to improve the intelligence level of the query method of the workload has become an urgent technical problem to be solved. Summary of the Invention

[0003] The following is an overview of the subject matter described in detail in this document. This overview is not intended to limit the scope of protection of the claims.

[0004] The main purpose of the embodiments of the present application is to propose a method and device for data index recommendation for a workload, an electronic device, and a computer-readable storage medium, aiming to provide an intelligent index query method.

[0005] To achieve the above object, a first aspect of the embodiments of the present application proposes a method for data index recommendation for a workload, where the workload includes multiple SQL statements, and the method includes:

[0006] For each of the SQL statements, perform a cost test on the SQL statement to obtain a first execution cost of the SQL statement;

[0007] Generate a virtual index set according to a predefined field set, where the virtual index set includes multiple virtual indexes, and each of the SQL statements corresponds to each of the virtual indexes, and the predefined field set is constructed according to the SQL statement;

[0008] Select one of the virtual indexes from the virtual index set as a target virtual index, where the target virtual index corresponds to the minimum execution cost of the SQL statement;

[0009] According to the first execution cost and the minimum execution cost, obtain a benefit cost of the SQL statement;

[0010] Obtain a recommendation evaluation value of the target virtual index according to the benefit cost;

[0011] Select several of the target virtual indexes whose recommended evaluation values meet the preset recommendation conditions from all the target virtual indexes corresponding to the load as the recommended index set.

[0012] According to the data index recommendation method provided by the embodiments of the present application, it has at least the following beneficial effects:

[0013] For each SQL statement of the load, obtain the first execution cost by performing a cost test on it, and generate a corresponding virtual index set for the SQL statement. Then, select a virtual index corresponding to the minimum execution cost of the SQL statement from the generated virtual index set as the target virtual index, so as to obtain the benefit cost of the SQL statement based on the first execution cost and the minimum execution cost, and thus obtain the recommended evaluation value of the target virtual index according to the benefit cost. Therefore, it is possible to select a recommended index set that meets the requirements from all the target virtual indexes corresponding to the load according to the recommended evaluation values of each target virtual index; in the above entire index recommendation process, by comprehensively analyzing each SQL statement in the load and intelligently providing a suitable recommended index set for the load, it can provide a good interaction experience for users, and without consuming too much cost, it has good application prospects.

[0014] In some embodiments, selecting one of the virtual indexes from the virtual index set as the target virtual index includes:

[0015] For each of the virtual indexes, add the virtual index to the SQL statement;

[0016] Perform a cost test on the SQL statement carrying the virtual index to obtain the second execution cost of the SQL statement;

[0017] Select one of the virtual indexes corresponding to the minimum second execution cost from the virtual index set as the target virtual index.

[0018] On the basis of the original SQL statement, add a virtual index to the SQL statement to facilitate performing a cost test on the SQL statement carrying the virtual index again to obtain the second execution cost. That is to say, the execution cost of the SQL statement corresponding to the case of virtual index gain can be obtained. This execution cost is different from the first execution cost that has been tested, and select one of the virtual indexes corresponding to the minimum second execution cost from the virtual index set as the target virtual index, that is, the virtual index with the greatest impact on the execution cost gain can be selected as the target virtual index.

[0019] In some embodiments, after selecting several of the target virtual indexes whose recommended evaluation values meet the preset recommendation conditions as the recommended index set, it further includes:

[0020] From the remaining target virtual indexes other than the several target virtual indexes whose recommended evaluation values meet the preset recommendation conditions, randomly select at least one of the target virtual indexes multiple times to replace the target virtual indexes in at least one of the recommended index sets, and obtain multiple optimized recommended index sets;

[0021] Calculate the total execution cost of all the optimized recommended index sets;

[0022] From all the optimized recommended index sets, select the optimized recommended index set with the minimum total execution cost as the new recommended index set.

[0023] Considering that a single target virtual index may have a beneficial impact on multiple SQL statements, the obtained recommended index set can be further optimized. That is, from the remaining target virtual indexes, randomly select several target virtual indexes multiple times to replace the target virtual indexes in the original recommended index set. Then, calculate the total execution cost for the replaced recommended index set to select the optimized recommended index set with the minimum total execution cost as the new recommended index set. This way of randomly replacing target virtual indexes is conducive to finding a more compliant recommended index set.

[0024] In some embodiments, the preset recommendation conditions include a preset quantity. The step of selecting several target virtual indexes whose recommended evaluation values meet the preset recommendation conditions as the recommended index set includes:

[0025] Sort each of the target virtual indexes in descending order according to the recommended evaluation value to obtain a target virtual index sequence;

[0026] In the target virtual index sequence, starting from the first target virtual index, sequentially select the target quantity of the target virtual indexes as the recommended index set, where the target quantity does not exceed the preset quantity.

[0027] By sorting each target virtual index in descending order according to the recommended evaluation value to obtain a target virtual index sequence, corresponding target virtual indexes can be selected from the front part of the target virtual index sequence as the recommended index set. Due to the limitation of the target quantity, it can be ensured that the number of selected target virtual indexes can meet the preset requirements and prevent incorrect selection.

[0028] In some embodiments, the cost test for the SQL statement includes:

[0029] Input the SQL statement into a preset database;

[0030] Execute the SQL statement through the optimizer in the preset database to obtain the first execution cost of the SQL statement recorded by the optimizer.

[0031] The execution cost of an SQL statement can be obtained by using the index recommendation function of the optimizer in the preset database. That is to say, the optimizer can be used to execute the SQL statement to record the first execution cost of the SQL statement, so as to ensure that the execution cost of the SQL statement can be reliably obtained.

[0032] In some embodiments, the predefined field set includes multiple predefined fields. Generating a virtual index set according to the predefined field set includes:

[0033] Arrange and combine the multiple predefined fields according to a preset permutation and combination rule to obtain multiple virtual indexes to generate the virtual index set.

[0034] By arranging and combining multiple predefined fields according to a preset permutation and combination rule, multiple different virtual indexes can be obtained, which is beneficial to obtaining more virtual indexes as alternatives, so that in subsequent steps, the target virtual index can be further screened based on the obtained multiple virtual indexes.

[0035] In some embodiments, obtaining a recommended evaluation value of the target virtual index according to the benefit cost includes:

[0036] Normalize the benefit cost to obtain the recommended evaluation value of the target virtual index.

[0037] By normalizing the benefit cost, the benefit cost under standard values can be converted and used as the recommended evaluation value of the target virtual index, so as to have better detection scenario applicability when selecting multiple recommended evaluation values, which is beneficial to improving the accuracy of obtaining the recommended evaluation value of the target virtual index.

[0038] To achieve the above object, a second aspect of the embodiments of the present application proposes a data index recommendation device for load, where the load includes multiple SQL statements, and the device includes:

[0039] A first processing module, configured to perform a cost test on each SQL statement to obtain the first execution cost of the SQL statement;

[0040] A second processing module, configured to generate a virtual index set according to a predefined field set, where the virtual index set includes multiple virtual indexes, and each SQL statement corresponds to each virtual index, and the predefined field set is constructed according to the SQL statement;

[0041] A third processing module, configured to select one of the virtual indexes from the virtual index set as a target virtual index, where the target virtual index corresponds to the minimum execution cost of the SQL statement;

[0042] A fourth processing module, configured to obtain a benefit cost of the SQL statement according to the first execution cost and the minimum execution cost;

[0043] A fifth processing module, configured to obtain a recommended evaluation value of the target virtual index according to the benefit cost;

[0044] A sixth processing module, configured to select, from all the target virtual indexes corresponding to the load, several target virtual indexes whose recommended evaluation values meet a preset recommendation condition as a recommended index set.

[0045] To achieve the above object, a third aspect of the embodiments of the present application provides an electronic device, including a memory, a processor, a program stored on the memory and executable on the processor, and a data bus for implementing connection communication between the processor and the memory. When the program is executed by the processor, the method described in the first aspect above is implemented.

[0046] To achieve the above object, a fourth aspect of the embodiments of the present application provides a storage medium, which is a computer-readable storage medium for computer-readable storage. The storage medium stores one or more programs, and the one or more programs can be executed by one or more processors to implement the method described in the first aspect above.

[0047] A data index recommendation method, apparatus, and storage medium for a load proposed by the present application. For each SQL statement of the load, by performing a cost test on it to obtain a first execution cost, and generating a corresponding virtual index set for the SQL statement, and then selecting a virtual index corresponding to the minimum execution cost of the SQL statement from the generated virtual index set as a target virtual index, so as to obtain a benefit cost of the SQL statement based on the first execution cost and the minimum execution cost, and thus obtain a recommended evaluation value of the target virtual index according to the benefit cost. Therefore, a recommended index set that meets the requirements can be selected from all the target virtual indexes corresponding to the load according to the recommended evaluation values of each target virtual index; in the above entire index recommendation process, by comprehensively analyzing each SQL statement in the load, a suitable recommended index set is intelligently provided for the load, which can provide a good interaction experience for users and does not require much cost, and has a good application prospect.

[0048] Other features and advantages of the present application will be described in the following specification, and in part will become apparent from the specification, or will be understood by implementing the present application. The objectives and other advantages of the present application can be achieved and obtained through the structures specifically pointed out in the specification, claims, and drawings. Description of the Drawings

[0049] Figure 1 is a flowchart of the data index recommendation method provided by an embodiment of the present application;

[0050] Figure 2 is Figure 1 a flowchart of step S101 provided by an embodiment in

[0051] Figure 3 is Figure 1 a flowchart of step S102 provided by an embodiment in

[0052] Figure 4 is Figure 1 a flowchart of step S103 provided by an embodiment in

[0053] Figure 5 is Figure 1 a flowchart of step S106 provided by an embodiment in

[0054] Figure 6 is Figure 1 a flowchart after step S106 provided by an embodiment in

[0055] Figure 7 is a schematic structural diagram of the data index recommendation device provided by an embodiment of the present application;

[0056] Figure 8 is a schematic hardware structure diagram of the electronic device provided by an embodiment of the present application. Detailed Embodiments

[0057] In order to make the objectives, technical solutions, and advantages of the present application clearer, the present application will be further described in detail below with reference to the drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0058] It should be noted that although functional module division is performed in the device schematic diagram and the logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in a different order from the module division in the device or the order in the flowchart. Terms such as "first" and "second" in the specification, claims, and the above drawings are used to distinguish similar objects and do not necessarily need to describe a specific order or sequence.

[0059] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs. The terms used herein are for the purpose of describing embodiments of this application only and are not intended to limit this application.

[0060] First, parse several nouns involved in this application:

[0061] SQL: A special-purpose programming language, which is a database query and programming language used to access data and query, update, and manage relational database systems. SQL is a high-level non-procedural programming language that allows users to work on high-level data structures. It does not require users to specify the storage method of data, nor does it require users to understand the specific data storage method. Therefore, different database systems with completely different underlying structures can use the same structured query language as the interface for data input and management, and can be nested, which gives it great flexibility and powerful functions.

[0062] SQL database: A database language with various functions such as data manipulation and data definition. This language has the characteristic of interactivity and can provide great convenience for users. The SQL language can not only be independently applied to terminals, but also be used as a sub-language to provide effective assistance for other programming designs. In this program application, SQL can optimize the program function together with other programming languages, and then provide users with more and more comprehensive information. The SQL database includes two sub-databases, namely Microsoft SQL Server and Sybase SQL Server. Whether this database can run properly is related to the running security of the entire computer system.

[0063] Currently, the optimization of an SQL statement by a relational database (such as MySQL, PostgreSQL) can be divided into two stages, namely logical optimization and physical optimization. Logical optimization is rule-based optimization, which makes some equivalent logical transformations according to rules, such as column pruning and predicate pushdown; physical optimization is cost-based optimization, which will determine which method has the lowest cost according to statistical information and select a specific implementation for logical operators, such as whether to rely on indexes during query and whether to choose hash join or merge join during connection.

[0064] In addition, when recommending indexes for a workload, first consider two practical limitations:

[0065] 1. It is impossible or inappropriate to actually execute the SQL statements in the workload. Although the execution time is the most objective indicator for measuring index performance, the cost of obtaining the execution time is too high. Therefore, variables with approximate execution times can be found to replace it, such as the cost output by the SQL optimizer, that is, the execution cost involved in this application, which all express the same meaning. To make the cost more accurately reflect the actual execution time, as accurate index statistics as possible can be provided to the optimizer.

[0066] 2. It is impossible or inappropriate to actually create all possible indexes on the table. The overhead of creating indexes is huge, and if the SQL statements in the workload are not actually executed and only the cost output by the SQL optimizer is used, creating indexes on the table is of little significance. For the SQL optimizer, it only needs to add the statistical information corresponding to the possible indexes in the statistics to obtain the cost of the current SQL statement when these possible indexes exist on the table.

[0067] Based on this, the embodiments of this application provide a data index recommendation method, its device, and storage medium for a workload. For each SQL statement in the workload, the first execution cost is obtained by performing a cost test on it, and a corresponding virtual index set is generated for the SQL statement. Then, a virtual index corresponding to the minimum execution cost of the SQL statement is selected from the generated virtual index set as the target virtual index, so as to obtain the benefit cost of the SQL statement based on the first execution cost and the minimum execution cost. Thus, the recommended evaluation value of the target virtual index is obtained according to the benefit cost. Therefore, a recommended index set that meets the requirements can be selected from all the target virtual indexes corresponding to the workload according to the recommended evaluation values of each target virtual index; during the above entire index recommendation process, by comprehensively analyzing each SQL statement in the workload, a suitable recommended index set is intelligently provided for the workload, which can provide a good interaction experience for users and does not require much cost, having a good application prospect.

[0068] The data index recommendation method, device, electronic device, and computer-readable storage medium provided by the embodiments of this application are specifically described through the following embodiments. First, the data index recommendation method in the embodiments of this application is described.

[0069] The data index recommendation method provided by the embodiments of the present application relates to the technical field of data processing. The data index recommendation method provided by the embodiments of the present application can be applied to a terminal, can also be applied to a server side, or can be software running on a terminal or a server side. In some embodiments, the terminal can be a smart phone, a tablet computer, a notebook computer, a desktop computer, etc.; the server side can be configured as an independent physical server, can also be configured as a server cluster or a distributed system composed of multiple physical servers, or can also be configured as a cloud server providing basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN, and big data and artificial intelligence platforms; the software can be an application that implements the data index recommendation method, etc., but is not limited to the above forms.

[0070] The present application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet-type devices, multi-processor systems, microprocessor-based systems, set-top boxes, programmable consumer electronic devices, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, and so on. The present application can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present application can also be practiced in a distributed computing environment where tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.

[0071] Figure 1 FIG. is an optional flowchart of a load-oriented data index recommendation method provided by an embodiment of the present application, where the load includes multiple SQL statements. Figure 1 The method in may include, but is not limited to, steps S101 to S106.

[0072] Step S101: For each SQL statement, perform a cost test on the SQL statement to obtain the first execution cost of the SQL statement.

[0073] Step S102: Generate a virtual index set according to a predefined field set, where the virtual index set includes multiple virtual indexes, each SQL statement corresponds to each virtual index, and the predefined field set is constructed according to the SQL statement.

[0074] Step S103: Select a virtual index from the virtual index set as the target virtual index, where the target virtual index corresponds to the minimum execution cost of the SQL statement.

[0075] Step S104: Obtain the benefit cost of the SQL statement according to the first execution cost and the minimum execution cost.

[0076] Step S105: Obtain the recommended evaluation value of the target virtual index according to the benefit cost.

[0077] Step S106: Select several target virtual indexes that meet the preset recommendation conditions from all the target virtual indexes corresponding to the load as the recommended index set.

[0078] Steps S101 to S106 illustrated in the embodiments of the present application, for each SQL statement of the load, obtain the first execution cost by performing a cost test on it, and generate a corresponding virtual index set for the SQL statement. Then, select a virtual index corresponding to the minimum execution cost of the SQL statement from the generated virtual index set as the target virtual index, so as to obtain the benefit cost of the SQL statement based on the first execution cost and the minimum execution cost, and thus obtain the recommended evaluation value of the target virtual index according to the benefit cost. Therefore, it is possible to select a recommended index set that meets the requirements from all the target virtual indexes corresponding to the load according to the recommended evaluation values of each target virtual index. In the above entire index recommendation process, by comprehensively analyzing each SQL statement in the load, an appropriate recommended index set is intelligently provided for the load, which can provide a good interaction experience for users and does not require much cost, having good application prospects.

[0079] It should be emphasized that the number of loads can be set according to specific application scenarios, and the embodiments of the present application can execute the index recommendation method such as Steps S101 to S106 for the SQL statements of multiple loads simultaneously, which is not limited herein.

[0080] In Step S101 of some embodiments, the method of performing a cost test on the SQL statement can be various, which is not limited herein. For example, the SQL statement can be tested based on a determined optimizer, or the SQL statement can be tested based on the optimizer in the database, etc., which is not limited herein. Specific embodiments are given below for illustration.

[0081] Please refer to Figure 2 , in some embodiments, Step S101 may but is not limited to include Steps S201 to S202.

[0082] Step S201: Input the SQL statement into a preset database.

[0083] Step S202: Execute the SQL statement through the optimizer in the preset database to obtain the first execution cost of the SQL statement recorded by the optimizer.

[0084] In this step, the execution cost of the SQL statement can be obtained by using the index recommendation function of the optimizer in the preset database. That is to say, the optimizer can be used to execute the SQL statement to record the first execution cost of the SQL statement, so as to ensure that the execution cost of the SQL statement can be obtained reliably.

[0085] In step S201 of some embodiments, the type of the preset database is not limited, and it is specifically determined according to the current test environment and test conditions. For example, it can be a common relational database in the art, or it can be some custom databases, that is, as long as the database has the optimizer execution function, it may be used as the preset database in the embodiments of the present application.

[0086] In step S202 of some embodiments, executing the SQL statement through the optimizer is well known to those skilled in the art, so it will not be elaborated here; the type of the optimizer can be selected according to the actual application scenario and is not limited here.

[0087] In step S102 of some embodiments, the virtual index corresponds to the actual recommended index, expressing the meaning that it may be used as an index. That is to say, it is necessary to finally find the virtual index that meets the requirements from multiple virtual indexes as the actual recommended index; the predefined field sets of different SQL statements can be different, but are not limited; since each SQL statement corresponds to each virtual index respectively, the combination of each virtual index and the SQL statement can be tested respectively to further determine which virtual indexes can bring a beneficial impact to the SQL statement. Specific embodiments are given below for illustration.

[0088] Please refer to Figure 3 , in some embodiments, when the predefined field set includes multiple predefined fields, step S102 may but is not limited to include step S301.

[0089] Step S301: Arrange and combine the multiple predefined fields according to the preset arrangement and combination rules to obtain multiple virtual indexes to generate a virtual index set.

[0090] In this step, by arranging and combining the multiple predefined fields according to the preset arrangement and combination rules, various different virtual indexes can be obtained, which is beneficial to obtaining more virtual indexes as alternatives, so as to further screen the target virtual index based on the obtained multiple virtual indexes in the subsequent steps.

[0091] In step S301 of some embodiments, the preset permutation and combination rules can be set by oneself and are not limited here; since the number and types of predefined fields in different SQL statements may be different, for a single SQL statement, the corresponding multiple virtual indexes finally generated may be different, but it does not exclude the possibility of overlapping parts, which are all normal situations.

[0092] A specific example is given below to illustrate the working principles and processes of the above embodiments, but it should not be construed as a limitation on step S201, step S202, and step S301.

[0093] Example 1:

[0094] Let the SQL statement pass through the optimizer, record the current cost, and analyze the columns in the SQL statement. For a single SQL statement, 4 field sets can be constructed for it, namely EQ, O, RANGE, and REF:

[0095] Specifically, the fields on both sides of the equal sign in the SQL statement are placed in the set EQ, the fields that appear after order by and group by and the fields that appear on both sides of the join condition are placed in the set O, the fields on both sides of the range condition are placed in the set RANGE, and the other fields are placed in the set REF.

[0096] For example, for the SQL statement "Select t.id,t.age from t where t.a=1and t.b=2and t.c>3order by t.d,t.e desc", EQ = {a,b}, O = {[id],[d,e]}, RANGE = {c}, REF = {age}.

[0097] After constructing the above field sets, the fields in these 4 sets can be permuted and combined according to 6 specific rules to establish virtual indexes. Among them, the 6 permutation and combination rules are as follows: ① EQ+O, ② EQ+O+RANGE, ③ EQ+O+RANGE+REF, ④ O+EQ, ⑤ O+EQ+RANGE, and ⑥ O+EQ+RANGE+REF.

[0098] For the SQL statement in the above example, 24 virtual indexes can be established according to these 6 rules. Taking rule ② as an example, 4 virtual indexes can be established according to rule ②, which are: [a,i d,c], [b,i d,c], [a,d,e,c], and [b,d,e,c].

[0099] In step S103 of some embodiments, if the target virtual index corresponds to the minimum execution cost of the SQL statement, it indicates that the target virtual index has the greatest impact on the gain of the execution cost of the SQL statement. That is to say, the obtained target virtual index can meet the execution requirements of the SQL statement.

[0100] Please refer to Figure 4 , in some embodiments, step S103 may but is not limited to include steps S401 to S403.

[0101] Step S401: For each virtual index, add the virtual index to the SQL statement;

[0102] Step S402: Perform a cost test on the SQL statement with the virtual index to obtain the second execution cost of the SQL statement;

[0103] Step S403: Select a virtual index with the smallest corresponding second execution cost from the virtual index set as the target virtual index.

[0104] In this step, based on the original SQL statement, the virtual index is added to the SQL statement to facilitate performing a cost test on the SQL statement with the virtual index again to obtain the second execution cost. That is to say, the execution cost of the SQL statement corresponding to the case of virtual index gain can be obtained. This execution cost is different from the first execution cost that has been tested. And a virtual index with the smallest corresponding second execution cost is selected from the virtual index set as the target virtual index. That is, a virtual index with the greatest impact on the execution cost gain can be selected as the target virtual index.

[0105] In step S402 of some embodiments, performing a cost test on the SQL statement with the virtual index to obtain the second execution cost of the SQL statement is similar in working principle to steps S101, S201 to S202 in the above embodiments, and all are used to implement the cost test. Therefore, the specific implementation manner of step S402 in this embodiment can refer to the specific implementation manners of steps S101, S201 to S202 in the foregoing embodiments. To avoid redundancy, it will not be elaborated here.

[0106] In step S104 of some embodiments, it may but is not limited to subtract the first execution cost from the minimum execution cost to obtain the benefit cost of the SQL statement.

[0107] In step S104 of some embodiments, a preset evaluation value of the benefit cost can be set, that is, by comparing the preset evaluation value with the calculated benefit cost. If the benefit cost is within a reasonable preset range compared to the preset evaluation value, it indicates that the benefit cost is normal, and then it can be determined that the two processes of executing the SQL statement are normal and error-free. Otherwise, it can be determined that there may be problems with the two processes of executing the SQL statement. In this case, it is considered not to retain the benefit cost and not use it for subsequent step judgments to improve the stability and accuracy of the overall index recommendation.

[0108] In step S104 of some embodiments, considering the influence of the physical space occupied by the newly built index, the result of dividing the second execution cost of each virtual index by the number of bytes occupied by the virtual index can also be used as another quantization index to replace the benefit cost, which can also achieve a similar quantization evaluation effect, and this is not limited here.

[0109] To better illustrate the working principles and processes of the above embodiments of the present application, the following will be described in conjunction with specific examples.

[0110] Example 2:

[0111] First, let the SQL statement pass through the optimizer, record the current cost, and analyze the columns in the SQL statement. For a SQL statement, 4 field sets can be constructed for it, namely EQ, O, RANGE, and REF:

[0112] Specifically, the fields on both sides of the equal sign in the SQL statement are placed in the set EQ, the fields that appear after order by and group by and the fields that appear on both sides of the join condition are placed in the set O, the fields on both sides of the range condition are placed in the set RANGE, and the other fields are placed in the set REF.

[0113] For example, for the SQL statement "Select t.id,t.age from t where t.a=1and t.b=2and t.c>3order by t.d,t.e desc", EQ = {a,b}, O = {[id],[d,e]}, RANGE = {c}, REF = {age}.

[0114] After constructing the above field sets, the fields in these 4 sets can be arranged and combined according to 6 specific rules to establish virtual indexes. Among them, the 6 arrangement and combination rules are as follows: ① EQ+O, ② EQ+O+RANGE, ③ EQ+O+RANGE+REF, ④ O+EQ, ⑤ O+EQ+RANGE, and ⑥ O+EQ+RANGE+REF.

[0115] For the SQL statements in the above examples, 24 virtual indexes can be established according to these 6 rules.

[0116] Then, let the SQL statements pass through the optimizer again with these virtual indexes. In the physical optimization stage, it is possible to estimate how much performance improvement the established virtual indexes can bring to the SQL statements, select the virtual index that minimizes the execution cost of the SQL statements, and use it as the target virtual index. This execution cost is denoted as new cost, and record benefit cost = cost - newcost. Benefit cost is the benefit cost.

[0117] In some embodiments, step S105 may but is not limited to include step S501.

[0118] Step S501: Normalize the benefit cost to obtain the recommended evaluation value of the target virtual index.

[0119] In this step, by normalizing the benefit cost, the benefit cost under standard values can be obtained and used as the recommended evaluation value of the target virtual index, so as to have a better detection scenario applicability when selecting multiple recommended evaluation values, which is conducive to improving the accuracy of obtaining the recommended evaluation value of the target virtual index.

[0120] In step S501 of some embodiments, the normalization process is a relatively common technique in the field of data processing and is well-known to those skilled in the art. To avoid redundancy, it will not be elaborated here.

[0121] In step S106 of some embodiments, the types and forms of the preset recommendation conditions can be various and are not limited here. For example, the preset recommendation conditions can be specifically set as quantity limit conditions, recommended evaluation value limit conditions, or combinations of the above limit conditions, and are presented and judged by setting thresholds.

[0122] In step S106 of some embodiments, the number of target virtual indexes as the recommended index set can be preset or determined according to the preset recommendation conditions, which is not limited here.

[0123] Please refer to Figure 5 , in some embodiments, when the preset recommendation conditions include a preset quantity, step S106 may but is not limited to include steps S601 to S602.

[0124] Step S601: Sort each target virtual index in descending order according to the recommended evaluation value to obtain a target virtual index sequence;

[0125] Step S602: In the target virtual index sequence, starting from the first target virtual index, sequentially select a target number of target virtual indexes as the recommended index set, where the target number does not exceed a preset number.

[0126] In this step, the target virtual index sequence is obtained by sorting each target virtual index in descending order according to the recommendation evaluation value. Thus, the corresponding target virtual indexes can be selected from the front part of the target virtual index sequence as the recommended index set. Due to the limitation of the target number, it can be ensured that the number of selected target virtual indexes can meet the preset requirements and prevent incorrect selection.

[0127] In step S602 of some embodiments, the specific value of the target number can be selected and set according to the actual application scenario, as long as it does not exceed the preset number.

[0128] In steps S601 to S602 of some embodiments, the target virtual index sequence can also be obtained by sorting in ascending order according to the recommendation evaluation value, and then the target virtual index sequence is filtered. Since this method is similar to the working principle of steps S601 to S602, it will not be elaborated here.

[0129] To better illustrate the working principle and process of the above embodiments of the present application, the following is described with specific examples.

[0130] Example 3:

[0131] Based on the above Example 2, that is, after obtaining the benefit cost = cost - new cost, the selected target virtual indexes are evaluated and scored. That is, the benefit cost of performance improvement is normalized to obtain a score, and then the virtual index and its score are added to a global map. Since the index recommendation recommends N indexes according to the workload, and each SQL statement in the workload will recommend a target virtual index, then if there are M SQL statements in the workload, after one round of execution, M target virtual indexes will be obtained. After the execution of the workload is completed and the execution results are integrated, a target virtual index score table sorted in descending order is obtained. It can be considered to select the first N (N <= M) target virtual indexes from the score table as the recommended index set, where the preset number is set to M.

[0132] Please refer to Figure 6 , in some embodiments, after step S106, it may further include but is not limited to steps S701 to S703.

[0133] Step S701: Randomly select at least one target virtual index from the remaining target virtual indexes except for several target virtual indexes whose recommended evaluation values meet the preset recommendation conditions, and replace the target virtual indexes in at least one recommended index set to obtain multiple optimized recommended index sets;

[0134] Step S702: Calculate the total execution cost of all optimized recommended index sets;

[0135] Step S703: Select an optimized recommended index set with the minimum total execution cost from all optimized recommended index sets as the new recommended index set.

[0136] In this step, considering that a single target virtual index may have a beneficial impact on multiple SQL statements, the obtained recommended index set can be further optimized. That is, randomly select several target virtual indexes from the remaining target virtual indexes to replace the target virtual indexes in the original recommended index set. Then, calculate the total execution cost for the replaced recommended index set, and select an optimized recommended index set with the minimum total execution cost as the new recommended index set. This way of randomly replacing target virtual indexes is conducive to finding a more compliant recommended index set.

[0137] In step S701 of some embodiments, the number and frequency of randomly selecting target virtual indexes to be replaced are not limited and can be set according to specific application scenarios.

[0138] In step S702 of some embodiments, calculating the total execution cost of all optimized recommended index sets is similar in working principle to steps S101, S201 to S202, and S402 in the above embodiments, all of which are used to implement the test of execution cost. The difference is only that in step S702, the execution costs of each SQL statement need to be added up. Therefore, the specific implementation of step S702 in this embodiment can refer to the specific implementation of steps S101, S201 to S202, and S402 in the foregoing embodiments. To avoid redundancy, it will not be elaborated here.

[0139] To better illustrate the working principle and process of the above embodiments of the present application, the following will be described with specific examples.

[0140] Example 4:

[0141] Based on the above Example 3, considering that the recommended index set composed of the first N target virtual indexes may not be optimal, the reason is that this scoring is only for a single SQL statement. However, some target virtual indexes may have an optimization impact on each SQL statement, but their actual evaluation scores are relatively low. For example, there is a very time-consuming SQL statement in the workload, and the recommended indexes output are index1 and index2. However, when recommending an index set for the workload, only index1 is selected. Without index2, in the case of only index1, the very time-consuming SQL statement cannot be optimized. Therefore, an additional metric is needed to quantify the quality of the index set. It can be the total execution cost of the workload assuming that all target virtual indexes in the recommended index set exist. The smaller the total execution cost of the workload, the more reasonable the recommended index set is.

[0142] In response to the above considerations, the Swap and Re-evaluate algorithm is adopted for further optimization. Specifically:

[0143] Randomly swap the target virtual indexes ranked lower in the table with the target virtual indexes in the currently obtained recommended index set, and perform the swap multiple times to form different optimized recommended index sets respectively. Then, for each optimized recommended index set, continuously calculate the total execution cost of the workload under the preset recommended conditions (for example, set a time threshold of 120s). Finally, select the optimized recommended index set with the smallest total execution cost of the workload as the final recommended index set.

[0144] Example 5:

[0145] The data index recommendation method of the embodiments of the present application is used for simulation experiments to further verify the embodiments of the present application. According to different workloads, there are two verification methods. Specifically:

[0146] 1. Use TPC-H as the workload for testing

[0147] The number of target virtual indexes in the recommended index set is set to N = 3 and N = 6 respectively, that is, the number of the final recommended indexes is 3 and 6 respectively. The simulation results reveal that when the selected indexes are added to the SQL statements, the overall execution time decreases by 11% when N = 3, and the overall execution time decreases by 29.8% when N = 6.

[0148] 2. Use TPC-DS as the workload for testing

[0149] The number sizes of the target virtual indexes in the recommended index set are set to N = 13 and N = 28 respectively, that is, the numbers of the final recommended indexes are 13 and 28 respectively. The simulation results reveal that when the selected indexes are added to the SQL statements, the overall execution time drops by 16.1% when N = 13, and the overall execution time drops by 33.85% when N = 28.

[0150] According to the above verification results, it can be known that the data index recommendation method proposed in the embodiment of the present application can intelligently recommend appropriate indexes for the input workload according to the input workload, so that after these recommended indexes are newly created, the execution time of the workload is significantly reduced compared with the original.

[0151] Please refer to Figure 7 , the embodiment of the present application further provides a data index recommendation device for the workload, which can implement the above data index recommendation method. Among them, the workload includes multiple SQL statements, and the device includes:

[0152] The first processing module is used to perform a cost test on each SQL statement to obtain the first execution cost of the SQL statement;

[0153] The second processing module is used to generate a virtual index set according to a predefined field set. Among them, the virtual index set includes multiple virtual indexes, and each SQL statement corresponds to each virtual index respectively. The predefined field set is constructed according to the SQL statement;

[0154] The third processing module is used to select a virtual index from the virtual index set as the target virtual index, where the target virtual index corresponds to the minimum execution cost of the SQL statement;

[0155] The fourth processing module is used to obtain the benefit cost of the SQL statement according to the first execution cost and the minimum execution cost;

[0156] The fifth processing module is used to obtain the recommended evaluation value of the target virtual index according to the benefit cost;

[0157] The sixth processing module is used to select several target virtual indexes whose recommended evaluation values meet the preset recommendation conditions from all the target virtual indexes corresponding to the workload as the recommended index set.

[0158] The specific implementation manner of this data index recommendation device is basically the same as the specific embodiment of the above data index recommendation method, and will not be elaborated here.

[0159] The embodiments of the present application also provide an electronic device, which includes: a memory, a processor, a program stored on the memory and executable on the processor, and a data bus for implementing connection communication between the processor and the memory. When the program is executed by the processor, the above data index recommendation method is implemented. The electronic device can be any intelligent terminal including a tablet computer, a vehicle-mounted computer, etc.

[0160] Please refer to Figure 8 , Figure 8 which schematically shows the hardware structure of an electronic device according to another embodiment. The electronic device includes:

[0161] A processor 901, which can be implemented in ways such as a general-purpose CPU (Central Processing Unit), a microprocessor, an application-specific integrated circuit (ASIC), or one or more integrated circuits, and is used to execute relevant programs to implement the technical solutions provided by the embodiments of the present application;

[0162] A memory 902, which can be implemented in forms such as a read-only memory (ROM), a static storage device, a dynamic storage device, or a random access memory (RAM). The memory 902 can store an operating system and other application programs. When implementing the technical solutions provided by the embodiments of this specification through software or firmware, the relevant program codes are stored in the memory 902, and the processor 901 is called to execute the data index recommendation method of the embodiments of the present application;

[0163] An input / output interface 903, which is used to implement information input and output;

[0164] A communication interface 904, which is used to implement communication interaction between this device and other devices, and can implement communication through a wired method (such as USB, network cable, etc.) or through a wireless method (such as a mobile network, WIFI, Bluetooth, etc.);

[0165] A bus 905, which transmits information between various components of the device (such as the processor 901, the memory 902, the input / output interface 903, and the communication interface 904);

[0166] Among them, the processor 901, the memory 902, the input / output interface 903, and the communication interface 904 are communicatively connected to each other inside the device through the bus 905.

[0167] The embodiment of the present application further provides a storage medium, which is a computer-readable storage medium for computer-readable storage. The storage medium stores one or more programs, and the one or more programs can be executed by one or more processors to implement the above data index recommendation method.

[0168] As a non-transitory computer-readable storage medium, the memory can be used to store non-transitory software programs and non-transitory computer-executable programs. In addition, the memory may include high-speed random access memory, and may also include non-transitory memory, such as at least one disk storage device, a flash memory device, or other non-transitory solid-state storage devices. In some embodiments, the memory may optionally include a memory remotely disposed relative to the processor, and these remote memories can be connected to the processor through a network. Examples of the above networks include, but are not limited to, the Internet, an enterprise intranet, a local area network, a mobile communication network, and combinations thereof.

[0169] For each SQL statement of the workload, the data index recommendation method, data index recommendation device, electronic device, and storage medium provided by the embodiment of the present application obtain a first execution cost by performing a cost test on it, and generate a corresponding virtual index set for the SQL statement. Then, a virtual index corresponding to the minimum execution cost of the SQL statement is selected from the generated virtual index set as the target virtual index, so as to obtain the benefit cost of the SQL statement based on the first execution cost and the minimum execution cost, and thus obtain the recommendation evaluation value of the target virtual index according to the benefit cost. Therefore, a recommended index set that meets the requirements can be selected from all the target virtual indexes corresponding to the workload according to the recommendation evaluation values of each target virtual index; in the above entire index recommendation process, by comprehensively analyzing each SQL statement in the workload and intelligently providing a suitable recommended index set for the workload, it can provide a good interaction experience for users, and does not require much cost, having a good application prospect.

[0170] The embodiments described in the embodiments of the present application are for more clearly explaining the technical solutions of the embodiments of the present application, and do not constitute a limitation to the technical solutions provided by the embodiments of the present application. Those skilled in the art can know that with the evolution of technology and the emergence of new application scenarios, the technical solutions provided by the embodiments of the present application are also applicable to similar technical problems.

[0171] Those skilled in the art can understand that Figure 1-6 the technical solutions shown do not constitute a limitation to the embodiments of the present application, and may include more or fewer steps than those shown, or combine certain steps, or different steps.

[0172] The above describes specific embodiments of the present application, and other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than in the embodiments and still achieve the desired result. Additionally, the processes depicted in the figures do not necessarily have to be performed in the specific order or a sequential order shown to achieve the desired result. In certain embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0173] Each embodiment in this application is described in a progressive manner. For the same or similar parts among the embodiments, reference can be made to each other. Each embodiment focuses on the differences from other embodiments. In particular, for the embodiments of the apparatus, device, and computer-readable storage medium, since they are basically similar to the method embodiments, the description is relatively simple. For the relevant parts, reference can be made to the description of the method embodiments.

[0174] The apparatus, device, and computer-readable storage medium provided in the embodiments of this application correspond to the method. Therefore, the apparatus, device, and non-volatile computer storage medium also have beneficial technical effects similar to the corresponding method. Since the beneficial technical effects of the method have been described in detail above, the beneficial technical effects of the corresponding apparatus, device, and computer storage medium will not be elaborated here.

[0175] In the 1990s, it was obvious to distinguish whether an improvement to a technology was a hardware improvement (e.g., improvement to the circuit structure of diodes, transistors, switches, etc.) or a software improvement (improvement to the method flow). However, with the development of technology, many method flow improvements today can be regarded as direct improvements to the hardware circuit structure. Almost all designers obtain the corresponding hardware circuit structure by programming the improved method flow into the hardware circuit. Therefore, it cannot be said that an improvement to a method flow cannot be implemented with a hardware entity module.

[0176] For example, a programmable logic device (PLD) (such as a field programmable gate array (FPGA)) is an integrated circuit whose logic function is determined by the user programming the device. The designer can program a digital system "integrated" on a PLD by himself / herself, without having to ask a chip manufacturer to design and fabricate a dedicated integrated circuit chip. Moreover, nowadays, instead of manually fabricating integrated circuit chips, this programming is mostly implemented using "logic compiler" software, which is similar to the software compiler used in program development and writing. The original code before compilation also has to be written in a specific programming language, which is called a hardware description language (HDL). There is not only one type of HDL, but many types, such as:

[0177] ABEL (Advanced Boolean Expression Language); AHDL (Altera Hardware Description Language); Confluence; CUPL (Cornell University Programming Language); HDCal; and JHDL (Java Hardware Description Language); Lava, Lola, MyHDL, PALASM, RHDL (Ruby Hardware Description Language), etc.; Currently, among those skilled in the art, relatively more commonly used are VHDL (Very-High-Speed Integrated Circuit Hardware Description Language) and the language Verilog. Those skilled in the art should also be clear that by simply performing logical programming on the method flow using the above-mentioned several hardware description languages and programming it into the integrated circuit, it is easy to obtain the hardware circuit that implements the logical method flow.

[0178] The controller can be implemented in any suitable manner. For example, the controller can take the form of, for example, a microprocessor or a processor and a computer-readable medium storing computer-readable program code (such as software or firmware) executable by the (micro)processor, logic gates, switches, an application specific integrated circuit (ASIC), a programmable logic controller, and an embedded microcontroller. Examples of the controller include, but are not limited to, the following microcontrollers:

[0179] ARC 625D, Atmel AT91SAM, MicrochIP address PIC18F26K20, and Silicone Labs C8051F320. The memory controller can also be implemented as part of the control logic of the memory. Those skilled in the art also know that in addition to implementing the controller in the form of pure computer-readable program code, it is entirely possible to logically program the method steps to enable the controller to be implemented in the form of logic gates, switches, application specific integrated circuits, programmable logic controllers, and embedded microcontrollers to achieve the same function. Therefore, such a controller can be considered a hardware component, and the devices included therein for implementing various functions can also be regarded as the structures within the hardware component. Or even, the devices for implementing various functions can be regarded as either software modules for implementing the method or structures within the hardware component.

[0180] The systems, devices, modules, or units illustrated in the above embodiments can be specifically implemented by a computer chip or an entity, or by a product with certain functions. A typical implementation device is a computer. Specifically, the computer can be, for example, a personal computer, a laptop computer, a cellular phone, a camera phone, a smart phone, a personal digital assistant, a media player, a navigation device, an email device, a game console, a tablet computer, a wearable device, or any combination of these devices.

[0181] For the convenience of description, when describing the above devices, they are described separately as various units according to their functions. Of course, when implementing the embodiments of the present application, the functions of each unit can be implemented in the same or multiple software and / or hardware.

[0182] Those skilled in the art should understand that the embodiments of the present application can be provided as a method, a system, or a computer program product. Therefore, the embodiments of the present application can take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware aspects. Moreover, the embodiments of the present application can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk memories, CD-ROMs, optical memories, etc.) containing computer-usable program code.

[0183] This specification is described with reference to the flowcharts and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the present application. It should be understood that each flow and / or block in the flowchart and / or block diagram, and combinations of flows and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to the processors of general-purpose computers, special-purpose computers, embedded processors, or other programmable data processing devices to generate a machine, such that the instructions executed by the processors of the computer or other programmable data processing devices produce means for implementing the functions specified in the Figure 1 one or more flows and / or blocks Figure 1 one or more blocks.

[0184] These computer program instructions can also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, such that the instructions stored in the computer-readable memory produce a manufactured article including instruction means that implement the functions specified in the Figure 1 one or more flows and / or blocks Figure 1 one or more blocks.

[0185] These computer program instructions can also be loaded onto a computer or other programmable data processing device, such that a series of operation steps are executed on the computer or other programmable device to generate a computer-implemented process, and thus the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in the Figure 1 one or more flows and / or blocks Figure 1 one or more blocks.

[0186] In a typical configuration, a computing device includes one or more processors (CPUs), an input / output interface, a network interface, and memory.

[0187] The memory may include non-permanent memory in the form of computer-readable media, random access memory (RAM), and / or non-volatile memory such as read-only memory (ROM) or flash memory (flash RAM). The memory is an example of computer-readable media.

[0188] A computer-readable medium includes permanent and non-permanent, removable and non-removable media that can implement information storage by any method or technology. The information can be computer-readable instructions, data structures, program modules, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassette tapes, magnetic tape disk storage or other magnetic storage devices, or any other non-transmission medium that can be used to store information accessible by a computing device. As defined herein, a computer-readable medium does not include transitory computer-readable media, such as modulated data signals and carrier waves.

[0189] It should also be noted that the term "comprising", "including" or any other variant thereof is intended to cover non-exclusive inclusion, such that a process, method, commodity or device comprising a series of elements not only includes those elements but also includes other elements not expressly listed, or elements inherent to such process, method, commodity or device. Without further limitation, an element defined by the statement "comprising one..." does not exclude the presence of additional identical elements in the process, method, commodity or device comprising the said element.

[0190] In the embodiments of the present application, "at least one" means one or more, and "a plurality" means two or more. "And / or" describes the association relationship of associated objects, indicating that three relationships can exist. For example, A and / or B can represent the cases of A existing alone, A and B existing simultaneously, and B existing alone. Where A and B can be singular or plural. The character " / " generally indicates that the associated objects before and after are in an "or" relationship. "At least one of the following" and its similar expressions refer to any combination of these items, including any combination of single items or plural items. For example, at least one of a, b, and c can represent: a, b, c, a and b, a and c, b and c, or a and b and c, where a, b, and c can be single or multiple.

[0191] Embodiments of the present application may be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. Embodiments of the present application may also be practiced in a distributed computing environment where tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules may be located in local and remote computer storage media including storage devices.

[0192] The various embodiments in the present application are described in a progressive manner. For the same or similar parts among the various embodiments, reference can be made to each other. Each embodiment focuses on the differences from other embodiments. In particular, for the system embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and for the relevant parts, reference can be made to the description of the method embodiment.

[0193] The above description is only for the embodiments of the present application and is not intended to limit the present application. For those skilled in the art, various changes and modifications can be made to the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application shall be included within the scope of the claims of the present application.

Claims

1. A data index recommendation method for a load, characterized in that, the load includes multiple Structured Query Language (SQL) statements, and the method includes: For each of the SQL statements, perform a cost test on the SQL statement to obtain a first execution cost of the SQL statement; Generate a virtual index set according to a predefined field set, where the virtual index set includes multiple virtual indexes, and each of the SQL statements corresponds to each of the virtual indexes, and the predefined field set is constructed according to the SQL statement; Select one of the virtual indexes from the virtual index set as the target virtual index, where the target virtual index corresponds to the minimum execution cost of the SQL statement; Obtain a benefit cost of the SQL statement according to the first execution cost and the minimum execution cost; Obtain a recommendation evaluation value of the target virtual index according to the benefit cost; Select several of the target virtual indexes whose recommendation evaluation values meet a preset recommendation condition from all the target virtual indexes corresponding to the load as a recommended index set; Randomly select at least one of the target virtual indexes from the remaining target virtual indexes other than the several target virtual indexes whose recommendation evaluation values meet the preset recommendation condition to replace at least one of the target virtual indexes in the recommended index set to obtain multiple optimized recommended index sets; Calculate the total execution cost of all the optimized recommended index sets; Select one of the optimized recommended index sets with the minimum total execution cost from all the optimized recommended index sets as the new recommended index set; where the predefined field set includes multiple predefined fields, and generating the virtual index set according to the predefined field set includes: Arrange and combine the multiple predefined fields according to a preset permutation and combination rule to obtain the multiple virtual indexes to generate the virtual index set; where the predefined field set is obtained by the following steps: For each of the SQL statements, construct 4 field sets for the SQL statement, and the field sets include EQ, O, RANGE, and REF; Place the fields on both sides of the equal sign in the SQL statement into the set EQ, place the fields that appear after order by and group by in the SQL statement and the fields that appear on both sides of the join condition into the set O, and place the fields on both sides of the range condition in the SQL statement into the set RANGE, and place other fields into the set REF.

2. The data index recommendation method according to claim 1, characterized in that, selecting one of the virtual indexes from the virtual index set as the target virtual index includes: For each of the virtual indexes, add the virtual index to the SQL statement; Perform a cost test on the SQL statement carrying the virtual index to obtain a second execution cost of the SQL statement; Select one of the virtual indexes corresponding to the minimum second execution cost from the virtual index set as the target virtual index.

3. The data index recommendation method according to claim 1, It is characterized in that the preset recommendation condition includes a preset quantity, and selecting a plurality of the target virtual indexes whose recommendation evaluation values meet the preset recommendation condition as a recommendation index set, including: sorting each of the target virtual indexes in descending order according to the recommendation evaluation value to obtain a target virtual index sequence; in the target virtual index sequence, sequentially selecting a target quantity of the target virtual indexes as the recommendation index set starting from the first target virtual index, where the target quantity does not exceed the preset quantity.

4. The data index recommendation method according to claim 1, It is characterized in that the performing a cost test on the SQL statement includes: inputting the SQL statement into a preset database; executing the SQL statement through an optimizer in the preset database to obtain a first execution cost of the SQL statement recorded by the optimizer.

5. The data index recommendation method according to claim 1, It is characterized in that the obtaining the recommendation evaluation value of the target virtual index according to the benefit cost includes: performing a normalization process on the benefit cost to obtain the recommendation evaluation value of the target virtual index.

6. A data index recommendation device for a load, It is characterized in that the load includes multiple Structured Query Language (SQL) statements, and the data index recommendation device includes: a first processing module, configured to perform a cost test on each SQL statement to obtain a first execution cost of the SQL statement; a second processing module, configured to generate a virtual index set according to a predefined field set, where the virtual index set includes multiple virtual indexes, and each SQL statement corresponds to each virtual index, and the predefined field set is constructed according to the SQL statement; wherein, the predefined field set includes multiple predefined fields, and generating the virtual index set according to the predefined field set includes: performing permutation and combination on the multiple predefined fields according to a preset permutation and combination rule to obtain multiple virtual indexes to generate the virtual index set; wherein, the predefined field set is obtained by the following steps: for each SQL statement, constructing 4 field sets for the SQL statement, and the field sets include EQ, O, RANGE, and REF; putting the fields on both sides of the equal sign in the SQL statement into the set EQ, putting the fields that appear after order by and group by in the SQL statement and the fields that appear on both sides of the join condition into the set O, and putting the fields on both sides of the range condition in the SQL statement into the set RANGE, and putting other fields into the set REF; a third processing module, configured to select one of the virtual indexes from the virtual index set as a target virtual index, where the target virtual index corresponds to the minimum execution cost of the SQL statement; a fourth processing module, configured to obtain a benefit cost of the SQL statement according to the first execution cost and the minimum execution cost A fifth processing module, configured to obtain a recommended evaluation value of the target virtual index according to the revenue cost; A sixth processing module, configured to select, from all the target virtual indexes corresponding to the load, several target virtual indexes whose recommended evaluation values meet a preset recommendation condition as a recommended index set; The data index recommendation device is further configured to randomly select at least one target virtual index from the remaining target virtual indexes other than the several target virtual indexes whose recommended evaluation values meet the preset recommendation condition to replace the target virtual indexes in at least one of the recommended index sets, so as to obtain a plurality of optimized recommended index sets; calculate the total execution cost of all the optimized recommended index sets; and select, from all the optimized recommended index sets, an optimized recommended index set with the minimum total execution cost as the new recommended index set.

7. An electronic device, comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, the data index recommendation method according to any one of claims 1 to 5 is implemented.

8. A computer-readable storage medium, characterized in that it stores computer-executable instructions for executing the data index recommendation method according to any one of claims 1 to 5.

Images