Index scheme recommendation method and device, electronic equipment and storage medium

Abstract

The application provides an index scheme recommendation method and device, electronic equipment and storage medium. The method comprises: obtaining a query log corresponding to a target query statement and a plurality of data columns, the data columns including a result column to be queried by the target query statement and a condition column as a query condition; extracting a column query feature corresponding to each data column from the query log; determining a target data column among the plurality of data columns based on the column query feature corresponding to each data column and a preset screening rule; creating a plurality of candidate indexes based on the target data column; inputting the plurality of candidate indexes into a preset index recommendation algorithm to output a corresponding index scheme by the preset index recommendation algorithm. Thus, the index scheme is intelligently recommended, thereby improving the efficiency of recommending the index scheme and saving labor costs.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to an indexing scheme recommendation method, apparatus, electronic device, and storage medium. Background Technology

[0002] A database is a repository for organizing, storing, and managing data according to a data structure. It is a large, organized, shareable, and uniformly managed collection of data stored permanently on a computer. It is an important tool for data storage and management. SQL (Structured Query Language) is a special-purpose programming language, a database query and programming language used to access, query, update, and manage relational database systems.

[0003] In real-world business applications, slow SQL queries are frequently encountered, meaning queries take too long to execute and have low performance. These slow queries can lead to a degraded user experience, wasted system resources, and business processing delays. To address these issues, the primary and effective approach for DBAs (database administrators) is index recommendation. Traditional index recommendation methods often rely on the DBA's experience and expertise to manually select and create indexes, forming a corresponding indexing scheme recommendation.

[0004] However, the data in the database is constantly changing, and new query requirements are constantly emerging. Traditional manual index recommendation methods are difficult to keep up with and adapt to these changes in a timely manner. Therefore, there is an urgent need for an intelligent index recommendation scheme to solve the problem of slow query SQL optimization. Summary of the Invention

[0005] The purpose of this application is to provide an indexing scheme recommendation method, apparatus, electronic device, and storage medium to solve the problem that traditional manual indexing optimization methods are difficult to keep up with and adapt to changes in a timely manner. The specific technical solution is as follows:

[0006] Firstly, this application provides an indexing scheme recommendation method, including:

[0007] Obtain the query log and multiple data columns corresponding to the target query statement. The data columns include the result column to be queried by the target query statement and the condition column as the query condition.

[0008] Extract the column query features corresponding to each data column from the query log;

[0009] Based on the column query features corresponding to each of the data columns and the preset filtering rules, the target data column is determined among the multiple data columns;

[0010] Create multiple candidate indexes based on the target data column;

[0011] Multiple candidate indices are input into a preset index recommendation algorithm, which then outputs a corresponding index scheme.

[0012] In one possible implementation, after inputting the plurality of candidate indices into a preset index recommendation algorithm so that the preset index recommendation algorithm outputs a corresponding index scheme, the method further includes:

[0013] Extract the first execution data corresponding to the target query statement from the query log;

[0014] For each index scheme, the target query statement is executed using the index scheme to obtain the corresponding second execution data;

[0015] Based on the first execution data, the second execution data, and the resource consumption data corresponding to the indexing scheme, the evaluation score of the indexing scheme is determined;

[0016] Index schemes whose corresponding evaluation scores are greater than a preset threshold are identified as reference index schemes;

[0017] The preset index recommendation algorithm is optimized using the aforementioned reference index scheme.

[0018] In one possible implementation, the first execution data includes multiple first sub-data, and the second execution data includes second sub-data corresponding to each of the first sub-data. Determining the evaluation score of the indexing scheme based on the first execution data, the second execution data, and the resource usage data corresponding to the indexing scheme includes:

[0019] Determine the indicator weights corresponding to each of the first sub-data points and the resource usage data;

[0020] For each first sub-data point, calculate the data difference between the first sub-data point and the corresponding second sub-data point;

[0021] Based on the corresponding indicator weights, a weighted summation operation is performed on all data differences and resource usage data to obtain the evaluation score of the indexing scheme.

[0022] In one possible implementation, the first sub-data includes the first query execution time and the size of the first query result set, the second sub-data includes the second query execution time and the size of the second query result set, and the resource usage data includes disk space usage.

[0023] In one possible implementation, optimizing the preset index recommendation algorithm using the reference index scheme includes:

[0024] Determine the filtering parameters corresponding to the reference index scheme, wherein the filtering parameters are the parameters used by the preset index recommendation algorithm to filter and obtain the reference index scheme;

[0025] The preset index recommendation algorithm is optimized based on the filtering parameters.

[0026] In one possible implementation, the filtering rules include constraints corresponding to each column query feature, and the step of determining the target data column among multiple data columns based on the column query features corresponding to each data column and the preset filtering rules includes:

[0027] For each data column, determine whether the query features of each column corresponding to the data column meet the corresponding constraints.

[0028] If each column query feature of the data column meets the corresponding constraint conditions, the data column is determined to be the target data column.

[0029] In one possible implementation, the method further includes:

[0030] Get at least one query statement;

[0031] Determine the query execution data corresponding to each query statement, wherein the query execution data includes query execution time, query result set size, and index hit rate;

[0032] If the query execution time is less than a preset time threshold, or the size of the query result set is greater than a preset size, or the index hit rate is less than a preset hit rate, then the query statement is determined to be the target query statement.

[0033] Secondly, this application provides an indexing scheme recommendation device, comprising:

[0034] The acquisition module is used to acquire the query log and multiple data columns corresponding to the target query statement. The data columns include the result column to be queried by the target query statement and the condition column as the query condition.

[0035] The extraction module is used to extract the column query features corresponding to each data column from the query log;

[0036] The determination module is used to determine the target data column among multiple data columns based on the column query features corresponding to each data column and preset filtering rules;

[0037] A creation module is used to create multiple candidate indexes based on the target data column;

[0038] The input module is used to input multiple candidate indices into a preset index recommendation algorithm, so that the preset index recommendation algorithm outputs the corresponding index scheme.

[0039] In one possible implementation, the device further includes an optimization module for:

[0040] Extract the first execution data corresponding to the target query statement from the query log;

[0041] For each index scheme, the target query statement is executed using the index scheme to obtain the corresponding second execution data;

[0042] Based on the first execution data, the second execution data, and the resource consumption data corresponding to the indexing scheme, the evaluation score of the indexing scheme is determined;

[0043] Index schemes whose corresponding evaluation scores are greater than a preset threshold are identified as reference index schemes;

[0044] The preset index recommendation algorithm is optimized using the aforementioned reference index scheme.

[0045] In one possible implementation, the first execution data includes a plurality of first sub-data, and the second execution data includes second sub-data corresponding to each of the first sub-data. The optimization module is further configured to:

[0046] Determine the indicator weights corresponding to each of the first sub-data points and the resource usage data;

[0047] For each first sub-data point, calculate the data difference between the first sub-data point and the corresponding second sub-data point;

[0048] Based on the corresponding indicator weights, a weighted summation operation is performed on all data differences and resource usage data to obtain the evaluation score of the indexing scheme.

[0049] In one possible implementation, the first sub-data includes the first query execution time and the size of the first query result set, the second sub-data includes the second query execution time and the size of the second query result set, and the resource usage data includes disk space usage.

[0050] In one possible implementation, the optimization module is further configured to:

[0051] Determine the filtering parameters corresponding to the reference index scheme, wherein the filtering parameters are the parameters used by the preset index recommendation algorithm to filter and obtain the reference index scheme;

[0052] The preset index recommendation algorithm is optimized based on the filtering parameters.

[0053] In one possible implementation, the filtering rules include constraints corresponding to each column query feature, and the determining module is specifically used for:

[0054] For each data column, determine whether the query features of each column corresponding to the data column meet the corresponding constraints.

[0055] If each column query feature of the data column meets the corresponding constraint conditions, the data column is determined to be the target data column.

[0056] In one possible implementation, the apparatus further includes a statement determination module for:

[0057] Get at least one query statement;

[0058] Determine the query execution data corresponding to each query statement, wherein the query execution data includes query execution time, query result set size, and index hit rate;

[0059] If the query execution time is less than a preset time threshold, or the size of the query result set is greater than a preset size, or the index hit rate is less than a preset hit rate, then the query statement is determined to be the target query statement.

[0060] Thirdly, an electronic device is provided, including a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other through the communication bus;

[0061] Memory, used to store computer programs;

[0062] When a processor executes a program stored in memory, it implements any of the steps described in the first aspect.

[0063] Fourthly, a computer-readable storage medium is provided, characterized in that the computer-readable storage medium stores a computer program, which, when executed by a processor, implements the steps of any of the methods described in the first aspect.

[0064] Fifthly, a computer program product containing instructions is provided that, when run on a computer, causes the computer to execute any of the indexing scheme recommendation methods described above.

[0065] Beneficial effects of the embodiments in this application:

[0066] This application provides an indexing scheme recommendation method, apparatus, electronic device, and storage medium. In this embodiment, firstly, query logs and multiple data columns corresponding to a target query statement are obtained. Then, column query features corresponding to each data column are extracted from the query logs. Next, based on the column query features and preset filtering rules, a target data column is determined from the multiple data columns, and multiple indexes are created based on the target data column. Finally, the multiple indexes are input into a preset indexing recommendation algorithm, which outputs the corresponding indexing scheme. This achieves intelligent indexing scheme recommendation, thereby improving the efficiency of indexing scheme recommendation and saving labor costs.

[0067] Of course, implementing any product or method of this application does not necessarily require achieving all of the advantages described above at the same time. Attached Figure Description

[0068] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.

[0069] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0070] One or more embodiments are illustrated by way of example with reference numerals in the accompanying drawings. These illustrations do not constitute a limitation on the embodiments. Elements with the same reference numerals in the drawings are denoted as similar elements. Unless otherwise stated, the figures in the drawings are not to be limited by scale.

[0071] Figure 1 A flowchart illustrating an indexing scheme recommendation method provided in this application embodiment;

[0072] Figure 2 A flowchart illustrating another indexing scheme recommendation method provided in this application embodiment;

[0073] Figure 3 This is a schematic diagram of the structure of an indexing scheme recommendation device provided in an embodiment of this application;

[0074] Figure 4 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

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

[0076] The following disclosure provides numerous different embodiments or examples for implementing various structures of the invention. To simplify the disclosure, specific examples of components and arrangements are described below. These are merely examples and are not intended to limit the scope of the invention. Furthermore, reference numerals and / or letters may be repeated in different examples. Such repetition is for simplification and clarity and does not in itself indicate a relationship between the various embodiments and / or arrangements discussed.

[0077] Figure 1 This is a flowchart illustrating an indexing scheme recommendation method provided in an embodiment of this application. This method can be applied to one or more electronic devices such as smartphones, laptops, desktop computers, portable computers, and servers. Furthermore, the execution entity of this method can be hardware or software. When the execution entity is hardware, it can be one or more of the aforementioned electronic devices. For example, a single electronic device can execute this method, or multiple electronic devices can cooperate with each other to execute this method.

[0078] When the aforementioned execution entity is software, this method can be implemented as multiple software programs or software modules for installation and deployment, or as a single software program or software module for installation and deployment. It can also be integrated into a DBMS (Database Management System) as a plugin or extended function. Specific implementation methods can be adjusted based on the specific database system and machine learning algorithm. No specific limitations are made here.

[0079] like Figure 1 As shown, the method specifically includes:

[0080] S101, obtain the query log and multiple data columns corresponding to the target query statement, wherein the data columns include the result column to be queried by the target query statement and the condition column as the query condition.

[0081] The target query statement refers to the SQL (Structured Query Language) that needs to be optimized using an indexing scheme. In applications, it is generally a slow query SQL that suffers from problems such as excessive execution time and low performance. In practice, the target query statement can be an SQL statement filtered according to certain rules, or it can be any SQL statement specified by the user.

[0082] The query log records detailed execution information of the target query statement, such as the query time and the size of the returned result set.

[0083] Data columns include the result columns to be retrieved by the target query statement and the condition columns used as query conditions. Each data column can be obtained by analyzing the information contained in the target query statement.

[0084] In practical applications, the target query statement can be obtained through the following steps:

[0085] At least one query statement is obtained, and query execution data corresponding to each query statement is determined. The query execution data includes query execution time, query result set size, and index hit rate. If the query execution time is less than a preset time threshold, or the query result set size is greater than a preset size, or the index hit rate is less than a preset hit rate, the query statement is determined to be the target query statement.

[0086] In this embodiment, if the query execution time is less than a preset time threshold, or the size of the query result set is greater than a preset size, or the index hit rate is less than a preset hit rate, it means that the corresponding query statement is a slow query SQL with poor performance. Therefore, it is identified as the target query statement, and an indexing scheme is determined to improve the subsequent query performance of the target query statement.

[0087] S102, extract the column query features corresponding to each data column from the query log.

[0088] Column query features are used to characterize the execution status of the corresponding data column. For example, the frequency of the data column in the query process, the query time of the SQL statement based on a certain data column, the data distribution of the query results of the SQL statement based on a certain data column, the size of the query result set of the SQL statement based on a certain data column, and so on.

[0089] In this embodiment of the application, the query log records the detailed execution status of the target query statement. Therefore, the column query features corresponding to each data column can be extracted from the query log.

[0090] S103, based on the column query features corresponding to each of the data columns and the preset filtering rules, determine the target data column among the multiple data columns.

[0091] Filtering rules are used to filter out data columns (i.e., target data columns) in the target query statement that may cause problems such as excessively long execution time or low performance. There can be one or more filtering rules. Each filtering rule includes the constraints corresponding to each column query feature. The constraints can be specific restrictions on the corresponding column query features, or they can be no restrictions on the corresponding column query features.

[0092] For example, filtering rule 1 is: the data column appears frequently in the query (i.e., the number of occurrences is greater than a preset threshold), and the data distribution of the query results is uneven. This only includes specific restrictions on the frequency of occurrence of the data column in the query process and the corresponding data distribution of the query results, without restrictions on other column query characteristics; filtering rule 2 is: the data column is a primary key column. This only includes restrictions on the data column type; filtering rule 3 is: the data column is a query condition. This also only includes restrictions on the data column type, and so on.

[0093] In this embodiment of the application, for each data column, if it satisfies any one of the filtering rules, it is determined to be the target data column.

[0094] Specifically, determining the target data column among multiple data columns based on the column query features corresponding to each data column and preset filtering rules may include the following steps:

[0095] Step A1: For each data column, determine whether the query features of each column corresponding to the data column meet the corresponding constraints.

[0096] Step A2: If the query features of each column corresponding to the data column meet the corresponding constraints, the data column is determined to be the target data column.

[0097] For example, if data column 'a' appears frequently in the query and the data distribution in the query results is uneven, then it meets the filtering rule 1 in the example above, and therefore, this data column is determined to be the target data column; if data column 'b' is the primary key column, then it meets the filtering rule 2 in the example above, and therefore, this data column is determined to be the target data column; if data column 'c' is a query condition, then it meets the filtering rule 3 in the example above, and therefore, this data column is determined to be the target data column, and so on.

[0098] S104, create multiple candidate indexes based on the target data column.

[0099] In this embodiment of the application, there are generally multiple target data columns. After filtering out multiple target data columns in the target query statement that may cause problems such as excessively long execution time and low performance, multiple candidate indexes can be created based on multiple target data columns.

[0100] Candidate indexes can be either individual indexes created for a single target data column or composite indexes created for multiple target data columns.

[0101] S105, input the multiple candidate indices into the preset index recommendation algorithm so that the preset index recommendation algorithm outputs the corresponding index scheme.

[0102] A pre-defined index recommendation algorithm is used to generate multiple candidate index schemes based on multiple candidate indexes, and then select the better scheme from the multiple candidate index schemes as the recommended index scheme output. Each candidate index scheme can contain one or more candidate indexes.

[0103] In practice, the pre-defined index recommendation algorithm can be a reinforcement learning algorithm, such as A2C (Advantage Actor-Critic). Compared with other reinforcement learning algorithms, the A2C algorithm has advantages such as high parameter utilization, high sample efficiency, and strong stability. By parallelizing execution and utilizing GPUs to accelerate the training process, the A2C algorithm performs excellently in tasks involving continuous action spaces.

[0104] Specifically, the A2C algorithm can be implemented using neural networks. The A2C algorithm includes an Actor network and a Critic network. The Actor network is used to generate candidate index schemes, and the Critic network is used to estimate the quality of the generated candidate index schemes. The best one or more index schemes are then output and recommended to the user.

[0105] In applications, after the preset index recommendation algorithm outputs the index scheme, the index scheme can be created in the corresponding database. The target query statement can be executed using the index scheme, thereby improving the execution efficiency of slow query SQL (i.e. the target query statement), reducing database query time and resource consumption, and improving system performance and response speed.

[0106] Furthermore, after creating an index scheme, its usage can be determined at preset intervals to promptly identify problems with improper index scheme usage. For example, if the performance of executing the corresponding SQL using the index scheme is poor (i.e., the query execution time is less than a preset time threshold, or the query result set size is greater than a preset size, or the index hit rate is less than a preset hit rate, etc.), then the index scheme can be reconstructed using the schemes in S101-S105 of this application to adjust and optimize the existing index scheme.

[0107] In this embodiment, firstly, the query log and multiple data columns corresponding to the target query statement are obtained. Then, the column query features corresponding to each data column are extracted from the query log. Next, based on the column query features corresponding to each data column and preset filtering rules, the target data column is determined from the multiple data columns, and multiple indexes are created based on the target data column. Finally, the multiple indexes are input into a preset index recommendation algorithm, which outputs the corresponding index scheme. This achieves intelligent index recommendation, thereby improving the efficiency of the recommended index scheme and saving labor costs.

[0108] See Figure 2 This is a flowchart illustrating an embodiment of another indexing scheme recommendation method provided in this application. Figure 2 As shown, the process may include the following steps:

[0109] S201, extract the first execution data corresponding to the target query statement from the query log.

[0110] S202, for each index scheme, execute the target query statement using the index scheme to obtain the corresponding second execution data.

[0111] The following provides a unified explanation of S201 and S202:

[0112] The first set of execution data is used to characterize the current execution status of the target query statement. Examples include the execution time of the target query statement and the size of the query result set.

[0113] The second execution data, corresponding to the first execution data, is used to characterize the execution status of the target query statement using the corresponding index scheme. For example, the execution time and size of the result set obtained when executing the target query statement using index scheme a, the execution time and size of the result set obtained when executing the target query statement using index scheme b, and so on.

[0114] S203, based on the first execution data, the second execution data, and the resource usage data corresponding to the indexing scheme, determine the evaluation score of the indexing scheme.

[0115] In this embodiment of the application, the first execution data includes a plurality of first sub-data, and the second execution data includes second sub-data corresponding to each of the first sub-data.

[0116] Based on this, S203 may specifically include the following steps: determining the indicator weights corresponding to each first sub-data and the resource occupancy data; for each first sub-data, calculating the data difference between the first sub-data and the corresponding second sub-data; and performing a weighted summation operation on all data differences and resource occupancy data based on the corresponding indicator weights to obtain the evaluation score of the indexing scheme.

[0117] The first sub-data includes the execution time of the first query and the size of the first query result set; the second sub-data includes the execution time of the second query and the size of the second query result set; and the resource usage data includes the disk space occupied.

[0118] The calculation process corresponding to the above steps is as follows:

[0119] evaluation=w1*(1-(execution_time_new / execution_time_old))+w2*(1-(result_size_new / result_size_old))+w3*(index_size_new / total_disk_space)

[0120] Where, evaluation is the evaluation score, execution_time_new is the execution time of the second query, execution_time_old is the execution time of the first query, result_size_new is the size of the second query result set, result_size_old is the size of the first query result set, index_size_new represents the disk space occupied by the index scheme, and total_disk_space represents the total disk space. w1, w2, and w3 represent the weights of the corresponding metrics.

[0121] S204. The index scheme with the corresponding evaluation score greater than the preset threshold is determined as the reference index scheme.

[0122] A preset threshold is used to filter out the better indexing scheme (i.e., the reference indexing scheme), which can be set by the user according to their needs.

[0123] S205, the preset index recommendation algorithm is optimized using the reference index scheme.

[0124] The aforementioned preset index recommendation algorithm uses pre-trained parameters to filter the candidate indices input into it, and combines the filtered indices to obtain a candidate index scheme. Therefore, whether the parameters in the preset index recommendation algorithm can accurately filter out suitable indices is very important for generating a candidate index scheme.

[0125] The parameters include: index type (e.g., B+ tree index, hash index), index column (i.e., the data column for which the index is created), index order (i.e., the priority sorting of candidate indexes among all candidate indexes, the priority setting can be set by the user), and so on.

[0126] Based on this, in this embodiment of the application, the specific implementation of S205 may include: determining the filtering parameters corresponding to the reference index scheme, wherein the filtering parameters are the parameters used by the preset index recommendation algorithm to filter and obtain the reference index scheme, and optimizing the preset index recommendation algorithm based on the filtering parameters.

[0127] The filtering parameters refer to the parameters used in the preset index recommendation algorithm to select each index in the reference index scheme. Since the reference index scheme is a calculated optimal scheme, its corresponding filtering parameters are also optimal parameters.

[0128] Therefore, in this embodiment, the preset index recommendation algorithm is optimized by referring to the filtering parameters corresponding to the index scheme. That is, the parameters in the preset index recommendation algorithm that are the same as the filtering parameters are retained, and the parameters that are different from the filtering parameters are removed.

[0129] For example, in a preset index recommendation algorithm, the parameters for index type are set to B+ tree index and hash index. This means that during the selection of candidate indexes, both B+ tree and hash indexes can be used as indexes in subsequent combinations to obtain candidate index schemes. If the selection parameter is determined to be a hash index, the preset index recommendation algorithm can be optimized by retaining the hash indexes and deleting the B+ tree indexes. In this case, during the subsequent execution of the preset index recommendation algorithm, only candidate indexes of type hash index will be selected as indexes for subsequent combinations to obtain candidate index schemes.

[0130] In this embodiment, a better reference index scheme can be selected from multiple index schemes by using the first execution data (i.e., the original execution status) corresponding to the target query statement and the second execution data obtained by executing the target query statement using each index scheme. The preset index recommendation algorithm is then optimized by using the filtering parameters corresponding to the reference index scheme. This improves the effect of the subsequent preset index recommendation algorithm in filtering indexes, thereby optimizing the output effect of the preset index recommendation algorithm.

[0131] Based on the same technical concept, embodiments of this application also provide an indexing scheme recommendation device, such as... Figure 3 As shown, the device includes:

[0132] The acquisition module 301 is used to acquire the query log and multiple data columns corresponding to the target query statement. The data columns include the result column to be queried by the target query statement and the condition column as the query condition.

[0133] Extraction module 302 is used to extract column query features corresponding to each data column from the query log;

[0134] The determining module 303 is used to determine the target data column among multiple data columns based on the column query features corresponding to each data column and preset filtering rules;

[0135] Module 304 is used to create multiple candidate indexes based on the target data column;

[0136] The input module 305 is used to input multiple candidate indices into a preset index recommendation algorithm so that the preset index recommendation algorithm can output the corresponding index scheme.

[0137] In one possible implementation, the device further includes an optimization module for:

[0138] Extract the first execution data corresponding to the target query statement from the query log;

[0139] For each index scheme, the target query statement is executed using the index scheme to obtain the corresponding second execution data;

[0140] Based on the first execution data, the second execution data, and the resource consumption data corresponding to the indexing scheme, the evaluation score of the indexing scheme is determined;

[0141] Index schemes whose corresponding evaluation scores are greater than a preset threshold are identified as reference index schemes;

[0142] The preset index recommendation algorithm is optimized using the aforementioned reference index scheme.

[0143] In one possible implementation, the first execution data includes a plurality of first sub-data, and the second execution data includes second sub-data corresponding to each of the first sub-data. The optimization module is further configured to:

[0144] Determine the indicator weights corresponding to each of the first sub-data points and the resource usage data;

[0145] For each first sub-data point, calculate the data difference between the first sub-data point and the corresponding second sub-data point;

[0146] Based on the corresponding indicator weights, a weighted summation operation is performed on all data differences and resource usage data to obtain the evaluation score of the indexing scheme.

[0147] In one possible implementation, the first sub-data includes the first query execution time and the size of the first query result set, the second sub-data includes the second query execution time and the size of the second query result set, and the resource usage data includes disk space usage.

[0148] In one possible implementation, the optimization module is further configured to:

[0149] Determine the filtering parameters corresponding to the reference index scheme, wherein the filtering parameters are the parameters used by the preset index recommendation algorithm to filter and obtain the reference index scheme;

[0150] The preset index recommendation algorithm is optimized based on the filtering parameters.

[0151] In one possible implementation, the filtering rules include constraints corresponding to each column query feature, and the determining module is specifically used for:

[0152] For each data column, determine whether the query features of each column corresponding to the data column meet the corresponding constraints.

[0153] If each column query feature of the data column meets the corresponding constraint conditions, the data column is determined to be the target data column.

[0154] In one possible implementation, the apparatus further includes a statement determination module for:

[0155] Get at least one query statement;

[0156] Determine the query execution data corresponding to each query statement, wherein the query execution data includes query execution time, query result set size, and index hit rate;

[0157] If the query execution time is less than a preset time threshold, or the size of the query result set is greater than a preset size, or the index hit rate is less than a preset hit rate, then the query statement is determined to be the target query statement.

[0158] In this embodiment, firstly, the query log and multiple data columns corresponding to the target query statement are obtained. Then, the column query features corresponding to each data column are extracted from the query log. Next, based on the column query features corresponding to each data column and preset filtering rules, the target data column is determined from the multiple data columns, and multiple indexes are created based on the target data column. Finally, the multiple indexes are input into a preset index recommendation algorithm, which outputs the corresponding index scheme. This achieves intelligent index recommendation, thereby improving the efficiency of the recommended index scheme and saving labor costs.

[0159] Based on the same technical concept, embodiments of this application also provide an electronic device, such as... Figure 4 As shown, it includes a processor 111, a communication interface 112, a memory 113, and a communication bus 114, wherein the processor 111, the communication interface 112, and the memory 113 communicate with each other through the communication bus 114.

[0160] Memory 113 is used to store computer programs;

[0161] When processor 111 executes a program stored in memory 113, it performs the following steps:

[0162] Obtain the query log and multiple data columns corresponding to the target query statement. The data columns include the result column to be queried by the target query statement and the condition column as the query condition.

[0163] Extract the column query features corresponding to each data column from the query log;

[0164] Based on the column query features corresponding to each of the data columns and the preset filtering rules, the target data column is determined among the multiple data columns;

[0165] Create multiple candidate indexes based on the target data column;

[0166] Multiple candidate indices are input into a preset index recommendation algorithm, which then outputs a corresponding index scheme.

[0167] The communication bus mentioned in the above electronic devices can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. This communication bus can be divided into address bus, data bus, control bus, etc. For ease of illustration, only one thick line is used to represent it in the diagram, but this does not indicate that there is only one bus or one type of bus.

[0168] The communication interface is used for communication between the aforementioned electronic devices and other devices.

[0169] The memory may include random access memory (RAM) or non-volatile memory (NVM), such as at least one disk storage device. Optionally, the memory may also be at least one storage device located remotely from the aforementioned processor.

[0170] The processors mentioned above can be general-purpose processors, including central processing units (CPUs), network processors (NPs), etc.; they can also be digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components.

[0171] In another embodiment provided in this application, a computer-readable storage medium is also provided, which stores a computer program that, when executed by a processor, implements the steps of any of the above-described indexing scheme recommendation methods.

[0172] In another embodiment provided in this application, a computer program product containing instructions is also provided, which, when run on a computer, causes the computer to execute any of the indexing scheme recommendation methods described above.

[0173] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs.

[0174] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented using software plus a general-purpose hardware platform, or of course, using hardware. Based on this understanding, the above technical solutions, in essence or the parts that contribute to the related technology, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0175] It should be understood that the terminology used herein is for the purpose of describing particular exemplary embodiments only and is not intended to be limiting. Unless the context clearly indicates otherwise, the singular forms “a,” “an,” and “described” as used herein may also include the plural forms. The terms “comprising,” “including,” “containing,” and “having” are inclusive and therefore indicate the presence of the stated features, steps, operations, elements, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, elements, components, and / or combinations thereof. The method steps, processes, and operations described herein are not construed as requiring them to be performed in a particular order described or illustrated unless the order of performance is explicitly indicated. It should also be understood that additional or alternative steps may be used.

[0176] The above description is merely a specific embodiment of the present invention, enabling those skilled in the art to understand or implement the invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the invention. Therefore, the present invention is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features claimed herein.

Claims

1. A method for recommending an indexing scheme, characterized in that, The method includes: Obtain the query log and multiple data columns corresponding to the target query statement. The data columns include the result column to be queried by the target query statement and the condition column as the query condition. Extract the column query features corresponding to each data column from the query log; Based on the column query features corresponding to each of the data columns and the preset filtering rules, the target data column is determined among the multiple data columns; Create multiple candidate indexes based on the target data column; Multiple candidate indices are input into a preset index recommendation algorithm, which then outputs a corresponding index scheme. The method further includes, after inputting multiple candidate indices into a preset index recommendation algorithm so that the preset index recommendation algorithm outputs a corresponding index scheme: Extract the first execution data corresponding to the target query statement from the query log; For each index scheme, the target query statement is executed using the index scheme to obtain the corresponding second execution data; Based on the first execution data, the second execution data, and the resource consumption data corresponding to the indexing scheme, the evaluation score of the indexing scheme is determined; Index schemes whose corresponding evaluation scores are greater than a preset threshold are identified as reference index schemes; The preset index recommendation algorithm is optimized using the aforementioned reference index scheme.

2. The method according to claim 1, characterized in that, The first execution data contains multiple first sub-data, and the second execution data contains second sub-data corresponding to each of the first sub-data. Determining the evaluation score of the indexing scheme based on the first execution data, the second execution data, and the resource consumption data corresponding to the indexing scheme includes: Determine the indicator weights corresponding to each of the first sub-data points and the resource usage data; For each first sub-data point, calculate the data difference between the first sub-data point and the corresponding second sub-data point; Based on the corresponding indicator weights, a weighted summation operation is performed on all data differences and resource usage data to obtain the evaluation score of the indexing scheme.

3. The method according to claim 2, characterized in that, The first sub-data includes the first query execution time and the size of the first query result set; the second sub-data includes the second query execution time and the size of the second query result set; and the resource usage data includes disk space usage.

4. The method according to claim 1, characterized in that, The optimization of the preset index recommendation algorithm using the reference index scheme includes: Determine the filtering parameters corresponding to the reference index scheme, wherein the filtering parameters are the parameters used by the preset index recommendation algorithm to filter and obtain the reference index scheme; The preset index recommendation algorithm is optimized based on the filtering parameters.

5. The method according to claim 1, characterized in that, The filtering rules include restrictions corresponding to each column query feature. The process of determining the target data column from multiple data columns based on the column query features corresponding to each data column and the preset filtering rules includes: For each data column, determine whether the query features of each column corresponding to the data column meet the corresponding constraints. If each column query feature of the data column meets the corresponding constraint conditions, the data column is determined to be the target data column.

6. The method according to claim 1, characterized in that, The method further includes: Get at least one query statement; Determine the query execution data corresponding to each query statement, wherein the query execution data includes query execution time, query result set size, and index hit rate; If the query execution time is less than a preset time threshold, or the size of the query result set is greater than a preset size, or the index hit rate is less than a preset hit rate, then the query statement is determined to be the target query statement.

7. An indexing scheme recommendation device, characterized in that, The device includes: The acquisition module is used to acquire the query log and multiple data columns corresponding to the target query statement. The data columns include the result column to be queried by the target query statement and the condition column as the query condition. The extraction module is used to extract the column query features corresponding to each data column from the query log; The determination module is used to determine the target data column among multiple data columns based on the column query features corresponding to each data column and preset filtering rules; A creation module is used to create multiple candidate indexes based on the target data column; The input module is used to input multiple candidate indices into a preset index recommendation algorithm so that the preset index recommendation algorithm can output a corresponding index scheme. The device further includes an optimization module for: Extract the first execution data corresponding to the target query statement from the query log; For each index scheme, the target query statement is executed using the index scheme to obtain the corresponding second execution data; Based on the first execution data, the second execution data, and the resource consumption data corresponding to the indexing scheme, the evaluation score of the indexing scheme is determined; Index schemes whose corresponding evaluation scores are greater than a preset threshold are identified as reference index schemes; The preset index recommendation algorithm is optimized using the aforementioned reference index scheme.

8. An electronic device, characterized in that, It includes a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other through the communication bus; Memory, used to store computer programs; A processor, when executing a program stored in memory, implements the steps of the method described in any one of claims 1-6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, implements the steps of the method described in any one of claims 1-6.

Images