CN118132566B - Database index optimization method - Google Patents

Abstract

The invention discloses a database index optimization method, which comprises the steps of obtaining operation data of a database; determining an index to be processed based on the operation data; generating a preliminary optimization scheme of the index to be processed; executing the preliminary optimization scheme of the index to be processed, and calculating the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed; and determining a final optimization result of the index to be processed according to the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed.

Description

Database index optimization method

Technical Field

The invention relates to the technical field of databases, in particular to a database index optimization method.

Background

The indexes of the database system are effective means for improving the performance and efficiency of the database, the query efficiency of the database is lower due to the fact that no indexes and too many indexes are available, and the maintenance cost is increased.

Disclosure of Invention

In order to solve the existing technical problems, the embodiment of the invention provides a database index optimization method and electronic equipment, which can enable the optimization of indexes to be more accurate and intelligent, thereby improving the stability and reliability of a database.

In a first aspect, a database index optimization method is provided, including: acquiring operation data of a database;

Determining an index to be processed based on the operation data; generating a preliminary optimization scheme of the index to be processed; executing the preliminary optimization scheme of the index to be processed, and calculating the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed; and determining a final optimization result of the index to be processed according to the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed.

According to the embodiment of the application, the operation data in the operation process of the database is obtained, the index to be processed is determined according to the operation data, the preliminary optimization scheme of the index to be processed is generated, the preliminary optimization scheme of the index to be processed is executed, the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed is calculated according to a plurality of performance indexes in the operation data, so that the contribution degree of the preliminary optimization scheme of the index to be processed to the overall performance of the database can be measured by the current comprehensive evaluation value, the final optimization result of the index to be processed is determined according to the current comprehensive evaluation value, the optimization of the index is more accurate and intelligent, and the stability and reliability of the database are improved.

Drawings

FIG. 1 is a diagram of an application environment for a database index optimization method in one embodiment;

FIG. 2 is a flow chart of a database index optimization method in one embodiment;

FIG. 3 is a flowchart of calculating a current comprehensive evaluation value in a database index optimization method according to an embodiment;

FIG. 4 is a schematic diagram of a database index optimizing apparatus according to an embodiment;

fig. 5 is a schematic diagram of an electronic device in an embodiment.

Detailed Description

The technical scheme of the invention is further elaborated below by referring to the drawings in the specification and the specific embodiments.

Unless defined otherwise, 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 invention belongs. The terminology used herein in the description of the invention is for the purpose of describing particular embodiments only and is not intended to be limiting of the scope of the invention. The term "and/or" as used herein includes any and all combinations of one or more of the associated listed items.

In the following description, reference is made to the expression "some embodiments" which describe a subset of all possible embodiments, but it should be understood that "some embodiments" may be the same subset or a different subset of all possible embodiments and may be combined with each other without conflict.

Referring to fig. 1, an application environment diagram of a database automatic optimization method in an embodiment is shown. The application environment map may include a business system 100, a database 200, and a database index optimization system 300. Wherein the business system 100 is in data communication with the database 200, the business system 100 is a system for user use, the database 200 is a database based on a relational database model, and data support is provided for the business system 100. The business system 100 provides various functions to users based on the data provided by the database 200. The business system 100 requests data from the database 200 based on the user's functional usage. Database 200 queries data related to the request according to the request and using a database query language. The database index optimization system 300 is in data communication with the database 200, and the database index optimization system 300 automatically optimizes the index in the database 200 by the database index optimization method in the embodiment of the application so as to improve the timeliness and accuracy of the data query of the service system 100. Database 200 may be an oracle database, db2 block, SQL SERVER database, mySQL, or the like. Business system 100, database index optimization system 300 may refer to software, applets, and the like that are logically independent of each other. The service system 100, the database 200 and the database index optimization system 300 may be located in the same electronic device at the same time or partially, or may be located in different electronic devices respectively, so as to ensure that the service system 100, the database 200 and the database index optimization system 300 can perform data communication. In some embodiments, database index optimization system 300 may be located in database 200 as an automated optimization tool for database 200.

Referring to fig. 2, a flowchart of a database index optimization method according to an embodiment of the application is shown. The database index optimization method is applied to the electronic equipment and comprises the following steps:

s11, acquiring operation data of a database.

In this embodiment, the operation data indicates data generated when the database executes the query of the query statement, and log records and query records are generated in the operation process, so that the log records and query records of the database can be scanned regularly, thereby obtaining the operation data of the database. In some embodiments, the timed scan database may be set while the database is in an idle state, or the user manually triggers the scan database.

In some embodiments, the operational data includes, but is not limited to: the method comprises the steps of resource occupation data, index statistical information and operation index data generated in the execution process of query sentences. The resource occupation data indicates performance data of the query statement loaded in the execution process, including but not limited to processor speed, IO retrieval time, IO transmission speed, maximum IO throughput, IO times, memory occupation rate, cache hit rate, processor occupation rate and the like. Where index statistics indicate the use of terms from the index by the query, optimizing the index may be used to optimize the query execution plan and improve query performance, index statistics include, but are not limited to: index query times, index hit rate, etc. The operation index data is used for indicating data generated by executing the query statement and data measuring operation indexes executed by the query statement, and the operation indexes include but are not limited to: resolving call times, disk reading times, cache reading times, processing line numbers, execution times, processing operation time, operation time of query sentences, average resolving time, sequencing times, shared memory, physical reading request times, physical reading byte numbers, physical writing requests, physical writing byte numbers and the like.

S12, determining a pending index based on the operation data.

In this embodiment, when query operation is performed in the database based on the query statement, corresponding resource occupation data, index statistics information, and operation index data generated in the execution process of the query statement are generated for the query statement, so that when the query statement is different, the operation data corresponding to the query statement is also different. Therefore, the query statement with more occupied resources can be found out from the viewpoint of resource utilization, the index to be processed is determined according to the query statement with more occupied resources, the query statement to be optimized can be found out from the viewpoint of operation index data of the query statement, and then the index to be processed is determined according to the query statement to be optimized.

In some embodiments, the index to be processed may be an existing index in the database, or may be a new index generated from the target query statement. The indexes to be processed comprise outdated indexes, pre-established indexes and indexes to be adjusted. Wherein the pre-established index indicates that the index was not previously in the database, and determining the index to be pre-established based on the operational data. The outdated index indicates an index that is not used for a long time or an index of which index utilization is not high, i.e., an index that evaluates whether it needs to be deleted. The index to be adjusted indicates an index existing in the database, but by utilizing the structure of the index, one or more items of operation data corresponding to the query statement do not reach the standard.

When the index to be processed is determined, a target query statement meeting the conditions can be screened out from the perspective of the resource occupation data, and then the index to be processed is determined based on the target query statement. The operation index data of the query statement can be directly analyzed, the target query statement meeting the conditions is screened out, and the index to be processed is determined based on the target query statement.

S13, generating a preliminary optimization scheme of the index to be processed.

In the present embodiment, the preliminary optimization scheme indicates a preliminary scheme that performs optimization on the index to be processed. The preliminary optimization scheme is not necessarily executed, and it is necessary to evaluate whether the preliminary optimization scheme can contribute to the overall performance of the database, that is, evaluate the contribution degree of the preliminary optimization scheme to the overall performance.

In some embodiments, the preliminary optimization schemes corresponding to different types of indexes to be processed are different, for example, for outdated indexes, the preliminary optimization scheme is to delete outdated indexes; for the pre-established index, the preliminary optimization scheme is to add the pre-established index into the database; for the index to be adjusted, the preliminary optimization scheme is to generate an optimization index corresponding to the index to be adjusted, and the optimization index is added into the database.

S14, executing the preliminary optimization scheme of the index to be processed, and calculating the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed.

In this embodiment, when executing the preliminary optimization scheme of the index to be processed, a test query statement is used to test the preliminary optimization scheme of the index to be processed, after the preliminary optimization scheme of the index to be processed is executed, the current operation data is obtained after the preliminary optimization scheme of the index to be processed is executed and the test query statement is used to operate in the database, a plurality of performance index data are obtained based on the current operation data, and whether the preliminary optimization scheme needs to be executed is comprehensively evaluated based on the plurality of performance index data. Therefore, the current comprehensive evaluation value indicates the performance comprehensive evaluation value calculated after the database is operated by adopting the test query statement after the preliminary optimization scheme of the index to be processed is executed. Wherein the performance metrics include, but are not limited to, a combination of one or more of the following: processor speed, IO retrieval time, IO transmission speed, maximum IO throughput, IO number, memory occupancy, cache hit rate, processor occupancy, index query number, index hit rate, resolution call number, disk read number, cache read number, processing number of lines, execution number, processing operation time, operation time of query statement, average resolution time, sequencing number, shared memory, physical read request number, physical read byte number, physical write request, physical write byte number, and the like.

And S15, determining a final optimization result of the index to be processed according to the comprehensive evaluation value of the preliminary optimization scheme of the index to be processed.

In this embodiment, the current comprehensive evaluation value is calculated according to a plurality of performance indexes, so that the current comprehensive evaluation value can measure the contribution degree of the preliminary optimization scheme of the index to be processed to the overall performance of the database after the preliminary optimization scheme is executed. If the contribution degree to the overall performance of the database is not high, a preliminary optimization scheme of the index to be processed can not be executed; and if the contribution degree of the overall performance of the database is higher, executing a preliminary optimization scheme of the index to be processed. Therefore, the final optimization result of the index to be processed is determined according to the contribution degree of the preliminary optimization scheme of the index to be processed to the overall performance of the database.

In the above embodiment, operation data in the operation process of the database is obtained, a to-be-processed index is determined according to the operation data, a preliminary optimization scheme of the to-be-processed index is generated, the preliminary optimization scheme of the to-be-processed index is executed, and a current comprehensive evaluation value of the preliminary optimization scheme of the to-be-processed index is calculated, wherein the current comprehensive evaluation value is calculated according to a plurality of performance indexes in the operation data, so that the contribution degree of the preliminary optimization scheme of the to-be-processed index to the overall performance of the database can be measured by the current comprehensive evaluation value, and a final optimization result of the to-be-processed index is determined according to the current comprehensive evaluation value, so that optimization of the index is more accurate and intelligent, and stability and reliability of the database are improved.

In some embodiments, the operational data includes at least one of: the method comprises the steps of determining operation index data generated in the execution process of resource occupation data, index statistical information and query sentences, wherein the index to be processed comprises an outdated index, a pre-established index and an index to be adjusted, and determining the index to be processed comprises one or more of the following combinations based on the operation data:

determining a target query statement meeting optimization conditions according to the operation data, acquiring an existing index associated with the target query statement from the database, and determining the existing index associated with the target query statement as a pending index;

Determining a target query statement meeting optimization conditions according to the operation data, generating a pre-established index based on the target query statement, and determining the pre-established index as a to-be-processed index, wherein the target query statement meeting optimization conditions comprises at least one of the following: query sentences with the resource occupation data larger than the resource occupation threshold and query sentences with the operation index data larger than the operation index threshold;

And determining indexes to be optimized which meet the conditions according to the index statistical information, and determining the indexes to be optimized which meet the conditions as indexes to be processed.

In this embodiment, the index to be processed may be an existing index in the database, or may be a pre-established index. The existing index and the index to be optimized associated with the target query statement are indexes existing in the database, and the pre-established index is an index which is not generated in the database according to the target query statement. When the index to be processed is determined, a target query statement with more occupied resources can be found out from the angle of resource utilization, the query statement with the occupied resources data larger than the occupied resources threshold value is determined as the target query statement, and then the index used by the target query statement in the process of executing query is used as the existing index associated with the target query statement, so that the target index which affects the performance can be more accurate from the point of resource occupied performance index. For example, a query statement with a CPU occupancy rate greater than a preset CPU occupancy value is determined as a target query statement, and then an index used by the target query statement is determined as a pending index. The target query statement can be found out from the view of the operation index data of the query statement, the query statement with the operation index data larger than the operation index threshold value is determined to be the target query statement, and then the index used by the target query statement is determined to be the index to be processed. The index to be optimized is found out from the operation index data of the query statement, so that the query efficiency of the query statement can be improved in a targeted manner.

After determining the target query statement meeting the optimization condition according to the operation data, the associated columns of the target query statement can be analyzed, target columns with the query frequency larger than a preset query value are screened from the associated columns, and when the target columns have no corresponding indexes in the existing indexes, a pre-established index is generated based on column fields of the target columns. When the database is not indexed, the index structure can be gradually built by using the database index optimization method provided by the embodiment, so that query service is provided for the business system. The pre-established index is a new index which is established based on the target query statement and is more targeted, so that index optimization is more efficient.

The index statistics indicate the use of the query statement from the index, or the index to be optimized may be determined directly from the index statistics.

In the above embodiment, the target query statement meeting the conditions can be searched from the angles of the performance index occupied by the resources and the operation index data of the query statement, the index to be processed is determined according to the target query statement, and the index to be optimized appears in the indexes, so that the target index to be optimized can be more accurately influenced from the performance index occupied by the resources, the index to be optimized can be found from the operation index data of the query statement, thereby the query efficiency of the query statement can be improved in a targeted manner, and a new index can be pre-established, and is established based on the target query statement, so that the index optimization is more targeted, and the efficiency is improved.

In some embodiments, the determining the pending index based on the operational data comprises:

When the index to be processed is the associated existing index and/or the index to be optimized meeting the condition, acquiring index statistical information of the index to be processed, determining the index to be processed meeting the condition of the outdated index as the outdated index, and determining the index to be processed meeting the condition of adjusting the existing index structure as the index to be adjusted, wherein the condition of meeting the outdated index comprises at least one of the following: the number of inquiry times is smaller than the preset number of inquiry times, the hit rate is smaller than the preset hit rate, and the current use time of the distance between the latest use time is longer than the preset duration; compliance with existing index structure adjustment conditions includes at least one of: the number of queries is greater than or equal to the preset number of queries and the hit rate is greater than or equal to the preset hit rate.

In this embodiment, the existing indexes in the database may be further classified into outdated indexes and indexes to be adjusted according to the index. In the existing indexes, the outdated indexes indicate indexes with unused time longer than preset time or indexes with inquiry times smaller than preset inquiry times and hit rate smaller than preset hit rate, and the outdated indexes evaluate whether the indexes need to be deleted or not. The index to be adjusted indicates an index existing in the database, but by utilizing the structure of the index, one or more items of operation data corresponding to the query statement do not reach the standard. The index to be adjusted indicates that query sentences in the database can be used frequently, but when the query sentences are operated, one or more items of resource occupation data of the database are higher than a resource occupation threshold or operation index data corresponding to the query sentences are larger than an operation index threshold, the index to be adjusted needs to be optimized, so that the overall performance is improved.

The existing indexes of the database are further divided into outdated indexes and indexes to be adjusted, so that the indexes can be optimized in a targeted mode, the efficiency of index optimization is improved, and the stability and reliability of the database are improved.

In some embodiments, the generating the preliminary optimization scheme for the index to be processed includes at least one of:

For the outdated index, generating a preliminary optimization scheme for deleting the outdated index; and for the index to be adjusted, acquiring a plurality of columns associated with the index to be adjusted, determining an index field based on the columns, and generating an optimized index corresponding to the index to be adjusted based on the index field.

For the outdated index, the preliminary optimization scheme is to delete the outdated index, and for the index to be adjusted, generate an optimized index for the index to be adjusted, and then test and evaluate the optimized index by using a test query statement, so as to determine the final optimized result of the index to be adjusted.

In the embodiment, the existing indexes are respectively optimized by classification, and different preliminary optimization schemes are correspondingly generated, so that the indexes can be optimized in a targeted manner, the efficiency of index optimization is improved, and the stability and reliability of the database are improved.

In some embodiments, the calculating the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed includes:

acquiring current performance index data after executing the preliminary optimization scheme of the index to be processed;

And inputting each piece of current performance index data into a trained index evaluation model, outputting a comprehensive evaluation value through the trained index evaluation model, and taking the comprehensive evaluation value as the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed.

Optionally, the method further comprises:

Acquiring a training data set, wherein each training sample in the training data set comprises sample data and label data corresponding to the sample data, the sample data comprises various performance index data after a sample query statement is operated, and the label data comprises a comprehensive evaluation value after the sample query statement is operated;

Constructing an initial index evaluation model;

And carrying out iterative training on the initial index evaluation model through the training data set, acquiring training samples from the training data set to form input sample data, outputting output data corresponding to the input sample data based on the index evaluation model in the iterative process, and calculating a loss value in each iterative process based on a loss function, label data corresponding to the input sample data and the output data corresponding to the input sample data until an iteration termination condition is reached, so as to obtain the trained index evaluation model.

As shown in fig. 3, fig. 3 is a flowchart of calculating a current comprehensive evaluation value in the database index optimization method according to an embodiment, which is specifically as follows:

S31, acquiring each piece of current performance index data after executing the preliminary optimization scheme of the index to be processed.

In this embodiment, for the preliminary optimization scheme of the same index to be processed, the same test query statement is adopted to perform test evaluation, after the preliminary optimization scheme of the index to be processed is executed, the test query statement is used to run in the database, and each current performance index data after the running is obtained. For example, if the index to be processed is an outdated index, the outdated index is set to be in an invisible state, so that the outdated index cannot be used by the test query statement, and then each piece of current performance index data after the test query statement is operated is obtained. And for the index to be adjusted or the pre-established index, adding the optimized index or the pre-established index corresponding to the index to be adjusted into an index structure of the database, setting the optimized index or the pre-established index corresponding to the index to be adjusted into a visible state, calling the optimized index or the pre-established index corresponding to the index to be adjusted by the test query statement, and then acquiring all the current performance index data after operation.

S32, inputting the current performance index data into a trained index evaluation model, outputting a comprehensive evaluation value through the trained index evaluation model, and taking the comprehensive evaluation value as the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed.

In this embodiment, the index evaluation model may be a learning model with supervised learning, and the index evaluation model is trained by training samples in a training data set, where each training sample in the training data set includes sample data and tag data corresponding to the sample data, the sample data includes performance index data after a sample query statement is run, and the tag data includes a comprehensive evaluation value after a sample query statement is run. The comprehensive evaluation value in the tag data can reflect the influence of the sample query statement on the overall performance of the data. The index evaluation model can learn the characteristics of each performance index data by taking the label data as a training purpose through the training data set, so that parameters in the index evaluation model are optimized, and after training is finished, the index evaluation model can accurately evaluate the condition of index optimization according to each performance index data.

And performing supervised training of the index evaluation model by taking the label data corresponding to the sample data as a training target, and optimizing parameters in the index evaluation model. In each iteration process, calculating a loss value between output data corresponding to input sample data and tag data corresponding to the input sample data based on a loss function, judging whether the current iteration meets an iteration termination condition based on the loss value, continuously acquiring the input sample data from a training data set to continue training when the current iteration does not meet the iteration termination condition, updating parameters in an optimized index evaluation model until the iteration termination condition is met, and taking the index evaluation model after stopping the iteration as a trained index evaluation model. Wherein the loss function includes, but is not limited to, a mean square error function, a cross entropy function, and the like. The iteration termination condition includes, but is not limited to, the loss value being less than a preset error value, the number of iterations being greater than a preset number of iterations, etc.

In the above embodiment, the label data corresponding to the sample data is used as a training target, the index evaluation model performs supervised learning in the training process, and learns the characteristics of each evaluation performance index data in the sample data, so that after the training is completed, a plurality of parameters in the index evaluation model are solidified, each current performance index data is directly used as the input of the index evaluation model, and then the index evaluation model can output the comprehensive evaluation value according to each evaluation performance index data, thereby accurately evaluating the influence of the preliminary optimization scheme on the overall performance of the database, facilitating the accurate decision of the index optimization scheme, and improving the efficiency of index optimization.

In some embodiments, the determining the final optimization result of the index to be processed according to the comprehensive evaluation value of the preliminary optimization scheme of the index to be processed includes:

Acquiring a historical comprehensive evaluation value, comparing the historical comprehensive evaluation value with the current comprehensive evaluation value, and determining a final optimization result of the index to be processed;

comparing the historical comprehensive evaluation value with the current comprehensive evaluation value, and determining a final optimization result of the index to be processed comprises:

When the database performance indicated by the current comprehensive evaluation value is better than the database performance indicated by the historical comprehensive evaluation value and the index to be processed is the index to be adjusted or a pre-established index, taking a preliminary optimization scheme of the index to be processed as a final optimization result of the index to be processed;

When the performance of the database indicated by the current comprehensive evaluation value is better than the performance of the database indicated by the historical comprehensive evaluation value, or the error between the performance of the database indicated by the current comprehensive evaluation value and the performance of the database indicated by the historical comprehensive evaluation value is within a preset error range, and the index to be processed is the outdated index, the final optimization result of the index to be processed is that the outdated index is deleted.

For the same index to be processed, the same test query statement and the same database are adopted for evaluation when the current comprehensive evaluation value and the historical comprehensive evaluation value are calculated, so that the influence of the preliminary optimization scheme of the index to be processed on the overall performance of the database can be evaluated based on the current comprehensive evaluation value and the historical comprehensive evaluation value, and whether the preliminary optimization scheme needs to be executed or not is determined. And in the same database, after the same test query statement is adopted to run in the same database without executing the preliminary optimization scheme of the index to be processed, acquiring each historical performance index data, and then inputting each historical performance index data into a trained index evaluation model to output to obtain a historical comprehensive evaluation value.

When the index to be processed is the index to be adjusted or the pre-established index, if the performance of the database indicated by the current comprehensive evaluation value is better than the performance of the database indicated by the historical comprehensive evaluation value, the overall performance of the database is improved after the query statement uses the optimized index corresponding to the index to be adjusted or the pre-established index, and then the final optimized result of the index to be processed is a preliminary optimized scheme for executing the index to be processed; if the performance of the database indicated by the current comprehensive evaluation value is not better than that indicated by the historical comprehensive evaluation value, the optimization index or the pre-established index corresponding to the index to be adjusted does not bring great contribution to the performance of the database, and the optimization index or the pre-established index corresponding to the index to be adjusted is required to be deleted from the database.

When the index to be processed is an outdated index and the database performance indicated by the current comprehensive evaluation value is better than the database performance indicated by the historical comprehensive evaluation value, or the error between the database performance indicated by the current comprehensive evaluation value and the database performance indicated by the historical comprehensive evaluation value is within a preset error range, the condition that the query statement does not use the outdated index in operation does not bring negative influence to the overall performance of the database or is better than the overall performance when the outdated index is used is indicated, and the outdated index can be deleted is indicated; the error between the database performance indicated by the current comprehensive evaluation value and the database indicated by the historical comprehensive evaluation value exceeds a preset error range, and the database performance indicated by the current comprehensive evaluation value is lower than the database performance indicated by the historical comprehensive evaluation value, the fact that the whole performance of the database is negatively affected if the outdated index is deleted is indicated, and therefore the final optimization result is that the outdated index is reserved.

In the above embodiment, by comparing the database overall performance data after the preliminary optimization scheme of the index to be processed is executed in the database with the database overall performance data of the preliminary scheme of the index not to be processed, the influence of the preliminary optimization scheme of the index to be processed on the overall performance of the database is accurately evaluated, so that the optimization of the index is accurately decided, the efficiency of the index optimization is improved, and the stability and reliability of the database are improved.

In some embodiments, the states of the pending index include a visible state indicating that the pending index can be used by a query statement and an invisible state indicating that the pending index cannot be used by a query statement; the index to be processed comprises an outdated index, a pre-established index and an index to be adjusted, and the method further comprises:

when the final optimization result of the index to be processed indicates that the index to be processed needs to be deleted and the current state of the index to be processed is a visible state, updating the current state of the index to be processed into an invisible state;

Updating the current state of the index to be processed into a visible state when the final optimization result of the index to be processed indicates that the index to be processed needs to reserve a pre-established index or an optimization index corresponding to the index to be adjusted and the current state of the index to be processed is an invisible state;

when the current condition triggers a preset cleaning condition, the index to be processed in an invisible state is automatically deleted.

By setting the state of the index to be processed, the evaluation of the index to be processed can not influence the normal operation of the database, and the concurrency and consistency of the database data can be ensured. In the database, index optimization is executed regularly, the time of index optimization is set in the time period when the database is in the idle state, and if index optimization is not completed in the time period of the idle state, the index optimization can be tentatively set for the next index optimization. For the to-be-processed index to be deleted indicated by the final optimization result, the to-be-processed index to be deleted can be set to be in an invisible state, the to-be-processed index in the invisible state is automatically deleted when a preset cleaning condition is triggered under the current condition, for example, when the current time interval reaches a preset time interval, the to-be-processed index in the invisible state is automatically deleted. When the index to be processed is in an invisible state, the index to be processed is not involved in subsequent index evaluation and query sentences, and the index is not utilized in the query process. For the indexes to be processed which are not evaluated, when the database is in a working state, the indexes to be processed which are not evaluated can be set to be in an invisible state, and when the database is in an idle state, the indexes to be processed which are not evaluated are updated to be in the visible state to continue the evaluation.

In the embodiment, by setting the state of the index to be processed, the evaluation of the index to be processed can not influence the normal operation of the database, and the concurrency and consistency of the database data can be ensured, so that the performance overhead and the index maintenance cost can be reduced, and the efficiency of index optimization is improved.

In some embodiments, the method further comprises:

Responding to a state setting operation of a user on a user interface, acquiring a selected index corresponding to the state setting operation, setting the state of the selected index according to the state setting operation, and setting an index state setting item on the user interface, wherein the index state setting item can be used for selecting an index needing to change the state and setting the state of the index.

The user interface is provided with an index state setting item, the index state setting item is provided with an index item selection item and a setting index state item, for example, indexes in the index item selection item comprise various types of indexes to be processed, the indexes to be processed can be classified and displayed, for example, an outdated index classification item, a pre-established index classification item and an index classification item to be adjusted can be classified and displayed, the outdated index to be set can be selected through the outdated index classification item, the pre-established index to be set can be selected through the pre-established index classification item, and the state of the optimized index corresponding to the index to be adjusted to be set can be selected through the index classification item to be adjusted. For example, the outdated index A determines that the final optimization result is deletion of the outdated index A through the method of the embodiment of the application, but maintenance personnel check the comparison index, synthesize the future use condition of the service system and the like, consider that the outdated index A should not be deleted, reset the state of the outdated index A through a user interface, update the invisible state of the outdated index A into the visible state, and thus the subsequent query statement can utilize the outdated index A in the operation process.

In the above embodiment, the user interface may provide the index state setting item to select the index of which the state needs to be changed and the state of which the index is set, so that the index may be flexibly set according to the use condition of the data system and the use condition of the service system, and the utilization rate of index optimization is improved.

In some embodiments, the method further comprises:

generating an optimization suggestion according to the final optimization result of the index to be processed, and displaying the optimization suggestion;

the generating optimization suggestions according to the final optimization result of the index to be processed comprises at least one or more of the following:

classifying and displaying final optimization results corresponding to various indexes to be processed;

For the pre-established index, displaying the comparison data of each load performance index after the index establishment is not executed and the pre-established index is executed, and highlighting the load performance index with the comprehensive evaluation value arranged in the preset front position;

For the index to be adjusted, displaying the comparison data of each load performance index after the index to be adjusted is executed and the optimization index corresponding to the index to be adjusted is executed, and highlighting the load performance index with the comprehensive evaluation value arranged in the preset front position;

And for the outdated index, displaying the comparison data of the outdated index which is not deleted and each load performance index which is deleted, and highlighting the load performance index which affects the level of the load performance index arranged in the preset front position.

The optimization suggestions can be presented to the user in the form of an optimization report, final optimization results corresponding to various indexes to be processed can be displayed in a classified mode in the optimization suggestions, for example, outdated indexes are displayed in a first area, pre-established indexes are displayed in a second area, indexes to be adjusted are displayed in a third area, and therefore the user can intuitively observe the optimization suggestions of various indexes, and rapid maintenance is facilitated. For the pre-established index or the index to be adjusted, the performance index with higher contribution degree in the overall performance index is highlighted, so that maintenance personnel can intuitively decide whether the contribution degree of the pre-established index or the index to be adjusted is effective. For the outdated index, the performance index with higher negative influence degree in the overall performance index is highlighted, so that maintenance personnel can intuitively decide whether the influence degree of the pre-established index or the index to be adjusted is effective or not, and the final optimization result of the outdated index is timely and flexibly set.

Referring to fig. 4, an embodiment of the present application provides a database index optimization apparatus, including: an acquisition module 41, configured to acquire operation data of the database; a determining module 42, configured to determine a pending index based on the operation data; a generating module 43, configured to generate a preliminary optimization scheme of the index to be processed; a calculation module 44, configured to execute a preliminary optimization scheme of the index to be processed, and calculate a current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed; and the optimization module 45 is used for determining a final optimization result of the index to be processed according to the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed.

Optionally, the operation data includes at least one of: the determining module 42 is further configured to:

determining a target query statement meeting optimization conditions according to the operation data, acquiring an existing index associated with the target query statement from the database, and determining the existing index associated with the target query statement as a pending index;

Determining a target query statement meeting optimization conditions according to the operation data, generating a pre-established index based on the target query statement, and determining the pre-established index as a to-be-processed index, wherein the target query statement meeting optimization conditions comprises at least one of the following: query sentences with the resource occupation data larger than the resource occupation threshold and query sentences with the operation index data larger than the operation index threshold;

And determining indexes to be optimized which meet the conditions according to the index statistical information, and determining the indexes to be optimized which meet the conditions as indexes to be processed.

Optionally, the indexes to be processed include outdated indexes, pre-established indexes, indexes to be adjusted, and the determining module 42 is further configured to include:

When the index to be processed is the associated existing index and/or the index to be optimized meeting the condition, acquiring index statistical information of the index to be processed, determining the index to be processed meeting the condition of the outdated index as the outdated index, and determining the index to be processed meeting the condition of adjusting the existing index structure as the index to be adjusted, wherein the condition of meeting the outdated index comprises at least one of the following: the number of inquiry times is smaller than the preset number of inquiry times, the hit rate is smaller than the preset hit rate, and the current use time of the distance between the latest use time is longer than the preset duration; compliance with existing index structure adjustment conditions includes at least one of: the number of queries is greater than or equal to the preset number of queries and the hit rate is greater than or equal to the preset hit rate.

Optionally, the generating module 43 is further configured to:

Generating an optimization scheme for deleting the outdated index for the outdated index; and for the index to be adjusted, acquiring a plurality of columns associated with the index to be adjusted, determining an index field based on the columns, and generating an optimized index corresponding to the index to be adjusted based on the index field.

Optionally, the calculating module 44 is further configured to:

acquiring current performance index data after executing the preliminary optimization scheme of the index to be processed;

And inputting each piece of current performance index data into a trained index evaluation model, outputting a comprehensive evaluation value through the trained index evaluation model, and taking the comprehensive evaluation value as the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed.

Optionally, the calculating module 44 is further configured to:

Acquiring a training data set, wherein each training sample in the training data set comprises sample data and label data corresponding to the sample data, the sample data comprises various performance index data after a sample query statement is operated, and the label data comprises a comprehensive evaluation value after the sample query statement is operated;

Constructing an initial index evaluation model;

And carrying out iterative training on the initial index evaluation model through the training data set, acquiring training samples from the training data set to form input sample data, outputting output data corresponding to the input sample data based on the index evaluation model in the iterative process, and calculating a loss value in each iterative process based on a loss function, label data corresponding to the input sample data and the output data corresponding to the input sample data until an iteration termination condition is reached, so as to obtain the trained index evaluation model.

Optionally, the optimization module 45 is further configured to:

Acquiring a historical comprehensive evaluation value, comparing the historical comprehensive evaluation value with the current comprehensive evaluation value, and determining a final optimization result of the index to be processed;

comparing the historical comprehensive evaluation value with the current comprehensive evaluation value, and determining a final optimization result of the index to be processed comprises:

When the database performance indicated by the current comprehensive evaluation value is better than the database performance indicated by the historical comprehensive evaluation value and the index to be processed is the index to be adjusted or a pre-established index, taking a preliminary optimization scheme of the index to be processed as a final optimization result of the index to be processed;

When the performance of the database indicated by the current comprehensive evaluation value is better than the performance of the database indicated by the historical comprehensive evaluation value, or the error between the performance of the database indicated by the current comprehensive evaluation value and the performance of the database indicated by the historical comprehensive evaluation value is within a preset error range, and the index to be processed is the outdated index, the final optimization result of the index to be processed is that the outdated index is deleted.

Optionally, the state of the index to be processed includes a visible state and an invisible state, the visible state indicates that the index to be processed can be used by the query statement, and the invisible state indicates that the index to be processed cannot be used by the query statement; the indexes to be processed include outdated indexes, pre-established indexes and indexes to be adjusted, and the optimization module 45 is further configured to:

when the final optimization result of the index to be processed indicates that the index to be processed needs to be deleted and the current state of the index to be processed is a visible state, updating the current state of the index to be processed into an invisible state;

Updating the current state of the index to be processed into a visible state when the final optimization result of the index to be processed indicates that the index to be processed needs to reserve a pre-established index or an optimization index corresponding to the index to be adjusted and the current state of the index to be processed is an invisible state;

when the current condition triggers a preset cleaning condition, the index to be processed in an invisible state is automatically deleted.

Optionally, the optimization module 45 is further configured to:

Responding to a state setting operation of a user on a user interface, acquiring a selected index corresponding to the state setting operation, setting the state of the selected index according to the state setting operation, and setting an index state setting item on the user interface, wherein the index state setting item can be used for selecting an index needing to change the state and setting the state of the index.

Optionally, the indexes to be processed include outdated indexes, pre-established indexes, indexes to be adjusted, and the optimization module 45 is further configured to:

generating an optimization suggestion according to the final optimization result of the index to be processed, and displaying the optimization suggestion;

the generating optimization suggestions according to the final optimization result of the index to be processed comprises at least one or more of the following:

classifying and displaying final optimization results corresponding to various indexes to be processed;

For the pre-established index, displaying the comparison data of each load performance index after the index establishment is not executed and the pre-established index is executed, and highlighting the load performance index with the comprehensive evaluation value arranged in the preset front position;

For the index to be adjusted, displaying the comparison data of each load performance index after the index to be adjusted is executed and the optimization index corresponding to the index to be adjusted is executed, and highlighting the load performance index with the comprehensive evaluation value arranged in the preset front position;

And for the outdated index, displaying the comparison data of the outdated index which is not deleted and each load performance index which is deleted, and highlighting the load performance index which affects the level of the load performance index arranged in the preset front position.

Those skilled in the art will appreciate that the structure of the database index optimizing apparatus in fig. 4 does not constitute a limitation of the database index optimizing apparatus, and the respective modules may be implemented in whole or in part by software, hardware, and combinations thereof. The above modules may be embedded in hardware or independent of a controller in a computer device, or may be stored in software in a memory in the computer device, so that the controller may call and execute operations corresponding to the above modules. In other embodiments, more or fewer modules than illustrated may be included in the database index optimizing apparatus.

Referring to fig. 5, in another aspect of the embodiment of the present application, there is further provided an electronic device 500, including a memory 3011 and a processor 3012, where the memory 3011 stores a computer program, and the computer program when executed by the processor causes the processor 3012 to execute the steps of the database index optimization method provided in any of the foregoing embodiments of the present application. Electronic device 500 may include a computing device (e.g., a desktop computer, a laptop computer, a tablet computer, a handheld computer, a smart speaker, a server, etc.), a mobile phone (e.g., a smart phone, a wireless phone, etc.), a wearable device (e.g., a pair of smart glasses or a smart watch), or the like.

Wherein the database index optimization device includes, but is not limited to: embedded equipment and electronic equipment.

Where the processor 3012 is a control center, various interfaces and lines are utilized to connect various portions of the overall computer device, perform various functions of the computer device and process data by running or executing software programs and/or modules stored in the memory 3011, and invoking data stored in the memory 3011. Optionally, the processor 3012 may include one or more processing cores; preferably, the processor 3012 may integrate an application processor and a modem processor, wherein the application processor primarily handles operating systems, user pages, applications, etc., and the modem processor primarily handles wireless communications. It will be appreciated that the modem processor described above may not be integrated into the processor 3012.

The memory 3011 may be used to store software programs and modules, and the processor 3012 executes various functional applications and data processing by executing the software programs and modules stored in the memory 3011. The memory 3011 may mainly include a storage program area that may store an operating system, application programs required for at least one function (such as a sound playing function, an image playing function, etc.), and a storage data area; the storage data area may store data created according to the use of the computer device, etc. In addition, memory 3011 may include high-speed random access memory, and may also include non-volatile memory, such as at least one magnetic disk storage device, flash memory device, or other volatile solid-state storage device. Accordingly, the memory 3011 may also include a memory controller to provide access to the memory 3011 by the processor 3012.

In another aspect of the embodiments of the present application, there is further provided a storage medium storing a computer program, where the computer program when executed by a processor causes the processor to execute the steps of the database index optimization method provided in any one of the foregoing embodiments of the present application.

Those skilled in the art will appreciate that implementing all or part of the processes of the methods provided in the above embodiments may be accomplished by computer programs stored on a non-transitory computer readable storage medium, which when executed, may comprise processes of the embodiments of the methods described above. Any reference to memory, storage, database, or other medium used in embodiments provided herein may include non-volatile and/or volatile memory. The nonvolatile memory can include Read Only Memory (ROM), programmable ROM (PROM), electrically Programmable ROM (EPROM), electrically Erasable Programmable ROM (EEPROM), or flash memory. Volatile memory can include Random Access Memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in a variety of forms such as Static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double Data Rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (SYNCHLINK) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), among others.

The foregoing is merely illustrative of the present invention, and the present invention is not limited thereto, and any person skilled in the art will readily recognize that variations or substitutions are within the scope of the present invention. The scope of the invention is to be determined by the appended claims.

Claims (7)

1. A method for optimizing a database index, comprising:

Acquiring operation data of a database;

Determining an index to be processed based on the operation data, wherein the index to be processed comprises an outdated index, a pre-established index and an index to be adjusted;

Generating a preliminary optimization scheme of the index to be processed, wherein the generating the preliminary optimization scheme of the index to be processed comprises the following steps: generating an optimization scheme for deleting the outdated index for the outdated index; for the index to be adjusted, acquiring a plurality of columns associated with the index to be adjusted, determining an index field based on the columns, and generating an optimized index corresponding to the index to be adjusted based on the index field;

Executing the preliminary optimization scheme of the index to be processed, and calculating the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed, wherein the calculating the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed comprises: acquiring current performance index data after executing the preliminary optimization scheme of the index to be processed; inputting each current performance index data into a trained index evaluation model, outputting a comprehensive evaluation value through the trained index evaluation model, and taking the comprehensive evaluation value as a current comprehensive evaluation value of a preliminary optimization scheme of the index to be processed;

Determining a final optimization result of the index to be processed according to the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed, wherein the determining the final optimization result of the index to be processed according to the current comprehensive evaluation value of the preliminary optimization scheme of the index to be processed comprises: acquiring a historical comprehensive evaluation value, comparing the historical comprehensive evaluation value with the current comprehensive evaluation value, and determining a final optimization result of the index to be processed; comparing the historical comprehensive evaluation value with the current comprehensive evaluation value, and determining a final optimization result of the index to be processed comprises: when the database performance indicated by the current comprehensive evaluation value is better than the database performance indicated by the historical comprehensive evaluation value and the index to be processed is the index to be adjusted or a pre-established index, taking a preliminary optimization scheme of the index to be processed as a final optimization result of the index to be processed; when the performance of the database indicated by the current comprehensive evaluation value is better than the performance of the database indicated by the historical comprehensive evaluation value, or the error between the performance of the database indicated by the current comprehensive evaluation value and the performance of the database indicated by the historical comprehensive evaluation value is within a preset error range, and the index to be processed is the outdated index, the final optimization result of the index to be processed is that the outdated index is deleted.

2. The database index optimization method of claim 1, wherein the operational data comprises at least one of: the method comprises the steps of determining indexes to be processed based on operation index data generated in the execution process of resource occupation data, index statistical information and query sentences, wherein the indexes to be processed comprise one or more of the following combinations:

determining a target query statement meeting optimization conditions according to the operation data, acquiring an existing index associated with the target query statement from the database, and determining the existing index associated with the target query statement as a pending index;

Determining a target query statement meeting optimization conditions according to the operation data, generating a pre-established index based on the target query statement, and determining the pre-established index as a to-be-processed index, wherein the target query statement meeting optimization conditions comprises at least one of the following: query sentences with the resource occupation data larger than the resource occupation threshold and query sentences with the operation index data larger than the operation index threshold;

And determining indexes to be optimized which meet the conditions according to the index statistical information, and determining the indexes to be optimized which meet the conditions as indexes to be processed.

3. The database index optimization method of claim 2, wherein the determining the pending index based on the operational data comprises:

When the index to be processed is the associated existing index and/or the index to be optimized meeting the condition, acquiring index statistical information of the index to be processed, determining the index to be processed meeting the condition of the outdated index as the outdated index, and determining the index to be processed meeting the condition of adjusting the existing index structure as the index to be adjusted, wherein the condition of meeting the outdated index comprises at least one of the following: the number of inquiry times is smaller than the preset number of inquiry times, the hit rate is smaller than the preset hit rate, and the current use time of the distance between the latest use time is longer than the preset duration; compliance with existing index structure adjustment conditions includes at least one of: the number of queries is greater than or equal to the preset number of queries and the hit rate is greater than or equal to the preset hit rate.

4. The database index optimization method of claim 1, wherein the method further comprises:

Acquiring a training data set, wherein each training sample in the training data set comprises sample data and label data corresponding to the sample data, the sample data comprises various performance index data after a sample query statement is operated, and the label data comprises a comprehensive evaluation value after the sample query statement is operated;

Constructing an initial index evaluation model;

And carrying out iterative training on the initial index evaluation model through the training data set, acquiring training samples from the training data set to form input sample data, outputting output data corresponding to the input sample data based on the index evaluation model in the iterative process, and calculating a loss value in each iterative process based on a loss function, label data corresponding to the input sample data and the output data corresponding to the input sample data until an iteration termination condition is reached, so as to obtain the trained index evaluation model.

5. The database index optimization method of claim 1, wherein the states of the pending index include a visible state and an invisible state, the visible state indicating that the pending index can be used by query statements, the invisible state indicating that the pending index cannot be used by query statements; the index to be processed comprises an outdated index, a pre-established index and an index to be adjusted, and the method further comprises:

when the final optimization result of the index to be processed indicates that the index to be processed needs to be deleted and the current state of the index to be processed is a visible state, updating the current state of the index to be processed into an invisible state;

Updating the current state of the index to be processed into a visible state when the final optimization result of the index to be processed indicates that the index to be processed needs to reserve a pre-established index or an optimization index corresponding to the index to be adjusted and the current state of the index to be processed is an invisible state;

when the current condition triggers a preset cleaning condition, the index to be processed in an invisible state is automatically deleted.

6. The database index optimization method of claim 1, wherein the method further comprises:

Responding to a state setting operation of a user on a user interface, acquiring a selected index corresponding to the state setting operation, setting the state of the selected index according to the state setting operation, and setting an index state setting item on the user interface, wherein the index state setting item can be used for selecting an index needing to change the state and setting the state of the index.

7. The database index optimization method of any one of claims 1 to 6, wherein the method further comprises:

generating an optimization suggestion according to the final optimization result of the index to be processed, and displaying the optimization suggestion;

the generating optimization suggestions according to the final optimization result of the index to be processed comprises at least one or more of the following:

classifying and displaying final optimization results corresponding to various indexes to be processed;

For the pre-established index, displaying the comparison data of each load performance index after the index establishment is not executed and the pre-established index is executed, and highlighting the load performance index with the comprehensive evaluation value arranged in the preset front position;

For the index to be adjusted, displaying the comparison data of each load performance index after the index to be adjusted is executed and the optimization index corresponding to the index to be adjusted is executed, and highlighting the load performance index with the comprehensive evaluation value arranged in the preset front position;

And for the outdated index, displaying the comparison data of the outdated index which is not deleted and each load performance index which is deleted, and highlighting the load performance index which affects the level of the load performance index arranged in the preset front position.