Database Query Execution Method and Device Based on Optimization of Dynamic Query Compilation Cache

Abstract

The present invention discloses a database query execution method and apparatus based on dynamic query compilation cache optimization, belonging to the field of database management systems. Receiving a query statement input by a user and constructing an abstract syntax tree; generating corresponding identifiers according to the abstract syntax tree, searching for matching machine code, loading and executing the reusable matching machine code to obtain an execution result; generating a corresponding executable plan tree for the query statement input by the user for which no matching machine code is found or the matching machine code cannot be reused, generating machine code through dynamic compilation and optimizing it to obtain optimized machine code and loading and executing it to obtain an execution result; subsequently sending the execution result to the user and periodically clearing the machine code in the cache. The present invention accurately determines whether to reuse the machine code in the cache, thereby reducing unnecessary compilation overhead and improving query execution efficiency.

Description

Technical Field

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

[0002] When a traditional database management system (DBMS) processes a query, it first needs to perform query parsing, optimization, and generate a query execution plan. With the increase in the query frequency, especially for the repeated execution of similar queries, the existing query plan generation and optimization processes will significantly increase the system overhead, thereby affecting the query response time and system throughput.

[0003] Currently, many modern RDBMSs, such as PostgreSQL, MySQL, Oracle, etc., adopt a caching mechanism to accelerate the query execution process. In particular, pre-compiled query plans and machine code caches can reduce the compilation overhead of multiple queries. However, the existing query caching mechanisms often rely on fixed rules such as query types or data volumes, and cannot intelligently judge the similarity between queries, easily leading 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, aiming to optimize the execution efficiency of database queries, reduce unnecessary compilation overhead, and ensure the continuous improvement of query performance through intelligent query cache reuse and dynamic cache update mechanisms. Summary of the Invention

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

[0006] The purpose of the present invention is achieved through the following technical solutions: A database query execution method based on dynamic query compilation cache optimization, including the following steps:

[0007] (1) Receive the query statement input by the user and perform input legality verification and syntax analysis, and then construct a corresponding abstract syntax tree from the query statement input by the user that passes the input legality verification and syntax analysis;

[0008] (2) Generate corresponding identifiers according to the abstract syntax tree, use the identifiers to find matching machine code and determine whether it can be reused, and then load and execute the machine code that can be reused and matches the query statement input by the user to obtain the execution result;

[0009] (3) Generate a corresponding effective 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;

[0010] (4) Generate the corresponding machine code for the specific execution operators in the valid executable plan tree through dynamic compilation and optimize it to obtain the optimized machine code, which is then cached in memory. Subsequently, establish the mapping relationship between the query statement input by the user and the optimized machine code cached in memory. Finally, load and execute the optimized machine code to obtain the execution result.

[0011] (5) Subsequently, send the execution result to the user and periodically clean up the machine code in the cache.

[0012] Further, the step (1) specifically includes the following sub-steps:

[0013] (1.1) The database system receives the query statement input by the user sent through the client and performs input legality verification: If the query statement input by the user contains illegal characters or syntax structures, it indicates that the query statement input by the user fails the input legality verification. The database system then refuses to execute the query statement and returns the first error message to the client. 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 syntax structure; otherwise, it indicates that the query statement input by the user passes the input legality verification.

[0014] (1.2) The database system then splits the query statement input by the user that has passed the input legality verification into a sequence of lexical units composed 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.

[0015] (1.3) The database system then performs semantic analysis on the query statement input by the user: Check whether there are table names or column names in the sequence of lexical units corresponding to the query statement and whether the data types match the constant values. If there are table names or column names and the data types match the constant values, it indicates that the query statement input by the user passes the semantic analysis; otherwise, it indicates that the query statement input by the user fails the semantic analysis. The database system then refuses to execute the query statement and returns the second error message to the client. The second error message includes the missing table name or column name in the query statement input by the user and the mismatched data types.

[0016] (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 legality verification and syntax analysis using the 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 the SELECT clause, FROM clause, or WHERE clause.

[0017] Further, the step (2) specifically includes the following sub-steps:

[0018] (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 in the cache for the machine code that is the same as the identifier as the machine code matching the query statement input by the user according to the generated identifier;

[0019] (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 step (3) is executed for this query statement; if it is in a valid state, continue to perform a 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%, the similarity between the data distribution of the query statement input by the user and the data distribution of the matching machine code does not reach 90%, the resource requirements of the matching machine code do not match the available resources of the database system, or the resource requirements of the matching machine code match the available resources of the database system and the deviation of the resource requirements of the matching 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;

[0020] (2.3) Load and execute the machine code matching the query statement input by the user that can be reused. During the execution process, monitor the machine code matching 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 occupancy rate of the resource requirements relative to the available resources of the database system exceeds 80%, mark the matching machine code as an invalid state; otherwise, continue to execute until the matching machine code is executed to completion and the execution result corresponding to the query statement input by the user is obtained.

[0021] Further, the step (3) specifically includes the following sub-steps:

[0022] (3.1) The database system, through the equivalence transformation rules of relational algebra, performs selection push-down, projection merging, and predicate reordering operations to move the selection nodes representing filter conditions in the abstract syntax tree corresponding to the query statement of the user input where no matching machine code is found, the matching machine code cannot be reused, or the matching machine code is in an invalid state to a position closer to the leaf nodes of the abstract syntax tree, merge the redundant projection nodes in the abstract syntax tree, and adjust the order of the predicate nodes in the abstract syntax tree so that the more selective predicate nodes are executed first, thereby generating a normalized logical query plan corresponding to the query statement of the user input from the abstract syntax tree;

[0023] (3.2) Subsequently, 12 combinations of physical implementation algorithms are respectively matched for each relational operator in the normalized logical query plan; each combination of physical implementation algorithms includes 1 table scan strategy and 1 Join algorithm. The table scan strategy is a table sequential scan, table index scan, or table bitmap scan strategy, and the Join algorithm is a nested loop join, hash join, sort-merge join, or index join algorithm; subsequently, 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 costs of various combinations of physical implementation algorithms corresponding to each relational operator, and select the combination of physical implementation algorithms with the lowest cost as the optimal physical execution plan for the corresponding relational operator; subsequently, the optimal physical execution plans corresponding to each relational operator are combined to obtain the optimal physical execution total plan structure;

[0024] (3.3) According to the optimal physical execution total plan, a corresponding specific execution operator is constructed for each relational operator. Each specific execution operator is bound to the optimal physical execution plan and parameter configuration of the corresponding relational operator. Among them, the parameter configuration of the relational operator includes an index selection identifier determined based on the predicate conditions and table structure characteristics in the query statement, and a parallelism parameter allocated based on the complexity of the query statement and the resource status of the database system. The value of the parallelism parameter is , ; subsequently, each specific execution operator forms a complete executable plan tree;

[0025] (3.4) Subsequently, verify whether the optimal physical execution plans tied to any two specific execution operators in the executable plan tree are all compatible, check whether the data flow formats between all specific execution operators are consistent, evaluate whether the resource requirements of the optimal overall 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 tied to any two specific execution operators in the executable plan tree are all compatible, the data flow formats between all specific execution operators are consistent, the resource requirements of the optimal overall 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, it indicates that the executable plan tree is valid; otherwise, it indicates that the executable plan tree is invalid, and a third error message is obtained. The third error message includes a specific execution operator compatibility conflict identifier, a database system resource overrun warning code, a description of the data flow mismatch of the specific execution operator, and a parallelism unreasonable prompt;

[0026] (3.5) Subsequently, repeat steps (3.2) - (3.4). During the repetition process, the database system imposes constraint conditions on the 12 physical implementation algorithm combinations assigned to each relational operator according to the third error message, adjusts the fitness function weights of the multi-objective optimization genetic algorithm, optimizes the parameter configurations of the relational operators corresponding to each specific execution operator bound, dynamically adjusts the index selection identifier and parallelism parameters, and finely tunes the structure of the executable plan tree to ensure that the data flow formats between adjacent specific execution operators are consistent and the resource requirements of the optimal overall physical execution plan are balanced with the available resources of the database system until the executable plan tree is valid.

[0027] Further, the step (4) specifically includes the following sub-steps:

[0028] (4.1) The database system traverses each specific execution operator in the valid executable plan tree, extracts the physical implementation algorithm combinations bound to each specific execution operator, calls the code template library of the compiler to convert the table scan strategy and Join algorithm corresponding to each extracted physical implementation algorithm combination into standard IR templates respectively, then injects the index selection identifier and parallelism parameters in the parameter configuration bound to each specific execution operator into the corresponding standard IR templates to generate parameterized IR code, and finally combines all the parameterized IR codes into the complete parameterized IR code corresponding to the query statement input by the user according to the data flow dependency relationship between specific execution operators;

[0029] (4.2) The database system then inputs the complete parameterized IR code into the front end of the LLVM compiler to generate LLVM bitcode; subsequently, the LLVM bitcode is subjected to platform-independent optimization through 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 for the target hardware platform by the back end of the LLVM compiler according to the characteristics of the target hardware platform, and the target hardware platform includes x86, ARM or GPU; preferably, the assembly code for 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;

[0030] (4.3) The different instructions in the machine code corresponding to the query statement input by the user are divided into different columns according to the rules of juxtaposing related instructions and separating unrelated instructions; subsequently, the instructions in each column are sorted from largest to smallest according to their usage rates, and the instructions with higher usage rates are placed closer to the cache; then the variables in the instructions with a usage rate greater than 50% are allocated to registers; finally, the code in the loop body in the machine code is copied N times to obtain the optimized machine code corresponding to the query statement input by the user;

[0031] (4.4) The optimized machine code corresponding to the query statement input by the user is cached in the memory using the LRU policy, and at the same time, the metadata information of the query statement input by the user is recorded, and the metadata information includes the hash value of the query statement, the compilation timestamp, the execution frequency, and the resource occupancy;

[0032] (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;

[0033] (4.6) Subsequently, 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.

[0034] Further, the step (5) specifically includes the following sub-steps:

[0035] (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 at the same time records the performance metrics during the execution of the query statement input by the user, and the performance metrics include execution time, CPU occupancy rate, memory consumption, and the number of I / O operations;

[0036] (5.2) Evaluate the effectiveness of the optimized machine code corresponding to the query statement input by the user in the cache based on the recorded performance metrics: If the execution time does not exceed 110% of the historical average, the CPU occupancy rate does not exceed 80% of the number of system cores, 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 baseline value before optimization, then mark the optimized machine code corresponding to the query statement input by the user in the cache as the valid state; otherwise, mark the optimized machine code corresponding to the query statement input by the user in the cache as the invalid state;

[0037] (5.3) Regularly clean the machine code marked as invalid in the cache.

[0038] 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. Executable code is stored in the memory. 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.

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

[0040] The beneficial effects of the present invention are as follows: By using dynamic compilation and machine code caching technologies, the present invention realizes the efficient optimization of database query execution, significantly improving the performance of the database management system (DBMS) under frequent queries. Compared with the prior art, the present invention reduces the repeated compilation and optimization overhead, reduces the consumption of database resources, and improves the query response speed. The system uses machine learning technology to intelligently judge query similarity, ensuring the accuracy of cache reuse, thereby improving the query execution efficiency. Brief Description of the Drawings

[0041] Figure 1 It is a flowchart of a database query execution method based on dynamic query compilation cache optimization;

[0042] Figure 2 It is a structural diagram of a database query execution device based on dynamic query compilation cache optimization. Detailed Embodiments

[0043] In order to make the objectives, technical solutions and advantages of the present invention more clearly understood, the present invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention, rather than all embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.

[0044] Embodiment 1: As Figure 1 shown, the present invention provides a database query execution method based on dynamic query compilation and cache optimization, including the following steps:

[0045] (1) Parsing stage: Receive the query statement input by the user and perform input legality verification and syntax analysis. Subsequently, construct a corresponding abstract syntax tree from the query statement input by the user that has passed the input legality verification and syntax analysis.

[0046] The step (1) specifically includes the following sub-steps:

[0047] (1.1) The database system receives the query statement input by the user sent through the client and performs input legality verification: If the query statement input by the user contains illegal characters or syntax structures, it means that the query statement input by the user fails the input legality verification. Subsequently, the database system rejects the execution of the query statement and returns the first error message to the client. The first error message includes the specific position of the illegal character in the query statement input by the user or the type of the illegal syntax structure; otherwise, it means that the query statement input by the user passes the input legality verification.

[0048] (1.2) The database system then splits the query statement input by the user that has passed the input legality verification into a sequence of lexical units composed 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 accuracy of query statement parsing.

[0049] (1.3) The database system then performs semantic analysis on the query statement input by the user: Check whether there are table names or column names in the sequence of lexical units corresponding to the query statement and whether the data types match the constant values. If there are table names or column names and the data types match the constant values, 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. Subsequently, the database system rejects the execution of the query statement and returns the second error message to the client. The second error message includes the missing table name or column name in the query statement input by the user and the mismatched data type.

[0050] (1.4) The database system then uses a top-down recursive descent analysis method to construct a corresponding abstract syntax tree for the query statement of the user input that has passed the input legality check and syntax analysis; 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.

[0051] (2) Generate corresponding identifiers according to the abstract syntax tree, use the identifiers to find matching machine code and determine whether it can be reused, and then load and execute the reusable machine code that matches the query statement of the user input to obtain the execution result.

[0052] The step (2) specifically includes the following sub-steps:

[0053] (2.1) Generate identifiers corresponding to the query statement of the user input according to the structure and parameter configuration of the abstract syntax tree corresponding to the query statement of the user input; find the machine code that is consistent with the identifier in the cache as the machine code that matches the query statement of the user input.

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

[0055] (2.3) Load and execute the machine code that can be reused and 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 occupancy rate of the resource demand relative to the available resources of the database system exceeds 80%, mark the matching machine code as the invalid state; otherwise, continue to execute until the matching machine code is executed completely and the execution result corresponding to the query statement input by the user is obtained.

[0056] (3) Generate a corresponding effective 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 the invalid state.

[0057] The step (3) specifically includes the following sub-steps:

[0058] (3.1) The database system moves the selection nodes representing the filtering conditions in the abstract syntax tree corresponding to 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 the invalid state to a position closer to the leaf nodes of the abstract syntax tree through the relational algebra equivalence transformation rules, implementing selection pushdown, projection merging, and predicate reordering operations, so as to reduce the amount of data that the upper-level 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 only retain the attribute columns finally required for the query; adjust the order of the predicate nodes in the abstract syntax tree so that the more selective predicate nodes are executed first, thereby generating a normalized logical query plan corresponding to the query statement input by the user. The normalized logical query plan ensures the optimality of the query logic and provides a basis for generating the optimal physical execution plan subsequently.

[0059] (3.2) Subsequently, match 12 physical implementation algorithm combinations 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 a table sequential scan, table index scan, or table bitmap scan strategy, and the Join algorithm is a nested loop join, hash join, sort-merge join, or index join algorithm; subsequently, use the multi-objective optimization genetic algorithm to comprehensively consider the I / O overhead, CPU utilization rate, and memory consumption factors of the database system to evaluate the cost of various physical implementation algorithm combinations corresponding to each relational operator, and select the physical implementation algorithm combination with the lowest cost as the optimal physical execution plan for the corresponding relational operator; subsequently, combine the optimal physical execution plans corresponding to each relational operator to obtain the optimal physical execution total plan structure.

[0060] (3.3) Construct an executable plan tree based on the optimal overall physical execution plan: For each relational operator, construct a corresponding specific execution operator according to the optimal overall physical execution plan. Each specific execution operator is bound to the optimal physical execution plan and parameter configuration of the corresponding relational operator. Among them, the parameter configuration of the relational operator includes the index selection identifier determined according to the predicate condition in the query statement and the table structure characteristics, and the parallelism parameter allocated based on the complexity of the query statement and the resource status of the database system. The value of the parallelism parameter is , ; Subsequently, form a complete executable plan tree with each specific execution operator;

[0061] (3.4) Subsequently, verify whether the optimal physical execution plans bound to any two specific execution operators in the executable plan tree are all compatible, check whether the data flow formats between all specific execution operators are consistent, evaluate whether the resource requirements of the optimal overall 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 all compatible, the data flow formats between all specific execution operators are consistent, the resource requirements of the optimal overall 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 all reasonable, it indicates that the executable plan tree is valid; otherwise, it indicates that the executable plan tree is invalid, and a third error message is obtained. The third error message includes the specific execution operator compatibility conflict identifier, the database system resource overrun warning code, the description of the data flow mismatch of the specific execution operator, and the parallelism unreasonable prompt;

[0062] (3.5) Subsequently, repeat steps (3.2) - (3.4). During the repetition process, the database system imposes constraint conditions on the selection of 12 physical implementation algorithm combinations matched to each relational operator according to the third error message, adjusts the fitness function weights of the multi-objective optimization genetic algorithm, and optimizes the parameter configuration of the relational operator bound to each specific execution operator, dynamically adjusts the index selection identifier and the parallelism parameter, and finely tunes the structure of the executable plan tree to ensure that the data flow formats between adjacent specific execution operators are consistent and the resource requirements of the optimal overall physical execution plan are balanced with the available resources of the database system until the executable plan tree is valid.

[0063] (4) Generate corresponding machine code through dynamic compilation for the specific execution operators in the valid executable plan tree and optimize it to obtain the optimized machine code and cache it in the memory. Subsequently, 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.

[0064] Step (4) specifically includes the following sub-steps:

[0065] (4.1) The database system traverses each specific execution operator in the valid executable plan tree, extracts the physical implementation algorithm combinations bound to each specific execution operator, calls the code template library of the compiler to convert the table scan strategy and Join algorithm corresponding to each extracted physical implementation algorithm combination into standard IR templates respectively, 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, and finally combines all the parameterized IR codes into the complete parameterized IR code corresponding to the query statement input by the user according to the data flow dependency relationship between specific execution operators; the complete parameterized IR code will be used as the input for subsequent machine code compilation and optimization.

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

[0067] (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 rules of juxtaposing related instructions and separating unrelated instructions; subsequently, they are sorted in descending order of the usage rate of the instructions in each column, and the instructions with higher usage rates are placed closer to the cache to reduce instruction loading latency; subsequently, variables in the instructions with a 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 in the machine code is copied N times to reduce the overhead of loop control instructions, reduce the probability of branch prediction failure, and thus improve the loop execution efficiency, 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 latency and serves as the basis for subsequent machine code execution.

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

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

[0070] (4.6)Subsequently, load and execute the optimized machine code corresponding to the query statement input by the user until the execution is completed and the execution result corresponding to the query statement input by the user is obtained.

[0071] (5)Subsequently, send the execution result to the user and periodically clean up the machine code in the cache to ensure more efficient use in subsequent queries.

[0072] The step (5) specifically includes the following sub-steps:

[0073] (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 metrics during the execution of the query statement input by the user. The performance metrics include execution time, CPU occupancy rate, memory consumption, and the number of I / O operations. The performance metrics serve as the basis for evaluating the effectiveness of the machine code in the subsequent cache, ensuring a comprehensive analysis of the performance of the machine code in the cache. The performance metric record also includes the context information of the query execution environment, such as the number of concurrent queries and the system load situation.

[0074] (5.2)Based on the recorded performance metrics, evaluate the effectiveness of the optimized machine code corresponding to the query statement input by the user in the cache: If the execution time does not exceed 110% of the historical average, the CPU occupancy rate does not exceed 80% of the number of system cores, the memory consumption does not exceed 1.3 times the estimated query requirement, and the number of I / O operations does not exceed 1.1 times the baseline value before optimization, then mark the optimized machine code corresponding to the query statement input by the user in the cache as in the valid state; otherwise, mark the optimized machine code corresponding to the query statement input by the user in the cache as in the invalid state, and this machine code will be removed in the next cache cleaning cycle.

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

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

[0077] See Figure 2 , an apparatus for optimizing database query execution based on dynamic query compilation cache provided by an embodiment of the present invention includes one or more processors for implementing the method for optimizing database query execution based on dynamic query compilation cache in the foregoing embodiment.

[0078] The embodiment of the apparatus for optimizing database query execution based on dynamic query compilation cache of the present invention can be applied to any device with data processing capabilities, and the any device with data processing capabilities can be a device or apparatus such as a computer. The device embodiment can be implemented by software, or by hardware or a combination of software and hardware. Taking software implementation as an example, as a logically meaningful device, it is formed by reading the corresponding computer program instructions in the non-volatile memory into the memory and running them through the processor of any device with data processing capabilities where it is located. From a hardware perspective, as Figure 2 shown, it is a hardware structure diagram of any device with data processing capabilities where the apparatus for optimizing database query execution based on dynamic query compilation cache of the present invention is located. In addition to Figure 2 the processors, memory, network interfaces, and non-volatile memories shown, the any device with data processing capabilities where the device in the embodiment is located usually also includes other hardware according to the actual functions of the any device with data processing capabilities, which will not be elaborated here.

[0079] The specific implementation processes of the functions and roles of each unit in the above apparatus are specifically described in the implementation processes of the corresponding steps in the above method, which will not be elaborated here.

[0080] For the device embodiment, since it basically corresponds to the method embodiment, the relevant parts can be referred to the partial description of the method embodiment. The device embodiments described above are only illustrative. The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place, or may be distributed to multiple network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the solution of the present invention. Those of ordinary skill in the art can understand and implement it without creative efforts.

[0081] An 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 may be an internal storage unit of any device with data processing capabilities described in any of the foregoing embodiments, such as a hard disk or a memory. The computer-readable storage medium may also be an external storage device of any device with data processing capabilities, such as a plug-in hard disk, a Smart Media Card (SMC), an SD card, a Flash Card, etc. equipped on the device. Further, the computer-readable storage medium may also include both an internal storage unit and an external storage device of any device with data processing capabilities. The computer-readable storage medium is used to store the computer program and other programs and data required by any device with data processing capabilities, and may also be used to temporarily store data that has been output or will be output.

[0082] The foregoing are only the preferred embodiments of the present invention, and are not intended to limit the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present invention shall be included within 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 steps include the following: (1) Receive the query statement input by the user, perform input legality verification and syntax analysis, and then construct a corresponding abstract syntax tree from the query statement input by the user that has passed the input legality verification and syntax analysis; (2) Generate corresponding identifiers according to the abstract syntax tree, use the identifiers to find matching machine code and determine whether it can be reused, and then load and execute the reusable machine code that matches the query statement input by the user to obtain the execution result; The specific content of step (2) is as follows: Generate corresponding identifiers according to the abstract syntax tree; Subsequently, determine whether the machine code that matches the query statement input by the user found in the cache can be reused: First, check whether the machine code that matches 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 that matches the query statement input by the user cannot be reused, and then step (3) is executed for this query statement; If it is in a valid state, continue to perform a compatibility analysis on the machine code that matches 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%, the similarity between the data distribution of the query statement input by the user and the data distribution of the matching machine code does not reach 90%, the resource requirements of the matching machine code do not match the available resources of the database system, or the resource requirements of the matching machine code match the available resources of the database system and the deviation of the resource requirements of the matching machine code exceeds 20%, it means that the machine code that matches the query statement input by the user fails the compatibility analysis, that is, it means that the machine code that matches the query statement input by the user cannot be reused; Otherwise, it means that the machine code that matches the query statement input by the user passes the compatibility analysis, that is, it means that the machine code that matches the query statement input by the user can be reused; Load and execute the 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 occupancy rate of the resource requirements relative to the available resources of the database system exceeds 80%, mark the matching machine code as an invalid state; 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; (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) Dynamically compile the specific execution operators in the valid executable plan tree to generate corresponding machine code and optimize it to obtain the optimized machine code, which is then cached in the memory. Subsequently, 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) Subsequently, send the execution result to the user and periodically clean up the machine code in the cache.

2. The database query execution method based on dynamic query compilation cache optimization according to claim 1, wherein The specific content of step (1) includes the following sub-steps: (1.1) The database system receives the query statement input by the user sent through the client and performs input legality verification: If the query statement input by the user contains illegal characters or syntax structures, it indicates that the query statement input by the user fails the input legality verification. Subsequently, the database system rejects the execution of the query statement and returns the first error message to the client. The first error message includes the specific position of the illegal character in the query statement input by the user or the type of the illegal syntax structure; otherwise, it indicates that the query statement input by the user passes the input legality verification. (1.2) The database system then splits the query statement input by the user that has passed the input legality verification into a sequence of lexical units composed 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. (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 sequence of lexical units corresponding to the query statement and whether the data type matches the constant value. If there is a table name or column name and the data type matches the constant value, it indicates that the query statement input by the user passes the semantic analysis; otherwise, it indicates that the query statement input by the user fails the semantic analysis. Subsequently, the database system rejects the execution of the query statement and returns the second error message to the client. The second error message includes the missing table name or column name in the query statement input by the user and the mismatched data type. (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 legality verification 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. The various components of the query statement include the SELECT clause, FROM clause, or WHERE clause.

3. The database query execution method based on dynamic query compilation cache optimization according to claim 2, wherein The step (2) further includes the following sub-steps: 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 in the cache for the machine code that is the same as the identifier as the machine code matching the query statement input by the user.

4. A database query execution method based on dynamic query compilation cache optimization according to claim 3, characterized in that, The step (3) specifically includes the following sub-steps: (3.1) The database system, through the relational algebra equivalence transformation rules, performs operations such as selection pushdown, projection merging, and predicate reordering to move the selection node representing the filtering condition in the abstract syntax tree corresponding to 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 to a position closer to the leaf nodes of the abstract syntax tree, merge the redundant projection nodes in the abstract syntax tree, and adjust the order of the predicate nodes in the abstract syntax tree so that the predicate nodes with higher selectivity are executed first, thereby generating a normalized logical query plan corresponding to the query statement input by the user from the abstract syntax tree. (3.2) Subsequently, match 12 combinations of physical implementation algorithms for each relational operator in the normalized logical query plan; each combination of physical implementation algorithms includes 1 table scan strategy and 1 Join algorithm. The table scan strategy is a table sequential scan, table index scan, or table bitmap scan strategy, and the Join algorithm is a nested loop join, hash join, sort-merge join, or index join algorithm. Subsequently, use a multi-objective optimization genetic algorithm to comprehensively consider the I / O overhead, CPU utilization, and memory consumption factors of the database system to evaluate the cost of various combinations of physical implementation algorithms corresponding to each relational operator, and select the combination of physical implementation algorithms with the lowest cost as the optimal physical execution plan for the corresponding relational operator. Subsequently, combine the optimal physical execution plans corresponding to each relational operator to obtain the overall optimal physical execution plan; (3.3)Construct corresponding specific execution operators for each relational operator according to the optimal overall physical execution plan. Each specific execution operator is bound to the optimal physical execution plan and parameter configuration of the corresponding relational operator. Among them, the parameter configuration of the relational operator includes the index selection identifier determined based on the predicate conditions in the query statement and the table structure characteristics, and the parallelism parameter allocated based on the complexity of the query statement and the resource status of the database system. The value of the parallelism parameter is , ; Subsequently, form a complete executable plan tree for each specific execution operator; (3.4) Subsequently, verify whether the optimal physical execution plans bound to any two specific execution operators in the executable plan tree are all compatible, check whether the data flow formats between all specific execution operators are consistent, evaluate whether the resource requirements of the overall 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 all compatible, the data flow formats between all specific execution operators are consistent, the resource requirements of the overall 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, it indicates that the executable plan tree is valid; otherwise, it indicates that the executable plan tree is invalid, and a third error message is obtained. The third error message includes a specific execution operator compatibility conflict identifier, a database system resource overrun warning code, a description of the data flow mismatch between specific execution operators, and a parallelism unreasonable prompt; (3.5) Subsequently, repeat steps (3.2) - (3.4). During the repetition process, the database system imposes constraint conditions on the selection of 12 combinations of physical implementation algorithms matched to each relational operator, adjusts the fitness function weights of the multi-objective optimization genetic algorithm, optimizes the parameter configuration of the relational operator corresponding to each specific execution operator, dynamically adjusts the index selection identifier and parallelism parameters, and makes fine-tuning of the structure of the executable plan tree to ensure that the data flow formats between adjacent specific execution operators are consistent and the resource requirements of the overall optimal physical execution plan are balanced with the available resources of the database system until the executable plan tree is valid.

5. The database query execution method based on dynamic query compilation cache optimization according to claim 4, wherein The specific steps of step (4) include 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 combinations bound to each specific execution operator, calls the code template library of the compiler to convert the table scan strategy and Join algorithm corresponding to each extracted physical implementation algorithm combination into standard IR templates respectively, 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, and finally combines all the parameterized IR code into the complete parameterized IR code corresponding to the query statement input by the user according to the data flow dependency relationship between 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; subsequently, 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 for the target hardware platform by the back end of the LLVM compiler according to the characteristics of the target hardware platform, and the target hardware platform includes x86, ARM or GPU; the assembly code for 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) The different instructions in the machine code corresponding to the query statement input by the user are divided into different columns according to the rules of juxtaposing related instructions and separating unrelated instructions; subsequently, they are sorted in descending order of the usage rate of the instructions in each column, and the instructions with higher usage rates are placed closer to the cache; then the variables in the instructions with a 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) The optimized machine code corresponding to the query statement input by the user is cached in the memory using the LRU strategy, and at the same time, the metadata information of the query statement input by the user is recorded, and the metadata information includes the hash value, compilation timestamp, execution frequency, and resource occupancy of the query statement; (4.5) A mapping relationship is established between the query statement input by the user and the optimized machine code cached in the memory, and a unique identifier is generated through the syntax tree structure and parameter configuration of the query statement input by the user; (4.6) Subsequently, 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 at the same time records the performance metrics during the execution of the query statement input by the user, and the performance metrics include execution time, CPU occupancy rate, memory consumption, and number of I / O operations; (5.2) Based on the recorded performance metrics, evaluate the effectiveness of the optimized machine code corresponding to the query statement input by the user in the cache: If the execution time does not exceed 110% of the historical average, the CPU occupancy rate does not exceed 80% of the number of system cores, the memory consumption does not exceed 1.3 times the estimated query requirement, and the number of I / O operations does not exceed 1.1 times the baseline value before optimization, then mark the optimized machine code corresponding to the query statement input by the user in the cache as the valid state; otherwise, mark the optimized machine code corresponding to the query statement input by the user in the cache as the invalid state; (5.3) Regularly clean up the machine code marked as invalid in the cache.

7. A database query execution device based on dynamic query compilation cache optimization, characterized in that It includes a memory and one or more processors. Executable code is stored in the memory. 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 according to any one of claims 1-6.

8. A computer-readable storage medium, characterized in that, A program is stored thereon. When the program is executed by a processor, it implements a database query execution method based on dynamic query compilation cache optimization according to any one of claims 1-6.

Images