Data query method and device, computer equipment, medium and program product
By constructing a query strategy recommendation model based on a bidirectional transformer network, the system automatically analyzes and optimizes slow SQL statements in the database, solving the performance problem when the database executes complex SQL statements and improving data query efficiency and system stability.
Patent Information
- Application Number
- CN202511706189.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-11-20
- Publication Date
- 2026-03-03
AI Technical Summary
In existing technologies, databases exhibit significant performance issues when executing complex SQL statements, leading to slow system response, impacting business stability, and making it complex and time-consuming to manually find the causes of slow queries and optimize indexing methods.
A query strategy recommendation model based on a bidirectional transformer network is adopted to generate optimized query strategies by automatically analyzing slow SQL statements and execution plans in the target database, thereby improving data query efficiency.
Automated analysis can quickly identify better query strategies, reduce query time, optimize resource usage, and improve database performance.
Smart Images
Figure CN121597705A_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of data processing technology, and in particular to a data query method, apparatus, computer equipment, medium, and program product. Background Technology
[0002] Database systems, as data processing tools that support data storage and computation, play a crucial role in data management and analysis. However, with the increase in data volume and business complexity, the performance issues of databases when executing Structured Query Language (SQL) statements are becoming increasingly prominent. Especially for complex query operations, excessively long SQL statement execution times can lead to slow system response and even affect the stability of the overall business operations.
[0003] To address these issues, database administrators (DBAs) typically manually analyze information in slow logs to identify the causes of slow queries and improve query efficiency by optimizing SQL statements or adding indexes.
[0004] However, manually finding the cause of slow queries and optimizing indexing methods is a complex and time-consuming task. Therefore, how to quickly improve data query efficiency is an urgent problem to be solved. Summary of the Invention
[0005] Therefore, it is necessary to provide a data query method, device, computer equipment, medium, and program product that can rapidly improve the efficiency of data query in response to the above-mentioned technical problems.
[0006] Firstly, this application provides a data query method, including:
[0007] Obtain the first SQL statement in the target database whose query time exceeds a preset time, and the first execution plan corresponding to the first SQL statement;
[0008] The first SQL statement and the first execution plan are input into a pre-trained query strategy recommendation model to obtain the first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network;
[0009] Execute the first SQL statement according to the first query strategy.
[0010] In one embodiment, the query strategy recommendation model is trained using the following steps: obtaining sample SQL statements with query durations exceeding a preset duration, the second execution plan corresponding to the sample SQL statements, and the actual query strategy of the sample SQL statements from the sample database; inputting the sample SQL statements and the second execution plan into the pre-built query strategy recommendation model to obtain the second query strategy corresponding to the sample SQL statements; and adjusting the parameters of the pre-built query strategy recommendation model based on the differences between the second query strategy and the actual query strategy to train the query strategy recommendation model.
[0011] In one embodiment, the query strategy recommendation model includes: a first feature extraction network, a second feature extraction network, a feature enhancement network, a feature fusion network, and a prediction network. The process involves inputting a sample SQL statement and a second execution plan into the pre-built query strategy recommendation model to obtain a second query strategy corresponding to the sample SQL statement. This includes: using the first feature extraction network to extract first features from the sample SQL statement to obtain SQL statement features; using the second feature extraction network to extract second features from the second execution plan to obtain execution plan features; using the feature enhancement network to perform self-attention enhancement on the SQL statement features to obtain enhanced SQL statement features; using the feature fusion network to fuse the enhanced SQL statement features and the execution plan features to obtain fused features; and using the prediction network to predict the second query strategy corresponding to the sample SQL statement based on the fused features.
[0012] In one embodiment, the pre-built query strategy recommendation model is tuned based on the differences between the second query strategy and the actual query strategy to train the query strategy recommendation model. This includes: determining the sub-differences between the second query strategy and the actual query strategy under at least one preset query indicator; determining the differences between the second query strategy and the actual query strategy based on the sub-differences under at least one preset query indicator; and tuning the pre-built query strategy recommendation model based on the differences between the second query strategy and the actual query strategy to train the query strategy recommendation model.
[0013] In one embodiment, the preset query metrics include at least one of the following: query duration and resource usage.
[0014] In one embodiment, the method further includes: obtaining at least one second SQL statement, a third execution plan corresponding to the at least one second SQL statement, and an actual query strategy of the at least one second SQL statement generated by the target database in the current time period; inputting the at least one second SQL statement, the third execution plan corresponding to the at least one second SQL statement, and the actual query strategy of the at least one second SQL statement into a pre-trained query strategy recommendation model, and optimizing the pre-trained query strategy recommendation model.
[0015] Secondly, this application also provides a data query device, comprising:
[0016] The first acquisition module is used to acquire the first SQL statement in the target database whose query time exceeds a preset time, and the first execution plan corresponding to the first SQL statement;
[0017] The first input module is used to input the first SQL statement and the first execution plan into a pre-trained query strategy recommendation model to obtain the first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network;
[0018] The execution module is used to execute the first SQL statement according to the first query strategy.
[0019] Thirdly, this application also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform the following steps:
[0020] Obtain the first SQL statement in the target database whose query time exceeds a preset time, and the first execution plan corresponding to the first SQL statement;
[0021] The first SQL statement and the first execution plan are input into a pre-trained query strategy recommendation model to obtain the first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network;
[0022] Execute the first SQL statement according to the first query strategy.
[0023] Fourthly, this application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, performs the following steps:
[0024] Obtain the first SQL statement in the target database whose query time exceeds a preset time, and the first execution plan corresponding to the first SQL statement;
[0025] The first SQL statement and the first execution plan are input into a pre-trained query strategy recommendation model to obtain the first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network;
[0026] Execute the first SQL statement according to the first query strategy.
[0027] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, performs the following steps:
[0028] Obtain the first SQL statement in the target database whose query time exceeds a preset time, and the first execution plan corresponding to the first SQL statement;
[0029] The first SQL statement and the first execution plan are input into a pre-trained query strategy recommendation model to obtain the first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network;
[0030] Execute the first SQL statement according to the first query strategy.
[0031] The aforementioned data query method, apparatus, computer equipment, medium, and program product can obtain a first SQL statement in a target database whose query time exceeds a preset time, and a first execution plan corresponding to the first SQL statement; input the first SQL statement and the first execution plan into a pre-trained query strategy recommendation model to obtain a first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network; and execute the first SQL statement according to the first query strategy. In the above process, the pre-trained query strategy recommendation model can be used to automatically analyze and process the first SQL statement and the first execution plan corresponding to the first SQL statement to obtain the first query strategy. Compared with the technical solution of manually optimizing the index, a better query strategy can be quickly determined, thereby improving data query efficiency. Attached Figure Description
[0032] To more clearly illustrate the technical solutions in the embodiments of this application or related technologies, the drawings used in the description of the embodiments of this application or related technologies will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.
[0033] Figure 1 This is a diagram illustrating the application environment of a data query method in one embodiment.
[0034] Figure 2 This is a flowchart illustrating a data query method in one embodiment;
[0035] Figure 3 This is a flowchart illustrating the training steps of a query strategy recommendation model in one embodiment.
[0036] Figure 4 This is a flowchart illustrating the data query method in another embodiment;
[0037] Figure 5 This is a flowchart illustrating the data query method in yet another embodiment;
[0038] Figure 6 This is a structural block diagram of a data query device in one embodiment;
[0039] Figure 7 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation
[0040] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.
[0041] The data query method provided in this application embodiment can be applied to, for example... Figure 1 In the application environment shown, terminal 102 communicates with server 104 via a network. A data storage system can store the data that server 104 needs to process. The data storage system can be integrated onto server 104, or it can be located on the cloud or other network servers. Terminal 102 can be, but is not limited to, various personal computers, laptops, smartphones, tablets, etc. Server 104 can be a standalone physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing cloud computing services.
[0042] In one exemplary embodiment, such as Figure 2 As shown, a data query method is provided, which can be applied to... Figure 1 Taking the server in the example of this, the explanation includes:
[0043] S210, obtain the first SQL statement in the target database whose query time exceeds the preset time, and the first execution plan corresponding to the first SQL statement.
[0044] SQL statements can be understood as statements used for operations such as querying, inserting, updating, and deleting data. Optionally, SQL statements may include information such as the fields to be queried, conditions, tables, sorting, and joins.
[0045] The first SQL statement can be understood as an SQL statement whose query duration exceeds the preset duration.
[0046] The first execution plan can be understood as the execution plan of the target database when executing the first SQL statement. In some embodiments, the first execution plan may include information such as access path, scan type (e.g., full table scan, index scan, etc.), row count estimation, sorting method, and join method.
[0047] In some embodiments, the target database records a slow query log during operation. The slow query log may include SQL statements whose query duration exceeds a preset time, along with their execution plans. The first SQL statement and its corresponding first execution plan can be extracted from the slow query log.
[0048] In some embodiments, raw SQL statements can be extracted from slow query logs. These raw SQL statements are then preprocessed to obtain a first SQL statement with feature vectorization, which is then used as input to the query strategy recommendation model.
[0049] In some embodiments, an SQL parser can be used to parse the original SQL statement to obtain a structured data format. For example, table names, field names, operation types, query conditions, sorting fields, and join conditions can be extracted. Then, feature extraction is performed on the parsed SQL statement to obtain feature data. In specific implementations, Natural Language Processing (NLP) techniques can be used to extract at least one of the following features: the number and type of tables, the number and type of fields, query conditions, sorting conditions, and join conditions. This feature data is then converted into a numerical vector to obtain the first feature-vectorized SQL statement.
[0050] In some embodiments, the first execution plan corresponding to the first SQL statement can be obtained through the execution plan command.
[0051] S220, the first SQL statement and the first execution plan are input into the pre-trained query strategy recommendation model to obtain the first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network.
[0052] The query strategy recommendation model is built based on the Bidirectional Encoder Representations from Transformers (BERT) network.
[0053] In some embodiments, the query strategy recommendation model can analyze the first SQL statement and the first execution plan to obtain possible query schemes, i.e., the first query strategy.
[0054] In some embodiments, the query strategy recommendation model can recommend not only single-field indexes, but also multi-field composite indexes based on query and sorting conditions.
[0055] S230, Execute the first SQL statement according to the first query strategy.
[0056] In some embodiments, if the administrator of the target database chooses to automatically execute the first SQL statement, the target database may automatically execute the first SQL statement according to the first query strategy.
[0057] In some embodiments, if the administrator of the target database selects a first query strategy after manual confirmation, the target database responds to the confirmation instruction and executes a first SQL statement according to the first query strategy.
[0058] In the above data query method, the first SQL statement whose query time exceeds a preset time in the target database, and the first execution plan corresponding to the first SQL statement, can be obtained. The first SQL statement and the first execution plan are input into a pre-trained query strategy recommendation model to obtain a first query strategy. The query strategy recommendation model is constructed based on a bidirectional transformer network. The first SQL statement is executed according to the first query strategy. In the above process, the pre-trained query strategy recommendation model can be used to automatically analyze and process the first SQL statement and the first execution plan corresponding to the first SQL statement to obtain the first query strategy. Compared with the technical solution of manually optimizing the index, a better query strategy can be quickly determined, thereby improving the data query efficiency.
[0059] Based on the technical solutions of the above embodiments, this application also provides another optional embodiment, in which the training steps of the query strategy recommendation model are refined.
[0060] See Figure 3 The training steps for the query strategy recommendation model shown include:
[0061] S310: Obtain sample SQL statements in the sample database whose query time exceeds the preset time, the second execution plan corresponding to the sample SQL statements, and the actual query strategy of the sample SQL statements.
[0062] The sample database can be understood as a database that is the same as or similar to the target database.
[0063] The sample SQL statement can be understood as an SQL statement that is the same as or similar to the first SQL statement. Optionally, the preset duration set for the sample SQL statement can be the same as the preset duration set for the first SQL statement.
[0064] In some embodiments, the process of obtaining the sample SQL statement and the second execution plan corresponding to the sample SQL statement can be similar to the process of obtaining the first SQL statement and the first execution plan corresponding to the first SQL statement, and will not be described in detail here.
[0065] In some embodiments, the actual query strategy for the sample SQL statement may be obtained from the slow query log.
[0066] S320: Input the sample SQL statement and the second execution plan into the pre-built query strategy recommendation model to obtain the second query strategy corresponding to the sample SQL statement.
[0067] In some embodiments, the query strategy recommendation model may include: a first feature extraction network, a second feature extraction network, a feature enhancement network, a feature fusion network, and a prediction network. Accordingly, inputting a sample SQL statement and a second execution plan into the pre-built query strategy recommendation model to obtain a second query strategy corresponding to the sample SQL statement includes: using the first feature extraction network to extract first features from the sample SQL statement to obtain SQL statement features; using the second feature extraction network to extract second features from the second execution plan to obtain execution plan features; using the feature enhancement network to perform self-attention enhancement on the SQL statement features to obtain enhanced SQL statement features; using the feature fusion network to fuse the enhanced SQL statement features and the execution plan features to obtain fused features; and using the prediction network to predict the second query strategy corresponding to the sample SQL statement based on the fused features.
[0068] In some embodiments, the sample SQL statement is a feature vector, and the SQL statement features corresponding to the sample SQL statement can be extracted using a first feature extraction network. Optionally, the SQL statement features may include at least one of the following: the number and type of tables, the number and type of fields, query conditions, sorting conditions, join conditions, etc.
[0069] In some embodiments, a second feature extraction network can be used to extract execution plan features corresponding to the second execution plan. Optionally, the execution plan features may include at least one of the following: access path, scan type, row count estimation, and sorting method. Execution plan features can be used to capture the relationship between different path selections and query efficiency when executing queries in a sample database.
[0070] In some embodiments, the feature enhancement network may include multiple self-attention layers. By employing the feature enhancement network to perform self-attention enhancement on the features of the SQL statement, the dependencies between different parts of the SQL statement can be captured.
[0071] In the above embodiments, the sample SQL statements and the second execution plan are processed by various networks in the query strategy recommendation model. Through the self-attention mechanism, the global structure and local dependencies of the SQL statements can be processed, the key factors affecting query efficiency can be accurately identified, and thus a more targeted query strategy can be generated.
[0072] S330, Based on the differences between the second query strategy and the actual query strategy, the parameters of the pre-built query strategy recommendation model are tuned in order to train the query strategy recommendation model.
[0073] In some embodiments, the sub-differences between the second query strategy and the actual query strategy under at least one preset query metric can be determined; the differences between the second query strategy and the actual query strategy can be determined based on the sub-differences under at least one preset query metric; and the pre-built query strategy recommendation model can be tuned based on the differences between the second query strategy and the actual query strategy to train the query strategy recommendation model.
[0074] The preset query metrics may include at least one of the following: query duration and resource usage.
[0075] Query duration can be understood as the length of time a query takes. Resource usage can include Central Processing Unit (CPU) utilization, memory usage, and disk input / output (I / O) operation frequency.
[0076] In some embodiments, for each preset query metric, the difference between the second query strategy and the actual query strategy under that preset query metric can be used as a sub-difference case.
[0077] In some embodiments, the weighted sum of sub-differences under at least one preset query metric can be used as the difference between the second query strategy and the actual query strategy.
[0078] In some embodiments, a loss function can be constructed based on the difference between the second query strategy and the actual query strategy, and the parameters of a pre-built query strategy recommendation model can be tuned based on the loss function to train the query strategy recommendation model. It is worth noting that this application does not limit the specific method for constructing the loss function.
[0079] In the above embodiments, by considering the sub-differences between the second query strategy and the actual query strategy under at least one preset query metric, the model can be optimized in at least one dimension, which can not only reduce query time but also optimize resource utilization, achieving a comprehensive performance improvement.
[0080] In the above embodiments, training the query strategy recommendation model based on the sample SQL statement, the corresponding second execution plan, and the actual query strategy of the sample SQL statement enables the model to comprehensively capture the global and local characteristics of the SQL statement, accurately identify key factors affecting performance, and thus predict the optimal query strategy.
[0081] Based on the technical solutions of the above embodiments, this application also provides another optional embodiment, in which the data query method is refined.
[0082] See Figure 4 The data query methods shown also include:
[0083] S410: Obtain at least one second SQL statement, the third execution plan corresponding to at least one second SQL statement, and the actual query strategy of at least one second SQL statement generated by the target database during the current time period.
[0084] In some embodiments, slow query log data of the target database within the current time period can be obtained. From the slow query log data, at least one second SQL statement, at least one third execution plan corresponding to the second SQL statement, and at least one actual query strategy of the second SQL statement can be extracted.
[0085] S420: Input at least one second SQL statement, the third execution plan corresponding to at least one second SQL statement, and the actual query strategy of at least one second SQL statement into the pre-trained query strategy recommendation model to optimize the pre-trained query strategy recommendation model.
[0086] In some embodiments, at least one second SQL statement and the third execution plan corresponding to at least one second SQL statement can be used as input to the training sample, and the actual query strategy of at least one second SQL statement can be used as output to train the query strategy recommendation model, thereby optimizing the query strategy recommendation model.
[0087] In the above embodiments, by optimizing the query strategy recommendation model, the query strategy recommendation model can continuously optimize the query strategy, thereby improving the accuracy and effectiveness of subsequent query strategies.
[0088] Based on the technical solutions of the above embodiments, this application also provides another optional embodiment, in which the data query method is described in detail.
[0089] See Figure 5 The data query methods shown include:
[0090] S501, obtain the sample SQL statement whose query time exceeds the preset time, the second execution plan corresponding to the sample SQL statement, and the actual query strategy of the sample SQL statement from the sample database.
[0091] S502 uses a first feature extraction network to extract the first feature from the sample SQL statement to obtain the SQL statement features.
[0092] S503 uses a second feature extraction network to extract second features from the second execution plan, thus obtaining the execution plan features.
[0093] S504 employs a feature enhancement network to perform self-attention enhancement on SQL statement features, resulting in enhanced SQL statement features.
[0094] S505 employs a feature fusion network to fuse SQL statement enhancement features and execution plan features to obtain fused features.
[0095] S506 uses a prediction network to predict the second query strategy corresponding to the sample SQL statement based on fusion features.
[0096] S507, determine the sub-differences between the second query strategy and the actual query strategy under at least one preset query metric.
[0097] S508, determine the difference between the second query strategy and the actual query strategy based on the sub-difference situation under at least one preset query indicator.
[0098] S509, Based on the differences between the second query strategy and the actual query strategy, the parameters of the pre-built query strategy recommendation model are tuned to train the query strategy recommendation model.
[0099] S510: Obtain the first SQL statement in the target database whose query time exceeds the preset time, and the first execution plan corresponding to the first SQL statement.
[0100] S511, the first SQL statement and the first execution plan are input into the pre-trained query strategy recommendation model to obtain the first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network.
[0101] S512, Execute the first SQL statement according to the first query strategy.
[0102] S513, obtain at least one second SQL statement, at least one third execution plan corresponding to the second SQL statement, and at least one actual query strategy of the second SQL statement generated by the target database in the current time period.
[0103] S514: Input at least one second SQL statement, the third execution plan corresponding to at least one second SQL statement, and the actual query strategy of at least one second SQL statement into the pre-trained query strategy recommendation model, and optimize the pre-trained query strategy recommendation model.
[0104] It should be understood that although the steps in the flowcharts of the embodiments described above are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the embodiments described above may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages of other steps.
[0105] Based on the same inventive concept, this application also provides a data query apparatus for implementing the data query method described above. The solution provided by this apparatus is similar to the implementation scheme described in the above method; therefore, the specific limitations in one or more data query apparatus embodiments provided below can be found in the limitations of the data query method described above, and will not be repeated here.
[0106] In one exemplary embodiment, such as Figure 6 As shown, a data query device is provided, comprising: a first acquisition module 610, a first input module 620, and an execution module 630, wherein:
[0107] The first acquisition module 610 is used to acquire the first SQL statement in the target database whose query time exceeds a preset time, and the first execution plan corresponding to the first SQL statement;
[0108] The first input module 620 is used to input the first SQL statement and the first execution plan into a pre-trained query strategy recommendation model to obtain a first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network;
[0109] The execution module 630 is used to execute the first SQL statement according to the first query strategy.
[0110] In one embodiment, the query strategy recommendation model is trained using the following steps: obtaining sample SQL statements with query durations exceeding a preset duration, the second execution plan corresponding to the sample SQL statements, and the actual query strategy of the sample SQL statements from the sample database; inputting the sample SQL statements and the second execution plan into the pre-built query strategy recommendation model to obtain the second query strategy corresponding to the sample SQL statements; and adjusting the parameters of the pre-built query strategy recommendation model based on the differences between the second query strategy and the actual query strategy to train the query strategy recommendation model.
[0111] In one embodiment, the query strategy recommendation model includes: a first feature extraction network, a second feature extraction network, a feature enhancement network, a feature fusion network, and a prediction network. The process involves inputting a sample SQL statement and a second execution plan into the pre-built query strategy recommendation model to obtain a second query strategy corresponding to the sample SQL statement. This includes: using the first feature extraction network to extract first features from the sample SQL statement to obtain SQL statement features; using the second feature extraction network to extract second features from the second execution plan to obtain execution plan features; using the feature enhancement network to perform self-attention enhancement on the SQL statement features to obtain enhanced SQL statement features; using the feature fusion network to fuse the enhanced SQL statement features and the execution plan features to obtain fused features; and using the prediction network to predict the second query strategy corresponding to the sample SQL statement based on the fused features.
[0112] In one embodiment, the pre-built query strategy recommendation model is tuned based on the differences between the second query strategy and the actual query strategy to train the query strategy recommendation model. This includes: determining the sub-differences between the second query strategy and the actual query strategy under at least one preset query indicator; determining the differences between the second query strategy and the actual query strategy based on the sub-differences under at least one preset query indicator; and tuning the pre-built query strategy recommendation model based on the differences between the second query strategy and the actual query strategy to train the query strategy recommendation model.
[0113] In one embodiment, the preset query metrics include at least one of the following: query duration and resource usage.
[0114] In one embodiment, the above apparatus further includes: a second acquisition module, configured to acquire at least one second SQL statement, a third execution plan corresponding to the at least one second SQL statement, and an actual query strategy of the at least one second SQL statement generated by the target database in the current time period; and a second input module, configured to input the at least one second SQL statement, the third execution plan corresponding to the at least one second SQL statement, and the actual query strategy of the at least one second SQL statement into a pre-trained query strategy recommendation model, and optimize the pre-trained query strategy recommendation model.
[0115] Each module in the aforementioned data query device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device, or stored in the memory of a computer device as software, so that the processor can call and execute the operations corresponding to each module.
[0116] In one exemplary embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as follows: Figure 7 As shown, the computer device includes a processor, memory, input / output interfaces (I / O), and a communication interface. The processor, memory, and I / O interfaces are connected via a system bus, and the communication interface is also connected to the system bus via the I / O interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and a database. The internal memory provides the environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The database stores first SQL statements, first execution plans, first query strategies, and other related data. The I / O interfaces are used for exchanging information between the processor and external devices. The communication interface is used for communicating with external terminals via a network connection. When the computer program is executed by the processor, it implements a data query method.
[0117] Those skilled in the art will understand that Figure 7 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.
[0118] In one exemplary embodiment, a computer device is provided, including a memory and a processor. The memory stores a computer program, and the processor executes the computer program to implement the steps of the data query method provided in any of the above embodiments or to implement the following steps:
[0119] Obtain the first SQL statement in the target database whose query time exceeds a preset time, and the first execution plan corresponding to the first SQL statement;
[0120] The first SQL statement and the first execution plan are input into a pre-trained query strategy recommendation model to obtain the first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network;
[0121] Execute the first SQL statement according to the first query strategy.
[0122] In one embodiment, the processor, when executing a computer program, also performs the following steps:
[0123] Obtain sample SQL statements from the sample database whose query duration exceeds a preset duration, the second execution plan corresponding to the sample SQL statements, and the actual query strategy of the sample SQL statements; input the sample SQL statements and the second execution plan into a pre-built query strategy recommendation model to obtain the second query strategy corresponding to the sample SQL statements; adjust the parameters of the pre-built query strategy recommendation model based on the difference between the second query strategy and the actual query strategy to train the query strategy recommendation model.
[0124] In one embodiment, the query strategy recommendation model includes: a first feature extraction network, a second feature extraction network, a feature enhancement network, a feature fusion network, and a prediction network; the processor, when executing the computer program, further implements the following steps:
[0125] A first feature extraction network is used to extract the first feature from the sample SQL statement to obtain the SQL statement feature; a second feature extraction network is used to extract the second feature from the second execution plan to obtain the execution plan feature; a feature enhancement network is used to enhance the SQL statement feature with self-attention to obtain the SQL statement enhanced feature; a feature fusion network is used to fuse the SQL statement enhanced feature and the execution plan feature to obtain the fused feature; and a prediction network is used to predict the second query strategy corresponding to the sample SQL statement based on the fused feature.
[0126] In one embodiment, the processor, when executing a computer program, also performs the following steps:
[0127] Determine the sub-differences between the second query strategy and the actual query strategy under at least one preset query indicator; based on the sub-differences under at least one preset query indicator, determine the differences between the second query strategy and the actual query strategy; based on the differences between the second query strategy and the actual query strategy, tune the parameters of the pre-built query strategy recommendation model to train the query strategy recommendation model.
[0128] In one embodiment, the preset query metrics include at least one of the following: query duration and resource usage.
[0129] In one embodiment, the processor, when executing a computer program, also performs the following steps:
[0130] Obtain at least one second SQL statement, the corresponding third execution plan, and the actual query strategy of at least one second SQL statement generated by the target database within the current time period; input the at least one second SQL statement, the corresponding third execution plan, and the actual query strategy of at least one second SQL statement into a pre-trained query strategy recommendation model, and optimize the pre-trained query strategy recommendation model.
[0131] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon. When executed by a processor, the computer program implements the steps of the data query method provided in any of the above embodiments or implements the following steps:
[0132] Obtain the first SQL statement in the target database whose query time exceeds a preset time, and the first execution plan corresponding to the first SQL statement;
[0133] The first SQL statement and the first execution plan are input into a pre-trained query strategy recommendation model to obtain the first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network;
[0134] Execute the first SQL statement according to the first query strategy.
[0135] In one embodiment, when the computer program is executed by a processor, it further performs the following steps:
[0136] Obtain sample SQL statements from the sample database whose query duration exceeds a preset duration, the second execution plan corresponding to the sample SQL statements, and the actual query strategy of the sample SQL statements; input the sample SQL statements and the second execution plan into a pre-built query strategy recommendation model to obtain the second query strategy corresponding to the sample SQL statements; adjust the parameters of the pre-built query strategy recommendation model based on the difference between the second query strategy and the actual query strategy to train the query strategy recommendation model.
[0137] In one embodiment, the query strategy recommendation model includes: a first feature extraction network, a second feature extraction network, a feature enhancement network, a feature fusion network, and a prediction network; when the computer program is executed by a processor, it further implements the following steps: using the first feature extraction network to extract first features from the sample SQL statement to obtain SQL statement features; using the second feature extraction network to extract second features from the second execution plan to obtain execution plan features; using the feature enhancement network to perform self-attention enhancement on the SQL statement features to obtain SQL statement enhancement features; using the feature fusion network to fuse the SQL statement enhancement features and the execution plan features to obtain fused features; and using the prediction network to predict the second query strategy corresponding to the sample SQL statement based on the fused features.
[0138] In one embodiment, when the computer program is executed by a processor, it further performs the following steps:
[0139] Determine the sub-differences between the second query strategy and the actual query strategy under at least one preset query indicator; based on the sub-differences under at least one preset query indicator, determine the differences between the second query strategy and the actual query strategy; based on the differences between the second query strategy and the actual query strategy, tune the parameters of the pre-built query strategy recommendation model to train the query strategy recommendation model.
[0140] In one embodiment, the preset query metrics include at least one of the following: query duration and resource usage.
[0141] In one embodiment, when the computer program is executed by a processor, it further performs the following steps:
[0142] Obtain at least one second SQL statement, the corresponding third execution plan, and the actual query strategy of at least one second SQL statement generated by the target database within the current time period; input the at least one second SQL statement, the corresponding third execution plan, and the actual query strategy of at least one second SQL statement into a pre-trained query strategy recommendation model, and optimize the pre-trained query strategy recommendation model.
[0143] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the steps of the data query method provided in any of the above embodiments, or implements the following steps:
[0144] Obtain the first SQL statement in the target database whose query time exceeds a preset time, and the first execution plan corresponding to the first SQL statement;
[0145] The first SQL statement and the first execution plan are input into a pre-trained query strategy recommendation model to obtain the first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network;
[0146] Execute the first SQL statement according to the first query strategy.
[0147] In one embodiment, when the computer program is executed by a processor, it further performs the following steps:
[0148] Obtain sample SQL statements from the sample database whose query duration exceeds a preset duration, the second execution plan corresponding to the sample SQL statements, and the actual query strategy of the sample SQL statements; input the sample SQL statements and the second execution plan into a pre-built query strategy recommendation model to obtain the second query strategy corresponding to the sample SQL statements; adjust the parameters of the pre-built query strategy recommendation model based on the difference between the second query strategy and the actual query strategy to train the query strategy recommendation model.
[0149] In one embodiment, the query strategy recommendation model includes: a first feature extraction network, a second feature extraction network, a feature enhancement network, a feature fusion network, and a prediction network; when the computer program is executed by a processor, it further implements the following steps: using the first feature extraction network to extract first features from the sample SQL statement to obtain SQL statement features; using the second feature extraction network to extract second features from the second execution plan to obtain execution plan features; using the feature enhancement network to perform self-attention enhancement on the SQL statement features to obtain SQL statement enhancement features; using the feature fusion network to fuse the SQL statement enhancement features and the execution plan features to obtain fused features; and using the prediction network to predict the second query strategy corresponding to the sample SQL statement based on the fused features.
[0150] In one embodiment, when the computer program is executed by a processor, it further performs the following steps:
[0151] Determine the sub-differences between the second query strategy and the actual query strategy under at least one preset query indicator; based on the sub-differences under at least one preset query indicator, determine the differences between the second query strategy and the actual query strategy; based on the differences between the second query strategy and the actual query strategy, tune the parameters of the pre-built query strategy recommendation model to train the query strategy recommendation model.
[0152] In one embodiment, the preset query metrics include at least one of the following: query duration and resource usage.
[0153] In one embodiment, when the computer program is executed by a processor, it further performs the following steps:
[0154] Obtain at least one second SQL statement, the corresponding third execution plan, and the actual query strategy of at least one second SQL statement generated by the target database within the current time period; input the at least one second SQL statement, the corresponding third execution plan, and the actual query strategy of at least one second SQL statement into a pre-trained query strategy recommendation model, and optimize the pre-trained query strategy recommendation model.
[0155] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, artificial intelligence (AI) processors, etc., and are not limited to these.
[0156] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.
[0157] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of this patent application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.
Claims
1. A data query method, characterized in that, The method includes: Obtain the first Structured Query Language (SQL) statement in the target database whose query time exceeds a preset time, and the first execution plan corresponding to the first SQL statement; The first SQL statement and the first execution plan are input into a pre-trained query strategy recommendation model to obtain a first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network; Execute the first SQL statement according to the first query strategy.
2. The method according to claim 1, characterized in that, The query strategy recommendation model is trained using the following steps: Obtain sample SQL statements from the sample database whose query duration exceeds the preset duration, the second execution plan corresponding to the sample SQL statements, and the actual query strategy of the sample SQL statements; The sample SQL statement and the second execution plan are input into a pre-built query strategy recommendation model to obtain the second query strategy corresponding to the sample SQL statement; Based on the differences between the second query strategy and the actual query strategy, the parameters of the pre-built query strategy recommendation model are tuned to train the query strategy recommendation model.
3. The method according to claim 2, characterized in that, The query strategy recommendation model includes: a first feature extraction network, a second feature extraction network, a feature enhancement network, a feature fusion network, and a prediction network; The step of inputting the sample SQL statement and the second execution plan into a pre-built query strategy recommendation model to obtain the second query strategy corresponding to the sample SQL statement includes: The first feature extraction network is used to perform first feature extraction on the sample SQL statement to obtain SQL statement features; The second feature extraction network is used to extract the second feature from the second execution plan to obtain the execution plan features; The feature enhancement network described above is used to perform self-attention enhancement on the SQL statement features to obtain enhanced SQL statement features. The feature fusion network is used to fuse the SQL statement enhancement features and the execution plan features to obtain fused features; Using the prediction network, a second query strategy corresponding to the sample SQL statement is predicted based on the fusion features.
4. The method according to claim 2, characterized in that, The step of tuning the parameters of the pre-built query strategy recommendation model based on the difference between the second query strategy and the actual query strategy, in order to train the query strategy recommendation model, includes: Determine the sub-differences between the second query strategy and the actual query strategy under at least one preset query metric; Based on the sub-differences under the at least one preset query indicator, determine the differences between the second query strategy and the actual query strategy; Based on the differences between the second query strategy and the actual query strategy, the parameters of the pre-built query strategy recommendation model are tuned to train the query strategy recommendation model.
5. The method according to claim 4, characterized in that, The preset query indicators include at least one of the following: query duration and resource usage.
6. The method according to any one of claims 1-5, characterized in that, The method further includes: Obtain at least one second SQL statement generated by the target database during the current time period, the third execution plan corresponding to the at least one second SQL statement, and the actual query strategy of the at least one second SQL statement; The at least one second SQL statement, the third execution plan corresponding to the at least one second SQL statement, and the actual query strategy of the at least one second SQL statement are input into the pre-trained query strategy recommendation model to optimize the pre-trained query strategy recommendation model.
7. A data query device, characterized in that, The device includes: The first acquisition module is used to acquire the first SQL statement in the target database whose query time exceeds a preset time, and the first execution plan corresponding to the first SQL statement; The first input module is used to input the first SQL statement and the first execution plan into a pre-trained query strategy recommendation model to obtain a first query strategy; wherein, the query strategy recommendation model is constructed based on a bidirectional transformer network; The execution module is used to execute the first SQL statement according to the first query strategy.
8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 6.
9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.
10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.