A slow query optimization method, system and terminal based on physical plan comparison

Abstract

The application discloses a slow query optimization method and system based on physical plan comparison and a terminal, and the method comprises the following steps: acquiring user query information, generating a query execution plan according to the user query information based on a database optimizer; acquiring a historical slow query, detecting whether the query execution plan contains a plan in the historical slow query; when the query execution plan does not contain the plan in the historical slow query, acquiring a pre-set timeout duration, acquiring the running time of the query execution plan in real time, saving the current query information into the historical slow query when the running time exceeds the timeout duration, and terminating the query. The slow query optimization method based on physical plan comparison can quickly determine whether the query physical plan contains the slow query physical plan, and also enables the user to discover the slow query in advance, thereby avoiding the influence of the slow query on the query speed and improving the query efficiency.

Description

Technical Field

[0001] This invention relates to the field of electronic information technology, and in particular to a slow query optimization method, system, and terminal based on physical plan comparison. Background Technology

[0002] Slow query management refers to various methods and techniques for identifying, analyzing, and predicting slow queries. Slow query management can be done through logs; the database system automatically records the execution time of all queries in the log. DBAs (Database Administrators) can then use the logs to identify slow-running queries and optimize them. However, slow query optimization requires highly skilled personnel and significant time investment. Therefore, to facilitate slow query management for DBAs, more readable database monitoring systems and more professional database diagnostic tools are used. Database monitoring systems can automatically identify which queries are slow and recommend feasible diagnostic tools to analyze them. The diagnostic tools then analyze the information related to these slow queries, ultimately informing the DBA of the reasons for the slowness and specific optimization methods.

[0003] However, while database monitoring systems and database diagnostic tools greatly reduce the burden on DBAs and ensure the stability of database systems, they cannot identify and intercept slow queries before query execution. As a result, users may still encounter slow queries during the query process, causing query blockage and affecting query speed.

[0004] Therefore, existing technologies still need to be improved and developed. Summary of the Invention

[0005] The main objective of this invention is to provide a slow query optimization method, system, terminal, and computer-readable storage medium based on physical plan comparison. This invention aims to solve the problem in the prior art that slow queries cannot be identified and intercepted before query execution, resulting in users still encountering slow queries during the query process, causing query obstruction and affecting query speed.

[0006] To achieve the above objectives, the present invention provides a slow query optimization method based on physical plan comparison, the slow query optimization method based on physical plan comparison comprising the following steps:

[0007] Obtain user query information and generate a query execution plan based on the user query information using the database optimizer;

[0008] Obtain historical slow queries and check whether the query execution plan contains the plan from the historical slow queries;

[0009] When the query execution plan does not contain the plan in the historical slow query, a preset timeout duration is obtained, the running time of the query execution plan is obtained in real time, and when the running time exceeds the timeout duration, the current query information is saved to the historical slow query, and the query is terminated.

[0010] Optionally, the obtaining of the historical slow query and the detection of whether the query execution plan contains the plan in the historical slow query specifically include:

[0011] The historical slow query is obtained, and a slow query table is obtained according to the historical slow query.

[0012] All sub-plans in the query execution plan are obtained, each sub-plan in the query execution plan is taken as a node, a structural expression corresponding to each sub-plan is constructed, and a slow query hash value corresponding to each sub-plan is generated according to each structural expression.

[0013] According to the slow query hash value corresponding to each sub-plan, whether the query execution plan contains the plan in the historical slow query is detected from the slow query table.

[0014] Optionally, the obtaining of all sub-plans in the query execution plan and the taking of each sub-plan in the query execution plan as a node specifically include:

[0015] All sub-plans in the query execution plan are obtained.

[0016] Each sub-plan in the query execution plan is taken as a node, and a structural expression with each sub-plan in the query execution plan as a root node is constructed, where each structural expression is represented by using a string.

[0017] Optionally, the detection of whether the query execution plan contains the plan in the historical slow query according to the slow query hash value corresponding to each sub-plan and from the slow query table specifically includes:

[0018] According to the slow query hash value corresponding to each sub-plan, a plan equivalence verification method is used to determine whether there is a plan equal to the query execution plan in the historical slow query.

[0019] When there is a plan equal to the query execution plan in the historical slow query, the query execution plan contains the plan in the historical slow query.

[0020] Optionally, the judgment of whether the historical slow query exists a plan equal to the query execution plan according to the slow query hash value corresponding to each sub-plan, specifically includes:

[0021] According to the number, content and attribute list length of all the sub-plans in the query execution plan, the plan equivalence verification method and the pre-set naming mapping are used to judge whether the historical slow query exists a plan equal to the query execution plan.

[0022] According to the number, content and attribute list length of all the sub-plans in the query execution plan, the plan equivalence verification method and the pre-set naming mapping are used to judge whether the historical slow query exists a plan equal to the query execution plan.

[0023] Optionally, when the query execution plan does not contain the plan in the historical slow query, a pre-set timeout length is obtained, the running time of the query execution plan is obtained in real time, when the running time exceeds the timeout length, the current query information is saved to the historical slow query, and the query is terminated, specifically including:

[0024] When the query execution plan does not contain the plan in the historical slow query, a pre-set timeout length is obtained;

[0025] When the query execution plan starts running, the running time of the query execution plan is obtained in real time based on a timer;

[0026] When the running time obtained by the timer exceeds the timeout length, the timer sends an interrupt signal to an executor, the query is interrupted based on the executor, and the current query information is saved to the historical slow query.

[0027] Optionally, when the query execution plan starts running, the running time of the query execution plan is obtained in real time based on a timer, and then includes:

[0028] When the running time of the query execution plan obtained in real time by the timer does not exceed the timeout length during the running of the query execution plan, the query is not terminated;

[0029] When the query is completed, the timer stops obtaining the running time of the query execution plan, and the query is ended.

[0030] In addition, in order to achieve the above purpose, the application also provides a slow query optimization system based on physical plan comparison, wherein the slow query optimization system based on physical plan comparison comprises:

[0031] The plan generation module is used for obtaining user query information, generating a query execution plan based on a database optimizer according to the user query information;

[0032] detecting and comparing module, configured to acquire historical slow queries, and detect whether the query execution plan contains the plan in the historical slow queries;

[0033] terminating module, configured to acquire a preset timeout duration when the query execution plan does not contain the plan in the historical slow queries, acquire the running time of the query execution plan in real time, save the current query information into the historical slow queries, and terminate the query when the running time exceeds the timeout duration.

[0034] In addition, to achieve the above object, the present application also provides a terminal, wherein the terminal comprises a memory, a processor, and a slow query optimization program based on physical plan comparison stored in the memory and executable on the processor, and the slow query optimization program based on physical plan comparison implements the steps of the slow query optimization method based on physical plan comparison when executed by the processor.

[0035] In addition, to achieve the above object, the present application also provides a computer readable storage medium, wherein the computer readable storage medium stores a slow query optimization program based on physical plan comparison, and the slow query optimization program based on physical plan comparison implements the steps of the slow query optimization method based on physical plan comparison when executed by a processor.

[0036] In the present application, user query information is acquired, a query execution plan is generated based on a database optimizer according to the user query information, historical slow queries are acquired, and it is detected whether the query execution plan contains the plan in the historical slow queries. When the query execution plan does not contain the plan in the historical slow queries, a preset timeout duration is acquired, the running time of the query execution plan is acquired in real time, the current query information is saved into the historical slow queries when the running time exceeds the timeout duration, and the query is terminated. In the present application, the current query execution plan is determined to be a slow query by the historical slow queries and the current query execution plan, and the query is terminated if it is a slow query. When it is not a slow query, the query is avoided for a long time according to the set timeout duration, thereby achieving the effect of quickly determining whether the query physical plan contains the slow query physical plan, and the user can discover the slow query in advance, thereby avoiding the influence of the slow query on the query speed and improving the query efficiency. BRIEF DESCRIPTION OF DRAWINGS

[0037] Figure 1 is a flowchart of a preferred embodiment of the slow query optimization method based on physical plan comparison of the present application.

[0038] Figure 2 is a flowchart of a preferred embodiment of the slow query optimization method based on physical plan comparison of the present application;

[0039] Figure 3 is a flowchart of detecting whether the query execution plan contains the plan of the historical slow query in the slow query optimization method based on physical plan comparison of the present application;

[0040] Figure 4 is a flowchart of plan equivalence checking in the slow query optimization method based on physical plan comparison of the present application;

[0041] Figure 5 is a structure diagram of a preferred embodiment of the slow query optimization system based on physical plan comparison of the present application;

[0042] Figure 6 is a structure diagram of a preferred embodiment of the terminal of the present application. DETAILED DESCRIPTION

[0043] In order to make the objectives, technical solutions and advantages of the present application clearer and more explicit, the present application is further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and do not limit the present application.

[0044] Slow query management refers to various means and techniques for identifying, analyzing and predicting slow queries. Slow query management can be performed through logs. A database system automatically records the execution time of all queries in a log. A database administrator (DBA) finds the slow queries through the log and performs tuning. The slow query optimization method requires personnel with professional knowledge to spend a lot of time to complete. Therefore, in order to facilitate the DBA to manage the slow query, a more readable database monitoring system and a more professional database diagnostic tool are used. The database monitoring system can automatically find which queries are slow queries among the executed queries and recommend feasible diagnostic tools to analyze these slow queries. The diagnostic tool analyzes the information related to these slow queries and finally tells the DBA the reasons for the slow queries and the specific optimization means.

[0045] However, there are few methods for slow query prevention based on physical execution plan comparison at present, mainly because: first, different database management systems may have differences in the implementation of query optimizers and the generation of physical execution plans, and it is difficult to simultaneously consider the characteristics of different database management systems for adaptation; second, the physical execution plan contains a large amount of information such as operation sequence, cost estimate value, node attribute, and the complexity of comparing the physical execution plan is high, and a suitable algorithm and data structure need to be designed to realize efficient comparison; third, the influence of automatic slow query identification on query execution speed, system resource occupation and the like needs to be considered.

[0046] Therefore, although the database monitoring system and the database diagnosis tool greatly reduce the burden of the DBA and ensure the stability of the database system, they cannot identify and intercept slow queries before query execution, so that users may still encounter slow queries in queries, which leads to query blocking and affects query speed.

[0047] In view of one or more of the above problems, the present application obtains user query information, generates a query execution plan based on a database optimizer according to the user query information, obtains historical slow queries, detects whether the query execution plan contains a plan in the historical slow queries, obtains a pre-set timeout duration when the query execution plan does not contain the plan in the historical slow queries, obtains the running time of the query execution plan in real time, saves the current query information to the historical slow queries when the running time exceeds the timeout duration, and terminates the query.

[0048] The slow query optimization method based on physical plan comparison of the preferred embodiment of the present application, as shown in Figure 1 The slow query optimization method based on physical plan comparison includes the following steps:

[0049] Step S10, obtaining user query information, generating a query execution plan based on a database optimizer according to the user query information.

[0050] It should be noted that the present application is a slow query optimization method based on physical plan comparison based on a PostgreSQL database. In the present application, the database optimizer is used to generate a query execution plan corresponding to the user query information.

[0051] Specifically, as shown in Figure 2 In the present application, when the user query is received, i.e., the user query information is obtained, a query execution plan is generated, and after obtaining the plan, it is detected whether the plan contains a historical slow query plan.

[0052] Step S20, obtaining historical slow queries, detecting whether the query execution plan contains a plan in the historical slow queries.

[0053] It should be noted that in the present application, the historical slow query includes the slow query encountered or collected by the user in the past, and the corresponding slow query is stored in the historical slow query after each time the slow query is encountered. By the historical slow query, the new query physical plan, i.e. the query execution plan, is compared with the physical plan in the slow query table; when the query execution plan contains the historical slow query structure, it is proved that the query is a slow query, and the execution will certainly time out. Therefore, the query is interrupted before the query execution plan, and the user is reminded that a slow query is encountered.

[0054] Specifically, as shown in Figure 2 In the present application, it is detected whether the query execution plan contains the historical slow query plan, and if so, the query is interrupted and the user is told that the current query is a slow query; if not, the system will execute the query.

[0055] Further, the historical slow query is obtained, and it is detected whether the query execution plan contains the plan in the historical slow query, specifically including:

[0056] The historical slow query is obtained, and a slow query table is obtained according to the historical slow query;

[0057] All sub-plans in the query execution plan are obtained, each sub-plan in the query execution plan is taken as a node, a structure expression corresponding to each sub-plan is constructed, and a slow query hash value corresponding to each sub-plan is generated according to each structure expression;

[0058] According to the slow query hash value corresponding to each sub-plan, it is detected from the slow query table whether the query execution plan contains the plan in the historical slow query.

[0059] It should be noted that the historical slow query is stored in the slow query table, and the attributes and their meanings in the slow query table are shown in Table 1 as follows:

[0060] Table 1, attributes and meanings of the slow query table;

[0061]

[0062] Specifically, in the present application, in the slow query detection process, it is necessary to judge whether the current query plan contains the historical slow query. This requires judging all sub-plans of the current plan; if the detection time is too long, it will affect the query performance, and in order to speed up the detection, a structure expression with the node as the root node is constructed for each node, and a slow query hash value is generated with this structure expression to quickly judge whether there is the same structure tree in the slow query table. The structure expression is represented by a string.

[0063] Wherein, for a node corresponding to a sub-plan as a root node, the corresponding leaf node, the leaf node of the plan tree is usually a scan node, and the structural expression will be constructed by the type of the node and the relationship name of the scan; for a non-leaf node, the system will first access its child nodes, obtain the structural expression of all child nodes, and then add the type of the node to construct the structural expression of the node.

[0064] After constructing the structural expression with the current node as the root node, a hash value is generated to quickly find the same structure of the historical slow query. For each sub-plan, if there is a slow query with the same structure, the system will judge whether the sub-plan and the slow query plan are equivalent, so as to judge whether the query execution plan contains the plan in the historical slow query.

[0065] As shown in Figure 3 , the present application, detecting whether the query execution plan contains the plan in the historical slow query, specifically includes:

[0066] Step S31, start;

[0067] Step S32, judge whether it is a leaf node, when yes, go to step S34, when no, go to step S33;

[0068] Step S33, recursively access child nodes to obtain the structural expression of all own flowers, and go to step S34;

[0069] Step S34, generate a structural expression in combination with node information;

[0070] Step S35, obtain the plan with the same structural expression from the slow query table and compare;

[0071] Step S36, judge whether there is the same plan, when yes, go to step S37, when no, go to step S38;

[0072] Step S37, detect slow query, interrupt query;

[0073] Step S38, no slow query is detected, return the structural expression.

[0074] Further, the structural expression corresponding to each sub-plan is constructed by taking each sub-plan in the query execution plan as a node, specifically including:

[0075] Obtain all sub-plans in the query execution plan;

[0076] construct a structural expression with each of the sub-plans in the query execution plan as a root node, wherein each structural expression is represented by a string.

[0077] Specifically, in the present application, a plan tree is constructed with each of the sub-plans in the query execution plan as a root node, thereby generating a structural expression corresponding to each sub-plan, and a string is used to represent the corresponding structural expression. When generating the structural expression corresponding to each sub-plan, a more specific leaf node, i.e., a leaf node, is used to generate the structural expression. The leaf node of the plan tree is usually a scan node, and the structural expression is constructed by the type of the node and the relationship name of the scan. For a non-leaf node in the plan tree, the system will first access its child nodes to obtain the structural expressions of all the child nodes, and then add the type of the node to construct the structural expression of the node.

[0078] Further, the detection of whether the plan in the query execution plan contains the plan in the historical slow query according to the slow query hash value corresponding to each of the sub-plans specifically includes:

[0079] determining whether there is a plan equal to the query execution plan in the historical slow query according to the slow query hash value corresponding to each of the sub-plans by using a plan equivalence verification method;

[0080] When there is a plan equal to the query execution plan in the historical slow query, the query execution plan contains the plan in the historical slow query.

[0081] Specifically, in the present application, the slow query hash value corresponding to each sub-plan is obtained, and a plan equivalence verification method is used to determine whether there is a plan equal to the query execution plan in the historical slow query. When there is, it means that the query execution plan contains the plan in the historical slow query, and the corresponding query execution plan is a slow query, and the query is terminated. When there is not, it means that the query execution plan does not contain the plan in the historical slow query, and the corresponding query execution plan is not a slow query.

[0082] Further, the detection of whether the plan in the query execution plan contains the plan in the historical slow query according to the slow query hash value corresponding to each of the sub-plans specifically includes:

[0083] obtaining the number, content, and attribute list length of all the sub-plans in the query execution plan according to the slow query hash value corresponding to each of the sub-plans;

[0084] According to the number, content and attribute list length of all the sub-plans in the query execution plan, a plan equivalence checking method and a pre-set naming mapping are adopted to determine whether there is a plan in the historical slow query that is equal to the query execution plan.

[0085] Specifically, as shown in the drawings, in the present application, the plan equivalence checking method specifically includes: Figure 4

[0086] Step S41, start;

[0087] Step S42, determine whether the number of plans in the historical slow query is equal to the number of sub-plans in the query execution plan, if yes, go to step S44, if no, go to step S43;

[0088] Step S43, return the result that the two plans are not equal;

[0089] Step S44, determine whether the comparison of all sub-plans and the currently selected plan in the historical slow query is completed, if yes, go to step S45, if no, go to step S44.1;

[0090] Step S44.1, take one sub-plan from each side for comparison;

[0091] Step S44.2, determine whether the two plans are equal, if yes, go to step S44, if no, go to step S43;

[0092] Step S45, return the result that the two plans are not equal;

[0093] Step S46, determine whether the comparison of all attributes is completed, if yes, go to step S47, if no, go to step S46.1;

[0094] Step S46.1, take one attribute from each side for comparison;

[0095] Step S46.2, determine whether it is related to naming, if yes, go to step S46.4, if no, go to step S46.3;

[0096] Step S46.3, determine whether the attribute values are equal, if yes, go to step S46, if no, go to step S43;

[0097] Step S46.4, structure mapping, update the global equivalence class mapping relationship;

[0098] Step S46.5, determine whether the mapping relationship is satisfied, if yes, go to step S46, if no, go to step S43;

[0099] Step S47, return the result that the two plans are equal. ​

[0100] Specifically, in the present application, according to the slow query hash value corresponding to each sub-plan, the number of all sub-plans in the query execution plan is obtained, and the number of sub-plans corresponding to the plans in the query execution plan and the historical slow query is compared respectively. When the number is not the same, it is considered that the two plans are not equal. If they are the same, one sub-plan is taken out from each side to compare the specific content. If they are not the same, it is considered that the two plans are not equal. If they are the same, one attribute is taken out from each side to compare. If they are all equal, it is considered that the current query execution plan is a slow query. If there is no inequality, it means that the two plans are not equal, and the current query execution plan is not a slow query.

[0101] In the judgment of whether the attributes of the two plans are equal, since each node in the plan tree contains various information, these information can be divided into two cases, one is the naming information such as table name, column name, alias, and the other is the naming-independent information. For the naming-independent information, it usually contains integer type and string type values, and the system only needs to judge whether the values of the nodes corresponding to the two plans are equal. However, the existence of alias in the query makes the judgment of naming-related information more complex. For the same query plan, the alias may change due to the operation of the user or the database optimizer, and the system needs to avoid the interference of the alias in the process of judging the plan equivalence. Therefore, the present application maps the naming information one by one. When mapping, the naming mapping table is used, which is shown in Table 2 as follows.

[0102] Table 2, naming mapping table;

[0103]

[0104]

[0105] For the members in the node involving the relationship attribute name, as shown in Table 2, for the list related to the order (such as the sorting list), the relationship attributes with the same position are mapped; for the list independent of the order (such as the output list), the concept of set is used, and the names involved in the list are mapped as a whole; for the expression, the symbol with symmetry (such as “=”) and the sub-expression form on the left and right sides of the symbol are equal, then the mapping is performed in the manner shown in the last row of Table 2.

[0106] After constructing the mapping set, the mapping set is tested to see if it is in conflict. If there is a mapping conflict in the mapping sets of the two plans, it is proved that the two query plans do not match. If there is still a mapping between the two sets, that is, the names cannot be matched one by one, it is also considered to be a match. Because the mapping of the set comes from the unordered list, it shows that the mapping of the elements in the set is not important and does not affect the result of the query plan.

[0107] By using the plan equivalence checking method and the pre-set naming mapping, it can be determined whether there is a plan equivalent to the query execution plan in the historical slow query.

[0108] In step S30, when the query execution plan does not contain the plan in the historical slow query, a pre-set timeout duration is obtained, the running time of the query execution plan is obtained in real time, when the running time exceeds the timeout duration, the current query information is saved to the historical slow query, and the query is terminated.

[0109] Specifically, as shown in the figure, Figure 2 In the present application, a timer is set before query execution, and the timing duration is the timeout duration set by the user. If the query is executed before the timeout duration, the query result can be returned to the user successfully. If the query cannot be completely executed before the timeout duration, the timer will send an interrupt signal to the executor to interrupt the query execution. When it is found that the execution is interrupted and the interruption is caused by timeout, the current query is determined as a slow query, and the related information of the slow query is stored in the slow query table.

[0110] When the query execution plan does not contain the plan in the historical slow query, a pre-set timeout duration is obtained, the running time of the query execution plan is obtained in real time, when the running time exceeds the timeout duration, the current query information is saved to the historical slow query, and the query is terminated. Specifically, it includes:

[0111] When the query execution plan does not contain the plan in the historical slow query, a pre-set timeout duration is obtained;

[0112] When the query execution plan starts running, the running time of the query execution plan is obtained in real time based on the timer;

[0113] When the running time obtained by the timer exceeds the timeout duration, the timer sends an interrupt signal to the executor, interrupts the query based on the executor, and saves the current query information to the historical slow query.

[0114] Specifically, in the present application, the query exceeding the timeout duration set by the user is terminated, and the related query information of the query is stored in the slow query table, so as to avoid the waste of user time caused by too long query time. A timer is set, and the timing duration is the timeout duration set by the user. If the query is not executed before the timeout duration, the running time obtained by the timer exceeds the timeout duration, otherwise the running time obtained by the timer does not exceed the timeout duration.

[0115] The running time of the query execution plan is obtained in real time based on the timer after the query execution plan starts running, and then the method further comprises:

[0116] When the running time of the query execution plan obtained in real time based on the timer during the running of the query execution plan does not exceed the timeout duration, the query is not terminated.

[0117] When the query is completed, the timer stops obtaining the running time of the query execution plan, and the query is ended.

[0118] Specifically, when the running time obtained by the timer does not exceed the timeout duration before the query is completed, it indicates that the query meets the requirements of the user at this time, and the query is ended when the query is completed.

[0119] Further, in the present application, when a large change occurs in the database, the original slow query may be changed into a query with higher efficiency or a query that cannot be executed; in order to verify the effectiveness of the slow query in this case, the community provides a slow query verification function, which executes all query execution plans saved in the slow query table, and if an error occurs during the execution or the execution time does not exceed the timeout time set by the user, the system will delete the record from the slow query table, thereby ensuring the effectiveness of the records in the slow query table.

[0120] Further, the present application describes the slow query optimization method based on physical plan comparison by Figure 2 , and the method specifically comprises:

[0121] Step S21, obtaining user query information;

[0122] Step S22, generating a query execution plan by an optimizer;

[0123] Step S23, judging whether the plan contains a slow query, and when yes, entering step S24, and when no, entering step S25;

[0124] Step S24, terminating the query;

[0125] Step S25, setting an execution timer and executing the query;

[0126] Step S26, judging whether the query is timed out, and when yes, entering step S27, and when no, entering step S28;

[0127] Step S27, saving the current user query information in a slow query table, and entering step S24;

[0128] Step S28, returning a result.

[0129] The application acquires user query information, generates a query execution plan based on a database optimizer according to the user query information, acquires a historical slow query, detects whether the query execution plan contains a plan in the historical slow query, acquires a preset timeout duration when the query execution plan does not contain the plan in the historical slow query, acquires a running time of the query execution plan in real time, saves current query information into the historical slow query when the running time exceeds the timeout duration, and terminates the query. In the application, the current query execution plan is judged to be a slow query or not through the historical slow query and the current query execution plan, the query is terminated if it is a slow query, and the query is avoided for a long time according to the set timeout duration if it is not a slow query, so that the effect of quickly determining whether the query physical plan contains the slow query physical plan is realized, the user can discover the slow query in advance, the influence of the slow query on the query speed is avoided, and the query efficiency is improved.

[0130] Further, as shown in Figure 5 based on the slow query optimization method based on the physical plan comparison, the application also correspondingly provides a slow query optimization system based on the physical plan comparison, wherein the slow query optimization system based on the physical plan comparison comprises:

[0131] The plan generation module 51 is configured to acquire user query information, generate a query execution plan based on a database optimizer according to the user query information.

[0132] The detection comparison module 52 is configured to acquire a historical slow query, and detect whether the query execution plan contains a plan in the historical slow query.

[0133] The termination module 53 is configured to acquire a preset timeout duration when the query execution plan does not contain the plan in the historical slow query, acquire a running time of the query execution plan in real time, save current query information into the historical slow query when the running time exceeds the timeout duration, and terminate the query.

[0134] Further, as shown in Figure 6 based on the slow query optimization method based on the physical plan comparison and the system, the application also correspondingly provides a terminal, which comprises a processor 10, a memory 20 and a display 30. Figure 6 Only part of the components of the terminal are shown, but it should be understood that all the shown components are not required to be implemented, and more or fewer components can be alternatively implemented.

[0135] The memory 20 can be an internal storage unit of the terminal in some embodiments, such as a hard disk or a memory of the terminal. The memory 20 can also be an external storage device of the terminal in other embodiments, such as a plug-in hard disk, a smart media card (SMC), a secure digital (SD) card, a flash card, etc. equipped on the terminal. Further, the memory 20 can include both the internal storage unit and the external storage device of the terminal. The memory 20 is used to store application software and various data installed on the terminal, such as program codes of the terminal, etc. The memory 20 can also be used to temporarily store data that has been output or will be output. In an embodiment, the memory 20 stores a slow query optimization program based on physical plan comparison 40, which can be executed by the processor 10 to implement the slow query optimization method based on physical plan comparison in the present application.

[0136] The processor 10 can be a central processing unit (CPU), a microprocessor or other data processing chip in some embodiments, which is used to run program codes or process data stored in the memory 20, such as to execute the slow query optimization method based on physical plan comparison, etc.

[0137] The display 30 can be an LED display, a liquid crystal display, a touch liquid crystal display, an OLED (Organic Light-Emitting Diode) touch, etc. in some embodiments. The display 30 is used to display information of the terminal and to display a visualized user interface. The components 10-30 of the terminal communicate with each other through a system bus.

[0138] In an embodiment, the following steps are implemented when the processor 10 executes the slow query optimization program based on physical plan comparison 40 in the memory 20:

[0139] Obtain user query information, generate a query execution plan according to the user query information based on a database optimizer;

[0140] Obtain a historical slow query, and detect whether the query execution plan contains a plan in the historical slow query;

[0141] When the query execution plan does not contain the plan in the historical slow query, obtain a pre-set timeout duration, obtain a running time of the query execution plan in real time, save current query information to the historical slow query when the running time exceeds the timeout duration, and terminate the query.

[0142] The method further includes:

[0143] The method further includes:

[0144] The method further includes:

[0145] The method further includes:

[0146] The method further includes:

[0147] The method further includes:

[0148] The method further includes:

[0149] The method further includes:

[0150] The method further includes:

[0151] The method further includes:

[0152] The method further includes:

[0153] The method further includes:

[0154] According to the number, content and attribute list length of all the sub-plans in the query execution plan, a plan equivalence verification method and a pre-set name mapping are used to determine whether there is a plan in the historical slow query that is equal to the query execution plan.

[0155] When the query execution plan does not contain the plan in the historical slow query, a pre-set timeout length is obtained, the running time of the query execution plan is obtained in real time, and when the running time exceeds the timeout length, the current query information is saved to the historical slow query, and the query is terminated.

[0156] When the query execution plan does not contain the plan in the historical slow query, a pre-set timeout length is obtained;

[0157] When the query execution plan starts running, the running time of the query execution plan is obtained in real time based on a timer;

[0158] When the running time obtained by the timer exceeds the timeout length, the timer sends an interrupt signal to an executor, the query is interrupted based on the executor, and the current query information is saved to the historical slow query.

[0159] When the query execution plan starts running, the running time of the query execution plan is obtained in real time based on a timer;

[0160] When the running time of the query execution plan obtained in real time by the timer during the running process does not exceed the timeout length, the query is not terminated;

[0161] When the query is completed, the timer stops obtaining the running time of the query execution plan, and the query is ended.

[0162] The application also provides a computer readable storage medium, wherein the computer readable storage medium stores a slow query optimization program based on physical plan comparison, and the slow query optimization program based on physical plan comparison is executed by a processor to realize the steps of the slow query optimization method based on physical plan comparison.

[0163] It should be noted that, in the present document, the terms "comprises / comprising" or any other variations thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but can also include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element preceded by "comprises... a" does not, without more constraints, foreclose the existence of additional identical elements in the process, method, article, or apparatus that comprises the recited element.

[0164] Of course, 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 relevant hardware (such as a processor, a controller, etc.) through a computer program, and the program can be stored in a computer readable computer readable storage medium, and the program can include the processes of the above-mentioned method embodiments when executed. The computer readable storage medium can be a memory, a magnetic disc, an optical disc, etc.

[0165] It should be understood that the application is not limited to the above examples, and those skilled in the art can make improvements or changes according to the above description, and all these improvements and changes shall fall within the protection scope of the appended claims of the present application.

Claims

1. A method for slow query optimization based on physical plan comparison, characterized in that, The method comprises the following steps: Obtaining user query information, and generating a query execution plan based on a database optimizer according to the user query information; Obtaining historical slow queries, and detecting whether the query execution plan contains a plan in the historical slow queries; When the query execution plan does not contain the plan in the historical slow queries, obtaining a pre-set timeout duration, obtaining a running time of the query execution plan in real time, saving current query information into the historical slow queries when the running time exceeds the timeout duration, and terminating the query; The method comprises the following steps: Obtaining historical slow queries, and obtaining a slow query table according to the historical slow queries; Obtaining all sub-plans in the query execution plan, taking each sub-plan in the query execution plan as a node, constructing a structure expression corresponding to each sub-plan, and generating a slow query hash value corresponding to each sub-plan according to each structure expression; According to the slow query hash value corresponding to each sub-plan, detecting whether the query execution plan contains a plan in the historical slow queries from the slow query table.

2. The method of claim 1, wherein, The method comprises the following steps: Obtaining all sub-plans in the query execution plan; Taking each sub-plan in the query execution plan as a node, constructing a structure expression with each sub-plan in the query execution plan as a root node, and using a string to represent each structure expression.

3. The physical plan based slow query optimization method of claim 1, wherein, The method comprises the following steps: According to the slow query hash value corresponding to each sub-plan, using a plan equivalence verification method to determine whether there is a plan equal to the query execution plan in the historical slow queries; When there is a plan equal to the query execution plan in the historical slow queries, the query execution plan contains a plan in the historical slow queries.

4. The physical plan based slow query optimization method of claim 3, wherein, The method comprises the following steps: According to the slow query hash value corresponding to each sub-plan, obtaining the number, content and attribute list length of all sub-plans in the query execution plan; According to the number, content and attribute list length of all sub-plans in the query execution plan, using a plan equivalence verification method and a pre-set naming mapping to determine whether there is a plan equal to the query execution plan in the historical slow queries.

5. The physical plan based slow query optimization method of claim 1, wherein, The timeout length is obtained when the query execution plan does not contain the plan in the historical slow query, the running time of the query execution plan is obtained in real time, the current query information is saved into the historical slow query when the running time exceeds the timeout length, and the query is terminated. The timeout length is obtained when the query execution plan does not contain the plan in the historical slow query. The running time of the query execution plan is obtained in real time based on the timer when the query execution plan starts running. When the running time obtained by the timer exceeds the timeout length, the timer sends an interrupt signal to the executor, the query is interrupted based on the executor, and the current query information is saved into the historical slow query.

6. The physical plan based slow query optimization method of claim 5, wherein, The running time of the query execution plan is obtained in real time based on the timer when the query execution plan starts running, and then the method further comprises: When the running time of the query execution plan obtained in real time by the timer does not exceed the timeout length during the running of the query execution plan, the query is not terminated. When the query is completed, the timer stops obtaining the running time of the query execution plan, and the query is ended.

7. A slow query optimization system based on physical plan comparison, characterized in that, The slow query optimization system based on physical plan comparison comprises: A plan generation module is configured to obtain user query information, generate a query execution plan based on a database optimizer according to the user query information. A detection comparison module is configured to obtain historical slow queries, and detect whether the query execution plan contains the plan in the historical slow queries. A termination module is configured to obtain a pre-set timeout length when the query execution plan does not contain the plan in the historical slow queries, obtain the running time of the query execution plan in real time, save the current query information into the historical slow queries when the running time exceeds the timeout length, and terminate the query. The historical slow queries are obtained, and whether the query execution plan contains the plan in the historical slow queries is detected, specifically comprising: The historical slow queries are obtained, and a slow query table is obtained according to the historical slow queries. All sub-plans in the query execution plan are obtained, each sub-plan in the query execution plan is taken as a node, a structure expression corresponding to each sub-plan is constructed, and a slow query hash value corresponding to each sub-plan is generated according to each structure expression. Whether the query execution plan contains the plan in the historical slow queries is detected from the slow query table according to the slow query hash value corresponding to each sub-plan.

8. A terminal, characterized by comprising: The terminal comprises a memory, a processor, and a slow query optimization program based on physical plan comparison stored on the memory and executable on the processor. When the slow query optimization program based on physical plan comparison is executed by the processor, the steps of the slow query optimization method based on physical plan comparison are implemented.

9. A computer-readable storage medium, characterized in that, The computer readable storage medium stores a physical plan comparison based slow query optimization program, which, when executed by the processor, implements the steps of the physical plan comparison based slow query optimization method according to any one of claims 1-6.

Images