A dynamic index optimization method, device, system and storage medium

Abstract

The present application discloses a dynamic index optimization method, device, system and storage medium, and relates to the field of electronic digital data processing technology. The method includes intercepting query operations on a database, recording logs according to query fields and query duration; configuring urgent optimization duration and required optimization duration; recording query operations, optimizing or making log reminders according to whether the query field is empty and the relationship between the query duration and the urgent optimization duration; and optimizing the index by checking whether the calculation result of the slow query is greater than the optimization threshold and whether the query field matches the index. The embodiment of the present application reduces the query time of the database, improves the overall data access efficiency, saves human resources, reduces the possibility of human errors, better adapts to different application scenarios and needs, improves database query efficiency and reduces resource consumption, and improves user satisfaction.

Description

Technical Field

[0001] The present application belongs to the technical field of electronic digital data processing, and in particular, relates to a dynamic index optimization method, device, system and storage medium. Background Art

[0002] With the continuous expansion of system business, the gradual complexity of data query scenarios, the increase in the number of users and the growing amount of data, the system may encounter performance bottlenecks when querying data, resulting in slow data query speed.

[0003] The traditional method relies on administrators to manually create and maintain indexes based on experience and query requirements. Although this method is direct, it is often difficult to adjust in time when the data scale and query mode change, resulting in a decrease in query performance. The traditional static index management method relies on administrators to manually create and maintain indexes. This method is not only time-consuming and labor-intensive, but also requires administrators to have high database management skills. When the data scale and query mode change frequently, manual index adjustment often cannot respond in time, resulting in a decrease in query performance.

[0004] As the business grows and the amount of data increases, manually managing indexes becomes increasingly complex. Frequent index updates, creations, and deletions are not only time-consuming and labor-intensive, but may also affect database performance. To address these challenges, it is particularly important to develop a method that can automatically and dynamically maintain indexes. Summary of the invention

[0005] The purpose of this application is to provide a dynamic index optimization method, device, system and storage medium, a method for database query optimization and log recording, which aims to improve query efficiency by intercepting database query operations, recording slow query operations, regularly analyzing recorded data, configuring optimization thresholds, and automatically or manually optimizing database indexes according to the degree of match between query fields and indexes.

[0006] In order to achieve the above objectives, the solution of this application is:

[0007] In a first aspect, an embodiment of the present application provides a dynamic index optimization method, including:

[0008] Intercept the query operation on the database and obtain the query data set of the query operation, which includes the set name, query field and query duration;

[0009] Configure the urgent optimization time and the required optimization time; when the query field is not empty and the query time is less than the urgent optimization time, record the collection name, query field and query time of the query operation; when the query field is not empty and the query time is greater than the urgent optimization time, perform index optimization; when the query field is empty and the query time is greater than the required optimization time, issue a log reminder;

[0010] Obtain the original index record table, process the query data set according to the original index record table, and obtain the processed query data set; configure the optimization threshold, traverse the processed query data set, calculate the processed query data set through slow query, and obtain the calculation result;

[0011] Extract the established indexes recorded in the original index record table. The established indexes include composite indexes. Determine whether the calculation result is greater than the optimization threshold. If the calculation result is greater than the optimization threshold, determine whether the query field matches the established index. If the query field completely matches the established index, perform manual debugging. If the query field matches the prefix of the established index and its length is greater than the established index, expand the composite index. If there is no index in the query data set or the query field does not match the established index at all, create a new index.

[0012] According to the above method of the embodiment of the present application, the following additional technical features may also be provided:

[0013] Further, the query data set is processed according to the original index record table to obtain a processed query data set, including:

[0014] Get the original index record table. If the original index record table is empty, determine whether the query field with the same collection name meets the prefix matching condition. If it meets the prefix matching condition, create a composite index; if it does not meet the prefix matching condition, create a single index.

[0015] Further, the query data set is processed according to the original index record table to obtain a processed query data set, including:

[0016] Get the original index record table. If the original index record table is not empty, traverse the original index record table to determine whether the query field with the same collection name meets the prefix of the established index. If it meets the prefix of the established index, remove the redundant fields in the composite index; if it does not meet the prefix of the established index, delete the established index.

[0017] Further, the method comprises:

[0018] Configure the number of days for log analysis, obtain query records in the log according to the number of days for log analysis, and process the query records. The processing method includes classifying them according to the collection name and query field, recording the number of queries for the same query field, and obtaining the average query duration.

[0019] Furthermore, the processed query data set is calculated through slow query to obtain the calculation result. The calculation formula of slow query is as follows:

[0020]

[0021] in, Indicates the calculation result; Indicates the average query duration; represents the logarithmic representation of the query data set; represents the fuzzy query factor; Indicates the query frequency factor.

[0022] Furthermore, in the slow query formula, it is determined whether the query mode is a fuzzy query. If the query mode is a fuzzy query, it does not affect the average query time.

[0023] Furthermore, in the slow query formula, the query frequency base is configured. If the query frequency factor is less than the query frequency base, it will not affect the average query duration.

[0024] In a second aspect, an embodiment of the present application provides a dynamic index optimization device, including:

[0025] A query interception module is configured to intercept a query operation on a database and obtain a query data set of the query operation, wherein the query data set includes a set name, a query field, and a query duration;

[0026] The log recording module is configured to configure the urgent optimization time and the required optimization time; when the query field is not empty and the query time is less than the urgent optimization time, the collection name, query field and query time of the query operation are recorded; when the query field is not empty and the query time is greater than the urgent optimization time, index optimization is performed; when the query field is empty and the query time is greater than the required optimization time, a log reminder is issued;

[0027] The index management module is configured to obtain an original index record table, process the query data set according to the original index record table, and obtain a processed query data set; configure an optimization threshold, traverse the processed query data set, and calculate the processed query data set through a slow query to obtain a calculation result;

[0028] The index optimization module is configured to extract the established indexes recorded in the original index record table, the established indexes including the composite indexes, and determine whether the calculation result is greater than the optimization threshold. If the calculation result is greater than the optimization threshold, determine whether the query field matches the established index. If the query field completely matches the established index, perform manual debugging; if the query field matches the prefix of the established index and the length is greater than the established index, expand the composite index; if there is no index for the query data set or the query field does not match the established index at all, create a new index.

[0029] In a third aspect, an embodiment of the present application provides a dynamic index optimization system, the system comprising a processor and a memory, the memory storing a computer program, the computer program being loaded and executed by the processor to implement the dynamic index optimization method provided in the first aspect of the embodiment of the present application.

[0030] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, in which a computer program is stored. When the computer program is executed by a processor, it is used to implement the dynamic index optimization method as described in the first aspect of the embodiment of the present application.

[0031] Compared with the prior art, the dynamic index optimization method provided by the embodiment of the present application has the following beneficial technical effects:

[0032] The embodiment of the present application intercepts query operations, records slow queries and regularly analyzes the records to identify performance bottlenecks, that is, operations that take a long time to query, and optimizes indexes accordingly, thereby significantly reducing database query time and improving overall data access efficiency.

[0033] The embodiment of the present application realizes the automation of the query optimization process, reduces the need for human intervention, automatically analyzes query records, determines whether optimization is needed, and automatically adjusts the index structure according to the degree of match between the query field and the index, thereby saving human resources and reducing the possibility of human error.

[0034] The embodiments of the present application adopt different optimization strategies according to different situations of query fields and query durations. For operations with clear query fields and long query durations, index optimization is prioritized. For operations with unclear query fields or low query frequencies, optimization is performed through log reminders or adjustment of query logic to better adapt to different application scenarios and needs.

[0035] The embodiments of the present application accumulate a large amount of query data through log recording and analysis, providing strong support for subsequent performance tuning and fault diagnosis.

[0036] The embodiments of the present application help reduce the IT operating costs of an enterprise by improving database query efficiency and reducing resource consumption. More efficient database access means less hardware resource requirements and lower energy consumption, and can also reduce business interruptions and losses caused by system performance issues.

[0037] For users who rely on databases to provide services, the faster query response speed of the embodiments of the present application means a better user experience. By optimizing database query performance, this method can significantly improve user satisfaction. BRIEF DESCRIPTION OF THE DRAWINGS

[0038] Figure 1 A schematic diagram showing a flow chart of a dynamic index optimization method according to an embodiment of the present application is shown;

[0039] Figure 2 A structural block diagram of a dynamic index optimization device according to an embodiment of the present application is shown;

[0040] Figure 3 A structural block diagram of a computer device according to an embodiment of the present application is shown. DETAILED DESCRIPTION

[0041] In order to make the above-mentioned purposes, features and advantages of the present application more obvious and easy to understand, the specific implementation methods of the present application are described in detail below in conjunction with the accompanying drawings. It is to be understood that the specific embodiments described herein are only used to explain the present application, rather than to limit the present application. It should also be noted that, for ease of description, only some structures related to the present application are shown in the accompanying drawings, rather than all structures. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of this application.

[0042] The terms "including" and "having" and any variations thereof in this application are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device comprising a series of steps or units is not limited to the listed steps or units, but may optionally include steps or units not listed, or may optionally include other steps or units inherent to these processes, methods, products or devices.

[0043] Reference to "embodiments" herein means that a particular feature, structure, or characteristic described in conjunction with the embodiments may be included in at least one embodiment of the present application. The appearance of the phrase in various locations in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment that is mutually exclusive with other embodiments. It is explicitly and implicitly understood by those skilled in the art that the embodiments described herein may be combined with other embodiments.

[0044] like Figure 1As shown, the embodiment of the present application provides a dynamic index optimization method, comprising the following steps:

[0045] Step 101, intercepting a query operation on a database, and obtaining a query data set of the query operation, wherein the query data set includes a set name, a query field, and a query duration.

[0046] Intercepting query records can be achieved through database middleware, proxy servers, or directly at the database level, such as using database triggers or audit plug-ins. These tools can capture all database query requests without modifying the application code.

[0047] Specifically, the embodiment of the present application integrates Spring Boot and MongoDB to intercept the Java program's operations on MongoDB and obtain a query data set. The interception can be for all queries of the entire database, or it can be configured for a specific database, table, or user to meet different monitoring needs. Although intercepting query records has a certain impact on performance, modern database monitoring tools are usually optimized to ensure that this impact is as small as possible. In addition, the granularity of monitoring can be configured to balance monitoring requirements and performance impacts, such as monitoring only slow queries.

[0048] Once a query request is intercepted, key information is extracted, including the collection name, query field, and query duration. The collection name refers to the name of the database or collection in NoSQL databases such as MongoDB, and may be the database name, table name, etc. in SQL databases. The query field refers to the field involved in the query condition, and the query duration refers to the time required to execute the query.

[0049] This data is usually stored in a structured format, such as JSON, XML, or a database table, to facilitate subsequent analysis and processing. In the process of extracting and storing query data, it is necessary to ensure the integrity and accuracy of the data to avoid analysis bias caused by data loss or errors.

[0050] By analyzing the query data set, you can identify which queries are performance bottlenecks and take corresponding optimization measures. Based on the query field and query duration information, you can evaluate the effectiveness of existing indexes and decide whether to create new indexes or adjust existing indexes. Query data sets can also be used for logging and auditing purposes to help track database usage and potential security issues.

[0051] It should be noted that when intercepting and storing query records, relevant privacy protection regulations need to be followed to ensure that sensitive information is not leaked. For regulated industries, it is also necessary to ensure that the collection and use of query data sets comply with industry norms and laws and regulations, such as finance and healthcare.

[0052] In summary, intercepting query records and obtaining query data sets is an important part of database performance monitoring and optimization, which involves multiple aspects such as technical implementation, data extraction, data usage, privacy and compliance.

[0053] Step 102, configure the urgent optimization time and the required optimization time; when the query field is not empty and the query time is less than the urgent optimization time, record the collection name, query field and query time of the query operation; when the query field is not empty and the query time is greater than the urgent optimization time, perform index optimization; when the query field is empty and the query time is greater than the required optimization time, issue a log reminder.

[0054] In the process of database query, the embodiment of the present application will capture all query requests in real time or batches, which include the query fields, that is, the columns or attributes involved in the query conditions and the execution time of the query. This information is the basis for subsequent analysis and optimization.

[0055] The embodiment of the present application classifies query records and records them into logs according to preset rules. These rules are mainly based on two dimensions: query field and query duration:

[0056] The query field is not empty and the query duration is less than the urgent optimization duration: In this case, the query is executed, but the execution time is short and the query purpose is clear because the query field is not empty. Such queries usually do not need to be optimized immediately, but can be recorded as part of normal operation for subsequent analysis or auditing.

[0057] The query field is empty and the query duration is less than the time to be optimized: This type of query represents a broad data retrieval requirement, but the execution time is still within an acceptable range. Similarly, such queries will also be logged, but the priority may be lower than the previous case because they may not directly affect system performance.

[0058] The query field is not empty and the query duration is longer than the urgent optimization duration: This indicates that the query purpose is clear but the execution efficiency is low, which has a great impact on performance. Therefore, it is necessary to immediately trigger the index optimization method to try to improve the execution efficiency of such queries.

[0059] The query field is empty and the query duration is longer than the required optimization duration: This type of query involves a large amount of data scanning, takes a long time to execute, and is inefficient. Although the query field is empty, which makes optimization more difficult, log reminders will still be issued to attract the attention of administrators, and other methods may be used to optimize such queries, such as adjusting the query logic, adding filtering conditions, etc.

[0060] When it is detected that the query field is not empty and the query duration exceeds the urgent optimization duration, the index optimization method will be triggered. First, the query statement that causes the performance problem is analyzed to determine which fields are used in the query conditions and the index status of these fields. Based on the analysis results of the query statement, evaluate whether the existing index strategy is effective and whether it is necessary to create a new index or adjust the structure of the existing index. After determining the index optimization plan, implement corresponding optimization measures, such as creating a new index, deleting useless indexes, or adjusting the order of indexes. After the optimization is completed, re-execute the original query statement to verify whether the optimization effect is as expected. If the optimization effect is not ideal, it may be necessary to re-evaluate and optimize the index strategy.

[0061] The embodiment of the present application allows the user to configure the number of days for log analysis so as to focus only on the query records of the most recent period of time. This helps to identify recent performance bottlenecks and hot queries. The query records are classified according to the collection name and query field to analyze the query mode and performance of specific data sets and query fields. By recording the number of queries for the same query field and calculating the average query duration. This information is a key indicator for evaluating query performance.

[0062] Through the above logic, the embodiment of the present application can automatically identify and solve performance problems in database queries, thereby improving the overall performance of the system and user experience.

[0063] Step 103, obtain the original index record table, process the query data set according to the original index record table, and obtain a processed query data set; configure the optimization threshold, traverse the processed query data set, calculate the processed query data set through slow query, and obtain the calculation result.

[0064] First, the purpose of step 103 is to extract information useful for subsequent optimization analysis from the original index record table, which may include the query field, execution time, involved tables or collections, etc.

[0065] The processing methods include removing invalid or erroneous data through data cleaning, converting the original data into the format required for analysis through data transformation, and merging similar query records through data aggregation to reduce the complexity of subsequent analysis.

[0066] The processing of the original index record table in the embodiment of the present application includes two cases:

[0067] Empty case: When it is detected that the original index record table is empty, it means that no index has been created for the current data set. At this time, check whether the query field with the same collection name meets the prefix matching condition.

[0068] The prefix matching condition is used to determine whether the name of the query field matches the index prefix. If it matches, choose to create a composite index, because the composite index can cover multiple fields and improve query efficiency. If it does not match, only a single index can be created to start the indexing strategy with the minimum cost.

[0069] The prefix matching condition is a preset rule, that is, it matches the index prefix, which is the relationship between the query condition of the current slow query and the built composite index. For example: suppose there is an index containing three fields (A, B, C). The order of this index is If the query condition involves only A, or involves A and B, then the query meets the index prefix because the query condition matches the prefix part of the index in order. If the query skips the previous fields and directly uses B or C, then the query condition does not meet the index prefix.

[0070] In layman's terms, matching the index prefix means that the query conditions must use the fields in the index in order, so that data can be found quickly. If a collection needs to create multiple indexes, first determine whether it is possible to merge the fields that match the prefix matching conditions to create a composite index, which can cover multiple queries and reduce resource consumption.

[0071] Not empty case: If the original index record table is not empty, you need to traverse the table to evaluate the validity of the existing index.

[0072] Conformance of index prefixes For each query field, check whether it conforms to the existing index prefix. If it conforms, remove unnecessary fields in the composite index to reduce index redundancy and size, thereby improving query performance. If it does not conform, you may need to delete the created index and reconsider the index strategy.

[0073] The optimization threshold is a criterion for determining whether a query needs to be optimized. This threshold is usually set based on the system's performance requirements, load capacity, and historical data performance. Configuring the threshold requires comprehensive consideration of multiple factors, such as the average query execution time, system resource availability, and the urgency of business needs. Once set, this threshold will serve as a benchmark for subsequent analysis and optimization.

[0074] By traversing the processed query data set, the performance of each query can be systematically checked and potential performance bottlenecks can be identified. The traversal is usually performed in a certain order, such as from the longest execution time to the shortest, so that the queries with the worst performance are processed first. During the traversal process, each query is judged whether it needs further optimization based on the previously set optimization threshold.

[0075] Slow query calculation refers to a detailed analysis of queries whose execution time exceeds the optimization threshold to determine the cause of their poor performance. This step includes query plan analysis to see how the database executes the query, index usage analysis to check whether appropriate indexes are used or whether there are missing indexes, and data distribution analysis to evaluate whether the distribution of data in the table affects query performance. Through these analyses, the specific causes of poor query performance can be identified.

[0076] In the slow query formula of the present application embodiment, if the query mode is fuzzy query, these queries are not included in the calculation of the average query duration. This is because fuzzy query usually has low performance, and its execution time may be affected by various factors such as the size of the data set, and is not suitable as the main focus of performance optimization.

[0077] The embodiment of the present application determines whether a query is a fuzzy query based on whether a specific SQL operator is used in the query statement, especially the LIKE operator, and its pattern part contains wildcards (such as % or _). In SQL, the LIKE operator is used to search for a specified pattern in a column in the WHERE clause.

[0078] Specifically, first check whether the LIKE operator is used in the query statement. LIKE is a keyword used to implement fuzzy matching in SQL.

[0079] Then check whether the pattern string on the right side of the LIKE operator contains the wildcard characters % or _. % represents any number of characters (including zero characters), and _ represents a single character. If the pattern string contains these wildcard characters, the query is a fuzzy query.

[0080] Here is an example:

[0081] Fuzzy query example: SELECT * FROM users WHERE name LIKE '%John%'. This query will match all records containing "John" in the name field, because the % wildcard allows any characters to appear before and after "John".

[0082] Non-fuzzy query example: SELECT * FROM users WHERE name = 'John'. This query exactly matches the records where the name field is "John" and does not use wildcards, so it is not a fuzzy query.

[0083] The embodiment of the present application also allows the user to configure the query frequency cardinality. If the frequency factor of a query is less than the query frequency cardinality, it will not be included in the calculation of the average query duration. This helps to eliminate the interference of infrequent queries on performance evaluation.

[0084] Specifically, the slow query formula is as follows:

[0085]

[0086] in, Indicates the calculation result; Indicates the average query duration; represents the logarithmic representation of the query data set; represents the fuzzy query factor; Indicates the query frequency factor.

[0087] Finally, through the above steps, a calculation result containing optimization suggestions or optimization results will be obtained. This calculation result may include a list of queries that need to be optimized, optimization suggestions for each query, such as adding indexes, adjusting query logic, etc., and expected optimization effects, such as the reduction in query execution time. This information will serve as the basis for subsequent database optimization work.

[0088] In the slow query formula of the embodiment of the present application, if the query frequency factor is greater than the query frequency base, it means that the query frequency is relatively high and exceeds the preset baseline. In this case, the query frequency will be regarded as an important consideration and may affect the calculation method or result of the average query duration.

[0089] Specifically, the present application provides the following processing methods:

[0090] Weighted calculation: When the query frequency factor is greater than the query frequency cardinality, a higher weight is given to the query duration of the query, so that when calculating the average query duration, the query duration has a greater impact on the final result. This helps identify and focus on queries that occur frequently but have poor performance.

[0091] Threshold triggering: Set a threshold. When the query frequency factor exceeds this threshold, that is, the query frequency base, specific processing logic is triggered, such as logging, sending alerts, or automatically optimizing the query. This mechanism helps to discover and solve potential performance problems in a timely manner.

[0092] Dynamic adjustment: Dynamically adjust the query frequency base or other related parameters based on changes in the query frequency factor. For example, if the frequency of a query continues to increase, the frequency base of the query will be automatically increased to adapt to its new usage pattern.

[0093] Performance evaluation: When the query frequency factor is greater than the query frequency cardinality, a more detailed performance evaluation is performed on the query, including analyzing query statements, checking index usage, evaluating server load, etc., to identify performance bottlenecks and develop corresponding optimization strategies.

[0094] Priority sorting: When resources are limited, queries are sorted based on frequency and performance, with those with high frequency and poor performance being prioritized to improve overall query performance and user experience.

[0095] It should be noted that the specific processing method depends on the specific implementation and overall design of the slow query formula. The above method provided in the embodiment of the present application is only used to illustrate several processing methods when the query frequency factor is greater than the query frequency cardinality, and is not used to limit the protection scope of the embodiment of the present application. In practical applications, the appropriate processing method should be selected according to specific needs and scenarios.

[0096] In summary, this process is a systematic database performance optimization process, which aims to improve the overall performance of the database by analyzing the query data set, identifying performance bottlenecks, and giving specific optimization suggestions or implementing optimization measures.

[0097] Step 104, extract the established indexes recorded in the original index record table, the established indexes include composite indexes, and determine whether the calculation result is greater than the optimization threshold. If the calculation result is greater than the optimization threshold, determine whether the query field matches the established index. If the query field completely matches the established index, perform manual debugging; if the query field matches the prefix of the established index and the length is greater than the established index, expand the composite index; if there is no index for the query data set or the query field does not match the established index at all, create a new index.

[0098] All created indexes in the embodiment of the present application will be recorded in the index record table. The indexes in this table are so-called built indexes. Composite indexes are a type of index. Indexing a field in a collection is a single-key index, and creating an index with multiple fields is a composite index. Built indexes include these two types.

[0099] For example, a composite index has been created for a collection, which is created for fields A, B, and C. When field D is used as the search field for searching, the query time is too long to meet the optimization criteria, and a new index needs to be created. However, it does not conform to the previously created composite indexes A, B, and C, and does not meet the index prefix, so a new index for field D needs to be created. This index has only one field, which is called a single-key index.

[0100] If fields A, B, C, and D are used as search conditions and the query duration reaches the optimization standard, this and the existing composite index satisfy the index prefix. You only need to expand the existing composite index and create a new composite index (A, B, C, and D).

[0101] The embodiment of the present application performs specific operations by determining whether the calculation result is greater than the optimization threshold. This step is the starting point of performance optimization. By comparing the execution time of the query with the optimization threshold, it is determined whether the query needs further optimization. The optimization threshold is usually set according to the performance requirements, load capacity and performance of historical data of the query. The query execution time or other performance indicators are automatically calculated and compared with the optimization threshold.

[0102] If the calculated result is greater than the optimization threshold, it means that the query performance is poor and needs to enter the next step of the optimization process; if it is less than or equal to the optimization threshold, no further optimization is required or it has been sufficiently optimized.

[0103] The embodiment of the present application refines further optimization operations by determining whether the query field matches the index, which is the key to determining the optimization direction. By checking the matching of the fields used in the query with the existing indexes in the database, it can be preliminarily determined whether the cause of poor performance is related to the index.

[0104] By comparing the fields involved in the query with the fields indexed in the database, it is determined whether there is a match, including complete match, incomplete match, and complete mismatch.

[0105] Different matching situations will determine the subsequent optimization strategy. If the performance is still poor despite a complete match, manual debugging may be required; if the match is incomplete or not at all, index optimization needs to be considered.

[0106] If the query field and the index completely match, in this case, although the query field and the index completely match, the query performance is still poor, indicating that there may be other factors affecting the performance. The embodiment of the present application is processed through manual debugging, including but not limited to checking the query logic, analyzing data distribution, and evaluating system resource usage. Manual debugging can help identify and solve non-index related performance problems and improve query efficiency.

[0107] If the query field does not completely match the index, in this case, there is a partial match between the query field and the index, but the query performance may be poor due to the missing or improper order of the index field. The embodiment of the present application is processed by expanding the composite index, that is, adding the missing fields or adjusting the order of the fields on the basis of the existing index to better match the query conditions. Expanding the composite index can improve the coverage and selectivity of the index, thereby reducing the amount of data that needs to be scanned during the query and improving the query performance.

[0108] If the query field does not match the index at all, in this case, the field used in the query has no index support in the database, resulting in poor query performance. The embodiment of the present application creates a new index for processing, and creates an index based on the fields frequently used in the query to optimize query performance. Creating a new index can significantly improve query efficiency, but it may also increase the maintenance cost of the database, such as updating, maintenance and storage space usage of the index. Therefore, it is necessary to weigh the pros and cons when creating a new index.

[0109] In summary, this process is a systematic database performance optimization process, which ultimately improves the database query performance by gradually analyzing and solving problems.

[0110] like Figure 2 As shown, the embodiment of the present application provides a dynamic index optimization device, including a query interception module 201, a log recording module 202, an index management module 203 and an index optimization module 204, wherein:

[0111] The query interception module 201 is configured to intercept a query operation on a database and obtain a query data set of the query operation, wherein the query data set includes a set name, a query field, and a query duration;

[0112] The log recording module 202 is configured to configure the urgent optimization time and the required optimization time; when the query field is not empty and the query time is less than the urgent optimization time, record the collection name, query field and query time of the query operation; when the query field is not empty and the query time is greater than the urgent optimization time, perform index optimization; when the query field is empty and the query time is greater than the required optimization time, perform log reminder;

[0113] The index management module 203 is configured to obtain an original index record table, process the query data set according to the original index record table, and obtain a processed query data set; configure an optimization threshold, traverse the processed query data set, and calculate the processed query data set through a slow query to obtain a calculation result;

[0114] The index optimization module 204 is configured to extract the established indexes recorded in the original index record table, the established indexes including composite indexes, and determine whether the calculation result is greater than the optimization threshold. If the calculation result is greater than the optimization threshold, determine whether the query field matches the established index. If the query field completely matches the established index, perform manual debugging; if the query field matches the prefix of the established index and the length is greater than the established index, expand the composite index; if there is no index for the query data set or the query field does not match the established index at all, create a new index.

[0115] The dynamic index optimization device of the embodiment of the present application can be a computer device, or a component in the computer device, such as an integrated circuit or a chip. The computer device can be a terminal, or other devices other than a terminal. Exemplarily, the computer device can be a mobile phone, a tablet computer, a laptop computer, a palmtop computer, a vehicle-mounted computer device, a mobile Internet device (Mobile Internet Device, MID), an ultra-mobile personal computer (Ultra-Mobile Personal Computer, UMPC), a netbook or a personal digital assistant (Personal Digital Assistant, PDA), etc., and can also be a server, a network attached storage (Network Attached Storage, NAS), a personal computer (Personal Computer, PC), etc., which is not specifically limited in the embodiment of the present application.

[0116] The dynamic index optimization device provided in the embodiment of the present application can achieve Figure 1 To avoid repetition, the various processes implemented in the embodiment of the dynamic index optimization method are not described here.

[0117] The present application also provides a computer device, such as Figure 3 As shown, the computer device includes a processor 301 and a memory 302. The memory 302 stores programs or instructions that can be executed on the processor 301. When the program or instructions are executed by the processor 301, the various steps of the above-mentioned dynamic index optimization method are implemented and the same technical effect can be achieved. To avoid repetition, they are not repeated here.

[0118] It should be noted that the computer device in the embodiment of the present application includes the above-mentioned mobile computer device and non-mobile computer device.

[0119] The memory 302 can be used to store software programs and various data. The memory 302 may mainly include a first storage area for storing programs or instructions and a second storage area for storing data, wherein the first storage area may store an operating system, an application program or instructions required for at least one function (such as a sound playback function, an image playback function, etc.), etc. In addition, the memory 302 may include a volatile memory or a non-volatile memory, or the memory 302 may include both volatile and non-volatile memories. Among them, the non-volatile memory may be a read-only memory (ROM), a programmable read-only memory (PROM), an erasable programmable read-only memory (EPROM), an electrically erasable programmable read-only memory (EEPROM), or a flash memory. The volatile memory may be a random access memory (RAM), a static random access memory (SRAM), a dynamic random access memory (DRAM), a synchronous dynamic random access memory (SDRAM), a double data rate synchronous dynamic random access memory (DDRSDRAM), an enhanced synchronous dynamic random access memory (ESDRAM), a synchronous link dynamic random access memory (SLDRAM) and a direct memory bus random access memory (DRRAM). The memory 302 of the embodiment of the present application includes but is not limited to these and any other suitable types of memory.

[0120] The processor 301 may include one or more processing units; optionally, the processor 301 integrates an application processor and a modem processor, wherein the application processor mainly processes operations related to an operating system, a user interface, and application programs, and the modem processor mainly processes wireless communication signals, such as a baseband processor. It is understandable that the modem processor may not be integrated into the processor 301.

[0121] An embodiment of the present application also provides a readable storage medium, on which a program or instruction is stored. When the program or instruction is executed by a processor, the various processes of the above-mentioned dynamic index optimization method embodiment are implemented, and the same technical effect can be achieved. To avoid repetition, it will not be repeated here.

[0122] An embodiment of the present application also provides a chip, which includes a processor and a communication interface. The communication interface and the processor are coupled, and the processor is used to run programs or instructions to implement the various processes of the above-mentioned dynamic index optimization method embodiment, and can achieve the same technical effect. To avoid repetition, it will not be repeated here.

[0123] It should be understood that the chip mentioned in the embodiments of the present application can also be called a system-level chip, a system chip, a chip system or a system-on-chip chip, etc.

[0124] An embodiment of the present application also provides a computer program product, which is stored in a storage medium. The program product is executed by at least one processor to implement the various processes of the above-mentioned dynamic index optimization method embodiment and can achieve the same technical effect. To avoid repetition, it will not be repeated here.

[0125] It should be noted that, in this article, the terms "comprise", "include" or any other variant thereof are intended to cover non-exclusive inclusion, so that the 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. In the absence of further restrictions, the elements defined by the sentence "comprise one..." do not exclude the presence of other identical elements in the process, method, article or device including the element. In addition, it should be pointed out that the scope of the methods and devices in the embodiments of the present application is not limited to performing functions in the order shown or discussed, and may also include performing functions in a substantially simultaneous manner or in reverse order according to the functions involved, for example, the described method may be performed in an order different from that described, and various steps may also be added, omitted, or combined. In addition, the features described with reference to certain examples may be combined in other examples.

[0126] The embodiments of the present application are described above in conjunction with the accompanying drawings, but the present application is not limited to the above-mentioned specific implementation methods. The above-mentioned specific implementation methods are merely illustrative and not restrictive. Under the guidance of the present application, ordinary technicians in this field can also make many forms without departing from the purpose of the present application and the scope of protection of the claims, all of which are within the protection of the present application.

Claims

1. A dynamic index optimization method, characterized in that: The method comprises: Intercepting a query operation on a database, and obtaining a query data set of the query operation, wherein the query data set includes a set name, a query field, and a query duration; Configure the urgent optimization duration and the required optimization duration; when the query field is not empty and the query duration is less than the urgent optimization duration, record the collection name, query field and query duration of the query operation; when the query field is not empty and the query duration is greater than the urgent optimization duration, perform index optimization; when the query field is empty and the query duration is greater than the required optimization duration, perform a log reminder; Obtaining an original index record table, processing the query data set according to the original index record table to obtain a processed query data set; configuring an optimization threshold, traversing the processed query data set, and calculating the processed query data set through a slow query to obtain a calculation result; Extract the built index recorded in the original index record table, the built index includes a composite index, and determine whether the calculation result is greater than the optimization threshold. If the calculation result is greater than the optimization threshold, determine whether the query field matches the built index. If the query field completely matches the built index, perform manual debugging; if the query field matches the prefix of the built index and its length is greater than the built index, expand the composite index; if the query data set does not have an index or the query field does not match the built index at all, create a new index.

2. A dynamic index optimization method as claimed in claim 1, characterized in that: The step of processing the query data set according to the original index record table to obtain a processed query data set includes: Get the original index record table. If the original index record table is empty, determine whether the query field with the same set name meets the prefix matching condition. If it meets the prefix matching condition, create a composite index; if it does not meet the prefix matching condition, create a single index.

3. A dynamic index optimization method as claimed in claim 1, characterized in that: The step of processing the query data set according to the original index record table to obtain a processed query data set includes: Get the original index record table. If the original index record table is not empty, traverse the original index record table to determine whether the query field with the same set name meets the prefix of the established index. If it meets the prefix of the established index, remove the redundant fields in the composite index; if it does not meet the prefix of the established index, delete the established index.

4. A dynamic index optimization method as claimed in claim 1, characterized in that: The method comprises: Configure the number of days for log analysis, obtain query records in the log according to the number of days for log analysis, and process the query records. The processing method includes classifying according to the collection name and the query field, recording the number of queries for the same query field and obtaining the average query duration.

5. A dynamic index optimization method as claimed in claim 4, characterized in that: The processed query data set is calculated by slow query to obtain a calculation result. The calculation formula of slow query is as follows: ； in, Indicates the calculation result; Indicates the average query duration; represents the logarithmic representation of the query data set; represents the fuzzy query factor; Indicates the query frequency factor.

6. A dynamic index optimization method as claimed in claim 5, characterized in that: In the slow query formula, it is determined whether the query mode is a fuzzy query. If the query mode is a fuzzy query, it does not affect the average query duration.

7. A dynamic index optimization method as claimed in claim 5, characterized in that: In the slow query formula, the query frequency base is configured. If the query frequency factor is less than the query frequency base, the average query duration is not affected.

8. A dynamic index optimization device, characterized in that: The device comprises: A query interception module is configured to intercept a query operation on a database and obtain a query data set of the query operation, wherein the query data set includes a set name, a query field, and a query duration; A log recording module is configured to configure an urgent optimization duration and a required optimization duration; when the query field is not empty and the query duration is less than the urgent optimization duration, record the set name, query field and query duration of the query operation; when the query field is not empty and the query duration is greater than the urgent optimization duration, perform index optimization; when the query field is empty and the query duration is greater than the required optimization duration, perform a log reminder; The index management module is configured to obtain an original index record table, process the query data set according to the original index record table to obtain a processed query data set; configure an optimization threshold, traverse the processed query data set, calculate the processed query data set through a slow query, and obtain a calculation result; The index optimization module is configured to extract the established index recorded in the original index record table, the established index including the composite index, and determine whether the calculation result is greater than the optimization threshold. If the calculation result is greater than the optimization threshold, determine whether the query field matches the established index. If the query field completely matches the established index, perform manual debugging; if the query field matches the prefix of the established index and is longer than the established index, expand the composite index; if the query field does not have an index for the query data set or the query field does not completely match the established index, create a new index.

9. A dynamic index optimization system, the system comprising a processor and a memory, wherein a computer program is stored in the memory, characterized in that: The computer program is loaded and executed by the processor to implement a dynamic index optimization method as described in any one of claims 1 to 7.

10. A computer-readable storage medium, wherein a computer program is stored in the storage medium, characterized in that: When the computer program is executed by a processor, it is used to implement a dynamic index optimization method as described in any one of claims 1 to 7.

Images