Database query optimization method and device based on large model and medium

Abstract

The embodiment of the invention discloses a database query optimization method and device based on a large model and a medium, and relates to the technical field of databases. The method comprises the steps that under triggering of a current query request, a query SQL text of the current query request is collected in real time, and real-time environment data of a current execution engine is obtained; vectorizing the query SQL text and the real-time environment data to determine a query reference vector; the query reference vector is input into a pre-trained space-time attention model, performance bottleneck positioning data for the current query is determined, a query optimization strategy is determined based on the performance bottleneck positioning data, and the query optimization strategy comprises an index optimization strategy and a query rewriting strategy; and optimizing the current query request according to the query optimization strategy, determining optimized query information, performing rewriting security verification on the optimized query information in a preset database copy environment, and executing database query based on the optimized query information after the rewriting security verification is passed.

Description

TECHNICAL FIELD

[0001] The present specification relates to the technical field of database, and particularly relates to a database query optimization method based on a large model, equipment and medium. BACKGROUND

[0002] With the rapid development of big data and cloud computing technology, the data scale and query complexity faced by the database system present exponential growth. In the traditional database query optimization technology, the database query optimization mainly depends on the rule-based static optimizer, which selects the execution plan through the pre-defined cost model (such as CPU / IO cost estimation) and the heuristic rules generated by human experience. The existing optimizer generates an execution strategy depending on historical statistical information (such as data histogram, index cardinality). However, in the large-scale dynamic data environment, the data distribution continuously changes (such as real-time stream data update), which causes the statistical information to quickly become invalid; the complex query mode such as multi-table connection nested aggregation exceeds the preset rule coverage range, which causes the optimizer to frequently select an inefficient execution plan, and causes the query response time to significantly decrease.

[0003] In order to make up for the defects of static rules, the current scheme needs manual intervention, the database administrator manually creates an index or adjusts the configuration parameter, and the developer forcibly specifies the execution path through the SQL prompt, which has serious lag, is difficult to respond to the instantaneous performance fluctuation of the high concurrency scene, and cannot meet the real-time optimization demand.

[0004] Therefore, in the data high concurrency scene, the traditional rule-based static optimization mode has the problem of long query response time, and the optimization operation has lag, which cannot meet the real-time optimization demand. SUMMARY

[0005] One or more embodiments of the present specification provide a database query optimization method based on a large model, equipment and medium, which are used to solve the technical problem that in the data high concurrency scene, the traditional rule-based static optimization mode has the problem of long query response time, and the optimization operation has lag, which cannot meet the real-time optimization demand.

[0006] One or more embodiments of the present specification adopt the following technical solutions:

[0007] One or more embodiments of the specification provide a large model-based database query optimization method, the method comprising: under triggering of a current query request, collecting a query SQL text of the current query request in real time, and obtaining real-time environment data of a current execution engine, performing vectorization processing on the query SQL text and the real-time environment data to determine a query reference vector; inputting the query reference vector into a pre-trained space-time attention model to determine performance bottleneck positioning data for the current query, so as to determine a query optimization strategy based on the performance bottleneck positioning data, wherein the query optimization strategy comprises an index optimization strategy and a query rewriting strategy; optimizing the current query request according to the query optimization strategy to determine optimized query information, performing rewriting security checking on the optimized query information in a pre-set database replica environment, and executing database query based on the optimized query information when the rewriting security checking is passed.

[0008] One or more embodiments of the specification provide a large model-based database query optimization device, comprising:

[0009] at least one processor; and,

[0010] a memory connected in communication with the at least one processor; wherein,

[0011] the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the above method.

[0012] One or more embodiments of the specification provide a non-volatile computer storage medium, which stores computer executable instructions configured to execute the above method.

[0013] The above at least one technical solution adopted by the embodiments of the present specification can achieve the following beneficial effects: Through the technical solutions of the embodiments of the present specification, in a high concurrency scenario, the traditional rule engine needs to traverse a massive static rule library, which introduces a decision tree depth explosion problem. By SQL text-environment state vectorization fusion in the embodiments of the present specification, the structural features of the SQL syntax tree are mapped to a unified numerical vector with real-time resource indicators, and a multi-dimensional dynamic feature representation system is constructed. The inherent complexity of the syntax tree vector captures the query, and the timing vector reflects the instantaneous environmental pressure. The linear combination of the two enables the system to perceive the nonlinear relationship between the performance degradation acceleration of complex queries under resource constraints. The text-type SQL statement and the numerical-type monitoring indicator are converted into isomorphic mathematical objects, providing standardized input for the deep learning model and solving the decision-making blind spot caused by the data type split in the traditional scheme. The performance bottleneck positioning module based on the space-time attention model uses a cross-period attention weight distribution algorithm to achieve accurate deconstruction of the bottleneck causes. In high concurrency, the traditional replica verification is inefficient due to data synchronization delay and double execution overhead. Through the technical solutions of the embodiments of the present specification, virtual index metadata mirrors are directly constructed in the replica environment, avoiding physical data replication, achieving stable control of verification delay within milliseconds under thousands of concurrent levels, and decoupling resource consumption and concurrency. The verification process implemented in the database replica environment constructs an industrial-level security protection, ensuring that any syntax tree modification in the optimization process does not change the essence of the business logic, and avoiding the influence of optimization behavior on system stability through resource isolation. BRIEF DESCRIPTION OF DRAWINGS

[0014] In order to more clearly illustrate the technical solutions in the embodiments of the present specification or the prior art, the following will briefly introduce the drawings needed to be used in the embodiment or prior art description. Obviously, the drawings in the following description are only some embodiments described in the present specification, and for those skilled in the art, other drawings can also be obtained without creative labor. In the drawings:

[0015] Figure 1 A flowchart of a database query optimization method based on a large model provided by the embodiments of the present specification is shown in the figure.

[0016] Figure 2 A structural diagram of a database query optimization device based on a large model provided by the embodiments of the present specification is shown in the figure. DETAILED DESCRIPTION

[0017] In order for those skilled in the art to better understand the technical solutions in the specification, the technical solutions in the specification will be clearly and completely described in the specification below in conjunction with the drawings in the specification. Obviously, the described embodiments are only part of the embodiments of the specification, not all. Based on the embodiments of the specification, all other embodiments obtained by those of ordinary skill in the art without creative labor should be within the scope of protection of the specification.

[0018] The embodiments of the specification provide a database query optimization method based on a large model. It should be noted that the execution subject in the embodiments of the specification can be a server or any device with data processing capability. Figure 1 A flowchart of a database query optimization method based on a large model provided by the embodiments of the specification is shown in FIG. 1, which mainly includes the following steps: Figure 1

[0019] Step S101, under the triggering of a current query request, a query SQL text of the current query request is collected in real time, and real-time environment data of a current execution engine is obtained. The query SQL text and the real-time environment data are vectorized to determine a query reference vector.

[0020] The query SQL text and the real-time environment data are vectorized to determine the query reference vector, which specifically includes: obtaining the real-time environment data, wherein the real-time environment data includes CPU utilization, memory pressure and IOPS indicators; the query SQL text is parsed into an abstract syntax tree, and the number of JOIN operators, the nesting depth of subqueries, and the WHERE condition predicate complexity in the abstract syntax tree are mapped into a fixed-dimension syntax tree structure vector; the CPU utilization fluctuation rate, the memory pressure change gradient, and the IOPS spike frequency in the real-time environment data are quantified into normalized numerical vectors to determine a time series feature vector.

[0021] ​In the field of database query optimization, traditional methods rely on static rules and manual experience, making it difficult to cope with dynamic changes in query load and real-time resource status. Step S101 collects query SQL text and execution engine environment data such as CPU, memory, and IOPS indicators in real time, and performs vectorization processing to generate query reference vectors, providing structured input for subsequent intelligent optimization decisions. First, the query pattern and data distribution in the database environment are highly dynamic, and static rules cannot capture these changes in time; second, query performance is not only affected by the SQL structure, but also closely related to real-time resource status, such as the possibility of overflow under high memory pressure; finally, converting heterogeneous data (SQL text, environment indicators) into a unified numerical vector is the basis for subsequent intelligent analysis by the model. Without this step, it will be impossible to establish a quantitative evaluation system, resulting in optimization strategies that are disconnected from the actual running environment, ultimately making it difficult to achieve precise performance improvement.

[0022] In one embodiment of the present specification, upon receiving a current query request, the original SQL text corresponding to the current query request is obtained through the database audit log module, and the real-time environment indicators of the execution environment are synchronously collected by the monitoring agent. The real-time environment indicators include CPU current utilization, memory page error rate, and disk IOPS throughput. The SQL text is converted into an abstract syntax tree (AST) by a syntax parser, from which key structural features are extracted. The number of JOIN operators is calculated to reflect the complexity of table association, the depth of subquery nesting is detected to assess the difficulty of execution, and the predicate type (equality match, range query, or fuzzy match) and its logical combination of the WHERE clause are analyzed. These features are mapped into a fixed-dimensional syntax tree structure vector, such as a three-dimensional vector value representing the basic complexity, for example, a three-dimensional vector [2, 1, 3] representing the existence of 2 JOIN operations, 1 layer of subquery nesting, and 3 WHERE predicate conditions. At the same time, the environment indicators are processed in time series, the volatility of CPU utilization is calculated by the deviation of the current value from the average value in a preset time period, the change gradient of memory pressure is determined by the increase in occupancy per unit time, and the peak frequency of IOPS is determined by the number of times exceeding the baseline threshold in a specific window. These dynamic indicators are normalized to convert them into 0-1 range values, forming a time series feature vector. Finally, the syntax tree vector and the time series vector are concatenated into a unified query reference vector, serving as the input carrier for the downstream model.

[0023] The conventional optimizer only relies on SQL text to generate an execution plan, and the embodiments of the present specification dynamically adapt the optimization strategy to the system state by fusing real-time environmental data vectors, such as avoiding full table scanning under high IOPS pressure, to solve the execution plan failure problem caused by ignoring the runtime resource state in the traditional method; the traditional vectorization method only focuses on the SQL syntax structure, and the embodiments of the present specification add a time sequence feature dimension, such as the memory pressure change gradient, to quantify the resource fluctuation trend, so that the model can predict potential risks, such as an imminent memory overflow, and thus actively avoid high-cost operations in the query rewriting stage; the conventional scheme needs to process SQL structure and environmental indicators separately, and the embodiments of the present specification unify the discrete features such as the number of syntax tree operators and the depth of subqueries and the continuous features such as CPU fluctuation rate and IOPS spike frequency by constructing a joint feature space, providing standardized input for subsequent models, and greatly reducing the complexity of feature engineering.

[0024] In step S102, the query reference vector is input into the pre-trained spatio-temporal attention model to determine performance bottleneck positioning data for the current query, so as to determine a query optimization strategy based on the performance bottleneck positioning data.

[0025] The query optimization strategy includes an index optimization strategy and a query rewriting strategy.

[0026] Before inputting the query reference vector into the pre-trained spatio-temporal attention model to determine the performance bottleneck positioning data for the current query, the method further includes: collecting historical query logs and associated execution exception labels from a database audit log module; processing the SQL text and execution environment indicators in the historical query logs into historical reference vectors; and training spatio-temporal attention model parameters through a cross-period attention mechanism with the execution exception labels as the training target.

[0027] In a dynamic database environment, the query performance bottleneck has significant spatio-temporal correlation characteristics, and historical query patterns (such as periodic report tasks) and short-term resource fluctuations (such as memory burst pressure) jointly determine the execution efficiency of the current query. The traditional optimizer only relies on current snapshot information for decision-making and cannot capture such cross-period dependencies. This step converts the SQL text, execution environment indicators, and associated exception labels (such as execution timeout and memory overflow) in the historical query logs into learnable pattern features through the pre-trained spatio-temporal attention model. Database performance problems often have continuity (such as index failure during daily peak hours), and long-term regularities need to be refined through historical data analysis; in addition, the execution exception label provides a supervision signal for the model, enabling bottleneck positioning to shift from passive detection to active prediction; in a distributed database scenario, the performance decay pattern of cross-node query links needs to be accurately modeled through global analysis of historical logs. Without this pre-training process, real-time optimization will degenerate into local empirical decision-making, losing the ability to predict systemic risks.

[0028] In an embodiment of the present specification, the database audit log module continuously collects historical SQL texts and their execution time environment indicators, including CPU utilization, memory occupation, IOPS value, while monitoring agent marks execution abnormal events, such as response timeout, resource exhaustion. Each historical record is processed through a unified vectorization process, after the SQL text is parsed into an abstract syntax tree, structural features such as the number of JOIN operators, subquery nesting depth are extracted; the environment indicators are calculated for their volatility and change gradient, and finally spliced into a historical reference vector. The execution abnormality label is encoded as a multi-class discrete value, such as index invalidation, memory contention, network delay. When training, a cross-period attention mechanism is used, the model first identifies periodic query patterns, such as complex aggregation operations of weekly reports, and then correlates short-term resource fluctuations, such as a sharp increase in memory occupation during report execution period, and finally establishes a mapping relationship of "syntax structure-environment state-exception type". By minimizing the prediction error of the exception type, the model learns the decision boundary such as "high subquery nesting + memory pressure gradient > 0.6 → Memory overflow probability > 80% ” After the parameters are solidified, they are deployed to the real-time analysis module.

[0029] The same SQL statement may trigger completely different performance problems under different resource states or historical load backgrounds. Traditional optimizers rely on static rules or instantaneous snapshot analysis and cannot capture this cross-time and space dimensionality. In an embodiment of the present specification, when the query reference vector is generated, a pre-trained spatio-temporal attention model is loaded. The model first parses the syntax tree features in the vector, such as the number of JOIN operators and subquery depth, as the basic query pattern, and extracts the time series features, such as the memory pressure change gradient, to represent the current environment state. Through a three-level analysis of the cross-period attention layer, the first level retrieves similar query patterns in the historical database, such as range queries with the same table structure, and compares their execution results under different resource states; the second level focuses on the recent time window to detect whether similar environment indicator combinations (such as high CPU volatility + low IOPS) have ever caused execution abnormalities; the third level builds inter-node attention weights to locate the resource contention transmission path in a distributed environment, such as memory overload in a computing node causing storage node IO blocking. Finally, structured bottleneck positioning data is output, and the index invalidation type and resource contention hotspot in the performance bottleneck positioning data.

[0030] Through the above technical solutions, traditional performance analysis tools only provide isolated diagnosis of the current query, while the embodiments of the present specification establish a three-dimensional association through a spatio-temporal attention mechanism, associate the fault mode of the historical similar scene in the time dimension, track the mutual influence of the distributed nodes in the space dimension, and bind the causal relationship between the syntax features and the resource consumption in the structure dimension.

[0031] Based on the performance bottleneck positioning data, a query optimization strategy is determined, specifically including: analyzing the index invalidation type and resource contention hotspot in the performance bottleneck positioning data to generate a joint index creation scheme according to the index invalidation type; inputting the resource contention hotspot and the index invalidation type into a reinforcement learning decision model to determine a query rewriting rule, wherein the query rewriting rule includes deleting a high memory consumption operator or replacing a high IOPS consumption operator. The resource contention hotspot and the index invalidation type are input into the reinforcement learning decision model to determine the query rewriting rule, specifically including: mapping the resource contention hotspot into a discrete state label, wherein the discrete state label includes a memory pressure anomaly label, an IOPS overload label, and a CPU contention label; defining a discrete action space, wherein the discrete action includes deleting a sorting operator node in an abstract syntax tree, replacing a fuzzy matching operator in a WHERE clause with a full-text index retrieval operator, and inserting an index forced use declaration symbol in a JOIN condition; obtaining an original query IO consumption and an optimized IO consumption through a database monitoring agent, and obtaining an original response time and an optimized response time through a database execution engine; calculating a reward function value corresponding to each discrete action according to a preset target reward function based on the original query IO consumption, the optimized IO consumption, the original response time, and the optimized response time; selecting a target action with the maximum reward function value in the discrete action space as an input feature through a preset strategy, and converting the target action into an executable query rewriting rule.

[0032] In actual application scenarios, index invalidation (such as not covering query conditions) often causes a chain of resource consumption, such as full table scanning to increase IOPS, which cannot be solved by single dimension optimization. In the e-commerce promotion scenario, index missing of the promotion query will cause the memory sorting area to overflow, at which time adding an index alone cannot relieve the memory pressure, and the sorting clause must be rewritten simultaneously. In addition, independently developed optimization strategies may interact with each other, for example, in a distributed database, creating a redundant index to reduce network transmission, which in turn exacerbates the storage node IO load; or deleting an aggregation operation to relieve CPU pressure, but triggering network blocking due to result set inflation; in a high concurrency scenario, manual processes cannot meet the timeliness requirements.

[0033] In an embodiment of the present specification, when the performance bottleneck positioning data is generated, the index invalidation type therein is first analyzed, such as joint index field missing and statistical information expiration, and the resource contention hotspot is analyzed, such as memory sorting area overload and network transmission bottleneck. The index invalidation type is directly converted into an executable scheme, if the range query field is identified as not covered by the index, a joint index creation statement containing the field is generated; if it is detected that the statistical information expiration causes index selection error, a statistical information refreshing task is triggered.

[0034] Meanwhile, resource contention hotspots are classified into discrete state labels, memory page fault rates are marked as memory pressure anomaly labels, disk queue depths exceeding thresholds are marked as IOPS overload labels, and CPU ready queue lengths are marked as CPU contention labels. These labels are input into a reinforcement learning decision model together with index invalidation types. The model is preset to include a discrete space containing three types of key actions: deleting ordering operator nodes in abstract syntax trees (such as removing ORDER BY clauses to avoid memory consumption), replacing fuzzy matching operators in WHERE clauses with full-text index retrieval operators (such as rewriting '%keyword%' to CONTAINS(column, 'keyword')), and inserting index enforcement declarators in JOIN conditions (such as adding / *+INDEX(t1 idx_col)* / hints).

[0035]

[0036] During the decision-making process, the model obtains real-time IO consumption and optimized IO consumption fed back by a database monitoring agent, as well as original response times and optimized response times recorded by an execution engine. A preset reward function is used to calculate the rewards of each action. A positive reward is given when IO consumption decreases and response time shortens after an action is performed. If IO consumption temporarily increases due to index creation but response time significantly improves, the long-term reward is calculated by combining a decay function. If the result set is incorrect after optimization, a high penalty is imposed. The model takes discrete state labels as input features, selects a target action that maximizes cumulative rewards through a policy network, and finally maps the action to an executable rewrite rule. For example, under the memory pressure anomaly label, the policy network may prefer to delete ordering operator nodes rather than create memory indexes to avoid exacerbating short-term memory pressure. Under the IOPS overload label, the policy network tends to replace fuzzy matching operators to reduce disk scan volume.

[0037] Through the above technical solutions, resource contention hotspots are converted into discrete state labels, enabling the decision model to identify differentiated cost characteristics under different resource bottlenecks. Under the CPU contention label, the model prioritizes rewrite actions that reduce computational complexity (such as expanding subqueries into JOINs). Under the IOPS overload label, the model focuses on reducing data scan volume (such as adding index hints). When optimization measures yield positive rewards in low-load scenarios with index creation, but performance deteriorates in high-IO-load scenarios with index creation, the model automatically establishes associations between environmental states and action effects, enabling dynamic decision-making. By using a reinforcement learning model, resource states are used as decision constraints, and lightweight rewrite schemes (such as deleting ordering clauses) are automatically selected to replace high-consumption index operations, thereby avoiding conflicts between measures from the root.

[0038] ​In step S103, the current query request is optimized according to the query optimization strategy, and optimized query information is determined to perform rewrite security check on the optimized query information in a pre-set database replica environment, and when the rewrite security check is passed, the database query is executed based on the optimized query information.

[0039] According to the query optimization strategy, the current query request is optimized to determine the optimized query information, specifically including: declaring a virtual index in the database metadata management module according to the joint index creation scheme in the index optimization strategy; removing the sorting operation node in the abstract syntax tree based on the operator deletion instruction in the query rewriting strategy; obtaining a pre-constructed query strategy knowledge graph, wherein the knowledge graph includes a mapping relationship between operator replacement rules and performance improvement rates; determining a full-text index retrieval operator based on the query strategy knowledge graph, so as to replace the fuzzy matching operator with the full-text index retrieval operator according to the operator replacement instruction in the query rewriting strategy, to determine the modified optimized abstract syntax tree; and determining the optimized query information based on the optimized abstract syntax tree.

[0040] In actual application scenarios, the creation of a physical index needs to occupy actual storage resources and cause table lock blocking, and in a high-concurrency scenario, it is easy to trigger cascading performance degradation; in addition, the query rewriting operation lacks safety boundary control, and aggressive syntax tree modification may cause result set errors or business logic damage.

[0041] In an embodiment of the present specification, after the optimization strategy is generated, the index optimization scheme is first processed. The virtual index structure is declared in the database metadata management module, which is implemented by extending the DDL syntax, for example, the VIRTUAL label is added to the joint index idx_order_cust to be created, the index field, type and expected statistical information are recorded to the special metadata table, but the physical storage space is not allocated. The virtual index is immediately visible to the optimizer, so that the subsequent query planning can simulate its existence effect, while avoiding the disk IO and lock performance caused by actual creation.

[0042] The query rewriting operation is executed synchronously, and the target node in the abstract syntax tree is located according to the operator deletion instruction. If the instruction requires to remove the sorting operation, the OrderByClause node in the AST is traversed, the node deletion is executed after checking its business impact, and the rollback metadata is injected. For the fuzzy matching operator replacement instruction, the pre-constructed query strategy knowledge graph is queried, the graph includes an operator replacement rule library and a performance improvement rate mapping table, and also stores a compatibility matrix. The operator replacement rule library is used to store, for example, LIKE

[0043] '%xxx%' →The mapping relationship of MATCH() AGAINST(), each rule is associated with field type, database version and other constraint conditions; the performance improvement rate mapping table records the actual effect data of historical replacement operations, for example, rule R001: LIKE → The average IO consumption reduction rate of MATCH() in the text field; the memory occupancy drop rate distribution of rule R002: delete the sorting clause; the compatibility matrix marks the support status and syntax difference of the rule in different database versions.

[0044] According to the current query characteristics (such as field type, database version), the atlas is retrieved, and the replacement rules and their performance improvement rate data that meet the constraints are obtained. For example, when processing WHERE address LIKE '% Shanghai%', the replacement rule matching the text field in the atlas is retrieved; the rule with the highest performance improvement rate, MATCH(address) AGAINST ('Shanghai' IN BOOLEAN MODE), is selected; and the support status of the rule in the target database version (such as MySQL 5.6+ needs to enable full-text index) is verified. After verification, the LikePredicate node in the AST is replaced with the target operator node. After completing the node operation, the optimized version of the AST is generated, and is reverse compiled into a SQL statement. Finally, the optimized statement and the virtual index declaration are packaged into an optimized query information package.

[0045] Through the above technical solutions, by means of virtual index declaration, the metadata change is decoupled from the actual physical operation, and the optimizer can refer to the expected effect of the virtual index when generating the execution plan, and the actual creation operation is executed after the system is idle or verified in the replica environment; the traditional optimizer cannot quantitatively evaluate the benefits of rewriting operations, but the performance improvement rate mapping table in the knowledge atlas can predict the optimization effect in the decision-making stage, so that the optimization decision is changed from experience guessing to data driving.

[0046] In the field of database query optimization, index creation can cause lock table blocking critical business, query statement modification can tamper with the result set semantics, and even trigger database crash. In one embodiment of the present specification, the optimization query information is checked for rewriting security in a pre-configured database replica environment. When the optimization query information is generated, it is automatically routed to the pre-configured database replica cluster. The replica maintains data consistency with the production environment through real-time log synchronization (such as MySQL Binlog replication), but has independent computing resources. First, the physical creation of the virtual index is performed in the replica environment. Parse the index definition in the optimization information package, invoke the CREATE INDEX CONCURRENTLY command (avoid lock table), and monitor the resource consumption of the index construction process at the same time. Flush the statistics after completion to ensure that the optimizer can accurately evaluate the index effect. Second, perform syntax-level rewriting verification, inject the optimized SQL statement into the replica database execution engine, and obtain the detailed execution plan through the EXPLAIN ANALYZE instruction. Focus on detecting semantic equivalence, operator compatibility, and permission consistency. Semantic equivalence refers to comparing the parse tree structure of the statement before and after optimization to ensure that the logic of WHERE / HAVING and other key clauses has not changed; operator compatibility is used to verify the support status of the replaced full-text search operator (such as CONTAINS()) in the replica database version; permission consistency is used to check whether the execution account has the required permissions for the new operator (such as the INDEX permission of the full-text index). Finally, compare the data result sets, construct a double verification pipeline, the first pipeline executes the original query statement, and the result set is temporarily stored in temporary table A; the second pipeline B executes the optimized statement, and the result set is temporarily stored in temporary table B. Use row-level hash comparison algorithm to calculate the checksum value (such as CRC64) of each row in the two tables, and then aggregate to generate a global fingerprint code. When the fingerprint code difference exceeds the preset threshold, locate the difference data row by row and mark the abnormal points (such as missing records or field value deviation). Collect key indicators through the performance monitoring agent of the replica environment, including the IO scan volume, memory peak, execution time of the optimized query, and index maintenance cost (such as reconstruction time, disk space increase); it can also include the throughput impact on concurrent queries (such as TPS fluctuation rate during testing). When detecting inconsistent result sets, abnormal resource consumption, or performance degradation, automatically trigger rollback operation: delete the newly created physical index, and restore the query statement through reverse instructions (such as restoring the deleted ORDER BY clause).

[0047] When the rewritten security check passes, the method further includes: obtaining real-time IOPS load data, and determining an SLA priority label corresponding to the current query request; when the real-time IOPS load data exceeds a dynamic threshold value, assigning a query with an SLA priority label of a normal level to an asynchronous execution queue. The method further includes: obtaining a historical IOPS load data set to separate, by a seasonal time series decomposition algorithm, a long-term trend component, a periodic fluctuation component, and a random residual component in the historical IOPS load data set; fitting the periodic fluctuation component based on an autoregressive integrated moving average model, and outputting a load fluctuation threshold curve of a future period; determining a reference value based on a peak value of the load fluctuation threshold curve, and determining the dynamic threshold value by increasing a preset proportion based on the reference value.

[0048] In a high-concurrency database scenario, the traditional static threshold scheduling has defects. First, the fixed threshold value cannot adapt to the periodic fluctuations of business load, such as the difference between the traffic flood peak of e-commerce promotion and the resource demand of the batch processing job in the early morning, which leads to frequent misjudgment triggering degradation; second, the scheduling strategy lacks business value perception, which makes the key business (such as payment transaction) and the background task (such as data export) fall into resource contention.

[0049] In an embodiment of the present specification, after the query passes the security check, real-time IOPS load data stream reported by the database monitoring agent is collected, including disk queue depth, average I / O waiting time and other dimensions, and the SLA priority label of the query is extracted from the application layer middleware, which is urgent or normal. The dynamic threshold value calculation engine periodically executes the following process:

[0050] First, the IOPS data set of the past few weeks is extracted from the historical repository of the monitoring agent, and the seasonal time series decomposition (STL) algorithm is applied to separate the long-term trend component, the periodic fluctuation component and the random residual component. The long-term trend component is used to describe the baseline rise caused by business growth, such as the monthly growth of user quantity leading to the rise of IO load; the periodic fluctuation component is used to extract the regular fluctuation that recurs by hour / day / week, such as the IO peak caused by the 10:00 report task every day; the random residual component is used to filter the noise caused by sudden events, such as temporary data backup.

[0051] Secondly, according to the periodic fluctuation component, an autoregressive integrated moving average (ARIMA) model is used for fitting, and the lag order is determined according to the autocorrelation of historical fluctuations, such as detecting similar peaks every 24 hours; non-stationarity is eliminated by order difference, such as converting the original sequence into a stationary volatility; the prediction bias is adjusted according to the residual sequence. The load fluctuation threshold curve of the future period is output, such as predicting that a periodic peak will occur at 14:00-15:00 today. Take the peak value of the curve as the reference value, and calculate the reference value Baseline = max(ARIMA_output) based on the system protection strategy and the preset proportion of floating. The dynamic threshold Threshold = Baseline × (1 + Buffer) is set, wherein the buffer ratio Buffer is dynamically adjusted according to the system redundancy, such as setting a high buffer in the test environment and strictly limiting in the production environment.

[0052] When the real-time IOPS exceeds the dynamic threshold, a hierarchical response of emergency-level queries and ordinary-level queries is performed. In the case of emergency-level queries, an exclusive thread pool is allocated and resource preemption permission is set, such as allowing interruption of ordinary query I / O operations; in the case of ordinary-level queries, redirection to an asynchronous execution queue is performed, and an incremental pull method is used for processing, such as scanning 1000 rows each time to avoid blocking the disk; when the real-time IOPS falls below a certain proportion of the dynamic threshold, the asynchronous queue query is automatically migrated back to the main queue. Through STL decomposition and ARIMA prediction, periodic IO pulses caused by whole point killing are successfully captured, and the dynamic threshold automatically floats to reserve buffer space before the killing, so that the order processing throughput peak period still maintains zero degradation record. Based on the prediction ability of historical rules, the resource overload misjudgment rate is significantly reduced.

[0053] Through the technical solutions of the embodiments of the present specification, in a high concurrency scenario, the traditional rule engine needs to traverse a massive static rule library, introducing a decision tree depth explosion problem. Through the SQL text-environment state vectorization fusion in the embodiments of the present specification, the structural features of the SQL syntax tree and real-time resource indicators are mapped into a unified numerical vector, and a multi-dimensional dynamic feature representation system is constructed. The inherent complexity of the syntax tree vector captures the query, and the time sequence vector reflects the instantaneous environmental pressure. The linear combination of the two enables the system to perceive the nonlinear relationship between the performance degradation acceleration of complex queries in resource-intensive scenarios. The text-type SQL statement and the numerical monitoring indicator are converted into isomorphic mathematical objects, providing standardized input for the deep learning model and solving the decision-making blind spot caused by the data type split in the traditional solution. The performance bottleneck positioning module based on the spatiotemporal attention model uses a cross-period attention weight distribution algorithm to achieve accurate deconstruction of the bottleneck causes. In a high concurrency scenario, the traditional replica verification is inefficient due to data synchronization delays and double execution overheads. Through the technical solutions of the embodiments of the present specification, a virtual index metadata mirror is directly constructed in the replica environment, avoiding physical data replication. The verification delay is stably controlled within milliseconds under thousands of concurrent scenarios, and the resource consumption is decoupled from the concurrency. The verification process implemented in the database replica environment constructs an industrial-level security protection, ensuring that any syntax tree modification during the optimization process does not change the essence of the business logic. At the same time, resource isolation is used to avoid the impact of optimization behavior on system stability.

[0054] The embodiments of the present specification also provide a database query optimization device based on a large model, as shown in Figure 2 The device includes at least one processor and a memory communicatively connected to the at least one processor. The memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the above method.

[0055] The embodiments of the present specification also provide a non-volatile computer storage medium storing computer executable instructions configured to execute the above method.

[0056] Each of the embodiments of the present specification adopts a progressive manner for description, and the same or similar parts of each embodiment can be referred to each other. Each embodiment focuses on the differences from other embodiments. In particular, for the device, equipment, and non-volatile computer storage medium embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can be referred to the part of the method embodiment.

[0057] The above describes particular embodiments of the present specification. Other embodiments are within the scope of the appended claims. In some cases, the acts or steps recited in the claims can be performed in a different order than those in the embodiments and still achieve desirable results. Additionally, the processes depicted in the accompanying figures do not necessarily require the particular order shown or sequential order to achieve the desired results. In certain implementations, multitasking and parallel processing can be advantageous or possible.

[0058] The device and medium provided by the embodiments of the present specification are one-to-one corresponding with the method, and therefore the device and medium also have similar beneficial technical effects as the method corresponding thereto. Since the beneficial technical effects of the method have been described in detail above, the beneficial technical effects of the device and medium will not be described here again.

[0059] Those skilled in the art will understand that the embodiments of the present specification can be provided as a method, a system, or a computer program product. Therefore, the present specification can take the form of an entirely hardware embodiment, an entirely software embodiment, or an embodiment combining software and hardware aspects. Moreover, the present specification can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk memory, CD-ROM, optical memory, etc.) containing computer-usable program code.

[0060] The present specification is described with reference to flowcharts and / or block diagrams of the method, device (system), and computer program product according to the embodiments of the present specification. It should be understood that each flow and / or block in the flowcharts and / or block diagrams, and the combination of flows and / or blocks in the flowcharts and / or block diagrams can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing apparatus to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing apparatus produce a device implemented in the flowcharts and / or block diagrams. Figure 1 one or more flows and / or blocks Figure 1 means for performing the function specified by one or more blocks.

[0061] These computer program instructions can also be stored in a computer-readable memory that can direct the computer or other programmable data processing apparatus to work in a specific manner, so that the instructions stored in the computer-readable memory produce a manufactured product including instruction means, which implements the flowcharts and / or block diagrams. Figure 1 one or more flows and / or blocks Figure 1 one or more blocks.

[0062] These computer program instructions can also be loaded into a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer-implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart block or blocks. Figure 1 Figure 1 These computer program instructions can also be loaded into a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer-implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart block or blocks.

[0063] In a typical configuration, a computing device includes one or more processors (CPUs), input / output interfaces, network interfaces, and memory.

[0064] The memory can include non-persistent memory and / or volatile memory, such as random access memory (RAM) and / or cache memory, and / or non-volatile memory, such as read-only memory (ROM), EPROM, and / or flash memory. The memory is an example of computer-readable media.

[0065] Computer-readable media includes permanent and non-permanent, removable and non-removable media implemented in any method or technology for storage of information such as computer-readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD), or other optical storage, magnetic cassette, magnetic tape disk storage or other magnetic storage devices, or any other non-transmission medium that can be used to store information accessible to a computing device. According to the definition herein, computer-readable media does not include transitory media such as modulated data signals and carriers.

[0066] It should also be noted that the terms "comprising", "containing", or any other variant thereof are intended to encompass non-exclusive inclusion, such that a process, method, article or apparatus that comprises a list of elements does not only include those elements, but can also include other elements not expressly listed or inherent to such process, method, article or apparatus. Without further limitation, the statement "a process, method, article or apparatus comprising one… … ” The statement "a process, method, article or apparatus comprising one…

[0067] ​The above merely provides one or more embodiments of the present specification and is not intended to limit the present specification. One of ordinary skill in the art can make various modifications and changes to one or more embodiments of the present specification. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of one or more embodiments of the present specification should be included in the scope of claims of the present specification.

Claims

1. A large model-based database query optimization method, characterized in that, The method comprises: Under the triggering of a current query request, collecting a query SQL text of the current query request in real time, and acquiring real-time environment data of a current execution engine, performing vectorization processing on the query SQL text and the real-time environment data to determine a query reference vector; inputting the query reference vector into a pre-trained spatio-temporal attention model to determine performance bottleneck positioning data for the current query, and determining a query optimization strategy based on the performance bottleneck positioning data, wherein the query optimization strategy comprises an index optimization strategy and a query rewriting strategy; optimizing the current query request according to the query optimization strategy, determining optimized query information, and performing rewriting security checking on the optimized query information in a pre-set database replica environment, and executing database query based on the optimized query information when the rewriting security checking is passed.

2. The database query optimization method based on a large model according to claim 1, characterized in that, The method further comprises: acquiring the real-time environment data, wherein the real-time environment data comprises CPU utilization, memory pressure and IOPS indicators; parsing the query SQL text into an abstract syntax tree, and mapping the number of JOIN operators, the subquery nesting depth and the WHERE condition predicate complexity in the abstract syntax tree into a fixed-dimension syntax tree structure vector; quantifying the CPU utilization fluctuation rate, the memory pressure change gradient and the IOPS spike frequency in the real-time environment data into a normalized numerical vector to determine a time series feature vector.

3. The database query optimization method based on a large model according to claim 1, characterized in that, Before inputting the query reference vector into the pre-trained spatio-temporal attention model to determine the performance bottleneck positioning data for the current query, the method further comprises: collecting historical query logs and associated execution exception labels from a database audit log module; processing the SQL text and execution environment indicators in the historical query logs into historical reference vectors; training spatio-temporal attention model parameters through cross-period attention mechanism with the execution exception labels as training targets.

4. The database query optimization method based on a large model according to claim 1, characterized in that, Based on the performance bottleneck positioning data, the method further comprises: parsing index invalidation types and resource contention hotspots in the performance bottleneck positioning data to generate a joint index creation scheme according to the index invalidation types; inputting the resource contention hotspots and the index invalidation types into a reinforcement learning decision model to determine query rewriting rules, wherein the query rewriting rules comprise deleting high memory consumption operators or replacing high IOPS consumption operators.

5. The large model-based database query optimization method of claim 4, wherein, Inputting the resource contention hotspots and the index invalidation types into a reinforcement learning decision model to determine query rewriting rules, specifically comprising: mapping the resource contention hotspots into discrete state labels, wherein the discrete state labels comprise memory pressure abnormality labels, IOPS overload labels and CPU contention labels; defining a discrete action space, wherein the discrete actions comprise deleting ordering operator nodes in an abstract syntax tree, replacing fuzzy matching operators in a WHERE clause with full-text index retrieval operators, and inserting index forced use declarators in JOIN conditions. The original query IO consumption and the optimized IO consumption are obtained through a database monitoring agent, and the original response time and the optimized response time are obtained through a database execution engine; According to a preset target reward function, a reward function value corresponding to each discrete action is calculated based on the original query IO consumption, the optimized IO consumption, the original response time, and the optimized response time; The discrete state label is taken as an input feature, a target action with the maximum reward function value in the discrete action space is selected through a preset strategy, and the target action is converted into an executable query rewriting rule.

6. The database query optimization method based on a large model according to claim 1, characterized in that, According to the query optimization strategy, the current query request is optimized to determine optimized query information, specifically including: According to a joint index creation scheme in the index optimization strategy, a virtual index is declared in a database metadata management module; Based on an operator deletion instruction in the query rewriting strategy, a sorting operation node in the abstract syntax tree is removed; A query strategy knowledge graph is obtained, wherein the knowledge graph includes a mapping relationship between an operator replacement rule and a performance improvement rate; Based on the query strategy knowledge graph, a full-text index retrieval operator is determined to replace a fuzzy matching operator with the full-text index retrieval operator according to an operator replacement instruction in the query rewriting strategy, so as to determine a modified optimized abstract syntax tree; Based on the optimized abstract syntax tree, the optimized query information is determined.

7. The database query optimization method based on large models according to claim 1, characterized in that, When the rewriting security check passes, the method further includes: Real-time IOPS load data is obtained, and an SLA priority label corresponding to the current query request is determined; When the real-time IOPS load data exceeds a dynamic threshold value, queries with an SLA priority label of a normal level are assigned to an asynchronous execution queue.

8. The large model-based database query optimization method of claim 7, wherein, The method further includes: A historical IOPS load data set is obtained to separate a long-term trend component, a periodic fluctuation component, and a random residual component in the historical IOPS load data set through a seasonal time series decomposition algorithm; The periodic fluctuation component is fitted based on an autoregressive integrated moving average model to output a load fluctuation threshold curve of a future period; A peak value of the load fluctuation threshold curve is taken as a reference value, and the dynamic threshold value is determined by increasing a preset proportion based on the reference value. 9.A database query optimization apparatus based on a large model, characterized by, The device includes: at least one processor; and a memory connected in communication with the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the method of any one of claims 1-8.

10. A non-transitory computer storage medium storing computer-executable instructions that, when executed, cause a computer to perform: The computer executable instructions are configured to perform the method of any one of claims 1-8. The computer executable instructions are configured to perform the method of any one of claims 1-8.

Images