Database selection rate determination method and device, medium and product

Abstract

The invention provides a database selection rate determination method and device, a medium and a product. The method for determining the selection rate of the database comprises the steps of obtaining a query statement, wherein query conditions of the query statement comprise connection conditions of a foreign key reference table and a foreign key referenced table; searching and storing a reference column filtering condition about a reference column of the foreign key referenced table in the query statement; obtaining an initial foreign key connection selection rate for the foreign key reference table and the foreign key referenced table, wherein the initial foreign key connection selection rate is one fraction of the total row number of the foreign key referenced table; and correcting the initial foreign key connection selection rate according to a reference column filtering condition to obtain a finally used foreign key connection selection rate. Therefore, according to different filtering conditions, the method can be suitable for a more appropriate foreign key connection selection rate, so that the cardinality estimation accuracy of the connection query of the foreign key reference table and the foreign key referenced table is improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method, device, medium and product for determining a selectivity of a database. Background Art

[0002] Structured Query Language (SQL) is a standard database language and a highly versatile database language. In the process of using a database, the execution of an SQL query statement must be optimized to improve query efficiency. In the query optimization process, selectivity, as an important parameter affecting the cardinality estimate, plays an important role in evaluating the execution cost of the query statement. Therefore, how to improve the accuracy of selectivity is one of the important technical issues that need to be paid attention to in order to improve the efficiency of database operation. Summary of the invention

[0003] An object of the present invention is to provide a method, device, medium and product for determining the selectivity of a database that can improve the accuracy of the selectivity of foreign key connections between foreign key referencing tables and foreign key referenced tables.

[0004] In particular, the present invention provides a method for determining a selectivity of a database, comprising:

[0005] Obtain a query statement, wherein the query condition of the query statement includes a connection condition of a foreign key reference table and a foreign key referenced table;

[0006] Find and save the reference column filtering condition of the reference column of the foreign key referenced table in the query statement;

[0007] Obtaining initial foreign key connection selection rates for the foreign key referencing table and the foreign key referenced table, wherein the initial foreign key connection selection rate is one-tenth of the total number of rows in the foreign key referenced table;

[0008] The initial foreign key join selectivity is modified according to the reference column filtering condition to obtain a foreign key join selectivity that is finally used.

[0009] Optionally, the step of correcting the initial foreign key connection selectivity according to the reference column filtering condition includes:

[0010] Obtain the selectivity of the referenced column filter condition in the foreign key referenced table, recorded as filter selectivity;

[0011] The initial foreign key connection selectivity is divided by the filtering selectivity to obtain a revised foreign key connection selectivity.

[0012] Optionally, the step of obtaining the selectivity of the referenced column filter condition in the foreign key referenced table includes:

[0013] Obtaining statistical information of the table referenced by the foreign key;

[0014] A filter selection rate of the filter condition is obtained according to the reference column filter condition and the statistical information.

[0015] Optionally, in the case of multiple reference column filter conditions, the step of dividing the initial foreign key connection selectivity by the filter selectivity includes:

[0016] Traverse and select the remaining reference column filtering conditions;

[0017] Updates the foreign key join selectivity based on the selected reference column filter condition.

[0018] Optionally, the step of updating the foreign key connection selectivity according to the selected reference column filtering condition includes:

[0019] Obtaining statistical information of the table referenced by the foreign key;

[0020] Obtaining a filter selection rate of the reference column filter condition according to the newly selected reference column filter condition and the statistical information;

[0021] The foreign key join selectivity is updated by dividing the foreign key join selectivity corrected by the last reference column filter condition by the filter selectivity.

[0022] Optionally, the step of searching and saving the reference column filtering condition for the reference column of the foreign key referenced table in the query statement includes:

[0023] Traversing all filtering conditions of the query statement;

[0024] It is determined whether the filter condition can be pushed down to the referenced column of the foreign key referenced table. If so, the filter condition is pushed down to the foreign key referenced table to obtain the referenced column filter condition and save it.

[0025] Optionally, the step of pushing the filter condition to the foreign key referenced table to obtain the referenced column filter condition includes:

[0026] A filtering condition whose field name is the referenced column of the foreign key referenced table and whose conditional expression is the same as the filtering condition is generated as the referenced column filtering condition.

[0027] According to another aspect of the present invention, a computer device is also provided, comprising a memory, a processor, and a computer executable program stored in the memory and running on the processor, and when the processor executes the computer executable program, the method for determining the selection rate of the database according to any one of the above items is implemented.

[0028] According to another aspect of the present invention, a computer-readable storage medium is provided, on which a computer-executable program is stored. When the computer-executable program is executed by a processor, the method for determining the selection rate of a database according to any one of the above items is implemented.

[0029] According to another aspect of the present invention, a computer program product is provided, including a computer executable program, and when the computer executable program is executed by a processor, the method for determining the selection rate of a database according to any one of the above items is implemented.

[0030] The method, device, medium and product for determining the selectivity of a database of the present invention obtains the initial foreign key connection selectivity for the foreign key reference table and the foreign key referenced table by searching and saving the reference column filtering condition for the reference column of the foreign key referenced table in the query statement after obtaining the query condition including the connection condition for the foreign key reference table and the foreign key referenced table, and corrects the initial foreign key connection selectivity according to the reference column filtering condition to obtain the foreign key connection selectivity used in the end. That is to say, in the process of performing the connection query of the foreign key reference table and the foreign key referenced table, the foreign key connection selectivity will be corrected according to the filtering condition, and the same foreign key connection selectivity is not used in any case, that is, the foreign key connection selectivity of the foreign key reference table and the foreign key referenced table will not always remain at one-tenth of the total number of rows of the foreign key referenced table. In this way, according to different filtering conditions, a more appropriate foreign key connection selectivity can be applied, thereby improving the accuracy of the cardinality estimation of the connection query of the foreign key reference table and the foreign key referenced table.

[0031] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:

[0033] Figure 1 is a schematic diagram of a method for determining a selection rate of a database according to an embodiment of the present invention;

[0034] Figure 2 It is a schematic diagram of a step of searching and saving a filter condition for a reference column of a foreign key referenced table in a query statement in a method for determining a selectivity of a database according to an embodiment of the present invention;

[0035] Figure 3It is a schematic diagram of a step of correcting an initial foreign key connection selectivity according to a reference column filtering condition in a method for determining a selectivity of a database according to an embodiment of the present invention;

[0036] Figure 4 It is a schematic diagram of a step of obtaining the selectivity of a reference column filter condition in a foreign key referenced table in a method for determining the selectivity of a database according to an embodiment of the present invention;

[0037] Figure 5 is a schematic diagram of a method for determining a selection rate of a database according to another embodiment of the present invention;

[0038] Figure 6 is a schematic diagram of a step of updating a foreign key connection selectivity in a method for determining a selectivity of a database according to another embodiment of the present invention;

[0039] Figure 7 is a schematic diagram of a method for determining a selection rate of a database according to yet another embodiment of the present invention;

[0040] Figure 8 is a schematic diagram of a computer device according to an embodiment of the present invention;

[0041] Fig. 9 is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention;

[0042] Fig.10 is a schematic diagram of a computer program product according to an embodiment of the present invention. DETAILED DESCRIPTION

[0043] It should be understood by those skilled in the art that the embodiments described below are only some embodiments of the present invention, rather than all embodiments of the present invention, and these embodiments are intended to explain the technical principles of the present invention, rather than to limit the protection scope of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should still fall within the protection scope of the present invention.

[0044] It should be noted that the logic and / or steps represented in the flowchart or described in other ways 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 an instruction execution system, device or equipment (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or equipment and execute instructions), or used in combination with these instruction execution systems, devices or equipment.

[0045] The flow chart provided by the present invention is not intended to indicate that the operation of the method will be performed in any particular order, or all operations of the method are included in all every case. In addition, the method may include additional operations. Within the scope of the technical thinking provided by the present embodiment method, additional changes can be made to the above method.

[0046] like Figure 1 As shown, in one embodiment, the method for determining the selectivity of a database generally includes:

[0047] Step S101, obtaining a query statement. The query conditions of the query statement include connection conditions for the foreign key referencing table and the foreign key referenced table.

[0048] Specifically, the foreign key referencing table and the foreign key referenced table are two associated tables in the database, wherein a field in the foreign key referencing table references a field with a unique constraint in the foreign key referenced table. The corresponding field of the foreign key referencing table can be called a foreign key column, and the field of the foreign key referenced table referenced by the foreign key column can be called a reference column. In other words, each data in the foreign key column of the foreign key referencing table must be found uniquely in the reference column of the corresponding foreign key referenced table. The connection condition of the foreign key referencing table and the foreign key referenced table is that the foreign key column of the foreign key referencing table is equal to the reference column of the foreign key referenced table.

[0049] For example, for the order information table and the customer information table, the order information table is used to record the customer's order information, and the customer information table is used to record the customer's related information. Among them, one field in the order information table is the customer ID (Identity document, identity identification), and there is also a field in the customer information table for the customer ID, because in the customer information table, the customer ID of each customer information is unique, and the customer ID in the order information table may not be unique (for example, if a customer has two orders, there are two order information, and the values ​​of the customer ID column of the two order information are the same customer ID value), so the order information table can be called a foreign key reference table, and the customer information table can be called a foreign key referenced table. The customer ID field in the order information table is the foreign key column, and the customer ID in the customer information table is the reference column.

[0050] The order information table is denoted as Table A, and the customer information table is denoted as Table B. Then the join condition of the foreign key reference table and the foreign key referenced table is expressed in the query statement as: Table A.ID = Table B.ID.

[0051] Step S102, searching and saving the reference column filtering condition of the referenced column of the foreign key referenced table in the query statement.

[0052] Reference Figure 2 As shown, this step includes:

[0053] Step S201, traverse all filter conditions of the query statement. The filter condition is the limitation on the field value.

[0054] Step S202, determine whether the filter condition can be pushed down to the referenced column of the foreign key referenced table, if so, execute step S203, if not, execute step S204. Specifically, detect whether the filter condition is for the foreign key column of the foreign key referenced table, if so, determine that the filter condition is a filter condition that can be pushed down to the referenced column of the foreign key referenced table. Because each data of the foreign key column of the foreign key referenced table can be found uniquely in the referenced column of the corresponding foreign key referenced table, when the query condition includes the connection condition of the foreign key referenced table and the foreign key referenced table, the filter condition for the foreign key column of the foreign key referenced table can also be converted into a referenced column filter condition for the referenced column of the foreign key referenced table.

[0055] Referring to the example of the order information table and the customer information table described above, if there is a filtering condition table A.ID>10, the filtering condition can be pushed down to table B.ID>10.

[0056] Step S203, push the filter condition down to the foreign key referenced table to obtain the reference column filter condition and save it. Specifically, if the filter condition can be pushed down to the reference column of the foreign key referenced table, generate a filter condition with the field name of the reference column of the foreign key referenced table and the condition expression being the same as the filter condition as the reference column filter condition. The condition expression is the limiting part of the field value in the filter condition. For example, if the filter condition table A.ID>10 is mentioned above and the condition expression is ">10", then the filter condition can generate the reference column filter condition of table B.ID>10 and be saved as table B.ID>10.

[0057] Step S204, determine whether the filtering conditions are traversed, if so, execute step S205, if not, continue traversing.

[0058] Step S205, calculating the foreign key connection selection rate of the foreign key referencing table and the foreign key referenced table. After the filtering condition traversal is completed, the foreign key connection selection rate of the foreign key referencing table and the foreign key referenced table is calculated, that is, the subsequent steps of step S102 are executed.

[0059] It should be noted that the process of traversing the filter conditions also includes determining whether the filter conditions are for the referenced columns of the foreign key referenced table, that is, detecting whether the original filter conditions in the query statement directly limit the referenced columns of the foreign key referenced table. If so, save the filter conditions. There is no order limit for determining whether the filter conditions are for the referenced columns of the foreign key referenced table and determining whether the filter conditions can be pushed down to the referenced columns of the foreign key referenced table.

[0060] Step S103, obtaining the initial foreign key connection selection rate for the foreign key reference table and the foreign key referenced table. The initial foreign key connection selection rate is one of the total number of rows in the foreign key referenced table. Specifically, an initial foreign key connection selection rate is obtained, and the value of the initial foreign key connection selection rate is 1 / N, where N is the total number of rows in the foreign key referenced table.

[0061] Step S104, modifying the initial foreign key connection selectivity according to the reference column filtering condition to obtain the foreign key connection selectivity that is finally used.

[0062] Reference Figure 3 Specifically, this step includes:

[0063] Step S301, obtain the selectivity of the referenced column filter condition in the foreign key referenced table, recorded as the filter selectivity, specifically, the ratio of the number of rows screened by the filter condition in the foreign key referenced table to the total number of rows in the foreign key referenced table.

[0064] Reference Figure 4 Specifically, this step includes:

[0065] Step S401, obtain the statistical information of the foreign key referenced table. Specifically, the statistical information includes at least the total number of rows in the foreign key referenced table and the value distribution of the referenced column. Commonly, the value distribution of the referenced column is usually stored in the form of a histogram. Or it is recorded as the frequency of each value. It can be understood by those skilled in the art that other recording methods of the statistical information of the database table can refer to the prior art, which will not be repeated here.

[0066] Step S402, obtaining the filter selectivity of the reference column filter condition according to the reference column filter condition and the statistical information. Specifically, obtaining the selectivity of the filter condition in a database table according to the filter condition of a database table and the statistical information of the database table is a prior art and will not be described in detail here.

[0067] Step S302: Divide the initial foreign key connection selectivity by the filter selectivity to obtain a revised foreign key connection selectivity. In other words, the denominator of the initial foreign key connection selectivity is multiplied by the filter selectivity to obtain a new foreign key connection selectivity.

[0068] In the prior art, the foreign key join selectivity of the foreign key referencing table and the foreign key referenced table is set by the program as a constant value, which is always maintained as one of the total number of rows in the foreign key referenced table. As a result, when the cardinality is estimated using the foreign key join selectivity of the foreign key referencing table and the foreign key referenced table, even if the data of the foreign key referenced table and the foreign key referencing table are filtered and screened, the same foreign key join selectivity will still be used, resulting in inaccurate cardinality estimation in some cases.

[0069] In the scheme of this embodiment, after obtaining a query statement whose query conditions include connection conditions for a foreign key referencing table and a foreign key referenced table, searching and saving the reference column filtering conditions for the reference column of the foreign key referenced table in the query statement, obtaining the initial foreign key connection selectivity for the foreign key referencing table and the foreign key referenced table, and correcting the initial foreign key connection selectivity according to the reference column filtering conditions to obtain the foreign key connection selectivity used in the end. That is, in the process of performing a connection query on the foreign key referencing table and the foreign key referenced table, the foreign key connection selectivity will be corrected according to the filtering conditions, and the same foreign key connection selectivity is not used in all cases, that is, the foreign key connection selectivity of the foreign key referencing table and the foreign key referenced table will not always remain at one-tenth of the total number of rows of the foreign key referenced table. In this way, according to different filtering conditions, a more appropriate foreign key connection selectivity can be applied, thereby improving the accuracy of the cardinality estimation of the connection query on the foreign key referencing table and the foreign key referenced table.

[0070] For example, referring to the order information table and the customer information table described above, if there is a connection query between Table A and Table B and the filter condition is Table A.ID>10, then the filter condition can be pushed down to the reference column filter condition Table B.ID>10, and both Table A and Table B will filter out a portion of the number of rows, and because each data in the foreign key column of the foreign key reference table can be found to exist uniquely in the reference column of the corresponding foreign key referenced table, the number of rows in the connection result should be the number of rows filtered out of Table A. However, in the prior art, the cardinality estimation is calculated by multiplying the number of rows filtered out of Table A by the number of rows filtered out of Table B by one of the total number of rows in Table B, and the obtained cardinality estimation value will be far from the actual value. However, this embodiment will correct the foreign key connection selectivity, and the cardinality estimation calculation method will become the number of rows filtered out of Table A multiplied by the number of rows filtered out of Table B multiplied by the corrected foreign key connection selectivity, so as to obtain a more accurate connection result estimation value.

[0071] Reference Figure 5 As shown, in one embodiment, in the case of multiple reference column filter conditions, the step of dividing the foreign key connection selectivity by the filter selectivity includes:

[0072] Step S501, traverse and select the remaining reference column filter conditions. That is, after the initial foreign key connection selectivity is corrected using the first reference column filter condition, traverse and select the remaining reference column filter conditions to continue the correction.

[0073] Step S502: updating the foreign key connection selection rate according to the selected reference column filtering condition.

[0074] Reference Figure 6 Specifically, this step includes:

[0075] Step S601, obtaining statistical information of the foreign key referenced table. The content of this step refers to the above step S401.

[0076] Step S602: Obtain the filter selection rate of the reference column filter condition according to the newly selected reference column filter condition and statistical information.

[0077] Step S603: Update the foreign key connection selectivity by dividing the foreign key connection selectivity after being modified by the last reference column filtering condition by the filtering selectivity.

[0078] Referring to the above, for example, if there are reference column filtering conditions Table B.ID>10 and Table B.ID<100, after the initial foreign key connection selectivity is corrected according to Table B.ID>10 to obtain the first corrected foreign key connection selectivity, Table B.ID<100 is selected again, and the first corrected foreign key connection selectivity is corrected using Table B.ID<100.

[0079] Step S503, check whether the reference column filter condition is traversed, if yes, end the modification of the foreign key connection selectivity, and use the current foreign key connection selectivity as the foreign key connection selectivity to be used finally. If no, continue traversing.

[0080] In the scheme of this embodiment, when there are multiple reference column filter conditions for the referenced column of the foreign key referenced table, the reference column filter conditions are traversed and selected, and the foreign key connection selection rate is updated according to the selected reference column filter conditions. That is, all reference column filter conditions are involved in the correction of the foreign key connection selection rate, thereby being able to further ensure the accuracy of the foreign key connection selection rate.

[0081] It should be noted that, in some other embodiments, when there are multiple reference column filter conditions for the reference column of the foreign key referenced table, the step of obtaining the selectivity of the reference column filter conditions in the foreign key referenced table can also be to calculate the selectivity of all reference column filter conditions combined as the filter selectivity.

[0082] like Figure 7 As shown, in one embodiment, the method for determining the selectivity of a database generally includes:

[0083] Step S701, obtaining a query statement. The query conditions of the query statement include connection conditions for the foreign key referencing table and the foreign key referenced table.

[0084] Step S702, traverse all filter conditions of the query statement.

[0085] Step S703, determining whether the filtering condition can be pushed down to the foreign key referenced table, if so, executing step S704, if not, executing step S705.

[0086] Step S704, save the reference column filter conditions pushed down to the foreign key referenced table. Specifically, if there are multiple sets of connection conditions between the foreign key reference table and the foreign key referenced table, record the corresponding reference column filter conditions respectively.

[0087] Step S705, determine whether the traversal of the filtering conditions is complete, if so, execute step S706, if not, continue traversing.

[0088] Step S706, traverse all referenced columns of the foreign key referenced table. Specifically, search the foreign key referenced table for referenced columns that match the connection condition between the foreign key referencing table and the foreign key referenced table.

[0089] Step S707, obtaining the foreign key connection selection rate for the foreign key referencing table and the foreign key referenced table. The initial value of the foreign key connection selection rate is one of the total number of rows in the foreign key referenced table. Specifically, if a reference column matching the connection conditions of the foreign key referencing table and the foreign key referenced table is found, an initial foreign key connection selection rate is obtained.

[0090] Step S708, traverse the reference column filter conditions saved for the reference column, specifically, traverse all reference column filter conditions saved for the current reference column in step S704.

[0091] Step S709, calculating the selectivity of the reference column filtering condition.

[0092] Step S710, update the foreign key connection selectivity to the current foreign key connection selectivity divided by the selectivity of the reference column filter condition. Specifically, if it is the first reference column filter condition traversed, it is the initial foreign key connection selectivity divided by the selectivity of the reference column filter condition. If it is the second or subsequent reference column filter conditions, it is the foreign key connection selectivity after the previous reference column filter condition is revised divided by the selectivity of the current reference column filter condition.

[0093] Step S711, traverse the saved reference column filtering conditions, if yes, execute step S712, if not, return to execute step S708.

[0094] Step S712, after traversing the referenced columns of the foreign key referenced table, if yes, end the modification of the foreign key connection selectivity of the foreign key referenced table, and use the obtained foreign key connection selectivity of each referenced column as the final foreign key connection selectivity. If no, return to step S706.

[0095] In the scheme of this embodiment, by correcting the foreign key connection selectivity according to the reference column filtering condition during the connection query of the foreign key referencing table and the foreign key referenced table, the same foreign key connection selectivity is not used in all cases, that is, the foreign key connection selectivity of the foreign key referencing table and the foreign key referenced table will not always remain at one-tenth of the total number of rows of the foreign key referenced table. In this way, according to different reference column filtering conditions, a more appropriate foreign key connection selectivity can be applied, thereby improving the accuracy of the cardinality estimation of the connection query of the foreign key referencing table and the foreign key referenced table.

[0096] This embodiment also provides a computer device and a computer-readable storage medium. Figure 8 is a schematic diagram of a computer device 10 according to one embodiment of the present invention. Fig. 9 is a schematic diagram of a computer-readable storage medium 20 according to one embodiment of the present invention.

[0097] The computer device 10 may include a memory 110, a processor 120, and a computer executable program 11 stored in the memory 110 and running on the processor 120, and when the processor 120 executes the computer executable program 11, the method for determining the selection rate of a database in any of the above embodiments is implemented.

[0098] The computer-readable storage medium 20 stores a computer-executable program 11 thereon. When the computer-executable program 11 is executed by a processor, the method for determining the selection rate of a database in any of the above-mentioned embodiments can be implemented.

[0099] This embodiment also provides a computer program product. Fig.10 1 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, and when the computer executable program 11 is executed by a processor 120, it can implement any of the above-mentioned methods for determining the selection rate of a database.

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

[0101] For the purpose of the description of the present embodiment, the computer-readable storage medium 20 can be any device that can contain, store, communicate, propagate or transmit a program for use with an instruction execution system, device or equipment or in conjunction with these instruction execution systems, devices or equipment. More specific examples of computer-readable media (a non-exhaustive list) include the following: an electrical connection portion (electronic device) with one or more wirings, a portable computer disk box (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable and editable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disk read-only memory (CDROM). In addition, the computer-readable storage medium 20 can even be paper or other suitable media on which the program can be printed, because the program can be obtained electronically, for example, by optically scanning the paper or other media, followed by editing, interpreting or processing in other suitable ways as necessary, and then stored in a computer memory.

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

[0103] The computer device 10 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smart phone. In some examples, the computer device 10 may be a cloud acquisition node. The computer device 10 may be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, a program module may include routines, programs, object programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. The computer device 10 may be implemented in a distributed cloud acquisition environment where remote processing devices linked via a communication network perform tasks. In a distributed cloud acquisition environment, program modules may be located on a local or remote acquisition system storage medium including a storage device.

[0104] The 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 the instructions during operation. The processor 120 may be a single-core processor, a multi-core processor, an acquisition cluster, or any number of other configurations. The memory 110 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0105] The processor 120 may be connected 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) via a system interconnect (e.g., PCI, PCI-Express, etc.). The I / O devices may include, for example, a keyboard and a pointing device, wherein the pointing device may include a touch pad or a touch screen, etc. The I / O devices may be built-in components of the computer device 10, or may be devices externally connected to an acquisition device.

[0106] Processor 120 can also be linked to a display interface suitable for connecting computer device 10 to a display device through a system interconnection. Display device can include a display screen as a built-in component of computer device 10. Display device can also include a computer monitor, a television or a projector, etc., which are externally connected to computer device 10. In addition, a network interface controller (NIC) can be suitable for connecting computer device 10 to a network through a system interconnection. In some embodiments, NIC can use any suitable interface or protocol (such as an Internet small computer system interface, etc.) to transmit data. The network can be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN) or the Internet, etc. A remote device can be connected to a computer device through a network.

[0107] At this point, those skilled in the art should recognize that, although multiple exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications that conform to the principles of the present invention can still be directly determined or derived based on the content disclosed in the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and recognized as covering all these other variations or modifications.

Claims

1. A method for determining a selectivity of a database, comprising: Obtain a query statement, wherein the query condition of the query statement includes a connection condition of a foreign key reference table and a foreign key referenced table; Find and save the reference column filtering condition of the reference column of the foreign key referenced table in the query statement; Obtaining initial foreign key connection selection rates for the foreign key referencing table and the foreign key referenced table, wherein the initial foreign key connection selection rate is one-tenth of the total number of rows in the foreign key referenced table; The initial foreign key join selectivity is modified according to the reference column filtering condition to obtain a foreign key join selectivity that is finally used.

2. The method for determining the selection rate of a database according to claim 1, wherein: The step of correcting the initial foreign key connection selectivity according to the reference column filtering condition comprises: Obtain the selectivity of the referenced column filter condition in the foreign key referenced table, recorded as filter selectivity; The initial foreign key connection selectivity is divided by the filtering selectivity to obtain a revised foreign key connection selectivity.

3. The method for determining the selection rate of a database according to claim 2, wherein: The step of obtaining the selectivity of the reference column filter condition in the foreign key referenced table includes: Obtaining statistical information of the table referenced by the foreign key; A filter selection rate of the reference column filter condition is obtained according to the reference column filter condition and the statistical information.

4. The method for determining the selection rate of a database according to claim 3, wherein: In the case of multiple reference column filter conditions, the step of dividing the initial foreign key connection selectivity by the filter selectivity includes: Traverse and select the remaining reference column filtering conditions; Updates the foreign key join selectivity based on the selected reference column filter condition.

5. The method for determining the selection rate of a database according to claim 4, wherein: The step of updating the foreign key connection selectivity according to the selected reference column filtering condition comprises: Obtaining statistical information of the table referenced by the foreign key; Obtaining a filter selection rate of the reference column filter condition according to the newly selected reference column filter condition and the statistical information; The foreign key join selectivity is updated by dividing the foreign key join selectivity corrected by the last reference column filter condition by the filter selectivity.

6. The method for determining the selection rate of a database according to claim 1, wherein: The step of searching and saving the reference column filtering condition for the reference column of the foreign key referenced table in the query statement comprises: Traversing all filtering conditions of the query statement; It is determined whether the filter condition can be pushed down to the referenced column of the foreign key referenced table. If so, the filter condition is pushed down to the foreign key referenced table to obtain the referenced column filter condition and save it.

7. The method for determining the selection rate of a database according to claim 6, wherein: The step of pushing the filtering condition to the foreign key referenced table to obtain the referenced column filtering condition comprises: A filtering condition whose field name is the referenced column of the foreign key referenced table and whose conditional expression is the same as the filtering condition is generated as the referenced column filtering 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 implements the method for determining the selection rate of a database according to any one of claims 1 to 7 when executing the computer executable program.

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 method for determining a selection rate of a database according to any one of claims 1 to 7.

10. A computer program product, comprising a computer executable program, wherein when the computer executable program is executed by a processor, the method for determining a selection rate of a database according to any one of claims 1 to 7 is implemented.

Images