Database index management method and system

Abstract

The invention discloses a database index management method and system, and the method comprises the steps: carrying out the selection of example workload through monitoring the execution statistical information of query, and identifying a query set which needs to be adjusted and optimized; generating candidate index sets, and determining a relative connection sequence of the candidate index sets through connection parameters; and determining a production index set by sorting and evaluating the usefulness of the candidate index set, and materializing the candidate index set on a database. According to the method, the search space of the candidate index is limited by utilizing the structure attribute of the single query, and the query structure is only used for generating the target index candidate item and does not search the whole storage space; according to the method, the feasibility of efficient search of the wide multi-column index is enhanced, the dependence on a database query optimizer is reduced, and the IMS does not query data distribution statistical information from the optimizer when a new column must be added to the multi-column candidate index every time, so that the running time of the IMS is reduced by several orders of magnitude, and better performance is achieved.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a database index management method and system. Background Art

[0002] One of the challenges of managing a database is maintaining an efficient system structure to facilitate data access for ever-changing workloads. This was done manually by professional database administrators in the past. With the advent of cloud-managed databases, the database tuning process has been automated, resulting in a reduction in manual involvement and a significant improvement in system design efficiency. A well-designed set of secondary indexes can have a decisive impact on applications powered by relational databases. For example, using larger storage space in exchange for lower CPU and I / O utilization, and reducing I / O during query execution to improve latency.

[0003] Existing approaches are designed from aspects such as navigation and selection in the available index space, however, few solutions can manage a variety of production workloads and identify secondary indexes in a reasonable time. Summary of the invention

[0004] The purpose of this section is to summarize some aspects of embodiments of the present invention and briefly introduce some preferred embodiments. Some simplifications or omissions may be made in this section and the specification abstract and the invention title of this application to avoid blurring the purpose of this section, the specification abstract and the invention title, and such simplifications or omissions cannot be used to limit the scope of the present invention.

[0005] In view of the above existing problems, the present invention is proposed. Therefore, the present invention provides a database index management method to solve the problem of how to manage various production workloads and identify secondary indexes within a reasonable time.

[0006] In order to solve the above technical problems, the present invention provides the following technical solutions:

[0007] In a first aspect, the present invention provides a database index management method, comprising: selecting an example workload by monitoring query execution statistics, and identifying a query set that needs to be tuned;

[0008] Based on each identified query set, a candidate index set is generated, and a relative join order of the candidate index sets is determined by a join parameter;

[0009] By ranking and evaluating the usefulness of the candidate index sets, a production index set is determined, and the candidate index sets are materialized on a database.

[0010] As a preferred solution of the database index management method of the present invention, the query set identified as requiring optimization includes:

[0011] query and analyze the execution statistics to identify whether there are effectively executed queries;

[0012] The execution statistics include CPU calculation cost, number of rows read, number of rows sent, and number of executions corresponding to each normalized query;

[0013] Based on querying the execution statistics, by identifying the query that needs to be tuned, select a workload with examples according to preset attributes, wherein the preset attributes include execution frequency, average CPU consumption per execution, and discarded data ratio;

[0014] clearing spurious query executions from a specific source based on a threshold of the execution frequency;

[0015] According to the average CPU consumption and discarded data ratio ddr avg , the storage budget SB allocated by optimizing the normalized query q executed using the configured OC is expressed as:

[0016] SB(q,OC,Δt)=(1-ddr avg (q,OC,Δt))*ccpu avg (q,OC,Δt)

[0017] Where SB(q,OC,Δt) represents the storage budget of the standardized query q in the time span Δt, cpu avg (q,OC,Δt) represents the average CPU utilization of the standardized query q within the time span Δt, ddr avg (q,OC,Δt) represents the discarded data ratio of the normalized query q within the time span Δt.

[0018] As a preferred solution of the database index management method of the present invention, the generating of the candidate index set includes:

[0019] Taking the workload and the join parameter j as input, for each query Q in the workload, checking whether the covering index is valid;

[0020] Generate a partially ordered set of index columns through an optional query execution strategy based on the optimal primary operation for the query;

[0021] The partial order sets are merged to obtain a final partial order set, and corresponding to each partial order, a candidate index set is generated by arbitrarily selecting a total order of index columns that satisfy the partial order.

[0022] As a preferred solution of the database index management method described in the present invention, wherein: merging the poset includes:

[0023] Define the function MergeCandidatesPairwise, which takes two strict partial orders (P, <P) and (Q, <Q) and produces another strict partial order (R, <R) when the precondition Cmerge is satisfied, expressed as:

[0024]

[0025] In C merge case, the strict partial order (R, <R) is expressed as:

[0026] (R, <R): = (P, <P) ⊕ (Q, <Q)

[0027] In other cases, the strict partial order (R, <R) is expressed as:

[0028]

[0029] where P, Q, and R respectively represent subsets of columns of the same table, C merge represents the precondition that the function MergeCandidatesPairwise can use, represents the empty set, a and b represent columns in the table, aPb, aQb, and aRb represent relationships, and ⊕ represents the ordinal sum operator.

[0030] Generate a set of partial orders of the index column PO through the function MergePartialOrders and recursively merge them pairwise until no new candidates are generated, expressed as:

[0031] PO n+1 : = {MergeCandidatesPairwise(X,Y)|X,Y ∈ PO n}

[0032] where X and Y represent partial orders in POn;

[0033] Merge PO, starting with PO0 := PO and using the function MergePartialOrders to return PO m , that is, PO m = PO m+1 .

[0034] As a preferred solution of the database index management method described in the present invention, wherein: sorting and evaluating the usefulness of the candidate index set includes:

[0035] The utility of the index is calculated by summarizing the gain cost of a single query using the index and counting the writes generated by index maintenance, expressed as:

[0036] u(i)=s i,q ·U + (q,i)+u - (i)

[0037] Among them, s i,q represents the share of the gain of index i, U + (q,i) represents the gain, u - (i) represents the maintenance cost of a single index i.

[0038] As a preferred solution of the database index management method of the present invention, the summarizing of the gain cost generated by a single query using an index includes:

[0039] The computational cost of query q is decomposed into two mPain components, expressed as:

[0040]

[0041] Among them, cost r (q,OC) represents the computational cost of locating records using the index in configuration X, cost u (q,i) represents the computational cost of updating index i in OC;

[0042] Quantify the gain cost of index query, isolate each query q, and calculate the gain cost U of the index query + , expressed as:

[0043]

[0044] Where L represents the candidate set generated for query q, represents the computational cost of querying q without a candidate set, It represents the average CPU utilization of the common query q in the time span Δt without the candidate set.

[0045] As a preferred solution of the database index management method of the present invention, the statistics of the writes generated by the index maintenance include:

[0046] The maintenance cost is calculated based on the index update and the required storage space. The cost of index maintenance is calculated based on a single index i and is expressed as:

[0047]

[0048] Among them, cost u(q,i) represents the computational cost of querying q under a single index i, and W represents the workload.

[0049] In a second aspect, the present invention provides a database index management system, comprising:

[0050] The identification module is used to select sample workloads and identify query sets that need to be tuned by monitoring query execution statistics;

[0051] A generation module, used for generating a candidate index set based on each identified query set, and determining a relative join order of the candidate index set through a join parameter;

[0052] An evaluation module is used to determine a production index set by ranking and evaluating the usefulness of the candidate index set, and to materialize the candidate index set on a database.

[0053] In a third aspect, the present invention provides an electronic device, comprising:

[0054] Memory and processor;

[0055] The memory is used to store computer executable instructions, and the processor is used to execute the computer executable instructions. When the computer executable instructions are executed by the processor, the steps of the database index management method are implemented.

[0056] In a fourth aspect, the present invention provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the steps of the database index management method.

[0057] Compared with the prior art, the present invention has the following beneficial effects: the present invention utilizes the structural attributes of a single query to limit the search space of candidate indexes, and the query structure is only used to generate target index candidates without searching the entire storage space; the feasibility of efficient search of wide multi-column indexes is enhanced, and most solutions must limit the index width to prevent a surge in the number of index configurations; IMS is one of the few widely deployed production algorithms that systematically handles complex join queries, reducing dependence on database query optimizers, and each time a new column must be appended to a multi-column candidate index, IMS does not query the optimizer for data distribution statistics, which makes the running time of IMS several orders of magnitude smaller than other modern algorithms. IMS can both fill the gaps left by manual tuning and build secondary indexes from scratch, with better performance, and can also detect and delete (partial) unused indexes. BRIEF DESCRIPTION OF THE DRAWINGS

[0058] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for describing the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative labor. Among them:

[0059] Figure 1 A schematic diagram of the overall process of a database index management method according to an embodiment of the present invention;

[0060] Figure 2 This is a Q2 connection diagram of a database index management method according to an embodiment of the present invention. DETAILED DESCRIPTION

[0061] In order to make the above-mentioned purposes, features and advantages of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are described in detail below in conjunction with the drawings of the specification. Obviously, the described embodiments are part of the embodiments of the present invention, but not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary persons in the art without creative work should fall within the scope of protection of the present invention.

[0062] In the following description, many specific details are set forth to facilitate a full understanding of the present invention, but the present invention may also be implemented in other ways different from those described herein, and those skilled in the art may make similar generalizations without violating the connotation of the present invention. Therefore, the present invention is not limited to the specific embodiments disclosed below.

[0063] Secondly, the term "one embodiment" or "embodiment" as used herein refers to a specific feature, structure, or characteristic that may be included in at least one implementation of the present invention. The term "in one embodiment" that appears in different places in this specification does not necessarily refer to the same embodiment, nor does it refer to a separate or selective embodiment that is mutually exclusive with other embodiments.

[0064] The present invention is described in detail with reference to schematic diagrams. When describing the embodiments of the present invention, for the sake of convenience, the cross-sectional diagrams showing the device structure will not be partially enlarged according to the general scale, and the schematic diagrams are only examples, which should not limit the scope of protection of the present invention. In addition, in actual production, the three-dimensional dimensions of length, width and depth should be included.

[0065] At the same time, in the description of the present invention, it should be noted that the directions or positional relationships indicated by the terms "upper, lower, inner and outer" are based on the directions or positional relationships shown in the drawings, and are only for the convenience of describing the present invention and simplifying the description, rather than indicating or implying that the device or element referred to must have a specific direction, be constructed and operated in a specific direction, and therefore cannot be understood as limiting the present invention. In addition, the terms "first, second or third" are only used for descriptive purposes and cannot be understood as indicating or implying relative importance.

[0066] In the present invention, unless otherwise clearly specified and limited, the terms "install, connect, connect" should be understood in a broad sense, for example: it can be a fixed connection, a detachable connection or an integral connection; it can also be a mechanical connection, an electrical connection or a direct connection, or it can be indirectly connected through an intermediate medium, or it can be the internal communication of two components. For ordinary technicians in this field, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0067] Example 1

[0068] Reference Figure 1-2 Tables 1-8 are an embodiment of the present invention, which provides a database index management method, such as Figure 1 As shown, including:

[0069] The focus of the index tuning problem in this approach is to select the optimal configuration OC (optimal configuration) for a relational database D (Database) so as to minimize the execution computational cost EW (Execution works) of the workload under certain constraints. For the scope of this problem, the configuration OC consists of a set of secondary indexes, each of which is materialized on a table in D. The workload EW consists of queries run on D. Each query q has an associated weight Wq, which can be based on the execution frequency of query q, the overall CPU consumption, and the importance manually specified by the query user.

[0070] The static index tuning problem involves finding the optimal subset of possible optimal indexes to minimize the computational cost under a certain workload. It is defined as follows:

[0071] Given a certain workload QW (Quantitative work), find the OC by query q∈QW, a set of possible indexes U and storage budget SB (Storage budget). * , so that:

[0072]

[0073] Among them, Wq is the weight associated with query q, cost(q,OC) is the execution computation cost of q under the configuration, and the index configuration OC conforms to SB.

[0074] Among them, the continuous index tuning problem is closer to real-world use, because most developers have an initial set of indexes when they deploy a database. This initial setting may not always be the most effective choice, but it can serve as a starting point for improvement. The problem statement is as follows:

[0075] Given a workload QW (consisting of queries q∈QW), a set of pre-existing indexes P and a storage budget SB, find P′ such that:

[0076]

[0077] Among them, (0≤A1, A2, A3<1) are configurable parameters. By adjusting the parameters to achieve the desired goal, the first formula above is based on the configuration OC detected at startup. * To control the overall computational cost, the second formula requires improving the performance of at least one query, and the third formula prevents the performance of a single query from regressing.

[0078] For a given query q and configured OC, Wq and cost(q,OC) are time functions that change with the workload and underlying data distribution, so the database needs to be continuously tuned.

[0079] It should be noted that selecting the optimal set of indexes for a given workload is NP-hard (Non-deterministic Polynomial time problem-hard, which means that all NP problems can be normalized within polynomial time complexity). In fact, selecting the optimal index for a single join query is itself a complex problem, and determining the optimal join order is itself NP-hard. Most solutions rely on using a query optimizer for evaluation, that is, the computational cost of executing a query using a hypothetical index configuration. The number of these configurations has surged in real production workloads, resulting in significant limitations on the solution space. This embodiment explores automatic index management from a practical perspective, relying on the design of index methods. The gain of using indexes is to reduce I / O operations, which comes from the efficient selection of records - by eliminating the need to sort the selected records, and only reading the relevant attributes and / or of the selected records.

[0080] The following lists the end-to-end design of a configurable index management method, IMS (Index Manager System), and points out the guarantees and trade-offs associated with this design choice. Some of the features of IMS include: fast convergence when composite indexes, reduced dependence on the query optimizer, and "no regression" guarantees for workloads. Each index suggestion of IMS is accompanied by a driver description, which makes it easier to verify driver changes. IMS can quickly identify the most effective indexes, and the resulting physical design is close to optimal.

[0081] S100, selecting sample workloads by monitoring query execution statistics, and identifying query sets that need to be tuned;

[0082] S200, generating a candidate index set based on each identified query set, and determining a relative join order of the candidate index set through a join parameter;

[0083] S300, by sorting and evaluating the usefulness of the candidate index sets, determining the production index set, and concretizing the candidate index set on the database.

[0084] It should be noted that Figure 1 The IMS process is described in the algorithm, which has two distinct phases, both of which follow the overall architecture given in Algorithm 1. If multiple queries are executed inefficiently, it is difficult to predict their relative frequencies in the steady state after the necessary supporting indexes have been added. Therefore, IMS attempts to add indexes of smaller width to support each inefficient query in the observed workload. In the next phase, it identifies queries that may benefit from covering indexes. Typically, these queries are executed frequently enough to offset the additional storage cost of creating wider indexes.

[0085] Specifically, Algorithm 1 is shown in Table 1, where Line 1 represents the workload selection step of the example, which identifies the query set that needs to be tuned by monitoring the execution statistics of the queries. For each query identified in the previous step, a set of candidate indexes is generated (Line 2). The join parameter j determines which tables present in the complex join query are exhaustively evaluated for all possible relative join orders.

[0086] Table 1: Algorithm 1 Automatic Index Manager Algorithm

[0087]

[0088] The set of candidate indexes is materialized on the database to determine whether the query optimizer can effectively use the indexes during execution (line 3), which is necessary to ensure that no queries experience regressions in production. The production index set is determined by ranking the usefulness of the candidate indexes (line 4), and then the candidate indexes are materialized in the production database.

[0089] It should be noted that the algorithm is applicable to both static and continuous index tuning. It can be run once to bootstrap the initial index set when the database is created, and then continuously tuned periodically to adapt to changes in workload.

[0090] Preferably, in step S100, identifying the query set that needs to be tuned includes:

[0091] Query analysis execution statistics to identify whether there are effectively executed queries;

[0092] The execution statistics include CPU computation cost, number of rows read, number of rows sent, and number of executions corresponding to each normalized query.

[0093] Based on query execution statistics, we identify queries that need tuning and select workloads with examples based on preset attributes, including execution frequency, average CPU consumption per execution, and discarded data ratio.

[0094] Eliminate spurious query executions from specific sources based on a threshold of execution frequency;

[0095] Based on the average CPU consumption and discarded data ratio ddr avg , the storage budget SB allocated by optimizing the normalized query q executed using the configured OC is expressed as:

[0096] SB(q,OC,Δt)=(1-ddr avg (q,OC,Δt))*cpu avg (q,OC,Δt)

[0097] Where SB(q,OC,Δt) represents the storage budget of the standardized query q in the time span Δt, cpu avg (q,OC,Δt) represents the average CPU utilization of the standardized query q within the time span Δt, ddr avg (q,OC,Δt) represents the discarded data ratio of the normalized query q within the time span Δt.

[0098] Specifically, the goal of this step is to identify the query set that requires index tuning. The workload monitor analyzes execution statistics to identify whether there are queries that are executed efficiently. Query execution statistics include CPU calculation cost, number of rows read, number of rows sent, and number of executions corresponding to each normalized query. These data are used to select / sort queries and add potential indexes to achieve the best results.

[0099] Sample workloads are selected by identifying queries that require tuning based on query execution statistics. Selection of sample workloads for a given database is fully automated and done at configurable intervals. The selection process incorporates three main attributes - execution frequency, average CPU consumption per execution, and discarded data ratio.

[0100] Preferably, generating a candidate index set includes:

[0101] Taking the workload and the join parameter j as input, for each query Q in the workload, check whether the covering index is valid;

[0102] Generate a partially ordered set of index columns through an optional query execution strategy based on the optimal primary operation for the query;

[0103] The partial order sets are merged to obtain the final partial order set. For each partial order, a candidate index set is generated by arbitrarily selecting the total order of the index columns that satisfy the partial order.

[0104] It should be noted that the candidate generation process takes into account the structure of the query and can efficiently generate effective composite indexes. It indirectly considers statistical information about data distribution through data-free indexes. The key lies in defining the conversion function from column usage metadata to potential candidate indexes represented by the partial order of columns. The basic conversion function is basically the same on most relational database servers. This method greatly reduces the number of candidate servers.

[0105] Specifically, Algorithm 2 is shown in Table 3, which shows the high-level algorithm for generating candidate indexes. This algorithm takes the workload and the join parameter j as inputs. For each query Q in the workload, the algorithm checks whether a covering index is effective for it (line 3). The TryCoveringIndex procedure makes a determination based on the index used to execute Q in the current configuration. If the selectivity cannot be further improved, it tries to use a covering index, and adding a covering index is expected to greatly reduce the number of additional lookups of the primary key. Taking Q4 as an example, the covering index will not be tried until an index with a prefix of <col2, col3> has been used. Additionally, if such an index has already been used, the number of disk seeks should be high enough to offset the additional storage and computational costs incurred by adding a column col1 to the index. For fast storage media (such as SSDs), the threshold for such devices is relatively high.

[0106] In line 4 of this algorithm, by considering alternative query execution strategies, a set of posets of index columns is generated. The execution strategy is based on the main operation (without selection, grouping, or sorting) that is optimal for the query, and the main operation is performed before all other operations. For example, one execution strategy for query Q may involve sorting the rows in group order before selecting the rows, while another strategy prefers to perform the selection before sorting by group. The set of partial orders generated in the previous step is merged to obtain the final poset (line 6). For each partial order, a candidate index is generated by arbitrarily choosing a total order of the index columns that satisfies the partial order. This is implemented by the GenerateCandidateIndexPerPO procedure (line 7).

[0107] Table 2: Alternative Generation Algorithm for Algorithm 2

[0108]

[0109] Preferably, the merging of the posets includes:

[0110] Define the function MergeCandidatesPairwise, which takes two strict partial orders (P, <P) and (Q, <Q) and produces another strict partial order (R, <R) when the precondition C merge is satisfied, denoted as:

[0111]

[0112] In the case of C merge the strict partial order (R, <R) is denoted as:

[0113] (R, <R) := (P, <P) ⊕ (Q, <Q)

[0114] In other cases, the strict partial order (R, <R) is denoted as:

[0115]

[0116] Among them, P, Q, and R represent subsets of columns in the same table, and C merge Indicates the prerequisites for the MergeCandidatesPairwise function to be used. represents the empty set, a and b represent specific columns in the table, column a comes before column b, aPb, aQb, and aRb represent relationships, aPb represents that in the partial order relation P, a and b have P relationship, aQb represents that in the partial order relation Q, a and b have Q relationship, aRb represents that in the partial order relation R, a and b have R relationship, ⊕ represents ordinals and operators, all of which are semantically represented in the indexes of these partial order representations.

[0117] A set of partial orders of the index column PO is generated by the function MergePartialOrders (from Algorithm 2), and the partial orders are recursively merged in pairs until no new candidate objects are generated, which is expressed as:

[0118] PO n+1 :={MergeCandidatesPairwise(X,Y)|X,Y∈PO n}

[0119] Among them, X and Y represent the partial order in POn. Through the function MergeCandidatesPairwise, the production satisfies the prerequisite C merge A strict partial order when assigning the value to PO n+1 , perform recursive merging;

[0120] Merge PO, starting with PO0:=PO, and use the function MergePartialOrders to return PO m , namely PO m =PO m+1 , merging the partial partial order of PO helps to select the correct index key order, thereby improving efficiency.

[0121] Specifically, merging partial orders of index columns helps choose the correct order of index keys, thus maximizing efficiency. For example, consider the following two partial orders: <{col1,col2,col3}> and <{col2,col3}>. The first partial order indicates that some queries may benefit from creating an index with any order of col1, col2, and col3. The second partial order indicates that some queries may benefit from creating an index with any order of col2 and col3. Merging partial orders will create a new partial order: <{col2,col3},{col1}>.

[0122] The merged partial order places constraints on the ordering of the index columns, limiting them to<col2,col3,col1> and<col3,col2,col1> , any candidate satisfies the merged partial order, and queries on the merged basic partial order are valid.

[0123] It should be noted that the generation of candidates needs to consider the structure of the query and convert the structured metadata into a partial order representing the index candidates. The structural metadata collected by the workload monitor includes the operation information corresponding to each column, the edge in the table connection graph, and the factors in the selection. Table 3 lists an example of such metadata. The operation of this information when generating candidate indexes for common will be described below.

[0124] Table 3: Queue structure for candidate generation

[0125]

[0126] First we need projection, which is an operation used to select a subset of attributes (columns) from a relation (table). Suppose there is a table t1 with five columns, col1, col2, col3, col4, and col5. Here is a very simple example demonstrating projection.

[0127] Q1:SELECT col2,col3 FROM t1 WHERE col5<2

[0128] In this query, instead of requesting all columns from t1 for rows that satisfy the WHERE clause, only col2 and col3 are requested in the output. The following partial ordering will optimize the index set for the above example Q1: <{col5},{col2,col3}>.

[0129] When using slow storage media, consider these secondary indexes to prevent primary key lookups. You should also note that in order to avoid primary key lookups, col2 and col3 must be added at the end. Most algorithms only add one column at a time and may not be able to discover covering indexes for common queries.

[0130] Next, the selection is made. The WHERE clause can contain forms connected by logical operators (such as AND, OR).

[0131] column_name op expression

[0132] These decision functions that help select rows from a single table instance are called filter operations. For each different table instance, a partial order of the columns participating in the filter operation is constructed, for example:

[0133] E1:WHERE col1=5AND col2='ABC'AND col3 IN(5,9,11)

[0134] Will result in the following partial order of columns: <{col1,col2,col3}>.

[0135] (1) Complex and-or selection predicate functions may use complex and-or chains. Depending on the differences in database server implementations, the generation of candidate items may be slightly different. This technique is represented by the FactorizeIndexPredicates routine in Algorithm 5 as shown in Table 4. The disjunctive normal form (DNF) is used to process complex predicate functions. Each factor in the DNF produces a separate partial order. For example, the following two partial orders are obtained: <{col1,col2,col3}> and <{col2,col4}>.

[0136] E2:WHERE(col1=5AND col2='ABC'AND col3 IN(5,9,11))

[0137] OR(col2='CDE'AND col4=8)

[0138] Table 4: Algorithm 5 generates prediction candidates

[0139]

[0140] (2) Index prefix determination function: Depending on the database server, operations also play an important role. By filtering columns associated with operators such as equals and not equals, they can be linked together to build multi-column indexes, where each participating column usually enhances the selectivity (or effectiveness) of the index. However, if columns are associated with operators such as greater than and less than, and linked together to form a multi-column secondary index, each column after the first column may not have a strictly additive benefit to the selectivity. This is best explained with an example.

[0141] E3:WHERE col1=5AND col2='ABC'

[0142] AND col3>5AND col4<2.0

[0143] If in<col1,col2,col3,col4> If a multi-column secondary index is added to the primary key, then all rows where col1 = 5, col2 = ABC, and col3 > 5 may have to be read and col4 < 2 compared. Subsequent steps can be performed by reading qualifying rows from the primary key or using index condition pushdown optimization. Therefore, the following partial order can be constructed for expression E3: <{col1,col2}, {col3,col4}>.

[0144] This partial order represents a set of 4 multi-column secondary indexes where col1 and col2 come before col3 and col4. The order of col1 relative to col2 is not important, and similarly, the order of col3 relative to col4 is not important, but it depends on the overall selection of a single predicate function col3>5 and col4<2.0. It is more efficient to put the columns corresponding to the more selective predicate function first, and this can be achieved by using a dataless index.

[0145] Therefore, the sub-determination functions col1=5 and col2='ABC' determine the prefix candidate index, and this process corresponds to GenerateCandidateIndexPredicates in the program description Algorithm 5.

[0146] More precisely, an index prefix predicate is a subpredicate of a filter clause that can be resolved by index scanning rows that share a non-empty prefix, a constant prefix that can be determined from the subpredicate itself. For example, the predicate col1 = 5 can be resolved using the index on col1 by scanning all rows with prefix 5, and col2 = 'ABC' can be resolved using the index on col2 by scanning all rows with the prefix "ABC".

[0147] In contrast, col3>5 can also be resolved by performing a range scan using the index on col3, however, all matching rows do not need to have a common prefix, and therefore, col3>5 is not an index prefix.

[0148] The table join operation calculates a subset of the Cartesian product of two or more tables (relations), so it can be considered as a selection operation on the cross product of two or more tables. For most database execution engines, only two tables are joined together at a time, and the order in which the tables are joined plays an important role in determining the performance of the join query. Determining the most effective join order for a query is itself an NP-hard problem. There is a circular dependency between selecting the most effective index to facilitate the join query and determining the best join order. Most relational database engines use models to predict effective join orders rather than calculating the most effective join order. The purpose is to prevent query optimization from becoming more costly than the actual execution. Therefore, the optimizer will only consider a portion of the join order.

[0149] This embodiment can generate candidate indexes for join queries, which is very effective for real workloads. The columns use metadata, including identifiers of table instances that participate in the join condition of any given column of another table instance. This helps build a join graph, where the nodes are table instances and the edges between them represent join conditions involving columns from participating table instances (nodes), such as Figure 2 As shown, the following is the connection diagram for query Q2.

[0150] Q2:SELECT t1.col1,t2.col2,t3.col3

[0151] FROM t1,t2,t3

[0152] WHERE t1.col2=t3.col2

[0153] AND t2.col4=t3.col7

[0154] IMS uses a positive integer j to limit its search for the join order. All table instances with columns participating in the join decision function are selected together with at most j other table instances. The candidate indexes of the selected table instances include all possibilities for the join order of the table. The upper bound is exponential in j because each participating table can precede or inherit the selected table instance. Therefore, the value of j is usually a small positive integer. Algorithm 3 is shown in Table 5.

[0155] Table 5: Algorithm 3 connection algorithm

[0156]

[0157] If the join order is predetermined, the join decision function can be combined with the selection decision function to produce a partial order representing the candidate indexes, which has gains for both join and selection operations. Intuitively, if we assume that the join order of query Q2 above is [t3, t1, t2], then it makes sense to try the index on t2.col4 because t3 comes before it, and perform a lookup of table t2 for each value of t3.col7. However, if the join order is [t1, t2, t3], the index on t2.col4 will not reduce the number of records considered starting from t2.

[0158] This embodiment considers candidate indexes, promotes several potential join orders, and lets the query optimizer select the best one. Algorithm 4, as shown in Table 6, provides pseudo code for generating index candidates that satisfy selection and join. The procedure ReferencedColumns is an overloaded helper that returns the columns referenced in an entity, which is a table in a query.

[0159] Table 6: Algorithm 4 generates alternative candidate algorithms

[0160]

[0161]

[0162] It is important to note that the methods used by most modern index selection algorithms will not be able to discover the optimal index for complex join queries because their search logic does not consider coordinating the search for candidate indexes across multiple tables.

[0163] Grouping, the GROUP BY statement groups the results according to the values ​​of one or more columns. Typically, grouping is used to apply some kind of aggregate function to each group.

[0164] If you use a group by operation on a table instance, secondary indexes can come in handy when reading rows in grouped order and computing any expressions involving aggregates for each group, as in the following example:

[0165] Q3:SELECT col3,COUNT(*)FROM t1

[0166] GROUP BY col3

[0167] Q4:SELECT col3,SUM(col1)

[0168] FROM t1 WHERE col2=5GROUP BY col3

[0169] For query Q3, a secondary index on col3 may come in handy when computing the aggregate value for each group (defined by the unique values ​​of col3). However, for query Q4, the following partial ordering will be more efficient because it forms a covering index that facilitates both selection and grouping operations: <{col2}, {col3}, {col1}>. Note that (col2) precedes the grouping column (col3) because it appears in the index prefix defined above. Algorithm 6 describes the candidate generation for the group by clause as shown in Table 7.

[0170] Table 7: Algorithm 6 generates grouping candidates

[0171]

[0172]

[0173] The ORDER BY clause specifies that a SELECT statement returns a result set sorted by the values ​​of one or more columns.

[0174] A query might request only a few rows in the output sorted in a specific order. Consider the following query:

[0175] Q5:SELECT t1.col13,t1.col14,

[0176] t2.col25,t1.col17

[0177] FROM t1 LEFT JOIN t2

[0178] ON t1.col11=t2.col21

[0179] WHERE t1.col12 IN('ABC','DEF')

[0180] ORDER BY t1.col13 LIMIT 2

[0181] The number of rows in t1 that match t1.col12 IN('ABC', 'DEF') may be too high. A more efficient execution plan may read the rows of t1 in ascending order of col13 and check whether t1.col 12 IN('ABC', 'DEF') satisfies it. In this case, the secondary index on col13 is more effective than the secondary index on col12. The candidates for the sort clause are generated by Algorithm 7 as shown in Table 8.

[0182] Table 8: Algorithm 7 for generating permutation candidates

[0183]

[0184]

[0185] It should be noted that when merging candidate indexes, the gains corresponding to a single query will be superimposed, and the write cost will increase, which needs to be calculated and compared with the cost when merging a larger candidate index. Through step S300, the index candidates are evaluated according to the overall utility per unit storage cost.

[0186] Preferably, in step S300, sorting and evaluating the usefulness of the candidate index set includes:

[0187] The utility of the index is calculated by summarizing the gain cost of a single query using the index and counting the writes generated by index maintenance, expressed as:

[0188] u(i)=s i,q ·U + (q,i)+u - (i)

[0189] Among them, s i,q represents the share of the gain of index i, U + (q,i) represents the gain, u - (i) represents the maintenance cost of a single index i.

[0190] The cost of the benefit for a single query using an index is summarized as follows:

[0191] The computational cost of query q is decomposed into two mPain components, expressed as:

[0192]

[0193] Among them, cost r (q,OC), represents the computational cost of locating records using the index in configuration X, cost u (q,i) represents the computational cost of updating index i in OC, cost u (q,i) is non-zero only for DML statements. The cost estimation logic depends on the storage engine and can use the statistics provided by the dataless index.

[0194] The two main ways of index query are to reduce the amount of data that needs to be read and to prevent repeated sorting operations. To quantify this gain cost, we first isolate each query q. The gain cost U + It can be estimated by the following formula, U + distributed among the indices (in I) used during the execution of q, attributed to U at index i + The share si,q is proportional to the i / O reduction caused by i (estimated by the optimizer);

[0195] Quantify the gain cost of index query, isolate each query q, and calculate the gain cost U of index query + , expressed as:

[0196]

[0197] Where L represents the candidate set generated for query q, represents the computational cost of querying q without a candidate set, It represents the average CPU utilization of the common query q in the time span Δt without the candidate set.

[0198] Preferably, collecting statistics on writes generated by index maintenance includes:

[0199] The maintenance cost is calculated based on the index update and the required storage space. The cost of index maintenance is calculated based on a single index i and is expressed as:

[0200]

[0201] Among them, cost u (q,i) represents the computational cost of querying q under a single index i. W represents the workload. High-frequency I / O operations will increase CPU utilization. The cpuavg index includes CPU IOWAIT events.

[0202] It should be noted that the consideration of index interactions is limited to the example partial order of merge candidate indexes. When merging candidate indexes, the gains corresponding to a single query are additive, and the write amplification cost is the same as the merged larger candidate index. Index selection can be modeled as a knapsack problem, where candidate indexes are evaluated in the order of their overall utility per unit storage cost while not violating the preset computational cost assigned to the index.

[0203] The present invention proposes an index management solution IMS for relational databases, which is suitable for workloads in production and can be expanded according to increasingly complex workloads. IMS makes trade-offs in solution granularity to provide lower running time and uses query structures to strictly limit the search space, which can prevent the algorithm from searching too many index configurations. It can support the following levels of solution granularity.

[0204] (1) Only a subset of queries can be selected for optimization, and only the first few queries account for most of the CPU utilization.

[0205] (2) At the query level, each table instance in a multi-table query has three options: it can be without index, with non-covering index, or with covering index.

[0206] (3) Relaxing / reducing the number of in-index prefixes (IPPs) can also significantly reduce computational cost, especially when the additive selection is below a certain threshold.

[0207] The IMS algorithm proposes a new method to search the candidate index search space and handles complex join queries. In addition, it also makes a comprehensive design to deal with fast convergence and reduce the solution search granularity.

[0208] The above is a schematic scheme of a database index management method of this embodiment. It should be noted that the technical scheme of the database index management system and the technical scheme of the above database index management method belong to the same concept, and the details of the technical scheme of the database index management system in this embodiment that are not described in detail can all be referred to the description of the technical scheme of the above database index management method.

[0209] The database index management system in this embodiment includes:

[0210] The identification module is used to select sample workloads and identify query sets that need to be tuned by monitoring query execution statistics;

[0211] A generation module, used for generating a candidate index set based on each identified query set, and determining a relative join order of the candidate index set through a join parameter;

[0212] The evaluation module is used to determine the production index set by ranking and evaluating the usefulness of the candidate index set, and to materialize the candidate index set on the database.

[0213] This embodiment also provides an electronic device, which is applicable to database index management, including:

[0214] Memory and processor; the memory is used to store computer executable instructions, and the processor is used to execute computer executable instructions to implement the database index management method proposed in the above embodiment.

[0215] This embodiment also provides a storage medium on which a computer program is stored. When the program is executed by a processor, the method for implementing database index management as proposed in the above embodiment is implemented.

[0216] The storage medium proposed in this embodiment and the method for implementing database index management proposed in the above embodiment belong to the same inventive concept. The technical details not fully described in this embodiment can be referred to the above embodiment, and this embodiment has the same beneficial effects as the above embodiment.

[0217] Through the above description of the implementation methods, the technicians in the relevant field can clearly understand that the present invention can be implemented by means of software and necessary general hardware, and of course can also be implemented by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art can be embodied in the form of a software product, and the computer software product can be stored in a computer-readable storage medium, such as a computer floppy disk, read-only memory (ReadOnly, Memory, ROM), random access memory (RandomAccess Memory, RAM), flash memory (FLASH), hard disk or optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, server, or network device, etc.) to perform the methods of various embodiments of the present invention.

[0218] Example 2

[0219] Referring to Table 9, an embodiment of the present invention provides a database index management method. In order to verify its beneficial effects, scientific demonstration is carried out through economic benefit calculation and simulation experiments.

[0220] IMS is deployed to database workloads. The workloads generated by these applications are different. The databases are dynamically updated. In order to compare IMS with traditional manual tuning, the workloads are simulated and tested. All secondary indexes need to be deleted in the test, and IMS is allowed to start adding them after analyzing the workload. From the simulation results, IMS uses fewer index resources in most cases and achieves performance comparable to multiple manual tunings. The representative workload is provided in Table 9 below.

[0221] Table 9: Workload data

[0222]

[0223] As can be seen from the table, IMS can achieve performance comparable to that of a manually tuned database and also has an advantage when compared to databases with a database administrator.

[0224] It should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit the present invention. Although the present invention has been described in detail with reference to the preferred embodiments, those skilled in the art should understand that the technical solutions of the present invention may be modified or replaced by equivalents without departing from the spirit and scope of the technical solutions of the present invention, which should all be included in the scope of the claims of the present invention.

Claims

1. A database index management method, characterized in that: Including: Selecting sample workloads by monitoring the execution statistics of queries, and identifying query sets that need to be tuned; Based on each identified query set, generating a candidate index set, and determining the relative join order of the candidate index set through join parameters; By sorting and evaluating the usefulness of the candidate index set, determining a production index set, and materializing the candidate index set on the database.

2. The database index management method according to claim 1, characterized in that: The identifying the query sets that need to be tuned includes: Analyzing the execution statistics of the queries to identify whether there are queries with effective executions; Among them, the execution statistics include CPU computing cost, number of rows read, number of rows sent, and number of executions corresponding to each normalized query; Based on querying the execution statistics, by identifying queries that need to be tuned, selecting a sample workload according to preset attributes, and the preset attributes include execution frequency, average CPU consumption per execution, and discarded data ratio; Clearing false query executions from a specific source according to the threshold of the execution frequency; According to the average CPU consumption and discarded data ratio ddr avg , the storage expected SB allocated by optimizing the normalized query q executed using the configured OC is expressed as: SB(q,OC,Δt)=(1-ddr avg (q,OC,Δt))*cpu avg (q,OC,Δt) Where SB(q,OC,Δt) represents the storage budget of the standardized query q in the time span Δt, cpu avg (q,OC,Δt) represents the average CPU utilization of the standardized query q within the time span Δt, ddr avg (q,OC,Δt) represents the discarded data ratio of the normalized query q within the time span Δt.

3. The database index management method according to claim 2, characterized in that: The generating the candidate index set includes: Taking the workload and join parameter j as inputs, for each query Q in the workload, checking whether the covering index is effective; Generating a set of partial orders of index columns through an optional query execution strategy, and the execution strategy is based on the main operation that is optimal for the query; Merging the partial orders to obtain a final partial order, and for each partial order, generating a candidate index set by arbitrarily selecting a total order of index columns that satisfy the partial order.

4. The database index management method according to claim 3, characterized in that: The merging of the partial orders includes: Defining the function MergeCandidatesPairwise, which takes two strict partial orders (P, <P) and (Q, <Q), and generates another strict partial order (R, <R) when the precondition Cmerge is satisfied, expressed as: In C merge The strict partial order (R, <R) is represented as: In other cases, the strict partial order (R, <R) is expressed as: Among them, P, Q, and R represent different subsets of the same table column, and C merge Indicates the prerequisites for the MergeCandidatesPairwise function to be used. represents an empty set, a and b represent columns in the table, aPb, aQb, and aRb represent relations, Represents ordinals and operators. Generating a set of partial orders of index column PO through the function MergePartialOrders, and recursively merging the partial orders pairwise until no new candidates are generated, expressed as: PO n+1 :={MergeCandidatesPairwise(X,Y)|X,Y∈PO n } Among them, X and Y represent the partial orders in POn; Merge PO, starting with PO0:=PO, and use the function MergePartialOrders to return PO m , namely PO m =PO m+1 .

5. The database index management method according to claim 4, characterized in that: The sorting and evaluating the usefulness of the candidate index set includes: By summarizing the gain costs generated by a single query using the index and counting the writes generated by index maintenance, calculating the utility of the index, expressed as: u(i)=s i,q ·U+(q,i)+u - (i) Among them, s i,q represents the share of the gain of index i, U + (q,i) represents the gain, u - (i) represents the maintenance cost of a single index i.

6. The database index management method according to claim 5, characterized in that: The summarizing the gain costs generated by a single query using the index includes: Decomposing the computing cost of query q into two mPain components, expressed as: Among them, cost r (q,OC) represents the computational cost of locating records using the index in configuration X, cost u (q,i) represents the computational cost of updating index i in OC; Quantify the gain cost of index query, isolate each query q, and calculate the gain cost U of index query + , expressed as: Where L represents the candidate set generated for query q, represents the computational cost of querying q without a candidate set, It represents the average CPU utilization of the common query q in the time span Δt without the candidate set.

7. The database index management method according to any one of claims 4 to 6, characterized in that: The counting the writes generated by index maintenance includes: Calculating the maintenance cost according to index updates and required storage space, and calculating the cost of index maintenance based on a single index i, expressed as: Among them, cost u (q,i) represents the computational cost of querying q under a single index i, and W represents the workload.

8. A database index management system, characterized in that: Including, An identification module for selecting sample workloads by monitoring the execution statistics of queries and identifying query sets that need to be tuned; A generation module for generating a candidate index set based on each identified query set and determining the relative join order of the candidate index set through join parameters; An evaluation module is used to determine a production index set by ranking and evaluating the usefulness of the candidate index set, and to materialize the candidate index set on a database.

9. An electronic device, characterized in that: include: Memory and processor; The memory is used to store computer executable instructions, and the processor is used to execute the computer executable instructions. When the computer executable instructions are executed by the processor, the steps of the database index management method described in any one of claims 1 to 7 are implemented.

10. A computer-readable storage medium, characterized in that: It stores computer executable instructions, which, when executed by a processor, implement the steps of the database index management method described in any one of claims 1 to 7.

Images