Database optimization method and device, equipment and storage medium

Abstract

The invention provides a database optimization method and device, equipment and a storage medium, and can be applied to the technical field of databases. The method comprises the following steps: acquiring a first running index of a target database query statement running in a target database; under the condition that the first operation index meets a first preset condition, obtaining a data table pointed by the target database query statement and candidate database query statements pointed to the data table; on the basis of an optimization strategy corresponding to the business scene category of the target database query statement, executing candidate optimization operation on the data table to obtain a candidate optimization data table; and obtaining a second operation index of the candidate database query statement operated in the candidate optimization data table, and determining a matched target optimization operation in the candidate optimization operations based on the second operation index so as to optimize the database based on the target optimization operation.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a database optimization method, apparatus, device, medium, and program product. Background Technology

[0002] In the initial stages of system deployment, due to the small data volume, simple business scenarios, and small increases in database tables, database queries executed by the program typically do not affect database execution efficiency. However, as the data volume grows and business scenarios become more complex, inefficient database queries can impact database response speed, necessitating database optimization.

[0003] In related technologies, for database query statements with low execution efficiency, an index can be added to the data table that the statement points to, thereby improving the execution efficiency of the statement. However, a data table may be associated with multiple business scenarios simultaneously, and the same optimization method may produce different optimization effects in different business scenarios. Optimizing only a specific statement has an uncertain effect on the overall database execution efficiency. For example, adding an index 'a' to table A can improve the execution efficiency of database query statement 1 pointing to table A in business scenario 1; but in business scenario 2, where table B is associated with table A, the execution efficiency of database query statement 2 pointing to table B may decrease due to the newly added index 'a' in table A. Therefore, the optimization of statement 1 has an uncertain effect on the overall database execution efficiency. Summary of the Invention

[0004] In view of the above problems, this application provides database optimization methods, apparatus, devices, media and program products to improve database execution efficiency.

[0005] According to a first aspect of this application, a database optimization method is provided, comprising: obtaining a first performance indicator of a database query statement running in a target database; if the first performance indicator satisfies a first preset condition, obtaining a data table pointed to by the database query statement and candidate database query statements pointing to the data table; performing candidate optimization operations on the data table based on an optimization strategy corresponding to the business scenario category of the database query statement to obtain a candidate optimized data table; obtaining a second performance indicator of the candidate database query statements running in the candidate optimized data table, and determining a matching target optimization operation among the candidate optimization operations based on the second performance indicator, so as to optimize the database based on the target optimization operation.

[0006] According to an embodiment of this application, obtaining the execution metrics of a database query statement running in a target database includes: obtaining the execution time cost, which represents the time consumed in executing the database query statement, and the execution hardware cost, which represents the computational resource consumed in executing the database query statement.

[0007] According to an embodiment of this application, determining a matching target optimization operation among candidate optimization operations based on a second operational metric includes: calculating the operational cost score corresponding to the second operational metric; the second operational metric includes the execution time cost representing the time consumption of executing a candidate database query statement and the execution hardware cost representing the computational resource consumption of executing a database query statement, and the operational cost score is proportional to the execution time cost and the execution hardware cost; the operational cost scores corresponding to each candidate optimization operation are arranged in order, and the candidate optimization operation with the lowest operational cost score is selected as the target optimization operation.

[0008] According to embodiments of this application, the business scenario categories include a first category indicating that the data table has no index, and performing candidate optimization operations on the data table, including: adding an index to the data table, adding a composite index to the data table, and adding at least one of an index and a composite index to the data table.

[0009] According to an embodiment of this application, the method further includes: optimizing the candidate database query statement whose second operating metric satisfies the second preset condition when the second operating metric corresponding to any candidate database query statement satisfies the second preset condition.

[0010] According to an embodiment of this application, the business scenario category includes a second category, which indicates that the number of data tables pointed to by the database query statement is N, where N is an integer greater than 1, and there is data with a relationship in the N data tables; performing candidate optimization operations on the data tables includes: creating a new auxiliary data table; writing the data with a relationship into the auxiliary data table so that the candidate database query statement can point to the auxiliary data table.

[0011] According to embodiments of this application, the performance metrics include the continuous running cost of a database query statement, whereby the continuous running cost characterizes the expected usage time of the database query statement.

[0012] A second aspect of this application provides a database optimization apparatus, comprising: a first acquisition module, configured to acquire a first performance indicator of a database query statement running in a target database; a second acquisition module, configured to acquire a data table pointed to by the database query statement and candidate database query statements pointing to the data table, provided that the first performance indicator meets a first preset condition; a simulation optimization module, configured to perform candidate optimization operations on the data table based on optimization strategies corresponding to the business scenario category of the database query statement, thereby obtaining a candidate optimized data table; and a target optimization module, configured to acquire a second performance indicator of the candidate database query statement running in the candidate optimized data table, determine a matching target optimization operation based on the second performance indicator in the candidate optimization operation, and optimize the database based on the target optimization operation.

[0013] A third aspect of this application provides an electronic device, comprising: one or more processors; and a memory for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors perform the methods described above.

[0014] A fourth aspect of this application also provides a computer-readable storage medium having executable instructions stored thereon, which, when executed by a processor, cause the processor to perform the methods described above.

[0015] The fifth aspect of this application also provides a computer program product, including a computer program that, when executed by a processor, implements the above-described method.

[0016] According to the database optimization method, apparatus, device, medium, and program products provided in this application, by obtaining the data table pointed to by the target database query statement to be optimized, and other database query statements pointing to that data table (i.e., candidate database query statements), the optimization process simultaneously considers the impact on the execution efficiency of all database query statements pointing to that data table, thus obtaining the target optimization operation that contributes the most to database execution efficiency. Therefore, it at least partially solves the problem that optimizing only specific statements during database optimization results in uncertain optimization effects on overall database execution efficiency, thereby improving database execution efficiency. Attached Figure Description

[0017] The above-mentioned contents, other objects, features and advantages of this application will become clearer from the following description of embodiments with reference to the accompanying drawings, in which:

[0018] Figure 1 The illustrations depict application scenarios of database optimization methods, apparatus, devices, media, and program products according to embodiments of this application.

[0019] Figure 2 A flowchart illustrating a database optimization method according to an embodiment of this application is shown schematically.

[0020] Figure 3 The illustration shows example diagrams of business scenario categories according to embodiments of this application;

[0021] Figure 4 A schematic diagram illustrating the structure of a database optimization apparatus according to an embodiment of this application is shown; and

[0022] Figure 5 A block diagram schematically illustrates an electronic device suitable for implementing a database optimization method according to an embodiment of this application. Detailed Implementation

[0023] The embodiments of this application will now be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of this application. In the following detailed description, numerous specific details are set forth to provide a thorough understanding of the embodiments of this application for ease of explanation. However, it will be apparent that one or more embodiments may be implemented without these specific details. Furthermore, descriptions of well-known structures and technologies are omitted in the following description to avoid unnecessarily obscuring the concepts of this application.

[0024] The terminology used herein is for the purpose of describing particular embodiments only and is not intended to limit the scope of this application. The terms “comprising,” “including,” etc., as used herein indicate the presence of the stated features, steps, operations, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, or components.

[0025] All terms used herein (including technical and scientific terms) have the meanings commonly understood by those skilled in the art, unless otherwise defined. It should be noted that the terms used herein are to be interpreted in a manner consistent with the context of this specification, and not in an idealized or overly rigid way.

[0026] When using expressions such as "at least one of A, B and C", they should generally be interpreted in accordance with the meaning that is commonly understood by those skilled in the art (e.g., "a system having at least one of A, B and C" should include, but is not limited to, a system having A alone, a system having B alone, a system having C alone, a system having A and B, a system having A and C, a system having B and C, and / or a system having A, B and C, etc.).

[0027] In the technical solution of this application, the collection, storage, use, processing, transmission, provision, disclosure and application of user personal information all comply with the provisions of relevant laws and regulations, necessary processing measures have been taken, and they do not violate public order and good morals.

[0028] In the technical solution of this application, the acquisition, collection, storage, use, processing, transmission, provision, disclosure and application of data all comply with the provisions of relevant laws and regulations, and necessary processing measures have been taken, and do not violate public order and good morals.

[0029] It should be noted that in the embodiments of this application, certain software, components, models and other existing solutions in the industry may be mentioned. These should be regarded as exemplary and are only intended to illustrate the feasibility of implementing the technical solution of this application. However, it does not mean that the applicant has used or necessarily used the solution.

[0030] Figure 1 The illustration shows an application scenario diagram of the database optimization method, apparatus, device, and storage medium according to embodiments of this application.

[0031] like Figure 1 As shown, application scenario 100 according to this embodiment may include terminal devices 101, 102, and 103, network 104, and server 105. Network 104 is used as a medium to provide a communication link between terminal devices 101, 102, and 103 and server 105. Network 104 may include various connection types, such as wired or wireless communication links or fiber optic cables, etc.

[0032] Users can use terminal devices 101, 102, and 103 to interact with server 105 via network 104 to receive or send messages, etc. Various communication client applications can be installed on terminal devices 101, 102, and 103, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social media platform software, etc. (for example only).

[0033] Terminal devices 101, 102, and 103 can be various electronic devices with displays and web browsing capabilities, including but not limited to smartphones, tablets, laptops, and desktop computers.

[0034] Server 105 can be a server that provides various services, such as a backend management server that supports websites browsed by users using terminal devices 101, 102, and 103 (for example only). The backend management server can analyze and process data such as received user requests, and feed back the processing results (such as web pages, information, or data obtained or generated according to user requests) to the terminal devices.

[0035] Users can use terminal devices 101, 102, and 103 to upload offline table data to server 105, so that server 105 can generate corresponding reports and store them in the database based on the uploaded data. Users can also use terminal devices 101, 102, and 103 to process online data transmitted via network 104, and upload the processed data to server 105, so that server 105 can generate corresponding reports and store them in the database based on the uploaded data.

[0036] It should be noted that the database optimization method provided in this application embodiment can generally be executed by server 105. Correspondingly, the database optimization device provided in this application embodiment can generally be located in server 105. The database optimization method provided in this application embodiment can also be executed by a server or server cluster that is different from server 105 and capable of communicating with terminal devices 101, 102, 103 and / or server 105. Correspondingly, the database optimization device provided in this application embodiment can also be located in a server or server cluster that is different from server 105 and capable of communicating with terminal devices 101, 102, 103 and / or server 105.

[0037] It should be understood that Figure 1 The number of terminal devices, networks, and servers shown is merely illustrative. Depending on implementation needs, any number of terminal devices, networks, and servers can be included.

[0038] The following will be based on Figure 1 The described scene, through Figures 2-3 The database optimization method of the embodiments of this application will be described in detail.

[0039] Figure 2 A flowchart illustrating a database optimization method according to an embodiment of this application is shown schematically.

[0040] like Figure 2 As shown, the database optimization method of this embodiment includes operations S210 to S240.

[0041] In operation S210, obtain the first running indicator of the target database query statement running in the target database.

[0042] In operation S220, if the first operating indicator meets the first preset condition, the data table pointed to by the target database query statement and the candidate database query statements pointed to the data table are obtained.

[0043] In operation S230, based on the optimization strategy corresponding to the business scenario category of the target database query statement, candidate optimization operations are performed on the data table to obtain candidate optimized data tables.

[0044] In operation S240, a second running indicator of the candidate database query statement running in the candidate optimization data table is obtained, and a matching target optimization operation is determined in the candidate optimization operation based on the second running indicator, so as to optimize the database based on the target optimization operation.

[0045] In the embodiments of this application, by obtaining the data table pointed to by the target database query statement to be optimized, and other database query statements (i.e., candidate database query statements) pointing to the data table, the impact on the execution efficiency of all database query statements pointing to the data table is considered during the optimization process. This allows for the acquisition of the target optimization operation that contributes the most to the database execution efficiency, thereby solving the problem that optimizing only specific statements during the database optimization process has an uncertain effect on the overall database execution efficiency, and improving the database execution efficiency.

[0046] In the embodiments of this application, the database query statement is a Structured Query Language (SMQL) statement, which is a standard language for accessing and manipulating relational databases. During the initial production run of the system, due to the small user base and relatively simple business scenarios, the increase in database tables is small, and the execution efficiency requirements for the database query statements executed by the program are not high. Even with a full table scan, the execution efficiency can still meet the system's operational requirements. Here, a business scenario refers to a specific business function unit or user operation unit completed by a specific sequence of database query statements. For example, a user login scenario may involve a single-table query on the user table, while an order submission scenario may involve multi-table queries on multiple tables such as the order table and inventory table. As the number of system users increases, the business scenarios and functions become more complex, and the increase in database tables gradually increases, thus gradually increasing the requirements for the execution efficiency of database query statements. In related technologies, indexes can be added or deleted from tables in the database according to the characteristics of the database and the business scenario to improve the execution efficiency of database query statements. However, current methods for optimizing database query execution efficiency typically focus only on low-performance queries. This involves identifying inefficient queries (e.g., those with excessively long execution times) and reducing their execution time by adding or removing indexes on the tables they point to. While this approach optimizes the execution time of low-performance queries, it fails to consider the impact of index additions or deletions on other queries. The same table in a database may be joined with other tables in different business scenarios. For example, adding an index 'a' to table A might improve the execution efficiency of query 1 (pointing to table A) in business scenario 1. However, in business scenario 2, where table B is joined to table A, the execution efficiency of query 2 (pointing to table B) might decrease due to the newly added index 'a' in table A. Therefore, optimizing query 1 has an uncertain impact on overall database execution efficiency, and could even potentially reduce it.

[0047] In operation S210, the first running metrics of the target database query statement running in the target database are obtained, including: obtaining the execution time cost that represents the time consumed in executing the target database query statement and the execution hardware cost that represents the computational resource consumed in executing the database query statement.

[0048] In the embodiments of this application, the first performance indicator characterizes the execution cost of the target database query statement to be evaluated. In related technologies, the execution time of a database query statement is typically used as the sole criterion for determining whether a database query statement needs optimization. However, as the amount of data in the database increases, judging whether a database query statement needs optimization solely based on execution time has limitations. Therefore, this application embodiment additionally introduces execution hardware cost as a performance indicator to evaluate the performance of the target database query statement, in order to avoid performance bottlenecks in scenarios with large data volumes and high concurrency, and to ensure database execution efficiency and stability.

[0049] In the embodiments of this application, the execution hardware cost may include the computation cost, memory cost, disk read / write cost, etc. consumed by the execution process of the target database query statement. The computation cost can be represented by the utilization rate of the processor (e.g., CPU) during the execution process of the target database query statement. The memory cost can be represented by the amount or utilization rate of RAM (Random Access Memory) during the execution process of the target database query statement. The disk read / write cost can be represented by the amount of data read / written on the disk during the execution process of the target database query statement.

[0050] In some implementations, execution time cost and execution hardware cost can be quantified into execution time cost score and execution hardware cost score, respectively, and the two scores can be weighted and summed to obtain the runtime cost score. The runtime cost score is then used to determine whether the target database query statement needs optimization. In this case, the execution time cost score can be mapped to the execution time of the target database query statement, and the execution hardware cost score can be obtained by weighted summing of the computation cost score, memory cost score, and disk read / write cost score. The computation cost score can be represented by the average CPU utilization during the execution of the target database query statement, the memory cost score can be mapped to the RAM capacity utilization during the execution of the target database query statement, and the disk read / write cost score can be mapped to the amount of data read / written on the disk during the execution of the target database query statement.

[0051] In other implementations, the execution time cost and execution hardware cost can be judged separately. As long as one of them meets the preset conditions, it can be determined that the target database query statement can be optimized.

[0052] In operation S220, the first operating indicator meets the first preset condition, which means that the corresponding target database query statement can be optimized.

[0053] When the first performance indicator is represented by the performance cost score, the first preset condition can be that the performance cost score is greater than a set threshold (the lower the score, the less cost is consumed and the better the execution efficiency of the database query statement). This indicates that the target database query statement has consumed more time and / or hardware costs than allowed, which may affect the overall execution efficiency of the database and needs to be optimized.

[0054] If the first performance indicator does not use a performance cost score, the execution time cost, computation cost, memory cost, and disk read / write cost can be assessed separately. If at least one cost meets a preset condition, the target database query statement is deemed to require optimization. In this case, the first preset condition may include: the execution time of the target database query statement is greater than a time threshold; the average CPU utilization during the execution of the target database query statement is greater than a CPU utilization threshold; the RAM capacity utilization during the execution of the target database query statement is greater than a RAM capacity utilization threshold; and the amount of data read / written to disk during the execution of the target database query statement is greater than a disk read / write data volume threshold.

[0055] If the first operating metric meets the first preset condition, the target database query statement is determined to be the database query statement that needs to be optimized. The data table pointed to by the target database query statement and other database query statements that point to the data table are obtained. That is, the database query statements that need to operate on the data table during the execution process. In order to monitor the execution efficiency of each database query statement at the same time during the optimization process, it is possible to avoid optimizing only a single database query statement and affecting the execution efficiency of other database query statements.

[0056] In the embodiments of this application, different business scenarios are categorized to adopt targeted optimization strategies for different business scenarios. In operation S230, the business scenario categories can be divided into two main categories based on whether the data table has an index: indexed and non-indexed. For the case with an index, it can be further divided into two categories: single-table index and multi-table inner-join index.

[0057] Figure 3 The diagram illustrates example business scenario categories according to embodiments of this application. See also Figure 3In the embodiments of this application, the business scenario categories are divided into six categories, from Category 1 to Category 6. Category 1 refers to cases where the data table has no index; Category 2 refers to cases where the data table has an index and the index on a single table does not exceed a set number of fields (e.g., 5 fields); Category 3 refers to cases where the data table has an index and the index on a single table exceeds a set number of fields (e.g., 5 fields); Category 4 refers to cases where the data table has an index and no more than a set number of data tables (e.g., 3 tables) are inlined; Category 5 refers to cases where the data table has an index and more than a set number of data tables (e.g., 3 tables) are inlined; and Category 6 refers to other cases not included in the above categories. Inlined data tables (multi-table inline) mean that the data tables are linked together through an inner join operation to extract related and complete information from multiple tables.

[0058] In the embodiments of this application, different categories have different preset optimization strategies. To fully evaluate the impact of different optimization schemes on execution efficiency, the optimization strategy corresponding to each business scenario category may include multiple specific candidate optimization operations to be performed on the data table. For example, for category one, when the table pointed to by the target database query statement does not have an index, the candidate optimization operations to be performed on the data table may include: adding an index to the data table, adding a composite index to the data table, and adding at least one of an index and a composite index to the data table. Index optimization is a process that needs to be weighed according to the specific business scenario. By evaluating the running indicators of adding an index (adding a single-column index, suitable for scenarios where the query condition only involves that column), adding a composite index (adding a multi-column index, creating an index on multiple columns, suitable for scenarios where multiple columns are sorted or multiple columns need to be queried at the same time), and adding both an index and a composite index (suitable for scenarios that require both single-column and multi-column indexes), the comprehensive impact of different business scenarios and different database query statements on execution efficiency is fully considered, ensuring that the optimization results can have a positive effect on the execution performance of the database.

[0059] In operation S240, for one or more candidate optimization data tables obtained by performing one or more candidate optimization operations in operation S230, a second running indicator is evaluated for all database query statements pointing to the table, i.e., those related to the table, to determine the contribution of the candidate optimization operation to the database execution efficiency. Then, the candidate optimization operation with the largest contribution to execution efficiency can be selected as the matching target optimization operation, and the target optimization operation is actually executed on the database to optimize its operating efficiency. The second running indicator here can be the same as the aforementioned first running indicator, or one or more indicators (e.g., time) can be selected for separate evaluation according to actual needs. In the embodiments of this application, the second running indicator is the same as the first running indicator, both evaluating the running cost of database query statements based on running cost scores. At this time, determining the matching target optimization operation among the candidate optimization operations based on the second running indicator includes: calculating the running cost score corresponding to the second running indicator; the second running indicator includes the execution time cost representing the time consumption of executing the candidate database query statement and the execution hardware cost representing the computational resource consumption of executing the database query statement, and the running cost score is proportional to the execution time cost and execution hardware cost; the running cost scores corresponding to each candidate optimization operation are arranged in order, and the candidate optimization operation with the lowest running cost score is selected as the target optimization operation.

[0060] In the embodiments of this application, the execution cost score integrates the execution time cost and the execution hardware cost, and optimizes the database from a dual cost perspective, achieving a balance between execution efficiency and resource efficiency, and ensuring the execution efficiency of the database.

[0061] In the embodiments of this application, for the optimization strategy of category one, the following steps can be performed in sequence to analyze the execution cost of all candidate database query statements under different conditions, thereby deriving the optimal optimization scheme.

[0062] The first step is to obtain the runtime cost score of all candidate database query statements, assuming no indexes are currently created.

[0063] The second step is to create an index (single-column index) on the data table and obtain the runtime cost score of all candidate database query statements.

[0064] The third step is to create a composite index (multi-column index) on the data table and obtain the runtime cost score of all candidate database query statements.

[0065] The fourth step is to create indexes (single-column indexes) and composite indexes (multi-column indexes) on the data table and obtain the runtime cost scores of all candidate database query statements.

[0066] The fifth step, based on the second step, is to further analyze and optimize the candidate database query statements whose running cost scores do not meet the set conditions (e.g., less than the set threshold) so that the running cost scores of all candidate database query statements meet the set conditions (e.g., less than the set threshold). Then, the running cost scores of all candidate database query statements are obtained.

[0067] The sixth step, based on the third step, is to further analyze and optimize the candidate database query statements whose running cost scores do not meet the set conditions (e.g., less than the set threshold) so that the running cost scores of all candidate database query statements meet the set conditions (e.g., less than the set threshold). Then, the running cost scores of all candidate database query statements are obtained.

[0068] Step 7: Based on step 4, further analyze and optimize the candidate database query statements whose running cost scores do not meet the set conditions (e.g., less than the set threshold) so that the running cost scores of all candidate database query statements meet the set conditions (e.g., less than the set threshold). Then obtain the running cost scores of all candidate database query statements.

[0069] Step 8: Combine the running cost scores obtained from steps 1 to 7 to select the optimal optimization operation. The selection criteria can be that the total running cost score of each candidate database query statement is the lowest, or that each candidate database query statement meets the set conditions (e.g., less than a set threshold).

[0070] In the embodiments of this application, when the second running metric corresponding to any candidate database query statement meets the second preset condition, the candidate database query statement whose second running metric meets the second preset condition is optimized. This method corresponds to the operation steps five to seven above. After completing the first optimization, for database query statements that still do not meet the optimization conditions after optimization, the statement itself is further optimized to reduce the running cost score of the database query statement. This avoids the impact of optimization measures such as adding indexes on the execution efficiency of individual slow database query statements on the execution efficiency of other related database query statements, thereby further improving the execution efficiency of the database.

[0071] For the optimization strategy in category two, the runtime cost score of all candidate database query statements can be obtained without performing additional operations on the table. Alternatively, candidate database query statements whose runtime cost scores do not meet the set conditions (e.g., are less than a set threshold) can be further analyzed and optimized to ensure that the runtime cost score of all candidate database query statements meets the set conditions (e.g., is less than a set threshold). Then, the runtime cost score of all candidate database query statements is obtained. The optimal optimization operation is selected based on the runtime cost scores obtained in both cases.

[0072] For optimization strategies in category three, the following approaches can be used: First, obtain the runtime cost score of all candidate database queries without performing additional operations on the table. Second, reduce the number of fields in indexes that exceed a set threshold (e.g., 5 fields) to no more than a set threshold (e.g., 5 fields) and obtain the runtime cost score of all candidate database queries. Third, based on the first two approaches, further analyze and optimize candidate database queries whose runtime cost scores do not meet the set conditions (e.g., are less than a set threshold) to ensure that the runtime cost score of all candidate database queries meets the set conditions (e.g., is less than a set threshold), and then obtain the runtime cost score of all candidate database queries. The optimal optimization operation is selected based on the runtime cost scores obtained from these four approaches.

[0073] For the optimization strategy in category four, the runtime cost score of all candidate database query statements can be obtained without performing additional operations on the table. Alternatively, candidate database query statements whose runtime cost scores do not meet the set conditions (e.g., are less than a set threshold) can be further analyzed and optimized to ensure that the runtime cost scores of all candidate database query statements meet the set conditions (e.g., are less than a set threshold). Then, the runtime cost scores of all candidate database query statements are obtained. The optimal optimization operation is selected based on the runtime cost scores obtained in both cases.

[0074] For optimization strategies in category five, the runtime cost scores of all candidate database query statements can be obtained without performing additional operations on the tables. For scenarios involving more than a set number of tables (e.g., 3 tables) inline joins, optimization is performed to reduce the number of inline joins to no more than the set number (e.g., 3), and the runtime cost scores of all candidate database query statements are obtained. Based on the first two cases, candidate database query statements whose runtime cost scores do not meet the set conditions (e.g., less than a set threshold) are further analyzed and optimized to ensure that the runtime cost scores of all candidate database query statements meet the set conditions (e.g., less than a set threshold), and then the runtime cost scores of all candidate database query statements are obtained. The optimal optimization operation is selected from the runtime cost scores obtained through these four cases.

[0075] In the embodiments of this application, for scenarios involving multiple table inlines, where the number of data tables pointed to by the business scenario category indicating the target database query statement is N, where N is an integer greater than 1, and there is related data in the N data tables, the number of inlines can be optimized to not exceed a set number. This can be achieved by creating an auxiliary data table, specifically including: creating an auxiliary data table; writing the related data into the auxiliary data table so that the candidate database query statement can point to the auxiliary data table.

[0076] In the embodiments of this application, for inline scenarios with more than a set number of tables (e.g., 3), it is first necessary to confirm with the business department whether the number of inline tables can be optimized. If so, the related data can be written into the newly created auxiliary data table by creating an auxiliary data table, so that the execution of related database query statements can directly point to the newly created auxiliary data table instead of pointing to multiple related data tables, thereby improving the execution efficiency of database query statements.

[0077] In the embodiments of this application, candidate database query statements whose running cost scores do not meet the set conditions (e.g., less than the set threshold) are further analyzed and optimized. This can be done by adding or deleting indexes to the data table pointed to by the candidate database query statement, and by adding or reducing the amount of data in the data table. By trying different methods such as adding or deleting indexes, adding or reducing the amount of data, the solution that can achieve the best execution efficiency can be deduced.

[0078] In the embodiments of this application, the creation of indexes, the optimization of database query statements, and the optimization of reducing index fields can be achieved using pre-trained deep learning models. Deep learning models can autonomously attempt and analyze different optimization methods to determine the optimal solution with the lowest cost.

[0079] In some implementations, the database operation data after the target optimization operation can be further collected to monitor and collect the actual execution results of the implementation plan. The collected data may include indicators such as the average transaction response time and average processing time of one or more business scenarios before and after optimization, and the relevant advantages and disadvantages of the target optimization operation can be obtained. Then, it can be used as training data to train a deep learning model and further optimize the model performance.

[0080] In embodiments of this application, the operational metrics may further include the continuous operational cost of the target database query statement, which characterizes the expected usage time of the target database query statement. The deep learning model can further predict, based on historical incremental data and optimization results, the data increment of each data table within a future period, and the sustainable usage time of the corresponding target optimization operation, i.e., the time during which the optimized database query statement can achieve high execution efficiency (i.e., the first operational metric of the database query statement does not meet the first preset condition). This guides the deep learning model to select sustainable optimization strategies with low optimization frequency, avoiding frequent optimizations that could affect database stability, and ensuring database operational stability while improving database execution efficiency.

[0081] Based on the above database optimization method, this application also provides a database optimization apparatus. The following will combine... Figure 4The device is described in detail.

[0082] Figure 4 A schematic block diagram of a database optimization apparatus according to an embodiment of this application is shown.

[0083] like Figure 4 As shown, the database optimization device 400 of this embodiment includes a first acquisition module 410, a second acquisition module 420, a simulation optimization module 430, and a target optimization module 440.

[0084] The first acquisition module 410 is used to acquire the first running indicators of the target database query statement running in the target database. In one embodiment, the first acquisition module 410 can be used to perform the operation S210 described above, which will not be repeated here.

[0085] The second acquisition module 420 is used to acquire, when the first operating indicator meets the first preset condition, the data table pointed to by the target database query statement, and the candidate database query statements pointing to the data table. In one embodiment, the second acquisition module 420 can be used to execute the operation S220 described above, which will not be repeated here.

[0086] The simulation optimization module 430 is used to perform candidate optimization operations on the data table based on the optimization strategy corresponding to the business scenario category of the target database query statement, thereby obtaining a candidate optimized data table. In one embodiment, the simulation optimization module 430 can be used to execute the operation S230 described above, which will not be repeated here.

[0087] The target optimization module 440 is used to obtain a second running indicator of the candidate database query statement running in the candidate optimization data table, and determine a matching target optimization operation based on the second running indicator, so as to optimize the database based on the target optimization operation. In one embodiment, the target optimization module 440 can be used to execute the operation S240 described above, which will not be repeated here.

[0088] According to an embodiment of this application, the first acquisition module 410 is further configured to acquire the execution time cost representing the time consumed in executing the target database query statement and the execution hardware cost representing the computational resource consumed in executing the database query statement.

[0089] According to an embodiment of this application, the target optimization module 440 is further used to calculate the operating cost score corresponding to the second operating indicator; the second operating indicator includes the execution time cost representing the time consumption of executing the candidate database query statement and the execution hardware cost representing the computational resource consumption of executing the database query statement, and the operating cost score is proportional to the execution time cost and the execution hardware cost; the operating cost scores corresponding to each candidate optimization operation are arranged in order, and the candidate optimization operation with the lowest operating cost score is taken as the target optimization operation.

[0090] According to embodiments of this application, the business scenario category includes a first category indicating that the data table has no index. The simulation optimization module 430 is also used to add an index to the data table, add a composite index to the data table, and add at least one of an index and a composite index to the data table.

[0091] According to an embodiment of this application, the simulation optimization module 430 is further configured to optimize the candidate database query statement whose second operating metric satisfies the second preset condition when the second operating metric corresponding to any candidate database query statement satisfies the second preset condition.

[0092] According to an embodiment of this application, the business scenario category includes a second category, which indicates that the number of data tables pointed to by the target database query statement is N, where N is an integer greater than 1, and there is data with a relationship in the N data tables; the simulation optimization module 430 is also used to create an auxiliary data table; write the data with a relationship into the auxiliary data table so that the candidate database query statement can point to the auxiliary data table.

[0093] According to an embodiment of this application, the performance metrics include the continuous execution cost of the target database query statement, whereby the continuous execution cost characterizes the expected usage time of the target database query statement.

[0094] According to embodiments of this application, any plurality of modules among the first acquisition module 410, the second acquisition module 420, the simulation optimization module 430, and the target optimization module 440 can be combined into one module, or any one of these modules can be split into multiple modules. Alternatively, at least part of the functionality of one or more of these modules can be combined with at least part of the functionality of other modules and implemented in one module. According to embodiments of this application, at least one of the first acquisition module 410, the second acquisition module 420, the simulation optimization module 430, and the target optimization module 440 can be at least partially implemented as hardware circuitry, such as a field-programmable gate array (FPGA), a programmable logic array (PLA), a system-on-a-chip, a system-on-a-substrate, a system-on-package, an application-specific integrated circuit (ASIC), or implemented in hardware or firmware by any other reasonable means of integrating or packaging the circuitry, or implemented in any one of the three implementation methods of software, hardware, and firmware, or in a suitable combination of any of these. Alternatively, at least one of the first acquisition module 410, the second acquisition module 420, the simulation optimization module 430, and the target optimization module 440 may be at least partially implemented as a computer program module, which can perform corresponding functions when the computer program module is run.

[0095] Figure 5A block diagram schematically illustrates an electronic device suitable for implementing a database optimization method according to an embodiment of this application.

[0096] like Figure 5 As shown, an electronic device 500 according to an embodiment of this application includes a processor 501, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 502 or a program loaded from a storage portion 508 into a random access memory (RAM) 503. The processor 501 may include, for example, a general-purpose microprocessor (e.g., a CPU), an instruction set processor and / or an associated chipset and / or a special-purpose microprocessor (e.g., an application-specific integrated circuit (ASIC)), etc. The processor 501 may also include onboard memory for caching purposes. The processor 501 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of this application.

[0097] RAM 503 stores various programs and data required for the operation of electronic device 500. Processor 501, ROM 502, and RAM 503 are interconnected via bus 504. Processor 501 executes various operations of the method flow according to embodiments of this application by executing programs in ROM 502 and / or RAM 503. It should be noted that the programs may also be stored in one or more memories other than ROM 502 and RAM 503. Processor 501 may also execute various operations of the method flow according to embodiments of this application by executing programs stored in said one or more memories.

[0098] According to embodiments of this application, the electronic device 500 may further include an input / output (I / O) interface 505, which is also connected to a bus 504. The electronic device 500 may also include one or more of the following components connected to the I / O interface 505: an input section 506 including a keyboard, mouse, etc.; an output section 507 including a cathode ray tube (CRT), liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 508 including a hard disk, etc.; and a communication section 509 including a network interface card such as a LAN card, modem, etc. The communication section 509 performs communication processing via a network such as the Internet. A drive 510 is also connected to the I / O interface 505 as needed. A removable medium 511, such as a disk, optical disk, magneto-optical disk, semiconductor memory, etc., is installed on the drive 510 as needed so that computer programs read from it can be installed into the storage section 508 as needed.

[0099] This application also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiments; or it may exist independently and not assembled into the device / apparatus / system. The computer-readable storage medium carries one or more programs, which, when executed, implement the method according to the embodiments of this application.

[0100] According to embodiments of this application, the computer-readable storage medium can be a non-volatile computer-readable storage medium, such as including but not limited to: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof. In this application, the computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device. For example, according to embodiments of this application, the computer-readable storage medium may include ROM 502 and / or RAM 503 and / or one or more memories other than ROM 502 and RAM 503 described above.

[0101] Embodiments of this application also include a computer program product comprising a computer program containing program code for performing the methods shown in the flowchart. When the computer program product is run on a computer system, the program code is used to enable the computer system to implement the database optimization method provided in the embodiments of this application.

[0102] When the computer program is executed by the processor 501, it performs the functions defined in the system / apparatus of this application embodiment. According to the embodiments of this application, the systems, apparatuses, modules, units, etc., described above can be implemented by computer program modules.

[0103] In one embodiment, the computer program may rely on a tangible storage medium such as an optical storage device or a magnetic storage device. In another embodiment, the computer program may also be transmitted and distributed in the form of signals over a network medium, and may be downloaded and installed via the communication section 509, and / or installed from a removable medium 511. The program code contained in the computer program can be transmitted using any suitable network medium, including but not limited to: wireless, wired, etc., or any suitable combination thereof.

[0104] In such an embodiment, the computer program can be downloaded and installed from a network via communication section 509, and / or installed from removable medium 511. When the computer program is executed by processor 501, it performs the functions defined in the system of this application embodiment. According to embodiments of this application, the systems, devices, apparatuses, modules, units, etc., described above can be implemented by computer program modules.

[0105] According to embodiments of this application, program code for executing the computer programs provided in the embodiments of this application can be written in any combination of one or more programming languages. Specifically, these computational programs can be implemented using high-level procedural and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages ​​include, but are not limited to, languages ​​such as Java, C++, Python, "C", or similar programming languages. The program code can be executed entirely on the user's computing device, partially on the user's device, partially on a remote computing device, or entirely on a remote computing device or server. In cases involving remote computing devices, the remote computing device can be connected to the user's computing device via any type of network, including a local area network (LAN) or a wide area network (WAN), or it can be connected to an external computing device (e.g., via the Internet using an Internet service provider).

[0106] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of this application. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in a block diagram or flowchart, and combinations of blocks in a block diagram or flowchart, may be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0107] Those skilled in the art will understand that the features described in the various embodiments and / or claims of this application can be combined or combined in various ways, even if such combinations or combinations are not explicitly described in this application. In particular, the features described in the various embodiments and / or claims of this application can be combined or combined in various ways without departing from the spirit and teachings of this application. All such combinations and / or combinations fall within the scope of this application.

[0108] The embodiments of this application have been described above. However, these embodiments are merely illustrative and not intended to limit the scope of this application. Although various embodiments have been described above, this does not mean that the measures in the various embodiments cannot be used advantageously in combination. The scope of this application is defined by the appended claims and their equivalents. Various substitutions and modifications can be made by those skilled in the art without departing from the scope of this application, and all such substitutions and modifications should fall within the scope of this application.

Claims

1. A database optimization method, characterized in that, include: Obtain the first performance indicator of the target database query statement running in the target database; If the first operating indicator meets the first preset condition, obtain the data table pointed to by the target database query statement, and the candidate database query statements pointed to the data table. Based on the optimization strategy corresponding to the business scenario category of the target database query statement, perform candidate optimization operations on the data table to obtain a candidate optimized data table; Obtain the second running indicator of the candidate database query statement running in the candidate optimization data table, determine the matching target optimization operation in the candidate optimization operation based on the second running indicator, and optimize the database based on the target optimization operation.

2. The method according to claim 1, characterized in that, The first operational metric for obtaining the target database query statement running in the target database includes: Obtain the execution time cost, which represents the time consumed in executing the target database query statement, and the execution hardware cost, which represents the computational resource consumed in executing the database query statement.

3. The method according to claim 2, characterized in that, The step of determining the matching target optimization operation among the candidate optimization operations based on the second operational metric includes: Calculate the operating cost score corresponding to the second operating indicator; the second operating indicator includes the execution time cost, which represents the time consumption of executing the candidate database query statement, and the execution hardware cost, which represents the computing resource consumption of executing the database query statement, and the operating cost score is proportional to the execution time cost and the execution hardware cost; The running cost scores corresponding to each candidate optimization operation are arranged in order, and the candidate optimization operation with the lowest running cost score is selected as the target optimization operation.

4. The method according to claim 1, characterized in that, The business scenario categories include a first category indicating that the data table has no index, and the candidate optimization operation performed on the data table includes: adding an index to the data table, adding a composite index to the data table, and adding at least one of an index and a composite index to the data table.

5. The method according to claim 4, characterized in that, The method further includes: If the second operating metric corresponding to any of the candidate database query statements meets the second preset condition, optimize the candidate database query statement whose second operating metric meets the second preset condition.

6. The method according to claim 1, characterized in that, The business scenario category includes a second category, which indicates that the number of data tables pointed to by the target database query statement is N, where N is an integer greater than 1, and there are related data in the N data tables; The step of performing candidate optimization operations on the data table includes: Create a new auxiliary data table; The data with the aforementioned relationship is written into the auxiliary data table so that the candidate database query statement can point to the auxiliary data table.

7. The method according to claim 1, characterized in that, The operational metrics include the ongoing operational cost of the target database query statement, which represents the expected usage time of the target database query statement.

8. A database optimization device, characterized in that, include: The first acquisition module is used to acquire the first running indicators of the target database query statement running in the target database. The second acquisition module is used to acquire, when the first operating indicator meets the first preset condition, the data table pointed to by the target database query statement and the candidate database query statement pointed to the data table. The simulation optimization module is used to perform candidate optimization operations on the data table based on the optimization strategy corresponding to the business scenario category of the target database query statement, and obtain a candidate optimized data table. as well as The target optimization module is used to obtain a second running indicator of the candidate database query statement running in the candidate optimization data table, determine a matching target optimization operation based on the second running indicator in the candidate optimization operation, and optimize the database based on the target optimization operation.

9. An electronic device, comprising: One or more processors; Storage device for storing one or more programs. The characteristic is that, when the one or more programs are executed by the one or more processors, the one or more processors perform the method according to any one of claims 1 to 7.

10. A computer-readable storage medium having executable instructions stored thereon, characterized in that, When executed by the processor, this instruction causes the processor to perform the method according to any one of claims 1 to 7.

11. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the method according to any one of claims 1 to 7.

Images