Index prompt generation method and device, computer equipment, medium and product

Abstract

The invention relates to an index prompt generation method and device, computer equipment, a medium and a product, and relates to the technical field of databases. The method is applied to a distributed database system and comprises the following steps: receiving a query request of a client for the distributed database system; obtaining execution statistical information and predicate information of each query statement in the query request; according to the execution statistical information of each query statement, determining a to-be-optimized query statement from each query statement; according to the predicate information of each query statement, respectively and correspondingly constructing an index prompt word of each query statement; and according to the to-be-optimized query statement and the index prompt of each query statement, sending the to-be-optimized query statement and the index prompt of the to-be-optimized query statement to the client. By adopting the method, the index prompt of the to-be-optimized query statement can be automatically generated, the effectiveness of the index of the distributed database system is improved, and the query efficiency and stability of the system are improved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method, device, computer equipment, medium and product for generating index prompts. Background Art

[0002] With the development of big data technology, traditional centralized database systems are facing performance bottlenecks and scalability limitations. In order to cope with the growing amount of data and query requirements, distributed database systems such as the Massively Parallel Processing (MPP) architecture have emerged. It organizes resources in a non-shared (Share Nothing) structure and processes data in parallel on multiple nodes, significantly improving the efficiency of data processing.

[0003] However, in database systems, how to effectively index data to improve query performance has always been a key technical challenge. The quality of the index not only determines the query performance, but also has a direct impact on data maintenance and management. Especially in the MPP architecture, due to the complexity of data distribution, the importance of reasonably constructing indexes becomes increasingly significant.

[0004] However, in distributed database systems, indexes are usually created manually by R&D personnel or database administrators based on business scenarios or engineering experience, which requires high business capabilities and technical knowledge of relevant personnel and cannot guarantee the effectiveness of index creation. Therefore, how to reasonably construct effective index prompts is an urgent problem to be solved. Summary of the invention

[0005] Based on this, it is necessary to provide a method, device, computer equipment, medium and product for generating index prompts in response to the above technical problems, which can automatically generate index prompts.

[0006] The present application provides a method for generating an index prompt, which is applied to a distributed database system, and the method comprises:

[0007] Receiving a query request from a client to the distributed database system;

[0008] Obtaining execution statistics and predicate information of each query statement in the query request;

[0009] Determining query statements to be optimized from among the query statements according to execution statistics of the query statements;

[0010] According to the predicate information of each query statement, construct the index prompt of each query statement respectively;

[0011] According to the query statement to be optimized and the index hint of each query statement, the query statement to be optimized and the index hint of each query statement are sent to the client.

[0012] In one embodiment, the execution statistics include at least one of the number of executions, average execution time, maximum execution time, and standard deviation of execution time; wherein, determining the query statement to be optimized from each query statement based on the execution statistics of each query statement includes:

[0013] Obtaining execution parameters of each query statement according to at least one of the number of executions, average execution time, maximum execution time, and standard deviation of execution time of each query statement;

[0014] The query statements to be optimized are determined from the query statements according to the execution parameters of the query statements and a preset performance threshold.

[0015] In one embodiment, constructing index prompts for each query statement according to predicate information of each query statement includes:

[0016] According to the predicate information of each query statement, a virtual index of each query statement is constructed respectively;

[0017] According to the virtual index of each query statement, the index benefit of each query statement is obtained respectively; the index benefit is used to represent the performance improvement effect of the distributed database system using the virtual index;

[0018] According to the index benefit and virtual index of each query statement, an index prompt for each query statement is generated respectively.

[0019] In one embodiment, the step of constructing a virtual index for each query statement according to the predicate information of each query statement includes:

[0020] Determine the virtual index structure and virtual index type of each query statement according to the predicate information of each query statement and the table to be queried;

[0021] According to the virtual index structure and virtual index type of each query statement, a virtual index for each query statement is constructed respectively.

[0022] In one embodiment, obtaining the index benefit of each query statement according to the virtual index of each query statement includes:

[0023] Obtaining a first execution cost of each query statement when using the virtual index;

[0024] Obtaining a second execution cost of each query statement when the virtual index is not used;

[0025] The index benefits of each query statement are obtained respectively according to the first execution cost and the second execution cost of each query statement.

[0026] In one embodiment, the method further comprises:

[0027] Generate a distributed query plan according to the query request; the distributed query plan includes multiple query subtasks;

[0028] Respectively obtain the query sub-results corresponding to each of the query sub-tasks;

[0029] Sending a query result to the client; the query result includes each of the query sub-results.

[0030] The present application provides a device for generating index prompts, which is applied to a distributed database system, and the device includes:

[0031] A receiving module, used for receiving a query request from a client to the distributed database system;

[0032] An acquisition module, used to acquire execution statistics and predicate information of each query statement in the query request;

[0033] A detection module, used to determine the query statement to be optimized from the query statements according to the execution statistics of the query statements;

[0034] A generation module, used to construct index prompts for each query statement according to the predicate information of each query statement;

[0035] The sending module is used to send the query statement to be optimized and the index prompt of the query statement to be optimized to the client according to the query statement to be optimized and the index prompt of each query statement.

[0036] The present application provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor implements the steps of the above method when executing the computer program.

[0037] The present application provides a computer-readable storage medium having a computer program stored thereon, and the computer program implements the steps of the above method when executed by a processor.

[0038] The present application provides a computer program product, including a computer program, which implements the steps of the above method when executed by a processor.

[0039] The above-mentioned index prompt generation method, device, computer equipment, storage medium and product receive a query request from a client to a distributed database system, obtain execution statistics and predicate information of each query statement in the query request, determine the query statement to be optimized from each query statement according to the execution statistics of each query statement, construct index prompts of each query statement according to the predicate information of each query statement, and send the query statement to be optimized and the index prompts of the query statement to be optimized to the client according to the query statement to be optimized and the index prompts of each query statement. Since the execution statistics represent the overhead required for the query statement to be executed by the distributed database, the query statement to be optimized that affects the performance can be identified from each query statement based on the execution statistics; in addition, the predicate information represents the conditions used to filter data in the query statement, so an effective index prompt can be constructed based on the predicate information, so that the query statement to be optimized and the corresponding index prompt can be matched, so that the index prompt of the query statement to be optimized is automatically generated, the effectiveness of the distributed database system index can be improved, and the database administrator or automatic optimization tool can be effectively guided to create an actual index, which is helpful to improve the query efficiency and stability of the distributed database system. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] Figure 1 A diagram of an application environment of a method for generating index prompts in one embodiment;

[0041] Figure 2 A schematic diagram of a flow chart of a method for generating index prompts in one embodiment;

[0042] Figure 3 is a flow chart of a method for generating index prompts in another embodiment;

[0043] Figure 4 A schematic diagram of a flow chart of a method for generating index prompts in another embodiment;

[0044] Figure 5 A schematic diagram of a flow chart of a method for generating index prompts in yet another embodiment;

[0045] Figure 6 A schematic diagram of a flow chart of a method for generating index prompts in another embodiment;

[0046] Figure 7 is a structural block diagram of a device for generating index prompts in one embodiment;

[0047] Figure 8 FIG. 4 is a diagram showing the internal structure of a computer device in one embodiment. DETAILED DESCRIPTION

[0048] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0049] The method for generating index prompts provided in the embodiment of the present application can be applied to Figure 1 In the application environment shown. Among them, the client 10 refers to a computer installed with a graphical management tool or a command line interactive tool of the distributed database system 20. The index prompt generating device 210 refers to a device that can be used to execute the index prompt generating method provided in the present application. The index prompt generating device 210 can be applied to the distributed database system 20. The index prompt generating device 210 can identify the query statement to be optimized with low execution efficiency according to the execution statistics of the query statement, and can create a virtual index according to the predicate information of the query statement and analyze its effectiveness, so as to generate a corresponding valid index prompt, thereby obtaining the query statement to be optimized and the corresponding index prompt.

[0050] In addition to the index prompt generating device, the distributed database system 20 may also include a coordinator node (CN) and a data node (DN). The CN is mainly responsible for receiving query requests from the client 10 and returning execution results to the client 10. In the distributed database system 20, the CN node usually does not directly store business data, but is responsible for coordinating and distributing tasks to data nodes for parallel execution, and summarizing execution results. The DN is responsible for storing business data, executing data query tasks, and returning execution results to the CN. The DN node is where data is actually stored in the database. They usually have high storage and computing capabilities to support the storage and processing of large-scale data. In the distributed database system 20, data is usually distributed and stored on multiple DN nodes to achieve load balancing and high availability. The coordinator node and the data node can respectively deploy an index prompt generating device 210, and the index prompt generating device 210 collects execution statistics and predicate information of each query statement on each node, detects the query statement to be optimized and automatically generates index prompts, thereby prompting and guiding relevant personnel to optimize the query statement, thereby improving system efficiency.

[0051] The distributed database system 20 may further include a database storage layer 220, which refers to a storage part responsible for physical storage and retrieval of data in the distributed database system 20. The database storage layer 220 may store the query statements to be optimized identified by the index hint generating device and the index hints generated by the index hint generating device.

[0052] In the application, the client 10 can send a query request to the distributed database system 20 through a network connection. The index prompt generating device 210 obtains the execution statistics information and predicate information of each query statement in the query request based on the query request, determines the query statement to be optimized from each query statement by analyzing the execution statistics information and predicate information, and constructs the index prompt of each query statement, and returns the query statement to be optimized and the index prompt of the query statement to be optimized to the client 10 through the network.

[0053] In some embodiments, Figure 2 As shown, a method for generating index prompts is provided, and the method is applied to Figure 1 Taking the distributed database system in as an example, the method includes the following steps.

[0054] S202: Receive a query request from a client to the distributed database system.

[0055] The query request is used to represent the client's access, operation or management of the distributed database system. After the distributed database system receives the query request sent by the client, the coordinator node can process the query request to generate a query plan, distribute the query subtasks in the query plan to the relevant data nodes, execute the query subtasks in parallel through different data nodes, and summarize the query subresults returned by each data node, and generate the final query result, and then send the query result to the client.

[0056] S204: Obtain execution statistics and predicate information of each query statement in the query request.

[0057] The query request may include at least one query statement. The query statement is a related statement used to execute the query request. In some embodiments, the query statement may use Structured Query Language (SQL). SQL is a database query and programming language used to access data and query, update and manage relational database systems.

[0058] Execution statistics are used to indicate the overhead of query statements being executed by a distributed database system. In an embodiment of the present application, execution statistics can reveal which query statements may become performance bottlenecks due to excessive execution time, that is, execution statistics are used to identify query statements to be optimized. Predicate information is used to indicate the conditions used to filter data in a query statement. In an embodiment of the present application, predicate information can be used to construct index prompts for query statements.

[0059] It can be understood that in a distributed database based on the MMP architecture, the coordinator node is responsible for managing the entire data processing flow and building a distributed query plan, while the data node is responsible for executing the query subtasks in the query plan, that is, the data node executes specific query statement processing tasks or local operations. In this way, through parallel processing of multiple different data nodes, the data processing efficiency can be effectively improved.

[0060] In the application, when each data node executes local operations, i.e., query subtasks, according to the query plan, the executor of each data node, i.e., the generation device of index prompts, can collect and record the execution statistics and predicate information of each query statement. Figure 3 As shown, during the predicate information collection process, the query statement can be processed by the coordinator node and a query plan can be constructed. For example, the query plan is represented in the form of a query plan tree; and through the executor in each data node, the nodes of the query plan tree are analyzed, focusing on the node attributes, predicate conditions, and expressions involved, and conditions such as equal value comparison and range query are retrieved and recorded. For nodes containing left and right subtrees or subqueries, the system recursively processes to obtain deeper predicate information; after the analysis is completed, each data node can save the predicate information to shared memory to ensure that the information can be accessed during the entire query process, avoiding repeated calculations and improving system performance.

[0061] S206: Determine the query statement to be optimized from the query statements according to the execution statistics of the query statements.

[0062] The query statement to be optimized refers to a query statement that, during the execution of a distributed database, has poor query performance or wastes resources due to inefficient execution plans, excessive resource consumption, or non-compliance with best practices. In this regard, in an embodiment of the present application, by generating index prompts, prompts and guides the query statement to be optimized to improve execution efficiency.

[0063] In the application, the index prompt generation device can identify the query statements to be optimized whose execution time exceeds the normal range from the query statements according to the execution statistics of the query statements and the preset performance threshold.

[0064] S208: constructing index prompts for each query statement according to the predicate information of each query statement.

[0065] Index hints refer to the DDL (Data Definition Language) statements for creating indexes for a given database and query statement, which usually makes the original query statement execute faster. DDL is used to define and manage all corresponding languages ​​in the database.

[0066] In applications, such as Figure 3 As shown, a virtual index can be constructed according to the predicate information of each query statement through an index prompt generating device on a data node; and an index prompt generating device on a coordinator node can analyze the effect of the virtual index on query performance improvement and generate corresponding index prompts.

[0067] S210: Sending the query statement to be optimized and the index prompt of each query statement to the client according to the query statement to be optimized and the index prompt of each query statement.

[0068] In some embodiments, each query statement has a unique identifier (queryid); based on this, the index prompt generating device can match the query statement to be optimized with the index prompt of each query statement according to the unique identifier of each query statement to determine the index prompt of the query statement to be optimized.

[0069] In the application, the index prompt generating device determines the index prompt of the query statement to be optimized according to the index prompts of the query statement to be optimized and each query statement, and sends the query statement to be optimized and the index prompt of the query statement to be optimized to the client.

[0070] The above-mentioned method for generating index hints receives a query request from a client to a distributed database system, obtains execution statistics and predicate information of each query statement in the query request, determines the query statement to be optimized from each query statement according to the execution statistics of each query statement, constructs index hints of each query statement according to the predicate information of each query statement, and sends the query statement to be optimized and the index hints of the query statement to be optimized to the client according to the query statement to be optimized and the index hints of each query statement. Since the execution statistics represent the overhead required for the query statement to be executed by the distributed database, the query statement to be optimized that affects the performance can be identified from each query statement based on the execution statistics; in addition, the predicate information represents the conditions used to filter data in the query statement, so an effective index hint can be constructed based on the predicate information, so that the query statement to be optimized and the corresponding index hint can be matched, so that the index hints of the query statement to be optimized are automatically generated, the effectiveness of the distributed database system index can be improved, and the database administrator or automatic optimization tool can be effectively guided to create the actual index, which is helpful to improve the query efficiency and stability of the distributed database system.

[0071] In some embodiments, the execution statistics include at least one of the number of executions, average execution time, maximum execution time, and standard deviation of execution time. Among them, the number of executions refers to the total number of times the query statement is actually executed within the statistical period. The average execution time refers to the average time taken for each execution of the query statement within the statistical period. The maximum execution time refers to the longest time taken for a single execution of the query statement within the statistical period. The standard deviation of execution time indicates the degree of fluctuation of the execution time of each query statement within the statistical period. In the application, the query statement to be optimized can be identified by collecting and recording at least one of the number of executions, average execution time, maximum execution time, and standard deviation of execution time.

[0072] Among them, Figure 4 As shown, according to the execution statistics of each query statement, determining the query statement to be optimized from each query statement includes the following steps.

[0073] S402: Obtain execution parameters of each query statement according to at least one of the number of executions, average execution time, maximum execution time, and standard deviation of execution time of each query statement.

[0074] The execution parameters are used to evaluate the overhead of query statements executed by the distributed database within the statistical period. In some embodiments, the execution statistics include the number of executions, the average execution time, the maximum execution time, and the standard deviation of the execution time; based on this, the execution parameters can be used to express the formula as follows:

[0075] (1)

[0076] Among them, Execution Score represents the execution parameters; mean_time represents the average execution time; max_time represents the maximum execution time; stddev_time represents the standard deviation of the execution time; calls represents the number of executions; W 1 The weight representing the average execution time; W 2 represents the weight of the standard deviation of execution time; W 3 Indicates the weight of the number of executions. In the above formula (1), the weight of the average execution time is 1.

[0077] It can be understood that the average execution time reflects the normal performance of the query statement, so its weight can be set to 1. When the value is large, it means that the query may be a query statement to be optimized, that is, a slow query statement; the maximum execution time reflects whether there is a performance bottleneck in special circumstances, so the weight W 1 It can be a larger value to amplify the impact of the maximum execution time on the evaluation of execution parameters; the standard deviation of the execution time reflects the fluctuation of the query execution time, so the weight W 2It can be a large value, which amplifies the instability of query performance. The number of query executions reflects the execution frequency of the query statement. If a query statement is executed many times but the single execution time is short, it is not necessarily a query statement to be optimized, so W 3 Can be set to a larger value to reduce the impact of frequently executed but less expensive queries.

[0078] In the application, the weights of the number of executions, average execution time, maximum execution time, and standard deviation of execution time can be set accordingly according to the actual scenario and are not limited here.

[0079] S404: Determine query statements to be optimized from among the query statements according to the execution parameters of the query statements and the preset performance threshold.

[0080] The performance threshold is preset and is used to screen the query statements to be optimized from the query statements. In some embodiments, the execution parameters of the query statements can be compared with the preset performance threshold, and the query statements whose execution parameters are greater than or equal to the preset performance threshold are determined as the query statements to be optimized. These query statements to be optimized are often the source of performance bottlenecks.

[0081] It should be noted that the above is only an exemplary description. In the application, the execution parameter can be set accordingly based on the execution statistics. For example, if the execution statistics include the average execution time, the maximum execution time, and the standard deviation of the execution time, the execution parameter can be the weighted average of the average execution time, the maximum execution time, and the standard deviation of the execution time. The execution statistics, execution parameters, and weights are not excessively limited here.

[0082] The method for generating index prompts provided in the above embodiment obtains the execution parameters of each query statement according to at least one of the execution times, average execution time, maximum execution time, and standard deviation of execution time of each query statement, and determines the query statement to be optimized from each query statement according to the execution parameters of each query statement and the preset performance threshold. Since the execution times, average execution time, maximum execution time, and standard deviation of execution time respectively reflect the overhead of the query statement from different dimensions, the execution parameters comprehensively evaluate the execution cost of the query statement in at least one dimension, thereby screening the query statement based on the execution parameters and the preset performance threshold, realizing the identification of the query statement to be optimized, and feeding back the corresponding index prompt to the client for the query statement to be optimized, thereby improving the effectiveness of the index and helping to improve the query efficiency of the system.

[0083] In some embodiments, Figure 4As shown, the method for generating index hints may further include S406: cleaning up execution statistics of each query statement. It is understandable that after determining the query statement to be optimized, the collected execution statistics of each query statement may also be cleaned up, for example, the execution statistics of each query statement may be cleaned up periodically to improve the resource utilization of the distributed database system and ensure the query efficiency of the system.

[0084] In some embodiments, Figure 5 As shown, S208, constructing index prompts for each query statement according to the predicate information of each query statement, includes the following steps.

[0085] S502: constructing virtual indexes for the query statements respectively according to the predicate information of the query statements.

[0086] A virtual index is a simulated index structure that is not directly stored in the database and does not consume resources, but can be used to simulate the potential impact of an index on query performance.

[0087] In the application, a virtual index for each query statement can be constructed based on a distributed database tool according to the predicate information of each query statement.

[0088] S504: According to the virtual index of each query statement, the index benefit of each query statement is obtained respectively.

[0089] Index benefit is used to indicate the performance improvement effect of using virtual indexes in a distributed database system. Index benefit can also be understood as the performance improvement effect of using virtual indexes in a distributed database system compared to not using virtual indexes.

[0090] In the application, the index benefit of the query statement can be determined based on the cost of executing the query statement using the virtual index and the cost of executing the query statement without using the virtual index in the distributed database system.

[0091] S506: Generate index prompts for each query statement according to the index benefits and virtual indexes of each query statement.

[0092] For each query statement, whether the virtual index is valid can be determined based on the index return; if the virtual index is valid, an index prompt for the query statement can be generated based on the virtual index; if the virtual index is invalid, the index prompt for the query statement can be set to empty.

[0093] For example, the index benefit is compared with the preset benefit threshold; if the index benefit is greater than or equal to the preset benefit threshold, it indicates that the virtual index corresponding to the index benefit is valid. In this case, a corresponding index prompt can be generated according to the virtual index, including but not limited to virtual index creation statements, tables, query plans, index benefits and other information; if the index benefit is less than the preset benefit threshold, it indicates that the virtual index corresponding to the index benefit is invalid. In this case, the index prompt corresponding to the virtual index can be determined to be empty, that is, the query statement corresponding to the virtual index does not need to be indexed for optimization. Among them, the preset benefit threshold is pre-set and can be set according to test values ​​or experience values, which is not limited here.

[0094] In the application, the index hints of each query statement can be stored in the distributed database system, which can provide support for the database administrator or automatic optimization tool to make decisions on whether to create an index for query optimization.

[0095] The method for generating index prompts provided in the above embodiment builds a virtual index for each query statement according to the predicate information of each query statement, obtains the index benefit of each query statement according to the virtual index of each query statement, and generates an index prompt for each query statement according to the index benefit and the virtual index of each query statement. Since the index benefit represents the performance improvement effect of using the virtual index in the distributed database system, the effectiveness of the virtual index can be analyzed according to the index benefit, thereby generating an index prompt with a valid virtual index to prompt and guide relevant personnel to optimize the query statements, thereby improving the performance of the distributed database system.

[0096] In some embodiments, S502, constructing a virtual index for each query statement respectively according to the predicate information of each query statement, may include: determining a virtual index structure and a virtual index type for each query statement respectively according to the predicate information of each query statement and the table to be queried, and constructing a virtual index for each query statement respectively according to the virtual index structure and the virtual index type of each query statement.

[0097] Among them, the table to be queried refers to the related table that the query statement needs to query. In the application, the query statement can be processed into a parse tree, that is, the query statement is converted into the internal representation of the distributed database system, and the parse tree includes relevant information such as syntax analysis, table and field references. In the process of creating a virtual index based on the parse tree, the predicate information of the query statement in the parse tree and the table involved, that is, the table to be queried, can be checked to determine the virtual index structure that needs to be simulated. After determining the virtual index structure, the information of the table and the virtual index type for which the virtual index needs to be created can be obtained to determine which table needs to create a virtual index and the index type to be simulated. At the same time, check whether the virtual index type to be created is supported to ensure that the simulated type of the virtual index is reasonable and effective in the actual environment. If the virtual index type is supported, create a virtual index entity, generate simulated index keys, set statistics, etc., and add it to the virtual index list to simulate the effect of the actual index in the database, thereby completing the construction of the virtual index for each query statement.

[0098] In some embodiments, S504, based on the virtual index of each query statement, the index benefit of each query statement is obtained respectively, including: obtaining the first execution cost of each query statement when using the virtual index; obtaining the second execution cost of each query statement when not using the virtual index; obtaining the index benefit of each query statement based on the first execution cost and the second execution cost of each query statement.

[0099] In the application, the index prompt generation device can use the "What-If" function of the distributed database system to obtain the first execution cost of the query statement when the virtual index is used, and the second execution cost of the query statement when the virtual index is not used, so as to determine the index benefit of the query statement according to the first execution cost and the second execution cost, so as to analyze the improvement effect of the virtual index on the query performance. The index benefit can be expressed by the formula:

[0100] (2)

[0101] Among them, Index Benefit represents index benefit; cost_without_index represents the second execution cost; cost_with_index represents the first execution cost. In this way, the index benefit of the virtual index can be calculated based on the first execution cost and the second execution cost, so as to analyze the effectiveness of the virtual index according to the index benefit, so as to create an index prompt with a valid virtual index, thereby improving system performance.

[0102] In some embodiments, the method for generating index prompts also includes: generating a distributed query plan based on the query request, the distributed query plan including multiple query subtasks; respectively obtaining query subresults corresponding to each query subtask; sending the query results to the client; the query results include each query subresult.

[0103] In the application, every query request submitted by the user through the client will be received and processed by the executor in the distributed database system. The distributed execution engine of the distributed database system is responsible for the generation, distribution and execution of distributed query plans. Specifically, the user interacts with the CN node in the distributed database system through the client, that is, the user can send a query request to the CN node through the client; after receiving the query request, the CN node parses and optimizes it, and decomposes the query into query subtasks that can be executed in parallel on different DN nodes; then, the CN node generates a distributed query plan, which includes multiple query subtasks. The query plan clarifies how each DN node obtains data and how to merge these scattered data to generate the final query result; then, CN distributes this distributed query plan to the relevant DN nodes, and each DN node performs local operations according to the assigned query subtasks and returns the query subresults to the CN node; the CN node finally summarizes the query subresults returned by all data nodes and sends the integrated query results back to the client.

[0104] The method for generating index prompts provided in the above embodiment adopts the cloud-native distributed database structure of cloud computing. By splitting the query request into multiple query sub-plans, the query sub-plans are executed in parallel by using multiple distributed data nodes, which significantly improves the efficiency of data processing.

[0105] In some embodiments, Figure 6 As shown, a method for generating index prompts is provided, and the method is applied to Figure 1 In the application environment shown, the method may include the following steps.

[0106] S602: Receive a query request from a client to the distributed database system.

[0107] S604: Obtain execution statistics of each query statement in the query request, wherein the execution statistics include execution times, average execution time, maximum execution time and execution time standard deviation.

[0108] S606: Obtain predicate information of each query statement in the query request respectively.

[0109] S608: Obtain execution parameters of each query statement according to the execution statistics of each query statement, wherein the execution parameters can be calculated using the above formula (1).

[0110] S610: Determine query statements to be optimized from the query statements according to the execution parameters of the query statements and the preset performance threshold, wherein the query statements whose execution parameters are greater than or equal to the preset performance threshold are determined as the query statements to be optimized.

[0111] S612: Constructing virtual indexes for the query statements respectively according to the predicate information of the query statements.

[0112] S614: According to the virtual index of each query statement, the index benefit of each query statement is obtained respectively. The index benefit can be calculated using the above formula (2).

[0113] S616: According to the index benefits of each query statement, the virtual index is effectively analyzed, and index prompts of the query statement are generated according to the effective virtual index.

[0114] S618: Match the query statement to be optimized and the corresponding index hint according to the unique identifier of the query statement, and send them to the client.

[0115] The method for generating index hints provided in the embodiment of the present application is applied to a distributed database system, and solves the problem of unstable query performance caused by difficulty in effectively indexing data in a distributed database system based on an MMP architecture. By automatically collecting execution statistics and predicate information of query statements, and intelligently detecting and analyzing index hints for optimized query statements, effective index hints can be dynamically generated according to data access patterns and query loads. Compared to the related art, most distributed database systems create index management data manually, and this automated index hint generation method can better manage large-scale data sets efficiently, significantly reduce query response time and maintenance costs, and improve query performance and system stability.

[0116] It should be understood that, although the steps in the flowcharts involved in the above embodiments are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence according to the order indicated by the arrows. Unless there is a clear explanation in this article, the execution of these steps is not strictly limited in order, and these steps can be executed in other orders. Moreover, at least a part of the steps in the flowcharts involved in the above embodiments may include multiple steps or multiple stages, and these steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these steps or stages is not necessarily carried out in sequence, but can be executed in turn or alternately with other steps or at least a part of the steps or stages in other steps.

[0117] Based on the same inventive concept, the embodiment of the present application also provides an index prompt generating device, which is used to implement the index prompt generating method involved above. The implementation scheme for solving the problem provided by the device is similar to the implementation scheme recorded in the above method, so the specific limitations in one or more index prompt generating device embodiments provided below can refer to the limitations of the index prompt generating method above, and will not be repeated here.

[0118] In some embodiments, Figure 7 As shown, a device 700 for generating index prompts is provided, comprising: a receiving module 701, an acquisition module 702, a detection module 703, a generation module 704 and a sending module 705. The receiving module 701 is used to receive a query request from a client to a distributed database system. The acquisition module 702 is used to obtain execution statistics and predicate information of each query statement in the query request. The detection module 703 is used to determine the query statement to be optimized from each query statement based on the execution statistics of each query statement. The generation module 704 is used to construct the index prompts of each query statement respectively according to the predicate information of each query statement. The sending module 705 is used to send the query statement to be optimized and the index prompts of the query statement to be optimized to the client according to the query statement to be optimized and the index prompts of each query statement.

[0119] The index prompt generation device 700 provided in the above embodiment receives a query request from a client to a distributed database system through a receiving module 701, obtains execution statistics and predicate information of each query statement in the query request through an acquisition module 702, determines the query statement to be optimized from each query statement according to the execution statistics of each query statement through a detection module 703, constructs index prompts for each query statement according to the predicate information of each query statement through a generation module 704, and sends the query statement to be optimized and the index prompts of the query statement to be optimized to the client through a sending module 705 according to the query statement to be optimized and the index prompts of each query statement. Since the execution statistics information represents the overhead required for query statements to be executed by the distributed database, the query statements to be optimized that affect the performance can be identified from each query statement based on the execution statistics information; in addition, the predicate information represents the conditions used to filter data in the query statement, so an effective index hint can be constructed based on the predicate information, so that the query statement to be optimized and the corresponding index hint can be matched. In this way, the index hint of the query statement to be optimized is automatically generated, the effectiveness of the distributed database system index can be improved, and the database administrator or automatic optimization tool can be effectively guided to create the actual index, which is helpful to improve the query efficiency and stability of the distributed database system.

[0120] In some embodiments, the execution statistics include at least one of the number of executions, average execution time, maximum execution time, and standard deviation of execution time; wherein the detection module is also used to obtain the execution parameters of each query statement according to at least one of the number of executions, average execution time, maximum execution time, and standard deviation of execution time of each query statement; and determine the query statement to be optimized from each query statement according to the execution parameters of each query statement and a preset performance threshold.

[0121] In some embodiments, the generation module is also used to construct a virtual index for each query statement according to the predicate information of each query statement; obtain the index benefit of each query statement according to the virtual index of each query statement; the index benefit is used to indicate the performance improvement effect of using virtual indexes in the distributed database system; and generate index prompts for each query statement according to the index benefit and virtual index of each query statement.

[0122] In some embodiments, the generation module is also used to determine the virtual index structure and virtual index type of each query statement based on the predicate information of each query statement and the table to be queried; and to construct a virtual index for each query statement based on the virtual index structure and virtual index type of each query statement.

[0123] In some embodiments, the generation module is also used to obtain a first execution cost of each query statement when using a virtual index; obtain a second execution cost of each query statement when not using a virtual index; and obtain the index benefit of each query statement based on the first execution cost and the second execution cost of each query statement.

[0124] In some embodiments, the index prompt generating device further includes a generating module, the generating module is used to generate a distributed query plan according to the query request; the distributed query plan includes multiple query subtasks. The acquiring module is also used to respectively acquire the query subresults corresponding to each query subtask. The sending module is also used to send the query result to the client; the query result includes each query subresult.

[0125] Each module in the above index prompt generating device can be implemented in whole or in part by software, hardware or a combination thereof. Each module can be embedded in or independent of a processor in a computer device in the form of hardware, or can be stored in a memory in a computer device in the form of software, so that the processor can call and execute the operations corresponding to each module.

[0126] In one embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as follows: Figure 8As shown. The computer device includes a processor, a memory and a network interface connected through a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store query statements to be optimized and index prompts. The network interface of the computer device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, a method for generating index prompts is implemented.

[0127] Those skilled in the art will understand that Figure 8 The structure shown in the figure is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the computer device to which the solution of the present application is applied. The specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0128] In one embodiment, a computer device is provided, including a memory and a processor. The memory stores a computer program, and the processor implements the steps of the aforementioned index prompt generation method when executing the computer program.

[0129] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the steps of the aforementioned index prompt generation method are implemented.

[0130] In one embodiment, a computer program product is provided, including a computer program, which implements the steps of the aforementioned index prompt generation method when executed by a processor.

[0131] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties.

[0132] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to the memory, database or other medium used in the embodiments provided in this application can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetoresistive random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. As an illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM). The database involved in each embodiment provided in this application may include at least one of a relational database and a non-relational database. Non-relational databases may include distributed databases based on blockchains, etc., but are not limited to this. The processor involved in each embodiment provided in this application may be a general-purpose processor, a central processing unit, a graphics processor, a digital signal processor, a programmable logic device, a data processing logic device based on quantum computing, etc., but are not limited to this.

[0133] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0134] The above-described embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the present application. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the attached claims.

Claims

1. A method for generating an index prompt, characterized in that: Applied to a distributed database system, the method comprises: Receiving a query request from a client to the distributed database system; Obtaining execution statistics and predicate information of each query statement in the query request; Determining query statements to be optimized from among the query statements according to execution statistics of the query statements; According to the predicate information of each query statement, construct the index prompt of each query statement respectively; According to the query statement to be optimized and the index hint of each query statement, the query statement to be optimized and the index hint of each query statement are sent to the client.

2. The method according to claim 1, characterized in that The execution statistics include at least one of the number of executions, average execution time, maximum execution time, and standard deviation of execution time; wherein, determining the query statement to be optimized from each query statement based on the execution statistics of each query statement includes: Obtaining execution parameters of each query statement according to at least one of the number of executions, average execution time, maximum execution time, and standard deviation of execution time of each query statement; The query statements to be optimized are determined from the query statements according to the execution parameters of the query statements and a preset performance threshold.

3. The method according to claim 1, characterized in that: The index prompts of each query statement are constructed according to the predicate information of each query statement, including: According to the predicate information of each query statement, a virtual index of each query statement is constructed respectively; According to the virtual index of each query statement, the index benefit of each query statement is obtained respectively; the index benefit is used to represent the performance improvement effect of the distributed database system using the virtual index; According to the index benefit and virtual index of each query statement, an index prompt for each query statement is generated respectively.

4. The method according to claim 3, characterized in that The step of constructing a virtual index for each query statement according to the predicate information of each query statement includes: Determine the virtual index structure and virtual index type of each query statement according to the predicate information of each query statement and the table to be queried; According to the virtual index structure and virtual index type of each query statement, a virtual index for each query statement is constructed respectively.

5. The method according to claim 3, characterized in that: The step of obtaining the index benefits of each query statement according to the virtual index of each query statement includes: Obtaining a first execution cost of each query statement when using the virtual index; Obtaining a second execution cost of each query statement when the virtual index is not used; The index benefits of each query statement are obtained respectively according to the first execution cost and the second execution cost of each query statement.

6. The method according to any one of claims 1 to 5, characterized in that: The method further comprises: Generate a distributed query plan according to the query request; the distributed query plan includes multiple query subtasks; Respectively obtain the query sub-results corresponding to each of the query sub-tasks; Sending a query result to the client; the query result includes each of the query sub-results.

7. A device for generating index prompts, characterized in that: Applied to a distributed database system, the device comprises: A receiving module, used for receiving a query request from a client to the distributed database system; An acquisition module, used to acquire execution statistics and predicate information of each query statement in the query request; A detection module, used to determine the query statement to be optimized from the query statements according to the execution statistics of the query statements; A generation module, used to construct index prompts for each query statement according to the predicate information of each query statement; The sending module is used to send the query statement to be optimized and the index prompt of the query statement to be optimized to the client according to the query statement to be optimized and the index prompt of each query statement.

8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 6 are implemented.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

Images