SQL query method and apparatus, electronic device, and storage medium
By dynamically adjusting the plan cache state of SQL statements and evaluating conditions based on generation and execution time, the problem of insufficient database performance in HTAP mixed load scenarios is solved, and efficient execution in TP and AP scenarios is achieved.
Patent Information
- Authority / Receiving Office
- WO · WO
- Patent Type
- Applications
- Current Assignee / Owner
- BEIJING OCEANBASE TECHNOLOGY CO LTD
- Filing Date
- 2025-11-12
- Publication Date
- 2026-05-21
AI Technical Summary
In HTAP hybrid workload scenarios, database systems cannot adapt to both transaction processing (TP) and analytical processing (AP) scenarios simultaneously, resulting in low efficiency and high computing power consumption for some transaction processing tasks.
By generating and recording the planned generation and execution times of SQL statements, it is determined whether the plan caching conditions are met, and the plan caching status is dynamically adjusted to adapt to the needs of different scenarios.
It improves database performance in HTAP mixed load scenarios, avoids memory consumption and computing power waste, and improves the execution efficiency of SQL statements.
Smart Images
Figure CN2025134516_21052026_PF_FP_ABST
Abstract
Description
SQL query methods and devices, electronic equipment and storage media Technical Field
[0001] This specification relates to the field of database technology, and more particularly to an SQL query method and apparatus, electronic device and storage medium. Background Technology
[0002] When executing SQL statements, database systems need to parse, optimize, and compile them to form a physical execution plan before execution. Database systems can use a query plan cache mechanism to avoid repeatedly parsing, optimizing, and compiling the same or similar SQL statements. This involves caching already generated physical execution plans, so that the cached plan is used the next time a similar SQL statement is executed, avoiding the computational overhead of regenerating the execution plan. In Transactional Processing (TP) scenarios, enabling query plan caching often results in performance improvements of tens of times compared to disabling it. However, in some Analytical Processing (AP) scenarios, the execution time of the plan is many times longer than the plan generation time. In these scenarios, the plan generation time can be negligible compared to the execution time, making it more efficient for the optimizer to regenerate the plan than to cache it.
[0003] In business scenarios involving HTAP (Hybrid Transactional / Analytical Processing) mixed workloads, related technologies employ planned caching that is either normally enabled or normally disabled to process SQL statements. However, neither normally disabled nor normally enabled planned caching can adapt to all transactions, resulting in low processing efficiency and high computational consumption for some transactions. Summary of the Invention
[0004] In view of the above, this specification provides an SQL query method and apparatus, electronic device and storage medium through one or more embodiments.
[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, an SQL query method is provided, the method comprising:
[0007] In response to the obtained SQL statement marked as having plan caching disabled, an execution plan is generated for the SQL statement, and the plan generation time consumed in generating the execution plan is recorded;
[0008] Execute the execution plan and record the execution time consumed in executing the execution plan;
[0009] Based on the plan generation duration and the plan execution duration, determine whether the SQL statement meets the plan caching conditions;
[0010] If the SQL statement meets the plan caching conditions, then the SQL statement is marked as enabling plan caching.
[0011] According to a second aspect of one or more embodiments of this specification, an SQL query method is provided, the method comprising:
[0012] In response to an SQL statement that is marked as having plan caching enabled, determine whether the execution plan of the SQL statement has been cached;
[0013] If the execution plan of the SQL statement has been cached, then execute the cached execution plan, record the execution time consumed by executing the cached execution plan, and obtain the execution time consumed by generating the execution plan of the SQL statement;
[0014] Based on the plan generation duration and the plan execution duration, determine whether the SQL statement meets the plan caching conditions;
[0015] If the SQL statement does not meet the plan caching conditions, then the SQL statement is marked as having plan caching disabled.
[0016] According to a third aspect of one or more embodiments of this specification, an SQL query apparatus is provided, the apparatus comprising:
[0017] The plan generation module, in response to the obtained SQL statement marked as having plan caching disabled, generates an execution plan for the SQL statement and records the plan generation time consumed in generating the execution plan;
[0018] The execution module executes the execution plan and records the execution time consumed in executing the execution plan.
[0019] The condition judgment module determines whether the SQL statement meets the plan caching conditions based on the plan generation duration and the plan execution duration.
[0020] The cache enabling module marks the SQL statement as having planned caching enabled if the SQL statement meets the planned caching conditions.
[0021] According to a fourth aspect of one or more embodiments of this specification, an SQL query apparatus is provided, the apparatus comprising:
[0022] The cache determination module, in response to the obtained SQL statement marked as having enabled plan caching, determines whether the execution plan of the SQL statement has been cached;
[0023] The execution plan module, if the execution plan of the SQL statement has been cached, executes the cached execution plan, records the execution time consumed by executing the cached execution plan, and obtains the generation time consumed by generating the execution plan of the SQL statement;
[0024] The condition judgment module determines whether the SQL statement meets the plan caching conditions based on the plan generation duration and the plan execution duration.
[0025] The cache shutdown module marks the SQL statement as having its planned cache disabled if the SQL statement does not meet the planned cache conditions.
[0026] According to a fifth aspect of one or more embodiments of this specification, a computer program product is provided, comprising a computer program / instructions that, when executed by a processor, implement the method as described in the first or second aspect.
[0027] According to a sixth aspect of one or more embodiments of this specification, an electronic device is provided, comprising:
[0028] processor;
[0029] Memory used to store processor-executable instructions;
[0030] The processor implements the method as described in the first or second aspect by running the executable instructions.
[0031] According to a seventh aspect of one or more embodiments of this specification, a computer-readable storage medium is provided that stores computer instructions thereon, which, when executed by a processor, implement the steps of the method as described in the first or second aspect.
[0032] The technical solutions provided in the embodiments of this specification may include the following beneficial effects:
[0033] The SQL query method provided in this specification, in response to an acquired SQL statement, if the SQL statement is marked as having plan caching disabled, can generate an execution plan for the SQL statement, record the plan generation time consumed in generating the execution plan, execute the execution plan, and record the plan execution time consumed in executing the execution plan. Based on the plan generation time and the plan execution time, it can be determined whether the SQL statement meets the plan caching conditions. If so, the SQL statement can be marked as having plan caching enabled. This method, for an SQL statement, accurately evaluates whether it is suitable to enable or disable plan caching by comparing the execution time and generation time of its execution plan. This allows SQL statements suitable for having plan caching enabled (e.g., SQL statements in TP scenarios) to use cached execution plans in subsequent executions to improve SQL statement execution efficiency and database performance. Simultaneously, SQL statements unsuitable for having plan caching enabled (e.g., SQL statements in AP scenarios) have their execution plans regenerated in subsequent executions to avoid memory consumption caused by caching execution plans, thus improving database performance and solving the technical problem of databases being unable to adapt to HTAP mixed load business scenarios. Attached Figure Description
[0034] Figure 1 is a schematic diagram of a planned caching mechanism provided in an exemplary embodiment.
[0035] Figure 2 is a flowchart of an exemplary SQL query method.
[0036] Figure 3 is a flowchart of an SQL query method provided by another exemplary embodiment.
[0037] Figure 4 is a schematic diagram of the structure of a device provided in an exemplary embodiment.
[0038] Figure 5 is a block diagram of an exemplary embodiment of an SQL query apparatus.
[0039] Figure 6 is a block diagram of an SQL query apparatus provided in another exemplary embodiment. Detailed Implementation
[0040] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numerals in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with one or more embodiments of this specification. Rather, they are merely examples of apparatuses and methods consistent with some aspects of one or more embodiments of this specification as detailed in the appended claims.
[0041] It should be noted that the steps of the corresponding methods are not necessarily performed in the order shown and described in this specification in other embodiments. In some other embodiments, the methods may include more or fewer steps than described in this specification. Furthermore, a single step described in this specification may be broken down into multiple steps in other embodiments; and multiple steps described in this specification may be combined into a single step in other embodiments.
[0042] Please refer to Figure 1, which exemplarily illustrates the complete process of the planned caching mechanism.
[0043] 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 if there is a cached physical execution plan that can be reused for the current SQL statement. If a reusable physical execution plan exists, it is directly executed by the Executor to complete the execution of the current SQL statement. If no plan exists, it is processed in sequence by the Parser, Resolver, Transformer, Optimizer, and Code Generator to generate a physical execution plan in real time. This real-time generated physical execution plan is cached for subsequent SQL statement matching, and then executed by the Executor to complete the execution of the current SQL statement.
[0044] In related technologies, the use of enabling plan caching to process SQL statements in HTAP mixed load business scenarios can lead to reduced memory consumption and execution efficiency when processing SQL statements in some AP scenarios due to the caching of execution plans.
[0045] In related technologies, using the method of disabling plan caching and enabling it to process SQL statements in HTAP mixed load business scenarios can lead to wasted computing power and reduced execution efficiency when processing SQL statements in some TP scenarios due to the repeated generation of execution plans.
[0046] Based on the above-mentioned technical problems, at least one embodiment of this specification provides an SQL query method that can be applied to business scenarios with HTAP hybrid loads, so that the database can achieve high performance when processing SQL statements in both AP and TP scenarios, thereby avoiding problems such as memory waste, computing power waste, and low execution efficiency during SQL statement execution.
[0047] Please refer to Figure 2, which exemplarily illustrates a flowchart of an SQL query method, including steps S201 to S203.
[0048] In step S201, in response to the obtained SQL statement marked as having its plan cache disabled, an execution plan is generated for the SQL statement, and the plan generation time consumed in generating the execution plan is recorded.
[0049] The SQL statement mentioned above can be any SQL statement obtained by the database system. Alternatively, the SQL statement can specifically be any SQL statement that needs to be used to determine whether it is appropriate to enable or disable the plan cache based on this method. Any SQL statement can refer to a single SQL statement or a group of similar SQL statements that can share a physical execution plan with each other.
[0050] It's important to note that SQL statements can be flagged to enable or disable plan caching. For example, a flag can be set for each SQL statement (a single SQL statement or a batch of similar SQL statements). When the flag indicates "enable plan caching," the physical execution plan of the generated SQL statement can be cached. This allows the cached physical execution plan to be reused when the SQL statement is retrieved again, without needing to regenerate it. Conversely, when the flag indicates "disable plan caching," the physical execution plan of the SQL statement must be generated every time it is retrieved.
[0051] For any SQL statement marked as having plan caching disabled (hereinafter referred to as the target SQL statement), plan caching can be enabled for the target SQL statement under certain circumstances.
[0052] Specifically, in response to the target SQL statement that is marked as having its plan cache disabled, an execution plan (i.e., a physical execution plan for the target SQL statement) can be generated for the target SQL statement, and the time consumed in generating the execution plan can be recorded (which can be called the plan generation time).
[0053] For example, the target SQL statement can be processed sequentially based on the parser, binder, converter, optimizer, and code generator to obtain the execution plan of the target SQL statement.
[0054] The parser is the first stage in the SQL query optimization process, responsible for parsing the user-written SQL statement into an Abstract Syntax Tree (AST) representing its internal structure. This process includes the following main tasks:
[0055] Lexical analysis: Decomposes the SQL query string into a series of tokens, such as keywords, table names, column names, operators, etc.
[0056] Syntax analysis: Checks if the SQL syntax conforms to SQL language rules. If the query statement contains syntax errors, the parser will return error messages.
[0057] Generate an Abstract Syntax Tree (AST): Organize the parsed tags into a tree structure that reflects the logical structure of the SQL query. The AST is the foundation for subsequent processing.
[0058] The Resolver performs symbol resolution and binding after the SQL parsing phase. After the parser transforms the SQL statement into an abstract syntax tree, the Resolver can perform the following operations:
[0059] Identifier resolution: Matching identifiers in a query (such as table names and column names) with actual objects in the database (such as database tables and columns). This process typically involves querying database metadata.
[0060] Type checking: Ensures that the data types in the SQL statement are consistent, avoiding type mismatch errors.
[0061] Table and column binding: Ensure that each table, column, and other identifier in the SQL query can be correctly parsed and the corresponding object can be found.
[0062] The Transformer is a component in the SQL optimization process, responsible for converting the Abstract Syntax Tree (AST) into an Intermediate Representation (IR), typically a Logical Query Plan. The main tasks of this stage include:
[0063] Rewrite queries: Optimize queries at the logical level, such as converting subqueries into joins or eliminating redundant operations.
[0064] Logical optimization: Transforming queries to make their execution plan more logically efficient without changing the query results. Examples include merging multiple joins or rearranging the join order.
[0065] The optimizer is a core component 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 cost of various execution strategies using a series of cost models and heuristic rules, and selects the best one. Its main tasks include:
[0066] Selecting an index: Based on factors such as the data distribution of the table and the query conditions, select the most suitable index to speed up the query.
[0067] Connection order optimization: Determine the order in which connection operations are performed to minimize execution costs.
[0068] Subquery optimization: Optimize queries by converting subqueries into join operations and removing unnecessary duplicate calculations.
[0069] The code generator is responsible for translating the optimized query execution plan into low-level operations that the database can execute. These operations are typically internal database engine functions such as function calls, scans, filtering, and sorting. The generated code is a set of instructions that the specific database execution engine can directly understand and execute.
[0070] In step S202, the execution plan is executed, and the execution time consumed by executing the execution plan is recorded.
[0071] After generating the execution plan for the target SQL statement, the execution plan can be executed to achieve the desired result. Furthermore, the execution time (referred to as the plan execution time) can be recorded.
[0072] In step S203, based on the plan generation duration and the plan execution duration, it is determined whether the SQL statement meets the plan caching conditions.
[0073] The plan generation duration and execution duration mentioned above can be used to determine whether plan caching needs to be enabled or disabled for the target SQL statement. Specifically, the plan generation duration and execution duration can be used to determine whether the target SQL statement meets the plan caching conditions.
[0074] In step S204, if the SQL statement meets the plan caching condition, the SQL statement is marked as enabling plan caching.
[0075] If it is determined in step S203 that the target SQL statement meets the plan caching conditions, the target SQL statement can be marked as having plan caching enabled, that is, the plan caching function can be enabled for the target SQL statement.
[0076] In practical applications, the plan generation duration and execution duration mentioned above can be used to determine whether the target SQL statement is an AP scenario SQL statement or a TP scenario SQL statement. Generally, if the target SQL statement does not meet the plan caching conditions, it can be considered a TP scenario SQL statement.
[0077] It should be understood that when the target SQL statement is a TP (Transaction Processing) scenario SQL statement, the plan caching function can be enabled for the target SQL statement. Subsequently, when the target SQL statement is obtained again, the cached execution plan of the target SQL statement can be reused without regenerating the execution plan for the target SQL statement, thereby avoiding the waste of computing power and the decrease in execution efficiency caused by repeatedly generating the execution plan for the target SQL statement.
[0078] Optionally, the above plan caching conditions may include: the plan execution time is not greater than a preset threshold (referred to as the second threshold); and / or, the ratio of the plan execution time to the plan generation time is not greater than a preset threshold (referred to as the third threshold), that is, the plan execution time is not greater than N times the plan generation time (N is a positive integer). In practical applications, the specific content of the plan caching conditions can be set according to the actual situation and needs, and this specification does not impose any special restrictions on this.
[0079] If the execution time of the aforementioned plan is greater than N times the plan generation time, then the generation time of the physical execution plan for the target SQL statement can be considered much shorter than the execution time. In comparison, the generation time is negligible. Therefore, regenerating the execution plan when executing the target SQL statement subsequently will achieve better performance than reusing the cached execution plan (because each generated execution plan may be exactly the same or may contain subtle differences). Thus, plan caching can be disabled for the target SQL statement. Correspondingly, the fact that the plan execution time is no more than N times the plan generation time can be used as a specific condition for plan caching.
[0080] Optionally, to improve the accuracy of enabling the plan caching function for the target SQL statement and avoid frequent fluctuations in the plan caching function between startup and shutdown, the target SQL statement can be confirmed as suitable for the plan caching function only when it meets the plan caching conditions multiple times consecutively. This allows the target SQL statement to be marked as having plan caching enabled, i.e., the plan caching function is enabled for the target SQL statement. Specifically, if it is determined in step S203 that the target SQL statement meets the plan caching conditions, it can be further determined whether the number of consecutive times the target SQL statement meets the plan caching conditions reaches a preset threshold (which can be called the first threshold, preferably 5). If the number of consecutive times the target SQL statement meets the plan caching conditions reaches the first threshold, the target SQL statement can be marked as having plan caching enabled.
[0081] For example, assuming the first threshold is 5, and the target SQL statement was determined to meet the plan caching conditions in the previous four times it was obtained, then if the target SQL statement is also determined to meet the plan caching conditions when it is obtained this time, the target SQL statement can be marked as having plan caching enabled because the number of consecutive times the target SQL statement meets the plan caching conditions reaches 5. However, if the target SQL statement is determined not to meet the plan caching conditions, the target SQL statement will not be marked as having plan caching enabled because the number of consecutive times the target SQL statement meets the plan caching conditions does not reach 5. Furthermore, the next time the target SQL statement is obtained, if the target SQL statement is determined to meet the plan caching conditions, the number of consecutive times the target SQL statement meets the plan caching conditions will become 1 again.
[0082] The SQL query method provided in this specification, in response to an acquired SQL statement, if the SQL statement is marked as having plan caching disabled, can generate an execution plan for the SQL statement, record the plan generation time consumed in generating the execution plan, execute the execution plan, and record the plan execution time consumed in executing the execution plan. Based on the plan generation time and the plan execution time, it can be determined whether the SQL statement meets the plan caching conditions. If so, the SQL statement can be marked as having plan caching enabled. This method, for an SQL statement, accurately evaluates whether it is suitable to enable or disable plan caching by comparing the execution time and generation time of its execution plan. This allows SQL statements suitable for having plan caching enabled (e.g., SQL statements in TP scenarios) to use cached execution plans in subsequent executions to improve SQL statement execution efficiency and database performance. Simultaneously, SQL statements unsuitable for having plan caching enabled (e.g., SQL statements in AP scenarios) have their execution plans regenerated in subsequent executions to avoid memory consumption caused by caching execution plans, thus improving database performance and solving the technical problem of databases being unable to adapt to HTAP mixed load business scenarios.
[0083] Please refer to Figure 3, which exemplarily illustrates a flowchart of another SQL query method, including steps S301 to S304. Specific implementations of some parts of steps S301 to S304 can be found in steps S201 to S204, and will not be elaborated upon here.
[0084] In step S301, in response to the obtained SQL statement marked as having plan caching enabled, it is determined whether the execution plan of the SQL statement has been cached.
[0085] For any SQL statement marked as having plan caching enabled (hereinafter referred to as the target SQL statement), the plan caching function can be disabled for the target SQL statement under certain circumstances.
[0086] Specifically, the first step is to determine whether the physical execution plan of the target SQL statement has been cached in response to the target SQL statement that has been marked as having plan caching enabled.
[0087] In step S301, if the execution plan of the SQL statement has been cached, the cached execution plan is executed, the execution time consumed by executing the cached execution plan is recorded, and the execution time consumed by generating the execution plan of the SQL statement is obtained.
[0088] If it is determined in step S301 that the execution plan of the target SQL statement has been cached, the cached execution plan can be executed to achieve the execution of the target SQL statement. Furthermore, the time consumed in executing the cached execution plan (which can be referred to as the plan execution time) can be recorded.
[0089] In addition, to determine whether plan caching needs to be enabled or disabled for the target SQL statement, the time consumed in generating the execution plan for the target SQL statement (which can be called the plan generation time) can be obtained. In this case, the plan generation time can be the time consumed in generating the execution plan for the target SQL statement recorded in the most recent time the execution plan for the target SQL statement was generated.
[0090] Optionally, if it is determined in step S301 that the execution plan of the target SQL statement is not cached (for example, when the target SQL statement is obtained for the first time with the plan caching function enabled by default), an execution plan can be generated for the target SQL statement, the plan generation time consumed in generating the execution plan can be recorded, and the generated execution plan can be executed, and the plan execution time consumed in executing the generated execution plan can be recorded.
[0091] In step S303, based on the plan generation duration and the plan execution duration, it is determined whether the SQL statement meets the plan caching conditions.
[0092] The plan generation duration and execution duration mentioned above can be used to determine whether plan caching needs to be enabled or disabled for the target SQL statement. Specifically, the plan generation duration and execution duration can be used to determine whether the target SQL statement meets the plan caching conditions.
[0093] In step S304, if the SQL statement does not meet the plan caching conditions, the SQL statement is marked as having its plan caching disabled.
[0094] If it is determined in step S303 that the target SQL statement does not meet the plan caching conditions, the target SQL statement can be marked as having plan caching disabled, that is, the plan caching function can be disabled for the target SQL statement.
[0095] In practical applications, the plan generation duration and execution duration mentioned above can be used to determine whether the target SQL statement is an AP scenario SQL statement or a TP scenario SQL statement. Generally, if the target SQL statement meets the plan caching conditions, it can be considered an AP scenario SQL statement.
[0096] It should be understood that when the target SQL statement is an AP scenario SQL statement, the plan caching function can be disabled for the target SQL statement. Subsequently, when the target SQL statement is obtained again, an execution plan can be regenerated for the target SQL statement, thereby obtaining better execution performance.
[0097] Optionally, the above plan caching conditions may include: the plan execution time is not greater than a preset threshold (referred to as the second threshold); and / or, the ratio of the plan execution time to the plan generation time is not greater than a preset threshold (referred to as the third threshold), that is, the plan execution time is not greater than N times the plan generation time (N is a positive integer). In practical applications, the specific content of the plan caching conditions can be set according to the actual situation and needs, and this specification does not impose any special restrictions on this.
[0098] In practical applications, after disabling the plan caching function for the target SQL statement, the cached physical execution plan of the target SQL statement can be removed without deleting it. Instead, the physical execution plan of the target SQL statement can be prevented from participating in the matching of subsequent SQL statements. For example, it can be removed from the reusable plan set (which can be matched in the physical execution set within the reusable plan set each time the SQL statement is executed to determine whether there is a physical execution plan that can be reused for the SQL statement). When the plan caching function is re-enabled for the target SQL statement, it can be added back to the reusable plan set.
[0099] Optionally, to improve the accuracy of disabling the plan caching function for the target SQL statement and avoid accidental caching due to temporary factors such as poor system status or high load, the target SQL statement can be confirmed as unsuitable for the plan caching function only when it fails to meet the plan caching conditions multiple times consecutively. In this case, the target SQL statement is marked as having its plan caching disabled, i.e., the plan caching function is disabled for the target SQL statement. Specifically, if it is determined in step S303 that the target SQL statement does not meet the plan caching conditions, it can be further determined whether the number of consecutive times the target SQL statement fails to meet the plan caching conditions reaches a preset threshold (which can be called the fourth threshold, preferably 5). If the number of consecutive times the target SQL statement fails to meet the plan caching conditions reaches the fourth threshold, the target SQL statement can be marked as having its plan caching disabled.
[0100] For example, suppose the first threshold is 5, and in the previous four times the target SQL statement was obtained, it was determined that the target SQL statement did not meet the plan caching conditions. Then, when the target SQL statement is obtained this time, if it is also determined that the target SQL statement does not meet the plan caching conditions, the target SQL statement can be marked as having its plan caching disabled because the number of consecutive times the target SQL statement does not meet the plan caching conditions reaches 5. However, if it is determined that the target SQL statement meets the plan caching conditions, the target SQL statement will not be marked as having its plan caching disabled because the number of consecutive times the target SQL statement does not meet the plan caching conditions does not reach 5. And the next time the target SQL statement is obtained, if it is determined that the target SQL statement does not meet the plan caching conditions, the number of consecutive times the target SQL statement does not meet the plan caching conditions will become 1 again.
[0101] Optionally, since plan caching can improve execution efficiency and reduce computing power consumption in most cases, for the target SQL statement mentioned above, the plan caching function can be disabled for the target SQL statement only if it is ensured that disabling the plan caching function is better than enabling the plan caching function.
[0102] Specifically, if it is determined that the number of consecutive times the target SQL statement fails to meet the plan caching conditions reaches the fourth threshold, it can be further confirmed whether disabling the plan caching function is better than enabling the plan caching function for the target SQL statement. If disabling the plan caching function is better than enabling the plan caching function, the target SQL statement can be marked as having its plan caching disabled, that is, the plan caching function is disabled for the target SQL statement.
[0103] For example, to confirm whether disabling the plan caching function is better than enabling it for the target SQL statement, execution plans can be continuously generated for the target SQL statement and executed until the number of consecutive executions reaches a preset threshold (which can be called the fifth threshold). For instance, assuming the fifth threshold is 5, if the number of consecutive times the target SQL statement does not meet the plan caching condition reaches the fourth threshold, then for the next 5 times the target SQL statement is obtained, an execution plan can be generated for the target SQL statement and executed each time.
[0104] After generating and executing execution plans for the target SQL statement multiple times consecutively, the average execution time consumed in generating and executing the execution plan can be calculated (referred to as the first average time). Furthermore, the average execution time of the cached execution plan for the SQL statement can be calculated (referred to as the second average time). The first average time reflects the average execution time of the target SQL statement when the plan caching function is disabled; the second average time reflects the average execution time of the target SQL statement when the plan caching function is enabled. Therefore, the first average time and the second average time can be compared. If the first average time is not greater than the second average time, it indicates that disabling the plan caching function is better than enabling it for the target SQL statement, and thus the target SQL statement can be marked as having its plan caching disabled.
[0105] The SQL query method provided in this specification, in response to an acquired SQL statement, if the SQL statement is marked as having plan caching enabled, determines whether the execution plan of the SQL statement has been cached. If the execution plan of the SQL statement has been cached, the cached execution plan can be executed, the execution time consumed by executing the cached execution plan is recorded, and the generation time consumed by generating the execution plan of the SQL statement is obtained. Based on the generation time and execution time of the plan, it can be determined whether the SQL statement meets the plan caching conditions. If not, the SQL statement can be marked as having plan caching disabled. This method, for SQL statements, accurately assesses whether plan caching is suitable for enabling or disabling for plan caching by comparing the execution time and generation time of their execution plans. This allows SQL statements suitable for having plan caching enabled (e.g., SQL statements in TP scenarios) to use cached execution plans in subsequent executions to improve SQL statement execution efficiency and database performance. Simultaneously, SQL statements unsuitable for having plan caching enabled (e.g., SQL statements in AP scenarios) have their execution plans regenerated in subsequent executions to avoid memory consumption caused by caching execution plans, thus improving database performance and solving the technical problem of databases being unable to adapt to HTAP mixed load business scenarios.
[0106] Figure 4 is a schematic structural diagram of a device provided in an exemplary embodiment. Referring to Figure 4, at the hardware level, the device includes a processor 402, an internal bus 404, a network interface 406, a memory 408, and a non-volatile memory 410, and may also include other hardware required for tasks. One or more embodiments of this specification can be implemented in software, for example, the processor 402 reads the corresponding computer program from the non-volatile memory 410 into the memory 408 and then runs it. Of course, in addition to software implementation, one or more embodiments of this specification do not exclude other implementation methods, such as logic devices or a combination of hardware and software, etc. That is to say, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.
[0107] Please refer to Figure 5. An SQL query device can be applied to the device shown in Figure 4 to implement the technical solution of this specification. The SQL query device may include:
[0108] The plan generation module 501, in response to the obtained SQL statement marked as having plan caching disabled, generates an execution plan for the SQL statement and records the plan generation time consumed in generating the execution plan;
[0109] The execution plan module 502 executes the execution plan and records the execution time consumed by executing the execution plan.
[0110] The condition judgment module 503 determines whether the SQL statement meets the plan caching conditions based on the plan generation duration and the plan execution duration.
[0111] The cache enabling module 504 marks the SQL statement as having a planned cache enabled if the SQL statement meets the planned cache conditions.
[0112] In one possible embodiment of this specification, marking the SQL statement as having plan caching enabled if the SQL statement satisfies the plan caching condition includes:
[0113] If the SQL statement satisfies the plan caching condition, then determine whether the number of consecutive times the SQL statement satisfies the plan caching condition has reached a preset first threshold.
[0114] If the number of consecutive occurrences reaches the first threshold, the SQL statement is marked as enabling plan caching.
[0115] In one possible embodiment of this specification, the planned caching conditions include:
[0116] The planned execution duration shall not exceed a preset second threshold; and / or,
[0117] The ratio of the planned execution time to the planned generation time shall not exceed the preset third threshold.
[0118] Please refer to Figure 6. Another SQL query device can be applied to the device shown in Figure 4 to implement the technical solution of this specification. This SQL query device may include:
[0119] The cache determination module 601, in response to the obtained SQL statement marked as having enabled plan caching, determines whether the execution plan of the SQL statement has been cached;
[0120] The execution plan module 602, if the execution plan of the SQL statement has been cached, executes the cached execution plan, records the execution time consumed by executing the cached execution plan, and obtains the generation time consumed by generating the execution plan of the SQL statement;
[0121] The condition judgment module 603 determines whether the SQL statement meets the plan caching conditions based on the plan generation duration and the plan execution duration.
[0122] The cache closure module 604 marks the SQL statement as having its planned cache disabled if the SQL statement does not meet the planned cache conditions.
[0123] In one possible embodiment of this specification, the plan execution module 602 is further configured to:
[0124] If the execution plan of the SQL statement is not cached, an execution plan is generated for the SQL statement, the time consumed in generating the execution plan is recorded, and the generated execution plan is executed, with the execution time consumed in executing the generated execution plan recorded.
[0125] In one possible embodiment of this specification, marking the SQL statement as having its plan cache disabled if the SQL statement does not meet the plan cache condition includes:
[0126] If the SQL statement does not meet the plan caching condition, then determine whether the number of consecutive times the SQL statement does not meet the plan caching condition has reached a preset fourth threshold.
[0127] If the number of consecutive occurrences reaches the fourth threshold, the SQL statement is marked as having its plan cache disabled.
[0128] In one possible embodiment of this specification, marking the SQL statement as having its plan cache disabled includes:
[0129] Continuously generate execution plans for the SQL statement and execute the generated execution plans until the number of consecutive executions reaches a preset fifth threshold;
[0130] Calculate the first average time consumed in generating an execution plan for the SQL statement and executing the generated execution plan, and calculate the second average time consumed in executing the cached execution plan of the SQL statement;
[0131] Determine whether the first average duration is not greater than the second average duration;
[0132] If the first average duration is not greater than the second average duration, then the SQL statement is marked as disabling the plan cache.
[0133] In one possible embodiment of this specification, the planned caching conditions include:
[0134] The execution time consumed by the execution plan does not exceed a preset third threshold; and / or,
[0135] The ratio of the time consumed in executing the execution plan to the time consumed in generating the execution plan is not greater than the preset fourth threshold.
[0136] One or more embodiments of this specification also provide a computer program product including a computer program / instructions that, when executed by a processor, implement the steps of the method provided in the first aspect.
[0137] One or more embodiments of this specification also provide a computer-readable storage medium having computer instructions stored thereon that, when executed by a processor, implement the steps of the method as described in the first aspect.
[0138] The systems, devices, modules, or units described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer, which can take the form of a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email sending and receiving device, game console, tablet computer, wearable device, or any combination of these devices.
[0139] In a typical configuration, a computer includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.
[0140] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.
[0141] Computer-readable media, including both permanent and non-permanent, removable and non-removable media, can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, 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 technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, disk storage, quantum memory, graphene-based storage media or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.
[0142] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.
[0143] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.
[0144] The terminology used in one or more embodiments of this specification is for the purpose of describing particular embodiments only and is not intended to limit the scope of one or more embodiments of this specification. The singular forms “a,” “described,” and “the” used in one or more embodiments of this specification and in the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term “and / or” as used herein refers to and includes any or all possible combinations of one or more associated listed items.
[0145] 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, data stored, data displayed, etc.) involved in this manual are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of related data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation portals are provided for users to choose to authorize or refuse.
[0146] 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, such information should not be limited to these terms. These terms are only used to distinguish information of the same type from one another. For example, first information may also be referred to as second information without departing from the scope of one or more embodiments of this specification, and similarly, second information may also be referred to as first information. Depending on the context, the word "if" as used herein may be interpreted as "when," "in response to a determination," or "when," or "in the event of a determination."
[0147] The above description is merely a preferred embodiment of one or more embodiments of this specification and is not intended to limit the scope of one or more embodiments of this specification. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of one or more embodiments of this specification should be included within the protection scope of one or more embodiments of this specification.
Claims
1. An SQL query method, the method comprising: In response to the obtained SQL statement marked as having plan caching disabled, an execution plan is generated for the SQL statement, and the plan generation time consumed in generating the execution plan is recorded; Execute the execution plan and record the execution time consumed in executing the execution plan; Based on the plan generation duration and the plan execution duration, determine whether the SQL statement meets the plan caching conditions; If the SQL statement meets the plan caching conditions, then the SQL statement is marked as enabling plan caching.
2. The method according to claim 1, wherein if the SQL statement satisfies the plan caching condition, then marking the SQL statement as having plan caching enabled includes: If the SQL statement satisfies the plan caching condition, then determine whether the number of consecutive times the SQL statement satisfies the plan caching condition has reached a preset first threshold. If the number of consecutive occurrences reaches the first threshold, the SQL statement is marked as enabling plan caching.
3. The method according to claim 1, wherein the planned caching conditions include: The planned execution duration shall not exceed the preset second threshold; And / or, The ratio of the planned execution time to the planned generation time shall not exceed the preset third threshold.
4. An SQL query method, the method comprising: In response to an SQL statement that is marked as having plan caching enabled, determine whether the execution plan of the SQL statement has been cached; If the execution plan of the SQL statement has been cached, then execute the cached execution plan, record the execution time consumed by executing the cached execution plan, and obtain the execution time consumed by generating the execution plan of the SQL statement; Based on the plan generation duration and the plan execution duration, determine whether the SQL statement meets the plan caching conditions; If the SQL statement does not meet the plan caching conditions, then the SQL statement is marked as having plan caching disabled.
5. The method according to claim 4, further comprising: If the execution plan of the SQL statement is not cached, an execution plan is generated for the SQL statement, the time consumed in generating the execution plan is recorded, and the generated execution plan is executed, with the execution time consumed in executing the generated execution plan recorded.
6. The method according to claim 4 or 5, wherein marking the SQL statement as having its plan cache disabled if the SQL statement does not meet the plan cache condition includes: If the SQL statement does not meet the plan caching condition, then determine whether the number of consecutive times the SQL statement does not meet the plan caching condition has reached a preset fourth threshold. If the number of consecutive occurrences reaches the fourth threshold, the SQL statement is marked as having its plan cache disabled.
7. The method according to claim 6, wherein marking the SQL statement as disabling the plan cache includes: Continuously generate execution plans for the SQL statement and execute the generated execution plans until the number of consecutive executions reaches a preset fifth threshold; Calculate the first average time consumed in generating an execution plan for the SQL statement and executing the generated execution plan, and calculate the second average time consumed in executing the cached execution plan of the SQL statement; Determine whether the first average duration is not greater than the second average duration; If the first average duration is not greater than the second average duration, then the SQL statement is marked as disabling the plan cache.
8. The method according to claim 4, wherein the planned caching conditions include: The time consumed in executing the execution plan shall not exceed the preset third threshold; And / or, The ratio of the time consumed in executing the execution plan to the time consumed in generating the execution plan is not greater than the preset fourth threshold.
9. An SQL query apparatus, the apparatus comprising: The plan generation module, in response to the obtained SQL statement marked as having plan caching disabled, generates an execution plan for the SQL statement and records the plan generation time consumed in generating the execution plan; The execution module executes the execution plan and records the execution time consumed in executing the execution plan. The condition judgment module determines whether the SQL statement meets the plan caching conditions based on the plan generation duration and the plan execution duration. The cache enabling module marks the SQL statement as having planned caching enabled if the SQL statement meets the planned caching conditions.
10. An SQL query apparatus, the apparatus comprising: The cache determination module, in response to the obtained SQL statement marked as having enabled plan caching, determines whether the execution plan of the SQL statement has been cached; The execution plan module, if the execution plan of the SQL statement has been cached, executes the cached execution plan, records the execution time consumed by executing the cached execution plan, and obtains the generation time consumed by generating the execution plan of the SQL statement; The condition judgment module determines whether the SQL statement meets the plan caching conditions based on the plan generation duration and the plan execution duration. The cache shutdown module marks the SQL statement as having its planned cache disabled if the SQL statement does not meet the planned cache conditions.
11. A computer program product comprising a computer program / instructions that, when executed by a processor, implement the steps of the method according to any one of claims 1 to 8.
12. An electronic device, comprising: processor; Memory used to store processor-executable instructions; The processor implements the method as described in any one of claims 1 to 8 by executing the executable instructions.
13. A computer-readable storage medium having stored thereon computer instructions that, when executed by a processor, implement the steps of the method as claimed in any one of claims 1 to 8.