Query optimization method of database and related product

Abstract

The invention provides a database query optimization method and a related product. The query optimization method comprises the following steps: analyzing an input query instruction to generate a dynamic sampling instruction; executing the dynamic sampling instruction to dynamically sample the base table corresponding to the query instruction and the index of the base table to obtain a statistical result; on the basis of the statistical result, calculating the expected operation cost of the query instruction for performing query according to the access path based on the full-table scanning and the access path based on the index scanning; and performing data query by taking the access path corresponding to the minimum one of the expected operation costs as a target access path of the query instruction. According to the method, the sample coverage degree of dynamic sampling can be improved, the optimal access path can be determined in more access paths, and the effects of improving the database query performance and reducing the query overhead are achieved.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a database query optimization method, a computer-readable storage medium, a computer program product, and a computer device. Background Technology

[0002] The execution of a data query statement requires sequential processing through the parser, optimizer, and executor stages before finally returning the query result. Currently, existing relational database systems typically estimate the execution cost based on statistical information during the optimizer stage to select the optimal execution plan. Statistical information generation techniques are divided into two types: static sampling and dynamic sampling. Static sampling, such as histograms and Most Common Values ​​(MCV), collects the data distribution across each column of a data table. This static statistical information is usually collected periodically and may not reflect the actual data distribution in a timely manner. Especially in scenarios with frequent data updates, the timeliness issue of static statistical information is more prominent, resulting in poor timeliness of statically sampled data.

[0003] Dynamic sampling involves collecting statistical information needed for a query statement (such as an SQL statement) during query execution, and the information collected is more timely than that obtained through static sampling. However, in related technologies, dynamic sampling has poor sample coverage, which can easily lead to the omission of the optimal access path when using the sampled information to estimate the running cost of each access path, resulting in higher query operation overhead. Summary of the Invention

[0004] One object of the present invention is to provide a database query optimization method, a computer-readable storage medium, a computer program product, and a computer device, which enables simultaneous dynamic sampling of the base tables and their indexes involved in the query command to improve the sample coverage of dynamic sampling, thereby determining the optimal access path among more access paths, improving database query performance, and reducing query overhead.

[0005] Specifically, according to one aspect of the present invention, the present invention provides a database query optimization method, comprising:

[0006] The input query command is analyzed to generate a dynamic sampling command;

[0007] Execute the dynamic sampling instruction to dynamically sample the base table and its indexes corresponding to the query instruction and obtain statistical results;

[0008] Based on the statistical results, the expected running costs of the query command when querying according to the access path based on a full table scan and the access path based on an index scan are calculated respectively.

[0009] The access path corresponding to the lowest of the expected operating costs is used as the target access path for the query instruction, and the data is queried.

[0010] Optionally, the analysis of the input query command to generate a dynamic sampling command includes:

[0011] Parse the query command to obtain the filtering conditions for the base table;

[0012] Based on the filtering conditions, generate a full table scan instruction for the base table and an index scan instruction for the index; and

[0013] Based on the filtering conditions, the sampling ratios of the full table scan command and the index scan command are determined respectively;

[0014] The dynamic sampling instruction is obtained based on the full table scan instruction and its sampling ratio, and the index scan instruction and its sampling ratio.

[0015] Optionally, generating a full table scan instruction for the base table and an index scan instruction for the index based on the filtering conditions includes:

[0016] Based on the overhead of the query command, determine whether dynamic sampling is required. The overhead includes the size of the base table and the complexity of the filtering conditions.

[0017] If necessary, based on the filtering conditions, generate a full table scan SQL segment for generating the full table scan instruction and an index scan SQL segment for generating the index scan instruction;

[0018] If not required, calculate the expected runtime cost of the access path based on the full table scan.

[0019] Optionally, after determining that dynamic sampling is required based on the cost corresponding to the query instruction, the method further includes:

[0020] Based on the filtering conditions, the full table scan SQL segment is generated;

[0021] Determine if the base table has a usable index;

[0022] If it exists, then the index scan SQL segment is generated according to the filtering conditions.

[0023] Optionally, determining the sampling ratios of the full table scan instruction and the index scan instruction based on the filtering conditions includes:

[0024] Obtain the size of the base table and the index;

[0025] Analyze the complexity of the filtering conditions, and determine the target number of samples for the base table and the index based on the complexity of the filtering conditions.

[0026] Based on the size of the base table and its target number of samples, the sampling ratio of the full table scan instruction is obtained; and

[0027] The sampling ratio of the index scan instruction is obtained based on the size of the index and its target number of samples.

[0028] Optionally, the step of calculating the expected execution cost of the query instruction based on the statistical results, using the access path based on a full table scan and the access path based on an index scan, includes:

[0029] Based on the full table sampling statistics and index sampling statistics in the statistical results, estimate the expected cardinality of the full table scan and the expected cardinality of the index scan, respectively.

[0030] Based at least on the expected cardinality, expected CPU execution cost, and expected I / O cost of the full table scan, estimate the expected execution cost of the access path based on the full table scan; and

[0031] The expected operating cost of the access path based on the index scan is estimated based at least on the expected cardinality, expected CPU operating cost, and expected I / O cost of the index scan.

[0032] Optionally, the step of using the access path corresponding to the minimum of the expected operating costs as the target access path for the query instruction to perform data query includes:

[0033] Among the various access paths, the one with the lowest expected operating cost is determined as the target access path;

[0034] Generate an SQL query statement corresponding to the target access path;

[0035] Use the SQL query statement to retrieve data.

[0036] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer program is stored, wherein the computer program, when executed by a processor, implements the steps of any of the above-described database query optimization methods.

[0037] According to another aspect of the present invention, a computer program product is also provided, comprising a computer program that, when executed by a processor, implements the steps of any of the above-described database query optimization methods.

[0038] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a computer program stored in the memory, wherein the processor executes the computer program to implement the steps of the database query optimization method described above.

[0039] The database query optimization method of the present invention improves the sample coverage of dynamic sampling by performing comprehensive dynamic sampling of full table scans and index scans, thereby increasing the types and number of optional access paths. This enables the determination of the optimal access path among more access paths, thereby improving database query performance and reducing query overhead.

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

[0041] The following sections will describe some specific embodiments of the invention in a detailed manner 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:

[0042] Figure 1 This is a flowchart illustrating a query optimization method according to an embodiment of the present invention;

[0043] Figure 2 This is a flowchart illustrating the generation of dynamic sampling instructions in a query optimization method according to an embodiment of the present invention.

[0044] Figure 3 This is a flowchart illustrating the process of determining whether to perform dynamic sampling in a query optimization method according to an embodiment of the present invention.

[0045] Figure 4 This is a flowchart illustrating the process of generating an index scanning SQL segment in a query optimization method according to an embodiment of the present invention;

[0046] Figure 5 This is a flowchart illustrating the calculation of the sampling ratio in a query optimization method according to an embodiment of the present invention;

[0047] Figure 6 This is a flowchart illustrating the estimation of expected running costs in a query optimization method according to an embodiment of the present invention.

[0048] Figure 7 This is a schematic diagram of the data query process according to a query optimization method of an embodiment of the present invention;

[0049] Figure 8This is a flowchart illustrating a query optimization method according to another embodiment of the present invention;

[0050] Figure 9 This is a schematic diagram of a computer program product according to an embodiment of the present invention;

[0051] Figure 10 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and

[0052] Figure 11 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0053] The purpose of the database query optimization method in this embodiment is to simultaneously perform dynamic sampling on the base tables and their indexes involved in the query command, so as to improve the sample coverage of dynamic sampling, thereby determining the optimal access path among more access paths, improving database query performance, and reducing query overhead.

[0054] Figure 1 This is a flowchart illustrating a database query optimization method according to an embodiment of the present invention. The method generally includes:

[0055] S100 analyzes the input query command and generates a dynamic sampling command;

[0056] S200: Execute the dynamic sampling instruction to dynamically sample the base table and its indexes corresponding to the query instruction and obtain statistical results;

[0057] S300, based on statistical results, calculates the expected running cost of querying the command according to the access path based on a full table scan and the access path based on an index scan, respectively;

[0058] S400 uses the access path corresponding to the one with the lowest expected operating cost as the target access path for the query instruction to perform data query.

[0059] Dynamic sampling is an important technique for database query optimization. It's a statistical collection method used to estimate the data distribution of tables in a database system. When traditional methods (such as static sampling) become inaccurate or inefficient (e.g., when dealing with very large datasets or frequent updates), dynamic sampling can provide more accurate statistical information, helping the database system's query optimizer make better decisions.

[0060] In related technologies, dynamic sampling during query optimization often suffers from poor sample coverage, leading to the potential omission of optimal access paths and consequently, a low cost-effectiveness ratio (low query performance and high query overhead). Specifically, dynamic sampling typically focuses only on statistics of access paths based on full table scans, ignoring statistics of access paths based on index scans. This results in the failure to estimate the expected cost of index-based access paths when indexes are available, leading to the incorrect selection of full table scan-based access paths for the query.

[0061] In this embodiment, upon receiving a query instruction (e.g., a query statement), the system first analyzes the user's query instruction, extracts key filtering conditions, and then automatically converts them into selection conditions required for the sampling query. These selection conditions include access conditions for the base tables involved in the query instruction and access conditions for the indexes of those base tables. Then, based on the selection conditions required for the query, a dynamic sampling instruction is generated to estimate the cardinality of the involved base tables and the cardinality of the base table indexes.

[0062] Next, the dynamic sampling instruction is executed, simultaneously sampling for both full table scans and index scans, yielding sampling results. These results are statistical findings based on the sampled data, allowing estimation of the cardinality of the base tables and their indexes involved in the query instruction. On one hand, comprehensive dynamic sampling of both full table scans and index scans improves sample coverage, ensuring no access paths are missed and increasing the variety and number of available access paths. On the other hand, because both full table scans and index scans are sampled simultaneously, the system only needs to sample once to evaluate the effectiveness of multiple access methods, saving the overhead of repeated sampling and thus reducing query overhead.

[0063] Next, based on the estimated cardinality, the expected execution cost of queries for each access path based on a full table scan and each access path based on an index scan is estimated. By simultaneously evaluating multiple access paths, the system can more accurately select the optimal path, thereby choosing the most cost-effective solution, improving database query performance, and reducing query overhead.

[0064] Expected execution cost quantifies and compares the costs of various possible query execution strategies. For example, expected execution cost can consider multiple aspects such as expected CPU execution cost, expected I / O cost, number of data block reads, and memory usage. By comparing the expected execution cost of each access path, the access path with the lowest expected execution cost is selected as the final execution plan. The system then generates the corresponding SQL statement to ensure that the query is executed along the selected path.

[0065] The query optimization method of this invention improves the sample coverage of dynamic sampling by performing comprehensive dynamic sampling on full table scans and index scans, thereby increasing the types and number of optional access paths. This enables the determination of the optimal access path among more access paths, thereby improving database query performance and reducing query overhead.

[0066] In some embodiments of the query optimization method of the present invention, such as Figure 2 As shown, the analysis of the input query command to generate a dynamic sampling command includes:

[0067] S111, parse the query command to obtain the filtering conditions for the base table;

[0068] S113, Based on the filtering conditions, generate a full table scan command for the base table and an index scan command for the index; and

[0069] S115, Based on the filtering conditions, determine the sampling ratio of the full table scan command and the index scan command respectively;

[0070] S117: Based on the full table scan instruction and its sampling ratio, and the index scan instruction and its sampling ratio, a dynamic sampling instruction is obtained.

[0071] In this embodiment, the system can automatically parse the user's query statement and extract the filtering conditions for the base table. These conditions may include equality conditions (e.g., id=1), range conditions (e.g., age>18), etc. Then, it is automatically converted into selection conditions required for dynamic sampling queries. Selection conditions include access conditions for the base table and access conditions for the indexes. Specifically, based on the extracted selection conditions, the system constructs a full table scan instruction for the base table, i.e., generates a full table scan SQL segment involving selection conditions for base table cardinality estimation. And based on the extracted selection conditions, it constructs an index scan instruction for the index, i.e., generates an index scan SQL segment involving selection conditions for index cardinality estimation. This allows for simultaneous evaluation of the runtime costs of both full table scans and index scans.

[0072] The next step is to calculate the sampling ratio for dynamic sampling, including the sampling ratio for full table scan commands and the sampling ratio for index scan commands. The sampling ratio is calculated from the sampling block size and the total block size of the table.

[0073] Then, the final dynamic sampling instruction is generated, integrating the previously generated full table scan SQL segment, index scan SQL segment, sampling ratio, etc., into a complete dynamic sampling statement. This statement will execute dynamic sampling for both the full table scan and the index scan simultaneously.

[0074] In some embodiments of the query optimization method of the present invention, such as Figure 3As shown, the process of generating full table scan instructions for the base table and index scan instructions for the index based on the filtering conditions includes:

[0075] S131, Based on the overhead of the query command, determine whether dynamic sampling is required. The overhead includes the size of the base table and the complexity of the filtering conditions.

[0076] S133, if necessary, generate a full table scan SQL segment for generating a full table scan instruction and an index scan SQL segment for generating an index scan instruction, based on the filtering conditions.

[0077] S135, if not required, calculate the expected runtime cost of the access path based on a full table scan.

[0078] In this embodiment, the system automatically determines whether dynamic sampling is required. Specifically, the system automatically assesses the overhead of the query instruction based on factors such as the size of the base table and the complexity of the selection conditions extracted from the query statement, thereby determining whether sampling is necessary. The complexity can be determined by comprehensively weighing and quantifying the types and number of filtering conditions. For example, when there are few filtering conditions, and they only include simple equality conditions, the complexity is considered low. When there are many filtering conditions, and they include complex expressions or conditions involving multiple columns, the complexity is considered high.

[0079] For example, for very small tables or low-complexity filtering conditions, the system determines that the overhead is minimal and can directly perform a full table scan query without sampling, avoiding the additional overhead of dynamic sampling when unnecessary. For very large tables or high-complexity filtering conditions, the system determines that the overhead is significant and will automatically trigger dynamic sampling, then select the optimal access path for data querying based on the dynamic sampling results. Although using dynamic sampling incurs additional overhead, using the optimal access path for data querying saves a significant amount of overhead, reducing overall query cost and improving query performance.

[0080] In particular, this solution automatically determines whether dynamic sampling is needed based on trigger conditions, and can automatically adapt to different data distributions and query patterns without manual intervention or optimization, thereby improving the system's adaptability and reducing the workload of database maintenance.

[0081] In some embodiments of the query optimization method of the present invention, such as Figure 4 As shown, after determining whether dynamic sampling is needed based on the cost of the query command, the process also includes:

[0082] S141, Generate a full table scan SQL segment based on the filtering conditions;

[0083] S143, Determine if a usable index exists in the base table;

[0084] S145, if it exists, then generate an index scan SQL segment based on the filtering conditions.

[0085] In this embodiment, the system automatically checks whether an available index exists on the base table. If an index matching the query conditions exists, an index scan SQL segment using that index is generated, and dynamic sampling of both a full table scan and an index scan is performed simultaneously. If no available index exists, only a full table scan SQL segment is generated, and dynamic sampling of the full table scan is performed.

[0086] In some embodiments of the query optimization method of the present invention, such as Figure 5 As shown, determining the sampling ratio of full table scan commands and index scan commands based on the filtering conditions includes:

[0087] S151, get the size of the base table and indexes;

[0088] S153, Analyze the complexity of the filtering conditions, and determine the target number of samples for the base table and index based on the complexity of the filtering conditions.

[0089] S155, based on the size of the base table and its target number of samples, obtains the sampling ratio of the full table scan command; and

[0090] S157, based on the size of the index and its target number of samples, obtain the sampling ratio of the index scan instruction.

[0091] The sampling ratio is calculated from the sampling block size and the total block size of the table. For example, the formula can be: Sampling Ratio = Sampling Block Size / Total Block Size of Table. The sampling block size is dynamically adjusted based on the complexity of the query filtering conditions. The complexity can be determined by comprehensively weighing and quantifying the types and number of filtering conditions. For instance, when there are few filtering conditions, and they only include simple equality conditions, the complexity is considered low. When there are many filtering conditions, and they include complex expressions or conditions involving multiple columns, the complexity is considered high. When the complexity of the filtering conditions is high, the system will correspondingly increase the sampling block size to improve the estimation accuracy.

[0092] In this embodiment, by dynamically adjusting the sampling ratio according to the complexity of the filtering conditions, the database system can obtain a more accurate cardinality estimate under various data distribution scenarios, thereby providing a more accurate cardinality estimate for estimating the expected running cost and helping to select a better execution plan.

[0093] In some embodiments of the query optimization method of the present invention, such as Figure 6As shown, based on statistical results, the expected execution cost of querying the command according to the access path based on a full table scan and the access path based on an index scan is calculated, including:

[0094] S311, based on the full table sampling statistics and index sampling statistics in the statistical results, estimate the expected cardinality of the full table scan and the expected cardinality of the index scan respectively;

[0095] S313, at least based on the expected base number, expected CPU cost, and expected I / O cost of a full table scan, estimate the expected cost of the access path based on the full table scan; and

[0096] S315 estimates the expected operating cost of the access path based on the index scan, at least based on the expected cardinality, expected CPU operating cost, and expected I / O cost of the index scan.

[0097] Dynamic sampling can utilize methods such as random sampling, stratified sampling, and systematic sampling. Random sampling yields samples that are more representative of the base table data. Stratified sampling divides the data into multiple strata based on its distribution characteristics, sampling from each stratum separately; this is suitable for scenarios with uneven data distribution. Systematic sampling selects samples at fixed intervals and is effective when the data distribution is relatively uniform.

[0098] Expected cardinality refers to the process in a database management system of predicting the proportion of records returned for a given query condition or predicate relative to the total number of records. This process is crucial for query optimization because it helps the database system determine the optimal access path and execution plan. In this embodiment, based on the sampling ratio, the cardinality estimate of the complete dataset can be calculated, i.e., the expected cardinality of a full table scan and the expected cardinality of an index scan. For example, the formula for calculating the cardinality estimate for a certain filter condition is: Cardinality estimate = Actual number of rows obtained after filtering the sampled results through this condition / Total actual number of rows obtained by the sampled results * Total number of rows in the table.

[0099] The expected execution cost is typically calculated using a cost model. A cost model is a mechanism in database management systems used to evaluate the cost of different execution plans. Through a cost model, the costs of various possible query execution strategies (such as CPU time consumption, number of I / O operations, etc.) can be quantified and compared. In this embodiment, based on the baseline estimated from statistical results, and combined with factors such as expected CPU execution cost and expected I / O cost, the overall expected execution cost of each access path is calculated. This incorporates multiple cost factors such as the number of data block reads and memory usage into the expected execution cost, making it more comprehensive and accurate in reflecting all overhead in the actual data query process. This helps in selecting the most cost-effective solution and improving query performance and resource utilization.

[0100] In some embodiments of the query optimization method of the present invention, such as Figure 7 As shown, the method of using the access path corresponding to the minimum expected operating cost among all options as the target access path for the query instruction to perform data query includes:

[0101] S411, determine the path with the lowest expected running cost among all access paths and use it as the target access path;

[0102] S413, Generate an SQL query statement corresponding to the target access path;

[0103] S415 uses SQL query statements to retrieve data.

[0104] In this embodiment, the system compares the costs of various access paths and selects the access path with the lowest cost as the final execution plan. Next, the system automatically generates the corresponding SQL query statement to ensure that the query is executed according to the selected target access path.

[0105] In some embodiments of the query optimization method of the present invention, such as Figure 8 As shown, query optimization methods include:

[0106] S511, obtain the query command and generate the access conditions for the corresponding base table;

[0107] S512 generates multiple access paths to the base table corresponding to the query command;

[0108] S513, generates a full table scan SQL code segment that generates access conditions;

[0109] S514, generates an index scan SQL code segment for access conditions;

[0110] S515, calculate the sampling ratio;

[0111] S516 generates the final dynamic sampling SQL statement;

[0112] S517 performs dynamic sampling;

[0113] S518 returns the expected cardinality for multiple access paths;

[0114] S519, Estimate the expected running cost of multiple access paths;

[0115] S520, determine the target access path;

[0116] S521, perform data query according to the target access path.

[0117] The flowchart provided in this embodiment is 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.

[0118] It should be understood that in some embodiments, the components may be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods may be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0119] This invention also provides a computer program product 10, a computer-readable storage medium 20, and a computer device 30. Figure 9 This is a schematic diagram of a computer program product 10 according to an embodiment of the present invention. Figure 10 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. Figure 11 This is a schematic diagram of a computer device 30 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which, when executed by the processor 32, implements the steps of any of the above-described query optimization methods. A computer-readable storage medium 20 stores the computer program 11 thereon, which, when executed by the processor 32, implements the steps of the query optimization method of any of the above-described embodiments. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored in the memory 31 and running on the processor 32.

[0120] The computer program 11 used to perform the operations of this invention may be assembly instructions, Instruction Set Architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, status setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages ​​and procedural programming languages. The computer program 11 may execute 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 the latter case, the remote computer may be connected to the user's computer via any type of network, including a Local Area Network (LAN) or Wide Area Network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, to perform aspects of this invention, electronic circuits, including, for example, programmable logic circuits, Field-Programmable Gate Arrays (FPGAs), or Programmable Logic Arrays (PLAs), may execute computer-readable program instructions using status information from computer-readable program instructions to personalize the electronic circuits.

[0121] For the purposes of this embodiment, computer program product 10 is a related product that includes computer program 11.

[0122] For the purposes of this embodiment, the computer-readable storage medium 20 is a tangible device capable of holding and storing a computer program 11. It can be any device capable of containing, storing, communicating, propagating, or transmitting the computer program 11 for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of the computer-readable storage medium 20 include: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable optical disc read-only memory (CD-ROM), digital versatile disc (DVD), memory stick, floppy disk, mechanical encoding device, and any suitable combination thereof.

[0123] Computer device 30 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 30 can be a cloud computing node. Computer device 30 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 30 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can reside on local or remote computing system storage media, including storage devices.

[0124] Computer device 30 may include a processor 32 adapted to execute stored instructions and a memory 31 that provides temporary storage space for the operation of said instructions during operation. The processor 32 may be a single-core processor, a multi-core processor, a computing cluster, or any other configuration. The memory 31 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0125] Computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows external devices that can be connected to the computer device to input and output data. The network adapter / interface provides communication between the computer device and a network, typically represented as a communication network.

[0126] 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, characterized in that, include: The input query command is analyzed to generate a dynamic sampling command; Execute the dynamic sampling instruction to dynamically sample the base table and its indexes corresponding to the query instruction and obtain statistical results; Based on the statistical results, the expected running costs of the query command when querying according to the access path based on a full table scan and the access path based on an index scan are calculated respectively. The access path corresponding to the lowest of the expected operating costs is used as the target access path for the query instruction, and the data is queried.

2. The query optimization method according to claim 1, characterized in that, The process of analyzing the input query command and generating a dynamic sampling command includes: Parse the query command to obtain the filtering conditions for the base table; Based on the filtering conditions, generate a full table scan instruction for the base table and an index scan instruction for the index; and Based on the filtering conditions, the sampling ratios of the full table scan command and the index scan command are determined respectively; The dynamic sampling instruction is obtained based on the full table scan instruction and its sampling ratio, and the index scan instruction and its sampling ratio.

3. The query optimization method according to claim 2, characterized in that, The process of generating a full table scan instruction for the base table and an index scan instruction for the index based on the filtering conditions includes: Based on the overhead of the query command, determine whether dynamic sampling is required. The overhead includes the size of the base table and the complexity of the filtering conditions. If necessary, based on the filtering conditions, generate a full table scan SQL segment for generating the full table scan instruction and an index scan SQL segment for generating the index scan instruction; If not required, calculate the expected runtime cost of the access path based on the full table scan.

4. The query optimization method according to claim 3, characterized in that, After determining that dynamic sampling is required based on the overhead corresponding to the query instruction, the process also includes: Based on the filtering conditions, the full table scan SQL segment is generated; Determine if the base table has a usable index; If it exists, then the index scan SQL segment is generated according to the filtering conditions.

5. The query optimization method according to claim 2, characterized in that, The step of determining the sampling ratio of the full table scan instruction and the index scan instruction based on the filtering conditions includes: Obtain the size of the base table and the index; Analyze the complexity of the filtering conditions, and determine the target number of samples for the base table and the index based on the complexity of the filtering conditions. Based on the size of the base table and its target number of samples, the sampling ratio of the full table scan instruction is obtained; and The sampling ratio of the index scan instruction is obtained based on the size of the index and its target number of samples.

6. The query optimization method according to claim 1, characterized in that, Based on the statistical results, the expected execution cost of the query instruction is calculated using the access path based on a full table scan and the access path based on an index scan, respectively, including: Based on the full table sampling statistics and index sampling statistics in the statistical results, estimate the expected cardinality of the full table scan and the expected cardinality of the index scan, respectively. Based at least on the expected cardinality, expected CPU execution cost, and expected I / O cost of the full table scan, estimate the expected execution cost of the access path based on the full table scan; and The expected operating cost of the access path based on the index scan is estimated based at least on the expected cardinality, expected CPU operating cost, and expected I / O cost of the index scan.

7. The query optimization method according to claim 1, characterized in that, The method of using the access path corresponding to the minimum of the expected operating costs as the target access path for the query instruction to perform data query includes: Among the various access paths, the one with the lowest expected operating cost is determined as the target access path; Generate an SQL query statement corresponding to the target access path; Use the SQL query statement to retrieve data.

8. A computer-readable storage medium, characterized in that, It stores a computer program that, when executed by a processor, implements the steps of the database query optimization method as described in any one of claims 1 to 7.

9. A computer program product, comprising a computer program, characterized in that, When executed by a processor, the computer program implements the steps of the database query optimization method as described in any one of claims 1 to 7.

10. A computer device, characterized in that, The system includes a memory, a processor, and a computer program stored in the memory, wherein the processor executes the computer program to implement the steps of the database query optimization method according to any one of claims 1 to 7.

Images