Database index suggestion processing method, device, medium and electronic device
By capturing and utilizing indexable information of preset query clauses during the database query optimization phase, creating optimized index suggestions and evaluating performance benefits, the problem of index creation in the existing technology relying on manual and limited scope of suggestions is achieved, and a richer Indexing recommendations and more efficient database performance optimization.
Patent Information
- Application Number
- CN202011001540.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2020-09-22
- Publication Date
- 2024-12-06
- Estimated Expiration
- 2040-09-22
AI Technical Summary
The creation of existing database indexes relies on manual operations, with limited scope of recommendations, and it is difficult to quantify performance benefits, resulting in limited improvement in database performance.
During the SQL statement execution query optimization phase, indexable information of the preset query clause is captured, optimized index suggestions are created based on this, and performance is improved through hypothetical index evaluation.
Expand the scope of index suggestions, provide quantitative performance improvement rate of return, save manual analysis time and resources, and improve database performance.
Smart Images

Figure CN112162983B_ABST
Abstract
Description
Technical Field
[0001] The embodiments of the present disclosure relate to the field of database technology, and in particular to a database index suggestion processing method, a database index suggestion processing device, a computer-readable storage medium and an electronic device for implementing the database index suggestion processing method. Background Art
[0002] Before executing SQL statements, the database generally goes through lexical analysis, syntax analysis, semantic analysis, query rewriting, query optimization, and execution plan generation and execution. This allows SQL statements to be executed more efficiently, saving database resources and improving database performance.
[0003] Creating a reasonable index is one of the key factors to improve database performance. An index is a structure that sorts the values of one or more columns in a database table. Using an index, you can quickly access specific information in a database table.
[0004] However, currently, database indexes are generally created manually, which is a test of the technical ability of database administrators. Therefore, some databases provide index suggestions, but the scope of the suggestions is still limited, resulting in limited improvement in database performance, so database performance needs to be further improved. In addition, it is currently difficult or even impossible to effectively evaluate how much performance benefits the created index suggestions can bring to the SQL statements to be optimized, making it difficult to quantify and analyze the impact of the created index suggestions on database performance. Summary of the invention
[0005] In order to solve the above technical problem or at least partially solve the above technical problem, the present disclosure provides a.
[0006] In a first aspect, an embodiment of the present disclosure provides a method for processing a database index suggestion, including:
[0007] When the SQL statement to be optimized is executed to the query optimization stage to optimize the preset query clause, capturing and recording the indexable information corresponding to the preset query clause;
[0008] Based on the indexable information, create an optimized index suggestion for the SQL statement to be optimized;
[0009] A hypothetical index is created based on the optimized index suggestion, and a performance improvement rate of return of the SQL statement to be optimized is determined based on the hypothetical index.
[0010] In some embodiments of the present disclosure, the preset query clause includes one or more of a maximum value clause, a sorting clause, a filtering condition, and a connection condition;
[0011] When the SQL statement to be optimized is executed to the query optimization stage to optimize the preset query clause, capturing and recording the indexable information corresponding to the preset query clause includes:
[0012] When the preset query clause uses an index, the optimizer captures the most valuable indexable information, sorting indexable information, filtering indexable information, and connection indexable information corresponding to the record.
[0013] In some embodiments of the present disclosure, the step of creating an optimized index suggestion for the SQL statement to be optimized based on the indexable information includes:
[0014] For the most valuable indexable information and the sorted indexable information, the indexing system of the database creates an optimized index suggestion statement based on the extracted table name information and column name information involved in the most valuable clause and the sorted clause;
[0015] For the filtered indexable information and the connected indexable information, a query is performed to determine whether the index system of the database supports operators related to the filtering conditions and the connecting conditions; if supported, the index system creates an optimized index suggestion statement based on the extracted table name information and column name information involved in the filtering conditions and the connecting conditions.
[0016] In some embodiments of the present disclosure, the index system of the database supports one or more of B-tree index, B+ index, and hash index; the maximum value clause includes a MAX clause / MIN clause.
[0017] In some embodiments of the present disclosure, creating a hypothetical index based on the optimized index suggestion includes:
[0018] Create a system assumption index table in memory;
[0019] The meta information of the assumed index is stored in the system assumed index table, and the meta information includes one or more of the index name, index type, table object identifier, number of index columns, index column name, index column type, and index operator.
[0020] In some embodiments of the present disclosure, determining the performance improvement rate of the SQL statement to be optimized based on the assumed index includes:
[0021] Estimating a first total cost of the original execution plan of the SQL statement to be optimized;
[0022] Configuring the hypothetical index in the original execution plan to form an optimized execution plan;
[0023] A second total cost of the optimized execution plan is estimated, and the rate of return is determined based on the first total cost and the second total cost.
[0024] In some embodiments of the present disclosure, determining the rate of return based on the first total cost and the second total cost includes:
[0025] determining a difference between the first total cost and the second total cost;
[0026] The percentage value obtained by dividing the difference by the first total cost is used as the rate of return.
[0027] In some embodiments of the present disclosure, configuring the hypothetical index in the original execution plan to form an optimized execution plan includes:
[0028] Obtaining meta information of the assumed index; the meta information includes one or more of an index name, an index type, a table object identifier, the number of index columns, an index column name, an index column type, and an index operator;
[0029] When the optimizer constructs an index list for a table involved in an SQL statement to be optimized, the meta information of the assumed index is filled into the candidate index list.
[0030] In some embodiments of the present disclosure, it further includes:
[0031] Before estimating the second total cost of the optimized execution plan, obtaining the number of index tuples, the total number of index pages, and the hierarchical depth of the assumed index;
[0032] The second total cost is estimated based on the number of index tuples, the total number of index pages, and the hierarchical depth of the index.
[0033] In a second aspect, an embodiment of the present disclosure provides a database index suggestion processing device, including:
[0034] A capture and record module, used to capture and record indexable information corresponding to the preset query clause when the SQL statement to be optimized is executed to optimize the preset query clause in the query optimization stage;
[0035] An index suggestion creation module, used to create an optimized index suggestion for the SQL statement to be optimized based on the indexable information;
[0036] The benefit calculation module is used to create a hypothetical index based on the optimization index suggestion, and determine the performance improvement yield of the SQL statement to be optimized based on the hypothetical index.
[0037] In a third aspect, an embodiment of the present disclosure provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the database index suggestion processing method described in any of the above embodiments.
[0038] In a fourth aspect, an embodiment of the present disclosure provides an electronic device, including:
[0039] Processor; and
[0040] A memory, configured to store executable instructions of the processor;
[0041] The processor is configured to execute the steps of the database index suggestion processing method described in any of the above embodiments by executing the executable instructions.
[0042] Compared with the prior art, the technical solution provided by the embodiments of the present disclosure has the following advantages:
[0043] In the disclosed embodiment, when the SQL statement to be optimized is executed to the query optimization stage to optimize the preset query clause, the indexable information corresponding to the preset query clause is captured and recorded, and then an optimized index suggestion is created for the SQL statement to be optimized based on the indexable information; finally, a hypothetical index can be created based on the optimized index suggestion, and the performance improvement rate of return of the SQL statement to be optimized is determined based on the hypothetical index. In this way, the scheme of this embodiment can create different optimized index suggestions based on different preset query clauses, the range of index suggestions is wider, and a quantitative performance improvement rate of return can be calculated, so that the content of the index suggestion is richer, which is convenient for database administrators to refer to for tuning database performance, and by introducing hypothetical indexes, time and resources are saved when creating index suggestions and calculating rates of return, which further improves the performance of the database as a whole to a certain extent, while saving time for manual analysis and improving the efficiency of index suggestion optimization processing. BRIEF DESCRIPTION OF THE DRAWINGS
[0044] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present disclosure and, together with the description, serve to explain the principles of the present disclosure.
[0045] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.
[0046] Figure 1 A flowchart of a method for processing a database index suggestion according to an embodiment of the present disclosure;
[0047] Figure 2 A flowchart of a method for processing a database index suggestion according to an embodiment of the present disclosure;
[0048] Figure 3 A schematic diagram of recording data for a database index suggestion process according to an embodiment of the present disclosure;
[0049] Figure 4 A schematic diagram of a database index suggestion processing device according to an embodiment of the present disclosure;
[0050] Figure 5 A schematic diagram of an electronic device for implementing a database index suggestion processing method according to an embodiment of the present disclosure. DETAILED DESCRIPTION
[0051] In order to more clearly understand the above-mentioned objectives, features and advantages of the present disclosure, the scheme of the present disclosure will be further described below. It should be noted that the embodiments of the present disclosure and the features in the embodiments can be combined with each other without conflict.
[0052] In the following description, many specific details are set forth to facilitate a full understanding of the present disclosure, but the present disclosure may also be implemented in other ways different from those described herein; it is obvious that the embodiments in the specification are only part of the embodiments of the present disclosure, rather than all of the embodiments.
[0053] First, some terms involved in the embodiments of the present disclosure are explained.
[0054] Index suggestions: For a given database and Structured Query Language (SQL) statement, DDL statements for creating indexes are given, which usually makes the original SQL statement execute faster.
[0055] DDL: The abbreviation of Data Definition Language, which is a language used to define and manage all objects in a SQL database.
[0056] Figure 1 The flowchart of a method for processing a database index suggestion is shown in an embodiment of the present disclosure. The method for processing a database index suggestion may include the following steps:
[0057] Step S101: when the SQL statement to be optimized is executed to the query optimization stage to optimize the preset query clause, the indexable information corresponding to the preset query clause is captured and recorded.
[0058] Step S102: creating an optimized index suggestion for the SQL statement to be optimized based on the indexable information.
[0059] Step S103: creating a hypothetical index based on the optimized index suggestion, and determining the performance improvement rate of the SQL statement to be optimized based on the hypothetical index.
[0060] The above-mentioned database index suggestion processing method of this embodiment can create different optimized index suggestions based on different preset query clauses. The scope of index suggestions is wide, and a quantitative performance improvement rate of return can be calculated, so that the content of index suggestions is richer, which is convenient for database administrators to refer to for tuning database performance. By introducing hypothetical indexes, time and resources are saved when creating index suggestions and calculating rates of return. Overall, the performance of the database is further improved to a certain extent, while saving time for manual analysis and improving the efficiency of index suggestion optimization processing.
[0061] Specifically, in step S101, when the SQL statement to be optimized is executed to the query optimization stage to optimize the preset query clause, the indexable information corresponding to the preset query clause is captured and recorded.
[0062] SQL statements in a database generally go through several stages, including lexical analysis, syntax analysis, semantic analysis, query rewriting, query optimization, and execution plan generation and execution. For most databases, the query optimization stage is the stage at which the index is determined to be applicable to the SQL statement to be optimized. Therefore, for index recommendations, the time to capture indexable information is also during the query optimization stage. The indexable information corresponding to a preset query clause indicates that the database supports the use of indexes under the preset query clause.
[0063] Exemplarily, in some embodiments of the present disclosure, the preset query clause may include but is not limited to one or more of a maximum clause, an order clause, a filter condition, and a join condition. The maximum clause may include a MAX clause / MIN clause. The join condition is a connection operator between tables. In this embodiment, after the SQL statement to be optimized is executed to the query optimization stage to optimize the preset query clause, when the preset query clause attempts to use an index, the optimizer can capture the maximum indexable information, order indexable information, filter indexable information, and join indexable information corresponding to the record. Exemplarily, the captured recorded information can be stored in the memory structure sys_recommond_condition table, and the recorded information can include the query ID (QUERYID), suggestion type, suggested column name, suggested column ID, etc. related to the SQL statement to be optimized. Reference Figure 3 As shown, three SQL statements to be optimized and the corresponding indexable information of the captured records are exemplified, that is, the indexable situation, wherein the first SQL statement involves filtering (FILTER) indexable information, the second SQL statement involves MIN / MAX indexable information, and the third SQL statement involves sorting (ORDER) indexable information.
[0064] Specifically, after entering the optimizer, according to the original normal index processing flow logic optimization, the key steps may include pulling up subqueries, condition simplification, outer join elimination, selection operation pushdown, etc., which can refer to the existing technology and will not be repeated here. Then check whether the clause of the current SQL statement contains a MIN clause / MAX clause, which can specifically meet the following conditions: the current clause is the simplest SFW query, that is, the target column only contains MIN or MAX aggregation, that is, the clause does not contain group by, order by, multi-table join, subquery, window function, groupping set, cte, etc. When this condition is met, the MIN / MAX indexable situation is recorded in the sys_recommond_condition table. You can also check the order by clause (i.e., filter clause) of the current clause. If the current clause is a single-table query and there is no group by clause, the corresponding filter (FILTER) indexable situation is recorded in the sys_recommond_condition table. You can also process physical optimization according to the original process. When selecting a scanning algorithm for a table, record the where condition and join condition on the table in the sys_recommond_condition table. This will end the indexable information capture process and get the following Figure 3 The indexable information of the record shown may be the indexable situation.
[0065] In this embodiment, for the MIN clause / MAX clause, the MIN / MAX indexable information can be recorded in the original processing logic for determining whether MIN / MAX can use the index. For the sort clause, the information of the sort column can be extracted for the order statement to record the sort indexable information. The situation where the order statement contains multiple table columns is not supported and can be eliminated. For the filter conditions and join conditions, after all the original logic optimizations are completed, the conditions on the table are extracted to record the filter indexable information and the join indexable information. If a preset query clause contains conditions for multiple tables, one condition record is recorded for each table.
[0066] In step S102, based on the indexable information, an optimized index suggestion is created for the SQL statement to be optimized.
[0067] Exemplarily, in some embodiments of the present disclosure, for the most valuable indexable information and the sorted indexable information, the indexing system of the database creates an optimized index suggestion statement based on the extracted table name information and column name information involved in the most valuable clause and the sorted clause.
[0068] Exemplarily, the index system of the database may support one or more of B-tree index, B+ index, and hash index, but is not limited thereto. In this embodiment, support for B-tree index is taken as an example. Specifically, for MIN / MAX indexable information and sorted indexable information, B-tree (BTree) index may be used for optimization. For example, the table name and column name information of the record may be directly obtained and spliced with the index suggestion statement to obtain the optimized index suggestion statement DDL.
[0069] For the filtered indexable information and the connected indexable information, a query is performed to determine whether the index system of the database supports operators related to the filtering conditions and the connecting conditions; if supported, the index system creates an optimized index suggestion statement based on the extracted table name information and column name information involved in the filtering conditions and the connecting conditions.
[0070] Specifically, for filtering indexable information and joining indexable information, it is necessary to query whether the database index system supports related operators, such as the ">" operator, which is supported by BTree index but not by HASH index. Therefore, in this embodiment, the BTree index can create an optimized index suggestion statement DDL based on the extracted table name information and column name information involved in the filtering condition and joining condition.
[0071] Combination Figure 3 As shown, in a specific embodiment, the above-mentioned creation of optimized index suggestions may include the following steps:
[0072] Step 1: Open the sys_recommond_condition table and query the indexability of each SQL statement to be optimized based on QueryID.
[0073] Step 2: If the indexable condition record is empty, end the current process; otherwise, traverse each indexable condition record in the sys_recommond_condition table.
[0074] Step 3: If the current SQL statement records the MIN / MAX situation: extract the table name and column name, and for each column involved, query whether the btree index supports the type of indexing the current column. If it does, splice out an optimized index suggestion statement such as "create indexidx_tbl_index_a on tbl_index using btree(a)".
[0075] Step 4: If the current record is sorted (ORDER): extract the table name and column name, and for each column involved, query whether the btree index supports the type of the current column. For columns that are not supported, directly discard them; for columns that are supported, uniformly splice out optimized index suggestion statements such as "create index idx_tbl_index_a on tbl_index using btree(a, b)".
[0076] Step 5: If the current record is a filter (FILTER) condition and / or a join condition: For each column involved, query whether all index systems of the database support the operator corresponding to the current condition. For columns that are not supported, directly discard them. If supported, continue to query whether the indexes supported by the current database support indexes of this type, such as the types corresponding to the FILTER condition and the join condition. If supported, obtain the table name and column name that meet the above two conditions, and uniformly splice them into an optimized index suggestion statement such as "create index idx_tbl_index_a on tbl_index using btree(a,b)".
[0077] Step 6: Record the above optimization index recommendation statements in the sys_recommond_index view. Figure 3 The INDEX_DDL column exemplarily shows the specific optimized index suggestion statements corresponding to the three SQL statements.
[0078] In step S103, a hypothetical index is created based on the optimized index suggestion, and a performance improvement rate of the SQL statement to be optimized is determined based on the hypothetical index.
[0079] In this embodiment, after providing the index optimization suggestion statement, the index yield rate can also be provided to more conveniently help the user make decisions to adjust the database performance.
[0080] Specifically, in one embodiment, creating a hypothetical index based on the optimized index suggestion in step S103 includes the following steps:
[0081] Step i) Create a system assumption index table in memory.
[0082] Step ii) stores meta information of the assumed index in the system assumed index table, wherein the meta information includes one or more of index name, index type, table object identifier, number of index columns, index column name, index column type, and index operator.
[0083] For example, a hypothetical index allows the optimizer to evaluate the execution plan using a hypothetical index without actually creating the index. Specifically, an additional memory structure sys_hypothetic_index table can be created to store the metadata of the hypothetical index, which can be encapsulated as a view for external display. The creation of a hypothetical index only stores metadata and does not generate data. The metadata is related to the above-mentioned optimized index suggestion statement, for example, it may include Figure 3 The INDEX_DDL column shows the index name, table object identifier (OID), number of index columns, index column name, index column type, index column type, index operator, and index type obtained by the optimized index suggestion statement.
[0084] Specifically, in one embodiment, combined with Figure 2 As shown in , the step of determining the performance improvement rate of the SQL statement to be optimized based on the assumed index in step S103 may specifically include the following steps:
[0085] Step S201: estimating a first total cost of the original execution plan of the SQL statement to be optimized.
[0086] Step S202: configuring the hypothetical index in the original execution plan to form an optimized execution plan.
[0087] Step S203: Estimate the second total cost of the optimized execution plan, and determine the rate of return based on the first total cost and the second total cost.
[0088] Among them, in some embodiments of the present disclosure, configuring the hypothetical index in the original execution plan to form an optimized execution plan may specifically include: obtaining meta-information of the hypothetical index; the meta-information includes one or more of an index name, an index type, a table object identifier, the number of index columns, an index column name, an index column type, and an index operator; when the optimizer builds an index list for a table involved in the SQL statement to be optimized, the meta-information of the hypothetical index is filled into a candidate index list.
[0089] Index creation often takes up a lot of time and consumes more resources, so hypothetical indexes are introduced in this embodiment. Hypothetical indexes allow the optimizer to use hypothetical indexes to evaluate the execution plan without actually creating the indexes. The specific method can be: create an additional memory structure sys_hypothetic_index table to save the metadata of the hypothetical index, which can be encapsulated as a view for external display. The creation of a hypothetical index only saves metadata and does not generate data. Meta information may include index name, table object identifier (OID, Object Identifier), number of index columns, index column name, index column type, index column type, index operator and index type, etc. For explain statements, when the optimizer builds an index list for the table, the metadata of the hypothetical index can also be filled into the candidate index list. In this way, the subsequent operations of the optimizer will treat the hypothetical index as a normal index.
[0090] The solution of this embodiment can save time and resources when creating index suggestions and calculating yields by introducing hypothetical indexes, thereby improving processing efficiency and reducing the impact on the database, thereby improving database performance.
[0091] In some embodiments of the present disclosure, the following steps may also be included: before estimating the second total cost of the optimized execution plan, obtaining the number of index tuples of the hypothetical index, the total number of index pages and the hierarchical depth of the index; estimating the second total cost based on the number of index tuples, the total number of index pages and the hierarchical depth of the index.
[0092] Exemplarily, after the optimizer treats the hypothetical index as a normal index, it can perform additional processing when making estimates. The processing method can be: estimate the number of tuples after the index based on the table data and the relevant selectivity function. Estimate the width of an index tuple based on the number of index columns and the data type of the index column, and then estimate the number of index tuples that a page can carry, and then estimate the total number of pages contained in the index and the hierarchical depth of the index. The specific processing process can refer to the prior art. The above information can be used to estimate the cost of using the hypothetical index as a normal index. After the above processing steps, an optimized execution plan using the hypothetical index can be obtained.
[0093] Then, the results of the original execution plan without the assumed index and the optimized execution plan using the assumed index can be formatted in JSON format, and finally the total cost of the two execution plans can be extracted to calculate the yield of the index.
[0094] Exemplarily, in some embodiments of the present disclosure, the determining the rate of return based on the first total cost before_cost and the second total cost after_cost may specifically include: determining the difference between the first total cost before_cost and the second total cost after_cost; dividing the difference by the first total cost before_cost to obtain a percentage value as the rate of return. That is, obtaining the SQL statement execution plan cost:before_cost before the optimization index suggestion is created, and obtaining the SQL statement execution plan cost:after_cost after the optimization index suggestion is created.
[0095] Among them, the rate of return = (before_cost-after_cost) / beforecost. Figure 3 The figure shows the yields of three SQL statements. The higher the yield, the better the performance improvement of the database system.
[0096] In a specific example, it is assumed that the index creation process may include the following steps:
[0097] Step 1: Use the query QueryID as input to start the creation of a hypothetical index through the function interface.
[0098] Step 2: Find the index creation statements of the SQL statements to be optimized, and parse each index creation statement according to the original index creation process.
[0099] Step 3: Store the parsed results in the sys_hypothetic_index table.
[0100] It is assumed that the index cost evaluation process may include the following steps:
[0101] Step 1: Obtain the execution plan of the SQL statement to be optimized through the explain statement
[0102] Step 2: Enter the optimizer logic optimization stage.
[0103] Step 3: At the MIN / MAX optimization position of the optimizer, check whether the current SQL statement has a hypothetical index created for MIN / MAX. If so, add the hypothetical index to the candidate index list.
[0104] Step 4: Enter the optimizer physical optimization stage.
[0105] When the optimizer selects an index algorithm for a table, it checks whether the current SQL statement has a hypothetical index created for sorting. If so, it adds the hypothetical index to the candidate index list.
[0106] Step 5: When evaluating the cost of a hypothetical index, introduce the cost evaluation algorithm for the hypothetical index. The number of index tuples is equal to the number of table tuples, and the index record width is the sum of the index column width and the tuple header. Then, the number of index tuples that an index page can contain, the total number of index pages, and the hierarchical depth of the index are calculated. With this information, the cost evaluation of the index can be the same as that of a normal index.
[0107] Step 6: Treat the hypothetical index as a normal index and evaluate the execution plan.
[0108] Step 7: Get the optimized execution plan with the hypothetical index added.
[0109] The yield assessment process can include the following steps:
[0110] Step 1: Use the explain statement to obtain the execution plan of the SQL statement to be optimized, extract the total cost estimated by the execution plan as the cost of the original execution plan, and name it before_cost.
[0111] Step 2: Create a hypothetical index based on the optimized index suggestion SQL statement.
[0112] Step 3: Use the explain statement to obtain the optimized execution plan of the SQL statement to be optimized, and extract the total cost estimated by the optimized execution plan as the cost of the optimized execution plan, named after_cost.
[0113] Step 4: Calculate the yield rate by the formula: yield rate = (before_cost-after_cost) / before_cost, and update it as follows: Figure 3 As shown in the sys_recommond_index view.
[0114] Step 5: End the benefit evaluation process.
[0115] It should be noted that, although the steps of the method in the present disclosure are described in a specific order in the accompanying drawings, this does not require or imply that the steps must be performed in this specific order, or that all the steps shown must be performed to achieve the desired results. Additionally or alternatively, some steps may be omitted, multiple steps may be combined into one step for execution, and / or one step may be decomposed into multiple steps for execution, etc. In addition, it is also easy to understand that these steps may be, for example, executed synchronously or asynchronously in multiple modules / processes / threads.
[0116] The disclosed embodiment also provides a database index suggestion processing device, such as Figure 4As shown, the database index suggestion processing device may include: a capture and record module 401, which is used to capture and record the indexable information corresponding to the preset query clause when the SQL statement to be optimized is executed to the query optimization stage to optimize the preset query clause. An index suggestion creation module 402, which is used to create an optimized index suggestion for the SQL statement to be optimized based on the indexable information. A benefit calculation module 403, which is used to create a hypothetical index based on the optimized index suggestion, and determine the performance improvement yield of the SQL statement to be optimized based on the hypothetical index.
[0117] The above-mentioned database index suggestion processing device of this embodiment can create different optimized index suggestions based on different preset query clauses. The scope of index suggestions is wide, and a quantitative performance improvement rate of return can be calculated, so that the content of index suggestions is richer, which is convenient for database administrators to refer to for tuning database performance. By introducing hypothetical indexes, time and resources are saved when creating index suggestions and calculating rates of return, which further improves the overall performance of the database to a certain extent, while saving time for manual analysis and improving the efficiency of index suggestion optimization processing.
[0118] In some embodiments of the present disclosure, the preset query clause may include one or more of a maximum value clause, a sorting clause, a filtering condition, and a connection condition. When the SQL statement to be optimized is executed to the query optimization stage to optimize the preset query clause, the capture and record module 401 captures and records the indexable information corresponding to the preset query clause, which may specifically include: when the preset query clause uses an index, the optimizer captures and records the corresponding maximum value indexable information, sorting indexable information, filtering indexable information, and connection indexable information.
[0119] In some embodiments of the present disclosure, the index suggestion creation module 402 creates an optimized index suggestion for the SQL statement to be optimized based on the indexable information, which may specifically include: for the most valuable indexable information and the sorting indexable information, the index system of the database creates an optimized index suggestion statement based on the extracted table name information and column name information involved in the most valuable clause and the sorting clause; for the filtering indexable information and the joining indexable information, query to determine whether the index system of the database supports operators related to the filtering conditions and the joining conditions; if supported, the index system creates an optimized index suggestion statement based on the extracted table name information and column name information involved in the filtering conditions and the joining conditions.
[0120] In some embodiments of the present disclosure, the index system of the database supports one or more of B-tree index, B+ index, and hash index; the maximum value clause includes a MAX clause / MIN clause.
[0121] In some embodiments of the present disclosure, the benefit calculation module 403 creates a hypothetical index based on the optimized index suggestion, and determines the performance improvement yield of the SQL statement to be optimized based on the hypothetical index, which may specifically include: estimating the first total cost of the original execution plan of the SQL statement to be optimized; configuring the hypothetical index in the original execution plan to form an optimized execution plan; estimating the second total cost of the optimized execution plan, and determining the yield based on the first total cost and the second total cost.
[0122] In some embodiments of the present disclosure, the profit calculation module 403 determines the rate of return based on the first total cost and the second total cost, which may specifically include: determining the difference between the first total cost and the second total cost; and dividing the difference by the first total cost to obtain a percentage value as the rate of return.
[0123] In some embodiments of the present disclosure, the benefit calculation module 403 configures the hypothetical index in the original execution plan to form an optimized execution plan, which may specifically include: obtaining metadata of the hypothetical index; the metadata includes one or more of the index name, index type, table object identifier, number of index columns, index column name, index column type, and index operator; when the optimizer builds an index list for the table involved in the SQL statement to be optimized, the metadata of the hypothetical index is filled into the candidate index list.
[0124] In some embodiments of the present disclosure, the benefit calculation module 403 can also be used to obtain the number of index tuples of the hypothetical index, the total number of index pages and the hierarchical depth of the index before estimating the second total cost of the optimized execution plan; and estimate the second total cost based on the number of index tuples, the total number of index pages and the hierarchical depth of the index.
[0125] Regarding the device in the above embodiment, the specific manner in which each module performs operations and the corresponding technical effects brought about have been described in detail in the embodiment of the method, and will not be elaborated here.
[0126] It should be noted that although several modules or units of the device for action execution are mentioned in the above detailed description, this division is not mandatory. In fact, according to the embodiment of the present disclosure, the features and functions of two or more modules or units described above can be concretized in one module or unit. Conversely, the features and functions of a module or unit described above can be further divided into multiple modules or units for concretization. The components displayed as modules or units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the disclosed scheme. Those of ordinary skill in the art can understand and implement it without paying creative work.
[0127] The present disclosure also provides a computer-readable storage medium on which a computer program is stored. When the program is executed by a processor, the steps of the database index suggestion processing method described in any of the above embodiments are implemented.
[0128] Exemplarily, the readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination thereof. More specific examples of readable storage media (a non-exhaustive list) include: an electrical connection with one or more wires, a portable disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof.
[0129] The computer readable storage medium may include a data signal propagated in a baseband or as part of a carrier wave, wherein a readable program code is carried. This propagated data signal may take a variety of forms, including but not limited to an electromagnetic signal, an optical signal, or any suitable combination of the above. The readable storage medium may also be any readable medium other than a readable storage medium, which may send, propagate, or transmit a program for use by an instruction execution system, an apparatus, or a device or used in combination with it. The program code contained on the readable storage medium may be transmitted with any appropriate medium, including but not limited to wireless, wired, optical cable, RF, etc., or any suitable combination of the above.
[0130] The present disclosure also provides an electronic device, including a processor and a memory, wherein the memory is used to store executable instructions of the processor, wherein the processor is configured to execute the steps of the database index suggestion processing method described in any of the above embodiments by executing the executable instructions.
[0131] Refer to the following Figure 5The electronic device 600 according to this embodiment of the present invention is described. Figure 5 The electronic device 600 shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present invention.
[0132] like Figure 5 As shown, the electronic device 600 is in the form of a general computing device. The components of the electronic device 600 may include, but are not limited to: at least one processing unit 610, at least one storage unit 620, a bus 630 connecting different system components (including the storage unit 620 and the processing unit 610), a display unit 640, etc.
[0133] The storage unit stores program codes, which can be executed by the processing unit 610, so that the processing unit 610 executes the steps of various exemplary embodiments of the present invention described in the above-mentioned database index suggestion processing method of this specification. For example, the processing unit 610 can execute the following steps: Figure 1 The steps of the method shown in .
[0134] The storage unit 620 may include a readable medium in the form of a volatile storage unit, such as a random access memory unit (RAM) 6201 and / or a cache memory unit 6202 , and may further include a read-only memory unit (ROM) 6203 .
[0135] The storage unit 620 may also include a program / utility 6204 having a set (at least one) of program modules 6205, such program modules 6205 including but not limited to: an operating system, one or more application programs, other program modules and program data, each of which or some combination may include the implementation of a network environment.
[0136] Bus 630 may represent one or more of several types of bus structures, including a memory unit bus or memory unit controller, a peripheral bus, an accelerated graphics port, a processing unit, or a local bus using any of a variety of bus architectures.
[0137] The electronic device 600 may also communicate with one or more external devices 700 (e.g., keyboards, pointing devices, Bluetooth devices, etc.), may also communicate with one or more devices that enable a user to interact with the electronic device 600, and / or communicate with any device that enables the electronic device 600 to communicate with one or more other computing devices (e.g., routers, modems, etc.). Such communication may be performed via an input / output (I / O) interface 650. Furthermore, the electronic device 600 may also communicate with one or more networks (e.g., a local area network (LAN), a wide area network (WAN), and / or a public network, such as the Internet) via a network adapter 660. The network adapter 660 may communicate with other modules of the electronic device 600 via a bus 630. It should be understood that, although not shown in the figure, other hardware and / or software modules may be used in conjunction with the electronic device 600, including but not limited to: microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data backup storage systems.
[0138] Through the description of the above implementation methods, it is easy for those skilled in the art to understand that the example implementation methods described here can be implemented by software or by combining software with necessary hardware. Therefore, the technical solution according to the implementation methods of the present disclosure can be embodied in the form of a software product, which can be stored in a non-volatile storage medium (which can be a CD-ROM, a USB flash drive, a mobile hard disk, etc.) or on a network, and includes a number of instructions to enable a computing device (which can be a personal computer, a server, or a network device, etc.) to execute the above-mentioned database index suggestion processing method according to the implementation methods of the present disclosure.
[0139] It should be noted that, in this article, relational terms such as "first" and "second" are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.
[0140] The above description is only a specific embodiment of the present disclosure, so that those skilled in the art can understand or implement the present disclosure. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present disclosure. Therefore, the present disclosure will not be limited to the embodiments described herein, but will conform to the widest scope consistent with the principles and novel features disclosed herein.
Claims
1. A method for processing database index suggestions, It is characterized in that include: When the SQL statement to be optimized is executed to the query optimization stage and the preset query clause is optimized using the index, the maximum value indexable information, sort indexable information, filter indexable information and connection indexable information corresponding to the preset query clause are captured and recorded by the optimizer, respectively, and the preset query clause includes one or more of the maximum value clause, sort clause, filter condition and connection condition; For the maximum value indexable information and the sort indexable information, the index system of the database creates an optimized index suggestion statement based on the extracted table name information and column name information involved in the maximum value clause and the sort clause; for the filter indexable information and the join indexable information, query to determine whether the index system of the database supports operators related to the filter condition and the join condition; If supported, the index system creates the optimized index suggestion statement based on the extracted table name information and column name information involved in the filter condition and the connection condition; A hypothetical index is created based on the optimized index suggestion, and a performance improvement rate of return of the SQL statement to be optimized is determined based on the hypothetical index.
2. The database index suggestion processing method according to claim 1, It is characterized in that The index system of the database supports one or more of B-tree index, B+ number index, and hash index; the maximum value clause includes a MAX clause / MIN clause.
3. The database index suggestion processing method according to claim 1 or 2, It is characterized in that The creating a hypothetical index based on the optimized index suggestion includes: Create a system assumption index table in memory; The meta information of the assumed index is stored in the system assumed index table, and the meta information includes one or more of the index name, index type, table object identifier, number of index columns, index column name, index column type, and index operator.
4. The database index suggestion processing method according to claim 3, It is characterized in that The determining the performance improvement rate of the SQL statement to be optimized based on the assumed index includes: Estimating a first total cost of the original execution plan of the SQL statement to be optimized; Configuring the hypothetical index in the original execution plan to form an optimized execution plan; A second total cost of the optimized execution plan is estimated, and the rate of return is determined based on the first total cost and the second total cost.
5. The database index suggestion processing method according to claim 4, It is characterized in that The determining the rate of return based on the first total cost and the second total cost comprises: determining a difference between the first total cost and the second total cost; The percentage value obtained by dividing the difference by the first total cost is used as the rate of return.
6. The database index suggestion processing method according to claim 4, It is characterized in that The configuring the hypothetical index into the original execution plan to form an optimized execution plan includes: Obtaining meta information of the assumed index; the meta information includes one or more of an index name, an index type, a table object identifier, the number of index columns, an index column name, an index column type, and an index operator; When the optimizer constructs an index list for a table involved in an SQL statement to be optimized, the meta information of the assumed index is filled into the candidate index list.
7. The database index suggestion processing method according to claim 6, It is characterized in that Also includes: Before estimating the second total cost of the optimized execution plan, obtaining the number of index tuples, the total number of index pages, and the hierarchical depth of the assumed index; The second total cost is estimated based on the number of index tuples, the total number of index pages, and the hierarchical depth of the index.
8. A database index suggestion processing device, It is characterized in that include: A capture and record module, used for capturing and recording the maximum value indexable information, sort indexable information, filter indexable information and connection indexable information corresponding to the preset query clause through the optimizer when the SQL statement to be optimized is executed to the query optimization stage to optimize the preset query clause using the index, wherein the preset query clause includes one or more of the maximum value clause, sort clause, filter condition and connection condition; An index suggestion creation module, configured to create an optimized index suggestion statement for the most value indexable information and the sort indexable information based on the extracted table name information and column name information involved in the most value clause and the sort clause by the index system of the database; and query and determine whether the index system of the database supports operators related to the filtering condition and the joining condition for the filtering indexable information and the joining indexable information; If supported, the index system creates the optimized index suggestion statement based on the extracted table name information and column name information involved in the filter condition and the connection condition; The benefit calculation module is used to create a hypothetical index based on the optimization index suggestion, and determine the performance improvement yield of the SQL statement to be optimized based on the hypothetical index.
9. A computer-readable storage medium having a computer program stored thereon, It is characterized in that When the program is executed by a processor, the steps of the database index suggestion processing method described in any one of claims 1 to 7 are implemented.
10. An electronic device, It is characterized in that include: processor; as well as A memory, configured to store executable instructions of the processor; The processor is configured to execute the steps of the database index suggestion processing method according to any one of claims 1 to 7 by executing the executable instructions.
