Target index recommendation method, electronic device and computer-readable storage medium
Through the target index recommendation method, the evaluation of structured query statement sets and candidate index sets is used to generate the recommended index sets, which solves the problems of low index creation efficiency and poor accuracy in the database system and improves the efficiency of SQL query.
Patent Information
- Application Number
- CN202411166371.7
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-23
- Publication Date
- 2024-11-29
- Estimated Expiration
- 2044-08-23
AI Technical Summary
Index creation in existing database systems is inefficient and cannot guarantee the accuracy of indexes, resulting in low SQL query efficiency.
A target index recommendation method is proposed. By obtaining a structured query statement set, a candidate index set is determined, and an index income evaluation is performed on it, and a recommended index set is generated to improve the efficiency and accuracy of index creation. The method includes template processing, optimized compression, syntax parsing and virtual indexing techniques to optimize index design.
It improves the efficiency and accuracy of index creation, reduces manual intervention in index design, and improves the execution efficiency of SQL queries.
Smart Images

Figure CN118689879B_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the technical field of database processing, and in particular to a target index recommendation method, an electronic device, and a computer-readable storage medium. Background Art
[0002] In the information age, data management has become an important part of enterprise and organizational operations. As one of the core tools for data management, database systems are used for data storage and retrieval. In database systems, index design and optimization are crucial to the execution efficiency of SQL statements. A good index can change the execution mode of SQL from full table scan to index point query, thereby reducing the query time from seconds to milliseconds.
[0003] Currently, index design and adjustment are usually performed by database administrators based on relevant theoretical knowledge and experience, which results in low efficiency in index creation and inability to guarantee accurate index creation.
[0004] Therefore, a target index recommendation method is urgently needed. Summary of the invention
[0005] The present application at least provides a target index recommendation method, an electronic device and a computer-readable storage medium, which can improve the efficiency of creating indexes.
[0006] The first aspect of the present application provides a target index recommendation method, which includes: obtaining a target structured query statement set, wherein the target structured query statement set includes a target structured query statement; determining a target candidate index set based on each target structured query statement in the target structured query statement set; performing index benefit evaluation processing on the target structured query statements in the target structured query statement set based on the candidate indexes in the target candidate index set to obtain a recommended index set.
[0007] In one embodiment, the step of obtaining the target structured query statement set includes: obtaining an initial structured query statement set; performing template processing on each initial structured query statement in the initial structured query statement set to obtain statement template information corresponding to each initial structured query statement; performing optimization and compression processing on each initial structured query statement according to the statement template information corresponding to each initial structured query statement to obtain the target structured query statement set.
[0008] In one embodiment, the statement template information includes a statement template, and the step of optimizing and compressing each initial structured query statement according to the statement template information corresponding to each initial structured query statement to obtain the target structured query statement set includes: obtaining a current optimized data set, wherein the optimized and compressed initial structured query statements stored in the current optimized data set are determined based on historical traversal, and the historical traversal time is earlier than the current traversal time; traversing the initial structured query statement set; in response to the statement template of the optimized and compressed initial structured query statement in the current optimized data set being the same as the statement template corresponding to the current initial structured query statement, accumulating the number of initial structured query statements belonging to the statement template; continuing to traverse the next initialization structured query statement until the initial structured query statement set is traversed, and determining the target structured query statement set based on the current optimized data set.
[0009] In one embodiment, the method further includes: in response to the fact that there is no statement template of the optimized and compressed initial structured query statement in the current optimized data set that is identical to the statement template corresponding to the current initial structured query statement, optimizing and compressing the statement according to the similarity between the current initial structured query statement and each optimized and compressed initial structured query statement in the current optimized data set; until the initial structured query statement set is traversed, and the current optimized data set is used as the target structured query statement set.
[0010] In one embodiment, the step of performing index benefit evaluation processing on the target structured query statements in the target structured query statement set according to the candidate indexes in the target candidate index set to obtain a recommended index set includes: traversing the target candidate index set, generating a current virtual index set according to the current candidate index and the current optimized index in the obtained current optimized index set, wherein the current optimized index set is used to store the optimized candidate index; performing index benefit evaluation processing on the target structured query statement set based on the current virtual index set to obtain a benefit valuation corresponding to the current candidate index; and updating the current optimized index set based on the benefit valuation corresponding to each candidate index in the target candidate index set until an optimized index set that meets the requirements is obtained.
[0011] In one embodiment, the step of performing index benefit evaluation processing on the target structured query statement set based on the current virtual index set to obtain a benefit valuation corresponding to the current candidate index includes: performing index benefit evaluation processing on the target structured query statement set to obtain a first total cost value; adding the virtual index in the current virtual index set to the target structured query statement set; performing index benefit evaluation processing on the target structured query statement set added with the virtual index to obtain a second total cost value; and using the difference between the second total cost value and the first total cost value as the benefit valuation corresponding to the current candidate index.
[0012] In one embodiment, the step of updating the optimized index set based on the profit valuation corresponding to each candidate index in the target candidate index set until an optimized index set that meets the requirements is obtained includes: determining a target historical candidate index from each historical candidate index according to the profit valuation corresponding to each historical candidate index when traversing the previous candidate index set; selecting a currently traversed target candidate index from the target candidate index set according to the profit valuation corresponding to each candidate index in the target candidate index set; in response to the profit valuation of the target historical candidate index being less than or equal to the profit valuation of the target candidate index, adding the target candidate index to the current optimized index set, deleting the target candidate index from the target candidate index set, continuing to traverse the next candidate index, and deleting the target candidate index set for the next round of traversal; in response to the profit valuation of the target historical candidate index being greater than the profit valuation of the target candidate index, determining whether the current optimized index set meets the requirements according to the number of indexes in the current optimized index set and / or the profit valuation of the target candidate index; if the current optimized index set meets the requirements, using the optimized index set that meets the requirements as the recommended index set.
[0013] In one embodiment, the step of determining a target candidate index set based on each target structured query statement in the target structured query statement set includes: performing syntax parsing processing on each of the target structured query statements to obtain syntax tree data of the target structured query statement; analyzing and processing the syntax tree data of the target structured query statement to obtain an information column of the target structured query statement; generating a candidate index based on the information column of the target structured query statement; and in response to the generated candidate index not existing in the database system and not existing in the acquired current candidate index set, adding the generated candidate index to the current candidate index set to obtain the target candidate index set.
[0014] A second aspect of the present application provides an electronic device, including a memory and a processor, wherein the processor is used to execute program instructions stored in the memory to implement the above-mentioned target index recommendation method.
[0015] A third aspect of the present application provides a computer-readable storage medium having program instructions stored thereon, which implement the above-mentioned target index recommendation method when the program instructions are executed by a processor.
[0016] The above scheme obtains a target structured query statement set, which includes a target structured query statement; determines a target candidate index set according to each target structured query statement in the target structured query statement set; and performs index benefit evaluation processing on the target structured query statement in the target structured query statement set according to the candidate indexes in the target candidate index set to obtain a recommended index set. On the one hand, considering the impact between indexes, the index benefit evaluation of the target structured query statement according to the candidate indexes can improve the accuracy of obtaining the recommended index; on the other hand, the recommended index is constructed according to the target candidate index determined by the target structured query statement, which can improve the efficiency of constructing the recommended index.
[0017] It is to be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present application. BRIEF DESCRIPTION OF THE DRAWINGS
[0018] The drawings herein are incorporated into the specification and constitute a part of the specification. These drawings illustrate embodiments consistent with the present application and are used together with the specification to illustrate the technical solution of the present application.
[0019] Figure 1 is a flowchart of an exemplary embodiment of a target index recommendation method shown in the present application;
[0020] Figure 2 yes Figure 1 FIG. 1 is a flow chart of an exemplary embodiment of step S110 in the target index recommendation method;
[0021] Figure 3 yes Figure 2 FIG. 1 is a flow chart of an exemplary embodiment of step S230 in the target index recommendation method;
[0022] Figure 4 yes Figure 1 FIG. 1 is a flow chart of an exemplary embodiment of step S130 in the target index recommendation method;
[0023] Figure 5 yes Figure 4 FIG. 1 is a flow chart of an exemplary embodiment of step S430 in the target index recommendation method;
[0024] Figure 6 yes Figure 1FIG. 1 is a flow chart of an exemplary embodiment of step S120 in the target index recommendation method;
[0025] Figure 7 is a block diagram of a target index recommendation system shown in an exemplary embodiment of the present application;
[0026] Figure 8 It is a structural schematic diagram of an embodiment of the electronic device of the present application;
[0027] Fig. 9 It is a structural diagram of an embodiment of a computer-readable storage medium of the present application. DETAILED DESCRIPTION
[0028] The scheme of the embodiment of the present application is described in detail below in conjunction with the drawings of the specification.
[0029] In the following description, for the purpose of explanation rather than limitation, specific details such as specific system structures, interfaces, and technologies are provided to facilitate a thorough understanding of the present application.
[0030] The term "and / or" in this article is only a description of the association relationship of associated objects, indicating that there may be three relationships. For example, A and / or B can represent: A exists alone, A and B exist at the same time, and B exists alone. In addition, the character " / " in this article generally indicates that the objects associated before and after are in an "or" relationship. In addition, "many" in this article means two or more than two. In addition, the term "at least one" in this article means any combination of at least two of any one or more of a plurality of, for example, including at least one of A, B, and C, can mean including any one or more elements selected from the set consisting of A, B, and C.
[0031] First of all, it should be noted that in order to improve the efficiency of index creation. This application proposes a target index recommendation method, an electronic device and a computer-readable storage medium. For details, please refer to Figure 1 , Figure 1 It is a flowchart of an exemplary embodiment of the target index recommendation method shown in the present application.
[0032] The target index recommendation method may be executed by a terminal device or a server or other processing device, wherein the terminal device may be a user equipment (UE), a computer, a mobile device, a user terminal, a terminal, a cellular phone, a cordless phone, a personal digital assistant (PDA), a handheld device, a computing device, a vehicle-mounted device, a wearable device, etc. The target index recommendation method may also be executed by a target index recommendation system with data processing and analysis capabilities. In some possible implementations, the target index recommendation method may be implemented by a processor calling computer-readable instructions stored in a memory.
[0033] Specifically, the target index recommendation method of this embodiment includes the following steps:
[0034] Step S110, obtaining a target structured query statement set, wherein the target structured query statement set includes a target structured query statement.
[0035] SQL (Structured Query Language) is a special-purpose programming language. It is a database query and programming language used to access data and query, update, and manage relational database systems.
[0036] The target structured query statement set refers to a query load SQL set, which includes query load SQL statements, that is, structured query statements.
[0037] The target structured query statement set of the embodiment of the present application can be directly obtained from the database. It can also be obtained by processing the query load SQL set initially obtained. Exemplarily, the target index recommendation system can also merge the query load SQLs in the query load SQL set initially obtained according to the statement similarity between the query load SQLs in the query load SQL set initially obtained to obtain the target structured query statement set. Of course, it is also possible to merge the query load SQLs in the query load SQL set initially obtained according to the key information similarity between the query load SQLs to obtain the target structured query statement set.
[0038] Step S120 : determining a target candidate index set according to each target structured query statement in the target structured query statement set.
[0039] The target candidate index set is a set formed by candidate indexes.
[0040] Exemplarily, the target index recommendation system can traverse the target structured query statements in the target structured query statement set, and adjust the current candidate index set according to the candidate index generated when traversing each target structured query statement, until the target structured query statements in the target structured query statement set are traversed to obtain the target candidate index set. In another exemplary embodiment, the target index recommendation system of the embodiment of the present application can perform index generation processing on each target structured query statement in the target structured query statement set to obtain the candidate index corresponding to each target structured query statement; and then construct a target candidate index set based on the candidate index corresponding to each target structured query statement. Furthermore, considering that the same index may exist in the candidate index generated by each target structured query statement, the target index recommendation system can perform index deduplication processing on the candidate index corresponding to each target structured query statement to obtain a target candidate index set.
[0041] Step S130 , performing index benefit evaluation processing on the target structured query statement in the target structured query statement set according to the candidate indexes in the target candidate index set to obtain a recommended index set.
[0042] Index benefit evaluation refers to executing the explain command on the SQL statement to view the execution plan planned by the optimizer in the explain statement before and after the index is created, and to verify whether the index to be created is adopted by the database and whether there is any performance improvement.
[0043] The target index recommendation system of the embodiment of the present application performs index benefit evaluation processing on the target structured query statement in the target structured query statement set according to the candidate index in the target candidate index set to obtain the target recommended index set. After that, the target index recommendation system can perform index creation processing based on the recommended index set. Specifically, the target index recommendation system can determine whether to create a recommended index set based on the system load, disk space and index data volume. In response to at least one of the system load, disk space and index data volume meeting the requirements, the index creation processing is performed based on the index in the target recommended index set; otherwise, the user is reminded to create offline. It should be noted that the system load includes IO, CPU and memory. The system load meets the requirements, which may be that the memory required to create the recommended index set is less than the current free memory of the system. It can also be that the disk space required to create the target recommended index set is less than the system disk free space. The embodiment of the present application does not limit this.
[0044] It can be seen that the target index recommendation method of the embodiment of the present application obtains a target structured query statement set, and the target structured query statement set includes a target structured query statement; determines a target candidate index set according to each target structured query statement in the target structured query statement set; and performs index benefit evaluation processing on the target structured query statement in the target structured query statement set according to the candidate indexes in the target candidate index set to obtain a recommended index set. On the one hand, considering the impact between indexes, performing index benefit evaluation on the target structured query statement according to the candidate indexes can improve the accuracy of obtaining the recommended index; on the other hand, constructing a recommended index according to the target candidate index in the target candidate index set determined by the target structured query statement can improve the efficiency of constructing the recommended index.
[0045] Based on the above embodiments, the present application embodiment describes in detail how to obtain the target structured query statement set. Figure 2 , Figure 2 yes Figure 1 The flowchart of an exemplary embodiment of step S110 in the target index recommendation method is shown. Specifically, based on the above embodiment, the process of obtaining the target structured query statement set in step S110 also includes the following steps:
[0046] Step S210, obtaining an initial structured query statement set.
[0047] The initial structured query statement set includes an initial structured query statement.
[0048] Step S220 , performing template processing on each initial structured query statement in the initial structured query statement set to obtain statement template information corresponding to each initial structured query statement.
[0049] Template processing can be to extract key information from SQL statements, retain sorting, grouping and other specific operation information, or to obtain information such as table names and column names of SQL statements.
[0050] The statement template information may include a statement template of a structured query statement, information columns in the structured query statement, and the like.
[0051] The target index recommendation system performs template processing on each initial structured query statement in the initial structured query statement set to obtain statement template information corresponding to each initial structured query statement. For example, the initial structured query statement is SELECT PN_NUM, REP_ID FROM P_ALARM WHERE CHANNEL_CODE IN ('A', 'B', 'C'), and after the template processing, its statement template is SELECTPN_NUM, REP_ID FROM P_ALARM WHERECHANNEL_CODE IN (???).
[0052] Step S230 , optimizing and compressing each initial structured query statement according to the statement template information corresponding to each initial structured query statement to obtain a target structured query statement set.
[0053] Optimized compression processing refers to merging the same or similar target structured query statements in the target structured query statement set.
[0054] As an example, the target index recommendation system of the embodiment of the present application can optimize and compress the sentence templates in the sentence template information corresponding to the initial structured query statement to obtain the target structured query statement set. As another example, the target structured query statement set can also be obtained by compressing the key information in the sentence template information corresponding to the initial structured query statement.
[0055] It can be seen that the target index recommendation method of the embodiment of the present application obtains the statement template information corresponding to each initial structured query statement by performing template processing on each initial structured query statement in the initial structured query statement set; and optimizes and compresses each initial structured query statement according to the statement template information corresponding to each initial structured query statement to obtain the target structured query statement set. By merging the same or similar initial structured query statements in the initial structured query statement set through the statement template information, the SQL statements required to be processed can be reduced, the data processing efficiency is improved, and the processing efficiency of the index recommendation is further improved.
[0056] Based on the above embodiments, the embodiments of the present application describe in detail how to optimize and compress each initial structured query statement according to the statement template information to obtain a target structured query statement set. Figure 3 , Figure 3 yes Figure 2The flowchart of an exemplary embodiment of step S230 in the target index recommendation method is shown. Specifically, based on the above embodiment, step S230 optimizes and compresses each initial structured query statement according to the statement template information corresponding to each initial structured query statement, and the process of obtaining the target structured query statement set also includes the following steps:
[0057] Step S310, obtaining the current optimized data set, wherein the optimized and compressed initial structured query statement stored in the current optimized data set is determined based on a historical traversal, and the historical traversal time is earlier than the current traversal time.
[0058] The optimized data set is created in advance and is used to store the optimized and compressed initial structured query statements. It should be noted that the optimized and compressed initial structured query statements do not exist in the initially created optimized data set. As the target index recommendation system traverses the initial structured query statements in the initial structured query statement set, the optimized data set will be updated as the traversal proceeds. When the target index recommendation system traverses to the current initial structured query statement, the current optimized data set in the system is determined based on the historical traversal, that is, the last traversal of the current traversal. Among them, the historical traversal time is earlier than the current traversal time.
[0059] Step S320, traversing the initial structured query statement set.
[0060] The target index recommendation system traverses the initial structured query statement set, and determines whether the statement template of the currently traversed initial structured query statement is the same as the statement template of an optimized and compressed initial structured query statement in the current optimized data set. If the same template exists, step S330 is executed; if the same template does not exist, step S350 is executed.
[0061] Step S330, in response to the statement template of the optimized and compressed initial structured query statement in the current optimized data set being the same as the statement template corresponding to the current initial structured query statement, the number of statements of the initial structured query statement belonging to the statement template is accumulated.
[0062] The target index recommendation system of the embodiment of the present application determines that the statement template of the optimized and compressed initial structured query statement in the current optimized data set is the same as the statement template corresponding to the current initial structured query statement, and then accumulates the number of statements of the initial structured query statements belonging to the same statement template. In other words, the target index recommendation system merges the query statements of the same template in the current initial structured query statement set, and only retains one SQL statement with the same template in the optimized data set to reduce the amount of data processing in the initial structured query statement set.
[0063] Step S340 , continue to traverse the next initial structured query statement until the initial structured query statement set is traversed, and determine the target structured query statement set based on the current optimized data set.
[0064] The target index recommendation system compares the statement template of each initial structured query statement in the initial structured query statement set with the statement template of the optimized and compressed initial structured query statement in the current optimized data set until the initial structured query statement set is traversed, and then the current optimized data set is determined as the target structured query statement set.
[0065] Step S350, in response to the fact that there is no statement template of the optimized and compressed initial structured query statement in the current optimized data set that is identical to the statement template corresponding to the current initial structured query statement, optimization and compression processing is performed based on the similarity between the current initial structured query statement and each optimized and compressed initial structured query statement in the current optimized data set.
[0066] The target index recommendation system of the embodiment of the present application determines that there is no sentence template of the optimized and compressed initial structured query statement in the current optimized data set that is the same as the sentence template corresponding to the current initial structured query statement, and then performs optimization and compression processing according to the similarity between the current initial structured query statement and each optimized and compressed initial structured query statement in the current optimized data set. That is, query statements with similar templates in the current initial structured query statement set are merged. Exemplarily, the target index recommendation system can calculate the cosine similarity of the statement template between the initial structured query statement and each optimized and compressed initial structured query statement in the current optimized data set; in response to the cosine similarity being greater than the similarity threshold, the number of initial structured query statements belonging to the same statement template is accumulated, and the process jumps to step S340 to continue traversing the next initialized structured query statement until the initial structured query statement set is traversed, and the target structured query statement set is determined based on the current optimized data set; in response to the cosine similarity being less than or equal to the similarity threshold, the initial structured query statement is added to the current optimized data set to obtain an updated optimized data set, and the number of initial structured query statements added is counted as one.
[0067] In other embodiments, the target index recommendation system may input the current initial structured query statement and each optimized and compressed initial structured query statement in the current optimized data set into a pre-trained network model, and the network model is used to determine the optimized and compressed initial structured query statement similar to the current initial structured query statement.
[0068] Step S360, until the initial structured query statement set is traversed, the current optimized data set is used as the target structured query statement set.
[0069] It can be seen that the target index recommendation method of the embodiment of the present application obtains the current optimized data set, and the optimized compressed initial structured query statement stored in the current optimized data set is determined based on the historical traversal, and the historical traversal time is earlier than the current traversal time; traverse the initial structured query statement set; in response to the statement template of the optimized compressed initial structured query statement in the current optimized data set being the same as the statement template corresponding to the current initial structured query statement, the number of statements of the initial structured query statement belonging to the statement template is accumulated; continue to traverse the next initialization structure query statement until the initial structured query statement set is traversed, and the target structured query statement set is determined based on the current optimized data set; in response to the statement template of the optimized compressed initial structured query statement not existing in the current optimized data set being the same as the statement template corresponding to the current initial structured query statement, the current initial structured query statement is optimized and compressed according to the similarity between the current initial structured query statement and each optimized compressed initial structured query statement in the current optimized data set; until the initial structured query statement set is traversed, the current optimized data set is used as the target structured query statement set. This can reduce the amount of data processing in the initial structured query statement set and improve data processing efficiency.
[0070] Based on the above embodiments, the present application embodiment describes in detail how to determine the recommended index set. Figure 4 , Figure 4 yes Figure 1 The flowchart of an exemplary embodiment of step S130 in the target index recommendation method is shown. Specifically, based on the above embodiment, step S130 performs index benefit evaluation processing on the target structured query statement in the target structured query statement set according to the candidate index in the target candidate index set, and the process of obtaining the recommended index set also includes the following steps:
[0071] Step S410, traverse the target candidate index set, create a current virtual index set according to the current candidate index and the current optimized index in the obtained current optimized index set, and the current optimized index set is used to store the optimized candidate index.
[0072] The optimized index set is created in advance and is used to store the optimized candidate indexes. It should be noted that the optimized candidate index does not exist in the initially created optimized index set. As the target index recommendation system traverses the candidate indexes in the target candidate index set, the optimized index set will be updated as the traversal proceeds. When the target index recommendation system traverses to the current candidate index, the current optimized index set in the system is determined based on the historical traversal, that is, based on the last traversal of the current traversal. The historical traversal time is earlier than the current traversal time.
[0073] The target index recommendation system traverses the target candidate index set, uses the virtual index technology to perform benefit evaluation processing, and obtains the recommended index set. Among them, the virtual index technology refers to the use of the virtual index function to obtain the cost estimate of the execution plan with this index without actually creating the index. For a given index table name and column name, a virtual index can be established inside the database. The virtual index only has the meta information of the index to be created, and does not actually create a physical index file, thereby avoiding the creation overhead of the real index. The virtual index is only used to display the possible execution path and cost estimate of the optimizer through the explain statement, and cannot provide a real index scan. By executing the explain command for each SQL statement, you can view the execution plan planned by the optimizer before and after the index is created, check whether the index to be created is adopted by the database, and whether there is a performance improvement. Based on this, the target index recommendation system of the embodiment of the present application generates a current virtual index set based on the current candidate index and the current optimized index in the current optimized index set obtained. Specifically, the current candidate index and the optimized index in the current optimized index set are used as virtual indexes to obtain a virtual index set corresponding to the current candidate index.
[0074] Step S420: performing index benefit evaluation processing on the target structured query statement set based on the created current virtual index set to obtain a benefit estimation value corresponding to the current candidate index.
[0075] The benefit valuation is the cost difference obtained by executing the explain command before and after creating the current virtual index set. Exemplarily, in the absence of a virtual index, the target index recommendation system performs an index benefit evaluation process on the target structured query statement set to obtain a first total cost value; after the current virtual index is created, the target index recommendation system performs a virtual index benefit evaluation process on the target structured query statement through the created current virtual index to obtain a second total cost value; the difference between the second total cost value and the first total cost value is used as the benefit valuation corresponding to the current candidate index. For example, the first total cost value is represented by costA, and the second total cost value is costB, then the benefit valuation corresponding to the current candidate index is costA-costB. It should be noted that for the determination method of the first total cost value, the total cost value of each target structured query statement with the same statement template can be determined according to the product between the cost value of each target structured query statement and the number of statements corresponding to the same statement template in the target structured query statement set, and then the sum of the total cost values corresponding to each statement template in the target structured query statement set is used as the total cost value of the target structured query statement set.
[0076] It should be noted that, in the embodiment of the present application, before constructing the virtual index set, there is no virtual index set in the system, and after the revenue valuation is performed based on the constructed virtual index set, the virtual index set created this time needs to be deleted.
[0077] Step S430 , updating the current optimized index set based on the revenue valuation corresponding to each candidate index in the target candidate index set, until an optimized index set that meets the requirements is obtained.
[0078] As an example, the target index recommendation system can select the candidate index with the maximum profit valuation from the profit valuations corresponding to each candidate index in the target candidate index set, and update the current optimized index set according to the size relationship between the candidate index with the maximum profit valuation and the preset profit valuation threshold. Specifically, if the candidate index with the maximum profit valuation is less than the preset profit valuation threshold, the corresponding candidate index is added to the current optimized index set to obtain an updated optimized index set, until the number of optimized indexes in the updated optimized index set meets the threshold.
[0079] Step S440: taking the optimized index set that meets the requirements as the recommended index set.
[0080] The optimized index set that meets the requirements may be that the number of optimized indexes in the optimized index set reaches a threshold, or the revenue valuation of the candidate index in the target candidate index set reaches a threshold.
[0081] It can be seen that the target index recommendation system of the embodiment of the present application traverses the target candidate index set, generates a current virtual index set based on the current candidate index and the current optimized index in the obtained current optimized index set, and the current optimized index set is used to store the optimized candidate index; performs index benefit evaluation processing on the target structured query statement set based on the current virtual index set to obtain the benefit valuation corresponding to the current candidate index; updates the current optimized index set based on the benefit valuation corresponding to each candidate index in the target candidate index set until an optimized index set that meets the requirements is obtained; and uses the optimized index set that meets the requirements as the recommended index set. Therefore, index recommendation based on virtual index technology can avoid the overhead of creating a real index, making the recommended index more accurate.
[0082] Based on the above examples, please continue to refer to Figure 5 , Figure 5 yes Figure 4 The flowchart of an exemplary embodiment of step S430 in the target index recommendation method is shown. Specifically, based on the above embodiment, step S430 updates the current optimized index set based on the revenue valuation corresponding to each candidate index in the target candidate index set until the optimized index set that meets the requirements is obtained, and the process also includes the following steps:
[0083] Step S510 , determining a target historical candidate index from among the historical candidate indexes according to the revenue valuations corresponding to the historical candidate indexes when traversing the previous candidate index set.
[0084] The previous candidate index set refers to an index set that is traversed earlier than the target candidate index set. The target candidate index set is obtained after the candidate index set is traversed for the last time. That is to say, the candidate index set traversed for the first time by the target index recommendation system is the previous candidate index set, and the candidate index set traversed for the second time is the target candidate index set. Exemplarily, after the candidate index set is traversed for the first time, the traversed candidate index set is index deleted to obtain the target candidate index set for the second traversal. At this time, the number of candidate indexes in the target candidate index set is greater than the number of indexes in the candidate index set traversed for the last time. Another exemplary method is that after the candidate index set is traversed for the first time, the target candidate index set may also be the same as the previous candidate index set, that is, the number of candidate indexes in the target candidate index set is the same as the number of indexes in the candidate index set traversed for the last time. It should be noted that the first and second in the embodiments of the present application are only for the purpose of clearly explaining the previous candidate index set and the target candidate index set, and do not constitute a limitation on the number of traversals.
[0085] The target historical candidate index may be an index with the largest estimated benefit in the previous candidate index set, or an index with a estimated benefit that reaches a threshold in the previous candidate index set.
[0086] Step S520 , selecting a currently traversed target candidate index from the target candidate index set according to the revenue valuation corresponding to each candidate index in the target candidate index set.
[0087] The target candidate index may be an index with the largest estimated benefit in the target candidate index set, or an index with a estimated benefit that reaches a threshold in the target candidate index set.
[0088] Step S530, in response to the revenue valuation of the target historical candidate index being less than or equal to the revenue valuation of the target candidate index, the target candidate index is added to the current optimized index set, and the target candidate index is deleted from the target candidate index set, and the traversal of the next candidate index continues, and the target candidate index set with the target candidate index deleted is used for the next round of traversal.
[0089] The target index recommendation system of the embodiment of the present application updates the current preferred index set based on the size relationship between the revenue valuation of the target historical candidate index and the revenue valuation of the target candidate index. Specifically, determine whether the revenue valuation of the target historical candidate index is less than or equal to the revenue valuation of the target candidate index. If so, add the target candidate index to the current optimized index set, delete the target candidate index from the target candidate index set, and continue to traverse the next candidate index. It should be noted that the target candidate index set from which the target candidate index is deleted is used for the next round of traversal. Continuing with the example in step S510, the target candidate index set from which the target candidate index is deleted is also the candidate index set for the third traversal.
[0090] Step S540, in response to the profit valuation of the target historical candidate index being greater than the profit valuation of the target candidate index, determine whether the current optimized index set meets the requirements based on the number of indexes in the current optimized index set and / or the profit valuation of the target candidate index, until an optimized index set that meets the requirements is obtained.
[0091] If the target index recommendation system of the embodiment of the present application determines that the revenue valuation of the target historical candidate index is greater than the revenue valuation of the target candidate index, then it determines whether the current optimized index set meets the requirements based on the number of indexes in the current optimized index set and / or the revenue valuation of the target candidate index. If the requirements are met, an optimized index set that meets the requirements is obtained, that is, a recommended index set. Exemplarily, if the revenue valuation of the target historical candidate index is greater than the revenue valuation of the target candidate index, the target index recommendation system can determine that the current optimized index set meets the requirements when the number of indexes in the current optimized index set is greater than a threshold value for the number of indexes. As another example, if the revenue valuation of the target historical candidate index is greater than the revenue valuation of the target candidate index, the target index recommendation system can determine that the current optimized index set meets the requirements when the revenue valuation of the target candidate index is greater than a revenue valuation threshold.
[0092] It can be seen that the target index recommendation method of the embodiment of the present application determines the target historical candidate index from each historical candidate index according to the revenue valuation corresponding to each historical candidate index when traversing the previous candidate index set; selects the currently traversed target candidate index from the target candidate index set according to the revenue valuation corresponding to each candidate index in the target candidate index set; in response to the revenue valuation of the target historical candidate index being less than or equal to the revenue valuation of the target candidate index, the target candidate index is added to the current optimized index set, and the target candidate index is deleted from the target candidate index set, and the next candidate index is traversed continuously, and the target candidate index set of the target candidate index is deleted for the next round of traversal; in response to the revenue valuation of the target historical candidate index being greater than the revenue valuation of the target candidate index, it is determined whether the current optimized index set meets the requirements according to the number of indexes in the current optimized index set and / or the revenue valuation of the target candidate index; if the current optimized index set meets the requirements, the optimized index set that meets the requirements is used as the recommended index set. This can improve the accuracy of index recommendation.
[0093] Based on the above examples, please continue to refer to Figure 6 , Figure 6 yes Figure 1 The flowchart of an exemplary embodiment of step S120 in the target index recommendation method is shown. Specifically, based on the above embodiment, the process of step S120 determining the target candidate index set according to each target structured query statement in the target structured query statement set further includes the following steps:
[0094] Step S610: Perform syntax analysis on each target structured query statement to obtain syntax tree data of the target structured query statement.
[0095] The target index recommendation system of the embodiment of the present application traverses the target structured query statement set, performs syntax parsing processing on each target structured query statement, and obtains syntax tree data of the target structured query statement.
[0096] Step S620: Analyze and process the syntax tree data of the target structured query statement to obtain an information column of the target structured query statement.
[0097] The target index recommendation system can determine the information columns of the target structured query statements from the syntax tree data corresponding to each target structured query statement. For example, the information columns of the target structured query statement can be the =,>,<, between, in related operation columns in the where condition, the sorting column of Order By, the aggregation column of Group By, the MIN and MAX function columns, and the condition column of Join operation.
[0098] Step S630: Generate candidate indexes based on the information columns of the target structured query statement.
[0099] The target index recommendation system can sort and combine the information columns of the target structured query statement to obtain candidate indexes.
[0100] Step S640 , in response to the generated candidate index not existing in the database system and not existing in the acquired current candidate index set, the generated candidate index is added to the current candidate index set to obtain a target candidate index set.
[0101] The current candidate index set is a candidate index set determined after traversing the previous target structured query statement. The target candidate index set of the embodiment of the present application is used to determine the candidate index set of the next target structured query statement.
[0102] The target index recommendation system of the embodiment of the present application determines the target candidate index set based on whether the candidate index corresponding to each target structured query statement exists in the database system and in the current candidate index set obtained. Specifically, if the generated candidate index exists in the database system and also exists in the current candidate index set, the corresponding candidate index is deleted from the current candidate index set, and then the current candidate index set with the corresponding candidate index deleted is used as the target candidate index set. If the generated candidate index does not exist in the database system and does not exist in the current candidate index set obtained, the generated candidate index is added to the current candidate index set to obtain the target candidate index set. If the generated candidate index does not exist in the database system but exists in the current candidate index set obtained, the corresponding candidate index is ignored.
[0103] It can be seen that the target index recommendation method of the embodiment of the present application performs syntax analysis processing on each target structured query statement to obtain the syntax tree data of the target structured query statement; analyzes and processes the syntax tree data of the target structured query statement to obtain the information column of the target structured query statement; generates a candidate index based on the information column of the target structured query statement; in response to the generated candidate index not existing in the database system and not existing in the current candidate index set obtained, adds the generated candidate index to the current candidate index set to obtain the target candidate index set. By traversing the target structured query statements in the target structured query statement set and determining the target candidate index set according to the candidate index generated by each target structured query statement, the accuracy of the target candidate index set can be improved.
[0104] Figure 7 FIG. 1 is a block diagram of a target index recommendation system shown in an exemplary embodiment of the present application. Figure 7 As shown, the exemplary target index recommendation system 700 includes: an acquisition module 710, a traversal module 720 and an index recommendation module 730. Specifically:
[0105] The acquisition module 710 is used to acquire a target structured query statement set, where the target structured query statement set includes a target structured query statement.
[0106] The traversal module 720 is used to determine a target candidate index set according to each target structured query statement in the target structured query statement set.
[0107] The index recommendation module 730 is used to perform index benefit evaluation processing on the target structured query statement in the target structured query statement set according to the candidate indexes in the target candidate index set to obtain a recommended index set.
[0108] In the exemplary target index recommendation system, a target structured query statement set is obtained, and the target structured query statement set includes a target structured query statement; a target candidate index set is determined according to each target structured query statement in the target structured query statement set; and an index benefit evaluation process is performed on the target structured query statement in the target structured query statement set according to the candidate index in the target candidate index set to obtain a recommended index set. On the one hand, considering the influence between indexes, the index benefit evaluation of the target structured query statement according to the candidate index can improve the accuracy of obtaining the recommended index; on the other hand, the recommended index is constructed according to the target candidate index in the target candidate index set determined according to the target structured query statement, which can improve the construction efficiency of the recommended index.
[0109] The functions of each module can be found in the target index recommendation method embodiment, which will not be described in detail here.
[0110] See also Figure 8 , Figure 8 The electronic device 80 includes a memory 81 and a processor 82, and the processor 82 is used to execute program instructions stored in the memory 81 to implement the steps in any of the above target index recommendation method embodiments.
[0111] In a specific implementation scenario, the electronic device 80 may include, but is not limited to: a microcomputer, a server, and in addition, the electronic device 80 may also include mobile devices such as a laptop computer and a tablet computer, which are not limited here.
[0112] Specifically, the processor 82 is used to control itself and the memory 81 to implement the steps in any of the above-mentioned target index recommendation method embodiments. The processor 82 can also be called a CPU (Central Processing Unit). The processor 82 may be an integrated circuit chip with signal processing capabilities. The processor 82 can also be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA) or other programmable logic devices, discrete gates or transistor logic devices, discrete hardware components. The general-purpose processor can be a microprocessor or the processor can also be any conventional processor, etc. In addition, the processor 82 can be implemented by an integrated circuit chip.
[0113] In the above scheme, the target index recommendation method of the embodiment of the present application obtains a target structured query statement set, and the target structured query statement set includes a target structured query statement; a target candidate index set is determined according to each target structured query statement in the target structured query statement set; and an index benefit evaluation process is performed on the target structured query statement in the target structured query statement set according to the candidate index in the target candidate index set to obtain a recommended index set. On the one hand, considering the impact between indexes, the index benefit evaluation of the target structured query statement is performed according to the candidate index, which can improve the accuracy of obtaining the recommended index; on the other hand, the recommended index is constructed according to the target candidate index in the target candidate index set determined according to the target structured query statement, which can improve the efficiency of constructing the recommended index.
[0114] See also Fig. 9 , Fig. 9The computer-readable storage medium 90 stores program instructions 91 that can be executed by a processor, and the program instructions 91 are used to implement the steps in any of the above target index recommendation method embodiments.
[0115] In the above scheme, the target index recommendation method of the embodiment of the present application obtains a target structured query statement set, and the target structured query statement set includes a target structured query statement; determines a target candidate index set according to each target structured query statement in the target structured query statement set; performs index benefit evaluation processing on the target structured query statement in the target structured query statement set according to the candidate index in the target candidate index set to obtain a recommended index set. On the one hand, considering the impact between indexes, the index benefit evaluation of the target structured query statement according to the candidate index can improve the accuracy of obtaining the recommended index; on the other hand, the recommended index is constructed according to the target candidate index in the target candidate index set determined by the target structured query statement, which can improve the construction efficiency of the recommended index.
[0116] In some embodiments, the functions or modules included in the device provided by the embodiments of the present disclosure can be used to execute the method described in the above method embodiments. The specific implementation can refer to the description of the above method embodiments, and for the sake of brevity, it will not be repeated here.
[0117] The above description of various embodiments tends to emphasize the differences between the various embodiments. The same or similar aspects can be referenced to each other, and for the sake of brevity, they will not be repeated herein.
[0118] In the several embodiments provided in the present application, it should be understood that the disclosed methods and devices can be implemented in other ways. For example, the device implementation described above is only schematic. For example, the division of modules or units is only a logical function division. There may be other division methods in actual implementation, such as units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, and the indirect coupling or communication connection of devices or units can be electrical, mechanical or other forms.
[0119] In addition, each functional unit in each embodiment of the present application can be integrated into a processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The above-mentioned integrated unit can be implemented in the form of hardware or in the form of a software functional unit. If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application is essentially or part of the contribution to the prior art or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) or a processor (processor) to perform all or part of the steps of each implementation method of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk and other media that can store program code.
[0120] If the technical solution of this application involves personal information, the product using the technical solution of this application has clearly informed the personal information processing rules and obtained the individual's voluntary consent before processing the personal information. If the technical solution of this application involves sensitive personal information, the product using the technical solution of this application has obtained the individual's separate consent before processing the sensitive personal information, and at the same time meets the "explicit consent" requirement. For example, on personal information collection devices such as cameras, clear and prominent signs are set to inform that the personal information collection scope has been entered and personal information will be collected. If the individual voluntarily enters the collection scope, it is deemed that he or she agrees to the collection of his or her personal information; or on the device that processes personal information, the personal information processing rules are notified by obvious signs / information, and the individual's authorization is obtained through pop-up information or by asking the individual to upload his or her personal information; among them, the personal information processing rules may include information such as the personal information processor, the purpose of personal information processing, the processing method, and the type of personal information processed.
Claims
1. A target index recommendation method, It is characterized in that The method comprises: Acquire a target structured query statement set, wherein the target structured query statement set includes a target structured query statement; Determining a target candidate index set according to each target structured query statement in the target structured query statement set; The step of performing index benefit evaluation processing on the target structured query statement in the target structured query statement set according to the candidate indexes in the target candidate index set to obtain a recommended index set includes: Traversing the target candidate index set, creating a current virtual index set according to the current candidate index and the current optimized index in the obtained current optimized index set, wherein the current optimized index set is used to store the optimized candidate index; Based on the created current virtual index set, index benefit evaluation processing is performed on the target structured query statement set to obtain a benefit valuation corresponding to the current candidate index; The current optimized index set is updated based on the revenue valuation corresponding to each candidate index in the target candidate index set until an optimized index set that meets the requirements is obtained; Taking the optimized index set that meets the requirements as the recommended index set; The step of performing index benefit evaluation processing on the target structured query statement set based on the current virtual index set to obtain a benefit valuation corresponding to the current candidate index includes: Performing index benefit evaluation processing on the target structured query statement set to obtain a first total cost value; Performing virtual index benefit evaluation processing on the target structured query statement in the target structured query statement set through the virtual index in the current virtual index set to obtain a second total cost value; The difference between the second total cost value and the first total cost value is used as the revenue estimation corresponding to the current candidate index.
2. The target index recommendation method according to claim 1, It is characterized in that The step of obtaining a target structured query statement set includes: Obtain an initial structured query statement set; Performing template processing on each initial structured query statement in the initial structured query statement set to obtain statement template information corresponding to each initial structured query statement; Each initial structured query statement is optimized and compressed according to the statement template information corresponding to each initial structured query statement to obtain the target structured query statement set.
3. The target index recommendation method according to claim 2, It is characterized in that The statement template information includes a statement template, and the step of optimizing and compressing each initial structured query statement according to the statement template information corresponding to each initial structured query statement to obtain the target structured query statement set includes: Acquire a current optimized data set, wherein the optimized and compressed initial structured query statement stored in the current optimized data set is determined based on a historical traversal, and the historical traversal time is earlier than the current traversal time; Traversing the initial structured query statement set; In response to the fact that there is a statement template of the optimized and compressed initial structured query statement in the current optimized data set that is the same as the statement template corresponding to the current initial structured query statement, the number of statements of the initial structured query statement belonging to the statement template is accumulated; Continue to traverse the next initialization structured query statement until the initial structured query statement set is traversed, and determine the target structured query statement set based on the current optimization data set.
4. The target index recommendation method according to claim 3, It is characterized in that The method further comprises: In response to the fact that there is no sentence template of the optimized and compressed initial structured query statement in the current optimized data set that is identical to the sentence template corresponding to the current initial structured query statement, optimizing and compressing the sentence according to the similarity between the current initial structured query statement and each optimized and compressed initial structured query statement in the current optimized data set; Until the initial structured query statement set is traversed, the current optimized data set is used as the target structured query statement set.
5. The target index recommendation method according to claim 1, It is characterized in that The step of updating the optimized index set based on the revenue valuation corresponding to each candidate index in the target candidate index set until an optimized index set that meets the requirements is obtained includes: Determine a target historical candidate index from each historical candidate index according to the revenue valuation corresponding to each historical candidate index when traversing the previous candidate index set; Selecting a currently traversed target candidate index from the target candidate index set according to the revenue valuation corresponding to each candidate index in the target candidate index set; In response to the revenue valuation of the target historical candidate index being less than or equal to the revenue valuation of the target candidate index, the target candidate index is added to the current optimized index set, and the target candidate index is deleted from the target candidate index set, and the next candidate index is traversed continuously, and the target candidate index set in which the target candidate index is deleted is used for the next round of traversal; In response to the profit valuation of the target historical candidate index being greater than the profit valuation of the target candidate index, whether the current optimized index set meets the requirements is determined based on the number of indexes in the current optimized index set and / or the profit valuation of the target candidate index, until the optimized index set that meets the requirements is obtained.
6. The target index recommendation method according to claim 1, It is characterized in that The step of determining a target candidate index set according to each target structured query statement in the target structured query statement set includes: Performing syntax analysis on each of the target structured query statements to obtain syntax tree data of the target structured query statement; Analyzing and processing the syntax tree data of the target structured query statement to obtain an information column of the target structured query statement; Generate a candidate index based on the information column of the target structured query statement; In response to the generated candidate index not existing in the database system and not existing in the acquired current candidate index set, the generated candidate index is added to the current candidate index set to obtain the target candidate index set.
7. An electronic device, It is characterized in that It comprises a memory and a processor, wherein the processor is used to execute program instructions stored in the memory to implement the target index recommendation method according to any one of claims 1 to 6.
8. A computer-readable storage medium having program instructions stored thereon, It is characterized in that When the program instructions are executed by a processor, the target index recommendation method according to any one of claims 1 to 6 is implemented.
Citation Information
Patent Citations
Index recommendation method and device, electronic equipment and storage medium
CN110807041A
Query data processing method and device, electronic equipment and computer storage medium
CN111666279A
Index recommendation method and device, electronic equipment and storage medium
CN116775691A
Index recommendation method and device, equipment, storage medium and program product
CN118152406A
System and method of query transformation
US20050015369A1
