Database index recommendation method and device based on query intention recognition and medium

Abstract

The embodiment of the invention discloses a database index recommendation method and device based on query intention recognition and a medium, belongs to the technical field of databases, and solves the problem that the storage cost is increased due to the fact that similar indexes are repeatedly created for SQL (Structured Query Language) texts with the same semantics but different grammar in a traditional index mode. Based on a preset query cost threshold value, collecting a target SQL text in a database, and converting the target SQL text into an abstract syntax tree; performing normalization processing on the abstract syntax tree based on a preset structure rule; performing core feature extraction on the normalized abstract syntax tree, splicing the extracted core features to generate a semantic signature, and dividing the plurality of target SQL texts into a plurality of intention families based on the semantic signature; generating an intention template based on the plurality of intention families, and performing index income analysis on each intention module in the intention template; and creating an index based on the index income analysis result and the security window period corresponding to the corresponding intention module.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a database index recommendation method, device and medium based on query intent recognition. Background Technology

[0002] In modern information society, relational databases, as the core infrastructure for data storage and management, are widely used in key areas such as finance, e-commerce, and government affairs. Indexes, as a core technology for improving query performance in relational databases, establish a mapping relationship between data and storage locations, reducing the scanning scope during data retrieval and significantly improving data query efficiency. They are a crucial component ensuring the efficient operation of databases.

[0003] However, with the advent of the big data era, the amount of data in databases is growing exponentially, and business scenarios are becoming increasingly complex. The expansion of data scale has greatly increased the pressure on traditional indexes in terms of storage space occupation and maintenance costs. Diverse business needs have led to more flexible and varied query patterns, thus placing higher demands on the optimization and configuration of indexes.

[0004] Currently, traditional index management methods mainly rely on the experience of database administrators and manual operations. During the operation, SQL texts with the same semantics but different syntax are often analyzed repeatedly, resulting in the repeated creation of similar indexes, increasing storage overhead and maintenance costs. Summary of the Invention

[0005] This application provides a database index recommendation method, device, and medium based on query intent recognition to solve the following technical problem: In the operation of traditional index management methods, SQL texts with the same semantics but different syntax are often repeatedly analyzed, resulting in the repeated creation of similar indexes, increasing storage overhead and maintenance costs.

[0006] The embodiments of this application adopt the following technical solutions: This application provides a database index recommendation method based on query intent recognition. The method includes: collecting target SQL text from a database based on a preset query cost threshold and converting the target SQL text into an abstract syntax tree (API); traversing the API and normalizing it based on preset structural rules; extracting core features from the normalized API and concatenating the extracted core features to generate a semantic signature, thereby dividing multiple target SQL texts into multiple intent families based on the semantic signature; generating intent templates based on multiple intent families and performing index benefit analysis on each intent module in the intent template; and creating an index based on the index benefit analysis results and the corresponding safety window period of the intent module.

[0007] In one implementation of this application, the abstract syntax tree (API) is traversed, and the API is normalized based on predefined structural rules. Specifically, this includes: structuring the API; wherein the structuring includes at least one of constant position swapping, field order adjustment, function name lowercase conversion, and alias restoration; and / or, determining the IN list in the API and sorting the IN list; and / or, determining redundant brackets in the API and deleting redundant brackets; and / or, replacing constants in the API with placeholders to generate a parameterized intermediate representation.

[0008] In one implementation of this application, multiple target SQL texts are divided into multiple intent families based on semantic signatures. Specifically, this includes: encoding the semantic signature into a high-dimensional vector using a lightweight model; wherein the semantic signature consists of at least one of filtering condition features, association relationship features, and sorting direction features; determining the cosine similarity between any two target SQL text vectors; and dividing the multiple SQL texts into multiple intent families based on a preset grouping threshold and the cosine similarity.

[0009] In one implementation of this application, generating an intent template based on multiple intent families specifically includes: determining template information corresponding to each intent family; wherein the template information includes at least one of the following: parameterized target SQL text, intent identifier, table name and fields corresponding to the target SQL text, preset key fields, sorting direction, pagination identifier, execution frequency, and response time; generating corresponding permission information based on the template information and filling the permission information into the corresponding permission module in the permission template; filling the template information corresponding to each intent family into the corresponding intent module and associating the intent module with the permission module through the intent identifier; and generating an intent template based on the associated intent module.

[0010] In one implementation of this application, after generating the corresponding permission information based on the template information, the method further includes: constructing a user attribute set; wherein the user attribute set includes at least one of role attributes, department attributes, time attributes, and permission level attributes; logically combining at least two attributes in the attribute set to construct an attribute access policy; encrypting the column-level permission information using an attribute-based encryption algorithm based on the attribute access policy to generate encrypted permission ciphertext and a policy description; receiving the attribute set and private key submitted by the user, and matching and verifying the attribute set submitted by the user with the policy description; if the attribute set submitted by the user matches the policy description, decrypting the permission ciphertext using the private key to obtain the plaintext permission rules.

[0011] In one implementation of this application, an index benefit analysis is performed on each intent module in the intent template. Specifically, this includes: determining the required historical index data for the intent module in the historical database; wherein the required historical index data includes at least one of historical index response time, historical resource consumption, and historical business importance level; and determining the required query data for the intent module in the absence of a recommended index; wherein the required query data includes at least one of query response time and query resource consumption; determining the difference between the required historical index data and the required query data; and obtaining the index benefit analysis result for the intent module based on the preset weight allocation value and the difference.

[0012] In one implementation of this application, index creation is performed based on the index revenue analysis results and the corresponding security window period of the intent module. Specifically, this includes: determining the historical business-related data of the target SQL text pair when the index revenue analysis results meet the revenue conditions; analyzing the historical business-related data based on a preset time window to determine the data fluctuation situation within each time window; determining the business off-peak period based on the data fluctuation situation and creating the index during the business off-peak period; and performing usage rate detection on the created index after each preset time interval, and deleting the index if the usage rate is less than a preset frequency threshold.

[0013] In one implementation of this application, after the index benefit analysis results and the corresponding security window period of the intent module, the method further includes: obtaining performance indicator data corresponding to the database; wherein, the performance indicator data includes at least one of query response time, IO wait time, and CPU load; after the database index is created and runs for a preset period of time, the performance indicator data is collected again; the two collected performance indicator data are compared, and if the comparison result shows that the performance indicator data after index creation meets the preset performance conditions, it is marked as a positive example; if the comparison result shows that the performance indicator data after index creation does not meet the preset performance conditions, it is marked as a misjudged case; based on the misjudged cases, the parameters in the benefit evaluation process are optimized and adjusted.

[0014] This application provides a database index recommendation device based on query intent recognition, comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to: collect target SQL text from a database based on a preset query cost threshold, and convert the target SQL text into an abstract syntax tree; traverse the abstract syntax tree and normalize it based on preset structural rules; extract core features from the normalized abstract syntax tree, and concatenate the extracted core features to generate a semantic signature, thereby dividing multiple target SQL texts into multiple intent families based on the semantic signature; generate intent templates based on the multiple intent families, and perform index benefit analysis on each intent module in the intent template; and create an index based on the index benefit analysis results and the corresponding safety window period of the intent module.

[0015] This application provides a non-volatile computer storage medium storing computer-executable instructions. The computer-executable instructions are configured to: collect target SQL text from a database based on a preset query cost threshold and convert the target SQL text into an abstract syntax tree (API); traverse the API and normalize it based on preset structural rules; extract core features from the normalized API and concatenate the extracted core features to generate a semantic signature, thereby dividing multiple target SQL texts into multiple intent families based on the semantic signature; generate intent templates based on the multiple intent families and perform index benefit analysis on each intent module in the intent template; and create an index based on the index benefit analysis results and the corresponding safety window period of the intent module.

[0016] The above-mentioned technical solutions adopted in this application embodiment can achieve the following beneficial effects: Converting SQL text into an abstract syntax tree enables the processing of SQL statements in a unified structured form. By traversing the abstract syntax tree, each node in the tree can be accessed, ensuring complete analysis of each part of the SQL statement. Based on pre-defined structure rules for normalization, differences in SQL statements caused by structure can be eliminated, giving SQL statements with the same semantics but different forms a unified expression. Secondly, by extracting core features, this application embodiment can filter out key information representing the semantics of SQL statements, reducing redundant data and lowering processing complexity. By concatenating and generating semantic signatures, SQL statements with similar semantics can be quickly and accurately clustered into the same intent family. Intent templates are generated based on intent families, enabling unified management and optimization of similar query requirements, reducing repetitive work, and improving the standardization of index management. By performing index benefit analysis on each intent module in the intent template, the performance improvement and resource consumption that may be brought about by creating indexes in different intent scenarios can be quantitatively evaluated, deciding whether to create indexes and avoiding resource waste. Attached Figure Description

[0017] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings: Figure 1 A flowchart of a database index recommendation method based on query intent recognition is provided for embodiments of this application; Figure 2 A flowchart for intent recognition clustering is provided as an embodiment of this application; Figure 3 A flowchart for evaluating index revenue is provided as an embodiment of this application; Figure 4 This is a schematic diagram of the structure of a database index recommendation device based on query intent recognition, provided in an embodiment of this application.

[0018] Figure label: 200: Database indexing recommendation device based on query intent recognition; 201: Processor; 202: Memory. Detailed Implementation

[0019] This application provides a database index recommendation method, device, and medium based on query intent recognition.

[0020] To enable those skilled in the art to better understand the technical solutions in this application, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this application.

[0021] Figure 1 This document provides a flowchart of a database index recommendation method based on query intent recognition, as illustrated in an embodiment of this application. Figure 1 As shown, the database index recommendation method based on query intent recognition includes the following steps: Step 101: Based on the preset query cost threshold, collect the target SQL text from the database and convert the target SQL text into an abstract syntax tree.

[0022] In one implementation of this application, SQL execution details are obtained by periodically querying the database's system performance view, such as once per second, and the database's slow query log is used as a supplementary data source. The preset query cost threshold in this embodiment is QRT (Query Response Time) > 100ms and an average of > 50 executions per day.

[0023] Among them, QRT>100ms: slow single execution, which directly affects user experience, is a target that urgently needs to be optimized.

[0024] Executions > 50 times per day: Even if each execution is slow, high-frequency execution will continuously consume a large amount of system resources, resulting in extremely high overall costs.

[0025] This application embodiment combines query response time with the average number of daily executions to ensure that the system can accurately obtain the critical few SQL query texts that have a significant impact on the overall performance of the database.

[0026] Step 102: Traverse the abstract syntax tree and normalize it based on the predefined structure rules.

[0027] In one implementation of this application, the abstract syntax tree is subjected to structuring processing; wherein, the structuring processing includes at least one of constant position swapping, field order adjustment processing, function name lowercase processing, and alias restoration processing, and / or, determining the IN list in the abstract syntax tree and sorting the IN list, and / or, determining redundant brackets in the abstract syntax tree and deleting redundant brackets, and / or, replacing constants in the abstract syntax tree with placeholders to generate a parameterized intermediate representation.

[0028] Specifically, the SQL parser is used to convert the SQL text collected by the query log collection module into an abstract syntax tree, and the abstract syntax tree is traversed to extract the most critical elements for index optimization: Filter condition: WHERE column = ?; Join condition: JOIN ... ON t1.col = t2.col; Sort field: ORDER BY column; Grouping field: GROUP BY column.

[0029] Output a structured feature set containing these key fields and their operators (=, >, LIKE, etc.).

[0030] It should be noted that the normalization process in this application embodiment is to solve the problem of different structural expressions for the same intent. For example, SELECT * FROM users WHERE id = 100 and SELECT * FROM users WHERE 100 = id should be identified as the same intent and clustered uniformly.

[0031] Furthermore, in this embodiment, the target SQL text is parsed into an abstract syntax tree. Specifically, an open-source SQL parser can be used to parse the original SQL statement into a structured abstract syntax tree, removing non-essential differences such as spaces, SQL case sensitivity, and table aliases. The following standardization operations are implemented at the node level of the abstract syntax tree: Swap constant positions, for example, change 100 = id to id = 100; Standardize the field order; for example, ORDER BY b, a is sorted by field name to become ORDER BY a, b. Sorting the IN list, for example, unifying id IN (3,1,2) into id IN (1,2,3); Remove redundant parentheses, for example, convert (a = 1) to a = 1; Lowercase function names: for example, convert UPPER(x) to upper(x); Alias ​​restoration: Combine database metadata, for example, to map u.id to the actual column users.id; Generate a parameterized intermediate representation: Replace all constants with placeholders (?), and output a uniformly formatted intermediate SQL template, for example: SELECT name FROM users WHERE id = ? AND status = ?.

[0032] Step 103: Extract core features from the normalized abstract syntax tree and concatenate the extracted core features to generate a semantic signature, so as to divide multiple target SQL texts into multiple intent families based on the semantic signature.

[0033] In one implementation of this application, structural normalization can handle explicit syntactic variations, but it cannot identify complex semantic variations that are logically equivalent but structurally different, such as LIKE 'abc%' and col>= 'abc' AND col<'abd'. Therefore, deep semantic matching is still required.

[0034] Specifically, a lightweight model encodes semantic signatures into high-dimensional vectors; each semantic signature consists of at least one of the following: filtering condition features, association features, and sorting direction features. The cosine similarity between any two target SQL text vectors is determined. Based on a preset grouping threshold and cosine similarity, multiple SQL texts are divided into multiple intent families.

[0035] Specifically, key semantic fragments are extracted and a signature string is constructed. Core structural features are extracted from the normalized abstract syntax tree and concatenated into a semantic signature, including: Filter condition: filter:"id=? AND status=?"; Relationship: join:"users<->orders"; Sort direction: order:"create_time DESC"; The final input text generated is: "filter:id=? status=?" join:users<->orders order:create_time DESC".

[0036] Furthermore, a lightweight Sentence-BERT model is employed to encode the semantic signature into a high-dimensional vector, and the cosine similarity between any two SQL vectors is calculated. The similarity calculation formula is: Similarity(A, B) = (AB) / (||A|| * ||B||); Where AB represents the dot product of vectors A and B, and ||A|| and ||B|| represent the magnitudes of vectors A and B, respectively. This formula measures the similarity of two vectors in a direction, with the result ranging from -1 to 1; a larger value indicates greater similarity. Furthermore, in this embodiment, a grouping threshold of 0.92 can be set. If the value is greater than this threshold, the vectors are determined to belong to the same intent family, thus performing intent family grouping.

[0037] Step 104: Generate intent templates based on multiple intent families, and perform indexing benefit analysis on each intent module in the intent template.

[0038] In one implementation of this application, template information corresponding to each intent family is determined. The template information includes at least one of the following: parameterized target SQL text, intent identifier, table name and fields corresponding to the target SQL text, preset key fields, sorting direction, pagination identifier, execution frequency, and response time. Based on the template information, corresponding permission information is generated and populated into the corresponding permission modules in the permission template. The template information corresponding to each intent family is populated into the corresponding intent modules, and the intent modules are associated with the permission modules through the intent identifier. Based on the associated intent modules, an intent template is generated.

[0039] Specifically, the embodiments of this application ultimately produce a standardized SQL intent template. For example, SELECT name FROM users WHERE id=100 and SELECT name FROM users WHERE id=200 are normalized to: SELECT name FROM users WHERE id = ?. All subsequent analysis, evaluation, and decision-making are based on the intent template rather than a single SQL statement, thereby improving efficiency.

[0040] Furthermore, for each intent family, common structural features are extracted from its contained SQL statements to form parameterized template information. The extracted content includes: the target SQL text with specific constants replaced by parameter variables, the intent identifier uniquely identifying the intent, the table names and field sets involved in the SQL, the pre-defined key fields corresponding to the execution, the columns and direction for result sorting, the flag indicating whether pagination is enabled, the average execution frequency of the SQL, and historical response times. Based on the table names, fields, operation types, etc., in the template information, combined with the system's preset permission policies, corresponding permission information is automatically generated. The generation process can utilize techniques such as attribute-based encryption to encrypt sensitive permission rules, ensuring that even if permission storage is leaked, unauthorized users cannot parse and use these permissions.

[0041] Furthermore, the generated permission information is populated into the corresponding modules of the permission template. Simultaneously, intent modules are associated with permission modules using intent identifiers, enabling each intent family's corresponding SQL template to automatically invoke its bound permission rules during execution. Based on the associated intent modules, template and permission information are integrated to generate a complete intent template. This intent template not only contains the parameterized structure of SQL but also embeds permission control logic, allowing it to be directly used for subsequent SQL generation, execution, and optimization, thus supporting intelligent database management.

[0042] Figure 2 An intent recognition clustering flowchart is provided for embodiments of this application, such as Figure 2 As shown, SQL query text is collected, and an SQL parser is used to parse the input SQL text, generating an abstract syntax tree. The generated syntax tree is traversed to extract structured features closely related to index optimization, including filtering conditions in the WHERE clause, join conditions in JOIN operations, sorting fields in ORDER BY, and grouping fields in GROUP BY. The extracted structured features are then semantically normalized using a rule engine and a lightweight NLP model. The rule engine handles simple normalization tasks, such as constant swapping, field order standardization, and removing redundant parentheses; the lightweight NLP model converts the SQL statement or its key parts into high-dimensional vectors. The normalized SQL statement is then used to generate a standardized query intent template.

[0043] In one implementation of this application, after generating corresponding permission information based on template information, this embodiment further constructs a user attribute set; wherein the user attribute set includes at least one of role attributes, department attributes, time attributes, and permission level attributes. At least two attributes in the attribute set are logically combined to construct an attribute access policy. Based on the attribute access policy, the column-level permission information is encrypted using an attribute-based encryption algorithm to generate encrypted permission ciphertext and a policy description. The user-submitted attribute set and private key are received, and the user-submitted attribute set is matched and verified against the policy description. If the user-submitted attribute set matches the policy description, the private key is used to decrypt the permission ciphertext to obtain the plaintext permission rules.

[0044] Specifically, during system initialization and user registration, a user attribute set is collected and constructed through user information entry, role assignment, and permission configuration processes. Role attributes are based on the user's functional division within the system, such as administrator or regular user; department attributes are related to the user's organizational structure; time attributes include the permitted time window for operations; and permission level attributes are categorized according to data sensitivity, such as public, internal, and confidential. This attribute information can be dynamically updated to ensure real-time reflection of user permission status. Based on business requirements and security rules, at least two attributes are selected from the user attribute set and combined using logical operators to construct attribute access policies.

[0045] Furthermore, for column-level permission information, the attribute access policy is combined with the permission content, and an attribute-based encryption algorithm is used for encryption. During encryption, the algorithm generates an encryption key based on the attribute access policy, transforms the plaintext permission content into ciphertext, and simultaneously generates a corresponding policy description. This policy description records the attributes and logical relationships involved in the policy. The encrypted ciphertext and policy description are stored together to ensure that the permission information is not parsed by unauthorized users during storage and transmission.

[0046] Furthermore, when a user initiates a data access request, the system receives the user's submitted attribute set and private key. The submitted attribute set is parsed and compared with the stored policy description. According to the logical relationships set in the attribute access policy, the system verifies whether the user attributes meet the policy requirements. For example, it checks whether attributes such as user role, department, time, and permission level match the policy conditions. If the user's submitted attribute set matches the attribute access policy, it indicates that the user has access rights. The system then uses the user's private key to decrypt the encrypted permission rules. The decrypted rules reveal the plaintext permission rules, specifying the range of data table fields the user can access, the operation types, and other information. Based on these plaintext rules, the system further determines whether the user's request is legitimate, thereby deciding whether to allow the user to perform the corresponding data access operation, thus achieving attribute-based dynamic access control.

[0047] The permission ciphertext in this embodiment is encrypted data obtained by converting plaintext permission rules using an attribute-based encryption algorithm. Its function is to ensure that permission information exists in ciphertext form during storage and transmission, so that even if the database or transmission link is attacked, unauthorized parties cannot directly obtain the permission content. Furthermore, the encryption process of the permission ciphertext in this embodiment relies on an attribute access policy, making the ciphertext strongly correlated with user attributes. When user attributes change, such as department transfers or role upgrades, the decryption conditions of the ciphertext also change, realizing dynamic activation and deactivation of permissions. For example, if a user is transferred from the finance department to the technology department, their attributes no longer meet the original permission policy, and even if they possess the old private key, they cannot decrypt the corresponding ciphertext, ensuring that permissions are adjusted in real time according to the user's status.

[0048] In one implementation of this application, the required historical index data corresponding to the intent module is determined in the historical database; wherein the required historical index data includes at least one of historical index response time, historical resource consumption, and historical business importance level. Furthermore, the required query data for the intent module in the absence of a recommended index is determined; wherein the required query data includes at least one of query response time and query resource consumption. The difference between the required historical index data and the required query data is determined, and based on the preset weight allocation value and the difference, the index benefit analysis result corresponding to the intent module is obtained.

[0049] Specifically, this application embodiment includes an index revenue analysis formula: Score=α·PerformanceGain-β·ResourceCost+γ·PriorityWeight; PerformanceGain represents the performance gain, used to estimate the percentage reduction in average response time for this type of query after creating the target index. The estimation method involves using database statistics such as table size and field cardinality to estimate the difference between the cost of a full table scan without an index and the cost of an index scan with an index in historical databases. ResourceCost represents the resource cost, the additional overhead of building and maintaining the index, including space cost and write overhead. Space cost refers to the disk space occupied by the index, while write overhead is the additional I / O and CPU consumption incurred each time an INSERT, UPDATE, or DELETE operation is performed, requiring the database to maintain the corresponding index structure. PriorityWeight represents the priority weight, indicating its importance at the business level. Queries originating from the core transaction chain should be prioritized, even if the benefit is not the highest. α is the first weight coefficient, β is the second weight coefficient, and γ is the third weight coefficient.

[0050] Step 105: Create the index based on the index revenue analysis results and the corresponding security window period of the intent module.

[0051] In one implementation of this application, if the index revenue analysis results meet the revenue criteria, historical business-related data for the target SQL text pair is determined. The historical business-related data is analyzed based on preset time windows to determine the data fluctuations within each time window. Based on the data fluctuations, business off-peak periods are identified, and indexes are created during these off-peak periods. After each preset time interval, the usage rate of the created indexes is checked, and if the usage rate is less than a preset frequency threshold, the indexes are deleted.

[0052] Specifically, after confirming that the index benefit analysis results meet the criteria, historical business data related to the target SQL text is extracted from data sources such as database history records and business logs. This includes multi-dimensional information such as business operation records, data access frequency, and system performance indicators. Based on preset time window rules, the collected historical business-related data is segmented. For the data within each time window, the changing trends of key indicators are analyzed, including increases or decreases in data volume, fluctuations in business operation frequency, and levels of system load. The amplitude and patterns of data fluctuations are calculated to determine the corresponding data fluctuation situation within each time window. Based on the data fluctuation situation within each time window, time periods with smaller data fluctuations, lower system resource utilization, and relatively sparse business operations are selected and identified as business off-peak periods. Within the identified business off-peak periods, the index creation process is initiated. According to the pre-planned indexing scheme, the index structure is constructed for the tables and fields involved in the target SQL text.

[0053] Furthermore, the usage rate of newly created indexes is checked periodically within a preset time period. For example, indexes that have not been used for seven consecutive days are marked as long-term unused indexes, indicating that the index is not being effectively utilized in the business scenario, potentially causing wasted storage space and performance degradation in data updates. At this point, the index deletion mechanism is triggered. A second confirmation is required before deleting the index to prevent accidental deletion. After confirmation, the `DROP INDEX` command is executed. `DROP INDEX` is a database management operation used to delete existing indexes in the database.

[0054] In one implementation of this application, performance metric data corresponding to the database is obtained; wherein the performance metric data includes at least one of query response time, IO wait time, and CPU load. After the database index is created and runs for a preset period of time, performance metric data is collected again, and the two sets of collected performance metric data are compared. If the comparison result shows that the performance metric data after index creation meets the preset performance conditions, it is marked as a positive example; if the comparison result shows that the performance metric data after index creation does not meet the preset performance conditions, it is marked as a false positive. Based on the false positive cases, the parameters in the benefit evaluation process are optimized and adjusted.

[0055] Specifically, key metrics such as query response time, IO wait time, and CPU load are recorded before index creation and again after a period of stable operation. The two sets of performance metrics are compared. If the performance improvement meets or exceeds expectations, it is marked as a positive example. If the performance improvement is not significant, or even declines, or if write overhead far exceeds expectations, this case is recorded as a misjudgment. Based on this misjudgment, parameters involved in the benefit evaluation process, such as the weighting coefficients and threshold parameters for calculating index benefits, are adjusted accordingly. For example, if it is found that setting the weight of write overhead too low leads to misjudgment, the weight of write overhead in the benefit evaluation process is appropriately increased to reduce the occurrence of misjudgments and gradually optimize the index management strategy.

[0056] Figure 3 A flowchart for evaluating index revenue is provided for embodiments of this application, such as... Figure 3As shown, the query intent template is used to calculate the performance gain (PerformanceGain). Based on database statistics, it estimates the percentage decrease in average response time for this type of query after creating the target index. The expected performance improvement is determined by comparing the cost of a full table scan without an index with the cost of an index scan with an index. ResourceCost is calculated to assess the additional overhead of creating and maintaining the index, including space and write costs. PriorityWeight is calculated to determine the priority weight of the query based on its importance to the business logic. A comprehensive score is calculated using the index benefit evaluation formula. The calculated comprehensive score is compared with a preset threshold. If the score is greater than the threshold, it is recommended to create the index; otherwise, it is decided not to create the index.

[0057] Figure 4 This is a schematic diagram of the structure of a database index recommendation device based on query intent recognition, provided as an embodiment of this application. Figure 4 As shown, a database index recommendation device 200 based on query intent recognition includes: at least one processor 201; and a memory 202 communicatively connected to the at least one processor 201. The memory 202 stores instructions executable by the at least one processor 201, which, when executed, enable the at least one processor 201 to: collect target SQL text from the database based on a preset query cost threshold and convert the target SQL text into an abstract syntax tree (ABS). Iterate through the ABS, normalize the ABS based on preset structural rules, extract core features from the normalized ABS, and concatenate the extracted core features to generate a semantic signature, thereby dividing multiple target SQL texts into multiple intent families based on the semantic signature. It generates intent templates based on the multiple intent families and performs index benefit analysis on each intent module within the intent template. Based on the index benefit analysis results and the corresponding safety window period for each intent module, it creates an index.

[0058] This application provides a non-volatile computer storage medium storing computer-executable instructions. The computer-executable instructions are configured to: collect target SQL text from a database based on a preset query cost threshold and convert the target SQL text into an abstract syntax tree (API); traverse the API and normalize it based on preset structural rules; extract core features from the normalized API and concatenate the extracted core features to generate a semantic signature, thereby dividing multiple target SQL texts into multiple intent families based on the semantic signature; generate intent templates based on the multiple intent families and perform index benefit analysis on each intent module in the intent template; and create an index based on the index benefit analysis results and the corresponding safety window period of the intent module.

[0059] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the embodiments of apparatus, devices, and non-volatile computer storage media are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0060] The above descriptions are merely embodiments of this application and are not intended to limit the scope of this application. For those skilled in the art, various modifications and variations can be made to the embodiments of this application. These modifications or substitutions do not cause the essence of the corresponding technical solutions to depart from the spirit and scope of the technical solutions in the embodiments of this application.

Claims

1. A database index recommendation method based on query intent recognition, characterized in that, The method includes: Based on a preset query cost threshold, target SQL text is collected from the database and converted into an abstract syntax tree; The abstract syntax tree is traversed, and normalization is performed on the abstract syntax tree based on preset structure rules; The core features of the normalized abstract syntax tree are extracted, and the extracted core features are concatenated to generate a semantic signature, so as to divide the multiple target SQL texts into multiple intent families based on the semantic signature; Intent templates are generated based on multiple intent families, and indexing benefit analysis is performed on each intent module in the intent template; Indexes are created based on the index revenue analysis results and the corresponding security window for the relevant intent modules.

2. The database index recommendation method based on query intent recognition according to claim 1, characterized in that, The traversal of the abstract syntax tree and the normalization of the abstract syntax tree based on preset structural rules specifically include: The abstract syntax tree is subjected to structuring processing; wherein the structuring processing includes at least one of the following: constant position swapping, field order adjustment processing, function name lowercase processing, and alias restoration processing; And / or, determine the IN list in the abstract syntax tree and sort the IN list; And / or, identify redundant parentheses in the abstract syntax tree and delete them; And / or, replace constants in the abstract syntax tree with placeholders to generate a parameterized intermediate representation.

3. The database index recommendation method based on query intent recognition according to claim 1, characterized in that, The step of dividing multiple target SQL texts into multiple intent families based on the semantic signature specifically includes: The semantic signature is encoded into a high-dimensional vector using a lightweight model; wherein the semantic signature consists of at least one of the following: filtering condition features, association relationship features, and sorting direction features; Determine the cosine similarity between any two target SQL text vectors; Based on a preset grouping threshold and the cosine similarity, multiple SQL texts are divided into multiple intent families.

4. The database index recommendation method based on query intent recognition according to claim 1, characterized in that, The generation of intent templates based on multiple intent families specifically includes: Determine the template information corresponding to each intent family; wherein, the template information includes at least one of the following: parameterized target SQL text, intent identifier, table name and fields corresponding to the target SQL text, preset key fields, sorting direction, pagination identifier, execution frequency and response time; Based on the template information, corresponding permission information is generated, and the permission information is filled into the corresponding permission module in the permission template; The template information corresponding to each intent family is filled into the corresponding intent module, and the intent module is associated with the permission module through the intent identifier; The intent template is generated based on the intent module after association processing.

5. The database index recommendation method based on query intent recognition according to claim 4, characterized in that, After generating the corresponding permission information based on the template information, the method further includes: Construct a user attribute set; wherein the user attribute set includes at least one of the following: role attribute, department attribute, time attribute, and permission level attribute; Logically combine at least two attributes from the attribute set to construct an attribute access strategy; Based on the attribute access strategy, the column-level permission information is encrypted using an attribute-based encryption algorithm to generate encrypted permission ciphertext and strategy description. Receive the set of attributes and private key submitted by the user, and match and verify the set of attributes submitted by the user with the policy description; If the set of attributes submitted by the user matches the policy description, the private key is used to decrypt the ciphertext of the permissions to obtain the plaintext permission rules.

6. The database index recommendation method based on query intent recognition according to claim 1, characterized in that, The indexing benefit analysis of each intent module in the intent template specifically includes: The required data for the historical index corresponding to the intent module is determined from the historical database; wherein, the required data for the historical index includes at least one of the following: historical index response time, historical resource consumption, and historical business importance level; In addition, the system determines the data required for the query of the intent module in the absence of a recommendation index; wherein the data required for the query includes at least one of the following: query response time and query resource consumption. Determine the difference between the data required for the historical index and the data required for the query; Based on the preset weight allocation value and the difference, the index revenue analysis result corresponding to the intent module is obtained.

7. The database index recommendation method based on query intent recognition according to claim 1, characterized in that, The process of creating an index based on the index revenue analysis results and the corresponding security window period of the intent module specifically includes: If the index revenue analysis results meet the revenue conditions, the historical business-related data of the target SQL text pair are determined. Based on the preset time window, the historical business-related data are analyzed to determine the data fluctuation situation within each time window. Based on the data fluctuations, the low-peak business periods are determined, and indexes are created during the low-peak business periods. After each preset time interval, the usage rate of the created index is checked, and if the usage rate is less than a preset frequency threshold, the index is deleted.

8. The database index recommendation method based on query intent recognition according to claim 1, characterized in that, After the security window period corresponding to the index revenue analysis results and the corresponding intent module, the method further includes: Obtain the performance metrics data corresponding to the database; wherein, the performance metrics data includes at least one of query response time, IO wait time, and CPU load; After the database index is created and runs for a preset period of time, the performance index data is collected again. The performance index data collected twice are compared. If the comparison result shows that the performance index data after index creation meets the preset performance conditions, it is marked as a positive example. If the comparison result shows that the performance index data after index creation does not meet the preset performance conditions, it is marked as a misjudgment case; Based on the aforementioned misjudgment cases, the parameters in the benefit assessment process were optimized and adjusted.

9. A database indexing and recommendation device based on query intent recognition, characterized in that, The device includes a memory for storing computer program instructions and a processor for executing the program instructions, wherein when the computer program instructions are executed by the processor, the device is triggered to perform the method described in any one of claims 1-8.

10. A non-volatile computer storage medium storing computer-executable instructions, characterized in that, The computer-executable instructions are capable of performing the method described in any one of claims 1-8.

Images