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

Abstract

The embodiment of the invention provides a database performance optimization method and device, equipment, a storage medium and a program product. The method can be used in financial field. According to the method, a query statement corresponding to key performance index data in a to-be-optimized database is obtained, then a query condition in the query statement and a corresponding table index in the to-be-optimized database are compared and matched, and a first table index needing to be optimized and a second table index not needing to be optimized are obtained; and finally recording the first table index and the second table index. According to the technical scheme, the query statement corresponding to the key performance index is obtained, then the query condition in the query statement and the table index of the database are matched and compared, the table index needing to be optimized is obtained, and the technical effects of automatically obtaining the index data to be optimized of the database and improving the performance optimization efficiency of the database are achieved.

Description

Technical Field

[0001] The present application relates to the financial field, and in particular to a method, device, equipment, storage medium and program for optimizing database performance. Background Art

[0002] As the infrastructure of financial services, the payment platform system carries extremely important functions and requires high availability, high performance, and strong security protection capabilities. After the payment platform is online, a large amount of transaction data will be involved, so the database is particularly critical for continuous monitoring and tuning of system performance. Optimizing the response speed and concurrency of the database can significantly improve the overall performance of the platform.

[0003] However, existing technologies often focus on system development and testing and whether the system can be successfully launched, but not enough attention is paid to the performance monitoring of the payment platform system after it is launched. In particular, the monitoring of the system only stays on the normal operation of the system, but the continuous monitoring of the system performance is insufficient. From the system performance monitoring report Automatic Workload Repository (AWR), to report analysis and query tuning, the whole process is fragmented.

[0004] Therefore, how to design an automatic monitoring and tuning method for database performance has become a technical problem that needs to be solved urgently. Summary of the invention

[0005] The embodiments of the present application provide a method, apparatus, device, storage medium and program for optimizing database performance, so as to achieve the technical effect of making database management efficient and intelligent, and improving the overall reliability and responsiveness of the system.

[0006] In a first aspect, an embodiment of the present application provides a method for optimizing database performance, including:

[0007] Obtain the query statement corresponding to the key performance indicator data in the database to be optimized;

[0008] Compare and match the query condition in the query statement with the corresponding table index in the database to be optimized to obtain a first table index that needs to be optimized and a second table index that does not need to be optimized;

[0009] The first table index and the second table index are recorded.

[0010] In a possible implementation, after comparing and matching the query condition in the query statement with the corresponding table index in the database to be optimized to obtain the first table index to be optimized and the second table index not to be optimized, the method further includes:

[0011] Obtain the third table indexes with top N matching ranks in the second table index, where N is a positive integer;

[0012] Compare and match the third table index with the index of the query execution plan in the database to be optimized to obtain the index of the first query execution plan that needs to be optimized and the index of the second query execution plan that does not need to be optimized;

[0013] An index of the first query execution plan and an index of the second query execution plan are recorded.

[0014] In a possible implementation, the method further includes:

[0015] An optimization report is determined according to the first table index and the index of the first query execution plan.

[0016] In a possible implementation manner, the query statement corresponding to the performance indicator data in the database to be optimized is obtained, including:

[0017] Obtain the AWR report of the database to be optimized;

[0018] Parse the query statements corresponding to the key performance indicator data from the AWR report.

[0019] In a possible implementation manner, the query statement corresponding to the performance indicator data in the database to be optimized is obtained, including:

[0020] The AWR report is obtained by using a preset tool at a preset time node.

[0021] In a possible implementation manner, before obtaining the query statement corresponding to the key performance indicator data in the database to be optimized, the method further includes:

[0022] Key performance indicator data that is higher than a preset performance indicator threshold is determined from the multiple performance indicator data in the database to be optimized.

[0023] In a second aspect, an embodiment of the present application provides a database performance optimization device, including:

[0024] An acquisition module is used to obtain query statements corresponding to key performance indicator data in the database to be optimized;

[0025] A matching module, used for comparing and matching the query condition in the query statement with the corresponding table index in the database to be optimized, to obtain a first table index to be optimized and a second table index not to be optimized;

[0026] A recording module is used to record the first table index and the second table index.

[0027] In a possible implementation, after comparing and matching the query condition in the query statement with the corresponding table index in the database to be optimized to obtain the first table index to be optimized and the second table index not to be optimized, the acquisition module is further used to: obtain the third table index with the top N matching rankings in the second table index, where N is a positive integer;

[0028] The matching module is further used to compare and match the third table index with the index of the query execution plan in the database to be optimized, so as to obtain the index of the first query execution plan to be optimized and the index of the second query execution plan not to be optimized;

[0029] The recording module is further configured to record an index of the first query execution plan and an index of the second query execution plan.

[0030] In a possible implementation, the device further includes:

[0031] A determination module is used to determine an optimization report according to the first table index and the index of the first query execution plan.

[0032] In a possible implementation, the acquisition module acquires a query statement corresponding to the performance indicator data in the database to be optimized, specifically for:

[0033] Obtain the AWR report of the database to be optimized;

[0034] Parse the query statements corresponding to the key performance indicator data from the AWR report.

[0035] In a possible implementation, the acquisition module acquires a query statement corresponding to the performance indicator data in the database to be optimized, specifically for:

[0036] The AWR report is obtained by using a preset tool at a preset time node.

[0037] In a possible implementation manner, before obtaining the query statement corresponding to the key performance indicator data in the database to be optimized, the obtaining module is further used to:

[0038] Key performance indicator data that is higher than a preset performance indicator threshold is determined from the multiple performance indicator data in the database to be optimized.

[0039] In a third aspect, an embodiment of the present application provides a device, including: a memory, a processor;

[0040] The memory stores computer-executable instructions;

[0041] The processor executes the computer-executable instructions stored in the memory, so that the processor executes the above first aspect and / or various possible implementations of the first aspect.

[0042] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the first aspect above and / or various possible implementations of the first aspect.

[0043] In a fifth aspect, an embodiment of the present application provides a computer program product, including a computer program, which, when executed by a processor, implements the above first aspect and / or various possible implementation methods of the first aspect.

[0044] The database performance optimization method, device, equipment, storage medium and program provided in the embodiment of the present application obtain the query statement corresponding to the key performance indicator data in the database to be optimized, then compare and match the query conditions in the query statement with the corresponding table index in the database to be optimized, obtain the first table index to be optimized and the second table index not to be optimized, and finally record the first table index and the second table index. In this technical solution, the query statement corresponding to the key performance indicator is obtained, and then the query conditions in the query statement are compared and matched with the table index of the database to obtain the table index to be optimized, so as to achieve the technical effect of automatically obtaining the database index data to be optimized and improving the efficiency of database performance optimization. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0046] Figure 1 Schematic diagram of the process of optimizing the database performance provided in this application Figure 1 ;

[0047] Figure 2 Schematic diagram of the process of optimizing the database performance provided in this application Figure 2 ;

[0048] Figure 3 Schematic diagram of the process of optimizing the database performance provided in this application Figure 3 ;

[0049] Figure 4 A schematic diagram of the structure of the optimized database performance provided by this application;

[0050] Figure 5 A schematic diagram of the structure of the electronic device provided in this application.

[0051] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0052] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0053] First, the terms involved in this application are explained:

[0054] Oracle: A relational database that is widely used in the financial industry.

[0055] Structured Query Language (SQL): is a standard programming language for managing relational databases. It allows users to perform database operations such as data retrieval, insertion, update, deletion, creation and modification of database structures by writing query statements.

[0056] AWR: It is a built-in performance monitoring function of Oracle database, which is used to automatically collect and store database performance data and provide report data of the entire system performance within a time period. Through AWR report, you can analyze the performance of the database system in a specified time period.

[0057] Query statement: It is a command used in the database to retrieve data from a table.

[0058] Execution plan: generated by the Oracle database system, describes how SQL statements are executed, including the execution order of SQL statements, the indexes used, how tables are connected, etc.

[0059] Index: A database structure that allows the server to quickly find a row in a table. Proper use of indexes can greatly improve the operating efficiency of the database.

[0060] Before introducing the embodiments of the present application, the application background of the embodiments of the present application is first explained:

[0061] The payment platform system in the financial field is the core system for processing and managing capital flow, settlement, inter-bank transactions and other businesses. The payment platform not only needs to meet the performance requirements of high concurrency and high availability, but also must ensure the security, accuracy and compliance of transactions. As the infrastructure of financial services, the payment platform system carries extremely important functions. After the payment platform is online, a large amount of transaction data will be involved. Therefore, database performance is particularly critical for the continuous monitoring and improvement of the overall system performance. Optimizing the response speed and concurrency of the database can significantly improve the overall performance of the platform.

[0062] However, existing technologies often focus on system development and testing and whether the system can be successfully launched, but not enough attention is paid to the performance monitoring of the payment platform system after it is launched. In particular, the monitoring of the system only stays on the normal operation of the system, but the continuous monitoring of the system performance is insufficient. From the system performance monitoring report AWR to report analysis and query tuning, the whole process is fragmented.

[0063] The database performance optimization method provided in the present application regularly collects AWR reports, parses out the key performance indicators that exceed the threshold from the AWR reports, automatically obtains the query statements corresponding to the key performance indicators, and then splits the query statements, compares and matches the table indexes and execution plan indexes captured in the database with the corresponding query statements after the split, obtains the table indexes and execution plan indexes that need to be optimized, and finally generates an optimization report. The technical means solves the technical problem of how to design an automatic monitoring and tuning method for database performance.

[0064] It should be noted that the database performance optimization method, device, equipment, storage medium and program of the present application can be used in the financial field or other fields, and can also be used in any field other than the financial field or other fields. The application field of the database performance optimization method and device of the present application is not limited.

[0065] The technical solution of the present application and how the technical solution of the present application solves the above-mentioned technical problems are described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.

[0066] Figure 1 Schematic diagram of the database performance optimization method provided for this application Figure 1 ,like Figure 1 As shown, the method includes:

[0067] S101, obtaining a query statement corresponding to the key performance indicator data in the database to be optimized;

[0068] In this step, software tools are used to obtain key performance indicators in the database to be optimized, so that potential problems in the database can be obtained for adjustment and optimization, and then corresponding query statements are obtained based on the key performance indicators.

[0069] The database is also the database system. Key performance indicators include response time, central processing unit (CPU) utilization, query throughput, database resource utilization, disk read and write, lock and wait, cache hit rate, number of connections, transaction submission and rollback, slow query, availability and fault recovery, replication delay, data growth, index efficiency, error rate, and system health.

[0070] Optionally, before step S201, the method may further include the following implementation:

[0071] Key performance indicator data that is higher than a preset performance indicator threshold is determined from multiple performance indicator data in the database to be optimized.

[0072] In this implementation, multiple performance indicators in the database to be optimized are parsed from the AWR report, and key performance indicator data exceeding the threshold is obtained according to the preset performance indicator threshold.

[0073] Among them, the performance indicator threshold refers to an important part of ensuring the efficient operation of the database system. By defining reasonable thresholds, performance deficiencies can be discovered in a timely manner and appropriate optimization measures can be taken.

[0074] In a possible implementation, if the CPU usage threshold is 80%, exceeding 80% indicates that it is necessary to check whether there are unreasonable queries or excessive concurrent loads; the memory usage threshold is 80%, exceeding 80% may cause operating system swapping or other performance problems; the disk read and write latency threshold is 20ms, exceeding the threshold indicates that the disk may become a bottleneck, and it is necessary to optimize the storage configuration or increase the disk bandwidth; the database cache hit rate threshold is 80%, exceeding the threshold indicates that the cache may not be effectively utilized, and it is necessary to adjust the cache configuration or optimize the query; the lock wait threshold is 50ms, exceeding the threshold may affect the concurrent performance of the system; the SQL execution time threshold is 5s, exceeding the threshold, it is necessary to check whether there are missing indexes, unoptimized queries or too much data.

[0075] S102: Compare and match the query condition in the query statement with the corresponding table index in the database to be optimized to obtain a first table index that needs to be optimized and a second table index that does not need to be optimized;

[0076] In this step, the query conditions in the query statements corresponding to the KPIs exceeding the threshold are compared and matched with the table indexes in the database, so as to determine which table indexes need to be optimized and which do not.

[0077] The first table index refers to a table index with a low comparison match, which means that the table index will reduce the performance of the database system and needs to be optimized. The second table index refers to a table index with a high comparison match score, which means that the table index will not affect the performance of the database system and does not need to be optimized.

[0078] In a possible implementation, a scoring strategy corresponding to the comparison match may be:

[0079] (1) The fields in the query conditions completely match the fields in the index, and the index covers all the fields in the query. The score is 10 points.

[0080] (2) High match: The fields in the query condition match 90% of the fields in the index, and the index covers 80% of the fields in the query, with a score of 8-9 points.

[0081] (2) Partial match: Some fields in the query condition match 80%-90% of the fields in the index, but the index covers less than 80% of the query fields. The score is 7 points (automatically generated based on the coverage and matching degree).

[0082] (3) Mismatch: The fields in the query criteria do not match the fields in the index, or the order of the index fields is not suitable for the sorting of the query criteria. The score is 0-5 points (automatically generated based on the degree of mismatch).

[0083] (4) Invalid index score: The index field is completely irrelevant to the query conditions, or the index type is not suitable for the query conditions (for example, an index created for a low-selectivity field), and the score is 0.

[0084] Specifically, the table index with a score of 8-10 is not required to be optimized, that is, the second table index, and the table index with a score lower than 8 is required to be optimized, that is, the first table index.

[0085] S103: Record the first table index and the second table index.

[0086] In this step, the first table index that does not need to be optimized and the second table index that needs to be optimized are recorded to facilitate the subsequent generation of a database system performance optimization report.

[0087] The method for optimizing database performance provided in the embodiment of the present application obtains the query statement corresponding to the key performance indicator data in the database to be optimized, then compares and matches the query conditions in the query statement with the corresponding table index in the database to be optimized, obtains the first table index that needs to be optimized and the second table index that does not need to be optimized, and finally records the first table index and the second table index. In this technical solution, the query statement corresponding to the key performance indicator is obtained, and then the query conditions in the query statement are compared and matched with the table index of the database to obtain the table index that needs to be optimized, thereby achieving the technical effect of automatically obtaining the table index of the database to be optimized and improving the efficiency of optimizing the table index of the database.

[0088] Based on the above embodiments, Figure 2 Schematic diagram of the process of optimizing the database performance provided in this application Figure 2 ,like Figure 2 As shown, after the above step S102, the method for optimizing database performance may further include:

[0089] S201, obtaining the third table indexes with the top N matching rankings in the second table indexes, where N is a positive integer;

[0090] In this step, the third table indexes with top N matching rankings among the second table indexes that do not need to be optimized are obtained, and the next step of index optimization judgment of the execution plan is performed on the table indexes that do not need to be optimized.

[0091] The third table index refers to the table index of the top N comparison and matching rankings in step S102.

[0092] In a possible implementation, the value of N is 3.

[0093] S202: Compare and match the index of the third table with the index of the query execution plan in the database to be optimized, and obtain the index of the first query execution plan that needs to be optimized and the index of the second query execution plan that does not need to be optimized;

[0094] In this step, the index of the third table and the index of the query execution plan automatically generated by the database are compared and matched to obtain the index of the first query execution plan that needs to be optimized and the index of the second query execution plan that does not need to be optimized.

[0095] In a possible implementation, a scoring strategy corresponding to the comparison match may be:

[0096] 1) High match: The execution plan accurately selects one or more indexes in the table indexes, and the query performance is significantly improved. Score 8-10.

[0097] 2) Moderate match: The execution plan partially uses indexes, but sometimes due to some factors (such as uneven data distribution, poor index selectivity, etc.), the table indexes are not optimally used, resulting in query performance that is not as expected. Score 5-7.

[0098] 3) Low match: The match between the execution plan and the table index is low, and the query performance relies heavily on full table scans or other non-optimal strategies, failing to effectively utilize the indexes in the table. Score 2-6.

[0099] 4) Very low match: The execution plan does not use table indexes at all, query performance is very poor, and it completely relies on full table scans or other less efficient operations. Score 0-1.

[0100] Specifically, the indexes of the execution plans with a comparison matching score of 8-10 do not need to be optimized, and the indexes of the execution plans with a comparison matching score of less than 8 points need to be optimized.

[0101] S203: Record the index of the first query execution plan and the index of the second query execution plan.

[0102] In this step, the index of the first query execution plan that needs to be optimized and the index of the second query execution plan that does not need to be optimized are recorded to facilitate the subsequent generation of a database system performance optimization report.

[0103] Optionally, after the above step S203, the method may further include the following implementation:

[0104] An optimization report is determined according to the first table index and the index of the first query execution plan.

[0105] In this implementation, optimization suggestions for the first table index and the first query execution plan are generated according to the first table index and the first query execution plan, and an optimization report is automatically generated for database system performance optimization.

[0106] The optimization report includes whether there are multi-column queries in the first table index, whether the table index columns are frequently used, avoid indexing frequently updated columns, whether the index of the first query execution plan is appropriate, whether there are unnecessary full table scans in the index of the first query execution plan, whether the index connection type and order of the first query execution plan are appropriate, etc. It is used to analyze the shortcomings of database performance and propose optimization measures.

[0107] The method for optimizing database performance provided in the embodiment of the present application obtains the third table index that matches the top N in the second table index, and then compares and matches the third table index with the index of the query execution plan in the database to be optimized, thereby obtaining the index of the first query execution plan that needs to be optimized and the index of the second query execution plan that does not need to be optimized, and finally records the index of the first query execution plan and the index of the second query execution plan. In this technical solution, the top N table indexes that match the query conditions and the table index are obtained, and then compared and matched with the index of the execution plan of the database, and the index of the execution plan that needs to be optimized and the index of the execution plan that does not need to be optimized are recorded respectively, so as to achieve the technical effect of automatically obtaining the index of the execution plan to be optimized in the database and improving the index optimization efficiency of the execution plan in the database.

[0108] Based on the above embodiments, Figure 3 Schematic diagram of the process of optimizing the database performance provided in this application Figure 3 ,like Figure 3 As shown, the optimization methods for the database performance include:

[0109] S301, obtaining the AWR report of the database to be optimized;

[0110] In this step, obtain the AWR report to analyze important information such as database load, performance deficiencies, and resource usage to facilitate problem troubleshooting and performance tuning.

[0111] Optionally, before the above step S301, the method may also be implemented as follows:

[0112] Use the preset tool to obtain the AWR report at the preset time node.

[0113] In this implementation, the database configuration information to be monitored and the AWR indicators and thresholds to be monitored are configured in the preset tool, and the AWR report is obtained at the preset time node.

[0114] Among them, the preset tools may be OraPerf, SentryOne, etc.

[0115] In a possible implementation, the preset time node is 3 days.

[0116] S302. Analyze the query statements corresponding to the key performance indicator data from the AWR report.

[0117] In this step, find the SQL performance-related part from the overall performance statistics in the AWR report, find the SQL statement corresponding to the key performance indicator, and thus obtain the query statement corresponding to the key performance indicator data.

[0118] Among them, the query statements corresponding to the key performance indicator data can be: the SQL query statements that take the longest time, which may be the source of insufficient database performance; the SQL query statements that occupy the most CPU, which will slow down the response speed of the database; the query statements that rely most on disk reading and writing, which will read a large amount of disk data rather than the data in the cache, seriously affecting database performance.

[0119] The method for optimizing database performance provided in the embodiment of the present application first obtains the AWR report of the database to be optimized, and then parses the query statements corresponding to the key performance indicator data from the AWR report. The technical solution parses the query statements corresponding to the key performance indicator data according to the obtained AWR report, thereby achieving the technical effect of accurately locating the deficiencies of database performance and improving the overall database performance.

[0120] Based on the above embodiments, Figure 4 A schematic diagram of the structure of the database performance optimization device provided in this application, such as Figure 4 As shown, the database performance optimization device includes:

[0121] Acquisition module 401, used to acquire query statements corresponding to key performance indicator data in the database to be optimized;

[0122] A matching module 402 is used to compare and match the query condition in the query statement with the corresponding table index in the database to be optimized, and obtain a first table index that needs to be optimized and a second table index that does not need to be optimized;

[0123] The recording module 403 is used to record the first table index and the second table index.

[0124] In a possible implementation, after comparing and matching the query condition in the query statement and the corresponding table index in the database to be optimized to obtain the first table index to be optimized and the second table index not to be optimized, the acquisition module 401 is further used to: obtain the third table index with the top N matching rankings in the second table index, where N is a positive integer;

[0125] The matching module 402 is further used to compare and match the index of the third table with the index of the query execution plan in the database to be optimized, so as to obtain the index of the first query execution plan to be optimized and the index of the second query execution plan not to be optimized;

[0126] The recording module 403 is further configured to record the index of the first query execution plan and the index of the second query execution plan.

[0127] In a possible implementation, the device further includes:

[0128] The determination module is used to determine the optimization report according to the first table index and the index of the first query execution plan.

[0129] In a possible implementation, the acquisition module 401 acquires a query statement corresponding to the performance indicator data in the database to be optimized, specifically for:

[0130] Get the Automatic Workload Repository (AWR) report for the database to be optimized.

[0131] Parse the query statements corresponding to the key performance indicator data from the AWR report.

[0132] In a possible implementation, the acquisition module 401 acquires a query statement corresponding to the performance indicator data in the database to be optimized, specifically for:

[0133] Use the preset tool to obtain the AWR report at the preset time node.

[0134] In a possible implementation manner, before obtaining the query statement corresponding to the key performance indicator data in the database to be optimized, the obtaining module 401 is further used to:

[0135] Key performance indicator data that is higher than a preset performance indicator threshold is determined from multiple performance indicator data in the database to be optimized.

[0136] The database performance optimization device provided in this embodiment can execute the method provided in the above method embodiment. Its implementation principle and technical effect are similar, and this embodiment will not be described in detail here.

[0137] Based on the above embodiments, Figure 5 This is a schematic diagram of the structure of the electronic device provided in this application. Figure 5 As shown, the electronic device 50 provided in this embodiment includes: at least one processor 501 and a memory 502. Optionally, the device 50 also includes a communication component 503. The processor 501, the memory 502 and the communication component 503 are connected via a bus 504.

[0138] In a specific implementation process, at least one processor 501 executes the computer-executable instructions stored in the memory 502, so that at least one processor 501 executes the above method.

[0139] The specific implementation process of the processor 501 can be found in the above method embodiment, and its implementation principle and technical effect are similar, so this embodiment will not be repeated here.

[0140] In the above embodiments, it should be understood that the processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. A general-purpose processor may be a microprocessor or any conventional processor. The steps of the method disclosed in the invention may be directly implemented as being executed by a hardware processor, or may be executed by a combination of hardware and software modules in the processor.

[0141] The memory may include a high-speed memory (Random Access Memory, RAM), and may also include a non-volatile memory (Non-volatile Memory, NVM), such as at least one disk memory.

[0142] The bus may be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. The bus may be divided into an address bus, a data bus, a control bus, etc. For ease of representation, the bus in the drawings of the present application is not limited to only one bus or one type of bus.

[0143] The present application also provides a computer program product, which includes a computer program. The computer program is stored in a computer-readable storage medium. At least one processor can read the computer program from the computer-readable storage medium. When at least one processor executes the computer program, the above method can be implemented.

[0144] The present application also provides a computer-readable storage medium, in which computer-executable instructions are stored. When a processor executes the computer-executable instructions, the above method is implemented.

[0145] The above-mentioned readable storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic memory, flash memory, magnetic disk or optical disk. The readable storage medium can be any available medium that can be accessed by a general or special-purpose computer.

[0146] An exemplary readable storage medium is coupled to a processor so that the processor can read information from the readable storage medium and write information to the readable storage medium. Of course, the readable storage medium can also be a component of the processor. The processor and the readable storage medium can be located in an application specific integrated circuit (Application Specific Integrated Circuits, referred to as: ASIC). Of course, the processor and the readable storage medium can also exist in the device as discrete components.

[0147] The division of units is only a logical function division, and there may be other divisions in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be an indirect coupling or communication connection through some interface, device or unit, which can be electrical, mechanical or other forms.

[0148] 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 units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0149] In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.

[0150] If the function is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art, or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, server, or network device, etc.) to perform all or part of the steps of the methods of each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc. Various media that can store program codes.

[0151] Those skilled in the art can understand that all or part of the steps of implementing the above-mentioned method embodiments can be completed by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When the program is executed, the steps of the above-mentioned method embodiments are executed; and the aforementioned storage medium includes: ROM, RAM, disk or optical disk and other media that can store program codes.

[0152] Finally, it should be noted that those skilled in the art will readily conceive of other embodiments of the present invention after considering the specification and practicing the invention disclosed herein. The present invention is intended to cover any variations, uses or adaptations of the present invention, which follow the general principles of the present invention and include common knowledge or customary technical means in the art not disclosed by the present invention, are not limited to the precise structure described above and shown in the drawings, and may be modified and changed in various ways without departing from the scope thereof. The scope of the present invention is limited only by the appended claims.

Claims

1. A method for optimizing database performance, characterized in that: The method comprises: Obtain the query statement corresponding to the key performance indicator data in the database to be optimized; Compare and match the query condition in the query statement with the corresponding table index in the database to be optimized to obtain a first table index that needs to be optimized and a second table index that does not need to be optimized; The first table index and the second table index are recorded.

2. The method according to claim 1, characterized in that After comparing and matching the query condition in the query statement with the corresponding table index in the database to be optimized to obtain the first table index to be optimized and the second table index not to be optimized, the method further includes: Obtain the third table indexes with top N matching ranks in the second table index, where N is a positive integer; Compare and match the third table index with the index of the query execution plan in the database to be optimized to obtain the index of the first query execution plan that needs to be optimized and the index of the second query execution plan that does not need to be optimized; An index of the first query execution plan and an index of the second query execution plan are recorded.

3. The method according to claim 2, characterized in that The method further comprises: An optimization report is determined according to the first table index and the index of the first query execution plan.

4. The method according to any one of claims 1 to 3, characterized in that: The query statement corresponding to the performance indicator data in the database to be optimized includes: Obtaining an automatic workload repository AWR report of the database to be optimized; Parse the query statements corresponding to the key performance indicator data from the AWR report.

5. The method according to claim 4, characterized in that The query statement corresponding to the performance indicator data in the database to be optimized includes: The AWR report is obtained by using a preset tool at a preset time node.

6. The method according to any one of claims 1 to 3, characterized in that: Before obtaining the query statement corresponding to the key performance indicator data in the database to be optimized, the method further includes: Key performance indicator data that is higher than a preset performance indicator threshold is determined from the multiple performance indicator data in the database to be optimized.

7. A database performance optimization device, characterized in that: include: An acquisition module is used to obtain query statements corresponding to key performance indicator data in the database to be optimized; A matching module, used for comparing and matching the query condition in the query statement with the corresponding table index in the database to be optimized, to obtain a first table index to be optimized and a second table index not to be optimized; A recording module is used to record the first table index and the second table index.

8. A device, characterized in that: include: Memory, processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory, so that the processor performs the method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 6 when executed by a processor.

10. A computer program product, characterized in that The computer program product comprises a computer program, which is stored in a computer-readable storage medium. At least one processor can read the computer program from the computer-readable storage medium, and when the at least one processor executes the computer program, the method described in any one of claims 1 to 6 can be implemented.

Images