An optimization query method, device, equipment, medium and product

Abstract

The application discloses an optimization query method, device, equipment, medium and product. The optimization query method 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 and the index of the target table used in the query, 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 that the back table is not needed and all data is 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.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to an optimized query method, apparatus, device, medium, and product. Background Technology

[0002] In databases, secondary indexes, also known as non-clustered indexes or minor indexes, are indexes created outside of primary indexes to improve query performance on non-primary key columns. They are the opposite of primary indexes, which are typically created on the primary key columns of a table to uniquely identify each row of data. Secondary indexes usually contain the indexed columns and pointers to the data rows, allowing for quick location of the data rows through the indexed columns.

[0003] Generally, a table query can only use one secondary index. However, when a table is queried using a secondary index, if the query statement involves columns outside the secondary index, the entire query cannot be completed using only the secondary index. It is necessary to use the clustered key or row identifier (rowid) on the secondary index to find the complete data record on the clustered index. This process is called a table lookup.

[0004] When performing a single-table query using conventional methods, accessing the table involves using a single index and a table lookup, which results in a large number of physical and logical reads, thus slowing down the query efficiency. Summary of the Invention

[0005] This invention provides an optimized query method, apparatus, device, medium, and product to enable data querying without the need for table lookup, thereby improving overall query performance.

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

[0007] Retrieve the original query statement and initialize the relevant variables;

[0008] Based on the original query statement and the initialized related variables, determine the copy of the target table;

[0009] 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;

[0010] Perform the query according to the final query plan to obtain the query results.

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

[0012] The statement retrieval module is used to retrieve the original query statement and initialize related variables;

[0013] 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;

[0014] 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;

[0015] A result determination module is used to perform a query according to the final query plan and obtain the query result. According to a third aspect of the present invention, an electronic device is provided, the electronic device comprising:

[0016] At least one processor; and

[0017] A memory communicatively connected to the at least one processor; wherein,

[0018] 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 embodiment of the present invention.

[0019] According to a fourth aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions for causing a processor to execute and implement the optimized query method described in any embodiment of the present invention.

[0020] According to a fifth aspect of the present invention, embodiments of the present invention also provide a computer program product, the computer program product including a computer program, which, when executed by a processor, implements the optimized query method of any embodiment of the present invention.

[0021] The technical solution of this invention involves obtaining the original query statement and initializing relevant variables; determining a copy of the target table based on the original query statement and the initialized variables; joining the copies to generate the optimal query plan for the target table; replacing the original query plan in the original query statement with the optimal query plan; and performing a query based on the final query plan to obtain the query results. By generating table copies based on the columns and indexes of the target table used in the query, and combining and joining these table copies, a combination of table copies with the lowest cost that exactly covers the columns used in the query is obtained as the optimal query plan. This achieves the goal of obtaining all data directly from the index without using table lookups, reducing the large number of physical and logical reads caused by table lookups, thereby improving overall query performance.

[0022] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

[0023] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0024] Figure 1 This is a flowchart of an optimized query method provided according to Embodiment 1 of the present invention;

[0025] Figure 2 This is a flowchart of an optimized query method provided according to Embodiment 2 of the present invention;

[0026] Figure 3 This is an example flowchart of an optimized query method provided according to Embodiment 2 of the present invention;

[0027] Figure 4 This is a schematic diagram of the structure of an optimized query device according to Embodiment 3 of the present invention;

[0028] Figure 5 This is a schematic diagram of the structure of an electronic device that implements an embodiment of the present invention. Detailed Implementation

[0029] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[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] For example, the relevant variables can include six, such as: 1) A byte array of columns (col_barr): a byte array that records all columns of the target table (hereinafter referred to as table T1) used in the query. 2) A map array of predicates and columns (pred_2_col_barr): a map array where the key corresponds to the predicate (must be a condition after WHERE, not after ON), and the value is the array of columns covering the WHERE condition (array type and meaning of each item are the same as col_barr). Initialized to empty; 3) A map array of indexes and columns (index_2_col_barr): a map array where the key corresponds to the secondary index, and the value is the array of columns covering the corresponding secondary index (i.e., records the columns in table T1 included in the index; array type and meaning of each item are the same as col_barr). Initialized to empty; 4) A map array of tables and columns (tab_2_col_barr): a map array where the key corresponds to the table copy generated based on the index, and the value corresponds to the array of columns covering the table copy (array type and meaning of each item are the same as col_barr). 5) Original plan (plan0): Initialized as the plan to be optimized; 6) Optimized plan (plan1): Initialized as empty, the optimized plan will be stored later.

[0037] S120. Based on the original query statement and the initialized related variables, determine the copy of the target table.

[0038] In this embodiment, the target table can be understood as the table that the original query statement wants to query. 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 based on the columns and conditions used in the query for table T1 (where the selected indexes can cover the conditions and columns they are assigned), and generate n copies for table T1. Each copy applies an index and is responsible for the output of a portion of the upper-level columns.

[0040] S130. 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.

[0041] In this embodiment, the optimal query plan can be understood as an optimized query plan that does not require a table lookup, such as forming the optimal query plan through a hash join. The original query plan can be understood as the query plan composed of the query conditions in the original query statement.

[0042] Specifically, the processor can combine multiple replicas with the row identifier column of each replica in different ways to form a hash connection, and select the newly generated hash connection whose query cost is less than that of the target table, and replace the original query plan in the original query statement with the newly generated hash connection to obtain the final query plan.

[0043] S140. Perform the query according to the final query plan and obtain the query results.

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

[0045] Specifically, the processor can perform multiple index scans on a single target table based on the final query plan, obtaining data and constructing query results by directly scanning the indexes.

[0046] The technical solution of this invention involves obtaining the original query statement and initializing relevant variables; determining a copy of the target table based on the original query statement and the initialized variables; joining the copies to generate the optimal query plan for the target table; replacing the original query plan in the original query statement with the optimal query plan; and performing a query based on the final query plan to obtain the query results. By generating table copies based on the columns and indexes of the target table used in the query, and combining and joining these table copies, a combination of table copies with the lowest cost that exactly covers the columns used in the query is obtained as the optimal query plan. This achieves the goal of obtaining all data directly from the index without using table lookups, reducing the large number of physical and logical reads caused by table lookups, thereby improving overall query performance.

[0047] Example 2

[0048] Figure 2 This is a flowchart of an optimized query method provided in Embodiment 2 of the present invention. This embodiment is a further refinement of the above embodiments. Figure 2 As shown, the method includes:

[0049] S201. Obtain the original query statement and initialize the relevant variables.

[0050] S202. Determine the bit array of the column based on the column number and corresponding column of the target table in the original query statement.

[0051] In this embodiment, the column number can be understood as a number set to distinguish each column in the target table. The column bit array can be understood as a record of all columns of the target table used in the query, where the array index i represents the column number (e.g., it can be represented by COLID, starting from 0). The i-th item in the array being 0 indicates that the column with COLID i was not used in the query, and the i-th item being 1 indicates that the column with COLID i was used in the query. All items are initialized to 0.

[0052] Specifically, the processor can analyze the columns of the target table used in the original query statement. The processor can record the columns according to their numbers to obtain a 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. Because each column represents a COLID and each column has a number, the array col_barr serves as the array's own number, with 0 in the array to mark whether a column exists or not. A total of 2048 (8*256) bits of column existence can be recorded, that is, the column bit array col_barr can record a maximum of 256 items (each item is a byte type data, and each byte type data has 8 bits). For other virtual columns created on the table (system-generated columns rather than those specified when the table is created), this invention only processes rowid, whose COLID is 65535 (since there must be an identifier for rowid data on the table, its COLID does not need to be stored in the column's bit array (represented later by col_barr), and can be retrieved directly from the table). This invention will not process other virtual columns (for example, the object_id column, whose COLID is 65522, which is not supported by the current optimization).

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

[0054] S203. Based on the target conditions and column bit arrays in the original query statement, determine the first mapping array. 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.

[0055] In this embodiment, the target condition can be understood as the condition used for querying, such as `select c4from t1 where c1='AAA1'and c2='AAA2'and c2||c3='AA2AAA3'`. The first mapping array can be understood as an array used to establish the mapping relationship between each sub-condition in the target condition and the first covering column array. The covering column array refers to the covering columns represented in array form, which are several columns involved in the condition. The first covering 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, and generate the corresponding first covering column array by combining the column bit array with the column corresponding to the sub-conditions after the key clause used to filter data in the target condition. For example, the key clause can be a WHERE condition to specify data query or operation.

[0057] Furthermore, based on the above embodiments, the step of determining the first mapping array according to the target conditions and column bit arrays in the original query statement can be refined as follows:

[0058] Filter the original query statement to select the target conditions; for each sub-condition in the target conditions, determine the first covering column array of the sub-condition based on the bit array of the column; determine the first mapping array based on the sub-condition and the first covering column array.

[0059] In this embodiment, the filtering condition can be understood as a statement used to characterize the multi-condition filtering of data in the target table, that is, the filtering condition represented by the WHERE clause in the original query statement, such as a statement containing the AND connector. This is because a single condition (without AND connection) does not need to apply multi-index scan optimization, and one index is sufficient. In other cases where multiple conditions are connected by OR, the optimization cannot be directly pushed down to the index, and the optimization effect cannot be achieved by utilizing the characteristics of the index itself, so there is no need for optimization.

[0060] Specifically, the processor can filter out conditional statements containing conjunctions from the original query statement as target conditions. For each data column specified by a sub-condition connected by conjunctions in the target condition, the processor can generate a first covering column array for the sub-conditions based on the bit array of the data columns, and determine a first mapping array based on the sub-conditions and the first covering column array.

[0061] For example, taking the target condition mentioned above, the processor can determine the first covering column array corresponding to each sub-condition connected by "and" after "where", namely c1 = 'AAA1', c2 = 'AAA2', and c2||c3 = 'AA2AAA3'. That is, the first covering column array corresponding to sub-condition c1 = 'AAA1' is 00000001, the first covering column array corresponding to sub-condition c2 = 'AAA2' is 00000010, and the first covering column array corresponding to sub-condition c2||c3 = 'AA2AAA3' is 00000110. These are stored in the first mapping array (pred_2_col_barr) in the form of <sub-condition, first covering column array>, where each condition is each item connected by "and".

[0062] S204. Based on the target table and the indexes involved in the target table in the original query statement, determine the second mapping array. The second mapping array is an array containing the mapping relationship between the candidate indexes and the corresponding second covering column array.

[0063] In this embodiment, the indexes involved in the target table can be understood as all available secondary indexes on the target table. The candidate indexes can be understood as those with no redundant indexes after filtering. The second mapping array can be understood as the mapping relationship between the candidate indexes and their corresponding second covering column arrays. For example, the second mapping array can be of type map, where the key corresponds to the table copy generated based on the candidate index, and the value corresponds to the second covering column array of the table copy (array type and meaning of each item are the same as col_barr).

[0064] Specifically, the processor can determine the covering column array corresponding to each index based on the target table and the indexes involved in the target table in the original query statement. First, it establishes a mapping relationship between each index and the covering column array. Then, by deduplicating the covering column arrays corresponding to the indexes under the same index, the remaining ones are the candidate indexes and their corresponding second covering column arrays, thereby determining the second mapping array.

[0065] Furthermore, based on the above embodiments, the step of determining the second mapping array according to the target table and the indexes involved in the target table in the original query statement can be refined as follows:

[0066] 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; determine the intermediate mapping array based on the secondary indexes and the intermediate covering column arrays. Filter the secondary indexes with the same intermediate covering column arrays in the intermediate mapping arrays to determine the filtered second mapping array.

[0067] In this embodiment, all available secondary indexes can be understood as all non-primary key indexes already created in the target table that can be used by the query optimizer. Their availability can be determined by the identifier in the index, and the availability of secondary indexes also depends on the query conditions. The intermediate covering column array can be understood as the array of covering columns corresponding to the index columns mentioned in all indexes involved in the target table. The intermediate mapping array can be understood as the array consisting of the mapping relationships between all secondary indexes and their intermediate covering column arrays.

[0068] Specifically, the processor can first generate an intermediate covering column array for all available secondary indexes in the target table based on the indexes involved in the original query statement; then, based on the secondary indexes and the intermediate covering column array, determine the intermediate mapping array. The processor can filter the secondary indexes with the same intermediate covering column array in the intermediate mapping array, determine the cost of the identical secondary indexes, retain the secondary indexes with lower cost, and delete the secondary indexes with higher cost, thereby determining the filtered second mapping array.

[0069] For example, to illustrate with a specific example, the index involved in the target table could 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] The indexes are defined as follows: IDX_C3_C4(C3,C4), IDX_C1_C3_C4(C1,C3,C4), and IDX_ID_C3_C4(ID,C3,C4), where the columns in parentheses are the columns included in the index, and IDX_XXX_YYY represents the index name. The intermediate covering column arrays (e.g., represented by index_2_col_barr) for each index can be represented as follows: IDX_C1(C1) has an intermediate covering column array of 00000001; IDX_C2(C2) has an intermediate covering column array of 00000010; IDX_C3(C3) has an intermediate covering column array of 00000100; IDX_C4(C4) has an intermediate covering column array of 00001000; and IDX_C1_C2(C1,C2) has an intermediate covering column array of 00001000. The intermediate covering column array corresponding to IDX_C2_C3(C2,C3) is 00000110; the intermediate covering column array corresponding to IDX_C3_C4(C3,C4) is 00001100; the intermediate covering column array corresponding to IDX_C1_C3_C4(C1,C3,C4) is 00001101; the intermediate covering column array corresponding to IDX_ID_C3_C4(ID,C3,C4) is 00001100 (wherein, the ID column does not appear in the query, so it is not included in the intermediate covering column array). As can be seen from the example above, the secondary indexes with the same intermediate covering column array are IDX_C3_C4(C3,C4) and IDX_ID_C3_C4(ID,C3,C4). It can be seen that both indexes involve columns C3 and C4, but IDX_C3_C4 involves fewer columns. Therefore, IDX_C3_C4 is ultimately retained. The resulting second mapping array is an intermediate mapping array that does not contain the IDX_ID_C3_C4(ID,C3,C4) item, which can be stored in index_2_col_barr in the form of <index, covering column array>.

[0072] S205. Based on the first mapping array and the second mapping array, generate a copy of the target table and a third mapping array. The third mapping array includes an array of mapping relationships between each table copy in the copy and the corresponding third covering column array.

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

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

[0075] Furthermore, based on the above embodiments, the steps of generating a copy of the target table and a third mapping array according to the first mapping array and the second mapping array can be refined as follows:

[0076] For each candidate index in the second mapping array, generate a table copy of the target table for the candidate index; determine the conditions covered by the candidate index based on the first mapping array, and determine the internal structure attributes of the table copy corresponding to the candidate index; construct a copy of the target table based on all table copies; determine the third mapping array based on the table copies and the corresponding third covering column array.

[0077] In this embodiment, internal structure attributes can be understood as attributes used to indicate the structure of table replicas.

[0078] Specifically, for each candidate index in the second mapping array, a table copy of the target table is generated for that candidate index; that is, each candidate index corresponds to one table copy. The processor can determine the conditions covered by the candidate index based on the first mapping array. The processor can use the candidate index and the conditions together as internal structural attributes to determine the table copy corresponding to the candidate index. The processor can construct a copy of the target table based on all table copies; and determine the third mapping array based on the table copies and the corresponding third covering column array.

[0079] For example, table copies of the target table are generated for each candidate index in the second covering column array `index_2_col_barr`. One table copy corresponds to one candidate index. Multiple table copies are obtained through copying. Combined with the first covering column array `pred_2_col_barr`, the conditions that each candidate index can cover are assigned to its corresponding table copy. When the third covering column array of a candidate index contains the covering column array of the conditions, it indicates that the index can cover that condition. These are stored in `tab_2_col_barr` in the form of `<table copy, covering column array>`. The conditions assigned to the candidate index and the table copy are recorded in the internal structure attributes of the table copy. In this example, the table copy corresponding to the candidate index and the conditions assigned to the table copy can be represented by the following table:

[0080] Example of the third covered column array in Table 1:

[0081]

[0082] S206. Determine the combination of table replicas with the minimum query cost for scanning the target table according to the cost of each table replica in the replica.

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

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

[0085] Exemplarily, there are four table replicas A, B, C, and D, and the costs of each table replica from small to large are A, B, C, and D. First, select the table A with the 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), then select A and C to perform a left join first; similarly, then make (A, C) try to combine with B and D in turn until the columns of the obtained table combination exactly cover the coverage column array of col_barr, and then the selection can be stopped.

[0086] S207. Determine the optimal combination and the corresponding join operation according to the fourth coverage column arrays corresponding to the table replicas in the combination of table replicas.

[0087] In this embodiment, the join operation can be understood as an operation used to combine the data in multiple tables according to specific conditions, such as join(table replica A, table replica B). The optimal combination can be understood as the combination of table replicas used to filter the minimum cost and the bit array that can cover the columns after combination.

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

[0089] For example, if the optimal combination determined based on query cost is to join table replicas A and B, then the covering column array corresponding to `join(A, B)` is the covering column array of table replica A plus the covering column array of table replica B. Adding a new table replica follows the same principle; the covering column array corresponding to the newly generated join operation is the covering column array of the current join plus the covering column array of the newly added table. For the newly generated join, if there exists an intersection between its covering column array and `col_barr` that is still `col_barr` (i.e., equivalent), it means that it can cover exactly. The first join operation that can cover exactly is then returned.

[0090] S208. Based on the row identifier column of each table replica in the table replica combination, generate the hash join condition for the join operation and determine the optimal query plan for the target table with the minimum cost.

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

[0092] Specifically, the processor can generate hash join conditions between table replicas based on the row identifier column of each table replica in the table replica combination. That is, if the row identifier columns of the two table replicas to be joined are the same, the hash join condition is used to obtain an optimized query plan. The optimizer calculates the query cost of the original query plan and the query cost of the optimized query plan respectively, and selects the query plan with the minimum query cost as the optimal query plan. That is, 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 optimized query plan replaces the original query plan in the overall plan to obtain the final query plan.

[0093] For example, the way to generate join conditions based on rowid is as follows: each table replica has a rowid. If it is determined that two table replicas A and B should be combined into a hashjoin, then the join condition A.ROWID = B.ROWID is generated. In this example, the original query plan is plan0, and the hash join consisting of table replicas corresponds to the optimized query plan plan1, as follows: plan0: T1(c1='AAA1'and c2='AAA2'and c2||c3='AA2AAA3'); plan1: HASH JOIN(T1_4.rowid=T1_6.rowid), the next level is: HASH JOIN(T1_4.rowid=T1_5.rowid) and T1_6(IDX_C2_C3(C2,C3),c2='AAA2'and c2||c3='AA2AAA3'), the next level is T1_4(IDX_C4(C4))T1_5(IDX_C1_C2(C1,C2),c1='AAA1'and c2='AAA2')

[0094] As can be seen, plan1 uses a multi-index scan approach for a single table, which avoids table lookup operations by directly scanning the index to retrieve data.

[0095] S209. Perform the query according to the final query plan and obtain the query results.

[0096] The technical solution of this invention generates multiple table copies of the target table based on the columns, conditional statements, and indexes involved in the target table in the original query statement. Then, a secondary index is allocated to each table copy, and the conditions covered by each secondary index are determined. By using the row identifier column of the target table as the join key, table copies with lower query costs are joined into a hash join. When the cost of the newly generated optimized query plan is lower than that of the original query plan, the original query plan is replaced by the optimized query plan as the final query plan. This achieves a multi-index scan approach, eliminating the need for table lookups and allowing all data to be obtained directly from the indexes. The fact that this invention eliminates the need for table lookups significantly reduces physical and logical reads, improves overall query performance, and reduces query time.

[0097] For example, a concrete example can be used to demonstrate the steps in determining the optimal query plan in this method. Figure 3 Here is an example flowchart of an optimized query method provided in Embodiment 2 of the present invention, as follows: Figure 3 As shown, the method includes:

[0098] S301. Obtain the original query statement and initialize the relevant variables;

[0099] S302. Generate a bit array col_barr for the columns based on the column numbers of the target table;

[0100] S303. Generate the first mapping array pred_2_col_barr according to the target conditions;

[0101] S304. Generate a second mapping array index_2_col_barr based on the indexes involved in the target table;

[0102] S305. Delete redundant indices in the second mapping array index_2_col_barr;

[0103] S306. Generate a table copy and a corresponding third mapping array tab_2_col_barr based on the first mapping array and the second mapping array;

[0104] S307. Select a set of table replicas from the third mapping array tab_2_col_barr that has the lowest cost and can just cover col_barr to generate a hashjoin, denoted as the optimized query plan plan1.

[0105] S308. Is the cost of the original query plan greater than the cost(plan0) > cost(plan1) of the optimized query plan? If yes, proceed to step S309; ​​otherwise, end.

[0106] S309. Optimize the query plan and replace the original query plan with the optimal query plan.

[0107] Example 3

[0108] Figure 4 This is a schematic diagram of an optimized query device provided in Embodiment 3 of the present invention. Figure 4 As shown, the device includes:

[0109] Statement retrieval module 41 is used to retrieve the original query statement and initialize related variables;

[0110] The replica determination module 42 is used to determine the replica of the target table based on the original query statement and the relevant variables after initialization.

[0111] The plan replacement module 43 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;

[0112] The result determination module 44 is used to perform a query based on the final query plan and obtain the query results.

[0113] The technical solution of this invention involves obtaining the original query statement and initializing relevant variables; determining a copy of the target table based on the original query statement and the initialized variables; joining the copies to generate the optimal query plan for the target table; replacing the original query plan in the original query statement with the optimal query plan; and performing a query based on the final query plan to obtain the query results. By generating table copies based on the columns and indexes of the target table used in the query, and combining and joining these table copies, a combination of table copies with the lowest cost that exactly covers the columns used in the query is obtained as the optimal query plan. This achieves the goal of obtaining all data directly from the index without using table lookups, reducing the large number of physical and logical reads caused by table lookups, thereby improving overall query performance.

[0114] Furthermore, the copy determination module 42 includes:

[0115] The first determining unit is used to determine the bit array of the column based on the column number and the corresponding column of the target table in the original query statement;

[0116] The second determining unit is used to determine a first mapping array based on the target conditions in the original query statement and the bit array of the column. 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.

[0117] The third determining unit is used to determine a second mapping array based on the target table and the indexes involved in the target table in the original query statement. The second mapping array is an array containing the mapping relationship between the candidate indexes and the corresponding second covering column arrays.

[0118] The fourth determining unit is used to generate a copy of the target table and a third mapping array based on the first mapping array and the second mapping array. The third mapping array includes an array of mapping relationships between each table copy in the copy and the corresponding third covering column array.

[0119] Specifically, the second determining unit is used for:

[0120] Filter the original query statement to select the target conditions;

[0121] 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;

[0122] The first mapping array is determined based on the sub-conditions and the first covering column array.

[0123] Specifically, the third determining unit is used for:

[0124] 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;

[0125] Based on the secondary index and the intermediate overlay column array, determine the intermediate mapping array;

[0126] The second mapping array is determined by filtering the secondary indices that are the same in the intermediate covering column array.

[0127] Specifically, the fourth determining unit is used for:

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

[0129] 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;

[0130] A copy of the target table is constructed based on all the aforementioned table copies;

[0131] The third mapping array is determined based on the table copy and the corresponding third overlay column array.

[0132] Furthermore, the planned replacement module 43 is specifically used for:

[0133] 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;

[0134] 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;

[0135] 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.

[0136] The optimized query device provided in this embodiment of the invention can execute the optimized query method provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0137] Example 4

[0138] Figure 5A schematic diagram of an electronic device 50 that can be used to implement embodiments of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (e.g., helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

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

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

[0141] Processor 51 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of 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 suitable processor, controller, microcontroller, etc. Processor 51 performs the various methods and processes described above, such as optimizing query methods.

[0142] In some embodiments, the optimization query method may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 58. In some embodiments, part or all of the computer program may be loaded and / or mounted on electronic device 50 via ROM 52 and / or communication unit 59. When the computer program is loaded into RAM 53 and executed by processor 51, one or more steps of the optimization query method described above may be performed. Alternatively, in other embodiments, processor 51 may be configured to execute the optimization query method by any other suitable means (e.g., by means of firmware).

[0143] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0144] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0145] In the context of this invention, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0146] To provide interaction with a user, the systems and techniques described herein 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 pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; 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 sound input, voice input, or tactile input).

[0147] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or computing systems that include middleware components (e.g., application servers), or computing systems that include frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0148] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.

[0149] In one embodiment, the present invention further includes a computer program product, which includes a computer program that, when executed by a processor, implements the optimized query method of any embodiment of the present invention.

[0150] In implementing the computer program product, computer program code for performing the operations of this invention can be written in one or more programming languages ​​or a combination thereof. Programming languages ​​include object-oriented programming languages ​​such as Java, Smalltalk, and C++, as well as conventional procedural programming languages ​​such as C or similar languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a local area network (LAN) or a wide area network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0151] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0152] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. 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 modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

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

2. The method according to claim 1, 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.

3. The method according to claim 1, 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.

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

5. 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. The copy determination module includes: The first determining unit is used to determine the bit array of the column based on the column number and the corresponding column of the target table in the original query statement; The second determining unit is used to determine a first mapping array based on the target conditions in the original query statement and the bit array of the column. 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. The third determining unit is used to determine a second mapping array based on the target table and the indexes involved in the target table in the original query statement. The second mapping array is an array containing the mapping relationship between the candidate indexes and the corresponding second covering column arrays. The fourth determining unit is used to generate a copy of the target table and a third mapping array based on the first mapping array and the second mapping array, wherein the third mapping array includes an array of mapping relationships between each table copy in the copy and the corresponding third covering column array; Specifically, the fourth determining unit is used for: 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. 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-4.

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

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