CN118410068B - Database SQL query optimization method, terminal and storage medium - Google Patents

Abstract

The application provides a database SQL query optimization method, a terminal and a storage medium, and relates to the field of databases. The method comprises the following steps: acquiring an SQL query statement to be processed, and converting the SQL query statement into a corresponding abstract syntax tree; acquiring the latest metadata information based on the metadata query interface; generating an abstract syntax tree after metadata enhancement according to the latest metadata information and the abstract syntax tree; based on the abstract syntax tree with enhanced metadata, query dynamic reorganization and optimization are carried out to obtain a reorganized abstract syntax tree; based on the recombined abstract syntax tree, SQL query sentences are executed. The application can intelligently and dynamically adjust and optimize SQL query sentences based on the latest metadata information, can adapt to continuously changing data environments, and can further improve query performance and processing efficiency.

Description

Database SQL query optimization method, terminal and storage medium

Technical Field

The present invention relates to the field of database technology, and in particular to a database SQL query optimization method, a terminal and a storage medium.

Background Art

With the development of database technology and the surge in data volume, database query optimization has become a key technology in database management systems. Traditional database query optimization relies on static, pre-defined rules and statistical information, which often performs poorly in situations where data distribution changes and query patterns are diverse. Especially when faced with big data and high-concurrency query requirements, this static optimization strategy cannot fully adapt to the rapid changes in the data environment, resulting in reduced query performance and processing efficiency.

Summary of the invention

The embodiments of the present invention provide a database SQL query optimization method, a terminal and a storage medium to solve the problem that the existing methods cannot fully adapt to the rapid changes in the data environment, resulting in decreased query performance and processing efficiency.

In a first aspect, an embodiment of the present invention provides a database SQL query optimization method, comprising:

Obtain the SQL query statement to be processed, and convert the SQL query statement into a corresponding abstract syntax tree;

Based on the metadata query interface, obtain the latest metadata information;

Generate an abstract syntax tree with metadata enhancement according to the latest metadata information and abstract syntax tree;

Based on the abstract syntax tree enhanced by metadata, the query is dynamically reorganized and optimized to obtain a reorganized abstract syntax tree;

Execute SQL query statements based on the reorganized abstract syntax tree.

In a possible implementation, the metadata query interface is an interface provided by the metadata analysis component for querying the latest metadata information;

The metadata analysis component monitors database changes in event subscription mode through metadata listeners, and records database change information in a differential manner when monitoring database change information. It also adopts delayed loading and lazy update strategies to load and update database change information when the database change information is actually applied to SQL query optimization.

In a possible implementation, the metadata analysis component dynamically determines whether to retain or eliminate metadata in the memory cache according to the access frequency and modification frequency of the metadata, and ensures the atomicity and consistency of metadata changes through transaction log analysis or a database write-ahead log;

Among them, the metadata analysis component retains metadata with an access frequency higher than a first preset access frequency and/or a modification frequency lower than the first preset modification frequency in the memory cache, and eliminates metadata with an access frequency lower than a second preset access frequency and/or a modification frequency higher than the second preset modification frequency; the first preset access frequency is greater than or equal to the second preset access frequency, and the first preset modification frequency is less than or equal to the second preset modification frequency.

In a possible implementation, based on the abstract syntax tree enhanced by the metadata, the query is dynamically reorganized and optimized to obtain a reorganized abstract syntax tree, including:

If a subquery or view exists, the target table on which the subquery or view depends is determined according to the latest metadata information, and the subquery or view is expanded when the access frequency of the target table is higher than a third preset access frequency, the index of the target table meets the preset efficient index condition, the data volume of the target table is less than the preset data volume, the data change frequency of the target table is lower than the first preset change frequency, or the result set size after executing the subquery or view is less than the preset size;

If there is an aggregation operation, then based on the statistical information in the latest metadata information, unnecessary data is filtered out in advance before the aggregation operation to reduce the amount of data input for the aggregation operation;

If there is a sorting operation, determine whether there is an index for the sequence to be sorted, the amount of data in the sequence to be sorted, and the data distribution information of the sequence to be sorted according to the latest metadata information, and determine the sorting strategy according to whether there is an index for the sequence to be sorted, the amount of data in the sequence to be sorted, and the data distribution information of the sequence to be sorted;

If the database supports column storage, adjust the storage method of the data in the database according to the latest metadata information.

In a possible implementation, based on the abstract syntax tree enhanced by the metadata, query dynamic reorganization and optimization are performed to obtain a reorganized abstract syntax tree, which also includes:

If there is a WHERE clause, reorder the predicates in descending order of index selectivity in the latest metadata information;

If there are multiple table connections, adjust the order and strategy of the multiple table connections based on the table size information, index coverage information and/or distribution characteristics of the data in the table in the latest metadata information;

Based on the latest metadata information, determine whether to use index scanning during the query process, and for composite indexes, analyze the matching degree between the columns in the query conditions and the index columns in the composite index, and determine the most effective index utilization strategy in the composite index;

According to the partition information in the latest metadata information, the query operation is performed on the partitions related to the query condition.

In a possible implementation, executing a SQL query statement based on the reorganized abstract syntax tree includes:

Generate multiple execution plans based on the reorganized abstract syntax tree;

Select the target execution plan with the lowest execution cost from multiple execution plans, and execute the SQL query statement according to the target execution plan;

Get real-time performance indicators during the execution of SQL query statements;

If the real-time performance indicators do not meet the expected targets, a new execution plan is generated, and the SQL query statement is executed according to the new execution plan;

Get the actual execution result of the SQL query statement and update the metadata information according to the actual execution result.

In a possible implementation, after executing the SQL query statement based on the reorganized abstract syntax tree, the database SQL query optimization method further includes:

Based on the query log and the latest metadata information, determine the execution frequency of SQL query statements and the data change frequency of the execution results of SQL query statements;

If the execution frequency of the SQL query statement is higher than the preset execution frequency and/or the data change frequency of the execution result of the SQL query statement is lower than the second preset change frequency, the execution result of the SQL query statement is stored in the query cache.

In a possible implementation, the validity period of the query cache is associated with the data change pattern of the metadata.

In a second aspect, an embodiment of the present invention provides a database SQL query optimization device, comprising:

A first acquisition module is used to acquire the SQL query statement to be processed and convert the SQL query statement into a corresponding abstract syntax tree;

The second acquisition module is used to acquire the latest metadata information based on the metadata query interface;

A generation module, used for generating an abstract syntax tree after metadata enhancement according to the latest metadata information and the abstract syntax tree;

A query optimization module is used to dynamically reorganize and optimize queries based on the abstract syntax tree enhanced by metadata to obtain a reorganized abstract syntax tree;

The execution module is used to execute SQL query statements based on the reorganized abstract syntax tree.

In a third aspect, an embodiment of the present invention provides a terminal, comprising a memory and a processor, the memory being used to store a computer program, the processor being used to call and run the computer program stored in the memory, and executing the database SQL query optimization method as described in the first aspect or any possible implementation of the first aspect.

In a fourth aspect, an embodiment of the present invention provides a computer-readable storage medium, which stores a computer program. When the computer program is executed by a processor, it implements the steps of the database SQL query optimization method as described in the first aspect or any possible implementation method of the first aspect.

The embodiment of the present invention provides a database SQL query optimization method, a terminal and a storage medium. When processing an SQL query statement to be processed, the method obtains the latest metadata information of the database through a metadata query interface, generates a metadata-enhanced abstract syntax tree corresponding to the SQL query statement through the latest metadata information, performs dynamic query reorganization and optimization through the metadata-enhanced abstract syntax tree, intelligently and dynamically adjusts and optimizes the SQL query statement based on the latest metadata information, adapts to a constantly changing data environment, and further improves query performance and processing efficiency.

BRIEF DESCRIPTION OF THE DRAWINGS

In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

1 is a schematic diagram of a flow chart of a database SQL query optimization method provided by an embodiment of the present invention;

2 is a schematic diagram of the structure of a database SQL query optimization device provided by an embodiment of the present invention;

FIG. 3 is a schematic diagram of a terminal provided by an embodiment of the present invention.

DETAILED DESCRIPTION

In the following description, specific details such as specific system structures, technologies, etc. are provided for the purpose of illustration rather than limitation, so as to provide a thorough understanding of the embodiments of the present invention. However, it should be clear to those skilled in the art that the present invention may be implemented in other embodiments without these specific details. In other cases, detailed descriptions of well-known systems, devices, circuits, and methods are omitted to prevent unnecessary details from obstructing the description of the present invention.

In order to make the purpose, technical solutions and advantages of the present invention more clear, specific embodiments will be described below in conjunction with the accompanying drawings.

Referring to Fig. 1, it shows a flowchart of the implementation of the database SQL query optimization method provided by an embodiment of the present invention. The execution subject of the above database SQL query optimization method may be a terminal.

The above database SQL query optimization method may include:

In S101, a SQL (Structured Query Language) query statement to be processed is obtained, and the SQL query statement is converted into a corresponding Abstract Syntax Tree (AST).

After the SQL query statement to be processed is obtained, it is preprocessed and parsed through the parser inside the database, and converted into an internal AST that the database can understand.

S101 to S105 may be executed by a query optimizer in the database.

In S102, the latest metadata information is obtained based on the metadata query interface.

Among them, the latest metadata information can also be understood as real-time metadata information, which can be used to enrich the parsed query representation, namely AST, to provide necessary data support for subsequent query reorganization and optimization.

The metadata analysis component of the database will provide a metadata query interface to the outside world, through which the latest metadata information of the database can be obtained.

In S103, an abstract syntax tree with metadata enhancement is generated according to the latest metadata information and the abstract syntax tree.

The metadata-enhanced abstract syntax tree is an abstract syntax tree that incorporates the latest metadata information. The latest metadata information can support the query optimizer to reorganize and optimize SQL query statements more intelligently.

In S104, based on the abstract syntax tree enhanced by the metadata, the query is dynamically reorganized and optimized to obtain a reorganized abstract syntax tree.

This application can dynamically reorganize (or rewrite) and optimize SQL queries based on the metadata-enhanced abstract syntax tree, using the latest metadata information (such as index usage and statistical information, etc.) integrated into the metadata-enhanced abstract syntax tree, so as to obtain a reorganized abstract syntax tree.

Compared with the abstract syntax tree before reorganization, the reorganized abstract syntax tree has higher execution efficiency and better query performance.

In S105 , the SQL query statement is executed based on the reorganized abstract syntax tree.

When processing an SQL query statement to be processed, the embodiment of the present application obtains the latest metadata information of the database through a metadata query interface, and generates a metadata-enhanced abstract syntax tree corresponding to the SQL query statement through the latest metadata information. The query is dynamically reorganized and optimized through the metadata-enhanced abstract syntax tree, and the SQL query statement can be intelligently and dynamically adjusted and optimized based on the latest metadata information, so as to adapt to the ever-changing data environment, thereby improving query performance and processing efficiency.

In some embodiments, the metadata query interface is an interface provided by the metadata analysis component for querying the latest metadata information;

The metadata analysis component monitors database changes in event subscription mode through metadata listeners, and records database change information in a differential manner when monitoring database change information. It also adopts delayed loading and lazy update strategies to load and update database change information when the database change information is actually applied to SQL query optimization.

The metadata analysis component is a component in the database that analyzes the metadata in the database. It provides a metadata query interface that allows the query optimizer and other components in the database to access the latest metadata information. The metadata query interface can respond to metadata query requests and return the latest, incrementally updated metadata information.

The metadata listener is embedded in the database management system. Through the metadata listener, using the event subscription mode, you can monitor the changes in the database, including changes in the database schema, which can include the creation, update or deletion of tables, views, indexes, stored procedures and functions, etc.

In actual applications, when a DDL (Data Definition Language) statement is executed, the metadata listener captures the change event and parses the change event content to determine the nature of the change. For example, if it is an ALTER TABLE operation, the metadata listener will identify which columns are added, deleted, or the data type is changed, etc.

After capturing the database change information, the metadata listener can record the database change information in the form of differences to complete the synchronization process. The synchronization process only records the database change information instead of replacing all existing metadata, so that only the changed part of the database can be synchronized, improving synchronization efficiency.

For metadata updates, a delayed loading and lazy update strategy can be adopted, and the database change information is not updated immediately to reduce the impact on database performance. The database change information can be loaded and updated only when the database change information is actually used as metadata for SQL query optimization. Exemplarily, when the query optimizer accesses the latest metadata information through the metadata query interface, the database change information can be loaded and updated so that the query optimizer can obtain the latest metadata information.

In some embodiments, the metadata analysis component dynamically determines whether to retain or eliminate metadata in the memory cache based on the access frequency and modification frequency of the metadata, and ensures the atomicity and consistency of metadata changes through transaction log analysis or database write-ahead logging (WAL);

Among them, the metadata analysis component retains metadata with an access frequency higher than a first preset access frequency and/or a modification frequency lower than the first preset modification frequency in the memory cache, and eliminates metadata with an access frequency lower than a second preset access frequency and/or a modification frequency higher than the second preset modification frequency; the first preset access frequency is greater than or equal to the second preset access frequency, and the first preset modification frequency is less than or equal to the second preset modification frequency.

In this embodiment, the efficient metadata cache mechanism is to retain metadata with high access frequency (important) and/or low modification frequency (stable) by comprehensively considering access frequency and modification frequency, and eliminate metadata with low access frequency (unimportant or infrequently used) and/or high modification frequency (unstable, frequently changing). The metadata cache mechanism helps to optimize the utilization efficiency of the cache and ensure the optimization of database performance. In addition, this cache mechanism can not only reduce the demand for storage, but also improve the access speed.

If some metadata is frequently accessed, for example, it is often used by the query optimizer to optimize queries, it indicates that this metadata is very important for database operations. Therefore, this metadata should be retained in the cache. If some metadata does not change frequently (i.e., the modification frequency is low), their retention value increases because they provide stable and long-term valid information and do not need to be updated frequently.

On the other hand, if some metadata is rarely accessed, it may not be important for the current database operation and can be considered to be eliminated from the cache to free up resources. If some metadata changes frequently, its value in the cache may be low because the stored information will become outdated quickly. In this case, frequently updated metadata may not be suitable for long-term retention in the cache.

Exemplarily, in this embodiment, metadata with an access frequency higher than a first preset access frequency may be referred to as metadata with a high access frequency, metadata with a modification frequency lower than the first preset modification frequency may be referred to as metadata with a low modification frequency, metadata with an access frequency lower than a second preset access frequency may be referred to as metadata with a low access frequency, and metadata with a modification frequency higher than the second preset modification frequency may be referred to as metadata with a high modification frequency.

The first preset access frequency, the second preset access frequency, the first preset modification frequency, and the second preset modification frequency may vary according to the actual workload, scale, and specific requirements of the database. In actual applications, these parameters are configurable to adapt to different environments and performance requirements.

Access frequency can be understood as the number of accesses within a certain period of time. Within a certain time window (for example, every day or every hour), if the number of times metadata is accessed exceeds a certain threshold, that is, the access frequency is higher than the first preset access frequency, it can be considered as metadata with a high access frequency. In contrast to high access frequency, low access frequency may mean that within the same time window, the number of accesses is lower than a certain threshold, that is, the access frequency is lower than the second preset access frequency. For example, if a certain metadata is accessed more than 100 times per hour, it can be considered as metadata with a high access frequency. If a certain metadata is accessed less than 10 times per day, it can be considered as metadata with a low access frequency, and so on.

Modification frequency can be understood as the number of modifications within a certain period of time. Within a certain time window (for example, weekly, monthly or quarterly), the number of times the metadata is modified exceeds a certain threshold, that is, the modification frequency is higher than the second preset modification frequency, which can be considered as metadata with a high modification frequency. In contrast to high modification frequency, low modification frequency may mean that within the same time window, the number of modifications is lower than a certain threshold, or within a longer time window, modifications rarely occur, that is, the modification frequency is lower than the first preset modification frequency. For example, if the metadata of the table structure is modified 3 times or more per week, it can be considered as a high modification frequency. If the metadata of the index is only changed once within six months, it can be considered as a low modification frequency, and so on.

To ensure data consistency, the metadata analysis component implements transaction log analysis or uses the database's write-ahead log (WAL) to ensure the atomicity and consistency of metadata changes and ensure that metadata updates do not interrupt the ongoing query optimization process.

The metadata analysis component is the foundation of the entire database management system, which can provide real-time and accurate metadata information and provide important data support for subsequent query optimization. By effectively tracking and synchronizing changes in the database schema, the embodiment of the present application can quickly respond to changes in the data structure and ensure that the query optimization strategy is always based on the latest database status, which can not only improve query efficiency, but also reduce the potential risk of errors caused by outdated metadata.

In some possible implementations, the latest metadata information may include the latest table information, index information, and statistical information, etc. Specifically, it may include the latest table structure information, table size information (such as the number of rows in the table or the size of the physical space occupied by the table), table statistical information, table relationship information, index coverage information (such as the existence of the index, the type of index, the attributes of the index, and the index selectivity), data distribution characteristics (such as whether the data is evenly distributed or concentrated, the partitioning of the data, whether there are frequently occurring values in the data, and whether the data has a long-tail distribution), index update frequency, index efficiency, partition information (such as partition range, partition size, and partition key), table and column access frequency, table and column data change frequency, column value distribution, column value frequency, column value cardinality, data timestamp or date, column access count, column access mode, and query mode, etc., or one or more of the following information.

It should be noted that the above only provides some examples of metadata information, and the metadata information may also include other related metadata, which is not specifically limited here.

In some embodiments, the above S104 may include:

If a subquery or view exists, the target table on which the subquery or view depends is determined according to the latest metadata information, and the subquery or view is expanded when the access frequency of the target table is higher than a third preset access frequency, the index of the target table meets the preset efficient index condition, the data volume of the target table is less than the preset data volume, the data change frequency of the target table is lower than the first preset change frequency, or the result set size after executing the subquery or view is less than the preset size;

If there is an aggregation operation, then based on the statistical information in the latest metadata information, unnecessary data is filtered out in advance before the aggregation operation to reduce the amount of data input for the aggregation operation;

If there is a sorting operation, determine whether there is an index for the sequence to be sorted, the amount of data in the sequence to be sorted, and the data distribution information of the sequence to be sorted according to the latest metadata information, and determine the sorting strategy according to whether there is an index for the sequence to be sorted, the amount of data in the sequence to be sorted, and the data distribution information of the sequence to be sorted;

If the database supports column storage, adjust the storage method of the data in the database according to the latest metadata information.

In this embodiment, if there are subqueries or views in the metadata-enhanced AST, that is, a complex query containing subqueries or views, determining whether to "expand" (inline) the subquery or view is based on the latest metadata information, which can simplify the query and improve efficiency.

Based on the latest metadata information, the target table and/or target column that the subquery or view depends on is determined, and the access frequency of the target table and/or target column is determined. If the target table and/or target column is frequently accessed in the database and has a high access frequency (i.e., the access frequency is higher than the third preset access frequency), then expanding the subquery or view can reduce repeated data access and processing, thereby improving query efficiency. The third preset access frequency can be set according to actual needs and is not specifically limited here.

Determine whether the target table and/or target column has an index based on the latest metadata information. If the target table and/or target column has an index and it is an efficient index (i.e., it meets the preset efficient index condition), then expanding the subquery or view can improve the utilization of the index and accelerate data access. Among them, the preset efficient index condition may include that the index selectivity is higher than the preset selectivity value, and the index update frequency is lower than the preset update frequency. The preset selectivity value and the preset update frequency can be set according to actual needs and are not specifically limited here.

The data volume of the target table can be determined based on the latest metadata information, and the data volume of the target table can be represented by the size information of the target table. If the data volume of the target table is small (that is, the data volume of the target table is less than the preset data volume), expanding the subquery or view can directly reduce the amount of data to be processed and improve query efficiency. The preset data volume can be set according to actual needs and is not specifically limited here.

Based on the latest metadata information, the data change frequency of the target table can be determined. Tables with stable data are suitable for expansion operations because the expanded query does not need to be updated frequently, which is conducive to maintaining query performance. If the data of the table changes frequently, the expansion operation may not be the best choice because it may lead to frequent recalculation and updating. Therefore, if the data change frequency of the target table is lower than the first preset change frequency, that is, the data change frequency of the target table is low, the subquery or view can be expanded. Among them, the first preset change frequency can be set according to actual usage requirements, and no specific restrictions are made here.

Based on the size information of the target table and the query conditions, the size of the result set after executing the subquery or view can be estimated, and the subquery or view with a smaller result set is more suitable for expansion. Therefore, when the size of the result set after executing the subquery or view is less than the preset size, that is, when the result set size is small, the subquery or view can be expanded. The preset size can be set according to actual usage requirements and is not specifically limited here.

In summary, the expansion operation can be performed on subqueries or views that can directly reduce the amount of data processing and improve index utilization without introducing new complexity or unnecessary calculations after expansion.

If there is an aggregation operation in the AST after metadata enhancement, the aggregation operation can be optimized according to the statistical information in the latest metadata information, and unnecessary data can be filtered out in advance before performing the aggregation operation, thereby reducing the amount of data input for the aggregation operation.

For example, the characteristics of the data can be determined based on the distribution of column values, the frequency of column values, and the cardinality of column values (the number of different column values) in the statistical information, and whether to filter out the data can be determined based on the characteristics of the data. For example, assuming that a certain value in a certain column is very rare, that is, the frequency and cardinality of the value are relatively low, the value may be filtered out before aggregation; based on the statistical information, high-frequency values or extreme values are identified, and according to the purpose of aggregation, it is determined whether to include or filter out these values; based on the timestamp or date field in the statistical information, the timeliness of the data is determined, and based on the timeliness of the data, old data, that is, data that has exceeded the timeliness, is filtered out; and so on.

If there is a sorting operation in the metadata-enhanced AST, the sorting operation can be optimized based on the index, data volume, and data distribution information of the column to be sorted.

Exemplarily, the above-mentioned determining the sorting strategy according to whether there is an index in the sequence to be sorted, the amount of data in the sequence to be sorted, and the data distribution information of the sequence to be sorted may include:

If the sequence to be sorted has an index, and the index type is a preset type suitable for sorting operations (such as a B-tree index), the sorting operation can be performed directly based on the index of the sequence to be sorted without an additional sorting step; otherwise, the most suitable sorting algorithm is determined based on the data volume and/or data distribution information of the sequence to be sorted. The preset type suitable for sorting operations can be used as the preset type suitable for sorting operations based on the index type.

Because indexes usually store data in some order (such as ascending or descending), sorting operations can be performed directly on the index if the index contains all the required fields.

The data distribution information of the sequence to be sorted may include whether the data is evenly distributed, whether there are frequently occurring values, or whether there is a long-tail distribution, etc.

Exemplarily, when the amount of data in the sequence to be sorted is large, external sorting may be used, and when the amount of data in the sequence to be sorted is small, quick sorting may be used, and so on.

The above method can effectively utilize the latest metadata information to optimize aggregation and sorting operations, thereby improving query performance and response speed.

If the database supports columnar storage, you can adjust the data storage and access methods based on the columns involved in the query and the access mode of the columns.

Determine which columns are frequently accessed, which columns are frequently accessed together, and which columns are rarely queried or accessed based on the statistics in the latest metadata information (such as the number of accesses/frequency or access pattern of each column). For column-based storage databases, columns that are frequently accessed together can be stored physically close together, or optimizations can be made in the storage structure to speed up joint access to these columns. For columns that are frequently queried or accessed, their storage format can be optimized, such as compression methods, to improve reading efficiency. For example, for columns with low access frequency (cold data), you can consider using a higher compression ratio to save storage space; for columns with high access frequency (hot data), use a lower compression ratio or a more optimized storage format to speed up reading operations. You can also optimize related queries based on access patterns, partitioning by time or a common column.

Through the above steps, the storage and access methods of data in the column storage database can be effectively adjusted according to the metadata information. Especially for wide tables (tables with a large number of columns), such optimization can significantly improve the performance of analytical queries.

In some embodiments, the above S104 may further include:

If there is a WHERE clause, reorder the predicates in descending order of index selectivity in the latest metadata information;

If there are multiple table connections, adjust the order and strategy of the multiple table connections based on the table size information, index coverage information and/or distribution characteristics of the data in the table in the latest metadata information;

Based on the latest metadata information, determine whether to use index scanning during the query process, and for composite indexes, analyze the matching degree between the columns in the query conditions and the index columns in the composite index, and determine the most effective index utilization strategy in the composite index;

According to the partition information in the latest metadata information, the query operation is performed on the partitions related to the query condition.

If there is a WHERE clause in the metadata-enhanced AST, the query optimizer will reorder the predicates and give priority to conditions based on highly selective indexes to reduce the amount of data scanning.

If there are multi-table connections in the metadata-enhanced AST, that is, multi-table JOINs are involved, the order and strategy of the multi-table connections can be adjusted according to the size information, index coverage information and/or distribution characteristics of the data in the tables involved in the multi-table connection to improve query efficiency.

The size information of a table includes information such as the number of rows in the table or the size of the physical space occupied by the table. Adjusting the order and strategy of multi-table joins based on the size information of the table may include: selecting the smallest table as the driving table for multi-table join operations based on the size information of the tables involved. Adjusting the order and strategy of multi-table joins based on the size information of the tables can effectively reduce the number of searches and comparisons on large tables, especially in Nested Loops Join, where the effect is more obvious.

Index coverage information may include information such as the existence of an index (whether an index exists), the type of index (e.g., primary key index or non-unique index, etc.), and index selectivity (i.e., the distribution of different values on the index key). Adjusting the multi-table join strategy based on index coverage information may include: using effective indexes to speed up multi-table join operations based on the existence of indexes, the type of indexes, and the index selectivity. For example, if there is a highly selective index on the JOIN condition column, an Indexed Nested Loops Join operation may be used.

The distribution characteristics of the data in the table may include information such as whether the data distribution is uniform or concentrated, and the partitioning of the data. Adjusting the multi-table connection strategy according to the distribution characteristics of the data in the table may include: selecting the appropriate JOIN type based on different data distribution characteristics to perform multi-table connection operations. Exemplarily, for a table with uneven data distribution, select HashJoin to complete the multi-table connection operation; for two or more tables with uniform data distribution and similar sizes, select MergeJoin to complete the multi-table connection operation. Similar size means that the absolute value of the size difference is less than the preset size difference, and the preset size difference is a small value that can be set according to actual usage requirements.

This embodiment can also optimize the index selection based on the latest metadata information. The above-mentioned determination of whether to use index scanning in the query process based on the latest metadata information may include: determining whether to use index scanning in the query process based on the index information in the latest metadata information (for example, index selectivity and index update frequency, etc.). Exemplarily, if the index of a column is efficient, the query will be reorganized to give priority to using the index. Among them, an index with high index selectivity and low update frequency is an efficient index, an index with an index selectivity higher than a preset selectivity value is determined to have high index selectivity, and an index update frequency lower than a preset update frequency is determined to have low index update frequency. That is, an index with an index selectivity higher than a preset selectivity value and an index update frequency lower than a preset update frequency is an efficient index.

For a composite index, the index utilization strategy with the highest degree of matching between the columns in the query condition and the index columns in the composite index is determined as the most effective index utilization strategy for the composite index. Among them, in the index utilization strategy, the more the number of index columns matching the columns in the query condition is, the more the order of the columns matching the columns in the query condition is in the front, and the higher the selectivity of the index columns matching the columns in the query condition is, the higher the degree of matching between the columns in the query condition and the index columns in the composite index is.

Specifically, you can first analyze the structure of the composite index to determine the index columns and the order of the index columns contained in the composite index; then, check whether there are corresponding index columns for each column in the query condition, and consider the order of the corresponding index columns in the composite index. In the composite index, the front columns are heavier than the back columns. If the query condition only uses the first column in the composite index, the efficiency of the index is usually the highest; then, analyze the selectivity of the corresponding index column in the query condition. If the corresponding index column in the query condition has high selectivity, it will be more efficient to use the index.

If all columns in the query conditions are included in the composite index and the index covers all the data required for the query, the index in this case is called a "covering index", which can greatly improve query efficiency.

This embodiment can also optimize the query of the partition table according to the partition information in the latest metadata information. If the data is stored in the partition table, the query reorganization can consider scanning only the relevant partitions instead of the entire table, thereby improving the query efficiency, which can be achieved by analyzing the conditions related to the partition key in the WHERE clause.

The partition information may include information such as partition range, partition size, and partition key. The partition information may be used to optimize the data scan range. Specifically, when the query condition is related to a partition key, only the partition containing the relevant data may be scanned instead of the entire table scan. For example, if a table is partitioned by date and the query condition specifies a specific date range, only the partition containing these dates may be scanned.

By accessing only the partitions containing query-related data, the amount of data that needs to be processed is reduced, thereby speeding up queries and improving overall query efficiency. Accessing fewer partitions means fewer disk I/O operations, which is especially important in large database systems because I/O operations are often performance bottlenecks. Partition positioning can reduce disk I/O operations. For some specific types of queries (such as range queries, aggregation queries, etc.), by locating specific partitions, the size of the data set that needs to be processed can be significantly reduced, optimizing specific types of queries.

In some embodiments, the above S105 may include:

Generate multiple execution plans based on the reorganized abstract syntax tree;

Select the target execution plan with the lowest execution cost from multiple execution plans, and execute the SQL query statement according to the target execution plan;

Get real-time performance indicators during the execution of SQL query statements;

If the real-time performance indicators do not meet the expected targets, a new execution plan is generated, and the SQL query statement is executed according to the new execution plan;

Get the actual execution result of the SQL query statement and update the metadata information according to the actual execution result.

Based on the reorganized abstract syntax tree, the query optimizer can generate multiple potential execution plans, each with different data access paths and resource usage estimates. Using the latest metadata information, such as the number of rows, index selectivity, etc., the query optimizer can evaluate the expected cost of each execution plan, select the execution plan with the lowest cost as the target execution plan, and submit the target execution plan to the database execution engine for processing.

During the execution of the SQL query statement, relevant real-time performance indicators can be obtained, such as at least one of the indicators such as execution time, CPU usage, memory consumption and number of I/O operations. Thresholds for each real-time performance indicator can be set based on historical performance data, expected resource usage or experience values, etc. The real-time performance indicator is compared with the corresponding threshold. If the real-time performance indicator does not meet the corresponding threshold, it is determined that the real-time performance indicator does not meet the expected target and deviates from expectations. At this time, the query optimizer can generate a new execution plan, for example, it can change the JOIN type, select different indexes or change the order of operations, etc. According to the new execution plan, the current SQL query statement is executed. The new execution plan can also be used to execute similar queries in the future.

When there are at least two real-time performance indicators, and all the real-time performance indicators do not meet the corresponding thresholds, it is determined that the real-time performance indicators do not meet the expected target.

This embodiment can also update the statistical information in the metadata information according to the actual execution results of the SQL query statement, for example, it can include the number of updated rows, index efficiency or data distribution, etc., which can help the query optimizer to make more accurate execution plan predictions in the future.

In some embodiments, after S105, the database SQL query optimization method may further include:

Based on the query log and the latest metadata information, determine the execution frequency of SQL query statements and the data change frequency of the execution results of SQL query statements;

If the execution frequency of the SQL query statement is higher than the preset execution frequency and/or the data change frequency of the execution result of the SQL query statement is lower than the second preset change frequency, the execution result of the SQL query statement is stored in the query cache.

This embodiment can identify frequently executed queries and/or queries with relatively stable result sets by analyzing statistical information in query logs and the latest metadata information. These queries can significantly improve the speed of data retrieval. Therefore, the query results (i.e., execution results) of these queries can be saved in the query cache.

Among them, the execution frequency and/or execution mode of each query can be captured in real time by regularly or continuously monitoring the query log, and the data change frequency of the execution result can be continuously monitored through event monitoring. The cache strategy is dynamically adjusted based on the real-time execution frequency and the data change frequency of the execution result. For example, when the execution frequency of a query increases significantly and is higher than the preset execution frequency, its execution result can be added to the cache; for queries with low data change frequency (lower than the second preset change frequency) but high execution frequency (higher than the preset execution frequency), the execution result can be added to the cache.

Among them, the value of the preset execution frequency and the second preset change frequency can be determined according to actual usage requirements and is not specifically limited here.

The query cache strategy of the embodiment of the present application can be executed based on the cache management component in the database system.

In some embodiments, the validity period of the query cache is associated with a data change pattern of the metadata.

The validity period of the query cache in this embodiment is associated with the data change mode of the metadata. The data change mode of the metadata can be determined by the update timestamp or change log of the metadata, and then the validity period of the query cache is determined according to the data change mode of the metadata. For example, assuming that the data change mode of the metadata is updated every night, the validity period of the query cache can be one day.

In this embodiment, when the basic data of the database changes, the relevant cache content is automatically refreshed to ensure the real-time and accuracy of the data.

This embodiment can dynamically manage the query cache by analyzing and applying the latest metadata information, intelligently determine which query results should be cached, and determine the validity period of the cache, rather than relying on manual intervention, which can further improve query efficiency, reduce unnecessary data processing, and significantly reduce the processing time of repeated queries. The adaptive cache management mechanism can ensure the timeliness and accuracy of cached data, while optimizing the use of system resources, and is an important supplement to improve query performance.

The embodiments of the present application analyze and apply metadata in real time, not only to optimize before query execution, but also to dynamically adjust the execution plan during query execution, and even intelligently manage the cache of query results, so as to comprehensively improve the efficiency and flexibility of database queries. The potential of metadata can be fully utilized and expanded, and a comprehensive, dynamic and adaptive solution for database query optimization is provided through the innovative integration of incremental metadata tracking and synchronization, real-time metadata-driven SQL query optimization, and metadata-based adaptive query cache management. The query strategy of the database has shifted from traditional static query optimization to a more dynamic and intelligent data-driven optimization strategy.

The method provided in the embodiment of the present application can be applied to gbase8s, and can also be applied to other databases used, and is suitable for big data environments and high concurrency scenarios, such as large database systems, real-time data analysis platforms, business intelligence and data warehouses. It can solve the query delay problem when processing complex and large-scale data sets, optimize the use efficiency of database resources, and reduce the operating cost of the system. By dynamically adjusting the query strategy and optimizing the execution plan, efficient processing of complex queries can be achieved. The method can achieve performance improvement, resource optimization, and strong adaptability. The execution efficiency of SQL queries can be significantly improved, especially in large and complex database environments, which can effectively reduce response time and increase data processing rate; through intelligent index management and query path optimization, unnecessary data reading and writing and calculation are reduced, thereby optimizing system resource utilization and reducing operating costs; query strategies are dynamically adjusted according to real-time database status and metadata so that they can adapt to different data modes and query requirements.

It should be understood that the order of execution of the steps in the above embodiment does not necessarily mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiment of the present invention.

The following is an embodiment of the device of the present invention. For details not described in detail, reference may be made to the corresponding method embodiment described above.

FIG2 shows a schematic diagram of the structure of a database SQL query optimization device provided by an embodiment of the present invention. For ease of explanation, only the parts related to the embodiment of the present invention are shown, which are described in detail as follows:

As shown in FIG. 2 , the database SQL query optimization device 20 may include: a first acquisition module 21 , a second acquisition module 22 , a generation module 23 , a query optimization module 24 and an execution module 25 .

A first acquisition module 21, used to acquire a SQL query statement to be processed, and convert the SQL query statement into a corresponding abstract syntax tree;

The second acquisition module 22 is used to acquire the latest metadata information based on the metadata query interface;

A generating module 23, used for generating an abstract syntax tree after metadata enhancement according to the latest metadata information and the abstract syntax tree;

A query optimization module 24, configured to dynamically reorganize and optimize queries based on the abstract syntax tree enhanced by the metadata, and obtain a reorganized abstract syntax tree;

The execution module 25 is used to execute the SQL query statement based on the reorganized abstract syntax tree.

In a possible implementation, the metadata query interface is an interface provided by the metadata analysis component for querying the latest metadata information;

The metadata analysis component monitors database changes in event subscription mode through metadata listeners, and records database change information in a differential manner when monitoring database change information. It also adopts delayed loading and lazy update strategies to load and update database change information when the database change information is actually applied to SQL query optimization.

In a possible implementation, the metadata analysis component dynamically determines whether to retain or eliminate metadata in the memory cache according to the access frequency and modification frequency of the metadata, and ensures the atomicity and consistency of metadata changes through transaction log analysis or a database write-ahead log;

Among them, the metadata analysis component retains metadata with an access frequency higher than a first preset access frequency and/or a modification frequency lower than the first preset modification frequency in the memory cache, and eliminates metadata with an access frequency lower than a second preset access frequency and/or a modification frequency higher than the second preset modification frequency; the first preset access frequency is greater than or equal to the second preset access frequency, and the first preset modification frequency is less than or equal to the second preset modification frequency.

In a possible implementation, the query optimization module 24 is specifically used to:

If a subquery or view exists, the target table on which the subquery or view depends is determined according to the latest metadata information, and the subquery or view is expanded when the access frequency of the target table is higher than a third preset access frequency, the index of the target table meets the preset efficient index condition, the data volume of the target table is less than the preset data volume, the data change frequency of the target table is lower than the first preset change frequency, or the result set size after executing the subquery or view is less than the preset size;

If there is an aggregation operation, then based on the statistical information in the latest metadata information, unnecessary data is filtered out in advance before the aggregation operation to reduce the amount of data input for the aggregation operation;

If there is a sorting operation, determine whether there is an index for the sequence to be sorted, the amount of data in the sequence to be sorted, and the data distribution information of the sequence to be sorted according to the latest metadata information, and determine the sorting strategy according to whether there is an index for the sequence to be sorted, the amount of data in the sequence to be sorted, and the data distribution information of the sequence to be sorted;

If the database supports column storage, adjust the storage method of the data in the database according to the latest metadata information.

In a possible implementation, the query optimization module 24 may also be used to:

If there is a WHERE clause, reorder the predicates in descending order of index selectivity in the latest metadata information;

If there are multiple table connections, adjust the order and strategy of the multiple table connections based on the table size information, index coverage information and/or distribution characteristics of the data in the table in the latest metadata information;

Based on the latest metadata information, determine whether to use index scanning during the query process, and for composite indexes, analyze the matching degree between the columns in the query conditions and the index columns in the composite index, and determine the most effective index utilization strategy in the composite index;

According to the partition information in the latest metadata information, the query operation is performed on the partitions related to the query condition.

In a possible implementation, the execution module 25 is specifically configured to:

Generate multiple execution plans based on the reorganized abstract syntax tree;

Select the target execution plan with the lowest execution cost from multiple execution plans, and execute the SQL query statement according to the target execution plan;

Get real-time performance indicators during the execution of SQL query statements;

If the real-time performance indicators do not meet the expected targets, a new execution plan is generated, and the SQL query statement is executed according to the new execution plan;

Get the actual execution result of the SQL query statement and update the metadata information according to the actual execution result.

In a possible implementation, the database SQL query optimization device 20 may further include: a query cache optimization module.

The query cache optimization module is used to: after executing the SQL query statement based on the reorganized abstract syntax tree, determine the execution frequency of the SQL query statement and the data change frequency of the execution result of the SQL query statement based on the query log and the latest metadata information; if the execution frequency of the SQL query statement is higher than the preset execution frequency and/or the data change frequency of the execution result of the SQL query statement is lower than the second preset change frequency, the execution result of the SQL query statement is stored in the query cache.

In a possible implementation, the validity period of the query cache is associated with the data change pattern of the metadata.

FIG3 is a schematic diagram of a terminal provided in an embodiment of the present invention. As shown in FIG3 , the terminal 3 of this embodiment includes: a processor 30 and a memory 31. The memory 31 is used to store a computer program 32, and the processor 30 is used to call and run the computer program 32 stored in the memory 31 to execute the steps in the above-mentioned various database SQL query optimization method embodiments, such as S101 to S105 shown in FIG1 . Alternatively, the processor 30 is used to call and run the computer program 32 stored in the memory 31 to implement the functions of each module/unit in the above-mentioned various device embodiments, such as the functions of the modules/units 21 to 25 shown in FIG2 .

Exemplarily, the computer program 32 may be divided into one or more modules/units, which are stored in the memory 31 and executed by the processor 30 to implement the present invention. The one or more modules/units may be a series of computer program instruction segments capable of implementing specific functions, which are used to describe the execution process of the computer program 32 in the terminal 3. For example, the computer program 32 may be divided into modules/units 21 to 25 as shown in FIG. 2 .

The terminal 3 may include, but is not limited to, a processor 30 and a memory 31. Those skilled in the art will appreciate that FIG3 is merely an example of the terminal 3 and does not limit the terminal 3. The terminal 3 may include more or fewer components than shown in the figure, or may combine certain components, or different components. For example, the terminal may also include input and output devices, network access devices, buses, etc.

The processor 30 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. A general-purpose processor may be a microprocessor or any conventional processor, etc.

The memory 31 may be an internal storage unit of the terminal 3, such as a hard disk or memory of the terminal 3. The memory 31 may also be an external storage device of the terminal 3, such as a plug-in hard disk, a smart media card (SMC), a secure digital (SD) card, a flash card, etc. equipped on the terminal 3. Further, the memory 31 may also include both an internal storage unit of the terminal 3 and an external storage device. The memory 31 is used to store the computer program and other programs and data required by the terminal. The memory 31 may also be used to temporarily store data that has been output or is to be output.

The technicians in the relevant field can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional units and modules is used as an example for illustration. In practical applications, the above-mentioned function allocation can be completed by different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above. The functional units and modules in the embodiment can be integrated in a processing unit, or each unit can exist physically separately, or two or more units can be integrated in one unit. The above-mentioned integrated unit can be implemented in the form of hardware or in the form of software functional units. In addition, the specific names of the functional units and modules are only for the convenience of distinguishing each other, and are not used to limit the scope of protection of this application. The specific working process of the units and modules in the above-mentioned system can refer to the corresponding process in the aforementioned method embodiment, which will not be repeated here.

In the above embodiments, the description of each embodiment has its own emphasis. For parts that are not described or recorded in detail in a certain embodiment, reference can be made to the relevant descriptions of other embodiments.

Those of ordinary skill in the art will appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of the present invention.

In the embodiments provided by the present invention, it should be understood that the disclosed devices/terminals and methods can be implemented in other ways. For example, the device/terminal embodiments described above are only schematic. For example, the division of the modules or units is only a logical function division. There may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.

The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

If the integrated module/unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the present invention implements all or part of the processes in the above-mentioned embodiment method, and can also be completed by instructing the relevant hardware through a computer program. The computer program can be stored in a computer-readable storage medium. When the computer program is executed by the processor, the steps of the above-mentioned database SQL query optimization method embodiments can be implemented. Among them, the computer program includes computer program code, and the computer program code can be in source code form, object code form, executable file or some intermediate form. The computer-readable medium may include: any entity or device that can carry the computer program code, recording medium, U disk, mobile hard disk, disk, optical disk, computer memory, read-only memory (ROM), random access memory (RAM), electric carrier signal, telecommunication signal and software distribution medium.

The embodiments described above are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some of the technical features may be replaced by equivalents. Such modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included in the protection scope of the present invention.

Claims (9)

1. A method for optimizing a database SQL query, comprising:

acquiring an SQL query statement to be processed, and converting the SQL query statement into a corresponding abstract syntax tree;

Acquiring the latest metadata information based on the metadata query interface;

Generating an abstract syntax tree with enhanced metadata according to the latest metadata information and the abstract syntax tree;

based on the abstract syntax tree with the enhanced metadata, query dynamic reorganization and optimization are carried out to obtain a reorganized abstract syntax tree;

executing the SQL query statement based on the reorganized abstract syntax tree;

The method for obtaining the abstract syntax tree after the query dynamic reorganization and optimization based on the abstract syntax tree after the metadata enhancement comprises the following steps:

if a sub-query or a view exists, determining a target table on which the sub-query or the view depends according to the latest metadata information, and performing expansion operation on the sub-query or the view when the access frequency of the target table is higher than a third preset access frequency, the index of the target table accords with a preset efficient index condition, the data volume of the target table is smaller than a preset data volume, the data change frequency of the target table is lower than a first preset change frequency, or the size of a result set after the sub-query or the view is executed is smaller than a preset size;

If the aggregation operation exists, filtering unnecessary data in advance before the aggregation operation according to the statistical information in the latest metadata information, and reducing the data input quantity of the aggregation operation;

If the sorting operation exists, determining whether an index exists in the to-be-sorted column, the data quantity of the to-be-sorted column and the data distribution information of the to-be-sorted column according to the latest metadata information, and determining a sorting strategy according to whether the index exists in the to-be-sorted column, the data quantity of the to-be-sorted sequence and the data distribution information of the to-be-sorted sequence;

And if the database supports columnar storage, adjusting the storage mode of the data in the database according to the latest metadata information.

2. The method for optimizing database SQL query according to claim 1, wherein the metadata query interface is an interface provided by a metadata analysis component for querying up-to-date metadata information;

The metadata analysis component monitors the change of the database in an event subscription mode through the metadata monitor, records the change information of the database in a differential mode when the change information of the database is monitored, and loads and updates the change information of the database when the change information of the database is actually applied to SQL query optimization by adopting a delay loading and inertia updating strategy.

3. The method according to claim 2, wherein the metadata analysis component dynamically determines the retention or elimination of metadata in the memory cache according to the access frequency and modification frequency of metadata, and ensures the atomicity and consistency of metadata changes through transaction log analysis or pre-written log of the database;

the metadata analysis component is used for storing metadata with access frequency higher than a first preset access frequency and/or modification frequency lower than a first preset modification frequency in a memory cache, and eliminating metadata with access frequency lower than a second preset access frequency and/or modification frequency higher than the second preset modification frequency; the first preset access frequency is greater than or equal to the second preset access frequency, and the first preset modification frequency is less than or equal to the second preset modification frequency.

4. The method for optimizing a database SQL query according to claim 1, wherein the dynamically reorganizing and optimizing the query based on the metadata-enhanced abstract syntax tree to obtain a reorganized abstract syntax tree, further comprises:

If the WHERE clause exists, reordering predicates according to the sequence from high index selectivity to low index selectivity in the latest metadata information;

if the multi-table connection exists, adjusting the sequence and strategy of the multi-table connection according to the size information, index coverage information and/or the distribution characteristics of the data in the table in the latest metadata information;

determining whether index scanning is used in the query process according to the latest metadata information, analyzing the matching degree of a column in the query condition and an index column in the composite index for the composite index, and determining the most effective index utilization strategy in the composite index;

and executing query operation on the partition related to the query condition according to the partition information in the latest metadata information.

5. The method of claim 1, wherein the executing the SQL query statement based on the reorganized abstract syntax tree comprises:

generating a plurality of execution plans based on the reorganized abstract syntax tree;

selecting a target execution plan with the lowest execution cost from the plurality of execution plans, and executing the SQL query statement according to the target execution plan;

acquiring a real-time performance index in the process of executing the SQL query statement;

If the real-time performance index does not accord with the expected target, generating a new execution plan, and executing the SQL query statement according to the new execution plan;

and acquiring an actual execution result of the SQL query statement, and updating metadata information according to the actual execution result.

6. The method of claim 1 to 5, wherein after the executing the SQL query statement based on the reorganized abstract syntax tree, the method of optimizing a database SQL query further comprises:

Determining the execution frequency of the SQL query statement and the data change frequency of the execution result of the SQL query statement based on the query log and the latest metadata information;

And if the execution frequency of the SQL query statement is higher than the preset execution frequency and/or the data change frequency of the execution result of the SQL query statement is lower than the second preset change frequency, storing the execution result of the SQL query statement in a query cache.

7. The method of claim 6, wherein the validity period of the query cache is associated with a data change pattern of metadata.

8. A terminal comprising a memory for storing a computer program and a processor for invoking and running the computer program stored in the memory to perform the database SQL query optimization method of any of claims 1 to 7.

9. A computer readable storage medium storing a computer program, characterized in that the computer program when executed by a processor implements the steps of the database SQL query optimization method according to any one of claims 1 to 7.