SQL query method and device, electronic equipment and storage medium

Abstract

The invention provides an SQL (Structured Query Language) query method and device, electronic equipment and a storage medium. The method comprises the following steps: generating an original execution plan for a target SQL statement, caching the original execution plan, and recording a first duration consumed for generating the original execution plan; executing the original execution plan and recording a second duration consumed for executing the original execution plan; and if the second duration is greater than a duration threshold and the second duration is not less than N times of the first duration, closing a planned caching function of the target SQL statement, where N is an integer greater than 1. According to the method, for the SQL statement of the AP scene which consumes long time to execute the original execution plan, the execution time and the generation time of the execution plan are compared to accurately evaluate whether the SQL statement is suitable for opening or closing the plan cache, so that the database performance is improved, and the technical problem that the database cannot adapt to the HTAP mixed load service scene is solved.

Description

Technical Field

[0001] One or more embodiments of the present specification relate to the field of database technology, and in particular, to a SQL query method and device, an electronic device, and a storage medium. Background Art

[0002] When executing SQL statements, the database system needs to parse, optimize, compile, and other processes to form a physical execution plan before executing. The database system can use the query plan cache mechanism to avoid repeated parsing, optimization, and compilation of the same or similar SQL statements, that is, cache the physical execution plan that has been generated, and directly use the cached physical execution plan the next time the same or similar SQL is executed, thereby avoiding the computational overhead of regenerating the execution plan. In TP (Transactional Processing) scenarios, turning on plan cache can often bring dozens of times performance improvement compared to turning off plan cache; however, in some AP (Analytical Processing) scenarios, the plan execution time is many times the plan generation time. In these scenarios, the plan generation time is even negligible compared to the plan execution time. Letting the optimizer regenerate the plan can achieve better execution performance than plan caching.

[0003] Related technologies In HTAP (Hybrid Transactional / Analytical Processing) mixed-load business scenarios, plan cache is normally turned on or off to process SQL statements. However, plan cache cannot adapt to all transactions, resulting in low processing efficiency and high computing power consumption for some transactions. Summary of the invention

[0004] In view of this, one or more embodiments of the present specification provide a SQL query method and device, an electronic device, and a storage medium.

[0005] To achieve the above objectives, one or more embodiments of this specification provide the following technical solutions:

[0006] According to a first aspect of one or more embodiments of this specification, a SQL query method is proposed, the method comprising:

[0007] Generate an original execution plan for the target SQL statement and cache it, and record a first time duration consumed in generating the original execution plan;

[0008] Executing the original execution plan and recording a second time duration consumed in executing the original execution plan;

[0009] If the second duration is greater than the duration threshold and the second duration is not less than N times the first duration, the plan cache function of the target SQL statement is disabled, where N is an integer greater than 1.

[0010] In a possible embodiment of this specification, the method further includes:

[0011] After caching the original execution plan, a plan caching function of the target SQL statement is enabled.

[0012] In a possible embodiment of the present specification, if the second duration is greater than a preset duration threshold and the second duration is not less than N times of the first duration, then disabling the plan caching function of the target SQL statement includes:

[0013] If the second duration is greater than the preset duration threshold, the second duration consumed in executing the original execution plan is recorded each time the original execution plan is executed thereafter;

[0014] If the second time duration consumed by executing the original execution plan M times in succession is not less than N times the first time duration, the plan caching function of the target SQL statement is disabled, where M is an integer greater than 1.

[0015] In a possible embodiment of the present specification, after disabling the plan cache function of the target SQL statement, the method further includes:

[0016] After each subsequent execution of the target SQL statement in a manner of generating an execution plan in real time, the third time duration consumed in generating the execution plan and the fourth time duration consumed in executing the execution plan are recorded;

[0017] If the fourth duration recorded after the target SQL statement is executed P times in a real-time execution plan generation manner is less than N times the third duration, the plan caching function of the target SQL statement is enabled, where P is an integer greater than 1.

[0018] In a possible embodiment of this specification, the method further includes:

[0019] If the fourth duration recorded after the target SQL statement is executed Q times in a real-time execution plan generation manner is not greater than the duration threshold, the plan caching function of the target SQL statement is enabled, where Q is an integer greater than 1.

[0020] In a possible embodiment of the present specification, generating an original execution plan for a target SQL statement and caching the original execution plan, and recording a first duration consumed in generating the original execution plan, includes:

[0021] If there is no cached original execution plan of the target SQL statement, an original execution plan is generated for the target SQL statement and cached, and a first duration consumed in generating the original execution plan is recorded.

[0022] In a possible embodiment of the present specification, generating an original execution plan for the target SQL statement includes:

[0023] The target SQL statement is processed in sequence based on the parser, binder, converter, optimizer and code generator to obtain the original execution plan of the target SQL statement.

[0024] In a possible embodiment of this specification, the method further includes:

[0025] If the second duration is not greater than the duration threshold, the plan caching function of the target SQL statement is enabled.

[0026] In a possible embodiment of this specification, the method further includes:

[0027] A target SQL statement is received, and an original execution plan of the SQL statement that has been cached is executed.

[0028] According to a second aspect of one or more embodiments of this specification, a SQL query device is provided, the device comprising:

[0029] A cache module, used to generate an original execution plan for a target SQL statement and cache it, and record a first time duration consumed in generating the original execution plan;

[0030] An execution module, configured to execute the original execution plan and record a second time duration consumed in executing the original execution plan;

[0031] A closing module is used to close the plan caching function of the target SQL statement if the second duration is greater than a duration threshold and the second duration is not less than N times the first duration, where N is an integer greater than 1.

[0032] According to a third aspect of one or more embodiments of this specification, a computer program product is provided, comprising a computer program / instruction, which implements the steps of the method described in the first aspect when executed by a processor.

[0033] According to a fourth aspect of one or more embodiments of this specification, an electronic device is provided, including:

[0034] processor;

[0035] a memory for storing processor-executable instructions;

[0036] The processor implements the method as described in the first aspect by running the executable instructions.

[0037] According to a fifth aspect of one or more embodiments of the present specification, a computer-readable storage medium is provided, on which computer instructions are stored, and when the instructions are executed by a processor, the steps of the method described in the first aspect are implemented.

[0038] The technical solutions provided by the embodiments of this specification may have the following beneficial effects:

[0039] The SQL query method provided in the embodiment of this specification first generates an original execution plan for the target SQL statement and caches it, and records the first time consumed to generate the original execution plan, then executes the original execution plan and records the second time consumed to execute the original execution plan. If the second time is greater than the time threshold, and the second time is not less than N times the first time, the plan cache function of the target SQL statement is turned off. For SQL statements in AP scenarios where the original execution plan takes a long time to execute, this method compares the execution time and generation time of its execution plan to accurately evaluate whether the SQL statement is suitable for turning on or off the plan cache, so that SQL statements suitable for turning on the plan cache can use the cached execution plan to improve the execution efficiency of the SQL statement and improve database performance during subsequent execution. At the same time, SQL statements that are not suitable for turning on the plan cache can regenerate the execution plan during subsequent execution to avoid the computing power consumption and memory consumption caused by the cached execution plan, thereby improving database performance and solving the technical problem that the database cannot adapt to the business scenario of HTAP mixed load. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] Figure 1 It is a schematic diagram of a plan caching mechanism provided by an exemplary embodiment.

[0041] Figure 2 It is a flowchart of a SQL query method provided by an exemplary embodiment.

[0042] Figure 3 is a flowchart of a SQL query method provided by another exemplary embodiment.

[0043] Figure 4 is a flowchart of a SQL query method provided by yet another exemplary embodiment.

[0044] Figure 5 It is a structural schematic diagram of a device provided by an exemplary embodiment.

[0045] Figure 6 It is a block diagram of a SQL query device provided by an exemplary embodiment. DETAILED DESCRIPTION

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

[0047] It should be noted that: in other embodiments, the steps of the corresponding method are not necessarily performed in the order shown and described in this specification. In some other embodiments, the steps included in the method may be more or less than those described in this specification. In addition, a single step described in this specification may be decomposed into multiple steps for description in other embodiments; and multiple steps described in this specification may be combined into a single step for description in other embodiments.

[0048] Please refer to the attached Figure 1 , which exemplarily shows the complete process of the plan caching mechanism.

[0049] The SQL statement is first parsed by the Fast Parser, and the parsing result is sent to the Plan Cache for plan matching to determine whether there is a cached physical execution plan that can be reused by the current SQL statement. If there is a reusable physical execution plan, it is directly executed by the Executor to complete the execution of the current SQL statement; if not, it is processed in turn by the Parser, Resolver, Transformer, Optimizer, and Code Generator to generate a physical execution plan in real time, cache the real-time generated physical execution plan for subsequent SQL statement matching, and execute the real-time generated physical execution plan by the Executor to complete the execution of the current SQL statement.

[0050] In the related art, in the business scenario of HTAP mixed load, the plan cache is enabled to process SQL statements, which will cause memory consumption and reduced execution efficiency due to cached execution plans when processing SQL statements in some AP scenarios.

[0051] In the related art, in the business scenario of HTAP mixed load, the method of turning off plan cache to process SQL statements is adopted. When processing SQL statements in some TP scenarios, the execution plan is repeatedly generated, resulting in a waste of computing power and a reduction in execution efficiency.

[0052] Based on the above technical problems, at least one embodiment of this specification provides a SQL query method, which can be applied in HTAP mixed load business scenarios, so that the database can obtain higher performance when processing SQL statements in AP scenarios and SQL statements in TP scenarios, thereby avoiding problems such as memory waste, computing power waste, and low execution efficiency when executing SQL statements.

[0053] Please refer to the attached Figure 2 , which exemplarily shows a flow chart of the SQL query method, including steps S201 to S203.

[0054] In step S201, an original execution plan is generated for a target SQL statement and cached, and a first duration consumed in generating the original execution plan is recorded.

[0055] Among them, the target SQL statement may be a SQL statement that needs to be judged based on the method whether it is suitable for opening plan caching or closing plan caching. Optionally, whether the SQL statement is used as the target SQL statement is judged based on whether the physical execution plan of the SQL statement is cached in the database. Because the physical execution plan of the SQL statement will be cached when judging whether the SQL statement is suitable for opening plan caching or closing plan caching based on the method. That is, this step may include: if there is no original execution plan of the target SQL statement that has been cached, that is, the execution plan of the target SQL statement is not cached in the database, then an original execution plan is generated for the target SQL statement and cached, and the first time consumed to generate the original execution plan is recorded.

[0056] For example, the target SQL statement may be processed in sequence based on a parser, a binder, a converter, an optimizer, and a code generator to obtain an original execution plan of the target SQL statement.

[0057] The parser is the first stage in the SQL query optimization process, responsible for parsing the SQL statements written by the user into an internal abstract syntax tree (AST). This process includes the following main tasks:

[0058] Lexical analysis: decompose the SQL query string into a series of tokens, such as keywords, table names, column names, operators, etc.

[0059] Syntax analysis: Checks whether the SQL syntax conforms to the SQL language rules. If there is a syntax error in the query statement, the parser will return an error message.

[0060] Generate an Abstract Syntax Tree (AST): Organize the parsed tokens into a tree structure that reflects the logical structure of the SQL query. The Abstract Syntax Tree is the basis for subsequent processing.

[0061] Among them, the binder (Resolver) performs symbol parsing and binding after the SQL parsing stage. After the parser converts the SQL statement into an abstract syntax tree, the binder can perform the following operations:

[0062] Identifier resolution: Matching identifiers in the query (such as table names, column names) with actual objects in the database (such as database tables, columns). This process usually involves querying database metadata.

[0063] Type checking: Ensure that the data types in SQL statements are consistent to avoid type mismatch errors.

[0064] Binding of tables and columns: Confirm that each table, column and other identifiers in the SQL query can be correctly parsed and find the corresponding object.

[0065] The transformer is a component in the SQL optimization process that is responsible for converting the abstract syntax tree (AST) into an intermediate representation (IR), usually a logical query plan. The main tasks of this phase include:

[0066] Rewrite queries: Optimize queries at the logical level, such as converting subqueries into joins or eliminating some redundant operations.

[0067] Logical optimization: Transform the query to make its execution plan more efficient logically, but do not change the query result. For example, merge multiple connections, rearrange the connection order, etc.

[0068] The optimizer is one of the core components in the SQL query execution process, responsible for generating the optimal physical execution plan based on the query's logical plan. The optimizer evaluates the costs of multiple execution strategies based on a series of cost models and heuristic rules, and selects the best one. The main tasks include:

[0069] Select index: Select the most appropriate index to speed up the query based on factors such as table data distribution and query conditions.

[0070] Join order optimization: Determines the order in which join operations are performed to minimize execution cost.

[0071] Subquery optimization: Optimize queries by converting subqueries into join operations and removing unnecessary repeated calculations.

[0072] The code generator is responsible for converting the optimized query execution plan into low-level operations that can be executed by the database. These operations are usually function calls, scanning, filtering, sorting, etc. within the database engine. The generated code is an instruction set that the execution engine of a specific database can directly understand and execute.

[0073] It should be understood that after caching the original execution plan, the plan caching function of the target SQL statement can be enabled. In other words, the database can enable the plan caching function for each SQL statement by default. The method is to enable some SQL statements that are not suitable for the plan caching function, such as some SQL statements in the AP scenario, to close the plan caching function in time to avoid the plan caching function affecting the execution efficiency and memory consumption of these SQL statements.

[0074] In step S202, the original execution plan is executed and the second duration consumed in executing the original execution plan is recorded.

[0075] The second duration consumed in executing the original execution plan can be used to determine whether the target SQL statement is a SQL statement for an AP scenario or a SQL statement for a TP scenario. Optionally, a preset duration threshold is used to determine whether the target SQL statement is a SQL statement for an AP scenario or a SQL statement for a TP scenario: if the second duration is greater than the duration threshold, the target SQL statement is a SQL statement for an AP scenario; if the second duration is not greater than the duration threshold, the target SQL statement is a SQL statement for a TP scenario.

[0076] It should be understood that if the second duration is not greater than the duration threshold, the plan caching function of the target SQL statement is enabled. Furthermore, the method can receive the target SQL statement and execute the original execution plan of the cached SQL statement, thereby avoiding repeated generation of an execution plan for the target SQL statement, which causes a waste of computing power and reduced efficiency.

[0077] It should be understood that the target SQL statement mentioned in the present disclosure refers to a batch of SQL statements that can share a physical execution plan with each other, rather than a single SQL statement. Specifically, the target SQL statement in step S201 refers to a SQL statement for which there is no cached execution plan in the database that can be shared with the SQL statement, and the target SQL statement mentioned outside step S201 refers to a SQL statement that can share a physical execution plan with the SQL statement mentioned in step S201, that is, a SQL statement that is the same or similar to the SQL statement mentioned in step S201.

[0078] In step S203, if the second duration is greater than the duration threshold and the second duration is not less than N times the first duration, the plan cache function of the target SQL statement is disabled, where N is an integer greater than 1.

[0079] Preferably, N is greater than 5.

[0080] If the second duration is not less than N times of the first duration, the generation time of the physical execution plan of the target SQL statement is much less than the execution time, which can be ignored. Therefore, when the target SQL statement is subsequently executed, real-time generation of the execution plan can achieve better execution performance than reusing the cached execution plan, so the plan cache function of the target SQL statement can be turned off. After the plan cache function of the target SQL statement is turned off, the physical execution plan of the cached target SQL statement will not be deleted, but the physical execution plan of the target SQL statement will only be made no longer participate in the matching of subsequent SQL statements, such as removing it from the reusable plan set (each time a SQL statement is executed, it will be matched in the physical execution set in the reusable plan set to determine whether there is a physical execution plan that can be reused for the SQL statement) (when it is reopened, it can be added back to the reusable plan set) for use after the subsequent plan cache function is turned on.

[0081] Exemplarily, each time the target SQL statement is executed in real-time generation of an execution plan, the third time consumed to generate the execution plan and the fourth time consumed to execute the execution plan are recorded; if the fourth time is not less than N times the third time, the plan caching function of the target SQL statement is kept closed; if the fourth time is less than N times the third time, the plan caching function of the target SQL statement is enabled.

[0082] In summary, when executing a certain SQL statement, the method can first match the physical execution plan that has been cached in the database (preferably in the above-mentioned reusable plan set) to determine whether the cached physical execution plan can be reused. If the match with a certain physical execution plan is successful, the physical execution plan is directly reused, that is, the SQL statement is executed in a plan cache manner; if the match with any physical execution plan is not successful, the target SQL statement is executed in a real-time execution plan generation manner, and the generated physical execution plan is cached and the plan cache function of the SQL statement is turned on or off in this method. In the subsequent process, when the plan cache function of the SQL statement is turned on, it is continuously determined whether the plan cache function of the SQL statement needs to be turned off (that is, it is determined whether "the second duration is greater than the duration threshold, and the second duration is not less than N times the first duration" is established), and when the plan cache function of the SQL statement is subsequently turned off, it is continuously determined whether the plan cache function of the SQL statement needs to be restarted (that is, it is determined whether "the second duration is not greater than the duration threshold, or the second duration is less than N times the first duration" is established).

[0083] The SQL query method provided in the embodiment of this specification first generates an original execution plan for the target SQL statement and caches it, and records the first time consumed to generate the original execution plan, then executes the original execution plan and records the second time consumed to execute the original execution plan. If the second time is greater than the time threshold, and the second time is not less than N times the first time, the plan cache function of the target SQL statement is turned off. For SQL statements in AP scenarios where the original execution plan takes a long time to execute, this method compares the execution time and generation time of its execution plan to accurately evaluate whether the SQL statement is suitable for turning on or off the plan cache, so that SQL statements suitable for turning on the plan cache can use the cached execution plan to improve the execution efficiency of the SQL statement and improve database performance during subsequent execution. At the same time, SQL statements that are not suitable for turning on the plan cache can regenerate the execution plan during subsequent execution to avoid the computing power consumption and memory consumption caused by the cached execution plan, thereby improving database performance and solving the technical problem that the database cannot adapt to the business scenario of HTAP mixed load.

[0084] In some embodiments of the present disclosure, step S203 in the above embodiment may be performed as shown in the attached Figure 3 The method shown in the figure includes steps S301 to S302.

[0085] In step S301, if the second duration is greater than a preset duration threshold, the second duration consumed in executing the original execution plan is recorded each time the original execution plan is executed thereafter.

[0086] In other words, the target SQL statement caches its original execution plan after the first execution and the plan cache function is enabled by default, so the database does not need to generate an execution plan in real time after receiving the target SQL statement, but directly reuses it after successfully matching the original execution plan. After each reuse of the original execution plan, the second time consumed to execute the plan can be recorded.

[0087] In step S302, if the second duration consumed by executing the original execution plan M times in succession is not less than N times the first duration, the plan cache function of the target SQL statement is disabled, where M is an integer greater than 1. Preferably, M is greater than 5.

[0088] Relative to the attached Figure 2 In the embodiment shown, the target SQL statement is not suitable for the plan cache function only when the execution duration of the plan to be cached for multiple consecutive times is not less than N times its generation duration, thereby increasing the accuracy of closing the plan cache function and avoiding the plan cache function being mistakenly closed due to temporary factors such as poor system status or high load.

[0089] In some embodiments of the present disclosure, after the target SQL statement turns off the plan cache function based on any of the above embodiments, the following Figure 4 The method shown determines in real time whether the target SQL statement needs to be reopened, including steps S401 and S402.

[0090] In step S401, each time after the target SQL statement is executed in a manner of generating an execution plan in real time, the third time duration consumed in generating the execution plan and the fourth time duration consumed in executing the execution plan are recorded.

[0091] In other words, after the plan cache function is turned off for the target SQL statement, the database will execute the target SQL statement by generating an execution plan in real time after receiving the target SQL statement; after each execution of the target SQL statement by generating an execution plan in real time, the third time consumed to generate the execution plan and the fourth time consumed to execute the execution plan can be recorded.

[0092] In step S402, if the fourth duration recorded after the target SQL statement is executed P times in a real-time execution plan generation manner is less than N times the third duration, the plan cache function of the target SQL statement is enabled, where P is an integer greater than 1. Preferably, P is greater than 5.

[0093] In this embodiment, when the execution time of the target SQL statement is less than N times of its generation time when the target SQL statement is executed multiple times in a row in a manner of generating an execution plan in real time, it is confirmed that the target SQL statement is suitable for the plan cache function, and the plan cache function of the SQL statement is enabled; thereby, for the SQL statement whose plan cache function is mistakenly disabled due to temporary factors such as poor system status or high load, the function can be reopened to improve the execution efficiency of the SQL statement.

[0094] As a supplement to the above embodiment, the method may further include: if the fourth duration recorded after the target SQL statement is executed Q times in a real-time execution plan generation manner is not greater than the duration threshold, then enabling the plan caching function of the target SQL statement, wherein Q is an integer greater than 1. Preferably, Q is greater than 5.

[0095] If the fourth duration is not greater than the duration threshold, it means that the target SQL statement is not a SQL statement of the AP scenario, and therefore it is suitable for the plan cache function, and the function is enabled. This preferred example enables the SQL statement that mistakenly turns off the plan cache function to restart the function, thereby improving execution efficiency; and this preferred example restarts the plan cache function only when the execution duration of the SQL statement that turns off the plan cache function does not meet the requirements of the AP scenario for multiple times, which has high accuracy and avoids the phenomenon of frequent fluctuations between the start and stop of the plan cache function.

[0096] In summary, when executing a certain SQL statement, the method can first match the physical execution plan that has been cached in the database (preferably in the above-mentioned reusable plan set) to determine whether the cached physical execution plan can be reused. If the match with a certain physical execution plan is successful, the physical execution plan is directly reused, that is, the SQL statement is executed in a plan caching manner; if the match with any physical execution plan is not successful, the target SQL statement is executed in a real-time execution plan generation manner, and the generated physical execution plan is cached and the plan caching function of the SQL statement is turned on or off in the manner provided in the above-mentioned embodiment. In the subsequent process, when the plan cache function of the SQL statement is turned on, it is continuously determined whether the plan cache function of the SQL statement needs to be turned off (that is, it is determined whether "the second time duration consumed by executing the original execution plan M times in a row is not less than N times the first time duration" is established), and when the plan cache function of the SQL statement is subsequently turned off, it is continuously determined whether the plan cache function of the SQL statement needs to be restarted (that is, it is determined whether "the fourth time duration recorded after executing the target SQL statement in a real-time execution plan generation manner for P consecutive times is less than N times the third time duration, or the fourth time duration recorded after executing the target SQL statement in a real-time execution plan generation manner for Q consecutive times is not greater than the time duration threshold" is established).

[0097] Figure 5 is a schematic structural diagram of a device provided by an exemplary embodiment. Figure 5 At the hardware level, the device includes a processor 502, an internal bus 504, a network interface 506, a memory 508, and a non-volatile memory 510, and may also include hardware required for other tasks. One or more embodiments of this specification may be implemented based on software, such as the processor 502 reading the corresponding computer program from the non-volatile memory 510 into the memory 508 and then running it. Of course, in addition to the software implementation, one or more embodiments of this specification do not exclude other implementations, such as logic devices or a combination of software and hardware, etc., that is, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.

[0098] Please refer to Figure 6 , the SQL query device can be applied to Figure 5 The SQL query device may include:

[0099] The cache module 601 is used to generate an original execution plan for a target SQL statement and cache it, and record a first time duration consumed in generating the original execution plan;

[0100] An execution module 602 is used to execute the original execution plan and record a second time duration consumed in executing the original execution plan;

[0101] The closing module 603 is used to close the plan caching function of the target SQL statement if the second duration is greater than the duration threshold and the second duration is not less than N times the first duration, where N is an integer greater than 1.

[0102] In a possible embodiment of the present specification, the device further includes an opening module, which is used to:

[0103] After caching the original execution plan, a plan caching function of the target SQL statement is enabled.

[0104] In a possible embodiment of this specification, the closing module is used to:

[0105] If the second duration is greater than the preset duration threshold, the second duration consumed in executing the original execution plan is recorded each time the original execution plan is executed thereafter;

[0106] If the second time duration consumed by executing the original execution plan M times in succession is not less than N times the first time duration, the plan caching function of the target SQL statement is disabled, where M is an integer greater than 1.

[0107] In a possible embodiment of the present specification, the device further includes a restart module, configured to:

[0108] After the plan caching function of the target SQL statement is turned off, each time the target SQL statement is executed in a manner of generating an execution plan in real time, a third duration consumed for generating the execution plan and a fourth duration consumed for executing the execution plan are recorded;

[0109] If the fourth duration recorded after the target SQL statement is executed P times in a real-time execution plan generation manner is less than N times the third duration, the plan caching function of the target SQL statement is enabled, where P is an integer greater than 1.

[0110] In a possible embodiment of this specification, the restart module is further used for:

[0111] If the fourth duration recorded after the target SQL statement is executed Q times in a real-time execution plan generation manner is not greater than the duration threshold, the plan caching function of the target SQL statement is enabled, where Q is an integer greater than 1.

[0112] In a possible embodiment of the present specification, the cache module is used for:

[0113] If there is no cached original execution plan of the target SQL statement, an original execution plan is generated for the target SQL statement and cached, and a first duration consumed in generating the original execution plan is recorded.

[0114] In a possible embodiment of the present specification, when the cache module is used to generate an original execution plan for a target SQL statement, it is used to:

[0115] The target SQL statement is processed in sequence based on the parser, binder, converter, optimizer and code generator to obtain the original execution plan of the target SQL statement.

[0116] In a possible embodiment of the present specification, the device further includes a multiplexing module, configured to:

[0117] If the second duration is not greater than the duration threshold, the plan caching function of the target SQL statement is enabled.

[0118] In a possible embodiment of this specification, the multiplexing module is further used for:

[0119] A target SQL statement is received, and an original execution plan of the SQL statement that has been cached is executed.

[0120] One or more embodiments of the present specification also propose a computer program product, including a computer program / instruction, which implements the steps of the method provided in the first aspect when the computer program / instruction is executed by a processor.

[0121] One or more embodiments of the present specification also propose a computer-readable storage medium having computer instructions stored thereon, which, when executed by a processor, implement the steps of the method described in the first aspect.

[0122] The systems, devices, modules or units described in the above embodiments may be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer, which may be in the form of a personal computer, a laptop computer, a cellular phone, a camera phone, a smart phone, a personal digital assistant, a media player, a navigation device, an email transceiver, a game console, a tablet computer, a wearable device or a combination of any of these devices.

[0123] In a typical configuration, a computer includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0124] The memory may include non-permanent storage in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. The memory is an example of a computer-readable medium.

[0125] Computer-readable media include permanent and non-permanent, removable and non-removable media that can be used to store information by any method or technology. Information can be computer-readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, disk storage, quantum memory, graphene-based storage media or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include temporary computer-readable media (transitory media), such as modulated data signals and carrier waves.

[0126] It should also be noted that the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, commodity or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, commodity or device. In the absence of more restrictions, the elements defined by the sentence "comprises a ..." do not exclude the existence of other identical elements in the process, method, commodity or device including the elements.

[0127] The above is a description of a specific embodiment of the specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recorded in the claims can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the processes depicted in the drawings do not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0128] The terms used in one or more embodiments of this specification are only for the purpose of describing specific embodiments, and are not intended to limit one or more embodiments of this specification. The singular forms of "a", "said" and "the" used in one or more embodiments of this specification and the appended claims are also intended to include plural forms, unless the context clearly indicates other meanings. It should also be understood that the term "and / or" used herein refers to and includes any or all possible combinations of one or more associated listed items.

[0129] 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 manual are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0130] It should be understood that although the terms first, second, third, etc. may be used to describe various information in one or more embodiments of this specification, these information should not be limited to these terms. These terms are only used to distinguish the same type of information from each other. For example, without departing from the scope of one or more embodiments of this specification, the first information may also be referred to as the second information, and similarly, the second information may also be referred to as the first information. Depending on the context, the word "if" as used herein may be interpreted as "at the time of" or "when" or "in response to determining".

[0131] The above description is merely a preferred embodiment of one or more embodiments of the present specification and is not intended to limit one or more embodiments of the present specification. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of one or more embodiments of the present specification shall be included in the scope of protection of one or more embodiments of the present specification.

Claims

1. A SQL query method, the method comprising: Generate an original execution plan for the target SQL statement and cache it, and record a first time duration consumed in generating the original execution plan; Executing the original execution plan and recording a second time duration consumed in executing the original execution plan; If the second duration is greater than the duration threshold and the second duration is not less than N times the first duration, the plan cache function of the target SQL statement is disabled, where N is an integer greater than 1.

2. The SQL query method according to claim 1, further comprising: After caching the original execution plan, a plan caching function of the target SQL statement is enabled.

3. The SQL query method according to claim 2, wherein if the second duration is greater than a preset duration threshold and the second duration is not less than N times of the first duration, then disabling the plan cache function of the target SQL statement comprises: If the second duration is greater than the preset duration threshold, the second duration consumed in executing the original execution plan is recorded each time the original execution plan is executed thereafter; If the second time duration consumed by executing the original execution plan M times in succession is not less than N times the first time duration, the plan caching function of the target SQL statement is disabled, where M is an integer greater than 1.

4. The SQL query method according to claim 3, after disabling the plan cache function of the target SQL statement, the method further comprises: After each subsequent execution of the target SQL statement in a manner of generating an execution plan in real time, the third time duration consumed in generating the execution plan and the fourth time duration consumed in executing the execution plan are recorded; If the fourth duration recorded after the target SQL statement is executed P times in a real-time execution plan generation manner is less than N times the third duration, the plan caching function of the target SQL statement is enabled, where P is an integer greater than 1.

5. The SQL query method according to claim 4, further comprising: If the fourth duration recorded after the target SQL statement is executed Q times in a real-time execution plan generation manner is not greater than the duration threshold, the plan caching function of the target SQL statement is enabled, where Q is an integer greater than 1.

6. The SQL query method according to claim 1, wherein generating an original execution plan for a target SQL statement and caching the original execution plan, and recording a first duration consumed in generating the original execution plan, comprises: If there is no cached original execution plan of the target SQL statement, an original execution plan is generated for the target SQL statement and cached, and a first duration consumed in generating the original execution plan is recorded.

7. The SQL query method according to claim 1, wherein generating an original execution plan for a target SQL statement comprises: The target SQL statement is processed in sequence based on the parser, binder, converter, optimizer and code generator to obtain the original execution plan of the target SQL statement.

8. The SQL query method according to claim 1, further comprising: If the second duration is not greater than the duration threshold, the plan caching function of the target SQL statement is enabled.

9. The SQL query method according to claim 8, further comprising: A target SQL statement is received, and an original execution plan of the SQL statement that has been cached is executed.

10. A SQL query device, comprising: A cache module, used to generate an original execution plan for a target SQL statement and cache it, and record a first time duration consumed in generating the original execution plan; An execution module, configured to execute the original execution plan and record a second time duration consumed in executing the original execution plan; A closing module is used to close the plan caching function of the target SQL statement if the second duration is greater than a duration threshold and the second duration is not less than N times the first duration, where N is an integer greater than 1.

11. The SQL query device according to claim 10, further comprising an opening module, configured to: After caching the original execution plan, a plan caching function of the target SQL statement is enabled.

12. The SQL query device according to claim 11, wherein the closing module is used for: If the second duration is greater than the preset duration threshold, the second duration consumed in executing the original execution plan is recorded each time the original execution plan is executed thereafter; If the second duration consumed by executing the original execution plan for M consecutive times is not less than N times of the first duration, the plan caching function of the target SQL statement is disabled, wherein: M is an integer greater than 1.

13. The SQL query device according to claim 12, further comprising a restart module, configured to: After the plan caching function of the target SQL statement is turned off, each time the target SQL statement is executed in a manner of generating an execution plan in real time, a third duration consumed for generating the execution plan and a fourth duration consumed for executing the execution plan are recorded; If the fourth duration recorded after the target SQL statement is executed P times in a real-time execution plan generation manner is less than N times the third duration, the plan caching function of the target SQL statement is enabled, wherein: P is an integer greater than 1.

14. The SQL query device according to claim 13, wherein the restart module is further used for: If the fourth duration recorded after the target SQL statement is executed Q times in a row in a manner of generating an execution plan in real time is not greater than the duration threshold, the plan caching function of the target SQL statement is enabled, wherein: Q is an integer greater than 1.

15. The SQL query device according to claim 10, further comprising a multiplexing module, configured to: If the second duration is not greater than the duration threshold, the plan caching function of the target SQL statement is enabled.

16. A computer program product comprising a computer program / instructions, which when executed by a processor implement the steps of the method according to any one of claims 1 to 9.

17. An electronic device comprising: processor; a memory for storing processor-executable instructions; The processor implements the method according to any one of claims 1 to 9 by running the executable instructions.

18. A computer-readable storage medium having computer instructions stored thereon, which, when executed by a processor, implement the steps of the method according to any one of claims 1 to 9.

Images