A method and device for database index optimization suggestion and verification

Abstract

The present invention discloses a method and device for database index optimization suggestion and verification, and relates to the field of database technology. A specific implementation of the method includes: generating simulation data according to a predetermined rule; applying a candidate index to the simulation data; executing a structured SQL statement on the simulation data to obtain a first performance parameter set; executing the structured SQL statement on the indexed simulation data to obtain a second performance parameter set; and comparing the first performance parameter set and the second performance parameter set to obtain a comparison result. This implementation overcomes the problem of affecting business when verifying optimization results of an online database.

Description

Technical Field

[0001] The present invention relates to the field of databases, and in particular to a method and device for database index optimization suggestion and verification. Background Art

[0002] An index is a structure that sorts the values ​​of one or more columns in a database table. Using an index, you can quickly access specific information in a database table. A good database index design can greatly improve data query performance, reduce user waiting time, and improve user experience.

[0003] Currently, for most scenarios, database indexes are mainly designed manually, which requires certain design principles and design experience. Since this method relies on the designer's experience, the performance of databases designed by different designers may vary greatly.

[0004] In the process of implementing the present invention, the inventors found that there are at least the following problems in the prior art:

[0005] (1) The optimization results are directly verified and applied to the online database, which may have an impact on the business.

[0006] (2) The SQL statements used for detection come from a single source.

[0007] (3) It is necessary to provide optimization solutions based on the large number of SQL statements executed by the database. This requires the system to run for a certain period of time before automatic index optimization can be performed. For systems in the design stage or initial operation, it is impossible or ineffective to provide optimization solutions.

[0008] (4) The source of metadata is single, mainly extracted from database metadata, which affects index optimization.

[0009] (5) The recommended index type is single, mainly BTree type index. Summary of the invention

[0010] In view of this, an embodiment of the present invention provides a method and device for database index optimization suggestion and verification, which can use a simulated data verification mechanism to complete database index optimization suggestion and verification, significantly reducing the impact of traditional verification methods on online databases, and further enhancing the effect of database index optimization by increasing the sources of SQL and metadata, which helps to maintain a smoother and more efficient operation of the online system.

[0011] To achieve the above objective, according to one aspect of an embodiment of the present invention, a method for database index optimization suggestion and verification is provided.

[0012] A method for database index optimization suggestion and verification according to an embodiment of the present invention includes:

[0013] Generate simulation data according to predetermined rules;

[0014] applying a candidate index to the simulated data;

[0015] Executing a structured SQL statement on the simulation data to obtain a first performance parameter set;

[0016] executing the structured SQL statement on the indexed simulation data to obtain a second performance parameter set; and

[0017] The first performance parameter set and the second performance parameter set are compared to obtain a comparison result.

[0018] Optionally, before executing the structured SQL statement on the simulation data, the method further includes:

[0019] The structured SQL statement is obtained by structuring one or more of the following: SQL execution log, database slow SQL log, MyBatis file, and custom SQL file.

[0020] Optionally, before applying the candidate index to the simulated data, the method further includes:

[0021] The candidate index is calculated based on the metadata and the structured SQL statement, wherein the candidate index types include at least BTree index, hash index and bitmap index.

[0022] Optionally, the predetermined rule includes:

[0023] Use the original data as the simulation data, or

[0024] The simulation data is generated according to the metadata of the original data.

[0025] Optionally, the metadata is obtained by one or more of the following steps:

[0026] Extract rules from the database where the original data is located;

[0027] Extracting the rules from a database design document;

[0028] Extracting the rules from existing data; or

[0029] The rules are checked and supplemented by the user.

[0030] Optionally, after obtaining the comparison result, the method further includes:

[0031] generating index optimization suggestions and verification results according to the comparison results; and

[0032] Output the generated index optimization suggestions and verification results.

[0033] According to another aspect of an embodiment of the present invention, a device for database index optimization suggestion and verification is provided.

[0034] The device for database index optimization suggestion and verification according to an embodiment of the present invention includes:

[0035] a simulation data processing module, configured to generate simulation data according to a predetermined rule and apply a candidate index to the simulation data; and

[0036] An index verification module is used to execute a structured SQL statement on the simulation data to obtain a first performance parameter set, execute the structured SQL statement on the indexed simulation data to obtain a second performance parameter set, and compare the first performance parameter set with the second performance parameter set to obtain a comparison result.

[0037] Optionally, further comprising:

[0038] The SQL processing module is used to obtain the structured SQL statement by structuring one or more of the following: SQL execution log, database slow SQL log, MyBatis file and custom SQL file.

[0039] Optionally, further comprising:

[0040] An index calculation module is used to calculate the candidate index according to the metadata and the structured SQL statement, wherein the candidate index types include at least BTree index, hash index and bitmap index.

[0041] Optionally, the simulation data processing module is further used for:

[0042] Use the original data as the simulation data, or

[0043] The simulation data is generated according to the metadata of the original data.

[0044] Optionally, further comprising:

[0045] The metadata module is used to obtain the metadata by one or more of the following steps:

[0046] Extract rules from the database where the original data is located;

[0047] Extracting the rules from a database design document;

[0048] Extracting the rules from existing data; or

[0049] The rules are checked and supplemented by the user.

[0050] Optionally, further comprising:

[0051] The result display module is used to generate index optimization suggestions and verification results according to the comparison results; and output the generated index optimization suggestions and verification results.

[0052] According to another aspect of an embodiment of the present invention, an electronic device for database index optimization suggestion and verification is provided.

[0053] An electronic device for database index optimization suggestion and verification according to an embodiment of the present invention includes:

[0054] one or more processors;

[0055] a storage device for storing one or more programs,

[0056] When the one or more programs are executed by the one or more processors, the one or more processors implement the method for database index optimization suggestion and verification provided by the first aspect of the embodiment of the present invention.

[0057] According to still another aspect of an embodiment of the present invention, a computer readable medium is provided.

[0058] The computer-readable medium according to the embodiment of the present invention stores a computer program, which, when executed by a processor, implements the method for database index optimization suggestion and verification provided by the first aspect of the embodiment of the present invention.

[0059] An embodiment of the above invention has the following advantages or beneficial effects: by creating a simulated database and using the created simulated database to verify the database index, the problem of affecting the business when the online database verification optimization result is overcame; using multiple SQL input sources, so that the system in the design stage and initial operation can also optimize the database index; expanding the metadata source to provide a basis for database index optimization; and automatically determining the optimal index type based on the extracted metadata features, so it is no longer limited to a single type of index.

[0060] The further effects of the above-mentioned non-conventional optional manner will be described below in conjunction with the specific implementation manner. BRIEF DESCRIPTION OF THE DRAWINGS

[0061] The accompanying drawings are used to better understand the present invention and do not constitute an improper limitation of the present invention.

[0062] Figure 1 is a schematic diagram of the main process of the method for database index optimization suggestion and verification according to an embodiment of the present invention;

[0063] Figure 2 is a flow chart of an algorithm for calculating candidate indexes according to an embodiment of the present invention;

[0064] Figure 3 is a flow chart of an algorithm for calculating a single content index according to an embodiment of the present invention;

[0065] Figure 4 is a schematic diagram of the main process of another method for database index optimization suggestion and verification according to an embodiment of the present invention;

[0066] Figure 5 is a schematic diagram of main modules of a device for database index optimization suggestion and verification according to an embodiment of the present invention;

[0067] Figure 6 is an exemplary system architecture diagram to which embodiments of the present invention may be applied;

[0068] Figure 7 It is a schematic diagram of the structure of a computer system of a terminal device or a server suitable for implementing an embodiment of the present invention. DETAILED DESCRIPTION

[0069] The following is a description of exemplary embodiments of the present invention in conjunction with the accompanying drawings, including various details of the embodiments of the present invention to facilitate understanding, which should be considered as merely exemplary. Therefore, it should be recognized by those of ordinary skill in the art that various changes and modifications may be made to the embodiments described herein without departing from the scope and spirit of the present invention. Similarly, for clarity and conciseness, the description of well-known functions and structures is omitted in the following description.

[0070] Figure 1 FIG. 1 is a schematic diagram of the main process of the method for database index optimization suggestion and verification according to an embodiment of the present invention. Figure 1 As shown, the method for database index optimization suggestion and verification according to an embodiment of the present invention includes steps S101, S102, S103, S104 and S105.

[0071] Step S101: Generate simulation data according to predetermined rules.

[0072] In order to make optimization suggestions and verify the database index, a simulation database is first established and simulation data is generated in the simulation database, and the simulation data is used to detect the actual effect of the index.

[0073] Preferably, the predetermined rule includes using original data as the simulated data, or generating the simulated data according to metadata of the original data. In practical applications, you can choose to copy data from the source database to the simulated database, or you can choose to generate simulated data according to metadata rules. Use the simulated database to verify that the index optimization solution has no impact on the online database.

[0074] Furthermore, the metadata is obtained by one or more of the following steps: extracting rules from a database where the original data is located; extracting the rules from a database design file; extracting the rules from existing data; or verifying and supplementing the rules by a user. These steps of extracting metadata can be performed in a certain order or simultaneously.

[0075] In the method described in this embodiment, the metadata mainly plays the following roles:

[0076] (1) A basis for index optimization. For example, if a field is of varchar type, when performing index optimization, the field type is used to determine that the index is a string, and thus it can be inferred that the first n characters can be indexed, because the first few characters may be able to distinguish most of the data, so there is no need to index all the characters. Such an index reduces storage space usage and is more efficient when performing index retrieval.

[0077] (2) The basis for generating simulated data. For example, if the metadata shows that a field is of varchar type and has a length of 20, the metadata attributes can be extended by user-specified methods, such as the first two characters are JD, the middle eight characters are the date YYYYMMDD, and the last 10 characters are a serial number of the day. Based on these attributes, data such as JD201807010000654321 can be generated.

[0078] From a business perspective, when querying a table, there will definitely be conditions such as col_a=? and col_b=?. However, the metadata obtained from the database itself will not have such rules. It can only obtain the data type, length, and precision of col_a and col_b. It can be supplemented by other methods to have a certain correlation between col_a and col_b. Then, based on data analysis, it can be concluded that col_b has a high degree of data differentiation (for example, there are 10,000 total data in the table, and col_b has 7,000 unique values ​​after excluding duplicate values, and col_a has 2,000 unique values ​​after excluding duplicate values). In this case, a joint index such as col_b and col_a can be established.

[0079] Therefore, the embodiment provided by the present invention collects metadata related to the database table from multiple locations to provide input for the index calculation module and the simulation data generation module. Collecting metadata related to the database table from multiple locations can provide input for the index calculation module and the simulation data generation module.

[0080] In some implementations, the metadata of the table is as shown in Table 1:

[0081] Metadata illustrate Example Table name tab_order Total number of fields 12 Total amount of data Current data volume 100000 Total amount of simulation data The total amount of data simulated during verification 1,000,000

[0082] Table 1

[0083] In some implementations, the metadata of each field is as shown in Table 2:

[0084]

[0085] Table 2

[0086] Step S102: applying candidate indexes to the simulation data.

[0087] Preferably, before applying the candidate index to the simulated data, the method further includes calculating the candidate index according to metadata and a structured SQL statement, wherein the candidate index types include at least a BTree index, a hash index and a bitmap index.

[0088] refer to Figure 2 , is a flowchart of an algorithm for calculating candidate indexes according to an embodiment of the present invention, such as Figure 2 As shown, the algorithm for calculating candidate indexes according to an embodiment of the present invention includes steps S201, S202, S203, S204 and S205.

[0089] Step S201: extracting conditions, Group By, and Order By from structured data.

[0090] Step S202: perform index calculation on each content item.

[0091] The algorithm for calculating the single content index will be combined below Figure 3 Give a description.

[0092] Step S203: Index optimization and merging.

[0093] For example, the result obtained in step S202 is as follows (ie, candidate index):

[0094] (1)col_a

[0095] (2)col_a,col_b

[0096] When index optimization and merging are performed, the index of the single field (1) col_a can be excluded because the condition (2) col_a, col_b for col_a = ? is still valid.

[0097] For example:

[0098] (1)col_a

[0099] (2)col_a,col_b

[0100] (3)col_b

[0101] When optimizing and merging indexes, you need to select the two indexes (1) col_a and (3) col_b, and exclude the indexes (2) col_a and col_b.

[0102] Step S204: sorting according to the index function.

[0103] The index is used to filter the size of data, for example.

[0104] Step S205: extract the first N indexes.

[0105] refer to Figure 3 , is a flowchart of an algorithm for calculating a single content index according to an embodiment of the present invention, such as Figure 3 As shown, the algorithm for calculating the candidate index according to the embodiment of the present invention includes steps S301, S302, S303, S304, S305, S306, S307, S308, S309, S310 and S311.

[0106] Step S301: Input SQL conditions.

[0107] Step S302: Determine whether index optimization can be performed?

[0108] Step S303: Determine whether the data discrimination (proportion of unique values) is high or low?

[0109] Step S304: Determine whether the database supports bitmap indexes?

[0110] Step S305: Get the bitmap index.

[0111] Step S306: Add other fields according to the query conditions to determine whether a composite index can be created.

[0112] Step S307: Determine whether there is only an equal comparison and it does not appear in Order By?

[0113] Step S308: Obtain a hash index.

[0114] Step S309: Obtain a Btree index.

[0115] Step S310: Is the field a string type?

[0116] Step S311: Determine the field length for creating an index based on metadata.

[0117] In some embodiments, the calculation results formed by calculation are shown in Table 3:

[0118] Index Type Fields The length of the data to be indexed Hash Index order_no 12 BTree Index submit_time BTree Index buyer_id,status

[0119] Table 3

[0120] The system automatically determines the optimal index type (BTree index, hash index, bitmap index, etc.) based on the extracted metadata features and is no longer limited to BTree type indexes.

[0121] Step S103: executing a structured SQL statement on the simulation data to obtain a first performance parameter set.

[0122] Preferably, before executing the structured SQL statement on the simulation data, the method further includes obtaining the structured SQL statement by structuring one or more of the following: SQL execution log, database slow SQL log, MyBatis file and custom SQL file.

[0123] Previous statements are historical records of statements executed in the database. The specific technical implementations are: for databases with audit functions, the audit function can be enabled, and the select statements executed on the database can also be recorded and extracted from the system table or specific log file; write a specific program to intercept the statements executed by the database and extract the required statements; write a specific program to extract from the log file generated by the program.

[0124] In the existing technology, the automatic index optimization operation can only be performed after the system has been running for a certain period of time. For the system in the design stage or initial operation, it is impossible or not very good to provide an optimization plan. This is because the existing optimization plan generally obtains historically executed SQL, that is, the SQL source is single as mentioned above. For example, during the system design stage and the initial operation, there is no historical SQL or the amount is relatively small, so there is no sufficient basis for optimization.

[0125] In order to solve the problem of single source of SQL statements in the prior art, SQL statements from multiple sources can be used, and through data format conversion, they can be parsed into recognizable SQL statements in a unified format, and the key features of SQL queries can be extracted from them. In other words, the source of SQL statements is not only extracted from the executed SQL, but also from program logs, slow SQL logs, MyBatis files, and custom files. Based on multiple SQL input sources, optimization suggestions for database indexes can be made, so that systems in the design phase and initial operation can also optimize database indexes. With multiple sources, analysis can be performed from multiple angles, mutual verification can be carried out, and better optimization effects can be achieved.

[0126] In some implementations, SQL statements can be extracted from SQL execution logs, database slow SQL logs, MyBatis files, but are not limited to the data sources. This module provides a unified data interface, and users can customize data sources and parsing programs according to the interface standards.

[0127] For the parsing process, the textual SQL statement is parsed into a structured and recognizable data structure. For example, the SQL statement is:

[0128]

[0129] After parsing, structured data is obtained, that is, structured SQL statements.

[0130] Step S104: Execute the structured SQL statement on the indexed simulation data to obtain a second performance parameter set.

[0131] Performance mainly refers to database performance, for example, the execution time of a query.

[0132] In some embodiments, the data recorded in the first performance parameter set and the second performance parameter set may include the items shown in Table 4:

[0133]

[0134]

[0135] Table 4

[0136] Step S105: Compare the first performance parameter set and the second performance parameter set to obtain a comparison result.

[0137] This step is used to verify the effect of the index. First, at step S103, when no index is created for the simulated data, the SQL statement is executed and the relevant data (e.g., the first performance parameter set) is recorded. Then, at step S104, the simulated data is indexed and the SQL statement is executed on the indexed simulated data and the relevant data (e.g., the second performance parameter set) is recorded. The two execution results are compared.

[0138] Optionally, after obtaining the comparison result, the method further includes generating index optimization suggestions and verification results according to the comparison result; and outputting the generated index optimization suggestions and verification results. The output results may be in any form, and the main purpose is to display the index performance results obtained by the test to the user.

[0139] The above-mentioned embodiments have the following advantages or beneficial effects: by creating a simulated database and using the created simulated database to verify the database index, the problem of affecting the business when verifying the optimization result of the online database is overcome; by using multiple SQL input sources, the system in the design stage and initial operation can also optimize the database index; the metadata source is expanded to provide a basis for database index optimization; and the optimal index type is automatically determined based on the extracted metadata features, so it is no longer limited to a single type of index.

[0140] Figure 4 FIG. 1 is a schematic diagram of the main process of another method for database index optimization suggestion and verification according to an embodiment of the present invention. Figure 4 As shown, another method for database index optimization suggestion and verification according to an embodiment of the present invention includes steps S401, S402, S403, S404, S405, S406, S407, S408 and S409.

[0141] Step S401: The structured SQL statement is obtained by structuring one or more of the following: SQL execution log, database slow SQL log, MyBatis file, and custom SQL file.

[0142] Step S402: The metadata is obtained by one or more of the following steps: extracting rules from a database where the original data is located; extracting the rules from a database design file; extracting the rules from existing data; or verifying and supplementing the rules by a user.

[0143] Step S403: Calculate the candidate index according to the metadata and the structured SQL statement, wherein the candidate index types include at least BTree index, hash index and bitmap index.

[0144] Step S404: Generate simulation data according to predetermined rules.

[0145] Preferably, the predetermined rule includes using original data as the simulation data, or generating the simulation data according to metadata of the original data.

[0146] Step S405: applying candidate indexes to the simulation data.

[0147] Step S406: Execute a structured SQL statement on the simulation data to obtain a first performance parameter set.

[0148] Step S407: Execute the structured SQL statement on the indexed simulation data to obtain a second performance parameter set.

[0149] Step S408: Compare the first performance parameter set and the second performance parameter set to obtain a comparison result.

[0150] Step S409: generating index optimization suggestions and verification results according to the comparison results; and outputting the generated index optimization suggestions and verification results.

[0151] The above-mentioned embodiments have the following advantages or beneficial effects: by creating a simulated database and using the created simulated database to verify the database index, the problem of affecting the business when verifying the optimization result of the online database is overcome; by using multiple SQL input sources, the system in the design stage and initial operation can also optimize the database index; the metadata source is expanded to provide a basis for database index optimization; and the optimal index type is automatically determined based on the extracted metadata features, so it is no longer limited to a single type of index.

[0152] Figure 5 Schematic diagram of the main modules of the device for database index optimization suggestion and verification according to an embodiment of the present invention. Figure 5 As shown, the device 500 for database index optimization suggestion and verification according to an embodiment of the present invention includes:

[0153] A simulation data processing module 501, configured to generate simulation data according to a predetermined rule and apply a candidate index to the simulation data; and

[0154] The index verification module 502 is used to execute the structured SQL statement on the simulation data to obtain a first performance parameter set, execute the structured SQL statement on the indexed simulation data to obtain a second performance parameter set, and compare the first performance parameter set and the second performance parameter set to obtain a comparison result.

[0155] Optionally, the database index optimization suggestion and verification device 500 further includes:

[0156] SQL processing module 503, used to obtain the structured SQL statement by structuring one or more of the following: SQL execution log, database slow SQL log, MyBatis file and custom SQL file

[0157] The index calculation module 504 is used to calculate the candidate index according to the metadata and the structured SQL statement, wherein the candidate index types include at least BTree index, hash index and bitmap index.

[0158] Optionally, the simulation data processing module 501 is further used for:

[0159] Use the original data as the simulation data, or

[0160] The simulation data is generated according to the metadata of the original data.

[0161] Optionally, the database index optimization suggestion and verification device 500 further includes:

[0162] The metadata module 505 is used to obtain the metadata by one or more of the following steps:

[0163] Extract rules from the database where the original data is located;

[0164] Extracting the rules from a database design document;

[0165] Extracting the rules from existing data; or

[0166] The rules are checked and supplemented by the user.

[0167] Optionally, the database index optimization suggestion and verification device 500 further includes:

[0168] The result display module 506 is used to generate index optimization suggestions and verification results according to the comparison results; and output the generated index optimization suggestions and verification results.

[0169] The above-mentioned embodiments have the following advantages or beneficial effects: by creating a simulated database and using the created simulated database to verify the database index, the problem of affecting the business when verifying the optimization result of the online database is overcome; by using multiple SQL input sources, the system in the design stage and initial operation can also optimize the database index; the metadata source is expanded to provide a basis for database index optimization; and the optimal index type is automatically determined based on the extracted metadata features, so it is no longer limited to a single type of index.

[0170] Figure 6An exemplary system architecture 600 is shown to which the method for database index optimization suggestion and verification or the apparatus for database index optimization suggestion and verification according to the embodiments of the present invention may be applied.

[0171] like Figure 6 As shown, system architecture 600 may include terminal devices 601, 602, 603, network 604 and server 605. Network 604 is used to provide a medium for communication links between terminal devices 601, 602, 603 and server 605. Network 604 may include various connection types, such as wired, wireless communication links or optical fiber cables, etc.

[0172] Users can use terminal devices 601, 602, and 603 to interact with server 605 through network 604 to receive or send messages, etc. Various communication client applications can be installed on terminal devices 601, 602, and 603, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, etc. (only examples).

[0173] The terminal devices 601 , 602 , and 603 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, and desktop computers, etc.

[0174] The server 605 may be a server that provides various services, such as a backend management server (only an example) that provides support for shopping websites browsed by users using the terminal devices 601, 602, and 603. The backend management server may analyze and process the received data such as product information query requests, and feed back the processing results (such as target push information, product information - only an example) to the terminal device.

[0175] It should be noted that the method for database index optimization suggestion and verification provided in the embodiment of the present invention is generally executed by the server 605 , and accordingly, the device for database index optimization suggestion and verification is generally set in the server 605 .

[0176] It should be understood that Figure 6 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to implementation requirements.

[0177] Reference below Figure 7 , which shows a schematic diagram of the structure of a computer system 700 of a terminal device suitable for implementing an embodiment of the present invention. Figure 7 The terminal device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present invention.

[0178] like Figure 7 As shown, the computer system 700 includes a central processing unit (CPU) 701, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 702 or a program loaded from a storage part 708 into a random access memory (RAM) 703. In the RAM 703, various programs and data required for the operation of the system 700 are also stored. The CPU 701, the ROM 702, and the RAM 703 are connected to each other via a bus 704. An input / output (I / O) interface 705 is also connected to the bus 704.

[0179] The following components are connected to the I / O interface 705: an input section 706 including a keyboard, a mouse, etc.; an output section 707 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 708 including a hard disk, etc.; and a communication section 709 including a network interface card such as a LAN card, a modem, etc. The communication section 709 performs communication processing via a network such as the Internet. A drive 710 is also connected to the I / O interface 705 as needed. A removable medium 711, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 710 as needed, so that a computer program read therefrom is installed into the storage section 708 as needed.

[0180] In particular, according to the embodiments disclosed in the present invention, the process described above with reference to the flowchart can be implemented as a computer software program. For example, the embodiments disclosed in the present invention include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from the network through the communication part 709, and / or installed from the removable medium 711. When the computer program is executed by the central processing unit (CPU) 701, the above-mentioned functions defined in the system of the present invention are executed.

[0181] It should be noted that the computer-readable medium shown in the present invention may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present invention, a computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in combination with an instruction execution system, device or device. In the present invention, a computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, which carries a computer-readable program code. This propagated data signal may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. Computer-readable signal media may also be any computer-readable medium other than computer-readable storage media, which may send, propagate or transmit a program for use by or in conjunction with an instruction execution system, apparatus or device. The program code contained on the computer-readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, optical cable, RF, etc., or any suitable combination of the above.

[0182] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present invention. In this regard, each box in the flow chart or block diagram can represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0183] The modules involved in the embodiments of the present invention may be implemented in software or hardware. The modules described may also be set in a processor, for example, may be described as: a processor includes a simulation data processing module and an index verification module. The names of these modules do not constitute a limitation on the modules themselves in some cases, for example, the simulation data processing module may also be described as "a module for generating simulation data according to predetermined rules and applying candidate indexes to the simulation data".

[0184] As another aspect, the present invention further provides a computer-readable medium, which may be included in the device described in the above embodiment; or may exist independently without being assembled into the device. The above computer-readable medium carries one or more programs, and when the above one or more programs are executed by a device, the device includes: generating simulation data according to a predetermined rule; applying a candidate index to the simulation data; executing a structured SQL statement on the simulation data to obtain a first performance parameter set; executing the structured SQL statement on the indexed simulation data to obtain a second performance parameter set; and comparing the first performance parameter set and the second performance parameter set to obtain a comparison result.

[0185] According to the technical solution of the embodiment of the present invention, by creating a simulated database and using the created simulated database to verify the database index, the problem of affecting the business when verifying the optimization result of the online database is overcome; by using multiple SQL input sources, the system in the design stage and initial operation can also optimize the database index; the metadata source is expanded to provide a basis for database index optimization; and according to the extracted metadata features, the optimal index type is automatically determined, so it is no longer limited to a single type of index.

[0186] The above specific implementations do not constitute a limitation on the protection scope of the present invention. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions may occur depending on design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A method for database index optimization suggestion and verification, characterized in that: include: Generate simulation data according to predetermined rules; applying a candidate index to the simulated data; Executing a structured SQL statement on the simulation data to obtain a first performance parameter set; executing the structured SQL statement on the indexed simulation data to obtain a second performance parameter set; as well as comparing the first performance parameter set and the second performance parameter set to obtain a comparison result; Before applying the candidate index to the simulated data, the method further includes: The candidate indexes are calculated based on the metadata and the structured SQL statements, the candidate indexes are optimized and merged, sorted according to index functions, and the top N indexes are extracted; The candidate index is calculated, including: Enter the SQL condition; determine whether index optimization can be performed; if so, determine whether the data discrimination is high or low; If it is high, determine whether there is only equal value comparison and it does not appear in the sort statement of the structured SQL statement; if so, obtain a hash index; if not, obtain a tree structure index, and when the field is a string type, determine the field length for creating the index based on metadata; If it is low, determine whether the database supports bitmap indexes; if so, obtain the bitmap index; if not, add other fields based on the query conditions to determine whether a composite index can be created.

2. The method according to claim 1, characterized in that Before executing the structured SQL statement on the simulation data, the method further includes: The structured SQL statement is obtained by structuring one or more of the following: SQL execution log, database slow SQL log, MyBatis file, and custom SQL file.

3. The method according to claim 1, characterized in that in, The candidate index types include at least BTree index, hash index and bitmap index.

4. The method according to claim 1, characterized in that: The predetermined rules include: Use the original data as the simulation data, or The simulation data is generated according to the metadata of the original data.

5. The method according to claim 3 or 4, characterized in that: The metadata is obtained by one or more of the following steps: Extract rules from the database where the original data is located; Extracting the rules from a database design document; Extracting the rules from existing data; or The rules are checked and supplemented by the user.

6. The method according to claim 1, characterized in that After obtaining the comparison result, the method further includes: generating index optimization suggestions and verification results according to the comparison results; and Output the generated index optimization suggestions and verification results.

7. A device for database index optimization suggestion and verification, characterized in that: include: A simulation data processing module, used to generate simulation data according to a predetermined rule and apply a candidate index to the simulation data; as well as an index verification module, configured to execute a structured SQL statement on the simulated data to obtain a first performance parameter set, execute the structured SQL statement on the indexed simulated data to obtain a second performance parameter set, and compare the first performance parameter set with the second performance parameter set to obtain a comparison result; An index calculation module, used to calculate the candidate indexes according to the metadata and the structured SQL statement, optimize and merge the candidate indexes, sort them according to the index functions, and extract the top N indexes; The index calculation module is further used for: Enter the SQL condition; Determine whether index optimization is possible; if so, determine whether the data discrimination is high or low; If high, determine whether there is only an equality comparison and it does not appear in the sort statement of the structured SQL statement; If yes, a hash index is obtained; if no, a tree structure index is obtained. When the field is a string type, the length of the field to create the index is determined based on the metadata. If it is low, determine whether the database supports bitmap indexes; if so, obtain the bitmap index; if not, add other fields based on the query conditions to determine whether a composite index can be created.

8. The device according to claim 7, characterized in that Further including: The SQL processing module is used to obtain the structured SQL statement by structuring one or more of the following: SQL execution log, database slow SQL log, MyBatis file and custom SQL file.

9. The device according to claim 7, characterized in that in, The candidate index types include at least BTree index, hash index and bitmap index.

10. The device according to claim 7, characterized in that The simulation data processing module is further used for: Use the original data as the simulation data, or The simulation data is generated according to the metadata of the original data.

11. The device according to claim 9 or 10, characterized in that Further including: The metadata module is used to obtain the metadata by one or more of the following steps: Extract rules from the database where the original data is located; Extracting the rules from a database design document; Extracting the rules from existing data; or The rules are checked and supplemented by the user.

12. The device according to claim 7, characterized in that Further including: A result display module, used to generate index optimization suggestions and verification results according to the comparison results; And output the generated index optimization suggestions and verification results.

13. An electronic device for database index optimization suggestion and verification, characterized in that: include: one or more processors; a storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1 to 6.

14. A computer readable medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 6 is implemented.

Images