Index recommendation method and device, electronic equipment, storage medium and program product

Abstract

The invention provides an index recommendation method and device, electronic equipment, a storage medium and a program product, and belongs to the technical field of data processing.The method comprises the steps that candidate composite indexes are determined based on the field distinction degree and the field occurrence frequency of multiple fields in a slow query statement; and determining an optimal recommendation index based on the execution cost of the candidate composite index. According to the index recommendation method and device, the electronic equipment, the storage medium and the program product provided by the invention, semantic features of two dimensions of field distinction degree and field occurrence frequency are fully utilized for an existing slow query statement with a poor index execution effect, and quantitative evaluation is performed through the execution cost of the candidate composite index, so that the efficiency of the slow query statement is improved. The advantages and disadvantages of the index are evaluated more comprehensively and objectively, the situation that the index created by depending on subjective experience does not accord with the actual situation is overcome, compared with a single-column index, the recommended composite index can better improve the statement query performance of a database, slow query is avoided, and better user experience is provided.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to an index recommendation method, device, electronic device, storage medium and program product. Background Art

[0002] As an enterprise's business rapidly expands, its user base will also grow significantly. The database, which serves as the core of data storage in business projects, carries an ever-increasing amount of data and increasingly complex business logic. Slow query statements that take a long time to execute, occupy a large amount of memory, and drag down the overall performance of the system are appearing more and more frequently.

[0003] The common cause of slow query statements is the lack of indexes or unreasonable settings. Currently, the main method for creating and recommending indexes for structured query language (SQL) statements is to rely on the experience and intuition of development engineers.

[0004] However, due to the lack of support from actual operating data, index creation that relies on the experience and intuition of development engineers is too subjective and may not match the actual execution situation. In addition, when creating indexes based on the experience and intuition of development engineers, they tend to create simple single-column indexes, failing to fully utilize the potential of composite indexes in improving query performance. Summary of the invention

[0005] The present invention provides an index recommendation method, device, electronic device, storage medium and program product, which are used to solve the defect of relying on the experience and intuition of development engineers to create indexes in the prior art, improve query performance and avoid the occurrence of slow query statements.

[0006] The present invention provides an index recommendation method, comprising: Determine candidate composite indexes based on the field distinction and field occurrence count of multiple fields in the slow query statement; An optimal recommended index is determined based on the execution costs of the candidate composite indexes.

[0007] According to an index recommendation method provided by the present invention, when the slow query statement includes an equal value query field, determining a candidate composite index based on field distinction and field occurrence counts of multiple fields in the slow query statement includes: Sort the equivalent query fields in descending order of the field discrimination of the equivalent query fields; If there are first fields with the same field distinction, sorting the first fields in descending order of the number of occurrences of the fields; Determine an index field sequence according to the equal-value query fields that have completed sorting; Based on the index field sequence, the candidate composite index is determined.

[0008] According to an index recommendation method provided by the present invention, when the slow query statement also includes non-equivalent query fields, determining the index field sequence according to the sorted equivalent query fields includes: If there is one non-equivalent query field, add the non-equivalent query field after the sorted equivalent query field to determine the index field sequence; If there are two or more non-equivalent query fields, the step of adding one non-equivalent query field after the sorted equivalent query fields is repeated to obtain index field sequences corresponding to the non-equivalent query fields respectively.

[0009] According to an index recommendation method provided by the present invention, when the slow query statement only includes non-equivalent query fields, determining a candidate composite index based on field distinctions and field occurrence counts of multiple fields in the slow query statement includes: Sort the non-equivalent query fields in descending order of the operator priority of the non-equivalent query fields; If there is a second field with the same priority as the operator, sorting the second fields in descending order of the field discrimination; If there is a third field with the same field distinction, sort the third fields in descending order of the number of occurrences of the fields; Determine an index field sequence according to the non-equal value query fields after sorting; Based on the index field sequence, the candidate composite index is determined.

[0010] According to an index recommendation method provided by the present invention, for each of the index field sequences, determining the candidate composite index based on the index field sequence includes: Determine the first index field in the index field sequence as the current field, and calculate the cumulative coverage of the current field; the cumulative coverage is determined based on the first field occurrence sum and the second field occurrence sum; the first field occurrence sum is the sum of the field occurrences of the current field and its preceding field; the second field occurrence sum is the sum of the field occurrences of all fields in the index field sequence; If the cumulative coverage is less than a preset threshold, the next index field of the current field is used as a new current field, and the process returns to the step of calculating the cumulative coverage of the current field; If the cumulative coverage is greater than or equal to a preset threshold, the candidate composite index is determined based on the most recently determined current field and its preceding field.

[0011] According to an index recommendation method provided by the present invention, when there are multiple candidate composite indexes, determining the optimal recommended index based on the execution cost of the candidate composite indexes includes: Based on the number of scanned rows and the number of returned rows when the candidate composite index is executed, obtaining the execution cost of each candidate composite index; When there is only one candidate composite index with the minimum execution cost, determining the candidate composite index with the minimum execution cost as the optimal recommended index; When there are two or more candidate composite indexes with the minimum execution cost, the candidate composite index with the shortest execution time among the candidate composite indexes with the minimum execution cost is determined as the optimal recommended index.

[0012] According to an index recommendation method provided by the present invention, before determining the optimal recommended index based on the execution cost of the candidate composite index, the method further includes: Deleting invalid indexes in the candidate composite index; The invalid index is a historical recommendation index whose hit count is lower than a preset count.

[0013] According to an index recommendation method provided by the present invention, the slow query statement is determined based on the following method: Determine the slow query cost of non-full table scan statements in the slow query log based on the number of scanned rows, number of returned rows, execution time, and number of hits; Determine unclassified slow query statements based on full table scan statements and non-full table scan statements whose slow query costs are greater than a preset cost threshold; Based on the execution command, the query condition, the data table name and the database name, a classification and deduplication operation is performed on the unclassified slow query statement to determine the slow query statement.

[0014] The present invention also provides an index recommendation device, comprising: A candidate index determination module is used to determine a candidate composite index based on the field distinction and field occurrence count of multiple fields in a slow query statement; The optimal index determination module is used to determine the optimal recommended index based on the execution cost of the candidate composite index.

[0015] The present invention also provides an electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, the index recommendation method described above is implemented.

[0016] The present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements any of the above-mentioned index recommendation methods.

[0017] The present invention also provides a computer program product, comprising a computer program, wherein when the computer program is executed by a processor, the computer program implements any of the above-mentioned index recommendation methods.

[0018] The index recommendation method, device, electronic device, storage medium and program product provided by the present invention fully utilize the semantic features of two dimensions, namely, field discrimination and field occurrence frequency, for slow query statements with poor execution effect of existing indexes, and perform quantitative evaluation through the execution cost of candidate composite indexes, so as to more comprehensively and objectively evaluate the pros and cons of indexes, and overcome the situation where the index created based on subjective experience is inconsistent with the actual situation. Compared with single-column indexes, the recommended composite index can improve the statement query performance of the database, avoid the occurrence of slow queries, and provide a better user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] In order to more clearly illustrate the technical solutions in the present invention or the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0020] Figure 1 It is a flowchart of the index recommendation method provided by the present invention.

[0021] Figure 2 It is a schematic diagram of the process of determining a candidate composite index provided by the present invention.

[0022] Figure 3 It is a structural schematic diagram of the index recommendation device provided by the present invention.

[0023] Figure 4 It is a structural schematic diagram of the electronic device provided by the present invention. DETAILED DESCRIPTION

[0024] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with the drawings of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0025] It should be noted that, in the description of the present invention, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also includes other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. For those of ordinary skill in the art, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0026] The terms "first", "second", etc. in the present invention are used to distinguish similar objects, and are not used to describe a specific order or sequence. It should be understood that the data used in this way can be interchanged under appropriate circumstances, so that the embodiments of the present invention can be implemented in an order other than those illustrated or described herein, and the objects distinguished by "first", "second", etc. are generally of the same type, and the number of objects is not limited. For example, the first object can be one or more. In addition, "and / or" means at least one of the connected objects, and the character " / " generally indicates that the objects associated with each other are in an "or" relationship.

[0027] Combine the following Figure 1-Figure 4 The present invention describes an index recommendation method, device, electronic device, storage medium and program product.

[0028] As an enterprise's business rapidly expands, its user base will also grow significantly. The database, which serves as the core of data storage in business projects, carries an ever-increasing amount of data and increasingly complex business logic. Slow query statements that take a long time to execute, occupy a large amount of memory, and drag down the overall performance of the system are appearing more and more frequently.

[0029] Taking the development and deployment of the home smart gateway soft probe project as an example, with the significant growth in the number of home users, the MongoDB database, as the core data storage solution of the project, carries an increasing amount of data and increasingly complex business logic. This rapid business expansion has led to significant performance issues, especially the execution of slow SQL statements, which not only take a long time to execute and occupy a large amount of memory, but also seriously drag down the overall performance of the system. In the MongoDB database, the lack of indexes or unreasonable settings are common causes of these problems, which directly affect the health and execution efficiency of SQL statements.

[0030] Currently, the main method for creating and recommending indexes for SQL statements is to rely on the experience and intuition of development engineers. However, this method has obvious limitations: first, the lack of support from actual running data makes index creation too subjective and may not match the actual execution situation; second, it tends to create single-column indexes and fails to fully utilize the potential of composite indexes in improving query performance.

[0031] Therefore, the present invention provides an index recommendation method, device, electronic device, storage medium and program product, which optimize the index creation process through automated and intelligent means, reduce dependence on developer experience, thereby improving query performance and avoiding the occurrence of slow query problems, and improving the efficiency of indexing and indexing effectiveness, so as to solve at least one of the above-mentioned technical problems.

[0032] Figure 1 is a flow chart of the index recommendation method provided by the present invention, such as Figure 1 As shown, the index recommendation method includes but is not limited to step 101 and step 102.

[0033] It should be noted that the executor of the index recommendation method provided by the present invention is the corresponding index recommendation device, which can specifically be a server, a computer device, such as a mobile phone, a tablet computer, a laptop computer, a PDA, a vehicle-mounted electronic device, a wearable device, an ultra-mobile personal computer (UMPC), a netbook or a personal digital assistant (PDA), etc.

[0034] Step 101: Determine a candidate composite index based on the field differentiation (differentiate) and field occurrence count (count) of multiple fields in the slow query statement.

[0035] Slow query statements, also called slow SQL statements, are query statements that are slow due to poor execution of created indexes.

[0036] The field discrimination is an indicator that measures the number of different values ​​of a field, and determines the number of scanned rows, because the number of scanned rows is inversely proportional to the number of different values. The more rows scanned, the lower the index efficiency. If the field discrimination is smaller, the index efficiency created based on the field is also lower. The greater the field discrimination, the higher the index efficiency created based on the field. When creating a composite index, placing the field with higher discrimination on the left side of the composite index will be more conducive to improving the efficiency of the composite index.

[0037] The field occurrence count is the number of times the field appears in the slow query statement.

[0038] Specifically, a syntax tree is abstracted from the slow query statement, the syntax tree is decomposed, multiple fields of the slow query statement and related field information are extracted, and the number of occurrences of each field in the slow query statement is counted. The related field information includes but is not limited to at least one of the query field (queryField), sort field (sortField), group field (groupField), query condition field (whereField), and other information.

[0039] At the same time, for the multiple fields extracted, the discrimination of each field is obtained from the database table as the field discrimination.

[0040] Furthermore, according to the field distinction and the number of field occurrences of the multiple fields, all or part of the multiple fields are added to the composite index according to a certain adding order and adding rule to obtain at least one candidate composite index.

[0041] For example, first add all fields to the composite index in descending order of field discrimination. If the fields have the same discrimination, add the fields with the same discrimination to the composite index in descending order of the number of occurrences of the fields.

[0042] For another example, when the field discriminations are the same, candidate composite indexes having the same number of fields as the field discriminations are obtained, and each candidate composite index includes only one of the fields having the same field discriminations, thereby obtaining a plurality of candidate composite indexes.

[0043] For another example, only some fields whose field distinction and / or field occurrence times are greater than a preset value among the multiple fields are added to the composite index to obtain a candidate composite index.

[0044] Step 102: Determine the optimal recommended index based on the execution cost of the candidate composite index.

[0045] In database operations, the execution cost of indexes mainly occurs at the server layer and the engine (storage engine) layer. The server layer is mainly the CPU cost, and the engine layer is mainly the IO cost. Therefore, the execution cost is also the cost determined by the CPU cost and IO cost when executing the candidate composite index.

[0046] Specifically, the execution cost of each candidate composite index is calculated, and the candidate composite index with the smallest execution cost is determined as the optimal recommended index, so that the optimal recommended index is recommended to the user, and the user creates the optimal recommended index on the slow query statement, or the optimal recommended index is automatically created on the slow query statement.

[0047] Optionally, after determining the execution cost of the candidate composite index, eliminate indexes whose execution time is greater than a time threshold (slowTime) configured for the slow query log and / or whose difference in the number of query rows is greater than a preset row number threshold (rowThresold), and then determine the optimal recommended index from the remaining candidate composite indexes.

[0048] Compared with relying on the experience and intuition of development engineers to create indexes, the index recommendation method provided by the present invention fully utilizes the semantic features of the two dimensions of field discrimination and field occurrence frequency for slow query statements with poor execution effect of existing indexes, and performs quantitative evaluation through the execution cost of candidate composite indexes, so as to more comprehensively and objectively evaluate the pros and cons of indexes, and overcome the situation where the index created relying on subjective experience is inconsistent with the actual situation. Compared with single-column indexes, the recommended composite index can improve the statement query performance of the database, avoid the occurrence of slow queries, and provide a better user experience.

[0049] Based on the above embodiment, as an optional embodiment, the slow query statement is determined based on the following method: Determine the slow query cost of non-full table scan statements in the slow query log based on the number of scanned rows (keysExamined), number of returned rows (nreturned), execution time (millis), and number of hits (hitCount); Determine unclassified slow query statements based on full table scan statements and non-full table scan statements whose slow query costs are greater than a preset cost threshold; Based on the execution command (commnad), the query condition (filter), the data table name (Collection) and the database name (db), a classification and deduplication operation is performed on the unclassified slow query statement to determine the slow query statement.

[0050] The number of returned rows is the number of rows in the result set returned by a specific query statement in a database query operation.

[0051] The number of scanned rows, also known as the number of index key scans, indicates the number of index keys that the database scans to find records that meet the conditions. The more keys that are scanned, the more IO and CPU resources are required, and the slower the query may be. However, the number of scanned rows is not always directly related to the slowness of the query, so considering the difference between the number of scanned rows and the number of returned rows as an indicator can better reflect the additional scanning overhead caused by improper indexing.

[0052] Execution time is the most direct indicator of query speed. The longer the execution time, the greater the impact on the business.

[0053] The hit count, also known as the index hit count, indicates the number of times the index is hit during the query process. The lower the hit count, the worse the index effect is, which may cause the query to slow down.

[0054] Specifically, after extracting the query statements and their statement information from the slow query log, first distinguish between full table scan statements and non-full table scan statements. Full table scan statements indicate that the query statement does not have an index set or the index setting is wrong, resulting in a missed index, that is, the index needs to be created or optimized.

[0055] Furthermore, based on the obtained information about the number of scanned rows, number of returned rows, execution time, and number of hits of the non-full table scan statements, different weights are assigned to calculate the slow query cost of each non-full table scan statement, and the non-full table scan statements whose slow query costs are greater than a preset cost threshold (slowCostThreslod) and the aforementioned full table scan statements are determined as unclassified slow query statements, thereby screening out slow query statements whose execution effects are poor due to index problems.

[0056] Furthermore, after obtaining the unclassified slow query statements, a classification and deduplication operation is performed on the unclassified slow query statements, and several unclassified slow query statements with exactly the same execution commands, database names and data table names, the same query keywords and operators in the query conditions, and the same or different arrangement orders of the query keywords are classified into the same type of slow query statements, and one of the same type of slow query statements is determined as a slow query statement to obtain several slow query statements.

[0057] In one embodiment, the slow query cost of the non-full table scan statement in the slow query log is determined based on the number of scanned rows, the number of returned rows, the execution time, and the number of hits according to the following expression (1): ; (1)

[0058] in, It is the cost of slow query; is the number of scan lines; is the number of rows returned; is the execution time; is the number of hits; , , The weights are determined based on actual conditions and are used to reflect the relative importance of each indicator in different business scenarios.

[0059] Considering the direct use May be in When the value is 0, a calculation error occurs when the divisor is 0. Therefore, based on the smoothing method, A small constant is added before taking the reciprocal to avoid division by 0.

[0060] In one embodiment, for an index whose hit count in the slow query log is lower than a preset number (hitCountThresold), it is determined as an invalid index, which is used to filter the invalid index when the optimal recommended index is subsequently determined, to prevent the creation of duplicate indexes and the generation of invalid indexes, and to delete the index in the index log table that is the same as the invalid index.

[0061] In one embodiment, before determining the non-full table scan statement, the unclassified slow query statement, and the slow query statement according to the query statement and related statement information in the slow query log, the query statement and related statement information thereof are extracted from the slow query log, specifically including: Enable the slow query log configuration switch on the database server (such as MongoDB database) and set the database statement execution timeout threshold (slowTime) in milliseconds (ms). When the execution time of a query statement exceeds the time threshold, the query statement is a slow query statement and will be recorded in the database's slow query log (such as the mongo.log log file).

[0062] According to the file format of the slow query log, a parser for parsing the slow query log is determined, and the slow query log is parsed by the parser to obtain each SQL statement in the slow query log and related statement information.

[0063] For example, when the slow query log is in JSON format, you can use the JSON parser to process the log, build the Java entity class code, generate the log entity class, and obtain the information of each SQL statement.

[0064] The statement information related to each SQL statement includes but is not limited to at least one of the execution command, filter information (filter), data table name, database name, number of scanned documents (docsExamined), number of scanned rows, number of returned rows and index information (planSummary).

[0065] Furthermore, after extracting the data table name information, execute the index usage query command (such as: db.table name.aggregate({"$indexStats":{}})) to obtain the historical index information of the database. The historical index information includes but is not limited to the index name, the number of index hits, etc.

[0066] Optionally, middleware (such as Flume middleware) is used to collect slow query logs in real time. When new changes are added to the slow query logs, they will be collected and processed by the middleware. The use of middleware can achieve fast and convenient deployment, so that the index recommendation method provided in this aspect can be widely used in various business platforms involving database applications.

[0067] The index recommendation method provided by the present invention, before extracting the slow query statement for determining the optimal recommended index, pre-processes the query statement in the slow query log, firstly performs weight calculation on different indicator information, screens out non-full table scan statements whose slow query cost is greater than a preset threshold, and determines these non-full table scan statements and full table statements as unclassified slow query statements, that is, screens out statements that cause slow queries due to poor index execution effect and no index setting, and then performs classification and deduplication operations on these statements, screens out slow query statements that are actually caused by index problems, avoids over-optimization, further reduces the amount of calculation in the subsequent optimal index recommendation process, shortens the calculation and analysis time, and thus improves the calculation speed and efficiency of index recommendation.

[0068] Based on the above embodiment, as an optional embodiment, when the slow query statement includes an equal value query field, determining a candidate composite index based on field distinction and field occurrence counts of multiple fields in the slow query statement includes: Sort the equivalent query fields in descending order of the field discrimination of the equivalent query fields; If there are first fields with the same field distinction, sorting the first fields in descending order of the number of occurrences of the fields; Determine an index field sequence according to the equal-value query fields that have completed sorting; Based on the index field sequence, the candidate composite index is determined.

[0069] Equal value query is a database query method that uses the equal sign to judge the query conditions and can directly obtain records that meet specific conditions.

[0070] The equal value query field is the keyword field in the slow query statement that uses the equal sign to determine the query condition.

[0071] The first field refers to an equal-value query field with the same field distinction.

[0072] Specifically, when a slow query statement contains two or more equal query fields, all equal query fields are sorted in descending order according to the field discrimination. If there are no equal query fields with the same field discrimination, all the equal query fields are sorted to obtain sorted equal query fields. The equal query field with the highest field discrimination is placed at the leftmost of the candidate composite index, and the candidate composite index is generated from left to right according to the sorting of the equal query fields.

[0073] If there are several first fields with the same field discrimination, the several first fields are sorted in descending order of the number of field occurrences to complete the sorting of all equivalent query fields. If there are still cases where the number of field occurrences of the first fields is the same, the order of the first fields is randomly determined to complete the sorting of all equivalent query fields. Similarly, according to the sorting of the equivalent query fields, candidate composite indexes are generated from left to right.

[0074] For example, if the slow query statement includes five equal-value query fields A, B, C, D, and E, the field distinctions are 20, 15, 10, 10, and 10, and the field occurrences are 3, 4, 5, 6, and 7. Then, after sorting, the order of all equal-value query fields is A, B, E, D, and C, and the index field sequence is ABEDC. The order of fields from left to right in the generated candidate composite index is A, B, E, D, and C.

[0075] It can be understood that when the slow query statement includes only a number of equal-value query fields, the number of candidate composite indexes determined is 1, and the candidate composite index can be directly used as the optimal recommended field.

[0076] The index recommendation method provided by the present invention determines the generated candidate composite index according to the field discrimination and the number of field occurrences of each equivalent query field when the slow query statement includes an equivalent query field, makes full use of the semantic features of the two dimensions of field discrimination and field occurrence, and more comprehensively considers the objective semantic features of each equivalent query field, which helps to further overcome the situation where the index created based on subjective experience is inconsistent with the actual situation, provides a candidate composite index with better execution performance, avoids the occurrence of slow queries, and provides a better user experience.

[0077] Based on the above embodiment, as an optional embodiment, when the slow query statement further includes non-equivalent query fields, determining the index field sequence according to the sorted equivalent query fields includes: If there is one non-equivalent query field, add the non-equivalent query field after the sorted equivalent query field to determine the index field sequence; If there are two or more non-equivalent query fields, the step of adding one non-equivalent query field after the sorted equivalent query fields is repeated to obtain index field sequences corresponding to the non-equivalent query fields respectively.

[0078] Non-equal value query is a database query method that uses inequality signs such as greater than, less than, greater than or equal to to query conditions.

[0079] The non-equal value query field is the keyword field in the slow query statement that uses the inequality sign to determine the query condition.

[0080] Specifically, when the slow query statement includes both non-equivalent query fields and equivalent query fields, the method for determining candidate composite indexes when the slow query statement includes equivalent query fields provided in the above embodiment is first used to determine the equivalent query fields after sorting.

[0081] Determine the number of non-equivalence query fields in the slow query statement. If the number of non-equivalence query fields is one, that is, directly add the non-equivalence query field after the sorted equivalent query field is completed, and obtain the index field sequence corresponding to the slow query statement containing the equivalent query field and the non-equivalence query field.

[0082] If the number of non-equivalence query fields is two or more, the step of adding one non-equivalence query field to the equivalent query fields after sorting is repeated to obtain two or more index field sequences whose number is the same as the number of non-equivalence query fields and which are different from each other, and further obtain candidate composite indexes whose number is the same as the number of index field sequences.

[0083] For example, a slow query statement includes equal query fields A, B, C, D, E, and non-equal query fields F and G. After sorting, the order of the equal query fields is A, B, E, D, C. Then, repeat the steps of adding one non-equal query field to the equal query fields after sorting, and obtain two index field sequences "ABEDCF" and "ABEDCG".

[0084] The index recommendation method provided by the present invention comprehensively considers the objective semantic features of each equivalent query field when a slow query statement includes an equivalent query field and a non-equivalent query field, determines the first part of the candidate composite index according to the field discrimination and the number of field occurrences of the equivalent query field, and then adds the non-equivalent query field as the second part to the candidate composite index, and creates a number of candidate composite indexes that is the same as the number of non-equivalent query fields. This helps to further overcome the situation where the index created based on subjective experience is inconsistent with the actual situation. Compared with the method of first performing non-equivalent query indexing and then performing equivalent query indexing, it can provide a candidate composite index with better execution performance, reduce the waste of execution cost computing resources, avoid the occurrence of slow queries, and provide a better user experience.

[0085] Based on the above embodiment, as an optional embodiment, when the slow query statement only includes non-equivalent query fields, determining the candidate composite index based on the field distinction and field occurrence count of multiple fields in the slow query statement includes: Sort the non-equivalent query fields in descending order of the operator priority of the non-equivalent query fields; If there is a second field with the same priority as the operator, sorting the second fields in descending order of the field discrimination; If there is a third field with the same field distinction, sort the third fields in descending order of the number of occurrences of the fields; Determine an index field sequence according to the non-equal value query fields after sorting; Based on the index field sequence, the candidate composite index is determined.

[0086] The second field is a non-equality query field with the same operator priority.

[0087] The third field is a non-equivalence query field with the same operator priority and field discrimination.

[0088] The operator priority is the priority set in advance for the operators provided by the database and used for non-equality queries.

[0089] Taking MongoDB database as an example, operators include but are not limited to: $in operator for selecting documents whose field value is equal to any value in a specified array; $gt operator for selecting documents whose value of a specified field is greater than a specified value; $gte operator for selecting documents whose value of a specified field is greater than or equal to a specified value; $lt operator for selecting documents whose value of a field is less than a specified value; $lte operator for selecting documents whose value of a field is less than or equal to a specified value; $nin operator for selecting documents whose value of a specified field is not in a specified array; $ne operator for selecting documents whose value of a specified field is not equal to a specified value; $type operator for selecting a field whose value is an instance of a specified BSON type; and $exist operator for matching documents that contain or do not contain a specified field.

[0090] The priorities of the operators of the aforementioned MongoDB database are predetermined from high to low as follows: $In, $gt, $gte, $lt, $lte, $nin, $ne, $type, $exist.

[0091] Specifically, when a slow query statement only includes non-equivalent query fields, all non-equivalent query fields are first sorted in descending order according to the operator priority of the non-equivalent query fields. If there are no non-equivalent query fields with the same operator priority, all non-equivalent query fields are sorted, and the sorted non-equivalent query fields are used as the index field sequence. The first field in the index field sequence, that is, the non-equivalent query field with the highest operator priority, is placed at the leftmost of the candidate composite index, and the candidate composite index is generated from left to right according to the sorting of the index field sequence.

[0092] If there are several second fields with the same operator priority, the several second fields are sorted in descending order of field discrimination. If there are no second fields with the same field discrimination, all non-equivalent query fields are sorted to obtain the corresponding index field sequence.

[0093] If there are several third fields with the same field distinction among the several second fields, the several third fields are sorted in descending order according to the number of field appearances. If there are no third fields with the same number of field appearances, the sorting of all non-equivalent query fields is completed at this time to obtain the corresponding index field sequence. If there are still cases where the number of field appearances of the third fields is the same, the sorting of the third fields is randomly determined, thereby completing the sorting of all non-equivalent query fields.

[0094] It can be understood that when the slow query statement includes only a number of non-equal value query fields, the number of candidate composite indexes determined is 1, and the candidate composite index can be directly used as the optimal recommended field.

[0095] The index recommendation method provided by the present invention introduces a third dimension of operator priority on the basis of the two dimensions of field distinction and field occurrence count when the slow query statement only includes non-equivalent query fields, so as to more comprehensively consider the objective semantic characteristics of each non-equivalent query field, help to further overcome the situation where the index created based on subjective experience is inconsistent with the actual situation, provide candidate composite indexes with better execution performance, avoid the occurrence of slow queries, and provide a better user experience.

[0096] In one embodiment, if there is only one query field in the slow query statement, a single-field index is directly output based on the query field.

[0097] In one embodiment, if the slow query statement is a sorting query statement that does not include equal value query fields or non-equal value query fields, the index field sequence of the candidate composite index is directly generated according to the order of the sorting fields.

[0098] In one embodiment, if the slow query statement includes an equal query field and a sorting field, the first part of the index field sequence is generated according to the method of determining the candidate composite index when the slow query statement includes an equal query field provided in the aforementioned embodiment, and then the second part of the index field sequence is generated according to the order of the sorting fields. After combining the first part and the second part, the index field sequence of the candidate composite index is obtained.

[0099] In one embodiment, if the slow query statement includes non-equal query fields and sorting fields, the first part of the index field sequence is generated according to the method of determining the candidate composite index when the slow query statement provided in the aforementioned embodiment only includes non-equal query fields, and then the second part of the index field sequence is generated according to the order of the sorting fields. After combining the first part and the second part, the index field sequence of the candidate composite index is obtained.

[0100] In one embodiment, if the slow query statement includes an equal query field, a non-equal query field, and a sorting field, the first part of the index field sequence is generated according to the method of determining the candidate composite index when the provided slow query statement includes an equal query field and a non-equal query field, and then the second part of the index field sequence is generated according to the order of the sorting fields. After combining the first part and the second part, the index field sequence of the candidate composite index is obtained.

[0101] In one embodiment, if the slow query statement includes an OR query, the index field sequence of each OR combination in the OR query is generated according to the method of determining the candidate composite index provided in the above embodiment, and the index field sequence of each OR combination corresponds to a candidate composite index, thereby obtaining the same number of candidate composite indexes as the OR combinations.

[0102] Figure 2 is a schematic diagram of a process for determining a candidate composite index provided by the present invention, such as Figure 2 As shown, based on the above embodiment, as an optional embodiment, for each of the index field sequences, determining the candidate composite index based on the index field sequence includes: Determine the first index field in the index field sequence as the current field, and calculate the cumulative coverage of the current field; the cumulative coverage is determined based on the first field occurrence count sum (count) and the second field occurrence count sum (totalCount); the first field occurrence count sum is the sum of the field occurrence counts of the current field and its preceding field; the second field occurrence count sum is the sum of the field occurrence counts of all fields in the index field sequence; If the cumulative coverage is less than a preset threshold, the next index field of the current field is used as a new current field, and the process returns to the step of calculating the cumulative coverage of the current field; If the cumulative coverage is greater than or equal to a preset threshold, the candidate composite index is determined based on the most recently determined current field and its preceding field.

[0103] Considering that generating a composite index based on all fields in a slow query statement may cause the index itself to be too large, thus adversely affecting database performance, this embodiment provides a method for generating a composite index using some fields of a slow query statement.

[0104] Specifically, the sum of the number of field occurrences of all fields in the index field sequence is calculated to obtain the sum of the second field occurrences, which is used as the denominator for calculating the cumulative coverage of all fields.

[0105] The first index field in the index field sequence is determined as the current field. At this time, the sum of the occurrence times of the first field is the field occurrence times of the current field. The sum of the occurrence times of the first field is used as the numerator for calculating the cumulative coverage rate of the first index field, and the cumulative coverage rate of the first index field is calculated.

[0106] Determine the relationship between the cumulative coverage of the current field and the preset threshold (such as 50%, 60%, 70%). If it is less than the preset threshold, take the next index field (the second index field) of the current field (the first index field) as the new current field, and calculate the cumulative coverage of the second index field. Take the sum of the field occurrence counts of the first index field and the second index field as the sum of the first field occurrence counts, and calculate the cumulative coverage of the second index field.

[0107] Then determine the relationship between the cumulative coverage of the new current field and the preset threshold. If it is less than the preset threshold, continue to use the next field as the new current field, repeatedly calculate its cumulative coverage, and determine the cumulative coverage and the preset threshold. In any process of determining the relationship between the cumulative coverage of the current field and the preset threshold, if the cumulative coverage is greater than or equal to the preset threshold, the most recently determined current field and its preceding field are directly used to generate a candidate composite index.

[0108] For example, a sequence of index fields is ABEDCF, the field occurrences of each index field are 10, 10, 8, 6, 4 and 10, and the preset threshold is 50%. After calculation, it can be obtained that the sum of the field occurrences of all index fields, that is, the sum of the second field occurrences is 48, and the sum of the first field occurrences of each index field is 10, 20, 28, 34, 38 and 48. Further calculations show that the cumulative coverage of each index field (retaining two decimal places) is 20.83%, 41.67%, 58.33%, 70.83%, 79.17% and 100% respectively.

[0109] It can be seen that when the current field is index field E, its cumulative coverage rate of 58.33% is already greater than the preset threshold of 50%, that is, the index field E and its preceding fields A and B are used to generate a candidate composite index, and the order of the index fields in the candidate composite index is A, B, and E from left to right.

[0110] Optionally, based on the sum of the first field occurrences and the sum of the second field occurrences, determining the cumulative coverage is based on the following expression (2): ; (2)

[0111] in, is the cumulative coverage; is the sum of the number of occurrences of the first field; is the sum of the number of occurrences of the second field.

[0112] The index recommendation method provided by the present invention screens the index fields through the cumulative coverage index, and can meet the needs of index query without adding all fields to the index, thereby preventing too many index fields and the index itself from being too large and causing adverse effects on database performance, and further improving the user experience.

[0113] Based on the above embodiment, as an optional embodiment, when there are multiple candidate composite indexes, determining the optimal recommended index based on the execution cost of the candidate composite indexes includes: Based on the number of scanned rows and the number of returned rows when the candidate composite index is executed, obtaining the execution cost of each candidate composite index; When there is only one candidate composite index with the minimum execution cost, determining the candidate composite index with the minimum execution cost as the optimal recommended index; When there are two or more candidate composite indexes with the minimum execution cost, the candidate composite index with the shortest execution time among the candidate composite indexes with the minimum execution cost is determined as the optimal recommended index.

[0114] Specifically, after generating candidate composite indexes, it is necessary to verify their actual effects by calculating the execution costs of these candidate composite indexes. In order to reduce the impact on the online sampling database, hidden nodes are preferred as sampling databases. If there are no hidden nodes in the system, slave nodes are preferred as sampling databases. At the same time, the number and frequency of sampling data are strictly controlled to minimize interference with the online database.

[0115] After determining a small number of sample databases, create candidate composite indexes in the sample databases. By executing the execution plan of the slow query statements in the slow query log, obtain parameters such as execution time, number of scanned documents, number of scanned rows, and number of returned rows. Calculate the difference between the number of scanned rows and the number of returned rows, and then classify them by data table name and index name.

[0116] The execution cost is defined based on the IO cost and the CPU cost. The IO cost of executing the candidate composite index is measured by the number of scanned rows, and the CPU cost of executing the candidate composite index is measured by the number of returned rows.

[0117] The weighted sum algorithm and constrained optimization method are used. Under the constraint of execution time, the execution cost of each candidate composite index is obtained according to the weighted sum of the number of scanned rows and the number of returned rows when each candidate composite index is executed.

[0118] The candidate composite indexes are sorted in descending order of execution cost. When there is only one candidate composite index with the lowest execution cost, the candidate composite index with the lowest execution cost is determined as the optimal recommended index. When there are two or more candidate composite indexes with the lowest execution cost, the candidate composite index with the shortest execution time among the candidate composite indexes with the lowest execution cost is determined as the optimal recommended index.

[0119] Optionally, the execution cost of obtaining each candidate composite index based on the number of scanned rows and the number of returned rows when the candidate composite index is executed is determined based on the following expression (3): ; (3)

[0120] in, For the execution cost; is the number of scan lines; To return the number of rows; , They are the weight values ​​of the number of scanned rows and the number of returned rows respectively; , Adjustments for the number of rows scanned and the number of rows returned, respectively, to avoid competition in the execution cost of candidate composite index scans and ranges.

[0121] The index recommendation method provided by the present invention combines a weighted average algorithm and a constraint algorithm to perform a cost evaluation on the execution results of candidate composite indexes, performs a quantitative evaluation on the candidate composite indexes, and screens out the candidate composite indexes with the lowest execution cost, thereby preventing the occurrence of multiple optimal recommended indexes and achieving better execution effect of the optimal recommended index.

[0122] Based on the above embodiment, as an optional embodiment, before determining the optimal recommended index based on the execution cost of the candidate composite index, the method further includes: Deleting invalid indexes in the candidate composite index; The invalid index is a historical recommendation index whose hit count is lower than a preset count.

[0123] The historical recommended index is the optimal recommended index recommended to the user and has been actually created and executed by the user in the database. The execution results, execution plan, original slow query statement execution plan, original index, execution time and other information of the historical recommended index will be saved in the database index log table.

[0124] Specifically, after determining several candidate composite indexes based on the field discrimination and field occurrence counts of multiple fields in the slow query statement, it is determined whether the candidate composite index is the same as an invalid index in the database index log table whose hit count is lower than a preset count. If so, it means that the historical execution data shows that the execution effect of the candidate composite index is not good and it is not suitable to be recommended to the user, and there is no need to determine the execution cost of the candidate composite index.

[0125] Optionally, in order to be able to use historical recommended indexes with hit times lower than a preset number to screen candidate composite indexes when determining the optimal recommended index, each time the user creates a historical recommended index in the online database, the system will synchronously save the execution results, execution plan, original slow query statement execution plan, original index, execution time and other information of the optimal recommended index to the database index log table for index result retention and comparison, as well as subsequent index execution result tracking and iterative optimization, so that the present invention has stronger dynamics and adaptability, and can better improve the generation effect of candidate indexes.

[0126] The index recommendation method provided by the present invention utilizes the historical recommendation index that has been created and executed, deletes the candidate composite index that is the same as the historical recommendation index with poor execution effect, does not need to create and execute the candidate composite index with poor execution effect on the sampling database to determine its execution cost, avoids the creation and execution of invalid candidate indexes, stops the waste of storage cost of creating indexes and calculation cost of executing indexes early, avoids aggravating load pressure when writing index data to index files, reduces the number of indexes in the table of the sampling database and optimizes the read and write performance of the sampling database, reduces the maintenance overhead of the database, improves the quality and generation efficiency of the optimal recommendation index, and has stronger dynamics and adaptability.

[0127] In one embodiment, after determining the optimal recommended index and actually creating and executing the optimal recommended index, the index log information in the database is visualized through a WEB page, and the query statement execution plan before and after optimization, the number of executions, the time consumed, the number of scanned rows, the number of scanned documents, the number of returned rows, whether to index (that is, whether to scan the entire table), the index name and other information are displayed. Through the graphical display, the comparison results before and after the index optimization can be easily understood.

[0128] The index recommendation method provided by the present invention can significantly increase economic benefits and optimize costs as a whole: the automatic index recommendation mechanism optimizes database query performance, and at the same time reduces unnecessary resource consumption by automatically deleting invalid indexes, avoids the crude method of simply expanding memory, effectively reduces memory costs, and achieves dual optimization of performance and cost; reduces additional expenses of the database due to index performance problems, such as the recovery cost of system downtime, the expansion cost of performance bottlenecks, etc.; by optimizing index configuration, reduces query response time, improves database throughput, and brings direct economic benefits and cost optimization to business projects; by automatically creating or optimizing indexes to respond to database alarms caused by slow query problems, the automation of alarm processing is achieved, the manual intervention of creating indexes based on subjective experience is reduced, and the labor intensity of operation and maintenance engineers in index management is reduced, thereby reducing human resource costs while enhancing the automation level of operation and maintenance.

[0129] Figure 3 is a schematic diagram of the structure of the index recommendation device provided by the present invention. Figure 3 As shown, the index recommendation device includes but is not limited to a candidate index determination module 301 and an optimal index determination module 302 .

[0130] The candidate index determination module 301 is used to determine a candidate composite index based on the field distinction and field occurrence count of multiple fields in the slow query statement; The optimal index determination module 302 is used to determine the optimal recommended index based on the execution cost of the candidate composite index.

[0131] It should be noted that the index recommendation device provided by the present invention can execute the index recommendation method described in any of the above embodiments during specific operation, which will not be described in detail in this embodiment.

[0132] The index recommendation device provided by the present invention fully utilizes the semantic features of two dimensions, namely, field discrimination and field occurrence frequency, for slow query statements with poor execution effect of existing indexes, and performs quantitative evaluation through the execution cost of candidate composite indexes, so as to more comprehensively and objectively evaluate the pros and cons of indexes, and overcome the situation where indexes created based on subjective experience are inconsistent with the actual situation. Compared with single-column indexes, the recommended composite index can improve the statement query performance of the database, avoid the occurrence of slow queries, and provide a better user experience.

[0133] Figure 4 is a schematic diagram of the structure of the electronic device provided by the present invention, such as Figure 4 As shown, the electronic device may include: a processor (Processor) 410, a communication interface (Communications Interface) 420, a memory (Memory) 430 and a communication bus 440, wherein the processor 410, the communication interface 420, and the memory 430 communicate with each other through the communication bus 440. The processor 410 may call the logic instructions in the memory 430 to execute the index recommendation method provided in any of the above embodiments, and the index recommendation method includes but is not limited to the following steps: determining a candidate composite index based on the field discrimination and field occurrence count of multiple fields in a slow query statement; and determining the optimal recommended index based on the execution cost of the candidate composite index.

[0134] In addition, the logic instructions in the above-mentioned memory 430 can be implemented in the form of a software functional unit and can be stored in a computer-readable storage medium when it is sold or used as an independent product. Based on such an understanding, the technical solution of the present invention can be essentially or partly embodied in the form of a software product that contributes to the prior art. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM), random access memory (RAM), disk or optical disk, etc., various media that can store program codes.

[0135] On the other hand, the present invention also provides a computer program product, which includes a computer program. The computer program can be stored on a non-transitory computer-readable storage medium. When the computer program is executed by a processor, the computer can execute the index recommendation method provided in any of the above embodiments. The index recommendation method includes but is not limited to the following steps: determining candidate composite indexes based on the field discrimination and field occurrence counts of multiple fields in a slow query statement; determining the optimal recommended index based on the execution cost of the candidate composite index.

[0136] On the other hand, the present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the index recommendation method provided in any of the above embodiments is implemented. The index recommendation method includes but is not limited to the following steps: determining candidate composite indexes based on the field discrimination and field occurrence counts of multiple fields in a slow query statement; and determining the optimal recommended index based on the execution cost of the candidate composite index.

[0137] The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of this embodiment. Ordinary technicians in this field can understand and implement it without paying creative labor.

[0138] Through the description of the above implementation methods, those skilled in the art can clearly understand that each implementation method can be implemented by means of software plus a necessary general hardware platform, and of course, can also be implemented by hardware. Based on this understanding, the above technical solution is essentially or the part that contributes to the prior art can be embodied in the form of a software product, and the computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a disk, an optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or some parts of the embodiments.

[0139] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. An index recommendation method, characterized in that: include: Determine candidate composite indexes based on the field distinction and field occurrence count of multiple fields in the slow query statement; An optimal recommended index is determined based on the execution costs of the candidate composite indexes.

2. The index recommendation method according to claim 1, characterized in that: When the slow query statement includes an equal value query field, determining a candidate composite index based on field distinctions and field occurrence counts of multiple fields in the slow query statement includes: Sort the equivalent query fields in descending order of the field discrimination of the equivalent query fields; If there are first fields with the same field distinction, sorting the first fields in descending order of the number of occurrences of the fields; Determine an index field sequence according to the equal-value query fields that have completed sorting; Based on the index field sequence, the candidate composite index is determined.

3. The index recommendation method according to claim 2, characterized in that: When the slow query statement further includes non-equivalent query fields, determining the index field sequence according to the sorted equivalent query fields includes: If there is one non-equivalent query field, add the non-equivalent query field after the sorted equivalent query field to determine the index field sequence; If there are two or more non-equivalent query fields, the step of adding one non-equivalent query field after the sorted equivalent query fields is repeated to obtain index field sequences corresponding to the non-equivalent query fields respectively.

4. The index recommendation method according to claim 1, characterized in that: When the slow query statement includes only non-equivalent query fields, determining the candidate composite index based on the field distinction and the number of field occurrences of the multiple fields in the slow query statement includes: Sort the non-equivalent query fields in descending order of the operator priority of the non-equivalent query fields; If there is a second field with the same priority as the operator, sorting the second fields in descending order of the field discrimination; If there is a third field with the same field distinction, sort the third fields in descending order of the number of occurrences of the fields; Determine an index field sequence according to the non-equal value query fields after sorting; Based on the index field sequence, the candidate composite index is determined.

5. The index recommendation method according to any one of claims 2 to 4, characterized in that: For each of the index field sequences, determining the candidate composite index based on the index field sequence includes: Determine the first index field in the index field sequence as the current field, and calculate the cumulative coverage of the current field; the cumulative coverage is determined based on the first field occurrence sum and the second field occurrence sum; the first field occurrence sum is the sum of the field occurrences of the current field and its preceding field; the second field occurrence sum is the sum of the field occurrences of all fields in the index field sequence; If the cumulative coverage is less than a preset threshold, the next index field of the current field is used as a new current field, and the process returns to the step of calculating the cumulative coverage of the current field; If the cumulative coverage is greater than or equal to a preset threshold, the candidate composite index is determined based on the most recently determined current field and its preceding field.

6. The index recommendation method according to claim 1, characterized in that: When there are multiple candidate composite indexes, determining the optimal recommended index based on the execution costs of the candidate composite indexes includes: Based on the number of scanned rows and the number of returned rows when the candidate composite index is executed, obtaining the execution cost of each candidate composite index; When there is only one candidate composite index with the minimum execution cost, determining the candidate composite index with the minimum execution cost as the optimal recommended index; When there are two or more candidate composite indexes with the minimum execution cost, the candidate composite index with the shortest execution time among the candidate composite indexes with the minimum execution cost is determined as the optimal recommended index.

7. The index recommendation method according to claim 1, characterized in that: Before determining the optimal recommended index based on the execution cost of the candidate composite index, the method further includes: Deleting invalid indexes in the candidate composite index; The invalid index is a historical recommendation index whose hit count is lower than a preset count.

8. The index recommendation method according to claim 1, characterized in that: The slow query statement is determined based on the following method: Determine the slow query cost of non-full table scan statements in the slow query log based on the number of scanned rows, number of returned rows, execution time, and number of hits; Determine unclassified slow query statements based on full table scan statements and non-full table scan statements whose slow query costs are greater than a preset cost threshold; Based on the execution command, the query condition, the data table name and the database name, a classification and deduplication operation is performed on the unclassified slow query statement to determine the slow query statement.

9. An index recommendation device, characterized in that: include: A candidate index determination module is used to determine a candidate composite index based on the field distinction and field occurrence count of multiple fields in a slow query statement; The optimal index determination module is used to determine the optimal recommended index based on the execution cost of the candidate composite index.

10. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the computer program, the index recommendation method according to any one of claims 1 to 8 is implemented.

11. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the index recommendation method according to any one of claims 1 to 8 is implemented.

12. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the index recommendation method according to any one of claims 1 to 8 is implemented.

Images