A database optimization query method, device and equipment based on artificial intelligence

Abstract

The present invention provides an artificial intelligence-based database optimization query method, device, and equipment, relating to the field of computer technology, and is applied to a database comprising multiple shards. The database optimization query method comprises: obtaining a user's query instruction and multidimensional feature data of each shard, parsing the query instruction to obtain multiple calculation factors; performing multidimensional dynamic decision processing on each calculation factor based on the multidimensional feature data to obtain execution probability data of the corresponding calculation factor on different shards; determining a pre-execution plan for executing the corresponding calculation factor on each shard based on the execution probability data; obtaining corresponding location prediction information based on the pre-execution results corresponding to each pre-execution plan based on a preset location prediction model; and sorting the pre-execution results based on all location prediction information to obtain a final query result. The present invention not only improves query efficiency, reduces data transmission and computational redundancy, but also enhances resource utilization efficiency.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a database optimization query method, device and equipment based on artificial intelligence. Background Art

[0002] With the rapid development of big data and artificial intelligence technologies, distributed databases have become core infrastructure for supporting massive data storage and high-concurrency real-time queries. In particular, in scenarios such as real-time AI training and dynamic business analysis, databases must efficiently process queries involving multi-table joins, nested aggregations, and complex filtering conditions. Traditional distributed database query optimization techniques primarily rely on two basic mechanisms: index construction and cache preloading strategies based on static metadata; and query execution plan generation using heuristic rules or cost models. While these approaches have reduced latency for simple queries to some extent, they still suffer from common bottlenecks such as data locality loss, computational redundancy, and static optimization limitations. For example, traditional sharding strategies ignore dynamic load and data hotspots, leading to frequent cross-node queries and network overhead accounting for over 60% of total latency. Complex queries require repeated computation of intermediate results across shards, resulting in less than 40% resource utilization. Cost models based on historical statistics cannot adapt to real-time load fluctuations, resulting in performance degradation of over 50% when switching between hot and cold data. Therefore, a query method is urgently needed to address the dual optimization issues of data read and computation efficiency in a sharded environment, fundamentally improving the response efficiency and resource utilization of complex queries. Summary of the Invention

[0003] The problem solved by the present invention is one or more of the above-mentioned related technical problems.

[0004] To solve the above problems, the present invention provides a database optimization query method, device and equipment based on artificial intelligence.

[0005] In a first aspect, the present invention provides an artificial intelligence-based database optimization query method, which is applied to a database, wherein the database includes multiple shards; the database optimization query method includes:

[0006] Obtaining a user's query instruction and the multi-dimensional feature data of each of the shards, and parsing the query instruction to obtain a plurality of calculation factors;

[0007] Based on each of the multidimensional feature data, a multidimensional dynamic decision process is performed on each of the calculation factors to obtain execution probability data of the corresponding calculation factor on different shards;

[0008] Determining, based on each of the execution probability data, a pre-execution plan for executing the corresponding calculation factor on each of the shards, and executing each of the pre-execution plans to obtain a corresponding pre-execution result;

[0009] Based on a preset location prediction model, obtaining corresponding location prediction information according to each of the pre-execution results;

[0010] The pre-execution results are sorted according to all the position prediction information to obtain a final query result.

[0011] Optionally, performing multi-dimensional dynamic decision processing on each of the calculation factors based on each of the multi-dimensional feature data to obtain execution probability data of the corresponding calculation factor on different shards includes:

[0012] Determine corresponding weight data according to the calculation factor;

[0013] A comprehensive score of each of the shards is determined based on each of the multidimensional feature data and the corresponding weight data, and execution probability data of the corresponding calculation factors on different shards is determined based on all of the comprehensive scores.

[0014] Optionally, the multidimensional feature data includes network distance, load data, popularity data, relevance data, and freshness data; determining a comprehensive score for each shard based on each multidimensional feature data and the corresponding weight data, and determining execution probability data of the corresponding calculation factor on different shards based on all the comprehensive scores, includes:

[0015] Determine the corresponding comprehensive score according to the network distance, the load data, the relevance data, the popularity data, the freshness data and the corresponding weight data of each shard;

[0016] Acquiring temperature parameters, and determining corresponding index data according to each of the comprehensive scores and the temperature parameters;

[0017] Total index data is determined based on all the index data, and execution probability data of the corresponding calculation factor on different shards is determined based on each index data and the total index data.

[0018] Optionally, determining execution probability data of the corresponding calculation factor on different shards according to each index data and the total index data includes:

[0019] By formula 1, the execution probability data of the corresponding calculation factor on different shards is determined according to each index data and the total index data;

[0020] Wherein, the formula 1 is:

[0021] ;

[0022] in, For the jth calculation factor in the shard Execution probability data on is the corresponding index data, is the total index data corresponding to the jth calculation factor, T is the temperature parameter, For the jth calculation factor in the shard The corresponding comprehensive score is is the i-th shard, and N is the total number of shards.

[0023] Optionally, the preset position prediction model includes a Transformer module and a multi-head attention mechanism; the pre-execution result includes feature data; and obtaining corresponding position prediction information according to each pre-execution result based on the preset position prediction model includes:

[0024] Encoding each of the feature data using the Transformer module to obtain corresponding encoded feature data;

[0025] Each of the encoded feature data is processed through the multi-head attention mechanism to obtain the corresponding position prediction information.

[0026] Optionally, the feature data includes corresponding original index data; and encoding the feature data by the Transformer module to obtain corresponding encoded feature data includes:

[0027] Convert each of the feature data to obtain corresponding feature matrix data;

[0028] Corresponding encoding feature data is determined according to the corresponding original index data and the feature matrix data.

[0029] Optionally, the sorting of the pre-execution results of all the location prediction information to obtain a final query result includes:

[0030] performing deduplication processing on the position prediction information to obtain temporary position prediction information;

[0031] When different records with different calculation factors in the temporary location prediction information have the same predicted location, the temporary location prediction information is rearranged based on the corresponding freshness data to obtain the final query result.

[0032] In a second aspect, the present invention provides an artificial intelligence-based database optimization query device, which is applied to a database, wherein the database includes multiple shards; the query device includes:

[0033] an acquisition unit, configured to acquire a user's query instruction and the multi-dimensional feature data of each of the shards, and parse the query instruction to obtain a plurality of calculation factors;

[0034] a processing unit configured to perform multidimensional dynamic decision processing on each of the calculation factors based on each of the multidimensional feature data to obtain execution probability data of the corresponding calculation factor on different shards; determine a pre-execution plan for executing the corresponding calculation factor on each of the shards based on each of the execution probability data; and execute each of the pre-execution plans to obtain a corresponding pre-execution result;

[0035] A prediction unit, configured to obtain corresponding position prediction information according to each of the pre-execution results based on a preset position prediction model;

[0036] A sorting unit is used to sort the pre-execution results according to all the position prediction information to obtain a final query result.

[0037] In a third aspect, the present invention provides an artificial intelligence-based database optimization query device, comprising a memory and a processor; the memory is used to store a computer program; the processor is used to implement the artificial intelligence-based database optimization query method as described in the first aspect when executing the computer program.

[0038] In a fourth aspect, the present invention provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the artificial intelligence-based database optimization query method as described in the first aspect is implemented.

[0039] The beneficial effects of the artificial intelligence-based database optimization query method, device, system, and storage medium of the present invention are:

[0040] By parsing the user's query and dividing it into multiple calculation factors, dynamic decision-making is performed based on the multidimensional feature data of each shard. The execution probability data for each calculation factor on different shards is obtained, and a pre-execution plan is formulated accordingly. Guided by the pre-execution plan, each calculation factor is executed on the optimal shard, ensuring efficient query performance. Furthermore, a preset location prediction model provides location prediction information for each record based on the pre-execution results. This helps to properly sort all data during the final result sorting phase, ensuring that the query results returned to the user are orderly and accurate. This approach not only improves query efficiency and reduces data transmission and computational redundancy, but also enhances resource utilization and improves overall system performance.

[0041] Specifically, the present invention achieves atomic decomposition of complex queries by parsing user query instructions into multiple independent computational factors. This decomposition enables independent execution of each computational factor, significantly reducing cross-node data transmission, effectively reducing computational redundancy, and improving resource utilization. Secondly, it utilizes a Softmax function combined with multi-dimensional feature data (including network latency, server load, and data popularity) to make multi-dimensional dynamic decisions, accurately determining the execution probability of each computational factor on different shards and generating an optimal execution plan. This process addresses the data locality loss problem caused by traditional sharding strategies that ignore dynamic load and data hotspots, significantly reducing cross-node query frequency and network overhead. Finally, the Transformer model is introduced to predict the location of pre-execution results, pre-determining the data's position in the final results, optimizing the result sorting process and significantly reducing sorting time. The application of this model effectively overcomes the limitations of static optimization in traditional query optimization techniques, enhances the system's ability to handle complex queries, and improves its adaptability and flexibility. Even with dynamic loads and complex query scenarios, the present invention consistently maintains excellent performance, ensuring users can quickly obtain accurate query results, thereby significantly improving the user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] Figure 1 A flowchart of a database optimization query method based on artificial intelligence according to an embodiment of the present invention;

[0043] Figure 2 This is a schematic diagram of the structure of an artificial intelligence-based database optimization query device according to an embodiment of the present invention;

[0044] Figure 3 A schematic diagram of an artificial intelligence-based database optimization query device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0045] To make the above-mentioned objects, features, and advantages of the present invention more clearly understood, specific embodiments of the present invention are described in detail below with reference to the accompanying drawings. Although certain embodiments of the present invention are shown in the accompanying drawings, it should be understood that the present invention can be implemented in various forms and should not be construed as being limited to the embodiments described herein. Instead, these embodiments are provided to provide a more thorough and complete understanding of the present invention. It should be understood that the drawings and embodiments of the present invention are for illustrative purposes only and are not intended to limit the scope of protection of the present invention.

[0046] It should be understood that the various steps described in the method embodiments of the present invention may be performed in different orders and / or in parallel. In addition, the method embodiments may include additional steps and / or omit the steps shown. The scope of the present invention is not limited in this respect.

[0047] The term "including" and its variations used in this document are open inclusions, that is, "including but not limited to"; the term "based on" means "based at least in part on"; the term "one embodiment" means "at least one embodiment"; the term "another embodiment" means "at least one other embodiment"; the term "some embodiments" means "at least some embodiments"; the term "optionally" means "optional embodiments". The relevant definitions of other terms will be given in the following description. It should be noted that the concepts of "first", "second", etc. mentioned in the present invention are only used to distinguish different devices, modules or units, and are not used to limit the order or interdependence of the functions performed by these devices, modules or units.

[0048] It should be noted that the modifications of "one" and "multiple" mentioned in the present invention are illustrative rather than restrictive. Those skilled in the art should understand that unless otherwise clearly indicated in the context, it should be understood as "one or more".

[0049] The names of the messages or information exchanged between multiple devices in the embodiments of the present invention are only used for illustrative purposes and are not used to limit the scope of these messages or information.

[0050] In response to the problems existing in the above-mentioned related technologies, the embodiments of the present invention provide a database optimization query method device, system and storage medium based on artificial intelligence.

[0051] like Figure 1 As shown, an embodiment of the present invention provides an artificial intelligence-based database optimization query method, which is applied to a database, wherein the database includes multiple shards; the query method includes:

[0052] Step S100: obtaining a user's query instruction and multi-dimensional feature data of each shard, and parsing the query instruction to obtain a plurality of calculation factors.

[0053] Specifically, the user's query instruction is obtained. For example, the user submits a query request through a database client or application program interface (API), which is usually in the form of an SQL statement. For example, the user may enter the following query statement:

[0054] SELECT u.name, SUM(o.amount); (This part specifies the columns to be returned in the query results.

[0055] u.name means selecting the name column from the users table, that is, the user's name.

[0056] SUM(o.amount) means to sum the amount column in the orders table to calculate the total order amount for each user).

[0057] FROM users u; (Specify the main table for query as users, and give this table an alias u for easy reference in subsequent queries).

[0058] JOIN orders o ON u.id = o.user_id; (This part indicates the join operation on the users table and the orders table. JOIN orders o means joining the orders table with the users table and giving the orders table an alias o. ON u.id = o.user_id specifies the join condition, that is, the id column in the users table is equal to the user_id column in the orders table. Through this condition, each user is associated with their orders).

[0059] GROUP BY u.id; (specifies that the results are grouped by the id column in the users table. After grouping, the query will return each user's name and the user's total order amount. SUM(o.amount) is the total order amount calculated for each user group).

[0060] Multi-dimensional feature data often includes network latency, server load, data popularity, shard freshness data, etc. These data reflect the current status and data characteristics of each shard. Among them, network latency: The network round-trip time (RTT) from the coordination node to each shard node is measured through network monitoring tools. Server load: The CPU utilization and memory usage of each shard node are obtained through system monitoring tools. Data popularity: The query frequency of each shard is counted through query logs to reflect the access popularity of the data. Shard freshness data: The most recent update time of each shard is obtained through the data update log to calculate the data freshness data.

[0061] To parse a query, you can use a SQL parsing tool (such as Apache Calcite) to parse the SQL query into an abstract syntax tree (AST), extracting information such as the tables, fields, and conditions in the query. A logical execution plan is then generated based on the AST, describing the query's operational flow, such as the order and relationships of operations like table scans, joins, and aggregations.

[0062] Based on the logical execution plan, the query is broken down into multiple independent calculation factors. For example, the above SQL query can be broken down into the following calculation factors:

[0063] Factor_SCAN_users: Scans the “users” table.

[0064] Factor_SCAN_orders: Scans the “orders” table.

[0065] Factor_JOIN_users_orders: Joins the “users“ table and the “orders“ table.

[0066] Factor_AGG_sum_amount: performs sum aggregation on the “amount” field.

[0067] By parsing complex query instructions into multiple independent calculation factors, each calculation factor can be executed independently. This reduces cross-node data transmission and improves the parallelism and efficiency of query execution. At the same time, by obtaining multi-dimensional feature data from each shard, the system can grasp the status and data characteristics of each shard in real time, providing support for subsequent dynamic decision-making and enhancing the system's adaptability to dynamic loads and complex query scenarios. In addition, parsing query instructions and breaking down calculation factors can avoid repeatedly calculating intermediate results on each shard, reducing computational redundancy and improving resource utilization. Overall, this approach can efficiently process user query requests and provide a foundation for subsequent dynamic decision-making and optimization steps.

[0068] Step S200: Based on each of the multidimensional feature data, perform multidimensional dynamic decision processing on each of the calculation factors to obtain execution probability data corresponding to the calculation factor on different shards.

[0069] Specifically, multidimensional feature data often includes network latency, server load, data popularity, shard freshness data, etc., and corresponding dynamic decision-making processing methods are adopted, such as the Softmax multidimensional dynamic decision-making method, which combines multidimensional feature data to perform dynamic decision-making processing on the calculation factors to obtain the execution probability data of each calculation factor on different shards.

[0070] Through multi-dimensional dynamic decision processing, the system selects the optimal shard for each calculation factor based on real-time, multi-dimensional feature data. This not only reduces cross-node data transmission but also improves the parallelism and efficiency of query execution. The system dynamically adjusts the execution plan based on the load and resource status of the shard, avoiding resource waste and significantly improving resource utilization. Furthermore, this process can adapt in real time to changes in shard status, such as fluctuations in network latency and server load, thereby enhancing the system's adaptability to dynamic loads and complex query scenarios. At the same time, the system comprehensively considers multi-dimensional feature data, including data popularity and freshness, and prioritizes shard execution calculation factors with high data relevance and strong timeliness, thereby ensuring the accuracy and timeliness of query results. In summary, these steps can efficiently process complex query requests, optimize query execution plans, and comprehensively improve query efficiency, resource utilization, and overall system performance.

[0071] Step S300: determining a pre-execution plan for executing the corresponding calculation factor on each shard according to each execution probability data, and executing each pre-execution plan to obtain a corresponding pre-execution result.

[0072] Specifically, the execution probability data obtained from step S200 represents the execution efficiency and applicability of each calculation factor on different shards.

[0073] For example, there is a calculation factor Factor_AGG_sum_amount, and shards Shard1, Shard2, and Shard3. The execution probability data may be as follows:

[0074] Shard1: 0.6; Shard2: 0.3; Shard3: 0.1;

[0075] Sort the shards based on the execution probability data, prioritizing the shards with the highest execution probability. Based on the above execution probability, the priority order is Shard1 > Shard2 > Shard3.

[0076] Develop an execution plan for each compute factor on the shards based on priority, clearly defining which compute factors will be executed on which shards. Consider the current load and resource availability of the shards, and rationally allocate computing resources to ensure smooth execution of high-priority shards.

[0077] For example, for Factor_AGG_sum_amount, the pre-execution plan is as follows:

[0078] Shard1: First executed on Shard1, the main computing resources are allocated.

[0079] Shard2: If Shard1 has insufficient resources or is overloaded, the job will be executed on Shard2.

[0080] Shard3: Finally, the process is executed on Shard3 as a backup option.

[0081] During execution, the pre-execution plan is dynamically adjusted based on real-time monitoring data to respond to changes in shard status. A load balancing algorithm is also used to ensure balanced resource utilization across shards, preventing some shards from being overloaded while others remain idle.

[0082] For example, suppose there are two calculation factors:

[0083] Factor_JOIN_users_orders and Factor_AGG_sum_amount, as well as three shards, Shard1, Shard2, and Shard3. The execution probability data is as follows:

[0084]

[0085] Based on these execution probability data, the pre-execution plan is formulated as follows:

[0086] 1. Factor_JOIN_users_orders:

[0087] Shard1: Executes first and allocates the main computing resources.

[0088] Shard2: Executed next as a backup.

[0089] Shard3: Executed last and used only when necessary.

[0090] 2. Factor_AGG_sum_amount:

[0091] Shard1: Executes first and allocates the main computing resources.

[0092] Shard2: Executed next as a backup.

[0093] Shard3: Executed last and used only when necessary.

[0094] Through this pre-execution plan, the system can efficiently utilize resources, reduce cross-node data transmission, and improve query execution efficiency.

[0095] Step S400 : Based on a preset location prediction model, corresponding location prediction information is obtained according to each of the pre-execution results.

[0096] Specifically, position prediction models (such as Transformer-based models) are used to predict the ideal position of each record in the final query results. The model input is pre-execution results, including data records and their feature information obtained after applying calculation factors to each shard. This feature information may include the record's hash value, normalized order amount, and timestamp encoding value. These features help the model understand the relative importance and order of records.

[0097] The model generates a position prediction for each record based on the input pre-execution results and feature information. This is usually a vector representing the expected position of the record in the final result.

[0098] For example, if the model prediction result is [3, 1, 5, ..., 2], it means that the first record should be ranked third in the final result, the second record should be ranked first, and so on.

[0099] Accurate location prediction ensures that the order of query results better aligns with user expectations and business logic, thereby improving the accuracy and usability of query results. Predetermining record locations helps reduce the amount of sorting computation required during the final result generation phase, thereby improving overall query performance. Furthermore, the location prediction model dynamically adjusts predictions based on varying query conditions and data characteristics, enhancing its adaptability to complex queries and dynamic data changes. Rapidly returning accurate and ordered query results significantly enhances the user experience.

[0100] That is, through step S400, the position of each record in the final query result can be effectively predicted based on the preset position prediction model, providing an important basis for subsequent result sorting and output, thereby improving the overall efficiency and accuracy of the query.

[0101] Step S500: sorting the pre-execution results according to all the location prediction information to obtain a final query result.

[0102] Specifically, the location prediction information obtained from step S400 is the expected location of each record in the final query result, and its information format is usually in the form of a key-value pair of "<record ID, predicted location>".

[0103] Collects pre-execution results after executing the calculation factors on all shards. The pre-execution results may contain detailed information about the record, such as field values, aggregation results, etc.

[0104] Use the position prediction information generated in step S400 as the sorting basis. Sort the pre-execution results by predicted position. For example, if the predicted position of record R_001 is 3 and the predicted position of record R_002 is 1, then in the final result, R_002 should be ranked before R_001.

[0105] During the sorting process, you may encounter records with the same predicted position. For records with the same predicted position, you can perform a secondary sort based on other business logic or data features (such as data freshness data, record ID, etc.).

[0106] Encapsulate the sorted results into the final query result format and return the final query result to the user or pass it to subsequent processing steps.

[0107] Through precise location prediction and sorting, we ensure that the order of query results not only conforms to business logic but also meets user expectations, thereby improving the accuracy and usability of results. Predetermining record locations reduces the amount of sorting calculations required during the final result generation phase, which helps improve overall query performance and reduce latency. Furthermore, the location prediction model can dynamically adjust based on query conditions and data characteristics, enhancing the system's ability to handle complex queries and adapt to dynamic data changes. Quickly returning accurate and ordered query results significantly improves the user experience, which is particularly important in real-time analysis and AI training scenarios that require immediate responses.

[0108] It should be noted that the information (including but not limited to user device information, user personal information, etc.), data (including but not limited to data used for analysis, stored data, displayed data, etc.) and signals involved in this application are all authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0109] In this embodiment, the AI-based database query optimization method parses the user's query instruction and divides it into multiple calculation factors. Dynamic decision-making is performed based on the multidimensional feature data of each shard, obtaining execution probability data for each calculation factor on different shards and formulating a pre-execution plan accordingly. Guided by the pre-execution plan, each calculation factor is executed on the optimal shard, ensuring efficient query performance. Furthermore, a preset location prediction model can provide location prediction information for each record based on the pre-execution results. This facilitates the proper sorting of all data during the final result sorting phase, ensuring that query results returned to the user are orderly and accurate. This method not only improves query efficiency and reduces data transmission and computational redundancy, but also enhances resource utilization efficiency and improves overall system performance.

[0110] Specifically, the present invention achieves atomic decomposition of complex queries by parsing user query instructions into multiple independent computational factors. This decomposition enables independent execution of each computational factor, significantly reducing cross-node data transmission, effectively reducing computational redundancy, and improving resource utilization. Secondly, it utilizes a Softmax function combined with multi-dimensional feature data (including network latency, server load, and data popularity) to make multi-dimensional dynamic decisions, accurately determining the execution probability of each computational factor on different shards and generating an optimal execution plan. This process addresses the data locality loss problem caused by traditional sharding strategies that ignore dynamic load and data hotspots, significantly reducing cross-node query frequency and network overhead. Finally, the Transformer model is introduced to predict the location of pre-execution results, pre-determining the data's position in the final results, optimizing the result sorting process and significantly reducing sorting time. The application of this model effectively overcomes the limitations of static optimization in traditional query optimization techniques, enhances the system's ability to handle complex queries, and improves its adaptability and flexibility. Even with dynamic loads and complex query scenarios, the present invention consistently maintains excellent performance, ensuring users can quickly obtain accurate query results, thereby significantly improving the user experience.

[0111] Optionally, performing multi-dimensional dynamic decision processing on each of the calculation factors based on each of the multi-dimensional feature data to obtain execution probability data of the corresponding calculation factor on different shards includes:

[0112] Determine corresponding weight data according to the calculation factor;

[0113] A comprehensive score of each of the shards is determined based on each of the multidimensional feature data and the corresponding weight data, and execution probability data of the corresponding calculation factors on different shards is determined based on all of the comprehensive scores.

[0114] Optionally, the multidimensional feature data includes network distance, load data, popularity data, relevance data, and freshness data; determining a comprehensive score for each shard based on each multidimensional feature data and the corresponding weight data, and determining execution probability data of the corresponding calculation factor on different shards based on all the comprehensive scores, includes:

[0115] Determine the corresponding comprehensive score according to the network distance, the load data, the relevance data, the popularity data, the freshness data and the corresponding weight data of each shard;

[0116] Acquiring temperature parameters, and determining corresponding index data according to each of the comprehensive scores and the temperature parameters;

[0117] Total index data is determined based on all the index data, and execution probability data of the corresponding calculation factor on different shards is determined based on each index data and the total index data.

[0118] Optionally, determining execution probability data of the corresponding calculation factor on different shards according to each index data and the total index data includes:

[0119] By formula 1, the execution probability data of the corresponding calculation factor on different shards is determined according to each index data and the total index data;

[0120] Wherein, the formula 1 is:

[0121] ;

[0122] in, For the jth calculation factor in the shard Execution probability data on is the corresponding index data, is the total index data corresponding to the jth calculation factor, T is the temperature parameter, For the jth calculation factor in the shard The corresponding comprehensive score is is the i-th shard, and N is the total number of shards.

[0123] Specifically, the corresponding weight data is determined based on the importance and characteristics of each calculation factor. The weight reflects the degree of influence of different characteristics on the final decision.

[0124] For example, for the calculation factor Factor_AGG_sum_amount, possible weight settings are:

[0125] Network distance: 0.3; load data: 0.2; popularity data: 0.1; freshness data: 0.2; correlation data: 0.1.

[0126] Based on the multi-dimensional feature data of each shard (network distance, load data, popularity data, and freshness data) and the corresponding weight data, the calculation factor Factor_AGG_sum_amount is calculated on each shard for the corresponding comprehensive score. The comprehensive score calculation formula is:

[0127] ;

[0128] in, 、 、 、 and The weight data corresponding to the j-th calculation factor respectively. For sharding The corresponding network distance, For sharding The corresponding load data, For sharding Corresponding heat data, For sharding Freshness data, For sharding The corresponding association data represents the shard The historical co-occurrence frequency of shards involved in the query (such as the number of JOIN operations, the data comes from database records).

[0129] in, ; For sharding Network round-trip delay; is a very small constant (such as ), to avoid division by zero errors.

[0130] ;in, is the attenuation coefficient (usually the default value is 0.02), which controls the sensitivity of the load influence; For sharding CPU utilization of the node (value range [0,1]).

[0131] ;in, The number of query requests per second for the i-th shard, reflecting the frequency of data access; The maximum number of query requests per second across all shards, used to normalize the query request volume across each shard.

[0132] ;in, The data expiration time of the i-th shard; The maximum expiration threshold allowed by the cluster.

[0133] Through precise multi-dimensional dynamic decision processing, the optimal shard can be selected to execute the calculation factor, thereby reducing the amount of data transmission across nodes and improving the parallelism and efficiency of query execution. In addition, the above process dynamically adjusts the execution plan of the calculation factor according to the load and resource status of each shard to avoid resource waste and improve resource utilization. It can also respond to changes in the state of the shard in real time, such as fluctuations in network latency and changes in server load, which enhances the system's adaptability to dynamic loads and complex query scenarios. By considering multi-dimensional feature data, such as data popularity and freshness, more relevant and timely data shards can be prioritized to execute the calculation factor, which improves the accuracy and timeliness of the query results. This method also allows for flexible adjustment of weight and temperature parameters according to different business needs and query characteristics, thereby enhancing the flexibility and scalability of the system.

[0134] Optionally, the preset position prediction model includes a Transformer module and a multi-head attention mechanism; the pre-execution result includes feature data; and obtaining corresponding position prediction information according to each pre-execution result based on the preset position prediction model includes:

[0135] Encoding each of the feature data using the Transformer module to obtain corresponding encoded feature data;

[0136] Each of the encoded feature data is processed through the multi-head attention mechanism to obtain the corresponding position prediction information.

[0137] Optionally, the feature data includes corresponding original index data; and encoding the feature data by the Transformer module to obtain corresponding encoded feature data includes:

[0138] Convert each of the feature data to obtain corresponding feature matrix data;

[0139] Corresponding encoding feature data is determined according to the corresponding original index data and the feature matrix data.

[0140] Specifically, the feature data in each pre-execution result is converted into feature matrix data. This step involves converting raw data (such as user ID, order amount, etc.) into numerical features that the model can process.

[0141] For example, the original data includes user ID (string type) and order amount (numeric type). Feature transformation may include hashing the user ID into a numeric value and normalizing the order amount.

[0142] Determine the encoded feature data based on the corresponding original index data and feature matrix data. This may involve positional encoding to address the order-independence problem of the Transformer model.

[0143] For example: Use sine and cosine functions to generate position codes, add them to the feature matrix, and form coded feature data.

[0144] Each encoded feature data is processed through a multi-head attention mechanism. The multi-head attention mechanism can capture the correlation between different subspaces in the feature data, thereby better understanding the relationship between the data.

[0145] For example: the dimension of the encoded feature data is ,After processing by the 8-head attention mechanism, the position prediction vector of each record is obtained.

[0146] Based on the results of the multi-head attention mechanism, a position prediction is generated for each record. This is typically a vector representing the record's expected position in the final result. For example, the prediction might be [3, 1, 5, ..., 2], indicating that the first record should be ranked third in the final result, the second record should be ranked first, and so on.

[0147] By introducing the Transformer module and multi-head attention mechanism, the model can more accurately capture complex relationships between data, thereby improving the accuracy of location prediction. The multi-head attention mechanism enables the model to simultaneously focus on different aspects of feature data, enhancing the model's expressive power and ability to handle complex queries. Accurate location predictions make computing resource allocation more efficient, reduce unnecessary data transmission and processing, and thus improve overall query performance. In addition, the rapid return of accurate and ordered query results significantly improves the user experience, especially in real-time analysis and AI training scenarios that require immediate response. Therefore, by adopting the Transformer module and multi-head attention mechanism, the location prediction model can more effectively process high-dimensional feature data, improve the accuracy and timeliness of query results, and optimize query performance and user experience.

[0148] Optionally, the sorting of the pre-execution results of all the location prediction information to obtain a final query result includes:

[0149] performing deduplication processing on the position prediction information to obtain temporary position prediction information;

[0150] When different records with different calculation factors in the temporary location prediction information have the same predicted location, the temporary location prediction information is rearranged based on the corresponding freshness data to obtain the final query result.

[0151] Specifically, deduplication ensures that each record's location prediction information is unique and avoids duplication. Deduplication is performed on the collected location prediction information to remove duplicate records. This step ensures that each record is considered only once in the subsequent sorting process.

[0152] For example, if the location prediction information contains duplicate record IDs and predicted locations, only one record will be retained after deduplication.

[0153] The purpose of this reordering process is to address situations where different records with different calculation factors in the temporary location prediction information correspond to the same predicted location. When two or more records have the same predicted location, a secondary sort is performed based on other business logic or data characteristics (such as data freshness) to determine the final order of these records.

[0154] That is, when different records in the temporary location prediction information correspond to the same predicted location, these records are reordered based on their corresponding freshness data. Freshness data reflects the update frequency or timeliness of the data, and generally, data with higher freshness attracts more attention.

[0155] For example, records R_001 and R_002 are both predicted to be at position 3, but R_001 is fresher than R_002. During the reordering process, R_001 will be placed at position 3 first, while R_002 may be adjusted to other positions.

[0156] The rearranged records are packaged into the final query result, and the final query result is returned to the user or passed to subsequent processing steps.

[0157] Calculation factor records refer to data records related to the calculation factors. These records are the data units processed during the pre-execution phase. Each record typically contains a unique record ID and data related to the calculation factor, such as the field values ​​involved in the database query. These records are part of the pre-execution results and are used in subsequent sorting and aggregation steps.

[0158] Pre-execution results refer to intermediate data obtained after executing the pre-execution plan. These results contain data generated after executing the calculation factors on each shard, and may include partially calculated data or records that have undergone preliminary processing. Pre-execution results are important inputs for subsequent steps (such as position prediction and result re-ranking) to generate the final query results.

[0159] Through deduplication and reordering, the position of each record in the query results is ensured to be accurate and unique, avoiding duplication and conflicts, thereby improving the accuracy of the results. Accurate position prediction and reordering reduce the amount of sorting calculations required during the final result generation phase, improving overall query performance and reducing query latency. Furthermore, dynamically adjusting the position of records based on the freshness of the data enhances the system's adaptability to dynamic data changes. Rapidly returning accurate and ordered query results significantly improves the user experience, especially in real-time analysis and AI training scenarios that require fast responses. At the same time, by considering data freshness and prioritizing the display of the latest or most relevant data, the timeliness and relevance of query results are improved.

[0160] That is, by introducing deduplication and freshness-based re-ranking processing, this method not only optimizes the sorting process of query results, but also improves the accuracy and timeliness of query results, thereby optimizing query performance and user experience.

[0161] like Figure 2 As shown, an embodiment of the present invention provides an artificial intelligence-based database optimization query device, which is applied to a database, wherein the database includes multiple shards; the query device includes:

[0162] an acquisition unit, configured to acquire a user's query instruction and the multi-dimensional feature data of each of the shards, and parse the query instruction to obtain a plurality of calculation factors;

[0163] a processing unit configured to perform multidimensional dynamic decision processing on each of the calculation factors based on each of the multidimensional feature data to obtain execution probability data of the corresponding calculation factor on different shards; determine a pre-execution plan for executing the corresponding calculation factor on each of the shards based on each of the execution probability data; and execute each of the pre-execution plans to obtain a corresponding pre-execution result;

[0164] A prediction unit, configured to obtain corresponding position prediction information according to each of the pre-execution results based on a preset position prediction model;

[0165] A sorting unit is used to sort the pre-execution results according to all the position prediction information to obtain a final query result.

[0166] like Figure 3 As shown, an embodiment of the present invention provides an artificial intelligence-based database optimization query device, including a memory and a processor; the memory is used to store a computer program; the processor is used to implement the artificial intelligence-based database optimization query method as described above when executing the computer program.

[0167] In other words, an artificial intelligence-based database optimization query device includes a memory and a processor coupled to the memory; the memory is configured to store a computer program; and the processor is configured to perform the following operations when executing the computer program:

[0168] Obtaining a user's query instruction and the multi-dimensional feature data of each of the shards, and parsing the query instruction to obtain a plurality of calculation factors;

[0169] Based on each of the multidimensional feature data, a multidimensional dynamic decision process is performed on each of the calculation factors to obtain execution probability data of the corresponding calculation factor on different shards;

[0170] Determining, based on each of the execution probability data, a pre-execution plan for executing the corresponding calculation factor on each of the shards, and executing each of the pre-execution plans to obtain a corresponding pre-execution result;

[0171] Based on a preset location prediction model, obtaining corresponding location prediction information according to each of the pre-execution results;

[0172] The pre-execution results are sorted according to all the position prediction information to obtain a final query result.

[0173] An embodiment of the present invention provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the above-mentioned artificial intelligence-based database optimization query method is implemented.

[0174] Obtaining a user's query instruction and the multi-dimensional feature data of each of the shards, and parsing the query instruction to obtain a plurality of calculation factors;

[0175] Based on each of the multidimensional feature data, a multidimensional dynamic decision process is performed on each of the calculation factors to obtain execution probability data of the corresponding calculation factor on different shards;

[0176] Determining, based on each of the execution probability data, a pre-execution plan for executing the corresponding calculation factor on each of the shards, and executing each of the pre-execution plans to obtain a corresponding pre-execution result;

[0177] Based on a preset location prediction model, obtaining corresponding location prediction information according to each of the pre-execution results;

[0178] The pre-execution results are sorted according to all the position prediction information to obtain a final query result.

[0179] The database optimization query device based on artificial intelligence that can be used as the server or client of the present invention will now be described, which is an example of a hardware device that can be applied to various aspects of the present invention. The database optimization query device based on artificial intelligence is intended to represent various forms of digital electronic computer equipment, such as laptop computers, desktop computers, workbenches, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The database optimization query device based on artificial intelligence can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or required herein.

[0180] An AI-based database optimization query device includes a computing unit that can perform various appropriate actions and processes based on a computer program stored in a read-only memory (ROM) or loaded from the storage unit into a random access memory (RAM). The RAM can also store various programs and data required for device operation. The computing unit, ROM, and RAM are interconnected via a bus. An input / output (I / O) interface is also connected to the bus.

[0181] Those skilled in the art will appreciate that all or part of the processes in the above-described method embodiments can be implemented by instructing the relevant hardware through a computer program. The program can be stored in a computer-readable storage medium. When executed, the program can include the processes in the above-described method embodiments. The storage medium can be a magnetic disk, an optical disk, a read-only memory (ROM), or a random access memory (RAM). In this application, the units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units, i.e., they may be located in one location or distributed across multiple network elements. Some or all of these units can be selected based on actual needs to achieve the objectives of the embodiments of the present invention. Furthermore, the functional units in the various embodiments of the present invention can be integrated into a single processing unit, each unit can exist physically separately, or two or more units can be integrated into a single unit. These integrated units can be implemented in either hardware or software functional units.

[0182] Although the present invention is disclosed as above, the protection scope of the present invention is not limited thereto. Those skilled in the art may make various changes and modifications without departing from the spirit and scope of the present invention, and these changes and modifications will fall within the protection scope of the present invention.

Claims

1. A database optimization query method based on artificial intelligence, characterized in that: Applied to a database, the database includes multiple shards; the database optimization query method includes: Obtaining a user's query instruction and multidimensional feature data of each of the shards, and parsing the query instruction to obtain multiple calculation factors; wherein the multidimensional feature data includes network distance, load data, popularity data, relevance data, and freshness data; Based on each of the multidimensional feature data, a multidimensional dynamic decision process is performed on each of the calculation factors to obtain execution probability data corresponding to the calculation factor on different shards, including: Determine corresponding weight data according to the calculation factor; Determine a corresponding comprehensive score according to the network distance, the load data, the relevance data, the popularity data, and the freshness data of each shard and the corresponding weight data; Acquiring temperature parameters, and determining corresponding index data according to each of the comprehensive scores and the temperature parameters; Determine total index data based on all the index data, and determine execution probability data of the corresponding calculation factor on different shards based on each index data and the total index data; Determining, based on each of the execution probability data, a pre-execution plan for executing the corresponding calculation factor on each of the shards, and executing each of the pre-execution plans to obtain a corresponding pre-execution result; Based on a preset location prediction model, obtaining corresponding location prediction information according to each of the pre-execution results; The pre-execution results are sorted according to all the position prediction information to obtain a final query result.

2. The database optimization query method based on artificial intelligence according to claim 1 is characterized in that: The determining, according to each index data and the total index data, execution probability data of the corresponding calculation factor on different shards includes: By formula 1, the execution probability data of the corresponding calculation factor on different shards is determined according to each index data and the total index data; Wherein, the formula 1 is: ； in, For the jth calculation factor in the shard Execution probability data on is the corresponding index data, is the total index data corresponding to the jth calculation factor, T is the temperature parameter, For the jth calculation factor in the shard The corresponding comprehensive score is is the i-th shard, and N is the total number of shards.

3. The database optimization query method based on artificial intelligence according to claim 1 is characterized in that: The preset position prediction model includes a Transformer module and a multi-head attention mechanism; The pre-execution result includes feature data; the corresponding position prediction information is obtained according to each of the pre-execution results based on the preset position prediction model, including: Encoding each of the feature data using the Transformer module to obtain corresponding encoded feature data; Each of the encoded feature data is processed through the multi-head attention mechanism to obtain the corresponding position prediction information.

4. The database optimization query method based on artificial intelligence according to claim 3 is characterized in that: The feature data includes corresponding original index data; the encoding process of each feature data by the Transformer module to obtain corresponding encoded feature data includes: Convert each of the feature data to obtain corresponding feature matrix data; Corresponding encoding feature data is determined according to the corresponding original index data and the feature matrix data.

5. The database optimization query method based on artificial intelligence according to claim 4 is characterized in that: The pre-execution results of all the position prediction information are sorted to obtain a final query result, including: performing deduplication processing on the position prediction information to obtain temporary position prediction information; When different records with different calculation factors in the temporary location prediction information have the same predicted location, the temporary location prediction information is rearranged based on the corresponding freshness data to obtain the final query result.

6. A database optimization query device based on artificial intelligence, characterized in that: Applied to a database, the database comprising a plurality of shards; The query device comprises: an acquisition unit, configured to acquire a user's query instruction and multidimensional feature data of each of the shards, and parse the query instruction to obtain a plurality of calculation factors; wherein the multidimensional feature data includes network distance, load data, popularity data, relevance data, and freshness data; A processing unit is configured to perform multidimensional dynamic decision processing on each of the calculation factors based on each of the multidimensional feature data to obtain execution probability data of the corresponding calculation factor on different shards; determine a pre-execution plan for executing the corresponding calculation factor on each of the shards based on each of the execution probability data, and execute each of the pre-execution plans to obtain a corresponding pre-execution result, including: determining corresponding weight data based on the calculation factor; determining a corresponding comprehensive score based on the network distance, load data, association data, popularity data, and freshness data of each shard and the corresponding weight data; obtaining a temperature parameter, and determining corresponding index data based on each of the comprehensive scores and the temperature parameter; determining total index data based on all of the index data, and determining execution probability data of the corresponding calculation factor on different shards based on each of the index data and the total index data; A prediction unit, configured to obtain corresponding position prediction information according to each of the pre-execution results based on a preset position prediction model; A sorting unit is used to sort the pre-execution results according to all the position prediction information to obtain a final query result.

7. A database optimization query device based on artificial intelligence, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the database optimization query method based on artificial intelligence as described in any one of claims 1 to 5 is implemented.

8. A computer-readable storage medium, characterized in that The storage medium stores a computer program, and when the computer program is executed by the processor, the database optimization query method based on artificial intelligence as described in any one of claims 1 to 5 is implemented.

Images