Dynamic SQL mapping optimization method, system, equipment and medium

Abstract

The invention discloses a dynamic SQL (Structured Query Language) mapping optimization method, system, equipment and medium, belongs to the technical field of database query optimization, and aims to solve the technical problem that the structure of a traditional static SQL statement is difficult to automatically adjust according to a specific query scene to achieve an optimal query path and performance. Analyzing the data query request to obtain query parameters and conditions; constructing an initial SQL query template according to the query parameters and conditions; calling a built-in query optimizer or an external execution plan analysis tool of the database to carry out performance analysis on the initial SQL query template; dynamically adjusting an SQL statement structure according to an execution plan analysis result; and executing the optimized SQL statement and returning a query result.

Description

Technical Field

[0001] The present invention relates to the technical field of database query optimization, in particular to a dynamic SQL mapping optimization method, system, equipment and medium. Background Art

[0002] As the scale of business continues to expand and the amount of data increases dramatically, the importance of database query performance has become increasingly prominent, which is directly related to the system's response speed, user experience, and overall operational efficiency. In the era of high information technology, enterprises not only rely on databases to store and manage massive amounts of data, but also expect to be able to quickly and accurately extract valuable information from these data to support decision-making and business innovation.

[0003] Although traditional static SQL statements are simple to write and easy to understand, they are powerless when faced with complex and ever-changing query requirements. Such queries often involve the association of multiple tables, complex aggregation operations, or specific sorting and filtering conditions. Static SQL statements are difficult to automatically adjust their structure according to specific query scenarios to achieve the optimal query path and performance. In addition, with the continuous optimization and reconstruction of database indexes, as well as the dynamic update of query data itself, the execution plan of static SQL statements may no longer be the best choice, and may even lead to performance bottlenecks.

[0004] Traditional JDBC database operations require manual parameter setting, result set processing, manual database connection, and database closing. Database operations are somewhat cumbersome, with high code coupling, which is not conducive to subsequent maintenance.

[0005] In summary, traditional static SQL statements have the problem of being difficult to automatically adjust their structure according to specific query scenarios to achieve the optimal query path and performance. Summary of the invention

[0006] The technical task of the present invention is to provide a dynamic SQL mapping optimization method, system, device and medium to solve the problem that traditional static SQL statements are difficult to automatically adjust their structure according to specific query scenarios to achieve the optimal query path and performance.

[0007] The technical task of the present invention is achieved in the following manner: a dynamic SQL mapping optimization method, the method is as follows:

[0008] Receive data query requests, parse the data query requests, and obtain query parameters and conditions;

[0009] Build an initial SQL query template based on query parameters and conditions;

[0010] Call the database's built-in query optimizer or an external execution plan analysis tool to perform performance analysis on the initial SQL query template;

[0011] Dynamically adjust the SQL statement structure based on the execution plan analysis results;

[0012] Execute the optimized SQL statement and return the query result.

[0013] As a preferred method, the initial SQL query template is constructed as follows:

[0014] Use predefined SQL template library and template engine to dynamically build initial SQL query template;

[0015] Among them, the SQL template library provides a method for dynamically building SQL statements based on the rule engine, specifically: automatically generating the optimal SQL query statement according to different query conditions;

[0016] The template engine is used to fill the parsed parameters and conditions into the corresponding template to form an executable SQL statement.

[0017] Preferably, the performance analysis of the initial SQL query template is as follows:

[0018] The database's built-in query optimizer or external execution plan analysis tool uses statistical information and cost estimation models to conduct in-depth analysis of the SQL statement execution plan and generate an execution plan report containing key information such as estimated cost, index usage, and data access path.

[0019] Among them, the execution plan analysis tool is used to analyze the database index usage and the execution plan information of the table connection sequence.

[0020] Preferably, the SQL statement structure is dynamically adjusted according to the execution plan analysis results as follows:

[0021] According to the bottleneck points in the execution plan, the rule-based optimization (RBO) algorithm, cost-based optimization (CBO) algorithm and machine learning-assisted optimization strategy are used to dynamically adjust the structure of SQL statements. The dynamic adjustment of the structure of SQL statements includes optimizing the query order to reduce the amount of data scanning, adjusting the connection conditions to use more effective indexes, and selecting the best index according to the data distribution characteristics.

[0022] The integrated query optimization component automatically optimizes the SQL query path by analyzing historical query patterns and data access efficiency, supports multi-dimensional query performance evaluation, and provides optimization suggestions for index creation and partitioning strategies.

[0023] More preferably, in the process of executing the optimized SQL statement and returning the query results, the database connection pool and query cache technology are used to reduce the database connection overhead and the overhead of repeated queries.

[0024] A dynamic SQL mapping optimization system, the system comprising:

[0025] The parameter parsing module is used to receive the data query request sent by the user, parse the data query request, and obtain the query parameters and conditions;

[0026] Initialize SQL template building module, used to build initial SQL query template according to query parameters and conditions;

[0027] The execution plan analysis module is used to call the database's built-in query optimizer or an external execution plan analysis tool to perform performance analysis on the initial SQL query template;

[0028] SQL optimization module, used to dynamically adjust the SQL statement structure according to the execution plan analysis results;

[0029] The query execution module is used to execute the optimized SQL statement and return the query results.

[0030] Preferably, the initial SQL query template construction module dynamically constructs the initial SQL query template using a predefined SQL template library and a template engine;

[0031] Among them, the SQL template library provides a method for dynamically building SQL statements based on the rule engine, specifically: automatically generating the optimal SQL query statement according to different query conditions;

[0032] The template engine is used to fill the parsed parameters and conditions into the corresponding template to form an executable SQL statement;

[0033] The execution plan analysis module uses the built-in query optimizer of the database or external execution plan analysis tools to conduct in-depth analysis of the execution plan of SQL statements using statistical information and cost estimation models, and generates an execution plan report containing key information such as estimated cost, index usage, and data access path.

[0034] Among them, the execution plan analysis tool is used to analyze the database index usage and the execution plan information of the table connection sequence.

[0035] Preferably, the SQL optimization module dynamically adjusts the structure of SQL statements based on bottlenecks in the execution plan using rule-based optimization (RBO) algorithms, cost-based optimization (CBO) algorithms, and machine learning-assisted optimization strategies; the dynamic adjustment of the structure of SQL statements includes optimizing the query order to reduce the amount of data scanning, adjusting the connection conditions to use more effective indexes, and selecting the best index based on data distribution characteristics; and integrating query optimization components to automatically optimize SQL query paths by analyzing historical query patterns and data access efficiency, and supporting multi-dimensional query performance evaluation, while providing optimization suggestions for index creation and partitioning strategies;

[0036] The query execution module uses database connection pool and query cache technology to reduce the overhead of database connections and repeated queries, while supporting streaming or paging loading of result sets to reduce memory pressure and improve user experience.

[0037] An electronic device comprising: a memory and at least one processor;

[0038] Wherein, the memory stores computer-executable instructions;

[0039] The at least one processor executes the computer-executable instructions stored in the memory, so that the at least one processor executes the dynamic SQL mapping optimization method as described above.

[0040] A computer-readable storage medium stores computer-executable instructions. When a processor executes the computer-executable instructions, the above-mentioned dynamic SQL mapping optimization method is implemented.

[0041] The dynamic SQL mapping optimization method, system, device and medium of the present invention have the following advantages:

[0042] (1) The present invention uses a dynamic SQL mapping optimization method to improve database query efficiency, reduce system resource consumption, provide certain technical methods for related businesses involving database queries, find new applications and breakthroughs for data query and acquisition, and overcome the shortcomings of traditional static SQL statements that are difficult to automatically adjust their structure according to specific query scenarios to achieve the optimal query path and performance;

[0043] (ii) The present invention improves database query efficiency and reduces system resource consumption by using dynamic SQL mapping optimization technology based on runtime parameters and database execution plan analysis;

[0044] (III) The present invention becomes a key way to improve database query performance through dynamic SQL generation and optimization technology, which can intelligently analyze query conditions, database architecture, data distribution and index status, and dynamically build or adjust the structure and execution plan of SQL statements;

[0045] (iv) The present invention, through this dynamic SQL generation and optimization technology, can not only significantly improve database query performance and reduce system operation and maintenance costs, but also better support complex and changing business needs and promote business innovation and development;

[0046] (V) The present invention is based on the Mybatis framework and uses a dynamic SQL mapping optimization method to improve database query efficiency, reduce system resource consumption, and provide certain technical methods for related businesses involving database queries, which can find new applications and breakthroughs for data query and acquisition;

[0047] (VI) During the query execution process, the present invention implements an intelligent caching algorithm to cache frequently executed SQL statements and their results, thereby reducing the overhead caused by repeated queries;

[0048] (VII) The present invention significantly improves query efficiency: by dynamically optimizing the SQL statement structure, unnecessary database scanning and index searches are reduced, significantly shortening query time;

[0049] (VIII) The present invention reduces system resource consumption: the optimized SQL statements can more effectively utilize database resources, reduce CPU and memory usage, and improve overall system performance;

[0050] (IX) The present invention enhances system flexibility: adapting to different query requirements and database status changes, and improving system scalability and maintainability;

[0051] (10) The rule engine of the present invention supports real-time updates, allowing the SQL generation strategy to be adjusted according to the latest business rules or data distribution;

[0052] (XII) The cache mechanism adopted by the present invention can automatically identify data changes and refresh the cache content in a timely manner to ensure data consistency. BRIEF DESCRIPTION OF THE DRAWINGS

[0053] The present invention is further described below in conjunction with the accompanying drawings.

[0054] Attached Figure 1 This is a schematic diagram of the basic workflow of the Mybatis framework;

[0055] Attached Figure 2 The flowchart of the dynamic SQL mapping optimization method is shown in FIG. DETAILED DESCRIPTION

[0056] The dynamic SQL mapping optimization method, system, device and medium of the present invention are described in detail below with reference to the accompanying drawings and specific embodiments.

[0057] Embodiment 1:

[0058] As attached Figure 2 As shown, this embodiment provides a dynamic SQL mapping optimization method, which is specifically as follows:

[0059] S1. Receive a data query request, parse the data query request, and obtain query parameters and conditions;

[0060] S2, constructing an initial SQL query template based on query parameters and conditions;

[0061] S3. Call the database's built-in query optimizer or an external execution plan analysis tool to perform performance analysis on the initial SQL query template;

[0062] S4. Dynamically adjust the SQL statement structure according to the execution plan analysis results;

[0063] S5. Execute the optimized SQL statement and return the query result.

[0064] The construction of the initial SQL query template in step S2 of this embodiment is specifically as follows:

[0065] Use predefined SQL template library and template engine to dynamically build initial SQL query template;

[0066] Among them, the SQL template library provides a method for dynamically building SQL statements based on the rule engine, specifically: automatically generating the optimal SQL query statement according to different query conditions;

[0067] The template engine is used to fill the parsed parameters and conditions into the corresponding template to form an executable SQL statement.

[0068] The performance analysis of the initial SQL query template in step S3 of this embodiment is specifically as follows:

[0069] The database's built-in query optimizer or external execution plan analysis tool uses statistical information and cost estimation models to conduct in-depth analysis of the SQL statement execution plan and generate an execution plan report containing key information such as estimated cost, index usage, and data access path.

[0070] Among them, the execution plan analysis tool is used to analyze the database index usage and the execution plan information of the table connection sequence.

[0071] In step S4 of this embodiment, the SQL statement structure is dynamically adjusted according to the execution plan analysis result as follows:

[0072] S401. According to the bottleneck points in the execution plan, a rule-based optimization (RBO) algorithm, a cost-based optimization (CBO) algorithm, and a machine learning-assisted optimization strategy are used to dynamically adjust the structure of the SQL statement; wherein the dynamic adjustment of the structure of the SQL statement includes optimizing the query sequence to reduce the amount of data scanning, adjusting the connection conditions to use more effective indexes, and selecting the best index according to data distribution characteristics;

[0073] S402, integrated query optimization component, automatically optimizes SQL query paths by analyzing historical query patterns and data access efficiency, supports multi-dimensional query performance evaluation, and provides optimization suggestions for index creation and partitioning strategies.

[0074] In the process of executing the optimized SQL statement and returning the query result in step S5 of this embodiment, the database connection pool and query cache technology are used to reduce the overhead of database connection and the overhead of repeated queries.

[0075] Among them, as attached Figure 1 As shown in the figure, Mybatis is a persistence layer framework, which is mainly used to simplify data operations. Mybatis matches the field names queried in the database with the attributes of the entity class in Java one by one. It is a semi-automatic ORM framework, provides mapping files, and uses dynamic SQL statements to perform basic operations such as adding, deleting, modifying, and querying the database. It eliminates the coupling problem caused by embedding SQL statements in Java code, facilitates unified management, is conducive to complex data operations, and clarifies a large amount of redundant JDBC code.

[0076] Embodiment 2:

[0077] This embodiment provides a dynamic SQL mapping optimization system, which includes:

[0078] The parameter parsing module is used to receive the data query request sent by the user, parse the data query request, and obtain the query parameters and conditions;

[0079] Initialize SQL template building module, used to build initial SQL query template according to query parameters and conditions;

[0080] The execution plan analysis module is used to call the database's built-in query optimizer or an external execution plan analysis tool to perform performance analysis on the initial SQL query template;

[0081] SQL optimization module, used to dynamically adjust the SQL statement structure according to the execution plan analysis results;

[0082] The query execution module is used to execute the optimized SQL statement and return the query results.

[0083] The initial SQL query template construction module in this embodiment uses a predefined SQL template library and a template engine to dynamically construct an initial SQL query template;

[0084] Among them, the SQL template library provides a method for dynamically building SQL statements based on the rule engine, specifically: automatically generating the optimal SQL query statement according to different query conditions;

[0085] The template engine is used to fill the parsed parameters and conditions into the corresponding template to form an executable SQL statement.

[0086] The execution plan analysis module in this embodiment uses the built-in query optimizer of the database or an external execution plan analysis tool to perform in-depth analysis on the execution plan of the SQL statement using statistical information and cost estimation models, and generates an execution plan report containing key information such as estimated cost, index usage, and data access path;

[0087] Among them, the execution plan analysis tool is used to analyze the database index usage and the execution plan information of the table connection sequence.

[0088] The SQL optimization module in this embodiment dynamically adjusts the structure of SQL statements according to the bottleneck points in the execution plan by using rule-based optimization (RBO) algorithm, cost-based optimization (CBO) algorithm and machine learning-assisted optimization strategy; wherein, the dynamic adjustment of the structure of SQL statements includes optimizing the query order to reduce the amount of data scanning, adjusting the connection conditions to utilize more effective indexes, and selecting the best index according to data distribution characteristics; and integrating the query optimization component to automatically optimize the SQL query path by analyzing the historical query pattern and data access efficiency, and supporting multi-dimensional query performance evaluation, while providing optimization suggestions for index creation and partitioning strategies.

[0089] The query execution module in this embodiment uses database connection pool and query cache technology to reduce the overhead of database connection and repeated query, and supports streaming processing or paging loading of result sets to reduce memory pressure and improve user experience.

[0090] Embodiment 3:

[0091] This embodiment also provides an electronic device, including: a memory and at least one processor;

[0092] Wherein, the memory stores computer-executable instructions;

[0093] The at least one processor executes the computer-executable instructions stored in the memory, so that the at least one processor executes the dynamic SQL mapping optimization method according to any one of the present inventions.

[0094] The processor may be a central processing unit (CPU), or 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. The processor may be a microprocessor or any conventional processor, etc.

[0095] The memory can be used to store computer programs and / or modules. The processor realizes various functions of the electronic device by running or executing the computer programs and / or modules stored in the memory, and calling the data stored in the memory. The memory can mainly include a program storage area and a data storage area, wherein the program storage area can store an operating system, at least one application required for a function, etc.; the data storage area can store data created according to the use of the terminal, etc. In addition, the memory can also include a high-speed random access memory, and can also include a non-volatile memory, such as a hard disk, a memory, a plug-in hard disk, a smart memory card (SMC), a secure digital (SD) card, a flash memory card, at least one disk storage period, a flash memory device, or other volatile solid-state storage devices.

[0096] Embodiment 4:

[0097] This embodiment also provides a computer-readable storage medium, in which a plurality of instructions are stored, and the instructions are loaded by a processor, so that the processor executes the dynamic SQL mapping optimization method in any embodiment of the present invention. Specifically, a system or device equipped with a storage medium can be provided, on which a software program code that implements the functions of any of the above embodiments is stored, and a computer (or CPU or MPU) of the system or device reads and executes the program code stored in the storage medium.

[0098] In this case, the program code itself read from the storage medium can realize the function of any one of the above-mentioned embodiments, and thus the program code and the storage medium storing the program code constitute a part of the present invention.

[0099] The storage medium embodiments for providing the program code include a floppy disk, a hard disk, a magneto-optical disk, an optical disk (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RAM, DVD-RW, DVD+RW), a magnetic tape, a non-volatile memory card, and a ROM. Alternatively, the program code can be downloaded from a server computer by a communication network.

[0100] In addition, it should be clear that the functions of any of the above embodiments can be implemented not only by executing the program code read by the computer, but also by enabling an operating system operating on the computer to complete part or all of the actual operations based on instructions from the program code.

[0101] In addition, it can be understood that the program code read from the storage medium is written to a memory provided in an expansion board inserted into the computer or written to a memory provided in an expansion unit connected to the computer, and then based on the instructions of the program code, a CPU installed on the expansion board or the expansion unit is enabled to perform part or all of the actual operations, thereby realizing the functions of any of the above-mentioned embodiments.

[0102] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A dynamic SQL mapping optimization method, characterized in that: The method is as follows: Receive data query requests, parse the data query requests, and obtain query parameters and conditions; Build an initial SQL query template based on query parameters and conditions; Call the database's built-in query optimizer or an external execution plan analysis tool to perform performance analysis on the initial SQL query template; Dynamically adjust the SQL statement structure based on the execution plan analysis results; Execute the optimized SQL statement and return the query result.

2. The dynamic SQL mapping optimization method according to claim 1, characterized in that: The initial SQL query template is as follows: Use predefined SQL template library and template engine to dynamically build initial SQL query template; Among them, the SQL template library automatically generates the optimal SQL query statement according to different query conditions; The template engine is used to fill the parsed parameters and conditions into the corresponding template to form an executable SQL statement.

3. The dynamic SQL mapping optimization method according to claim 1 or 2, characterized in that: The performance analysis of the initial SQL query template is as follows: The database's built-in query optimizer or external execution plan analysis tool uses statistical information and cost estimation models to conduct in-depth analysis of the SQL statement execution plan and generate an execution plan report containing key information such as estimated cost, index usage, and data access path. Among them, the execution plan analysis tool is used to analyze the database index usage and the execution plan information of the table connection sequence.

4. The dynamic SQL mapping optimization method according to claim 3, characterized in that: Dynamically adjust the SQL statement structure according to the execution plan analysis results as follows: According to the bottleneck points in the execution plan, the rule-based optimization algorithm, cost-based optimization algorithm and machine learning-assisted optimization strategy are used to dynamically adjust the structure of SQL statements. The dynamic adjustment of the structure of SQL statements includes optimizing the query order to reduce the amount of data scanning, adjusting the connection conditions to use more effective indexes, and selecting the best index according to the data distribution characteristics. The integrated query optimization component automatically optimizes the SQL query path by analyzing historical query patterns and data access efficiency, supports multi-dimensional query performance evaluation, and provides optimization suggestions for index creation and partitioning strategies.

5. The dynamic SQL mapping optimization method according to claim 4, characterized in that: In the process of executing optimized SQL statements and returning query results, database connection pool and query cache technology are used to reduce database connection overhead and repeated query overhead.

6. A dynamic SQL mapping optimization system, characterized in that: The system includes: The parameter parsing module is used to receive the data query request sent by the user, parse the data query request, and obtain the query parameters and conditions; Initialize SQL template building module, used to build initial SQL query template according to query parameters and conditions; The execution plan analysis module is used to call the database's built-in query optimizer or an external execution plan analysis tool to perform performance analysis on the initial SQL query template; SQL optimization module, used to dynamically adjust the SQL statement structure according to the execution plan analysis results; The query execution module is used to execute the optimized SQL statement and return the query results.

7. The dynamic SQL mapping optimization system according to claim 6, characterized in that: The initial SQL query template building module uses the predefined SQL template library and template engine to dynamically build the initial SQL query template; Among them, the SQL template library automatically generates the optimal SQL query statement according to different query conditions; The template engine is used to fill the parsed parameters and conditions into the corresponding template to form an executable SQL statement; The execution plan analysis module uses the built-in query optimizer of the database or external execution plan analysis tools to conduct in-depth analysis of the execution plan of SQL statements using statistical information and cost estimation models, and generates an execution plan report containing key information such as estimated cost, index usage, and data access path. Among them, the execution plan analysis tool is used to analyze the database index usage and the execution plan information of the table connection sequence.

8. The dynamic SQL mapping optimization system according to claim 6 or 7, characterized in that: The SQL optimization module dynamically adjusts the structure of SQL statements based on the bottleneck points in the execution plan using rule-based optimization algorithms, cost-based optimization algorithms, and machine learning-assisted optimization strategies. The dynamic adjustment of the SQL statement structure includes optimizing the query order to reduce the amount of data scanning, adjusting the connection conditions to use more effective indexes, and selecting the best index based on data distribution characteristics. The query optimization component is integrated to automatically optimize the SQL query path by analyzing historical query patterns and data access efficiency, and supports multi-dimensional query performance evaluation, while providing optimization suggestions for index creation and partitioning strategies. The query execution module uses database connection pool and query cache technology to reduce the overhead of database connections and repeated queries, while supporting streaming processing or paging loading of result sets.

9. An electronic device, characterized in that: include: memory and at least one processor; Wherein, the memory stores computer-executable instructions; The at least one processor executes the computer-executable instructions stored in the memory, so that the at least one processor executes the dynamic SQL mapping optimization method according to any one of claims 1 to 5.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, and when the processor executes the computer-executable instructions, the dynamic SQL mapping optimization method according to any one of claims 1 to 5 is implemented.

Images