Database query optimization methods, equipment, media, and products based on secondary indexes

Abstract

This invention provides a database query optimization method, device, medium, and product based on secondary indexes. The database query optimization method based on secondary indexes includes: obtaining an SQL statement; extracting the target table and all target query columns of the SQL statement; checking all composite secondary indexes of the target table based on all target query columns; if an index combination meeting a first preset condition exists among all composite secondary indexes of the target table, determining a group of index combinations meeting the first preset condition as a target index combination, wherein the first preset condition is that multiple composite secondary indexes in the index combination are concatenated according to a common column to cover all target query columns; using all composite secondary indexes in the target index combination to search for target data; and combining the searched target data according to the common column to obtain the query result of the SQL statement. This solution avoids table lookups and effectively improves query efficiency.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a database query optimization method, device, medium, and product based on a secondary index. Background Technology

[0002] Secondary indexes are auxiliary indexes built on non-primary key columns in row-oriented databases and are one of the core index structures of the database. Secondary indexes store only the indexed column data and row locators, not the entire row data. Currently, databases typically use a "single index + table lookup" approach to apply secondary indexes. This involves identifying a secondary index with good selectivity, performing an index scan to obtain the indexed column data and row locators that satisfy the query conditions, and then, for each matched row, performing a table lookup operation based on the row locators to find the data in the corresponding row of the query column not covered by the secondary index. This table lookup results in significant random I / O, cache thrashing, and lock contention, leading to substantial resource consumption. Summary of the Invention

[0003] One object of the present invention is to provide a database query optimization method, device, medium and product based on a secondary index that can solve any of the above problems.

[0004] Specifically, this invention provides a database query optimization method based on a secondary index, comprising: Obtain the SQL statement, which is a single-table multi-column projection query; Extract the target table and all target query columns of the SQL statement; Examine all composite secondary indexes of the target table based on all the target query columns; If an index combination that meets the first preset condition is found among all the composite secondary indexes of the target table, a group is determined from the index combination that meets the first preset condition as the target index combination. The first preset condition is that the multiple composite secondary indexes in the index combination are concatenated according to the common column to cover all the target query columns. Use all the composite secondary indexes in the target index combination to find the target data; The found target data is combined according to common columns to obtain the query results of the SQL statement.

[0005] Optionally, if multiple index combinations that meet the first preset condition exist among all composite secondary indexes of the target table, the step of determining a target index combination from the index combinations that meet the first preset condition includes: Check the uniqueness of column values ​​in the common column of the multiple sets of index combinations that meet the first preset condition; If, upon checking that there is an index combination among the multiple sets of index combinations that meet the first preset condition, a set is determined from the index combinations with unique column values ​​in the common column as the target index combination.

[0006] Optionally, if multiple sets of index combinations with unique column values ​​are found, the step of determining a set from the index combinations with unique column values ​​of the common columns as the target index combination includes: The target index combination is selected from the index combinations where the column values ​​of the common column are unique, and the combination with the lowest execution cost is selected as the target index combination.

[0007] Optionally, if it is found that there is no index combination in the multiple sets of index combinations that meet the first preset condition where the column values ​​of the common column are unique, the set with the lowest execution cost is selected from the multiple sets of index combinations that meet the first preset condition as the target index combination.

[0008] Optionally, if the column values ​​of the common columns in the target index combination are not unique, the step of using all the composite secondary indexes in the target index combination to find the target data includes: Extract the corresponding column data and row locators from all composite secondary indexes, and use them as the target data.

[0009] Optionally, if there is no index combination that meets the first preset condition among all the composite secondary indexes of the target table, but there is an index combination that meets the second preset condition among all the composite secondary indexes of the target table, a group is determined from the index combination that meets the second preset condition as the target index combination. The second preset condition is that multiple composite secondary indexes in the index combination are concatenated according to a common column to cover part of the target query column. Use all the composite secondary indexes in the target index combination to find the corresponding column data and row locators, which are then used as the first data. The first data found is combined according to common columns to obtain the first target data; Based on the row locator of the first target data, search for the second target data in the target query column that is not covered by the target index combination; The query result of the SQL statement is obtained by combining the first target data and the second target data.

[0010] Optionally, the step of determining a set of index combinations as the target index combination from the index combinations that meet the second preset condition includes: Estimate the number of rows returned by the SQL statement; Determine whether the ratio of the number of returned rows to the total number of rows in the target table is greater than a preset threshold. If not, execute the step of determining a set of index combinations as the target index combination from the index combinations that meet the second preset condition.

[0011] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a computer executable program stored in the memory and running on the processor, wherein the processor, when executing the computer executable program, implements the database query optimization method based on a secondary index according to any one of the preceding claims.

[0012] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer-executable program is stored, which, when executed by a processor, implements the database query optimization method based on a secondary index as described in any of the preceding claims.

[0013] According to another aspect of the present invention, a computer program product is also provided, comprising a computer executable program that, when executed by a processor, implements the database query optimization method based on a secondary index as described in any of the preceding claims.

[0014] The database query optimization method based on secondary indexes of this invention extracts the target table and all target query columns of the SQL statement, and checks all composite secondary indexes of the target table based on all target query columns. If an index combination that meets a first preset condition exists among all composite secondary indexes of the target table, a group of such index combinations is determined as the target index combination. The target data is then searched using all composite secondary indexes within the target index combination, and the searched target data is combined according to a common column to obtain the query result of the SQL statement. The first preset condition is that the concatenation of multiple composite secondary indexes in the index combination according to a common column covers all target query columns. In other words, if an index combination that meets the first preset condition exists among all composite secondary indexes of the target table, by using the common column as the join basis, the required data can be found entirely using the secondary indexes. Then, the data found by each secondary index is concatenated to obtain the final query result, thereby avoiding table lookups, effectively improving query efficiency, and reducing the consumption of runtime resources.

[0015] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0016] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings: Figure 1 This is a schematic flowchart of a database query optimization method based on a secondary index according to an embodiment of the present invention; Figure 2 This is a schematic flowchart illustrating the determination of target index combinations in a database query optimization method based on a secondary index according to an embodiment of the present invention. Figure 3 This is a schematic flowchart of a database query optimization method based on a secondary index according to another embodiment of the present invention; Figure 4 This is a schematic flowchart of a database query optimization method based on a secondary index according to yet another embodiment of the present invention; Figure 5 This is a schematic diagram of a computer device according to an embodiment of the present invention; Figure 6 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; Figure 7 This is a schematic diagram of a computer program product according to an embodiment of the present invention. Detailed Implementation

[0017] Those skilled in the art should understand that the embodiments described below are merely a part of the embodiments of the present invention, and not all of the embodiments of the present invention. These partial embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.

[0018] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0019] The flowcharts provided in this invention are not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.

[0020] like Figure 1 As shown, in one embodiment, the database query optimization method based on secondary indexes generally includes: Step S101: Obtain the SQL statement. The SQL (Structured Query Language) statement is a single-table multi-column projection query.

[0021] Specifically, a single-table multi-column query means retrieving data from multiple target query columns from the target table.

[0022] For example, `SELECT a, b, c FROM t1` means to search for data in columns a, b, and c from the target table t1.

[0023] It should be noted that WHERE conditions can also be set for single-table multi-column projection queries.

[0024] Step S102: Extract the target table and all target query columns of the SQL statement.

[0025] Specifically, this means determining which table and which columns of data to retrieve from. Referring to the previous example, the target table is t1, and the target query columns are a, b, and c.

[0026] Step S103: Check all composite secondary indexes of the target table based on all target query columns.

[0027] Specifically, a composite secondary index is an index built on multiple columns of a target table, which can index and scan data from multiple columns at once. For example, Ix_ab represents a composite secondary index built on columns a and b, and Ix_ab can be used to find data from both columns a and b simultaneously.

[0028] The target table's composite secondary indexes are checked based on the target query column. If an index combination that meets the first preset condition is found among all the composite secondary indexes of the target table, step S104 is executed.

[0029] Step S104: Select a set of index combinations that meet the first preset condition as the target index combination. The first preset condition is that multiple composite secondary indexes in the index combination are concatenated according to a common column to cover all target query columns.

[0030] For example, the SQL statement is `Select a,b,c from t1`, with the target query columns being a, b, and c. The target table t1 has composite secondary indexes Ix_ab and Ix_bc. These two indexes share a common column b. When concatenated according to this common column b, the resulting column set (a,b,c) covers the target query columns a, b, and c. Therefore, the composite secondary indexes Ix_ab and Ix_bc are an index combination that meets the first preset condition.

[0031] A target index combination is selected from the index combinations that meet the first preset condition. That is, if there is only one index combination that meets the first preset condition, it is selected as the target index combination. If there are multiple index combinations that meet the first preset condition, one of them is selected as the target index combination.

[0032] Reference Figure 2 As shown, specifically, given that there are multiple index combinations that meet the first preset condition among all composite secondary indexes of the target table, the step of determining one set of index combinations that meet the first preset condition as the target index combination includes: Step S201: Check the uniqueness of column values ​​in the common column of multiple index combinations that meet the first preset conditions.

[0033] Specifically, column value uniqueness means that all column values ​​are different. This means checking whether the column values ​​of the common column in each index combination that meets the first preset condition have duplicate values. If there are duplicate values, the column values ​​of the common column are not unique; if there are no duplicate values, the column values ​​of the common column are unique.

[0034] For example, for the index combination Ix_ab and Ix_bc that meets the first preset condition, if two rows of data in the common column b are both 25, then the common column b is not unique. If the data in each row of the common column b is different, then the common column b is unique.

[0035] Step S202: Determine if there is an index combination where the column values ​​of the common column are unique. If yes, proceed to step S203; otherwise, proceed to step S204.

[0036] Step S203: Determine a target index combination from the index combinations where the column values ​​of the common column are unique.

[0037] Specifically, if there is only one set of index combinations where the column values ​​of a common column are unique, then that set is selected as the target index combination. If there are multiple sets of index combinations where the column values ​​of a common column are unique, then one of them is selected as the target index combination.

[0038] Furthermore, if multiple index combinations with unique values ​​in their common columns are found, the index combination with the lowest execution cost is selected as the target index combination. The execution cost includes the scan cost of multiple composite secondary indexes within the index combination and the cost of concatenating the common columns. In simpler terms, the optimizer estimates the total number of rows to be scanned based on the target table's statistics and the preferred scan order of multiple composite secondary indexes, and estimates the concatenation cost based on the number of rows to be concatenated.

[0039] Step S204: Select the group with the lowest execution cost from multiple groups of index combinations that meet the first preset conditions as the target index combination.

[0040] If, when multiple sets of index combinations that meet the first preset condition are found to be index combinations where the column values ​​of the common column are unique, the set with the lowest execution cost is selected as the target index combination from the multiple sets of index combinations that meet the first preset condition.

[0041] Step S105: Use all the composite secondary indexes in the target index combination to search for the target data.

[0042] Step S106: Combine the found target data according to the common columns to obtain the query results of the SQL statement.

[0043] For example, the SQL statement is `Select a,b,c from t1`, with target columns a, b, and c. For the target index combination `Ix_ab` and `Ix_bc`, the composite secondary indexes `Ix_ab` and `Ix_bc` are used to scan and find the target data in columns a and b, and the target data in column b and c, respectively. Then, the target data in columns a and b are concatenated using the common column b as a base to obtain the data in column abc, which is the result data retrieved by the SQL statement.

[0044] When the common column is unique, the target data retrieved by each composite secondary index can be directly concatenated based on the column values ​​of the common column. Therefore, each composite secondary index can retrieve only the column data of the index column. However, when the column values ​​of the common column in the target index combination are not unique, the steps for retrieving the target data using all composite secondary indexes in the target index combination include: extracting the corresponding column data and row locators from each composite secondary index as the target data.

[0045] In other words, when the column values ​​of the common column in the target index combination are not unique, while using each composite secondary index to find the column data of the index column, it is necessary to extract the row locator of the row containing the column data. In this way, when concatenating using the common column, even if multiple rows of data with the same column value appear, the concatenation method can be determined based on the row locator.

[0046] In this embodiment, the target table and all target query columns of the SQL statement are extracted, and all composite secondary indexes of the target table are checked based on all target query columns. If an index combination that meets a first preset condition exists among all composite secondary indexes of the target table, a group of these index combinations is determined as the target index combination. The target data is then searched using all composite secondary indexes within the target index combination, and the searched target data is combined according to a common column to obtain the query result of the SQL statement. The first preset condition is that the concatenation of multiple composite secondary indexes in the index combination according to a common column covers all target query columns. In other words, if an index combination that meets the first preset condition exists among all composite secondary indexes of the target table, by using the common column as the join basis, the required data can be found entirely using the secondary indexes. Then, the data found by each secondary index is concatenated to obtain the final query result, thereby avoiding table lookup operations, effectively improving query efficiency, and reducing the consumption of runtime resources.

[0047] Furthermore, if multiple index combinations meeting the first preset condition exist among all composite secondary indexes of the target table, the uniqueness of the column values ​​in the common column of these multiple index combinations is checked. If an index combination with unique common column values ​​is found, one such combination is selected as the target index combination. Because a unique common column value allows for direct determination of the unique concatenation relationship without referencing other elements, prioritizing the use of index combinations with unique common column values ​​among all composite secondary indexes of the target table simplifies subsequent search and concatenation operations, thereby improving query efficiency.

[0048] Furthermore, if multiple index combinations with unique values ​​in common columns are found, the index combination with the lowest execution cost is selected as the target index combination. Similarly, if no index combination with unique values ​​in common columns exists among multiple index combinations meeting the first preset condition, the index combination with the lowest execution cost is selected as the target index combination. Using the index combination with the lowest execution cost helps improve query efficiency.

[0049] like Figure 3As shown, in one embodiment, after checking all composite secondary indexes of the target table according to all target query columns, if there is no index combination that meets the first preset condition among all composite secondary indexes of the target table, but there is an index combination that meets the second preset condition among all composite secondary indexes of the target table, the database query optimization method based on secondary indexes generally includes: Step S301: Select a set of index combinations that meet the second preset condition as the target index combination. The second preset condition is that multiple composite secondary indexes in the index combination are concatenated according to a common column to cover part of the target query column.

[0050] For example, the SQL statement is `Select a,b,c,d from t1`, with the target query columns being a, b, c, and d. The target table t1 has composite secondary indexes Ix_ab and Ix_bc. These two indexes share a common column b. When concatenated according to this common column b, the resulting column set (a, b, c) covers part of the target query columns. Therefore, the composite secondary indexes Ix_ab and Ix_bc are an index combination that meets the second preset condition.

[0051] If only one set of index combinations meets the second preset condition, it is determined as the target index combination. If multiple sets of index combinations meet the second preset condition, one set is selected as the target index combination. The specific method for determining this is similar to the method described earlier for determining a target index combination from index combinations meeting the first preset condition; that is, using the uniqueness of the column values ​​in the common column and the execution cost to determine the target index combination.

[0052] Step S302: Use all the composite secondary indexes in the target index combination to find the corresponding column data and row locators, and use them as the first data.

[0053] Specifically, because there are still target query columns that are not covered by the index, a table lookup is needed later to obtain the column data of the target query columns that are not covered by the index. The column data of the target query columns that are not covered by the index needs to be found based on the row locator. Therefore, the corresponding row locator is obtained at the same time as the column data.

[0054] Step S303: Combine the found first data according to the common column to obtain the first target data.

[0055] Specifically, this means combining the column data of the target query column covered by the composite secondary index in the target index combination to obtain the row data of the target query column that is missing and not covered by the index.

[0056] Step S304: Based on the row locator of the first target data, search for the second target data in the target query column that is not covered by the target index combination.

[0057] Specifically, it means retrieving the column data of the target query column of the corresponding row in the target table based on the row locator, which is the second target data.

[0058] Step S305: Combine the first target data and the second target data to obtain the query result of the SQL statement.

[0059] Specifically, the first target data and the second target data are combined according to the row locator, so that the first target data and the second target data belonging to the same row are combined together, which yields the result data to be queried by the SQL statement.

[0060] In this embodiment, under the condition that no index combination meeting the first preset condition exists among all composite secondary indexes of the target table, but an index combination meeting the second preset condition exists, a group of index combinations meeting the second preset condition is determined as the target index combination. The second preset condition is that multiple composite secondary indexes in the index combination are concatenated according to a common column to cover part of the target query column. The corresponding column data and row locators are found using all composite secondary indexes in the target index combination, serving as the first data. The found first data is combined according to the common column to obtain the first target data. Based on the row locators of the first target data, the second target data is found in the target query column not covered by the target index combination. The first target data and the second target data are combined to obtain the query result of the SQL statement. In other words, under the condition that an index combination meeting the second preset condition exists among all composite secondary indexes of the target table, by using the common column as the join basis, most of the required column data can be found using secondary indexes, effectively reducing the amount of column data requiring table lookups, improving query efficiency, and reducing the consumption of runtime resources.

[0061] like Figure 4 As shown, in one embodiment, before the step of determining a set of index combinations as the target index combination from the index combinations that meet the second preset conditions, the steps include: estimating the number of rows returned by the SQL statement; determining whether the ratio of the number of returned rows to the total number of rows in the target table is greater than a preset threshold; if not, performing the step of determining a set of index combinations as the target index combination from the index combinations that meet the second preset conditions.

[0062] Specifically, in this embodiment, the database query optimization method based on secondary indexes generally includes: Step S401: Estimate the number of rows returned by the SQL statement.

[0063] Specifically, the optimizer can estimate the number of rows that an SQL statement will return based on statistics of the target table.

[0064] Step S402: Determine whether the ratio of the number of returned rows to the total number of rows in the target table is greater than a preset threshold. If yes, proceed to step S403; otherwise, proceed to step S404.

[0065] Step S403: Process the SQL statement using the conventional query method.

[0066] Step S404: Determine a set of index combinations that meet the second preset conditions as the target index combination.

[0067] Specifically, because the query uses index combinations, data concatenation is required, resulting in additional concatenation overhead. If the ratio of the number of returned rows to the total number of rows in the target table exceeds a preset threshold, it indicates a large number of returned rows. The concatenation overhead is already significant; if a table lookup operation is added, although the amount of data retrieved through the table lookup is reduced, it will still exceed the overhead of a direct full table scan or a single index with a table lookup. Therefore, it is best to process SQL statements using conventional query methods, that is, query methods known to those skilled in the art, such as full table scans or single index with a table lookup.

[0068] If the ratio of the number of returned rows to the total number of rows in the target table is not greater than the preset threshold, it means that the number of returned rows is small. At this time, the splicing overhead is small, and the total cost is much less than a full table scan or adding a single index back to the table. Therefore, the step of determining a set of index combinations that meet the second preset condition as the target index combination is executed again.

[0069] Before determining a target index combination from index combinations that meet the second preset condition, the number of rows returned by the SQL statement is estimated. If the ratio of the returned rows to the total number of rows in the target table does not exceed a preset threshold, then the step of determining a target index combination from index combinations that meet the second preset condition is executed. If the target index combination cannot completely cover all target query columns, it is initially determined that the join cost will subsequently outweigh the table lookup cost of the saved columns, thus helping to adopt a query method with lower overhead.

[0070] This embodiment also provides a computer device and a computer-readable storage medium. Figure 5 This is a schematic diagram of a computer device 10 according to an embodiment of the present invention. Figure 6 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention.

[0071] Computer device 10 may include memory 110, processor 120, and computer executable program 11 stored on memory 110 and running on processor 120. When processor 120 executes computer executable program 11, it implements the database query optimization method based on secondary index of any of the above embodiments.

[0072] The computer-readable storage medium 20 stores a computer-executable program 11 thereon, which, when executed by a processor, implements the database query optimization method based on a secondary index according to any of the above embodiments.

[0073] This embodiment also provides a computer program product. Figure 7 This is a schematic diagram of a computer program product 30 according to an embodiment of the present invention. The computer program product 30 includes a computer executable program 11, which, when executed by a processor 120, implements any of the database query optimization methods based on secondary indexes described above.

[0074] Specifically, the computer executable program 11 used to perform the operations of the present invention may be assembly instructions, instruction set architecture (ISA) instructions, computer instructions, computer-related instructions, microcode, firmware instructions, status setting data, or source code or object code written in any combination of one or more programming languages.

[0075] For the purposes of this embodiment, the computer-readable storage medium 20 can be any means capable of containing, storing, communicating, propagating, or transmitting a program for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable media include: an electrical connection (electronic device) having one or more wires, a portable computer disk drive (magnetic device), random access memory (RAM), read-only memory (ROM), erasable and editable read-only memory (EPROM or flash memory), fiber optic devices, and portable optical disc read-only memory (CDROM). Furthermore, the computer-readable storage medium 20 can even be paper or other suitable media on which the program can be printed, since the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting, or otherwise processing as necessary, and then stored in a computer memory.

[0076] It should be understood that various parts of the present invention can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0077] Computer device 10 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 10 can be a cloud acquisition node. Computer device 10 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 10 can be implemented in a distributed cloud acquisition environment where tasks are performed by remote processing devices linked via a communication network. In a distributed cloud acquisition environment, program modules can reside on local or remote acquisition system storage media, including storage devices.

[0078] Computer device 10 may include a processor 120 adapted to execute stored instructions and a memory 110 that provides temporary storage space for the operation of said instructions during operation. Processor 120 may be a single-core processor, a multi-core processor, an acquisition cluster, or any other configuration. Memory 110 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0079] The processor 120 can be connected via a system interconnect (e.g., PCI, PCI-Express, etc.) to an I / O interface (input / output interface) suitable for connecting the computer device 10 to one or more I / O devices (input / output devices). I / O devices may include, for example, a keyboard and indicating devices, where indicating devices may include a touchpad or touchscreen, etc. I / O devices may be built into the computer device 10 or may be external devices connected to the acquisition device.

[0080] The processor 120 may also be linked via a system interconnect to a display interface suitable for connecting the computer device 10 to a display device. The display device may include a display screen that is a built-in component of the computer device 10. The display device may also include an external computer monitor, television, or projector connected to the computer device 10. Furthermore, a network interface controller (NIC) may be adapted to connect the computer device 10 to a network via a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an Internet Minicomputer System Interface) to transmit data. The network may be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. Remote devices may connect to the computer device via the network.

[0081] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A database query optimization method based on secondary indexes, comprising: Obtain the SQL statement, which is a single-table multi-column projection query; Extract the target table and all target query columns of the SQL statement; Examine all composite secondary indexes of the target table based on all the target query columns; If an index combination that meets the first preset condition is found among all the composite secondary indexes of the target table, a group is determined from the index combination that meets the first preset condition as the target index combination. The first preset condition is that the multiple composite secondary indexes in the index combination are concatenated according to the common column to cover all the target query columns. Use all the composite secondary indexes in the target index combination to find the target data; The found target data is combined according to common columns to obtain the query results of the SQL statement.

2. The database query optimization method based on a secondary index according to claim 1, wherein... Given that multiple index combinations satisfying the first preset condition exist among all composite secondary indexes of the target table, the step of determining a target index combination from the index combinations satisfying the first preset condition includes: Check the uniqueness of column values ​​in the common column of the multiple sets of index combinations that meet the first preset condition; If, upon checking that there is an index combination among the multiple sets of index combinations that meet the first preset condition, a set is determined from the index combinations with unique column values ​​in the common column as the target index combination.

3. The database query optimization method based on a secondary index according to claim 2, wherein... Given that multiple sets of index combinations with unique column values ​​are found, the step of determining a set as the target index combination from the index combinations with unique column values ​​of the common columns includes: The target index combination is selected from the index combinations where the column values ​​of the common column are unique, and the combination with the lowest execution cost is selected as the target index combination.

4. The database query optimization method based on a secondary index according to claim 2, wherein... If, upon checking that there are no index combinations among the multiple sets of index combinations that meet the first preset condition, the set with the lowest execution cost is selected as the target index combination.

5. The database query optimization method based on a secondary index according to claim 4, wherein... When the column values ​​of the common column in the target index combination are not unique, the step of using all the composite secondary indexes in the target index combination to find the target data includes: Extract the corresponding column data and row locators from all composite secondary indexes, and use them as the target data.

6. The database query optimization method based on a secondary index according to claim 1, wherein... If there is no index combination that meets the first preset condition among all the composite secondary indexes of the target table, but there is an index combination that meets the second preset condition among all the composite secondary indexes of the target table, then a group of index combinations that meet the second preset condition is determined as the target index combination. The second preset condition is that multiple composite secondary indexes in the index combination are concatenated according to a common column to cover part of the target query column. Use all the composite secondary indexes in the target index combination to find the corresponding column data and row locators, which are then used as the first data. The first data found is combined according to common columns to obtain the first target data; Based on the row locator of the first target data, search for the second target data in the target query column that is not covered by the target index combination; The query result of the SQL statement is obtained by combining the first target data and the second target data.

7. The database query optimization method based on a secondary index according to claim 6, wherein... Before the step of determining a target index combination from the index combinations that meet the second preset condition, the following steps are included: Estimate the number of rows returned by the SQL statement; Determine whether the ratio of the number of returned rows to the total number of rows in the target table is greater than a preset threshold. If not, execute the step of determining a set of index combinations as the target index combination from the index combinations that meet the second preset condition.

8. A computer device comprising a memory, a processor, and a computer-executable program stored in the memory and running on the processor, wherein the processor, when executing the computer-executable program, implements the database query optimization method based on a secondary index according to any one of claims 1 to 7.

9. A computer-readable storage medium having a computer-executable program stored thereon, wherein the computer-executable program, when executed by a processor, implements the database query optimization method based on a secondary index according to any one of claims 1 to 7.

10. A computer program product comprising a computer executable program, wherein the computer executable program, when executed by a processor, implements the database query optimization method based on a secondary index according to any one of claims 1 to 7.

Images