Database index recommendation method and device based on query intent recognition, and medium

Abstract

The embodiment of the application 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 of repeated creation of similar indexes for SQL texts with the same semantics but different grammars in the traditional index mode, thereby increasing storage cost. Based on a preset query cost threshold, a target SQL text is collected in a database, and the target SQL text is converted into an abstract syntax tree; the abstract syntax tree is normalized based on a preset structure rule; core features of the normalized abstract syntax tree are extracted, and the extracted core features are spliced to generate a semantic signature, so as to divide multiple target SQL texts into multiple intention families based on the semantic signature; an intention template is generated based on the multiple intention families, and index yield analysis is performed on each intention module in the intention template; and index creation is performed based on the index yield analysis result and a corresponding safety window period of the corresponding intention module.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and particularly relates to a database index recommendation method based on query intention recognition, equipment and medium. BACKGROUND

[0002] In the modern information society, as the core infrastructure of data storage and management, relational databases are widely used in key fields such as finance, e-commerce and government affairs. As a core technical means to improve query performance in relational databases, index can reduce the scanning range during data retrieval and greatly improve data query efficiency, and is an important part of ensuring efficient operation of databases.

[0003] However, with the advent of the big data era, the amount of data in databases grows exponentially, and business scenarios are increasingly complex. The expansion of data size puts tremendous pressure on the storage space occupation and maintenance cost of traditional indexes, and the diversification of business requirements leads to more flexible and variable query patterns, thus higher requirements are put forward for the optimization configuration of indexes.

[0004] Currently, the traditional index management method mainly relies on the experience and manual operation of database administrators. In the operation process, SQL texts with the same semantics but different syntax are usually analyzed repeatedly, leading to repeated creation of similar indexes and increasing storage overhead and maintenance cost. SUMMARY

[0005] The embodiments of the present application provide a database index recommendation method based on query intention recognition, equipment and medium, which is used to solve the technical problem that in the operation process of the traditional index management method, SQL texts with the same semantics but different syntax are usually analyzed repeatedly, leading to repeated creation of similar indexes and increasing storage overhead and maintenance cost.

[0006] The embodiments of the present application adopt the following technical solutions:

[0007] The embodiments of the present application provide a database index recommendation method based on query intention recognition. It includes collecting target SQL texts in a database based on a preset query cost threshold, and converting the target SQL texts into abstract syntax trees; traversing the abstract syntax trees, and performing normalization processing on the abstract syntax trees based on a preset structure rule; extracting core features from the normalized abstract syntax trees, and splicing the extracted core features to generate semantic signatures, so as to divide multiple target SQL texts into multiple intention families based on the semantic signatures; generating intention templates based on the multiple intention families, and performing index benefit analysis on each intention module in the intention templates; and creating indexes based on the index benefit analysis results and the corresponding safety window period of the intention modules.

[0008] In an implementation manner of the present application, the abstract syntax tree is traversed, and the abstract syntax tree is normalized based on preset structure rules, specifically including: structuring the abstract syntax tree; wherein the structuring at least includes one of constant position exchange, field order adjustment processing, function name lowercasing processing, and alias restoring processing; and / or, determining an IN list in the abstract syntax tree, and sorting the IN list; and / or, determining redundant parentheses in the abstract syntax tree, and deleting the redundant parentheses; and / or, replacing constants in the abstract syntax tree with placeholders to generate a parameterized intermediate representation form.

[0009] In an implementation manner of the present application, the plurality of target SQL texts are divided into a plurality of intent families based on semantic signatures, specifically including: encoding the semantic signatures into high-dimensional vectors through a lightweight model; wherein the semantic signatures at least consist of one of filtering condition features, correlation relationship features, and sorting direction features; determining the cosine similarity between any two target SQL text vectors; dividing the plurality of SQL texts into a plurality of intent families based on a preset grouping threshold and the cosine similarity.

[0010] In an implementation manner of the present application, the intent template is generated based on the plurality of intent families, specifically including: determining the template information corresponding to each intent family; wherein the template information at least includes one of parameterized target SQL texts, intent identifiers, table names and fields corresponding to the target SQL texts, preset key fields, sorting column directions, paging identifiers, execution frequencies, and response times; generating corresponding permission information based on the template information, and filling the permission information into the corresponding permission modules in the permission template; filling the template information corresponding to each intent family into the corresponding intent modules, and associating the intent modules with the permission modules through the intent identifiers; generating the intent template based on the intent modules after the association processing.

[0011] In an implementation manner of the present 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 at least 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 strategy; encrypting the column-level permission information through an attribute-based encryption algorithm based on the attribute access strategy to generate encrypted permission ciphertext and strategy description; receiving the attribute set submitted by the user and the private key, and matching and verifying the attribute set submitted by the user with the strategy description; if the attribute set submitted by the user conforms to the strategy description, decrypting the permission ciphertext using the private key to obtain the plaintext permission rule.

[0012] In an implementation form of the present application, the index benefit analysis of each intent module in the intent template is performed, specifically including: determining the historical index required data corresponding to the intent module in the historical database; wherein the historical index required data at least includes one of historical index response time, historical resource consumption and historical business importance level; and determining the query required data corresponding to the intent module without recommended index; wherein the query required data at least includes one of query response time and query required resource consumption; determining the difference between the historical index required data and the query required data; obtaining the index benefit analysis result corresponding to the intent module based on the preset weight distribution value and the difference.

[0013] In an implementation form of the present application, based on the index benefit analysis result and the corresponding safety window period of the intent module, the index creation is performed, specifically including: in the case that the index benefit analysis result meets the benefit condition, determining the historical business related data of the target SQL text pair; based on the preset time window, analyzing the historical business related data to determine the corresponding data fluctuation in each time window; based on the data fluctuation, determining the business low peak period and creating the index in the business low peak period; every interval preset period, detecting the usage rate of the created index, and in the case that the usage rate is less than the preset frequency threshold, deleting the index.

[0014] In an implementation form of the present application, after the index benefit analysis result and the corresponding safety window period of the intent module, the method further includes: obtaining the performance index data corresponding to the database; wherein the performance index data at least includes one of query response time, IO waiting time and CPU load; after the database index is created and runs for a preset time length, the performance index data is collected again; comparing the performance index data collected twice, if the comparison result is that the performance index data after index creation meets the preset performance condition, it is marked as a positive example; if the comparison result is that the performance index data after index creation does not meet the preset performance condition, it is marked as a misjudgment case; based on the misjudgment case, the parameters in the benefit evaluation process are optimized and adjusted.

[0015] The embodiment of the application provides a database index recommendation device based on query intention recognition, comprising: at least one processor; and a memory connected with the at least one processor in communication; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to: based on a preset query cost threshold, collect target SQL text in a database, and convert the target SQL text into an abstract syntax tree; traverse the abstract syntax tree, and normalize the abstract syntax tree based on a preset structure rule; extract core features of the normalized abstract syntax tree, splice the extracted core features to generate a semantic signature, and divide a plurality of target SQL texts into a plurality of intention families based on the semantic signature; generate an intention template based on the plurality of intention families, and analyze index benefits of each intention module in the intention template; and based on the index benefit analysis result and a corresponding safety window period of the intention module, create an index.

[0016] The embodiment of the application provides a nonvolatile computer storage medium, which stores computer executable instructions, and the computer executable instructions are configured to: based on a preset query cost threshold, collect target SQL text in a database, and convert the target SQL text into an abstract syntax tree; traverse the abstract syntax tree, and normalize the abstract syntax tree based on a preset structure rule; extract core features of the normalized abstract syntax tree, splice the extracted core features to generate a semantic signature, and divide a plurality of target SQL texts into a plurality of intention families based on the semantic signature; generate an intention template based on the plurality of intention families, and analyze index benefits of each intention module in the intention template; and based on the index benefit analysis result and a corresponding safety window period of the intention module, create an index.

[0017] The at least one technical solution adopted by the embodiments of the present application can achieve the following beneficial effects: the embodiments of the present application can convert SQL text into an abstract syntax tree, process SQL statements in a unified structured form, access each node in the tree by traversing the abstract syntax tree, and ensure complete analysis of each part of the SQL statement. Based on the preset structure rule normalization processing, the differences in SQL statements caused by the structure can be eliminated, and SQL statements with the same semantics but different forms have a unified expression form. Secondly, the embodiments of the present application can filter out key information representing the semantics of the SQL statement by extracting core features, reduce redundant data, and reduce processing complexity. By splicing to generate a semantic signature, SQL statements with similar semantics can be quickly and accurately clustered into the same intent family. According to the intent family, an intent template is generated, the unified management and optimization of similar query requirements are realized, the repetitive work is reduced, and the standardization degree of index management is improved. By performing index benefit analysis on each intent module in the intent template, the performance improvement and resource consumption that may be brought by creating an index in different intent scenarios can be quantitatively evaluated, it is determined whether to create an index, and resource waste is avoided. BRIEF DESCRIPTION OF DRAWINGS

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

[0019] Figure 1 A flow chart of a database index recommendation method based on query intent recognition is provided for the embodiments of the present application;

[0020] Figure 2 A flow chart of intent recognition clustering is provided for the embodiments of the present application;

[0021] Figure 3 A flow chart of index benefit evaluation is provided for the embodiments of the present application;

[0022] Figure 4 A structural schematic diagram of a database index recommendation device based on query intent recognition is provided for the embodiments of the present application.

[0023] Reference signs:

[0024] 200: a database index recommendation device based on query intent recognition, 201: a processor, 202: a memory. DETAILED DESCRIPTION

[0025] The embodiment of the application provides a database index recommendation method based on query intention recognition, equipment and medium.

[0026] In order to enable personnel in the technical field to better understand the technical solutions in the application, the technical solutions in the embodiments of the application will be clearly and completely described below in combination with the drawings in the embodiments of the application. Obviously, the described embodiments are only part of the embodiments of the application, rather than all the embodiments. Based on the embodiments of the present application, all other embodiments obtained by those of ordinary skill in the art without creative labor should fall within the scope of the present application.

[0027] Figure 1 A flow chart of a database index recommendation method based on query intention recognition is provided in the embodiment of the application. As shown in the figure, Figure 1 The database index recommendation method based on query intention recognition comprises the following steps:

[0028] Step 101, based on a preset query cost threshold, collecting a target SQL text in a database, and converting the target SQL text into an abstract syntax tree.

[0029] In an implementation manner of the application, the system performance view of the database is queried at a fixed time, such as once per second, to obtain SQL execution details, and the slow query log of the database is used as a supplementary data source. The preset query cost threshold in the embodiment of the application is QRT (Query Response Time) > 100 ms and daily average execution > 50 times.

[0030] QRT > 100 ms: single execution is slow, directly affecting user experience, and is a target that needs to be optimized urgently.

[0031] Daily average execution > 50 times: even if single execution is not fast, but high-frequency execution will continuously consume a large amount of system resources, and the overall cost is extremely high.

[0032] The embodiment of the application combines the query response time and the daily average execution times, and ensures that the system can accurately obtain a few key SQL query texts that have a greater impact on the overall performance of the database.

[0033] Step 102, traversing the abstract syntax tree, and performing normalization processing on the abstract syntax tree based on a preset structure rule.

[0034] In an implementation form of the application, the abstract syntax tree is processed in a structured manner; wherein the structured processing comprises at least one of a constant position exchange, a field order adjustment processing, a function name lowercasing processing and an alias restoring processing, and / or an IN list is determined in the abstract syntax tree and the IN list is sorted, and / or redundant parentheses are determined in the abstract syntax tree and the redundant parentheses are deleted, and / or a constant in the abstract syntax tree is replaced by a placeholder to generate a parameterized intermediate representation.

[0035] Specifically, the SQL text collected by the query log collection module is converted into an abstract syntax tree using a SQL parser, the abstract syntax tree is traversed, and the most critical elements for optimizing indexes are extracted:

[0036] Filter condition: WHERE column =?;

[0037] Correlation condition: JOIN... ON t1.col = t2.col;

[0038] Sort field: ORDER BY column;

[0039] Grouping field: GROUP BY column.

[0040] A structured feature set containing these critical fields and their operators (=, >, LIKE, etc.) is output.

[0041] It should be noted that the normalization processing in the embodiments of the application is to solve the problem of the same intent but different structural writing. For example, SELECT * FROM users WHERE id = 100 and SELECT * FROM users WHERE 100 = id should be identified as the same intent and uniformly clustered.

[0042] Further, the target SQL text is parsed into an abstract syntax tree, and specifically, an open source SQL parser can be used to parse the original SQL statement into a structured abstract syntax tree, and to strip spaces, SQL case, table alias and other non-essential differences. The following standardization operations are implemented at the abstract syntax tree node level:

[0043] Constant position exchange, for example, 100 = id is uniformly converted to id = 100;

[0044] Field order standardization, for example, ORDER BY b, a is sorted by field name as ORDER BY a, b;

[0045] IN list ordering, e.g., id IN (3, 1, 2) is normalized to id IN (1, 2, 3);

[0046] Remove redundant parentheses, e.g., (a = 1) is converted to a = 1;

[0047] Lowercase function names: e.g., UPPER(x) is converted to upper(x);

[0048] Alias resolution: combined with database metadata, e.g., u.id is mapped to the actual column users.id;

[0049] Generate parameterized intermediate representation: replace all constants with placeholders?, output uniform format intermediate SQL template, e.g.,

[0050] SELECT name FROM users WHERE id =? AND status =?.

[0051] Step 103, core feature extraction is performed on the normalized abstract syntax tree, and the extracted core features are spliced to generate a semantic signature, so as to divide a plurality of target SQL texts into a plurality of intent families based on the semantic signature.

[0052] In an implementation manner of the present application, structural normalization can process explicit syntax variants, but cannot identify complex semantic variants with different structures but equivalent logic such as LIKE 'abc%' and col>= 'abc' AND col<'abd', so deep semantic matching is still needed.

[0053] Specifically, the semantic signature is encoded into a high-dimensional vector by a lightweight model; wherein the semantic signature is composed of at least one of a filter condition feature, a correlation relationship feature, and an ordering direction feature. The cosine similarity between any two target SQL text vectors is determined. Based on the preset grouping threshold and the cosine similarity, a plurality of SQL texts are divided into a plurality of intent families.

[0054] Specifically, the key semantic fragments are extracted and the signature string is constructed, the core structure features are extracted from the normalized abstract syntax tree, and spliced into a semantic signature, including:

[0055] Filter condition: filter: "id=? AND status=?" ;

[0056] Correlation relationship: join: "users<->orders" ;

[0057] Ordering direction: order: "create_time DESC" ;

[0058] The final generated input text is: "filter:id=? status=?

[0059] join:users<->orders order:create_time DESC".

[0060] Further, a lightweight Sentence-BERT model is used to encode the semantic signatures into high-dimensional vectors, and the cosine similarity between any two SQL vectors is calculated. The similarity calculation formula is:

[0061] Similarity(A, B) = (AB) / (||A|| * ||B||);

[0062] where AB represents the dot product of vector A and vector B, and ||A|| and ||B|| represent the lengths of vector A and vector B, respectively. This formula is used to measure the similarity of two vectors in direction, and the result ranges from -1 to 1, with a larger value indicating a more similar vector. Further, the application embodiment can set the grouping threshold to 0.92, and if it is greater than the grouping threshold, it is determined to be the same intent family, so as to perform intent family grouping.

[0063] Step 104, generating an intent template based on multiple intent families, and performing index benefit analysis on each intent module in the intent template.

[0064] In an implementation manner of the present application, the template information corresponding to each intent family is determined; wherein the template information at least includes the parameterized target SQL text, the intent identifier, the table name and field corresponding to the target SQL text, the preset key field, the sorting column direction, the paging identifier, one of the execution frequency and the response time. Based on the template information, the 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 and the permission module are associated through the intent identifier. Based on the intent module after the association processing, the intent template is generated.

[0065] Specifically, the application embodiment finally outputs 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 =?. Thereafter, all analysis, evaluation and decision are based on the intent template rather than a single SQL, thereby improving the efficiency.

[0066] 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.

[0067] 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.

[0068] 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.

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

[0070] Specifically, in the system initialization and user registration stage, the user attribute set is collected and constructed through the user information input, role allocation and permission configuration process. Among them, the role attribute is divided according to the function of the user in the system, such as administrator, ordinary user; the department attribute is associated with the organizational structure to which the user belongs; the time attribute includes the time window allowed for operation; and the permission level attribute is divided according to the sensitivity of data. Such as public, internal, confidential. These attribute information can be dynamically updated to ensure that the user permission state is reflected in real time. Based on business requirements and security rules, at least two attributes are selected from the user attribute set and combined through logical operators to construct an attribute access strategy.

[0071] Further, for column-level permission information, the attribute access strategy is combined with the permission content, and an attribute-based encryption algorithm is used for encryption processing. In the encryption process, the algorithm generates an encryption key according to the attribute access strategy, converts the plaintext permission content into permission ciphertext, and generates a corresponding strategy description at the same time. The strategy description records the attributes and logical relationships involved in the strategy. The encrypted permission ciphertext and the strategy description are stored together to ensure that the permission information cannot be parsed by unauthorized users during storage and transmission.

[0072] Further, when the user initiates a data access request, the attribute set submitted by the user and the private key are received. The attribute set submitted by the user and the stored strategy description are parsed and compared, and the logical relationship set in the attribute access strategy is used to verify whether the user attributes meet the strategy requirements. For example, check whether the user role, department, time and permission level attributes meet the strategy condition combination. If the attribute set submitted by the user matches the attribute access strategy successfully, it indicates that the user has access permission, and the system uses the user's private key to decrypt the encrypted permission ciphertext. After decryption, the plaintext permission rule is obtained, which clearly indicates the data table field range and operation type that the user can access. Based on this plaintext rule, it is further judged whether the user request is legal, so as to decide whether to allow the user to perform the corresponding data access operation, thereby realizing dynamic permission control based on attributes.

[0073] The permission ciphertext in the embodiments of the present application is encrypted data converted from the plaintext permission rule through an attribute-based encryption algorithm. The permission ciphertext ensures that the permission information exists in the form of ciphertext during storage and transmission, so that even if the database or transmission link is attacked, unauthorized parties cannot directly obtain the permission content. In addition, the encryption process of the permission ciphertext in the embodiments of the present application depends on the attribute access policy, so that the ciphertext is strongly associated with the user attributes. When the user attributes change, such as department relocation or role upgrade, the decryption conditions of the ciphertext also change, realizing the dynamic validity and invalidity of the permission. For example, after the user is relocated from the finance department to the technology department, the attributes no longer meet the original permission policy, and even if the old private key is held, the corresponding ciphertext cannot be decrypted, ensuring that the permission is adjusted in real time with the user state.

[0074] In an implementation manner of the present application, the history index required data corresponding to the intent module is determined in the history database; wherein the history index required data at least includes one of history index response time, history resource consumption and history business importance level. And the query required data corresponding to the intent module in the case of no recommended index is determined; wherein the query required data at least includes one of query response time and query required resource consumption. The difference between the history index required data and the query required data is determined, and based on the preset weight distribution value and the difference, the index benefit analysis result corresponding to the intent module is obtained.

[0075] Specifically, the embodiment of the present application is provided with an index benefit analysis formula:

[0076] Score=α·PerformanceGain-β·ResourceCost+γ·PriorityWeight;

[0077] Wherein, PerformanceGain is the performance benefit, which is used to estimate how much the average response time of this type of query can be reduced after creating the target index. The estimation method is as follows: based on the statistical information of the database, such as table size and field cardinality, estimate the difference between the full table scan cost without index and the index scan cost with index in the history database. ResourceCost is the resource cost, that is, the additional overhead brought by the construction and maintenance of the index, including space cost and write overhead, wherein the space cost is the disk space occupied by the index, and the write overhead is the additional I / O and CPU consumption caused by the database maintaining the corresponding index structure every time the INSERT, UPDATE and DELETE is executed. PriorityWeight is the priority weight, that is, the importance in the business level. The queries from the core transaction link 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.

[0078] Step 105, based on the index benefit analysis result and the corresponding security window period of the intention module, the index is created.

[0079] In an implementation manner of the present application, in the case that the index benefit analysis result meets the benefit condition, the historical business related data of the target SQL text pair is determined. The historical business related data is analyzed based on the preset time window, and the corresponding data fluctuation in each time window is determined. The business low peak period is determined based on the data fluctuation, and the index is created in the business low peak period. After each interval of the preset period, the usage rate of the created index is detected, and in the case that the usage rate is less than the preset frequency threshold, the index is deleted.

[0080] Specifically, after determining that the index benefit analysis result meets the condition, the historical business data related to the target SQL text is extracted from the database history record, business log and other data sources, including multi-dimensional information such as business operation record, data access frequency, system performance index, etc. Based on the preset time window rule, the collected historical business related data is segmented, and for the data in each time window, the change trend of the key indicators is analyzed, including the increase and decrease of data volume, the fluctuation of business operation frequency, the high and low of system load, etc. The data fluctuation amplitude and change law are calculated, so as to determine the corresponding data fluctuation in each time window. According to the data fluctuation in each time window, the time period with small data fluctuation, low system resource utilization and relatively sparse business operation is selected as the business low peak period. In the determined business low peak period, the index creation process is started. According to the pre-planned index scheme, the index structure is constructed for the table and field involved in the target SQL text.

[0081] Further, the usage rate of the newly created index is detected in a preset period, for example, the index which is not used for 7 consecutive days is marked as long-term unused index, indicating that the index is not effectively utilized in the business scenario, which may cause waste of storage space and loss of data update performance. At this time, the index deletion mechanism is triggered, and the index needs to be confirmed twice before deletion to prevent misdeletion. After confirmation, DROP INDEX is executed. DROP INDEX is an operation in database management, which is used to delete the existing index in the database.

[0082] 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.

[0083] 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.

[0084] Figure 3 A flowchart for evaluating index revenue is provided for embodiments of this application, such as... Figure 3 As 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.

[0085] 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 4As shown, the database index recommendation device 200 based on query intention recognition comprises: at least one processor 201; and a memory 202 communicatively connected with the at least one processor 201; wherein the memory 202 stores instructions executable by the at least one processor 201, and the instructions are executed by the at least one processor 201 to enable the at least one processor 201 to: based on a preset query cost threshold, collect target SQL texts in a database and convert the target SQL texts into abstract syntax trees; traverse the abstract syntax trees, and perform normalization processing on the abstract syntax trees based on a preset structure rule; extract core features from the normalized abstract syntax trees, splice the extracted core features to generate semantic signatures, and divide a plurality of target SQL texts into a plurality of intention families based on the semantic signatures; generate intention templates based on the plurality of intention families, and perform index benefit analysis on each intention module in the intention templates; and perform index creation based on the index benefit analysis results and corresponding safety window periods of the intention modules.

[0086] The non-volatile computer storage medium provided by the embodiments of the present application stores computer executable instructions, which are configured to: based on a preset query cost threshold, collect target SQL texts in a database and convert the target SQL texts into abstract syntax trees; traverse the abstract syntax trees, and perform normalization processing on the abstract syntax trees based on a preset structure rule; extract core features from the normalized abstract syntax trees, splice the extracted core features to generate semantic signatures, and divide a plurality of target SQL texts into a plurality of intention families based on the semantic signatures; generate intention templates based on the plurality of intention families, and perform index benefit analysis on each intention module in the intention templates; and perform index creation based on the index benefit analysis results and corresponding safety window periods of the intention modules.

[0087] Each of the embodiments in the present application is described in a progressive manner, and the same or similar parts of each of the embodiments can be referred to each other. Each of the embodiments mainly describes the differences from other embodiments. In particular, for the device, equipment, and non-volatile computer storage medium embodiments, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can be referred to the part of the description of the method embodiments.

[0088] The above only describes the embodiments of the present application and is not used to limit the present application. The embodiments of the present application can have various modifications and changes for those skilled in the art. The modifications or replacements do not make the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. A database index recommendation method based on query intent recognition, characterized by, The method comprises: Based on the preset query cost threshold, the target SQL text is collected in the database, and the target SQL text is converted into an abstract syntax tree; The abstract syntax tree is traversed, and the abstract syntax tree is normalized based on the preset structure rule; Core feature extraction is performed on the normalized abstract syntax tree, and the extracted core features are spliced to generate a semantic signature, so as to divide a plurality of target SQL texts into a plurality of intent families based on the semantic signature; Based on a plurality of intent families, an intent template is generated, and index benefit analysis is performed on each intent module in the intent template; Based on the index benefit analysis result and the corresponding safety window period of the intent module, the index is created. 2.The database index recommendation method based on query intent recognition according to claim 1, characterized in that, The abstract syntax tree is traversed, and the abstract syntax tree is normalized based on the preset structure rule, which specifically comprises: The abstract syntax tree is structured; wherein the structured processing at least includes one of constant position exchange, field order adjustment processing, function name lowercasing processing and alias restoration processing; And / or, determine the IN list in the abstract syntax tree, and sort the IN list; And / or, determine the redundant parentheses in the abstract syntax tree, and delete the redundant parentheses; And / or, replace the constant in the abstract syntax tree with a placeholder to generate a parameterized intermediate representation form. 3.The database index recommendation method based on query intent recognition according to claim 1, characterized in that, The plurality of target SQL texts are divided into a plurality of intent families based on the semantic signature, which specifically comprises: The semantic signature is encoded into a high-dimensional vector through a lightweight model; wherein the semantic signature at least consists of one of filtering condition feature, correlation relationship feature and sorting direction feature; Determine the cosine similarity between any two target SQL text vectors; Based on the preset grouping threshold and the cosine similarity, the plurality of SQL texts are divided into a plurality of intent families. 4.The database index recommendation method based on query intent recognition according to claim 1, characterized in that, The intent template is generated based on a plurality of intent families, which specifically comprises: Determine the template information corresponding to each intent family respectively; wherein the template information at least includes one of parameterized target SQL text, intent identification, table name and field corresponding to the target SQL text, preset key field, sorting column direction, paging identification, execution frequency and response time; Based on the template information, the 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 and the permission module are associated through the intent identification; Based on the associated intent module, the intent template is generated. 5.The database index recommendation method based on query intent recognition according to claim 4, characterized in that, After the corresponding permission information is generated based on the template information, the method further comprises: Build a user attribute set; wherein the user attribute set at least includes at least one of role attribute, department attribute, time attribute and permission level attribute; At least two attributes in the attribute set are logically combined to build an attribute access strategy; Based on the attribute access strategy, column-level permission information is encrypted by an attribute-based encryption algorithm to generate encrypted permission ciphertext and a policy description; An attribute set submitted by a user and a private key are received, and the attribute set submitted by the user is matched and verified with the policy description; If the attribute set submitted by the user meets the policy description, the private key is used to decrypt the permission ciphertext to obtain a plaintext permission rule. 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: In the historical database, the historical indexing required data corresponding to the intent module is determined, wherein the historical indexing required data at least includes one of historical indexing response time, historical resource consumption and historical business importance level; And the query required data corresponding to the intent module in the case of no recommended indexing is determined, wherein the query required data at least includes one of query response time and query required resource consumption; The difference between the historical indexing required data and the query required data is determined; Based on the preset weight allocation value and the difference, the indexing benefit 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, Based on the indexing benefit analysis result and the security window period corresponding to the corresponding intent module, the index is created, specifically including: In the case that the indexing benefit analysis result meets the benefit condition, the historical business related data of the target SQL text pair is determined; Based on the preset time window, the historical business related data is analyzed to determine the corresponding data fluctuation in each time window; Based on the data fluctuation, the business low peak period is determined, and the index is created in the business low peak period; Every interval after a preset period, the usage rate of the created index is detected, and in the case that 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 indexing benefit analysis result and the security window period corresponding to the corresponding intent module, the method further includes: Obtain the performance index data corresponding to the database; wherein the performance index data at least includes one of query response time, IO waiting time and CPU load; After the database index is created and runs for a preset time length, the performance index data is collected again; The performance index data collected twice is compared, and if the comparison result is that the performance index data after index creation meets the preset performance condition, it is marked as a positive example; If the comparison result is that the performance index data after index creation does not meet the preset performance condition, it is marked as a misjudgment case; Based on the misjudgment case, the parameters in the benefit evaluation process are optimized and adjusted. 9.The database index recommendation device based on query intent recognition, characterized by, The device includes a memory for storing computer program instructions and a processor for executing program instructions, wherein when the computer program instructions are executed by the processor, the device triggers to execute the method of any one of claims 1-8.

10. A non-transitory computer storage medium storing computer-executable instructions, the computer-executable instructions comprising instructions for: receiving a request to access a file; determining whether the file is stored in a cache; and in response to determining that the file is stored in the cache, providing access to the file from the cache. The computer executable instructions can execute the method of any one of claims 1-8. The computer executable instructions can execute the method of any one of claims 1-8.

Images