Optimized query method and device, equipment, medium and product

Abstract

The invention discloses an optimization query method and device, equipment, a medium and a product. The optimization query method comprises the following steps: obtaining an original query statement and initializing a related variable; determining a copy of the target table according to the original query statement and the initialized related variable; connecting the copies to generate an optimal query plan of the target table, and replacing an original query plan in the original query statement to obtain a final query plan; and querying according to the final query plan to obtain a query result. According to the method, the table copies are generated according to the columns of the target table used in the query and the index of the target table, and the table copies are combined and connected to obtain the table copy combination which has the minimum cost and can just cover the columns used in the query as the optimal query plan, so that the purposes that a return table does not need to be used and all data are directly obtained from the index are achieved, and the query efficiency is improved. And a large amount of physical reading and logic reading caused by table returning are reduced, so that the overall query performance is improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and particularly relates to an optimized query method, device, equipment, medium and product. BACKGROUND

[0002] In a database, a secondary index, also known as a non-clustered index or a secondary index, is an index created in addition to a primary index, and is used to improve the query performance of a non-primary key column. It is opposite to a primary index, which is usually an index created for a primary key column in a table, and is used to uniquely identify each row of data. A secondary index usually contains an index column and a pointer to a data row, and can quickly locate the data row through the index column.

[0003] Generally, a table can only select one secondary index for a query. However, when a table is queried using a secondary index, if the query statement also involves columns other than the secondary index, the entire query cannot be completed only through the secondary index, and it is also necessary to find complete data records on the cluster index according to the cluster key or row identifier rowid on the secondary index. This process is called back to the table.

[0004] When using a conventional method for single-table query, accessing the table will go through a single index plus back to the table, which will cause a large number of physical and logical reads, resulting in slow query efficiency. SUMMARY

[0005] The present application provides an optimized query method, device, equipment, medium and product, so as to realize data query without using back to the table, and improve the overall query performance.

[0006] According to a first aspect of the present application, an optimized query method is provided, comprising:

[0007] obtaining an original query statement and initializing related variables;

[0008] determining a copy of a target table according to the original query statement and the initialized related variables;

[0009] connecting the copy to generate an optimal query plan of the target table, and replacing an original query plan in the original query statement to obtain a final query plan;

[0010] querying according to the final query plan to obtain a query result.

[0011] According to another aspect of the present application, an optimized query device is provided, comprising:

[0012] a statement obtaining module configured to obtain an original query statement and initialize related variables;

[0013] A copy determining module is configured to determine a copy of the target table according to the original query statement and the initialized related variables;

[0014] A plan replacing module is configured to connect the copy, generate an optimal query plan of the target table, and replace an original query plan in the original query statement to obtain a final query plan;

[0015] A result determining module is configured to perform a query according to the final query plan to obtain a query result. According to a third aspect of the present application, an electronic device is provided, and the electronic device comprises:

[0016] at least one processor; and

[0017] a memory connected with the at least one processor; wherein

[0018] The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor to enable the at least one processor to execute the optimization query method according to any one of the embodiments of the present application.

[0019] According to a fourth aspect of the present application, a computer readable storage medium is provided, and the computer readable storage medium stores computer instructions for enabling a processor to implement the optimization query method according to any one of the embodiments of the present application when the processor executes the computer instructions.

[0020] According to a fifth aspect of the present application, the embodiments of the present application further provide a computer program product, and the computer program product comprises a computer program, and the computer program implements the optimization query method according to any one of the embodiments of the present application when the computer program is executed by a processor.

[0021] The technical solution of the embodiments of the present application obtains an original query statement and initializes related variables; determines a copy of a target table according to the original query statement and the initialized related variables; connects the copy, generates an optimal query plan of the target table, replaces an original query plan in the original query statement, and obtains a final query plan; and performs a query according to the final query plan to obtain a query result. The table copy is generated according to the columns of the target table and the indexes of the target table used in the query, the table copy is combined and connected, the table copy combination with the minimum cost and covering exactly the columns used in the query is obtained as the optimal query plan, the purpose of not using a back table and obtaining all data directly from the index is achieved, a large number of physical reads and logical reads caused by the back table are reduced, and the overall query performance is improved.

[0022] It is to be understood that the embodiments described herein are merely exemplary of the application and that a person skilled in the art can devise other embodiments without departing from the scope of the present application. It is also to be understood that not all of the features and / or benefits described and / or illustrated herein need be present in every embodiment of the application. The scope of the embodiments described and / or illustrated herein is limited only by the claims. BRIEF DESCRIPTION OF DRAWINGS

[0023] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings needed in the embodiments description will be briefly introduced as follows. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without any creative effort based on these drawings.

[0024] Figure 1 is a flow chart of an optimization query method according to an embodiment of the present application;

[0025] Figure 2 is a flow chart of an optimization query method according to an embodiment of the present application;

[0026] Figure 3 is an example flow chart of an optimization query method according to an embodiment of the present application;

[0027] Figure 4 is a structural schematic diagram of an optimization query device according to an embodiment of the present application;

[0028] Figure 5 is a structural schematic diagram of an electronic device for implementing an embodiment of the present application. DETAILED DESCRIPTION

[0029] In order to make the technical personnel in the art better understand the present application, the technical solutions in the embodiments of the present application will be described clearly and completely in the following with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only some of the embodiments of the present application, not all. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without any creative effort should be within the scope of protection of the present application.

[0030] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0031] Example 1

[0032] Figure 1 This is a flowchart of an optimized query method provided in Embodiment 1 of the present invention. This embodiment is applicable to queries involving multiple secondary indexes in a single table. The method can be executed by an optimized query device, which can be implemented in hardware and / or software and can be configured in an electronic device. Figure 1 As shown, the method includes:

[0033] S110. Obtain the original query statement and initialize the relevant variables.

[0034] In this embodiment, the original query statement can be understood as the statement entered by the user for querying. Related variables can be understood as variables set to optimize the query statement.

[0035] Specifically, when the processor receives the original query statement input by the user, it first initializes the relevant variables for the query.

[0036] Exemplarily, the relevant variables can include 6, for example, can be: 1) col_barr of the column: byte type array, recording all columns of the target table (hereinafter referred to as T1 table) used in the query. 2) pred_2_col_barr of the predicate and column mapping array: map type, the key corresponds to the predicate (must be the condition after where, cannot be the condition after on), and the value is the covered column array of the condition after where (the array type and each item meaning are the same as col_barr). Initialize to empty; 3) index_2_col_barr of the index and column mapping array: map type, the key corresponds to the secondary index, and the value is the covered column array of the corresponding secondary index (that is, the column in the table T1 included in the index, the array type and each item meaning are the same as col_barr), initialize to empty; 4) tab_2_col_barr of the table and column mapping array: map type, the key corresponds to the table copy generated according to the index, and the value corresponds to the covered column array of the table copy (the array type and each item meaning are the same as col_barr). Initialize to empty; 5) plan0 of the original plan: initialize to the plan to be optimized; 6) plan1 of the optimized plan: initialize to empty, and subsequently store the optimized plan.

[0037] S120, determining the copy of the target table according to the original query statement and the initialized relevant variables.

[0038] In the embodiment, the target table can be understood as the table to be queried in the original query statement. The copy can be understood as a new table created by copying the original table structure and data.

[0039] Specifically, the processor can select n indexes according to the columns used by the table T1 in the query, the condition (wherein the selected index can cover the condition and the column assigned thereto), and generate n copies for the table T1, each copy applying an index and being responsible for the output of a part of the upper layer column.

[0040] S130, connecting the copies to generate the optimal query plan of the target table, and replacing the original query plan in the original query statement to obtain the final query plan.

[0041] In the embodiment, the optimal query plan can be understood as the optimized query plan without the need for back table, for example, forming the optimal query plan through hash connection. The original query plan can be understood as the query plan constituted by the query condition in the original query statement.

[0042] Specifically, the processor can combine each copy of the plurality of copies with a row identifier column of the copy by different combination manners to form a hash join, and select a new generated hash join with a query cost less than a cost of the target table to replace an original query plan in the original query statement with the new generated hash join to obtain a final query plan.

[0043] S140, querying according to the final query plan to obtain a query result.

[0044] In the embodiment, the query result can be understood as a data result obtained after querying.

[0045] Specifically, the processor can perform multi-index scanning on a single target table based on the final query plan, and obtain data by directly scanning the index to form the query result.

[0046] The technical scheme of the embodiment of the application comprises the following steps: obtaining an original query statement and initializing related variables; determining a copy of a target table according to the original query statement and the initialized related variables; connecting the copy to generate an optimal query plan of the target table, and replacing an original query plan in the original query statement to obtain a final query plan; and querying according to the final query plan to obtain a query result. The table copy is generated according to the column of the target table used in the query and the index of the target table, the table copy is combined and connected, the table copy combination with the minimum cost and capable of covering the column used in the query is obtained as the optimal query plan, the purpose of not using a back table and all data being directly obtained from the index is achieved, a large amount of physical reading and logical reading caused by the back table is reduced, and the overall query performance is improved.

[0047] Embodiment two

[0048] Figure 2 A flowchart of an optimization query method provided by the embodiment two of the application, and the embodiment is a further refinement of the above-mentioned embodiments. As shown in the figure, the method comprises the following steps. Figure 2

[0049] S201, obtaining an original query statement and initializing related variables.

[0050] S202, determining a bit array of a column according to a column number and a corresponding column of a target table in the original query statement.

[0051] ​In the embodiment, the column number can be understood as a number set for distinguishing each column in the target table. The column bit array can be understood as a bit array for recording all columns of the target table used in the query, wherein the subscript i of the array represents the column number (for example, it can be represented by COLID, and the rightmost is 0), and the i-th item in the array is 0, indicating that the column with COLID i is not used in the query, and the i-th item in the array is 1, indicating that the column with COLID i is used in the query. Each item is initialized to 0.

[0052] Specifically, the processor can analyze the columns of the target table used in the original query statement, and the processor can record according to the column numbers to obtain the column bit array of the columns used in the original query statement. For example, when creating a table, the maximum number of columns defined is 2048 columns. Because each column represents a COLID and each column has a number, the array col_barr is the number of the array itself, and the array is 0, which marks the presence of a total of 2048 (8*256) bits of columns, that is, the column bit array col_barr records 256 items at most (each item is a byte type data, and each byte type data has 8 bits). For other virtual columns (system generated columns, not columns specified to be generated when creating a table) created on the table, the present application only processes rowid, whose COLID is 65535 (because the table must have the identifier of the rowid row data, the COLID does not need to be stored in the column bit array (represented by col_barr later), but can be directly taken from the table), and the present application does not process other virtual columns (for example, the object_id column, whose COLID is 65522, and the current optimization does not support processing).

[0053] For example, the query statement involves 4 columns c1, c2, c3 and c4 in the 5 columns of the T1 table, and the corresponding COLIDs are 0, 1, 2 and 3 respectively, so the numbers of c1, c2, c3 and c4 can be 00000001, 00000010, 00000100 and 00001000 respectively.

[0054] S203, determining a first mapping array according to the target condition in the original query statement and the column bit array, the first mapping array being an array containing the mapping relationship between the sub-conditions and the first cover column array generated based on the sub-conditions.

[0055] In the embodiment, the target condition can be understood as a condition for performing a query, for example, can be select c4from t1 where c1= 'AAA1' and c2= 'AAA2' and c2||c3= 'AA2AAA3'. The first mapping array can be understood as an array for establishing a mapping relationship between each sub-condition in the target condition and a first cover column array, wherein the cover column array refers to a cover column represented in the form of an array, and the cover column refers to a plurality of columns involved in the condition, and the first cover column array corresponds to each sub-condition.

[0056] Specifically, the processor can first determine the sub-conditions involved in the target condition in the original query statement, generate a corresponding first cover column array for the column combined with the bit array of the column corresponding to the sub-condition after the key clause for filtering data in the target condition, for example, the key clause can be a where condition to specify data query or operation.

[0057] Further, on the basis of the above embodiment, the step of determining the first mapping array according to the target condition in the original query statement and the bit array of the column can be refined as:

[0058] Filtering conditions in the original query statement are selected as the target condition; for the data column specified by each sub-condition in the target condition, the first cover column array of the sub-condition is determined according to the bit array of the column of the data column; the first mapping array is determined according to the sub-condition and the first cover column array.

[0059] In the embodiment, the filtering condition can be understood as a statement for representing multi-condition filtering of data in the target table, that is, the filtering condition represented by where in the original query statement, for example, a statement containing an and conjunction word, because a single condition (without and connection) does not need to apply multi-index scan optimization, and one index can be used; other cases using or connection of multiple conditions cannot directly push the optimization to the index, and cannot utilize the characteristics of the index itself to achieve optimization effect, and there is no need to optimize.

[0060] Specifically, the processor can select the condition statement containing the conjunction word from the original query statement as the target condition. The processor can generate a first cover column array of each sub-condition connected by the conjunction word in the target condition according to the bit array of the column of the data column specified by the sub-condition, and determine the first mapping array according to the sub-condition and the first cover column array.

[0061] For example, the processor can determine the sub-conditions connected by and after where, i.e., c1= 'AAA1', c2= 'AAA2', and c2||c3= 'AA2AAA3', according to the above target condition. The processor can determine the corresponding first cover column array for each sub-condition. For example, the first cover column array corresponding to the sub-condition c1= 'AAA1' is 00000001, the first cover column array corresponding to the sub-condition c2= 'AAA2' is 00000010, and the first cover column array corresponding to the sub-condition c2||c3= 'AA2AAA3' is 00000110. The processor can store the sub-condition and the first cover column array in the first mapping array (pred_2_col_barr) in the form of <sub-condition, first cover column array>, where the condition is each item connected by and.

[0062] In the embodiment, the index involved in the target table can be understood as all available secondary indexes on the target table. The candidate index can be understood as the index after the screening. The second mapping array can be understood as the mapping relationship between the candidate index and the corresponding second cover column array. For example, the second mapping array can be of the map type, the key corresponds to the table copy generated according to the candidate index, and the value corresponds to the second cover column array of the table copy (the array type and the meaning of each item are the same as col_barr).

[0063] In the embodiment, the index involved in the target table can be understood as all available secondary indexes on the target table. The candidate index can be understood as the index after the screening. The second mapping array can be understood as the mapping relationship between the candidate index and the corresponding second cover column array. For example, the second mapping array can be of the map type, the key corresponds to the table copy generated according to the candidate index, and the value corresponds to the second cover column array of the table copy (the array type and the meaning of each item are the same as col_barr).

[0064] Specifically, the processor can determine the cover column array corresponding to each index according to the target table and the index involved in the target table in the original query statement. First, the processor can establish the mapping relationship between each index and the cover column array. Then, the processor can remove the duplicate indexes in the same index from the cover column array corresponding to the index, and the remaining index and the corresponding second cover column array are the candidate index and the second cover column array, respectively. Thus, the processor can determine the second mapping array.

[0065] Further, based on the above embodiment, the processor can further refine the step of determining the second mapping array according to the target table and the index involved in the target table in the original query statement as follows:

[0066] The processor can generate the corresponding intermediate cover column array for all available secondary indexes in the target table according to the index involved in the target table in the original query statement. The processor can determine the intermediate mapping array according to the secondary index and the intermediate cover column array. The processor can screen the secondary indexes with the same intermediate cover column array in the intermediate mapping array, and determine the second mapping array after the screening.

[0067] In the embodiment, all available secondary indexes can be understood as all non-primary key indexes that have been created in the target table and can be used by the query optimizer, and whether the secondary indexes are available can be determined by the identification in the indexes. The availability of the secondary indexes also depends on the query condition. The intermediate cover column array can be understood as the cover column array corresponding to the index column mentioned in all indexes involved in the target table. The intermediate mapping array can be understood as the array formed by the mapping relationship between all secondary indexes and the intermediate cover column array.

[0068] Specifically, the processor can first generate the corresponding intermediate cover column array for all available secondary indexes in the target table according to the indexes involved in the original query statement of the target table, and determine the intermediate mapping array according to the secondary indexes and the intermediate cover column array. The processor can filter the secondary indexes with the same intermediate cover column array in the intermediate mapping array, judge the cost of the same secondary indexes, retain the secondary indexes with low cost, and delete the secondary indexes with high cost, so as to determine the second mapping array after filtering.

[0069] For example, a specific example is shown, and the indexes involved in the target table can be: IDX C1 (C1),

[0070] IDX C2 (C2), IDX C3 (C3), IDX C4 (C4), IDX C1 C2 (C1, C2), IDX C2 C3 (C2, C3),

[0071] IDX_C3_C4(C3,C4), IDX_C1_C3_C4(C1,C3,C4), IDX_ID_C3_C4(ID,C3,C4), wherein the bracket is the column contained in the index, and IDX_XXX_YYY represents the index name. The intermediate cover column array corresponding to each index (for example, which can be represented by index_2_col_barr) can be represented as follows: the intermediate cover column array corresponding to IDX_C1(C1) is 00000001; the intermediate cover column array corresponding to IDX_C2(C2) is 00000010; the intermediate cover column array corresponding to IDX_C3(C3) is 00000100; the intermediate cover column array corresponding to IDX_C4(C4) is 00001000; the intermediate cover column array corresponding to IDX_C1_C2(C1,C2) is 00000011; the intermediate cover column array corresponding to IDX_C2_C3(C2,C3) is 00000110; the intermediate cover column array corresponding to IDX_C3_C4(C3,C4) is 00001100; the intermediate cover column array corresponding to IDX_C1_C3_C4(C1,C3,C4) is 00001101; and the intermediate cover column array corresponding to IDX_ID_C3_C4(ID,C3,C4) is 00001100 (wherein the ID column does not appear in the query, and thus is not included in the intermediate cover column array). As can be seen from the above example, the secondary indexes with the same intermediate cover column array are IDX_C3_C4(C3,C4) and IDX_ID_C3_C4(ID,C3,C4), and it can be seen that both indexes involve columns C3 and C4, but IDX_C3_C4 involves fewer columns, and thus IDX_C3_C4 is finally retained, and the final second mapping array is the intermediate mapping array that does not include the IDX_ID_C3_C4(ID,C3,C4) item, which can be stored in the index_2_col_barr in the form of <index, cover column array>.

[0072] In S205, a copy of the target table and a third mapping array are generated according to the first mapping array and the second mapping array, and the third mapping array includes an array of mapping relationships between each table copy in the copy and the corresponding third cover column array.

[0073] In this embodiment, the table copy can be understood as a copy of all columns used by the query. The third mapping array is used to record the mapping relationship between all table copies and the corresponding third cover column array.

[0074] Specifically, the processor can generate a corresponding table copy according to each candidate index involved in the second mapping array, determine the condition that can be covered by the candidate index through the first mapping array, assign the condition to the table copy, determine the third cover column corresponding to the table copy, and establish a mapping relationship between each table copy and the third cover column to obtain the third cover column array of the target table.

[0075] Further, on the basis of the above embodiment, the step of generating a copy of the target table and the third mapping array according to the first mapping array and the second mapping array can be refined as:

[0076] For each candidate index in the second mapping array, a table copy of the target table is generated for the candidate index; the condition covered by the candidate index is determined based on the first mapping array, and the internal structure attribute of the table copy corresponding to the candidate index is determined; a copy of the target table is formed according to all table copies; and the third mapping array is determined according to the table copy and the corresponding third cover column array.

[0077] In this embodiment, the internal structure attribute can be understood as an attribute for indicating the structure of the table copy.

[0078] Specifically, for each candidate index in the second mapping array, a table copy of the target table is generated for the candidate index, that is, each candidate index corresponds to a table copy. The processor can determine the condition covered by the candidate index based on the first mapping array, and the processor can jointly determine the internal structure attribute of the table copy corresponding to the candidate index as the candidate index and the condition. The processor can form a copy of the target table according to all table copies; and determine the third mapping array according to the table copy and the corresponding third cover column array.

[0079] For example, a table copy of the target table is generated for each candidate index in the second cover column array index_2_col_barr, one table copy corresponding to one candidate index, a plurality of table copies are obtained by copying, and the condition that can be covered by each candidate index is assigned to the table copy corresponding to the candidate index through the first cover column array pred_2_col_barr, wherein the third cover column array of the candidate index contains the cover column array of the condition, indicating that the index can cover the condition. In the form of <table copy, cover column array>, it is stored in tab_2_col_barr, wherein the candidate index and the condition assigned on the table copy are recorded on the internal structure attribute of the table copy. In this example, the table copy corresponding to the candidate index and the condition assigned on the table copy can be represented by the following table:

[0080] Table 1: Third cover column array example:

[0081]

[0082] S206, determine a table replica combination with minimum query cost of scanning the target table according to the cost of each table replica in the replica.

[0083] In the embodiment, the query cost can be understood as the system resource and time cost consumed by executing the query, which is used to evaluate the efficiency of the query execution plan. The table replica combination can be understood as the table replica after the hash join.

[0084] Specifically, the processor can estimate the query cost of each table replica combination according to the predicate (i.e. condition) corresponding to each table replica. The processor can determine the table replica combination with minimum query cost of scanning the target table based on the greedy algorithm.

[0085] For example, there are four table replicas A, B, C and D, and the cost of each table replica from small to large is A, B, C and D. First, select the table A with minimum cost, and then try to combine A with B, C and D one by one. If there is cost(A, C) < cost(A, B) < cost(A, D), select A and C to perform left join first. Similarly, try to combine (A, C) with B and D in turn until the column of the obtained table combination covers the coverage column array of col_barr, and then stop selecting.

[0086] S207, determine the optimal combination and the corresponding join operation according to the fourth coverage column array corresponding to each table replica in the table replica combination.

[0087] In the embodiment, the join operation can be understood as an operation for combining data in multiple tables according to certain conditions, such as join (table replica A, table replica B). The optimal combination can be understood as a table replica combination for filtering the table replica combination with minimum cost and covering the bit array of the column.

[0088] Specifically, each table replica in the table replica combination has a corresponding fourth coverage column array. The processor can perform the join operation based on the table replica in the table replica combination, and combine the multiple fourth coverage column arrays after the join. If the intersection of the combined fourth coverage column array and the bit array of the column is still the bit array of the column, it means that it covers exactly. The first join operation that can cover exactly is the final join operation.

[0089] For example, if the optimal combination is determined to be join of table replicas A and B according to the query cost, the cover column array corresponding to the join(A, B) is the cover column array of table replica A and the cover column array of table replica B. The same principle applies to the addition of new table replicas. The cover column array corresponding to the newly generated join operation join is the cover column array of the current join and the cover column array of the newly added table. For the newly generated join, if the intersection of the cover column array and col_barr is still col_barr (i.e. equivalent), it indicates that it can be exactly covered. The first join operation join that can be exactly covered is found, and the result is returned.

[0090] In S208, a hash join condition of the join operation is generated according to the row identifier column of each table replica in the table replica combination, and an optimal query plan of a table replica with the minimum cost is determined.

[0091] In this embodiment, the row identifier column can be understood as a unique identifier for representing each row of data.

[0092] Specifically, the processor can generate a hash join condition between table replicas according to the row identifier column of each table replica in the table replica combination, i.e. the same row identifier column of two table replicas to be joined as the hash join condition, obtain an optimized query plan, calculate the query cost of the original query plan and the query cost of the optimized query plan through the optimizer respectively, and take the query plan with the minimum query cost as the optimal query plan. If the cost of the original query plan is smaller, the original query plan is not changed. If the cost of the optimized query plan is smaller, the position of the original query plan in the overall plan is replaced by the optimized query plan to obtain a final query plan.

[0093] Exemplarily, a manner of generating a join condition according to rowid is that: there is rowid on each table copy, if it is determined that two table copies A and B are combined into a hash join, a join condition of A.ROWID=B.ROWID is generated. In the example, the original query plan is plan0, the hash join of the table copies corresponds to the optimized query plan plan1 after optimization, and the plan1 is as follows: plan0: T1 (c1='AAA1' and c2='AAA2' and c2||c3='AA2AAA3'); plan1: HASH JOIN (T1_4.rowid=T1_6.rowid), the next layer is: HASH JOIN (T1_4.rowid=T1_5.rowid) and T1_6 (IDX_C2_C3 (C2, C3), c2='AAA2' and c2||c3='AA2AAA3'), and the next layer is T1_4 (IDX_C4 (C4)) T1_5 (IDX_C1_C2 (C1, C2), c1='AAA1' and c2='AAA2')

[0094] It can be seen that in plan1, a single table is scanned by using a multi-index scanning manner, and data is directly obtained by scanning an index, so that a back table operation is avoided.

[0095] S209, querying according to the final query plan to obtain a query result.

[0096] The technical scheme of the embodiment of the application generates multiple table copies for a target table according to columns of the target table in an original query statement, condition statements and indexes related to the target table, allocates a secondary index for each table copy and determines conditions covered by each secondary index. The table copies with small query cost are connected into a hash join by taking a row identifier column on the target table as a join key, when the cost of the newly generated optimized query plan is smaller than the cost of the original query plan, the original query plan is replaced by the optimized query plan as a final query plan, a multi-index scanning manner is realized, and the purpose of obtaining all data directly from an index without using a back table is achieved. The feature of not using a back table in the application reduces a large amount of physical reading and logical reading, improves overall query performance and reduces query time consumption.

[0097] Exemplarily, the determination step of the optimal query plan in the method can be demonstrated by using a specific example, Figure 3 An example flowchart of an optimization query method provided by the second embodiment of the application is shown in Figure 3 The method comprises the following steps.

[0098] S301, obtaining an original query statement and initializing related variables;

[0099] S302, generating a bit array col barr of the column according to the column number of the target table;

[0100] S303, generating a first mapping array pred_2_col_barr according to the target condition;

[0101] S304, generating a second mapping array index_2_col_barr according to the index involved in the target table;

[0102] S305, deleting the redundant indexes in the second mapping array index_2_col_barr;

[0103] S306, generating a table copy and a corresponding third mapping array tab_2_col_barr according to the first mapping array and the second mapping array;

[0104] S307, generating a hashjoin from a group of table copies with the minimum cost and covering col barr from the third mapping array tab_2_col_barr, and recording as an optimized query plan plan1;

[0105] S308, whether the cost of the original query plan is greater than the cost of the optimized query plan cost (plan0) > cost (plan1)? If yes, jump to step S309, if not, end;

[0106] S309, replacing the original query plan with the optimized query plan as the optimal query plan.

[0107] Embodiment three

[0108] Figure 4 A structural schematic diagram of an optimization query device provided by the third embodiment of the present application is shown in the figure. Figure 4 As shown in the figure, the device comprises:

[0109] A statement acquisition module 41 for acquiring an original query statement and initializing related variables;

[0110] A copy determination module 42 for determining a table copy according to the original query statement and the initialized related variables;

[0111] A plan replacement module 43 for connecting the table copy, generating an optimal query plan of the target table, and replacing an original query plan in the original query statement to obtain a final query plan;

[0112] A result determination module 44 for querying according to the final query plan to obtain a query result.

[0113] The technical scheme of the embodiment of the present application comprises the following steps: obtaining an original query statement and initializing related variables; determining a copy of a target table according to the original query statement and the initialized related variables; connecting the copy to generate an optimal query plan of the target table, and replacing an original query plan in the original query statement to obtain a final query plan; and querying according to the final query plan to obtain a query result. The column of the target table used in the query and the index of the target table are used to generate a table copy, and the table copy is combined to obtain a table copy combination with the minimum cost and capable of covering the column used in the query, which is used as the optimal query plan, so that the purpose of not using a back table and obtaining all data directly from the index is achieved, a large number of physical reading and logical reading caused by the back table are reduced, and the overall query performance is improved.

[0114] Further, the copy determining module 42 comprises:

[0115] A first determining unit configured to determine a bit array of a column according to the column number of the target table and the corresponding column in the original query statement;

[0116] A second determining unit configured to determine a first mapping array according to the target condition in the original query statement and the bit array of the column, the first mapping array being an array comprising the mapping relationship between a sub-condition and a first cover column array generated based on the sub-condition;

[0117] A third determining unit configured to determine a second mapping array according to the target table and the index related to the target table in the original query statement, the second mapping array being an array comprising the mapping relationship between a candidate index and a corresponding second cover column array;

[0118] A fourth determining unit configured to generate a copy of the target table and a third mapping array according to the first mapping array and the second mapping array, the third mapping array comprising an array of the mapping relationship between each table copy in the copy and a corresponding third cover column array.

[0119] The second determining unit is specifically configured to:

[0120] filtering a filter condition from the original query statement as a target condition;

[0121] determining a first cover column array of the sub-condition according to the bit array of the column of the data column specified by each sub-condition in the target condition;

[0122] determining a first mapping array according to the sub-condition and the first cover column array.

[0123] The third determining unit is specifically configured to:

[0124] generating a corresponding intermediate cover column array for all available secondary indexes of the target table according to indexes involved by the target table in the original query statement;

[0125] determining an intermediate mapping array according to the secondary indexes and the intermediate cover column array;

[0126] screening secondary indexes with the same intermediate cover column array in the intermediate mapping array to determine a screened second mapping array.

[0127] The fourth determining unit is specifically configured to:

[0128] generating a table copy of the target table for each candidate index in the second mapping array;

[0129] determining an internal structure attribute of the table copy corresponding to the candidate index based on a condition covered by the candidate index determined according to the first mapping array;

[0130] constructing a copy of the target table according to all the table copies;

[0131] determining a third mapping array according to the table copy and a corresponding third cover column array.

[0132] Further, the plan replacing module 43 is specifically configured to:

[0133] determining a table copy combination with a minimum query cost of scanning the target table according to a cost of each table copy in the copy;

[0134] determining an optimal combination and a corresponding join operation according to a fourth cover column array corresponding to each table copy in the table copy combination;

[0135] generating a hash join condition of the join operation according to a row identifier column of each table copy in the table copy combination, and determining an optimal query plan of the target table with a minimum cost.

[0136] The optimization query device provided in the embodiments of the present application can execute the optimization query method provided in any of the embodiments of the present application, and has the corresponding function modules and beneficial effects of the execution method.

[0137] Embodiment four

[0138] Figure 5A structural diagram of an electronic device 50 that can be used to implement embodiments of the present application is shown. The electronic device is intended to represent various forms of digital computers, such as laptops, desktops, tablets, personal digital assistants, servers, blade servers, mainframes, and other appropriate computers. The electronic device can also represent various forms of mobile devices, such as personal digital assistants, cellular telephones, smartphones, wearable devices (e.g., headsets, glasses, watches, etc.), and other similar computing devices. The components shown here, their connections and relationships, and their functions, are meant to be examples only, and are not meant to limit implementations of the present application described and / or claimed in this document.

[0139] As shown in Figure 5 The electronic device 50 includes at least one processor 51, and memory, such as read-only memory (ROM) 52, random access memory (RAM) 53, etc., communicatively connected to the at least one processor 51, where the memory stores computer programs executable by the at least one processor. The processor 51 can perform various appropriate actions and processes according to the computer programs stored in the read-only memory (ROM) 52 or loaded into the random access memory (RAM) 53 from the storage unit 58. In the RAM 53, various programs and data required for the operation of the electronic device 50 can also be stored. The processor 51, the ROM 52, and the RAM 53 are connected to each other through a bus 54. An input / output (I / O) interface 55 is also connected to the bus 54.

[0140] Various components in the electronic device 50 are connected to the I / O interface 55, including an input unit 56, such as a keyboard, a mouse, etc., an output unit 57, such as various types of displays, speakers, etc., a storage unit 58, such as a magnetic disk, an optical disk, etc., and a communication unit 59, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 59 allows the electronic device 50 to exchange information / data with other devices through a computer network, such as the Internet, and / or various telecommunication networks.

[0141] The processor 51 can be various general and / or special purpose processing components with processing and computing capabilities. Some examples of the processor 51 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The processor 51 performs various methods and processes described above, such as the optimization query method.

[0142] In some embodiments, the optimized query method can be implemented as a computer program tangibly embodied in a computer readable storage medium, e.g., storage unit 58. In some embodiments, parts or all of the computer program can be loaded and / or installed onto electronic device 50 via, e.g., ROM 52 and / or communication unit 59. When the computer program is loaded onto RAM 53 and executed by processor 51, one or more steps of the optimized query method described above can be performed. Alternatively, in other embodiments, processor 51 can be configured to perform the optimized query method by way of other means (e.g., by way of firmware).

[0143] Various implementations of the systems and techniques described above can be realized in digital electronic circuitry, integrated circuitry, a field programmable gate array (FPGA), an application specific integrated circuit (ASIC), a system on a chip (SOC), a programmable logic device (PLD), a computer hardware, firmware, software, and / or combinations thereof. These various implementations can include implementation in one or more computer programs that are executable and / or interpretable on a programmable system including at least one programmable processor, which can be special or general purpose, coupled to receive data and instructions from, and to transmit data and instructions to, a storage system, at least one input device, and at least one output device.

[0144] Computer programs used to implement the methods of the present application can be written in any combination of one or more programming languages. These computer programs can be provided to a processor of a general purpose computer, special purpose computer, or other programmable data processing apparatus to produce a machine, such that the computer program, when executed by the processor of the machine, implements the functions / acts specified in the flowcharts and / or block diagrams. The computer program can be executed entirely on a machine, partially on a machine, partially on a machine as part of a standalone software package, or entirely on a remote machine or server.

[0145] In the context of the present application, a computer-readable storage medium can be a tangible medium that can contain or store a computer program for use by or in connection with an instruction execution system, apparatus, or device. A computer-readable storage medium can include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium can be a machine-readable signal medium. More specific examples of a machine-readable storage medium will include one or more lines of a program of instructions in a transitory signal, a portable computer diskette, 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.

[0146] To provide for interaction with a user, the systems and techniques described here can be implemented on an electronic device having a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user and a keyboard and a pointing device (e.g., a mouse or a trackball) by which the user can provide input to the electronic device. Other kinds of devices can be used to provide for interaction with a user as well; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form, including acoustic, speech, or tactile input.

[0147] The systems and techniques described here can be implemented in a computing system that includes a back end component (e.g., as a data server), or that includes a middleware component (e.g., an application server), or that includes a front end component (e.g., a user computer having a graphical user interface or a Web browser through which a user can interact with an implementation of the systems and techniques described here), or any combination of such back end, middleware, or front end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0148] The computing system can include clients and servers. A client and server are generally remote from each other and typically interact through a communication network. The relationship of client and server arises by virtue of computer programs running on the respective computers and having a client-server relationship to each other. Servers can be cloud servers, also known as cloud computing servers or cloud hosts, which are a host product in the cloud computing service system to solve the defects of great management difficulty and weak business scalability in traditional physical hosts and VPS services.

[0149] In an embodiment, the present embodiment further includes a computer program product, which includes a computer program, the computer program, when executed by a processor, implements the optimization query method of any embodiment of the present application.

[0150] The computer program code implementing the application can be written in one or more programming languages or combinations of languages including object oriented languages such as Java, Smalltalk, C++ or conventional procedural programming languages such as the "C" programming language or similar programming languages. The program code can execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the latter scenario, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection can be made to an external computer (for example, through the Internet using an Internet Service Provider).

[0151] It should be understood that the various forms of flow shown above can be re-ordered, added to, or deleted from without departing from the spirit and scope of the present application. For example, the steps recited in the present application can be performed in parallel, in series, or in a different order, and the present application is not limited in this regard.

[0152] The above detailed description does not limit the scope of the present application. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modification, equivalent replacement, and improvement within the spirit and principles of the present application should be included in the scope of the present application.

Claims

1. An optimized query method, characterized in that, include: Retrieve the original query statement and initialize the relevant variables; Based on the original query statement and the initialized related variables, determine the copy of the target table; Join the replicas to generate the optimal query plan for the target table, and replace the original query plan in the original query statement to obtain the final query plan; Perform the query according to the final query plan to obtain the query results.

2. The method according to claim 1, characterized in that, The step of determining a copy of the target table based on the original query statement and the initialized related variables includes: Based on the column numbers and corresponding columns of the target table in the original query statement, determine the bit array of the columns; Based on the target conditions in the original query statement and the bit array of the column, a first mapping array is determined. The first mapping array is an array containing the mapping relationship between the sub-conditions and the first covering column array generated based on the sub-conditions. Based on the target table and the indexes involved in the target table in the original query statement, a second mapping array is determined. The second mapping array is an array containing the mapping relationship between the candidate indexes and the corresponding second covering column arrays. Based on the first mapping array and the second mapping array, a copy of the target table and a third mapping array are generated. The third mapping array includes an array of mapping relationships between each table copy in the copy and the corresponding third covering column array.

3. The method according to claim 2, characterized in that, Determining the first mapping array based on the target conditions in the original query statement and the bit array of the column includes: Filter the original query statement to select the target conditions; For each data column specified in the target condition, the first covering column array of the sub-condition is determined based on the bit array of the column of the data column; The first mapping array is determined based on the sub-conditions and the first covering column array.

4. The method according to claim 2, characterized in that, The step of determining the second mapping array based on the target table and the indexes involved in the target table in the original query statement includes: Based on the indexes involved in the target table in the original query statement, generate corresponding intermediate covering column arrays for all available secondary indexes in the target table; Based on the secondary index and the intermediate overlay column array, determine the intermediate mapping array; The second mapping array is determined by filtering the secondary indices that are the same in the intermediate covering column array.

5. The method according to claim 2, characterized in that, The step of generating a copy of the target table and a third mapping array based on the first mapping array and the second mapping array includes: For each candidate index in the second mapping array, generate a table copy of the target table for the candidate index; Based on the first mapping array, determine the conditions covered by the candidate index, and determine the internal structure attributes of the table replica corresponding to the candidate index; A copy of the target table is constructed based on all the aforementioned table copies; The third mapping array is determined based on the table copy and the corresponding third overlay column array.

6. The method according to claim 1, characterized in that, The step of joining the replicas to generate the optimal query plan for the target table includes: Based on the cost of each table replica in the replicas, determine the combination of table replicas that has the lowest query cost for scanning the target table; Based on the fourth covering column array corresponding to each table replica in the table replica combination, determine the optimal combination and the corresponding join operation; Based on the row identifier column of each table replica in the table replica combination, the hash join condition for the join operation is generated, and the optimal query plan for the target table with the minimum cost is determined.

7. An optimized query device, characterized in that, include: The statement retrieval module is used to retrieve the original query statement and initialize related variables; The replica determination module is used to determine the replica of the target table based on the original query statement and the initialized related variables; The plan replacement module is used to join the replicas, generate the optimal query plan for the target table, and replace the original query plan in the original query statement to obtain the final query plan; The result determination module is used to perform a query based on the final query plan and obtain the query results.

8. An electronic device, characterized in that, The electronic device includes: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the optimized query method according to any one of claims 1-6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that cause a processor to execute the optimized query method according to any one of claims 1-6.

10. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the optimized query method according to any one of claims 1-6.

Images