CN112559805A - Index optimization method and device - Google Patents
Index optimization method and device Download PDFInfo
- Publication number
- CN112559805A CN112559805A CN202011366665.6A CN202011366665A CN112559805A CN 112559805 A CN112559805 A CN 112559805A CN 202011366665 A CN202011366665 A CN 202011366665A CN 112559805 A CN112559805 A CN 112559805A
- Authority
- CN
- China
- Prior art keywords
- index
- data
- recommended
- database
- task
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000005457 optimization Methods 0.000 title claims abstract description 89
- 238000000034 method Methods 0.000 title claims abstract description 73
- 238000012795 verification Methods 0.000 claims description 30
- 238000011156 evaluation Methods 0.000 claims description 27
- 238000010200 validation analysis Methods 0.000 claims description 16
- 238000012216 screening Methods 0.000 claims description 13
- 238000012545 processing Methods 0.000 claims description 10
- 230000008569 process Effects 0.000 abstract description 35
- 230000000694 effects Effects 0.000 description 12
- 238000004891 communication Methods 0.000 description 10
- 238000004590 computer program Methods 0.000 description 5
- 238000010586 diagram Methods 0.000 description 4
- 230000009471 action Effects 0.000 description 3
- 230000008878 coupling Effects 0.000 description 3
- 238000010168 coupling process Methods 0.000 description 3
- 238000005859 coupling reaction Methods 0.000 description 3
- 238000001914 filtration Methods 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 230000002411 adverse Effects 0.000 description 1
- 238000003491 array Methods 0.000 description 1
- 230000006872 improvement Effects 0.000 description 1
- 238000012544 monitoring process Methods 0.000 description 1
- 230000000717 retained effect Effects 0.000 description 1
- 230000011664 signaling Effects 0.000 description 1
- 230000008685 targeting Effects 0.000 description 1
- 230000001960 triggered effect Effects 0.000 description 1
- 239000002699 waste material Substances 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/90—Details of database functions independent of the retrieved data types
- G06F16/901—Indexing; Data structures therefor; Storage structures
Abstract
The application provides an index optimization method and device, which are applied to the field of databases. Therefore, in the process of optimizing the index of the database, the query of the execution efficiency and the optimization of the index can be realized according to the historical index data, so that the optimization efficiency and the optimization accuracy are improved.
Description
Technical Field
The application relates to the field of databases, in particular to an index optimization method and device.
Background
In the prior art, the scheme for optimizing the index in the database is generally as follows: the execution efficiency of the query is judged manually through monitoring and logging, and then the index is optimized through operations such as creating and modifying, so that the statement performance is improved. However, determining the execution efficiency of the query and optimizing the index manually is inefficient and less accurate.
Disclosure of Invention
An object of the embodiments of the present application is to provide an index optimization method and apparatus, so as to solve the technical problems of low efficiency and low accuracy in determining the execution efficiency of a query and performing related optimization on an index by a human.
In order to achieve the above purpose, the technical solutions provided in the embodiments of the present application are as follows:
in a first aspect, an embodiment of the present application provides an index optimization method, including: acquiring historical index data of a database; generating a recommended index task according to the historical index data; executing the recommended index task, and respectively acquiring first verification data before the recommended index task is executed and second verification data after the recommended index task is executed; the first verification data comprises the overall query performance of the database before the recommended index task is executed, and the second verification data comprises the overall query performance of the database after the recommended index task is executed; determining a gain in overall query performance for the database based on the first validation data and the second validation data; and if the gain of the overall query performance of the database is greater than a preset gain threshold, maintaining the index after the recommended index task is executed so as to realize index optimization of the database. In the above scheme, based on the historical index data generated by the database, a corresponding recommended index task may be generated, and after the recommended index task is executed, the index after the recommended index task is executed is determined based on the evaluation of the overall query performance of the database, so as to optimize the index of the database. Therefore, in the process of optimizing the index of the database, the query of the execution efficiency and the optimization of the index can be realized according to the historical index data, so that the optimization efficiency and the optimization accuracy are improved.
In an optional embodiment of the present application, the historical index data includes index missing data, and the index missing data includes missing identification information, missing count information, and missing influence evaluation information; the acquiring of the historical index data of the database comprises: if the index is missed during query, recording the missing identification information corresponding to the missed index and the missing count information queried by taking the missed index as a target; obtaining the missing influence evaluation information for characterizing the influence of the missing index on the query. In the above scheme, if the index of the query is found to be missing in the process of querying the index, the related information of the missing index can be recorded to generate a recommended index task related to the missing index, so that optimization of the database index is realized.
In an optional embodiment of the present application, the generating a recommendation index task according to the historical index data includes: and generating a task for increasing the index according to the index missing data. In the above scheme, if the index of the query is found to be missing in the process of querying the index, a task of adding the index can be generated, so that the optimization of the database index is realized.
In an optional embodiment of the present application, the historical index data comprises index hit data, the index hit data comprising hit identification information, hit count information, and hit impact evaluation information; the acquiring of the historical index data of the database comprises: if the index is hit during query, recording the hit identification information corresponding to the hit index and the hit count information for querying by taking the hit index as a target; and obtaining the hit influence evaluation information for representing the influence of the index of the optimized hit on the query. In the above scheme, if the index of the query is found to be hit in the process of querying the index, the relevant information of the hit index can be recorded, so that a recommended index task related to the hit index is generated when needed, and optimization of the database index is realized.
In an optional embodiment of the present application, the generating a recommendation index task according to the historical index data includes: and generating a task for modifying the index according to the index hit data. In the above scheme, if the index of the query is found to be hit in the process of querying the index, a task of modifying the index can be generated when needed, so that the optimization of the database index is realized.
In an optional embodiment of the present application, the historical index data includes query performance data corresponding to hardware that processes a query; the acquiring of the historical index data of the database further comprises: obtaining the query performance data; and counting the query performance data according to different dimensions, and grouping the counted data according to time to obtain the historical index data. In the above scheme, the obtained historical index data may include, in addition to the information related to the index, query performance data corresponding to the hardware in the process of processing the query, so that in the process of optimizing the index of the database, the performance influence of the hardware is considered to achieve the best optimization effect.
In an optional embodiment of the present application, after the generating a recommended index task according to the historical index data, the method further includes: determining the influence of each recommended index task on the overall query performance of the database; and screening out recommended index tasks of which the influence on the overall query performance of the database is lower than a preset influence threshold value. In the scheme, in the generated recommended index tasks, the influence of each recommended index task on the overall query performance of the database is determined, and the recommended index tasks with smaller overall query performance of the database are screened out, so that better optimization effect on the index of the database is realized.
In an optional embodiment of the present application, after the determining an influence of each recommended indexing task on the overall query performance of the database, the method further comprises: and screening out recommended index tasks with the difference value of the influences of the overall query performance of the database under different conditions being larger than a preset difference value threshold value. In the scheme, the recommended index tasks with large influence difference of the overall query performance of the database under different conditions are screened out from the generated recommended index tasks, so that better optimization effect on the index of the database is realized.
In an optional embodiment of the present application, the determining an influence of each recommended index task on the overall query performance of the database includes: acquiring the frequency of the indexes corresponding to the recommended index tasks to be queried, the average cost of each query and the cost percentage which can be optimized by the indexes and is evaluated by an optimizer; and determining the product of the frequency of querying the index corresponding to the recommended index task, the average cost of each query and the percentage of the cost which can be optimized by the index and is evaluated by the optimizer as the influence of each recommended index task on the overall query performance of the database. In the above scheme, the influence of each recommended index task on the overall query performance of the database can be determined according to the frequency of the indexes being queried, the average cost of each query and the percentage of the cost which can be optimized by the indexes and is evaluated by the optimizer.
In an optional embodiment of the present application, after the generating a recommended index task according to the historical index data, the method further includes: and merging the recommended index tasks aiming at the same table in the recommended index tasks. In the scheme, the recommended index tasks aiming at the same table in the index recommended index tasks can be combined into one recommended index task, so that the times of executing the tasks are reduced, and the optimization efficiency is improved.
In a second aspect, an embodiment of the present application provides an index optimization apparatus, including: the acquisition module is used for acquiring historical index data of the database; the generation module is used for generating a recommended index task according to the historical index data; the execution module is used for executing the recommended index task and respectively acquiring first verification data before the recommended index task is executed and second verification data after the recommended index task is executed; the first verification data comprises the overall query performance of the database before the recommended index task is executed, and the second verification data comprises the overall query performance of the database after the recommended index task is executed; a first determining module for determining a gain of overall query performance of the database based on the first validation data and the second validation data; and the optimization module is used for keeping the index after the recommended index task is executed if the gain of the overall query performance of the database is greater than a preset gain threshold value so as to realize index optimization of the database. In the above scheme, based on the historical index data generated by the database, a corresponding recommended index task may be generated, and after the recommended index task is executed, the index after the recommended index task is executed is determined based on the evaluation of the overall query performance of the database, so as to optimize the index of the database. Therefore, in the process of optimizing the index of the database, the query of the execution efficiency and the optimization of the index can be realized according to the historical index data, so that the optimization efficiency and the optimization accuracy are improved.
In an optional embodiment of the present application, the historical index data includes index missing data, and the index missing data includes missing identification information, missing count information, and missing influence evaluation information; the acquisition module is further configured to: if the index is missed during query, recording the missing identification information corresponding to the missed index and the missing count information queried by taking the missed index as a target; obtaining the missing influence evaluation information for characterizing the influence of the missing index on the query. In the above scheme, if the index of the query is found to be missing in the process of querying the index, the related information of the missing index can be recorded to generate a recommended index task related to the missing index, so that optimization of the database index is realized.
In an optional embodiment of the present application, the generating module is further configured to: and generating a task for increasing the index according to the index missing data. In the above scheme, if the index of the query is found to be missing in the process of querying the index, a task of adding the index can be generated, so that the optimization of the database index is realized.
In an optional embodiment of the present application, the historical index data comprises index hit data, the index hit data comprising hit identification information, hit count information, and hit impact evaluation information; the acquisition module is further to: if the index is hit during query, recording the hit identification information corresponding to the hit index and the hit count information for querying by taking the hit index as a target; and obtaining the hit influence evaluation information for representing the influence of the index of the optimized hit on the query. In the above scheme, if the index of the query is found to be hit in the process of querying the index, the relevant information of the hit index can be recorded, so that a recommended index task related to the hit index is generated when needed, and optimization of the database index is realized.
In an optional embodiment of the present application, the generating module is further configured to: and generating a task for modifying the index according to the index hit data. In the above scheme, if the index of the query is found to be hit in the process of querying the index, a task of modifying the index can be generated when needed, so that the optimization of the database index is realized.
In an optional embodiment of the present application, the historical index data includes query performance data corresponding to hardware that processes a query; the acquisition module is further to: obtaining the query performance data; and counting the query performance data according to different dimensions, and grouping the counted data according to time to obtain the historical index data. In the above scheme, the obtained historical index data may include, in addition to the information related to the index, query performance data corresponding to the hardware in the process of processing the query, so that in the process of optimizing the index of the database, the performance influence of the hardware is considered to achieve the best optimization effect.
In an alternative embodiment of the present application, the apparatus further comprises: the second determination module is used for determining the influence of each recommended index task on the overall query performance of the database; and the first screening module is used for screening the recommended index tasks of which the influence on the overall query performance of the database is lower than a preset influence threshold value. In the scheme, in the generated recommended index tasks, the influence of each recommended index task on the overall query performance of the database is determined, and the recommended index tasks with smaller overall query performance of the database are screened out, so that better optimization effect on the index of the database is realized.
In an alternative embodiment of the present application, the apparatus further comprises: and the second screening module is used for screening the recommended index tasks of which the difference value of the influences of the overall query performance of the database under different conditions is greater than a preset difference value threshold value. In the scheme, the recommended index tasks with large influence difference of the overall query performance of the database under different conditions are screened out from the generated recommended index tasks, so that better optimization effect on the index of the database is realized.
In an optional embodiment of the present application, the second determining module is further configured to: acquiring the frequency of the indexes corresponding to the recommended index tasks to be queried, the average cost of each query and the cost percentage which can be optimized by the indexes and is evaluated by an optimizer; and determining the product of the frequency of querying the index corresponding to the recommended index task, the average cost of each query and the percentage of the cost which can be optimized by the index and is evaluated by the optimizer as the influence of each recommended index task on the overall query performance of the database. In the above scheme, the influence of each recommended index task on the overall query performance of the database can be determined according to the frequency of the indexes being queried, the average cost of each query and the percentage of the cost which can be optimized by the indexes and is evaluated by the optimizer.
In an alternative embodiment of the present application, the apparatus further comprises: and the merging module is used for merging the recommended index tasks aiming at the same table in the recommended index tasks. In the scheme, the recommended index tasks aiming at the same table in the index recommended index tasks can be combined into one recommended index task, so that the times of executing the tasks are reduced, and the optimization efficiency is improved.
In a third aspect, an embodiment of the present application provides an electronic device, including: a processor, a memory, and a bus; the processor and the memory are communicated with each other through the bus; the memory stores program instructions executable by the processor, the processor invoking the program instructions capable of performing the index optimization method as in the first aspect.
In a fourth aspect, embodiments of the present application provide a non-transitory computer-readable storage medium storing computer instructions that cause the computer to perform the index optimization method as in the first aspect.
In order to make the aforementioned objects, features and advantages of the present application more comprehensible, embodiments accompanied with figures are described in detail below.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings that are required to be used in the embodiments of the present application will be briefly described below, it should be understood that the following drawings only illustrate some embodiments of the present application and therefore should not be considered as limiting the scope, and that those skilled in the art can also obtain other related drawings based on the drawings without inventive efforts.
Fig. 1 is a flowchart of an index optimization method according to an embodiment of the present disclosure;
fig. 2 is a flowchart of an embodiment of step S101 provided in this embodiment of the present application;
fig. 3 is a flowchart of another specific implementation of step S101 provided in an embodiment of the present application;
fig. 4 is a flowchart of another specific implementation of step S101 provided in an embodiment of the present application;
FIG. 5 is a flowchart illustrating an evaluation of an impact of a recommendation indexing task on overall query performance of a database according to an embodiment of the present application;
fig. 6 is a block diagram illustrating an index optimization apparatus according to an embodiment of the present disclosure;
fig. 7 is a block diagram of an electronic device according to an embodiment of the present disclosure.
Detailed Description
The technical solutions in the embodiments of the present application will be described below with reference to the drawings in the embodiments of the present application.
Referring to fig. 1, fig. 1 is a flowchart of an index optimization method according to an embodiment of the present disclosure, where the index optimization method includes the following steps:
step S101: and acquiring historical index data of the database.
Step S102: and generating a recommended index task according to the historical index data.
Step S103: and executing the recommended index task, and respectively acquiring first verification data before the recommended index task is executed and second verification data after the recommended index task is executed.
Step S104: a gain in overall query performance of the database is determined based on the first validation data and the second validation data.
Step S105: and if the gain of the overall query performance of the database is greater than a preset gain threshold, maintaining the index after the recommended index task is executed so as to realize index optimization of the database.
In the process of daily querying the index in the database, some corresponding data are generated, for example: the index corresponds to a flag, a performance of the hardware during query, and the like, which is not specifically limited in the embodiments of the present application. The generated data can be stored locally in the electronic device, or uploaded to the cloud server for storage so as to be read when needed, and the data is the historical index data generated based on the database in the embodiment of the application.
In this embodiment of the present application, the historical index data of the database may be obtained first. Since the manner of storing the historical index data is different, the manner of acquiring the historical index data is also different in the embodiment of the present application. For example: the method and the device have the advantages that the prestored historical index data can be directly read from the electronic device locally, the historical index data sent from the cloud service area can also be received, the historical index data sent by the external device can also be read, the method and the device are not specifically limited, and the technical personnel in the field can carry out proper adjustment according to actual conditions.
As an embodiment, in the process of acquiring the historical index data, the embodiment of the present application may acquire all the stored historical index data, or may acquire only a part of the stored historical index data, for example: obtaining historical index data of last three months, obtaining historical index data between one month and three months, and the like. As another embodiment, in the process of storing the historical index data, all of the historical index data may be retained, or a part of the historical index data may be deleted after a certain time, for example: historical index data stored one year ago can be deleted, all historical index data corresponding to a certain table in a database can be deleted, and the like. The embodiments of the present application are not particularly limited to these examples.
After obtaining the historical index data of the database, one or more recommended index tasks may be generated based on the historical index data, where the recommended index tasks are generated based on different historical index data, for example: index missing data, index hit data, query performance data, etc., different recommended index tasks may be generated. This is illustrated by way of example below:
first, assume that the acquired historical index data of the database includes: index missing data, wherein the index missing data may include missing identification information, missing count information, and missing impact evaluation information. At this time, referring to fig. 2, fig. 2 is a flowchart of a specific implementation manner of step S101 provided in this embodiment, where the step S101 may include the following steps:
step S201: if the index is missed during query, recording missing identification information corresponding to the missed index and missing count information for querying by taking the missed index as a target.
Step S202: missing influence evaluation information for characterizing the influence of the missing index on the query is obtained.
At this time, the step S102 may include the steps of:
and step one, generating a task of increasing the index according to the index missing data.
Specifically, if a corresponding index is not queried in the process of querying the database index, the index may be considered as a missing index, and at this time, index missing data corresponding to the missing index may be stored. The missing identification information refers to an identification of index missing triggered by query, that is, an identification corresponding to the missing index; the missing count information refers to the frequency or times of querying with each missing index as a target, namely the frequency or times of querying a certain missing index before acquiring historical index data; the missing influence evaluation information assumes that the missing index has an influence on the query.
For the missing index, a recommended index task for adding the index may be generated based on the missing index.
In the above scheme, if the index of the query is found to be missing in the process of querying the index, the related information of the missing index may be recorded to generate a recommended index task related to the missing index and increasing the index, so as to optimize the database index.
Secondly, assume that the acquired historical index data of the database includes: index hit data, where the index hit data may include hit identification information, hit count information, and hit impact evaluation information. At this time, referring to fig. 3, fig. 3 is a flowchart of another specific implementation of step S101 provided in this embodiment of the application, where the step S101 may include the following steps:
step S301: if the index is hit during query, the hit identification information corresponding to the hit index and the hit count information for querying with the hit index as a target are recorded.
Step S302: and obtaining hit influence evaluation information for representing the influence of the indexes of the optimized hits on the query.
At this time, the step S102 may include the steps of:
in the first step, a task for modifying the index is generated according to the index hit data.
Specifically, if the corresponding index is found in the process of querying the database index, it may be considered that the index is hit, and at this time, the index hit data corresponding to the hit index may be stored. Similar to the above embodiment, the hit identification information refers to an identification of an index that is hit due to a query, that is, an identification corresponding to the hit index; the hit count information refers to the frequency or number of queries targeting each hit index, i.e., the frequency or number of queries for a certain hit index before obtaining historical index data; the hit impact evaluation information is the impact on the query assuming that the hit index is optimized.
For the hit index, a recommended index task that optimizes the index may be generated based on the hit index.
In the above scheme, if the index of the query is found to be hit in the process of querying the index, the related information of the hit index can be recorded, so that a recommended index task related to the hit index and modifying the index can be generated when needed, and optimization of the database index is realized.
Finally, assume that the obtained historical index data of the database includes: referring to fig. 4, fig. 4 is a flowchart illustrating a further specific implementation manner of step S101 according to an embodiment of the present application, where the step S101 may include the following steps:
step S401: query performance data is obtained.
Step S402: and counting the query performance data according to different dimensions, and grouping the counted data according to time to obtain historical index data.
Specifically, in the process of querying an index in a database, query performance data corresponding to hardware for processing a query may be synchronously obtained, where the query performance data may include a number of processor cycles for processing the query, a usage amount of a storage medium, and the like. The usage amount of the storage medium may be divided into a volatile storage usage case and a nonvolatile usage case.
For comparison, the query performance data may be counted at various latitudes, for example: mean, median, etc., and then group save at different time intervals. Thus, when acquiring the query performance data, the data after statistical grouping can be directly acquired.
In the above scheme, the obtained historical index data may include, in addition to the information related to the index, query performance data corresponding to the hardware in the process of processing the query, so that in the process of optimizing the index of the database, the performance influence of the hardware is considered to achieve the best optimization effect.
It should be understood that the three historical index data are only examples provided in the embodiments of the present application, and those skilled in the art may also adopt more historical index data in combination with the technical means used in the art, and the embodiments of the present application are not limited specifically. In addition, the acquired historical index data may be one of the three types of historical index data, or may be a plurality of the three types of historical index data, which is also not specifically limited in the embodiment of the present application.
As one embodiment, the recommendation index task may include an index identification, a database entity corresponding to the index, and an impact of the recommendation index task on the overall query performance of the database. The following method can be adopted for evaluating the influence of the recommendation index task on the overall query performance of the database:
referring to fig. 5, fig. 5 is a flowchart for evaluating an influence of a recommendation indexing task on an overall query performance of a database according to an embodiment of the present application, where the flowchart may include the following steps:
step S501: and acquiring the frequency of the indexes queried corresponding to the recommended index tasks, the average cost of each query and the cost percentage which can be optimized by the indexes and is evaluated by the optimizer.
Step S502: and determining the product of the frequency of querying the index corresponding to the recommended index task, the average cost of each query and the percentage of the cost which can be optimized by the index and is evaluated by the optimizer as the influence of each recommended index task on the overall query performance of the database.
Specifically, the frequency or the number of times that the index corresponding to the recommended index task is queried and the average cost of the hardware corresponding to each query may be obtained based on the historical index data obtained in step S101, and then, the optimizer may be used to evaluate the percentage of the cost that may be optimized by the index. The influence of the recommendation index task on the overall query performance of the database is the product of the three data.
In the above scheme, the influence of each recommended index task on the overall query performance of the database can be determined according to the frequency of the indexes being queried, the average cost of each query and the percentage of the cost which can be optimized by the indexes and is evaluated by the optimizer.
Further, after determining the influence of the recommended index task on the overall query performance of the database, the index optimization method provided by the embodiment of the application may further include the following steps:
in the first step, the influence of each recommended index task on the overall query performance of the database is determined.
And secondly, screening out recommended index tasks of which the influence on the overall query performance of the database is lower than a preset influence threshold value from the recommended index tasks.
Specifically, after determining the influence of each recommended index task on the overall query performance of the database, the recommended index tasks with smaller influence on the overall query performance of the database may be screened out, that is, the recommended index tasks with influence on the overall query performance of the database lower than a preset influence threshold may be screened out.
There are three cases of these recommendation indexing tasks: first, the overall query performance of the database may be adversely affected, for example: slower query speed, etc.; second, the overall query performance of the database is not altered; third, the impact on the overall query performance of the database is small, for example: increase query speed only a little, etc. Therefore, in order to reduce the resource waste caused by unnecessary optimization, the recommendation index task with less influence on the overall query performance of the database can be screened out.
It is to be understood that, in the embodiment of the present application, the magnitude of the preset influence threshold in the above embodiment is not specifically limited, and a person skilled in the art may make appropriate adjustments according to actual situations.
In the scheme, in the generated recommended index tasks, the influence of each recommended index task on the overall query performance of the database is determined, and the recommended index tasks with smaller overall query performance of the database are screened out, so that better optimization effect on the index of the database is realized.
In addition, besides the recommendation index task having an influence on the overall query performance of the database below the preset influence threshold in the above embodiment, some other recommendation index tasks may be screened, for example: recommending index tasks, wherein the difference of the influences of the overall query performance of the database under different conditions is larger than a preset difference threshold value.
Specifically, the same index may be hit in different situations, and there is a case that the index may greatly improve the overall query performance of the database when querying one table, and greatly reduce the overall query performance of the database when querying another table, so that, in order to ensure that the overall performance of the database is better, the recommended index task of this type may also be filtered out.
It is to be understood that, similar to the foregoing embodiments, the magnitude of the preset influence threshold in the foregoing embodiments is not specifically limited in the embodiments of the present application, and those skilled in the art may make appropriate adjustments according to actual situations.
In the scheme, the recommended index tasks with large influence difference of the overall query performance of the database under different conditions are screened out from the generated recommended index tasks, so that better optimization effect on the index of the database is realized.
In the above embodiment, two ways of filtering out the recommendation index tasks are described, and it can be understood that after the recommendation index tasks are generated, in addition to filtering out a part of the recommendation index tasks in the two ways, other operations may be performed on the generated recommendation index tasks. For example, the recommended index tasks for the same table in the recommended index tasks may be merged, and the like, which is not specifically limited in the embodiment of the present application.
In the scheme, the recommended index tasks aiming at the same table in the index recommended index tasks can be combined into one recommended index task, so that the times of executing the tasks are reduced, and the optimization efficiency is improved.
After the recommendation index task is generated and processed as required, the reserved recommendation index task can be executed. Before the recommended index task is executed, a user can manually select one recommended index task to execute, and the electronic equipment can also automatically execute one recommended index task.
For example: if the user manually selects one recommended index task to execute, all the generated recommended index tasks are firstly output to the user for selection, and the user executes the recommended index task selected by the user after selection; and if the electronic equipment automatically executes a recommended index task, directly executing a certain recommended index task.
It can be understood that, in order to compare the overall query performance of the database before and after the recommendation index task is executed, before the recommendation index task is executed, it is required to determine whether the relevant data of the overall query performance of the database before the recommendation index task is executed has been collected, and if not, the recommendation index task is executed after the data is collected. Similarly, after the recommendation index task is executed, relevant data of the overall query performance of the database after the recommendation index task is executed needs to be collected.
Then, the gain of the overall query performance of the database before and after the recommended indexing task is executed is determined according to the data related to the overall query performance of the database before the recommended indexing task is executed (i.e., the first verification data in step S103) and the data related to the overall query performance of the database after the recommended indexing task is executed (i.e., the second verification data in step S103).
If the overall query performance gain is significant (namely the gain of the overall query performance of the database is greater than a preset gain threshold), the index after the task is executed can be kept so as to realize the index optimization of the database; if there is no significant overall query performance gain (i.e., the gain in overall query performance of the database is not greater than the preset gain threshold), the pre-mission index is restored.
It is to be understood that, in the embodiment of the present application, the magnitude of the preset gain threshold in the above embodiment is not specifically limited, and a person skilled in the art may make appropriate adjustments according to actual situations.
In the above scheme, based on the historical index data generated by the database, a corresponding recommended index task may be generated, and after the recommended index task is executed, the index after the recommended index task is executed is determined based on the evaluation of the overall query performance of the database, so as to optimize the index of the database. Therefore, in the process of optimizing the index of the database, the query of the execution efficiency and the optimization of the index can be realized according to the historical index data, so that the optimization efficiency and the optimization accuracy are improved.
Referring to fig. 6, fig. 6 is a block diagram illustrating an index optimization apparatus according to an embodiment of the present disclosure, where the index optimization apparatus 600 may include: an obtaining module 601, configured to obtain historical index data of a database; a generating module 602, configured to generate a recommended index task according to the historical index data; an executing module 603, configured to execute the recommended index task, and respectively obtain first verification data before the recommended index task is executed and second verification data after the recommended index task is executed; the first verification data comprises the overall query performance of the database before the recommended index task is executed, and the second verification data comprises the overall query performance of the database after the recommended index task is executed; a first determining module 604 for determining a gain of the overall query performance of the database based on the first validation data and the second validation data; an optimizing module 605, configured to keep the index after the recommended index task is executed if the gain of the overall query performance of the database is greater than a preset gain threshold, so as to implement index optimization on the database.
In the embodiment of the application, a corresponding recommended index task can be generated based on historical index data generated by a database, and after the recommended index task is executed, a reserved index after the recommended index task is executed is determined based on the evaluation of the overall query performance of the database, so that index optimization of the database is realized. Therefore, in the process of optimizing the index of the database, the query of the execution efficiency and the optimization of the index can be realized according to the historical index data, so that the optimization efficiency and the optimization accuracy are improved.
Further, the historical index data comprises index missing data, and the index missing data comprises missing identification information, missing count information and missing influence evaluation information; the obtaining module 601 is further configured to: if the index is missed during query, recording the missing identification information corresponding to the missed index and the missing count information queried by taking the missed index as a target; obtaining the missing influence evaluation information for characterizing the influence of the missing index on the query.
In the embodiment of the application, if the index of the query is found to be missing in the process of querying the index, the related information of the missing index can be recorded to generate a recommended index task related to the missing index, so that the optimization of the database index is realized.
Further, the generating module 602 is further configured to: and generating a task for increasing the index according to the index missing data.
In the embodiment of the application, if the index of the query is found to be missing in the process of querying the index, a task of adding the index can be generated, so that the optimization of the database index is realized.
Further, the historical index data includes index hit data, which includes hit identification information, hit count information, and hit influence evaluation information; the obtaining module 601 further uses: if the index is hit during query, recording the hit identification information corresponding to the hit index and the hit count information for querying by taking the hit index as a target; and obtaining the hit influence evaluation information for representing the influence of the index of the optimized hit on the query.
In the embodiment of the application, if the index of the query is found to be hit in the process of querying the index, the relevant information of the hit index can be recorded, so that a recommended index task related to the hit index can be generated when needed, and the optimization of the database index is realized.
Further, the generating module 602 is further configured to: and generating a task for modifying the index according to the index hit data.
In the embodiment of the application, if the indexes of the query are hit in the process of querying the indexes, a task of modifying the indexes can be generated when needed, so that the optimization of the database indexes is realized.
Further, the historical index data includes query performance data corresponding to hardware processing a query; the obtaining module 601 further uses: obtaining the query performance data; and counting the query performance data according to different dimensions, and grouping the counted data according to time to obtain the historical index data.
In the embodiment of the application, the acquired historical index data may include information related to the index, and may also include query performance data corresponding to hardware in the process of processing the query, so that in the process of optimizing the index of the database, the performance influence of the hardware is considered, and the best optimization effect is achieved.
Further, the index optimizing apparatus 600 further includes: the second determination module is used for determining the influence of each recommended index task on the overall query performance of the database; and the first screening module is used for screening the recommended index tasks of which the influence on the overall query performance of the database is lower than a preset influence threshold value.
In the embodiment of the application, in the generated recommended index tasks, the influence of each recommended index task on the overall query performance of the database is determined, and the recommended index tasks with smaller overall query performance of the database are screened out, so that better optimization effect on the index of the database is realized.
Further, the index optimizing apparatus 600 further includes: and the second screening module is used for screening the recommended index tasks of which the difference value of the influences of the overall query performance of the database under different conditions is greater than a preset difference value threshold value.
In the embodiment of the application, the recommended index tasks with large difference in influence of the overall query performance of the database under different conditions are screened out from the generated recommended index tasks, so that better optimization effect on the index of the database is realized.
Further, the second determining module is further configured to: acquiring the frequency of the indexes corresponding to the recommended index tasks to be queried, the average cost of each query and the cost percentage which can be optimized by the indexes and is evaluated by an optimizer; and determining the product of the frequency of querying the index corresponding to the recommended index task, the average cost of each query and the percentage of the cost which can be optimized by the index and is evaluated by the optimizer as the influence of each recommended index task on the overall query performance of the database.
In the embodiment of the application, the influence of each recommended index task on the overall query performance of the database can be determined according to the frequency of the indexes being queried, the average cost of each query and the percentage of the cost which can be optimized by the indexes and is evaluated by the optimizer.
Further, the index optimizing apparatus 600 further includes: and the merging module is used for merging the recommended index tasks aiming at the same table in the recommended index tasks.
In the embodiment of the application, the recommended index tasks aiming at the same table in the index recommended index tasks can be combined into one recommended index task, so that the times of executing the tasks are reduced, and the optimization efficiency is improved.
Referring to fig. 7, fig. 7 is a block diagram of an electronic device according to an embodiment of the present disclosure, where the electronic device 700 includes: at least one processor 701, at least one communication interface 702, at least one memory 703 and at least one communication bus 704. Wherein the communication bus 704 is used for implementing direct connection communication of these components, the communication interface 702 is used for communicating signaling or data with other node devices, and the memory 703 stores machine readable instructions executable by the processor 701. When the electronic device 700 is in operation, the processor 701 communicates with the memory 703 via a communication bus 704, and the machine-readable instructions, when called by the processor 701, perform the index optimization method described above.
For example, the processor 701 of the embodiment of the present application may read the computer program from the memory 703 through the communication bus 704 and execute the computer program to implement the following method: step S101: and acquiring historical index data of the database. Step S102: and generating a recommended index task according to the historical index data. Step S103: and executing the recommended index task, and respectively acquiring first verification data before the recommended index task is executed and second verification data after the recommended index task is executed. Step S104: a gain in overall query performance of the database is determined based on the first validation data and the second validation data. Step S105: and if the gain of the overall query performance of the database is greater than a preset gain threshold, maintaining the index after the recommended index task is executed so as to realize index optimization of the database.
The processor 701 may be an integrated circuit chip having signal processing capabilities. The Processor 701 may be a general-purpose Processor, including a Central Processing Unit (CPU), a Network Processor (NP), and the like; but also 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, discrete hardware components. Which may implement or perform the various methods, steps, and logic blocks disclosed in the embodiments of the present application. A general purpose processor may be a microprocessor or the processor may be any conventional processor or the like.
The Memory 703 may include, but is not limited to, Random Access Memory (RAM), Read Only Memory (ROM), Programmable Read Only Memory (PROM), Erasable Read Only Memory (EPROM), electrically Erasable Read Only Memory (EEPROM), and the like.
It will be appreciated that the configuration shown in fig. 7 is merely illustrative and that electronic device 700 may include more or fewer components than shown in fig. 7 or have a different configuration than shown in fig. 7. The components shown in fig. 7 may be implemented in hardware, software, or a combination thereof. In this embodiment, the electronic device 700 may be, but is not limited to, an entity device such as a desktop, a laptop, a smart phone, an intelligent wearable device, and a vehicle-mounted device, and may also be a virtual device such as a virtual machine. In addition, the electronic device 700 is not necessarily a single device, but may also be a combination of multiple devices, such as a server cluster, and the like.
Embodiments of the present application further provide a computer program product, including a computer program stored on a non-transitory computer readable storage medium, where the computer program includes program instructions, and when the program instructions are executed by a computer, the computer can perform the steps of the index optimization method in the foregoing embodiments, for example, including: acquiring historical index data of a database; generating a recommended index task according to the historical index data; executing the recommended index task, and respectively acquiring first verification data before the recommended index task is executed and second verification data after the recommended index task is executed; the first verification data comprises the overall query performance of the database before the recommended index task is executed, and the second verification data comprises the overall query performance of the database after the recommended index task is executed; determining a gain in overall query performance for the database based on the first validation data and the second validation data; and if the gain of the overall query performance of the database is greater than a preset gain threshold, maintaining the index after the recommended index task is executed so as to realize index optimization of the database.
In the embodiments provided in the present application, it should be understood that the disclosed apparatus and method may be implemented in other ways. The above-described embodiments of the apparatus are merely illustrative, and for example, the division of the units is only one logical division, and there may be other divisions when actually implemented, and for example, a plurality of units or components may be combined or integrated into another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual coupling or direct coupling or communication connection may be an indirect coupling or communication connection of devices or units through some communication interfaces, and may be in an electrical, mechanical or other form.
In addition, units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of the embodiment.
Furthermore, the functional modules in the embodiments of the present application may be integrated together to form an independent part, or each module may exist separately, or two or more modules may be integrated to form an independent part.
In this document, relational terms such as first and second, and the like may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions.
The above description is only an example of the present application and is not intended to limit the scope of the present application, and various modifications and changes may be made by those skilled in the art. Any modification, equivalent replacement, improvement and the like made within the spirit and principle of the present application shall be included in the protection scope of the present application.
Claims (13)
1. An index optimization method, comprising:
acquiring historical index data of a database;
generating a recommended index task according to the historical index data;
executing the recommended index task, and respectively acquiring first verification data before the recommended index task is executed and second verification data after the recommended index task is executed; the first verification data comprises the overall query performance of the database before the recommended index task is executed, and the second verification data comprises the overall query performance of the database after the recommended index task is executed;
determining a gain in overall query performance for the database based on the first validation data and the second validation data;
and if the gain of the overall query performance of the database is greater than a preset gain threshold, maintaining the index after the recommended index task is executed so as to realize index optimization of the database.
2. The index optimization method of claim 1, wherein the historical index data includes index missing data including missing identification information, missing count information, and missing impact evaluation information;
the acquiring of the historical index data of the database comprises:
if the index is missed during query, recording the missing identification information corresponding to the missed index and the missing count information queried by taking the missed index as a target;
obtaining the missing influence evaluation information for characterizing the influence of the missing index on the query.
3. The index optimization method of claim 2, wherein the generating a recommended index task from the historical index data comprises:
and generating a task for increasing the index according to the index missing data.
4. The index optimization method of claim 1, wherein the historical index data includes index hit data, the index hit data including hit identification information, hit count information, and hit impact evaluation information;
the acquiring of the historical index data of the database comprises:
if the index is hit during query, recording the hit identification information corresponding to the hit index and the hit count information for querying by taking the hit index as a target;
and obtaining the hit influence evaluation information for representing the influence of the index of the optimized hit on the query.
5. The index optimization method of claim 4, wherein the generating a recommended index task from the historical index data comprises:
and generating a task for modifying the index according to the index hit data.
6. The index optimization method of any one of claims 2-5, wherein the historical index data includes query performance data corresponding to hardware processing the query;
the acquiring of the historical index data of the database further comprises:
obtaining the query performance data;
and counting the query performance data according to different dimensions, and grouping the counted data according to time to obtain the historical index data.
7. The index optimization method of claim 1, wherein after the generating a recommended index task from the historical index data, the method further comprises:
determining the influence of each recommended index task on the overall query performance of the database;
and screening out recommended index tasks of which the influence on the overall query performance of the database is lower than a preset influence threshold value.
8. The index optimization method of claim 7, wherein after the determining the impact of each recommended indexing task on the overall query performance of the database, the method further comprises:
and screening out recommended index tasks with the difference value of the influences of the overall query performance of the database under different conditions being larger than a preset difference value threshold value.
9. The index optimization method of claim 7 or 8, wherein the determining the influence of each recommended indexing task on the overall query performance of the database comprises:
acquiring the frequency of the indexes corresponding to the recommended index tasks to be queried, the average cost of each query and the cost percentage which can be optimized by the indexes and is evaluated by an optimizer;
and determining the product of the frequency of querying the index corresponding to the recommended index task, the average cost of each query and the percentage of the cost which can be optimized by the index and is evaluated by the optimizer as the influence of each recommended index task on the overall query performance of the database.
10. The index optimization method of claim 1, wherein after the generating a recommended index task from the historical index data, the method further comprises:
and merging the recommended index tasks aiming at the same table in the recommended index tasks.
11. An index optimization apparatus, comprising:
the acquisition module is used for acquiring historical index data of the database;
the generation module is used for generating a recommended index task according to the historical index data;
the execution module is used for executing the recommended index task and respectively acquiring first verification data before the recommended index task is executed and second verification data after the recommended index task is executed; the first verification data comprises the overall query performance of the database before the recommended index task is executed, and the second verification data comprises the overall query performance of the database after the recommended index task is executed;
a first determining module for determining a gain of overall query performance of the database based on the first validation data and the second validation data;
and the optimization module is used for keeping the index after the recommended index task is executed if the gain of the overall query performance of the database is greater than a preset gain threshold value so as to realize index optimization of the database.
12. An electronic device, comprising: a processor, a memory, and a bus;
the processor and the memory are communicated with each other through the bus;
the memory stores program instructions executable by the processor, the processor invoking the program instructions to perform the index optimization method of any of claims 1-10.
13. A non-transitory computer-readable storage medium storing computer instructions which, when executed by a computer, cause the computer to perform the index optimization method of any one of claims 1-10.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011366665.6A CN112559805A (en) | 2020-11-26 | 2020-11-26 | Index optimization method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011366665.6A CN112559805A (en) | 2020-11-26 | 2020-11-26 | Index optimization method and device |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112559805A true CN112559805A (en) | 2021-03-26 |
Family
ID=75046250
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011366665.6A Pending CN112559805A (en) | 2020-11-26 | 2020-11-26 | Index optimization method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112559805A (en) |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP1302870A2 (en) * | 2001-10-12 | 2003-04-16 | NCR International, Inc. | Index selection in a database system |
| US20130060782A1 (en) * | 2011-09-02 | 2013-03-07 | Bbs Technologies, Inc. | Determining indexes for improving database system performance |
| CN107820613A (en) * | 2015-06-26 | 2018-03-20 | 微软技术许可有限责任公司 | Database index is recommended and created automatically |
-
2020
- 2020-11-26 CN CN202011366665.6A patent/CN112559805A/en active Pending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP1302870A2 (en) * | 2001-10-12 | 2003-04-16 | NCR International, Inc. | Index selection in a database system |
| US20130060782A1 (en) * | 2011-09-02 | 2013-03-07 | Bbs Technologies, Inc. | Determining indexes for improving database system performance |
| CN107820613A (en) * | 2015-06-26 | 2018-03-20 | 微软技术许可有限责任公司 | Database index is recommended and created automatically |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110399262B (en) | Operation and maintenance monitoring alarm convergence method and device, computer equipment and storage medium | |
| CN109039819B (en) | Time delay statistical method, device, system and storage medium | |
| CN112311617A (en) | Configured data monitoring and alarming method and system | |
| CN109445768B (en) | Database script generation method and device, computer equipment and storage medium | |
| CN112769612A (en) | Alarm event false alarm removing method and device | |
| CN111737246A (en) | Data fusion method and device, electronic equipment and storage medium | |
| US20150317336A1 (en) | Data reconstruction | |
| CN112860808A (en) | User portrait analysis method, device, medium and equipment based on data tag | |
| CN112559805A (en) | Index optimization method and device | |
| CN111414395A (en) | Data processing method, system and computer equipment | |
| CN107688959B (en) | Breakpoint list processing method, storage medium and server | |
| CN108429632B (en) | Service monitoring method and device | |
| CN114860608A (en) | Scene construction based system automation testing method, device, equipment and medium | |
| CN114399297A (en) | Charging management method, system, electronic equipment and storage medium | |
| CN109885384B (en) | Task parallelism optimization method and device, computer equipment and storage medium | |
| US10558647B1 (en) | High performance data aggregations | |
| CN111475505A (en) | Data acquisition method and equipment | |
| CN116382924B (en) | Recommendation method and device for resource allocation, electronic equipment and storage medium | |
| CN116701337B (en) | Log data processing method and device, electronic equipment and storage medium | |
| CN112115030B (en) | Node determination method and device, electronic equipment and storage medium | |
| CN116433197B (en) | Information reporting method, device, reporting end and storage medium | |
| CN110377592B (en) | Data preprocessing method and device for quantifying variable to virtual variable and terminal equipment | |
| CN112068901A (en) | Configuration information determining method and device | |
| CN114254170A (en) | Data processing method, system, electronic equipment and storage medium | |
| CN111131393A (en) | User activity data statistical method, electronic device and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20210326 |