CN112162983A

CN112162983A - Database index suggestion processing method, apparatus, medium and electronic equipment

Info

Publication number: CN112162983A
Application number: CN202011001540.3A
Authority: CN
Inventors: 尹强
Original assignee: Beijing Kingbase Information Technologies Co Ltd
Current assignee: Beijing Kingbase Information Technologies Co Ltd
Priority date: 2020-09-22
Filing date: 2020-09-22
Publication date: 2021-01-01
Anticipated expiration: 2040-09-22
Also published as: CN112162983B

Abstract

The present disclosure relates to a database index suggestion processing method, apparatus, medium, and electronic device, wherein the method comprises: when the SQL sentence to be optimized is executed to the query optimization stage, optimizing the preset query clause, capturing and recording indexable information corresponding to the preset query clause; establishing an optimization index suggestion for the SQL statement to be optimized based on the indexable information; and creating a hypothesis index based on the optimization index suggestion, and determining the performance improvement yield of the SQL statement to be optimized based on the hypothesis index. According to the scheme, different optimization index suggestions can be created based on different preset query clauses, the range of the index suggestions is wide, the quantitative performance improvement yield can be calculated, the content of the index suggestions is richer, and by introducing the hypothesis indexes, time and resources are saved when the index suggestions are created and the yield is calculated, so that the performance of the database is integrally improved, meanwhile, the time of manual analysis is saved, and the optimization processing efficiency of the index suggestions is improved.

Description

Database index suggestion processing method, device, medium and electronic equipment

Technical Field

The embodiment of the disclosure relates to the technical field of databases, 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

Before executing a structured query language SQL statement, a database typically undergoes lexical analysis, syntax analysis, semantic analysis, query rewriting, and query optimization, as well as generating an execution plan and executing several stages. Therefore, the SQL sentences can be executed more efficiently, and the database resources are saved so as to improve the performance of the database.

Creating a reasonable index is one of the key factors that improve database performance. An index is a structure that sorts one or more columns of values in a database table, and the index can be used to quickly access specific information in the database table.

However, the database index is generally created manually at present, and the technical ability of a database administrator is tested. Therefore, some databases provide index suggestions, but the range of suggestions is still limited, resulting in a limited degree of improvement in database performance, and thus further improvement in database performance is desired. In addition, at present, it is difficult or even impossible to effectively evaluate how much performance benefit can be brought to the SQL statement to be optimized by the created index suggestion, so that it is difficult to quantitatively analyze the influence of the created index suggestion on the database performance.

Disclosure of Invention

To solve the above technical problem or at least partially solve the above technical problem, the present disclosure provides a method.

In a first aspect, an embodiment of the present disclosure provides a database index suggestion processing method, including:

when the SQL sentence to be optimized is executed to the query optimization stage, optimizing a preset query clause, and capturing and recording indexable information corresponding to the preset query clause;

based on the indexable information, establishing an optimized index suggestion for the SQL statement to be optimized;

and creating a hypothesis index based on the optimization index suggestion, and determining the performance improvement yield of the SQL statement to be optimized based on the hypothesis index.

In some embodiments of the present disclosure, the preset query clause includes one or more of a most valued clause, a ranking clause, a filtering condition, and a connection condition;

when the SQL sentence 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 the method comprises the following steps:

when the preset query clause uses the index, respectively capturing and recording the corresponding most value indexable information, sequencing indexable information, filtering indexable information and connecting indexable information through an optimizer.

In some embodiments of the present disclosure, the creating an optimization index suggestion for the SQL statement to be optimized based on the indexable information includes:

for the most valued indexable information and the sequencing indexable information, the indexing system of the database creates an optimized index suggestion sentence based on the table name information and the column name information related to the extracted most valued clause and sequencing clause;

for the filtering indexable information and the connection indexable information, querying to determine whether an index system of the database supports operators related to the filtering conditions and the connection conditions; and if so, creating an optimized index suggestion sentence by the index system based on the table name information and the column name information related to the extracted filtering condition and connecting condition.

In some embodiments of the present disclosure, the indexing system of the database supports one or more of B-tree indexing, B + number indexing, hash indexing; the most valued clause includes a MAX clause/MIN clause.

In some embodiments of the present disclosure, the creating a hypothesis index based on the optimized index suggestion includes:

creating a system hypothesis index table in a memory;

storing meta information of the hypothetical index in the system hypothetical index table, the meta information including one or more of an index name, an index type, a table object identifier, a number of index columns, an index column name, an index column type, and an index operator.

In some embodiments of the present disclosure, the determining a performance improvement yield of the SQL statement to be optimized based on the hypothesis index includes:

estimating a first total cost of the original execution plan of the SQL statement to be optimized;

configuring the hypothesis indexes in the original execution plan to form an optimized execution plan;

estimating a second total cost of the optimized execution plan, and determining the profitability based on the first total cost and the second total cost.

In some embodiments of the present disclosure, the determining the rate of return based on the first total cost and the second total cost includes:

determining a difference value of the first total cost minus a second total cost;

and dividing the difference value by the first total cost to obtain a percentage value as the yield.

In some embodiments of the disclosure, configuring the hypothesis index into the original execution plan to form an optimized execution plan includes:

acquiring meta information of the hypothesis index; the meta information comprises one or more of index name, index type, table object identifier, index column number, index column name, index column type and index operator;

and when the optimizer constructs an index list for the table related to the SQL statement to be optimized, filling the meta information of the hypothetical index into the candidate index list.

In some embodiments of the present disclosure, further comprising:

before estimating a second total cost of the optimized execution plan, acquiring the number of index tuples of the hypothetical index, the total number of pages of the index and the hierarchical depth of the index;

estimating the second total cost based on the number of index tuples, the total number of pages indexed, and the hierarchical depth of the index.

In a second aspect, an embodiment of the present disclosure provides a database index suggestion processing apparatus, including:

the acquisition and recording module is used for acquiring and recording indexable information corresponding to a preset query clause when the SQL sentence to be optimized is executed to the query optimization stage and the preset query clause is optimized;

the index suggestion creating module is used for creating an optimized index suggestion for the SQL statement to be optimized based on the indexable information;

and the profit calculation module is used for creating a hypothesis index based on the optimization index suggestion and determining the performance improvement profit rate of the SQL statement to be optimized based on the hypothesis index.

In a third aspect, the present disclosure provides a computer-readable storage medium, on which a computer program is stored, where the computer program, when executed by a processor, implements the steps of the database index suggestion processing method according to any one of the embodiments.

In a fourth aspect, an embodiment of the present disclosure provides an electronic device, including:

a processor; and

a memory for storing executable instructions of the processor;

wherein the processor is configured to perform the steps of the database index suggestion processing method of any of the above embodiments via execution of the executable instructions.

Compared with the prior art, the technical scheme provided by the embodiment of the disclosure has the following advantages:

in the embodiment of the disclosure, when the SQL sentence 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 sentence to be optimized based on the indexable information; finally, a hypothesis index can be created based on the optimization index suggestion, and the performance improvement yield of the SQL statement to be optimized is determined based on the hypothesis index. Therefore, the scheme of the embodiment can create different optimization index suggestions based on different preset query clauses, the range of the index suggestions is wide, the quantitative performance improvement yield can be calculated, the content of the index suggestions is richer, database managers can conveniently refer to the index suggestions to adjust and optimize the performance of the database, the assumed indexes are introduced, time and resources are saved when the index suggestions are created and the yield is calculated, the performance of the database is further improved to a certain degree integrally, the time of manual analysis is saved at the same time, and the optimization processing efficiency of the index suggestions is improved.

Drawings

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 disclosure.

In order to more clearly illustrate the embodiments or technical solutions in the prior art of the present disclosure, the drawings used in the description of the embodiments or prior art will be briefly described below, and it is obvious for those skilled in the art that other drawings can be obtained according to the drawings without inventive exercise.

FIG. 1 is a flow chart of a database index suggestion processing method according to an embodiment of the present disclosure;

FIG. 2 is a flow chart of a database index suggestion processing method according to an embodiment of the present disclosure;

FIG. 3 is a schematic diagram of a database indexing suggestion process record data according to an embodiment of the present disclosure;

FIG. 4 is a schematic diagram of a database index suggestion processing apparatus according to an embodiment of the disclosure;

fig. 5 is 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

In order that the above objects, features and advantages of the present disclosure may be more clearly understood, aspects of the present disclosure will be further described below. It should be noted that the embodiments and features of the embodiments of the present disclosure may be combined with each other without conflict.

In the following description, numerous specific details are set forth in order to provide a thorough understanding of the present disclosure, but the present disclosure may be practiced in other ways than those described herein; it is to be understood that the embodiments disclosed in the specification are only a few embodiments of the present disclosure, and not all embodiments.

Some terms referred to in the embodiments of the present disclosure are explained first.

And index suggestion: for a given database and Structured Query Language (SQL) statements, the index suggestion DDL statement for creating the index is given, usually making the original SQL statement execute faster.

DDL: an abbreviation of Data Definition Language, Language for defining and managing all objects in the SQL database.

Fig. 1 is a flowchart of a database index suggestion processing method according to an embodiment of the present disclosure, where the database index suggestion processing method may include the following steps:

step S101: when the SQL sentence to be optimized is executed to the query optimization stage, the preset query clause is optimized, and the indexable information corresponding to the preset query clause is captured and recorded.

Step S102: and creating an optimization index suggestion for the SQL statement to be optimized based on the indexable information.

Step S103: and creating a hypothesis index based on the optimization index suggestion, and determining the performance improvement yield of the SQL statement to be optimized based on the hypothesis index.

The database index suggestion processing method of the embodiment can create different optimized index suggestions based on different preset query clauses, the range of the index suggestions is wide, the quantized performance improvement yield can be calculated, the content of the index suggestions is richer, the database performance can be adjusted and optimized by being convenient for a database administrator to refer to, by introducing the hypothesis indexes, time and resources are saved when the index suggestions are created and the yield is calculated, the performance of the database is further improved to a certain degree integrally, meanwhile, the time of manual analysis is saved, and the optimization processing efficiency of the index suggestions is improved.

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.

SQL statements in a database typically undergo lexical analysis, syntax analysis, semantic analysis, query rewriting and query optimization, as well as generating execution plans and executing several phases. For most databases, determining whether the index can be applied to the current SQL statement to be optimized is in the query optimization stage. The opportunity to capture indexable information for index suggestions is also in the query optimization stage. The indexable information corresponding to the preset query clause indicates that the database supports the usable index under the preset query clause.

For example, in some embodiments of the present disclosure, the preset query clause may include, but is not limited to, one or more of a most valued clause, an ordering (order) clause, a filtering (filter) condition, and a join (join) condition. The most valued clause may include a MAX clause/MIN clause. A join condition is a join operator from table to table. 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 tries to use the index, the optimizer may capture and record the corresponding maximum indexable information, sorted indexable information, filtered indexable information, and connected indexable information, respectively. For example, the information of the capture record may be stored in the memory structure sys _ consistent _ condition table, and the recorded information may include query ID (queryid), suggestion type, suggestion column name, suggestion column ID, and the like related to the SQL statement to be optimized. Referring to fig. 3, three recorded SQL statements to be optimized and corresponding indexable information, i.e., indexable information, are captured, wherein the first SQL statement relates to FILTER (FILTER) indexable information, the second SQL statement relates to MIN/MAX indexable information, and the second SQL statement relates to ORDER (ORDER) indexable information.

Specifically, after entering the optimizer, the logic optimization is performed according to the original normal index processing flow, and the key steps may include a pull-up sub-query, a condition simplification, an external connection elimination, a selection operation push-down, and the like, which refer to the prior art and are not described herein again. Then, whether the clauses of the current SQL statement contain MIN clauses/MAX clauses is checked, and the following conditions can be specifically met: the current clause is the simplest SFW query, i.e. the target column only contains MIN or MAX aggregates, i.e. the clause does not contain group by, order by, multi-table join, subquery, window function, grouping set, cte, etc. When the condition is satisfied, the MIN/MAX indexable condition is recorded in the sys _ recammond _ condition table. It is also possible to check the order by clause (i.e., the FILTER clause) of the current clause and, if the current clause is a single table query and there is no group by clause, record the corresponding FILTER (FILTER) indexable the case into the sys _ recammond _ condition table. Or, the physical optimization can be processed according to the original flow, and when the scanning algorithm is selected for the table, the where condition and join condition on the table are recorded into the sys _ recammond _ condition table. This may end the indexable information capture process and obtain the indexable information indexable case recorded as shown in fig. 3.

In this embodiment, for the MIN clause/MAX clause, the index-able MIN/MAX information may be recorded in the original processing logic for determining whether the MIN/MAX clause can use the index. For the sorting clause, the information of the sorting sequence can be extracted for recording the indexable information of the sorting aiming at the sorting (order) clause. The condition that the sequencing statement comprises a plurality of lists is not supported, and the sequencing statement can be eliminated. For the filtering condition and the join condition, after all the original logic optimization is completed, the condition extraction record on the table is used for filtering the indexable information and the connection indexable information. Wherein if a preset query clause contains a plurality of table conditions, one condition of each table is recorded.

In step S102, an optimization index suggestion is created for the SQL statement to be optimized based on the indexable information.

Illustratively, in some embodiments of the present disclosure, for the most valued indexable information and the sorted indexable information, the indexing system of the database creates an optimized index proposal sentence based on the table name information and the column name information to which the extracted most valued clause and the sorted clause relate.

For example, the indexing system of the database may support one or more of a B-tree index, a B + number index, and a hash index, but is not limited thereto. In this embodiment, the supporting of the B-tree index is taken as an example for explanation. Specifically, for MIN/MAX indexable information and sorted indexable information, a B-tree (BTree) index may be used for optimization, for example, table names and column name information of records may be directly obtained to join an index proposal statement, so as to obtain an optimized index proposal statement DDL.

Specifically, for filtering indexable information and join indexable information, it is required to query whether the indexing system of the database supports relevant operators, such as a ">" operator, BTree index support, and HASH index support. Therefore, in this embodiment, the optimized index suggestion statement DDL may be created by the BTree index based on the extracted table name information and column name information related to the filter condition and join condition.

In a specific embodiment, as shown in fig. 3, the creating of the optimization index suggestion may include the following steps:

the method comprises the following steps: and opening a sys _ recommond _ condition table, and querying the indexable condition of each SQL statement to be optimized by taking the QueryID as a condition.

Step two: if the indexable condition record is empty, ending the current flow; otherwise, each indexable case record in the sys _ recurmond _ condition table is traversed.

Step three: if the current SQL statement records the MIN/MAX condition: extracting the table name and the column name, inquiring whether the btree index supports the type of the index current column or not for each involved column, and if so, splicing out an optimized index proposal sentence with the type such as 'create index idx _ tbl _ index _ a on tbl _ index using btree (a)'.

Step four: if the current record is a sort (ORDER) case: and extracting the table name and the column name, and inquiring whether the btree index supports the type of the current column of the index for each involved column. For unsupported columns, directly discard; for the supported columns, an optimized index suggestion statement like "create index idx _ tbl _ index _ a on tbl _ index using btree (a, b)" is uniformly spliced out.

Step five: if the current record is a FILTER (FILTER) condition and/or a join condition case: for each involved column, querying whether all index systems of the database support operators corresponding to the current conditions, and directly discarding the unsupported columns. For the supporting situation, continuously inquiring whether the index supported by the current database supports the index of the type, such as the type corresponding to the FILTER condition and the join condition. If yes, obtaining the table name and the column name which meet the two conditions, and uniformly splicing an optimized index suggestion statement such as 'create index idx _ tbl _ index _ a on tbl _ index using btree (a, b)'.

Step six: and recording the optimized index proposal sentences into the sys _ recurmond _ index view. The INDEX _ DDL column in FIG. 3 illustrates a specific optimized INDEX suggestion statement for three SQL statements.

In step S103, a hypothesis index is created based on the optimization index suggestion, and a performance improvement yield of the SQL statement to be optimized is determined based on the hypothesis index.

In this embodiment, after the optimized index suggestion statement is given, the index profitability can also be given together to help the user to make a decision to adjust the performance of the database more conveniently.

Specifically, in one embodiment, the creating of the hypothetical index based on the optimized index suggestion in step S103 includes the following steps:

step i) creating a system hypothesis index table in the memory.

Step ii) storing meta information of the hypothetical index in the system hypothetical index table, the meta information comprising one or more of an index name, an index type, a table object identifier, an index column number, an index column name, an index column type, an index operator.

For example, the hypothetical index may be used by the optimizer to evaluate the execution plan without actually creating the index. Specifically, an additional memory structure sys _ hypothetic _ index table may be created to store meta information of the hypothetical index, which may be encapsulated as a view for presentation. Assuming that the creation of the INDEX only stores the meta information, and does not generate data, the meta information is related to the above-mentioned optimized INDEX suggestion statement, and may include, for example, information such as an INDEX name, a table Object Identifier (OID), an INDEX column number, an INDEX column name, an INDEX column type, an INDEX operator, and an INDEX type obtained by the optimized INDEX suggestion statement shown in the INDEX _ DDL column in fig. 3.

Specifically, in an embodiment, with reference to fig. 2, the step of determining the performance improvement yield of the SQL statement to be optimized based on the hypothetical index in step S103 may specifically include the following steps:

step S201: and estimating a first total cost of the original execution plan of the SQL statement to be optimized.

Step S202: and configuring the hypothesis indexes in the original execution plan to form an optimized execution plan.

Step S203: estimating a second total cost of the optimized execution plan, and determining the profitability based on the first total cost and the second total cost.

In some embodiments of the present disclosure, configuring the hypothesis index in the original execution plan to form an optimized execution plan may specifically include: acquiring meta information of the hypothesis index; the meta information comprises one or more of index name, index type, table object identifier, index column number, index column name, index column type and index operator; and when the optimizer constructs an index list for the table related to the SQL statement to be optimized, filling the meta information of the hypothetical index into the candidate index list.

Index creation often takes much time and consumes much resources, so the hypothetical index is introduced in the embodiment. The assumption index is used by the optimizer to evaluate the execution plan without actually creating the index, and the specific method may be: an additional memory structure sys _ hypothetic _ index table is created to hold meta information of the hypothetical index, which can be encapsulated as a view for presentation. It is assumed that the creation of the index only holds meta information and does not produce data. The meta information may include an index name, a table Object Identifier (OID), an index column number, an index column name, an index column type, an index operator, an index type, and the like. For an explain statement, when the optimizer builds an index list for a table, the meta information of the hypothetical index may also be populated into the candidate index list. Subsequent operations of the optimizer thus treat the hypothetical index as a normal index.

According to the scheme of the embodiment, by introducing the hypothesis index, time and resources can be saved when the index suggestion is created and the yield is calculated, the processing efficiency is improved, meanwhile, the influence on the database can be reduced, and the performance of the database is improved.

In some embodiments of the present disclosure, the following steps may also be included: before estimating a second total cost of the optimized execution plan, acquiring the number of index tuples of the hypothetical index, the total number of pages of the index and the hierarchical depth of the index; estimating the second total cost based on the number of index tuples, the total number of pages indexed, and the hierarchical depth of the index.

For example, after the optimizer treats the hypothetical index as a normal index, additional processing may be performed during estimation, and the processing may be: the number of indexed tuples is estimated based on the table data and the associated selectivity function. The width of an index tuple is estimated based on the number of index columns and the data type of the index columns, and further the index element ancestor which can be borne by a page is estimated, so that the total page number contained in the index and the hierarchy depth of the index can be estimated, and the specific processing process can refer to the prior art. The cost can be estimated by using the hypothetical index as a normal index through the information. Through the processing steps, an optimized execution plan given by using the hypothesis index can be obtained.

The results of the original execution plan not using the hypothetical index and the optimized execution plan using the hypothetical index may then be formatted, e.g., json, and finally the total cost of the two execution plans may be extracted, and the profitability of the index may be calculated.

For example, in some embodiments of the present disclosure, the determining the profitability based on the first total cost before _ cost and the second total cost after _ cost may specifically include: determining a difference value of the first total cost before _ cost minus a second total cost after _ cost; and dividing the difference value by the first total cost before _ cost to obtain a percentage value as the yield. Obtaining an SQL statement execution plan cost before the creation of the optimized index suggestion, before _ cost, and obtaining an SQL statement execution plan cost after the creation of the optimized index suggestion: after _ cost.

Wherein, the profitability is (before _ cost-after _ cost)/before. As shown in fig. 3, the yield of 3 SQL statements is higher, and the performance of the database system is improved better.

In one specific example, assume that the index creation flow may include the following steps:

the method comprises the following steps: and starting the creation of the hypothesis index through a function interface by taking the query QueryID as an input.

Step two: and searching index creation statements of SQL statements to be optimized, and analyzing each index creation statement according to the original index creation flow.

Step three: the parsed result is stored in the sys _ hypothetic _ index table.

The hypothetical index cost evaluation flow may include the following steps:

the method comprises the following steps: obtaining an execution plan of an SQL statement to be optimized through an explain statement

Step two: and entering an optimizer logic optimization stage.

Step three: and at the MIN/MAX optimized position of the optimizer, searching whether the current SQL statement has a hypothesis index created for MIN/MAX, and if so, adding the hypothesis index into the candidate index list.

Step four: and entering an optimizer physical optimization stage.

When the optimizer selects an index algorithm for the table, whether the current SQL statement has a hypothesis index created for sorting is searched, and if yes, the hypothesis index is added into a candidate index list.

Step five: when the cost evaluation is carried out on the hypothesis indexes, a cost evaluation algorithm of the hypothesis indexes is introduced. The index tuple number is equal to the table tuple number, the index record width is the sum of the index column width and the tuple header, and the index tuple number, the total page number of the index and the hierarchy depth of the index which can be contained in one index page are further calculated. With this information, the cost evaluation of the index can be the same as that of the normal index.

Step six: and evaluating the execution plan to be performed according to the normal index by using the hypothesis index.

Step seven: an optimized execution plan that incorporates the hypothetical index is obtained.

The profitability assessment process may comprise the steps of:

the method comprises the following steps: and acquiring an execution plan of the SQL statement to be optimized through the explain statement, and extracting the total cost estimated by the execution plan as the cost of the original execution plan, wherein the total cost is named as before _ cost.

Step two: the hypothetical index is created based on the optimized index suggestion SQL statement.

Step three: and obtaining an optimized execution plan of the SQL statement to be optimized through the explain statement, and extracting the total cost estimated by the optimized execution plan as the cost of the optimized execution plan, wherein the cost is named as after _ cost.

Step four: by the formula: the profit rate is calculated as (before _ cost-after _ cost)/before _ cost, and can be updated in the view of sys _ recurmond _ index as shown in fig. 3.

Step five: and ending the income evaluation flow.

It should be noted that although the various steps of the methods of the present disclosure are depicted in the drawings in a particular order, this does not require or imply that these steps must be performed in this particular order, or that all of the depicted steps must be performed, to achieve desirable results. Additionally or alternatively, certain steps may be omitted, multiple steps combined into one step execution, and/or one step broken down into multiple step executions, etc. Additionally, it will also be readily appreciated that the steps may be performed synchronously or asynchronously, e.g., among multiple modules/processes/threads.

An embodiment of the present disclosure further provides a database index suggestion processing apparatus, as shown in fig. 4, the database index suggestion processing apparatus may include: the capturing and recording module 401 is configured to capture and record indexable information corresponding to a preset query clause when the SQL statement to be optimized is executed to optimize the preset query clause in the query optimization stage. An index suggestion creating module 402, configured to create an optimized index suggestion for the SQL statement to be optimized based on the indexable information. And the profit calculation module 403 is configured to create a hypothetical index based on the optimized index suggestion, and determine a performance improvement profit rate of the SQL statement to be optimized based on the hypothetical index.

The database index suggestion processing device of the embodiment can create different optimized index suggestions based on different preset query clauses, the range of the index suggestions is wide, and the quantized performance improvement yield can be calculated, so that the content of the index suggestions is richer, the database performance can be adjusted and optimized by being conveniently referred by a database administrator, and by introducing the hypothesis indexes, time and resources are saved when the index suggestions are created and the yield is calculated, the performance of the database is further improved to a certain degree integrally, meanwhile, the time of manual analysis is saved, and the optimization processing efficiency of the index suggestions is improved.

In some embodiments of the present disclosure, the preset query clause may include one or more of a most valued clause, a ranking clause, a filtering condition, and a join condition. When the SQL statement to be optimized is executed to the query optimization stage, and a preset query clause is optimized, the capturing and recording module 401 captures and records indexable information corresponding to the preset query clause, which may specifically include: when the preset query clause uses the index, respectively capturing and recording the corresponding most value indexable information, sequencing indexable information, filtering indexable information and connecting indexable information through an optimizer.

In some embodiments of the present disclosure, the index suggestion creating module 402 creates an optimized index suggestion for the SQL statement to be optimized based on the indexable information, which specifically includes: for the most valued indexable information and the sequencing indexable information, the indexing system of the database creates an optimized index suggestion sentence based on the table name information and the column name information related to the extracted most valued clause and sequencing clause; for the filtering indexable information and the connection indexable information, querying to determine whether an index system of the database supports operators related to the filtering conditions and the connection conditions; and if so, creating an optimized index suggestion sentence by the index system based on the table name information and the column name information related to the extracted filtering condition and connecting condition.

In some embodiments of the present disclosure, the revenue calculation module 403 creates a hypothetical index based on the optimization index suggestion, and determines a performance improvement revenue rate of the SQL statement to be optimized based on the hypothetical index, which specifically includes: estimating a first total cost of the original execution plan of the SQL statement to be optimized; configuring the hypothesis indexes in the original execution plan to form an optimized execution plan; estimating a second total cost of the optimized execution plan, and determining the profitability based on the first total cost and the second total cost.

In some embodiments of the disclosure, the determining, by the profit calculation module 403, the profit margin based on the first total cost and the second total cost may specifically include: determining a difference value of the first total cost minus a second total cost; and dividing the difference value by the first total cost to obtain a percentage value as the yield.

In some embodiments of the disclosure, the configuring, by the benefit calculation module 403, the hypothesis index into the original execution plan to form an optimized execution plan may specifically include: acquiring meta information of the hypothesis index; the meta information comprises one or more of index name, index type, table object identifier, index column number, index column name, index column type and index operator; and when the optimizer constructs an index list for the table related to the SQL statement to be optimized, filling the meta information of the hypothetical index into the candidate index list.

In some embodiments of the present disclosure, the profit computation module 403 may be further configured to obtain the number of index tuples of the hypothetical index, the total number of pages of the index, and the hierarchical depth of the index before estimating the second total cost of the optimized execution plan; estimating the second total cost based on the number of index tuples, the total number of pages indexed, and the hierarchical depth of the index.

The specific manner in which the above-mentioned embodiments of the apparatus, and the corresponding technical effects brought about by the operations performed by the respective modules, have been described in detail in the embodiments related to the method, and will not be described in detail herein.

It should be noted that although in the above detailed description several modules or units of the device for action execution are mentioned, such a division is not mandatory. Indeed, the features and functionality of two or more modules or units described above may be embodied in one module or unit, according to embodiments of the present disclosure. Conversely, the features and functions of one module or unit described above may be further divided into embodiments by a plurality of modules or units. The components shown as modules or units may or may not be physical units, i.e. may be located in one place or may also be distributed over a plurality of network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the wood-disclosed scheme. One of ordinary skill in the art can understand and implement it without inventive effort.

The embodiments of the present disclosure also provide a computer-readable storage medium, on which a computer program is stored, where the computer program, when executed by a processor, implements the steps of the database index suggestion processing method according to any one of the embodiments.

By way of example, and not limitation, such readable storage media can be an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples (a non-exhaustive list) of the readable storage medium include: an electrical connection having 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 disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

The computer readable storage medium may include a propagated data signal with readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A readable storage medium may also be any readable medium that is not a readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a readable storage medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.

The embodiment of the disclosure also provides an electronic device, which includes a processor and a memory, wherein the memory is used for storing the executable instruction of the processor. Wherein the processor is configured to perform the steps of the database index suggestion processing method in any of the above embodiments via execution of the executable instructions.

An electronic device 600 according to this embodiment of the invention is described below with reference to fig. 5. The electronic device 600 shown in fig. 5 is only an example and should not bring any limitation to the functions and the scope of use of the embodiments of the present invention.

As shown in fig. 5, the electronic device 600 is embodied in the form of a general purpose 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 that connects the various system components (including the storage unit 620 and the processing unit 610), a display unit 640, and the like.

Wherein the storage unit stores program code executable by the processing unit 610 to cause the processing unit 610 to perform steps according to various exemplary embodiments of the present invention described in the database index suggestion processing method section above in this specification. For example, the processing unit 610 may perform the steps of the method as shown in fig. 1.

The storage unit 620 may include readable media in the form of volatile memory units, 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.

The memory 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 thereof, may comprise an implementation of a network environment.

Bus 630 may be 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.

The electronic device 600 may also communicate with one or more external devices 700 (e.g., keyboard, pointing device, bluetooth device, etc.), with one or more devices that enable a user to interact with the electronic device 600, and/or with any devices (e.g., router, modem, etc.) that enable the electronic device 600 to communicate with one or more other computing devices. Such communication may occur via an input/output (I/O) interface 650. Also, the electronic device 600 may 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 the network adapter 660. The network adapter 660 may communicate with other modules of the electronic device 600 via the bus 630. It should be appreciated that although not shown in the figures, 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, among others.

Through the above description of the embodiments, those skilled in the art will readily understand that the exemplary embodiments described herein may be implemented by software, or by software in combination with necessary hardware. Therefore, the technical solution according to the embodiments of the present disclosure may be embodied in the form of a software product, which may be stored in a non-volatile storage medium (which may be a CD-ROM, a usb disk, a removable hard disk, etc.) or on a network, and includes several instructions to enable a computing device (which may be a personal computer, a server, or a network device, etc.) to execute the above database index suggestion processing method according to the embodiments of the present disclosure.

It is noted that, in this document, relational terms such as "first" and "second," and the like, may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Also, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.

The foregoing are merely exemplary embodiments of the present disclosure, which enable those skilled in the art to understand or practice the present disclosure. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the disclosure. Thus, the present disclosure is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. a database index suggestion processing method, is characterized in that, comprises:

When the to-be-optimized SQL statement is executed to the query optimization stage and the preset query clause is optimized, the indexable information corresponding to the preset query clause is captured and recorded;

based on the indexable information, creating an optimized index suggestion for the to-be-optimized SQL statement;

A hypothetical index is created based on the optimized index suggestion, and the performance improvement rate of the SQL statement to be optimized is determined based on the hypothetical index.

2. The method for processing database index suggestions according to claim 1, wherein the preset query clause comprises one or more of a most value clause, a sorting clause, a filter condition and a join condition;

When the to-be-optimized SQL statement is executed to the query optimization stage and the preset query clause is optimized, the indexable information corresponding to the preset query clause is captured and recorded, including:

When the preset query clause is optimized by using an index, the most value indexable information, the sorting indexable information, the filtering indexable information and the connection indexable information corresponding to the records are captured by the optimizer respectively.

3. The method for processing database index suggestions according to claim 2, wherein, based on the indexable information, creating an optimized index suggestion for the to-be-optimized SQL statement comprises:

For the most value indexable information and the sorting indexable information, the indexing system of the database creates an optimized index suggestion statement based on the table name information and column name information involved in the extracted most value clause and the sorting clause;

For the filter indexable information and the connection indexable information, query to determine whether the indexing system of the database supports the operator related to the filter condition and the connection condition; if so, the indexing system based on the extracted filter The table name information and column name information involved in the condition and join condition create an optimized index suggestion statement.

4. The method for processing database index suggestions according to claim 3, wherein the index system of the database supports one or more of a B-tree index, a B+ number index, and a hash index; the most value clause Include MAX clause/MIN clause.

The method for processing database index suggestions according to any one of claims 1 to 4, wherein the creating a hypothetical index based on the optimized index suggestion comprises:

Create system hypothetical index tables in memory;

Meta information of the hypothetical index is stored in the system hypothetical index table, where the meta information includes one of index name, index type, table object identifier, number of index columns, index column name, index column type, and index operator or more.

6. The database index suggestion processing method according to claim 5, wherein the determining the performance improvement rate of return of the SQL statement to be optimized based on the hypothetical index comprises:

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 constitute an optimized execution plan;

A second total cost of the optimized execution plan is estimated, and the yield is determined based on the first total cost and the second total cost.

7. The method for processing database index suggestions according to claim 6, wherein the determining the rate of return based on the first total cost and the second total cost comprises:

determining the difference between the first total cost and the second total cost;

A percentage value obtained by dividing the difference by the first total cost is used as the yield.

8 . The method for processing database index suggestions according to claim 6 , wherein configuring the hypothetical index in the original execution plan to constitute an optimized execution plan comprises: 8 .

Obtain meta-information of the hypothetical index; 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;

When the optimizer builds an index list for the table involved in the SQL statement to be optimized, the meta-information of the hypothetical index is filled into the candidate index list.

9. The method for processing database index suggestions according to claim 8, further comprising:

Before estimating the second total cost of the optimized execution plan, obtain the number of index tuples of the hypothetical index, the total number of pages of the index, and the level depth of the index;

The second total cost is estimated based on the number of index tuples, the total number of pages indexed, and the hierarchical depth of the index.

10. A database index suggestion processing device, characterized in that it comprises:

A capture and record module, configured 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, configured to create an optimized index suggestion for the to-be-optimized SQL statement based on the indexable information;

A benefit calculation module, configured 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.

11 . A computer-readable storage medium having a computer program stored thereon, wherein when the program is executed by a processor, the steps of the database index suggestion processing method according to any one of claims 1 to 9 are implemented.

12. An electronic device, characterized in that, comprising:

processor; and

a memory for storing executable instructions for the processor;

The processor is configured to execute the steps of the method for processing database index suggestions according to any one of claims 1 to 9 by executing the executable instructions.