Session variable generation method and device, computer equipment and storage medium
By generating target variable storage space and automatically generating session variables in PostgreSQL database, the problem of not being able to store temporary data in the database is solved, and system performance and operation efficiency are improved.
Patent Information
- Application Number
- CN202411393467.7
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-09-30
- Publication Date
- 2025-02-07
AI Technical Summary
The PostgreSQL database cannot apply session variables to store temporary data, resulting in frequent creation of temporary tables and increasing the system operation load, which poses great hidden dangers.
When the session information is established, the target variable storage space is generated, and the query tree is generated through feature extraction and optimization algorithms, and the session variables are automatically generated and stored in the target variable storage space, realizing efficient management of session variables.
It reduces latency during the session, improves query efficiency, reduces system load, enhances system flexibility and scalability, and simplifies the operation process of SQL database.
Smart Images
Figure CN119396864A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of machine learning technology, which is applied in the financial field, and in particular to a method, apparatus, computer equipment and storage medium for generating session variables. Background Art
[0002] Session variables are variables that can be seen everywhere in a database session. They can participate in reading, writing, comparison and calculation in most commands in the corresponding session. PostgreSQL database is widely used in financial systems due to its data consistency and integrity, high scalability, rich data types and functions, high security and stability, open source code and flexibility.
[0003] However, due to the limitation that only temporary tables and packages can be used to store temporary data in PostgresSQL, session variables cannot be applied to them. As a result, the creation of temporary tables needs to be recorded in the system dictionary, and the deletion of zeros needs to be removed from the system dictionary, so a temporary table must be created for almost every session type. In the scenario of a financial system with many sessions, the frequent creation of temporary tables doubles the system operation load, which poses a great risk to system operation.
[0004] Therefore, there is an urgent need for a session variable generation method for the PostgresSQL database to alleviate the operating load of the financial system. Summary of the invention
[0005] The present application provides a method, device, computer equipment and storage medium for generating session variables, aiming to solve the problem that only temporary tables and packages can store temporary data in PostgresSQL, and session variables cannot be applied thereto. As a result, the creation of temporary tables needs to be recorded in the system dictionary, and when zeros are deleted, they need to be removed from the system dictionary, so that a temporary table must be created for almost every session type. In the scenario of a financial system with many sessions, the frequent creation of temporary tables causes the system operation load to double, which poses a great risk to the system operation.
[0006] In a first aspect, the present application provides a method for generating a session variable, comprising:
[0007] When it is detected that the session information is established, a target variable storage space is generated in the memory pool corresponding to the SQL database;
[0008] If a query statement corresponding to the session information is received, feature extraction is performed on the query statement to obtain grammatical feature information corresponding to the query statement;
[0009] Obtaining a preset session variable structure, and generating a query tree corresponding to the query statement according to the session variable structure, grammatical feature information and the query statement; wherein the structure of the query tree is the session variable structure;
[0010] The execution information corresponding to the query tree is calculated according to a preset optimization algorithm, a session variable is generated according to the execution information and the session variable is stored in the target variable storage space to complete the generation of the session variable; wherein, by running the session variable in the target variable storage space, the preset operation corresponding to the session variable can be implemented in the SQL database.
[0011] In some embodiments, the feature extraction of the query statement to obtain grammatical feature information corresponding to the query statement includes: obtaining a pre-trained query analysis model, the query analysis model including a lexical analysis model, a grammatical analysis model and a semantic analysis model; inputting the query statement into the lexical analysis model, the grammatical analysis model and the semantic analysis model respectively, the lexical analysis model, the grammatical analysis model and the semantic analysis model respectively parse the query statement, and respectively output the lexical features, grammatical features and syntactic features corresponding to the query statement; and generating the grammatical feature information according to the lexical features, grammatical features and syntactic features.
[0012] In some embodiments, before generating a query tree corresponding to the query statement according to the session variable structure, grammatical feature information and the query statement, it also includes: obtaining a command type corresponding to the query statement according to the grammatical feature information; wherein the command type includes a first command type and a second command type, and the complexity of the first command type is lower than that of the second command type; if the command type corresponding to the query statement is the first command type, determining that the query statement is a simple statement, and directly generating the session variable corresponding to the simple statement; if the command type corresponding to the query statement is the second command type, determining that the query statement is a complex statement, and generating a query tree corresponding to the complex statement according to the session variable structure, grammatical feature information and the query statement.
[0013] In some embodiments, a query tree corresponding to the query statement is generated according to the session variable structure, grammatical feature information and the query statement, including: obtaining template information corresponding to the query tree according to the variable structure, and generating an initial query tree according to the template information; dividing the query statement according to the grammatical feature information to obtain multiple sub-query statements; generating a child node corresponding to the initial query tree according to each of the sub-query statements; obtaining a target connection method according to the variable structure, completing the connection of each of the child nodes in the query tree according to the target connection method, and completing the generation of the query tree.
[0014] In some embodiments, before calculating the execution information corresponding to the query tree according to a preset optimization algorithm, it also includes: parsing the query tree to obtain query tree rules and query tree views corresponding to the query tree; updating the query tree rules and query tree views according to preset session variable rules and session variable views respectively; regenerating the query tree according to the updated query tree rules and query tree views to calculate the updated execution information corresponding to the query tree according to the preset optimization algorithm.
[0015] In some embodiments, the calculation of execution information corresponding to the query tree according to a preset optimization algorithm includes: generating a preliminary execution plan according to the query tree; the preliminary plan includes multiple sub-plans; obtaining the best access method corresponding to the query tree according to the variable structure; the best access method includes any one of a full table scan and an index scan; calculating the cost information of the sub-plans using multiple preset connection methods respectively according to the best access method and the optimization algorithm; wherein the preset connection methods include at least nested loop connections, sort merge connections and hash connections; determining the optimal connection method among multiple preset connection methods according to the cost information; and generating the execution information according to the optimal connection method, the best access method and the preliminary plan.
[0016] Exemplarily, the cost information of the sub-plans adopting multiple preset connection methods respectively is calculated according to the optimal access method and the optimization algorithm, including: randomly adjusting the order of the multiple sub-plans to generate multiple candidate sequence queues; calculating the cost information of each candidate sequence queue adopting multiple preset connection methods respectively according to the optimal access method and the optimization algorithm; determining the optimal connection method and the optimal sequence queue from the multiple preset connection methods according to the cost information, and generating the execution information according to the optimal connection method, the optimal access method, the optimal sequence queue and the preliminary plan.
[0017] In a second aspect, the present application provides a session variable generation device, comprising:
[0018] A space establishment unit, used for generating a target variable storage space in a memory pool corresponding to the SQL database when detecting that the session information is established;
[0019] A statement receiving unit, configured to extract features of a query statement corresponding to the session information upon receiving the query statement, and obtain grammatical feature information corresponding to the query statement;
[0020] A structure acquisition unit, used to acquire a preset session variable structure, and generate a query tree corresponding to the query statement according to the session variable structure, grammatical feature information and the query statement; wherein the structure of the query tree is the session variable structure;
[0021] A variable generation unit is used to calculate the execution information corresponding to the query tree according to a preset optimization algorithm, generate session variables according to the execution information and store the session variables in the target variable storage space to complete the generation of the session variables; wherein, by running the session variables in the target variable storage space, the preset operations corresponding to the session variables can be implemented in the SQL database.
[0022] In a third aspect, the present application further provides a computer device, including:
[0023] Memory and processor;
[0024] The memory is used to store computer programs;
[0025] The processor is used to execute the computer program and implement the steps of the session variable generation method described in the first aspect when executing the computer program.
[0026] In a fourth aspect, the present application further provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the processor implements the steps of the session variable generation method described in the first aspect above.
[0027] The present application discloses a method, device, computer equipment and storage medium for generating session variables. By immediately generating variable storage space when a session is established, possible delays during the session are reduced. At the same time, feature extraction and optimization are performed for each query statement to ensure the generation of an optimal execution plan and improve query efficiency. By using a preset session variable structure and query tree structure, session variable management is made more standardized and efficient. In summary, by presetting optimization algorithms and structure templates, different types of queries and database operations can be adapted to improve the flexibility and scalability of the system. At the same time, by optimizing the execution path of query statements, resource consumption is reduced and system performance is improved. This enables automatic generation and management of session variables, simplifies the operation process of the SQL database, and reduces the complexity of human intervention.
[0028] Finally, the provided method effectively connects session information with SQL database operations, achieves query optimization and efficient execution through a series of steps, and improves the overall performance and operational efficiency of database applications in financial systems.
[0029] It should be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present application. BRIEF DESCRIPTION OF THE DRAWINGS
[0030] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.
[0031] Figure 1 This is a schematic flow chart of the steps of a method for generating session variables provided in one embodiment of the present application;
[0032] Figure 2 is a schematic flow chart of the steps of a method for generating grammatical feature information provided by an embodiment of the present application;
[0033] Figure 3 is a schematic flow chart of the steps of a query tree generation method provided by an embodiment of the present application;
[0034] Figure 4 It is a structural diagram of a session variable generation device provided in an embodiment of the present application;
[0035] Figure 5 It is a schematic block diagram of the structure of a computer device provided in one embodiment of the present application.
[0036] It should be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present application. DETAILED DESCRIPTION
[0037] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.
[0038] The flowcharts shown in the accompanying drawings are only examples and do not necessarily include all the contents and operations / steps, nor must they be executed in the order described. For example, some operations / steps may also be decomposed, combined or partially merged, so the actual execution order may change according to actual conditions.
[0039] It should be understood that, in order to facilitate the clear description of the technical solutions of the embodiments of the present invention, in the embodiments of the present invention, the words "first", "second", etc. are used to distinguish the same items or similar items with substantially the same functions and effects. Those skilled in the art can understand that the words "first", "second", etc. do not limit the quantity and execution order, and the words "first", "second", etc. do not necessarily limit the difference.
[0040] It should be understood that the terms used in this application specification are only for the purpose of describing specific embodiments and are not intended to limit the application. As used in this application specification and the appended claims, unless the context clearly indicates otherwise, the singular forms "a", "an" and "the" are intended to include plural forms.
[0041] It should also be understood that the term “and / or” used in the specification and appended claims refers to any and all possible combinations of one or more of the associated listed items, and includes these combinations.
[0042] In conjunction with the accompanying drawings, some embodiments of the present application are described in detail below. In the absence of conflict, the following embodiments and features in the embodiments can be combined with each other.
[0043] Session variables are variables that can be seen everywhere in a database session. They can participate in reading, writing, comparison and calculation in most commands in the corresponding session. PostgreSQL database is widely used in financial systems due to its data consistency and integrity, high scalability, rich data types and functions, high security and stability, open source code and flexibility.
[0044] However, due to the limitation that only temporary tables and packages can be used to store temporary data in PostgresSQL, session variables cannot be applied to them. As a result, the creation of temporary tables needs to be recorded in the system dictionary, and the deletion of zeros needs to be removed from the system dictionary, so a temporary table must be created for almost every session type. In the scenario of a financial system with many sessions, the frequent creation of temporary tables doubles the system operation load, which poses a great risk to system operation.
[0045] Therefore, there is an urgent need for a session variable generation method for the PostgresSQL database to alleviate the operating load of the financial system.
[0046] To solve the above problems, please refer to Figure 1 , Figure 11 is a schematic flow chart of a session variable generation method provided in an embodiment of the present application. The session variable generation method can be implemented by a computer device, which can be deployed on a single server or a server cluster. It can also be deployed on a handheld terminal, a laptop computer, a wearable device, or a robot.
[0047] It should be noted that the method provided can be applied not only to the financial field to apply session variables in a PostgreSQL database, but also to other systems that use a PostgreSQL database and have a large amount of session information, such as medical and education fields, to reduce the system operation load. Therefore, the embodiments of the present application do not limit the scenarios in which the method is applied.
[0048] To solve the above problems, please refer to Figure 1 Specifically, Figure 1 As shown, the provided session variable generation method includes steps S101 to S104. The details are as follows:
[0049] Step S101: When it is detected that session information is established, a target variable storage space is generated in a memory pool corresponding to the SQL database.
[0050] Specifically, when new session information is detected, the provided method allocates a dedicated storage space for the session in the memory pool of the SQL database to store session variables. This operation usually involves memory management technology to ensure that reserving space in the memory does not lead to resource waste or frequent memory fragmentation. Resources are allocated in advance for subsequent operations to ensure that there is enough memory for storing and operating session variables. At the same time, the memory allocation time during query is reduced, thereby improving the response speed and overall performance of the system. Through centralized memory pool management, memory usage efficiency is improved and memory fragmentation is reduced.
[0051] It should be noted that, since the original solution of implementing session variables in SQL databases through temporary tables, a temporary table will be created for each session that uses session variables, and a temporary table of each type needs to be manually created first. For example: createtemp table session_int_var(varname name,value int); -- a temporary table of integer type; createtemp table session_text_var(varname name,value int); -- a temporary table of text type.
[0052] These temporary tables will be deleted when the session exits. The creation of all tables will generate several records. When the table is deleted, these records are not deleted immediately but marked as invalid. Once a large number of concurrent sessions use temporary tables, most of the pages in the system table will be these invalid records, which will greatly reduce the performance of the database system. At the same time, each table in the system will be assigned a unique O ID identifier. This identifier is an unsigned integer with a maximum value of 2 to the power of 32. After it is used up, it will start looking for unused values from 0. Under normal circumstances, it is difficult to create such a large number of tables, so this part may be undertested. If temporary tables are continuously created, it may trigger system defects in this part, leading to risk accidents.
[0053] The provided method allocates a space in the memory pool to store session variables when the session is established. This space will be used as an array, in which each element records the name, type, and value of a session variable. In this way, no table will be created and the above problems will not exist.
[0054] Step S102: If a query statement corresponding to the session information is received, feature extraction is performed on the query statement to obtain grammatical feature information corresponding to the query statement.
[0055] Specifically, after receiving the query statement, the system uses parsing and feature extraction algorithms to identify the grammatical elements and structure of the query statement. Use a grammar analyzer (such as a parsing tool based on Bison or ANTLR) to extract key features in the query statement, such as table names, field names, conditions, functions, etc. Then, use a lexical analysis model to analyze the key features in terms of lexical, grammatical, and syntax to obtain grammatical feature information.
[0056] It can accurately parse and process complex query statements, increasing the flexibility of the system. By obtaining specific grammatical feature information, accurate query optimization can be performed in subsequent steps to improve execution efficiency. Structured feature extraction can reduce the occurrence of grammatical errors and improve the correctness of query statements.
[0057] In some embodiments, Figure 2 As shown, the feature extraction of the query statement to obtain grammatical feature information corresponding to the query statement includes steps S102a to S102c.
[0058] S102a. Obtain a pre-trained query analysis model, wherein the query analysis model includes a lexical analysis model, a grammatical analysis model, and a semantic analysis model.
[0059] The pre-trained query analysis model includes three sub-models: lexical analysis model, grammatical analysis model and semantic analysis model. The lexical analysis model is used to decompose the query statement into basic vocabulary units (such as identifiers, operators, keywords, etc.). The grammatical analysis model is used to check whether the arrangement of vocabulary units conforms to the SQL grammar rules. The semantic analysis model is used to understand the specific meaning and contextual relationship of vocabulary units and ensure the logical consistency of the query. By using pre-trained models, query statements can be parsed quickly and accurately, improving the efficiency of the system. At the same time, pre-trained models are usually trained with a large amount of data, have strong robustness and generalization capabilities, and can handle various complex queries. Ultimately, pre-trained models can reduce the workload of developing parsers from scratch and reduce development costs.
[0060] S102b. Input the query statement into the lexical analysis model, grammatical analysis model and semantic analysis model respectively. The lexical analysis model, grammatical analysis model and semantic analysis model respectively parse the query statement and output the lexical features, grammatical features and syntactic features corresponding to the query statement respectively.
[0061] The query statement is input into the lexical analysis model, the grammatical analysis model and the semantic analysis model for parsing. The lexical analysis model outputs the lexical features of the query statement, including information such as the type and position of the vocabulary unit. The grammatical analysis model outputs the grammatical features of the query statement, including a syntax tree or other structural representations. The semantic analysis model outputs the semantic features of the query statement, including contextual meaning and logical relationships. Then, through multi-level parsing (lexical, grammatical, semantics), the structure and meaning of the query statement can be fully understood. At the same time, the accuracy and reliability of the parsing can be improved by parsing and outputting their respective feature information independently. Different types of analysis results can also be processed and optimized independently to facilitate the expansion and updating of the model.
[0062] S102c. Generate the grammatical feature information according to the lexical features, grammatical features and syntactic features.
[0063] Based on the acquired lexical features, grammatical features, and semantic features, the grammatical feature information of the query statement is comprehensively generated. This comprehensive process may involve feature fusion technology, and the final comprehensive feature representation is generated through reasonable weighting or rules. By integrating feature information at different levels into unified grammatical feature information, it is convenient for subsequent processing and use. The comprehensive feature information can more comprehensively describe the properties of the query statement and improve the richness and expression ability of the feature information. The uniformly generated grammatical feature information can provide more accurate input data for the subsequent query tree generation and optimization algorithm, and improve the performance and reliability of the overall system.
[0064] The query statement is parsed in detail through steps S102a to S102c to obtain its grammatical feature information. This method not only utilizes the pre-trained advanced parsing model to achieve comprehensive parsing of lexical, grammatical and semantic aspects, but also generates unified grammatical features through comprehensive feature information, laying a solid foundation for subsequent query processing and optimization. Step S102a achieves efficient and robust query analysis model acquisition, reducing development costs. Step S102b improves the accuracy and comprehensiveness of query parsing through hierarchical parsing. Step S102c optimizes subsequent processing steps and improves the overall performance of the system by comprehensively generating unified feature information. Through such multi-level parsing and comprehensive processing methods, strong technical support and performance guarantee are provided for the session variable generation method.
[0065] Step S103: Obtain a preset session variable structure, and generate a query tree corresponding to the query statement according to the session variable structure, grammatical feature information and the query statement; wherein the structure of the query tree is the session variable structure.
[0066] Specifically, the preset session variable structure is a standard data structure used to define session variables. According to the extracted grammatical features and query statements, a query tree is generated, which is used to express the logic and operation steps of the query. The structure of the query tree depends on the preset session variable structure, which can ensure structural consistency. By using a unified session variable structure and query tree, the query processing flow is standardized. In addition, the unified structure and query tree make development and debugging more intuitive and convenient. At the same time, the standardized data structure improves the maintainability and extensibility of the code.
[0067] In some embodiments, Figure 3 As shown, generating a query tree corresponding to the query statement according to the session variable structure, grammatical feature information and the query statement includes steps S103a to S103c.
[0068] Step S103a. Acquire template information corresponding to the query tree according to the variable structure, and generate an initial query tree according to the template information.
[0069] First, according to the definition in the session variable structure, the template information used to generate the query tree is obtained. This template information may include the basic structure of the query tree, node type, arrangement method, etc. Then, an initial query tree is constructed using the template information, which contains the basic query framework, but has not yet added specific query content and child nodes. Finally, by using predefined template information, the generated query tree is ensured to be consistent and standardized, which improves the maintainability of the system. Template information simplifies the generation process of the initial query tree, reduces the construction time, and improves the overall efficiency. At the same time, the initial query tree provides a clear structural framework, which provides a basis for detailed filling and optimization in subsequent steps.
[0070] Step S103b. Divide the query statement according to the grammatical feature information to obtain multiple sub-query statements; generate a child node corresponding to the initial query tree according to each sub-query statement.
[0071] Based on the grammatical feature information, complex query statements are divided into multiple sub-query statements. This may involve identifying independent query sub-parts based on the syntax tree or other parsing results. According to each sub-query statement, a sub-node corresponding to the initial query tree is generated. These sub-nodes are specific query parts of the initial query tree, and each sub-node contains the specific content of a sub-query. Then, the complex query is decomposed into multiple sub-queries, which is convenient for optimization and processing one by one, and the processing accuracy is improved. Multiple sub-queries can be processed in parallel, using multi-threaded or distributed computing resources to improve processing efficiency. In addition, the decomposed query structure is clearer, which is convenient for analysis and debugging.
[0072] Step S103c. Obtain a target connection mode according to the variable structure, complete the connection of each of the child nodes in the query tree according to the target connection mode, and complete the generation of the query tree.
[0073] According to the definition of the variable structure, the connection mode of each child node in the query tree is obtained. These connection modes may include JOI N operations, nested queries, etc. According to the target connection mode, the child nodes are connected to the initial query tree to complete the generation of the final query tree. Ensure that all child nodes are integrated into the query tree according to the correct logical relationship to form a complete query plan. Then, through a clear connection mode, the logical integrity of the query tree is ensured, and potential syntax and logic errors are avoided. Through reasonable node connection, query optimization can be achieved and query execution efficiency can be improved. The connection mode of the query tree can be adjusted according to actual conditions, and has good adaptability and scalability. Through the above steps, the method not only improves the accuracy and efficiency of query processing, but also enhances flexibility and maintainability, providing a solid foundation for subsequent query optimization.
[0074] Step S104. Calculate the execution information corresponding to the query tree according to the preset optimization algorithm, generate session variables according to the execution information and store the session variables in the target variable storage space to complete the generation of the session variables; wherein, by running the session variables in the target variable storage space, the preset operations corresponding to the session variables can be implemented in the SQL database.
[0075] Specifically, by using an optimization algorithm (such as a cost model-based optimizer, a genetic algorithm, a dynamic programming algorithm, etc.) to calculate the execution information of the query tree, an optimized execution plan is generated. Session variables are generated according to the execution plan and stored in the previously allocated target variable storage space. Session variables can be executed in the SQL database through a specific operating mechanism to implement corresponding database operations. The optimization algorithm ensures that the generated query execution plan is efficient and improves query performance. At the same time, the automatic generation and optimization of session variables reduces manual intervention and improves the level of automation. By optimizing and generating session variables in advance, the system's response speed to query requests is improved. The optimized execution plan and session variables ensure the accurate execution of database operations and improve the consistency and reliability of data processing.
[0076] In summary, the provided session variable generation method significantly improves the efficiency and accuracy of SQL database queries through flexible response to session information, precise parsing of query statements, standardized structure management, and efficient calculation of optimization algorithms, enhances the flexibility and automation level of the system, and provides a technical solution with great practical value.
[0077] In some embodiments, before generating a query tree corresponding to the query statement according to the session variable structure, grammatical feature information and the query statement, it also includes: obtaining a command type corresponding to the query statement according to the grammatical feature information; wherein the command type includes a first command type and a second command type, and the complexity of the first command type is lower than that of the second command type; if the command type corresponding to the query statement is the first command type, determining that the query statement is a simple statement, and directly generating the session variable corresponding to the simple statement; if the command type corresponding to the query statement is the second command type, determining that the query statement is a complex statement, and generating a query tree corresponding to the complex statement according to the session variable structure, grammatical feature information and the query statement.
[0078] First, after obtaining the grammatical feature information of the query statement, the provided method further analyzes the feature information to determine the command type of the query statement. The command type can be classified according to predefined rules, usually including a first command type and a second command type. The first command type indicates a simple command (such as creating a table, creating a user, backup, etc.) with a lower complexity; the second command type indicates a complex command (such as SELECT / INSERT / DELETE / UPDATE) with a higher complexity. By analyzing the grammatical feature information, the complexity of the query statement can be accurately distinguished to ensure the pertinence of subsequent processing. At the same time, different processing resources are allocated according to the command type to improve resource utilization and processing efficiency.
[0079] Secondly, for simple statements, instead of generating complex query trees, corresponding session variables are generated directly according to predefined rules. This simplified processing can avoid unnecessary complex calculations and quickly obtain the required results. For simple statements, session variables can be generated directly to speed up processing. At the same time, unnecessary query trees can be avoided, saving computing resources. And through simplified processing, the overall system efficiency is improved.
[0080] Finally, for complex statements, a query tree is generated according to the session variable structure, grammatical feature information, and query statements in accordance with predefined steps. This processing method ensures that complex statements are fully parsed and optimized. By generating a query tree, the structure and logic of complex query statements are parsed in detail to ensure the accuracy of processing. By generating and optimizing the query tree, an optimized execution plan can be obtained to improve query efficiency. It can respond to and process complex query requirements and enhance the flexibility and processing capabilities of the system.
[0081] In summary, the above embodiment further optimizes the query processing process by adding a command type determination step before generating the query tree. Through command classification, simple statements are processed quickly and complex statements are parsed in detail. Avoid complex calculations on simple statements and optimize resource allocation. Improve system flexibility and processing capabilities: It can quickly respond to simple queries and cope with complex query requirements. Through this processing method, the provided method significantly improves processing speed and resource utilization efficiency while ensuring the accuracy and reliability of query results.
[0082] In some embodiments, before calculating the execution information corresponding to the query tree according to a preset optimization algorithm, it also includes: parsing the query tree to obtain query tree rules and query tree views corresponding to the query tree; updating the query tree rules and query tree views according to preset session variable rules and session variable views respectively; regenerating the query tree according to the updated query tree rules and query tree views to calculate the updated execution information corresponding to the query tree according to the preset optimization algorithm.
[0083] In some embodiments, the calculation of execution information corresponding to the query tree according to a preset optimization algorithm includes: generating a preliminary execution plan according to the query tree; the preliminary plan includes multiple sub-plans; obtaining the best access method corresponding to the query tree according to the variable structure; the best access method includes any one of a full table scan and an index scan; calculating the cost information of the sub-plans using multiple preset connection methods respectively according to the best access method and the optimization algorithm; wherein the preset connection methods include at least nested loop connections, sort merge connections and hash connections; determining the optimal connection method among multiple preset connection methods according to the cost information; and generating the execution information according to the optimal connection method, the best access method and the preliminary plan.
[0084] First, a preliminary execution plan is generated based on the query tree. The preliminary plan includes multiple sub-plans, each of which corresponds to a query operation or step. Through the preliminary execution plan, the overall framework of query execution is determined in advance, providing a basis for subsequent optimization. The division of sub-plans makes complex queries more structured and hierarchical, which is convenient for subsequent optimization processing.
[0085] Then, based on the information in the variable structure, the best access method for the query tree is obtained. Typical access methods include full table scan and index scan. Ensure that the query uses the optimal access path to improve query efficiency. By selecting the appropriate access method, resource usage is optimized and unnecessary IO operations are reduced.
[0086] Then, according to the best access method and optimization algorithm, the cost information of multiple preset connection methods is calculated for each sub-plan. These preset connection methods include nested loop connection, sort merge connection and hash connection. The cost calculation of multiple connection methods ensures a comprehensive evaluation of different query paths. By comparing the cost information, the connection method with the lowest cost is selected to improve the query execution efficiency.
[0087] Finally, the optimal connection method is selected based on the cost information of multiple preset connection methods. The final execution information is also generated based on the optimal connection method, the best access method and the preliminary plan. By selecting the optimal connection method to generate the final execution information, the optimality of the execution plan is ensured. The optimized execution plan significantly improves the execution efficiency and response speed of the query.
[0088] Exemplarily, the cost information of the sub-plans adopting multiple preset connection methods respectively is calculated according to the optimal access method and the optimization algorithm, including: randomly adjusting the order of the multiple sub-plans to generate multiple candidate sequence queues; calculating the cost information of each candidate sequence queue adopting multiple preset connection methods respectively according to the optimal access method and the optimization algorithm; determining the optimal connection method and the optimal sequence queue from the multiple preset connection methods according to the cost information, and generating the execution information according to the optimal connection method, the optimal access method, the optimal sequence queue and the preliminary plan.
[0089] By randomly adjusting the order of sub-plans, multiple execution orders are generated, providing more optimization options. The random adjustment method can avoid falling into local optimality and improve the overall optimization level.
[0090] At the same time, according to the best access method and optimization algorithm, the cost information of each candidate sequential queue under different preset connection methods is calculated. The cost calculation of diversified candidate sequential queues ensures a comprehensive evaluation and covers more possible execution paths. By comparing the costs of different sequential queues and connection methods, the combination with the lowest cost is selected to optimize query execution.
[0091] Finally, based on the cost information, the optimal connection method and sequence queue are determined among multiple preset connection methods and candidate sequence queues. The final execution information is generated based on the optimal connection method, the best access method, the optimal sequence queue and the preliminary plan. By optimizing the connection method and the execution sequence at the same time, the query execution efficiency is further improved. The final generated execution information is optimized in many aspects, ensuring the optimality and execution performance of the query plan.
[0092] It should be noted that the focus of this method is to convert the syntax of session variable references into a structure referenced by constants. The value of the session variable can be directly queried during the syntax analysis phase and a query tree corresponding to the structure can be generated. Since the structure corresponding to the query tree is a native structure, no development is required for subsequent processes, which can reduce the impact on the original SQL engine and coding omissions.
[0093] At the same time, the corresponding basic syntax is defined as follows:
[0094] The definition syntax of a session variable is: DECLARE SESSION VARIABLE varname vartype DEFAULTdefva l ue.
[0095] varname is the name of the session variable, which is composed of characters, numbers, and underscores. For example: var_name, v1, etc. vartype is the type of the session variable, which can only be an existing type in the database system. default is the default value of the session variable.
[0096] The syntax for referencing a session variable is: @ followed by the session variable name. The session variable name should consist of letters, numbers, and underscores. For example: @var_name, @v1, etc. (At the same time, the provided method can achieve the replacement of students.id=@id in the SQL statement with students.id=1). Delete a session variable: DROP SESS ION VARIABLEvarname.
[0097] See also Figure 4 As shown, Figure 4 1 is a schematic diagram of the structure of the session variable generation device 200 provided in an embodiment of the present application. The session variable generation device 200 is used to execute the steps of the session variable generation method shown in the above embodiments. The session variable generation device 200 can be a single server or a server cluster, or the session variable generation device 200 can be a terminal, which can be a handheld terminal, a laptop computer, a wearable device or a robot, etc.
[0098] like Figure 4 As shown, the session variable generating device 200 includes:
[0099] The space establishing unit 201 is used to generate a target variable storage space in a memory pool corresponding to the SQL database when detecting that the session information is established.
[0100] The statement receiving unit 202 is used to extract features of the query statement if a query statement corresponding to the session information is received, and obtain grammatical feature information corresponding to the query statement.
[0101] The structure acquisition unit 203 is used to acquire a preset session variable structure, and generate a query tree corresponding to the query statement according to the session variable structure, grammatical feature information and the query statement; wherein the structure of the query tree is the session variable structure.
[0102] The variable generation unit 204 is used to calculate the execution information corresponding to the query tree according to a preset optimization algorithm, generate session variables according to the execution information and store the session variables in the target variable storage space to complete the generation of the session variables; wherein, by running the session variables in the target variable storage space, the preset operations corresponding to the session variables can be implemented in the SQL database.
[0103] In some embodiments, the statement receiving unit 202 is used to: obtain a pre-trained query analysis model, the query analysis model including a lexical analysis model, a grammatical analysis model and a semantic analysis model; input the query statement into the lexical analysis model, the grammatical analysis model and the semantic analysis model respectively, the lexical analysis model, the grammatical analysis model and the semantic analysis model respectively parse the query statement, and respectively output the lexical features, grammatical features and syntactic features corresponding to the query statement; generate the grammatical feature information according to the lexical features, grammatical features and syntactic features.
[0104] In some embodiments, before generating a query tree corresponding to the query statement according to the session variable structure, grammatical feature information and the query statement, it also includes: a type acquisition unit, which is used to acquire a command type corresponding to the query statement according to the grammatical feature information; wherein the command type includes a first command type and a second command type, and the complexity of the first command type is lower than that of the second command type; if the command type corresponding to the query statement is the first command type, it is determined that the query statement is a simple statement, and the session variable corresponding to the simple statement is directly generated; if the command type corresponding to the query statement is the second command type, it is determined that the query statement is a complex statement, and a query tree corresponding to the complex statement is generated according to the session variable structure, grammatical feature information and the query statement.
[0105] In some embodiments, the structure acquisition unit 203 is further used to: acquire template information corresponding to the query tree according to the variable structure, and generate an initial query tree according to the template information; divide the query statement according to the grammatical feature information to acquire multiple sub-query statements; generate a child node corresponding to the initial query tree according to each of the sub-query statements; acquire a target connection method according to the variable structure, complete the connection of each of the child nodes in the query tree according to the target connection method, and complete the generation of the query tree.
[0106] In some embodiments, before the execution information corresponding to the query tree is calculated according to the preset optimization algorithm, it also includes: a query update unit, which is used to parse the query tree and obtain the query tree rules and query tree views corresponding to the query tree; update the query tree rules and query tree views according to the preset session variable rules and session variable views respectively; regenerate the query tree according to the updated query tree rules and query tree views, so as to calculate the execution information corresponding to the updated query tree according to the preset optimization algorithm.
[0107] In some embodiments, the variable generation unit 204 is also used to: generate a preliminary execution plan according to the query tree; the preliminary plan includes multiple sub-plans; obtain the best access method corresponding to the query tree according to the variable structure; the best access method includes any one of a full table scan and an index scan; calculate the cost information of the sub-plans using multiple preset connection methods according to the best access method and the optimization algorithm; wherein the preset connection methods include at least nested loop connections, sort merge connections and hash connections; determine the optimal connection method from multiple preset connection methods according to the cost information; and generate the execution information according to the optimal connection method, the best access method and the preliminary plan.
[0108] Exemplarily, the variable generation unit 204 is also used to: randomly adjust the order of multiple sub-plans to generate multiple candidate sequence queues; calculate the cost information of each candidate sequence queue using multiple preset connection methods according to the optimal access method and the optimization algorithm; determine the optimal connection method and the optimal sequence queue among the multiple preset connection methods according to the cost information, and generate the execution information according to the optimal connection method, the optimal access method, the optimal sequence queue and the preliminary plan.
[0109] It should be noted that those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working processes of the session variable generation device and each module described above can refer to the corresponding processes in the session variable generation method embodiments described in the above embodiments, and will not be repeated here.
[0110] The above session variable generation method can be implemented in the form of a computer program. The computer program can be used in Figure 4 Run on the device shown.
[0111] See also Figure 5 , Figure 5 1 is a schematic block diagram of the structure of a computer device provided in an embodiment of the present application. The computer device includes a processor, a memory and a network interface connected via a device bus, wherein the memory may include a storage medium and an internal memory.
[0112] The storage medium can store an operating device and a computer program. The computer program includes program instructions, and when the program instructions are executed, the processor can execute any session variable generation method.
[0113] The processor is used to provide computing and control capabilities and support the operation of the entire computer equipment.
[0114] The internal memory provides an environment for the operation of the computer program in the non-volatile storage medium. When the computer program is executed by the processor, the processor can execute any session variable generation method.
[0115] The network interface is used for network communication, such as sending assigned tasks, etc. Those skilled in the art will understand that Figure 5 The structure shown in the figure is only a block diagram of a part of the structure related to the scheme of the present application, and does not constitute a limitation on the terminal to which the scheme of the present application is applied. The specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.
[0116] It should be understood that the processor may be a central processing unit (CPU), and the processor may also be other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. Among them, the general-purpose processor may be a microprocessor or the processor may also be any conventional processor, etc.
[0117] In one embodiment, the processor is used to run a computer program stored in the memory to implement the following steps:
[0118] When it is detected that the session information is established, a target variable storage space is generated in the memory pool corresponding to the SQL database;
[0119] If a query statement corresponding to the session information is received, feature extraction is performed on the query statement to obtain grammatical feature information corresponding to the query statement;
[0120] Obtaining a preset session variable structure, and generating a query tree corresponding to the query statement according to the session variable structure, grammatical feature information and the query statement; wherein the structure of the query tree is the session variable structure;
[0121] The execution information corresponding to the query tree is calculated according to a preset optimization algorithm, a session variable is generated according to the execution information and the session variable is stored in the target variable storage space to complete the generation of the session variable; wherein, by running the session variable in the target variable storage space, the preset operation corresponding to the session variable can be implemented in the SQL database.
[0122] In some embodiments, the feature extraction of the query statement to obtain grammatical feature information corresponding to the query statement includes: obtaining a pre-trained query analysis model, the query analysis model including a lexical analysis model, a grammatical analysis model and a semantic analysis model; inputting the query statement into the lexical analysis model, the grammatical analysis model and the semantic analysis model respectively, the lexical analysis model, the grammatical analysis model and the semantic analysis model respectively parse the query statement, and respectively output the lexical features, grammatical features and syntactic features corresponding to the query statement; and generating the grammatical feature information according to the lexical features, grammatical features and syntactic features.
[0123] In some embodiments, before generating a query tree corresponding to the query statement according to the session variable structure, grammatical feature information and the query statement, it also includes: obtaining a command type corresponding to the query statement according to the grammatical feature information; wherein the command type includes a first command type and a second command type, and the complexity of the first command type is lower than that of the second command type; if the command type corresponding to the query statement is the first command type, determining that the query statement is a simple statement, and directly generating the session variable corresponding to the simple statement; if the command type corresponding to the query statement is the second command type, determining that the query statement is a complex statement, and generating a query tree corresponding to the complex statement according to the session variable structure, grammatical feature information and the query statement.
[0124] In some embodiments, a query tree corresponding to the query statement is generated according to the session variable structure, grammatical feature information and the query statement, including: obtaining template information corresponding to the query tree according to the variable structure, and generating an initial query tree according to the template information; dividing the query statement according to the grammatical feature information to obtain multiple sub-query statements; generating a child node corresponding to the initial query tree according to each of the sub-query statements; obtaining a target connection method according to the variable structure, completing the connection of each of the child nodes in the query tree according to the target connection method, and completing the generation of the query tree.
[0125] In some embodiments, before calculating the execution information corresponding to the query tree according to a preset optimization algorithm, it also includes: parsing the query tree to obtain query tree rules and query tree views corresponding to the query tree; updating the query tree rules and query tree views according to preset session variable rules and session variable views respectively; regenerating the query tree according to the updated query tree rules and query tree views to calculate the updated execution information corresponding to the query tree according to the preset optimization algorithm.
[0126] In some embodiments, the calculation of execution information corresponding to the query tree according to a preset optimization algorithm includes: generating a preliminary execution plan according to the query tree; the preliminary plan includes multiple sub-plans; obtaining the best access method corresponding to the query tree according to the variable structure; the best access method includes any one of a full table scan and an index scan; calculating the cost information of the sub-plans using multiple preset connection methods respectively according to the best access method and the optimization algorithm; wherein the preset connection methods include at least nested loop connections, sort merge connections and hash connections; determining the optimal connection method among multiple preset connection methods according to the cost information; and generating the execution information according to the optimal connection method, the best access method and the preliminary plan.
[0127] Exemplarily, the cost information of the sub-plans adopting multiple preset connection methods respectively is calculated according to the optimal access method and the optimization algorithm, including: randomly adjusting the order of the multiple sub-plans to generate multiple candidate sequence queues; calculating the cost information of each candidate sequence queue adopting multiple preset connection methods respectively according to the optimal access method and the optimization algorithm; determining the optimal connection method and the optimal sequence queue from the multiple preset connection methods according to the cost information, and generating the execution information according to the optimal connection method, the optimal access method, the optimal sequence queue and the preliminary plan.
[0128] A computer-readable storage medium is also provided in an embodiment of the present application. The computer-readable storage medium stores a computer program. The computer program includes program instructions. The processor executes the program instructions to implement the steps of the session variable generation method provided in the above embodiments of the present application.
[0129] The computer-readable storage medium may be an internal storage unit of the computer device described in the foregoing embodiment, such as a hard disk or memory of the computer device. The computer-readable storage medium may also be an external storage device of the computer device, such as a plug-in hard disk, a smart memory card (SmartMedia Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (Flash Card), etc., equipped on the computer device.
[0130] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any technician familiar with the technical field can easily think of various equivalent modifications or replacements within the technical scope disclosed in the present application, and these modifications or replacements should be included in the protection scope of the present application. Therefore, the protection scope of the present application shall be based on the protection scope of the claims.
Claims
1. A method for generating a session variable, characterized in that: include: When it is detected that the session information is established, a target variable storage space is generated in the memory pool corresponding to the SQL database; If a query statement corresponding to the session information is received, feature extraction is performed on the query statement to obtain grammatical feature information corresponding to the query statement; Obtaining a preset session variable structure, and generating a query tree corresponding to the query statement according to the session variable structure, grammatical feature information and the query statement; wherein the structure of the query tree is the session variable structure; The execution information corresponding to the query tree is calculated according to a preset optimization algorithm, a session variable is generated according to the execution information and the session variable is stored in the target variable storage space to complete the generation of the session variable; wherein, by running the session variable in the target variable storage space, the preset operation corresponding to the session variable can be implemented in the SQL database.
2. The method according to claim 1, characterized in that: The extracting features of the query statement to obtain grammatical feature information corresponding to the query statement includes: Acquire a pre-trained query analysis model, wherein the query analysis model includes a lexical analysis model, a grammatical analysis model, and a semantic analysis model; Inputting the query statement into the lexical analysis model, the grammatical analysis model and the semantic analysis model respectively, the lexical analysis model, the grammatical analysis model and the semantic analysis model respectively parse the query statement, and respectively outputting the lexical features, grammatical features and syntactic features corresponding to the query statement; The grammatical feature information is generated according to the lexical features, grammatical features and syntactic features.
3. The method according to claim 1, characterized in that Before generating a query tree corresponding to the query statement according to the session variable structure, the grammatical feature information and the query statement, the method further includes: Acquire a command type corresponding to the query statement according to the grammatical feature information; wherein the command type includes a first command type and a second command type, and the complexity of the first command type is lower than that of the second command type; If the command type corresponding to the query statement is the first command type, determining that the query statement is a simple statement, and directly generating the session variable corresponding to the simple statement; If the command type corresponding to the query statement is the second command type, the query statement is determined to be a complex statement, and a query tree corresponding to the complex statement is generated according to the session variable structure, grammatical feature information and the query statement.
4. The method according to claim 1, characterized in that: Generating a query tree corresponding to the query statement according to the session variable structure, the grammatical feature information and the query statement, including: Acquire template information corresponding to the query tree according to the variable structure, and generate an initial query tree according to the template information; Divide the query statement according to the grammatical feature information to obtain multiple sub-query statements; Generate a child node corresponding to the initial query tree according to each of the sub-query statements; A target connection mode is obtained according to the variable structure, and connection of each of the child nodes is completed in the query tree according to the target connection mode, thereby completing generation of the query tree.
5. The method according to claim 1, characterized in that: Before calculating the execution information corresponding to the query tree according to the preset optimization algorithm, the method further includes: Parsing the query tree to obtain a query tree rule and a query tree view corresponding to the query tree; updating the query tree rule and the query tree view respectively according to the preset session variable rule and the session variable view; The query tree is regenerated according to the updated query tree rule and the query tree view, so as to calculate the execution information corresponding to the updated query tree according to a preset optimization algorithm.
6. The method according to claim 1, characterized in that The calculating the execution information corresponding to the query tree according to the preset optimization algorithm includes: Generate a preliminary execution plan according to the query tree; the preliminary plan includes multiple sub-plans; Acquire the best access method corresponding to the query tree according to the variable structure; the best access method includes any one of a full table scan and an index scan; According to the optimal access method and the optimization algorithm, cost information of the sub-plans using a plurality of preset connection modes is calculated respectively; wherein the preset connection modes include at least a nested loop connection, a sort-merge connection and a hash connection; Determining an optimal connection mode among multiple preset connection modes according to the cost information; The execution information is generated according to the optimal connection mode, the best access method and the preliminary plan.
7. The method according to claim 6, characterized in that The calculating, according to the optimal access method and the optimization algorithm, the cost information of the sub-plans respectively using a plurality of preset connection modes includes: Randomly adjusting the order of the plurality of sub-plans to generate a plurality of candidate order queues; According to the optimal access method and the optimization algorithm, the cost information of each candidate sequential queue adopting multiple preset connection methods is calculated respectively; the optimal connection method and the optimal sequential queue are determined among the multiple preset connection methods according to the cost information, and the execution information is generated according to the optimal connection method, the optimal access method, the optimal sequential queue and the preliminary plan.
8. A session variable generation device, characterized in that: include: A space establishment unit, used for generating a target variable storage space in a memory pool corresponding to the SQL database when detecting that the session information is established; A statement receiving unit, configured to extract features of a query statement corresponding to the session information upon receiving the query statement, and obtain grammatical feature information corresponding to the query statement; A structure acquisition unit, used to acquire a preset session variable structure, and generate a query tree corresponding to the query statement according to the session variable structure, grammatical feature information and the query statement; wherein the structure of the query tree is the session variable structure; A variable generation unit is used to calculate the execution information corresponding to the query tree according to a preset optimization algorithm, generate session variables according to the execution information and store the session variables in the target variable storage space to complete the generation of the session variables; wherein, by running the session variables in the target variable storage space, the preset operations corresponding to the session variables can be implemented in the SQL database.
9. A computer device, characterized in that: The computer device includes a memory and a processor; The memory is used to store computer programs; The processor is configured to execute the computer program and implement the method according to any one of claims 1 to 7 when executing the computer program.
10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, which, when executed by a processor, causes the processor to implement the method according to any one of claims 1 to 7.