Database query execution method and device based on dynamic query compiling cache optimization

Abstract

The invention discloses a database query execution method and device based on dynamic query compiling cache optimization, and belongs to the field of database management systems. Receiving a query statement input by a user and constructing an abstract syntax tree; generating a corresponding identifier according to the abstract syntax tree, searching a matched machine code, and loading and executing the matched machine code which can be reused to obtain an execution result; generating a corresponding executable plan tree for the query statement input by the user without finding the matched machine code or the query statement input by the user with the matched machine code incapable of being reused, generating and optimizing the machine code through dynamic compiling, obtaining the optimized machine code, and loading and executing the optimized machine code to obtain an execution result; and then an execution result is sent to the user, and machine codes in the cache are cleaned regularly. According to the method, whether the machine codes in the cache are multiplexed or not is accurately determined, so that unnecessary compilation overhead is reduced, and query execution efficiency is improved.

Description

Technical Field

[0001] The present invention belongs to the field of database management systems, and in particular relates to a database query execution method and device based on dynamic query compilation cache optimization. Background Art

[0002] When processing queries, traditional database management systems (DBMS) first need to perform query parsing, optimization, and query execution plan generation. As the query frequency increases, especially for repeated execution of similar queries, the existing query plan generation and optimization process will significantly increase system overhead, thereby affecting query response time and system throughput.

[0003] Currently, many modern RDBMS, such as PostgreSQL, MySQL, Oracle, etc., use caching mechanisms to speed up the query execution process. In particular, pre-compiled query plans and machine code caches can reduce the compilation overhead of multiple queries. However, existing query caching mechanisms often rely on fixed rules such as query type or data volume, and cannot intelligently judge the similarity between queries, which can easily lead to unnecessary cache invalidation or over-compilation, affecting performance.

[0004] In this context, the present invention proposes a query execution method based on dynamic query compilation and cache optimization, which aims to optimize the execution efficiency of database queries, reduce unnecessary compilation overhead, and ensure continuous improvement of query performance through intelligent query cache reuse and dynamic cache update mechanism. Summary of the invention

[0005] The purpose of the present invention is to address the deficiencies of the prior art and provide a database query execution method and device based on dynamic query compilation cache optimization.

[0006] The object of the present invention is achieved through the following technical solution: a database query execution method based on dynamic query compilation cache optimization, comprising the following steps: (1) Receive the query statement input by the user and perform input validity verification and syntax analysis, and then construct the corresponding abstract syntax tree based on the query statement input by the user that has passed the input validity verification and syntax analysis; (2) Generate corresponding identifiers based on the abstract syntax tree, use the identifiers to find matching machine codes and determine whether they can be reused, then load and execute the reusable machine codes that match the query statement entered by the user to obtain the execution results; (3) Generate a corresponding valid executable plan tree for the query statement input by the user for which no matching machine code is found, the matching machine code cannot be reused, or the matching machine code is in an invalid state; (4) Generate the corresponding machine code through dynamic compilation and optimize the specific execution operators in the effective executable plan tree, obtain the optimized machine code and cache it in the memory, then establish a mapping relationship between the query statement input by the user and the optimized machine code cached in the memory; finally, load and execute the optimized machine code to obtain the execution result; (5) The execution results are then sent to the user, and the machine code in the cache is cleaned up regularly.

[0007] Furthermore, the step (1) specifically includes the following sub-steps: (1.1) The database system receives a query statement input by a user sent through a client and performs an input validity check: if the query statement input by the user contains illegal characters or grammatical structures, it means that the query statement input by the user fails to pass the input validity check, and the database system then refuses to execute the query statement and returns a first error message to the client, wherein the first error message includes the specific position of the illegal characters in the query statement input by the user or the type of the illegal grammatical structure; otherwise, it means that the query statement input by the user passes the input validity check; (1.2) The database system then splits the query statement input by the user that has passed the input validity check into a lexical unit sequence consisting of multiple lexical units, and records the position information of each lexical unit in the query statement, wherein the lexical unit is an SQL keyword, identifier, operator or constant value; the identifier is a table name, column name or data type; (1.3) The database system then performs semantic analysis on the query statement input by the user: checks whether there is a table name or column name in the lexical unit sequence corresponding to the query statement and whether the data type matches the constant value. If the table name or column name exists and the data type matches the constant value, it means that the query statement input by the user passes the semantic analysis; otherwise, it means that the query statement input by the user fails the semantic analysis, and the database system then refuses to execute the query statement and returns a second error message to the client, wherein the second error message includes the missing table name or column name and the mismatched data type in the query statement input by the user; (1.4) The database system then constructs a corresponding abstract syntax tree for the query statement input by the user that has passed the input validity check and syntax analysis using a top-down recursive descent analysis method; the nodes of the abstract syntax tree are used to represent the various components of the query statement, and the various components of the query statement include a SELECT clause, a FROM clause, or a WHERE clause.

[0008] Furthermore, the step (2) specifically includes the following sub-steps: (2.1) Generate an identifier corresponding to the query statement input by the user according to the structure and parameter configuration of the abstract syntax tree corresponding to the query statement input by the user; search the cache for a machine code consistent with the identifier according to the generated identifier as the machine code matching the query statement input by the user; (2.2) Then determine whether the machine code matching the query statement input by the user found in the cache can be reused: first check whether the machine code matching the query statement input by the user is in an invalid state or a valid state. If it is in an invalid state, it means that the machine code matching the query statement input by the user cannot be reused, and then execute step (3) for the query statement; if it is in a valid state, continue to perform compatibility analysis on the machine code matching the query statement input by the user: when the deviation between the parameter range of the query statement input by the user and the parameter range of the matching machine code exceeds 10%, and the data distribution and matching of the query statement input by the user are If the similarity of the data distribution of the matched machine code does not reach 90%, the resource requirements of the matched machine code do not match the available resources of the database system, or the resource requirements of the matched machine code match the available resources of the database system and the resource requirements deviation of the matched machine code exceeds 20%, it means that the machine code matching the query statement input by the user has not passed the compatibility analysis, that is, it means that the machine code matching the query statement input by the user cannot be reused; otherwise, it means that the machine code matching the query statement input by the user has passed the compatibility analysis, that is, it means that the machine code matching the query statement input by the user can be reused; (2.3) Load and execute reusable machine code that matches the query statement input by the user. During the execution process, monitor the machine code that matches the query statement input by the user in real time: when the execution time of the matching machine code exceeds 15 seconds or the resource demand exceeds 80% of the resource utilization rate of the available resources of the database system, mark the matching machine code as invalid; otherwise, continue to execute until the matching machine code is executed and the execution result corresponding to the query statement input by the user is obtained.

[0009] Furthermore, the step (3) specifically includes the following sub-steps: (3.1) The database system implements selection pushdown, projection merging and predicate reordering operations through relational algebra equivalent transformation rules. For user-input query statements that do not find matching machine codes, matching machine codes that cannot be reused or matching machine codes that are in an invalid state, the selection nodes representing the filter conditions in the abstract syntax tree corresponding to the user-input query statements are moved to positions close to leaf nodes in the abstract syntax tree, and redundant projection nodes in the abstract syntax tree are merged. The order of predicate nodes in the abstract syntax tree is adjusted so that predicate nodes with higher selectivity are executed first, thereby generating a normalized logical query plan corresponding to the user-input query statement from the abstract syntax tree; (3.2) Then, 12 physical implementation algorithm combinations are matched for each relational operator in the normalized logical query plan; each physical implementation algorithm combination includes a table scan strategy and a Join algorithm, wherein the table scan strategy is a table sequential scan, a table index scan or a table bitmap scan strategy, and the Join algorithm is a nested loop join, a hash join, a sort merge join or an index join algorithm; then, a multi-objective optimization genetic algorithm is used to comprehensively consider the I / O overhead, CPU utilization and memory consumption factors of the database system to evaluate the cost of various physical implementation algorithm combinations corresponding to each relational operator, and the physical implementation algorithm combination with the lowest cost is selected as the optimal physical execution plan of the corresponding relational operator; then, the optimal physical execution plan combination corresponding to each relational operator is obtained by combining the optimal physical execution plan; (3.3) Construct a corresponding specific execution operator for each relational operator based on the optimal physical execution plan. Each specific execution operator is bound to the optimal physical execution plan and parameter configuration of the corresponding relational operator. The parameter configuration of the relational operator includes an index selection identifier determined according to the predicate conditions in the query statement and the table structure characteristics, and a parallelism parameter allocated based on the query statement complexity and the database system resource status. The value of the parallelism parameter is , ; Then each specific execution operator forms a complete executable plan tree; (3.4) Then verify whether the optimal physical execution plans bound to any two specific execution operators in the executable plan tree are compatible, check whether the data flow formats between all specific execution operators are consistent, evaluate whether the resource requirements of the optimal physical execution plan match the available resources of the database system, and check whether the values ​​of the parallelism parameters of each specific execution operator in the executable plan tree are reasonable: if the optimal physical execution plans bound to any two specific execution operators in the executable plan tree are compatible, the data flow formats between all specific execution operators are consistent, the resource requirements of the optimal physical execution plan match the available resources of the database system, and the values ​​of the parallelism parameters of each specific execution operator in the executable plan tree are reasonable, then the executable plan tree is valid; otherwise, the executable plan tree is invalid, and a third error message is obtained, which includes a specific execution operator compatibility conflict identifier, a database system resource overlimit warning code, a data flow mismatch description of the specific execution operator, and an unreasonable parallelism prompt; (3.5) Then repeat steps (3.2) to (3.4). During the repetitive process, the database system imposes constraints on the selection of 12 physical implementation algorithm combinations matched to each relational operator based on the third error information, adjusts the fitness function weight of the multi-objective optimization genetic algorithm, and optimizes the parameter configuration of the corresponding relational operator bound to each specific execution operator, dynamically adjusts the index selection identifier and parallelism parameter, and fine-tunes the structure of the executable plan tree to ensure that the data flow format between adjacent specific execution operators is consistent and the resource requirements of the optimal physical execution overall plan are balanced with the available resources of the database system until the executable plan tree is valid.

[0010] Furthermore, the step (4) specifically includes the following sub-steps: (4.1) The database system traverses each specific execution operator in the valid executable plan tree, extracts the physical implementation algorithm combination bound to each specific execution operator, calls the compiler's code template library to convert the table scan strategy and Join algorithm corresponding to each extracted physical implementation algorithm combination into standard IR templates, and then injects the index selection identifier and parallelism parameter in the parameter configuration bound to each specific execution operator into the corresponding standard IR template to generate parameterized IR code. Finally, all parameterized IR codes are combined into a complete parameterized IR code corresponding to the query statement input by the user according to the data flow dependency relationship between the specific execution operators; (4.2) The database system then inputs the complete parameterized IR code into the front end of the LLVM compiler to generate LLVM bitcode; then the LLVM bitcode is subjected to platform-independent optimization by the intermediate optimizer of the LLVM compiler to generate optimized complete parameterized IR code; then the optimized complete parameterized IR code is converted into assembly code of the target hardware platform according to the characteristics of the target hardware platform by the back end of the LLVM compiler, and the target hardware platform includes x86, ARM or GPU; preferably, the assembly code of the target hardware platform is converted into binary machine code by the assembler and loaded into the executable memory area to form the machine code corresponding to the query statement input by the user; (4.3) Different instructions in the machine code corresponding to the query statement input by the user are divided into different columns according to the rule of sorting related instructions in parallel and sorting unrelated instructions in columns; then the instructions in each column are sorted from large to small according to the usage rate, and the instructions with higher usage rate are closer to the cache location; then the variables in the instructions with usage rate greater than 50% are allocated to registers; finally, the code in the loop body of the machine code is copied N times to obtain the optimized machine code corresponding to the query statement input by the user; (4.4) Cache the optimized machine code corresponding to the query statement input by the user into the memory using the LRU strategy, and record the metadata information of the query statement input by the user, wherein the metadata information includes the hash value, compilation timestamp, execution frequency and resource usage of the query statement; (4.5) Establish a mapping relationship between the query statement input by the user and the optimized machine code cached in the memory, and generate a unique identifier through the syntax tree structure and parameter configuration of the query statement input by the user; (4.6) Then, the optimized machine code corresponding to the query statement input by the user is loaded and executed until the execution is completed and the execution result corresponding to the query statement input by the user is obtained.

[0011] Furthermore, the step (5) specifically includes the following sub-steps: (5.1) The database system then sends the execution result of the query statement input by the user to the user through the client, and records the performance indicators of the execution process of the query statement input by the user, including execution time, CPU usage, memory consumption and number of I / O operations; (5.2) Based on the recorded performance indicators, evaluate the effectiveness of the optimized machine code corresponding to the query statement entered by the user in the cache: if the execution time does not exceed 110% of the historical average value and the CPU usage does not exceed 80% of the number of system cores, and the memory consumption does not exceed 1.3 times the estimated query demand, and the number of I / O operations does not exceed 1.1 times the pre-optimization baseline value, then the optimized machine code corresponding to the query statement entered by the user in the cache is marked as valid; otherwise, the optimized machine code corresponding to the query statement entered by the user in the cache is marked as invalid; (5.3) Regularly clear the machine code in the cache that is marked as invalid.

[0012] The present invention also includes a database query execution device based on dynamic query compilation cache optimization, including a memory and one or more processors, wherein the memory stores executable code, and when the one or more processors execute the executable code, it is used for the above-mentioned database query execution method based on dynamic query compilation cache optimization.

[0013] The present invention also includes a computer-readable storage medium on which a program is stored. When the program is executed by a processor, the above-mentioned database query execution method based on dynamic query compilation cache optimization is implemented.

[0014] The beneficial effects of the present invention are as follows: the present invention realizes efficient optimization of database query execution by using dynamic compilation and machine code caching technology, and significantly improves the performance of the database management system (DBMS) under frequent queries. Compared with the prior art, the present invention reduces database resource consumption and improves query response speed by reducing repeated compilation and optimization overhead. The system uses machine learning technology to intelligently judge query similarity, ensure the accuracy of cache reuse, and thus improve query execution efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] Figure 1 A flowchart of a database query execution method based on dynamic query compilation cache optimization; Figure 2 The present invention is a structural diagram of a database query execution device based on dynamic query compilation cache optimization. DETAILED DESCRIPTION

[0016] In order to make the purpose, technical scheme and advantages of the present invention more clear, the present invention is further described in detail in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention, rather than all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.

[0017] Example 1: Figure 1 As shown, the present invention provides a database query execution method based on dynamic query compilation and cache optimization, comprising the following steps: (1) In the parsing phase, the query statement input by the user is received and input validity verification and syntax analysis are performed. Then, the corresponding abstract syntax tree is constructed based on the query statement input by the user that has passed the input validity verification and syntax analysis.

[0018] The step (1) specifically includes the following sub-steps: (1.1) The database system receives a query statement input by a user sent through a client and performs an input validity check: if the query statement input by the user contains illegal characters or grammatical structures, it means that the query statement input by the user has not passed the input validity check, and the database system then refuses to execute the query statement and returns a first error message to the client, wherein the first error message includes the specific location of the illegal characters in the query statement input by the user or the type of the illegal grammatical structure; otherwise, it means that the query statement input by the user has passed the input validity check.

[0019] (1.2) The database system then splits the query statement entered by the user that has passed the input validity check into a lexical unit sequence consisting of multiple lexical units, and records the position information of each lexical unit in the query statement. The lexical unit is an SQL keyword, identifier, operator or constant value; the identifier is a table name, column name or data type. The position information of each lexical unit in the query statement is used for subsequent semantic analysis and error location to ensure the parsing accuracy of the query statement.

[0020] (1.3) The database system then performs a semantic analysis on the query statement input by the user: checking whether there is a table name or column name in the lexical unit sequence corresponding to the query statement and whether the data type matches the constant value. If the table name or column name exists and the data type matches the constant value, it means that the query statement input by the user passes the semantic analysis; otherwise, it means that the query statement input by the user fails the semantic analysis. The database system then refuses to execute the query statement and returns a second error message to the client. The second error message includes the missing table name or column name and the mismatched data type in the query statement input by the user.

[0021] (1.4) The database system then constructs a corresponding abstract syntax tree for the query statement input by the user that has passed the input validity check and syntax analysis using a top-down recursive descent analysis method; the nodes of the abstract syntax tree are used to represent the various components of the query statement, and the various components of the query statement include a SELECT clause, a FROM clause, or a WHERE clause, providing a structured representation for the subsequent optimization and execution of the query statement.

[0022] (2) Generate corresponding identifiers based on the abstract syntax tree, use the identifiers to find matching machine codes and determine whether they can be reused, and then load and execute the reusable machine codes that match the query statements input by the user to obtain the execution results.

[0023] The step (2) specifically includes the following sub-steps: (2.1) Generate an identifier corresponding to the query statement input by the user based on the structure and parameter configuration of the abstract syntax tree corresponding to the query statement input by the user; and search the cache for a machine code consistent with the identifier based on the generated identifier as the machine code matching the query statement input by the user.

[0024] (2.2) Then determine whether the machine code matching the query statement input by the user found in the cache can be reused: first check whether the machine code matching the query statement input by the user is in an invalid state or a valid state. If it is in an invalid state, it means that the machine code matching the query statement input by the user cannot be reused, and then execute step (3) for the query statement; if it is in a valid state, continue to perform compatibility analysis on the machine code matching the query statement input by the user: when the deviation between the parameter range of the query statement input by the user and the parameter range of the matching machine code exceeds 10%, and the data distribution and matching of the query statement input by the user are If the similarity of data distribution of the matched machine code does not reach 90%, the resource requirement of the matched machine code does not match the available resources of the database system, or the resource requirement of the matched machine code matches the available resources of the database system and the resource requirement deviation of the matched machine code exceeds 20%, it means that the machine code matching the query statement input by the user fails the compatibility analysis, that is, it means that the machine code matching the query statement input by the user cannot be reused; otherwise, it means that the machine code matching the query statement input by the user passes the compatibility analysis, that is, it means that the machine code matching the query statement input by the user can be reused.

[0025] (2.3) Load and execute reusable machine code that matches the query statement input by the user. During the execution process, monitor the machine code that matches the query statement input by the user in real time: when the execution time of the matching machine code exceeds 15 seconds or the resource demand exceeds 80% of the resource utilization rate of the available resources of the database system, mark the matching machine code as invalid; otherwise, continue to execute until the matching machine code is executed and the execution result corresponding to the query statement input by the user is obtained.

[0026] (3) Generate a corresponding valid executable plan tree for the user-input query statement for which no matching machine code is found, the matching machine code cannot be reused, or the matching machine code is in an invalid state.

[0027] The step (3) specifically includes the following sub-steps: (3.1) The database system implements selection pushdown, projection merging and predicate reordering operations through relational algebra equivalent transformation rules to move the selection nodes representing the filter conditions in the abstract syntax tree corresponding to the user-input query statement that does not find a matching machine code, the matching machine code cannot be reused or the matching machine code is in an invalid state to a position close to the leaf node in the abstract syntax tree, so as to reduce the amount of data that the upper nodes of the abstract syntax tree need to process; merge the redundant projection nodes in the abstract syntax tree to simplify the tree structure and retain only the attribute columns ultimately required for the query; adjust the order of the predicate nodes in the abstract syntax tree so that the predicate nodes with higher selectivity are executed first, so as to generate a normalized logical query plan corresponding to the user-input query statement from the abstract syntax tree. The normalized logical query plan ensures the optimality of the query logic and provides a basis for the subsequent generation of the optimal physical execution plan.

[0028] (3.2) Then, 12 physical implementation algorithm combinations are matched for each relational operator in the normalized logical query plan; each physical implementation algorithm combination includes 1 table scan strategy and 1 Join algorithm, the table scan strategy is table sequential scan, table index scan or table bitmap scan strategy, and the Join algorithm is nested loop join, hash join, sort merge join or index join algorithm; then, a multi-objective optimization genetic algorithm is used to comprehensively consider the I / O overhead, CPU utilization and memory consumption factors of the database system to evaluate the cost of various physical implementation algorithm combinations corresponding to each relational operator, and the physical implementation algorithm combination with the lowest cost is selected as the optimal physical execution plan of the corresponding relational operator; then, the optimal physical execution plan corresponding to each relational operator is combined to obtain the optimal physical execution overall plan structure.

[0029] (3.3) Obtain an executable plan tree based on the optimal physical execution plan: Construct a corresponding specific execution operator for each relational operator according to the optimal physical execution plan. Each specific execution operator is bound to the optimal physical execution plan and parameter configuration of the corresponding relational operator. The parameter configuration of the relational operator includes an index selection identifier determined according to the predicate conditions in the query statement and the table structure characteristics, and a parallelism parameter allocated based on the query statement complexity and the database system resource status. The value of the parallelism parameter is , ; Then each specific execution operator forms a complete executable plan tree; (3.4) Then verify whether the optimal physical execution plans bound to any two specific execution operators in the executable plan tree are compatible, check whether the data flow formats between all specific execution operators are consistent, evaluate whether the resource requirements of the optimal physical execution plan match the available resources of the database system, and check whether the values ​​of the parallelism parameters of each specific execution operator in the executable plan tree are reasonable: if the optimal physical execution plans bound to any two specific execution operators in the executable plan tree are compatible, the data flow formats between all specific execution operators are consistent, the resource requirements of the optimal physical execution plan match the available resources of the database system, and the values ​​of the parallelism parameters of each specific execution operator in the executable plan tree are reasonable, then the executable plan tree is valid; otherwise, the executable plan tree is invalid, and a third error message is obtained, which includes a specific execution operator compatibility conflict identifier, a database system resource overlimit warning code, a data flow mismatch description of the specific execution operator, and an unreasonable parallelism prompt; (3.5) Then repeat steps (3.2) to (3.4). During the repetitive process, the database system imposes constraints on the selection of 12 physical implementation algorithm combinations matched to each relational operator based on the third error information, adjusts the fitness function weight of the multi-objective optimization genetic algorithm, and optimizes the parameter configuration of the corresponding relational operator bound to each specific execution operator, dynamically adjusts the index selection identifier and parallelism parameter, and fine-tunes the structure of the executable plan tree to ensure that the data flow format between adjacent specific execution operators is consistent and the resource requirements of the optimal physical execution overall plan are balanced with the available resources of the database system until the executable plan tree is valid.

[0030] (4) Generate the corresponding machine code through dynamic compilation and optimize the specific execution operators in the effective executable plan tree, obtain the optimized machine code and cache it in the memory, then establish a mapping relationship between the query statement input by the user and the optimized machine code cached in the memory; finally, load and execute the optimized machine code to obtain the execution result.

[0031] The step (4) specifically includes the following sub-steps: (4.1) The database system traverses each specific execution operator in the valid executable plan tree, extracts the physical implementation algorithm combination bound to each specific execution operator, calls the compiler's code template library to convert the table scan strategy and Join algorithm corresponding to each extracted physical implementation algorithm combination into standard IR templates respectively, and then injects the index selection identifier and parallelism parameter in the parameter configuration bound to each specific execution operator into the corresponding standard IR template to generate parameterized IR code. Finally, all parameterized IR codes are combined into a complete parameterized IR code corresponding to the query statement input by the user according to the data flow dependency relationship between the specific execution operators; the complete parameterized IR code will be used as the input for subsequent machine code compilation and optimization.

[0032] (4.2) The database system then inputs the complete parameterized IR code into the front end of the LLVM compiler to generate LLVM bitcode; then the LLVM bitcode is subjected to platform-independent optimization by the intermediate optimizer of the LLVM compiler to generate optimized complete parameterized IR code; then the optimized complete parameterized IR code is converted into assembly code of the target hardware platform according to the characteristics of the target hardware platform by the back end of the LLVM compiler, and the target hardware platform includes x86, ARM or GPU; preferably, the assembly code of the target hardware platform is converted into binary machine code by an assembler and loaded into the executable memory area to form the machine code corresponding to the query statement input by the user.

[0033] (4.3) Different instructions in the machine code corresponding to the query statement input by the user are divided into different columns according to the rule of sorting related instructions in parallel and sorting unrelated instructions in columns; then the instructions in each column are sorted from large to small according to the usage rate, and the instructions with higher usage rate are closer to the cache position to reduce the instruction loading delay; then the variables in the instructions with usage rate greater than 50% are allocated to registers to reduce the number of memory accesses and reduce the memory bandwidth pressure; finally, the code in the loop body of the machine code is copied N times to reduce the overhead of the loop control instructions and reduce the probability of branch prediction failure, thereby improving the loop execution efficiency, and obtaining the optimized machine code corresponding to the query statement input by the user; the optimized machine code has higher instruction execution efficiency and lower memory access delay, which serves as the basis for subsequent machine code execution.

[0034] (4.4) The optimized machine code corresponding to the query statement input by the user is cached in the memory using the LRU strategy, and the metadata information of the query statement input by the user is recorded at the same time, the metadata information includes the hash value, compilation timestamp, execution frequency and resource usage of the query statement, and provides support for the matching of subsequent query statements with the optimized machine code. The cache management strategy also includes the optimization of cache partitions to ensure that high-frequency query statements can be matched quickly.

[0035] (4.5) Establish a mapping relationship between the query statement entered by the user and the optimized machine code cached in the memory, and generate a unique identifier based on the syntax tree structure and parameter configuration of the query statement entered by the user. This identifier is used to quickly match and retrieve the machine code in the cache, ensuring that the same query statement can directly reuse the compiled and optimized machine code, reducing the overhead of repeated compilation.

[0036] (4.6) Then, the optimized machine code corresponding to the query statement input by the user is loaded and executed until the execution is completed and the execution result corresponding to the query statement input by the user is obtained.

[0037] (5) The execution results are then sent to the user, and the machine code in the cache is cleaned up regularly to ensure that it can be used more efficiently in subsequent queries.

[0038] The step (5) specifically includes the following sub-steps: (5.1) The database system then sends the execution results of the query statements entered by the user to the user through the client, and records the performance indicators of the query statements entered by the user during execution, including execution time, CPU usage, memory consumption, and number of I / O operations. The performance indicators serve as the basis for the subsequent effectiveness evaluation of the machine code in the cache, ensuring a comprehensive analysis of the performance of the machine code in the cache. The performance indicator record also includes contextual information about the query execution environment, such as the number of concurrent queries and system load.

[0039] (5.2) Based on the recorded performance indicators, evaluate the effectiveness of the optimized machine code corresponding to the query statement entered by the user in the cache: if the execution time does not exceed 110% of the historical average and the CPU usage does not exceed 80% of the number of system cores, and the memory consumption does not exceed 1.3 times the estimated query demand, and the number of I / O operations does not exceed 1.1 times the pre-optimization baseline value, then the optimized machine code corresponding to the query statement entered by the user in the cache is marked as valid; otherwise, the optimized machine code corresponding to the query statement entered by the user in the cache is marked as invalid, and the machine code will be removed in the next cache cleanup cycle.

[0040] (5.3) Regularly clear the machine code marked as invalid in the cache to release memory resources and ensure efficient use of the cache space in the memory.

[0041] Embodiment 2: Corresponding to the aforementioned embodiment 1 of a database query execution method based on dynamic query compilation cache optimization, the present invention also provides an embodiment of a database query execution device based on dynamic query compilation cache optimization.

[0042] See also Figure 2A database query execution device based on dynamic query compilation cache optimization provided by an embodiment of the present invention includes one or more processors for implementing a database query execution method based on dynamic query compilation cache optimization in the above embodiment.

[0043] An embodiment of a database query execution device based on dynamic query compilation cache optimization of the present invention can be applied to any device with data processing capabilities, and the device with data processing capabilities can be a device or apparatus such as a computer. The device embodiment can be implemented through software, or through hardware or a combination of software and hardware. Taking software implementation as an example, as a device in a logical sense, it is formed by the processor of any device with data processing capabilities in which it is located reading the corresponding computer program instructions in the non-volatile memory into the internal memory for execution. From the hardware level, if Figure 2 As shown in the figure, it is a hardware structure diagram of any device with data processing capability where a database query execution device based on dynamic query compilation cache optimization of the present invention is located, except Figure 2 In addition to the processor, memory, network interface, and non-volatile memory shown, any device with data processing capabilities in which the apparatus in the embodiments is located may also include other hardware, generally based on the actual functions of the device with data processing capabilities, which will not be described in detail.

[0044] The implementation process of the functions and effects of each unit in the above-mentioned device is specifically described in the implementation process of the corresponding steps in the above-mentioned method, and will not be repeated here.

[0045] For the device embodiment, since it basically corresponds to the method embodiment, the relevant parts can refer to the partial description of the method embodiment. The device embodiment described above is only schematic, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of the present invention. Ordinary technicians in this field can understand and implement it without paying creative work.

[0046] The embodiment of the present invention also provides a computer-readable storage medium, on which a program is stored. When the program is executed by a processor, a database query execution method based on dynamic query compilation cache optimization in the above embodiment is implemented. The computer-readable storage medium can be an internal storage unit of any device with data processing capability described in any of the above embodiments, such as a hard disk or a memory. The computer-readable storage medium can also be an external storage device of any device with data processing capability, such as a plug-in hard disk, a smart memory card (Smart Media Card, SMC), an SD card, a flash card (Flash Card), etc. equipped on the device. Further, the computer-readable storage medium can also include both an internal storage unit and an external storage device of any device with data processing capability. The computer-readable storage medium is used to store the computer program and other programs and data required by any device with data processing capability, and can also be used to temporarily store data that has been output or is to be output.

[0047] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A database query execution method based on dynamic query compilation cache optimization, characterized in that: The following steps are involved: (1) Receive the query statement input by the user and perform input validity verification and syntax analysis, and then construct the corresponding abstract syntax tree based on the query statement input by the user that has passed the input validity verification and syntax analysis; (2) Generate corresponding identifiers based on the abstract syntax tree, use the identifiers to find matching machine codes and determine whether they can be reused, then load and execute the reusable machine codes that match the query statement entered by the user to obtain the execution results; (3) Generate a corresponding valid executable plan tree for the query statement input by the user for which no matching machine code is found, the matching machine code cannot be reused, or the matching machine code is in an invalid state; (4) Generate the corresponding machine code through dynamic compilation and optimize the specific execution operators in the effective executable plan tree, obtain the optimized machine code and cache it in the memory, then establish a mapping relationship between the query statement input by the user and the optimized machine code cached in the memory; finally, load and execute the optimized machine code to obtain the execution result; (5) The execution results are then sent to the user, and the machine code in the cache is cleaned up regularly.

2. According to the method of claim 1, the database query execution method based on dynamic query compilation cache optimization is characterized in that: The step (1) specifically includes the following sub-steps: (1.1) The database system receives a query statement input by a user sent through a client and performs an input validity check: if the query statement input by the user contains illegal characters or grammatical structures, it means that the query statement input by the user fails to pass the input validity check, and the database system then refuses to execute the query statement and returns a first error message to the client, wherein the first error message includes the specific position of the illegal characters in the query statement input by the user or the type of the illegal grammatical structure; otherwise, it means that the query statement input by the user passes the input validity check; (1.2) The database system then splits the query statement input by the user that has passed the input validity check into a lexical unit sequence consisting of multiple lexical units, and records the position information of each lexical unit in the query statement, wherein the lexical unit is an SQL keyword, identifier, operator or constant value; the identifier is a table name, column name or data type; (1.3) The database system then performs semantic analysis on the query statement input by the user: checks whether there is a table name or column name in the lexical unit sequence corresponding to the query statement and whether the data type matches the constant value. If the table name or column name exists and the data type matches the constant value, it means that the query statement input by the user passes the semantic analysis; otherwise, it means that the query statement input by the user fails the semantic analysis, and the database system then refuses to execute the query statement and returns a second error message to the client, wherein the second error message includes the missing table name or column name and the mismatched data type in the query statement input by the user; (1.4) The database system then constructs the corresponding abstract syntax tree for the query statement input by the user that has passed the input validity check and syntax analysis using a top-down recursive descent analysis method; The nodes of the abstract syntax tree are used to represent various components of a query statement, and the various components of the query statement include a SELECT clause, a FROM clause or a WHERE clause.

3. A database query execution method based on dynamic query compilation cache optimization according to claim 2, characterized in that: The step (2) specifically includes the following sub-steps: (2.1) generating an identifier corresponding to the query statement input by the user according to the structure and parameter configuration of the abstract syntax tree corresponding to the query statement input by the user; According to the generated identifier, searching the cache for a machine code that is consistent with the identifier as the machine code that matches the query statement input by the user; (2.2) Then determine whether the machine code matching the query statement input by the user found in the cache can be reused: first check whether the machine code matching the query statement input by the user is in an invalid state or a valid state. If it is in an invalid state, it means that the machine code matching the query statement input by the user cannot be reused, and then execute step (3) for the query statement; if it is in a valid state, continue to perform compatibility analysis on the machine code matching the query statement input by the user: when the deviation between the parameter range of the query statement input by the user and the parameter range of the matching machine code exceeds 10%, and the data distribution and matching of the query statement input by the user are If the similarity of the data distribution of the matched machine code does not reach 90%, the resource requirements of the matched machine code do not match the available resources of the database system, or the resource requirements of the matched machine code match the available resources of the database system and the resource requirements deviation of the matched machine code exceeds 20%, it means that the machine code matching the query statement input by the user has not passed the compatibility analysis, that is, it means that the machine code matching the query statement input by the user cannot be reused; otherwise, it means that the machine code matching the query statement input by the user has passed the compatibility analysis, that is, it means that the machine code matching the query statement input by the user can be reused; (2.3) Load and execute reusable machine code that matches the query statement input by the user. During the execution process, monitor the machine code that matches the query statement input by the user in real time: when the execution time of the matching machine code exceeds 15 seconds or the resource demand exceeds 80% of the resource utilization rate of the available resources of the database system, mark the matching machine code as invalid; otherwise, continue to execute until the matching machine code is executed and the execution result corresponding to the query statement input by the user is obtained.

4. The database query execution method based on dynamic query compilation cache optimization according to claim 3 is characterized in that: The step (3) specifically includes the following sub-steps: (3.1) The database system implements selection pushdown, projection merging and predicate reordering operations through relational algebra equivalent transformation rules. For user-input query statements that do not find matching machine codes, matching machine codes that cannot be reused or matching machine codes that are in an invalid state, the selection nodes representing the filter conditions in the abstract syntax tree corresponding to the user-input query statements are moved to positions close to leaf nodes in the abstract syntax tree, and redundant projection nodes in the abstract syntax tree are merged. The order of predicate nodes in the abstract syntax tree is adjusted so that predicate nodes with higher selectivity are executed first, thereby generating a normalized logical query plan corresponding to the user-input query statement from the abstract syntax tree; (3.2) Then, 12 physical implementation algorithm combinations are matched for each relational operator in the normalized logical query plan; each physical implementation algorithm combination includes a table scan strategy and a Join algorithm, wherein the table scan strategy is a table sequential scan, a table index scan or a table bitmap scan strategy, and the Join algorithm is a nested loop join, a hash join, a sort merge join or an index join algorithm; then, a multi-objective optimization genetic algorithm is used to comprehensively consider the I / O overhead, CPU utilization and memory consumption factors of the database system to evaluate the cost of various physical implementation algorithm combinations corresponding to each relational operator, and the physical implementation algorithm combination with the lowest cost is selected as the optimal physical execution plan of the corresponding relational operator; then, the optimal physical execution plan combination corresponding to each relational operator is obtained by combining the optimal physical execution plan; (3.3) Construct a corresponding specific execution operator for each relational operator based on the optimal physical execution plan. Each specific execution operator is bound to the optimal physical execution plan and parameter configuration of the corresponding relational operator. The parameter configuration of the relational operator includes an index selection identifier determined according to the predicate conditions in the query statement and the table structure characteristics, and a parallelism parameter allocated based on the query statement complexity and the database system resource status. The value of the parallelism parameter is , ; Then each specific execution operator forms a complete executable plan tree; (3.4) Then verify whether the optimal physical execution plans bound to any two specific execution operators in the executable plan tree are compatible, check whether the data flow formats between all specific execution operators are consistent, evaluate whether the resource requirements of the optimal physical execution plan match the available resources of the database system, and check whether the values ​​of the parallelism parameters of each specific execution operator in the executable plan tree are reasonable: if the optimal physical execution plans bound to any two specific execution operators in the executable plan tree are compatible, the data flow formats between all specific execution operators are consistent, the resource requirements of the optimal physical execution plan match the available resources of the database system, and the values ​​of the parallelism parameters of each specific execution operator in the executable plan tree are reasonable, then the executable plan tree is valid; otherwise, the executable plan tree is invalid, and a third error message is obtained, which includes a specific execution operator compatibility conflict identifier, a database system resource overlimit warning code, a data flow mismatch description of the specific execution operator, and an unreasonable parallelism prompt; (3.5) Then repeat steps (3.2) to (3.4). During the repetitive process, the database system imposes constraints on the selection of 12 physical implementation algorithm combinations matched to each relational operator based on the third error information, adjusts the fitness function weight of the multi-objective optimization genetic algorithm, and optimizes the parameter configuration of the corresponding relational operator bound to each specific execution operator, dynamically adjusts the index selection identifier and parallelism parameter, and fine-tunes the structure of the executable plan tree to ensure that the data flow format between adjacent specific execution operators is consistent and the resource requirements of the optimal physical execution overall plan are balanced with the available resources of the database system until the executable plan tree is valid.

5. A database query execution method based on dynamic query compilation cache optimization according to claim 4, characterized in that: The step (4) specifically includes the following sub-steps: (4.1) The database system traverses each specific execution operator in the valid executable plan tree, extracts the physical implementation algorithm combination bound to each specific execution operator, calls the compiler's code template library to convert the table scan strategy and Join algorithm corresponding to each extracted physical implementation algorithm combination into standard IR templates, and then injects the index selection identifier and parallelism parameter in the parameter configuration bound to each specific execution operator into the corresponding standard IR template to generate parameterized IR code. Finally, all parameterized IR codes are combined into a complete parameterized IR code corresponding to the query statement input by the user according to the data flow dependency relationship between the specific execution operators; (4.2) The database system then inputs the complete parameterized IR code into the front end of the LLVM compiler to generate LLVM bitcode; then the LLVM bitcode is subjected to platform-independent optimization by the intermediate optimizer of the LLVM compiler to generate optimized complete parameterized IR code; then the optimized complete parameterized IR code is converted into assembly code of the target hardware platform according to the characteristics of the target hardware platform by the back end of the LLVM compiler, and the target hardware platform includes x86, ARM or GPU; preferably, the assembly code of the target hardware platform is converted into binary machine code by the assembler and loaded into the executable memory area to form the machine code corresponding to the query statement input by the user; (4.3) Different instructions in the machine code corresponding to the query statement input by the user are divided into different columns according to the rule of sorting related instructions in parallel and sorting unrelated instructions in columns; then the instructions in each column are sorted from large to small according to the usage rate, and the instructions with higher usage rate are closer to the cache location; then the variables in the instructions with usage rate greater than 50% are allocated to registers; finally, the code in the loop body of the machine code is copied N times to obtain the optimized machine code corresponding to the query statement input by the user; (4.4) Cache the optimized machine code corresponding to the query statement input by the user into the memory using the LRU strategy, and record the metadata information of the query statement input by the user, wherein the metadata information includes the hash value, compilation timestamp, execution frequency and resource usage of the query statement; (4.5) Establish a mapping relationship between the query statement input by the user and the optimized machine code cached in the memory, and generate a unique identifier through the syntax tree structure and parameter configuration of the query statement input by the user; (4.6) Then, the optimized machine code corresponding to the query statement input by the user is loaded and executed until the execution is completed and the execution result corresponding to the query statement input by the user is obtained.

6. A database query execution method based on dynamic query compilation cache optimization according to claim 5, characterized in that: The step (5) specifically includes the following sub-steps: (5.1) The database system then sends the execution result of the query statement input by the user to the user through the client, and records the performance indicators of the execution process of the query statement input by the user, including execution time, CPU usage, memory consumption and number of I / O operations; (5.2) Based on the recorded performance indicators, evaluate the effectiveness of the optimized machine code corresponding to the query statement entered by the user in the cache: if the execution time does not exceed 110% of the historical average value and the CPU usage does not exceed 80% of the number of system cores, and the memory consumption does not exceed 1.3 times the estimated query demand, and the number of I / O operations does not exceed 1.1 times the pre-optimization baseline value, then the optimized machine code corresponding to the query statement entered by the user in the cache is marked as valid; otherwise, the optimized machine code corresponding to the query statement entered by the user in the cache is marked as invalid; (5.3) Regularly clear the machine code in the cache that is marked as invalid.

7. A database query execution device based on dynamic query compilation cache optimization, characterized in that: It comprises a memory and one or more processors, wherein the memory stores executable code, and when the one or more processors execute the executable code, it is used to implement a database query execution method based on dynamic query compilation cache optimization as described in any one of claims 1-6.

8. A computer-readable storage medium, characterized in that: A program is stored thereon, and when the program is executed by a processor, a database query execution method based on dynamic query compilation cache optimization as described in any one of claims 1 to 6 is implemented.

Images