Database performance optimization method and device, electronic equipment and storage medium

Abstract

The invention relates to a database performance optimization method and device, electronic equipment and a storage medium, and the method comprises the steps: obtaining query data in a slow query log, carrying out the analysis, and carrying out the matching of a historical query mode in a mode library based on a query mode obtained through analysis, and when the matching is successful, taking the prompt statement combination corresponding to the historical query mode as an effective prompt statement combination, and when the matching is not successful, analyzing the current query mode and generating a new effective prompt statement combination. And respectively testing the query modes based on the optimization suggestions corresponding to the groups of effective prompt statement combinations in an independent test environment, and selecting the effective prompt statement combination corresponding to the query mode with the optimal performance as the optimal prompt statement combination. By means of the technical scheme, the most suitable prompt statement combination can be dynamically provided according to the query modes of different queries, the complex query modes and execution environments can be covered, and intelligent management of database query optimization is achieved.

Description

Technical Field

[0001] The present application relates to the field of database performance optimization, and in particular to a database performance optimization method, device, electronic device and storage medium. Background Art

[0002] In the era of big data and high concurrency, the importance of database optimization has become increasingly prominent. Database optimization is a necessary means to ensure efficient operation of the system and meet user needs. Through database optimization, query performance can be improved, resource usage can be reduced, system stability can be improved, and ultimately user experience and system maintainability can be improved.

[0003] Existing database optimization tools, such as SQLAdvisor and Oracle Tuning Advisor, mainly provide index and execution plan suggestions through static analysis, that is, without performing actual query operations, they analyze the structure of the database, the relationship between tables, the configuration of indexes and other database objects, and provide index and execution plan suggestions based on this. However, when dealing with complex query scenarios (such as multi-table JOINs and nested subqueries), static analysis is prone to misjudgment due to the inability to consider the performance of queries in different execution environments and the inability to dynamically adapt to changes in query patterns. If used improperly, it will lead to a decrease in database query performance. Therefore, a database performance optimization method, device, electronic device and storage medium are urgently needed to solve the above problems. Summary of the invention

[0004] The present application provides a database performance optimization method, device, electronic device and storage medium to improve the query performance of the database.

[0005] In a first aspect, the present application provides a method for optimizing database performance, the method comprising:

[0006] Acquire query data, and parse the query data to obtain a query mode corresponding to the query data;

[0007] Based on the query pattern, a historical query pattern matching the query pattern is obtained in a preset pattern library; each of the historical query patterns in the pattern library has at least one set of corresponding prompt statement combinations, and each set of the prompt statement combinations corresponds to at least one optimization suggestion;

[0008] In the case where the historical query pattern matching the query pattern is obtained, all prompt statement combinations corresponding to the historical query pattern are used as valid prompt statement combinations; in the case where the historical query pattern matching the query pattern is not obtained, the query pattern is parsed and at least one group of valid prompt statement combinations is generated based on the parsing result;

[0009] Based on the optimization suggestions corresponding to each group of the valid prompt statement combinations, the query modes are tested separately and the performance data is recorded. According to the preset selection rules and the performance data of the query mode corresponding to each group of the valid prompt statement combinations, at least one group of the valid prompt statement combinations is selected as the optimal prompt statement combination for the query mode.

[0010] In a second aspect, the present application provides a database performance optimization device, the device comprising:

[0011] The slow log collection module is used to obtain the newly added query data in the slow query log, and parse the query data to obtain the query mode corresponding to the query data;

[0012] A history analysis module, used for obtaining, based on the query pattern, a historical query pattern matching the query pattern in a preset pattern library; each of the historical query patterns in the pattern library has at least one corresponding prompt statement combination, and each of the prompt statement combinations corresponds to at least one optimization suggestion;

[0013] A query analysis module, configured to, when the historical query pattern matching the query pattern is obtained, use all prompt statement combinations corresponding to the historical query pattern as valid prompt statement combinations; when the historical query pattern matching the query pattern is not obtained, parse the query pattern and generate at least one set of valid prompt statement combinations based on the parsing result;

[0014] A testing module is used to test the query modes separately based on the optimization suggestions corresponding to each group of the valid prompt statement combinations and record performance data, and select at least one group of the valid prompt statement combinations as the optimal prompt statement combination for the query mode according to preset selection rules and the performance data of the query mode corresponding to each group of the valid prompt statement combinations.

[0015] In a third aspect, the present application provides an electronic device comprising: at least one communication interface; at least one bus connected to the at least one communication interface; at least one processor connected to the at least one bus; and at least one memory connected to the at least one bus, wherein the processor is configured to execute a database performance optimization method described in the first aspect above.

[0016] In a fourth aspect, the present application further provides a computer storage medium storing computer executable instructions, wherein the computer executable instructions are used to execute the database performance optimization method described in the first aspect of the present application.

[0017] The above technical solution provided by the embodiment of the present application has the following advantages over the prior art: the database performance optimization method provided by the embodiment of the present application, after obtaining the query data and parsing the corresponding query mode, analyzes the historical query mode stored in the mode library, and if there is a historical query mode matching the query mode, the prompt statement combination corresponding to the historical query mode is used as the effective prompt statement combination, otherwise the query mode is parsed and the effective prompt statement combination is generated, and then the query mode is tested and the performance data is recorded based on the optimization suggestions corresponding to each group of effective prompt statement combinations, and the effective prompt statement combination corresponding to the query mode when the performance data is optimal is selected as the optimal prompt statement combination of the query mode. Compared with the problem that static analysis in the prior art cannot consider the performance of the query in different execution environments and cannot dynamically adapt to the changes in the query mode when processing complex query scenarios, and thus is prone to misjudgment and leads to the degradation of database query performance, the technical solution provided by the present application realizes that the most suitable prompt statement combination can be provided as a reference solution for database query performance optimization for different execution environments and different query modes without changing the SQL statement structure in the query data, so it is not easy to cause misjudgment, and improves the applicability and reliability of database query performance optimization. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.

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

[0020] One or more embodiments are exemplarily described by pictures in the corresponding drawings, and these exemplified descriptions do not constitute limitations on the embodiments. Elements with the same reference numerals in the drawings represent similar elements, and unless otherwise stated, the figures in the drawings do not constitute proportional limitations.

[0021] Figure 1 A flowchart of a database performance optimization method provided by an embodiment of the present application;

[0022] Figure 2 A flowchart of a database performance optimization method provided in another embodiment of the present application;

[0023] Figure 3 A schematic diagram of the structure of a database performance optimization device provided in an embodiment of the present application;

[0024] Figure 4 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0025] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. 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.

[0026] The disclosure below provides many different embodiments or examples to implement different structures of the present invention. In order to simplify the disclosure of the present invention, the parts and settings of specific examples are described below. Of course, they are only examples, and the purpose is not to limit the present invention. In addition, the present invention can repeat reference numbers and / or letters in different examples. This repetition is for the purpose of simplification and clarity, and does not itself indicate the relationship between the various embodiments and / or settings discussed.

[0027] In order to solve the technical problem that database optimization tools in the prior art provide index and execution plan suggestions through static analysis, but fail to consider the performance of queries in different execution environments when processing complex query scenarios and fail to dynamically adapt to changes in query patterns, which easily leads to misjudgment and thus causes a decline in database query performance, the present application provides a database performance optimization method that can improve the applicability and reliability of database query performance optimization.

[0028] Figure 1 A database performance optimization method provided in the embodiment of the present application is referred to Figure 1 , a database performance optimization method provided by the present application includes:

[0029] S1: Obtain query data, and parse the query data to obtain a query pattern corresponding to the query data.

[0030] Specifically, the present invention captures the newly added slow query data in the slow query log by real-time monitoring of the slow query log (slow.log) of MySQL. The slow query log is a log for recording slow queries, which refer to database queries whose execution time exceeds a preset threshold. This threshold is usually set by a database administrator and can be adjusted according to actual needs. Recording slow queries in the slow query log helps to identify SQL queries with poor performance and then perform targeted optimization.

[0031] In a feasible embodiment of the present application, the slow.log file of MySQL can be monitored in real time by using the eBPF (extended Berkeley PacketFilter) program in combination with inotify technology to capture the newly added slow query data in the file. The eBPF program can monitor the changes of the slow.log file in the kernel space, and can pass the newly added query data (including but not limited to SQL statements, execution time, time consumption, index usage, etc.) in the slow.log file to the user space in real time for parsing and storage. inotify is a very effective file system event monitoring mechanism that can provide real-time file change notifications for applications such as databases. In the database, inotify can be used to monitor changes in data files, log files, and configuration files.

[0032] In a feasible embodiment of the present application, query data is obtained, and the query data is parsed to obtain a query mode corresponding to the query data. The specific steps are as follows:

[0033] S101: Acquire newly added query data in the slow query log based on a preset acquisition rule, and store the query data in a preset slow query database.

[0034] It should be understood that the above preset acquisition rules can be set according to actual conditions, for example, the changes of the slow.log file can be monitored periodically at a fixed time to obtain new query data, or the changes of the slow.log file can be monitored in real time to obtain new query data, and this application does not limit this. Preferably, the changes of the slow.log file can be monitored in real time.

[0035] like Figure 2 As shown, in a feasible embodiment of the present application, after reading the newly added query data in the slow.log file, before storing the query data in a preset slow query database, the query data may be parsed first to extract SQL statements and their corresponding performance data, wherein the performance data includes but is not limited to at least one of the project parameters such as execution time, time consumption, and whether an index is used, and then the parsed SQL statements and their performance data are stored in the slow query database, so that data can be read from the slow query database at any time to perform comparative analysis before and after optimization, without the need to read the slow.log file in each comparative test.

[0036] S102: extracting query data from the slow query database, and parsing the query data to obtain a query pattern corresponding to the query data.

[0037] Specifically, Figure 2As shown, after the query data is extracted from the slow query database, the SQL pattern is parsed by the SQL parsing tool to analyze the query pattern of the query data. In a feasible embodiment of the present application, the query data can be parsed by using parsing tools including but not limited to sqlparse, antlr, etc., and disassembled into at least one of the features including but not limited to JOIN connection, WHERE condition, etc.

[0038] S2: Based on the query pattern, obtain historical query patterns matching the query pattern in a preset pattern library; each historical query pattern in the pattern library has at least one set of corresponding prompt statement combinations, and each set of prompt statement combinations corresponds to at least one optimization suggestion.

[0039] Specifically, the pattern library is used to store historical query patterns and the hint statement combinations corresponding to the historical query patterns (hereinafter referred to as Hint combinations) to support query pattern matching and reuse. Each hint statement combination corresponds to at least one optimization suggestion as a reference for optimizing query performance. When new query data arrives and its corresponding query pattern is parsed, the same or similar historical query patterns are matched in the pattern library.

[0040] S3: When a historical query pattern matching the query pattern is obtained, all prompt statement combinations corresponding to the historical query pattern are used as valid prompt statement combinations; when a historical query pattern matching the query pattern is not obtained, the query pattern is parsed and at least one set of valid prompt statement combinations is generated based on the parsing result.

[0041] Specifically, Figure 2 As shown, since there are corresponding verified Hint combinations for the historical query patterns in the pattern library, when there is a historical query pattern matching the current query pattern in the pattern library, the Hint combination corresponding to the historical query pattern is directly reused, and the Hint combination corresponding to the historical query pattern is used as the valid Hint combination of the current query pattern. In the case that there is no historical query pattern that completely matches the current query pattern in the pattern library, the current query pattern is parsed to obtain its query characteristics, and then at least one set of new Hint combinations is generated based on the query characteristics as the valid Hint combination of the current query pattern for subsequent testing. It should be understood that the present application does not limit Hint combinations. For example, Hint combinations may include but are not limited to at least one of items such as indexes, connection orders, and parallel processing.

[0042] In a feasible embodiment of the present application, the query pattern is parsed and at least one group of valid prompt sentence combinations is generated based on the parsing result, such as Figure 2 As shown, the specific steps are as follows:

[0043] S301: Analyze the query pattern to obtain an execution plan of the query pattern.

[0044] In a feasible embodiment of the present application, the execution plan of the query mode can be predicted using the EXPLAIN command of MySQL, or the actual execution path and performance data of the query mode can be obtained through the EXPLAIN ANALYZE command. The execution plan of the query mode includes but is not limited to at least one of the query scanning mode (such as full disk scan, index scan), the connection order, and the temporary table creation situation.

[0045] S302: Determine the performance bottleneck of the query mode according to the execution plan.

[0046] Specifically, based on the execution plan parsed in step S301, the corresponding performance bottleneck is identified, and the performance bottleneck includes but is not limited to at least one of index miss, connection order error, temporary table sorting, and file sorting.

[0047] S303: Generate optimization suggestions corresponding to the query mode based on the performance bottleneck.

[0048] Specifically, based on the performance bottleneck determined in step S303, a preliminary optimization suggestion is generated, and the optimization suggestion includes but is not limited to at least one of suggestions such as adding an index, adjusting a connection order, and rewriting a query structure.

[0049] S304: Generate at least one group of valid prompt statement combinations corresponding to the query pattern based on the optimization suggestion.

[0050] Specifically, based on the optimization suggestions generated in step S303, at least one new Hint combination is generated as a valid Hint combination, such as INDEX() or JOIN_ORDER(), and the generated valid Hint combination is used as a candidate solution for subsequent testing.

[0051] S4: Based on the optimization suggestions corresponding to each group of valid prompt statement combinations, the query modes are tested separately and the performance data is recorded. According to the preset selection rules and the performance data of the query modes corresponding to each group of valid prompt statement combinations, at least one group of valid prompt statement combinations is selected as the optimal prompt statement combination of the query mode.

[0052] Specifically, after obtaining each group of valid Hint combinations corresponding to the current query mode, it is necessary to execute the current query mode again based on the optimization suggestions of each group of Hint combinations in turn, and record the performance data of the query mode under each group of Hint combinations during the execution process, and repeat the test multiple times until the optimization suggestions corresponding to all valid Hint combinations have been executed.

[0053] Since the query operation based on each group of Hint combinations needs to occupy certain system resources, if the test is performed directly in the production system, it will inevitably have an adverse effect on the normal operation of the production system, and if the consistency of the system environment before the test cannot be guaranteed, the result deviation caused by the system performance difference cannot be eliminated. Therefore, in a feasible embodiment of the present application, in order to avoid the impact of testing each group of effective Hint combinations on the production system, and at the same time ensure the consistency of the test environment before execution of each group of effective Hint combinations, the steps of testing the query mode and recording the performance data based on the optimization suggestions corresponding to each group of effective prompt statement combinations are performed in an independent test environment independent of the production system. Through multiple rounds of testing, the performance data such as the execution time, resource occupancy (such as CPU, memory, I / O, etc.) and response speed of the query executed based on the optimization suggestions corresponding to each group of effective Hint combinations can be obtained, and the Hint combination corresponding to the optimal query performance is selected as the optimal Hint combination based on the preset selection rules. It should be understood that the present application does not limit the selection rules of the optimal Hint combination. For example, any of the following schemes can be used to select the optimal Hint combination:

[0054] (1) Select the valid Hint combination corresponding to the query pattern with the shortest execution time as the optimal Hint combination;

[0055] (2) Select the effective Hint combination corresponding to the query pattern that occupies the least resources as the optimal Hint combination;

[0056] (3) Assign corresponding weights to the execution time and resource usage respectively, calculate the performance score of the query mode corresponding to each group of valid Hint combinations under the comprehensive consideration of execution time and resource usage, and select the valid Hint combination corresponding to the query mode with the highest score as the optimal Hint combination.

[0057] The database optimization tools used in the prior art (such as SQLAdvisor and Oracle Tuning Advisor) generally lack closed-loop feedback capabilities and are unable to continuously optimize query patterns based on historical query results. For example, after the database optimization tools in the prior art perform static analysis on a query request and give execution plan suggestions, even if the same type of query is encountered again in the future, a complete static analysis is still required to give optimization suggestions. It is impossible to directly use the optimization suggestion results that have been obtained previously, nor is it possible to improve the optimization suggestion results that have been obtained previously. In response to this problem, in a feasible embodiment of the present application, after selecting at least one set of valid prompt statement combinations as the optimal prompt statement combination for the query pattern in step S4, such as Figure 2As shown, the following steps are also included: updating the query mode and the optimal hint statement combination corresponding to the query mode to the pattern library. By storing each optimization result in the pattern library, the existing optimization results in the pattern library can be improved to form a closed-loop feedback system. When similar queries appear in the future, the verified Hint combination in the pattern library can be used first to reduce unnecessary repeated testing. Compared with the existing solutions in the prior art that change the SQL statement structure to achieve optimization, which leads to the need for complex consistency verification, the present application uses Hint combinations to guide the database optimizer to select indexes, connection orders and connection methods, etc., to achieve query optimization without changing the SQL statement structure, avoiding the complex work of consistency verification of the result set, and making the optimization simpler and more efficient. In addition, this application uses Hint combinations to guide the database optimizer for optimization, and can generate corresponding multi-level Hint strategies according to different query scenarios, such as index control (INDEX / NO_INDEX): avoid suboptimal indexes; join order (JOIN_ORDER): manually adjust the execution order of multi-table joins; batch key access (BKA) and multi-range read (MRR): optimize I / O performance; disable ICP: prevent incorrect filter condition pushdown; query block naming (QB_NAME): improve the debugging ability of complex queries, etc., to improve the optimization effect in a targeted manner.

[0058] The database optimization tools used in the prior art use static analysis to provide index and execution plan suggestions, but do not perform actual query operations. Therefore, they cannot compare the performance before and after query optimization, resulting in an inability to intuitively display the performance improvement effect after optimization. In view of this problem, in a feasible embodiment of the present application, after selecting at least one set of valid prompt statement combinations as the optimal prompt statement combination for the query mode in step S4, such as Figure 2 As shown, the following steps are also included: summarizing and comparing the performance data of the query mode before and after optimization based on the optimal prompt statement combination, and generating a report based on the comparison results to show the performance changes before and after optimization.

[0059] Specifically, by comparing the query performance after optimization based on the optimal Hint combination with the query performance before optimization, and organizing the comparison results into a detailed optimization report, it is not only possible to intuitively demonstrate the effect of performance improvement, but also to provide a basis for decision-making for database administrators and development teams. The optimization report not only includes a comparative analysis of execution time and resource consumption before and after optimization, but also can add detailed suggestions corresponding to the Hint combination, such as recommending the creation of a new index to cover the Hint combination used in the optimization process and its specific effects, ensuring that the optimization process is transparent, easy to understand and reuse. It should be understood that the report in this application may include not only the query performance after optimization based on the optimal Hint combination and the query performance before optimization, but also the query performance after optimization based on various effective Hint combinations, so as to intuitively demonstrate the impact of multiple Hint combinations on query performance, and provide database administrators and development teams with more diverse decision-making basis.

[0060] The technical solution provided by the present application obtains and parses the query data in the slow query log, matches the historical query pattern in the pattern library based on the query pattern obtained by the analysis, and uses the Hint combination corresponding to the historical query pattern as the effective Hint combination when the match is successful. When the match is not successful, the current query pattern is parsed and a new effective Hint combination is generated. The optimization suggestions corresponding to the above-mentioned groups of effective Hint combinations are tested in an independent test environment, and the effective Hint combination corresponding to the query pattern with the best query performance is selected as the optimal Hint combination of the current query pattern, and the current query pattern and the optimal Hint combination corresponding to the query pattern are updated to the pattern library to form a closed-loop feedback. Compared with the static analysis method used in the prior art for optimizing database query performance, which cannot consider the performance of queries in different execution environments and cannot dynamically adapt to changes in query patterns, the solution adopted by the present application can dynamically provide the most suitable Hint combination according to the query patterns of different queries, can cover complex query patterns and execution environments, and has a wide range of applications. The solution provided in this application builds a closed-loop feedback system. When encountering a new query, it can match historical query patterns in the pattern library and recommend proven effective Hint combinations, thereby reducing unnecessary repeated testing, providing intuitive optimization reports, and realizing intelligent management of database query optimization.

[0061] Corresponding to the above method embodiment, the present application also provides a database performance optimization device, referring to Figure 3 A database performance optimization device provided in the present application includes: a slow log collection module 301, a history analysis module 302, a query analysis module 303 and a test module 304.

[0062] The slow log collection module 301 is used to obtain the newly added query data in the slow query log, and parse the query data to obtain the query mode corresponding to the query data.

[0063] The historical analysis module 302 is used to obtain historical query patterns matching the query pattern in a preset pattern library based on the query pattern; each historical query pattern in the pattern library has at least one set of corresponding prompt statement combinations, and each set of prompt statement combinations corresponds to at least one optimization suggestion.

[0064] The query analysis module 303 is used to, when a historical query pattern matching the query pattern is obtained, take all prompt statement combinations corresponding to the historical query pattern as valid prompt statement combinations; when a historical query pattern matching the query pattern is not obtained, parse the query pattern and generate at least one group of valid prompt statement combinations based on the parsing result.

[0065] The testing module 304 is used to test the query modes respectively based on the optimization suggestions corresponding to each group of valid prompt statement combinations and record the performance data, and select at least one group of the valid prompt statement combinations as the optimal prompt statement combination of the query mode according to the preset selection rules and the performance data of the query modes corresponding to each group of valid prompt statement combinations.

[0066] In a feasible embodiment of the present application, the test module 304 further includes an update feedback unit, which is used to update the query mode and the optimal prompt statement combination corresponding to the query mode into the mode library.

[0067] In a feasible embodiment of the present application, the above-mentioned database performance optimization device also includes a report generation module, which is used to summarize and compare the performance data of the query mode before and after optimization based on the optimization suggestions corresponding to the optimal prompt statement combination, and generate a report based on the comparison results to show the performance changes before and after optimization.

[0068] like Figure 4 As shown, an embodiment of the present application provides an electronic device, including a processor 411, a communication interface 412, a memory 413 and a communication bus 414, wherein the processor 411, the communication interface 412, and the memory 413 communicate with each other through the communication bus 414, and the memory 413 is used to store computer programs.

[0069] In one embodiment of the present application, the processor 411 is used to implement the database performance optimization method provided by any one of the aforementioned method embodiments when executing the program stored in the memory 413, including:

[0070] Obtain query data, and parse the query data to obtain a query mode corresponding to the query data;

[0071] Based on the query pattern, a historical query pattern matching the query pattern is obtained in a preset pattern library; each historical query pattern in the pattern library has at least one set of corresponding prompt statement combinations, and each set of prompt statement combinations corresponds to at least one optimization suggestion;

[0072] In the case where a historical query pattern matching the query pattern is obtained, all prompt sentence combinations corresponding to the historical query pattern are used as valid prompt sentence combinations; in the case where a historical query pattern matching the query pattern is not obtained, the query pattern is parsed and at least one set of valid prompt sentence combinations is generated based on the parsing result;

[0073] Based on the optimization suggestions corresponding to each group of the valid prompt statement combinations, the query modes are tested separately and the performance data is recorded. According to the preset selection rules and the performance data of the query mode corresponding to each group of the valid prompt statement combinations, at least one group of the valid prompt statement combinations is selected as the optimal prompt statement combination for the query mode.

[0074] An embodiment of the present application further provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the steps of the database performance optimization method provided in any of the aforementioned method embodiments are implemented.

[0075] 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 shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0076] 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 general hardware platform, and of course, by hardware. Based on this understanding, the above technical solution is essentially or the part that contributes to the relevant technology 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.

[0077] It should be understood that the terms used herein are only for the purpose of describing specific example embodiments and are not intended to be limiting. Unless the context clearly indicates otherwise, the singular forms "one", "an" and "said" as used herein may also be meant to include plural forms. The terms "include", "comprise", "contain", and "have" are inclusive, and therefore specify the existence of stated features, steps, operations, elements and / or parts, but do not exclude the existence or addition of one or more other features, steps, operations, elements, parts, and / or combinations thereof. The method steps, processes, and operations described herein are not interpreted as necessarily requiring them to be performed in the specific order described or illustrated, unless the execution order is clearly indicated. It should also be understood that additional or alternative steps may be used.

[0078] The foregoing is merely a specific embodiment of the present invention, which enables those skilled in the art to understand or implement the present invention. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present invention. Therefore, the present invention will not be limited to the embodiments shown herein, but rather to the widest scope consistent with the principles and novel features claimed herein.

Claims

1. A database performance optimization method, characterized in that: include: Acquire query data, and parse the query data to obtain a query mode corresponding to the query data; Based on the query pattern, a historical query pattern matching the query pattern is obtained in a preset pattern library; each of the historical query patterns in the pattern library has at least one set of corresponding prompt statement combinations, and each set of the prompt statement combinations corresponds to at least one optimization suggestion; In the case where the historical query pattern matching the query pattern is obtained, all prompt sentence combinations corresponding to the historical query pattern are used as valid prompt sentence combinations; If the historical query pattern matching the query pattern is not obtained, parsing the query pattern and generating at least one set of valid prompt sentence combinations based on the parsing result; Based on the optimization suggestions corresponding to each group of the valid prompt statement combinations, the query modes are tested separately and the performance data is recorded. According to the preset selection rules and the performance data of the query mode corresponding to each group of the valid prompt statement combinations, at least one group of the valid prompt statement combinations is selected as the optimal prompt statement combination for the query mode.

2. The database performance optimization method according to claim 1, characterized in that: After selecting at least one group of the valid prompt sentence combinations as the optimal prompt sentence combination of the query mode, the method further includes: The query pattern and the optimal prompt statement combination corresponding to the query pattern are updated in the pattern library.

3. The database performance optimization method according to claim 1, characterized in that: Parsing the query pattern and generating at least one set of valid prompt statement combinations based on the parsing result, including: Parsing the query pattern to obtain an execution plan of the query pattern; Determine the performance bottleneck of the query mode according to the execution plan; generating an optimization suggestion corresponding to the query mode based on the performance bottleneck; At least one group of valid prompt statement combinations corresponding to the query mode is generated based on the optimization suggestion.

4. The database performance optimization method according to claim 3, characterized in that: The execution plan includes at least one of a scanning method, a connection order, and a temporary table creation situation; the performance bottleneck includes at least one of an index miss situation, a connection order error situation, a temporary table sort, and a file sort; and the optimization suggestion includes at least one of adding an index, adjusting a connection order, and rewriting a query structure.

5. The database performance optimization method according to claim 1, characterized in that: The steps of testing the query modes respectively based on the optimization suggestions corresponding to each group of the effective prompt statement combinations and recording performance data are performed in a preset independent test environment.

6. The database performance optimization method according to claim 1, characterized in that: After selecting at least one group of the valid prompt sentence combinations as the optimal prompt sentence combination of the query mode, the method further includes: The performance data of the query mode before and after optimization based on the optimal prompt statement combination are summarized and compared, and a report for displaying the performance changes before and after optimization is generated based on the comparison result.

7. The database performance optimization method according to any one of claims 1 to 6, characterized in that: Acquiring query data and parsing the query data to obtain a query mode corresponding to the query data includes: Acquire newly added query data in the slow query log based on a preset acquisition rule, and store the query data in a preset slow query database; The query data is extracted from the slow query database, and the query data is parsed to obtain a query pattern corresponding to the query data.

8. A database performance optimization device, characterized in that: The device comprises: The slow log collection module is used to obtain the newly added query data in the slow query log, and parse the query data to obtain the query mode corresponding to the query data; A history analysis module, used for obtaining, based on the query pattern, a historical query pattern matching the query pattern in a preset pattern library; each of the historical query patterns in the pattern library has at least one corresponding prompt statement combination, and each of the prompt statement combinations corresponds to at least one optimization suggestion; A query analysis module, configured to, when the historical query pattern matching the query pattern is obtained, use all prompt statement combinations corresponding to the historical query pattern as valid prompt statement combinations; when the historical query pattern matching the query pattern is not obtained, parse the query pattern and generate at least one set of valid prompt statement combinations based on the parsing result; A testing module is used to test the query modes separately based on the optimization suggestions corresponding to each group of the valid prompt statement combinations and record performance data, and select at least one group of the valid prompt statement combinations as the optimal prompt statement combination for the query mode according to preset selection rules and the performance data of the query mode corresponding to each group of the valid prompt statement combinations.

9. An electronic device, characterized in that: include: at least one communication interface; at least one bus connected to the at least one communication interface; at least one processor coupled to the at least one bus; At least one memory connected to the at least one bus, wherein the processor is configured to implement the database performance optimization method according to any one of claims 1 to 7.

10. A computer storage medium, characterized in that: Computer executable instructions are stored, and the computer executable instructions are used to execute the database performance optimization method according to any one of claims 1 to 7.

Images