Method for optimizing database index, storage medium and device

Abstract

The application provides an optimization method of database index, a storage medium and an equipment. The optimization method comprises the following steps: collecting statistical information of total operations on data in a database running period; wherein the total operations comprise query operations and change operations, and the change operations comprise at least one of adding, deleting and modifying operations; matching indexes in the database with the statistical information, and taking the matched indexes as used indexes; calculating positive values brought by the used indexes according to the query operations of the used indexes; calculating negative values brought by the used indexes according to the change operations of the used indexes and / or occupied disk space; obtaining final values of the used indexes according to the positive values and the negative values; and optimizing the used indexes if the final values are less than a preset threshold. The low-value used indexes are accurately found and optimized to improve the performance of the database system.

Description

Technical Field

[0001] This invention relates to database technology, and in particular to a method, storage medium, and device for optimizing database indexes. Background Technology

[0002] Currently, database query performance is a crucial indicator of database efficiency, and index creation is one of the main methods to improve query efficiency. However, indexes can also bring problems. For example, index creation doesn't always improve database system performance; both index creation and maintenance consume database system resources and physical space, and this resource consumption increases with the amount of data. While indexes can improve database query efficiency, maintaining each related index incurs costs during data addition, deletion, and modification operations, reducing the performance of other operations besides queries. Existing index suggestion mechanisms only consider the suggestion generation period. Real-time system statistics such as the number of tuples in a table and disk read costs are used to obtain the current time through cost-based query optimization capabilities, suggesting and applying indexes that offer the highest query performance improvement. However, indexes generated through these suggestions are time-sensitive. When statistical data changes or business adjustments occur, the index may lose its original value and still consume system resources, reducing system performance. Therefore, how to find low-value indexes and optimize them to improve database performance has become an urgent technical problem to be solved. Summary of the Invention

[0003] One objective of this invention is to accurately identify low-value indexes and optimize them to improve the performance of database systems.

[0004] A further objective of this invention is to identify unused indexes and optimize them to improve the performance of the database system.

[0005] Specifically, the present invention provides a method for optimizing database indexes, comprising:

[0006] Collect statistical information on the total operations performed on data during the database's operating cycle; wherein the total operations include query operations and change operations, and the change operations include at least one of add, delete and modify operations;

[0007] The indexes in the database are matched with the statistical information, and the indexes that match successfully are used as the indexes.

[0008] The positive value derived from using the index is calculated based on the query operation that uses the index;

[0009] calculating a positive value brought by the usage index according to the query operation of the usage index and / or a negative value brought by the usage index according to a change operation of the usage index and / or a disk space occupied by the usage index;

[0010] obtaining a final value of the usage index according to the positive value and the negative value;

[0011] optimizing the usage index if the final value is less than a preset threshold.

[0012] Optionally, after the statistics information of the operation on the data in the collection database running period, further comprising:

[0013] finding information generated in the period when the database is not in normal business operation in the statistics information and filtering to obtain remaining statistics information;

[0014] the matching of the index in the database and the statistics information, and the index matched successfully is used as a usage index includes:

[0015] the matching of the index in the database and the remaining statistics information, and the index matched successfully is used as a usage index.

[0016] Optionally, the calculation of the positive value brought by the usage index according to the query operation of the usage index includes:

[0017] calculating a first time length of the query operation without the usage index and a second time length of the query operation with the usage index, obtaining a first difference value of the first time length and the second time length, and obtaining the positive value brought by the usage index in the query operation based on the first difference value;

[0018] the calculation of the negative value brought by the usage index according to the change operation of the usage index and / or the disk space occupied by the usage index includes:

[0019] calculating a third time length of the change operation without the usage index and a fourth time length of the change operation with the usage index, obtaining a second difference value of the third time length and the fourth time length, and obtaining a change operation negative value brought by the usage index in the change operation based on the second difference value;

[0020] obtaining a space negative value of the usage index based on the disk space occupied by the usage index;

[0021] the change operation negative value or the space negative value is used as a negative value; or the change operation negative value and the space negative value are summed to obtain a negative value.

[0022] Optionally, the obtaining of the positive value brought by the usage index in the query operation based on the first difference value includes:

[0023] counting the query times of the usage index under the query operation and the total times under the total operation to obtain a query ratio of the query times to the total times;

[0024] setting a first query weight for the first difference value according to the query ratio;

[0025] obtaining a positive value of the usage index under the query operation according to the first difference value with the first query weight;

[0026] the obtaining of a change operation negative value of the usage index under the change operation based on the second difference value comprises:

[0027] counting the change times of the usage index under the change operation and the total times under the total operation to obtain a change ratio of the change times to the total times;

[0028] setting a first change weight for the second difference value according to the change ratio;

[0029] obtaining a change negative value of the usage index under the change operation according to the second difference value with the first change weight;

[0030] the obtaining of a space negative value of the usage index based on the occupied disk space comprises:

[0031] calculating a space ratio of the occupied disk space to the overall disk space;

[0032] setting a space weight for the occupied disk space according to the space ratio;

[0033] obtaining the space negative value of the usage index according to the occupied disk space with the space weight.

[0034] Optionally, after the statistical information of the data under the total operation in the collection database running period, the method further comprises:

[0035] determining a normal ratio of a normal business running period of the database to the running period;

[0036] the obtaining of the positive value of the usage index under the query operation according to the first difference value with the first query weight comprises:

[0037] setting a second query weight for the first difference value according to the normal ratio;

[0038] obtaining the positive value of the usage index under the query operation according to the first difference value with the first query weight and the second query weight;

[0039] The change negative value of the usage index under the change operation is obtained according to the second difference value with the first change weight.

[0040] The second change weight is set to the second difference value according to the normal proportion;

[0041] The change negative value of the usage index under the change operation is obtained according to the second difference value with the first change weight and the second change weight.

[0042] Optionally, the optimization of the usage index comprises:

[0043] The usage index is inserted into a low value index list;

[0044] At least one of the final value, the total operation times, the positive value, the negative value, the space negative value of the usage index, and a delete statement capable of deleting the usage index are outputted.

[0045] Optionally, the index in the database and the index which fails in matching the statistical information are taken as an unused index;

[0046] The unused index is optimized.

[0047] Optionally, the optimization of the unused index comprises:

[0048] The disk space size occupied by the unused index is outputted, and a delete statement capable of deleting the unused index is outputted.

[0049] According to another aspect of the present application, there is also provided a machine readable storage medium having stored thereon a machine executable program which, when executed by a processor, implements any of the above database index optimization methods.

[0050] According to still another aspect of the present application, there is also provided a computer device comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, and the processor implements any of the above database index optimization methods when executing the machine executable program.

[0051] The optimization method of the database index of the application collects statistical information of total operations on data in a database running period; matches indexes in the database with the statistical information, and takes the matched indexes as used indexes; calculates positive values brought by the used indexes according to query operations of the used indexes; calculates negative values brought by the used indexes according to change operations of the used indexes and / or occupied disk space; obtains final values of the used indexes according to the positive values and the negative values; and optimizes the used indexes if the final values are less than a preset threshold. The application can accurately find low-value indexes in the used indexes and optimize the low-value indexes, realizes automatic analysis and tuning of database performance, reduces manual intervention, and improves the performance of the database. Moreover, even if data of the database system changes or business is adjusted, the scheme is still applicable and has high versatility.

[0052] Further, the database running period can include a normal business running period and an abnormal business running period. The abnormal business running period can be a database system maintenance period. Information generated in the abnormal business running period generally belongs to abnormal information and has very low reference value or even no reference value. Finding information generated in the abnormal business running period in the statistical information and filtering to obtain remaining statistical information, matching indexes in the database with the remaining statistical information, and taking the matched indexes as used indexes can reduce interference of the abnormal information, obtain accurate and effective used indexes, and be beneficial to more accurately judging whether the used indexes are low-value indexes so as to more accurately optimize.

[0053] Further, the more the query times are, the greater the query ratio is, the greater the first query weight set according to the first difference is, and the greater the positive value is. The fewer the change times are, the smaller the change ratio is, the smaller the first change weight set according to the second difference is. The smaller the space proportion is, the smaller the space weight set according to the occupied disk space is. Respective weights can be set for the first difference, the second difference, and the occupied disk space, the actual use of the used indexes can be fully combined, so that the determined low-value indexes can meet the actual situation and the accuracy of judging the low-value indexes is improved.

[0054] The above and other objects, advantages and features of the application will become more apparent from the following detailed description of specific embodiments thereof, taken in conjunction with the accompanying drawings. BRIEF DESCRIPTION OF DRAWINGS

[0055] Some specific embodiments of the present application will be described in detail with reference to the accompanying drawings, which are provided by way of example and without limitation. Like or similar components or parts are designated with the same reference numerals throughout the drawings. It should be understood by those skilled in the art that the drawings are not necessarily to scale. In the drawings:

[0056] Figure 1 is a schematic flowchart of an optimization method of database index according to an embodiment of the present application;

[0057] Figure 2 is a schematic flowchart of an optimization method of database index according to another embodiment of the present application;

[0058] Figure 3 is a schematic diagram of a machine-readable storage medium according to an embodiment of the present application;

[0059] Figure 4 is a schematic diagram of a computer device according to an embodiment of the present application. DETAILED DESCRIPTION

[0060] Figure 1 is a schematic flowchart of an optimization method of database index according to an embodiment of the present application. The optimization method of database index can generally include:

[0061] In step S102, statistics of total operations on data in a database running period are collected; wherein the total operations can include query operations and change operations, and the change operations can include at least one of adding, deleting and modifying operations. Generally, step S102 can be performed once every preset time interval, which can be any value between 1-3 hours. The total operations and resource consumption (such as time consumption) generated by user business can be counted and recorded. Before performing this step, the instance level and SQL level related GUC (Grand Unified Configuration, database configuration parameter) parameters can be opened.

[0062] In step S104, the indexes in the database are matched with the statistics, and the indexes that are successfully matched are used as the using indexes.

[0063] In step S106, the positive value brought by the using indexes is calculated according to the query operations using the indexes.

[0064] In step S108, the negative value brought by the using indexes is calculated according to the change operations using the indexes and / or the occupied disk space. The occupied disk space of the indexes can be obtained according to the table tuple number and the data type.

[0065] In step S110, the final value of the using indexes is obtained according to the positive value and the negative value. Generally, the final value can be obtained by summing the positive value and the negative value.

[0066] In step S112, if the final value is less than the preset threshold, the usage index is optimized. The preset threshold can be set according to actual needs. Generally, if the final value is less than the preset threshold, the usage index can be defined as a low-value index. If the final value is greater than or equal to the preset threshold, the usage index can be defined as a high-value index and does not need to be optimized.

[0067] In the embodiment, statistical information of total operations on data in a database running period is collected; indexes in the database are matched with the statistical information, and indexes that are successfully matched are used as usage indexes; positive values brought by the usage indexes are calculated according to query operations of the usage indexes; negative values brought by the usage indexes are calculated according to change operations and / or occupied disk space of the usage indexes; final values of the usage indexes are obtained according to the positive values and the negative values; and if the final values are less than a preset threshold, the usage indexes are optimized. The application can accurately find low-value indexes in the usage indexes and optimize the low-value indexes, realizes automatic analysis and tuning of database performance, reduces manual intervention, and improves the performance of the database. Moreover, even if data of the database system changes or business is adjusted, the scheme is still applicable and has high versatility.

[0068] In an embodiment of the application, after the statistical information of operations on data in a database running period is collected, the method can further include:

[0069] finding information in the statistical information that is generated in a non-normal business running period of the database and filtering the information to obtain remaining statistical information;

[0070] matching indexes in the database with the statistical information, and using indexes that are successfully matched as usage indexes can include:

[0071] matching indexes in the database with the remaining statistical information, and using indexes that are successfully matched as usage indexes.

[0072] In the embodiment, the database running period can include a normal business running period and a non-normal business running period. The non-normal business running period can be a database system maintenance period and an upgrade period, etc. Information generated in the non-normal business running period generally belongs to non-normal information and has very low reference value or even no reference value. Finding information in the statistical information that is generated in the non-normal business running period of the database and filtering the information to obtain remaining statistical information, and matching indexes in the database with the remaining statistical information, and using indexes that are successfully matched as usage indexes can reduce interference brought by non-normal information, obtain accurate and effective usage indexes, and be beneficial to more accurately judging whether the usage indexes are low-value indexes so as to more accurately optimize.

[0073] In one embodiment of the present application, calculating the positive value brought by the use of the index according to the query operation using the index can include:

[0074] calculating a first duration of the query operation without the use of the index and a second duration of the query operation with the use of the index, obtaining a first difference value of the first duration and the second duration, and obtaining the positive value brought by the use of the index in the query operation based on the first difference value;

[0075] calculating the negative value brought by the use of the index can include:

[0076] calculating a third duration of the change operation without the use of the index and a fourth duration of the change operation with the use of the index, obtaining a second difference value of the third duration and the fourth duration, and obtaining the change operation negative value brought by the use of the index in the change operation based on the second difference value;

[0077] obtaining the space negative value of the use of the index based on the occupied disk space;

[0078] taking the change operation negative value or the space negative value as the negative value; or summing the change operation negative value and the space negative value to obtain the negative value.

[0079] In the embodiment, the creation of the index generally shortens the duration of the query operation, and therefore, the positive value brought by the use of the index in the query operation obtained based on the first difference value is generally more accurate and reliable. However, the creation of the index generally increases the duration of the change operation, and therefore, the change operation negative value brought by the use of the index in the change operation obtained based on the second difference value is generally more accurate and reliable. Moreover, the created index also occupies a certain disk space, and therefore, the space negative value of the use of the index obtained based on the occupied disk space is generally more accurate and reliable. When calculating the durations, the unit can be second or minute, and generally, the unit of second is preferred. When summing the change operation negative value and the space negative value to obtain the negative value, the units of the change operation negative value and the space negative value can be unified according to a preset rule, for example, the unit of the occupied disk space is replaced by the unit of the calculation of the durations.

[0080] In one embodiment of the present application, obtaining the positive value brought by the use of the index in the query operation based on the first difference value can include:

[0081] counting the query times of the use of the index in the query operation and the total times in the total operation, and obtaining a query ratio of the query times and the total times;

[0082] setting a first query weight for the first difference value according to the query ratio (for example, taking the query ratio as the first query weight of the first difference value);

[0083] According to the first difference value with the first query weight, a positive value brought by the using index under the query operation is obtained;

[0084] The negative value brought by the using index under the change operation based on the second difference value can include:

[0085] The change times of the using index under the change operation and the total times under the total operation are counted, and a change ratio of the change times to the total times is obtained;

[0086] The second difference value is set with a first change weight according to the change ratio (for example, the change ratio is taken as the first change weight of the second difference value);

[0087] According to the second difference value with the first change weight, a change negative value brought by the using index under the change operation is obtained;

[0088] The negative value of the using index based on the occupied disk space can include:

[0089] The space ratio of the occupied disk space to the total disk space is calculated;

[0090] The occupied disk space is set with a space weight according to the space ratio (for example, the space ratio is taken as the space weight of the occupied disk space);

[0091] The negative value of the using index according to the occupied disk space with the space weight is obtained.

[0092] In the embodiment, the more the query times are, the larger the query ratio is, and the first query weight set according to the query ratio to the first difference value is larger, and the positive value is larger. The less the change times are, the smaller the change ratio is, and the first change weight set according to the change ratio to the second difference value is smaller. The smaller the space ratio is, the smaller the space weight set according to the space ratio to the occupied disk space is. The first difference value, the second difference value and the occupied disk space can be respectively set with corresponding weights, the actual use of the using index can be fully combined, so that the determined low-value index can be consistent with the actual situation, and the accuracy of the judgment of the low-value index is improved.

[0093] In an embodiment of the present application, after the statistical information of the total operation of the data in the database running period is collected, the method can further include:

[0094] The normal proportion of the normal business running period of the database to the running period is determined;

[0095] The positive value brought by the using index under the query operation according to the first difference value with the first query weight includes:

[0096] setting the second query weight according to the normal proportion of the first difference (for example, taking the normal proportion as the second query weight) ;

[0097] obtaining the positive value brought by the use index under the query operation according to the first difference with the first query weight and the second query weight;

[0098] obtaining the change negative value brought by the use index under the change operation according to the second difference with the first change weight can include:

[0099] setting the second change weight according to the normal proportion of the second difference (for example, taking the normal proportion as the second change weight) ;

[0100] obtaining the change negative value brought by the use index under the change operation according to the second difference with the first change weight and the second change weight.

[0101] In the embodiment, the greater the normal proportion of the normal business operation period in the operation period, the greater the second query weight and the second change weight. Setting the second query weight according to the normal proportion of the first difference and setting the second change weight according to the normal proportion of the second difference can further ensure that the obtained positive value and change negative value conform to the use situation of the use index in practice, so as to ensure that the determined low-value index can conform to the actual situation and improve the accuracy of the judgment of the low-value index.

[0102] In an embodiment of the present application, optimizing the use index can include:

[0103] inserting the use index into the low-value index list;

[0104] outputting at least one of the final value, the total operation times, the positive value, the negative value, and the space negative value of the use index, and a delete statement capable of deleting the use index.

[0105] In the embodiment, inserting the use index into the low-value index list can facilitate the management of the low-value use index. Outputting at least one of the final value, the total operation times, the positive value, the negative value, and the space negative value of the use index, and the delete statement capable of directly deleting the use index can enable the user to fully understand the value situation of the use index and facilitate the execution of the delete statement for optimization.

[0106] In an embodiment of the present application, the index and the statistical information in the database that fail to match are taken as unused indexes;

[0107] optimizing the unused indexes.

[0108] In the embodiment, the unused indexes and the low-value used indexes in the above embodiment are processed separately, so that the indexes in the database can be optimized more reasonably.

[0109] In one embodiment of the application, optimizing the unused indexes can include:

[0110] The occupied disk space size of the unused indexes and the delete statement for deleting the unused indexes are outputted.

[0111] In the embodiment, the occupied disk space size of the unused indexes and the delete statement for deleting the unused indexes are outputted, so that the user can fully understand the value of the unused indexes and can execute the delete statement conveniently.

[0112] Figure 2 is a schematic flowchart of the database index optimization method according to one embodiment of the application. The database index optimization method can include:

[0113] Step S202: Collecting statistical information of total operations on data in a running period of the database. The total operations include query operations and change operations, and the change operations include at least one of adding, deleting and modifying operations.

[0114] Step S204: Determining a normal proportion of a normal business running period of the database in the running period. For example, the running period is 24 hours, the normal business running period is 12 hours, and the normal proportion is 1 / 2.

[0115] Step S206: Finding information generated in the non-normal business running period of the database in the statistical information and filtering to obtain remaining statistical information.

[0116] Step S208: Matching the indexes in the database with the remaining statistical information, and taking the matched indexes as used indexes.

[0117] Step S210: Calculating a first time length of the query operation without the used indexes and a second time length of the query operation with the used indexes to obtain a first difference value of the first time length and the second time length, and calculating a query ratio of a query number in the query operation to a total number in the total operation. For example, the first time length is 200 seconds, the second time length is 100 seconds, the first difference value is 100 seconds, the query number is 60, the total number is 100, and the query ratio is 3 / 5.

[0118] Step S212: setting a first query weight for the first difference value according to the query ratio, setting a second query weight for the first difference value according to the normal ratio, and obtaining a positive value of the use index under the query operation according to the first difference value with the first query weight and the second query weight. For example, the query ratio of 3 / 5 is taken as the first query weight, the normal ratio of 1 / 2 is taken as the second query weight, and the positive value = the first difference value * the first query weight * the second query weight = 100 * (3 / 5) * (1 / 2) = 30.

[0119] Step S214: calculating a third time length of the change operation without the use of the index and a fourth time length of the change operation with the use of the index, obtaining a second difference value of the third time length and the fourth time length, counting the number of changes of the use index under the change operation and the total number of times under the total operation, and obtaining a change ratio of the number of changes and the total number of times. For example, the third time length is 20 seconds, the fourth time length is 100 seconds, and the second difference value is 80 seconds; the number of changes is 40 times, and the change ratio is 2 / 5.

[0120] Step S216: setting a first change weight for the second difference value according to the change ratio, setting a second change weight for the second difference value according to the normal ratio, and obtaining a change negative value of the use index under the change operation according to the second difference value with the first change weight and the second change weight. For example, the change ratio of 2 / 5 is taken as the first change weight, the normal ratio of 1 / 2 is taken as the second change weight, and the change negative value = the second difference value * the first change weight * the second change weight = 80 * (2 / 5) * (1 / 2) = 16.

[0121] Step S218: calculating a space ratio of the occupied disk space and the overall disk space, setting a space weight for the occupied disk space according to the space ratio, and obtaining a space negative value of the use index according to the occupied disk space with the space weight. For example, the occupied disk space is 100 MB, the overall disk space is 8000 MB, the space ratio is 1 / 80, the space ratio is taken as the space weight of the occupied disk space, and the space negative value = the occupied disk space * the space weight = 100 * (1 / 80) = 1.25. The result unit of the space negative value can be replaced into the unit of the change negative value, such as seconds.

[0122] Step S220: taking the change operation negative value or the space negative value as the negative value; or summing the change operation negative value and the space negative value to obtain the negative value. For example, the change operation negative value is taken as the negative value, and the result is 16; or the space negative value is taken as the negative value, and the result is 1.25; or the change operation negative value and the space negative value are summed to obtain the negative value, and the result is 17.25.

[0123] Step S222: obtaining a final value of the usage index according to the positive value and the negative value, and optimizing the usage index if the final value is less than a preset threshold. For example, the final value = the positive value - the negative value = 30 - 16 = 14; or the final value = the positive value - the negative value = 30 - 1.25 = 28.75; or the final value = the positive value - the negative value = 30 - 17.25 = 12.75. The preset threshold is 15. The final value is less than the preset threshold, and the usage index is a low-value index.

[0124] Step S224: taking the index that fails to match the index and the statistical information in the database as an unused index, and optimizing the unused index.

[0125] In the embodiment, the data listed above is only used to explain the logic of the scheme and is not actual data, and does not limit the present application, and different databases can obviously obtain different data. The present application realizes accurate searching of low-value usage indexes and unused indexes, and respectively optimizes the indexes, thereby obviously improving the performance of the database.

[0126] The various embodiments above can be combined arbitrarily, and according to any one of the above preferred embodiments or a combination of the preferred embodiments, the embodiment of the present application can achieve the following beneficial effects:

[0127] The database index optimization method of the present application collects statistical information of total operations on data in a database running period; matches the index and the statistical information in the database, takes the index that matches successfully as a usage index; calculates a positive value brought by the usage index according to a query operation of the usage index; calculates a negative value brought by the usage index according to a change operation and / or occupied disk space of the usage index; obtains a final value of the usage index according to the positive value and the negative value; and optimizes the usage index if the final value is less than a preset threshold. The present application can accurately find low-value indexes in the usage index, and optimize the low-value indexes, thereby improving the performance of the database.

[0128] The embodiment further provides a machine readable storage medium and a computer device. Figure 3 Fig. 8 is a schematic diagram of a machine readable storage medium 830 according to an embodiment of the present application; Figure 4 Fig. 9 is a schematic diagram of a computer device 900 according to an embodiment of the present application. The machine readable storage medium 830 has a machine executable program 840 stored thereon, and the machine executable program 840 is executed by a processor to realize the database index optimization method of any one of the above embodiments.

[0129] The computer device 900 can include a memory 920, a processor 910, and a machine executable program 840 stored on the memory 920 and running on the processor 910, and the processor 910 implements the database index optimization method of any of the above embodiments when executing the machine executable program 840.

[0130] It should be noted that the logic and / or steps represented in the flowcharts and / or described herein, for example, can be considered as a list of executable instructions for implementing logic functions, and can be embodied in any machine readable storage medium for use by or in connection with an instruction execution system, apparatus or device, such as a computer-based system, a processor-based system, or other system that can fetch the instructions from the instruction execution system, apparatus or device and execute the instructions, or in conjunction with such an instruction execution system, apparatus or device.

[0131] For the description of the present embodiment, the machine readable storage medium 830 can be any device that can contain, store, communicate, propagate or transport the program for use by or in connection with the instruction execution system, apparatus or device, or in conjunction with such an instruction execution system, apparatus or device. More specific examples (non-exhaustive list) of computer readable medium include the following: electrical connections having one or more wires (electronic devices), portable computer diskette (magnetic devices), random access memory (RAM), read only memory (ROM), erasable programmable read only memory (EPROM or flash memory), fiber optic devices, and portable compact disk read only memory (CDROM). In addition, the computer readable medium 40 can even be paper or other suitable medium on which the program can be printed, as the program can be electronically obtained, for example, by optical scanning of the paper or other medium, followed by electronic conversion of the optical scanning into an electronic format, and then editing, interpreting or otherwise processing the program into a suitable format for storage in a computer memory.

[0132] It should be understood that parts of the present application can be implemented in hardware, software, firmware or a combination thereof. In the above described embodiments, a plurality of steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0133] The computer device 900 can be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smartphone. In some examples, the computer device 900 can be a cloud computing node. The computer device 900 can be described in the general context of computer system-executable instructions, such as program modules, being executed by a computer system. Generally, program modules can include routines, programs, objects, components, logic, data structures, and so on that perform particular tasks or implement particular abstract data types. The computer device 900 can be practiced in distributed cloud computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed cloud computing environment, program modules can be located in local or remote computer system storage media including memory storage devices.

[0134] The computer device 900 can include a processor 910 adapted to execute instructions stored in memory 920, which in operation provides temporary storage for the instructions during execution. The processor 910 can be a single threaded processor, a multi-threaded processor, a computing cluster, or any number of other configurations. The memory 920 can include random access memory (RAM), read only memory (ROM), flash memory, or any other suitable memory systems.

[0135] The processor 910 can be connected through a system interconnect (e.g., a PCI, a PCI-Express, etc.) to an I / O interface adapted to connect the computer device 900 to one or more I / O devices (input / output devices). The I / O devices can include, for example, a keyboard and a pointing device, where the pointing device can include a touchpad or a touchscreen, among others. The I / O devices can be built-in components of the computer device 900, or can be devices externally connected to the computing device.

[0136] The processor 910 can also be linked through the system interconnect to a display interface adapted to connect the computer device 900 to a display device. The display device can include a display screen that is a built-in component of the computer device 900. The display device can also include a computer monitor, a television, or a projector, among others, that is externally connected to the computer device 900. Further, a network interface controller (NIC) can be adapted to connect the computer device 900 to a network through the system interconnect. In some embodiments, the NIC can use any suitable interface or protocol (such as an Internet Small Computer Systems Interface, among others) to transfer data. The network can be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, among others. Remote devices can be connected to the computing device through the network.

[0137] The flowcharts provided by the embodiments are not intended to indicate that the operations of the methods will be executed in any particular order, or that all of the operations of the methods are included in every case. In addition, the methods can include additional operations. Additional changes can be made to the above-described methods within the scope of the technical ideas provided by the embodiments.

[0138] Thus, those skilled in the art will recognize that the present application has been well- described and clearly demonstrated by way of example, and that numerous modifications, substitutions and changes can be made thereto without departing from the spirit and scope of the present application as set forth above. It is intended that the scope of the present application be limited solely by the scope of the appended claims.

Claims

1. A method for optimizing database indexes, comprising: collecting statistics of total operations on data in a database running period; wherein the total operations comprise query operations and change operations, and the change operations comprise at least one of add, delete and modify operations; matching indexes in the database with the statistics, and taking indexes matched successfully as used indexes; calculating positive values brought by the used indexes according to the query operations of the used indexes; calculating negative values brought by the used indexes according to the change operations of the used indexes and / or disk space occupied by the used indexes; obtaining final values of the used indexes according to the positive values and the negative values; optimizing the used indexes if the final values are less than a preset threshold; the calculating of the positive values brought by the used indexes according to the query operations of the used indexes comprises: calculating a first time length of the query operations without the used indexes and a second time length of the query operations with the used indexes, obtaining a first difference value of the first time length and the second time length, and obtaining the positive values brought by the used indexes in the query operations based on the first difference value; the calculating of the negative values brought by the used indexes according to the change operations of the used indexes and / or the disk space occupied by the used indexes comprises: calculating a third time length of the change operations without the used indexes and a fourth time length of the change operations with the used indexes, obtaining a second difference value of the third time length and the fourth time length, and obtaining change operation negative values brought by the used indexes in the change operations based on the second difference value; obtaining space negative values of the used indexes based on the disk space occupied by the used indexes; taking the change operation negative values or the space negative values as the negative values, or summing the change operation negative values and the space negative values to obtain the negative values. 2.The method of claim 1, wherein, after the collecting of the statistics of the total operations on data in the database running period, the method further comprises: finding information in the statistics generated in a non-normal business running period of the database and filtering to obtain remaining statistics; the matching of the indexes in the database with the statistics, and taking indexes matched successfully as used indexes comprises: matching the indexes in the database with the remaining statistics, and taking indexes matched successfully as used indexes. 3.The method of claim 1, wherein, the obtaining of the positive values brought by the used indexes in the query operations based on the first difference value comprises: counting a query times of the used indexes in the query operations and a total times in the total operations to obtain a query ratio of the query times to the total times; setting a first query weight to the first difference value according to the query ratio; and obtaining the positive values brought by the used indexes in the query operations based on the first difference value with the first query weight; the obtaining of the change operation negative values brought by the used indexes in the change operations based on the second difference value comprises: ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ counting a change number of the usage index under the change operation and a total number of the usage index under the total operation to obtain a change ratio of the change number to the total number; setting a first change weight for the second difference according to the change ratio; obtaining a change negative value of the usage index under the change operation according to the second difference with the first change weight; the obtaining the space negative value of the usage index based on the occupied disk space comprises: calculating a space ratio of the occupied disk space to a total disk space; setting a space weight for the occupied disk space according to the space ratio; obtaining the space negative value of the usage index according to the occupied disk space with the space weight.

4. The database index optimization method of claim 3, wherein, after the counting the statistical information of the data under the total operation in the collection database running period, further comprising: determining a normal ratio of a normal business running period of the database to the running period; the obtaining a positive value of the usage index under the query operation according to the first difference with the first query weight comprises: setting a second query weight for the first difference according to the normal ratio; obtaining the positive value of the usage index under the query operation according to the first difference with the first query weight and the second query weight; the obtaining a change negative value of the usage index under the change operation according to the second difference with the first change weight comprises: setting a second change weight for the second difference according to the normal ratio; obtaining the change negative value of the usage index under the change operation according to the second difference with the first change weight and the second change weight.

5. The database index optimization method of claim 4, wherein, the optimizing the usage index comprises: inserting the usage index into a low value index list; outputting at least one of the final value, the total operation number, the positive value, the negative value, the space negative value of the usage index, and a delete statement capable of deleting the usage index.

6. The database index optimization method of claim 1, wherein, matching the indexes in the database with the statistical information fails to obtain unused indexes; optimizing the unused indexes.

7. The database index optimization method of claim 6, wherein, the optimizing the unused indexes comprises: outputting an occupied disk space size of the unused indexes, and a delete statement capable of deleting the unused indexes.

8. A machine readable storage medium having stored thereon a machine executable program, the machine executable program, when executed by a processor, implements the database index optimization method according to any one of claims 1 to 7.

9. A computer device comprising a memory, a processor, and a machine executable program stored on the memory and running on the processor, and the processor, when executing the machine executable program, implements the database index optimization method according to any one of claims 1 to 7.

Images