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, and the method comprises the following steps: querying a current to-be-processed SQL statement in a preset cache region; if the cache data matched with the SQL statement is not queried in the cache region, generating an execution plan for the SQL statement, and storing outline data of an optimizer in the execution plan generation process as the cache data of the SQL statement in the cache region; and if the cache data matched with the SQL statement is queried in the cache region, generating an execution plan for the SQL statement based on the cache data matched with the SQL statement.

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, and compile them to form a physical execution plan before executing them. 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, to cache the generated physical execution plan, 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.

[0003] However, in the related art, the physical execution plans cached by the plan caching mechanism occupy a large amount of storage space. The system will clear some physical execution plans when the remaining space is small, so that the physical execution plans are frequently generated and cleared, which reduces the effect of the plan caching mechanism and the query performance of the system. 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: According to a first aspect of one or more embodiments of this specification, a SQL query method is proposed, the method comprising: Query the preset cache area for the SQL statement currently to be processed; If no cache data matching the SQL statement is found in the cache area, an execution plan is generated for the SQL statement, and the outline data of the optimizer during the execution plan generation process is stored in the cache area as cache data of the SQL statement; If cache data matching the SQL statement is found in the cache area, an execution plan is generated for the SQL statement based on the cache data matching the SQL statement.

[0006] In a possible embodiment of this specification, the method further includes: If no cache data matching the SQL statement is found in the cache area, and the remaining space in the cache area is not less than a preset threshold, the execution plan is stored in the cache area as cache data of the SQL statement; If cache data matching the SQL statement is found in the cache area, generating an execution plan for the SQL statement based on the cache data matching the SQL statement includes: If cache data matching the SQL statement is found in the cache area, and an execution plan for the SQL statement does not exist in the cache data, an execution plan is generated based on outline data in the cache data.

[0007] In a possible embodiment of this specification, the method further includes: If cache data matching the SQL statement is found in the cache area, and the execution plan of the SQL statement exists in the cache data, the execution plan of the SQL statement is used.

[0008] In a possible embodiment of this specification, the method further includes: If the remaining space in the cache area is less than the space recovery threshold, at least one execution plan in the cache area is deleted.

[0009] In a possible embodiment of this specification, the method further includes: If the remaining space in the cache area is less than the space recovery threshold, at least one execution plan and / or at least one outline data in the cache area is deleted.

[0010] In a possible embodiment of the present specification, deleting at least one execution plan and / or at least one outline data in the cache area includes: For each execution plan in the cache area, determining a performance benefit index of the execution plan according to the execution frequency and occupied space of the execution plan; For each outline data in the cache area, determining a performance benefit index of the outline data according to the usage frequency and occupied space of the outline data; At least one execution plan and / or at least one outline data with the lowest performance benefit index in the cache area is deleted until the remaining space in the cache area is not less than the recycling stop threshold.

[0011] In a possible embodiment of the present specification, determining the performance benefit index of the execution plan according to the execution frequency and occupied space of the execution plan includes: The performance benefit index of the execution plan is determined according to the execution frequency, generation time and occupied space of the execution plan.

[0012] In a possible embodiment of the present specification, determining the performance benefit index of the outline data according to the usage frequency and occupied space of the outline data includes: A performance benefit indicator of the outline data is determined according to the usage frequency and occupied space of the outline data, the generation time of the corresponding execution plan, and the time and occupied space of generating the execution plan based on the outline data.

[0013] In a possible embodiment of the present specification, generating an execution plan for the SQL statement includes: The SQL statement is processed in sequence based on a parser, a binder, a converter, an optimizer and a code generator to obtain an execution plan of the SQL statement.

[0014] According to a second aspect of one or more embodiments of this specification, a SQL query device is provided, the device comprising: A query module is used to query the SQL statement currently to be processed in a preset buffer area; A cache module, configured to generate an execution plan for the SQL statement if no cache data matching the SQL statement is found in the cache area, and store the optimizer's outline data during the execution plan generation process as cache data of the SQL statement in the cache area; A multiplexing module is used to generate an execution plan for the SQL statement based on the cache data matching the SQL statement if cache data matching the SQL statement is found in the cache area.

[0015] 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.

[0016] According to a fourth aspect of one or more embodiments of this specification, an electronic device is provided, including: processor; a memory for storing processor-executable instructions; The processor implements the method as described in the first aspect by running the executable instructions.

[0017] 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.

[0018] The technical solutions provided by the embodiments of this specification may have the following beneficial effects: The SQL query method provided in the embodiment of the specification first queries the SQL statement to be processed currently in a preset cache area to determine whether there is cache data matching the SQL statement in the cache area; if no cache data matching the SQL statement is found in the cache area, an execution plan is generated for the SQL statement, and the outline data of the optimizer in the execution plan generation process is stored in the cache area as the cache data of the SQL statement; if cache data matching the SQL statement is found in the cache area, an execution plan is generated for the SQL statement based on the cache data matching the SQL statement. The method generates an execution plan when the SQL statement is executed for the first time and caches the outline data of the optimizer in the plan generation process, so that when the SQL statement is not executed for the first time, the execution plan can be quickly generated directly based on the cached outline data, thereby improving the generation efficiency of the execution plan and the execution efficiency of the SQL statement, that is, improving the query performance of the system; and the space occupied by the outline data is much smaller than the space occupied by the physical execution plan, that is, the way of caching the outline data can improve the execution efficiency of more SQL statements relative to the way of caching the physical execution plan, and solves the problem of waste of computing power and poor query performance caused by frequent generation and clearing of physical execution plans. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] Figure 1 is a schematic diagram of a distributed database provided by an exemplary embodiment.

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

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

[0022] Figure 4 It is a schematic diagram of the relationship between multiple thresholds provided by an exemplary embodiment.

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

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

[0025] 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.

[0026] 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.

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

[0028] 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.

[0029] In the related technology, when the storage space used for plan caching is occupied by execution plans and the remaining space is small, the system will delete some execution plans to reclaim the storage space; if there are many query tasks, the execution plans in the system are frequently cached and cleared, so that each cached execution plan cannot be effectively reused, thereby wasting a lot of computing power and reducing the effect of the plan caching mechanism and the query performance of the system.

[0030] Based on the above technical problems, at least one embodiment of this specification provides an SQL query method, which can be applied to a database system, such as a stand-alone database system, or an attached Figure 2 In the distributed database system shown, the method can cache the outline data of the optimizer in the execution plan generation process when processing the SQL statement for the first time, thereby achieving a query performance improvement effect similar to that of the cached execution plan, and solving the problem of computing power waste and query performance degradation caused by frequent generation and clearing of the execution plan.

[0031] Please refer to the attached Figure 3 , which exemplarily shows a flow chart of the SQL query method, including steps S301 to S302.

[0032] In step S301, a query is performed in a preset buffer area for the SQL statement currently to be processed.

[0033] The cache area is a limited storage space allocated by the system for the plan cache mechanism. After receiving an SQL statement, the system needs to query the cache area for the SQL statement to determine whether there is cache data matching the SQL statement cached in the cache area, that is, to determine whether the SQL statement is executed for the first time by the system or not. Specifically, if cache data matching the SQL statement is found in the cache area, it is determined that the SQL statement is not executed for the first time by the system; if cache data matching the SQL statement is not found in the cache area, it is determined that the SQL statement is executed for the first time by the system. The cache data matching the SQL statement may include the optimizer's outline data during the execution plan generation process, and / or the execution plan.

[0034] For example, a cache data mapping table is preset in the cache area to record the correspondence between SQL statements and cache data. When querying the SQL statement in the cache area, the SQL statement to be queried can be compared with each SQL statement in the SQL statement column in the cache data mapping table, until a SQL statement matching the SQL statement to be queried is found in the SQL statement column, or all SQL statements in the SQL statement column are traversed (i.e., no SQL statement matching the SQL statement to be queried is found). If a SQL statement matching the SQL statement to be queried is found, the cache data corresponding to the queried SQL statement in the cache data mapping table matches the SQL statement to be queried; if a SQL statement matching the SQL statement to be queried is not found, there is no cache data matching the SQL statement to be queried in the cache area.

[0035] In step S302, if no cache data matching the SQL statement is found in the cache area, an execution plan is generated for the SQL statement, and the outline data of the optimizer during the execution plan generation process is stored in the cache area as cache data of the SQL statement.

[0036] The system can complete the data query of the SQL statement by executing the execution result.

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

[0038] 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: Lexical analysis: decomposes the SQL query string into a series of tokens, such as keywords, table names, column names, operators, etc.

[0039] 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.

[0040] Generate an abstract syntax tree (AST): Organize the tokens that have undergone syntax analysis into a tree structure that reflects the logical structure of the SQL query. The abstract syntax tree is the basis for subsequent processing.

[0041] Among them, the binder (Resolver) performs symbol parsing and binding after the SQL parsing stage. After the resolver converts the SQL statement into an abstract syntax tree, the binder can perform the following operations: 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.

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

[0043] 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.

[0044] 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: Rewrite queries: Optimize queries at the logical level, such as converting subqueries into joins or eliminating some redundant operations.

[0045] 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.

[0046] 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 logical plan of the query. 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: Select index: Select the most appropriate index to speed up the query based on factors such as table data distribution and query conditions.

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

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

[0049] The outline data obtained by the optimizer in the process of processing SQL statements is used to record the execution strategy selected by the optimizer, which can be used to guide the optimizer to efficiently generate a physical execution plan; for example, it includes rewrite content, base table path, connection order and connection algorithm.

[0050] 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.

[0051] It should be understood that after generating the execution plan of the SQL statement, the method may cache only the outline data of the optimizer; or further cache the execution plan based on caching the outline data of the optimizer. For example, the outline data of the optimizer and the execution plan may be cached when the remaining space in the cache area is not less than a preset threshold, and only the outline data of the optimizer may be cached when the remaining space in the cache area is less than a specific threshold. That is: if no cache data matching the SQL statement is found in the cache area, and the remaining space in the cache area is not less than a preset threshold, the execution plan is stored in the cache area as the cache data of the SQL statement.

[0052] In addition, the method may further include: if the remaining space in the cache area is less than the space recovery threshold, deleting at least one execution plan in the cache area. That is, when the remaining space in the cache area is less than the space recovery threshold, at least one cached execution plan is deleted until the remaining space is not less than the recovery stop threshold, wherein the space recovery threshold may be the same as the recovery stop threshold, or less than the recovery stop threshold.

[0053] That is to say, when the execution plan and outline data are stored in the cache area, if the remaining space is small, the execution plan is deleted to reclaim the space to provide cache space for the outline data or outline data plus execution plan of subsequent SQL statements.

[0054] In addition, the method may further include: if the remaining space in the cache area is less than the space recovery threshold, deleting at least one execution plan and / or at least one outline data in the cache area. That is, when the remaining space in the cache area is less than the space recovery threshold, deleting the cached at least one execution plan and / or at least one outline data until the remaining space is not less than the recovery stop threshold, wherein the space recovery threshold may be the same as the recovery stop threshold, or less than the recovery stop threshold.

[0055] That is, when the execution plan and outline data are stored in the cache area, if the remaining space is small, the execution plan and / or outline data are deleted to reclaim the space, so as to provide cache space for the outline data or outline data plus the execution plan of the subsequent SQL statement. For example, the execution plan and outline can be sorted by the execution frequency, occupied space, etc., and then some execution plans and / or outline data with low utilization rate can be selected for deletion.

[0056] Please refer to the attached Figure 4 , which exemplarily shows the relationship between the preset threshold, the space recovery threshold, and the recovery stop threshold mentioned in the above multiple embodiments, that is, the space recovery threshold>the preset threshold>the recovery stop threshold.

[0057] In step S303, if cache data matching the SQL statement is found in the cache area, an execution plan is generated for the SQL statement based on the cache data matching the SQL statement.

[0058] The system can complete the data query of the SQL statement by executing the execution result.

[0059] Exemplarily, in this step, if cache data matching the SQL statement is found in the cache area, and the execution plan of the SQL statement does not exist in the cache data, an execution plan is generated based on the outline data in the cache data.

[0060] Compared with the time consumption of plan generation for the complete process, the time consumption of generating an execution plan based on the outline data of the SQL statement is greatly shortened, thereby improving the efficiency of plan generation by orders of magnitude.

[0061] When an execution plan is generated based on outline data, the SQL statement can be processed in sequence based on the parser, binder, converter, optimizer and code generator to obtain a reused execution plan for the SQL statement; when processing the SQL statement based on the optimizer, there is no need to select a strategy but the strategy indicated in the outline data can be directly used.

[0062] As another example, in this step, if cache data matching the SQL statement is found in the cache area, and the execution plan of the SQL statement exists in the cache data, the execution plan of the SQL statement is used.

[0063] In combination with the above-mentioned multiple embodiments, it can be seen that after receiving the SQL statement, the method will query whether an execution plan matching the SQL statement is cached in the cache area. If an execution plan matching the SQL statement is cached, the execution of the SQL statement is completed by reusing the execution plan; if an execution plan matching the SQL statement is not cached, it will further query whether outline data matching the SQL statement is cached in the cache area. If outline data matching the SQL statement is cached, an execution plan is quickly generated based on the outline data, and the execution of the SQL statement is completed by executing the execution plan. If outline data matching the SQL statement is not cached, an execution plan is generated for the SQL statement, and the outline data of the optimizer in the execution plan process, or the outline data and execution plan of the optimizer in the execution plan process, are cached based on the remaining space in the cache area.

[0064] The SQL query method provided in the embodiment of the specification first queries the SQL statement to be processed currently in a preset cache area to determine whether there is cache data matching the SQL statement in the cache area; if no cache data matching the SQL statement is found in the cache area, an execution plan is generated for the SQL statement, and the outline data of the optimizer in the execution plan generation process is stored in the cache area as the cache data of the SQL statement; if cache data matching the SQL statement is found in the cache area, an execution plan is generated for the SQL statement based on the cache data matching the SQL statement. The method generates an execution plan when the SQL statement is executed for the first time and caches the outline data of the optimizer in the plan generation process, so that when the SQL statement is not executed for the first time, the execution plan can be quickly generated directly based on the cached outline data, thereby improving the generation efficiency of the execution plan and the execution efficiency of the SQL statement, that is, improving the query performance of the system; and the space occupied by the outline data is much smaller than the space occupied by the physical execution plan, that is, the way of caching the outline data can improve the execution efficiency of more SQL statements relative to the way of caching the physical execution plan, and solves the problem of waste of computing power and poor query performance caused by frequent generation and clearing of physical execution plans.

[0065] In some embodiments of the present disclosure, if the remaining space in the cache area is less than the space reclamation threshold, at least one cached execution plan and / or at least one outline data may be deleted in the following manner: First, for each execution plan in the cache area, the performance benefit index of the execution plan is determined according to the execution frequency and occupied space of the execution plan; for each outline data in the cache area, the performance benefit index of the outline data is determined according to the usage frequency and occupied space of the outline data.

[0066] Preferably, the performance benefit index of the execution plan is determined according to the execution frequency, generation time and occupied space of the execution plan. For example, the performance benefit index of the execution plan is determined according to the following formula: X=F(p) * T(p) / M(p) In the above formula, X is a performance benefit indicator, F(p) is the execution frequency of the execution plan, for example, the number of executions of the execution plan within a certain continuous time period including the current moment, T(p) is the generation time of the execution plan when it is generated according to the complete process, and M(p) is the storage space occupied by the execution plan.

[0067] Preferably, the performance benefit index of the outline data is determined according to the usage frequency and occupied space of the outline data, the generation time of the corresponding execution plan, and the generation time and occupied space of the execution plan based on the outline data. For example, the performance benefit index of the outline data is determined according to the following formula: X = F(o) * (T(p) - T(o)) / M(o) In the above formula, X is a performance benefit indicator, F(0) is the usage frequency of the outline data, for example, the number of times the outline data is used within a certain continuous time period including the current moment, T(p) is the generation time of the corresponding execution plan when it is generated according to the complete process, that is, the generation time of the plan generation process for obtaining the outline data, T(o) is the time for generating the execution plan based on the outline data, and M(o) is the storage space occupied by the outline data.

[0068] This method improves the plan cache mechanism based on the optimizer outline data, better balances the plan generation time and the space occupied by the plan cache. In the scenario of limited cache space, it can reduce the frequent elimination and generation of execution plans, thereby achieving the purpose of improving query performance.

[0069] 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.

[0070] Please refer to Figure 6 , the SQL query device can be applied to Figure 5 The SQL query device may include: The query module 601 is used to query the SQL statement currently to be processed in a preset buffer area; The cache module 602 is used to generate an execution plan for the SQL statement if no cache data matching the SQL statement is found in the cache area, and store the optimizer's outline data in the execution plan generation process as cache data of the SQL statement in the cache area; The reuse module 603 is configured to generate an execution plan for the SQL statement based on the cache data matching the SQL statement if cache data matching the SQL statement is found in the cache area.

[0071] In a possible embodiment of the present specification, the cache module is further used for: If no cache data matching the SQL statement is found in the cache area, and the remaining space in the cache area is not less than a preset threshold, the execution plan is stored in the cache area as cache data of the SQL statement; The multiplexing module is used for: If cache data matching the SQL statement is found in the cache area, and an execution plan for the SQL statement does not exist in the cache data, an execution plan is generated based on outline data in the cache data.

[0072] In a possible embodiment of this specification, the multiplexing module is further used for: If cache data matching the SQL statement is found in the cache area, and the execution plan of the SQL statement exists in the cache data, the execution plan of the SQL statement is used.

[0073] In a possible embodiment of the present specification, the device further includes a deletion module, configured to: If the remaining space in the cache area is less than the space recovery threshold, at least one execution plan in the cache area is deleted.

[0074] In a possible embodiment of the present specification, the device further includes a deletion module, configured to: If the remaining space in the cache area is less than the space recovery threshold, at least one execution plan and / or at least one outline data in the cache area is deleted.

[0075] In a possible embodiment of the present specification, the deletion module is used to: For each original execution plan in the cache area, determining a performance benefit index of the original execution plan according to the execution frequency and occupied space of the original execution plan; For each outline data in the cache area, determining a performance benefit index of the outline data according to the usage frequency and occupied space of the outline data; At least one original execution plan and / or at least one outline data with the lowest performance benefit index in the cache area is deleted until the remaining space in the cache area is not less than the recycling stop threshold.

[0076] In a possible embodiment of the present disclosure, when the deletion module is used to determine the performance benefit index of the execution plan according to the execution frequency and the occupied space of the execution plan, it is used to: The performance benefit index of the execution plan is determined according to the execution frequency, generation time and occupied space of the execution plan.

[0077] In a possible embodiment of the present disclosure, when the deletion module is used to determine the performance benefit index of the outline data according to the usage frequency and occupied space of the outline data, it is used to: A performance benefit indicator of the outline data is determined according to the usage frequency and occupied space of the outline data, the generation time of the corresponding execution plan, and the time and occupied space of generating the execution plan based on the outline data.

[0078] In a possible embodiment of the present disclosure, when the cache module is used to generate an execution plan for the SQL statement, it is used to: The SQL statement is processed in sequence based on a parser, a binder, a converter, an optimizer and a code generator to obtain an execution plan of the SQL statement.

[0079] 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.

[0080] 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.

[0081] 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.

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

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

[0084] 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.

[0085] 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.

[0086] 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.

[0087] 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.

[0088] 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.

[0089] 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".

[0090] 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: Query the preset cache area for the SQL statement currently to be processed; If no cache data matching the SQL statement is found in the cache area, an execution plan is generated for the SQL statement, and the outline data of the optimizer during the execution plan generation process is stored in the cache area as cache data of the SQL statement; If cache data matching the SQL statement is found in the cache area, an execution plan is generated for the SQL statement based on the cache data matching the SQL statement.

2. The SQL query method according to claim 1, further comprising: If no cache data matching the SQL statement is found in the cache area, and the remaining space in the cache area is not less than a preset threshold, the execution plan is stored in the cache area as cache data of the SQL statement; If cache data matching the SQL statement is found in the cache area, generating an execution plan for the SQL statement based on the cache data matching the SQL statement includes: If cache data matching the SQL statement is found in the cache area, and an execution plan for the SQL statement does not exist in the cache data, an execution plan is generated based on outline data in the cache data.

3. The SQL query method according to claim 2, further comprising: If cache data matching the SQL statement is found in the cache area, and the execution plan of the SQL statement exists in the cache data, the execution plan of the SQL statement is used.

4. The SQL query method according to claim 2, further comprising: If the remaining space in the cache area is less than the space recovery threshold, at least one execution plan in the cache area is deleted.

5. The SQL query method according to claim 2, further comprising: If the remaining space in the cache area is less than the space recovery threshold, at least one execution plan and / or at least one outline data in the cache area is deleted.

6. The SQL query method according to claim 5, wherein the deleting at least one execution plan and / or at least one outline data in the cache area comprises: For each execution plan in the cache area, determining a performance benefit index of the execution plan according to the execution frequency and occupied space of the execution plan; For each outline data in the cache area, determining a performance benefit index of the outline data according to the usage frequency and occupied space of the outline data; At least one execution plan and / or at least one outline data with the lowest performance benefit index in the cache area is deleted until the remaining space in the cache area is not less than the recycling stop threshold.

7. The SQL query method according to claim 6, wherein determining the performance benefit index of the execution plan according to the execution frequency and occupied space of the execution plan comprises: The performance benefit index of the execution plan is determined according to the execution frequency, generation time and occupied space of the execution plan.

8. The SQL query method according to claim 6, wherein determining the performance benefit index of the outline data according to the usage frequency and occupied space of the outline data comprises: A performance benefit indicator of the outline data is determined according to the usage frequency and occupied space of the outline data, the generation time of the corresponding execution plan, and the time and occupied space of generating the execution plan based on the outline data.

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

10. A SQL query device, comprising: A query module is used to query the SQL statement currently to be processed in a preset buffer area; A cache module, configured to generate an execution plan for the SQL statement if no cache data matching the SQL statement is found in the cache area, and store the optimizer's outline data during the execution plan generation process as cache data of the SQL statement in the cache area; A multiplexing module is used to generate an execution plan for the SQL statement based on the cache data matching the SQL statement if cache data matching the SQL statement is found in the cache area.

11. The SQL query device according to claim 10, wherein the cache module is further used for: If no cache data matching the SQL statement is found in the cache area, and the remaining space in the cache area is not less than a preset threshold, the execution plan is stored in the cache area as cache data of the SQL statement; The multiplexing module is used for: If cache data matching the SQL statement is found in the cache area, and an execution plan for the SQL statement does not exist in the cache data, an execution plan is generated based on outline data in the cache data.

12. The SQL query device according to claim 11, wherein the multiplexing module is further used for: If cache data matching the SQL statement is found in the cache area, and the execution plan of the SQL statement exists in the cache data, the execution plan of the SQL statement is used.

13. The SQL query device according to claim 11, further comprising a deletion module, configured to: If the remaining space in the cache area is less than the space recovery threshold, at least one execution plan and / or at least one outline data in the cache area is deleted.

14. The SQL query device according to claim 13, wherein the deletion module is used for: For each original execution plan in the cache area, determining a performance benefit index of the original execution plan according to the execution frequency and occupied space of the original execution plan; For each outline data in the cache area, determining a performance benefit index of the outline data according to the usage frequency and occupied space of the outline data; At least one original execution plan and / or at least one outline data with the lowest performance benefit index in the cache area is deleted until the remaining space in the cache area is not less than the recycling stop threshold.

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

16. 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.

17. 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